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