VIEW: APPS.PAY_IE_P45_PRSI_DETAILS

Source

View Text - Preformatted

SELECT PAA.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID ,(NVL(pay_ie_p45_archive.get_arc_bal_value(paa.assignment_action_id,paa.payroll_action_id,'IE PRSI Employer'),0) + NVL(pay_ie_p45_archive.get_arc_bal_value(paa.assignment_action_id, paa.payroll_action_id,'IE PRSI K Employer Lump Sum'),0)+ NVL(pay_ie_p45_archive.get_arc_bal_value(paa.assignment_action_id, paa.payroll_action_id,'IE PRSI M Employer Lump Sum'),0)) TOTAL_EMPLOYER_PRSI ,(NVL(pay_ie_p45_archive.get_arc_bal_value(paa.assignment_action_id,paa.payroll_action_id,'IE PRSI Employee'),0)+ NVL(pay_ie_p45_archive.get_arc_bal_value(paa.assignment_action_id, paa.payroll_action_id,'IE PRSI K Employee Lump Sum'),0)+ NVL(pay_ie_p45_archive.get_arc_bal_value(paa.assignment_action_id, paa.payroll_action_id,'IE PRSI M Employee Lump Sum'),0)) TOTAL_EMPLOYEE_PRSI ,(NVL(pay_ie_p45_archive.get_arc_bal_value(paa.assignment_action_id,paa.payroll_action_id,'IE PRSI Insurable Weeks'),0)+ NVL(pay_ie_p45_archive.get_arc_bal_value(paa.assignment_action_id, paa.payroll_action_id,'IE PRSI K Term Insurable Weeks'),0)+ NVL(pay_ie_p45_archive.get_arc_bal_value(paa.assignment_action_id, paa.payroll_action_id,'IE PRSI M Term Insurable Weeks'),0)) INSURABLE_WEEKS ,pay_ie_p45_archive.get_arc_bal_value(paa.assignment_action_id, paa.payroll_action_id,'IE PRSI_ClassA Insurable Weeks') CLASS_A_INSURABLE_WEEKS ,SUBSTR(PAI_IEED.ACTION_INFORMATION22, 1,40) OTHER_INSURABLE_WEEKS FROM PAY_ASSIGNMENT_ACTIONS PAA ,PAY_PAYROLL_ACTIONS PPA ,PAY_ACTION_INFORMATION PAI_IEED WHERE PAA.ASSIGNMENT_ACTION_ID = PAI_IEED.ACTION_CONTEXT_ID AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID AND PPA.EFFECTIVE_DATE = PAI_IEED.EFFECTIVE_DATE 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'

View Text - HTML Formatted

SELECT PAA.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID
, (NVL(PAY_IE_P45_ARCHIVE.GET_ARC_BAL_VALUE(PAA.ASSIGNMENT_ACTION_ID
, PAA.PAYROLL_ACTION_ID
, 'IE PRSI EMPLOYER')
, 0) + NVL(PAY_IE_P45_ARCHIVE.GET_ARC_BAL_VALUE(PAA.ASSIGNMENT_ACTION_ID
, PAA.PAYROLL_ACTION_ID
, 'IE PRSI K EMPLOYER LUMP SUM')
, 0)+ NVL(PAY_IE_P45_ARCHIVE.GET_ARC_BAL_VALUE(PAA.ASSIGNMENT_ACTION_ID
, PAA.PAYROLL_ACTION_ID
, 'IE PRSI M EMPLOYER LUMP SUM')
, 0)) TOTAL_EMPLOYER_PRSI
, (NVL(PAY_IE_P45_ARCHIVE.GET_ARC_BAL_VALUE(PAA.ASSIGNMENT_ACTION_ID
, PAA.PAYROLL_ACTION_ID
, 'IE PRSI EMPLOYEE')
, 0)+ NVL(PAY_IE_P45_ARCHIVE.GET_ARC_BAL_VALUE(PAA.ASSIGNMENT_ACTION_ID
, PAA.PAYROLL_ACTION_ID
, 'IE PRSI K EMPLOYEE LUMP SUM')
, 0)+ NVL(PAY_IE_P45_ARCHIVE.GET_ARC_BAL_VALUE(PAA.ASSIGNMENT_ACTION_ID
, PAA.PAYROLL_ACTION_ID
, 'IE PRSI M EMPLOYEE LUMP SUM')
, 0)) TOTAL_EMPLOYEE_PRSI
, (NVL(PAY_IE_P45_ARCHIVE.GET_ARC_BAL_VALUE(PAA.ASSIGNMENT_ACTION_ID
, PAA.PAYROLL_ACTION_ID
, 'IE PRSI INSURABLE WEEKS')
, 0)+ NVL(PAY_IE_P45_ARCHIVE.GET_ARC_BAL_VALUE(PAA.ASSIGNMENT_ACTION_ID
, PAA.PAYROLL_ACTION_ID
, 'IE PRSI K TERM INSURABLE WEEKS')
, 0)+ NVL(PAY_IE_P45_ARCHIVE.GET_ARC_BAL_VALUE(PAA.ASSIGNMENT_ACTION_ID
, PAA.PAYROLL_ACTION_ID
, 'IE PRSI M TERM INSURABLE WEEKS')
, 0)) INSURABLE_WEEKS
, PAY_IE_P45_ARCHIVE.GET_ARC_BAL_VALUE(PAA.ASSIGNMENT_ACTION_ID
, PAA.PAYROLL_ACTION_ID
, 'IE PRSI_CLASSA INSURABLE WEEKS') CLASS_A_INSURABLE_WEEKS
, SUBSTR(PAI_IEED.ACTION_INFORMATION22
, 1
, 40) OTHER_INSURABLE_WEEKS
FROM PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, PAY_ACTION_INFORMATION PAI_IEED
WHERE PAA.ASSIGNMENT_ACTION_ID = PAI_IEED.ACTION_CONTEXT_ID
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PPA.EFFECTIVE_DATE = PAI_IEED.EFFECTIVE_DATE
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'