[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Update_Blanket_Qty
(p_line_rec IN OE_Order_PUB.Line_Rec_Type
,p_fulfilled_quantity IN NUMBER DEFAULT NULL
)
IS
l_fulfilled_quantity NUMBER := p_fulfilled_quantity;
SELECT transactional_curr_code, conversion_type_code
INTO l_blanket_currency, l_conversion_type
FROM OE_BLANKET_HEADERS
WHERE ORDER_NUMBER = p_line_rec.blanket_number
AND SALES_DOCUMENT_TYPE_CODE = 'B';
SELECT L.Order_Quantity_UOM
INTO l_blanket_uom
FROM OE_BLANKET_LINES L,OE_BLANKET_LINES_EXT BL
WHERE BL.ORDER_NUMBER = p_line_rec.blanket_number
AND BL.LINE_NUMBER = p_line_rec.blanket_line_number
AND L.LINE_ID = BL.LINE_ID
AND L.SALES_DOCUMENT_TYPE_CODE = 'B';
UPDATE oe_blanket_lines l
SET l.lock_control = l.lock_control + 1
WHERE L.Line_Id IN (SELECT Line_Id FROM oe_blanket_lines_ext bl
WHERE bl.order_number = p_line_rec.blanket_number
AND bl.line_number = p_line_rec.blanket_line_number)
AND l.sales_document_type_code = 'B';
UPDATE oe_blanket_lines_ext ble
SET ble.fulfilled_amount = nvl(ble.fulfilled_amount,0) +
l_amount
WHERE ble.order_number = p_line_rec.blanket_number
AND ble.line_number = p_line_rec.blanket_line_number;
UPDATE oe_blanket_lines_ext ble
SET ble.fulfilled_quantity = nvl(ble.fulfilled_quantity,0) +
l_fulfilled_quantity
,ble.fulfilled_amount = nvl(ble.fulfilled_amount,0) +
l_amount
WHERE ble.order_number = p_line_rec.blanket_number
AND ble.line_number = p_line_rec.blanket_line_number;
UPDATE oe_blanket_headers_ext bhe
SET bhe.fulfilled_amount = nvl(bhe.fulfilled_amount,0) +
l_amount
WHERE bhe.order_number = p_line_rec.blanket_number;
UPDATE oe_blanket_headers bh
SET bh.lock_control = bh.lock_control + 1
WHERE bh.order_number = p_line_rec.blanket_number
AND bh.sales_document_type_code = 'B';
UPDATE oe_blanket_lines_ext ble
SET ble.returned_amount = nvl(ble.returned_amount,0) +
l_amount
WHERE ble.order_number = p_line_rec.blanket_number
AND ble.line_number = p_line_rec.blanket_line_number;
UPDATE oe_blanket_lines_ext ble
SET ble.returned_quantity = nvl(ble.returned_quantity,0) +
l_fulfilled_quantity
,ble.returned_amount = nvl(ble.returned_amount,0) +
l_amount
WHERE ble.order_number = p_line_rec.blanket_number
AND ble.line_number = p_line_rec.blanket_line_number;
UPDATE oe_blanket_lines bl
SET bl.lock_control = bl.lock_control + 1
WHERE bl.line_Id IN (SELECT Line_Id FROM oe_blanket_lines_ext bl
WHERE bl.order_number = p_line_rec.blanket_number
AND bl.line_number = p_line_rec.blanket_line_number)
AND bl.sales_document_type_code = 'B';
UPDATE oe_blanket_headers_ext bhe
SET bhe.returned_amount = nvl(bhe.returned_amount,0) +
l_amount
WHERE bhe.order_number = p_line_rec.blanket_number;
UPDATE oe_blanket_headers bh
SET bh.lock_control = bh.lock_control + 1
WHERE bh.order_number = p_line_rec.blanket_number
AND bh.sales_document_type_code = 'B';
oe_debug_pub.add('Update_Blanket_Qty: Others Error', 1);
, 'Update_Blanket_Qty'
);
END Update_Blanket_Qty;
Select set_id
Into l_set_id
From oe_line_sets
Where line_id = p_line_id;
SELECT LINE_ID,
HEADER_ID,
ORDERED_QUANTITY,
SHIPPED_QUANTITY,
FULFILLED_FLAG,
BLANKET_NUMBER,
BLANKET_LINE_NUMBER,
ORDER_QUANTITY_UOM,
ITEM_TYPE_CODE,
LINE_CATEGORY_CODE,
UNIT_SELLING_PRICE,
INVENTORY_ITEM_ID,
ORDER_FIRMED_DATE,
ACTUAL_SHIPMENT_DATE
FROM OE_ORDER_LINES_ALL
WHERE SERVICE_REFERENCE_LINE_ID = p_line_id
AND SERVICE_REFERENCE_TYPE_CODE = 'ORDER' -- added in 115.46
AND HEADER_ID = l_header_id -- 1717444
AND TOP_MODEL_LINE_ID IS NULL; --3449588
SELECT header_id
INTO l_header_id
FROM oe_order_lines
WHERE line_id = p_line_id;
SELECT fulfilled_quantity
INTO l_parent_line_fulfilled_qty
FROM oe_order_lines
WHERE line_id = p_line_id;
l_fulfill_tbl(l_fulfill_index).operation := OE_GLOBALS.G_OPR_UPDATE;
/* Update the fulfilled flag and quantity for the service lines */
IF l_fulfill_index <> 0 THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( '..516..CALLING FULFILL LINE TABLE : ' , 3 ) ;
OE_Order_WF_Util.Update_Flow_Status_Code
(p_line_id => l_service_tbl(l_service_index).line_id,
p_flow_status_code => 'FULFILLED',
x_return_status => l_return_status
);
oe_debug_pub.add('after update of flow status code - error '||sqlerrm,1);
oe_debug_pub.add('after update of flow status code - exc error '||sqlerrm,1);
PROCEDURE Update_Service_Dates /* 2048753 */
(
p_line_rec IN OUT NOCOPY OE_Order_Pub.Line_Rec_Type
)
IS
l_return_status VARCHAR2(1);
oe_debug_pub.add( 'ENTERING UPDATE_SERVICE_DATES: ' || TO_CHAR ( P_LINE_REC.LINE_ID ) || ' '|| TO_CHAR ( P_LINE_REC.SERVICE_START_DATE , 'YYYY/MM/DD' ) ||' , '|| TO_CHAR ( P_LINE_REC.SERVICE_END_DATE , 'YYYY/MM/DD' ) , 5 ) ;
oe_debug_pub.add( 'EXITING UPDATE_SERVICE_DATES:' || TO_CHAR ( P_LINE_REC.SERVICE_START_DATE , 'YYYY/MM/DD' ) ||' , '|| TO_CHAR ( P_LINE_REC.SERVICE_END_DATE , 'YYYY/MM/DD' ) , 5 ) ;
END Update_Service_Dates;
This procedure is to update the fulfilled flag, fulfilled quantity and
fulfillment date for a line or a table of line records by calling
Process_Order API
*/
PROCEDURE Fulfill_Line
(
p_line_rec IN OE_Order_Pub.Line_Rec_Type DEFAULT OE_Order_Pub.G_MISS_LINE_REC
,p_line_tbl IN OE_Order_Pub.Line_Tbl_Type DEFAULT OE_Order_Pub.G_MISS_LINE_TBL
,p_mode IN VARCHAR2
,p_fulfillment_type IN VARCHAR2
,p_fulfillment_activity IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
,x_return_status OUT NOCOPY VARCHAR2
)
IS
l_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
select service_start_date,
service_end_date,
service_period,
service_duration,
service_coterminate_flag,
item_type_code -- this one added for 2417601
from oe_order_lines
where line_id = l_line_rec.line_id;
select ordered_date into l_ordered_date
from oe_order_headers_all
where header_id = p_line_rec.header_id;
l_update_line_tbl(1) := OE_Order_PUB.G_MISS_LINE_REC;
l_update_line_tbl(1).line_id := p_line_rec.line_id;
l_update_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
l_update_line_tbl(1).fulfilled_flag := 'Y';
l_update_line_tbl(1).fulfillment_date := SYSDATE;
SELECT type INTO l_accounting_rule_type
FROM ra_rules
WHERE rule_id = p_line_rec.accounting_rule_id;
Update_Service_Dates(l_line_rec);
oe_debug_pub.add('After calling update_service_lines() ',5) ;
l_line_tbl(1).last_update_date := SYSDATE;
l_line_tbl(1).last_updated_by := l_user; -- 3169637
l_line_tbl(1).last_update_login := FND_GLOBAL.LOGIN_ID;
OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists =>False,
p_header_id=>p_line_rec.header_id,
p_line_rec =>l_line_tbl(1),
p_line_id => p_line_rec.line_id,
x_index => l_notify_index,
x_return_status => l_return_status);
oe_debug_pub.add('Update_global return status from oe_line_fullfill.fullfill_line is: '|| l_return_status ) ;
OE_ORDER_UTIL.g_line_tbl(l_notify_index).last_update_date := SYSDATE;
OE_ORDER_UTIL.g_line_tbl(l_notify_index).last_updated_by := l_user; -- 3169637
OE_ORDER_UTIL.g_line_tbl(l_notify_index).last_update_login :=FND_GLOBAL.LOGIN_ID;
UPDATE OE_ORDER_LINES_ALL
SET FULFILLED_FLAG = 'Y',
FULFILLED_QUANTITY = l_fulfilled_quantity,
FULFILLED_QUANTITY2 = l_fulfilled_quantity2, -- 5126873
FULFILLMENT_DATE = SYSDATE,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_user, -- 3169637
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
/* next two lines: 2048753 */
SERVICE_START_DATE = l_line_rec.service_start_date,
SERVICE_END_DATE = l_line_rec.service_end_date,
ACTUAL_FULFILLMENT_DATE = l_actual_fulfillment_date,
LOCK_CONTROL = LOCK_CONTROL+1
WHERE LINE_ID = p_line_rec.line_id;
select order_number
into l_order_number
from oe_order_headers_all
where header_id = p_line_rec.header_id;
Update_Blanket_Qty(p_line_rec,l_fulfilled_quantity);
oe_debug_pub.add('Number of lines to update: '||p_line_tbl.count,3) ;
oe_debug_pub.add('Exiting fulfill_line() - no lines to update',3) ;
select ordered_date into l_ordered_date
from oe_order_headers_all
where header_id = p_line_tbl(l).header_id;
l_line_tbl(l_index).last_update_date := SYSDATE;
l_line_tbl(l_index).last_updated_by := l_user; -- 3169637
l_line_tbl(l_index).last_update_login := FND_GLOBAL.LOGIN_ID;
Update_Service_Dates(l_line_rec);
oe_debug_pub.add( 'AFTER CALLING UPDATE_SERVICE_DATES - ASO INSTALLED' , 5 ) ;
OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists =>False,
p_header_id=>l_line_tbl(l_index).header_id,
p_line_rec=>l_line_tbl(l_index),
p_line_id => p_line_tbl(l_index).line_id,
x_index => l_notify_index,
x_return_status => l_return_status);
oe_debug_pub.add('Update_global return status from fulfill_line with line table is: '||l_return_status);
OE_ORDER_UTIL.g_line_tbl(l_notify_index).last_update_date := SYSDATE;
OE_ORDER_UTIL.g_line_tbl(l_notify_index).last_updated_by := l_user; -- 3169637
OE_ORDER_UTIL.g_line_tbl(l_notify_index).last_update_login :=FND_GLOBAL.LOGIN_ID;
Update_Service_Dates(l_line_rec);
oe_debug_pub.add('After calling update_service_dates - aso not installed',5) ;
/* 2417601 uses a different update statements for service line */
UPDATE OE_ORDER_LINES_ALL
SET FULFILLED_FLAG = p_line_tbl(l_index).fulfilled_flag,
FULFILLED_QUANTITY = p_line_tbl(l_index).fulfilled_quantity,
FULFILLED_QUANTITY2 = p_line_tbl(l_index).fulfilled_quantity2, -- 5126873
FULFILLMENT_DATE = p_line_tbl(l_index).fulfillment_date,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_user, -- 3169637
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
SERVICE_START_DATE = l_line_rec.service_start_date,
SERVICE_END_DATE = l_line_rec.service_end_date,
ACTUAL_FULFILLMENT_DATE = l_actual_fulfillment_date,
LOCK_CONTROL = LOCK_CONTROL+1
WHERE LINE_ID = p_line_tbl(l_index).line_id;
select order_number
into l_order_number
from oe_order_headers_all
where header_id = p_line_tbl(l_index).header_id;
UPDATE OE_ORDER_LINES_ALL
SET FULFILLED_FLAG = p_line_tbl(l_index).fulfilled_flag,
FULFILLED_QUANTITY = p_line_tbl(l_index).fulfilled_quantity,
FULFILLED_QUANTITY2 = p_line_tbl(l_index).fulfilled_quantity2, -- 5126873
FULFILLMENT_DATE = p_line_tbl(l_index).fulfillment_date,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_user, -- 3169637
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
ACTUAL_FULFILLMENT_DATE = l_actual_fulfillment_date,
LOCK_CONTROL = LOCK_CONTROL+1
WHERE LINE_ID = p_line_tbl(l_index).line_id;
Update_Blanket_Qty(p_line_tbl(l_index)
,p_line_tbl(l_index).fulfilled_quantity);
oe_debug_pub.ADD('Calling OE_Order_PVT.Process_Order to update the fulfilled quantity and fulfilled flag',2);
p_line_tbl => l_update_line_tbl,
p_control_rec => l_control_rec,
x_return_status => l_return_status
);
SELECT wias.ACTIVITY_STATUS, wias.ACTIVITY_RESULT_CODE, wias.PROCESS_ACTIVITY
INTO x_activity_status_code, x_activity_result, x_activity_id
FROM WF_ITEM_ACTIVITY_STATUSES wias, WF_PROCESS_ACTIVITIES wpa
WHERE wias.ITEM_KEY = p_item_key
AND wias.ITEM_TYPE = p_item_type
AND wpa.ACTIVITY_NAME = p_activity_name
AND wias.PROCESS_ACTIVITY = wpa.INSTANCE_ID;
SELECT nvl(upgraded_flag,'N')
INTO l_upgraded_flag
FROM oe_order_lines
WHERE line_id = to_number(p_item_key);
SELECT OLS.LINE_ID, OLS.SET_ID
FROM OE_LINE_SETS OLS, OE_SETS OST
WHERE OLS.LINE_ID = p_line_id
AND OLS.SET_ID = OST.SET_ID
AND OST.SET_TYPE = 'FULFILLMENT_SET';
SELECT LINE_ID, SET_ID
FROM OE_LINE_SETS
WHERE SET_ID = l_set_id;
select line_category_code into l_line_category_code
from oe_order_lines_all
where line_id = to_number(p_item_key);
SELECT nvl(upgraded_flag,'N') -- Bug 3589692
INTO l_upgraded_flag
FROM oe_order_lines
WHERE line_id = to_number(p_item_key);
SELECT LINE_ID,
FULFILLED_FLAG
FROM OE_ORDER_LINES
WHERE TOP_MODEL_LINE_ID = l_top_model_line_id
AND OPEN_FLAG = 'Y';
SELECT LINE_ID,
HEADER_ID,
FULFILLED_FLAG
FROM OE_ORDER_LINES
WHERE TOP_MODEL_LINE_ID = l_top_model_line_id
AND OPEN_FLAG = 'Y';
select ordered_date into l_ordered_date
from oe_order_headers_all
where header_id = l_line_tbl(1).header_id;
update oe_order_lines
set fulfilled_flag = l_line_tbl(1).fulfilled_flag,
fulfilled_quantity = l_line_tbl(1).fulfilled_quantity,
fulfillment_date = l_line_tbl(1).fulfillment_date,
actual_fulfillment_date = l_line_tbl(1).actual_fulfillment_date,
lock_control = lock_control+1
where line_id = l_line_tbl(1).line_id;
OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists =>False,
p_header_id=>l_line_tbl(1).header_id,
p_old_line_rec => l_old_line_tbl(1),
p_line_rec =>l_line_tbl(1),
p_line_id =>l_line_tbl(1).line_id ,
x_index => l_index,
x_return_status => l_return_status);
oe_debug_pub.add('update_global return status for line ID '||L_LINE_TBL(1).LINE_ID||'IS:'||l_return_status,1) ;
oe_debug_pub.add('update_global index in fulfill_pto_kit for line_id '||l_line_tbl(1).LINE_ID ||' is: '||l_index,1);
OE_ORDER_UTIL.g_line_tbl(l_index).last_update_date:= l_line_tbl(1).last_update_date;
OE_Order_WF_Util.Update_Flow_Status_Code
(p_line_id => l_top_lines.line_id,
p_flow_status_code => 'FULFILLED',
x_return_status => l_return_status
);
SELECT line_id, item_type_code, shippable_flag, fulfilled_flag
FROM OE_ORDER_LINES
WHERE top_model_line_id = p_top_model_line_id
AND open_flag = 'Y';
SELECT count(*)
INTO l_count
FROM oe_order_lines
WHERE top_model_line_id = p_line_id;
SELECT item_type_code,ato_line_id
INTO l_item_type_code,l_ato_line_id
FROM oe_order_lines
WHERE line_id = p_line_id;
SELECT item_type_code,ato_line_id
INTO l_item_type_code,l_ato_line_id
FROM oe_order_lines
WHERE line_id = p_line_id;
l_fulfill_tbl(l_fulfill_index).operation := OE_GLOBALS.G_OPR_UPDATE;
l_fulfill_tbl(l_fulfill_index).operation := OE_GLOBALS.G_OPR_UPDATE;
oe_debug_pub.add('update the flow status code on the lines ',5);
OE_Order_WF_Util.Update_Flow_Status_Code
(p_line_id => l_line_tbl(l_line_index).line_id,
p_flow_status_code => 'FULFILLED',
x_return_status => l_return_status
);
oe_debug_pub.add('after update of flow status '||l_return_status,5);
oe_debug_pub.add('update flow status for - '||l_line_id,5);
OE_Order_WF_Util.Update_Flow_Status_Code
(p_line_id => l_fulfill_service_tbl(l_fulfill_service_index).line_id,
p_flow_status_code => 'FULFILLED',
x_return_status => l_return_status
);
SELECT HEADER_ID,
BLANKET_NUMBER,
BLANKET_LINE_NUMBER,
ORDER_QUANTITY_UOM,
ITEM_TYPE_CODE,
LINE_CATEGORY_CODE,
UNIT_SELLING_PRICE,
INVENTORY_ITEM_ID,
-- changes for AFD
ACTUAL_SHIPMENT_DATE,
ORDER_FIRMED_DATE
FROM OE_ORDER_LINES_ALL
WHERE LINE_ID = l_cursor_line_id;
SELECT TOP_MODEL_LINE_ID
INTO l_top_model_line_id
FROM OE_ORDER_LINES
WHERE LINE_ID = p_line_id;
/* Prepare a table of lines which have not been fulfilled to update
the fulfillment related attributes and comlpete the fulfill line */
FOR l_line_set_index IN 1 .. l_process_tbl.count
LOOP
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'LINE ID/SET ID : '|| TO_CHAR ( L_PROCESS_TBL ( L_LINE_SET_INDEX ) .LINE_ID ) ||'/'||TO_CHAR ( L_PROCESS_TBL ( L_LINE_SET_INDEX ) .SET_ID ) , 3 ) ;
l_fulfill_tbl(l_fulfill_index).operation := OE_GLOBALS.G_OPR_UPDATE;
l_fulfill_tbl(l_fulfill_index).operation := OE_GLOBALS.G_OPR_UPDATE;
SELECT HEADER_ID
INTO l_header_id
FROM OE_ORDER_LINES
WHERE LINE_ID = l_line_tbl(l_line_tbl_index);
OE_Order_WF_Util.Update_Flow_Status_Code
(p_line_id => l_line_tbl(l_line_tbl_index),
p_flow_status_code => 'FULFILLED',
x_return_status => l_return_status
);
UPDATE OE_SETS
SET SET_STATUS = 'C',
UPDATE_DATE = SYSDATE
WHERE SET_ID = l_set_tbl(l_set_tbl_index);
has a fulfillment activity it updates the fulfilled quantity, flag and
fulfillment date for the line. If the line is part of a MODEL the
fulfillment processing for PTO/KIT will take place, if the line is part
of fulfillment set(s) fulfillment processing for a fulfillment set will
take place. If the line is part of a remnant MODEL the FULILL_LINE work
flow activity will be completed for the line. If the line is not part of
either MODEL or fulfillment set, the FULFILL_LINE work flow activity will
be set to COMPLETE. If the line does not have a fulfillment activity and
is not part of MODEL or fulfillment set, the fulfilled quantity will be
updated from the ordered quantity of the line and FULFILL_LINE activity
will be completed.
*/
PROCEDURE Process_Fulfillment
(
p_api_version_number IN NUMBER
, p_line_id IN NUMBER
, p_activity_id IN NUMBER
, x_result_out OUT NOCOPY VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY VARCHAR2
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_line_rec OE_Order_Pub.Line_Rec_Type;
g_set_tbl.delete;
SELECT HEADER_ID
INTO l_header_id
FROM OE_ORDER_HEADERS
WHERE HEADER_ID = l_line_rec.header_id
FOR UPDATE NOWAIT;
SELECT SET_ID
INTO l_set_id
FROM OE_SETS
WHERE SET_ID = lock_set_id
FOR UPDATE NOWAIT;
SELECT line_id, top_model_line_id
INTO l_line_id, l_top_model_line_id
FROM oe_order_lines
WHERE line_id = l_line_rec.top_model_line_id
FOR UPDATE NOWAIT;
SELECT top_model_line_id
INTO l_top_model_line_id
FROM oe_order_lines
WHERE line_id = l_line_rec.service_reference_line_id
FOR UPDATE NOWAIT;
SELECT line_id
INTO l_line_id
FROM oe_order_lines
WHERE line_id = l_top_model_line_id
FOR UPDATE NOWAIT;
SELECT line_id
INTO l_line_id
FROM oe_order_lines
WHERE line_id = l_line_rec.service_reference_line_id
FOR UPDATE NOWAIT;
SELECT line_id
INTO l_line_id
FROM oe_order_lines
WHERE line_id = l_line_rec.line_id
FOR UPDATE NOWAIT;
SELECT line_id
INTO l_line_id
FROM oe_order_lines
WHERE line_id = l_line_rec.line_id
FOR UPDATE NOWAIT;
SELECT fulfilled_flag
INTO l_fulfilled_flag
FROM oe_order_lines
WHERE Line_id = l_line_rec.line_id;
oe_debug_pub.add('update flow status code',5);
OE_Order_WF_Util.Update_Flow_Status_Code
(p_line_id => l_line_tbl(l_line_index).line_id,
p_flow_status_code => 'FULFILLED',
x_return_status => l_return_status
);
OE_Order_WF_Util.Update_Flow_Status_Code
(p_line_id => l_line_tbl(l_config_index).line_id,
p_flow_status_code => 'FULFILLED',
x_return_status => l_return_status
);
OE_Order_WF_Util.Update_Flow_Status_Code
(p_line_id => l_line_rec.ato_line_id,
p_flow_status_code => 'FULFILLED',
x_return_status => l_return_status
);
SELECT fulfilled_flag,
header_id -- 1717444
INTO l_fulfilled_flag,
l_ref_header_id -- 1717444
FROM oe_order_lines
WHERE line_id = l_line_rec.service_reference_line_id;
SELECT '1'
INTO l_dummy
FROM oe_order_lines_all
WHERE line_id = l_line_rec.line_id
FOR update nowait;
oe_debug_pub.add('Unable to lock to update status to'||l_flow_status_code,3);
OE_Order_WF_Util.Update_Flow_Status_Code
(p_line_id => l_line_rec.line_id,
p_flow_status_code => l_flow_status_code,
x_return_status => l_return_status
);
SELECT TOP_MODEL_LINE_ID,
MODEL_REMNANT_FLAG
INTO l_store_top,
l_store_rem
FROM OE_ORDER_LINES
WHERE LINE_ID = p_line_id;
select count(*)
into l_howmany
from oe_line_sets l,
oe_sets s
where l.line_id = p_line_id
and l.set_id = s.set_id
and s.set_id <> p_set_id
and s.set_type = 'FULFILLMENT_SET';
select count(*) -- added for 2525203
into l_howmany
from oe_line_sets l1,
oe_line_sets l2,
oe_sets s1,
oe_order_lines ol
where l1.line_id <> p_line_id
and l1.set_id = s1.set_id
and s1.set_type = 'FULFILLMENT_SET'
and s1.set_id <> l_set_id
and l2.line_id = l1.line_id
and l1.line_id = ol.line_id
and (ol.top_model_line_id is null or
ol.top_model_line_id = ol.line_id or
nvl(ol.model_remnant_flag, 'N') = 'Y')
and l2.set_id = l_set_id;
select count(*)
into l_common_sets
from oe_line_sets l1,
oe_line_sets l2,
oe_line_sets l3,
oe_sets s1,
oe_order_lines ol
where l1.line_id = p_line_id
and l1.set_id = s1.set_id
and s1.set_type = 'FULFILLMENT_SET'
and s1.set_id <> l_set_id
and l2.set_id = s1.set_id
and l2.line_id = ol.line_id
and (ol.top_model_line_id is null or
ol.top_model_line_id = ol.line_id or
nvl(ol.model_remnant_flag, 'N') = 'Y')
and l2.line_id <> l1.line_id
and l2.line_id = l3.line_id
and l3.set_id = l_set_id;
g_set_tbl.delete;
SELECT TOP_MODEL_LINE_ID,
MODEL_REMNANT_FLAG
INTO l_top_model_line_id,
l_model_remnant_flag
FROM OE_ORDER_LINES
WHERE LINE_ID = l_set_tbl(l_set_index).line_id;
l_fulfilled_lines.DELETE; -- 2525203
SELECT fulfilled_flag
INTO l_fulfilled_flag
FROM OE_ORDER_LINES
WHERE LINE_ID = l_ful_set_tbl(l_ful_set_index).line_id;
OE_Order_WF_Util.Update_Flow_Status_Code
(p_line_id => l_ful_set_tbl(l_ful_set_index).line_Id,
p_flow_status_code => 'FULFILLED',
x_return_status => l_return_status
);
UPDATE OE_SETS
SET SET_STATUS = 'C',
UPDATE_DATE = SYSDATE
WHERE SET_ID = l_close_tbl(l_close_tbl_index);
oe_debug_pub.add( 'CURRENT LINE IS GETTING DELETED OR CANCELLED , DONOT PROCESS ' , 3 ) ;
SELECT fulfilled_flag
INTO l_fulfilled_flag
FROM OE_ORDER_LINES
WHERE LINE_ID = p_line_id;
SELECT HEADER_ID
INTO l_header_id
FROM OE_ORDER_LINES
WHERE LINE_ID = p_line_id;
OE_Order_WF_Util.Update_Flow_Status_Code
(p_line_id => p_line_Id,
p_flow_status_code => 'FULFILLED',
x_return_status => l_return_status
);