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_SELECTED_PERSONS_T

Line 193: SELECT nvl(count( person_id),0) into l_cnt from psp_selected_persons_t where

189: get_final_selection_list(errBuf, retCode, l_request_id, TRUE);
190:
191: --fnd_file.put_line(fnd_file.log,'==== After getting sleection list ');
192:
193: SELECT nvl(count( person_id),0) into l_cnt from psp_selected_persons_t where
194: request_id = l_request_id;
195:
196: --fnd_file.put_line(fnd_file.log,'==== count is '|| l_cnt);
197:

Line 218: delete from psp_Selected_persons_t outer where request_id = l_request_id and

214:
215: ELSE
216:
217: -- delete duplicates
218: delete from psp_Selected_persons_t outer where request_id = l_request_id and
219: (person_id, nvl( assignment_id, -999) ) in (select
220: person_id, nvl( assignment_id, -999) from psp_Selected_persons_t inner where inner.person_id=outer.person_id and inner.request_id =outer.request_id and inner.rowid > outer.rowid);
221:
222: END IF;

Line 220: person_id, nvl( assignment_id, -999) from psp_Selected_persons_t inner where inner.person_id=outer.person_id and inner.request_id =outer.request_id and inner.rowid > outer.rowid);

216:
217: -- delete duplicates
218: delete from psp_Selected_persons_t outer where request_id = l_request_id and
219: (person_id, nvl( assignment_id, -999) ) in (select
220: person_id, nvl( assignment_id, -999) from psp_Selected_persons_t inner where inner.person_id=outer.person_id and inner.request_id =outer.request_id and inner.rowid > outer.rowid);
221:
222: END IF;
223:
224:

Line 227: sqlstr := 'select distinct person_id from psp_selected_persons_t pspt,

223:
224:
225: -- fnd_file.put_line(fnd_file.log,'==== cnt sleection '||l_cnt);
226:
227: sqlstr := 'select distinct person_id from psp_selected_persons_t pspt,
228: pay_payroll_actions pact
229: where pact.payroll_action_id = :payroll_action_id and
230: pspt.request_id= pact.request_id ORDER BY pspt.person_id';
231:

Line 322: ---select count(*) into l_count_x from psp_selected_persons_t where request_id

318:
319: hr_utility.trace( 'get_template_selection-> After get_lowest_cardinality' );
320:
321: prepare_initial_person_list(p_request_id , l_effort_start, l_effort_end, l_business_group_id, l_set_of_books_id);
322: ---select count(*) into l_count_x from psp_selected_persons_t where request_id
323: ---= p_request_id;
324: ---fnd_file.put_line(fnd_file.log,' after initial person list ');
325:
326: FND_STATS.GATHER_TABLE_STATS(ownname=>'PSP', tabname=>'PSP_SELECTED_PERSONS_T');

Line 326: FND_STATS.GATHER_TABLE_STATS(ownname=>'PSP', tabname=>'PSP_SELECTED_PERSONS_T');

322: ---select count(*) into l_count_x from psp_selected_persons_t where request_id
323: ---= p_request_id;
324: ---fnd_file.put_line(fnd_file.log,' after initial person list ');
325:
326: FND_STATS.GATHER_TABLE_STATS(ownname=>'PSP', tabname=>'PSP_SELECTED_PERSONS_T');
327:
328: hr_utility.trace( 'get_template_selection-> After prepare_initial_person_list ' );
329: If g_lookup_code <> 'ALL' then
330:

Line 358: FND_STATS.GATHER_TABLE_STATS(ownname=>'PSP', tabname=>'PSP_SELECTED_PERSONS_T');

354: p_business_group_id => l_business_group_id,
355: p_set_of_books_id => l_set_of_books_id);
356: hr_utility.trace('get_template_selection-> After prepare_initial_asg_list');
357:
358: FND_STATS.GATHER_TABLE_STATS(ownname=>'PSP', tabname=>'PSP_SELECTED_PERSONS_T');
359:
360: IF g_lookup_code <> 'ALL' THEN
361: prune_initial_asg_list (p_request_id => p_request_id,
362: p_effort_start => l_effort_start,

Line 384: select nvl(count(person_id),0) into l_count from psp_Selected_persons_t where request_id=p_request_id;

380:
381:
382: --fnd_file.put_line(fnd_file.log,' before count ');
383:
384: select nvl(count(person_id),0) into l_count from psp_Selected_persons_t where request_id=p_request_id;
385: --fnd_file.put_line(fnd_file.log,' after count ' || l_count);
386: hr_utility.trace( 'get_template_selection-> After getting count= '||l_count );
387:
388: COMMIT;

Line 393: FND_STATS.GATHER_TABLE_STATS(ownname=>'PSP', tabname=>'PSP_SELECTED_PERSONS_T');

389:
390: If l_count > 0 then
391:
392:
393: FND_STATS.GATHER_TABLE_STATS(ownname=>'PSP', tabname=>'PSP_SELECTED_PERSONS_T');
394: -- fnd_file.put_line(fnd_file.log,' after gather stats ');
395:
396: end if;
397: EXCEPTION

Line 1056: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)

1052:
1053: BEGIN
1054: IF g_lookup_code = 'PTY' then
1055:
1056: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
1057: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1058: FROM per_people_f ppf,
1059: per_assignments_f paf,
1060: per_assignment_status_types past

Line 1093: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)

1089: AND rownum=1));
1090:
1091: ELSIF g_lookup_code ='EMP' then
1092:
1093: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
1094: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1095: FROM per_all_people_f ppf,
1096: per_assignments_f paf,
1097: per_assignment_status_types past

Line 1130: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)

1126: AND rownum=1));
1127:
1128: ELSIF g_lookup_code ='SUP' then
1129:
1130: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
1131: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1132: FROM per_all_people_f ppf,
1133: per_assignments_f paf,
1134: per_assignment_status_types past

Line 1169: insert into psp_selected_persons_t(request_id, person_id, assignment_id)(

1165:
1166: ELSIF g_lookup_code='AWD' then
1167:
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,

Line 1211: INSERT INTO psp_selected_persons_t(request_id , person_id, assignment_id)(

1207:
1208: ELSIF g_lookup_code ='ATY' then
1209:
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 ,

Line 1254: INSERT INTO psp_selected_persons_t(request_id , person_id, assignment_id)(

1250:
1251: ELSIF g_lookup_code ='PRT' then
1252: --- replaced non-performant insert with this for 4429787
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 ,

Line 1294: -- select count(person_id) into l_cnt from psp_selected_persons_t where request_id = p_request_id;

1290: AND palh.adjustment_batch_name IS NULL
1291: AND palh.reversal_entry_flag IS NULL AND
1292: NVL(palh.original_line_flag, 'N') ='N')));
1293:
1294: -- select count(person_id) into l_cnt from psp_selected_persons_t where request_id = p_request_id;
1295:
1296: -- fnd_file.put_line(fnd_file.log,'after insert into psp_selected_persons_t '||l_cnt);
1297:
1298:

Line 1296: -- fnd_file.put_line(fnd_file.log,'after insert into psp_selected_persons_t '||l_cnt);

1292: NVL(palh.original_line_flag, 'N') ='N')));
1293:
1294: -- select count(person_id) into l_cnt from psp_selected_persons_t where request_id = p_request_id;
1295:
1296: -- fnd_file.put_line(fnd_file.log,'after insert into psp_selected_persons_t '||l_cnt);
1297:
1298:
1299:
1300: ELSIF g_lookup_code ='PRJ' then

Line 1302: insert into psp_selected_persons_t(request_id, person_id, assignment_id)(

1298:
1299:
1300: ELSIF g_lookup_code ='PRJ' then
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,

Line 1343: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)

1339: NVL(palh.original_line_flag, 'N') ='N')));
1340:
1341: ELSIF g_lookup_code ='PAY' then
1342:
1343: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
1344: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1345: FROM per_all_people_f ppf,
1346: per_assignments_f paf,
1347: per_assignment_status_types past

Line 1380: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)

1376: AND rownum=1));
1377:
1378: ELSIF g_lookup_code ='LOC' then
1379:
1380: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
1381: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1382: FROM per_all_people_f ppf,
1383: per_assignments_f paf,
1384: per_assignment_status_types past

Line 1417: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)

1413: AND rownum=1));
1414:
1415: ELSIF g_lookup_code ='ORG' then
1416:
1417: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
1418: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1419: FROM per_all_people_f ppf,
1420: per_assignments_f paf,
1421: per_assignment_status_types past

Line 1454: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)

1450: AND rownum=1));
1451:
1452: ELSIF g_lookup_code='CST' then
1453:
1454: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
1455: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1456: FROM pay_payrolls_f pp,
1457: per_all_people_f ppf,
1458: per_assignments_f paf,

Line 1495: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)

1491: AND rownum=1));
1492:
1493: ELSIF g_lookup_code = 'AST' then
1494:
1495: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
1496: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1497: FROM per_all_assignments_f paf,
1498: hr_assignment_sets has,
1499: hr_assignment_set_amendments hasa,

Line 1548: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)

1544:
1545:
1546: ELSIF g_lookup_code ='JOB' then
1547:
1548: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
1549: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1550: FROM per_all_people_f ppf,
1551: per_assignments_f paf,
1552: per_assignment_status_types past

Line 1585: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)

1581: AND rownum=1));
1582:
1583: ELSIF g_lookup_code ='POS' then
1584:
1585: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
1586: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1587: FROM per_all_people_f ppf,
1588: per_assignments_f paf,
1589: per_assignment_status_types past

Line 1622: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)

1618: AND rownum=1));
1619:
1620: ELSIF g_lookup_code ='ASS' then
1621:
1622: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
1623: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1624: FROM per_all_people_f ppf,
1625: per_assignments_f paf,
1626: per_assignment_status_types past

Line 1691: g_exec_string := 'insert into psp_selected_persons_t

