View: PAY_IE_P45_PAYE_DETAILS

Product:	PAY - Payroll
Description:	Used by P45 form to populate PAYE details
Implementation/DBA Data:	APPS.PAY_IE_P45_PAYE_DETAILS

View Text

SELECT PAA.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID
, TO_NUMBER (DECODE (INSTR (PTP.PERIOD_TYPE
, 'WEEK')
, 0
, SUBSTR (PAI_IEED.ACTION_INFORMATION26
, 1
, 30)
, NULL ) ) MONTHLY_TAX_CREDIT
, TO_NUMBER (DECODE (INSTR (PTP.PERIOD_TYPE
, 'WEEK')
, 0
, SUBSTR (PAI_IEED.ACTION_INFORMATION27
, 1
, 30)
, NULL ) ) MONTHLY_STD_CUT_OFF
, TO_NUMBER (DECODE (INSTR (PTP.PERIOD_TYPE
, 'WEEK')
, 0
, NULL
, SUBSTR (PAI_IEED.ACTION_INFORMATION26
, 1
, 30) ) ) WEEKLY_TAX_CREDIT
, TO_NUMBER (DECODE (INSTR (PTP.PERIOD_TYPE
, 'WEEK')
, 0
, NULL
, SUBSTR (PAI_IEED.ACTION_INFORMATION27
, 1
, 30) ) ) WEEKLY_STD_CUT_OFF
, TO_NUMBER (DECODE (INSTR (PTP.PERIOD_TYPE
, 'WEEK')
, 0
, SUBSTR (PAI_IEP45.ACTION_INFORMATION5
, 1
, 30)
, NULL ) ) MONTH_NO
, TO_NUMBER (DECODE (INSTR (PTP.PERIOD_TYPE
, 'WEEK')
, 0
, NULL
, SUBSTR (PAI_IEP45.ACTION_INFORMATION5
, 1
, 30) ) ) WEEK_NO
, DECODE (SIGN ( TO_DATE (LEG.RULE_MODE || TO_CHAR (PPA.EFFECTIVE_DATE
, 'YYYY')
, 'DD/MM/YYYY' ) - DECODE (LENGTH (PAI_IEED.ACTION_INFORMATION30)
, 19
, FND_DATE.CANONICAL_TO_DATE (PAI_IEED.ACTION_INFORMATION30)
, FND_DATE.DISPLAYDATE_TO_DATE (PAI_IEED.ACTION_INFORMATION30) ) )
, -1
, NVL (PAY_IE_P45_ARCHIVE.GET_ARC_BAL_VALUE (PAA.ASSIGNMENT_ACTION_ID
, PAA.PAYROLL_ACTION_ID
, 'IE NET TAX' )
, 0 ) + NVL (PAY_IE_P45_ARCHIVE.GET_ARC_BAL_VALUE (PAA.ASSIGNMENT_ACTION_ID
, PAA.PAYROLL_ACTION_ID
, 'IE P45 TAX DEDUCTED' )
, 0 )
, PAY_IE_P45_ARCHIVE.GET_ARC_BAL_VALUE (PAA.ASSIGNMENT_ACTION_ID
, PAA.PAYROLL_ACTION_ID
, 'IE NET TAX' ) ) TOTAL_TAX
, DECODE (SIGN ( TO_DATE (LEG.RULE_MODE || TO_CHAR (PPA.EFFECTIVE_DATE
, 'YYYY')
, 'DD/MM/YYYY' ) - DECODE (LENGTH (PAI_IEED.ACTION_INFORMATION30)
, 19
, FND_DATE.CANONICAL_TO_DATE (PAI_IEED.ACTION_INFORMATION30)
, FND_DATE.DISPLAYDATE_TO_DATE (PAI_IEED.ACTION_INFORMATION30) ) )
, -1
, NVL (PAY_IE_P45_ARCHIVE.GET_ARC_BAL_VALUE (PAA.ASSIGNMENT_ACTION_ID
, PAA.PAYROLL_ACTION_ID
, 'IE TAXABLE PAY' )
, 0 ) + NVL (PAY_IE_P45_ARCHIVE.GET_ARC_BAL_VALUE (PAA.ASSIGNMENT_ACTION_ID
, PAA.PAYROLL_ACTION_ID
, 'IE P45 PAY' )
, 0 )
, PAY_IE_P45_ARCHIVE.GET_ARC_BAL_VALUE (PAA.ASSIGNMENT_ACTION_ID
, PAA.PAYROLL_ACTION_ID
, 'IE TAXABLE PAY' ) ) TOTAL_PAY
, PAY_IE_P45_ARCHIVE.GET_ARC_BAL_VALUE (PAA.ASSIGNMENT_ACTION_ID
, PAA.PAYROLL_ACTION_ID
, 'IE TERM HEALTH LEVY' ) LUMP_SUM
, PAI_IEP45.ACTION_INFORMATION4 EMERGENCY_TAX
, DECODE (SIGN ( TO_DATE (LEG.RULE_MODE || TO_CHAR (PPA.EFFECTIVE_DATE
, 'YYYY')
, 'DD/MM/YYYY' ) - DECODE (LENGTH (PAI_IEED.ACTION_INFORMATION30)
, 19
, FND_DATE.CANONICAL_TO_DATE (PAI_IEED.ACTION_INFORMATION30)
, FND_DATE.DISPLAYDATE_TO_DATE (PAI_IEED.ACTION_INFORMATION30) ) )
, 1
, NULL
, 0
, NULL
, PAY_IE_P45_ARCHIVE.GET_ARC_BAL_VALUE (PAA.ASSIGNMENT_ACTION_ID
, PAA.PAYROLL_ACTION_ID
, 'IE TAXABLE PAY' ) ) THIS_PAY
, DECODE (SIGN ( TO_DATE (LEG.RULE_MODE || TO_CHAR (PPA.EFFECTIVE_DATE
, 'YYYY')
, 'DD/MM/YYYY' ) - DECODE (LENGTH (PAI_IEED.ACTION_INFORMATION30)
, 19
, FND_DATE.CANONICAL_TO_DATE (PAI_IEED.ACTION_INFORMATION30)
, FND_DATE.DISPLAYDATE_TO_DATE (PAI_IEED.ACTION_INFORMATION30) ) )
, 1
, NULL
, 0
, NULL
, PAY_IE_P45_ARCHIVE.GET_ARC_BAL_VALUE (PAA.ASSIGNMENT_ACTION_ID
, PAA.PAYROLL_ACTION_ID
, 'IE NET TAX' ) ) THIS_TAX
FROM PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, PAY_ACTION_INFORMATION PAI_IEED
, PAY_ACTION_INFORMATION PAI_ED
, PAY_ACTION_INFORMATION PAI_IEP45
, PER_TIME_PERIODS PTP
, PAY_LEGISLATION_RULES LEG
WHERE PAA.ASSIGNMENT_ACTION_ID = PAI_IEED.ACTION_CONTEXT_ID
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PPA.REPORT_TYPE ='P45'
AND PPA.REPORT_QUALIFIER ='IE'
AND PPA.REPORT_CATEGORY ='ARCHIVE'
AND PPA.ACTION_STATUS = 'C'
AND PAI_IEED.ACTION_CONTEXT_TYPE = 'AAP'
AND PAI_IEED.ACTION_INFORMATION_CATEGORY = 'IE EMPLOYEE DETAILS'
AND PAA.ASSIGNMENT_ACTION_ID = PAI_IEP45.ACTION_CONTEXT_ID
AND PAI_IEP45.ACTION_CONTEXT_TYPE = 'AAP'
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 LEG.LEGISLATION_CODE = 'IE'
AND LEG.RULE_TYPE = 'L'

Columns