[Home] [Help]
481:
482:
483: sql_range :=
484: 'select distinct asg.person_id
485: from per_assignments_f asg,
486: pay_payrolls_f ppf,
487: pay_payroll_actions ppa
488: where ppa.payroll_action_id = :payroll_action_id
489: and asg.business_group_id = ppa.business_group_id
531: hr_utility.set_location('Entering ' || l_proc, 10);
532:
533: sql_range :=
534: 'select distinct asg.person_id
535: from per_assignments_f asg,
536: pay_payrolls_f ppf,
537: pay_payroll_actions ppa
538: where ppa.payroll_action_id = :payroll_action_id
539: and asg.business_group_id = ppa.business_group_id
584: , p_canonical_start_date date
585: , p_canonical_end_date date) is
586: select asg.person_id person_id,
587: asg.assignment_id assignment_id
588: from per_assignments_f asg,
589: per_assignments_f asg3,
590: --pay_payrolls_f ppf, -- Bug 2608190
591: pay_payroll_actions ppa_arch,
592: per_assignment_extra_info paei,
585: , p_canonical_end_date date) is
586: select asg.person_id person_id,
587: asg.assignment_id assignment_id
588: from per_assignments_f asg,
589: per_assignments_f asg3,
590: --pay_payrolls_f ppf, -- Bug 2608190
591: pay_payroll_actions ppa_arch,
592: per_assignment_extra_info paei,
593: per_periods_of_service pds -- Bug 2654703
610: -- Get the Assignment End Date
611: and asg.effective_end_date =
612: (
613: select max(asg2.effective_end_date)
614: from per_assignments_f asg2
615: , per_assignment_status_types sta
616: where asg2.assignment_id = asg.assignment_id
617: and asg2.assignment_status_type_id = sta.assignment_status_type_id
618: and sta.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
624: and asg3.assignment_id = asg.assignment_id
625: and asg3.effective_start_date =
626: (
627: select min(asg4.effective_start_date)
628: from per_assignments_f asg4
629: , per_assignment_status_types sta
630: where asg4.assignment_id = asg3.assignment_id
631: and asg4.assignment_status_type_id = sta.assignment_status_type_id
632: and sta.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
937: and flv.enabled_flag = 'Y';
938:
939:
940: --Assignment Start Date
941: l_asg_start per_assignments_f.effective_start_date%type;
942:
943: cursor csr_asg_start (p_asg_id number) is
944: select min(asg2.effective_start_date)
945: from per_assignments_f asg2
941: l_asg_start per_assignments_f.effective_start_date%type;
942:
943: cursor csr_asg_start (p_asg_id number) is
944: select min(asg2.effective_start_date)
945: from per_assignments_f asg2
946: , per_assignment_status_types sta
947: where asg2.assignment_id = p_asg_id
948: and asg2.assignment_status_type_id = sta.assignment_status_type_id
949: and sta.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN', 'TERM_ASSIGN'); -- Added 'TERM_ASSIGN' for bug 3869426
1082:
1083:
1084: l_asgid pay_assignment_actions.assignment_id%type;
1085:
1086: l_person_id per_assignments_f.person_id%type;
1087:
1088: l_empl_stat_value varchar2(200);
1089:
1090: l_action_info_id number;
1140: , asg.effective_end_date
1141: into l_person_id
1142: , l_business_group_id
1143: , l_asg_eff_end_date
1144: from per_assignments_f asg
1145: where asg.assignment_id = l_asgid
1146: and asg.effective_end_date =
1147: (
1148: select max(asg2.effective_end_date)
1145: where asg.assignment_id = l_asgid
1146: and asg.effective_end_date =
1147: (
1148: select max(asg2.effective_end_date)
1149: from per_assignments_f asg2
1150: , per_assignment_status_types sta
1151: where asg2.assignment_id = l_asgid --Bug 4768622
1152: and asg2.assignment_status_type_id = sta.assignment_status_type_id
1153: and sta.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN') -- Bug 4306265: Removed 'TERM_ASSIGN') -- Added 'TERM_ASSIGN' for bug 3869426