1687: close ppg_cursor;
1688:
1689:
1690: if l_sql_string is not null then
1691: g_exec_string := 'insert into psp_selected_persons_t
1692: (request_id, person_id, assignment_id)
1693: (select :request_id , paf.person_id, paf.assignment_id
1694: from per_assignments_f paf,
1695: pay_people_groups ppg,

Line 1764: g_exec_string := 'insert into psp_selected_persons_t(request_id, person_id, assignment_id)

1760:
1761: -- l_sql_string:= g_exec_string;
1762:
1763: if l_sql_string is not null then
1764: g_exec_string := 'insert into psp_selected_persons_t(request_id, person_id, assignment_id)
1765: ( select :p_request_id, psl.person_id, psl.assignment_id
1766: from psp_summary_lines psl, psp_distribution_lines_history pdnh,
1767: psp_adjustment_lines_history palh, psp_pre_gen_dist_lines_history ppg,
1768: gl_code_combinations gcc

Line 1811: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)

1807:
1808: --Bug 8222520
1809:
1810:
1811: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
1812: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1813: FROM per_all_people_f ppf,
1814: per_assignments_f paf,
1815: per_assignment_status_types past

Line 1913: DELETE FROM psp_selected_persons_t pspt

1909:
1910: IF template_sel_criteria.array_sel_criteria(i) <> g_lookup_code then
1911: IF template_sel_criteria.array_sel_criteria(i) = 'PTY' THEN
1912:
1913: DELETE FROM psp_selected_persons_t pspt
1914: WHERE request_id = p_request_id
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

Line 1954: DELETE FROM psp_selected_persons_t pspt

1950: AND rownum=1)));
1951:
1952: ELSIF template_sel_criteria.array_sel_criteria(i) = 'EMP' THEN
1953:
1954: DELETE FROM psp_selected_persons_t pspt
1955: WHERE request_id = p_request_id
1956: /* Bug 5087294 : Performance fix replacing not in with not exists */
1957: -- AND person_id NOT IN (SELECT NVL(person_id,0)
1958: AND NOT EXISTS ( SELECT 1

Line 1996: DELETE FROM psp_selected_persons_t pspt

1992:
1993:
1994: ELSIF template_sel_criteria.array_sel_criteria(i) ='SUP' THEN
1995:
1996: DELETE FROM psp_selected_persons_t pspt
1997: WHERE request_id = p_request_id
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

Line 2036: delete from psp_selected_persons_t where request_id = p_request_id AND person_id not in (

2032: ELSIF template_sel_criteria.array_sel_criteria(i) = 'AWD' THEN
2033:
2034:
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

Line 2074: DELETE FROM psp_selected_persons_t WHERE request_id = p_request_id AND person_id NOT IN (

2070: ELSIF template_sel_criteria.array_sel_criteria(i) = 'ATY' then
2071:
2072:
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 ,

Line 2112: DELETE FROM psp_selected_persons_t WHERE request_id = p_request_id AND person_id NOT IN (

2108:
2109: ELSIF template_sel_criteria.array_sel_criteria(i) ='PRT' THEN
2110:
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 ,

Line 2150: delete from psp_selected_persons_t where request_id = p_request_id AND person_id not in (

2146:
2147: elsif template_sel_criteria.array_sel_criteria(i) ='PRJ' THEN
2148:
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

Line 2186: DELETE FROM psp_selected_persons_t pspt

2182: NVL(palh.original_line_flag, 'N') ='N')));
2183:
2184: elsif template_sel_criteria.array_sel_criteria(i) ='PAY' THEN
2185:
2186: DELETE FROM psp_selected_persons_t pspt
2187: WHERE request_id = p_request_id
2188: /* Bug 5087294 : Performance fix replacing not in with not exists */
2189: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2190: AND NOT EXISTS ( SELECT 1

Line 2225: DELETE FROM psp_selected_persons_t pspt

2221:
2222:
2223: elsif template_sel_criteria.array_sel_criteria(i) ='LOC' THEN
2224:
2225: DELETE FROM psp_selected_persons_t pspt
2226: WHERE request_id = p_request_id
2227: /* Bug 5087294 : Performance fix replacing not in with not exists */
2228: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2229: AND NOT EXISTS ( SELECT 1

Line 2262: DELETE FROM psp_selected_persons_t pspt

2258: AND rownum=1)));
2259:
2260: elsif template_sel_criteria.array_sel_criteria(i) ='ORG' THEN
2261:
2262: DELETE FROM psp_selected_persons_t pspt
2263: WHERE request_id = p_request_id
2264: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2265: AND NOT EXISTS ( SELECT 1
2266: FROM per_assignments_f paf ,

Line 2298: DELETE FROM psp_selected_persons_t pspt

2294: AND rownum=1)));
2295:
2296:
2297: elsif template_sel_criteria.array_sel_criteria(i)='CST' THEN
2298: DELETE FROM psp_selected_persons_t pspt
2299: WHERE request_id = p_request_id
2300: /* Bug 5087294 : Performance fix replacing not in with not exists */
2301: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2302: AND NOT EXISTS ( SELECT 1

Line 2340: DELETE FROM psp_selected_persons_t

2336:
2337:
2338: elsif template_sel_criteria.array_sel_criteria(i) = 'AST' THEN
2339:
2340: DELETE FROM psp_selected_persons_t
2341: WHERE request_id = p_request_id
2342: AND person_id NOT IN (SELECT NVL(paf.person_id,0)
2343: FROM per_all_assignments_f paf,
2344: hr_assignment_sets has,

Line 2392: DELETE FROM psp_selected_persons_t pspt

2388:
2389:
2390: elsif template_sel_criteria.array_sel_criteria(i) = 'JOB' THEN
2391:
2392: DELETE FROM psp_selected_persons_t pspt
2393: WHERE request_id = p_request_id
2394: /* Bug 5087294 : Performance fix replacing not in with not exists */
2395: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2396: AND NOT EXISTS ( SELECT 1

Line 2429: DELETE FROM psp_selected_persons_t pspt

2425: AND rownum=1)));
2426:
2427: elsif template_sel_criteria.array_sel_criteria(i) ='POS' THEN
2428:
2429: DELETE FROM psp_selected_persons_t pspt
2430: WHERE request_id = p_request_id
2431: /* Bug 5087294 : Performance fix replacing not in with not exists */
2432: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2433: AND NOT EXISTS ( SELECT 1

Line 2468: DELETE FROM psp_selected_persons_t pspt

2464:
2465: elsif template_sel_criteria.array_sel_criteria(i) ='ASS' THEN
2466:
2467:
2468: DELETE FROM psp_selected_persons_t pspt
2469: WHERE request_id = p_request_id
2470: /* Bug 5087294 : Performance fix replacing not in with not exists */
2471: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2472: AND NOT EXISTS ( SELECT 1

Line 2547: g_exec_string := 'delete from psp_selected_persons_t sel where request_id = :request_id

2543:
2544: if l_sql_string is not null then
2545: --- replaced non-performant delete stmnt for 4429787
2546:
2547: g_exec_string := 'delete from psp_selected_persons_t sel where request_id = :request_id
2548: AND NOT EXISTS
2549: (SELECT 1 FROM per_assignments_f paf, pay_people_groups ppg, per_assignment_status_types past
2550: WHERE paf.people_group_id= ppg.people_group_id
2551: AND paf.assignment_type = ''' || 'E' || '''

Line 2610: g_exec_string := 'delete from psp_selected_persons_t where person_id

2606: -- l_sql_string:= g_exec_string;
2607:
2608: if l_sql_string is not null then
2609:
2610: g_exec_string := 'delete from psp_selected_persons_t where person_id
2611: not in (select nvl(psl.person_id,0) from psp_summary_lines psl,
2612: psp_distribution_lines_history pdnh,
2613: psp_adjustment_lines_history palh, psp_pre_gen_dist_lines_history ppg,
2614: gl_code_combinations gcc

Line 2705: DELETE FROM psp_selected_persons_t

2701: -- IF template_sel_criteria.array_sel_criteria(i) <> g_lookup_code THEN
2702:
2703: IF template_sel_criteria.array_sel_criteria(i) = 'PTY' THEN
2704:
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,

Line 2744: DELETE FROM psp_selected_persons_t

2740:
2741:
2742: ELSIF template_sel_criteria.array_sel_criteria(i) = 'EMP' THEN
2743:
2744: DELETE FROM psp_selected_persons_t
2745: WHERE request_id = p_request_id
2746: AND person_id IN (SELECT DISTINCT ppf.person_id
2747: FROM per_all_people_f ppf,
2748: per_assignments_f paf,

Line 2783: DELETE FROM psp_selected_persons_t

2779:
2780:
2781: ELSIF template_sel_criteria.array_sel_criteria(i) ='SUP' THEN
2782:
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,

Line 2819: delete from psp_selected_persons_t where request_id = p_request_id AND person_id in (

2815:
2816: ELSIF template_sel_criteria.array_sel_criteria(i) = 'AWD' THEN
2817:
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

Line 2856: DELETE FROM psp_selected_persons_t WHERE request_id = p_request_id AND person_id IN (

2852:
2853: ELSIF template_sel_criteria.array_sel_criteria(i) = 'ATY' THEN
2854:
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 ,

Line 2894: DELETE FROM psp_selected_persons_t WHERE request_id = p_request_id AND person_id IN (

2890:
2891: ELSIF template_sel_criteria.array_sel_criteria(i) ='PRT' THEN
2892:
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 ,

Line 2932: delete from psp_selected_persons_t where request_id = p_request_id AND person_id in (

2928:
2929: elsif template_sel_criteria.array_sel_criteria(i) ='PRJ' THEN
2930:
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

Line 2969: DELETE FROM psp_selected_persons_t

2965:
2966:
2967: elsif template_sel_criteria.array_sel_criteria(i) ='PAY' THEN
2968:
2969: DELETE FROM psp_selected_persons_t
2970: WHERE request_id = p_request_id
2971: AND person_id IN (SELECT person_id
2972: FROM per_assignments_f paf,
2973: psp_report_template_details_h prtd,

Line 3004: DELETE FROM psp_selected_persons_t

3000:
3001:
3002: elsif template_sel_criteria.array_sel_criteria(i) ='LOC' THEN
3003:
3004: DELETE FROM psp_selected_persons_t
3005: WHERE request_id = p_request_id
3006: AND person_id IN (SELECT person_id
3007: FROM per_assignments_f paf,
3008: psp_report_template_details_h prtd,

Line 3039: DELETE FROM psp_selected_persons_t

3035:
3036:
3037: elsif template_sel_criteria.array_sel_criteria(i) ='ORG' THEN
3038:
3039: DELETE FROM psp_selected_persons_t
3040: WHERE request_id = p_request_id
3041: AND person_id IN (SELECT person_id
3042: FROM per_assignments_f paf,
3043: psp_report_template_details_h prtd,

Line 3078: DELETE FROM psp_selected_persons_t

3074: */
3075:
3076: elsif template_sel_criteria.array_sel_criteria(i) = 'JOB' THEN
3077:
3078: DELETE FROM psp_selected_persons_t
3079: WHERE request_id = p_request_id
3080: AND person_id IN (SELECT person_id
3081: FROM per_assignments_f paf,
3082: psp_report_template_details_h prtd,

Line 3113: DELETE FROM psp_selected_persons_t

3109:
3110:
3111: elsif template_sel_criteria.array_sel_criteria(i) ='POS' THEN
3112:
3113: DELETE FROM psp_selected_persons_t
3114: WHERE request_id = p_request_id
3115: AND person_id IN (SELECT person_id
3116: FROM per_assignments_f paf,
3117: psp_report_template_details_h prtd,

Line 3147: DELETE FROM psp_selected_persons_t

3143: AND rownum=1)));
3144:
3145: elsif template_sel_criteria.array_sel_criteria(i) ='ASS' THEN
3146:
3147: DELETE FROM psp_selected_persons_t
3148: WHERE request_id = p_request_id
3149: AND person_id IN (SELECT person_id
3150: FROM per_assignments_f paf,
3151: psp_report_template_details_h prtd,

Line 3182: DELETE FROM psp_selected_persons_t

3178:
3179:
3180: elsif template_sel_criteria.array_sel_criteria(i)='CST' THEN
3181:
3182: DELETE FROM psp_selected_persons_t
3183: WHERE request_id = p_request_id
3184: AND person_id IN (SELECT NVL(person_id,0)
3185: FROM per_assignments_f paf,
3186: pay_payrolls_f ppf,

Line 3222: DELETE FROM psp_selected_persons_t

3218:
3219: elsif template_sel_criteria.array_sel_criteria(i) = 'AST' THEN
3220:
3221:
3222: DELETE FROM psp_selected_persons_t
3223: WHERE request_id = p_request_id
3224: AND person_id IN (SELECT NVL(paf.person_id,0)
3225: FROM per_all_assignments_f paf,
3226: hr_assignment_sets has,

Line 3306: g_exec_string := 'delete from psp_selected_persons_t where person_id

3302:
3303:
3304: IF l_sql_string is not null then
3305:
3306: g_exec_string := 'delete from psp_selected_persons_t where person_id
3307: in (select person_id from per_assignments_f paf,
3308: pay_people_groups ppg , per_assignment_status_types past
3309: where paf.people_group_id= ppg.people_group_id
3310: AND paf.assignment_type = ''' || 'E' || '''

Line 3371: g_exec_string := 'delete from psp_selected_persons_t where person_id

3367:
3368:
3369: IF l_sql_string is not null then
3370:
3371: g_exec_string := 'delete from psp_selected_persons_t where person_id
3372: in (select nvl(psl.person_id,0) from psp_summary_lines psl,
3373: psp_distribution_lines_history pdnh,
3374: psp_adjustment_lines_history palh, psp_pre_gen_dist_lines_history ppg,
3375: gl_code_combinations gcc

Line 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

3461: tot_cnt number;
3462: local_ctr number:=0;
3463: l_cnt number:=0;
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';

Line 3557: DELETE FROM psp_selected_persons_t

3553: l_results:= l_outputs(l_out_cnt).value;
3554:
3555:
3556: IF (l_results='FALSE' or l_results = 0 )THEN -- introduced for bug 4195678
3557: DELETE FROM psp_selected_persons_t
3558: WHERE person_id = r_ls_criteria_rec.l_person_id(k)
3559: AND assignment_id = r_ls_criteria_rec.l_assignment_id(k);
3560:
3561: END IF;

Line 4165: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)

4161: l_cnt NUMBER;
4162: BEGIN
4163: IF g_lookup_code = 'PTY' THEN
4164:
4165: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4166: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4167: FROM per_people_f ppf,
4168: per_assignments_f paf,
4169: per_assignment_status_types past

Line 4202: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)

4198: AND rownum=1));
4199:
4200:
4201: ELSIF g_lookup_code ='EMP' THEN
4202: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4203: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4204: FROM per_all_people_f ppf,
4205: per_assignments_f paf,
4206: per_assignment_status_types past

Line 4239: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)

4235: AND rownum=1));
4236:
4237:
4238: ELSIF g_lookup_code ='SUP' THEN
4239: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
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)

Line 4271: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)

4267: AND reversal_entry_flag IS NULL
4268: AND rownum=1));
4269:
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

Line 4307: INSERT INTO psp_selected_persons_t (request_id , person_id, assignment_id)

4303: AND palh.adjustment_batch_name IS NULL
4304: AND palh.reversal_entry_flag IS NULL
4305: AND NVL(palh.original_line_flag, 'N') ='N'));
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,

Line 4345: INSERT INTO psp_selected_persons_t (request_id , person_id, assignment_id)

4341: AND palh.reversal_entry_flag IS NULL
4342: AND NVL(palh.original_line_flag, 'N') ='N'));
4343:
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,

Line 4382: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)

