January 10, 2013 at 7:36 am
Hi,
Having a hard time figuring this out.
I have a query that is used for benefit deductions. We recently added a new deduction code which is fine but calculating the 'Monthly_ER_AMT' amount incorrectly. I know it's incorrect because it is using the 52 weeks and should be using 24.
Here's is the first query:
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 is the [WSI_F_VEBA_PREPAY_HAWC_ER_AMT] function that gets the amount:
--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 TESTED
SELECT @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
For the life of me I can't see how to make it calc the amount to use 24 weeks instead of 52.
January 10, 2013 at 7:43 am
Code snippet:
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
What is the value of this column, EC.EECPAYPERIOD, in EMPCOMP for the parameters @COID and @EEID passed to this function where the calculations are incorrect.
January 10, 2013 at 9:44 am
What is the value of this column, EC.EECPAYPERIOD, in EMPCOMP for the parameters @COID and @EEID passed to this function where the calculations are incorrect.
The value of EC.EECPAYPERIOD is 'B'
which I know calcs at 26
So I tried updating one of the records to 'S' but when I did that it did a strange thing put the value in 'Monthly_EE_AMT'.
The intersting part is that it was the correct amount now when using 'S'- but wrong field..
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply