VIEW: APPS.PAY_JP_SI_CHANGE_OF_ADDRESS_V

Source

View Text - Preformatted

SELECT /*+ ORDERED NO_MERGE(V) USE_NL(pad2 pa pee pel peev1 peev2) */ pa.assignment_id, pa.person_id, v1.business_group_id, pad1.date_from EFFECTIVE_DATE, pad1.address_id NEW_ADDRESS_ID, pad2.address_id PREV_ADDRESS_ID, pay_jp_balance_pkg.get_entry_value_number( decode(peev1.input_value_id, v1.hi_qualifdate_iv_id, v1.hi_org_iv_id, v1.wp_qualifdate_iv_id, v1.wp_org_iv_id, v1.wpf_qualifdate_iv_id, v1.wpf_org_iv_id), /* Not pee.assignment_id but pa.assignment_id for better performance. */ pa.assignment_id, pad1.date_from) ORGANIZATION_ID, substrb(pay_jp_balance_pkg.get_entry_value_char( v1.hi_number_iv_id, pee.assignment_id, pad1.date_from), 1, 10) HI_NUMBER, substrb(pay_jp_balance_pkg.get_entry_value_char( v1.wp_number_iv_id, pee.assignment_id, pad1.date_from), 1, 10) WP_NUMBER, sum(decode(peev1.input_value_id, v1.hi_qualifdate_iv_id, 1, v1.wp_qualifdate_iv_id, 2, v1.wpf_qualifdate_iv_id, 4)) SI_TYPE from ( select pbg.business_group_id, hr_jp_id_pkg.element_type_id('COM_HI_QUALIFY_INFO',pbg.business_group_id,NULL,'FALSE') HI_ELEMENT_TYPE_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_HI_QUALIFY_INFO','DISQUALIFY_DATE',pbg.business_group_id,NULL,'FALSE') HI_DISQUALIFDATE_IV_ID, hr_jp_id_pkg.element_type_id('COM_WP_QUALIFY_INFO',pbg.business_group_id,NULL,'FALSE') WP_ELEMENT_TYPE_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_WP_QUALIFY_INFO','DISQUALIFY_DATE',pbg.business_group_id,NULL,'FALSE') WP_DISQUALIFDATE_IV_ID, hr_jp_id_pkg.element_type_id('COM_WPF_QUALIFY_INFO',pbg.business_group_id,NULL,'FALSE') WPF_ELEMENT_TYPE_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_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, per_addresses pad1, per_addresses pad2, per_all_assignments_f pa, pay_element_entries_f pee, pay_element_links_f pel, pay_element_entry_values_f peev1, pay_element_entry_values_f peev2 where pad1.business_group_id + 0 = v1.business_group_id and pad1.address_type = 'JP_C' and pad2.person_id = pad1.person_id and pad2.address_type = 'JP_C' and pad2.date_to = pad1.date_from - 1 and ( pad1.address_line1 <> pad2.address_line1 or (pad1.address_line1 is null and pad2.address_line1 is not null) or (pad2.address_line1 is null and pad1.address_line1 is not null) or pad1.address_line2 <> pad2.address_line2 or (pad1.address_line2 is null and pad2.address_line2 is not null) or (pad2.address_line2 is null and pad1.address_line2 is not null) or pad1.address_line3 <> pad2.address_line3 or (pad1.address_line3 is null and pad2.address_line3 is not null) or (pad2.address_line3 is null and pad1.address_line3 is not null)) and pa.person_id = pad1.person_id and pa.primary_flag = 'Y' and pad1.date_from between pa.effective_start_date and pa.effective_end_date and pee.assignment_id = pa.assignment_id and pee.entry_type = 'E' and pad1.date_from between pee.effective_start_date and pee.effective_end_date and pel.element_link_id = pee.element_link_id and pad1.date_from between pel.effective_start_date and pel.effective_end_date and pel.element_type_id in (v1.hi_element_type_id, v1.wp_element_type_id, v1.wpf_element_type_id) 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 in (v1.hi_qualifdate_iv_id, v1.wp_qualifdate_iv_id, v1.wpf_qualifdate_iv_id) 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 in (v1.hi_disqualifdate_iv_id, v1.wp_disqualifdate_iv_id, v1.wpf_disqualifdate_iv_id) and pad1.date_from between fnd_date.canonical_to_date(peev1.screen_entry_value) and fnd_date.canonical_to_date(nvl(peev2.screen_entry_value, '4712/12/31')) - 1 and pad2.date_to between fnd_date.canonical_to_date(peev1.screen_entry_value) and fnd_date.canonical_to_date(nvl(peev2.screen_entry_value, '4712/12/31')) - 1 group by pa.assignment_id, pa.person_id, v1.business_group_id, pad1.date_from, pad1.address_id, pad2.address_id, pay_jp_balance_pkg.get_entry_value_number( decode(peev1.input_value_id, v1.hi_qualifdate_iv_id, v1.hi_org_iv_id, v1.wp_qualifdate_iv_id, v1.wp_org_iv_id, v1.wpf_qualifdate_iv_id, v1.wpf_org_iv_id), /* Not pee.assignment_id but pa.assignment_id for better performance. */ pa.assignment_id, pad1.date_from), substrb(pay_jp_balance_pkg.get_entry_value_char( v1.hi_number_iv_id, pee.assignment_id, pad1.date_from), 1, 10), substrb(pay_jp_balance_pkg.get_entry_value_char( v1.wp_number_iv_id, pee.assignment_id, pad1.date_from), 1, 10)

