[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))
1191: ,hsck.segment1 tax_unit_id
1192: ,hoi2.org_information1 requester_id_code
1193: ,hoi2.org_information2 user_control_data
1194: from hr_organization_information hoi
1195: ,hr_organization_units hou
1196: ,hr_soft_coding_keyflex hsck
1197: ,hr_organization_information hoi2
1198: where hou.business_group_id = p_business_group_id
1199: and hsck.segment1 = to_char(hou.organization_id)
1213: ,hoi.org_information2 user_control_data
1214: ,hou.name name
1215: FROM
1216: hr_organization_information hoi
1217: ,hr_organization_units hou
1218: WHERE
1219: hoi.organization_id = p_tax_unit_id
1220: AND hoi.org_information_context = 'EVS Filing'
1221: and hoi.organization_id = hou.organization_id
1287:
1288: if csr_get_org_info%NOTFOUND then
1289: close csr_get_org_info;
1290: select name into l_gre_name
1291: from hr_organization_units
1292: where organization_id = p_tax_unit_id;
1293: fnd_message.set_name('PER','HR_449246_EVS_NO_REQ_ID');
1294: fnd_message.set_token('GRE',l_gre_name);
1295: fnd_message.raise_error;