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 PSP_REPORT_TEMPLATE_DETAILS_H

Line 95: insert into psp_report_template_details_h (request_id, template_detail_id, criteria_lookup_type,

91: NULL, NULL, sprcd_tolerance_amt, sprcd_tolerance_percent, description, legislation_code,
92: p_payroll_action_id, last_update_date, last_updated_by, last_update_login, created_by, creation_date, hundred_pcent_eff_at_per_asg,selection_match_level from psp_report_templates where template_id = l_psp_template_id);
93:
94:
95: insert into psp_report_template_details_h (request_id, template_detail_id, criteria_lookup_type,
96: criteria_lookup_code, include_exclude_flag, criteria_value1, criteria_value2, criteria_value3,
97: last_update_date, last_updated_by, last_update_login, created_by, creation_date) (select
98: p_request_id, template_detail_id,
99: criteria_lookup_type, criteria_lookup_code, include_exclude_flag,

Line 438: psp_report_template_details_h where request_id = p_request_id and

434:
435: CURSOR get_selection_cardinality_csr(p_request_id IN NUMBER)
436: IS
437: Select distinct (criteria_lookup_code) from
438: psp_report_template_details_h where request_id = p_request_id and
439: include_exclude_flag='I' and criteria_lookup_type='PSP_SELECTION_CRITERIA';
440:
441:
442: /* The below cursors would only be used only when no statis selection criteria have been chosen */

Line 446: psp_report_template_details_h where request_id = p_request_id and

442: /* The below cursors would only be used only when no statis selection criteria have been chosen */
443:
444: CURSOR PPG_CURSOR IS
445: select criteria_value1, criteria_value2 from
446: psp_report_template_details_h where request_id = p_request_id and
447: include_exclude_flag='I' and
448: criteria_lookup_type= 'PSP_SELECTION_CRITERIA' and criteria_lookup_code='PPG';
449:
450:

Line 453: psp_report_template_details_h where request_id = p_request_id and

449:
450:
451: CURSOR GLA_CURSOR IS
452: select criteria_value1 , criteria_value2, criteria_value3 from
453: psp_report_template_details_h where request_id = p_request_id and
454: include_exclude_flag='I' and
455: criteria_lookup_type ='PSP_SELECTION_CRITERIA' and criteria_lookup_code ='GLA';
456:
457:

Line 475: person_type_id in (select TO_NUMBER(criteria_value1) from psp_report_template_details_h prtd where

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 )
479: AND paf.person_id = ppf.person_id

Line 492: ppf.person_id in (select TO_NUMBER(criteria_value1) from psp_report_template_details_h prtd where

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 )
496: AND paf.person_id = ppf.person_id

Line 508: supervisor_id in (select TO_NUMBER(criteria_value1) from psp_report_template_details_h prtd where

504: ELSIF template_rec.array_sel_criteria(i) ='SUP' THEN
505:
506: insert into psp_selection_cardinality_gt(lookup_code, total_count) (
507: select 'SUP', count(distinct person_id) from per_all_assignments_f paf where
508: supervisor_id in (select TO_NUMBER(criteria_value1) from psp_report_template_details_h prtd where
509: criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code='SUP'
510: and request_id = p_request_id
511: and include_exclude_flag='I'
512: )

Line 524: psp_report_template_details_h prtd ,

520: --- replaced non-performant insert with this for 4429787
521: insert into psp_selection_cardinality_gt(lookup_code, total_count)(
522: SELECT 'AWD', COUNT(DISTINCT psl.person_id)
523: FROM psp_summary_lines psl,
524: psp_report_template_details_h prtd ,
525: per_time_periods ptp
526: WHERE
527: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
528: prtd.criteria_lookup_code='AWD' AND

Line 562: psp_report_template_details_h prtd ,

558:
559: INSERT INTO psp_selection_cardinality_gt(lookup_code, total_count)(
560: SELECT 'ATY', COUNT(DISTINCT psl.person_id)
561: FROM psp_summary_lines psl,
562: psp_report_template_details_h prtd ,
563: gms_awards_all gaa,
564: per_time_periods ptp
565: WHERE psl.award_id = gaa.award_id AND
566: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND

Line 599: psp_report_template_details_h prtd ,

595: --- replaced non-performant insert with this for 4429787
596: INSERT INTO psp_selection_cardinality_gt(lookup_code, total_count)(
597: SELECT 'PRT', COUNT(DISTINCT psl.person_id)
598: FROM psp_summary_lines psl,
599: psp_report_template_details_h prtd ,
600: pa_projects_all ppa ,
601: per_time_periods ptp
602: WHERE psl.project_id = ppa.project_id AND
603: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND

Line 636: psp_report_template_details_h prtd ,

632: --- replaced non-performant insert with this for 4429787
633: insert into psp_selection_cardinality_gt(lookup_code, total_count)(
634: SELECT 'PRJ', COUNT(DISTINCT psl.person_id)
635: FROM psp_summary_lines psl,
636: psp_report_template_details_h prtd ,
637: per_time_periods ptp
638: WHERE
639: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
640: prtd.criteria_lookup_code='PRJ' AND

Line 671: payroll_id in (select TO_NUMBER(criteria_value1) from psp_report_template_details_h prtd where

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: )
675: AND paf.assignment_type = 'E'

Line 684: location_id in (select TO_NUMBER(criteria_value1) from psp_report_template_details_h prtd where

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: )
688: AND paf.assignment_type = 'E'

Line 697: organization_id in (select TO_NUMBER(criteria_value1) from psp_report_template_details_h prtd

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

Line 710: job_id in (select TO_NUMBER(criteria_value1) from psp_report_template_details_h prtd where

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: )
714: AND paf.assignment_type = 'E'

Line 723: position_id in (select TO_NUMBER(criteria_value1) from psp_report_template_details_h prtd where

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: )
727: AND paf.assignment_type = 'E'

Line 736: assignment_status_type_id in (select TO_NUMBER(criteria_value1) from psp_report_template_details_h

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

Line 753: (select TO_NUMBER(criteria_value1) from psp_report_template_details_h

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
754: prtd where
755: criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code='CST'
756: and include_exclude_flag='I' and request_id = p_request_id
757: )

Line 773: select TO_NUMBER(criteria_value1) from psp_report_template_details_h

769: select 'AST', count(distinct paf.person_id) from per_all_assignments_f paf, hr_assignment_sets has ,
770: hr_assignment_Set_amendments hasa
771: where
772: has.assignment_set_id in (
773: select TO_NUMBER(criteria_value1) from psp_report_template_details_h
774: prtd where
775: criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code='AST'
776: and include_exclude_flag='I' and request_id =p_request_id
777: )

Line 891: into g_exec_string from psp_report_template_details_h ;

887:
888: /*
889: select l_sql_string
890: || ' OR ' || l_criteria_value1 || ' = ' || ''''|| l_criteria_value2 ||''''
891: into g_exec_string from psp_report_template_details_h ;
892:
893: */
894:
895: g_exec_string:= l_sql_string|| ' OR '||l_criteria_value1 ||' = '||'''' || l_criteria_value2|| '''';

Line 1033: psp_report_template_details_h where request_id = p_request_id and

1029: IN DATE, p_effort_end IN DATE, p_business_group_id IN NUMBER, p_Set_of_books_id IN NUMBER) IS
1030: PRAGMA AUTONOMOUS_TRANSACTION;
1031: CURSOR PPG_CURSOR IS
1032: select criteria_value1, criteria_value2 from
1033: psp_report_template_details_h where request_id = p_request_id and
1034: criteria_lookup_type= 'PSP_SELECTION_CRITERIA' and criteria_lookup_code='PPG' and
1035: include_exclude_flag='I';
1036:
1037:

Line 1040: psp_report_template_details_h where request_id = p_request_id and

1036:
1037:
1038: CURSOR GLA_CURSOR IS
1039: select criteria_value1 , criteria_value2, criteria_value3 from
1040: psp_report_template_details_h where request_id = p_request_id and
1041: criteria_lookup_type ='PSP_SELECTION_CRITERIA' and criteria_lookup_code ='GLA' and
1042: include_exclude_flag='I';
1043:
1044: i number;

Line 1062: FROM psp_report_template_details_h prtd

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'
1064: AND criteria_lookup_code='PTY'
1065: AND request_id = p_request_id
1066: AND include_exclude_flag='I')

Line 1099: FROM psp_report_template_details_h prtd

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'
1101: AND criteria_lookup_code='EMP'
1102: AND include_exclude_flag='I'
1103: AND request_id = p_request_id)

Line 1136: FROM psp_report_template_details_h prtd

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'
1138: AND criteria_lookup_code='SUP'
1139: AND include_exclude_flag='I'
1140: AND request_id = p_request_id)

Line 1172: psp_report_template_details_h prtd ,

1168: --- replaced original query for performance issues -- 4429787
1169: insert into psp_selected_persons_t(request_id, person_id, assignment_id)(
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

Line 1214: psp_report_template_details_h prtd ,

1210: --- replaced non-performant insert with this for 4429787
1211: INSERT INTO psp_selected_persons_t(request_id , person_id, assignment_id)(
1212: SELECT DISTINCT p_request_id, psl.person_id, paf.assignment_id
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

Line 1257: psp_report_template_details_h prtd ,

1253:
1254: INSERT INTO psp_selected_persons_t(request_id , person_id, assignment_id)(
1255: SELECT DISTINCT p_request_id, psl.person_id, paf.assignment_id
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

Line 1305: psp_report_template_details_h prtd ,

1301: --- replaced original query for performance issues -- 4429787
1302: insert into psp_selected_persons_t(request_id, person_id, assignment_id)(
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

Line 1349: FROM psp_report_template_details_h prtd

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'
1351: AND criteria_lookup_code='PAY'
1352: AND include_exclude_flag='I'
1353: AND request_id = p_request_id)

Line 1386: FROM psp_report_template_details_h prtd

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'
1388: AND criteria_lookup_code='LOC'
1389: AND include_exclude_flag='I'
1390: AND request_id = p_request_id)

Line 1423: FROM psp_report_template_details_h prtd

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'
1425: AND criteria_lookup_code='ORG'
1426: AND request_id = p_request_id
1427: AND include_exclude_flag='I')

Line 1462: FROM psp_report_template_details_h prtd

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
1463: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
1464: AND criteria_lookup_code='CST'
1465: AND include_exclude_flag='I'
1466: AND request_id = p_request_id)

Line 1503: FROM psp_report_template_details_h prtd

1499: hr_assignment_set_amendments hasa,
1500: per_all_people_f ppf,
1501: per_assignment_status_types past
1502: WHERE has.assignment_set_id IN (SELECT TO_NUMBER(criteria_value1)
1503: FROM psp_report_template_details_h prtd
1504: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
1505: AND criteria_lookup_code='AST'
1506: AND include_exclude_flag='I'
1507: AND request_id =p_request_id)

Line 1554: FROM psp_report_template_details_h prtd

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'
1556: AND criteria_lookup_code='JOB'
1557: AND include_exclude_flag='I'
1558: AND request_id = p_request_id)

Line 1591: FROM psp_report_template_details_h prtd

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'
1593: AND criteria_lookup_code='POS'
1594: AND request_id = p_request_id
1595: AND include_exclude_flag='I')

Line 1628: FROM psp_report_template_details_h prtd

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'
1630: AND criteria_lookup_code='ASS'
1631: AND request_id = p_request_id
1632: AND include_exclude_flag='I')

Line 1679: into g_exec_string from psp_report_template_details_h ;

1675:
1676: /*
1677: select l_sql_string
1678: || ' OR ' || l_criteria_value1 || ' = ' || ''''|| l_criteria_value2 ||''''
1679: into g_exec_string from psp_report_template_details_h ;
1680:
1681: */
1682: g_exec_string:= l_sql_string || ' OR '||l_criteria_value1 ||' = ' || ''''||l_criteria_value2 || '''';
1683:

Line 1828: psp_report_template_details_h where request_id = p_request_id and

1824:
1825: CURSOR get_all_selection_criteria(p_request_id IN NUMBER) is
1826: SELECT distinct criteria_lookup_code,
1827: include_exclude_flag from
1828: psp_report_template_details_h where request_id = p_request_id and
1829: criteria_lookup_type='PSP_SELECTION_CRITERIA' ORDER BY include_exclude_flag;
1830:
1831:
1832: CURSOR PPG_CURSOR IS

Line 1834: psp_report_template_details_h where request_id = p_request_id and

1830:
1831:
1832: CURSOR PPG_CURSOR IS
1833: select criteria_value1, criteria_value2 from
1834: psp_report_template_details_h where request_id = p_request_id and
1835: criteria_lookup_type= 'PSP_SELECTION_CRITERIA' and criteria_lookup_code='PPG' and include_exclude_flag='I';
1836:
1837:
1838: CURSOR GLA_CURSOR IS

Line 1840: psp_report_template_details_h where request_id = p_request_id and

1836:
1837:
1838: CURSOR GLA_CURSOR IS
1839: select criteria_value1 , criteria_value2, criteria_value3 from
1840: psp_report_template_details_h where request_id = p_request_id and
1841: criteria_lookup_type ='PSP_SELECTION_CRITERIA' and criteria_lookup_code ='GLA' and include_exclude_flag='I';
1842:
1843:
1844: type t_varchar_30_type is TABLE Of VARCHAR2(30) INDEX BY BINARY_INTEGER;

Line 1886: psp_report_template_details_h prtd,

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

Line 1930: FROM psp_report_template_details_h prtd

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'
1932: AND criteria_lookup_code='EMP'
1933: AND include_exclude_flag='I'
1934: AND prtd.request_id = p_request_id)

Line 1969: psp_report_template_details_h prtd,

1965: /* Bug 5087294 : Performance fix replacing not in with not exists */
1966: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
1967: AND NOT EXISTS ( SELECT 1
1968: FROM per_all_assignments_f paf,
1969: psp_report_template_details_h prtd,
1970: per_assignment_status_types past
1971: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
1972: AND criteria_lookup_code='SUP'
1973: AND include_exclude_flag='I'

Line 2006: psp_report_template_details_h prtd ,

2002: --- replaced original query for performance issues -- 4429787
2003: delete from psp_selected_persons_t where request_id = p_request_id AND person_id not in (
2004: select psl.person_id
2005: FROM psp_summary_lines psl,
2006: psp_report_template_details_h prtd ,
2007: per_time_periods ptp
2008: WHERE
2009: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
2010: prtd.criteria_lookup_code='AWD' AND

Line 2044: psp_report_template_details_h prtd ,

2040: --- replaced non-performant delete with this for 4429787
2041: DELETE FROM psp_selected_persons_t WHERE request_id = p_request_id AND person_id NOT IN (
2042: SELECT NVL(psl.person_id,0)
2043: FROM psp_summary_lines psl,
2044: psp_report_template_details_h prtd ,
2045: gms_awards_all gaa ,
2046: per_time_periods ptp
2047: WHERE psl.award_id = gaa.award_id AND
2048: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND

Line 2082: psp_report_template_details_h prtd ,

2078: --- replaced non-performant delete with this for 4429787
2079: DELETE FROM psp_selected_persons_t WHERE request_id = p_request_id AND person_id NOT IN (
2080: SELECT NVL(psl.person_id,0)
2081: FROM psp_summary_lines psl,
2082: psp_report_template_details_h prtd ,
2083: pa_projects_all ppa ,
2084: per_time_periods ptp
2085: WHERE psl.project_id = ppa.project_id AND
2086: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND

Line 2120: psp_report_template_details_h prtd ,

2116: --- replaced non-performant insert with this for 4429787
2117: delete from psp_selected_persons_t where request_id = p_request_id AND person_id not in (
2118: select nvl( psl.person_id ,0)
2119: FROM psp_summary_lines psl,
2120: psp_report_template_details_h prtd ,
2121: per_time_periods ptp
2122: WHERE
2123: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
2124: prtd.criteria_lookup_code='PRJ' AND

Line 2159: psp_report_template_details_h prtd,

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'
2163: AND include_exclude_flag='I'

Line 2198: psp_report_template_details_h prtd,

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'
2202: AND paf.assignment_type = 'E'

Line 2234: psp_report_template_details_h prtd,

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'
2238: AND include_exclude_flag='I'

Line 2275: FROM psp_report_template_details_h prtd

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)
2275: FROM psp_report_template_details_h prtd
2276: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2277: AND criteria_lookup_code='CST'
2278: AND include_exclude_flag='I'
2279: AND request_id = p_request_id)

Line 2315: FROM psp_report_template_details_h prtd

2311: hr_assignment_sets has,
2312: hr_assignment_set_amendments hasa,
2313: per_assignment_status_types past
2314: WHERE has.assignment_set_id IN (SELECT TO_NUMBER(criteria_value1)
2315: FROM psp_report_template_details_h prtd
2316: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2317: AND criteria_lookup_code='AST'
2318: AND include_exclude_flag='I'
2319: AND request_id =p_request_id)

Line 2365: psp_report_template_details_h prtd,

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'
2369: AND include_exclude_flag='I'

Line 2402: psp_report_template_details_h prtd,

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'
2406: AND paf.assignment_type = 'E'

Line 2441: psp_report_template_details_h prtd,

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'
2445: AND include_exclude_flag='I'

Line 2495: into g_exec_string from psp_report_template_details_h ;

2491:
2492: /*
2493: select l_sql_string
2494: || ' OR ' || l_criteria_value1 || ' = ' || ''''|| l_criteria_value2 ||''''
2495: into g_exec_string from psp_report_template_details_h ;
2496:
2497:
2498: */
2499:

Line 2633: psp_report_template_details_h where request_id = p_request_id and

2629: PROCEDURE APPLY_EXCLUSION_CRITERIA(P_REQUEST_ID IN NUMBER, P_EFFORT_START IN DATE , P_EFFORT_END IN DATE, P_BUSINESS_GROUP_ID IN NUMBER, P_SET_OF_BOOKS_ID IN NUMBER) IS
2630:
2631: CURSOR PPG_CURSOR IS
2632: select criteria_value1, criteria_value2 from
2633: psp_report_template_details_h where request_id = p_request_id and
2634: criteria_lookup_type= 'PSP_SELECTION_CRITERIA' and criteria_lookup_code='PPG'
2635: and include_exclude_flag='E';
2636:
2637:

Line 2640: psp_report_template_details_h where request_id = p_request_id and

2636:
2637:
2638: CURSOR GLA_CURSOR IS
2639: select criteria_value1 , criteria_value2, criteria_value3 from
2640: psp_report_template_details_h where request_id = p_request_id and
2641: criteria_lookup_type ='PSP_SELECTION_CRITERIA' and criteria_lookup_code ='GLA'
2642: and include_exclude_flag='E';
2643:
2644:

Line 2676: psp_report_template_details_h prtd,

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

Line 2718: FROM psp_report_template_details_h prtd

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'
2720: AND criteria_lookup_code='EMP'
2721: AND include_exclude_flag='E'
2722: AND prtd.request_id = p_request_id)

Line 2754: psp_report_template_details_h prtd,

2750: DELETE FROM psp_selected_persons_t
2751: WHERE request_id = p_request_id
2752: AND person_id IN (SELECT NVL(person_id,0)
2753: FROM per_all_assignments_f paf,
2754: psp_report_template_details_h prtd,
2755: per_assignment_status_types past
2756: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2757: AND criteria_lookup_code='SUP'
2758: AND include_exclude_flag='E'

Line 2789: psp_report_template_details_h prtd ,

2785: --- replaced original query for performance issues -- 4429787
2786: delete from psp_selected_persons_t where request_id = p_request_id AND person_id in (
2787: select psl.person_id
2788: FROM psp_summary_lines psl,
2789: psp_report_template_details_h prtd ,
2790: per_time_periods ptp
2791: WHERE
2792: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
2793: prtd.criteria_lookup_code='AWD' AND

Line 2826: psp_report_template_details_h prtd ,

2822: --- replaced non-performant delete with this for 4429787
2823: DELETE FROM psp_selected_persons_t WHERE request_id = p_request_id AND person_id IN (
2824: SELECT NVL(psl.person_id,0)
2825: FROM psp_summary_lines psl,
2826: psp_report_template_details_h prtd ,
2827: gms_awards_all gaa ,
2828: per_time_periods ptp
2829: WHERE psl.award_id = gaa.award_id AND
2830: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND

Line 2864: psp_report_template_details_h prtd ,

2860: --- replaced non-performant delete with this for 4429787
2861: DELETE FROM psp_selected_persons_t WHERE request_id = p_request_id AND person_id IN (
2862: SELECT NVL(psl.person_id,0)
2863: FROM psp_summary_lines psl,
2864: psp_report_template_details_h prtd ,
2865: pa_projects_all ppa ,
2866: per_time_periods ptp
2867: WHERE psl.project_id = ppa.project_id AND
2868: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND

Line 2902: psp_report_template_details_h prtd ,

2898: --- replaced non-performant insert with this for 4429787
2899: delete from psp_selected_persons_t where request_id = p_request_id AND person_id in (
2900: select nvl( psl.person_id ,0)
2901: FROM psp_summary_lines psl,
2902: psp_report_template_details_h prtd ,
2903: per_time_periods ptp
2904: WHERE
2905: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
2906: prtd.criteria_lookup_code='PRJ' AND

Line 2940: psp_report_template_details_h prtd,

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'
2944: AND include_exclude_flag='E'

Line 2975: psp_report_template_details_h prtd,

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'
2979: AND include_exclude_flag='E'

Line 3010: psp_report_template_details_h prtd,

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'
3014: AND include_exclude_flag='E'

Line 3049: psp_report_template_details_h prtd,

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'
3053: AND include_exclude_flag='E'

Line 3084: psp_report_template_details_h prtd,

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'
3088: AND include_exclude_flag='E'

Line 3118: psp_report_template_details_h prtd,

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'
3122: AND include_exclude_flag='E'

Line 3157: FROM psp_report_template_details_h prtd

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)
3157: FROM psp_report_template_details_h prtd
3158: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3159: AND criteria_lookup_code='CST'
3160: AND include_exclude_flag='E'
3161: AND request_id = p_request_id)

Line 3197: FROM psp_report_template_details_h prtd

3193: hr_assignment_sets has,
3194: hr_assignment_set_amendments hasa,
3195: per_assignment_status_types past
3196: WHERE has.assignment_set_id IN (SELECT TO_NUMBER(criteria_value1)
3197: FROM psp_report_template_details_h prtd
3198: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3199: AND criteria_lookup_code='AST'
3200: AND include_exclude_flag='E'
3201: AND request_id =p_request_id)

Line 3263: into g_exec_string from psp_report_template_details_h ;

3259: g_exec_string := l_sql_string || ' OR '|| l_criteria_value1 || ' = ' || '''' || l_criteria_value2||'''';
3260: /*
3261: select l_sql_string
3262: || ' OR ' || l_criteria_value1 || ' = ' || ''''|| l_criteria_value2 || ''''
3263: into g_exec_string from psp_report_template_details_h ;
3264:
3265: */
3266: l_sql_string:= g_exec_string;
3267:

Line 3435: CURSOR get_ff_for_template_csr is select criteria_value1 from psp_report_template_details_h where

3431:
3432: CURSOR select_everyone_csr is select person_id , assignment_id, p_request_id, p_effort_start, p_effort_end from psp_selected_persons_t where
3433: request_id =p_request_id;
3434:
3435: CURSOR get_ff_for_template_csr is select criteria_value1 from psp_report_template_details_h where
3436: request_id = p_request_id and criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code='FFE';
3437:
3438: BEGIN
3439:

Line 3597: FROM psp_report_template_details_h

3593:
3594:
3595: CURSOR get_selection_cardinality_csr(p_request_id IN NUMBER) IS
3596: SELECT DISTINCT criteria_lookup_code
3597: FROM psp_report_template_details_h
3598: WHERE request_id = p_request_id
3599: AND include_exclude_flag = 'I'
3600: AND criteria_lookup_type = 'PSP_SELECTION_CRITERIA';
3601:

Line 3606: FROM psp_report_template_details_h

3602: /* The below cursors would only be used only when no statis selection criteria have been chosen */
3603: CURSOR ppg_cursor IS
3604: SELECT criteria_value1,
3605: criteria_value2
3606: FROM psp_report_template_details_h
3607: WHERE request_id = p_request_id
3608: AND include_exclude_flag='I'
3609: AND criteria_lookup_type= 'PSP_SELECTION_CRITERIA'
3610: AND criteria_lookup_code='PPG';

Line 3616: FROM psp_report_template_details_h

3612: CURSOR gla_cursor IS
3613: SELECT criteria_value1,
3614: criteria_value2,
3615: criteria_value3
3616: FROM psp_report_template_details_h
3617: WHERE request_id = p_request_id
3618: AND include_exclude_flag='I'
3619: AND criteria_lookup_type ='PSP_SELECTION_CRITERIA'
3620: AND criteria_lookup_code ='GLA';

Line 3635: FROM psp_report_template_details_h prtd

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'
3638: AND include_exclude_flag='I'
3639: AND request_id = p_request_id)

Line 3651: FROM psp_report_template_details_h prtd

3647: INSERT INTO psp_selection_cardinality_gt (lookup_code, total_count)
3648: SELECT 'EMP', COUNT(DISTINCT paf.assignment_id)
3649: FROM per_all_assignments_f paf
3650: WHERE paf.person_id IN (SELECT TO_NUMBER(criteria_value1)
3651: FROM psp_report_template_details_h prtd
3652: WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
3653: AND criteria_lookup_code='EMP'
3654: AND include_exclude_flag='I'
3655: AND request_id = p_request_id)

Line 3665: FROM psp_report_template_details_h prtd

3661: (lookup_code, total_count)
3662: SELECT 'SUP', COUNT(DISTINCT assignment_id)
3663: FROM per_all_assignments_f paf
3664: WHERE supervisor_id IN (SELECT TO_NUMBER(criteria_value1)
3665: FROM psp_report_template_details_h prtd
3666: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3667: AND criteria_lookup_code='SUP'
3668: AND request_id = p_request_id
3669: AND include_exclude_flag='I')

Line 3678: psp_report_template_details_h prtd ,

3674: INSERT INTO psp_selection_cardinality_gt
3675: (lookup_code, total_count)
3676: SELECT 'AWD', COUNT(DISTINCT psl.assignment_id)
3677: FROM psp_summary_lines psl,
3678: psp_report_template_details_h prtd ,
3679: per_time_periods ptp
3680: WHERE
3681: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
3682: AND prtd.criteria_lookup_code='AWD'

Line 3716: psp_report_template_details_h prtd ,

3712: INSERT INTO psp_selection_cardinality_gt
3713: (lookup_code, total_count)
3714: SELECT 'ATY', COUNT(DISTINCT psl.assignment_id)
3715: FROM psp_summary_lines psl,
3716: psp_report_template_details_h prtd ,
3717: gms_awards_all gaa,
3718: per_time_periods ptp
3719: WHERE psl.award_id = gaa.award_id
3720: AND prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'

Line 3755: psp_report_template_details_h prtd ,

3751: INSERT INTO psp_selection_cardinality_gt
3752: (lookup_code, total_count)
3753: SELECT 'PRT', COUNT(DISTINCT psl.assignment_id)
3754: FROM psp_summary_lines psl,
3755: psp_report_template_details_h prtd ,
3756: pa_projects_all ppa ,
3757: per_time_periods ptp
3758: WHERE psl.project_id = ppa.project_id
3759: AND prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'

Line 3794: psp_report_template_details_h prtd ,

3790: INSERT INTO psp_selection_cardinality_gt
3791: (lookup_code, total_count)
3792: SELECT 'PRJ', COUNT(DISTINCT psl.assignment_id)
3793: FROM psp_summary_lines psl,
3794: psp_report_template_details_h prtd ,
3795: per_time_periods ptp
3796: WHERE
3797: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
3798: AND prtd.criteria_lookup_code='PRJ'

Line 3830: FROM psp_report_template_details_h prtd

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'
3833: AND include_exclude_flag='I' AND request_id = p_request_id)
3834: AND paf.assignment_type = 'E'

Line 3843: FROM psp_report_template_details_h prtd

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'
3846: AND include_exclude_flag='I'
3847: AND request_id = p_request_id)

Line 3857: FROM psp_report_template_details_h prtd

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'
3860: AND include_exclude_flag='I'
3861: AND request_id = p_request_id)

Line 3871: FROM psp_report_template_details_h prtd

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'
3874: AND include_exclude_flag='I'
3875: AND request_id=p_request_id)

Line 3885: FROM psp_report_template_details_h prtd

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'
3888: AND include_exclude_flag='I'
3889: AND request_id = p_request_id)

