po_ebps_lifecost

/****** Object:  StoredProcedure [dbo].[po_ebps_lifecost]    Script Date: 03/07/2013 11:34:13 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
/****** Object:  Stored Procedure dbo.po_ebps_lifecost    Script Date: 5/18/2004 7:24:48 PM ******/
/****** Object:  Stored Procedure dbo.po_ebps_lifecost    Script Date: 5/23/02 2:43:52 PM ******/
/****** Object:  Stored Procedure dbo.po_ebps_lifecost    Script Date: 4/30/02 9:52:18 AM ******/
/****** Object:  Stored Procedure dbo.po_ebps_lifecost    Script Date: 3/15/02 2:07:30 PM ******/
/****** Object:  Stored Procedure dbo.po_ebps_lifecost    Script Date: 3/6/01 12:02:57 PM ******/
/****** SQL Server upgrade (2K to 2K8) by John 10/09/2013 ******/
create proc [dbo].[po_ebps_lifecost]
(@compcode char(7),@verno char(1),@life_uwver char(1) )
as
/*
created by: Wincent Xiao 12/10/2000
*/
 begin tran
 
 delete from t_ps_life1
 if @@error<>0
 begin
  rollback tran
  return
 end
 delete from t_ps_life2
 if @@error<>0
 begin
  rollback tran
  return
 end
/* Life */
 insert t_ps_life1(comp_code,ver_no,prod_name,num_emp,tot_sa)
  select comp_code,ver_no,"LIFE",count(*),sum(convert(decimal(14,0),life_sum_assu)) from t_wp_emp
  where comp_code=@compcode and ver_no=@verno and life_sum_assu>0
  group by comp_code,ver_no
  order by comp_code,ver_no /*Add by John 10/09/2013*/
 if @@error<>0
 begin
  rollback tran
  return
 end
 /*update t_ps_life1*/
 update a   /*Add by John 10/09/2013*/
         /*set ann_rate=b.sa_gtl_rate,nel=b.sa_gtl_nel*/
         set a.ann_rate=b.sa_gtl_rate,a.nel=b.sa_gtl_nel
  from t_ps_life1 a,t_wp_life_underwriting b
  where a.comp_code=@compcode and a.ver_no=@verno and a.prod_name="LIFE" and 
   b.comp_code=@compcode and b.ver_no=@verno and b.uwver_no=@life_uwver
 if @@error<>0
 begin
  rollback tran
  return
 end
/* ADDL */
 insert t_ps_life1(comp_code,ver_no,prod_name,num_emp,tot_sa)
  select comp_code,ver_no,"ADD(L)",count(*),sum(convert(decimal(14,0),addl_sum_assu)) from t_wp_emp
  where comp_code=@compcode and ver_no=@verno and addl_sum_assu>0
  group by comp_code,ver_no
  order by comp_code,ver_no /*Add by John 10/09/2013*/
 if @@error<>0
 begin
  rollback tran
  return
 end
 /*update t_ps_life1*/
 update a   /*Add by John 10/09/2013*/
         /*set ann_rate=b.sa_add_rate,nel=b.sa_add_nel*/
         set a.ann_rate=b.sa_add_rate,a.nel=b.sa_add_nel
  from t_ps_life1 a,t_wp_life_underwriting b
  where a.comp_code=@compcode and a.ver_no=@verno and a.prod_name="ADD(L)" and 
   b.comp_code=@compcode and b.ver_no=@verno and b.uwver_no=@life_uwver
 if @@error<>0
 begin
  rollback tran
  return
 end
/* ADDS */
 insert t_ps_life1(comp_code,ver_no,prod_name,num_emp,tot_sa)
  select comp_code,ver_no,"ADD(S)",count(*),sum(convert(decimal(14,0),adds_sum_assu)) from t_wp_emp
  where comp_code=@compcode and ver_no=@verno and adds_sum_assu>0
  group by comp_code,ver_no
  order by comp_code,ver_no /*Add by John 10/09/2013*/
 if @@error<>0
 begin
  rollback tran
  return
 end
 /*update t_ps_life1*/
 update a   /*Add by John 10/09/2013*/ 
        /*set ann_rate=b.sa_add_rate,nel=b.sa_add_nel*/
         set a.ann_rate=b.sa_add_rate,a.nel=b.sa_add_nel
  from t_ps_life1 a,t_wp_life_underwriting b
  where a.comp_code=@compcode and a.ver_no=@verno and a.prod_name="ADD(S)" and 
   b.comp_code=@compcode and b.ver_no=@verno and b.uwver_no=@life_uwver
 if @@error<>0
 begin
  rollback tran
  return
 end
