create or replace
PACKAGE BODY PK_PER_LIMRA_CAL IS
FUNCTION FN_PER_LIMRA(P_DEC_LAPSE IN NUMBER,
P_DEC_REINSTATE IN NUMBER,
P_DEC_EXPOSURE IN NUMBER) RETURN NUMBER IS
BEGIN
/* REVISE TO ROUND 4 INSTEAD OF ROUND 5 */
IF NVL(P_DEC_EXPOSURE, 0) <= 0 THEN
/* DEC_PERSISTENCY := DEC_DEFAULT_PERSISTENCY ;
ELSE*/
IF P_DEC_LAPSE - P_DEC_REINSTATE > 0 THEN
DEC_PERSISTENCY := 0;
ELSE
DEC_PERSISTENCY := 1;
END IF;
ELSE
DEC_LAPSE_RATE := (P_DEC_LAPSE - P_DEC_REINSTATE) / P_DEC_EXPOSURE;
IF DEC_LAPSE_RATE > 1 THEN
DEC_PERSISTENCY := 0;
ELSIF DEC_LAPSE_RATE < 0 THEN
DEC_PERSISTENCY := 1;
ELSE
--DEC_PERSISTENCY := ROUND(1 - DEC_LAPSE_RATE, 5);
DEC_PERSISTENCY := ROUND(1 - DEC_LAPSE_RATE, 4);
--DEC_PERSISTENCY:= 1- DEC_LAPSE_RATE;
END IF;
END IF;
RETURN(DEC_PERSISTENCY);
EXCEPTION
WHEN OTHERS THEN
RETURN(DEC_DEFAULT_PERSISTENCY);
END;
--AGENT AGENCY LEVEL
--UPDATE BY RAY ON JUN 11 FOR CHANNEL
PROCEDURE SP_PER_LIMRA_CAL00
( P_STR_CMCD IN VARCHAR2,
P_DT_CYCLE_MTH IN DATE,
P_DT_CYCLE_MTH_END IN DATE,
P_STR_LIMRA_TYPE IN VARCHAR2,
/*P_STR_CHANNEL IN VARCHAR2,*/
P_STR_USER IN VARCHAR2
) IS
STR_AGENT_TYPE VARCHAR2(2) := '00';
DEC_START_MTH INT;
--STR_PROD_CAT VARCHAR2(2) := 'LF';
STR_BREAKDOWN_BY CHAR(1) :='N'; --HKG001
DEC_IN_COUNT_P INT; --HKG001
PRC_VAL VARCHAR2(4); --HKG001
BEGIN
--GET THE NB PERIOD FOR EXPOSURE CAL
/*HKG001 START
IF (P_STR_LIMRA_TYPE='LIM19') THEN
DEC_START_MTH:=-20;
ELSIF (P_STR_LIMRA_TYPE='LIM13') THEN
DEC_START_MTH:=-14;
ELSIF (P_STR_LIMRA_TYPE='LIM25') THEN
DEC_START_MTH:=-26;
END IF;
*/
IF (SUBSTR(P_STR_LIMRA_TYPE, 0, 5)='LIM19') THEN
DEC_START_MTH:=-20;
ELSIF (SUBSTR(P_STR_LIMRA_TYPE, 0, 5)='LIM13') THEN
DEC_START_MTH:=-14;
ELSIF (SUBSTR(P_STR_LIMRA_TYPE, 0, 5)='LIM25') THEN
DEC_START_MTH:=-26;
END IF;
--HKG001 END
--HKG001: START
STR_BREAKDOWN_BY := SUBSTR(P_STR_LIMRA_TYPE, 6, 1);
EXECUTE IMMEDIATE 'TRUNCATE TABLE PER_LIMRA_00_TEMP';
IF (STR_BREAKDOWN_BY IS NULL) OR (STR_BREAKDOWN_BY IS NOT NULL AND STR_BREAKDOWN_BY !='V') THEN
INSERT INTO PER_LIMRA_00_TEMP NOLOGGING(CMCD, CYCLE_MTH, AGENT_TYPE,LIMRA_TYPE, CHANNEL, AGT, AGY,
POL_TYPE,
NB_CC, NB_SA, NB_AP, NB_CAP,
REINSTATE_CC, REINSTATE_SA, REINSTATE_AP, REINSTATE_CAP,
LAPSE_CC, LAPSE_SA, LAPSE_AP, LAPSE_CAP,
CREATE_DT, CREATE_BY, UPDATE_DT, UPDATE_BY, EFFECTIVESTART_DT, EFFECTIVEEND_DT)
SELECT P_STR_CMCD AS CMCD, P_DT_CYCLE_MTH AS CYCLE_MTH, STR_AGENT_TYPE, P_STR_LIMRA_TYPE, '' AS CHANNEL, SUP_AGT_AGY.AGT AS AGT, SUP_AGT_AGY.AGY AS AGY,
CASE WHEN STR_BREAKDOWN_BY='P' AND POLNO LIKE 'G%' THEN 'PRC' WHEN STR_BREAKDOWN_BY='P' AND POLNO NOT LIKE 'G%' THEN 'NPRC' WHEN STR_BREAKDOWN_BY='P' AND POLNO IS NULL THEN 'NPRC' --HKG008
ELSE ' ' END AS POL_TYPE, --HKG008
NVL(RAW_DATA.NB_CC,0) NB_CC, NVL(NB_SA,0) NB_SA, NVL(NB_AP,0) NB_AP, NVL(NB_CAP,0) NB_CAP,
NVL(RAW_DATA.REINSTATE_CC,0) REINSTATE_CC, NVL(REINSTATE_SA,0) REINSTATE_SA, NVL(REINSTATE_AP,0) REINSTATE_AP, NVL(REINSTATE_CAP,0) REINSTATE_CAP,
NVL(RAW_DATA.LAPSE_CC,0) LAPSE_CC, NVL(LAPSE_SA, 0) AS LAPSE_SA, NVL(LAPSE_AP, 0) LAPSE_AP, NVL(LAPSE_CAP, 0) LAPSE_CAP,
SYSDATE, P_STR_USER, SYSDATE, P_STR_USER, TRUNC(P_DT_CYCLE_MTH,'MM'), DT_REMOVE_DATE
FROM
(
SELECT DISTINCT AGT, AGY FROM (
--CURRENT MONTH AGENT AGENCY IN PER_LIMRA_POLICY
SELECT AGT, AGY
FROM PER_LIMRA_POLICY
WHERE CMCD = P_STR_CMCD
AND CYCLE_DT >= TRUNC(P_DT_CYCLE_MTH,'MM')
AND CYCLE_DT < P_DT_CYCLE_MTH_END
UNION
--SUPPLY PRIOR 20 MONTHS AGENT AGENCY IN PER_LIMRA
SELECT DISTINCT AGT, AGY
FROM PER_LIMRA
WHERE CMCD = P_STR_CMCD
AND LIMRA_TYPE = P_STR_LIMRA_TYPE
AND AGENT_TYPE = STR_AGENT_TYPE
AND CYCLE_MTH <= ADD_MONTHS(P_DT_CYCLE_MTH, -1)
AND CYCLE_MTH >= ADD_MONTHS(P_DT_CYCLE_MTH, DEC_START_MTH)
UNION
--SUPPLY CURRENT MONTH AGENT AGENCY HIERARCHY IN PER_AGENT_INFO
SELECT AGT, AGY
FROM PER_AGENT_INFO
WHERE CMCD = P_STR_CMCD
AND CYCLE_MTH = P_DT_CYCLE_MTH
AND NVL(TERM_DT, TO_DATE('22000101', 'YYYYMMDD')) >= TRUNC(P_DT_CYCLE_MTH,'MM')
)
) SUP_AGT_AGY
LEFT JOIN
(
--SELECT CMCD, AGY, AGT, POLNO, --HKG001
SELECT CMCD, AGY, AGT, POLNO,
CASE WHEN TRN_TYPE= STR_NB THEN CASE ELSE 0 END AS NB_CC,
CASE WHEN TRN_TYPE= STR_NB THEN SA_AMT ELSE 0 END AS NB_SA,
CASE WHEN TRN_TYPE= STR_NB THEN ANN_PREM ELSE 0 END AS NB_AP,
CASE WHEN TRN_TYPE= STR_NB THEN CAP_ANN_PREM ELSE 0 END AS NB_CAP,
-- CASE WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE LIKE 'LIM13%' AND TRNCD = '65' THEN PER_LIMRA_POLICY_CAP.CASE --HKG002
-- WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE LIKE 'LIM19%' AND TRNCD IN ('65','66') THEN PER_LIMRA_POLICY_CAP.CASE --HKG002
-- WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE LIKE 'LIM25%' AND TRNCD IN ('65','66','67','68') THEN PER_LIMRA_POLICY_CAP.CASE --HKG002
CASE WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE = 'LIM13' AND TRNCD = '65' THEN PER_LIMRA_POLICY_CAP.CASE --HKG002
WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE = 'LIM19' AND TRNCD IN ('65','66') THEN PER_LIMRA_POLICY_CAP.CASE --HKG002
--WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE = 'LIM13P' AND TRNCD IN ('61','65') THEN PER_LIMRA_POLICY_CAP.CASE --HKG002 --HKG003
--WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE = 'LIM19P' AND TRNCD IN ('61','62','65','66') THEN PER_LIMRA_POLICY_CAP.CASE --HKG002 --HKG003
--WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE IN ('LIM13P','LIM13N') AND TRNCD IN ('61','65') THEN PER_LIMRA_POLICY_CAP.CASE --HKG003 --HKG006 --HKG009
-- WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE IN ('LIM19P','LIM19N') AND TRNCD IN ('61','62','65','66') THEN PER_LIMRA_POLICY_CAP.CASE --HKG003 --HKG006 --HKG009
-- WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE LIKE 'LIM25%' AND TRNCD IN ('61','62','63','65','66','67') THEN PER_LIMRA_POLICY_CAP.CASE --HKG002 --HKG009
WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE = 'LIM13P' AND TRNCD = '65' THEN PER_LIMRA_POLICY_CAP.CASE --HKG003 --HKG006 --HKG009
WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE = 'LIM19P' AND TRNCD IN ('65','66') THEN PER_LIMRA_POLICY_CAP.CASE --HKG003 --HKG006 --HKG009
WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE LIKE 'LIM25%' AND TRNCD IN ('65','66','67') THEN PER_LIMRA_POLICY_CAP.CASE --HKG002 --HKG009
ELSE 0
END AS LAPSE_CC,
-- CASE WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE LIKE 'LIM13%' AND TRNCD = '65' THEN SA_AMT --HKG002
-- WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE LIKE 'LIM19%' AND TRNCD IN ('65','66') THEN SA_AMT --HKG002
-- WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE LIKE 'LIM25%' AND TRNCD IN ('65','66','67','68') THEN SA_AMT --HKG002
CASE WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE = 'LIM13' AND TRNCD = '65' THEN SA_AMT --HKG002
WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE = 'LIM19' AND TRNCD IN ('65','66') THEN SA_AMT --HKG002
--WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE = 'LIM13P' AND TRNCD IN ('61','65') THEN SA_AMT --HKG002 --HKG003
--WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE = 'LIM19P' AND TRNCD IN ('61','62','65','66') THEN SA_AMT --HKG002 --HKG003
--WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE IN ('LIM13P','LIM13N') AND TRNCD IN ('61','65') THEN SA_AMT --HKG003 --HKG006 --HKG009
-- WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE IN ('LIM19P','LIM19N') AND TRNCD IN ('61','62','65','66') THEN SA_AMT --HKG003 --HKG006 --HKG009
-- WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE LIKE 'LIM25%' AND TRNCD IN ('61','62','63','65','66','67') THEN SA_AMT --HKG002 --HKG009
WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE = 'LIM13P' AND TRNCD = '65' THEN SA_AMT --HKG003 --HKG006 --HKG009
WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE = 'LIM19P' AND TRNCD IN ('65','66') THEN SA_AMT --HKG003 --HKG006 --HKG009
WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE LIKE 'LIM25%' AND TRNCD IN ('65','66','67') THEN SA_AMT --HKG002 --HKG009
ELSE 0
END AS LAPSE_SA,
-- CASE WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE LIKE 'LIM13%' AND TRNCD = '65' THEN ANN_PREM --HKG002
-- WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE LIKE 'LIM19%' AND TRNCD IN ('65','66') THEN ANN_PREM --HKG002
-- WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE LIKE 'LIM25%' AND TRNCD IN ('65','66','67','68') THEN ANN_PREM --HKG002
CASE WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE = 'LIM13' AND TRNCD = '65' THEN ANN_PREM --HKG002
WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE = 'LIM19' AND TRNCD IN ('65','66') THEN ANN_PREM --HKG002
--WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE = 'LIM13P' AND TRNCD IN ('61','65') THEN ANN_PREM --HKG002 --HKG003
--WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE = 'LIM19P' AND TRNCD IN ('61','62','65','66') THEN ANN_PREM --HKG002 --HKG003
--WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE IN ('LIM13P','LIM13N') AND TRNCD IN ('61','65') THEN ANN_PREM --HKG003 --HKG006 --HKG009
-- WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE IN ('LIM19P','LIM19N') AND TRNCD IN ('61','62','65','66') THEN ANN_PREM --HKG003 --HKG006 --HKG009
-- WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE LIKE 'LIM25%' AND TRNCD IN ('61','62','63','65','66','67') THEN ANN_PREM --HKG002 --HKG009
WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE = 'LIM13P' AND TRNCD = '65' THEN ANN_PREM --HKG003 --HKG006 --HKG009
WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE = 'LIM19P' AND TRNCD IN ('65','66') THEN ANN_PREM --HKG003 --HKG006 --HKG009
WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE LIKE 'LIM25%' AND TRNCD IN ('65','66','67') THEN ANN_PREM --HKG002 --HKG009
ELSE 0
END AS LAPSE_AP,
-- CASE WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE LIKE 'LIM13%' AND TRNCD = '65' THEN CAP_ANN_PREM --HKG002
-- WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE LIKE 'LIM19%' AND TRNCD IN ('65','66') THEN CAP_ANN_PREM --HKG002
-- WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE LIKE 'LIM25%' AND TRNCD IN ('65','66','67','68') THEN CAP_ANN_PREM --HKG002
CASE WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE = 'LIM13' AND TRNCD = '65' THEN CAP_ANN_PREM --HKG002
WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE = 'LIM19' AND TRNCD IN ('65','66') THEN CAP_ANN_PREM --HKG002
--WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE = 'LIM13P' AND TRNCD IN ('61','65') THEN CAP_ANN_PREM --HKG002 --HKG003
--WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE = 'LIM19P' AND TRNCD IN ('61','62','65','66') THEN CAP_ANN_PREM --HKG002 --HKG003
--WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE IN ('LIM13P','LIM13N') AND TRNCD IN ('61','65') THEN CAP_ANN_PREM --HKG003 --HKG006 --HKG009
-- WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE IN ('LIM19P','LIM19N') AND TRNCD IN ('61','62','65','66') THEN CAP_ANN_PREM --HKG003 --HKG006 --HKG009
-- WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE LIKE 'LIM25%' AND TRNCD IN ('61','62','63','65','66','67') THEN CAP_ANN_PREM --HKG002 --HKG009
WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE = 'LIM13P' AND TRNCD = '65' THEN CAP_ANN_PREM --HKG003 --HKG006 --HKG009
WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE = 'LIM19P' AND TRNCD IN ('65','66') THEN CAP_ANN_PREM --HKG003 --HKG006 --HKG009
WHEN TRN_TYPE= STR_LAPSE AND P_STR_LIMRA_TYPE LIKE 'LIM25%' AND TRNCD IN ('65','66','67') THEN CAP_ANN_PREM --HKG002 --HKG009
ELSE 0
END AS LAPSE_CAP,
-- CASE WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE LIKE 'LIM13%' AND TRNCD = '56' THEN PER_LIMRA_POLICY_CAP.CASE --HKG002
-- WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE LIKE 'LIM19%' AND TRNCD IN ('56','96') THEN PER_LIMRA_POLICY_CAP.CASE --HKG002
-- WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE LIKE 'LIM25%' AND TRNCD IN ('56','96','97','98') THEN PER_LIMRA_POLICY_CAP.CASE --HKG002
CASE WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE = 'LIM13' AND TRNCD = '56' THEN PER_LIMRA_POLICY_CAP.CASE --HKG002
WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE = 'LIM19' AND TRNCD IN ('56','96') THEN PER_LIMRA_POLICY_CAP.CASE --HKG002
--WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE = 'LIM13P' AND TRNCD IN ('56','91') THEN PER_LIMRA_POLICY_CAP.CASE --HKG002 --HKG003
--WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE = 'LIM19P' AND TRNCD IN ('56','91','92','96') THEN PER_LIMRA_POLICY_CAP.CASE --HKG002 --HKG003
--WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE IN ('LIM13P','LIM13N') AND TRNCD IN ('56','91') THEN PER_LIMRA_POLICY_CAP.CASE --HKG003 --HKG006 --HKG009
-- WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE IN ('LIM19P','LIM19N') AND TRNCD IN ('56','91','92','96') THEN PER_LIMRA_POLICY_CAP.CASE --HKG003 --HKG006 --HKG009
-- WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE LIKE 'LIM25%' AND TRNCD IN ('56','91','92','93','96','97') THEN PER_LIMRA_POLICY_CAP.CASE --HKG002 --HKG009
WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE = 'LIM13P' AND TRNCD = '56' THEN PER_LIMRA_POLICY_CAP.CASE --HKG003 --HKG006 --HKG009
WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE = 'LIM19P' AND TRNCD IN ('56','96') THEN PER_LIMRA_POLICY_CAP.CASE --HKG003 --HKG006 --HKG009
WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE LIKE 'LIM25%' AND TRNCD IN ('56','96','97') THEN PER_LIMRA_POLICY_CAP.CASE --HKG002 --HKG009
ELSE 0
END AS REINSTATE_CC,
-- CASE WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE LIKE 'LIM13%' AND TRNCD = '56' THEN SA_AMT --HKG002
-- WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE LIKE 'LIM19%' AND TRNCD IN ('56','96') THEN SA_AMT --HKG002
-- WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE LIKE 'LIM25%' AND TRNCD IN ('56','96','97','98') THEN SA_AMT --HKG002
CASE WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE = 'LIM13' AND TRNCD = '56' THEN SA_AMT --HKG002
WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE = 'LIM19' AND TRNCD IN ('56','96') THEN SA_AMT --HKG002
--WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE = 'LIM13P' AND TRNCD IN ('56','91') THEN SA_AMT --HKG002 --HKG003
--WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE = 'LIM19P' AND TRNCD IN ('56','91','92','96') THEN SA_AMT --HKG002 --HKG003
--WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE IN ('LIM13P','LIM13N') AND TRNCD IN ('56','91') THEN SA_AMT --HKG003 --HKG006 --HKG009
-- WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE IN ('LIM19P','LIM19N') AND TRNCD IN ('56','91','92','96') THEN SA_AMT --HKG003 --HKG006 --HKG009
-- WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE LIKE 'LIM25%' AND TRNCD IN ('56','91','92','93','96','97') THEN SA_AMT --HKG002 --HKG009
WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE = 'LIM13P' AND TRNCD = '56' THEN SA_AMT --HKG003 --HKG006 --HKG009
WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE = 'LIM19P' AND TRNCD IN ('56','96') THEN SA_AMT --HKG003 --HKG006 --HKG009
WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE LIKE 'LIM25%' AND TRNCD IN ('56','96','97') THEN SA_AMT --HKG002 --HKG009
ELSE 0
END AS REINSTATE_SA,
-- CASE WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE LIKE 'LIM13%' AND TRNCD = '56' THEN ANN_PREM --HKG002
-- WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE LIKE 'LIM19%' AND TRNCD IN ('56','96') THEN ANN_PREM --HKG002
-- WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE LIKE 'LIM25%' AND TRNCD IN ('56','96','97','98') THEN ANN_PREM --HKG002
CASE WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE = 'LIM13' AND TRNCD = '56' THEN ANN_PREM --HKG002
WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE = 'LIM19' AND TRNCD IN ('56','96') THEN ANN_PREM --HKG002
--WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE = 'LIM13P' AND TRNCD IN ('56','91') THEN ANN_PREM --HKG002 --HKG003
--WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE = 'LIM19P' AND TRNCD IN ('56','91','92','96') THEN ANN_PREM --HKG002 --HKG003
--WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE IN ('LIM13P','LIM13N') AND TRNCD IN ('56','91') THEN ANN_PREM --HKG003 --HKG006 --HKG009
-- WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE IN ('LIM19P','LIM19N') AND TRNCD IN ('56','91','92','96') THEN ANN_PREM --HKG003 --HKG006 --HKG009
-- WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE LIKE 'LIM25%' AND TRNCD IN ('56','91','92','93','96','97') THEN ANN_PREM --HKG002 --HKG009
WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE = 'LIM13P' AND TRNCD = '56' THEN ANN_PREM --HKG003 --HKG006 --HKG009
WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE = 'LIM19P' AND TRNCD IN ('56','96') THEN ANN_PREM --HKG003 --HKG006 --HKG009
WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE LIKE 'LIM25%' AND TRNCD IN ('56','96','97') THEN ANN_PREM --HKG002 --HKG009
ELSE 0
END AS REINSTATE_AP,
-- CASE WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE LIKE 'LIM13%' AND TRNCD = '56' THEN CAP_ANN_PREM --HKG002
-- WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE LIKE 'LIM19%' AND TRNCD IN ('56','96') THEN CAP_ANN_PREM --HKG002
-- WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE LIKE 'LIM25%' AND TRNCD IN ('56','96','97','98') THEN CAP_ANN_PREM --HKG002
CASE WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE = 'LIM13' AND TRNCD = '56' THEN CAP_ANN_PREM --HKG002
WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE = 'LIM19' AND TRNCD IN ('56','96') THEN CAP_ANN_PREM --HKG002
--WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE = 'LIM13P' AND TRNCD IN ('56','91') THEN CAP_ANN_PREM --HKG002
-- WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE = 'LIM19P' AND TRNCD IN ('56','91','92','96') THEN CAP_ANN_PREM --HKG002
--WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE IN ('LIM13P','LIM13N') AND TRNCD IN ('56','91') THEN CAP_ANN_PREM --HKG002 --HKG003 --HKG006 --HKG009
-- WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE IN ('LIM19P','LIM19N') AND TRNCD IN ('56','91','92','96') THEN CAP_ANN_PREM --HKG002 --HKG003 --HKG006 --HKG009
-- WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE LIKE 'LIM25%' AND TRNCD IN ('56','91','92','93','96','97') THEN CAP_ANN_PREM --HKG002 --HKG009
WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE = 'LIM13P' AND TRNCD = '56' THEN CAP_ANN_PREM --HKG002 --HKG003 --HKG006 --HKG009
WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE = 'LIM19P' AND TRNCD IN ('56','96') THEN CAP_ANN_PREM --HKG002 --HKG003 --HKG006 --HKG009
WHEN TRN_TYPE= STR_REINSTATE AND P_STR_LIMRA_TYPE LIKE 'LIM25%' AND TRNCD IN ('56','96','97') THEN CAP_ANN_PREM --HKG002 --HKG009
ELSE 0
END AS REINSTATE_CAP
FROM PER_LIMRA_POLICY_CAP
WHERE
CYCLE_DT >= TRUNC(P_DT_CYCLE_MTH,'MM')
AND CYCLE_DT < P_DT_CYCLE_MTH_END
AND TRN_TYPE IN (STR_NB, STR_LAPSE, STR_REINSTATE)
AND CMCD=P_STR_CMCD
) RAW_DATA
ON SUP_AGT_AGY.AGT = RAW_DATA.AGT
AND SUP_AGT_AGY.AGY = RAW_DATA.AGY;
END IF;
INSERT INTO PER_LIMRA NOLOGGING(CMCD, CYCLE_MTH, AGENT_TYPE,LIMRA_TYPE, CHANNEL, AGT, AGY, POL_TYPE,
NB_CC_CM, NB_SA_CM, NB_AP_CM, NB_CAP_CM,
REINSTATE_CC_CM, REINSTATE_SA_CM, REINSTATE_AP_CM, REINSTATE_CAP_CM,
LAPSE_CC_CM, LAPSE_SA_CM, LAPSE_AP_CM, LAPSE_CAP_CM,
CREATE_DT, CREATE_BY, UPDATE_DT, UPDATE_BY, EFFECTIVESTART_DT, EFFECTIVEEND_DT)
SELECT CMCD, CYCLE_MTH, AGENT_TYPE,LIMRA_TYPE, CHANNEL, AGT, AGY, POL_TYPE,
SUM(NB_CC) NB_CC_CM, SUM(NB_SA) NB_SA_CM, SUM(NB_AP) NB_AP_CM, SUM(NB_CAP) NB_CAP_CM,
SUM(REINSTATE_CC) REINSTATE_CC_CM, SUM(REINSTATE_SA) REINSTATE_SA_CM, SUM(REINSTATE_AP) REINSTATE_AP_CM, SUM(REINSTATE_CAP) REINSTATE_CAP_CM,
SUM(LAPSE_CC) LAPSE_CC_CM, SUM(LAPSE_SA) LAPSE_SA_CM, SUM(LAPSE_AP) LAPSE_AP_CM, SUM(LAPSE_CAP) LAPSE_CAP_CM,
CREATE_DT, CREATE_BY, UPDATE_DT, UPDATE_BY, EFFECTIVESTART_DT, EFFECTIVEEND_DT
FROM PER_LIMRA_00_TEMP
GROUP BY CMCD, CYCLE_MTH, AGENT_TYPE,LIMRA_TYPE, CHANNEL, AGT, AGY, POL_TYPE,CREATE_DT, CREATE_BY, UPDATE_DT, UPDATE_BY, EFFECTIVESTART_DT, EFFECTIVEEND_DT;
--TO CREATE DUMMY PAIRING RECORD FOR SINGLE ENTRY IF NEED BREAKDOWN
--IF STR_BREAKDOWN_BY IS NOT NULL THEN --HKG006
IF STR_BREAKDOWN_BY IS NOT NULL AND STR_BREAKDOWN_BY !='N' THEN --HKG006
EXECUTE IMMEDIATE 'TRUNCATE TABLE PER_LIMRA_TMP';
INSERT INTO PER_LIMRA_TMP NOLOGGING(
CMCD, CYCLE_MTH, AGENT_TYPE,LIMRA_TYPE, CHANNEL, AGT, AGY, POL_TYPE,
NB_CC_CM, NB_SA_CM, NB_AP_CM, NB_CAP_CM,
REINSTATE_CC_CM, REINSTATE_SA_CM, REINSTATE_AP_CM, REINSTATE_CAP_CM,
LAPSE_CC_CM, LAPSE_SA_CM, LAPSE_AP_CM, LAPSE_CAP_CM,
CREATE_DT, CREATE_BY, UPDATE_DT, UPDATE_BY, EFFECTIVESTART_DT, EFFECTIVEEND_DT
)
SELECT CMCD, CYCLE_MTH, AGENT_TYPE,LIMRA_TYPE, CHANNEL, AGT, AGY, MAX(POL_TYPE),
0,0,0,0,
0,0,0,0,
0,0,0,0,
SYSDATE, P_STR_USER, SYSDATE, P_STR_USER, TRUNC(P_DT_CYCLE_MTH,'MM'), DT_REMOVE_DATE
FROM PER_LIMRA
WHERE CMCD=P_STR_CMCD AND CYCLE_MTH= P_DT_CYCLE_MTH AND AGENT_TYPE = STR_AGENT_TYPE AND LIMRA_TYPE= P_STR_LIMRA_TYPE
GROUP BY CMCD, CYCLE_MTH, AGENT_TYPE,LIMRA_TYPE, CHANNEL, AGT, AGY
HAVING COUNT(POL_TYPE) = 1;
INSERT INTO PER_LIMRA NOLOGGING(CMCD, CYCLE_MTH, AGENT_TYPE,LIMRA_TYPE, CHANNEL, AGT, AGY,
POL_TYPE,
NB_CC_CM, NB_SA_CM, NB_AP_CM, NB_CAP_CM,
REINSTATE_CC_CM, REINSTATE_SA_CM, REINSTATE_AP_CM, REINSTATE_CAP_CM,
LAPSE_CC_CM, LAPSE_SA_CM, LAPSE_AP_CM, LAPSE_CAP_CM,
CREATE_DT, CREATE_BY, UPDATE_DT, UPDATE_BY, EFFECTIVESTART_DT, EFFECTIVEEND_DT)
SELECT CMCD, CYCLE_MTH, AGENT_TYPE,LIMRA_TYPE, CHANNEL, AGT, AGY,
--HKG009
--CASE WHEN POL_TYPE='NPRC' THEN 'PRC' WHEN POL_TYPE='PRC' THEN 'NPRC' WHEN POL_TYPE='NVIT' THEN 'VIT' WHEN POL_TYPE='VIT' THEN 'NVIT' ELSE POL_TYPE END AS POL_TYPE,
CASE WHEN POL_TYPE='NPRC' THEN 'PRC' WHEN POL_TYPE='PRC' THEN 'NPRC' ELSE POL_TYPE END AS POL_TYPE,
--HKG009
NB_CC_CM, NB_SA_CM, NB_AP_CM,NB_CAP_CM,
REINSTATE_CC_CM, REINSTATE_SA_CM, REINSTATE_AP_CM, REINSTATE_CAP_CM,
LAPSE_CC_CM, LAPSE_SA_CM, LAPSE_AP_CM, LAPSE_CAP_CM,
CREATE_DT, CREATE_BY, UPDATE_DT, UPDATE_BY, EFFECTIVESTART_DT, EFFECTIVEEND_DT
FROM PER_LIMRA_TMP;
END IF;
--HKG001:END
UPDATE PER_LIMRA T SET T.CHANNEL = (
SELECT AGY_INFO.CHANNEL
FROM PER_AGENCY_INFO AGY_INFO
WHERE AGY_INFO.CMCD = P_STR_CMCD
AND AGY_INFO.CYCLE_MTH = P_DT_CYCLE_MTH
AND UPPER(AGY_INFO.AGY_TYPE) = 'AGY'
AND AGY_INFO.AGY = T.AGY)
WHERE T.CMCD = P_STR_CMCD
AND T.CYCLE_MTH = P_DT_CYCLE_MTH
AND T.AGENT_TYPE = STR_AGENT_TYPE
AND T.LIMRA_TYPE = P_STR_LIMRA_TYPE;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, 'SP_PER_LIMRA_CAL00 ERROR '||SQLERRM);
END;
PROCEDURE SP_PER_LIMRA_CAL01
( P_STR_CMCD IN VARCHAR2,
P_DT_CYCLE_MTH IN DATE,
P_DT_CYCLE_MTH_END IN DATE,
P_STR_LIMRA_TYPE IN VARCHAR2,
/*P_STR_CHANNEL IN VARCHAR2,*/
P_STR_USER IN VARCHAR2
) IS
STR_AGENT_TYPE VARCHAR2(2) := '01';
STR_AGENCY_TYPE VARCHAR2(5) := 'AGY';
STR_BREAKDOWN_BY CHAR(1) :='N'; --HKG001
BEGIN
--UPDATE AGENT_TYPE 01 TO
--BY AGENT CODE
INSERT INTO PER_LIMRA NOLOGGING( CMCD, CYCLE_MTH, AGENT_TYPE, LIMRA_TYPE, CHANNEL, AGT, AGY, POL_TYPE, --HKG001
DISTRICT,CONTRACT_DT, TERM_DT,
NB_CC_CM, NB_SA_CM, NB_AP_CM, NB_CAP_CM,
LAPSE_CC_CM, LAPSE_SA_CM, LAPSE_AP_CM, LAPSE_CAP_CM,
REINSTATE_CC_CM, REINSTATE_SA_CM, REINSTATE_AP_CM, REINSTATE_CAP_CM,
EXP_CC_CM, EXP_SA_CM, EXP_AP_CM, EXP_CAP_CM,
LAPSE_CC_P12, LAPSE_SA_P12, LAPSE_AP_P12, LAPSE_CAP_P12,
REINSTATE_CC_P12, REINSTATE_SA_P12, REINSTATE_AP_P12, REINSTATE_CAP_P12,
EXP_CC_P12, EXP_SA_P12, EXP_AP_P12, EXP_CAP_P12,
LAPSE_CC_YTD, LAPSE_SA_YTD, LAPSE_AP_YTD, LAPSE_CAP_YTD,
REINSTATE_CC_YTD, REINSTATE_SA_YTD, REINSTATE_AP_YTD, REINSTATE_CAP_YTD,
EXP_CC_YTD, EXP_SA_YTD, EXP_AP_YTD, EXP_CAP_YTD,
CREATE_DT, CREATE_BY, UPDATE_DT, UPDATE_BY, EFFECTIVESTART_DT, EFFECTIVEEND_DT)
SELECT P_STR_CMCD, P_DT_CYCLE_MTH, STR_AGENT_TYPE, P_STR_LIMRA_TYPE, AGT_INFO.CHANNEL, AGT_INFO.AGT,AGT_INFO.AGY, CASE WHEN POL_TYPE IS NULL THEN ' ' ELSE POL_TYPE END AS POL_TYPE, --HKG001
AGT_INFO.DISTRICT, AGT_INFO.ACTIVE_DT, AGT_INFO.TERM_DT,
NVL(NB_CC_CM,0), NVL(NB_SA_CM,0), NVL(NB_AP_CM,0), NVL(NB_CAP_CM,0),
NVL(LAPSE_CC_CM,0), NVL(LAPSE_SA_CM,0), NVL(LAPSE_AP_CM,0), NVL(LAPSE_CAP_CM,0),
NVL(REINSTATE_CC_CM,0), NVL(REINSTATE_SA_CM,0), NVL(REINSTATE_AP_CM,0), NVL(REINSTATE_CAP_CM,0),
NVL(EXP_CC_CM,0), NVL(EXP_SA_CM,0), NVL(EXP_AP_CM,0), NVL(EXP_CAP_CM,0),
NVL(LAPSE_CC_P12,0), NVL(LAPSE_SA_P12,0), NVL(LAPSE_AP_P12,0), NVL(LAPSE_CAP_P12,0),
NVL(REINSTATE_CC_P12,0), NVL(REINSTATE_SA_P12,0), NVL(REINSTATE_AP_P12,0), NVL(REINSTATE_CAP_P12,0),
NVL(EXP_CC_P12,0), NVL(EXP_SA_P12,0), NVL(EXP_AP_P12,0), NVL(EXP_CAP_P12,0),
NVL(LAPSE_CC_YTD,0), NVL(LAPSE_SA_YTD,0), NVL(LAPSE_AP_YTD,0), NVL(LAPSE_CAP_YTD,0),
NVL(REINSTATE_CC_YTD,0), NVL(REINSTATE_SA_YTD,0), NVL(REINSTATE_AP_YTD,0), NVL(REINSTATE_CAP_YTD,0),
NVL(EXP_CC_YTD,0), NVL(EXP_SA_YTD,0), NVL(EXP_AP_YTD,0), NVL(EXP_CAP_YTD,0),
SYSDATE, P_STR_USER, SYSDATE, P_STR_USER, TRUNC(P_DT_CYCLE_MTH,'MM'), DT_REMOVE_DATE FROM
(
SELECT A.AGT, A.AGY, A.ACTIVE_DT, A.TERM_DT, B.CHANNEL, B.DISTRICT
FROM PER_AGENT_INFO A, PER_AGENCY_INFO B
WHERE A.AGY = B.AGY(+)
AND A.CMCD = P_STR_CMCD
/*AND A.CHANNEL = P_STR_CHANNEL*/
AND A.CYCLE_MTH = P_DT_CYCLE_MTH
AND B.CMCD = P_STR_CMCD
/*AND B.CHANNEL = P_STR_CHANNEL*/
AND B.AGY_TYPE = STR_AGENCY_TYPE
AND B.CYCLE_MTH = P_DT_CYCLE_MTH
) AGT_INFO LEFT JOIN
(
SELECT AGT, POL_TYPE, --HKG001
SUM(NB_CC_CM) AS NB_CC_CM , SUM(NB_SA_CM) AS NB_SA_CM, SUM(NB_AP_CM) AS NB_AP_CM, SUM(NB_CAP_CM) AS NB_CAP_CM,
SUM(REINSTATE_CC_CM) AS REINSTATE_CC_CM, SUM(REINSTATE_SA_CM) AS REINSTATE_SA_CM, SUM(REINSTATE_AP_CM) AS REINSTATE_AP_CM, SUM(REINSTATE_CAP_CM) AS REINSTATE_CAP_CM,
SUM(LAPSE_CC_CM) AS LAPSE_CC_CM, SUM(LAPSE_SA_CM) AS LAPSE_SA_CM, SUM(LAPSE_AP_CM) AS LAPSE_AP_CM, SUM(LAPSE_CAP_CM) AS LAPSE_CAP_CM,
SUM(EXP_CC_CM) AS EXP_CC_CM, SUM(EXP_SA_CM) AS EXP_SA_CM, SUM(EXP_AP_CM) AS EXP_AP_CM, SUM(EXP_CAP_CM) AS EXP_CAP_CM,
SUM(LAPSE_CC_P12) AS LAPSE_CC_P12, SUM(LAPSE_SA_P12) AS LAPSE_SA_P12 , SUM(LAPSE_AP_P12) AS LAPSE_AP_P12, SUM(LAPSE_CAP_P12) AS LAPSE_CAP_P12,
SUM(REINSTATE_CC_P12) AS REINSTATE_CC_P12, SUM(REINSTATE_SA_P12) AS REINSTATE_SA_P12, SUM(REINSTATE_AP_P12) AS REINSTATE_AP_P12, SUM(REINSTATE_CAP_P12) AS REINSTATE_CAP_P12,
SUM(EXP_CC_P12) AS EXP_CC_P12, SUM(EXP_SA_P12) AS EXP_SA_P12, SUM(EXP_AP_P12) AS EXP_AP_P12, SUM(EXP_CAP_P12) AS EXP_CAP_P12,
SUM(LAPSE_CC_YTD) AS LAPSE_CC_YTD, SUM(LAPSE_SA_YTD) AS LAPSE_SA_YTD, SUM(LAPSE_AP_YTD) AS LAPSE_AP_YTD, SUM(LAPSE_CAP_YTD) AS LAPSE_CAP_YTD,
SUM(REINSTATE_CC_YTD) AS REINSTATE_CC_YTD, SUM(REINSTATE_SA_YTD) AS REINSTATE_SA_YTD, SUM(REINSTATE_AP_YTD) AS REINSTATE_AP_YTD, SUM(REINSTATE_CAP_YTD) AS REINSTATE_CAP_YTD,
SUM(EXP_CC_YTD) AS EXP_CC_YTD, SUM(EXP_SA_YTD) AS EXP_SA_YTD, SUM(EXP_AP_YTD) AS EXP_AP_YTD, SUM(EXP_CAP_YTD) AS EXP_CAP_YTD
FROM PER_LIMRA
WHERE CMCD=P_STR_CMCD AND CYCLE_MTH= P_DT_CYCLE_MTH
AND AGENT_TYPE = '00'
AND LIMRA_TYPE= P_STR_LIMRA_TYPE
/*AND CHANNEL = P_STR_CHANNEL*/
--GROUP BY AGT)SRC_CM --HKG001
GROUP BY AGT, POL_TYPE)SRC_CM --HKG001
ON AGT_INFO.AGT = SRC_CM.AGT;
--AND AGT_INFO.CHANNEL = SRC_CM.CHANNEL;
--COMMIT; --HKG001
--HKG001 START: PREPARE DUMMY PAIRS IF NEED BREAKDOWN
STR_BREAKDOWN_BY := SUBSTR(P_STR_LIMRA_TYPE, 6, 1);
--IF STR_BREAKDOWN_BY IS NOT NULL THEN --HKG006
IF STR_BREAKDOWN_BY IS NOT NULL AND STR_BREAKDOWN_BY !='N' THEN --HKG006
EXECUTE IMMEDIATE 'TRUNCATE TABLE PER_LIMRA_TMP';
INSERT INTO PER_LIMRA_TMP NOLOGGING
SELECT * FROM PER_LIMRA
WHERE CMCD=P_STR_CMCD AND CYCLE_MTH= P_DT_CYCLE_MTH AND AGENT_TYPE = STR_AGENT_TYPE AND LIMRA_TYPE= P_STR_LIMRA_TYPE AND POL_TYPE=' ';
IF STR_BREAKDOWN_BY = 'P' THEN
UPDATE PER_LIMRA SET POL_TYPE='NPRC'
WHERE CMCD=P_STR_CMCD AND CYCLE_MTH= P_DT_CYCLE_MTH AND AGENT_TYPE = STR_AGENT_TYPE AND LIMRA_TYPE= P_STR_LIMRA_TYPE AND POL_TYPE=' ';
--ELSE --HKG009
-- UPDATE PER_LIMRA SET POL_TYPE='NVIT' --HKG009
-- WHERE CMCD=P_STR_CMCD AND CYCLE_MTH= P_DT_CYCLE_MTH AND AGENT_TYPE = STR_AGENT_TYPE AND LIMRA_TYPE= P_STR_LIMRA_TYPE AND POL_TYPE=' '; --HKG009
END IF;
INSERT INTO PER_LIMRA NOLOGGING( CMCD, CYCLE_MTH, AGENT_TYPE, LIMRA_TYPE, CHANNEL, AGT, AGY, POL_TYPE, DISTRICT,CONTRACT_DT, TERM_DT,
NB_CC_CM, NB_SA_CM, NB_AP_CM, NB_CAP_CM,
LAPSE_CC_CM, LAPSE_SA_CM, LAPSE_AP_CM, LAPSE_CAP_CM,
REINSTATE_CC_CM, REINSTATE_SA_CM, REINSTATE_AP_CM, REINSTATE_CAP_CM,
EXP_CC_CM, EXP_SA_CM, EXP_AP_CM, EXP_CAP_CM,
LAPSE_CC_P12, LAPSE_SA_P12, LAPSE_AP_P12, LAPSE_CAP_P12,
REINSTATE_CC_P12, REINSTATE_SA_P12, REINSTATE_AP_P12, REINSTATE_CAP_P12,
EXP_CC_P12, EXP_SA_P12, EXP_AP_P12, EXP_CAP_P12,
LAPSE_CC_YTD, LAPSE_SA_YTD, LAPSE_AP_YTD, LAPSE_CAP_YTD,
REINSTATE_CC_YTD, REINSTATE_SA_YTD, REINSTATE_AP_YTD, REINSTATE_CAP_YTD,
EXP_CC_YTD, EXP_SA_YTD, EXP_AP_YTD, EXP_CAP_YTD,
CREATE_DT, CREATE_BY, UPDATE_DT, UPDATE_BY, EFFECTIVESTART_DT, EFFECTIVEEND_DT)
--SELECT CMCD, CYCLE_MTH, AGENT_TYPE, LIMRA_TYPE, CHANNEL, AGT, AGY, CASE WHEN STR_BREAKDOWN_BY='P' THEN 'PRC' ELSE 'VIT' END AS POL_TYPE, DISTRICT,CONTRACT_DT, TERM_DT, --HKG009
SELECT CMCD, CYCLE_MTH, AGENT_TYPE, LIMRA_TYPE, CHANNEL, AGT, AGY, 'PRC' AS POL_TYPE, DISTRICT,CONTRACT_DT, TERM_DT, --HKG009
NB_CC_CM, NB_SA_CM, NB_AP_CM, NB_CAP_CM,
LAPSE_CC_CM, LAPSE_SA_CM, LAPSE_AP_CM, LAPSE_CAP_CM,
REINSTATE_CC_CM, REINSTATE_SA_CM, REINSTATE_AP_CM, REINSTATE_CAP_CM,
EXP_CC_CM, EXP_SA_CM, EXP_AP_CM, EXP_CAP_CM,
LAPSE_CC_P12, LAPSE_SA_P12, LAPSE_AP_P12, LAPSE_CAP_P12,
REINSTATE_CC_P12, REINSTATE_SA_P12, REINSTATE_AP_P12, REINSTATE_CAP_P12,
EXP_CC_P12, EXP_SA_P12, EXP_AP_P12, EXP_CAP_P12,
LAPSE_CC_YTD, LAPSE_SA_YTD, LAPSE_AP_YTD, LAPSE_CAP_YTD,
REINSTATE_CC_YTD, REINSTATE_SA_YTD, REINSTATE_AP_YTD, REINSTATE_CAP_YTD,
EXP_CC_YTD, EXP_SA_YTD, EXP_AP_YTD, EXP_CAP_YTD,
CREATE_DT, CREATE_BY, UPDATE_DT, UPDATE_BY, EFFECTIVESTART_DT, EFFECTIVEEND_DT
FROM PER_LIMRA_TMP;
END IF;
COMMIT;
--HKG001 END
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, 'SP_PER_LIMRA_CAL01 ERROR '||SQLERRM);
END;
--FOR AGENCY LEVEL PERSISTENCY
PROCEDURE SP_PER_LIMRA_CAL02
( P_STR_CMCD IN VARCHAR2,
P_DT_CYCLE_MTH IN DATE,
P_DT_CYCLE_MTH_END IN DATE,
P_STR_LIMRA_TYPE IN VARCHAR2,
/*P_STR_CHANNEL IN VARCHAR2,*/
P_STR_USER IN VARCHAR2
) IS
STR_AGENT_TYPE VARCHAR2(2) := '02';
STR_AGENCY_TYPE VARCHAR2(5) := 'AGY';
STR_BREAKDOWN_BY CHAR(1) :='N'; --HKG001
BEGIN
INSERT INTO PER_LIMRA NOLOGGING(CMCD, CYCLE_MTH, AGENT_TYPE,LIMRA_TYPE, CHANNEL, AGT, AGY, DISTRICT, POL_TYPE, --HKG001
CONTRACT_DT, TERM_DT,
NB_CC_CM, NB_SA_CM, NB_AP_CM, NB_CAP_CM,
LAPSE_CC_CM, LAPSE_SA_CM, LAPSE_AP_CM, LAPSE_CAP_CM,
REINSTATE_CC_CM, REINSTATE_SA_CM, REINSTATE_AP_CM, REINSTATE_CAP_CM,
EXP_CC_CM, EXP_SA_CM, EXP_AP_CM, EXP_CAP_CM,
LAPSE_CC_P12, LAPSE_SA_P12, LAPSE_AP_P12, LAPSE_CAP_P12,
REINSTATE_CC_P12, REINSTATE_SA_P12, REINSTATE_AP_P12, REINSTATE_CAP_P12,
EXP_CC_P12, EXP_SA_P12, EXP_AP_P12, EXP_CAP_P12,
LAPSE_CC_YTD, LAPSE_SA_YTD, LAPSE_AP_YTD, LAPSE_CAP_YTD,
REINSTATE_CC_YTD, REINSTATE_SA_YTD, REINSTATE_AP_YTD, REINSTATE_CAP_YTD,
EXP_CC_YTD, EXP_SA_YTD, EXP_AP_YTD, EXP_CAP_YTD,
CREATE_DT, CREATE_BY, UPDATE_DT, UPDATE_BY, EFFECTIVESTART_DT, EFFECTIVEEND_DT)
SELECT P_STR_CMCD AS CMCD, P_DT_CYCLE_MTH AS CYCLE_MTH, STR_AGENT_TYPE, P_STR_LIMRA_TYPE AS LIMRA_TYPE, AGENCY_INFO.CHANNEL AS CHANNEL,
AGENCY_INFO.LEADER_AGT AS AGT, AGENCY_INFO.AGY, AGENCY_INFO.DISTRICT, CASE WHEN POL_TYPE IS NULL THEN ' ' ELSE POL_TYPE END AS POL_TYPE, --HKG001
AGENCY_INFO.ACTIVE_DT, AGENCY_INFO.TERM_DT,
NVL(NB_CC_CM,0), NVL(NB_SA_CM,0), NVL(NB_AP_CM,0), NVL(NB_CAP_CM,0),
NVL(LAPSE_CC_CM,0), NVL(LAPSE_SA_CM,0), NVL(LAPSE_AP_CM,0), NVL(LAPSE_CAP_CM,0),
NVL(REINSTATE_CC_CM,0), NVL(REINSTATE_SA_CM,0), NVL(REINSTATE_AP_CM,0), NVL(REINSTATE_CAP_CM,0),
NVL(EXP_CC_CM,0), NVL(EXP_SA_CM,0), NVL(EXP_AP_CM,0), NVL(EXP_CAP_CM,0),
NVL(LAPSE_CC_P12,0), NVL(LAPSE_SA_P12,0), NVL(LAPSE_AP_P12,0), NVL(LAPSE_CAP_P12,0),
NVL(REINSTATE_CC_P12,0), NVL(REINSTATE_SA_P12,0), NVL(REINSTATE_AP_P12,0), NVL(REINSTATE_CAP_P12,0),
NVL(EXP_CC_P12,0), NVL(EXP_SA_P12,0), NVL(EXP_AP_P12,0), NVL(EXP_CAP_P12,0),
NVL(LAPSE_CC_YTD,0), NVL(LAPSE_SA_YTD,0), NVL(LAPSE_AP_YTD,0), NVL(LAPSE_CAP_YTD,0),
NVL(REINSTATE_CC_YTD,0), NVL(REINSTATE_SA_YTD,0), NVL(REINSTATE_AP_YTD,0), NVL(REINSTATE_CAP_YTD,0),
NVL(EXP_CC_YTD,0), NVL(EXP_SA_YTD,0), NVL(EXP_AP_YTD,0), NVL(EXP_CAP_YTD,0),
SYSDATE, P_STR_USER, SYSDATE, P_STR_USER, TRUNC(P_DT_CYCLE_MTH,'MM'), DT_REMOVE_DATE
FROM
(
SELECT AGY, LEADER_AGT, DISTRICT, ACTIVE_DT, TERM_DT, CHANNEL
FROM PER_AGENCY_INFO
WHERE CMCD = P_STR_CMCD
AND CYCLE_MTH = P_DT_CYCLE_MTH
/*AND CHANNEL = P_STR_CHANNEL*/
AND UPPER(AGY_TYPE) = STR_AGENCY_TYPE
) AGENCY_INFO LEFT JOIN
(SELECT AGY, POL_TYPE, --HKG001
SUM(NVL(NB_CC_CM,0)) AS NB_CC_CM, SUM(NVL(NB_SA_CM,0)) AS NB_SA_CM , SUM(NVL(NB_AP_CM,0)) AS NB_AP_CM , SUM(NVL(NB_CAP_CM,0)) AS NB_CAP_CM,
SUM(NVL(LAPSE_CC_CM,0)) AS LAPSE_CC_CM, SUM(NVL(LAPSE_SA_CM,0)) AS LAPSE_SA_CM, SUM(NVL(LAPSE_AP_CM,0)) AS LAPSE_AP_CM, SUM(NVL(LAPSE_CAP_CM,0)) AS LAPSE_CAP_CM,
SUM(NVL(REINSTATE_CC_CM,0)) AS REINSTATE_CC_CM, SUM(NVL(REINSTATE_SA_CM,0)) AS REINSTATE_SA_CM, SUM(NVL(REINSTATE_AP_CM,0)) AS REINSTATE_AP_CM, SUM(NVL(REINSTATE_CAP_CM,0)) AS REINSTATE_CAP_CM,
SUM(NVL(EXP_CC_CM,0)) AS EXP_CC_CM, SUM(NVL(EXP_SA_CM,0)) AS EXP_SA_CM, SUM(NVL(EXP_AP_CM,0)) AS EXP_AP_CM, SUM(NVL(EXP_CAP_CM,0)) AS EXP_CAP_CM,
SUM(NVL(LAPSE_CC_P12,0)) AS LAPSE_CC_P12, SUM(NVL(LAPSE_SA_P12,0)) AS LAPSE_SA_P12, SUM(NVL(LAPSE_AP_P12,0)) AS LAPSE_AP_P12, SUM(NVL(LAPSE_CAP_P12,0)) AS LAPSE_CAP_P12,
SUM(NVL(REINSTATE_CC_P12,0)) AS REINSTATE_CC_P12, SUM(NVL(REINSTATE_SA_P12,0)) AS REINSTATE_SA_P12, SUM(NVL(REINSTATE_AP_P12,0)) AS REINSTATE_AP_P12, SUM(NVL(REINSTATE_CAP_P12,0)) AS REINSTATE_CAP_P12,
SUM(NVL(EXP_CC_P12,0)) AS EXP_CC_P12, SUM(NVL(EXP_SA_P12,0)) AS EXP_SA_P12, SUM(NVL(EXP_AP_P12,0)) AS EXP_AP_P12, SUM(NVL(EXP_CAP_P12,0)) AS EXP_CAP_P12,
SUM(NVL(LAPSE_CC_YTD,0)) AS LAPSE_CC_YTD, SUM(NVL(LAPSE_SA_YTD,0)) AS LAPSE_SA_YTD, SUM(NVL(LAPSE_AP_YTD,0)) AS LAPSE_AP_YTD, SUM(NVL(LAPSE_CAP_YTD,0)) AS LAPSE_CAP_YTD,
SUM(NVL(REINSTATE_CC_YTD,0)) AS REINSTATE_CC_YTD, SUM(NVL(REINSTATE_SA_YTD,0)) AS REINSTATE_SA_YTD, SUM(NVL(REINSTATE_AP_YTD,0)) AS REINSTATE_AP_YTD, SUM(NVL(REINSTATE_CAP_YTD,0)) AS REINSTATE_CAP_YTD,
SUM(NVL(EXP_CC_YTD,0)) AS EXP_CC_YTD, SUM(NVL(EXP_SA_YTD,0)) AS EXP_SA_YTD, SUM(NVL(EXP_AP_YTD,0)) AS EXP_AP_YTD, SUM(NVL(EXP_CAP_YTD,0)) AS EXP_CAP_YTD
FROM PER_LIMRA WHERE
CMCD=P_STR_CMCD AND CYCLE_MTH= P_DT_CYCLE_MTH AND
AGENT_TYPE = '00' AND LIMRA_TYPE= P_STR_LIMRA_TYPE /*AND CHANNEL = P_STR_CHANNEL*/
--GROUP BY AGY --HKG001
GROUP BY AGY, POL_TYPE --HKG001
) PERSISTENCY
ON PERSISTENCY.AGY = AGENCY_INFO.AGY;
--HKG001 START: PREPARE DUMMY PAIRS
STR_BREAKDOWN_BY := SUBSTR(P_STR_LIMRA_TYPE, 6, 1);
--IF STR_BREAKDOWN_BY IS NOT NULL THEN --HKG006
IF STR_BREAKDOWN_BY IS NOT NULL AND STR_BREAKDOWN_BY !='N'THEN
EXECUTE IMMEDIATE 'TRUNCATE TABLE PER_LIMRA_TMP';
INSERT INTO PER_LIMRA_TMP NOLOGGING
SELECT * FROM PER_LIMRA
WHERE CMCD=P_STR_CMCD AND CYCLE_MTH= P_DT_CYCLE_MTH AND AGENT_TYPE = STR_AGENT_TYPE AND LIMRA_TYPE= P_STR_LIMRA_TYPE AND POL_TYPE=' ';
IF STR_BREAKDOWN_BY = 'P' THEN
UPDATE PER_LIMRA SET POL_TYPE='NPRC'
WHERE CMCD=P_STR_CMCD AND CYCLE_MTH= P_DT_CYCLE_MTH AND AGENT_TYPE = STR_AGENT_TYPE AND LIMRA_TYPE= P_STR_LIMRA_TYPE AND POL_TYPE=' ';
--ELSE --HKG009
-- UPDATE PER_LIMRA SET POL_TYPE='NVIT' --HKG009
-- WHERE CMCD=P_STR_CMCD AND CYCLE_MTH= P_DT_CYCLE_MTH AND AGENT_TYPE = STR_AGENT_TYPE AND LIMRA_TYPE= P_STR_LIMRA_TYPE AND POL_TYPE=' '; --HKG009
END IF;
INSERT INTO PER_LIMRA NOLOGGING(CMCD, CYCLE_MTH, AGENT_TYPE,LIMRA_TYPE, CHANNEL, AGT, AGY, DISTRICT, POL_TYPE,CONTRACT_DT, TERM_DT,
NB_CC_CM, NB_SA_CM, NB_AP_CM, NB_CAP_CM,
LAPSE_CC_CM, LAPSE_SA_CM, LAPSE_AP_CM, LAPSE_CAP_CM,
REINSTATE_CC_CM, REINSTATE_SA_CM, REINSTATE_AP_CM, REINSTATE_CAP_CM,
EXP_CC_CM, EXP_SA_CM, EXP_AP_CM, EXP_CAP_CM,
LAPSE_CC_P12, LAPSE_SA_P12, LAPSE_AP_P12, LAPSE_CAP_P12,
REINSTATE_CC_P12, REINSTATE_SA_P12, REINSTATE_AP_P12, REINSTATE_CAP_P12,
EXP_CC_P12, EXP_SA_P12, EXP_AP_P12, EXP_CAP_P12,
LAPSE_CC_YTD, LAPSE_SA_YTD, LAPSE_AP_YTD, LAPSE_CAP_YTD,
REINSTATE_CC_YTD, REINSTATE_SA_YTD, REINSTATE_AP_YTD, REINSTATE_CAP_YTD,
EXP_CC_YTD, EXP_SA_YTD, EXP_AP_YTD, EXP_CAP_YTD,
CREATE_DT, CREATE_BY, UPDATE_DT, UPDATE_BY, EFFECTIVESTART_DT, EFFECTIVEEND_DT)
--SELECT CMCD, CYCLE_MTH, AGENT_TYPE,LIMRA_TYPE, CHANNEL, AGT, AGY, DISTRICT, CASE WHEN STR_BREAKDOWN_BY='P' THEN 'PRC' ELSE 'VIT' END AS POL_TYPE,CONTRACT_DT, TERM_DT, --HKG009
SELECT CMCD, CYCLE_MTH, AGENT_TYPE,LIMRA_TYPE, CHANNEL, AGT, AGY, DISTRICT, 'PRC' AS POL_TYPE,CONTRACT_DT, TERM_DT, --HKG009
NB_CC_CM, NB_SA_CM, NB_AP_CM, NB_CAP_CM,
LAPSE_CC_CM, LAPSE_SA_CM, LAPSE_AP_CM, LAPSE_CAP_CM,
REINSTATE_CC_CM, REINSTATE_SA_CM, REINSTATE_AP_CM, REINSTATE_CAP_CM,
EXP_CC_CM, EXP_SA_CM, EXP_AP_CM, EXP_CAP_CM,
LAPSE_CC_P12, LAPSE_SA_P12, LAPSE_AP_P12, LAPSE_CAP_P12,
REINSTATE_CC_P12, REINSTATE_SA_P12, REINSTATE_AP_P12, REINSTATE_CAP_P12,
EXP_CC_P12, EXP_SA_P12, EXP_AP_P12, EXP_CAP_P12,
LAPSE_CC_YTD, LAPSE_SA_YTD, LAPSE_AP_YTD, LAPSE_CAP_YTD,
REINSTATE_CC_YTD, REINSTATE_SA_YTD, REINSTATE_AP_YTD, REINSTATE_CAP_YTD,
EXP_CC_YTD, EXP_SA_YTD, EXP_AP_YTD, EXP_CAP_YTD,
CREATE_DT, CREATE_BY, UPDATE_DT, UPDATE_BY, EFFECTIVESTART_DT, EFFECTIVEEND_DT
FROM PER_LIMRA_TMP;
END IF;
--HKG001 END
COMMIT;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, 'SP_PER_LIMRA_CAL02 ERROR '||SQLERRM);
END;
--LIMRA PERSISTENCY CALCULATION FOR DISTRICT LEVEL
PROCEDURE SP_PER_LIMRA_CAL03
( P_STR_CMCD IN VARCHAR2,
P_DT_CYCLE_MTH IN DATE,
P_DT_CYCLE_MTH_END IN DATE,
P_STR_LIMRA_TYPE IN VARCHAR2,
/*P_STR_CHANNEL IN VARCHAR2,*/
P_STR_USER IN VARCHAR2
) IS
STR_AGENT_TYPE VARCHAR2(2) := '03';
DT_START_MTH DATE;
DT_CYCLE_YTD DATE;
STR_AGENCY_TYPE VARCHAR2(5) := 'AGY';
STR_DISTRICT_TYPE VARCHAR2(5) := 'DM';
DEC_TABLE_COUNTER NUMBER :=0;
STR_SQL_STMT VARCHAR2(5000);
STR_BREAKDOWN_BY CHAR(1) :='N'; --HKG001
BEGIN
/* HKG001: START
IF (P_STR_LIMRA_TYPE='LIM19') THEN
DT_START_MTH:=ADD_MONTHS(P_DT_CYCLE_MTH, -11);
ELSIF (P_STR_LIMRA_TYPE='LIM13') THEN
DT_START_MTH:=ADD_MONTHS(P_DT_CYCLE_MTH, -11);
ELSIF (P_STR_LIMRA_TYPE='LIM25') THEN
DT_START_MTH:=ADD_MONTHS(P_DT_CYCLE_MTH, -11);
END IF;
*/
IF (SUBSTR(P_STR_LIMRA_TYPE, 0, 5)='LIM19') THEN
DT_START_MTH:=ADD_MONTHS(P_DT_CYCLE_MTH, -11);
ELSIF (SUBSTR(P_STR_LIMRA_TYPE, 0, 5)='LIM13') THEN
DT_START_MTH:=ADD_MONTHS(P_DT_CYCLE_MTH, -11);
ELSIF (SUBSTR(P_STR_LIMRA_TYPE, 0, 5)='LIM25') THEN
DT_START_MTH:=ADD_MONTHS(P_DT_CYCLE_MTH, -11);
END IF;
--HKG001 END
IF (TO_CHAR(P_DT_CYCLE_MTH,'MM')= '12') THEN
DT_CYCLE_YTD:= P_DT_CYCLE_MTH;
ELSE
DT_CYCLE_YTD := TO_DATE(TO_CHAR(TO_NUMBER(TO_CHAR(P_DT_CYCLE_MTH,'YYYY')-1))||'1231', 'YYYYMMDD');
END IF;
-- DBMS_OUTPUT.PUT_LINE('START INSERT' || TO_CHAR(SYSDATE, 'YYYYMMDD HH:MI:SS'));
--CALCULATE CURRENT MONTH NB,LAPSE, REINSTATE,EXPOSURE
EXECUTE IMMEDIATE 'TRUNCATE TABLE PER_LIMRA_TMP';
INSERT INTO PER_LIMRA_TMP NOLOGGING(CMCD, CYCLE_MTH, AGENT_TYPE,LIMRA_TYPE, CHANNEL, AGT, AGY, DISTRICT, POL_TYPE, --HKG001
CONTRACT_DT, TERM_DT,
NB_CC_CM, NB_SA_CM, NB_AP_CM, NB_CAP_CM,
LAPSE_CC_CM, LAPSE_SA_CM, LAPSE_AP_CM, LAPSE_CAP_CM,
REINSTATE_CC_CM, REINSTATE_SA_CM, REINSTATE_AP_CM, REINSTATE_CAP_CM,
EXP_CC_CM, EXP_SA_CM, EXP_AP_CM, EXP_CAP_CM,
CREATE_DT, CREATE_BY, UPDATE_DT, UPDATE_BY, EFFECTIVESTART_DT, EFFECTIVEEND_DT)
SELECT P_STR_CMCD, P_DT_CYCLE_MTH, STR_AGENT_TYPE, P_STR_LIMRA_TYPE, CHANNEL, DIS_LEADER_CD, AGY_CD, DIS_CD, POL_TYPE, --HKG001
ACTIVE_DT, TERM_DT,
NB_CC_CM, NB_SA_CM, NB_AP_CM, NB_CAP_CM,
LAPSE_CC_CM, LAPSE_SA_CM, LAPSE_AP_CM, LAPSE_CAP_CM,
REINSTATE_CC_CM, REINSTATE_SA_CM, REINSTATE_AP_CM, REINSTATE_CAP_CM,
EXP_CC_CM, EXP_SA_CM, EXP_AP_CM, EXP_CAP_CM,
SYSDATE, P_STR_USER, SYSDATE, P_STR_USER, TRUNC(P_DT_CYCLE_MTH,'MM'), DT_REMOVE_DATE FROM
(
SELECT DIS_LEADER_CD, AGY_CD, DIS_CD, ACTIVE_DT, TERM_DT, DIS_INFO.CHANNEL, POL_TYPE, --HKG001
SUM(NVL(NB_CC_CM,0)) NB_CC_CM, SUM(NVL(NB_SA_CM,0)) NB_SA_CM, SUM(NVL(NB_AP_CM,0)) NB_AP_CM, SUM(NVL(NB_CAP_CM,0)) NB_CAP_CM,
SUM(NVL(LAPSE_CC_CM,0)) LAPSE_CC_CM, SUM(NVL(LAPSE_SA_CM,0)) LAPSE_SA_CM, SUM(NVL(LAPSE_AP_CM,0)) LAPSE_AP_CM, SUM(NVL(LAPSE_CAP_CM,0)) LAPSE_CAP_CM,
SUM(NVL(REINSTATE_CC_CM,0)) REINSTATE_CC_CM, SUM(NVL(REINSTATE_SA_CM,0)) REINSTATE_SA_CM, SUM(NVL(REINSTATE_AP_CM,0)) REINSTATE_AP_CM, SUM(NVL(REINSTATE_CAP_CM,0)) REINSTATE_CAP_CM,
SUM(NVL(EXP_CC_CM,0)) EXP_CC_CM, SUM(NVL(EXP_SA_CM,0)) EXP_SA_CM, SUM(NVL(EXP_AP_CM,0)) EXP_AP_CM, SUM(NVL(EXP_CAP_CM,0)) EXP_CAP_CM FROM
(
SELECT DIS.LEADER_AGT AS DIS_LEADER_CD,
DIS.AGY AS AGY_CD,
DIS.ACTIVE_DT,
DIS.TERM_DT,
DIS.CHANNEL,
AGY.AGY AS SUB_AGY_CD,
DIS.DISTRICT AS DIS_CD
FROM PER_AGENCY_INFO DIS, PER_AGENCY_INFO AGY
WHERE DIS.AGY_TYPE = STR_DISTRICT_TYPE
AND AGY.AGY_TYPE = STR_AGENCY_TYPE
AND DIS.AGY = AGY.DISTRICT
AND DIS.CMCD = P_STR_CMCD
AND DIS.CYCLE_MTH = P_DT_CYCLE_MTH
/*AND DIS.CHANNEL = P_STR_CHANNEL*/
AND AGY.CMCD = P_STR_CMCD
AND AGY.CYCLE_MTH = P_DT_CYCLE_MTH
/*AND AGY.CHANNEL = P_STR_CHANNEL*/
--AND DIS.CHANNEL = AGY.CHANNEL --ADD CHANNEL NEED TO CONFIRM /** DELETED BY WING 20140109 **/
) DIS_INFO
LEFT JOIN
(
--SELECT AGY,CHANNEL,--ADD CHANNEL BY JULIA 20130619 --HKG001
SELECT AGY,CHANNEL, POL_TYPE, --HKG001
SUM(NVL(NB_CC_CM,0)) NB_CC_CM, SUM(NVL(NB_SA_CM,0)) NB_SA_CM, SUM(NVL(NB_AP_CM,0)) NB_AP_CM, SUM(NVL(NB_CAP_CM,0)) NB_CAP_CM,
SUM(NVL(LAPSE_CC_CM,0)) LAPSE_CC_CM, SUM(NVL(LAPSE_SA_CM,0)) LAPSE_SA_CM, SUM(NVL(LAPSE_AP_CM,0)) LAPSE_AP_CM, SUM(NVL(LAPSE_CAP_CM,0)) LAPSE_CAP_CM,
SUM(NVL(REINSTATE_CC_CM,0)) REINSTATE_CC_CM, SUM(NVL(REINSTATE_SA_CM,0)) REINSTATE_SA_CM, SUM(NVL(REINSTATE_AP_CM,0)) REINSTATE_AP_CM, SUM(NVL(REINSTATE_CAP_CM,0)) REINSTATE_CAP_CM,
SUM(NVL(EXP_CC_CM,0)) EXP_CC_CM, SUM(NVL(EXP_SA_CM,0)) EXP_SA_CM, SUM(NVL(EXP_AP_CM,0)) EXP_AP_CM, SUM(NVL(EXP_CAP_CM,0)) EXP_CAP_CM
FROM PER_LIMRA
WHERE CMCD = P_STR_CMCD
AND CYCLE_MTH = P_DT_CYCLE_MTH
AND AGENT_TYPE = '00'
AND LIMRA_TYPE = P_STR_LIMRA_TYPE
/*AND CHANNEL = P_STR_CHANNEL*/
--GROUP BY AGY,CHANNEL --ADD CHANNEL BY JULIA 20130619 --HKG001
GROUP BY AGY,CHANNEL,POL_TYPE --HKG001
) PERSISTENCY
ON DIS_INFO.SUB_AGY_CD = PERSISTENCY.AGY
--AND DIS_INFO.CHANNEL = PERSISTENCY.CHANNEL --ADD CHANNEL BY JULIA 20130619
--GROUP BY DIS_LEADER_CD, AGY_CD, DIS_CD, ACTIVE_DT, TERM_DT, DIS_INFO.CHANNEL); --HKG001
GROUP BY DIS_LEADER_CD, AGY_CD, DIS_CD, ACTIVE_DT, TERM_DT, DIS_INFO.CHANNEL, PERSISTENCY.POL_TYPE); --HKG001
COMMIT;
--HKG001 START
STR_BREAKDOWN_BY := SUBSTR(P_STR_LIMRA_TYPE, 6, 1);
UPDATE PER_LIMRA_TMP SET POL_TYPE = ' ' WHERE POL_TYPE IS NULL AND STR_BREAKDOWN_BY IS NULL;
--UPDATE PER_LIMRA_TMP SET POL_TYPE = ' ' WHERE POL_TYPE IS NULL AND STR_BREAKDOWN_BY = 'N'; --HKG006 --HKG009
UPDATE PER_LIMRA_TMP SET POL_TYPE = 'NPRC' WHERE POL_TYPE IS NULL AND STR_BREAKDOWN_BY = 'P';
--UPDATE PER_LIMRA_TMP SET POL_TYPE = 'NVIT' WHERE POL_TYPE IS NULL AND STR_BREAKDOWN_BY = 'V'; --HKG009
INSERT INTO PER_LIMRA NOLOGGING(CMCD, CYCLE_MTH, AGENT_TYPE,LIMRA_TYPE, CHANNEL, AGT, AGY, DISTRICT, POL_TYPE,
CONTRACT_DT, TERM_DT,
NB_CC_CM, NB_SA_CM, NB_AP_CM, NB_CAP_CM,
LAPSE_CC_CM, LAPSE_SA_CM, LAPSE_AP_CM, LAPSE_CAP_CM,
REINSTATE_CC_CM, REINSTATE_SA_CM, REINSTATE_AP_CM, REINSTATE_CAP_CM,
EXP_CC_CM, EXP_SA_CM, EXP_AP_CM, EXP_CAP_CM,
CREATE_DT, CREATE_BY, UPDATE_DT, UPDATE_BY, EFFECTIVESTART_DT, EFFECTIVEEND_DT)
SELECT CMCD, CYCLE_MTH, AGENT_TYPE,LIMRA_TYPE, CHANNEL, AGT, AGY, DISTRICT, POL_TYPE, CONTRACT_DT, TERM_DT,
SUM(NB_CC_CM), SUM(NB_SA_CM), SUM(NB_AP_CM), SUM(NB_CAP_CM),
SUM(LAPSE_CC_CM), SUM(LAPSE_SA_CM), SUM(LAPSE_AP_CM), SUM(LAPSE_CAP_CM),
SUM(REINSTATE_CC_CM), SUM(REINSTATE_SA_CM), SUM(REINSTATE_AP_CM), SUM(REINSTATE_CAP_CM),
SUM(EXP_CC_CM), SUM(EXP_SA_CM), SUM(EXP_AP_CM), SUM(EXP_CAP_CM),
CREATE_DT, CREATE_BY, UPDATE_DT, UPDATE_BY, EFFECTIVESTART_DT, EFFECTIVEEND_DT
FROM PER_LIMRA_TMP
GROUP BY CMCD, CYCLE_MTH, AGENT_TYPE,LIMRA_TYPE, CHANNEL, AGT, AGY, DISTRICT, POL_TYPE, CONTRACT_DT, TERM_DT, CREATE_DT, CREATE_BY, UPDATE_DT, UPDATE_BY, EFFECTIVESTART_DT, EFFECTIVEEND_DT;
--TO CREATE DUMMY PAIRING RECORD FOR SINGLE ENTRY IF NEED TO BREAKDOWN
--IF STR_BREAKDOWN_BY IS NOT NULL THEN --HKG006
IF STR_BREAKDOWN_BY IS NOT NULL AND STR_BREAKDOWN_BY !='N' THEN
EXECUTE IMMEDIATE 'TRUNCATE TABLE PER_LIMRA_TMP';
INSERT INTO PER_LIMRA_TMP NOLOGGING(
CMCD, CYCLE_MTH, AGENT_TYPE,LIMRA_TYPE, CHANNEL, AGT, AGY, DISTRICT, POL_TYPE,
NB_CC_CM, NB_SA_CM, NB_AP_CM, NB_CAP_CM,
LAPSE_CC_CM, LAPSE_SA_CM, LAPSE_AP_CM, LAPSE_CAP_CM,
REINSTATE_CC_CM, REINSTATE_SA_CM, REINSTATE_AP_CM, REINSTATE_CAP_CM,
EXP_CC_CM, EXP_SA_CM, EXP_AP_CM, EXP_CAP_CM,
CREATE_DT, CREATE_BY, UPDATE_DT, UPDATE_BY, EFFECTIVESTART_DT, EFFECTIVEEND_DT
)
SELECT CMCD, CYCLE_MTH, AGENT_TYPE,LIMRA_TYPE, CHANNEL, AGT, AGY, DISTRICT, MAX(POL_TYPE),
0,0,0,0,
0,0,0,0,
0,0,0,0,
0,0,0,0,
SYSDATE, P_STR_USER, SYSDATE, P_STR_USER, TRUNC(P_DT_CYCLE_MTH,'MM'), DT_REMOVE_DATE
FROM PER_LIMRA
WHERE CMCD=P_STR_CMCD AND CYCLE_MTH= P_DT_CYCLE_MTH AND AGENT_TYPE = STR_AGENT_TYPE AND LIMRA_TYPE= P_STR_LIMRA_TYPE
GROUP BY CMCD, CYCLE_MTH, AGENT_TYPE,LIMRA_TYPE, CHANNEL, AGT, AGY, DISTRICT
HAVING COUNT(POL_TYPE) = 1;
INSERT INTO PER_LIMRA NOLOGGING(
CMCD, CYCLE_MTH, AGENT_TYPE,LIMRA_TYPE, CHANNEL, AGT, AGY, DISTRICT,
POL_TYPE,
CONTRACT_DT, TERM_DT,
NB_CC_CM, NB_SA_CM, NB_AP_CM, NB_CAP_CM,
LAPSE_CC_CM, LAPSE_SA_CM, LAPSE_AP_CM, LAPSE_CAP_CM,
REINSTATE_CC_CM, REINSTATE_SA_CM, REINSTATE_AP_CM, REINSTATE_CAP_CM,
EXP_CC_CM, EXP_SA_CM, EXP_AP_CM, EXP_CAP_CM,
CREATE_DT, CREATE_BY, UPDATE_DT, UPDATE_BY, EFFECTIVESTART_DT, EFFECTIVEEND_DT)
SELECT CMCD, CYCLE_MTH, AGENT_TYPE,LIMRA_TYPE, CHANNEL, AGT, AGY, DISTRICT,
--CASE WHEN POL_TYPE='NPRC' THEN 'PRC' WHEN POL_TYPE='PRC' THEN 'NPRC' WHEN POL_TYPE='NVIT' THEN 'VIT' WHEN POL_TYPE='VIT' THEN 'NVIT' ELSE POL_TYPE END AS POL_TYPE, --HKG009
CASE WHEN POL_TYPE='NPRC' THEN 'PRC' WHEN POL_TYPE='PRC' THEN 'NPRC' ELSE POL_TYPE END AS POL_TYPE, --HKG009
CONTRACT_DT, TERM_DT,
NB_CC_CM, NB_SA_CM, NB_AP_CM, NB_CAP_CM,
LAPSE_CC_CM, LAPSE_SA_CM, LAPSE_AP_CM, LAPSE_CAP_CM,
REINSTATE_CC_CM, REINSTATE_SA_CM, REINSTATE_AP_CM, REINSTATE_CAP_CM,
EXP_CC_CM, EXP_SA_CM, EXP_AP_CM, EXP_CAP_CM,
CREATE_DT, CREATE_BY, UPDATE_DT, UPDATE_BY, EFFECTIVESTART_DT, EFFECTIVEEND_DT
FROM PER_LIMRA_TMP;
END IF;
COMMIT;
-- DBMS_OUTPUT.PUT_LINE('COMPLETE INSERT' || TO_CHAR(SYSDATE, 'YYYYMMDD HH:MI:SS'));
EXECUTE IMMEDIATE 'TRUNCATE TABLE PER_LIMRA_03_TEMP';
INSERT INTO PER_LIMRA_03_TEMP
SELECT AGY.DISTRICT, 'NA', LIMRA_TYPE, P_DT_CYCLE_MTH AS CYCLE_MTH,
SUM(LAPSE_CC_CM) AS LAPSE_CC_P12
, SUM(LAPSE_SA_CM) AS LAPSE_SA_P12
, SUM(LAPSE_AP_CM) AS LAPSE_AP_P12
, SUM(LAPSE_CAP_CM) AS LAPSE_CAP_P12
, SUM(REINSTATE_CC_CM) AS REINSTATE_CC_P12
, SUM(REINSTATE_SA_CM) AS REINSTATE_SA_P12
, SUM(REINSTATE_AP_CM) AS REINSTATE_AP_P12
, SUM(REINSTATE_CAP_CM) AS REINSTATE_CAP_P12
, SUM(EXP_CC_CM) AS EXP_CC_P12, SUM(EXP_SA_CM) AS EXP_SA_P12 , SUM(EXP_AP_CM) AS EXP_AP_P12, SUM(EXP_CAP_CM) AS EXP_CAP_P12
, SUM(CASE WHEN PER_LIMRA.CYCLE_MTH >= DT_CYCLE_YTD THEN LAPSE_CC_CM ELSE 0 END) AS LAPSE_CC_YTD
, SUM(CASE WHEN PER_LIMRA.CYCLE_MTH >= DT_CYCLE_YTD THEN LAPSE_SA_CM ELSE 0 END) AS LAPSE_SA_YTD
, SUM(CASE WHEN PER_LIMRA.CYCLE_MTH >= DT_CYCLE_YTD THEN LAPSE_AP_CM ELSE 0 END) AS LAPSE_AP_YTD
, SUM(CASE WHEN PER_LIMRA.CYCLE_MTH >= DT_CYCLE_YTD THEN LAPSE_CAP_CM ELSE 0 END) AS LAPSE_CAP_YTD
, SUM(CASE WHEN PER_LIMRA.CYCLE_MTH >= DT_CYCLE_YTD THEN REINSTATE_CC_CM ELSE 0 END) AS REINSTATE_CC_YTD
, SUM(CASE WHEN PER_LIMRA.CYCLE_MTH >= DT_CYCLE_YTD THEN REINSTATE_SA_CM ELSE 0 END) AS REINSTATE_SA_YTD
, SUM(CASE WHEN PER_LIMRA.CYCLE_MTH >= DT_CYCLE_YTD THEN REINSTATE_AP_CM ELSE 0 END) AS REINSTATE_AP_YTD
, SUM(CASE WHEN PER_LIMRA.CYCLE_MTH >= DT_CYCLE_YTD THEN REINSTATE_CAP_CM ELSE 0 END) AS REINSTATE_CAP_YTD
, SUM(CASE WHEN PER_LIMRA.CYCLE_MTH >= DT_CYCLE_YTD THEN EXP_CC_CM ELSE 0 END) AS EXP_CC_YTD
, SUM(CASE WHEN PER_LIMRA.CYCLE_MTH >= DT_CYCLE_YTD THEN EXP_SA_CM ELSE 0 END) AS EXP_SA_YTD
, SUM(CASE WHEN PER_LIMRA.CYCLE_MTH >= DT_CYCLE_YTD THEN EXP_AP_CM ELSE 0 END)AS EXP_AP_YTD
, SUM(CASE WHEN PER_LIMRA.CYCLE_MTH >= DT_CYCLE_YTD THEN EXP_CAP_CM ELSE 0 END) AS EXP_CAP_YTD
, POL_TYPE --HKG001
FROM (
SELECT * FROM PER_LIMRA A
WHERE A.CMCD= P_STR_CMCD AND
--A.LIMRA_TYPE= P_STR_LIMRA_TYPE AND A.AGENT_TYPE='00' AND
A.AGENT_TYPE='00' AND A.LIMRA_TYPE= P_STR_LIMRA_TYPE AND --HKG009
A.EFFECTIVEEND_DT = DT_REMOVE_DATE AND
A.CYCLE_MTH <= P_DT_CYCLE_MTH AND A.CYCLE_MTH >= DT_START_MTH
/*UNION ALL
SELECT * FROM PER_LIMRA_CUR A
WHERE A.CMCD= P_STR_CMCD AND
A.LIMRA_TYPE= P_STR_LIMRA_TYPE AND A.AGENT_TYPE='00' AND
A.EFFECTIVEEND_DT = DT_REMOVE_DATE AND
A.CYCLE_MTH <= P_DT_CYCLE_MTH AND A.CYCLE_MTH >= DT_START_MTH*/
) PER_LIMRA ---JOIN OLD MONTHS AND CURRENT MONTHS
, PER_AGENCY_INFO AGY
WHERE
AGY.AGY= PER_LIMRA.AGY AND
AGY.CYCLE_MTH = P_DT_CYCLE_MTH AND
AGY.CMCD = P_STR_CMCD AND
AGY.AGY_TYPE = STR_AGENCY_TYPE
-- NVL(PER_LIMRA.CHANNEL, '*') = NVL(AGY.CHANNEL,'*')
--AND PER_LIMRA.POL_TYPE IS NOT NULL --HKG001
GROUP BY AGY.DISTRICT, PER_LIMRA.LIMRA_TYPE, PER_LIMRA.POL_TYPE; --HKG001
--TLOG ('CHECK');
--GET UPDATED RECORD INTO TMP TABLE WITH P12, YTD
UPDATE PER_LIMRA TAR SET
( LAPSE_CC_P12, LAPSE_SA_P12, LAPSE_AP_P12, LAPSE_CAP_P12,
REINSTATE_CC_P12, REINSTATE_SA_P12, REINSTATE_AP_P12, REINSTATE_CAP_P12,
EXP_CC_P12, EXP_SA_P12, EXP_AP_P12, EXP_CAP_P12,
LAPSE_CC_YTD, LAPSE_SA_YTD, LAPSE_AP_YTD, LAPSE_CAP_YTD,
REINSTATE_CC_YTD, REINSTATE_SA_YTD, REINSTATE_AP_YTD, REINSTATE_CAP_YTD,
EXP_CC_YTD, EXP_SA_YTD, EXP_AP_YTD, EXP_CAP_YTD,
UPDATE_DT, UPDATE_BY) =
( SELECT
LAPSE_CC_P12, LAPSE_SA_P12, LAPSE_AP_P12, LAPSE_CAP_P12,
REINSTATE_CC_P12, REINSTATE_SA_P12, REINSTATE_AP_P12, REINSTATE_CAP_P12,
EXP_CC_P12, EXP_SA_P12, EXP_AP_P12, EXP_CAP_P12,
LAPSE_CC_YTD, LAPSE_SA_YTD, LAPSE_AP_YTD, LAPSE_CAP_YTD,
REINSTATE_CC_YTD, REINSTATE_SA_YTD, REINSTATE_AP_YTD, REINSTATE_CAP_YTD,
EXP_CC_YTD, EXP_SA_YTD, EXP_AP_YTD, EXP_CAP_YTD,
SYSDATE, P_STR_USER
FROM PER_LIMRA_03_TEMP
WHERE
NVL(TAR.AGY, '*') = NVL(PER_LIMRA_03_TEMP.DISTRICT, '*') --AND NVL(TAR.CHANNEL,'*') = NVL(PER_LIMRA_03_TEMP.CHANNEL, '*') AND--ADD CHANNEL NEED TO CONFIRM
AND TAR.CYCLE_MTH=PER_LIMRA_03_TEMP.CYCLE_MTH
AND TAR.POL_TYPE = PER_LIMRA_03_TEMP.POL_TYPE --HKG001
)
WHERE
TAR.CMCD= P_STR_CMCD AND
TAR.AGENT_TYPE = STR_AGENT_TYPE AND TAR.LIMRA_TYPE = P_STR_LIMRA_TYPE
AND TAR.CYCLE_MTH = P_DT_CYCLE_MTH
-- AND TAR.POL_TYPE = POL_TYPE --HKG001
AND EXISTS (
SELECT 1 FROM
PER_LIMRA_03_TEMP
WHERE
NVL(TAR.AGY, '*') = NVL(PER_LIMRA_03_TEMP.DISTRICT, '*')-- AND NVL(TAR.CHANNEL,'*') = NVL(PER_LIMRA_03_TEMP.CHANNEL, '*') AND--ADD CHANNEL NEED TO CONFIRM
AND TAR.CYCLE_MTH=PER_LIMRA_03_TEMP.CYCLE_MTH
AND TAR.POL_TYPE = PER_LIMRA_03_TEMP.POL_TYPE --HKG001
);
--TLOG ('END CALL03');
COMMIT;
-- DBMS_OUTPUT.PUT_LINE('UPDATE TABLE COMPLETED' || TO_CHAR(SYSDATE, 'YYYYMMDD HH:MI:SS'));
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, 'SP_PER_LIMRA_CAL03 ERROR '||SQLERRM);
END;
--COMPANY LEVEL PERSISTENCY
PROCEDURE SP_PER_LIMRA_CAL04
( P_STR_CMCD IN VARCHAR2,
P_DT_CYCLE_MTH IN DATE,
P_DT_CYCLE_MTH_END IN DATE,
P_STR_LIMRA_TYPE IN VARCHAR2,
/*P_STR_CHANNEL IN VARCHAR2,*/
P_STR_USER IN VARCHAR2
) IS
STR_AGENT_TYPE VARCHAR2(2) := '04';
--DT_START_MTH DATE;
--DT_CYCLE_YTD DATE;
BEGIN
/* IF (P_STR_LIMRA_TYPE='LIM19') THEN
DT_START_MTH:=ADD_MONTHS(P_DT_CYCLE_MTH, -11);
ELSIF (P_STR_LIMRA_TYPE='LIM13') THEN
DT_START_MTH:=ADD_MONTHS(P_DT_CYCLE_MTH, -11);
ELSIF (P_STR_LIMRA_TYPE='LIM25') THEN
DT_START_MTH:=ADD_MONTHS(P_DT_CYCLE_MTH, -11);
END IF;
IF (TO_CHAR(P_DT_CYCLE_MTH,'MM')= '12') THEN
DT_CYCLE_YTD:= P_DT_CYCLE_MTH;
ELSE
DT_CYCLE_YTD := TO_DATE(TO_CHAR(TO_NUMBER(TO_CHAR(P_DT_CYCLE_MTH,'YYYY')-1))||'1201', 'YYYYMMDD');
END IF; */
INSERT INTO PER_LIMRA ( CMCD, CYCLE_MTH, AGENT_TYPE, LIMRA_TYPE, CHANNEL,POL_TYPE, --HKG001
NB_CC_CM, NB_SA_CM, NB_AP_CM, NB_CAP_CM,
LAPSE_CC_CM, LAPSE_SA_CM, LAPSE_AP_CM, LAPSE_CAP_CM,
REINSTATE_CC_CM, REINSTATE_SA_CM, REINSTATE_AP_CM, REINSTATE_CAP_CM,
EXP_CC_CM, EXP_SA_CM, EXP_AP_CM, EXP_CAP_CM,
LAPSE_CC_P12, LAPSE_SA_P12, LAPSE_AP_P12, LAPSE_CAP_P12,
REINSTATE_CC_P12, REINSTATE_SA_P12, REINSTATE_AP_P12, REINSTATE_CAP_P12,
EXP_CC_P12, EXP_SA_P12, EXP_AP_P12, EXP_CAP_P12,
LAPSE_CC_YTD, LAPSE_SA_YTD, LAPSE_AP_YTD, LAPSE_CAP_YTD,
REINSTATE_CC_YTD, REINSTATE_SA_YTD, REINSTATE_AP_YTD, REINSTATE_CAP_YTD,
EXP_CC_YTD, EXP_SA_YTD, EXP_AP_YTD, EXP_CAP_YTD,
CREATE_DT, CREATE_BY, UPDATE_DT, UPDATE_BY, EFFECTIVESTART_DT, EFFECTIVEEND_DT)
SELECT P_STR_CMCD, P_DT_CYCLE_MTH, STR_AGENT_TYPE, P_STR_LIMRA_TYPE, CHANNEL, POL_TYPE, --HKG001
SUM(NB_CC_CM) AS NB_CC_CM, SUM(NB_SA_CM) AS NB_SA_CM, SUM(NB_AP_CM) AS NB_AP_CM, SUM(NB_CAP_CM) NB_CAP_CM,
SUM(LAPSE_CC_CM) AS LAPSE_CC_CM, SUM(LAPSE_SA_CM) AS LAPSE_SA_CM, SUM(LAPSE_AP_CM) AS LAPSE_AP_CM, SUM(LAPSE_CAP_CM) AS LAPSE_CAP_CM,
SUM(REINSTATE_CC_CM) AS REINSTATE_CC_CM, SUM(REINSTATE_SA_CM) AS REINSTATE_SA_CM, SUM(REINSTATE_AP_CM) AS REINSTATE_AP_CM, SUM(REINSTATE_CAP_CM) AS REINSTATE_CAP_CM,
SUM(EXP_CC_CM) AS EXP_CC_CM, SUM(EXP_SA_CM) AS EXP_SA_CM, SUM(EXP_AP_CM) AS EXP_AP_CM, SUM(EXP_CAP_CM) AS EXP_CAP_CM,
SUM(LAPSE_CC_P12) AS LAPSE_CC_P12, SUM(LAPSE_SA_P12) AS LAPSE_SA_P12, SUM(LAPSE_AP_P12) AS LAPSE_AP_P12, SUM(LAPSE_CAP_P12) AS LAPSE_CAP_P12,
SUM(REINSTATE_CC_P12) AS REINSTATE_CC_P12 , SUM(REINSTATE_SA_P12) AS REINSTATE_SA_P12, SUM(REINSTATE_AP_P12) AS REINSTATE_AP_P12, SUM(REINSTATE_CAP_P12) AS REINSTATE_CAP_P12,
SUM(EXP_CC_P12) AS EXP_CC_P12, SUM(EXP_SA_P12) AS EXP_SA_P12, SUM(EXP_AP_P12) AS EXP_AP_P12, SUM(EXP_CAP_P12) AS EXP_CAP_P12,
SUM(LAPSE_CC_YTD) AS LAPSE_CC_YTD, SUM(LAPSE_SA_YTD) AS LAPSE_SA_YTD, SUM(LAPSE_AP_YTD) AS LAPSE_AP_YTD, SUM(LAPSE_CAP_YTD) AS LAPSE_CAP_YTD,
SUM(REINSTATE_CC_YTD) AS REINSTATE_CC_YTD, SUM(REINSTATE_SA_YTD) AS REINSTATE_SA_YTD, SUM(REINSTATE_AP_YTD) AS REINSTATE_AP_YTD, SUM(REINSTATE_CAP_YTD) AS REINSTATE_CAP_YTD,
SUM(EXP_CC_YTD) AS EXP_CC_YTD, SUM(EXP_SA_YTD) AS EXP_SA_YTD, SUM(EXP_AP_YTD) AS EXP_AP_YTD, SUM(EXP_CAP_YTD) AS EXP_CAP_YTD,
SYSDATE, P_STR_USER, SYSDATE, P_STR_USER, TRUNC(P_DT_CYCLE_MTH,'MM'), DT_REMOVE_DATE
FROM PER_LIMRA WHERE
CMCD=P_STR_CMCD AND CYCLE_MTH = P_DT_CYCLE_MTH --HKG009
AND AGENT_TYPE = '00' AND LIMRA_TYPE = P_STR_LIMRA_TYPE /*AND CHANNEL = P_STR_CHANNEL*/
--AND CYCLE_MTH = P_DT_CYCLE_MTH
AND EFFECTIVEEND_DT = DT_REMOVE_DATE
--GROUP BY CHANNEL --HKG001
GROUP BY CHANNEL,POL_TYPE --HKG001
HAVING CHANNEL IS NOT NULL;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, 'SP_PER_LIMRA_CAL04 ERROR '||SQLERRM);
END;
--COMPANY LEVEL PERSISTENCY
PROCEDURE SP_PER_LIMRA_CAL05
( P_STR_CMCD IN VARCHAR2,
P_DT_CYCLE_MTH IN DATE,
P_DT_CYCLE_MTH_END IN DATE,
P_STR_LIMRA_TYPE IN VARCHAR2,
/*P_STR_CHANNEL IN VARCHAR2,*/
P_STR_USER IN VARCHAR2
) IS
STR_AGENT_TYPE VARCHAR2(2) := '05';
--DT_START_MTH DATE;
--DT_CYCLE_YTD DATE;
BEGIN
/*IF (P_STR_LIMRA_TYPE='LIM19') THEN
DT_START_MTH:=ADD_MONTHS(P_DT_CYCLE_MTH, -11);
ELSIF (P_STR_LIMRA_TYPE='LIM13') THEN
DT_START_MTH:=ADD_MONTHS(P_DT_CYCLE_MTH, -11);
ELSIF (P_STR_LIMRA_TYPE='LIM25') THEN
DT_START_MTH:=ADD_MONTHS(P_DT_CYCLE_MTH, -11);
END IF;
IF (TO_CHAR(P_DT_CYCLE_MTH,'MM')= '12') THEN
DT_CYCLE_YTD:= P_DT_CYCLE_MTH;
ELSE
DT_CYCLE_YTD := TO_DATE(TO_CHAR(TO_NUMBER(TO_CHAR(P_DT_CYCLE_MTH,'YYYY')-1))||'1201', 'YYYYMMDD');
END IF; */
INSERT INTO PER_LIMRA ( CMCD, CYCLE_MTH, AGENT_TYPE, LIMRA_TYPE, POL_TYPE, --HKG001
NB_CC_CM, NB_SA_CM, NB_AP_CM, NB_CAP_CM,
LAPSE_CC_CM, LAPSE_SA_CM, LAPSE_AP_CM, LAPSE_CAP_CM,
REINSTATE_CC_CM, REINSTATE_SA_CM, REINSTATE_AP_CM, REINSTATE_CAP_CM,
EXP_CC_CM, EXP_SA_CM, EXP_AP_CM, EXP_CAP_CM,
LAPSE_CC_P12, LAPSE_SA_P12, LAPSE_AP_P12, LAPSE_CAP_P12,
REINSTATE_CC_P12, REINSTATE_SA_P12, REINSTATE_AP_P12, REINSTATE_CAP_P12,
EXP_CC_P12, EXP_SA_P12, EXP_AP_P12, EXP_CAP_P12,
LAPSE_CC_YTD, LAPSE_SA_YTD, LAPSE_AP_YTD, LAPSE_CAP_YTD,
REINSTATE_CC_YTD, REINSTATE_SA_YTD, REINSTATE_AP_YTD, REINSTATE_CAP_YTD,
EXP_CC_YTD, EXP_SA_YTD, EXP_AP_YTD, EXP_CAP_YTD,
CREATE_DT, CREATE_BY, UPDATE_DT, UPDATE_BY, EFFECTIVESTART_DT, EFFECTIVEEND_DT)
SELECT P_STR_CMCD, P_DT_CYCLE_MTH, STR_AGENT_TYPE, P_STR_LIMRA_TYPE, POL_TYPE, --HKG001
SUM(NB_CC_CM) AS NB_CC_CM, SUM(NB_SA_CM) AS NB_SA_CM, SUM(NB_AP_CM) AS NB_AP_CM, SUM(NB_CAP_CM) NB_CAP_CM,
SUM(LAPSE_CC_CM) AS LAPSE_CC_CM, SUM(LAPSE_SA_CM) AS LAPSE_SA_CM, SUM(LAPSE_AP_CM) AS LAPSE_AP_CM, SUM(LAPSE_CAP_CM) AS LAPSE_CAP_CM,
SUM(REINSTATE_CC_CM) AS REINSTATE_CC_CM, SUM(REINSTATE_SA_CM) AS REINSTATE_SA_CM, SUM(REINSTATE_AP_CM) AS REINSTATE_AP_CM, SUM(REINSTATE_CAP_CM) AS REINSTATE_CAP_CM,
SUM(EXP_CC_CM) AS EXP_CC_CM, SUM(EXP_SA_CM) AS EXP_SA_CM, SUM(EXP_AP_CM) AS EXP_AP_CM, SUM(EXP_CAP_CM) AS EXP_CAP_CM,
SUM(LAPSE_CC_P12) AS LAPSE_CC_P12, SUM(LAPSE_SA_P12) AS LAPSE_SA_P12, SUM(LAPSE_AP_P12) AS LAPSE_AP_P12, SUM(LAPSE_CAP_P12) AS LAPSE_CAP_P12,
SUM(REINSTATE_CC_P12) AS REINSTATE_CC_P12 , SUM(REINSTATE_SA_P12) AS REINSTATE_SA_P12, SUM(REINSTATE_AP_P12) AS REINSTATE_AP_P12, SUM(REINSTATE_CAP_P12) AS REINSTATE_CAP_P12,
SUM(EXP_CC_P12) AS EXP_CC_P12, SUM(EXP_SA_P12) AS EXP_SA_P12, SUM(EXP_AP_P12) AS EXP_AP_P12, SUM(EXP_CAP_P12) AS EXP_CAP_P12,
SUM(LAPSE_CC_YTD) AS LAPSE_CC_YTD, SUM(LAPSE_SA_YTD) AS LAPSE_SA_YTD, SUM(LAPSE_AP_YTD) AS LAPSE_AP_YTD, SUM(LAPSE_CAP_YTD) AS LAPSE_CAP_YTD,
SUM(REINSTATE_CC_YTD) AS REINSTATE_CC_YTD, SUM(REINSTATE_SA_YTD) AS REINSTATE_SA_YTD, SUM(REINSTATE_AP_YTD) AS REINSTATE_AP_YTD, SUM(REINSTATE_CAP_YTD) AS REINSTATE_CAP_YTD,
SUM(EXP_CC_YTD) AS EXP_CC_YTD, SUM(EXP_SA_YTD) AS EXP_SA_YTD, SUM(EXP_AP_YTD) AS EXP_AP_YTD, SUM(EXP_CAP_YTD) AS EXP_CAP_YTD,
SYSDATE, P_STR_USER, SYSDATE, P_STR_USER, TRUNC(P_DT_CYCLE_MTH,'MM'), DT_REMOVE_DATE
FROM PER_LIMRA WHERE CMCD=P_STR_CMCD
AND CYCLE_MTH = P_DT_CYCLE_MTH
AND AGENT_TYPE = '01' AND LIMRA_TYPE = P_STR_LIMRA_TYPE
AND EFFECTIVEEND_DT = DT_REMOVE_DATE --; --HKG001
GROUP BY POL_TYPE; --HKG001
COMMIT;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, 'SP_PER_LIMRA_CAL05 ERROR '||SQLERRM);
END;
PROCEDURE SP_PER_LIMRA_CALEXP
( P_STR_CMCD IN VARCHAR2,
P_DT_CYCLE_MTH IN DATE,
P_DT_CYCLE_MTH_END IN DATE,
P_STR_LIMRA_TYPE IN VARCHAR2,
/*P_STR_CHANNEL IN VARCHAR2,*/
P_STR_USER IN VARCHAR2
) IS
DEC_START_MTH INT;
DEC_END_MTH INT;
DEC_EXP_PERIOD INT;
STR_AGENT_TYPE VARCHAR2(2) := '00';
BEGIN
--GET THE NB PERIOD FOR EXPOSURE CAL
/* HKG001 START
IF (P_STR_LIMRA_TYPE='LIM19') THEN
DEC_START_MTH:=-20;
ELSIF (P_STR_LIMRA_TYPE='LIM13') THEN
DEC_START_MTH:=-14;
ELSIF (P_STR_LIMRA_TYPE='LIM25') THEN
DEC_START_MTH:=-26;
END IF;
*/
IF (SUBSTR(P_STR_LIMRA_TYPE, 0, 5)='LIM19') THEN
DEC_START_MTH:=-20;
ELSIF (SUBSTR(P_STR_LIMRA_TYPE, 0, 5)='LIM13') THEN
DEC_START_MTH:=-14;
ELSIF (SUBSTR(P_STR_LIMRA_TYPE, 0, 5)='LIM25') THEN
DEC_START_MTH:=-26;
END IF;
--HKG001 END
DEC_END_MTH:= -3;
/* HKG001
IF (P_STR_LIMRA_TYPE='LIM19') THEN
DEC_EXP_PERIOD:=18;
ELSIF (P_STR_LIMRA_TYPE='LIM13') THEN
DEC_EXP_PERIOD:=12;
ELSIF (P_STR_LIMRA_TYPE='LIM25') THEN
DEC_EXP_PERIOD:=25;
END IF;
*/
IF (SUBSTR(P_STR_LIMRA_TYPE, 0, 5)='LIM19') THEN
DEC_EXP_PERIOD:=18;
ELSIF (SUBSTR(P_STR_LIMRA_TYPE, 0, 5)='LIM13') THEN
DEC_EXP_PERIOD:=12;
ELSIF (SUBSTR(P_STR_LIMRA_TYPE, 0, 5)='LIM25') THEN
DEC_EXP_PERIOD:=24; --HKG001 CHANGE FROM 25 TO 24
END IF;
--HKG001 END
--CALCULATE CM EXPORSURE
/*UPDATE PER_LIMRA TAR SET
( EXP_CC_CM, EXP_SA_CM, EXP_AP_CM, EXP_CAP_CM,
UPDATE_DT, UPDATE_BY) =
( SELECT
NVL(EXP_CC_CM,0) AS EXP_CC_CM, NVL(EXP_SA_CM,0) AS EXP_SA_CM, NVL(EXP_AP_CM,0) AS EXP_AP_CM, NVL(EXP_CC_CM,0) AS EXP_CAP_CM,
SYSDATE, P_STR_USER
FROM
(
SELECT
CMCD, CYCLE_MTH, AGENT_TYPE, LIMRA_TYPE, CHANNEL, AGT, AGY, CONTRACT_DT, TERM_DT --ADD CHANNEL NEED TO CONFIRM
FROM PER_LIMRA WHERE CMCD= P_STR_CMCD \*AND CHANNEL =P_STR_CHANNEL*\ AND LIMRA_TYPE= P_STR_LIMRA_TYPE AND
CYCLE_MTH = P_DT_CYCLE_MTH
AND AGENT_TYPE =STR_AGENT_TYPE --ADD THIS TO SPEED UP
) SRC_CM LEFT JOIN
(
SELECT
CMCD, AGENT_TYPE, LIMRA_TYPE, CHANNEL, AGT, AGY, --ADD CHANNEL NEED TO CONFIRM
SUM(NB_CC_CM)/DEC_EXP_PERIOD AS EXP_CC_CM, SUM(NB_SA_CM)/DEC_EXP_PERIOD AS EXP_SA_CM, SUM(NB_AP_CM)/DEC_EXP_PERIOD AS EXP_AP_CM, SUM(NB_CAP_CM)/DEC_EXP_PERIOD AS EXP_CAP_CM
FROM PER_LIMRA WHERE CMCD= P_STR_CMCD \*AND CHANNEL =P_STR_CHANNEL*\ AND LIMRA_TYPE= P_STR_LIMRA_TYPE AND
CYCLE_MTH <= ADD_MONTHS(P_DT_CYCLE_MTH, DEC_END_MTH) AND CYCLE_MTH >= ADD_MONTHS(P_DT_CYCLE_MTH, DEC_START_MTH)
AND EFFECTIVEEND_DT = DT_REMOVE_DATE
AND AGENT_TYPE =STR_AGENT_TYPE --ADD THIS TO SPEED UP
GROUP BY CMCD, AGENT_TYPE, LIMRA_TYPE, CHANNEL, AGT, AGY
) SRC_HIST ON
SRC_CM.CMCD= SRC_HIST.CMCD
AND SRC_CM.AGENT_TYPE = SRC_HIST.AGENT_TYPE
AND SRC_CM.LIMRA_TYPE = SRC_HIST.LIMRA_TYPE
AND SRC_CM.CHANNEL = SRC_HIST.CHANNEL --ADD CHANNEL NEED TO CONFIRM
AND SRC_CM.AGT = SRC_HIST.AGT
AND NVL(SRC_CM.AGY,'-99999') =NVL(SRC_HIST.AGY,'-99999')
WHERE TAR.CMCD= SRC_CM.CMCD
AND TAR.CYCLE_MTH = SRC_CM.CYCLE_MTH
AND TAR.AGENT_TYPE = SRC_CM.AGENT_TYPE
AND TAR.LIMRA_TYPE = SRC_CM.LIMRA_TYPE
AND TAR.CHANNEL = SRC_CM.CHANNEL --ADD CHANNEL NEED TO CONFIRM
AND TAR.AGT = SRC_CM.AGT
AND NVL(TAR.AGY,'-99999') = NVL(SRC_CM.AGY,'-99999')
)
WHERE TAR.CMCD= P_STR_CMCD \*AND TAR.CHANNEL =P_STR_CHANNEL*\ AND TAR.LIMRA_TYPE= P_STR_LIMRA_TYPE AND TAR.CYCLE_MTH = P_DT_CYCLE_MTH;*/
/*UPDATE PER_LIMRA TAR SET
( EXP_CC_CM, EXP_SA_CM, EXP_AP_CM, EXP_CAP_CM,
UPDATE_DT, UPDATE_BY) =
(
SELECT
NVL(EXP_CC_CM,0) AS EXP_CC_CM, NVL(EXP_SA_CM,0) AS EXP_SA_CM, NVL(EXP_AP_CM,0) AS EXP_AP_CM, NVL(EXP_CC_CM,0) AS EXP_CAP_CM,
SYSDATE, P_STR_USER
FROM
(
SELECT
CMCD, AGENT_TYPE, LIMRA_TYPE, CHANNEL, AGT, AGY,
SUM(NB_CC_CM)/DEC_EXP_PERIOD AS EXP_CC_CM, SUM(NB_SA_CM)/DEC_EXP_PERIOD AS EXP_SA_CM, SUM(NB_AP_CM)/DEC_EXP_PERIOD AS EXP_AP_CM, SUM(NB_CAP_CM)/DEC_EXP_PERIOD AS EXP_CAP_CM
FROM PER_LIMRA WHERE CMCD= P_STR_CMCD AND LIMRA_TYPE= P_STR_LIMRA_TYPE AND
CYCLE_MTH <= ADD_MONTHS(P_DT_CYCLE_MTH, DEC_END_MTH) AND CYCLE_MTH >= ADD_MONTHS(P_DT_CYCLE_MTH, DEC_START_MTH)
AND EFFECTIVEEND_DT = DT_REMOVE_DATE
AND AGENT_TYPE ='00' --ADD THIS TO SPEED UP
GROUP BY CMCD,AGENT_TYPE, LIMRA_TYPE, CHANNEL, AGT, AGY
) SRC_CRM
WHERE TAR.CMCD= SRC_CRM.CMCD
AND TAR.AGENT_TYPE = SRC_CRM.AGENT_TYPE
AND TAR.LIMRA_TYPE = SRC_CRM.LIMRA_TYPE
AND TAR.CHANNEL = SRC_CRM.CHANNEL
AND TAR.AGT = SRC_CRM.AGT
AND NVL(TAR.AGY,'-99999') = NVL(SRC_CRM.AGY,'-99999')
)
--WHERE TAR.CMCD= P_STR_CMCD AND TAR.LIMRA_TYPE= P_STR_LIMRA_TYPE AND TAR.CYCLE_MTH = P_DT_CYCLE_MTH
WHERE TAR.CMCD= P_STR_CMCD AND TAR.LIMRA_TYPE= P_STR_LIMRA_TYPE AND TAR.CYCLE_MTH = P_DT_CYCLE_MTH
AND TAR.CMCD= CMCD
AND TAR.AGENT_TYPE = AGENT_TYPE AND TAR.LIMRA_TYPE = LIMRA_TYPE
AND TAR.AGT = AGT
AND TAR.AGY = AGY
AND TAR.CHANNEL = CHANNEL;*/
UPDATE PER_LIMRA TAR SET
( EXP_CC_CM, EXP_SA_CM, EXP_AP_CM, EXP_CAP_CM,
UPDATE_DT, UPDATE_BY) =
(
SELECT
NVL(SRC_CRM.EXP_CC_CM,0) AS EXP_CC_CM, NVL(SRC_CRM.EXP_SA_CM,0) AS EXP_SA_CM, NVL(SRC_CRM.EXP_AP_CM,0) AS EXP_AP_CM, NVL(SRC_CRM.EXP_CAP_CM,0) AS EXP_CAP_CM,
SYSDATE, P_STR_USER
FROM
(
SELECT
CMCD, AGENT_TYPE, LIMRA_TYPE, AGT, AGY, POL_TYPE, --HKG001
SUM(NB_CC_CM)/DEC_EXP_PERIOD AS EXP_CC_CM, SUM(NB_SA_CM)/DEC_EXP_PERIOD AS EXP_SA_CM, SUM(NB_AP_CM)/DEC_EXP_PERIOD AS EXP_AP_CM, SUM(NB_CAP_CM)/DEC_EXP_PERIOD AS EXP_CAP_CM
FROM PER_LIMRA WHERE CMCD= P_STR_CMCD AND LIMRA_TYPE= P_STR_LIMRA_TYPE AND
CYCLE_MTH <= ADD_MONTHS(P_DT_CYCLE_MTH, DEC_END_MTH) AND CYCLE_MTH >= ADD_MONTHS(P_DT_CYCLE_MTH, DEC_START_MTH)
AND EFFECTIVEEND_DT = DT_REMOVE_DATE
AND AGENT_TYPE ='00' --ADD THIS TO SPEED UP
GROUP BY CMCD,AGENT_TYPE, LIMRA_TYPE, AGT, AGY, POL_TYPE --HKG001
HAVING SUM(NB_CC_CM) <>0 OR SUM(NB_SA_CM)<>0 OR SUM(NB_AP_CM) <>0 OR SUM(NB_CAP_CM)<>0
) SRC_CRM
WHERE TAR.CMCD= SRC_CRM.CMCD
AND TAR.AGENT_TYPE = SRC_CRM.AGENT_TYPE
AND TAR.LIMRA_TYPE = SRC_CRM.LIMRA_TYPE
--AND NVL(TAR.CHANNEL, '*')= NVL(CHANNEL, '*')
AND TAR.AGT = SRC_CRM.AGT
AND NVL(TAR.AGY,'-99999') = NVL(SRC_CRM.AGY,'-99999')
AND ( SRC_CRM.EXP_CC_CM IS NOT NULL OR SRC_CRM.EXP_SA_CM IS NOT NULL OR SRC_CRM.EXP_AP_CM IS NOT NULL OR SRC_CRM.EXP_CAP_CM IS NOT NULL)
AND TAR.CYCLE_MTH = P_DT_CYCLE_MTH
AND TAR.POL_TYPE = SRC_CRM.POL_TYPE --HKG001
)
--WHERE TAR.CMCD= P_STR_CMCD AND TAR.LIMRA_TYPE= P_STR_LIMRA_TYPE AND TAR.CYCLE_MTH = P_DT_CYCLE_MTH
WHERE TAR.CMCD= P_STR_CMCD AND TAR.LIMRA_TYPE= P_STR_LIMRA_TYPE AND TAR.CYCLE_MTH = P_DT_CYCLE_MTH
/*
AND TAR.CMCD= CMCD
AND TAR.AGENT_TYPE = AGENT_TYPE AND TAR.LIMRA_TYPE = LIMRA_TYPE
-- AND TAR.CHANNEL = SRC_CM.CHANNEL
AND TAR.AGT = AGT
AND NVL(TAR.AGY,'-99999') = NVL(AGY,'-99999')
AND NVL(TAR.CHANNEL, '*')= NVL(CHANNEL, '*')
AND (NVL(EXP_CC_CM,0) <>0 OR NVL(EXP_SA_CM,0) <>0 OR NVL(EXP_AP_CM,0) <>0 OR NVL(EXP_CC_CM,0) <>0 ) */
AND EXISTS (
SELECT
1
/* NVL(SRC_CRM.EXP_CC_CM,0) AS EXP_CC_CM, NVL(SRC_CRM.EXP_SA_CM,0) AS EXP_SA_CM, NVL(SRC_CRM.EXP_AP_CM,0) AS EXP_AP_CM, NVL(SRC_CRM.EXP_CC_CM,0) AS EXP_CAP_CM,
SYSDATE, P_STR_USER*/
FROM
(
SELECT
CMCD, AGENT_TYPE, LIMRA_TYPE, AGT, AGY, POL_TYPE, --HKG001
SUM(NB_CC_CM)/DEC_EXP_PERIOD AS EXP_CC_CM, SUM(NB_SA_CM)/DEC_EXP_PERIOD AS EXP_SA_CM, SUM(NB_AP_CM)/DEC_EXP_PERIOD AS EXP_AP_CM, SUM(NB_CAP_CM)/DEC_EXP_PERIOD AS EXP_CAP_CM
FROM PER_LIMRA WHERE CMCD= P_STR_CMCD AND LIMRA_TYPE= P_STR_LIMRA_TYPE AND
CYCLE_MTH <= ADD_MONTHS(P_DT_CYCLE_MTH, DEC_END_MTH) AND CYCLE_MTH >= ADD_MONTHS(P_DT_CYCLE_MTH, DEC_START_MTH)
AND EFFECTIVEEND_DT = DT_REMOVE_DATE
AND AGENT_TYPE ='00' --ADD THIS TO SPEED UP
GROUP BY CMCD,AGENT_TYPE, LIMRA_TYPE, AGT, AGY, POL_TYPE --HKG001
HAVING SUM(NB_CC_CM) <>0 OR SUM(NB_SA_CM)<>0 OR SUM(NB_AP_CM) <>0 OR SUM(NB_CAP_CM)<>0
) SRC_CRM
WHERE TAR.CMCD= SRC_CRM.CMCD
AND TAR.AGENT_TYPE = SRC_CRM.AGENT_TYPE
AND TAR.LIMRA_TYPE = SRC_CRM.LIMRA_TYPE
--AND NVL(TAR.CHANNEL, '*')= NVL(CHANNEL, '*')
AND TAR.AGT = SRC_CRM.AGT
AND NVL(TAR.AGY,'-99999') = NVL(SRC_CRM.AGY,'-99999')
AND ( SRC_CRM.EXP_CC_CM IS NOT NULL OR SRC_CRM.EXP_SA_CM IS NOT NULL OR SRC_CRM.EXP_AP_CM IS NOT NULL OR SRC_CRM.EXP_CAP_CM IS NOT NULL)
AND TAR.CYCLE_MTH = P_DT_CYCLE_MTH
AND TAR.POL_TYPE = SRC_CRM.POL_TYPE --HKG001
)
;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, ' SP_PER_LIMRA_CALEXP ERROR '||SQLERRM);
END;
PROCEDURE SP_PER_LIMRA_CALSUM
( P_STR_CMCD IN VARCHAR2,
P_DT_CYCLE_MTH IN DATE,
P_DT_CYCLE_MTH_END IN DATE,
P_STR_LIMRA_TYPE IN VARCHAR2,
/*P_STR_CHANNEL IN VARCHAR2,*/
P_STR_USER IN VARCHAR2
) IS
DT_START_MTH DATE;
DT_CYCLE_YTD DATE;
STR_AGENT_TYPE VARCHAR2(2) := '00';
BEGIN
/* HKG001 START
IF (P_STR_LIMRA_TYPE='LIM19') THEN
DT_START_MTH:=ADD_MONTHS(P_DT_CYCLE_MTH, -11);
ELSIF (P_STR_LIMRA_TYPE='LIM13') THEN
DT_START_MTH:=ADD_MONTHS(P_DT_CYCLE_MTH, -11);
ELSIF (P_STR_LIMRA_TYPE='LIM25') THEN
DT_START_MTH:=ADD_MONTHS(P_DT_CYCLE_MTH, -11);
END IF;
*/
IF (SUBSTR(P_STR_LIMRA_TYPE, 0, 5)='LIM19') THEN
DT_START_MTH:=ADD_MONTHS(P_DT_CYCLE_MTH, -11);
ELSIF (SUBSTR(P_STR_LIMRA_TYPE, 0, 5)='LIM13') THEN
DT_START_MTH:=ADD_MONTHS(P_DT_CYCLE_MTH, -11);
ELSIF (SUBSTR(P_STR_LIMRA_TYPE, 0, 5)='LIM25') THEN
DT_START_MTH:=ADD_MONTHS(P_DT_CYCLE_MTH, -11);
END IF;
--HKG001
IF (TO_CHAR(P_DT_CYCLE_MTH,'MM')= '12') THEN
DT_CYCLE_YTD:= P_DT_CYCLE_MTH;
ELSE
DT_CYCLE_YTD := TO_DATE(TO_CHAR(TO_NUMBER(TO_CHAR(P_DT_CYCLE_MTH,'YYYY')-1))||'1231', 'YYYYMMDD');
END IF;
EXECUTE IMMEDIATE 'TRUNCATE TABLE PER_LIMRA_TMP';
INSERT INTO PER_LIMRA_TMP(CMCD, AGENT_TYPE, LIMRA_TYPE, CHANNEL, AGT, AGY, POL_TYPE, --HKG001
EXP_CC_P12, EXP_SA_P12, EXP_AP_P12, EXP_CAP_P12,
LAPSE_CC_P12, LAPSE_SA_P12, LAPSE_AP_P12, LAPSE_CAP_P12,
REINSTATE_CC_P12, REINSTATE_SA_P12, REINSTATE_AP_P12, REINSTATE_CAP_P12,
EXP_CC_YTD, EXP_SA_YTD, EXP_AP_YTD, EXP_CAP_YTD,
LAPSE_CC_YTD, LAPSE_SA_YTD, LAPSE_AP_YTD, LAPSE_CAP_YTD,
REINSTATE_CC_YTD, REINSTATE_SA_YTD, REINSTATE_AP_YTD, REINSTATE_CAP_YTD )
SELECT
CMCD, AGENT_TYPE, LIMRA_TYPE, '', AGT, AGY, POL_TYPE, --HKG001
SUM(EXP_CC_CM) AS EXP_CC_P12, SUM(EXP_SA_CM) AS EXP_SA_P12, SUM(EXP_AP_CM) AS EXP_AP_P12, SUM(EXP_CAP_CM) AS EXP_CAP_P12,
SUM(LAPSE_CC_CM) AS LAPSE_CC_P12, SUM(LAPSE_SA_CM) AS LAPSE_SA_P12, SUM(LAPSE_AP_CM) AS LAPSE_AP_P12, SUM(LAPSE_CAP_CM) AS LAPSE_CAP_P12,
SUM(REINSTATE_CC_CM) AS REINSTATE_CC_P12, SUM(REINSTATE_SA_CM) AS REINSTATE_SA_P12, SUM(REINSTATE_AP_CM) AS REINSTATE_AP_P12, SUM(REINSTATE_CAP_CM) AS REINSTATE_CAP_P12,
SUM(CASE WHEN CYCLE_MTH >= DT_CYCLE_YTD THEN EXP_CC_CM ELSE 0 END) AS EXP_CC_YTD, SUM(CASE WHEN CYCLE_MTH >= DT_CYCLE_YTD THEN EXP_SA_CM ELSE 0 END) AS EXP_SA_YTD, SUM(CASE WHEN CYCLE_MTH >= DT_CYCLE_YTD THEN EXP_AP_CM ELSE 0 END) AS EXP_AP_YTD, SUM(CASE WHEN CYCLE_MTH >= DT_CYCLE_YTD THEN EXP_CAP_CM ELSE 0 END) AS EXP_CAP_YTD,
SUM(CASE WHEN CYCLE_MTH >= DT_CYCLE_YTD THEN LAPSE_CC_CM ELSE 0 END) AS LAPSE_CC_YTD, SUM(CASE WHEN CYCLE_MTH >= DT_CYCLE_YTD THEN LAPSE_SA_CM ELSE 0 END) AS LAPSE_SA_YTD, SUM(CASE WHEN CYCLE_MTH >= DT_CYCLE_YTD THEN LAPSE_AP_CM ELSE 0 END) AS LAPSE_AP_YTD, SUM(CASE WHEN CYCLE_MTH >= DT_CYCLE_YTD THEN LAPSE_CAP_CM ELSE 0 END) AS LAPSE_CAP_YTD,
SUM(CASE WHEN CYCLE_MTH >= DT_CYCLE_YTD THEN REINSTATE_CC_CM ELSE 0 END) AS REINSTATE_CC_YTD, SUM(CASE WHEN CYCLE_MTH >= DT_CYCLE_YTD THEN REINSTATE_SA_CM ELSE 0 END) AS REINSTATE_SA_YTD, SUM(CASE WHEN CYCLE_MTH >= DT_CYCLE_YTD THEN REINSTATE_AP_CM ELSE 0 END) AS REINSTATE_AP_YTD, SUM(CASE WHEN CYCLE_MTH >= DT_CYCLE_YTD THEN REINSTATE_CAP_CM ELSE 0 END) AS REINSTATE_CAP_YTD
FROM (
SELECT * FROM PER_LIMRA A
WHERE A.CMCD= P_STR_CMCD
AND A.AGENT_TYPE = STR_AGENT_TYPE
AND A.LIMRA_TYPE = P_STR_LIMRA_TYPE
AND A.EFFECTIVEEND_DT = DT_REMOVE_DATE
--AND A.AGENT_TYPE = STR_AGENT_TYPE --ADD THIS TO SPEED UP
--AND A.CMCD= P_STR_CMCD
--AND A.LIMRA_TYPE = P_STR_LIMRA_TYPE
AND A.CYCLE_MTH <= P_DT_CYCLE_MTH AND A.CYCLE_MTH >= DT_START_MTH
--AND A.CHANNEL = CHANNEL
--AND A.AGT = AGT
--AND A.AGY = AGY
/* UNION ALL
SELECT * FROM PER_LIMRA_CUR A
WHERE A.CMCD= P_STR_CMCD AND A.LIMRA_TYPE= P_STR_LIMRA_TYPE AND
A.CYCLE_MTH <= P_DT_CYCLE_MTH AND A.CYCLE_MTH >= DT_START_MTH
AND A.EFFECTIVEEND_DT = DT_REMOVE_DATE
AND A.AGENT_TYPE = STR_AGENT_TYPE --ADD THIS TO SPEED UP
AND A.CMCD= P_STR_CMCD
AND A.AGENT_TYPE = STR_AGENT_TYPE
AND A.LIMRA_TYPE = P_STR_LIMRA_TYPE
--AND A.CHANNEL = CHANNEL
--AND A.AGT = AGT
--AND A.AGY = AGY*/
) PER_LIMRA
GROUP BY CMCD, AGENT_TYPE, LIMRA_TYPE, AGT, AGY, POL_TYPE; --HKG001
COMMIT;
UPDATE PER_LIMRA TAR SET
( EXP_CC_P12, EXP_SA_P12, EXP_AP_P12, EXP_CAP_P12,
LAPSE_CC_P12, LAPSE_SA_P12, LAPSE_AP_P12, LAPSE_CAP_P12,
REINSTATE_CC_P12, REINSTATE_SA_P12, REINSTATE_AP_P12, REINSTATE_CAP_P12,
EXP_CC_YTD, EXP_SA_YTD, EXP_AP_YTD, EXP_CAP_YTD,
LAPSE_CC_YTD, LAPSE_SA_YTD, LAPSE_AP_YTD, LAPSE_CAP_YTD,
REINSTATE_CC_YTD, REINSTATE_SA_YTD, REINSTATE_AP_YTD, REINSTATE_CAP_YTD,
UPDATE_DT, UPDATE_BY) =
( SELECT
EXP_CC_P12, EXP_SA_P12, EXP_AP_P12, EXP_CAP_P12,
LAPSE_CC_P12, LAPSE_SA_P12, LAPSE_AP_P12, LAPSE_CAP_P12,
REINSTATE_CC_P12, REINSTATE_SA_P12, REINSTATE_AP_P12, REINSTATE_CAP_P12,
EXP_CC_YTD, EXP_SA_YTD, EXP_AP_YTD, EXP_CAP_YTD,
LAPSE_CC_YTD, LAPSE_SA_YTD, LAPSE_AP_YTD, LAPSE_CAP_YTD,
REINSTATE_CC_YTD, REINSTATE_SA_YTD, REINSTATE_AP_YTD, REINSTATE_CAP_YTD,
SYSDATE, P_STR_USER
FROM PER_LIMRA_TMP
WHERE TAR.POL_TYPE = POL_TYPE --HKG001
AND TAR.AGT = AGT
AND TAR.AGY = AGY
AND TAR.AGENT_TYPE= AGENT_TYPE
AND TAR.LIMRA_TYPE = LIMRA_TYPE
)
WHERE
TAR.CMCD= P_STR_CMCD AND TAR.POL_TYPE = POL_TYPE --HKG001 FIXING BUG -- LOSING DATA BY OVERRITING PREIOUS PRC VALUE
AND TAR.LIMRA_TYPE= P_STR_LIMRA_TYPE AND TAR.CYCLE_MTH = P_DT_CYCLE_MTH
AND TAR.AGENT_TYPE = STR_AGENT_TYPE
AND EXISTS (
SELECT 1 FROM PER_LIMRA_TMP
WHERE TAR.AGT = AGT
AND TAR.AGY = AGY)
;
COMMIT;
/* UPDATE PER_LIMRA TAR SET
( EXP_CC_P12, EXP_SA_P12, EXP_AP_P12, EXP_CAP_P12,
LAPSE_CC_P12, LAPSE_SA_P12, LAPSE_AP_P12, LAPSE_CAP_P12,
REINSTATE_CC_P12, REINSTATE_SA_P12, REINSTATE_AP_P12, REINSTATE_CAP_P12,
EXP_CC_YTD, EXP_SA_YTD, EXP_AP_YTD, EXP_CAP_YTD,
LAPSE_CC_YTD, LAPSE_SA_YTD, LAPSE_AP_YTD, LAPSE_CAP_YTD,
REINSTATE_CC_YTD, REINSTATE_SA_YTD, REINSTATE_AP_YTD, REINSTATE_CAP_YTD,
UPDATE_DT, UPDATE_BY) =
( SELECT
SUM(EXP_CC_CM) AS EXP_CC_P12, SUM(EXP_SA_CM) AS EXP_SA_P12, SUM(EXP_AP_CM) AS EXP_AP_P12, SUM(EXP_CAP_CM) AS EXP_CAP_P12,
SUM(LAPSE_CC_CM) AS LAPSE_CC_P12, SUM(LAPSE_SA_CM) AS LAPSE_SA_P12, SUM(LAPSE_AP_CM) AS LAPSE_AP_P12, SUM(LAPSE_CAP_CM) AS LAPSE_CAP_P12,
SUM(REINSTATE_CC_CM) AS REINSTATE_CC_P12, SUM(REINSTATE_SA_CM) AS REINSTATE_SA_P12, SUM(REINSTATE_AP_CM) AS REINSTATE_AP_P12, SUM(REINSTATE_CAP_CM) AS REINSTATE_CAP_P12,
SUM(CASE WHEN CYCLE_MTH >= DT_CYCLE_YTD THEN EXP_CC_CM ELSE 0 END) AS EXP_CC_YTD, SUM(CASE WHEN CYCLE_MTH >= DT_CYCLE_YTD THEN EXP_SA_CM ELSE 0 END) AS EXP_SA_YTD, SUM(CASE WHEN CYCLE_MTH >= DT_CYCLE_YTD THEN EXP_AP_CM ELSE 0 END) AS EXP_AP_YTD, SUM(CASE WHEN CYCLE_MTH >= DT_CYCLE_YTD THEN EXP_CAP_CM ELSE 0 END) AS EXP_CAP_YTD,
SUM(CASE WHEN CYCLE_MTH >= DT_CYCLE_YTD THEN LAPSE_CC_CM ELSE 0 END) AS LAPSE_CC_YTD, SUM(CASE WHEN CYCLE_MTH >= DT_CYCLE_YTD THEN LAPSE_SA_CM ELSE 0 END) AS LAPSE_SA_YTD, SUM(CASE WHEN CYCLE_MTH >= DT_CYCLE_YTD THEN LAPSE_AP_CM ELSE 0 END) AS LAPSE_AP_YTD, SUM(CASE WHEN CYCLE_MTH >= DT_CYCLE_YTD THEN LAPSE_CAP_CM ELSE 0 END) AS LAPSE_CAP_YTD,
SUM(CASE WHEN CYCLE_MTH >= DT_CYCLE_YTD THEN REINSTATE_CC_CM ELSE 0 END) AS REINSTATE_CC_YTD, SUM(CASE WHEN CYCLE_MTH >= DT_CYCLE_YTD THEN REINSTATE_SA_CM ELSE 0 END) AS REINSTATE_SA_YTD, SUM(CASE WHEN CYCLE_MTH >= DT_CYCLE_YTD THEN REINSTATE_AP_CM ELSE 0 END) AS REINSTATE_AP_YTD, SUM(CASE WHEN CYCLE_MTH >= DT_CYCLE_YTD THEN REINSTATE_CAP_CM ELSE 0 END) AS REINSTATE_CAP_YTD,
SYSDATE, P_STR_USER
FROM PER_LIMRA
WHERE CMCD= P_STR_CMCD \*AND CHANNEL =P_STR_CHANNEL*\ AND LIMRA_TYPE= P_STR_LIMRA_TYPE AND
CYCLE_MTH <= P_DT_CYCLE_MTH AND CYCLE_MTH >= DT_START_MTH
AND EFFECTIVEEND_DT = DT_REMOVE_DATE
AND AGENT_TYPE = STR_AGENT_TYPE --ADD THIS TO SPEED UP
AND TAR.CMCD= CMCD
AND TAR.AGENT_TYPE = AGENT_TYPE AND TAR.LIMRA_TYPE = LIMRA_TYPE
\*AND TAR.CHANNEL = SRC_CM.CHANNEL*\
AND TAR.AGT = AGT
AND TAR.AGY = AGY
AND TAR.CHANNEL = CHANNEL --ADD CHANNEL NEED TO CONFIRM
GROUP BY CMCD, AGENT_TYPE, LIMRA_TYPE, CHANNEL, AGT, AGY
)
WHERE
TAR.CMCD= P_STR_CMCD\*AND TAR.CHANNEL =P_STR_CHANNEL*\ AND TAR.LIMRA_TYPE= P_STR_LIMRA_TYPE AND TAR.CYCLE_MTH = P_DT_CYCLE_MTH
AND TAR.CMCD= CMCD
AND TAR.AGENT_TYPE = AGENT_TYPE AND TAR.LIMRA_TYPE = LIMRA_TYPE
\*AND TAR.CHANNEL = SRC_CM.CHANNEL*\
AND TAR.AGT = AGT
AND TAR.AGY = AGY
AND TAR.CHANNEL = CHANNEL; --ADD CHANNEL NEED TO CONFIRM*/
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, ' SP_PER_LIMRA_CALSUM ERROR '||SQLERRM);
END;
PROCEDURE SP_PER_LIMRA_CALPER
( P_STR_CMCD IN VARCHAR2,
P_DT_CYCLE_MTH IN DATE,
P_STR_LIMRA_TYPE IN VARCHAR2,
/*P_STR_CHANNEL IN VARCHAR2,*/
P_STR_USER IN VARCHAR2
) IS
BEGIN
--CALL FUNCTION TO CALCULATE PERSISTENCY.
UPDATE PER_LIMRA SET
PER_CC_CM = FN_PER_LIMRA(LAPSE_CC_CM, REINSTATE_CC_CM, EXP_CC_CM),
PER_SA_CM = FN_PER_LIMRA(LAPSE_SA_CM, REINSTATE_SA_CM, EXP_SA_CM),
PER_AP_CM = FN_PER_LIMRA(LAPSE_AP_CM, REINSTATE_AP_CM, EXP_AP_CM),
PER_CAP_CM = FN_PER_LIMRA(LAPSE_CAP_CM, REINSTATE_CAP_CM, EXP_CAP_CM),
PER_CC_YTD = FN_PER_LIMRA(LAPSE_CC_YTD, REINSTATE_CC_YTD, EXP_CC_YTD),
PER_SA_YTD = FN_PER_LIMRA(LAPSE_SA_YTD, REINSTATE_SA_YTD, EXP_SA_YTD),
PER_AP_YTD = FN_PER_LIMRA(LAPSE_AP_YTD, REINSTATE_AP_YTD, EXP_AP_YTD),
PER_CAP_YTD = FN_PER_LIMRA(LAPSE_CAP_YTD, REINSTATE_CAP_YTD, EXP_CAP_YTD),
PER_CC_P12 = FN_PER_LIMRA(LAPSE_CC_P12, REINSTATE_CC_P12, EXP_CC_P12),
PER_SA_P12 = FN_PER_LIMRA(LAPSE_SA_P12, REINSTATE_SA_P12, EXP_SA_P12),
PER_AP_P12 = FN_PER_LIMRA(LAPSE_AP_P12, REINSTATE_AP_P12, EXP_AP_P12),
PER_CAP_P12 = FN_PER_LIMRA(LAPSE_CAP_P12, REINSTATE_CAP_P12, EXP_CAP_P12),
UPDATE_DT= SYSDATE
WHERE
CMCD= P_STR_CMCD /*AND CHANNEL =P_STR_CHANNEL*/ AND LIMRA_TYPE= P_STR_LIMRA_TYPE AND
CYCLE_MTH = P_DT_CYCLE_MTH;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, ' SP_PER_LIMRA_CALPER ERROR '||SQLERRM);
END;
PROCEDURE SP_PER_LIMRA_LOOP
( P_STR_CMCD IN VARCHAR2,
P_DT_CYCLE_DT IN DATE,
/*P_STR_CHANNEL IN VARCHAR2,*/
P_STR_USER IN VARCHAR2
) IS
DEC_TOTAL_CYCLE INT;
DT_CYCLE_DT DATE;
BEGIN
DBMS_OUTPUT.PUT_LINE ('RUN CYCLE: '||TO_CHAR(P_DT_CYCLE_DT,'YYYY-MM-DD'));
SP_PER_LIMRA(P_STR_CMCD, P_DT_CYCLE_DT, /*P_STR_CHANNEL,*/ P_STR_USER);
EXCEPTION
/*WHEN INVALID_CYCLE THEN
RAISE_APPLICATION_ERROR(-20001, 'SP_PER_LIMRA_LOOP WITH INVALID CYCLE IN ST_INBOUND_CONTROL '||SQLERRM);*/
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, 'SP_PER_LIMRA_LOOP ERROR '||SQLERRM);
END;
PROCEDURE SP_PER_LIMRA
( P_STR_CMCD IN VARCHAR2,
P_DT_CYCLE_DT IN DATE,
/*P_STR_CHANNEL IN VARCHAR2,*/
P_STR_USER IN VARCHAR2
) IS
DT_CYCLE_DT DATE;
DT_CYCLE_MTH DATE;
DT_CYCLE_MTH_START DATE;
DT_CYCLE_MTH_END DATE;
STR_LIM13_TYPE VARCHAR2(10) := 'LIM13';
STR_LIM19_TYPE VARCHAR2(10) := 'LIM19';
STR_LIMPI_TYPE VARCHAR2(10) := 'LIMPI';
/*STR_AGY_CHANNEL VARCHAR2(5) := 'AGY';
STR_PD_CHANNEL VARCHAR2(5) := 'PD';*/
BEGIN
--IF CYCLE DATE IS NULL THEN GET THE CYCLEDATE FROM ST_INBOUND_CONTROL TABLE
/* IF (P_DT_CYCLE_DT IS NULL) THEN
SELECT TO_DATE(TXT_KEY_VALUE, 'YYYY-MM-DD') INTO DT_CYCLE_DT FROM IN_ETL_CONTROL WHERE TXT_KEY_STRING ='PER_FILE_CYCLE_DATE' AND TXT_FILE_NAME = P_STR_CMCD||'_LIMRA';
ELSE*/
DT_CYCLE_DT := P_DT_CYCLE_DT;
--END IF;
DBMS_OUTPUT.PUT_LINE('CYCLE_DT' || DT_CYCLE_DT);
DT_CYCLE_MTH:=LAST_DAY(DT_CYCLE_DT);
DT_CYCLE_MTH_START := TRUNC(DT_CYCLE_DT,'MM');
DT_CYCLE_MTH_END := ADD_MONTHS(TRUNC(DT_CYCLE_MTH,'MM'), 1);
--CLEAN DATA
--DELETE FROM PER_LIMRA WHERE CYCLE_MTH=DT_CYCLE_MTH AND CMCD= P_STR_CMCD /*AND CHANNEL=P_STR_CHANNEL*/; --HKG008
--DELETE FROM PER_LIMRA WHERE CYCLE_MTH=DT_CYCLE_MTH AND CMCD= P_STR_CMCD AND LIMRA_TYPE IN ('LIM13','LIM19'); --HKG008
DELETE FROM PER_LIMRA WHERE CMCD= P_STR_CMCD AND CYCLE_MTH=DT_CYCLE_MTH AND LIMRA_TYPE IN ('LIM13','LIM19'); --HKG009
COMMIT;
--CLEAN REPORT DATA
/* DELETE FROM PER_LIMRA_REPORT WHERE CYCLE_MTH=DT_CYCLE_MTH AND CMCD= P_STR_CMCD \*AND CHANNEL=P_STR_CHANNEL*\;
COMMIT;*/
--STEP 1 ADJUSTMENT HANDLING
/* DBMS_OUTPUT.PUT_LINE('STEP 1 START AT '||SYSDATE );
SP_PER_LIMRA_CALADJ( P_STR_CMCD, DT_CYCLE_MTH_START, DT_CYCLE_MTH_END, STR_LIM13_TYPE, P_STR_USER);
SP_PER_LIMRA_CALADJ( P_STR_CMCD, DT_CYCLE_MTH_START, DT_CYCLE_MTH_END, STR_LIM19_TYPE, P_STR_USER);
*/
--STEP 2 CALCULATE 00 AGENT AGENCY
DBMS_OUTPUT.PUT_LINE('STEP 2 START AT '||TO_CHAR(SYSDATE,'YY-MM-DD HH24:MI:SS') );
SP_PER_LIMRA_CAL00( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM13_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL00( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM19_TYPE, P_STR_USER);
--STEP 3 CALCULATE EXPOSURE OF EVERY AGENT AGENCY
DBMS_OUTPUT.PUT_LINE('STEP 3 START AT '||TO_CHAR(SYSDATE,'YY-MM-DD HH24:MI:SS'));
SP_PER_LIMRA_CALEXP( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM13_TYPE, P_STR_USER);
SP_PER_LIMRA_CALEXP( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM19_TYPE, P_STR_USER);
--STEP 4 CALCULATE LAPSE,REINSTATE AND EXPOSURES OF P12,YTD ON AGENT AGENCY LEVEL.
DBMS_OUTPUT.PUT_LINE('STEP 4 START AT '||TO_CHAR(SYSDATE,'YY-MM-DD HH24:MI:SS') );
SP_PER_LIMRA_CALSUM( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM13_TYPE, P_STR_USER);
SP_PER_LIMRA_CALSUM( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM19_TYPE, P_STR_USER);
--STEP 5 CALCULATE 01 AGENT LEVEL.CURRENT MONTH, P12 AND YTD OF LAPSE,REINSTATE AND EXPOSURES
DBMS_OUTPUT.PUT_LINE('STEP 5 START AT '||TO_CHAR(SYSDATE,'YY-MM-DD HH24:MI:SS') );
SP_PER_LIMRA_CAL01( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM13_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL01( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM19_TYPE, P_STR_USER);
--STEP 6 CALCULATE 02 AGENCY LEVEL.CURRENT MONTH, P12 AND YTD OF LAPSE,REINSTATE AND EXPOSURES.
DBMS_OUTPUT.PUT_LINE('STEP 6 START AT '||TO_CHAR(SYSDATE,'YY-MM-DD HH24:MI:SS') );
SP_PER_LIMRA_CAL02( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM13_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL02( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM19_TYPE, P_STR_USER);
---NO NEED PI FOR TH, GARY ON 20130813
--STEP 7 CALCULATE 02 AGENCY LEVEL FOR PI.CURRENT MONTH, P12 AND YTD OF LAPSE,REINSTATE AND EXPOSURES.
--DBMS_OUTPUT.PUT_LINE('STEP 7' );
--SP_PER_LIMRA_CAL02PI( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIMPI_TYPE, P_STR_USER);
--STEP 8 CALCULATE 03 DISTRICT LEVEL.CURRENT MONTH, P12 AND YTD OF LAPSE,REINSTATE AND EXPOSURES.
DBMS_OUTPUT.PUT_LINE('STEP 8 START AT '||TO_CHAR(SYSDATE,'YY-MM-DD HH24:MI:SS') );
SP_PER_LIMRA_CAL03( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM13_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL03( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM19_TYPE, P_STR_USER);
---NO NEED PI FOR TH, GARY ON 20130813
--STEP 9 CALCULATE 03PI(FOR PI BONUS) DISTRICT LEVEL.CURRENT MONTH, P12 AND YTD OF LAPSE,REINSTATE AND EXPOSURES.
-- DBMS_OUTPUT.PUT_LINE('STEP 9' );
-- SP_PER_LIMRA_CAL03PI( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIMPI_TYPE, P_STR_USER);
--STEP 10 CALCULATE 04 CHANNEL LEVEL.CURRENT MONTH, P12 AND YTD OF LAPSE,REINSTATE AND EXPOSURES.
DBMS_OUTPUT.PUT_LINE('STEP 10 START AT '||TO_CHAR(SYSDATE,'YY-MM-DD HH24:MI:SS') );
SP_PER_LIMRA_CAL04( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM13_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL04( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM19_TYPE, P_STR_USER);
--STEP 11 CALCULATE 05 COMPANY LEVEL.CURRENT MONTH, P12 AND YTD OF LAPSE,REINSTATE AND EXPOSURES.
DBMS_OUTPUT.PUT_LINE('STEP 11 START AT '||TO_CHAR(SYSDATE,'YY-MM-DD HH24:MI:SS') );
SP_PER_LIMRA_CAL05( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM13_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL05( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM19_TYPE, P_STR_USER);
--LAST STEP CALCULATE PERSISTENCY
DBMS_OUTPUT.PUT_LINE('STEP 12 START AT '||TO_CHAR(SYSDATE,'YY-MM-DD HH24:MI:SS') );
SP_PER_LIMRA_CALPER( P_STR_CMCD, DT_CYCLE_MTH, STR_LIM13_TYPE, P_STR_USER);
SP_PER_LIMRA_CALPER( P_STR_CMCD, DT_CYCLE_MTH, STR_LIM19_TYPE, P_STR_USER);
--SP_PER_LIMRA_CALPER( P_STR_CMCD, DT_CYCLE_MTH, STR_LIMPI_TYPE, P_STR_USER); --HKG001 NEED TO REMOVE?
DBMS_OUTPUT.PUT_LINE('STEP 12 END AT '||TO_CHAR(SYSDATE,'YY-MM-DD HH24:MI:SS') ); --HKG003
---NO NEED FOR TH, GARY ON 20130813
--GENERATE RECORDS TO PER_LIMRA_REPORT TABLE FOR REPORT SHOW
/*SP_PER_LIMRA_REPORT( P_STR_CMCD, DT_CYCLE_MTH, STR_LIM13_TYPE, P_STR_USER);
SP_PER_LIMRA_REPORT( P_STR_CMCD, DT_CYCLE_MTH, STR_LIM19_TYPE, P_STR_USER);
SP_PER_LIMRA_REPORT( P_STR_CMCD, DT_CYCLE_MTH, STR_LIMPI_TYPE, P_STR_USER);*/
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, 'SP_PER_LIMRA ERROR '||SQLERRM);
END;
--HKG007: START
PROCEDURE SP_PER_LIMRA_LOOP2
( P_STR_CMCD IN VARCHAR2,
P_DT_CYCLE_DT IN DATE,
P_STR_USER IN VARCHAR2
) IS
BEGIN
DBMS_OUTPUT.PUT_LINE ('RUN CYCLE: '||TO_CHAR(P_DT_CYCLE_DT,'YYYY-MM-DD'));
SP_PER_LIMRA2(P_STR_CMCD, P_DT_CYCLE_DT, /*P_STR_CHANNEL,*/ P_STR_USER);
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, 'SP_PER_LIMRA_LOOP2 ERROR '||SQLERRM);
END;
PROCEDURE SP_PER_LIMRA2
( P_STR_CMCD IN VARCHAR2,
P_DT_CYCLE_DT IN DATE,
/*P_STR_CHANNEL IN VARCHAR2,*/
P_STR_USER IN VARCHAR2
) IS
DT_CYCLE_DT DATE;
DT_CYCLE_MTH DATE;
DT_CYCLE_MTH_START DATE;
DT_CYCLE_MTH_END DATE;
STR_LIM25_TYPE VARCHAR2(10) := 'LIM25'; --HKG008 --HKG009
STR_LIM13P_TYPE VARCHAR2(10) := 'LIM13P';
STR_LIM19P_TYPE VARCHAR2(10) := 'LIM19P';
STR_LIM25P_TYPE VARCHAR2(10) := 'LIM25P';
--HKG009
/*
STR_LIM13V_TYPE VARCHAR2(10) := 'LIM13V';
STR_LIM19V_TYPE VARCHAR2(10) := 'LIM19V';
STR_LIM25V_TYPE VARCHAR2(10) := 'LIM25V';
STR_LIM13N_TYPE VARCHAR2(10) := 'LIM13N';
STR_LIM19N_TYPE VARCHAR2(10) := 'LIM19N';
STR_LIM25N_TYPE VARCHAR2(10) := 'LIM25N'; --HKG008
*/
--HKG009
BEGIN
DT_CYCLE_DT := P_DT_CYCLE_DT;
DBMS_OUTPUT.PUT_LINE('CYCLE_DT' || DT_CYCLE_DT);
DT_CYCLE_MTH:=LAST_DAY(DT_CYCLE_DT);
DT_CYCLE_MTH_START := TRUNC(DT_CYCLE_DT,'MM');
DT_CYCLE_MTH_END := ADD_MONTHS(TRUNC(DT_CYCLE_MTH,'MM'), 1);
--CLEAN DATA
--DELETE FROM PER_LIMRA WHERE CYCLE_MTH=DT_CYCLE_MTH AND CMCD= P_STR_CMCD AND LIMRA_TYPE IN ('LIM13P','LIM19P','LIM25P','LIM13V','LIM19V','LIM25V','LIM13N','LIM19N','LIM25N'); --HKG009
--DELETE FROM PER_LIMRA WHERE CYCLE_MTH=DT_CYCLE_MTH AND CMCD= P_STR_CMCD AND LIMRA_TYPE IN ('LIM25','LIM13P','LIM19P','LIM25P'); --HKG009
DELETE FROM PER_LIMRA WHERE CMCD= P_STR_CMCD AND CYCLE_MTH=DT_CYCLE_MTH AND LIMRA_TYPE IN ('LIM25','LIM13P','LIM19P','LIM25P','LIM13V','LIM19V','LIM25V','LIM13N','LIM19N','LIM25N'); --HKG009
COMMIT;
--STEP 2 CALCULATE 00 AGENT AGENCY
DBMS_OUTPUT.PUT_LINE('STEP 2 START AT '||TO_CHAR(SYSDATE,'YY-MM-DD HH24:MI:SS') );
SP_PER_LIMRA_CAL00( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM25_TYPE, P_STR_USER); --HKG008 --HKG009
SP_PER_LIMRA_CAL00( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM13P_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL00( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM19P_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL00( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM25P_TYPE, P_STR_USER);
--HKG009
/*
SP_PER_LIMRA_CAL00( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM13V_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL00( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM19V_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL00( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM25V_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL00( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM13N_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL00( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM19N_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL00( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM25N_TYPE, P_STR_USER); --HKG008
*/
--HKG009
--STEP 3 CALCULATE EXPOSURE OF EVERY AGENT AGENCY
DBMS_OUTPUT.PUT_LINE('STEP 3 START AT '||TO_CHAR(SYSDATE,'YY-MM-DD HH24:MI:SS'));
SP_PER_LIMRA_CALEXP( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM25_TYPE, P_STR_USER); --HKG008 --HKG009
SP_PER_LIMRA_CALEXP( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM13P_TYPE, P_STR_USER);
SP_PER_LIMRA_CALEXP( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM19P_TYPE, P_STR_USER);
SP_PER_LIMRA_CALEXP( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM25P_TYPE, P_STR_USER);
--HKG009
/*
SP_PER_LIMRA_CALEXP( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM13V_TYPE, P_STR_USER);
SP_PER_LIMRA_CALEXP( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM19V_TYPE, P_STR_USER);
SP_PER_LIMRA_CALEXP( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM25V_TYPE, P_STR_USER);
SP_PER_LIMRA_CALEXP( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM13N_TYPE, P_STR_USER);
SP_PER_LIMRA_CALEXP( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM19N_TYPE, P_STR_USER);
SP_PER_LIMRA_CALEXP( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM25N_TYPE, P_STR_USER); --HKG008
*/
--HKG009
--STEP 4 CALCULATE LAPSE,REINSTATE AND EXPOSURES OF P12,YTD ON AGENT AGENCY LEVEL.
DBMS_OUTPUT.PUT_LINE('STEP 4 START AT '||TO_CHAR(SYSDATE,'YY-MM-DD HH24:MI:SS') );
SP_PER_LIMRA_CALSUM( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM25_TYPE, P_STR_USER); --HKG008 --HKG009
SP_PER_LIMRA_CALSUM( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM13P_TYPE, P_STR_USER);
SP_PER_LIMRA_CALSUM( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM19P_TYPE, P_STR_USER);
SP_PER_LIMRA_CALSUM( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM25P_TYPE, P_STR_USER);
--HKG009
/*
SP_PER_LIMRA_CALSUM( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM13V_TYPE, P_STR_USER);
SP_PER_LIMRA_CALSUM( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM19V_TYPE, P_STR_USER);
SP_PER_LIMRA_CALSUM( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM25V_TYPE, P_STR_USER);
SP_PER_LIMRA_CALSUM( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM13N_TYPE, P_STR_USER);
SP_PER_LIMRA_CALSUM( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM19N_TYPE, P_STR_USER);
SP_PER_LIMRA_CALSUM( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM25N_TYPE, P_STR_USER); --HKG008
*/
--HKG009
--STEP 5 CALCULATE 01 AGENT LEVEL.CURRENT MONTH, P12 AND YTD OF LAPSE,REINSTATE AND EXPOSURES
DBMS_OUTPUT.PUT_LINE('STEP 5 START AT '||TO_CHAR(SYSDATE,'YY-MM-DD HH24:MI:SS') );
SP_PER_LIMRA_CAL01( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM25_TYPE, P_STR_USER); --HKG008 --HKG009
SP_PER_LIMRA_CAL01( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM13P_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL01( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM19P_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL01( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM25P_TYPE, P_STR_USER);
--HKG009
/*
SP_PER_LIMRA_CAL01( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM13V_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL01( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM19V_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL01( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM25V_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL01( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM13N_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL01( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM19N_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL01( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM25N_TYPE, P_STR_USER); --HKG008
*/
--HKG009
--STEP 6 CALCULATE 02 AGENCY LEVEL.CURRENT MONTH, P12 AND YTD OF LAPSE,REINSTATE AND EXPOSURES.
DBMS_OUTPUT.PUT_LINE('STEP 6 START AT '||TO_CHAR(SYSDATE,'YY-MM-DD HH24:MI:SS') );
SP_PER_LIMRA_CAL02( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM25_TYPE, P_STR_USER); --HKG008 --HKG009
SP_PER_LIMRA_CAL02( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM13P_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL02( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM19P_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL02( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM25P_TYPE, P_STR_USER);
--HKG009
/*
SP_PER_LIMRA_CAL02( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM13V_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL02( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM19V_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL02( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM25V_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL02( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM13N_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL02( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM19N_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL02( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM25N_TYPE, P_STR_USER); --HKG008
*/
--HKG009
--STEP 8 CALCULATE 03 DISTRICT LEVEL.CURRENT MONTH, P12 AND YTD OF LAPSE,REINSTATE AND EXPOSURES.
DBMS_OUTPUT.PUT_LINE('STEP 8 START AT '||TO_CHAR(SYSDATE,'YY-MM-DD HH24:MI:SS') );
SP_PER_LIMRA_CAL03( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM25_TYPE, P_STR_USER); --HKG008 --HKG009
SP_PER_LIMRA_CAL03( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM13P_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL03( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM19P_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL03( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM25P_TYPE, P_STR_USER);
--HKG009
/*
SP_PER_LIMRA_CAL03( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM13V_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL03( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM19V_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL03( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM25V_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL03( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM13N_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL03( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM19N_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL03( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM25N_TYPE, P_STR_USER); --HKG008
*/
--HKG009
--STEP 10 CALCULATE 04 CHANNEL LEVEL.CURRENT MONTH, P12 AND YTD OF LAPSE,REINSTATE AND EXPOSURES.
DBMS_OUTPUT.PUT_LINE('STEP 10 START AT '||TO_CHAR(SYSDATE,'YY-MM-DD HH24:MI:SS') );
SP_PER_LIMRA_CAL04( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM25_TYPE, P_STR_USER); --HKG008 --HKG009
SP_PER_LIMRA_CAL04( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM13P_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL04( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM19P_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL04( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM25P_TYPE, P_STR_USER);
--HKG009
/*
SP_PER_LIMRA_CAL04( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM13V_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL04( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM19V_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL04( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM25V_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL04( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM13N_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL04( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM19N_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL04( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM25N_TYPE, P_STR_USER); --HKG008
*/
--HKG009
--STEP 11 CALCULATE 05 COMPANY LEVEL.CURRENT MONTH, P12 AND YTD OF LAPSE,REINSTATE AND EXPOSURES.
DBMS_OUTPUT.PUT_LINE('STEP 11 START AT '||TO_CHAR(SYSDATE,'YY-MM-DD HH24:MI:SS') );
SP_PER_LIMRA_CAL05( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM25_TYPE, P_STR_USER); --HKG008 --HKG009
SP_PER_LIMRA_CAL05( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM13P_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL05( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM19P_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL05( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM25P_TYPE, P_STR_USER);
--HKG009
/*
SP_PER_LIMRA_CAL05( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM13V_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL05( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM19V_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL05( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM25V_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL05( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM13N_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL05( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM19N_TYPE, P_STR_USER);
SP_PER_LIMRA_CAL05( P_STR_CMCD, DT_CYCLE_MTH, DT_CYCLE_MTH_END, STR_LIM25N_TYPE, P_STR_USER); --HKG008
*/
--HKG009
--LAST STEP CALCULATE PERSISTENCY
DBMS_OUTPUT.PUT_LINE('STEP 12 START AT '||TO_CHAR(SYSDATE,'YY-MM-DD HH24:MI:SS') );
SP_PER_LIMRA_CALPER( P_STR_CMCD, DT_CYCLE_MTH, STR_LIM25_TYPE, P_STR_USER); --HKG008 --HKG009
SP_PER_LIMRA_CALPER( P_STR_CMCD, DT_CYCLE_MTH, STR_LIM13P_TYPE, P_STR_USER);
SP_PER_LIMRA_CALPER( P_STR_CMCD, DT_CYCLE_MTH, STR_LIM19P_TYPE, P_STR_USER);
SP_PER_LIMRA_CALPER( P_STR_CMCD, DT_CYCLE_MTH, STR_LIM25P_TYPE, P_STR_USER);
--HKG009
/*
SP_PER_LIMRA_CALPER( P_STR_CMCD, DT_CYCLE_MTH, STR_LIM13V_TYPE, P_STR_USER);
SP_PER_LIMRA_CALPER( P_STR_CMCD, DT_CYCLE_MTH, STR_LIM19V_TYPE, P_STR_USER);
SP_PER_LIMRA_CALPER( P_STR_CMCD, DT_CYCLE_MTH, STR_LIM25V_TYPE, P_STR_USER);
SP_PER_LIMRA_CALPER( P_STR_CMCD, DT_CYCLE_MTH, STR_LIM13N_TYPE, P_STR_USER);
SP_PER_LIMRA_CALPER( P_STR_CMCD, DT_CYCLE_MTH, STR_LIM19N_TYPE, P_STR_USER);
SP_PER_LIMRA_CALPER( P_STR_CMCD, DT_CYCLE_MTH, STR_LIM25N_TYPE, P_STR_USER); --HKG008
DBMS_OUTPUT.PUT_LINE('STEP 12 END AT '||TO_CHAR(SYSDATE,'YY-MM-DD HH24:MI:SS') );
*/
--HKG009
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, 'SP_PER_LIMRA2 ERROR '||SQLERRM);
END;
--HKG007: END
PROCEDURE SP_PER_LIMRA_POLICY
( P_STR_CMCD IN VARCHAR2,
P_DT_CYCLE_DT IN DATE,
/*P_STR_CHANNEL IN VARCHAR2,*/
P_STR_USER IN VARCHAR2
) IS
DT_CYCLE_DT DATE;
CNT NUMBER;
BEGIN
SP_AGT_AGY_INFO(P_DT_CYCLE_DT);
DELETE FROM PER_LIMRA_POLICY NOLOGGING WHERE CMCD = P_STR_CMCD AND CYCLE_DT = P_DT_CYCLE_DT /*AND CHANNEL = P_STR_CHANNEL*/;
/* IF (P_DT_CYCLE_DT IS NULL) THEN
SELECT TO_DATE(TXT_KEY_VALUE, 'YYYY-MM-DD') INTO DT_CYCLE_DT FROM IN_ETL_CONTROL WHERE TXT_KEY_STRING ='PER_FILE_CYCLE_DATE' AND TXT_FILE_NAME = P_STR_CMCD||'_LIMRA';
ELSE*/
DT_CYCLE_DT := P_DT_CYCLE_DT;
--END IF;
DBMS_OUTPUT.PUT_LINE('CALL SP_PER_LIMRA_VAD TO VALIDATE RECORDS IN PER_LIMRA_FEED TABLE.' );
SP_PER_LIMRA_VAD( P_STR_CMCD,P_DT_CYCLE_DT, /*P_STR_CHANNEL,*/ P_STR_USER);
--DBMS_OUTPUT.PUT_LINE('TRANSFORM THE IMPORTED DATA FROM STAGE TABLE PER_LIMRA_FEED TO REQUIRED FORMAT TO POLICY DETAIL TABLE PER_LIMRA_POLICY FOR FUTURE CALCULATION' );
DBMS_OUTPUT.PUT_LINE('BEFORE POLICY INSERT'||SYSDATE);
--SHOULD ADD FIELDS.
INSERT INTO PER_LIMRA_POLICY NOLOGGING (SEQNUM, CMCD, CYCLE_DT, AGY, AGT,
TRNCD, TRN_YY, TRN_MM, TRN_DD, TRN_TYPE, CHANNEL,
CASE, SA_AMT, ANN_PREM, CAP_ANN_PREM,
POLNO, CURR, UPD, CNTCT_TYP, CHNL_CD, UNIT_MGR, AREA_MGR, OVRID,
LAPSE_IND, POLICY_MONTH, PLAN_CODE, LIFE_NO, COVERAGE_NO, RIDER_NO, INCLUDE_FLAG, SRV_AGY, SRV_AGT,
PROD_CAT,INSURED_NAME, POL_PTD, AFYP, INCEPTION, CLIENT_ID, CREATE_DT, CREATE_BY,COMPONENT_CODE,AP_POLICY,LAYER_SEQ,BASIC_PLAN_COMP_CD,BASIC_PLAN_CD,SHARE_PER,EXCLUDE_IND)
SELECT
SEQ_PER_LIMRA_POLICY.NEXTVAL,
--DECODE(CMCD,'2','SG','4','BN','ZZ'), CYCLE_DT, AGY, SUBSTR(AGT,-5,5),
FEED.CMCD, FEED.CYCLE_DT, FEED.AGY, FEED.AGT,
TRNCD, TRN_YY, TRN_MM, TRN_DD,
--TRANSACTION CODE SHOULD BE VERIFY LATER
--MODIFIED BY WING 20140109 START
--CASE WHEN TRNCD='30' THEN STR_NB WHEN TRNCD IN ('65','66','67','68') THEN STR_REINSTATE WHEN TRNCD IN ('56','96','97','98') THEN STR_LAPSE ELSE 'ZZ' END TRN_TYPE,
CASE WHEN TRNCD='30' THEN STR_NB WHEN TRNCD IN ('56','96','97','98') THEN STR_REINSTATE WHEN TRNCD IN ('65','66','67','68') THEN STR_LAPSE WHEN TRNCD IN ('GP1','GP2','GP3') THEN 'GP' ELSE 'ZZ' END TRN_TYPE, --HKG002 --HKG009
--CASE WHEN TRNCD='30' THEN STR_NB WHEN TRNCD IN ('56','91','92','93','94','96','97','98') THEN STR_REINSTATE WHEN TRNCD IN ('61','62','63','64','65','66','67','68') THEN STR_LAPSE WHEN TRNCD IN ('GP1','GP2','GP3') THEN 'GP' ELSE 'ZZ' END TRN_TYPE, --HKG002 --HKG009
AGY_INFO.CHANNEL AS CHANNEL,
/*CASE WHEN CASE_S='-' THEN TO_NUMBER(CASE)/ 10 * -1 ELSE TO_NUMBER(CASE)/ 10 END AS CASE,
CASE WHEN AMT_S='-' THEN TO_NUMBER(AMT) * -1 ELSE TO_NUMBER(AMT) END AS SA_AMT,
CASE WHEN ANN_PREM_S='-' THEN TO_NUMBER(ANN_PREM)/ 100 * -1 ELSE TO_NUMBER(ANN_PREM)/ 100 END AS ANN_PREM,
CASE WHEN ANN_PREM_S='-' THEN TO_NUMBER(ANN_PREM)/ 100 * -1 ELSE TO_NUMBER(ANN_PREM)/ 100 END AS CAP_ANN_PREM,*/
-- (CASE WHEN TRNCD IN ('56','96','97','98') AND CASE_S='-' THEN TO_NUMBER(CASE)/ 10
-- WHEN TRNCD IN ('56','96','97','98') AND CASE_S='+' THEN TO_NUMBER(CASE)/ 10 *-1
(CASE WHEN TRNCD IN ('65','66','67','68') AND CASE_S='-' THEN TO_NUMBER(CASE)/ 10 --HKG002 --HKG009
WHEN TRNCD IN ('65','66','67','68') AND CASE_S='+' THEN TO_NUMBER(CASE)/ 10 *-1 --HKG002 --HKG009
-- (CASE WHEN TRNCD IN ('61','62','63','64','65','66','67','68') AND CASE_S='-' THEN TO_NUMBER(CASE)/ 10 --HKG002 --HKG009
-- WHEN TRNCD IN ('61','62','63','64','65','66','67','68') AND CASE_S='+' THEN TO_NUMBER(CASE)/ 10 *-1 --HKG002 --HKG009
--FOR GP CASE COUNT SHOULD BE DIVIDED BY 100
WHEN TRNCD IN ('GP1','GP2','GP3') THEN TO_NUMBER(CASE)/ 100
WHEN CASE_S='-' THEN TO_NUMBER(CASE)/ 10*-1
ELSE TO_NUMBER(CASE)/ 10
END) AS CASE,
-- (CASE WHEN TRNCD IN ('56','96','97','98') AND AMT_S='-' THEN TO_NUMBER(AMT)
-- WHEN TRNCD IN ('56','96','97','98') AND AMT_S='+' THEN TO_NUMBER(AMT) *-1
(CASE WHEN TRNCD IN ('65','66','67','68') AND AMT_S='-' THEN TO_NUMBER(AMT) --HKG002 --HKG009
WHEN TRNCD IN ('65','66','67','68') AND AMT_S='+' THEN TO_NUMBER(AMT) *-1 --HKG002 --HKG009
-- (CASE WHEN TRNCD IN ('61','62','63','64','65','66','67','68') AND AMT_S='-' THEN TO_NUMBER(AMT) --HKG002 --HKG009
-- WHEN TRNCD IN ('61','62','63','64','65','66','67','68') AND AMT_S='+' THEN TO_NUMBER(AMT) *-1 --HKG002 --HKG009
WHEN AMT_S='-' THEN TO_NUMBER(AMT)*-1
ELSE TO_NUMBER(AMT)
END) AS SA_AMT,
-- (CASE WHEN TRNCD IN ('56','96','97','98') AND ANN_PREM_S='-' THEN TO_NUMBER(ANN_PREM)/ 100
-- WHEN TRNCD IN ('56','96','97','98') AND ANN_PREM_S='+' THEN TO_NUMBER(ANN_PREM)/ 100 *-1
(CASE WHEN TRNCD IN ('65','66','67','68') AND ANN_PREM_S='-' THEN TO_NUMBER(ANN_PREM)/ 100 --HKG002 --HKG009
WHEN TRNCD IN ('65','66','67','68') AND ANN_PREM_S='+' THEN TO_NUMBER(ANN_PREM)/ 100 *-1 --HKG002 --HKG009
-- (CASE WHEN TRNCD IN ('61','62','63','64','65','66','67','68') AND ANN_PREM_S='-' THEN TO_NUMBER(ANN_PREM)/ 100 --HKG002 --HKG009
-- WHEN TRNCD IN ('61','62','63','64','65','66','67','68') AND ANN_PREM_S='+' THEN TO_NUMBER(ANN_PREM)/ 100 *-1 --HKG002 --HKG009
WHEN ANN_PREM_S='-' THEN TO_NUMBER(ANN_PREM)/ 100*-1
ELSE TO_NUMBER(ANN_PREM)/ 100
END) AS ANN_PREM,
-- (CASE WHEN TRNCD IN ('56','96','97','98') AND ANN_PREM_S='-' THEN TO_NUMBER(ANN_PREM)/ 100
-- WHEN TRNCD IN ('56','96','97','98') AND ANN_PREM_S='+' THEN TO_NUMBER(ANN_PREM)/ 100 *-1
/*(CASE WHEN TRNCD IN ('65','66','67','68') AND ANN_PREM_S='-' THEN TO_NUMBER(ANN_PREM)/ 100
WHEN TRNCD IN ('65','66','67','68') AND ANN_PREM_S='+' THEN TO_NUMBER(ANN_PREM)/ 100 *-1
WHEN ANN_PREM_S='-' THEN TO_NUMBER(ANN_PREM)/ 100*-1
ELSE TO_NUMBER(ANN_PREM)/ 100
END)*/0 AS CAP_ANN_PREM,
--MODIFIED BY WING 20140109 END
POLNO, CURR, UPD, CNTCT_TYP, CHNL_CD, UNIT_MGR, AREA_MGR, OVRID,
LAPSE_IND, POLICY_MONTH, PLAN_CODE, LIFE_NO, COVERAGE_NO, RIDER_NO, INCLUDE_FLAG, SRV_AGY, SRV_AGT,
PROD_CAT, INSURED_NAME, POL_PTD, TO_NUMBER(AFYP)/100, INCEPTION,CLIENT_ID, SYSDATE, P_STR_USER,
COMPONENT_CODE,AP_POLICY/100,SUBSTR(LAYER_SEQ,-2),BASIC_PLAN_COMP_CD,BASIC_PLAN_CD,SHARE_PERCENTAGE/100,
'1'
FROM PER_LIMRA_FEED FEED, PER_AGENCY_INFO AGY_INFO
WHERE FEED.CYCLE_DT = DT_CYCLE_DT
AND AGY_INFO.CMCD = P_STR_CMCD
AND FEED.CMCD=P_STR_CMCD
AND AGY_INFO.AGY_TYPE = 'AGY'
AND FEED.AGY = AGY_INFO.AGY
AND AGY_INFO.CYCLE_MTH = DT_CYCLE_DT
AND ((NOT EXISTS (SELECT 1 FROM PER_GRACE_EXCLUSION GXL
WHERE GXL.EXC_PLAN_CD = SUBSTR(BASIC_PLAN_CD,0,4)
)) OR FEED.TRNCD NOT IN ('45','46','47','48'))
AND NOT EXISTS (SELECT 1 FROM PER_LKP_EXCLUSION EXL
WHERE EXL.CLASS_ID = SUBSTR(BASIC_PLAN_CD,0,3)
AND FN_CONVERT_BASIC_PLAN_CD(SUBSTR(BASIC_PLAN_CD,4,3))<=FN_CONVERT_BASIC_PLAN_CD(EXL.BASIC_PLAN_CD_TO)
AND FN_CONVERT_BASIC_PLAN_CD(SUBSTR(BASIC_PLAN_CD,4,3))>=FN_CONVERT_BASIC_PLAN_CD(EXL.BASIC_PLAN_CD_FROM)
)
AND PROD_CAT NOT IN ('PA','CP');
COMMIT;
--SP_PER_LIMRA_SUP_VAD( P_STR_CMCD,P_DT_CYCLE_DT, /*P_STR_CHANNEL,*/ P_STR_USER);
--HKG009
/*
--HKG003 START
UPDATE PER_LIMRA_POLICY TAR
SET TAR.POL_TYPE = 'VIT'
WHERE CYCLE_DT=P_DT_CYCLE_DT AND EXISTS
( SELECT 1 FROM PER_VITALITY_INFO VITINFO WHERE TAR.POLNO = VITINFO.POLNO AND TAR.LIFE_NO = VITINFO.LIFE_NO AND TAR.COVERAGE_NO = VITINFO.COVERAGE_NO AND TAR.RIDER_NO = VITINFO.RIDER_NO);
COMMIT;
--HKG003 END
*/
--HKG009
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, 'SP_PER_LIMRA_POLICY ERROR '||SQLERRM);
END;
PROCEDURE SP_PER_LIMRA_VAD
( P_STR_CMCD IN VARCHAR2,
P_DT_CYCLE_DT IN DATE,
/*P_STR_CHANNEL IN VARCHAR2,*/
P_STR_USER IN VARCHAR2
) IS
DEC_NOT_IN_COUNT INT;
DT_CYCLE_MTH_END DATE;
BEGIN
--DELETE RECORD IF RERUN MODE
DELETE FROM PER_LIMRA_FEED_SUP NOLOGGING WHERE CMCD = P_STR_CMCD AND CYCLE_DT = P_DT_CYCLE_DT;
COMMIT;
DT_CYCLE_MTH_END := ADD_MONTHS(TRUNC(P_DT_CYCLE_DT,'MM'), 1);
--CHECK AGENT OR AGENCY CODE IN PER_AGENT_INFO OR PER_AGENCY_INFO TABLE,
SELECT COUNT(*) INTO DEC_NOT_IN_COUNT
FROM PER_LIMRA_FEED RAW_DATA
LEFT JOIN (SELECT DISTINCT AGT FROM PER_AGENT_INFO WHERE CMCD=P_STR_CMCD AND CYCLE_MTH = P_DT_CYCLE_DT ) AGT ON RAW_DATA.AGT=AGT.AGT
LEFT JOIN (SELECT DISTINCT AGY FROM PER_AGENCY_INFO WHERE CMCD = P_STR_CMCD AND CYCLE_MTH = P_DT_CYCLE_DT) AGY ON RAW_DATA.AGY=AGY.AGY
WHERE (AGT.AGT IS NULL OR AGY.AGY IS NULL)
AND RAW_DATA.CYCLE_DT = P_DT_CYCLE_DT
AND RAW_DATA.CMCD=P_STR_CMCD;
DBMS_OUTPUT.PUT_LINE(DEC_NOT_IN_COUNT);
IF DEC_NOT_IN_COUNT > 0 THEN
DBMS_OUTPUT.PUT_LINE('BEFORE VAD INSERT'||SYSDATE);
--INSERT DATA INTO PER_LIMRA_FEED_SUP AND SET SUP_FLAG = 'F'
INSERT /*+ APPEND*/ INTO PER_LIMRA_FEED_SUP NOLOGGING (CMCD, AGY, AGT,
TRNCD, CASE_S, CASE, AMT_S, AMT, ANN_PREM_S, ANN_PREM, TRN_YY, TRN_MM, POLNO, CURR, UPD, CNTCT_TYP, CHNL_CD,
UNIT_MGR, AREA_MGR, OVRID, LAPSE_IND, POLICY_MONTH, PLAN_CODE, LIFE_NO, COVERAGE_NO, RIDER_NO, INCLUDE_FLAG, SRV_AGY, SRV_AGT, TRN_DD, CYCLE_DT,
PROD_CAT, INSURED_NAME, POL_PTD, AFYP, SUP_FLAG, INCEPTION, CLIENT_ID, CREATE_DT, CREATE_BY,COMPONENT_CODE,AP_POLICY,LAYER_SEQ,BASIC_PLAN_COMP_CD,BASIC_PLAN_CD,SHARE_PERCENTAGE) --HKG009
--,TATCODE,PSTATCODE,CONDCODE,TRANNO) --HKG004 --HKG009
SELECT RAW_DATA.CMCD, RAW_DATA.AGY, RAW_DATA.AGT,
TRNCD, CASE_S, CASE, AMT_S, AMT, ANN_PREM_S, ANN_PREM, TRN_YY, TRN_MM, POLNO, CURR, UPD, CNTCT_TYP, CHNL_CD,
UNIT_MGR, AREA_MGR, OVRID, LAPSE_IND, POLICY_MONTH, PLAN_CODE, LIFE_NO, COVERAGE_NO, RIDER_NO, INCLUDE_FLAG, SRV_AGY, SRV_AGT, TRN_DD, CYCLE_DT,
PROD_CAT, INSURED_NAME, POL_PTD, AFYP, 'F',INCEPTION, CLIENT_ID, CREATE_DT, CREATE_BY,COMPONENT_CODE,AP_POLICY,LAYER_SEQ,BASIC_PLAN_COMP_CD,BASIC_PLAN_CD,SHARE_PERCENTAGE
--,TATCODE,PSTATCODE,CONDCODE,TRANNO --HKG004 --HKG009
FROM PER_LIMRA_FEED RAW_DATA
LEFT JOIN (SELECT DISTINCT AGT FROM PER_AGENT_INFO WHERE CMCD=P_STR_CMCD AND CYCLE_MTH = P_DT_CYCLE_DT ) AGT ON RAW_DATA.AGT=AGT.AGT
LEFT JOIN (SELECT DISTINCT AGY FROM PER_AGENCY_INFO WHERE CMCD = P_STR_CMCD AND CYCLE_MTH = P_DT_CYCLE_DT) AGY ON RAW_DATA.AGY=AGY.AGY
WHERE (AGT.AGT IS NULL OR AGY.AGY IS NULL )
AND RAW_DATA.CYCLE_DT = P_DT_CYCLE_DT
AND RAW_DATA.CMCD=P_STR_CMCD;
COMMIT;
DBMS_OUTPUT.PUT_LINE('END VAD INSERT'||SYSDATE);
--DELETE INVALID DATA FROM PER_LIMRA_FEED
DELETE FROM PER_LIMRA_FEED RAW_DATA
WHERE ROWID IN
(SELECT RAW_DATA.ROWID FROM PER_LIMRA_FEED RAW_DATA
LEFT JOIN (SELECT DISTINCT AGT FROM PER_AGENT_INFO WHERE CMCD=P_STR_CMCD AND CYCLE_MTH = P_DT_CYCLE_DT ) AGT ON RAW_DATA.AGT=AGT.AGT
LEFT JOIN (SELECT DISTINCT AGY FROM PER_AGENCY_INFO WHERE CMCD = P_STR_CMCD AND CYCLE_MTH = P_DT_CYCLE_DT) AGY ON RAW_DATA.AGY=AGY.AGY
WHERE (AGT.AGT IS NULL OR AGY.AGY IS NULL)
AND RAW_DATA.CYCLE_DT = P_DT_CYCLE_DT
AND RAW_DATA.CMCD=P_STR_CMCD
);
COMMIT;
DBMS_OUTPUT.PUT_LINE('DELETE INVALID RECORDS FROM PER_LIMRA_FEED TABLE'||SYSDATE);
ELSIF DEC_NOT_IN_COUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE('ALL RECORDS IN PER_LIMRA_FEED TABLE PASS VALIDATION.');
ELSE RAISE INVALID_RECORD_COUNT;
END IF;
EXCEPTION
WHEN INVALID_RECORD_COUNT THEN
RAISE_APPLICATION_ERROR(-20001,'RECORD COUNT ERROR IN SP_PER_LIMRA_VAD ' || SQLERRM);
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,'SP_PER_LIMRA_VAD ERROR ' || SQLERRM);
END;
PROCEDURE SP_PER_LIMRA_SUP_VAD
( P_STR_CMCD IN VARCHAR2,
P_DT_CYCLE_DT IN DATE,
/*P_STR_CHANNEL IN VARCHAR2,*/
P_STR_USER IN VARCHAR2
) IS
DEC_IN_COUNT_C INT;
DEC_IN_COUNT_P INT;
DT_CYCLE_MTH_FIR DATE;
BEGIN
--DELETE RECORD IF RERUN MODE
DT_CYCLE_MTH_FIR := TRUNC(P_DT_CYCLE_DT,'MM');
--CHECK AGENT OR AGENCY CODE IN PER_AGENT_INFO OR PER_AGENCY_INFO TABLE,
--AND TRANSACTION DATE IS THE SAME MONTH OF THE CYCLE DATE.
SELECT COUNT(*) INTO DEC_IN_COUNT_C
FROM PER_LIMRA_FEED_SUP RAW_DATA
INNER JOIN (SELECT DISTINCT AGT FROM PER_AGENT_INFO WHERE CMCD=P_STR_CMCD AND CYCLE_MTH = P_DT_CYCLE_DT ) AGT ON RAW_DATA.AGT=AGT.AGT
INNER JOIN (SELECT DISTINCT AGY FROM PER_AGENCY_INFO WHERE CMCD = P_STR_CMCD AND CYCLE_MTH = P_DT_CYCLE_DT) AGY ON RAW_DATA.AGY=AGY.AGY
WHERE RAW_DATA.CYCLE_DT >= DT_CYCLE_MTH_FIR
AND RAW_DATA.CYCLE_DT < P_DT_CYCLE_DT
AND RAW_DATA.CMCD=P_STR_CMCD
AND RAW_DATA.SUP_FLAG = 'F';
IF DEC_IN_COUNT_C >0 THEN
DELETE FROM PER_LIMRA_POLICY_ADJ WHERE CMCD = P_STR_CMCD AND PROCESS_DT = P_DT_CYCLE_DT AND TRANS_SRC_CD = 'SUP';
COMMIT;
INSERT INTO PER_LIMRA_POLICY_ADJ NOLOGGING (SEQNUM, CMCD, CYCLE_DT, AGY, AGT,
TRNCD, TRN_YY, TRN_MM, TRN_DD, TRN_TYPE, CHANNEL,
CASE, SA_AMT, ANN_PREM, CAP_ANN_PREM,
POLNO, CURR, UPD, CNTCT_TYP, CHNL_CD, UNIT_MGR, AREA_MGR, OVRID,
LAPSE_IND, POLICY_MONTH, PLAN_CODE, LIFE_NO, COVERAGE_NO, RIDER_NO, INCLUDE_FLAG, SRV_AGY, SRV_AGT,
PROD_CAT,INSURED_NAME, POL_PTD, AFYP, INCEPTION, CLIENT_ID, CREATE_DT, CREATE_BY,COMPONENT_CODE,AP_POLICY,LAYER_SEQ,BASIC_PLAN_COMP_CD,BASIC_PLAN_CD,SHARE_PER,EXCLUDE_IND,
TRANS_SRC_CD,PROCESS_DT)
SELECT
SEQ_PER_LIMRA_POLICY_ADJ.NEXTVAL,
--DECODE(CMCD,'2','SG','4','BN','ZZ'), CYCLE_DT, AGY, SUBSTR(AGT,-5,5),
RAW_DATA.CMCD, RAW_DATA.CYCLE_DT, RAW_DATA.AGY, RAW_DATA.AGT,
TRNCD, TRN_YY, TRN_MM, TRN_DD,
--TRANSACTION CODE SHOULD BE VERIFY LATER
--MODIFIED BY WING 20140109 START
--CASE WHEN TRNCD='30' THEN STR_NB WHEN TRNCD IN ('65','66','67','68') THEN STR_REINSTATE WHEN TRNCD IN ('56','96','97','98') THEN STR_LAPSE ELSE 'ZZ' END TRN_TYPE,
CASE WHEN TRNCD='30' THEN STR_NB WHEN TRNCD IN ('56','96','97','98') THEN STR_REINSTATE WHEN TRNCD IN ('65','66','67','68') THEN STR_LAPSE WHEN TRNCD IN ('GP1','GP2','GP3') THEN 'GP' ELSE 'ZZ' END TRN_TYPE, --HKG002 --HKG009
--CASE WHEN TRNCD='30' THEN STR_NB WHEN TRNCD IN ('56','91','92','93','94','96','97','98') THEN STR_REINSTATE WHEN TRNCD IN ('61','62','63','64','65','66','67','68') THEN STR_LAPSE WHEN TRNCD IN ('GP1','GP2','GP3') THEN 'GP' ELSE 'ZZ' END TRN_TYPE, --HKG002 --HKG009
AGY_INFO.CHANNEL AS CHANNEL,
(CASE WHEN TRNCD IN ('65','66','67','68') AND CASE_S='-' THEN TO_NUMBER(CASE)/ 10 --HKG002 --HKG009
WHEN TRNCD IN ('65','66','67','68') AND CASE_S='+' THEN TO_NUMBER(CASE)/ 10 *-1 --HKG002 --HKG009
-- (CASE WHEN TRNCD IN ('61','62','63','64','65','66','67','68') AND CASE_S='-' THEN TO_NUMBER(CASE)/ 10 --HKG002 --HKG009
-- WHEN TRNCD IN ('61','62','63','64','65','66','67','68') AND CASE_S='+' THEN TO_NUMBER(CASE)/ 10 *-1 --HKG002 --HKG009
--FOR GP CASE COUNT SHOULD BE DIVIDED BY 100
WHEN TRNCD IN ('GP1','GP2','GP3') THEN TO_NUMBER(CASE)/ 100
WHEN CASE_S='-' THEN TO_NUMBER(CASE)/ 10*-1
ELSE TO_NUMBER(CASE)/ 10
END) AS CASE,
-- (CASE WHEN TRNCD IN ('56','96','97','98') AND AMT_S='-' THEN TO_NUMBER(AMT)
-- WHEN TRNCD IN ('56','96','97','98') AND AMT_S='+' THEN TO_NUMBER(AMT) *-1
(CASE WHEN TRNCD IN ('65','66','67','68') AND AMT_S='-' THEN TO_NUMBER(AMT) --HKG002 --HKG009
WHEN TRNCD IN ('65','66','67','68') AND AMT_S='+' THEN TO_NUMBER(AMT) *-1 --HKG002 --HKG009
-- (CASE WHEN TRNCD IN ('61','62','63','64','65','66','67','68') AND AMT_S='-' THEN TO_NUMBER(AMT) --HKG002 --HKG009
-- WHEN TRNCD IN ('61','62','63','64','65','66','67','68') AND AMT_S='+' THEN TO_NUMBER(AMT) *-1 --HKG002 --HKG009
WHEN AMT_S='-' THEN TO_NUMBER(AMT)*-1
ELSE TO_NUMBER(AMT)
END) AS SA_AMT,
-- (CASE WHEN TRNCD IN ('56','96','97','98') AND ANN_PREM_S='-' THEN TO_NUMBER(ANN_PREM)/ 100
-- WHEN TRNCD IN ('56','96','97','98') AND ANN_PREM_S='+' THEN TO_NUMBER(ANN_PREM)/ 100 *-1
(CASE WHEN TRNCD IN ('65','66','67','68') AND ANN_PREM_S='-' THEN TO_NUMBER(ANN_PREM)/ 100 --HKG002 --HKG009
WHEN TRNCD IN ('65','66','67','68') AND ANN_PREM_S='+' THEN TO_NUMBER(ANN_PREM)/ 100 *-1 --HKG002 --HKG009
-- (CASE WHEN TRNCD IN ('61','62','63','64','65','66','67','68') AND ANN_PREM_S='-' THEN TO_NUMBER(ANN_PREM)/ 100 --HKG002 --HKG009
-- WHEN TRNCD IN ('61','62','63','64','65','66','67','68') AND ANN_PREM_S='+' THEN TO_NUMBER(ANN_PREM)/ 100 *-1 --HKG002 --HKG009
WHEN ANN_PREM_S='-' THEN TO_NUMBER(ANN_PREM)/ 100*-1
ELSE TO_NUMBER(ANN_PREM)/ 100
END) AS ANN_PREM,
-- (CASE WHEN TRNCD IN ('56','96','97','98') AND ANN_PREM_S='-' THEN TO_NUMBER(ANN_PREM)/ 100
-- WHEN TRNCD IN ('56','96','97','98') AND ANN_PREM_S='+' THEN TO_NUMBER(ANN_PREM)/ 100 *-1
(CASE WHEN TRNCD IN ('65','66','67','68') AND ANN_PREM_S='-' THEN TO_NUMBER(ANN_PREM)/ 100 --HKG002 --HKG009
WHEN TRNCD IN ('65','66','67','68') AND ANN_PREM_S='+' THEN TO_NUMBER(ANN_PREM)/ 100 *-1 --HKG002 --HKG009
-- (CASE WHEN TRNCD IN ('61','62','63','64','65','66','67','68') AND ANN_PREM_S='-' THEN TO_NUMBER(ANN_PREM)/ 100 --HKG002 --HKG009
-- WHEN TRNCD IN ('61','62','63','64','65','66','67','68') AND ANN_PREM_S='+' THEN TO_NUMBER(ANN_PREM)/ 100 *-1 --HKG002 --HKG009
WHEN ANN_PREM_S='-' THEN TO_NUMBER(ANN_PREM)/ 100*-1
ELSE TO_NUMBER(ANN_PREM)/ 100
END) AS CAP_ANN_PREM,
--MODIFIED BY WING 20140109 END
POLNO, CURR, UPD, CNTCT_TYP, CHNL_CD, UNIT_MGR, AREA_MGR, OVRID,
LAPSE_IND, POLICY_MONTH, PLAN_CODE, LIFE_NO, COVERAGE_NO, RIDER_NO, INCLUDE_FLAG, SRV_AGY, SRV_AGT,
PROD_CAT, INSURED_NAME, POL_PTD, TO_NUMBER(AFYP)/100, INCEPTION,CLIENT_ID, SYSDATE, P_STR_USER,
COMPONENT_CODE,AP_POLICY/100,SUBSTR(LAYER_SEQ,-2),BASIC_PLAN_COMP_CD,BASIC_PLAN_CD,SHARE_PERCENTAGE/100,
'1' ,'SUP', P_DT_CYCLE_DT
FROM PER_LIMRA_FEED_SUP RAW_DATA
INNER JOIN (SELECT DISTINCT AGT FROM PER_AGENT_INFO WHERE CMCD=P_STR_CMCD AND CYCLE_MTH = P_DT_CYCLE_DT ) AGT ON RAW_DATA.AGT=AGT.AGT
INNER JOIN (SELECT DISTINCT AGY FROM PER_AGENCY_INFO WHERE CMCD = P_STR_CMCD AND CYCLE_MTH = P_DT_CYCLE_DT) AGY ON RAW_DATA.AGY=AGY.AGY
INNER JOIN PER_AGENCY_INFO AGY_INFO ON RAW_DATA.AGY = AGY_INFO.AGY AND AGY_INFO.CMCD = P_STR_CMCD AND AGY_INFO.AGY_TYPE = 'AGY' AND AGY_INFO.CYCLE_MTH = P_DT_CYCLE_DT
WHERE RAW_DATA.CYCLE_DT >= DT_CYCLE_MTH_FIR
AND RAW_DATA.CYCLE_DT < P_DT_CYCLE_DT
AND RAW_DATA.CMCD=P_STR_CMCD
AND RAW_DATA.SUP_FLAG = 'F'
AND ((NOT EXISTS (SELECT 1 FROM PER_GRACE_EXCLUSION GXL
WHERE GXL.EXC_PLAN_CD = SUBSTR(BASIC_PLAN_CD,0,4)
)) OR RAW_DATA.TRNCD NOT IN ('45','46','47','48'))
AND NOT EXISTS (SELECT 1 FROM PER_LKP_EXCLUSION EXL
WHERE EXL.CLASS_ID = SUBSTR(BASIC_PLAN_CD,0,3)
AND FN_CONVERT_BASIC_PLAN_CD(SUBSTR(BASIC_PLAN_CD,4,3))<=FN_CONVERT_BASIC_PLAN_CD(EXL.BASIC_PLAN_CD_TO)
AND FN_CONVERT_BASIC_PLAN_CD(SUBSTR(BASIC_PLAN_CD,4,3))>=FN_CONVERT_BASIC_PLAN_CD(EXL.BASIC_PLAN_CD_FROM)
)
AND PROD_CAT NOT IN ('PA','CP');
COMMIT;
UPDATE
PER_LIMRA_FEED_SUP TAR SET TAR.SUP_FLAG = 'P' WHERE TAR.ROWID IN (
SELECT RAW_DATA.ROWID FROM PER_LIMRA_FEED_SUP RAW_DATA
INNER JOIN (SELECT DISTINCT AGT FROM PER_AGENT_INFO WHERE CMCD=P_STR_CMCD AND CYCLE_MTH = P_DT_CYCLE_DT ) AGT ON RAW_DATA.AGT=AGT.AGT
INNER JOIN (SELECT DISTINCT AGY FROM PER_AGENCY_INFO WHERE CMCD = P_STR_CMCD AND CYCLE_MTH = P_DT_CYCLE_DT) AGY ON RAW_DATA.AGY=AGY.AGY
WHERE RAW_DATA.CYCLE_DT >= DT_CYCLE_MTH_FIR
AND RAW_DATA.CYCLE_DT < P_DT_CYCLE_DT
AND RAW_DATA.CMCD=P_STR_CMCD
AND RAW_DATA.SUP_FLAG = 'F');
COMMIT;
END IF;
--INSERT MANNUL ADJ INTO PER_LIMRA_POLICY_ADJ
SELECT COUNT(*) INTO DEC_IN_COUNT_C FROM PER_LIMRA_ADJ_FEED WHERE CYCLE_DT = P_DT_CYCLE_DT;
IF DEC_IN_COUNT_C >0 THEN
DELETE FROM PER_LIMRA_POLICY_ADJ WHERE CMCD = P_STR_CMCD AND PROCESS_DT = P_DT_CYCLE_DT AND TRANS_SRC_CD = 'ADJ';
COMMIT;
INSERT INTO PER_LIMRA_POLICY_ADJ NOLOGGING (SEQNUM, CMCD, CYCLE_DT, AGY, AGT,
TRNCD, TRN_YY, TRN_MM, TRN_DD, TRN_TYPE, CHANNEL,
CASE, SA_AMT, ANN_PREM, /*CAP_ANN_PREM,*/
POLNO, CURR, UPD, CNTCT_TYP, CHNL_CD, UNIT_MGR, AREA_MGR, OVRID,
LAPSE_IND, POLICY_MONTH, PLAN_CODE, LIFE_NO, COVERAGE_NO, RIDER_NO, INCLUDE_FLAG, SRV_AGY, SRV_AGT,
PROD_CAT,INSURED_NAME, POL_PTD, AFYP, INCEPTION, CLIENT_ID, CREATE_DT, CREATE_BY,COMPONENT_CODE,AP_POLICY,LAYER_SEQ,BASIC_PLAN_COMP_CD,BASIC_PLAN_CD,SHARE_PER,EXCLUDE_IND,
TRANS_SRC_CD,PROCESS_DT)
SELECT
SEQ_PER_LIMRA_POLICY_ADJ.NEXTVAL,
--DECODE(CMCD,'2','SG','4','BN','ZZ'), CYCLE_DT, AGY, SUBSTR(AGT,-5,5),
FEED.CMCD, P_DT_CYCLE_DT, FEED.AGY, FEED.AGT,
TRNCD, TRN_YY, TRN_MM, TRN_DD,
--TRANSACTION CODE SHOULD BE VERIFY LATER
--MODIFIED BY WING 20140109 START
--CASE WHEN TRNCD='30' THEN STR_NB WHEN TRNCD IN ('65','66','67','68') THEN STR_REINSTATE WHEN TRNCD IN ('56','96','97','98') THEN STR_LAPSE ELSE 'ZZ' END TRN_TYPE,
--CASE WHEN TRNCD='30' THEN STR_NB WHEN TRNCD IN ('56','91','92','93','94','96','97','98') THEN STR_REINSTATE WHEN TRNCD IN ('61','62','63','64','65','66','67','68') THEN STR_LAPSE WHEN TRNCD IN ('GP1','GP2','GP3') THEN 'GP' ELSE 'ZZ' END TRN_TYPE, --HKG002
CASE WHEN TRNCD='30' THEN STR_NB WHEN TRNCD IN ('56','96','97','98') THEN STR_REINSTATE WHEN TRNCD IN ('65','66','67','68') THEN STR_LAPSE WHEN TRNCD IN ('GP1','GP2','GP3') THEN 'GP' ELSE 'ZZ' END TRN_TYPE, --HKG009
AGY_INFO.CHANNEL /*P_STR_CHANNEL*/ AS CHANNEL,
/*(CASE WHEN TRNCD IN ('65','66','67','68') AND CASE_S='-' THEN TO_NUMBER(CASE)/ 10
WHEN TRNCD IN ('65','66','67','68') AND CASE_S='+' THEN TO_NUMBER(CASE)/ 10 *-1
--FOR GP CASE COUNT SHOULD BE DIVIDED BY 100
WHEN TRNCD IN ('G1','G2','G3') THEN TO_NUMBER(CASE)/ 100
WHEN CASE_S='-' THEN TO_NUMBER(CASE)/ 10*-1
ELSE TO_NUMBER(CASE)/ 10
END)*/ TO_NUMBER(CASE) AS CASE,
-- (CASE WHEN TRNCD IN ('56','96','97','98') AND AMT_S='-' THEN TO_NUMBER(AMT)
-- WHEN TRNCD IN ('56','96','97','98') AND AMT_S='+' THEN TO_NUMBER(AMT) *-1
/*(CASE WHEN TRNCD IN ('65','66','67','68') AND AMT_S='-' THEN TO_NUMBER(AMT)
WHEN TRNCD IN ('65','66','67','68') AND AMT_S='+' THEN TO_NUMBER(AMT) *-1
WHEN AMT_S='-' THEN TO_NUMBER(AMT)*-1
ELSE TO_NUMBER(AMT)
END)*/ TO_NUMBER(AMT) AS SA_AMT,
-- (CASE WHEN TRNCD IN ('56','96','97','98') AND ANN_PREM_S='-' THEN TO_NUMBER(ANN_PREM)/ 100
-- WHEN TRNCD IN ('56','96','97','98') AND ANN_PREM_S='+' THEN TO_NUMBER(ANN_PREM)/ 100 *-1
/*(CASE WHEN TRNCD IN ('65','66','67','68') AND ANN_PREM_S='-' THEN TO_NUMBER(ANN_PREM)/ 100
WHEN TRNCD IN ('65','66','67','68') AND ANN_PREM_S='+' THEN TO_NUMBER(ANN_PREM)/ 100 *-1
WHEN ANN_PREM_S='-' THEN TO_NUMBER(ANN_PREM)/ 100*-1
ELSE TO_NUMBER(ANN_PREM)/ 100
END)*/TO_NUMBER(ANN_PREM) AS ANN_PREM,
-- (CASE WHEN TRNCD IN ('56','96','97','98') AND ANN_PREM_S='-' THEN TO_NUMBER(ANN_PREM)/ 100
-- WHEN TRNCD IN ('56','96','97','98') AND ANN_PREM_S='+' THEN TO_NUMBER(ANN_PREM)/ 100 *-1
/*(CASE WHEN TRNCD IN ('65','66','67','68') AND ANN_PREM_S='-' THEN TO_NUMBER(ANN_PREM)/ 100
WHEN TRNCD IN ('65','66','67','68') AND ANN_PREM_S='+' THEN TO_NUMBER(ANN_PREM)/ 100 *-1
WHEN ANN_PREM_S='-' THEN TO_NUMBER(ANN_PREM)/ 100*-1
ELSE TO_NUMBER(ANN_PREM)/ 100
END) AS CAP_ANN_PREM,*/
--MODIFIED BY WING 20140109 END
POLNO, CURR, UPD, CNTCT_TYP, CHNL_CD, UNIT_MGR, AREA_MGR, OVRID,
LAPSE_IND, POLICY_MONTH, PLAN_CODE, LIFE_NO, COVERAGE_NO, RIDER_NO, INCLUDE_FLAG, SRV_AGY, SRV_AGT,
PROD_CAT, INSURED_NAME, POL_PTD, TO_NUMBER(AFYP)/100, INCEPTION,CLIENT_ID, SYSDATE, P_STR_USER,
COMPONENT_CODE,AP_POLICY,LAYER_SEQ,BASIC_PLAN_COMP_CD,BASIC_PLAN_CD,TO_NUMBER(SHARE_PERCENTAGE),
/*CASE WHEN PROD_CAT = 'PA' OR EXL.CLASS_ID IS NOT NULL THEN '0' ELSE '1' END*/'1','ADJ', P_DT_CYCLE_DT
FROM PER_LIMRA_ADJ_FEED FEED, /*PER_LKP_EXCLUSION EXL,*/PER_AGENCY_INFO AGY_INFO
WHERE /*FEED.CYCLE_DT = P_DT_CYCLE_DT
AND*/ AGY_INFO.CMCD = P_STR_CMCD
AND FEED.CMCD=P_STR_CMCD
AND FEED.AGY = AGY_INFO.AGY
AND AGY_INFO.AGY_TYPE = 'AGY'
AND AGY_INFO.CYCLE_MTH = P_DT_CYCLE_DT
/* AND SUBSTR(BASIC_PLAN_CD,1,3)=EXL.CLASS_ID(+)
AND FN_CONVERT_BASIC_PLAN_CD(SUBSTR(BASIC_PLAN_CD,4,3))<=FN_CONVERT_BASIC_PLAN_CD(EXL.BASIC_PLAN_CD_TO(+))
AND FN_CONVERT_BASIC_PLAN_CD(SUBSTR(BASIC_PLAN_CD,4,3))>=FN_CONVERT_BASIC_PLAN_CD(EXL.BASIC_PLAN_CD_FROM(+))*/;
COMMIT;
END IF;
--HKG009
/*
--HKG003 START
UPDATE PER_LIMRA_POLICY_ADJ TAR
SET TAR.POL_TYPE = 'VIT'
WHERE CYCLE_DT=P_DT_CYCLE_DT AND EXISTS
( SELECT 1 FROM PER_VITALITY_INFO VITINFO WHERE TAR.POLNO = VITINFO.POLNO AND TAR.LIFE_NO = VITINFO.LIFE_NO AND TAR.COVERAGE_NO = VITINFO.COVERAGE_NO AND TAR.RIDER_NO = VITINFO.RIDER_NO);
COMMIT;
--HKG003 END
*/
--HKG009
EXCEPTION
/* WHEN INVALID_RECORD_COUNT THEN
RAISE_APPLICATION_ERROR(-20001,'RECORD COUNT ERROR IN SP_PER_LIMRA_SUP_VAD' || SQLERRM);*/
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,'SP_PER_LIMRA_SUP_VAD ERROR ' || SQLERRM);
END;
PROCEDURE SP_PER_LIMRA_POL_CAP
( P_STR_CMCD IN VARCHAR2,
P_DT_CYCLE_DT IN DATE,
P_STR_USER IN VARCHAR2
) IS
V_SQL VARCHAR2(100);
V_MIN_VALUE NUMBER;
BEGIN
V_MIN_VALUE := -9999999999;
V_SQL := 'TRUNCATE TABLE PER_LIMRA_POLICY_CAP_DTL_TEMP';
EXECUTE IMMEDIATE V_SQL;
INSERT INTO PER_LIMRA_POLICY_CAP_DTL_TEMP
SELECT
CMCD,
CYC_DT,
AGY,
AGT,
TRNCD,
TRN_YY,
TRN_MM,
TRN_DD,
MAX(TRN_TYPE),
CHANNEL,
SUM(CASE),
SUM(SA_AMT),
SUM(ANN_PREM),
POLNO,
LAYER_SEQ,
MAX(CURR),
MAX(POLICY_MONTH),
MAX(CASE WHEN SRC_CD <> 'MOV' THEN V_MIN_VALUE ELSE AP_POLICY END) AP_POLICY_MOV,
MAX(CASE WHEN SRC_CD <> 'ADJ' THEN V_MIN_VALUE ELSE NVL(AP_POLICY,V_MIN_VALUE) END) AP_POLICY_ADJ,
MAX(CASE WHEN SRC_CD <> 'SUP' THEN V_MIN_VALUE ELSE AP_POLICY END) AP_POLICY_SUP,
SUM(CASE WHEN SRC_CD = 'SUP' THEN ANN_PREM ELSE 0 END) ANN_PREM_SUP,
MAX(SHARE_PER),
SYSDATE,
P_STR_USER
FROM (
SELECT
CMCD,
--PROCESS_DT CYC_DT,
CYCLE_DT CYC_DT,
AGY,
AGT,
TRNCD,
TRN_YY,
TRN_MM,
--DECODE(TRANS_SRC_CD,'SUP',SUBSTR(TO_CHAR(P_DT_CYCLE_DT,'YYYYMMDD'),-2),TRN_DD) TRN_DD,
TRN_DD,
TRN_TYPE,
CHANNEL,
CASE,
SA_AMT,
ANN_PREM,
POLNO,
LAYER_SEQ,
CURR,
POLICY_MONTH,
AP_POLICY,
SHARE_PER,
TRANS_SRC_CD SRC_CD
FROM PER_LIMRA_POLICY_ADJ WHERE CMCD = P_STR_CMCD AND CYCLE_DT >= TRUNC(P_DT_CYCLE_DT,'MM')
AND CYCLE_DT <= P_DT_CYCLE_DT AND EXCLUDE_IND = '1'
UNION ALL
SELECT
CMCD,
CYCLE_DT CYC_DT,
AGY,
AGT,
TRNCD,
TRN_YY,
TRN_MM,
TRN_DD,
TRN_TYPE,
CHANNEL,
CASE,
SA_AMT,
ANN_PREM,
POLNO,
LAYER_SEQ,
CURR,
POLICY_MONTH,
AP_POLICY,
SHARE_PER,
'MOV' SRC_CD
FROM PER_LIMRA_POLICY WHERE CMCD = P_STR_CMCD AND CYCLE_DT >= TRUNC(P_DT_CYCLE_DT,'MM')
AND CYCLE_DT <= P_DT_CYCLE_DT AND EXCLUDE_IND = '1')
GROUP BY
CMCD,
CYC_DT,
AGY,
AGT,
TRNCD,
TRN_YY,
TRN_MM,
TRN_DD,
CHANNEL,
POLNO,
LAYER_SEQ,
AP_POLICY;
COMMIT;
V_SQL := 'TRUNCATE TABLE PER_LIMRA_POLICY_CAP_TEMP';
EXECUTE IMMEDIATE V_SQL;
INSERT INTO PER_LIMRA_POLICY_CAP_TEMP
SELECT
CMCD,
CYCLE_DT,
AGY,
AGT,
TRNCD,
TRN_YY,
TRN_MM,
TRN_DD,
TRN_TYPE,
CHANNEL,
CASE,
SA_AMT,
ANN_PREM,
POLNO,
TXTLAYER_SEQ,
CURR,
POLICY_MONTH,
CASE WHEN AP_POLICY_ADJ <> V_MIN_VALUE THEN AP_POLICY_ADJ
WHEN AP_POLICY_MOV <> V_MIN_VALUE THEN AP_POLICY_MOV
WHEN AP_POLICY_SUP <> V_MIN_VALUE THEN AP_POLICY_SUP
ELSE 0 END,
SHARE_PER,
SYSDATE,
P_STR_USER
FROM PER_LIMRA_POLICY_CAP_DTL_TEMP;
COMMIT;
-- HANDLING FOR FOR US$6000 CAP ON REINSTATEMENT
UPDATE PER_LIMRA_POLICY_CAP_TEMP A
SET A.AP_POLICY = A.ANN_PREM/(A.SHARE_PER/100)
WHERE A.TRNCD IN ('56','96','97','98') AND A.SHARE_PER > 0;
COMMIT;
DELETE FROM PER_LIMRA_POLICY_CAP WHERE CMCD = P_STR_CMCD AND CYCLE_DT >= TRUNC(P_DT_CYCLE_DT,'MM')
AND CYCLE_DT <= P_DT_CYCLE_DT;
COMMIT;
INSERT INTO PER_LIMRA_POLICY_CAP
SELECT
CMCD,
CYCLE_DT,
AGY,
AGT,
TRNCD,
TRN_YY,
TRN_MM,
TRN_DD,
TRN_TYPE,
CHANNEL,
CASE,
SA_AMT,
ANN_PREM,
(CASE WHEN AP_POLICY*SHARE_PER/100 - ANN_PREM < 6000*SHARE_PER/100 AND AP_POLICY*SHARE_PER/100 <= 6000*SHARE_PER/100 THEN ANN_PREM
WHEN AP_POLICY*SHARE_PER/100 - ANN_PREM < 6000*SHARE_PER/100 AND AP_POLICY*SHARE_PER/100 > 6000*SHARE_PER/100 THEN 6000*SHARE_PER/100 - (AP_POLICY*SHARE_PER/100 - ANN_PREM)
WHEN AP_POLICY*SHARE_PER/100 - ANN_PREM >= 6000*SHARE_PER/100 AND AP_POLICY*SHARE_PER/100 >= 6000*SHARE_PER/100 THEN 0
WHEN AP_POLICY*SHARE_PER/100 - ANN_PREM >= 6000*SHARE_PER/100 AND AP_POLICY*SHARE_PER/100 < 6000*SHARE_PER/100 THEN -(6000*SHARE_PER/100 - AP_POLICY*SHARE_PER/100)
ELSE 0
END) CAP_ANN_PREM,
POLNO,
TXTLAYER_SEQ,
CURR,
POLICY_MONTH,
(AP_POLICY*SHARE_PER/100 - ANN_PREM) AP_POLICY_BEF,
AP_POLICY*SHARE_PER/100,
SHARE_PER,
SYSDATE,
P_STR_USER
FROM PER_LIMRA_POLICY_CAP_TEMP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,'SP_PER_LIMRA_POL_CAP ERROR ' || SQLERRM);
END;
END ;
Telegram: DB developers channel