VIEW: APPS.PAY_JP_SOE_RUN_DEDUCTIONS_V

Source

View Text - Preformatted

SELECT v1.assignment_action_id, nvl(v1.reporting_name,v1.element_name), v1.run_result_id, nvl(to_number(prrv.result_value),0) from pay_run_result_values prrv, hr_lookups hrl, pay_input_values_f piv, ( select paa.assignment_action_id, ppa.effective_date, prr.run_result_id, pet.element_type_id, pettl.reporting_name, pettl.element_name from hr_jp_parameters hjp, pay_element_types_f_tl pettl, pay_element_types_f pet, pay_run_results prr, pay_payroll_actions ppa, pay_assignment_actions paa where ppa.payroll_action_id=paa.payroll_action_id and prr.assignment_action_id=paa.assignment_action_id and prr.status in ('P','PA') and pet.element_type_id=prr.element_type_id and ppa.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 hjp.owner='PAYJPSOE' and hjp.parameter_value=to_char(pet.classification_id) and ( ( hjp.parameter_name || '' in ( 'SAL_SI_PREM_CLASS_ID', 'SAL_SI_PREM_NONRES_CLASS_ID', 'SAL_DEDUCT_CLASS_ID', 'BON_SI_PREM_CLASS_ID', 'BON_SI_PREM_NONRES_CLASS_ID', 'BON_DEDUCT_CLASS_ID', 'SP_BON_SI_PREM_CLASS_ID', 'SP_BON_SI_PREM_NONRES_CLASS_ID', 'SP_BON_DEDUCT_CLASS_ID', 'TERM_DEDUCT_CLASS_ID') ) or( hjp.parameter_name = 'YEA_CLASS_ID' and exists( select NULL from PAY_SUB_CLASSIFICATION_RULES_F sub, hr_jp_parameters hjp2 where hjp2.owner='PAYJPSOE' and hjp2.parameter_name='YEA_ITAX_DEDUCT_CLASS_ID' and sub.element_type_id=pet.element_type_id and sub.classification_id=to_number(hjp2.parameter_value) and ppa.effective_date between sub.effective_start_date and sub.effective_end_date) ) ) ) v1 where piv.element_type_id(+)=v1.element_type_id and piv.uom='M' and v1.effective_date between piv.effective_start_date and piv.effective_end_date and hrl.lookup_type='NAME_TRANSLATIONS' and upper(hrl.lookup_code)='PAY VALUE' and hrl.meaning=piv.name and prrv.run_result_id=nvl(v1.run_result_id,piv.input_value_id) and prrv.input_value_id=piv.input_value_id

View Text - HTML Formatted

SELECT V1.ASSIGNMENT_ACTION_ID
, NVL(V1.REPORTING_NAME
, V1.ELEMENT_NAME)
, V1.RUN_RESULT_ID
, NVL(TO_NUMBER(PRRV.RESULT_VALUE)
, 0)
FROM PAY_RUN_RESULT_VALUES PRRV
, HR_LOOKUPS HRL
, PAY_INPUT_VALUES_F PIV
, ( SELECT PAA.ASSIGNMENT_ACTION_ID
, PPA.EFFECTIVE_DATE
, PRR.RUN_RESULT_ID
, PET.ELEMENT_TYPE_ID
, PETTL.REPORTING_NAME
, PETTL.ELEMENT_NAME
FROM HR_JP_PARAMETERS HJP
, PAY_ELEMENT_TYPES_F_TL PETTL
, PAY_ELEMENT_TYPES_F PET
, PAY_RUN_RESULTS PRR
, PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAA
WHERE PPA.PAYROLL_ACTION_ID=PAA.PAYROLL_ACTION_ID
AND PRR.ASSIGNMENT_ACTION_ID=PAA.ASSIGNMENT_ACTION_ID
AND PRR.STATUS IN ('P'
, 'PA')
AND PET.ELEMENT_TYPE_ID=PRR.ELEMENT_TYPE_ID
AND PPA.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 HJP.OWNER='PAYJPSOE'
AND HJP.PARAMETER_VALUE=TO_CHAR(PET.CLASSIFICATION_ID)
AND ( ( HJP.PARAMETER_NAME || '' IN ( 'SAL_SI_PREM_CLASS_ID'
, 'SAL_SI_PREM_NONRES_CLASS_ID'
, 'SAL_DEDUCT_CLASS_ID'
, 'BON_SI_PREM_CLASS_ID'
, 'BON_SI_PREM_NONRES_CLASS_ID'
, 'BON_DEDUCT_CLASS_ID'
, 'SP_BON_SI_PREM_CLASS_ID'
, 'SP_BON_SI_PREM_NONRES_CLASS_ID'
, 'SP_BON_DEDUCT_CLASS_ID'
, 'TERM_DEDUCT_CLASS_ID') ) OR( HJP.PARAMETER_NAME = 'YEA_CLASS_ID'
AND EXISTS( SELECT NULL
FROM PAY_SUB_CLASSIFICATION_RULES_F SUB
, HR_JP_PARAMETERS HJP2
WHERE HJP2.OWNER='PAYJPSOE'
AND HJP2.PARAMETER_NAME='YEA_ITAX_DEDUCT_CLASS_ID'
AND SUB.ELEMENT_TYPE_ID=PET.ELEMENT_TYPE_ID
AND SUB.CLASSIFICATION_ID=TO_NUMBER(HJP2.PARAMETER_VALUE)
AND PPA.EFFECTIVE_DATE BETWEEN SUB.EFFECTIVE_START_DATE
AND SUB.EFFECTIVE_END_DATE) ) ) ) V1
WHERE PIV.ELEMENT_TYPE_ID(+)=V1.ELEMENT_TYPE_ID
AND PIV.UOM='M'
AND V1.EFFECTIVE_DATE BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE
AND HRL.LOOKUP_TYPE='NAME_TRANSLATIONS'
AND UPPER(HRL.LOOKUP_CODE)='PAY VALUE'
AND HRL.MEANING=PIV.NAME
AND PRRV.RUN_RESULT_ID=NVL(V1.RUN_RESULT_ID
, PIV.INPUT_VALUE_ID)
AND PRRV.INPUT_VALUE_ID=PIV.INPUT_VALUE_ID