[Home] [Help]
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;
118:
119:
120: TYPE num_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
121: l_tmp_plan_version_id num_tbl;
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
131: and nvl(resource_class_flag,'Y') = 'N'
132: UNION
133: SELECT /*+ ordered */ b.plan_version_id
134: FROM PJI_FM_EXTR_PLNVER3_T b, pa_proj_fp_options a, pa_resource_lists_all_bg c
135: WHERE b.project_id = a.project_id
136: AND b.PLAN_TYPE_ID = a.FIN_PLAN_TYPE_ID
137: AND b.plan_version_id = a.fin_plan_version_id
138: 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
1256: SELECT 1
1257: FROM pji_pjp_wbs_header
1258: WHERE (project_id, plan_type_id, plan_version_id) IN
1259: (SELECT DISTINCT project_id, plan_type_id, plan_version_id
1260: FROM pji_fm_extr_plnver3_t)
1261: FOR UPDATE;
1262:
1263: CURSOR c_get_hdrs_lock_map_cur IS
1264: SELECT /*+ index(hd, pji_pjp_wbs_header_n1) */ 1
1301: print_time( ' Invalid p_context, returning: ' || p_context);
1302: RETURN;
1303: END IF;
1304:
1305: DELETE FROM pji_fm_extr_plnver3_t;
1306:
1307: /*
1308: IF (p_context LIKE '%c_incr_act_etc%' AND p_update_mode = 'P') THEN
1309:
1306:
1307: /*
1308: IF (p_context LIKE '%c_incr_act_etc%' AND p_update_mode = 'P') THEN
1309:
1310: INSERT INTO pji_fm_extr_plnver3_t
1311: (project_id, plan_version_id, plan_type_id)
1312: SELECT / * + ORDERED * / -- INDEX(HD9, PJI_PJP_WBS_HEADER_N1)
1313: DISTINCT hd9.project_id, hd9.plan_version_id, hd9.plan_type_id
1314: FROM pji_pjp_proj_batch_map map
1345: */
1346:
1347: IF (p_context LIKE '%c_all_cb_co_above%' AND p_update_mode = 'P') THEN
1348:
1349: INSERT INTO pji_fm_extr_plnver3_t
1350: (project_id, plan_version_id, plan_type_id)
1351: SELECT /*+ ORDERED */
1352: DISTINCT hd9.project_id, hd9.plan_version_id, hd9.plan_type_id
1353: FROM pji_pjp_proj_batch_map map
1372: END IF;
1373:
1374: IF (p_context LIKE '%c_all_baselined_fps_generic%' AND p_update_mode = 'P') THEN
1375:
1376: INSERT INTO pji_fm_extr_plnver3_t
1377: (project_id, plan_version_id, plan_type_id)
1378: SELECT
1379: DISTINCT bv.project_id, bv.budget_version_id, bv.fin_plan_type_id
1380: FROM pji_pjp_proj_batch_map map
1391:
1392:
1393: IF (p_context LIKE '%c_all_plans_prg_chng%') THEN -- !! ONLY FOR PRG_CHANGE
1394:
1395: INSERT INTO pji_fm_extr_plnver3_t
1396: (project_id, plan_version_id, plan_type_id)
1397: SELECT DISTINCT hd9.project_id, hd9.plan_version_id, hd9.plan_type_id
1398: FROM -- pji_pjp_wbs_header hd2
1399: -- pa_proj_elem_ver_structure hd2
1419:
1420:
1421: IF (p_context LIKE '%c_all_cb_co_rbsprg_below%' AND p_update_mode = 'P') THEN
1422:
1423: INSERT INTO pji_fm_extr_plnver3_t
1424: (project_id, plan_version_id, plan_type_id)
1425: SELECT /*+ ordered */
1426: DISTINCT hd2.project_id, hd2.plan_version_id, hd2.plan_type_id
1427: FROM
1452:
1453:
1454: IF (p_context LIKE '%c_all_baselined_fps_rbsassoc%' AND p_update_mode = 'P') THEN
1455:
1456: INSERT INTO pji_fm_extr_plnver3_t
1457: (project_id, plan_version_id, plan_type_id)
1458: SELECT /*+ LEADING(MAP) */ /* leading hint added for bug 9337307 */
1459: DISTINCT hd9.project_id, hd9.plan_version_id, hd9.plan_type_id
1460: FROM pji_pjp_proj_batch_map map
1481:
1482:
1483: IF (p_context LIKE '%c_all_cb_co_rbsassoc_above_cur%' AND p_update_mode = 'P') THEN
1484:
1485: INSERT INTO pji_fm_extr_plnver3_t
1486: (project_id, plan_version_id, plan_type_id)
1487: SELECT DISTINCT hd9.project_id, hd9.plan_version_id, hd9.plan_type_id
1488: FROM pji_xbs_denorm den
1489: , pji_pjp_wbs_header hd1 -- SUB
1519:
1520:
1521: IF (p_context LIKE '%c_all_bslnd_fps_rbsprg_below%' AND p_update_mode = 'P') THEN
1522:
1523: INSERT INTO pji_fm_extr_plnver3_t
1524: (project_id, plan_version_id, plan_type_id)
1525: SELECT /*+ ordered */
1526: DISTINCT hd9.project_id, hd9.plan_version_id, hd9.plan_type_id
1527: FROM pji_pjp_proj_batch_map map
1555:
1556:
1557: IF (p_context LIKE '%c_all_cb_co_rbsprg_above%' AND p_update_mode = 'P') THEN
1558:
1559: INSERT INTO pji_fm_extr_plnver3_t
1560: (project_id, plan_version_id, plan_type_id)
1561: SELECT DISTINCT hd9.project_id, hd9.plan_version_id, hd9.plan_type_id
1562: FROM pji_xbs_denorm den
1563: , pji_pjp_wbs_header hd2 -- SUB
1586:
1587:
1588: IF (p_context LIKE '%c_dangl_fps_self%' AND p_update_mode = 'P') THEN
1589:
1590: INSERT INTO pji_fm_extr_plnver3_t
1591: (project_id, plan_version_id, plan_type_id)
1592: SELECT DISTINCT hd9.project_id, hd9.plan_version_id, hd9.plan_type_id
1593: FROM pji_pjp_proj_batch_map map
1594: , PA_BUDGET_VERSIONS bv
1609:
1610:
1611: IF (p_context LIKE '%c_dangl_cb_co_above%' AND p_update_mode = 'P') THEN
1612:
1613: INSERT INTO pji_fm_extr_plnver3_t
1614: (project_id, plan_version_id, plan_type_id)
1615: SELECT DISTINCT hd9.project_id, hd9.plan_version_id, hd9.plan_type_id
1616: FROM pji_xbs_denorm den
1617: , pji_pjp_wbs_header hd1 -- SUB
1654: , LAST_UPDATED_BY = l_last_updated_by
1655: , LAST_UPDATE_LOGIN = l_last_update_login
1656: WHERE (project_id, plan_type_id, plan_version_id) IN
1657: (SELECT DISTINCT project_id, plan_type_id, plan_version_id
1658: FROM pji_fm_extr_plnver3_t);
1659:
1660: print_time ( ' UPDATE_LOCKS # updated hdrs = ' || SQL%ROWCOUNT );
1661:
1662: DELETE FROM pji_fm_extr_plnver3_t;
1658: FROM pji_fm_extr_plnver3_t);
1659:
1660: print_time ( ' UPDATE_LOCKS # updated hdrs = ' || SQL%ROWCOUNT );
1661:
1662: DELETE FROM pji_fm_extr_plnver3_t;
1663: print_time ( ' # of records deleted in plnver3 = ' || SQL%ROWCOUNT );
1664:
1665:
1666: IF (p_update_mode IS NULL) THEN