eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.PAY_HOURS_BY_RATE_V

Source

View Text - Preformatted


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)

View Text - HTML Formatted

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)