[Home] [Help]
152: if (l_c_asg_amendments.ioe='I' /*and l_c_asg_amendments.cnt > 0*/) then
153:
154: open c_ref_cur for -- ASG 1
155: 'select distinct paf.person_id person_id ,pef.employee_number employee_number,pef.full_name full_name
156: from per_all_assignments_f paf,
157: hr_assignment_sets hs,
158: per_all_people_f pef,
159: per_periods_of_service ppos
160: where hs.assignment_set_id=:p_assignment_set_id
173: and :l_date_to between pef.effective_start_date and pef.effective_end_date
174: and ((:l_date_to between paf.effective_start_date and paf.effective_end_date)
175: or
176: (:l_date_to >= (select max(effective_end_date)
177: from per_all_assignments_f p1 /*To include TERMINATED employee*/
178: where p1.person_id=pef.person_id))
179: )
180: ORDER BY '||p_sort_order
181: using p_assignment_set_id,p_business_group_id,p_assignment_set_id,l_date_from,l_date_to,l_date_to,l_date_to;
190:
191: if (l_c_asg_amendments.ioe='E' /* and l_c_asg_amendments.cnt > 0*/) then
192: open c_ref_cur for
193: 'select distinct paf.person_id person_id ,pef.employee_number employee_number,pef.full_name full_name
194: from per_all_assignments_f paf,
195: hr_assignment_sets hs,
196: per_all_people_f pef,
197: per_periods_of_service ppos
198: where hs.assignment_set_id=:p_assignment_set_id
211: and :l_date_to between pef.effective_start_date and pef.effective_end_date
212: and ((:l_date_to between paf.effective_start_date and paf.effective_end_date)
213: or
214: (:l_date_to >= (select max(effective_end_date)
215: from per_all_assignments_f p1 /*To include TERMINATED employee*/
216: where p1.person_id=pef.person_id))
217: )
218: ORDER BY '||p_sort_order
219: using p_assignment_set_id,p_business_group_id,p_assignment_set_id,l_date_from,l_date_to,l_date_to,l_date_to;
227: else
228: ---- No amendment is mentioned
229: open c_ref_cur for
230: 'select distinct paf.person_id ,pef.employee_number employee_number,pef.full_name full_name
231: from per_all_assignments_f paf,
232: hr_assignment_sets hs,
233: per_all_people_f pef,
234: per_periods_of_service ppos
235: where hs.assignment_set_id=:p_assignment_set_id
248: and :l_date_to between pef.effective_start_date and pef.effective_end_date
249: and ((:l_date_to between paf.effective_start_date and paf.effective_end_date)
250: or
251: (:l_date_to >= (select max(effective_end_date)
252: from per_all_assignments_f p1 /*To include TERMINATED employee*/
253: where p1.person_id=pef.person_id))
254: )
255: ORDER BY '||p_sort_order
256: using p_assignment_set_id,p_business_group_id,p_assignment_set_id,l_date_from,l_date_to,l_date_to,l_date_to;
267:
268: open c_ref_cur for
269: 'Select DISTINCT pap.person_id person_id,pap.employee_number employee_number,pap.full_name full_name
270: from per_all_people_f pap,
271: per_all_assignments_f paa,
272: hr_all_organization_units hao,
273: hr_organization_information hoi,
274: per_periods_of_service ppos
275: where pap.person_id =paa.person_id
287: and :l_date_to between pap.effective_start_date and pap.effective_end_date
288: and ((:l_date_to between paa.effective_start_date and paa.effective_end_date)
289: or
290: (:l_date_to >= (select max(effective_end_date)
291: from per_all_assignments_f p1 /*To include TERMINATED employee*/
292: where p1.person_id=pap.person_id))
293: )
294: ORDER BY '||p_sort_order
295: using
356: hla2.region_1||' '||hla2.country||' '||hla2.telephone_number_1||' '||hla2.telephone_number_2||' '||
357: hla2.telephone_number_3 COM_ADDRESS
358: from per_all_people_f papf,
359: per_addresses pa,
360: per_all_assignments_f paaf,
361: per_periods_of_service ppos,
362: per_contracts_f pcf,
363: per_collective_agreements pca,
364: hr_all_organization_units hou1,
392: and l_p_date_to between papf.effective_start_date and papf.effective_end_date
393: and ((l_p_date_to between paaf.effective_start_date and paaf.effective_end_date)
394: or
395: (l_p_date_to >= (select max(effective_end_date)
396: from per_all_assignments_f p1 /*To include TERMINATED employee*/
397: where p1.person_id=papf.person_id))
398: )
399: and l_p_date_to between pcf.effective_start_date and pcf.effective_end_date;
400: