[Home] [Help]
308: , rl.CUSTOM15
309: , rl.PLAN_TYPE_CODE /* 4471527 */
310: FROM
311: pji_fp_xbs_accum_f rl
312: , pji_fm_extr_plnver3_t ver3
313: WHERE 1=1
314: AND rl.project_id = l_src_project_ids(i)
315: AND rl.plan_version_id = p_source_fp_version_ids(i)
316: AND rl.plan_type_code = ver3.plan_type_code /* 4471527 */
832:
833:
834: IF ( p_slice_type = 'PRI') THEN
835:
836: INSERT INTO PJI_FM_EXTR_PLNVER3_T
837: (
838: PROJECT_ID ,
839: PLAN_VERSION_ID ,
840: WBS_STRUCT_VERSION_ID ,
894: ;
895:
896: ELSIF ( p_slice_type = 'SEC') THEN
897:
898: INSERT INTO PJI_FM_EXTR_PLNVER3_T
899: (
900: PROJECT_ID ,
901: PLAN_VERSION_ID ,
902: WBS_STRUCT_VERSION_ID ,
959:
960:
961: ELSIF ( p_slice_type = 'SECRBS') THEN
962:
963: INSERT INTO PJI_FM_EXTR_PLNVER3_T
964: (
965: PROJECT_ID ,
966: PLAN_VERSION_ID ,
967: WBS_STRUCT_VERSION_ID ,
1068: IF (p_slice_type = 'PRI') THEN
1069:
1070: FOR I IN 1..p_fp_version_ids.COUNT LOOP
1071:
1072: INSERT INTO PJI_FM_EXTR_PLNVER3_T ver3
1073: (
1074: PROJECT_ID ,
1075: PLAN_VERSION_ID ,
1076: WBS_STRUCT_VERSION_ID ,
1135: ELSIF (p_slice_type = 'SEC') THEN
1136:
1137: FOR I IN 1..p_fp_version_ids.COUNT LOOP
1138:
1139: INSERT INTO PJI_FM_EXTR_PLNVER3_T ver3
1140: (
1141: PROJECT_ID ,
1142: PLAN_VERSION_ID ,
1143: WBS_STRUCT_VERSION_ID ,
1206: ELSIF (p_slice_type = 'SECRBS') THEN
1207:
1208: FOR I IN 1..p_fp_version_ids.COUNT LOOP
1209:
1210: INSERT INTO PJI_FM_EXTR_PLNVER3_T ver3
1211: (
1212: PROJECT_ID ,
1213: PLAN_VERSION_ID ,
1214: WBS_STRUCT_VERSION_ID ,
1299: --
1300: PROCEDURE EXTRACT_CB_CO_PLAN_VERSIONS IS
1301: BEGIN
1302:
1303: INSERT INTO pji_fm_extr_plnver3_t
1304: (
1305: project_id
1306: , plan_version_id
1307: , wbs_struct_version_id
1337: , 'N' -- current_original_flag -- corr orig flag
1338: , baselined_flag -- baselined flag
1339: , secondary_rbs_flag -- sec rbs flag
1340: , lp_flag -- lp flag
1341: FROM pji_fm_extr_plnver3_t
1342: WHERE wp_flag = 'N'
1343: AND baselined_flag = 'Y'
1344: AND current_flag = 'Y'
1345: AND plan_version_id > 0
1363: , current_original_flag -- corr orig flag
1364: , 'N' -- baselined_flag -- baselined flag
1365: , secondary_rbs_flag -- sec rbs flag
1366: , lp_flag -- lp flag
1367: FROM pji_fm_extr_plnver3_t
1368: WHERE wp_flag = 'N'
1369: AND baselined_flag = 'Y'
1370: AND current_original_flag = 'Y'
1371: AND plan_version_id > 0
1392: CURSOR res_list IS
1393: SELECT /*+ ordered */ b.plan_version_id, decode(b.plan_type_code, 'C', a.cost_resource_list_id,
1394: 'R', a.revenue_resource_list_id,
1395: 'A', a.all_resource_list_id) resource_list_id
1396: FROM PJI_FM_EXTR_PLNVER3_T b, pa_proj_fp_options a
1397: WHERE b.project_id = a.project_id
1398: AND b.PLAN_TYPE_ID = a.FIN_PLAN_TYPE_ID
1399: AND b.plan_version_id = a.fin_plan_version_id;
1400: l_count1 NUMBER;
1421: FETCH res_list BULK COLLECT INTO l_plan_version_id, l_resource_list_id;
1422: CLOSE res_list;
1423:
1424: FORALL i IN l_plan_version_id.FIRST..l_plan_version_id.LAST
1425: UPDATE PJI_FM_EXTR_PLNVER3_T t
1426: SET worker_id = -99
1427: WHERE plan_version_id = l_plan_version_id(i)
1428: AND EXISTS (SELECT 1
1429: FROM pa_resource_lists_all_bg
1823: , ver.plan_type_code plan_type_code /* 4471527 */
1824: FROM
1825: PA_BUDGET_LINES bl
1826: , pa_resource_asSIGNments ra
1827: , PJI_FM_EXTR_PLNVER3_T ver
1828: , pa_projects_all ppa
1829: , PA_PROJ_ELEM_VER_STRUCTURE pevs
1830: WHERE
1831: ra.resource_asSIGNment_id = bl.resource_asSIGNment_id
1870: , collapse_bl.plan_type_code /* 4471527 */
1871: ) plr
1872: ---- End of first inline view plr ..........
1873: , PJI_ORG_EXTR_INFO orginfo
1874: , PJI_FM_EXTR_PLNVER3_T vers
1875: , pji_time_cal_period_v pji_time
1876: WHERE 1=1
1877: -- AND orginfo.projfunc_currency_mau is not NULL
1878: AND plr.project_org_id = orginfo.org_id
1915: IF P_PA_DEBUG_MODE = 'Y' THEN
1916: pa_debug.write('EXTRACT_PLAN_AMOUNTS_PRIRBS: ' || g_module_name,'EXTRACT_PLAN_AMOUNTS_PRIRBS count : '||SQL%ROWCOUNT ,1);
1917: END IF;
1918: -- cbs change
1919: UPDATE PJI_FM_EXTR_PLNVER3_T t
1920: SET worker_id = null
1921: WHERE worker_id = -99;
1922: -- End cbs change
1923:
2604: , ver.plan_type_code plan_type_code /* 4471527 */
2605: FROM
2606: PA_BUDGET_LINES bl
2607: , pa_resource_asSIGNments ra
2608: , PJI_FM_EXTR_PLNVER3_T ver
2609: , pa_projects_all ppa
2610: , PA_PROJ_ELEM_VER_STRUCTURE pevs
2611: , PJI_FM_AGGR_DLY_RATES_T rates
2612: , pji_time_cal_period_v prd
2910: , ver.plan_type_code plan_type_code /* 4471527 */
2911: FROM
2912: PA_BUDGET_LINES bl
2913: , pa_resource_asSIGNments ra
2914: , PJI_FM_EXTR_PLNVER3_T ver
2915: , pa_projects_all ppa
2916: , PA_PROJ_ELEM_VER_STRUCTURE pevs
2917: , PJI_FM_AGGR_DLY_RATES_T rates
2918: -- , pji_time_cal_period prd
2983: ,collapse_bl.rate2
2984: , collapse_bl.plan_type_code /* 4471527 */
2985: ) plr
2986: ---- End of first inline view plr ..........
2987: , pji_fm_extr_plnver3_t vers
2988: WHERE 1=1
2989: AND vers.plan_version_id = plr.plan_version_id
2990: AND vers.plan_type_code = plr.plan_type_code /*4471527 */
2991: -- AND plr.CURR_RECORD_TYPE IS NOT NULL
3665: , ver.plan_type_code plan_type_code /* 4471527 */
3666: FROM
3667: PA_BUDGET_LINES bl
3668: , pa_resource_asSIGNments ra
3669: , PJI_FM_EXTR_PLNVER3_T ver
3670: , pa_projects_all ppa
3671: , PA_PROJ_ELEM_VER_STRUCTURE pevs
3672: , PJI_FM_AGGR_DLY_RATES_T rates
3673: , pji_time_cal_period_v prd
3957: , ver.plan_type_code plan_type_code /* 4471527 */
3958: FROM
3959: PA_BUDGET_LINES bl
3960: , pa_resource_asSIGNments ra
3961: , PJI_FM_EXTR_PLNVER3_T ver
3962: , pa_projects_all ppa
3963: , PA_PROJ_ELEM_VER_STRUCTURE pevs
3964: , PJI_FM_AGGR_DLY_RATES_T rates
3965: WHERE 1=1
4018: ,collapse_bl.rate2
4019: ,collapse_bl.plan_type_code /* 4471527 */
4020: ) plr
4021: ---- End of first inline view plr ..........
4022: , pji_fm_extr_plnver3_t vers
4023: WHERE 1=1
4024: AND vers.plan_version_id = plr.plan_version_id
4025: AND vers.plan_type_code = plr.plan_type_code /*4471527 */
4026: AND NVL(vers.rbs_struct_version_id, -1) = plr.rbs_struct_version_id
4215: , g_ntp_period_name
4216: , fact.plan_type_code /* 4471527 */
4217: FROM
4218: pji_fp_xbs_accum_f fact
4219: , pji_fm_extr_plnver3_t ver
4220: WHERE 1=1
4221: AND fact.plan_version_id = ver.plan_version_id
4222: AND fact.plan_type_code = ver.plan_type_code /* 4471527 */
4223: AND fact.project_id = ver.project_id
4528: Commented for bug 9048716 */
4529:
4530: CURSOR c_struct_ver_ids IS
4531: SELECT wbs_struct_version_id
4532: FROM PJI_FM_EXTR_PLNVER3_T;
4533:
4534: BEGIN
4535:
4536: /* l_per_analysis_flag := PJI_UTILS.GET_SETUP_PARAMETER('PER_ANALYSIS_FLAG'); /* Added for bug 8708651
4946: , ppa.carrying_out_organization_id project_organization_id
4947: , ver.plan_type_code plan_type_code /* 4471527 */
4948: FROM
4949: PJI_FM_EXTR_PLAN_LINES bl
4950: , PJI_FM_EXTR_PLNVER3_T ver
4951: , pa_projects_all ppa
4952: , PA_PROJ_ELEM_VER_STRUCTURE pevs
4953: , pji_fp_rmap_fpr_update_t rwid
4954: WHERE 1=1
4995: , collapse_bl.plan_type_code /* 4471527 */
4996: ) plr
4997: ---- End of first inline view plr ..........
4998: , PJI_ORG_EXTR_INFO orginfo
4999: , PJI_FM_EXTR_PLNVER3_T vers
5000: , pji_time_cal_period_v pji_time
5001: WHERE 1=1
5002: AND plr.project_org_id = orginfo.org_id
5003: AND plr.project_id = vers.project_id
5055: BEGIN
5056:
5057: print_time('........POPULATE_PLN_VER_TABLE : Begin.' );
5058:
5059: INSERT INTO PJI_FM_EXTR_PLNVER3_T
5060: (
5061: PROJECT_ID ,
5062: PLAN_VERSION_ID ,
5063: WBS_STRUCT_VERSION_ID ,
5213: , l_gl2_mau mau2
5214: FROM pa_budget_lines bl
5215: , pa_budget_versions bv -- added for bug 5842896
5216: , pji_time_cal_period_v prd
5217: , PJI_FM_EXTR_PLNVER3_T ver
5218: , pa_projects_all ppa
5219: ,PJI_ORG_EXTR_INFO inf
5220: WHERE bl.budget_version_id = ver.plan_version_id
5221: AND bl.budget_version_id = bv.budget_version_id -- added for bug 5842896
5254: ) end_date
5255: , l_gl1_mau mau1
5256: , l_gl2_mau mau2
5257: FROM pa_resource_assignments ra
5258: , PJI_FM_EXTR_PLNVER3_T ver
5259: , pa_projects_all ppa
5260: WHERE ra.budget_version_id = ver.plan_version_id
5261: AND ver.wp_flag = 'N'
5262: AND ver.baselined_flag = 'Y'
5268:
5269: /* See if any line with negative rate was derived. If so then update the
5270: corresponding line in ver3 as rate dangling*/
5271:
5272: UPDATE PJI_FM_EXTR_PLNVER3_T ver
5273: SET rate_dangling_flag = 'Y'
5274: WHERE (project_id,plan_version_id) IN
5275: ( SELECT project_id,budget_version_id
5276: FROM PA_BUDGET_LINES bl,
5789: , null TIME_DANGLING_FLAG
5790: -- , 0 prg_level
5791: , fact.plan_type_code plan_type_code -- 4471527
5792: FROM pji_fp_xbs_accum_f fact
5793: , pji_fm_extr_plnver3_t ver3
5794: WHERE 1=1
5795: AND rbs_aggr_level = g_lowest_level
5796: AND wbs_rollup_flag = 'N'
5797: AND prg_rollup_flag = 'N'
5892: , ( SELECT cal_period_id period_id, start_date, end_date FROM pji_time_cal_period_v
5893: UNION ALL
5894: SELECT ent_period_id, start_date, end_date FROM pji_time_ent_period_v
5895: ) dates
5896: , pji_fm_extr_plnver3_t ver
5897: WHERE 1=1
5898: AND fact.project_id = ver.project_id -- new
5899: AND fact.plan_version_id = ver.plan_version_id
5900: AND fact.plan_type_code = ver.plan_type_code -- 4471527
6004: , ( SELECT cal_period_id period_id, start_date, end_date FROM pji_time_cal_period_v
6005: UNION ALL
6006: SELECT ent_period_id, start_date, end_date FROM pji_time_ent_period_v
6007: ) dates
6008: , pji_fm_extr_plnver3_t ver
6009: WHERE 1=1
6010: AND fact.project_id = ver.project_id -- new
6011: AND fact.plan_version_id = ver.plan_version_id
6012: ANd fact.plan_type_code = ver.plan_type_code -- 4471527
6107: , fact.TIME_DANGLING_FLAG
6108: -- , 0
6109: , fact.plan_type_code plan_type_code -- 4471527
6110: FROM pji_fp_aggr_pjp1_t fact
6111: , pji_fm_extr_plnver3_t ver
6112: WHERE 1=1
6113: AND fact.worker_id = -1
6114: AND fact.project_id = ver.project_id
6115: AND fact.plan_version_id = ver.plan_version_id
6206: -- , END_DATE
6207: -- , 0
6208: , fact.plan_type_code plan_type_code -- 4471527
6209: FROM pji_fp_xbs_accum_f fact
6210: , pji_fm_extr_plnver3_t ver
6211: WHERE 1=1
6212: AND fact.project_id = ver.project_id
6213: AND fact.plan_version_id = ver.plan_version_id
6214: AND fact.plan_type_code = ver.plan_type_code -- 4471527
6265: WHERE BITAND(curr_record_type_id, 28) >= 4 -- 000.011.110 = 16 + 8 + 4 = 28
6266: AND (currency_code IN (g_global1_currency_code, g_global2_currency_code))
6267: AND plan_version_id IN
6268: (SELECT DISTINCT plan_version_id
6269: FROM PJI_FM_EXTR_PLNVER3_T
6270: WHERE 1=1
6271: AND baselined_flag = 'Y'
6272: AND wp_flag = 'N');
6273:
6279: AND (currency_code IN (g_global1_currency_code, g_global2_currency_code))
6280: AND worker_id = g_worker_id
6281: AND plan_version_id IN
6282: (SELECT DISTINCT plan_version_id
6283: FROM PJI_FM_EXTR_PLNVER3_T
6284: WHERE 1=1
6285: AND baselined_flag = 'Y'
6286: AND wp_flag = 'N');
6287:
6298:
6299: PROCEDURE DO_CURRENCY_DANGLING_CHECK
6300: IS BEGIN
6301:
6302: UPDATE PJI_FM_EXTR_PLNVER3_T
6303: SET rate_dangling_flag = 'Y'
6304: WHERE plan_version_id IN (
6305: SELECT
6306: fact.PLAN_VERSION_ID
6464: updating the plan versions as time dangling so that the plan will be marked as dangling. Not updating
6465: the existing records so that data for the primary calendar is not impacted because of issue in proration to
6466: other calendars */
6467:
6468: UPDATE PJI_FM_EXTR_PLNVER3_T
6469: SET time_dangling_flag='Y'
6470: WHERE wp_flag='N'
6471: AND baselined_flag = 'Y' ;
6472: -- Not raising any exception as we want the processing to happen properly.
7197: , ppa.carrying_out_organization_id project_organization_id
7198: , ver.plan_type_code plan_type_code /* 4471527 */
7199: FROM
7200: PJI_FM_EXTR_PLAN_LINES bl
7201: , PJI_FM_EXTR_PLNVER3_T ver
7202: , PA_PROJECTS_ALL ppa
7203: , PA_PROJ_ELEM_VER_STRUCTURE pevs
7204: , pji_fp_rmap_fpr_update_t rwid
7205: WHERE 1=1
7267: , ver.plan_type_code plan_type_code /* 4471527 */
7268: FROM
7269: PA_BUDGET_LINES bl
7270: , PA_RESOURCE_ASSIGNMENTS ra
7271: , PJI_FM_EXTR_PLNVER3_T ver
7272: , PA_PROJECTS_ALL ppa
7273: , PA_PROJ_ELEM_VER_STRUCTURE pevs
7274: WHERE
7275: ra.resource_asSIGNment_id = bl.resource_asSIGNment_id
7310: , collapse_bl.plan_type_code
7311: ) plr
7312: ---- End of first inline view plr ..........
7313: , PJI_ORG_EXTR_INFO orginfo
7314: , PJI_FM_EXTR_PLNVER3_T vers
7315: , pji_time_cal_period_v pji_time
7316: WHERE 1=1
7317: AND plr.project_org_id = orginfo.org_id
7318: AND plr.project_id = vers.project_id
9020: , fact1.line_type
9021: , g_default_prg_level
9022: , fact1.PLAN_TYPE_CODE /* 4471527 */
9023: FROM pji_fp_aggr_pjp1_t fact1
9024: , pji_fm_extr_plnver3_t ver3
9025: WHERE
9026: fact1.RBS_AGGR_LEVEL = g_lowest_level
9027: AND fact1.worker_id = g_worker_id
9028: AND fact1.plan_version_id = ver3.plan_version_id
9107: , l_created_by
9108: , l_last_update_login
9109: , bv.plan_type_code /*4471527 */
9110: FROM pa_rbs_prj_assignments rpa
9111: , PJI_FM_EXTR_PLNVER3_T bv
9112: , pji_pjp_rbs_header head
9113: WHERE bv.project_id = rpa.project_id
9114: AND bv.RBS_STRUCT_VERSION_ID = rpa.RBS_VERSION_ID
9115: AND bv.PROJECT_ID = head.PROJECT_ID (+)
9147: , l_creation_date
9148: , l_created_by
9149: , l_last_update_login
9150: , bv.plan_type_code /* 4471527 */
9151: FROM PJI_FM_EXTR_PLNVER3_T bv
9152: , pji_pjp_rbs_header head
9153: WHERE bv.PROJECT_ID = head.PROJECT_ID (+)
9154: AND bv.PLAN_VERSION_ID = head.PLAN_VERSION_ID (+)
9155: AND bv.plan_type_code = head.plan_type_code (+) /* 4471527 */
9216: , l_last_updated_by
9217: , l_creation_date
9218: , l_created_by
9219: , l_last_update_login
9220: FROM PJI_FM_EXTR_PLNVER3_T ver
9221: , PJI_PJP_WBS_HEADER whdr
9222: WHERE ver.plan_version_id = whdr.plan_version_id (+)
9223: AND ver.project_id = whdr.project_id (+)
9224: -- AND ver.wbs_struct_version_id = whdr.wbs_version_id (+)
9300:
9301: PROCEDURE MARK_TIME_DANGLING_VERSIONS IS
9302: BEGIN
9303:
9304: UPDATE PJI_FM_EXTR_PLNVER3_T
9305: SET TIME_DANGLING_FLAG = 'Y'
9306: WHERE plan_version_id IN
9307: (SELECT fact.plan_version_id
9308: FROM pji_fp_aggr_pjp1_t fact
9314: AND fact.time_id = time.cal_period_id
9315: AND orginfo.org_id = fact.project_org_id
9316: AND TO_NUMBER(TO_CHAR(time.end_date, 'J')) > gl_calendar_max_date);
9317:
9318: UPDATE PJI_FM_EXTR_PLNVER3_T
9319: SET TIME_DANGLING_FLAG = 'Y'
9320: WHERE plan_version_id IN
9321: (SELECT fact.plan_version_id
9322: FROM pji_fp_aggr_pjp1_t fact
9342: l_plan_version_id NUMBER;
9343:
9344: CURSOR PLANS_TO_MARK
9345: IS SELECT DISTINCT project_id,plan_version_id
9346: FROM pji_fm_extr_plnver3_t;
9347:
9348: l_plans_to_mark PLANS_TO_MARK%ROWTYPE;
9349:
9350: BEGIN
9376: ),
9377: record_version_number=nvl(record_version_number,0)+1
9378: WHERE budget_version_id IN
9379: ( SELECT DISTINCT plan_version_id
9380: FROM pji_fm_extr_plnver3_t );
9381:
9382: ELSE -- Secondary slice.
9383: FOR l_plans_to_mark IN PLANS_TO_MARK LOOP
9384: DECLARE
9395: AND project_id=l_plans_to_mark.project_id
9396: AND plan_version_id=l_plans_to_mark.plan_version_id
9397: UNION ALL
9398: SELECT 1
9399: FROM PJI_FM_EXTR_PLNVER3_T
9400: WHERE time_dangling_flag IS NOT NULL
9401: AND project_id=l_plans_to_mark.project_id
9402: AND plan_version_id=l_plans_to_mark.plan_version_id
9403: );
9413: record_version_number=nvl(record_version_number,0)+1
9414: WHERE budget_version_id IN
9415: (
9416: SELECT plan_version_id
9417: FROM PJI_FM_EXTR_PLNVER3_T
9418: );
9419:
9420: END LOOP;
9421: -- Added INDEX for bug 3828698
9438: OR rate_dangling_flag IS NOT NULL )
9439: )
9440: AND budget_version_id NOT IN
9441: ( SELECT distinct plan_version_id
9442: FROM PJI_FM_EXTR_PLNVER3_T
9443: WHERE time_dangling_flag IS NOT NULL
9444: );
9445:
9446: UPDATE pa_budget_versions
9455: OR rate_dangling_flag IS NOT NULL )
9456: )
9457: OR budget_version_id in
9458: ( SELECT distinct plan_version_id
9459: FROM PJI_FM_EXTR_PLNVER3_T
9460: WHERE time_dangling_flag IS NOT NULL
9461: ); */
9462:
9463: END IF;
9575: BEGIN
9576:
9577: print_time('.......CLEANUP_INTERIM_TABLES: Begin. ');
9578:
9579: DELETE FROM PJI_FM_EXTR_PLNVER3_T ; -- No worker id join needed for online case.
9580:
9581: print_time('.......ver3 rows deleted: #= ' || sql%rowcount);
9582:
9583: DELETE FROM pji_fp_aggr_pjp1_t ; -- No worker id join needed for online case.
9632: BEGIN
9633:
9634: SELECT MAX(plan_version_id)
9635: INTO x_max_plnver_id
9636: FROM pji_fm_extr_plnver3_t;
9637:
9638: EXCEPTION
9639: WHEN NO_DATA_FOUND THEN
9640: RETURN;
9733: ) period_type
9734: FROM pa_implementations_all imp
9735: , gl_sets_of_books sob
9736: , pa_projects_all proj
9737: , pji_fm_extr_plnver3_t ver
9738: , ( SELECT 1 cal_type FROM DUAL -- 1 GL, 2 PA.
9739: UNION ALL
9740: SELECT 2 FROM DUAL
9741: ) split_cal