4378: AND palh.adjustment_batch_name IS NULL
4379: AND palh.reversal_entry_flag IS NULL
4380: AND NVL(palh.original_line_flag, 'N') ='N'));
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

Line 4419: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)

4415: AND palh.reversal_entry_flag IS NULL
4416: AND NVL(palh.original_line_flag, 'N') ='N'));
4417: ELSIF g_lookup_code ='PAY' THEN
4418:
4419: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4420: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4421: FROM per_assignments_f paf,
4422: per_assignment_status_types past
4423: WHERE payroll_id IN (SELECT TO_NUMBER(criteria_value1)

Line 4452: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)

4448: AND rownum=1));
4449:
4450: ELSIF g_lookup_code ='LOC' THEN
4451:
4452: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4453: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4454: FROM per_assignments_f paf,
4455: per_assignment_status_types past
4456: WHERE location_id IN (SELECT TO_NUMBER(criteria_value1)

Line 4484: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)

4480: AND reversal_entry_flag IS NULL
4481: AND rownum=1));
4482:
4483: ELSIF g_lookup_code ='ORG' THEN
4484: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4485: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4486: FROM per_assignments_f paf,
4487: per_assignment_status_types past
4488: WHERE organization_id IN (SELECT TO_NUMBER(criteria_value1)

Line 4516: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)

