156: sqlstr :=
157:
158: -- Bug# 5687781
159:
160: 'SELECT /*+ INDEX(hsck,HR_SOFT_CODING_KEYFLEX_PK),
161: INDEX(HR_ORGANIZATION_UNITS_PK,hou)*/
162: DISTINCT ppf.person_id
163: FROM per_all_people_f ppf
164: ,per_all_assignments_f paf
161: INDEX(HR_ORGANIZATION_UNITS_PK,hou)*/
162: DISTINCT ppf.person_id
163: FROM per_all_people_f ppf
164: ,per_all_assignments_f paf
165: ,hr_soft_coding_keyflex hsck
166: ,hr_organization_units hou
167: ,hr_organization_information hoi
168: WHERE paf.assignment_type = ''E''
169: AND paf.primary_flag = ''Y''
192:
193: 'select distinct ppf.person_id
194: from per_all_people_f ppf
195: ,per_all_assignments_f paf
196: ,hr_soft_coding_keyflex hsck
197: ,hr_organization_units hou
198: ,hr_organization_information hoi
199: where paf.assignment_type = ''E''
200: and paf.primary_flag = ''Y''
220: /*
221: 'select distinct ppf.person_id
222: from
223: per_people_f ppf
224: ,hr_soft_coding_keyflex hsck
225: ,per_assignments_f paf
226: where ppf.person_id = paf.person_id
227: and ppf.effective_start_date <= to_date('''||g_end_date||''',''YYYY/MM/DD/'')
228: and ppf.effective_end_date >= to_date('''||g_start_date||''',''YYYY/MM/DD/'')
232: (
233: select distinct hsck2.segment1
234: from hr_organization_information hoi
235: ,hr_organization_units hou
236: ,hr_soft_coding_keyflex hsck2
237: where
238: hou.business_group_id +0 = ' || g_business_group_id || '
239: and hsck2.segment1 = nvl('''||g_tax_unit_id||''',to_char(hou.organization_id))
240: and hoi.organization_id = hou.organization_id
280: select distinct paf.assignment_id
281: ,hsck.segment1
282: from
283: per_people_f ppf
284: ,hr_soft_coding_keyflex hsck
285: ,per_assignments_f paf
286: ,per_periods_of_service pps
287: where ppf.person_id = pps.person_id
288: and pps.date_start
302: select distinct hsck2.segment1
303: from
304: hr_organization_information hoi
305: ,hr_organization_units hou
306: ,hr_soft_coding_keyflex hsck2
307: where
308: hou.business_group_id +0 = g_business_group_id
309: and hsck2.segment1 = nvl(g_tax_unit_id,to_char(hou.organization_id))
310: and hoi.organization_id = hou.organization_id
337: distinct paf.assignment_id,
338: hsck.segment1
339: from per_all_people_f ppf ,
340: per_all_assignments_f paf,
341: hr_soft_coding_keyflex hsck,
342: hr_all_organization_units hou,
343: hr_organization_information hoi,
344: per_assignment_status_types past
345: where paf.assignment_type = 'E'
378: /* select distinct paf.assignment_id
379: ,hsck.segment1
380: from
381: per_people_f ppf
382: ,hr_soft_coding_keyflex hsck
383: ,per_assignments_f paf
384: ,per_periods_of_service pps
385: ,per_assignment_status_types past
386: where ppf.person_id = paf.person_id
401: (
402: select distinct hsck2.segment1
403: from hr_organization_information hoi
404: ,hr_organization_units hou
405: ,hr_soft_coding_keyflex hsck2
406: where
407: hou.business_group_id +0 = g_business_group_id
408: and hsck2.segment1 = nvl(g_tax_unit_id,to_char(hou.organization_id))
409: and hoi.organization_id = hou.organization_id
426: /* commented for bug# 5687781
427: select distinct paf.assignment_id, hsck.segment1
428: from per_all_people_f ppf
429: ,per_all_assignments_f paf
430: ,hr_soft_coding_keyflex hsck
431: ,hr_organization_units hou
432: ,hr_organization_information hoi
433: ,per_assignment_status_types past
434: where paf.assignment_type = 'E'
475: select distinct paf.assignment_id
476: ,hsck.segment1
477: from
478: per_people_f ppf
479: ,hr_soft_coding_keyflex hsck
480: ,per_assignments_f paf
481: ,per_periods_of_service pps
482: ,per_person_type_usages_f ptu
483: ,per_person_types ppt
509: (
510: select distinct hsck2.segment1
511: from hr_organization_information hoi
512: ,hr_organization_units hou
513: ,hr_soft_coding_keyflex hsck2
514: where
515: hou.business_group_id +0 = g_business_group_id
516: and hsck2.segment1 = nvl(g_tax_unit_id,to_char(hou.organization_id))
517: and hoi.organization_id = hou.organization_id
713: --,paf.ASSIGNMENT_ID
714:
715: From
716: per_people_f ppf
717: ,hr_soft_coding_keyflex hsck
718: ,per_assignments_f paf
719: ,per_periods_of_service pps
720: ,per_assignment_status_types past
721: Where
758: --,paf.ASSIGNMENT_ID
759:
760: From
761: per_people_f ppf
762: ,hr_soft_coding_keyflex hsck
763: ,per_assignments_f paf
764: ,per_periods_of_service pps
765: Where
766: ppf.person_id = pps.person_id
797: --,paf.ASSIGNMENT_ID
798:
799: From
800: per_people_f ppf
801: ,hr_soft_coding_keyflex hsck
802: ,per_assignments_f paf
803: ,per_periods_of_service pps
804: ,per_person_type_usages_f ptu
805: ,per_person_types ppt
851: --,paf.ASSIGNMENT_ID
852:
853: From
854: per_people_f ppf
855: ,hr_soft_coding_keyflex hsck
856: ,per_assignments_f paf
857: ,per_periods_of_service pps
858: ,per_person_type_usages_f ptu
859: ,per_person_types ppt
1212: ,hoi2.org_information1 requester_id_code
1213: ,hoi2.org_information2 user_control_data
1214: from hr_organization_information hoi
1215: ,hr_organization_units hou
1216: ,hr_soft_coding_keyflex hsck
1217: ,hr_organization_information hoi2
1218: where hou.business_group_id = p_business_group_id
1219: and hsck.segment1 = to_char(hou.organization_id)
1220: and hoi.organization_id = hou.organization_id