[Home] [Help]
306: , rl.CUSTOM15
307: , rl.PLAN_TYPE_CODE /* 4471527 */
308: FROM
309: pji_fp_xbs_accum_f rl
310: , pji_fm_extr_plnver3_t ver3
311: WHERE 1=1
312: AND rl.project_id = l_src_project_ids(i)
313: AND rl.plan_version_id = p_source_fp_version_ids(i)
314: AND rl.plan_type_code = ver3.plan_type_code /* 4471527 */
830:
831:
832: IF ( p_slice_type = 'PRI') THEN
833:
834: INSERT INTO PJI_FM_EXTR_PLNVER3_T
835: (
836: PROJECT_ID ,
837: PLAN_VERSION_ID ,
838: WBS_STRUCT_VERSION_ID ,
892: ;
893:
894: ELSIF ( p_slice_type = 'SEC') THEN
895:
896: INSERT INTO PJI_FM_EXTR_PLNVER3_T
897: (
898: PROJECT_ID ,
899: PLAN_VERSION_ID ,
900: WBS_STRUCT_VERSION_ID ,
957:
958:
959: ELSIF ( p_slice_type = 'SECRBS') THEN
960:
961: INSERT INTO PJI_FM_EXTR_PLNVER3_T
962: (
963: PROJECT_ID ,
964: PLAN_VERSION_ID ,
965: WBS_STRUCT_VERSION_ID ,
1066: IF (p_slice_type = 'PRI') THEN
1067:
1068: FOR I IN 1..p_fp_version_ids.COUNT LOOP
1069:
1070: INSERT INTO PJI_FM_EXTR_PLNVER3_T ver3
1071: (
1072: PROJECT_ID ,
1073: PLAN_VERSION_ID ,
1074: WBS_STRUCT_VERSION_ID ,
1133: ELSIF (p_slice_type = 'SEC') THEN
1134:
1135: FOR I IN 1..p_fp_version_ids.COUNT LOOP
1136:
1137: INSERT INTO PJI_FM_EXTR_PLNVER3_T ver3
1138: (
1139: PROJECT_ID ,
1140: PLAN_VERSION_ID ,
1141: WBS_STRUCT_VERSION_ID ,
1204: ELSIF (p_slice_type = 'SECRBS') THEN
1205:
1206: FOR I IN 1..p_fp_version_ids.COUNT LOOP
1207:
1208: INSERT INTO PJI_FM_EXTR_PLNVER3_T ver3
1209: (
1210: PROJECT_ID ,
1211: PLAN_VERSION_ID ,
1212: WBS_STRUCT_VERSION_ID ,
1297: --
1298: PROCEDURE EXTRACT_CB_CO_PLAN_VERSIONS IS
1299: BEGIN
1300:
1301: INSERT INTO pji_fm_extr_plnver3_t
1302: (
1303: project_id
1304: , plan_version_id
1305: , wbs_struct_version_id
1335: , 'N' -- current_original_flag -- corr orig flag
1336: , baselined_flag -- baselined flag
1337: , secondary_rbs_flag -- sec rbs flag
1338: , lp_flag -- lp flag
1339: FROM pji_fm_extr_plnver3_t
1340: WHERE wp_flag = 'N'
1341: AND baselined_flag = 'Y'
1342: AND current_flag = 'Y'
1343: AND plan_version_id > 0
1361: , current_original_flag -- corr orig flag
1362: , 'N' -- baselined_flag -- baselined flag
1363: , secondary_rbs_flag -- sec rbs flag
1364: , lp_flag -- lp flag
1365: FROM pji_fm_extr_plnver3_t
1366: WHERE wp_flag = 'N'
1367: AND baselined_flag = 'Y'
1368: AND current_original_flag = 'Y'
1369: AND plan_version_id > 0
1781: , ver.plan_type_code plan_type_code /* 4471527 */
1782: FROM
1783: PA_BUDGET_LINES bl
1784: , pa_resource_asSIGNments ra
1785: , PJI_FM_EXTR_PLNVER3_T ver
1786: , pa_projects_all ppa
1787: , PA_PROJ_ELEM_VER_STRUCTURE pevs
1788: WHERE
1789: ra.resource_asSIGNment_id = bl.resource_asSIGNment_id
1828: , collapse_bl.plan_type_code /* 4471527 */
1829: ) plr
1830: ---- End of first inline view plr ..........
1831: , PJI_ORG_EXTR_INFO orginfo
1832: , PJI_FM_EXTR_PLNVER3_T vers
1833: , pji_time_cal_period_v pji_time
1834: WHERE 1=1
1835: -- AND orginfo.projfunc_currency_mau is not NULL
1836: AND plr.project_org_id = orginfo.org_id
2537: , ver.plan_type_code plan_type_code /* 4471527 */
2538: FROM
2539: PA_BUDGET_LINES bl
2540: , pa_resource_asSIGNments ra
2541: , PJI_FM_EXTR_PLNVER3_T ver
2542: , pa_projects_all ppa
2543: , PA_PROJ_ELEM_VER_STRUCTURE pevs
2544: , PJI_FM_AGGR_DLY_RATES_T rates
2545: , pji_time_cal_period_v prd
2840: , ver.plan_type_code plan_type_code /* 4471527 */
2841: FROM
2842: PA_BUDGET_LINES bl
2843: , pa_resource_asSIGNments ra
2844: , PJI_FM_EXTR_PLNVER3_T ver
2845: , pa_projects_all ppa
2846: , PA_PROJ_ELEM_VER_STRUCTURE pevs
2847: , PJI_FM_AGGR_DLY_RATES_T rates
2848: -- , pji_time_cal_period prd
2913: ,collapse_bl.rate2
2914: , collapse_bl.plan_type_code /* 4471527 */
2915: ) plr
2916: ---- End of first inline view plr ..........
2917: , pji_fm_extr_plnver3_t vers
2918: WHERE 1=1
2919: AND vers.plan_version_id = plr.plan_version_id
2920: AND vers.plan_type_code = plr.plan_type_code /*4471527 */
2921: -- AND plr.CURR_RECORD_TYPE IS NOT NULL
3581: , ver.plan_type_code plan_type_code /* 4471527 */
3582: FROM
3583: PA_BUDGET_LINES bl
3584: , pa_resource_asSIGNments ra
3585: , PJI_FM_EXTR_PLNVER3_T ver
3586: , pa_projects_all ppa
3587: , PA_PROJ_ELEM_VER_STRUCTURE pevs
3588: , PJI_FM_AGGR_DLY_RATES_T rates
3589: , pji_time_cal_period_v prd
3870: , ver.plan_type_code plan_type_code /* 4471527 */
3871: FROM
3872: PA_BUDGET_LINES bl
3873: , pa_resource_asSIGNments ra
3874: , PJI_FM_EXTR_PLNVER3_T ver
3875: , pa_projects_all ppa
3876: , PA_PROJ_ELEM_VER_STRUCTURE pevs
3877: , PJI_FM_AGGR_DLY_RATES_T rates
3878: WHERE 1=1
3931: ,collapse_bl.rate2
3932: ,collapse_bl.plan_type_code /* 4471527 */
3933: ) plr
3934: ---- End of first inline view plr ..........
3935: , pji_fm_extr_plnver3_t vers
3936: WHERE 1=1
3937: AND vers.plan_version_id = plr.plan_version_id
3938: AND vers.plan_type_code = plr.plan_type_code /*4471527 */
3939: AND NVL(vers.rbs_struct_version_id, -1) = plr.rbs_struct_version_id
4128: , g_ntp_period_name
4129: , fact.plan_type_code /* 4471527 */
4130: FROM
4131: pji_fp_xbs_accum_f fact
4132: , pji_fm_extr_plnver3_t ver
4133: WHERE 1=1
4134: AND fact.plan_version_id = ver.plan_version_id
4135: AND fact.plan_type_code = ver.plan_type_code /* 4471527 */
4136: AND fact.project_id = ver.project_id
4437: l_max_plnver_id NUMBER := NULL;
4438:
4439: CURSOR c_struct_ver_ids IS
4440: SELECT wbs_struct_version_id
4441: FROM PJI_FM_EXTR_PLNVER3_T;
4442:
4443: BEGIN
4444:
4445: print_time('........RETRIEVE_DELTA_SLICE : Begin.' );
4847: , ppa.carrying_out_organization_id project_organization_id
4848: , ver.plan_type_code plan_type_code /* 4471527 */
4849: FROM
4850: PJI_FM_EXTR_PLAN_LINES bl
4851: , PJI_FM_EXTR_PLNVER3_T ver
4852: , pa_projects_all ppa
4853: , PA_PROJ_ELEM_VER_STRUCTURE pevs
4854: , pji_fp_rmap_fpr_update_t rwid
4855: WHERE 1=1
4896: , collapse_bl.plan_type_code /* 4471527 */
4897: ) plr
4898: ---- End of first inline view plr ..........
4899: , PJI_ORG_EXTR_INFO orginfo
4900: , PJI_FM_EXTR_PLNVER3_T vers
4901: , pji_time_cal_period_v pji_time
4902: WHERE 1=1
4903: AND plr.project_org_id = orginfo.org_id
4904: AND plr.project_id = vers.project_id
4955: BEGIN
4956:
4957: print_time('........POPULATE_PLN_VER_TABLE : Begin.' );
4958:
4959: INSERT INTO PJI_FM_EXTR_PLNVER3_T
4960: (
4961: PROJECT_ID ,
4962: PLAN_VERSION_ID ,
4963: WBS_STRUCT_VERSION_ID ,
5113: , l_gl2_mau mau2
5114: FROM pa_budget_lines bl
5115: , pa_budget_versions bv -- added for bug 5842896
5116: , pji_time_cal_period_v prd
5117: , PJI_FM_EXTR_PLNVER3_T ver
5118: , pa_projects_all ppa
5119: ,PJI_ORG_EXTR_INFO inf
5120: WHERE bl.budget_version_id = ver.plan_version_id
5121: AND bl.budget_version_id = bv.budget_version_id -- added for bug 5842896
5153: ) end_date
5154: , l_gl1_mau mau1
5155: , l_gl2_mau mau2
5156: FROM pa_resource_assignments ra
5157: , PJI_FM_EXTR_PLNVER3_T ver
5158: , pa_projects_all ppa
5159: WHERE ra.budget_version_id = ver.plan_version_id
5160: AND ver.wp_flag = 'N'
5161: AND ver.baselined_flag = 'Y'
5167:
5168: /* See if any line with negative rate was derived. If so then update the
5169: corresponding line in ver3 as rate dangling*/
5170:
5171: UPDATE PJI_FM_EXTR_PLNVER3_T ver
5172: SET rate_dangling_flag = 'Y'
5173: WHERE (project_id,plan_version_id) IN
5174: ( SELECT project_id,budget_version_id
5175: FROM PA_BUDGET_LINES bl,
5688: , null TIME_DANGLING_FLAG
5689: -- , 0 prg_level
5690: , fact.plan_type_code plan_type_code -- 4471527
5691: FROM pji_fp_xbs_accum_f fact
5692: , pji_fm_extr_plnver3_t ver3
5693: WHERE 1=1
5694: AND rbs_aggr_level = g_lowest_level
5695: AND wbs_rollup_flag = 'N'
5696: AND prg_rollup_flag = 'N'
5791: , ( SELECT cal_period_id period_id, start_date, end_date FROM pji_time_cal_period_v
5792: UNION ALL
5793: SELECT ent_period_id, start_date, end_date FROM pji_time_ent_period_v
5794: ) dates
5795: , pji_fm_extr_plnver3_t ver
5796: WHERE 1=1
5797: AND fact.project_id = ver.project_id -- new
5798: AND fact.plan_version_id = ver.plan_version_id
5799: AND fact.plan_type_code = ver.plan_type_code -- 4471527
5903: , ( SELECT cal_period_id period_id, start_date, end_date FROM pji_time_cal_period_v
5904: UNION ALL
5905: SELECT ent_period_id, start_date, end_date FROM pji_time_ent_period_v
5906: ) dates
5907: , pji_fm_extr_plnver3_t ver
5908: WHERE 1=1
5909: AND fact.project_id = ver.project_id -- new
5910: AND fact.plan_version_id = ver.plan_version_id
5911: ANd fact.plan_type_code = ver.plan_type_code -- 4471527
6006: , fact.TIME_DANGLING_FLAG
6007: -- , 0
6008: , fact.plan_type_code plan_type_code -- 4471527
6009: FROM pji_fp_aggr_pjp1_t fact
6010: , pji_fm_extr_plnver3_t ver
6011: WHERE 1=1
6012: AND fact.worker_id = -1
6013: AND fact.project_id = ver.project_id
6014: AND fact.plan_version_id = ver.plan_version_id
6105: -- , END_DATE
6106: -- , 0
6107: , fact.plan_type_code plan_type_code -- 4471527
6108: FROM pji_fp_xbs_accum_f fact
6109: , pji_fm_extr_plnver3_t ver
6110: WHERE 1=1
6111: AND fact.project_id = ver.project_id
6112: AND fact.plan_version_id = ver.plan_version_id
6113: AND fact.plan_type_code = ver.plan_type_code -- 4471527
6164: WHERE BITAND(curr_record_type_id, 28) >= 4 -- 000.011.110 = 16 + 8 + 4 = 28
6165: AND (currency_code IN (g_global1_currency_code, g_global2_currency_code))
6166: AND plan_version_id IN
6167: (SELECT DISTINCT plan_version_id
6168: FROM PJI_FM_EXTR_PLNVER3_T
6169: WHERE 1=1
6170: AND baselined_flag = 'Y'
6171: AND wp_flag = 'N');
6172:
6178: AND (currency_code IN (g_global1_currency_code, g_global2_currency_code))
6179: AND worker_id = g_worker_id
6180: AND plan_version_id IN
6181: (SELECT DISTINCT plan_version_id
6182: FROM PJI_FM_EXTR_PLNVER3_T
6183: WHERE 1=1
6184: AND baselined_flag = 'Y'
6185: AND wp_flag = 'N');
6186:
6197:
6198: PROCEDURE DO_CURRENCY_DANGLING_CHECK
6199: IS BEGIN
6200:
6201: UPDATE PJI_FM_EXTR_PLNVER3_T
6202: SET rate_dangling_flag = 'Y'
6203: WHERE plan_version_id IN (
6204: SELECT
6205: fact.PLAN_VERSION_ID
6363: updating the plan versions as time dangling so that the plan will be marked as dangling. Not updating
6364: the existing records so that data for the primary calendar is not impacted because of issue in proration to
6365: other calendars */
6366:
6367: UPDATE PJI_FM_EXTR_PLNVER3_T
6368: SET time_dangling_flag='Y'
6369: WHERE wp_flag='N'
6370: AND baselined_flag = 'Y' ;
6371: -- Not raising any exception as we want the processing to happen properly.
7068: , ppa.carrying_out_organization_id project_organization_id
7069: , ver.plan_type_code plan_type_code /* 4471527 */
7070: FROM
7071: PJI_FM_EXTR_PLAN_LINES bl
7072: , PJI_FM_EXTR_PLNVER3_T ver
7073: , PA_PROJECTS_ALL ppa
7074: , PA_PROJ_ELEM_VER_STRUCTURE pevs
7075: , pji_fp_rmap_fpr_update_t rwid
7076: WHERE 1=1
7134: , ver.plan_type_code plan_type_code /* 4471527 */
7135: FROM
7136: PA_BUDGET_LINES bl
7137: , PA_RESOURCE_ASSIGNMENTS ra
7138: , PJI_FM_EXTR_PLNVER3_T ver
7139: , PA_PROJECTS_ALL ppa
7140: , PA_PROJ_ELEM_VER_STRUCTURE pevs
7141: WHERE
7142: ra.resource_asSIGNment_id = bl.resource_asSIGNment_id
7177: , collapse_bl.plan_type_code
7178: ) plr
7179: ---- End of first inline view plr ..........
7180: , PJI_ORG_EXTR_INFO orginfo
7181: , PJI_FM_EXTR_PLNVER3_T vers
7182: , pji_time_cal_period_v pji_time
7183: WHERE 1=1
7184: AND plr.project_org_id = orginfo.org_id
7185: AND plr.project_id = vers.project_id
8872: , fact1.line_type
8873: , g_default_prg_level
8874: , fact1.PLAN_TYPE_CODE /* 4471527 */
8875: FROM pji_fp_aggr_pjp1_t fact1
8876: , pji_fm_extr_plnver3_t ver3
8877: WHERE
8878: fact1.RBS_AGGR_LEVEL = g_lowest_level
8879: AND fact1.worker_id = g_worker_id
8880: AND fact1.plan_version_id = ver3.plan_version_id
8959: , l_created_by
8960: , l_last_update_login
8961: , bv.plan_type_code /*4471527 */
8962: FROM pa_rbs_prj_assignments rpa
8963: , PJI_FM_EXTR_PLNVER3_T bv
8964: , pji_pjp_rbs_header head
8965: WHERE bv.project_id = rpa.project_id
8966: AND bv.RBS_STRUCT_VERSION_ID = rpa.RBS_VERSION_ID
8967: AND bv.PROJECT_ID = head.PROJECT_ID (+)
8999: , l_creation_date
9000: , l_created_by
9001: , l_last_update_login
9002: , bv.plan_type_code /* 4471527 */
9003: FROM PJI_FM_EXTR_PLNVER3_T bv
9004: , pji_pjp_rbs_header head
9005: WHERE bv.PROJECT_ID = head.PROJECT_ID (+)
9006: AND bv.PLAN_VERSION_ID = head.PLAN_VERSION_ID (+)
9007: AND bv.plan_type_code = head.plan_type_code (+) /* 4471527 */
9068: , l_last_updated_by
9069: , l_creation_date
9070: , l_created_by
9071: , l_last_update_login
9072: FROM PJI_FM_EXTR_PLNVER3_T ver
9073: , PJI_PJP_WBS_HEADER whdr
9074: WHERE ver.plan_version_id = whdr.plan_version_id (+)
9075: AND ver.project_id = whdr.project_id (+)
9076: -- AND ver.wbs_struct_version_id = whdr.wbs_version_id (+)
9145:
9146: PROCEDURE MARK_TIME_DANGLING_VERSIONS IS
9147: BEGIN
9148:
9149: UPDATE PJI_FM_EXTR_PLNVER3_T
9150: SET TIME_DANGLING_FLAG = 'Y'
9151: WHERE plan_version_id IN
9152: (SELECT fact.plan_version_id
9153: FROM pji_fp_aggr_pjp1_t fact
9159: AND fact.time_id = time.cal_period_id
9160: AND orginfo.org_id = fact.project_org_id
9161: AND TO_NUMBER(TO_CHAR(time.end_date, 'J')) > gl_calendar_max_date);
9162:
9163: UPDATE PJI_FM_EXTR_PLNVER3_T
9164: SET TIME_DANGLING_FLAG = 'Y'
9165: WHERE plan_version_id IN
9166: (SELECT fact.plan_version_id
9167: FROM pji_fp_aggr_pjp1_t fact
9187: l_plan_version_id NUMBER;
9188:
9189: CURSOR PLANS_TO_MARK
9190: IS SELECT DISTINCT project_id,plan_version_id
9191: FROM pji_fm_extr_plnver3_t;
9192:
9193: l_plans_to_mark PLANS_TO_MARK%ROWTYPE;
9194:
9195: BEGIN
9221: ),
9222: record_version_number=nvl(record_version_number,0)+1
9223: WHERE budget_version_id IN
9224: ( SELECT DISTINCT plan_version_id
9225: FROM pji_fm_extr_plnver3_t );
9226:
9227: ELSE -- Secondary slice.
9228: FOR l_plans_to_mark IN PLANS_TO_MARK LOOP
9229: DECLARE
9240: AND project_id=l_plans_to_mark.project_id
9241: AND plan_version_id=l_plans_to_mark.plan_version_id
9242: UNION ALL
9243: SELECT 1
9244: FROM PJI_FM_EXTR_PLNVER3_T
9245: WHERE time_dangling_flag IS NOT NULL
9246: AND project_id=l_plans_to_mark.project_id
9247: AND plan_version_id=l_plans_to_mark.plan_version_id
9248: );
9258: record_version_number=nvl(record_version_number,0)+1
9259: WHERE budget_version_id IN
9260: (
9261: SELECT plan_version_id
9262: FROM PJI_FM_EXTR_PLNVER3_T
9263: );
9264:
9265: END LOOP;
9266: -- Added INDEX for bug 3828698
9283: OR rate_dangling_flag IS NOT NULL )
9284: )
9285: AND budget_version_id NOT IN
9286: ( SELECT distinct plan_version_id
9287: FROM PJI_FM_EXTR_PLNVER3_T
9288: WHERE time_dangling_flag IS NOT NULL
9289: );
9290:
9291: UPDATE pa_budget_versions
9300: OR rate_dangling_flag IS NOT NULL )
9301: )
9302: OR budget_version_id in
9303: ( SELECT distinct plan_version_id
9304: FROM PJI_FM_EXTR_PLNVER3_T
9305: WHERE time_dangling_flag IS NOT NULL
9306: ); */
9307:
9308: END IF;
9420: BEGIN
9421:
9422: print_time('.......CLEANUP_INTERIM_TABLES: Begin. ');
9423:
9424: DELETE FROM PJI_FM_EXTR_PLNVER3_T ; -- No worker id join needed for online case.
9425:
9426: print_time('.......ver3 rows deleted: #= ' || sql%rowcount);
9427:
9428: DELETE FROM pji_fp_aggr_pjp1_t ; -- No worker id join needed for online case.
9477: BEGIN
9478:
9479: SELECT MAX(plan_version_id)
9480: INTO x_max_plnver_id
9481: FROM pji_fm_extr_plnver3_t;
9482:
9483: EXCEPTION
9484: WHEN NO_DATA_FOUND THEN
9485: RETURN;
9578: ) period_type
9579: FROM pa_implementations_all imp
9580: , gl_sets_of_books sob
9581: , pa_projects_all proj
9582: , pji_fm_extr_plnver3_t ver
9583: , ( SELECT 1 cal_type FROM DUAL -- 1 GL, 2 PA.
9584: UNION ALL
9585: SELECT 2 FROM DUAL
9586: ) split_cal