[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
1832: AND reversal_entry_flag IS NULL
1833: AND rownum=1 )
1834: OR EXISTS (SELECT null
1835: FROM psp_distribution_lines_history pdlh
1836: , psp_summary_lines psl
1837: WHERE pdlh.summary_line_id = psl.summary_line_id
1838: AND distribution_date between p_effort_start and p_effort_end
1839: AND psl.person_id = paf.person_id
1840: AND psl.assignment_id = paf.assignment_id
1940: AND reversal_entry_flag IS NULL
1941: AND rownum=1 )
1942: OR EXISTS (SELECT null
1943: FROM psp_distribution_lines_history pdlh
1944: , psp_summary_lines psl
1945: WHERE pdlh.summary_line_id = psl.summary_line_id
1946: AND distribution_date between p_effort_start and p_effort_end
1947: AND psl.person_id = paf.person_id
1948: AND psl.assignment_id = paf.assignment_id
1981: AND reversal_entry_flag IS NULL
1982: AND rownum=1 )
1983: OR EXISTS (SELECT null
1984: FROM psp_distribution_lines_history pdlh
1985: , psp_summary_lines psl
1986: WHERE pdlh.summary_line_id = psl.summary_line_id
1987: AND distribution_date between p_effort_start and p_effort_end
1988: AND psl.person_id = paf.person_id
1989: AND psl.assignment_id = paf.assignment_id
2019: AND reversal_entry_flag IS NULL
2020: AND rownum=1 )
2021: OR EXISTS (SELECT null
2022: FROM psp_distribution_lines_history pdlh
2023: , psp_summary_lines psl
2024: WHERE pdlh.summary_line_id = psl.summary_line_id
2025: AND distribution_date between p_effort_start and p_effort_end
2026: AND psl.person_id = paf.person_id
2027: AND psl.assignment_id = paf.assignment_id
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
2041: WHERE
2042: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
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 ,
2079: per_time_periods ptp
2080: WHERE psl.award_id = gaa.award_id AND
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 ,
2117: per_time_periods ptp
2118: WHERE psl.project_id = ppa.project_id AND
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
2155: WHERE
2156: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
2209: AND reversal_entry_flag IS NULL
2210: AND rownum=1 )
2211: OR EXISTS (SELECT null
2212: FROM psp_distribution_lines_history pdlh
2213: , psp_summary_lines psl
2214: WHERE pdlh.summary_line_id = psl.summary_line_id
2215: AND distribution_date between p_effort_start and p_effort_end
2216: AND psl.person_id = paf.person_id
2217: AND psl.assignment_id = paf.assignment_id
2248: AND reversal_entry_flag IS NULL
2249: AND rownum=1 )
2250: OR EXISTS (SELECT null
2251: FROM psp_distribution_lines_history pdlh
2252: , psp_summary_lines psl
2253: WHERE pdlh.summary_line_id = psl.summary_line_id
2254: AND distribution_date between p_effort_start and p_effort_end
2255: AND psl.person_id = paf.person_id
2256: AND psl.assignment_id = paf.assignment_id
2284: AND reversal_entry_flag IS NULL
2285: AND rownum=1 )
2286: OR EXISTS (SELECT null
2287: FROM psp_distribution_lines_history pdlh
2288: , psp_summary_lines psl
2289: WHERE pdlh.summary_line_id = psl.summary_line_id
2290: AND distribution_date between p_effort_start and p_effort_end
2291: AND psl.person_id = paf.person_id
2292: AND psl.assignment_id = paf.assignment_id
2325: AND reversal_entry_flag IS NULL
2326: AND rownum=1 )
2327: OR EXISTS (SELECT null
2328: FROM psp_distribution_lines_history pdlh
2329: , psp_summary_lines psl
2330: WHERE pdlh.summary_line_id = psl.summary_line_id
2331: AND distribution_date between p_effort_start and p_effort_end
2332: AND psl.person_id = paf.person_id
2333: AND psl.assignment_id = paf.assignment_id
2377: AND reversal_entry_flag IS NULL
2378: AND rownum=1 )
2379: OR EXISTS (SELECT null
2380: FROM psp_distribution_lines_history pdlh
2381: , psp_summary_lines psl
2382: WHERE pdlh.summary_line_id = psl.summary_line_id
2383: AND distribution_date between p_effort_start and p_effort_end
2384: AND psl.person_id = paf.person_id
2385: AND psl.assignment_id = paf.assignment_id
2415: AND reversal_entry_flag IS NULL
2416: AND rownum=1 )
2417: OR EXISTS (SELECT null
2418: FROM psp_distribution_lines_history pdlh
2419: , psp_summary_lines psl
2420: WHERE pdlh.summary_line_id = psl.summary_line_id
2421: AND distribution_date between p_effort_start and p_effort_end
2422: AND psl.person_id = paf.person_id
2423: AND psl.assignment_id = paf.assignment_id
2453: AND reversal_entry_flag IS NULL
2454: AND rownum=1 )
2455: OR EXISTS (SELECT null
2456: FROM psp_distribution_lines_history pdlh
2457: , psp_summary_lines psl
2458: WHERE pdlh.summary_line_id = psl.summary_line_id
2459: AND distribution_date between p_effort_start and p_effort_end
2460: AND psl.person_id = paf.person_id
2461: AND psl.assignment_id = paf.assignment_id
2491: AND reversal_entry_flag IS NULL
2492: AND rownum=1 )
2493: OR EXISTS (SELECT null
2494: FROM psp_distribution_lines_history pdlh
2495: , psp_summary_lines psl
2496: WHERE pdlh.summary_line_id = psl.summary_line_id
2497: AND distribution_date between p_effort_start and p_effort_end
2498: AND psl.person_id = paf.person_id
2499: AND psl.assignment_id = paf.assignment_id
2562: AND reversal_entry_flag IS NULL
2563: AND rownum=1 )
2564: OR EXISTS (SELECT null
2565: FROM psp_distribution_lines_history pdlh
2566: , psp_summary_lines psl
2567: WHERE pdlh.summary_line_id = psl.summary_line_id
2568: AND distribution_date between :p_effort_start and :p_effort_end
2569: AND psl.person_id = paf.person_id
2570: AND psl.assignment_id = paf.assignment_id
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
2615: where gcc.code_combination_id= psl.gl_code_combination_id and
2729: AND reversal_entry_flag IS NULL
2730: AND rownum=1 )
2731: OR EXISTS (SELECT null
2732: FROM psp_distribution_lines_history pdlh
2733: , psp_summary_lines psl
2734: WHERE pdlh.summary_line_id = psl.summary_line_id
2735: AND distribution_date between p_effort_start and p_effort_end
2736: AND psl.person_id = paf.person_id
2737: AND psl.assignment_id = paf.assignment_id
2768: AND reversal_entry_flag IS NULL
2769: AND rownum=1 )
2770: OR EXISTS (SELECT null
2771: FROM psp_distribution_lines_history pdlh
2772: , psp_summary_lines psl
2773: WHERE pdlh.summary_line_id = psl.summary_line_id
2774: AND distribution_date between p_effort_start and p_effort_end
2775: AND psl.person_id = paf.person_id
2776: AND psl.assignment_id = paf.assignment_id
2803: AND reversal_entry_flag IS NULL
2804: AND rownum=1 )
2805: OR EXISTS (SELECT null
2806: FROM psp_distribution_lines_history pdlh
2807: , psp_summary_lines psl
2808: WHERE pdlh.summary_line_id = psl.summary_line_id
2809: AND distribution_date between p_effort_start and p_effort_end
2810: AND psl.person_id = paf.person_id
2811: AND psl.assignment_id = paf.assignment_id
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
2824: WHERE
2825: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
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 ,
2861: per_time_periods ptp
2862: WHERE psl.award_id = gaa.award_id AND
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 ,
2899: per_time_periods ptp
2900: WHERE psl.project_id = ppa.project_id AND
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
2937: WHERE
2938: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
2989: AND reversal_entry_flag IS NULL
2990: AND rownum=1 )
2991: OR EXISTS (SELECT null
2992: FROM psp_distribution_lines_history pdlh
2993: , psp_summary_lines psl
2994: WHERE pdlh.summary_line_id = psl.summary_line_id
2995: AND distribution_date between p_effort_start and p_effort_end
2996: AND psl.person_id = paf.person_id
2997: AND psl.assignment_id = paf.assignment_id
3024: AND reversal_entry_flag IS NULL
3025: AND rownum=1 )
3026: OR EXISTS (SELECT null
3027: FROM psp_distribution_lines_history pdlh
3028: , psp_summary_lines psl
3029: WHERE pdlh.summary_line_id = psl.summary_line_id
3030: AND distribution_date between p_effort_start and p_effort_end
3031: AND psl.person_id = paf.person_id
3032: AND psl.assignment_id = paf.assignment_id
3059: AND reversal_entry_flag IS NULL
3060: AND rownum=1 )
3061: OR EXISTS (SELECT null
3062: FROM psp_distribution_lines_history pdlh
3063: , psp_summary_lines psl
3064: WHERE pdlh.summary_line_id = psl.summary_line_id
3065: AND distribution_date between p_effort_start and p_effort_end
3066: AND psl.person_id = paf.person_id
3067: AND psl.assignment_id = paf.assignment_id
3098: AND reversal_entry_flag IS NULL
3099: AND rownum=1 )
3100: OR EXISTS (SELECT null
3101: FROM psp_distribution_lines_history pdlh
3102: , psp_summary_lines psl
3103: WHERE pdlh.summary_line_id = psl.summary_line_id
3104: AND distribution_date between p_effort_start and p_effort_end
3105: AND psl.person_id = paf.person_id
3106: AND psl.assignment_id = paf.assignment_id
3133: AND reversal_entry_flag IS NULL
3134: AND rownum=1 )
3135: OR EXISTS (SELECT null
3136: FROM psp_distribution_lines_history pdlh
3137: , psp_summary_lines psl
3138: WHERE pdlh.summary_line_id = psl.summary_line_id
3139: AND distribution_date between p_effort_start and p_effort_end
3140: AND psl.person_id = paf.person_id
3141: AND psl.assignment_id = paf.assignment_id
3167: AND reversal_entry_flag IS NULL
3168: AND rownum=1 )
3169: OR EXISTS (SELECT null
3170: FROM psp_distribution_lines_history pdlh
3171: , psp_summary_lines psl
3172: WHERE pdlh.summary_line_id = psl.summary_line_id
3173: AND distribution_date between p_effort_start and p_effort_end
3174: AND psl.person_id = paf.person_id
3175: AND psl.assignment_id = paf.assignment_id
3206: AND reversal_entry_flag IS NULL
3207: AND rownum=1 )
3208: OR EXISTS (SELECT null
3209: FROM psp_distribution_lines_history pdlh
3210: , psp_summary_lines psl
3211: WHERE pdlh.summary_line_id = psl.summary_line_id
3212: AND distribution_date between p_effort_start and p_effort_end
3213: AND psl.person_id = paf.person_id
3214: AND psl.assignment_id = paf.assignment_id
3259: AND reversal_entry_flag IS NULL
3260: AND rownum=1 )
3261: OR EXISTS (SELECT null
3262: FROM psp_distribution_lines_history pdlh
3263: , psp_summary_lines psl
3264: WHERE pdlh.summary_line_id = psl.summary_line_id
3265: AND distribution_date between p_effort_start and p_effort_end
3266: AND psl.person_id = paf.person_id
3267: AND psl.assignment_id = paf.assignment_id
3323: AND reversal_entry_flag IS NULL
3324: AND rownum=1 )
3325: OR EXISTS (SELECT null
3326: FROM psp_distribution_lines_history pdlh
3327: , psp_summary_lines psl
3328: WHERE pdlh.summary_line_id = psl.summary_line_id
3329: AND distribution_date between :p_effort_start and :p_effort_end
3330: AND psl.person_id = paf.person_id
3331: AND psl.assignment_id = paf.assignment_id
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
3376: where gcc.code_combination_id= psl.gl_code_combination_id and
3706: ELSIF template_rec.array_sel_criteria(i) ='AWD' THEN
3707: INSERT INTO psp_selection_cardinality_gt
3708: (lookup_code, total_count)
3709: SELECT 'AWD', COUNT(DISTINCT psl.assignment_id)
3710: FROM psp_summary_lines psl,
3711: psp_report_template_details_h prtd ,
3712: per_time_periods ptp
3713: WHERE
3714: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
3744: ELSIF template_rec.array_sel_criteria(i)='ATY' THEN
3745: INSERT INTO psp_selection_cardinality_gt
3746: (lookup_code, total_count)
3747: SELECT 'ATY', COUNT(DISTINCT psl.assignment_id)
3748: FROM psp_summary_lines psl,
3749: psp_report_template_details_h prtd ,
3750: gms_awards_all gaa,
3751: per_time_periods ptp
3752: WHERE psl.award_id = gaa.award_id
3783: ELSIF template_rec.array_sel_criteria(i)='PRT' THEN
3784: INSERT INTO psp_selection_cardinality_gt
3785: (lookup_code, total_count)
3786: SELECT 'PRT', COUNT(DISTINCT psl.assignment_id)
3787: FROM psp_summary_lines psl,
3788: psp_report_template_details_h prtd ,
3789: pa_projects_all ppa ,
3790: per_time_periods ptp
3791: WHERE psl.project_id = ppa.project_id
3822: ELSIF template_rec.array_sel_criteria(i)='PRJ' THEN
3823: INSERT INTO psp_selection_cardinality_gt
3824: (lookup_code, total_count)
3825: SELECT 'PRJ', COUNT(DISTINCT psl.assignment_id)
3826: FROM psp_summary_lines psl,
3827: psp_report_template_details_h prtd ,
3828: per_time_periods ptp
3829: WHERE
3830: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
4076:
4077: IF l_sql_string IS NOT NULL THEN
4078: g_exec_string := 'INSERT INTO psp_selection_cardinality_gt(lookup_code, total_count)
4079: SELECT '|| '''' || 'GLA' || ''''|| ' , COUNT( DISTINCT psl.assignment_id)
4080: FROM psp_summary_lines psl,
4081: psp_distribution_lines_history pdnh,
4082: psp_pre_gen_dist_lines_history ppg,
4083: psp_adjustment_lines_history palh,
4084: gl_code_combinations gcc
4188: AND reversal_entry_flag IS NULL
4189: AND rownum=1 )
4190: OR EXISTS (SELECT null
4191: FROM psp_distribution_lines_history pdlh
4192: , psp_summary_lines psl
4193: WHERE pdlh.summary_line_id = psl.summary_line_id
4194: AND distribution_date between p_effort_start and p_effort_end
4195: AND psl.person_id = paf.person_id
4196: 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
4258: AND reversal_entry_flag IS NULL
4259: AND rownum=1 )
4260: OR EXISTS (SELECT null
4261: FROM psp_distribution_lines_history pdlh
4262: , psp_summary_lines psl
4263: WHERE pdlh.summary_line_id = psl.summary_line_id
4264: AND distribution_date between p_effort_start and p_effort_end
4265: AND psl.person_id = paf.person_id
4266: AND psl.assignment_id = paf.assignment_id
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
4276: WHERE
4277: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
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,
4312: per_time_periods ptp
4313: WHERE psl.award_id = gaa.award_id
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,
4350: per_time_periods ptp
4351: WHERE psl.project_id = ppa.project_id
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
4387: WHERE
4388: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
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
4471: AND reversal_entry_flag IS NULL
4472: AND rownum=1 )
4473: OR EXISTS (SELECT null
4474: FROM psp_distribution_lines_history pdlh
4475: , psp_summary_lines psl
4476: WHERE pdlh.summary_line_id = psl.summary_line_id
4477: AND distribution_date between p_effort_start and p_effort_end
4478: AND psl.person_id = paf.person_id
4479: AND psl.assignment_id = paf.assignment_id
4503: AND reversal_entry_flag IS NULL
4504: AND rownum=1 )
4505: OR EXISTS (SELECT null
4506: FROM psp_distribution_lines_history pdlh
4507: , psp_summary_lines psl
4508: WHERE pdlh.summary_line_id = psl.summary_line_id
4509: AND distribution_date between p_effort_start and p_effort_end
4510: AND psl.person_id = paf.person_id
4511: AND psl.assignment_id = paf.assignment_id
4539: AND reversal_entry_flag IS NULL
4540: AND rownum=1 )
4541: OR EXISTS (SELECT null
4542: FROM psp_distribution_lines_history pdlh
4543: , psp_summary_lines psl
4544: WHERE pdlh.summary_line_id = psl.summary_line_id
4545: AND distribution_date between p_effort_start and p_effort_end
4546: AND psl.person_id = paf.person_id
4547: 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
4620: AND reversal_entry_flag IS NULL
4621: AND rownum=1 )
4622: OR EXISTS (SELECT null
4623: FROM psp_distribution_lines_history pdlh
4624: , psp_summary_lines psl
4625: WHERE pdlh.summary_line_id = psl.summary_line_id
4626: AND distribution_date between p_effort_start and p_effort_end
4627: AND psl.person_id = paf.person_id
4628: AND psl.assignment_id = paf.assignment_id
4652: AND reversal_entry_flag IS NULL
4653: AND rownum=1 )
4654: OR EXISTS (SELECT null
4655: FROM psp_distribution_lines_history pdlh
4656: , psp_summary_lines psl
4657: WHERE pdlh.summary_line_id = psl.summary_line_id
4658: AND distribution_date between p_effort_start and p_effort_end
4659: AND psl.person_id = paf.person_id
4660: AND psl.assignment_id = paf.assignment_id
4684: AND reversal_entry_flag IS NULL
4685: AND rownum=1 )
4686: OR EXISTS (SELECT null
4687: FROM psp_distribution_lines_history pdlh
4688: , psp_summary_lines psl
4689: WHERE pdlh.summary_line_id = psl.summary_line_id
4690: AND distribution_date between p_effort_start and p_effort_end
4691: AND psl.person_id = paf.person_id
4692: AND psl.assignment_id = paf.assignment_id
4732: AND reversal_entry_flag IS NULL
4733: AND rownum=1 )
4734: OR EXISTS (SELECT null
4735: FROM psp_distribution_lines_history pdlh
4736: , psp_summary_lines psl
4737: WHERE pdlh.summary_line_id = psl.summary_line_id
4738: AND distribution_date between :p_effort_start and :p_effort_end
4739: AND psl.person_id = paf.person_id
4740: AND psl.assignment_id = paf.assignment_id
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,
4772: psp_pre_gen_dist_lines_history ppg,
4773: gl_code_combinations gcc
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 ,
4929: per_time_periods ptp
4930: WHERE
4931: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
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,
4970: gms_awards_all gaa,
4971: per_time_periods ptp
4972: WHERE psl.award_id = gaa.award_id
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,
5012: pa_projects_all ppa,
5013: per_time_periods ptp
5014: WHERE psl.project_id = ppa.project_id
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 ,
5054: per_time_periods ptp
5055: WHERE
5056: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
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
5305: WHERE gcc.code_combination_id= psl.gl_code_combination_id
5306: AND psl.business_group_id = '|| p_business_group_id || '
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,
5435: per_time_periods ptp
5436: WHERE
5437: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
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,
5473: gms_awards_all gaa,
5474: per_time_periods ptp
5475: WHERE psl.award_id = gaa.award_id
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,
5512: pa_projects_all ppa,
5513: per_time_periods ptp
5514: WHERE psl.project_id = ppa.project_id
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,
5551: per_time_periods ptp
5552: WHERE
5553: prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
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,
5772: psp_pre_gen_dist_lines_history ppg,
5773: gl_code_combinations gcc