[Home] [Help]
145:
146: CURSOR c_app_term_assignments
147: ( l_location_id per_gen_hierarchy_nodes.entity_id%type,
148: l_period_end date,
149: l_period_start per_assignments_f.effective_start_date%type
150: )
151: IS
152: /* Retrieve applicants only */
153: SELECT paf.assignment_id,
154: paf.person_id,
155: pj.job_information1 job_category,
156: paf.assignment_status_type_id
157: ,'APPLICANT'
158: FROM per_assignments_f paf,
159: per_jobs pj,
160: per_assignment_status_types past,
161: fnd_common_lookups fcl
162: WHERE paf.assignment_status_type_id = past.assignment_status_type_id
170: -- and paf.effective_start_date between
171: -- l_period_start and l_period_end)
172: -- )
173: AND paf.effective_start_Date = (select max(paf1.effective_Start_date)
174: from per_assignments_f paf1
175: where paf1.assignment_id = paf.assignment_id
176: and paf1.effective_start_Date <=l_period_end
177: and paf1.assignment_status_type_id =
178: paf.assignment_Status_type_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
198: ,fnd_common_lookups fcl
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
245: and ppf.effective_end_date >= l_period_start
246: and ppf.person_type_id = ppt.person_type_id
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
275: and pps.person_id = ppf.person_id
276: and pps.actual_termination_date is not null
295:
296: /* This cursor checks if applicant was also an ACTIVE_APL in the same period.
297: If he had both the status in the same period then he should be counted just once.*/
298:
299: CURSOR applicant_active(l_asgn_id per_assignments_f.assignment_id%type,
300: l_period_start date,
301: l_period_end date)
302: is
303: select 'x'
300: l_period_start date,
301: l_period_end date)
302: is
303: select 'x'
304: from per_assignments_f paf,
305: per_assignment_Status_types past
306: where paf.assignment_id = l_asgn_id
307: and paf.assignment_type = 'A'
308: and paf.assignment_status_type_id = past.assignment_status_type_id
681:
682: /* This cursor selects the person information like race, job_category only for employees
683: and EMP_APL. */
684:
685: CURSOR c_person_infm( l_person_id per_assignments_f.person_id%type,
686: l_period_start date,
687: l_period_end date)
688:
689: IS
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)
736: IS
739: paf.assignment_type
740: ,paf.assignment_id
741: ,paf.effective_start_date
742: ,paf.effective_end_date
743: FROM per_assignments_f paf,
744: per_jobs pj,
745: fnd_common_lookups fcl
746: WHERE paf.person_id =l_person_id
747: AND pj.job_id = paf.job_id
768: 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);
1192: /*If the report is not run for the recently concluded calendar year
1193: then the asact_id should be calculated for the recently concluded
1194: calendar year which is selected here.*/
1195:
1196: CURSOR asact_id(c_assignment_id per_assignments_f.assignment_id%type,
1197: c_period_end date)
1198: IS
1199: SELECT to_number(substr(max(lpad(paa.action_sequence,15,'0')||
1200: paa.assignment_action_id),16))
1227: and ppf2.effective_start_date < p_end_date
1228: and ppf2.effective_end_date > p_start_date
1229: );
1230:
1231: CURSOR c_get_updated_racecode( l_person_id per_assignments_f.person_id%type,
1232: l_period_start date,
1233: l_period_end date)
1234:
1235: IS
1264: and ppf.person_id =l_person_id
1265: and ppt.person_type_id = ppf.person_type_id
1266: and pps.person_id = ppf.person_id;
1267:
1268: l_hours_worked per_assignments_f.normal_hours%type;
1269: l_asact_id pay_assignment_actions.assignment_action_id%type;
1270: l_year varchar2(4);
1271: calendar_period_start date;
1272: calendar_period_end date;
1874: m_promotion_count out nocopy number,
1875: f_promotion_count out nocopy number) is
1876:
1877: /*-----------------------------------
1878: cursor promotion_check(l_assignment_id per_assignments_f.assignment_id%type) is
1879: select 'Y'
1880: from per_assignment_extra_info
1881: where aei_information_category = 'Promotion'
1882: and to_date(aei_information1,'dd-mm-yyyy') between