[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
LAST_UPDATED_BY NUMBER,
RES_INST_ID NUMBER,
SERIAL_NUMBER VARCHAR2(2000));
SELECT
NUMBER1,
NUMBER2,
NUMBER3,
NUMBER4,
NUMBER5,
DATE1,
NUMBER6,
NUMBER7,
NUMBER8,
NUMBER9,
LAST_UPDATED_BY,
NUMBER10, CHAR1
FROM Msc_FORM_QUERY
WHERE query_id = g_query_id
ORDER BY number2, number3, number4, number5, date1,
number6, number7,number1;
SELECT
transaction_id,
parent_id,
aggregate_resource_id,
simulation_set,
from_time,
to_time,
capacity_units,
status,
applied,
updated,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
shift_date,
shift_num
FROM MSC_NET_RESOURCE_AVAIL
WHERE plan_id=g_plan_id
AND organization_id = g_org_id
AND sr_instance_id =g_instance_id
AND department_id = g_department_id
AND resource_id = g_resource_id
and shift_date = g_shift_date
and decode(resource_id,-1,-1,shift_num) =
decode(resource_id,-1,-1,g_shift_number)
and capacity_units >=0
and nvl(parent_id,0) <> -1
order by from_time;
SELECT
inst_transaction_id transaction_id,
parent_id,
to_number(null) aggregate_resource_id,
simulation_set,
from_time,
to_time,
nvl(capacity_units,1) capacity_units,
status,
applied,
updated,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
shift_date,
shift_num
FROM msc_net_res_inst_avail
WHERE plan_id=g_plan_id
AND organization_id = g_org_id
AND sr_instance_id =g_instance_id
AND department_id = g_department_id
AND resource_id = g_resource_id
and res_instance_id = g_res_inst_id
and serial_number = g_serial_number
and shift_date = g_shift_date
and decode(resource_id,-1,-1,shift_num) = decode(resource_id,-1,-1,g_shift_number)
and nvl(capacity_units,1) >=0
and nvl(parent_id,0) <> -1
order by from_time;
SELECT distinct mra.shift_date
FROM msc_net_resource_avail mra,
msc_form_query mfq
WHERE mra.plan_id = g_plan_id
and mra.organization_id = g_org_id
and mra.sr_instance_id = g_instance_id
and mra.department_id = g_department_id
and mra.resource_id = g_resource_id
and nvl(mra.parent_id,0) <> -1
and mra.capacity_units >=0
and mfq.query_id = g_query_id
and trunc(mra.shift_date) between
trunc(mfq.date1) and trunc(mfq.date2)
ORDER BY mra.shift_date;
SELECT nvl(dates.seq_num, -1)
FROM msc_trading_partners mtp,
msc_calendar_dates dates
WHERE dates.calendar_date = trunc(g_shift_date)
AND dates.calendar_code = mtp.calendar_code
AND dates.exception_set_id = mtp.calendar_exception_set_id
AND dates.sr_instance_id = mtp.sr_instance_id
AND mtp.partner_type = 3
AND mtp.sr_tp_id = g_org_id
AND mtp.sr_instance_id = g_instance_id;
update_table;
g_res_tab.delete;
g_res_inst_tab.delete;
Function insert_undo_data(undo_type number,
j number default null,
v_undo_parent_id number default null) return number is
v_undo_id number;
select msc_undo_summary_s.nextval
into v_undo_id
from dual;
if undo_type = 2 then -- update
i := 1;
undo_type, --2 is update , 1 is insert a record
g_tmp_tab(k).transaction_id,
g_plan_id,
g_instance_id,
v_undo_parent_id,
net_res_Columns,
x_return_sts,
x_msg_count,
x_msg_data,
v_undo_id);
end insert_undo_data;
Function insert_res_inst_undo_data(undo_type number,
v_trx_id number,
v_old_shift_date varchar2, v_new_shift_date varchar2,
v_old_shift_number varchar2, v_new_shift_number varchar2,
v_old_from_time varchar2, v_new_from_time varchar2,
v_old_to_time varchar2, v_new_to_time varchar2,
v_old_units varchar2, v_new_units varchar2,
v_undo_parent_id number default null) return number is
v_undo_id number;
select msc_undo_summary_s.nextval
into v_undo_id
from dual;
if undo_type = 2 then -- update
i := 1;
undo_type, --2 is update , 1 is insert a record
v_trx_id,
g_plan_id,
g_instance_id,
v_undo_parent_id,
net_res_Columns,
x_return_sts,
x_msg_count,
x_msg_data,
v_undo_id);
end insert_res_inst_undo_data;
g_tmp_tab.delete;
v_undo_id :=insert_undo_data(1); -- insert
-- if add non working day, set the updated field as 1
-- so that when re-plan, it will be treated as work day
IF g_change_rec.operation = OP_ADD_DAY THEN
g_tmp_tab(k).updated :=1;
v_undo_id :=insert_undo_data(2,j); -- update
v_undo_parent_id :=insert_undo_data(2,j); --update
-- delete work day and add non working day would be caught
-- here only if it falls inside the range and not in a gap,
-- because v_start_record will always = v_end_record in these cases
IF g_change_rec.operation <> OP_DEL_DAY THEN
if v_undo_parent_id is not null then
add_new_record(j,false,false);
g_tmp_tab(k).updated :=1;
insert_undo_data(1,j,v_undo_parent_id); -- insert
v_undo_parent_id:=insert_undo_data(2,j); -- update
insert_undo_data(1,j,v_undo_parent_id); -- insert
v_undo_parent_id:=insert_undo_data(2,j); -- update
v_undo_parent_id :=insert_undo_data(2,j); --update
insert_undo_data(1,j,v_undo_parent_id); -- insert
v_undo_parent_id:=insert_undo_data(2,j); -- update
v_undo_parent_id :=insert_undo_data(2,j);
v_undo_id :=insert_undo_data(1,j,v_undo_parent_id);
v_undo_id :=insert_undo_data(1);
v_undo_id :=insert_undo_data(1);
v_undo_id :=insert_undo_data(1,j,v_undo_parent_id);
g_tmp_tab(k).updated :=1;
v_undo_id :=insert_undo_data(1);
v_undo_id :=insert_undo_data(1);
-- need to insert row with date change only first
add_new_record(j,true,true);
v_undo_parent_id :=insert_undo_data(2,j);
insert_undo_data(1,j,v_undo_parent_id); -- insert
v_undo_parent_id := insert_undo_data(2,j);
insert_undo_data(1,j,v_undo_parent_id); -- insert
v_undo_parent_id :=insert_undo_data(2,j);
insert_undo_data(1,j,v_undo_parent_id); -- insert
v_undo_id :=insert_undo_data(1);
g_tmp_tab.delete;
v_undo_id :=insert_undo_data(1);
select msc_net_res_inst_avail_s.nextval
into v_trx_id
from dual;
PROCEDURE update_table IS
CURSOR bucket IS
SELECT mpb.bkt_start_date, mpb.bkt_end_date
FROM msc_plan_buckets mpb,
msc_plans mp
where mp.plan_id = g_plan_id
and mp.plan_id = mpb.plan_id
and mp.organization_id = mpb.organization_id
and mp.sr_instance_id = mpb.sr_instance_id
and mpb.curr_flag =1
and g_shift_date between mpb.bkt_start_date and mpb.bkt_end_date;
delete from msc_net_resource_avail
where plan_id = g_plan_id
and transaction_id = g_res_tab(m).transaction_id
and g_res_tab(m).from_time <> g_res_tab(m).to_time;
update msc_net_resource_avail
set capacity_units = -1,
status =0,
applied =2,
from_time = from_time+1,
to_time = to_time +1
where plan_id = g_plan_id
and organization_id = g_org_id
and sr_instance_id = g_instance_id
AND department_id = g_department_id
AND resource_id = g_resource_id
AND nvl(parent_id, 0) <> -1
AND shift_date = g_shift_date
AND decode(resource_id, -1,-1,shift_num) =
decode(resource_id,-1,-1,g_shift_number) ;
update msc_net_res_inst_avail
set capacity_units = 0,
status =0,
applied =2
where plan_id = g_plan_id
and inst_transaction_id = g_res_inst_tab(m).transaction_id
and g_res_inst_tab(m).from_time <> g_res_inst_tab(m).to_time;
dbms_output.put_line('insert for tran='||to_char(g_res_tab(m).transaction_id));
INSERT INTO msc_net_resource_avail
(plan_id,
parent_id,
transaction_id,
organization_id,
sr_instance_id,
department_id,
resource_id,
shift_date,
shift_num,
from_time,
to_time,
capacity_units,
simulation_set,
status,
applied,
updated,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login)
VALUES
(g_plan_id,
-2,
g_res_tab(m).transaction_id,
g_org_id,
g_instance_id,
g_department_id,
g_resource_id,
g_shift_date,
decode(g_resource_id, -1, null,g_shift_number),
g_res_tab(m).from_time,
g_res_tab(m).to_time,
l_units,
g_res_tab(m).simulation_set,
g_res_tab(m).status,
g_res_tab(m).applied,
g_res_tab(m).updated,
g_res_tab(m).last_update_date,
g_res_tab(m).last_updated_by,
g_res_tab(m).creation_date,
g_res_tab(m).created_by,
g_res_tab(m).last_update_login);
update msc_net_res_inst_avail
set capacity_units = l_res_inst_units,
shift_date = g_shift_date,
shift_num = g_shift_number,
from_time = g_res_tab(m).from_time,
to_time = g_res_tab(m).to_time,
status =0,
applied =2
where plan_id = g_plan_id
and inst_transaction_id = g_res_inst_tab(m).transaction_id;
l_res_inst_undo_id := insert_res_inst_undo_data(2,
g_res_inst_tab(m).transaction_id,
fnd_date.date_to_canonical(g_res_inst_tab(m).shift_date),
fnd_date.date_to_canonical(g_shift_date),
g_res_inst_tab(m).shift_number, g_shift_number,
g_res_inst_tab(m).from_time, g_res_tab(m).from_time,
g_res_inst_tab(m).to_time, g_res_tab(m).to_time,
g_res_inst_tab(m).capacity_units, l_res_inst_units,
null);
insert into msc_net_res_inst_avail (
inst_transaction_id,
last_update_date, last_updated_by, creation_date, created_by, last_update_login,
plan_id, sr_instance_id, organization_id, department_id, resource_id,
res_instance_id, serial_number, shift_num, shift_date,
from_time, to_time, simulation_set, capacity_units)
values (
l_res_inst_trx_id,
sysdate, fnd_global.user_id, sysdate,fnd_global.user_id, fnd_global.user_id,
g_plan_id, g_instance_id, g_org_id, g_department_id, g_resource_id, g_res_inst_id,
g_serial_number, g_shift_number, g_shift_date,
g_res_tab(m).from_time, g_res_tab(m).to_time, g_res_tab(m).simulation_set, l_res_inst_units);
--dbms_output.put_line('res-inst insert '||l_res_inst_trx_id||' - '||g_shift_number
--||' - '||g_res_tab(m).from_time||' - '||g_res_tab(m).to_time||' - '|| l_res_inst_units);
l_res_inst_undo_id := insert_res_inst_undo_data(1,
l_res_inst_trx_id,
null, null,
null, null,
null, null,
null, null,
null, null,
null);
select round(sum(decode(sign(to_time-from_time),1,(to_time - from_time),
(to_time+86400-from_time)
)/3600*capacity_units),6)
into v_capacity_units
from msc_net_resource_avail
where plan_id = g_plan_id
and organization_id = g_org_id
and sr_instance_id = g_instance_id
AND department_id = g_department_id
AND resource_id = g_resource_id
and nvl(parent_id, 0) <> -1
and capacity_units >0
and shift_date between v_start_date and v_end_date;
update msc_net_resource_avail
set capacity_units = v_capacity_units,
status =0,
applied =2,
updated =2
where plan_id = g_plan_id
and organization_id = g_org_id
and sr_instance_id = g_instance_id
AND department_id = g_department_id
AND resource_id = g_resource_id
and shift_date = v_start_date
and parent_id =-1;
update msc_net_resource_avail
set parent_id = g_res_tab(1).parent_id
where plan_id = g_plan_id
and organization_id = g_org_id
and sr_instance_id = g_instance_id
AND department_id = g_department_id
AND resource_id = g_resource_id
and parent_id = -2 ;
END update_table;
select msc_net_resource_avail_s.nextval
into v_transaction_id
from dual;
g_tmp_tab(k).last_update_date := sysdate;
g_tmp_tab(k).last_updated_by := g_change_rec.last_updated_by;
g_tmp_tab(k).created_by := g_change_rec.last_updated_by;
SELECT res.department_id, res.resource_id,
res.organization_id, res.sr_instance_id
FROM msc_department_resources res
WHERE plan_id = v_plan_id;
delete from msc_net_resource_avail
where plan_id = v_plan_id
AND organization_id = v_org_id
AND sr_instance_id =v_instance_id
AND department_id = v_dept_id
AND resource_id = v_res_id
and parent_id =-1;
' and department_id in (select distinct department_id ' ||
' from msc_department_resources where NVL(department_class,''@@@'') '||
' in (' || p_dept_class_list || ') and plan_id = '
||to_char(v_plan_id)||
' and (sr_instance_id, organization_id) in ('||p_org_instance_list ||'))';
' and (department_id, resource_id) in (select '||
' department_id, resource_id from msc_department_resources where ' ||
' NVL(resource_group_name,''@@@'') in ('
|| p_res_group_list || ') and '||
' plan_id = '||to_char(v_plan_id)||
' and (sr_instance_id, organization_id) in ('||
p_org_instance_list ||'))';
'SELECT distinct department_id, resource_id, '||
'organization_id, sr_instance_id '||
'FROM msc_net_resource_avail '||
'WHERE plan_id = '||to_char(v_plan_id) ||
' AND nvl(parent_id, 0) <> -1 ' ||
' AND (sr_instance_id, organization_id) in ('||
p_org_instance_list ||')' || where_statement;
'SELECT distinct department_id, resource_id, '||
'organization_id, sr_instance_id '||
'FROM msc_net_resource_avail '||
'WHERE plan_id = '||to_char(v_plan_id) ||
' AND nvl(parent_id, 0) <> -1 ' || where_statement;
SELECT mpb.bkt_start_date, mpb.bkt_end_date
FROM msc_plan_buckets mpb,
msc_plans mp
WHERE mp.plan_id = v_plan_id
and mp.plan_id = mpb.plan_id
and mp.sr_instance_id = mpb.sr_instance_id
and mp.organization_id = mpb.organization_id
and mpb.curr_flag =1
order by mpb.bucket_index;
select 1
from msc_net_resource_avail
where plan_id = v_plan_id
and sr_instance_id = p_instance_id
and organization_id = p_org_id
and department_id = p_dept_id
and resource_id = p_res_id
and parent_id =-1
and rownum <2;
select sum(decode(sign(to_time-from_time),-1,(to_time+86400 - from_time),
(to_time-from_time)
)/3600*capacity_units)
from msc_net_resource_avail
where plan_id = v_plan_id
and sr_instance_id = p_instance_id
and organization_id = p_org_id
AND department_id = p_dept_id
AND resource_id = p_res_id
and capacity_units >0
and nvl(parent_id,0) <> -1
and trunc(shift_date) between trunc(v_start_date)
and trunc(v_end_date);
SELECT aggregate_resource_flag
from msc_department_resources
where plan_id = v_plan_id
and sr_instance_id = p_instance_id
and organization_id = p_org_id
AND department_id = p_dept_id
AND resource_id = p_res_id;
select sum(capacity_units)
from msc_net_resource_avail
where plan_id = v_plan_id
and sr_instance_id = p_instance_id
and organization_id = p_org_id
AND department_id = p_dept_id
AND resource_id = p_res_id
and capacity_units >0
and trunc(shift_date) between trunc(v_start_date)
and trunc(v_end_date);
select msc_net_resource_avail_s.nextval
into v_transaction_id
from dual;
insert into msc_net_resource_avail
( TRANSACTION_ID,
parent_id,
PLAN_ID ,
ORGANIZATION_ID,
SR_INSTANCE_ID ,
DEPARTMENT_ID ,
RESOURCE_ID ,
SHIFT_DATE ,
CAPACITY_UNITS ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY
)
values (
v_transaction_id,
-1,
v_plan_id,
p_org_id,
p_instance_id,
p_dept_id,
p_res_id,
v_bucket(i).start_date,
v_new_capacity_units,
sysdate,
1,
sysdate,
1);
update msc_net_resource_avail
set parent_id = v_transaction_id
where plan_id = v_plan_id
and sr_instance_id = p_instance_id
and organization_id = p_org_id
AND department_id = p_dept_id
AND resource_id = p_res_id
and capacity_units >=0
AND nvl(parent_id,0) <> -1
and trunc(shift_date) between trunc(v_bucket(i).start_date)
and trunc(v_bucket(i).end_date);
select organization_id,
department_id,
resource_id,
shift_date
from msc_net_resource_avail
where plan_id = p_plan_id
and transaction_id = p_transaction_id
and sr_instance_id = p_instance_id;
SELECT mpb.bkt_start_date, mpb.bkt_end_date
FROM msc_plan_buckets mpb,
msc_plans mp
where mp.plan_id = p_plan_id
and mp.plan_id = mpb.plan_id
and mp.organization_id = mpb.organization_id
and mp.sr_instance_id = mpb.sr_instance_id
and mpb.curr_flag =1
and v_shift_date between mpb.bkt_start_date and mpb.bkt_end_date;
select round(sum(decode(sign(to_time-from_time),1,(to_time - from_time),
(to_time+86400-from_time)
)/3600*capacity_units),6)
into v_capacity_units
from msc_net_resource_avail
where plan_id = p_plan_id
and organization_id = v_org_id
and sr_instance_id = p_instance_id
AND department_id = v_dept_id
AND resource_id = v_res_id
and nvl(parent_id, 0) <> -1
and capacity_units >0
and shift_date between v_start_date and v_end_date;
update msc_net_resource_avail
set capacity_units = v_capacity_units,
status =0,
applied =2,
updated =2
where plan_id = p_plan_id
and organization_id = v_org_id
and sr_instance_id = p_instance_id
AND department_id = v_dept_id
AND resource_id = v_res_id
and shift_date = v_start_date
and parent_id =-1;
select operation_seq_num,resource_seq_num
from msc_resource_requirements
where plan_id = p_plan_id
and supply_id = p_supply_id
and parent_id = 2
order by operation_seq_num,resource_seq_num;
p_trans_id.delete;
p_start_time.delete;
p_end_time.delete;
p_resource_units.delete;
sim_res.org_id.delete;
sim_res.inst_id.delete;
sim_res.dept_id.delete;
sim_res.res_id.delete;
sim_res.res_hours.delete;
sim_res.assign_units.delete;
sim_res.op_seq_id.delete;
sim_res.rt_seq_id.delete;
select transaction_id, operation_seq_num,resource_seq_num,
organization_id,sr_instance_id,department_id,resource_id,
resource_hours,assigned_units,
nvl(firm_start_date,start_date),
nvl(firm_end_date,end_date),
operation_sequence_id,
routing_sequence_id
from msc_resource_requirements
where plan_id = p_plan_id
and supply_id = p_supply_id
and parent_id = 2
order by operation_seq_num,resource_seq_num;
update msc_resource_requirements
set firm_start_date = p_start_time(a),
firm_end_date = p_end_time(a),
assigned_units = p_resource_units(a), --bug 5973698
status = 0,
applied =2,
firm_flag = 7
where plan_id = p_plan_id
and transaction_id = p_trans_id(a);
update msc_supplies
set status = 0,
applied =2,
firm_planned_type = 1,
firm_date = p_end_time(a),
firm_quantity = new_order_quantity
where plan_id = p_plan_id
and transaction_id = p_supply_id;
select shift_date, from_time, to_time, capacity_units
from msc_net_resource_avail
where plan_id = p_plan_id
and organization_id = p_org_id
and sr_instance_id = p_inst_id
and department_id = p_dept_id
and resource_id = p_res_id
and capacity_units > 0
and nvl(parent_id, 0) <> -1
and shift_date >= trunc(p_changed_date)
order by shift_date, from_time, to_time;
select 1
from msc_net_resource_avail
where plan_id = p_plan_id
and organization_id = p_org_id
and sr_instance_id = p_inst_id
and department_id = p_dept_id
and resource_id = p_res_id
and nvl(parent_id, 0) <> -1;
select mpe.effectivity_date, mpe.disable_date
from msc_process_effectivity mpe,
msc_supplies ms
where ms.plan_id = p_plan_id
and ms.transaction_id = p_supply_id
and mpe.plan_id = ms.plan_id
and mpe.process_sequence_id = ms.process_seq_id;
select nvl(resource_units, max_resource_units)
from msc_operation_resources
where plan_id = p_plan_id
and operation_sequence_id = p_op_seq_id
and routing_sequence_id = p_rt_seq_id
and resource_id = p_res_id;
select transaction_id, operation_seq_num,resource_seq_num, assigned_units,
organization_id,sr_instance_id,department_id,resource_id,
to_char(firm_start_date,'MM/DD/RRRR HH24:MI') firm_start_time,
to_char(firm_end_date,'MM/DD/RRRR HH24:MI') firm_end_time,
to_char(start_date,'MM/DD/RRRR HH24:MI') start_time,
to_char(end_date,'MM/DD/RRRR HH24:MI') end_time,
resource_hours, overloaded_capacity
from msc_resource_requirements
where plan_id = p_plan_id
and supply_id = p_supply_id
and parent_id = 2
order by operation_seq_num,resource_seq_num;
select shift_date, from_time, to_time, capacity_units
from msc_net_resource_avail
where plan_id = p_plan_id
and organization_id = p_org_id
and sr_instance_id = p_inst_id
and department_id = p_dept_id
and resource_id = p_res_id
and capacity_units > 0
and nvl(parent_id, 0) <> -1
and shift_date >= trunc(p_start_time)
and shift_date <= trunc(p_end_time)
order by 1,2,3;