[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
1831: AND assignment_id = paf.assignment_id
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
1939: AND 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
1980: AND 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
2018: AND 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
2048: psl.set_of_books_id = p_set_of_books_id AND
2049: psl.status_code= 'A' AND
2050: ptp.time_period_id = psl.time_period_id AND
2051: (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
2052: ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
2053: WHERE pdnh.summary_line_id = psl.summary_line_id
2054: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
2055: AND pdnh.reversal_entry_flag IS NULL
2056: AND pdnh.adjustment_batch_name IS NULL )
2087: psl.set_of_books_id = p_set_of_books_id AND
2088: psl.status_code= 'A' AND
2089: ptp.time_period_id = psl.time_period_id AND
2090: (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
2091: ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
2092: WHERE pdnh.summary_line_id = psl.summary_line_id
2093: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
2094: AND pdnh.reversal_entry_flag IS NULL
2095: AND pdnh.adjustment_batch_name IS NULL )
2125: psl.set_of_books_id = p_set_of_books_id AND
2126: psl.status_code= 'A' AND
2127: ptp.time_period_id = psl.time_period_id AND
2128: (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
2129: ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
2130: WHERE pdnh.summary_line_id = psl.summary_line_id
2131: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
2132: AND pdnh.reversal_entry_flag IS NULL
2133: AND pdnh.adjustment_batch_name IS NULL )
2162: psl.set_of_books_id = p_set_of_books_id AND
2163: psl.status_code= 'A' AND
2164: ptp.time_period_id = psl.time_period_id AND
2165: (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
2166: ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
2167: WHERE pdnh.summary_line_id = psl.summary_line_id
2168: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
2169: AND pdnh.reversal_entry_flag IS NULL
2170: AND pdnh.adjustment_batch_name IS NULL )
2208: AND assignment_id = paf.assignment_id
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
2247: AND 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
2283: AND 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
2324: AND 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
2376: AND 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
2414: AND 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
2452: AND 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
2490: AND 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
2561: AND 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
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
2616: psl.business_group_id = '|| p_business_group_id || ' and
2728: AND assignment_id = paf.assignment_id
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
2767: AND 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
2802: AND 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
2831: psl.set_of_books_id = p_set_of_books_id AND
2832: psl.status_code= 'A' AND
2833: ptp.time_period_id = psl.time_period_id AND
2834: (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
2835: ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
2836: WHERE pdnh.summary_line_id = psl.summary_line_id
2837: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
2838: AND pdnh.reversal_entry_flag IS NULL
2839: AND pdnh.adjustment_batch_name IS NULL )
2869: psl.set_of_books_id = p_set_of_books_id AND
2870: psl.status_code= 'A' AND
2871: ptp.time_period_id = psl.time_period_id AND
2872: (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
2873: ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
2874: WHERE pdnh.summary_line_id = psl.summary_line_id
2875: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
2876: AND pdnh.reversal_entry_flag IS NULL
2877: AND pdnh.adjustment_batch_name IS NULL )
2907: psl.set_of_books_id = p_set_of_books_id AND
2908: psl.status_code= 'A' AND
2909: ptp.time_period_id = psl.time_period_id AND
2910: (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
2911: ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
2912: WHERE pdnh.summary_line_id = psl.summary_line_id
2913: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
2914: AND pdnh.reversal_entry_flag IS NULL
2915: AND pdnh.adjustment_batch_name IS NULL )
2944: psl.set_of_books_id = p_set_of_books_id AND
2945: psl.status_code= 'A' AND
2946: ptp.time_period_id = psl.time_period_id AND
2947: (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
2948: ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
2949: WHERE pdnh.summary_line_id = psl.summary_line_id
2950: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
2951: AND pdnh.reversal_entry_flag IS NULL
2952: AND pdnh.adjustment_batch_name IS NULL )
2988: AND assignment_id = paf.assignment_id
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
3023: AND 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
3058: AND 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
3097: AND 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
3132: AND 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
3166: AND 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
3205: AND 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
3258: AND 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
3322: AND 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
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
3377: psl.business_group_id = '|| p_business_group_id || ' and
3722: AND ptp.time_period_id = psl.time_period_id
3723: AND (ptp.start_date <= p_effort_end
3724: AND ptp.end_date >= p_effort_start)
3725: AND (EXISTS (SELECT 1
3726: FROM psp_distribution_lines_history pdnh
3727: WHERE pdnh.summary_line_id = psl.summary_line_id
3728: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
3729: AND pdnh.reversal_entry_flag IS NULL
3730: AND pdnh.adjustment_batch_name IS NULL )
3761: AND ptp.time_period_id = psl.time_period_id
3762: AND (ptp.start_date <= p_effort_end
3763: AND ptp.end_date >= p_effort_start)
3764: AND (EXISTS (SELECT 1
3765: FROM psp_distribution_lines_history pdnh
3766: WHERE pdnh.summary_line_id = psl.summary_line_id
3767: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
3768: AND pdnh.reversal_entry_flag IS NULL
3769: AND pdnh.adjustment_batch_name IS NULL )
3800: AND ptp.time_period_id = psl.time_period_id
3801: AND (ptp.start_date <= p_effort_end
3802: AND ptp.end_date >= p_effort_start)
3803: AND (EXISTS (SELECT 1
3804: FROM psp_distribution_lines_history pdnh
3805: WHERE pdnh.summary_line_id = psl.summary_line_id
3806: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
3807: AND pdnh.reversal_entry_flag IS NULL
3808: AND pdnh.adjustment_batch_name IS NULL )
3837: AND psl.status_code= 'A'
3838: AND ptp.time_period_id = psl.time_period_id
3839: AND (ptp.start_date <= p_effort_end
3840: AND ptp.end_date >= p_effort_start)
3841: AND (EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
3842: WHERE pdnh.summary_line_id = psl.summary_line_id
3843: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
3844: AND pdnh.reversal_entry_flag IS NULL
3845: AND pdnh.adjustment_batch_name IS NULL )
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
4085: WHERE psl.business_group_id = '|| p_business_group_id || '
4187: AND assignment_id = paf.assignment_id
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
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
4257: AND 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
4284: AND psl.status_code= 'A'
4285: AND ptp.time_period_id = psl.time_period_id
4286: AND (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start)
4287: AND ( EXISTS (SELECT 1
4288: FROM psp_distribution_lines_history pdnh
4289: WHERE pdnh.summary_line_id = psl.summary_line_id
4290: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
4291: AND pdnh.reversal_entry_flag IS NULL
4292: AND pdnh.adjustment_batch_name IS NULL )
4321: AND psl.status_code= 'A'
4322: AND ptp.time_period_id = psl.time_period_id
4323: AND (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start)
4324: AND ( EXISTS (SELECT 1
4325: FROM psp_distribution_lines_history pdnh
4326: WHERE pdnh.summary_line_id = psl.summary_line_id
4327: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
4328: AND pdnh.reversal_entry_flag IS NULL
4329: AND pdnh.adjustment_batch_name IS NULL )
4359: AND psl.status_code= 'A'
4360: AND ptp.time_period_id = psl.time_period_id
4361: AND (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start)
4362: AND ( EXISTS (SELECT 1
4363: FROM psp_distribution_lines_history pdnh
4364: WHERE pdnh.summary_line_id = psl.summary_line_id
4365: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
4366: AND pdnh.reversal_entry_flag IS NULL
4367: AND pdnh.adjustment_batch_name IS NULL )
4395: AND psl.status_code= 'A'
4396: AND ptp.time_period_id = psl.time_period_id
4397: AND (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start)
4398: AND ( EXISTS (SELECT 1
4399: FROM psp_distribution_lines_history pdnh
4400: WHERE pdnh.summary_line_id = psl.summary_line_id
4401: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
4402: AND pdnh.reversal_entry_flag IS NULL
4403: AND pdnh.adjustment_batch_name IS NULL )
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
4470: AND 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
4502: AND 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
4538: AND 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
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
4619: AND 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
4651: AND 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
4683: AND 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
4731: AND 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
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
4774: WHERE psl.business_group_id = '|| p_business_group_id || '
4939: AND ptp.time_period_id = psl.time_period_id
4940: AND (ptp.start_date <= p_effort_end
4941: AND ptp.end_date >= p_effort_start)
4942: AND ( EXISTS (SELECT 1
4943: FROM psp_distribution_lines_history pdnh
4944: WHERE pdnh.summary_line_id = psl.summary_line_id
4945: AND pdnh.distribution_date BETWEEN p_effort_start
4946: AND p_effort_end
4947: AND pdnh.reversal_entry_flag IS NULL
4981: AND ptp.time_period_id = psl.time_period_id
4982: AND (ptp.start_date <= p_effort_end
4983: AND ptp.end_date >= p_effort_start)
4984: AND ( EXISTS (SELECT 1
4985: FROM psp_distribution_lines_history pdnh
4986: WHERE pdnh.summary_line_id = psl.summary_line_id
4987: AND pdnh.distribution_date BETWEEN p_effort_start
4988: AND p_effort_end
4989: AND pdnh.reversal_entry_flag IS NULL
5023: AND ptp.time_period_id = psl.time_period_id
5024: AND (ptp.start_date <= p_effort_end
5025: AND ptp.end_date >= p_effort_start)
5026: AND ( EXISTS (SELECT 1
5027: FROM psp_distribution_lines_history pdnh
5028: WHERE pdnh.summary_line_id = psl.summary_line_id
5029: AND pdnh.distribution_date BETWEEN p_effort_start
5030: AND p_effort_end
5031: AND pdnh.reversal_entry_flag IS NULL
5064: AND ptp.time_period_id = psl.time_period_id
5065: AND (ptp.start_date <= p_effort_end
5066: AND ptp.end_date >= p_effort_start)
5067: AND ( EXISTS (SELECT 1
5068: FROM psp_distribution_lines_history pdnh
5069: WHERE pdnh.summary_line_id = psl.summary_line_id
5070: AND pdnh.distribution_date BETWEEN p_effort_start
5071: AND p_effort_end
5072: AND pdnh.reversal_entry_flag IS NULL
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 || '
5445: AND ptp.time_period_id = psl.time_period_id
5446: AND (ptp.start_date <= p_effort_end
5447: AND ptp.end_date >= p_effort_start)
5448: AND ( EXISTS (SELECT 1
5449: FROM psp_distribution_lines_history pdnh
5450: WHERE pdnh.summary_line_id = psl.summary_line_id
5451: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
5452: AND pdnh.reversal_entry_flag IS NULL
5453: AND pdnh.adjustment_batch_name IS NULL)
5484: AND ptp.time_period_id = psl.time_period_id
5485: AND (ptp.start_date <= p_effort_end
5486: AND ptp.end_date >= p_effort_start)
5487: AND ( EXISTS (SELECT 1
5488: FROM psp_distribution_lines_history pdnh
5489: WHERE pdnh.summary_line_id = psl.summary_line_id
5490: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
5491: AND pdnh.reversal_entry_flag IS NULL
5492: AND pdnh.adjustment_batch_name IS NULL)
5523: AND ptp.time_period_id = psl.time_period_id
5524: AND (ptp.start_date <= p_effort_end
5525: AND ptp.end_date >= p_effort_start)
5526: AND ( EXISTS (SELECT 1
5527: FROM psp_distribution_lines_history pdnh
5528: WHERE pdnh.summary_line_id = psl.summary_line_id
5529: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
5530: AND pdnh.reversal_entry_flag IS NULL
5531: AND pdnh.adjustment_batch_name IS NULL)
5561: AND ptp.time_period_id = psl.time_period_id
5562: AND (ptp.start_date <= p_effort_end
5563: AND ptp.end_date >= p_effort_start)
5564: AND ( EXISTS (SELECT 1
5565: FROM psp_distribution_lines_history pdnh
5566: WHERE pdnh.summary_line_id = psl.summary_line_id
5567: AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
5568: AND pdnh.reversal_entry_flag IS NULL
5569: AND pdnh.adjustment_batch_name IS NULL)
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
5774: WHERE gcc.code_combination_id= psl.gl_code_combination_id