View Text - HTML Formatted

SELECT /*+ ORDERED NO_MERGE(V) USE_NL(PAD2 PA PEE PEL PEEV1 PEEV2) */ PA.ASSIGNMENT_ID
, PA.PERSON_ID
, V1.BUSINESS_GROUP_ID
, PAD1.DATE_FROM EFFECTIVE_DATE
, PAD1.ADDRESS_ID NEW_ADDRESS_ID
, PAD2.ADDRESS_ID PREV_ADDRESS_ID
, PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_NUMBER( DECODE(PEEV1.INPUT_VALUE_ID
, V1.HI_QUALIFDATE_IV_ID
, V1.HI_ORG_IV_ID
, V1.WP_QUALIFDATE_IV_ID
, V1.WP_ORG_IV_ID
, V1.WPF_QUALIFDATE_IV_ID
, V1.WPF_ORG_IV_ID)
, /* NOT PEE.ASSIGNMENT_ID BUT PA.ASSIGNMENT_ID FOR BETTER PERFORMANCE. */ PA.ASSIGNMENT_ID
, PAD1.DATE_FROM) ORGANIZATION_ID
, SUBSTRB(PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR( V1.HI_NUMBER_IV_ID
, PEE.ASSIGNMENT_ID
, PAD1.DATE_FROM)
, 1
, 10) HI_NUMBER
, SUBSTRB(PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR( V1.WP_NUMBER_IV_ID
, PEE.ASSIGNMENT_ID
, PAD1.DATE_FROM)
, 1
, 10) WP_NUMBER
, SUM(DECODE(PEEV1.INPUT_VALUE_ID
, V1.HI_QUALIFDATE_IV_ID
, 1
, V1.WP_QUALIFDATE_IV_ID
, 2
, V1.WPF_QUALIFDATE_IV_ID
, 4)) SI_TYPE
FROM ( SELECT PBG.BUSINESS_GROUP_ID
, HR_JP_ID_PKG.ELEMENT_TYPE_ID('COM_HI_QUALIFY_INFO'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') HI_ELEMENT_TYPE_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_HI_QUALIFY_INFO'
, 'DISQUALIFY_DATE'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') HI_DISQUALIFDATE_IV_ID
, HR_JP_ID_PKG.ELEMENT_TYPE_ID('COM_WP_QUALIFY_INFO'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') WP_ELEMENT_TYPE_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_WP_QUALIFY_INFO'
, 'DISQUALIFY_DATE'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') WP_DISQUALIFDATE_IV_ID
, HR_JP_ID_PKG.ELEMENT_TYPE_ID('COM_WPF_QUALIFY_INFO'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') WPF_ELEMENT_TYPE_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_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
, PER_ADDRESSES PAD1
, PER_ADDRESSES PAD2
, PER_ALL_ASSIGNMENTS_F PA
, PAY_ELEMENT_ENTRIES_F PEE
, PAY_ELEMENT_LINKS_F PEL
, PAY_ELEMENT_ENTRY_VALUES_F PEEV1
, PAY_ELEMENT_ENTRY_VALUES_F PEEV2
WHERE PAD1.BUSINESS_GROUP_ID + 0 = V1.BUSINESS_GROUP_ID
AND PAD1.ADDRESS_TYPE = 'JP_C'
AND PAD2.PERSON_ID = PAD1.PERSON_ID
AND PAD2.ADDRESS_TYPE = 'JP_C'
AND PAD2.DATE_TO = PAD1.DATE_FROM - 1
AND ( PAD1.ADDRESS_LINE1 <> PAD2.ADDRESS_LINE1 OR (PAD1.ADDRESS_LINE1 IS NULL
AND PAD2.ADDRESS_LINE1 IS NOT NULL) OR (PAD2.ADDRESS_LINE1 IS NULL
AND PAD1.ADDRESS_LINE1 IS NOT NULL) OR PAD1.ADDRESS_LINE2 <> PAD2.ADDRESS_LINE2 OR (PAD1.ADDRESS_LINE2 IS NULL
AND PAD2.ADDRESS_LINE2 IS NOT NULL) OR (PAD2.ADDRESS_LINE2 IS NULL
AND PAD1.ADDRESS_LINE2 IS NOT NULL) OR PAD1.ADDRESS_LINE3 <> PAD2.ADDRESS_LINE3 OR (PAD1.ADDRESS_LINE3 IS NULL
AND PAD2.ADDRESS_LINE3 IS NOT NULL) OR (PAD2.ADDRESS_LINE3 IS NULL
AND PAD1.ADDRESS_LINE3 IS NOT NULL))
AND PA.PERSON_ID = PAD1.PERSON_ID
AND PA.PRIMARY_FLAG = 'Y'
AND PAD1.DATE_FROM BETWEEN PA.EFFECTIVE_START_DATE
AND PA.EFFECTIVE_END_DATE
AND PEE.ASSIGNMENT_ID = PA.ASSIGNMENT_ID
AND PEE.ENTRY_TYPE = 'E'
AND PAD1.DATE_FROM BETWEEN PEE.EFFECTIVE_START_DATE
AND PEE.EFFECTIVE_END_DATE
AND PEL.ELEMENT_LINK_ID = PEE.ELEMENT_LINK_ID
AND PAD1.DATE_FROM BETWEEN PEL.EFFECTIVE_START_DATE
AND PEL.EFFECTIVE_END_DATE
AND PEL.ELEMENT_TYPE_ID IN (V1.HI_ELEMENT_TYPE_ID
, V1.WP_ELEMENT_TYPE_ID
, V1.WPF_ELEMENT_TYPE_ID)
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 IN (V1.HI_QUALIFDATE_IV_ID
, V1.WP_QUALIFDATE_IV_ID
, V1.WPF_QUALIFDATE_IV_ID)
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 IN (V1.HI_DISQUALIFDATE_IV_ID
, V1.WP_DISQUALIFDATE_IV_ID
, V1.WPF_DISQUALIFDATE_IV_ID)
AND PAD1.DATE_FROM BETWEEN FND_DATE.CANONICAL_TO_DATE(PEEV1.SCREEN_ENTRY_VALUE)
AND FND_DATE.CANONICAL_TO_DATE(NVL(PEEV2.SCREEN_ENTRY_VALUE
, '4712/12/31')) - 1
AND PAD2.DATE_TO BETWEEN FND_DATE.CANONICAL_TO_DATE(PEEV1.SCREEN_ENTRY_VALUE)
AND FND_DATE.CANONICAL_TO_DATE(NVL(PEEV2.SCREEN_ENTRY_VALUE
, '4712/12/31')) - 1 GROUP BY PA.ASSIGNMENT_ID
, PA.PERSON_ID
, V1.BUSINESS_GROUP_ID
, PAD1.DATE_FROM
, PAD1.ADDRESS_ID
, PAD2.ADDRESS_ID
, PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_NUMBER( DECODE(PEEV1.INPUT_VALUE_ID
, V1.HI_QUALIFDATE_IV_ID
, V1.HI_ORG_IV_ID
, V1.WP_QUALIFDATE_IV_ID
, V1.WP_ORG_IV_ID
, V1.WPF_QUALIFDATE_IV_ID
, V1.WPF_ORG_IV_ID)
, /* NOT PEE.ASSIGNMENT_ID BUT PA.ASSIGNMENT_ID FOR BETTER PERFORMANCE. */ PA.ASSIGNMENT_ID
, PAD1.DATE_FROM)
, SUBSTRB(PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR( V1.HI_NUMBER_IV_ID
, PEE.ASSIGNMENT_ID
, PAD1.DATE_FROM)
, 1
, 10)
, SUBSTRB(PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR( V1.WP_NUMBER_IV_ID
, PEE.ASSIGNMENT_ID
, PAD1.DATE_FROM)
, 1
, 10)