View: PAY_HOURS_BY_RATE_V

Product:	PAY - Payroll
Description:	The view returns the run assignment_action_id, element_type_id, element name, rate, Hours and multiplier for an Hours by Rate Element
Implementation/DBA Data:	APPS.PAY_HOURS_BY_RATE_V

View Text

SELECT ASSIGNMENT_ACTION_ID
, TO_NUMBER(ELEMENT_TYPE_ID) ELEMENT_TYPE_ID
, ELEMENT_NAME
, LEGISLATION_CODE
, PROCESSING_PRIORITY
, TO_NUMBER(RATE) RATE
, TO_NUMBER(MULTIPLE) MULTIPLE
, SUM(TO_NUMBER(HOURS)) HOURS
, SUM(TO_NUMBER(NVL(AMOUNT
, (HOURS*RATE*NVL(MULTIPLE
, 1))))) AMOUNT
FROM (SELECT /*+ LEADING(PRR) */ RV_EL.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID
, RV_EL.RESULT_VALUE ELEMENT_TYPE_ID
, NVL(PETTL.REPORTING_NAME
, PETTL.ELEMENT_NAME) ELEMENT_NAME
, RV_EL.LEGISLATION_CODE
, PET1.PROCESSING_PRIORITY
, PAY_HOURS_BY_RATE.GET_RESULT_VALUE(RV_EL.RUN_RESULT_ID
, RV_EL.ELEMENT_TYPE_ID
, 'RATE') RATE
, PAY_HOURS_BY_RATE.GET_RESULT_VALUE(RV_EL.RUN_RESULT_ID
, RV_EL.ELEMENT_TYPE_ID
, 'HOURS') HOURS
, PAY_HOURS_BY_RATE.GET_RESULT_VALUE(RV_EL.RUN_RESULT_ID
, RV_EL.ELEMENT_TYPE_ID
, 'MULTIPLE') MULTIPLE
, PAY_HOURS_BY_RATE.GET_RESULT_VALUE(RV_EL.RUN_RESULT_ID
, RV_EL.ELEMENT_TYPE_ID
, 'PAY VALUE') AMOUNT
FROM (SELECT /*+ INDEX(PRR PAY_RUN_RESULTS_N50) INDEX(PET PAY_ELEMENT_TYPES_F_UK2) INDEX(PRRV PAY_RUN_RESULT_VALUES_PK) */ PRRV.RESULT_VALUE
, /* CUSTOM ELEMENT */ PET.ELEMENT_TYPE_ID
, /* SEEDED HOURS BY RATE */ PET.EFFECTIVE_START_DATE
, PET.EFFECTIVE_END_DATE
, PET.LEGISLATION_CODE
, PRR.ASSIGNMENT_ACTION_ID
, PRR.RUN_RESULT_ID
FROM PAY_ELEMENT_TYPES_F PET
, PAY_RUN_RESULTS PRR
, PAY_RUN_RESULT_VALUES PRRV
WHERE PET.ELEMENT_NAME = 'HOURS BY RATE'
AND PET.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID
AND PRRV.RUN_RESULT_ID = PRR.RUN_RESULT_ID
AND PRRV.INPUT_VALUE_ID = (SELECT /*+ INDEX(PIV_EL PAY_INPUT_VALUES_F_UK2) */ PIV_EL.INPUT_VALUE_ID
FROM PAY_INPUT_VALUES_F PIV_EL
, FND_SESSIONS FS
WHERE PIV_EL.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PIV_EL.NAME = 'ELEMENT TYPE ID'
AND FS.EFFECTIVE_DATE BETWEEN PIV_EL.EFFECTIVE_START_DATE
AND PIV_EL.EFFECTIVE_END_DATE
AND FS.SESSION_ID = USERENV('SESSIONID'))) RV_EL
, PAY_ELEMENT_TYPES_F PET1
, PAY_ELEMENT_TYPES_F_TL PETTL
, FND_SESSIONS FS
WHERE FS.SESSION_ID = USERENV('SESSIONID')
AND FS.EFFECTIVE_DATE BETWEEN RV_EL.EFFECTIVE_START_DATE
AND RV_EL.EFFECTIVE_END_DATE
AND PET1.ELEMENT_TYPE_ID = RV_EL.RESULT_VALUE
AND PETTL.ELEMENT_TYPE_ID = PET1.ELEMENT_TYPE_ID
AND PETTL.LANGUAGE = USERENV('LANG')
AND FS.EFFECTIVE_DATE BETWEEN PET1.EFFECTIVE_START_DATE
AND PET1.EFFECTIVE_END_DATE) GROUP BY ASSIGNMENT_ACTION_ID
, LEGISLATION_CODE
, PROCESSING_PRIORITY
, TO_NUMBER(ELEMENT_TYPE_ID)
, ELEMENT_NAME
, TO_NUMBER(RATE)
, TO_NUMBER(MULTIPLE)

Columns