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_IE_P45_SUPP_DETAILS

Source

View Text - Preformatted


SELECT PAA.ASSIGNMENT_ID ASSIGNMENT_ID ,DECODE(PTP.PERIOD_TYPE, 'Lunar Month','Weekly', DECODE(INSTR(PTP.PERIOD_TYPE,'Week'),0,'Monthly','Weekly')) PERIOD_FREQUENCY ,TO_NUMBER(SUBSTR(PAI_IEP45.ACTION_INFORMATION5, 1,30)) PAY_PERIOD ,TO_NUMBER(SUBSTR(NVL(PAI_IEED.ACTION_INFORMATION26,0), 1,30)) TAX_CREDIT ,TO_NUMBER(SUBSTR(NVL(PAI_IEED.ACTION_INFORMATION27,0), 1,30)) STD_CUT_OFF ,pay_ie_p45_archive.get_arc_bal_value(paa.assignment_action_id, paa.payroll_action_id, 'IE Taxable PayASG_RUN') TOTAL_PAY ,pay_ie_p45_archive.get_arc_bal_value(paa.assignment_action_id, paa.payroll_action_id, 'IE Net TaxASG_RUN') TOTAL_TAX ,pay_ie_p45_archive.get_arc_bal_value(paa.assignment_action_id, paa.payroll_action_id, 'IE Lump SumASG_RUN') + pay_ie_p45_archive.get_arc_bal_value(paa.assignment_action_id, paa.payroll_action_id, 'IE Term Health Levy') LUMP_SUM ,pay_ie_p45_archive.get_arc_bal_value(paa.assignment_action_id, paa.payroll_action_id, 'IE PRSI EmployerASG_RUN') + pay_ie_p45_archive.get_arc_bal_value(paa.assignment_action_id, paa.payroll_action_id, 'IE PRSI K Employer Lump SumASG_RUN')+ pay_ie_p45_archive.get_arc_bal_value(paa.assignment_action_id, paa.payroll_action_id, 'IE PRSI M Employer Lump SumASG_RUN') TOTAL_EMPLOYER_PRSI ,pay_ie_p45_archive.get_arc_bal_value(paa.assignment_action_id, paa.payroll_action_id, 'IE PRSI EmployeeASG_RUN') + pay_ie_p45_archive.get_arc_bal_value(paa.assignment_action_id, paa.payroll_action_id, 'IE PRSI K Employee Lump SumASG_RUN')+ pay_ie_p45_archive.get_arc_bal_value(paa.assignment_action_id, paa.payroll_action_id, 'IE PRSI M Employee Lump SumASG_RUN') TOTAL_EMPLOYEE_PRSI ,pay_ie_p45_archive.get_arc_bal_value(paa.assignment_action_id, paa.payroll_action_id, 'IE PRSI Insurable WeeksASG_PTD') INSURABLE_WEEKS ,pay_ie_p45_archive.get_arc_bal_value(paa.assignment_action_id, paa.payroll_action_id, 'IE PRSI_ClassA Insurable WeeksASG_PTD' ) supp_insurable_classA_weeks ,SUBSTR(PAI_IEED.ACTION_INFORMATION22, 1,30) PRSI_CLASS ,TO_DATE(SUBSTR(PAI_IEP45.ACTION_INFORMATION7, 1,30),'DD-MON-RRRR') DATE_PAID ,paaf.person_id person_id ,paa.tax_unit_id tax_unit_id ,TO_DATE (pai_iep45.action_information3, 'DD-MON-RRRR') date_of_leaving ,pay_ie_p45_archive.get_arc_bal_value (paa.assignment_action_id ,paa.payroll_action_id ,'IE USCable PayASG_RUN') total_usc_pay ,pay_ie_p45_archive.get_arc_bal_value (paa.assignment_action_id ,paa.payroll_action_id ,'IE USC BalanceASG_RUN') total_usc FROM PER_ALL_ASSIGNMENTS_F PAAF, PAY_ACTION_INFORMATION PAI_ED , PAY_ACTION_INFORMATION PAI_IEP45 , PAY_ACTION_INFORMATION PAI_IEED , PAY_ASSIGNMENT_ACTIONS PAA , PAY_PAYROLL_ACTIONS PPA , PER_TIME_PERIODS PTP WHERE PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID AND PAA.ASSIGNMENT_ACTION_ID = PAI_IEP45.ACTION_CONTEXT_ID AND PAI_IEP45.ACTION_CONTEXT_TYPE = 'AAP' AND PAI_IEP45.ACTION_INFORMATION2 = 'Y' AND PPA.REPORT_TYPE = 'P45' AND PPA.REPORT_QUALIFIER = 'IE' AND PPA.REPORT_CATEGORY = 'ARCHIVE' AND PPA.ACTION_STATUS = 'C' AND PAI_IEP45.ACTION_INFORMATION_CATEGORY = 'IE P45 INFORMATION' AND PAA.ASSIGNMENT_ACTION_ID = PAI_ED.ACTION_CONTEXT_ID AND PAI_ED.ACTION_CONTEXT_TYPE = 'AAP' AND PAI_ED.ACTION_INFORMATION_CATEGORY = 'EMPLOYEE DETAILS' AND PTP.TIME_PERIOD_ID = PAI_ED.ACTION_INFORMATION16 AND PAA.ASSIGNMENT_ACTION_ID = PAI_IEED.ACTION_CONTEXT_ID AND PAI_IEED.ACTION_CONTEXT_TYPE = 'AAP' AND PAI_IEED.ACTION_INFORMATION_CATEGORY = 'IE EMPLOYEE DETAILS' AND PAA.ASSIGNMENT_ID = PAAF.ASSIGNMENT_ID AND PAAF.EFFECTIVE_END_DATE = (SELECT MAX(PAAF1.EFFECTIVE_END_DATE) FROM PER_ASSIGNMENTS_F PAAF1 WHERE PAAF1.ASSIGNMENT_ID = PAAF.ASSIGNMENT_ID )

