PACKAGE pk73059_prem_hist_summary
IS
TYPE v_array_type IS VARRAY(500) OF manual_class_base.manual_class_num%TYPE;
TYPE annual_phps_rec IS RECORD(
priority_num prem_hist_prem_summary.priority_num%TYPE
, prem_adjustment_type_code prem_hist_prem_summary.prem_adjustment_type_code%TYPE
, rating_rule_master_id prem_hist_prem_summary.rating_rule_master_id%TYPE
, prem_summary_eff_date prem_hist_prem_summary.prem_summary_eff_date%TYPE
, prem_summary_exp_date prem_hist_prem_summary.prem_summary_exp_date%TYPE
, prem_summary_adjustment prem_hist_prem_summary.prem_summary_adjustment%TYPE
, prem_summary_base_amount prem_hist_prem_summary.prem_summary_base_amount%TYPE
, prem_summary_diff_amount prem_hist_prem_summary.prem_summary_diff_amount%TYPE
, dcbs_diff_amt prem_hist_prem_summary.dcbs_diff_amt%TYPE
, include_std_prem_flag prem_rating_rule_priority.include_std_prem_flag%TYPE
, rating_rule_function_code prem_rating_rule_priority.rating_rule_function_code%TYPE
);
TYPE annual_phps_tt IS TABLE OF annual_phps_rec
INDEX BY BINARY_INTEGER;
PROCEDURE sp62005_retrieve_rate(
io_class_num IN OUT VARCHAR2
, in_class_eff_date IN DATE
, in_ard IN DATE
, in_risk_type_code IN VARCHAR2
, in_policy_period_eff_date IN DATE
, in_policy_period_exp_date IN DATE
, in_ard_extension_flag IN VARCHAR2
, io_rate IN OUT NUMBER
, io_descr IN OUT VARCHAR2
, io_rate_file_seq IN OUT NUMBER
, io_exposure_base IN OUT VARCHAR2);
PROCEDURE sp62022_get_gov_class(
in_policy_num IN NUMBER
, in_pol_per_eff_date IN DATE
, io_set_id IN OUT NUMBER
, io_governing_class IN OUT VARCHAR2);
PROCEDURE get_annualized_std_premium(
in_policy_num IN policy_period.policy_num%TYPE
, in_policy_period_eff_date IN policy_period.policy_period_eff_date%TYPE
, in_prem_hist_set_id IN prem_hist_summary.prem_hist_set_id%TYPE
, out_annualized_std_prem_amount OUT NUMBER);
FUNCTION get_standard_premium(
in_prem_hist_set_id IN prem_hist_summary.prem_hist_set_id%TYPE)
RETURN NUMBER;
FUNCTION get_reported_payroll(
in_prem_hist_set_id IN prem_hist_class_coverage.prem_hist_set_id%TYPE
, in_class_cov_seq_num IN prem_hist_class_coverage.class_cov_seq_num%TYPE)
RETURN NUMBER;
PROCEDURE get_6_digit_gov_class(
in_policy_num IN policy_period.policy_num%TYPE
, in_pol_per_eff_date IN policy_period.policy_period_eff_date%TYPE
, out_governing_class OUT prem_hist_class_coverage.MANUAL_CLASS_NUM%TYPE);
FUNCTION determine_manual_premium(
in_policy_num IN policy.policy_num%TYPE
, in_policy_period_eff_date IN policy_period.policy_period_eff_date%TYPE)
RETURN NUMBER;
FUNCTION get_max_ph_set_id(
in_policy_num IN policy.policy_num%TYPE
, in_policy_period_eff_date IN policy_period.policy_period_eff_date%TYPE)
RETURN NUMBER;
END pk73059_prem_hist_summary;
/
PACKAGE BODY pk73059_prem_hist_summary
/*******************************************************************************
| SQL Script : pk73059_bod.sql
| Called By :
| Prepared By : carade
| Created : 01/05/2007
| Modification History
| This package was split out from pk73059. It contains procedures related to the
| premium history set but not the premium calculation. See original comments in
| pk62030.
| ----------------------------------------------------------------------------
| 11/13/2006 carade - Split out from pk62030.
| Change sp72934 for wrap-ups.
| 04/13/2007 carade - Modified sp62022 to check for a value of 0 in deciding whether to
| select the max set id.
| 06/11/2007 carade - Modified sp72930 to create reestimated set as type 3 if period is billed like
| an installment plan.
| 02/27/2008 leedas - OSQ project - Added a stored procedure determine_manual_premium to get the manual premium
| 03/10/2008 carade trackit 3261 - Corrected sp62001 to recalculated prorated payroll if there was reported adjusted rate
| class attached to original class coverage record.
| 05/13/2008 leedas - OSQ Prod Fix - Added stored procedure get_max_ph_set_id to get max premium history set id
| Added stored procedure get_annualized_man_premium to get annualized manual premium
| 05/19/2008 leedas - OSQ Prod Fix - Modified determine_manual_premium to determine the manual premium
| when WCIS owns the submission else return 0
| 07/15/2008 valrey - Modified sp62022 to cursor get_min_seq_cur and add a check to the govering class.
| 12/09/2008 carade - premium audit
| Added function to get standard premium.
| 04/15/2009 carade - premium audit
| Added function to get reported payroll for arrears.
| 08/14/2009 valrey - Trackit 2592 - Regional Business
| Added procedure to get 6 digit governing class code.
| 12/07/2009 carade - Trackit project 1626 (variable minimum)
| Renamed sp62022 and removed minimum premium as an out variable.
|19/19/2016 saycha - Removed unrequired prcedures from package.
| 12/07/2017 tinhay - Return 0 from get_annualized_std_premium for GW policies.
| 02/09/2018 carade - Calculate annualized std premium for policies.
| 02/18/2021 lanpet - Include rating_rule_function_code to filter MIN row for
| calculate annualized std premium for BDS download.
|*******************************************************************************/
IS
FUNCTION get_applicable_ard(in_policy_period_eff_date IN DATE
, in_policy_period_exp_date IN DATE
, in_ard IN DATE
, in_ard_extension_flag IN VARCHAR2
, in_eff_date IN DATE)
RETURN DATE
IS
v_next_ard DATE;
BEGIN
v_next_ard :=
sp72233_get_next_ard(in_policy_period_eff_date
, in_policy_period_exp_date
, in_ard
, in_ard_extension_flag);
IF v_next_ard > in_policy_period_eff_date
AND v_next_ard < in_policy_period_exp_date THEN
IF sp72247_midpolicy_ard_rerated(v_next_ard) THEN
NULL;
ELSE
v_next_ard := in_policy_period_exp_date;
END IF;
END IF;
-- determine the ard that applies to the eff date
IF v_next_ard > in_eff_date THEN
RETURN in_ard;
ELSE
RETURN v_next_ard;
END IF;
EXCEPTION
WHEN OTHERS THEN
pk73090_error_handler.raise_an_error(
'get_applicable_ard '
|| in_policy_period_eff_date
|| ','
|| in_policy_period_exp_date
|| ','
|| in_ard
|| ','
|| in_ard_extension_flag
|| ','
|| in_eff_date
|| ','
|| SQLERRM);
END get_applicable_ard;
/****************************************************************************
*Procedure Title : Retrieve_Rate *
*Name : SP62005_retrieve_rate *
*Called By : pk73059_prem_hist_summary.SP62011, SP62082_setup_class_coverage, *
* UW250S, SP62029_modify_policy_period *
*Prepared By : Sundeep sardana *
*Date Last Updated : 04/15/00
* 9/07/2000 - Valrey - Checked to see if class has expired. *
* 10/29/2010 - carade - rewrote 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;
/********************************************************
* Procedure Title : Get Governing Class
* Name : sp62022_get_gov_class
* Prepared By : Saiyed I. Haider
* Date Last updated : 06/18/1996
*
* carade 05/06/2002 5.9 csr 4
* Added nvl to sum of payroll to sum ifany classes.
* julfle 02/10/2003 5.8.3 csr6
* modified to exclude classes 8810, 8742 and 7380 from
* initial select and to include those in select only if
* no other class found. Added edit to use lowest numeric
* class if payroll is zero.
*********************************************************/
PROCEDURE sp62022_get_gov_class(in_policy_num IN NUMBER
, in_pol_per_eff_date IN DATE
, io_set_id IN OUT NUMBER
, io_governing_class IN OUT VARCHAR2)
IS
v_seq_num NUMBER; /* to store minimum rate_file_seq_num */
max_payroll NUMBER; /* Maximum payroll, to determine governing class */
/***********************************************************
This cursor gets the most recent set id if the function call
has passed a null or 0 set id.
***********************************************************/
CURSOR set_id_cur
IS
SELECT MAX(prem_hist_set_id)
FROM prem_hist_summary
WHERE policy_num = in_policy_num
AND policy_period_eff_date = in_pol_per_eff_date;
/***********************************************************
Create a cursor that gets the sum of payroll for each manual
class on the particular set
***********************************************************/
CURSOR max_payroll_cur
IS
SELECT SUBSTR(manual_class_num
, 1
, 4)
manual_class_num
, SUM(NVL(manual_class_period_payroll, 0)) sum_payroll
FROM prem_hist_class_coverage
WHERE prem_hist_set_id = io_set_id
AND SUBSTR(manual_class_num
, 1
, 4) NOT IN ('8810', '8742', '7380')
GROUP BY SUBSTR(manual_class_num
, 1
, 4);
payroll_rec max_payroll_cur%ROWTYPE;
CURSOR max_payroll_excluded_cur
IS
SELECT SUBSTR(manual_class_num
, 1
, 4)
manual_class_num
, SUM(NVL(manual_class_period_payroll, 0)) sum_payroll
FROM prem_hist_class_coverage
WHERE prem_hist_set_id = io_set_id
GROUP BY SUBSTR(manual_class_num
, 1
, 4);
payroll_excluded_rec max_payroll_excluded_cur%ROWTYPE;
CURSOR get_lowest_class_cur
IS
SELECT MIN(SUBSTR(manual_class_num
, 1
, 4))
manual_class_num
FROM prem_hist_class_coverage
WHERE prem_hist_set_id = io_set_id;
get_lowest_class_rec get_lowest_class_cur%ROWTYPE;
/** local procedure(s) **/
/** This procedure closes all the open cursors **/
PROCEDURE close_cursors
IS
BEGIN
IF set_id_cur%ISOPEN THEN
CLOSE set_id_cur;
END IF;
IF max_payroll_cur%ISOPEN THEN
CLOSE max_payroll_cur;
END IF;
IF max_payroll_excluded_cur%ISOPEN THEN
CLOSE max_payroll_excluded_cur;
END IF;
IF get_lowest_class_cur%ISOPEN THEN
CLOSE get_lowest_class_cur;
END IF;
END;
BEGIN
/**************************************************************
If a valid set id has not been passed in, get the most recent
Several callers pass set id = 0 to indicate no set.
***************************************************************/
IF (io_set_id IS NULL)
OR (io_set_id = 0) THEN
IF NOT set_id_cur%ISOPEN THEN
OPEN set_id_cur;
END IF;
FETCH set_id_cur INTO io_set_id;
IF set_id_cur%NOTFOUND THEN
close_cursors;
raise_application_error(-20001
, 'No Data Found in PHS in SP62022');
END IF;
close_cursors;
END IF;
/***********************************************************
Set the governing_class to 'NONE' in case there are no class
coverage records. If one is found, it replaces the default
value.
***********************************************************/
max_payroll := 0;
io_governing_class := 'NONE';
FOR payroll_rec IN max_payroll_cur
LOOP
IF ((payroll_rec.sum_payroll >= max_payroll)
AND (payroll_rec.manual_class_num IS NOT NULL)) THEN
max_payroll := payroll_rec.sum_payroll;
io_governing_class := payroll_rec.manual_class_num;
END IF;
END LOOP;
/*********************************************************
If there is no governing class, check for one of the
otherwise excluded classes
**********************************************************/
IF ((io_governing_class IS NULL)
OR (io_governing_class = 'NONE')) THEN
max_payroll := 0;
io_governing_class := 'NONE';
FOR payroll_excluded_rec IN max_payroll_excluded_cur
LOOP
IF ((payroll_excluded_rec.sum_payroll >= max_payroll)
AND (payroll_excluded_rec.manual_class_num IS NOT NULL)) THEN
max_payroll := payroll_excluded_rec.sum_payroll;
io_governing_class := payroll_excluded_rec.manual_class_num;
END IF;
END LOOP;
END IF;
/* if the payroll on the policy is zero then use the lowest numeric class */
IF max_payroll = 0 THEN
OPEN get_lowest_class_cur;
FETCH get_lowest_class_cur INTO get_lowest_class_rec;
IF get_lowest_class_cur%NOTFOUND THEN
close_cursors;
raise_application_error(-20001
, 'No Data Found in get_lowest_class in SP62022');
ELSE
io_governing_class := get_lowest_class_rec.manual_class_num;
END IF;
CLOSE get_lowest_class_cur;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
close_cursors;
raise_application_error(-20002
, 'Error in SP62022 ' || SQLERRM);
WHEN PROGRAM_ERROR THEN
close_cursors;
raise_application_error(-20003
, 'Error in SP62022 ' || SQLERRM);
WHEN STORAGE_ERROR THEN
close_cursors;
raise_application_error(-20004
, 'Error in SP62022 ' || SQLERRM);
WHEN VALUE_ERROR THEN
close_cursors;
raise_application_error(-20005
, 'Error in SP62022 ' || SQLERRM);
WHEN INVALID_CURSOR THEN
close_cursors;
raise_application_error(-20006
, 'Error in SP62022 ' || SQLERRM);
END sp62022_get_gov_class;
/********************************************************************
| Procedure Title : Get annualized standard premium. Only call for a policy that is
| not exactly 1 year in length.
| Name : get_annualized_std_premium
| Prepared By : carade
| Created : 09/18/2007
| Modification History
| ------------------------------------------------------------------
|
| Description :
| Calls annualize_premium_history_set to create an annualized set and then sums
| the standard premium from the returned table and returns.
*******************************************************************/
PROCEDURE get_annualized_std_premium(
in_policy_num IN policy_period.policy_num%TYPE
, in_policy_period_eff_date IN policy_period.policy_period_eff_date%TYPE
, in_prem_hist_set_id IN prem_hist_summary.prem_hist_set_id%TYPE
, out_annualized_std_prem_amount OUT NUMBER)
IS
v_phps_t annual_phps_tt;
v_source VARCHAR2(5);
v_standard_prem_amt number;
v_period_end_date date;
BEGIN
select PP.SOURCE_SYSTEM_CODE
into v_source
from policy_period pp
where PP.POLICY_NUM = in_policy_num
and PP.POLICY_PERIOD_EFF_DATE = in_policy_period_eff_date;
IF v_source = 'GW' THEN
out_annualized_std_prem_amount := 0;
select sum(case when prrp.include_std_prem_flag = 'Y' then phps.prem_summary_diff_amount else 0 end)
, max(phps.prem_summary_exp_date)
into v_standard_prem_amt
, v_period_end_date
from prem_hist_prem_summary phps,
prem_rating_rule_priority prrp
where phps.prem_hist_set_id = in_prem_hist_set_id
and phps.rating_rule_master_id = prrp.rating_rule_master_id
and phps.priority_num = prrp.priority_num
and prrp.rating_rule_function_code NOT IN ('PREM', 'DCBS');
if v_period_end_date > in_policy_period_eff_date then
out_annualized_std_prem_amount := round((v_standard_prem_amt / (v_period_end_date - in_policy_period_eff_date))
* (add_months(in_policy_period_eff_date,12) - in_policy_period_eff_date),2);
end if;
ELSE
WCIS_DBA.PK73059_Prem_Hist_Summary.get_annualized_std_premium@POLDB(in_policy_num
, in_policy_period_eff_date,in_prem_hist_set_id,out_annualized_std_prem_amount);
END IF;
EXCEPTION
WHEN OTHERS THEN
pk73090_error_handler.raise_an_error(
'get_annualized_std_premium: '
|| in_policy_num
|| ','
|| in_policy_period_eff_date
|| ','
|| in_prem_hist_set_id
|| ','
|| SQLERRM);
END get_annualized_std_premium;
/* ===========================================================================
Name : determine_manual_premium
Prepared By : leedas
Created : 02/21/2008
Description : Returns manual premium
Modification History
05/13/2008 leedas - OSQ Prod Fix Added logic to call get_annualized_man_premium in order to get
the manual premium when a policy length is not 1 year.
05/19/2008 leedas - OSQ Prod Fix - Modified to determine the manual premium when WCIS owns the submission else return 0
--------------------------------------------------------------------------
=========================================================================== */
FUNCTION determine_manual_premium(
in_policy_num IN policy.policy_num%TYPE
, in_policy_period_eff_date IN policy_period.policy_period_eff_date%TYPE)
RETURN NUMBER
IS
BEGIN
RETURN WCIS_DBA.PK73059_Prem_Hist_Summary.determine_manual_premium@POLDB(in_policy_num, in_policy_period_eff_date);
END determine_manual_premium;
/********************************************************************
| Procedure Title : Get standard premium for a premium history set.
| Name : get_standard_premium
| Prepared By : carade
| Created : 12/09/2008
| Modification History
| ------------------------------------------------------------------
|
| Description :
| Returns the standard premium from a premium history set for a type 1 or 3 set.
*******************************************************************/
FUNCTION get_standard_premium(in_prem_hist_set_id IN prem_hist_summary.prem_hist_set_id%TYPE)
RETURN NUMBER
IS
v_std_prem_amount NUMBER;
BEGIN
SELECT NVL(SUM(prem_summary_diff_amount), 0)
INTO v_std_prem_amount
FROM prem_hist_prem_summary phps
, prem_rating_rule_priority prrp
WHERE phps.rating_rule_master_id = prrp.rating_rule_master_id
AND phps.priority_num = prrp.priority_num
AND prrp.rating_rule_function_code NOT IN ('PREM', 'DCBS')
AND prrp.include_std_prem_flag = 'Y'
AND phps.prem_hist_set_id = in_prem_hist_set_id;
RETURN v_std_prem_amount;
EXCEPTION
WHEN OTHERS THEN
pk73090_error_handler.raise_an_error('get_standard_premium ' || in_prem_hist_set_id || SQLERRM);
END get_standard_premium;
/********************************************************************
| Procedure Title : Get reported payroll for arrears
| Name : get_reported_payroll
| Prepared By : carade
| Created : 04/15/2009
| Modification History
| ------------------------------------------------------------------
|
| Description :
| Get the reported payroll for a class.
*******************************************************************/
FUNCTION get_reported_payroll(in_prem_hist_set_id IN prem_hist_class_coverage.prem_hist_set_id%TYPE
, in_class_cov_seq_num IN prem_hist_class_coverage.class_cov_seq_num%TYPE)
RETURN NUMBER
IS
v_payroll prem_hist_adj_rate_class.class_payroll%TYPE;
BEGIN
SELECT NVL(SUM(pharc.class_payroll), 0)
INTO v_payroll
FROM prem_hist_adj_rate_class pharc
WHERE pharc.prem_hist_set_id = in_prem_hist_set_id
AND pharc.class_cov_seq_num = in_class_cov_seq_num
AND pharc.class_reported_flag = 'Y';
RETURN v_payroll;
EXCEPTION
WHEN OTHERS THEN
pk73090_error_handler.raise_an_error(
'get_reported_payroll ' || in_prem_hist_set_id || ',' || in_class_cov_seq_num || ',' || SQLERRM);
END get_reported_payroll;
/********************************************************
* Procedure Title : Get Governing Class
* Name : get_6_digit_gov_class
* Prepared By : Saiyed I. Haider
* Date Last updated : 06/18/1996
*
* valrey 08/14/2009 Trackit 2592
* Copied from sp62022.
*********************************************************/
PROCEDURE get_6_digit_gov_class(
in_policy_num IN policy_period.policy_num%TYPE
, in_pol_per_eff_date IN policy_period.policy_period_eff_date%TYPE
, out_governing_class OUT prem_hist_class_coverage.manual_class_num%TYPE)
IS
max_payroll NUMBER; /* Maximum payroll, to determine governing class */
v_prem_hist_set_id prem_hist_summary.prem_hist_set_id%TYPE;
/***********************************************************
This cursor gets the most recent set id if the function call
has passed a null or 0 set id.
***********************************************************/
CURSOR set_id_cur
IS
SELECT MAX(prem_hist_set_id)
FROM prem_hist_summary
WHERE policy_num = in_policy_num
AND policy_period_eff_date = in_pol_per_eff_date;
/***********************************************************
Create a cursor that gets the sum of payroll for each manual
class on the particular set
***********************************************************/
CURSOR max_payroll_cur
IS
SELECT manual_class_num
, SUM(NVL(manual_class_period_payroll, 0)) sum_payroll
FROM prem_hist_class_coverage
WHERE prem_hist_set_id = v_prem_hist_set_id
AND SUBSTR(manual_class_num
, 1
, 4) NOT IN ('8810', '8742', '7380')
GROUP BY manual_class_num;
payroll_rec max_payroll_cur%ROWTYPE;
CURSOR max_payroll_excluded_cur
IS
SELECT manual_class_num
, SUM(NVL(manual_class_period_payroll, 0)) sum_payroll
FROM prem_hist_class_coverage
WHERE prem_hist_set_id = v_prem_hist_set_id
GROUP BY manual_class_num;
payroll_excluded_rec max_payroll_excluded_cur%ROWTYPE;
CURSOR get_lowest_class_cur
IS
SELECT MIN(manual_class_num) manual_class_num
FROM prem_hist_class_coverage
WHERE prem_hist_set_id = v_prem_hist_set_id;
get_lowest_class_rec get_lowest_class_cur%ROWTYPE;
/** local procedure(s) **/
/** This procedure closes all the open cursors **/
PROCEDURE close_cursors
IS
BEGIN
IF set_id_cur%ISOPEN THEN
CLOSE set_id_cur;
END IF;
IF max_payroll_cur%ISOPEN THEN
CLOSE max_payroll_cur;
END IF;
IF max_payroll_excluded_cur%ISOPEN THEN
CLOSE max_payroll_excluded_cur;
END IF;
IF get_lowest_class_cur%ISOPEN THEN
CLOSE get_lowest_class_cur;
END IF;
END;
BEGIN
IF NOT set_id_cur%ISOPEN THEN
OPEN set_id_cur;
END IF;
FETCH set_id_cur INTO v_prem_hist_set_id;
IF set_id_cur%NOTFOUND THEN
close_cursors;
raise_application_error(-20001
, 'No Data Found in PHS in get_6_digit_gov_class');
END IF;
close_cursors;
/***********************************************************
Set the governing_class to 'NONE' in case there are no class
coverage records. If one is found, it replaces the default
value.
***********************************************************/
max_payroll := 0;
out_governing_class := 'NONE';
FOR payroll_rec IN max_payroll_cur
LOOP
IF ((payroll_rec.sum_payroll >= max_payroll)
AND (payroll_rec.manual_class_num IS NOT NULL)) THEN
max_payroll := payroll_rec.sum_payroll;
out_governing_class := payroll_rec.manual_class_num;
END IF;
END LOOP;
/*********************************************************
If there is no governing class, check for one of the
otherwise excluded classes
**********************************************************/
IF ((out_governing_class IS NULL)
OR (out_governing_class = 'NONE')) THEN
max_payroll := 0;
out_governing_class := 'NONE';
FOR payroll_excluded_rec IN max_payroll_excluded_cur
LOOP
IF ((payroll_excluded_rec.sum_payroll >= max_payroll)
AND (payroll_excluded_rec.manual_class_num IS NOT NULL)) THEN
max_payroll := payroll_excluded_rec.sum_payroll;
out_governing_class := payroll_excluded_rec.manual_class_num;
END IF;
END LOOP;
END IF;
/* if the payroll on the policy is zero then use the lowest numeric class */
IF max_payroll = 0 THEN
OPEN get_lowest_class_cur;
FETCH get_lowest_class_cur INTO get_lowest_class_rec;
IF get_lowest_class_cur%NOTFOUND THEN
close_cursors;
raise_application_error(-20001
, 'No Data Found in get_lowest_class in get_6_digit_gov_class');
ELSE
out_governing_class := get_lowest_class_rec.manual_class_num;
END IF;
CLOSE get_lowest_class_cur;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
close_cursors;
raise_application_error(-20002
, 'Error in get_6_digit_gov_class ' || SQLERRM);
WHEN PROGRAM_ERROR THEN
close_cursors;
raise_application_error(-20003
, 'Error in get_6_digit_gov_class ' || SQLERRM);
WHEN STORAGE_ERROR THEN
close_cursors;
raise_application_error(-20004
, 'Error in get_6_digit_gov_class ' || SQLERRM);
WHEN VALUE_ERROR THEN
close_cursors;
raise_application_error(-20005
, 'Error in get_6_digit_gov_class ' || SQLERRM);
WHEN INVALID_CURSOR THEN
close_cursors;
raise_application_error(-20006
, 'Error in get_6_digit_gov_class ' || SQLERRM);
END get_6_digit_gov_class;
/* ===========================================================================
Name : get_max_ph_set_id
Prepared By : leedas
Created : 05/13/2008
Description : Get the max premium history set id
Modification History
--------------------------------------------------------------------------
=========================================================================== */
FUNCTION get_max_ph_set_id(in_policy_num IN policy.policy_num%TYPE
, in_policy_period_eff_date IN policy_period.policy_period_eff_date%TYPE)
RETURN NUMBER
IS
v_prem_hist_set_id NUMBER;
BEGIN
SELECT MAX(phs.prem_hist_set_id)
INTO v_prem_hist_set_id
FROM prem_hist_summary phs
WHERE phs.policy_num = in_policy_num
AND phs.policy_period_eff_date = in_policy_period_eff_date;
RETURN v_prem_hist_set_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0;
WHEN OTHERS THEN
pk73090_error_handler.raise_an_error('get_max_ph_set_id' || SQLERRM);
END;
END pk73059_prem_hist_summary;