View: PAY_GB_PAY_VALUES_V

Product:	PAY - Payroll
Description:
Implementation/DBA Data:	APPS.PAY_GB_PAY_VALUES_V

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(PRRV.RESULT_VALUE)) RESULT_VALUE
, PET.PROCESSING_PRIORITY
, BAL_ASSACT.ASSIGNMENT_ACTION_ID
, PEC.CLASSIFICATION_NAME BASE_CLASSIFICATION_NAME
FROM PAY_ASSIGNMENT_ACTIONS BAL_ASSACT
, PAY_PAYROLL_ACTIONS BACT
, PAY_ASSIGNMENT_ACTIONS PPASSACT
, PAY_ACTION_INTERLOCKS INTLCK2
, PAY_ASSIGNMENT_ACTIONS ASSACT
, PAY_PAYROLL_ACTIONS PACT
, PAY_RUN_TYPES_F PRT
, PAY_RUN_RESULTS RR
, PAY_RUN_RESULT_VALUES PRRV
, PAY_INPUT_VALUES_F PIV
, PAY_ELEMENT_TYPES_F PET
, PAY_ELEMENT_CLASSIFICATIONS PEC
, PAY_ELEMENT_TYPES_F_TL PETTL
, PAY_ELEMENT_CLASSIFICATIONS_TL PECTL
WHERE BAL_ASSACT.PAYROLL_ACTION_ID = BACT.PAYROLL_ACTION_ID
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 PIV.NAME = 'PAY VALUE'
AND PIV.UOM = 'M'
AND PIV.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID
AND PET.ELEMENT_TYPE_ID = PIV.ELEMENT_TYPE_ID
AND PET.ELEMENT_TYPE_ID = RR.ELEMENT_TYPE_ID
AND PACT.EFFECTIVE_DATE BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE
AND PEC.CLASSIFICATION_ID = PET.CLASSIFICATION_ID
AND PRRV.RUN_RESULT_ID = RR.RUN_RESULT_ID
AND RR.ASSIGNMENT_ACTION_ID = ASSACT.ASSIGNMENT_ACTION_ID
AND ASSACT.PAYROLL_ACTION_ID = PACT.PAYROLL_ACTION_ID
AND PACT.EFFECTIVE_DATE BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE
AND RR.STATUS IN ('P'
, 'PA')
AND BACT.ACTION_TYPE IN ('P'
, 'U')
AND PPASSACT.ASSIGNMENT_ID = BAL_ASSACT.ASSIGNMENT_ID
AND PPASSACT.ASSIGNMENT_ACTION_ID = PAY_CORE_UTILS.GET_PP_ACTION_ID(BACT.ACTION_TYPE
, BAL_ASSACT.ASSIGNMENT_ACTION_ID)
AND PPASSACT.ASSIGNMENT_ACTION_ID = INTLCK2.LOCKING_ACTION_ID
AND INTLCK2.LOCKED_ACTION_ID = ASSACT.ASSIGNMENT_ACTION_ID
AND NVL(ASSACT.RUN_TYPE_ID
, -999) = PRT.RUN_TYPE_ID (+) GROUP BY PEC.CLASSIFICATION_NAME
, BAL_ASSACT.ASSIGNMENT_ACTION_ID
, PET.PROCESSING_PRIORITY
, PECTL.CLASSIFICATION_NAME
, NVL(PETTL.REPORTING_NAME
, PETTL.ELEMENT_NAME)
, 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(PRRV.RESULT_VALUE)) RESULT_VALUE
, PET.PROCESSING_PRIORITY
, BAL_ASSACT.ASSIGNMENT_ACTION_ID
, PEC.CLASSIFICATION_NAME BASE_CLASSIFICATION_NAME
FROM PAY_ASSIGNMENT_ACTIONS BAL_ASSACT
, PAY_PAYROLL_ACTIONS BACT
, PAY_ASSIGNMENT_ACTIONS PPASSACT
, PAY_ASSIGNMENT_ACTIONS ASSACT
, PAY_PAYROLL_ACTIONS PACT
, PAY_RUN_TYPES_F PRT
, PAY_RUN_RESULTS RR
, PAY_RUN_RESULT_VALUES PRRV
, PAY_INPUT_VALUES_F PIV
, PAY_ELEMENT_TYPES_F PET
, PAY_ELEMENT_CLASSIFICATIONS PEC
, PAY_ELEMENT_TYPES_F_TL PETTL
, PAY_ELEMENT_CLASSIFICATIONS_TL PECTL
WHERE BAL_ASSACT.PAYROLL_ACTION_ID = BACT.PAYROLL_ACTION_ID
AND PIV.NAME = 'PAY VALUE'
AND PIV.UOM = 'M'
AND PIV.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID
AND PET.ELEMENT_TYPE_ID = PIV.ELEMENT_TYPE_ID
AND PET.ELEMENT_TYPE_ID = RR.ELEMENT_TYPE_ID
AND PACT.EFFECTIVE_DATE BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE
AND PETTL.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PETTL.LANGUAGE = USERENV('LANG')
AND PEC.CLASSIFICATION_ID = PET.CLASSIFICATION_ID
AND PECTL.CLASSIFICATION_ID = PEC.CLASSIFICATION_ID
AND PECTL.LANGUAGE = USERENV('LANG')
AND PRRV.RUN_RESULT_ID = RR.RUN_RESULT_ID
AND RR.ASSIGNMENT_ACTION_ID = ASSACT.ASSIGNMENT_ACTION_ID
AND ASSACT.PAYROLL_ACTION_ID = PACT.PAYROLL_ACTION_ID
AND PACT.EFFECTIVE_DATE BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE
AND RR.STATUS IN ('P'
, 'PA')
AND BACT.ACTION_TYPE IN ('R'
, 'Q'
, 'V')
AND PPASSACT.ASSIGNMENT_ID = BAL_ASSACT.ASSIGNMENT_ID
AND PPASSACT.ASSIGNMENT_ACTION_ID = DECODE(BACT.ACTION_TYPE
, 'V'
, BAL_ASSACT.ASSIGNMENT_ACTION_ID
, HR_GBBAL.GET_MASTER_ACTION_ID( BACT.ACTION_TYPE
, BAL_ASSACT.ASSIGNMENT_ACTION_ID))
AND PPASSACT.ASSIGNMENT_ID = ASSACT.ASSIGNMENT_ID
AND PPASSACT.PAYROLL_ACTION_ID = ASSACT.PAYROLL_ACTION_ID
AND PPASSACT.ASSIGNMENT_ACTION_ID = NVL(ASSACT.SOURCE_ACTION_ID
, ASSACT.ASSIGNMENT_ACTION_ID)
AND NVL(ASSACT.RUN_TYPE_ID
, -999) = PRT.RUN_TYPE_ID(+)
AND ASSACT.ASSIGNMENT_ACTION_ID = DECODE(BACT.ACTION_TYPE
, 'R'
, DECODE(NVL(BAL_ASSACT.SOURCE_ACTION_ID
, -999)
, -999
, DECODE(NVL(PRT.RUN_METHOD
, 'N')
, 'S'
, -999
, ASSACT.ASSIGNMENT_ACTION_ID)
, BAL_ASSACT.ASSIGNMENT_ACTION_ID)
, 'Q'
, DECODE(NVL(BAL_ASSACT.SOURCE_ACTION_ID
, -999)
, -999
, DECODE(NVL(PRT.RUN_METHOD
, 'N')
, 'S'
, -999
, ASSACT.ASSIGNMENT_ACTION_ID)
, BAL_ASSACT.ASSIGNMENT_ACTION_ID)
, 'V'
, BAL_ASSACT.ASSIGNMENT_ACTION_ID ) GROUP BY PEC.CLASSIFICATION_NAME
, BAL_ASSACT.ASSIGNMENT_ACTION_ID
, PET.PROCESSING_PRIORITY
, PECTL.CLASSIFICATION_NAME
, NVL(PETTL.REPORTING_NAME
, PETTL.ELEMENT_NAME)
, PET.ROWID

Columns