VIEW: APPS.PAY_JP_SI_DISQUALIFIED_MT_V

Source

View Text - Preformatted

SELECT /*+ ORDERED NO_MERGE(V) INDEX(PEEV1 PAY_ELEMENT_ENTRY_VALUES_F_N1) INDEX(PEE PAY_ELEMENT_ENTRIES_F_PK) INDEX(PA PER_ASSIGNMENTS_F_PK) */ pa.assignment_id, pa.person_id, v1.business_group_id, pay_jp_balance_pkg.get_entry_value_date ( decode(peev1.input_value_id,v1.hi_disqualifdate_iv_id,v1.hi_qualifdate_iv_id,v1.wp_disqualifdate_iv_id,v1.wp_qualifdate_iv_id,v1.wpf_disqualifdate_iv_id,v1.wpf_qualifdate_iv_id), pee.assignment_id, peev1.effective_start_date ), fnd_date.canonical_to_date(peev1.screen_entry_value) - 1, pay_jp_balance_pkg.get_entry_value_number ( decode(peev1.input_value_id,v1.hi_disqualifdate_iv_id,v1.hi_org_iv_id,v1.wp_disqualifdate_iv_id,v1.wp_org_iv_id,v1.wpf_disqualifdate_iv_id,v1.wpf_org_iv_id), /* Not pa.assignment_id but pee.assignment_id for better performance. */ pee.assignment_id, fnd_date.canonical_to_date(peev1.screen_entry_value) - 1 ), substrb(pay_jp_balance_pkg.get_entry_value_char(v1.hi_number_iv_id,pee.assignment_id,fnd_date.canonical_to_date(peev1.screen_entry_value) - 1),1,10), substrb(pay_jp_balance_pkg.get_entry_value_char(v1.wp_number_iv_id,pee.assignment_id,fnd_date.canonical_to_date(peev1.screen_entry_value) - 1),1,10), sum(decode(peev1.input_value_id,v1.hi_disqualifdate_iv_id,1,v1.wp_disqualifdate_iv_id,2,v1.wpf_disqualifdate_iv_id,4)) from ( select pbg.business_group_id, hr_jp_id_pkg.input_value_id('COM_HI_QUALIFY_INFO', 'QUALIFY_DATE', pbg.business_group_id, NULL, 'FALSE') HI_QUALIFDATE_IV_ID, hr_jp_id_pkg.input_value_id('COM_WP_QUALIFY_INFO', 'QUALIFY_DATE', pbg.business_group_id, NULL, 'FALSE') WP_QUALIFDATE_IV_ID, hr_jp_id_pkg.input_value_id('COM_WPF_QUALIFY_INFO', 'QUALIFY_DATE', pbg.business_group_id, NULL, 'FALSE') WPF_QUALIFDATE_IV_ID, hr_jp_id_pkg.input_value_id('COM_HI_QUALIFY_INFO', 'DISQUALIFY_DATE', pbg.business_group_id, NULL, 'FALSE') HI_DISQUALIFDATE_IV_ID, hr_jp_id_pkg.input_value_id('COM_WP_QUALIFY_INFO', 'DISQUALIFY_DATE', pbg.business_group_id, NULL, 'FALSE') WP_DISQUALIFDATE_IV_ID, hr_jp_id_pkg.input_value_id('COM_WPF_QUALIFY_INFO', 'DISQUALIFY_DATE', pbg.business_group_id, NULL, 'FALSE') WPF_DISQUALIFDATE_IV_ID, hr_jp_id_pkg.input_value_id('COM_SI_INFO', 'HI_LOCATION', pbg.business_group_id, NULL, 'FALSE') HI_ORG_IV_ID, hr_jp_id_pkg.input_value_id('COM_SI_INFO', 'WP_LOCATION', pbg.business_group_id, NULL, 'FALSE') WP_ORG_IV_ID, hr_jp_id_pkg.input_value_id('COM_SI_INFO', 'WPF_LOCATION', pbg.business_group_id, NULL, 'FALSE') WPF_ORG_IV_ID, hr_jp_id_pkg.input_value_id('COM_SI_INFO', 'HI_CARD_NUM', pbg.business_group_id, NULL, 'FALSE') HI_NUMBER_IV_ID, hr_jp_id_pkg.input_value_id('COM_SI_INFO', 'WP_SERIAL_NUM', pbg.business_group_id, NULL, 'FALSE') WP_NUMBER_IV_ID from per_business_groups_perf pbg group by pbg.business_group_id ) v1, pay_element_entry_values_f peev1, pay_element_entries_f pee, per_all_assignments_f pa where peev1.input_value_id in (v1.hi_disqualifdate_iv_id, v1.wp_disqualifdate_iv_id, v1.wpf_disqualifdate_iv_id) and fnd_date.canonical_to_date(peev1.screen_entry_value) - 1 between peev1.effective_start_date and peev1.effective_end_date and pee.element_entry_id = peev1.element_entry_id and pee.effective_start_date = peev1.effective_start_date and pee.effective_end_date = peev1.effective_end_date and pee.entry_type = 'E' and pa.assignment_id = pee.assignment_id and pa.primary_flag = 'Y' and pa.business_group_id + 0 = v1.business_group_id and fnd_date.canonical_to_date(peev1.screen_entry_value) - 1 between pa.effective_start_date and pa.effective_end_date group by pa.assignment_id, pa.person_id, v1.business_group_id, pay_jp_balance_pkg.get_entry_value_date ( decode(peev1.input_value_id,v1.hi_disqualifdate_iv_id,v1.hi_qualifdate_iv_id,v1.wp_disqualifdate_iv_id,v1.wp_qualifdate_iv_id,v1.wpf_disqualifdate_iv_id,v1.wpf_qualifdate_iv_id), pee.assignment_id, peev1.effective_start_date ), fnd_date.canonical_to_date(peev1.screen_entry_value) - 1, pay_jp_balance_pkg.get_entry_value_number ( decode(peev1.input_value_id,v1.hi_disqualifdate_iv_id,v1.hi_org_iv_id,v1.wp_disqualifdate_iv_id,v1.wp_org_iv_id,v1.wpf_disqualifdate_iv_id,v1.wpf_org_iv_id), /* Not pa.assignment_id but pee.assignment_id for better performance. */ pee.assignment_id, fnd_date.canonical_to_date(peev1.screen_entry_value) - 1 ), substrb(pay_jp_balance_pkg.get_entry_value_char(v1.hi_number_iv_id,pee.assignment_id,fnd_date.canonical_to_date(peev1.screen_entry_value) - 1),1,10), substrb(pay_jp_balance_pkg.get_entry_value_char(v1.wp_number_iv_id,pee.assignment_id,fnd_date.canonical_to_date(peev1.screen_entry_value) - 1),1,10)

