[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure update_avail( var_rowid in ROWID,
var_date in DATE,
var_from_time in number,
var_to_time in number) is
var_time1 number;
UPDATE mrp_net_resource_avail
SET shift_date = var_date1,
from_time = var_time1,
to_time = var_time2
WHERE rowid = var_rowid;
end update_avail;
procedure delete_avail( var_rowid in ROWID) is
begin
/*
if var_gt_debug then
dbms_output.put_line('about to delete');
DELETE from mrp_net_resource_avail
WHERE rowid = var_rowid;
end delete_avail;
procedure insert_avail( var_date in DATE,
var_department_id in number,
var_resource_id in number,
var_instance_id in number,
var_serial_num in varchar2,
var_organization_id in number,
var_shift_num in number,
var_simulation_set in varchar2,
var_from_time in number,
var_to_time in number,
var_cap_units in number) is
var_time1 number;
dbms_output.put_line('Ready to insert' ||
' Dept ' || to_char(var_department_id) ||
' Res ' || to_char(var_resource_id) ||
' shift ' || to_char(var_shift_num) ||
' date '|| to_char(var_date) ||
' from time '|| to_char(var_from_time/3600)||
' to time '|| to_char(var_to_time/3600) ||
' units '|| to_char(var_cap_units));
INSERT into mrp_net_resource_avail(
department_id,
resource_id,
instance_id,
serial_number,
organization_id,
shift_num,
shift_date,
from_time,
to_time,
capacity_units,
simulation_set,
last_update_date,
last_updated_by,
creation_date,
created_by)
VALUES(
var_department_id,
var_resource_id,
var_instance_id,
var_serial_num,
var_organization_id,
var_shift_num,
var_date1,
var_time1,
var_time2,
var_cap_units,
var_simulation_set,
sysdate,
var_gt_user_id,
sysdate,
var_gt_user_id);
end insert_avail;
SELECT distinct
changes.action_type,
changes.from_time,
DECODE(LEAST(changes.to_time, changes.from_time),
changes.to_time, changes.to_time + 24*3600,
changes.to_time),
dates.shift_date,
changes.shift_num,
reschanges.capacity_change
from bom_shift_dates dates,
bom_res_instance_changes changes,
bom_resource_changes reschanges,
mtl_parameters param
WHERE dates.calendar_code = param.calendar_code
AND dates.exception_set_id = param.calendar_exception_set_id
AND dates.seq_num is not null
AND dates.shift_date between changes.from_date AND
NVL(changes.to_date, changes.from_date)
AND dates.shift_num = changes.shift_num
AND param.organization_id = arg_organization_id
AND changes.to_date >= trunc(arg_start_date)
AND changes.from_date <= arg_cutoff_date
AND changes.simulation_set = arg_simulation_set
AND changes.action_type = CHANGE_WORKDAY
AND reschanges.action_type = CHANGE_WORKDAY
AND changes.resource_id = arg_resource_id
AND changes.instance_id = arg_instance_id
AND nvl(changes.serial_number,-1) = nvl(arg_serial_num, -1)
AND changes.department_id = arg_department_id
AND reschanges.department_id = changes.department_id
AND reschanges.resource_id = changes.resource_id
AND reschanges.shift_num = changes.shift_num
AND reschanges.from_date = changes.from_date
AND reschanges.to_date = changes.to_date
AND reschanges.simulation_set = changes.simulation_set
AND reschanges.action_type = changes.action_type
AND reschanges.from_time = changes.from_time
AND reschanges.to_time = changes.to_time
-- Removed for bug #2318675 (24hr changes were ignored)
--AND NOT (changes.from_time = changes.to_time AND
-- changes.from_date = changes.to_date)
ORDER BY dates.shift_date, changes.from_time;
SELECT capacity_units capacity_units,
from_time from_time,
DECODE(LEAST(to_time, from_time),
to_time, to_time + 24*3600,
to_time) to_time,
rowid
FROM mrp_net_resource_avail
WHERE department_id = arg_department_id
AND resource_id = arg_resource_id
AND instance_id = arg_instance_id
AND nvl(serial_number,-1) = nvl(arg_serial_num, -1)
AND simulation_set = arg_simulation_set
AND organization_id = arg_organization_id
AND shift_num = var_shift_num
AND shift_date = var_shift_date
ORDER BY 2, 3;
insert into mrp_net_resource_avail(
organization_id,
department_id,
resource_id,
instance_id,
serial_number,
shift_num,
shift_date,
from_time,
to_time,
capacity_units,
simulation_set,
last_update_date,
last_updated_by,
creation_date,
created_by)
select arg_organization_id,
arg_department_id,
arg_resource_id,
arg_instance_id,
arg_serial_num,
res_shifts.shift_num,
dates.shift_date,
shifts.from_time,
shifts.to_time,
-- the capacity unit for instance can only be 1.
1,
arg_simulation_set,
sysdate,
var_gt_user_id,
sysdate,
var_gt_user_id
FROM bom_shift_dates dates,
bom_shift_times shifts,
bom_resource_shifts res_shifts,
bom_department_resources dept_res1,
bom_dept_res_instances dept_ins,
mtl_parameters param
WHERE dates.calendar_code = param.calendar_code
AND dates.exception_set_id = param.calendar_exception_set_id
AND dates.shift_num = shifts.shift_num
AND dates.seq_num is not null
AND dates.shift_date >= trunc(arg_start_date)
AND dates.shift_date <= arg_cutoff_date
AND shifts.shift_num = res_shifts.shift_num
AND shifts.calendar_code = param.calendar_code
AND res_shifts.department_id = dept_res1.department_id
AND res_shifts.resource_id = dept_res1.resource_id
AND NVL(dept_res1.available_24_hours_flag, 2) = 2
AND dept_res1.share_from_dept_id is null
AND dept_res1.resource_id = arg_resource_id
AND dept_res1.department_id = arg_department_id
AND dept_ins.department_id = arg_department_id
AND dept_ins.resource_id = arg_resource_id
AND dept_ins.instance_id = arg_instance_id
AND nvl(dept_ins.serial_number,-1) = nvl(arg_serial_num, -1)
AND param.organization_id = arg_organization_id
AND NOT EXISTS
(SELECT NULL
FROM bom_resource_changes changes
WHERE changes.department_id = dept_res1.department_id
AND changes.resource_id = dept_res1.resource_id
AND changes.simulation_set = arg_simulation_set
AND changes.shift_num = dates.shift_num
AND changes.from_date = dates.shift_date
AND changes.action_type = DELETE_WORKDAY);
select count(*) into var_rowcount
FROM mrp_net_resource_avail
where resource_id = arg_resource_id
and instance_id = arg_instance_id
and department_id = arg_department_id;
dbms_output.put_line(' Inserted '|| to_char(var_rowcount)||' avails'); */
insert into mrp_net_resource_avail(
organization_id,
department_id,
resource_id,
instance_id,
serial_number,
shift_num,
shift_date,
from_time,
to_time,
capacity_units,
simulation_set,
last_update_date,
last_updated_by,
creation_date,
created_by)
select arg_organization_id,
arg_department_id,
arg_resource_id,
arg_instance_id,
arg_serial_num,
0,
dates.calendar_date,
1,
24*60*60 - 1,
1,
arg_simulation_set,
sysdate,
var_gt_user_id,
sysdate,
var_gt_user_id
FROM bom_calendar_dates dates,
bom_department_resources dept_res1,
bom_dept_res_instances dept_ins,
mtl_parameters param
WHERE dates.calendar_code = param.calendar_code
AND dates.exception_set_id = param.calendar_exception_set_id
AND dates.calendar_date <= arg_cutoff_date
AND dates.seq_num is not null
AND dates.calendar_date >= trunc(arg_start_date)
AND NVL(dept_res1.available_24_hours_flag, 2) = 1
AND dept_res1.share_from_dept_id is null
AND dept_res1.resource_id = arg_resource_id
AND dept_res1.department_id = arg_department_id
AND dept_ins.department_id = arg_department_id
AND dept_ins.resource_id = arg_resource_id
AND dept_ins.instance_id = arg_instance_id
AND nvl(dept_ins.serial_number,-1) = nvl(arg_serial_num,-1)
AND param.organization_id = arg_organization_id;
-- of the modification, update avail from the start of
-- the shift to the start of the modification.
if var_to_shift_time <= var_to_time then
update_avail(var_rowid,
var_shift_date,
var_from_shift_time,
var_from_time -1);
update_avail(var_rowid,
var_shift_date,
var_from_shift_time,
var_from_time -1);
insert_avail(var_shift_date,
arg_department_id,
arg_resource_id,
arg_instance_id,
arg_serial_num,
arg_organization_id,
var_shift_num,
arg_simulation_set,
var_to_time+1,
var_to_shift_time,
var_orig_cap);
update_avail(var_rowid,
var_shift_date,
var_to_time+1,
var_to_shift_time);
delete_avail(var_rowid);
-- Insert modification
if var_cap_change = 1 then
insert_avail(var_shift_date,
arg_department_id,
arg_resource_id,
arg_instance_id,
arg_serial_num,
arg_organization_id,
var_shift_num,
arg_simulation_set,
var_from_time,
var_to_time,
var_cap_change);
-- dbms_output.put_line('going to insert added workdays');
INSERT into mrp_net_resource_avail(
organization_id,
department_id,
resource_id,
instance_id,
serial_number,
shift_num,
shift_date,
from_time,
to_time,
capacity_units,
simulation_set,
last_update_date,
last_updated_by,
creation_date,
created_by)
select arg_organization_id,
arg_department_id,
arg_resource_id,
arg_instance_id,
arg_serial_num,
changes.shift_num,
changes.from_date,
changes.from_time,
changes.to_time,
1,
arg_simulation_set,
sysdate,
var_gt_user_id,
sysdate,
var_gt_user_id
FROM bom_res_instance_changes changes
WHERE changes.department_id = arg_department_id
AND changes.resource_id = arg_resource_id
and changes.instance_id = arg_instance_id
and nvl(changes.serial_number,-1) = nvl(arg_serial_num, -1)
AND changes.action_type = ADD_WORKDAY
AND changes.simulation_set= arg_simulation_set;