[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
1804:
1805: ELSIf g_lookup_code='ALL' then
1806:
1807: insert into psp_selected_persons_t(request_id, person_id)(select p_request_id,
1808: person_id from per_people_f where
1809: business_group_id = p_business_group_id and
1810: effective_start_date <= p_effort_end and
1811: effective_end_date >= p_effort_start);
1812: END IF;
1881: WHERE request_id = p_request_id
1882: /* Bug 5087294 : Performance fix replacing not in with not exists */
1883: -- AND person_id NOT IN (SELECT NVL( person_id, 0)
1884: AND NOT EXISTS ( SELECT 1
1885: FROM per_people_f ppf,
1886: psp_report_template_details_h prtd,
1887: per_assignments_f paf,
1888: per_assignment_status_types past
1889: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2671:
2672: DELETE FROM psp_selected_persons_t
2673: WHERE request_id = p_request_id
2674: AND person_id IN (SELECT ppf.person_id
2675: FROM per_people_f ppf,
2676: psp_report_template_details_h prtd,
2677: per_assignments_f paf,
2678: per_assignment_status_types past
2679: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3628: LOOP
3629: IF template_rec.array_sel_criteria(i) = 'PTY' THEN
3630: INSERT INTO psp_selection_cardinality_gt(lookup_code, total_count)
3631: SELECT 'PTY', COUNT(DISTINCT assignment_id)
3632: FROM per_people_f ppf,
3633: per_assignments_f paf
3634: WHERE person_type_id IN (SELECT TO_NUMBER(criteria_value1)
3635: FROM psp_report_template_details_h prtd
3636: WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
4130: IF g_lookup_code = 'PTY' THEN
4131:
4132: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4133: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4134: FROM per_people_f ppf,
4135: per_assignments_f paf,
4136: per_assignment_status_types past
4137: WHERE person_type_id IN (SELECT TO_NUMBER(criteria_value1)
4138: FROM psp_report_template_details_h prtd
4833: WHERE request_id = p_request_id
4834: /* Bug 5087294 : Performance fix replacing not in with not exists */
4835: -- AND person_id NOT IN (SELECT NVL( person_id, 0)
4836: AND NOT EXISTS ( SELECT 1
4837: FROM per_people_f ppf,
4838: psp_report_template_details_h prtd,
4839: per_assignments_f paf
4840: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
4841: AND criteria_lookup_code='PTY'
5346: IF template_sel_criteria.array_sel_criteria(i) = 'PTY' THEN
5347: DELETE FROM psp_selected_persons_t
5348: WHERE request_id = p_request_id
5349: AND person_id IN (SELECT ppf.person_id
5350: FROM per_people_f ppf,
5351: psp_report_template_details_h prtd,
5352: per_assignments_f paf
5353: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5354: AND criteria_lookup_code='PTY'