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_V

Source

View Text - Preformatted


SELECT /*+ ORDERED USE_NL(PPA, PAA, PEE_APPL, PEEV_APPL_MTH, PEEV_APPL_CAT, PLIV_ORG, PEE_ORG, PEEV_ORG, PEEV_HI_NUM, PEEV_WP_NUM) INDEX(PPA PAY_PAYROLL_ACTIONS_FK6) INDEX(PAA PAY_ASSIGNMENT_ACTIONS_N50) INDEX(PEE_APPL PAY_ELEMENT_ENTRIES_F_FK3) INDEX(PEEV_APPL_MTH PAY_ELEMENT_ENTRY_VALUES_F_N50) INDEX(PEEV_APPL_CAT PAY_ELEMENT_ENTRY_VALUES_F_N50) INDEX(PLIV_ORG PAY_LINK_INPUT_VALUES_F_N2) INDEX(PEE_ORG PAY_ELEMENT_ENTRIES_F_N51) INDEX(PEEV_ORG PAY_ELEMENT_ENTRY_VALUES_F_N50) INDEX(PEEV_HI_NUM PAY_ELEMENT_ENTRY_VALUES_F_N50) INDEX(PEEV_WP_NUM PAY_ELEMENT_ENTRY_VALUES_F_N50) */ paa.assignment_action_id, min(paa.assignment_id) assignment_id, min(ppa.business_group_id) business_group_id, min(ppa.payroll_id) payroll_id, min(ppa.consolidation_set_id) consolidation_set_id, min(ppa.date_earned) date_earned, min(ppa.effective_date) effective_date, min(nvl(peev_appl_cat.screen_entry_value,'O')) applied_category, max(to_number(nvl(peev_org.screen_entry_value,-1))) organization_id, min(substrb(peev_hi_num.screen_entry_value,1,10)) hi_number, min(substrb(peev_wp_num.screen_entry_value,1,10)) wp_number, sum(decode(peev_org.input_value_id, pay_jp_report_pkg.get_si_rec_id('hi_org_iv_id'),1, pay_jp_report_pkg.get_si_rec_id('wp_org_iv_id'),2, pay_jp_report_pkg.get_si_rec_id('wpf_org_iv_id'),4,0)) si_type, min(nvl(substrb(pay_jp_balance_pkg.get_entry_value_char( pay_jp_report_pkg.get_si_rec_id('exc_iv_id'), paa.assignment_id, ppa.effective_date),1,1),'N')) output_flag, ppa.element_set_id, min(peev_appl_mth.screen_entry_value) applied_month from pay_payroll_actions ppa, pay_assignment_actions paa, pay_element_entries_f pee_appl, pay_element_entry_values_f peev_appl_mth, pay_element_entry_values_f peev_appl_cat, (select pay_jp_report_pkg.get_si_rec_id('hi_org_iv_id') org_iv_id from dual union select pay_jp_report_pkg.get_si_rec_id('wp_org_iv_id') org_iv_id from dual union select pay_jp_report_pkg.get_si_rec_id('wpf_org_iv_id') org_iv_id from dual) iv_org, pay_link_input_values_f pliv_org, pay_element_entries_f pee_org, pay_element_entry_values_f peev_org, pay_element_entry_values_f peev_hi_num, pay_element_entry_values_f peev_wp_num where ppa.element_set_id in ( pay_jp_report_pkg.get_gs_rec_id('san_ele_set_id'), pay_jp_report_pkg.get_gs_rec_id('gep_ele_set_id'), pay_jp_report_pkg.get_gs_rec_id('iku_ele_set_id')) and ppa.action_type = 'R' and paa.payroll_action_id = ppa.payroll_action_id and paa.action_status = 'C' and pee_appl.updating_action_id = paa.assignment_action_id and pee_appl.effective_start_date = ppa.effective_date and pee_appl.entry_type = 'E' and peev_appl_mth.element_entry_id = pee_appl.element_entry_id and peev_appl_mth.effective_start_date = pee_appl.effective_start_date and peev_appl_mth.effective_end_date = pee_appl.effective_end_date and peev_appl_mth.input_value_id in ( pay_jp_report_pkg.get_gs_rec_id('hi_appl_mth_iv_id'), pay_jp_report_pkg.get_gs_rec_id('wp_appl_mth_iv_id')) and peev_appl_mth.screen_entry_value = decode(ppa.element_set_id, pay_jp_report_pkg.get_gs_rec_id('san_ele_set_id'), decode(sign(to_date('2003/04/01','YYYY/MM/DD') - ppa.date_earned), 1,to_char(ppa.date_earned,'YYYY')||'10', to_char(ppa.date_earned,'YYYY')||'09'), to_char(ppa.date_earned,'YYYYMM')) and peev_appl_cat.element_entry_id = pee_appl.element_entry_id and peev_appl_cat.effective_start_date = pee_appl.effective_start_date and peev_appl_cat.effective_end_date = pee_appl.effective_end_date and peev_appl_cat.input_value_id in ( pay_jp_report_pkg.get_gs_rec_id('hi_appl_cat_iv_id'), pay_jp_report_pkg.get_gs_rec_id('wp_appl_cat_iv_id')) and peev_appl_cat.screen_entry_value = decode(ppa.element_set_id, pay_jp_report_pkg.get_gs_rec_id('san_ele_set_id'),'S', pay_jp_report_pkg.get_gs_rec_id('gep_ele_set_id'),'G','I') and pliv_org.input_value_id = iv_org.org_iv_id and ((peev_appl_cat.input_value_id = pay_jp_report_pkg.get_gs_rec_id('hi_appl_cat_iv_id') and pliv_org.input_value_id = pay_jp_report_pkg.get_si_rec_id('hi_org_iv_id')) or (peev_appl_cat.input_value_id = pay_jp_report_pkg.get_gs_rec_id('wp_appl_cat_iv_id') and pliv_org.input_value_id in ( pay_jp_report_pkg.get_si_rec_id('wp_org_iv_id'), pay_jp_report_pkg.get_si_rec_id('wpf_org_iv_id')))) and ppa.effective_date between pliv_org.effective_start_date and pliv_org.effective_end_date and pee_org.element_link_id = pliv_org.element_link_id and pee_org.assignment_id = pee_appl.assignment_id and ppa.effective_date between pee_org.effective_start_date and pee_org.effective_end_date and peev_org.element_entry_id = pee_org.element_entry_id and peev_org.input_value_id = pliv_org.input_value_id and peev_org.effective_start_date = pee_org.effective_start_date and peev_org.effective_end_date = pee_org.effective_end_date and peev_hi_num.element_entry_id = pee_org.element_entry_id and peev_hi_num.input_value_id = pay_jp_report_pkg.get_si_rec_id('hi_num_iv_id') and peev_hi_num.effective_start_date = pee_org.effective_start_date and peev_hi_num.effective_end_date = pee_org.effective_end_date and peev_wp_num.element_entry_id = pee_org.element_entry_id and peev_wp_num.input_value_id = pay_jp_report_pkg.get_si_rec_id('wp_num_iv_id') and peev_wp_num.effective_start_date = pee_org.effective_start_date and peev_wp_num.effective_end_date = pee_org.effective_end_date group by paa.assignment_action_id, ppa.element_set_id

