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 1807: insert into psp_selected_persons_t(request_id, person_id)(select p_request_id,

1803: END IF;
1804:
1805: ELSIf g_lookup_code='ALL' then
1806:
1807: insert into psp_selected_persons_t(request_id, person_id)(select p_request_id,
1808: person_id from per_people_f where
1809: business_group_id = p_business_group_id and
1810: effective_start_date <= p_effort_end and
1811: effective_end_date >= p_effort_start);

Line 1880: DELETE FROM psp_selected_persons_t pspt

1876:
1877: IF template_sel_criteria.array_sel_criteria(i) <> g_lookup_code then
1878: IF template_sel_criteria.array_sel_criteria(i) = 'PTY' THEN
1879:
1880: DELETE FROM psp_selected_persons_t pspt
1881: WHERE request_id = p_request_id
1882: /* Bug 5087294 : Performance fix replacing not in with not exists */
1883: -- AND person_id NOT IN (SELECT NVL( person_id, 0)
1884: AND NOT EXISTS ( SELECT 1

Line 1921: DELETE FROM psp_selected_persons_t pspt

1917: AND rownum=1)));
1918:
1919: ELSIF template_sel_criteria.array_sel_criteria(i) = 'EMP' THEN
1920:
1921: DELETE FROM psp_selected_persons_t pspt
1922: WHERE request_id = p_request_id
1923: /* Bug 5087294 : Performance fix replacing not in with not exists */
1924: -- AND person_id NOT IN (SELECT NVL(person_id,0)
1925: AND NOT EXISTS ( SELECT 1

Line 1963: DELETE FROM psp_selected_persons_t pspt

1959:
1960:
1961: ELSIF template_sel_criteria.array_sel_criteria(i) ='SUP' THEN
1962:
1963: DELETE FROM psp_selected_persons_t pspt
1964: WHERE request_id = p_request_id
1965: /* Bug 5087294 : Performance fix replacing not in with not exists */
1966: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
1967: AND NOT EXISTS ( SELECT 1

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

1999: ELSIF template_sel_criteria.array_sel_criteria(i) = 'AWD' THEN
2000:
2001:
2002: --- replaced original query for performance issues -- 4429787
2003: delete from psp_selected_persons_t where request_id = p_request_id AND person_id not in (
2004: select psl.person_id
2005: FROM psp_summary_lines psl,
2006: psp_report_template_details_h prtd ,
2007: per_time_periods ptp

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

2037: ELSIF template_sel_criteria.array_sel_criteria(i) = 'ATY' then
2038:
2039:
2040: --- replaced non-performant delete with this for 4429787
2041: DELETE FROM psp_selected_persons_t WHERE request_id = p_request_id AND person_id NOT IN (
2042: SELECT NVL(psl.person_id,0)
2043: FROM psp_summary_lines psl,
2044: psp_report_template_details_h prtd ,
2045: gms_awards_all gaa ,

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

2075:
2076: ELSIF template_sel_criteria.array_sel_criteria(i) ='PRT' THEN
2077:
2078: --- replaced non-performant delete with this for 4429787
2079: DELETE FROM psp_selected_persons_t WHERE request_id = p_request_id AND person_id NOT IN (
2080: SELECT NVL(psl.person_id,0)
2081: FROM psp_summary_lines psl,
2082: psp_report_template_details_h prtd ,
2083: pa_projects_all ppa ,

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

2113:
2114: elsif template_sel_criteria.array_sel_criteria(i) ='PRJ' THEN
2115:
2116: --- replaced non-performant insert with this for 4429787
2117: delete from psp_selected_persons_t where request_id = p_request_id AND person_id not in (
2118: select nvl( psl.person_id ,0)
2119: FROM psp_summary_lines psl,
2120: psp_report_template_details_h prtd ,
2121: per_time_periods ptp

Line 2153: DELETE FROM psp_selected_persons_t pspt

2149: NVL(palh.original_line_flag, 'N') ='N')));
2150:
2151: elsif template_sel_criteria.array_sel_criteria(i) ='PAY' THEN
2152:
2153: DELETE FROM psp_selected_persons_t pspt
2154: WHERE request_id = p_request_id
2155: /* Bug 5087294 : Performance fix replacing not in with not exists */
2156: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2157: AND NOT EXISTS ( SELECT 1

Line 2192: DELETE FROM psp_selected_persons_t pspt

2188:
2189:
2190: elsif template_sel_criteria.array_sel_criteria(i) ='LOC' THEN
2191:
2192: DELETE FROM psp_selected_persons_t pspt
2193: WHERE request_id = p_request_id
2194: /* Bug 5087294 : Performance fix replacing not in with not exists */
2195: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2196: AND NOT EXISTS ( SELECT 1

Line 2229: DELETE FROM psp_selected_persons_t pspt

2225: AND rownum=1)));
2226:
2227: elsif template_sel_criteria.array_sel_criteria(i) ='ORG' THEN
2228:
2229: DELETE FROM psp_selected_persons_t pspt
2230: WHERE request_id = p_request_id
2231: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2232: AND NOT EXISTS ( SELECT 1
2233: FROM per_assignments_f paf ,

Line 2265: DELETE FROM psp_selected_persons_t pspt

2261: AND rownum=1)));
2262:
2263:
2264: elsif template_sel_criteria.array_sel_criteria(i)='CST' THEN
2265: DELETE FROM psp_selected_persons_t pspt
2266: WHERE request_id = p_request_id
2267: /* Bug 5087294 : Performance fix replacing not in with not exists */
2268: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2269: AND NOT EXISTS ( SELECT 1

Line 2307: DELETE FROM psp_selected_persons_t

2303:
2304:
2305: elsif template_sel_criteria.array_sel_criteria(i) = 'AST' THEN
2306:
2307: DELETE FROM psp_selected_persons_t
2308: WHERE request_id = p_request_id
2309: AND person_id NOT IN (SELECT NVL(paf.person_id,0)
2310: FROM per_all_assignments_f paf,
2311: hr_assignment_sets has,

Line 2359: DELETE FROM psp_selected_persons_t pspt

2355:
2356:
2357: elsif template_sel_criteria.array_sel_criteria(i) = 'JOB' THEN
2358:
2359: DELETE FROM psp_selected_persons_t pspt
2360: WHERE request_id = p_request_id
2361: /* Bug 5087294 : Performance fix replacing not in with not exists */
2362: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2363: AND NOT EXISTS ( SELECT 1

Line 2396: DELETE FROM psp_selected_persons_t pspt

2392: AND rownum=1)));
2393:
2394: elsif template_sel_criteria.array_sel_criteria(i) ='POS' THEN
2395:
2396: DELETE FROM psp_selected_persons_t pspt
2397: WHERE request_id = p_request_id
2398: /* Bug 5087294 : Performance fix replacing not in with not exists */
2399: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2400: AND NOT EXISTS ( SELECT 1

Line 2435: DELETE FROM psp_selected_persons_t pspt

2431:
2432: elsif template_sel_criteria.array_sel_criteria(i) ='ASS' THEN
2433:
2434:
2435: DELETE FROM psp_selected_persons_t pspt
2436: WHERE request_id = p_request_id
2437: /* Bug 5087294 : Performance fix replacing not in with not exists */
2438: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2439: AND NOT EXISTS ( SELECT 1

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

2510:
2511: if l_sql_string is not null then
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' || '''

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

2573: -- l_sql_string:= g_exec_string;
2574:
2575: if l_sql_string is not null then
2576:
2577: g_exec_string := 'delete from psp_selected_persons_t where person_id
2578: not in (select nvl(psl.person_id,0) from psp_summary_lines psl,
2579: psp_distribution_lines_history pdnh,
2580: psp_adjustment_lines_history palh, psp_pre_gen_dist_lines_history ppg,
2581: gl_code_combinations gcc

Line 2672: DELETE FROM psp_selected_persons_t

2668: -- IF template_sel_criteria.array_sel_criteria(i) <> g_lookup_code THEN
2669:
2670: IF template_sel_criteria.array_sel_criteria(i) = 'PTY' THEN
2671:
2672: DELETE FROM psp_selected_persons_t
2673: WHERE request_id = p_request_id
2674: AND person_id IN (SELECT ppf.person_id
2675: FROM per_people_f ppf,
2676: psp_report_template_details_h prtd,

Line 2711: DELETE FROM psp_selected_persons_t

2707:
2708:
2709: ELSIF template_sel_criteria.array_sel_criteria(i) = 'EMP' THEN
2710:
2711: DELETE FROM psp_selected_persons_t
2712: WHERE request_id = p_request_id
2713: AND person_id IN (SELECT DISTINCT ppf.person_id
2714: FROM per_all_people_f ppf,
2715: per_assignments_f paf,

Line 2750: DELETE FROM psp_selected_persons_t

2746:
2747:
2748: ELSIF template_sel_criteria.array_sel_criteria(i) ='SUP' THEN
2749:
2750: DELETE FROM psp_selected_persons_t
2751: WHERE request_id = p_request_id
2752: AND person_id IN (SELECT NVL(person_id,0)
2753: FROM per_all_assignments_f paf,
2754: psp_report_template_details_h prtd,

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

2782:
2783: ELSIF template_sel_criteria.array_sel_criteria(i) = 'AWD' THEN
2784:
2785: --- replaced original query for performance issues -- 4429787
2786: delete from psp_selected_persons_t where request_id = p_request_id AND person_id in (
2787: select psl.person_id
2788: FROM psp_summary_lines psl,
2789: psp_report_template_details_h prtd ,
2790: per_time_periods ptp

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

2819:
2820: ELSIF template_sel_criteria.array_sel_criteria(i) = 'ATY' THEN
2821:
2822: --- replaced non-performant delete with this for 4429787
2823: DELETE FROM psp_selected_persons_t WHERE request_id = p_request_id AND person_id IN (
2824: SELECT NVL(psl.person_id,0)
2825: FROM psp_summary_lines psl,
2826: psp_report_template_details_h prtd ,
2827: gms_awards_all gaa ,

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

2857:
2858: ELSIF template_sel_criteria.array_sel_criteria(i) ='PRT' THEN
2859:
2860: --- replaced non-performant delete with this for 4429787
2861: DELETE FROM psp_selected_persons_t WHERE request_id = p_request_id AND person_id IN (
2862: SELECT NVL(psl.person_id,0)
2863: FROM psp_summary_lines psl,
2864: psp_report_template_details_h prtd ,
2865: pa_projects_all ppa ,

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

2895:
2896: elsif template_sel_criteria.array_sel_criteria(i) ='PRJ' THEN
2897:
2898: --- replaced non-performant insert with this for 4429787
2899: delete from psp_selected_persons_t where request_id = p_request_id AND person_id in (
2900: select nvl( psl.person_id ,0)
2901: FROM psp_summary_lines psl,
2902: psp_report_template_details_h prtd ,
2903: per_time_periods ptp

Line 2936: DELETE FROM psp_selected_persons_t

2932:
2933:
2934: elsif template_sel_criteria.array_sel_criteria(i) ='PAY' THEN
2935:
2936: DELETE FROM psp_selected_persons_t
2937: WHERE request_id = p_request_id
2938: AND person_id IN (SELECT person_id
2939: FROM per_assignments_f paf,
2940: psp_report_template_details_h prtd,

Line 2971: DELETE FROM psp_selected_persons_t

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

Line 3006: DELETE FROM psp_selected_persons_t

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

Line 3045: DELETE FROM psp_selected_persons_t

3041: */
3042:
3043: elsif template_sel_criteria.array_sel_criteria(i) = 'JOB' THEN
3044:
3045: DELETE FROM psp_selected_persons_t
3046: WHERE request_id = p_request_id
3047: AND person_id IN (SELECT person_id
3048: FROM per_assignments_f paf,
3049: psp_report_template_details_h prtd,

Line 3080: DELETE FROM psp_selected_persons_t

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

Line 3114: DELETE FROM psp_selected_persons_t

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

Line 3149: DELETE FROM psp_selected_persons_t

3145:
3146:
3147: elsif template_sel_criteria.array_sel_criteria(i)='CST' THEN
3148:
3149: DELETE FROM psp_selected_persons_t
3150: WHERE request_id = p_request_id
3151: AND person_id IN (SELECT NVL(person_id,0)
3152: FROM per_assignments_f paf,
3153: pay_payrolls_f ppf,

Line 3189: DELETE FROM psp_selected_persons_t

3185:
3186: elsif template_sel_criteria.array_sel_criteria(i) = 'AST' THEN
3187:
3188:
3189: DELETE FROM psp_selected_persons_t
3190: WHERE request_id = p_request_id
3191: AND person_id IN (SELECT NVL(paf.person_id,0)
3192: FROM per_all_assignments_f paf,
3193: hr_assignment_sets has,

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

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

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

3334:
3335:
3336: IF l_sql_string is not null then
3337:
3338: g_exec_string := 'delete from psp_selected_persons_t where person_id
3339: in (select nvl(psl.person_id,0) from psp_summary_lines psl,
3340: psp_distribution_lines_history pdnh,
3341: psp_adjustment_lines_history palh, psp_pre_gen_dist_lines_history ppg,
3342: gl_code_combinations gcc

Line 3432: CURSOR select_everyone_csr is select person_id , assignment_id, p_request_id, p_effort_start, p_effort_end from psp_selected_persons_t where

3428: tot_cnt number;
3429: local_ctr number:=0;
3430: l_cnt number:=0;
3431:
3432: CURSOR select_everyone_csr is select person_id , assignment_id, p_request_id, p_effort_start, p_effort_end from psp_selected_persons_t where
3433: request_id =p_request_id;
3434:
3435: CURSOR get_ff_for_template_csr is select criteria_value1 from psp_report_template_details_h where
3436: request_id = p_request_id and criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code='FFE';

Line 3524: DELETE FROM psp_selected_persons_t

3520: l_results:= l_outputs(l_out_cnt).value;
3521:
3522:
3523: IF (l_results='FALSE' or l_results = 0 )THEN -- introduced for bug 4195678
3524: DELETE FROM psp_selected_persons_t
3525: WHERE person_id = r_ls_criteria_rec.l_person_id(k)
3526: AND assignment_id = r_ls_criteria_rec.l_assignment_id(k);
3527:
3528: END IF;

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

4128: l_cnt NUMBER;
4129: BEGIN
4130: IF g_lookup_code = 'PTY' THEN
4131:
4132: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4133: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4134: FROM per_people_f ppf,
4135: per_assignments_f paf,
4136: per_assignment_status_types past

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

4165: AND rownum=1));
4166:
4167:
4168: ELSIF g_lookup_code ='EMP' THEN
4169: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4170: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4171: FROM per_all_people_f ppf,
4172: per_assignments_f paf,
4173: per_assignment_status_types past

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

4202: AND rownum=1));
4203:
4204:
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)

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

4234: AND reversal_entry_flag IS NULL
4235: AND rownum=1));
4236:
4237: ELSIF g_lookup_code='AWD' THEN
4238: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4239: SELECT DISTINCT p_request_id, psl.person_id, psl.assignment_id
4240: FROM psp_summary_lines psl,
4241: psp_report_template_details_h prtd,
4242: per_time_periods ptp

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

4270: AND palh.adjustment_batch_name IS NULL
4271: AND palh.reversal_entry_flag IS NULL
4272: AND NVL(palh.original_line_flag, 'N') ='N'));
4273: ELSIF g_lookup_code ='ATY' THEN
4274: INSERT INTO psp_selected_persons_t (request_id , person_id, assignment_id)
4275: SELECT DISTINCT p_request_id, psl.person_id, psl.assignment_id
4276: FROM psp_summary_lines psl,
4277: psp_report_template_details_h prtd,
4278: gms_awards_all gaa,

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

4308: AND palh.reversal_entry_flag IS NULL
4309: AND NVL(palh.original_line_flag, 'N') ='N'));
4310:
4311: ELSIF g_lookup_code ='PRT' THEN
4312: INSERT INTO psp_selected_persons_t (request_id , person_id, assignment_id)
4313: SELECT DISTINCT p_request_id, psl.person_id, psl.assignment_id
4314: FROM psp_summary_lines psl,
4315: psp_report_template_details_h prtd,
4316: pa_projects_all ppa,

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

4345: AND palh.adjustment_batch_name IS NULL
4346: AND palh.reversal_entry_flag IS NULL
4347: AND NVL(palh.original_line_flag, 'N') ='N'));
4348: ELSIF g_lookup_code ='PRJ' THEN
4349: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4350: SELECT DISTINCT p_request_id, psl.person_id, psl.assignment_id
4351: FROM psp_summary_lines psl,
4352: psp_report_template_details_h prtd,
4353: per_time_periods ptp

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

4382: AND palh.reversal_entry_flag IS NULL
4383: AND NVL(palh.original_line_flag, 'N') ='N'));
4384: ELSIF g_lookup_code ='PAY' THEN
4385:
4386: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4387: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4388: FROM per_assignments_f paf,
4389: per_assignment_status_types past
4390: WHERE payroll_id IN (SELECT TO_NUMBER(criteria_value1)

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

4415: AND rownum=1));
4416:
4417: ELSIF g_lookup_code ='LOC' THEN
4418:
4419: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4420: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4421: FROM per_assignments_f paf,
4422: per_assignment_status_types past
4423: WHERE location_id IN (SELECT TO_NUMBER(criteria_value1)

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

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

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

4479: AND reversal_entry_flag IS NULL
4480: AND rownum=1));
4481:
4482: ELSIF g_lookup_code='CST' THEN
4483: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4484: SELECT DISTINCT p_request_id, person_id, assignment_id
4485: FROM per_assignments_f paf,
4486: pay_payrolls_f ppf,
4487: per_assignment_status_types past

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

