[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select TRUNC(MIN(calendar_date))
into v_start_date
from msc_calendar_dates cal
,msc_trading_partners tp
where tp.sr_tp_id = arg_organization_id
and tp.sr_instance_id = arg_sr_instance_id
and tp.partner_type = 3
and cal.calendar_code = tp.calendar_code
and cal.sr_instance_id = tp.sr_instance_id
and cal.exception_set_id = tp.calendar_exception_set_id
and cal.seq_num is not null;
select TRUNC(MAX(calendar_date))
into v_cutoff_date
from msc_calendar_dates cal
,msc_trading_partners tp
where tp.sr_tp_id = arg_organization_id
and tp.sr_instance_id = arg_sr_instance_id
and tp.partner_type = 3
and cal.calendar_code = tp.calendar_code
and cal.sr_instance_id = tp.sr_instance_id
and cal.exception_set_id = tp.calendar_exception_set_id
and cal.seq_num is not null;
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 msc_net_res_inst_avail
SET shift_date = var_date1,
from_time = var_time1,
to_time = var_time2
WHERE rowid = var_rowid;
MSC_UTIL.MSC_DEBUG('Error in update_avail:: ' || to_char(sqlcode) || ':' || substr(sqlerrm,1,60));
END update_avail;
PROCEDURE delete_avail( var_rowid in ROWID) is
BEGIN
/*
if var_gt_debug then
dbms_output.put_line('about to delete');
log_message('about to delete');
DELETE from msc_net_res_inst_avail
WHERE rowid = var_rowid;
log_message('delete row count ' || sql%rowcount);
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_equipment_item_id IN Number,
var_organization_id in number,
var_sr_instance_id in number,
var_shift_num in number,
var_simulation_set in varchar2,
var_from_time in number,
var_to_time in number,
var_refresh_number 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 msc_net_res_inst_avail(
inst_transaction_id,
plan_id,
sr_instance_id,
organization_id,
department_id,
resource_id,
res_instance_id,
equipment_item_id,
parent_id,
serial_number,
simulation_set,
shift_num,
shift_date,
from_time,
to_time,
status,
applied,
updated,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
refresh_number)
VALUES( msc_net_res_inst_avail_s.NEXTVAL,
-1,
var_sr_instance_id,
var_organization_id,
var_department_id,
var_resource_id,
var_instance_id,
var_equipment_item_id,
null, --PARENT_ID
var_serial_num,
var_simulation_set,
var_shift_num,
var_date1,
var_time1,
var_time2,
null, --STATUS
null, --APPLIED
2, --UPDATED
sysdate,
var_gt_user_id,
sysdate,
var_gt_user_id,
var_gt_login,
var_gt_request,
var_gt_application,
var_gt_conc_program,
sysdate,
var_refresh_number
);
MSC_UTIL.MSC_DEBUG('Error in insert_avail:: ' || to_char(sqlcode) || ':' || substr(sqlerrm,1,60));
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 msc_shift_dates dates,
msc_res_instance_changes changes,
msc_resource_changes reschanges,
msc_trading_partners param
WHERE dates.calendar_code = param.calendar_code
AND dates.exception_set_id = param.calendar_exception_set_id
AND dates.sr_instance_id = param.sr_instance_id
AND dates.sr_instance_id = arg_sr_instance_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.sr_tp_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.res_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.sr_instance_id = changes.sr_instance_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 equipment_item_id,
from_time from_time,
DECODE(LEAST(to_time, from_time),
to_time, to_time + 24*3600,
to_time) to_time,
rowid
FROM msc_net_res_inst_avail
WHERE plan_id = -1
AND department_id = arg_department_id
AND resource_id = arg_resource_id
AND res_instance_id = arg_instance_id
AND nvl(serial_number,-1) = nvl(arg_serial_num, -1)
AND simulation_set = arg_simulation_set
AND sr_instance_id = arg_sr_instance_id
AND organization_id = arg_organization_id
AND shift_num = var_shift_num
AND shift_date = var_shift_date
ORDER BY 2, 3;
log_message('first insert for not 24HR');
INSERT into msc_net_res_inst_avail(
inst_transaction_id,
plan_id,
sr_instance_id,
organization_id,
department_id,
resource_id,
res_instance_id,
equipment_item_id,
parent_id,
serial_number,
simulation_set,
shift_num,
shift_date,
from_time,
to_time,
status,
applied,
updated,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
refresh_number)
select msc_net_res_inst_avail_s.NEXTVAL,
-1,
arg_sr_instance_id,
arg_organization_id,
arg_department_id,
arg_resource_id,
arg_instance_id,
dept_ins.equipment_item_id,
null, --PARENT_ID
arg_serial_num,
arg_simulation_set,
res_shifts.shift_num,
dates.shift_date,
shifts.from_time,
shifts.to_time,
null, --STATUS
null, --APPLIED
2, --UPDATED
sysdate,
var_gt_user_id,
sysdate,
var_gt_user_id,
var_gt_login,
var_gt_request,
var_gt_application,
var_gt_conc_program,
sysdate,
arg_refresh_number
FROM msc_shift_dates dates,
msc_shift_times shifts,
msc_resource_shifts res_shifts,
msc_department_resources dept_res1,
msc_dept_res_instances dept_ins,
msc_trading_partners param
WHERE dates.calendar_code = param.calendar_code
AND dates.exception_set_id = param.calendar_exception_set_id
AND dates.sr_instance_id = param.sr_instance_id
AND dates.sr_instance_id = arg_sr_instance_id
AND param.sr_tp_id = arg_organization_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 shifts.sr_instance_id = arg_sr_instance_id
AND res_shifts.department_id = dept_res1.department_id
AND res_shifts.resource_id = dept_res1.resource_id
AND res_shifts.sr_instance_id = arg_sr_instance_id
AND dept_res1.plan_id = -1
AND NVL(dept_res1.available_24_hours_flag, 2) = 2
-- AND dept_res1.owning_department_id is null
AND dept_res1.resource_id = arg_resource_id
AND dept_res1.department_id = arg_department_id
AND dept_res1.organization_id = arg_organization_id
AND dept_res1.sr_instance_id = arg_sr_instance_id
AND dept_ins.department_id = arg_department_id
AND dept_ins.organization_id = arg_organization_id
AND dept_ins.sr_instance_id = arg_sr_instance_id
AND dept_ins.resource_id = arg_resource_id
AND dept_ins.res_instance_id = arg_instance_id
AND dept_ins.plan_id = -1
AND nvl(dept_ins.serial_number,-1) = nvl(arg_serial_num, -1)
AND NOT EXISTS
(SELECT NULL
FROM msc_resource_changes changes
WHERE changes.sr_instance_id = dept_res1.sr_instance_id
AND 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 msc_net_res_inst_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'); */
log_message(' Inserted -- it is a 24 hrs');
insert into msc_net_res_inst_avail(
inst_transaction_id,
plan_id,
sr_instance_id,
organization_id,
department_id,
resource_id,
res_instance_id,
equipment_item_id,
parent_id,
serial_number,
simulation_set,
shift_num,
shift_date,
from_time,
to_time,
status,
applied,
updated,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
refresh_number)
select msc_net_res_inst_avail_s.NEXTVAL,
-1,
arg_sr_instance_id,
arg_organization_id,
arg_department_id,
arg_resource_id,
arg_instance_id,
dept_ins.equipment_item_id,
null, --PARENT_ID
arg_serial_num,
arg_simulation_set,
0,
dates.calendar_date,
1,
24*60*60 - 1,
null, --STATUS
null, --APPLIED
2, --UPDATED
sysdate,
var_gt_user_id,
sysdate,
var_gt_user_id,
var_gt_login,
var_gt_request,
var_gt_application,
var_gt_conc_program,
sysdate,
arg_refresh_number
FROM msc_calendar_dates dates,
msc_department_resources dept_res1,
msc_dept_res_instances dept_ins,
msc_trading_partners param
WHERE dates.calendar_code = param.calendar_code
AND dates.exception_set_id = param.calendar_exception_set_id
AND dates.sr_instance_id = param.sr_instance_id
AND dates.sr_instance_id = arg_sr_instance_id
AND dates.calendar_date <= arg_cutoff_date
AND dates.seq_num is not null
AND dates.calendar_date >= trunc(arg_start_date)
AND dept_res1.plan_id = -1
AND NVL(dept_res1.available_24_hours_flag, 2) = 1
-- AND dept_res1.owning_department_id is null
AND dept_res1.resource_id = arg_resource_id
AND dept_res1.department_id = arg_department_id
AND dept_res1.organization_id = arg_organization_id
AND dept_res1.sr_instance_id = arg_sr_instance_id
AND dept_ins.department_id = arg_department_id
AND dept_ins.resource_id = arg_resource_id
AND dept_ins.res_instance_id = arg_instance_id
AND dept_ins.organization_id = arg_organization_id
AND dept_ins.sr_instance_id = arg_sr_instance_id
AND dept_ins.plan_id = -1
AND nvl(dept_ins.serial_number,-1) = nvl(arg_serial_num,-1)
AND param.sr_tp_id = arg_organization_id;
--dbms_output.put_line('2nd insert row count ' || sql%rowcount);
-- 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
--dbms_output.put_line('update only');
update_avail(var_rowid,
var_shift_date,
var_from_shift_time,
var_from_time -1);
--dbms_output.put_line('update and insert');
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,
var_equipment_item_id,
arg_organization_id,
arg_sr_instance_id,
var_shift_num,
arg_simulation_set,
var_to_time+1,
var_to_shift_time,
arg_refresh_number);
--dbms_output.put_line('update row');
update_avail(var_rowid,
var_shift_date,
var_to_time+1,
var_to_shift_time);
--dbms_output.put_line('delete row ' || var_rowid);
delete_avail(var_rowid);
-- Insert modification
if var_cap_change = 1 then
--dbms_output.put_line('insert the modification');
insert_avail(var_shift_date,
arg_department_id,
arg_resource_id,
arg_instance_id,
arg_serial_num,
var_equipment_item_id,
arg_organization_id,
arg_sr_instance_id,
var_shift_num,
arg_simulation_set,
var_from_time,
var_to_time,
arg_refresh_number);
--dbms_output.put_line('going to insert added workdays');
log_message('going to insert added workdays');
INSERT into msc_net_res_inst_avail(
inst_transaction_id,
plan_id,
sr_instance_id,
organization_id,
department_id,
resource_id,
res_instance_id,
equipment_item_id,
parent_id,
serial_number,
simulation_set,
shift_num,
shift_date,
from_time,
to_time,
status,
applied,
updated,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
refresh_number)
select msc_net_res_inst_avail_s.NEXTVAL,
-1,
arg_sr_instance_id,
arg_organization_id,
arg_department_id,
arg_resource_id,
arg_instance_id,
var_equipment_item_id,
null, --PARENT_ID
arg_serial_num,
arg_simulation_set,
changes.shift_num,
changes.from_date,
changes.from_time,
changes.to_time,
null, --STATUS
null, --APPLIED
2, --UPDATED
sysdate,
var_gt_user_id,
sysdate,
var_gt_user_id,
var_gt_login,
var_gt_request,
var_gt_application,
var_gt_conc_program,
sysdate,
arg_refresh_number
FROM msc_res_instance_changes changes
WHERE changes.sr_instance_id = arg_sr_instance_id
AND changes.department_id = arg_department_id
AND changes.resource_id = arg_resource_id
and changes.res_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;
SELECT dept_res.department_id,
dept_res.resource_id,
dept_ins.res_instance_id,
dept_ins.serial_number,
dept_ins.equipment_item_id,
NVL(dept_res.available_24_hours_flag, 2),
dept_res.aggregate_resource_id,
NVL(dept_res.capacity_units,1), --**
dept_res.disable_date --, --**
--org.calendar_code, --**
-- org.calendar_exception_set_id --**
FROM msc_trading_partners org,
msc_department_resources dept_res,
msc_dept_res_instances dept_ins
WHERE dept_res.owning_department_id = dept_res.department_id
AND dept_res.plan_id = -1
AND dept_res.resource_id <> -1
AND dept_res.aggregate_resource_flag <> 1 -- if it's not aggregate
AND NVL(dept_res.disable_date,sysdate+1) > sysdate
AND dept_res.organization_id = org.sr_tp_id
AND dept_res.sr_instance_id = org.sr_instance_id
AND org.sr_tp_id= arg_organization_id
AND org.sr_instance_id= arg_sr_instance_id
AND org.partner_type=3
AND dept_res.plan_id = dept_ins.plan_id
AND dept_res.organization_id = dept_ins.organization_id
AND dept_res.sr_instance_id = dept_ins.sr_instance_id
AND dept_res.department_id = dept_ins.department_id
AND dept_res.resource_id = dept_ins.resource_id
/*
adding the following condition that the collection for the resource
instances for only there is any resource instance change */
AND exists (select * from msc_res_instance_changes chg
where dept_ins.department_id = chg.department_id
and dept_ins.resource_id = chg.resource_id
and dept_ins.sr_instance_id = chg.sr_instance_id
and dept_ins.res_instance_id = chg.res_instance_id
and dept_ins.serial_number = chg.serial_number);
SELECT distinct dept_res.department_id,
dept_res.resource_id,
res_ins.res_instance_id,
res_ins.serial_number,
NVL(dept_res.available_24_hours_flag, 2),
dept_res.aggregate_resource_id,
NVL(dept_res.capacity_units,1), --**
dept_res.disable_date --, --**
--org.calendar_code, --**
-- org.calendar_exception_set_id --**
FROM msc_trading_partners org,
msc_resource_changes chg,
msc_department_resources dept_res,
msc_res_instance_changes res_ins
WHERE chg.department_id = dept_res.department_id
AND chg.resource_id = dept_res.resource_id
AND chg.sr_instance_id = dept_res.sr_instance_id
AND chg.refresh_number = arg_refresh_number
AND dept_res.owning_department_id = dept_res.department_id
AND dept_res.plan_id = -1
AND dept_res.resource_id <> -1
AND dept_res.aggregate_resource_flag <> 1 -- if it's not aggregate
AND NVL(dept_res.disable_date,sysdate+1) > sysdate
AND dept_res.organization_id = org.sr_tp_id
AND dept_res.sr_instance_id = org.sr_instance_id
AND org.sr_tp_id= arg_organization_id
AND org.sr_instance_id= arg_sr_instance_id
AND org.partner_type=3
AND chg.sr_instance_id = res_ins.sr_instance_id
AND chg.department_id = res_ins.department_id
AND chg.resource_id = res_ins.resource_id;
SELECT simulation_set
FROM msc_simulation_sets
WHERE organization_id = arg_organization_id
AND sr_instance_id = arg_sr_instance_id;
log_message('Delete msc_net_res_inst_avail');
delete from msc_net_res_inst_avail
where rowid in (select res.rowid
from msc_net_res_inst_avail res,
msc_resource_changes chg,
msc_department_resources dept
where res.organization_id = arg_organization_id
and res.sr_instance_id = arg_sr_instance_id
and res.plan_id = -1
and res.department_id = chg.department_id
and res.resource_id = chg.resource_id
and chg.sr_instance_id = arg_sr_instance_id
and chg.refresh_number = arg_refresh_number
and dept.department_id = chg.department_id
and dept.resource_id = chg.resource_id
and dept.line_flag <> 1
and dept.plan_id = -1
and dept.organization_id = arg_organization_id
and dept.sr_instance_id = arg_sr_instance_id );
log_message('Number of row deleted from net change ' || sql%rowcount);