[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_last_update_date DATE;
select plan_id,
transaction_id,
sr_instance_id,
table_changed,
action,
last_update_date
from msc_undo_summary
where undo_id = v_undo_id;
select column_changed,
old_value,
new_value,
column_type
from msc_undo_details
where plan_id = v_plan_id
and undo_id = v_undo_id;
select undo_id,transaction_id, sr_instance_id
from msc_undo_summary
where plan_id = v_plan_id
and ( undo_id = v_undo_id
or parent_id = v_undo_id )
and table_changed = 3
and action=2;
l_last_update_date;
if ( l_action = inserted ) then
--undo an inserted record
insert_table(l_undo_id , l_table_changed ,
l_plan_id , l_transaction_id ,
l_sr_instance_id, x_return_status,
x_msg_count, x_msg_data );
Delete from msc_undo_details
where plan_id = l_plan_id
and (undo_id = l_undo_id
or undo_id in ( select undo_id
from msc_undo_summary
where plan_id = l_plan_id
and parent_id = l_undo_id));
Delete from msc_undo_summary
where plan_id = l_plan_id
and (undo_id = l_undo_id
or parent_id = l_undo_id);
Delete from msc_undo_summary
where plan_id = l_plan_id
and undo_id = l_undo_id;
Delete from msc_undo_summary
where plan_id = l_plan_id
and undo_id = l_undo_id;
elsif ( l_action = updated ) then
if (l_table_changed = 3) then
open c_supp(l_plan_id, l_undo_id);
--undo an updated record from undo_details
update_table(l_table_changed,
l_column_changed,
l_old_value, l_new_value,l_column_type,
l_plan_id, l_sr_instance_id,
l_transaction_id, x_return_status,
x_msg_count, x_msg_data, s_undo_id );
--undo an updated record from undo_details
update_table(l_table_changed,
l_column_changed,
l_old_value, l_new_value,l_column_type,
l_plan_id, l_sr_instance_id,
l_transaction_id, x_return_status,
x_msg_count, x_msg_data, l_undo_id );
--end undo update table
Begin
if (l_table_changed in (3,4) ) then
Delete from msc_undo_details
where plan_id = l_plan_id
and (undo_id = l_undo_id
or undo_id in ( select undo_id
from msc_undo_summary
where plan_id = l_plan_id
and parent_id = l_undo_id));
Delete from msc_undo_summary
where plan_id = l_plan_id
and (undo_id = l_undo_id
or parent_id = l_undo_id);
Delete from msc_undo_details
where plan_id = l_plan_id
and undo_id = l_undo_id;
Delete from msc_undo_summary
where plan_id = l_plan_id
and undo_id = l_undo_id;
-- end delete undo_details
Exception
When others then
x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
Delete from msc_undo_summary
where plan_id = l_plan_id
and undo_id = l_undo_id;
if ( action not in (inserted, updated) ) then
x_return_status := FND_API.G_RET_STS_ERROR;
if ( (action = inserted) or (action = updated) ) then
if ( action = updated) and (changed_values.count = 0) then
x_return_status := fnd_api.g_ret_sts_success;
select MSC_UNDO_SUMMARY_S.nextval
into v_undo_id
from dual;
--Insert a record into MSC_UNDO_SUMMARY
INSERT INTO MSC_UNDO_SUMMARY (
undo_id,
plan_id,
sr_instance_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
table_changed,
action,
transaction_id,
bookmark_name,
parent_id )
VALUES (
v_undo_id,
plan_id,
sr_instance_id,
v_user_id,
SYSDATE,
v_user_id,
SYSDATE,
v_last_update_login,
table_changed,
action,
transaction_id,
NULL,
parent_id);
if ( action = updated ) then
i := changed_values.first;
INSERT INTO MSC_UNDO_DETAILS (
UNDO_ID,
PLAN_ID,
COLUMN_CHANGED,
COLUMN_CHANGED_TEXT,
COLUMN_TYPE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OLD_VALUE,
NEW_VALUE )
VALUES (
v_undo_id,
plan_id,
l_column_changed,
l_column_changed_text,
l_column_type,
v_user_id,
sysdate,
v_user_id,
sysdate,
v_last_update_login,
l_old_value,
l_new_value );
SELECT MSC_UNDO_SUMMARY_S.nextval
INTO v_undo_id
from dual;
--Insert a record into MSC_UNDO_SUMMARY
INSERT INTO MSC_UNDO_SUMMARY (
undo_id,
plan_id,
sr_instance_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
table_changed,
action,
transaction_id,
bookmark_name )
VALUES (
v_undo_id,
plan_id,
0,
v_user_id,
SYSDATE,
v_user_id,
SYSDATE,
v_last_update_login,
NULL,
action,
0,
bookmark_name );
PROCEDURE insert_table (p_undo_id NUMBER,
p_table_changed NUMBER,
p_plan_id NUMBER,
p_transaction_id NUMBER,
p_sr_instance_id NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
cursor c_net_res (l_undo_id number) is
select distinct a.sr_instance_id,
a.transaction_id,
b.old_value,
a.action,
b.column_changed
from msc_undo_summary a,
msc_undo_details b
where a.undo_id = b.undo_id (+)
and (a.undo_id = l_undo_id
or a.parent_id = l_undo_id);
select origination_type
from msc_demands
where plan_id = l_plan
and sr_instance_id = l_instance
and demand_id = l_trx;
Delete from msc_supplies
where transaction_id = p_transaction_id
and plan_id = p_plan_id
and sr_instance_id = p_sr_instance_id ;
update msc_supplies
set firm_quantity = 0,
firm_planned_type = 0,
status = 0,
applied = 2
where transaction_id = p_transaction_id
and plan_id = p_plan_id
and sr_instance_id = p_sr_instance_id ;
update msc_demands
set firm_quantity = 0,
status = 0,
applied = 2
where plan_id = p_plan_id
and demand_id = p_transaction_id
and sr_instance_id = p_sr_instance_id;
Delete from msc_demands
where demand_id = p_transaction_id
and plan_id = p_plan_id
and sr_instance_id = p_sr_instance_id ;
Delete from msc_supplier_capacities
where transaction_id in (select transaction_id
from msc_undo_summary
where plan_id = p_plan_id
and (undo_id = p_undo_id or parent_id = p_undo_id))
and plan_id = p_plan_id ;
update msc_supplier_capacities
set capacity = 0,
status = 0,
applied = 2
where plan_id = p_plan_id
and transaction_id in (select transaction_id
from msc_undo_summary
where plan_id = p_plan_id
and (undo_id = p_undo_id or parent_id = p_undo_id));
-- bug 1314938 - typical one .. do not delete the row,
--instead update the capacity to zero, for this record and its parent record
/*
Delete from msc_net_resource_avail
where transaction_id = p_transaction_id
and plan_id = p_plan_id
and sr_instance_id = p_sr_instance_id ;
update msc_net_resource_avail
set capacity_units = -1,
status = 0,
applied = 2
where plan_id = p_plan_id
and sr_instance_id = l_sr_instance_id
and transaction_id = l_transaction_id;
update msc_net_resource_avail
set capacity_units =
decode(l_column_name,'CAPACITY_UNITS',
l_old_value,capacity_units),
from_time =
decode(l_column_name,'FROM_TIME',
l_old_value,from_time),
to_time =
decode(l_column_name,'TO_TIME',
l_old_value,to_time),
status = 0,
applied = 2
where plan_id = p_plan_id
and sr_instance_id = l_sr_instance_id
and transaction_id = l_transaction_id;
update msc_net_res_inst_avail
set capacity_units =
decode(l_column_name,'CAPACITY_UNITS',
l_old_value,capacity_units),
from_time =
decode(l_column_name,'FROM_TIME',
l_old_value,from_time),
to_time =
decode(l_column_name,'TO_TIME',
l_old_value,to_time),
status = 0,
applied = 2
where plan_id = p_plan_id
and sr_instance_id = l_sr_instance_id
and inst_transaction_id = l_transaction_id;
msc_update_resource.refresh_parent_record(
p_plan_id,l_sr_instance_id, l_transaction_id);
Delete from msc_plans
where plan_id = p_plan_id ;
delete from msc_shipments
where plan_id = p_plan_id
and sr_instance_id = l_sr_instance_id
and shipment_id = l_transaction_id;
FND_MSG_PUB.add_exc_msg('MSC_UNDO', 'INSERT_TABLE');
END insert_table ;
v_last_update_login := FND_GLOBAL.login_id;
PROCEDURE update_table(p_table_changed NUMBER,
p_column_changed VARCHAR2,
p_old_value VARCHAR2,
p_new_value VARCHAR2,
p_column_type VARCHAR2,
p_plan_id NUMBER,
p_sr_instance_id NUMBER,
p_transaction_id NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_undo_id NUMBER) IS
update_str varchar2(500);
cursor c_net_res (l_undo_id number) is select
distinct a.sr_instance_id,
a.transaction_id,
b.old_value,
a.action,
b.column_changed
from msc_undo_summary a,
msc_undo_details b
where a.undo_id = b.undo_id (+)
and (a.undo_id = l_undo_id
or a.parent_id = l_undo_id);
update_str := 'UPDATE MSC_SUPPLIES SET STATUS=0, APPLIED=2, ';
sql_string := update_str||' '||set_str||' '||where_str;
update_str := 'UPDATE MSC_DEMANDS SET STATUS=0, APPLIED=2, ';
sql_string := update_str||' '||set_str||' '||where_str;
update_str := 'UPDATE MSC_SUPPLIER_CAPACITIES SET ';
update_str := update_str||' STATUS=0, APPLIED=2, ';
sql_string := update_str||' '||set_str||' '||where_str;
update msc_net_resource_avail
set capacity_units = -1,
status = 0,
applied = 2
where plan_id = p_plan_id
and sr_instance_id = l_sr_instance_id
and transaction_id = l_transaction_id;
update msc_net_resource_avail
set capacity_units =
decode(l_column_name,'CAPACITY_UNITS',
l_old_value,capacity_units),
from_time =
decode(l_column_name,'FROM_TIME',
l_old_value,from_time),
to_time =
decode(l_column_name,'TO_TIME',
l_old_value,to_time),
status = 0,
applied = 2
where plan_id = p_plan_id
and sr_instance_id = l_sr_instance_id
and transaction_id = l_transaction_id;
update msc_net_res_inst_avail
set capacity_units =
decode(l_column_name,'CAPACITY_UNITS',
l_old_value,capacity_units),
from_time =
decode(l_column_name,'FROM_TIME',
l_old_value,from_time),
to_time =
decode(l_column_name,'TO_TIME',
l_old_value,to_time),
status = 0,
applied = 2
where plan_id = p_plan_id
and sr_instance_id = l_sr_instance_id
and inst_transaction_id = l_transaction_id;
msc_update_resource.refresh_parent_record(
p_plan_id,l_sr_instance_id, l_transaction_id);
update_str := 'UPDATE MSC_PLANS SET ';
sql_string := update_str||' '||set_str||' '||where_str;
update_str := 'UPDATE MSC_RESOURCE_REQUIREMENTS SET ';
update_str := update_str||' STATUS=0, APPLIED=2, ';
sql_string := update_str||' '||set_str||' '||where_str;
update_str := 'UPDATE MSC_SHIPMENTS SET STATUS=0, APPLIED=2, ';
sql_string := update_str||' '||set_str||' '||where_str;
FND_MSG_PUB.add_exc_msg('MSC_UNDO', 'UPDATE_TABLE'
||' '||sql_string);
END update_table;
select plan_id, sr_instance_id, transaction_id, table_changed, action,
last_updated_by, last_update_date, identifier1_name, identifier2_name,
identifier3_name
from msc_undo_summary_v
where undo_id = l_undo_id;
select count(undo_id)
from msc_undo_summary
where plan_id = v_plan_id
and sr_instance_id = v_sr_instance_id
and table_changed = v_table_changed
and last_updated_by = v_user
--and last_update_date > v_date
-- and parent_id is null
and undo_id >v_undo_id;
select count(undo_id)
from msc_undo_summary
where plan_id = v_plan_id
and sr_instance_id = v_sr_instance_id
and transaction_id = v_transaction_id
and table_changed = v_table_changed
and last_updated_by = v_user
--and last_update_date > v_date
-- and parent_id is null
and undo_id > v_undo_id;
select count(undo_id)
from msc_undo_summary
where plan_id = v_plan_id
and sr_instance_id = v_sr_instance_id
and table_changed = v_table_changed
and last_updated_by <> v_user
--and last_update_date > v_date
--and parent_id is null
and undo_id > v_undo_id;
select count(undo_id)
from msc_undo_summary
where plan_id = v_plan_id
and sr_instance_id = v_sr_instance_id
and transaction_id = v_transaction_id
and table_changed = v_table_changed
and last_updated_by <> v_user
--and last_update_date > v_date
--and parent_id is null
and undo_id > v_undo_id;
select firm_planned_type
from msc_supplies
where transaction_id = v_transaction_id
and sr_instance_id = v_sr_instance_id
and plan_id = v_plan_id ;
select undo_id
from msc_undo_summary
where plan_id = v_plan_id
and action = 4;
l_last_updated_by number;
l_last_update_date date;
l_last_updated_by,
l_last_update_date,
l_identifier1_name,
l_identifier2_name,
l_identifier3_name ;
if (fnd_global.user_id <> l_last_updated_by ) then
x_return_status := FND_API.G_RET_STS_ERROR;
l_table_changed, l_last_updated_by,
l_last_update_date);
l_transaction_id, l_table_changed, l_last_updated_by,
l_last_update_date);
l_table_changed, l_last_updated_by,
l_last_update_date);
l_transaction_id, l_table_changed, l_last_updated_by,
l_last_update_date);