VIEW: APPS.PAY_IE_ELEMENT_TYPES_V

Source

View Text - Preformatted

SELECT PET.ROWID ROW_ID , NVL(PET.REPORTING_NAME, PET.ELEMENT_NAME) REPORT_NAME , PEC.CLASSIFICATION_NAME , decode(NVL(PET.REPORTING_NAME,PET.ELEMENT_NAME),'BIK Arrearage', SUM(FND_NUMBER.CANONICAL_TO_NUMBER(PRV.RESULT_VALUE*(-1))) ,SUM(FND_NUMBER.CANONICAL_TO_NUMBER(PRV.RESULT_VALUE))) RESULT_VALUE , PET.PROCESSING_PRIORITY , PRR.ASSIGNMENT_ACTION_ID, PEC.CLASSIFICATION_NAME BASE_CLASSIFICATION_NAME, decode (pec.classification_name,'PAYE',1 ,'PRSI',2 ,'Income Tax Levy',3 ,'IE Benefit In Kind Arrearage',4 ,'IE Benefit In Kind Arrearage Recovery',5 ,'Court Orders',6 ,'Parking Levy',7 ,'IE Social Benefits Clearup',8 ,'Pre-Tax Deductions',9 ,'Pre Tax and Pre PRSI Deduction',10 ,'Pre PRSI Deduction',11 ,'Voluntary Deductions',12 ,13) sort_order FROM PAY_ELEMENT_TYPES_F PET, PAY_ELEMENT_TYPES_F_TL PETTL, PAY_ELEMENT_CLASSIFICATIONS PEC, PAY_ELEMENT_CLASSIFICATIONS_TL PECTL, PAY_INPUT_VALUES_F PIV, PAY_RUN_RESULT_VALUES PRV, PAY_RUN_RESULTS PRR , PAY_ASSIGNMENT_ACTIONS PAA, PAY_PAYROLL_ACTIONS PPA WHERE PEC.LEGISLATION_CODE = 'IE' AND PEC.BUSINESS_GROUP_ID IS NULL AND PEC.Classification_id = PECTL.classification_id AND PECTL.LANGUAGE = userenv('LANG') AND PET.element_type_id = PETTL.element_type_id AND PETTL.LANGUAGE = userenv('LANG') AND PRR.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID AND PAA.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID AND PRR.STATUS IN ('P','PA') AND PET.CLASSIFICATION_ID = PEC.CLASSIFICATION_ID AND PIV.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID AND PRV.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID AND PRV.RUN_RESULT_ID = PRR.RUN_RESULT_ID AND ((PIV.NAME ='Pay Value' AND PIV.UOM = 'M') OR (PET.ELEMENT_NAME IN ('IE BIK Arrearage Details','IE BIK Arrearage Recovery Details') AND PET.LEGISLATION_CODE= 'IE' AND PET.BUSINESS_GROUP_ID is NULL AND PIV.NAME in ('BIK Arrearage','BIK Arrearage Recovered')) OR (PET.ELEMENT_NAME IN ('IE PAYE at higher rate','IE PAYE at standard rate') AND PET.LEGISLATION_CODE= 'IE' AND PET.BUSINESS_GROUP_ID is NULL AND PIV.NAME ='Value' )) AND PET.ELEMENT_NAME NOT IN ('IE PRSI','IE Net tax') AND PPA.EFFECTIVE_DATE BETWEEN PIV.EFFECTIVE_START_DATE AND PIV.EFFECTIVE_END_DATE AND PPA.EFFECTIVE_DATE BETWEEN PET.EFFECTIVE_START_DATE AND PET.EFFECTIVE_END_DATE GROUP BY PEC.CLASSIFICATION_NAME, PRR.ASSIGNMENT_ACTION_ID, PET.PROCESSING_PRIORITY, PEC.CLASSIFICATION_NAME, NVL(PET.REPORTING_NAME, PET.ELEMENT_NAME), PET.ROWID UNION ALL SELECT PET.ROWID ROW_ID, NVL(PET.REPORTING_NAME, PET.ELEMENT_NAME) REPORT_NAME, PEC.CLASSIFICATION_NAME, decode(NVL(PET.REPORTING_NAME,PET.ELEMENT_NAME),'BIK Arrearage', SUM(FND_NUMBER.CANONICAL_TO_NUMBER(PRV.RESULT_VALUE*(-1))) ,SUM(FND_NUMBER.CANONICAL_TO_NUMBER(PRV.RESULT_VALUE))) RESULT_VALUE , PET.PROCESSING_PRIORITY, PAC.LOCKING_ACTION_ID, PEC.CLASSIFICATION_NAME BASE_CLASSIFICATION_NAME , decode (pec.classification_name,'PAYE',1 ,'PRSI',2 ,'Income Tax Levy',3 ,'IE Benefit In Kind Arrearage',4 ,'IE Benefit In Kind Arrearage Recovery',5 ,'Court Orders',6 ,'Parking Levy',7 ,'IE Social Benefits Clearup',8 ,'Pre-Tax Deductions',9 ,'Pre Tax and Pre PRSI Deduction',10 ,'Pre PRSI Deduction',11 ,'Voluntary Deductions',12 ,13) sort_order FROM PAY_ELEMENT_TYPES_F PET, PAY_ELEMENT_TYPES_F_TL PETTL, PAY_ELEMENT_CLASSIFICATIONS PEC, PAY_ELEMENT_CLASSIFICATIONS_TL PECTL, PAY_INPUT_VALUES_F PIV, PAY_RUN_RESULT_VALUES PRV, PAY_RUN_RESULTS PRR, PAY_ACTION_INTERLOCKS PAC, PAY_ASSIGNMENT_ACTIONS PAA, PAY_PAYROLL_ACTIONS PPA WHERE PEC.LEGISLATION_CODE = 'IE' AND PEC.BUSINESS_GROUP_ID IS NULL and PEC.Classification_id = PECTL.classification_id AND PECTL.LANGUAGE = userenv('LANG') AND PET.element_type_id = PETTL.element_type_id AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID AND PAA.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID AND PETTL.LANGUAGE = userenv('LANG') AND PRR.ELEMENT_TYPE_ID= PET.ELEMENT_TYPE_ID AND PRR.STATUS IN ('P','PA') AND PET.CLASSIFICATION_ID = PEC.CLASSIFICATION_ID AND PIV.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID AND PRV.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID AND PRV.RUN_RESULT_ID = PRR.RUN_RESULT_ID AND ((PIV.NAME ='Pay Value' AND PIV.UOM = 'M') OR (PET.ELEMENT_NAME IN ('IE BIK Arrearage Details','IE BIK Arrearage Recovery Details') and pet.legislation_code = 'IE' AND pet.business_group_id is null AND PIV.NAME in ('BIK Arrearage','BIK Arrearage Recovered')) OR (PET.ELEMENT_NAME IN ('IE PAYE at higher rate','IE PAYE at standard rate') and pet.legislation_code = 'IE' AND pet.business_group_id is null AND PIV.NAME ='Value' )) AND PET.ELEMENT_NAME NOT IN ('IE PRSI','IE Net tax') AND PPA.EFFECTIVE_DATE BETWEEN PIV.EFFECTIVE_START_DATE AND PIV.EFFECTIVE_END_DATE AND PPA.EFFECTIVE_DATE BETWEEN PET.EFFECTIVE_START_DATE AND PET.EFFECTIVE_END_DATE AND PAC.LOCKED_ACTION_ID= PRR.ASSIGNMENT_ACTION_ID AND EXISTS (SELECT NULL FROM PAY_PAYROLL_ACTIONS PPAL, PAY_ASSIGNMENT_ACTIONS PAAL WHERE PAAL.ASSIGNMENT_ACTION_ID = PAC.LOCKING_ACTION_ID AND PPAL.PAYROLL_ACTION_ID = PAAL.PAYROLL_ACTION_ID AND PPAL.ACTION_TYPE IN ('P','U')) GROUP BY PEC.CLASSIFICATION_NAME, PAC.LOCKING_ACTION_ID, PET.PROCESSING_PRIORITY, PEC.CLASSIFICATION_NAME, NVL(PET.REPORTING_NAME, PET.ELEMENT_NAME), PET.ROWID