Line 3899: FROM psp_report_template_details_h prtd

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'
3902: AND include_exclude_flag='I'
3903: AND request_id = p_request_id)

Line 3915: FROM psp_report_template_details_h prtd

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
3916: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3917: AND criteria_lookup_code='CST'
3918: AND include_exclude_flag='I'
3919: AND request_id = p_request_id)

Line 3933: FROM psp_report_template_details_h prtd

3929: FROM per_all_assignments_f paf,
3930: hr_assignment_sets has ,
3931: hr_assignment_set_amendments hasa
3932: WHERE has.assignment_set_id IN (SELECT TO_NUMBER(criteria_value1)
3933: FROM psp_report_template_details_h prtd
3934: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3935: AND criteria_lookup_code='AST'
3936: AND include_exclude_flag='I'
3937: AND request_id =p_request_id)

Line 4110: FROM psp_report_template_details_h

4106: p_set_of_books_id IN NUMBER) IS
4107: PRAGMA AUTONOMOUS_TRANSACTION;
4108: CURSOR PPG_CURSOR IS
4109: SELECT criteria_value1, criteria_value2
4110: FROM psp_report_template_details_h
4111: WHERE request_id = p_request_id
4112: AND criteria_lookup_type= 'PSP_SELECTION_CRITERIA' AND criteria_lookup_code='PPG'
4113: AND include_exclude_flag='I';
4114:

Line 4118: FROM psp_report_template_details_h

4114:
4115:
4116: CURSOR GLA_CURSOR IS
4117: SELECT criteria_value1 , criteria_value2, criteria_value3
4118: FROM psp_report_template_details_h
4119: WHERE request_id = p_request_id
4120: AND criteria_lookup_type ='PSP_SELECTION_CRITERIA' AND criteria_lookup_code ='GLA'
4121: AND include_exclude_flag='I';
4122:

Line 4138: FROM psp_report_template_details_h prtd

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'
4140: AND criteria_lookup_code='PTY'
4141: AND request_id = p_request_id
4142: AND include_exclude_flag='I')

Line 4175: FROM psp_report_template_details_h prtd

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'
4177: AND criteria_lookup_code='EMP'
4178: AND include_exclude_flag='I'
4179: AND request_id = p_request_id)

Line 4211: FROM psp_report_template_details_h prtd

4207: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4208: FROM per_all_assignments_f paf,
4209: per_assignment_status_types past
4210: WHERE supervisor_id IN (SELECT TO_NUMBER(criteria_value1)
4211: FROM psp_report_template_details_h prtd
4212: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
4213: AND criteria_lookup_code='SUP'
4214: AND include_exclude_flag='I'
4215: AND request_id = p_request_id)