4512: AND reversal_entry_flag IS NULL
4513: AND rownum=1));
4514:
4515: ELSIF g_lookup_code='CST' THEN
4516: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4517: SELECT DISTINCT p_request_id, person_id, assignment_id
4518: FROM per_assignments_f paf,
4519: pay_payrolls_f ppf,
4520: per_assignment_status_types past

Line 4553: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)

4549: AND rownum=1));
4550:
4551: ELSIF g_lookup_code = 'AST' THEN
4552:
4553: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4554: SELECT DISTINCT p_request_id, person_id, paf.assignment_id
4555: FROM per_all_assignments_f paf,
4556: hr_assignment_sets has,
4557: hr_assignment_set_amendments hasa,

Line 4601: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)

4597: AND rownum=1));
4598:
4599: ELSIF g_lookup_code ='JOB' THEN
4600:
4601: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4602: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4603: FROM per_assignments_f paf,
4604: per_assignment_status_types past
4605: WHERE job_id IN (SELECT TO_NUMBER(criteria_value1)

Line 4633: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)

4629: AND reversal_entry_flag IS NULL
4630: AND rownum=1));
4631:
4632: ELSIF g_lookup_code ='POS' THEN
4633: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4634: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4635: FROM per_assignments_f paf,
4636: per_assignment_status_types past
4637: WHERE position_id IN (SELECT TO_NUMBER(criteria_value1)

Line 4665: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)