4516: AND rownum=1));
4517:
4518: ELSIF g_lookup_code = 'AST' THEN
4519:
4520: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
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,

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

4564: AND rownum=1));
4565:
4566: ELSIF g_lookup_code ='JOB' THEN
4567:
4568: INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4569: SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4570: FROM per_assignments_f paf,
4571: per_assignment_status_types past
4572: WHERE job_id IN (SELECT TO_NUMBER(criteria_value1)

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

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

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

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

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

4677: END LOOP;
4678: CLOSE ppg_cursor;
4679:
4680: IF l_sql_string IS NOT NULL THEN
4681: g_exec_string := 'INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4682: SELECT :request_id , person_id, assignment_id
4683: FROM per_assignments_f paf,
4684: pay_people_groups ppg,
4685: per_assignment_status_types past

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

4730: END LOOP;
4731: CLOSE gla_cursor;
4732:
4733: IF l_sql_string IS NOT NULL THEN
4734: g_exec_string := 'INSERT INTO psp_selected_persons_t(request_id, person_id, assignment_id)
4735: SELECT :p_request_id, psl.person_id, psl.assignment_id
4736: FROM psp_summary_lines psl,
4737: psp_distribution_lines_history pdnh,
4738: psp_adjustment_lines_history palh,

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

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

Line 4832: DELETE FROM psp_selected_persons_t pspt

4828: LOOP
4829: IF template_sel_criteria.array_inc_exc_flag(i) = 'I' THEN
4830: IF template_sel_criteria.array_sel_criteria(i) <> g_lookup_code THEN
4831: IF template_sel_criteria.array_sel_criteria(i) = 'PTY' THEN
4832: DELETE FROM psp_selected_persons_t pspt
4833: WHERE request_id = p_request_id
4834: /* Bug 5087294 : Performance fix replacing not in with not exists */
4835: -- AND person_id NOT IN (SELECT NVL( person_id, 0)
4836: AND NOT EXISTS ( SELECT 1

Line 4854: DELETE FROM psp_selected_persons_t pspt

4850: AND ppf.person_type_id = TO_NUMBER(prtd.criteria_value1)
4851: AND prtd.request_id = p_request_id
4852: AND ppf.person_id = pspt.person_id );
4853: ELSIF template_sel_criteria.array_sel_criteria(i) = 'EMP' THEN
4854: DELETE FROM psp_selected_persons_t pspt
4855: WHERE request_id = p_request_id
4856: /* Bug 5087294 : Performance fix replacing not in with not exists */
4857: -- AND person_id NOT IN (SELECT NVL(person_id,0)
4858: AND NOT EXISTS ( SELECT 1

Line 4874: DELETE FROM psp_selected_persons_t pspt

4870: AND ppf.effective_start_date <= p_effort_end
4871: AND ppf.effective_end_date >= p_effort_start
4872: AND ppf.person_id = pspt.person_id );
4873: ELSIF template_sel_criteria.array_sel_criteria(i) ='SUP' THEN
4874: DELETE FROM psp_selected_persons_t pspt
4875: WHERE request_id = p_request_id
4876: /* Bug 5087294 : Performance fix replacing not in with not exists */
4877: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
4878: AND NOT EXISTS ( SELECT 1

Line 4891: DELETE FROM psp_selected_persons_t

4887: AND paf.effective_start_date <= p_effort_end
4888: AND effective_end_date >= p_effort_start
4889: AND paf.assignment_id = pspt.assignment_id );
4890: ELSIF template_sel_criteria.array_sel_criteria(i) = 'AWD' THEN
4891: DELETE FROM psp_selected_persons_t
4892: WHERE request_id = p_request_id
4893: AND assignment_id NOT IN (SELECT psl.assignment_id
4894: FROM psp_summary_lines psl,
4895: psp_report_template_details_h prtd ,

Line 4932: DELETE FROM psp_selected_persons_t

4928: AND palh.adjustment_batch_name IS NULL
4929: AND palh.reversal_entry_flag IS NULL
4930: AND NVL(palh.original_line_flag, 'N') ='N')));
4931: ELSIF template_sel_criteria.array_sel_criteria(i) = 'ATY' THEN
4932: DELETE FROM psp_selected_persons_t
4933: WHERE request_id = p_request_id
4934: AND assignment_id NOT IN (SELECT NVL(psl.assignment_id,0)
4935: FROM psp_summary_lines psl,
4936: psp_report_template_details_h prtd,

Line 4974: DELETE FROM psp_selected_persons_t

4970: AND palh.adjustment_batch_name IS NULL
4971: AND palh.reversal_entry_flag IS NULL
4972: AND NVL(palh.original_line_flag, 'N') ='N')));
4973: ELSIF template_sel_criteria.array_sel_criteria(i) ='PRT' THEN
4974: DELETE FROM psp_selected_persons_t
4975: WHERE request_id = p_request_id
4976: AND assignment_id NOT IN (SELECT NVL(psl.assignment_id,0)
4977: FROM psp_summary_lines psl,
4978: psp_report_template_details_h prtd,

Line 5016: DELETE FROM psp_selected_persons_t

5012: AND palh.adjustment_batch_name IS NULL
5013: AND palh.reversal_entry_flag IS NULL
5014: AND NVL(palh.original_line_flag, 'N') ='N')));
5015: ELSIF template_sel_criteria.array_sel_criteria(i) ='PRJ' THEN
5016: DELETE FROM psp_selected_persons_t
5017: WHERE request_id = p_request_id
5018: AND assignment_id NOT IN (SELECT NVL(psl.assignment_id ,0)
5019: FROM psp_summary_lines psl,
5020: psp_report_template_details_h prtd ,

Line 5057: DELETE FROM psp_selected_persons_t pspt

5053: AND palh.adjustment_batch_name IS NULL
5054: AND palh.reversal_entry_flag IS NULL
5055: AND NVL(palh.original_line_flag, 'N') ='N')));
5056: ELSIF template_sel_criteria.array_sel_criteria(i) ='PAY' THEN
5057: DELETE FROM psp_selected_persons_t pspt
5058: WHERE request_id = p_request_id
5059: /* Bug 5087294 : Performance fix replacing not in with not exists */
5060: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5061: AND NOT EXISTS ( SELECT 1

Line 5075: DELETE FROM psp_selected_persons_t pspt

5071: AND paf.payroll_id = TO_NUMBER(prtd.criteria_value1)
5072: AND prtd.request_id = p_request_id
5073: AND paf.assignment_id = pspt.assignment_id );
5074: ELSIF template_sel_criteria.array_sel_criteria(i) ='LOC' THEN
5075: DELETE FROM psp_selected_persons_t pspt
5076: WHERE request_id = p_request_id
5077: /* Bug 5087294 : Performance fix replacing not in with not exists */
5078: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5079: AND NOT EXISTS ( SELECT 1

Line 5094: DELETE FROM psp_selected_persons_t pspt

5090: AND prtd.request_id = p_request_id
5091: AND paf.assignment_id = pspt.assignment_id);
5092: ELSIF template_sel_criteria.array_sel_criteria(i) ='ORG' THEN
5093: /* Bug 5087294 : Performance fix replacing not in with not exists */
5094: DELETE FROM psp_selected_persons_t pspt
5095: WHERE request_id = p_request_id
5096: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5097: AND NOT EXISTS ( SELECT 1
5098: FROM per_assignments_f paf ,

Line 5111: DELETE FROM psp_selected_persons_t pspt

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

Line 5132: DELETE FROM psp_selected_persons_t

5128: AND paf.effective_start_date <= p_effort_end
5129: AND paf.effective_end_date >= p_effort_start
5130: AND paf.assignment_id = pspt.assignment_id );
5131: ELSIF template_sel_criteria.array_sel_criteria(i) = 'AST' THEN
5132: DELETE FROM psp_selected_persons_t
5133: WHERE request_id = p_request_id
5134: AND assignment_id NOT IN (SELECT NVL(paf.assignment_id,0)
5135: FROM per_all_assignments_f paf,
5136: hr_assignment_sets has,

Line 5163: DELETE FROM psp_selected_persons_t pspt

5159: AND hasa.include_or_exclude ='E'
5160: AND paf.effective_start_date <= p_effort_end
5161: AND paf.effective_end_date >= p_effort_start));
5162: ELSIF template_sel_criteria.array_sel_criteria(i) = 'JOB' THEN
5163: DELETE FROM psp_selected_persons_t pspt
5164: WHERE request_id = p_request_id
5165: /* Bug 5087294 : Performance fix replacing not in with not exists */
5166: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5167: AND NOT EXISTS ( SELECT 1

Line 5181: DELETE FROM psp_selected_persons_t pspt

5177: AND paf.job_id = TO_NUMBER(prtd.criteria_value1)
5178: AND prtd.request_id = p_request_id
5179: AND paf.assignment_id = pspt.assignment_id);
5180: ELSIF template_sel_criteria.array_sel_criteria(i) ='POS' THEN
5181: DELETE FROM psp_selected_persons_t pspt
5182: WHERE request_id = p_request_id
5183: /* Bug 5087294 : Performance fix replacing not in with not exists */
5184: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5185: AND NOT EXISTS ( SELECT 1

Line 5199: DELETE FROM psp_selected_persons_t pspt

5195: AND paf.position_id = TO_NUMBER(prtd.criteria_value1)
5196: AND prtd.request_id = p_request_id
5197: AND paf.assignment_id = pspt.assignment_id);
5198: ELSIF template_sel_criteria.array_sel_criteria(i) ='ASS' THEN
5199: DELETE FROM psp_selected_persons_t pspt
5200: WHERE request_id = p_request_id
5201: /* Bug 5087294 : Performance fix replacing not in with not exists */
5202: -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5203: AND NOT EXISTS ( SELECT 1

Line 5232: g_exec_string := 'DELETE FROM psp_selected_persons_t sel

5228: END LOOP;
5229: CLOSE ppg_cursor;
5230:
5231: IF l_sql_string IS NOT NULL THEN
5232: g_exec_string := 'DELETE FROM psp_selected_persons_t sel
5233: WHERE request_id = :request_id
5234: AND NOT EXISTS (SELECT 1
5235: FROM per_assignments_f paf, pay_people_groups ppg
5236: WHERE paf.people_group_id= ppg.people_group_id

Line 5267: g_exec_string := 'DELETE FROM psp_selected_persons_t

5263: END LOOP;
5264: CLOSE gla_cursor;
5265:
5266: IF l_sql_string IS NOT NULL THEN
5267: g_exec_string := 'DELETE FROM psp_selected_persons_t
5268: WHERE assignment_id NOT IN (SELECT NVL(psl.assignment_id,0)
5269: FROM psp_summary_lines psl, psp_distribution_lines_history pdnh,
5270: psp_adjustment_lines_history palh,
5271: psp_pre_gen_dist_lines_history ppg, gl_code_combinations gcc

Line 5347: DELETE FROM psp_selected_persons_t

5343: FOR i IN 1..template_sel_criteria.array_sel_criteria.COUNT
5344: LOOP
5345: IF template_sel_criteria.array_inc_exc_flag(i) = 'E' THEN
5346: IF template_sel_criteria.array_sel_criteria(i) = 'PTY' THEN
5347: DELETE FROM psp_selected_persons_t
5348: WHERE request_id = p_request_id
5349: AND person_id IN (SELECT ppf.person_id
5350: FROM per_people_f ppf,
5351: psp_report_template_details_h prtd,

Line 5366: DELETE FROM psp_selected_persons_t

5362: AND include_exclude_flag='E'
5363: AND ppf.person_type_id = TO_NUMBER(prtd.criteria_value1)
5364: AND prtd.request_id = p_request_id);
5365: ELSIF template_sel_criteria.array_sel_criteria(i) = 'EMP' THEN
5366: DELETE FROM psp_selected_persons_t
5367: WHERE request_id = p_request_id
5368: AND person_id IN (SELECT DISTINCT ppf.person_id
5369: FROM per_all_people_f ppf, per_assignments_f paf
5370: WHERE ppf.person_id IN (SELECT TO_NUMBER(criteria_value1)

Line 5383: DELETE FROM psp_selected_persons_t

5379: AND paf.effective_end_date >= p_effort_start
5380: AND ppf.effective_start_date <= p_effort_end
5381: AND ppf.effective_end_date >= p_effort_start);
5382: ELSIF template_sel_criteria.array_sel_criteria(i) ='SUP' THEN
5383: DELETE FROM psp_selected_persons_t
5384: WHERE request_id = p_request_id
5385: AND assignment_id IN (SELECT NVL(assignment_id,0)
5386: FROM per_all_assignments_f paf,
5387: psp_report_template_details_h prtd

Line 5397: DELETE FROM psp_selected_persons_t

5393: AND paf.assignment_type = 'E'
5394: AND paf.effective_start_date <= p_effort_end
5395: AND paf.effective_end_date >= p_effort_start);
5396: ELSIF template_sel_criteria.array_sel_criteria(i) = 'AWD' THEN
5397: DELETE FROM psp_selected_persons_t
5398: WHERE request_id = p_request_id
5399: AND assignment_id IN (SELECT psl.assignment_id
5400: FROM psp_summary_lines psl,
5401: psp_report_template_details_h prtd,

Line 5435: DELETE FROM psp_selected_persons_t

5431: AND palh.adjustment_batch_name IS NULL
5432: AND palh.reversal_entry_flag IS NULL
5433: AND NVL(palh.original_line_flag, 'N') ='N')));
5434: ELSIF template_sel_criteria.array_sel_criteria(i) = 'ATY' THEN
5435: DELETE FROM psp_selected_persons_t
5436: WHERE request_id = p_request_id
5437: AND assignment_id IN (SELECT NVL(psl.assignment_id,0)
5438: FROM psp_summary_lines psl,
5439: psp_report_template_details_h prtd,

Line 5474: DELETE FROM psp_selected_persons_t

5470: AND palh.adjustment_batch_name IS NULL
5471: AND palh.reversal_entry_flag IS NULL
5472: AND NVL(palh.original_line_flag, 'N') ='N')));
5473: ELSIF template_sel_criteria.array_sel_criteria(i) ='PRT' THEN
5474: DELETE FROM psp_selected_persons_t
5475: WHERE request_id = p_request_id
5476: AND assignment_id IN (SELECT NVL(psl.assignment_id,0)
5477: FROM psp_summary_lines psl,
5478: psp_report_template_details_h prtd,

Line 5513: DELETE FROM psp_selected_persons_t

5509: AND palh.adjustment_batch_name IS NULL
5510: AND palh.reversal_entry_flag IS NULL
5511: AND NVL(palh.original_line_flag, 'N') ='N')));
5512: ELSIF template_sel_criteria.array_sel_criteria(i) ='PRJ' THEN
5513: DELETE FROM psp_selected_persons_t
5514: WHERE request_id = p_request_id
5515: AND assignment_id IN (SELECT NVL(psl.assignment_id,0)
5516: FROM psp_summary_lines psl,
5517: psp_report_template_details_h prtd,

Line 5551: DELETE FROM psp_selected_persons_t

5547: AND palh.adjustment_batch_name IS NULL
5548: AND palh.reversal_entry_flag IS NULL
5549: AND NVL(palh.original_line_flag, 'N') ='N')));
5550: ELSIF template_sel_criteria.array_sel_criteria(i) ='PAY' THEN
5551: DELETE FROM psp_selected_persons_t
5552: WHERE request_id = p_request_id
5553: AND assignment_id IN (SELECT assignment_id
5554: FROM per_assignments_f paf,
5555: psp_report_template_details_h prtd

Line 5565: DELETE FROM psp_selected_persons_t

5561: effective_end_date >= p_effort_start
5562: AND paf.payroll_id = TO_NUMBER(prtd.criteria_value1)
5563: AND prtd.request_id = p_request_id);
5564: ELSIF template_sel_criteria.array_sel_criteria(i) ='LOC' THEN
5565: DELETE FROM psp_selected_persons_t
5566: WHERE request_id = p_request_id
5567: AND assignment_id IN (SELECT assignment_id
5568: FROM per_assignments_f paf,
5569: psp_report_template_details_h prtd

Line 5579: DELETE FROM psp_selected_persons_t

5575: effective_end_date >= p_effort_start
5576: AND paf.location_id = TO_NUMBER(prtd.criteria_value1)
5577: AND prtd.request_id = p_request_id);
5578: ELSIF template_sel_criteria.array_sel_criteria(i) ='ORG' THEN
5579: DELETE FROM psp_selected_persons_t
5580: WHERE request_id = p_request_id
5581: AND assignment_id IN (SELECT assignment_id
5582: FROM per_assignments_f paf,
5583: psp_report_template_details_h prtd

Line 5593: DELETE FROM psp_selected_persons_t

5589: effective_end_date >= p_effort_start
5590: AND paf.organization_id = TO_NUMBER(prtd.criteria_value1)
5591: AND prtd.request_id = p_request_id);
5592: ELSIF template_sel_criteria.array_sel_criteria(i) = 'JOB' THEN
5593: DELETE FROM psp_selected_persons_t
5594: WHERE request_id = p_request_id
5595: AND assignment_id IN (SELECT assignment_id
5596: FROM per_assignments_f paf,
5597: psp_report_template_details_h prtd

Line 5607: DELETE FROM psp_selected_persons_t

5603: effective_end_date >= p_effort_start
5604: AND paf.job_id = TO_NUMBER(prtd.criteria_value1)
5605: AND prtd.request_id = p_request_id);
5606: ELSIF template_sel_criteria.array_sel_criteria(i) ='POS' THEN
5607: DELETE FROM psp_selected_persons_t
5608: WHERE request_id = p_request_id
5609: AND assignment_id IN (SELECT assignment_id
5610: FROM per_assignments_f paf,
5611: psp_report_template_details_h prtd

Line 5621: DELETE FROM psp_selected_persons_t

5617: effective_end_date >= p_effort_start
5618: AND paf.position_id = TO_NUMBER(prtd.criteria_value1)
5619: AND prtd.request_id = p_request_id);
5620: ELSIF template_sel_criteria.array_sel_criteria(i) ='ASS' THEN
5621: DELETE FROM psp_selected_persons_t
5622: WHERE request_id = p_request_id
5623: AND assignment_id IN (SELECT assignment_id
5624: FROM per_assignments_f paf,
5625: psp_report_template_details_h prtd

Line 5635: DELETE FROM psp_selected_persons_t

5631: AND prtd.request_id = p_request_id
5632: AND effective_start_date <= p_effort_end
5633: AND effective_end_date >= p_effort_start);
5634: ELSIF template_sel_criteria.array_sel_criteria(i)='CST' THEN
5635: DELETE FROM psp_selected_persons_t
5636: WHERE request_id = p_request_id
5637: AND assignment_id IN (SELECT NVL(assignment_id,0)
5638: FROM per_assignments_f paf,
5639: pay_payrolls_f ppf

Line 5653: DELETE FROM psp_selected_persons_t

5649: AND ppf.effective_end_date >= p_effort_start
5650: AND paf.effective_start_date <= p_effort_end
5651: AND paf.effective_end_date >= p_effort_start);
5652: ELSIF template_sel_criteria.array_sel_criteria(i) = 'AST' THEN
5653: DELETE FROM psp_selected_persons_t
5654: WHERE request_id = p_request_id
5655: AND assignment_id IN (SELECT NVL(paf.assignment_id,0)
5656: FROM per_all_assignments_f paf, hr_assignment_sets has,
5657: hr_assignment_set_amendments hasa

Line 5699: g_exec_string := 'DELETE FROM psp_selected_persons_t

5695: END LOOP;
5696: CLOSE ppg_cursor;
5697:
5698: IF l_sql_string IS NOT NULL THEN
5699: g_exec_string := 'DELETE FROM psp_selected_persons_t
5700: WHERE assignment_id IN (SELECT assignment_id
5701: FROM per_assignments_f paf,
5702: pay_people_groups ppg
5703: WHERE paf.people_group_id= ppg.people_group_id

Line 5734: g_exec_string := 'DELETE FROM psp_selected_persons_t

5730: END LOOP;
5731: CLOSE gla_cursor;
5732:
5733: IF l_sql_string IS NOT NULL THEN
5734: g_exec_string := 'DELETE FROM psp_selected_persons_t
5735: WHERE assignment_id IN (SELECT NVL(psl.assignment_id,0)
5736: FROM psp_summary_lines psl,
5737: psp_distribution_lines_history pdnh,
5738: psp_adjustment_lines_history palh,