[Home] [Help]
567: SELECT count(*) into l_test
568: FROM pji_prg_batch
569: WHERE nvl(curr_request_status,'PENDING') <> 'COMPLETED'
570: and( exists -- added sridhar_refresh
571: ( select 'x' from PJI_LAUNCH_INCR
572: where incr_type = 'REFRESH' and g_launch_type = 'RPPD' ) -- sridhar_phase_1 p_temp_table_size = 666666 )
573: or
574: exists -- added sridhar_refresh
575: ( select 'x' from PJI_LAUNCH_INCR
571: ( select 'x' from PJI_LAUNCH_INCR
572: where incr_type = 'REFRESH' and g_launch_type = 'RPPD' ) -- sridhar_phase_1 p_temp_table_size = 666666 )
573: or
574: exists -- added sridhar_refresh
575: ( select 'x' from PJI_LAUNCH_INCR
576: where incr_type <> 'REFRESH' and g_launch_type = 'UPPD' ) -- sridhar_phase_1 and p_temp_table_size <> 666666 )
577: );
578: IF ( l_test = 0 ) THEN
579:
906: union all
907: select /*+ ordered index(ver PA_PROJ_ELEMENT_VERSIONS_N5) */
908: ver.PRG_GROUP , count( distinct ver.project_id ) cnt, 'PRG_CHANGE' prg_type
909: from
910: PJI_LAUNCH_INCR grp ,
911: PA_PROJ_ELEMENT_VERSIONS ver
912: where ( nvl(p_incremental_mode,'N') = 'Y' or g_launch_type = 'RPPD' ) and -- sridhar_phase_1 p_wbs_temp_table_size = 666666 ) and -- sridhar refresh and
913: ver.object_type = 'PA_STRUCTURES' and
914: grp.incr_type in ( 'PRG_BASE', 'REFRESH') and
916: group by ver.prg_group
917: union all
918: select grp.prg_group ,count( distinct grp.project_id ) cnt, 'PRG_PARENT' prg_type
919: from
920: PJI_LAUNCH_INCR grp
921: where ( nvl(p_incremental_mode,'N') = 'Y' or g_launch_type = 'RPPD' ) and -- sridhar_phase_1 p_wbs_temp_table_size = 666666 ) and -- sridhar refresh and
922: grp.incr_type not in ( 'PRG_BASE' , 'REFRESH') and
923: grp.prg_group > 0
924: group by grp.prg_group;
941: and sub_level = sup_level
942: and prg_group = x_prg_group
943: UNION
944: select prg_group , prg_level , project_id
945: from PJI_LAUNCH_INCR grp
946: where grp.prg_group = x_prg_group
947: and x_prg_type = 'PRG_PARENT'
948: and grp.incr_type not in ( 'PRG_BASE' , 'REFRESH')
949: and grp.prg_group > 0 ;
990:
991: select count(1) into r_count
992: from dual
993: where exists
994: ( select 'x' from PJI_LAUNCH_INCR where incr_type = 'REFRESH' ) ;
995:
996: select count(1) into u_count
997: from dual
998: where exists
995:
996: select count(1) into u_count
997: from dual
998: where exists
999: ( select 'x' from PJI_LAUNCH_INCR where incr_type <> 'REFRESH' ) ;
1000:
1001:
1002:
1003:
1325: (select 'x' from pji_prg_group b
1326: where a.project_id = b.project_id)
1327: union all
1328: select project_id
1329: from pji_launch_incr a
1330: where nvl(p_incremental_mode,'N') = 'Y'
1331: and incr_type like 'PROJ%'
1332: and prg_group = -1
1333: and not exists
1460: ( select 'x'
1461: from pji_prg_group b where a.project_id = b.project_id )
1462: union all
1463: select a.project_id
1464: from pji_launch_incr a, pa_projects_all b /* added for bug 9712797 */
1465: where ( nvl(p_incremental_mode,'N') = 'Y' or g_launch_type = 'RPPD' ) -- sridhar_phase_1 p_wbs_temp_table_size = 666666
1466: and a. project_id = b.project_id /* added for bug 9712797 */
1467: -- Moved following condition to the next line for UPPD case. Not applicable for RPPD case
1468: -- and nvl(b.project_status_code,'PS') = nvl(p_project_status,nvl(b.project_status_code,'PS')) /* added for bug 9712797 */
1589: sum(decode(incr_type, 'VAL_ACT_WP_COST',1,0)) WP_COST, /* Added for bug 12328939 */
1590: sum(decode(incr_type, 'VAL_ACT_FC_COST',1,0)) FC_COST /* Added for bug 12328939 */
1591: from
1592: ( select distinct incr_type , project_id
1593: FROM PJI_LAUNCH_INCR where incr_type like 'VAL%'
1594: ) GROUP BY project_id ) t1 , pa_projects_all p1
1595: where p1.project_id = t1.project_id
1596: order by p1.segment1 ;
1597:
1614: l_FC_COST , /* Added for bug 12328939 */
1615: l_proj_count
1616: from
1617: ( select distinct incr_type , project_id
1618: FROM PJI_LAUNCH_INCR where incr_type like 'VAL%'
1619: );
1620:
1621: print_line('----------------------------------------------------------------------------');
1622: print_line(' Reporting Measures Validation Report ');
1663: LOOP
1664: print_line(get_proj_rec.project_line);
1665: END LOOP;
1666:
1667: DELETE FROM PJI_LAUNCH_INCR where incr_type like 'VAL%';
1668:
1669: END generate_validation_report;
1670:
1671: PROCEDURE Create_refresh_proj_list(p_operating_unit in number) IS
1773: where nvl(costbud.BUDGET_COST, 0) <> nvl(costbud.REPORTING_COST, 0)
1774: AND pa.project_id = costbud.project_id
1775: AND pa.template_flag = 'N'
1776: AND pa.org_id = nvl(p_operating_unit, pa.org_id )
1777: AND ( not exists ( select 'x' from PJI_LAUNCH_INCR l1 where l1.project_id = pa.project_id )
1778: or l_run_type <> 'REFRESH');
1779:
1780: -- Reporting script for Approved Revenue Budgets:
1781: CURSOR get_proj_budget_rev IS
1818: where nvl(revbud.BUDGET_REVENUE, 0) <> nvl(revbud.REPORTING_REVENUE, 0)
1819: AND pa.project_id = revbud.project_id
1820: AND pa.template_flag = 'N'
1821: AND pa.org_id = nvl(p_operating_unit, pa.org_id )
1822: AND ( not exists ( select 'x' from PJI_LAUNCH_INCR l1 where l1.project_id = pa.project_id )
1823: or l_run_type <> 'REFRESH');
1824:
1825: -- For Actuals
1826: CURSOR get_proj_cost IS
1865: AND p1.project_id = fact1.project_id (+)
1866: AND NVL(cdl1.amt,0) <> NVL(fact1.amt,0)
1867: AND (abs(NVL(cdl1.amt,0) - NVL(fact1.amt,0)) > l_tolerance_amt)
1868: AND p1.org_id = nvl(p_operating_unit, p1.org_id )
1869: AND ( not exists ( select 'x' from PJI_LAUNCH_INCR l1 where l1.project_id = p1.project_id )
1870: or l_run_type <> 'REFRESH') ;
1871:
1872: /* Added for bug 12328939 starts */
1873: -- For Workplan Actuals
1917: OR base.act_eq_effort <> nvl(rep.act_eq_effort,0)
1918: OR base.act_raw_cost <> nvl(rep.act_raw_cost,0)
1919: OR base.act_brdn_cost <> nvl(rep.act_brdn_cost,0))
1920: AND p1.org_id = nvl(p_operating_unit, p1.org_id )
1921: AND ( not exists ( select 'x' from PJI_LAUNCH_INCR l1 where l1.project_id = p1.project_id )
1922: or l_run_type <> 'REFRESH') ;
1923: -- For Cost Forecast Actuals
1924: CURSOR get_proj_fc_cost IS
1925: SELECT distinct p1.project_id
1964: OR base.act_eq_effort <> nvl(rep.act_eq_effort,0)
1965: OR base.act_raw_cost <> nvl(rep.act_raw_cost,0)
1966: OR base.act_brdn_cost <> nvl(rep.act_brdn_cost,0))
1967: AND p1.org_id = nvl(p_operating_unit, p1.org_id )
1968: AND ( not exists ( select 'x' from PJI_LAUNCH_INCR l1 where l1.project_id = p1.project_id )
1969: or l_run_type <> 'REFRESH') ;
1970: /* Added for bug 12328939 ends */
1971:
1972: TYPE num_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1981:
1982: BEGIN
1983: if ( g_validation_process = 'N') then
1984:
1985: DELETE FROM PJI_LAUNCH_INCR;
1986:
1987: l_run_type := 'REFRESH';
1988: l_data_type := 'REFRESH';
1989: l_data_type_parent := 'REF_PARENT';
1988: l_data_type := 'REFRESH';
1989: l_data_type_parent := 'REF_PARENT';
1990:
1991: else
1992: DELETE FROM PJI_LAUNCH_INCR; /* Added for bug 12328939 */
1993:
1994: l_run_type := 'VALIDATION';
1995: l_data_type := 'VAL';
1996: l_data_type_parent := 'VAL_PARENT';
2007: FETCH get_proj_backlog bulk collect into v_project_id;
2008:
2009: IF v_project_id.COUNT > 0 THEN
2010: FORALL i in v_project_id.FIRST..v_project_id.LAST
2011: INSERT INTO PJI_LAUNCH_INCR
2012: (incr_type, project_id, prg_group ,prg_level )
2013: values
2014: (l_data_type,
2015: v_project_id(i),
2033: FETCH get_proj_budget_cost bulk collect into v_project_id;
2034:
2035: IF v_project_id.COUNT > 0 THEN
2036: FORALL i in v_project_id.FIRST..v_project_id.LAST
2037: INSERT INTO PJI_LAUNCH_INCR
2038: (incr_type, project_id,prg_group , prg_level )
2039: values
2040: (l_data_type,
2041: v_project_id(i),
2059: FETCH get_proj_budget_rev bulk collect into v_project_id;
2060:
2061: IF v_project_id.COUNT > 0 THEN
2062: FORALL i in v_project_id.FIRST..v_project_id.LAST
2063: INSERT INTO PJI_LAUNCH_INCR
2064: (incr_type, project_id, prg_group ,prg_level )
2065: values
2066: (l_data_type,
2067: v_project_id(i),
2086: FETCH get_proj_cost bulk collect into v_project_id;
2087:
2088: IF v_project_id.COUNT > 0 THEN
2089: FORALL i in v_project_id.FIRST..v_project_id.LAST
2090: INSERT INTO PJI_LAUNCH_INCR
2091: (incr_type, project_id, prg_group ,prg_level )
2092: values
2093: (l_data_type,
2094: v_project_id(i),
2112: FETCH get_proj_wp_cost bulk collect into v_project_id;
2113:
2114: IF v_project_id.COUNT > 0 THEN
2115: FORALL i in v_project_id.FIRST..v_project_id.LAST
2116: INSERT INTO PJI_LAUNCH_INCR
2117: (incr_type, project_id, prg_group ,prg_level )
2118: values
2119: (l_data_type,
2120: v_project_id(i),
2136: FETCH get_proj_fc_cost bulk collect into v_project_id;
2137:
2138: IF v_project_id.COUNT > 0 THEN
2139: FORALL i in v_project_id.FIRST..v_project_id.LAST
2140: INSERT INTO PJI_LAUNCH_INCR
2141: (incr_type, project_id, prg_group ,prg_level )
2142: values
2143: (l_data_type,
2144: v_project_id(i),
2153:
2154: if ( l_run_type = 'REFRESH' ) then
2155:
2156:
2157: UPDATE PJI_LAUNCH_INCR a
2158: SET a.INCR_TYPE = 'PROJ_WRK'
2159: WHERE
2160: exists
2161: ( select 'x'
2174: SELECT /*+ ordered index(a PA_PROJ_ELEMENT_VERSIONS_N6) index(b PA_OBJECT_RELATIONSHIPS_N4) */
2175: DISTINCT
2176: a0.project_id child_proj_id , a.prg_level child_prg_level,
2177: c.project_id parent_proj_id , c.prg_group , c.prg_level parent_prg_level
2178: FROM pji_launch_incr a0,
2179: pa_proj_element_versions a,
2180: pa_object_relationships b,
2181: pa_proj_element_versions c
2182: WHERE a0.incr_type in ( 'PROJ_WRK', 'PROJ_WRK_NEW' )
2190: ) LOOP
2191:
2192: l_prg_parent := 1;
2193:
2194: UPDATE PJI_LAUNCH_INCR
2195: SET prg_group = PRG_PARENT_NODE.prg_group,
2196: incr_type = decode(incr_type,'PROJ_WRK','PROJ_B_PARENT','PROJ_PARENT'),
2197: prg_level = PRG_PARENT_NODE.child_prg_level
2198: WHERE project_id = PRG_PARENT_NODE.child_proj_id
2197: prg_level = PRG_PARENT_NODE.child_prg_level
2198: WHERE project_id = PRG_PARENT_NODE.child_proj_id
2199: AND incr_type in ( 'PROJ_WRK', 'PROJ_WRK_NEW' );
2200:
2201: INSERT INTO PJI_LAUNCH_INCR a
2202: (a.incr_type, a.prg_group , a.project_id , a.prg_level)
2203: select
2204: 'PROJ_WRK_NEW',
2205: PRG_PARENT_NODE.prg_group,
2208: from dual
2209: where not exists
2210: (
2211: select 'x'
2212: from PJI_LAUNCH_INCR b
2213: where PRG_PARENT_NODE.parent_proj_id = b.project_id
2214: and PRG_PARENT_NODE.prg_group = b.prg_group
2215: and PRG_PARENT_NODE.parent_prg_level = b.prg_level
2216: and b.incr_type in ('PROJ_PARENT','PROJ_B_PARENT','PROJ_WRK_NEW')
2225: l_prg_count := l_prg_count+1;
2226:
2227: END LOOP;
2228:
2229: UPDATE PJI_LAUNCH_INCR
2230: SET incr_type = decode(incr_type,'PROJ_WRK',l_data_type,l_data_type_parent)
2231: WHERE incr_type in ( 'PROJ_WRK', 'PROJ_WRK_NEW' );
2232:
2233:
2236: /**
2237: if ( l_run_type = 'VALIDATION') then
2238:
2239: DELETE FROM pji_data_validation;
2240: insert into pji_data_validation select * from pji_launch_incr where incr_type like 'VAL%';
2241: DELETE FROM PJI_LAUNCH_INCR where incr_type like 'VAL%';
2242:
2243: end if ; -- if run_type = 'VALIDATION'
2244: **/
2237: if ( l_run_type = 'VALIDATION') then
2238:
2239: DELETE FROM pji_data_validation;
2240: insert into pji_data_validation select * from pji_launch_incr where incr_type like 'VAL%';
2241: DELETE FROM PJI_LAUNCH_INCR where incr_type like 'VAL%';
2242:
2243: end if ; -- if run_type = 'VALIDATION'
2244: **/
2245:
2296:
2297: BEGIN
2298:
2299: -- NOTES :
2300: --- This procedure created rows into the table pji_launch_incr
2301: --- Following type of rows are created
2302: -- INCR_TYPE :
2303: --- PRG_BASE :-> These are PRG_CHANGE events
2304: -- PROJ_BASE :-> These are proejcts that has incremental data
2313: -- PROJ_PARENT :-> These are the rows that parents of the rows
2314: -- of type PROJ_PRG.
2315: --
2316: --
2317: DELETE FROM PJI_LAUNCH_INCR;
2318: --
2319: --
2320: COMMIT;
2321:
2321:
2322: --
2323: -- INSERT 001
2324: --
2325: INSERT INTO PJI_LAUNCH_INCR
2326: (incr_type, prg_group , project_id, prg_level )
2327: SELECT /*+ ordered use_nl(log ver ) index(ver PA_PROJ_ELEMENT_VERSIONS_N5) */
2328: DISTINCT
2329: 'PRG_BASE' incr_type ,
2347: --
2348: -- INSERT 002
2349: --
2350: --
2351: INSERT INTO PJI_LAUNCH_INCR
2352: (incr_type, prg_group , project_id , prg_level)
2353: SELECT
2354: DISTINCT
2355: 'PROJ_BASE',
2362: log.EVENT_TYPE = 'PRG_CHANGE' AND
2363: log.EVENT_OBJECT = - 1
2364: AND NOT EXISTS
2365: (SELECT 'x' FROM
2366: PJI_LAUNCH_INCR grp2
2367: WHERE grp2.incr_type = 'PROJ_BASE'
2368: AND grp2.prg_group = - 1
2369: AND grp2.project_id = to_number(log.attribute1) )
2370: and exists ( select 'x' from
2375: --
2376: -- INSERT 003
2377: --
2378: --
2379: INSERT INTO PJI_LAUNCH_INCR
2380: (incr_type, prg_group , project_id, prg_level )
2381: SELECT
2382: DISTINCT
2383: 'PROJ_BASE',
2389: WHERE
2390: log.EVENT_TYPE IN ('RBS_ASSOC', 'RBS_PRG' )
2391: AND NOT EXISTS
2392: (SELECT 'x' FROM
2393: PJI_LAUNCH_INCR grp2
2394: WHERE grp2.incr_type = 'PROJ_BASE'
2395: AND grp2.prg_group = - 1
2396: AND grp2.project_id = to_number(log.attribute1) )
2397: and exists ( select 'x' from
2402: --
2403: -- INSERT 003
2404: --
2405: --
2406: INSERT INTO PJI_LAUNCH_INCR
2407: (incr_type, prg_group , project_id , prg_level)
2408: SELECT
2409: DISTINCT
2410: 'PROJ_BASE',
2418: log.EVENT_TYPE = 'RBS_PUSH' AND
2419: asg.RBS_VERSION_ID IN (log.EVENT_OBJECT, log.ATTRIBUTE2)
2420: AND NOT EXISTS
2421: (SELECT 'x' FROM
2422: PJI_LAUNCH_INCR grp2
2423: WHERE grp2.incr_type = 'PROJ_BASE'
2424: AND grp2.prg_group = - 1
2425: AND grp2.project_id = asg.project_id )
2426: and exists ( select 'x' from
2431: --
2432: -- INSERT 004
2433: --
2434: --
2435: INSERT INTO PJI_LAUNCH_INCR
2436: (incr_type, prg_group , project_id , prg_level)
2437: SELECT
2438: DISTINCT
2439: 'PROJ_BASE',
2447: log.EVENT_TYPE = 'RBS_DELETE' AND
2448: asg.RBS_VERSION_ID = log.EVENT_OBJECT
2449: AND NOT EXISTS
2450: (SELECT 'x' FROM
2451: PJI_LAUNCH_INCR grp2
2452: WHERE grp2.incr_type = 'PROJ_BASE'
2453: AND grp2.prg_group = - 1
2454: AND grp2.project_id = asg.project_id )
2455: and exists ( select 'x' from
2461: --
2462: --
2463: /* Not required. based on the reply from Kranthi
2464:
2465: insert into PJI_LAUNCH_INCR
2466: ( incr_type, prg_group , project_id )
2467: select
2468: distinct
2469: 'PROJ_BASE',
2472: from
2473: pji_fm_extr_plan_lines a1
2474: where not exists
2475: ( select 'x' from
2476: PJI_LAUNCH_INCR grp2
2477: where grp2.incr_type = 'PROJ_BASE'
2478: and grp2.prg_group = -1
2479: and grp2.project_id = a1.project_id );
2480:
2483: --
2484: -- INSERT 006
2485: --
2486: --
2487: INSERT INTO PJI_LAUNCH_INCR
2488: (incr_type, prg_group , project_id , prg_level)
2489: SELECT
2490: DISTINCT
2491: 'PROJ_BASE',
2494: - 1
2495: FROM PJI_FM_AGGR_FIN7 a1
2496: WHERE NOT EXISTS
2497: (SELECT 'x' FROM
2498: PJI_LAUNCH_INCR grp2
2499: WHERE grp2.incr_type = 'PROJ_BASE'
2500: AND grp2.prg_group = - 1
2501: AND grp2.project_id = a1.project_id )
2502: and exists ( select 'x' from
2505: and nvl(p1.org_id,-1) = nvl(p_operating_unit,nvl(p1.org_id,-1)));
2506:
2507: -- INSERT 007
2508: --
2509: INSERT INTO PJI_LAUNCH_INCR
2510: (incr_type, prg_group , project_id , prg_level )
2511: SELECT
2512: DISTINCT
2513: 'PROJ_BASE',
2516: - 1
2517: FROM PJI_FM_AGGR_ACT4 a1
2518: WHERE NOT EXISTS
2519: (SELECT 'x' FROM
2520: PJI_LAUNCH_INCR grp2
2521: WHERE grp2.incr_type = 'PROJ_BASE'
2522: AND grp2.prg_group = - 1
2523: AND grp2.project_id = a1.project_id )
2524: and exists ( select 'x' from
2525: pa_projects_all p1
2526: where p1.project_id = a1.project_id
2527: and nvl(p1.org_id,-1) = nvl(p_operating_unit,nvl(p1.org_id,-1)));
2528:
2529: INSERT INTO PJI_LAUNCH_INCR
2530: (incr_type, prg_group , project_id , prg_level )
2531: SELECT
2532: DISTINCT
2533: 'PROJ_BASE',
2538: WHERE budget_status_code = 'B' AND
2539: pji_summarized_flag = 'P' AND
2540: NOT EXISTS
2541: (SELECT 'x' FROM
2542: PJI_LAUNCH_INCR grp2
2543: WHERE grp2.incr_type = 'PROJ_BASE'
2544: AND grp2.prg_group = - 1
2545: AND grp2.project_id = a1.project_id )
2546: and exists ( select 'x' from
2551: --
2552: /*
2553: -- pji_fm_aggr_fin8 table is not required , based on the reply from shane.
2554:
2555: insert into PJI_LAUNCH_INCR
2556: ( incr_type, prg_group , project_id )
2557: select
2558: distinct
2559: 'PROJ_BASE',
2562: from pji_fm_aggr_fin8 a1
2563: where
2564: not exists
2565: ( select 'x' from
2566: PJI_LAUNCH_INCR grp2
2567: where grp2.incr_type = 'PROJ_BASE'
2568: and grp2.prg_group = -1
2569: and grp2.project_id = a1.project_id );
2570: */
2577: --
2578: --
2579: BEGIN
2580:
2581: UPDATE PJI_LAUNCH_INCR a
2582: SET a.INCR_TYPE = 'PROJ_PRG'
2583: WHERE a.INCR_TYPE = 'PROJ_BASE'
2584: AND EXISTS
2585: (SELECT /*+ ordered index(c PA_PROJ_ELEMENT_VERSIONS_N6) */ 'x'
2584: AND EXISTS
2585: (SELECT /*+ ordered index(c PA_PROJ_ELEMENT_VERSIONS_N6) */ 'x'
2586: FROM
2587: pa_proj_element_versions c,
2588: PJI_LAUNCH_INCR b
2589: WHERE a.project_id = c.project_id
2590: and c.object_type = 'PA_STRUCTURES'
2591: AND c.prg_group IS NOT NULL
2592: AND c.prg_group = b.prg_group
2602:
2603: -- Update the rows to working rows, since the corresponding parent need to be fetched.
2604: --
2605:
2606: UPDATE PJI_LAUNCH_INCR a
2607: SET a.INCR_TYPE = 'PROJ_WRK'
2608: WHERE a.INCR_TYPE = 'PROJ_BASE' and
2609: exists
2610: ( select 'x'
2625: SELECT /*+ ordered index(a PA_PROJ_ELEMENT_VERSIONS_N6) index(b PA_OBJECT_RELATIONSHIPS_N4) */
2626: DISTINCT
2627: a0.project_id child_proj_id , a.prg_level child_prg_level,
2628: c.project_id parent_proj_id , c.prg_group , c.prg_level parent_prg_level
2629: FROM pji_launch_incr a0,
2630: pa_proj_element_versions a,
2631: pa_object_relationships b,
2632: pa_proj_element_versions c
2633: WHERE a0.incr_type in ( 'PROJ_WRK', 'PROJ_WRK_NEW' )
2641: ) LOOP
2642:
2643: l_prg_parent := 1;
2644:
2645: UPDATE PJI_LAUNCH_INCR
2646: SET prg_group = PRG_PARENT_NODE.prg_group,
2647: incr_type = decode(incr_type,'PROJ_WRK','PROJ_B_PARENT','PROJ_PARENT'),
2648: prg_level = PRG_PARENT_NODE.child_prg_level
2649: WHERE project_id = PRG_PARENT_NODE.child_proj_id
2648: prg_level = PRG_PARENT_NODE.child_prg_level
2649: WHERE project_id = PRG_PARENT_NODE.child_proj_id
2650: AND incr_type in ( 'PROJ_WRK', 'PROJ_WRK_NEW' );
2651:
2652: INSERT INTO PJI_LAUNCH_INCR a
2653: (a.incr_type, a.prg_group , a.project_id , a.prg_level)
2654: select
2655: 'PROJ_WRK_NEW',
2656: PRG_PARENT_NODE.prg_group,
2659: from dual
2660: where not exists
2661: (
2662: select 'x'
2663: from PJI_LAUNCH_INCR b
2664: where PRG_PARENT_NODE.parent_proj_id = b.project_id
2665: and PRG_PARENT_NODE.prg_group = b.prg_group
2666: and PRG_PARENT_NODE.parent_prg_level = b.prg_level
2667: and b.incr_type in ('PROJ_PARENT','PROJ_B_PARENT','PROJ_WRK_NEW')
2676: l_prg_count := l_prg_count+1;
2677:
2678: END LOOP;
2679:
2680: UPDATE PJI_LAUNCH_INCR
2681: SET incr_type = decode(incr_type,'PROJ_WRK','PROJ_BASE','PROJ_PARENT')
2682: WHERE incr_type in ( 'PROJ_WRK', 'PROJ_WRK_NEW' );
2683:
2684:
2688: -- INSERT 008
2689: --
2690: --
2691: --
2692: INSERT INTO PJI_LAUNCH_INCR
2693: (incr_type, prg_group , project_id ,prg_level)
2694: SELECT /*+ ordered index(ver PA_PROJ_ELEMENT_VERSIONS_N6) */
2695: DISTINCT
2696: 'PROJ_BASE_MAP' incr_type ,
2704: ver.object_type = 'PA_STRUCTURES' AND
2705: ver.project_id = map1.project_id ;
2706: --AND
2707: -- NOT EXISTS (
2708: -- SELECT 'x' FROM PJI_LAUNCH_INCR grp2
2709: -- WHERE grp2.prg_group = nvl(ver.prg_group,-1) AND grp2.incr_type = 'PRG_BASE' AND grp2.project_id = - 1) and
2710: -- NOT EXISTS (
2711: -- SELECT 'x' FROM PJI_LAUNCH_INCR grp3
2712: -- WHERE grp3.project_id = map1.project_id AND grp3.incr_type like 'PROJ%' ) ;
2707: -- NOT EXISTS (
2708: -- SELECT 'x' FROM PJI_LAUNCH_INCR grp2
2709: -- WHERE grp2.prg_group = nvl(ver.prg_group,-1) AND grp2.incr_type = 'PRG_BASE' AND grp2.project_id = - 1) and
2710: -- NOT EXISTS (
2711: -- SELECT 'x' FROM PJI_LAUNCH_INCR grp3
2712: -- WHERE grp3.project_id = map1.project_id AND grp3.incr_type like 'PROJ%' ) ;
2713: --
2714: --
2715: --
2713: --
2714: --
2715: --
2716:
2717: UPDATE PJI_LAUNCH_INCR a
2718: set a.prg_group = -2
2719: where incr_type = 'PROJ_BASE_MAP'
2720: and exists
2721: (
2718: set a.prg_group = -2
2719: where incr_type = 'PROJ_BASE_MAP'
2720: and exists
2721: (
2722: SELECT 'x' FROM PJI_LAUNCH_INCR grp2
2723: WHERE grp2.prg_group = nvl(a.prg_group,-1) AND grp2.incr_type = 'PRG_BASE' AND grp2.project_id = - 1) ;
2724:
2725: /** New Changes July-9th END */
2726:
2733: --
2734: --
2735: -- INSERT 009
2736: --
2737: -- INSERT INTO PJI_LAUNCH_INCR
2738: -- (incr_type, prg_group , project_id )
2739: -- SELECT /*+ ordered index(ver PA_PROJ_ELEMENT_VERSIONS_N3) */
2740: -- DISTINCT
2741: -- 'PRG_BASE' incr_type ,
2741: -- 'PRG_BASE' incr_type ,
2742: -- ver.PRG_GROUP ,
2743: -- - 1 project_id
2744: -- FROM
2745: -- PJI_LAUNCH_INCR grp ,
2746: -- PA_PROJ_ELEMENT_VERSIONS ver
2747: -- WHERE
2748: -- ver.object_type = 'PA_STRUCTURES' AND
2749: -- grp.incr_type = 'PROJ_BASE' AND
2750: -- grp.prg_group = - 1 AND
2751: -- ver.project_id = grp.project_id AND
2752: -- ver.prg_group IS NOT NULL AND
2753: -- NOT EXISTS (
2754: -- SELECT 'x' FROM PJI_LAUNCH_INCR grp2
2755: -- WHERE grp2.prg_group = ver.prg_group AND grp2.incr_type = 'PRG_BASE' AND grp2.project_id = - 1) ;
2756: ----
2757: ----
2758: ----