[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 1
from user_tab_columns
where table_name ='OE_MRP_OPEN_DEMANDS_V'
and column_name = p_column_name;
'insert into mrp_atp_schedule_temp '||
'(action, '||
'calling_module, '||
'sequence_number, '||
'session_id, '||
'scenario_id, '||
'firm_flag, '||
'status_flag, '||
'insert_flag, '||
'order_header_id, '||
'order_number, '||
'order_line_id, '||
'order_line_number, '||
'inventory_item_id, '||
'inventory_item_name, '||
'sr_instance_id, '||
'source_organization_id, '||
'source_organization_code, '||
'old_source_organization_code, '||
'quantity_ordered, '||
'uom_code, '||
'scheduled_ship_date, '||
'scheduled_arrival_date, '||
'old_line_schedule_date, '||
'requested_ship_date, '||
'requested_arrival_date, '||
'promise_date, '||
'latest_acceptable_date, '||
'delivery_lead_time, '||
'ship_method, '||
'demand_class, '||
'ship_set_id, '||
'ship_set_name, '||
'arrival_set_id, '||
'arrival_set_name, '||
'customer_id, '||
'customer_site_id, '||
'customer_name, '||
'customer_location, '||
'shipment_number, '||
'option_number, '||
'old_source_organization_id, '||
'old_demand_class, '||
'creation_date, '||
'created_by, '||
'last_update_date, '||
'last_updated_by, '||
'last_update_login, '||
'freight_carrier, '||
'flow_status_code '||
') ( '||
'select '||
'110, '||
'-1, '||
':l_seq_num, '||
':p_session_id, '||
'1, ' || -- scenario_id
'2, '|| -- firm_flag 2 -> NO
'1, '|| -- status_flag 1 -> INPUT
':l_profile_value, '|| -- insert_flag 1 -> INPUT
'omodv.header_id, '||
'omodv.order_number, '||
'omodv.line_id, '||
'omodv.line_number, '||
'omodv.inventory_item_id, '||
'omodv.ordered_item, '||
'maai.instance_id, '||
'decode(:p_overwrite,1,NULL,omodv.ship_from_org_id), '||
'decode(:p_overwrite,1,NULL,ood.organization_code), '||
'ood.organization_code, '||
'omodv.ordered_quantity, '||
'omodv.order_quantity_uom, '||
'omodv.schedule_ship_date, '||
'omodv.schedule_arrival_date, '||
'decode(omodv.schedule_ship_date,NULL, '||
'omodv.schedule_arrival_date,omodv.schedule_ship_date), '||
'omodv.request_ship_date, '||
'omodv.request_arrival_date, '||
'omodv.promise_date, '||
'omodv.latest_acceptable_date, '||
'omodv.delivery_lead_time, '||
'omodv.shipping_method_code, '||
'omodv.demand_class_code, '||
'omodv.ship_set_id, '||
'omodv.ship_set_name, '||
'omodv.arrival_set_id, '||
'omodv.arrival_set_name, '||
'omodv.sold_to_org_id, '||
'omodv.ship_to_org_id, '||
'hp.party_name, '||
'ras.location, '||
'omodv.shipment_number, '||
'omodv.option_number, '||
'omodv.ship_from_org_id, '||
'omodv.demand_class_code, '||
'sysdate, '||
'FND_GLOBAL.USER_ID, '||
'sysdate, '||
'FND_GLOBAL.USER_ID, '||
'FND_GLOBAL.USER_ID ';
select to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS')
into l_time
from dual;
delete from mrp_atp_schedule_temp
where session_id = -444;
l_stmt := ' INSERT into mrp_atp_schedule_temp
(order_line_id,
order_header_id,
scenario_id,
session_id,
inventory_item_id,
ship_set_id,
arrival_set_id,
sequence_number,
ato_model_line_id,
top_model_line_id,
inventory_item_name, ---item_type_code
order_number,
scheduled_ship_date,
scheduled_arrival_date,
requested_ship_date,
requested_arrival_date,
promise_date,
sr_instance_id)
SELECT omodv.line_id,
omodv.header_id,
0,
-444,
0,
omodv.ship_set_id,
omodv.arrival_set_id,
0,
omodv.ato_line_id,
omodv.top_model_line_id,
omodv.item_type_code,
omodv.order_number,
omodv.schedule_ship_date ,
omodv.schedule_arrival_date,
omodv.request_ship_date,
omodv.request_arrival_date,
omodv.promise_date,
omodv.planning_priority
FROM
oe_mrp_open_demands_v omodv
WHERE 1=1 ';
l_stmt := ' INSERT into mrp_atp_schedule_temp
(order_line_id,
order_header_id,
scenario_id, -- not null col
session_id, -- not null col
inventory_item_id, -- not null col
ship_set_id,
arrival_set_id,
sequence_number,
ato_model_line_id,
top_model_line_id,
inventory_item_name, -- -item_type_code
order_number,
scheduled_ship_date,
scheduled_arrival_date,
requested_ship_date,
requested_arrival_date,
promise_date)
SELECT
omodv.line_id,
omodv.header_id,
0,
-444,
0,
omodv.ship_set_id,
omodv.arrival_set_id,
0,
omodv.ato_line_id,
omodv.top_model_line_id,
omodv.item_type_code,
omodv.order_number,
omodv.schedule_ship_date ,
omodv.schedule_arrival_date,
omodv.request_ship_date,
omodv.request_arrival_date,
omodv.promise_date
FROM
oe_mrp_open_demands_v omodv
WHERE 1=1 ';
' and not exists ( select 1
from wsh_delivery_details
where source_line_id =omodv.line_id
and source_code = '||' ''OE'' '||
' and released_status in ( '||' ''Y'' '|| ', '
||' ''S'' '|| ', '
||' ''C'' '|| ' ) ) ';
msc_sch_wb.atp_debug(' INSERTED into mrp table '|| SQL%ROWCOUNT);
l_stmt:= 'select distinct
omodv1.line_id,
omodv1.ship_set_id,
omodv1.arrival_set_id,
0,
omodv1.ato_line_id,
omodv1.top_model_line_id,
omodv1.item_type_code,
omodv1.order_number,
omodv1.schedule_ship_date,
omodv1.schedule_arrival_date,
omodv1.request_ship_date,
omodv1.request_arrival_date,
omodv1.promise_date,
omodv1.order_number,
omodv1.planning_priority
from oe_mrp_open_demands_v omodv1,
mrp_atp_schedule_temp mrp
where mrp.session_id = -444
and mrp.order_header_id = omodv1.header_id
and ( mrp.order_line_id = omodv1.line_id
OR
nvl(mrp.ship_set_id, -1) = nvl(omodv1.ship_set_id, -2)
OR
nvl(mrp.arrival_set_id, -1) = nvl(omodv1.arrival_set_id, -2))';
l_stmt:= 'select distinct
omodv1.line_id,
omodv1.ship_set_id,
omodv1.arrival_set_id,
0,
omodv1.ato_line_id,
omodv1.top_model_line_id,
omodv1.item_type_code,
omodv1.order_number,
omodv1.schedule_ship_date,
omodv1.schedule_arrival_date,
omodv1.request_ship_date,
omodv1.request_arrival_date,
omodv1.promise_date,
omodv1.order_number,
0
from oe_mrp_open_demands_v omodv1,
mrp_atp_schedule_temp mrp
where mrp.session_id = -444
and mrp.order_header_id = omodv1.header_id
and ( mrp.order_line_id = omodv1.line_id
OR
nvl(mrp.ship_set_id, -1) = nvl(omodv1.ship_set_id, -2)
OR
nvl(mrp.arrival_set_id, -1) = nvl(omodv1.arrival_set_id, -2))';
' and not exists ( select 1
from wsh_delivery_details
where source_line_id =omodv1.line_id
and source_code = '||' ''OE'' '||
' and released_status in ( '||' ''Y'' '|| ', '
||' ''S'' '|| ', '
||' ''C'' '|| ' ) ) ';
msc_sch_wb.atp_debug('POPULATE_TEMP_TABLE : ' || ' Num of order lines selected '||num_of_lines);
select to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS')
into l_time
from dual;
SELECT
Rowidtochar(a.ROWID),
a.inventory_item_id,
a.inventory_item_name,
a.organization_id,
a.sr_instance_id,
Decode(override_flag,'Y',Nvl(a.firm_source_org_id,
a.source_organization_id),
a.source_organization_id),
Decode(override_flag,'Y',Nvl(a.firm_source_org_code,
a.source_organization_code),
a.source_organization_code),
a.order_line_id,
a.Scenario_Id,
a.Order_Header_Id,
a.order_number,
a.Calling_Module,
a.Customer_Id,
a.Customer_Site_Id,
a.Destination_Time_Zone,
0, -- send qty = 0 to atp
a.uom_code,
Decode(override_flag,'Y', Nvl(a.firm_ship_date,a.requested_ship_date),
a.requested_ship_date),
Decode(override_flag,'Y', Nvl(a.firm_arrival_date,a.requested_arrival_date)
, a.requested_arrival_date),
date_null, -- a.Earliest_Acceptable_Date,
a.Latest_Acceptable_Date,
a.Delivery_Lead_Time,
a.Freight_Carrier,
a.Ship_Method,
a.Demand_Class,
a.Ship_Set_Name,
-- When it is put back into the table the name will be used.
a.arrival_set_id, --a.Arrival_Set_Name
-- we don't append source_org since they can be different
-- and we don't need it since we don't have pick sources
a.Override_Flag,
a.Action,
date_null, --a.Ship_Date, ??? scheduled_ship_date
number_null, -- a.Available_Quantity,
number_null, -- a.Requested_Date_Quantity,
date_null, -- a.Group_Ship_Date,
date_null, -- a.Group_Arrival_Date,
a.Vendor_Id,
a.Vendor_Name,
a.Vendor_Site_Id,
a.Vendor_Site_Name,
a.Insert_Flag,
number_null, -- a.Error_Code,
char_1_null, -- a.Error_Message
a.old_source_organization_id,
a.old_demand_class,
a.atp_lead_time, -- bug 1303240
null, --substitution_typ_code,
null, -- REQ_ITEM_DETAIL_FLAG
2, -- ATP Pegging
a.assignment_set_id, -- ATP Pegging
a.sequence_number,
a.firm_flag,
a.order_line_number,
a.option_number,
a.shipment_number,
a.item_desc,
a.old_line_schedule_date,
a.old_source_organization_code,
a.firm_source_org_id,
a.firm_source_org_code,
a.firm_ship_date,
a.firm_arrival_date,
a.ship_method_text,
a.ship_set_id,
a.arrival_set_id,
a.PROJECT_ID,
a.TASK_ID,
a.PROJECT_NUMBER,
a.TASK_NUMBER,
a.Top_Model_line_id,
a.ATO_Model_Line_Id,
a.Parent_line_id,
a.Config_item_line_id,
a.Validation_Org,
a.Component_Sequence_ID,
a.Component_Code,
a.line_number,
a.included_item_flag
bulk collect into
x_atp_rec.row_id,
x_atp_rec.Inventory_Item_Id,
x_atp_rec.Inventory_Item_Name,
x_atp_rec.organization_id,
x_atp_rec.instance_id,
x_atp_rec.Source_Organization_Id,
x_atp_rec.Source_Organization_Code,
x_atp_rec.Identifier,
x_atp_rec.Scenario_Id,
x_atp_rec.Demand_Source_Header_Id,
x_atp_rec.order_number,
x_atp_rec.Calling_Module,
x_atp_rec.Customer_Id,
x_atp_rec.Customer_Site_Id,
x_atp_rec.Destination_Time_Zone,
x_atp_rec.Quantity_Ordered,
x_atp_rec.Quantity_UOM,
x_atp_rec.Requested_Ship_Date,
x_atp_rec.Requested_Arrival_Date,
x_atp_rec.Earliest_Acceptable_Date,
x_atp_rec.Latest_Acceptable_Date,
x_atp_rec.Delivery_Lead_Time,
x_atp_rec.Freight_Carrier,
x_atp_rec.Ship_Method,
x_atp_rec.Demand_Class,
x_atp_rec.Ship_Set_Name,
x_atp_rec.Arrival_Set_Name,
x_atp_rec.Override_Flag,
x_atp_rec.Action,
x_atp_rec.Ship_Date,
x_atp_rec.Available_Quantity,
x_atp_rec.Requested_Date_Quantity,
x_atp_rec.Group_Ship_Date,
x_atp_rec.Group_Arrival_Date,
x_atp_rec.Vendor_Id,
x_atp_rec.Vendor_Name,
x_atp_rec.Vendor_Site_Id,
x_atp_rec.Vendor_Site_Name,
x_atp_rec.Insert_Flag,
x_atp_rec.Error_Code,
x_atp_rec.message,
x_atp_rec.old_source_organization_id,
x_atp_rec.old_demand_class,
x_atp_rec.atp_lead_time, -- bug 1303240
x_atp_rec.substitution_typ_code,
x_atp_rec.REQ_ITEM_DETAIL_FLAG,
x_atp_rec.attribute_02, -- ATP Pegging
x_atp_rec.attribute_03,
x_atp_rec.sequence_number,
x_atp_rec.firm_flag,
x_atp_rec.order_line_number,
x_atp_rec.option_number,
x_atp_rec.shipment_number,
x_atp_rec.item_desc,
x_atp_rec.old_line_schedule_date,
x_atp_rec.old_source_organization_code,
x_atp_rec.firm_source_org_id,
x_atp_rec.firm_source_org_code,
x_atp_rec.firm_ship_date,
x_atp_rec.firm_arrival_date,
x_atp_rec.ship_method_text,
x_atp_rec.ship_set_id,
x_atp_rec.arrival_set_id,
x_atp_rec.PROJECT_ID,
x_atp_rec.TASK_ID,
x_atp_rec.PROJECT_NUMBER,
x_atp_rec.TASK_NUMBER,
x_atp_rec.Top_Model_line_id,
x_atp_rec.ATO_Model_Line_Id,
x_atp_rec.Parent_line_id,
x_atp_rec.Config_item_line_id,
x_atp_rec.Validation_Org,
x_atp_rec.Component_Sequence_ID,
x_atp_rec.Component_Code,
x_atp_rec.line_number,
x_atp_rec.included_item_flag
from mrp_atp_schedule_temp a
where a.session_id = p_session_id
and a.status_flag = 1
order by a.sequence_number;
select to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS')
into l_time
from dual;
select quantity_ordered, order_line_id, session_id
bulk collect into
p_atp_qty_ordered_temp.quantity_ordered,
p_atp_qty_ordered_temp.order_line_id,
p_atp_qty_ordered_temp.session_id
from mrp_atp_schedule_temp
where session_id = p_session_id
and status_flag =1 ;
select to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS')
into l_time
from dual;
select to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS')
into l_time
from dual;
update_schedule_qties(p_atp_qty_ordered_temp,
l_return_status,
l_error_message);
select to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS')
into l_time
from dual;
select to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS')
into l_time
from dual;
msc_sch_wb.atp_debug('RESCHEDULE: ' || ' error is update_qty '|| l_error_message);
PROCEDURE update_schedule_qties(p_atp_qty_ordered_temp IN MSC_BAL_UTILS.ATP_QTY_ORDERED_TYP,
p_return_status out nocopy VARCHAR2,
p_error_message out nocopy VARCHAR2) IS
l_count NUMBER :=0;
select count(*)
INTO l_count
from mrp_atp_schedule_temp
where session_id = p_atp_qty_ordered_temp.session_id(1)
and status_flag = 1;
msc_sch_wb.atp_debug('MSC_BAL_UTILS.update_schedule_qties status_flag = 1 rec are '|| l_count);
select count(*)
INTO l_count
from mrp_atp_schedule_temp
where session_id = p_atp_qty_ordered_temp.session_id(1)
and status_flag = 2;
msc_sch_wb.atp_debug('MSC_BAL_UTILS.update_schedule_qties status_flag = 2 rec are '|| l_count);
select count(*)
INTO l_count
from mrp_atp_schedule_temp
where session_id = p_atp_qty_ordered_temp.session_id(1);
msc_sch_wb.atp_debug('MSC_BAL_UTILS.update_schedule_qties TOTAL rec are '|| l_count);
update mrp_atp_schedule_temp
set status_flag = 1,
quantity_ordered = p_atp_qty_ordered_temp.quantity_ordered(lCounter)
where session_id =p_atp_qty_ordered_temp.session_id(lCounter)
and order_line_id = p_atp_qty_ordered_temp.order_line_id(lCounter)
and status_flag = 2 ;
msc_sch_wb.atp_debug('MSC_BAL_UTILS.update_schedule_qties ' ||p_atp_qty_ordered_temp.order_line_id.COUNT);
select count(*)
INTO l_count
from mrp_atp_schedule_temp
where session_id = p_atp_qty_ordered_temp.session_id(1);
msc_sch_wb.atp_debug('MSC_BAL_UTILS.update_schedule_qties TOTAL rec are '|| l_count);
END update_schedule_qties;
select count(*)
from mrp_atp_schedule_temp
where session_id = p_session_id
and status_flag = 2;
select to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS')
into l_time
from dual;
delete from mrp_atp_schedule_temp
where session_id = p_session_id
and status_flag = 1;
delete from mrp_atp_details_temp
where session_id = p_session_id;
update mrp_atp_schedule_temp
set status_flag = 1
where session_id = p_session_id
and status_flag = 2;
select quantity_ordered, order_line_id, session_id
bulk collect into
p_atp_qty_ordered_temp.quantity_ordered,
p_atp_qty_ordered_temp.order_line_id,
p_atp_qty_ordered_temp.session_id
from mrp_atp_schedule_temp
where session_id = p_session_id
and status_flag =1 ;
update_schedule_qties(p_atp_qty_ordered_temp,
l_return_status,
l_error_message);
select to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS')
into l_time
from dual;
select to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS')
into l_time
from dual;
select to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS')
into l_time
from dual;
' error in update_qty '|| l_error_message);
select 1
from user_tab_columns
where table_name ='OE_MRP_OPEN_DEMANDS_V'
and column_name ='FLOW_STATUS_CODE';
' update mrp_atp_schedule_temp a set flow_status_code = '||
' ( select flow_status_code from oe_mrp_open_demands_v b '||
' where a.order_line_id = b.line_id ) '||
' where a.session_id = :p_session_id ';
select to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS')
into l_time
from dual;
select to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS')
into l_time
from dual;
select to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS')
into l_time
from dual;
select to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS')
into l_time
from dual;
select to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS')
into l_time
from dual;
SELECT mrp.order_line_id,
omodv.org_id,
mrp.order_header_id,
mrp.source_organization_id,
nvl(mrp.group_ship_date,mrp.scheduled_ship_date),
nvl(mrp.group_arrival_date,mrp.scheduled_arrival_date),
to_date(null), --earliest_ship_date
mrp.delivery_lead_time,
mrp.ship_method,
decode(mrp.firm_flag, 1, 'Y', 2, 'N')
FROM mrp_atp_schedule_temp mrp,
oe_mrp_open_demands_v omodv
WHERE mrp.session_id = p_session_id
AND mrp.order_line_id = omodv.line_id
AND mrp.order_header_id = omodv.header_id
AND mrp.status_flag = 2
AND (mrp.error_code is NULL OR
(mrp.error_code is not NULL and mrp.error_code IN ('0','150','61')))
ORDER BY omodv.org_id, mrp.order_header_id, omodv.arrival_set_id,
omodv.ship_set_id, omodv.top_model_line_id, omodv.ato_line_id;
' before calling Update_Scheduling_Results '
||p_so_table.count);
select ltrim(rtrim(value))
into l_dir
from (select value from v$parameter2
where name='utl_file_dir' order by rownum desc)
where rownum <2;
select value
into l_dir
from v$parameter
where name = 'utl_file_dir';
SELECT oe_msg_request_id_s.nextval
INTO l_request_id
FROM dual;
OE_SCHEDULE_GRP.Update_Scheduling_Results(
p_so_table,
l_request_id,
x_return_status);
|| ' after calling Update_Results_from_backlog_wb '
||x_return_status||' '|| x_return_status);
x_msg_data := 'Unexpected error in oe_order_sch_util.Update_Results_from_backlog_wb ';
oe_order_sch_util.Update_Results_from_backlog_wb
( mast_table
, x_msg_count
, x_msg_data
, x_return_status);
msc_sch_wb.atp_debug('call_oe_api: ' || ' after calling Update_Results_from_backlog_wb II '
||x_return_status||' '||x_msg_data);
x_msg_data := 'Unexpected error in oe_order_sch_util.Update_Results_from_backlog_wb II ';
PROCEDURE update_seq(p_session_id NUMBER,
p_seq_alter IN OUT NoCopy mrp_bal_utils.seq_alter,
x_msg_count OUT NoCopy NUMBER,
x_msg_data OUT NoCopy VARCHAR2,
x_return_status OUT NoCopy VARCHAR2)
IS
l_old_seq NUMBER;
delete from mrp_atp_schedule_temp
where session_id = p_session_id
and status_flag = -88;
msc_sch_wb.atp_debug(' entered update_seq '
||p_seq_alter.order_line_id.COUNT);
INSERT INTO mrp_atp_schedule_temp (session_id,
inventory_item_id,
scenario_id,
status_flag,
sequence_number,
order_line_id,
quantity_ordered, --old_seq_num,
available_quantity) --sequence_diff)
VALUES (p_session_id,
-88,
-88,
-88,
p_seq_alter.seq_num(j),
p_seq_alter.order_line_id(j),
p_seq_alter.orig_seq_num(j),
p_seq_alter.seq_diff(j));
l_stmt := ' SELECT
sequence_number,
order_line_id,
quantity_ordered, --old_seq_num,
available_quantity --sequence_diff
FROM mrp_atp_schedule_temp
WHERE status_flag = -88
AND session_id = '||p_session_id
|| 'order by sequence_number';
select count(*)
INTO l_num_rec
from mrp_atp_schedule_temp
where status_flag = -88
and session_id = p_session_id;
delete from mrp_atp_schedule_temp
where session_id = p_session_id
and status_flag = -88;
SELECT MAX(sequence_number)
INTO l_max_seq
FROM mrp_atp_schedule_temp
WHERE session_id = p_session_id
and status_flag = 1;
select sequence_number, sequence_number + p_seq_alter.seq_diff(j)
into l_old_seq, l_new_seq
from mrp_atp_schedule_temp
where
order_line_id = p_seq_alter.order_line_id(j)
and session_id = p_session_id
and status_flag = 1;
select sequence_number, sequence_number + p_seq_alter.seq_diff(j)
into l_old_seq, l_new_seq
from mrp_atp_schedule_temp
where
NVL(p_seq_alter.ship_set_id(j),p_seq_alter.arrival_set_id(j))
= Decode(p_seq_alter.ship_set_id(j),NULL, arrival_set_id, ship_set_id)
and session_id = p_session_id
and status_flag = 1;
msc_sch_wb.atp_debug('update_seq: ' || ' old seq '||l_old_seq||' new_seq '||l_new_seq);
-- select all affected nodes including the changed node
-- second decode. depening on the direction of change, update the
-- affected nodes.
IF PG_DEBUG in ('Y', 'C') THEN
msc_sch_wb.atp_debug('update_seq: ' || ' b4 update ');
update mrp_atp_schedule_temp mast
set mast.sequence_number = p_seq_alter.seq_num(j)
, last_update_date = sysdate
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_login = FND_GLOBAL.USER_ID
WHERE mast.session_id = p_session_id
AND mast.order_line_id = p_seq_alter.order_line_id(j) ;
update mrp_atp_schedule_temp mast
set mast.sequence_number = Decode( Sign(p_seq_alter.seq_diff(j)),
-1,mast.sequence_number + 1,
1, mast.sequence_number -1)
-- dsting
, last_update_date = sysdate
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_login = FND_GLOBAL.USER_ID
WHERE
mast.sequence_number BETWEEN
Decode(Sign(p_seq_alter.seq_diff(j)),
-1, p_seq_alter.seq_num(j),
+1, l_old_seq) AND
Decode(Sign(p_seq_alter.seq_diff(j)),
-1, l_old_seq,
+1, p_seq_alter.seq_num(j))
AND mast.session_id = p_session_id
AND mast.order_line_id <> p_seq_alter.order_line_id(j) ;
update mrp_atp_schedule_temp mast
set mast.sequence_number = record_firm_filter.seq_num(b)
, last_update_date = sysdate
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_login = FND_GLOBAL.USER_ID
WHERE
mast.session_id = p_session_id
AND mast.order_line_id = record_firm_filter.order_line_id(b) ;
msc_sch_wb.atp_debug('IN THE LOOP FOR FIRM: ' || ' 1 after update '||
' seq num '||record_firm_filter.seq_num(b)||
' line_id ' || record_firm_filter.order_line_id(b) || ' ' ||SQL%ROWCOUNT);
update mrp_atp_schedule_temp mast
set mast.sequence_number = Decode( Sign(p_seq_alter.seq_diff(j)),
-1, mast.sequence_number + 1,
1, mast.sequence_number -1)
where
mast.sequence_number = record_firm_filter.seq_num(b)
and mast.order_line_id <> record_firm_filter.order_line_id(b)
and mast.session_id = p_session_id;
msc_sch_wb.atp_debug('IN THE LOOP FOR FIRM: ' || ' 2 after update '||SQL%ROWCOUNT);
msc_sch_wb.atp_debug('update_seq: ' || ' after update '||SQL%ROWCOUNT);
msc_sch_wb.atp_debug('Excp in update_seq '||Substr(Sqlerrm,1,80));
x_msg_data := 'Excp in update_seq '||Substr(Sqlerrm,1,80);
END update_seq;