[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
UPDATE WSH_DELIVERY_DETAILS
SET released_status = decode(pickable_flag, 'Y','R', 'X')
WHERE source_line_id = p_changed_attributes(l_counter).source_line_id
AND source_code = p_source_code
RETURNING delivery_detail_id, organization_id BULK COLLECT INTO l_detail_tab, l_organization_tab;
WSH_INTEGRATION.DBI_Update_Detail_Log
(p_delivery_detail_id_tab => l_detail_tab,
p_dml_type => 'UPDATE',
x_return_status => l_dbi_rs);
SELECT inventory_item_id , requested_quantity_uom ,sum ( requested_quantity ),
sum ( NVL(requested_quantity2,0) )
from wsh_delivery_details
where source_line_id = p_source_line_id
and source_code = 'OE'
group by inventory_item_id , requested_quantity_uom ;
SELECT wdd.delivery_detail_id,
wdd.requested_quantity,
wdd.picked_quantity,
wdd.shipped_quantity,
wdd.cycle_count_quantity,
wdd.requested_quantity_uom,
wdd.requested_quantity2,
wdd.picked_quantity2,
wdd.shipped_quantity2,
wdd.cycle_count_quantity2,
wdd.requested_quantity_uom2,
wdd.released_status,
wdd.move_order_line_id,
wdd.organization_id,
wdd.inventory_item_id,
wdd.revision,
wdd.subinventory,
wdd.lot_number,
-- HW OPMCONV - No need for sublot_number
-- wdd.sublot_number,
wdd.locator_id,
wdd.source_line_id,
wdd.source_code,
wdd.source_header_id,
wdd.net_weight,
wdd.cancelled_quantity,
wdd.cancelled_quantity2,
wdd.serial_number,
wdd.to_serial_number,
wdd.transaction_temp_id,
wdd.pickable_flag,
wdd.ato_line_id,
wdd.container_flag,
NVL(wdd.inv_interfaced_flag, 'N') inv_interfaced_flag,
wdd.source_line_set_id, -- anxsharm Bug 2181132
wda.delivery_id,
wda.parent_delivery_detail_id ,
wdd1.lpn_id, -- Bug 2773605 : Need to fetch the parent's lpn_id also.
-- J: W/V Changes
wdd.gross_weight,
wdd.volume,
wdd.weight_uom_code,
wdd.volume_uom_code,
wdd.wv_frozen_flag,
-- End W/V Changes
wdd.source_header_number,-- ECO 4524041, add field to display in message
-- K: MDC
wda.parent_delivery_id,
NVL(wda.type, 'S') wda_type,
-- END K: MDC
wdd.replenishment_status -- bug# 6719369 (replenishment project)
FROM wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_delivery_details wdd1 -- Bug 2773605: Added to fetch parent's lpn_id
WHERE wdd.source_code = x_source_code
AND wdd.source_line_id = x_original_source_line_id
AND wdd.container_flag = 'N'
AND wdd.released_status <> 'D'
AND (
( x_interface_flag = 'N'
)
OR
( x_interface_flag = 'Y'
AND (
(x_shipped_flag = 'Y' AND wdd.oe_interfaced_flag = 'P')
OR (x_shipped_flag = 'N' AND NVL(wdd.oe_interfaced_flag, 'N') = 'N')
)
)
)
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND NVL(wda.type, 'S') in ('S', 'C')
AND wda.parent_Delivery_Detail_id = wdd1.delivery_Detail_id(+)
ORDER BY DECODE(wdd.released_status,
'C', 1,
'Y', 2,
'R', 3,
'N', 4,
'B', 5,
'X', 6,
7), -- 'S': save "released to warehouse" for last
wdd.REQUESTED_QUANTITY DESC;
SELECT sum(nvl(wdd.requested_quantity, 0))
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
wsh_delivery_details wdd1
WHERE wdd.source_code = x_source_code
AND wdd.source_line_id = x_original_source_line_id
AND wdd.container_flag = 'N'
AND wdd.released_status <> 'D'
AND NVL(wdd.oe_interfaced_flag, 'N') = 'N'
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wda.parent_Delivery_Detail_id = wdd1.delivery_Detail_id(+);
l_delete_dds WSH_UTIL_CORE.Id_Tab_Type ; -- to delete delivery lines pending overpick
UPDATE WSH_DELIVERY_DETAILS
SET source_line_set_id = p_changed_attributes(l_counter).source_line_set_id,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE source_line_id = p_changed_attributes(l_counter).original_source_line_id
AND source_code = p_source_code
AND container_flag = 'N'
AND released_status <> 'D';
FND_MESSAGE.SET_NAME('WSH', 'WSH_UPDATE_CANNOT_SPLIT');
l_delete_dds( l_delete_dds.count+1 ) := c.delivery_detail_id;
UPDATE WSH_DELIVERY_DETAILS
SET source_line_id = p_changed_attributes(l_counter).source_line_id,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE delivery_detail_id = c.delivery_detail_id;
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WMS_REPLENISHMENT_PUB.UPDATE_DELIVERY_DETAIL' ,WSH_DEBUG_SV.C_PROC_LEVEL);
WMS_REPLENISHMENT_PUB.UPDATE_DELIVERY_DETAIL(
p_delivery_detail_id => l_detail_info.delivery_detail_id,
p_primary_quantity => c.requested_quantity - l_quantity_split,
p_split_delivery_detail_id => l_working_detail_id,
p_split_source_line_id => p_changed_attributes(l_counter).source_line_id,
x_return_status => x_return_status);
WSH_DEBUG_SV.logmsg(l_module_name, 'UNEXPECTED ERROR FROM WMS_REPLENISHMENT_PUB.UPDATE_DELIVERY_DETAIL');
UPDATE wsh_delivery_details
SET released_status = 'R',
move_order_line_id = NULL
WHERE delivery_detail_id = c.delivery_detail_id;
UPDATE wsh_delivery_details
SET released_status = 'S',
move_order_line_id = NULL
WHERE delivery_detail_id = c.delivery_detail_id;
WSH_INTEGRATION.DBI_Update_Detail_Log
(p_delivery_detail_id_tab => l_detail_tab,
p_dml_type => 'UPDATE',
x_return_status => l_dbi_rs);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit CTO_WORKFLOW_API_PK.WF_UPDATE_AFTER_INV_UNRESERV',WSH_DEBUG_SV.C_PROC_LEVEL);
CTO_WORKFLOW_API_PK.wf_update_after_inv_unreserv(
p_order_line_id => p_changed_attributes(l_counter).source_line_id,
x_return_status => l_rs,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
WSH_DEBUG_SV.log(l_module_name,'wf_update_after_inv_unreserv returned error');
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit CTO_WORKFLOW_API_PK.WF_UPDATE_AFTER_INV_UNRESERV',WSH_DEBUG_SV.C_PROC_LEVEL);
CTO_WORKFLOW_API_PK.wf_update_after_inv_unreserv(
p_order_line_id => p_changed_attributes(l_counter).original_source_line_id,
x_return_status => l_rs,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
WSH_DEBUG_SV.log(l_module_name,'wf_update_after_inv_unreserv returned error');
UPDATE WSH_DELIVERY_DETAILS
SET SRC_REQUESTED_QUANTITY = SRC_REQUESTED_QUANTITY - p_changed_attributes(l_counter).ordered_quantity,
SRC_REQUESTED_QUANTITY2 = SRC_REQUESTED_QUANTITY2 - p_changed_attributes(l_counter).ordered_quantity2
WHERE SOURCE_LINE_ID = p_changed_attributes(l_counter).original_source_line_id
AND SOURCE_CODE = p_source_code;
AND l_delete_dds.count > 0 THEN
WSH_INTERFACE.Delete_Details(
p_details_id => l_delete_dds,
x_return_status => l_rs
);
l_updates_count NUMBER ;
l_updates_count := 0 ;
FOR i in p_changed_attributes.FIRST..p_changed_attributes.LAST --{loop on p_changed_attributes to scan for request_date on update records.
LOOP
IF l_debug_on THEN
WSH_DEBUG_SV.log(l_module_name,'Index i',i);
l_updates_count := l_updates_count + 1 ;
PROCEDURE Update_Records(
p_source_code IN VARCHAR2,
p_changed_attributes IN WSH_INTERFACE.ChangedAttributeTabType,
p_interface_flag IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_counter NUMBER;
l_update_allowed VARCHAR2(1);
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_RECORDS';
l_update_allowed := 'Y';
x_update_allowed => l_update_allowed,
x_return_status => l_rs);
WSH_DEBUG_SV.log(l_module_name, 'L_UPDATE_ALLOWED', l_update_allowed);
IF NVL(l_update_allowed, 'N') = 'N' THEN
-- bug 5387341: to prevent data corruption, we should return
-- error when update is not allowed, since that means that
-- the delivery detail(s) will not be synchronized with the
-- order line.
IF NVL(l_rs, 'X') <> WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
l_rs := WSH_UTIL_CORE.G_RET_STS_ERROR;
IF l_update_allowed = 'Y' THEN
WSH_USA_ACTIONS_PVT.Update_Attributes(
p_source_code => p_source_code,
p_attributes_rec => p_changed_attributes(l_counter),
p_changed_detail => l_changed_detail,
x_return_status => l_rs);
WSH_DEBUG_SV.logmsg(l_module_name, 'AFTER CALLING WSH_USA_ACTIONS_PVT.UPDATE_ATTRIBUTES ' || L_RS );
END IF; -- l_update_allowed = 'Y'
wsh_util_core.default_handler('WSH_USA_ACTIONS_PVT.Update_Records',l_module_name);
END Update_Records;
Procedure Update_Attributes(
p_source_code IN VARCHAR2,
p_attributes_rec IN WSH_INTERFACE.ChangedAttributeRecType,
p_changed_detail IN WSH_USA_CATEGORIES_PVT.ChangedDetailRec,
x_return_status OUT NOCOPY VARCHAR2)
IS
--bug#6407943:Needs to change items org dependent attributes when org changes.
cursor c_is_reservable IS
select inventory_item_id, organization_id, pickable_flag,requested_quantity_uom,
unit_weight,weight_uom_code,unit_volume,volume_uom_code,hazard_class_id,item_description
from wsh_delivery_details
where source_line_id = p_attributes_rec.source_line_id
and source_code = p_source_code
and rownum = 1;
select delivery_detail_id, released_status, date_requested, date_scheduled
from wsh_delivery_details
WHERE source_code = p_source_code
AND source_line_id = p_attributes_rec.source_line_id
AND container_flag = 'N'
AND delivery_detail_id = decode( p_attributes_rec.delivery_detail_id, FND_API.G_MISS_NUM ,
delivery_detail_id, p_attributes_rec.delivery_detail_id );
select organization_id, carrier_id, ship_method_code, ignore_for_planning
from wsh_delivery_details
WHERE source_code = p_source_code
AND source_line_id = p_attributes_rec.source_line_id
AND container_flag = 'N'
AND delivery_detail_id = decode( p_attributes_rec.delivery_detail_id, FND_API.G_MISS_NUM ,
delivery_detail_id, p_attributes_rec.delivery_detail_id )
AND nvl(ignore_for_planning,'N')<>'Y'
AND rownum=1;
SELECT wnd.name delivery_name
FROM wsh_delivery_assignments_v wda, wsh_new_deliveries wnd
WHERE wda.delivery_id = wnd.delivery_id AND
wda.delivery_id IS NOT NULL AND
wda.delivery_detail_id=p_detailid;
SELECT NVL(mtl_transactions_enabled_flag, 'N')
FROM mtl_system_items
WHERE inventory_item_id = c_item_id
AND organization_id = c_org_id;
SELECT hazard_class_id, primary_uom_code, weight_uom_code,
unit_weight, volume_uom_code, unit_volume,description
FROM mtl_system_items m
WHERE m.inventory_item_id = c_p_inventory_item_id
AND m.organization_id = c_p_organization_id;
Update_Failed Exception;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_ATTRIBUTES';
raise Update_Failed;
raise Update_Failed;
raise Update_Failed;
raise Update_Failed;
raise Update_Failed;
Raise Update_failed;
raise Update_Failed;
raise Update_Failed;
UPDATE wsh_delivery_details
SET sold_to_contact_id = decode ( p_attributes_rec.sold_to_contact_id, FND_API.G_MISS_NUM ,
sold_to_contact_id , NVL(p_attributes_rec.sold_to_contact_id, sold_to_contact_id) ) ,
ship_to_contact_id = decode ( p_attributes_rec.ship_to_contact_id, FND_API.G_MISS_NUM ,
ship_to_contact_id , p_attributes_rec.ship_to_contact_id ) ,
deliver_to_contact_id = decode ( p_attributes_rec.deliver_to_contact_id, FND_API.G_MISS_NUM ,
deliver_to_contact_id , p_attributes_rec.deliver_to_contact_id ) ,
organization_id = decode (p_attributes_rec.ship_from_org_id,
FND_API.G_MISS_NUM, organization_id,
NULL, organization_id,
p_attributes_rec.ship_from_org_id),
ship_from_location_id = decode (p_attributes_rec.ship_from_org_id,
FND_API.G_MISS_NUM, ship_from_location_id,
NULL, ship_from_location_id,
-- bug 2894922: if organization is not changed, keep ship_from_location_id
organization_id, ship_from_location_id,
l_ship_from_location_id ) ,
ship_to_location_id = decode (p_attributes_rec.ship_to_org_id, FND_API.G_MISS_NUM ,
ship_to_location_id , l_ship_to_location_id ) ,
ship_to_site_use_id = decode ( p_attributes_rec.ship_to_org_id, FND_API.G_MISS_NUM ,
ship_to_site_use_id , p_attributes_rec.ship_to_org_id ) ,
deliver_to_site_use_id = decode ( p_attributes_rec.deliver_to_org_id, FND_API.G_MISS_NUM ,
deliver_to_site_use_id , p_attributes_rec.deliver_to_org_id ) ,
deliver_to_location_id = decode (p_attributes_rec.deliver_to_org_id, FND_API.G_MISS_NUM ,
deliver_to_location_id , l_deliver_to_location_id ) ,
intmed_ship_to_contact_id = decode ( p_attributes_rec.intmed_ship_to_contact_id, FND_API.G_MISS_NUM ,
intmed_ship_to_contact_id , p_attributes_rec.intmed_ship_to_contact_id ) ,
intmed_ship_to_location_id = decode (p_attributes_rec.intmed_ship_to_org_id, FND_API.G_MISS_NUM ,
intmed_ship_to_location_id , l_intmed_ship_to_location_id ) ,
customer_id = decode (p_attributes_rec.sold_to_org_id, FND_API.G_MISS_NUM ,
customer_id , p_attributes_rec.sold_to_org_id),
ship_tolerance_above = decode ( p_attributes_rec.ship_tolerance_above, FND_API.G_MISS_NUM ,
ship_tolerance_above , p_attributes_rec.ship_tolerance_above ) ,
ship_tolerance_below = decode ( p_attributes_rec.ship_tolerance_below, FND_API.G_MISS_NUM ,
ship_tolerance_below , p_attributes_rec.ship_tolerance_below ) ,
customer_requested_lot_flag = decode ( p_attributes_rec.customer_requested_lot_flag, FND_API.G_MISS_CHAR ,
customer_requested_lot_flag , p_attributes_rec.customer_requested_lot_flag ),
date_requested = decode ( p_attributes_rec.date_requested, FND_API.G_MISS_DATE ,
date_requested , p_attributes_rec.date_requested ) ,
date_scheduled = decode ( p_attributes_rec.date_scheduled, FND_API.G_MISS_DATE ,
date_scheduled , p_attributes_rec.date_scheduled ) ,
dep_plan_required_flag = decode ( p_attributes_rec.dep_plan_required_flag, FND_API.G_MISS_CHAR ,
dep_plan_required_flag , p_attributes_rec.dep_plan_required_flag ) ,
customer_prod_seq = decode ( p_attributes_rec.customer_prod_seq, FND_API.G_MISS_CHAR ,
customer_prod_seq , p_attributes_rec.customer_prod_seq ) ,
customer_dock_code = decode ( p_attributes_rec.customer_dock_code, FND_API.G_MISS_CHAR ,
customer_dock_code , p_attributes_rec.customer_dock_code ) ,
cust_model_serial_number = decode ( p_attributes_rec.cust_model_serial_number, FND_API.G_MISS_CHAR ,
cust_model_serial_number , p_attributes_rec.cust_model_serial_number ) ,
customer_job = decode ( p_attributes_rec.customer_job, FND_API.G_MISS_CHAR ,
customer_job , p_attributes_rec.customer_job ) ,
customer_production_line = decode ( p_attributes_rec.customer_production_line, FND_API.G_MISS_CHAR ,
customer_production_line , p_attributes_rec.customer_production_line ) ,
cust_po_number = decode ( p_attributes_rec.cust_po_number, FND_API.G_MISS_CHAR ,
cust_po_number , p_attributes_rec.cust_po_number ) ,
packing_instructions = decode ( p_attributes_rec.packing_instructions, FND_API.G_MISS_CHAR ,
packing_instructions , p_attributes_rec.packing_instructions ) ,
shipment_priority_code = decode ( p_attributes_rec.shipment_priority_code, FND_API.G_MISS_CHAR ,
shipment_priority_code , p_attributes_rec.shipment_priority_code ) ,
ship_set_id = decode ( p_attributes_rec.ship_set_id, FND_API.G_MISS_NUM ,
ship_set_id , p_attributes_rec.ship_set_id ) ,
ato_line_id = decode ( p_attributes_rec.ato_line_id, FND_API.G_MISS_NUM ,
ato_line_id , p_attributes_rec.ato_line_id ) ,
arrival_set_id = decode ( p_attributes_rec.arrival_set_id, FND_API.G_MISS_NUM ,
arrival_set_id , p_attributes_rec.arrival_set_id ) ,
ship_model_complete_flag = decode ( p_attributes_rec.ship_model_complete_flag, FND_API.G_MISS_CHAR ,
ship_model_complete_flag , p_attributes_rec.ship_model_complete_flag ) ,
-- Bug 2830372. We update the released status to 'N' only if the released status is in ('R', 'B', 'Y').
-- ATO sets the released status to 'N' only when the reservations are removed for that order line.
-- OM does not pass a released status of 'N', always passes 'R'.
-- Bug 3125768: Checking for l_pickable_flag to update the released_status
released_status = decode(released_status,
'C', released_status,
'D', released_status,
'Y', decode(p_attributes_rec.released_status,
'N', p_attributes_rec.released_status,
released_status),
'X', decode(p_source_code , 'OKE', released_status,
decode(l_pickable_flag, 'N', released_status, 'R') ), -- 5870774
'S', released_status,
'B', decode(p_attributes_rec.released_status,
'N', p_attributes_rec.released_status,
decode(l_pickable_flag, 'Y', released_status, 'X')),
/* bug 2421965: backordered should stay backordered except for ATO reservations: Bug: 2587777 */
decode ( l_pickable_flag, 'N' , 'X',
decode ( p_attributes_rec.released_status, FND_API.G_MISS_CHAR ,
released_status , p_attributes_rec.released_status ))) ,
shipping_instructions = decode ( p_attributes_rec.shipping_instructions, FND_API.G_MISS_CHAR,
shipping_instructions , p_attributes_rec.shipping_instructions ) ,
shipped_quantity = decode ( p_attributes_rec.shipped_quantity, FND_API.G_MISS_NUM ,
shipped_quantity , p_attributes_rec.shipped_quantity ) ,
cycle_count_quantity = decode ( p_attributes_rec.cycle_count_quantity,
FND_API.G_MISS_NUM , decode(p_attributes_rec.shipped_quantity,
FND_API.G_MISS_NUM, cycle_count_quantity,
GREATEST(requested_quantity - p_attributes_rec.shipped_quantity, 0)),
p_attributes_rec.cycle_count_quantity),
-- OPM
shipped_quantity2 = decode ( p_attributes_rec.shipped_quantity2, FND_API.G_MISS_NUM ,
shipped_quantity2 , p_attributes_rec.shipped_quantity2 ) ,
cycle_count_quantity2 = decode ( p_attributes_rec.cycle_count_quantity2,
FND_API.G_MISS_NUM , decode(p_attributes_rec.shipped_quantity2,
FND_API.G_MISS_NUM , cycle_count_quantity2 ,
GREATEST(requested_quantity2 - p_attributes_rec.shipped_quantity2, 0)),
p_attributes_rec.cycle_count_quantity2 ),
currency_code = decode ( p_attributes_rec.currency_code, FND_API.G_MISS_CHAR ,
currency_code , p_attributes_rec.currency_code ) ,
tracking_number = decode(p_attributes_rec.tracking_number, FND_API.G_MISS_CHAR ,
tracking_number , p_attributes_rec.tracking_number ) ,
locator_id = decode(p_attributes_rec.locator_id,
FND_API.G_MISS_NUM, locator_id,
decode(released_status,
'C', locator_id,
p_attributes_rec.locator_id)),
serial_number = decode(p_attributes_rec.serial_number,
FND_API.G_MISS_CHAR, serial_number,
decode(released_status,
'C',serial_number,
p_attributes_rec.serial_number)),
lot_number = decode(p_attributes_rec.lot_number,
FND_API.G_MISS_CHAR, lot_number,
decode(released_status,
'C', lot_number,
p_attributes_rec.lot_number)),
-- OPM
-- HW OPMCONV - Removed sublot code
preferred_grade = decode(p_attributes_rec.preferred_grade,
FND_API.G_MISS_CHAR, preferred_grade,
decode(released_status,
'C', preferred_grade,
'Y', preferred_grade,
p_attributes_rec.preferred_grade)),
revision = decode(p_attributes_rec.revision,
FND_API.G_MISS_CHAR, revision,
decode(released_status,
'C', revision,
p_attributes_rec.revision)),
-- Bug 3125768: changed pickable_flag to l_pickable_flag
subinventory = decode(p_attributes_rec.subinventory,
FND_API.G_MISS_CHAR, subinventory,
decode(released_status,
'Y', decode(l_reservable_flag,
'N', decode(original_subinventory,
p_attributes_rec.subinventory, subinventory,
p_attributes_rec.subinventory),
subinventory),
'S', decode(l_reservable_flag,
'N', decode(l_pickable_flag,
'N', decode(original_subinventory,
p_attributes_rec.subinventory, subinventory,
p_attributes_rec.subinventory),
subinventory),
subinventory),
'C', subinventory,
'X', subinventory,
decode(l_reservable_flag,
'N', decode(original_subinventory,
p_attributes_rec.subinventory, subinventory,
p_attributes_rec.subinventory),
p_attributes_rec.subinventory))),
original_subinventory = decode(p_attributes_rec.subinventory,
FND_API.G_MISS_CHAR, original_subinventory,
decode (released_status,
'C', original_subinventory,
p_attributes_rec.subinventory)),
source_line_number = decode ( p_attributes_rec.line_number, FND_API.G_MISS_CHAR ,
source_line_number , p_attributes_rec.line_number ) ,
master_container_item_id = decode ( p_attributes_rec.master_container_item_id, FND_API.G_MISS_NUM ,
master_container_item_id , p_attributes_rec.master_container_item_id ) ,
detail_container_item_id = decode ( p_attributes_rec.detail_container_item_id, FND_API.G_MISS_NUM ,
detail_container_item_id , p_attributes_rec.detail_container_item_id ) ,
ship_method_code = decode ( l_ship_method_code, FND_API.G_MISS_CHAR ,
ship_method_code , l_ship_method_code ) ,
mode_of_transport = decode ( l_mode_of_transport, FND_API.G_MISS_CHAR ,
mode_of_transport , l_mode_of_transport ) ,
service_level = decode ( l_service_level, FND_API.G_MISS_CHAR ,
service_level , l_service_level ) ,
carrier_id = decode ( l_carrier_id, FND_API.G_MISS_NUM ,
carrier_id , l_carrier_id ) ,
freight_terms_code = decode ( p_attributes_rec.freight_terms_code, FND_API.G_MISS_CHAR ,
freight_terms_code , p_attributes_rec.freight_terms_code ) ,
fob_code = decode ( p_attributes_rec.fob_code, FND_API.G_MISS_CHAR ,
fob_code , p_attributes_rec.fob_code ) ,
customer_item_id = decode ( p_attributes_rec.customer_item_id, FND_API.G_MISS_NUM ,
customer_item_id , p_attributes_rec.customer_item_id ) ,
top_model_line_id = decode ( p_attributes_rec.top_model_line_id, FND_API.G_MISS_NUM ,
top_model_line_id , p_attributes_rec.top_model_line_id ) ,
hold_code = decode ( p_attributes_rec.hold_code, FND_API.G_MISS_CHAR ,
hold_code , p_attributes_rec.hold_code ) ,
inspection_flag = decode ( p_attributes_rec.inspection_flag, FND_API.G_MISS_CHAR ,
inspection_flag , p_attributes_rec.inspection_flag ) ,
src_requested_quantity = decode ( l_oke_full_cancel_flag, 'Y', -- 5870774, Bypass for Non-Canceled dds
src_requested_quantity,
decode ( p_attributes_rec.ordered_quantity, FND_API.G_MISS_NUM ,
src_requested_quantity , p_attributes_rec.ordered_quantity )) ,
src_requested_quantity_uom = decode ( p_attributes_rec.order_quantity_uom, FND_API.G_MISS_CHAR ,
src_requested_quantity_uom , p_attributes_rec.order_quantity_uom ) ,
src_requested_quantity2 = decode ( p_attributes_rec.ordered_quantity2, FND_API.G_MISS_NUM ,
src_requested_quantity2 , p_attributes_rec.ordered_quantity2 ) ,
src_requested_quantity_uom2 = decode ( p_attributes_rec.ordered_quantity_uom2, FND_API.G_MISS_CHAR ,
src_requested_quantity_uom2 , p_attributes_rec.ordered_quantity_uom2 ) ,
attribute_category = decode ( p_attributes_rec.attribute_category, FND_API.G_MISS_CHAR ,
attribute_category , p_attributes_rec.attribute_category ) ,
attribute1 = decode ( p_attributes_rec.attribute1, FND_API.G_MISS_CHAR ,
attribute1 , p_attributes_rec.attribute1 ) ,
attribute2 = decode ( p_attributes_rec.attribute2, FND_API.G_MISS_CHAR ,
attribute2 , p_attributes_rec.attribute2 ) ,
attribute3 = decode ( p_attributes_rec.attribute3, FND_API.G_MISS_CHAR ,
attribute3 , p_attributes_rec.attribute3 ) ,
attribute4 = decode ( p_attributes_rec.attribute4, FND_API.G_MISS_CHAR ,
attribute4 , p_attributes_rec.attribute4 ) ,
attribute5 = decode ( p_attributes_rec.attribute5, FND_API.G_MISS_CHAR ,
attribute5 , p_attributes_rec.attribute5 ) ,
attribute6 = decode ( p_attributes_rec.attribute6, FND_API.G_MISS_CHAR ,
attribute6 , p_attributes_rec.attribute6 ) ,
attribute7 = decode ( p_attributes_rec.attribute7, FND_API.G_MISS_CHAR ,
attribute7 , p_attributes_rec.attribute7 ) ,
attribute8 = decode ( p_attributes_rec.attribute8, FND_API.G_MISS_CHAR ,
attribute8 , p_attributes_rec.attribute8 ) ,
attribute9 = decode ( p_attributes_rec.attribute9, FND_API.G_MISS_CHAR ,
attribute9 , p_attributes_rec.attribute9 ) ,
attribute10 = decode ( p_attributes_rec.attribute10, FND_API.G_MISS_CHAR ,
attribute10 , p_attributes_rec.attribute10 ) ,
attribute11 = decode ( p_attributes_rec.attribute11, FND_API.G_MISS_CHAR ,
attribute11 , p_attributes_rec.attribute11 ) ,
attribute12 = decode ( p_attributes_rec.attribute12, FND_API.G_MISS_CHAR ,
attribute12 , p_attributes_rec.attribute12 ) ,
attribute13 = decode ( p_attributes_rec.attribute13, FND_API.G_MISS_CHAR ,
attribute13 , p_attributes_rec.attribute13 ) ,
attribute14 = decode ( p_attributes_rec.attribute14, FND_API.G_MISS_CHAR ,
attribute14 , p_attributes_rec.attribute14 ) ,
attribute15 = decode ( p_attributes_rec.attribute15, FND_API.G_MISS_CHAR ,
attribute15 , p_attributes_rec.attribute15 ),
cancelled_quantity = decode ( p_attributes_rec.cancelled_quantity, FND_API.G_MISS_NUM ,
cancelled_quantity , p_attributes_rec.cancelled_quantity ),
cancelled_quantity2 = decode ( p_attributes_rec.cancelled_quantity2, FND_API.G_MISS_NUM ,
cancelled_quantity2 , p_attributes_rec.cancelled_quantity2 ) ,
classification = decode ( p_attributes_rec.classification, FND_API.G_MISS_CHAR ,
classification , p_attributes_rec.classification ) ,
commodity_code_cat_id = decode ( p_attributes_rec.commodity_code_cat_id, FND_API.G_MISS_NUM ,
commodity_code_cat_id , p_attributes_rec.commodity_code_cat_id ) ,
container_flag = decode ( p_attributes_rec.container_flag, FND_API.G_MISS_CHAR ,
container_flag , p_attributes_rec.container_flag ) ,
container_name = decode ( p_attributes_rec.container_name, FND_API.G_MISS_CHAR ,
container_name , p_attributes_rec.container_name ) ,
container_type_code = decode ( p_attributes_rec.container_type_code, FND_API.G_MISS_CHAR ,
container_type_code , p_attributes_rec.container_type_code ) ,
country_of_origin = decode ( p_attributes_rec.country_of_origin, FND_API.G_MISS_CHAR ,
country_of_origin , p_attributes_rec.country_of_origin ) ,
delivered_quantity = decode ( p_attributes_rec.delivered_quantity, FND_API.G_MISS_NUM ,
delivered_quantity , p_attributes_rec.delivered_quantity ) ,
delivered_quantity2 = decode ( p_attributes_rec.delivered_quantity2, FND_API.G_MISS_NUM ,
delivered_quantity2 , p_attributes_rec.delivered_quantity2 ) ,
fill_percent = decode ( p_attributes_rec.fill_percent, FND_API.G_MISS_NUM ,
fill_percent , p_attributes_rec.fill_percent ) ,
freight_class_cat_id = decode ( p_attributes_rec.freight_class_cat_id, FND_API.G_MISS_NUM ,
freight_class_cat_id , p_attributes_rec.freight_class_cat_id ) ,
-- Bug 3125768: Checking for l_pickable_flag to update the inv_interfaced_flag
inv_interfaced_flag = decode (inv_interfaced_flag, 'Y',
decode ( p_attributes_rec.inv_interfaced_flag, FND_API.G_MISS_CHAR ,
inv_interfaced_flag , p_attributes_rec.inv_interfaced_flag ) ,
decode (l_pickable_flag, 'N', 'X', 'N')
),
inventory_item_id = decode ( p_attributes_rec.inventory_item_id, FND_API.G_MISS_NUM ,
inventory_item_id , p_attributes_rec.inventory_item_id ),
--bug#6407943 (begin) :Needs to change items org dependent attributes when org changes
item_description = decode(l_change_item_desc,'Y',l_item_description,
decode (p_attributes_rec.item_description, FND_API.G_MISS_CHAR ,
item_description , p_attributes_rec.item_description )),
requested_quantity_uom = decode (l_change_req_quantity_uom,'Y',l_primary_uom_code,
requested_quantity_uom) ,
unit_weight = decode(l_change_unit_weight,'Y',l_unit_weight,unit_weight),
unit_volume = decode (l_change_unit_volume,'Y',l_unit_volume,unit_volume),
net_weight = decode(l_change_weight,'Y',requested_quantity * decode(l_change_unit_weight,'Y',l_unit_weight,unit_weight),
decode ( p_attributes_rec.net_weight, FND_API.G_MISS_NUM ,
net_weight , p_attributes_rec.net_weight )) ,
gross_weight = decode(l_change_weight,'Y',requested_quantity * decode(l_change_unit_weight,'Y',l_unit_weight,unit_weight),
decode ( p_attributes_rec.gross_weight, FND_API.G_MISS_NUM ,
gross_weight , p_attributes_rec.gross_weight )) ,
weight_uom_code = decode(l_change_weight_uom,'Y',l_weight_uom,decode ( p_attributes_rec.weight_uom_code, FND_API.G_MISS_CHAR ,
weight_uom_code , p_attributes_rec.weight_uom_code )) ,
volume = decode(l_change_volume,'Y', requested_quantity * decode (l_change_unit_volume,'Y',l_unit_volume,unit_volume),
decode ( p_attributes_rec.volume, FND_API.G_MISS_NUM ,
volume , p_attributes_rec.volume )) ,
volume_uom_code = decode(l_change_volume_uom,'Y',l_volume_uom,decode ( p_attributes_rec.volume_uom_code, FND_API.G_MISS_CHAR ,
volume_uom_code , p_attributes_rec.volume_uom_code )) ,
hazard_class_id = decode(l_change_haz_class_id,'Y',l_haz_class_id,
decode( p_attributes_rec.hazard_class_id, FND_API.G_MISS_NUM ,
hazard_class_id , p_attributes_rec.hazard_class_id)),
--bug#6407943 (end):Needs to change items org dependent attributes when org changes.
load_seq_number = decode ( p_attributes_rec.load_seq_number, FND_API.G_MISS_NUM ,
load_seq_number , p_attributes_rec.load_seq_number ) ,
lpn_id = decode ( p_attributes_rec.lpn_id, FND_API.G_MISS_NUM ,
lpn_id , p_attributes_rec.lpn_id ) ,
maximum_load_weight = decode ( p_attributes_rec.maximum_load_weight, FND_API.G_MISS_NUM ,
maximum_load_weight , p_attributes_rec.maximum_load_weight ) ,
maximum_volume = decode ( p_attributes_rec.maximum_volume, FND_API.G_MISS_NUM ,
maximum_volume , p_attributes_rec.maximum_volume ) ,
minimum_fill_percent = decode ( p_attributes_rec.minimum_fill_percent, FND_API.G_MISS_NUM ,
minimum_fill_percent , p_attributes_rec.minimum_fill_percent ) ,
move_order_line_id = decode ( p_attributes_rec.move_order_line_id, FND_API.G_MISS_NUM ,
move_order_line_id, p_attributes_rec.move_order_line_id ) ,
movement_id = decode ( p_attributes_rec.movement_id, FND_API.G_MISS_NUM ,
movement_id , p_attributes_rec.movement_id ) ,
mvt_stat_status = decode ( p_attributes_rec.mvt_stat_status, FND_API.G_MISS_CHAR ,
mvt_stat_status , p_attributes_rec.mvt_stat_status ) ,
oe_interfaced_flag = decode ( p_attributes_rec.oe_interfaced_flag, FND_API.G_MISS_CHAR ,
oe_interfaced_flag , p_attributes_rec.oe_interfaced_flag ) ,
org_id = decode ( p_attributes_rec.org_id, FND_API.G_MISS_NUM ,
org_id , p_attributes_rec.org_id ) ,
-- Bug 3125768: changed pickable_flag to l_pickable_flag
pickable_flag = decode ( p_attributes_rec.pickable_flag, FND_API.G_MISS_CHAR ,
l_pickable_flag , p_attributes_rec.pickable_flag ) ,
picked_quantity = decode ( p_attributes_rec.picked_quantity, FND_API.G_MISS_NUM ,
picked_quantity , p_attributes_rec.picked_quantity ),
picked_quantity2 = decode ( p_attributes_rec.picked_quantity2, FND_API.G_MISS_NUM ,
picked_quantity2 , p_attributes_rec.picked_quantity2 ),
project_id = decode ( p_attributes_rec.project_id, FND_API.G_MISS_NUM ,
project_id , p_attributes_rec.project_id ) ,
quality_control_quantity = decode ( p_attributes_rec.quality_control_quantity, FND_API.G_MISS_NUM ,
quality_control_quantity , p_attributes_rec.quality_control_quantity ) ,
quality_control_quantity2 = decode ( p_attributes_rec.quality_control_quantity2, FND_API.G_MISS_NUM ,
quality_control_quantity2 , p_attributes_rec.quality_control_quantity2 ) ,
received_quantity = decode ( p_attributes_rec.received_quantity, FND_API.G_MISS_NUM ,
received_quantity , p_attributes_rec.received_quantity ) ,
received_quantity2 = decode ( p_attributes_rec.received_quantity2, FND_API.G_MISS_NUM ,
received_quantity2 , p_attributes_rec.received_quantity2 ) ,
request_id = decode ( p_attributes_rec.request_id, FND_API.G_MISS_NUM ,
request_id , p_attributes_rec.request_id ) ,
seal_code = decode ( p_attributes_rec.seal_code, FND_API.G_MISS_CHAR ,
seal_code , p_attributes_rec.seal_code ) ,
source_code = decode ( p_attributes_rec.source_code, FND_API.G_MISS_CHAR ,
source_code , p_attributes_rec.source_code ),
source_header_id = decode ( p_attributes_rec.source_header_id, FND_API.G_MISS_NUM ,
source_header_id , p_attributes_rec.source_header_id ) ,
source_header_number = decode ( p_attributes_rec.source_header_number, FND_API.G_MISS_CHAR ,
source_header_number , p_attributes_rec.source_header_number ) ,
source_header_type_id = decode ( p_attributes_rec.source_header_type_id, FND_API.G_MISS_NUM ,
source_header_type_id , p_attributes_rec.source_header_type_id ) ,
source_header_type_name = decode ( p_attributes_rec.source_header_type_name, FND_API.G_MISS_CHAR ,
source_header_type_name , p_attributes_rec.source_header_type_name ) ,
source_line_id = decode ( p_attributes_rec.source_line_id, FND_API.G_MISS_NUM ,
source_line_id , p_attributes_rec.source_line_id ) ,
source_line_set_id = decode ( p_attributes_rec.source_line_set_id, FND_API.G_MISS_NUM ,
source_line_set_id , p_attributes_rec.source_line_set_id ) ,
split_from_delivery_detail_id = decode (p_attributes_rec.split_from_delivery_detail_id, FND_API.G_MISS_NUM,
split_from_delivery_detail_id , p_attributes_rec.split_from_delivery_detail_id ) ,
task_id = decode ( p_attributes_rec.task_id, FND_API.G_MISS_NUM ,
task_id , p_attributes_rec.task_id ) ,
to_serial_number = decode ( p_attributes_rec.to_serial_number, FND_API.G_MISS_CHAR ,
to_serial_number , p_attributes_rec.to_serial_number ) ,
tp_attribute1 = decode ( p_attributes_rec.tp_attribute1, FND_API.G_MISS_CHAR ,
tp_attribute1 , p_attributes_rec.tp_attribute1 ),
tp_attribute10 = decode ( p_attributes_rec.tp_attribute10, FND_API.G_MISS_CHAR ,
tp_attribute10 , p_attributes_rec.tp_attribute10 ) ,
tp_attribute11 = decode ( p_attributes_rec.tp_attribute11, FND_API.G_MISS_CHAR ,
tp_attribute11 , p_attributes_rec.tp_attribute11 ) ,
tp_attribute12 = decode ( p_attributes_rec.tp_attribute12, FND_API.G_MISS_CHAR ,
tp_attribute12 , p_attributes_rec.tp_attribute12 ) ,
tp_attribute13 = decode ( p_attributes_rec.tp_attribute13, FND_API.G_MISS_CHAR ,
tp_attribute13 , p_attributes_rec.tp_attribute13 ) ,
tp_attribute14 = decode ( p_attributes_rec.tp_attribute14, FND_API.G_MISS_CHAR ,
tp_attribute14 , p_attributes_rec.tp_attribute14 ) ,
tp_attribute15 = decode ( p_attributes_rec.tp_attribute15, FND_API.G_MISS_CHAR ,
tp_attribute15 , p_attributes_rec.tp_attribute15 ) ,
tp_attribute2 = decode ( p_attributes_rec.tp_attribute2, FND_API.G_MISS_CHAR ,
tp_attribute2 , p_attributes_rec.tp_attribute2 ) ,
tp_attribute3 = decode ( p_attributes_rec.tp_attribute3, FND_API.G_MISS_CHAR ,
tp_attribute3 , p_attributes_rec.tp_attribute3 ) ,
tp_attribute4 = decode ( p_attributes_rec.tp_attribute4, FND_API.G_MISS_CHAR ,
tp_attribute4 , p_attributes_rec.tp_attribute4 ) ,
tp_attribute5 = decode ( p_attributes_rec.tp_attribute5, FND_API.G_MISS_CHAR ,
tp_attribute5 , p_attributes_rec.tp_attribute5 ) ,
tp_attribute6 = decode ( p_attributes_rec.tp_attribute6, FND_API.G_MISS_CHAR ,
tp_attribute6 , p_attributes_rec.tp_attribute6 ) ,
tp_attribute7 = decode ( p_attributes_rec.tp_attribute7, FND_API.G_MISS_CHAR ,
tp_attribute7 , p_attributes_rec.tp_attribute7 ) ,
tp_attribute8 = decode ( p_attributes_rec.tp_attribute8, FND_API.G_MISS_CHAR ,
tp_attribute8 , p_attributes_rec.tp_attribute8 ) ,
tp_attribute9 = decode ( p_attributes_rec.tp_attribute9, FND_API.G_MISS_CHAR ,
tp_attribute9 , p_attributes_rec.tp_attribute9 ),
tp_attribute_category = decode ( p_attributes_rec.tp_attribute_category, FND_API.G_MISS_CHAR ,
tp_attribute_category , p_attributes_rec.tp_attribute_category ) ,
transaction_temp_id = decode ( p_attributes_rec.transaction_temp_id, FND_API.G_MISS_NUM ,
transaction_temp_id , p_attributes_rec.transaction_temp_id ) ,
unit_number = decode ( p_attributes_rec.unit_number, FND_API.G_MISS_CHAR ,
unit_number , p_attributes_rec.unit_number ) ,
unit_price = decode ( p_attributes_rec.unit_price, FND_API.G_MISS_NUM ,
unit_price , p_attributes_rec.unit_price ),
/* J TP Release */
earliest_pickup_date = decode(tpdates_changed, 'N' ,
to_date(to_char(earliest_pickup_date,'mm-dd-yy HH24:MI:SS'),'mm-dd-yy HH24:MI:SS')
, p_changed_detail.earliest_pickup_date ),
latest_pickup_date = decode(tpdates_changed, 'N' ,
to_date(to_char(latest_pickup_date,'mm-dd-yy HH24:MI:SS'),'mm-dd-yy HH24:MI:SS')
, p_changed_detail.latest_pickup_date ),
earliest_dropoff_date = decode(tpdates_changed, 'N' ,
to_date(to_char(earliest_dropoff_date,'mm-dd-yy HH24:MI:SS'),'mm-dd-yy HH24:MI:SS')
, p_changed_detail.earliest_dropoff_date ),
latest_dropoff_date = decode(tpdates_changed, 'N' ,
to_date(to_char(latest_dropoff_date,'mm-dd-yy HH24:MI:SS'),'mm-dd-yy HH24:MI:SS')
, p_changed_detail.latest_dropoff_date),
--OTM R12 Org-Specific ( Changes made for update to ignore_for_planning column ).
--Update the field only if organization_id is changed.
ignore_for_planning = decode(organization_id, l_orgid, ignore_for_planning,
nvl(l_ignore_for_planning,nvl(ignore_for_planning,'N'))),
-- Bug 3244272 : Added laste_update_date and last_updated_by in the
-- update statement.
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE source_code = p_source_code
AND source_line_id = p_attributes_rec.source_line_id
AND container_flag = 'N'
AND delivery_detail_id = decode( p_attributes_rec.delivery_detail_id, FND_API.G_MISS_NUM ,
delivery_detail_id, p_attributes_rec.delivery_detail_id );
WSH_INTEGRATION.DBI_Update_Detail_Log
(p_delivery_detail_id_tab => l_details_marked,
p_dml_type => 'UPDATE',
x_return_status => l_dbi_rs);
WHEN Update_Failed THEN
FND_MESSAGE.Set_Name('WSH', 'WSH_INVALID_LOCATION');
WSH_DEBUG_SV.logmsg(l_module_name,'UPDATE_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:UPDATE_FAILED');
wsh_util_core.default_handler('WSH_USA_ACTIONS.Update_Attributes',l_module_name);
END Update_Attributes;
SELECT hazard_class_id, primary_uom_code, weight_uom_code,
unit_weight, volume_uom_code, unit_volume , decode(mtl_transactions_enabled_flag,'Y','Y','N')
FROM mtl_system_items m
WHERE m.inventory_item_id = c_p_inventory_item_id
AND m.organization_id = c_p_organization_id;
SELECT lot_id
FROM ic_lots_mst
WHERE item_id = c_opm_item_id
AND lot_no = c_opm_lot_number
AND sublot_no = c_opm_sublot_number;
SELECT
HEADER_ID,
HEADER_NUMBER,
HEADER_TYPE_ID,
HEADER_TYPE_NAME,
LINE_ID,
LINE_NUMBER,
ORG_ID,
SOLD_TO_ORG_ID,
INVENTORY_ITEM_ID,
ITEM_DESCRIPTION,
SHIP_FROM_ORG_ID,
SUBINVENTORY,
SHIP_TO_ORG_ID,
DELIVER_TO_ORG_ID,
SHIP_TO_CONTACT_ID,
DELIVER_TO_CONTACT_ID,
INTMED_SHIP_TO_ORG_ID,
INTMED_SHIP_TO_CONTACT_ID,
SHIP_TOLERANCE_ABOVE,
SHIP_TOLERANCE_BELOW,
ORDERED_QUANTITY,
SHIPPED_QUANTITY,
DELIVERED_QUANTITY,
ORDER_QUANTITY_UOM,
SHIPPING_QUANTITY_UOM,
SHIPPING_QUANTITY,
DATE_SCHEDULED,
SHIPPING_METHOD_CODE,
FREIGHT_CARRIER_CODE,
FREIGHT_TERMS_CODE,
SHIPMENT_PRIORITY_CODE,
FOB_CODE,
ITEM_IDENTIFIER_TYPE,
ORDERED_ITEM_ID,
DATE_REQUESTED,
DEP_PLAN_REQUIRED_FLAG,
CUSTOMER_PROD_SEQ_NUMBER,
CUSTOMER_DOCK_CODE,
SHIPPING_INTERFACED_FLAG,
SHIP_SET_ID,
ATO_LINE_ID,
SHIP_MODEL_COMPLETE_FLAG,
TOP_MODEL_LINE_ID,
ITEM_TYPE_CODE,
CUST_PO_NUMBER,
ARRIVAL_SET_ID,
SOURCE_TYPE_CODE,
LINE_TYPE_ID,
PROJECT_ID,
TASK_ID,
SHIPPING_INSTRUCTIONS,
PACKING_INSTRUCTIONS,
MASTER_CONTAINER_ITEM_ID,
DETAIL_CONTAINER_ITEM_ID,
PREFERRED_GRADE,
ORDERED_QUANTITY2,
ORDERED_QUANTITY_UOM2,
UNIT_LIST_PRICE,
TRANSACTIONAL_CURR_CODE,
END_ITEM_UNIT_NUMBER,
TP_CONTEXT,
TP_ATTRIBUTE1,
TP_ATTRIBUTE2,
TP_ATTRIBUTE3,
TP_ATTRIBUTE4,
TP_ATTRIBUTE5,
TP_ATTRIBUTE6,
TP_ATTRIBUTE7,
TP_ATTRIBUTE8,
TP_ATTRIBUTE9,
TP_ATTRIBUTE10,
TP_ATTRIBUTE11,
TP_ATTRIBUTE12,
TP_ATTRIBUTE13,
TP_ATTRIBUTE14,
TP_ATTRIBUTE15,
SOLD_TO_CONTACT_ID,
CUSTOMER_JOB,
CUSTOMER_PRODUCTION_LINE,
CUST_MODEL_SERIAL_NUMBER,
LINE_SET_ID,
/* J TP Release */
latest_acceptable_date,
promise_date,
schedule_arrival_date,
earliest_acceptable_date,
earliest_ship_date, --equivalent of demand_satisfaction_date in TP
order_date_type_code,
source_document_type_id
from oe_delivery_lines_v
where ship_from_org_id is not NULL
and order_quantity_uom is not NULL
and ordered_quantity is not NULL;
SELECT
HEADER_ID,
HEADER_NUMBER,
HEADER_TYPE_ID,
HEADER_TYPE_NAME,
LINE_ID,
LINE_NUMBER,
ORG_ID,
SOLD_TO_ORG_ID,
INVENTORY_ITEM_ID,
ITEM_DESCRIPTION,
SHIP_FROM_ORG_ID,
SUBINVENTORY,
SHIP_TO_ORG_ID,
DELIVER_TO_ORG_ID,
SHIP_TO_CONTACT_ID,
DELIVER_TO_CONTACT_ID,
INTMED_SHIP_TO_ORG_ID,
INTMED_SHIP_TO_CONTACT_ID,
SHIP_TOLERANCE_ABOVE,
SHIP_TOLERANCE_BELOW,
ORDERED_QUANTITY,
SHIPPED_QUANTITY,
DELIVERED_QUANTITY,
ORDER_QUANTITY_UOM,
SHIPPING_QUANTITY_UOM,
SHIPPING_QUANTITY,
DATE_SCHEDULED,
SHIPPING_METHOD_CODE,
FREIGHT_CARRIER_CODE,
FREIGHT_TERMS_CODE,
SHIPMENT_PRIORITY_CODE,
FOB_CODE,
ITEM_IDENTIFIER_TYPE,
ORDERED_ITEM_ID,
DATE_REQUESTED,
DEP_PLAN_REQUIRED_FLAG,
CUSTOMER_PROD_SEQ_NUMBER,
CUSTOMER_DOCK_CODE,
SHIPPING_INTERFACED_FLAG,
SHIP_SET_ID,
ATO_LINE_ID,
SHIP_MODEL_COMPLETE_FLAG,
TOP_MODEL_LINE_ID,
ITEM_TYPE_CODE,
CUST_PO_NUMBER,
ARRIVAL_SET_ID,
SOURCE_TYPE_CODE,
LINE_TYPE_ID,
PROJECT_ID,
TASK_ID,
SHIPPING_INSTRUCTIONS,
PACKING_INSTRUCTIONS,
MASTER_CONTAINER_ITEM_ID,
DETAIL_CONTAINER_ITEM_ID,
PREFERRED_GRADE,
ORDERED_QUANTITY2,
ORDERED_QUANTITY_UOM2,
UNIT_LIST_PRICE,
TRANSACTIONAL_CURR_CODE,
END_ITEM_UNIT_NUMBER,
TP_CONTEXT,
TP_ATTRIBUTE1,
TP_ATTRIBUTE2,
TP_ATTRIBUTE3,
TP_ATTRIBUTE4,
TP_ATTRIBUTE5,
TP_ATTRIBUTE6,
TP_ATTRIBUTE7,
TP_ATTRIBUTE8,
TP_ATTRIBUTE9,
TP_ATTRIBUTE10,
TP_ATTRIBUTE11,
TP_ATTRIBUTE12,
TP_ATTRIBUTE13,
TP_ATTRIBUTE14,
TP_ATTRIBUTE15,
SOLD_TO_CONTACT_ID,
CUSTOMER_JOB,
CUSTOMER_PRODUCTION_LINE,
CUST_MODEL_SERIAL_NUMBER,
LINE_SET_ID,
latest_acceptable_date,
promise_date,
schedule_arrival_date,
earliest_acceptable_date,
earliest_ship_date,
order_date_type_code,
source_document_type_id
from oe_delivery_lines_v
WHERE line_id = p_source_line_id
and p_source_line_id IS NOT NULL
and ship_from_org_id is not NULL
and order_quantity_uom is not NULL
and ordered_quantity is not NULL;
SELECT line_id
FROM oe_order_lines_all
--FROM oe_delivery_lines_v
WHERE line_id = c_line_id
and nvl(shipping_interfaced_flag, 'N') = 'N'
FOR UPDATE NOWAIT;
SELECT freight_class_cat_set_id, commodity_code_cat_set_id, enforce_ship_set_and_smc --2373131
FROM wsh_shipping_parameters
WHERE organization_id = c_organization_id;
select set_name
from oe_sets
where set_id = c_set_id;
SELECT category_id
FROM mtl_item_categories
WHERE inventory_item_id = c_inventory_item_id
AND organization_id = c_organization_id
AND category_set_id = c_category_set_id;
/* Replace process order with update_shipping_interface API for better
performance
*/
IF l_debug_on THEN
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit OE_SHIPPING_INTEGRATION_PUB.UPDATE_SHIPPING_INTERFACE',WSH_DEBUG_SV.C_PROC_LEVEL);
OE_Shipping_Integration_PUB.Update_Shipping_Interface(
p_api_version_number => 1.0,
p_line_id => ln_rec_info.line_id,
p_shipping_interfaced_flag => 'Y',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR: OE_SHIPPING_INTEGRATION_PUB.UPDATE_SHIPPING_INTERFACE RETURNED ' || L_RETURN_STATUS );
txt=>'Update_Shipping_Interface failed for line:' || ln_rec_info.line_id );
WSH_DEBUG_SV.logmsg(l_module_name, 'OE_SHIPPING_INTEGRATION_PUB.UPDATE_SHIPPING_INTERFACE RETURNED ' || L_RETURN_STATUS );