[Home] [Help]
37: inserting temporary employees as well as
38: Promoted employee details.
39: 05-Jul-2005 ssmukher 115.2 Modified the cursor c_promo.
40: Modified the effective end date condition for
41: per_all_people_f and per_all_assignments_f
42: table
43: 05-Jul-2005 ssmukher 115.3 Modified the code so that if the designated
44: code is not specified for an employee then
45: record is not inserted into the exception
166: nvl(ppf.per_information6,'N') desg_vminor,
167: nvl(ppf.per_information7,'N') desg_disab,
168: trunc(ppf.original_date_of_hire) date_of_hire
169: from
170: per_all_people_f ppf
171: where person_type_exists(ppf.person_type_id) is not null and
172: ppf.effective_end_date >= p_start_date and
173: ppf.business_group_id = p_business_group_id
174: order by l_person_id,emp_no;
254: p_end_date date) is
255: select trunc(ppos.actual_termination_date) term_date,
256: trunc(ppos.projected_termination_date) end_dt
257: from
258: per_all_people_f ppf,
259: per_periods_of_service ppos
260: where person_type_exists(ppf.person_type_id) is not null and
261: ppos.actual_termination_date between
262: ppf.effective_start_date and
275: select max(paf.effective_start_date) start_dt,
276: trunc(ppos.actual_termination_date) term_date,
277: trunc(ppos.projected_termination_date) end_dt
278: from
279: per_all_people_f ppf,
280: per_all_assignments_f paf,
281: per_periods_of_service ppos
282: where person_type_exists(ppf.person_type_id) is not null and
283: paf.person_id = ppf.person_id and
304: cursor c_total_salary (p_person_id number) is
305: select sum(trunc(to_number(pppv.proposed_salary)) * ppb.pay_annualization_factor) salary
306: from per_pay_bases ppb,
307: per_all_assignments_f paf,
308: per_all_people_f ppf,
309: per_pay_proposals_v2 pppv,
310: per_assignment_status_types past,
311: hr_lookups hrl
312: where paf.pay_basis_id = ppb.pay_basis_id and
332: and hrl1.lookup_code = past1.per_system_status
333: and hrl1.lookup_code = 'ACTIVE_ASSIGN'
334: ) and
335: ppf.effective_start_date = ( select max(effective_start_date)
336: from per_all_people_f ppf1
337: where ppf1.person_id = ppf.person_id
338: and ppf1.business_group_id = p_business_group_id
339: and ppf1.current_emp_or_apl_flag = 'Y'
340: ) and
397: p_end_date date) is
398: select distinct ppp.assignment_id asg_id,
399: TRUNC(ppp.change_date) promo_date
400: from per_pay_proposals_v2 ppp,
401: per_all_people_f ppf,
402: per_all_assignments_f paf
403: where ppf.person_id = paf.person_id and
404: ppf.person_id = p_person_id and
405: paf.assignment_id = ppp.assignment_id and
420: table_date per_fastformula_events_utility.date_tab;
421:
422: l_output_txt varchar2(1000);
423: l_org_info hr_organization_information.org_information1%type;
424: l_personid per_all_people_f.person_id%type;
425: l_emp_no per_all_people_f.employee_number%type;
426: l_first_name per_all_people_f.first_name%type;
427: l_last_name per_all_people_f.last_name%type;
428: l_employment_category varchar2(5);
421:
422: l_output_txt varchar2(1000);
423: l_org_info hr_organization_information.org_information1%type;
424: l_personid per_all_people_f.person_id%type;
425: l_emp_no per_all_people_f.employee_number%type;
426: l_first_name per_all_people_f.first_name%type;
427: l_last_name per_all_people_f.last_name%type;
428: l_employment_category varchar2(5);
429: l_gender per_all_people_f.sex%type;
422: l_output_txt varchar2(1000);
423: l_org_info hr_organization_information.org_information1%type;
424: l_personid per_all_people_f.person_id%type;
425: l_emp_no per_all_people_f.employee_number%type;
426: l_first_name per_all_people_f.first_name%type;
427: l_last_name per_all_people_f.last_name%type;
428: l_employment_category varchar2(5);
429: l_gender per_all_people_f.sex%type;
430: l_loc_id per_all_assignments_f.location_id%type;
423: l_org_info hr_organization_information.org_information1%type;
424: l_personid per_all_people_f.person_id%type;
425: l_emp_no per_all_people_f.employee_number%type;
426: l_first_name per_all_people_f.first_name%type;
427: l_last_name per_all_people_f.last_name%type;
428: l_employment_category varchar2(5);
429: l_gender per_all_people_f.sex%type;
430: l_loc_id per_all_assignments_f.location_id%type;
431: l_asg_id per_all_assignments_f.assignment_id%type;
425: l_emp_no per_all_people_f.employee_number%type;
426: l_first_name per_all_people_f.first_name%type;
427: l_last_name per_all_people_f.last_name%type;
428: l_employment_category varchar2(5);
429: l_gender per_all_people_f.sex%type;
430: l_loc_id per_all_assignments_f.location_id%type;
431: l_asg_id per_all_assignments_f.assignment_id%type;
432: l_province varchar2(5);
433: l_desg_abor per_all_people_f.per_information5%type;
429: l_gender per_all_people_f.sex%type;
430: l_loc_id per_all_assignments_f.location_id%type;
431: l_asg_id per_all_assignments_f.assignment_id%type;
432: l_province varchar2(5);
433: l_desg_abor per_all_people_f.per_information5%type;
434: l_desg_vminor per_all_people_f.per_information6%type;
435: l_desg_disab per_all_people_f.per_information7%type;
436: l_naics_no hr_organization_information.org_information8%type;
437: l_naics_no_gre hr_organization_information.org_information8%type;
430: l_loc_id per_all_assignments_f.location_id%type;
431: l_asg_id per_all_assignments_f.assignment_id%type;
432: l_province varchar2(5);
433: l_desg_abor per_all_people_f.per_information5%type;
434: l_desg_vminor per_all_people_f.per_information6%type;
435: l_desg_disab per_all_people_f.per_information7%type;
436: l_naics_no hr_organization_information.org_information8%type;
437: l_naics_no_gre hr_organization_information.org_information8%type;
438: l_hire_date date;
431: l_asg_id per_all_assignments_f.assignment_id%type;
432: l_province varchar2(5);
433: l_desg_abor per_all_people_f.per_information5%type;
434: l_desg_vminor per_all_people_f.per_information6%type;
435: l_desg_disab per_all_people_f.per_information7%type;
436: l_naics_no hr_organization_information.org_information8%type;
437: l_naics_no_gre hr_organization_information.org_information8%type;
438: l_hire_date date;
439: l_term_date date;
894: where pcer.context = 'PROMO' and
895: pcer.request_id = 20
896: order by emp_no,promo_dt;
897:
898: l_emp_no per_all_people_f.employee_number%type;
899: l_prev_emp per_all_people_f.employee_number%type;
900: l_promo_dt varchar2(10);
901: l_promo_cnt number;
902: l_output varchar2(1000);
895: pcer.request_id = 20
896: order by emp_no,promo_dt;
897:
898: l_emp_no per_all_people_f.employee_number%type;
899: l_prev_emp per_all_people_f.employee_number%type;
900: l_promo_dt varchar2(10);
901: l_promo_cnt number;
902: l_output varchar2(1000);
903: begin
943: where pcer.context = 'TMP' and
944: pcer.request_id = 10
945: order by emp_no;
946:
947: l_emp_no per_all_people_f.employee_number%type;
948: l_term_cnt number;
949: l_start_dt varchar2(10);
950: l_end_dt varchar2(10);
951: l_term_dt varchar2(10);
949: l_start_dt varchar2(10);
950: l_end_dt varchar2(10);
951: l_term_dt varchar2(10);
952: l_output varchar2(1000);
953: l_prev_emp per_all_people_f.employee_number%type;
954: BEGIN
955: l_prev_emp := -999;
956: open c_term_details;
957: loop
1017: from per_ca_ee_report_lines pcer
1018: where pcer.request_id = 30 and
1019: pcer.context = 'EXCEP'
1020: order by emp_no;
1021: l_emp_no per_all_people_f.employee_number%type;
1022: l_prov varchar2(20);
1023: l_naic varchar2(20);
1024: l_cma varchar2(20);
1025: l_emp_catg varchar2(20);