eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PSP_TEMPLATE_SELECTION dependencies on PER_ASSIGNMENTS_F

Line 474: select 'PTY', count(distinct ppf.person_id) from per_people_f ppf, per_assignments_f paf where

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 )

Line 491: select 'EMP', count(distinct ppf.person_id) from per_all_people_f ppf, per_assignments_f paf where

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 )

Line 670: select 'PAY', count(distinct person_id) from per_assignments_f paf where

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: )

Line 683: select 'LOC', count(distinct person_id) from per_assignments_f paf where

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: )

Line 696: select 'ORG', count(distinct person_id) from per_assignments_f paf where

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

Line 709: select 'JOB', count(distinct person_id) from per_assignments_f paf where

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: )

Line 722: select 'POS', count(distinct person_id) from per_assignments_f paf where

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: )

Line 735: select 'ASS', count(distinct person_id) from per_assignments_f paf where

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

Line 749: select 'CST', count(distinct paf.person_id) from per_assignments_f paf

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

Line 907: from per_assignments_f paf,

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

Line 1059: per_assignments_f paf,

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'

Line 1096: per_assignments_f paf,

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'

Line 1133: per_assignments_f paf,

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'

Line 1174: per_assignments_f paf

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

Line 1217: per_assignments_f paf

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

Line 1260: per_assignments_f paf

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

Line 1307: per_assignments_f paf

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

Line 1346: per_assignments_f paf,

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'

Line 1383: per_assignments_f paf,

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'

Line 1420: per_assignments_f paf,

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'

Line 1458: per_assignments_f paf,

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

Line 1551: per_assignments_f paf,

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'

Line 1588: per_assignments_f paf,

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'

Line 1625: per_assignments_f paf,

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'

Line 1694: from per_assignments_f paf,

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

Line 1887: per_assignments_f paf,

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'
1890: AND criteria_lookup_code='PTY'
1891: AND paf.person_id = ppf.person_id

Line 1927: per_assignments_f paf,

