VIEW: APPS.PAY_JP_UI_V

Source

View Text - Preformatted

SELECT /*+ ORDERED NO_MERGE(V) INDEX(PEEV PAY_ELEMENT_ENTRY_VALUES_F_N1) INDEX(PEE PAY_ELEMENT_ENTRIES_F_PK) INDEX(PEEV2 PAY_ELEMENT_ENTRY_VALUES_F_N50) INDEX(PA PER_ASSIGNMENTS_F_PK) */ pa.assignment_id, pa.person_id, v.business_group_id, fnd_date.canonical_to_date(peev.screen_entry_value), fnd_date.canonical_to_date(peev.screen_entry_value), substrb(peev2.screen_entry_value,1,1), substrb(NULL,1,1), substrb(NULL,1,1), pay_jp_balance_pkg.get_entry_value_number(v.input_value_id6, pa.assignment_id,fnd_date.canonical_to_date(peev.screen_entry_value)), to_number(NULL), substrb('1',1,1) from ( select pbg.business_group_id, hr_jp_id_pkg.input_value_id( 'COM_EI_QUALIFY_INFO', 'QUALIFY_DATE', pbg.business_group_id,NULL,'FALSE') INPUT_VALUE_ID1, hr_jp_id_pkg.input_value_id( 'COM_EI_QUALIFY_INFO', 'QUALIFY_CHANGE_TYPE', pbg.business_group_id,NULL,'FALSE') INPUT_VALUE_ID2, hr_jp_id_pkg.input_value_id( 'COM_LI_INFO', 'EI_LOCATION', pbg.business_group_id,NULL,'FALSE') INPUT_VALUE_ID6 from per_business_groups_perf pbg group by pbg.business_group_id ) v, pay_element_entry_values_f peev, pay_element_entries_f pee, per_all_assignments_f pa, pay_element_entry_values_f peev2 where peev.input_value_id = v.input_value_id1 and fnd_date.canonical_to_date(peev.screen_entry_value) between peev.effective_start_date and peev.effective_end_date and pee.element_entry_id = peev.element_entry_id and pee.effective_start_date = peev.effective_start_date and pee.effective_end_date = peev.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 = v.business_group_id and fnd_date.canonical_to_date(peev.screen_entry_value) between pa.effective_start_date and pa.effective_end_date and peev2.element_entry_id = peev.element_entry_id and peev2.effective_start_date = peev.effective_start_date and peev2.effective_end_date = peev.effective_end_date and peev2.input_value_id + 0 = v.input_value_id2 and peev2.screen_entry_value in ('1','2') UNION ALL /* Disqualified */ select /*+ ORDERED NO_MERGE(V) INDEX(PEEV PAY_ELEMENT_ENTRY_VALUES_F_N1) INDEX(PEE PAY_ELEMENT_ENTRIES_F_PK) INDEX(PEEV2 PAY_ELEMENT_ENTRY_VALUES_F_N50) INDEX(PEEV3 PAY_ELEMENT_ENTRY_VALUES_F_N50) INDEX(PA PER_ASSIGNMENTS_F_PK) */ pa.assignment_id, pa.person_id, v.business_group_id, fnd_date.canonical_to_date(peev.screen_entry_value), fnd_date.canonical_to_date(peev.screen_entry_value), substrb(NULL,1,1), substrb(peev2.screen_entry_value,1,1), substrb(decode(peev3.screen_entry_value,'Y','1','2'),1,1), pay_jp_balance_pkg.get_entry_value_number(v.input_value_id6, pa.assignment_id,fnd_date.canonical_to_date(peev.screen_entry_value)), to_number(NULL), substrb('2',1,1) from ( select pbg.business_group_id, hr_jp_id_pkg.input_value_id( 'COM_EI_QUALIFY_INFO', 'DISQUALIFY_DATE', pbg.business_group_id,NULL,'FALSE') INPUT_VALUE_ID3, hr_jp_id_pkg.input_value_id( 'COM_EI_QUALIFY_INFO', 'DISQUALIFY_CAUSE', pbg.business_group_id,NULL,'FALSE') INPUT_VALUE_ID4, hr_jp_id_pkg.input_value_id( 'COM_EI_QUALIFY_INFO', 'TRM_REPORT_OUTPUT_FLAG', pbg.business_group_id,NULL,'FALSE') INPUT_VALUE_ID5, hr_jp_id_pkg.input_value_id( 'COM_LI_INFO', 'EI_LOCATION', pbg.business_group_id,NULL,'FALSE') INPUT_VALUE_ID6 from per_business_groups_perf pbg group by pbg.business_group_id ) v, pay_element_entry_values_f peev, pay_element_entries_f pee, per_all_assignments_f pa, pay_element_entry_values_f peev2, pay_element_entry_values_f peev3 where peev.input_value_id = v.input_value_id3 and fnd_date.canonical_to_date(peev.screen_entry_value) between peev.effective_start_date and peev.effective_end_date and pee.element_entry_id = peev.element_entry_id and pee.effective_start_date = peev.effective_start_date and pee.effective_end_date = peev.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 = v.business_group_id and fnd_date.canonical_to_date(peev.screen_entry_value) between pa.effective_start_date and pa.effective_end_date and peev2.element_entry_id = peev.element_entry_id and peev2.effective_start_date = peev.effective_start_date and peev2.effective_end_date = peev.effective_end_date and peev2.input_value_id + 0 = v.input_value_id4 and peev3.element_entry_id = peev.element_entry_id and peev3.effective_start_date = peev.effective_start_date and peev3.effective_end_date = peev.effective_end_date and peev3.input_value_id + 0 = v.input_value_id5 UNION ALL /* Qualified Category Changed */ select /*+ ORDERED NO_MERGE(V) INDEX(PEEV PAY_ELEMENT_ENTRY_VALUES_F_N1) INDEX(PEE PAY_ELEMENT_ENTRIES_F_PK) INDEX(PEEV2 PAY_ELEMENT_ENTRY_VALUES_F_N50) INDEX(PEEV3 PAY_ELEMENT_ENTRY_VALUES_F_N50) INDEX(PA PER_ASSIGNMENTS_F_PK) */ pa.assignment_id, pa.person_id, v.business_group_id, fnd_date.canonical_to_date(peev.screen_entry_value), fnd_date.canonical_to_date(peev.screen_entry_value), '2', substrb('2',1,1), substrb('2',1,1), pay_jp_balance_pkg.get_entry_value_number(v.input_value_id6, pa.assignment_id,fnd_date.canonical_to_date(peev.screen_entry_value)), to_number(NULL), substrb('6',1,1) from ( select pbg.business_group_id, hr_jp_id_pkg.input_value_id( 'COM_EI_QUALIFY_INFO', 'QUALIFY_DATE', pbg.business_group_id,NULL,'FALSE') INPUT_VALUE_ID1, hr_jp_id_pkg.input_value_id( 'COM_EI_QUALIFY_INFO', 'QUALIFY_CHANGE_TYPE', pbg.business_group_id,NULL,'FALSE') INPUT_VALUE_ID2, hr_jp_id_pkg.input_value_id( 'COM_LI_INFO', 'EI_LOCATION', pbg.business_group_id,NULL,'FALSE') INPUT_VALUE_ID6 from per_business_groups_perf pbg group by pbg.business_group_id ) v, pay_element_entry_values_f peev, pay_element_entries_f pee, per_all_assignments_f pa, pay_element_entry_values_f peev2 where peev.input_value_id = v.input_value_id1 and fnd_date.canonical_to_date(peev.screen_entry_value) between peev.effective_start_date and peev.effective_end_date and pee.element_entry_id = peev.element_entry_id and pee.effective_start_date = peev.effective_start_date and pee.effective_end_date = peev.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 = v.business_group_id and fnd_date.canonical_to_date(peev.screen_entry_value) between pa.effective_start_date and pa.effective_end_date and peev2.element_entry_id = peev.element_entry_id and peev2.effective_start_date = peev.effective_start_date and peev2.effective_end_date = peev.effective_end_date and peev2.input_value_id + 0 = v.input_value_id2 and peev2.screen_entry_value = '3' UNION ALL /* Transfer */ select /*+ ORDERED NO_MERGE(V) INDEX(PEEV PAY_ELEMENT_ENTRY_VALUES_F_N1) INDEX(PEE PAY_ELEMENT_ENTRIES_F_PK) INDEX(PEE2 PAY_ELEMENT_ENTRIES_F_PK) INDEX(PEEV2 PAY_ELEMENT_ENTRY_VALUES_F_N50) INDEX(PA PER_ASSIGNMENTS_F_PK) */ pa.assignment_id, pa.person_id, v.business_group_id, pee2.effective_end_date, pee.effective_start_date, substrb(NULL,1,1), substrb(NULL,1,1), substrb(NULL,1,1), to_number(peev.screen_entry_value), to_number(peev2.screen_entry_value), substrb('3',1,1) from ( select pbg.business_group_id, hr_jp_id_pkg.input_value_id( 'COM_EI_QUALIFY_INFO', 'QUALIFY_DATE', pbg.business_group_id,NULL,'FALSE') INPUT_VALUE_ID1, hr_jp_id_pkg.input_value_id( 'COM_EI_QUALIFY_INFO', 'QUALIFY_CHANGE_TYPE', pbg.business_group_id,NULL,'FALSE') INPUT_VALUE_ID2, hr_jp_id_pkg.input_value_id( 'COM_EI_QUALIFY_INFO', 'DISQUALIFY_DATE', pbg.business_group_id,NULL,'FALSE') INPUT_VALUE_ID3, hr_jp_id_pkg.input_value_id( 'COM_LI_INFO', 'EI_LOCATION', pbg.business_group_id,NULL,'FALSE') INPUT_VALUE_ID6 from per_business_groups_perf pbg group by pbg.business_group_id ) v, pay_element_entry_values_f peev, pay_element_entries_f pee, pay_element_entries_f pee2, pay_element_entry_values_f peev2, per_all_assignments_f pa where peev.input_value_id = v.input_value_id6 and pee.element_entry_id = peev.element_entry_id and pee.effective_start_date = peev.effective_start_date and pee.effective_end_date = peev.effective_end_date and pee.entry_type = 'E' and pee2.element_entry_id = pee.element_entry_id and pee2.effective_end_date = pee.effective_start_date - 1 and pee2.entry_type = 'E' and peev2.element_entry_id = pee2.element_entry_id and peev2.effective_start_date = pee2.effective_start_date and peev2.effective_end_date = pee2.effective_end_date and peev2.input_value_id + 0 = peev.input_value_id and nvl(peev2.screen_entry_value,peev.screen_entry_value) <> peev.screen_entry_value and pa.assignment_id = pee.assignment_id and pa.primary_flag = 'Y' and pa.business_group_id + 0 = v.business_group_id and peev.effective_start_date between pa.effective_start_date and pa.effective_end_date and peev.effective_start_date between pay_jp_balance_pkg.get_entry_value_date(v.input_value_id1,pa.assignment_id,peev.effective_start_date) + decode(pay_jp_balance_pkg.get_entry_value_char(v.input_value_id2,pa.assignment_id,peev.effective_start_date),'1',1,'2',1,'3',0) and nvl(pay_jp_balance_pkg.get_entry_value_date(v.input_value_id3,pa.assignment_id,peev.effective_start_date),peev.effective_start_date) UNION ALL /* Person Name Changed(Sub-query is done for optimizer to do unique scan on purpose) */ select /*+ ORDERED NO_MERGE(V) INDEX(PP2 PER_PEOPLE_F_PK) INDEX(PA PER_ASSIGNMENTS_F_N12) */ pa.assignment_id, pp.person_id, v.business_group_id, pp.effective_start_date, pp.effective_start_date, substrb(NULL,1,1), substrb(NULL,1,1), substrb(NULL,1,1), pay_jp_balance_pkg.get_entry_value_number(v.input_value_id6,pa.assignment_id,pp.effective_start_date), to_number(NULL), substrb('5',1,1) from ( select pbg.business_group_id, hr_jp_id_pkg.input_value_id( 'COM_EI_QUALIFY_INFO', 'QUALIFY_DATE', pbg.business_group_id,NULL,'FALSE') INPUT_VALUE_ID1, hr_jp_id_pkg.input_value_id( 'COM_EI_QUALIFY_INFO', 'QUALIFY_CHANGE_TYPE', pbg.business_group_id,NULL,'FALSE') INPUT_VALUE_ID2, hr_jp_id_pkg.input_value_id( 'COM_EI_QUALIFY_INFO', 'DISQUALIFY_DATE', pbg.business_group_id,NULL,'FALSE') INPUT_VALUE_ID3, hr_jp_id_pkg.input_value_id( 'COM_LI_INFO', 'EI_LOCATION', pbg.business_group_id,NULL,'FALSE') INPUT_VALUE_ID6 from per_business_groups_perf pbg group by pbg.business_group_id ) v, per_all_people_f pp, per_all_people_f pp2, per_all_assignments_f pa where pp.business_group_id + 0 = v.business_group_id and pp.effective_start_date > pp.start_date and pp2.person_id = pp.person_id and pp2.effective_end_date = pp.effective_start_date - 1 and pp2.full_name <> pp.full_name and pa.person_id = pp.person_id and pa.primary_flag = 'Y' and pp.effective_start_date between pa.effective_start_date and pa.effective_end_date and pp.effective_start_date between pay_jp_balance_pkg.get_entry_value_date(v.input_value_id1,pa.assignment_id,pp.effective_start_date) + decode(pay_jp_balance_pkg.get_entry_value_char(v.input_value_id2,pa.assignment_id,pp.effective_start_date),'1',1,'2',1,'3',0) and nvl(pay_jp_balance_pkg.get_entry_value_date(v.input_value_id3,pa.assignment_id,pp.effective_start_date),pp.effective_start_date)

