View: PAY_IE_P45_SUPP_DETAILS

Product:	PAY - Payroll
Description:	Used by P45 form to populate Supplementary Details tab.
Implementation/DBA Data:	APPS.PAY_IE_P45_SUPP_DETAILS

View Text

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
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 )

Columns