[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT PICK_STATUS
INTO l_release_status
FROM WSH_DELIVERY_LINE_STATUS_V
WHERE SOURCE_CODE = 'OE'
AND SOURCE_LINE_ID = OE_LINE_SECURITY.g_record.line_id
AND PICK_STATUS = 'S';
SELECT PICK_STATUS
INTO l_pick_status
FROM WSH_DELIVERY_LINE_STATUS_V
WHERE SOURCE_CODE = 'OE'
AND SOURCE_LINE_ID = OE_LINE_SECURITY.g_record.line_id
AND PICK_STATUS = 'Y';
PROCEDURE Update_Shipping_From_OE
parameters in request rec=>
request_unique_key1: line_rec.operation
param1: true/false, whether to update shipping from OE
param2: true/false, explosion_date_changed
param3:
param4:
param5: ordered_quantity_changed
meaning of action flag,
I : delete
S : create
U : update
-----------------------------------------------------------------*/
PROCEDURE Update_Shipping_From_OE
( p_update_lines_tbl IN OE_ORDER_PUB.Request_Tbl_Type
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_shp_index NUMBER :=0;
oe_debug_pub.add('ENTERING oexvshpb UPDATE_SHIPPING_FROM_OE',1);
oe_debug_pub.add('Tbl Count:'||p_update_lines_tbl.COUNT,4);
FOR l_line_index IN p_update_lines_tbl.First .. p_update_lines_tbl.Last
LOOP
l_line_id := p_update_lines_tbl(l_line_index).entity_id;
IF p_update_lines_tbl(l_line_index).request_unique_key1
= OE_GLOBALS.G_OPR_DELETE THEN
-- fix for 3779333
-- select item type code from oe_orderlines_table. For config lines
-- pass action code as 'U' to shipping. for other item types, pass
-- action code as 'D'
SELECT item_type_code
INTO l_item_type_code
FROM oe_order_lines
WHERE line_id = l_line_id;
:= p_update_lines_tbl(l_line_index).entity_id;
oe_debug_pub.add('line is deleted '
|| L_CHANGED_ATTRIBUTES(L_SHP_INDEX).SOURCE_LINE_ID , 3 ) ;
GOTO END_UPDATE_SHIPPING_LOOP;
:= p_update_lines_tbl(l_line_index).entity_id;
OE_DEBUG_PUB.Add ('This is a config item cancel/delete case',1);
END IF; -- if operation is delete
IF p_update_lines_tbl(l_line_index).param2 = FND_API.G_TRUE THEN
IF g_debug_call > 0 THEN
G_DEBUG_MSG := G_DEBUG_MSG || '7,';
GOTO END_UPDATE_SHIPPING_LOOP;
IF p_update_lines_tbl(l_line_index).param4 = FND_API.G_TRUE THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add('line at shipping, update released flag '
|| TO_CHAR ( L_LINE_ID ) , 3 ) ;
SELECT shipping_interfaced_flag, header_id
INTO l_shipping_interfaced_flag, l_header_id
FROM oe_order_lines
WHERE line_id = l_line_id;
GOTO END_UPDATE_SHIPPING_LOOP;
IF p_update_lines_tbl(l_line_index).param2 = FND_API.G_TRUE
AND l_line_at_shipping = FND_API.G_TRUE THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add('explosion date changed, activity shipping '
|| TO_CHAR ( L_LINE_ID ) , 3 ) ;
GOTO END_UPDATE_SHIPPING_LOOP;
IF p_update_lines_tbl(l_line_index).request_unique_key1
= OE_GLOBALS.G_OPR_CREATE THEN
l_changed_attributes(l_shp_index).action_flag := 'S';
oe_debug_pub.add(p_update_lines_tbl(l_line_index).param5
|| '-- param5---', 1);
IF p_update_lines_tbl(l_line_index).param5 = FND_API.G_TRUE AND
nvl(l_line_rec.shipped_quantity,0) = 0 AND
nvl(l_line_rec.ordered_quantity,0) <> 0 AND -- bug 2129287
nvl(l_line_rec.ship_tolerance_below,0) < 100 THEN --1829490
IF g_debug_call > 0 THEN
G_DEBUG_MSG := G_DEBUG_MSG || '18,';
UPDATE oe_order_lines
SET flow_status_code = 'SHIPPED',
shipped_quantity = 0,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE line_id = l_line_rec.line_id;
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_tbl(1),
p_line_rec => l_line_tbl(1),
p_line_id => l_line_rec.line_id,
x_index => l_index,
x_return_status => l_return_status);
oe_debug_pub.add( 'after update global picture, status: '
|| L_RETURN_STATUS ) ;
OE_ORDER_UTIL.g_line_tbl(l_index).last_update_date
:= SYSDATE;
OE_ORDER_UTIL.g_line_tbl(l_index).last_updated_by
:= FND_GLOBAL.USER_ID;
OE_ORDER_UTIL.g_line_tbl(l_index).last_update_login
:= FND_GLOBAL.LOGIN_ID;
('global flow status code after update: '
|| OE_ORDER_UTIL.G_LINE_TBL( L_INDEX ).FLOW_STATUS_CODE ,1);
UPDATE OE_SETS
SET SET_STATUS = 'C'
WHERE SET_ID = l_line_rec.arrival_set_id;
ELSE -- regular qtu update
l_changed_attributes(l_shp_index).ordered_quantity
:= l_line_rec.ordered_quantity;
p_update_lines_tbl(l_line_index).request_unique_key1 =
OE_GLOBALS.G_OPR_DELETE THEN
l_changed_attributes(l_shp_index).ordered_quantity := 0;
OE_DEBUG_PUB.Add('This is Config cancel/delete case!',1);
END IF; -- p_update_lines_tbl(l_line_index).param5 etc
<>
NULL;
oe_debug_pub.add('CALLING WSH UPDATE_SHIPPING_ATTRIBUTES' , 2 ) ;
WSH_INTERFACE.Update_Shipping_Attributes
( p_source_code => l_source_code
,p_changed_attributes => l_changed_attributes
,x_return_status => l_return_status);
oe_debug_pub.add( 'RETURNED FROM WSH UPDATE_SHIPPING_ATTRIBUTES '
|| L_RETURN_STATUS , 2 ) ;
SELECT Count(*)
INTO l_exist_dd_count
FROM wsh_delivery_details
WHERE source_line_id = l_changed_attributes(I).source_line_id
AND source_code = 'OE'
;
SELECT Count(*)
INTO l_non_cxl_dd_count
FROM wsh_delivery_details wdd, oe_order_lines line
WHERE wdd.source_line_id = line.line_id
AND wdd.source_code = 'OE'
AND wdd.source_line_id = l_changed_attributes(I).source_line_id
AND Nvl(wdd.released_status, 'N') <> 'D'
;
oe_msg_pub.delete_msg;
oe_debug_pub.add('action delete, do not call dcp here',2);
SELECT header_id
INTO l_header_id
FROM oe_order_lines
WHERE line_id = l_changed_attributes(1).source_line_id;
WSH_DCP_PVT.g_dc_table.DELETE;
('Update_Shipping_From_OE, DCP post process'|| sqlerrm);
SELECT COUNT(*)
INTO l_cxl_dd_count
FROM WSH_DELIVERY_DETAILS
WHERE SOURCE_LINE_ID = l_changed_attributes(I).source_line_id
AND SOURCE_CODE = 'OE'
AND RELEASED_STATUS <> 'D';
oe_debug_pub.add( 'returning from UPDATE_SHIPPING_FROM_OE' , 1 ) ;
'Update_Shipping_From_OE'
);
'Update_Shipping_From_OE'
);
END Update_Shipping_From_OE;
PROCEDURE Update_Shipping_PVT
-----------------------------------------------------------------*/
PROCEDURE Update_Shipping_PVT
(
p_line_id IN NUMBER
, p_hold_type IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
, p_shipping_activity IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
, x_return_status OUT NOCOPY VARCHAR2
)
IS
l_update_lines_tbl OE_ORDER_PUB.Request_Tbl_Type;
oe_debug_pub.add( 'ENTERING OE_SHIPPING_INTEGRATION_PVT.UPDATE_SHIPPING_PVT' , 1 ) ;
-- Prepare the table for calling Update_Shipping_From_OE
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).param3 := p_hold_type;
l_update_lines_tbl(1).param4 := p_shipping_activity;
Update_Shipping_From_OE
(
p_update_lines_tbl => l_update_lines_tbl,
x_return_status => x_return_status
);
oe_debug_pub.add( 'EXITING OE_SHIPPING_INTEGRATION_PVT.UPDATE_SHIPPING_PVT' , 1 ) ;
'Update_Shipping_PVT'
);
'Update_Shipping_PVT'
);
END Update_Shipping_PVT;
SELECT line_id,
line_set_id,
ordered_quantity,
shipped_quantity,
shipping_quantity,
ship_tolerance_below,
ship_tolerance_above,
item_type_code,
inventory_item_id,
order_quantity_uom,
shipping_quantity_uom
INTO l_line_rec.line_id,
l_line_rec.line_set_id,
l_line_rec.ordered_quantity,
l_line_rec.shipped_quantity,
l_line_rec.shipping_quantity,
l_line_rec.ship_tolerance_below,
l_line_rec.ship_tolerance_above,
l_line_rec.item_type_code,
l_line_rec.inventory_item_id,
l_line_rec.order_quantity_uom,
l_line_rec.shipping_quantity_uom
FROM OE_ORDER_LINES
WHERE line_id = p_line_rec.top_model_line_id;
SELECT count(*)
INTO l_wdd_count
FROM wsh_delivery_details
WHERE source_line_id = p_line_rec.line_id
AND released_status <> 'D'
AND source_code = 'OE'
AND oe_interfaced_flag = 'N';
SELECT QUANTITY, QUANTITY_RECEIVED
INTO actual_quantity,poll_received_quantity
FROM PO_LINE_LOCATIONS_ALL
WHERE LINE_LOCATION_ID = (SELECT LINE_LOCATION_ID
FROM OE_DROP_SHIP_SOURCES
WHERE LINE_ID = l_line_rec.line_id);
update oe_order_lines
set model_remnant_flag = 'Y'
where top_model_line_id = p_x_line_tbl(1).top_model_line_id;
l_update_tolerance_value NUMBER := 0 ;
l_update_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
l_update_tolerance_value := l_line_tbl(l_pto_index).ship_tolerance_below;
l_update_tolerance_value := 0;
oe_debug_pub.add( 'NEW TOLERANCE VALUE : '||L_UPDATE_TOLERANCE_VALUE , 3 ) ;
l_update_line_tbl(l_upd_tbl_index) := l_line_tbl(l_pto_index);
l_update_line_tbl(l_upd_tbl_index).operation
:= OE_GLOBALS.G_OPR_UPDATE;
l_update_line_tbl(l_upd_tbl_index).ship_tolerance_below
:= l_update_tolerance_value;
oe_debug_pub.add( 'NEW TOLERANCE = '|| L_UPDATE_LINE_TBL ( L_UPD_TBL_INDEX ) .SHIP_TOLERANCE_BELOW , 3 ) ;
oe_debug_pub.add( 'SHIPPED QUANTITY = '|| TO_CHAR ( L_UPDATE_LINE_TBL ( L_UPD_TBL_INDEX ) .SHIPPED_QUANTITY ) , 3 ) ;
oe_debug_pub.add( 'CALLING PO TO UPDATE SHIPPED QTY FOR NON SHIPPABLE LINES' , 3 ) ;
( p_line_tbl => l_update_line_tbl,
p_control_rec => l_control_rec,
x_return_status => l_return_status );
l_split_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
UPDATE OE_ORDER_LINES_ALL
SET SHIP_TOLERANCE_BELOW = 0,
SHIP_TOLERANCE_ABOVE = 0
WHERE TOP_MODEL_LINE_ID = l_line_tbl(l_top_model_index).line_id;
:= OE_GLOBALS.G_OPR_UPDATE;
:= 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);
SELECT nvl(sum(shipped_quantity),0), requested_quantity_uom
INTO l_shipped_quantity, l_requested_qty_uom
FROM wsh_delivery_details
WHERE source_line_id in
(SELECT line_id
FROM oe_order_lines
WHERE line_set_id = l_line_rec.line_set_id
AND shipped_quantity IS NULL
AND line_id <> l_line_rec.line_id)
AND source_code = 'OE'
AND released_status = 'C'
GROUP BY requested_quantity_uom;
oe_debug_pub.add('wsh select error '|| sqlerrm, 1);
will be shipping interfaced, and delete reservation
will be taken care by shipping */
IF g_debug_call > 0 THEN
G_DEBUG_MSG := G_DEBUG_MSG || '104';
l_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
SELECT ato_line_id
INTO l_line_rec.ato_line_id
FROM oe_order_lines
WHERE line_id = l_line_rec.line_id;
OE_Order_WF_Util.Update_Flow_Status_Code
(p_header_id => l_line_rec.header_id,
p_line_id => l_line_rec.line_id,
p_flow_status_code => 'SHIPPED',
x_return_status => l_return_status );
UPDATE OE_SETS
SET SET_STATUS = 'C'
WHERE SET_ID = l_line_rec.arrival_set_id;
l_update_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
x_line_tbl => l_update_line_tbl);
l_update_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
l_update_line_tbl(1).shipped_quantity := l_line_rec.shipped_quantity;
l_update_line_tbl(1).actual_shipment_date := l_line_rec.actual_shipment_date;
||TO_CHAR(L_UPDATE_LINE_TBL(1).ACTUAL_SHIPMENT_DATE,'DD-MM-YY'),3);
( p_line_tbl => l_update_line_tbl,
p_control_rec => l_control_rec,
x_return_status => l_return_status);
l_split_line_tbl(1) := l_update_line_tbl(1);
l_split_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
l_split_line_tbl(2).split_from_line_id := l_update_line_tbl(1).line_id;
-- Update the set status to closed.
UPDATE OE_SETS
SET SET_STATUS = 'C'
WHERE SET_ID = p_ship_set_id;
l_set_tbl(l_set_index).operation := OE_GLOBALS.G_OPR_UPDATE;
oe_debug_pub.add( 'CALLING PROCESS ORDER TO UPDATE SHIP SET ID' , 3 ) ;
SELECT line_id
FROM oe_order_lines,wf_item_activity_statuses wias,
wf_process_activities wpa
WHERE header_id = l_header_id
AND top_model_line_id = l_top_model_line_id
AND link_to_line_id = p_line_id
AND item_type_code = 'INCLUDED'
AND wias.item_type = 'OEOL'
AND wias.item_key = to_char(line_id)
AND wias.process_activity = wpa.instance_id
AND wpa.activity_name = 'SHIP_LINE'
AND wias.activity_status = 'NOTIFIED'
AND shipping_interfaced_flag = 'N'
ORDER BY line_number,shipment_number,nvl(option_number,-1),
nvl(component_number,-1);
SELECT line_category_code,
source_type_code,
ship_model_complete_flag,
top_model_line_id,
link_to_line_id,
shippable_flag,
explosion_date,
ato_line_id,
line_id,
item_type_code,
header_id,
model_remnant_flag,
ship_from_org_id
INTO l_line_category_code,
l_source_type_code,
l_ship_model_complete_flag,
l_top_model_line_id,
l_link_to_line_id,
l_shippable_flag,
l_explosion_date,
l_ato_line_id,
l_line_id,
l_item_type_code,
l_header_id,
l_model_remnant_flag,
l_ship_from_org_id
FROM OE_ORDER_LINES_ALL
WHERE LINE_ID = p_line_id;
SELECT Enforce_Ship_Set_And_Smc
INTO l_enforce_smc_flag
FROM Wsh_Shipping_Parameters
WHERE Organization_Id = l_ship_from_org_Id;
Update_Shipping_PVT(p_line_id => c_line.line_id,
p_shipping_activity => l_shipping_activity,
x_return_status => l_return_status);
SELECT explosion_date
INTO l_parent_explosion_date
FROM oe_order_lines
WHERE top_model_line_id = l_top_model_line_id
AND header_id = l_header_id
AND line_id = l_link_to_line_id;
Update_Shipping_PVT(p_line_id => l_line_id,
p_shipping_activity => l_shipping_activity,
x_return_status => l_return_status);
OE_Order_WF_Util.Update_Flow_Status_Code
(p_header_id => l_header_id,
p_line_id => l_line_id,
p_flow_status_code => l_flow_status_code,
x_return_status => l_return_status);
Yes for now. It will be done in Update Shipping procedure
on the change of explosion date because of explosion.
--------------------------------------------------------------*/
PROCEDURE Process_SMC_Shipping
( p_line_id IN NUMBER
,p_top_model_line_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_line_tbl OE_Order_Pub.Line_Tbl_Type;
l_update_lines_tbl OE_ORDER_PUB.Request_Tbl_Type;
l_update_lines_index NUMBER := 0;
SELECT 'Line is Released , staged or confirmed..'
INTO l_dummy
FROM WSH_DELIVERY_DETAILS
WHERE SOURCE_LINE_ID = l_line_tbl(l_line_index).line_id
AND RELEASED_STATUS <> 'N';
oe_debug_pub.add( 'SHIPPING NEEDS UPDATE'||L_LINE_TBL ( L_LINE_INDEX ) .LINE_ID , 3 ) ;
l_update_lines_index := l_update_lines_index + 1;
l_update_lines_tbl(l_update_lines_index).entity_id
:= l_line_tbl(l_line_index).line_id;
l_update_lines_tbl(l_update_lines_index).param1
:= FND_API.G_FALSE;
l_update_lines_tbl(l_update_lines_index).param2
:= FND_API.G_FALSE;
l_update_lines_tbl(l_update_lines_index).param4
:= FND_API.G_TRUE;
SELECT 'X'
INTO l_dummy
FROM oe_order_lines
WHERE top_model_line_id = p_top_model_line_id
AND ato_line_id = l_line_tbl(l_line_index).ato_line_id
AND item_type_code = 'CONFIG';
select activity_status
into l_activity_status_code
from wf_item_activity_statuses wias,
wf_process_activities wpa
where wias.item_type = 'OEOL'
and wias.item_key = to_char(l_line_tbl(l_line_index).line_id)
and wias.process_activity = wpa.instance_id
and wpa.activity_name = 'SHIP_LINE'
and wias.activity_status in ('NOTIFIED','COMPLETE');
IF l_update_lines_tbl.count > 0 THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'UPDATE SHIPPING '||L_UPDATE_LINES_TBL.COUNT , 3 ) ;
Update_Shipping_From_OE
( p_update_lines_tbl => l_update_lines_tbl,
x_return_status => x_return_status);
oe_debug_pub.add( 'UPDATE_SHIPPING_FROM_OE : '|| X_RETURN_STATUS , 3 ) ;
oe_debug_pub.add( 'DONT CALL UPDATE_SHIPPING_FROM_OE' , 3 ) ;
SELECT /* MOAC_SQL_CHANGE */ line_id, line_set_id, ordered_quantity, ordered_quantity2,
order_quantity_uom, ordered_quantity_uom2, inventory_item_id
FROM oe_order_lines_all oe
WHERE line_id in
(SELECT line_id
FROM oe_order_lines_all
WHERE line_set_id = p_line_rec.line_set_id
AND line_id <> p_line_rec.line_id)
AND open_flag = 'Y'
AND shipped_quantity is NULL
-- AND line_id not in
-- (SELECT source_line_id
-- FROM wsh_delivery_details
-- WHERE released_status in ('Y', 'C')
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';
UPDATE OE_ORDER_LINES
SET shipping_quantity = l_line_set_rec.shipping_quantity,
shipped_quantity = l_line_set_rec.shipped_quantity,
shipping_quantity2 = l_line_set_rec.shipping_quantity2,
shipped_quantity2 = l_line_set_rec.shipped_quantity2,
shipping_quantity_uom = l_line_set_rec.shipping_quantity_uom,
shipping_quantity_uom2 = l_line_set_rec.shipping_quantity_uom2, -- INVCONV
actual_shipment_date = l_line_set_rec.actual_shipment_date
WHERE line_id = l_line_set_rec.line_id;
END IF; -- if wdd has lines staged/shipped or not deleted
OE_ORDER_UTIL.Update_Global_Picture
(p_Upd_New_Rec_If_Exists => False,
p_header_id => l_line_tbl(I).header_id,
p_old_line_rec => l_old_line_tbl(I),
p_line_rec => l_line_tbl(I),
p_line_id => l_line_tbl(I).line_id,
x_index => J,
x_return_status => x_return_status);
(J || ' UPDATE_GLOBAL ret sts: ' || x_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;
('AFTER UPDATE GLOBAL FLOW_STATUS_CODE IS: '
|| OE_ORDER_UTIL.G_LINE_TBL( J ).FLOW_STATUS_CODE ,1);
WSH_bulk_process_grp.Create_update_delivery_details
( p_api_version_number => 1.0
,p_init_msg_list => FND_API.G_TRUE
,p_commit => FND_API.G_FALSE
,p_action_prms => l_action_rec
,p_line_rec => p_line_rec
,x_out_rec => l_out_rec
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
UPDATE oe_order_lines
SET shipping_interfaced_flag = 'Y',
flow_status_code = 'AWAITING_SHIPPING',
firm_demand_flag = NVL(l_firm_flag,firm_demand_flag)
WHERE line_id = p_line_rec.line_id(I)
AND p_line_rec.shipping_interfaced_flag(I) = 'Y';
select activity_result_code
into l_ship_confirm_status
from wf_process_activities p, wf_item_activity_statuses s
where p.instance_id = s.process_activity
and item_type = 'OEOL'
and item_key = to_char(OE_LINE_SECURITY.g_record.line_id)
and p.activity_name = 'SHIP_LINE'
and s.activity_status = 'COMPLETE';
select count(1)
into l_count_reserve
from wf_process_activities p, wf_item_activity_statuses s
where p.instance_id = s.process_activity
and item_type = 'OEOL'
and item_key = to_char(OE_LINE_SECURITY.g_record.line_id)
and p.activity_name = 'SHIP_LINE'
and s.activity_status = 'COMPLETE'
and (activity_result_code IS NULL OR activity_result_code <> 'UNRESERVE');