I have been asked to add a row counter in the following sql statement. The following sql statement has a number of union all statements. I have tried using rownum, and row_number to get the desired result of adding a counter column for each employee and code. The issue here I believe is the union all statement resets the rownum or row number counter back to 1. Any suggestions on how to get around this issue are welcome:
SELECT D.FIRST_NAME || ' ' || D.LAST_NAME AS "Worker Name"
,X.TO_VALUE_STR50 as "WORKER ID"
,(SELECT X.TO_VALUE_STR50 FROM PS_HK_XWALK_TBL X WHERE X.FIELDNAME='POSITION_NBR' AND X.FROM_VALUE_STR50= A.EMPLID||A.POSITION_NBR||A.EMPL_RCD AND X.HK_ENTITY_NAME='BH') AS "POSITION ID"
,'' AS "Payroll Input"
,'Payroll_P1_DEDUCTION_INPUT' as "Batch ID"
,'' as "Source Reference ID"
,CASE WHEN E.DEDUCTION_END_DT IS NULL THEN 'Y' ELSE 'N' END AS "Ongoing Input"
,TO_CHAR(E.EFFDT, 'YYYY-MM-DD') AS "Start Date"
,CASE WHEN E.DEDCD IN ('DENPRE','FSAHLT','GTLINC','HOSIND','IHCEFT','MEDPRE','MEDSRC','VISPRE','VOLADD','LEGALI','SUPLIF','CHLIFE','HSA-EE',
'SPLIFE','ACCINS','CRITIL','FSADEP','DEPSRC','HSA-ER','STDINS') THEN '2023-12-16' ELSE TO_CHAR(E.DEDUCTION_END_DT, 'YYYY-MM-DD') END AS "End Date"
,'' AS "Run Category Reference ID"
,'' AS "Earning Reference ID"
,(SELECT V.TO_VALUE_STR50 FROM PS_HK_XWALK_TBL V WHERE V.FIELDNAME = 'DEDCD' AND V.HK_ENTITY_NAME = 'BH' AND V.FROM_DATE = '01-JAN-1900'
AND V.FROM_VALUE_STR50 = E.DEDCD) AS "Deduction Reference ID"
,CASE WHEN E.DED_ADDL_AMT <> 0 THEN TO_CHAR(E.DED_ADDL_AMT,'99999999.99') END AS "Amount"
,'' AS "Hours"
,'' AS "Rate"
,'Y' AS "Adjustment"
,'' AS "Location Reference ID"
,'' AS "Region Reference ID"
,'' AS "Job Profile Reference ID"
,'' AS "Cost Center Reference ID"
,'' AS "Project Reference ID"
,'' AS "Project Phase Reference ID"
,'' AS "Project Task Reference ID"
,'' AS "Order Case Reference ID"
,'' AS "State Authority Reference ID"
,'' AS "Workers Compensation Ref ID"
,'' AS "County Authority Reference ID"
,'' AS "City Authority Reference ID"
,'' AS "School District Reference ID"
,'' AS "Custom Worktag 1 Reference ID"
,'' AS "Custom Worktag 2 Reference ID"
,'' AS "Custom Worktag 3 Reference ID"
,'' AS "Fund Reference ID"
,'' AS "Grant Reference ID"
,'' AS "Gift Reference ID"
,'' AS "Program Reference ID"
,'' AS "Business Unit Reference ID"
,'' AS "Object Class Reference ID"
,'' AS "Custom Organization Ref ID"
,'' AS "Local Other Tax Auth Ref ID"
,'' AS "NI Category Reference ID"
,'' AS "ARRCO-AGIRC Category Ref ID"
, CASE WHEN (E.GOAL_AMT - E.GOAL_BAL) <> 0 AND E.DEDCD IN ('MISC','ACCREC', 'DCWREC', 'ISPREC', 'KDPREC', 'PRYROV','HSA-EE', 'FSADEP', 'FSAHLT', 'BFFOUN2') THEN 'W_ORIG'
ELSE case when E.DED_RATE_PCT <> 0 AND E.DEDCD IN ('MISC','ACCREC', 'DCWREC', 'ISPREC', 'KDPREC', 'PRYROV') THEN 'W_PCT' END END AS "Related Calculation Ref ID"
, CASE WHEN (E.GOAL_AMT - E.GOAL_BAL) <> 0 AND E.DEDCD IN ('MISC','ACCREC', 'DCWREC', 'ISPREC', 'KDPREC', 'PRYROV','HSA-EE', 'FSADEP', 'FSAHLT', 'BFFOUN2') THEN (E.GOAL_AMT - E.GOAL_BAL)
ELSE CASE WHEN E.DED_RATE_PCT <> 0 AND E.DEDCD IN ('MISC','ACCREC', 'DCWREC', 'ISPREC', 'KDPREC', 'PRYROV') THEN (E.DED_RATE_PCT/100) END END AS "Related Calculation Input"
,'' AS "Currency ID Reference"
,'' AS "Match Existing Input"
,'' AS "Company Reference ID"
,'' AS "Costing Company Reference ID"
,'' AS "Coverage Start Date"
,'' AS "Coverage End Date"
--, row_number() over(partition by d.emplid order by e.emplid, e.dedcd) as "Unique ID"
--, row_number() over(PARTITION BY E.EMPLID, E.DEDCD ORDER BY (SELECT NULL FROM DUAL)) as "Unique ID":
,X.FROM_VALUE_STR50 AS "Legacy ID"
,E.DEDCD AS "LEGACY DEDUCTION CODE"
,E.GOAL_AMT AS "goal amount"
,E.GOAL_BAL as "Amt deducted"
FROM PS_HK_XWALK_TBL X, PS_JOB A, PS_NAMES D, PS_GENL_DEDUCTION E
WHERE X.FIELDNAME='PERORG_EMP' AND X.HK_ENTITY_NAME='BH' AND X.FROM_DATE='01-JAN-1900'
AND E.EMPLID = X.FROM_VALUE_STR50
AND E.EFFDT = (SELECT MAX(F.EFFDT) FROM PS_GENL_DEDUCTION F
WHERE F.EMPLID = E.EMPLID AND F.COMPANY = E.COMPANY
AND F.DEDCD = E.DEDCD AND F.EFFDT >= :1)
--:1 bind 1 variable should be DD-MON-YYYY format. The date in which to pull all 2022 or 2023 general deduction data only.
-- in 2022 it was the 12/26/2021 date for 2022. As benefits loaded data into general deductions for 2022 open enrollment on 12/26/2021.
AND (E.DEDUCTION_END_DT >= :2 OR E.DEDUCTION_END_DT IS NULL)
--:2 bind 2 variable should be DD-MON-YYYY format. The date should be greater than the last processed pay end date. As the deduction
--end date must be greater than or equal to the pay end date to not be pulled.
AND (E.GOAL_BAL <> E.GOAL_AMT OR E.GOAL_BAL = 0 AND E.GOAL_AMT = 0)
AND E.EMPLID = D.EMPLID
AND D.NAME_TYPE = 'PRI'
AND D.EFFDT = (SELECT MAX(G.EFFDT) FROM PS_NAMES G
WHERE D.EMPLID = G.EMPLID
AND D.NAME_TYPE = G.NAME_TYPE
AND G.EFFDT <= SYSDATE)
AND E.EMPLID = A.EMPLID AND A.PER_ORG = 'EMP'
AND A.JOB_INDICATOR = 'P'
AND A.HR_STATUS = 'A'
AND A.PAYGROUP NOT IN ('ESM','PSM','XSM')
AND A.EFFDT = (SELECT MAX(B.EFFDT) FROM PS_JOB B
WHERE A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND B.EFFDT <= :2 AND
A.EFFSEQ = (SELECT MAX(C.EFFSEQ) FROM PS_JOB C WHERE C.EMPLID = A.EMPLID
AND C.EMPL_RCD = A.EMPL_RCD AND C.EFFDT = A.EFFDT))
AND E.DEDCD IN ('ISPREC','DCWREC','KDPREC','MISC','ACCREC')
AND E.DED_CALC NOT IN ('D')
UNION ALL
--- General Payroll Input
-- NOTE: SOME EMPLOYEES MAY HAVE COMPANY 400 & COMPANY 100 ROWS AS THEY WORK IN BOTH COMPANIES
SELECT D.FIRST_NAME || ' ' || D.LAST_NAME AS "Worker Name"
,X.TO_VALUE_STR50 as "WORKER ID"
,(SELECT X.TO_VALUE_STR50 FROM PS_HK_XWALK_TBL X WHERE X.FIELDNAME='POSITION_NBR' AND X.FROM_VALUE_STR50= A.EMPLID||A.POSITION_NBR||A.EMPL_RCD AND X.HK_ENTITY_NAME='BH') AS "POSITION ID"
,'' AS "Payroll Input"
,'Payroll_P1_DEDUCTION_INPUT' as "Batch ID"
,'' as "Source Reference ID"
,CASE WHEN E.DEDUCTION_END_DT IS NULL THEN 'Y' ELSE 'N' END AS "Ongoing Input"
,TO_CHAR(E.EFFDT, 'YYYY-MM-DD') AS "Start Date"
,CASE WHEN E.DEDCD IN ('DENPRE','FSAHLT','GTLINC','HOSIND','IHCEFT','MEDPRE','MEDSRC','VISPRE','VOLADD','LEGALI','SUPLIF','CHLIFE','HSA-EE',
'SPLIFE','ACCINS','CRITIL','FSADEP','DEPSRC','HSA-ER','STDINS') THEN '2023-12-16' ELSE TO_CHAR(E.DEDUCTION_END_DT, 'YYYY-MM-DD') END AS "End Date"
,'' AS "Run Category Reference ID"
,'' AS "Earning Reference ID"
,(SELECT V.TO_VALUE_STR50 FROM PS_HK_XWALK_TBL V WHERE V.FIELDNAME = 'DEDCD' AND V.HK_ENTITY_NAME = 'BH' AND V.FROM_DATE = '01-JAN-1900'
AND V.FROM_VALUE_STR50 = E.DEDCD) AS "Deduction Reference ID"
,CASE WHEN E.DED_ADDL_AMT <> 0 THEN TO_CHAR(E.DED_ADDL_AMT,'99999999.99') END AS "Amount"
,'' AS "Hours"
,'' AS "Rate"
,'Y' AS "Adjustment"
,'' AS "Location Reference ID"
,'' AS "Region Reference ID"
,'' AS "Job Profile Reference ID"
,'' AS "Cost Center Reference ID"
,'' AS "Project Reference ID"
,'' AS "Project Phase Reference ID"
,'' AS "Project Task Reference ID"
,'' AS "Order Case Reference ID"
,'' AS "State Authority Reference ID"
,'' AS "Workers Compensation Ref ID"
,'' AS "County Authority Reference ID"
,'' AS "City Authority Reference ID"
,'' AS "School District Reference ID"
,'' AS "Custom Worktag 1 Reference ID"
,'' AS "Custom Worktag 2 Reference ID"
,'' AS "Custom Worktag 3 Reference ID"
,'' AS "Fund Reference ID"
,'' AS "Grant Reference ID"
,'' AS "Gift Reference ID"
,'' AS "Program Reference ID"
,'' AS "Business Unit Reference ID"
,'' AS "Object Class Reference ID"
,'' AS "Custom Organization Ref ID"
,'' AS "Local Other Tax Auth Ref ID"
,'' AS "NI Category Reference ID"
,'' AS "ARRCO-AGIRC Category Ref ID"
, CASE WHEN (E.GOAL_AMT - E.GOAL_BAL) <> 0 AND E.DEDCD IN ('MISC','ACCREC', 'DCWREC', 'ISPREC', 'KDPREC', 'PRYROV','HSA-EE', 'FSADEP', 'FSAHLT', 'BFFOUN2') THEN 'W_ORIG'
ELSE CASE WHEN E.DED_RATE_PCT <> 0 AND E.DEDCD IN ('MISC','ACCREC', 'DCWREC', 'ISPREC', 'KDPREC', 'PRYROV') THEN 'W_PCT' END END AS "Related Calculation Ref ID"
, CASE WHEN (E.GOAL_AMT - E.GOAL_BAL) <> 0 AND E.DEDCD IN ('MISC','ACCREC', 'DCWREC', 'ISPREC', 'KDPREC', 'PRYROV','HSA-EE', 'FSADEP', 'FSAHLT', 'BFFOUN2') THEN (E.GOAL_AMT- E.GOAL_BAL)
ELSE CASE WHEN E.DED_RATE_PCT <> 0 AND E.DEDCD IN ('MISC','ACCREC', 'DCWREC', 'ISPREC', 'KDPREC', 'PRYROV') THEN (E.DED_RATE_PCT/100) END END AS "Related Calculation Input"
,'' AS "Currency ID Reference"
,'' AS "Match Existing Input"
,'' AS "Company Reference ID"
,'' AS "Costing Company Reference ID"
,'' AS "Coverage Start Date"
,'' AS "Coverage End Date"
--, row_number() over(partition by d.emplid order by e.emplid, e.dedcd) as "Unique ID"
--, ROW_NUMBER() OVER (PARTITION BY E.EMPLID, E.DEDCD ORDER BY E.EMPLID) + 1
,X.FROM_VALUE_STR50 AS "Legacy ID"
,E.DEDCD AS "LEGACY DEDUCTION CODE"
,E.GOAL_AMT AS "goal amount"
,E.GOAL_BAL as "Amt deducted"
FROM PS_HK_XWALK_TBL X, PS_JOB A, PS_NAMES D, PS_GENL_DEDUCTION E
WHERE X.FIELDNAME='PERORG_EMP' AND X.HK_ENTITY_NAME='BH' AND X.FROM_DATE='01-JAN-1900'
AND E.EMPLID = X.FROM_VALUE_STR50
AND E.EFFDT = (SELECT MAX(F.EFFDT) FROM PS_GENL_DEDUCTION F
WHERE F.EMPLID = E.EMPLID AND F.COMPANY = E.COMPANY
AND F.DEDCD = E.DEDCD AND F.EFFDT >= :1)
--:1 bind 1 variable should be DD-MON-YYYY format. The date in which to pull all 2022 or 2023 general deduction data only.
-- in 2022 it was the 12/26/2021 date for 2022. As benefits loaded data into general deductions for 2022 open enrollment on 12/26/2021.
AND (E.DEDUCTION_END_DT >= :2 OR E.DEDUCTION_END_DT IS NULL)
--:2 bind 2 variable should be DD-MON-YYYY format. The date should be greater than the last processed pay end date. As the deduction
--end date must be greater than or equal to the pay end date to not be pulled.
AND (E.GOAL_BAL <> E.GOAL_AMT OR E.GOAL_BAL = 0 AND E.GOAL_AMT = 0)
AND E.EMPLID = D.EMPLID
AND D.NAME_TYPE = 'PRI'
AND D.EFFDT = (SELECT MAX(G.EFFDT) FROM PS_NAMES G
WHERE D.EMPLID = G.EMPLID
AND D.NAME_TYPE = G.NAME_TYPE
AND G.EFFDT <= SYSDATE)
AND E.EMPLID = A.EMPLID AND A.PER_ORG = 'EMP'
AND A.JOB_INDICATOR = 'P'
AND A.HR_STATUS = 'I'
AND A.PAYGROUP NOT IN ('ESM','PSM','XSM')
AND A.EFFDT = (SELECT MAX(B.EFFDT) FROM PS_JOB B
WHERE A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND B.EFFDT <= :2 AND
A.EFFSEQ = (SELECT MAX(C.EFFSEQ) FROM PS_JOB C WHERE C.EMPLID = A.EMPLID
AND C.EMPL_RCD = A.EMPL_RCD AND C.EFFDT = A.EFFDT))
--AND E.DEDCD IN ('GARNSH','ISPREC','DCWREC','KDPREC','MISC','ACCREC')
AND E.DED_CALC NOT IN ('D')
AND E.DEDCD IN ('ISPREC','DCWREC','KDPREC','MISC','ACCREC')
UNION ALL
--- General Payroll Input
-- NOTE: THE TWO SQL'S CREATE A SECONDARY ROW FOR EACH EMPLOYEE WHO HAVE CERTAIN DEDUCTION CODES AND HAVE A PERCENTAGE
SELECT D.FIRST_NAME || ' ' || D.LAST_NAME AS "Worker Name"
,X.TO_VALUE_STR50 as "WORKER ID"
,''
,'' AS "Payroll Input"
,'' as "Batch ID"
,'' as "Source Reference ID"
,'' AS "Ongoing Input"
,'' AS "Start Date"
,'' AS "End Date"
,'' AS "Run Category Reference ID"
,'' AS "Earning Reference ID"
,(SELECT V.TO_VALUE_STR50 FROM PS_HK_XWALK_TBL V WHERE V.FIELDNAME = 'DEDCD' AND V.HK_ENTITY_NAME = 'BH' AND V.FROM_DATE = '01-JAN-1900'
AND V.FROM_VALUE_STR50 = E.DEDCD) AS "Deduction Reference ID"
,'' AS "Amount"
,'' AS "Hours"
,'' AS "Rate"
,'' AS "Adjustment"
,'' AS "Location Reference ID"
,'' AS "Region Reference ID"
,'' AS "Job Profile Reference ID"
,'' AS "Cost Center Reference ID"
,'' AS "Project Reference ID"
,'' AS "Project Phase Reference ID"
,'' AS "Project Task Reference ID"
,'' AS "Order Case Reference ID"
,'' AS "State Authority Reference ID"
,'' AS "Workers Compensation Ref ID"
,'' AS "County Authority Reference ID"
,'' AS "City Authority Reference ID"
,'' AS "School District Reference ID"
,'' AS "Custom Worktag 1 Reference ID"
,'' AS "Custom Worktag 2 Reference ID"
,'' AS "Custom Worktag 3 Reference ID"
,'' AS "Fund Reference ID"
,'' AS "Grant Reference ID"
,'' AS "Gift Reference ID"
,'' AS "Program Reference ID"
,'' AS "Business Unit Reference ID"
,'' AS "Object Class Reference ID"
,'' AS "Custom Organization Ref ID"
,'' AS "Local Other Tax Auth Ref ID"
,'' AS "NI Category Reference ID"
,'' AS "ARRCO-AGIRC Category Ref ID"
, case when E.DED_RATE_PCT <> 0 AND E.DEDCD IN ('MISC','ACCREC', 'DCWREC', 'ISPREC', 'KDPREC', 'PRYROV') THEN 'W_PCT' END AS "Related Calculation Ref ID"
, CASE WHEN E.DED_RATE_PCT <> 0 AND E.DEDCD IN ('MISC','ACCREC', 'DCWREC', 'ISPREC', 'KDPREC', 'PRYROV') THEN (E.DED_RATE_PCT/100) END AS "Related Calculation Input"
,'' AS "Currency ID Reference"
,'' AS "Match Existing Input"
,'' AS "Company Reference ID"
,'' AS "Costing Company Reference ID"
,'' AS "Coverage Start Date"
,'' AS "Coverage End Date"
--, row_number() over(partition by d.emplid order by e.emplid, e.dedcd) as "Unique ID"
--, ROW_NUMBER() OVER (PARTITION BY E.EMPLID, E.DEDCD ORDER BY E.EMPLID) + 1
,X.FROM_VALUE_STR50 AS "Legacy ID"
,E.DEDCD AS "LEGACY DEDUCTION CODE"
,E.GOAL_AMT AS "goal amount"
,E.GOAL_BAL as "Amt deducted"
FROM PS_HK_XWALK_TBL X, PS_JOB A, PS_NAMES D, PS_GENL_DEDUCTION E
WHERE X.FIELDNAME='PERORG_EMP' AND X.HK_ENTITY_NAME='BH' AND X.FROM_DATE='01-JAN-1900'
AND E.EMPLID = X.FROM_VALUE_STR50
AND E.EFFDT = (SELECT MAX(F.EFFDT) FROM PS_GENL_DEDUCTION F
WHERE F.EMPLID = E.EMPLID AND F.COMPANY = E.COMPANY
AND F.DEDCD = E.DEDCD AND F.EFFDT >= :1)
--:1 bind 1 variable should be DD-MON-YYYY format. The date in which to pull all 2022 or 2023 general deduction data only.
-- in 2022 it was the 12/26/2021 date for 2022. As benefits loaded data into general deductions for 2022 open enrollment on 12/26/2021.
AND (E.DEDUCTION_END_DT >= :2 OR E.DEDUCTION_END_DT IS NULL)
--:2 bind 2 variable should be DD-MON-YYYY format. The date should be greater than the last processed pay end date. As the deduction
--end date must be greater than or equal to the pay end date to not be pulled.
AND (E.GOAL_BAL <> E.GOAL_AMT OR E.GOAL_BAL = 0 AND E.GOAL_AMT = 0)
AND E.EMPLID = D.EMPLID
AND D.NAME_TYPE = 'PRI'
AND D.EFFDT = (SELECT MAX(G.EFFDT) FROM PS_NAMES G
WHERE D.EMPLID = G.EMPLID
AND D.NAME_TYPE = G.NAME_TYPE
AND G.EFFDT <= SYSDATE)
AND E.EMPLID = A.EMPLID AND A.PER_ORG = 'EMP'
AND A.JOB_INDICATOR = 'P'
AND A.HR_STATUS = 'A'
AND A.PAYGROUP NOT IN ('ESM','PSM','XSM')
AND A.EFFDT = (SELECT MAX(B.EFFDT) FROM PS_JOB B
WHERE A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND B.EFFDT <= :2 AND
A.EFFSEQ = (SELECT MAX(C.EFFSEQ) FROM PS_JOB C WHERE C.EMPLID = A.EMPLID
AND C.EMPL_RCD = A.EMPL_RCD AND C.EFFDT = A.EFFDT))
AND E.DEDCD IN ('ISPREC','DCWREC','KDPREC','MISC','ACCREC')
AND E.DED_CALC NOT IN ('D')
AND E.DED_RATE_PCT <> 0
--AND E.DEDCD IN ('PRYROV')
AND (E.GOAL_AMT - E.GOAL_BAL) <> 0
AND E.DED_RATE_PCT <> 0
UNION ALL
--- General Payroll Input
-- NOTE: SOME EMPLOYEES MAY HAVE COMPANY 400 & COMPANY 100 ROWS AS THEY WORK IN BOTH COMPANIES
SELECT D.FIRST_NAME || ' ' || D.LAST_NAME AS "Worker Name"
,X.TO_VALUE_STR50 as "WORKER ID"
,''
,'' AS "Payroll Input"
,'' as "Batch ID"
,'' as "Source Reference ID"
,'' AS "Ongoing Input"
,'' AS "Start Date"
,'' AS "End Date"
,'' AS "Run Category Reference ID"
,'' AS "Earning Reference ID"
,(SELECT V.TO_VALUE_STR50 FROM PS_HK_XWALK_TBL V WHERE V.FIELDNAME = 'DEDCD' AND V.HK_ENTITY_NAME = 'BH' AND V.FROM_DATE = '01-JAN-1900'
AND V.FROM_VALUE_STR50 = E.DEDCD) AS "Deduction Reference ID"
,'' AS "Amount"
,'' AS "Hours"
,'' AS "Rate"
,'' AS "Adjustment"
,'' AS "Location Reference ID"
,'' AS "Region Reference ID"
,'' AS "Job Profile Reference ID"
,'' AS "Cost Center Reference ID"
,'' AS "Project Reference ID"
,'' AS "Project Phase Reference ID"
,'' AS "Project Task Reference ID"
,'' AS "Order Case Reference ID"
,'' AS "State Authority Reference ID"
,'' AS "Workers Compensation Ref ID"
,'' AS "County Authority Reference ID"
,'' AS "City Authority Reference ID"
,'' AS "School District Reference ID"
,'' AS "Custom Worktag 1 Reference ID"
,'' AS "Custom Worktag 2 Reference ID"
,'' AS "Custom Worktag 3 Reference ID"
,'' AS "Fund Reference ID"
,'' AS "Grant Reference ID"
,'' AS "Gift Reference ID"
,'' AS "Program Reference ID"
,'' AS "Business Unit Reference ID"
,'' AS "Object Class Reference ID"
,'' AS "Custom Organization Ref ID"
,'' AS "Local Other Tax Auth Ref ID"
,'' AS "NI Category Reference ID"
,'' AS "ARRCO-AGIRC Category Ref ID"
, case when E.DED_RATE_PCT <> 0 AND E.DEDCD IN ('MISC','ACCREC', 'DCWREC', 'ISPREC', 'KDPREC', 'PRYROV') THEN 'W_PCT' END AS "Related Calculation Ref ID"
, CASE WHEN E.DED_RATE_PCT <> 0 AND E.DEDCD IN ('MISC','ACCREC', 'DCWREC', 'ISPREC', 'KDPREC', 'PRYROV') THEN (E.DED_RATE_PCT/100) END AS "Related Calculation Input"
,'' AS "Currency ID Reference"
,'' AS "Match Existing Input"
,'' AS "Company Reference ID"
,'' AS "Costing Company Reference ID"
,'' AS "Coverage Start Date"
,'' AS "Coverage End Date"
--, row_number() over(partition by d.emplid order by e.emplid, e.dedcd) as "Unique ID"
--, ROW_NUMBER() OVER (PARTITION BY E.EMPLID, E.DEDCD ORDER BY E.EMPLID) + 1
,X.FROM_VALUE_STR50 AS "Legacy ID"
,E.DEDCD AS "LEGACY DEDUCTION CODE"
,E.GOAL_AMT AS "goal amount"
,E.GOAL_BAL as "Amt deducted"
FROM PS_HK_XWALK_TBL X, PS_JOB A, PS_NAMES D, PS_GENL_DEDUCTION E
WHERE X.FIELDNAME='PERORG_EMP' AND X.HK_ENTITY_NAME='BH' AND X.FROM_DATE='01-JAN-1900'
AND E.EMPLID = X.FROM_VALUE_STR50
AND E.EFFDT = (SELECT MAX(F.EFFDT) FROM PS_GENL_DEDUCTION F
WHERE F.EMPLID = E.EMPLID AND F.COMPANY = E.COMPANY
AND F.DEDCD = E.DEDCD AND F.EFFDT >= :1)
--:1 bind 1 variable should be DD-MON-YYYY format. The date in which to pull all 2022 or 2023 general deduction data only.
-- in 2022 it was the 12/26/2021 date for 2022. As benefits loaded data into general deductions for 2022 open enrollment on 12/26/2021.
AND (E.DEDUCTION_END_DT >= :2 OR E.DEDUCTION_END_DT IS NULL)
--:2 bind 2 variable should be DD-MON-YYYY format. The date should be greater than the last processed pay end date. As the deduction
--end date must be greater than or equal to the pay end date to not be pulled.
AND (E.GOAL_BAL <> E.GOAL_AMT OR E.GOAL_BAL = 0 AND E.GOAL_AMT = 0)
AND E.EMPLID = D.EMPLID
AND D.NAME_TYPE = 'PRI'
AND D.EFFDT = (SELECT MAX(G.EFFDT) FROM PS_NAMES G
WHERE D.EMPLID = G.EMPLID
AND D.NAME_TYPE = G.NAME_TYPE
AND G.EFFDT <= SYSDATE)
AND E.EMPLID = A.EMPLID AND A.PER_ORG = 'EMP'
AND A.JOB_INDICATOR = 'P'
AND A.HR_STATUS = 'I'
AND A.PAYGROUP NOT IN ('ESM','PSM','XSM')
AND A.EFFDT = (SELECT MAX(B.EFFDT) FROM PS_JOB B
WHERE A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND B.EFFDT <= :2 AND
A.EFFSEQ = (SELECT MAX(C.EFFSEQ) FROM PS_JOB C WHERE C.EMPLID = A.EMPLID
AND C.EMPL_RCD = A.EMPL_RCD AND C.EFFDT = A.EFFDT))
AND E.DEDCD IN ('ISPREC','DCWREC','KDPREC','MISC','ACCREC')
AND E.DED_CALC NOT IN ('D')
--AND E.DEDCD IN ('PRYROV', 'ADVPAY')
AND E.DED_RATE_PCT <> 0
AND (E.GOAL_AMT - E.GOAL_BAL) <> 0
AND E.DED_RATE_PCT <> 0
UNION all
SELECT D.FIRST_NAME || ' ' || D.LAST_NAME AS "Worker Name"
,X.TO_VALUE_STR50 as "WORKER ID"
,(SELECT X.TO_VALUE_STR50 FROM PS_HK_XWALK_TBL X WHERE X.FIELDNAME='POSITION_NBR' AND X.FROM_VALUE_STR50= A.EMPLID||A.POSITION_NBR||A.EMPL_RCD AND X.HK_ENTITY_NAME='BH') AS "POSITION ID"
,'' AS "Payroll Input"
,'Payroll_P1_DEDUCTION_INPUT' as "Batch ID"
,'' as "Source Reference ID"
,CASE WHEN E.DEDUCTION_END_DT IS NULL THEN 'Y' ELSE 'N' END AS "Ongoing Input"
,TO_CHAR(E.EFFDT, 'YYYY-MM-DD') AS "Start Date"
,CASE WHEN E.DEDCD IN ('DENPRE','FSAHLT','GTLINC','HOSIND','IHCEFT','MEDPRE','MEDSRC','VISPRE','VOLADD','LEGALI','SUPLIF','CHLIFE','HSA-EE',
'SPLIFE','ACCINS','CRITIL','FSADEP','DEPSRC','HSA-ER','STDINS') THEN '2023-12-16' ELSE TO_CHAR(E.DEDUCTION_END_DT, 'YYYY-MM-DD') END AS "End Date"
,'' AS "Run Category Reference ID"
,'' AS "Earning Reference ID"
,(SELECT V.TO_VALUE_STR50 FROM PS_HK_XWALK_TBL V WHERE V.FIELDNAME = 'DEDCD' AND V.HK_ENTITY_NAME = 'BH' AND V.FROM_DATE = '01-JAN-1900'
AND V.FROM_VALUE_STR50 = E.DEDCD) AS "Deduction Reference ID"
,CASE WHEN E.DED_ADDL_AMT <> 0 THEN TO_CHAR(E.DED_ADDL_AMT,'99999999.99') END AS "Amount"
,'' AS "Hours"
,'' AS "Rate"
,'Y' AS "Adjustment"
,'' AS "Location Reference ID"
,'' AS "Region Reference ID"
,'' AS "Job Profile Reference ID"
,'' AS "Cost Center Reference ID"
,'' AS "Project Reference ID"
,'' AS "Project Phase Reference ID"
,'' AS "Project Task Reference ID"
,'' AS "Order Case Reference ID"
,'' AS "State Authority Reference ID"
,'' AS "Workers Compensation Ref ID"
,'' AS "County Authority Reference ID"
,'' AS "City Authority Reference ID"
,'' AS "School District Reference ID"
,'' AS "Custom Worktag 1 Reference ID"
,'' AS "Custom Worktag 2 Reference ID"
,'' AS "Custom Worktag 3 Reference ID"
,'' AS "Fund Reference ID"
,'' AS "Grant Reference ID"
,'' AS "Gift Reference ID"
,'' AS "Program Reference ID"
,'' AS "Business Unit Reference ID"
,'' AS "Object Class Reference ID"
,'' AS "Custom Organization Ref ID"
,'' AS "Local Other Tax Auth Ref ID"
,'' AS "NI Category Reference ID"
,'' AS "ARRCO-AGIRC Category Ref ID"
, CASE WHEN (E.GOAL_AMT - E.GOAL_BAL) <> 0 AND E.DEDCD IN ('MISC','ACCREC', 'DCWREC', 'ISPREC', 'KDPREC', 'PRYROV','HSA-EE', 'FSADEP', 'FSAHLT', 'BFFOUN2') THEN 'W_ORIG'
ELSE case when E.DED_RATE_PCT <> 0 AND E.DEDCD IN ('MISC','ACCREC', 'DCWREC', 'ISPREC', 'KDPREC', 'PRYROV') THEN 'W_PCT' END END AS "Related Calculation Ref ID"
, CASE WHEN (E.GOAL_AMT - E.GOAL_BAL) <> 0 AND E.DEDCD IN ('MISC','ACCREC', 'DCWREC', 'ISPREC', 'KDPREC', 'PRYROV','HSA-EE', 'FSADEP', 'FSAHLT', 'BFFOUN2') THEN (E.GOAL_AMT - E.GOAL_BAL)
ELSE CASE WHEN E.DED_RATE_PCT <> 0 AND E.DEDCD IN ('MISC','ACCREC', 'DCWREC', 'ISPREC', 'KDPREC', 'PRYROV') THEN (E.DED_RATE_PCT/100) END END AS "Related Calculation Input"
,'' AS "Currency ID Reference"
,'' AS "Match Existing Input"
,'' AS "Company Reference ID"
,'' AS "Costing Company Reference ID"
,'' AS "Coverage Start Date"
,'' AS "Coverage End Date"
--, row_number() over(partition by d.emplid order by e.emplid, e.dedcd) as "Unique ID"
--,ROW_NUMBER() OVER (PARTITION BY E.EMPLID, E.DEDCD ORDER BY E.EMPLID) + 1
,X.FROM_VALUE_STR50 AS "Legacy ID"
,E.DEDCD AS "LEGACY DEDUCTION CODE"
,E.GOAL_AMT AS "goal amount"
,E.GOAL_BAL as "Amt deducted"
FROM PS_HK_XWALK_TBL X, PS_JOB A, PS_NAMES D, PS_GENL_DEDUCTION E
WHERE X.FIELDNAME='PERORG_EMP' AND X.HK_ENTITY_NAME='BH' AND X.FROM_DATE='01-JAN-1900'
AND E.EMPLID = X.FROM_VALUE_STR50
AND E.EFFDT = (SELECT MAX(F.EFFDT) FROM PS_GENL_DEDUCTION F
WHERE F.EMPLID = E.EMPLID AND F.COMPANY = E.COMPANY
AND F.DEDCD = E.DEDCD AND F.EFFDT >= :1)
--:1 bind 1 variable should be DD-MON-YYYY format. The date in which to pull all 2022 or 2023 general deduction data only.
-- in 2022 it was the 12/26/2021 date for 2022. As benefits loaded data into general deductions for 2022 open enrollment on 12/26/2021.
AND (E.DEDUCTION_END_DT >= :2 OR E.DEDUCTION_END_DT IS NULL)
--:2 bind 2 variable should be DD-MON-YYYY format. The date should be greater than the last processed pay end date. As the deduction
--end date must be greater than or equal to the pay end date to not be pulled.
AND (E.GOAL_BAL <> E.GOAL_AMT OR E.GOAL_BAL = 0 AND E.GOAL_AMT = 0)
AND E.EMPLID = D.EMPLID
AND D.NAME_TYPE = 'PRI'
AND D.EFFDT = (SELECT MAX(G.EFFDT) FROM PS_NAMES G
WHERE D.EMPLID = G.EMPLID
AND D.NAME_TYPE = G.NAME_TYPE
AND G.EFFDT <= SYSDATE)
AND E.EMPLID = A.EMPLID AND A.PER_ORG = 'EMP'
AND A.JOB_INDICATOR = 'P'
AND A.HR_STATUS = 'I'
AND A.PAYGROUP NOT IN ('ESM','PSM','XSM')
AND A.EFFDT = (SELECT MAX(B.EFFDT) FROM PS_JOB B
WHERE A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD and B.EFFDT between
(to_date(:2,'DD-MON-YYYY') - 14) and :2 AND
A.EFFSEQ = (SELECT MAX(C.EFFSEQ) FROM PS_JOB C WHERE C.EMPLID = A.EMPLID
AND C.EMPL_RCD = A.EMPL_RCD AND C.EFFDT = A.EFFDT))
AND E.DEDCD IN ('ISPREC','DCWREC','KDPREC','MISC','ACCREC')
AND E.DED_CALC NOT IN ('D')
--AND E.DEDCD IN ('PRYROV', 'ADVPAY')
AND E.DED_RATE_PCT = 0
--AND (E.GOAL_AMT - E.GOAL_BAL) <> 0
ORDER BY 1,12,51;