[Home] [Help]
533: psl.set_of_books_id = p_set_of_books_id AND
534: psl.status_code= 'A' AND
535: ptp.time_period_id = psl.time_period_id AND
536: (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
537: ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
538: WHERE pdnh.summary_line_id = psl.summary_line_id
539: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
540: AND pdnh.reversal_entry_flag IS NULL
541: AND pdnh.adjustment_batch_name IS NULL )
572: psl.set_of_books_id = p_set_of_books_id AND
573: psl.status_code= 'A' AND
574: ptp.time_period_id = psl.time_period_id AND
575: (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
576: ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
577: WHERE pdnh.summary_line_id = psl.summary_line_id
578: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
579: AND pdnh.reversal_entry_flag IS NULL
580: AND pdnh.adjustment_batch_name IS NULL )
609: psl.set_of_books_id = p_set_of_books_id AND
610: psl.status_code= 'A' AND
611: ptp.time_period_id = psl.time_period_id AND
612: (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
613: ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
614: WHERE pdnh.summary_line_id = psl.summary_line_id
615: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
616: AND pdnh.reversal_entry_flag IS NULL
617: AND pdnh.adjustment_batch_name IS NULL )
645: psl.status_code= 'A' AND
646: ptp.time_period_id = psl.time_period_id AND
647: psl.project_id = TO_NUMBER(prtd.criteria_value1) AND
648: (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
649: ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
650: WHERE pdnh.summary_line_id = psl.summary_line_id
651: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
652: AND pdnh.reversal_entry_flag IS NULL
653: AND pdnh.adjustment_batch_name IS NULL )
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
1078: AND assignment_id = paf.assignment_id
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
1115: AND 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
1152: AND 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
1186: paf.person_id = psl.person_id AND
1187: paf.assignment_type = 'E' AND
1188: paf.effective_start_date <= p_effort_end AND
1189: paf.effective_end_date >= p_effort_start AND
1190: ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
1191: WHERE pdnh.summary_line_id = psl.summary_line_id
1192: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
1193: AND pdnh.reversal_entry_flag IS NULL
1194: AND pdnh.adjustment_batch_name IS NULL )
1229: paf.person_id = psl.person_id AND
1230: paf.assignment_type = 'E' AND
1231: paf.effective_start_date <= p_effort_end AND
1232: paf.effective_end_date >= p_effort_start AND
1233: ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
1234: WHERE pdnh.summary_line_id = psl.summary_line_id
1235: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
1236: AND pdnh.reversal_entry_flag IS NULL
1237: AND pdnh.adjustment_batch_name IS NULL )
1272: paf.person_id = psl.person_id AND
1273: paf.assignment_type = 'E' AND
1274: paf.effective_start_date <= p_effort_end AND
1275: paf.effective_end_date >= p_effort_start AND
1276: ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
1277: WHERE pdnh.summary_line_id = psl.summary_line_id
1278: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
1279: AND pdnh.reversal_entry_flag IS NULL
1280: AND pdnh.adjustment_batch_name IS NULL )
1319: paf.person_id = psl.person_id AND
1320: paf.assignment_type = 'E' AND
1321: paf.effective_start_date <= p_effort_end AND
1322: paf.effective_end_date >= p_effort_start AND
1323: ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
1324: WHERE pdnh.summary_line_id = psl.summary_line_id
1325: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
1326: AND pdnh.reversal_entry_flag IS NULL
1327: AND pdnh.adjustment_batch_name IS NULL )
1365: AND assignment_id = paf.assignment_id
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
1402: AND 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
1439: AND 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
1480: AND 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
1532: AND 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
1570: AND 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
1607: AND 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
1645: AND 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
1712: AND 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
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
1906: AND assignment_id = paf.assignment_id
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
1947: AND 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
1985: AND 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
2015: psl.set_of_books_id = p_set_of_books_id AND
2016: psl.status_code= 'A' AND
2017: ptp.time_period_id = psl.time_period_id AND
2018: (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
2019: ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
2020: WHERE pdnh.summary_line_id = psl.summary_line_id
2021: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
2022: AND pdnh.reversal_entry_flag IS NULL
2023: AND pdnh.adjustment_batch_name IS NULL )
2054: psl.set_of_books_id = p_set_of_books_id AND
2055: psl.status_code= 'A' AND
2056: ptp.time_period_id = psl.time_period_id AND
2057: (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
2058: ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
2059: WHERE pdnh.summary_line_id = psl.summary_line_id
2060: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
2061: AND pdnh.reversal_entry_flag IS NULL
2062: AND pdnh.adjustment_batch_name IS NULL )
2092: psl.set_of_books_id = p_set_of_books_id AND
2093: psl.status_code= 'A' AND
2094: ptp.time_period_id = psl.time_period_id AND
2095: (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
2096: ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
2097: WHERE pdnh.summary_line_id = psl.summary_line_id
2098: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
2099: AND pdnh.reversal_entry_flag IS NULL
2100: AND pdnh.adjustment_batch_name IS NULL )
2129: psl.set_of_books_id = p_set_of_books_id AND
2130: psl.status_code= 'A' AND
2131: ptp.time_period_id = psl.time_period_id AND
2132: (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
2133: ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
2134: WHERE pdnh.summary_line_id = psl.summary_line_id
2135: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
2136: AND pdnh.reversal_entry_flag IS NULL
2137: AND pdnh.adjustment_batch_name IS NULL )
2175: AND assignment_id = paf.assignment_id
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
2214: AND 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
2250: AND 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
2291: AND 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
2343: AND 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
2381: AND 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
2419: AND 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
2457: AND 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
2528: AND 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
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
2583: psl.business_group_id = '|| p_business_group_id || ' and
2695: AND assignment_id = paf.assignment_id
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
2734: AND 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
2769: AND 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
2798: psl.set_of_books_id = p_set_of_books_id AND
2799: psl.status_code= 'A' AND
2800: ptp.time_period_id = psl.time_period_id AND
2801: (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
2802: ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
2803: WHERE pdnh.summary_line_id = psl.summary_line_id
2804: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
2805: AND pdnh.reversal_entry_flag IS NULL
2806: AND pdnh.adjustment_batch_name IS NULL )
2836: psl.set_of_books_id = p_set_of_books_id AND
2837: psl.status_code= 'A' AND
2838: ptp.time_period_id = psl.time_period_id AND
2839: (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
2840: ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
2841: WHERE pdnh.summary_line_id = psl.summary_line_id
2842: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
2843: AND pdnh.reversal_entry_flag IS NULL
2844: AND pdnh.adjustment_batch_name IS NULL )
2874: psl.set_of_books_id = p_set_of_books_id AND
2875: psl.status_code= 'A' AND
2876: ptp.time_period_id = psl.time_period_id AND
2877: (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
2878: ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
2879: WHERE pdnh.summary_line_id = psl.summary_line_id
2880: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
2881: AND pdnh.reversal_entry_flag IS NULL
2882: AND pdnh.adjustment_batch_name IS NULL )
2911: psl.set_of_books_id = p_set_of_books_id AND
2912: psl.status_code= 'A' AND
2913: ptp.time_period_id = psl.time_period_id AND
2914: (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
2915: ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
2916: WHERE pdnh.summary_line_id = psl.summary_line_id
2917: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
2918: AND pdnh.reversal_entry_flag IS NULL
2919: AND pdnh.adjustment_batch_name IS NULL )
2955: AND assignment_id = paf.assignment_id
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
2990: AND 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
3025: AND 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
3064: AND 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
3099: AND 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
3133: AND 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
3172: AND 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
3225: AND 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
3289: AND 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
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
3344: psl.business_group_id = '|| p_business_group_id || ' and
3689: AND ptp.time_period_id = psl.time_period_id
3690: AND (ptp.start_date <= p_effort_end
3691: AND ptp.end_date >= p_effort_start)
3692: AND (EXISTS (SELECT 1
3693: FROM psp_distribution_lines_history pdnh
3694: WHERE pdnh.summary_line_id = psl.summary_line_id
3695: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
3696: AND pdnh.reversal_entry_flag IS NULL
3697: AND pdnh.adjustment_batch_name IS NULL )
3728: AND ptp.time_period_id = psl.time_period_id
3729: AND (ptp.start_date <= p_effort_end
3730: AND ptp.end_date >= p_effort_start)
3731: AND (EXISTS (SELECT 1
3732: FROM psp_distribution_lines_history pdnh
3733: WHERE pdnh.summary_line_id = psl.summary_line_id
3734: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
3735: AND pdnh.reversal_entry_flag IS NULL
3736: AND pdnh.adjustment_batch_name IS NULL )
3767: AND ptp.time_period_id = psl.time_period_id
3768: AND (ptp.start_date <= p_effort_end
3769: AND ptp.end_date >= p_effort_start)
3770: AND (EXISTS (SELECT 1
3771: FROM psp_distribution_lines_history pdnh
3772: WHERE pdnh.summary_line_id = psl.summary_line_id
3773: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
3774: AND pdnh.reversal_entry_flag IS NULL
3775: AND pdnh.adjustment_batch_name IS NULL )
3804: AND psl.status_code= 'A'
3805: AND ptp.time_period_id = psl.time_period_id
3806: AND (ptp.start_date <= p_effort_end
3807: AND ptp.end_date >= p_effort_start)
3808: AND (EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
3809: WHERE pdnh.summary_line_id = psl.summary_line_id
3810: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
3811: AND pdnh.reversal_entry_flag IS NULL
3812: AND pdnh.adjustment_batch_name IS NULL )
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
4052: WHERE psl.business_group_id = '|| p_business_group_id || '
4154: AND assignment_id = paf.assignment_id
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
4191: AND 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
4224: AND 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
4251: AND psl.status_code= 'A'
4252: AND ptp.time_period_id = psl.time_period_id
4253: AND (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start)
4254: AND ( EXISTS (SELECT 1
4255: FROM psp_distribution_lines_history pdnh
4256: WHERE pdnh.summary_line_id = psl.summary_line_id
4257: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
4258: AND pdnh.reversal_entry_flag IS NULL
4259: AND pdnh.adjustment_batch_name IS NULL )
4288: AND psl.status_code= 'A'
4289: AND ptp.time_period_id = psl.time_period_id
4290: AND (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start)
4291: AND ( EXISTS (SELECT 1
4292: FROM psp_distribution_lines_history pdnh
4293: WHERE pdnh.summary_line_id = psl.summary_line_id
4294: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
4295: AND pdnh.reversal_entry_flag IS NULL
4296: AND pdnh.adjustment_batch_name IS NULL )
4326: AND psl.status_code= 'A'
4327: AND ptp.time_period_id = psl.time_period_id
4328: AND (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start)
4329: AND ( EXISTS (SELECT 1
4330: FROM psp_distribution_lines_history pdnh
4331: WHERE pdnh.summary_line_id = psl.summary_line_id
4332: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
4333: AND pdnh.reversal_entry_flag IS NULL
4334: AND pdnh.adjustment_batch_name IS NULL )
4362: AND psl.status_code= 'A'
4363: AND ptp.time_period_id = psl.time_period_id
4364: AND (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start)
4365: AND ( EXISTS (SELECT 1
4366: FROM psp_distribution_lines_history pdnh
4367: WHERE pdnh.summary_line_id = psl.summary_line_id
4368: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
4369: AND pdnh.reversal_entry_flag IS NULL
4370: AND pdnh.adjustment_batch_name IS NULL )
4404: AND assignment_id = paf.assignment_id
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
4437: AND 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
4469: AND 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
4505: AND 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
4553: AND 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
4586: AND 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
4618: AND 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
4650: AND 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
4698: AND 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
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
4741: WHERE psl.business_group_id = '|| p_business_group_id || '
4906: AND ptp.time_period_id = psl.time_period_id
4907: AND (ptp.start_date <= p_effort_end
4908: AND ptp.end_date >= p_effort_start)
4909: AND ( EXISTS (SELECT 1
4910: FROM psp_distribution_lines_history pdnh
4911: WHERE pdnh.summary_line_id = psl.summary_line_id
4912: AND pdnh.distribution_date BETWEEN p_effort_start
4913: AND p_effort_end
4914: AND pdnh.reversal_entry_flag IS NULL
4948: AND ptp.time_period_id = psl.time_period_id
4949: AND (ptp.start_date <= p_effort_end
4950: AND ptp.end_date >= p_effort_start)
4951: AND ( EXISTS (SELECT 1
4952: FROM psp_distribution_lines_history pdnh
4953: WHERE pdnh.summary_line_id = psl.summary_line_id
4954: AND pdnh.distribution_date BETWEEN p_effort_start
4955: AND p_effort_end
4956: AND pdnh.reversal_entry_flag IS NULL
4990: AND ptp.time_period_id = psl.time_period_id
4991: AND (ptp.start_date <= p_effort_end
4992: AND ptp.end_date >= p_effort_start)
4993: AND ( EXISTS (SELECT 1
4994: FROM psp_distribution_lines_history pdnh
4995: WHERE pdnh.summary_line_id = psl.summary_line_id
4996: AND pdnh.distribution_date BETWEEN p_effort_start
4997: AND p_effort_end
4998: AND pdnh.reversal_entry_flag IS NULL
5031: AND ptp.time_period_id = psl.time_period_id
5032: AND (ptp.start_date <= p_effort_end
5033: AND ptp.end_date >= p_effort_start)
5034: AND ( EXISTS (SELECT 1
5035: FROM psp_distribution_lines_history pdnh
5036: WHERE pdnh.summary_line_id = psl.summary_line_id
5037: AND pdnh.distribution_date BETWEEN p_effort_start
5038: AND p_effort_end
5039: AND pdnh.reversal_entry_flag IS NULL
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 || '
5412: AND ptp.time_period_id = psl.time_period_id
5413: AND (ptp.start_date <= p_effort_end
5414: AND ptp.end_date >= p_effort_start)
5415: AND ( EXISTS (SELECT 1
5416: FROM psp_distribution_lines_history pdnh
5417: WHERE pdnh.summary_line_id = psl.summary_line_id
5418: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
5419: AND pdnh.reversal_entry_flag IS NULL
5420: AND pdnh.adjustment_batch_name IS NULL)
5451: AND ptp.time_period_id = psl.time_period_id
5452: AND (ptp.start_date <= p_effort_end
5453: AND ptp.end_date >= p_effort_start)
5454: AND ( EXISTS (SELECT 1
5455: FROM psp_distribution_lines_history pdnh
5456: WHERE pdnh.summary_line_id = psl.summary_line_id
5457: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
5458: AND pdnh.reversal_entry_flag IS NULL
5459: AND pdnh.adjustment_batch_name IS NULL)
5490: AND ptp.time_period_id = psl.time_period_id
5491: AND (ptp.start_date <= p_effort_end
5492: AND ptp.end_date >= p_effort_start)
5493: AND ( EXISTS (SELECT 1
5494: FROM psp_distribution_lines_history pdnh
5495: WHERE pdnh.summary_line_id = psl.summary_line_id
5496: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
5497: AND pdnh.reversal_entry_flag IS NULL
5498: AND pdnh.adjustment_batch_name IS NULL)
5528: AND ptp.time_period_id = psl.time_period_id
5529: AND (ptp.start_date <= p_effort_end
5530: AND ptp.end_date >= p_effort_start)
5531: AND ( EXISTS (SELECT 1
5532: FROM psp_distribution_lines_history pdnh
5533: WHERE pdnh.summary_line_id = psl.summary_line_id
5534: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
5535: AND pdnh.reversal_entry_flag IS NULL
5536: AND pdnh.adjustment_batch_name IS NULL)
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
5741: WHERE gcc.code_combination_id= psl.gl_code_combination_id