[Home] [Help]
93: LAST_UPDATED_BY,
94: CREATION_DATE,
95: CREATED_BY,
96: LAST_UPDATE_LOGIN
97: FROM MSC_NET_RESOURCE_AVAIL
98: WHERE plan_id=g_plan_id
99: AND organization_id = g_org_id
100: AND sr_instance_id =g_instance_id
101: AND department_id = g_department_id
108: order by from_time;
109:
110: CURSOR date_range IS
111: SELECT distinct mra.shift_date
112: FROM msc_net_resource_avail mra,
113: msc_form_query mfq
114: WHERE mra.plan_id = g_plan_id
115: and mra.organization_id = g_org_id
116: and mra.sr_instance_id = g_instance_id
174: g_shift_date :=to_date(null);
175: g_shift_number :=null;
176:
177: -- load from mrp_form_query for the changes, if the resource changes
178: -- re-query data from msc_net_resource_avail
179:
180: OPEN C_MFQ;
181: LOOP
182: FETCH C_MFQ INTO g_change_rec;
298: end if;
299:
300: end if;
301:
302: msc_undo.store_undo(4, --means msc_net_resource_avail
303: undo_type, --2 is update , 1 is insert a record
304: g_tmp_tab(k).transaction_id,
305: g_plan_id,
306: g_instance_id,
783: END IF;
784:
785: END IF;
786:
787: ELSE --user enters a resource which is not in msc_net_resource_avail table
788: g_tmp_tab.delete;
789: add_new_record(0,false,false);
790: v_undo_id :=insert_undo_data(1);
791: END IF;
795: END calculate_change;
796:
797:
798: ------------------------------------------------------------------------
799: --to update msc_net_resource_avail table
800: -----------------------------------------------------------------------------
801: PROCEDURE update_table IS
802: CURSOR bucket IS
803: SELECT mpb.bkt_start_date, mpb.bkt_end_date
822: /*
823: dbms_output.put_line('del for tran='||to_char(g_res_tab(m).transaction_id));
824: dbms_output.put_line('capacity_units='||to_char(g_res_tab(m).capacity_units));
825: */
826: delete from msc_net_resource_avail
827: where plan_id = g_plan_id
828: and transaction_id = g_res_tab(m).transaction_id
829: and g_res_tab(m).from_time <> g_res_tab(m).to_time;
830: end loop;
828: and transaction_id = g_res_tab(m).transaction_id
829: and g_res_tab(m).from_time <> g_res_tab(m).to_time;
830: end loop;
831:
832: update msc_net_resource_avail
833: set capacity_units = -1,
834: status =0,
835: applied =2,
836: from_time = from_time+1,
856: dbms_output.put_line('to='||to_char(g_res_tab(m).to_time));
857: dbms_output.put_line('simulation='||to_char(g_res_tab(m).simulation_set));
858: dbms_output.put_line('capacity_units='||to_char(g_res_tab(m).capacity_units));
859: */
860: INSERT INTO msc_net_resource_avail
861: (plan_id,
862: parent_id,
863: transaction_id,
864: organization_id,
915: select round(sum(decode(sign(to_time-from_time),1,(to_time - from_time),
916: (to_time+86400-from_time)
917: )/3600*capacity_units),6)
918: into v_capacity_units
919: from msc_net_resource_avail
920: where plan_id = g_plan_id
921: and organization_id = g_org_id
922: and sr_instance_id = g_instance_id
923: AND department_id = g_department_id
931:
932: v_capacity_units := nvl(v_capacity_units,0);
933:
934: -- update the resource units for parent record
935: update msc_net_resource_avail
936: set capacity_units = v_capacity_units,
937: status =0,
938: applied =2,
939: updated =2
945: and shift_date = v_start_date
946: and parent_id =-1;
947:
948: -- update the parent_id for the added record
949: update msc_net_resource_avail
950: set parent_id = g_res_tab(1).parent_id
951: where plan_id = g_plan_id
952: and organization_id = g_org_id
953: and sr_instance_id = g_instance_id
957:
958: END update_table;
959:
960: ------------------------------------------------------------------------
961: --to get transaction_id from msc_net_resource_avail table
962: -----------------------------------------------------------------------------
963: FUNCTION get_transaction_id RETURN NUMBER IS
964: v_transaction_id NUMBER;
965: BEGIN
962: -----------------------------------------------------------------------------
963: FUNCTION get_transaction_id RETURN NUMBER IS
964: v_transaction_id NUMBER;
965: BEGIN
966: select msc_net_resource_avail_s.nextval
967: into v_transaction_id
968: from dual;
969:
970: return v_transaction_id;
970: return v_transaction_id;
971: END;
972:
973: ------------------------------------------------------------------------
974: --to get transaction_id from msc_net_resource_avail table
975: -----------------------------------------------------------------------------
976: PROCEDURE add_new_record(m NUMBER, retain_old boolean default false,
977: retain_id boolean default false) IS
978: BEGIN
1049: v_instance_id := v_res_table.instance_id;
1050:
1051: -- delete old parent record first
1052:
1053: delete from msc_net_resource_avail
1054: where plan_id = v_plan_id
1055: AND organization_id = v_org_id
1056: AND sr_instance_id =v_instance_id
1057: AND department_id = v_dept_id
1122: if p_org_instance_list is not null then
1123: sql_statement :=
1124: 'SELECT distinct department_id, resource_id, '||
1125: 'organization_id, sr_instance_id '||
1126: 'FROM msc_net_resource_avail '||
1127: 'WHERE plan_id = '||to_char(v_plan_id) ||
1128: ' AND nvl(parent_id, 0) <> -1 ' ||
1129: ' AND (sr_instance_id, organization_id) in ('||
1130: p_org_instance_list ||')' || where_statement;
1131: else
1132: sql_statement :=
1133: 'SELECT distinct department_id, resource_id, '||
1134: 'organization_id, sr_instance_id '||
1135: 'FROM msc_net_resource_avail '||
1136: 'WHERE plan_id = '||to_char(v_plan_id) ||
1137: ' AND nvl(parent_id, 0) <> -1 ' || where_statement;
1138: end if;
1139:
1180: dummy number;
1181:
1182: CURSOR parent_record IS
1183: select 1
1184: from msc_net_resource_avail
1185: where plan_id = v_plan_id
1186: and sr_instance_id = p_instance_id
1187: and organization_id = p_org_id
1188: and department_id = p_dept_id
1193: CURSOR time_record(v_start_date DATE, v_end_date DATE) IS
1194: select sum(decode(sign(to_time-from_time),-1,(to_time+86400 - from_time),
1195: (to_time-from_time)
1196: )/3600*capacity_units)
1197: from msc_net_resource_avail
1198: where plan_id = v_plan_id
1199: and sr_instance_id = p_instance_id
1200: and organization_id = p_org_id
1201: AND department_id = p_dept_id
1216: AND resource_id = p_res_id;
1217: /*
1218: CURSOR agg_record(v_start_date DATE, v_end_date DATE) IS
1219: select sum(capacity_units)
1220: from msc_net_resource_avail
1221: where plan_id = v_plan_id
1222: and sr_instance_id = p_instance_id
1223: and organization_id = p_org_id
1224: AND department_id = p_dept_id
1273: -- we will insert one for each bucket, even the resource_units is 0
1274:
1275: -- if nvl(v_new_capacity_units,0) <>0 then
1276:
1277: select msc_net_resource_avail_s.nextval
1278: into v_transaction_id
1279: from dual;
1280:
1281: -- insert parent record
1279: from dual;
1280:
1281: -- insert parent record
1282:
1283: insert into msc_net_resource_avail
1284: ( TRANSACTION_ID,
1285: parent_id,
1286: PLAN_ID ,
1287: ORGANIZATION_ID,
1310: sysdate,
1311: 1);
1312:
1313: -- now update the parent_id for the child records
1314: update msc_net_resource_avail
1315: set parent_id = v_transaction_id
1316: where plan_id = v_plan_id
1317: and sr_instance_id = p_instance_id
1318: and organization_id = p_org_id
1337: select organization_id,
1338: department_id,
1339: resource_id,
1340: shift_date
1341: from msc_net_resource_avail
1342: where plan_id = p_plan_id
1343: and transaction_id = p_transaction_id
1344: and sr_instance_id = p_instance_id;
1345:
1376: select round(sum(decode(sign(to_time-from_time),1,(to_time - from_time),
1377: (to_time+86400-from_time)
1378: )/3600*capacity_units),6)
1379: into v_capacity_units
1380: from msc_net_resource_avail
1381: where plan_id = p_plan_id
1382: and organization_id = v_org_id
1383: and sr_instance_id = p_instance_id
1384: AND department_id = v_dept_id
1392:
1393: v_capacity_units := nvl(v_capacity_units,0);
1394:
1395: -- update the resource units for parent record
1396: update msc_net_resource_avail
1397: set capacity_units = v_capacity_units,
1398: status =0,
1399: applied =2,
1400: updated =2
1685: p_end date;
1686:
1687: cursor avail_c is
1688: select shift_date, from_time, to_time, capacity_units
1689: from msc_net_resource_avail
1690: where plan_id = p_plan_id
1691: and organization_id = p_org_id
1692: and sr_instance_id = p_inst_id
1693: and department_id = p_dept_id
1698: order by shift_date, from_time, to_time;
1699:
1700: cursor infinite_c is
1701: select 1
1702: from msc_net_resource_avail
1703: where plan_id = p_plan_id
1704: and organization_id = p_org_id
1705: and sr_instance_id = p_inst_id
1706: and department_id = p_dept_id
1856: order by operation_seq_num,resource_seq_num;
1857:
1858: cursor avail_c is
1859: select shift_date, from_time, to_time, capacity_units
1860: from msc_net_resource_avail
1861: where plan_id = p_plan_id
1862: and organization_id = p_org_id
1863: and sr_instance_id = p_inst_id
1864: and department_id = p_dept_id