[Home] [Help]
470:
471: IF template_rec.array_sel_criteria(i) = 'PTY' THEN
472:
473: insert into psp_selection_cardinality_gt(lookup_code, total_count) (
474: select 'PTY', count(distinct ppf.person_id) from per_people_f ppf, per_assignments_f paf where
475: person_type_id in (select TO_NUMBER(criteria_value1) from psp_report_template_details_h prtd where
476: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code ='PTY'
477: and include_exclude_flag='I'
478: and request_id = p_request_id )
1054: IF g_lookup_code = 'PTY' then
1055:
1056: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
1057: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1058: FROM per_people_f ppf,
1059: per_assignments_f paf,
1060: per_assignment_status_types past
1061: WHERE person_type_id IN (SELECT TO_NUMBER(criteria_value1)
1062: FROM psp_report_template_details_h prtd
1812: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1813: FROM per_all_people_f ppf,
1814: per_assignments_f paf,
1815: per_assignment_status_types past
1816: WHERE ppf.person_id IN (select person_id from per_people_f where
1817: business_group_id = p_business_group_id and
1818: effective_start_date <= p_effort_end and
1819: effective_end_date >= p_effort_start)
1820: AND paf.assignment_type = 'E'
1914: WHERE request_id = p_request_id
1915: /* Bug 5087294 : Performance fix replacing not in with not exists */
1916: -- AND person_id NOT IN (SELECT NVL( person_id, 0)
1917: AND NOT EXISTS ( SELECT 1
1918: FROM per_people_f ppf,
1919: psp_report_template_details_h prtd,
1920: per_assignments_f paf,
1921: per_assignment_status_types past
1922: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2704:
2705: DELETE FROM psp_selected_persons_t
2706: WHERE request_id = p_request_id
2707: AND person_id IN (SELECT ppf.person_id
2708: FROM per_people_f ppf,
2709: psp_report_template_details_h prtd,
2710: per_assignments_f paf,
2711: per_assignment_status_types past
2712: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3661: LOOP
3662: IF template_rec.array_sel_criteria(i) = 'PTY' THEN
3663: INSERT INTO psp_selection_cardinality_gt(lookup_code, total_count)
3664: SELECT 'PTY', COUNT(DISTINCT assignment_id)
3665: FROM per_people_f ppf,
3666: per_assignments_f paf
3667: WHERE person_type_id IN (SELECT TO_NUMBER(criteria_value1)
3668: FROM psp_report_template_details_h prtd
3669: WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
4163: IF g_lookup_code = 'PTY' THEN
4164:
4165: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4166: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4167: FROM per_people_f ppf,
4168: per_assignments_f paf,
4169: per_assignment_status_types past
4170: WHERE person_type_id IN (SELECT TO_NUMBER(criteria_value1)
4171: FROM psp_report_template_details_h prtd
4866: WHERE request_id = p_request_id
4867: /* Bug 5087294 : Performance fix replacing not in with not exists */
4868: -- AND person_id NOT IN (SELECT NVL( person_id, 0)
4869: AND NOT EXISTS ( SELECT 1
4870: FROM per_people_f ppf,
4871: psp_report_template_details_h prtd,
4872: per_assignments_f paf
4873: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
4874: AND criteria_lookup_code='PTY'
5379: IF template_sel_criteria.array_sel_criteria(i) = 'PTY' THEN
5380: DELETE FROM psp_selected_persons_t
5381: WHERE request_id = p_request_id
5382: AND person_id IN (SELECT ppf.person_id
5383: FROM per_people_f ppf,
5384: psp_report_template_details_h prtd,
5385: per_assignments_f paf
5386: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5387: AND criteria_lookup_code='PTY'