[Home] [Help]
519:
520: --- replaced non-performant insert with this for 4429787
521: insert into psp_selection_cardinality_gt(lookup_code, total_count)(
522: SELECT 'AWD', COUNT(DISTINCT psl.person_id)
523: FROM psp_summary_lines psl,
524: psp_report_template_details_h prtd ,
525: per_time_periods ptp
526: WHERE
527: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
557:
558:
559: INSERT INTO psp_selection_cardinality_gt(lookup_code, total_count)(
560: SELECT 'ATY', COUNT(DISTINCT psl.person_id)
561: FROM psp_summary_lines psl,
562: psp_report_template_details_h prtd ,
563: gms_awards_all gaa,
564: per_time_periods ptp
565: WHERE psl.award_id = gaa.award_id AND
594: ELSIF template_rec.array_sel_criteria(i)='PRT' THEN
595: --- replaced non-performant insert with this for 4429787
596: INSERT INTO psp_selection_cardinality_gt(lookup_code, total_count)(
597: SELECT 'PRT', COUNT(DISTINCT psl.person_id)
598: FROM psp_summary_lines psl,
599: psp_report_template_details_h prtd ,
600: pa_projects_all ppa ,
601: per_time_periods ptp
602: WHERE psl.project_id = ppa.project_id AND
631: ELSIF template_rec.array_sel_criteria(i)='PRJ' THEN
632: --- replaced non-performant insert with this for 4429787
633: insert into psp_selection_cardinality_gt(lookup_code, total_count)(
634: SELECT 'PRJ', COUNT(DISTINCT psl.person_id)
635: FROM psp_summary_lines psl,
636: psp_report_template_details_h prtd ,
637: per_time_periods ptp
638: WHERE
639: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
961: -- fnd_file.put_line(fnd_file.log,' before isnerting g_exec_string in GL criteria ');
962: if l_sql_string is not null then
963: g_exec_string := 'insert into psp_selection_cardinality_gt(lookup_code, total_count)
964: ( select '|| '''' || 'GLA' || ''''|| ' , count( distinct psl.person_id)
965: from psp_summary_lines psl, psp_distribution_lines_history pdnh, psp_pre_gen_dist_lines_history ppg,
966: psp_adjustment_lines_history palh
967: , gl_code_combinations gcc
968: where
969: psl.business_group_id = '|| p_business_group_id || ' and
1079: AND reversal_entry_flag IS NULL
1080: AND rownum=1 )
1081: OR EXISTS (SELECT null
1082: FROM psp_distribution_lines_history pdlh
1083: , psp_summary_lines psl
1084: WHERE pdlh.summary_line_id = psl.summary_line_id
1085: AND distribution_date between p_effort_start and p_effort_end
1086: AND psl.person_id = paf.person_id
1087: AND psl.assignment_id = paf.assignment_id
1116: AND reversal_entry_flag IS NULL
1117: AND rownum=1 )
1118: OR EXISTS (SELECT null
1119: FROM psp_distribution_lines_history pdlh
1120: , psp_summary_lines psl
1121: WHERE pdlh.summary_line_id = psl.summary_line_id
1122: AND distribution_date between p_effort_start and p_effort_end
1123: AND psl.person_id = paf.person_id
1124: AND psl.assignment_id = paf.assignment_id
1153: AND reversal_entry_flag IS NULL
1154: AND rownum=1 )
1155: OR EXISTS (SELECT null
1156: FROM psp_distribution_lines_history pdlh
1157: , psp_summary_lines psl
1158: WHERE pdlh.summary_line_id = psl.summary_line_id
1159: AND distribution_date between p_effort_start and p_effort_end
1160: AND psl.person_id = paf.person_id
1161: AND psl.assignment_id = paf.assignment_id
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,
1174: per_assignments_f paf
1175: WHERE
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 ,
1216: per_time_periods ptp,
1217: per_assignments_f paf
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 ,
1259: per_time_periods ptp,
1260: per_assignments_f paf
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,
1307: per_assignments_f paf
1308: WHERE
1366: AND reversal_entry_flag IS NULL
1367: AND rownum=1 )
1368: OR EXISTS (SELECT null
1369: FROM psp_distribution_lines_history pdlh
1370: , psp_summary_lines psl
1371: WHERE pdlh.summary_line_id = psl.summary_line_id
1372: AND distribution_date between p_effort_start and p_effort_end
1373: AND psl.person_id = paf.person_id
1374: AND psl.assignment_id = paf.assignment_id
1403: AND reversal_entry_flag IS NULL
1404: AND rownum=1 )
1405: OR EXISTS (SELECT null
1406: FROM psp_distribution_lines_history pdlh
1407: , psp_summary_lines psl
1408: WHERE pdlh.summary_line_id = psl.summary_line_id
1409: AND distribution_date between p_effort_start and p_effort_end
1410: AND psl.person_id = paf.person_id
1411: AND psl.assignment_id = paf.assignment_id
1440: AND reversal_entry_flag IS NULL
1441: AND rownum=1 )
1442: OR EXISTS (SELECT null
1443: FROM psp_distribution_lines_history pdlh
1444: , psp_summary_lines psl
1445: WHERE pdlh.summary_line_id = psl.summary_line_id
1446: AND distribution_date between p_effort_start and p_effort_end
1447: AND psl.person_id = paf.person_id
1448: AND psl.assignment_id = paf.assignment_id
1481: AND reversal_entry_flag IS NULL
1482: AND rownum=1 )
1483: OR EXISTS (SELECT null
1484: FROM psp_distribution_lines_history pdlh
1485: , psp_summary_lines psl
1486: WHERE pdlh.summary_line_id = psl.summary_line_id
1487: AND distribution_date between p_effort_start and p_effort_end
1488: AND psl.person_id = paf.person_id
1489: AND psl.assignment_id = paf.assignment_id
1533: AND reversal_entry_flag IS NULL
1534: AND rownum=1 )
1535: OR EXISTS (SELECT null
1536: FROM psp_distribution_lines_history pdlh
1537: , psp_summary_lines psl
1538: WHERE pdlh.summary_line_id = psl.summary_line_id
1539: AND distribution_date between p_effort_start and p_effort_end
1540: AND psl.person_id = paf.person_id
1541: AND psl.assignment_id = paf.assignment_id
1571: AND reversal_entry_flag IS NULL
1572: AND rownum=1 )
1573: OR EXISTS (SELECT null
1574: FROM psp_distribution_lines_history pdlh
1575: , psp_summary_lines psl
1576: WHERE pdlh.summary_line_id = psl.summary_line_id
1577: AND distribution_date between p_effort_start and p_effort_end
1578: AND psl.person_id = paf.person_id
1579: AND psl.assignment_id = paf.assignment_id
1608: AND reversal_entry_flag IS NULL
1609: AND rownum=1 )
1610: OR EXISTS (SELECT null
1611: FROM psp_distribution_lines_history pdlh
1612: , psp_summary_lines psl
1613: WHERE pdlh.summary_line_id = psl.summary_line_id
1614: AND distribution_date between p_effort_start and p_effort_end
1615: AND psl.person_id = paf.person_id
1616: AND psl.assignment_id = paf.assignment_id
1646: AND reversal_entry_flag IS NULL
1647: AND rownum=1 )
1648: OR EXISTS (SELECT null
1649: FROM psp_distribution_lines_history pdlh
1650: , psp_summary_lines psl
1651: WHERE pdlh.summary_line_id = psl.summary_line_id
1652: AND distribution_date between p_effort_start and p_effort_end
1653: AND psl.person_id = paf.person_id
1654: AND psl.assignment_id = paf.assignment_id
1713: AND reversal_entry_flag IS NULL
1714: AND rownum=1 )
1715: OR EXISTS (SELECT null
1716: FROM psp_distribution_lines_history pdlh
1717: , psp_summary_lines psl
1718: WHERE pdlh.summary_line_id = psl.summary_line_id
1719: AND distribution_date between :p_effort_start and :p_effort_end
1720: AND psl.person_id = paf.person_id
1721: AND psl.assignment_id = paf.assignment_id
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
1769: where
1770: psl.business_group_id = '|| p_business_group_id || ' and
1907: AND reversal_entry_flag IS NULL
1908: AND rownum=1 )
1909: OR EXISTS (SELECT null
1910: FROM psp_distribution_lines_history pdlh
1911: , psp_summary_lines psl
1912: WHERE pdlh.summary_line_id = psl.summary_line_id
1913: AND distribution_date between p_effort_start and p_effort_end
1914: AND psl.person_id = paf.person_id
1915: AND psl.assignment_id = paf.assignment_id
1948: AND reversal_entry_flag IS NULL
1949: AND rownum=1 )
1950: OR EXISTS (SELECT null
1951: FROM psp_distribution_lines_history pdlh
1952: , psp_summary_lines psl
1953: WHERE pdlh.summary_line_id = psl.summary_line_id
1954: AND distribution_date between p_effort_start and p_effort_end
1955: AND psl.person_id = paf.person_id
1956: AND psl.assignment_id = paf.assignment_id
1986: AND reversal_entry_flag IS NULL
1987: AND rownum=1 )
1988: OR EXISTS (SELECT null
1989: FROM psp_distribution_lines_history pdlh
1990: , psp_summary_lines psl
1991: WHERE pdlh.summary_line_id = psl.summary_line_id
1992: AND distribution_date between p_effort_start and p_effort_end
1993: AND psl.person_id = paf.person_id
1994: AND psl.assignment_id = paf.assignment_id
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
2008: WHERE
2009: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
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 ,
2046: per_time_periods ptp
2047: WHERE psl.award_id = gaa.award_id AND
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 ,
2084: per_time_periods ptp
2085: WHERE psl.project_id = ppa.project_id AND
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
2122: WHERE
2123: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
2176: AND reversal_entry_flag IS NULL
2177: AND rownum=1 )
2178: OR EXISTS (SELECT null
2179: FROM psp_distribution_lines_history pdlh
2180: , psp_summary_lines psl
2181: WHERE pdlh.summary_line_id = psl.summary_line_id
2182: AND distribution_date between p_effort_start and p_effort_end
2183: AND psl.person_id = paf.person_id
2184: AND psl.assignment_id = paf.assignment_id
2215: AND reversal_entry_flag IS NULL
2216: AND rownum=1 )
2217: OR EXISTS (SELECT null
2218: FROM psp_distribution_lines_history pdlh
2219: , psp_summary_lines psl
2220: WHERE pdlh.summary_line_id = psl.summary_line_id
2221: AND distribution_date between p_effort_start and p_effort_end
2222: AND psl.person_id = paf.person_id
2223: AND psl.assignment_id = paf.assignment_id
2251: AND reversal_entry_flag IS NULL
2252: AND rownum=1 )
2253: OR EXISTS (SELECT null
2254: FROM psp_distribution_lines_history pdlh
2255: , psp_summary_lines psl
2256: WHERE pdlh.summary_line_id = psl.summary_line_id
2257: AND distribution_date between p_effort_start and p_effort_end
2258: AND psl.person_id = paf.person_id
2259: AND psl.assignment_id = paf.assignment_id
2292: AND reversal_entry_flag IS NULL
2293: AND rownum=1 )
2294: OR EXISTS (SELECT null
2295: FROM psp_distribution_lines_history pdlh
2296: , psp_summary_lines psl
2297: WHERE pdlh.summary_line_id = psl.summary_line_id
2298: AND distribution_date between p_effort_start and p_effort_end
2299: AND psl.person_id = paf.person_id
2300: AND psl.assignment_id = paf.assignment_id
2344: AND reversal_entry_flag IS NULL
2345: AND rownum=1 )
2346: OR EXISTS (SELECT null
2347: FROM psp_distribution_lines_history pdlh
2348: , psp_summary_lines psl
2349: WHERE pdlh.summary_line_id = psl.summary_line_id
2350: AND distribution_date between p_effort_start and p_effort_end
2351: AND psl.person_id = paf.person_id
2352: AND psl.assignment_id = paf.assignment_id
2382: AND reversal_entry_flag IS NULL
2383: AND rownum=1 )
2384: OR EXISTS (SELECT null
2385: FROM psp_distribution_lines_history pdlh
2386: , psp_summary_lines psl
2387: WHERE pdlh.summary_line_id = psl.summary_line_id
2388: AND distribution_date between p_effort_start and p_effort_end
2389: AND psl.person_id = paf.person_id
2390: AND psl.assignment_id = paf.assignment_id
2420: AND reversal_entry_flag IS NULL
2421: AND rownum=1 )
2422: OR EXISTS (SELECT null
2423: FROM psp_distribution_lines_history pdlh
2424: , psp_summary_lines psl
2425: WHERE pdlh.summary_line_id = psl.summary_line_id
2426: AND distribution_date between p_effort_start and p_effort_end
2427: AND psl.person_id = paf.person_id
2428: AND psl.assignment_id = paf.assignment_id
2458: AND reversal_entry_flag IS NULL
2459: AND rownum=1 )
2460: OR EXISTS (SELECT null
2461: FROM psp_distribution_lines_history pdlh
2462: , psp_summary_lines psl
2463: WHERE pdlh.summary_line_id = psl.summary_line_id
2464: AND distribution_date between p_effort_start and p_effort_end
2465: AND psl.person_id = paf.person_id
2466: AND psl.assignment_id = paf.assignment_id
2529: AND reversal_entry_flag IS NULL
2530: AND rownum=1 )
2531: OR EXISTS (SELECT null
2532: FROM psp_distribution_lines_history pdlh
2533: , psp_summary_lines psl
2534: WHERE pdlh.summary_line_id = psl.summary_line_id
2535: AND distribution_date between :p_effort_start and :p_effort_end
2536: AND psl.person_id = paf.person_id
2537: AND psl.assignment_id = paf.assignment_id
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
2582: where gcc.code_combination_id= psl.gl_code_combination_id and
2696: AND reversal_entry_flag IS NULL
2697: AND rownum=1 )
2698: OR EXISTS (SELECT null
2699: FROM psp_distribution_lines_history pdlh
2700: , psp_summary_lines psl
2701: WHERE pdlh.summary_line_id = psl.summary_line_id
2702: AND distribution_date between p_effort_start and p_effort_end
2703: AND psl.person_id = paf.person_id
2704: AND psl.assignment_id = paf.assignment_id
2735: AND reversal_entry_flag IS NULL
2736: AND rownum=1 )
2737: OR EXISTS (SELECT null
2738: FROM psp_distribution_lines_history pdlh
2739: , psp_summary_lines psl
2740: WHERE pdlh.summary_line_id = psl.summary_line_id
2741: AND distribution_date between p_effort_start and p_effort_end
2742: AND psl.person_id = paf.person_id
2743: AND psl.assignment_id = paf.assignment_id
2770: AND reversal_entry_flag IS NULL
2771: AND rownum=1 )
2772: OR EXISTS (SELECT null
2773: FROM psp_distribution_lines_history pdlh
2774: , psp_summary_lines psl
2775: WHERE pdlh.summary_line_id = psl.summary_line_id
2776: AND distribution_date between p_effort_start and p_effort_end
2777: AND psl.person_id = paf.person_id
2778: AND psl.assignment_id = paf.assignment_id
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
2791: WHERE
2792: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
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 ,
2828: per_time_periods ptp
2829: WHERE psl.award_id = gaa.award_id AND
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 ,
2866: per_time_periods ptp
2867: WHERE psl.project_id = ppa.project_id AND
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
2904: WHERE
2905: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
2956: AND reversal_entry_flag IS NULL
2957: AND rownum=1 )
2958: OR EXISTS (SELECT null
2959: FROM psp_distribution_lines_history pdlh
2960: , psp_summary_lines psl
2961: WHERE pdlh.summary_line_id = psl.summary_line_id
2962: AND distribution_date between p_effort_start and p_effort_end
2963: AND psl.person_id = paf.person_id
2964: AND psl.assignment_id = paf.assignment_id
2991: AND reversal_entry_flag IS NULL
2992: AND rownum=1 )
2993: OR EXISTS (SELECT null
2994: FROM psp_distribution_lines_history pdlh
2995: , psp_summary_lines psl
2996: WHERE pdlh.summary_line_id = psl.summary_line_id
2997: AND distribution_date between p_effort_start and p_effort_end
2998: AND psl.person_id = paf.person_id
2999: AND psl.assignment_id = paf.assignment_id
3026: AND reversal_entry_flag IS NULL
3027: AND rownum=1 )
3028: OR EXISTS (SELECT null
3029: FROM psp_distribution_lines_history pdlh
3030: , psp_summary_lines psl
3031: WHERE pdlh.summary_line_id = psl.summary_line_id
3032: AND distribution_date between p_effort_start and p_effort_end
3033: AND psl.person_id = paf.person_id
3034: AND psl.assignment_id = paf.assignment_id
3065: AND reversal_entry_flag IS NULL
3066: AND rownum=1 )
3067: OR EXISTS (SELECT null
3068: FROM psp_distribution_lines_history pdlh
3069: , psp_summary_lines psl
3070: WHERE pdlh.summary_line_id = psl.summary_line_id
3071: AND distribution_date between p_effort_start and p_effort_end
3072: AND psl.person_id = paf.person_id
3073: AND psl.assignment_id = paf.assignment_id
3100: AND reversal_entry_flag IS NULL
3101: AND rownum=1 )
3102: OR EXISTS (SELECT null
3103: FROM psp_distribution_lines_history pdlh
3104: , psp_summary_lines psl
3105: WHERE pdlh.summary_line_id = psl.summary_line_id
3106: AND distribution_date between p_effort_start and p_effort_end
3107: AND psl.person_id = paf.person_id
3108: AND psl.assignment_id = paf.assignment_id
3134: AND reversal_entry_flag IS NULL
3135: AND rownum=1 )
3136: OR EXISTS (SELECT null
3137: FROM psp_distribution_lines_history pdlh
3138: , psp_summary_lines psl
3139: WHERE pdlh.summary_line_id = psl.summary_line_id
3140: AND distribution_date between p_effort_start and p_effort_end
3141: AND psl.person_id = paf.person_id
3142: AND psl.assignment_id = paf.assignment_id
3173: AND reversal_entry_flag IS NULL
3174: AND rownum=1 )
3175: OR EXISTS (SELECT null
3176: FROM psp_distribution_lines_history pdlh
3177: , psp_summary_lines psl
3178: WHERE pdlh.summary_line_id = psl.summary_line_id
3179: AND distribution_date between p_effort_start and p_effort_end
3180: AND psl.person_id = paf.person_id
3181: AND psl.assignment_id = paf.assignment_id
3226: AND reversal_entry_flag IS NULL
3227: AND rownum=1 )
3228: OR EXISTS (SELECT null
3229: FROM psp_distribution_lines_history pdlh
3230: , psp_summary_lines psl
3231: WHERE pdlh.summary_line_id = psl.summary_line_id
3232: AND distribution_date between p_effort_start and p_effort_end
3233: AND psl.person_id = paf.person_id
3234: AND psl.assignment_id = paf.assignment_id
3290: AND reversal_entry_flag IS NULL
3291: AND rownum=1 )
3292: OR EXISTS (SELECT null
3293: FROM psp_distribution_lines_history pdlh
3294: , psp_summary_lines psl
3295: WHERE pdlh.summary_line_id = psl.summary_line_id
3296: AND distribution_date between :p_effort_start and :p_effort_end
3297: AND psl.person_id = paf.person_id
3298: AND psl.assignment_id = paf.assignment_id
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
3343: where gcc.code_combination_id= psl.gl_code_combination_id and
3673: ELSIF template_rec.array_sel_criteria(i) ='AWD' THEN
3674: INSERT INTO psp_selection_cardinality_gt
3675: (lookup_code, total_count)
3676: SELECT 'AWD', COUNT(DISTINCT psl.assignment_id)
3677: FROM psp_summary_lines psl,
3678: psp_report_template_details_h prtd ,
3679: per_time_periods ptp
3680: WHERE
3681: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
3711: ELSIF template_rec.array_sel_criteria(i)='ATY' THEN
3712: INSERT INTO psp_selection_cardinality_gt
3713: (lookup_code, total_count)
3714: SELECT 'ATY', COUNT(DISTINCT psl.assignment_id)
3715: FROM psp_summary_lines psl,
3716: psp_report_template_details_h prtd ,
3717: gms_awards_all gaa,
3718: per_time_periods ptp
3719: WHERE psl.award_id = gaa.award_id
3750: ELSIF template_rec.array_sel_criteria(i)='PRT' THEN
3751: INSERT INTO psp_selection_cardinality_gt
3752: (lookup_code, total_count)
3753: SELECT 'PRT', COUNT(DISTINCT psl.assignment_id)
3754: FROM psp_summary_lines psl,
3755: psp_report_template_details_h prtd ,
3756: pa_projects_all ppa ,
3757: per_time_periods ptp
3758: WHERE psl.project_id = ppa.project_id
3789: ELSIF template_rec.array_sel_criteria(i)='PRJ' THEN
3790: INSERT INTO psp_selection_cardinality_gt
3791: (lookup_code, total_count)
3792: SELECT 'PRJ', COUNT(DISTINCT psl.assignment_id)
3793: FROM psp_summary_lines psl,
3794: psp_report_template_details_h prtd ,
3795: per_time_periods ptp
3796: WHERE
3797: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
4043:
4044: IF l_sql_string IS NOT NULL THEN
4045: g_exec_string := 'INSERT INTO psp_selection_cardinality_gt(lookup_code, total_count)
4046: SELECT '|| '''' || 'GLA' || ''''|| ' , COUNT( DISTINCT psl.assignment_id)
4047: FROM psp_summary_lines psl,
4048: psp_distribution_lines_history pdnh,
4049: psp_pre_gen_dist_lines_history ppg,
4050: psp_adjustment_lines_history palh,
4051: gl_code_combinations gcc
4155: AND reversal_entry_flag IS NULL
4156: AND rownum=1 )
4157: OR EXISTS (SELECT null
4158: FROM psp_distribution_lines_history pdlh
4159: , psp_summary_lines psl
4160: WHERE pdlh.summary_line_id = psl.summary_line_id
4161: AND distribution_date between p_effort_start and p_effort_end
4162: AND psl.person_id = paf.person_id
4163: AND psl.assignment_id = paf.assignment_id
4192: AND reversal_entry_flag IS NULL
4193: AND rownum=1 )
4194: OR EXISTS (SELECT null
4195: FROM psp_distribution_lines_history pdlh
4196: , psp_summary_lines psl
4197: WHERE pdlh.summary_line_id = psl.summary_line_id
4198: AND distribution_date between p_effort_start and p_effort_end
4199: AND psl.person_id = paf.person_id
4200: AND psl.assignment_id = paf.assignment_id
4225: AND reversal_entry_flag IS NULL
4226: AND rownum=1 )
4227: OR EXISTS (SELECT null
4228: FROM psp_distribution_lines_history pdlh
4229: , psp_summary_lines psl
4230: WHERE pdlh.summary_line_id = psl.summary_line_id
4231: AND distribution_date between p_effort_start and p_effort_end
4232: AND psl.person_id = paf.person_id
4233: AND psl.assignment_id = paf.assignment_id
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
4243: WHERE
4244: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
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,
4279: per_time_periods ptp
4280: WHERE psl.award_id = gaa.award_id
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,
4317: per_time_periods ptp
4318: WHERE psl.project_id = ppa.project_id
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
4354: WHERE
4355: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
4405: AND reversal_entry_flag IS NULL
4406: AND rownum=1 )
4407: OR EXISTS (SELECT null
4408: FROM psp_distribution_lines_history pdlh
4409: , psp_summary_lines psl
4410: WHERE pdlh.summary_line_id = psl.summary_line_id
4411: AND distribution_date between p_effort_start and p_effort_end
4412: AND psl.person_id = paf.person_id
4413: AND psl.assignment_id = paf.assignment_id
4438: AND reversal_entry_flag IS NULL
4439: AND rownum=1 )
4440: OR EXISTS (SELECT null
4441: FROM psp_distribution_lines_history pdlh
4442: , psp_summary_lines psl
4443: WHERE pdlh.summary_line_id = psl.summary_line_id
4444: AND distribution_date between p_effort_start and p_effort_end
4445: AND psl.person_id = paf.person_id
4446: AND psl.assignment_id = paf.assignment_id
4470: AND reversal_entry_flag IS NULL
4471: AND rownum=1 )
4472: OR EXISTS (SELECT null
4473: FROM psp_distribution_lines_history pdlh
4474: , psp_summary_lines psl
4475: WHERE pdlh.summary_line_id = psl.summary_line_id
4476: AND distribution_date between p_effort_start and p_effort_end
4477: AND psl.person_id = paf.person_id
4478: AND psl.assignment_id = paf.assignment_id
4506: AND reversal_entry_flag IS NULL
4507: AND rownum=1 )
4508: OR EXISTS (SELECT null
4509: FROM psp_distribution_lines_history pdlh
4510: , psp_summary_lines psl
4511: WHERE pdlh.summary_line_id = psl.summary_line_id
4512: AND distribution_date between p_effort_start and p_effort_end
4513: AND psl.person_id = paf.person_id
4514: AND psl.assignment_id = paf.assignment_id
4554: AND reversal_entry_flag IS NULL
4555: AND rownum=1 )
4556: OR EXISTS (SELECT null
4557: FROM psp_distribution_lines_history pdlh
4558: , psp_summary_lines psl
4559: WHERE pdlh.summary_line_id = psl.summary_line_id
4560: AND distribution_date between p_effort_start and p_effort_end
4561: AND psl.person_id = paf.person_id
4562: AND psl.assignment_id = paf.assignment_id
4587: AND reversal_entry_flag IS NULL
4588: AND rownum=1 )
4589: OR EXISTS (SELECT null
4590: FROM psp_distribution_lines_history pdlh
4591: , psp_summary_lines psl
4592: WHERE pdlh.summary_line_id = psl.summary_line_id
4593: AND distribution_date between p_effort_start and p_effort_end
4594: AND psl.person_id = paf.person_id
4595: AND psl.assignment_id = paf.assignment_id
4619: AND reversal_entry_flag IS NULL
4620: AND rownum=1 )
4621: OR EXISTS (SELECT null
4622: FROM psp_distribution_lines_history pdlh
4623: , psp_summary_lines psl
4624: WHERE pdlh.summary_line_id = psl.summary_line_id
4625: AND distribution_date between p_effort_start and p_effort_end
4626: AND psl.person_id = paf.person_id
4627: AND psl.assignment_id = paf.assignment_id
4651: AND reversal_entry_flag IS NULL
4652: AND rownum=1 )
4653: OR EXISTS (SELECT null
4654: FROM psp_distribution_lines_history pdlh
4655: , psp_summary_lines psl
4656: WHERE pdlh.summary_line_id = psl.summary_line_id
4657: AND distribution_date between p_effort_start and p_effort_end
4658: AND psl.person_id = paf.person_id
4659: AND psl.assignment_id = paf.assignment_id
4699: AND reversal_entry_flag IS NULL
4700: AND rownum=1 )
4701: OR EXISTS (SELECT null
4702: FROM psp_distribution_lines_history pdlh
4703: , psp_summary_lines psl
4704: WHERE pdlh.summary_line_id = psl.summary_line_id
4705: AND distribution_date between :p_effort_start and :p_effort_end
4706: AND psl.person_id = paf.person_id
4707: AND psl.assignment_id = paf.assignment_id
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,
4739: psp_pre_gen_dist_lines_history ppg,
4740: gl_code_combinations gcc
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 ,
4896: per_time_periods ptp
4897: WHERE
4898: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
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,
4937: gms_awards_all gaa,
4938: per_time_periods ptp
4939: WHERE psl.award_id = gaa.award_id
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,
4979: pa_projects_all ppa,
4980: per_time_periods ptp
4981: WHERE psl.project_id = ppa.project_id
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 ,
5021: per_time_periods ptp
5022: WHERE
5023: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
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
5272: WHERE gcc.code_combination_id= psl.gl_code_combination_id
5273: AND psl.business_group_id = '|| p_business_group_id || '
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,
5402: per_time_periods ptp
5403: WHERE
5404: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
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,
5440: gms_awards_all gaa,
5441: per_time_periods ptp
5442: WHERE psl.award_id = gaa.award_id
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,
5479: pa_projects_all ppa,
5480: per_time_periods ptp
5481: WHERE psl.project_id = ppa.project_id
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,
5518: per_time_periods ptp
5519: WHERE
5520: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
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,
5739: psp_pre_gen_dist_lines_history ppg,
5740: gl_code_combinations gcc