[Home] [Help]
107: CREATED_BY,
108: LAST_UPDATE_LOGIN,
109: shift_date,
110: shift_num
111: FROM MSC_NET_RESOURCE_AVAIL
112: WHERE plan_id=g_plan_id
113: AND organization_id = g_org_id
114: AND sr_instance_id =g_instance_id
115: AND department_id = g_department_id
155: order by from_time;
156:
157: CURSOR date_range IS
158: SELECT distinct mra.shift_date
159: FROM msc_net_resource_avail mra,
160: msc_form_query mfq
161: WHERE mra.plan_id = g_plan_id
162: and mra.organization_id = g_org_id
163: and mra.sr_instance_id = g_instance_id
222: g_shift_date :=to_date(null);
223: g_shift_number :=null;
224:
225: -- load from mrp_form_query for the changes, if the resource changes
226: -- re-query data from msc_net_resource_avail
227:
228: OPEN C_MFQ;
229: LOOP
230: FETCH C_MFQ INTO g_change_rec;
375: end if;
376:
377: end if;
378:
379: msc_undo.store_undo(4, --means msc_net_resource_avail
380: undo_type, --2 is update , 1 is insert a record
381: g_tmp_tab(k).transaction_id,
382: g_plan_id,
383: g_instance_id,
447: net_res_columns(i).column_type := 'NUMBER';
448: net_res_columns(i).new_value := to_char(v_new_units);
449: end if;
450:
451: msc_undo.store_undo(8, --means msc_net_resource_avail
452: undo_type, --2 is update , 1 is insert a record
453: v_trx_id,
454: g_plan_id,
455: g_instance_id,
929: v_undo_id :=insert_undo_data(1);
930: END IF;
931: END IF;
932:
933: ELSE --user enters a resource which is not in msc_net_resource_avail table
934: g_tmp_tab.delete;
935: add_new_record(0,false,false);
936: v_undo_id :=insert_undo_data(1);
937: END IF;
948: from dual;
949: return v_trx_id;
950: END;
951: ------------------------------------------------------------------------
952: --to update msc_net_resource_avail table
953: -----------------------------------------------------------------------------
954: PROCEDURE update_table IS
955: CURSOR bucket IS
956: SELECT mpb.bkt_start_date, mpb.bkt_end_date
979: /*
980: dbms_output.put_line('del for tran='||to_char(g_res_tab(m).transaction_id));
981: dbms_output.put_line('capacity_units='||to_char(g_res_tab(m).capacity_units));
982: */
983: delete from msc_net_resource_avail
984: where plan_id = g_plan_id
985: and transaction_id = g_res_tab(m).transaction_id
986: and g_res_tab(m).from_time <> g_res_tab(m).to_time;
987: end loop;
985: and transaction_id = g_res_tab(m).transaction_id
986: and g_res_tab(m).from_time <> g_res_tab(m).to_time;
987: end loop;
988:
989: update msc_net_resource_avail
990: set capacity_units = -1,
991: status =0,
992: applied =2,
993: from_time = from_time+1,
1052: end if;
1053: end if;
1054: --dbms_output.put_line('capacity_units 2 ='||l_units);
1055:
1056: INSERT INTO msc_net_resource_avail
1057: (plan_id,
1058: parent_id,
1059: transaction_id,
1060: organization_id,
1177: select round(sum(decode(sign(to_time-from_time),1,(to_time - from_time),
1178: (to_time+86400-from_time)
1179: )/3600*capacity_units),6)
1180: into v_capacity_units
1181: from msc_net_resource_avail
1182: where plan_id = g_plan_id
1183: and organization_id = g_org_id
1184: and sr_instance_id = g_instance_id
1185: AND department_id = g_department_id
1193:
1194: v_capacity_units := nvl(v_capacity_units,0);
1195:
1196: -- update the resource units for parent record
1197: update msc_net_resource_avail
1198: set capacity_units = v_capacity_units,
1199: status =0,
1200: applied =2,
1201: updated =2
1207: and shift_date = v_start_date
1208: and parent_id =-1;
1209:
1210: -- update the parent_id for the added record
1211: update msc_net_resource_avail
1212: set parent_id = g_res_tab(1).parent_id
1213: where plan_id = g_plan_id
1214: and organization_id = g_org_id
1215: and sr_instance_id = g_instance_id
1219:
1220: END update_table;
1221:
1222: ------------------------------------------------------------------------
1223: --to get transaction_id from msc_net_resource_avail table
1224: -----------------------------------------------------------------------------
1225: FUNCTION get_transaction_id RETURN NUMBER IS
1226: v_transaction_id NUMBER;
1227: BEGIN
1224: -----------------------------------------------------------------------------
1225: FUNCTION get_transaction_id RETURN NUMBER IS
1226: v_transaction_id NUMBER;
1227: BEGIN
1228: select msc_net_resource_avail_s.nextval
1229: into v_transaction_id
1230: from dual;
1231:
1232: return v_transaction_id;
1232: return v_transaction_id;
1233: END;
1234:
1235: ------------------------------------------------------------------------
1236: --to get transaction_id from msc_net_resource_avail table
1237: -----------------------------------------------------------------------------
1238: PROCEDURE add_new_record(m NUMBER, retain_old boolean default false,
1239: retain_id boolean default false) IS
1240:
1327: v_instance_id := v_res_table.instance_id;
1328:
1329: -- delete old parent record first
1330:
1331: delete from msc_net_resource_avail
1332: where plan_id = v_plan_id
1333: AND organization_id = v_org_id
1334: AND sr_instance_id =v_instance_id
1335: AND department_id = v_dept_id
1400: if p_org_instance_list is not null then
1401: sql_statement :=
1402: 'SELECT distinct department_id, resource_id, '||
1403: 'organization_id, sr_instance_id '||
1404: 'FROM msc_net_resource_avail '||
1405: 'WHERE plan_id = '||to_char(v_plan_id) ||
1406: ' AND nvl(parent_id, 0) <> -1 ' ||
1407: ' AND (sr_instance_id, organization_id) in ('||
1408: p_org_instance_list ||')' || where_statement;
1409: else
1410: sql_statement :=
1411: 'SELECT distinct department_id, resource_id, '||
1412: 'organization_id, sr_instance_id '||
1413: 'FROM msc_net_resource_avail '||
1414: 'WHERE plan_id = '||to_char(v_plan_id) ||
1415: ' AND nvl(parent_id, 0) <> -1 ' || where_statement;
1416: end if;
1417:
1458: dummy number;
1459:
1460: CURSOR parent_record IS
1461: select 1
1462: from msc_net_resource_avail
1463: where plan_id = v_plan_id
1464: and sr_instance_id = p_instance_id
1465: and organization_id = p_org_id
1466: and department_id = p_dept_id
1471: CURSOR time_record(v_start_date DATE, v_end_date DATE) IS
1472: select sum(decode(sign(to_time-from_time),-1,(to_time+86400 - from_time),
1473: (to_time-from_time)
1474: )/3600*capacity_units)
1475: from msc_net_resource_avail
1476: where plan_id = v_plan_id
1477: and sr_instance_id = p_instance_id
1478: and organization_id = p_org_id
1479: AND department_id = p_dept_id
1494: AND resource_id = p_res_id;
1495: /*
1496: CURSOR agg_record(v_start_date DATE, v_end_date DATE) IS
1497: select sum(capacity_units)
1498: from msc_net_resource_avail
1499: where plan_id = v_plan_id
1500: and sr_instance_id = p_instance_id
1501: and organization_id = p_org_id
1502: AND department_id = p_dept_id
1551: -- we will insert one for each bucket, even the resource_units is 0
1552:
1553: -- if nvl(v_new_capacity_units,0) <>0 then
1554:
1555: select msc_net_resource_avail_s.nextval
1556: into v_transaction_id
1557: from dual;
1558:
1559: -- insert parent record
1557: from dual;
1558:
1559: -- insert parent record
1560:
1561: insert into msc_net_resource_avail
1562: ( TRANSACTION_ID,
1563: parent_id,
1564: PLAN_ID ,
1565: ORGANIZATION_ID,
1588: sysdate,
1589: 1);
1590:
1591: -- now update the parent_id for the child records
1592: update msc_net_resource_avail
1593: set parent_id = v_transaction_id
1594: where plan_id = v_plan_id
1595: and sr_instance_id = p_instance_id
1596: and organization_id = p_org_id
1615: select organization_id,
1616: department_id,
1617: resource_id,
1618: shift_date
1619: from msc_net_resource_avail
1620: where plan_id = p_plan_id
1621: and transaction_id = p_transaction_id
1622: and sr_instance_id = p_instance_id;
1623:
1654: select round(sum(decode(sign(to_time-from_time),1,(to_time - from_time),
1655: (to_time+86400-from_time)
1656: )/3600*capacity_units),6)
1657: into v_capacity_units
1658: from msc_net_resource_avail
1659: where plan_id = p_plan_id
1660: and organization_id = v_org_id
1661: and sr_instance_id = p_instance_id
1662: AND department_id = v_dept_id
1670:
1671: v_capacity_units := nvl(v_capacity_units,0);
1672:
1673: -- update the resource units for parent record
1674: update msc_net_resource_avail
1675: set capacity_units = v_capacity_units,
1676: status =0,
1677: applied =2,
1678: updated =2
1963: p_end date;
1964:
1965: cursor avail_c is
1966: select shift_date, from_time, to_time, capacity_units
1967: from msc_net_resource_avail
1968: where plan_id = p_plan_id
1969: and organization_id = p_org_id
1970: and sr_instance_id = p_inst_id
1971: and department_id = p_dept_id
1976: order by shift_date, from_time, to_time;
1977:
1978: cursor infinite_c is
1979: select 1
1980: from msc_net_resource_avail
1981: where plan_id = p_plan_id
1982: and organization_id = p_org_id
1983: and sr_instance_id = p_inst_id
1984: and department_id = p_dept_id
2141: order by operation_seq_num,resource_seq_num;
2142:
2143: cursor avail_c is
2144: select shift_date, from_time, to_time, capacity_units
2145: from msc_net_resource_avail
2146: where plan_id = p_plan_id
2147: and organization_id = p_org_id
2148: and sr_instance_id = p_inst_id
2149: and department_id = p_dept_id