- 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)
VYR=SPACE(6)
WAY=SPACE(1)
@ 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 ” Input Valuaton Strat (EX.201501) : ” FONT “Arial”,14 COLOR BG/W*
@ 17, 63 GET VYR FONT “Arial”,12 COLOR B+/W*
@ 19, 07 SAY ” Input way (EX.1) : ” FONT “Arial”,14 COLOR BG/W*
@ 19, 42 GET WAY FONT “Arial”,12 COLOR B+/W* &&way=1; 契徹率，way=2;投資型占率，way=3;全部
READ
CYM=STR((VAL(YR)-1911)*100+VAL(MON),5)
?CYM
OPATH=”D:\企業精算部\01 – AUM and CF information\Risk Control Index\”+YR+MON+”\”
SELECT 0
USE “D:\foxpro data\A01M\A01M_”+CYM+”.DBF” ALIAS A01M
SELECT 0
USE “D:\foxpro data\F1819\F1819_”+CYM+”.DBF” ALIAS F1819
*=========== 計算契徹率 (開始)
IF WAY=”1″ OR WAY=”3″ THEN
SELECT CHDRNUM, CRTABLE, anbccd, LEFT(CRTABLE,3) AS PRODUCT, INT(HISSDATE/100) AS ISSUETIME, STSECT, STFUND, STATCODE, INSTBCHNL, CAMPAIGN, " " AS LINE, 0 AS CFN FROM A01M WHERE;
STFUND="M" AND !(CRTABLE="X" OR (CRTABLE="RP" AND RIGHT(CRTABLE,1)="2")) AND INT(HISSDATE/100)>=VAL(VYR) INTO TABLE OPATH+"AA.dbf"
SCAN && 將各險種給予分類註記
DO CASE
CASE CRTABLE="G"
REPLACE LINE WITH "NA"
CASE (STSECT="AL" OR STSECT="LL") AND LEFT(CAMPAIGN,2)="KA"
REPLACE LINE WITH "FB_ILP"
CASE (STSECT="AL" OR STSECT="LL") AND INSTBCHNL="BK" AND LEFT(CAMPAIGN,2)<>"KA"
REPLACE LINE WITH "OBK_ILP"
CASE (STSECT="AL" OR STSECT="LL") AND INSTBCHNL="BR"
REPLACE LINE WITH "BR_ILP"
CASE INSTBCHNL="TM" AND !(STSECT="AL" OR STSECT="LL")
REPLACE LINE WITH "TM"
CASE INSTBCHNL="IS" AND !(STSECT="AL" OR STSECT="LL")
REPLACE LINE WITH "IS"
CASE LEFT(CAMPAIGN,2)="KA" AND INLIST(PRODUCT, "MT", "TS", "TL", "TE", "TF", "TG", "TH", "TK", "TM", "TN", "TO", "TP", "TQ", "TR", "TU", "TV", "TW", "TX") AND !(STSECT="AL" OR STSECT="LL") AND ! INLIST(PRODUCT, "TLJ")
REPLACE LINE WITH "FB_Mortgage"
CASE LEFT(CAMPAIGN,2)="KA" AND INLIST(PRODUCT, "UL", "IS", "FU" ) AND !(STSECT="AL" OR STSECT="LL")
REPLACE LINE WITH "NA"
CASE LEFT(CAMPAIGN,2)="KA" AND !(STSECT="AL" OR STSECT="LL")
REPLACE LINE WITH "FB_Insurance"
OTHERWISE
REPLACE LINE WITH "NA"
ENDCASE
ENDSCAN
REPLACE CFN WITH 1 ALL FOR STATCODE="CF" &&給予契徹件註記
COPY TO OPATH+"CFRATE_wk"+YR+MON+".xls" XL5
SELECT LINE, SUM(CFN), COUNT(LINE) FROM AA GROUP BY 1 INTO TABLE OPATH+"CFRATE"+CYM
COPY TO OPATH+"CFRATE"+YR+MON+".xls" XL5ENDIF
*=========== 計算契徹率(結束)
IF WAY=”2″ OR WAY=”3″ THEN
SELECT CHDRNUM, CRTABLE, LEFT(CRTABLE,3) AS PRODUCT, INT(HISSDATE/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, COUNT(CHDRNUM) AS COUNTA, 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" XL5ENDIF