02.upr\A0_upr43.sql 112/02/28 決算日 02.upr\A1_upr_arco_a.sql 02.upr\A2_upr_inf_p.sql 02.upr\A3_upr_inf_pa_shr.sql 02.upr\B_upr_gico_new.sql 02.upr\B1_upr_ginw.sql 02.upr\B1-1_ginw_upr_v1.sql 02.upr\C_vupr.sql 02.upr\C_vupr_temp.sql 02.upr\check_gta_upr.sql 02.upr\Currency.sql 02.upr\upr_all.sql 02.upr\comp\01.AED_KED 02.upr\comp\02.JPA_JPAT_AHAD 02.upr\comp\03.PWPR_new.sql 02.upr\comp\04.SDL_new.sql 02.upr\comp\05.SPTL_SFDL_SHDL_JYL_CATL.sql 02.upr\comp\06.KAD_new.sql 02.upr\comp\07.SPA_SFR_SEHI.sql 02.upr\comp\08.SFDR_new.sql 02.upr\comp\output_comp.sql 02.upr\comp\upr_com.sql 00.事前準備\check 01.首年度暫停\1. 短年期附約費率 01.首年度暫停\2. check_arco_a 03.dist\dist_upr_life_n 03.dist\dist_upr_n 03.dist\dist_upr_分區 04.low_clm\01.L_rsv_low_clm_new 04.low_clm\02.dist_rsv_low create temp table cc 112/01/31 上月底 create temp table cc(value_date char(9)); "select plan_code, rate_scale," "select (case when plan_code='ADD0081' then '1' else '0' end) ind," create temp table dd (value_date char(9)); create temp table ee (value_date char(9)); create temp table ee (value_date char(9)); select * from vupr delete from vupr_all --確認應提存的保單 unload to 'currency.txt' create temp table cc(value_date char(9)); --drop table dd; create temp table dd --op table dd; --op table dd; create temp table dd create temp table dd create temp table dd create temp table dd create temp table dd ( create temp table cc(value_date char(9)); create temp table dd drop table if exists k1; "select value_date, policy_no, coverage_no, plan_abbr_code, " select * from acjd create temp table r1 create temp table cc --create temp table wa (value_date char(9)); --select * from RSVL; (value_date char(9)); 112/01/01 上月初 insert into cc values('112/02/28'); --update "count(distinct rate_age) rate_age," "b.insurance_type,b.plan_abbr_code," insert into dd values('112/02/28'); --update insert into ee values('112/02/28'); insert into ee values('112/02/28'); into temp vupr_r; where value_date = '112/02/28'; unload to 'GTA.txt' "select currency, insurance_type,plan_abbr_code," insert into cc values('112/02/28'); --update --較早期以一般短年期險種方式提存 "(value_date char(9), last_value_date char(9));" create temp table dd "--依據本險投保規則,不得辦理減額繳清&展期定期" "(value_date char(9), last_value_date char(9));" "(value_date char(9), last_value_date char(9));" "(value_date char(9), last_value_date char(9));" "(value_date char(9), last_value_date char(9));" "value_date char(9)," insert into cc values('112/02/28'); --update (value_date char(9)); drop table if exists a1; "plan_code, rate_scale, agp, paid_to_date, upr, " where acct_no in (value_date char(9)) (value_date char(9)) truncate table wa; "select a.area_code,plan_abbr_code[1,3] ," insert into cc 111/02/28 去年同期決算日 "count(distinct rate_dur) rate_dur,---全都是0" "count(b.coverage_no) cnt, sum(b.agp) sum_agp" update vupr_r --當月COI有入帳的保單 "select policy_no,gta_seq,po_issue_date,expired_date from gtcl" count() from arkt create temp table dd insert into dd "(value_date char(9), last_value_date char(9));" create temp table dd insert into dd insert into dd insert into dd insert into dd last_value_date char(9)); drop table if exists a2; "datediff_k(paid_to_date, value_date) day" "('45060108','45060208')--投資型" ; ; insert into wa values('112/02/28'); sum(b.rsv_low_clm) low values('112/02/28') ---update; 112/02 決算月 select a. "count(distinct rate_sex) rate_sex," "from plan_category a,arkt b" delete from gico_upr delete from ginw_upr delete from ginw_upr_v1 "set rsv_upr=round(rsv_upr,2)" "select policy_no, currency, sum(-amount) coi" where "where insurance_type in ('V','U')" delete from upr_all "(value_date char(9)," "values('112/02/28','111/02/28'); ---update this month and the same month of last year" insert into dd "(value_date char(9), last_value_date char(9));" "values('112/02/28','111/02/28'); ---update this month and the same month of last year" "values('112/02/28','111/02/28'); ---update this month and the same month of last year" "values('112/02/28','111/02/28'); ---update this month and the same month of last year" "values('112/02/28','111/02/28'); ---update this month and the same month of last year" delete from upr_com insert into dd drop table if exists a3; from arco_a into temp s1; insert into r1 values insert into cc values create temp table qq (pp_date char(9)); "from dist_code_n a,rsvl b" ; 111/02 去年同月 "from arco_a a, cc b" "count(distinct rate_medi) rate_medi," where a.plan_abbr_code=b.plan_abbr_code where value_date in (select value_date from dd); where value_date in (select value_date from ee); where value_date in (select value_date from ee); where currency<>'TWD'; from acjd "expired_date[1,6]>'112/02'" and plan_abbr_code!='LVL' where value_date in (select value_date from cc); last_value_date char(9)); "values('112/02/28','111/02/28'); ---update this month and the same month of last year" insert into dd "insert into dd values('112/02/28','111/02/28');" where value_date in (select value_date from cc); values('112/01/31'); --update 改成上個月底 drop table if exists a4; into temp a1; ('112/02/28') ('112/02/28') insert into qq values('112/01/01'); where a.dept_belong=b.area_code delete from upr43 11202 決算年月(無/) "where insurance_type in ('D','H','P','Z')" "count(distinct rate_occu) rate_occu," "and a.category in ('PA','PAR','SHR','SH')" update vupr_r "where acct_no in ('45060108','45060208','45060163','45060263')" "and expired_date[5,9]<>'03/01'" "group by 1,2,3" ------------------------------------------------------------ insert into dd delete from JPA "values('112/02/28','111/02/28'); ---update this month and the same month of last year" delete from SPTL delete from KAD delete from SPA delete from SFDR ------------------------------------ drop table if exists a5; "select a.,b.dept_belong" ; ; "--inforce add NB(AMRR,HSR) for 60 days to pay" "group by 1,2" where value_date in (select value_date from cc) 03/01 下月初(月日) and a.value_date=b.value_date and b.plan_abbr_code <> ('SPA') "select c.value_date," select distinct policy_no from gico select distinct policy_no from gico "set rsv_upr=round(rsv_upr,0)" and plan_code not in ( and po_issue_date<='112/02/28' "order by 1,2,3" --1.傳統型附約 "values('112/02/28','111/02/28'); ---update this month and the same month of last year" where value_date in (select value_date from dd); delete from pwpr_v1 where value_date in (select value_date from dd); where value_date in (select value_date from dd); where value_date in (select value_date from dd); where value_date in (select value_date from dd); "select plan_abbr_code," insert into upr_com --1.確認豁免保單(短年期->長年期) drop table if exists f1; "select a., b.currency" "From s1 a,dept b" --op table echo_pldf; select a.* "order by 2,1" ; 02/28 決算日(月日) and paid_to_date>b.value_date count(distinct rate_unit) rate_unit-----全都是0 and b.policy_no||coverage_no not in (select policy_no||coverage_no from rsvi) "a.policy_no,a.client_id,a.effect_date,a.co_sts_code,a.co_sts_date," "where plan_code in ('KGI01','KGI02','KGI03')" "where plan_code in ('KGI01','KGI02','KGI03')" where currency='TWD'; select plan_code from pldf and process_date<='112/02/28' insert into upr_all delete from JPAT where value_date in (select value_date from dd); delete from SFDL ---------------------------------------------------------- delete from SFR "(case when po_issue_date>b.last_value_date then 1 else 2 end) RY_RY," "select value_date,policy_no,co_issue_date,dur,plan_code,rate_scale," "select policy_no, plan_abbr_code, " "from a1 a, pldf b" where a.dept_code=b.dept_code --op table echo_arap_1; "from upr_all a, cc b" "select c.value_date,a.,paid_to_date,po_sts_code,d.pp_date" 01/01 上月初(月日) and co_issue_date<=b.value_date from pra2 "group by 1,2,3" "a.plan_code,a.rate_scale,a.face_amt,a.co_mode_prem,b.modx," into temp gh_kgi; into temp gh_kgi; where plan_abbr_code = 'LVL') "group by 1,2,3,4" "select a.value_date,a.plan_code,a.rate_scale,a.plan_abbr_code," delete from AED_KED where value_date in (select value_date from dd); delete from SDL where value_date in (select value_date from dd); "--KAD無保價金,故無減額繳清及展期保險" where value_date in (select value_date from dd); --減額繳清件需提存UPR，但責任準備因子不同於一般件 "count() cnt, sum(round(agp,0)) agp, sum(upr) upr" "plan_abbr_code,rate_age,rate_sex,rate_occu,'0',face_amt,modx,paid_to_date," "po_sts_code, co_sts_code" delete from upr_fycheck where a.plan_code = b.plan_code into temp s2; --op table echo_arap_2; where a.plan_code not in (select plan_code from gipd group by 1) "from vltp a,outer polf b,wa c,qq d" truncate table upr43_cnt; 3/01 and plan_abbr_code!='GPA' "group by 1,2" "order by 1,2,3" "b.mode_prem,a.paid_to_date ptd_co,b.paid_to_date ptd_po," unload to 'vupr.txt' "group by 1,2" "order by 1,2;" "dur, area_code,count(),sum(agp) agp_upr,sum(upr) upr, d.currency" where value_date in (select value_date from dd); delete from AHAD "select a., b.unit_value,b.rate_plan_code,b.rate_rate_scale," where value_date in (select value_date from dd); delete from SHDL select * delete from SEHI "select a.policy_no from arco a, arpo b" "from AED_KED a,dd b" "unit_value,rate_plan_code,rate_rate_scale,vl_plan_code,vl_rate_scale," from rsvm_adj_rsvi where value_date in ('112/02/28') and a.rate_scale = b.rate_scale --op table echo_arap; and a.value_date = b.value_date "--where plan_abbr_code in ('AMRR','HSR','AMR') --> vltp 只有這幾個險種" "select policy_no,coverage_no,plan_code,rate_scale,insurance_type," 1/01 and a.policy_no||a.coverage_no not in into temp ind_rate; into temp a1; (case when b.modx!='0' then round(a.co_mode_prem12/b.modx) insert into gh_kgi insert into gh_kgi "select insurance_type,plan_abbr_code," "order by 2,1" ",'N', d.insurance_type, co_issue_date[1,6]" where value_date in (select value_date from dd); " b.vl_plan_code,b.vl_rate_scale" where value_date in (select value_date from dd); from arkt where value_date in (select value_date from dd); where a.policy_no=b.policy_no where a.value_date=b.value_date "agp_p,day1,day2,t0,t1,agp_p_modx,upr,inactive_ind,inact_age," "order by 1,2;" and 1 = 1; into temp a2; "select a.,b.area_code area" and a.plan_abbr_code <>'GPA' where a.policy_no=b.policy_no "plan_abbr_code,prem_year,premium_year_age,premium_year_dur," 2023/02/28 (select policy_no||coverage_no from rsvi) " else a.co_mode_prem end) agp," select policy_no from gp1; select policy_no from gp1; "case when dur='1' then 'FY' else 'RY' end dur,currency," into temp a1; create temp table cc "from arco_a a, cc b, arpo c, pldf d" "select a., b.unit_value,b.rate_plan_code,b.rate_rate_scale" "from arkt a, pldf b" delete from JYL "where plan_abbr_code in ('KAD','JAD')" delete from NSEHI and a.coverage_no=1 "group by 1,2" inact_occu_dur "from s2 a,dist_code_n b" "select currency,insurance_type,plan_abbr_code" into temp a1 into temp jo1; "co_issue_date,rate_age,rate_sex,face_amt,agp,client_id,client_ag," 112/2/28 決算日 into temp s1; "select a., b.unit_value,b.rate_plan_code,b.rate_rate_scale" insert into a1 ( "count() cnt,sum(rsv_upr) upr" "(value_date char(9), last_value_date char(9))" "where a.insurance_type in ('D','H','P','Z')" "from arkt a, pldf b" --減額繳清件需提存UPR，但責任準備因子不同於一般件 where a.plan_code=b.plan_code "select policy_no,coverage_no,plan_code,rate_scale," where value_date in (select value_date from dd); into temp a1; where value_date in (select value_date from dd); and a.plan_abbr_code in ('SFDR') into temp yt1; from pwpr_v1 "select plan_abbr_code, count()" "select a., b.modx_r, c.currency" "select , case when currency = 'USD'" where a.dept_belong[2]=b.dept_belong from pldf ; "dept_belong,modx,medical_flag,method,ext_ind,sts_ind,currency,value_rate" upr_all團險放大比例年更 "from arkt a, pldf b" "select (case when plan_code='ADD0081' then '1' else '0' end) ind," case when b.modx!='0' then "select a.policy_no,a.ginw_type,a.effect_date,b.renew_date,b.expired_date," "select a.policy_no, a.gipn_code, a.gipn_vers," from vupr_r select a. ; and a.value_date=b.value_date where a.plan_code=b.plan_code "select a.policy_no from arco a, arpo b" and a.rate_scale=b.rate_scale plan_abbr_code from arkt delete from CATL and (po_sts_code='46' or co_sts_code='46') where value_date in ( select value_date from cc) from rsvi "from arco_a a,polf b,colf c" " then round(day/365agp,2)" into temp s3; where plan_term_ind='A' --A：一年期 update a1 "select a.,case when paid_to_date[5,9]<=co_issue_date[5,9]" from arkt 112 當年 select * from upr43 where value_date in (select * from cc) where a.plan_code=b.plan_code "b.insurance_type,b.plan_abbr_code," " round(((datediff_k(c.value_date,b.paid_to_date))/365)" " a.mode_prem mode_prem_nw," " a.effect_date,b.renew_date,b.expired_date," "where insurance_type in ('V','U')" "from a1 a, polf b" insert into cc values and a.paid_to_date>b.value_date and a.rate_scale=b.rate_scale where a.policy_no=b.policy_no and a.plan_abbr_code in ('PWPR') where plan_abbr_code='SDL' where value_date in (select value_date from dd); "select a.policy_no, a.coverage_no, a.plan_abbr_code," --減額繳清件需提存UPR，但責任準備因子不同於一般件 into temp rpu1; insert into yt1 ; where plan_abbr_code like '%WPR' where dur=1 " else round(day/365agp,0) end upr_ck" and insurance_type !='Z' --WADD set area_code='B' " then paid_to_date[1,3]-co_issue_date[1,3]" where plan_abbr_code in into temp s2; and a.rate_scale=b.rate_scale "count(b.coverage_no) cnt, sum(b.agp) sum_agp" a.co_mode_prem12/b.modx) " b.modx,b.po_issue_date," " a.ginw_type, a.mode_prem," and rsv_upr>0 where a.policy_no = b.policy_no "('112/02/28','111/02/28')" and co_issue_date<=b.value_date "and a.plan_abbr_code in ('AED','KED')" and a.coverage_no=1 into temp j1; into temp lu1; delete from CYDL "b.cl_item, c.cl_item_desc," "select a.policy_no from arco a, arpo b" "select plan_abbr_code," insert into upr_com group by 1 and a.policy_no=c.policy_no from a2 "select case when acct_no[5,6]='01' then 'FY'" "group by 1,2,3" where plan_abbr_code in (select plan_abbr_code from pldf " else paid_to_date[1,3]-co_issue_date[1,3] 1 end as ptd_dur" (select plan_abbr_code from pldf and a.plan_abbr_code in ( "from plan_category a,arkt b" " else round(((datediff_k(c.value_date,b.paid_to_date))/365)" " a.ginw_type_cnt,a.paid_to_date ptd_nw," " b.modx,b.po_issue_date," and plan_abbr_code!='LVL' --> SP COI一次扣完(算責任準備) and b.po_sts_code between '42' and '59' ; and a.plan_abbr_code!='GPA' into temp iii; "and a.plan_abbr_code in ('JPA','JSA','JPAT','AHAD')" ---暫時先修改 where value_date in (select value_date from dd); (case when cl_deform_perc = '100' then '1' where a.policy_no=b.policy_no --展期定期件不需提存UPR "(case when dur=1 then 1 else 2 end)," "select value_date,policy_no,co_issue_date,dur,plan_code,rate_scale," order by 1; and a.coverage_no=c.coverage_no into temp a3; " else 'RY' end dur," into temp echo_pldf where agent_line_code='B') from jo1 a " where plan_term_ind=""A"")---短年期險種寫法 update_1070504" delete from s1 select plan_abbr_code where a.plan_abbr_code=b.plan_abbr_code a.co_mode_prem) end " a.ginw_sts_code,a.ginw_sts_date," " a.ginw_type_cnt,a.paid_to_date ptd_nw," and po_sts_code between '42' and '59' into temp a2; unload to 'gta_upr_11202.txt' and a.policy_no=c.policy_no and (po_sts_code='46' or co_sts_code='46') --先從rsvm讀取base_date重新計算dur_Rita "select a.,client_id from lu1 a,pocl b" when cl_deform_perc = '90' then '2' and a.coverage_no=1 "select a.policy_no from arco a, arpo b" "count(),sum(round(agp_p_modx,0)),sum(upr)" "plan_abbr_code,rate_age,rate_sex,rate_occu,'0',face_amt,modx,paid_to_date," ------------------------------------ and a.policy_no in (select policy_no from polf from s3 ; ; into temp jo2; into temp ar1; where policy_no||coverage_no in ( from pldf and a.plan_abbr_code = 'GOSTR' ) upr " b.po_sts_code,b.po_sts_date" " a.ginw_sts_code,a.ginw_sts_date," "group by 1,2,3,4" "select policy_no,sequence,plan_code,sum(receive_prem),sum(upr)" and a.plan_code=d.plan_code "select c.value_date,a.policy_no, a.plan_code, a.rate_scale, plan_abbr_code," into temp rpu1; "select c.value_date,a.policy_no,a.co_issue_date," where a.policy_no=b.policy_no --展期定期件不需提存UPR when cl_deform_perc = '80' then '3' "and a.plan_abbr_code in ('SPA','SFR','SEHI','NSEHI')" where a.policy_no=b.policy_no "from JPA a,dd b" "unit_value,rate_plan_code,rate_rate_scale,vl_plan_code,vl_rate_scale," --2.確認有無新險種 where fy_billing_ind ='0' select count() into temp s4; "insert into echo_pldf values('TWD','G','GTA');" update a1 select policy_no||coverage_no from s2); where rate_plan_code||rate_scale in ( and b.policy_no||coverage_no not in (select policy_no||coverage_no from rsvi) " from gico a,gipo b ,dd c" "from ginw a,gipo b" " b.po_sts_code,b.po_sts_date, a.occupation" "order by 1 desc,3,2,4" --當月需提upr的保單 from taupr and a.rate_scale=d.rate_scale " a.rate_age, a.rate_sex, a.face_amt, a.modx," case and client_ident='I1' "select a.policy_no from arco a, arpo b" when cl_deform_perc = '70' then '4' and (po_sts_code='46' or co_sts_code='46') and a.coverage_no=1 where a.value_date=b.value_date "agp_p,day1,day2,t0,t1,agp_p_modx,upr,'N',' ',''" select distinct a.plan_abbr_code and modx <>12) from a3 "insert into echo_pldf values('TWD','G','GOHS');" set area_code='T' update jo2 delete from ar1 select plan_code||rate_scale from ind_rate))---10703新增組合型條件 "group by 1,2,3" where a.policy_no=b.policy_no where a.policy_no=b.policy_no "from ginw a,gipo b,ee c" ; select * from vupr INNER JOIN cc ON taupr.value_date = cc.value_date and a.policy_no||a.coverage_no not in (select policy_no||coverage_no from rsvi) " b.po_issue_date, b.paid_to_date, unit_value,a.rate_plan_code," --展期定期件不需提存UPR " when cast(d.co_issue_date[5,6] as int) >cast(base_date[5,6] as int) or" into temp lu2; where a.policy_no=b.policy_no when cl_deform_perc = '60' then '5' into temp rpu1; and a.plan_abbr_code in ('SFDR') "group by 1,2;" from JPAT "from upr_all a, dd b" and a.co_issue_date = b.po_issue_date where upr <> upr_ck; unload to 'dist_upr_IN.txt' "insert into echo_pldf values('TWD','G','GTL');" where plan_abbr_code in (select plan_abbr_code from pldf set ptd_dur=1 "where plan_abbr_code in ('JWPR','SWPR','OWPR','ZWPR')" "select dur,insurance_type,plan_abbr_code,plan_code,rate_scale," into temp lu1; "order by 1,2,3;" and a.policy_no not in (select policy_no from gp1) and b.policy_no not in (select policy_no from gh_kgi) where a.policy_no=b.policy_no into temp vupr_r; where policy_no[1]='R' and a.policy_no||a.coverage_no not in ( a.rate_rate_scale "select a.policy_no from arco a, arpo b" " (cast(d.co_issue_date[5,6] as int) =cast(base_date[5,6] as int) and" and a.coverage_no=1 when cl_deform_perc = '50' then '6' and (po_sts_code='45' or co_sts_code='45') where value_date in ( select value_date from cc) where a.value_date = b.value_date and a.policy_no = b.policy_no "select currency,dur,area,sum(amount-1)" "insert into echo_pldf values('TWD','G','GH');" where agent_line_code='T') where ptd_dur=0 and co_issue_date>=pp_date and policy_no||coverage_no in (select policy_no||coverage_no from arco_vul "count() cnt,sum(agp) agp_upr,sum(upr) upr" and b.po_sts_code between '42' and '59' and a.effect_date>=b.renew_date and b.policy_no not in (select policy_no from gh_kgi) update vupr_r "group by 1,2,3" select policy_no||coverage_no from upr43 where " from iii a, arpo b,dd c" where a.policy_no=b.policy_no " cast(d.co_issue_date[8,9] as int) >cast(base_date[8,9] as int) )" "select a.,occupation_code from lu2 a,clnt b" "and a.plan_abbr_code in ('SPTL','SFDL','SHDL','JYL','PWL','CATL','CYDL')" when cl_deform_perc = '40' then '7' --展期定期件不需提存UPR into temp del1; insert into yt1 ; into temp a1; into temp k1 "select upr, upr_ck, paid_to_date, agp, day" from s4 "insert into echo_pldf values('TWD','T','TA');" ; and co_issue_date<=value_date "where plan_abbr_code in ('JWPR','SWPR','OWPR','ZWPR')" from s1 unload to 'upr_inf_PA.txt' and a.co_sts_code between '42' and '59' and a.ginw_sts_code between '42' and '49' and a.effect_date>=b.renew_date "set rsv_upr=round(rsv_upr,2)" "order by 1,2,3" value_date in (select * from cc))--避免重複計提 where a.policy_no=b.policy_no and a.coverage_no=1 then where a.client_id=b.client_id and (po_sts_code='46' or co_sts_code='46') when cl_deform_perc = '30' then '8' "select a.policy_no from arco a, arpo b" "select plan_abbr_code," insert into upr_com ; from a3 "group by 1,2,3" "insert into echo_pldf values('TWD','H','OHS');" unload to 'upr_ind.txt' ; and co_sts_code = '43'); "group by 1,2,3,4,5" "select a.,b.rate_occu,b.rate_medi," select * from a1 and b.paid_to_date>=c.value_date and b.po_sts_code between '42' and '49' and a.paid_to_date > c.value_date where currency<>'TWD'; ; and a.policy_no not in (select policy_no from varco) --and b.paid_to_date>c.value_date --099/07 datediff_k會算出絕對值 要加此條件 "and a.plan_abbr_code in ('JPA','JSA','JPAT','AHAD')" " cast(base_date[1,3] as int)-cast(d.co_issue_date[1,3] as int)" into temp lu3; into temp rpu1; when cl_deform_perc = '20' then '9' where a.policy_no=b.policy_no --篩出所有需提存保單 "(case when dur=1 then 1 else 2 end)," "select value_date,policy_no,co_issue_date,dur,plan_code,rate_scale," --2.1 長年期傷害險(arkt中不一定有) where upr <> upr_ck "order by 1,2,3;" "select ta_ind, currency,insurance_type,plan_abbr_code,area_code," "order by 1,2,3,4,5" --為了適用統一公式新增dur，目前尚未有商品有此區分_1070606 "order by 1,2,3" and a.paid_to_date<=b.paid_to_date into temp aaa1 and a.ginw_sts_code between '42' and '49' update vupr_r unload to 'GTA_acjd_11202.txt' "group by 1,2,3,4,5,6,10,11,12,13" into temp ooo; and (po_sts_code='45' or co_sts_code='45') else when cl_deform_perc = '10' then '10' and a.coverage_no=1 "select a., b.unit_value,b.rate_plan_code,b.rate_rate_scale," "count(),sum(round(agp_p_modx,0)),sum(upr)" "plan_abbr_code,rate_age,rate_sex,rate_occu,'0',face_amt,modx,paid_to_date," select distinct plan_abbr_code "select a., b.rate_age, 0 as rate_dur," order by 3 update echo_pldf "sum(round(upr,2))" "select a.,case when ptd_dur<policy_dur" "insert into upr43_cnt values('ar1',(select count() from ar1));" into temp qqq; "b.dur rate_dur, '0' rate_insu, '0' rate_unit" and a.paid_to_date is not null ; and b.po_sts_code between '42' and '49' "set rsv_upr=round(rsv_upr,0)" "select tran_date[1,6],policy_no,plan_code,amount" "order by 1,2,3,4,5,6,10,11,12,13" into temp del1; " cast(base_date[1,3] as int)-cast(d.co_issue_date[1,3] as int) 1" "select a.,occupation_level from lu3 a,nboc b" "select a.policy_no from arco a, arpo b" when cl_deform_perc = '5' then '11' "and a.plan_abbr_code in ('SPA','SFR','SEHI','NSEHI')" " b.vl_plan_code,b.vl_rate_scale" "from pwpr_v1 a,dd b" "unit_value,rate_plan_code,rate_rate_scale,vl_plan_code,vl_rate_scale," from pldf "b.rate_sex, b.rate_medi, b.rate_occu, b.rate_insu" select * from acjd set insurance_type='G' from a1 " then ptd_dur else policy_dur end as dur_final," "from lu1 a,arco b" and a.paid_to_date!='' into temp s1; where currency='TWD'; from acjd ; "select a., prem_factorface_amt/unit_value agp," " end dur," where a.occupation_code=b.occupation_code where a.policy_no=b.policy_no end) d_grade and (po_sts_code='45' or co_sts_code='45') "from arkt a, pldf b" where a.value_date=b.value_date "agp_p,day1,day2,t0,t1,agp_p_modx,upr,'N',' ',''" where plan_code in ( "from k1 a, arco_vul b" where acct_no in where plan_abbr_code='GPA' "group by 1,2,3,4,5" case when ptd_dur<policy_dur then select * from arco_a update qqq where a.policy_no=b.policy_no into temp eee insert into ginw_upr where policy_no[1]='R' insert into upr_all case when paid_to_date>value_date then --篩出所有需提存保單 " a.plan_code, a.rate_scale, a.plan_abbr_code," into temp lu4; and a.coverage_no=1 "from a1 a, cldt b, clit c" into temp del1; where a.plan_code=b.plan_code "group by 1,2;" from AHAD select distinct plan_code where a.policy_no = b.policy_no "('45060163','45060263') --萬能" ; "order by 1,2,3,4,6" " (case when mod(ptd_dur,5)=0 then 0 else agp end)" where plan_abbr_code in set dur='2' and a.coverage_no=b.coverage_no ; "select c.value_date,a.," "select a., b.plan_code, b.rate_scale, b.face_amt, b.unit_value," "select policy_no, currency, rsv_upr" "order by 2,3" "select a.value_date,a.plan_code,a.rate_scale,a.plan_abbr_code," " datediff_k( paid_to_date, value_date)" "select a., b.unit_value,b.rate_plan_code,b.rate_rate_scale," " a.rate_age, a.rate_sex,d.rate_occu,a.face_amt, a.modx," "and a.plan_abbr_code in ('SPTL','SFDL','SHDL','JYL','PWL','CATL','CYDL')" where a.policy_no = b.policy_no and a.rate_scale=b.rate_scale where value_date in ( select value_date from cc) from pra2) and a.coverage_no = b.coverage_no and currency='TWD' --op table echo_arap_1; ; else agp end as agp_final (select plan_abbr_code from pldf where dur>='2'; into temp lu2; insert into eee (case when ginw_type='A' and "b.gi_rate_class_ptr pn_rate_class_ptr, b.rate_occu_ind" from vupr_r ; "dur, dept_belong[2],count(),sum(agp) agp_upr,sum(upr) upr, 'TWD','N'," when paid_to_date<=value_date then " b.vl_plan_code,b.vl_rate_scale" " b.paid_to_date, unit_value,a.rate_plan_code," "select a.,dur from lu4 a,rsvm b" and (po_sts_code='45' or co_sts_code='45') and b.cl_item = c.cl_item --篩出所有需提存保單 and a.plan_abbr_code in ('SFDR') insert into yt1 ; "and plan_abbr_code not in ('ACTL','AXDL','CLE') --已無有效契約" into temp a1 into temp ss1; "select a., " "select a.,dept_code" from jo2 a " where plan_term_ind=""A"")---短年期險種寫法 update_1070504" "select c.value_date," ptd_nw>c.value_date and modx!='0' then "from s1 a, gipn b" "where insurance_type in ('V','U')" drop table if exists a1; "f.insurance_type, a.co_issue_date[1,6]" 0 end days "from arkt a, pldf b" " a.rate_rate_scale,a.vl_plan_code,a.vl_rate_scale" where a.policy_no=b.policy_no into temp del1; and b.cl_item in ('614') "select a., b.unit_value,b.rate_plan_code,b.rate_rate_scale," and policy_no not in (select policy_no from del1) "select plan_abbr_code," insert into upr_com and plan_abbr_code not in ( ; " b.currency,'1' order_1," "from gico_upr a ,gipo b, cc c" into temp jo3; into temp ar2; unload to 'upr_arco_a.txt' --適用統一公式_1070606--Rita "a.policy_no,a.client_id,a.effect_date,a.co_sts_code,a.co_sts_date," round(mode_prem_nw(12/modx)ginw_type_cnt) where a.policy_no = b.policy_no and rsv_upr>0 drop table if exists a2; "from arco_a a,poag b,aaor c,dept d,cc e, pldf f" " from ooo a, outer pra2 b" where a.plan_code=b.plan_code "from j1 a, arpo b,dd c,arco d, rsvm e" and a.coverage_no=b.coverage_no "order by 1,2,3" " b.vl_plan_code,b.vl_rate_scale" into temp j1; "(case when dur=1 then 1 else 2 end)," "select value_date,policy_no,co_issue_date,dur,plan_code,rate_scale," select distinct plan_abbr_code from a1) "select a.,b.dept_belong" " c.dept_belong," where a.policy_no=b.policy_no "select a.dur,a.insurance_type,b.primary_rider_ind," update lu2 "a.plan_code,a.rate_scale,a.face_amt,a.co_mode_prem,b.modx," when ginw_type='A' and and a.gipn_code = b.gipn_code and plan_abbr_code!='LVL' --> SP COI一次扣完(算責任準備) where a.policy_no[1]='9' where a.rate_plan_code=b.plan_code and a.rate_scale=b.rate_scale where a.policy_no=b.policy_no into temp lu5; "select policy_no,coverage_no,plan_code,rate_scale," into temp a2; "from arkt a, pldf b" "count(),sum(round(agp_p_modx,0)),sum(upr)" "plan_abbr_code,rate_age,rate_sex,rate_occu,'0',face_amt,modx,paid_to_date," order by 1 "select a., b.rate_plan_code, b.rate_rate_scale, b.unit_value" "From ss1 a,dept b" b.insurance_type type and a.value_date = c.value_date "insert into upr43_cnt values('ar2',(select count() from ar2));" "b.plan_abbr_code,sum(a.cnt),sum(a.agp_upr),sum(a.upr)" set rate_dur=0 "b.mode_prem,a.paid_to_date ptd_co,b.paid_to_date ptd_po," ptd_nw>c.value_date and modx='0' then and a.gipn_vers = b.gipn_vers and po_sts_code between '42' and '59' unload to 'gtco_11202.txt' and a.policy_no = b.policy_no and a.rate_rate_scale=b.rate_scale "and a.plan_abbr_code in ('JPA','JSA','JPAT','AHAD')" and a.policy_no=d.policy_no plan_abbr_code from arkt where a.plan_code=b.plan_code "select c.value_date,a.policy_no,a.co_issue_date,d.dur," "from SDL a,dd b" "unit_value,rate_plan_code,rate_rate_scale,vl_plan_code,vl_rate_scale," into temp b1; "from a1 a, pldf b" where a.dept_code=b.dept_code "from arap a,echo_pldf b,dept c,r1 d" into temp s2; "select a.,b.co_sts_code" "from qqq a,pldf b" where rate_plan_code||rate_rate_scale in ( (case when b.modx!='0' then round(a.co_mode_prem12/b.modx) round(mode_prem_nwginw_type_cnt) and a.effect_date = b.gipn_issue_date into temp a3; "select policy_no,gta_seq,plan_code,sum(co_mode_prem)" and a.policy_no = b.policy_no and a.rate_age=b.rate_age and policy_no not in (select policy_no from del1) and a.coverage_no=d.coverage_no delete from rate_occu_upr; "where plan_abbr_code in ('SPTL','SFDL','SHDL','JYL','PWL','CATL','CYDL')" "select a.policy_no, a.coverage_no, a.plan_abbr_code," and a.rate_scale=b.rate_scale " a.plan_code, a.rate_scale, a.plan_abbr_code," where a.value_date=b.value_date "agp_p,day1,day2,t0,t1,agp_p_modx,upr,'N',' ',''" where a.plan_code = b.plan_code into temp ss2; "where a.value_date[1,3]=substr(d.value_date,1,3)" "from jo3 a,outer colf b" select * from ar1 where a.plan_code=b.plan_code select plan_code||rate_scale from ind_rate " else a.co_mode_prem end) agp," when ginw_type='D' and into temp s2; ------------------------------------ from gtco and b.relation = 'A' and a.rate_sex=b.rate_sex into temp j1; and b.paid_to_date>c.value_date insert into rate_occu_upr and policy_no not in (select policy_no from del1) "b.cl_item, c.cl_item_desc," "and a.plan_abbr_code in ('SPA','SFR','SEHI','NSEHI')" " a.rate_age, a.rate_sex,d.rate_occu,a.face_amt, a.modx," "group by 1,2;" from JPA --2.2 短年期健康險、傷害險 and a.rate_scale = b.rate_scale and a.class_code ='A' "select a.,dept_belong from s2 a,dept b" where a.policy_no=b.policy_no where policy_no||coverage_no not in (select policy_no||coverage_no from ar2) and a.rate_scale=b.rate_scale where rate_dur=1) ( ptd_nw>c.value_date and modx!='0' then --check amount-- where policy_no in (select distinct policy_no "and c.prod_ym=e.value_date[1,6]" into temp kkk; and d.co_sts_code!='43' select * from lu5; into temp lu1; (case when cl_deform_perc = '100' then '1' and policy_no not in (select policy_no from del1) " b.paid_to_date, unit_value,a.rate_plan_code," where value_date in ( select value_date from cc) "select distinct b.insurance_type, b.plan_abbr_code" into temp a2 "select a.,b.area_code area" and a.plan_abbr_code=b.plan_abbr_code where a.dept_code=b.dept_code and a.coverage_no=b.coverage_no into temp ex1; "group by 1,2,3,4" ; case when b.modx!='0' then -round(mode_prem_nw*(12/modx)ginw_type_cnt) update s2 --有入帳但不需提存 "from gipo a, cc b" and b.agent_code = c.agent_code "select c.value_date,a.policy_no,a.co_issue_date,e.dur," and a.policy_no=e.policy_no when cl_deform_perc = '90' then '2' into temp j1; " a.rate_rate_scale,a.vl_plan_code,a.vl_rate_scale" insert into yt1 ; "from plan_category a,arkt b" ; "from ss2 a,dist_code_n b" and a.dept_code=c.dept_code into temp s3; into temp jo4; "order by 1,2,3,4;" update lu2 " round(((datediff_k(c.value_date,a.paid_to_date))/365)" when ginw_type='D' and set occupation = '0' "select currency, count()" where a.expired_date > b.value_date) and c.dept_code=d.dept_code "select , case when modx='1' then round(agp0.08812,0)" " a.plan_code, a.rate_scale, a.plan_abbr_code," and e.coverage_no='1' "select a., b.unit_value,b.rate_plan_code,b.rate_rate_scale," "select a.,client_id from lu1 a,pocl b" when cl_deform_perc = '80' then '3' "from j1 a, arpo b,dd c,arco d" "select plan_abbr_code," insert into upr_com where a.plan_abbr_code=b.plan_abbr_code where a.dept_belong[2]=b.dept_belong into temp a1; "insert into upr43_cnt values('ex1',(select count() from ex1));" set rate_medi=0 a.co_mode_prem12/b.modx) ptd_nw>c.value_date and modx='0' then where rate_occu_ind = '0'; from a2 "group by 1,2,3" and a.plan_code =f.plan_code " when modx='3' then round(agp0.2624,0)" " a.rate_age, a.rate_sex,d.rate_occu,a.face_amt, a.modx," into temp j2; " b.vl_plan_code,b.vl_rate_scale" where a.policy_no=b.policy_no when cl_deform_perc = '70' then '4' "select c.value_date,a.policy_no,a.co_issue_date,d.dur," where a.policy_no=b.policy_no "(case when dur=1 then 1 else 2 end)," "select value_date,policy_no,co_issue_date,dur,plan_code,rate_scale," "and a.category in ('PA','PAR','SHR','SH')" "--1. ADDR, HADDR, HPDR" into temp ss3; "select 'Group',insurance_type,dept_belong[2],sum(upr)" "select value_date,policy_no,coverage_no,area_code,plan_code,rate_scale," where rate_plan_code||rate_rate_scale in ( " else round(((datediff_k(c.value_date,a.paid_to_date))/365)" -round(mode_prem_nwginw_type_cnt) where policy_no||currency not in ( "order by 1,3,2;" and a.rate_scale=f.rate_scale " when modx='6' then round(agp0.522,0)" " b.paid_to_date, unit_value,a.rate_plan_code," "from arkt a, pldf b" and client_ident='I1' when cl_deform_perc = '60' then '5' " a.plan_code, a.rate_scale, a.plan_abbr_code," and a.policy_no=d.policy_no "count(),sum(round(agp_p_modx,0)),sum(upr)" "plan_abbr_code,rate_age,rate_sex,rate_occu,rate_medi,face_amt," and b.plan_abbr_code <> ('SPA') "select a., b.prem_factor " "select a.,b.area_code area" from s3 "plan_abbr_code,policy_dur,co_issue_date,face_amt,agp,rsv_low_clm," "select a., paid_to_date,po_sts_code from ex1 a, arpo b" select plan_code||rate_scale from ind_rate a.co_mode_prem) end end "select a., b.insurance_type" select policy_no||currency and a.policy_no||a.coverage_no not in (select policy_no||coverage_no from rsvi) else agp end agp_modx " a.rate_rate_scale,a.vl_plan_code,a.vl_rate_scale" ----------------------------------------------- where a.plan_code=b.plan_code into temp lu2; when cl_deform_perc = '50' then '6' " a.rate_age, a.rate_sex,d.rate_occu,a.face_amt, a.modx," and a.coverage_no=d.coverage_no "from JPAT a,dd b" "modx,paid_to_date,unit_value,rate_plan_code,rate_rate_scale," and b.plan_abbr_code not in ( "from a2 a, prat b" "select case when acct_no[5,6]='01' then 'FY'" "from a1 a,dist_code_n b" "where plan_code not in ('KGI01','KGI02','KGI03')" "paid_to_date ,ptd_dur ," where a.policy_no=b.policy_no where rate_medi=1) ) upr ") agp," "from s2 a, gipf b" from vupr_r "group by 1,2,3,4,5,6,10,11,12,13" from kkk "from j1 a, arpo b,dd c,arco d,rsvm e" "--主約全殘,不在rsvm" and a.rate_scale=b.rate_scale when cl_deform_perc = '40' then '7' " b.paid_to_date, unit_value,a.rate_plan_code," into temp j2; where a.value_date=b.value_date "vl_plan_code,vl_rate_scale," select distinct plan_abbr_code from a1) "where a.plan_abbr_code in ('ADDR','HADDR','HPDR')" " else 'RY' end dur," where a.dept_belong[2]=b.dept_belong "group by 1,2,3" case when po_sts_code='44' then policy_dur into temp ex2; ; " from gico a,gipo b ,dd c" where a.plan_code = b.plan_code where po_sts_code between '42' and '59' "order by 1,2,3,4,5,6,10,11,12,13" into temp zzz where a.policy_no=b.policy_no insert into j2 and a.plan_abbr_code in ('SDL') "select a.,occupation_code from lu2 a,clnt b" when cl_deform_perc = '30' then '8' " a.rate_rate_scale,a.vl_plan_code,a.vl_rate_scale" "group by 1,2;" "agp_p,day1,day2,t0,t1,agp_p_modx,upr,'N',' ',''" "order by 1,2" and a.rate_plan_code = b.plan_code from ss3 into temp a2; "order by 2,3;" " else dur_final end as dur_final , --繳費期滿" update lu2 where a.policy_no=b.policy_no and a.rate_scale = b.rate_scale and plan_abbr_code!='LVL') ; ; and a.policy_no=d.policy_no "select c.value_date,a.policy_no,a.co_issue_date," into temp j1; where a.client_id=b.client_id when cl_deform_perc = '20' then '9' "from j1 a, arpo b,dd c,arco d" "select a.,prem_factorface_amt/unit_value agp_p," from SDL into temp b2; and a.rate_rate_scale = b.rate_scale into temp ss4; case when (po_sts_code='43'or co_sts_code='43') then 0 "insert into upr43_cnt values('ex2',(select count() from ex2));" set rate_occu=0 and a.policy_no not in (select policy_no from gp1) (case when ginw_type='A' and --A加保 D退保 into temp s3; group by 1; ------------------------------------------------------------ and a.coverage_no=d.coverage_no case into temp lu3; when cl_deform_perc = '10' then '10' where a.policy_no=b.policy_no " case when substr(co_issue_date,5,9)= '02/29'" insert into yt1 where value_date in ( select value_date from cc) and a.rate_age = b.rate_age "select currency, order_1," "select '學保',(case when area_code='B' then '7'" "else agp_final end as agp_final , --豁免保費" where rate_plan_code||rate_rate_scale in ( and b.po_sts_code between '42' and '59' ptd_nw>c.value_date and modx!='0' then --2.投資型附約 "select ,round(agp_modxdays/365,0) upr" and a.policy_no=e.policy_no " when cast(d.co_issue_date[5,6] as int) >cast(c.value_date[5,6] as int) or" "select c.value_date,a.policy_no,a.co_issue_date,d.dur," when cl_deform_perc = '5' then '11' and a.policy_no=d.policy_no then "select plan_abbr_code," ; --2.3 UL&VUL and a.rate_dur = b.rate_dur unload to 'dist_upr_U.txt' " type," "when area_code='C' then '4' else area_code end)," "po_sts_code ,co_sts_code" "select a., paid_to_date,po_sts_code from ex1 a, arpo_vul b" select plan_code||rate_scale from ind_rate and a.co_sts_code between '42' and '59' " round(((datediff_k(c.value_date,ptd_nw))/365)" "select a., b.prem_factor" select policy_no insert into upr_all from zzz and a.coverage_no=e.coverage_no " (cast(d.co_issue_date[5,6] as int) =cast(c.value_date[5,6] as int) and" " a.plan_code, a.rate_scale, a.plan_abbr_code," "select a.,occupation_level from lu3 a,nboc b" end) d_grade and a.coverage_no=d.coverage_no " datediff_k(value_date,""113/02/29"") -->每年2月結算update成下一年" "(case when dur=1 then 1 else 2 end)," insert into upr_com "select distinct insurance_type, plan_abbr_code, currency" and a.rate_occu = b.rate_occu "select currency,dur,area,sum(amount*-1)" " area," sum(amt_1 amt_2 amt_3) where a.policy_no=b.policy_no where rate_occu=1) and a.paid_to_date>=c.value_date mode_prem_nw*(12/modx)ginw_type_cnt) "from s3 a, gipr b" from a2 "select a.value_date,a.plan_code,a.rate_scale,a.plan_abbr_code," into temp aed; into temp j2; " cast(d.co_issue_date[8,9] as int) >cast(c.value_date[8,9] as int) )" " a.rate_age, a.rate_sex,d.occupation_level rate_occu," where a.occupation_code=b.occupation_code "from a1 a, cldt b, clit c" into temp j2; else "count(),sum(round(agp_p_modx,0)),sum(upr)" "select value_date,policy_no,co_issue_date,dur,plan_code,rate_scale," from vupr into temp a3 from ss4 " round(sum(f_prem),0) f_p," from acti from jo4 into temp ex3; ; and b.paid_to_date<a.paid_to_date when ginw_type='A' and where a.plan_code = b.plan_code where policy_no||currency not in ( "a.dur, area_code,count(),sum(a.agp) agp_upr,sum(upr) upr, d.currency,'N'," then " a.face_amt, a.modx," into temp lu4; where a.policy_no = b.policy_no " datediff_k(value_date," "from SPTL a,dd b" "plan_abbr_code,rate_age,rate_sex,rate_occu,rate_medi,face_amt," "where insurance_type in ('V','U')" ; "group by 1,2,3" " round(sum(r_prem),0) r_p" INNER JOIN cc ON acti.value_date = cc.value_date into temp jo5; "select a., prem_factorface_amt/unit_value agp_p" and a.paid_to_date is not null ptd_nw>c.value_date and modx='0' then and a.rate_scale = b.rate_scale select policy_no||currency "d.insurance_type, a.co_issue_date[1,6]" select * from aed "select a.,prem_factorface_amt/unit_value agp_p," " cast(c.value_date[1,3] as int)-cast(d.co_issue_date[1,3] as int)" " b.paid_to_date, unit_value,a.rate_plan_code," and b.cl_item = c.cl_item ---------------------------------- " round(co_issue_date[1,3] dur-1,0)||co_issue_date[4,9])" where a.value_date=b.value_date "modx,paid_to_date,unit_value,rate_plan_code,rate_rate_scale," and rsv_upr <> '0' "order by 1,2,3" from a2 "where plan_code in ('KGI01','KGI02','KGI03')" "insert into upr43_cnt values('ex3',(select count() from ex3));" "from lu2 a, outer pra2 b" and a.paid_to_date!='' " round(((datediff_k(c.value_date,ptd_nw))/365)" and a.pn_rate_class_ptr = b.gi_rate_class_ptr from vupr_r "from arco_a a,cc b,varco c, pldf d" where days<0; " case when substr(co_issue_date,5,9)= '02/29'" else " a.rate_rate_scale,a.vl_plan_code,a.vl_rate_scale" "select a.,dur from lu4 a,rsvm b" and b.cl_item in ('612') --pra2中的rate_occu皆為0，但colf中rate_occu = '' " end d1," "group by 1,2;" "vl_plan_code,vl_rate_scale," and plan_abbr_code <> 'LVL' --2. AFRR "group by 1,2,3,4" "group by 1,2" where a.rate_plan_code=b.plan_code ; mode_prem_nwginw_type_cnt) and a.occupation = b.rate_occu where po_sts_code between '42' and '59' where a.plan_abbr_code!='GPA' then " cast(c.value_date[1,3] as int)-cast(d.co_issue_date[1,3] as int) 1" "from j1 a, arpo b,dd c,rate_occu_upr d" where a.policy_no=b.policy_no and a.policy_no||a.coverage_no not in ( update j2 " case when substr(co_issue_date,5,9)= '02/29'" "agp_p,day1,day2,t0,t1,agp_p_modx,upr,'N',' ',''" and insurance_type||plan_abbr_code not in ( insert into a3 into temp echo_arap_1 "order by 1,2;" truncate table rsv_low_clm_t; "select a., paid_to_date,po_sts_code from ex1 a, popa b" and a.rate_rate_scale=b.rate_scale insert into gico_upr when ginw_type='D' and into temp s4; and plan_abbr_code!='LVL') and a.policy_no=c.policy_no insert into AED_KED " datediff_k(value_date,""113/02/29"") -->每年2月結算update成下一年" " end dur," where a.policy_no=b.policy_no and a.coverage_no=b.coverage_no select policy_no||coverage_no from a2) set rate_occu = '0' then insert into yt1 from SPTL "select a.insurance_type||a.plan_abbr_code from vupr_all a, dd b" "select a., b.prem_factor " ; where a.policy_no=b.policy_no and a.rate_age=b.rate_age "select a.,b.insurance_type" ptd_nw>c.value_date and modx!='0' then and currency = 'USD' and a.policy_no=c.policy_no "select value_date,policy_no,plan_code,rate_scale," else " a.plan_code, a.rate_scale, a.plan_abbr_code," and a.policy_no=d.policy_no into temp lu5; "order by 1,2,3" "where plan_abbr_code in ('SEHI','NSEHI');" " datediff_k(""112/02/28"",""113/02/29"")" "select plan_abbr_code," where value_date in ( select value_date from cc) where a.value_date = b.value_date) "from a2 a, prat b" update echo_arap_1 "select '學保',(case when area_code='B' then '7'" insert into rsv_low_clm_t into temp ex4; and a.rate_sex=b.rate_sex " from eee a,gipf b" " -round(((datediff_k(c.value_date,ptd_nw))/365)" "select , round(face_amtprem_factor/unit_value,0) prem" ; and a.plan_code =d.plan_code "plan_abbr_code,rate_age,rate_sex,face_amt,modx," " datediff_k(value_date," " a.rate_age, a.rate_sex,d.rate_occu,a.face_amt, a.modx," and a.coverage_no=d.coverage_no into temp a3; ---------------------------------- else "(case when dur=1 then 1 else 2 end)," ; into temp b3; where a.plan_abbr_code in ('AFRR') set order_1='2' "when area_code='C' then '4' else area_code end)," "select value_date,policy_no,coverage_no,area_code,plan_code,rate_scale," and a.rate_dur=b.rate_dur where a.plan_code=b.plan_code mode_prem_nw(12/modx)ginw_type_cnt) from s4 and a.rate_scale=d.rate_scale "po_issue_date,paid_to_date,unit_value,agp,days," " round(co_issue_date[1,3] dur-1,0)||co_issue_date[4,9])" " b.paid_to_date, ""1000"",a.rate_plan_code," into temp j2; delete from rate_occu_upr " datediff_k(round(co_issue_date[1,3] dur,0)||co_issue_date[4,9]," "count(),sum(round(agp_p_modx,0)),sum(upr)" insert into upr_com and a.rate_plan_code = b.plan_code where type='T' sum(amt_1 amt_2 amt_3) "plan_abbr_code,policy_dur,co_issue_date,face_amt,agp,rsv_low_clm," "insert into upr43_cnt values('ex4',(select count() from ex4));" and a.rate_medi=b.rate_medi and a.rate_scale=b.rate_scale into temp a1; --需提存但沒有入帳 and a.policy_no||a.coverage_no not in (select policy_no||coverage_no from rsvi) "agp_modx,upr" " end d1," " a.rate_rate_scale,a.vl_plan_code,a.vl_rate_scale" where 1=1; --因疾病以致1-6級失能，只需提平衡制準備金部分(豁免保險費) "select a.,prem_factorface_amt/unit_value agp_p," " round(co_issue_date[1,3] dur-1,0)||co_issue_date[4,9]) " "from SFDL a,dd b" "select value_date,policy_no,co_issue_date,dur,plan_code,rate_scale," --3 合併 and a.rate_rate_scale = b.rate_scale ; "from acti a, cc b" "paid_to_date ,ptd_dur ," and a.rate_occu=b.rate_occu and a.upr>='0'; when ginw_type='D' and "select currency, count()" "group by 1,2,3,4,5,6,10,11,12,13" from aed; " case when substr(co_issue_date,5,9)= '02/29'" "from j1 a, arpo b,dd c,arco d" "select a., b.rate_medi from j2 a,rsvm b" insert into rate_occu_upr "select policy_no, coverage_no" " case when substr(co_issue_date,5,9)= '02/29'" " end d2," where a.value_date=b.value_date "plan_abbr_code,rate_age,rate_sex,rate_occu,rate_medi,face_amt," "select '長年期傷害險', " and a.rate_dur = b.rate_dur update echo_arap_1 "where plan_code in ('NUGI','GUI','GUIR','NUGIR'" case when co_issue_date>value_date then 0 "select a.,co_sts_code" and a.rate_insu=b.rate_insu ptd_nw>c.value_date and modx='0' then "select c.value_date,a.," from a3 "order by 1,2,3,4,5,6,10,11,12,13" then where a.policy_no=b.policy_no where a.policy_no=b.policy_no select * from lu5; from a3 then case when paid_to_date>value_date "group by 1,2;" "modx,paid_to_date,unit_value,rate_plan_code,rate_rate_scale," from b1; and a.rate_occu = b.rate_occu set order_1='3' ",'NUI','UGI','GECI','GECIGI','GECMR','GECMXR','GECSI'" " else dur_final end as dur_final ," "from ex2 a,arco b" and a.rate_unit=b.rate_unit unload to 'upr_gico.txt' " -round(((datediff_k(c.value_date,ptd_nw))/365)" (case when ginw_type='A' and where policy_no||currency not in ( ; " datediff_k(""112/02/28"",""113/02/29"")" and a.policy_no=d.policy_no and a.plan_code=b.plan_code where d_grade <= 6 " datediff_k(value_date,""113/02/29"") -->每年2月結算update成下一年" then "vl_plan_code,vl_rate_scale," ; where type='G' ",'GECHI','GECHSA','GECHSB','GECDDB','GECCS','GECMSI')" case when co_issue_date>value_date then 0 where a.policy_no=b.policy_no into temp lu3; select mode_prem_nwginw_type_cnt) ptd_nw>c.value_date and modx!='0' then select policy_no||currency ------------------------------------------------------------ else and a.coverage_no=d.coverage_no and a.rate_scale=b.rate_scale "select a., b.unit_value,b.rate_plan_code,b.rate_rate_scale," into temp b1; else " datediff_k( paid_to_date, value_date)" insert into yt1 "agp_p,day1,day2,t0,t1,agp_p_modx,upr,'N',' ',''" "select '短年期健康險、傷害險', " ; and a.value_date = b.value_date "else agp_final end as agp_final ," and a.coverage_no=b.coverage_no "select agp_p, from lu3" "value_date,insurance_type ," end round(prem(12/modx)ginw_type_cnt) from a2) --3.長年期(具傷害險部分) " datediff_k(round(co_issue_date[1,3] dur,0)||co_issue_date[4,9]," and b.paid_to_date>c.value_date into temp j22; " b.vl_plan_code,b.vl_rate_scale" " datediff_k(value_date," else 0 end day2 "select plan_abbr_code," from SFDL from b2; --3. AMRR update echo_arap_1 "group by 1,2" "po_sts_code ,co_sts_code" into temp s1 where agp_p is null; "count() cnt, sum(agp) agp,sum(upr) upr from gico_upr" ) upr when ginw_type='A' and group by 1; insert into upr_all " round(co_issue_date[1,3] dur-1,0)||co_issue_date[4,9]) " and d.co_sts_code = '42' "from arkt a, pldf b" --因傷害以致1-6級失能，不需提(豁免保險費&平衡制準備金=0) " round(co_issue_date[1,3] dur-1,0)||co_issue_date[4,9])" "from j2 a, outer pra2 b" "(case when dur=1 then 1 else 2 end)," where value_date in ( select value_date from cc) insert into a3 set order_1='4' "order by 1,2;" from jo5 ; where value_date in (select value_date from dd) ptd_nw>c.value_date and modx='0' then "select a.value_date,a.plan_code,a.rate_scale,a.plan_abbr_code," " end d2," and a.policy_no = '178900187641'; "select a.,prem_factorface_amt/unit_value agp_p," where a.plan_code=b.plan_code "select policy_no, coverage_no" " end d1," where a.rate_plan_code=b.plan_code "count(),sum(round(agp_p_modx,0)),sum(upr)" ; "select 'UL&VUL', " "select a., b.prem_factor " where type='H' ; insert into s1 "select , case when modx='1' then round(agp_p0.08812,0)" "and plan_code not in ('KGI01','KGI02','KGI03')" "from aaa1 a ,ee c" round(premginw_type_cnt) select policy_no "dur, area_code,count(),sum(agp_p) agp_upr,sum(upr) upr, d.currency,'N'," case when paid_to_date>value_date ----------------------------------------------- " case when substr(co_issue_date,5,9)= '02/29'--不動" and a.rate_scale=b.rate_scale from a2 " case when substr(co_issue_date,5,9)= '02/29'" and a.rate_rate_scale=b.rate_scale "from SHDL a,dd b" insert into upr_com from b3; "from a2 a, prat b" ; "select a.,co_sts_code" " when modx='3' then round(agp_p0.2624,0)" "--and plan_code not in ('UGI','NUGI','NUI','KGI01','KGI02','KGI03'," ; when ginw_type='D' and from a3 "insurance_type, co_issue_date[1,6]" then then "and a.plan_abbr_code in ('SPTL','SFDL','SHDL','JYL','PWL','CATL','CYDL')" where d_grade <= 6 then and a.rate_age=b.rate_age where a.value_date=b.value_date "select value_date,policy_no,co_issue_date,dur,plan_code,rate_scale," where a.plan_abbr_code in ('AMRR') update echo_arap_1 unload to 'low_11202.dat' "from ex3 a,arco_vul b" " when modx='6' then round(agp_p0.522,0)" "-- 'GUI','GUIR')" ptd_nw>c.value_date and modx!='0' then where policy_no||currency not in ( "from upr_com a, cc b, arpo c, pldf d" " datediff_k( paid_to_date, value_date)" "select a.,prem_factorface_amt/unit_value agp_p," " datediff_k(value_date,""113/02/29"") -->每年2月結算update成下一年" and policy_no not in (select policy_no from del1) into temp b2; " datediff_k(""112/02/28"",""113/02/29"")" and a.rate_sex=b.rate_sex "group by 1,2;" "plan_abbr_code,rate_age,rate_sex,rate_occu,rate_medi,face_amt," and a.rate_plan_code = b.plan_code set order_1='1' "select plan_abbr_code,dur_final,count() cnt," where a.policy_no=b.policy_no " else round(agp_p,0) end agp_modx" "group by 1,2" -round(prem*(12/modx)ginw_type_cnt) select policy_no||currency where a.value_date=b.value_date else 0 end day2 " case when substr(co_issue_date,5,9)= '02/29'" else into temp j1; else and a.rate_occu=b.rate_occu "modx,paid_to_date,unit_value,rate_plan_code,rate_rate_scale," and a.rate_rate_scale = b.rate_scale where type='P' sum(agp_final) agp and a.coverage_no=b.coverage_no from lu3 order by 2 unload to 'upr_ginw.txt' when ginw_type='D' and from a2) and a.policy_no=c.policy_no "from j2 a, outer pra2 b" then " datediff_k(value_date," select * from a1 " datediff_k(round(co_issue_date[1,3] dur,0)||co_issue_date[4,9]," into temp nk1; insert into yt1 "vl_plan_code,vl_rate_scale," and a.rate_occu = b.rate_occu ; from rsv_low_clm_t ; into temp lu4; "select a.value_date,'',''," ptd_nw>c.value_date and modx='0' then into temp j1; and a.plan_code=d.plan_code where a.rate_plan_code=b.plan_code " datediff_k(value_date,""113/02/29"") -->每年2月結算update成下一年" " round(co_issue_date[1,3] dur-1,0)||co_issue_date[4,9])" "select c.value_date,a.policy_no,a.co_issue_date,d.dur," where policy_no||coverage_no not in ( " round(co_issue_date[1,3] dur-1,0)||co_issue_date[4,9]) " "select plan_abbr_code," "agp_p,day1,day2,t0,t1,agp_p_modx,upr,'N',' ',''" and a.rate_insu = b.rate_insu "select currency,type,sum(f_p) f_p_t," "where plan_abbr_code in ('AMRR','HSR','AMR')" "insert into upr43_cnt values('s1',(select count() from s1));" "sum(agp) agp,sum(upr) upr from ginw_upr a,ee b" -round(premginw_type_cnt) ------------------------------------ and a.rate_scale=d.rate_scale and a.rate_rate_scale=b.rate_scale else " end d1," " a.plan_code, a.rate_scale, a.plan_abbr_code," select policy_no||coverage_no from b2) " end d2," "select , case when d1 > d2" "(case when dur=1 then 1 else 2 end)," from SHDL ; sum(r_p) r_p_t "group by 1,2" --check 新增險種皆以納入_1070331 where a.value_date=b.value_date end --若無異常 "group by 1,2,3,4,5,6,10,11,12,13" and a.rate_age=b.rate_age " datediff_k(value_date," " case when substr(co_issue_date,5,9)= '02/29'" " a.rate_age, a.rate_sex,d.occupation_level rate_occu," into temp t1; case when paid_to_date>value_date then d2 "count(),sum(round(agp_p_modx,0)),sum(upr)" where value_date in ( select value_date from cc) from echo_arap_1 "order by 1,2" select * from s1 select plan_abbr_code from lu1 group by 1 ") agp," insert into vupr_all "order by 1,2,3,4,5,6,10,11,12,13" and a.rate_sex=b.rate_sex " round(co_issue_date[1,3] dur-1,0)||co_issue_date[4,9])" then " a.face_amt, a.modx," then else d1 "from AHAD a,dd b" ; "--4. BHSR, DHSR, DZHSR, GOFCR, HSCR, JWPR, MJWPR, MSWPR, SWPR, OWPR" "group by 1,2" where (po_sts_code='43' or co_sts_code='43') where plan_abbr_code not in ( "select a.value_date, a.policy_no," ; and a.rate_occu=b.rate_occu " end d1," " datediff_k(""112/02/28"",""113/02/29"")" " b.paid_to_date, unit_value,a.rate_plan_code," "select a., b.unit_value,b.rate_plan_code,b.rate_rate_scale," " datediff_k( paid_to_date, value_date)" end day1 where a.value_date=b.value_date insert into upr_com insert into a3 "order by 2,1" ; into temp s43; select plan_abbr_code from lu4) (case when ginw_type='A' and --A加保 D退保 "a.plan_code, a.rate_scale, a.plan_abbr_code," ------------------------------------------------------------ into temp nk1; " case when substr(co_issue_date,5,9)= '02/29'" else " a.rate_rate_scale,a.vl_plan_code,a.vl_rate_scale" "b.vl_plan_code,b.vl_rate_scale" else 0 end day2 from nk1 "group by 1,2;" "select value_date,policy_no,co_issue_date,dur,plan_code,rate_scale," "select a., b.prem_factor " into temp echo_arap_2 unload to 'low_exp_11202.dat' group by 1 ptd_nw>c.value_date and modx!='0' then "a.dur, a.area_code, count() cnt, sum(b.coi12) agp_upr," --4.投資型COI then " datediff_k(round(co_issue_date[1,3] dur,0)||co_issue_date[4,9]," "from j1 a, arpo b,dd c,rate_occu_upr d" "from t1 a, pldf b" "from j2 a, outer pra2 b" into temp j3; "plan_abbr_code,rate_age,rate_sex,rate_occu,rate_medi,face_amt," "from a2 a, prat b" ; select a. "insert into upr43_cnt values('s43',(select count() from s43));" order by 1 " round(((datediff_k(c.value_date,ptd_nw))/365)" "sum(a.rsv_upr) upr, a.currency,'N'," insert into upr_all "select , case when d1 > d2" " datediff_k(""112/02/28"",""113/02/29"")" " round(co_issue_date[1,3] dur-1,0)||co_issue_date[4,9]) " where a.policy_no=b.policy_no where a.plan_code=b.plan_code where a.rate_plan_code=b.plan_code insert into yt1 "modx,paid_to_date,unit_value,rate_plan_code,rate_rate_scale," "where a.plan_abbr_code in ('BHSR','DHSR','DZHSR'," "select a.,b.f_p_t,b.r_p_t" "from jo5 a,qq b" ; prem*(12/modx)ginw_type_cnt) "a.insurance_type, a.po_issue_date[1,6] issue_ym" "select a.value_date, a.plan_code, a.rate_scale, a.plan_abbr_code," then d2 else " end d2," and a.policy_no=d.policy_no and a.rate_scale=b.rate_scale and a.rate_rate_scale=b.rate_scale --選出一般件的責任準備因子 "select plan_abbr_code," "vl_plan_code,vl_rate_scale," "'GOFCR','HSCR','JWPR','MJWPR','MSWPR','SWPR','OWPR')" "from echo_arap_1 a,echo_arap_2 b" where paid_to_date<co_issue_date --年繳 unload to 'upr_inf_p.txt' when ginw_type='A' and "from vupr_r a, a2 b" "a.dur, a.area_code, sum(a.cnt) cnt, sum(a.agp_upr) agp_upr, sum(a.upr) upr, " else d1 " datediff_k(round(co_issue_date[1,3] dur,0)||co_issue_date[4,9]," case when paid_to_date>value_date and a.coverage_no=d.coverage_no into temp j1; and a.rate_age=b.rate_age "select a.,b.tv_unit_value_t0 t0,b.tv_unit_value_t1 t1" "(case when dur=1 then 1 else 2 end)," "agp_p,day1,day2,t0,t1,agp_p_modx,upr,'N',' ',''" and a.rate_plan_code = b.plan_code where a.type=b.type and co_issue_date< b.pp_date "---生效月>決算月,年度不動" "select plan_abbr_code,count(),sum(agp_modx)" ptd_nw>c.value_date and modx='0' then "where a.insurance_type in ('V','U')" "a.currency, a.ta_ind, a.insurance_type, a.issue_ym" end day1 " round(co_issue_date[1,3] dur-1,0)||co_issue_date[4,9]) " then into temp j2; and a.rate_sex=b.rate_sex "from j3 a,outer arv5 b" "count(),sum(round(agp_p_modx,0)),sum(upr)" from JYL and a.rate_rate_scale = b.rate_scale and a.currency=b.currency and ptd_dur=0 "select ,'112/'||co_issue_date[5,9] paid_to_date_n from s43" from lu4 " round(((datediff_k(c.value_date,ptd_nw))/365)" and a.rsv_upr>0 "from vupr_all a, cc b" from nk1 " end d2," " datediff_k( paid_to_date, value_date)" "select policy_no,coverage_no,plan_code,rate_scale," and a.rate_occu=b.rate_occu where a.vl_plan_code=b.vl_plan_code "from JYL a,dd b" where value_date in ( select value_date from cc) and a.rate_age = b.rate_age into temp echo_arap "and plan_abbr_code in ('AMRR','HSR')" "where co_issue_date[5,9]> (select value_date[5,9] from cc)" group by 1 premginw_type_cnt) and a.plan_abbr_code!='LVL' --> SP COI一次扣完(算責任準備) where a.value_date = b.value_date into temp j3; case when paid_to_date>value_date else 0 end day2 "select a., b.rate_medi from j2 a,rsvm b" plan_abbr_code from j1 into temp nk1; and a.vl_rate_scale=b.vl_rate_scale where a.value_date=b.value_date ; and a.rate_dur = b.rate_dur ; ; "and co_issue_date[5,9]<>'02/29'" order by 1; when ginw_type='D' and and a.po_sts_code between '42' and '59' "group by 1,2,3,4,5,6,10,11,12,13" then "from j22 a, outer pra2 b" where a.policy_no=b.policy_no into temp lu1; and a.rate_sex=b.sex "group by 1,2;" insert into upr_com and a.rate_sex = b.rate_sex unload to 'dist_upr_FY.txt' and modx ='12' ptd_nw>c.value_date and modx!='0' then and a.policy_no = b.policy_no "order by 1,2,3,4,5,6,10,11,12,13;" --選出一般件的平準制責任準備因子 " datediff_k( paid_to_date, value_date)" where a.rate_plan_code=b.plan_code and a.plan_code=b.plan_code "select , case when d1 > d2" and a.rate_age=b.age "select value_date,policy_no,co_issue_date,dur,plan_code,rate_scale," ; "select currency,'FY' fy_or_ry,order_1,type,area,round(f_p/f_p_t,4) ratio1" into temp m1 " -round(((datediff_k(c.value_date,ptd_nw))/365)" and a.currency = b.currency ------------------------------------------------------------ "select a.,b.tv_unit_value_t0 t0,b.tv_unit_value_t1 t1" else 0 end day2 and a.rate_rate_scale=b.rate_scale and a.rate_scale=b.rate_scale "select a.,client_id from lu1 a,pocl b" then d2 and a.dur=b.dur insert into yt1 "plan_abbr_code,rate_age,rate_sex,rate_occu,rate_medi,face_amt," " --und(r_p/r_p_t,4) ratio2" ; prem*(12/modx)ginw_type_cnt) and a.policy_no not in (select policy_no from j1) --5.豁免附約 "from j3 a,outer arv5 b -->104/01改為arv5" "from j2 a, outer pra2 b" and a.rate_age=b.rate_age into temp j22; where a.policy_no=b.policy_no else d1 and policy_no not in (select policy_no from rpu1) "select plan_abbr_code," "modx,paid_to_date,unit_value,rate_plan_code,rate_rate_scale," --5. DHIR from echo_arap "---生效月<=決算月,年度 1" "group by 1,2,3,4,5,6,7,11,12,13,14" insert into upr_all where a.vl_plan_code=b.vl_plan_code where a.rate_plan_code=b.plan_code and a.rate_sex=b.rate_sex and client_ident='I1' end day1 into temp j4; "(case when dur=1 then 1 else 2 end)," "vl_plan_code,vl_rate_scale," insert into a3 "order by 1,2,3,4,5" --特別處理2/29' Q:閏年2/29決算年度該 1嗎 when ginw_type='D' and "order by 1,2,5,3,4;" "select a.value_date,a.plan_code,a.rate_scale,a.plan_abbr_code," and a.vl_rate_scale=b.vl_rate_scale and a.rate_rate_scale=b.rate_scale and a.rate_medi=b.rate_medi "select a.,prem_factorface_amt/unit_value agp_p," into temp lu2; from nk1 "count(),sum(round(agp_p_modx,0)),sum(upr)" "agp_p,day1,day2,t0,t1,agp_p_modx,upr,'N',' ',''" "select a., b.prem_factor " ; insert into m1 A:該 ptd_nw>c.value_date and modx='0' then ------------------------------------ "dur, area_code,count(),sum(a.agp) agp_upr,sum(upr) upr, a.currency,'N'," and a.rate_sex=b.sex and a.rate_age=b.rate_age --and a.rate_occu=b.rate_occu --SDL保費不受職業等級影響 " case when substr(co_issue_date,5,9)= '02/29' --不動" into temp j3; --選出減額繳清件的責任準備因子 "from KAD a,dd b" from KAD "from a2 a, prat b" unload to 'dist_upr_RY.txt' "select ,'113/02/29' paid_to_date_n from s43 --一年更新一次" 113/02/29 " -round(((datediff_k(c.value_date,ptd_nw))/365)" "--本月未入帳,但先提存(待後續調整)" "e.insurance_type,a.co_issue_date[1,6]" and a.rate_age=b.age and a.rate_sex=b.rate_sex into temp nk1; then "select a.,occupation_code from lu2 a,clnt b" insert into j4 where a.value_date=b.value_date where value_date in ( select value_date from cc) where a.plan_abbr_code in ('DHIR') "select currency,'RY' fy_or_ry,order_1,type,area,round(r_p/r_p_t,4) ratio2" "where co_issue_date[5,9]='02/29'" 2024/02/01 premginw_type_cnt) insert into vupr_all "from upr43 a, cc b, arco c,arpo d, pldf e" and a.dur=b.dur and a.rate_occu=b.rate_occu " datediff_k(value_date,""113/02/29"") -->每年2月結算update成下一年" where a.client_id=b.client_id --選出一般件的責任準備因子 "select a.,b.pd_factor_t0 t0,b.pd_factor_t1 t1" "group by 1,2;" ; and a.rate_plan_code = b.plan_code from echo_arap and modx ='12' 112/02/28 end "select a.value_date, a.policy_no," where a.value_date=b.value_date and policy_no not in (select policy_no from rpu1) into temp nk1; "select , case when d1 > d2" else into temp lu3; "select a.,b.tv_unit_value_t0 t0,b.tv_unit_value_t1 t1" "from j3 a,outer arv5 b -->104/01改為arv5" insert into upr_com and a.rate_rate_scale = b.rate_scale "where order_1 in ('1','4')" ; 2023/02/01 ) upr "a.plan_code, a.rate_scale, a.plan_abbr_code," and a.policy_no=c.policy_no and a.plan_abbr_code not in ('AHAD') then d2 " datediff_k(value_date," "from j3 a,outer arv5 b" where a.vl_plan_code=b.vl_plan_code insert into yt1 "select value_date,policy_no,co_issue_date,dur,plan_code,rate_scale," and a.rate_occu = b.rate_occu "order by 1,2,3,4,5" insert into m1 "from a1 a ,ee c" "a.dur, a.area_code, count() cnt, '0' agp_upr," and a.coverage_no =c.coverage_no into temp j4; "select , case when d1 > d2" else d1 " round(co_issue_date[1,3] dur-1,0)||co_issue_date[4,9])" "select lu3.,nboc.occupation_level from lu3 LEFT JOIN nboc" where a.vl_plan_code=b.vl_plan_code and a.vl_rate_scale=b.vl_rate_scale "select plan_abbr_code," "plan_abbr_code,rate_age,rate_sex,rate_occu,rate_medi,face_amt," ; ; "select ,'113/'||co_issue_date[5,9] paid_to_date_n from s43" into temp a2; "sum(a.rsv_upr) upr, a.currency,'N'," and a.policy_no = d.policy_no then d2 end day1 " end d1," ON lu3.occupation_code = nboc.occupation_code and a.vl_rate_scale=b.vl_rate_scale and a.rate_sex=b.sex "(case when dur=1 then 1 else 2 end)," "modx,paid_to_date,unit_value,rate_plan_code,rate_rate_scale," unload to 'dist_upr_ARAP.txt' "where co_issue_date[5,9] between '01/01' and (select value_date[5,9] from cc)" "a.insurance_type, a.po_issue_date[1,6] issue_ym" and a.plan_code =e.plan_code insert into j4 else d1 from nk1 " case when substr(co_issue_date,5,9)= '02/29'" into temp lu4; and a.rate_sex=b.sex and a.rate_age=b.age "count(),sum(round(agp_p_modx,0)),sum(upr)" "vl_plan_code,vl_rate_scale," "--6. GODCR, GOLHR, NGODCR" select * from echo_arap_1 "and co_issue_date[5,9]<>'02/29'" "select value_date, policy_no, ginw_type, gipn_code, gipn_vers," "from vupr_r a, j1 b" and a.rate_scale=e.rate_scale "select a.,b.tv_unit_value_t0 t0,b.tv_unit_value_t1 t1" end day1 into temp j3; then and a.rate_age=b.age and a.dur=b.dur "from CATL a,dd b" "agp_p,day1,day2,t0,t1,agp_p_modx,upr,'N',' ',''" insert into a3 "order by 1,2" and modx ='12' " plan_code, rate_scale, insurance_type, modx, face_amt," "where a.insurance_type in ('V','U')" "group by 1,2,3,4,5,6,10,11,12,13" "from j3 a,outer arv5 b -->104/01改為arv5" from nk1 " datediff_k(""112/02/28"",""113/02/29"")" "select a.,dur from lu4 a,rsvm b" and a.dur=b.dur and policy_no in (select policy_no from rpu1) where a.value_date=b.value_date from CATL "select a., b.prem_factor " ; " unit_value, prem_factor, prem, agp, upr, mode_prem," and a.rsv_upr>0 "order by 1,2,3,4,5,6,10,11,12,13" where a.vl_plan_code=b.vl_plan_code into temp j3; "select a.,b.tv_unit_value_t0 t0,b.tv_unit_value_t1 t1" else where a.policy_no=b.policy_no and policy_no not in (select policy_no from rpu1) ; "group by 1,2;" where value_date in ( select value_date from cc) "from a2 a, prat b" "insert into upr43_cnt values('m1',(select count() from m1));" ginw_type_cnt and a.plan_abbr_code!='LVL' --> SP COI一次扣完(算責任準備) ; and a.vl_rate_scale=b.vl_rate_scale "from j3 a,outer arv5 b -->104/01改arv5" " datediff_k(round(co_issue_date[1,3] dur,0)||co_issue_date[4,9]," and a.coverage_no=b.coverage_no into temp j4; "select , case when modx='1' then round(agp_p0.08812,0)" ; "where a.plan_abbr_code in ('GODCR','GOLHR','NGODCR')" from a2 and a.po_sts_code between '42' and '59' ------------------------------------------------------------ and a.rate_sex=b.sex "select a.,b.tv_unit_value_t0 t0,b.tv_unit_value_t1 t1" where a.vl_plan_code=b.vl_plan_code " round(co_issue_date[1,3] dur-1,0)||co_issue_date[4,9]) " into temp lu5; " when modx='3' then round(agp_p0.2624,0)" insert into yt1 insert into upr_com and a.rate_plan_code = b.plan_code "--季繳,當月份時需調整大於value_date" "order by 2,3,4,5,16,6,7" and a.policy_no = b.policy_no --6.旅平險 and a.rate_age=b.age "from j3 a,outer arv5 b -->104/01起改arv5" and a.vl_rate_scale=b.vl_rate_scale " end d2," --選出減額繳清件的責任準備因子 " when modx='6' then round(agp_p0.522,0)" "select plan_abbr_code," "select value_date,policy_no,co_issue_date,dur,plan_code,rate_scale," and a.rate_rate_scale = b.rate_scale "select ,'112/04/'||co_issue_date[8,9] paid_to_date_n from s43" 112/04 into temp f1; "group by 1,2,3,4,5,6,7,11,12,13,14" select distinct policy_no and a.dur=b.dur where a.vl_plan_code=b.vl_plan_code and a.rate_sex=b.sex case when paid_to_date>value_date delete from rate_occu_upr insert into j4 else agp_p end agp_p_modx "(case when dur=1 then 1 else 2 end)," "plan_abbr_code,rate_age,rate_sex,rate_occu,'0',face_amt," and a.rate_age = b.rate_age "where co_issue_date[5,6] in ('01','04','07','10')" 2023/04/01 "order by 1,2,5,3,4;" "from taupr a, cc b" and policy_no not in (select policy_no from rpu1) and a.vl_rate_scale=b.vl_rate_scale and a.rate_age=b.age then where 1=1; "select a.,b.pd_factor_t0 t0,b.pd_factor_t1 t1" from j4 "count(),sum(round(agp_p_modx,0)),sum(upr)" "modx,paid_to_date,unit_value,rate_plan_code,rate_rate_scale," and a.rate_dur = b.rate_dur "and modx ='3' and co_issue_date[8,9]<='30'" 30 insert into ginw_upr_v1 ------------------------------------ "where a.value_date[1,6]=b.value_date[1,6]" and b.sts_ind = 0 and a.rate_sex=b.sex and a.dur=b.dur " datediff_k( paid_to_date, value_date)" insert into rate_occu_upr "from j3 a,outer arv5 b -->104/01改為arv5" into temp j5; "from SPA a,dd b" "vl_plan_code,vl_rate_scale," and a.rate_sex = b.rate_sex into temp m2 select * from f1; unload to 'vupr_temp.txt' and plan_code='ADD' and a.plan_abbr_code in ('AHAD') and a.rate_age=b.age --d a.rate_occu=b.occu else 0 end day2 select * from lu5; where a.vl_plan_code=b.vl_plan_code where a.value_date=b.value_date "agp_p,day1,day2,t0,t1,agp_p_modx,upr,'N',' ',''" and a.rate_medi = b.rate_medi ; "select insurance_type, plan_abbr_code," and policy_no not in (select policy_no from co03 ; and a.dur=b.dur into temp j4; "from j22 a, outer pra2 b" and a.vl_rate_scale=b.vl_rate_scale "select ," "group by 1,2;" from SPA ; insert into m2 unload to 'ginw_upr_v1.txt' "case when dur='1' then 'FY' else 'RY' end dur, currency," where plan_code='TA' --選出減額繳清件的責任準備因子 into temp j4 where a.rate_plan_code=b.plan_code "select c.value_date,a.policy_no,a.co_issue_date,d.dur," and a.rate_sex=b.sex "round((t0 day1/365(t1-t0))face_amt/unit_value agp_p_modxday2/365,0) upr" where value_date in ( select value_date from cc) "select ,'112/04/30' paid_to_date_n from s43" 112/04/30 "select a.value_date, a.insurance_type , ''," "sum(cnt), sum(agp_upr), sum(upr)" and coverage_no <>'0') insert into j4 ; "select , case when modx='1' then round(agp_p0.08812,0)" and a.rate_rate_scale=b.rate_scale " a.plan_code, a.rate_scale, a.plan_abbr_code," and a.rate_age=b.age from j5 insert into yt1 ; --7. GODR "where co_issue_date[5,6] in ('01','04','07','10')" "sum(a.agp) agp,sum(a.upr) upr" from vupr_all into temp bb; "select a.,b.pd_factor_t0 t0,b.pd_factor_t1 t1" "select , case when modx='1' then round(agp_p0.08812,0)" " when modx='3' then round(agp_p0.2624,0)" and a.rate_age=b.rate_age " a.rate_age, a.rate_sex,d.occupation_level rate_occu," and a.dur=b.dur into temp j6; "select plan_abbr_code," insert into upr_com insert into a3 "and modx ='3' and co_issue_date[8,9]>'30'" 30 "from ginw_upr_v1 a, ee b" where value_date = '112/02/28' "from j3 a,outer arv5 b -->104/01改為arv5" " when modx='3' then round(agp_p0.2624,0)" " when modx='6' then round(agp_p0.522,0)" and a.rate_sex=b.rate_sex " a.face_amt, a.modx," and policy_no in (select policy_no from rpu1) "(case when dur=1 then 1 else 2 end)," "select value_date,policy_no,co_issue_date,dur,plan_code,rate_scale," "select a., b.prem_factor " ; where a.value_date = b.value_date "group by 1,2,3,4" select a. where a.vl_plan_code=b.vl_plan_code " when modx='6' then round(agp_p0.522,0)" else agp_p end agp_p_modx and a.rate_medi=b.rate_medi " b.paid_to_date, unit_value,a.rate_plan_code," ; select count() cnt from j1 into temp check_cnt; "count(),sum(round(agp_p_modx,0)),sum(upr)" "plan_abbr_code,rate_age,rate_sex,rate_occu,'0',face_amt," "from a2 a, prat b" insert into m2 "group by 1,2" "order by 1 desc,4,3,2" "from taupr a,tapo b, cc c" and a.vl_rate_scale=b.vl_rate_scale else agp_p end agp_p_modx from j4 into temp nk1; " a.rate_rate_scale,a.vl_plan_code,a.vl_rate_scale" "select , case when modx='1' then round(agp_p0.08812,0)" select count() cnt from j6 into temp check_upr_cnt; "from SFDR a,dd b" "modx,paid_to_date,unit_value,rate_plan_code,rate_rate_scale," where a.plan_abbr_code in ('GODR') "select ,'112/05/'||co_issue_date[8,9] paid_to_date_n from s43" 112/05 order by 2 ; where a.policy_no=b.policy_no and a.rate_sex=b.sex from j4 into temp j5; "from j1 a, arpo b,dd c,rate_occu_upr d" " when modx='3' then round(agp_p0.2624,0)" "select 'check',cnt from check_cnt" where a.value_date=b.value_date "vl_plan_code,vl_rate_scale," and a.rate_plan_code = b.plan_code "where co_issue_date[5,6] in ('02','05','08','11')" 2023/05/01 and a.sequence=b.sequence and a.rate_age=b.age into temp j5 "select , case when d1 > d2" where a.policy_no=b.policy_no " when modx='6' then round(agp_p0.522,0)" where cnt <>(select cnt from check_upr_cnt); "group by 1,2;" "agp_p,day1,day2,t0,t1,agp_p_modx,upr,'N',' ',''" and a.rate_rate_scale = b.rate_scale "and co_issue_date[8,9]<='31'" 31 and a.value_date=c.value_date and a.dur=b.dur ; "select ," then d2 and a.policy_no=d.policy_no else agp_p end agp_p_modx select count() from j6 from SFDR and a.rate_age = b.rate_age and modx ='3' "and po_sts_code not in ('X','3')" and policy_no in (select policy_no from rpu1) "select ," "round((t0 day1/365(t1-t0))face_amt/unit_value agp_p_modxday2/365,0) upr" else d1 and a.coverage_no=d.coverage_no from j4 where day1<0 or day2<0 insert into yt1 where value_date in ( select value_date from cc) and a.rate_dur = b.rate_dur ; into temp s2; and a.plan_abbr_code not in ('AHAD') "round((t0 day1/365*(t1-t0))face_amt/unit_value agp_p_modxday2/365,0) upr" from j5 end day1 into temp j2; into temp j5; or(paid_to_date<value_date and day2>0); "select plan_abbr_code," ; and a.rate_sex = b.rate_sex insert into m2 ; from j5 into temp j6; from nk1 "(case when dur=1 then 1 else 2 end)," insert into upr_com and a.rate_occu = b.rate_occu "select ,'112/05/31' paid_to_date_n from s43" 112/05/31 delete from s2 insert into j4 into temp j6 into temp j3; "select a., b.rate_medi from j2 a,rsvm b" "select ," insert into SFDR "count(),sum(round(agp_p_modx,0)),sum(upr)" "select value_date,policy_no,co_issue_date,dur,plan_code,rate_scale," ; "where co_issue_date[5,6] in ('02','05','08','11')" where policy_no in(select policy_no from bb); "select a.,b.pd_factor_t0 t0,b.pd_factor_t1 t1" ; --檢核 where a.policy_no=b.policy_no "round((t0 day1/365(t1-t0))face_amt/unit_value agp_p_modxday2/365,0) upr" "select value_date, policy_no, co_issue_date, dur, plan_code, rate_scale," "from SEHI a,dd b" "plan_abbr_code,rate_age,rate_sex,rate_occu,'0',face_amt," "and co_issue_date[8,9]>'31'" 31 "from j3 a,outer arv5 b -->104/01改為arv5" insert into pwpr_v1 select count() cnt from j1 into temp check_cnt; "select a.,b.tv_unit_value_t0 t0,b.tv_unit_value_t1 t1" and a.plan_code=b.plan_code from j5 "plan_abbr_code, rate_age, rate_sex, rate_occu, face_amt, modx," where a.value_date=b.value_date "modx,paid_to_date,unit_value,rate_plan_code,rate_rate_scale," --8. TADDR and modx ='3' "select a., rate_scale" where a.vl_plan_code=b.vl_plan_code "select value_date, policy_no, co_issue_date, dur, plan_code, rate_scale," select count() cnt from j6 into temp check_upr_cnt; "from j3 a,outer arv5 b -->104/01改arv5" and a.rate_scale=b.rate_scale into temp j6; "paid_to_date, unit_value, rate_plan_code, rate_rate_scale, vl_plan_code," "group by 1,2;" "vl_plan_code,vl_rate_scale," insert into a3 ; "from s2 a, outer taco b" and a.vl_rate_scale=b.vl_rate_scale "plan_abbr_code, rate_age, rate_sex, rate_occu, face_amt, modx," "select 'check',cnt from check_cnt" where a.vl_plan_code=b.vl_plan_code into temp j22; "vl_rate_scale, agp_p, day1, day2, t0, t1, agp_p_modx, upr" "agp_p,day1,day2,t0,t1,agp_p_modx,upr,'N',' ',''" "select a., b.prem_factor " insert into m2 where a.policy_no=b.policy_no and a.rate_sex=b.sex "paid_to_date, unit_value, rate_plan_code, rate_rate_scale, vl_plan_code," where cnt <>(select cnt from check_upr_cnt); and a.vl_rate_scale=b.vl_rate_scale select count() cnt from j1 into temp check_cnt; from j6; insert into yt1 from SEHI "from a2 a, prat b" "select ,'112/03/'||co_issue_date[8,9] paid_to_date_n from s43" 112/03 and a.coverage_no=b.coverage_no and a.rate_age=b.age "vl_rate_scale, agp_p, day1, day2, t0, t1, agp_p_modx, upr," select count() from j6 and a.rate_sex=b.sex "select a.,prem_factorface_amt/unit_value agp_p," select count() cnt from j6 into temp check_upr_cnt; "select plan_abbr_code," where value_date in ( select value_date from cc) where a.plan_abbr_code in ('TADDR') "where co_issue_date[5,6] in ('03','06','09','12')" 2023/03/01 and a.sequence=b.sequence and a.dur=b.dur """N"","""" ,""""" where day1<0 or day2<0 and a.rate_age=b.age " case when substr(co_issue_date,5,9)= '02/29'--不動" "select 'check',cnt from check_cnt" "(case when dur=1 then 1 else 2 end)," ; and a.rate_plan_code = b.plan_code "and co_issue_date[8,9]<='31'" 31 into temp s3 and policy_no in (select policy_no from rpu1) from j6 or(paid_to_date<value_date and day2>0); and a.dur=b.dur then where cnt <>(select cnt from check_upr_cnt); "count(),sum(round(agp_p_modx,0)),sum(upr)" insert into upr_com and a.rate_rate_scale = b.rate_scale and modx ='3' ; and b.sts_ind = 0 ; and policy_no not in (select policy_no from rpu1) " datediff_k(value_date,""113/02/29"") -->每年2月結算update成下一年" select count() from j6 "from SFR a,dd b" "select value_date,policy_no,co_issue_date,dur,plan_code,rate_scale," ; ; insert into upr_all and a.plan_abbr_code in ('AHAD') insert into SDL into temp j4; else where day1<0 or day2<0 where a.value_date=b.value_date "plan_abbr_code,rate_age,rate_sex,rate_occu,'0',face_amt," insert into m2 "select d.value_date,d.plan_code,d.rate_scale,e.plan_abbr_code," ; ---------20150924 mod for moses patty lu---43--- "select value_date,policy_no,co_issue_date,dur,plan_code,rate_scale," " datediff_k(value_date," or(paid_to_date<value_date and day2>0); "group by 1,2;" "modx,paid_to_date,unit_value,rate_plan_code,rate_rate_scale," update a3 "select ,'112/03/31' paid_to_date_n from s43" 112/03/31 "case when d.po_issue_date[5,9]<=f.value_date[5,9] then" "select , case when modx='1' then round(agp_p0.08812,0)" "plan_abbr_code,rate_age,rate_sex,rate_occu,rate_medi,face_amt,modx," insert into j4 " round(co_issue_date[1,3] dur-1,0)||co_issue_date[4,9])" "vl_plan_code,vl_rate_scale," set unit_value = '1' "where co_issue_date[5,6] in ('03','06','09','12')" " f.value_date[1,3]-d.po_issue_date[1,3] 1" " when modx='3' then round(agp_p0.2624,0)" "select a.,round((cast(" "paid_to_date,unit_value,rate_plan_code,rate_rate_scale,vl_plan_code," "select a.,b.pd_factor_t0 t0,b.pd_factor_t1 t1" " end d1," insert into SPA insert into yt1 "agp_p,day1,day2,t0,t1,agp_p_modx,upr,'N',' ',''" "where plan_abbr_code in ('BHSR','DHSR','DZHSR')" "and co_issue_date[8,9]>'31'" 31 "else f.value_date[1,3]-d.po_issue_date[1,3] end ," " when modx='6' then round(agp_p0.522,0)" "cast(substr(co_sts_date,1,3) as int) 1911||" "vl_rate_scale,agp_p,day1,day2,t0,t1,agp_p_modx,upr" "from j3 a,outer arv5 b -->104/01改arv5" " case when substr(co_issue_date,5,9)= '02/29'" "select value_date, policy_no, co_issue_date, dur, plan_code, rate_scale," "select plan_abbr_code," from SFR ; and modx ='3' "dept_belong[2] area_code," else agp_p end agp_p_modx "cast(substr(co_sts_date,5,2) as char(2))||" from j6; where a.vl_plan_code=b.vl_plan_code then "plan_abbr_code, rate_age, rate_sex, rate_occu, face_amt, modx," "(case when dur=1 then 1 else 2 end)," where value_date in ( select value_date from cc) ; "count()cnt,sum(d.receive_prem) agp_upr,sum(upr) upr, currency,'Y'," from j4 "cast(substr(co_sts_date,8,2) as char(2)) as date)-" and a.vl_rate_scale=b.vl_rate_scale " datediff_k(""112/02/28"",""113/02/29"")" "paid_to_date, unit_value, rate_plan_code, rate_rate_scale, vl_plan_code," "count(),sum(round(agp_p_modx,0)),sum(upr)" ; "select , " "insert into upr43_cnt values('m2',(select count() from m2));" "'T',d.po_issue_date[1,6]" into temp j5; cast( and a.rate_sex=b.sex else "vl_rate_scale, agp_p, day1, day2, t0, t1, agp_p_modx, upr" "from CYDL a,dd b" insert into upr_com "case when plan_abbr_code in ('MJWPR','MSWPR')" "from tapo a,aaor b,dept c,s3 d, outer pldf e, cc f" "cast(substr(co_issue_date,1,3) as int) 1911||" and a.rate_age=b.age " datediff_k(round(co_issue_date[1,3] dur,0)||co_issue_date[4,9]," from j6 where a.value_date=b.value_date "select value_date,policy_no,co_issue_date,dur,plan_code,rate_scale," " then round(face_amt/unit_valueprem_factor,2) " --半年繳 where a.ta_agent_1=b.agent_code "select ," "cast(substr(co_issue_date,5,2) as char(2))||" and a.dur=b.dur " round(co_issue_date[1,3] dur-1,0)||co_issue_date[4,9]) " where plan_abbr_code in ('SPA'); "group by 1,2;" "plan_abbr_code,rate_age,rate_sex,rate_occu,'0',face_amt," " else round(face_amt/unit_valueprem_factor,0) " "select ,'112/07/'||co_issue_date[8,9] paid_to_date_n from s43" 112/07 and b.dept_code=c.dept_code "round((t0 day1/365(t1-t0))face_amt/unit_value agp_p_modxday2/365,0) upr" "cast(substr(co_issue_date,8,2) as char(2)) as date))/365 0.5,0)" and policy_no in (select policy_no from rpu1) " end d2," "modx,paid_to_date,unit_value,rate_plan_code,rate_rate_scale," end as new_agp "where co_issue_date[5,6] in ('01','07')" "and b.prod_ym=f.value_date[1,6]" from j5 as inact_occu_dur ; case when paid_to_date>value_date insert into SFR insert into yt1 "vl_plan_code,vl_rate_scale," from a3 and modx ='6' and a.policy_no=d.policy_no into temp j6; from arco a "select , case when modx='1' then round(agp_p0.08812,0)" then "select value_date, policy_no, co_issue_date, dur, plan_code, rate_scale," "select plan_abbr_code," "agp_p,day1,day2,t0,t1,agp_p_modx,upr,'N',' ',''" into temp a4; into temp m3 and a.sequence=d.sequence where plan_abbr_code='PWPR' " when modx='3' then round(agp_p0.2624,0)" " datediff_k( paid_to_date, value_date)" "plan_abbr_code, rate_age, rate_sex, rate_occu, face_amt, modx," "(case when dur=1 then 1 else 2 end)," from CYDL ; and d.plan_code =e.plan_code select count() cnt from j1 into temp check_cnt; and co_sts_code='43' " when modx='6' then round(agp_p0.522,0)" else 0 end day2 "paid_to_date, unit_value, rate_plan_code, rate_rate_scale, vl_plan_code," "count(),sum(round(agp_p_modx,0)),sum(upr)" where value_date in ( select value_date from cc) "select value_date, policy_no, coverage_no, plan_abbr_code, paid_to_date, currency," insert into m3 and d.rate_scale=e.rate_scale select count() cnt from j6 into temp check_upr_cnt; into temp s1 else agp_p end agp_p_modx "from j22 a, outer pra2 b" "vl_rate_scale, agp_p, day1, day2, t0, t1, agp_p_modx, upr" "from NSEHI a,dd b" ; "new_agp as agp, datediff_k(value_date, paid_to_date) as day" "select ,'112/08/'||co_issue_date[8,9] paid_to_date_n from s43" 112/08 "group by 1,2,3,4,5,6,10,11,12,13" "select 'check',cnt from check_cnt" ; from j4 where a.rate_plan_code=b.plan_code from j6 where a.value_date=b.value_date insert into upr_com from a4 "where co_issue_date[5,6] in ('02','08')" 2023/08/01 "order by 1,2,3,4,5,6,10,11,12,13" where cnt <>(select cnt from check_upr_cnt); "select a.,""Y""as inactive_ind,rate_age inact_occu_dur-1 as inact_age" into temp j5; and a.rate_rate_scale=b.rate_scale where plan_abbr_code in ('SFR'); "group by 1,2;" "select value_date,policy_no,co_issue_date,dur,plan_code,rate_scale," into temp a5; and modx ='6' ; select count() from j6 from s1 a and a.rate_age=b.rate_age "plan_abbr_code,rate_age,rate_sex,rate_occu,'0',face_amt," "and co_issue_date[8,9]<='31'" 31 update upr_all where day1<0 or day2<0 into temp s2 "select ," and a.rate_sex=b.rate_sex insert into SEHI unload to 'comp.txt' "modx,paid_to_date,unit_value,rate_plan_code,rate_rate_scale," "select value_date, policy_no, coverage_no, plan_abbr_code, paid_to_date, currency," ; set plan_abbr_code=plan_code or(paid_to_date<value_date and day2>0); ; "round((t0 day1/365(t1-t0))face_amt/unit_value agp_p_modxday2/365,0) upr" and a.rate_medi=b.rate_medi "select value_date, policy_no, co_issue_date, dur, plan_code, rate_scale," select * from yt1 "vl_plan_code,vl_rate_scale," "agp," insert into m3 where plan_abbr_code is null ---暫時修改 from j5 --and a.rate_occu=b.rate_occu --KAD保費不受職業等級影響 "plan_abbr_code, rate_age, rate_sex, rate_occu, face_amt, modx," "order by 1,2;" "agp_p,day1,day2,t0,t1,agp_p_modx,upr,'N',' ',''" case when currency in ('USD') "select ,'112/08/31' paid_to_date_n from s43" 112/08/31 ; insert into JPA "select c.value_date,a.policy_no,a.co_issue_date," into temp j6; into temp nk1; "paid_to_date, unit_value, rate_plan_code, rate_rate_scale, vl_plan_code," from NSEHI " then round(day/365agp,2)" "where co_issue_date[5,6] in ('02','08')" update upr_all "select value_date, policy_no, co_issue_date, dur, plan_code, rate_scale," case --檢核 "vl_rate_scale, agp_p, day1, day2, t0, t1, agp_p_modx, upr" where value_date in ( select value_date from cc) " else round(day/365agp,0) end upr" and modx ='6' set currency='TWD' "plan_abbr_code, rate_age, rate_sex, rate_occu, face_amt, modx," " when cast(d.co_issue_date[5,6] as int) >cast(base_date[5,6] as int) or" select count() cnt from j1 into temp check_cnt; "select , case when d1 > d2" from j6 ; from a5 "and co_issue_date[8,9]>'31'" 31 where plan_abbr_code ='OHS' "paid_to_date, unit_value, rate_plan_code, rate_rate_scale, vl_plan_code," " (cast(d.co_issue_date[5,6] as int) =cast(base_date[5,6] as int) and" select count() cnt from j6 into temp check_upr_cnt; then d2 where plan_abbr_code in ('SEHI'); insert into upr_com into temp f1; ; ; "vl_rate_scale, agp_p, day1, day2, t0, t1, agp_p_modx, upr" " cast(d.co_issue_date[8,9] as int) >cast(base_date[8,9] as int) )" "select 'check',cnt from check_cnt" else d1 "select value_date,policy_no,po_issue_date,' ',plan_code,rate_scale," insert into m3 drop table s2 from j6 then where cnt <>(select cnt from check_upr_cnt); end day1 insert into NSEHI "plan_abbr_code,rate_age,rate_sex,'0','0',face_amt," select count() from k1; "select ,'112/03/'||co_issue_date[8,9] paid_to_date_n from s43" 112/03 ; "where plan_abbr_code in ('JPA','JSA');" " cast(base_date[1,3] as int)-cast(d.co_issue_date[1,3] as int) " select count() from j6 from nk1 "select value_date, policy_no, co_issue_date, dur, plan_code, rate_scale," "modx,paid_to_date,unit_value,' ',' '," select count() from f1; "where co_issue_date[5,6] in ('03','09')" 2023/03/01 drop table s3 else where day1<0 or day2<0 into temp j3; "plan_abbr_code, rate_age, rate_sex, rate_occu, face_amt, modx," "'','',--vl_plan_code,vl_rate_scale," "and co_issue_date[8,9]<='31'" 31 ; insert into JPAT " cast(base_date[1,3] as int)-cast(d.co_issue_date[1,3] as int) 1" or(paid_to_date<value_date and day2>0); "paid_to_date, unit_value, rate_plan_code, rate_rate_scale, vl_plan_code," "agp_o,0,days,' ',' ',agp,upr,'N',' ',''" select distinct plan_abbr_code from k1 and modx ='6' ------------------------------------------------------------ "select value_date, policy_no, co_issue_date, dur, plan_code, rate_scale," " end dur," "select a.,b.tv_unit_value_t0 t0,b.tv_unit_value_t1 t1" "vl_rate_scale, agp_p, day1, day2, t0, t1, agp_p_modx, upr" from aed_ked where policy_no||coverage_no not in ( ; --7.有造冊的團險 "plan_abbr_code, rate_age, rate_sex, rate_occu, face_amt, modx," " a.plan_code, a.rate_scale, a.plan_abbr_code," insert into SPTL "from j3 a,outer arv5 b" from j6 where value_date in ( select value_date from cc) select policy_no||coverage_no from f1) insert into m3 "select a.,dept_code, po_issue_date" "paid_to_date, unit_value, rate_plan_code, rate_rate_scale, vl_plan_code," " a.rate_age, a.rate_sex,d.rate_occu,a.face_amt, a.modx," "select value_date,policy_no,co_issue_date,dur,plan_code,rate_scale," where a.vl_plan_code=b.vl_plan_code where plan_abbr_code in ('NSEHI'); ; and 1 = 0; "select ,'112/03/31' paid_to_date_n from s43" 112/03/31 "from gico_upr a ,gipo b, cc c" "vl_rate_scale, agp_p, day1, day2, t0, t1, agp_p_modx, upr" " b.paid_to_date, unit_value,a.rate_plan_code," "plan_abbr_code,rate_age,rate_sex,rate_occu,rate_medi,face_amt,modx," and a.vl_rate_scale=b.vl_rate_scale "where co_issue_date[5,6] in ('03','09')" where a.policy_no=b.policy_no from j6 " a.rate_rate_scale,a.vl_plan_code,a.vl_rate_scale" "paid_to_date,unit_value,rate_plan_code,rate_rate_scale,vl_plan_code," and a.rate_sex=b.sex insert into upr_fycheck "and co_issue_date[8,9]>'31'" 31 and a.value_date=c.value_date where plan_abbr_code in ('JPAT'); "from j1 a, arpo b,dd c,arco d, rsvm e" "vl_rate_scale,agp_p,day1,day2,t0,t1,agp_p_modx,upr" and a.rate_age=b.age "select value_date, policy_no, coverage_no, plan_abbr_code, currency, agp, upr" and modx ='6' into temp s2; where a.policy_no in (select policy_no from s1) from j6 and a.dur=b.dur from f1; ; "select a.,dept_belong from s2 a,dept b" insert into AHAD and a.policy_no=b.policy_no where plan_abbr_code in ('SPTL'); and b.sts_ind = '0' insert into m3 where a.dept_code=b.dept_code "select value_date, policy_no, co_issue_date, dur, plan_code, rate_scale," and a.policy_no=d.policy_no and a.policy_no not in ( unload to 'upr_fycheck.txt' "select ,'112/04/'||co_issue_date[8,9] paid_to_date_n from s43" 112/04 into temp s3; "plan_abbr_code, rate_age, rate_sex, rate_occu, face_amt, modx," and a.coverage_no=d.coverage_no insert into SFDL select policy_no from b1) "select value_date, policy_no, coverage_no, currency," "where co_issue_date[5,6] in('04','10')" 2023/04/01 { "paid_to_date, unit_value, rate_plan_code, rate_rate_scale, vl_plan_code," and a.policy_no=e.policy_no "select value_date,policy_no,co_issue_date,dur,plan_code,rate_scale," into temp j4; "agp, paid_to_date, upr" and modx ='6' update s3 "vl_rate_scale, agp_p, day1, day2, t0, t1, agp_p_modx, upr" and '1'=e.coverage_no "plan_abbr_code,rate_age,rate_sex,rate_occu,rate_medi,face_amt,modx," from f1; "and co_issue_date[8,9]<='30'" 30 "set upr=round(upr1.6939,0) ---一年更新一次upr.xls[INPUT]" from j6 into temp j22; "paid_to_date,unit_value,rate_plan_code,rate_rate_scale,vl_plan_code," insert into j4 where value_date in ('112/02/28') ; where insurance_type='D' where plan_abbr_code in ('AHAD'); "vl_rate_scale,agp_p,day1,day2,t0,t1,agp_p_modx,upr" "select a.,b.tv_unit_value_t0 t0,b.tv_unit_value_t1 t1" and 1 = 1 insert into m3 ; ----------------------change-paid_to_date------------ from j6 "from j3 a,outer arv5 b" "select ,'112/04/30' paid_to_date_n from s43" 112/04/30 --Q:為什麼要更新PTD? where plan_abbr_code in ('SFDL'); where a.vl_plan_code=b.vl_plan_code "where co_issue_date[5,6] in('04','10')" update s3 update j22 and a.vl_rate_scale=b.vl_rate_scale "and co_issue_date[8,9]>'30'" 30 "set upr=round(upr1.3335,0) ---一年更新一次upr.xls[INPUT]" set paid_to_date=( insert into SHDL and a.rate_sex=b.sex and modx ='6' "where insurance_type in ('P','Z')" "case when co_issue_date[5,9]>value_date[5,9]" "select value_date,policy_no,co_issue_date,dur,plan_code,rate_scale," and a.rate_age=b.age ; ; and modx ='12' "plan_abbr_code,rate_age,rate_sex,rate_occu,rate_medi,face_amt,modx," and a.dur=b.dur insert into m3 update s3 " then '112/'||co_issue_date[5,9]" "paid_to_date,unit_value,rate_plan_code,rate_rate_scale,vl_plan_code," and a.policy_no in ( "select ,'112/05/'||co_issue_date[8,9] paid_to_date_n from s43" 112/05 "set upr=round(upr1.4720,0) ---一年更新一次upr.xls[INPUT]" "vl_rate_scale,agp_p,day1,day2,t0,t1,agp_p_modx,upr" select policy_no from b1) "where co_issue_date[5,6] in('05','11')" 2023/05/01 where insurance_type in ('H') " when co_issue_date[5,9] between '01/01' and value_date[5,9]" from j6 and b.sts_ind = '1'; "and co_issue_date[8,9]<='31'" 31 ; and modx ='12' where plan_abbr_code in ('SHDL'); and modx ='6' } " and co_issue_date[5,9]<>'02/29'" "select , case when modx='1' then round(agp_p0.08812,0)" ; " then '113/'||co_issue_date[5,9]" insert into JYL " when modx='3' then round(agp_p0.2624,0)" insert into m3 insert into upr_all "select value_date,policy_no,co_issue_date,dur,plan_code,rate_scale," " when modx='6' then round(agp_p0.522,0)" "select ,'112/05/31' paid_to_date_n from s43" 112/05/31 "select a.value_date,plan_code,rate_scale,plan_code," " when co_issue_date[5,9]='02/29' and modx ='12'" "plan_abbr_code,rate_age,rate_sex,rate_occu,rate_medi,face_amt,modx," else agp_p end agp_p_modx "where co_issue_date[5,6] in ('05','11')" "case when a.po_issue_date[5,6]<=b.value_date[5,6] then" " then ""113/02/29""" "paid_to_date,unit_value,rate_plan_code,rate_rate_scale,vl_plan_code," from j4 "and co_issue_date[8,9]>'31'" 31 " b.value_date[1,3]-a.po_issue_date[1,3] 1" "vl_rate_scale,agp_p,day1,day2,t0,t1,agp_p_modx,upr" into temp j5; and modx ='6' "else b.value_date[1,3]-a.po_issue_date[1,3] end," --季繳 from j6 ; "dept_belong[2] area_code," " when co_issue_date[5,6] in ('01','04','07','10')" "where plan_abbr_code in ('JYL', 'PWL');" "select ," insert into m3 "count()cnt,sum(agp) agp_upr,sum(upr) upr,'TWD','N',effect_date[1,6]" " and modx ='3' and co_issue_date[8,9]<='30'" 30 "round((t0 day1/365(t1-t0))face_amt/unit_value agp_p_modxday2/365,0) upr" "select ,'112/06/'||co_issue_date[8,9] paid_to_date_n from s43" 112/06 "insurance_type, po_issue_date[1,6]" " then '112/04/'||co_issue_date[8,9]" 112/04 insert into CATL from j5 "where co_issue_date[5,6] in ('06','12')" 2023/06/01 "from s3 a, cc b" "select value_date,policy_no,co_issue_date,dur,plan_code,rate_scale," into temp j6; "and co_issue_date[8,9]<='30'" 30 "group by 1,2,3,4,5,6,10,11,12,13" " when co_issue_date[5,6] in ('01','04','07','10')" "plan_abbr_code,rate_age,rate_sex,rate_occu,rate_medi,face_amt,modx," and modx ='6' "order by 1,2,3,4,5,6,10,11,12,13" " and modx ='3' and co_issue_date[8,9]>'30'" 30 "paid_to_date,unit_value,rate_plan_code,rate_rate_scale,vl_plan_code," select count() cnt from j1 into temp check_cnt; ; then '112/04/30' 112/04/30 "vl_rate_scale,agp_p,day1,day2,t0,t1,agp_p_modx,upr" select count() cnt from j6 into temp check_upr_cnt; insert into m3 from j6 "select ,'112/06/30' as paid_to_date_n from s43" 112/06/30 " when co_issue_date[5,6] in ('02','05','08','11')" where plan_abbr_code in ('CATL'); "select 'check',cnt from check_cnt" "where co_issue_date[5,6] in ('06','12')" " and co_issue_date[8,9]<='31'" 31 where cnt <> (select cnt from check_upr_cnt); "and co_issue_date[8,9]>'30'" 30 and modx ='3' insert into CYDL and modx ='6' " then '112/05/'||co_issue_date[8,9]" 112/05 "select value_date,policy_no,co_issue_date,dur,plan_code,rate_scale," "select policy_no, plan_abbr_code" ; "plan_abbr_code,rate_age,rate_sex,rate_occu,rate_medi,face_amt,modx," from j6 "insert into upr43_cnt values('m3',(select count() from m3));" " when co_issue_date[5,6] in ('02','05','08','11')" "paid_to_date,unit_value,rate_plan_code,rate_rate_scale,vl_plan_code," where policy_no not in ( 步驟: " and co_issue_date[8,9]>'31'" 31 "vl_rate_scale,agp_p,day1,day2,t0,t1,agp_p_modx,upr" select policy_no from j1) --月繳 2023/02/01 1.決算日(西元) and modx ='3' from j6 into temp f1; "select ,'112/03'||'/'||co_issue_date[8,9] paid_to_date_n" 112/03 3.下月(年月補齊) then '112/05/31' 112/05/31 where plan_abbr_code in ('CYDL'); from s43 112/3 2.下月(年月) select count() from f1 where modx=1 " when co_issue_date[5,6] in ('03','06','09','12')" where policy_no in ( "and co_issue_date[8,9]<='31'" 31 下月底(日) " and co_issue_date[8,9]<='31'" 31 select policy_no from b1); into temp m4 and modx ='3' ; " then '112/03/'||co_issue_date[8,9]" 112/03 insert into KAD insert into m4 "select value_date,policy_no,co_issue_date,dur,plan_code,rate_scale," "select ,'112/03/31' paid_to_date_n" 112/03/31 " when co_issue_date[5,6] in ('03','06','09','12')" "plan_abbr_code,rate_age,rate_sex,rate_occu,rate_medi,face_amt,modx," from s43 " and co_issue_date[8,9]>'31'" 31 "paid_to_date,unit_value,rate_plan_code,rate_rate_scale,vl_plan_code," where modx=1 and modx ='3' "vl_rate_scale,agp_p,day1,day2,t0,t1,agp_p_modx,upr" "and co_issue_date[8,9]>'31'" 31 then '112/03/31' 112/03/31 from j6; ; --半年繳 " when co_issue_date[5,6] in ('01','07') and modx ='6'" "insert into upr43_cnt values('m4',(select count() from m4));" " then '112/07/'||co_issue_date[8,9]" 112/07

