View: PAY_IE_ELEMENT_TYPES_V

Product:	PAY - Payroll
Description:	Used for displaying Element Types in IE SOE Form
Implementation/DBA Data:	APPS.PAY_IE_ELEMENT_TYPES_V

View Text

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

Columns