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 1861: psp_report_template_details_h where request_id = p_request_id and

1857:
1858: CURSOR get_all_selection_criteria(p_request_id IN NUMBER) is
1859: SELECT distinct criteria_lookup_code,
1860: include_exclude_flag from
1861: psp_report_template_details_h where request_id = p_request_id and
1862: criteria_lookup_type='PSP_SELECTION_CRITERIA' ORDER BY include_exclude_flag;
1863:
1864:
1865: CURSOR PPG_CURSOR IS

Line 1867: psp_report_template_details_h where request_id = p_request_id and

1863:
1864:
1865: CURSOR PPG_CURSOR IS
1866: select criteria_value1, criteria_value2 from
1867: psp_report_template_details_h where request_id = p_request_id and
1868: criteria_lookup_type= 'PSP_SELECTION_CRITERIA' and criteria_lookup_code='PPG' and include_exclude_flag='I';
1869:
1870:
1871: CURSOR GLA_CURSOR IS

Line 1873: psp_report_template_details_h where request_id = p_request_id and

1869:
1870:
1871: CURSOR GLA_CURSOR IS
1872: select criteria_value1 , criteria_value2, criteria_value3 from
1873: psp_report_template_details_h where request_id = p_request_id and
1874: criteria_lookup_type ='PSP_SELECTION_CRITERIA' and criteria_lookup_code ='GLA' and include_exclude_flag='I';
1875:
1876:
1877: type t_varchar_30_type is TABLE Of VARCHAR2(30) INDEX BY BINARY_INTEGER;

Line 1919: psp_report_template_details_h prtd,

