CREATE OR REPLACE PROCEDURE STAGING.M1_Urban_Rural IS -- ********************************************************************************************* -- Stored Procedure Header -- ********************************************************************************************* -- Report Name : M1 Urban Rural Report -- Release : NA -- Purpose : This Procedure is used to generate data for the -- M1 Urban Rural Report -- Author : Unknown -- Called Func : Fn_Channel_Name, Fn_Smnameid, Fn_Pol_Sector_Category -- Tables Used : -- -- Indexes Used : -- -- Modification Log : -- Ver No Date Author Modification Reviewed By -- 1.10 07-AUG-2007 Ambar Sarkar Changes for rel 8.1 -- ********************************************************************************************* TYPE C_NAME IS TABLE OF CHM_CHANNEL_M.STRCHANNELNAME%TYPE INDEX BY BINARY_INTEGER; TYPE AGT_CD IS TABLE OF CHM_AGENT_M.STRAGENTCD%TYPE INDEX BY BINARY_INTEGER; TYPE AGT_NAME1 IS TABLE OF VARCHAR(500); TYPE AGT_DTJOIN1 IS TABLE OF CHM_AGENT_M.DTJOIN%TYPE INDEX BY BINARY_INTEGER; TYPE AGT_STATUS1 IS TABLE OF VARCHAR(50); TYPE AGT_BRANCH1 IS TABLE OF CHM_AGENT_M.STRAGENCYBRCHCD%TYPE INDEX BY BINARY_INTEGER; TYPE AGT_TDT1 IS TABLE OF CHM_AGENT_M.DTTERMINATION%TYPE INDEX BY BINARY_INTEGER; TYPE SUP_AGT1 IS TABLE OF CHM_AGENT_M.STRSUPAGENTCD%TYPE INDEX BY BINARY_INTEGER; TYPE SUP_NAME1 IS TABLE OF VARCHAR(500); TYPE APP_AGT1 IS TABLE OF CHM_AGENT_M.STRRECRUITBY%TYPE INDEX BY BINARY_INTEGER; TYPE APP_NAME1 IS TABLE OF VARCHAR(500); TYPE POL_ID1 IS TABLE OF CHM_POL_AGENT_ORG.STRPOLNBR%TYPE INDEX BY BINARY_INTEGER; TYPE PLAN_ID1 IS TABLE OF TCVG.PLAN_ID%TYPE INDEX BY BINARY_INTEGER; TYPE SECTOR1 IS TABLE OF VARCHAR(50); TYPE IS_INSURANCE_AGENT1 IS TABLE OF VARCHAR(3); TYPE AGENT_ROLE1 IS TABLE OF VARCHAR(100); TYPE DPRODNPERC1 IS TABLE OF CHM_POL_AGENT_ORG.DPRODNPERC%TYPE INDEX BY BINARY_INTEGER; TYPE NPRODSEQ1 IS TABLE OF CHM_POL_AGENT_ORG.NPRODSEQ%TYPE INDEX BY BINARY_INTEGER; TYPE AFYP_YEAR1 IS TABLE OF VARCHAR(5); TYPE AFYP_MONTH1 IS TABLE OF VARCHAR(5); TYPE AFYP_NPMTMODE1 IS TABLE OF VARCHAR(5); TYPE AFYP_NYEARINFORCEFROM1 IS TABLE OF NUMBER; TYPE AFYP_AMT1 IS TABLE OF NUMBER; TYPE MFYP_AMT1 IS TABLE OF NUMBER; TYPE TOPUP_AMT1 IS TABLE OF NUMBER; TYPE DAPPRAMNT1 IS TABLE OF NUMBER; TYPE OPP_AMT1 IS TABLE OF NUMBER; TYPE YC_AMT1 IS TABLE OF NUMBER; TYPE NPOLYEAR1 IS TABLE OF NUMBER; TYPE YC_AMT_MF1 IS TABLE OF NUMBER; TYPE NCOMMTYPE IS TABLE OF NUMBER; CURSOR UR_BASIC IS SELECT Fn_Channel_Name(CHM_AGENT_M.CCHANNELTYPE) CHANNEL_NAME, CHM_AGENT_M.STRAGENTCD ID, CHM_AGENT_M.STRTITLE||' '||CHM_AGENT_M.STRFIRSTNAME||' '||CHM_AGENT_M.STRLASTNAME NAME, TO_CHAR(CHM_AGENT_M.DTJOIN,'dd-MON-YYYY') JOIN_DT, DECODE(CHM_AGENT_M.NSTATUS,1,'Pending',5,'Active',10,'Suspended',15,'Terminated',20,'Rejected',25,'Deleted') STATUS, CHM_AGENT_M.STRAGENCYBRCHCD GO, TO_CHAR(CHM_AGENT_M.DTTERMINATION,'dd-MON-YYYY') TERMINATIONDATE, CHM_AGENT_M.STRSUPAGENTCD REPORTINGTOSMID, Fn_Smnameid(CHM_AGENT_M.STRAGENTCD,2) REPORTINGTO, CHM_AGENT_M.STRRECRUITBY APOINTEDBYID, (SELECT AGTRECRUIT.STRTITLE||' '||AGTRECRUIT.STRFIRSTNAME||' '||AGTRECRUIT.STRLASTNAME FROM CHM_AGENT_M AGTRECRUIT WHERE AGTRECRUIT.STRAGENTCD = CHM_AGENT_M.STRRECRUITBY) APOINTEDBYNAME, ORG.STRPOLNBR POL_ID, ORG.STRPRODCD PLAN_ID, Fn_Pol_Sector_Category(ORG.STRPOLNBR, 'S') SECTOR, (SELECT DM.STRDESGNDESC FROM CHM_DESIGNATION_M DM WHERE DM.STRDESGNCD = CHM_AGENT_M.STRDESGNCD) AGENT_ROLE, DECODE(CHM_AGENT_M.NISINSAGENCY, 1, 'YES', 2, 'NO' ) IS_INSURANCE_AGENT FROM CHM_AGENT_M, CHM_POL_AGENT_ORG ORG WHERE --ORG.STRPRODCD <> 'TN60R' -- TN60R introduced in rel 8.1 --AND ORG.STRAGENTCD = CHM_AGENT_M.STRAGENTCD AND CHM_AGENT_M.CCHANNELTYPE = 'R'; CURSOR AFYP_BASIC (POL VARCHAR2, PLAN VARCHAR2, AGT VARCHAR2, CHKYEAR VARCHAR2) IS SELECT TO_CHAR(B.DTPOLCOMC, 'YYYY') AFYP_YEAR, -- Enforced cases TO_CHAR(B.DTPOLCOMC, 'MM') AFYP_MONTH, B.NPMTMODE, A1.NYEARINFORCEFROM, ((C.DMODALPRMAMNT * DECODE(B.NPMTMODE, 99, 1, B.NPMTMODE) * A1.DPRODNPERC) / 100) PREM_VAL, A1.DPRODNPERC / 100, A1.NPRODSEQ FROM CHM_POL_AGENT_ORG A1, PS_POLICY_MST B, PS_POL_PROD_DTL C WHERE A1.STRPOLNBR = POL AND B.STRPOLNBR = A1.STRPOLNBR AND A1.STRPOLNBR = C.STRPOLNBR AND A1.STRPRODCD = TRIM(PLAN) AND A1.STRPRODCD = C.STRPRODCD AND A1.NPRODSEQ = C.NPRODSEQ AND NVL(C.NPRODSTAT, 1) <> 9 AND A1.DTEFFTO = '31-DEC-3099' AND A1.STRAGENTCD = AGT AND TO_CHAR(B.DTPOLCOMC, 'YYYY') = CHKYEAR UNION ALL SELECT TO_CHAR(TPOL.POL_STAT_CHNG_DT, 'YYYY') AFYP_YEAR, -- NTU-ed cases TO_CHAR(TPOL.POL_STAT_CHNG_DT, 'MM') AFYP_MONTH, B.NPMTMODE, A1.NYEARINFORCEFROM, ((C.DMODALPRMAMNT * DECODE(B.NPMTMODE, 99, 1, B.NPMTMODE) * A1.DPRODNPERC) / 100) * -1 PREM_VAL, (A1.DPRODNPERC / 100) * -1, A1.NPRODSEQ FROM CHM_POL_AGENT_ORG A1, PS_POLICY_MST B, PS_POL_PROD_DTL C, TPOL WHERE A1.STRPOLNBR = POL AND B.STRPOLNBR = A1.STRPOLNBR AND A1.STRPOLNBR = C.STRPOLNBR AND A1.STRPRODCD = TRIM(PLAN) AND A1.STRPRODCD = C.STRPRODCD AND A1.NPRODSEQ = C.NPRODSEQ AND NVL(C.NPRODSTAT, 1) <> 9 AND A1.DTEFFTO = '31-DEC-3099' AND A1.STRAGENTCD = AGT AND B.STRPOLNBR = TRIM(TPOL.POL_ID) AND TPOL.CO_ID = 'CP' AND TPOL.POL_CSTAT_CD = 'A' AND TPOL.POL_STAT_CHNG_DT >= TO_DATE('01-JAN-' || CHKYEAR) UNION ALL SELECT TO_CHAR(TPOL.POL_STAT_CHNG_DT, 'YYYY') AFYP_YEAR, -- Re-enforced cases TO_CHAR(TPOL.POL_STAT_CHNG_DT, 'MM') AFYP_MONTH, B.NPMTMODE, A1.NYEARINFORCEFROM, ((C.DMODALPRMAMNT * DECODE(B.NPMTMODE, 99, 1, B.NPMTMODE) * A1.DPRODNPERC) / 100) PREM_VAL, (A1.DPRODNPERC / 100), A1.NPRODSEQ FROM CHM_POL_AGENT_ORG A1, PS_POLICY_MST B, PS_POL_PROD_DTL C, TPOL WHERE A1.STRPOLNBR = POL AND B.STRPOLNBR = A1.STRPOLNBR AND A1.STRPOLNBR = C.STRPOLNBR AND A1.STRPRODCD = TRIM(PLAN) AND A1.STRPRODCD = C.STRPRODCD AND A1.NPRODSEQ = C.NPRODSEQ AND NVL(C.NPRODSTAT, 1) <> 9 AND A1.DTEFFTO = '31-DEC-3099' AND A1.STRAGENTCD = AGT AND TO_CHAR(B.DTPOLCOMC, 'YYYY') <> CHKYEAR AND B.STRPOLNBR = TRIM(TPOL.POL_ID) AND TPOL.CO_ID = 'CP' AND TPOL.POL_PREV_CSTAT_CD = 'A' AND TPOL.POL_CSTAT_CD = '1' AND TPOL.POL_STAT_CHNG_DT >= TO_DATE('01-JAN-' || CHKYEAR); CURSOR MFYP_BASIC (POL VARCHAR2, PLAN VARCHAR2, AGT VARCHAR2, CHKYEAR VARCHAR2) IS SELECT TO_CHAR(A.DTAPPR, 'YYYY') AFYP_YEAR, TO_CHAR(A.DTAPPR,'MM') AFYP_MONTH, C.NPMTMODE, A.NPOLYEARINFORCE, (A.DAPPRAMNT * B.DPRODNPERC) / 100, (A.DAPPRAMNT * B.DCOMMNPERC) / 100, (A.DAPPRAMNT / ABS(A.DAPPRAMNT)) * B.DPRODNPERC / 100, B.NPRODSEQ, (A.DTOPUPAMNT * B.DPRODNPERC) / 100 FROM CHM_SC_ORC_INT A, CHM_POL_AGENT_ORG B, PS_POLICY_MST C WHERE TO_CHAR(A.DTAPPR, 'YYYY') = CHKYEAR AND A.STRPOLNBR = POL AND A.STRPRODCD = TRIM(PLAN) AND B.STRAGENTCD = AGT AND A.NAPPRTYPE IN(1,4) AND A.STRPOLNBR = B.STRPOLNBR AND A.STRPRODCD = B.STRPRODCD AND C.STRPOLNBR = B.STRPOLNBR AND A.DAPPRAMNT <> 0; CURSOR OPP_BASIC (POL VARCHAR2, PLAN VARCHAR2, AGT VARCHAR2, CHKYEAR VARCHAR2) IS SELECT TO_CHAR(DTAPPR, 'YYYY'), TO_CHAR(DTAPPR, 'MM'), NVL((DAPPRAMNT), 0) FROM CHM_SC_ORC_INT ORC_INT WHERE STRPRODCD = 'OP00' AND TO_CHAR(DTAPPR, 'YYYY') = CHKYEAR AND STRPOLNBR = POL AND NPOLYEARINFORCE = 1 AND STRPRODCD = TRIM(PLAN) AND ( ((ORC_INT.DTDUE <> ORC_INT.DTRISKCOMMNT) AND STRPOLNBR = (SELECT DISTINCT STRPOLNBR FROM CHM_POL_AGENT_ORG WHERE STRAGENTCD = AGT AND STRPOLNBR = POL AND STRPRODCD = TRIM(PLAN)) ) OR ((ORC_INT.DTDUE = ORC_INT.DTRISKCOMMNT) AND STRPOLNBR = (SELECT DISTINCT STRPOLNBR FROM CHM_POL_AGENT_SERV WHERE STRAGENTCD = AGT AND STRPOLNBR = POL) ) ); CURSOR YC_BASIC (POL VARCHAR2, PLAN VARCHAR2, AGT VARCHAR2, CHKYEAR VARCHAR2) IS SELECT POLCOM.NCALMONTH, NPOLYEAR, POLCOM.DCOMMAMNT, NCOMMTYPE FROM CHM_AGT_POL_COM POLCOM WHERE POLCOM.ICALYEAR = CHKYEAR AND TRIM(PLAN) = POLCOM.STRPRODCD AND POL = POLCOM.STRPOLNBR AND POLCOM.STRAGENTCD = AGT; CHANNEL_NAME C_NAME; AGT AGT_CD; AGT_NAME AGT_NAME1; JOIN_DT AGT_DTJOIN1; STATUS AGT_STATUS1; AFT_BRCD AGT_BRANCH1; TEM_DT AGT_TDT1; SUP_ID SUP_AGT1; SUP_NAME SUP_NAME1; APP_AGT APP_AGT1; APP_NAME APP_NAME1; POL_ID POL_ID1; PLAN_ID PLAN_ID1; SECTOR SECTOR1; IS_INSURANCE_AGENT IS_INSURANCE_AGENT1; AGENT_ROLE AGENT_ROLE1; DPRODNPERC DPRODNPERC1; NPRODSEQ NPRODSEQ1; AFYP_YEAR AFYP_YEAR1; AFYP_MONTH AFYP_MONTH1; AFYP_NPMTMODE AFYP_NPMTMODE1; AFYP_NYEARINFORCEFROM AFYP_NYEARINFORCEFROM1; AFYP_AMT AFYP_AMT1; MFYP_AMT MFYP_AMT1; TOPUP_AMT TOPUP_AMT1; DAPPRAMNT DAPPRAMNT1; OPP_AMT OPP_AMT1; YC_AMT YC_AMT1; YC_NPOLYEAR NPOLYEAR1; YC_AMT_MF YC_AMT_MF1; YC_NCOMMTYPE NCOMMTYPE; AFYP_AMOUNT_MTD NUMBER; AFYP_AMOUNT_YTD NUMBER; EASY_AFYP_AMOUNT_MTD NUMBER; EASY_AFYP_AMOUNT_YTD NUMBER; AFYP_SP_AMOUNT_MTD NUMBER; AFYP_SP_AMOUNT_YTD NUMBER; AFYP_ANNREG1040_AMOUNT_MTD NUMBER; AFYP_ANNREG1040_AMOUNT_YTD NUMBER; AFYP_ANNSNGPRM_AMOUNT_MTD NUMBER; AFYP_ANNSNGPRM_AMOUNT_YTD NUMBER; AFYP_LIFE_LINE_MTD NUMBER; AFYP_LIFE_LINE_YTD NUMBER; AFYP_UL_MTD NUMBER; AFYP_UL_YTD NUMBER; UL_TOPUP_MTD NUMBER; UL_TOPUP_YTD NUMBER; NO_OF_PAID_CASES_MTD NUMBER; NO_OF_PAID_CASES_YTD NUMBER; NO_OF_PAID_CASES_ANN_MTD NUMBER; NO_OF_PAID_CASES_ANN_YTD NUMBER; NO_OF_PAID_CASES_UL_MTD NUMBER; NO_OF_PAID_CASES_UL_YTD NUMBER; NO_OF_PC_LIFE_LINE_MTD NUMBER; NO_OF_PC_LIFE_LINE_YTD NUMBER; MFYP_AMOUNT_MTD NUMBER; MFYP_AMOUNT_YTD NUMBER; MFYP_LIFE_LINE_MTD NUMBER; MFYP_LIFE_LINE_YTD NUMBER; MFYP_UL_AMOUNT_MTD NUMBER; MFYP_UL_AMOUNT_YTD NUMBER; MRYP_UL_AMOUNT_MTD NUMBER; MRYP_UL_AMOUNT_YTD NUMBER; MRYP_LIFE_LINE_MTD NUMBER; MRYP_LIFE_LINE_YTD NUMBER; EASY_MFYP_AMOUNT_MTD NUMBER; EASY_MFYP_AMOUNT_YTD NUMBER; MFYP_SP_AMOUNT_MTD NUMBER; MFYP_SP_AMOUNT_YTD NUMBER; MFYC_AMOUNT_MTD NUMBER; MFYC_AMOUNT_YTD NUMBER; MRYC_AMOUNT_MTD NUMBER; MRYC_AMOUNT_YTD NUMBER; MRYP_AMOUNT_MTD NUMBER; MRYP_AMOUNT_YTD NUMBER; MRYP_SP_AMOUNT_MTD NUMBER; MRYP_SP_AMOUNT_YTD NUMBER; MFYP_ANNREG1040_AMOUNT_MTD NUMBER; MFYP_ANNREG1040_AMOUNT_YTD NUMBER; MFYP_ANNSNGPRM_AMOUNT_MTD NUMBER; MFYP_ANNSNGPRM_AMOUNT_YTD NUMBER; MRYP_UL_TOPUP_MTD NUMBER; MRYP_UL_TOPUP_YTD NUMBER; NPOLYEAR NUMBER; CNT NUMBER; CHK_YEAR VARCHAR(4); CHK_MONTH VARCHAR(2); FYC_MTD NUMBER; RYC_MTD NUMBER; FYC_YTD NUMBER; RYC_YTD NUMBER; OPP_MTD NUMBER; OPP_YTD NUMBER; CNT_M NUMBER; SNAPDATE DATE; BEGIN -- PICKING THE DATE FROM SNAPSHOT TABLE SELECT (ST.REFRESH_DATE-1) INTO SNAPDATE FROM SNAPSHOT_REFRESH_STATUS ST; EXECUTE IMMEDIATE 'TRUNCATE TABLE M1_URBAN_RURAL_DATA'; CHK_YEAR := TO_CHAR(TRUNC(SNAPDATE),'YYYY'); CHK_MONTH := TO_CHAR(TRUNC(SNAPDATE),'MM'); OPEN UR_BASIC; FETCH UR_BASIC BULK COLLECT INTO CHANNEL_NAME, AGT, AGT_NAME, JOIN_DT, STATUS, AFT_BRCD, TEM_DT, SUP_ID, SUP_NAME, APP_AGT, APP_NAME, POL_ID, PLAN_ID, SECTOR, AGENT_ROLE, IS_INSURANCE_AGENT; CLOSE UR_BASIC; FOR I IN POL_ID.FIRST..POL_ID.LAST LOOP CNT_M := 0; CNT := 0; SELECT NVL(COUNT(1),0) INTO CNT FROM CHM_ALT_POL_HDR H, PS_POLICY_MST P WHERE H.STRPOLNBR = POL_ID(I) AND TO_CHAR(H.DTCREATED,'YYYY') = CHK_YEAR AND H.STRPOLNBR = P.STRPOLNBR AND P.NSTATUS = 15; SELECT NVL(COUNT(1),0) INTO CNT_M FROM CHM_ALT_POL_HDR H, PS_POLICY_MST P WHERE H.STRPOLNBR = POL_ID(I) AND TO_CHAR(H.DTCREATED,'YYYYMM') = CHK_YEAR || CHK_MONTH AND H.STRPOLNBR = P.STRPOLNBR AND P.NSTATUS = 15; AFYP_AMOUNT_MTD := 0; AFYP_AMOUNT_YTD := 0; EASY_AFYP_AMOUNT_MTD := 0; EASY_AFYP_AMOUNT_YTD := 0; AFYP_SP_AMOUNT_MTD := 0; AFYP_SP_AMOUNT_YTD := 0; AFYP_ANNREG1040_AMOUNT_MTD := 0; AFYP_ANNREG1040_AMOUNT_YTD := 0; AFYP_ANNSNGPRM_AMOUNT_MTD := 0; AFYP_ANNSNGPRM_AMOUNT_YTD := 0; AFYP_LIFE_LINE_MTD := 0; AFYP_LIFE_LINE_YTD := 0; AFYP_UL_MTD := 0; AFYP_UL_YTD := 0; UL_TOPUP_MTD := 0; UL_TOPUP_YTD := 0; NO_OF_PAID_CASES_MTD := 0; NO_OF_PAID_CASES_YTD := 0; NO_OF_PAID_CASES_ANN_MTD := 0; NO_OF_PAID_CASES_ANN_YTD := 0; NO_OF_PAID_CASES_UL_MTD := 0; NO_OF_PAID_CASES_UL_YTD := 0; NO_OF_PC_LIFE_LINE_MTD := 0; NO_OF_PC_LIFE_LINE_YTD := 0; MFYP_AMOUNT_MTD := 0; MFYP_AMOUNT_YTD := 0; EASY_MFYP_AMOUNT_MTD := 0; EASY_MFYP_AMOUNT_YTD := 0; MFYP_SP_AMOUNT_MTD := 0; MFYP_SP_AMOUNT_YTD := 0; MFYC_AMOUNT_MTD := 0; MFYC_AMOUNT_YTD := 0; MRYC_AMOUNT_MTD := 0; MRYC_AMOUNT_YTD := 0; MRYP_AMOUNT_MTD := 0; MRYP_AMOUNT_YTD := 0; MRYP_SP_AMOUNT_MTD := 0; MRYP_SP_AMOUNT_YTD := 0; MFYP_UL_AMOUNT_MTD := 0; MFYP_UL_AMOUNT_YTD := 0; MFYP_LIFE_LINE_MTD := 0; MFYP_LIFE_LINE_YTD := 0; MRYP_UL_AMOUNT_MTD := 0; MRYP_UL_AMOUNT_YTD := 0; MRYP_UL_TOPUP_MTD := 0; MRYP_UL_TOPUP_YTD := 0; MRYP_LIFE_LINE_MTD := 0; MRYP_LIFE_LINE_YTD := 0; MFYP_ANNREG1040_AMOUNT_MTD := 0; MFYP_ANNREG1040_AMOUNT_YTD := 0; MFYP_ANNSNGPRM_AMOUNT_MTD := 0; MFYP_ANNSNGPRM_AMOUNT_YTD := 0; OPP_MTD := 0; OPP_YTD := 0; FYC_MTD := 0; FYC_YTD := 0; RYC_MTD := 0; RYC_YTD := 0; /*-------------------AFYP and Paidcase Calculations--------------------*/ BEGIN OPEN AFYP_BASIC(POL_ID(I), PLAN_ID(I), AGT(I), CHK_YEAR); FETCH AFYP_BASIC BULK COLLECT INTO AFYP_YEAR, AFYP_MONTH, AFYP_NPMTMODE, AFYP_NYEARINFORCEFROM, AFYP_AMT, DPRODNPERC, NPRODSEQ; CLOSE AFYP_BASIC; FOR J IN AFYP_YEAR.FIRST..AFYP_YEAR.LAST LOOP IF TRIM(PLAN_ID(I)) <> 'AP00R' AND TRIM(PLAN_ID(I)) <> 'AP00S' AND TRIM(PLAN_ID(I)) <> 'OP00' AND TRIM(PLAN_ID(I)) <> 'TN60R' AND TRIM(PLAN_ID(I)) NOT LIKE 'U%' -- Revised FS for rel 8.1, UL products to be excluded AND AFYP_NYEARINFORCEFROM(J) = 1 THEN IF AFYP_MONTH(J) = CHK_MONTH THEN IF AFYP_NPMTMODE(J) <> '99' THEN IF CNT_M = 0 THEN AFYP_AMOUNT_MTD := AFYP_AMOUNT_MTD+AFYP_AMT(J); END IF; IF CNT = 0 THEN AFYP_AMOUNT_YTD := AFYP_AMOUNT_YTD+AFYP_AMT(J); END IF; ELSE IF CNT_M = 0 THEN AFYP_SP_AMOUNT_MTD := AFYP_SP_AMOUNT_MTD+AFYP_AMT(J); END IF; IF CNT = 0 THEN AFYP_SP_AMOUNT_YTD := AFYP_SP_AMOUNT_YTD+AFYP_AMT(J); END IF; END IF; ELSE IF AFYP_NPMTMODE(J) <> '99' THEN IF CNT = 0 THEN AFYP_AMOUNT_YTD := AFYP_AMOUNT_YTD+AFYP_AMT(J); END IF; ELSE IF CNT = 0 THEN AFYP_SP_AMOUNT_YTD := AFYP_SP_AMOUNT_YTD+AFYP_AMT(J); END IF; END IF; END IF; END IF; IF TRIM(PLAN_ID(I)) = 'TN60R' -- Revised FS for rel 8.1, Easy AFYP columns population AND AFYP_NYEARINFORCEFROM(J) = 1 THEN IF AFYP_MONTH(J) = CHK_MONTH THEN IF CNT_M = 0 THEN EASY_AFYP_AMOUNT_MTD := EASY_AFYP_AMOUNT_MTD+AFYP_AMT(J); END IF; IF CNT = 0 THEN EASY_AFYP_AMOUNT_YTD := EASY_AFYP_AMOUNT_YTD+AFYP_AMT(J); END IF; ELSE IF CNT = 0 THEN EASY_AFYP_AMOUNT_YTD := EASY_AFYP_AMOUNT_YTD+AFYP_AMT(J); END IF; END IF; END IF; IF TRIM(PLAN_ID(I)) = 'AP00R' AND AFYP_NYEARINFORCEFROM(J) = 1 THEN IF AFYP_MONTH(J) = CHK_MONTH THEN IF AFYP_NPMTMODE(J) = '1' THEN IF CNT_M = 0 THEN AFYP_ANNREG1040_AMOUNT_MTD := AFYP_ANNREG1040_AMOUNT_MTD+AFYP_AMT(J); END IF; IF CNT = 0 THEN AFYP_ANNREG1040_AMOUNT_YTD := AFYP_ANNREG1040_AMOUNT_YTD+AFYP_AMT(J); END IF; END IF; ELSE IF AFYP_NPMTMODE(J) = '1' THEN IF CNT = 0 THEN AFYP_ANNREG1040_AMOUNT_YTD := AFYP_ANNREG1040_AMOUNT_YTD+AFYP_AMT(J); END IF; END IF; END IF; END IF; IF TRIM(PLAN_ID(I)) IN ('AP00S','IAL','IAL5','IAL10','IAL15','IAL20','IALROP', 'IALC','IALC5','IALC10','IALC15','IALC20','IALRPC') --Added by Atul for Relase8.4 on 5-May-2008 AND AFYP_NYEARINFORCEFROM(J) = 1 AND AFYP_NPMTMODE(J) = '99' THEN IF AFYP_MONTH(J) = CHK_MONTH THEN IF CNT_M = 0 THEN AFYP_ANNSNGPRM_AMOUNT_MTD := AFYP_ANNSNGPRM_AMOUNT_MTD+AFYP_AMT(J); END IF; IF CNT= 0 THEN AFYP_ANNSNGPRM_AMOUNT_YTD := AFYP_ANNSNGPRM_AMOUNT_YTD+AFYP_AMT(J); END IF; ELSE IF CNT = 0 THEN AFYP_ANNSNGPRM_AMOUNT_YTD := AFYP_ANNSNGPRM_AMOUNT_YTD+AFYP_AMT(J); END IF; END IF; END IF; IF TRIM(PLAN_ID(I)) LIKE 'U%' -- Revised FS for rel 8.1, UL products to have separate columns AND AFYP_NYEARINFORCEFROM(J) = 1 THEN IF AFYP_MONTH(J) = CHK_MONTH THEN IF CNT_M = 0 THEN AFYP_UL_MTD := AFYP_UL_MTD+AFYP_AMT(J); END IF; IF CNT = 0 THEN AFYP_UL_YTD := AFYP_UL_YTD+AFYP_AMT(J); END IF; ELSE IF CNT = 0 THEN AFYP_UL_YTD := AFYP_UL_YTD+AFYP_AMT(J); END IF; END IF; END IF; IF TRIM(PLAN_ID(I)) IN ('DHCS','DHCSP','DWLPL','DWPLP','DSNET','DACIB','DTPD','DADB') -- Rel 8.3 AND AFYP_NYEARINFORCEFROM(J) = 1 THEN IF AFYP_MONTH(J) = CHK_MONTH THEN IF CNT_M = 0 THEN AFYP_LIFE_LINE_MTD := AFYP_LIFE_LINE_MTD+AFYP_AMT(J); END IF; IF CNT = 0 THEN AFYP_LIFE_LINE_YTD := AFYP_LIFE_LINE_YTD+AFYP_AMT(J); END IF; ELSE IF CNT = 0 THEN AFYP_LIFE_LINE_YTD := AFYP_LIFE_LINE_YTD+AFYP_AMT(J); END IF; END IF; END IF; IF TRIM(PLAN_ID(I)) <> 'AP00R' AND TRIM(PLAN_ID(I)) <> 'AP00S' AND TRIM(PLAN_ID(I)) NOT LIKE 'U%' AND AFYP_NYEARINFORCEFROM(J) = 1 AND NPRODSEQ(J) = 1 THEN IF AFYP_MONTH(J) = CHK_MONTH THEN IF CNT_M = 0 THEN NO_OF_PAID_CASES_MTD := NO_OF_PAID_CASES_MTD+DPRODNPERC(J); END IF; IF CNT = 0 THEN NO_OF_PAID_CASES_YTD := NO_OF_PAID_CASES_YTD+DPRODNPERC(J); END IF; ELSE IF CNT = 0 THEN NO_OF_PAID_CASES_YTD := NO_OF_PAID_CASES_YTD+DPRODNPERC(J); END IF; END IF; END IF; IF TRIM(PLAN_ID(I)) LIKE 'U%' AND AFYP_NYEARINFORCEFROM(J) = 1 AND NPRODSEQ(J) = 1 THEN IF AFYP_MONTH(J) = CHK_MONTH THEN IF CNT_M = 0 THEN NO_OF_PAID_CASES_UL_MTD := NO_OF_PAID_CASES_UL_MTD+DPRODNPERC(J); END IF; IF CNT = 0 THEN NO_OF_PAID_CASES_UL_YTD := NO_OF_PAID_CASES_UL_YTD+DPRODNPERC(J); END IF; ELSE IF CNT = 0 THEN NO_OF_PAID_CASES_UL_YTD := NO_OF_PAID_CASES_UL_YTD+DPRODNPERC(J); END IF; END IF; END IF; IF TRIM(PLAN_ID(I)) IN ('DHCS','DHCSP','DWLPL','DWPLP','DSNET','DACIB','DTPD','DADB') -- Rel 8.3 AND AFYP_NYEARINFORCEFROM(J) = 1 AND NPRODSEQ(J) = 1 THEN IF AFYP_MONTH(J) = CHK_MONTH THEN IF CNT_M = 0 THEN NO_OF_PC_LIFE_LINE_MTD := NO_OF_PC_LIFE_LINE_MTD+DPRODNPERC(J); END IF; IF CNT = 0 THEN NO_OF_PC_LIFE_LINE_YTD := NO_OF_PC_LIFE_LINE_YTD+DPRODNPERC(J); END IF; ELSE IF CNT = 0 THEN NO_OF_PC_LIFE_LINE_YTD := NO_OF_PC_LIFE_LINE_YTD+DPRODNPERC(J); END IF; END IF; END IF; IF (TRIM(PLAN_ID(I)) = 'AP00R' OR TRIM(PLAN_ID(I)) IN ('AP00S','IAL','IAL5','IAL10','IAL15','IAL20', 'IALROP','IALC','IALC5','IALC10','IALC15','IALC20','IALRPC') --Added by Atul for Release8.4 on 5-May-2008 ) AND AFYP_NYEARINFORCEFROM(J) = 1 AND NPRODSEQ(J) = 1 THEN IF AFYP_MONTH(J) = CHK_MONTH THEN IF CNT_M = 0 THEN NO_OF_PAID_CASES_ANN_MTD := NO_OF_PAID_CASES_ANN_MTD+DPRODNPERC(J); END IF; IF CNT = 0 THEN NO_OF_PAID_CASES_ANN_YTD := NO_OF_PAID_CASES_ANN_YTD+DPRODNPERC(J); END IF; ELSE IF CNT = 0 THEN NO_OF_PAID_CASES_ANN_YTD := NO_OF_PAID_CASES_ANN_YTD+DPRODNPERC(J); END IF; END IF; END IF; END LOOP; EXCEPTION WHEN OTHERS THEN AFYP_AMOUNT_MTD:=0; AFYP_AMOUNT_YTD:=0; AFYP_SP_AMOUNT_MTD:=0; AFYP_SP_AMOUNT_YTD:=0; AFYP_ANNREG1040_AMOUNT_MTD:=0; AFYP_ANNREG1040_AMOUNT_YTD:=0; AFYP_ANNSNGPRM_AMOUNT_MTD:=0; AFYP_ANNSNGPRM_AMOUNT_YTD:=0; NO_OF_PAID_CASES_MTD:=0; NO_OF_PAID_CASES_YTD:=0; NO_OF_PAID_CASES_ANN_MTD:=0; NO_OF_PAID_CASES_ANN_YTD:=0; NO_OF_PC_LIFE_LINE_MTD:=0; NO_OF_PC_LIFE_LINE_YTD:=0; AFYP_LIFE_LINE_MTD:=0; AFYP_LIFE_LINE_YTD:=0; END; /*--------------------------MFYP Calculations--------------------------*/ BEGIN OPEN MFYP_BASIC(POL_ID(I), PLAN_ID(I), AGT(I), CHK_YEAR); FETCH MFYP_BASIC BULK COLLECT INTO AFYP_YEAR, AFYP_MONTH, AFYP_NPMTMODE, AFYP_NYEARINFORCEFROM, MFYP_AMT, YC_AMT_MF, DPRODNPERC, NPRODSEQ, TOPUP_AMT; CLOSE MFYP_BASIC; FOR J IN AFYP_YEAR.FIRST..AFYP_YEAR.LAST LOOP IF AFYP_NYEARINFORCEFROM(J) = 1 THEN IF TRIM(PLAN_ID(I)) <> 'AP00R' AND TRIM(PLAN_ID(I)) <> 'AP00S' AND TRIM(PLAN_ID(I)) NOT LIKE 'U%' AND TRIM(PLAN_ID(I)) <> 'OP00' AND TRIM(PLAN_ID(I)) <> 'TN60R' THEN IF AFYP_NPMTMODE(J) <> '99' THEN IF AFYP_MONTH(J) = CHK_MONTH THEN MFYP_AMOUNT_MTD := MFYP_AMOUNT_MTD+MFYP_AMT(J); MFYP_AMOUNT_YTD := MFYP_AMOUNT_YTD+MFYP_AMT(J); SELECT NVL(COUNT(1),0) INTO CNT FROM CHM_ALT_POL_HDR WHERE STRPOLNBR = POL_ID(I); ELSE MFYP_AMOUNT_YTD := MFYP_AMOUNT_YTD+MFYP_AMT(J); SELECT NVL(COUNT(1),0) INTO CNT FROM CHM_ALT_POL_HDR WHERE STRPOLNBR = POL_ID(I); END IF; ELSE IF AFYP_MONTH(J) = CHK_MONTH THEN MFYP_SP_AMOUNT_MTD := MFYP_SP_AMOUNT_MTD+MFYP_AMT(J); MFYP_SP_AMOUNT_YTD := MFYP_SP_AMOUNT_YTD+MFYP_AMT(J); ELSE MFYP_SP_AMOUNT_YTD := MFYP_SP_AMOUNT_YTD+MFYP_AMT(J); SELECT NVL(COUNT(1),0) INTO CNT FROM CHM_ALT_POL_HDR WHERE STRPOLNBR = POL_ID(I); END IF; END IF; END IF; IF AFYP_NPMTMODE(J) <> '99' THEN IF TRIM(PLAN_ID(I)) = 'AP00R' THEN IF AFYP_MONTH(J) = CHK_MONTH THEN MFYP_ANNREG1040_AMOUNT_MTD := MFYP_ANNREG1040_AMOUNT_MTD+MFYP_AMT(J); MFYP_ANNREG1040_AMOUNT_YTD := MFYP_ANNREG1040_AMOUNT_YTD+MFYP_AMT(J); ELSE MFYP_ANNREG1040_AMOUNT_YTD := MFYP_ANNREG1040_AMOUNT_YTD+MFYP_AMT(J); END IF; END IF; ELSE IF TRIM(PLAN_ID(I)) IN ('AP00S','IAL','IAL5','IAL10','IAL15','IAL20','IALROP', 'IALC','IALC5','IALC10','IALC15','IALC20','IALRPC') --Added by Atul for Release8.4 on 5-May-2008 THEN IF AFYP_MONTH(J) = CHK_MONTH THEN MFYP_ANNSNGPRM_AMOUNT_MTD := MFYP_ANNSNGPRM_AMOUNT_MTD+MFYP_AMT(J); MFYP_ANNSNGPRM_AMOUNT_YTD := MFYP_ANNSNGPRM_AMOUNT_YTD+MFYP_AMT(J); ELSE MFYP_ANNSNGPRM_AMOUNT_YTD := MFYP_ANNSNGPRM_AMOUNT_YTD+MFYP_AMT(J); END IF; END IF; END IF; IF TRIM(PLAN_ID(I)) = 'TN60R' THEN -- Revised FS for rel 8.1, Easy MFYP columns population IF AFYP_MONTH(J) = CHK_MONTH THEN EASY_MFYP_AMOUNT_MTD := EASY_MFYP_AMOUNT_MTD+MFYP_AMT(J); EASY_MFYP_AMOUNT_YTD := EASY_MFYP_AMOUNT_YTD+MFYP_AMT(J); ELSE EASY_MFYP_AMOUNT_YTD := EASY_MFYP_AMOUNT_YTD+MFYP_AMT(J); END IF; END IF; IF TRIM(PLAN_ID(I)) IN ('DHCS','DHCSP','DWLPL','DWPLP','DSNET','DACIB','DTPD','DADB') THEN -- Rel 8.3 IF AFYP_MONTH(J) = CHK_MONTH THEN MFYP_LIFE_LINE_MTD := MFYP_LIFE_LINE_MTD+MFYP_AMT(J); MFYP_LIFE_LINE_YTD := MFYP_LIFE_LINE_YTD+MFYP_AMT(J); ELSE MFYP_LIFE_LINE_YTD := MFYP_LIFE_LINE_YTD+MFYP_AMT(J); END IF; END IF; IF TRIM(PLAN_ID(I)) LIKE 'U%' THEN -- Revised FS for rel 8.1, MFYP UL columns added IF AFYP_MONTH(J) = CHK_MONTH THEN MFYP_UL_AMOUNT_MTD := MFYP_UL_AMOUNT_MTD+TOPUP_AMT(J)+MFYP_AMT(J); MFYP_UL_AMOUNT_YTD := MFYP_UL_AMOUNT_YTD+TOPUP_AMT(J)+MFYP_AMT(J); UL_TOPUP_MTD := UL_TOPUP_MTD+TOPUP_AMT(J); UL_TOPUP_YTD := UL_TOPUP_YTD+TOPUP_AMT(J); ELSE MFYP_UL_AMOUNT_YTD := MFYP_UL_AMOUNT_YTD+TOPUP_AMT(J)+MFYP_AMT(J); UL_TOPUP_YTD := UL_TOPUP_YTD+TOPUP_AMT(J); END IF; END IF; ELSE IF TRIM(PLAN_ID(I)) <> 'AP00R' AND TRIM(PLAN_ID(I)) <>'AP00S' AND TRIM(PLAN_ID(I)) NOT LIKE 'U%' AND TRIM(PLAN_ID(I)) <>'OP00' AND PLAN_ID(I) <> 'TN60R' THEN IF AFYP_NPMTMODE(J) <> '99' THEN IF AFYP_MONTH(J) = CHK_MONTH THEN MRYP_AMOUNT_MTD := MRYP_AMOUNT_MTD+MFYP_AMT(J); MRYP_AMOUNT_YTD := MRYP_AMOUNT_YTD+MFYP_AMT(J); ELSE MRYP_AMOUNT_YTD := MRYP_AMOUNT_YTD+MFYP_AMT(J); END IF; ELSE IF AFYP_MONTH(J) = CHK_MONTH THEN MRYP_SP_AMOUNT_MTD := MRYP_SP_AMOUNT_MTD+MFYP_AMT(J); MRYP_SP_AMOUNT_YTD := MRYP_SP_AMOUNT_YTD+MFYP_AMT(J); ELSE MRYP_SP_AMOUNT_YTD := MRYP_SP_AMOUNT_YTD+MFYP_AMT(J); END IF; END IF; END IF; IF TRIM(PLAN_ID(I)) IN ('DHCS','DHCSP','DWLPL','DWPLP','DSNET','DACIB','DTPD','DADB') THEN -- Rel 8.3 IF AFYP_MONTH(J) = CHK_MONTH THEN MRYP_LIFE_LINE_MTD := MRYP_LIFE_LINE_MTD+MFYP_AMT(J); MRYP_LIFE_LINE_YTD := MRYP_LIFE_LINE_YTD+MFYP_AMT(J); ELSE MRYP_LIFE_LINE_YTD := MRYP_LIFE_LINE_YTD+MFYP_AMT(J); END IF; END IF; /*-----Renewal Year Premium and Topup for UL policies------*/ IF TRIM(PLAN_ID(I)) LIKE 'U%' THEN -- Revised FS for rel 8.1, RYP UL columns added IF AFYP_MONTH(J) = CHK_MONTH THEN MRYP_UL_AMOUNT_MTD := MRYP_UL_AMOUNT_MTD+TOPUP_AMT(J)+MFYP_AMT(J); MRYP_UL_AMOUNT_YTD := MRYP_UL_AMOUNT_YTD+TOPUP_AMT(J)+MFYP_AMT(J); MRYP_UL_TOPUP_MTD := MRYP_UL_TOPUP_MTD+TOPUP_AMT(J); MRYP_UL_TOPUP_YTD := MRYP_UL_TOPUP_YTD+TOPUP_AMT(J); UL_TOPUP_MTD := UL_TOPUP_MTD+TOPUP_AMT(J); UL_TOPUP_YTD := UL_TOPUP_YTD+TOPUP_AMT(J); MFYP_UL_AMOUNT_MTD := MFYP_UL_AMOUNT_MTD+TOPUP_AMT(J); MFYP_UL_AMOUNT_YTD := MFYP_UL_AMOUNT_YTD+TOPUP_AMT(J); ELSE MRYP_UL_AMOUNT_YTD := MRYP_UL_AMOUNT_YTD+MFYP_AMT(J); MRYP_UL_TOPUP_YTD := MRYP_UL_TOPUP_YTD+TOPUP_AMT(J); UL_TOPUP_YTD := UL_TOPUP_YTD+TOPUP_AMT(J); MFYP_UL_AMOUNT_YTD := MFYP_UL_AMOUNT_YTD+TOPUP_AMT(J); END IF; END IF; END IF; END LOOP; EXCEPTION WHEN OTHERS THEN MFYP_AMOUNT_MTD:=0; MFYP_AMOUNT_YTD:=0; MFYP_SP_AMOUNT_MTD:=0; MFYP_SP_AMOUNT_YTD:=0; MRYP_AMOUNT_MTD:=0; MRYP_AMOUNT_YTD:=0; MRYP_SP_AMOUNT_MTD:=0; MRYP_SP_AMOUNT_YTD:=0; MFYC_AMOUNT_MTD:=0; MFYC_AMOUNT_YTD:=0; MRYC_AMOUNT_MTD:=0; MRYC_AMOUNT_YTD:=0; MFYP_ANNREG1040_AMOUNT_MTD:=0; MFYP_ANNREG1040_AMOUNT_YTD:=0; MFYP_ANNSNGPRM_AMOUNT_MTD:=0; MFYP_ANNSNGPRM_AMOUNT_YTD:=0; MFYP_LIFE_LINE_MTD:=0; MFYP_LIFE_LINE_YTD:=0; MRYP_LIFE_LINE_MTD:=0; MRYP_LIFE_LINE_YTD:=0; END; /*----------------------OPP AFYP, FYP Calculations---------------------*/ BEGIN OPEN OPP_BASIC(POL_ID(I), PLAN_ID(I), AGT(I), CHK_YEAR); FETCH OPP_BASIC BULK COLLECT INTO AFYP_YEAR, AFYP_MONTH, OPP_AMT; CLOSE OPP_BASIC; FOR J IN AFYP_YEAR.FIRST..AFYP_YEAR.LAST LOOP IF AFYP_MONTH(J) = CHK_MONTH THEN OPP_MTD := OPP_MTD+OPP_AMT(J); OPP_YTD := OPP_YTD+OPP_AMT(J); ELSE OPP_YTD := OPP_YTD+OPP_AMT(J); END IF; END LOOP; EXCEPTION WHEN OTHERS THEN OPP_MTD := 0; OPP_YTD := 0; END; /*-----------------------Commission Calculations-----------------------*/ BEGIN OPEN YC_BASIC(POL_ID(I), PLAN_ID(I), AGT(I), CHK_YEAR); FETCH YC_BASIC BULK COLLECT INTO AFYP_MONTH, YC_NPOLYEAR, YC_AMT, YC_NCOMMTYPE ; CLOSE YC_BASIC; FOR J IN AFYP_MONTH.FIRST..AFYP_MONTH.LAST LOOP IF AFYP_MONTH(J) = TO_NUMBER(CHK_MONTH) THEN IF YC_NPOLYEAR(J) = 1 OR YC_NCOMMTYPE(J) = 23 THEN FYC_MTD := FYC_MTD+YC_AMT(J); FYC_YTD := FYC_YTD+YC_AMT(J); ELSE RYC_MTD := RYC_MTD+YC_AMT(J); RYC_YTD := RYC_YTD+YC_AMT(J); END IF; ELSE IF YC_NPOLYEAR(J) = 1 OR YC_NCOMMTYPE(J) = 23 THEN FYC_YTD := FYC_YTD + YC_AMT(J); ELSE RYC_YTD := RYC_YTD+YC_AMT(J); END IF; END IF; END LOOP; EXCEPTION WHEN OTHERS THEN FYC_MTD:=0; RYC_MTD:=0; FYC_YTD:=0; RYC_YTD:=0; END; INSERT INTO M1_URBAN_RURAL_DATA (POL_ID, PLAN_ID, CHANNELNAME, ID, NAME, JOIN_DT, STATUS, --1 GO, TERMINATIONDATE, REPORTINGTOSMID, REPORTINGTO, APOINTEDBYID, --2 APOINTEDBYNAME, SECTOR, AFYP_AMOUNT_MTD, EASY_AFYP_AMOUNT_MTD, --3 OPP_AFYP_MTD,FYC_MTD,RYC_MTD ,AFYP_SP_AMOUNT_MTD, AFYP_AMOUNT_ANNREG1040_MTD, --4 AFYP_SP_AMOUNT_ANNSNGPRM_MTD, AFYP_AMOUNT_YTD,EASY_AFYP_AMOUNT_YTD, --5 OPP_AFYP_YTD, FYC_YTD,RYC_YTD, AFYP_SP_AMOUNT_YTD, --6 AFYP_AMOUNT_ANNREG1040_YTD, AFYP_SP_AMOUNT_ANNSNGPRM_YTD, MFYP_AMOUNT_MTD, --7 EASY_MFYP_MTD, OPP_MFYP_MTD, SP_MFYP_MTD, RYP_MFYP_MTD, ANNSNGPRM_MFYP_MTD, --8 ANNREG1040_MFYP_MTD, MFYP_AMOUNT_YTD, EASY_MFYP_YTD, OPP_MFYP_YTD, --9 SP_MFYP_YTD, RYP_MFYP_YTD, ANNSNGPRM_MFYP_YTD, ANNREG1040_MFYP_YTD, --10 NO_OF_PAID_CASES_MTD, NO_OF_PAID_CASES_ANN_MTD, NO_OF_PAID_CASES_YTD, --11 NO_OF_PAID_CASES_ANN_YTD, SNAPSHOT_DATE, AFYP_UL_MTD, AFYP_UL_YTD, --12 AFYP_UL_TOPUP_MTD, AFYP_UL_TOPUP_YTD, MFYP_UL_TOP_UP_MTD, MFYP_UL_TOP_UP_YTD, --13 MFYP_UL_AMOUNT_MTD, MFYP_UL_AMOUNT_YTD, RYP_UL_AMOUNT_MTD, RYP_UL_AMOUNT_YTD, --14 RYP_UL_TOPUP_MTD, RYP_UL_TOPUP_YTD, NO_OF_PAID_CASES_UL_MTD, --15 NO_OF_PAID_CASES_UL_YTD, AGENT_ROLE, IS_INSURANCE_AGENT, --16 AFYP_LIFE_LINE_MTD, AFYP_LIFE_LINE_YTD, --17 MFYP_LIFE_LINE_MTD, MFYP_LIFE_LINE_YTD, --18 MRYP_LIFE_LINE_MTD, MRYP_LIFE_LINE_YTD, --19 NO_OF_PC_LIFE_LINE_MTD, NO_OF_PC_LIFE_LINE_YTD) --20 VALUES (POL_ID(I), PLAN_ID(I), CHANNEL_NAME(I), AGT(I), AGT_NAME(I), JOIN_DT(I), STATUS(I), --1 AFT_BRCD(I), TEM_DT(I), SUP_ID(I), SUP_NAME(I), APP_AGT(I), --2 APP_NAME(I), SECTOR(I), AFYP_AMOUNT_MTD, EASY_AFYP_AMOUNT_MTD, --3 OPP_MTD, FYC_MTD, RYC_MTD, AFYP_SP_AMOUNT_MTD, AFYP_ANNREG1040_AMOUNT_MTD, --4 AFYP_ANNSNGPRM_AMOUNT_MTD, AFYP_AMOUNT_YTD, EASY_AFYP_AMOUNT_YTD, --5 OPP_YTD, FYC_YTD, RYC_YTD, AFYP_SP_AMOUNT_YTD, --6 AFYP_ANNREG1040_AMOUNT_YTD, AFYP_ANNSNGPRM_AMOUNT_YTD, MFYP_AMOUNT_MTD, --7 EASY_MFYP_AMOUNT_MTD, OPP_MTD, MFYP_SP_AMOUNT_MTD, MRYP_AMOUNT_MTD, MFYP_ANNSNGPRM_AMOUNT_MTD, --8 MFYP_ANNREG1040_AMOUNT_MTD, MFYP_AMOUNT_YTD, EASY_MFYP_AMOUNT_YTD, OPP_YTD, --9 MFYP_SP_AMOUNT_YTD, MRYP_AMOUNT_YTD, MFYP_ANNSNGPRM_AMOUNT_YTD, MFYP_ANNREG1040_AMOUNT_YTD, --10 NO_OF_PAID_CASES_MTD, NO_OF_PAID_CASES_ANN_MTD, NO_OF_PAID_CASES_YTD, --11 NO_OF_PAID_CASES_ANN_YTD, SNAPDATE, AFYP_UL_MTD+UL_TOPUP_MTD, --12 AFYP_UL_YTD+UL_TOPUP_YTD, UL_TOPUP_MTD, UL_TOPUP_YTD, UL_TOPUP_MTD, UL_TOPUP_YTD, --13 MFYP_UL_AMOUNT_MTD, MFYP_UL_AMOUNT_YTD, MRYP_UL_AMOUNT_MTD, MRYP_UL_AMOUNT_YTD, --14 MRYP_UL_TOPUP_MTD, MRYP_UL_TOPUP_YTD, NO_OF_PAID_CASES_UL_MTD, --15 NO_OF_PAID_CASES_UL_YTD, AGENT_ROLE(I), IS_INSURANCE_AGENT(I), --16 AFYP_LIFE_LINE_MTD, AFYP_LIFE_LINE_YTD, --17 MFYP_LIFE_LINE_MTD, MFYP_LIFE_LINE_YTD, --18 MRYP_LIFE_LINE_MTD, MRYP_LIFE_LINE_YTD, --19 NO_OF_PC_LIFE_LINE_MTD, NO_OF_PC_LIFE_LINE_YTD); --20 COMMIT; END LOOP; COMMIT; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE (SQLERRM); END M1_Urban_Rural; /