[Home] [Help]
403: to_number(null) planned_order,
404: to_number(null) work_order,
405: to_number(null) total_resource_cost
406: from
407: msc_net_resource_avail mra,
408: msc_hp_col_dtls b,
409: msc_hp_row_dtls h
410: where h.query_id = p_query_id
411: and mra.plan_id = h.plan_id
706: ru.start_date||','||ru.end_date);
707:
708: /*
709: msc_phub_util.log(
710: ' from msc_net_resource_avail mra, msc_calendar_dates md'||
711: ' where mra.plan_id(+) = '||ru.plan_id||
712: ' and mra.sr_instance_id(+) = '||ru.sr_instance_id||
713: ' and mra.organization_id(+) = '||ru.organization_id||
714: ' and mra.department_id(+) = '||ru.department_id||
764: mra.parent_id,
765: mra.capacity_units,
766: fnd_global.user_id, sysdate,
767: sysdate, fnd_global.user_id, fnd_global.login_id
768: from msc_net_resource_avail mra, msc_calendar_dates md
769: where mra.plan_id(+) = ru.plan_id
770: and mra.sr_instance_id(+) = ru.sr_instance_id
771: and mra.organization_id(+) = ru.organization_id
772: and mra.department_id(+) = ru.department_id
903:
904: if (r.prev_to_time2 is null) then
905: select max(shift_date+(decode(sign(to_time-from_time), -1, to_time+86400, to_time))/86400)
906: into r.prev_to_time2
907: from msc_net_resource_avail
908: where plan_id = r.plan_id
909: and sr_instance_id = r.sr_instance_id
910: and organization_id = r.organization_id
911: and department_id = r.department_id
951:
952: if (r.next_from_time2 is null) then
953: select min(shift_date+(from_time/86400))
954: into r.next_from_time2
955: from msc_net_resource_avail
956: where plan_id = r.plan_id
957: and sr_instance_id = r.sr_instance_id
958: and organization_id = r.organization_id
959: and department_id = r.department_id
1172: capacity_units,
1173: last_update_date,
1174: last_updated_by,
1175: last_update_login
1176: from msc_net_resource_avail
1177: where plan_id = p_plan_id
1178: and transaction_id = p_transaction_id
1179: and sr_instance_id = p_sr_instance_id
1180: for update nowait;
1188: capacity_units,
1189: last_update_date,
1190: last_updated_by,
1191: last_update_login
1192: from msc_net_resource_avail
1193: where plan_id = p_plan_id
1194: and transaction_id = p_parent_id
1195: and parent_id = -1
1196: and sr_instance_id = p_sr_instance_id
1205:
1206: if (r3.parent_id is null) then
1207: begin
1208: select transaction_id into r3.parent_id
1209: from msc_net_resource_avail
1210: where plan_id = r3.plan_id
1211: and sr_instance_id = r3.sr_instance_id
1212: and organization_id = r3.organization_id
1213: and department_id = r3.department_id
1216: and parent_id = -1;
1217:
1218: exception
1219: when no_data_found then
1220: select msc_net_resource_avail_s.nextval into r3.parent_id from dual;
1221:
1222: insert into msc_net_resource_avail (
1223: plan_id,
1224: transaction_id,
1218: exception
1219: when no_data_found then
1220: select msc_net_resource_avail_s.nextval into r3.parent_id from dual;
1221:
1222: insert into msc_net_resource_avail (
1223: plan_id,
1224: transaction_id,
1225: parent_id,
1226: sr_instance_id,
1244: (decode(sign(r3.to_time-r3.from_time),
1245: -1, r3.to_time+86400, r3.to_time) - r3.from_time)/3600),
1246: fnd_global.user_id, sysdate,
1247: sysdate, fnd_global.user_id, fnd_global.login_id);
1248: msc_phub_util.log('insert into msc_net_resource_avail (summary): '||sql%rowcount||', '||r3.parent_id);
1249: end;
1250: end if;
1251:
1252: if (r3.transaction_id = -1) then
1249: end;
1250: end if;
1251:
1252: if (r3.transaction_id = -1) then
1253: select msc_net_resource_avail_s.nextval into r3.transaction_id from dual;
1254:
1255: insert into msc_net_resource_avail (
1256: plan_id,
1257: transaction_id,
1251:
1252: if (r3.transaction_id = -1) then
1253: select msc_net_resource_avail_s.nextval into r3.transaction_id from dual;
1254:
1255: insert into msc_net_resource_avail (
1256: plan_id,
1257: transaction_id,
1258: parent_id,
1259: sr_instance_id,
1287: 2,
1288: 2,
1289: fnd_global.user_id, sysdate,
1290: sysdate, fnd_global.user_id, fnd_global.login_id);
1291: msc_phub_util.log('insert into msc_net_resource_avail (detail): '||sql%rowcount||', '||r3.transaction_id);
1292:
1293: update msc_res_plan_updates
1294: set transaction_id=r3.transaction_id
1295: where current of c3;
1295: where current of c3;
1296: msc_phub_util.log('update msc_res_plan_updates: '||sql%rowcount);
1297: else
1298: for r4 in c4(r3.plan_id, r3.transaction_id, r3.sr_instance_id) loop
1299: update msc_net_resource_avail
1300: set from_time = r3.from_time,
1301: to_time = r3.to_time,
1302: shift_num = r3.shift_num,
1303: capacity_units = r3.capacity_units,
1307: last_update_date = sysdate,
1308: last_updated_by = fnd_global.user_id,
1309: last_update_login = fnd_global.login_id
1310: where current of c4;
1311: msc_phub_util.log('update msc_net_resource_avail (detail): '||sql%rowcount||', '||r3.transaction_id);
1312: exit;
1313: end loop;
1314: end if;
1315:
1313: end loop;
1314: end if;
1315:
1316: for r5 in c5(r3.plan_id, r3.parent_id, r3.sr_instance_id) loop
1317: update msc_net_resource_avail
1318: set capacity_units = r3.capacity_units * decode(r3.from_time, null, 0,
1319: (decode(sign(r3.to_time-r3.from_time),
1320: -1, r3.to_time+86400, r3.to_time) - r3.from_time)/3600)
1321: where current of c5;
1318: set capacity_units = r3.capacity_units * decode(r3.from_time, null, 0,
1319: (decode(sign(r3.to_time-r3.from_time),
1320: -1, r3.to_time+86400, r3.to_time) - r3.from_time)/3600)
1321: where current of c5;
1322: msc_phub_util.log('update msc_net_resource_avail (summary): '||sql%rowcount||', '||r3.parent_id);
1323: exit;
1324: end loop;
1325:
1326: end loop;