VIEW: APPS.PAY_ZA_ELEMENT_TYPES_V1

Source

View Text - Preformatted

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' OR PIV.NAME = 'Remuneration Amount') 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' OR PIV.NAME = 'Remuneration Amount') 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

View Text - HTML Formatted

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' OR PIV.NAME = 'REMUNERATION AMOUNT')
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' OR PIV.NAME = 'REMUNERATION AMOUNT')
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