VIEW: APPS.PAY_US_DEDUCTIONS_REPORT_V

Source

View Text - Preformatted

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, /* Bug 7586556 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,*/ 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, pec.classification_name, 'ENTRY_ITD', rr.source_id)) 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, /* Bug 7586556 */ rr.source_id

View Text - HTML Formatted

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
, /* BUG 7586556 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
, */ 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
, PEC.CLASSIFICATION_NAME
, 'ENTRY_ITD'
, RR.SOURCE_ID)) 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
, /* BUG 7586556 */ RR.SOURCE_ID