[Home] [Help]
421: and b.bucket_index>0
422: and nvl(mra.parent_id,0) <> -1
423: and not exists (
424: select 1
425: from msc_res_plan_updates a2
426: where mra.plan_id = a2.plan_id
427: and mra.sr_instance_id = a2.sr_instance_id
428: and mra.organization_id = a2.organization_id
429: and mra.department_id = a2.department_id
447: to_number(null) planned_order,
448: to_number(null) work_order,
449: to_number(null) total_resource_cost
450: from
451: msc_res_plan_updates a2,
452: msc_hp_col_dtls b,
453: msc_hp_row_dtls h
454: where h.query_id = p_query_id
455: and a2.query_id = h.query_id
676: parent_id,
677: decode(from_time, null, 0,
678: (decode(sign(to_time-from_time),
679: -1, to_time+86400, to_time) - from_time)/3600) shift_capacity
680: from msc_res_plan_updates
681: where query_id = p_query_id
682: and plan_id = p_plan_id
683: and sr_instance_id = p_sr_instance_id
684: and organization_id = p_organization_id
718: ' and md.calendar_code = '''||l_calendar_code||''''||
719: ' and md.exception_set_id = -1'||
720: ' and nvl(mra.parent_id(+),0) <> -1');
721: */
722: delete from msc_res_plan_updates
723: where query_id = p_query_id
724: and sr_instance_id = ru.sr_instance_id
725: and organization_id = ru.organization_id
726: and department_id = ru.department_id
725: and organization_id = ru.organization_id
726: and department_id = ru.department_id
727: and resource_id = ru.resource_id
728: and shift_date between ru.start_date and ru.end_date;
729: msc_phub_util.log('delete from msc_res_plan_updates: '||sql%rowcount);
730: commit;
731:
732: insert into msc_res_plan_updates (
733: query_id,
728: and shift_date between ru.start_date and ru.end_date;
729: msc_phub_util.log('delete from msc_res_plan_updates: '||sql%rowcount);
730: commit;
731:
732: insert into msc_res_plan_updates (
733: query_id,
734: plan_id,
735: transaction_id,
736: sr_instance_id,
775: and md.calendar_date between ru.start_date and ru.end_date
776: and md.calendar_code = l_calendar_code
777: and md.exception_set_id = -1
778: and nvl(mra.parent_id(+),0) <> -1;
779: msc_phub_util.log('insert into msc_res_plan_updates: '||sql%rowcount);
780: commit;
781:
782: select
783: sum(nvl(decode(from_time, null, 0,
784: (decode(sign(to_time-from_time),
785: -1, to_time+86400, to_time) - from_time)/3600) * capacity_units, 0))
786: total_capacity
787: into l_total_capacity
788: from msc_res_plan_updates
789: where query_id = p_query_id
790: and plan_id = ru.plan_id
791: and sr_instance_id = ru.sr_instance_id
792: and organization_id = ru.organization_id
796:
797:
798: /*
799: msc_phub_util.log(
800: ' from msc_res_plan_updates'||
801: ' where query_id = '||p_query_id||
802: ' and plan_id = '||ru.plan_id||
803: ' and sr_instance_id = '||ru.sr_instance_id||
804: ' and organization_id = '||ru.organization_id||
830:
831: msc_phub_util.log('D1:'||r.shift_num||','||r.shift_date||','||round(r.from_time)||','||round(r.to_time)||','||
832: round((r.to_time2 - r.from_time2)*24)||','||r.capacity_units);
833:
834: update msc_res_plan_updates
835: set from_time = r.from_time,
836: from_time2 = r.from_time2,
837: to_time = r.to_time,
838: to_time2 = r.to_time2,
847:
848: msc_phub_util.log('D2:'||r.shift_num||','||r.shift_date||','||round(r.from_time)||','||round(r.to_time)||','||
849: round((r.to_time2 - r.from_time2)*24)||','||r.capacity_units);
850:
851: update msc_res_plan_updates
852: set to_time = r.to_time,
853: to_time2 = r.to_time2,
854: change_flag = 1,
855: last_update_date = sysdate,
891:
892: if (r.prev_to_time2 is null) then
893: select max(to_time2)
894: into r.prev_to_time2
895: from msc_res_plan_updates
896: where query_id = p_query_id
897: and sr_instance_id = r.sr_instance_id
898: and organization_id = r.organization_id
899: and department_id = r.department_id
939: -- extend end time
940: if (r.next_from_time2 is null) then
941: select min(from_time2)
942: into r.next_from_time2
943: from msc_res_plan_updates
944: where query_id = p_query_id
945: and sr_instance_id = r.sr_instance_id
946: and organization_id = r.organization_id
947: and department_id = r.department_id
981: msc_phub_util.log('1B:'||r.shift_num||','||r.shift_date||','||round(l_change)||','||round(r.from_time)||','||round(r.to_time)||','||
982: round((r.to_time2 - r.from_time2)*24)||','||r.capacity_units||','||round(l_total_capacity)||','||round(l_difference/3600));
983: end if;
984:
985: update msc_res_plan_updates
986: set from_time = r.from_time,
987: from_time2 = r.from_time2,
988: to_time = r.to_time,
989: to_time2 = r.to_time2,
1026:
1027: msc_phub_util.log('2:'||r.shift_num||','||r.shift_date||','||round(r.from_time)||','||round(r.to_time)||','||
1028: round((r.to_time2 - r.from_time2)*24)||','||r.capacity_units||','||round(l_total_capacity)||','||round(l_difference/3600));
1029:
1030: update msc_res_plan_updates
1031: set from_time = r.from_time,
1032: from_time2 = r.from_time2,
1033: to_time = r.to_time,
1034: to_time2 = r.to_time2,
1073: msc_phub_util.log('3B:'||r.shift_num||','||r.shift_date||','||round(r.from_time)||','||round(r.to_time)||','||
1074: round((r.to_time2 - r.from_time2)*24)||','||r.capacity_units||','||round(l_total_capacity)||','||round(l_difference/3600));
1075: end if;
1076:
1077: update msc_res_plan_updates
1078: set from_time = r.from_time,
1079: from_time2 = r.from_time2,
1080: to_time = r.to_time,
1081: to_time2 = r.to_time2,
1129: delete from msc_hp_updates where query_id = p_query_id;
1130: msc_phub_util.log('delete from msc_hp_updates: '||sql%rowcount);
1131: commit;
1132:
1133: delete from msc_res_plan_updates where query_id = p_query_id;
1134: msc_phub_util.log('delete from msc_res_plan_updates: '||sql%rowcount);
1135: commit;
1136: end close_data;
1137:
1130: msc_phub_util.log('delete from msc_hp_updates: '||sql%rowcount);
1131: commit;
1132:
1133: delete from msc_res_plan_updates where query_id = p_query_id;
1134: msc_phub_util.log('delete from msc_res_plan_updates: '||sql%rowcount);
1135: commit;
1136: end close_data;
1137:
1138: procedure save_changes(p_query_id number)
1154: to_time2,
1155: parent_id,
1156: change_flag,
1157: capacity_units
1158: from msc_res_plan_updates
1159: where query_id = p_query_id
1160: and change_flag = 1
1161: for update nowait;
1162:
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;
1296: msc_phub_util.log('update msc_res_plan_updates: '||sql%rowcount);
1297: else
1292:
1293: update msc_res_plan_updates
1294: set transaction_id=r3.transaction_id
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,
1326: end loop;
1327: commit;
1328:
1329: /*
1330: update msc_res_plan_updates
1331: set change_flag = 2
1332: where query_id = p_query_id
1333: and change_flag = 1;
1334: msc_phub_util.log('update msc_res_plan_updates: '||sql%rowcount);
1330: update msc_res_plan_updates
1331: set change_flag = 2
1332: where query_id = p_query_id
1333: and change_flag = 1;
1334: msc_phub_util.log('update msc_res_plan_updates: '||sql%rowcount);
1335: commit;
1336: */
1337:
1338: delete from msc_res_plan_updates
1334: msc_phub_util.log('update msc_res_plan_updates: '||sql%rowcount);
1335: commit;
1336: */
1337:
1338: delete from msc_res_plan_updates
1339: where query_id = p_query_id;
1340: msc_phub_util.log('delete from msc_res_plan_updates: '||sql%rowcount);
1341: commit;
1342:
1336: */
1337:
1338: delete from msc_res_plan_updates
1339: where query_id = p_query_id;
1340: msc_phub_util.log('delete from msc_res_plan_updates: '||sql%rowcount);
1341: commit;
1342:
1343:
1344: end save_changes;