select * from m1 " when co_issue_date[5,6] in ('02','08') --august mod for feb" into temp n1 " and co_issue_date[8,9]<='31' and modx ='6'" 31 ; " then '112/08/'||co_issue_date[8,9]" 112/08 insert into n1 select * from m2 " when co_issue_date[5,6] in ('02','08') --august mod for feb" ; " and co_issue_date[8,9]>'31' and modx ='6'" 31 insert into n1 then '112/08/31' 112/08/31 select * from m3 ; " when co_issue_date[5,6] in ('03','09')" insert into n1 " and co_issue_date[8,9]<='31' and modx ='6'" 31 select * from m4; " then '112/03/'||co_issue_date[8,9]" 112/03

"insert into upr43_cnt values('n1',(select count() from n1));" " when co_issue_date[5,6] in ('03','09')" " and co_issue_date[8,9] >'31' and modx ='6'" 31 --check ptd的合理性 then '112/03/' 112/03/ "select * from n1,cc" where paid_to_date_n<value_date " when co_issue_date[5,6] in('04','10')" ; " and co_issue_date[8,9]<='30' and modx ='6'" 30 --check是否有重複保單__1080209新增 " then '112/04/'||co_issue_date[8,9]" 112/04 "select policy_no, coverage_no, count() cnt from n1" "group by 1,2" " when co_issue_date[5,6] in('04','10')" into temp ck1; " and co_issue_date[8,9]>'30' and modx ='6'" 30 select * from ck1 then '112/04/' 112/04/ where cnt>1 ; " when co_issue_date[5,6] in('05','11')" "select a., datediff_k(paid_to_date_n, value_date) days --update" " and co_issue_date[8,9]<='31' and modx ='6'" 31 "from n1 a, cc b" " then '112/05/'||co_issue_date[8,9]" 112/05 into temp nn1 ; " when co_issue_date[5,6] in ('05','11')" "--check days的合理性,估算而已" " and co_issue_date[8,9]>'31' and modx ='6'" 31 "select modx, days," then '112/05/' 112/05/ * from nn1 " when co_issue_date[5,6] in ('06','12')" where (days>modx31 and modx<>12) or(modx=12 and days>365) " and co_issue_date[8,9]<='30' and modx ='6'" 30 ; " then '112/06/'||co_issue_date[8,9]" 112/06