View Text - HTML Formatted

SELECT /*+ ORDERED USE_NL(PPA
, PAA
, PEE_APPL
, PEEV_APPL_MTH
, PEEV_APPL_CAT
, PLIV_ORG
, PEE_ORG
, PEEV_ORG
, PEEV_HI_NUM
, PEEV_WP_NUM) INDEX(PPA PAY_PAYROLL_ACTIONS_FK6) INDEX(PAA PAY_ASSIGNMENT_ACTIONS_N50) INDEX(PEE_APPL PAY_ELEMENT_ENTRIES_F_FK3) INDEX(PEEV_APPL_MTH PAY_ELEMENT_ENTRY_VALUES_F_N50) INDEX(PEEV_APPL_CAT PAY_ELEMENT_ENTRY_VALUES_F_N50) INDEX(PLIV_ORG PAY_LINK_INPUT_VALUES_F_N2) INDEX(PEE_ORG PAY_ELEMENT_ENTRIES_F_N51) INDEX(PEEV_ORG PAY_ELEMENT_ENTRY_VALUES_F_N50) INDEX(PEEV_HI_NUM PAY_ELEMENT_ENTRY_VALUES_F_N50) INDEX(PEEV_WP_NUM PAY_ELEMENT_ENTRY_VALUES_F_N50) */ PAA.ASSIGNMENT_ACTION_ID
, MIN(PAA.ASSIGNMENT_ID) ASSIGNMENT_ID
, MIN(PPA.BUSINESS_GROUP_ID) BUSINESS_GROUP_ID
, MIN(PPA.PAYROLL_ID) PAYROLL_ID
, MIN(PPA.CONSOLIDATION_SET_ID) CONSOLIDATION_SET_ID
, MIN(PPA.DATE_EARNED) DATE_EARNED
, MIN(PPA.EFFECTIVE_DATE) EFFECTIVE_DATE
, MIN(NVL(PEEV_APPL_CAT.SCREEN_ENTRY_VALUE
, 'O')) APPLIED_CATEGORY
, MAX(TO_NUMBER(NVL(PEEV_ORG.SCREEN_ENTRY_VALUE
, -1))) ORGANIZATION_ID
, MIN(SUBSTRB(PEEV_HI_NUM.SCREEN_ENTRY_VALUE
, 1
, 10)) HI_NUMBER
, MIN(SUBSTRB(PEEV_WP_NUM.SCREEN_ENTRY_VALUE
, 1
, 10)) WP_NUMBER
, SUM(DECODE(PEEV_ORG.INPUT_VALUE_ID
, PAY_JP_REPORT_PKG.GET_SI_REC_ID('HI_ORG_IV_ID')
, 1
, PAY_JP_REPORT_PKG.GET_SI_REC_ID('WP_ORG_IV_ID')
, 2
, PAY_JP_REPORT_PKG.GET_SI_REC_ID('WPF_ORG_IV_ID')
, 4
, 0)) SI_TYPE
, MIN(NVL(SUBSTRB(PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR( PAY_JP_REPORT_PKG.GET_SI_REC_ID('EXC_IV_ID')
, PAA.ASSIGNMENT_ID
, PPA.EFFECTIVE_DATE)
, 1
, 1)
, 'N')) OUTPUT_FLAG
, PPA.ELEMENT_SET_ID
, MIN(PEEV_APPL_MTH.SCREEN_ENTRY_VALUE) APPLIED_MONTH
FROM PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_ELEMENT_ENTRIES_F PEE_APPL
, PAY_ELEMENT_ENTRY_VALUES_F PEEV_APPL_MTH
, PAY_ELEMENT_ENTRY_VALUES_F PEEV_APPL_CAT
, (SELECT PAY_JP_REPORT_PKG.GET_SI_REC_ID('HI_ORG_IV_ID') ORG_IV_ID
FROM DUAL UNION SELECT PAY_JP_REPORT_PKG.GET_SI_REC_ID('WP_ORG_IV_ID') ORG_IV_ID
FROM DUAL UNION SELECT PAY_JP_REPORT_PKG.GET_SI_REC_ID('WPF_ORG_IV_ID') ORG_IV_ID
FROM DUAL) IV_ORG
, PAY_LINK_INPUT_VALUES_F PLIV_ORG
, PAY_ELEMENT_ENTRIES_F PEE_ORG
, PAY_ELEMENT_ENTRY_VALUES_F PEEV_ORG
, PAY_ELEMENT_ENTRY_VALUES_F PEEV_HI_NUM
, PAY_ELEMENT_ENTRY_VALUES_F PEEV_WP_NUM
WHERE PPA.ELEMENT_SET_ID IN ( PAY_JP_REPORT_PKG.GET_GS_REC_ID('SAN_ELE_SET_ID')
, PAY_JP_REPORT_PKG.GET_GS_REC_ID('GEP_ELE_SET_ID')
, PAY_JP_REPORT_PKG.GET_GS_REC_ID('IKU_ELE_SET_ID'))
AND PPA.ACTION_TYPE = 'R'
AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PAA.ACTION_STATUS = 'C'
AND PEE_APPL.UPDATING_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PEE_APPL.EFFECTIVE_START_DATE = PPA.EFFECTIVE_DATE
AND PEE_APPL.ENTRY_TYPE = 'E'
AND PEEV_APPL_MTH.ELEMENT_ENTRY_ID = PEE_APPL.ELEMENT_ENTRY_ID
AND PEEV_APPL_MTH.EFFECTIVE_START_DATE = PEE_APPL.EFFECTIVE_START_DATE
AND PEEV_APPL_MTH.EFFECTIVE_END_DATE = PEE_APPL.EFFECTIVE_END_DATE
AND PEEV_APPL_MTH.INPUT_VALUE_ID IN ( PAY_JP_REPORT_PKG.GET_GS_REC_ID('HI_APPL_MTH_IV_ID')
, PAY_JP_REPORT_PKG.GET_GS_REC_ID('WP_APPL_MTH_IV_ID'))
AND PEEV_APPL_MTH.SCREEN_ENTRY_VALUE = DECODE(PPA.ELEMENT_SET_ID
, PAY_JP_REPORT_PKG.GET_GS_REC_ID('SAN_ELE_SET_ID')
, DECODE(SIGN(TO_DATE('2003/04/01'
, 'YYYY/MM/DD') - PPA.DATE_EARNED)
, 1
, TO_CHAR(PPA.DATE_EARNED
, 'YYYY')||'10'
, TO_CHAR(PPA.DATE_EARNED
, 'YYYY')||'09')
, TO_CHAR(PPA.DATE_EARNED
, 'YYYYMM'))
AND PEEV_APPL_CAT.ELEMENT_ENTRY_ID = PEE_APPL.ELEMENT_ENTRY_ID
AND PEEV_APPL_CAT.EFFECTIVE_START_DATE = PEE_APPL.EFFECTIVE_START_DATE
AND PEEV_APPL_CAT.EFFECTIVE_END_DATE = PEE_APPL.EFFECTIVE_END_DATE
AND PEEV_APPL_CAT.INPUT_VALUE_ID IN ( PAY_JP_REPORT_PKG.GET_GS_REC_ID('HI_APPL_CAT_IV_ID')
, PAY_JP_REPORT_PKG.GET_GS_REC_ID('WP_APPL_CAT_IV_ID'))
AND PEEV_APPL_CAT.SCREEN_ENTRY_VALUE = DECODE(PPA.ELEMENT_SET_ID
, PAY_JP_REPORT_PKG.GET_GS_REC_ID('SAN_ELE_SET_ID')
, 'S'
, PAY_JP_REPORT_PKG.GET_GS_REC_ID('GEP_ELE_SET_ID')
, 'G'
, 'I')
AND PLIV_ORG.INPUT_VALUE_ID = IV_ORG.ORG_IV_ID
AND ((PEEV_APPL_CAT.INPUT_VALUE_ID = PAY_JP_REPORT_PKG.GET_GS_REC_ID('HI_APPL_CAT_IV_ID')
AND PLIV_ORG.INPUT_VALUE_ID = PAY_JP_REPORT_PKG.GET_SI_REC_ID('HI_ORG_IV_ID')) OR (PEEV_APPL_CAT.INPUT_VALUE_ID = PAY_JP_REPORT_PKG.GET_GS_REC_ID('WP_APPL_CAT_IV_ID')
AND PLIV_ORG.INPUT_VALUE_ID IN ( PAY_JP_REPORT_PKG.GET_SI_REC_ID('WP_ORG_IV_ID')
, PAY_JP_REPORT_PKG.GET_SI_REC_ID('WPF_ORG_IV_ID'))))
AND PPA.EFFECTIVE_DATE BETWEEN PLIV_ORG.EFFECTIVE_START_DATE
AND PLIV_ORG.EFFECTIVE_END_DATE
AND PEE_ORG.ELEMENT_LINK_ID = PLIV_ORG.ELEMENT_LINK_ID
AND PEE_ORG.ASSIGNMENT_ID = PEE_APPL.ASSIGNMENT_ID
AND PPA.EFFECTIVE_DATE BETWEEN PEE_ORG.EFFECTIVE_START_DATE
AND PEE_ORG.EFFECTIVE_END_DATE
AND PEEV_ORG.ELEMENT_ENTRY_ID = PEE_ORG.ELEMENT_ENTRY_ID
AND PEEV_ORG.INPUT_VALUE_ID = PLIV_ORG.INPUT_VALUE_ID
AND PEEV_ORG.EFFECTIVE_START_DATE = PEE_ORG.EFFECTIVE_START_DATE
AND PEEV_ORG.EFFECTIVE_END_DATE = PEE_ORG.EFFECTIVE_END_DATE
AND PEEV_HI_NUM.ELEMENT_ENTRY_ID = PEE_ORG.ELEMENT_ENTRY_ID
AND PEEV_HI_NUM.INPUT_VALUE_ID = PAY_JP_REPORT_PKG.GET_SI_REC_ID('HI_NUM_IV_ID')
AND PEEV_HI_NUM.EFFECTIVE_START_DATE = PEE_ORG.EFFECTIVE_START_DATE
AND PEEV_HI_NUM.EFFECTIVE_END_DATE = PEE_ORG.EFFECTIVE_END_DATE
AND PEEV_WP_NUM.ELEMENT_ENTRY_ID = PEE_ORG.ELEMENT_ENTRY_ID
AND PEEV_WP_NUM.INPUT_VALUE_ID = PAY_JP_REPORT_PKG.GET_SI_REC_ID('WP_NUM_IV_ID')
AND PEEV_WP_NUM.EFFECTIVE_START_DATE = PEE_ORG.EFFECTIVE_START_DATE
AND PEEV_WP_NUM.EFFECTIVE_END_DATE = PEE_ORG.EFFECTIVE_END_DATE GROUP BY PAA.ASSIGNMENT_ACTION_ID
, PPA.ELEMENT_SET_ID