- Purpuse: Risk control index by channel to SS
- Data source: A01M
- Edit by Brady in May. 2015
CLEAR ALL
CLEAR
SET SAFETY OFF
YR=SPACE(4)
MON=SPACE(2)
P13MS=SPACE(6)
P13ME=SPACE(6)
P25MS=SPACE(6)
P25ME=SPACE(6)
@ 13, 07 SAY ” Input Year (EX.2010) : ” FONT “Arial”,14 COLOR BG/W*
@ 13, 42 GET YR FONT “Arial”,12 COLOR B+/W*
@ 15, 07 SAY ” Input Month (EX.08) : ” FONT “Arial”,14 COLOR BG/W*
@ 15, 42 GET MON FONT “Arial”,12 COLOR B+/W*
@ 17, 07 SAY ” 13M Start (EX.201302) : ” FONT “Arial”,14 COLOR BG/W*
@ 17, 45 GET P13MS FONT “Arial”,12 COLOR B+/W*
@ 19, 07 SAY ” 13M End (EX.201302) : ” FONT “Arial”,14 COLOR BG/W*
@ 19, 45 GET P13ME FONT “Arial”,12 COLOR B+/W*
@ 21, 07 SAY ” 25M Start (EX.201302) : ” FONT “Arial”,14 COLOR BG/W*
@ 21, 45 GET P25MS FONT “Arial”,12 COLOR B+/W*
@ 23, 07 SAY ” 25M End (EX.201302) : ” FONT “Arial”,14 COLOR BG/W*
@ 23, 45 GET P25ME FONT “Arial”,12 COLOR B+/W*
READ
CYM=STR((VAL(YR)-1911)*100+VAL(MON),5)
?CYM
OPATH=”D:\企業精算部\01 – AUM and CF information\”+YR+MON+”\”
DSpath=”D:\foxpro data\”
SELECT 0
USE “D:\企業精算部\01 – AUM and CF information\ACC_A01M.dbf”
COPY TO OPATH+”A01M_ACC_”+YR+MON+”.dbf”
USE OPATH+”A01M_ACC_”+YR+MON+”.dbf”
APPEND FROM DSpath+”A01M\”+”A01M_”+CYM+”.DBF” FOR (stsect=”AL” OR stsect=”LL”)
REPLACE t_index WITH YR+MON FOR t_index=” “
SELECT 0
USE “D:\企業精算部\01 – AUM and CF information\ACC_F1819.dbf”
COPY TO OPATH+”F1819_ACC_”+YR+MON+”.dbf”
USE OPATH+”F1819_ACC_”+YR+MON+”.dbf”
APPEND FROM DSpath+”F1819\”+”F1819_”+CYM+”.DBF”
REPLACE t_index WITH YR+MON FOR t_index=” “
SELECT chdrnum, crtable, t_index, SUM(av) as av FROM OPATH+”F1819_ACC_”+YR+MON+”.dbf” GROUP BY 1,2,3 INTO TABLE “&OPATH”+”AUM.dbf”
*==For Pre 13M 分母(開始)===
SELECT CHDRNUM, CRTABLE, LEFT(CRTABLE,3) AS PRODUCT, INT(HISSDATE/100) AS ISSUETIME, STSECT, STFUND, STATCODE, INSTBCHNL, CAMPAIGN, CNTCURR, PREMIUMB01, PREMIUMC, t_index;
FROM OPATH+”A01M_ACC_”+YR+MON+”.dbf” WHERE (INT(HISSDATE/100)>=VAL(P13MS) AND INT(HISSDATE/100)<=VAL(P13ME)) AND INT(HISSDATE/100)=VAL(t_index) INTO TABLE “&OPATH”+”AA.dbf” *FROM OPATH+”A01M_ACC_”+YR+MON+”.dbf” WHERE (INT(HISSDATE/100)>=VAL(P13MS) AND INT(HISSDATE/100)<=VAL(P13ME)) AND INT(HISSDATE/100)+100=VAL(t_index) INTO TABLE “&OPATH”+”AA.dbf”
SCAN
DO CASE
CASE LEFT(crtable, 2)=”XV”
replace crtable WITH “V”+STR(crtable,3,1)+”S1”
CASE LEFT(crtable, 1)=”X” AND (RIGHT(crtable,2)=”N1″ OR RIGHT(crtable,2)=”A1″)
replace crtable WITH “L”+ RIGHT(crtable, 3)
OTHERWISE
replace crtable WITH crtable
ENDCASE
ENDSCAN
SELECT chdrnum, crtable, product, issuetime, stsect, stfund, statcode, instbchnl, campaign, cntcurr, ” ” as Line, SUM(premiumb01+premiumc) as ACC_Prem, t_index, VAL(” “) as AUM FROM aa GROUP BY 1,2,3,4,5,6,7,8,9,10,11,13 INTO TABLE “&OPATH”+”P13M.dbf”
*==For Pre 13M 分母(結束)===
*==For Pre 13M 分子(開始)===
CLOSE TABLES
SELE A
USE “&OPATH”+”AUM.dbf”
INDEX ON chdrnum+crtable+t_index TAG kkk
SET ORDER TO kkk
SELE D
USE “D:\企業精算部\01 – AUM and CF information\Risk Control Index\”+YR+MON+”\”+”currencyrate.dbf”
INDEX ON cntcurr TAG yyy
SET ORDER TO yyy
SELE B
USE “&OPATH”+”P13M.dbf”
SCAN
STORE chdrnum+crtable+t_index TO key1
SELE A
DO CASE
CASE SEEK(“&key1”)
SELE B
REPLACE AUM WITH A. av
OTHERWISE
SELE B
REPLACE AUM WITH 0
ENDCASEENDSCAN
SCAN
STORE cntcurr TO key1
SELE D
DO CASE
CASE SEEK(“&key1”)
SELE B
REPLACE AUM WITH D. rateAUM REPLACE ACC_PREM WITH D. rateACC_PREM
OTHERWISE
SELE B
REPLACE AUM WITH 0
REPLACE ACC_PREM WITH 0
ENDCASEENDSCAN
*==For Pre 13M 分子(結束)===
*==For Pre 13M 註記(開始)===
Scan
DO CASE
CASE INSTBCHNL=”TM”
REPLACE LINE WITH “TM”
CASE INSTBCHNL=”IS”
REPLACE LINE WITH “IS”
CASE INSTBCHNL=”BK”
REPLACE LINE WITH LEFT(CAMPAIGN,2)
CASE INSTBCHNL=”BR”
REPLACE LINE WITH LEFT(CAMPAIGN,6)
OTHERWISE
REPLACE LINE WITH “NA”
ENDCASE
ENDSCAN
SELECT line, SUM(AUM) as AUM, SUM(acc_prem) as acc_prem FROM P13M GROUP BY 1 INTO TABLE “&OPATH”+”P13M_F.dbf”
COPY TO “&OPATH”+”P13M_F.xls” xl5
SELECT crtable, SUM(AUM) as AUM, SUM(acc_prem) as acc_prem FROM P13M GROUP BY 1 INTO TABLE “&OPATH”+”P13M_by product.dbf”
COPY TO “&OPATH”+”P13M_by product.xls” xl5
SELECT line, crtable, SUM(AUM) as AUM, SUM(acc_prem) as acc_prem FROM P13M GROUP BY 1,2 INTO TABLE “&OPATH”+”P13M_by CPPM.dbf”
COPY TO “&OPATH”+”P13M_by CPPM.xls” xl5
*==For Pre 13M 註記(結束)===
*==For Pre 25M 分母(開始)===
SELECT CHDRNUM, CRTABLE, LEFT(CRTABLE,3) AS PRODUCT, INT(HISSDATE/100) AS ISSUETIME, STSECT, STFUND, STATCODE, INSTBCHNL, CAMPAIGN, CNTCURR, PREMIUMB01, PREMIUMC, t_index;
FROM OPATH+”A01M_ACC_”+YR+MON+”.dbf” WHERE (INT(HISSDATE/100)>=VAL(P25MS) AND INT(HISSDATE/100)<=VAL(P25ME)) INTO TABLE “&OPATH”+”BB.dbf” *FROM OPATH+”A01M_ACC_”+YR+MON+”.dbf” WHERE (INT(HISSDATE/100)>=VAL(P25MS) AND INT(HISSDATE/100)<=VAL(P25ME)) AND INT(HISSDATE/100)+200=VAL(t_index) INTO TABLE “&OPATH”+”BB.dbf”
SCAN
DO CASE
CASE LEFT(crtable, 2)=”XV”
replace crtable WITH “V”+STR(crtable,3,1)+”S1”
CASE LEFT(crtable, 1)=”X” AND (RIGHT(crtable,2)=”N1″ OR RIGHT(crtable,2)=”A1″)
replace crtable WITH “L”+ RIGHT(crtable, 3)
OTHERWISE
replace crtable WITH crtable
ENDCASE
ENDSCAN
SELECT chdrnum, crtable, product, issuetime, stsect, stfund, statcode, instbchnl, campaign, cntcurr, ” ” as Line, SUM(premiumb01+premiumc) as ACC_Prem, t_index, VAL(” “) as AUM FROM bb GROUP BY 1,2,3,4,5,6,7,8,9,10,11,13 INTO TABLE “&OPATH”+”P25M.dbf”
*==For Pre 25M 分母(結束)===
*==For Pre 25M 分子(開始)===
CLOSE TABLES
SELE A
USE “&OPATH”+”AUM.dbf”
INDEX ON chdrnum+crtable+t_index TAG kkk
SET ORDER TO kkk
SELE D
USE “D:\企業精算部\01 – AUM and CF information\Risk Control Index\”+YR+MON+”\”+”currencyrate.dbf”
INDEX ON cntcurr TAG yyy
SET ORDER TO yyy
SELE B
USE “&OPATH”+”P25M.dbf”
SCAN
STORE chdrnum+crtable+t_index TO key1
SELE A
DO CASE
CASE SEEK(“&key1”)
SELE B
REPLACE AUM WITH A. av
OTHERWISE
SELE B
REPLACE AUM WITH 0
ENDCASEENDSCAN
SCAN
STORE cntcurr TO key1
SELE D
DO CASE
CASE SEEK(“&key1”)
SELE B
REPLACE AUM WITH D. rateAUM REPLACE ACC_PREM WITH D. rateACC_PREM
OTHERWISE
SELE B
REPLACE AUM WITH 0
REPLACE ACC_PREM WITH 0
ENDCASEENDSCAN
*==For Pre 25M 分子(結束)===
*==For Pre 13M 註記(開始)===
Scan
DO CASE
CASE INSTBCHNL=”TM”
REPLACE LINE WITH “TM”
CASE INSTBCHNL=”IS”
REPLACE LINE WITH “IS”
CASE INSTBCHNL=”BK”
REPLACE LINE WITH LEFT(CAMPAIGN,2)
CASE INSTBCHNL=”BR”
REPLACE LINE WITH LEFT(CAMPAIGN,6)
OTHERWISE
REPLACE LINE WITH “NA”
ENDCASE
ENDSCAN
SELECT line, SUM(AUM) as AUM, SUM(acc_prem) as acc_prem FROM P25M GROUP BY 1 INTO TABLE “&OPATH”+”P25M_F.dbf”
COPY TO “&OPATH”+”P25M_F.xls” xl5
SELECT crtable, SUM(AUM) as AUM, SUM(acc_prem) as acc_prem FROM P25M GROUP BY 1 INTO TABLE “&OPATH”+”P25M_by product.dbf”
COPY TO “&OPATH”+”P25M_by product.xls” xl5
SELECT line, crtable, SUM(AUM) as AUM, SUM(acc_prem) as acc_prem FROM P25M GROUP BY 1,2 INTO TABLE “&OPATH”+”P25M_by CPPM.dbf”
COPY TO “&OPATH”+”P25M_by CPPM.xls” xl5
*==For Pre 13M 註記(結束)===
! SELECT CHDRNUM, CRTABLE, LEFT(CRTABLE,3) AS PRODUCT, INT(HISSDTE/100) AS ISSUETIME, STSECT, STFUND, STATCODE, INSTBCHNL, CAMPAIGN, CNTCURR, PREMIUMB01, PREMIUMC FROM A01M WHERE;
! (STSECT=”AL” OR STSECT=”LL”) INTO TABLE OPATH+”CC.dbf”
! SCAN
! DO CASE
! CASE LEFT(CRTABLE,2)=”XV”
! REPLACE CRTABLE WITH “V”+SUBSTR(CRTABLE, 3,1)+”S1”
! REPLACE PRODUCT WITH LEFT(CRTABLE,3)
! REPLACE STFUND WITH “M”
! CASE LEFT(CRTABLE,1)=”X” AND (RIGHT(CRTABLE,2)=”N1″ OR RIGHT(CRTABLE,2)=”A1″)
! REPLACE CRTABLE WITH “L”+RIGHT(CRTABLE,3)
! REPLACE PRODUCT WITH LEFT(CRTABLE,3)
! REPLACE STFUND WITH “M”
! ENDCASE
! ENDSCAN
! SELECT CHDRNUM, CRTABLE, PRODUCT, ISSUETIME, STSECT, STFUND, STATCODE, INSTBCHNL, CAMPAIGN, CNTCURR, SUM(PREMIUMB01) AS PREM1, SUM(PREMIUMC) AS PREMC, VAL(” “) AS AUM, VAL(” “) AS P1NTD, VAL(” “) AS PCNTD, VAL(” “) AS AUMNTD, ” ” AS LINE FROM CC ;
! GROUP BY 1,2,3,4,5,6,7,8,9,10,13,14 INTO TABLE OPATH+”DD.dbf”
! SCAN && 將各險種給予分類註記
! DO CASE
! CASE INSTBCHNL=”TM”
! REPLACE LINE WITH “TM”
! CASE INSTBCHNL=”IS”
! REPLACE LINE WITH “IS”
! CASE INSTBCHNL=”BK”
! REPLACE LINE WITH LEFT(CAMPAIGN,2)
! CASE INSTBCHNL=”BR”
! REPLACE LINE WITH LEFT(CAMPAIGN,6)
! OTHERWISE
! REPLACE LINE WITH “NA”
! ENDCASE
! ENDSCAN
! SELECT CHDRNUM, SUM(AV) AS AUM FROM F1819 GROUP BY 1 INTO TABLE OPATH+”EE.dbf”
! CLOSE TABLES
! SELE A
! USE OPATH+”EE.dbf”
! INDEX ON CHDRNUM TAG KKK
! SET ORDER TO KKK
! SELE C
! USE OPATH+”currencyrate.DBF”
! INDEX ON CNTCURR TAG YYY
! SET ORDER TO YYY
! SELE B
! USE OPATH+”DD.dbf”
! SCAN
! STORE CHDRNUM TO KEY1 && 註記原幣帳戶價值
! SELE A
! DO CASE
! CASE SEEK(“&key1”)
! SELE B
! REPLACE AUM WITH A. AUM
! OTHERWISE
! SELE B
! REPLACE AUM WITH 0
! ENDCASE
! STORE CNTCURR TO KEY2 && 將保費、帳戶價值轉成台幣
! SELE C
! DO CASE
! CASE SEEK(“&key2”)
! SELE B
! REPLACE AUMNTD WITH AUM * C. RATE
! REPLACE P1NTD WITH PREM1 * C. RATE
! REPLACE PCNTD WITH PREMC * C. RATE
! OTHERWISE
! SELE B
! REPLACE AUMNTD WITH 0
! REPLACE P1NTD WITH 0
! REPLACE PCNTD WITH 0
! ENDCASE
! ENDSCAN
! SELECT LINE, INT(ISSUETIME/100) AS ISSUEYEAR, SUM(AUMNTD) AS AUM1, SUM(P1NTD+PCNTD) AS YTDP FROM DD GROUP BY 1,2 INTO TABLE OPATH+”AUM_by_effectyear_”+CYM
! COPY TO OPATH+”AUM_by_effectyear_”+CYM+”.xls” XL5
! SELECT LINE, CRTABLE, SUM(AUMNTD) AS AUM1, SUM(P1NTD+PCNTD) AS YTDP FROM DD GROUP BY 1,2 INTO TABLE OPATH+”AUM_by_plannam_”+CYM
! COPY TO OPATH+”AUM_by_plannam_”+CYM+”.xls” XL5
! ENDIF