View: PAY_ELEMENT_TYPES_V1

Product:	PAY - Payroll
Description:	View based on PAY_RUN_RESULT_VALUES used by the PAYGBSOE form and report.
Implementation/DBA Data:	APPS.PAY_ELEMENT_TYPES_V1

View Text

SELECT PET.ROWID ROW_ID
, NVL(PETTL.REPORTING_NAME
, PETTL.ELEMENT_NAME) REPORT_NAME
, PECTL.CLASSIFICATION_NAME
, 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_TL PETTL
, PAY_ELEMENT_CLASSIFICATIONS_TL PECTL
, PAY_ELEMENT_TYPES_F PET
, PAY_ELEMENT_CLASSIFICATIONS PEC
, PAY_INPUT_VALUES_F PIV
, PAY_RUN_RESULT_VALUES PRV
, PAY_RUN_RESULTS PRR
, PAY_ASSIGNMENT_ACTIONS ASACT
, PAY_PAYROLL_ACTIONS PACT
WHERE 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 PRR.STATUS IN ('P'
, 'PA')
AND PACT.PAYROLL_ACTION_ID = ASACT.PAYROLL_ACTION_ID
AND ASACT.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
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'
AND PACT.EFFECTIVE_DATE BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE
AND PACT.EFFECTIVE_DATE BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE GROUP BY PEC.CLASSIFICATION_NAME
, PRR.ASSIGNMENT_ACTION_ID
, PET.PROCESSING_PRIORITY
, PECTL.CLASSIFICATION_NAME
, NVL(PETTL.REPORTING_NAME
, PETTL.ELEMENT_NAME)
, /* DECODE(PETTL.PROCESSING_TYPE
, 'N'
, NULL
, PRV.RUN_RESULT_ID)
, */ PET.ROWID UNION ALL SELECT PET.ROWID ROW_ID
, NVL(PETTL.REPORTING_NAME
, PETTL.ELEMENT_NAME) REPORT_NAME
, PECTL.CLASSIFICATION_NAME
, 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_ASSIGNMENT_ACTIONS ASACT
, PAY_PAYROLL_ACTIONS PACT
, PAY_ACTION_INTERLOCKS PAC
WHERE 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 PRR.STATUS IN ('P'
, 'PA')
AND PACT.PAYROLL_ACTION_ID = ASACT.PAYROLL_ACTION_ID
AND ASACT.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
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'
AND PACT.EFFECTIVE_DATE BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE
AND PACT.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
, PECTL.CLASSIFICATION_NAME
, NVL(PETTL.REPORTING_NAME
, PETTL.ELEMENT_NAME)
, /* DECODE(PET.PROCESSING_TYPE
, 'N'
, NULL
, PRV.RUN_RESULT_ID)
, */ PET.ROWID

Columns