4661: AND reversal_entry_flag IS NULL
4662: AND rownum=1));
4663:
4664: ELSIF g_lookup_code ='ASS' THEN
4665: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4666: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4667: FROM per_assignments_f paf,
4668: per_assignment_status_types past
4669: WHERE paf.assignment_status_type_id IN (SELECT TO_NUMBER(criteria_value1)

Line 4714: g_exec_string := 'INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)

4710: END LOOP;
4711: CLOSE ppg_cursor;
4712:
4713: IF l_sql_string IS NOT NULL THEN
4714: g_exec_string := 'INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4715: SELECT :request_id , person_id, assignment_id
4716: FROM per_assignments_f paf,
4717: pay_people_groups ppg,
4718: per_assignment_status_types past

Line 4767: g_exec_string := 'INSERT INTO psp_selected_persons_t(request_id, person_id, assignment_id)

4763: END LOOP;
4764: CLOSE gla_cursor;
4765:
4766: IF l_sql_string IS NOT NULL THEN
4767: g_exec_string := 'INSERT INTO psp_selected_persons_t(request_id, person_id, assignment_id)
4768: SELECT :p_request_id, psl.person_id, psl.assignment_id
4769: FROM psp_summary_lines psl,
4770: psp_distribution_lines_history pdnh,
4771: psp_adjustment_lines_history palh,

Line 4805: INSERT INTO psp_selected_persons_t(request_id, person_id, assignment_id)

4801: p_effort_end, p_effort_start, p_effort_end, p_effort_start, p_effort_end;
4802: END IF;
4803: END IF;
4804: ELSIF g_lookup_code='ALL' THEN
4805: INSERT INTO psp_selected_persons_t(request_id, person_id, assignment_id)
4806: SELECT DISTINCT p_request_id, person_id, assignment_id
4807: FROM per_assignments_f
4808: WHERE assignment_type = 'E'
4809: AND business_group_id = p_business_group_id

Line 4865: DELETE FROM psp_selected_persons_t pspt

4861: LOOP
4862: IF template_sel_criteria.array_inc_exc_flag(i) = 'I' THEN
4863: IF template_sel_criteria.array_sel_criteria(i) <> g_lookup_code THEN
4864: IF template_sel_criteria.array_sel_criteria(i) = 'PTY' THEN
4865: DELETE FROM psp_selected_persons_t pspt
4866: WHERE request_id = p_request_id
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

Line 4887: DELETE FROM psp_selected_persons_t pspt

4883: AND ppf.person_type_id = TO_NUMBER(prtd.criteria_value1)
4884: AND prtd.request_id = p_request_id
4885: AND ppf.person_id = pspt.person_id );
4886: ELSIF template_sel_criteria.array_sel_criteria(i) = 'EMP' THEN
4887: DELETE FROM psp_selected_persons_t pspt
4888: WHERE request_id = p_request_id
4889: /* Bug 5087294 : Performance fix replacing not in with not exists */
4890: -- AND person_id NOT IN (SELECT NVL(person_id,0)
4891: AND NOT EXISTS ( SELECT 1

Line 4907: DELETE FROM psp_selected_persons_t pspt

4903: AND ppf.effective_start_date <= p_effort_end
4904: AND ppf.effective_end_date >= p_effort_start
4905: AND ppf.person_id = pspt.person_id );
4906: ELSIF template_sel_criteria.array_sel_criteria(i) ='SUP' THEN
4907: DELETE FROM psp_selected_persons_t pspt
4908: WHERE request_id = p_request_id
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

Line 4924: DELETE FROM psp_selected_persons_t

4920: AND paf.effective_start_date <= p_effort_end
4921: AND effective_end_date >= p_effort_start
4922: AND paf.assignment_id = pspt.assignment_id );
4923: ELSIF template_sel_criteria.array_sel_criteria(i) = 'AWD' THEN
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 ,

Line 4965: DELETE FROM psp_selected_persons_t

4961: AND palh.adjustment_batch_name IS NULL
4962: AND palh.reversal_entry_flag IS NULL
4963: AND NVL(palh.original_line_flag, 'N') ='N')));
4964: ELSIF template_sel_criteria.array_sel_criteria(i) = 'ATY' THEN
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,

Line 5007: DELETE FROM psp_selected_persons_t

5003: AND palh.adjustment_batch_name IS NULL
5004: AND palh.reversal_entry_flag IS NULL
5005: AND NVL(palh.original_line_flag, 'N') ='N')));
5006: ELSIF template_sel_criteria.array_sel_criteria(i) ='PRT' THEN
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,

Line 5049: DELETE FROM psp_selected_persons_t

5045: AND palh.adjustment_batch_name IS NULL
5046: AND palh.reversal_entry_flag IS NULL
5047: AND NVL(palh.original_line_flag, 'N') ='N')));
5048: ELSIF template_sel_criteria.array_sel_criteria(i) ='PRJ' THEN
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 ,

Line 5090: DELETE FROM psp_selected_persons_t pspt