View Text - HTML Formatted

SELECT PAA.ASSIGNMENT_ID ASSIGNMENT_ID
, DECODE(PTP.PERIOD_TYPE
, 'LUNAR MONTH'
, 'WEEKLY'
, DECODE(INSTR(PTP.PERIOD_TYPE
, 'WEEK')
, 0
, 'MONTHLY'
, 'WEEKLY')) PERIOD_FREQUENCY
, TO_NUMBER(SUBSTR(PAI_IEP45.ACTION_INFORMATION5
, 1
, 30)) PAY_PERIOD
, TO_NUMBER(SUBSTR(NVL(PAI_IEED.ACTION_INFORMATION26
, 0)
, 1
, 30)) TAX_CREDIT
, TO_NUMBER(SUBSTR(NVL(PAI_IEED.ACTION_INFORMATION27
, 0)
, 1
, 30)) STD_CUT_OFF
, PAY_IE_P45_ARCHIVE.GET_ARC_BAL_VALUE(PAA.ASSIGNMENT_ACTION_ID
, PAA.PAYROLL_ACTION_ID
, 'IE TAXABLE PAYASG_RUN') TOTAL_PAY
, PAY_IE_P45_ARCHIVE.GET_ARC_BAL_VALUE(PAA.ASSIGNMENT_ACTION_ID
, PAA.PAYROLL_ACTION_ID
, 'IE NET TAXASG_RUN') TOTAL_TAX
, PAY_IE_P45_ARCHIVE.GET_ARC_BAL_VALUE(PAA.ASSIGNMENT_ACTION_ID
, PAA.PAYROLL_ACTION_ID
, 'IE LUMP SUMASG_RUN') + PAY_IE_P45_ARCHIVE.GET_ARC_BAL_VALUE(PAA.ASSIGNMENT_ACTION_ID
, PAA.PAYROLL_ACTION_ID
, 'IE TERM HEALTH LEVY') LUMP_SUM
, PAY_IE_P45_ARCHIVE.GET_ARC_BAL_VALUE(PAA.ASSIGNMENT_ACTION_ID
, PAA.PAYROLL_ACTION_ID
, 'IE PRSI EMPLOYERASG_RUN') + PAY_IE_P45_ARCHIVE.GET_ARC_BAL_VALUE(PAA.ASSIGNMENT_ACTION_ID
, PAA.PAYROLL_ACTION_ID
, 'IE PRSI K EMPLOYER LUMP SUMASG_RUN')+ PAY_IE_P45_ARCHIVE.GET_ARC_BAL_VALUE(PAA.ASSIGNMENT_ACTION_ID
, PAA.PAYROLL_ACTION_ID
, 'IE PRSI M EMPLOYER LUMP SUMASG_RUN') TOTAL_EMPLOYER_PRSI
, PAY_IE_P45_ARCHIVE.GET_ARC_BAL_VALUE(PAA.ASSIGNMENT_ACTION_ID
, PAA.PAYROLL_ACTION_ID
, 'IE PRSI EMPLOYEEASG_RUN') + PAY_IE_P45_ARCHIVE.GET_ARC_BAL_VALUE(PAA.ASSIGNMENT_ACTION_ID
, PAA.PAYROLL_ACTION_ID
, 'IE PRSI K EMPLOYEE LUMP SUMASG_RUN')+ PAY_IE_P45_ARCHIVE.GET_ARC_BAL_VALUE(PAA.ASSIGNMENT_ACTION_ID
, PAA.PAYROLL_ACTION_ID
, 'IE PRSI M EMPLOYEE LUMP SUMASG_RUN') TOTAL_EMPLOYEE_PRSI
, PAY_IE_P45_ARCHIVE.GET_ARC_BAL_VALUE(PAA.ASSIGNMENT_ACTION_ID
, PAA.PAYROLL_ACTION_ID
, 'IE PRSI INSURABLE WEEKSASG_PTD') INSURABLE_WEEKS
, PAY_IE_P45_ARCHIVE.GET_ARC_BAL_VALUE(PAA.ASSIGNMENT_ACTION_ID
, PAA.PAYROLL_ACTION_ID
, 'IE PRSI_CLASSA INSURABLE WEEKSASG_PTD' ) SUPP_INSURABLE_CLASSA_WEEKS
, SUBSTR(PAI_IEED.ACTION_INFORMATION22
, 1
, 30) PRSI_CLASS
, TO_DATE(SUBSTR(PAI_IEP45.ACTION_INFORMATION7
, 1
, 30)
, 'DD-MON-RRRR') DATE_PAID
, PAAF.PERSON_ID PERSON_ID
, PAA.TAX_UNIT_ID TAX_UNIT_ID
, TO_DATE (PAI_IEP45.ACTION_INFORMATION3
, 'DD-MON-RRRR') DATE_OF_LEAVING
, PAY_IE_P45_ARCHIVE.GET_ARC_BAL_VALUE (PAA.ASSIGNMENT_ACTION_ID
, PAA.PAYROLL_ACTION_ID
, 'IE USCABLE PAYASG_RUN') TOTAL_USC_PAY
, PAY_IE_P45_ARCHIVE.GET_ARC_BAL_VALUE (PAA.ASSIGNMENT_ACTION_ID
, PAA.PAYROLL_ACTION_ID
, 'IE USC BALANCEASG_RUN') TOTAL_USC
FROM PER_ALL_ASSIGNMENTS_F PAAF
, PAY_ACTION_INFORMATION PAI_ED
, PAY_ACTION_INFORMATION PAI_IEP45
, PAY_ACTION_INFORMATION PAI_IEED
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, PER_TIME_PERIODS PTP
WHERE PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PAA.ASSIGNMENT_ACTION_ID = PAI_IEP45.ACTION_CONTEXT_ID
AND PAI_IEP45.ACTION_CONTEXT_TYPE = 'AAP'
AND PAI_IEP45.ACTION_INFORMATION2 = 'Y'
AND PPA.REPORT_TYPE = 'P45'
AND PPA.REPORT_QUALIFIER = 'IE'
AND PPA.REPORT_CATEGORY = 'ARCHIVE'
AND PPA.ACTION_STATUS = 'C'
AND PAI_IEP45.ACTION_INFORMATION_CATEGORY = 'IE P45 INFORMATION'
AND PAA.ASSIGNMENT_ACTION_ID = PAI_ED.ACTION_CONTEXT_ID
AND PAI_ED.ACTION_CONTEXT_TYPE = 'AAP'
AND PAI_ED.ACTION_INFORMATION_CATEGORY = 'EMPLOYEE DETAILS'
AND PTP.TIME_PERIOD_ID = PAI_ED.ACTION_INFORMATION16
AND PAA.ASSIGNMENT_ACTION_ID = PAI_IEED.ACTION_CONTEXT_ID
AND PAI_IEED.ACTION_CONTEXT_TYPE = 'AAP'
AND PAI_IEED.ACTION_INFORMATION_CATEGORY = 'IE EMPLOYEE DETAILS'
AND PAA.ASSIGNMENT_ID = PAAF.ASSIGNMENT_ID
AND PAAF.EFFECTIVE_END_DATE = (SELECT MAX(PAAF1.EFFECTIVE_END_DATE)
FROM PER_ASSIGNMENTS_F PAAF1
WHERE PAAF1.ASSIGNMENT_ID = PAAF.ASSIGNMENT_ID )