Line 4241: psp_report_template_details_h prtd,

4237: ELSIF g_lookup_code='AWD' THEN
4238: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4239: SELECT DISTINCT p_request_id, psl.person_id, psl.assignment_id
4240: FROM psp_summary_lines psl,
4241: psp_report_template_details_h prtd,
4242: per_time_periods ptp
4243: WHERE
4244: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
4245: AND prtd.criteria_lookup_code='AWD'

Line 4277: psp_report_template_details_h prtd,

4273: ELSIF g_lookup_code ='ATY' THEN
4274: INSERT INTO psp_selected_persons_t (request_id , person_id, assignment_id)
4275: SELECT DISTINCT p_request_id, psl.person_id, psl.assignment_id
4276: FROM psp_summary_lines psl,
4277: psp_report_template_details_h prtd,
4278: gms_awards_all gaa,
4279: per_time_periods ptp
4280: WHERE psl.award_id = gaa.award_id
4281: AND prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'

Line 4315: psp_report_template_details_h prtd,

4311: ELSIF g_lookup_code ='PRT' THEN
4312: INSERT INTO psp_selected_persons_t (request_id , person_id, assignment_id)
4313: SELECT DISTINCT p_request_id, psl.person_id, psl.assignment_id
4314: FROM psp_summary_lines psl,
4315: psp_report_template_details_h prtd,
4316: pa_projects_all ppa,
4317: per_time_periods ptp
4318: WHERE psl.project_id = ppa.project_id
4319: AND prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'

