[Home] [Help]
1014: --
1015: cursor csr_active_home_asgs(p_person_id number) is
1016: select paaf.assignment_id, paaf.object_version_number
1017: from per_all_assignments_f paaf,
1018: per_assignment_status_types past
1019: where paaf.person_id = p_person_id
1020: and l_dpl_rec.start_date between
1021: paaf.effective_start_date and paaf.effective_end_date
1022: and paaf.assignment_type = 'E'
2195: --
2196: cursor csr_susp_home_asgs(p_person_deployment_id number) is
2197: select paaf.assignment_id, paaf.effective_start_date
2198: from per_all_assignments_f paaf,
2199: per_assignment_status_types past
2200: where paaf.person_id = L_DPL_REC.FROM_PERSON_ID
2201: and paaf.effective_start_date = L_DPL_REC.START_DATE
2202: and paaf.assignment_status_type_id = past.assignment_status_type_id
2203: and past.per_system_status = 'SUSP_ASSIGN';
2536: --
2537: cursor csr_susp_home_asgs(p_person_id number) is
2538: select paaf.assignment_id, paaf.object_version_number
2539: from per_all_assignments_f paaf,
2540: per_assignment_status_types past
2541: where paaf.person_id = p_person_id
2542: and L_END_DATE_IN between
2543: paaf.effective_start_date and paaf.effective_end_date
2544: and paaf.assignment_type = 'E'
2546: and past.per_system_status = 'SUSP_ASSIGN'
2547: and exists
2548: (select 1
2549: from per_all_assignments_f paaf1,
2550: per_assignment_status_types past1
2551: where paaf1.assignment_id = paaf.assignment_id
2552: and paaf1.effective_start_date = L_DPL_REC.START_DATE
2553: and paaf1.assignment_status_type_id = past1.assignment_status_type_id
2554: and past1.per_system_status = 'SUSP_ASSIGN')
2554: and past1.per_system_status = 'SUSP_ASSIGN')
2555: and exists
2556: (select 1
2557: from per_all_assignments_f paaf2,
2558: per_assignment_status_types past2
2559: where paaf2.assignment_id = paaf.assignment_id
2560: and paaf2.effective_end_date = L_DPL_REC.START_DATE-1
2561: and paaf2.assignment_status_type_id = past2.assignment_status_type_id
2562: and past2.per_system_status = 'ACTIVE_ASSIGN');