TSO SQL SET SSID DB0A
SET SQLENGINE = DB2
SET WIDTH = 999
SET PANEL = 999
SET NODATA = ' '
-READ INPUTP &STRDT.I10
-READ INPUTP &ENDDT.I10
-READ INPUTP &PAYOTYP.A1
-READ INREGN &RGN.A5
-RUN
-*
SQL DB2
SELECT A.CO_ID, A.POL_ID, A.CVG_NUM,
B.INSRD_CLI_ID,
C.CLI_INDV_EFF_DT,
C.CLI_INDV_GR_CD,
C.ENTR_SUR_NM,
C.ENTR_GIV_NM,
C.CLI_INDV_SUR_NM,
C.CLI_INDV_GIV_NM,
C.CLI_INDV_MID_NM
FROM &RGN.TROPP A, &RGN.TCVGC B, &RGN.TCLNM C
WHERE A.CO_ID = B.CO_ID AND
A.POL_ID = B.POL_ID AND
A.CVG_NUM = B.CVG_NUM AND
B.CVG_CLI_REL_TYP_CD = 'P' AND
A.CO_ID = C.CO_ID AND
B.INSRD_CLI_ID = C.CLI_ID AND
C.CLI_INDV_EFF_DT =
(SELECT MAX(C.CLI_INDV_EFF_DT)
FROM &RGN.TROPP A, &RGN.TCVGC B, &RGN.TCLNM C
WHERE A.CO_ID = B.CO_ID AND
A.POL_ID = B.POL_ID AND
A.CVG_NUM = B.CVG_NUM AND
B.CVG_CLI_REL_TYP_CD = 'P' AND
A.CO_ID = C.CO_ID AND
B.INSRD_CLI_ID = C.CLI_ID AND
C.CLI_INDV_EFF_DT <=
A.ROP_PAYO_EFF_DT)
ORDER BY A.CO_ID, A.POL_ID, A.CVG_NUM;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS TPNAME
END
-RUN
-*
SQL DB2
SELECT A.CO_ID, A.POL_ID, A.CVG_NUM, A.CVG_BNFT_NUM,
A.ROP_PAYO_EFF_DT, A.ROP_PAYO_SEQ_NUM,
CHAR(A.PREV_UPDT_TS) AS ROP_PROC_DATE,
A.PAYO_STAT_CD, A.ROP_PD_AMT, A.CAROVR_ROP_PD_AMT,
-* A.ROP_PD_RT, A.ROP_FORFT_RT,
B.ISS_EFF_DT,
( CASE
WHEN B.SUPP_BNFT_CD = 'J' THEN '1'
WHEN B.SUPP_BNFT_CD = 'S' THEN '3'
-* WHEN (A.ROP_PD_RT + A.ROP_FORFT_RT) < 1 THEN '3'
ELSE '2'
END ) AS OUTTYPE,
E.CVG_ORIG_CD
FROM &RGN.TROPP A , &RGN.TCVGB B , &RGN.TCVG E
WHERE A.CO_ID = B.CO_ID
AND A.POL_ID = B.POL_ID
AND A.CVG_NUM = B.CVG_NUM
AND A.CVG_BNFT_NUM = B.CVG_BNFT_NUM
AND A.CO_ID = E.CO_ID
AND A.POL_ID = E.POL_ID
AND A.CVG_NUM = E.CVG_NUM
ORDER BY A.CO_ID, A.POL_ID, A.CVG_NUM, A.CVG_BNFT_NUM;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS TPROPP
END
-RUN
-*
SQL DB2
SELECT M.CO_ID, M.POL_ID, M.CVG_NUM,
M.ASSUM_CO_ID,
N.ETBL_DESC_TXT
FROM &RGN.TRI M, &RGN.TEDIT N
WHERE M.CO_ID = N.CO_ID AND
M.ASSUM_CO_ID = N.ETBL_VALU_ID AND
N.ETBL_TYP_ID = 'REIN' AND
N.ETBL_LANG_CD = 'E' AND
M.CSN_TRTY_TYP_CD = 'F'
ORDER BY M.CO_ID, M.POL_ID, M.CVG_NUM;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS TPREIN
END
-RUN
-*
JOIN
TPROPP.CO_ID AND TPROPP.POL_ID AND TPROPP.CVG_NUM
IN TPROPP
TO TPNAME.CO_ID AND TPNAME.POL_ID AND TPNAME.CVG_NUM
IN TPNAME
AS J001
END
JOIN
TPROPP.CO_ID AND TPROPP.POL_ID AND TPROPP.CVG_NUM
IN TPROPP
TO TPREIN.CO_ID AND TPREIN.POL_ID AND TPREIN.CVG_NUM
IN TPREIN
AS J002
END
-*
DEFINE FILE TPROPP
-* DEFINE BEGIN BUSSRCE
BUSSRCE/A50 = IF TPROPP.CVG_ORIG_CD EQ '2'
THEN 'CHILD TERMRIDER CONVERSION' ELSE
IF TPROPP.CVG_ORIG_CD EQ 'E'
THEN 'CONVERSION OF TERM POLICY OR COVERAGE' ELSE
IF TPROPP.CVG_ORIG_CD EQ 'F'
THEN 'CONVERSION FROM FAMILY RIDER' ELSE
IF TPROPP.CVG_ORIG_CD EQ 'U'
THEN 'EXCHANGE' ELSE
IF TPROPP.CVG_ORIG_CD EQ '7'
THEN 'EXERCISING' ELSE
IF TPROPP.CVG_ORIG_CD EQ 'T'
THEN 'EXTERNAL' ELSE
IF TPROPP.CVG_ORIG_CD EQ 'Z'
THEN 'FULL CONVERSION' ELSE
IF TPROPP.CVG_ORIG_CD EQ 'Y'
THEN 'GROUP POLICY CONVERSION' ELSE
IF TPROPP.CVG_ORIG_CD EQ 'C'
THEN 'GUARANTEED ISSUED' ELSE
IF TPROPP.CVG_ORIG_CD EQ '6'
THEN 'GIR' ELSE
IF TPROPP.CVG_ORIG_CD EQ 'S'
THEN 'INTERNAL' ELSE
IF TPROPP.CVG_ORIG_CD EQ 'H'
THEN 'ISSUED UNDER BUSINESS PURCHASE OPTION' ELSE
IF TPROPP.CVG_ORIG_CD EQ 'G'
THEN 'ISSUED UNDER GIO' ELSE
IF TPROPP.CVG_ORIG_CD EQ '5'
THEN 'ISSUED UNDER SIB' ELSE
IF TPROPP.CVG_ORIG_CD EQ 'D'
THEN 'LIFE AND ANNUITY COMBO' ELSE
IF TPROPP.CVG_ORIG_CD EQ 'A'
THEN 'NEW BUSINESS - REGULAR' ELSE
IF TPROPP.CVG_ORIG_CD EQ 'B'
THEN 'NEW BUSINESS - SPECIAL' ELSE
IF TPROPP.CVG_ORIG_CD EQ 'W'
THEN 'NON-CONTRACTUAL CONVERSION' ELSE
IF TPROPP.CVG_ORIG_CD EQ 'X'
THEN 'OPTIMIZATION/UPGRADE' ELSE
IF TPROPP.CVG_ORIG_CD EQ '4'
THEN 'OEB' ELSE
IF TPROPP.CVG_ORIG_CD EQ '0'
THEN 'PARTIAL CONVERSION' ELSE
IF TPROPP.CVG_ORIG_CD EQ 'O'
THEN 'POLICY PLAN CHANGE' ELSE
IF TPROPP.CVG_ORIG_CD EQ '3'
THEN 'POLICY SPLIT' ELSE
IF TPROPP.CVG_ORIG_CD EQ 'R'
THEN 'REPLACEMENT' ELSE
IF TPROPP.CVG_ORIG_CD EQ 'K'
THEN 'REPLACEMENT OF EXISTING POLICY/COV' ELSE
IF TPROPP.CVG_ORIG_CD EQ '1'
THEN 'TERM CONVERSION' ELSE
IF TPROPP.CVG_ORIG_CD EQ 'V'
THEN 'TRANSFER' ELSE
IF TPROPP.CVG_ORIG_CD EQ 'I'
THEN 'VESTED ANNUITY' ELSE
IF TPROPP.CVG_ORIG_CD EQ 'N'
THEN 'NEW INSURANCE ISSUED IN INGENIUM' ELSE
'UNKNOWN';
REINSURER/A10 = SUBSTR (80,TPREIN.ETBL_DESC_TXT,1,10,10,REINSURER);
PRCYYYY/A4 = SUBSTR (26,TPROPP.ROP_PROC_DATE,1,4,4,PRCYYYY);
PRCMM/A2 = SUBSTR (26,TPROPP.ROP_PROC_DATE,6,7,2,PRCMM);
PRCDD/A2 = SUBSTR (26,TPROPP.ROP_PROC_DATE,9,10,2,PRCDD);
PRCDATE/A8YYMD = PRCYYYY | PRCMM | PRCDD;
PAYODESC/A25 = IF TPROPP.OUTTYPE EQ '1'
THEN 'DEATH ' ELSE
IF TPROPP.OUTTYPE EQ '2'
THEN 'EXPIRY OR EARLY SURRENDER' ELSE
'PARTIAL SURRENDER ';
STRDTTM/A10 = '&STRDT';
ENDDATE/YYMD = '&ENDDT';
STRDATE/YYMD = IF STRDTTM EQ ' '
THEN ENDDATE - 7 ELSE
'&STRDT';
PRCDATE1/YYMD = PRCDATE;
INSURED_NAM/A51 = TPNAME.ENTR_SUR_NM ||
(' ' | TPNAME.ENTR_GIV_NM);
CLNM_EFF/YYMD = TPNAME.CLI_INDV_EFF_DT;
-* DEFINE END BUSSRCE
END
OFFLINE
-*
TABLE FILE TPROPP
PRINT TPROPP.CO_ID NOPRINT
TPROPP.PAYO_STAT_CD NOPRINT
TPROPP.POL_ID AS 'POLICY,NUMBER'
TPROPP.CVG_NUM AS 'COVERAGE,NUMBER'
TPROPP.CVG_BNFT_NUM NOPRINT
TPROPP.ISS_EFF_DT AS 'ROP,ISSUE DATE'
PRCDATE AS 'PAYOUT,PROCESSING,DATE'
TPROPP.ROP_PAYO_EFF_DT AS 'PAYOUT,EFFEECTIVE,DATE'
TPROPP.ROP_PD_AMT/P17.2M AS 'ROP PAYOUT,AMOUNT'
TPROPP.CAROVR_ROP_PD_AMT/P17.2M AS 'CARRIED OVER,PAYOUT AMOUNT'
BUSSRCE AS 'SOURCE OF,BUSINESS'
TPNAME.INSRD_CLI_ID AS 'CLIENT ID'
INSURED_NAM AS 'INSURED,NAME'
CLNM_EFF AS 'NAME,EFFECTIVE'
-*TPROPP.ASSUM_CO_ID
REINSURER AS 'REINSURER'
-* PAYODESC AS 'PAYOUT,DESCRIPTION'
BY TPROPP.PAYODESC
NOPRINT
-* ROWS 'DEATH ' OVER
-* 'EXPIRY OR EARLY SURRENDER' OVER
-* 'PARTIAL SURRENDER '
-* SUBTOTAL AS 'SUBTOTAL ROP PAYOUT,'
BY TPROPP.CO_ID NOPRINT
BY TPROPP.POL_ID NOPRINT
BY TPROPP.CVG_NUM NOPRINT
BY TPROPP.CVG_BNFT_NUM NOPRINT
WHERE (TPROPP.PAYO_STAT_CD EQ '&PAYOTYP')
AND (PRCDATE1 GE STRDATE AND PRCDATE1 LE ENDDATE);
HEADING
"CI PAYOUT REPORT
<70 DATE: &DATETRMDYY <102 TIME: <108 &TOD </1"
"FOR THE PERIOD: <STRDATE TO <PRCDATE"
" "
ON TPROPP.PAYODESC SUBTOTAL AS 'SUBTOTAL ROP PAYOUT,'
ON TABLE COLUMN-TOTAL AS 'GRAND TOTAL : '
END
-RUN
-*
FIN
