pk73059_prem_hist_summary.sp62005_retrieve_rate¶
Overview¶
| Field | Value |
|---|---|
| Schema | WCIS_DBA |
| Type | Package procedure |
| Database | Oracle |
| Calling libraries | SAIF.Business.Policy.Rating |
| Calling classes | clsManualClassBase.GetManualClassBaseKey |
Purpose¶
Resolves the manual class, rate, description, exposure base, and rate-file sequence number that apply to a rating request, including replacement-class and outstanding-filing fallback logic.
Procedures / Methods¶
sp62005_retrieve_rate¶
| Field | Value |
|---|---|
| Returns | Nothing (procedure); resolved values are returned via the IN OUT parameters. |
| Called by | clsManualClassBase.GetManualClassBaseKey |
Description: Resolves the manual class, rate, description, exposure base, and rate-file sequence number that apply to a rating request, including replacement-class and outstanding-filing fallback logic. The procedure first derives the applicable ARD via get_applicable_ard, then opens class_cur against RATE_FILE_MASTER (outer-joined to MANUAL_CLASS_BASE) for the current, non-outstanding filing matching the applicable ARD and risk type. If no valid, unexpired class row is found, the procedure resolves a REPLACEMENT_MANUAL_CLASS_NUM (from rep_class_cur, or from the expired class row itself) and re-runs the class cursor against the replacement class. If a class is ultimately found, the procedure then checks outstanding_class_cur for an outstanding filing linked via NR_RATE_FILE_SEQ_NUM and, when found, replaces the resolved row with the outstanding-filing row. The resolved class number, rate, description, rate-file sequence number, and exposure base are written back to the IN OUT parameters; the original io_class_num is preserved via NVL when no class was ultimately resolved.
Parameters¶
| Parameter | Direction | Type | Default | Description |
|---|---|---|---|---|
io_class_num |
IN OUT | VARCHAR2 | — | Manual class number to resolve; on input the requested/current class, on output the resolved class number (unchanged from input when no class or replacement class was found). |
in_class_eff_date |
IN | DATE | — | Manual class effective date; used as the comparison date when looking up an outstanding filing via outstanding_class_cur and as the class-effective-date input to get_applicable_ard. |
in_ard |
IN | DATE | — | Anniversary rating date supplied by the caller; passed to get_applicable_ard to derive the actual lookup date. |
in_risk_type_code |
IN | VARCHAR2 | — | Risk-type/rate-tier code used to filter RATE_FILE_MASTER rows in both the class and outstanding-filing cursors. |
in_policy_period_eff_date |
IN | DATE | — | Effective date of the policy period; passed to get_applicable_ard to bound the mid-policy ARD check. |
in_policy_period_exp_date |
IN | DATE | — | Expiration date of the policy period; passed to get_applicable_ard as the fallback ARD when the mid-policy ARD was not rerated. |
in_ard_extension_flag |
IN | VARCHAR2 | — | ARD-extension indicator; passed through to get_applicable_ard. |
io_rate |
IN OUT | NUMBER | — | Manual class rate; overwritten with manual_class_rate from the resolved class row (or left as-is when no class is resolved, since class_rec_null clears the record and the assignment yields NULL). |
io_descr |
IN OUT | VARCHAR2 | — | Manual class description; overwritten with manual_class_descr from the resolved class row. |
io_rate_file_seq |
IN OUT | NUMBER | — | Rate-file sequence number; overwritten with rate_file_seq_num from the resolved class row. |
io_exposure_base |
IN OUT | VARCHAR2 | — | Exposure base code; overwritten with exposure_base_code from the resolved class row. |
Business Rules¶
Rules extracted from this procedure's logic:
| Rule ID | Rule | Condition | Action |
|---|---|---|---|
| BR-034 | Applicable ARD is derived before rate lookup starts. | Always on procedure entry. | Call helper get_applicable_ard(...) and use the returned date (v_ard) for all subsequent class-rate lookups. |
| BR-035 | Current non-outstanding filing is searched first. | class_cur filters RATE_FILE_MASTER on rate_file_eff_date <= v_ard, NVL(rate_file_exp_date, v_ard + 1) > v_ard, matching risk_type_code, new_outstanding_code = 'N'. |
Fetch the requested manual class from RATE_FILE_MASTER outer-joined to MANUAL_CLASS_BASE. |
| BR-036 | Replacement class lookup runs when the requested class is missing or expired. | class_cur%NOTFOUND, or the fetched row has a null manual_class_num, or the class rate window does not cover v_ard (NOT (manual_class_rate_eff_date <= v_ard AND manual_class_rate_exp_date > v_ard)). |
Resolve REPLACEMENT_MANUAL_CLASS_NUM (via rep_class_cur, or from the expired row) and reopen class_cur against the replacement class; if the replacement class is also not found, class_rec.manual_class_num is set to NULL. |
| BR-037 | Outstanding filing rows override the current filing when present. | A class row is found (class_rec.manual_class_num IS NOT NULL), then outstanding_class_cur finds a row via rfm.nr_rate_file_seq_num = cur_rate_file_seq_num. |
Replace class_rec with class_outstanding_rec (the outstanding-filing row). |
| BR-038 | Original class number is preserved when no replacement class is resolved. | Final resolved class is null (no class or replacement class found). | Set io_class_num := NVL(class_rec.manual_class_num, io_class_num); class_rec is reset to class_rec_null first when no class was ultimately found. |
These rules are documented in business-rules.md. Newly introduced rules must be added to
business-rules.mdas part of the same writing task.
Operations¶
Operations and processes implemented in this procedure:
| Operation | Description | Trigger |
|---|---|---|
| Resolve class rate in current filing | Finds the matching manual-class row in the current filing, with replacement-class fallback. | clsManualClassBase.GetManualClassBaseKey (SQL-019). |
| Prefer outstanding filing override | Rechecks the resolved class against an outstanding filing referenced by NR_RATE_FILE_SEQ_NUM. |
Continuation inside sp62005_retrieve_rate after a class row is found. |
These operations are also listed in operations-processes.md with source reference back to this document.
Source Code¶
PL/SQL procedure
PROCEDURE sp62005_retrieve_rate(io_class_num IN OUT VARCHAR2 /* manual class num */
, in_class_eff_date IN DATE /* manual class eff date */
, in_ard IN DATE /* Anniversary rating date */
, in_risk_type_code IN VARCHAR2 /* Tier of multi-tier rate bk*/
, in_policy_period_eff_date IN DATE /* effective date of policy period */
, in_policy_period_exp_date IN DATE /* expiration date of policy period */
, in_ard_extension_flag IN VARCHAR2 /* ard extension */
, io_rate IN OUT NUMBER /* Manual class rate */
, io_descr IN OUT VARCHAR2 /* Manual class descr */
, io_rate_file_seq IN OUT NUMBER /* rate_file_seq_num */
, io_exposure_base IN OUT VARCHAR2) /* Exposure base code */
IS
v_ard DATE;
CURSOR class_cur(cur_ard DATE
, cur_risk_type_code VARCHAR2
, cur_manual_class_num VARCHAR2)
IS
SELECT rfm.rate_file_seq_num
, mcb.manual_class_num
, mcb.manual_class_rate
, mcb.manual_class_descr
, mcb.exposure_base_code
, mcb.manual_class_rate_eff_date
, mcb.manual_class_rate_exp_date
, mcb.replacement_manual_class_num
FROM rate_file_master rfm
LEFT OUTER JOIN
manual_class_base mcb
ON rfm.rate_file_seq_num = mcb.rate_file_seq_num
AND mcb.manual_class_num = cur_manual_class_num
AND (mcb.manual_class_rate_eff_date <>
NVL(mcb.manual_class_rate_exp_date, mcb.manual_class_rate_eff_date + 1)
OR mcb.replacement_manual_class_num IS NOT NULL)
WHERE rfm.rate_file_eff_date <= cur_ard
AND NVL(rfm.rate_file_exp_date, cur_ard + 1) > cur_ard
AND rfm.risk_type_code = cur_risk_type_code
AND rfm.new_outstanding_code = 'N';
CURSOR outstanding_class_cur(cur_rate_file_seq_num NUMBER
, cur_risk_type_code VARCHAR2
, cur_manual_class_num VARCHAR2
, cur_class_eff_date DATE)
IS
SELECT rfm.rate_file_seq_num
, mcb.manual_class_num
, mcb.manual_class_rate
, mcb.manual_class_descr
, mcb.exposure_base_code
, mcb.manual_class_rate_eff_date
, mcb.manual_class_rate_exp_date
, mcb.replacement_manual_class_num
FROM rate_file_master rfm
, manual_class_base mcb
WHERE rfm.nr_rate_file_seq_num = cur_rate_file_seq_num
AND rfm.rate_file_eff_date <= cur_class_eff_date
AND rfm.risk_type_code = cur_risk_type_code
AND rfm.rate_file_seq_num = mcb.rate_file_seq_num
AND mcb.manual_class_num = cur_manual_class_num
ORDER BY rfm.rate_file_eff_date DESC
, rfm.rate_file_seq_num DESC;
class_rec_null class_cur%ROWTYPE;
class_rec class_cur%ROWTYPE;
class_outstanding_rec class_cur%ROWTYPE;
CURSOR rep_class_cur(cur_manual_class_num VARCHAR2
, cur_rate_file_seq_num NUMBER)
IS
SELECT mcb.replacement_manual_class_num
FROM manual_class_base mcb
WHERE mcb.manual_class_num = cur_manual_class_num
AND mcb.rate_file_seq_num = cur_rate_file_seq_num;
rep_class_rec rep_class_cur%ROWTYPE;
BEGIN
v_ard :=
get_applicable_ard(in_policy_period_eff_date
, in_policy_period_exp_date
, in_ard
, in_ard_extension_flag
, in_class_eff_date);
-- attempt to get the class details using the input class and applicable ard
OPEN class_cur(v_ard
, in_risk_type_code
, io_class_num);
FETCH class_cur INTO class_rec;
IF class_cur%NOTFOUND
OR (class_cur%FOUND
AND class_rec.manual_class_num IS NULL)
OR (class_cur%FOUND
AND NOT (class_rec.manual_class_rate_eff_date <= v_ard
AND class_rec.manual_class_rate_exp_date > v_ard)) THEN
IF class_rec.manual_class_num IS NULL THEN
-- class_cur was not found or the rate filing was found, but not the class
-- attempt to find a replacement class using the input class and rate file seq
OPEN rep_class_cur(io_class_num
, io_rate_file_seq);
FETCH rep_class_cur INTO rep_class_rec;
CLOSE rep_class_cur;
ELSE
-- class was found but was expired and had a replacement class
rep_class_rec.replacement_manual_class_num := class_rec.replacement_manual_class_num;
END IF;
-- if the mcb was found and there is a replacement class then find the replacemnt
-- class with the applicable ard
IF rep_class_rec.replacement_manual_class_num IS NOT NULL THEN
CLOSE class_cur;
OPEN class_cur(v_ard
, in_risk_type_code
, rep_class_rec.replacement_manual_class_num);
FETCH class_cur INTO class_rec;
IF class_cur%NOTFOUND
OR (class_cur%FOUND
AND class_rec.manual_class_num IS NULL)
OR (class_cur%FOUND
AND NOT (class_rec.manual_class_rate_eff_date <= v_ard
AND class_rec.manual_class_rate_exp_date > v_ard)) THEN
-- a replacement class was not found
class_rec.manual_class_num := NULL;
END IF;
END IF;
END IF;
CLOSE class_cur;
-- if the input class or replacement class was found in a new/renewing filing
-- for the applicable ard then see if there is an outstanding filing
IF class_rec.manual_class_num IS NOT NULL THEN
OPEN outstanding_class_cur(class_rec.rate_file_seq_num
, in_risk_type_code
, class_rec.manual_class_num
, in_class_eff_date);
FETCH outstanding_class_cur INTO class_outstanding_rec;
IF outstanding_class_cur%FOUND THEN
class_rec := class_outstanding_rec;
END IF;
CLOSE outstanding_class_cur;
ELSE
class_rec := class_rec_null;
END IF;
io_class_num := NVL(class_rec.manual_class_num, io_class_num);
io_rate := class_rec.manual_class_rate;
io_descr := class_rec.manual_class_descr;
io_rate_file_seq := class_rec.rate_file_seq_num;
io_exposure_base := class_rec.exposure_base_code;
END sp62005_retrieve_rate;
Related Documentation¶
Sources¶
- Oracle DBA_SOURCE: WCIS_DBA.pk73059_prem_hist_summary
Business\SAIF.Business.Policy.Rating\clsManualClassBase.vb