1915: /* Bug 5087294 : Performance fix replacing not in with not exists */
1916: -- AND person_id NOT IN (SELECT NVL( person_id, 0)
1917: AND NOT EXISTS ( SELECT 1
1918: FROM per_people_f ppf,
1919: psp_report_template_details_h prtd,
1920: per_assignments_f paf,
1921: per_assignment_status_types past
1922: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
1923: AND criteria_lookup_code='PTY'

Line 1963: FROM psp_report_template_details_h prtd

1959: FROM per_all_people_f ppf,
1960: per_assignments_f paf,
1961: per_assignment_status_types past
1962: WHERE ppf.person_id IN (SELECT TO_NUMBER(criteria_value1)
1963: FROM psp_report_template_details_h prtd
1964: WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
1965: AND criteria_lookup_code='EMP'
1966: AND include_exclude_flag='I'
1967: AND prtd.request_id = p_request_id)

Line 2002: psp_report_template_details_h prtd,

1998: /* Bug 5087294 : Performance fix replacing not in with not exists */
1999: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2000: AND NOT EXISTS ( SELECT 1
2001: FROM per_all_assignments_f paf,
2002: psp_report_template_details_h prtd,
2003: per_assignment_status_types past
2004: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2005: AND criteria_lookup_code='SUP'
2006: AND include_exclude_flag='I'

Line 2039: psp_report_template_details_h prtd ,

2035: --- replaced original query for performance issues -- 4429787
2036: delete from psp_selected_persons_t where request_id = p_request_id AND person_id not in (
2037: select psl.person_id
2038: FROM psp_summary_lines psl,
2039: psp_report_template_details_h prtd ,
2040: per_time_periods ptp
2041: WHERE
2042: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
2043: prtd.criteria_lookup_code='AWD' AND

Line 2077: psp_report_template_details_h prtd ,

2073: --- replaced non-performant delete with this for 4429787
2074: DELETE FROM psp_selected_persons_t WHERE request_id = p_request_id AND person_id NOT IN (
2075: SELECT NVL(psl.person_id,0)
2076: FROM psp_summary_lines psl,
2077: psp_report_template_details_h prtd ,
2078: gms_awards_all gaa ,
2079: per_time_periods ptp
2080: WHERE psl.award_id = gaa.award_id AND
2081: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND

Line 2115: psp_report_template_details_h prtd ,

2111: --- replaced non-performant delete with this for 4429787
2112: DELETE FROM psp_selected_persons_t WHERE request_id = p_request_id AND person_id NOT IN (
2113: SELECT NVL(psl.person_id,0)
2114: FROM psp_summary_lines psl,
2115: psp_report_template_details_h prtd ,
2116: pa_projects_all ppa ,
2117: per_time_periods ptp
2118: WHERE psl.project_id = ppa.project_id AND
2119: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND

Line 2153: psp_report_template_details_h prtd ,

2149: --- replaced non-performant insert with this for 4429787
2150: delete from psp_selected_persons_t where request_id = p_request_id AND person_id not in (
2151: select nvl( psl.person_id ,0)
2152: FROM psp_summary_lines psl,
2153: psp_report_template_details_h prtd ,
2154: per_time_periods ptp
2155: WHERE
2156: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
2157: prtd.criteria_lookup_code='PRJ' AND

Line 2192: psp_report_template_details_h prtd,

2188: /* Bug 5087294 : Performance fix replacing not in with not exists */
2189: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2190: AND NOT EXISTS ( SELECT 1
2191: FROM per_assignments_f paf,
2192: psp_report_template_details_h prtd,
2193: per_assignment_status_types past
2194: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2195: AND criteria_lookup_code='PAY'
2196: AND include_exclude_flag='I'

Line 2231: psp_report_template_details_h prtd,

2227: /* Bug 5087294 : Performance fix replacing not in with not exists */
2228: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2229: AND NOT EXISTS ( SELECT 1
2230: FROM per_assignments_f paf ,
2231: psp_report_template_details_h prtd,
2232: per_assignment_status_types past
2233: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2234: AND criteria_lookup_code='LOC'
2235: AND paf.assignment_type = 'E'

Line 2267: psp_report_template_details_h prtd,

2263: WHERE request_id = p_request_id
2264: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2265: AND NOT EXISTS ( SELECT 1
2266: FROM per_assignments_f paf ,
2267: psp_report_template_details_h prtd,
2268: per_assignment_status_types past
2269: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2270: AND criteria_lookup_code='ORG'
2271: AND include_exclude_flag='I'

Line 2308: FROM psp_report_template_details_h prtd

2304: pay_payrolls_f ppf,
2305: per_assignment_status_types past
2306: WHERE ppf.payroll_id = paf.payroll_id
2307: AND ppf.consolidation_set_id IN (SELECT TO_NUMBER(criteria_value1)
2308: FROM psp_report_template_details_h prtd
2309: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2310: AND criteria_lookup_code='CST'
2311: AND include_exclude_flag='I'
2312: AND request_id = p_request_id)

Line 2348: FROM psp_report_template_details_h prtd

2344: hr_assignment_sets has,
2345: hr_assignment_set_amendments hasa,
2346: per_assignment_status_types past
2347: WHERE has.assignment_set_id IN (SELECT TO_NUMBER(criteria_value1)
2348: FROM psp_report_template_details_h prtd
2349: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2350: AND criteria_lookup_code='AST'
2351: AND include_exclude_flag='I'
2352: AND request_id =p_request_id)

Line 2398: psp_report_template_details_h prtd,

2394: /* Bug 5087294 : Performance fix replacing not in with not exists */
2395: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2396: AND NOT EXISTS ( SELECT 1
2397: FROM per_assignments_f paf ,
2398: psp_report_template_details_h prtd,
2399: per_assignment_status_types past
2400: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2401: AND criteria_lookup_code='JOB'
2402: AND include_exclude_flag='I'

Line 2435: psp_report_template_details_h prtd,

2431: /* Bug 5087294 : Performance fix replacing not in with not exists */
2432: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2433: AND NOT EXISTS ( SELECT 1
2434: FROM per_assignments_f paf ,
2435: psp_report_template_details_h prtd,
2436: per_assignment_status_types past
2437: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2438: AND criteria_lookup_code='POS'
2439: AND paf.assignment_type = 'E'

Line 2474: psp_report_template_details_h prtd,

2470: /* Bug 5087294 : Performance fix replacing not in with not exists */
2471: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2472: AND NOT EXISTS ( SELECT 1
2473: FROM per_assignments_f paf ,
2474: psp_report_template_details_h prtd,
2475: per_assignment_status_types past
2476: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2477: AND criteria_lookup_code='ASS'
2478: AND include_exclude_flag='I'

Line 2528: into g_exec_string from psp_report_template_details_h ;

2524:
2525: /*
2526: select l_sql_string
2527: || ' OR ' || l_criteria_value1 || ' = ' || ''''|| l_criteria_value2 ||''''
2528: into g_exec_string from psp_report_template_details_h ;
2529:
2530:
2531: */
2532:

Line 2666: psp_report_template_details_h where request_id = p_request_id and

2662: 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
2663:
2664: CURSOR PPG_CURSOR IS
2665: select criteria_value1, criteria_value2 from
2666: psp_report_template_details_h where request_id = p_request_id and
2667: criteria_lookup_type= 'PSP_SELECTION_CRITERIA' and criteria_lookup_code='PPG'
2668: and include_exclude_flag='E';
2669:
2670:

Line 2673: psp_report_template_details_h where request_id = p_request_id and

2669:
2670:
2671: CURSOR GLA_CURSOR IS
2672: select criteria_value1 , criteria_value2, criteria_value3 from
2673: psp_report_template_details_h where request_id = p_request_id and
2674: criteria_lookup_type ='PSP_SELECTION_CRITERIA' and criteria_lookup_code ='GLA'
2675: and include_exclude_flag='E';
2676:
2677:

Line 2709: psp_report_template_details_h prtd,

2705: DELETE FROM psp_selected_persons_t
2706: WHERE request_id = p_request_id
2707: AND person_id IN (SELECT ppf.person_id
2708: FROM per_people_f ppf,
2709: psp_report_template_details_h prtd,
2710: per_assignments_f paf,
2711: per_assignment_status_types past
2712: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2713: AND criteria_lookup_code='PTY'

Line 2751: FROM psp_report_template_details_h prtd

2747: FROM per_all_people_f ppf,
2748: per_assignments_f paf,
2749: per_assignment_status_types past
2750: WHERE ppf.person_id IN (SELECT TO_NUMBER(criteria_value1)
2751: FROM psp_report_template_details_h prtd
2752: WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
2753: AND criteria_lookup_code='EMP'
2754: AND include_exclude_flag='E'
2755: AND prtd.request_id = p_request_id)

Line 2787: psp_report_template_details_h prtd,

2783: DELETE FROM psp_selected_persons_t
2784: WHERE request_id = p_request_id
2785: AND person_id IN (SELECT NVL(person_id,0)
2786: FROM per_all_assignments_f paf,
2787: psp_report_template_details_h prtd,
2788: per_assignment_status_types past
2789: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2790: AND criteria_lookup_code='SUP'
2791: AND include_exclude_flag='E'

Line 2822: psp_report_template_details_h prtd ,

2818: --- replaced original query for performance issues -- 4429787
2819: delete from psp_selected_persons_t where request_id = p_request_id AND person_id in (
2820: select psl.person_id
2821: FROM psp_summary_lines psl,
2822: psp_report_template_details_h prtd ,
2823: per_time_periods ptp
2824: WHERE
2825: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
2826: prtd.criteria_lookup_code='AWD' AND

Line 2859: psp_report_template_details_h prtd ,

2855: --- replaced non-performant delete with this for 4429787
2856: DELETE FROM psp_selected_persons_t WHERE request_id = p_request_id AND person_id IN (
2857: SELECT NVL(psl.person_id,0)
2858: FROM psp_summary_lines psl,
2859: psp_report_template_details_h prtd ,
2860: gms_awards_all gaa ,
2861: per_time_periods ptp
2862: WHERE psl.award_id = gaa.award_id AND
2863: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND

Line 2897: psp_report_template_details_h prtd ,

2893: --- replaced non-performant delete with this for 4429787
2894: DELETE FROM psp_selected_persons_t WHERE request_id = p_request_id AND person_id IN (
2895: SELECT NVL(psl.person_id,0)
2896: FROM psp_summary_lines psl,
2897: psp_report_template_details_h prtd ,
2898: pa_projects_all ppa ,
2899: per_time_periods ptp
2900: WHERE psl.project_id = ppa.project_id AND
2901: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND

Line 2935: psp_report_template_details_h prtd ,

2931: --- replaced non-performant insert with this for 4429787
2932: delete from psp_selected_persons_t where request_id = p_request_id AND person_id in (
2933: select nvl( psl.person_id ,0)
2934: FROM psp_summary_lines psl,
2935: psp_report_template_details_h prtd ,
2936: per_time_periods ptp
2937: WHERE
2938: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
2939: prtd.criteria_lookup_code='PRJ' AND

Line 2973: psp_report_template_details_h prtd,

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

Line 3008: psp_report_template_details_h prtd,

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

Line 3043: psp_report_template_details_h prtd,

3039: DELETE FROM psp_selected_persons_t
3040: WHERE request_id = p_request_id
3041: AND person_id IN (SELECT person_id
3042: FROM per_assignments_f paf,
3043: psp_report_template_details_h prtd,
3044: per_assignment_status_types past
3045: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3046: AND criteria_lookup_code='ORG'
3047: AND include_exclude_flag='E'

Line 3082: psp_report_template_details_h prtd,

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

Line 3117: psp_report_template_details_h prtd,

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

Line 3151: psp_report_template_details_h prtd,

3147: DELETE FROM psp_selected_persons_t
3148: WHERE request_id = p_request_id
3149: AND person_id IN (SELECT person_id
3150: FROM per_assignments_f paf,
3151: psp_report_template_details_h prtd,
3152: per_assignment_status_types past
3153: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3154: AND criteria_lookup_code='ASS'
3155: AND include_exclude_flag='E'

Line 3190: FROM psp_report_template_details_h prtd

3186: pay_payrolls_f ppf,
3187: per_assignment_status_types past
3188: WHERE ppf.payroll_id = paf.payroll_id
3189: AND ppf.consolidation_set_id IN (SELECT TO_NUMBER(criteria_value1)
3190: FROM psp_report_template_details_h prtd
3191: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3192: AND criteria_lookup_code='CST'
3193: AND include_exclude_flag='E'
3194: AND request_id = p_request_id)

Line 3230: FROM psp_report_template_details_h prtd

3226: hr_assignment_sets has,
3227: hr_assignment_set_amendments hasa,
3228: per_assignment_status_types past
3229: WHERE has.assignment_set_id IN (SELECT TO_NUMBER(criteria_value1)
3230: FROM psp_report_template_details_h prtd
3231: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3232: AND criteria_lookup_code='AST'
3233: AND include_exclude_flag='E'
3234: AND request_id =p_request_id)

Line 3296: into g_exec_string from psp_report_template_details_h ;

3292: g_exec_string := l_sql_string || ' OR '|| l_criteria_value1 || ' = ' || '''' || l_criteria_value2||'''';
3293: /*
3294: select l_sql_string
3295: || ' OR ' || l_criteria_value1 || ' = ' || ''''|| l_criteria_value2 || ''''
3296: into g_exec_string from psp_report_template_details_h ;
3297:
3298: */
3299: l_sql_string:= g_exec_string;
3300:

Line 3468: CURSOR get_ff_for_template_csr is select criteria_value1 from psp_report_template_details_h where

3464:
3465: 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
3466: request_id =p_request_id;
3467:
3468: CURSOR get_ff_for_template_csr is select criteria_value1 from psp_report_template_details_h where
3469: request_id = p_request_id and criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code='FFE';
3470:
3471: BEGIN
3472:

Line 3630: FROM psp_report_template_details_h

3626:
3627:
3628: CURSOR get_selection_cardinality_csr(p_request_id IN NUMBER) IS
3629: SELECT DISTINCT criteria_lookup_code
3630: FROM psp_report_template_details_h
3631: WHERE request_id = p_request_id
3632: AND include_exclude_flag = 'I'
3633: AND criteria_lookup_type = 'PSP_SELECTION_CRITERIA';
3634:

Line 3639: FROM psp_report_template_details_h

3635: /* The below cursors would only be used only when no statis selection criteria have been chosen */
3636: CURSOR ppg_cursor IS
3637: SELECT criteria_value1,
3638: criteria_value2
3639: FROM psp_report_template_details_h
3640: WHERE request_id = p_request_id
3641: AND include_exclude_flag='I'
3642: AND criteria_lookup_type= 'PSP_SELECTION_CRITERIA'
3643: AND criteria_lookup_code='PPG';

Line 3649: FROM psp_report_template_details_h

3645: CURSOR gla_cursor IS
3646: SELECT criteria_value1,
3647: criteria_value2,
3648: criteria_value3
3649: FROM psp_report_template_details_h
3650: WHERE request_id = p_request_id
3651: AND include_exclude_flag='I'
3652: AND criteria_lookup_type ='PSP_SELECTION_CRITERIA'
3653: AND criteria_lookup_code ='GLA';

Line 3668: FROM psp_report_template_details_h prtd

3664: SELECT 'PTY', COUNT(DISTINCT assignment_id)
3665: FROM per_people_f ppf,
3666: per_assignments_f paf
3667: WHERE person_type_id IN (SELECT TO_NUMBER(criteria_value1)
3668: FROM psp_report_template_details_h prtd
3669: WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
3670: AND criteria_lookup_code ='PTY'
3671: AND include_exclude_flag='I'
3672: AND request_id = p_request_id)

Line 3684: FROM psp_report_template_details_h prtd

3680: INSERT INTO psp_selection_cardinality_gt (lookup_code, total_count)
3681: SELECT 'EMP', COUNT(DISTINCT paf.assignment_id)
3682: FROM per_all_assignments_f paf
3683: WHERE paf.person_id IN (SELECT TO_NUMBER(criteria_value1)
3684: FROM psp_report_template_details_h prtd
3685: WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
3686: AND criteria_lookup_code='EMP'
3687: AND include_exclude_flag='I'
3688: AND request_id = p_request_id)

Line 3698: FROM psp_report_template_details_h prtd

3694: (lookup_code, total_count)
3695: SELECT 'SUP', COUNT(DISTINCT assignment_id)
3696: FROM per_all_assignments_f paf
3697: WHERE supervisor_id IN (SELECT TO_NUMBER(criteria_value1)
3698: FROM psp_report_template_details_h prtd
3699: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3700: AND criteria_lookup_code='SUP'
3701: AND request_id = p_request_id
3702: AND include_exclude_flag='I')

Line 3711: psp_report_template_details_h prtd ,

3707: INSERT INTO psp_selection_cardinality_gt
3708: (lookup_code, total_count)
3709: SELECT 'AWD', COUNT(DISTINCT psl.assignment_id)
3710: FROM psp_summary_lines psl,
3711: psp_report_template_details_h prtd ,
3712: per_time_periods ptp
3713: WHERE
3714: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
3715: AND prtd.criteria_lookup_code='AWD'

Line 3749: psp_report_template_details_h prtd ,

3745: INSERT INTO psp_selection_cardinality_gt
3746: (lookup_code, total_count)
3747: SELECT 'ATY', COUNT(DISTINCT psl.assignment_id)
3748: FROM psp_summary_lines psl,
3749: psp_report_template_details_h prtd ,
3750: gms_awards_all gaa,
3751: per_time_periods ptp
3752: WHERE psl.award_id = gaa.award_id
3753: AND prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'

Line 3788: psp_report_template_details_h prtd ,

3784: INSERT INTO psp_selection_cardinality_gt
3785: (lookup_code, total_count)
3786: SELECT 'PRT', COUNT(DISTINCT psl.assignment_id)
3787: FROM psp_summary_lines psl,
3788: psp_report_template_details_h prtd ,
3789: pa_projects_all ppa ,
3790: per_time_periods ptp
3791: WHERE psl.project_id = ppa.project_id
3792: AND prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'

Line 3827: psp_report_template_details_h prtd ,

3823: INSERT INTO psp_selection_cardinality_gt
3824: (lookup_code, total_count)
3825: SELECT 'PRJ', COUNT(DISTINCT psl.assignment_id)
3826: FROM psp_summary_lines psl,
3827: psp_report_template_details_h prtd ,
3828: per_time_periods ptp
3829: WHERE
3830: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
3831: AND prtd.criteria_lookup_code='PRJ'

Line 3863: FROM psp_report_template_details_h prtd

3859: (lookup_code, total_count)
3860: SELECT 'PAY', COUNT(DISTINCT assignment_id)
3861: FROM per_assignments_f paf
3862: WHERE payroll_id IN (SELECT TO_NUMBER(criteria_value1)
3863: FROM psp_report_template_details_h prtd
3864: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3865: AND criteria_lookup_code='PAY'
3866: AND include_exclude_flag='I' AND request_id = p_request_id)
3867: AND paf.assignment_type = 'E'

Line 3876: FROM psp_report_template_details_h prtd

3872: (lookup_code, total_count)
3873: SELECT 'LOC', COUNT(DISTINCT assignment_id)
3874: FROM per_assignments_f paf
3875: WHERE location_id IN (SELECT TO_NUMBER(criteria_value1)
3876: FROM psp_report_template_details_h prtd
3877: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3878: AND criteria_lookup_code='LOC'
3879: AND include_exclude_flag='I'
3880: AND request_id = p_request_id)

Line 3890: FROM psp_report_template_details_h prtd

3886: (lookup_code, total_count)
3887: SELECT 'ORG', COUNT(DISTINCT assignment_id)
3888: FROM per_assignments_f paf
3889: WHERE organization_id IN (SELECT TO_NUMBER(criteria_value1)
3890: FROM psp_report_template_details_h prtd
3891: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3892: AND criteria_lookup_code='ORG'
3893: AND include_exclude_flag='I'
3894: AND request_id = p_request_id)

Line 3904: FROM psp_report_template_details_h prtd

3900: (lookup_code, total_count)
3901: SELECT 'JOB', COUNT(DISTINCT assignment_id)
3902: FROM per_assignments_f paf
3903: WHERE job_id IN (SELECT TO_NUMBER(criteria_value1)
3904: FROM psp_report_template_details_h prtd
3905: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3906: AND criteria_lookup_code='JOB'
3907: AND include_exclude_flag='I'
3908: AND request_id=p_request_id)

Line 3918: FROM psp_report_template_details_h prtd

3914: (lookup_code, total_count)
3915: SELECT 'POS', COUNT(DISTINCT assignment_id)
3916: FROM per_assignments_f paf
3917: WHERE position_id IN (SELECT TO_NUMBER(criteria_value1)
3918: FROM psp_report_template_details_h prtd
3919: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3920: AND criteria_lookup_code='POS'
3921: AND include_exclude_flag='I'
3922: AND request_id = p_request_id)

Line 3932: FROM psp_report_template_details_h prtd

3928: (lookup_code, total_count)
3929: SELECT 'ASS', COUNT(DISTINCT assignment_id)
3930: FROM per_assignments_f paf
3931: WHERE assignment_status_type_id IN (SELECT TO_NUMBER(criteria_value1)
3932: FROM psp_report_template_details_h prtd
3933: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3934: AND criteria_lookup_code='ASS'
3935: AND include_exclude_flag='I'
3936: AND request_id = p_request_id)

Line 3948: FROM psp_report_template_details_h prtd

3944: FROM per_assignments_f paf,
3945: pay_payrolls_f ppf
3946: WHERE ppf.payroll_id = paf.payroll_id
3947: AND ppf.consolidation_set_id IN (SELECT TO_NUMBER(criteria_value1)
3948: FROM psp_report_template_details_h prtd
3949: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3950: AND criteria_lookup_code='CST'
3951: AND include_exclude_flag='I'
3952: AND request_id = p_request_id)

Line 3966: FROM psp_report_template_details_h prtd

3962: FROM per_all_assignments_f paf,
3963: hr_assignment_sets has ,
3964: hr_assignment_set_amendments hasa
3965: WHERE has.assignment_set_id IN (SELECT TO_NUMBER(criteria_value1)
3966: FROM psp_report_template_details_h prtd
3967: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3968: AND criteria_lookup_code='AST'
3969: AND include_exclude_flag='I'
3970: AND request_id =p_request_id)

Line 4143: FROM psp_report_template_details_h

4139: p_set_of_books_id IN NUMBER) IS
4140: PRAGMA AUTONOMOUS_TRANSACTION;
4141: CURSOR PPG_CURSOR IS
4142: SELECT criteria_value1, criteria_value2
4143: FROM psp_report_template_details_h
4144: WHERE request_id = p_request_id
4145: AND criteria_lookup_type= 'PSP_SELECTION_CRITERIA' AND criteria_lookup_code='PPG'
4146: AND include_exclude_flag='I';
4147:

Line 4151: FROM psp_report_template_details_h

4147:
4148:
4149: CURSOR GLA_CURSOR IS
4150: SELECT criteria_value1 , criteria_value2, criteria_value3
4151: FROM psp_report_template_details_h
4152: WHERE request_id = p_request_id
4153: AND criteria_lookup_type ='PSP_SELECTION_CRITERIA' AND criteria_lookup_code ='GLA'
4154: AND include_exclude_flag='I';
4155:

Line 4171: FROM psp_report_template_details_h prtd

4167: FROM per_people_f ppf,
4168: per_assignments_f paf,
4169: per_assignment_status_types past
4170: WHERE person_type_id IN (SELECT TO_NUMBER(criteria_value1)
4171: FROM psp_report_template_details_h prtd
4172: WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
4173: AND criteria_lookup_code='PTY'
4174: AND request_id = p_request_id
4175: AND include_exclude_flag='I')

Line 4208: FROM psp_report_template_details_h prtd

4204: FROM per_all_people_f ppf,
4205: per_assignments_f paf,
4206: per_assignment_status_types past
4207: WHERE ppf.person_id IN (SELECT TO_NUMBER(criteria_value1)
4208: FROM psp_report_template_details_h prtd
4209: WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
4210: AND criteria_lookup_code='EMP'
4211: AND include_exclude_flag='I'
4212: AND request_id = p_request_id)

Line 4244: FROM psp_report_template_details_h prtd

4240: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4241: FROM per_all_assignments_f paf,
4242: per_assignment_status_types past
4243: WHERE supervisor_id IN (SELECT TO_NUMBER(criteria_value1)
4244: FROM psp_report_template_details_h prtd
4245: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
4246: AND criteria_lookup_code='SUP'
4247: AND include_exclude_flag='I'
4248: AND request_id = p_request_id)

Line 4274: psp_report_template_details_h prtd,

4270: ELSIF g_lookup_code='AWD' THEN
4271: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4272: SELECT DISTINCT p_request_id, psl.person_id, psl.assignment_id
4273: FROM psp_summary_lines psl,
4274: psp_report_template_details_h prtd,
4275: per_time_periods ptp
4276: WHERE
4277: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
4278: AND prtd.criteria_lookup_code='AWD'

Line 4310: psp_report_template_details_h prtd,

4306: ELSIF g_lookup_code ='ATY' THEN
4307: INSERT INTO psp_selected_persons_t (request_id , person_id, assignment_id)
4308: SELECT DISTINCT p_request_id, psl.person_id, psl.assignment_id
4309: FROM psp_summary_lines psl,
4310: psp_report_template_details_h prtd,
4311: gms_awards_all gaa,
4312: per_time_periods ptp
4313: WHERE psl.award_id = gaa.award_id
4314: AND prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'

Line 4348: psp_report_template_details_h prtd,

4344: ELSIF g_lookup_code ='PRT' THEN
4345: INSERT INTO psp_selected_persons_t (request_id , person_id, assignment_id)
4346: SELECT DISTINCT p_request_id, psl.person_id, psl.assignment_id
4347: FROM psp_summary_lines psl,
4348: psp_report_template_details_h prtd,
4349: pa_projects_all ppa,
4350: per_time_periods ptp
4351: WHERE psl.project_id = ppa.project_id
4352: AND prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'

Line 4385: psp_report_template_details_h prtd,

4381: ELSIF g_lookup_code ='PRJ' THEN
4382: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4383: SELECT DISTINCT p_request_id, psl.person_id, psl.assignment_id
4384: FROM psp_summary_lines psl,
4385: psp_report_template_details_h prtd,
4386: per_time_periods ptp
4387: WHERE
4388: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
4389: AND prtd.criteria_lookup_code='PRJ'

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 payroll_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='PAY'
4427: AND include_exclude_flag='I'
4428: AND request_id = p_request_id)

Line 4457: FROM psp_report_template_details_h prtd

4453: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4454: FROM per_assignments_f paf,
4455: per_assignment_status_types past
4456: WHERE location_id IN (SELECT TO_NUMBER(criteria_value1)
4457: FROM psp_report_template_details_h prtd
4458: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
4459: AND criteria_lookup_code='LOC'
4460: AND include_exclude_flag='I'
4461: AND request_id = p_request_id)

Line 4489: FROM psp_report_template_details_h prtd

4485: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4486: FROM per_assignments_f paf,
4487: per_assignment_status_types past
4488: WHERE organization_id IN (SELECT TO_NUMBER(criteria_value1)
4489: FROM psp_report_template_details_h prtd
4490: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
4491: AND criteria_lookup_code='ORG'
4492: AND request_id = p_request_id
4493: AND include_exclude_flag='I')

Line 4523: FROM psp_report_template_details_h prtd

4519: pay_payrolls_f ppf,
4520: per_assignment_status_types past
4521: WHERE ppf.payroll_id = paf.payroll_id
4522: AND ppf.consolidation_set_id IN (SELECT TO_NUMBER(criteria_value1)
4523: FROM psp_report_template_details_h prtd
4524: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
4525: AND criteria_lookup_code='CST'
4526: AND include_exclude_flag='I'
4527: AND request_id = p_request_id)

Line 4560: FROM psp_report_template_details_h prtd

4556: hr_assignment_sets has,
4557: hr_assignment_set_amendments hasa,
4558: per_assignment_status_types past
4559: WHERE has.assignment_set_id IN (SELECT TO_NUMBER(criteria_value1)
4560: FROM psp_report_template_details_h prtd
4561: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
4562: AND criteria_lookup_code='AST'
4563: AND include_exclude_flag='I'
4564: AND request_id =p_request_id)

Line 4606: FROM psp_report_template_details_h prtd

4602: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4603: FROM per_assignments_f paf,
4604: per_assignment_status_types past
4605: WHERE job_id IN (SELECT TO_NUMBER(criteria_value1)
4606: FROM psp_report_template_details_h prtd
4607: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
4608: AND criteria_lookup_code='JOB'
4609: AND include_exclude_flag='I'
4610: AND request_id = p_request_id)

Line 4638: FROM psp_report_template_details_h prtd

4634: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4635: FROM per_assignments_f paf,
4636: per_assignment_status_types past
4637: WHERE position_id IN (SELECT TO_NUMBER(criteria_value1)
4638: FROM psp_report_template_details_h prtd
4639: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
4640: AND criteria_lookup_code='POS'
4641: AND request_id = p_request_id
4642: AND include_exclude_flag='I')

Line 4670: FROM psp_report_template_details_h prtd

4666: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4667: FROM per_assignments_f paf,
4668: per_assignment_status_types past
4669: WHERE paf.assignment_status_type_id IN (SELECT TO_NUMBER(criteria_value1)
4670: FROM psp_report_template_details_h prtd
4671: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
4672: AND criteria_lookup_code='ASS'
4673: AND request_id = p_request_id
4674: AND include_exclude_flag='I')

Line 4825: FROM psp_report_template_details_h

4821: p_set_of_books_id IN NUMBER) IS
4822: CURSOR get_all_selection_criteria(p_request_id IN NUMBER) IS
4823: SELECT distinct criteria_lookup_code,
4824: include_exclude_flag
4825: FROM psp_report_template_details_h
4826: WHERE request_id = p_request_id
4827: AND criteria_lookup_type='PSP_SELECTION_CRITERIA'
4828: ORDER BY include_exclude_flag;
4829:

Line 4832: FROM psp_report_template_details_h

4828: ORDER BY include_exclude_flag;
4829:
4830: CURSOR PPG_CURSOR IS
4831: SELECT criteria_value1, criteria_value2
4832: FROM psp_report_template_details_h
4833: WHERE request_id = p_request_id
4834: AND criteria_lookup_type= 'PSP_SELECTION_CRITERIA'
4835: AND criteria_lookup_code='PPG'
4836: AND include_exclude_flag='I';

Line 4840: FROM psp_report_template_details_h

4836: AND include_exclude_flag='I';
4837:
4838: CURSOR GLA_CURSOR IS
4839: SELECT criteria_value1 , criteria_value2, criteria_value3
4840: FROM psp_report_template_details_h
4841: WHERE request_id = p_request_id
4842: AND criteria_lookup_type ='PSP_SELECTION_CRITERIA'
4843: AND criteria_lookup_code ='GLA'
4844: AND include_exclude_flag='I';

Line 4871: psp_report_template_details_h prtd,

4867: /* Bug 5087294 : Performance fix replacing not in with not exists */
4868: -- AND person_id NOT IN (SELECT NVL( person_id, 0)
4869: AND NOT EXISTS ( SELECT 1
4870: FROM per_people_f ppf,
4871: psp_report_template_details_h prtd,
4872: per_assignments_f paf
4873: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
4874: AND criteria_lookup_code='PTY'
4875: AND paf.person_id = ppf.person_id

Line 4894: FROM psp_report_template_details_h prtd

4890: -- AND person_id NOT IN (SELECT NVL(person_id,0)
4891: AND NOT EXISTS ( SELECT 1
4892: FROM per_all_people_f ppf, per_assignments_f paf
4893: WHERE ppf.person_id IN (SELECT TO_NUMBER(criteria_value1)
4894: FROM psp_report_template_details_h prtd
4895: WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
4896: AND criteria_lookup_code='EMP'
4897: AND include_exclude_flag='I'
4898: AND prtd.request_id = p_request_id)

Line 4913: psp_report_template_details_h prtd

4909: /* Bug 5087294 : Performance fix replacing not in with not exists */
4910: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
4911: AND NOT EXISTS ( SELECT 1
4912: FROM per_all_assignments_f paf,
4913: psp_report_template_details_h prtd
4914: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
4915: AND criteria_lookup_code='SUP'
4916: AND include_exclude_flag='I'
4917: AND paf.supervisor_id = TO_NUMBER(prtd.criteria_value1)

Line 4928: psp_report_template_details_h prtd ,

4924: DELETE FROM psp_selected_persons_t
4925: WHERE request_id = p_request_id
4926: AND assignment_id NOT IN (SELECT psl.assignment_id
4927: FROM psp_summary_lines psl,
4928: psp_report_template_details_h prtd ,
4929: per_time_periods ptp
4930: WHERE
4931: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
4932: AND prtd.criteria_lookup_code='AWD'

Line 4969: psp_report_template_details_h prtd,

4965: DELETE FROM psp_selected_persons_t
4966: WHERE request_id = p_request_id
4967: AND assignment_id NOT IN (SELECT NVL(psl.assignment_id,0)
4968: FROM psp_summary_lines psl,
4969: psp_report_template_details_h prtd,
4970: gms_awards_all gaa,
4971: per_time_periods ptp
4972: WHERE psl.award_id = gaa.award_id
4973: AND prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'

Line 5011: psp_report_template_details_h prtd,

5007: DELETE FROM psp_selected_persons_t
5008: WHERE request_id = p_request_id
5009: AND assignment_id NOT IN (SELECT NVL(psl.assignment_id,0)
5010: FROM psp_summary_lines psl,
5011: psp_report_template_details_h prtd,
5012: pa_projects_all ppa,
5013: per_time_periods ptp
5014: WHERE psl.project_id = ppa.project_id
5015: AND prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'

Line 5053: psp_report_template_details_h prtd ,

5049: DELETE FROM psp_selected_persons_t
5050: WHERE request_id = p_request_id
5051: AND assignment_id NOT IN (SELECT NVL(psl.assignment_id ,0)
5052: FROM psp_summary_lines psl,
5053: psp_report_template_details_h prtd ,
5054: per_time_periods ptp
5055: WHERE
5056: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
5057: AND prtd.criteria_lookup_code='PRJ'

Line 5096: psp_report_template_details_h prtd

5092: /* Bug 5087294 : Performance fix replacing not in with not exists */
5093: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5094: AND NOT EXISTS ( SELECT 1
5095: FROM per_assignments_f paf,
5096: psp_report_template_details_h prtd
5097: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5098: AND criteria_lookup_code='PAY'
5099: AND include_exclude_flag='I'
5100: AND paf.assignment_type = 'E'

Line 5114: psp_report_template_details_h prtd

5110: /* Bug 5087294 : Performance fix replacing not in with not exists */
5111: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5112: AND NOT EXISTS ( SELECT 1
5113: FROM per_assignments_f paf ,
5114: psp_report_template_details_h prtd
5115: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5116: AND criteria_lookup_code='LOC'
5117: AND paf.assignment_type = 'E'
5118: and

Line 5132: psp_report_template_details_h prtd

5128: WHERE request_id = p_request_id
5129: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5130: AND NOT EXISTS ( SELECT 1
5131: FROM per_assignments_f paf ,
5132: psp_report_template_details_h prtd
5133: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5134: AND criteria_lookup_code='ORG'
5135: AND include_exclude_flag='I'
5136: AND paf.assignment_type = 'E'

Line 5153: FROM psp_report_template_details_h prtd

5149: FROM per_assignments_f paf,
5150: pay_payrolls_f ppf
5151: WHERE ppf.payroll_id = paf.payroll_id
5152: AND ppf.consolidation_set_id IN (SELECT TO_NUMBER(criteria_value1)
5153: FROM psp_report_template_details_h prtd
5154: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5155: AND criteria_lookup_code='CST'
5156: AND include_exclude_flag='I'
5157: AND request_id = p_request_id)

Line 5172: FROM psp_report_template_details_h prtd

5168: FROM per_all_assignments_f paf,
5169: hr_assignment_sets has,
5170: hr_assignment_set_amendments hasa
5171: WHERE has.assignment_set_id IN (SELECT TO_NUMBER(criteria_value1)
5172: FROM psp_report_template_details_h prtd
5173: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5174: AND criteria_lookup_code='AST'
5175: AND include_exclude_flag='I'
5176: AND request_id =p_request_id)

Line 5202: psp_report_template_details_h prtd

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

Line 5220: psp_report_template_details_h prtd

5216: /* Bug 5087294 : Performance fix replacing not in with not exists */
5217: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5218: AND NOT EXISTS ( SELECT 1
5219: FROM per_assignments_f paf ,
5220: psp_report_template_details_h prtd
5221: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5222: AND criteria_lookup_code='POS'
5223: AND paf.assignment_type = 'E'
5224: and

Line 5238: psp_report_template_details_h prtd

5234: /* Bug 5087294 : Performance fix replacing not in with not exists */
5235: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5236: AND NOT EXISTS ( SELECT 1
5237: FROM per_assignments_f paf ,
5238: psp_report_template_details_h prtd
5239: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5240: AND criteria_lookup_code='ASS'
5241: AND include_exclude_flag='I'
5242: AND paf.assignment_status_type_id = TO_NUMBER(prtd.criteria_value1)

Line 5354: FROM psp_report_template_details_h

5350: p_business_group_id IN NUMBER,
5351: p_set_of_books_id IN NUMBER) IS
5352: CURSOR PPG_CURSOR IS
5353: SELECT criteria_value1, criteria_value2
5354: FROM psp_report_template_details_h
5355: WHERE request_id = p_request_id
5356: AND criteria_lookup_type= 'PSP_SELECTION_CRITERIA'
5357: AND criteria_lookup_code='PPG'
5358: AND include_exclude_flag='E';

Line 5362: FROM psp_report_template_details_h

5358: AND include_exclude_flag='E';
5359:
5360: CURSOR GLA_CURSOR IS
5361: SELECT criteria_value1, criteria_value2, criteria_value3
5362: FROM psp_report_template_details_h
5363: WHERE request_id = p_request_id
5364: AND criteria_lookup_type ='PSP_SELECTION_CRITERIA'
5365: AND criteria_lookup_code ='GLA'
5366: AND include_exclude_flag='E';

Line 5384: psp_report_template_details_h prtd,

5380: DELETE FROM psp_selected_persons_t
5381: WHERE request_id = p_request_id
5382: AND person_id IN (SELECT ppf.person_id
5383: FROM per_people_f ppf,
5384: psp_report_template_details_h prtd,
5385: per_assignments_f paf
5386: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5387: AND criteria_lookup_code='PTY'
5388: AND paf.person_id = ppf.person_id

Line 5404: FROM psp_report_template_details_h prtd

5400: WHERE request_id = p_request_id
5401: AND person_id IN (SELECT DISTINCT ppf.person_id
5402: FROM per_all_people_f ppf, per_assignments_f paf
5403: WHERE ppf.person_id IN (SELECT TO_NUMBER(criteria_value1)
5404: FROM psp_report_template_details_h prtd
5405: WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
5406: AND criteria_lookup_code='EMP'
5407: AND include_exclude_flag='E'
5408: AND prtd.request_id = p_request_id)

Line 5420: psp_report_template_details_h prtd

5416: DELETE FROM psp_selected_persons_t
5417: WHERE request_id = p_request_id
5418: AND assignment_id IN (SELECT NVL(assignment_id,0)
5419: FROM per_all_assignments_f paf,
5420: psp_report_template_details_h prtd
5421: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5422: AND criteria_lookup_code='SUP'
5423: AND include_exclude_flag='E'
5424: AND paf.supervisor_id = TO_NUMBER(prtd.criteria_value1)

Line 5434: psp_report_template_details_h prtd,

5430: DELETE FROM psp_selected_persons_t
5431: WHERE request_id = p_request_id
5432: AND assignment_id IN (SELECT psl.assignment_id
5433: FROM psp_summary_lines psl,
5434: psp_report_template_details_h prtd,
5435: per_time_periods ptp
5436: WHERE
5437: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
5438: AND prtd.criteria_lookup_code='AWD'

Line 5472: psp_report_template_details_h prtd,

5468: DELETE FROM psp_selected_persons_t
5469: WHERE request_id = p_request_id
5470: AND assignment_id IN (SELECT NVL(psl.assignment_id,0)
5471: FROM psp_summary_lines psl,
5472: psp_report_template_details_h prtd,
5473: gms_awards_all gaa,
5474: per_time_periods ptp
5475: WHERE psl.award_id = gaa.award_id
5476: AND prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'

Line 5511: psp_report_template_details_h prtd,

5507: DELETE FROM psp_selected_persons_t
5508: WHERE request_id = p_request_id
5509: AND assignment_id IN (SELECT NVL(psl.assignment_id,0)
5510: FROM psp_summary_lines psl,
5511: psp_report_template_details_h prtd,
5512: pa_projects_all ppa,
5513: per_time_periods ptp
5514: WHERE psl.project_id = ppa.project_id
5515: AND prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'

Line 5550: psp_report_template_details_h prtd,

5546: DELETE FROM psp_selected_persons_t
5547: WHERE request_id = p_request_id
5548: AND assignment_id IN (SELECT NVL(psl.assignment_id,0)
5549: FROM psp_summary_lines psl,
5550: psp_report_template_details_h prtd,
5551: per_time_periods ptp
5552: WHERE
5553: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
5554: AND prtd.criteria_lookup_code='PRJ'

Line 5588: psp_report_template_details_h prtd

5584: DELETE FROM psp_selected_persons_t
5585: WHERE request_id = p_request_id
5586: AND assignment_id IN (SELECT assignment_id
5587: FROM per_assignments_f paf,
5588: psp_report_template_details_h prtd
5589: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5590: AND criteria_lookup_code='PAY'
5591: AND include_exclude_flag='E'
5592: AND paf.assignment_type = 'E'

Line 5602: psp_report_template_details_h prtd

5598: DELETE FROM psp_selected_persons_t
5599: WHERE request_id = p_request_id
5600: AND assignment_id IN (SELECT assignment_id
5601: FROM per_assignments_f paf,
5602: psp_report_template_details_h prtd
5603: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5604: AND criteria_lookup_code='LOC'
5605: AND include_exclude_flag='E'
5606: AND paf.assignment_type = 'E'

Line 5616: psp_report_template_details_h prtd

5612: DELETE FROM psp_selected_persons_t
5613: WHERE request_id = p_request_id
5614: AND assignment_id IN (SELECT assignment_id
5615: FROM per_assignments_f paf,
5616: psp_report_template_details_h prtd
5617: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5618: AND criteria_lookup_code='ORG'
5619: AND include_exclude_flag='E'
5620: AND paf.assignment_type = 'E'

Line 5630: psp_report_template_details_h prtd

5626: DELETE FROM psp_selected_persons_t
5627: WHERE request_id = p_request_id
5628: AND assignment_id IN (SELECT assignment_id
5629: FROM per_assignments_f paf,
5630: psp_report_template_details_h prtd
5631: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5632: AND criteria_lookup_code='JOB'
5633: AND include_exclude_flag='E'
5634: AND paf.assignment_type = 'E'

Line 5644: psp_report_template_details_h prtd

5640: DELETE FROM psp_selected_persons_t
5641: WHERE request_id = p_request_id
5642: AND assignment_id IN (SELECT assignment_id
5643: FROM per_assignments_f paf,
5644: psp_report_template_details_h prtd
5645: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5646: AND criteria_lookup_code='POS'
5647: AND include_exclude_flag='E'
5648: AND paf.assignment_type = 'E'

Line 5658: psp_report_template_details_h prtd

5654: DELETE FROM psp_selected_persons_t
5655: WHERE request_id = p_request_id
5656: AND assignment_id IN (SELECT assignment_id
5657: FROM per_assignments_f paf,
5658: psp_report_template_details_h prtd
5659: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5660: AND criteria_lookup_code='ASS'
5661: AND include_exclude_flag='E'
5662: AND paf.assignment_type = 'E'

Line 5675: FROM psp_report_template_details_h prtd

5671: FROM per_assignments_f paf,
5672: pay_payrolls_f ppf
5673: WHERE ppf.payroll_id = paf.payroll_id
5674: AND ppf.consolidation_set_id IN (SELECT TO_NUMBER(criteria_value1)
5675: FROM psp_report_template_details_h prtd
5676: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5677: AND criteria_lookup_code='CST'
5678: AND include_exclude_flag='E'
5679: AND request_id = p_request_id)

Line 5692: FROM psp_report_template_details_h prtd

5688: AND assignment_id IN (SELECT NVL(paf.assignment_id,0)
5689: FROM per_all_assignments_f paf, hr_assignment_sets has,
5690: hr_assignment_set_amendments hasa
5691: WHERE has.assignment_set_id IN (SELECT TO_NUMBER(criteria_value1)
5692: FROM psp_report_template_details_h prtd
5693: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5694: AND criteria_lookup_code='AST'
5695: AND include_exclude_flag='E'
5696: AND request_id =p_request_id)