[Home] [Help]
462: AND 'x' = ( select 'x' from per_all_people_f ppf
463: where asg.person_id = ppf.person_id
464: and p_effective_date between ppf.effective_start_date and ppf.effective_end_date
465: and ('Y' = decode( p_plan_rec.assignment_types_code, 'E', ppf.current_employee_flag, 'C',
466: ppf.current_npw_flag, 'EC', (select 'Y' from dual where
467: ppf.current_employee_flag = 'Y' or ppf.current_npw_flag = 'Y') )) )
468: ) o
469: CONNECT BY o.supervisor_id = PRIOR o.person_id
470: AND LEVEL <= NVL(p_plan_rec.hierarchy_levels, LEVEL) + 1
488: AND 'x' = ( select 'x' from per_all_people_f ppf
489: where asg.person_id = ppf.person_id
490: and p_effective_date between ppf.effective_start_date and ppf.effective_end_date
491: and ('Y' = decode( p_plan_rec.assignment_types_code, 'E', ppf.current_employee_flag, 'C',
492: ppf.current_npw_flag, 'EC', (select 'Y' from dual where
493: ppf.current_employee_flag = 'Y' or ppf.current_npw_flag = 'Y') )) )
494: ) o
495: CONNECT BY o.supervisor_assignment_id = PRIOR o.assignment_id
496: AND LEVEL <= NVL(p_plan_rec.hierarchy_levels, LEVEL) + 1
517: AND 'x' = ( select 'x' from per_all_people_f ppf
518: where asg.person_id = ppf.person_id
519: and p_effective_date between ppf.effective_start_date and ppf.effective_end_date
520: and ('Y' = decode( p_plan_rec.assignment_types_code, 'E', ppf.current_employee_flag, 'C',
521: ppf.current_npw_flag, 'EC', (select 'Y' from dual where
522: ppf.current_employee_flag = 'Y' or ppf.current_npw_flag = 'Y') )) )
523: AND asg.organization_id in
524: (select o.organization_id_child from
525: (SELECT o.organization_id_child
529: AND LEVEL <= NVL(p_plan_rec.hierarchy_levels, LEVEL)
530: START WITH o.organization_id_parent = p_plan_rec.top_organization_id
531: AND o.org_structure_version_id = p_plan_rec.org_structure_version_id
532: UNION
533: SELECT p_plan_rec.top_organization_id organization_id_child from dual) o, hr_organization_units org
534: where o.organization_id_child = org.organization_id
535: and p_effective_date between org.date_from and nvl(org.date_to, p_effective_date));
536:
537:
555: AND 'x' = ( select 'x' from per_all_people_f ppf
556: where asg.person_id = ppf.person_id
557: and p_effective_date between ppf.effective_start_date and ppf.effective_end_date
558: and ('Y' = decode( p_plan_rec.assignment_types_code, 'E', ppf.current_employee_flag, 'C',
559: ppf.current_npw_flag, 'EC', (select 'Y' from dual where
560: ppf.current_employee_flag = 'Y' or ppf.current_npw_flag = 'Y') )) )
561: AND ( asg.position_id in
562: (select o.subordinate_position_id from (SELECT p.subordinate_position_id
563: FROM per_pos_structure_elements p
568: AND p.pos_structure_version_id = p_plan_rec.pos_structure_version_id) o, per_positions pos
569: where o.subordinate_position_id = pos.position_id
570: and p_effective_date between pos.date_effective and nvl(pos.date_end,p_effective_date)
571: UNION
572: select p_plan_rec.top_position_id subordinate_position_id from dual));
573:
574: CURSOR csr_apprl_periods(p_pln_id per_perf_mgmt_plans.plan_id%TYPE) IS
575: select prds.appraisal_period_id, prds.appraisal_template_id, templ.business_group_id
576: from per_appraisal_periods prds, per_appraisal_templates templ
3031: -- Derive the item key
3032: --
3033: select hr_workflow_item_key_s.nextval
3034: into l_item_key
3035: from dual;
3036:
3037: --
3038: -- Derive the other values based on plan action
3039: --
5817: --
5818: --
5819: CURSOR csr_apprs_exist(p_plan_id IN NUMBER) IS
5820: SELECT 'Y'
5821: FROM dual
5822: WHERE EXISTS (SELECT 'x'
5823: FROM per_appraisals
5824: WHERE plan_id = p_plan_id);
5825: l_chk_exists VARCHAR2(1);