[Home] [Help]
189: ,paf.person_id
190: ,pj.job_information1 job_category
191: ,paf.assignment_status_type_id
192: ,ppt.system_person_type
193: FROM per_people_f ppf
194: ,per_assignments_f paf
195: ,per_periods_of_service pps
196: ,per_person_types ppt
197: ,per_jobs pj
202: between l_period_start and l_period_end
203: /* BUG4434130
204: and ppf.effective_start_date =
205: (select max(ppf2.effective_start_date)
206: from per_people_f ppf2
207: where ppf2.person_id = ppf.person_id
208: and ppf2.current_employee_flag is null
209: )
210: */
228:
229: /* This cursor picks up the person's information.*/
230:
231: CURSOR c_persons
232: (l_person_id per_people_f.person_id%type,
233: l_period_start date,
234: l_period_end date)
235: is
236: select ppf.per_information1 race_code
236: select ppf.per_information1 race_code
237: ,ppf.sex sex
238: ,ppt.system_person_type person_type
239: ,pj.job_information1 job_category -- BUG4434130
240: from per_people_f ppf
241: ,per_person_types ppt
242: ,per_assignments_f paf -- BUG4434130
243: ,per_jobs pj -- BUG4434130
244: where ppf.effective_start_date <= l_period_end
247: -- and ppt.system_person_type not in ( 'EMP_APL','EMP')
248: and ppt.system_person_type <> 'EMP' -- BUG3878442
249: and ppf.effective_start_Date
250: = (select max(effective_Start_date)
251: from per_people_f ppf1
252: where ppf1.person_type_id = ppf.person_type_id
253: and ppf1.effective_start_Date <=l_period_end
254: and ppf1.person_id =ppf.person_id
255: )
266: select ppf.per_information1 race_code
267: ,ppf.sex sex
268: ,'EX_EMP' person_type
269: ,pj.job_information1 job_category
270: from per_people_f ppf
271: ,per_periods_of_service pps
272: ,per_assignments_f paf
273: ,per_jobs pj
274: where ppf.person_id = l_person_id
311: and paf.effective_end_date <= l_period_end;
312:
313:
314: CURSOR c_race_code
315: (l_person_id per_people_f.person_id%type,
316: l_period_start date,
317: l_period_end date)
318: is
319: select ppf.per_information1 race_code,
317: l_period_end date)
318: is
319: select ppf.per_information1 race_code,
320: ppt.system_person_type person_type
321: from per_people_f ppf
322: ,per_person_types ppt
323: ,per_periods_of_service pps
324: where ppf.effective_start_date <= l_period_end
325: and ppf.effective_end_date >= l_period_start
694: ppf.effective_start_Date eff_Start,
695: ppf.effective_end_date eff_end,
696: ppf.per_information1 race,
697: ppf.person_type_id,pps.date_start service_start
698: FROM per_people_f ppf,
699: per_person_types ppt,
700: per_periods_of_service pps
701: WHERE ( ( ppt.system_person_type = 'EMP'
702: /* BUG4434130
701: WHERE ( ( ppt.system_person_type = 'EMP'
702: /* BUG4434130
703: and ppf.effective_start_date
704: = (select max(ppf1.effective_start_date)
705: from per_people_f ppf1
706: where ppf1.person_type_id = ppf.person_type_id
707: and ppf1.person_id = ppf.person_id
708: and ppf1.effective_start_date<=l_period_end
709: )
715: OR
716: ( ppt.system_person_type = 'EMP_APL'
717: and ppf.effective_start_date
718: = (select max(ppf2.effective_Start_date)
719: from per_people_f ppf2
720: where ppf2.person_id = ppf.person_id
721: and ppf2.person_type_id = ppf.person_type_id
722: and ppf2.effective_start_Date <=l_period_end
723: and ppf2.effective_end_date >= l_period_start
727: and ppf.person_id =l_person_id
728: and ppt.person_type_id = ppf.person_type_id
729: and pps.person_id = ppf.person_id;
730:
731: CURSOR assignment_details(l_person_id per_people_f.person_id%TYPE,
732: l_location_id per_assignments_f.location_id%TYPE,
733: l_person_type per_person_types.system_person_type%TYPE,
734: l_period_start date,
735: l_period_end date)
769: l_period_start date;
770: l_est_count number;
771: l_max_asact_id pay_assignment_Actions.assignment_action_id%type;
772: l_location_id per_assignments_f.location_id%TYPE;
773: l_person_id per_people_f.person_id%type;
774: l_job varchar2(10);
775: l_assignment_type varchar2(10);
776: l_package varchar2(70);
777: l_effective_start_date date; -- BUG3940867
1211: ,p_start_date in date
1212: ,p_end_date in date)
1213: is
1214: select 1
1215: from per_people_f ppf
1216: ,per_periods_of_service pps
1217: where ppf.person_id = p_person_id
1218: and ppf.effective_start_date < p_end_date
1219: and ppf.effective_end_date > p_start_date
1221: and pps.actual_termination_date between
1222: p_start_date and p_end_date
1223: and ppf.effective_start_date =
1224: (select max(ppf2.effective_start_date)
1225: from per_people_f ppf2
1226: where ppf2.person_id = ppf.person_id
1227: and ppf2.effective_start_date < p_end_date
1228: and ppf2.effective_end_date > p_start_date
1229: );
1237: SELECT ppt.system_person_type person_type,
1238: ppf.effective_start_Date eff_Start,
1239: ppf.effective_end_date eff_end,
1240: ppf.per_information1 race
1241: FROM per_people_f ppf,
1242: per_person_types ppt,
1243: per_periods_of_service pps
1244: WHERE ( ( ppt.system_person_type = 'EMP'
1245: and ppf.effective_start_date
1243: per_periods_of_service pps
1244: WHERE ( ( ppt.system_person_type = 'EMP'
1245: and ppf.effective_start_date
1246: = (select max(effective_start_date)
1247: from per_people_f
1248: where person_type_id = ppf.person_type_id
1249: and person_id = ppf.person_id
1250: and effective_start_date<=l_period_end
1251: )
1252: )
1253: OR
1254: ( ppt.system_person_type = 'EMP_APL'
1255: and ppf.effective_start_date = (select max(effective_Start_date)
1256: from per_people_f
1257: where person_id = ppf.person_id
1258: and person_type_id = ppf.person_type_id
1259: and effective_start_Date <=l_period_end
1260: and effective_end_date >= l_period_start