/* TPDI */
 insert t_ps_life1(comp_code,ver_no,prod_name,num_emp,tot_sa)
  select comp_code,ver_no,"TPDI",count(*),sum(convert(decimal(14,0),tpdi_sum_assu)) from t_wp_emp
  where comp_code=@compcode and ver_no=@verno and tpdi_sum_assu>0
  group by comp_code,ver_no
  order by comp_code,ver_no /*Add by John 10/09/2013*/
 if @@error<>0
 begin
  rollback tran
  return
 end
 /*update t_ps_life1*/
 update a   /*Add by John 10/09/2013*/
         /*set ann_rate=b.sa_tpd_rate,nel=b.sa_tpd_nel*/
         set a.ann_rate=b.sa_tpd_rate,a.nel=b.sa_tpd_nel
  from t_ps_life1 a,t_wp_life_underwriting b
  where a.comp_code=@compcode and a.ver_no=@verno and a.prod_name="TPDI" and 
   b.comp_code=@compcode and b.ver_no=@verno and b.uwver_no=@life_uwver
 if @@error<>0
 begin
  rollback tran
  return
 end
/* CI */
 insert t_ps_life1(comp_code,ver_no,prod_name,num_emp,tot_sa)
  select comp_code,ver_no,"CI",count(*),sum(convert(decimal(14,0),ci_sum_assu)) from t_wp_emp
  where comp_code=@compcode and ver_no=@verno and ci_sum_assu>0
  group by comp_code,ver_no
  order by comp_code,ver_no /*Add by John 10/09/2013*/
 if @@error<>0
 begin
  rollback tran
  return
 end
 /*update t_ps_life1*/
 update a   /*Add by John 10/09/2013*/
         /*set ann_rate=b.sa_ci_rate,nel=b.sa_ci_nel*/
         set a.ann_rate=b.sa_ci_rate,a.nel=b.sa_ci_nel/*Add by John 10/09/2013*/
  from t_ps_life1 a,t_wp_life_underwriting b
  where a.comp_code=@compcode and a.ver_no=@verno and a.prod_name="CI" and 
   b.comp_code=@compcode and b.ver_no=@verno and b.uwver_no=@life_uwver
 if @@error<>0
 begin
  rollback tran
  return
 end
/* LTD */
 insert t_ps_life1(comp_code,ver_no,prod_name,num_emp,tot_sa)
  select comp_code,ver_no,"LTD",count(*),sum(convert(decimal(14,0),ltd_sum_assu)) from t_wp_emp
  where comp_code=@compcode and ver_no=@verno and ltd_sum_assu>0
  group by comp_code,ver_no
  order by comp_code,ver_no /*Add by John 10/09/2013*/
 if @@error<>0
 begin
  rollback tran
  return
 end
 /*update t_ps_life1*/
 update a   /*Add by John 10/09/2013*/
         /*set ann_rate=b.sa_ltd_rate,nel=b.sa_ltd_nel*/
         set a.ann_rate=b.sa_ltd_rate,a.nel=b.sa_ltd_nel/*Add by John 10/09/2013*/
  from t_ps_life1 a,t_wp_life_underwriting b
  where a.comp_code=@compcode and a.ver_no=@verno and a.prod_name="LTD" and 
   b.comp_code=@compcode and b.ver_no=@verno and b.uwver_no=@life_uwver
 if @@error<>0
 begin
  rollback tran
  return
 end