View Text - HTML Formatted

SELECT /*+ ORDERED NO_MERGE(V) INDEX(PEEV1 PAY_ELEMENT_ENTRY_VALUES_F_N1) INDEX(PEE PAY_ELEMENT_ENTRIES_F_PK) INDEX(PA PER_ASSIGNMENTS_F_PK) */ PA.ASSIGNMENT_ID
, PA.PERSON_ID
, V1.BUSINESS_GROUP_ID
, PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_DATE ( DECODE(PEEV1.INPUT_VALUE_ID
, V1.HI_DISQUALIFDATE_IV_ID
, V1.HI_QUALIFDATE_IV_ID
, V1.WP_DISQUALIFDATE_IV_ID
, V1.WP_QUALIFDATE_IV_ID
, V1.WPF_DISQUALIFDATE_IV_ID
, V1.WPF_QUALIFDATE_IV_ID)
, PEE.ASSIGNMENT_ID
, PEEV1.EFFECTIVE_START_DATE )
, FND_DATE.CANONICAL_TO_DATE(PEEV1.SCREEN_ENTRY_VALUE) - 1
, PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_NUMBER ( DECODE(PEEV1.INPUT_VALUE_ID
, V1.HI_DISQUALIFDATE_IV_ID
, V1.HI_ORG_IV_ID
, V1.WP_DISQUALIFDATE_IV_ID
, V1.WP_ORG_IV_ID
, V1.WPF_DISQUALIFDATE_IV_ID
, V1.WPF_ORG_IV_ID)
, /* NOT PA.ASSIGNMENT_ID BUT PEE.ASSIGNMENT_ID FOR BETTER PERFORMANCE. */ PEE.ASSIGNMENT_ID
, FND_DATE.CANONICAL_TO_DATE(PEEV1.SCREEN_ENTRY_VALUE) - 1 )
, SUBSTRB(PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR(V1.HI_NUMBER_IV_ID
, PEE.ASSIGNMENT_ID
, FND_DATE.CANONICAL_TO_DATE(PEEV1.SCREEN_ENTRY_VALUE) - 1)
, 1
, 10)
, SUBSTRB(PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR(V1.WP_NUMBER_IV_ID
, PEE.ASSIGNMENT_ID
, FND_DATE.CANONICAL_TO_DATE(PEEV1.SCREEN_ENTRY_VALUE) - 1)
, 1
, 10)
, SUM(DECODE(PEEV1.INPUT_VALUE_ID
, V1.HI_DISQUALIFDATE_IV_ID
, 1
, V1.WP_DISQUALIFDATE_IV_ID
, 2
, V1.WPF_DISQUALIFDATE_IV_ID
, 4))
FROM ( SELECT PBG.BUSINESS_GROUP_ID
, HR_JP_ID_PKG.INPUT_VALUE_ID('COM_HI_QUALIFY_INFO'
, 'QUALIFY_DATE'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') HI_QUALIFDATE_IV_ID
, HR_JP_ID_PKG.INPUT_VALUE_ID('COM_WP_QUALIFY_INFO'
, 'QUALIFY_DATE'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') WP_QUALIFDATE_IV_ID
, HR_JP_ID_PKG.INPUT_VALUE_ID('COM_WPF_QUALIFY_INFO'
, 'QUALIFY_DATE'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') WPF_QUALIFDATE_IV_ID
, HR_JP_ID_PKG.INPUT_VALUE_ID('COM_HI_QUALIFY_INFO'
, 'DISQUALIFY_DATE'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') HI_DISQUALIFDATE_IV_ID
, HR_JP_ID_PKG.INPUT_VALUE_ID('COM_WP_QUALIFY_INFO'
, 'DISQUALIFY_DATE'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') WP_DISQUALIFDATE_IV_ID
, HR_JP_ID_PKG.INPUT_VALUE_ID('COM_WPF_QUALIFY_INFO'
, 'DISQUALIFY_DATE'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') WPF_DISQUALIFDATE_IV_ID
, HR_JP_ID_PKG.INPUT_VALUE_ID('COM_SI_INFO'
, 'HI_LOCATION'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') HI_ORG_IV_ID
, HR_JP_ID_PKG.INPUT_VALUE_ID('COM_SI_INFO'
, 'WP_LOCATION'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') WP_ORG_IV_ID
, HR_JP_ID_PKG.INPUT_VALUE_ID('COM_SI_INFO'
, 'WPF_LOCATION'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') WPF_ORG_IV_ID
, HR_JP_ID_PKG.INPUT_VALUE_ID('COM_SI_INFO'
, 'HI_CARD_NUM'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') HI_NUMBER_IV_ID
, HR_JP_ID_PKG.INPUT_VALUE_ID('COM_SI_INFO'
, 'WP_SERIAL_NUM'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') WP_NUMBER_IV_ID
FROM PER_BUSINESS_GROUPS_PERF PBG GROUP BY PBG.BUSINESS_GROUP_ID ) V1
, PAY_ELEMENT_ENTRY_VALUES_F PEEV1
, PAY_ELEMENT_ENTRIES_F PEE
, PER_ALL_ASSIGNMENTS_F PA
WHERE PEEV1.INPUT_VALUE_ID IN (V1.HI_DISQUALIFDATE_IV_ID
, V1.WP_DISQUALIFDATE_IV_ID
, V1.WPF_DISQUALIFDATE_IV_ID)
AND FND_DATE.CANONICAL_TO_DATE(PEEV1.SCREEN_ENTRY_VALUE) - 1 BETWEEN PEEV1.EFFECTIVE_START_DATE
AND PEEV1.EFFECTIVE_END_DATE
AND PEE.ELEMENT_ENTRY_ID = PEEV1.ELEMENT_ENTRY_ID
AND PEE.EFFECTIVE_START_DATE = PEEV1.EFFECTIVE_START_DATE
AND PEE.EFFECTIVE_END_DATE = PEEV1.EFFECTIVE_END_DATE
AND PEE.ENTRY_TYPE = 'E'
AND PA.ASSIGNMENT_ID = PEE.ASSIGNMENT_ID
AND PA.PRIMARY_FLAG = 'Y'
AND PA.BUSINESS_GROUP_ID + 0 = V1.BUSINESS_GROUP_ID
AND FND_DATE.CANONICAL_TO_DATE(PEEV1.SCREEN_ENTRY_VALUE) - 1 BETWEEN PA.EFFECTIVE_START_DATE
AND PA.EFFECTIVE_END_DATE GROUP BY PA.ASSIGNMENT_ID
, PA.PERSON_ID
, V1.BUSINESS_GROUP_ID
, PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_DATE ( DECODE(PEEV1.INPUT_VALUE_ID
, V1.HI_DISQUALIFDATE_IV_ID
, V1.HI_QUALIFDATE_IV_ID
, V1.WP_DISQUALIFDATE_IV_ID
, V1.WP_QUALIFDATE_IV_ID
, V1.WPF_DISQUALIFDATE_IV_ID
, V1.WPF_QUALIFDATE_IV_ID)
, PEE.ASSIGNMENT_ID
, PEEV1.EFFECTIVE_START_DATE )
, FND_DATE.CANONICAL_TO_DATE(PEEV1.SCREEN_ENTRY_VALUE) - 1
, PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_NUMBER ( DECODE(PEEV1.INPUT_VALUE_ID
, V1.HI_DISQUALIFDATE_IV_ID
, V1.HI_ORG_IV_ID
, V1.WP_DISQUALIFDATE_IV_ID
, V1.WP_ORG_IV_ID
, V1.WPF_DISQUALIFDATE_IV_ID
, V1.WPF_ORG_IV_ID)
, /* NOT PA.ASSIGNMENT_ID BUT PEE.ASSIGNMENT_ID FOR BETTER PERFORMANCE. */ PEE.ASSIGNMENT_ID
, FND_DATE.CANONICAL_TO_DATE(PEEV1.SCREEN_ENTRY_VALUE) - 1 )
, SUBSTRB(PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR(V1.HI_NUMBER_IV_ID
, PEE.ASSIGNMENT_ID
, FND_DATE.CANONICAL_TO_DATE(PEEV1.SCREEN_ENTRY_VALUE) - 1)
, 1
, 10)
, SUBSTRB(PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR(V1.WP_NUMBER_IV_ID
, PEE.ASSIGNMENT_ID
, FND_DATE.CANONICAL_TO_DATE(PEEV1.SCREEN_ENTRY_VALUE) - 1)
, 1
, 10)