eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.PAY_CA_DEDUCTIONS_REPORT_V

Source

View Text - Preformatted


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

View Text - HTML Formatted

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