[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 )
487:
488: ELSIF template_rec.array_sel_criteria(i) ='EMP' THEN
489:
490: insert into psp_selection_cardinality_gt(lookup_code, total_count) (
491: select 'EMP', count(distinct ppf.person_id) from per_all_people_f ppf, per_assignments_f paf where
492: ppf.person_id in (select TO_NUMBER(criteria_value1) from psp_report_template_details_h prtd where
493: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code='EMP' and
494: include_exclude_flag='I'
495: and request_id = p_request_id )
666:
667: ELSIF template_rec.array_sel_criteria(i)='PAY' THEN
668:
669: insert into psp_selection_cardinality_gt(lookup_code, total_count)(
670: select 'PAY', count(distinct person_id) from per_assignments_f paf where
671: payroll_id in (select TO_NUMBER(criteria_value1) from psp_report_template_details_h prtd where
672: criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code='PAY'
673: and include_exclude_flag='I' and request_id = p_request_id
674: )
679:
680: ELSIF template_rec.array_sel_criteria(i)='LOC' THEN
681:
682: insert into psp_selection_cardinality_gt(lookup_code, total_count)(
683: select 'LOC', count(distinct person_id) from per_assignments_f paf where
684: location_id in (select TO_NUMBER(criteria_value1) from psp_report_template_details_h prtd where
685: criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code='LOC'
686: and include_exclude_flag='I' and request_id = p_request_id
687: )
692:
693: ELSIF template_rec.array_sel_criteria(i)='ORG' THEN
694:
695: insert into psp_selection_cardinality_gt(lookup_code, total_count)(
696: select 'ORG', count(distinct person_id) from per_assignments_f paf where
697: organization_id in (select TO_NUMBER(criteria_value1) from psp_report_template_details_h prtd
698: where
699: criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code='ORG'
700: and include_exclude_flag='I' and request_id = p_request_id
705:
706: ELSIF template_rec.array_sel_criteria(i)='JOB' THEN
707:
708: insert into psp_selection_cardinality_gt(lookup_code, total_count)(
709: select 'JOB', count(distinct person_id) from per_assignments_f paf where
710: job_id in (select TO_NUMBER(criteria_value1) from psp_report_template_details_h prtd where
711: criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code='JOB'
712: and include_exclude_flag='I' and request_id=p_request_id
713: )
718:
719: ELSIF template_rec.array_sel_criteria(i)='POS' THEN
720:
721: insert into psp_selection_cardinality_gt(lookup_code, total_count) (
722: select 'POS', count(distinct person_id) from per_assignments_f paf where
723: position_id in (select TO_NUMBER(criteria_value1) from psp_report_template_details_h prtd where
724: criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code='POS'
725: and include_exclude_flag='I' and request_id = p_request_id
726: )
731:
732: ELSIF template_rec.array_sel_criteria(i)='ASS' THEN
733:
734: insert into psp_selection_cardinality_gt(lookup_code, total_count) (
735: select 'ASS', count(distinct person_id) from per_assignments_f paf where
736: assignment_status_type_id in (select TO_NUMBER(criteria_value1) from psp_report_template_details_h
737: prtd where
738: criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code='ASS'
739: and include_exclude_flag='I' and request_id = p_request_id
745:
746: ELSIF template_rec.array_sel_criteria(i)='CST' THEN
747:
748: insert into psp_selection_cardinality_gt(lookup_code, total_count) (
749: select 'CST', count(distinct paf.person_id) from per_assignments_f paf
750: , pay_payrolls_f ppf where
751: ppf.payroll_id = paf.payroll_id and
752: ppf.consolidation_set_id in
753: (select TO_NUMBER(criteria_value1) from psp_report_template_details_h
903:
904: IF l_sql_string is not null then
905: g_exec_string := 'insert into psp_selection_cardinality_gt(lookup_code, total_count)
906: (select '|| '''' || 'PPG' || ''''||' , count(person_id)
907: from per_assignments_f paf,
908: pay_people_groups ppg
909: where paf.people_group_id= ppg.people_group_id
910: AND paf.assignment_type = ''' || 'E' || '''
911: and paf.effective_end_date >= :p_effort_Start and
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
1063: WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
1092:
1093: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
1094: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1095: FROM per_all_people_f ppf,
1096: per_assignments_f paf,
1097: per_assignment_status_types past
1098: WHERE ppf.person_id IN (SELECT TO_NUMBER(criteria_value1)
1099: FROM psp_report_template_details_h prtd
1100: WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
1129:
1130: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
1131: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1132: FROM per_all_people_f ppf,
1133: per_assignments_f paf,
1134: per_assignment_status_types past
1135: WHERE supervisor_id IN (SELECT TO_NUMBER(criteria_value1)
1136: FROM psp_report_template_details_h prtd
1137: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
1170: select distinct p_request_id, psl.person_id , paf.assignment_id
1171: FROM psp_summary_lines psl,
1172: psp_report_template_details_h prtd ,
1173: per_time_periods ptp,
1174: per_assignments_f paf
1175: WHERE
1176: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
1177: prtd.criteria_lookup_code='AWD' AND
1178: prtd.include_exclude_flag='I' AND
1213: FROM psp_summary_lines psl,
1214: psp_report_template_details_h prtd ,
1215: gms_awards_all gaa ,
1216: per_time_periods ptp,
1217: per_assignments_f paf
1218: WHERE psl.award_id = gaa.award_id AND
1219: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
1220: prtd.criteria_lookup_code='ATY' AND
1221: prtd.include_exclude_flag='I' AND
1256: FROM psp_summary_lines psl,
1257: psp_report_template_details_h prtd ,
1258: pa_projects_all ppa ,
1259: per_time_periods ptp,
1260: per_assignments_f paf
1261: WHERE psl.project_id = ppa.project_id AND
1262: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
1263: prtd.criteria_lookup_code='PRT' AND
1264: prtd.include_exclude_flag='I' AND
1303: select distinct p_request_id, psl.person_id, paf.assignment_id
1304: FROM psp_summary_lines psl,
1305: psp_report_template_details_h prtd ,
1306: per_time_periods ptp,
1307: per_assignments_f paf
1308: WHERE
1309: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
1310: prtd.criteria_lookup_code='PRJ' AND
1311: prtd.include_exclude_flag='I' AND
1342:
1343: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
1344: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1345: FROM per_all_people_f ppf,
1346: per_assignments_f paf,
1347: per_assignment_status_types past
1348: WHERE payroll_id IN (SELECT TO_NUMBER(criteria_value1)
1349: FROM psp_report_template_details_h prtd
1350: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
1379:
1380: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
1381: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1382: FROM per_all_people_f ppf,
1383: per_assignments_f paf,
1384: per_assignment_status_types past
1385: WHERE location_id IN (SELECT TO_NUMBER(criteria_value1)
1386: FROM psp_report_template_details_h prtd
1387: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
1416:
1417: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
1418: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1419: FROM per_all_people_f ppf,
1420: per_assignments_f paf,
1421: per_assignment_status_types past
1422: WHERE organization_id IN (SELECT TO_NUMBER(criteria_value1)
1423: FROM psp_report_template_details_h prtd
1424: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
1454: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
1455: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1456: FROM pay_payrolls_f pp,
1457: per_all_people_f ppf,
1458: per_assignments_f paf,
1459: per_assignment_status_types past
1460: WHERE pp.payroll_id = paf.payroll_id
1461: AND pp.consolidation_set_id IN (SELECT TO_NUMBER(criteria_value1)
1462: FROM psp_report_template_details_h prtd
1547:
1548: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
1549: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1550: FROM per_all_people_f ppf,
1551: per_assignments_f paf,
1552: per_assignment_status_types past
1553: WHERE job_id IN (SELECT TO_NUMBER(criteria_value1)
1554: FROM psp_report_template_details_h prtd
1555: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
1584:
1585: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
1586: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1587: FROM per_all_people_f ppf,
1588: per_assignments_f paf,
1589: per_assignment_status_types past
1590: WHERE position_id IN (SELECT TO_NUMBER(criteria_value1)
1591: FROM psp_report_template_details_h prtd
1592: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
1621:
1622: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
1623: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1624: FROM per_all_people_f ppf,
1625: per_assignments_f paf,
1626: per_assignment_status_types past
1627: WHERE paf.assignment_status_type_id IN (SELECT TO_NUMBER(criteria_value1)
1628: FROM psp_report_template_details_h prtd
1629: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
1690: if l_sql_string is not null then
1691: g_exec_string := 'insert into psp_selected_persons_t
1692: (request_id, person_id, assignment_id)
1693: (select :request_id , paf.person_id, paf.assignment_id
1694: from per_assignments_f paf,
1695: pay_people_groups ppg,
1696: per_all_people_f ppf,
1697: per_assignment_status_types past
1698: where paf.people_group_id= ppg.people_group_id
1810:
1811: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
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
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'
1923: AND criteria_lookup_code='PTY'
1924: AND paf.person_id = ppf.person_id
1956: /* Bug 5087294 : Performance fix replacing not in with not exists */
1957: -- AND person_id NOT IN (SELECT NVL(person_id,0)
1958: AND NOT EXISTS ( SELECT 1
1959: FROM per_all_people_f ppf,
1960: per_assignments_f paf,
1961: per_assignment_status_types past
1962: WHERE ppf.person_id IN (SELECT TO_NUMBER(criteria_value1)
1963: FROM psp_report_template_details_h prtd
1964: WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
2187: WHERE request_id = p_request_id
2188: /* Bug 5087294 : Performance fix replacing not in with not exists */
2189: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2190: AND NOT EXISTS ( SELECT 1
2191: FROM per_assignments_f paf,
2192: psp_report_template_details_h prtd,
2193: per_assignment_status_types past
2194: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2195: AND criteria_lookup_code='PAY'
2226: WHERE request_id = p_request_id
2227: /* Bug 5087294 : Performance fix replacing not in with not exists */
2228: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2229: AND NOT EXISTS ( SELECT 1
2230: FROM per_assignments_f paf ,
2231: psp_report_template_details_h prtd,
2232: per_assignment_status_types past
2233: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2234: AND criteria_lookup_code='LOC'
2262: DELETE FROM psp_selected_persons_t pspt
2263: WHERE request_id = p_request_id
2264: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2265: AND NOT EXISTS ( SELECT 1
2266: FROM per_assignments_f paf ,
2267: psp_report_template_details_h prtd,
2268: per_assignment_status_types past
2269: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2270: AND criteria_lookup_code='ORG'
2299: WHERE request_id = p_request_id
2300: /* Bug 5087294 : Performance fix replacing not in with not exists */
2301: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2302: AND NOT EXISTS ( SELECT 1
2303: FROM per_assignments_f paf,
2304: pay_payrolls_f ppf,
2305: per_assignment_status_types past
2306: WHERE ppf.payroll_id = paf.payroll_id
2307: AND ppf.consolidation_set_id IN (SELECT TO_NUMBER(criteria_value1)
2393: WHERE request_id = p_request_id
2394: /* Bug 5087294 : Performance fix replacing not in with not exists */
2395: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2396: AND NOT EXISTS ( SELECT 1
2397: FROM per_assignments_f paf ,
2398: psp_report_template_details_h prtd,
2399: per_assignment_status_types past
2400: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2401: AND criteria_lookup_code='JOB'
2430: WHERE request_id = p_request_id
2431: /* Bug 5087294 : Performance fix replacing not in with not exists */
2432: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2433: AND NOT EXISTS ( SELECT 1
2434: FROM per_assignments_f paf ,
2435: psp_report_template_details_h prtd,
2436: per_assignment_status_types past
2437: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2438: AND criteria_lookup_code='POS'
2469: WHERE request_id = p_request_id
2470: /* Bug 5087294 : Performance fix replacing not in with not exists */
2471: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2472: AND NOT EXISTS ( SELECT 1
2473: FROM per_assignments_f paf ,
2474: psp_report_template_details_h prtd,
2475: per_assignment_status_types past
2476: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2477: AND criteria_lookup_code='ASS'
2545: --- replaced non-performant delete stmnt for 4429787
2546:
2547: g_exec_string := 'delete from psp_selected_persons_t sel where request_id = :request_id
2548: AND NOT EXISTS
2549: (SELECT 1 FROM per_assignments_f paf, pay_people_groups ppg, per_assignment_status_types past
2550: WHERE paf.people_group_id= ppg.people_group_id
2551: AND paf.assignment_type = ''' || 'E' || '''
2552: AND paf.effective_end_date >= :p_effort_Start AND
2553: paf.effective_start_date <= :p_effort_end
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'
2713: AND criteria_lookup_code='PTY'
2714: AND paf.person_id = ppf.person_id
2744: DELETE FROM psp_selected_persons_t
2745: WHERE request_id = p_request_id
2746: AND person_id IN (SELECT DISTINCT ppf.person_id
2747: FROM per_all_people_f ppf,
2748: per_assignments_f paf,
2749: per_assignment_status_types past
2750: WHERE ppf.person_id IN (SELECT TO_NUMBER(criteria_value1)
2751: FROM psp_report_template_details_h prtd
2752: WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
2968:
2969: DELETE FROM psp_selected_persons_t
2970: WHERE request_id = p_request_id
2971: AND person_id IN (SELECT person_id
2972: FROM per_assignments_f paf,
2973: psp_report_template_details_h prtd,
2974: per_assignment_status_types past
2975: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2976: AND criteria_lookup_code='PAY'
3003:
3004: DELETE FROM psp_selected_persons_t
3005: WHERE request_id = p_request_id
3006: AND person_id IN (SELECT person_id
3007: FROM per_assignments_f paf,
3008: psp_report_template_details_h prtd,
3009: per_assignment_status_types past
3010: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3011: AND criteria_lookup_code='LOC'
3038:
3039: DELETE FROM psp_selected_persons_t
3040: WHERE request_id = p_request_id
3041: AND person_id IN (SELECT person_id
3042: FROM per_assignments_f paf,
3043: psp_report_template_details_h prtd,
3044: per_assignment_status_types past
3045: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3046: AND criteria_lookup_code='ORG'
3077:
3078: DELETE FROM psp_selected_persons_t
3079: WHERE request_id = p_request_id
3080: AND person_id IN (SELECT person_id
3081: FROM per_assignments_f paf,
3082: psp_report_template_details_h prtd,
3083: per_assignment_status_types past
3084: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3085: AND criteria_lookup_code='JOB'
3112:
3113: DELETE FROM psp_selected_persons_t
3114: WHERE request_id = p_request_id
3115: AND person_id IN (SELECT person_id
3116: FROM per_assignments_f paf,
3117: psp_report_template_details_h prtd,
3118: per_assignment_status_types past
3119: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3120: AND criteria_lookup_code='POS'
3146:
3147: DELETE FROM psp_selected_persons_t
3148: WHERE request_id = p_request_id
3149: AND person_id IN (SELECT person_id
3150: FROM per_assignments_f paf,
3151: psp_report_template_details_h prtd,
3152: per_assignment_status_types past
3153: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3154: AND criteria_lookup_code='ASS'
3181:
3182: DELETE FROM psp_selected_persons_t
3183: WHERE request_id = p_request_id
3184: AND person_id IN (SELECT NVL(person_id,0)
3185: FROM per_assignments_f paf,
3186: pay_payrolls_f ppf,
3187: per_assignment_status_types past
3188: WHERE ppf.payroll_id = paf.payroll_id
3189: AND ppf.consolidation_set_id IN (SELECT TO_NUMBER(criteria_value1)
3303:
3304: IF l_sql_string is not null then
3305:
3306: g_exec_string := 'delete from psp_selected_persons_t where person_id
3307: in (select person_id from per_assignments_f paf,
3308: pay_people_groups ppg , per_assignment_status_types past
3309: where paf.people_group_id= ppg.people_group_id
3310: AND paf.assignment_type = ''' || 'E' || '''
3311: and paf.effective_end_date >= :p_effort_Start and
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'
3670: AND criteria_lookup_code ='PTY'
3857: ELSIF template_rec.array_sel_criteria(i)='PAY' THEN
3858: INSERT INTO psp_selection_cardinality_gt
3859: (lookup_code, total_count)
3860: SELECT 'PAY', COUNT(DISTINCT assignment_id)
3861: FROM per_assignments_f paf
3862: WHERE payroll_id IN (SELECT TO_NUMBER(criteria_value1)
3863: FROM psp_report_template_details_h prtd
3864: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3865: AND criteria_lookup_code='PAY'
3870: ELSIF template_rec.array_sel_criteria(i)='LOC' THEN
3871: INSERT INTO psp_selection_cardinality_gt
3872: (lookup_code, total_count)
3873: SELECT 'LOC', COUNT(DISTINCT assignment_id)
3874: FROM per_assignments_f paf
3875: WHERE location_id IN (SELECT TO_NUMBER(criteria_value1)
3876: FROM psp_report_template_details_h prtd
3877: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3878: AND criteria_lookup_code='LOC'
3884: ELSIF template_rec.array_sel_criteria(i)='ORG' THEN
3885: INSERT INTO psp_selection_cardinality_gt
3886: (lookup_code, total_count)
3887: SELECT 'ORG', COUNT(DISTINCT assignment_id)
3888: FROM per_assignments_f paf
3889: WHERE organization_id IN (SELECT TO_NUMBER(criteria_value1)
3890: FROM psp_report_template_details_h prtd
3891: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3892: AND criteria_lookup_code='ORG'
3898: ELSIF template_rec.array_sel_criteria(i)='JOB' THEN
3899: INSERT INTO psp_selection_cardinality_gt
3900: (lookup_code, total_count)
3901: SELECT 'JOB', COUNT(DISTINCT assignment_id)
3902: FROM per_assignments_f paf
3903: WHERE job_id IN (SELECT TO_NUMBER(criteria_value1)
3904: FROM psp_report_template_details_h prtd
3905: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3906: AND criteria_lookup_code='JOB'
3912: ELSIF template_rec.array_sel_criteria(i)='POS' THEN
3913: INSERT INTO psp_selection_cardinality_gt
3914: (lookup_code, total_count)
3915: SELECT 'POS', COUNT(DISTINCT assignment_id)
3916: FROM per_assignments_f paf
3917: WHERE position_id IN (SELECT TO_NUMBER(criteria_value1)
3918: FROM psp_report_template_details_h prtd
3919: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3920: AND criteria_lookup_code='POS'
3926: ELSIF template_rec.array_sel_criteria(i)='ASS' THEN
3927: INSERT INTO psp_selection_cardinality_gt
3928: (lookup_code, total_count)
3929: SELECT 'ASS', COUNT(DISTINCT assignment_id)
3930: FROM per_assignments_f paf
3931: WHERE assignment_status_type_id IN (SELECT TO_NUMBER(criteria_value1)
3932: FROM psp_report_template_details_h prtd
3933: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3934: AND criteria_lookup_code='ASS'
3940: ELSIF template_rec.array_sel_criteria(i)='CST' THEN
3941: INSERT INTO psp_selection_cardinality_gt
3942: (lookup_code, total_count)
3943: SELECT 'CST', COUNT(DISTINCT paf.assignment_id)
3944: FROM per_assignments_f paf,
3945: pay_payrolls_f ppf
3946: WHERE ppf.payroll_id = paf.payroll_id
3947: AND ppf.consolidation_set_id IN (SELECT TO_NUMBER(criteria_value1)
3948: FROM psp_report_template_details_h prtd
4038:
4039: IF l_sql_string IS NOT NULL THEN
4040: g_exec_string := 'INSERT INTO psp_selection_cardinality_gt(lookup_code, total_count)
4041: SELECT '|| '''' || 'PPG' || ''''||' , COUNT(person_id)
4042: FROM per_assignments_f paf,
4043: pay_people_groups ppg
4044: WHERE paf.people_group_id= ppg.people_group_id
4045: AND paf.assignment_type = ''' || 'E' || '''
4046: AND paf.effective_end_date >= :p_effort_start
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
4172: WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
4201: ELSIF g_lookup_code ='EMP' THEN
4202: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4203: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4204: FROM per_all_people_f ppf,
4205: per_assignments_f paf,
4206: per_assignment_status_types past
4207: WHERE ppf.person_id IN (SELECT TO_NUMBER(criteria_value1)
4208: FROM psp_report_template_details_h prtd
4209: WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
4417: ELSIF g_lookup_code ='PAY' THEN
4418:
4419: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4420: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4421: FROM per_assignments_f paf,
4422: per_assignment_status_types past
4423: WHERE payroll_id IN (SELECT TO_NUMBER(criteria_value1)
4424: FROM psp_report_template_details_h prtd
4425: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
4450: ELSIF g_lookup_code ='LOC' THEN
4451:
4452: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4453: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4454: FROM per_assignments_f paf,
4455: per_assignment_status_types past
4456: WHERE location_id IN (SELECT TO_NUMBER(criteria_value1)
4457: FROM psp_report_template_details_h prtd
4458: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
4482:
4483: ELSIF g_lookup_code ='ORG' THEN
4484: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4485: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4486: FROM per_assignments_f paf,
4487: per_assignment_status_types past
4488: WHERE organization_id IN (SELECT TO_NUMBER(criteria_value1)
4489: FROM psp_report_template_details_h prtd
4490: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
4514:
4515: ELSIF g_lookup_code='CST' THEN
4516: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4517: SELECT DISTINCT p_request_id, person_id, assignment_id
4518: FROM per_assignments_f paf,
4519: pay_payrolls_f ppf,
4520: per_assignment_status_types past
4521: WHERE ppf.payroll_id = paf.payroll_id
4522: AND ppf.consolidation_set_id IN (SELECT TO_NUMBER(criteria_value1)
4599: ELSIF g_lookup_code ='JOB' THEN
4600:
4601: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4602: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4603: FROM per_assignments_f paf,
4604: per_assignment_status_types past
4605: WHERE job_id IN (SELECT TO_NUMBER(criteria_value1)
4606: FROM psp_report_template_details_h prtd
4607: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
4631:
4632: ELSIF g_lookup_code ='POS' THEN
4633: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4634: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4635: FROM per_assignments_f paf,
4636: per_assignment_status_types past
4637: WHERE position_id IN (SELECT TO_NUMBER(criteria_value1)
4638: FROM psp_report_template_details_h prtd
4639: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
4663:
4664: ELSIF g_lookup_code ='ASS' THEN
4665: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4666: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4667: FROM per_assignments_f paf,
4668: per_assignment_status_types past
4669: WHERE paf.assignment_status_type_id IN (SELECT TO_NUMBER(criteria_value1)
4670: FROM psp_report_template_details_h prtd
4671: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
4712:
4713: IF l_sql_string IS NOT NULL THEN
4714: g_exec_string := 'INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4715: SELECT :request_id , person_id, assignment_id
4716: FROM per_assignments_f paf,
4717: pay_people_groups ppg,
4718: per_assignment_status_types past
4719: WHERE paf.people_group_id= ppg.people_group_id
4720: AND paf.assignment_type = ''' || 'E' || '''
4803: END IF;
4804: ELSIF g_lookup_code='ALL' THEN
4805: INSERT INTO psp_selected_persons_t(request_id, person_id, assignment_id)
4806: SELECT DISTINCT p_request_id, person_id, assignment_id
4807: FROM per_assignments_f
4808: WHERE assignment_type = 'E'
4809: AND business_group_id = p_business_group_id
4810: AND effective_start_date <= p_effort_end
4811: AND effective_end_date >= p_effort_start;
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'
4875: AND paf.person_id = ppf.person_id
4876: AND paf.assignment_type = 'E'
4888: WHERE request_id = p_request_id
4889: /* Bug 5087294 : Performance fix replacing not in with not exists */
4890: -- AND person_id NOT IN (SELECT NVL(person_id,0)
4891: AND NOT EXISTS ( SELECT 1
4892: FROM per_all_people_f ppf, per_assignments_f paf
4893: WHERE ppf.person_id IN (SELECT TO_NUMBER(criteria_value1)
4894: FROM psp_report_template_details_h prtd
4895: WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
4896: AND criteria_lookup_code='EMP'
5091: WHERE request_id = p_request_id
5092: /* Bug 5087294 : Performance fix replacing not in with not exists */
5093: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5094: AND NOT EXISTS ( SELECT 1
5095: FROM per_assignments_f paf,
5096: psp_report_template_details_h prtd
5097: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5098: AND criteria_lookup_code='PAY'
5099: AND include_exclude_flag='I'
5109: WHERE request_id = p_request_id
5110: /* Bug 5087294 : Performance fix replacing not in with not exists */
5111: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5112: AND NOT EXISTS ( SELECT 1
5113: FROM per_assignments_f paf ,
5114: psp_report_template_details_h prtd
5115: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5116: AND criteria_lookup_code='LOC'
5117: AND paf.assignment_type = 'E'
5127: DELETE FROM psp_selected_persons_t pspt
5128: WHERE request_id = p_request_id
5129: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5130: AND NOT EXISTS ( SELECT 1
5131: FROM per_assignments_f paf ,
5132: psp_report_template_details_h prtd
5133: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5134: AND criteria_lookup_code='ORG'
5135: AND include_exclude_flag='I'
5145: WHERE request_id = p_request_id
5146: /* Bug 5087294 : Performance fix replacing not in with not exists */
5147: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5148: AND NOT EXISTS ( SELECT 1
5149: FROM per_assignments_f paf,
5150: pay_payrolls_f ppf
5151: WHERE ppf.payroll_id = paf.payroll_id
5152: AND ppf.consolidation_set_id IN (SELECT TO_NUMBER(criteria_value1)
5153: FROM psp_report_template_details_h prtd
5197: WHERE request_id = p_request_id
5198: /* Bug 5087294 : Performance fix replacing not in with not exists */
5199: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5200: AND NOT EXISTS ( SELECT 1
5201: FROM per_assignments_f paf ,
5202: psp_report_template_details_h prtd
5203: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5204: AND criteria_lookup_code='JOB'
5205: AND include_exclude_flag='I'
5215: WHERE request_id = p_request_id
5216: /* Bug 5087294 : Performance fix replacing not in with not exists */
5217: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5218: AND NOT EXISTS ( SELECT 1
5219: FROM per_assignments_f paf ,
5220: psp_report_template_details_h prtd
5221: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5222: AND criteria_lookup_code='POS'
5223: AND paf.assignment_type = 'E'
5233: WHERE request_id = p_request_id
5234: /* Bug 5087294 : Performance fix replacing not in with not exists */
5235: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5236: AND NOT EXISTS ( SELECT 1
5237: FROM per_assignments_f paf ,
5238: psp_report_template_details_h prtd
5239: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5240: AND criteria_lookup_code='ASS'
5241: AND include_exclude_flag='I'
5264: IF l_sql_string IS NOT NULL THEN
5265: g_exec_string := 'DELETE FROM psp_selected_persons_t sel
5266: WHERE request_id = :request_id
5267: AND NOT EXISTS (SELECT 1
5268: FROM per_assignments_f paf, pay_people_groups ppg
5269: WHERE paf.people_group_id= ppg.people_group_id
5270: AND paf.assignment_type = ''' || 'E' || '''
5271: AND paf.effective_end_date >= :p_effort_Start
5272: AND paf.effective_start_date <= :p_effort_end
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'
5388: AND paf.person_id = ppf.person_id
5389: AND paf.assignment_type = 'E'
5398: ELSIF template_sel_criteria.array_sel_criteria(i) = 'EMP' THEN
5399: DELETE FROM psp_selected_persons_t
5400: WHERE request_id = p_request_id
5401: AND person_id IN (SELECT DISTINCT ppf.person_id
5402: FROM per_all_people_f ppf, per_assignments_f paf
5403: WHERE ppf.person_id IN (SELECT TO_NUMBER(criteria_value1)
5404: FROM psp_report_template_details_h prtd
5405: WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
5406: AND criteria_lookup_code='EMP'
5583: ELSIF template_sel_criteria.array_sel_criteria(i) ='PAY' THEN
5584: DELETE FROM psp_selected_persons_t
5585: WHERE request_id = p_request_id
5586: AND assignment_id IN (SELECT assignment_id
5587: FROM per_assignments_f paf,
5588: psp_report_template_details_h prtd
5589: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5590: AND criteria_lookup_code='PAY'
5591: AND include_exclude_flag='E'
5597: ELSIF template_sel_criteria.array_sel_criteria(i) ='LOC' THEN
5598: DELETE FROM psp_selected_persons_t
5599: WHERE request_id = p_request_id
5600: AND assignment_id IN (SELECT assignment_id
5601: FROM per_assignments_f paf,
5602: psp_report_template_details_h prtd
5603: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5604: AND criteria_lookup_code='LOC'
5605: AND include_exclude_flag='E'
5611: ELSIF template_sel_criteria.array_sel_criteria(i) ='ORG' THEN
5612: DELETE FROM psp_selected_persons_t
5613: WHERE request_id = p_request_id
5614: AND assignment_id IN (SELECT assignment_id
5615: FROM per_assignments_f paf,
5616: psp_report_template_details_h prtd
5617: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5618: AND criteria_lookup_code='ORG'
5619: AND include_exclude_flag='E'
5625: ELSIF template_sel_criteria.array_sel_criteria(i) = 'JOB' THEN
5626: DELETE FROM psp_selected_persons_t
5627: WHERE request_id = p_request_id
5628: AND assignment_id IN (SELECT assignment_id
5629: FROM per_assignments_f paf,
5630: psp_report_template_details_h prtd
5631: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5632: AND criteria_lookup_code='JOB'
5633: AND include_exclude_flag='E'
5639: ELSIF template_sel_criteria.array_sel_criteria(i) ='POS' THEN
5640: DELETE FROM psp_selected_persons_t
5641: WHERE request_id = p_request_id
5642: AND assignment_id IN (SELECT assignment_id
5643: FROM per_assignments_f paf,
5644: psp_report_template_details_h prtd
5645: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5646: AND criteria_lookup_code='POS'
5647: AND include_exclude_flag='E'
5653: ELSIF template_sel_criteria.array_sel_criteria(i) ='ASS' THEN
5654: DELETE FROM psp_selected_persons_t
5655: WHERE request_id = p_request_id
5656: AND assignment_id IN (SELECT assignment_id
5657: FROM per_assignments_f paf,
5658: psp_report_template_details_h prtd
5659: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5660: AND criteria_lookup_code='ASS'
5661: AND include_exclude_flag='E'
5667: ELSIF template_sel_criteria.array_sel_criteria(i)='CST' THEN
5668: DELETE FROM psp_selected_persons_t
5669: WHERE request_id = p_request_id
5670: AND assignment_id IN (SELECT NVL(assignment_id,0)
5671: FROM per_assignments_f paf,
5672: pay_payrolls_f ppf
5673: WHERE ppf.payroll_id = paf.payroll_id
5674: AND ppf.consolidation_set_id IN (SELECT TO_NUMBER(criteria_value1)
5675: FROM psp_report_template_details_h prtd
5730:
5731: IF l_sql_string IS NOT NULL THEN
5732: g_exec_string := 'DELETE FROM psp_selected_persons_t
5733: WHERE assignment_id IN (SELECT assignment_id
5734: FROM per_assignments_f paf,
5735: pay_people_groups ppg
5736: WHERE paf.people_group_id= ppg.people_group_id
5737: AND paf.assignment_type = ''' || 'E' || '''
5738: AND paf.effective_end_date >= :p_effort_Start