--- title: "pk73059_prem_hist_summary.sp62005_retrieve_rate stored procedure documentation" summary: Procedure analysis for manual-class rate resolution. status: draft owner: Technical Writer last_reviewed: 2026-07-31 library: SAIF.Business.Policy.Rating reference_type: package source_schema: WCIS_DBA source_name: pk73059_prem_hist_summary.sp62005_retrieve_rate source_type: PACKAGE PROCEDURE oracle_status: found --- # 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](../business-rules.md#br-034-applicable-ard-is-derived-before-rate-lookup-starts) | 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](../business-rules.md#br-035-current-non-outstanding-filing-is-searched-first) | 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](../business-rules.md#br-036-replacement-class-lookup-runs-when-the-requested-class-is-missing-or-expired) | 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](../business-rules.md#br-037-outstanding-filing-rows-override-the-current-filing-when-present) | 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](../business-rules.md#br-038-original-class-number-is-preserved-when-no-replacement-class-is-resolved) | 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](../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 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](../operations-processes.md) with source reference back to this document. ## Source Code
PL/SQL procedure ```sql 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 - [Procedure catalog](index.md) - [Business rules](../business-rules.md) - [Operations and processes](../operations-processes.md) - [SQL catalog](../sql/index.md) ## Sources - Oracle DBA_SOURCE: WCIS_DBA.pk73059_prem_hist_summary - `Business\SAIF.Business.Policy.Rating\clsManualClassBase.vb`