[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
184: nvl(ppf.per_information6,'N') desg_vminor,
185: nvl(ppf.per_information7,'N') desg_disab,
186: trunc(ppf.original_date_of_hire) date_of_hire
187: from
188: per_all_people_f ppf
189: where person_type_exists(ppf.person_type_id) is not null and
190: ppf.effective_end_date >= p_start_date and
191: ppf.business_group_id = p_business_group_id and
192: -- to_char(ppf.original_date_of_hire,'YYYY') <= p_year
277: p_end_date date) is
278: select trunc(ppos.actual_termination_date) term_date,
279: trunc(ppos.projected_termination_date) end_dt
280: from
281: per_all_people_f ppf,
282: per_periods_of_service ppos
283: where person_type_exists(ppf.person_type_id) is not null and
284: ppos.actual_termination_date between
285: ppf.effective_start_date and
298: select max(paf.effective_start_date) start_dt,
299: trunc(ppos.actual_termination_date) term_date,
300: trunc(ppos.projected_termination_date) end_dt
301: from
302: per_all_people_f ppf,
303: per_all_assignments_f paf,
304: per_periods_of_service ppos
305: where person_type_exists(ppf.person_type_id) is not null and
306: paf.person_id = ppf.person_id and
328: select max(paf.effective_start_date) start_dt,
329: trunc(ppos.actual_termination_date) term_date,
330: trunc(ppos.projected_termination_date) end_dt
331: from
332: per_all_people_f ppf,
333: per_all_assignments_f paf,
334: per_periods_of_service ppos
335: where person_type_exists(ppf.person_type_id) is not null and
336: paf.person_id = ppf.person_id and
358: --select sum(trunc(to_number(pppv.proposed_salary)) * ppb.pay_annualization_factor) salary
359: select round(sum(to_number(pppv.proposed_salary) * ppb.pay_annualization_factor)) salary
360: from per_pay_bases ppb,
361: per_all_assignments_f paf,
362: per_all_people_f ppf,
363: per_pay_proposals_v2 pppv,
364: per_assignment_status_types past,
365: hr_lookups hrl
366: where paf.pay_basis_id = ppb.pay_basis_id and
387: and hrl1.lookup_code = past1.per_system_status
388: and hrl1.lookup_code = 'ACTIVE_ASSIGN'
389: ) and
390: ppf.effective_start_date = ( select max(effective_start_date)
391: from per_all_people_f ppf1
392: where ppf1.person_id = ppf.person_id
393: and ppf1.business_group_id = p_business_group_id
394: and ppf1.current_emp_or_apl_flag = 'Y'
395: ) and
454: p_end_date date) is
455: select distinct ppp.assignment_id asg_id,
456: TRUNC(ppp.change_date) promo_date
457: from per_pay_proposals_v2 ppp,
458: per_all_people_f ppf,
459: per_all_assignments_f paf
460: where ppf.person_id = paf.person_id and
461: ppf.person_id = p_person_id and
462: paf.assignment_id = ppp.assignment_id and
477: table_date per_fastformula_events_utility.date_tab;
478:
479: l_output_txt varchar2(1000);
480: l_org_info hr_organization_information.org_information1%type;
481: l_personid per_all_people_f.person_id%type;
482: l_emp_no per_all_people_f.employee_number%type;
483: l_first_name per_all_people_f.first_name%type;
484: l_last_name per_all_people_f.last_name%type;
485: l_employment_category varchar2(5);
478:
479: l_output_txt varchar2(1000);
480: l_org_info hr_organization_information.org_information1%type;
481: l_personid per_all_people_f.person_id%type;
482: l_emp_no per_all_people_f.employee_number%type;
483: l_first_name per_all_people_f.first_name%type;
484: l_last_name per_all_people_f.last_name%type;
485: l_employment_category varchar2(5);
486: l_gender per_all_people_f.sex%type;
479: l_output_txt varchar2(1000);
480: l_org_info hr_organization_information.org_information1%type;
481: l_personid per_all_people_f.person_id%type;
482: l_emp_no per_all_people_f.employee_number%type;
483: l_first_name per_all_people_f.first_name%type;
484: l_last_name per_all_people_f.last_name%type;
485: l_employment_category varchar2(5);
486: l_gender per_all_people_f.sex%type;
487: l_loc_id per_all_assignments_f.location_id%type;
480: l_org_info hr_organization_information.org_information1%type;
481: l_personid per_all_people_f.person_id%type;
482: l_emp_no per_all_people_f.employee_number%type;
483: l_first_name per_all_people_f.first_name%type;
484: l_last_name per_all_people_f.last_name%type;
485: l_employment_category varchar2(5);
486: l_gender per_all_people_f.sex%type;
487: l_loc_id per_all_assignments_f.location_id%type;
488: l_asg_id per_all_assignments_f.assignment_id%type;
482: l_emp_no per_all_people_f.employee_number%type;
483: l_first_name per_all_people_f.first_name%type;
484: l_last_name per_all_people_f.last_name%type;
485: l_employment_category varchar2(5);
486: l_gender per_all_people_f.sex%type;
487: l_loc_id per_all_assignments_f.location_id%type;
488: l_asg_id per_all_assignments_f.assignment_id%type;
489: l_province varchar2(5);
490: l_desg_abor per_all_people_f.per_information5%type;
486: l_gender per_all_people_f.sex%type;
487: l_loc_id per_all_assignments_f.location_id%type;
488: l_asg_id per_all_assignments_f.assignment_id%type;
489: l_province varchar2(5);
490: l_desg_abor per_all_people_f.per_information5%type;
491: l_desg_vminor per_all_people_f.per_information6%type;
492: l_desg_disab per_all_people_f.per_information7%type;
493: l_naics_no hr_organization_information.org_information8%type;
494: l_naics_no_gre hr_organization_information.org_information8%type;
487: l_loc_id per_all_assignments_f.location_id%type;
488: l_asg_id per_all_assignments_f.assignment_id%type;
489: l_province varchar2(5);
490: l_desg_abor per_all_people_f.per_information5%type;
491: l_desg_vminor per_all_people_f.per_information6%type;
492: l_desg_disab per_all_people_f.per_information7%type;
493: l_naics_no hr_organization_information.org_information8%type;
494: l_naics_no_gre hr_organization_information.org_information8%type;
495: l_hire_date date;
488: l_asg_id per_all_assignments_f.assignment_id%type;
489: l_province varchar2(5);
490: l_desg_abor per_all_people_f.per_information5%type;
491: l_desg_vminor per_all_people_f.per_information6%type;
492: l_desg_disab per_all_people_f.per_information7%type;
493: l_naics_no hr_organization_information.org_information8%type;
494: l_naics_no_gre hr_organization_information.org_information8%type;
495: l_hire_date date;
496: l_term_date date;
973: where pcer.context = 'PROMO' and
974: pcer.request_id = 20
975: order by emp_no,promo_dt;
976:
977: l_emp_no per_all_people_f.employee_number%type;
978: l_prev_emp per_all_people_f.employee_number%type;
979: l_promo_dt varchar2(10);
980: l_promo_cnt number;
981: l_output varchar2(1000);
974: pcer.request_id = 20
975: order by emp_no,promo_dt;
976:
977: l_emp_no per_all_people_f.employee_number%type;
978: l_prev_emp per_all_people_f.employee_number%type;
979: l_promo_dt varchar2(10);
980: l_promo_cnt number;
981: l_output varchar2(1000);
982: begin
1023: where pcer.context = 'TMP' and
1024: pcer.request_id = 10
1025: order by emp_no;
1026:
1027: l_emp_no per_all_people_f.employee_number%type;
1028: l_term_cnt number;
1029: l_start_dt varchar2(10);
1030: l_end_dt varchar2(10);
1031: l_term_dt varchar2(10);
1029: l_start_dt varchar2(10);
1030: l_end_dt varchar2(10);
1031: l_term_dt varchar2(10);
1032: l_output varchar2(1000);
1033: l_prev_emp per_all_people_f.employee_number%type;
1034: BEGIN
1035: l_prev_emp := -999;
1036: open c_term_details;
1037: loop
1101: from per_ca_ee_report_lines pcer
1102: where pcer.request_id = 30 and
1103: pcer.context = 'EXCEP'
1104: order by emp_no;
1105: l_emp_no per_all_people_f.employee_number%type;
1106: l_prov varchar2(20);
1107: l_naic varchar2(20);
1108: l_cma varchar2(20);
1109: l_emp_catg varchar2(20);