View: PAY_CA_DEDUCTIONS_REPORT_V

Product:	PAY - Payroll
Description:
Implementation/DBA Data:	APPS.PAY_CA_DEDUCTIONS_REPORT_V

View Text

SELECT DISTINCT PCS.CONSOLIDATION_SET_ID
, PCS.CONSOLIDATION_SET_NAME
, PAP.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
, PRR.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_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE( PET.ELEMENT_NAME
, 'RUN'
, PAA.ASSIGNMENT_ACTION_ID
, PAAS.ASSIGNMENT_ID
, NULL
, 'ASG'
, PAA.TAX_UNIT_ID
, PPA.BUSINESS_GROUP_ID
, NULL)
, PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE( PET.ELEMENT_NAME||' NOT TAKEN'
, 'RUN'
, PAA.ASSIGNMENT_ACTION_ID
, PAAS.ASSIGNMENT_ID
, NULL
, 'ASG'
, PAA.TAX_UNIT_ID
, PPA.BUSINESS_GROUP_ID
, NULL)
, PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE( PET.ELEMENT_NAME||' ARREARS'
, 'RUN'
, PAA.ASSIGNMENT_ACTION_ID
, PAAS.ASSIGNMENT_ID
, NULL
, 'ASG'
, PAA.TAX_UNIT_ID
, PPA.BUSINESS_GROUP_ID
, NULL)
, TO_NUMBER(DECODE(PET.ELEMENT_INFORMATION11
, NULL
, NULL
, NVL(PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE( PET.ELEMENT_NAME||' ACCRUED'
, 'ITD'
, PAA.ASSIGNMENT_ACTION_ID
, PAAS.ASSIGNMENT_ID
, NULL
, 'ASG'
, PAA.TAX_UNIT_ID
, PPA.BUSINESS_GROUP_ID
, NULL)
, 0) ) )
, TO_NUMBER(DECODE(PET.ELEMENT_INFORMATION11
, NULL
, NULL
, PAY_CA_DEDN_PKG.PAY_CA_TOT_OWED( PAA.ASSIGNMENT_ID
, PRR.ELEMENT_TYPE_ID
, PPA.EFFECTIVE_DATE
, PPA.DATE_EARNED) ) )
FROM PAY_ALL_PAYROLLS_F PAP
, PAY_CONSOLIDATION_SETS PCS
, PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAA
, PER_TIME_PERIODS PTP
, PAY_RUN_RESULTS PRR
, PAY_ELEMENT_TYPES_F PET
, PAY_ELEMENT_CLASSIFICATIONS PEC
, PER_ALL_ASSIGNMENTS_F PAAS
, PER_ALL_PEOPLE_F PAPP
, HR_LOCATIONS HRL
, HR_ORGANIZATION_UNITS HOU
, HR_ORGANIZATION_UNITS HOU1
WHERE PCS.CONSOLIDATION_SET_ID = PPA.CONSOLIDATION_SET_ID
AND PAP.PAYROLL_ID = PPA.PAYROLL_ID
AND NVL(PPA.DATE_EARNED
, PPA.EFFECTIVE_DATE) BETWEEN PAP.EFFECTIVE_START_DATE
AND PAP.EFFECTIVE_END_DATE
AND PPA.ACTION_TYPE IN ('Q'
, 'R'
, 'V'
, 'B'
, 'I')
AND PPA.ACTION_STATUS = 'C'
AND PPA.TIME_PERIOD_ID = PTP.TIME_PERIOD_ID
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PAA.ACTION_STATUS = 'C'
AND PAA.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
AND PRR.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND NVL(PPA.DATE_EARNED
, PPA.EFFECTIVE_DATE) BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE
AND PET.CLASSIFICATION_ID = PEC.CLASSIFICATION_ID
AND PEC.LEGISLATION_CODE = 'CA'
AND PEC.CLASSIFICATION_NAME IN ('PRE-TAX DEDUCTIONS'
, 'INVOLUNTARY DEDUCTIONS'
, 'VOLUNTARY DEDUCTIONS')
AND PET.ELEMENT_NAME NOT LIKE '%SPECIAL FEATURES'
AND PET.ELEMENT_NAME NOT LIKE '%SPECIAL FEATURES 2'
AND PET.ELEMENT_NAME NOT LIKE '%SPECIAL INPUTS'
AND PET.ELEMENT_NAME NOT LIKE '%VERIFIER'
AND PAA.ASSIGNMENT_ID = PAAS.ASSIGNMENT_ID
AND NVL(PPA.DATE_EARNED
, PPA.EFFECTIVE_DATE) BETWEEN PAAS.EFFECTIVE_START_DATE
AND PAAS.EFFECTIVE_END_DATE
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 PAAS.LOCATION_ID = HRL.LOCATION_ID
AND PAAS.ORGANIZATION_ID = HOU.ORGANIZATION_ID
AND PAA.TAX_UNIT_ID = HOU1.ORGANIZATION_ID

Columns