View: PAY_US_DEDUCTIONS_REPORT_RBR_V

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

View Text

SELECT PPA.CONSOLIDATION_SET_ID CONSOLIDATION_SET_ID
, PCS.CONSOLIDATION_SET_NAME CONSOLIDATION_SET_NAME
, PPA.PAYROLL_ID PAYROLL_ID
, PAP.PAYROLL_NAME PAYROLL_NAME
, PAP.EFFECTIVE_START_DATE PAYROLL_EFFECTIVE_START_DATE
, PAP.EFFECTIVE_END_DATE PAYROLL_EFFECTIVE_END_DATE
, PPA.PAYROLL_ACTION_ID PAYROLL_ACTION_ID
, PPA.EFFECTIVE_DATE PAYROLL_ACTION_EFFECTIVE_DATE
, PPA.DATE_EARNED PAYROLL_ACTION_DATE_EARNED
, PTP.TIME_PERIOD_ID TIME_PERIOD_ID
, PTP.PERIOD_NAME PERIOD_NAME
, PTP.PERIOD_NUM PERIOD_NUM
, PTP.PERIOD_TYPE PERIOD_TYPE
, PTP.START_DATE PERIOD_START_DATE
, PTP.END_DATE PERIOD_END_DATE
, PAA.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID ASSIGNMENT_ID
, PAA.TAX_UNIT_ID TAX_UNIT_ID
, HOU1.NAME GRE
, PAAS.PERSON_ID PERSON_ID
, PAPP.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, PAPP.FULL_NAME FULL_NAME
, PAAS.ORGANIZATION_ID ORGANIZATION_ID
, HOU.NAME ORGANIZATION_NAME
, PAAS.LOCATION_ID LOCATION_ID
, HRL.LOCATION_CODE LOCATION_CODE
, HRL.DESCRIPTION LOCATION_DESCRIPTION
, PAAS.ASSIGNMENT_SEQUENCE ASSIGNMENT_SEQUENCE
, PAAS.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, PET.ELEMENT_TYPE_ID ELEMENT_TYPE_ID
, PET.ELEMENT_NAME ELEMENT_NAME
, PET.DESCRIPTION ELEMENT_DESCRIPTION
, PBA.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PET.ELEMENT_INFORMATION10 PRIMARY_BALANCE_ID
, PET.ELEMENT_INFORMATION11 HOURS_BALANCE_ID
, PEC.CLASSIFICATION_ID CLASSIFICATION_ID
, PEC.CLASSIFICATION_NAME 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
, PBA.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
, PBA.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
, PBA.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
, PBA.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_ELEMENT_TYPES_F PET
, PAY_ELEMENT_CLASSIFICATIONS PEC
, PER_ALL_PEOPLE_F PAPP
, PAY_BALANCE_ATTRIBUTES PBA
, PAY_RUN_BALANCES PRB
, PAY_DEFINED_BALANCES PDB
, PAY_BAL_ATTRIBUTE_DEFINITIONS PBAD
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 IS NOT NULL
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 PBAD.ATTRIBUTE_NAME IN ('PAY_US_PRE_TAX_DEDUCTIONS'
, 'PAY_US_AFTER_TAX_DEDUCTIONS'
, 'PAY_US_EMPLOYER_LIABILITY')
AND PBAD.LEGISLATION_CODE = 'US'
AND PBA.ATTRIBUTE_ID = PBAD.ATTRIBUTE_ID
AND PRB.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PRB.DEFINED_BALANCE_ID = PBA.DEFINED_BALANCE_ID
AND PBA.DEFINED_BALANCE_ID = PDB.DEFINED_BALANCE_ID
AND PBA.BUSINESS_GROUP_ID = PET.BUSINESS_GROUP_ID
AND (NVL(TRANSLATE(PET.ELEMENT_INFORMATION10
, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ'
, '01234567890000000000000000000000000000000000000000000000000000')
, '0') = PDB.BALANCE_TYPE_ID OR NVL(TRANSLATE(PET.ELEMENT_INFORMATION12
, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ'
, '01234567890000000000000000000000000000000000000000000000000000')
, '0') = PDB.BALANCE_TYPE_ID OR NVL(TRANSLATE(PET.ELEMENT_INFORMATION13
, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ'
, '01234567890000000000000000000000000000000000000000000000000000')
, '0') = PDB.BALANCE_TYPE_ID)
AND NVL(PPA.DATE_EARNED
, PPA.EFFECTIVE_DATE) BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE
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 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
, PBA.BUSINESS_GROUP_ID
, PET.ELEMENT_INFORMATION10
, PET.ELEMENT_INFORMATION11
, PEC.CLASSIFICATION_ID
, PEC.CLASSIFICATION_NAME

Columns