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;