/* for all life */
 /*update t_ps_life1 set ann_prem=tot_sa*ann_rate/1000   
  where comp_code=@compcode and ver_no=@verno*/
 update a 
         set a.ann_prem=tot_sa*ann_rate/1000   
  from t_ps_life1 a
  where a.comp_code=@compcode and a.ver_no=@verno /*Add by John 10/09/2013*/
  
 if @@error<>0
 begin
  rollback tran
  return
 end
 insert t_ps_life2(comp_code,ver_no,prod_name,plan_no,remarks)
  (select comp_code,ver_no,prod_name,plan_no,isnull(benefit_schedule,"")
   from t_wp_life1 where comp_code=@compcode and ver_no=@verno)
 if @@error<>0
 begin
  rollback tran
  return
 end
 insert t_ps_life2(comp_code,ver_no,prod_name,plan_no,ben_form,dedu_prd,wait_prd,ben_prd,ben_ind,max_ben)
  (select comp_code,ver_no,"LTD",plan_no,ratio_bms,remain_bala,Wait_period,ben_period,
   ben_indexation,Max_month_ben from t_wp_ltd1 where comp_code=@compcode and ver_no=@verno)
 if @@error<>0
 begin
  rollback tran
  return
 end
 /*update t_ps_life2 set sum_assu= y.sumplan from
   (select life_plan_no,sum(convert(decimal(14,0),life_sum_assu)) as "sumplan" from t_wp_emp
   where comp_code=@compcode and ver_no=@verno group by life_plan_no) as y
  where prod_name="LIFE" and plan_no=y.life_plan_no*/
 update a set a.sum_assu= y.sumplan from
                  t_ps_life2 a,
   (select life_plan_no,sum(convert(decimal(14,0),life_sum_assu)) as "sumplan" from t_wp_emp
   where comp_code=@compcode and ver_no=@verno group by life_plan_no order by life_plan_no) as y
  where a.prod_name="LIFE" and a.plan_no=y.life_plan_no  /*Add by John 10/09/2013*/
 if @@error<>0
 begin
  rollback tran
  return
 end
 /*update t_ps_life2 set sum_assu= y.sumplan from
   (select addl_plan_no,sum(convert(decimal(14,0),addl_sum_assu)) as "sumplan" from t_wp_emp
   where comp_code=@compcode and ver_no=@verno group by addl_plan_no) as y
  where prod_name="ADD(L)" and plan_no=y.addl_plan_no*/
 update a set a.sum_assu= y.sumplan from
                 t_ps_life2 a, 
   (select addl_plan_no,sum(convert(decimal(14,0),addl_sum_assu)) as "sumplan" from t_wp_emp
   where comp_code=@compcode and ver_no=@verno group by addl_plan_no order by addl_plan_no) as y
  where a.prod_name="ADD(L)" and a.plan_no=y.addl_plan_no /*Add by John 10/09/2013*/
 if @@error<>0
 begin
  rollback tran
  return
 end
 /*update t_ps_life2 set sum_assu= y.sumplan from
   (select adds_plan_no,sum(convert(decimal(14,0),adds_sum_assu)) as "sumplan" from t_wp_emp
   where comp_code=@compcode and ver_no=@verno group by adds_plan_no) as y
  where prod_name="ADD(S)" and plan_no=y.adds_plan_no*/
 update a set a.sum_assu= y.sumplan from
                 t_ps_life2 a,
   (select adds_plan_no,sum(convert(decimal(14,0),adds_sum_assu)) as "sumplan" from t_wp_emp
   where comp_code=@compcode and ver_no=@verno group by adds_plan_no order by adds_plan_no) as y
  where a.prod_name="ADD(S)" and a.plan_no=y.adds_plan_no /*Add by John 10/09/2013*/
 if @@error<>0
 begin
  rollback tran
  return
 end
 /*update t_ps_life2 set sum_assu= y.sumplan from
   (select tpdi_plan_no,sum(convert(decimal(14,0),tpdi_sum_assu)) as "sumplan" from t_wp_emp
   where comp_code=@compcode and ver_no=@verno group by tpdi_plan_no) as y
  where prod_name="TPDI" and plan_no=y.tpdi_plan_no*/
 update a set a.sum_assu= y.sumplan from
                 t_ps_life2 a,
   (select tpdi_plan_no,sum(convert(decimal(14,0),tpdi_sum_assu)) as "sumplan" from t_wp_emp
   where comp_code=@compcode and ver_no=@verno group by tpdi_plan_no order by tpdi_plan_no) as y
  where a.prod_name="TPDI" and a.plan_no=y.tpdi_plan_no /*Add by John 10/09/2013*/
 if @@error<>0
 begin
  rollback tran
  return
 end
 /*update t_ps_life2 set sum_assu= y.sumplan from
   (select ci_plan_no,sum(convert(decimal(14,0),ci_sum_assu)) as "sumplan" from t_wp_emp
   where comp_code=@compcode and ver_no=@verno group by ci_plan_no) as y
  where prod_name="CI" and plan_no=y.ci_plan_no*/
 update a set a.sum_assu= y.sumplan from
                 t_ps_life2 a,
   (select ci_plan_no,sum(convert(decimal(14,0),ci_sum_assu)) as "sumplan" from t_wp_emp
   where comp_code=@compcode and ver_no=@verno group by ci_plan_no order by ci_plan_no) as y
  where a.prod_name="CI" and a.plan_no=y.ci_plan_no /*Add by John 10/09/2013*/ 
 if @@error<>0
 begin
  rollback tran
  return
 end
 /*update t_ps_life2 set sum_assu= y.sumplan from
   (select ltd_plan_no,sum(convert(decimal(14,0),ltd_sum_assu)) as "sumplan" from t_wp_emp
   where comp_code=@compcode and ver_no=@verno group by ltd_plan_no) as y
  where prod_name="LTD" and plan_no=y.ltd_plan_no*/
 update a set a.sum_assu= y.sumplan from
                  t_ps_life2 a,
   (select ltd_plan_no,sum(convert(decimal(14,0),ltd_sum_assu)) as "sumplan" from t_wp_emp
   where comp_code=@compcode and ver_no=@verno group by ltd_plan_no order by ltd_plan_no) as y
  where a.prod_name="LTD" and a.plan_no=y.ltd_plan_no  /*Add by John 10/09/2013*/ 
 if @@error<>0
 begin
  rollback tran
  return
 end
 commit tran
GO