[Home] [Help]
109:
110: CURSOR plan_ver_cur
111: IS
112: select plan_version_id ver_id
113: from PJI_FM_EXTR_PLNVER4
114: where worker_id = g_worker_id
115: UNION
116: select plan_version_id ver_id
117: from PJI_FM_EXTR_PLNVER3_T;
122: l_resource_list_id_tbl num_tbl;
123:
124: CURSOR res_list IS
125: SELECT /*+ ordered */ b.plan_version_id
126: FROM PJI_FM_EXTR_PLNVER4 b, pa_proj_fp_options a, pa_resource_lists_all_bg c
127: WHERE b.project_id = a.project_id
128: AND b.PLAN_TYPE_ID = a.FIN_PLAN_TYPE_ID
129: AND b.plan_version_id = a.fin_plan_version_id
130: and decode(b.plan_type_code, 'C', a.cost_resource_list_id, 'R', a.revenue_resource_list_id, 'A', a.all_resource_list_id) = c.resource_list_id
253: l_plan_type_id := NULL;
254: l_rbs_version_id := p_rbs_version_id;
255:
256:
257: INSERT INTO PJI_FM_EXTR_PLNVER4
258: (
259: WORKER_ID ,
260: PROJECT_ID ,
261: PLAN_VERSION_ID ,
515: end;
516:
517:
518:
519: INSERT INTO PJI_FM_EXTR_PLNVER4
520: (
521: WORKER_ID ,
522: PROJECT_ID ,
523: PLAN_VERSION_ID ,
720:
721: ELSIF (p_context IN ( 'INCREMENTAL', 'FULL')) THEN -- Workplans only.
722:
723:
724: INSERT INTO PJI_FM_EXTR_PLNVER4
725: (
726: WORKER_ID ,
727: PROJECT_ID ,
728: PLAN_VERSION_ID ,
835: l_cb_ver := -3;
836: l_co_ver := -4;
837: END IF;
838:
839: INSERT INTO PJI_FM_EXTR_PLNVER4
840: (
841: WORKER_ID ,
842: PROJECT_ID ,
843: PLAN_VERSION_ID ,
952: )
953: WHERE (project_id, plan_version_id, NVL(plan_type_id, -1), plan_type_code, rbs_struct_version_id)
954: NOT IN
955: (SELECT project_id, plan_version_id, NVL(plan_type_id, -1), plan_type_code, rbs_struct_version_id
956: FROM pji_fm_extr_plnver4
957: WHERE worker_id = g_worker_id
958: AND plan_version_id in (-1, -3, -4));
959:
960: l_num_rows := l_num_rows + SQL%ROWCOUNT; -- Bug#5208250
1840: IF ( p_slice_type = 'PRI') THEN
1841:
1842: if (l_up_process_flag = 'Y') then /* Added for bug 8708651 */
1843:
1844: INSERT INTO PJI_FM_EXTR_PLNVER4
1845: (
1846: WORKER_ID ,
1847: PROJECT_ID ,
1848: PLAN_VERSION_ID ,
2012: ;
2013:
2014: else
2015:
2016: INSERT INTO PJI_FM_EXTR_PLNVER4
2017: (
2018: WORKER_ID ,
2019: PROJECT_ID ,
2020: PLAN_VERSION_ID ,
2088: --#bug 5356051
2089: If l_prg_exists = 'Y' then --Programs exist, need to insert -3/-4 for programs as well
2090:
2091:
2092: INSERT INTO PJI_FM_EXTR_PLNVER4
2093: (
2094: WORKER_ID ,
2095: PROJECT_ID ,
2096: PLAN_VERSION_ID ,
2129: , DECODE(cbco.plan_version_id, -3, 'Y', 'N') baselined_flag
2130: , 'Y' SECONDARY_RBS_FLAG
2131: , 'Y' lp_flag
2132: FROM
2133: pji_fm_extr_plnver4 hd2 -- sub
2134: , pji_xbs_denorm den
2135: , ( SELECT -3 plan_version_id FROM dual
2136: UNION ALL
2137: SELECT -4 plan_version_id FROM dual ) cbco
2145: AND NVL(den.relationship_type, 'WF') IN ('LF', 'WF');
2146:
2147: elsif l_prg_exists = 'N' then
2148:
2149: INSERT INTO PJI_FM_EXTR_PLNVER4
2150: (
2151: WORKER_ID ,
2152: PROJECT_ID ,
2153: PLAN_VERSION_ID ,
2186: , DECODE(cbco.plan_version_id, -3, 'Y', 'N') baselined_flag
2187: , 'Y' SECONDARY_RBS_FLAG
2188: , 'Y' lp_flag
2189: FROM
2190: pji_fm_extr_plnver4 hd2 -- sub
2191: , ( SELECT -3 plan_version_id FROM dual
2192: UNION ALL
2193: SELECT -4 plan_version_id FROM dual ) cbco
2194: WHERE
2201:
2202:
2203: ELSIF ( p_slice_type = 'SEC') THEN
2204:
2205: INSERT INTO PJI_FM_EXTR_PLNVER4
2206: (
2207: WORKER_ID ,
2208: PROJECT_ID ,
2209: PLAN_VERSION_ID ,
2277:
2278:
2279: ELSIF ( p_slice_type = 'SECRBS') THEN
2280:
2281: INSERT INTO PJI_FM_EXTR_PLNVER4
2282: (
2283: WORKER_ID ,
2284: PROJECT_ID ,
2285: PLAN_VERSION_ID ,
2364: ELSIF ( p_slice_type = 'SEC_PROJ') THEN
2365:
2366: if (upper(nvl(l_profile_check, 'N')) = 'Y') then /* Added for bug 8708651 */
2367:
2368: INSERT INTO PJI_FM_EXTR_PLNVER4
2369: (
2370: WORKER_ID ,
2371: PROJECT_ID ,
2372: PLAN_VERSION_ID ,
2551: AND NVL(bv.wp_version_flag, 'N') = 'N';
2552:
2553: else
2554:
2555: INSERT INTO PJI_FM_EXTR_PLNVER4
2556: (
2557: WORKER_ID ,
2558: PROJECT_ID ,
2559: PLAN_VERSION_ID ,
2689: ELSIF ( p_slice_type = 'SECRBS_PROJ') THEN
2690:
2691: if (upper(nvl(l_profile_check, 'N')) = 'Y') then /* Added for bug 8708651 */
2692:
2693: INSERT INTO PJI_FM_EXTR_PLNVER4
2694: (
2695: WORKER_ID ,
2696: PROJECT_ID ,
2697: PLAN_VERSION_ID ,
3267: );
3268:
3269: else
3270:
3271: INSERT INTO PJI_FM_EXTR_PLNVER4
3272: (
3273: WORKER_ID ,
3274: PROJECT_ID ,
3275: PLAN_VERSION_ID ,
3794:
3795: --#bug 5356051
3796: If l_prg_exists = 'Y' then --Programs exist, need to insert -3/-4 for programs as well
3797:
3798: INSERT INTO PJI_FM_EXTR_PLNVER4
3799: (
3800: WORKER_ID ,
3801: PROJECT_ID ,
3802: PLAN_VERSION_ID ,
3835: , DECODE(cbco.plan_version_id, -3, 'Y', 'N') baselined_flag
3836: , 'Y' SECONDARY_RBS_FLAG
3837: , 'Y' lp_flag
3838: FROM
3839: pji_fm_extr_plnver4 hd2 -- sub
3840: , pji_xbs_denorm den
3841: , ( SELECT -3 plan_version_id FROM dual
3842: UNION ALL
3843: SELECT -4 plan_version_id FROM dual ) cbco
3851: AND NVL(den.relationship_type, 'WF') IN ('LF', 'WF')
3852: /* Added for bug 13030627 */
3853: AND NOT EXISTS (
3854: SELECT 1
3855: FROM pji_fm_extr_plnver4 ver3
3856: WHERE den.sup_project_id = ver3.project_id
3857: AND cbco.plan_version_id = ver3.plan_version_id
3858: AND hd2.plan_type_id = ver3.plan_type_id
3859: AND hd2.plan_type_code = ver3.plan_type_code
3860: AND hd2.rbs_struct_version_id = ver3.rbs_struct_version_id
3861: AND worker_id = g_worker_id);
3862: /* AND (den.sup_project_id, cbco.plan_version_id, hd2.plan_type_id,hd2.plan_type_code, hd2.rbs_struct_version_id) NOT IN
3863: ( SELECT project_id, plan_version_id, plan_type_id, plan_type_code,rbs_struct_version_id
3864: FROM pji_fm_extr_plnver4 ver3
3865: WHERE worker_id = g_worker_id ); Commented for bug 13030627 */
3866:
3867: elsif l_prg_exists = 'N' then
3868:
3865: WHERE worker_id = g_worker_id ); Commented for bug 13030627 */
3866:
3867: elsif l_prg_exists = 'N' then
3868:
3869: INSERT INTO PJI_FM_EXTR_PLNVER4
3870: (
3871: WORKER_ID ,
3872: PROJECT_ID ,
3873: PLAN_VERSION_ID ,
3906: , DECODE(cbco.plan_version_id, -3, 'Y', 'N') baselined_flag
3907: , 'Y' SECONDARY_RBS_FLAG
3908: , 'Y' lp_flag
3909: FROM
3910: pji_fm_extr_plnver4 hd2 -- sub
3911: , ( SELECT -3 plan_version_id FROM dual
3912: UNION ALL
3913: SELECT -4 plan_version_id FROM dual ) cbco
3914: WHERE hd2.baselined_flag = 'Y'
3917: AND hd2.worker_id = g_worker_id
3918: /* Added for bug 13030627 */
3919: AND NOT EXISTS (
3920: SELECT 1
3921: FROM pji_fm_extr_plnver4 ver3
3922: WHERE hd2.project_id = ver3.project_id
3923: AND cbco.plan_version_id = ver3.plan_version_id
3924: AND hd2.plan_type_id = ver3.plan_type_id
3925: AND hd2.plan_type_code = ver3.plan_type_code
3927: AND worker_id = g_worker_id);
3928: /* Commented for bug 13030627
3929: AND (hd2.project_id, cbco.plan_version_id, hd2.plan_type_id,hd2.plan_type_code, hd2.rbs_struct_version_id) NOT IN
3930: ( SELECT project_id, plan_version_id, plan_type_id, plan_type_code,rbs_struct_version_id
3931: FROM pji_fm_extr_plnver4 ver3
3932: WHERE worker_id = g_worker_id );
3933: */
3934: end if;
3935: --#bug 5356051
3974: IF (p_slice_type = 'PRI') THEN
3975:
3976: FOR I IN 1..p_fp_version_ids.COUNT LOOP
3977:
3978: INSERT INTO PJI_FM_EXTR_PLNVER4 ver3
3979: (
3980: WORKER_ID ,
3981: PROJECT_ID ,
3982: PLAN_VERSION_ID ,
4053: ELSIF (p_slice_type = 'SEC') THEN
4054:
4055: FOR I IN 1..p_fp_version_ids.COUNT LOOP
4056:
4057: INSERT INTO PJI_FM_EXTR_PLNVER4 ver3
4058: (
4059: WORKER_ID ,
4060: PROJECT_ID ,
4061: PLAN_VERSION_ID ,
4134: ELSIF (p_slice_type = 'SECRBS') THEN
4135:
4136: FOR I IN 1..p_fp_version_ids.COUNT LOOP
4137:
4138: INSERT INTO PJI_FM_EXTR_PLNVER4 ver3
4139: (
4140: WORKER_ID ,
4141: PROJECT_ID ,
4142: PLAN_VERSION_ID ,
4659: , ppa.carrying_out_organization_id project_organization_id
4660: , ver.plan_type_code /*4471527 */
4661: , ver.WP_FLAG
4662: FROM
4663: PJI_FM_EXTR_PLNVER4 ver
4664: , pa_resource_asSIGNments ra
4665: , PA_BUDGET_LINES bl
4666: , pa_projects_all ppa
4667: , PA_PROJ_ELEM_VER_STRUCTURE pevs
4784: l_count NUMBER := NULL;
4785:
4786: CURSOR c_lp_struct_ver IS
4787: SELECT ver.project_id, ver.wbs_struct_version_id
4788: FROM pji_fm_extr_plnver4 ver
4789: WHERE ver.wp_flag = 'Y'
4790: AND ver.lp_flag = 'Y'
4791: AND ver.worker_id = g_worker_id;
4792:
4843: DELETE FROM pji_fm_extr_plan_lines
4844: WHERE
4845: (PROJECT_ID, PLAN_VERSION_ID) IN
4846: (SELECT DISTINCT project_id, plan_version_id
4847: FROM pji_fm_extr_plnver4) AND
4848: ETC_QUANTITY IS NULL AND
4849: ETC_TXN_BURDENED_COST IS NULL AND
4850: ETC_PRJ_BURDENED_COST IS NULL AND
4851: ETC_PFC_BURDENED_COST IS NULL AND
5262: , ppa.carrying_out_organization_id project_organization_id
5263: , ver.plan_type_code
5264: , ver.time_phased_type_code
5265: FROM
5266: PJI_FM_EXTR_PLNVER4 ver
5267: , PA_RESOURCE_ASSIGNMENTS ra
5268: , PA_BUDGET_LINES bl
5269: , PA_PROJECTS_ALL ppa
5270: , PA_PROJ_ELEM_VER_STRUCTURE pevs
5335: , ppa.carrying_out_organization_id project_organization_id
5336: , ver.plan_type_code
5337: , ver.time_phased_type_code
5338: FROM
5339: PJI_FM_EXTR_PLNVER4 ver
5340: , PJI_FM_EXTR_PLAN_LINES bl
5341: , PA_PROJECTS_ALL ppa
5342: , PA_PROJ_ELEM_VER_STRUCTURE pevs
5343: WHERE 1=1
5461:
5462: DELETE FROM pji_fm_extr_plan_lines
5463: WHERE (project_id, plan_version_id) IN
5464: (SELECT ver.project_id, ver.plan_version_id
5465: FROM pji_fm_extr_plnver4 ver
5466: WHERE ver.worker_id = g_worker_id)
5467: AND txn_currency_code IS NOT NULL
5468: AND prj_currency_code IS NOT NULL
5469: AND pfc_currency_code IS NOT NULL;
5467: AND txn_currency_code IS NOT NULL
5468: AND prj_currency_code IS NOT NULL
5469: AND pfc_currency_code IS NOT NULL;
5470:
5471: --Bug 6047966. In case actuals alone are summarized, pji_fm_extr_plnver4
5472: --contains only -1 record. Hence that case should be handled.
5473: IF l_plan_ver_type_code ='ACTUAL' THEN
5474:
5475: DELETE FROM pji_fm_extr_plan_lines
5474:
5475: DELETE FROM pji_fm_extr_plan_lines
5476: WHERE (project_id) IN
5477: (SELECT ver.project_id
5478: FROM pji_fm_extr_plnver4 ver
5479: WHERE ver.worker_id = g_worker_id
5480: AND ver.plan_version_id=-1)
5481: AND txn_currency_code IS NOT NULL
5482: AND prj_currency_code IS NOT NULL
5956: BEGIN
5957:
5958: print_time('........POPULATE_PLN_VER_TABLE : Begin.' );
5959:
5960: INSERT INTO PJI_FM_EXTR_PLNVER4
5961: (
5962: WORKER_ID ,
5963: PROJECT_ID ,
5964: PLAN_VERSION_ID ,
6015:
6016: /*
6017: SELECT COUNT(1)
6018: INTO l_count
6019: FROM PJI_FM_EXTR_PLNVER4;
6020: */
6021:
6022: print_time('count is ... ' || SQL%ROWCOUNT );
6023:
6131: , TO_DATE(NULL)
6132: ) end_date
6133: FROM pa_budget_lines bl
6134: , pji_time_cal_period_v prd
6135: , PJI_FM_EXTR_PLNVER4 ver
6136: , pa_projects_all ppa
6137: ,PJI_ORG_EXTR_INFO inf
6138: WHERE 1=1
6139: AND bl.budget_version_id = ver.plan_version_id
6170: , ra.planning_end_date
6171: , TO_DATE(NULL)
6172: ) end_date
6173: FROM pa_resource_assignments ra
6174: , PJI_FM_EXTR_PLNVER4 ver
6175: , pa_projects_all ppa
6176: WHERE 1=1
6177: AND ra.budget_version_id = ver.plan_version_id
6178: AND ver.wp_flag = 'N'
6184:
6185: PJI_UTILS.g_max_roll_days := NULL; /*5155692 */
6186:
6187: UPDATE /*+ INDEX(prd,pji_time_cal_period_u1) index(rates, PJI_FM_AGGR_DLY_RATES_N1) */
6188: PJI_FM_EXTR_PLNVER4 ver
6189: SET rate_dangling_flag = 'Y'
6190: WHERE (project_id,plan_version_id) IN
6191: ( SELECT project_id,budget_version_id
6192: FROM PA_BUDGET_LINES bl,
6443: updating the plan versions as time dangling so that the plan will be marked as dangling. Not updating
6444: the existing records so that data for the primary calendar is not impacted because of issue in proration to
6445: other calendars */
6446:
6447: UPDATE PJI_FM_EXTR_PLNVER4
6448: SET time_dangling_flag='Y'
6449: WHERE wp_flag='N'
6450: AND baselined_flag = 'Y'
6451: AND worker_id = g_worker_id; -- Not raising any exception as we want the processing to happen properly.
8333: , fact1.line_type
8334: , g_default_prg_level
8335: , fact1.plan_type_code
8336: FROM pji_fp_aggr_pjp1 fact1
8337: , pji_fm_extr_plnver4 ver3
8338: WHERE
8339: fact1.RBS_AGGR_LEVEL = g_lowest_level
8340: AND fact1.worker_id = g_worker_id
8341: AND ver3.worker_id = g_worker_id
8431: , l_created_by
8432: , l_last_update_login
8433: , bv.plan_type_code
8434: FROM
8435: PJI_FM_EXTR_PLNVER4 bv,
8436: PA_RBS_PRJ_ASSIGNMENTS rpa,
8437: PJI_PJP_RBS_HEADER head
8438: WHERE bv.project_id = rpa.project_id
8439: AND bv.worker_id = g_worker_id
8504: , l_creation_date
8505: , l_created_by
8506: , l_last_update_login
8507: , ver.plan_type_code
8508: FROM PJI_FM_EXTR_PLNVER4 ver
8509: , PJI_PJP_WBS_HEADER whdr
8510: WHERE ver.worker_id = g_worker_id
8511: AND ver.plan_version_id = whdr.plan_version_id (+)
8512: AND ver.plan_type_code = whdr.plan_type_code(+) /*4471527 */
8652: SET pji_summarized_flag = 'P',
8653: record_version_number=nvl(record_version_number,0)+1
8654: WHERE budget_version_id IN
8655: ( SELECT DISTINCT plan_version_id
8656: FROM pji_fm_extr_plnver4
8657: WHERE worker_id = g_worker_id )
8658: AND (bv.current_flag = 'Y' or bv.current_original_flag = 'Y')
8659: AND nvl(bv.wp_version_flag,'Y') = 'N';
8660:
8663: SET pji_summarized_flag = 'Y',
8664: record_version_number=nvl(record_version_number,0)+1
8665: WHERE budget_version_id IN
8666: ( SELECT DISTINCT plan_version_id
8667: FROM pji_fm_extr_plnver4
8668: WHERE worker_id = g_worker_id )
8669: AND bv.current_flag = 'N'
8670: AND bv.current_original_flag = 'N';
8671:
8683: ),
8684: record_version_number=nvl(record_version_number,0)+1
8685: WHERE budget_version_id IN
8686: ( SELECT DISTINCT plan_version_id
8687: FROM pji_fm_extr_plnver4
8688: WHERE worker_id = g_worker_id );
8689:
8690: end if; -- Added for bug 9108728
8691: ELSE -- Secondary slice.
8690: end if; -- Added for bug 9108728
8691: ELSE -- Secondary slice.
8692:
8693: /* Added for bug 13897252 starts */
8694: UPDATE PJI_FM_EXTR_PLNVER4 ver3
8695: SET time_dangling_flag = 'Y'
8696: WHERE ver3.worker_id = g_worker_id
8697: AND EXISTS (SELECT /*+ index (pjp1, PJI_FP_AGGR_PJP1_N5) */ NULL
8698: FROM pji_fp_aggr_pjp1 pjp1
8709: pa_budget_versions bv
8710: SET pji_summarized_flag = 'Y',
8711: record_version_number=nvl(record_version_number,0)+1
8712: WHERE budget_version_id IN (SELECT plan_version_id
8713: FROM PJI_FM_EXTR_PLNVER4 ver3
8714: WHERE ver3.worker_id=g_worker_id
8715: AND ver3.time_dangling_flag IS NULL);
8716:
8717: UPDATE /*+ index( bv , pa_budget_versions_u1 ) */
8718: pa_budget_versions bv
8719: SET pji_summarized_flag = 'P',
8720: record_version_number=nvl(record_version_number,0)+1
8721: WHERE budget_version_id IN (SELECT plan_version_id
8722: FROM PJI_FM_EXTR_PLNVER4 ver3
8723: WHERE ver3.worker_id=g_worker_id
8724: AND ver3.time_dangling_flag IS NOT NULL);
8725:
8726: END IF;
8837: BEGIN
8838:
8839: print_time('.......CLEANUP_INTERIM_TABLES: Begin. ');
8840:
8841: DELETE FROM PJI_FM_EXTR_PLNVER4
8842: WHERE worker_id = g_worker_id ;
8843:
8844: print_time('.......ver3 rows deleted: #= ' || sql%rowcount);
8845:
9604: ,decode(ver.rate_dangling_flag,'Y',-1,1) rate2
9605: ,ver.plan_type_code plan_type_code /*4471527 */
9606: , ver.WP_FLAG
9607: FROM
9608: PJI_FM_EXTR_PLNVER4 ver
9609: , pa_resource_asSIGNments ra
9610: , PA_BUDGET_LINES bl
9611: , pa_projects_all ppa
9612: , PJI_ORG_EXTR_INFO oei
9916: ,decode(ver.rate_dangling_flag,'Y',-1,1) rate2
9917: , ver.plan_type_code plan_type_code /*4471527 */
9918: , ver.WP_FLAG
9919: FROM
9920: PJI_FM_EXTR_PLNVER4 ver
9921: , pa_resource_asSIGNments ra
9922: , PA_BUDGET_LINES bl
9923: , pa_projects_all ppa
9924: , pji_pjp_rbs_header rhdr
10793: ,decode(ver.rate_dangling_flag,'Y',-1,1) rate2
10794: , ver.plan_type_code plan_type_code /*4471527 */
10795: , ver.WP_FLAG
10796: FROM
10797: PJI_FM_EXTR_PLNVER4 ver
10798: , pa_resource_asSIGNments ra
10799: , PA_BUDGET_LINES bl
10800: , pa_projects_all ppa
10801: , PJI_ORG_EXTR_INFO oei
11087: ,decode(ver.rate_dangling_flag,'Y',-1,1) rate2
11088: , ver.plan_type_code plan_type_code /*4471527 */
11089: , ver.WP_FLAG
11090: FROM
11091: PJI_FM_EXTR_PLNVER4 ver
11092: , pa_resource_asSIGNments ra
11093: , PA_BUDGET_LINES bl
11094: , pa_projects_all ppa
11095: , PJI_FM_AGGR_DLY_RATES rates
11393: , fact.plan_type_code plan_type_code
11394: -- Bug 13107651 Reverted fix for bug 8510978
11395: FROM
11396: pji_fp_xbs_accum_f fact,
11397: pji_fm_extr_plnver4 ver
11398: WHERE fact.plan_version_id=ver.plan_version_id
11399: AND fact.plan_type_code = ver.plan_type_code /*4471527 */
11400: AND fact.project_id = ver.project_id
11401: AND NVL(ver.rbs_struct_version_id,-1) = fact.rbs_version_id
11780: , ppa.org_id project_org_id
11781: , ppa.carrying_out_organization_id project_organization_id
11782: , ver.plan_type_code plan_type_code /* 4471527*/
11783: FROM
11784: PJI_FM_EXTR_PLNVER4 ver
11785: , PA_RESOURCE_ASSIGNMENTS ra
11786: , PA_BUDGET_LINES bl
11787: , PA_PROJECTS_ALL ppa
11788: , PA_PROJ_ELEM_VER_STRUCTURE pevs
12380: , ppa.carrying_out_organization_id project_organization_id
12381: , ver.plan_type_code plan_type_code /*4471527 */
12382: , ver.time_phased_type_code
12383: FROM
12384: PJI_FM_EXTR_PLNVER4 ver
12385: , PJI_FM_EXTR_PLAN_LINES bl
12386: , PA_PROJECTS_ALL ppa
12387: , PA_PROJ_ELEM_VER_STRUCTURE pevs
12388: WHERE 1=1