if exists (select * from sysobjects where id = object_id('dbo.proc_fct_prsp') and sysstat & 0xf = 4) drop procedure dbo.proc_fct_prsp GO /****** Object: Stored Procedure dbo.proc_fct_prsp Script Date: 8/31/99 11:42:45 AM ******/ /****** Object: Stored Procedure dbo.proc_fct_prsp Script Date: 8/20/99 12:55:13 PM ******/ /****** Object: Stored Procedure dbo.proc_fct_prsp Script Date: 7/22/1999 8:30:16 AM ******/ /* * proc_fct_prsp * * 01/28/99 cd fixed duplicates caused by 2 market segments in a prospect * 01/31/99 cd moved subqueries to temp tables... * also, added activity to prospect. On the reports, it was decided that the current activity for * a prospect is derived from the case. I can anticipated that this will be changed in the * future. The columns will be already there. * 02/02/99 cd added 3 columns to the prospect fact table for the prospect status report * 02/07/1999 cd cursor insensitive * 06/18/1999 jp added code to handle dtq at autoscreen * 07/16/1999 cd rewrote number of lives and market segment calculation (used temp table logic from product line sales proc) * 07/20/1999 cd made market segment an inner join, not nullable field anymore * * (to view this code correctly, set your editor to 8 char tabs) */ CREATE PROCEDURE proc_fct_prsp @date_passed_in smalldatetime WITH RECOMPILE AS BEGIN TRUNCATE TABLE fct_prospect_t DECLARE @prsp_id dom_id, @case_id dom_guid, @prod_line_id dom_id, @prod_prft_centr_id dom_id, @mkt_seg_id dom_id, @mkt_dstrb_chnl_id dom_id, @actv_id dom_id, @spnsr_id dom_id, @sale_org_id dom_id, @hist_sale_org_id dom_id, @gr_offce_terr_id dom_id, @legal_enty_id dom_id, @dstrbr_id dom_id, @acq_blk_id dom_id, @dy_aftr_rlse_id dom_id, @dy_aftr_recv_id dom_id, @dy_bfor_due_id dom_id, @dy_bfor_eff_id dom_id, @prsp_due_dt dom_dt, @prsp_eff_dt dom_dt, @prsp_rlvnt_for_goal_cd dom_cd, @prsp_rfp_recv_dt dom_dt, @prsp_cvg_qty dom_qty1, @prsp_hot_cd dom_cd, @prsp_rep_trgt_cd dom_cd, @prsp_uw_trgt_cd dom_cd, @prsp_yrly_prem_amt dom_amt, @prsp_recv_not_quot_cd dom_cd, @prsp_rat_rel_not_prps_cd dom_cd, @prsp_prps_not_sold_cd dom_cd, @prsp_life_qty dom_qty1, @cvg_grp_actv_cd dom_cd, @return_cd integer, @cntrl_val_quote dom_desc1, @cntrl_val_requote dom_desc1, @cntrl_val_followquote dom_desc1, @cvg_grp_id dom_guid, @cvg_outcome_id dom_id, @date dom_dt, @net_days1 int, @net_days2 int, @net_days3 int, @net_days4 int DECLARE @cntrl_CvgSrcAutoscreen dom_desc1, @cntrl_CvgOutcomeDtq dom_desc1 DECLARE @cntrl_base_life_id dom_guid, @cntrl_opt_life_id dom_guid, @cntrl_se_base_life_id dom_guid, @cntrl_se_opt_life_id dom_guid, @cntrl_specific_stoploss_id dom_guid, @cntrl_prod_line_id_life dom_guid, @cntrl_prod_line_id_sl dom_guid DECLARE @proc_id integer SELECT @cntrl_base_life_id = prod_id FROM dim_product_t WHERE prod_nm = (SELECT cntrl_value FROM control_t WHERE cntrl_id = 110) SELECT @cntrl_opt_life_id = prod_id FROM dim_product_t WHERE prod_nm = (SELECT cntrl_value FROM control_t WHERE cntrl_id = 111) SELECT @cntrl_se_base_life_id = prod_id FROM dim_product_t WHERE prod_nm = (SELECT cntrl_value FROM control_t WHERE cntrl_id = 100) SELECT @cntrl_se_opt_life_id = prod_id FROM dim_product_t WHERE prod_nm = (SELECT cntrl_value FROM control_t WHERE cntrl_id = 101) SELECT @cntrl_specific_stoploss_id = prod_id FROM dim_product_t WHERE prod_nm = (SELECT cntrl_value FROM control_t WHERE cntrl_id = 114) SELECT @cntrl_prod_line_id_life = prod_line_id from dim_product_line_t where prod_line_nm = (SELECT cntrl_value FROM control_t WHERE cntrl_id = 130) SELECT @cntrl_prod_line_id_sl = prod_line_id from dim_product_line_t where prod_line_nm = (SELECT cntrl_value FROM control_t WHERE cntrl_id = 133) SELECT @cntrl_val_quote = (SELECT cntrl_value FROM control_t WHERE cntrl_id = 3) SELECT @cntrl_val_requote = (SELECT cntrl_value FROM control_t WHERE cntrl_id = 5) SELECT @cntrl_val_followquote = (SELECT cntrl_value FROM control_t WHERE cntrl_id = 25) SELECT @cntrl_CvgSrcAutoscreen = (SELECT cntrl_value FROM control_t WHERE cntrl_id = 60) SELECT @cntrl_CvgOutcomeDtq = (SELECT cntrl_value FROM control_t WHERE cntrl_id = 1) /* check those control values for existence */ IF @cntrl_val_quote IS NULL OR @cntrl_val_requote IS NULL OR @cntrl_val_followquote IS NULL OR @cntrl_CvgSrcAutoscreen IS NULL OR @cntrl_CvgOutcomeDtq IS NULL BEGIN SELECT @proc_id = @@procid EXECUTE @return_cd = proc_error 55556, @proc_id IF @return_cd >= 80 OR @return_cd < 0 RETURN @return_cd END SELECT @cvg_outcome_id = (SELECT cvg_outcome_id FROM dim_coverage_outcome_t where cvg_outcome_cd = @cntrl_CvgOutcomeDtq) /***************************************************************/ /* Build us a temp tables */ /***************************************************************/ /* let's keep in mind that this temp table would come in handy for the case facts, too. permanent table instead ? */ SELECT DISTINCT /* get the latest activity for each coverage group in a prospect */ CG.case_id, CG.cvg_grp_id, AH.actv_hist_seq_num, PRD.prod_line_id, CG.cvg_grp_actv_cd INTO #temp1 FROM GISTGEd2..activity_history_t AH INNER join fct_coverage_group_t CG ON Ah.cvg_grp_gu_id = CG.cvg_grp_id INNER join fct_coverage_t CVG ON CG.cvg_grp_id = CVG.cvg_grp_id INNER join dim_product_t PRD ON CVG.prod_id = PRD.prod_id WHERE AH.actv_hist_chrono_seq_num = (select MAX(actv_hist_chrono_seq_num) from GISTGEd2..activity_history_t where cvg_grp_gu_id = CG.cvg_grp_id) SELECT "case_id" = fC.case_id, "prod_line_id" = dP.prod_line_id, "prsp_cvg_qty" = CONVERT(smallint,count(*)), "prsp_yrly_prem_amt" = sum(cvg_prem_amt) INTO #prsp_cvg_totals FROM fct_case_t fC INNER JOIN fct_coverage_t fCvg ON fC.case_id = fCvg.case_id INNER JOIN dim_product_t dP ON fCvg.prod_id = dP.prod_id GROUP BY fC.case_id, dP.prod_line_id SELECT fC.case_id, dP.prod_line_id INTO #prsp_hot_cd FROM fct_case_t fC INNER JOIN fct_coverage_t fCvg ON fC.case_id = fCvg.case_id INNER JOIN dim_product_t dP ON fCvg.prod_id = dP.prod_id WHERE fCvg.cvg_hot_cd = 'Y' SELECT fC.case_id, dP.prod_line_id INTO #prsp_rep_trgt_cd FROM fct_case_t fC INNER JOIN fct_coverage_t fCvg ON fC.case_id = fCvg.case_id INNER JOIN dim_product_t dP ON fCvg.prod_id = dP.prod_id WHERE fCvg.cvg_rep_trgt_cd = 'Y' SELECT fC.case_id, dP.prod_line_id INTO #prsp_uw_trgt_cd FROM fct_case_t fC INNER JOIN fct_coverage_t fCvg ON fC.case_id = fCvg.case_id INNER JOIN dim_product_t dP ON fCvg.prod_id = dP.prod_id WHERE fCvg.cvg_uw_trgt_cd = 'Y' /******************************************************** * #prsp_life_qty * * * * Because we have somewhat of a hierarchy among * * products within a product line (ie, basic life, * * optional life, dependent life), we must create a * * temp table to sort out where we are getting the life * * amount from. Sun Advisor is included here * * * ********************************************************/ -- These first three queries are for the life product line -- This first query loads all basic life cvg_life_qty's into the table SELECT fC.case_id, fCvg.cvg_life_qty, dP.prod_line_id INTO #prsp_life_qty FROM fct_case_t fC INNER JOIN fct_coverage_t fCvg ON fC.case_id = fCvg.case_id AND ( fCvg.prod_id = @cntrl_base_life_id OR fCvg.prod_id = @cntrl_se_base_life_id ) INNER JOIN dim_product_t dP ON fCvg.prod_id = dP.prod_id -- This second query loads all optional life cvg_life_qty's into the table -- for coverages that are not already in the table (ie, coverages that do not have -- a basic life product INSERT INTO #prsp_life_qty SELECT fC.case_id, fCvg.cvg_life_qty, dP.prod_line_id FROM fct_case_t fC INNER JOIN fct_coverage_t fCvg ON fC.case_id = fCvg.case_id AND ( fCvg.prod_id = @cntrl_opt_life_id OR fCvg.prod_id = @cntrl_se_opt_life_id ) INNER JOIN dim_product_t dP ON fCvg.prod_id = dP.prod_id WHERE fC.case_id NOT IN (SELECT case_id FROM #prsp_life_qty WHERE prod_line_id = dP.prod_line_id) -- finally (for life) we ad the max amount of lives for coverages in the life product line -- that are not basic or optional life. INSERT INTO #prsp_life_qty SELECT fC.case_id, max(fCvg.cvg_life_qty), dP.prod_line_id FROM fct_case_t fC INNER JOIN fct_coverage_t fCvg ON fC.case_id = fCvg.case_id INNER JOIN dim_product_t dP ON fCvg.prod_id = dP.prod_id AND dP.prod_line_id = @cntrl_prod_line_id_life WHERE fC.case_id NOT IN (SELECT case_id FROM #prsp_life_qty WHERE prod_line_id = dP.prod_line_id) GROUP BY fC.case_id, dP.prod_line_id -- similar to life, stoploss has a product hierarchy (specific, aggregate). First we load -- the specific stop loss coverages and then we load the others. INSERT INTO #prsp_life_qty SELECT fC.case_id, fCvg.cvg_life_qty, dP.prod_line_id FROM fct_case_t fC INNER JOIN fct_coverage_t fCvg ON fC.case_id = fCvg.case_id AND fCvg.prod_id = @cntrl_specific_stoploss_id INNER JOIN dim_product_t dP ON fCvg.prod_id = dP.prod_id INSERT INTO #prsp_life_qty SELECT fC.case_id, max(fCvg.cvg_life_qty), dP.prod_line_id FROM fct_case_t fC INNER JOIN fct_coverage_t fCvg ON fC.case_id = fCvg.case_id INNER JOIN dim_product_t dP ON fCvg.prod_id = dP.prod_id AND dP.prod_line_id = @cntrl_prod_line_id_sl WHERE fC.case_id NOT IN (SELECT case_id FROM #prsp_life_qty WHERE prod_line_id = dP.prod_line_id) GROUP BY fC.case_id, dP.prod_line_id -- now for all the other products (STD, LTD and Sun Advisor) INSERT INTO #prsp_life_qty SELECT fC.case_id, max(fCvg.cvg_life_qty), dP.prod_line_id FROM fct_case_t fC INNER JOIN fct_coverage_t fCvg ON fC.case_id = fCvg.case_id INNER JOIN dim_product_t dP ON fCvg.prod_id = dP.prod_id WHERE fC.case_id NOT IN (SELECT case_id FROM #prsp_life_qty WHERE prod_line_id = dP.prod_line_id) GROUP BY fC.case_id, dP.prod_line_id /***************************************************************/ /* Declare the cursor */ /***************************************************************/ DECLARE cur_prsp CURSOR FOR /* a prospect is the sum of all coverages with the same product line */ SELECT DISTINCT fC.case_id, dP.prod_line_id, fCvg.prod_prft_centr_id, fCvg.mkt_dstrb_chnl_id, fC.spnsr_id, fC.sale_org_id, fC.hist_sale_org_id, fC.gr_offce_terr_id, fC.legal_enty_id, fC.dstrbr_id, fC.acq_blk_id, fC.case_due_dt, fC.case_eff_dt, fC.case_rfp_recv_dt, TOTALS.prsp_cvg_qty, CASE WHEN HOT.case_id IS NOT NULL THEN 'Y' ELSE 'N' END, CASE WHEN Rep.case_id IS NOT NULL THEN 'Y' ELSE 'N' END, CASE WHEN UW.case_id IS NOT NULL THEN 'Y' ELSE 'N' END, CONVERT(money,TOTALS.prsp_yrly_prem_amt), Lives.cvg_life_qty, dMS.mkt_seg_id FROM fct_case_t fC INNER JOIN fct_coverage_t fCvg ON fC.case_id = fCvg.case_id INNER JOIN dim_product_t dP ON fCvg.prod_id = dP.prod_id INNER JOIN #prsp_cvg_totals Totals ON fC.case_id = Totals.case_id AND dP.prod_line_id = Totals.prod_line_id INNER JOIN #prsp_life_qty Lives ON fC.case_id = Lives.case_id AND dP.prod_line_id = Lives.prod_line_id LEFT JOIN #prsp_hot_cd Hot ON fC.case_id = Hot.case_id AND dP.prod_line_id = Hot.prod_line_id LEFT JOIN #prsp_rep_trgt_cd Rep ON fC.case_id = Rep.case_id AND dP.prod_line_id = Rep.prod_line_id LEFT JOIN #prsp_uw_trgt_cd Uw ON fC.case_id = Uw.case_id AND dP.prod_line_id = Uw.prod_line_id INNER JOIN dim_market_segment_t dMS ON fCvg.prod_prft_centr_id = dMS.prod_prft_centr_id AND ( Lives.cvg_life_qty between dMS.mkt_seg_low_rng_qty AND dMs.mkt_seg_upr_rng_qty OR Lives.cvg_life_qty = dMS.mkt_seg_low_rng_qty -- works for NULL, too! ) IF @@error <> 0 BEGIN execute @return_cd = proc_error 50140 IF @return_cd >= 80 OR @return_cd < 0 RETURN @return_cd END /***************************************************************/ /* Loop through the cursor */ /***************************************************************/ OPEN cur_prsp FETCH NEXT from cur_prsp INTO @case_id, @prod_line_id, @prod_prft_centr_id, @mkt_dstrb_chnl_id, @spnsr_id, @sale_org_id, @hist_sale_org_id, @gr_offce_terr_id, @legal_enty_id, @dstrbr_id, @acq_blk_id, @prsp_due_dt, @prsp_eff_dt, @prsp_rfp_recv_dt, @prsp_cvg_qty, @prsp_hot_cd, @prsp_rep_trgt_cd, @prsp_uw_trgt_cd, @prsp_yrly_prem_amt, @prsp_life_qty, @mkt_seg_id SELECT @prsp_id = 0 WHILE (@@fetch_status >= 0) BEGIN IF EXISTS (SELECT * FROM fct_coverage_t fCvg INNER JOIN dim_product_t dProd ON fCvg.prod_id = dProd.prod_id WHERE dProd.prod_line_id = @prod_line_id AND fCvg.case_id = @case_id AND ( fCvg.cvg_outcome_src_cd NOT LIKE @cntrl_CvgSrcAutoscreen OR fCvg.cvg_outcome_src_cd IS NULL OR fCvg.cvg_outcome_id <> @cvg_outcome_id OR fCvg.cvg_outcome_id IS NULL ) ) BEGIN SELECT @prsp_rlvnt_for_goal_cd = 'Y' END ELSE SELECT @prsp_rlvnt_for_goal_cd = 'N' /* this query is supposed to find the coverage group (from all coverage groups in this prospect) */ /* that has the current activity with the lowest sequence number */ /* this coverage group is considered to be significant for the status of the prospect */ /* the MIN(cvg_grp_id) limits the result set to any 1 random record, in case there are */ /* 2 or more coverage groups with the same activity sequence number in this case */ SELECT @cvg_grp_id = MIN(cvg_grp_id) FROM #temp1 t1 WHERE t1.case_id = @case_id AND t1.prod_line_id = @prod_line_id AND t1.actv_hist_seq_num = ( SELECT MIN(actv_hist_seq_num) FROM #temp1 t2 WHERE t2.case_id = @case_id AND t2.prod_line_id = @prod_line_id ) /* with this info, we don't have to look at the activity dimension or the coverage group anymore */ SELECT @cvg_grp_actv_cd = cvg_grp_actv_cd FROM #temp1 WHERE cvg_grp_id = @cvg_grp_id /* get all the activities for this cvg grp in a temp table. According to Kathy M., a SELECT INTO */ /* and then dropping the table is faster than INSERTS and reuse a table, because the transaction log is not affected */ SELECT fAH.actv_hist_id, fAH.cvg_grp_id, fAH.actv_id, fAH.rrac_pers_id, fAH.actv_hist_chrono_seq_num, fAH.actv_hist_eff_dt, fAH.actv_hist_end_dt, dA.actv_nm, da.actv_seq_num INTO #fct_actv_hist FROM fct_activity_history_t fAH INNER JOIN dim_activity_t dA ON fAH.actv_id = dA.actv_id WHERE cvg_grp_id = @cvg_grp_id /* there must be a coverage group now .... */ /* get last activity for this prospect, based on coverage group that we found before */ SELECT @actv_id = actv_id FROM #fct_actv_hist WHERE actv_hist_chrono_seq_num = ( SELECT MAX(actv_hist_chrono_seq_num) FROM #fct_actv_hist ) /* for turnaround time: end of earliest underwriting activity */ SELECT @date = (SELECT MIN(actv_hist_eff_dt) FROM #fct_actv_hist WHERE actv_nm IN (@cntrl_val_quote, @cntrl_val_requote)) /* prospect is received, but not quoted or declined (= no outcome) */ SELECT @prsp_recv_not_quot_cd = 'N' IF NOT EXISTS ( SELECT * FROM #fct_actv_hist WHERE actv_nm = @cntrl_val_quote) AND @cvg_grp_actv_cd = 'Y' SELECT @prsp_recv_not_quot_cd = 'Y' /* prospect has rates released, but not proposed or declined (= no outcome) */ SELECT @prsp_rat_rel_not_prps_cd = 'N' IF EXISTS (SELECT * FROM #fct_actv_hist WHERE actv_nm = @cntrl_val_quote) BEGIN IF NOT EXISTS ( SELECT * FROM #fct_actv_hist WHERE actv_nm = @cntrl_val_followquote) AND @cvg_grp_actv_cd = 'Y' SELECT @prsp_rat_rel_not_prps_cd = 'Y' END /* prospect has proposal released, but not lost or sold (= no outcome) */ SELECT @prsp_prps_not_sold_cd = 'N' IF EXISTS (SELECT * FROM #fct_actv_hist WHERE actv_nm = @cntrl_val_followquote) AND @cvg_grp_actv_cd = 'Y' SELECT @prsp_prps_not_sold_cd = 'Y' EXECUTE proc_datediff @date_passed_in, @date, @net_days1 OUTPUT SELECT @dy_aftr_rlse_id = (SELECT dy_aftr_rlse_id FROM dim_days_after_release_t WHERE @net_days1 BETWEEN dy_low_rng_qty AND dy_upr_rng_qty) EXECUTE proc_datediff @date_passed_in, @date, @net_days1 OUTPUT SELECT @dy_aftr_rlse_id = (SELECT dy_aftr_rlse_id FROM dim_days_after_release_t WHERE @net_days1 BETWEEN dy_low_rng_qty AND dy_upr_rng_qty) IF @cvg_grp_id IS NULL SELECT @dy_aftr_rlse_id = NULL EXECUTE proc_datediff @date_passed_in, @prsp_rfp_recv_dt, @net_days2 OUTPUT SELECT @dy_aftr_recv_id = (SELECT dy_aftr_recv_id FROM dim_days_after_received_t WHERE @net_days2 BETWEEN dy_low_rng_qty AND dy_upr_rng_qty) EXECUTE proc_datediff @date_passed_in, @prsp_due_dt, @net_days3 OUTPUT SELECT @dy_bfor_due_id = (SELECT dy_bfor_due_id FROM dim_days_before_due_t WHERE @net_days3 BETWEEN dy_low_rng_qty AND dy_upr_rng_qty) EXECUTE proc_datediff @date_passed_in, @prsp_eff_dt, @net_days4 OUTPUT SELECT @dy_bfor_eff_id = (SELECT dy_bfor_eff_id FROM dim_days_before_effective_t WHERE @net_days4 BETWEEN dy_low_rng_qty AND dy_upr_rng_qty) -- 12/29/98 JM IF @mkt_seg_id = NULL BEGIN EXECUTE @return_cd = proc_error 50142,@case_id IF @return_cd >= 80 OR @return_cd < 0 RETURN @return_cd END INSERT fct_prospect_t ( prsp_id, case_id, prod_line_id, prod_prft_centr_id, mkt_seg_id, mkt_dstrb_chnl_id, actv_id, spnsr_id, sale_org_id, hist_sale_org_id, gr_offce_terr_id, legal_enty_id, dstrbr_id, acq_blk_id, dy_aftr_rlse_id, dy_aftr_recv_id, dy_bfor_due_id, dy_bfor_eff_id, prsp_due_dt, prsp_eff_dt, prsp_rfp_recv_dt, prsp_cvg_qty, prsp_hot_cd, prsp_rep_trgt_cd, prsp_uw_trgt_cd, prsp_yrly_prem_amt, prsp_life_qty, prsp_recv_not_quot_cd, prsp_rat_rel_not_prps_cd, prsp_prps_not_sold_cd, prsp_rlvnt_for_goal_cd, creat_dt ) VALUES ( @prsp_id, @case_id, @prod_line_id, @prod_prft_centr_id, @mkt_seg_id, @mkt_dstrb_chnl_id, @actv_id, @spnsr_id, @sale_org_id, @hist_sale_org_id, @gr_offce_terr_id, @legal_enty_id, @dstrbr_id, @acq_blk_id, @dy_aftr_rlse_id, @dy_aftr_recv_id, @dy_bfor_due_id, @dy_bfor_eff_id, CASE /* strip time information off date values */ WHEN @prsp_due_dt = NULL THEN NULL ELSE convert(smalldatetime, convert(char(10), @prsp_due_dt, 1) ) END, CASE WHEN @prsp_eff_dt = NULL THEN NULL ELSE convert(smalldatetime, convert(char(10), @prsp_eff_dt, 1) ) END, CASE WHEN @prsp_rfp_recv_dt = NULL THEN NULL ELSE convert(smalldatetime, convert(char(10), @prsp_rfp_recv_dt, 1) ) END, @prsp_cvg_qty, @prsp_hot_cd, @prsp_rep_trgt_cd, @prsp_uw_trgt_cd, @prsp_yrly_prem_amt, @prsp_life_qty, @prsp_recv_not_quot_cd, @prsp_rat_rel_not_prps_cd, @prsp_prps_not_sold_cd, @prsp_rlvnt_for_goal_cd, getdate() ) SELECT @prsp_id = @prsp_id + 1 FETCH NEXT from cur_prsp INTO @case_id, @prod_line_id, @prod_prft_centr_id, @mkt_dstrb_chnl_id, @spnsr_id, @sale_org_id, @hist_sale_org_id, @gr_offce_terr_id, @legal_enty_id, @dstrbr_id, @acq_blk_id, @prsp_due_dt, @prsp_eff_dt, @prsp_rfp_recv_dt, @prsp_cvg_qty, @prsp_hot_cd, @prsp_rep_trgt_cd, @prsp_uw_trgt_cd, @prsp_yrly_prem_amt, @prsp_life_qty, @mkt_seg_id DROP TABLE #fct_actv_hist /* drop local activity history */ END CLOSE cur_prsp DEALLOCATE cur_prsp /* ** Now we have to add the prospects to the coverage fact */ EXECUTE @return_cd = proc_fct_prsp_updt_cvg IF @return_cd <> 0 BEGIN EXECUTE @return_cd = proc_error 50141,NULL IF @return_cd >= 80 OR @return_cd < 0 RETURN @return_cd END DROP TABLE #temp1 DROP TABLE #prsp_hot_cd DROP TABLE #prsp_cvg_totals DROP TABLE #prsp_rep_trgt_cd DROP TABLE #prsp_uw_trgt_cd DROP TABLE #prsp_life_qty RETURN 0 END GO