VIEW: APPS.PAY_JP_SOE_COSTS_V

Source

View Text - Preformatted

SELECT v.assignment_action_id, v.input_value_id, v.run_result_id, nvl(pec.costing_debit_or_credit,'D'), nvl(pettl.reporting_name,pettl.element_name) || decode(pivtl.name,hr_general.pay_value,NULL,'(' || pivtl.name || ')'), decode(v.dr_count,1,'N',0,'N',null,'N','Y'), decode(v.dr_count,1,v.dr_cost_allocation_keyflex_id, null), v.dr_value, decode(v.cr_count,1,'N',0,'N',null,'N','Y'), decode(v.cr_count,1,v.cr_cost_allocation_keyflex_id, null), v.cr_value from pay_element_types_f_tl pettl, pay_element_classifications pec, pay_element_types_f pet, pay_input_values_f_tl pivtl, /* Run pact */ pay_payroll_actions ppa, /* Run assact */ pay_assignment_actions paa, pay_run_results prr, ( select pc.assignment_action_id, pc.input_value_id, pc.run_result_id, count(decode(pc.debit_or_credit,'D',pc.cost_allocation_keyflex_id,NULL)) dr_count, max(decode(pc.debit_or_credit,'D',pc.cost_allocation_keyflex_id,NULL)) dr_cost_allocation_keyflex_id, nvl(sum(decode(pc.debit_or_credit,'D',pc.costed_value,NULL)),0) dr_value, count(decode(pc.debit_or_credit,'C',pc.cost_allocation_keyflex_id,NULL)) cr_count, max(decode(pc.debit_or_credit,'C',pc.cost_allocation_keyflex_id,NULL)) cr_cost_allocation_keyflex_id, nvl(sum(decode(pc.debit_or_credit,'C',pc.costed_value,NULL)),0) cr_value from pay_costs pc group by pc.assignment_action_id, pc.input_value_id, pc.run_result_id ) v where prr.run_result_id = v.run_result_id and paa.assignment_action_id = prr.assignment_action_id and ppa.payroll_action_id = paa.payroll_action_id and pivtl.input_value_id = v.input_value_id and pivtl.language = userenv('LANG') and pet.element_type_id = prr.element_type_id and ppa.date_earned between pet.effective_start_date and pet.effective_end_date and pec.classification_id = pet.classification_id and pettl.element_type_id = pet.element_type_id and pettl.language = userenv('LANG')

View Text - HTML Formatted

SELECT V.ASSIGNMENT_ACTION_ID
, V.INPUT_VALUE_ID
, V.RUN_RESULT_ID
, NVL(PEC.COSTING_DEBIT_OR_CREDIT
, 'D')
, NVL(PETTL.REPORTING_NAME
, PETTL.ELEMENT_NAME) || DECODE(PIVTL.NAME
, HR_GENERAL.PAY_VALUE
, NULL
, '(' || PIVTL.NAME || ')')
, DECODE(V.DR_COUNT
, 1
, 'N'
, 0
, 'N'
, NULL
, 'N'
, 'Y')
, DECODE(V.DR_COUNT
, 1
, V.DR_COST_ALLOCATION_KEYFLEX_ID
, NULL)
, V.DR_VALUE
, DECODE(V.CR_COUNT
, 1
, 'N'
, 0
, 'N'
, NULL
, 'N'
, 'Y')
, DECODE(V.CR_COUNT
, 1
, V.CR_COST_ALLOCATION_KEYFLEX_ID
, NULL)
, V.CR_VALUE
FROM PAY_ELEMENT_TYPES_F_TL PETTL
, PAY_ELEMENT_CLASSIFICATIONS PEC
, PAY_ELEMENT_TYPES_F PET
, PAY_INPUT_VALUES_F_TL PIVTL
, /* RUN PACT */ PAY_PAYROLL_ACTIONS PPA
, /* RUN ASSACT */ PAY_ASSIGNMENT_ACTIONS PAA
, PAY_RUN_RESULTS PRR
, ( SELECT PC.ASSIGNMENT_ACTION_ID
, PC.INPUT_VALUE_ID
, PC.RUN_RESULT_ID
, COUNT(DECODE(PC.DEBIT_OR_CREDIT
, 'D'
, PC.COST_ALLOCATION_KEYFLEX_ID
, NULL)) DR_COUNT
, MAX(DECODE(PC.DEBIT_OR_CREDIT
, 'D'
, PC.COST_ALLOCATION_KEYFLEX_ID
, NULL)) DR_COST_ALLOCATION_KEYFLEX_ID
, NVL(SUM(DECODE(PC.DEBIT_OR_CREDIT
, 'D'
, PC.COSTED_VALUE
, NULL))
, 0) DR_VALUE
, COUNT(DECODE(PC.DEBIT_OR_CREDIT
, 'C'
, PC.COST_ALLOCATION_KEYFLEX_ID
, NULL)) CR_COUNT
, MAX(DECODE(PC.DEBIT_OR_CREDIT
, 'C'
, PC.COST_ALLOCATION_KEYFLEX_ID
, NULL)) CR_COST_ALLOCATION_KEYFLEX_ID
, NVL(SUM(DECODE(PC.DEBIT_OR_CREDIT
, 'C'
, PC.COSTED_VALUE
, NULL))
, 0) CR_VALUE
FROM PAY_COSTS PC GROUP BY PC.ASSIGNMENT_ACTION_ID
, PC.INPUT_VALUE_ID
, PC.RUN_RESULT_ID ) V
WHERE PRR.RUN_RESULT_ID = V.RUN_RESULT_ID
AND PAA.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PIVTL.INPUT_VALUE_ID = V.INPUT_VALUE_ID
AND PIVTL.LANGUAGE = USERENV('LANG')
AND PET.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID
AND PPA.DATE_EARNED BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE
AND PEC.CLASSIFICATION_ID = PET.CLASSIFICATION_ID
AND PETTL.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PETTL.LANGUAGE = USERENV('LANG')