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_JP_GEPPEN_SANTEI_MT_V

Source

View Text - Preformatted


SELECT /*+ ORDERED NO_MERGE(HRV) INDEX(PPA PAY_PAYROLL_ACTIONS_FK1) INDEX(PAA PAY_ASSIGNMENT_ACTIONS_N50) INDEX(PEE PAY_ELEMENT_ENTRIES_F_FK3) INDEX(PEEV PAY_ELEMENT_ENTRY_VALUES_F_N50) INDEX(PA PER_ASSIGNMENTS_F_PK) */ paa.assignment_action_id, pa.assignment_id + 0, pa.person_id + 0, hrv.business_group_id, ppa.date_earned, ppa.effective_date, decode(ppa.element_set_id,hrv.element_set_id_geppen, 'G', 'S'), pay_jp_balance_pkg.get_entry_value_number(hrv.iv_id_si_org, pee.assignment_id, ppa.effective_date), substrb(pay_jp_balance_pkg.get_entry_value_char(hrv.iv_id_hi_num, pee.assignment_id, ppa.date_earned), 1, 10), substrb(pay_jp_balance_pkg.get_entry_value_char(hrv.iv_id_wp_num, pee.assignment_id, ppa.date_earned), 1, 10), sum(hrv.si_type) from ( select pbg.business_group_id, pesg.element_set_id ELEMENT_SET_ID_GEPPEN, pess.element_set_id ELEMENT_SET_ID_SANTEI, pivhi1.input_value_id IV_ID_HI_COMP_APPLY_MTH, pivwp1.input_value_id IV_ID_WP_COMP_APPLY_MTH, pivhi2.input_value_id IV_ID_HI_COMP_APPLY_TYP, pivwp2.input_value_id IV_ID_WP_COMP_APPLY_TYP, pivchi.input_value_id IV_ID_HI_NUM, pivcwp.input_value_id IV_ID_WP_NUM, pivco.input_value_id IV_ID_SI_ORG, decode(pivco.name, 'HI_LOCATION', 1, 'WP_LOCATION', 2, 'WPF_LOCATION', 4) SI_TYPE from per_business_groups_perf pbg, pay_element_sets pesg, pay_element_sets pess, pay_element_types_f pethi, pay_input_values_f pivhi1, pay_input_values_f pivhi2, pay_element_types_f petwp, pay_input_values_f pivwp1, pay_input_values_f pivwp2, pay_element_types_f petc, pay_input_values_f pivchi, pay_input_values_f pivcwp, pay_input_values_f pivco where pesg.element_set_name = 'GEP' and nvl(pesg.business_group_id, pbg.business_group_id) = pbg.business_group_id and nvl(pesg.legislation_code, pbg.legislation_code) = pbg.legislation_code and pess.element_set_name = 'SAN' and nvl(pess.business_group_id, pbg.business_group_id) = pbg.business_group_id and nvl(pess.legislation_code, pbg.legislation_code) = pbg.legislation_code and pethi.element_name = 'COM_HI_SMR_INFO' and nvl(pethi.business_group_id, pbg.business_group_id) = pbg.business_group_id and nvl(pethi.legislation_code, pbg.legislation_code) = pbg.legislation_code and pivhi1.element_type_id = pethi.element_type_id and pivhi1.name = 'APPLY_MTH' and pivhi2.element_type_id = pethi.element_type_id and pivhi2.name = 'APPLY_TYPE' and petwp.element_name = 'COM_WP_SMR_INFO' and nvl(petwp.business_group_id, pbg.business_group_id) = pbg.business_group_id and nvl(petwp.legislation_code, pbg.legislation_code) = pbg.legislation_code and pivwp1.element_type_id = petwp.element_type_id and pivwp1.name = 'APPLY_MTH' and pivwp2.element_type_id = petwp.element_type_id and pivwp2.name = 'APPLY_TYPE' and petc.element_name = 'COM_SI_INFO' and nvl(petc.business_group_id, pbg.business_group_id) = pbg.business_group_id and nvl(petc.legislation_code, pbg.legislation_code) = pbg.legislation_code and pivchi.element_type_id = petc.element_type_id and pivchi.name = 'HI_CARD_NUM' and pivcwp.element_type_id = petc.element_type_id and pivcwp.name = 'WP_SERIAL_NUM' and pivco.element_type_id = petc.element_type_id and pivco.name in ('HI_LOCATION', 'WP_LOCATION', 'WPF_LOCATION') group by pbg.business_group_id, pesg.element_set_id, pess.element_set_id, pivhi1.input_value_id, pivwp1.input_value_id, pivhi2.input_value_id, pivwp2.input_value_id, pivchi.input_value_id, pivcwp.input_value_id, pivco.input_value_id, pivco.name ) hrv, pay_payroll_actions ppa, pay_assignment_actions paa, pay_element_entries_f pee, pay_element_entry_values_f peev1, pay_element_entry_values_f peev2, per_all_assignments_f pa where ppa.business_group_id = hrv.business_group_id /* This view used by reports only target RUN with Geppen and Santei element set */ and ppa.action_type = 'R' and ppa.element_set_id in (hrv.element_set_id_geppen, hrv.element_set_id_santei) and paa.payroll_action_id = ppa.payroll_action_id and paa.action_status = 'C' and pa.assignment_id = paa.assignment_id and pa.primary_flag = 'Y' /* and pa.business_group_id + 0 = hrv.business_group_id */ and ppa.effective_date between pa.effective_start_date and pa.effective_end_date and pee.updating_action_id = paa.assignment_action_id and pee.effective_start_date = ppa.effective_date and pee.entry_type = 'E' and peev1.element_entry_id = pee.element_entry_id and peev1.effective_start_date = pee.effective_start_date and peev1.effective_end_date = pee.effective_end_date and ( ( peev1.input_value_id = hrv.iv_id_hi_comp_apply_mth and hrv.si_type = 1 ) or ( peev1.input_value_id = hrv.iv_id_wp_comp_apply_mth and hrv.si_type in (2, 4) ) ) and ( ( ppa.date_earned < TO_DATE('2003/04/01', 'YYYY/MM/DD') and peev1.screen_entry_value = DECODE(ppa.element_set_id, hrv.element_set_id_geppen, to_char(ppa.date_earned, 'YYYYMM'), to_char(ppa.date_earned, 'YYYY') || '10') ) or ( ppa.date_earned >= TO_DATE('2003/04/01', 'YYYY/MM/DD') and peev1.screen_entry_value = DECODE(ppa.element_set_id, hrv.element_set_id_geppen, to_char(ppa.date_earned, 'YYYYMM'), to_char(ppa.date_earned, 'YYYY') || '09') ) ) and peev2.element_entry_id = pee.element_entry_id and peev2.effective_start_date = pee.effective_start_date and peev2.effective_end_date = pee.effective_end_date and ( ( peev2.input_value_id = hrv.iv_id_hi_comp_apply_typ and hrv.si_type = 1 ) or ( peev2.input_value_id = hrv.iv_id_wp_comp_apply_typ and hrv.si_type in (2, 4) ) ) and peev2.screen_entry_value = decode(ppa.element_set_id,hrv.element_set_id_geppen, 'G', 'S') group by paa.assignment_action_id, pa.assignment_id, pa.person_id, hrv.business_group_id, ppa.date_earned, ppa.effective_date, decode(ppa.element_set_id,hrv.element_set_id_geppen, 'G', 'S'), pay_jp_balance_pkg.get_entry_value_number(hrv.iv_id_si_org, pee.assignment_id, ppa.effective_date), substrb(pay_jp_balance_pkg.get_entry_value_char(hrv.iv_id_hi_num, pee.assignment_id, ppa.date_earned), 1, 10), substrb(pay_jp_balance_pkg.get_entry_value_char(hrv.iv_id_wp_num, pee.assignment_id, ppa.date_earned), 1, 10) UNION select /*+ ORDERED NO_MERGE(HRV) INDEX(PPA PAY_PAYROLL_ACTIONS_FK1) INDEX(PAA PAY_ASSIGNMENT_ACTIONS_N50) INDEX(PRR PAY_RUN_RESULTS_N50) INDEX(PRRV PAY_RUN_RESULT_VALUES_N50) INDEX(PA PER_ASSIGNMENTS_F_PK) */ paa.assignment_action_id, pa.assignment_id, pa.person_id, hrv.business_group_id, ppa.date_earned, ppa.effective_date, 'S', pay_jp_balance_pkg.get_entry_value_number(hrv.iv_id_si_org, paa.assignment_id, ppa.effective_date), substrb(pay_jp_balance_pkg.get_entry_value_char(hrv.iv_id_hi_num, paa.assignment_id, ppa.date_earned), 1, 10), substrb(pay_jp_balance_pkg.get_entry_value_char(hrv.iv_id_wp_num, paa.assignment_id, ppa.date_earned), 1, 10), sum(hrv.si_type) from ( select pbg.business_group_id, pesg.element_set_id ELEMENT_SET_ID_GEPPEN, pess.element_set_id ELEMENT_SET_ID_SANTEI, pivhig.input_value_id IV_ID_HI_GEPPEN_MTH, pivwpg.input_value_id IV_ID_WP_GEPPEN_MTH, pivchi.input_value_id IV_ID_HI_NUM, pivcwp.input_value_id IV_ID_WP_NUM, pivco.input_value_id IV_ID_SI_ORG, decode(pivco.name, 'HI_LOCATION', 1, 'WP_LOCATION', 2, 'WPF_LOCATION', 4) SI_TYPE from per_business_groups_perf pbg, pay_element_sets pesg, pay_element_sets pess, pay_element_types_f petsig, pay_input_values_f pivhig, pay_input_values_f pivwpg, pay_element_types_f petc, pay_input_values_f pivchi, pay_input_values_f pivcwp, pay_input_values_f pivco where pesg.element_set_name = 'GEP' and nvl(pesg.business_group_id, pbg.business_group_id) = pbg.business_group_id and nvl(pesg.legislation_code, pbg.legislation_code) = pbg.legislation_code and pess.element_set_name = 'SAN' and nvl(pess.business_group_id, pbg.business_group_id) = pbg.business_group_id and nvl(pess.legislation_code, pbg.legislation_code) = pbg.legislation_code and petsig.element_name = 'SAN_GEP_MTH_RSLT' and nvl(petsig.business_group_id, pbg.business_group_id) = pbg.business_group_id and nvl(petsig.legislation_code, pbg.legislation_code) = pbg.legislation_code and pivhig.element_type_id = petsig.element_type_id and pivhig.name = 'HI' and pivwpg.element_type_id = petsig.element_type_id and pivwpg.name = 'WP' and petc.element_name = 'COM_SI_INFO' and nvl(petc.business_group_id, pbg.business_group_id) = pbg.business_group_id and nvl(petc.legislation_code, pbg.legislation_code) = pbg.legislation_code and pivchi.element_type_id = petc.element_type_id and pivchi.name = 'HI_CARD_NUM' and pivcwp.element_type_id = petc.element_type_id and pivcwp.name = 'WP_SERIAL_NUM' and pivco.element_type_id = petc.element_type_id and pivco.name in ('HI_LOCATION', 'WP_LOCATION', 'WPF_LOCATION') group by pbg.business_group_id, pesg.element_set_id, pess.element_set_id, pivhig.input_value_id, pivwpg.input_value_id, pivchi.input_value_id, pivcwp.input_value_id, pivco.input_value_id, pivco.name ) hrv, pay_payroll_actions ppa, pay_assignment_actions paa, pay_run_results prr, pay_run_result_values prrv, per_all_assignments_f pa where ppa.business_group_id = hrv.business_group_id /* This view used by reports only target RUN with Santei element set */ and ppa.action_type = 'R' and ppa.element_set_id = hrv.element_set_id_santei and paa.payroll_action_id = ppa.payroll_action_id and paa.action_status = 'C' and prr.assignment_action_id = paa.assignment_action_id and prr.status = 'P' and prrv.run_result_id = prr.run_result_id and ( ( prrv.input_value_id = hrv.iv_id_hi_geppen_mth and hrv.si_type = 1 and prrv.result_value is not null ) or ( prrv.input_value_id = hrv.iv_id_wp_geppen_mth and hrv.si_type in (2, 4) and prrv.result_value is not null ) ) and pa.assignment_id = paa.assignment_id and pa.primary_flag = 'Y' /* and pa.business_group_id + 0 = hrv.business_group_id */ and ppa.effective_date between pa.effective_start_date and pa.effective_end_date and prrv.result_value is not null group by paa.assignment_action_id, pa.assignment_id, pa.person_id, hrv.business_group_id, ppa.date_earned, ppa.effective_date, 'S', pay_jp_balance_pkg.get_entry_value_number(hrv.iv_id_si_org, paa.assignment_id, ppa.effective_date), substrb(pay_jp_balance_pkg.get_entry_value_char(hrv.iv_id_hi_num, paa.assignment_id, ppa.date_earned), 1, 10), substrb(pay_jp_balance_pkg.get_entry_value_char(hrv.iv_id_wp_num, paa.assignment_id, ppa.date_earned), 1, 10)