Line 4352: psp_report_template_details_h prtd,

4348: ELSIF g_lookup_code ='PRJ' THEN
4349: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4350: SELECT DISTINCT p_request_id, psl.person_id, psl.assignment_id
4351: FROM psp_summary_lines psl,
4352: psp_report_template_details_h prtd,
4353: per_time_periods ptp
4354: WHERE
4355: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
4356: AND prtd.criteria_lookup_code='PRJ'

Line 4391: FROM psp_report_template_details_h prtd

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'
4393: AND criteria_lookup_code='PAY'
4394: AND include_exclude_flag='I'
4395: AND request_id = p_request_id)

Line 4424: FROM psp_report_template_details_h prtd

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'
4426: AND criteria_lookup_code='LOC'
4427: AND include_exclude_flag='I'
4428: AND request_id = p_request_id)

Line 4456: FROM psp_report_template_details_h prtd

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'
4458: AND criteria_lookup_code='ORG'
4459: AND request_id = p_request_id
4460: AND include_exclude_flag='I')

Line 4490: FROM psp_report_template_details_h prtd

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)
4490: FROM psp_report_template_details_h prtd
4491: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
4492: AND criteria_lookup_code='CST'
4493: AND include_exclude_flag='I'
4494: AND request_id = p_request_id)