View Text - HTML Formatted

SELECT /*+ ORDERED NO_MERGE(V) INDEX(PEEV PAY_ELEMENT_ENTRY_VALUES_F_N1) INDEX(PEE PAY_ELEMENT_ENTRIES_F_PK) INDEX(PEEV2 PAY_ELEMENT_ENTRY_VALUES_F_N50) INDEX(PA PER_ASSIGNMENTS_F_PK) */ PA.ASSIGNMENT_ID
, PA.PERSON_ID
, V.BUSINESS_GROUP_ID
, FND_DATE.CANONICAL_TO_DATE(PEEV.SCREEN_ENTRY_VALUE)
, FND_DATE.CANONICAL_TO_DATE(PEEV.SCREEN_ENTRY_VALUE)
, SUBSTRB(PEEV2.SCREEN_ENTRY_VALUE
, 1
, 1)
, SUBSTRB(NULL
, 1
, 1)
, SUBSTRB(NULL
, 1
, 1)
, PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_NUMBER(V.INPUT_VALUE_ID6
, PA.ASSIGNMENT_ID
, FND_DATE.CANONICAL_TO_DATE(PEEV.SCREEN_ENTRY_VALUE))
, TO_NUMBER(NULL)
, SUBSTRB('1'
, 1
, 1)
FROM ( SELECT PBG.BUSINESS_GROUP_ID
, HR_JP_ID_PKG.INPUT_VALUE_ID( 'COM_EI_QUALIFY_INFO'
, 'QUALIFY_DATE'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') INPUT_VALUE_ID1
, HR_JP_ID_PKG.INPUT_VALUE_ID( 'COM_EI_QUALIFY_INFO'
, 'QUALIFY_CHANGE_TYPE'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') INPUT_VALUE_ID2
, HR_JP_ID_PKG.INPUT_VALUE_ID( 'COM_LI_INFO'
, 'EI_LOCATION'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') INPUT_VALUE_ID6
FROM PER_BUSINESS_GROUPS_PERF PBG GROUP BY PBG.BUSINESS_GROUP_ID ) V
, PAY_ELEMENT_ENTRY_VALUES_F PEEV
, PAY_ELEMENT_ENTRIES_F PEE
, PER_ALL_ASSIGNMENTS_F PA
, PAY_ELEMENT_ENTRY_VALUES_F PEEV2
WHERE PEEV.INPUT_VALUE_ID = V.INPUT_VALUE_ID1
AND FND_DATE.CANONICAL_TO_DATE(PEEV.SCREEN_ENTRY_VALUE) BETWEEN PEEV.EFFECTIVE_START_DATE
AND PEEV.EFFECTIVE_END_DATE
AND PEE.ELEMENT_ENTRY_ID = PEEV.ELEMENT_ENTRY_ID
AND PEE.EFFECTIVE_START_DATE = PEEV.EFFECTIVE_START_DATE
AND PEE.EFFECTIVE_END_DATE = PEEV.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 = V.BUSINESS_GROUP_ID
AND FND_DATE.CANONICAL_TO_DATE(PEEV.SCREEN_ENTRY_VALUE) BETWEEN PA.EFFECTIVE_START_DATE
AND PA.EFFECTIVE_END_DATE
AND PEEV2.ELEMENT_ENTRY_ID = PEEV.ELEMENT_ENTRY_ID
AND PEEV2.EFFECTIVE_START_DATE = PEEV.EFFECTIVE_START_DATE
AND PEEV2.EFFECTIVE_END_DATE = PEEV.EFFECTIVE_END_DATE
AND PEEV2.INPUT_VALUE_ID + 0 = V.INPUT_VALUE_ID2
AND PEEV2.SCREEN_ENTRY_VALUE IN ('1'
, '2') UNION ALL /* DISQUALIFIED */ SELECT /*+ ORDERED NO_MERGE(V) INDEX(PEEV PAY_ELEMENT_ENTRY_VALUES_F_N1) INDEX(PEE PAY_ELEMENT_ENTRIES_F_PK) INDEX(PEEV2 PAY_ELEMENT_ENTRY_VALUES_F_N50) INDEX(PEEV3 PAY_ELEMENT_ENTRY_VALUES_F_N50) INDEX(PA PER_ASSIGNMENTS_F_PK) */ PA.ASSIGNMENT_ID
, PA.PERSON_ID
, V.BUSINESS_GROUP_ID
, FND_DATE.CANONICAL_TO_DATE(PEEV.SCREEN_ENTRY_VALUE)
, FND_DATE.CANONICAL_TO_DATE(PEEV.SCREEN_ENTRY_VALUE)
, SUBSTRB(NULL
, 1
, 1)
, SUBSTRB(PEEV2.SCREEN_ENTRY_VALUE
, 1
, 1)
, SUBSTRB(DECODE(PEEV3.SCREEN_ENTRY_VALUE
, 'Y'
, '1'
, '2')
, 1
, 1)
, PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_NUMBER(V.INPUT_VALUE_ID6
, PA.ASSIGNMENT_ID
, FND_DATE.CANONICAL_TO_DATE(PEEV.SCREEN_ENTRY_VALUE))
, TO_NUMBER(NULL)
, SUBSTRB('2'
, 1
, 1)
FROM ( SELECT PBG.BUSINESS_GROUP_ID
, HR_JP_ID_PKG.INPUT_VALUE_ID( 'COM_EI_QUALIFY_INFO'
, 'DISQUALIFY_DATE'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') INPUT_VALUE_ID3
, HR_JP_ID_PKG.INPUT_VALUE_ID( 'COM_EI_QUALIFY_INFO'
, 'DISQUALIFY_CAUSE'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') INPUT_VALUE_ID4
, HR_JP_ID_PKG.INPUT_VALUE_ID( 'COM_EI_QUALIFY_INFO'
, 'TRM_REPORT_OUTPUT_FLAG'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') INPUT_VALUE_ID5
, HR_JP_ID_PKG.INPUT_VALUE_ID( 'COM_LI_INFO'
, 'EI_LOCATION'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') INPUT_VALUE_ID6
FROM PER_BUSINESS_GROUPS_PERF PBG GROUP BY PBG.BUSINESS_GROUP_ID ) V
, PAY_ELEMENT_ENTRY_VALUES_F PEEV
, PAY_ELEMENT_ENTRIES_F PEE
, PER_ALL_ASSIGNMENTS_F PA
, PAY_ELEMENT_ENTRY_VALUES_F PEEV2
, PAY_ELEMENT_ENTRY_VALUES_F PEEV3
WHERE PEEV.INPUT_VALUE_ID = V.INPUT_VALUE_ID3
AND FND_DATE.CANONICAL_TO_DATE(PEEV.SCREEN_ENTRY_VALUE) BETWEEN PEEV.EFFECTIVE_START_DATE
AND PEEV.EFFECTIVE_END_DATE
AND PEE.ELEMENT_ENTRY_ID = PEEV.ELEMENT_ENTRY_ID
AND PEE.EFFECTIVE_START_DATE = PEEV.EFFECTIVE_START_DATE
AND PEE.EFFECTIVE_END_DATE = PEEV.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 = V.BUSINESS_GROUP_ID
AND FND_DATE.CANONICAL_TO_DATE(PEEV.SCREEN_ENTRY_VALUE) BETWEEN PA.EFFECTIVE_START_DATE
AND PA.EFFECTIVE_END_DATE
AND PEEV2.ELEMENT_ENTRY_ID = PEEV.ELEMENT_ENTRY_ID
AND PEEV2.EFFECTIVE_START_DATE = PEEV.EFFECTIVE_START_DATE
AND PEEV2.EFFECTIVE_END_DATE = PEEV.EFFECTIVE_END_DATE
AND PEEV2.INPUT_VALUE_ID + 0 = V.INPUT_VALUE_ID4
AND PEEV3.ELEMENT_ENTRY_ID = PEEV.ELEMENT_ENTRY_ID
AND PEEV3.EFFECTIVE_START_DATE = PEEV.EFFECTIVE_START_DATE
AND PEEV3.EFFECTIVE_END_DATE = PEEV.EFFECTIVE_END_DATE
AND PEEV3.INPUT_VALUE_ID + 0 = V.INPUT_VALUE_ID5 UNION ALL /* QUALIFIED CATEGORY CHANGED */ SELECT /*+ ORDERED NO_MERGE(V) INDEX(PEEV PAY_ELEMENT_ENTRY_VALUES_F_N1) INDEX(PEE PAY_ELEMENT_ENTRIES_F_PK) INDEX(PEEV2 PAY_ELEMENT_ENTRY_VALUES_F_N50) INDEX(PEEV3 PAY_ELEMENT_ENTRY_VALUES_F_N50) INDEX(PA PER_ASSIGNMENTS_F_PK) */ PA.ASSIGNMENT_ID
, PA.PERSON_ID
, V.BUSINESS_GROUP_ID
, FND_DATE.CANONICAL_TO_DATE(PEEV.SCREEN_ENTRY_VALUE)
, FND_DATE.CANONICAL_TO_DATE(PEEV.SCREEN_ENTRY_VALUE)
, '2'
, SUBSTRB('2'
, 1
, 1)
, SUBSTRB('2'
, 1
, 1)
, PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_NUMBER(V.INPUT_VALUE_ID6
, PA.ASSIGNMENT_ID
, FND_DATE.CANONICAL_TO_DATE(PEEV.SCREEN_ENTRY_VALUE))
, TO_NUMBER(NULL)
, SUBSTRB('6'
, 1
, 1)
FROM ( SELECT PBG.BUSINESS_GROUP_ID
, HR_JP_ID_PKG.INPUT_VALUE_ID( 'COM_EI_QUALIFY_INFO'
, 'QUALIFY_DATE'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') INPUT_VALUE_ID1
, HR_JP_ID_PKG.INPUT_VALUE_ID( 'COM_EI_QUALIFY_INFO'
, 'QUALIFY_CHANGE_TYPE'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') INPUT_VALUE_ID2
, HR_JP_ID_PKG.INPUT_VALUE_ID( 'COM_LI_INFO'
, 'EI_LOCATION'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') INPUT_VALUE_ID6
FROM PER_BUSINESS_GROUPS_PERF PBG GROUP BY PBG.BUSINESS_GROUP_ID ) V
, PAY_ELEMENT_ENTRY_VALUES_F PEEV
, PAY_ELEMENT_ENTRIES_F PEE
, PER_ALL_ASSIGNMENTS_F PA
, PAY_ELEMENT_ENTRY_VALUES_F PEEV2
WHERE PEEV.INPUT_VALUE_ID = V.INPUT_VALUE_ID1
AND FND_DATE.CANONICAL_TO_DATE(PEEV.SCREEN_ENTRY_VALUE) BETWEEN PEEV.EFFECTIVE_START_DATE
AND PEEV.EFFECTIVE_END_DATE
AND PEE.ELEMENT_ENTRY_ID = PEEV.ELEMENT_ENTRY_ID
AND PEE.EFFECTIVE_START_DATE = PEEV.EFFECTIVE_START_DATE
AND PEE.EFFECTIVE_END_DATE = PEEV.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 = V.BUSINESS_GROUP_ID
AND FND_DATE.CANONICAL_TO_DATE(PEEV.SCREEN_ENTRY_VALUE) BETWEEN PA.EFFECTIVE_START_DATE
AND PA.EFFECTIVE_END_DATE
AND PEEV2.ELEMENT_ENTRY_ID = PEEV.ELEMENT_ENTRY_ID
AND PEEV2.EFFECTIVE_START_DATE = PEEV.EFFECTIVE_START_DATE
AND PEEV2.EFFECTIVE_END_DATE = PEEV.EFFECTIVE_END_DATE
AND PEEV2.INPUT_VALUE_ID + 0 = V.INPUT_VALUE_ID2
AND PEEV2.SCREEN_ENTRY_VALUE = '3' UNION ALL /* TRANSFER */ SELECT /*+ ORDERED NO_MERGE(V) INDEX(PEEV PAY_ELEMENT_ENTRY_VALUES_F_N1) INDEX(PEE PAY_ELEMENT_ENTRIES_F_PK) INDEX(PEE2 PAY_ELEMENT_ENTRIES_F_PK) INDEX(PEEV2 PAY_ELEMENT_ENTRY_VALUES_F_N50) INDEX(PA PER_ASSIGNMENTS_F_PK) */ PA.ASSIGNMENT_ID
, PA.PERSON_ID
, V.BUSINESS_GROUP_ID
, PEE2.EFFECTIVE_END_DATE
, PEE.EFFECTIVE_START_DATE
, SUBSTRB(NULL
, 1
, 1)
, SUBSTRB(NULL
, 1
, 1)
, SUBSTRB(NULL
, 1
, 1)
, TO_NUMBER(PEEV.SCREEN_ENTRY_VALUE)
, TO_NUMBER(PEEV2.SCREEN_ENTRY_VALUE)
, SUBSTRB('3'
, 1
, 1)
FROM ( SELECT PBG.BUSINESS_GROUP_ID
, HR_JP_ID_PKG.INPUT_VALUE_ID( 'COM_EI_QUALIFY_INFO'
, 'QUALIFY_DATE'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') INPUT_VALUE_ID1
, HR_JP_ID_PKG.INPUT_VALUE_ID( 'COM_EI_QUALIFY_INFO'
, 'QUALIFY_CHANGE_TYPE'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') INPUT_VALUE_ID2
, HR_JP_ID_PKG.INPUT_VALUE_ID( 'COM_EI_QUALIFY_INFO'
, 'DISQUALIFY_DATE'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') INPUT_VALUE_ID3
, HR_JP_ID_PKG.INPUT_VALUE_ID( 'COM_LI_INFO'
, 'EI_LOCATION'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') INPUT_VALUE_ID6
FROM PER_BUSINESS_GROUPS_PERF PBG GROUP BY PBG.BUSINESS_GROUP_ID ) V
, PAY_ELEMENT_ENTRY_VALUES_F PEEV
, PAY_ELEMENT_ENTRIES_F PEE
, PAY_ELEMENT_ENTRIES_F PEE2
, PAY_ELEMENT_ENTRY_VALUES_F PEEV2
, PER_ALL_ASSIGNMENTS_F PA
WHERE PEEV.INPUT_VALUE_ID = V.INPUT_VALUE_ID6
AND PEE.ELEMENT_ENTRY_ID = PEEV.ELEMENT_ENTRY_ID
AND PEE.EFFECTIVE_START_DATE = PEEV.EFFECTIVE_START_DATE
AND PEE.EFFECTIVE_END_DATE = PEEV.EFFECTIVE_END_DATE
AND PEE.ENTRY_TYPE = 'E'
AND PEE2.ELEMENT_ENTRY_ID = PEE.ELEMENT_ENTRY_ID
AND PEE2.EFFECTIVE_END_DATE = PEE.EFFECTIVE_START_DATE - 1
AND PEE2.ENTRY_TYPE = 'E'
AND PEEV2.ELEMENT_ENTRY_ID = PEE2.ELEMENT_ENTRY_ID
AND PEEV2.EFFECTIVE_START_DATE = PEE2.EFFECTIVE_START_DATE
AND PEEV2.EFFECTIVE_END_DATE = PEE2.EFFECTIVE_END_DATE
AND PEEV2.INPUT_VALUE_ID + 0 = PEEV.INPUT_VALUE_ID
AND NVL(PEEV2.SCREEN_ENTRY_VALUE
, PEEV.SCREEN_ENTRY_VALUE) <> PEEV.SCREEN_ENTRY_VALUE
AND PA.ASSIGNMENT_ID = PEE.ASSIGNMENT_ID
AND PA.PRIMARY_FLAG = 'Y'
AND PA.BUSINESS_GROUP_ID + 0 = V.BUSINESS_GROUP_ID
AND PEEV.EFFECTIVE_START_DATE BETWEEN PA.EFFECTIVE_START_DATE
AND PA.EFFECTIVE_END_DATE
AND PEEV.EFFECTIVE_START_DATE BETWEEN PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_DATE(V.INPUT_VALUE_ID1
, PA.ASSIGNMENT_ID
, PEEV.EFFECTIVE_START_DATE) + DECODE(PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR(V.INPUT_VALUE_ID2
, PA.ASSIGNMENT_ID
, PEEV.EFFECTIVE_START_DATE)
, '1'
, 1
, '2'
, 1
, '3'
, 0)
AND NVL(PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_DATE(V.INPUT_VALUE_ID3
, PA.ASSIGNMENT_ID
, PEEV.EFFECTIVE_START_DATE)
, PEEV.EFFECTIVE_START_DATE) UNION ALL /* PERSON NAME CHANGED(SUB-QUERY IS DONE FOR OPTIMIZER TO DO UNIQUE SCAN ON PURPOSE) */ SELECT /*+ ORDERED NO_MERGE(V) INDEX(PP2 PER_PEOPLE_F_PK) INDEX(PA PER_ASSIGNMENTS_F_N12) */ PA.ASSIGNMENT_ID
, PP.PERSON_ID
, V.BUSINESS_GROUP_ID
, PP.EFFECTIVE_START_DATE
, PP.EFFECTIVE_START_DATE
, SUBSTRB(NULL
, 1
, 1)
, SUBSTRB(NULL
, 1
, 1)
, SUBSTRB(NULL
, 1
, 1)
, PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_NUMBER(V.INPUT_VALUE_ID6
, PA.ASSIGNMENT_ID
, PP.EFFECTIVE_START_DATE)
, TO_NUMBER(NULL)
, SUBSTRB('5'
, 1
, 1)
FROM ( SELECT PBG.BUSINESS_GROUP_ID
, HR_JP_ID_PKG.INPUT_VALUE_ID( 'COM_EI_QUALIFY_INFO'
, 'QUALIFY_DATE'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') INPUT_VALUE_ID1
, HR_JP_ID_PKG.INPUT_VALUE_ID( 'COM_EI_QUALIFY_INFO'
, 'QUALIFY_CHANGE_TYPE'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') INPUT_VALUE_ID2
, HR_JP_ID_PKG.INPUT_VALUE_ID( 'COM_EI_QUALIFY_INFO'
, 'DISQUALIFY_DATE'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') INPUT_VALUE_ID3
, HR_JP_ID_PKG.INPUT_VALUE_ID( 'COM_LI_INFO'
, 'EI_LOCATION'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') INPUT_VALUE_ID6
FROM PER_BUSINESS_GROUPS_PERF PBG GROUP BY PBG.BUSINESS_GROUP_ID ) V
, PER_ALL_PEOPLE_F PP
, PER_ALL_PEOPLE_F PP2
, PER_ALL_ASSIGNMENTS_F PA
WHERE PP.BUSINESS_GROUP_ID + 0 = V.BUSINESS_GROUP_ID
AND PP.EFFECTIVE_START_DATE > PP.START_DATE
AND PP2.PERSON_ID = PP.PERSON_ID
AND PP2.EFFECTIVE_END_DATE = PP.EFFECTIVE_START_DATE - 1
AND PP2.FULL_NAME <> PP.FULL_NAME
AND PA.PERSON_ID = PP.PERSON_ID
AND PA.PRIMARY_FLAG = 'Y'
AND PP.EFFECTIVE_START_DATE BETWEEN PA.EFFECTIVE_START_DATE
AND PA.EFFECTIVE_END_DATE
AND PP.EFFECTIVE_START_DATE BETWEEN PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_DATE(V.INPUT_VALUE_ID1
, PA.ASSIGNMENT_ID
, PP.EFFECTIVE_START_DATE) + DECODE(PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR(V.INPUT_VALUE_ID2
, PA.ASSIGNMENT_ID
, PP.EFFECTIVE_START_DATE)
, '1'
, 1
, '2'
, 1
, '3'
, 0)
AND NVL(PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_DATE(V.INPUT_VALUE_ID3
, PA.ASSIGNMENT_ID
, PP.EFFECTIVE_START_DATE)
, PP.EFFECTIVE_START_DATE)