View: PAY_US_DEDUCTIONS_REPORT_V

Product:	PAY - Payroll
Description:	This view returns all the deductions for an employee
Implementation/DBA Data:	APPS.PAY_US_DEDUCTIONS_REPORT_V

View Text

SELECT PPA.CONSOLIDATION_SET_ID
, PCS.CONSOLIDATION_SET_NAME
, PPA.PAYROLL_ID
, PAP.PAYROLL_NAME
, PAP.EFFECTIVE_START_DATE
, PAP.EFFECTIVE_END_DATE
, PPA.PAYROLL_ACTION_ID
, PPA.EFFECTIVE_DATE
, PPA.DATE_EARNED
, PTP.TIME_PERIOD_ID
, PTP.PERIOD_NAME
, PTP.PERIOD_NUM
, PTP.PERIOD_TYPE
, PTP.START_DATE
, PTP.END_DATE
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PAA.TAX_UNIT_ID
, HOU1.NAME
, PAAS.PERSON_ID
, PAPP.EMPLOYEE_NUMBER
, PAPP.FULL_NAME
, PAAS.ORGANIZATION_ID
, HOU.NAME
, PAAS.LOCATION_ID
, HRL.LOCATION_CODE
, HRL.DESCRIPTION
, PAAS.ASSIGNMENT_SEQUENCE
, PAAS.ASSIGNMENT_NUMBER
, PET.ELEMENT_TYPE_ID
, PET.ELEMENT_NAME
, PET.DESCRIPTION
, PET.BUSINESS_GROUP_ID
, PET.ELEMENT_INFORMATION10
, PET.ELEMENT_INFORMATION11
, PEC.CLASSIFICATION_ID
, PEC.CLASSIFICATION_NAME
, PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM(UPPER(PET.ELEMENT_NAME)
, 'ASG_GRE_RUN'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, NULL
, PAA.TAX_UNIT_ID
, PET.BUSINESS_GROUP_ID
, NULL) PRIMARY_BALANCE
, PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM(UPPER(PET.ELEMENT_NAME)||' NOT TAKEN'
, 'ASG_GRE_RUN'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, NULL
, PAA.TAX_UNIT_ID
, PET.BUSINESS_GROUP_ID
, NULL) NOT_TAKEN_BALANCE
, PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM(UPPER(PET.ELEMENT_NAME)||' ARREARS'
, 'ASG_GRE_RUN'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, NULL
, PAA.TAX_UNIT_ID
, PET.BUSINESS_GROUP_ID
, NULL) ARREARS_BALANCE
, DECODE(SIGN(TO_NUMBER(DECODE(PET.ELEMENT_INFORMATION11
, NULL
, NULL
, PAY_US_DEDN_PKG.PAY_US_TOT_OWED( PAA.ASSIGNMENT_ID
, PET.ELEMENT_TYPE_ID
, PPA.EFFECTIVE_DATE
, PPA.DATE_EARNED))))
, NULL
, NULL
, -1
, NULL
, 0
, NULL
, PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(PET.ELEMENT_NAME)||' ACCRUED'
, 'ASG_GRE_ITD'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, NULL
, PAA.TAX_UNIT_ID
, PET.BUSINESS_GROUP_ID
, NULL)) ACCRUED_BALANCE
, TO_NUMBER(DECODE(PET.ELEMENT_INFORMATION11
, NULL
, NULL
, PAY_US_DEDN_PKG.PAY_US_TOT_OWED( PAA.ASSIGNMENT_ID
, PET.ELEMENT_TYPE_ID
, PPA.EFFECTIVE_DATE
, PPA.DATE_EARNED))) TOTAL_OWED
FROM PAY_PAYROLL_ACTIONS PPA
, PAY_CONSOLIDATION_SETS PCS
, PAY_ALL_PAYROLLS_F PAP
, PER_TIME_PERIODS PTP
, PAY_ASSIGNMENT_ACTIONS PAA
, HR_ALL_ORGANIZATION_UNITS HOU1
, PER_ALL_ASSIGNMENTS_F PAAS
, HR_ORGANIZATION_UNITS HOU
, HR_LOCATIONS_ALL HRL
, PAY_RUN_RESULTS RR
, PAY_ELEMENT_TYPES_F PET
, PAY_ELEMENT_CLASSIFICATIONS PEC
, PER_ALL_PEOPLE_F PAPP
, PAY_BALANCE_FEEDS_F FEED
WHERE PPA.ACTION_TYPE IN ('Q'
, 'R'
, 'V'
, 'B'
, 'I')
AND PPA.ACTION_STATUS = 'C'
AND PPA.CONSOLIDATION_SET_ID = PCS.CONSOLIDATION_SET_ID
AND PPA.PAYROLL_ID = PAP.PAYROLL_ID
AND NVL(PPA.DATE_EARNED
, PPA.EFFECTIVE_DATE) BETWEEN PAP.EFFECTIVE_START_DATE
AND PAP.EFFECTIVE_END_DATE
AND PPA.TIME_PERIOD_ID = PTP.TIME_PERIOD_ID
AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PAA.ACTION_STATUS = 'C'
AND PAA.TAX_UNIT_ID = HOU1.ORGANIZATION_ID
AND PAAS.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND NVL(PPA.DATE_EARNED
, PPA.EFFECTIVE_DATE) BETWEEN PAAS.EFFECTIVE_START_DATE
AND PAAS.EFFECTIVE_END_DATE
AND PAAS.ORGANIZATION_ID = HOU.ORGANIZATION_ID
AND PAAS.LOCATION_ID = HRL.LOCATION_ID
AND RR.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND RR.STATUS IN ('P'
, 'PA')
AND EXISTS(SELECT 'EXISTS'
FROM PAY_RUN_RESULT_VALUES TARGET
WHERE TARGET.RUN_RESULT_ID = RR.RUN_RESULT_ID
AND FEED.INPUT_VALUE_ID = TARGET.INPUT_VALUE_ID
AND NVL(TARGET.RESULT_VALUE
, '0') <> '0' )
AND NVL(PPA.DATE_EARNED
, PPA.EFFECTIVE_DATE) BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE
AND ELEMENT_NAME NOT LIKE '%SPECIAL FEATURES'
AND ELEMENT_NAME NOT LIKE '%SPECIAL INPUTS'
AND ELEMENT_NAME NOT LIKE '%WITHHOLDING'
AND ELEMENT_NAME NOT LIKE '%VERIFIER'
AND ELEMENT_NAME NOT LIKE '%PRIORITY'
AND ELEMENT_NAME NOT LIKE '%TAXABLE BY JD'
AND ELEMENT_NAME NOT LIKE '%CALCULATOR'
AND PEC.LEGISLATION_CODE = 'US'
AND PEC.CLASSIFICATION_NAME IN ('PRE-TAX DEDUCTIONS'
, 'INVOLUNTARY DEDUCTIONS'
, 'VOLUNTARY DEDUCTIONS'
, 'EMPLOYER LIABILITIES')
AND PET.CLASSIFICATION_ID = PEC.CLASSIFICATION_ID
AND PAAS.PERSON_ID = PAPP.PERSON_ID
AND NVL(PPA.DATE_EARNED
, PPA.EFFECTIVE_DATE) BETWEEN PAPP.EFFECTIVE_START_DATE
AND PAPP.EFFECTIVE_END_DATE
AND PPA.EFFECTIVE_DATE BETWEEN FEED.EFFECTIVE_START_DATE
AND FEED.EFFECTIVE_END_DATE
AND FEED.BALANCE_TYPE_ID IN (NVL(TRANSLATE(PET.ELEMENT_INFORMATION10
, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ'
, '01234567890000000000000000000000000000000000000000000000000000')
, '0')
, NVL(TRANSLATE(PET.ELEMENT_INFORMATION12
, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ'
, '01234567890000000000000000000000000000000000000000000000000000')
, '0')
, NVL(TRANSLATE(PET.ELEMENT_INFORMATION13
, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ'
, '01234567890000000000000000000000000000000000000000000000000000')
, '0')) GROUP BY PPA.CONSOLIDATION_SET_ID
, PCS.CONSOLIDATION_SET_NAME
, PPA.PAYROLL_ID
, PAP.PAYROLL_NAME
, PAP.EFFECTIVE_START_DATE
, PAP.EFFECTIVE_END_DATE
, PPA.PAYROLL_ACTION_ID
, PPA.EFFECTIVE_DATE
, PPA.DATE_EARNED
, PTP.TIME_PERIOD_ID
, PTP.PERIOD_NAME
, PTP.PERIOD_NUM
, PTP.PERIOD_TYPE
, PTP.START_DATE
, PTP.END_DATE
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PAA.TAX_UNIT_ID
, HOU1.NAME
, PAAS.PERSON_ID
, PAPP.EMPLOYEE_NUMBER
, PAPP.FULL_NAME
, PAAS.ORGANIZATION_ID
, HOU.NAME
, PAAS.LOCATION_ID
, HRL.LOCATION_CODE
, HRL.DESCRIPTION
, PAAS.ASSIGNMENT_SEQUENCE
, PAAS.ASSIGNMENT_NUMBER
, PET.ELEMENT_TYPE_ID
, PET.ELEMENT_NAME
, PET.DESCRIPTION
, PET.BUSINESS_GROUP_ID
, PET.ELEMENT_INFORMATION10
, PET.ELEMENT_INFORMATION11
, PEC.CLASSIFICATION_ID
, PEC.CLASSIFICATION_NAME

Columns