Line 4527: FROM psp_report_template_details_h prtd

4523: hr_assignment_sets has,
4524: hr_assignment_set_amendments hasa,
4525: per_assignment_status_types past
4526: WHERE has.assignment_set_id IN (SELECT TO_NUMBER(criteria_value1)
4527: FROM psp_report_template_details_h prtd
4528: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
4529: AND criteria_lookup_code='AST'
4530: AND include_exclude_flag='I'
4531: AND request_id =p_request_id)

Line 4573: FROM psp_report_template_details_h prtd

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'
4575: AND criteria_lookup_code='JOB'
4576: AND include_exclude_flag='I'
4577: AND request_id = p_request_id)

Line 4605: FROM psp_report_template_details_h prtd

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'
4607: AND criteria_lookup_code='POS'
4608: AND request_id = p_request_id
4609: AND include_exclude_flag='I')

Line 4637: FROM psp_report_template_details_h prtd

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'
4639: AND criteria_lookup_code='ASS'
4640: AND request_id = p_request_id
4641: AND include_exclude_flag='I')

Line 4792: FROM psp_report_template_details_h

4788: p_set_of_books_id IN NUMBER) IS
4789: CURSOR get_all_selection_criteria(p_request_id IN NUMBER) IS
4790: SELECT distinct criteria_lookup_code,
4791: include_exclude_flag
4792: FROM psp_report_template_details_h
4793: WHERE request_id = p_request_id
4794: AND criteria_lookup_type='PSP_SELECTION_CRITERIA'
4795: ORDER BY include_exclude_flag;
4796:

