--- title: "PK73304_LOSS_SUMMARY stored procedure documentation" summary: "Analysis of Oracle package/procedure including business rules and operations." status: draft owner: "SAIF" last_reviewed: "2026-07-28" source_schema: "WCIS_DBA" source_name: "PK73304_LOSS_SUMMARY" source_type: "PACKAGE" oracle_status: "found" library: SAIF.Business.UnitStat.Claim reference_type: package --- # PK73304_LOSS_SUMMARY ## Overview | Field | Value | | --- | --- | | **Schema** | WCIS_DBA | | **Type** | Package | | **Database** | Oracle | | **Calling libraries** | `SAIF.Business.UnitStat.Claim` | | **Calling classes** | `clsCurrentLoss, clsLossSummary, clsLossValuation, clsThirdPartyPrediction` | ## Purpose PK73304_LOSS_SUMMARY centralizes Oracle-side logic consumed by `SAIF.Business.UnitStat.Claim` for retrieval, persistence, sequence generation, or derived calculations. ## Procedures / Methods ### report_non_compensable | Field | Value | | --- | --- | | **Returns** | Scalar value | | **Called by** | `clsCurrentLoss, clsLossSummary, clsLossValuation, clsThirdPartyPrediction` | **Description:** report_non_compensable is one of the Oracle entry points referenced by this library. #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `in_claim_id` | IN | us_claim_unit.claim_id%TYPE | — | Parameter from package specification. | ### get_override_value_date | Field | Value | | --- | --- | | **Returns** | Scalar value | | **Called by** | `clsCurrentLoss, clsLossSummary, clsLossValuation, clsThirdPartyPrediction` | **Description:** get_override_value_date is one of the Oracle entry points referenced by this library. #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `in_claim_id` | IN | us_claim_valuation.claim_id%TYPE | — | Parameter from package specification. | | `in_policy_unit_seq_num` | IN | us_claim_valuation.us_policy_unit_seq_num%TYPE | — | Parameter from package specification. | | `in_value_num` | IN | us_claim_valuation.valuation_num%TYPE | — | Parameter from package specification. | ### get_override_prediction_date | Field | Value | | --- | --- | | **Returns** | Scalar value | | **Called by** | `clsCurrentLoss, clsLossSummary, clsLossValuation, clsThirdPartyPrediction` | **Description:** get_override_prediction_date is one of the Oracle entry points referenced by this library. #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `in_claim_id` | IN | us_claim_valuation.claim_id%TYPE | — | Parameter from package specification. | | `in_policy_unit_seq_num` | IN | us_claim_valuation.us_policy_unit_seq_num%TYPE | — | Parameter from package specification. | | `in_value_num` | IN | us_claim_valuation.valuation_num%TYPE | — | Parameter from package specification. | ### get_thirdparty_prediction | Field | Value | | --- | --- | | **Returns** | REF CURSOR or scalar value | | **Called by** | `clsCurrentLoss, clsLossSummary, clsLossValuation, clsThirdPartyPrediction` | **Description:** get_thirdparty_prediction is one of the Oracle entry points referenced by this library. #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `in_claim_id` | IN | us_claim_unit.claim_id%TYPE | — | Parameter from package specification. | | `in_eff_date` | IN | DATE | — | Parameter from package specification. | | `in_exp_date` | IN | DATE | — | Parameter from package specification. | | `recordset` | IN OUT | prediction_cursor | — | Parameter from package specification. | ### get_final_denial_dates | Field | Value | | --- | --- | | **Returns** | REF CURSOR or scalar value | | **Called by** | `clsCurrentLoss, clsLossSummary, clsLossValuation, clsThirdPartyPrediction` | **Description:** get_final_denial_dates is one of the Oracle entry points referenced by this library. #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `in_claim_id` | IN | us_claim_unit.claim_id%TYPE | — | Parameter from package specification. | | `in_value_num` | IN | us_policy_unit.unit_valuation_num%TYPE | — | Parameter from package specification. | | `recordset` | IN OUT | final_denial_cursor | — | Parameter from package specification. | ### get_ncci_claim_type_code | Field | Value | | --- | --- | | **Returns** | Scalar value | | **Called by** | `clsCurrentLoss, clsLossSummary, clsLossValuation, clsThirdPartyPrediction` | **Description:** get_ncci_claim_type_code is one of the Oracle entry points referenced by this library. #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `in_parta_claim_id` | IN | us_claim_unit.claim_id%TYPE | — | Parameter from package specification. | | `in_partb_claim_id` | IN | us_claim_unit.claim_id%TYPE | — | Parameter from package specification. | | `in_coverage_code` | IN | VARCHAR2 | — | Parameter from package specification. | ### get_ncci_loss_act_code | Field | Value | | --- | --- | | **Returns** | Scalar value | | **Called by** | `clsCurrentLoss, clsLossSummary, clsLossValuation, clsThirdPartyPrediction` | **Description:** get_ncci_loss_act_code is one of the Oracle entry points referenced by this library. #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `in_jurisdiction_code` | IN | VARCHAR2 | — | Parameter from package specification. | | `in_payroll_class_code` | IN | NUMBER | — | Parameter from package specification. | ### get_ncci_loss_type_code | Field | Value | | --- | --- | | **Returns** | Scalar value | | **Called by** | `clsCurrentLoss, clsLossSummary, clsLossValuation, clsThirdPartyPrediction` | **Description:** get_ncci_loss_type_code is one of the Oracle entry points referenced by this library. #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `in_injury_od_code` | IN | VARCHAR2 | — | Parameter from package specification. | ### get_ncci_fraud_type_code | Field | Value | | --- | --- | | **Returns** | Scalar value | | **Called by** | `clsCurrentLoss, clsLossSummary, clsLossValuation, clsThirdPartyPrediction` | **Description:** get_ncci_fraud_type_code is one of the Oracle entry points referenced by this library. #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `in_parta_claim_id` | IN | us_claim_unit.claim_id%TYPE | — | Parameter from package specification. | | `in_partb_claim_id` | IN | us_claim_unit.claim_id%TYPE := NULL | — | Parameter from package specification. | | `in_fully_fraudulent_flag` | IN | VARCHAR2 | — | Parameter from package specification. | ### get_ncci_settlement_type_code | Field | Value | | --- | --- | | **Returns** | Scalar value | | **Called by** | `clsCurrentLoss, clsLossSummary, clsLossValuation, clsThirdPartyPrediction` | **Description:** get_ncci_settlement_type_code is one of the Oracle entry points referenced by this library. #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `in_report_type_code` | IN | VARCHAR2 | — | Parameter from package specification. | | `in_parta_claim_id` | IN | us_claim_unit.claim_id%TYPE | — | Parameter from package specification. | | `in_partb_claim_id` | IN | us_claim_unit.claim_id%TYPE := NULL | — | Parameter from package specification. | | `in_denied_flag` | IN | VARCHAR2 | — | Parameter from package specification. | | `in_dcs_settlement_exists` | IN | VARCHAR2 | — | Parameter from package specification. | | `in_cda_settlement_exists` | IN | VARCHAR2 | — | Parameter from package specification. | | `in_federal_settlement_exists` | IN | VARCHAR2 | — | Parameter from package specification. | ## Business Rules | Rule ID | Rule | Condition | Action | | --- | --- | --- | --- | | PROC-RULE-001 | Package-backed logic is required by this library. | The library calls this Oracle package. | Return values or mutate data for the calling BOF object. | ## Operations | Operation | Description | Trigger | | --- | --- | --- | | report_non_compensable | Oracle routine referenced by `SAIF.Business.UnitStat.Claim`. | Called from package-backed BOF logic. | | get_override_value_date | Oracle routine referenced by `SAIF.Business.UnitStat.Claim`. | Called from package-backed BOF logic. | | get_override_prediction_date | Oracle routine referenced by `SAIF.Business.UnitStat.Claim`. | Called from package-backed BOF logic. | | get_thirdparty_prediction | Oracle routine referenced by `SAIF.Business.UnitStat.Claim`. | Called from package-backed BOF logic. | | get_final_denial_dates | Oracle routine referenced by `SAIF.Business.UnitStat.Claim`. | Called from package-backed BOF logic. | | get_ncci_claim_type_code | Oracle routine referenced by `SAIF.Business.UnitStat.Claim`. | Called from package-backed BOF logic. | | get_ncci_loss_act_code | Oracle routine referenced by `SAIF.Business.UnitStat.Claim`. | Called from package-backed BOF logic. | | get_ncci_loss_type_code | Oracle routine referenced by `SAIF.Business.UnitStat.Claim`. | Called from package-backed BOF logic. | | get_ncci_fraud_type_code | Oracle routine referenced by `SAIF.Business.UnitStat.Claim`. | Called from package-backed BOF logic. | | get_ncci_settlement_type_code | Oracle routine referenced by `SAIF.Business.UnitStat.Claim`. | Called from package-backed BOF logic. | ## Source Code
PL/SQL package specification and body ```sql PACKAGE pk73304_loss_summary AS c_non_compensable_start_date CONSTANT DATE := '01-JAN-2013'; TYPE prediction_rec IS RECORD( thrdparty_case_seq_num thrdparty_prediction.thrdparty_case_seq_num%TYPE , thrdparty_prediction_seq_num thrdparty_prediction.thrdparty_prediction_seq_num%TYPE , thrdparty_lien_seq_num thrdparty_prediction.thrdparty_lien_seq_num%TYPE , prediction_status_code thrdparty_prediction.prediction_status_code%TYPE , prediction_date thrdparty_prediction.prediction_date%TYPE); TYPE prediction_cursor IS REF CURSOR RETURN prediction_rec; TYPE final_denial_rec IS RECORD( final_denial_date us_reported_loss.final_denial_date%TYPE); TYPE final_denial_cursor IS REF CURSOR RETURN final_denial_rec; FUNCTION report_non_compensable ( in_claim_id IN us_claim_unit.claim_id%TYPE ) RETURN VARCHAR2; FUNCTION get_override_value_date ( in_claim_id IN us_claim_valuation.claim_id%TYPE , in_policy_unit_seq_num IN us_claim_valuation.us_policy_unit_seq_num%TYPE , in_value_num IN us_claim_valuation.valuation_num%TYPE ) RETURN DATE; FUNCTION get_override_prediction_date ( in_claim_id IN us_claim_valuation.claim_id%TYPE , in_policy_unit_seq_num IN us_claim_valuation.us_policy_unit_seq_num%TYPE , in_value_num IN us_claim_valuation.valuation_num%TYPE ) RETURN DATE; PROCEDURE get_thirdparty_prediction ( in_claim_id IN us_claim_unit.claim_id%TYPE , in_eff_date IN DATE , in_exp_date IN DATE , recordset IN OUT prediction_cursor ); PROCEDURE get_final_denial_dates ( in_claim_id IN us_claim_unit.claim_id%TYPE , in_value_num IN us_policy_unit.unit_valuation_num%TYPE , recordset IN OUT final_denial_cursor ); FUNCTION get_ncci_claim_type_code ( in_parta_claim_id IN us_claim_unit.claim_id%TYPE , in_partb_claim_id IN us_claim_unit.claim_id%TYPE , in_coverage_code IN VARCHAR2 ) RETURN VARCHAR2; FUNCTION get_ncci_loss_act_code ( in_jurisdiction_code IN VARCHAR2 , in_payroll_class_code IN NUMBER ) RETURN VARCHAR2; FUNCTION get_maritime_loss_act ( in_payroll_class_code IN NUMBER ) RETURN VARCHAR2; FUNCTION get_ncci_loss_type_code ( in_injury_od_code IN VARCHAR2 ) RETURN VARCHAR2; FUNCTION get_ncci_mco_type_code ( in_claim_id IN us_claim_unit.claim_id%TYPE , in_mco_id IN VARCHAR2 ) RETURN VARCHAR2; FUNCTION get_ncci_fraud_type_code ( in_parta_claim_id IN us_claim_unit.claim_id%TYPE , in_partb_claim_id IN us_claim_unit.claim_id%TYPE := NULL , in_fully_fraudulent_flag IN VARCHAR2 ) RETURN VARCHAR2; FUNCTION get_ncci_settlement_type_code ( in_report_type_code IN VARCHAR2 , in_parta_claim_id IN us_claim_unit.claim_id%TYPE , in_partb_claim_id IN us_claim_unit.claim_id%TYPE := NULL , in_denied_flag IN VARCHAR2 , in_dcs_settlement_exists IN VARCHAR2 , in_cda_settlement_exists IN VARCHAR2 , in_federal_settlement_exists IN VARCHAR2 ) RETURN VARCHAR2; FUNCTION get_ncci_lump_sum_flag ( in_parta_claim_id IN us_claim_unit.claim_id%TYPE , in_partb_claim_id IN us_claim_unit.claim_id%TYPE := NULL ) RETURN VARCHAR2; FUNCTION get_dci_mandatory_claim_type ( in_claim_id IN NUMBER , in_ftl_incurred_cost_amt IN NUMBER , in_ptd_incurred_cost_amt IN NUMBER ) RETURN VARCHAR2; END pk73304_loss_summary; / PACKAGE BODY pk73304_loss_summary IS /*================================================== Application : Unit Stat Program Name: Loss Summary Description : This package contains Loss Summary functionality ==================================================== Initials --Date-- Description of modification ======== ======= ============================= lanpet 05/05/2010 Inital release to support Unit Stat phase 4 lanpet 11/08/2010 Add 3rd party predictions to SUBROGATION_EXISTS lanpet 11/17/2010 Update settlement_exists to check transaction from date is NULL to filter out normal TL and awards lanpet 12/03/2010 Final Denial - use comp decision date + 75 days if denial status eff date is NULL.. lanpet 12/14/2010 Changed get_total_ndr_amount out PARM from decimal to number. lanpet 02/23/2011 Phase 6 Correction Reporting lanpet 05/13/2011 Prod fix: Add payment date to Final Date derivation. lanpet 09/21/2011 Include firm 3 transfers for fraud corrections.. lanpet 10/04/2011 Ignore non-chargable with cost category NULL (caused abort) from claim financial transaction detail rows lanpet 02/02/2012 Get a list of final denial dates for loss summary valuation lanpet 12/18/2012 Production Bug - Settlement type and Lump Sum Removed Awards Exists (NULL payment data) from Settlement Exists. lanpet 12/19/2012 2013 Non Compensable rule changes: 1. Add new function to get the Injury Claim denied final flag 2. Add new function to get the fraudulent claim code 3. Add new function to get the MCO code 4. Update NCCI Loss Record view to reference new functions lanpet 02/22/2013 New Third Party Inactive Status - remove the third party case status as part of the prediction select criteria - always get a prediction lanpet 02/27/2013 Filter out disapproved predictions Note: Only about 2 dozen disapproved in productuion and validated ALL had an approved prediction with later date - no corrections needed lanpet 03/04/2013 Add new function to get the settlement type and update the NCCI Loss Record view to reference new procedure lanpet 03/18/2013 2013 Non Compensable rule changes: 1. Split fraudulent into partial (original) and full procedures. 2. Add a new function to get lump sum flag 3. Add a new function to get recovery type 4. Update NCCI loss record to reference new functions lanpet 04/05/2013 Add function to get override valuation date lanpet 04/22/2013 2013 Non Compensable rule changes: 1. Finish FULL Fraudulent to add costs transferred off claim. 2. Changed fraud investigation from a JOIN to EXISTS - eliminate cartesian product lanpet 08/23/2013 Final Denial date deriviation - removed table expression to select the most recent claim payment and add as a column selection Previously, if no payment existed, ignored the claim cost date and returned a NULL (lacked a left outer join concept). lanpet 08/27/2014 Add function to get an override Third Party prediction effective date. lanpet 01/27/2015 Adverse Audit Finding - Always set lump sum flag = 'N' jantra 12/22/2016 Added procdure for voided claims cat amounts rilcar 05/08/2017 Updated to not double-select medical refund offsets NOTE: Need to obsolete get_fraud_recovery_amounts (split out) - provides ability to implement NCCI Loss Record view changes w/o loss summary fraudulent changes jantra 09/23/2020 Update settlement code type to check for federal settlement first. correct federal settlement - removed the Full or Part Code for federal and CDA settlement payments ==================================================*/ /********************************************************** Procedure Title: Report non compensable Description: Return a flag to apply a new set of non compensable rules for policy units effective on or after Jan 1, 2013 . Called by: Unit Stat **********************************************************/ FUNCTION report_non_compensable ( in_claim_id IN us_claim_unit.claim_id%TYPE ) RETURN VARCHAR2 IS v_report_flag VARCHAR2(1); BEGIN SELECT CASE WHEN COUNT(*) > 0 THEN 'Y' ELSE 'N' END INTO v_report_flag FROM us_claim_unit cu JOIN us_policy_unit pu ON pu.us_policy_unit_seq_num = cu.us_policy_unit_seq_num AND pu.active_unit_flag = 'Y' AND pu.historical_flag = 'N' AND pu.unit_eff_date >= c_non_compensable_start_date WHERE cu.claim_id = in_claim_id AND cu.active_unit_flag = 'Y'; RETURN v_report_flag; EXCEPTION WHEN OTHERS THEN pk73090_error_handler.raise_an_error(' pk73304.report_non_compensable: ' || in_claim_id || ',' || SQLERRM); END report_non_compensable; /********************************************************** Procedure Title: Get Override Valuation Date Description: Return a value date for a claim and report number Called by: Loss Summary **********************************************************/ FUNCTION get_override_value_date ( in_claim_id IN us_claim_valuation.claim_id%TYPE , in_policy_unit_seq_num IN us_claim_valuation.us_policy_unit_seq_num%TYPE , in_value_num IN us_claim_valuation.valuation_num%TYPE ) RETURN DATE AS v_value_date DATE; BEGIN SELECT valuation_date INTO v_value_date FROM us_claim_valuation WHERE claim_id = in_claim_id AND us_policy_unit_seq_num = in_policy_unit_seq_num AND valuation_num = in_value_num; RETURN v_value_date; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; WHEN OTHERS THEN pk73090_error_handler.raise_an_error(' pk73304. get_override_value_date: ' || in_claim_id || ',' || SQLERRM); END get_override_value_date; /********************************************************** Procedure Title: Get Override Valuation Date Description: Return a value date for a claim and report number Called by: Loss Summary **********************************************************/ FUNCTION get_override_prediction_date ( in_claim_id IN us_claim_valuation.claim_id%TYPE , in_policy_unit_seq_num IN us_claim_valuation.us_policy_unit_seq_num%TYPE , in_value_num IN us_claim_valuation.valuation_num%TYPE ) RETURN DATE AS v_prediction_eff_date DATE; BEGIN SELECT third_party_prediction_date INTO v_prediction_eff_date FROM us_claim_valuation WHERE claim_id = in_claim_id AND us_policy_unit_seq_num = in_policy_unit_seq_num AND valuation_num = in_value_num; RETURN v_prediction_eff_date; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; WHEN OTHERS THEN pk73090_error_handler.raise_an_error(' pk73304. get_override_prediction_date: ' || in_claim_id || ',' || SQLERRM); END get_override_prediction_date; /********************************************************** Procedure Title: Get Third Party Prediction Description: Return the sequence number of the most recent prediction and its lien within the unit stat valuation dates Called by: Unit Stat Loss Summary **********************************************************/ PROCEDURE get_thirdparty_prediction ( in_claim_id IN us_claim_unit.claim_id%TYPE , in_eff_date IN DATE , in_exp_date IN DATE , recordset IN OUT prediction_cursor ) AS BEGIN OPEN recordset FOR SELECT tp.thrdparty_case_seq_num , tp.thrdparty_prediction_seq_num , tp.thrdparty_lien_seq_num , tp.prediction_status_code , tp.prediction_date FROM thrdparty_case tc JOIN thrdparty_prediction tp ON tp.thrdparty_case_seq_num = tc.thrdparty_case_seq_num WHERE tc.claim_id = in_claim_id AND tp.prediction_status_code <> 'DISA' AND tp.prediction_date = (SELECT MAX(tp2.prediction_date) FROM thrdparty_prediction tp2 WHERE tp2.thrdparty_case_seq_num = tp.thrdparty_case_seq_num AND tp2.prediction_status_code <> 'DISA' AND ( TRUNC(tp2.prediction_date) > in_eff_date AND TRUNC(tp2.prediction_date) <= in_exp_date)); EXCEPTION WHEN OTHERS THEN pk73090_error_handler.raise_an_error(' pk73304.get_thirdparty_prediction: ' || in_claim_id || ',' || SQLERRM); END get_thirdparty_prediction; /********************************************************** Procedure Title: Get a list of Final Denial Dates Description: Return a list of final denial dates from report history and the current final denial date from claim unit. Called by: Unit Stat Loss Summary **********************************************************/ PROCEDURE get_final_denial_dates ( in_claim_id IN us_claim_unit.claim_id%TYPE , in_value_num IN us_policy_unit.unit_valuation_num%TYPE , recordset IN OUT final_denial_cursor ) AS BEGIN OPEN recordset FOR SELECT DISTINCT rl.final_denial_date AS final_denial_date FROM us_reported_loss rl JOIN us_claim_unit cu ON cu.claim_id = rl.claim_id AND cu.active_unit_flag = 'Y' JOIN us_policy_unit pu ON pu.us_policy_unit_seq_num = cu.us_policy_unit_seq_num JOIN us_unit_report ur ON ur.us_unit_report_seq_num = rl.us_unit_report_seq_num AND ur.us_policy_unit_seq_num = pu.us_policy_unit_seq_num AND ur.rejected_report_flag = 'N' WHERE rl.claim_id = in_claim_id AND ur.report_valuation_num = in_value_num AND rl.update_type_code <> 'D' UNION SELECT cu.final_denial_date AS final_denial_date FROM us_claim_unit cu WHERE cu.claim_id = in_claim_id AND cu.active_unit_flag = 'Y' ORDER BY final_denial_date; EXCEPTION WHEN OTHERS THEN pk73090_error_handler.raise_an_error(' pk73304.get_denial_final_dates: ' || in_claim_id|| ',' || SQLERRM); END get_final_denial_dates; /********************************************************** Procedure Title: Get NCCI Claim Type Code Description: Derive the code from injury claim Called by: NCCI Loss Record and Claim Corrections **********************************************************/ FUNCTION get_ncci_claim_type_code ( in_parta_claim_id IN us_claim_unit.claim_id%TYPE , in_partb_claim_id IN us_claim_unit.claim_id%TYPE , in_coverage_code IN VARCHAR2 ) RETURN VARCHAR2 IS v_claim_type_code VARCHAR2(5); BEGIN CASE WHEN in_partb_claim_id IS NULL THEN CASE WHEN in_coverage_code = 'A' THEN v_claim_type_code := '01'; ELSE v_claim_type_code := '02'; END CASE; ELSE v_claim_type_code := '03'; END CASE; RETURN v_claim_type_code; EXCEPTION WHEN OTHERS THEN pk73090_error_handler.raise_an_error(' pk73304.get_ncci_claim_type_code: ' || in_parta_claim_id || ',' || SQLERRM); END get_ncci_claim_type_code; /********************************************************** Procedure Title: Get NCCI Loss Act Code Description: Derive the code from injury claim Called by: NCCI Loss Record and Claim Corrections **********************************************************/ FUNCTION get_ncci_loss_act_code ( in_jurisdiction_code IN VARCHAR2 , in_payroll_class_code IN NUMBER ) RETURN VARCHAR2 IS v_loss_act_code VARCHAR2(5); BEGIN CASE WHEN in_jurisdiction_code IN (3, 6, 7, 8) THEN v_loss_act_code := '02'; WHEN in_jurisdiction_code = 4 THEN v_loss_act_code := get_maritime_loss_act(in_payroll_class_code); ELSE v_loss_act_code := '01'; END CASE; RETURN v_loss_act_code; EXCEPTION WHEN OTHERS THEN pk73090_error_handler.raise_an_error(' pk73304.get_ncci_loss_act_code: ' || SQLERRM); END get_ncci_loss_act_code; /********************************************************** Procedure Title: Get maritime loss act Description: Get the loss act for maritime jurisdiction based on the claim payroll class jurisdiction Called by: Private **********************************************************/ FUNCTION get_maritime_loss_act ( in_payroll_class_code IN NUMBER ) RETURN VARCHAR2 IS v_loss_act_code VARCHAR2(5); BEGIN SELECT CASE WHEN COUNT(*) > 0 THEN '02' ELSE '01' END INTO v_loss_act_code FROM manual_class_base WHERE manual_class_num like in_payroll_class_code || '%' AND benefit_jurisdiction_code = 'F' ; RETURN v_loss_act_code; EXCEPTION WHEN OTHERS THEN pk73090_error_handler.raise_an_error(' pk73304.get_maritime_loss_act: ' || SQLERRM); END get_maritime_loss_act; /********************************************************** Procedure Title: Get NCCI Loss Type Code Description: Derive the code from injury claim Called by: NCCI Loss Record and Claim Corrections **********************************************************/ FUNCTION get_ncci_loss_type_code ( in_injury_od_code IN VARCHAR2 ) RETURN VARCHAR2 IS v_loss_type_code VARCHAR2(5); BEGIN CASE WHEN in_injury_od_code = 'I' THEN v_loss_type_code := '01'; ELSE v_loss_type_code := '02'; END CASE; RETURN v_loss_type_code; EXCEPTION WHEN OTHERS THEN pk73090_error_handler.raise_an_error(' pk73304.get_ncci_loss_type_code: ' || SQLERRM); END get_ncci_loss_type_code; /********************************************************** Procedure Title: Get NCCI MCO Type Code Description: Derive the NCCI code from injury claim MCO Id For policy units effecitve on Jan 1, 2013, and claim (Part A) is covered by an MCO, set to '06' (managed care organization), else '01' (approved organization not specifically listed in other codes). Return '00' for claims not covered by an MCO. Called by: NCCI Loss Record **********************************************************/ FUNCTION get_ncci_mco_type_code ( in_claim_id IN us_claim_unit.claim_id%TYPE , in_mco_id IN VARCHAR2 ) RETURN VARCHAR2 IS v_mco_type_code VARCHAR2(5); BEGIN CASE WHEN in_mco_id IS NULL THEN v_mco_type_code := '00'; WHEN in_mco_id IN ('EX', '99', ' ') THEN v_mco_type_code := '00'; ELSE IF report_non_compensable(in_claim_id ) = 'Y' THEN v_mco_type_code := '06'; ELSE v_mco_type_code := '01'; END IF; END CASE; RETURN v_mco_type_code; EXCEPTION WHEN OTHERS THEN pk73090_error_handler.raise_an_error(' pk73304.get_ncci_mco_type_code: ' || in_claim_id || ',' || SQLERRM); END get_ncci_mco_type_code; /********************************************************** Procedure Title: Get NCCI Fraudulent Type Code Description: Derive the code from injury claim or investigation Report as partially fraudulent for a claim if a fraud investigation that has been closed exists for the policy unit effecitve before Jan 1, 2013. Only report fully fraudulent claims flagged on Injury Claim for policy units effective as of Jan 1, 2013. Partial fraud are no longer reported -- only costs are lowered. Called by: NCCI Loss Record **********************************************************/ FUNCTION get_ncci_fraud_type_code ( in_parta_claim_id IN us_claim_unit.claim_id%TYPE , in_partb_claim_id IN us_claim_unit.claim_id%TYPE := NULL , in_fully_fraudulent_flag IN VARCHAR2 ) RETURN VARCHAR2 IS v_fraud_type_code VARCHAR2(5); BEGIN IF report_non_compensable(in_parta_claim_id ) = 'Y' THEN IF in_fully_fraudulent_flag = 'Y' THEN v_fraud_type_code := '02'; ELSE v_fraud_type_code := '00'; END IF; ELSE SELECT CASE WHEN COUNT (*) > 0 THEN '01' ELSE '00' END INTO v_fraud_type_code FROM investigation i WHERE investigation_type_code = 'FRD' AND i.claim_id IN (in_parta_claim_id, in_partb_claim_id) AND i.investigation_closure_code IN (SELECT code FROM investigation_closure_code icc WHERE i.investigation_closure_code = icc.code AND icc.fraud_flag = 'Y'); END IF; RETURN v_fraud_type_code; EXCEPTION WHEN OTHERS THEN pk73090_error_handler.raise_an_error(' pk73304. get_ncci_fraud_type_code: ' || in_parta_claim_id || ',' || SQLERRM); END get_ncci_fraud_type_code; /********************************************************** Procedure Title: Get Settlement Type Code Description: Derive the code for a dismissed claim or existence of settlements. For policy units effective as of Jan 1, 2013, use the denied final flag from Injury Claim. Called by: NCCI Loss Record **********************************************************/ FUNCTION get_ncci_settlement_type_code ( in_report_type_code IN VARCHAR2 , in_parta_claim_id IN us_claim_unit.claim_id%TYPE , in_partb_claim_id IN us_claim_unit.claim_id%TYPE := NULL , in_denied_flag IN VARCHAR2 , in_dcs_settlement_exists IN VARCHAR2 , in_cda_settlement_exists IN VARCHAR2 , in_federal_settlement_exists IN VARCHAR2 ) RETURN VARCHAR2 AS v_final_denial_flag VARCHAR2(1); v_denied_flag VARCHAR2(1); v_parta_paid NUMBER; v_partb_paid NUMBER; v_settlement_type VARCHAR2(05); BEGIN IF report_non_compensable(in_parta_claim_id ) = 'Y' THEN v_denied_flag := in_denied_flag; ELSE SELECT CASE WHEN cub.claim_id IS NULL THEN CASE WHEN cua.final_denial_date IS NULL THEN 'N' ELSE 'Y' END WHEN cua.final_denial_date IS NULL OR cub.final_denial_date IS NULL THEN 'N' ELSE 'Y' END , lsa.paid_medical_amount + lsa.paid_indemnity_amount as parta_paid , NVL((SELECT lsb.paid_medical_amount + lsb.paid_indemnity_amount FROM us_loss_summary lsb WHERE lsb.claim_id = cub.claim_id AND lsb.us_policy_unit_seq_num = cub.us_policy_unit_seq_num AND lsb.report_type_code = in_report_type_code AND lsb.report_valuation_num = lsa.report_valuation_num ), 0) as partb_paid INTO v_final_denial_flag , v_parta_paid , v_partb_paid FROM us_claim_unit cua LEFT OUTER JOIN us_claim_unit cub ON cub.claim_id = in_partb_claim_id AND cub.active_unit_flag = 'Y' JOIN us_loss_summary lsa ON lsa.claim_id = cua.claim_id AND lsa.us_policy_unit_seq_num = cua.us_policy_unit_seq_num AND lsa.report_type_code = in_report_type_code AND lsa.report_valuation_num = (SELECT MAX( lsa2.report_valuation_num) FROM us_loss_summary lsa2 WHERE lsa2.claim_id = lsa.claim_id AND lsa2.us_policy_unit_seq_num = lsa.us_policy_unit_seq_num AND lsa2.report_type_code = in_report_type_code) WHERE cua.claim_id = in_parta_claim_id AND cua.active_unit_flag = 'Y'; IF v_final_denial_flag = 'Y' and (v_parta_paid + v_partb_paid) = 0 THEN v_denied_flag := 'Y'; ELSE v_denied_flag := 'N'; END IF; END IF; CASE WHEN in_dcs_settlement_exists = 'Y' THEN v_settlement_type := '03'; WHEN in_cda_settlement_exists = 'Y' OR in_federal_settlement_exists = 'Y' THEN v_settlement_type := '06'; WHEN v_denied_flag = 'Y' THEN v_settlement_type := '05'; ELSE v_settlement_type := '00'; END CASE; RETURN v_settlement_type; EXCEPTION WHEN OTHERS THEN pk73090_error_handler.raise_an_error(' pk73304. get_ncci_settlement_type_code: ' || in_parta_claim_id || ',' || SQLERRM); END get_ncci_settlement_type_code; /********************************************************** Procedure Title: Get Lump Sum Flag Description: Return a flag to indicate a settlement exists or non-scheduled awards lump payment was made Called by: NCCI Loss Record **********************************************************/ FUNCTION get_ncci_lump_sum_flag ( in_parta_claim_id IN us_claim_unit.claim_id%TYPE , in_partb_claim_id IN us_claim_unit.claim_id%TYPE := NULL ) RETURN VARCHAR2 AS v_lump_sum_flag VARCHAR2(1) := 'N'; BEGIN RETURN v_lump_sum_flag; EXCEPTION WHEN OTHERS THEN pk73090_error_handler.raise_an_error(' pk73304. get_ncci_lump_sum_flag: ' || in_parta_claim_id || ',' || SQLERRM); END get_ncci_lump_sum_flag; /********************************************************** Description: Get Fatal or PTD claim type for DCI mandatory reporting Called by: DCI Candidate Selection **********************************************************/ FUNCTION get_dci_mandatory_claim_type ( in_claim_id IN NUMBER , in_ftl_incurred_cost_amt IN NUMBER , in_ptd_incurred_cost_amt IN NUMBER ) RETURN VARCHAR2 IS v_type_code VARCHAR2(5); BEGIN SELECT CASE WHEN NVL (in_ftl_incurred_cost_amt, 0) <> 0 OR NVL (cu.reported_fatal_flag, 'N') = 'Y' THEN 'FTL' WHEN NVL (in_ptd_incurred_cost_amt, 0) <> 0 THEN 'PTD' ELSE NULL END INTO v_type_code FROM us_claim_unit cu JOIN us_policy_unit pu ON pu.us_policy_unit_seq_num = cu.us_policy_unit_seq_num AND pu.active_unit_flag = 'Y' AND pu.historical_flag = 'N' WHERE cu.claim_id = in_claim_id AND cu.active_unit_flag = 'Y'; RETURN v_type_code; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; WHEN OTHERS THEN pk73090_error_handler.raise_an_error(' pk73304.get_dci_mandatory_claim_type: ' || in_claim_id || ',' || SQLERRM); END get_dci_mandatory_claim_type; END pk73304_Loss_Summary; ```
## Related Documentation - [Procedure catalog](index.md) - [Business rules](../business-rules.md) - [Operations and processes](../operations-processes.md) - [SQL catalog](../sql/index.md) ## Sources - `Business\SAIF.Business.UnitStat.Claim\Current Loss\clsCurrentLoss.vb` - `Business\SAIF.Business.UnitStat.Claim\Loss Summary\clsLossSummary.vb` - `Business\SAIF.Business.UnitStat.Claim\Loss Summary\clsLossValuation.vb` - `Business\SAIF.Business.UnitStat.Claim\Loss Summary\clsThirdPartyPrediction.vb`