[Home] [Help]
14: g_business_group_id number;
15: g_debug boolean;
16:
17: --
18: -- Set to 'Y' if PQP_GB_SWF_CONTRACT_TYPE = ASG_CAT, dates used from per_all_assignments_f
19: -- Set to 'N' if PQP_GB_SWF_CONTRACT_TYPE = , dates used from pqp_assignment_attributes_f
20: g_pick_from_asg varchar2(10);
21: --
22: g_teacher_sql_str varchar2(3000);
322:
323: if l_qts_grade_seg_name is not null then
324: g_qts_sql_str:=
325: 'select max(hr_general.decode_lookup(''YES_NO'',pcv.pcv_information4))
326: from per_all_assignments_f paf,
327: per_grades pgr,
328: per_grade_definitions pgd ,
329: pqp_configuration_values pcv
330: where paf.business_group_id + 0 = :bg_id
352:
353: if l_qts_route_dff_name = 'GRD' then
354: g_qts_route_sql_str:=
355: 'select max(pcv.pcv_information4)
356: from per_all_assignments_f paf,
357: per_grades pgr,
358: per_grade_definitions pgd ,
359: pqp_configuration_values pcv
360: where paf.business_group_id + 0 = :bg_id
371: and pgd.'||l_qts_route_seg_name||' between pcv.pcv_information2 and pcv_information3))';
372: elsif l_qts_route_dff_name = 'JOB' then
373: g_qts_route_sql_str:=
374: 'select max(pcv.pcv_information4) QT_status
375: from per_all_assignments_f paf,
376: per_jobs job,
377: per_job_definitions jobdef ,
378: pqp_configuration_values pcv
379: where paf.business_group_id + 0 = :bg_id
387: and jobdef.'||l_qts_route_seg_name||' = pcv.pcv_information2';
388: elsif l_qts_route_dff_name = 'POS' then
389: g_qts_route_sql_str:=
390: 'select max(pcv.pcv_information4) QT_status
391: from per_all_assignments_f paf,
392: per_positions pos,
393: per_position_definitions posdef ,
394: pqp_configuration_values pcv
395: where paf.business_group_id + 0 = :bg_id
409:
410: if g_hlta_dff_name = 'JOB' then
411: g_hlta_sql_str:=
412: 'select max(hr_general.decode_lookup(''YES_NO'',pcv.pcv_information3))
413: from per_all_assignments_f paf,
414: per_jobs job,
415: per_job_definitions jobdef ,
416: pqp_configuration_values pcv
417: where paf.business_group_id + 0 = :bg_id
437: and pap.'||l_hlta_seg_name||' = pcv.pcv_information2';
438: elsif g_hlta_dff_name = 'PER_ASSIGNMENTS' then
439: g_hlta_sql_str:=
440: 'select max(hr_general.decode_lookup(''YES_NO'',pcv.pcv_information3))
441: from per_all_assignments_f paf,
442: pqp_configuration_values pcv
443: where ass_attribute_category = '''||l_hlta_context_name||'''
444: and paf.person_id = :person_id
445: and :effective_date between paf.effective_start_date and paf.effective_end_date
454:
455: if g_cont_post_src = 'GRD' then
456: g_cont_post_sql_str:=
457: 'select pcv.pcv_information4
458: from per_all_assignments_f paf,
459: per_grades pgr,
460: per_grade_definitions pgd ,
461: pqp_configuration_values pcv
462: where paf.business_group_id + 0 = :bg_id
473: and pgd.'||l_cont_post_seg||' between pcv.pcv_information2 and pcv_information3))';
474: elsif g_cont_post_src = 'JOB' then
475: g_cont_post_sql_str:=
476: 'select pcv.pcv_information4
477: from per_all_assignments_f paf,
478: per_jobs job,
479: per_job_definitions jobdef ,
480: pqp_configuration_values pcv
481: where paf.business_group_id + 0 = :bg_id
489: and jobdef.'||l_cont_post_seg||' = pcv.pcv_information2';
490: elsif g_cont_post_src = 'POS' then
491: g_cont_post_sql_str:=
492: 'select pcv.pcv_information4
493: from per_all_assignments_f paf,
494: per_positions pos,
495: per_position_definitions posdef ,
496: pqp_configuration_values pcv
497: where paf.business_group_id + 0 = :bg_id
511:
512: if g_origin_dff = 'PER_ASSIGNMENTS' then
513: g_origin_sql_str:=
514: 'select '||l_origin_segment||'
515: from per_all_assignments_f where ass_attribute_category = '''||l_origin_context||'''
516: and assignment_id = :assignment_id
517: and :effective_date between effective_start_date and effective_end_date';
518: elsif g_origin_dff = 'PER_PEOPLE' then
519: g_origin_sql_str:=
529:
530: if g_destination_dff = 'PER_ASSIGNMENTS' then
531: g_destination_sql_str:=
532: 'select '||l_destination_segment||'
533: from per_all_assignments_f where ass_attribute_category = '''||l_destination_context||'''
534: and assignment_id = :assignment_id
535: and :effective_date between effective_start_date and effective_end_date';
536: elsif g_destination_dff = 'PER_PEOPLE' then
537: g_destination_sql_str:=
546:
547: if g_role_src = 'GRD' then
548: g_role_sql_str:=
549: 'select pcv.pcv_information4
550: from per_all_assignments_f paf,
551: per_grades pgr,
552: per_grade_definitions pgd ,
553: pqp_configuration_values pcv
554: where paf.business_group_id + 0 = :bg_id
565: and pgd.'||l_role_segment||' between pcv.pcv_information2 and pcv_information3))';
566: elsif g_role_src = 'JOB' then
567: g_role_sql_str:=
568: 'select pcv.pcv_information4
569: from per_all_assignments_f paf,
570: per_jobs job,
571: per_job_definitions jobdef ,
572: pqp_configuration_values pcv
573: where paf.business_group_id + 0 = :bg_id
581: and jobdef.'||l_role_segment||' = pcv.pcv_information2';
582: elsif g_role_src = 'POS' then
583: g_role_sql_str:=
584: 'select pcv.pcv_information4
585: from per_all_assignments_f paf,
586: per_positions pos,
587: per_position_definitions posdef ,
588: pqp_configuration_values pcv
589: where paf.business_group_id + 0 = :bg_id
603:
604: if g_addl_role_src = 'Extra Position Info DDF' then
605: g_addl_role_sql_str:=
606: 'select pcv.pcv_information4
607: from per_all_assignments_f paa,
608: per_position_extra_info pei,
609: pqp_configuration_values pcv
610: where paa.assignment_id = :p_assignment_id
611: and pei.position_id = paa.position_id
616: and :effective_date between paa.effective_start_date and paa.effective_end_date';
617: elsif g_addl_role_src = 'Extra Job Info DDF' then
618: g_addl_role_sql_str:=
619: 'select pcv.pcv_information4
620: from per_all_assignments_f paa,
621: per_job_extra_info jei,
622: pqp_configuration_values pcv
623: where paa.assignment_id = :p_assignment_id
624: and jei.job_id = paa.job_id
766: cursor csr_check_action is
767: select min(assignment_action_id)
768: from pay_assignment_actions
769: where assignment_id in (select assignment_id
770: from per_all_assignments_f
771: where person_id = p_person_id)
772: and payroll_action_id = p_pactid;
773:
774:
819: cursor csr_asg (p_asg_set_id number)
820: is
821: select distinct asg.assignment_id,
822: asg.person_id
823: from per_all_assignments_f asg,
824: hr_location_extra_info hlei,
825: per_people_extra_info pei,
826: (select distinct min(asg1.effective_start_date) over( partition by assignment_id) effective_start_date,
827: max(asg1.effective_end_date) over( partition by assignment_id) effective_end_date,
826: (select distinct min(asg1.effective_start_date) over( partition by assignment_id) effective_start_date,
827: max(asg1.effective_end_date) over( partition by assignment_id) effective_end_date,
828: first_value(location_id)over( partition by assignment_id order by asg1.effective_end_date desc ) location_id,
829: asg1.assignment_id
830: from per_all_assignments_f asg1,
831: per_assignment_status_types pas
832: where asg1.assignment_status_type_id = pas.assignment_status_type_id
833: and pas.per_system_status = 'ACTIVE_ASSIGN') min_max
834: where asg.person_id between stperson and endperson
904:
905: cursor csr_contract (p_asg_set_id number)
906: is
907: select distinct asg.assignment_id,asg.person_id
908: from per_all_assignments_f asg,
909: pqp_assignment_attributes_f att,
910: hr_location_extra_info hlei,
911: per_people_extra_info pei,
912: (select min(att1.effective_start_date) effective_start_date,
916: group by assignment_id ) min_max,
917:
918: (select first_value(location_id)over( partition by assignment_id order by asg1.effective_end_date desc ) location_id,
919: asg1.assignment_id
920: from per_all_assignments_f asg1,
921: per_assignment_status_types pas
922: where asg1.assignment_status_type_id = pas.assignment_status_type_id
923: and pas.per_system_status = 'ACTIVE_ASSIGN') loc
924: where asg.person_id between stperson and endperson
1220: per_information1 ethnic_code,
1221: pap.date_of_birth date_of_birth,
1222: to_char(pap.date_of_birth,'YYYY-MM-DD') dob_dcsf
1223: from pay_assignment_actions act,
1224: per_all_assignments_f paa,
1225: per_all_people_f pap
1226: where act.assignment_action_id = p_assactid
1227: and act.assignment_id = paa.assignment_id
1228: and paa.person_id = pap.person_id
1249: --
1250: cursor csr_cont_eff_date_asg is
1251: select max(effective_end_date)
1252: from pay_assignment_actions act,
1253: per_all_assignments_f paa,
1254: per_assignment_status_types pas
1255: where act.assignment_action_id = p_assactid
1256: and act.assignment_id = paa.assignment_id
1257: and paa.assignment_status_type_id = pas.assignment_status_type_id
1266:
1267: cursor get_person_id(p_effective_date date) is
1268: select ppf.person_id,ppf.employee_number
1269: from pay_assignment_actions act,
1270: per_all_assignments_f paa,
1271: per_all_people_f ppf
1272: where act.assignment_action_id = p_assactid
1273: and act.assignment_id = paa.assignment_id
1274: and ppf.person_id = paa.person_id
1276: and p_effective_date between ppf.effective_start_date and ppf.effective_end_date;
1277:
1278: cursor get_estab_no( p_assignment_id in number , p_effective_date in date) is
1279: select hlei.lei_information2
1280: from per_all_assignments_f asg,
1281: hr_location_extra_info hlei
1282: where asg.business_group_id + 0 = g_business_group_id
1283: and asg.location_id = hlei.location_id
1284: and asg.assignment_id = p_assignment_id
1886:
1887: begin
1888: select pexi.eei_information1,to_char(nvl(sum(prrv.result_value),0),'fm999999.00')
1889: bulk collect into p_addl_payments
1890: from per_all_assignments_f paf,
1891: pay_element_entries_f pee,
1892: pay_element_type_extra_info pexi,
1893: pay_run_results prr,
1894: pay_input_values_f piv,
1940: cursor get_assignment_details is
1941: select paa.assignment_number assignment_number,
1942: paa.employee_category employee_cat,
1943: paa.employment_category assignment_cat
1944: from per_all_assignments_f paa
1945: where paa.assignment_id = p_assignment_id
1946: and p_effective_date between paa.effective_start_date and paa.effective_end_date;
1947:
1948: cursor role_dcsf(p_role in varchar2) is
1953: and pcv_information2 = p_role;
1954:
1955:
1956: l_main_role hr_lookups.lookup_code%type;
1957: l_assignment_number per_all_assignments_f.assignment_number%type;
1958: l_asg_cat per_all_assignments_f.employment_category%type;
1959: l_employee_cat per_all_assignments_f.employee_category%type;
1960: l_role_tab_idx pls_integer;
1961:
1954:
1955:
1956: l_main_role hr_lookups.lookup_code%type;
1957: l_assignment_number per_all_assignments_f.assignment_number%type;
1958: l_asg_cat per_all_assignments_f.employment_category%type;
1959: l_employee_cat per_all_assignments_f.employee_category%type;
1960: l_role_tab_idx pls_integer;
1961:
1962:
1955:
1956: l_main_role hr_lookups.lookup_code%type;
1957: l_assignment_number per_all_assignments_f.assignment_number%type;
1958: l_asg_cat per_all_assignments_f.employment_category%type;
1959: l_employee_cat per_all_assignments_f.employee_category%type;
1960: l_role_tab_idx pls_integer;
1961:
1962:
1963: begin
2055: and pcv.business_group_id = g_business_group_id;
2056:
2057: cursor get_asg_contract_details is
2058: select att.contract_type,work_pattern
2059: from per_all_assignments_f paa,
2060: pqp_assignment_attributes_f att
2061: where paa.assignment_id = p_assignment_id
2062: and paa.assignment_id = att.assignment_id
2063: and p_effective_date between att.effective_start_date and att.effective_end_date
2064: and p_effective_date between paa.effective_start_date and paa.effective_end_date;
2065:
2066: cursor get_asg_details is
2067: select paa.assignment_number,paa.person_id,paa.frequency,paa.normal_hours
2068: from per_all_assignments_f paa
2069: where paa.assignment_id = p_assignment_id
2070: and p_effective_date between paa.effective_start_date and paa.effective_end_date;
2071:
2072: cursor get_work_pattern(p_work_pattern in varchar2) is
2118:
2119: l_proc constant varchar2(50) := g_package || ' fetch_hours_details';
2120: l_hrs_details_rec get_hrs_details%rowtype;
2121: l_contract_type pqp_assignment_attributes_f.contract_type%type;
2122: l_assignment_number per_all_assignments_f.assignment_number%type;
2123: l_hrs_src pqp_configuration_values.pcv_information1%type;
2124: l_hours_per_week varchar2(20);
2125: l_weeks_per_yr varchar2(20);
2126: l_work_pattern pqp_assignment_attributes_f.work_pattern%type;
2123: l_hrs_src pqp_configuration_values.pcv_information1%type;
2124: l_hours_per_week varchar2(20);
2125: l_weeks_per_yr varchar2(20);
2126: l_work_pattern pqp_assignment_attributes_f.work_pattern%type;
2127: l_frequency per_all_assignments_f.frequency%type;
2128: l_no_of_hrs per_all_assignments_f.normal_hours%type;
2129: l_person_id per_all_assignments_f.person_id%type;
2130: l_no_of_days_per_wk number;
2131: l_fte_hrs varchar2(1000);
2124: l_hours_per_week varchar2(20);
2125: l_weeks_per_yr varchar2(20);
2126: l_work_pattern pqp_assignment_attributes_f.work_pattern%type;
2127: l_frequency per_all_assignments_f.frequency%type;
2128: l_no_of_hrs per_all_assignments_f.normal_hours%type;
2129: l_person_id per_all_assignments_f.person_id%type;
2130: l_no_of_days_per_wk number;
2131: l_fte_hrs varchar2(1000);
2132: l_fte_src varchar2(30);
2125: l_weeks_per_yr varchar2(20);
2126: l_work_pattern pqp_assignment_attributes_f.work_pattern%type;
2127: l_frequency per_all_assignments_f.frequency%type;
2128: l_no_of_hrs per_all_assignments_f.normal_hours%type;
2129: l_person_id per_all_assignments_f.person_id%type;
2130: l_no_of_days_per_wk number;
2131: l_fte_hrs varchar2(1000);
2132: l_fte_src varchar2(30);
2133: l_fte_formula_id number;
2695: paa.employment_category contract_agg_type,
2696: paa.employee_category employee_cat,
2697: paa.employment_category assignment_cat
2698: from pay_assignment_actions act,
2699: per_all_assignments_f paa
2700: where act.assignment_action_id = p_assactid
2701: and act.assignment_id = paa.assignment_id
2702: and p_effective_date between paa.effective_start_date and paa.effective_end_date;
2703:
2707: to_char(min(paa.effective_start_date),'YYYY-MM-DD') contract_st_date_dcsf,
2708: decode(to_char(max(paa.effective_end_date) ,'YYYY-MM-DD'),'4712-12-31',null,
2709: to_char(max(paa.effective_end_date) ,'YYYY-MM-DD'))contract_end_date_dcsf,
2710: to_char(min(paa.effective_start_date) ,'YYYY-MM-DD') date_of_arrival_dcsf
2711: from per_all_assignments_f paa,
2712: per_assignment_status_types pas
2713: where paa.assignment_status_type_id = pas.assignment_status_type_id
2714: and pas.per_system_status = 'ACTIVE_ASSIGN'
2715: and paa.assignment_id = p_assignment_id;
2719: paa.assignment_number assignment_number,
2720: att.contract_type contract_agg_type,
2721: paa.employee_category employee_cat,
2722: paa.employment_category assignment_cat
2723: from per_all_assignments_f paa,
2724: pay_assignment_actions act,
2725: pqp_assignment_attributes_f att
2726: where act.assignment_action_id = p_assactid
2727: and act.assignment_id = att.assignment_id
2737: decode(to_char(max(att.effective_end_date) ,'YYYY-MM-DD'),'4712-12-31',null,
2738: to_char(max(att.effective_end_date) ,'YYYY-MM-DD'))contract_end_date_dcsf,
2739: to_char(min(paa.effective_start_date) ,'YYYY-MM-DD') date_of_arrival_dcsf
2740: from pqp_assignment_attributes_f att,
2741: per_all_assignments_f paa
2742: where att.assignment_id = p_assignment_id
2743: and paa.assignment_id = att.assignment_id;
2744:
2745: cursor get_date_of_arrival_src is
2787: where pcv.pcv_information_category = 'PQP_GB_SWF_CONTRACT_TYPE'
2788: and pcv_information4 = p_pqp_cont_type
2789: and pcv.business_group_id = g_business_group_id;
2790:
2791: cursor get_asg_contract_type_dcsf(p_cont_type in per_all_assignments_f.employment_category%type ) is
2792: select pcv_information5
2793: from pqp_configuration_values pcv
2794: where pcv.pcv_information_category = 'PQP_GB_SWF_CONTRACT_TYPE'
2795: and pcv_information3 = p_cont_type
3600: select ps.parent_spine_id
3601: from per_grade_spines_f grs,
3602: per_grades gdt,
3603: per_parent_spines ps,
3604: per_all_assignments_f asg
3605: where grs.grade_id = gdt.grade_id
3606: and grs.parent_spine_id = ps.parent_spine_id
3607: and asg.grade_id = grs.grade_id
3608: and asg.assignment_id = p_assignment_id
3721: close salary_rate;
3722:
3723: if l_regional_spine_context = 'GRD' then
3724: l_regional_spine_sql_str :='select pcv.pcv_information4
3725: from per_all_assignments_f paf,
3726: per_grades pgr,
3727: per_grade_definitions pgd ,
3728: pqp_configuration_values pcv
3729: where paf.business_group_id + 0 = :bg_id
3959: l_pactid number;
3960: l_contract_type varchar2(50);
3961: l_do_not_process_further boolean;
3962: -- Type 4
3963: l_assignment_id per_all_assignments_f.assignment_id%type;
3964: l_employment_category per_all_assignments_f.employment_category%type;
3965: l_assignment_number per_all_assignments_f.assignment_number%type;
3966: l_arch_role boolean :=true;
3967: l_epsy varchar2(1) := 'N';
3960: l_contract_type varchar2(50);
3961: l_do_not_process_further boolean;
3962: -- Type 4
3963: l_assignment_id per_all_assignments_f.assignment_id%type;
3964: l_employment_category per_all_assignments_f.employment_category%type;
3965: l_assignment_number per_all_assignments_f.assignment_number%type;
3966: l_arch_role boolean :=true;
3967: l_epsy varchar2(1) := 'N';
3968: p_role_tab addl_role_tab;
3961: l_do_not_process_further boolean;
3962: -- Type 4
3963: l_assignment_id per_all_assignments_f.assignment_id%type;
3964: l_employment_category per_all_assignments_f.employment_category%type;
3965: l_assignment_number per_all_assignments_f.assignment_number%type;
3966: l_arch_role boolean :=true;
3967: l_epsy varchar2(1) := 'N';
3968: p_role_tab addl_role_tab;
3969: l_temp_or_perm varchar2(10);
3994:
3995: cursor csr_asg_no(p_census_day in date) is
3996: select paa.assignment_id , paa.employment_category, paa.assignment_number
3997: from pay_assignment_actions act,
3998: per_all_assignments_f paa,
3999: per_assignment_status_types pas
4000: where act.assignment_action_id = p_assactid
4001: and act.assignment_id = paa.assignment_id
4002: and p_census_day between paa.effective_start_date and paa.effective_end_date
4272: peo.national_identifier ni_no,
4273: paa.assignment_action_id asg_act_id
4274: from pay_payroll_actions pay,
4275: pay_assignment_actions paa,
4276: per_all_assignments_f paf,
4277: per_all_people_f peo,
4278: (select max(effective_end_date) effective_date,assignment_id
4279: from per_all_assignments_f
4280: group by assignment_id) max_eff_date
4275: pay_assignment_actions paa,
4276: per_all_assignments_f paf,
4277: per_all_people_f peo,
4278: (select max(effective_end_date) effective_date,assignment_id
4279: from per_all_assignments_f
4280: group by assignment_id) max_eff_date
4281: where pay.payroll_action_id = pactid
4282: and paa.payroll_action_id = pay.payroll_action_id
4283: and paf.assignment_id = paa.assignment_id
4319: peo.national_identifier ni_no,
4320: paa.assignment_action_id asg_act_id
4321: from pay_payroll_actions pay,
4322: pay_assignment_actions paa,
4323: per_all_assignments_f paf,
4324: per_all_people_f peo,
4325: (select max(effective_end_date) effective_date,assignment_id
4326: from per_all_assignments_f
4327: group by assignment_id) max_eff_date
4322: pay_assignment_actions paa,
4323: per_all_assignments_f paf,
4324: per_all_people_f peo,
4325: (select max(effective_end_date) effective_date,assignment_id
4326: from per_all_assignments_f
4327: group by assignment_id) max_eff_date
4328: where pay.payroll_action_id = pactid
4329: and paa.payroll_action_id = pay.payroll_action_id
4330: and paf.assignment_id = paa.assignment_id