[Home] [Help]
544: -- Called by Create Plan UI. This procedure will identify if ther
545: -- is a free partition available in MSC_APPS_INSTANCES. If yes then
546: -- it returns the plan_id. Otherwise it create a new partition by
547: -- performing DDL on all the partitioned tables. It store the new
548: -- plan_id in MSC_PLAN_PARTITIONS, marks it as being used and returns it
549: -- to the calling UI
550:
551: FUNCTION get_plan (p_plan_name IN VARCHAR2,
552: x_return_status OUT NOCOPY VARCHAR2,
553: x_msg_data OUT NOCOPY VARCHAR2) RETURN NUMBER IS
554:
555: CURSOR C_FREE_PLAN IS
556: SELECT plan_id
557: FROM MSC_PLAN_PARTITIONS
558: WHERE free_flag = 1
559: FOR UPDATE;
560:
561:
560:
561:
562: CURSOR C_PLAN_COUNT IS
563: SELECT count(*)
564: FROM MSC_PLAN_PARTITIONS;
565:
566: CURSOR C_MAX_PLAN IS
567: SELECT max(plan_id)
568: FROM MSC_PLAN_PARTITIONS;
564: FROM MSC_PLAN_PARTITIONS;
565:
566: CURSOR C_MAX_PLAN IS
567: SELECT max(plan_id)
568: FROM MSC_PLAN_PARTITIONS;
569:
570: l_plan_id NUMBER;
571: l_max_plan NUMBER;
572: l_plan_count NUMBER;
645: X_user_id := to_number(FND_GLOBAL.User_Id);
646: X_Login_Id := to_number(FND_GLOBAL.Login_Id);
647:
648: begin
649: INSERT INTO MSC_PLAN_PARTITIONS (
650: PLAN_ID,
651: PLAN_NAME,
652: FREE_FLAG,
653: PARTITION_NUMBER,
686: --
687: -- dbms_output.put_line('Found a free plan'||p_plan_name);
688: BEGIN
689: if check_partition_pvt(l_plan_id) = FND_API.G_RET_STS_SUCCESS then
690: UPDATE MSC_PLAN_PARTITIONS
691: SET plan_name = p_plan_name,
692: free_flag = 2
693: WHERE plan_id = l_plan_id;
694: EXIT;
692: free_flag = 2
693: WHERE plan_id = l_plan_id;
694: EXIT;
695: ELSE
696: UPDATE MSC_PLAN_PARTITIONS
697: SET plan_name = '*UNUSABLE*',
698: free_flag = 2
699: WHERE plan_id = l_plan_id;
700: END IF;
872: l_id NUMBER;
873:
874: CURSOR Plan_exists_C(P_plan_id IN NUMBER) IS
875: SELECT count(*)
876: FROM MSC_PLAN_PARTITIONS
877: WHERE plan_id = P_plan_id;
878:
879: CURSOR Instance_exists_C(P_instance_id IN NUMBER) IS
880: SELECT count(*)
891:
892: if (P_is_plan = SYS_YES) then
893: l_id := P_plan_id;
894: --
895: -- now see if the plan/instance id exists in msc_plan_partitions
896: --
897: OPEN Plan_exists_C(P_plan_id);
898: FETCH Plan_exists_C INTO l_count;
899: CLOSE Plan_exists_C;
943:
944: if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
945: begin
946:
947: UPDATE MSC_PLAN_PARTITIONS
948: SET free_flag = 1
949: WHERE plan_id = P_plan_id;
950: exception
951: when others then
989:
990:
991: begin
992: if (plan = SYS_YES) then
993: INSERT INTO MSC_PLAN_PARTITIONS (
994: PLAN_ID,
995: PLAN_NAME,
996: FREE_FLAG,
997: PARTITION_NUMBER,
1086: -- dbms_output.put_line(sql_stmt);
1087: -- execute immediate sql_stmt;
1088:
1089: if (plan = SYS_YES) then
1090: DELETE FROM MSC_PLAN_PARTITIONS
1091: WHERE plan_id = partition_num;
1092: commit;
1093: else
1094: DELETE FROM MSC_INST_PARTITIONS
1161: if (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
1162: exit;
1163: end if;
1164:
1165: INSERT INTO MSC_PLAN_PARTITIONS (
1166: PLAN_ID,
1167: PLAN_NAME,
1168: FREE_FLAG,
1169: PARTITION_NUMBER,
1210: if (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
1211: exit;
1212: end if;
1213:
1214: INSERT INTO MSC_PLAN_PARTITIONS (
1215: PLAN_ID,
1216: PLAN_NAME,
1217: FREE_FLAG,
1218: PARTITION_NUMBER,
1283: END LOOP;
1284:
1285: CLOSE C_PLAN;
1286:
1287: DELETE FROM MSC_PLAN_PARTITIONS;
1288: retcode := G_SUCCESS;
1289:
1290:
1291: END drop_exist_plan_partitions;
1374: part_number NUMBER;
1375: BEGIN
1376: SELECT partition_number
1377: INTO part_number
1378: FROM MSC_PLAN_PARTITIONS
1379: WHERE plan_id = x_plan_id;
1380:
1381: retcode := G_SUCCESS;
1382: return part_number;
1600: X_Login_Id := to_number(FND_GLOBAL.Login_Id);
1601:
1602:
1603: begin
1604: INSERT INTO MSC_PLAN_PARTITIONS (
1605: PLAN_ID,
1606: PLAN_NAME,
1607: FREE_FLAG,
1608: PARTITION_NUMBER,