Line 4799: FROM psp_report_template_details_h

4795: ORDER BY include_exclude_flag;
4796:
4797: CURSOR PPG_CURSOR IS
4798: SELECT criteria_value1, criteria_value2
4799: FROM psp_report_template_details_h
4800: WHERE request_id = p_request_id
4801: AND criteria_lookup_type= 'PSP_SELECTION_CRITERIA'
4802: AND criteria_lookup_code='PPG'
4803: AND include_exclude_flag='I';

Line 4807: FROM psp_report_template_details_h

4803: AND include_exclude_flag='I';
4804:
4805: CURSOR GLA_CURSOR IS
4806: SELECT criteria_value1 , criteria_value2, criteria_value3
4807: FROM psp_report_template_details_h
4808: WHERE request_id = p_request_id
4809: AND criteria_lookup_type ='PSP_SELECTION_CRITERIA'
4810: AND criteria_lookup_code ='GLA'
4811: AND include_exclude_flag='I';

Line 4838: psp_report_template_details_h prtd,

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

Line 4861: FROM psp_report_template_details_h prtd

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'
4864: AND include_exclude_flag='I'
4865: AND prtd.request_id = p_request_id)

Line 4880: psp_report_template_details_h prtd

4876: /* Bug 5087294 : Performance fix replacing not in with not exists */
4877: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
4878: AND NOT EXISTS ( SELECT 1
4879: FROM per_all_assignments_f paf,
4880: psp_report_template_details_h prtd
4881: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
4882: AND criteria_lookup_code='SUP'
4883: AND include_exclude_flag='I'
4884: AND paf.supervisor_id = TO_NUMBER(prtd.criteria_value1)

Line 4895: psp_report_template_details_h prtd ,

4891: DELETE FROM psp_selected_persons_t
4892: WHERE request_id = p_request_id
4893: AND assignment_id NOT IN (SELECT psl.assignment_id
4894: FROM psp_summary_lines psl,
4895: psp_report_template_details_h prtd ,
4896: per_time_periods ptp
4897: WHERE
4898: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
4899: AND prtd.criteria_lookup_code='AWD'

Line 4936: psp_report_template_details_h prtd,

4932: DELETE FROM psp_selected_persons_t
4933: WHERE request_id = p_request_id
4934: AND assignment_id NOT IN (SELECT NVL(psl.assignment_id,0)
4935: FROM psp_summary_lines psl,
4936: psp_report_template_details_h prtd,
4937: gms_awards_all gaa,
4938: per_time_periods ptp
4939: WHERE psl.award_id = gaa.award_id
4940: AND prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'

Line 4978: psp_report_template_details_h prtd,

4974: DELETE FROM psp_selected_persons_t
4975: WHERE request_id = p_request_id
4976: AND assignment_id NOT IN (SELECT NVL(psl.assignment_id,0)
4977: FROM psp_summary_lines psl,
4978: psp_report_template_details_h prtd,
4979: pa_projects_all ppa,
4980: per_time_periods ptp
4981: WHERE psl.project_id = ppa.project_id
4982: AND prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'

Line 5020: psp_report_template_details_h prtd ,

5016: DELETE FROM psp_selected_persons_t
5017: WHERE request_id = p_request_id
5018: AND assignment_id NOT IN (SELECT NVL(psl.assignment_id ,0)
5019: FROM psp_summary_lines psl,
5020: psp_report_template_details_h prtd ,
5021: per_time_periods ptp
5022: WHERE
5023: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
5024: AND prtd.criteria_lookup_code='PRJ'

Line 5063: psp_report_template_details_h prtd

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'
5067: AND paf.assignment_type = 'E'

Line 5081: psp_report_template_details_h prtd

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'
5085: and

Line 5099: psp_report_template_details_h prtd

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'
5103: AND paf.assignment_type = 'E'

Line 5120: FROM psp_report_template_details_h prtd

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
5121: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5122: AND criteria_lookup_code='CST'
5123: AND include_exclude_flag='I'
5124: AND request_id = p_request_id)

Line 5139: FROM psp_report_template_details_h prtd

5135: FROM per_all_assignments_f paf,
5136: hr_assignment_sets has,
5137: hr_assignment_set_amendments hasa
5138: WHERE has.assignment_set_id IN (SELECT TO_NUMBER(criteria_value1)
5139: FROM psp_report_template_details_h prtd
5140: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5141: AND criteria_lookup_code='AST'
5142: AND include_exclude_flag='I'
5143: AND request_id =p_request_id)

Line 5169: psp_report_template_details_h prtd

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'
5173: AND paf.assignment_type = 'E'

Line 5187: psp_report_template_details_h prtd

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'
5191: and

Line 5205: psp_report_template_details_h prtd

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'
5209: AND paf.assignment_status_type_id = TO_NUMBER(prtd.criteria_value1)

Line 5321: FROM psp_report_template_details_h

5317: p_business_group_id IN NUMBER,
5318: p_set_of_books_id IN NUMBER) IS
5319: CURSOR PPG_CURSOR IS
5320: SELECT criteria_value1, criteria_value2
5321: FROM psp_report_template_details_h
5322: WHERE request_id = p_request_id
5323: AND criteria_lookup_type= 'PSP_SELECTION_CRITERIA'
5324: AND criteria_lookup_code='PPG'
5325: AND include_exclude_flag='E';

Line 5329: FROM psp_report_template_details_h

5325: AND include_exclude_flag='E';
5326:
5327: CURSOR GLA_CURSOR IS
5328: SELECT criteria_value1, criteria_value2, criteria_value3
5329: FROM psp_report_template_details_h
5330: WHERE request_id = p_request_id
5331: AND criteria_lookup_type ='PSP_SELECTION_CRITERIA'
5332: AND criteria_lookup_code ='GLA'
5333: AND include_exclude_flag='E';

Line 5351: psp_report_template_details_h prtd,

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

Line 5371: FROM psp_report_template_details_h prtd

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'
5374: AND include_exclude_flag='E'
5375: AND prtd.request_id = p_request_id)

Line 5387: psp_report_template_details_h prtd

5383: DELETE FROM psp_selected_persons_t
5384: WHERE request_id = p_request_id
5385: AND assignment_id IN (SELECT NVL(assignment_id,0)
5386: FROM per_all_assignments_f paf,
5387: psp_report_template_details_h prtd
5388: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5389: AND criteria_lookup_code='SUP'
5390: AND include_exclude_flag='E'
5391: AND paf.supervisor_id = TO_NUMBER(prtd.criteria_value1)

Line 5401: psp_report_template_details_h prtd,

5397: DELETE FROM psp_selected_persons_t
5398: WHERE request_id = p_request_id
5399: AND assignment_id IN (SELECT psl.assignment_id
5400: FROM psp_summary_lines psl,
5401: psp_report_template_details_h prtd,
5402: per_time_periods ptp
5403: WHERE
5404: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
5405: AND prtd.criteria_lookup_code='AWD'

Line 5439: psp_report_template_details_h prtd,

5435: DELETE FROM psp_selected_persons_t
5436: WHERE request_id = p_request_id
5437: AND assignment_id IN (SELECT NVL(psl.assignment_id,0)
5438: FROM psp_summary_lines psl,
5439: psp_report_template_details_h prtd,
5440: gms_awards_all gaa,
5441: per_time_periods ptp
5442: WHERE psl.award_id = gaa.award_id
5443: AND prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'

Line 5478: psp_report_template_details_h prtd,

5474: DELETE FROM psp_selected_persons_t
5475: WHERE request_id = p_request_id
5476: AND assignment_id IN (SELECT NVL(psl.assignment_id,0)
5477: FROM psp_summary_lines psl,
5478: psp_report_template_details_h prtd,
5479: pa_projects_all ppa,
5480: per_time_periods ptp
5481: WHERE psl.project_id = ppa.project_id
5482: AND prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'

Line 5517: psp_report_template_details_h prtd,

5513: DELETE FROM psp_selected_persons_t
5514: WHERE request_id = p_request_id
5515: AND assignment_id IN (SELECT NVL(psl.assignment_id,0)
5516: FROM psp_summary_lines psl,
5517: psp_report_template_details_h prtd,
5518: per_time_periods ptp
5519: WHERE
5520: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
5521: AND prtd.criteria_lookup_code='PRJ'

Line 5555: psp_report_template_details_h prtd

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'
5559: AND paf.assignment_type = 'E'

Line 5569: psp_report_template_details_h prtd

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'
5573: AND paf.assignment_type = 'E'

Line 5583: psp_report_template_details_h prtd

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'
5587: AND paf.assignment_type = 'E'

Line 5597: psp_report_template_details_h prtd

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'
5601: AND paf.assignment_type = 'E'

Line 5611: psp_report_template_details_h prtd

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'
5615: AND paf.assignment_type = 'E'

Line 5625: psp_report_template_details_h prtd

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'
5629: AND paf.assignment_type = 'E'

Line 5642: FROM psp_report_template_details_h prtd

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
5643: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5644: AND criteria_lookup_code='CST'
5645: AND include_exclude_flag='E'
5646: AND request_id = p_request_id)

Line 5659: FROM psp_report_template_details_h prtd

5655: AND assignment_id IN (SELECT NVL(paf.assignment_id,0)
5656: FROM per_all_assignments_f paf, hr_assignment_sets has,
5657: hr_assignment_set_amendments hasa
5658: WHERE has.assignment_set_id IN (SELECT TO_NUMBER(criteria_value1)
5659: FROM psp_report_template_details_h prtd
5660: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5661: AND criteria_lookup_code='AST'
5662: AND include_exclude_flag='E'
5663: AND request_id =p_request_id)