View Text - HTML Formatted

SELECT PET.ROWID ROW_ID
, NVL(PET.REPORTING_NAME
, PET.ELEMENT_NAME) REPORT_NAME
, PEC.CLASSIFICATION_NAME
, DECODE(NVL(PET.REPORTING_NAME
, PET.ELEMENT_NAME)
, 'BIK ARREARAGE'
, SUM(FND_NUMBER.CANONICAL_TO_NUMBER(PRV.RESULT_VALUE*(-1)))
, SUM(FND_NUMBER.CANONICAL_TO_NUMBER(PRV.RESULT_VALUE))) RESULT_VALUE
, PET.PROCESSING_PRIORITY
, PRR.ASSIGNMENT_ACTION_ID
, PEC.CLASSIFICATION_NAME BASE_CLASSIFICATION_NAME
, DECODE (PEC.CLASSIFICATION_NAME
, 'PAYE'
, 1
, 'PRSI'
, 2
, 'INCOME TAX LEVY'
, 3
, 'IE BENEFIT IN KIND ARREARAGE'
, 4
, 'IE BENEFIT IN KIND ARREARAGE RECOVERY'
, 5
, 'COURT ORDERS'
, 6
, 'PARKING LEVY'
, 7
, 'IE SOCIAL BENEFITS CLEARUP'
, 8
, 'PRE-TAX DEDUCTIONS'
, 9
, 'PRE TAX
AND PRE PRSI DEDUCTION'
, 10
, 'PRE PRSI DEDUCTION'
, 11
, 'VOLUNTARY DEDUCTIONS'
, 12
, 13) SORT_ORDER
FROM PAY_ELEMENT_TYPES_F PET
, PAY_ELEMENT_TYPES_F_TL PETTL
, PAY_ELEMENT_CLASSIFICATIONS PEC
, PAY_ELEMENT_CLASSIFICATIONS_TL PECTL
, PAY_INPUT_VALUES_F PIV
, PAY_RUN_RESULT_VALUES PRV
, PAY_RUN_RESULTS PRR
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
WHERE PEC.LEGISLATION_CODE = 'IE'
AND PEC.BUSINESS_GROUP_ID IS NULL
AND PEC.CLASSIFICATION_ID = PECTL.CLASSIFICATION_ID
AND PECTL.LANGUAGE = USERENV('LANG')
AND PET.ELEMENT_TYPE_ID = PETTL.ELEMENT_TYPE_ID
AND PETTL.LANGUAGE = USERENV('LANG')
AND PRR.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PAA.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
AND PRR.STATUS IN ('P'
, 'PA')
AND PET.CLASSIFICATION_ID = PEC.CLASSIFICATION_ID
AND PIV.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PRV.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
AND PRV.RUN_RESULT_ID = PRR.RUN_RESULT_ID
AND ((PIV.NAME ='PAY VALUE'
AND PIV.UOM = 'M') OR (PET.ELEMENT_NAME IN ('IE BIK ARREARAGE DETAILS'
, 'IE BIK ARREARAGE RECOVERY DETAILS')
AND PET.LEGISLATION_CODE= 'IE'
AND PET.BUSINESS_GROUP_ID IS NULL
AND PIV.NAME IN ('BIK ARREARAGE'
, 'BIK ARREARAGE RECOVERED')) OR (PET.ELEMENT_NAME IN ('IE PAYE AT HIGHER RATE'
, 'IE PAYE AT STANDARD RATE')
AND PET.LEGISLATION_CODE= 'IE'
AND PET.BUSINESS_GROUP_ID IS NULL
AND PIV.NAME ='VALUE' ))
AND PET.ELEMENT_NAME NOT IN ('IE PRSI'
, 'IE NET TAX')
AND PPA.EFFECTIVE_DATE BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE
AND PPA.EFFECTIVE_DATE BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE GROUP BY PEC.CLASSIFICATION_NAME
, PRR.ASSIGNMENT_ACTION_ID
, PET.PROCESSING_PRIORITY
, PEC.CLASSIFICATION_NAME
, NVL(PET.REPORTING_NAME
, PET.ELEMENT_NAME)
, PET.ROWID UNION ALL SELECT PET.ROWID ROW_ID
, NVL(PET.REPORTING_NAME
, PET.ELEMENT_NAME) REPORT_NAME
, PEC.CLASSIFICATION_NAME
, DECODE(NVL(PET.REPORTING_NAME
, PET.ELEMENT_NAME)
, 'BIK ARREARAGE'
, SUM(FND_NUMBER.CANONICAL_TO_NUMBER(PRV.RESULT_VALUE*(-1)))
, SUM(FND_NUMBER.CANONICAL_TO_NUMBER(PRV.RESULT_VALUE))) RESULT_VALUE
, PET.PROCESSING_PRIORITY
, PAC.LOCKING_ACTION_ID
, PEC.CLASSIFICATION_NAME BASE_CLASSIFICATION_NAME
, DECODE (PEC.CLASSIFICATION_NAME
, 'PAYE'
, 1
, 'PRSI'
, 2
, 'INCOME TAX LEVY'
, 3
, 'IE BENEFIT IN KIND ARREARAGE'
, 4
, 'IE BENEFIT IN KIND ARREARAGE RECOVERY'
, 5
, 'COURT ORDERS'
, 6
, 'PARKING LEVY'
, 7
, 'IE SOCIAL BENEFITS CLEARUP'
, 8
, 'PRE-TAX DEDUCTIONS'
, 9
, 'PRE TAX
AND PRE PRSI DEDUCTION'
, 10
, 'PRE PRSI DEDUCTION'
, 11
, 'VOLUNTARY DEDUCTIONS'
, 12
, 13) SORT_ORDER
FROM PAY_ELEMENT_TYPES_F PET
, PAY_ELEMENT_TYPES_F_TL PETTL
, PAY_ELEMENT_CLASSIFICATIONS PEC
, PAY_ELEMENT_CLASSIFICATIONS_TL PECTL
, PAY_INPUT_VALUES_F PIV
, PAY_RUN_RESULT_VALUES PRV
, PAY_RUN_RESULTS PRR
, PAY_ACTION_INTERLOCKS PAC
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
WHERE PEC.LEGISLATION_CODE = 'IE'
AND PEC.BUSINESS_GROUP_ID IS NULL
AND PEC.CLASSIFICATION_ID = PECTL.CLASSIFICATION_ID
AND PECTL.LANGUAGE = USERENV('LANG')
AND PET.ELEMENT_TYPE_ID = PETTL.ELEMENT_TYPE_ID
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PAA.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
AND PETTL.LANGUAGE = USERENV('LANG')
AND PRR.ELEMENT_TYPE_ID= PET.ELEMENT_TYPE_ID
AND PRR.STATUS IN ('P'
, 'PA')
AND PET.CLASSIFICATION_ID = PEC.CLASSIFICATION_ID
AND PIV.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PRV.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
AND PRV.RUN_RESULT_ID = PRR.RUN_RESULT_ID
AND ((PIV.NAME ='PAY VALUE'
AND PIV.UOM = 'M') OR (PET.ELEMENT_NAME IN ('IE BIK ARREARAGE DETAILS'
, 'IE BIK ARREARAGE RECOVERY DETAILS')
AND PET.LEGISLATION_CODE = 'IE'
AND PET.BUSINESS_GROUP_ID IS NULL
AND PIV.NAME IN ('BIK ARREARAGE'
, 'BIK ARREARAGE RECOVERED')) OR (PET.ELEMENT_NAME IN ('IE PAYE AT HIGHER RATE'
, 'IE PAYE AT STANDARD RATE')
AND PET.LEGISLATION_CODE = 'IE'
AND PET.BUSINESS_GROUP_ID IS NULL
AND PIV.NAME ='VALUE' ))
AND PET.ELEMENT_NAME NOT IN ('IE PRSI'
, 'IE NET TAX')
AND PPA.EFFECTIVE_DATE BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE
AND PPA.EFFECTIVE_DATE BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE
AND PAC.LOCKED_ACTION_ID= PRR.ASSIGNMENT_ACTION_ID
AND EXISTS (SELECT NULL
FROM PAY_PAYROLL_ACTIONS PPAL
, PAY_ASSIGNMENT_ACTIONS PAAL
WHERE PAAL.ASSIGNMENT_ACTION_ID = PAC.LOCKING_ACTION_ID
AND PPAL.PAYROLL_ACTION_ID = PAAL.PAYROLL_ACTION_ID
AND PPAL.ACTION_TYPE IN ('P'
, 'U')) GROUP BY PEC.CLASSIFICATION_NAME
, PAC.LOCKING_ACTION_ID
, PET.PROCESSING_PRIORITY
, PEC.CLASSIFICATION_NAME
, NVL(PET.REPORTING_NAME
, PET.ELEMENT_NAME)
, PET.ROWID