/* Garnishment Report */
WITH EMPLOYMENT_DETAILS_DS AS (
SELECT PPOS.PERSON_ID PERID
, PPOS.DATE_START LAST_HIRE_DT
, PPOS.TERM_DATE
, PPOS.LDG
, PAPF.PERSON_NUMBER PERNUM
, PPNF.FIRST_NAME FNAME
, PPNF.LAST_NAME LNAME
, PPNF.MIDDLE_NAMES MNAME
, XEP.LEGAL_ENTITY_IDENTIFIER COMPANY
, XEP.NAME CMP_DESCR
FROM (SELECT P.PERSON_ID, P.PERIOD_OF_SERVICE_ID, P.DATE_START, P.ACTUAL_TERMINATION_DATE TERM_DATE, P.LEGISLATION_CODE LDG
, ROW_NUMBER() OVER (PARTITION BY P.PERSON_ID ORDER BY P.DATE_START DESC) ROW_ID
FROM PER_PERIODS_OF_SERVICE P WHERE P.DATE_START <= :P_PAYENDDATE) PPOS
, PER_ALL_PEOPLE_F PAPF
, PER_PERSON_NAMES_F PPNF
, PER_ALL_ASSIGNMENTS_M PAAM
, HR_ALL_ORGANIZATION_UNITS_F_VL HAOUFV_LE
, XLE_ENTITY_PROFILES XEP
WHERE 1=1
AND PPOS.ROW_ID = 1
/* AND (PPOS.ACTUAL_TERMINATION_DATE IS NULL OR PPOS.ACTUAL_TERMINATION_DATE > SYSDATE) */
AND PAPF.PERSON_ID = PPOS.PERSON_ID
AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND PPNF.PERSON_ID = PPOS.PERSON_ID
AND PPNF.NAME_TYPE = 'GLOBAL'
AND SYSDATE BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE
AND PAAM.PERSON_ID = PPOS.PERSON_ID
AND PAAM.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
AND PAAM.ASSIGNMENT_TYPE = 'E'
AND PAAM.PRIMARY_FLAG = 'Y'
AND PAAM.EFFECTIVE_LATEST_CHANGE = 'Y'
AND SYSDATE BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE
AND HAOUFV_LE.ORGANIZATION_ID = PAAM.LEGAL_ENTITY_ID
AND SYSDATE BETWEEN HAOUFV_LE.EFFECTIVE_START_DATE AND HAOUFV_LE.EFFECTIVE_END_DATE
AND XEP.LEGAL_ENTITY_ID = HAOUFV_LE.LEGAL_ENTITY_ID
)
SELECT PPRD.PERSON_ID PERID
, PDCCDT.COMPONENT_NAME
, PBTT.BALANCE_TYPE_ID
, PDCCF.DIR_CARD_ID
, PDCCF.DIR_CARD_COMP_ID
, PDCCF.DIR_CARD_COMP_DEF_ID
, PDCCF.CONTEXT_VALUE1
, PDCCF.CONTEXT_VALUE2
, DECODE(PDCCF.CONTEXT_VALUE2, NULL, PDCCF.CONTEXT_VALUE1, PDCCF.CONTEXT_VALUE2) ORDER_NUM
, TO_CHAR(PDCCF.EFFECTIVE_START_DATE, 'YYYYMMDD') EFFECTIVE_START_DATE
, PDCCF.EFFECTIVE_END_DATE
, PDCDF.EFFECTIVE_END_DATE CALC_END_DATE
, PDCDF.DIR_INFORMATION_NUMBER1 ORDER_ID
, PDCDF_CON.DIR_INFORMATION_CHAR1 CONTACT_TYPE
, PDCDF_CON.DIR_INFORMATION_CHAR2 || PDCDF_CON.DIR_INFORMATION_CHAR4 CONTACT_NAME
, PDCDF_CON.DIR_INFORMATION_CHAR5 CONTACT_ADDR_LN1
, PDCDF_CON.DIR_INFORMATION_CHAR7 CONTACT_CITY
, PDCDF_CON.DIR_INFORMATION_CHAR9 CONTACT_STATE
, PDCDF_CON.DIR_INFORMATION_CHAR10 CONTACT_ZIP
, PDCDF_CON.DIR_INFORMATION_CHAR14 CONTACT_OTH_PH
, PRIF.VALUE1 OWED_AMOUNT
, HP.ADDRESS1 VEN_ADDR1
, HP.ADDRESS2 VEN_ADDR2
, HP.CITY VEN_CITY
, HP.POSTAL_CODE VEN_ZIP
, HP.STATE VEN_STATE
, HP.PARTY_NAME COURT_NAME
, HP.PRIMARY_PHONE_AREA_CODE||'/'||HP.PRIMARY_PHONE_NUMBER VEN_PH
, EDD.PERNUM
, EDD.LAST_HIRE_DT
, EDD.TERM_DATE
, EDD.FNAME
, EDD.LNAME
, EDD.MNAME
, EDD.COMPANY
, EDD.CMP_DESCR
, EDD.LDG
FROM PAY_DIR_CARD_COMP_DEFS_TL PDCCDT
, PAY_BALANCE_TYPES_TL PBTT
, PAY_DIR_CARD_COMPONENTS_F PDCCF
, (SELECT P.PERSON_ID, P.DIR_CARD_COMP_ID, P.DIR_COMP_FLEX_ID, P.EFFECTIVE_START_DATE, P.EFFECTIVE_END_DATE, P.DIR_INFORMATION_NUMBER1, P.DIR_INFORMATION_CATEGORY
, ROW_NUMBER() OVER (PARTITION BY P.PERSON_ID, P.DIR_COMP_FLEX_ID ORDER BY P.EFFECTIVE_START_DATE DESC) ROW_ID
FROM PAY_DIR_COMP_DETAILS_F P WHERE P.DIR_INFORMATION_CATEGORY IN ('INVLN_DEDN_PAYEE_DETAILS')) PDCDF
, PAY_DIR_COMP_DETAILS_F PDCDF_CON
, PAY_DIR_CARDS_F PDCF
, PAY_PAY_RELATIONSHIPS_DN PPRD
, PAY_VALUE_DEFINITIONS_F PVDF
, PAY_RANGE_ITEMS_F PRIF
, HZ_PARTIES HP
-- , PAYROLL_DS PD
, EMPLOYMENT_DETAILS_DS EDD
WHERE 1=1
AND ((COALESCE(null, :P_ELEMENTS) IS NULL) OR PDCCDT.COMPONENT_NAME IN(:P_ELEMENTS))
/* AND PDCCDT.COMPONENT_NAME IN('Federal Tax Levy','Garnishment','Garnishment Alimony','Garnishment Bankruptcy'
,'Garnishment Child Support','Garnishment DCIA','Garnishment Social Security AWG','Garnishment Spousal Support'
,'Garnishment State Tax Levy','Garnishment Student Loan','Garnishment Tax Levy','Garnishment Wage Assignment','Garnishment Writ') */
AND PDCCDT.LANGUAGE = 'US'
AND PBTT.LANGUAGE = 'US'
AND PBTT.BALANCE_NAME = PDCCDT.COMPONENT_NAME
AND PDCCF.DIR_CARD_COMP_DEF_ID = PDCCDT.DIR_CARD_COMP_DEF_ID
AND (:P_PAYENDDATE BETWEEN PDCCF.EFFECTIVE_START_DATE AND PDCCF.EFFECTIVE_END_DATE
OR PDCCF.EFFECTIVE_END_DATE BETWEEN :P_PAYBEGINDATE AND :P_PAYENDDATE)
AND PDCDF.DIR_CARD_COMP_ID = PDCCF.DIR_CARD_COMP_ID
AND PDCDF.EFFECTIVE_END_DATE = PDCCF.EFFECTIVE_END_DATE
AND PDCDF.ROW_ID in (1,2)
--AND PDCDF.ROW_ID = 1
AND PDCDF_CON.DIR_CARD_COMP_ID(+) = PDCCF.DIR_CARD_COMP_ID
AND PDCDF_CON.EFFECTIVE_END_DATE(+) = PDCCF.EFFECTIVE_END_DATE
AND PDCDF_CON.DIR_INFORMATION_CATEGORY(+) IN ('HRX_US_INV_DEDN_CONTACT_INFO_DATA','ORA_HRX_CA_INV_DEDN_DATA')
AND PDCF.DIR_CARD_ID = PDCCF.DIR_CARD_ID
AND :P_PAYENDDATE BETWEEN PDCF.EFFECTIVE_START_DATE AND PDCF.EFFECTIVE_END_DATE
AND PPRD.PAYROLL_RELATIONSHIP_ID = PDCF.PAYROLL_RELATIONSHIP_ID
AND :P_PAYENDDATE BETWEEN PPRD.START_DATE AND PPRD.END_DATE
AND PVDF.SOURCE_ID = PDCCF.DIR_CARD_COMP_ID
AND PVDF.BASE_NAME LIKE '%OWED_AMOUNT%'
AND PVDF.EFFECTIVE_END_DATE = PDCDF.EFFECTIVE_END_DATE
AND PRIF.VALUE_DEFN_ID = PVDF.VALUE_DEFN_ID
AND PRIF.EFFECTIVE_END_DATE = PVDF.EFFECTIVE_END_DATE
AND HP.PARTY_ID(+) = PDCDF.DIR_INFORMATION_NUMBER1
AND EDD.PERID = PPRD.PERSON_ID
--AND PPRD.PERSON_ID IN (300000040035544,100000932652856,100000476987486,100000476571614)
/* Garnishment Report with payroll deductions */
with payroll_ds as (
SELECT PPRD.PERSON_ID PERID
, PB.BALANCE_NAME
, PRR.THIRD_PARTY_PAYEE_ID ORDER_ID
, sum(PRRV.RESULT_VALUE) PAID_AMOUNT
FROM PAY_PAY_RELATIONSHIPS_DN PPRD
,PAY_PAYROLL_REL_ACTIONS PPRA
,PAY_PAYROLL_ACTIONS PPA
,PAY_RUN_RESULTS PRR
,PAY_RUN_RESULT_VALUES PRRV
,PAY_BALANCE_FEEDS_F PBF
,PAY_BALANCE_TYPES_TL PB
WHERE PPRA.PAYROLL_RELATIONSHIP_ID = PPRD.PAYROLL_RELATIONSHIP_ID
AND PPRA.RETRO_COMPONENT_ID IS NULL
AND PPRA.ACTION_STATUS = 'C'
AND PPA.PAYROLL_ACTION_ID = PPRA.PAYROLL_ACTION_ID
AND PPA.ACTION_TYPE IN ('Q','R','V','I')
AND PPA.ACTION_STATUS = 'C'
AND PPA.EFFECTIVE_DATE IS NOT NULL
AND PPA.DATE_EARNED IS NOT NULL
-- AND PPA.EFFECTIVE_DATE BETWEEN To_DATE('03012018', 'MMDDYYYY') AND To_DATE('03092019', 'MMDDYYYY')
AND PRR.PAYROLL_REL_ACTION_ID = PPRA.PAYROLL_REL_ACTION_ID
AND PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID
-- AND PRR.THIRD_PARTY_PAYEE_ID = 300001881261457
AND PRRV.RESULT_VALUE <> '0'
AND PRRV.INPUT_VALUE_ID = PBF.INPUT_VALUE_ID
AND PPA.EFFECTIVE_DATE BETWEEN PBF.EFFECTIVE_START_DATE AND PBF.EFFECTIVE_END_DATE
AND PB.BALANCE_TYPE_ID = PBF.BALANCE_TYPE_ID
AND PB.LANGUAGE = 'US'
AND PB.BALANCE_NAME IN('Federal Tax Levy','Garnishment','Garnishment Alimony','Garnishment Bankruptcy'
,'Garnishment Child Support','Garnishment DCIA','Garnishment Social Security AWG','Garnishment Spousal Support'
,'Garnishment State Tax Levy','Garnishment Student Loan','Garnishment Tax Levy','Garnishment Wage Assignment','Garnishment Writ')
-- AND PPRD.PERSON_ID IN (300000046765401, 100001045183643)
GROUP BY PPRD.PERSON_ID
, PB.BALANCE_NAME
, PRR.THIRD_PARTY_PAYEE_ID
)
, LAST_PAYROLL_DEDUCTION_DS AS (
SELECT PPRD.PERSON_ID PERID
, PB.BALANCE_NAME
, PPA.EFFECTIVE_DATE
, PRR.THIRD_PARTY_PAYEE_ID ORDER_ID
, sum(PRRV.RESULT_VALUE) PAID_AMOUNT
, row_number() over (partition by pprd.person_id order by ppa.effective_date desc) row_id
FROM PAY_PAY_RELATIONSHIPS_DN PPRD
,PAY_PAYROLL_REL_ACTIONS PPRA
,PAY_PAYROLL_ACTIONS PPA
,PAY_RUN_RESULTS PRR
,PAY_RUN_RESULT_VALUES PRRV
,PAY_BALANCE_FEEDS_F PBF
,PAY_BALANCE_TYPES_TL PB
WHERE PPRA.PAYROLL_RELATIONSHIP_ID = PPRD.PAYROLL_RELATIONSHIP_ID
AND PPRA.RETRO_COMPONENT_ID IS NULL
AND PPRA.ACTION_STATUS = 'C'
AND PPA.PAYROLL_ACTION_ID = PPRA.PAYROLL_ACTION_ID
AND PPA.ACTION_TYPE IN ('Q','R','V','I')
AND PPA.ACTION_STATUS = 'C'
AND PPA.EFFECTIVE_DATE IS NOT NULL
AND PPA.DATE_EARNED IS NOT NULL
AND PPA.EFFECTIVE_DATE BETWEEN To_DATE('03022019', 'MMDDYYYY') AND To_DATE('03152019', 'MMDDYYYY')
AND PRR.PAYROLL_REL_ACTION_ID = PPRA.PAYROLL_REL_ACTION_ID
AND PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID
-- AND PRR.THIRD_PARTY_PAYEE_ID = 300001881261457
AND PRRV.RESULT_VALUE <> '0'
AND PRRV.INPUT_VALUE_ID = PBF.INPUT_VALUE_ID
AND PPA.EFFECTIVE_DATE BETWEEN PBF.EFFECTIVE_START_DATE AND PBF.EFFECTIVE_END_DATE
AND PB.BALANCE_TYPE_ID = PBF.BALANCE_TYPE_ID
AND PB.LANGUAGE = 'US'
AND PB.BALANCE_NAME IN('Federal Tax Levy','Garnishment','Garnishment Alimony','Garnishment Bankruptcy'
,'Garnishment Child Support','Garnishment DCIA','Garnishment Social Security AWG','Garnishment Spousal Support'
,'Garnishment State Tax Levy','Garnishment Student Loan','Garnishment Tax Levy','Garnishment Wage Assignment','Garnishment Writ')
-- AND PPRD.PERSON_ID IN (300000046765401, 100001045183643)
GROUP BY PPRD.PERSON_ID
, PB.BALANCE_NAME
, PPA.EFFECTIVE_DATE
, PRR.THIRD_PARTY_PAYEE_ID
)
, EMPLOYMENT_DETAILS_DS AS (
SELECT PPOS.PERSON_ID PERID
, PPOS.DATE_START LAST_HIRE_DT
, PAPF.PERSON_NUMBER PERNUM
, PPNF.FIRST_NAME FNAME
, PPNF.LAST_NAME LNAME
, PPNF.MIDDLE_NAMES MNAME
, XEP.LEGAL_ENTITY_IDENTIFIER COMPANY
FROM (SELECT P.PERSON_ID, P.PERIOD_OF_SERVICE_ID, P.DATE_START, P.ACTUAL_TERMINATION_DATE
, ROW_NUMBER() OVER (PARTITION BY P.PERSON_ID ORDER BY P.DATE_START DESC) ROW_ID
FROM PER_PERIODS_OF_SERVICE P) PPOS
, PER_ALL_PEOPLE_F PAPF
, PER_PERSON_NAMES_F PPNF
, PER_ALL_ASSIGNMENTS_M PAAM
, HR_ALL_ORGANIZATION_UNITS_F_VL HAOUFV_LE
, XLE_ENTITY_PROFILES XEP
WHERE 1=1
AND PPOS.ROW_ID = 1
/* AND (PPOS.ACTUAL_TERMINATION_DATE IS NULL OR PPOS.ACTUAL_TERMINATION_DATE > SYSDATE) */
AND PAPF.PERSON_ID = PPOS.PERSON_ID
AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND PPNF.PERSON_ID = PPOS.PERSON_ID
AND PPNF.NAME_TYPE = 'GLOBAL'
AND SYSDATE BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE
AND PAAM.PERSON_ID = PPOS.PERSON_ID
AND PAAM.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
AND PAAM.ASSIGNMENT_TYPE = 'E'
AND PAAM.PRIMARY_FLAG = 'Y'
AND PAAM.EFFECTIVE_LATEST_CHANGE = 'Y'
AND SYSDATE BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE
AND HAOUFV_LE.ORGANIZATION_ID = PAAM.LEGAL_ENTITY_ID
AND SYSDATE BETWEEN HAOUFV_LE.EFFECTIVE_START_DATE AND HAOUFV_LE.EFFECTIVE_END_DATE
AND XEP.LEGAL_ENTITY_ID = HAOUFV_LE.LEGAL_ENTITY_ID
)
SELECT PPRD.PERSON_ID PERID
, PDCCDT.COMPONENT_NAME
, PDCCF.DIR_CARD_ID
, PDCCF.DIR_CARD_COMP_ID
, PDCCF.DIR_CARD_COMP_DEF_ID
, PDCCF.CONTEXT_VALUE1
, PDCCF.CONTEXT_VALUE2
, PDCCF.EFFECTIVE_START_DATE
, PDCCF.EFFECTIVE_END_DATE
, PDCDF.DIR_INFORMATION_NUMBER1 ORDER_ID
, PRIF.VALUE1 OWED_AMOUNT
, HP.STATE ORDERED_STATE
, PD.PAID_AMOUNT
, LPDD.EFFECTIVE_DATE
, EDD.PERNUM
, EDD.LAST_HIRE_DT
, EDD.FNAME
, EDD.LNAME
, EDD.MNAME
, EDD.COMPANY
FROM PAY_DIR_CARD_COMP_DEFS_TL PDCCDT
, PAY_DIR_CARD_COMPONENTS_F PDCCF
, PAY_DIR_COMP_DETAILS_F PDCDF
, PAY_DIR_CARDS_F PDCF
, PAY_PAY_RELATIONSHIPS_DN PPRD
, PAY_VALUE_DEFINITIONS_F PVDF
, PAY_RANGE_ITEMS_F PRIF
, HZ_PARTIES HP
, PAYROLL_DS PD
, LAST_PAYROLL_DEDUCTION_DS LPDD
, EMPLOYMENT_DETAILS_DS EDD
WHERE 1=1
-- AND ((COALESCE(null, :P_GARN_TYPE) IS NULL) OR PDCCDT.COMPONENT_NAME IN(:P_GARN_TYPE))
AND PDCCDT.COMPONENT_NAME IN('Federal Tax Levy','Garnishment','Garnishment Alimony','Garnishment Bankruptcy'
,'Garnishment Child Support','Garnishment DCIA','Garnishment Social Security AWG','Garnishment Spousal Support'
,'Garnishment State Tax Levy','Garnishment Student Loan','Garnishment Tax Levy','Garnishment Wage Assignment','Garnishment Writ')
AND PDCCDT.LANGUAGE = 'US'
AND PDCCF.DIR_CARD_COMP_DEF_ID = PDCCDT.DIR_CARD_COMP_DEF_ID
-- AND PDCCF.DIR_CARD_COMP_ID IN (300001617570565, 300001955299374, 300001955333775)
AND (TO_DATE('03152019', 'MMDDYYYY') BETWEEN PDCCF.EFFECTIVE_START_DATE AND PDCCF.EFFECTIVE_END_DATE
OR PDCCF.EFFECTIVE_END_DATE BETWEEN TO_DATE('03022019', 'MMDDYYYY') AND TO_DATE('03152019', 'MMDDYYYY'))
AND PDCDF.DIR_CARD_COMP_ID = PDCCF.DIR_CARD_COMP_ID
AND PDCDF.EFFECTIVE_END_DATE = PDCCF.EFFECTIVE_END_DATE
AND PDCDF.DIR_INFORMATION_CATEGORY IN ('INVLN_DEDN_PAYEE_DETAILS')
AND PDCF.DIR_CARD_ID = PDCCF.DIR_CARD_ID
AND TO_DATE('03152019', 'MMDDYYYY') BETWEEN PDCF.EFFECTIVE_START_DATE AND PDCF.EFFECTIVE_END_DATE
AND PPRD.PAYROLL_RELATIONSHIP_ID = PDCF.PAYROLL_RELATIONSHIP_ID
AND TO_DATE('03152019', 'MMDDYYYY') BETWEEN PPRD.START_DATE AND PPRD.END_DATE
AND PVDF.SOURCE_ID = PDCDF.DIR_CARD_COMP_ID
AND PVDF.BASE_NAME LIKE '%OWED_AMOUNT%'
AND PVDF.EFFECTIVE_END_DATE = PDCDF.EFFECTIVE_END_DATE
AND PRIF.VALUE_DEFN_ID = PVDF.VALUE_DEFN_ID
AND PRIF.EFFECTIVE_END_DATE = PVDF.EFFECTIVE_END_DATE
AND HP.PARTY_ID(+) = PDCDF.DIR_INFORMATION_NUMBER1
AND PD.PERID = PPRD.PERSON_ID
AND PD.BALANCE_NAME = PDCCDT.COMPONENT_NAME
AND PD.ORDER_ID = PDCDF.DIR_INFORMATION_NUMBER1
AND LPDD.PERID = PPRD.PERSON_ID
AND LPDD.BALANCE_NAME = PDCCDT.COMPONENT_NAME
AND LPDD.ORDER_ID = PDCDF.DIR_INFORMATION_NUMBER1
AND LPDD.ROW_ID = 1
AND EDD.PERID = PPRD.PERSON_ID
AND PPRD.PERSON_ID IN (300000040190894)
No comments:
Post a Comment