[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT WDD.LOT_NUMBER,
-- WDD.SUBLOT_NUMBER, INVCONV
WDD.SHIPPED_QUANTITY
FROM WSH_DELIVERY_DETAILS WDD
WHERE WDD.SOURCE_LINE_ID = p_line_Id
AND WDD.RELEASED_STATUS ='C'
AND WDD.SOURCE_CODE='OE'
AND NVL ( WDD.OE_INTERFACED_FLAG , 'N' ) <> 'Y';
SELECT ordered_quantity,
order_quantity_uom,
inventory_item_id,
top_model_line_id,
ato_line_id,
item_type_code,
line_set_id,
ship_from_org_id,
ordered_quantity2
INTO l_ordered_quantity,
l_order_quantity_uom,
l_inventory_item_id,
l_top_model_line_id,
l_ato_line_id,
l_item_type_code,
l_line_set_id,
l_ship_from_org_id,
l_ordered_quantity2
FROM OE_ORDER_LINES
WHERE line_id = p_req_qty_tbl(J).line_id;
oe_debug_pub.add( 'OPM PROCESS SHIPPING UPDATE ' , 1 ) ;
l_split_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
SELECT OE_PRICE_ADJUSTMENTS_S.nextval
INTO l_price_adjustment_id
FROM DUAL;
l_Line_adj_rec.last_update_date := SYSDATE;
l_Line_adj_rec.last_updated_by := FND_GLOBAL.USER_ID;
l_Line_adj_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
oe_debug_pub.add( 'INSERTING THE ADJ RECORD '||TO_CHAR ( L_LINE_ADJ_REC.PRICE_ADJUSTMENT_ID ) , 2 ) ;
OE_LINE_ADJ_UTIL.INSERT_ROW(p_Line_Adj_rec => l_line_adj_rec);
-- Update Line records for required attributes..
FOR J IN 1..p_line_tbl.COUNT
LOOP
SELECT top_model_line_id
INTO l_top_model_line_id
FROM oe_order_lines
WHERE line_id = p_line_tbl(J).line_id;
SELECT top_model_line_id
INTO l_top_model_line_id
FROM oe_order_lines
WHERE line_id = l_top_model_line_id
FOR UPDATE NOWAIT;
SELECT shipping_quantity,
shipping_quantity_uom,
order_quantity_uom,
actual_shipment_date,
inventory_item_id,
ship_from_org_id,
ship_set_id,
top_model_line_id,
ato_line_id,
model_remnant_flag,
ordered_quantity,
ship_tolerance_below,
over_ship_reason_code,
item_type_code,
header_id,
calculate_price_flag,
ship_model_complete_flag
INTO l_shipping_quantity,
l_shipping_quantity_uom,
l_order_quantity_uom,
l_actual_shipment_date,
l_inventory_item_id,
l_ship_from_org_id,
l_ship_set_id,
l_top_model_line_id,
l_ato_line_id,
l_model_remnant_flag,
l_ordered_quantity,
l_ship_tolerance_below,
l_over_ship_reason_code,
l_item_type_code,
l_header_id,
l_calculate_price_flag,
l_smc_flag
FROM OE_ORDER_LINES
WHERE line_id = p_line_tbl(J).line_id
FOR UPDATE NOWAIT;
oe_debug_pub.add( 'DUAL ITEM - SHIPPING UPDATE ' , 1 ) ;
SELECT ic.lot_id
INTO l_lot_id
FROM ic_lots_mst ic
WHERE ic.lot_no = LOT.lot_number
AND ic.item_id = l_item_rec.opm_item_id
AND ic.sublot_no = LOT.sublot_number;
SELECT ic.lot_id
INTO l_lot_id
FROM ic_lots_mst ic
WHERE ic.lot_no = LOT.lot_number
AND ic.item_id = l_item_rec.opm_item_id
AND ic.sublot_no IS NULL;
oe_debug_pub.add( 'DUAL PROCESS SHIPPING UPDATE CONVERSION' || ' GIVES SHIPPED QUANTITY OF ' ||
L_TEMP_SHIPPED_QUANTITY , 1 ) ;
SELECT ato_line_id
INTO l_count
FROM oe_order_lines
WHERE line_id = l_top_model_line_id;
SELECT COUNT(*)
INTO l_shippable_lines
FROM OE_ORDER_LINES
WHERE top_model_line_id = l_top_model_line_id
AND NVL(CANCELLED_FLAG,'N')='N'
AND NVL(SHIPPABLE_FLAG,'N')='Y';
SELECT count(*)
INTO l_count
FROM oe_order_lines
WHERE top_model_line_id = l_top_model_line_id
AND cancelled_flag = 'N'
AND source_type_code = 'EXTERNAL';
SELECT count(*)
INTO l_count
FROM oe_order_lines oe1
WHERE top_model_line_id = l_top_model_line_id
AND ato_line_id = line_id
AND item_type_code = 'CLASS'
AND cancelled_flag = 'N'
AND not exists
(SELECT NULL
FROM oe_order_lines_all
WHERE top_model_line_id = l_top_model_line_id
AND ato_line_id = oe1.line_id
AND cancelled_flag = 'N'
AND item_type_code = 'CONFIG');
SELECT 1 INTO l_count
FROM DUAL
WHERE EXISTS (
SELECT NULL
FROM oe_order_lines
WHERE top_model_line_id = l_top_model_line_id
AND cancelled_flag = 'N'
AND schedule_ship_date is NULL);
UPDATE oe_order_lines
SET model_remnant_flag = 'Y'
WHERE top_model_line_id = l_top_model_line_id;
END IF; /* need to update global picture */
UPDATE OE_ORDER_LINES
SET shipping_quantity = p_line_tbl(J).shipping_quantity,
shipping_quantity2 = p_line_tbl(J).shipping_quantity2,
shipped_quantity2 = p_line_tbl(J).shipping_quantity2,
shipping_quantity_uom = p_line_tbl(J).shipping_quantity_uom,
shipping_quantity_uom2 = p_line_tbl(J).shipping_quantity_uom2, -- INVCONV
actual_shipment_date = p_line_tbl(J).actual_shipment_date,
ship_tolerance_below = l_ship_tolerance_below_upd,
over_ship_reason_code = l_over_ship_reason_code_upd,
shipped_quantity = l_shipped_quantity
WHERE line_id = p_line_tbl(J).line_id;
END IF; /* need to update global picture */
UPDATE OE_ORDER_LINES
SET shipping_quantity = p_line_tbl(J).shipping_quantity,
shipping_quantity2 = p_line_tbl(J).shipping_quantity2,
shipped_quantity2 = p_line_tbl(J).shipping_quantity2,
shipping_quantity_uom = p_line_tbl(J).shipping_quantity_uom,
shipping_quantity_uom2 = p_line_tbl(J).shipping_quantity_uom2, -- INVCONV
actual_shipment_date = p_line_tbl(J).actual_shipment_date,
ship_tolerance_below = l_ship_tolerance_below_upd,
over_ship_reason_code = l_over_ship_reason_code_upd,
shipped_quantity = l_shipped_quantity,
calculate_price_flag = l_calculate_price_flag
WHERE line_id = p_line_tbl(J).line_id;
OE_ORDER_UTIL.Update_Global_Picture
(p_Upd_New_Rec_If_Exists =>FALSE
, p_header_id => l_line_tbl(l_loop_index).header_id
, p_line_rec => l_line_tbl(l_loop_index)
, p_old_line_rec => l_old_line_tbl(l_loop_index)
, p_line_id => l_line_tbl(l_loop_index).line_id
, x_index => l_notify_index
, x_return_status => l_return_status);
oe_debug_pub.add('UPDATE_GLOBAL RET_STATUS FOR LINE_ID '||L_LINE_TBL ( L_LOOP_INDEX ) .LINE_ID ||' IS: ' || L_RETURN_STATUS , 1
) ;
oe_debug_pub.add('UPDATE_GLOBAL INDEX FOR LINE_ID '||L_LINE_TBL ( L_LOOP_INDEX ) .LINE_ID ||' IS: ' || L_NOTIFY_INDEX , 1 ) ;
OE_ORDER_UTIL.g_line_tbl(l_notify_index).last_update_date := l_line_tbl(l_loop_index).last_update_date;
END LOOP; -- over each line to update global picture
,p_delete => FND_API.G_TRUE
,x_return_status => l_return_status
);
,p_delete => FND_API.G_TRUE
,x_return_status => l_return_status
);
,p_delete => FND_API.G_TRUE
,x_return_status => l_return_status
);
,p_delete => FND_API.G_TRUE
,x_return_status => l_return_status
);
,p_delete => FND_API.G_TRUE
,x_return_status => l_return_status
);
,p_delete => FND_API.G_TRUE
,x_return_status => l_return_status
);
,p_delete => FND_API.G_TRUE
,x_return_status => l_return_status
);
,p_delete => FND_API.G_TRUE
,x_return_status => l_return_status
);
,p_delete => FND_API.G_TRUE
,x_return_status => l_return_status
);
,p_delete => FND_API.G_TRUE
,x_return_status => l_return_status
);
,p_delete => FND_API.G_TRUE
,x_return_status => l_return_status
);
oe_debug_pub.add( 'DUAL PROCESS SHIPPING UPDATE ', 5); -- INVCONV
SELECT count(*)
INTO l_wdd_count
FROM wsh_delivery_details
WHERE source_line_id = p_requested_line_rec.line_id(I)
AND released_status <> 'D'
AND source_code = 'OE'
AND oe_interfaced_flag = 'N';
l_split_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
oe_debug_pub.add(1 ||' opr update qty '
|| l_split_line_tbl(1).ordered_quantity, 1);
oe_debug_pub.add(1 ||' opr update qty2 '
|| l_split_line_tbl(1).ordered_quantity2, 1);
select order_quantity_uom2??
-------------------------------------------------------------*/
PROCEDURE Split_Line
( p_ship_line_rec IN OUT NOCOPY Ship_Line_Rec_Type
-- ,p_opm_check IN VARCHAR2 := 'Y' -- INVCONV - NOT NEEDed NOW
,p_index IN NUMBER
,p_split_model IN VARCHAR2 := 'N')
IS
l_line_tbl OE_Order_Pub.Line_Tbl_Type;
l_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
SELECT item_type_code
INTO l_line_tbl(1).item_type_code
FROM oe_order_lines_all
WHERE line_id = p_ship_line_rec.ato_line_id(p_index);
SELECT line_id, ordered_quantity
,ordered_quantity2 -- bug 12731457
,header_id, actual_shipment_date, order_firmed_date
,blanket_number,blanket_line_number,blanket_version_number
FROM oe_order_lines
WHERE top_model_line_id = p_top_model_line_id
AND nvl(ato_line_id,-99) = -99 /*added for bug 6640292*/
AND shippable_flag = 'N'
AND model_remnant_flag = 'Y';
l_fulfill_tbl(l_fulfill_index).operation := OE_GLOBALS.G_OPR_UPDATE;
OE_Order_WF_Util.Update_Flow_Status_Code
(p_header_id => l_fulfill_tbl(l_fulfill_index).header_id,
p_line_id => l_fulfill_tbl(l_fulfill_index).line_id,
p_flow_status_code => 'FULFILLED',
x_return_status => l_return_status);
UPDATE oe_order_lines
SET shipped_quantity = (ordered_quantity *
p_ship_line_rec.shipped_quantity(p_index)/
p_ship_line_rec.ordered_quantity(p_index))
,actual_shipment_date
= p_ship_line_rec.actual_shipment_date(p_index)
,lock_control = lock_control + 1
--last_updated_by**
WHERE ato_line_id = p_ship_line_rec.ato_line_id(p_index)
AND header_id = p_ship_line_rec.header_id(p_index)
AND shippable_flag = 'N';
SELECT count(*)
INTO l_wdd_count
FROM wsh_delivery_details
WHERE source_line_id = p_ship_line_rec.line_id(p_index)
AND released_status <> 'D'
AND source_code = 'OE'
AND oe_interfaced_flag = 'N';
oe_debug_pub.add('calling Update_Flow_Status_Code', 3);
OE_Order_WF_Util.Update_Flow_Status_Code
(p_header_id => p_ship_line_rec.header_id(p_index),
p_line_id => p_ship_line_rec.line_id(p_index),
p_flow_status_code => 'SHIPPED',
x_return_status => l_return_status );
SELECT line_id, ordered_quantity, shipped_quantity
FROM oe_order_lines
WHERE shippable_flag = 'Y'
AND shipped_quantity is NOT NULL
AND top_model_line_id = p_top_model_line_id;
SELECT count(*)
INTO l_count1
FROM oe_order_lines
WHERE top_model_line_id = p_top_model_line_id
AND cancelled_flag = 'N'
AND shippable_flag = 'Y';
SELECT count(*)
INTO l_count
FROM oe_order_lines
WHERE top_model_line_id = p_top_model_line_id
AND cancelled_flag = 'N'
AND source_type_code = 'EXTERNAL';
SELECT count(*)
INTO l_count
FROM oe_order_lines oe1
WHERE top_model_line_id = p_top_model_line_id
AND ato_line_id = line_id
AND item_type_code = 'CLASS'
AND cancelled_flag = 'N'
AND not exists
(SELECT NULL
FROM oe_order_lines_all
WHERE top_model_line_id = p_top_model_line_id
AND ato_line_id = oe1.line_id
AND cancelled_flag = 'N'
AND item_type_code = 'CONFIG');
SELECT 1 INTO l_count
FROM DUAL
WHERE EXISTS (
SELECT NULL
FROM oe_order_lines
WHERE top_model_line_id = p_top_model_line_id
AND cancelled_flag = 'N'
AND schedule_ship_date is NULL);
UPDATE oe_order_lines
SET model_remnant_flag = 'Y'
,lock_control = lock_control + 1
WHERE top_model_line_id = p_top_model_line_id
AND cancelled_flag = 'N';
SELECT count(*)
INTO l_count
FROM oe_order_lines
WHERE top_model_line_id = p_top_model_line_id
AND shippable_flag = 'Y'
AND NVL(CANCELLED_FLAG,'N')='N' --9822866
AND shipped_quantity is NULL;
oe_debug_pub.add('1 select '|| l_count , 3);
SELECT count(*)
INTO l_count
FROM oe_order_lines oe1
WHERE top_model_line_id = p_top_model_line_id
AND shippable_flag = 'Y'
AND NVL(OE1.CANCELLED_FLAG,'N')='N' --9822866
AND ordered_quantity/shipped_quantity = ALL
(SELECT ordered_quantity/shipped_quantity
FROM oe_order_lines_all oe2
WHERE oe2.top_model_line_id = oe1.top_model_line_id
AND oe2.line_id <> oe1.line_id
AND oe2.shippable_flag = 'Y'
AND NVL(OE2.CANCELLED_FLAG,'N')='N' --9822866
);
oe_debug_pub.add('2 select '|| l_count || l_count1 , 3);
SELECT shipped_quantity/ordered_quantity
INTO l_shipped_ratio
FROM oe_order_lines_all
WHERE top_model_line_id = p_top_model_line_id
AND shippable_flag = 'Y'
AND ROWNUM =1;
SELECT ordered_quantity
into l_model_ordered_qty
FROM oe_order_lines_all
WHERE line_id = p_top_model_line_id;
SELECT shipped_quantity,ordered_quantity
INTO l_line_model_shipped_qty,l_line_model_ordered_qty
FROM oe_order_lines_all
WHERE top_model_line_id = p_top_model_line_id
AND shippable_flag = 'Y'
AND ROWNUM =1;
SELECT ordered_quantity
into l_model_ordered_qty
FROM oe_order_lines_all
WHERE line_id = p_top_model_line_id;
UPDATE oe_order_lines
SET shipped_quantity = (ordered_quantity *
p_ship_line_rec.shipped_quantity(p_index)/
p_ship_line_rec.ordered_quantity(p_index))
,actual_shipment_date =
p_ship_line_rec.actual_shipment_date(p_index)
,lock_control = lock_control + 1
--last_updated_by**
WHERE top_model_line_id = p_top_model_line_id
AND open_flag = 'Y'
AND nvl(cancelled_flag, 'N') = 'N'
AND source_type_code = 'INTERNAL'
AND shippable_flag = 'N';
oe_debug_pub.add('nonshippable lines updated '|| sql%rowcount, 3);
SELECT line_id, shipped_quantity, ordered_quantity, ordered_quantity_uom2, -- INVCONV 4199186
ato_line_id,top_model_line_id, item_type_code, ship_from_org_id -- Bug 10338240
INTO p_ship_line_rec.line_id(l_index),
p_ship_line_rec.shipped_quantity(l_index),
p_ship_line_rec.ordered_quantity(l_index),
p_ship_line_rec.order_quantity_uom2(l_index), -- INVCONV 4199186
p_ship_line_rec.ato_line_id(l_index),
p_ship_line_rec.top_model_line_id(l_index),
p_ship_line_rec.item_type_code(l_index),
p_ship_line_rec.ship_from_org_id(l_index) -- Bug 10338240
FROM oe_order_lines
WHERE line_id = p_top_model_line_id;
l_split_line_tbl(l_count).operation := OE_GLOBALS.G_OPR_UPDATE;
SELECT line_id, shipped_quantity, ordered_quantity,
header_id,ato_line_id,item_type_code
INTO p_ship_line_rec.line_id(l_index),
p_ship_line_rec.shipped_quantity(l_index),
p_ship_line_rec.ordered_quantity(l_index),
p_ship_line_rec.header_id(l_index),
p_ship_line_rec.ato_line_id(l_index),
p_ship_line_rec.item_type_code(l_index)
FROM oe_order_lines
WHERE line_id = l_split_line_tbl(I).line_id;
SELECT line_id, line_set_id, ordered_quantity, ordered_quantity2,
order_quantity_uom, ordered_quantity_uom2, inventory_item_id,
header_id
FROM oe_order_lines oe
WHERE line_id in
(SELECT line_id
FROM oe_order_lines_all
WHERE line_set_id = p_ship_line_rec.line_set_id(p_index)
AND line_id <> p_ship_line_rec.line_id(p_index))
AND open_flag = 'Y'
AND shipped_quantity is NULL
AND line_id in
(SELECT source_line_id
FROM wsh_delivery_details
WHERE source_header_id = oe.header_id);
SELECT count(*)
INTO l_count
FROM wsh_delivery_details
WHERE source_line_id = line_rec.line_id
AND released_status <> 'D';
oe_debug_pub.add('Before quantities db update', 3);
UPDATE OE_ORDER_LINES
SET shipping_quantity = l_line_set_rec.shipping_quantity(I),
shipped_quantity = l_line_set_rec.shipped_quantity(I),
shipping_quantity2 = l_line_set_rec.shipping_quantity2(I),
shipped_quantity2 = l_line_set_rec.shipped_quantity2(I),
shipping_quantity_uom = l_line_set_rec.shipping_quantity_uom(I),
shipping_quantity_uom2 = l_line_set_rec.shipping_quantity_uom2(I), -- INVCONV
actual_shipment_date = l_line_set_rec.actual_shipment_date(I),
lock_control = lock_control + 1
WHERE line_id = l_line_set_rec.line_id(I);
END IF; -- if wdd has lines staged/shipped or not deleted
SELECT line_id, item_type_code,
top_model_line_id,
nvl(model_remnant_flag, 'N') model_remnant_flag,
fulfilled_quantity,
open_flag,
invoiced_quantity
FROM oe_order_lines
WHERE ship_set_id = p_ship_set_id
AND shipped_quantity is NULL;
Oe_Debug_pub.Add('check if set id needs to be updated '|| I, 3);
oe_debug_pub.add('DIRECT UPDATE LINE ID: '||line_rec.line_id,3);
UPDATE oe_order_lines
SET ship_set_id = NULL
WHERE line_id=line_rec.line_id;
SELECT line_id
INTO l_line_id
FROM oe_order_lines
WHERE line_id = line_rec.line_id
FOR UPDATE NOWAIT;
l_set_tbl(J).operation := OE_GLOBALS.G_OPR_UPDATE;
oe_debug_pub.add('UPDATE SHIP SET ID '|| J,3);
3358774 -- Update Ordered Quantity if there is a call to
Handle_Requested_Quantity
3613716 -- extend fields ordered_quantuty,ship_tolerance_below
and ship_tolerance_above
3670530 -- If the order line belongs to a set, lock the
corresponding set from oe_sets table. handle lock
exception in the exception handler. also, move the
code to lock order lines to a separate block
------------------------------------------------------------*/
PROCEDURE Handle_NonBulk_Mode
( p_ship_line_rec IN OUT NOCOPY Ship_Line_Rec_Type
,p_requested_line_rec IN OUT NOCOPY Ship_Line_Rec_Type
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
SELECT ordered_quantity,
ship_tolerance_below,
ship_tolerance_above
INTO p_ship_line_rec.ordered_quantity(I),
p_ship_line_rec.ship_tolerance_below(I),
p_ship_line_rec.ship_tolerance_above(I)
FROM oe_order_lines_all
WHERE line_id = p_ship_line_rec.line_id(I);
SELECT top_model_line_id, shippable_flag
INTO p_ship_line_rec.top_model_line_id(I), p_ship_line_rec.shippable_flag(I)
FROM OE_ORDER_LINES
WHERE line_id = p_ship_line_rec.line_id(I)
FOR UPDATE NOWAIT;
SELECT top_model_line_id
INTO l_rem_top_model_line_id
FROM oe_order_lines
WHERE line_id = p_ship_line_rec.top_model_line_id(I)
FOR UPDATE NOWAIT;
SELECT Set_id
INTO l_ship_set_id
FROM OE_SETS
WHERE set_id = p_ship_line_rec.ship_set_id(I)
FOR UPDATE NOWAIT ;
SELECT Set_id
INTO l_arrival_set_id
FROM OE_SETS
WHERE set_id = p_ship_line_rec.arrival_set_id(I)
FOR UPDATE NOWAIT ;
UPDATE OE_ORDER_LINES
SET shipping_quantity = p_ship_line_rec.shipping_quantity(I),
shipping_quantity2 = p_ship_line_rec.shipping_quantity2(I),
shipped_quantity2 = p_ship_line_rec.shipping_quantity2(I),
shipped_quantity = p_ship_line_rec.shipped_quantity(I),
shipping_quantity_uom = p_ship_line_rec.shipping_quantity_uom(I),
shipping_quantity_uom2 = p_ship_line_rec.shipping_quantity_uom2(I), -- INVCONV
actual_shipment_date = p_ship_line_rec.actual_shipment_date(I),
over_ship_reason_code = p_ship_line_rec.over_ship_reason_code(I),
calculate_price_flag = p_ship_line_rec.calculate_price_flag(I),
lock_control = lock_control + 1
WHERE line_id = p_ship_line_rec.line_id(I);
Oe_Debug_pub.Add('shipped qty updated to: '
|| p_ship_line_rec.shipped_quantity(I) || ' shp qty 2 is :'
|| p_ship_line_rec.shipping_quantity2(I), 3);
UPDATE OE_SETS
SET SET_STATUS = 'C'
WHERE SET_ID = p_ship_line_rec.arrival_set_id(I)
AND SET_STATUS <> 'C';
UPDATE OE_SETS
SET SET_STATUS = 'C'
WHERE SET_ID = p_ship_line_rec.ship_set_id(I)
AND SET_STATUS <> 'C';
SELECT Count(*)
INTO l_cnt_config
FROM oe_order_lines_all
WHERE ato_line_id = p_ship_line_rec.ato_line_id(I)
AND item_type_code = 'CONFIG' ;
,p_operation => OE_GLOBALS.G_OPR_UPDATE);
l_change_line_tbl.delete;
l_order_has_lines.delete;
* locking -- update table will lock rows, not doing for now
* before notification framework.
*
* change record:
* bug bugs 3544045, 3544209: a mix of internal and external
* lines with complete shipped quantity should make the
* model remnant even in case of BULK model call from WSH.
*
* Bug 3679500: Changed the return_status variable to be l_return_status
* in the call to Price_Line and Process_Requests_And_Notify. Previously,
* x_return_status was being used.
* ------------------------------------------------------------*/
PROCEDURE Handle_Bulk_Mode_Per_Order
( p_ship_line_rec IN OUT NOCOPY Ship_Line_Rec_Type
,p_line_adj_rec IN Ship_Adj_Rec_Type
,p_start_index IN NUMBER
,p_end_index IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
UPDATE OE_ORDER_LINES_ALL
SET shipping_quantity = p_ship_line_rec.shipping_quantity(I),
shipping_quantity2 = p_ship_line_rec.shipping_quantity2(I),
shipped_quantity2 = p_ship_line_rec.shipping_quantity2(I),
shipped_quantity = p_ship_line_rec.ordered_quantity(I),
-- shipped_quantity2 = p_ship_line_rec.ordered_quantity2(I), -- INVCONV
shipping_quantity_uom = p_ship_line_rec.shipping_quantity_uom(I),
shipping_quantity_uom2 = p_ship_line_rec.shipping_quantity_uom2(I),
actual_shipment_date = p_ship_line_rec.actual_shipment_date(I),
flow_status_code = 'SHIPPED',
last_update_date = sysdate,--6901322
lock_control = lock_control + 1
WHERE line_id = p_ship_line_rec.line_id(I);
oe_debug_pub.ADD('updated lines with shipped qty '|| sql%rowcount,1);
UPDATE OE_ORDER_LINES_ALL oe1
SET model_remnant_flag = 'Y'
WHERE top_model_line_id is not NULL
AND top_model_line_id = p_ship_line_rec.top_model_line_id(I)
AND model_remnant_flag is NULL
AND (EXISTS (SELECT NULL
FROM oe_order_lines_all oe2
WHERE oe2.top_model_line_id = oe1.top_model_line_id
AND source_type_code = 'EXTERNAL')
OR -- added for bug 4701487
EXISTS (SELECT NULL
FROM oe_order_lines_all oe3
WHERE oe3.top_model_line_id = oe1.top_model_line_id
AND cancelled_flag = 'N'
AND schedule_ship_date is NULL));
oe_debug_pub.ADD('updated lines model_remnant_flag '|| sql%rowcount,1);
SELECT calculate_price_flag
INTO p_ship_line_rec.calculate_price_flag(i)
FROM oe_order_lines_all
WHERE line_id = p_ship_line_rec.line_id(i);
,p_operation => OE_GLOBALS.G_OPR_UPDATE);
l_change_line_tbl.delete(K);
l_order_has_lines.delete;
l_change_line_tbl.delete;
UPDATE oe_order_lines_all
SET shipped_quantity = ordered_quantity, -- INVCONV
shipped_quantity2 = ordered_quantity2 -- INVCONV
,actual_shipment_date = p_ship_line_rec.actual_shipment_date(i)
,lock_control = lock_control + 1
WHERE line_id in
(SELECT line_id
FROM oe_order_lines_all
WHERE top_model_line_id =
p_ship_line_rec.top_model_line_id(i))
AND shippable_flag = 'N'
AND p_ship_line_rec.error_flag(i) = 'N'
AND shipped_quantity is NULL
AND open_flag = 'Y'
AND nvl(cancelled_flag, 'N') = 'N'
AND source_type_code = 'INTERNAL'
RETURNING line_id,
ato_line_id,
item_type_code,
shipped_quantity,
shipped_quantity2, -- INVCONV
actual_shipment_date,
model_remnant_flag,
top_model_line_id
BULK COLLECT
INTO g_non_shippable_rec.line_id,
g_non_shippable_rec.ato_line_id,
g_non_shippable_rec.item_type_code,
g_non_shippable_rec.shipped_quantity,
g_non_shippable_rec.shipped_quantity2, -- INVCONV
g_non_shippable_rec.actual_shipment_date,
g_non_shippable_rec.model_remnant_flag,
g_non_shippable_rec.top_model_line_id;
oe_debug_pub.ADD('nonshippable lines updated '|| sql%rowcount,1);
UPDATE oe_order_lines_all
SET shipped_quantity = NULL
,actual_shipment_date = NULL
,lock_control = lock_control + 1
WHERE line_id = g_non_shippable_rec.line_id(I);
UPDATE oe_order_lines_all
SET shipped_quantity = null
,lock_control = lock_control + 1
WHERE line_id = g_non_shippable_rec.line_id(I);
g_non_shippable_rec.line_id.delete;
g_non_shippable_rec.ato_line_id.delete;
g_non_shippable_rec.item_type_code.delete;
g_non_shippable_rec.shipped_quantity.delete;
g_non_shippable_rec.shipped_quantity2.delete; -- INVCONV
g_non_shippable_rec.actual_shipment_date.delete;
UPDATE OE_ORDER_LINES_ALL oe1
SET ship_set_id = NULL
WHERE ship_set_id is not NULL
AND shipped_quantity is NULL
AND ship_set_id = p_ship_line_rec.ship_set_id(I);
UPDATE oe_sets
SET set_status = 'C'
WHERE set_id = p_ship_line_rec.ship_set_id(i)
AND SET_STATUS <> 'C'
AND p_ship_line_rec.error_flag(i) = 'N';
UPDATE oe_sets
SET set_status = 'C'
WHERE set_id = p_ship_line_rec.arrival_set_id(i)
AND SET_STATUS <> 'C'
AND p_ship_line_rec.error_flag(i) = 'N';
UPDATE OE_ORDER_LINES_ALL
SET shipping_quantity = null,
shipping_quantity2 = null,
shipped_quantity2 = null,
shipped_quantity = null,
shipping_quantity_uom = null,
shipping_quantity_uom2 = null,
actual_shipment_date = null,
flow_status_code = p_ship_line_rec.flow_status_code(i),
lock_control = lock_control - 1
WHERE line_id = p_ship_line_rec.line_id(i)
AND p_ship_line_rec.error_flag(i) = 'Y';
,p_delete => FND_API.G_TRUE
,x_return_status => l_return_status
);
oe_debug_pub.add('inserting adjustments '
|| p_line_adj_rec.line_id.COUNT, 5);
DELETE FROM OE_PRICE_ADJUSTMENTS
WHERE LINE_ID = p_line_adj_rec.line_id(i)
AND CHARGE_TYPE_CODE IN ('FTEPRICE','FTECHARGE')
AND p_line_adj_rec.charge_type_code(i) IN ('FTEPRICE','FTECHARGE')
AND list_line_type_code = 'COST'
AND p_line_adj_rec.list_line_type_code(i) = 'COST'
AND ESTIMATED_FLAG = 'Y';
INSERT INTO OE_PRICE_ADJUSTMENTS
( price_adjustment_id
,cost_id
,automatic_flag
,list_line_type_code
,charge_type_code
,header_id
,line_id
,adjusted_amount
,arithmetic_operator
,last_update_date
,last_updated_by
,last_update_login
,creation_date
,created_by)
VALUES
( OE_PRICE_ADJUSTMENTS_S.nextval
,p_line_adj_rec.cost_id(i)
,p_line_adj_rec.automatic_flag(i)
,p_line_adj_rec.list_line_type_code(i)
,p_line_adj_rec.charge_type_code(i)
,p_line_adj_rec.header_id(i)
,p_line_adj_rec.line_id(i)
,p_line_adj_rec.adjusted_amount(i)
,p_line_adj_rec.arithmetic_operator(i)
,SYSDATE
,FND_GLOBAL.USER_ID
,FND_GLOBAL.LOGIN_ID
,SYSDATE
,FND_GLOBAL.USER_ID)
RETURNING price_adjustment_id
BULK COLLECT
INTO p_line_adj_rec.price_adjustment_id;
SELECT hsecs INTO l_start_time from v$timer;
('update GLOBAL FLOW_STATUS is: '||l_line_rec.flow_status_code,3);
OE_ORDER_UTIL.Update_Global_Picture
(p_Upd_New_Rec_If_Exists => False,
--p_header_id => l_line_rec.header_id,
p_old_line_rec => l_old_line_rec,
p_line_rec => l_line_rec,
p_line_id => l_line_rec.line_id,
x_index => J,
x_return_status => l_return_status);
oe_debug_pub.add(J || ' UPDATE_GLOBAL ret sts: ' || l_retuRN_STATUS);
OE_ORDER_UTIL.g_line_tbl(J).last_update_date := SYSDATE;
OE_ORDER_UTIL.g_line_tbl(J).last_updated_by := FND_GLOBAL.USER_ID;
OE_ORDER_UTIL.g_line_tbl(J).last_update_login:= FND_GLOBAL.LOGIN_ID;
:= OE_GLOBALS.G_OPR_UPDATE; -- Bug 8442372
('AFTER UPDATE GLOBAL FLOW_STATUS_CODE IS: '
|| OE_ORDER_UTIL.G_LINE_TBL( J ).FLOW_STATUS_CODE ,1);
SELECT hsecs INTO l_end_time from v$timer;
l_update_lines_tbl OE_ORDER_PUB.Request_Tbl_Type;
select Count (1)
INTO l_count
from wf_item_activity_statuses wias, wf_process_activities wpa
where wias.process_activity = wpa.instance_id
and to_number(wias.item_key) = p_line_id
and wias.item_type = 'OEOL'
AND wpa.activity_item_type = wias.item_type
and wias.activity_status = 'NOTIFIED'
and wpa.activity_name = 'SHIP_LINE';
SELECT Count(1)
INTO l_count
FROM wsh_delivery_details
WHERE source_line_id = p_line_id
AND released_status = 'C';
l_update_lines_tbl(1).entity_id := p_line_id;
l_update_lines_tbl(1).param1 := FND_API.G_FALSE;
l_update_lines_tbl(1).param2 := FND_API.G_FALSE;
l_update_lines_tbl(1).param5 := FND_API.G_TRUE;
l_update_lines_tbl(1).request_type := OE_GLOBALS.G_OPR_UPDATE;
OE_Shipping_Integration_PVT.Update_Shipping_From_OE
(
p_update_lines_tbl => l_update_lines_tbl,
x_return_status => l_return_status
);
,p_delete => FND_API.G_TRUE
,x_return_status => l_return_status
);