[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE_WORKDAY CONSTANT number := 1;
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_from_time in number,
var_to_time in number) IS
var_time1 number;
UPDATE MSC_net_resource_avail
SET to_time = var_time1,
from_time =var_time2,
shift_date = shift_date + 1
WHERE rowid = var_rowid;
UPDATE MSC_net_resource_avail
SET to_time = var_time1,
from_time = var_time2
WHERE rowid = var_rowid;
END update_avail;
PROCEDURE delete_avail(var_rowid in ROWID) IS
BEGIN
v_stmt := 40;
DELETE from MSC_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_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_cap_units in number,
var_aggregate_resource_id in number,
var_refresh_number in number) IS
var_time1 number;
INSERT into MSC_net_resource_avail(
transaction_id,
plan_id,
department_id,
resource_id,
organization_id,
sr_instance_id,
shift_num,
shift_date,
from_time,
to_time,
capacity_units,
simulation_set,
aggregate_resource_id,
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_resource_avail_s.NEXTVAL
, -1
,var_department_id
,var_resource_id
,var_organization_id
,var_sr_instance_id
,var_shift_num
,var_date
,var_from_time
,var_to_time
,var_cap_units
,var_simulation_set
,var_aggregate_resource_id
,NULL /* STATUS */
,NULL /* APPLIED */
,2 /* UPDATED */
,MSC_CL_COLLECTION.v_current_date
,MSC_CL_COLLECTION.v_current_user
,MSC_CL_COLLECTION.v_current_date
,MSC_CL_COLLECTION.v_current_user
,v_current_login
,v_current_request
,v_current_application
,v_current_conc_program
,MSC_CL_COLLECTION.v_current_date
,var_refresh_number);
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 msc_shift_dates dates,
msc_resource_changes changes
WHERE dates.calendar_code = v_calendar_code
AND dates.sr_instance_id = arg_sr_instance_id
AND dates.exception_set_id = v_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 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
AND changes.sr_instance_id = arg_sr_instance_id
ORDER BY dates.shift_date, changes.from_time;
SELECT /*+ index (nra MSC_NET_RESOURCE_AVAIL_U2 )*/
capacity_units capacity_units,
from_time from_time,
to_time to_time,
rowid
FROM MSC_net_resource_avail nra
WHERE plan_id = -1
AND sr_instance_id = arg_sr_instance_id
AND organization_id = arg_organization_id
AND department_id = arg_department_id
AND resource_id = arg_resource_id
AND simulation_set = arg_simulation_set
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 MSC_net_resource_avail(
transaction_id,
plan_id,
organization_id,
sr_instance_id,
department_id,
resource_id,
shift_num,
shift_date,
from_time,
to_time,
capacity_units,
simulation_set,
aggregate_resource_id,
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)
SELECT msc_net_resource_avail_s.NEXTVAL
,-1
,arg_organization_id
,arg_sr_instance_id
,arg_department_id
,arg_resource_id
,res_shifts.shift_num
,arg_disable_date
,0
,0
,0
,arg_simulation_set
,arg_aggregate_resource_id
,NULL /* STATUS */
,NULL /* APPLIED */
,2 /* UPDATED */
,MSC_CL_COLLECTION.v_current_date
,MSC_CL_COLLECTION.v_current_user
,MSC_CL_COLLECTION.v_current_date
,MSC_CL_COLLECTION.v_current_user
,v_current_login
,v_current_request
,v_current_application
,v_current_conc_program
,MSC_CL_COLLECTION.v_current_date
FROM
msc_resource_shifts res_shifts
WHERE
res_shifts.department_id = arg_department_id
AND res_shifts.resource_id = arg_resource_id
AND res_shifts.sr_instance_id = arg_sr_instance_id;
INSERT into MSC_net_resource_avail(
transaction_id,
plan_id,
organization_id,
sr_instance_id,
department_id,
resource_id,
shift_num,
shift_date,
from_time,
to_time,
capacity_units,
simulation_set,
aggregate_resource_id,
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)
SELECT msc_net_resource_avail_s.NEXTVAL
,-1
,arg_organization_id
,arg_sr_instance_id
,arg_department_id
,arg_resource_id
,res_shifts.shift_num
,dates.shift_date
,shifts.from_time
,decode(least(shifts.from_time,shifts.to_time),shifts.to_time,shifts.to_time+86400,shifts.to_time)
,decode(changes.action_type,DELETE_WORKDAY,0,nvl(res_shifts.capacity_units,arg_capacity_units))
,arg_simulation_set
,arg_aggregate_resource_id
,NULL /* STATUS */
,NULL /* APPLIED */
,2 /* UPDATED */
,MSC_CL_COLLECTION.v_current_date
,MSC_CL_COLLECTION.v_current_user
,MSC_CL_COLLECTION.v_current_date
,MSC_CL_COLLECTION.v_current_user
,v_current_login
,v_current_request
,v_current_application
,v_current_conc_program
,MSC_CL_COLLECTION.v_current_date
FROM msc_shift_dates dates,
msc_shift_times shifts,
msc_resource_shifts res_shifts,
(select distinct department_id, resource_id,
sr_instance_id, simulation_set,
from_date, shift_num,action_type
From MSC_RESOURCE_CHANGES
where action_type = DELETE_WORKDAY ) changes --7705958
WHERE dates.calendar_code = v_calendar_code
AND dates.sr_instance_id = arg_sr_instance_id
AND dates.exception_set_id = v_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 <= least(trunc(arg_cutoff_date),
trunc(NVL(arg_disable_date-1,arg_cutoff_date)))
AND shifts.shift_num = res_shifts.shift_num
AND shifts.calendar_code = v_calendar_code
AND shifts.sr_instance_id= arg_sr_instance_id
AND res_shifts.department_id = arg_department_id
AND res_shifts.resource_id = arg_resource_id
AND res_shifts.sr_instance_id = arg_sr_instance_id
/* Bug 6648494 incorporated here */
AND changes.department_id (+) = arg_department_id
AND changes.resource_id (+) = arg_resource_id
AND changes.sr_instance_id (+) = arg_sr_instance_id
AND changes.simulation_set (+) = arg_simulation_set
AND changes.from_date (+) = dates.shift_date
AND changes.shift_num (+) = dates.shift_num;
/* Due to the performace issues, the delete_workday is handled
by the next UPDATE SQL statements.
AND ( arg_simulation_set is null
OR NOT EXISTS
(SELECT NULL
FROM msc_resource_changes changes
WHERE changes.department_id = arg_department_id
AND changes.resource_id = arg_resource_id
AND changes.sr_instance_id = arg_sr_instance_id
AND changes.shift_num = dates.shift_num
AND changes.from_date = dates.shift_date
AND changes.simulation_set= arg_simulation_set
AND changes.action_type = DELETE_WORKDAY) );
UPDATE MSC_NET_RESOURCE_AVAIL
SET capacity_units= 0
WHERE ROWID IN
( select /*+ ordered index (nra MSC_NET_RESOURCE_AVAIL_U2 )*/
/* nra.ROWID
from MSC_RESOURCE_CHANGES changes,
MSC_NET_RESOURCE_AVAIL nra
WHERE changes.department_id = arg_department_id
AND changes.resource_id = arg_resource_id
AND changes.sr_instance_id = arg_sr_instance_id
AND changes.simulation_set= arg_simulation_set
AND changes.action_type = DELETE_WORKDAY
AND changes.from_date >= trunc(arg_start_date)
AND changes.from_date <= arg_cutoff_date
AND nra.plan_id= -1
AND nra.sr_instance_id= changes.sr_instance_id
AND nra.organization_id= arg_organization_id
AND nra.simulation_set= changes.simulation_set
AND nra.department_id= changes.department_id
AND nra.resource_id= changes.resource_id
AND nra.shift_num= changes.shift_num
AND nra.shift_date= changes.from_date );
INSERT into MSC_net_resource_avail(
transaction_id,
plan_id,
organization_id,
sr_instance_id,
department_id,
resource_id,
shift_num,
shift_date,
from_time,
to_time,
capacity_units,
simulation_set,
aggregate_resource_id,
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)
VALUES( msc_net_resource_avail_s.NEXTVAL
,-1
,arg_organization_id
,arg_sr_instance_id
,arg_department_id
,arg_resource_id
,0
,arg_disable_date
,0
,0
,0
,arg_simulation_set
,arg_aggregate_resource_id
,NULL /* STATUS */
,NULL /* APPLIED */
,2 /* UPDATED */
,MSC_CL_COLLECTION.v_current_date
,MSC_CL_COLLECTION.v_current_user
,MSC_CL_COLLECTION.v_current_date
,MSC_CL_COLLECTION.v_current_user
,v_current_login
,v_current_request
,v_current_application
,v_current_conc_program
,MSC_CL_COLLECTION.v_current_date);
SELECT dates.calendar_date
BULK COLLECT
INTO v_workdate
FROM msc_calendar_dates dates
WHERE dates.calendar_code = v_calendar_code
AND dates.exception_set_id = v_calendar_exception_set_id
AND dates.sr_instance_id = arg_sr_instance_id
AND dates.calendar_date >= trunc(arg_start_date)
AND dates.calendar_date <= least(trunc(arg_cutoff_date),
trunc(NVL(arg_disable_date-1,arg_cutoff_date)))
AND dates.seq_num is not null;
INSERT into MSC_net_resource_avail(
transaction_id,
plan_id,
organization_id,
sr_instance_id,
department_id,
resource_id,
shift_num,
shift_date,
from_time,
to_time,
capacity_units,
simulation_set,
aggregate_resource_id,
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)
VALUES( msc_net_resource_avail_s.NEXTVAL
,-1
,arg_organization_id
,arg_sr_instance_id
,arg_department_id
,arg_resource_id
,0
,v_workdate(j)
,0
,24*60*60
,arg_capacity_units
,arg_simulation_set
,arg_aggregate_resource_id
,NULL /* STATUS */
,NULL /* APPLIED */
,2 /* UPDATED */
,MSC_CL_COLLECTION.v_current_date
,MSC_CL_COLLECTION.v_current_user
,MSC_CL_COLLECTION.v_current_date
,MSC_CL_COLLECTION.v_current_user
,v_current_login
,v_current_request
,v_current_application
,v_current_conc_program
,MSC_CL_COLLECTION.v_current_date);
insert_avail(
var_shift_date,
arg_department_id,
arg_resource_id,
arg_organization_id,
arg_sr_instance_id,
var_shift_num,
arg_simulation_set,
var_from_time,
var_from_shift_time - 1,
var_cap_change,
arg_aggregate_resource_id,
arg_refresh_number);
insert_avail(
var_shift_date,
arg_department_id,
arg_resource_id,
arg_organization_id,
arg_sr_instance_id,
var_shift_num,
arg_simulation_set,
var_from_shift_time,
var_to_time,
var_orig_cap + var_cap_change,
arg_aggregate_resource_id,
arg_refresh_number);
update_avail(
var_rowid,
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,
arg_sr_instance_id,
var_shift_num,
arg_simulation_set,
var_from_time,
var_from_shift_time - 1,
var_cap_change,
arg_aggregate_resource_id,
arg_refresh_number);
insert_avail(
var_shift_date,
arg_department_id,
arg_resource_id,
arg_organization_id,
arg_sr_instance_id,
var_shift_num,
arg_simulation_set,
var_from_shift_time,
var_to_shift_time,
var_orig_cap + var_cap_change,
arg_aggregate_resource_id,
arg_refresh_number);
insert_avail(
var_shift_date,
arg_department_id,
arg_resource_id,
arg_organization_id,
arg_sr_instance_id,
var_shift_num,
arg_simulation_set,
var_from_time,
var_to_time,
var_cap_change,
arg_aggregate_resource_id,
arg_refresh_number);
delete_avail(
var_rowid);
insert_avail(
var_shift_date,
arg_department_id,
arg_resource_id,
arg_organization_id,
arg_sr_instance_id,
var_shift_num,
arg_simulation_set,
var_from_time,
var_to_time,
var_orig_cap + var_cap_change,
arg_aggregate_resource_id,
arg_refresh_number);
insert_avail(
var_shift_date,
arg_department_id,
arg_resource_id,
arg_organization_id,
arg_sr_instance_id,
var_shift_num,
arg_simulation_set,
var_from_time,
var_to_time,
var_orig_cap + var_cap_change,
arg_aggregate_resource_id,
arg_refresh_number);
update_avail(
var_rowid,
var_to_time + 1,
var_to_shift_time);
insert_avail(
var_shift_date,
arg_department_id,
arg_resource_id,
arg_organization_id,
arg_sr_instance_id,
var_shift_num,
arg_simulation_set,
var_from_time,
var_to_time,
var_orig_cap + var_cap_change,
arg_aggregate_resource_id,
arg_refresh_number);
delete_avail(var_rowid);
update_avail(var_rowid,
var_from_shift_time,
var_from_time - 1);
insert_avail(
var_shift_date,
arg_department_id,
arg_resource_id,
arg_organization_id,
arg_sr_instance_id,
var_shift_num,
arg_simulation_set,
var_from_time,
var_to_time,
var_orig_cap + var_cap_change,
arg_aggregate_resource_id,
arg_refresh_number);
update_avail(
var_rowid,
var_from_shift_time,
var_from_time - 1);
insert_avail(
var_shift_date,
arg_department_id,
arg_resource_id,
arg_organization_id,
arg_sr_instance_id,
var_shift_num,
arg_simulation_set,
var_to_time + 1,
var_to_shift_time,
var_orig_cap,
arg_aggregate_resource_id,
arg_refresh_number);
delete_avail(
var_rowid);
insert_avail(
var_shift_date,
arg_department_id,
arg_resource_id,
arg_organization_id,
arg_sr_instance_id,
var_shift_num,
arg_simulation_set,
var_from_time,
var_to_shift_time,
var_orig_cap + var_cap_change,
arg_aggregate_resource_id,
arg_refresh_number);
insert_avail(
var_shift_date,
arg_department_id,
arg_resource_id,
arg_organization_id,
arg_sr_instance_id,
var_shift_num,
arg_simulation_set,
var_to_shift_time + 1,
var_to_time,
var_cap_change,
arg_aggregate_resource_id,
arg_refresh_number);
update_avail(
var_rowid,
var_from_shift_time,
var_from_time - 1);
insert_avail(
var_shift_date,
arg_department_id,
arg_resource_id,
arg_organization_id,
arg_sr_instance_id,
var_shift_num,
arg_simulation_set,
var_from_time,
var_to_shift_time,
var_orig_cap + var_cap_change,
arg_aggregate_resource_id,
arg_refresh_number);
insert_avail(
var_shift_date,
arg_department_id,
arg_resource_id,
arg_organization_id,
arg_sr_instance_id,
var_shift_num,
arg_simulation_set,
var_to_shift_time + 1,
var_to_time,
var_cap_change,
arg_aggregate_resource_id,
arg_refresh_number);
insert_avail(
var_shift_date,
arg_department_id,
arg_resource_id,
arg_organization_id,
arg_sr_instance_id,
var_shift_num,
arg_simulation_set,
var_from_time,
var_to_time,
var_cap_change,
arg_aggregate_resource_id,
arg_refresh_number);
INSERT into MSC_net_resource_avail(
transaction_id,
plan_id,
organization_id,
sr_instance_id,
department_id,
resource_id,
shift_num,
shift_date,
from_time,
to_time,
capacity_units,
simulation_set,
aggregate_resource_id,
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_resource_avail_s.NEXTVAL
,-1
,arg_organization_id
,arg_sr_instance_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
,arg_aggregate_resource_id
,NULL /* STATUS */
,NULL /* APPLIED */
,2 /* UPDATED */
,MSC_CL_COLLECTION.v_current_date
,MSC_CL_COLLECTION.v_current_user
,MSC_CL_COLLECTION.v_current_date
,MSC_CL_COLLECTION.v_current_user
,v_current_login
,v_current_request
,v_current_application
,v_current_conc_program
,MSC_CL_COLLECTION.v_current_date
,arg_refresh_number
FROM msc_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
AND changes.sr_instance_id = arg_sr_instance_id;
SELECT dept_res.department_id,
dept_res.resource_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
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
ORDER BY
org.calendar_code,
org.calendar_exception_set_id;
SELECT distinct dept_res.department_id,
dept_res.resource_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_resource_changes chg,
msc_department_resources dept_res
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
ORDER BY
org.calendar_code,
org.calendar_exception_set_id;
SELECT simulation_set
FROM msc_simulation_sets
WHERE organization_id = arg_organization_id
AND sr_instance_id = arg_sr_instance_id;
delete from msc_net_resource_avail
where rowid in (select res.rowid
from msc_net_resource_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 );
delete from msc_net_resource_avail
where rowid in (select res.rowid
from msc_net_resource_avail res, msc_department_resources line
where res.organization_id = line.organization_id
and res.sr_instance_id = line.sr_instance_id
and res.department_id = line.department_id
and res.resource_id = -1
and line.line_flag = 1
and line.plan_id = -1
and line.refresh_number = arg_refresh_number
and line.organization_id = arg_organization_id
and line.sr_instance_id = arg_sr_instance_id ) ;
INSERT into MSC_net_resource_avail(
transaction_id,
plan_id,
organization_id,
sr_instance_id,
department_id,
resource_id,
shift_date,
from_time,
to_time,
capacity_units,
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 /*+ leading(line) INDEX(LINE) use_nl(dates) */
msc_net_resource_avail_s.NEXTVAL
,-1
,arg_organization_id
,arg_sr_instance_id
,line.department_id
,-1
,dates.calendar_date
,line.start_time
,line.stop_time
,line.max_rate
,NULL /*STATUS*/
,NULL /*APPLIED*/
,2 /*UPDATED*/
,SYSDATE
,FND_GLOBAL.USER_ID
,SYSDATE
,FND_GLOBAL.USER_ID
,FND_GLOBAL.LOGIN_ID
,FND_GLOBAL.CONC_REQUEST_ID /* REQUEST_ID */
,FND_GLOBAL.PROG_APPL_ID /*PROGRAM_APPLICATION_ID */
,FND_GLOBAL.CONC_PROGRAM_ID /*PROGRAM_ID */
,SYSDATE /* PROGRAM_UPDATE_DATE */
,arg_refresh_number
FROM msc_calendar_dates dates,
msc_department_resources line,
msc_trading_partners org
WHERE line.organization_id = arg_organization_id
AND line.sr_instance_id = arg_sr_instance_id
AND line.line_flag = 1
AND line.plan_id = -1
AND line.refresh_number = arg_refresh_number
AND NVL(line.disable_date, sysdate+1) > sysdate
AND org.sr_tp_id = line.organization_id
AND org.sr_instance_id = line.sr_instance_id
AND org.partner_type = 3
AND dates.calendar_code = org.calendar_code
AND dates.sr_instance_id = arg_sr_instance_id
AND dates.exception_set_id = org.calendar_exception_set_id
AND dates.calendar_date >= trunc(v_start_date)
AND dates.calendar_date <= least(trunc(v_cutoff_date),
trunc(nvl(line.disable_date-1, v_cutoff_date)) )
AND dates.seq_num is not null;
INSERT into MSC_net_resource_avail(
transaction_id,
plan_id,
organization_id,
sr_instance_id,
department_id,
resource_id,
shift_date,
from_time,
to_time,
capacity_units,
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 /*+ leading(line) INDEX(LINE) use_nl(dates) */
msc_net_resource_avail_s.NEXTVAL
,-1
,arg_organization_id
,arg_sr_instance_id
,line.department_id
,-1
,line.disable_date
,0
,0
,0
,NULL /*STATUS*/
,NULL /*APPLIED*/
,2 /*UPDATED*/
,SYSDATE
,FND_GLOBAL.USER_ID
,SYSDATE
,FND_GLOBAL.USER_ID
,FND_GLOBAL.LOGIN_ID
,FND_GLOBAL.CONC_REQUEST_ID /* REQUEST_ID */
,FND_GLOBAL.PROG_APPL_ID /*PROGRAM_APPLICATION_ID */
,FND_GLOBAL.CONC_PROGRAM_ID /*PROGRAM_ID */
,SYSDATE /* PROGRAM_UPDATE_DATE */
,arg_refresh_number
FROM
msc_department_resources line,
msc_trading_partners org
WHERE line.organization_id = arg_organization_id
AND line.sr_instance_id = arg_sr_instance_id
AND line.line_flag = 1
AND line.plan_id = -1
AND line.refresh_number = arg_refresh_number
AND line.disable_date IS NOT NULL
AND org.sr_tp_id = line.organization_id
AND org.sr_instance_id = line.sr_instance_id
AND org.partner_type = 3;
SELECT
APPS_VER,
SYSDATE,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
UPPER(INSTANCE_CODE), /* Bug 2129155 */
INSTANCE_TYPE, -- OPM
nvl(LCID,0)
INTO
MSC_CL_COLLECTION.v_apps_ver,
MSC_CL_COLLECTION.START_TIME,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user,
MSC_CL_COLLECTION.v_instance_code,
MSC_CL_COLLECTION.v_instance_type, -- OPM
MSC_CL_COLLECTION.v_last_collection_id
FROM MSC_APPS_INSTANCES
WHERE INSTANCE_ID= pINSTANCE_ID;
lv_where_clause := ' AND ORGANIZATION_ID IN ( SELECT SR_TP_ID FROM MSC_TRADING_PARTNERS WHERE '||
' SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id ||
' AND ORGANIZATION_TYPE =1 ) ';
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RESOURCE_AVAIL', MSC_CL_COLLECTION.v_instance_id, -1);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RES_INST_AVAIL', MSC_CL_COLLECTION.v_instance_id, -1);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RESOURCE_AVAIL', MSC_CL_COLLECTION.v_instance_id, -1,lv_where_clause);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RES_INST_AVAIL', MSC_CL_COLLECTION.v_instance_id, -1,lv_where_clause);
SELECT tp.Organization_ID
FROM MSC_PARAMETERS tp,
MSC_INSTANCE_ORGS ins_org,
MSC_TRADING_PARTNERS mtp
WHERE tp.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND ins_org.SR_INSTANCE_ID=tp.SR_INSTANCE_ID
AND ins_org.Organization_ID=tp.ORGANIZATION_ID
AND ins_org.ENABLED_FLAG= MSC_UTIL.SYS_YES
AND ((pORG_GROUP = MSC_UTIL.G_ALL_ORGANIZATIONS ) OR (ins_org.ORG_GROUP=pORG_GROUP))
AND mtp.sr_instance_id = MSC_CL_COLLECTION.v_instance_id
AND mtp.sr_tp_id = tp.organization_id
AND mtp.partner_type = 3
AND mtp.organization_type = 1; -- Discrete Mfg.
SELECT 1
FROM MSC_ST_RESOURCE_CHANGES
WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND process_flag = 5;
update MSC_net_resource_avail
set capacity_units = 0
where capacity_units < 0
and plan_id = -1
AND sr_instance_id = MSC_CL_COLLECTION.v_instance_id
AND simulation_set is not null
and shift_date between trunc(lv_task_start_time) and
lv_task_end_time;
SELECT
msnra.organization_id,
msnra.sr_instance_id,
msnra.resource_id,
msnra.department_id,
msnra.simulation_set,
msnra.shift_num,
msnra.shift_date,
msnra.from_time,
msnra.to_time,
msnra.capacity_units
FROM msc_st_net_resource_avail msnra
WHERE msnra.sr_instance_id = MSC_CL_COLLECTION.v_instance_id and
msnra.organization_id=org_id;
SELECT
msnria.sr_instance_id,
msnria.res_instance_id,
msnria.resource_id,
msnria.department_id,
msnria.organization_id,
msnria.serial_number,
t1.inventory_item_id equipment_item_id,
msnria.simulation_set,
msnria.shift_num,
msnria.shift_date,
msnria.from_time,
msnria.to_time
FROM msc_st_net_res_inst_avail msnria,
MSC_ITEM_ID_LID t1
WHERE msnria.sr_instance_id = MSC_CL_COLLECTION.v_instance_id
and t1.sr_instance_id (+) = msnria.sr_instance_id
and t1.sr_inventory_item_id (+) = msnria.equipment_item_id
and msnria.organization_id=org_id;
select organization_id
from msc_instance_orgs mio,
msc_trading_partners mtp
where mio.sr_instance_id= MSC_CL_COLLECTION.v_instance_id and
mio.enabled_flag= 1 and
((MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS ) or (mio.org_group = MSC_CL_COLLECTION.v_coll_prec.org_group_flag)) and
mio.sr_instance_id=mtp.sr_instance_id and
mio.organization_id=mtp.sr_tp_id and
mtp.partner_type=3 and
mtp.organization_type=2;
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RESOURCE_AVAIL', v_instance_id, -1);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RES_INST_AVAIL', v_instance_id, -1);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RESOURCE_AVAIL', v_instance_id, -1,v_sub_str);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RES_INST_AVAIL', v_instance_id, -1,v_sub_str);
We will do a bulk insert of res avail for OPM orgs. If this fails,
then we will switch to old, row by row processing.
The same applies to collection of net res instance avail data as well.
*/
FOR c_rec1 IN c_org_list LOOP
BEGIN
SAVEPOINT LOAD_RES_AVAIL_SP;
' INSERT into MSC_net_resource_avail '
||' ( transaction_id,'
||' plan_id,'
||' department_id,'
||' resource_id,'
||' organization_id,'
||' sr_instance_id,'
||' shift_num,'
||' shift_date,'
||' from_time,'
||' to_time,'
||' capacity_units,'
||' simulation_set,'
||' status,'
||' applied,'
||' updated,'
||' last_update_date,'
||' last_updated_by,'
||' creation_date,'
||' created_by,'
||' refresh_number)'
||' SELECT'
||' msc_net_resource_avail_s.NEXTVAL,'
||' -1,'
||' msnra.department_id,'
||' msnra.resource_id,'
||' msnra.organization_id,'
||' msnra.sr_instance_id,'
||' msnra.shift_num,'
||' msnra.shift_date,'
||' msnra.from_time,'
||' msnra.to_time,'
||' msnra.capacity_units,'
||' msnra.simulation_set,'
||' NULL,' /* STATUS */
||' NULL,' /* APPLIED */
||' 2,' /* UPDATED */
||' :v_current_date,'
||' :v_current_user,'
||' :v_current_date,'
||' :v_current_user,'
||' :v_last_collection_id'
||' FROM msc_st_net_resource_avail msnra'
||' WHERE msnra.sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
||' AND msnra.organization_id = ' ||c_rec1.organization_id;
INSERT into MSC_net_resource_avail(
transaction_id,
plan_id,
department_id,
resource_id,
organization_id,
sr_instance_id,
shift_num,
shift_date,
from_time,
to_time,
capacity_units,
simulation_set,
status,
applied,
updated,
last_update_date,
last_updated_by,
creation_date,
created_by,
refresh_number)
VALUES(
msc_net_resource_avail_s.NEXTVAL,
-1,
c_rec.department_id,
c_rec.resource_id,
c_rec.organization_id,
c_rec.sr_instance_id,
c_rec.shift_num,
c_rec.shift_date,
c_rec.from_time,
c_rec.to_time,
c_rec.capacity_units,
c_rec.simulation_set,
NULL, /* STATUS */
NULL, /* APPLIED */
2, /* UPDATED */
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user,
MSC_CL_COLLECTION.v_last_collection_id);
' 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,'
||' refresh_number)'
||' SELECT'
||' msc_net_res_inst_avail_s.NEXTVAL,'
||' -1,'
||' msnria.sr_instance_id,'
||' msnria.organization_id,'
||' msnria.department_id,'
||' msnria.resource_id,'
||' msnria.res_instance_id,'
||' t1.inventory_item_id,'
||' NULL,'
||' msnria.serial_number,'
||' msnria.simulation_set,'
||' msnria.shift_num,'
||' msnria.shift_date,'
||' msnria.from_time,'
||' msnria.to_time,'
||' NULL,' /* STATUS */
||' NULL,' /* APPLIED */
||' 2,' /* UPDATED */
||' :v_current_date,'
||' :v_current_user,'
||' :v_current_date,'
||' :v_current_user,'
||' :v_last_collection_id'
||' FROM msc_st_net_res_inst_avail msnria,'
||' MSC_ITEM_ID_LID t1'
||' WHERE msnria.sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
||' and t1.sr_instance_id (+) = msnria.sr_instance_id'
||' and t1.sr_inventory_item_id (+) = msnria.equipment_item_id'
||' and msnria.organization_id = ' ||c_rec1.organization_id;
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,
refresh_number)
VALUES(
msc_net_res_inst_avail_s.NEXTVAL,
-1,
c_rec_resinst.sr_instance_id,
c_rec_resinst.organization_id,
c_rec_resinst.department_id,
c_rec_resinst.resource_id,
c_rec_resinst.res_instance_id,
c_rec_resinst.equipment_item_id,
NULL,
c_rec_resinst.serial_number,
c_rec_resinst.simulation_set,
c_rec_resinst.shift_num,
c_rec_resinst.shift_date,
c_rec_resinst.from_time,
c_rec_resinst.to_time,
NULL, /* STATUS */
NULL, /* APPLIED */
2, /* UPDATED */
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user,
MSC_CL_COLLECTION.v_last_collection_id);
v_current_date MSC_NET_RESOURCE_AVAIL.LAST_UPDATE_DATE%TYPE;
v_current_user MSC_NET_RESOURCE_AVAIL.LAST_UPDATED_BY%TYPE;
SELECT DECODE (M2A_DBLINK, NULL, NULL_DBLINK, '@' || M2A_DBLINK)
INTO v_dblink
FROM MSC_APPS_INSTANCES
WHERE INSTANCE_ID = pINSTANCE_ID;
l_sql_stmt_tab := 'SELECT TABLE_NAME from all_tables'|| v_dblink
|| ' where TABLE_NAME =''AHL_DEPT_RESOURCE_CAPACITY'''
|| ' AND owner= ''AHL''';
SELECT PLAN_ID,DATA_START_DATE,PLAN_COMPLETION_DATE
INTO lv_plan_id,lv_DATA_START_DATE,lv_PLAN_COMPLETION_DATE
FROM msc_plans
WHERE COMPILE_DESIGNATOR = lv_plan_name
AND SR_INSTANCE_ID = pINSTANCE_ID;
SELECT COUNT ( * )
INTO V_count
FROM MSC_NET_RESOURCE_AVAIL
WHERE PLAN_ID = lv_plan_id AND SR_INSTANCE_ID = pINSTANCE_ID and rownum <2;
l_sql_stmt_del := 'DELETE from AHL_DEPT_RESOURCE_CAPACITY'|| v_dblink
||'where SOURCE_APPLICATION = ''MSC'''; -- 13820344
'INSERT INTO AHL_DEPT_RESOURCE_CAPACITY'
|| v_dblink
|| ' (Organization_id, '
|| ' Department_id, '
|| ' Resource_id, '
|| ' available_date, '
|| ' LAST_UPDATE_DATE, '
|| ' LAST_UPDATED_BY, '
|| ' CREATION_DATE, '
|| ' LAST_UPDATE_LOGIN, '
|| ' CREATED_BY, '
|| ' capacity_units, '
|| ' UOM_CODE, '
|| ' SOURCE_APPLICATION, '
|| ' ASCP_PLAN_ID, '
|| ' ASCP_PLAN_DATE, '
|| ' ASCP_PLAN_NAME, '
|| ' BOM_SIMULATION_SET_ID) '
|| ' SELECT '
|| ' MNRA.ORGANIZATION_ID, '
|| ' MNRA.DEPARTMENT_ID/2, '
|| ' MNRA.RESOURCE_ID/2, '
|| ' trunc(MNRA.SHIFT_DATE), '
||' SYSDATE, '
|| v_current_user
|| ','
||' SYSDATE, '
|| V_CURRENT_LOGIN
|| ','
|| v_current_user
|| ','
|| ' SUM( nvl((DECODE(LEAST(mnra.to_time, mnra.from_time),mnra.to_time, mnra.to_time + 24*3600,mnra.to_time)- mnra.from_time)/3600,0.0) *mnra.capacity_units), '
|| ' MDR.UNIT_OF_MEASURE, '
|| '''' || LV_SOURCE_APPLICATION || ''','
|| ' MNRA.plan_id, '
|| '''' || lv_DATA_START_DATE || ''','
|| '''' || lv_plan_name || ''','
|| ' MNRA.SIMULATION_SET_ID '
|| ' From MSC_NET_RESOURCE_AVAIL MNRA,MSC_DEPARTMENT_RESOURCES MDR '
|| ' WHERE MNRA.PLAN_ID = MDR.PLAN_ID '
|| ' AND MNRA.ORGANIZATION_ID = MDR.ORGANIZATION_ID '
|| ' AND MNRA.SR_INSTANCE_ID = MDR.SR_INSTANCE_ID '
|| ' AND MNRA.RESOURCE_ID = MDR.RESOURCE_ID '
|| ' AND MNRA.DEPARTMENT_ID = MDR.DEPARTMENT_ID '
|| ' AND MNRA.PLAN_ID ='
|| lv_plan_id
|| ' AND MNRA.SR_INSTANCE_ID = :pINSTANCE_ID '
|| ' GROUP BY MNRA.ORGANIZATION_ID,MNRA.DEPARTMENT_ID,MNRA.RESOURCE_ID,trunc( MNRA.SHIFT_DATE ),UNIT_OF_MEASURE,MNRA.plan_id,MNRA.SIMULATION_SET_ID';