DBA Data[Home] [Help]

VIEW: APPS.PAY_JP_SI_BON_PAYMENT_V

Source

View Text - Preformatted

SELECT /*+ ORDERED NO_MERGE(iv) AND_EQUAL(PPA PAY_PAYROLL_ACTIONS_FK6,PAY_PAYROLL_ACTIONS_FK1) INDEX(PAA PAY_ASSIGNMENT_ACTIONS_N50) AND_EQUAL(PRR PAY_RUN_RESULTS_N50,PAY_RUN_RESULTS_N1) INDEX(PRRV PAY_RUN_RESULT_VALUES_PK) INDEX(PA PER_ASSIGNMENTS_F_PK) */ ppa.payroll_action_id payroll_action_id, paa.assignment_action_id assignment_action_id, paa.action_sequence action_sequence, pa.assignment_id assignment_id, pa.person_id person_id, iv.business_group_id business_group_id, ppa.effective_date effective_date, ppa.date_earned date_earned, pay_core_utils.get_parameter('SI_SUM_MTD_BON',ppa.legislative_parameters) si_sum_mtd_bon, pay_jp_balance_pkg.get_entry_value_number(iv.org_iv_id,paa.assignment_id,ppa.effective_date) si_org_id, pay_jp_balance_pkg.get_entry_value_char(iv.hi_num_iv_id,paa.assignment_id,ppa.effective_date) hi_number, pay_jp_balance_pkg.get_entry_value_char(iv.wp_num_iv_id,paa.assignment_id,ppa.effective_date) wp_number, sum(iv.si_type) si_type from (select /*+ ORDERED INDEX(SI_ELE PAY_ELEMENT_TYPES_F_UK2) INDEX(HIWP_RSLT_ELE PAY_ELEMENT_TYPES_F_UK2) INDEX(HIWP_RSLT_SAL_IV PAY_INPUT_VALUES_F_UK2) INDEX(HIWP_RSLT_MAT_IV PAY_INPUT_VALUES_F_UK2) INDEX(ORG PAY_INPUT_VALUES_F_UK2) INDEX(HI_NUM PAY_INPUT_VALUES_F_UK2) INDEX(WP_NUM PAY_INPUT_VALUES_F_UK2) */ pbg.business_group_id, pbg.legislation_code, hiwp_rslt_ele.element_type_id hiwp_rslt_ele_id, hiwp_rslt_sal_iv.input_value_id hiwp_rslt_sal_iv_id, hiwp_rslt_sal_iv.uom hiwp_rslt_sal_uom, hiwp_rslt_mat_iv.input_value_id hiwp_rslt_mat_iv_id, hiwp_rslt_mat_iv.uom hiwp_rslt_mat_uom, pes.element_set_name bon_ele_set_code, pes.element_set_id bon_ele_set_id, org.input_value_id org_iv_id, hi_num.input_value_id hi_num_iv_id, wp_num.input_value_id wp_num_iv_id, decode(org.name,'HI_LOCATION',1,'WP_LOCATION',2,'WPF_LOCATION',4) si_type from /* this is faster than perf here */ per_business_groups pbg, pay_element_sets pes, pay_element_types_f si_ele, pay_element_types_f hiwp_rslt_ele, pay_input_values_f hiwp_rslt_sal_iv, pay_input_values_f hiwp_rslt_mat_iv, pay_input_values_f org, pay_input_values_f hi_num, pay_input_values_f wp_num where pes.element_set_name = 'BON' and nvl(pes.business_group_id,pbg.business_group_id) = pbg.business_group_id and nvl(pes.legislation_code,pbg.legislation_code) = pbg.legislation_code and si_ele.element_name = 'COM_SI_INFO' /* ELM_SI */ and nvl(si_ele.business_group_id,pbg.business_group_id) = pbg.business_group_id and nvl(si_ele.legislation_code,pbg.legislation_code) = pbg.legislation_code and hiwp_rslt_ele.element_name in ('BON_HI_STD_BON','BON_WP_STD_BON') and nvl(hiwp_rslt_ele.business_group_id,pbg.business_group_id) = pbg.business_group_id and nvl(hiwp_rslt_ele.legislation_code,pbg.legislation_code) = pbg.legislation_code and hiwp_rslt_sal_iv.element_type_id = hiwp_rslt_ele.element_type_id and hiwp_rslt_sal_iv.name = 'ERN_MONEY' /* IV_SAL */ and hiwp_rslt_mat_iv.element_type_id = hiwp_rslt_ele.element_type_id and hiwp_rslt_mat_iv.name = 'ERN_KIND' /* IV_MAT */ and org.element_type_id = si_ele.element_type_id and ((hiwp_rslt_ele.element_name = 'BON_HI_STD_BON' and org.name = 'HI_LOCATION') /* IV_HI_ORG */ or (hiwp_rslt_ele.element_name = 'BON_WP_STD_BON' and org.name in ('WP_LOCATION', /* IV_WP_ORG */ 'WPF_LOCATION'))) /* IV_WPF_ORG */ and hi_num.element_type_id = si_ele.element_type_id and hi_num.name = 'HI_CARD_NUM' /* IV_HI_NUMBER */ and wp_num.element_type_id = si_ele.element_type_id and wp_num.name = 'WP_SERIAL_NUM') iv, /* IV_WP_NUMBER */ pay_payroll_actions ppa, pay_assignment_actions paa, pay_run_results prr, pay_run_result_values sal_rv, pay_run_result_values mat_rv, per_all_assignments_f pa where ppa.business_group_id = iv.business_group_id and ppa.action_type='R' and ppa.element_set_id = iv.bon_ele_set_id and paa.payroll_action_id = ppa.payroll_action_id and paa.action_status = 'C' /* This is for excluding not only payment amount<1000 but also skipped process from si_type. */ /* if nvl(BON_HI(WP)_STD_BON.ERN_MONEY + ERN_KIND,0) < 1000 then the emp is excluded. */ /* Childcare absent and Expected termination assignment can be included by setting premium element entry as 0 */ and prr.assignment_action_id = paa.assignment_action_id and prr.element_type_id = iv.hiwp_rslt_ele_id and sal_rv.run_result_id = prr.run_result_id and sal_rv.input_value_id = iv.hiwp_rslt_sal_iv_id and mat_rv.run_result_id = prr.run_result_id and mat_rv.input_value_id = iv.hiwp_rslt_mat_iv_id and decode(iv.hiwp_rslt_sal_uom,'M',to_number(nvl(sal_rv.result_value,'0')),0) + decode(iv.hiwp_rslt_mat_uom,'M',to_number(nvl(mat_rv.result_value,'0')),0) >= 1000 /* This validation is for case of inconsistent between si_type and result by process_flag */ /* This logic can cover the case of childcare absence and expected termination because result 0 premium will be returned. */ and exists( select /*+ ORDERED INDEX(SI_BON_ERR_ELE PAY_ELEMENT_TYPES_F_UK2) AND_EQUAL(SI_BON_ERR_RV PAY_RUN_RESULTS_N50,PAY_RUN_RESULTS_N1) */ null from pay_element_types_f si_bon_err_ele, pay_run_results si_bon_err_rv where si_bon_err_ele.element_name = decode(iv.si_type, 1, 'BON_HI_PREM_ER', /* JP_ELM_RSLT_HI_ER_BON */ 2, 'BON_WP_PREM_ER', /* JP_ELM_RSLT_WP_ER_BON */ 4, 'BON_WPF_PREM_ER') /* JP_ELM_RSLT_WPF_ER_BON */ and nvl(si_bon_err_ele.business_group_id,iv.business_group_id) = iv.business_group_id and nvl(si_bon_err_ele.legislation_code,iv.legislation_code) = iv.legislation_code and si_bon_err_rv.assignment_action_id = paa.assignment_action_id and si_bon_err_rv.element_type_id = si_bon_err_ele.element_type_id) and pa.assignment_id = paa.assignment_id /* Commented for Rehire before final processing date */ /* and pa.primary_flag = 'Y' */ and ppa.effective_date between pa.effective_start_date and pa.effective_end_date group by ppa.payroll_action_id, paa.assignment_action_id, paa.action_sequence, pa.assignment_id, pa.person_id, iv.business_group_id, ppa.effective_date, ppa.date_earned, pay_core_utils.get_parameter('SI_SUM_MTD_BON',ppa.legislative_parameters), pay_jp_balance_pkg.get_entry_value_number(iv.org_iv_id,paa.assignment_id,ppa.effective_date), pay_jp_balance_pkg.get_entry_value_char(iv.hi_num_iv_id,paa.assignment_id,ppa.effective_date), pay_jp_balance_pkg.get_entry_value_char(iv.wp_num_iv_id,paa.assignment_id,ppa.effective_date)
View Text - HTML Formatted

