[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE_WORKDAY CONSTANT number := 1;
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_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,
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_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 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,
changes.capacity_change
from bom_shift_dates dates,
bom_resource_changes changes,
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 changes.resource_id = arg_resource_id
AND changes.department_id = arg_department_id
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 simulation_set = arg_simulation_set
AND organization_id = arg_organization_id
AND shift_num = var_shift_num
AND shift_date = var_shift_date
UNION ALL
SELECT 0 capacity_units,
HOLD_TIME from_time,
HOLD_TIME to_time,
rowid
from dual
ORDER BY 2, 3;
insert into mrp_net_resource_avail(
organization_id,
department_id,
resource_id,
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,
res_shifts.shift_num,
dates.shift_date,
shifts.from_time,
shifts.to_time,
nvl(res_shifts.capacity_units,nvl(dept_res1.capacity_units,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,
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 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.shift_num = dates.shift_num
AND changes.from_date = dates.shift_date
AND changes.action_type = DELETE_WORKDAY);
insert into mrp_net_resource_avail(
organization_id,
department_id,
resource_id,
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,
0,
dates.calendar_date,
1,
24*60*60 - 1,
nvl(dept_res1.capacity_units, 1),
arg_simulation_set,
sysdate,
var_gt_user_id,
sysdate,
var_gt_user_id
FROM bom_calendar_dates dates,
bom_department_resources dept_res1,
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 param.organization_id = arg_organization_id;
insert_avail(var_shift_date,
arg_department_id,
arg_resource_id,
arg_organization_id,
var_shift_num,
arg_simulation_set,
var_from_time,
var_to_time,
var_cap_change);
insert_avail(var_shift_date,
arg_department_id,
arg_resource_id,
arg_organization_id,
var_shift_num,
arg_simulation_set,
var_from_time,
var_from_shift_time - 1,
var_cap_change);
insert_avail(var_shift_date,
arg_department_id,
arg_resource_id,
arg_organization_id,
var_shift_num,
arg_simulation_set,
var_from_time,
var_from_shift_time - 1,
var_cap_change);
delete_avail(var_rowid);
insert_avail(var_shift_date,
arg_department_id,
arg_resource_id,
arg_organization_id,
var_shift_num,
arg_simulation_set,
var_from_shift_time,
var_to_time,
var_orig_cap + var_cap_change);
update_avail(var_rowid,
var_shift_date,
var_to_time + 1,
var_to_shift_time);
delete_avail(var_rowid);
insert_avail(var_shift_date,
arg_department_id,
arg_resource_id,
arg_organization_id,
var_shift_num,
arg_simulation_set,
var_from_shift_time,
var_to_shift_time,
var_orig_cap + var_cap_change);
insert_avail(var_shift_date,
arg_department_id,
arg_resource_id,
arg_organization_id,
var_shift_num,
arg_simulation_set,
var_from_time,
var_from_shift_time - 1,
var_cap_change);
delete_avail(var_rowid);
insert_avail(var_shift_date,
arg_department_id,
arg_resource_id,
arg_organization_id,
var_shift_num,
arg_simulation_set,
var_from_shift_time,
var_to_shift_time,
var_orig_cap + var_cap_change);
insert_avail(var_shift_date,
arg_department_id,
arg_resource_id,
arg_organization_id,
var_shift_num,
arg_simulation_set,
var_to_shift_time + 1,
var_to_time,
var_cap_change);
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_organization_id,
var_shift_num,
arg_simulation_set,
var_from_time,
var_to_time,
var_orig_cap + var_cap_change);
insert_avail(var_shift_date,
arg_department_id,
arg_resource_id,
arg_organization_id,
var_shift_num,
arg_simulation_set,
var_to_time + 1,
var_to_shift_time,
var_orig_cap);
delete_avail(var_rowid);
update_avail(var_rowid,
var_shift_date,
var_from_shift_time,
var_from_time - 1);
delete_avail(var_rowid);
insert_avail(var_shift_date,
arg_department_id,
arg_resource_id,
arg_organization_id,
var_shift_num,
arg_simulation_set,
var_from_time,
var_to_shift_time,
var_orig_cap + var_cap_change);
insert_avail(var_shift_date,
arg_department_id,
arg_resource_id,
arg_organization_id,
var_shift_num,
arg_simulation_set,
var_to_shift_time + 1,
var_to_time,
var_cap_change);
insert_avail(var_shift_date,
arg_department_id,
arg_resource_id,
arg_organization_id,
var_shift_num,
arg_simulation_set,
var_from_time,
var_to_time,
var_cap_change);
insert_avail(var_shift_date,
arg_department_id,
arg_resource_id,
arg_organization_id,
var_shift_num,
arg_simulation_set,
var_to_shift_time + 1,
var_to_time,
var_cap_change);
INSERT into mrp_net_resource_avail(
organization_id,
department_id,
resource_id,
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,
changes.shift_num,
changes.from_date,
changes.from_time,
changes.to_time,
changes.capacity_change,
arg_simulation_set,
sysdate,
var_gt_user_id,
sysdate,
var_gt_user_id
FROM bom_resource_changes changes
WHERE changes.department_id = arg_department_id
AND changes.resource_id = arg_resource_id
AND changes.action_type = ADD_WORKDAY
AND changes.simulation_set= arg_simulation_set;
select dept_res.department_id,
dept_res.resource_id,
NVL(dept_res.available_24_hours_flag, 2)
from bom_department_resources dept_res,
bom_departments dept
where dept_res.department_id = dept.department_id
AND dept_res.share_from_dept_id is null
AND dept.organization_id = arg_organization_id;
delete from mrp_net_resource_avail
where organization_id = arg_organization_id
and (arg_simulation_set is null or simulation_set = arg_simulation_set);