PK73193_submission.get_rate_file_seq_num

Overview

Field	Value
Schema	WCIS_DBA
Type	Package function
Database	Oracle
Calling libraries	SAIF.Business.Policy.Rating
Calling classes	clsManualClassBaseCollection.GetRateFileSeqNum

Purpose

Returns the active non-outstanding rate-file sequence number for an anniversary rating date and risk type, or zero when no filing matches.

Procedures / Methods

get_rate_file_seq_num

Field	Value
Returns	NUMBER — the matching RATE_FILE_MASTER.RATE_FILE_SEQ_NUM, or 0 when no active non-outstanding filing matches.
Called by	clsManualClassBaseCollection.GetRateFileSeqNum

Description: Looks up RATE_FILE_MASTER for a row where NEW_OUTSTANDING_CODE = 'N', the risk type matches, the filing is effective on or before the supplied ARD, and the filing has no expiration date or expires after the ARD. The lookup is wrapped in its own BEGIN...EXCEPTION block so that NO_DATA_FOUND is trapped locally and converted to a 0 return value rather than propagating as an unhandled exception.

Parameters

Parameter	Direction	Type	Default	Description
inARD	IN	RATE_FILE_MASTER.rate_file_eff_date%TYPE (DATE)	—	Anniversary rating date to match against each candidate filing's effective/expiration window.
inRiskTypeCode	IN	RATE_FILE_MASTER.RISK_TYPE_CODE%TYPE (VARCHAR2)	—	Risk-type/rate-tier code used to filter candidate filings.

Business Rules

Rules extracted from this function's logic:

Rule ID	Rule	Condition	Action
BR-041	Rate-file lookup returns zero when no active filing matches ARD and risk type.	Matching RATE_FILE_MASTER row exists where NEW_OUTSTANDING_CODE = 'N', RISK_TYPE_CODE = inRiskTypeCode, rate_file_eff_date <= inARD, and (rate_file_exp_date IS NULL or rate_file_exp_date > inARD); NO_DATA_FOUND is trapped locally.	Return the matching RATE_FILE_SEQ_NUM; otherwise return 0.

These rules are documented in business-rules.md. Newly introduced rules must be added to business-rules.md as part of the same writing task.

Operations

Operations and processes implemented in this function:

Operation	Description	Trigger
Return active rate-file sequence number	Looks up the active non-outstanding rate file for an anniversary rating date and risk type.	clsManualClassBaseCollection.GetRateFileSeqNum (SQL-021).

These operations are also listed in operations-processes.md with source reference back to this document.

Source Code

PL/SQL function

FUNCTION get_rate_file_seq_num
(    inARD IN RATE_FILE_MASTER.rate_file_eff_date%TYPE,
    inRiskTypeCode IN RATE_FILE_MASTER.RISK_TYPE_CODE%TYPE
)
 RETURN NUMBER IS

v_rate_file_seq_num             NUMBER := 0;

BEGIN
    BEGIN
        SELECT rate_file_seq_num
        INTO v_rate_file_seq_num
        FROM RATE_FILE_MASTER
        WHERE NEW_OUTSTANDING_CODE = 'N'
        AND RISK_TYPE_CODE = inRiskTypeCode
        AND rate_file_eff_date <= inARD
        AND (rate_file_exp_date IS NULL OR rate_file_exp_date > inARD);
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
        v_rate_file_seq_num := 0;
    END;

    RETURN v_rate_file_seq_num;
END get_rate_file_seq_num;

Related Documentation

• Procedure catalog
• Business rules
• Operations and processes
• SQL catalog

Sources

• Oracle DBA_SOURCE: WCIS_DBA.PK73193_submission
• Business\SAIF.Business.Policy.Rating\clsManualClassBaseCollection.vb