View Text - HTML Formatted

SELECT /*+ ORDERED NO_MERGE(HRV) INDEX(PPA PAY_PAYROLL_ACTIONS_FK1) INDEX(PAA PAY_ASSIGNMENT_ACTIONS_N50) INDEX(PEE PAY_ELEMENT_ENTRIES_F_FK3) INDEX(PEEV PAY_ELEMENT_ENTRY_VALUES_F_N50) INDEX(PA PER_ASSIGNMENTS_F_PK) */ PAA.ASSIGNMENT_ACTION_ID
, PA.ASSIGNMENT_ID + 0
, PA.PERSON_ID + 0
, HRV.BUSINESS_GROUP_ID
, PPA.DATE_EARNED
, PPA.EFFECTIVE_DATE
, DECODE(PPA.ELEMENT_SET_ID
, HRV.ELEMENT_SET_ID_GEPPEN
, 'G'
, 'S')
, PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_NUMBER(HRV.IV_ID_SI_ORG
, PEE.ASSIGNMENT_ID
, PPA.EFFECTIVE_DATE)
, SUBSTRB(PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR(HRV.IV_ID_HI_NUM
, PEE.ASSIGNMENT_ID
, PPA.DATE_EARNED)
, 1
, 10)
, SUBSTRB(PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR(HRV.IV_ID_WP_NUM
, PEE.ASSIGNMENT_ID
, PPA.DATE_EARNED)
, 1
, 10)
, SUM(HRV.SI_TYPE)
FROM ( SELECT PBG.BUSINESS_GROUP_ID
, PESG.ELEMENT_SET_ID ELEMENT_SET_ID_GEPPEN
, PESS.ELEMENT_SET_ID ELEMENT_SET_ID_SANTEI
, PIVHI1.INPUT_VALUE_ID IV_ID_HI_COMP_APPLY_MTH
, PIVWP1.INPUT_VALUE_ID IV_ID_WP_COMP_APPLY_MTH
, PIVHI2.INPUT_VALUE_ID IV_ID_HI_COMP_APPLY_TYP
, PIVWP2.INPUT_VALUE_ID IV_ID_WP_COMP_APPLY_TYP
, PIVCHI.INPUT_VALUE_ID IV_ID_HI_NUM
, PIVCWP.INPUT_VALUE_ID IV_ID_WP_NUM
, PIVCO.INPUT_VALUE_ID IV_ID_SI_ORG
, DECODE(PIVCO.NAME
, 'HI_LOCATION'
, 1
, 'WP_LOCATION'
, 2
, 'WPF_LOCATION'
, 4) SI_TYPE
FROM PER_BUSINESS_GROUPS_PERF PBG
, PAY_ELEMENT_SETS PESG
, PAY_ELEMENT_SETS PESS
, PAY_ELEMENT_TYPES_F PETHI
, PAY_INPUT_VALUES_F PIVHI1
, PAY_INPUT_VALUES_F PIVHI2
, PAY_ELEMENT_TYPES_F PETWP
, PAY_INPUT_VALUES_F PIVWP1
, PAY_INPUT_VALUES_F PIVWP2
, PAY_ELEMENT_TYPES_F PETC
, PAY_INPUT_VALUES_F PIVCHI
, PAY_INPUT_VALUES_F PIVCWP
, PAY_INPUT_VALUES_F PIVCO
WHERE PESG.ELEMENT_SET_NAME = 'GEP'
AND NVL(PESG.BUSINESS_GROUP_ID
, PBG.BUSINESS_GROUP_ID) = PBG.BUSINESS_GROUP_ID
AND NVL(PESG.LEGISLATION_CODE
, PBG.LEGISLATION_CODE) = PBG.LEGISLATION_CODE
AND PESS.ELEMENT_SET_NAME = 'SAN'
AND NVL(PESS.BUSINESS_GROUP_ID
, PBG.BUSINESS_GROUP_ID) = PBG.BUSINESS_GROUP_ID
AND NVL(PESS.LEGISLATION_CODE
, PBG.LEGISLATION_CODE) = PBG.LEGISLATION_CODE
AND PETHI.ELEMENT_NAME = 'COM_HI_SMR_INFO'
AND NVL(PETHI.BUSINESS_GROUP_ID
, PBG.BUSINESS_GROUP_ID) = PBG.BUSINESS_GROUP_ID
AND NVL(PETHI.LEGISLATION_CODE
, PBG.LEGISLATION_CODE) = PBG.LEGISLATION_CODE
AND PIVHI1.ELEMENT_TYPE_ID = PETHI.ELEMENT_TYPE_ID
AND PIVHI1.NAME = 'APPLY_MTH'
AND PIVHI2.ELEMENT_TYPE_ID = PETHI.ELEMENT_TYPE_ID
AND PIVHI2.NAME = 'APPLY_TYPE'
AND PETWP.ELEMENT_NAME = 'COM_WP_SMR_INFO'
AND NVL(PETWP.BUSINESS_GROUP_ID
, PBG.BUSINESS_GROUP_ID) = PBG.BUSINESS_GROUP_ID
AND NVL(PETWP.LEGISLATION_CODE
, PBG.LEGISLATION_CODE) = PBG.LEGISLATION_CODE
AND PIVWP1.ELEMENT_TYPE_ID = PETWP.ELEMENT_TYPE_ID
AND PIVWP1.NAME = 'APPLY_MTH'
AND PIVWP2.ELEMENT_TYPE_ID = PETWP.ELEMENT_TYPE_ID
AND PIVWP2.NAME = 'APPLY_TYPE'
AND PETC.ELEMENT_NAME = 'COM_SI_INFO'
AND NVL(PETC.BUSINESS_GROUP_ID
, PBG.BUSINESS_GROUP_ID) = PBG.BUSINESS_GROUP_ID
AND NVL(PETC.LEGISLATION_CODE
, PBG.LEGISLATION_CODE) = PBG.LEGISLATION_CODE
AND PIVCHI.ELEMENT_TYPE_ID = PETC.ELEMENT_TYPE_ID
AND PIVCHI.NAME = 'HI_CARD_NUM'
AND PIVCWP.ELEMENT_TYPE_ID = PETC.ELEMENT_TYPE_ID
AND PIVCWP.NAME = 'WP_SERIAL_NUM'
AND PIVCO.ELEMENT_TYPE_ID = PETC.ELEMENT_TYPE_ID
AND PIVCO.NAME IN ('HI_LOCATION'
, 'WP_LOCATION'
, 'WPF_LOCATION') GROUP BY PBG.BUSINESS_GROUP_ID
, PESG.ELEMENT_SET_ID
, PESS.ELEMENT_SET_ID
, PIVHI1.INPUT_VALUE_ID
, PIVWP1.INPUT_VALUE_ID
, PIVHI2.INPUT_VALUE_ID
, PIVWP2.INPUT_VALUE_ID
, PIVCHI.INPUT_VALUE_ID
, PIVCWP.INPUT_VALUE_ID
, PIVCO.INPUT_VALUE_ID
, PIVCO.NAME ) HRV
, PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_ELEMENT_ENTRIES_F PEE
, PAY_ELEMENT_ENTRY_VALUES_F PEEV1
, PAY_ELEMENT_ENTRY_VALUES_F PEEV2
, PER_ALL_ASSIGNMENTS_F PA
WHERE PPA.BUSINESS_GROUP_ID = HRV.BUSINESS_GROUP_ID /* THIS VIEW USED BY REPORTS ONLY TARGET RUN WITH GEPPEN
AND SANTEI ELEMENT SET */
AND PPA.ACTION_TYPE = 'R'
AND PPA.ELEMENT_SET_ID IN (HRV.ELEMENT_SET_ID_GEPPEN
, HRV.ELEMENT_SET_ID_SANTEI)
AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PAA.ACTION_STATUS = 'C'
AND PA.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PA.PRIMARY_FLAG = 'Y' /*
AND PA.BUSINESS_GROUP_ID + 0 = HRV.BUSINESS_GROUP_ID */
AND PPA.EFFECTIVE_DATE BETWEEN PA.EFFECTIVE_START_DATE
AND PA.EFFECTIVE_END_DATE
AND PEE.UPDATING_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PEE.EFFECTIVE_START_DATE = PPA.EFFECTIVE_DATE
AND PEE.ENTRY_TYPE = 'E'
AND PEEV1.ELEMENT_ENTRY_ID = PEE.ELEMENT_ENTRY_ID
AND PEEV1.EFFECTIVE_START_DATE = PEE.EFFECTIVE_START_DATE
AND PEEV1.EFFECTIVE_END_DATE = PEE.EFFECTIVE_END_DATE
AND ( ( PEEV1.INPUT_VALUE_ID = HRV.IV_ID_HI_COMP_APPLY_MTH
AND HRV.SI_TYPE = 1 ) OR ( PEEV1.INPUT_VALUE_ID = HRV.IV_ID_WP_COMP_APPLY_MTH
AND HRV.SI_TYPE IN (2
, 4) ) )
AND ( ( PPA.DATE_EARNED < TO_DATE('2003/04/01'
, 'YYYY/MM/DD')
AND PEEV1.SCREEN_ENTRY_VALUE = DECODE(PPA.ELEMENT_SET_ID
, HRV.ELEMENT_SET_ID_GEPPEN
, TO_CHAR(PPA.DATE_EARNED
, 'YYYYMM')
, TO_CHAR(PPA.DATE_EARNED
, 'YYYY') || '10') ) OR ( PPA.DATE_EARNED >= TO_DATE('2003/04/01'
, 'YYYY/MM/DD')
AND PEEV1.SCREEN_ENTRY_VALUE = DECODE(PPA.ELEMENT_SET_ID
, HRV.ELEMENT_SET_ID_GEPPEN
, TO_CHAR(PPA.DATE_EARNED
, 'YYYYMM')
, TO_CHAR(PPA.DATE_EARNED
, 'YYYY') || '09') ) )
AND PEEV2.ELEMENT_ENTRY_ID = PEE.ELEMENT_ENTRY_ID
AND PEEV2.EFFECTIVE_START_DATE = PEE.EFFECTIVE_START_DATE
AND PEEV2.EFFECTIVE_END_DATE = PEE.EFFECTIVE_END_DATE
AND ( ( PEEV2.INPUT_VALUE_ID = HRV.IV_ID_HI_COMP_APPLY_TYP
AND HRV.SI_TYPE = 1 ) OR ( PEEV2.INPUT_VALUE_ID = HRV.IV_ID_WP_COMP_APPLY_TYP
AND HRV.SI_TYPE IN (2
, 4) ) )
AND PEEV2.SCREEN_ENTRY_VALUE = DECODE(PPA.ELEMENT_SET_ID
, HRV.ELEMENT_SET_ID_GEPPEN
, 'G'
, 'S') GROUP BY PAA.ASSIGNMENT_ACTION_ID
, PA.ASSIGNMENT_ID
, PA.PERSON_ID
, HRV.BUSINESS_GROUP_ID
, PPA.DATE_EARNED
, PPA.EFFECTIVE_DATE
, DECODE(PPA.ELEMENT_SET_ID
, HRV.ELEMENT_SET_ID_GEPPEN
, 'G'
, 'S')
, PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_NUMBER(HRV.IV_ID_SI_ORG
, PEE.ASSIGNMENT_ID
, PPA.EFFECTIVE_DATE)
, SUBSTRB(PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR(HRV.IV_ID_HI_NUM
, PEE.ASSIGNMENT_ID
, PPA.DATE_EARNED)
, 1
, 10)
, SUBSTRB(PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR(HRV.IV_ID_WP_NUM
, PEE.ASSIGNMENT_ID
, PPA.DATE_EARNED)
, 1
, 10) UNION SELECT /*+ ORDERED NO_MERGE(HRV) INDEX(PPA PAY_PAYROLL_ACTIONS_FK1) INDEX(PAA PAY_ASSIGNMENT_ACTIONS_N50) INDEX(PRR PAY_RUN_RESULTS_N50) INDEX(PRRV PAY_RUN_RESULT_VALUES_N50) INDEX(PA PER_ASSIGNMENTS_F_PK) */ PAA.ASSIGNMENT_ACTION_ID
, PA.ASSIGNMENT_ID
, PA.PERSON_ID
, HRV.BUSINESS_GROUP_ID
, PPA.DATE_EARNED
, PPA.EFFECTIVE_DATE
, 'S'
, PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_NUMBER(HRV.IV_ID_SI_ORG
, PAA.ASSIGNMENT_ID
, PPA.EFFECTIVE_DATE)
, SUBSTRB(PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR(HRV.IV_ID_HI_NUM
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED)
, 1
, 10)
, SUBSTRB(PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR(HRV.IV_ID_WP_NUM
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED)
, 1
, 10)
, SUM(HRV.SI_TYPE)
FROM ( SELECT PBG.BUSINESS_GROUP_ID
, PESG.ELEMENT_SET_ID ELEMENT_SET_ID_GEPPEN
, PESS.ELEMENT_SET_ID ELEMENT_SET_ID_SANTEI
, PIVHIG.INPUT_VALUE_ID IV_ID_HI_GEPPEN_MTH
, PIVWPG.INPUT_VALUE_ID IV_ID_WP_GEPPEN_MTH
, PIVCHI.INPUT_VALUE_ID IV_ID_HI_NUM
, PIVCWP.INPUT_VALUE_ID IV_ID_WP_NUM
, PIVCO.INPUT_VALUE_ID IV_ID_SI_ORG
, DECODE(PIVCO.NAME
, 'HI_LOCATION'
, 1
, 'WP_LOCATION'
, 2
, 'WPF_LOCATION'
, 4) SI_TYPE
FROM PER_BUSINESS_GROUPS_PERF PBG
, PAY_ELEMENT_SETS PESG
, PAY_ELEMENT_SETS PESS
, PAY_ELEMENT_TYPES_F PETSIG
, PAY_INPUT_VALUES_F PIVHIG
, PAY_INPUT_VALUES_F PIVWPG
, PAY_ELEMENT_TYPES_F PETC
, PAY_INPUT_VALUES_F PIVCHI
, PAY_INPUT_VALUES_F PIVCWP
, PAY_INPUT_VALUES_F PIVCO
WHERE PESG.ELEMENT_SET_NAME = 'GEP'
AND NVL(PESG.BUSINESS_GROUP_ID
, PBG.BUSINESS_GROUP_ID) = PBG.BUSINESS_GROUP_ID
AND NVL(PESG.LEGISLATION_CODE
, PBG.LEGISLATION_CODE) = PBG.LEGISLATION_CODE
AND PESS.ELEMENT_SET_NAME = 'SAN'
AND NVL(PESS.BUSINESS_GROUP_ID
, PBG.BUSINESS_GROUP_ID) = PBG.BUSINESS_GROUP_ID
AND NVL(PESS.LEGISLATION_CODE
, PBG.LEGISLATION_CODE) = PBG.LEGISLATION_CODE
AND PETSIG.ELEMENT_NAME = 'SAN_GEP_MTH_RSLT'
AND NVL(PETSIG.BUSINESS_GROUP_ID
, PBG.BUSINESS_GROUP_ID) = PBG.BUSINESS_GROUP_ID
AND NVL(PETSIG.LEGISLATION_CODE
, PBG.LEGISLATION_CODE) = PBG.LEGISLATION_CODE
AND PIVHIG.ELEMENT_TYPE_ID = PETSIG.ELEMENT_TYPE_ID
AND PIVHIG.NAME = 'HI'
AND PIVWPG.ELEMENT_TYPE_ID = PETSIG.ELEMENT_TYPE_ID
AND PIVWPG.NAME = 'WP'
AND PETC.ELEMENT_NAME = 'COM_SI_INFO'
AND NVL(PETC.BUSINESS_GROUP_ID
, PBG.BUSINESS_GROUP_ID) = PBG.BUSINESS_GROUP_ID
AND NVL(PETC.LEGISLATION_CODE
, PBG.LEGISLATION_CODE) = PBG.LEGISLATION_CODE
AND PIVCHI.ELEMENT_TYPE_ID = PETC.ELEMENT_TYPE_ID
AND PIVCHI.NAME = 'HI_CARD_NUM'
AND PIVCWP.ELEMENT_TYPE_ID = PETC.ELEMENT_TYPE_ID
AND PIVCWP.NAME = 'WP_SERIAL_NUM'
AND PIVCO.ELEMENT_TYPE_ID = PETC.ELEMENT_TYPE_ID
AND PIVCO.NAME IN ('HI_LOCATION'
, 'WP_LOCATION'
, 'WPF_LOCATION') GROUP BY PBG.BUSINESS_GROUP_ID
, PESG.ELEMENT_SET_ID
, PESS.ELEMENT_SET_ID
, PIVHIG.INPUT_VALUE_ID
, PIVWPG.INPUT_VALUE_ID
, PIVCHI.INPUT_VALUE_ID
, PIVCWP.INPUT_VALUE_ID
, PIVCO.INPUT_VALUE_ID
, PIVCO.NAME ) HRV
, PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_RUN_RESULTS PRR
, PAY_RUN_RESULT_VALUES PRRV
, PER_ALL_ASSIGNMENTS_F PA
WHERE PPA.BUSINESS_GROUP_ID = HRV.BUSINESS_GROUP_ID /* THIS VIEW USED BY REPORTS ONLY TARGET RUN WITH SANTEI ELEMENT SET */
AND PPA.ACTION_TYPE = 'R'
AND PPA.ELEMENT_SET_ID = HRV.ELEMENT_SET_ID_SANTEI
AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PAA.ACTION_STATUS = 'C'
AND PRR.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PRR.STATUS = 'P'
AND PRRV.RUN_RESULT_ID = PRR.RUN_RESULT_ID
AND ( ( PRRV.INPUT_VALUE_ID = HRV.IV_ID_HI_GEPPEN_MTH
AND HRV.SI_TYPE = 1
AND PRRV.RESULT_VALUE IS NOT NULL ) OR ( PRRV.INPUT_VALUE_ID = HRV.IV_ID_WP_GEPPEN_MTH
AND HRV.SI_TYPE IN (2
, 4)
AND PRRV.RESULT_VALUE IS NOT NULL ) )
AND PA.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PA.PRIMARY_FLAG = 'Y' /*
AND PA.BUSINESS_GROUP_ID + 0 = HRV.BUSINESS_GROUP_ID */
AND PPA.EFFECTIVE_DATE BETWEEN PA.EFFECTIVE_START_DATE
AND PA.EFFECTIVE_END_DATE
AND PRRV.RESULT_VALUE IS NOT NULL GROUP BY PAA.ASSIGNMENT_ACTION_ID
, PA.ASSIGNMENT_ID
, PA.PERSON_ID
, HRV.BUSINESS_GROUP_ID
, PPA.DATE_EARNED
, PPA.EFFECTIVE_DATE
, 'S'
, PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_NUMBER(HRV.IV_ID_SI_ORG
, PAA.ASSIGNMENT_ID
, PPA.EFFECTIVE_DATE)
, SUBSTRB(PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR(HRV.IV_ID_HI_NUM
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED)
, 1
, 10)
, SUBSTRB(PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR(HRV.IV_ID_WP_NUM
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED)
, 1
, 10)