[Home] [Help]
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
165: ,hr_soft_coding_keyflex hsck
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''
170: AND paf.effective_start_date <= to_date('''||g_end_date||''',''YYYY/MM/DD/'')
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''
201: and paf.effective_start_date <= to_date('''||g_end_date||''',''YYYY/MM/DD/'')
231: and hsck.segment1 in
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))
301: (
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))
332: stperson number,
333: endperson number
334: ) is
335:
336: select /*+ index(hou,HR_ORGANIZATION_UNITS_FK1)*/
337: distinct paf.assignment_id,
338: hsck.segment1
339: from per_all_people_f ppf ,
340: per_all_assignments_f paf,
400: and hsck.segment1 in
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))
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'
435: and paf.primary_flag = 'Y'
508: and hsck.segment1 in
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))
1211: ,hsck.segment1 tax_unit_id
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)
1233: ,hoi.org_information2 user_control_data
1234: ,hou.name name
1235: FROM
1236: hr_organization_information hoi
1237: ,hr_organization_units hou
1238: WHERE
1239: hoi.organization_id = p_tax_unit_id
1240: AND hoi.org_information_context = 'EVS Filing'
1241: and hoi.organization_id = hou.organization_id
1307:
1308: if csr_get_org_info%NOTFOUND then
1309: close csr_get_org_info;
1310: select name into l_gre_name
1311: from hr_organization_units
1312: where organization_id = p_tax_unit_id;
1313: fnd_message.set_name('PER','HR_449246_EVS_NO_REQ_ID');
1314: fnd_message.set_token('GRE',l_gre_name);
1315: fnd_message.raise_error;