SELECT /*+ ORDERED NO_MERGE(IV) AND_EQUAL(PPA PAY_PAYROLL_ACTIONS_FK6
, PAY_PAYROLL_ACTIONS_FK1) INDEX(PAA PAY_ASSIGNMENT_ACTIONS_N50) AND_EQUAL(PRR PAY_RUN_RESULTS_N50
, PAY_RUN_RESULTS_N1) INDEX(PRRV PAY_RUN_RESULT_VALUES_PK) INDEX(PA PER_ASSIGNMENTS_F_PK) */ PPA.PAYROLL_ACTION_ID PAYROLL_ACTION_ID
, PAA.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID
, PAA.ACTION_SEQUENCE ACTION_SEQUENCE
, PA.ASSIGNMENT_ID ASSIGNMENT_ID
, PA.PERSON_ID PERSON_ID
, IV.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PPA.EFFECTIVE_DATE EFFECTIVE_DATE
, PPA.DATE_EARNED DATE_EARNED
, PAY_CORE_UTILS.GET_PARAMETER('SI_SUM_MTD_BON'
, PPA.LEGISLATIVE_PARAMETERS) SI_SUM_MTD_BON
, PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_NUMBER(IV.ORG_IV_ID
, PAA.ASSIGNMENT_ID
, PPA.EFFECTIVE_DATE) SI_ORG_ID
, PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR(IV.HI_NUM_IV_ID
, PAA.ASSIGNMENT_ID
, PPA.EFFECTIVE_DATE) HI_NUMBER
, PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR(IV.WP_NUM_IV_ID
, PAA.ASSIGNMENT_ID
, PPA.EFFECTIVE_DATE) WP_NUMBER
, SUM(IV.SI_TYPE) SI_TYPE
FROM (SELECT /*+ ORDERED INDEX(SI_ELE PAY_ELEMENT_TYPES_F_UK2) INDEX(HIWP_RSLT_ELE PAY_ELEMENT_TYPES_F_UK2) INDEX(HIWP_RSLT_SAL_IV PAY_INPUT_VALUES_F_UK2) INDEX(HIWP_RSLT_MAT_IV PAY_INPUT_VALUES_F_UK2) INDEX(ORG PAY_INPUT_VALUES_F_UK2) INDEX(HI_NUM PAY_INPUT_VALUES_F_UK2) INDEX(WP_NUM PAY_INPUT_VALUES_F_UK2) */ PBG.BUSINESS_GROUP_ID
, PBG.LEGISLATION_CODE
, HIWP_RSLT_ELE.ELEMENT_TYPE_ID HIWP_RSLT_ELE_ID
, HIWP_RSLT_SAL_IV.INPUT_VALUE_ID HIWP_RSLT_SAL_IV_ID
, HIWP_RSLT_SAL_IV.UOM HIWP_RSLT_SAL_UOM
, HIWP_RSLT_MAT_IV.INPUT_VALUE_ID HIWP_RSLT_MAT_IV_ID
, HIWP_RSLT_MAT_IV.UOM HIWP_RSLT_MAT_UOM
, PES.ELEMENT_SET_NAME BON_ELE_SET_CODE
, PES.ELEMENT_SET_ID BON_ELE_SET_ID
, ORG.INPUT_VALUE_ID ORG_IV_ID
, HI_NUM.INPUT_VALUE_ID HI_NUM_IV_ID
, WP_NUM.INPUT_VALUE_ID WP_NUM_IV_ID
, DECODE(ORG.NAME
, 'HI_LOCATION'
, 1
, 'WP_LOCATION'
, 2
, 'WPF_LOCATION'
, 4) SI_TYPE
FROM /* THIS IS FASTER THAN PERF HERE */ PER_BUSINESS_GROUPS PBG
, PAY_ELEMENT_SETS PES
, PAY_ELEMENT_TYPES_F SI_ELE
, PAY_ELEMENT_TYPES_F HIWP_RSLT_ELE
, PAY_INPUT_VALUES_F HIWP_RSLT_SAL_IV
, PAY_INPUT_VALUES_F HIWP_RSLT_MAT_IV
, PAY_INPUT_VALUES_F ORG
, PAY_INPUT_VALUES_F HI_NUM
, PAY_INPUT_VALUES_F WP_NUM
WHERE PES.ELEMENT_SET_NAME = 'BON'
AND NVL(PES.BUSINESS_GROUP_ID
, PBG.BUSINESS_GROUP_ID) = PBG.BUSINESS_GROUP_ID
AND NVL(PES.LEGISLATION_CODE
, PBG.LEGISLATION_CODE) = PBG.LEGISLATION_CODE
AND SI_ELE.ELEMENT_NAME = 'COM_SI_INFO' /* ELM_SI */
AND NVL(SI_ELE.BUSINESS_GROUP_ID
, PBG.BUSINESS_GROUP_ID) = PBG.BUSINESS_GROUP_ID
AND NVL(SI_ELE.LEGISLATION_CODE
, PBG.LEGISLATION_CODE) = PBG.LEGISLATION_CODE
AND HIWP_RSLT_ELE.ELEMENT_NAME IN ('BON_HI_STD_BON'
, 'BON_WP_STD_BON')
AND NVL(HIWP_RSLT_ELE.BUSINESS_GROUP_ID
, PBG.BUSINESS_GROUP_ID) = PBG.BUSINESS_GROUP_ID
AND NVL(HIWP_RSLT_ELE.LEGISLATION_CODE
, PBG.LEGISLATION_CODE) = PBG.LEGISLATION_CODE
AND HIWP_RSLT_SAL_IV.ELEMENT_TYPE_ID = HIWP_RSLT_ELE.ELEMENT_TYPE_ID
AND HIWP_RSLT_SAL_IV.NAME = 'ERN_MONEY' /* IV_SAL */
AND HIWP_RSLT_MAT_IV.ELEMENT_TYPE_ID = HIWP_RSLT_ELE.ELEMENT_TYPE_ID
AND HIWP_RSLT_MAT_IV.NAME = 'ERN_KIND' /* IV_MAT */
AND ORG.ELEMENT_TYPE_ID = SI_ELE.ELEMENT_TYPE_ID
AND ((HIWP_RSLT_ELE.ELEMENT_NAME = 'BON_HI_STD_BON'
AND ORG.NAME = 'HI_LOCATION') /* IV_HI_ORG */ OR (HIWP_RSLT_ELE.ELEMENT_NAME = 'BON_WP_STD_BON'
AND ORG.NAME IN ('WP_LOCATION'
, /* IV_WP_ORG */ 'WPF_LOCATION'))) /* IV_WPF_ORG */
AND HI_NUM.ELEMENT_TYPE_ID = SI_ELE.ELEMENT_TYPE_ID
AND HI_NUM.NAME = 'HI_CARD_NUM' /* IV_HI_NUMBER */
AND WP_NUM.ELEMENT_TYPE_ID = SI_ELE.ELEMENT_TYPE_ID
AND WP_NUM.NAME = 'WP_SERIAL_NUM') IV
, /* IV_WP_NUMBER */ PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_RUN_RESULTS PRR
, PAY_RUN_RESULT_VALUES SAL_RV
, PAY_RUN_RESULT_VALUES MAT_RV
, PER_ALL_ASSIGNMENTS_F PA
WHERE PPA.BUSINESS_GROUP_ID = IV.BUSINESS_GROUP_ID
AND PPA.ACTION_TYPE='R'
AND PPA.ELEMENT_SET_ID = IV.BON_ELE_SET_ID
AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PAA.ACTION_STATUS = 'C' /* THIS IS FOR EXCLUDING NOT ONLY PAYMENT AMOUNT<1000 BUT ALSO SKIPPED PROCESS
FROM SI_TYPE. */ /* IF NVL(BON_HI(WP)_STD_BON.ERN_MONEY + ERN_KIND
, 0) < 1000 THEN THE EMP IS EXCLUDED. */ /* CHILDCARE ABSENT
AND EXPECTED TERMINATION ASSIGNMENT CAN BE INCLUDED BY SETTING PREMIUM ELEMENT ENTRY AS 0 */
AND PRR.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PRR.ELEMENT_TYPE_ID = IV.HIWP_RSLT_ELE_ID
AND SAL_RV.RUN_RESULT_ID = PRR.RUN_RESULT_ID
AND SAL_RV.INPUT_VALUE_ID = IV.HIWP_RSLT_SAL_IV_ID
AND MAT_RV.RUN_RESULT_ID = PRR.RUN_RESULT_ID
AND MAT_RV.INPUT_VALUE_ID = IV.HIWP_RSLT_MAT_IV_ID
AND DECODE(IV.HIWP_RSLT_SAL_UOM
, 'M'
, TO_NUMBER(NVL(SAL_RV.RESULT_VALUE
, '0'))
, 0) + DECODE(IV.HIWP_RSLT_MAT_UOM
, 'M'
, TO_NUMBER(NVL(MAT_RV.RESULT_VALUE
, '0'))
, 0) >= 1000 /* THIS VALIDATION IS FOR CASE OF INCONSISTENT BETWEEN SI_TYPE
AND RESULT BY PROCESS_FLAG */ /* THIS LOGIC CAN COVER THE CASE OF CHILDCARE ABSENCE
AND EXPECTED TERMINATION BECAUSE RESULT 0 PREMIUM WILL BE RETURNED. */
AND EXISTS( SELECT /*+ ORDERED INDEX(SI_BON_ERR_ELE PAY_ELEMENT_TYPES_F_UK2) AND_EQUAL(SI_BON_ERR_RV PAY_RUN_RESULTS_N50
, PAY_RUN_RESULTS_N1) */ NULL
FROM PAY_ELEMENT_TYPES_F SI_BON_ERR_ELE
, PAY_RUN_RESULTS SI_BON_ERR_RV
WHERE SI_BON_ERR_ELE.ELEMENT_NAME = DECODE(IV.SI_TYPE
, 1
, 'BON_HI_PREM_ER'
, /* JP_ELM_RSLT_HI_ER_BON */ 2
, 'BON_WP_PREM_ER'
, /* JP_ELM_RSLT_WP_ER_BON */ 4
, 'BON_WPF_PREM_ER') /* JP_ELM_RSLT_WPF_ER_BON */
AND NVL(SI_BON_ERR_ELE.BUSINESS_GROUP_ID
, IV.BUSINESS_GROUP_ID) = IV.BUSINESS_GROUP_ID
AND NVL(SI_BON_ERR_ELE.LEGISLATION_CODE
, IV.LEGISLATION_CODE) = IV.LEGISLATION_CODE
AND SI_BON_ERR_RV.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND SI_BON_ERR_RV.ELEMENT_TYPE_ID = SI_BON_ERR_ELE.ELEMENT_TYPE_ID)
AND PA.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID /* COMMENTED FOR REHIRE BEFORE FINAL PROCESSING DATE */ /*
AND PA.PRIMARY_FLAG = 'Y' */
AND PPA.EFFECTIVE_DATE BETWEEN PA.EFFECTIVE_START_DATE
AND PA.EFFECTIVE_END_DATE GROUP BY PPA.PAYROLL_ACTION_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ACTION_SEQUENCE
, PA.ASSIGNMENT_ID
, PA.PERSON_ID
, IV.BUSINESS_GROUP_ID
, PPA.EFFECTIVE_DATE
, PPA.DATE_EARNED
, PAY_CORE_UTILS.GET_PARAMETER('SI_SUM_MTD_BON'
, PPA.LEGISLATIVE_PARAMETERS)
, PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_NUMBER(IV.ORG_IV_ID
, PAA.ASSIGNMENT_ID
, PPA.EFFECTIVE_DATE)
, PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR(IV.HI_NUM_IV_ID
, PAA.ASSIGNMENT_ID
, PPA.EFFECTIVE_DATE)
, PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR(IV.WP_NUM_IV_ID
, PAA.ASSIGNMENT_ID
, PPA.EFFECTIVE_DATE)