select fullname ,empno ,empstatus ,termdate ,coid ,eeid ,org2 ,eeddedcode ,deddedtype ,eedbenstartdate ,eedbenstopdate ,case when deddedtype in ('STD','LTD') THEN DBO.WSI_F_VEBA_PREPAY_HAWC_STD_LTD_BENAMT (COID ,EEID ,ORG2 ,EEDDEDCODE ,dbo.WSI_F_GET_FIRST_DAY_OF_MONTH (getdate()) ,dbo.WSI_F_GET_LAST_DAY_OF_MONTH (getdate())) when deddedtype in ('OPT','OPS','OPC') THEN DBO.WSI_F_VEBA_PREPAY_HAWC_OPTLIFE_BENAMT (COID ,EEID ,ORG2 ,EEDDEDCODE ,dbo.WSI_F_GET_FIRST_DAY_OF_MONTH (getdate()) ,dbo.WSI_F_GET_LAST_DAY_OF_MONTH (getdate())) when deddedtype in ('GTL') THEN CASE EEDBENAMT WHEN 0 THEN ISNULL(DEDEEBENAMT,0) END end AS BEN_AMT ,dbo.WSI_F_VEBA_PREPAY_HAWC_EE_AMT (coid ,eeid ,org2 ,eeddedcode ,dbo.WSI_F_GET_FIRST_DAY_OF_MONTH (getdate()) ,dbo.WSI_F_GET_LAST_DAY_OF_MONTH (getdate())) as Monthly_EE_AMT ,dbo.WSI_F_VEBA_PREPAY_HAWC_ER_AMT (coid ,eeid ,org2 ,eeddedcode ,dbo.WSI_F_GET_FIRST_DAY_OF_MONTH (getdate()) ,dbo.WSI_F_GET_LAST_DAY_OF_MONTH (getdate())) as Monthly_ER_AMT from wsi_v_personnel_data PD inner join empded ED on eeid = eedeeid inner join dedcode DC on eeddedcode = dc.deddedcode INNER JOIN wsi_t_custom_PREMGROUPCODES PG ON ORG2 = PREMORG2CODE inner join wsi_t_custom_benefit_rates BR on (eeddedcode = dedbencode AND PG.PREMGROUPCODE = BR.PREMGROUPCODE) where org2 = 'WSIDOJ' and (eedbenstartdate <= dbo.WSI_F_GET_LAST_DAY_OF_MONTH(getdate()) AND (EEDBENSTOPDATE IS NULL OR EEDBENSTOPDATE >= dbo.WSI_F_GET_FIRST_DAY_OF_MONTH(getdate()))) and (EFFdate <= dbo.WSI_F_GET_LAST_DAY_OF_MONTH(getdate()) AND (EXPDATE IS NULL OR EXPDATE >= dbo.WSI_F_GET_FIRST_DAY_OF_MONTH(getdate())))AND eeddedcode = 'FDJ10' ORDER BY FULLNAME, CALC_ORDER, EEDDEDCODE
--THIS FUNCTION WORKS WITH ONE DEDCODE PER PREMGROUPCODE--IT'S CODED TO HANDLE MULTIPLE DEDCODES PER PREMGROUPCODE, BUT IS UNTESTED AS OF 1PM 02/02/2011 - MH-- THIS SCENARIO NEEDS TO BE TESTEDSELECT @COID = '7T6C1' ,@EEID = '8DBOAA000080' ,@ORG2 = 'TSIFHU' ,@DEDCODE = 'F9411' ,@STARTDATE = '04/01/2011' ,@STOPDATE = '04/01/2011' -- GET THE BATCH PERIOD END DATE TO COMPARE AGAINST CUSTOM RATE TABLE EFFDATES--*/ SELECT @PAY_FREQ = EC.EECPAYPERIOD ,@PAY_FREQ_DIVIDER = CASE EC.EECPAYPERIOD WHEN 'W' THEN 52 WHEN 'S' THEN 24 WHEN 'B' THEN 26 WHEN 'M' THEN 12 ELSE 26 END ,@ANN_SALARY = EC.EECANNSALARY ,@FT_PT = EC.EECFULLTIMEORPARTTIME ,@REG_TMP = EC.EECEETYPE ,@SAL_HRLY = CASE WHEN EC.EECSALARYORHOURLY = 'H' AND ((EC.EECUNIONLOCAL IS NULL OR EC.EECUNIONLOCAL = '') AND (EC.EECUNIONNATIONAL IS NULL OR EC.EECUNIONNATIONAL = '')) THEN 'N' WHEN EC.EECSALARYORHOURLY = 'H' AND ((EC.EECUNIONLOCAL IS NOT NULL AND EC.EECUNIONLOCAL <> '') OR (EC.EECUNIONNATIONAL IS NOT NULL AND EC.EECUNIONNATIONAL <> '')) THEN 'U' WHEN EC.EECSALARYORHOURLY = 'S' THEN 'S' WHEN EC.EECSALARYORHOURLY = 'A' THEN 'A' END ,@LOC_UNION = EC.EECUNIONLOCAL ,@NAT_UNION = EC.EECUNIONNATIONAL FROM EMPCOMP EC WITH (NOLOCK) WHERE EC.EECCOID = @COID AND EC.EECEEID = @EEID --USED ONLY TO FIGURE RSICK AMOUNT FOR THOSE THAT HAVE THE RSICK DEDCODE ON THEIR PANEL SELECT @RSICKAMT = (((@ANN_SALARY/2080)*40)/@PAY_FREQ_DIVIDER) FROM EMPDED WITH (NOLOCK) WHERE EEDEEID = @EEID AND EEDSTARTDATE <= @STOPDATE AND ((EEDSTOPDATE IS NULL OR EEDSTOPDATE < '01/01/1999') OR EEDSTOPDATE >= @STARTDATE) AND EEDDEDCODE IN (SELECT RSICKDEDCODE FROM WSI_T_CUSTOM_RSICK_DEDCODES WITH (NOLOCK)) IF @RSICKAMT IS NULL SELECT @RSICKAMT = 0 SET @RUNNING_TOT = 0 SET @TOT_HW_AMT = 0 SET @REG_HW_AMT = 0 SELECT @REG_HW_AMT = CASE @PAY_FREQ WHEN 'M' THEN 160 * R.HW_RATE WHEN 'B' THEN 80 * R.HW_RATE WHEN 'W' THEN 40 * R.HW_RATE ELSE 0 END ,@REG_HW_HRS = CASE @PAY_FREQ WHEN 'M' THEN 160 WHEN 'B' THEN 80 WHEN 'W' THEN 40 ELSE 0 END ,@HW_RATE = R.HW_RATE FROM WSI_T_CUSTOM_HW_RATES R WITH (NOLOCK) WHERE R.ORG2CODE = @ORG2 AND (R.JOBCODE = 'ALL' AND (FT_OR_PT = 'F' OR FT_OR_PT = 'A') AND (EE_TYPE = 'R' OR EE_TYPE = 'A') AND (SALARYHOURLYUNION = 'H' OR SALARYHOURLYUNION = 'A')) GROUP BY R.HOURS_CAP ,R.HW_RATE SELECT @REG_HW_AMT = ISNULL(@REG_HW_AMT,0) IF (@REG_HW_AMT - @RSICKAMT) < 0 SELECT @RSICKAMT = 0 SELECT @START_HW_AMT = @REG_HW_AMT - @RSICKAMT SELECT @TOT_HW_AMT = @START_HW_AMT --- PREMIUM CALCULATIONS DECLARE @GROUPED_PREMS TABLE (PRIMARY_KEY INT IDENTITY(1,1) NOT NULL ,PREMGRPCODE VARCHAR( 16) ,DEDCODE VARCHAR( 25) ,CALC_ORDER SMALLINT ,EE_PREM DECIMAL(9,4) ,EEC_PREM DECIMAL(9,4) ,EES_PREM DECIMAL(9,4) ,FAM_PREM DECIMAL(9,4)) INSERT INTO @GROUPED_PREMS SELECT BR.PREMGROUPCODE ,BR.DEDBENCODE ,BR.CALC_ORDER ,BR.EE_PREM ,BR.EEC_PREM ,BR.EES_PREM ,BR.FAM_PREM FROM WSI_T_CUSTOM_BENEFIT_RATES BR WITH (NOLOCK) INNER JOIN WSI_T_CUSTOM_PREMGROUPCODES PGC WITH (NOLOCK) ON BR.PREMGROUPCODE = PGC.PREMGROUPCODE WHERE DEDBENCODE IN (SELECT EEDDEDCODE FROM EMPDED WITH (NOLOCK) WHERE EEDEEID = @EEID AND EEDSTARTDATE <= @STOPDATE AND ((EEDSTOPDATE IS NULL OR EEDSTOPDATE < '01/01/1999') OR EEDSTOPDATE >= @STARTDATE)-- AND DBTBENOPTION <> 'Z' ) AND PGC.PREMORG2CODE = @ORG2 AND EFFDATE <= @STOPDATE AND ((EXPDATE IS NULL OR EXPDATE < '01/01/1999') OR EXPDATE >= @STARTDATE) ORDER BY CALC_ORDER --LOOPS THRU THE DEDCODES IN THE BENEFIT RATES TABLE DECLARE @PG_ROW_COUNTER SMALLINT , @PG_LOOP_COUNTER SMALLINT , @INDIV_EE_PREM DECIMAL(9,4) , @INDIV_EEC_PREM DECIMAL(9,4) , @INDIV_EES_PREM DECIMAL(9,4) , @INDIV_FAM_PREM DECIMAL(9,4) , @STD_CALCED_PREM DECIMAL(9,4) , @LTD_CALCED_PREM DECIMAL(9,4) , @OPL_CALCED_PREM DECIMAL(9,4) SELECT @PG_LOOP_COUNTER = COUNT(*) FROM @GROUPED_PREMS SELECT @PG_LOOP_COUNTER = ISNULL(@PG_LOOP_COUNTER, 0) SET @PG_ROW_COUNTER = 1 IF @PG_LOOP_COUNTER = 0 SELECT @ER_AMT = 0 ELSE BEGIN --PREMGROUP LOOP WHILE @PG_LOOP_COUNTER > 0 AND @PG_ROW_COUNTER <= @PG_LOOP_COUNTER BEGIN --SELECT THE DEDCODE TO CALCULATE SELECT @TMP_DEDCODE = DEDCODE ,@INDIV_EE_PREM = EE_PREM ,@INDIV_EEC_PREM = EEC_PREM ,@INDIV_EES_PREM = EES_PREM ,@INDIV_FAM_PREM = FAM_PREM ,@PREMCODE = PREMGRPCODE --ONLY NEEDED FOR TROUBLESHOOTING FROM @GROUPED_PREMS WHERE PRIMARY_KEY = @PG_ROW_COUNTER -- GETS DEDTYPE AND BENOPTION FOR DEDCODE THATS LOOPING SELECT @DEDTYPE = DC.DEDDEDTYPE ,@BENOPTION = ED.EEDBENOPTION FROM DEDCODE DC WITH (NOLOCK) INNER JOIN EMPDED ED WITH (NOLOCK) ON (DC.DEDDEDCODE = ED.EEDDEDCODE) WHERE DC.DEDDEDCODE = @TMP_DEDCODE AND ED.EEDEEID = @EEID AND DC.DEDDEDEFFSTARTDATE <= @STOPDATE AND ((DC.DEDDEDEFFSTOPDATE IS NULL OR DC.DEDDEDEFFSTOPDATE < '01/01/1990') OR DC.DEDDEDEFFSTOPDATE >= @STARTDATE) IF @BENOPTION IS NULL SELECT @BENOPTION = 'EE' SET @STD_CALCED_PREM = 0 SET @LTD_CALCED_PREM = 0 SET @OPL_CALCED_PREM = 0 IF @DEDTYPE = 'STD' SELECT @STD_CALCED_PREM = DBO.WSI_F_VEBA_PREPAY_HAWC_STD_LTD (@COID ,@EEID ,@ORG2 ,@TMP_DEDCODE ,@STARTDATE ,@STOPDATE) ELSE SELECT @STD_CALCED_PREM = 0 IF @DEDTYPE = 'LTD' SELECT @LTD_CALCED_PREM = DBO.WSI_F_VEBA_PREPAY_HAWC_STD_LTD (@COID ,@EEID ,@ORG2 ,@TMP_DEDCODE ,@STARTDATE ,@STOPDATE) ELSE SELECT @LTD_CALCED_PREM = 0 IF @DEDTYPE LIKE 'OP%' SELECT @OPL_CALCED_PREM = DBO.WSI_F_VEBA_PREPAY_HAWC_OPTLIFE (@COID ,@EEID ,@ORG2 ,@TMP_DEDCODE ,@STARTDATE ,@STOPDATE) ELSE SELECT @OPL_CALCED_PREM = 0------------------------------------------------------------------------------------- /* SELECT 'PRE_CALC VALUES - ROW#: ' + CAST(@PG_ROW_COUNTER AS CHAR(1)) , @PG_LOOP_COUNTER AS PG_LOOP_COUNTER , @TMP_DEDCODE AS ROW_DEDCODE , @DEDCODE AS IN_DEDCODE , DBO.WSI_F_GET_EMP_FULLNAME(@EEID) AS FULLNAME , @BENOPTION AS BENOPTION , @PREMCODE AS PREMCODE , @PAY_FREQ AS PAYFREQ , @ANN_SALARY AS ANN_SALARY , @REG_HW_AMT AS REG_HW_AMT , @REG_HW_HRS AS REG_HW_HRS , @HW_RATE AS HW_RATE , @RSICKAMT AS RSICKAMT , @INDIV_EE_PREM AS INDIV_EE_PREM , @INDIV_EES_PREM AS INDIV_EES_PREM , @INDIV_EEC_PREM AS INDIV_EEC_PREM , @INDIV_FAM_PREM AS INDIV_FAM_PREM , @STARTDATE AS STARTDATE , @STOPDATE AS STOPDATE , @STD_CALCED_PREM AS STD_PREM , @LTD_CALCED_PREM AS LTD_PREM , @OPL_CALCED_PREM AS OPL_PREM --*/ IF @DEDTYPE = 'STD' SELECT @PREM_AMT = @STD_CALCED_PREM ELSE IF @DEDTYPE = 'LTD' SELECT @PREM_AMT = @LTD_CALCED_PREM ELSE IF @DEDTYPE LIKE 'OP%' SELECT @PREM_AMT = @OPL_CALCED_PREM ELSE BEGIN IF @BENOPTION = 'EE' SELECT @PREM_AMT = @INDIV_EE_PREM ELSE IF @BENOPTION = 'EEC' SELECT @PREM_AMT = @INDIV_EEC_PREM ELSE IF @BENOPTION = 'EES' SELECT @PREM_AMT = @INDIV_EES_PREM ELSE IF @BENOPTION = 'FAM' SELECT @PREM_AMT = @INDIV_FAM_PREM END------------------------------------------------------------------------------------- IF @TOT_HW_AMT <= @PREM_AMT SELECT @ER_AMT = @TOT_HW_AMT ELSE SELECT @ER_AMT = @PREM_AMT SELECT @RUNNING_TOT = @RUNNING_TOT + @PREM_AMT IF @BENOPTION = 'Z' --<=====WHEN 'Z', BENEFIT WAS WAIVED ( SELECT @ER_AMT = 0 ,@PREM_AMT = 0 ) IF @PREM_AMT > @TOT_HW_AMT SELECT @TOT_HW_AMT = 0 ELSE SELECT @TOT_HW_AMT = @START_HW_AMT - @RUNNING_TOT/* SELECT 'POST_CALC VALUES - ROW#: ' + CAST(@PG_ROW_COUNTER AS CHAR(1)) , @TMP_DEDCODE AS TMP_DEDCODE , @DEDCODE AS IN_DEDCODE , @HW_RATE AS HW_RATE , @TOT_HW_AMT AS TOT_HW_AMT , @BENOPTION AS BENOPTION , @PREM_AMT AS PREM_AMT , @ER_AMT AS OUTPUT_ER_AMT , @RUNNING_TOT AS RUNNING_TOT --HOLDS THE TOTAL HW AMOUNT THRU OUT THE LOOP.--*/ --ADVANCES THE COUNTER FOR THE @@GROUPED_PREMS TEMP TABLE WHILE LOOP SELECT @PG_ROW_COUNTER = @PG_ROW_COUNTER + 1 IF @TMP_DEDCODE = @DEDCODE BEGIN BREAK END END --WHILE LOOP FOR PREM GROUP DEDCODES END --PREMGROUP LOOPCOUNTER > 0 IF @ER_AMT IS NULL OR @ER_AMT <= 0 SELECT @ER_AMT = 0 IF @ER_AMT <> 0 IF @DEDTYPE NOT LIKE 'OP%' SELECT @ER_AMT = @ER_AMT * @PAY_FREQ_DIVIDER /12/* SELECT @ER_AMT AS ER_AMT--*/ RETURN @ER_AMT END ---FUNCTION
SELECT @PAY_FREQ = EC.EECPAYPERIOD ,@PAY_FREQ_DIVIDER = CASE EC.EECPAYPERIOD WHEN 'W' THEN 52 WHEN 'S' THEN 24 WHEN 'B' THEN 26 WHEN 'M' THEN 12 ELSE 26 END ,@ANN_SALARY = EC.EECANNSALARY ,@FT_PT = EC.EECFULLTIMEORPARTTIME ,@REG_TMP = EC.EECEETYPE ,@SAL_HRLY = CASE WHEN EC.EECSALARYORHOURLY = 'H' AND ((EC.EECUNIONLOCAL IS NULL OR EC.EECUNIONLOCAL = '') AND (EC.EECUNIONNATIONAL IS NULL OR EC.EECUNIONNATIONAL = '')) THEN 'N' WHEN EC.EECSALARYORHOURLY = 'H' AND ((EC.EECUNIONLOCAL IS NOT NULL AND EC.EECUNIONLOCAL <> '') OR (EC.EECUNIONNATIONAL IS NOT NULL AND EC.EECUNIONNATIONAL <> '')) THEN 'U' WHEN EC.EECSALARYORHOURLY = 'S' THEN 'S' WHEN EC.EECSALARYORHOURLY = 'A' THEN 'A' END ,@LOC_UNION = EC.EECUNIONLOCAL ,@NAT_UNION = EC.EECUNIONNATIONAL FROM EMPCOMP EC WITH (NOLOCK) WHERE EC.EECCOID = @COID AND EC.EECEEID = @EEID