5086: AND palh.adjustment_batch_name IS NULL
5087: AND palh.reversal_entry_flag IS NULL
5088: AND NVL(palh.original_line_flag, 'N') ='N')));
5089: ELSIF template_sel_criteria.array_sel_criteria(i) ='PAY' THEN
5090: DELETE FROM psp_selected_persons_t pspt
5091: WHERE request_id = p_request_id
5092: /* Bug 5087294 : Performance fix replacing not in with not exists */
5093: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5094: AND NOT EXISTS ( SELECT 1

Line 5108: DELETE FROM psp_selected_persons_t pspt

5104: AND paf.payroll_id = TO_NUMBER(prtd.criteria_value1)
5105: AND prtd.request_id = p_request_id
5106: AND paf.assignment_id = pspt.assignment_id );
5107: ELSIF template_sel_criteria.array_sel_criteria(i) ='LOC' THEN
5108: DELETE FROM psp_selected_persons_t pspt
5109: WHERE request_id = p_request_id
5110: /* Bug 5087294 : Performance fix replacing not in with not exists */
5111: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5112: AND NOT EXISTS ( SELECT 1

Line 5127: DELETE FROM psp_selected_persons_t pspt

5123: AND prtd.request_id = p_request_id
5124: AND paf.assignment_id = pspt.assignment_id);
5125: ELSIF template_sel_criteria.array_sel_criteria(i) ='ORG' THEN
5126: /* Bug 5087294 : Performance fix replacing not in with not exists */
5127: DELETE FROM psp_selected_persons_t pspt
5128: WHERE request_id = p_request_id
5129: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5130: AND NOT EXISTS ( SELECT 1
5131: FROM per_assignments_f paf ,

Line 5144: DELETE FROM psp_selected_persons_t pspt

5140: AND prtd.request_id = p_request_id
5141: AND paf.assignment_id = pspt.assignment_id );
5142:
5143: ELSIF template_sel_criteria.array_sel_criteria(i)='CST' THEN
5144: DELETE FROM psp_selected_persons_t pspt
5145: WHERE request_id = p_request_id
5146: /* Bug 5087294 : Performance fix replacing not in with not exists */
5147: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5148: AND NOT EXISTS ( SELECT 1

Line 5165: DELETE FROM psp_selected_persons_t

5161: AND paf.effective_start_date <= p_effort_end
5162: AND paf.effective_end_date >= p_effort_start
5163: AND paf.assignment_id = pspt.assignment_id );
5164: ELSIF template_sel_criteria.array_sel_criteria(i) = 'AST' THEN
5165: DELETE FROM psp_selected_persons_t
5166: WHERE request_id = p_request_id
5167: AND assignment_id NOT IN (SELECT NVL(paf.assignment_id,0)
5168: FROM per_all_assignments_f paf,
5169: hr_assignment_sets has,

Line 5196: DELETE FROM psp_selected_persons_t pspt

5192: AND hasa.include_or_exclude ='E'
5193: AND paf.effective_start_date <= p_effort_end
5194: AND paf.effective_end_date >= p_effort_start));
5195: ELSIF template_sel_criteria.array_sel_criteria(i) = 'JOB' THEN
5196: DELETE FROM psp_selected_persons_t pspt
5197: WHERE request_id = p_request_id
5198: /* Bug 5087294 : Performance fix replacing not in with not exists */
5199: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5200: AND NOT EXISTS ( SELECT 1

Line 5214: DELETE FROM psp_selected_persons_t pspt

5210: AND paf.job_id = TO_NUMBER(prtd.criteria_value1)
5211: AND prtd.request_id = p_request_id
5212: AND paf.assignment_id = pspt.assignment_id);
5213: ELSIF template_sel_criteria.array_sel_criteria(i) ='POS' THEN
5214: DELETE FROM psp_selected_persons_t pspt
5215: WHERE request_id = p_request_id
5216: /* Bug 5087294 : Performance fix replacing not in with not exists */
5217: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5218: AND NOT EXISTS ( SELECT 1

Line 5232: DELETE FROM psp_selected_persons_t pspt

5228: AND paf.position_id = TO_NUMBER(prtd.criteria_value1)
5229: AND prtd.request_id = p_request_id
5230: AND paf.assignment_id = pspt.assignment_id);
5231: ELSIF template_sel_criteria.array_sel_criteria(i) ='ASS' THEN
5232: DELETE FROM psp_selected_persons_t pspt
5233: WHERE request_id = p_request_id
5234: /* Bug 5087294 : Performance fix replacing not in with not exists */
5235: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5236: AND NOT EXISTS ( SELECT 1

Line 5265: g_exec_string := 'DELETE FROM psp_selected_persons_t sel

5261: END LOOP;
5262: CLOSE ppg_cursor;
5263:
5264: IF l_sql_string IS NOT NULL THEN
5265: g_exec_string := 'DELETE FROM psp_selected_persons_t sel
5266: WHERE request_id = :request_id
5267: AND NOT EXISTS (SELECT 1
5268: FROM per_assignments_f paf, pay_people_groups ppg
5269: WHERE paf.people_group_id= ppg.people_group_id

Line 5300: g_exec_string := 'DELETE FROM psp_selected_persons_t

5296: END LOOP;
5297: CLOSE gla_cursor;
5298:
5299: IF l_sql_string IS NOT NULL THEN
5300: g_exec_string := 'DELETE FROM psp_selected_persons_t
5301: WHERE assignment_id NOT IN (SELECT NVL(psl.assignment_id,0)
5302: FROM psp_summary_lines psl, psp_distribution_lines_history pdnh,
5303: psp_adjustment_lines_history palh,
5304: psp_pre_gen_dist_lines_history ppg, gl_code_combinations gcc

Line 5380: DELETE FROM psp_selected_persons_t

5376: FOR i IN 1..template_sel_criteria.array_sel_criteria.COUNT
5377: LOOP
5378: IF template_sel_criteria.array_inc_exc_flag(i) = 'E' THEN
5379: IF template_sel_criteria.array_sel_criteria(i) = 'PTY' THEN
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,

Line 5399: DELETE FROM psp_selected_persons_t

5395: AND include_exclude_flag='E'
5396: AND ppf.person_type_id = TO_NUMBER(prtd.criteria_value1)
5397: AND prtd.request_id = p_request_id);
5398: ELSIF template_sel_criteria.array_sel_criteria(i) = 'EMP' THEN
5399: DELETE FROM psp_selected_persons_t
5400: WHERE request_id = p_request_id
5401: AND person_id IN (SELECT DISTINCT ppf.person_id
5402: FROM per_all_people_f ppf, per_assignments_f paf
5403: WHERE ppf.person_id IN (SELECT TO_NUMBER(criteria_value1)

Line 5416: DELETE FROM psp_selected_persons_t

5412: AND paf.effective_end_date >= p_effort_start
5413: AND ppf.effective_start_date <= p_effort_end
5414: AND ppf.effective_end_date >= p_effort_start);
5415: ELSIF template_sel_criteria.array_sel_criteria(i) ='SUP' THEN
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

Line 5430: DELETE FROM psp_selected_persons_t

5426: AND paf.assignment_type = 'E'
5427: AND paf.effective_start_date <= p_effort_end
5428: AND paf.effective_end_date >= p_effort_start);
5429: ELSIF template_sel_criteria.array_sel_criteria(i) = 'AWD' THEN
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,

Line 5468: DELETE FROM psp_selected_persons_t

5464: AND palh.adjustment_batch_name IS NULL
5465: AND palh.reversal_entry_flag IS NULL
5466: AND NVL(palh.original_line_flag, 'N') ='N')));
5467: ELSIF template_sel_criteria.array_sel_criteria(i) = 'ATY' THEN
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,

Line 5507: DELETE FROM psp_selected_persons_t