1923: /* Bug 5087294 : Performance fix replacing not in with not exists */
1924: -- AND person_id NOT IN (SELECT NVL(person_id,0)
1925: AND NOT EXISTS ( SELECT 1
1926: FROM per_all_people_f ppf,
1927: per_assignments_f paf,
1928: per_assignment_status_types past
1929: WHERE ppf.person_id IN (SELECT TO_NUMBER(criteria_value1)
1930: FROM psp_report_template_details_h prtd
1931: WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'

Line 2158: FROM per_assignments_f paf,

2154: WHERE request_id = p_request_id
2155: /* Bug 5087294 : Performance fix replacing not in with not exists */
2156: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2157: AND NOT EXISTS ( SELECT 1
2158: FROM per_assignments_f paf,
2159: psp_report_template_details_h prtd,
2160: per_assignment_status_types past
2161: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2162: AND criteria_lookup_code='PAY'

Line 2197: FROM per_assignments_f paf ,

2193: WHERE request_id = p_request_id
2194: /* Bug 5087294 : Performance fix replacing not in with not exists */
2195: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2196: AND NOT EXISTS ( SELECT 1
2197: FROM per_assignments_f paf ,
2198: psp_report_template_details_h prtd,
2199: per_assignment_status_types past
2200: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2201: AND criteria_lookup_code='LOC'

Line 2233: FROM per_assignments_f paf ,

2229: DELETE FROM psp_selected_persons_t pspt
2230: WHERE request_id = p_request_id
2231: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2232: AND NOT EXISTS ( SELECT 1
2233: FROM per_assignments_f paf ,
2234: psp_report_template_details_h prtd,
2235: per_assignment_status_types past
2236: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2237: AND criteria_lookup_code='ORG'

Line 2270: FROM per_assignments_f paf,

2266: WHERE request_id = p_request_id
2267: /* Bug 5087294 : Performance fix replacing not in with not exists */
2268: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2269: AND NOT EXISTS ( SELECT 1
2270: FROM per_assignments_f paf,
2271: pay_payrolls_f ppf,
2272: per_assignment_status_types past
2273: WHERE ppf.payroll_id = paf.payroll_id
2274: AND ppf.consolidation_set_id IN (SELECT TO_NUMBER(criteria_value1)

Line 2364: FROM per_assignments_f paf ,

2360: WHERE request_id = p_request_id
2361: /* Bug 5087294 : Performance fix replacing not in with not exists */
2362: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2363: AND NOT EXISTS ( SELECT 1
2364: FROM per_assignments_f paf ,
2365: psp_report_template_details_h prtd,
2366: per_assignment_status_types past
2367: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2368: AND criteria_lookup_code='JOB'

Line 2401: FROM per_assignments_f paf ,

2397: WHERE request_id = p_request_id
2398: /* Bug 5087294 : Performance fix replacing not in with not exists */
2399: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2400: AND NOT EXISTS ( SELECT 1
2401: FROM per_assignments_f paf ,
2402: psp_report_template_details_h prtd,
2403: per_assignment_status_types past
2404: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2405: AND criteria_lookup_code='POS'

Line 2440: FROM per_assignments_f paf ,

2436: WHERE request_id = p_request_id
2437: /* Bug 5087294 : Performance fix replacing not in with not exists */
2438: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2439: AND NOT EXISTS ( SELECT 1
2440: FROM per_assignments_f paf ,
2441: psp_report_template_details_h prtd,
2442: per_assignment_status_types past
2443: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2444: AND criteria_lookup_code='ASS'

Line 2516: (SELECT 1 FROM per_assignments_f paf, pay_people_groups ppg, per_assignment_status_types past

2512: --- replaced non-performant delete stmnt for 4429787
2513:
2514: g_exec_string := 'delete from psp_selected_persons_t sel where request_id = :request_id
2515: AND NOT EXISTS
2516: (SELECT 1 FROM per_assignments_f paf, pay_people_groups ppg, per_assignment_status_types past
2517: WHERE paf.people_group_id= ppg.people_group_id
2518: AND paf.assignment_type = ''' || 'E' || '''
2519: AND paf.effective_end_date >= :p_effort_Start AND
2520: paf.effective_start_date <= :p_effort_end

Line 2677: per_assignments_f paf,

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'
2680: AND criteria_lookup_code='PTY'
2681: AND paf.person_id = ppf.person_id

Line 2715: per_assignments_f paf,

2711: DELETE FROM psp_selected_persons_t
2712: WHERE request_id = p_request_id
2713: AND person_id IN (SELECT DISTINCT ppf.person_id
2714: FROM per_all_people_f ppf,
2715: per_assignments_f paf,
2716: per_assignment_status_types past
2717: WHERE ppf.person_id IN (SELECT TO_NUMBER(criteria_value1)
2718: FROM psp_report_template_details_h prtd
2719: WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'

Line 2939: FROM per_assignments_f paf,

2935:
2936: DELETE FROM psp_selected_persons_t
2937: WHERE request_id = p_request_id
2938: AND person_id IN (SELECT person_id
2939: FROM per_assignments_f paf,
2940: psp_report_template_details_h prtd,
2941: per_assignment_status_types past
2942: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2943: AND criteria_lookup_code='PAY'

Line 2974: FROM per_assignments_f paf,

2970:
2971: DELETE FROM psp_selected_persons_t
2972: WHERE request_id = p_request_id
2973: AND person_id IN (SELECT person_id
2974: FROM per_assignments_f paf,
2975: psp_report_template_details_h prtd,
2976: per_assignment_status_types past
2977: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2978: AND criteria_lookup_code='LOC'

Line 3009: FROM per_assignments_f paf,

3005:
3006: DELETE FROM psp_selected_persons_t
3007: WHERE request_id = p_request_id
3008: AND person_id IN (SELECT person_id
3009: FROM per_assignments_f paf,
3010: psp_report_template_details_h prtd,
3011: per_assignment_status_types past
3012: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3013: AND criteria_lookup_code='ORG'

Line 3048: FROM per_assignments_f paf,

3044:
3045: DELETE FROM psp_selected_persons_t
3046: WHERE request_id = p_request_id
3047: AND person_id IN (SELECT person_id
3048: FROM per_assignments_f paf,
3049: psp_report_template_details_h prtd,
3050: per_assignment_status_types past
3051: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3052: AND criteria_lookup_code='JOB'

Line 3083: FROM per_assignments_f paf,

3079:
3080: DELETE FROM psp_selected_persons_t
3081: WHERE request_id = p_request_id
3082: AND person_id IN (SELECT person_id
3083: FROM per_assignments_f paf,
3084: psp_report_template_details_h prtd,
3085: per_assignment_status_types past
3086: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3087: AND criteria_lookup_code='POS'

Line 3117: FROM per_assignments_f paf,

3113:
3114: DELETE FROM psp_selected_persons_t
3115: WHERE request_id = p_request_id
3116: AND person_id IN (SELECT person_id
3117: FROM per_assignments_f paf,
3118: psp_report_template_details_h prtd,
3119: per_assignment_status_types past
3120: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3121: AND criteria_lookup_code='ASS'

Line 3152: FROM per_assignments_f paf,

3148:
3149: DELETE FROM psp_selected_persons_t
3150: WHERE request_id = p_request_id
3151: AND person_id IN (SELECT NVL(person_id,0)
3152: FROM per_assignments_f paf,
3153: pay_payrolls_f ppf,
3154: per_assignment_status_types past
3155: WHERE ppf.payroll_id = paf.payroll_id
3156: AND ppf.consolidation_set_id IN (SELECT TO_NUMBER(criteria_value1)

Line 3274: in (select person_id from per_assignments_f paf,

3270:
3271: IF l_sql_string is not null then
3272:
3273: g_exec_string := 'delete from psp_selected_persons_t where person_id
3274: in (select person_id from per_assignments_f paf,
3275: pay_people_groups ppg , per_assignment_status_types past
3276: where paf.people_group_id= ppg.people_group_id
3277: AND paf.assignment_type = ''' || 'E' || '''
3278: and paf.effective_end_date >= :p_effort_Start and

Line 3633: per_assignments_f paf

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'
3637: AND criteria_lookup_code ='PTY'

Line 3828: FROM per_assignments_f paf

3824: ELSIF template_rec.array_sel_criteria(i)='PAY' THEN
3825: INSERT INTO psp_selection_cardinality_gt
3826: (lookup_code, total_count)
3827: SELECT 'PAY', COUNT(DISTINCT assignment_id)
3828: FROM per_assignments_f paf
3829: WHERE payroll_id IN (SELECT TO_NUMBER(criteria_value1)
3830: FROM psp_report_template_details_h prtd
3831: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3832: AND criteria_lookup_code='PAY'

Line 3841: FROM per_assignments_f paf

3837: ELSIF template_rec.array_sel_criteria(i)='LOC' THEN
3838: INSERT INTO psp_selection_cardinality_gt
3839: (lookup_code, total_count)
3840: SELECT 'LOC', COUNT(DISTINCT assignment_id)
3841: FROM per_assignments_f paf
3842: WHERE location_id IN (SELECT TO_NUMBER(criteria_value1)
3843: FROM psp_report_template_details_h prtd
3844: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3845: AND criteria_lookup_code='LOC'

Line 3855: FROM per_assignments_f paf

3851: ELSIF template_rec.array_sel_criteria(i)='ORG' THEN
3852: INSERT INTO psp_selection_cardinality_gt
3853: (lookup_code, total_count)
3854: SELECT 'ORG', COUNT(DISTINCT assignment_id)
3855: FROM per_assignments_f paf
3856: WHERE organization_id IN (SELECT TO_NUMBER(criteria_value1)
3857: FROM psp_report_template_details_h prtd
3858: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3859: AND criteria_lookup_code='ORG'

Line 3869: FROM per_assignments_f paf

3865: ELSIF template_rec.array_sel_criteria(i)='JOB' THEN
3866: INSERT INTO psp_selection_cardinality_gt
3867: (lookup_code, total_count)
3868: SELECT 'JOB', COUNT(DISTINCT assignment_id)
3869: FROM per_assignments_f paf
3870: WHERE job_id IN (SELECT TO_NUMBER(criteria_value1)
3871: FROM psp_report_template_details_h prtd
3872: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3873: AND criteria_lookup_code='JOB'

Line 3883: FROM per_assignments_f paf

3879: ELSIF template_rec.array_sel_criteria(i)='POS' THEN
3880: INSERT INTO psp_selection_cardinality_gt
3881: (lookup_code, total_count)
3882: SELECT 'POS', COUNT(DISTINCT assignment_id)
3883: FROM per_assignments_f paf
3884: WHERE position_id IN (SELECT TO_NUMBER(criteria_value1)
3885: FROM psp_report_template_details_h prtd
3886: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3887: AND criteria_lookup_code='POS'

Line 3897: FROM per_assignments_f paf

3893: ELSIF template_rec.array_sel_criteria(i)='ASS' THEN
3894: INSERT INTO psp_selection_cardinality_gt
3895: (lookup_code, total_count)
3896: SELECT 'ASS', COUNT(DISTINCT assignment_id)
3897: FROM per_assignments_f paf
3898: WHERE assignment_status_type_id IN (SELECT TO_NUMBER(criteria_value1)
3899: FROM psp_report_template_details_h prtd
3900: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3901: AND criteria_lookup_code='ASS'

Line 3911: FROM per_assignments_f paf,

3907: ELSIF template_rec.array_sel_criteria(i)='CST' THEN
3908: INSERT INTO psp_selection_cardinality_gt
3909: (lookup_code, total_count)
3910: SELECT 'CST', COUNT(DISTINCT paf.assignment_id)
3911: FROM per_assignments_f paf,
3912: pay_payrolls_f ppf
3913: WHERE ppf.payroll_id = paf.payroll_id
3914: AND ppf.consolidation_set_id IN (SELECT TO_NUMBER(criteria_value1)
3915: FROM psp_report_template_details_h prtd

Line 4009: FROM per_assignments_f paf,

4005:
4006: IF l_sql_string IS NOT NULL THEN
4007: g_exec_string := 'INSERT INTO psp_selection_cardinality_gt(lookup_code, total_count)
4008: SELECT '|| '''' || 'PPG' || ''''||' , COUNT(person_id)
4009: FROM per_assignments_f paf,
4010: pay_people_groups ppg
4011: WHERE paf.people_group_id= ppg.people_group_id
4012: AND paf.assignment_type = ''' || 'E' || '''
4013: AND paf.effective_end_date >= :p_effort_start

Line 4135: per_assignments_f paf,

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
4139: WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'

Line 4172: per_assignments_f paf,

4168: ELSIF g_lookup_code ='EMP' THEN
4169: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4170: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4171: FROM per_all_people_f ppf,
4172: per_assignments_f paf,
4173: per_assignment_status_types past
4174: WHERE ppf.person_id IN (SELECT TO_NUMBER(criteria_value1)
4175: FROM psp_report_template_details_h prtd
4176: WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'

Line 4388: FROM per_assignments_f paf,

4384: ELSIF g_lookup_code ='PAY' THEN
4385:
4386: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4387: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4388: FROM per_assignments_f paf,
4389: per_assignment_status_types past
4390: WHERE payroll_id IN (SELECT TO_NUMBER(criteria_value1)
4391: FROM psp_report_template_details_h prtd
4392: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'

Line 4421: FROM per_assignments_f paf,

4417: ELSIF g_lookup_code ='LOC' 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 location_id IN (SELECT TO_NUMBER(criteria_value1)
4424: FROM psp_report_template_details_h prtd
4425: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'

Line 4453: FROM per_assignments_f paf,

4449:
4450: ELSIF g_lookup_code ='ORG' THEN
4451: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4452: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4453: FROM per_assignments_f paf,
4454: per_assignment_status_types past
4455: WHERE organization_id IN (SELECT TO_NUMBER(criteria_value1)
4456: FROM psp_report_template_details_h prtd
4457: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'

Line 4485: FROM per_assignments_f paf,

4481:
4482: ELSIF g_lookup_code='CST' THEN
4483: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4484: SELECT DISTINCT p_request_id, person_id, assignment_id
4485: FROM per_assignments_f paf,
4486: pay_payrolls_f ppf,
4487: per_assignment_status_types past
4488: WHERE ppf.payroll_id = paf.payroll_id
4489: AND ppf.consolidation_set_id IN (SELECT TO_NUMBER(criteria_value1)

Line 4570: FROM per_assignments_f paf,

4566: ELSIF g_lookup_code ='JOB' THEN
4567:
4568: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4569: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4570: FROM per_assignments_f paf,
4571: per_assignment_status_types past
4572: WHERE job_id IN (SELECT TO_NUMBER(criteria_value1)
4573: FROM psp_report_template_details_h prtd
4574: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'

Line 4602: FROM per_assignments_f paf,

4598:
4599: ELSIF g_lookup_code ='POS' THEN
4600: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4601: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4602: FROM per_assignments_f paf,
4603: per_assignment_status_types past
4604: WHERE position_id IN (SELECT TO_NUMBER(criteria_value1)
4605: FROM psp_report_template_details_h prtd
4606: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'

Line 4634: FROM per_assignments_f paf,

4630:
4631: ELSIF g_lookup_code ='ASS' THEN
4632: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4633: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4634: FROM per_assignments_f paf,
4635: per_assignment_status_types past
4636: WHERE paf.assignment_status_type_id IN (SELECT TO_NUMBER(criteria_value1)
4637: FROM psp_report_template_details_h prtd
4638: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'

Line 4683: FROM per_assignments_f paf,

4679:
4680: IF l_sql_string IS NOT NULL THEN
4681: g_exec_string := 'INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4682: SELECT :request_id , person_id, assignment_id
4683: FROM per_assignments_f paf,
4684: pay_people_groups ppg,
4685: per_assignment_status_types past
4686: WHERE paf.people_group_id= ppg.people_group_id
4687: AND paf.assignment_type = ''' || 'E' || '''

Line 4774: FROM per_assignments_f

4770: END IF;
4771: ELSIF g_lookup_code='ALL' THEN
4772: INSERT INTO psp_selected_persons_t(request_id, person_id, assignment_id)
4773: SELECT DISTINCT p_request_id, person_id, assignment_id
4774: FROM per_assignments_f
4775: WHERE assignment_type = 'E'
4776: AND business_group_id = p_business_group_id
4777: AND effective_start_date <= p_effort_end
4778: AND effective_end_date >= p_effort_start;

Line 4839: per_assignments_f paf

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'
4842: AND paf.person_id = ppf.person_id
4843: AND paf.assignment_type = 'E'

Line 4859: FROM per_all_people_f ppf, per_assignments_f paf

4855: WHERE request_id = p_request_id
4856: /* Bug 5087294 : Performance fix replacing not in with not exists */
4857: -- AND person_id NOT IN (SELECT NVL(person_id,0)
4858: AND NOT EXISTS ( SELECT 1
4859: FROM per_all_people_f ppf, per_assignments_f paf
4860: WHERE ppf.person_id IN (SELECT TO_NUMBER(criteria_value1)
4861: FROM psp_report_template_details_h prtd
4862: WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
4863: AND criteria_lookup_code='EMP'

Line 5062: FROM per_assignments_f paf,

5058: WHERE request_id = p_request_id
5059: /* Bug 5087294 : Performance fix replacing not in with not exists */
5060: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5061: AND NOT EXISTS ( SELECT 1
5062: FROM per_assignments_f paf,
5063: psp_report_template_details_h prtd
5064: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5065: AND criteria_lookup_code='PAY'
5066: AND include_exclude_flag='I'

Line 5080: FROM per_assignments_f paf ,

5076: WHERE request_id = p_request_id
5077: /* Bug 5087294 : Performance fix replacing not in with not exists */
5078: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5079: AND NOT EXISTS ( SELECT 1
5080: FROM per_assignments_f paf ,
5081: psp_report_template_details_h prtd
5082: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5083: AND criteria_lookup_code='LOC'
5084: AND paf.assignment_type = 'E'

Line 5098: FROM per_assignments_f paf ,

5094: DELETE FROM psp_selected_persons_t pspt
5095: WHERE request_id = p_request_id
5096: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5097: AND NOT EXISTS ( SELECT 1
5098: FROM per_assignments_f paf ,
5099: psp_report_template_details_h prtd
5100: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5101: AND criteria_lookup_code='ORG'
5102: AND include_exclude_flag='I'

Line 5116: FROM per_assignments_f paf,

5112: WHERE request_id = p_request_id
5113: /* Bug 5087294 : Performance fix replacing not in with not exists */
5114: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5115: AND NOT EXISTS ( SELECT 1
5116: FROM per_assignments_f paf,
5117: pay_payrolls_f ppf
5118: WHERE ppf.payroll_id = paf.payroll_id
5119: AND ppf.consolidation_set_id IN (SELECT TO_NUMBER(criteria_value1)
5120: FROM psp_report_template_details_h prtd

Line 5168: FROM per_assignments_f paf ,

5164: WHERE request_id = p_request_id
5165: /* Bug 5087294 : Performance fix replacing not in with not exists */
5166: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5167: AND NOT EXISTS ( SELECT 1
5168: FROM per_assignments_f paf ,
5169: psp_report_template_details_h prtd
5170: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5171: AND criteria_lookup_code='JOB'
5172: AND include_exclude_flag='I'

Line 5186: FROM per_assignments_f paf ,

5182: WHERE request_id = p_request_id
5183: /* Bug 5087294 : Performance fix replacing not in with not exists */
5184: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5185: AND NOT EXISTS ( SELECT 1
5186: FROM per_assignments_f paf ,
5187: psp_report_template_details_h prtd
5188: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5189: AND criteria_lookup_code='POS'
5190: AND paf.assignment_type = 'E'

Line 5204: FROM per_assignments_f paf ,

5200: WHERE request_id = p_request_id
5201: /* Bug 5087294 : Performance fix replacing not in with not exists */
5202: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5203: AND NOT EXISTS ( SELECT 1
5204: FROM per_assignments_f paf ,
5205: psp_report_template_details_h prtd
5206: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5207: AND criteria_lookup_code='ASS'
5208: AND include_exclude_flag='I'

Line 5235: FROM per_assignments_f paf, pay_people_groups ppg

5231: IF l_sql_string IS NOT NULL THEN
5232: g_exec_string := 'DELETE FROM psp_selected_persons_t sel
5233: WHERE request_id = :request_id
5234: AND NOT EXISTS (SELECT 1
5235: FROM per_assignments_f paf, pay_people_groups ppg
5236: WHERE paf.people_group_id= ppg.people_group_id
5237: AND paf.assignment_type = ''' || 'E' || '''
5238: AND paf.effective_end_date >= :p_effort_Start
5239: AND paf.effective_start_date <= :p_effort_end

Line 5352: per_assignments_f paf

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'
5355: AND paf.person_id = ppf.person_id
5356: AND paf.assignment_type = 'E'

Line 5369: FROM per_all_people_f ppf, per_assignments_f paf

5365: ELSIF template_sel_criteria.array_sel_criteria(i) = 'EMP' THEN
5366: DELETE FROM psp_selected_persons_t
5367: WHERE request_id = p_request_id
5368: AND person_id IN (SELECT DISTINCT ppf.person_id
5369: FROM per_all_people_f ppf, per_assignments_f paf
5370: WHERE ppf.person_id IN (SELECT TO_NUMBER(criteria_value1)
5371: FROM psp_report_template_details_h prtd
5372: WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
5373: AND criteria_lookup_code='EMP'

Line 5554: FROM per_assignments_f paf,

5550: ELSIF template_sel_criteria.array_sel_criteria(i) ='PAY' THEN
5551: DELETE FROM psp_selected_persons_t
5552: WHERE request_id = p_request_id
5553: AND assignment_id IN (SELECT assignment_id
5554: FROM per_assignments_f paf,
5555: psp_report_template_details_h prtd
5556: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5557: AND criteria_lookup_code='PAY'
5558: AND include_exclude_flag='E'

Line 5568: FROM per_assignments_f paf,

5564: ELSIF template_sel_criteria.array_sel_criteria(i) ='LOC' THEN
5565: DELETE FROM psp_selected_persons_t
5566: WHERE request_id = p_request_id
5567: AND assignment_id IN (SELECT assignment_id
5568: FROM per_assignments_f paf,
5569: psp_report_template_details_h prtd
5570: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5571: AND criteria_lookup_code='LOC'
5572: AND include_exclude_flag='E'

Line 5582: FROM per_assignments_f paf,

5578: ELSIF template_sel_criteria.array_sel_criteria(i) ='ORG' THEN
5579: DELETE FROM psp_selected_persons_t
5580: WHERE request_id = p_request_id
5581: AND assignment_id IN (SELECT assignment_id
5582: FROM per_assignments_f paf,
5583: psp_report_template_details_h prtd
5584: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5585: AND criteria_lookup_code='ORG'
5586: AND include_exclude_flag='E'

Line 5596: FROM per_assignments_f paf,

5592: ELSIF template_sel_criteria.array_sel_criteria(i) = 'JOB' THEN
5593: DELETE FROM psp_selected_persons_t
5594: WHERE request_id = p_request_id
5595: AND assignment_id IN (SELECT assignment_id
5596: FROM per_assignments_f paf,
5597: psp_report_template_details_h prtd
5598: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5599: AND criteria_lookup_code='JOB'
5600: AND include_exclude_flag='E'

Line 5610: FROM per_assignments_f paf,

5606: ELSIF template_sel_criteria.array_sel_criteria(i) ='POS' THEN
5607: DELETE FROM psp_selected_persons_t
5608: WHERE request_id = p_request_id
5609: AND assignment_id IN (SELECT assignment_id
5610: FROM per_assignments_f paf,
5611: psp_report_template_details_h prtd
5612: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5613: AND criteria_lookup_code='POS'
5614: AND include_exclude_flag='E'

Line 5624: FROM per_assignments_f paf,

5620: ELSIF template_sel_criteria.array_sel_criteria(i) ='ASS' THEN
5621: DELETE FROM psp_selected_persons_t
5622: WHERE request_id = p_request_id
5623: AND assignment_id IN (SELECT assignment_id
5624: FROM per_assignments_f paf,
5625: psp_report_template_details_h prtd
5626: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5627: AND criteria_lookup_code='ASS'
5628: AND include_exclude_flag='E'

Line 5638: FROM per_assignments_f paf,

5634: ELSIF template_sel_criteria.array_sel_criteria(i)='CST' THEN
5635: DELETE FROM psp_selected_persons_t
5636: WHERE request_id = p_request_id
5637: AND assignment_id IN (SELECT NVL(assignment_id,0)
5638: FROM per_assignments_f paf,
5639: pay_payrolls_f ppf
5640: WHERE ppf.payroll_id = paf.payroll_id
5641: AND ppf.consolidation_set_id IN (SELECT TO_NUMBER(criteria_value1)
5642: FROM psp_report_template_details_h prtd

Line 5701: FROM per_assignments_f paf,

5697:
5698: IF l_sql_string IS NOT NULL THEN
5699: g_exec_string := 'DELETE FROM psp_selected_persons_t
5700: WHERE assignment_id IN (SELECT assignment_id
5701: FROM per_assignments_f paf,
5702: pay_people_groups ppg
5703: WHERE paf.people_group_id= ppg.people_group_id
5704: AND paf.assignment_type = ''' || 'E' || '''
5705: AND paf.effective_end_date >= :p_effort_Start