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_PAYE_DETAILS

Source

View Text - Preformatted


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 ,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 USCable Pay')) this_usc_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 USC Balance')) this_usc ,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 USC Balance') ,0) + nvl (pay_ie_p45_archive.get_arc_bal_value (paa.assignment_action_id ,paa.payroll_action_id ,'IE P45 USC Deducted') ,0) ,pay_ie_p45_archive.get_arc_bal_value (paa.assignment_action_id ,paa.payroll_action_id ,'IE USC Balance')) total_usc ,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 USCable Pay') ,0) + nvl (pay_ie_p45_archive.get_arc_bal_value (paa.assignment_action_id ,paa.payroll_action_id ,'IE P45 USC Pay') ,0) ,pay_ie_p45_archive.get_arc_bal_value (paa.assignment_action_id ,paa.payroll_action_id ,'IE USCable Pay')) total_usc_pay 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'

View Text - HTML Formatted

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
, 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 USCABLE PAY')) THIS_USC_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 USC BALANCE')) THIS_USC
, 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 USC BALANCE')
, 0) + NVL (PAY_IE_P45_ARCHIVE.GET_ARC_BAL_VALUE (PAA.ASSIGNMENT_ACTION_ID
, PAA.PAYROLL_ACTION_ID
, 'IE P45 USC DEDUCTED')
, 0)
, PAY_IE_P45_ARCHIVE.GET_ARC_BAL_VALUE (PAA.ASSIGNMENT_ACTION_ID
, PAA.PAYROLL_ACTION_ID
, 'IE USC BALANCE')) TOTAL_USC
, 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 USCABLE PAY')
, 0) + NVL (PAY_IE_P45_ARCHIVE.GET_ARC_BAL_VALUE (PAA.ASSIGNMENT_ACTION_ID
, PAA.PAYROLL_ACTION_ID
, 'IE P45 USC PAY')
, 0)
, PAY_IE_P45_ARCHIVE.GET_ARC_BAL_VALUE (PAA.ASSIGNMENT_ACTION_ID
, PAA.PAYROLL_ACTION_ID
, 'IE USCABLE PAY')) TOTAL_USC_PAY
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'