"select ,round(agpdays/365,0) upr" " when co_issue_date[5,6] in ('06','12')" from nn1 " and co_issue_date[8,9] >'30' and modx ='6'" 30 into temp n2 then '112/06/30' 112/06/30 ; --月繳 " when modx=1 and co_issue_date[8,9]<='31'" 31 delete from n2 " then '112/03/'||co_issue_date[8,9]" 112/03 "where plan_abbr_code in ('JWPR','SWPR','OWPR','ZWPR')" and co_sts_code = '43' " when modx=1 and co_issue_date[8,9]>'31'" 31 ; then '112/03/31' --update 112/03/31

insert into upr43 else '999/12/31' end ) where 1=1 "select value_date, a." ; "from n2 a, cc b" ; "select a.,prem_factorface_amt/unit_value agp_p," " case when substr(co_issue_date,5,9)= '02/29'" unload to 'upr_43.txt' then select case when co_issue_date> " datediff_k(value_date,""113/02/29"") -->每年2月結算update成下一年" " cast(value_date[1,3]-1 as char(3))||""/""||value_date[5,9]" else then 1 " datediff_k(value_date," " else 2 end FY_RY," " round(co_issue_date[1,3] dur-1,0)||co_issue_date[4,9])" " plan_abbr_code," " end d1," "count(), sum(round(agp,0)), sum(upr)" " case when substr(co_issue_date,5,9)= '02/29'" "from n2 a, cc b" then "group by 1, 2" " datediff_k(""112/02/28"",""113/02/29"")" "order by 2, 1;" else " datediff_k(round(co_issue_date[1,3] dur,0)||co_issue_date[4,9]," " round(co_issue_date[1,3] dur-1,0)||co_issue_date[4,9]) " " end d2," case when paid_to_date>value_date then " datediff_k( paid_to_date, value_date)" else 0 end day2 "from j22 a, outer pra2 b" where a.rate_plan_code=b.plan_code and a.rate_rate_scale=b.rate_scale and a.rate_age=b.rate_age and a.rate_sex=b.rate_sex and a.rate_occu=b.rate_occu into temp nk2;

																												"select *, case when d1 > d2"																																			
																												              then d2																																			
																												              else d1																																			
																												            end day1																																			
																												from nk2																																			
																												into temp j33																																			
																												;																																			
																																																															
																												select a.* from j33 a																																			
																												into temp k1																																			
																																																															
																												;																																			
																																																															
																												--update K1																																			
																												--set t0=16988.6   ----ardb找數值																																			
																												"select a.*,c.tv_factor t0"																																			
																												"from k1 a,s2 b,pldf d,arda e,outer ardb c"																																			
																												where a.policy_no=b.policy_no																																			
																												and   a.plan_code=d.plan_code																																			
																												and   a.rate_scale=d.rate_scale																																			
																												and   c.db_plan_code = e.db_plan_code																																			
																												and   c.db_rate_scale = e.db_rate_scale																																			
																												and d.plan_code = e.plan_code																																			
																												and d.rate_scale = e.rate_scale																																			
																												and  a.rate_sex=c.rate_sex																																			
																												and  b.wp_ind=c.sts_ind																																			
																												and  c.age=b.rate_age b.inact_occu_dur-1																																			
																												and  c.inact_dur=b.dur-b.inact_occu_dur																																			
																												and  c.wop_year=d.premium_year_dur-b.dur 1																																			
																												into temp k2;																																			
																																																															
																												"select a.*,c.tv_factor t1"																																			
																												"from k2 a,s1 b,pldf d,arda e,outer ardb c"																																			
																												where a.policy_no=b.policy_no																																			
																												and   a.plan_code=d.plan_code																																			
																												and   a.rate_scale=d.rate_scale																																			
																												and   c.db_plan_code = e.db_plan_code																																			
																												and   c.db_rate_scale = e.db_rate_scale																																			
																												and d.plan_code = e.plan_code																																			
																												and d.rate_scale = e.rate_scale																																			
																												and  a.rate_sex=c.rate_sex																																			
																												and  b.wp_ind=c.sts_ind																																			
																												and  c.age=b.rate_age b.inact_occu_dur-1																																			
																												and  c.inact_dur=b.dur-b.inact_occu_dur 1																																			
																												and  c.wop_year=d.premium_year_dur-b.dur																																			
																												into temp k3;																																			
																																																															
																												"select *, case when modx='1' then round(agp_p*0.088*12,0)"																																			
																												"            when modx='3' then    round(agp_p*0.262*4,0)"																																			
																												"            when modx='6' then    round(agp_p*0.52*2,0)"																																			
																												            else agp_p end agp_p_modx																																			
																												from k3																																			
																												into temp k4;																																			
																																																															
																																																															
																												--update k1																																			
																												--set t1=16175.4																																			
																																																															
																												"select a.*,"																																			
																												"round((a.t0 a.day1/365*(a.t1-a.t0))*a.face_amt/a.unit_value agp_p_modx*day2/365,0) upr,"																																			
																												"b.inactive_ind,b.inact_age,b.inact_occu_dur"																																			
																												"from k4 a ,outer s2 b"																																			
																												where a.policy_no=b.policy_no																																			
																												and  a.plan_code=b.plan_code																																			
																												and  a.rate_scale=b.rate_scale																																			
																												into temp k5;																																			
																																																															
																												insert into pwpr_v1																																			
																												"select value_date, policy_no, co_issue_date, dur, plan_code, rate_scale,"																																			
																												"plan_abbr_code, rate_age, rate_sex, rate_occu, face_amt, modx,"																																			
																												"paid_to_date, unit_value, rate_plan_code, rate_rate_scale, vl_plan_code,"																																			
																												"vl_rate_scale, agp_p, day1, day2, t0, t1, agp_p_modx, upr,"																																			
																												"inactive_ind,inact_age,inact_occu_dur"																																			
																												from k5;																																			
																																																															
																																																															
																												------------------------------PWPR_繳費期滿_150924------																																			
																																																															
																																																															
																												"select a.*, b.unit_value,b.rate_plan_code,b.rate_rate_scale,"																																			
																												"       b.vl_plan_code,b.vl_rate_scale"																																			
																												"from arkt a, pldf b"																																			
																												where a.plan_code=b.plan_code																																			
																												and a.rate_scale=b.rate_scale																																			
																												and a.plan_abbr_code in ('PWPR')																																			
																												into temp ja1;																																			
																																																															
																												--暫時修改(應該也包含沒繳錢的但還在寬限期)																																			
																												"select c.value_date,a.policy_no,a.co_issue_date,"																																			
																												      case																																			
																												"      when cast(d.co_issue_date[5,6] as int) >cast(base_date[5,6] as int) or"																																			
																												"            (cast(d.co_issue_date[5,6] as int) =cast(base_date[5,6] as int) and"																																			
																												"              cast(d.co_issue_date[8,9] as int) >=cast(base_date[8,9] as int) )"																																			
																												      then																																			
																												"          cast(base_date[1,3] as int)-cast(d.co_issue_date[1,3] as int) "																																			
																												         else																																			
																												"           cast(base_date[1,3] as int)-cast(d.co_issue_date[1,3] as int) 1"																																			
																												"      end dur,"																																			
																												"       a.plan_code, a.rate_scale, a.plan_abbr_code,"																																			
																												"       a.rate_age, a.rate_sex,d.rate_occu,a.face_amt, a.modx,"																																			
																												"       b.paid_to_date, unit_value,a.rate_plan_code,"																																			
																												"       a.rate_rate_scale,a.vl_plan_code,a.vl_rate_scale"																																			
																												"from ja1 a, arpo b,dd c,arco d, rsvm e"																																			
																												where a.policy_no=b.policy_no																																			
																												and a.policy_no=d.policy_no																																			
																												and a.coverage_no=d.coverage_no																																			
																												and b.paid_to_date<=c.value_date																																			
																												and d.co_sts_code!='43'																																			
																												and a.policy_no=e.policy_no																																			
																												and '1'=e.coverage_no																																			
																												into temp ja2																																			
																												;																																			
																																																															
																												"select a.*,prem_factor*face_amt/unit_value agp_p,"																																			
																												"         case when substr(co_issue_date,5,9)= '02/29'"																																			
																												           then																																			
																												"            datediff_k(value_date,""113/02/29"")  -->每年2月結算update成下一年"																																			
																												           else																																			
																												"             datediff_k(value_date,"																																			
																												"              round(co_issue_date[1,3] dur-1,0)||co_issue_date[4,9])"																																			
																												"         end d1,"																																			
																												"         case when substr(co_issue_date,5,9)= '02/29'"																																			
																												          then																																			
																												"            datediff_k(""112/02/28"",""113/02/29"")"																																			
																												           else																																			
																												"            datediff_k(round(co_issue_date[1,3] dur,0)||co_issue_date[4,9],"																																			
																												"             round(co_issue_date[1,3] dur-1,0)||co_issue_date[4,9]) "																																			
																												"           end d2,"																																			
																												       case when paid_to_date>value_date																																			
																												       then																																			
																												"         datediff_k( paid_to_date, value_date)"																																			
																												       else 0 end day2																																			
																												"from ja2 a, outer pra2 b"																																			
																												where a.rate_plan_code=b.plan_code																																			
																												and a.rate_rate_scale=b.rate_scale																																			
																												and a.rate_age=b.rate_age																																			
																												and a.rate_sex=b.rate_sex																																			
																												and a.rate_occu=b.rate_occu																																			
																												into temp nk3;																																			
																																																															
																												"select *, case when d1 > d2"																																			
																												              then d2																																			
																												              else d1																																			
																												            end day1																																			
																												from nk3																																			
																												into temp ja3;																																			
																																																															
																												"select a.*,b.tv_unit_value_t0 t0,b.tv_unit_value_t1 t1"																																			
																												"from ja3 a,outer arv5 b"																																			
																												where a.vl_plan_code=b.vl_plan_code																																			
																												and a.vl_rate_scale=b.vl_rate_scale																																			
																												and a.rate_sex=b.sex																																			
																												and a.rate_age=b.age																																			
																												and a.dur=b.dur																																			
																												into temp ja4;																																			
																												"select *, case when modx='1' then round(agp_p*0.088*12,0)"																																			
																												"            when modx='3' then    round(agp_p*0.262*4,0)"																																			
																												"            when modx='6' then    round(agp_p*0.52*2,0)"																																			
																												            else agp_p end agp_p_modx																																			
																												from ja4																																			
																												into temp ja5;																																			
																												"select *,"																																			
																												"round((t0 day1/365*(t1-t0))*face_amt/unit_value agp_p_modx*day2/365,0) upr"																																			
																												from ja5																																			
																												into temp ja6;																																			
																																																															
																												insert into pwpr_v1																																			
																												"select value_date, policy_no, co_issue_date, dur, plan_code, rate_scale,"																																			
																												"plan_abbr_code, rate_age, rate_sex, rate_occu, face_amt, modx,"																																			
																												"paid_to_date, unit_value, rate_plan_code, rate_rate_scale, vl_plan_code,"																																			
																												"vl_rate_scale, agp_p, day1, day2, t0, t1, agp_p_modx, upr,"																																			
																												"""N"","""" ,"""""																																			
																												from ja6;