5503: AND palh.adjustment_batch_name IS NULL
5504: AND palh.reversal_entry_flag IS NULL
5505: AND NVL(palh.original_line_flag, 'N') ='N')));
5506: ELSIF template_sel_criteria.array_sel_criteria(i) ='PRT' THEN
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,

Line 5546: DELETE FROM psp_selected_persons_t

5542: AND palh.adjustment_batch_name IS NULL
5543: AND palh.reversal_entry_flag IS NULL
5544: AND NVL(palh.original_line_flag, 'N') ='N')));
5545: ELSIF template_sel_criteria.array_sel_criteria(i) ='PRJ' THEN
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,

Line 5584: DELETE FROM psp_selected_persons_t

5580: AND palh.adjustment_batch_name IS NULL
5581: AND palh.reversal_entry_flag IS NULL
5582: AND NVL(palh.original_line_flag, 'N') ='N')));
5583: ELSIF template_sel_criteria.array_sel_criteria(i) ='PAY' THEN
5584: DELETE FROM psp_selected_persons_t
5585: WHERE request_id = p_request_id
5586: AND assignment_id IN (SELECT assignment_id
5587: FROM per_assignments_f paf,
5588: psp_report_template_details_h prtd

Line 5598: DELETE FROM psp_selected_persons_t

5594: effective_end_date >= p_effort_start
5595: AND paf.payroll_id = TO_NUMBER(prtd.criteria_value1)
5596: AND prtd.request_id = p_request_id);
5597: ELSIF template_sel_criteria.array_sel_criteria(i) ='LOC' THEN
5598: DELETE FROM psp_selected_persons_t
5599: WHERE request_id = p_request_id
5600: AND assignment_id IN (SELECT assignment_id
5601: FROM per_assignments_f paf,
5602: psp_report_template_details_h prtd

Line 5612: DELETE FROM psp_selected_persons_t

5608: effective_end_date >= p_effort_start
5609: AND paf.location_id = TO_NUMBER(prtd.criteria_value1)
5610: AND prtd.request_id = p_request_id);
5611: ELSIF template_sel_criteria.array_sel_criteria(i) ='ORG' THEN
5612: DELETE FROM psp_selected_persons_t
5613: WHERE request_id = p_request_id
5614: AND assignment_id IN (SELECT assignment_id
5615: FROM per_assignments_f paf,
5616: psp_report_template_details_h prtd

Line 5626: DELETE FROM psp_selected_persons_t

5622: effective_end_date >= p_effort_start
5623: AND paf.organization_id = TO_NUMBER(prtd.criteria_value1)
5624: AND prtd.request_id = p_request_id);
5625: ELSIF template_sel_criteria.array_sel_criteria(i) = 'JOB' THEN
5626: DELETE FROM psp_selected_persons_t
5627: WHERE request_id = p_request_id
5628: AND assignment_id IN (SELECT assignment_id
5629: FROM per_assignments_f paf,
5630: psp_report_template_details_h prtd

Line 5640: DELETE FROM psp_selected_persons_t

5636: effective_end_date >= p_effort_start
5637: AND paf.job_id = TO_NUMBER(prtd.criteria_value1)
5638: AND prtd.request_id = p_request_id);
5639: ELSIF template_sel_criteria.array_sel_criteria(i) ='POS' THEN
5640: DELETE FROM psp_selected_persons_t
5641: WHERE request_id = p_request_id
5642: AND assignment_id IN (SELECT assignment_id
5643: FROM per_assignments_f paf,
5644: psp_report_template_details_h prtd

Line 5654: DELETE FROM psp_selected_persons_t

5650: effective_end_date >= p_effort_start
5651: AND paf.position_id = TO_NUMBER(prtd.criteria_value1)
5652: AND prtd.request_id = p_request_id);
5653: ELSIF template_sel_criteria.array_sel_criteria(i) ='ASS' THEN
5654: DELETE FROM psp_selected_persons_t
5655: WHERE request_id = p_request_id
5656: AND assignment_id IN (SELECT assignment_id
5657: FROM per_assignments_f paf,
5658: psp_report_template_details_h prtd

Line 5668: DELETE FROM psp_selected_persons_t

5664: AND prtd.request_id = p_request_id
5665: AND effective_start_date <= p_effort_end
5666: AND effective_end_date >= p_effort_start);
5667: ELSIF template_sel_criteria.array_sel_criteria(i)='CST' THEN
5668: DELETE FROM psp_selected_persons_t
5669: WHERE request_id = p_request_id
5670: AND assignment_id IN (SELECT NVL(assignment_id,0)
5671: FROM per_assignments_f paf,
5672: pay_payrolls_f ppf

Line 5686: DELETE FROM psp_selected_persons_t

5682: AND ppf.effective_end_date >= p_effort_start
5683: AND paf.effective_start_date <= p_effort_end
5684: AND paf.effective_end_date >= p_effort_start);
5685: ELSIF template_sel_criteria.array_sel_criteria(i) = 'AST' THEN
5686: DELETE FROM psp_selected_persons_t
5687: WHERE request_id = p_request_id
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

Line 5732: g_exec_string := 'DELETE FROM psp_selected_persons_t

5728: END LOOP;
5729: CLOSE ppg_cursor;
5730:
5731: IF l_sql_string IS NOT NULL THEN
5732: g_exec_string := 'DELETE FROM psp_selected_persons_t
5733: WHERE assignment_id IN (SELECT assignment_id
5734: FROM per_assignments_f paf,
5735: pay_people_groups ppg
5736: WHERE paf.people_group_id= ppg.people_group_id

Line 5767: g_exec_string := 'DELETE FROM psp_selected_persons_t

5763: END LOOP;
5764: CLOSE gla_cursor;
5765:
5766: IF l_sql_string IS NOT NULL THEN
5767: g_exec_string := 'DELETE FROM psp_selected_persons_t
5768: WHERE assignment_id IN (SELECT NVL(psl.assignment_id,0)
5769: FROM psp_summary_lines psl,
5770: psp_distribution_lines_history pdnh,
5771: psp_adjustment_lines_history palh,