[Home] [Help]
234: ) lp_flag
235: FROM
236: pji_pjp_proj_batch_map map,
237: pji_pjp_rbs_header rhd,
238: pa_budget_versions bv,
239: pa_proj_fp_options fpo,
240: pji_pa_proj_events_log pel,
241: pa_projects_all ppa,
242: pa_rbs_versions_b rvb --Added for bug#5728852
296: ) lp_flag
297: FROM
298: PJI_PJP_PROJ_BATCH_MAP map,
299: PJI_PJP_RBS_HEADER rhd,
300: PA_BUDGET_VERSIONS bv,
301: PA_PROJ_FP_OPTIONS fpo,
302: PJI_PA_PROJ_EVENTS_LOG pel,
303: PA_RBS_PRJ_ASSIGNMENTS rpa,
304: PA_PROJECTS_ALL ppa,
358: , 'Y' lp_flag
359: FROM
360: PJI_PJP_PROJ_BATCH_MAP map,
361: PJI_PJP_RBS_HEADER rhd,
362: PA_BUDGET_VERSIONS bv,
363: PA_PROJ_FP_OPTIONS fpo,
364: PJI_PA_PROJ_EVENTS_LOG pel,
365: PA_RBS_PRJ_ASSIGNMENTS rpa,
366: PA_PROJECTS_ALL ppa,
496: ) lp_flag
497: FROM
498: pji_pjp_proj_batch_map map
499: , pa_projects_all ppa
500: , pa_budget_versions bv
501: , pa_proj_fp_options fpo
502: WHERE 1=1
503: AND ppa.project_id = bv.project_id
504: AND bv.version_type is not NULL -- COST, REVENUE, etc. Should not be null.
580: ) lp_flag
581: FROM
582: pji_pjp_proj_batch_map map
583: , pa_projects_all ppa -- @pjdev115 ppa
584: , pa_budget_versions bv -- @pjdev115 bv
585: , pa_proj_fp_options fpo -- @pjdev115 fpo
586: , PA_RBS_PRJ_ASSIGNMENTS rpa
587: WHERE 1=1
588: AND ppa.project_id = bv.project_id
690: , 'Y' lp_flag
691: FROM
692: pji_pjp_proj_batch_map map,
693: PA_PROJECTS_ALL ppa,
694: PA_BUDGET_VERSIONS pbv,
695: pji_pjp_wbs_header head,
696: PA_PROJ_ELEM_VER_STRUCTURE ppevs,
697: PA_PROJ_FP_OPTIONS fpo
698: WHERE
988: AND worker_id = g_worker_id;
989:
990:
991: BEGIN
992: select /*+ ordered index(bv PA_BUDGET_VERSIONS_U2) */
993: 'Y'
994: into
995: l_dangl_exists
996: from
994: into
995: l_dangl_exists
996: from
997: PJI_PJP_PROJ_BATCH_MAP map,
998: PA_BUDGET_VERSIONS bv
999: where
1000: map.WORKER_ID = g_worker_id and
1001: bv.PROJECT_ID = map.PROJECT_ID and
1002: nvl(bv.WP_VERSION_FLAG, 'N') = 'N' and
1279: (project_id, plan_version_id, plan_type_id)
1280: SELECT
1281: DISTINCT bv.project_id, bv.budget_version_id, bv.fin_plan_type_id
1282: FROM pji_pjp_proj_batch_map map
1283: , PA_BUDGET_VERSIONS bv
1284: WHERE
1285: map.worker_id = g_worker_id
1286: AND bv.project_id = map.project_id
1287: AND NVL(bv.wp_version_flag, 'N') = 'N'
1426: SELECT /*+ ordered */
1427: DISTINCT hd9.project_id, hd9.plan_version_id, hd9.plan_type_id
1428: FROM pji_pjp_proj_batch_map map
1429: , pji_pa_proj_events_log ev
1430: -- , PA_BUDGET_VERSIONS bv
1431: , pji_xbs_denorm den
1432: , pji_pjp_wbs_header wh1 -- sup
1433: , pji_pjp_wbs_header hd9 -- SUb!!
1434: WHERE
1491: INSERT INTO pji_fm_extr_plnver3_t
1492: (project_id, plan_version_id, plan_type_id)
1493: SELECT DISTINCT hd9.project_id, hd9.plan_version_id, hd9.plan_type_id
1494: FROM pji_pjp_proj_batch_map map
1495: , PA_BUDGET_VERSIONS bv
1496: , pji_pjp_wbs_header hd9 -- SUP
1497: WHERE
1498: map.worker_id = g_worker_id
1499: AND hd9.project_id = map.project_id
1516: SELECT DISTINCT hd9.project_id, hd9.plan_version_id, hd9.plan_type_id
1517: FROM pji_xbs_denorm den
1518: , pji_pjp_wbs_header hd1 -- SUB
1519: , pji_pjp_proj_batch_map map
1520: , pa_budget_versions bv
1521: , pji_pjp_wbs_header hd9 -- SUP
1522: WHERE
1523: den.struct_version_id IS NULL
1524: AND map.project_id = bv.project_id
1722: , 'N')
1723: , 'N'
1724: ) lp_flag
1725: FROM
1726: pa_budget_versions bv -- @pjdev115 bv
1727: , pa_proj_fp_options fpo -- @pjdev115 fpo
1728: , pa_projects_all ppa -- @pjdev115 ppa
1729: WHERE 1=1
1730: AND ppa.project_id = bv.project_id
1910: , 'N')
1911: , 'N'
1912: ) lp_flag
1913: FROM
1914: pa_budget_versions bv -- @pjdev115 bv
1915: , pa_proj_fp_options fpo -- @pjdev115 fpo
1916: , pa_projects_all ppa -- @pjdev115 ppa
1917: WHERE 1=1
1918: AND ppa.project_id = bv.project_id
1987: , 'N')
1988: , 'N'
1989: ) lp_flag
1990: FROM
1991: pa_budget_versions bv -- @pjdev115 bv
1992: , pa_proj_fp_options fpo -- @pjdev115 fpo
1993: , pa_projects_all ppa -- @pjdev115 ppa
1994: , PA_RBS_PRJ_ASSIGNMENTS rpa
1995: WHERE 1=1
2071: , 'N')
2072: , 'N'
2073: ) lp_flag
2074: FROM
2075: pa_budget_versions bv -- @pjdev115 bv
2076: , pa_proj_fp_options fpo -- @pjdev115 fpo
2077: , pa_projects_all ppa -- @pjdev115 ppa
2078: , pji_pjp_proj_batch_map map
2079: WHERE 1=1
2126: , 'N')
2127: , 'N'
2128: ) lp_flag
2129: FROM
2130: pa_budget_versions bv
2131: , pa_proj_fp_options fpo
2132: , pji_pjp_proj_batch_map map
2133: , ( SELECT -3 plan_version_id FROM dual
2134: UNION ALL
2225: , 'N')
2226: , 'N'
2227: ) lp_flag
2228: FROM
2229: pa_budget_versions bv -- @pjdev115 bv
2230: , pa_proj_fp_options fpo -- @pjdev115 fpo
2231: , pa_projects_all ppa -- @pjdev115 ppa
2232: , PA_RBS_PRJ_ASSIGNMENTS rpa
2233: , pji_pjp_proj_batch_map map
2282: , 'N')
2283: , 'N'
2284: ) lp_flag
2285: FROM
2286: pa_budget_versions bv -- @pjdev115 bv
2287: , pa_proj_fp_options fpo -- @pjdev115 fpo
2288: -- , pa_projects_all ppa -- @pjdev115 ppa
2289: , PA_RBS_PRJ_ASSIGNMENTS rpa
2290: , pji_pjp_proj_batch_map map
2345: , 'N')
2346: , 'N'
2347: ) lp_flag
2348: FROM
2349: pa_budget_versions bv -- @pjdev115 bv
2350: , pa_proj_fp_options fpo -- @pjdev115 fpo
2351: , pa_projects_all ppa -- @pjdev115 ppa
2352: , PA_RBS_PRJ_ASSIGNMENTS rpa
2353: , pji_pjp_proj_batch_map map
2408: , 'N')
2409: , 'N'
2410: ) lp_flag
2411: FROM
2412: pa_budget_versions bv
2413: , pa_proj_fp_options fpo
2414: , PJI_PA_PROJ_EVENTS_LOG ev
2415: , (SELECT -3 plan_version_id FROM DUAL
2416: UNION ALL
2472: , 'N')
2473: , 'N'
2474: ) lp_flag
2475: FROM
2476: pa_budget_versions bv
2477: , pa_proj_fp_options fpo
2478: , pji_pa_proj_events_log ev
2479: , pji_pjp_wbs_header hd1 -- sup
2480: , pji_pjp_wbs_header hd2 -- sub
2543: , 'N')
2544: , 'N'
2545: ) lp_flag
2546: FROM
2547: pa_budget_versions bv
2548: , pa_proj_fp_options fpo
2549: , PJI_PA_PROJ_EVENTS_LOG ev
2550: , pji_pjp_wbs_header hd1 -- sup
2551: , pji_pjp_wbs_header hd2 -- sub
2623: , NVL(bv.wp_version_flag, 'N') wpf
2624: , bv.budget_status_code bsc
2625: FROM pa_proj_fp_options fpo
2626: , pa_rbs_prj_assignments rpa
2627: , pa_budget_versions bv
2628: , PJI_PJP_PROJ_BATCH_MAP map
2629: WHERE 1=1
2630: AND fpo.project_id (+)= rpa.project_id
2631: AND fpo.rbs_version_id (+)= rpa.rbs_version_id
2891: , 'N')
2892: , 'N'
2893: ) lp_flag
2894: FROM
2895: pa_budget_versions bv -- @pjdev115 bv
2896: , pa_proj_fp_options fpo -- @pjdev115 fpo
2897: , pa_projects_all ppa -- @pjdev115 ppa
2898: WHERE 1=1
2899: AND ppa.project_id = bv.project_id
2970: , 'N')
2971: , 'N'
2972: ) lp_flag
2973: FROM
2974: pa_budget_versions bv -- @pjdev115 bv
2975: , pa_proj_fp_options fpo -- @pjdev115 fpo
2976: , pa_projects_all ppa -- @pjdev115 ppa
2977: WHERE 1=1
2978: AND ppa.project_id = bv.project_id
3051: , 'N')
3052: , 'N'
3053: ) lp_flag
3054: FROM
3055: pa_budget_versions bv -- @pjdev115 bv
3056: , pa_proj_fp_options fpo -- @pjdev115 fpo
3057: , pa_projects_all ppa -- @pjdev115 ppa
3058: , PA_RBS_PRJ_ASSIGNMENTS rpa
3059: WHERE 1=1
4738: , g_default_prg_level
4739: , rl.PLAN_TYPE_CODE
4740: FROM
4741: pji_fp_xbs_accum_f rl
4742: , pa_budget_versions bv -- @pjdev115 bv
4743: , pa_proj_fp_options fpo -- @pjdev115 fpo
4744: WHERE
4745: rl.project_id = bv.project_id
4746: AND bv.version_type IS NOT NULL
4818: , bv.original_flag original_flag
4819: , bv.current_original_flag current_original_flag
4820: , DECODE(bv.baselined_date, NULL, 'N', 'Y') baselined_flag
4821: FROM PJI_FM_EXTR_PLAN_LINES epl
4822: , pa_budget_versions bv
4823: , pa_proj_fp_options fpo
4824: WHERE
4825: epl.plan_version_id = bv.budget_version_id
4826: AND fpo.project_id = bv.project_id
6515:
6516: PROCEDURE MARK_DANGLING_PLAN_VERSIONS IS
6517: BEGIN
6518:
6519: UPDATE pa_budget_versions bv
6520: SET pji_summarized_flag = 'P',
6521: record_version_number = nvl(record_version_number,0)+1
6522: WHERE budget_version_id IN (
6523: SELECT plan_version_id
6533: ) b
6534: WHERE dangling > 2
6535: );
6536:
6537: UPDATE pa_budget_versions bv
6538: SET pji_summarized_flag = 'Y',
6539: record_version_number = nvl(record_version_number,0)+1
6540: WHERE budget_version_id IN (
6541: SELECT plan_version_id
7395: PROCEDURE MARK_EXTRACTED_PLANS(p_slice_type IN VARCHAR2) IS
7396: BEGIN
7397:
7398: --
7399: -- On PA_BUDGET_VERSIONS, the PJI_SUMMARIZED_FLAG can have four values:
7400: -- 'N' the plan version has not been summarized
7401: -- 'P' the plan version has been partially summarized due to dangling currency or calendar records
7402: -- 'Y' the plan version has been fully summarized in Project Performance and
7403: -- NULL the plan version has been fully summarized in both PJI and Project Performance.
7403: -- NULL the plan version has been fully summarized in both PJI and Project Performance.
7404: --
7405: -- Note that, in PJI data extraction, data that existed before PJI was installed
7406: -- has PJI_SUMMARIZED_FLAG = null and new transactions are inserted with value 'N'.
7407: -- A similar situation does not occur in PA_BUDGET_VERSIONS. During the upgrade process
7408: -- will pull necessary data from PA_BUDGET_VERSIONS and set PJI_SUMMARIZATION_FLAG to 'P', 'N'
7409: -- or 'Y' accordingly. New versions will be inserted with value 'N'.)
7410: --
7411:
7404: --
7405: -- Note that, in PJI data extraction, data that existed before PJI was installed
7406: -- has PJI_SUMMARIZED_FLAG = null and new transactions are inserted with value 'N'.
7407: -- A similar situation does not occur in PA_BUDGET_VERSIONS. During the upgrade process
7408: -- will pull necessary data from PA_BUDGET_VERSIONS and set PJI_SUMMARIZATION_FLAG to 'P', 'N'
7409: -- or 'Y' accordingly. New versions will be inserted with value 'N'.)
7410: --
7411:
7412: IF (p_slice_type = 'PRI') THEN
7409: -- or 'Y' accordingly. New versions will be inserted with value 'N'.)
7410: --
7411:
7412: IF (p_slice_type = 'PRI') THEN
7413: UPDATE /*+ index(bv,pa_budget_versions_u1) */
7414: pa_budget_versions bv
7415: SET pji_summarized_flag =
7416: DECODE(wp_version_flag
7417: , 'Y', 'Y'
7410: --
7411:
7412: IF (p_slice_type = 'PRI') THEN
7413: UPDATE /*+ index(bv,pa_budget_versions_u1) */
7414: pa_budget_versions bv
7415: SET pji_summarized_flag =
7416: DECODE(wp_version_flag
7417: , 'Y', 'Y'
7418: , DECODE(budget_status_code
7437: OR pjp1.rate_dangling_flag IS NOT NULL )
7438: )
7439: AND worker_id = g_worker_id ;
7440:
7441: UPDATE /*+ index( bv , pa_budget_versions_u1 ) */
7442: pa_budget_versions bv
7443: SET pji_summarized_flag = 'Y',
7444: record_version_number=nvl(record_version_number,0)+1
7445: WHERE budget_version_id IN (SELECT plan_version_id
7438: )
7439: AND worker_id = g_worker_id ;
7440:
7441: UPDATE /*+ index( bv , pa_budget_versions_u1 ) */
7442: pa_budget_versions bv
7443: SET pji_summarized_flag = 'Y',
7444: record_version_number=nvl(record_version_number,0)+1
7445: WHERE budget_version_id IN (SELECT plan_version_id
7446: FROM PJI_FM_EXTR_PLNVER4 ver3
7446: FROM PJI_FM_EXTR_PLNVER4 ver3
7447: WHERE ver3.worker_id=g_worker_id
7448: AND ver3.time_dangling_flag IS NULL);
7449:
7450: UPDATE /*+ index( bv , pa_budget_versions_u1 ) */
7451: pa_budget_versions bv
7452: SET pji_summarized_flag = 'P',
7453: record_version_number=nvl(record_version_number,0)+1
7454: WHERE budget_version_id IN (SELECT plan_version_id
7447: WHERE ver3.worker_id=g_worker_id
7448: AND ver3.time_dangling_flag IS NULL);
7449:
7450: UPDATE /*+ index( bv , pa_budget_versions_u1 ) */
7451: pa_budget_versions bv
7452: SET pji_summarized_flag = 'P',
7453: record_version_number=nvl(record_version_number,0)+1
7454: WHERE budget_version_id IN (SELECT plan_version_id
7455: FROM PJI_FM_EXTR_PLNVER4 ver3