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

APPS.PSP_TEMPLATE_SELECTION dependencies on PER_ASSIGNMENT_STATUS_TYPES

Line 1060: per_assignment_status_types past

1056: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
1057: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1058: FROM per_people_f ppf,
1059: per_assignments_f paf,
1060: per_assignment_status_types past
1061: WHERE person_type_id IN (SELECT TO_NUMBER(criteria_value1)
1062: FROM psp_report_template_details_h prtd
1063: WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
1064: AND criteria_lookup_code='PTY'

Line 1097: per_assignment_status_types past

1093: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
1094: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1095: FROM per_all_people_f ppf,
1096: per_assignments_f paf,
1097: per_assignment_status_types past
1098: WHERE ppf.person_id IN (SELECT TO_NUMBER(criteria_value1)
1099: FROM psp_report_template_details_h prtd
1100: WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
1101: AND criteria_lookup_code='EMP'

Line 1134: per_assignment_status_types past

1130: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
1131: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1132: FROM per_all_people_f ppf,
1133: per_assignments_f paf,
1134: per_assignment_status_types past
1135: WHERE supervisor_id IN (SELECT TO_NUMBER(criteria_value1)
1136: FROM psp_report_template_details_h prtd
1137: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
1138: AND criteria_lookup_code='SUP'

Line 1347: per_assignment_status_types past

1343: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
1344: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1345: FROM per_all_people_f ppf,
1346: per_assignments_f paf,
1347: per_assignment_status_types past
1348: WHERE payroll_id IN (SELECT TO_NUMBER(criteria_value1)
1349: FROM psp_report_template_details_h prtd
1350: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
1351: AND criteria_lookup_code='PAY'

Line 1384: per_assignment_status_types past

1380: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
1381: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1382: FROM per_all_people_f ppf,
1383: per_assignments_f paf,
1384: per_assignment_status_types past
1385: WHERE location_id IN (SELECT TO_NUMBER(criteria_value1)
1386: FROM psp_report_template_details_h prtd
1387: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
1388: AND criteria_lookup_code='LOC'

Line 1421: per_assignment_status_types past

1417: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
1418: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1419: FROM per_all_people_f ppf,
1420: per_assignments_f paf,
1421: per_assignment_status_types past
1422: WHERE organization_id IN (SELECT TO_NUMBER(criteria_value1)
1423: FROM psp_report_template_details_h prtd
1424: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
1425: AND criteria_lookup_code='ORG'

Line 1459: per_assignment_status_types past

1455: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1456: FROM pay_payrolls_f pp,
1457: per_all_people_f ppf,
1458: per_assignments_f paf,
1459: per_assignment_status_types past
1460: WHERE pp.payroll_id = paf.payroll_id
1461: AND pp.consolidation_set_id IN (SELECT TO_NUMBER(criteria_value1)
1462: FROM psp_report_template_details_h prtd
1463: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'

Line 1501: per_assignment_status_types past

1497: FROM per_all_assignments_f paf,
1498: hr_assignment_sets has,
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'

Line 1552: per_assignment_status_types past

1548: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
1549: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1550: FROM per_all_people_f ppf,
1551: per_assignments_f paf,
1552: per_assignment_status_types past
1553: WHERE job_id IN (SELECT TO_NUMBER(criteria_value1)
1554: FROM psp_report_template_details_h prtd
1555: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
1556: AND criteria_lookup_code='JOB'

Line 1589: per_assignment_status_types past

1585: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
1586: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1587: FROM per_all_people_f ppf,
1588: per_assignments_f paf,
1589: per_assignment_status_types past
1590: WHERE position_id IN (SELECT TO_NUMBER(criteria_value1)
1591: FROM psp_report_template_details_h prtd
1592: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
1593: AND criteria_lookup_code='POS'

Line 1626: per_assignment_status_types past

1622: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
1623: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1624: FROM per_all_people_f ppf,
1625: per_assignments_f paf,
1626: per_assignment_status_types past
1627: WHERE paf.assignment_status_type_id IN (SELECT TO_NUMBER(criteria_value1)
1628: FROM psp_report_template_details_h prtd
1629: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
1630: AND criteria_lookup_code='ASS'

Line 1697: per_assignment_status_types past

1693: (select :request_id , paf.person_id, paf.assignment_id
1694: from per_assignments_f paf,
1695: pay_people_groups ppg,
1696: per_all_people_f ppf,
1697: per_assignment_status_types past
1698: where paf.people_group_id= ppg.people_group_id
1699: AND paf.person_id = ppf.person_id
1700: AND paf.assignment_status_type_id = past.assignment_status_type_id
1701: AND paf.assignment_type = ''' || 'E' || '''

Line 1888: per_assignment_status_types past

1884: AND NOT EXISTS ( SELECT 1
1885: FROM per_people_f ppf,
1886: psp_report_template_details_h prtd,
1887: per_assignments_f paf,
1888: per_assignment_status_types past
1889: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
1890: AND criteria_lookup_code='PTY'
1891: AND paf.person_id = ppf.person_id
1892: AND paf.assignment_type = 'E'

Line 1928: per_assignment_status_types past

1924: -- AND person_id NOT IN (SELECT NVL(person_id,0)
1925: AND NOT EXISTS ( SELECT 1
1926: FROM per_all_people_f ppf,
1927: per_assignments_f paf,
1928: per_assignment_status_types past
1929: WHERE ppf.person_id IN (SELECT TO_NUMBER(criteria_value1)
1930: FROM psp_report_template_details_h prtd
1931: WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
1932: AND criteria_lookup_code='EMP'

Line 1970: per_assignment_status_types past

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

Line 2160: per_assignment_status_types past

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

Line 2199: per_assignment_status_types past

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'
2203: AND effective_start_date <= p_effort_end

Line 2235: per_assignment_status_types past

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

Line 2272: per_assignment_status_types past

2268: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2269: AND NOT EXISTS ( SELECT 1
2270: FROM per_assignments_f paf,
2271: pay_payrolls_f ppf,
2272: per_assignment_status_types past
2273: WHERE ppf.payroll_id = paf.payroll_id
2274: AND ppf.consolidation_set_id IN (SELECT TO_NUMBER(criteria_value1)
2275: FROM psp_report_template_details_h prtd
2276: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'

Line 2313: per_assignment_status_types past

2309: AND person_id NOT IN (SELECT NVL(paf.person_id,0)
2310: FROM per_all_assignments_f paf,
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'

Line 2366: per_assignment_status_types past

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

Line 2403: per_assignment_status_types past

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

Line 2442: per_assignment_status_types past

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

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

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

Line 2678: per_assignment_status_types past

2674: AND person_id IN (SELECT ppf.person_id
2675: FROM per_people_f ppf,
2676: psp_report_template_details_h prtd,
2677: per_assignments_f paf,
2678: per_assignment_status_types past
2679: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2680: AND criteria_lookup_code='PTY'
2681: AND paf.person_id = ppf.person_id
2682: AND paf.assignment_type = 'E'

Line 2716: per_assignment_status_types past

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

Line 2755: per_assignment_status_types past

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

Line 2941: per_assignment_status_types past

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

Line 2976: per_assignment_status_types past

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

Line 3011: per_assignment_status_types past

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

Line 3050: per_assignment_status_types past

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

Line 3085: per_assignment_status_types past

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

Line 3119: per_assignment_status_types past

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

Line 3154: per_assignment_status_types past

3150: WHERE request_id = p_request_id
3151: AND person_id IN (SELECT NVL(person_id,0)
3152: FROM per_assignments_f paf,
3153: pay_payrolls_f ppf,
3154: per_assignment_status_types past
3155: WHERE ppf.payroll_id = paf.payroll_id
3156: AND ppf.consolidation_set_id IN (SELECT TO_NUMBER(criteria_value1)
3157: FROM psp_report_template_details_h prtd
3158: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'

Line 3195: per_assignment_status_types past

3191: AND person_id IN (SELECT NVL(paf.person_id,0)
3192: FROM per_all_assignments_f paf,
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'

Line 3275: pay_people_groups ppg , per_assignment_status_types past

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

Line 4136: per_assignment_status_types past

4132: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4133: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4134: FROM per_people_f ppf,
4135: per_assignments_f paf,
4136: per_assignment_status_types past
4137: WHERE person_type_id IN (SELECT TO_NUMBER(criteria_value1)
4138: FROM psp_report_template_details_h prtd
4139: WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
4140: AND criteria_lookup_code='PTY'

Line 4173: per_assignment_status_types past

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

Line 4209: per_assignment_status_types past

4205: ELSIF g_lookup_code ='SUP' THEN
4206: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
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'

Line 4389: per_assignment_status_types past

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

Line 4422: per_assignment_status_types past

4418:
4419: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4420: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4421: FROM per_assignments_f paf,
4422: per_assignment_status_types past
4423: WHERE location_id IN (SELECT TO_NUMBER(criteria_value1)
4424: FROM psp_report_template_details_h prtd
4425: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
4426: AND criteria_lookup_code='LOC'

Line 4454: per_assignment_status_types past

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

Line 4487: per_assignment_status_types past

4483: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4484: SELECT DISTINCT p_request_id, person_id, assignment_id
4485: FROM per_assignments_f paf,
4486: pay_payrolls_f ppf,
4487: per_assignment_status_types past
4488: WHERE ppf.payroll_id = paf.payroll_id
4489: AND ppf.consolidation_set_id IN (SELECT TO_NUMBER(criteria_value1)
4490: FROM psp_report_template_details_h prtd
4491: WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'

Line 4525: per_assignment_status_types past

4521: SELECT DISTINCT p_request_id, person_id, paf.assignment_id
4522: FROM per_all_assignments_f paf,
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'

Line 4571: per_assignment_status_types past

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

Line 4603: per_assignment_status_types past

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

Line 4635: per_assignment_status_types past

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

Line 4685: per_assignment_status_types past

4681: g_exec_string := 'INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4682: SELECT :request_id , person_id, assignment_id
4683: FROM per_assignments_f paf,
4684: pay_people_groups ppg,
4685: per_assignment_status_types past
4686: WHERE paf.people_group_id= ppg.people_group_id
4687: AND paf.assignment_type = ''' || 'E' || '''
4688: AND paf.effective_end_date >= :p_effort_start
4689: AND paf.effective_start_date <= :p_effort_end