[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1
INTO l_validate_combinition
FROM mtl_system_items_b msi,
org_organization_definitions org
WHERE msi.inventory_item_id= p_inventory_item_id
AND org.organization_id=msi.organization_id
AND sysdate<=nvl(org.disable_date,sysdate)
AND org.organization_id=p_ship_from_org_id
AND rownum=1 ;
Select nvl(arrival_set_id,ship_set_id)
Into l_set_id
From oe_order_lines_all
Where line_id = p_top_model_line_id;
l_rsv_update BOOLEAN := FALSE;
SELECT line_id
FROM oe_order_lines_all
WHERE header_id = p_header_id
AND open_flag = 'Y'
AND ato_line_id is NULL
AND item_type_code IN ('MODEL', 'CLASS', 'KIT')
AND schedule_status_code is NULL;
SELECT line_id,schedule_status_code,
shippable_flag
FROM oe_order_lines_all
WHERE header_id = p_header_id
AND open_flag = 'Y'
AND line_category_code <> 'RETURN'
AND item_type_code <> 'SERVICE'
AND source_type_code <> OE_GLOBALS.G_SOURCE_EXTERNAL
ORDER BY arrival_set_id,ship_set_id,line_number,shipment_number,nvl(option_number,-1);
SELECT line_id,ship_set_id,arrival_set_id, shippable_flag,
shipping_interfaced_flag,orig_sys_document_ref, orig_sys_line_ref,
orig_sys_shipment_ref, change_sequence, source_document_type_id,
source_document_id, source_document_line_id, order_source_id
FROM oe_order_lines_all
WHERE header_id = p_header_id
AND open_flag = 'Y'
AND line_category_code <> 'RETURN'
AND schedule_status_code IS NOT NULL
ORDER BY arrival_set_id,ship_set_id,line_number,shipment_number,nvl(option_number,-1);
oe_debug_pub.add( 'LINE SELECTED FOR SCHEDULING **** : ' || L_LINE_REC.LINE_ID , 1 ) ;
x_line_tbl(I).operation := OE_GLOBALS.G_OPR_UPDATE;
oe_debug_pub.add( 'LINE SELECTED FOR RESERVE **** : '|| L_LINE_REC.LINE_ID , 1 ) ;
l_rsv_update := TRUE;
,p_rsv_update => l_rsv_update
,x_return_status => l_return_status);
oe_debug_pub.add( 'LINE SELECTED FOR RESERVE **** : '|| L_LINE_REC.LINE_ID , 1 ) ;
x_line_tbl(I).operation := OE_GLOBALS.G_OPR_UPDATE;
oe_debug_pub.add( 'LINE SELECTED FOR ATP **** : ' || C1.LINE_ID , 1 ) ;
oe_debug_pub.add( 'LINE SELECTED FOR UNRES **** : ' || L_LINE_REC.LINE_ID , 1 ) ;
l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
oe_debug_pub.add( 'LINE SELECTED FOR UNSCH **** : ' || C2.LINE_ID , 1 ) ;
l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
Initially system selects few scheduling related attributes to see whether the
line has to be selected for scheduling or not. If it has to be selected then
checks if it is part of non smc and model is also selected by user. If Model is selected by user we will ignore the line or else we will select the line for
processing. If the line selected is Included item and No SMC, make sure not only
it's modle is selected, also check for its immediate parent. If its immediate
parent is selected then ignore the included item, since included item will be
selected by it's parent.
If a line is part of SMC or ATO or if it is a TOP MODEL, selected whole model for processing. If it is a non smc class or kit, select its included items if any.
----------------------------------------------------------------- */
PROCEDURE Query_Schedule_Lines
( p_selected_tbl IN OE_GLOBALS.Selected_record_Tbl, --R12.MOAC
p_sch_action IN VARCHAR2,
x_line_tbl IN OUT NOCOPY OE_Order_PUB.Line_Tbl_Type
)
IS
l_line_tbl OE_Order_PUB.Line_Tbl_Type;
FOR L IN 1..p_selected_tbl.count LOOP --R12.MOAC
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'PROCESSING LINE_ID' || P_SELECTED_TBL(L).ID1 , 2 ) ;
l_line_id := p_selected_tbl(L).id1;
Select Arrival_set_id, Ship_set_id,top_model_line_id,
Ship_model_complete_flag, ato_line_id,item_type_code,
Schedule_status_code,line_category_code,header_id,open_flag,
source_type_code,link_to_line_id,
orig_sys_document_ref, orig_sys_line_ref, orig_sys_shipment_ref,
source_document_type_id, change_sequence, source_document_id,
source_document_line_id, order_source_id
Into l_arrival_set_id, l_ship_set_id, l_top_model_line_id,
l_smc_flag, l_ato_line_id, l_item_type_code,
l_schedule_status_code,l_line_category_code,l_header_id,l_open_flag,
l_source_type_code,l_link_to_line_id,
l_orig_sys_document_ref, l_orig_sys_line_ref, l_orig_sys_shipment_ref,
l_source_document_type_id, l_change_sequence, l_source_document_id,
l_source_document_line_id, l_order_source_id
From oe_order_lines_all
Where line_id = l_line_id;
ELSE -- line not scheduled, please select the line to schedule.
l_query := 'Y';
FOR K IN 1..p_selected_tbl.count LOOP -- R12.MOAC
IF l_top_model_line_id = p_selected_tbl(K).id1
OR (l_item_type_code = OE_GLOBALS.G_ITEM_INCLUDED AND
l_link_to_line_id = p_selected_tbl(K).id1) THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'PARENT LINE IS PART OF TABLE' || P_SELECTED_TBL(K).ID1 , 2 ) ;
x_line_tbl.delete(x_line_tbl.count);
Initially system selects few scheduling related attributes to see whether the
line has to be selected for scheduling or not. If it has to be selected then
check if it is part of non smc and model is also selected by user. If Model is selected by user we will ignore the line or else we will select the line for
processing. If the line selected is Included item and No SMC, make sure not only
it's modle is selected, also check for its immediate parent. If its immediate
parent is selected then ignore the included item, since included item will be
selected by it's parent.
If a line is part of SMC or ATO or if it is a TOP MODEL, selected whole model
for processing. If it is a non smc class or kit, select its included items if any.
----------------------------------------------------------------- */
PROCEDURE Query_Unschedule_Lines
( p_selected_tbl IN OE_GLOBALS.Selected_record_Tbl, --R12.MOAC
p_sch_action IN VARCHAR2,
x_line_tbl IN OUT NOCOPY OE_Order_PUB.Line_Tbl_Type
)
IS
l_line_tbl OE_Order_PUB.Line_Tbl_Type;
FOR L IN 1..p_selected_tbl.count LOOP --R12.MOAC
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'PROCESSING LINE_ID' || p_selected_tbl(L).id1 , 2 ) ;
l_line_id := p_selected_tbl(L).id1;
Select Arrival_set_id, Ship_set_id,top_model_line_id,
Ship_model_complete_flag, ato_line_id,item_type_code,
Schedule_status_code,line_category_code,header_id,open_flag,
source_type_code,link_to_line_id,
orig_sys_document_ref, orig_sys_line_ref, orig_sys_shipment_ref,
source_document_type_id, change_sequence, source_document_id,
source_document_line_id, order_source_id
Into l_arrival_set_id, l_ship_set_id, l_top_model_line_id,
l_smc_flag, l_ato_line_id, l_item_type_code,
l_schedule_status_code,l_line_category_code,l_header_id,l_open_flag,
l_source_type_code,l_link_to_line_id,
l_orig_sys_document_ref, l_orig_sys_line_ref, l_orig_sys_shipment_ref,
l_source_document_type_id, l_change_sequence, l_source_document_id,
l_source_document_line_id, l_order_source_id
From oe_order_lines_all
Where line_id = l_line_id;
ELSE -- line not scheduled, please select the line to schedule.
l_query := 'Y';
FOR K IN 1..p_selected_tbl.count LOOP
IF l_top_model_line_id =p_selected_tbl(K).id1
OR (l_item_type_code = OE_GLOBALS.G_ITEM_INCLUDED AND
l_link_to_line_id = p_selected_tbl(K).id1) THEN
l_query := 'N';
x_line_tbl.delete(x_line_tbl.count);
Initially system selects few scheduling related attributes to see whether the
line has to be selected for Unreserve or not. If it has to be selected then
check if it is part of model and parent is also selected by user. If Model is selected by user we will ignore the line or else we will select the line for
processing. If the line selected is Included item, make sure not only it's modle is selected, also check for its immediate parent. If its immediate parent is selected then ignore the included item, since included item will be selected by it's parent.
If line is a top model selected all its children that are scheduled and it is a class or kit select its included items as well or else select by itself.
----------------------------------------------------------------- */
PROCEDURE Query_Unreserve_Lines
( p_selected_tbl IN OE_GLOBALS.Selected_record_Tbl, --R12.MOAC
p_sch_action IN VARCHAR2,
x_line_tbl IN OUT NOCOPY OE_Order_PUB.Line_Tbl_Type
)
IS
l_line_tbl OE_Order_PUB.Line_Tbl_Type;
FOR L IN 1..p_selected_tbl.count LOOP --R12.MOAC
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'PROCESSING LINE_ID' || P_SELECTED_TBL(L).ID1 , 2 ) ;
l_line_id := p_selected_tbl(L).id1;
Select Arrival_set_id, Ship_set_id,top_model_line_id,
Ship_model_complete_flag, ato_line_id,item_type_code,
Schedule_status_code,line_category_code,header_id,open_flag,
nvl(shipping_interfaced_flag,'N'),source_type_code,link_to_line_id,
orig_sys_document_ref, orig_sys_line_ref, orig_sys_shipment_ref,
source_document_type_id, change_sequence, source_document_id,
source_document_line_id, order_source_id
Into l_arrival_set_id, l_ship_set_id, l_top_model_line_id,
l_smc_flag, l_ato_line_id, l_item_type_code,
l_schedule_status_code,l_line_category_code,l_header_id,l_open_flag,
l_shipping_interfaced_flag,l_source_type_code,l_link_to_line_id,
l_orig_sys_document_ref, l_orig_sys_line_ref, l_orig_sys_shipment_ref,
l_source_document_type_id, l_change_sequence, l_source_document_id,
l_source_document_line_id, l_order_source_id
From oe_order_lines_all
Where line_id = l_line_id;
ELSE -- line not scheduled, please select the line to schedule.
l_query := 'Y';
FOR K IN 1..p_selected_tbl.count LOOP
IF l_top_model_line_id = p_selected_tbl(K).id1
OR (l_item_type_code = OE_GLOBALS.G_ITEM_INCLUDED AND
l_link_to_line_id = p_selected_tbl(K).id1) THEN
l_query := 'N';
x_line_tbl.delete(x_line_tbl.count);
Initially system selects few scheduling related attributes to see whether the
line has to be selected for reserve or not. If it has to be selected then
check if it is part of model and parent is also selected by user. If Model is selected by user we will ignore the line or else we will select the line for
processing. If the line selected is Included item, make sure not only it's
modle is selected, also check for its immediate parent. If its immediate parent
is selected then ignore the included item, since included item will be selected
by it's parent.
If top model is selected for processing then query whole model. If a line
selected is part of smc or ATO model and if it is not scheduled, then select
whole model or else select only the line for reservation. If class or kit is
selected then select its included items as well.
------------------------------------------------------------------*/
PROCEDURE Query_Reserve_Lines
( p_selected_tbl IN OE_GLOBALS.Selected_record_Tbl, --R12.MOAC
p_sch_action IN VARCHAR2,
x_line_tbl IN OUT NOCOPY OE_Order_PUB.Line_Tbl_Type
)
IS
l_line_tbl OE_Order_PUB.Line_Tbl_Type;
l_rsv_update BOOLEAN :=FALSE;
FOR L IN 1..p_selected_tbl.count LOOP --R12.MOAC
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'PROCESSING LINE_ID' || P_SELECTED_TBL(L).ID1 , 2 ) ;
l_line_id := p_selected_tbl(L).id1;
Select Arrival_set_id, Ship_set_id,top_model_line_id,
Ship_model_complete_flag, ato_line_id,item_type_code,
Schedule_status_code,line_category_code,header_id,open_flag,
source_type_code,link_to_line_id,
orig_sys_document_ref, orig_sys_line_ref, orig_sys_shipment_ref,
source_document_type_id, change_sequence, source_document_id,
source_document_line_id, order_source_id
Into l_arrival_set_id, l_ship_set_id, l_top_model_line_id,
l_smc_flag, l_ato_line_id, l_item_type_code,
l_schedule_status_code,l_line_category_code,l_header_id,l_open_flag,
l_source_type_code,l_link_to_line_id,
l_orig_sys_document_ref, l_orig_sys_line_ref, l_orig_sys_shipment_ref,
l_source_document_type_id, l_change_sequence, l_source_document_id,
l_source_document_line_id, l_order_source_id
From oe_order_lines_all
Where line_id = l_line_id;
FOR K IN 1..p_selected_tbl.count LOOP
IF l_top_model_line_id = p_selected_tbl(K).id1
OR (l_item_type_code = OE_GLOBALS.G_ITEM_INCLUDED AND
l_link_to_line_id = p_selected_tbl(K).id1) THEN
l_query := 'N';
SELECT line_id
INTO l_config_id
FROM oe_order_lines_all
WHERE header_id = l_header_id
AND ato_line_id = l_ato_line_id
AND item_type_code = 'CONFIG';
l_line_tbl.delete(l_line_tbl.count);
x_line_tbl.delete;
l_rsv_update := TRUE;
,p_rsv_update => l_rsv_update
,x_return_status => l_return_status);
Initially system selects few scheduling related attributes to see whether the
line has to be selected for atp_check or not. If it has to be selected then
check if it is part of model and parent is also selected by user. If Model is selected by user we will ignore the line or else we will select the line for
processing. If the line selected is Included item, make sure not only it's modle is selected, also check for its immediate parent. If its immediate parent is selected then ignore the included item, since included item will be selected by it's parent.
If top model is selected for processing or line part of smc or ato then query whole model. If class or kit is selected then select its included items as well.If line is selected which is part of set then select whole set for processing
------------------------------------------------------------------*/
PROCEDURE Query_ATP_CHECK_Lines
( p_selected_tbl IN OE_GLOBALS.Selected_record_Tbl, --R12.MOAC
p_sch_action IN VARCHAR2,
x_line_tbl IN OUT NOCOPY OE_Order_PUB.Line_Tbl_Type
)
IS
l_line_tbl OE_Order_PUB.Line_Tbl_Type;
FOR L IN 1..p_selected_tbl.count LOOP --R12.MOAC
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'PROCESSING LINE_ID ' || P_SELECTED_TBL(L).ID1 , 2 ) ;
l_line_id := p_selected_tbl(L).id1;
Select Arrival_set_id, Ship_set_id,top_model_line_id,
Ship_model_complete_flag, ato_line_id,item_type_code,
Schedule_status_code,line_category_code,header_id,open_flag,
source_type_code,link_to_line_id,
orig_sys_document_ref, orig_sys_line_ref, orig_sys_shipment_ref,
source_document_type_id, change_sequence, source_document_id,
source_document_line_id, order_source_id
Into l_arrival_set_id, l_ship_set_id, l_top_model_line_id,
l_smc_flag, l_ato_line_id, l_item_type_code,
l_schedule_status_code,l_line_category_code,l_header_id,l_open_flag,
l_source_type_code,l_link_to_line_id,
l_orig_sys_document_ref, l_orig_sys_line_ref, l_orig_sys_shipment_ref,
l_source_document_type_id, l_change_sequence, l_source_document_id,
l_source_document_line_id, l_order_source_id
From oe_order_lines_all
Where line_id = l_line_id;
FOR K IN 1..p_selected_tbl.count LOOP
IF l_top_model_line_id = p_selected_tbl(K).id1
OR (l_item_type_code = OE_GLOBALS.G_ITEM_INCLUDED AND
l_link_to_line_id = p_selected_tbl(K).id1) THEN
l_query := 'N';
x_line_tbl.delete(x_line_tbl.count);
Oe_Schedule_Util.g_atp_tbl.delete;
This procedure is called when lines are multi-selected and
scheduling action is performed.
Based on the requested action this API will call appropriate
query api's which will get all approprite a lines. Based on the
action call process group or multi atp check.
---------------------------------------------------------------*/
Procedure Schedule_Multi_lines
(p_selected_line_tbl IN OE_GLOBALS.Selected_Record_Tbl, --R12.MOAC
p_line_count IN NUMBER,
p_sch_action IN VARCHAR2,
x_atp_tbl OUT NOCOPY /* file.sql.39 change */ OE_ATP.Atp_Tbl_Type,
x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
IS
j Integer;
Oe_Schedule_Util.g_atp_tbl.delete;
p_selected_tbl => p_selected_line_tbl, --R12.MOAC
x_line_tbl => l_line_tbl);
p_selected_tbl => p_selected_line_tbl, --R12.MOAC
x_line_tbl => l_line_tbl);
p_selected_tbl => p_selected_line_tbl, --R12.MOAC
x_line_tbl => l_line_tbl);
p_selected_tbl => p_selected_line_tbl, --R12.MOAC
x_line_tbl => l_line_tbl);
p_selected_tbl => p_selected_line_tbl, --R12.MOAC
x_line_tbl => l_line_tbl);
l_line_tbl(I).operation := OE_GLOBALS.G_OPR_UPDATE;
when new lines are inserted to a SCHEDULE SET. A set being
a user defined ship or arrival set.When multiple lines are
iserted to the same set, this procedure is called once for
all the lines of the set.
If the included item is part of request table, check for the
parent. If parent exists in the table, assume that parent will
fetch the included items and remove included items from the list.
Changes have been made to copy override_atp flag from model/class/
kit to it's included items when the flag is set.
-------------------------------------------------------------------*/
Procedure Schedule_set_lines
( p_sch_set_tbl IN OE_ORDER_PUB.request_tbl_type
, x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
IS
l_line_rec OE_ORDER_PUB.line_rec_type;
Select line_id, shipping_interfaced_flag
from oe_order_lines_all
where top_model_line_id = l_top_model_line_id
and open_flag = 'Y'
and shipping_interfaced_flag = 'Y';
Select item_type_code,
link_to_line_id,
top_model_line_id,
ato_line_id,
header_id,
ship_set_id,
arrival_set_id
Into l_item_type_code,
l_link_to_line_id,
l_top_model_line_id,
l_ato_line_id,
l_header_id,
l_ship_set_id,
l_arrival_set_id
From oe_order_lines_all
Where line_id = p_sch_set_tbl(I).entity_id;
oe_debug_pub.add( 'ERROR WHILE SELECTING DATA' , 5 ) ;
Update oe_order_lines_all l
Set arrival_set_id = Null,
ship_set_id = Null
Where top_model_line_id = l_top_model_line_id
And open_flag = 'Y';
oe_debug_pub.add( 'UPDATE SHIPPING : CHILDREN OF MODEL ' || TO_CHAR ( OPTIONREC.LINE_ID ) , 1 ) ;
p_request_type => OE_GLOBALS.G_UPDATE_SHIPPING,
p_request_unique_key1 => OE_GLOBALS.G_OPR_UPDATE,
p_param1 => FND_API.G_TRUE,
x_return_status => x_return_status);
oe_debug_pub.add( 'LINE IS SELECTED FOR PROCESSING ' || P_SCH_SET_TBL ( I ) .ENTITY_ID , 1 ) ;
l_line_tbl(K).operation := OE_GLOBALS.G_OPR_UPDATE;
UPDATE OE_ORDER_LINES_ALL
SET SCHEDULE_SHIP_DATE = Null,
SCHEDULE_ARRIVAL_DATE = Null,
SHIP_FROM_ORG_ID = decode(re_source_flag,'N',ship_from_org_id,null),
SHIP_SET_ID = Null,
ARRIVAL_SET_ID = Null,
override_atp_date_code = Null
WHERE line_id = l_iline_tbl(I).line_id;
UPDATE OE_ORDER_LINES_ALL
SET
SCHEDULE_SHIP_DATE = p_sch_set_tbl(I).date_param1,
SCHEDULE_ARRIVAL_DATE = p_sch_set_tbl(I).date_param2,
SHIP_FROM_ORG_ID = p_sch_set_tbl(I).param4,
SHIP_SET_ID = p_sch_set_tbl(I).param6,
ARRIVAL_SET_ID = p_sch_set_tbl(I).param7
WHERE line_id = l_iline_tbl(I).line_id;
oe_schedule_util.Log_Delete_Set_Request
(p_header_id => l_iline_tbl(I).header_id,
p_line_id => l_iline_tbl(I).line_id,
p_set_id => nvl(l_iline_tbl(I).ship_set_id,
l_iline_tbl(I).arrival_set_id),
x_return_status => x_return_status);
oe_debug_pub.add( 'AFTER LOGGING DELETE SETS DELAYED REQUEST '
|| X_RETURN_STATUS , 1 ) ;
p_request_type => OE_GLOBALS.G_UPDATE_SHIPPING,
p_request_unique_key1 => OE_GLOBALS.G_OPR_UPDATE,
p_param1 => FND_API.G_TRUE,
x_return_status => x_return_status);
l_line_tbl(K).operation := OE_GLOBALS.G_OPR_UPDATE;
oe_debug_pub.add('Calling Update Set');
UPDATE OE_ORDER_LINES_ALL
SET SCHEDULE_SHIP_DATE = Null,
SCHEDULE_ARRIVAL_DATE = Null,
SHIP_FROM_ORG_ID = decode(re_source_flag,'N',ship_from_org_id,null),
SHIP_SET_ID = Null,
ARRIVAL_SET_ID = Null,
override_atp_date_code = Null
WHERE line_id = l_line_tbl(I).line_id;
UPDATE OE_ORDER_LINES_ALL
SET
SCHEDULE_STATUS_CODE = l_old_line_tbl(I).schedule_status_code,
VISIBLE_DEMAND_FLAG = l_old_line_tbl(I).visible_demand_flag,
SCHEDULE_SHIP_DATE = l_old_line_tbl(I).schedule_ship_date,
SCHEDULE_ARRIVAL_DATE = l_old_line_tbl(I).schedule_arrival_date,
SHIP_FROM_ORG_ID = l_old_line_tbl(I).ship_from_org_id,
SHIP_SET_ID = l_old_set_tbl(I).ship_set_id,
ARRIVAL_SET_ID = l_old_set_tbl(I).arrival_set_id
WHERE line_id = l_line_tbl(I).line_id;
oe_schedule_util.Log_Delete_Set_Request
(p_header_id => l_line_tbl(I).header_id,
p_line_id => l_line_tbl(I).line_id,
p_set_id => nvl(l_line_tbl(I).ship_set_id,l_line_tbl(I).arrival_set_id),
x_return_status => x_return_status);
oe_debug_pub.add( 'AFTER LOGGING DELETE SETS DELAYED REQUEST '
|| X_RETURN_STATUS , 1 ) ;
p_request_type => OE_GLOBALS.G_UPDATE_SHIPPING,
p_request_unique_key1 => OE_GLOBALS.G_OPR_UPDATE,
p_param1 => FND_API.G_TRUE,
x_return_status => x_return_status);
l_old_set_tbl.delete;
l_old_set_tbl.delete;
l_old_set_tbl.delete;
l_old_set_tbl.delete;
l_operation VARCHAR2(30) := 'UPDATE';
Select Schedule_ship_date, Schedule_arrival_date,
ship_to_org_id, ship_from_org_id, request_date,
Ship_set_id,arrival_Set_id
Into l_Schedule_ship_date, l_Schedule_arrival_date,
l_ship_to_org_id, l_ship_from_org_id, l_request_date,
l_ship_set_id,l_arrival_set_id
From OE_ORDER_LINES_ALL
Where line_id = l_line_id;
Select 1 into l_count from OE_ORDER_LINES_ALL
WHERE header_id = l_header_id and arrival_Set_id = p_request_rec.param1
AND rownum = 1;
Select 1 into l_count from OE_ORDER_LINES_ALL
WHERE header_id = l_header_id and ship_set_id = p_request_rec.param1
AND rownum = 1;
Select override_atp_date_code
Into l_line_tbl(I).override_atp_date_code
From oe_order_lines_all
Where header_id = l_line_tbl(I).header_id
And ato_line_id = l_line_tbl(I).ato_line_id
And override_atp_date_code = 'Y'
And rownum < 2;
Select override_atp_date_code
Into l_line_tbl(I).override_atp_date_code
From oe_order_lines_all
Where header_id = l_line_tbl(I).header_id
And line_id = l_line_tbl(I).link_to_line_id;
l_line_tbl(I).operation := OE_GLOBALS.G_OPR_UPDATE;
l_line_tbl(I).operation := OE_GLOBALS.G_OPR_UPDATE;
oe_schedule_util.OE_sch_Attrb_Tbl.delete(l_index);
OE_SCHEDULE_UTIL.OE_Override_Tbl.delete
(l_line_tbl(I).line_id);
UPDATE OE_ORDER_LINES_ALL
SET SCHEDULE_SHIP_DATE = Null,
SCHEDULE_ARRIVAL_DATE = Null,
SHIP_FROM_ORG_ID = decode(re_source_flag,'N',ship_from_org_id,null),
SHIP_SET_ID = Null,
ARRIVAL_SET_ID = Null,
override_atp_date_code = Null
WHERE line_id = l_line_tbl(I).line_id;
oe_schedule_util.Log_Delete_Set_Request
(p_header_id => l_line_tbl(I).header_id,
p_line_id => l_line_tbl(I).line_id,
p_set_id => nvl(l_line_tbl(I).ship_set_id,l_line_tbl(I).arrival_set_id),
x_return_status => x_return_status);
oe_debug_pub.add( 'AFTER LOGGING DELETE SETS DELAYED REQUEST ' || X_RETURN_STATUS , 1 ) ;
/* Removed the shipping_method_code from the following select to fix the bug 2916814 */
Select ship_from_org_id, ship_to_org_id, schedule_ship_date,
schedule_arrival_date,Shipping_Method_Code,
Freight_Carrier_Code,shipment_priority_code
INTO l_ship_from_org_id,l_ship_to_org_id,l_schedule_ship_date,
l_schedule_arrival_date,l_Shipping_Method_Code,
l_Freight_Carrier_Code,l_shipment_priority_code
From oe_order_lines_all
Where line_id = l_line_tbl(1).line_id;
oe_debug_pub.add( 'CALLING UPDATE SET' ) ;
OE_Set_Util.Update_Set
(p_Set_Id => l_set_id,
p_Ship_From_Org_Id => l_Ship_From_Org_Id,
p_Ship_To_Org_Id => l_Ship_To_Org_Id,
p_Schedule_Ship_Date => l_Schedule_Ship_Date,
p_Schedule_Arrival_Date => l_Schedule_Arrival_Date,
p_Freight_Carrier_Code => l_Freight_Carrier_Code,
p_Shipping_Method_Code => l_Shipping_Method_Code,
p_shipment_priority_code => l_shipment_priority_code,
X_Return_Status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
oe_debug_pub.add( 'AFTER CALLING UPDATE SET' ) ;
Select 'Y'
Into l_set_exists
From oe_order_lines_all
Where header_id = p_sch_set_tbl(I).param2
And (ship_set_id = p_sch_set_tbl(I).entity_id
Or arrival_set_id = p_sch_set_tbl(I).entity_id)
And open_flag = 'Y'
And rownum = 1;
l_sch_line_tbl(l_count).operation := 'UPDATE';
Select override_atp_date_code,
schedule_ship_date,
schedule_arrival_date
Into l_sch_line_tbl(l_count).override_atp_date_code,
l_sch_line_tbl(l_count).schedule_ship_date,
l_sch_line_tbl(l_count).schedule_arrival_date
From oe_order_lines_all
Where header_id = l_line_tbl(J).header_id
And ato_line_id = l_line_tbl(J).ato_line_id
And override_atp_date_code = 'Y'
And rownum < 2;
UPDATE OE_ORDER_LINES_ALL
SET SCHEDULE_SHIP_DATE = Null,
SCHEDULE_ARRIVAL_DATE = Null,
SHIP_FROM_ORG_ID = decode(re_source_flag,'N',ship_from_org_id,null),
SHIP_SET_ID = Null,
ARRIVAL_SET_ID = Null,
OVERRIDE_ATP_DATE_CODE = Null
WHERE line_id = p_sch_set_tbl(I).param3;
UPDATE OE_ORDER_LINES_ALL
SET SCHEDULE_SHIP_DATE = Null,
SCHEDULE_ARRIVAL_DATE = Null,
SHIP_FROM_ORG_ID = decode(re_source_flag,'N',ship_from_org_id,null),
SHIP_SET_ID = Null,
ARRIVAL_SET_ID = Null,
OVERRIDE_ATP_DATE_CODE = Null
WHERE top_model_line_id = p_sch_set_tbl(I).param4;
oe_schedule_util.Log_Delete_Set_Request
(p_header_id => l_sch_line_tbl(I).header_id,
p_line_id => l_sch_line_tbl(I).line_id,
p_set_id => nvl(l_sch_line_tbl(I).ship_set_id,l_sch_line_tbl(I).arrival_set_id),
x_return_status => x_return_status);
oe_debug_pub.add( 'AFTER LOGGING DELETE SETS DELAYED REQUEST '
|| X_RETURN_STATUS , 1 ) ;
UPDATE OE_ORDER_LINES_ALL
SET SCHEDULE_SHIP_DATE = Null,
SCHEDULE_ARRIVAL_DATE = Null,
SHIP_FROM_ORG_ID = decode(re_source_flag,'N',ship_from_org_id,null),
SHIP_SET_ID = Null,
ARRIVAL_SET_ID = Null,
OVERRIDE_ATP_DATE_CODE = Null
WHERE line_id = l_sch_line_tbl(I).line_id;
UPDATE OE_ORDER_LINES_ALL
SET SCHEDULE_SHIP_DATE = Null,
SCHEDULE_ARRIVAL_DATE = Null,
SHIP_FROM_ORG_ID = decode(re_source_flag,'N',ship_from_org_id,null),
SHIP_SET_ID = Null,
ARRIVAL_SET_ID = Null,
OVERRIDE_ATP_DATE_CODE = Null
WHERE top_model_line_id = l_sch_line_tbl(I).top_model_line_id;
OE_Set_Util.Update_Set
(p_Set_Id => Nvl(l_sch_line_tbl(I).arrival_Set_id,
l_sch_line_tbl(I).ship_Set_id),
p_Ship_From_Org_Id => l_sch_line_tbl(I).Ship_From_Org_Id,
p_Ship_To_Org_Id => l_sch_line_tbl(I).Ship_To_Org_Id,
p_Schedule_Ship_Date => l_sch_line_tbl(I).Schedule_Ship_Date,
p_Schedule_Arrival_Date => l_sch_line_tbl(I).Schedule_Arrival_Date,
p_Freight_Carrier_Code => l_sch_line_tbl(I).Freight_Carrier_Code,
p_Shipping_Method_Code => l_sch_line_tbl(I).Shipping_Method_Code,
p_shipment_priority_code => l_sch_line_tbl(I).shipment_priority_code,
X_Return_Status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
UPDATE OE_ORDER_LINES_ALL
SET SCHEDULE_SHIP_DATE = Null,
SCHEDULE_ARRIVAL_DATE = Null,
SHIP_FROM_ORG_ID = decode(re_source_flag,'N',ship_from_org_id,null),
SHIP_SET_ID = Null,
ARRIVAL_SET_ID = Null,
override_atp_date_code = Null
--Bug 5654321
--Modified the record from l_line_tbl to l_sch_line_tbl
--WHERE line_id = l_line_tbl(I).line_id;
oe_schedule_util.Log_Delete_Set_Request
(p_header_id => l_sch_line_tbl(I).header_id,
p_line_id => l_sch_line_tbl(I).line_id,
p_set_id => nvl(l_sch_line_tbl(I).ship_set_id,l_sch_line_tbl(I).arrival_set_id),
x_return_status => l_return_status);
oe_debug_pub.add('AFTER LOGGING DELETE SETS DELAYED REQUEST ' || L_RETURN_STATUS,1) ;