[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_last_update_date DATE;
g_last_updated_by NUMBER;
g_last_update_login NUMBER;
fnd_msg_pub.delete_msg(p_msg_index=>i);
debug('Deleted message at position: ' || i,'process_mobile_msg');
SELECT wt.trip_id
,wt.name
,wt.vehicle_item_id
,msi.concatenated_segments
,wt.vehicle_num_prefix
,wt.vehicle_number
,wts.departure_seal_code
,WMS_DIRECT_SHIP_PVT.GET_ENFORCE_SHIP
,WMS_DIRECT_SHIP_PVT.GET_SHIPMETHOD_MEANING(wt.ship_method_code)
,wt.ship_method_code
FROM wsh_trips_ob_grp_v wt
,wsh_trip_stops_ob_grp_v wts
,mtl_system_items_kfv msi
WHERE wt.trip_id = p_trip_id
AND wt.trip_id = wts.trip_id
AND (wt.vehicle_item_id = msi.inventory_item_id(+)
AND msi.organization_id(+) = p_org_id)
AND ROWNUM < 2;
SELECT wnd.delivery_id
,wnd.name
,WMS_DIRECT_SHIP_PVT.GET_DELIVERY_LPN(wnd.delivery_id)
,nvl(wnd.net_weight, 0)
,nvl(wnd.gross_weight, 0)
,wnd.weight_uom_code
,wnd.waybill
,WMS_DIRECT_SHIP_PVT.GET_SHIPMETHOD_MEANING(wnd.ship_method_code)
,wnd.ship_method_code
,WMS_DIRECT_SHIP_PVT.GET_FOBLOC_CODE_MEANING(wnd.fob_code)
,wnd.fob_location_id
,WMS_DIRECT_SHIP_PVT.GET_FOB_LOCATION(wnd.fob_location_id)
,wnd.freight_terms_code
,WMS_DIRECT_SHIP_PVT.GET_FREIGHT_TERM(wnd.freight_terms_code)
,WMS_DIRECT_SHIP_PVT.GET_FOB_LOCATION(wnd.INTMED_SHIP_TO_LOCATION_ID)
,WMS_DIRECT_SHIP_PVT.GET_BOL(wnd.delivery_id)
,nvl(wnd.status_code,'OP')
,WMS_DIRECT_SHIP_PVT.GET_ENFORCE_SHIP
,wts1.trip_id --2767767
FROM wsh_new_deliveries_ob_grp_v wnd,
-- 2767767
(SELECT wdl.delivery_id,wts.trip_id
FROM wsh_delivery_legs_ob_grp_v wdl,
wsh_trip_stops_ob_grp_v wts
WHERE wdl.delivery_id=p_delivery_id
AND wdl.pick_up_stop_id=wts.stop_id
AND ROWNUM=1) wts1 -- end 2767767
WHERE wnd.delivery_id = p_delivery_id
and wnd.delivery_id=wts1.delivery_id(+); --2767767
SELECT wts.trip_id,wt.name
INTO l_trip_id,l_trip_name
FROM wsh_delivery_legs_ob_grp_v wdl,
wsh_trip_stops_ob_grp_v wts,
wsh_trips_ob_grp_v wt
WHERE wdl.delivery_id=p_delivery_id
AND wdl.pick_up_stop_id=wts.stop_id
AND wt.trip_id=wts.trip_id
AND ROWNUM=1;
UPDATE wms_shipping_transaction_temp
SET trip_id=l_trip_id,trip_name=l_trip_name
WHERE delivery_id=p_delivery_id and trip_id is null;
debug('trip_id update on wstt failed','get_delivery_info');
SELECT distinct license_plate_number
FROM wms_shipping_transaction_temp wstt
,wms_license_plate_numbers wlpn
WHERE wstt.delivery_id = p_delivery_id
AND wstt.outermost_lpn_id = wlpn.lpn_id
AND wstt.direct_ship_flag = 'Y';
SELECT meaning
INTO l_ship_method_meaning
FROM fnd_lookup_values_vl
WHERE lookup_type = 'SHIP_METHOD'
AND view_application_id = 3
AND lookup_code = p_ship_method_code;
SELECT meaning
INTO l_fob_loc_meaning
FROM ar_lookups
WHERE lookup_type = 'FOB'
AND SYSDATE BETWEEN nvl(start_date_active,sysdate) AND nvl(end_date_active,sysdate)
AND enabled_flag = 'Y'
AND lookup_code = p_fob_code;
SELECT description
INTO l_fob_location
FROM wsh_hr_locations_v
WHERE location_id = p_fob_location_id;
SELECT freight_terms
INTO l_freight_term
FROM oe_frght_terms_active_v
WHERE freight_terms_code = p_freight_term_code;
SELECT wdi.SEQUENCE_NUMBER
INTO l_BOL
FROM wsh_document_instances wdi
,wsh_delivery_legs_ob_grp_v wdl
WHERE wdl.delivery_id = p_delivery_id
AND wdi.entity_id = wdl.delivery_leg_id
AND wdi.entity_name = 'WSH_DELIVERY_LEGS'
AND rownum < 2;
SELECT milk.concatenated_segments
, wstt.outermost_lpn
FROM mtl_item_locations_kfv milk
,wms_shipping_transaction_temp wstt
WHERE wstt.delivery_id = p_delivery_id
AND wstt.organization_id = p_org_id
AND wstt.dock_appoint_flag = 'N'
AND wstt.direct_ship_flag = 'Y'
AND wstt.dock_door_id <> p_dock_door_id
AND milk.organization_id = p_org_id
AND milk.inventory_location_id =wstt.dock_door_id;
SELECT name
INTO l_del_name
FROM wsh_new_deliveries_ob_grp_v
WHERE delivery_id =p_delivery_id;
SELECT 1
INTO temp_val
FROM wsh_new_deliveries_ob_grp_v
WHERE delivery_id = p_delivery_id
FOR UPDATE NOWAIT;
SELECT name
INTO x_delivery_name
FROM wsh_new_deliveries_ob_grp_v
WHERE delivery_id = p_delivery_id;
PROCEDURE UPDATE_DELIVERY(
x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_delivery_id IN NUMBER
,p_net_weight IN NUMBER
,p_gross_weight IN NUMBER
,p_wt_uom_code IN VARCHAR2
,p_waybill IN VARCHAR2
,p_ship_method_code IN VARCHAR2
,p_fob_code IN VARCHAR2
,p_fob_location_id IN NUMBER
,p_freight_term_code IN VARCHAR2
,p_freight_term_name IN VARCHAR2
,p_intmed_shipto_loc_id IN NUMBER
)IS
l_init_msg_list VARCHAR2(1) :=FND_API.G_TRUE;
SELECT ROWID
,FREIGHT_COST_TYPE_ID
,CURRENCY_CODE
,FREIGHT_AMOUNT
,CONVERSION_TYPE
FROM WMS_FREIGHT_COST_TEMP
WHERE delivery_id = p_delivery_id
AND FREIGHT_COST_ID IS NULL
FOR UPDATE OF FREIGHT_COST_ID;
SELECT status_code
INTO l_status_code
FROM wsh_new_deliveries_ob_grp_v
WHERE delivery_id = p_delivery_id;
debug('In Update delivery procedure ','update_Delivery');
DEBUG('p_delivery_id : '||p_delivery_id,'update_Delivery');
DEBUG('p_ship_method_code : '||p_ship_method_code,'update_Delivery');
DEBUG('p_FREIGHT_TERM_CODE : '||p_FREIGHT_TERM_CODE,'update_Delivery');
DEBUG('p_fob_code : '||p_fob_code,'update_Delivery');
DEBUG('p_fob_location_id : '||p_fob_location_id,'update_Delivery');
DEBUG('p_waybill : '||p_waybill,'update_Delivery');
DEBUG('p_net_weight : '||p_net_weight,'update_Delivery');
DEBUG('p_gross_weight : '||p_gross_weight,'update_Delivery');
DEBUG('p_wt_uom_code : '||p_wt_uom_code,'update_Delivery');
DEBUG('p_intmed_shipto_loc_id : '||p_intmed_shipto_loc_id,'update_Delivery');
SELECT name
INTO l_del_name
FROM wsh_new_deliveries_ob_grp_v
WHERE delivery_id = p_delivery_id;
WSH_FREIGHT_COSTS_PUB.Create_Update_Freight_Costs (
p_api_version_number =>1.0
, p_init_msg_list =>l_init_msg_list
, p_commit => FND_API.G_FALSE
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_pub_freight_costs => l_freight_cost_rec
, p_action_code => 'CREATE'
, x_freight_cost_id => l_freight_cost_id
);
debug('Update of Freight Cost for Del is failed ','UPDATE_DELIVERY');
debug('Update of Freight Cost for Del is succ ','UPDATE_DELIVERY');
UPDATE WMS_FREIGHT_COST_TEMP
SET freight_cost_id = l_freight_cost_id,
last_update_date= SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID
WHERE rowid = l_row_id;
SELECT distinct wts.trip_id
INTO l_trip_id
FROM wsh_delivery_legs_ob_grp_v wdl,
wsh_trip_stops_ob_grp_v wts
WHERE wdl.delivery_id=p_delivery_id
AND wdl.pick_up_stop_id=wts.stop_id
AND rownum=1;
debug('Delivery '||p_delivery_id||' is assigned to trip '||l_trip_id,'Update Delivery');
||l_trip_id ||' and hence not updating ship method','Update Delivery');
WSH_DELIVERIES_PUB.Create_Update_Delivery(
p_api_version_number => 1.0
,p_init_msg_list => l_init_msg_list
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_action_code => 'UPDATE'
,p_delivery_info => l_delivery_info
,p_delivery_name => l_delivery_name
,x_delivery_id => l_delivery_id
,x_name => l_name);
debug('Create _Update_Delivery has errored ','UPDATE_DELIVERY');
FND_MESSAGE.SET_NAME('WMS','WMS_UPDATE_DELIVERY_FAILED');
FND_MESSAGE.SET_NAME('WMS','UPDATE_DELIVERY_FAILED');
debug('Update delivery completed successfully');
debug('Update of Delivery has failed :Unexpected Error','Update Delivery');
debug('Update of Delivery has failed : Unexpected Error '||SQLERRM,'Update_Delivery');
debug(SQLCODE,'Update_Delivery');
END; -- UPDATE_DELIVERY
select wnd.name,
wdd.delivery_detail_id,
wdd.inventory_item_id,
wdd.requested_quantity,
msik.concatenated_segments,
msik.description
from wsh_delivery_details_ob_grp_v wdd
,wsh_delivery_assignments_v wda
,wsh_new_deliveries_ob_grp_v wnd
,mtl_system_items_kfv msik
where wnd.delivery_id = p_delivery_id
AND wnd.delivery_id = wda.delivery_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.lpn_id is null
AND wdd.inventory_item_id = msik.inventory_item_id
AND wdd.organization_id = msik.organization_id
AND ((wda.parent_delivery_detail_id is null
AND msik.mtl_transactions_enabled_flag <> 'N')
OR wdd.released_status is null
OR wdd.released_status NOT IN ('X', 'Y'));
open x_missing_item_cur FOR select 1 from dual;
select wt.trip_id , wt.carrier_id, wt.ship_method_code, wt.mode_of_transport,
wt.tp_plan_name -- glog proj
from wsh_delivery_legs wdl, wsh_trip_stops wts, wsh_trips wt
where wdl.pick_up_stop_id=wts.stop_id
and wdl.delivery_id=v_del_id
and wts.trip_id=wt.trip_id;
SELECT status_code,NAME,ignore_for_planning, tms_interface_flag
FROM wsh_new_deliveries_ob_grp_v
WHERE delivery_id = p_delivery_id;
WMS_DIRECT_SHIP_PVT.UPDATE_DELIVERY(
x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_delivery_id => p_delivery_id
,p_net_weight => p_net_weight
,p_gross_weight => p_gross_weight
,p_wt_uom_code => p_wt_uom_code
,p_waybill => p_waybill
,p_ship_method_code => p_ship_method_code
,p_fob_code => p_fob_code
,p_fob_location_id => p_fob_location_id
,p_freight_term_code => p_freight_term_code
,p_freight_term_name => p_freight_term_name
,p_intmed_shipto_loc_id => p_intmed_shipto_loc_id
);
DEBUG('Update Delivery API failed with status E ','SHIP_CONFIRM');
DEBUG('Update Delivery API failed with status U','SHIP_CONFIRM');
/* Note: We do not need this update to ignore for planning here again becs things are taken care of during
the stage_lpn() API. At this time, we will always have the delivery - either existing or created in stage_LPN()
there is no check on exception severity in stage_lpn().
-- Glog Changes
IF WSH_UTIL_CORE.GC3_IS_INSTALLED = 'Y' AND nvl(l_ignore_for_planning, 'N') = 'N' THEN
l_otm_trip_id := 0;
l_msg_table.delete;
SELECT DISTINCT WSTT.delivery_id
FROM WMS_SHIPPING_TRANSACTION_TEMP wstt
,WSH_NEW_DELIVERIES_OB_GRP_V WND
WHERE wstt.organization_id = p_org_id
AND wstt.dock_door_id = p_dock_door_id
AND wstt.dock_appoint_flag = 'N'
AND nvl(wstt.direct_ship_flag,'N') = 'Y'
AND wstt.delivery_id = wnd.delivery_id
AND wnd.status_code = 'OP';
SELECT DELIVERY_ID FROM WMS_SHIPPING_TRANSACTION_TEMP
WHERE DELIVERY_ID IS NOT NULL
AND TRIP_ID IS NULL
AND ORGANIZATION_ID=P_ORG_ID
AND DOCK_DOOR_ID=P_DOCK_DOOR_ID
AND NVL(DIRECT_SHIP_FLAG,'N')='Y';
WMS_DIRECT_SHIP_PVT.UPDATE_DELIVERY(
x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_delivery_id => p_delivery_id
,p_net_weight => p_net_weight
,p_gross_weight => p_gross_weight
,p_wt_uom_code => p_wt_uom_code
,p_waybill => p_waybill
,p_ship_method_code => p_ship_method_code
,p_fob_code => p_fob_code
,p_fob_location_id => p_fob_location_id
,p_freight_term_code => p_freight_term_code
,p_freight_term_name => p_freight_term_name
,p_intmed_shipto_loc_id => p_intmed_shipto_loc_id
);
debug('Return Status from Update_Delivery: '||l_return_status, ' Confirm_All_Deliveries');
debug('Update Delivery API failed with status E ','Confirm_All_deliveries');
debug('Update Delivery API failed with status U','Confirm_All_Deliveries');
SELECT wts.trip_id,wt.name
INTO l_trip_id,l_trip_name
FROM wsh_delivery_legs_ob_grp_v wdl,
wsh_trip_stops_ob_grp_v wts,
wsh_trips_ob_grp_v wt
WHERE wdl.delivery_id=l_deliveries.delivery_id
AND wdl.pick_up_stop_id=wts.stop_id
AND wt.trip_id=wts.trip_id
AND ROWNUM=1;
UPDATE wms_shipping_transaction_temp
SET trip_id=l_trip_id,trip_name=l_trip_name
WHERE delivery_id=l_deliveries.delivery_id AND trip_id IS NULL;
debug('trip_id update on wstt failed','CONFIRM_ALL_DELIVERIES');
debug('trip_id update on wstt failed','CONFIRM_ALL_DELIVERIES');
SELECT wts.trip_id
INTO l_trip_id
FROM wsh_delivery_legs_ob_grp_v wdl, wsh_trip_stops_ob_grp_v wts
WHERE wdl.delivery_id = p_delivery_id --l_delivery_id /* bug 2741857 */
and wdl.pick_up_stop_id = wts.stop_id;
UPDATE wms_shipping_transaction_temp
SET trip_id = l_trip_id,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID
WHERE delivery_id = p_delivery_id;--l_delivery_id; /* bug 2741857 */
SELECT wt.trip_id
INTO l_chk_trip_id
FROM WMS_SHIPPING_TRANSACTION_TEMP wstt
,WSH_TRIPS_OB_GRP_V wt
WHERE
wt.trip_id = wstt.trip_id
AND organization_id = p_organization_id
AND status_code = 'OP'
AND dock_door_id = p_dock_door_id
AND dock_appoint_flag = 'N'
AND nvl(direct_ship_flag,'N') = 'Y'
AND ROWNUM =1;
debug('UPDATE WSTT with the trip_id assigned','CREATE_TRIP');
UPDATE WMS_SHIPPING_TRANSACTION_TEMP
SET trip_id = l_chk_trip_id
WHERE delivery_id = l_del_tab(i);
debug('UPDATE WSTT with the trip created','CREATE_TRIP');
UPDATE WMS_SHIPPING_TRANSACTION_TEMP
SET trip_id = l_trip_id
WHERE delivery_id = l_del_tab(k);
debug('UPDATE WSTT with the trip for failed assignments','CREATE_TRIP');
UPDATE WMS_SHIPPING_TRANSACTION_TEMP
SET trip_id = l_trip_id
WHERE delivery_id = l_auto_trip_del(j);
PROCEDURE UPDATE_TRIPSTOP(
x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_trip_id IN NUMBER
,p_vehicle_item_id IN NUMBER
,p_vehicle_num_prefix IN VARCHAR2
,p_vehicle_num IN VARCHAR2
,p_seal_code IN VARCHAR2
,p_org_id IN NUMBER DEFAULT NULL
,p_dock_door_id IN NUMBER DEFAULT NULL
,p_ship_method_code IN VARCHAR2 DEFAULT NULL)
IS
l_init_msg_list VARCHAR2(1) :=FND_API.G_TRUE;
SELECT ROWID
,FREIGHT_COST_TYPE_ID
,CURRENCY_CODE
,FREIGHT_AMOUNT
,CONVERSION_TYPE
FROM WMS_FREIGHT_COST_TEMP
WHERE TRIP_ID = p_trip_id
AND FREIGHT_COST_ID IS NULL
FOR UPDATE OF FREIGHT_COST_ID NOWAIT;
SELECT 'x'
FROM wsh_trip_stops_ob_grp_v
WHERE trip_id = p_trip_id
FOR UPDATE NOWAIT;
SELECT 'x'
FROM wsh_trips_ob_grp_v
WHERE trip_id = p_trip_id
FOR UPDATE NOWAIT;
debug('Begin UPDATE_TRIPSTOP ','UPDATE_TRIPSTOP');
debug('p_trip_id : ' || p_trip_id , 'UPDATE_TRIPSTOP');
debug('p_vehicle_item_id : ' || p_vehicle_item_id , 'UPDATE_TRIPSTOP');
debug('p_vehicle_num_prefix : ' || p_vehicle_num_prefix , 'UPDATE_TRIPSTOP');
debug('p_vehicle_num : ' || p_vehicle_num , 'UPDATE_TRIPSTOP');
debug('p_seal_code : ' || p_seal_code , 'UPDATE_TRIPSTOP');
debug('p_org_id : ' || p_org_id , 'UPDATE_TRIPSTOP');
debug('p_dock_door_id : ' || p_dock_door_id , 'UPDATE_TRIPSTOP');
debug('p_ship_method_code : ' || p_ship_method_code , 'UPDATE_TRIPSTOP');
debug('In the procedure update trip_stop','trip_Stop');
debug('Update Trip Ship method:'||p_ship_method_code,'Update_tripstop');
debug('Update Trip No Trip Id is passed','Update_tripstop');
SELECT name
INTO l_name
FROM wsh_trips_ob_grp_v
WHERE trip_id=l_trip_id;
WSH_FREIGHT_COSTS_PUB.Create_Update_Freight_Costs
(p_api_version_number =>1.0
, p_init_msg_list =>l_init_msg_list
, p_commit => FND_API.G_FALSE
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_DATA
, p_pub_freight_costs => l_freight_cost_rec
, p_action_code => 'CREATE'
, x_freight_cost_id => l_freight_cost_id
);
debug('Create_Update Freight Cost API failed with status E ','Update Trip Stop');
debug('Update Freight Cost failed with status U','Update TripStop');
UPDATE WMS_FREIGHT_COST_TEMP
SET freight_cost_id = l_freight_cost_id
,last_update_date = SYSDATE
,last_updated_by = FND_GLOBAL.USER_ID
WHERE rowid = l_row_id;
SELECT stop_id
INTO l_stop_id
FROM WSH_TRIP_STOPS_OB_GRP_V wts, WSH_DELIVERY_LEGS_OB_GRP_V wdl
-- WHERE wts.trip_id = p_trip_id
WHERE wts.trip_id=l_trip_id
AND wts.stop_id = wdl.pick_up_stop_id
AND ROWNUM <2;
debug('Call WSH_TRIP_STOPS_PUB.Create_Update_Stop to Update the Seal Code','Update Trip Stop');
WSH_TRIP_STOPS_PUB.Create_Update_Stop(
p_api_version_number => 1.0
,p_init_msg_list => l_init_msg_list
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_action_code => 'UPDATE'
,p_stop_info => l_stop_info
,p_trip_id => l_trip_id
,x_stop_id => l_stop_id);
debug('Create_Update_Stop API failed with status E ','Update Trip Stop');
debug('Create_Update_Stop failed with status U','Update TripStop');
END IF; -- Update Stop
debug('Update Trip also ','Update_Trip_Stop');
p_trip_info.last_update_date := SYSDATE;
p_trip_info.last_updated_by :=FND_GLOBAL.USER_ID;
p_trip_info.last_update_login := FND_GLOBAL.USER_ID;
debug('Call to Create_update_Trip','Update_Trip_Stop');
WSH_TRIPS_PUB.Create_Update_Trip (
p_api_version_number => 1.0
,p_init_msg_list => l_init_msg_list
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_action_code => 'UPDATE'
,p_trip_info => p_trip_info
,x_trip_id => l_trip_id
,x_trip_name => l_trip_name);
debug('Create_Update_Trip API failed with status E ','Update Trip Stop');
debug('Create_Update_Trip failed with status U','Update TripStop');
debug('In exception (E) ','Update_Trip_Stop');
debug('Update of Trip Stop has failed :Unexpected Error','Update_Trip_Stop');
debug('Update of Trip has failed : Unexpected Error '||SQLERRM,'Update_trip_Stop');
debug(SQLCODE,'Update_Trip_Stop');
SELECT wdl.pick_up_stop_id pick_up_stop_id
FROM wsh_delivery_legs_ob_grp_v wdl, wsh_trip_stops_ob_grp_v wts
WHERE wts.trip_id = p_trip_id
AND wdl.pick_up_stop_id = wts.stop_id;
SELECT delivery_id
FROM wsh_delivery_legs_ob_grp_v wdl, wsh_trip_stops_ob_grp_v wts
WHERE wts.trip_id = p_trip_id
AND wdl.pick_up_stop_id = wts.stop_id;
wms_direct_ship_pvt.update_tripstop(
x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_trip_id => p_trip_id
, p_vehicle_item_id => p_vehicle_item_id
, p_vehicle_num_prefix => p_vehicle_num_prefix
, p_vehicle_num => p_vehicle_num
, p_seal_code => p_seal_code
, p_org_id => p_org_id
, p_dock_door_id => p_dock_door_id
, p_ship_method_code => p_ship_method_code
);
DEBUG('Update tripstop API failed with status E ', 'Print_shipping_dcouments');
DEBUG('Update tripstop API failed with status U ', 'Print_shipping_dcouments');
SELECT delivery_report_set_id
INTO l_report_set_id
FROM wsh_shipping_parameters
WHERE organization_id = p_org_id
AND ROWNUM < 2;
fnd_msg_pub.delete_msg(i);
SELECT DISTINCT milk.concatenated_segments
, wstt.outermost_lpn
FROM mtl_item_locations_kfv milk, wms_shipping_transaction_temp wstt
WHERE wstt.trip_id = p_trip_id
AND wstt.organization_id = p_org_id
AND wstt.dock_appoint_flag = 'N'
AND wstt.direct_ship_flag = 'Y'
AND wstt.dock_door_id <> p_dock_door_id
AND milk.organization_id = p_org_id
AND milk.inventory_location_id = wstt.dock_door_id;
SELECT DISTINCT wstt.trip_id
FROM wms_shipping_transaction_temp wstt, wsh_trips_ob_grp_v wt
WHERE wstt.organization_id = p_org_id
AND wstt.dock_door_id = p_dock_door_id
AND wt.trip_id = wstt.trip_id
AND wt.status_code IN('CL', 'IT');
SELECT DISTINCT wstt.delivery_id
FROM wms_shipping_transaction_temp wstt
WHERE wstt.organization_id = p_org_id
AND wstt.dock_door_id = p_dock_door_id
AND wstt.trip_id = p_trip_id;
SELECT NAME
INTO l_name
FROM wsh_trips_ob_grp_v
WHERE trip_id = p_trip_id;
SELECT NAME
INTO l_trip_name
FROM wsh_trips_ob_grp_v
WHERE trip_id = p_trip_id;
SELECT stop_id
INTO l_stop_id
FROM wsh_trip_stops_ob_grp_v wts, wsh_delivery_legs_ob_grp_v wdl
WHERE wts.trip_id = p_trip_id
AND wts.stop_id = wdl.pick_up_stop_id
AND ROWNUM < 2;
SELECT delivery_detail_id
FROM wsh_delivery_assignments_v
START WITH delivery_detail_id = l_outermost_dd_id
CONNECT BY PRIOR delivery_detail_id = parent_delivery_detail_id;
SELECT wdd.delivery_detail_id
, wdd.inventory_item_id
, wdd.serial_number
, wdd.source_line_id
, wdd.requested_quantity
, wdd.transaction_temp_id
FROM wsh_delivery_details_ob_grp_v wdd
WHERE wdd.organization_id = p_org_id
AND wdd.container_flag <> 'Y'
AND wdd.delivery_detail_id = p_delivery_detail_id
ORDER BY wdd.source_line_id;
SELECT lpn_id
FROM wms_license_plate_numbers wlpn
WHERE wlpn.outermost_lpn_id = p_outermost_lpn_id;
select lpn_id, organization_id
from wms_license_plate_numbers
where outermost_lpn_id = p_outermost_lpn_id;
SELECT wdd.delivery_detail_id, wfc.freight_cost_id
INTO l_freight_costs.delivery_detail_id,
l_freight_costs.freight_cost_id
FROM wsh_delivery_details_ob_grp_v wdd,
wsh_freight_costs wfc
WHERE wdd.lpn_id = p_outermost_lpn_id
AND wdd.released_status = 'X' -- For LPN reuse ER : 6845650
AND wdd.delivery_detail_id = wfc.delivery_detail_id;
debug('About to call wsh_freight_costs_pub.delete_freight_costs','UNLOAD_TRUCK');
wsh_freight_costs_pub.delete_freight_costs
(p_api_version_number => 1.0,
p_init_msg_list => l_init_msg_list,
p_commit => fnd_api.g_false,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_pub_freight_costs => l_freight_costs);
SELECT wstt.delivery_id
, wnd.status_code
INTO l_del_id
, l_status_code
FROM wms_shipping_transaction_temp wstt, wsh_new_deliveries_ob_grp_v wnd
WHERE wstt.outermost_lpn_id = p_outermost_lpn_id
AND wstt.direct_ship_flag = 'Y'
AND wstt.delivery_id = wnd.delivery_id
AND ROWNUM = 1;
SELECT wdd.delivery_detail_id
INTO l_outermost_dd_id
FROM wsh_delivery_details_ob_grp_v wdd
WHERE lpn_id = p_outermost_lpn_id
AND released_status = 'X'; -- For LPN reuse ER : 6845650
SELECT serial_number_control_code,
lot_control_code,
lot_divisible_flag
INTO l_serial_ctrl_code,
l_lot_ctrl_code,
l_lot_divisible_flag
FROM mtl_system_items
WHERE inventory_item_id = l_item_id
AND organization_id = p_org_id;
UPDATE mtl_serial_numbers
SET current_status = 3
, group_mark_id = NULL -- -1
WHERE inventory_item_id = l_item_id
AND current_organization_id = p_org_id
AND(serial_number = l_serial_number
OR group_mark_id = l_transaction_temp_id); --bug#2829514
UPDATE mtl_serial_numbers
SET current_status = 1
, group_mark_id = NULL -- -1
WHERE inventory_item_id = l_item_id
AND current_organization_id = p_org_id
AND(serial_number = l_serial_number
OR group_mark_id = l_transaction_temp_id); --bug#2829514
DEBUG('Update LPN Context to Reside in inventory', 'Unload_truck');
to update the context and remove the WDD records
associated with those LPNs */
l_lpn_tbl.delete;
SELECT DISTINCT outermost_lpn_id
FROM wms_shipping_transaction_temp
WHERE organization_id = p_org_id
AND trip_id = p_trip_id;
SELECT DISTINCT delivery_id
FROM wms_shipping_transaction_temp
WHERE organization_id = p_org_id
AND trip_id = p_trip_id;
DELETE FROM wms_freight_cost_temp
WHERE organization_id = p_org_id
AND lpn_id = l_outermost_lpn_id;
DELETE FROM mtl_serial_numbers_temp
WHERE transaction_temp_id IN(SELECT transaction_temp_id
FROM wms_direct_ship_temp
WHERE lpn_id = p_outermost_lpn_id);
DELETE FROM wms_direct_ship_temp
WHERE organization_id = p_org_id
--AND lpn_id = p_outermost_lpn_id;
DELETE FROM wms_freight_cost_temp
WHERE organization_id = p_org_id
AND delivery_id = l_delivery.delivery_id;
DELETE FROM wms_freight_cost_temp
WHERE organization_id = p_org_id
AND trip_id = p_trip_id;
DELETE FROM wms_shipping_transaction_temp
WHERE organization_id = p_org_id
AND trip_id = p_trip_id;
DEBUG('Deleted temp recs for the case when trip id is not passed', 'CLEANUP_TEMP_RECS');
DELETE FROM wms_shipping_transaction_temp
WHERE organization_id = p_org_id
AND outermost_lpn_id = p_outermost_lpn_id;
DELETE FROM wms_freight_cost_temp
WHERE lpn_id = p_outermost_lpn_id;
DELETE FROM mtl_serial_numbers_temp
WHERE transaction_temp_id IN(SELECT transaction_temp_id
FROM wms_direct_ship_temp
WHERE lpn_id = p_outermost_lpn_id);
DELETE FROM wms_direct_ship_temp
WHERE lpn_id = p_outermost_lpn_id;
DEBUG('Deleted temp recs for the case when trip id is not passed', 'CLEANUP_TEMP_RECS');
SELECT wlpn.lpn_id
, wlpn.subinventory_code
, wlpn.locator_id
, wlc.lot_number
, wlc.inventory_item_id
, msn.serial_number
FROM wms_lpn_contents wlc, wms_license_plate_numbers wlpn, mtl_serial_numbers msn
WHERE wlpn.lpn_id = wlc.parent_lpn_id
AND msn.lpn_id(+) = wlc.parent_lpn_id
AND msn.inventory_item_id(+) = wlc.inventory_item_id
AND msn.current_organization_id(+) = wlc.organization_id
/* Bug# 3119461 Without the following joins for lot and rev
** we would get a cartesian product */
AND NVL(msn.lot_number(+),'#NULL#') = NVL(wlc.lot_number,'#NULL#')
AND NVL(msn.revision(+),'#NULL#') = NVL(wlc.revision,'#NULL#')
AND wlpn.outermost_lpn_id = p_lpn_id
AND wlpn.organization_id = p_org_id
AND wlc.organization_id = p_org_id;
SELECT status_control_flag
INTO l_trx_status_enabled
FROM mtl_transaction_types
WHERE transaction_type_id = 52; /* Transaction_type_id is hardcoded to 52 ..sales order staging transfer*/
PROCEDURE update_freight_cost(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_lpn_id IN NUMBER
) IS
CURSOR lpn_freight IS
SELECT ROWID
, freight_cost_type_id
, currency_code
, freight_amount
, conversion_type
FROM wms_freight_cost_temp
WHERE lpn_id = p_lpn_id
AND freight_cost_id IS NULL
FOR UPDATE OF freight_cost_id;
SELECT wdd.delivery_detail_id
FROM wsh_delivery_details_ob_grp_v wdd
WHERE lpn_id = p_lpn_id
AND released_status = 'X'; -- For LPN reuse ER : 6845650
wsh_freight_costs_pub.create_update_freight_costs(
p_api_version_number => 1.0
, p_init_msg_list => l_init_msg_list
, p_commit => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_pub_freight_costs => l_freight_cost_rec
, p_action_code => 'CREATE'
, x_freight_cost_id => l_freight_cost_id
);
DEBUG('Create_Update_Freight_Costs API completed status E ', 'update_freight_cost');
DEBUG('Create_Update_Freight_Costs API completed status U ', 'update_freight_cost');
UPDATE wms_freight_cost_temp
SET freight_cost_id = l_freight_cost_id
, last_update_date = SYSDATE
, last_updated_by = fnd_global.user_id
WHERE ROWID = l_lpn_freight.ROWID;
DEBUG('Update_Freight cost Update was successful', 'update_freight_cost');
DEBUG('Update_Freight cost API failed with status (E)', 'update_freight_cost');
DEBUG('Update_Freight cost API failed with status (U)', 'update_freight_cost');
DEBUG('Update_Freight cost API failed with status (U)', 'update_freight_cost');
fnd_msg_pub.add_exc_msg('WMS_DIRECT_SHIP_PVT', 'update_freight_cost');
END update_freight_cost;
* MSNT and inserted into MSN with appropriate values copied from
* MSNT. When delivery lines are split transaction temp ids are changed
* and appropriately updated into MSNT and into MSN as group mark id.
******************************/
PROCEDURE explode_delivery_details(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
--Bug No 3390432
, x_transaction_temp_id OUT NOCOPY NUMBER
, p_organization_id IN NUMBER
, p_lpn_id IN NUMBER
, p_serial_number_control_code IN NUMBER
, p_delivery_detail_id IN NUMBER
, p_quantity IN NUMBER
, p_transaction_temp_id IN NUMBER DEFAULT NULL
, p_reservation_id IN NUMBER DEFAULT NULL
, p_last_action IN VARCHAR2 DEFAULT 'U'
) IS
l_running_quantity NUMBER;
SELECT msnt.ROWID
, dd.requested_quantity
, msnt.transaction_temp_id
, dd.delivery_detail_id
, msnt.fm_serial_number
, msnt.to_serial_number
, msnt.serial_prefix
, dd.organization_id
, dd.inventory_item_id
, msnt.serial_attribute_category
, msnt.origination_date
, msnt.c_attribute1
, msnt.c_attribute2
, msnt.c_attribute3
, msnt.c_attribute4
, msnt.c_attribute5
, msnt.c_attribute6
, msnt.c_attribute7
, msnt.c_attribute8
, msnt.c_attribute9
, msnt.c_attribute10
, msnt.c_attribute11
, msnt.c_attribute12
, msnt.c_attribute13
, msnt.c_attribute14
, msnt.c_attribute15
, msnt.c_attribute16
, msnt.c_attribute17
, msnt.c_attribute18
, msnt.c_attribute19
, msnt.c_attribute20
, msnt.d_attribute1
, msnt.d_attribute2
, msnt.d_attribute3
, msnt.d_attribute4
, msnt.d_attribute5
, msnt.d_attribute6
, msnt.d_attribute7
, msnt.d_attribute8
, msnt.d_attribute9
, msnt.d_attribute10
, msnt.n_attribute1
, msnt.n_attribute2
, msnt.n_attribute3
, msnt.n_attribute4
, msnt.n_attribute5
, msnt.n_attribute6
, msnt.n_attribute7
, msnt.n_attribute8
, msnt.n_attribute9
, msnt.n_attribute10
, msnt.status_id
, msnt.territory_code
, msnt.time_since_new
, msnt.cycles_since_new
, msnt.time_since_overhaul
, msnt.cycles_since_overhaul
, msnt.time_since_repair
, msnt.cycles_since_repair
, msnt.time_since_visit
, msnt.cycles_since_visit
, msnt.time_since_mark
, msnt.cycles_since_mark
, msnt.number_of_repairs
FROM wsh_delivery_details_ob_grp_v dd
, mtl_serial_numbers_temp msnt
WHERE delivery_detail_id = p_delivery_detail_id
AND msnt.transaction_temp_id = p_transaction_temp_id;
SELECT serial_number
FROM mtl_serial_numbers
WHERE current_organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND serial_number BETWEEN p_fm_serial_number AND p_to_serial_number
AND current_status IN(1, 6)
AND LENGTH(serial_number) = p_serial_length;
SELECT serial_number,
group_mark_id,
reservation_id
FROM mtl_serial_numbers a
WHERE current_organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND lpn_id = p_lpn_id
AND current_status = 3
AND ( NVL(group_mark_id, 0) < 1
OR (NVL(group_mark_id, 0) = p_reservation_id)
OR (reservation_id = p_reservation_id))
ORDER BY a.reservation_id
, a.serial_number;
SELECT serial_number,
group_mark_id,
reservation_id
FROM mtl_serial_numbers a
WHERE current_organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND lpn_id = p_lpn_id
AND current_status = 3
AND NVL(end_item_unit_number, '@@') = NVL(p_eiun, '@@')
AND ( NVL(group_mark_id, 0) < 1
OR (NVL(group_mark_id, 0) = p_reservation_id)
OR (reservation_id = p_reservation_id))
ORDER BY a.reservation_id
, a.serial_number;
SELECT SUM(wdd2.requested_quantity)
INTO l_tot_line_qty
FROM wsh_delivery_details_ob_grp_v wdd1
, wsh_delivery_details_ob_grp_v wdd2
WHERE wdd1.delivery_detail_id = p_delivery_detail_id
AND wdd2.source_header_id = wdd1.source_header_id
AND wdd2.source_line_id = wdd1.source_line_id
AND wdd2.source_code = wdd1.source_code
AND wdd2.released_status IN('R', 'B', 'X')
AND wdd2.container_flag = 'N';
SELECT mtl_material_transactions_s.NEXTVAL
INTO x_transaction_temp_id
FROM DUAL;
UPDATE mtl_serial_numbers
SET serial_attribute_category = l_explode_detail.serial_attribute_category
, origination_date = l_explode_detail.origination_date
, c_attribute1 = l_explode_detail.c_attribute1
, c_attribute2 = l_explode_detail.c_attribute2
, c_attribute3 = l_explode_detail.c_attribute3
, c_attribute4 = l_explode_detail.c_attribute4
, c_attribute5 = l_explode_detail.c_attribute5
, c_attribute6 = l_explode_detail.c_attribute6
, c_attribute7 = l_explode_detail.c_attribute7
, c_attribute8 = l_explode_detail.c_attribute8
, c_attribute9 = l_explode_detail.c_attribute9
, c_attribute10 = l_explode_detail.c_attribute10
, c_attribute11 = l_explode_detail.c_attribute11
, c_attribute12 = l_explode_detail.c_attribute12
, c_attribute13 = l_explode_detail.c_attribute13
, c_attribute14 = l_explode_detail.c_attribute14
, c_attribute15 = l_explode_detail.c_attribute15
, c_attribute16 = l_explode_detail.c_attribute16
, c_attribute17 = l_explode_detail.c_attribute17
, c_attribute18 = l_explode_detail.c_attribute18
, c_attribute19 = l_explode_detail.c_attribute19
, c_attribute20 = l_explode_detail.c_attribute20
, d_attribute1 = l_explode_detail.d_attribute1
, d_attribute2 = l_explode_detail.d_attribute2
, d_attribute3 = l_explode_detail.d_attribute3
, d_attribute4 = l_explode_detail.d_attribute4
, d_attribute5 = l_explode_detail.d_attribute5
, d_attribute6 = l_explode_detail.d_attribute6
, d_attribute7 = l_explode_detail.d_attribute7
, d_attribute8 = l_explode_detail.d_attribute8
, d_attribute9 = l_explode_detail.d_attribute9
, d_attribute10 = l_explode_detail.d_attribute10
, n_attribute1 = l_explode_detail.n_attribute1
, n_attribute2 = l_explode_detail.n_attribute2
, n_attribute3 = l_explode_detail.n_attribute3
, n_attribute4 = l_explode_detail.n_attribute4
, n_attribute5 = l_explode_detail.n_attribute5
, n_attribute6 = l_explode_detail.n_attribute6
, n_attribute7 = l_explode_detail.n_attribute7
, n_attribute8 = l_explode_detail.n_attribute8
, n_attribute9 = l_explode_detail.n_attribute9
, n_attribute10 = l_explode_detail.n_attribute10
, status_id = l_explode_detail.status_id
, territory_code = l_explode_detail.territory_code
, time_since_new = l_explode_detail.time_since_new
, cycles_since_new = l_explode_detail.cycles_since_new
, time_since_overhaul = l_explode_detail.time_since_overhaul
, cycles_since_overhaul = l_explode_detail.cycles_since_overhaul
, time_since_repair = l_explode_detail.time_since_repair
, cycles_since_repair = l_explode_detail.cycles_since_repair
, time_since_visit = l_explode_detail.time_since_visit
, cycles_since_visit = l_explode_detail.cycles_since_visit
, time_since_mark = l_explode_detail.time_since_mark
, cycles_since_mark = l_explode_detail.cycles_since_mark
, number_of_repairs = l_explode_detail.number_of_repairs
, group_mark_id = l_group_mark_id
WHERE current_organization_id = l_explode_detail.organization_id
AND inventory_item_id = l_explode_detail.inventory_item_id
AND serial_number = l_explode_detail.fm_serial_number;
'After update mtl_serial_numbers with the attributes: c_attribute1 ' || l_explode_detail.c_attribute1
, 'EXPLODE_DELIVERY_DETAILS'
);
UPDATE mtl_serial_numbers_temp
SET fm_serial_number = l_new_fm_serial
WHERE ROWID = l_rowid;
inv_trx_util_pub.insert_ser_trx(
p_trx_tmp_id => l_explode_detail.transaction_temp_id
, p_user_id => fnd_global.user_id
, p_fm_ser_num => l_explode_detail.fm_serial_number
, p_to_ser_num => l_current_to_serial
, p_serial_attribute_category => l_explode_detail.serial_attribute_category
, p_orgination_date => l_explode_detail.origination_date
, p_c_attribute1 => l_explode_detail.c_attribute1
, p_c_attribute2 => l_explode_detail.c_attribute2
, p_c_attribute3 => l_explode_detail.c_attribute3
, p_c_attribute4 => l_explode_detail.c_attribute4
, p_c_attribute5 => l_explode_detail.c_attribute5
, p_c_attribute6 => l_explode_detail.c_attribute6
, p_c_attribute7 => l_explode_detail.c_attribute7
, p_c_attribute8 => l_explode_detail.c_attribute8
, p_c_attribute9 => l_explode_detail.c_attribute9
, p_c_attribute10 => l_explode_detail.c_attribute10
, p_c_attribute11 => l_explode_detail.c_attribute11
, p_c_attribute12 => l_explode_detail.c_attribute12
, p_c_attribute13 => l_explode_detail.c_attribute13
, p_c_attribute14 => l_explode_detail.c_attribute14
, p_c_attribute15 => l_explode_detail.c_attribute15
, p_c_attribute16 => l_explode_detail.c_attribute16
, p_c_attribute17 => l_explode_detail.c_attribute17
, p_c_attribute18 => l_explode_detail.c_attribute18
, p_c_attribute19 => l_explode_detail.c_attribute19
, p_c_attribute20 => l_explode_detail.c_attribute20
, p_d_attribute1 => l_explode_detail.d_attribute1
, p_d_attribute2 => l_explode_detail.d_attribute2
, p_d_attribute3 => l_explode_detail.d_attribute3
, p_d_attribute4 => l_explode_detail.d_attribute4
, p_d_attribute5 => l_explode_detail.d_attribute5
, p_d_attribute6 => l_explode_detail.d_attribute6
, p_d_attribute7 => l_explode_detail.d_attribute7
, p_d_attribute8 => l_explode_detail.d_attribute8
, p_d_attribute9 => l_explode_detail.d_attribute9
, p_d_attribute10 => l_explode_detail.d_attribute10
, p_n_attribute1 => l_explode_detail.n_attribute1
, p_n_attribute2 => l_explode_detail.n_attribute2
, p_n_attribute3 => l_explode_detail.n_attribute3
, p_n_attribute4 => l_explode_detail.n_attribute4
, p_n_attribute5 => l_explode_detail.n_attribute5
, p_n_attribute6 => l_explode_detail.n_attribute6
, p_n_attribute7 => l_explode_detail.n_attribute7
, p_n_attribute8 => l_explode_detail.n_attribute8
, p_n_attribute9 => l_explode_detail.n_attribute9
, p_n_attribute10 => l_explode_detail.n_attribute10
, p_status_id => l_explode_detail.status_id
, p_territory_code => l_explode_detail.territory_code
, p_time_since_new => l_explode_detail.time_since_new
, p_cycles_since_new => l_explode_detail.cycles_since_new
, p_time_since_overhaul => l_explode_detail.time_since_overhaul
, p_cycles_since_overhaul => l_explode_detail.cycles_since_overhaul
, p_time_since_repair => l_explode_detail.time_since_repair
, p_cycles_since_repair => l_explode_detail.cycles_since_repair
, p_time_since_visit => l_explode_detail.time_since_visit
, p_cycles_since_visit => l_explode_detail.cycles_since_visit
, p_time_since_mark => l_explode_detail.time_since_mark
, p_cycles_since_mark => l_explode_detail.cycles_since_mark
, p_number_of_repairs => l_explode_detail.number_of_repairs
, x_proc_msg => l_proc_msg
);
'Before update the mtl_serial_numbers with org '
|| TO_CHAR(l_explode_detail.organization_id)
|| ' item '
|| TO_CHAR(l_explode_detail.inventory_item_id)
|| ' serial '
|| l_explode_detail.fm_serial_number
, 'EXPLODE_DELIVERY_DETAILS'
);
UPDATE mtl_serial_numbers
SET serial_attribute_category = l_explode_detail.serial_attribute_category
, origination_date = l_explode_detail.origination_date
, c_attribute1 = l_explode_detail.c_attribute1
, c_attribute2 = l_explode_detail.c_attribute2
, c_attribute3 = l_explode_detail.c_attribute3
, c_attribute4 = l_explode_detail.c_attribute4
, c_attribute5 = l_explode_detail.c_attribute5
, c_attribute6 = l_explode_detail.c_attribute6
, c_attribute7 = l_explode_detail.c_attribute7
, c_attribute8 = l_explode_detail.c_attribute8
, c_attribute9 = l_explode_detail.c_attribute9
, c_attribute10 = l_explode_detail.c_attribute10
, c_attribute11 = l_explode_detail.c_attribute11
, c_attribute12 = l_explode_detail.c_attribute12
, c_attribute13 = l_explode_detail.c_attribute13
, c_attribute14 = l_explode_detail.c_attribute14
, c_attribute15 = l_explode_detail.c_attribute15
, c_attribute16 = l_explode_detail.c_attribute16
, c_attribute17 = l_explode_detail.c_attribute17
, c_attribute18 = l_explode_detail.c_attribute18
, c_attribute19 = l_explode_detail.c_attribute19
, c_attribute20 = l_explode_detail.c_attribute20
, d_attribute1 = l_explode_detail.d_attribute1
, d_attribute2 = l_explode_detail.d_attribute2
, d_attribute3 = l_explode_detail.d_attribute3
, d_attribute4 = l_explode_detail.d_attribute4
, d_attribute5 = l_explode_detail.d_attribute5
, d_attribute6 = l_explode_detail.d_attribute6
, d_attribute7 = l_explode_detail.d_attribute7
, d_attribute8 = l_explode_detail.d_attribute8
, d_attribute9 = l_explode_detail.d_attribute9
, d_attribute10 = l_explode_detail.d_attribute10
, n_attribute1 = l_explode_detail.n_attribute1
, n_attribute2 = l_explode_detail.n_attribute2
, n_attribute3 = l_explode_detail.n_attribute3
, n_attribute4 = l_explode_detail.n_attribute4
, n_attribute5 = l_explode_detail.n_attribute5
, n_attribute6 = l_explode_detail.n_attribute6
, n_attribute7 = l_explode_detail.n_attribute7
, n_attribute8 = l_explode_detail.n_attribute8
, n_attribute9 = l_explode_detail.n_attribute9
, n_attribute10 = l_explode_detail.n_attribute10
, status_id = l_explode_detail.status_id
, territory_code = l_explode_detail.territory_code
, time_since_new = l_explode_detail.time_since_new
, cycles_since_new = l_explode_detail.cycles_since_new
, time_since_overhaul = l_explode_detail.time_since_overhaul
, cycles_since_overhaul = l_explode_detail.cycles_since_overhaul
, time_since_repair = l_explode_detail.time_since_repair
, cycles_since_repair = l_explode_detail.cycles_since_repair
, time_since_visit = l_explode_detail.time_since_visit
, cycles_since_visit = l_explode_detail.cycles_since_visit
, time_since_mark = l_explode_detail.time_since_mark
, cycles_since_mark = l_explode_detail.cycles_since_mark
, number_of_repairs = l_explode_detail.number_of_repairs
, group_mark_id = l_group_mark_id
WHERE current_organization_id = l_explode_detail.organization_id
AND inventory_item_id = l_explode_detail.inventory_item_id
AND serial_number = l_serial_number;
* Update all the Processed serial numbers
* with the new already generated transaction
* temp id
****************************/
l_running_quantity := l_serial_numbers_table.COUNT;
UPDATE mtl_serial_numbers_temp
SET transaction_temp_id = x_transaction_temp_id
WHERE transaction_temp_id = p_transaction_temp_id
AND fm_serial_number = l_serial_numbers_table(j);
SELECT inventory_item_id
INTO l_inventory_item_id
FROM mtl_reservations
WHERE reservation_id = p_reservation_id;
SELECT oel.end_item_unit_number
INTO l_end_item_unit_number
FROM oe_order_lines_all oel, wsh_delivery_details_ob_grp_v wdd
WHERE wdd.delivery_detail_id = p_delivery_detail_id
AND wdd.source_header_id = oel.header_id
AND wdd.source_line_id = oel.line_id;
/* 5506223: Because of the above, using direct update to MSN to set the
* group_mark_id */
BEGIN
UPDATE mtl_serial_numbers
SET group_mark_id = l_group_mark_id
WHERE inventory_item_id = l_inventory_item_id
AND serial_number = l_serial_numbers_table(n)
AND current_organization_id = p_organization_id;
DEBUG( 'inserting serials ' || l_serial_numbers_table(n) || ' into msnt WITH temp_id ='
|| p_transaction_temp_id , 'EXPLODE_DELIVERY_DETAILS');
inv_trx_util_pub.insert_ser_trx(
p_trx_tmp_id => p_transaction_temp_id
, p_user_id => fnd_global.user_id
, p_fm_ser_num => l_serial_numbers_table(n)
, p_to_ser_num => l_serial_numbers_table(n)
, x_proc_msg => l_proc_msg
);
DEBUG('insert_ser_trx ended with errors ' || l_proc_msg, 'EXPLODE_DELIVERY_DETAILS');
SELECT wds.transaction_temp_id
, wdd.delivery_detail_id
, wdd.picked_quantity
, wdd.shipped_quantity
, msi.primary_uom_code
, msi.lot_control_code
, msi.serial_number_control_code
INTO l_transaction_temp_id
, l_delivery_detail_id
, l_picked_qty
, l_shipped_qty
, l_primary_uom_code
, l_lot_control_code
, l_serial_number_control_code
FROM wms_direct_ship_temp wds,
wsh_delivery_details wdd,
mtl_system_items msi
WHERE wds.organization_id = p_organization_id
AND wds.lpn_id = p_lpn_id
AND wds.order_header_id = p_source_header_id
AND wds.order_line_id = p_source_line_id
AND wdd.organization_id = p_organization_id
AND wdd.source_header_id = wds.order_header_id
AND wdd.source_line_id = wds.order_line_id
AND msi.organization_id = p_organization_id
AND msi.inventory_item_id = wdd.inventory_item_id
AND wdd.released_status = 'Y'
AND wdd.container_flag = 'N'
--AND wdd.split_from_delivery_detail_id is null
AND rownum =1;
/* Now we have the delivery_Detail to split so first update the quantity from
Reservation on the wdd and then split from it*/
DEBUG('Before calling Update_shipping_attributes to update qty'||l_picked_qty, 'Overship Staged Lines');
l_shipping_attr_tab.DELETE; --Added delete to prevent usage from previous loop bug4128854
wsh_interface.update_shipping_attributes(p_source_code => 'INV',
p_changed_attributes => l_shipping_attr_tab
,x_return_status => l_return_status);
l_shipping_attr_tab.DELETE;
DEBUG('Before calling Update_shipping_attributes to update qty'||
l_shipping_attr_tab(1).shipped_quantity , 'Overship Staged Lines');
wsh_interface.update_shipping_attributes(p_source_code => 'INV',
p_changed_attributes => l_shipping_attr_tab
,x_return_status => l_return_status);
DEBUG('After calling Update_shipping_attributes', 'Overship Staged Lines');
DEBUG('Return error from update shipping attributes 2', 'Overship Staged Lines');
DEBUG('Return unexpected error from update shipping attributes', 'Overship Staged Lines');
DEBUG('Shipping attributes updated successfully','Overship Staged Lines');
/*Now update the new delivery_Detail with correct attributes*/
l_shipping_attr_tab.DELETE;
wsh_interface.update_shipping_attributes(
p_source_code => 'INV',
p_changed_attributes => l_shipping_attr_tab,
x_return_status => l_return_status);
DEBUG('After update shipping attributes', 'Overship Staged Lines');
DEBUG('Return error from update shipping attributes', 'Overship Staged Lines');
DEBUG('Return unexpected error from update shipping attributes','Overship Staged Lines');
SELECT wds.ORGANIZATION_ID
,WDS.DOCK_DOOR_ID
,WDS.LPN_ID
,WDS.ORDER_HEADER_ID
,WDS.ORDER_LINE_ID
,WDS.LINE_ITEM_ID
,WDS.TRANSACTION_TEMP_ID
,WDD.DELIVERY_DETAIL_ID
,DECODE(WDD.REQUESTED_QUANTITY_UOM,MSI.PRIMARY_UOM_CODE
,WDD.REQUESTED_QUANTITY
,GREATEST(INV_CONVERT.INV_UM_CONVERT(
null
,null
,WDD.REQUESTED_QUANTITY
,WDD.REQUESTED_QUANTITY_UOM
,MSI.PRIMARY_UOM_CODE
,null
,null),0)) REQUESTED_QUANTITY
,MSI.PRIMARY_UOM_CODE
,MSI.LOT_CONTROL_CODE
,MSI.SERIAL_NUMBER_CONTROL_CODE
INTO l_delivery_detail_tab(i).ORGANIZATION_ID
,l_delivery_detail_tab(i).DOCK_DOOR_ID
,l_delivery_detail_tab(i).LPN_ID
,l_delivery_detail_tab(i).ORDER_HEADER_ID
,l_delivery_detail_tab(i).ORDER_LINE_ID
,l_delivery_detail_tab(i).LINE_ITEM_ID
,l_delivery_detail_tab(i).TRANSACTION_TEMP_ID
,l_delivery_detail_tab(i).DELIVERY_DETAIL_ID
,l_delivery_detail_tab(i).REQUESTED_QUANTITY
,l_delivery_detail_tab(i).PRIMARY_UOM_CODE
,l_delivery_detail_tab(i).LOT_CONTROL_CODE
,l_delivery_detail_tab(i).SERIAL_NUMBER_CONTROL_CODE
FROM WMS_DIRECT_SHIP_TEMP WDS,
WSH_DELIVERY_DETAILS WDD,
MTL_SYSTEM_ITEMS MSI
WHERE WDS.ORGANIZATION_ID = p_organization_id
AND WDS.lpn_id = p_lpn_id
AND WDS.ORDER_HEADER_ID = p_source_header_id
AND WDS.ORDER_LINE_ID = p_source_line_id
AND WDD.ORGANIZATION_ID = p_organization_id
AND WDD.SOURCE_HEADER_ID = WDS.ORDER_HEADER_ID
AND WDD.SOURCE_LINE_ID = WDS.ORDER_LINE_ID
AND MSI.ORGANIZATION_ID = p_organization_id
AND MSI.INVENTORY_ITEM_ID = WDD.INVENTORY_ITEM_ID
AND WDD.delivery_detail_id = l_new_delivery_detail_id
AND rownum = 1
ORDER BY WDS.LINE_ITEM_ID;
SELECT DISTINCT lpn_id
FROM wms_direct_ship_temp
WHERE GROUP_ID = p_group_id
AND organization_id = p_organization_id
AND dock_door_id = p_dock_door_id;
SELECT lpn_id
FROM wms_license_plate_numbers
WHERE outermost_lpn_id = p_lpn_id;
SELECT wds.order_header_id
, wds.order_line_id
, sub.reservable_type
, msi.reservable_type
, msi.lot_control_code
, msi.serial_number_control_code
FROM wms_direct_ship_temp wds
, wms_license_plate_numbers wlpn
, mtl_secondary_inventories sub
, mtl_system_items msi
WHERE wds.GROUP_ID = p_group_id
AND wds.organization_id = p_organization_id
AND wds.dock_door_id = p_dock_door_id
AND wds.lpn_id = p_lpn_id
AND wlpn.lpn_id = wds.lpn_id
AND sub.organization_id = wds.organization_id
AND wlpn.subinventory_code = sub.secondary_inventory_name
AND msi.organization_id = wds.organization_id
AND msi.inventory_item_id = wds.line_item_id;
SELECT DISTINCT trip_id
FROM wms_shipping_transaction_temp
WHERE organization_id = p_organization_id
AND dock_door_id = p_dock_door_id
AND dock_appoint_flag = 'N'
AND direct_ship_flag = 'Y';
SELECT DISTINCT delivery_id
FROM wms_shipping_transaction_temp
WHERE organization_id = p_organization_id
AND dock_door_id = p_dock_door_id
AND NVL(trip_id, 0) = 0
AND direct_ship_flag = 'Y';
SELECT wdd.delivery_detail_id
FROM wsh_delivery_details_ob_grp_v wdd
, wms_direct_ship_temp wds
WHERE wds.organization_id = p_organization_id
AND wds.lpn_id = p_lpn_id
AND wdd.source_header_id = wds.order_header_id
AND wdd.source_line_id = wds.order_line_id
AND wdd.released_status IN('R', 'B')
AND wdd.picked_quantity > 0;
CURSOR delete_details(p_outermost_lpn_id NUMBER) IS
SELECT delivery_detail_id
FROM wsh_delivery_details_ob_grp_v wdd
, wms_license_plate_numbers lpn
WHERE lpn.outermost_lpn_id = p_outermost_lpn_id
AND lpn.lpn_id = wdd.lpn_id
AND wdd.released_status = 'X'; -- For LPN reuse ER : 6845650
SELECT lpn_id
FROM wms_license_plate_numbers
WHERE outermost_lpn_id = p_outermost_lpn_id
ORDER BY lpn_id;
SELECT ignore_for_planning, tms_interface_flag
FROM wsh_new_deliveries_ob_grp_v
WHERE delivery_id = v_del_id ;
wms_direct_ship_pvt.create_update_containers(
x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_org_id => p_organization_id
, p_outermost_lpn_id => l_outer_lpn.lpn_id
);
DEBUG('create_update_containers API failed with status E ', 'STAGE_LPN');
DEBUG('create_update_containers failed with status U', 'STAGE_LPN');
SELECT delivery_detail_id
INTO l_cont_instance_id
FROM wsh_delivery_details_ob_grp_v
WHERE lpn_id = l_outer_lpn.lpn_id
AND released_status = 'X'; -- For LPN reuse ER : 6845650
SELECT wdd.delivery_detail_id
INTO l_delivery_detail_id
FROM wsh_delivery_details_ob_grp_v wdd, wms_direct_ship_temp wds
WHERE wds.organization_id = p_organization_id
AND wds.lpn_id = l_outer_lpn.lpn_id
AND wdd.source_header_id = wds.order_header_id
AND wdd.source_line_id = wds.order_line_id
AND wdd.released_status IN('R', 'B', 'Y') --Added 'Y' bug4128854
AND ROWNUM = 1;
wsh_container_actions.update_cont_hierarchy(
p_del_detail_id => l_delivery_detail_id
, p_delivery_id => NULL
, p_container_instance_id => l_cont_instance_id
, x_return_status => l_return_status
);
DEBUG('The Update container_Hierarchy was successful for outer-lpn ' || l_outer_lpn.lpn_id, 'STAGE_LPN');
l_mtl_reservation_tbl.DELETE;
' SELECT wds.organization_id ' ||
' , wds.dock_door_id ' ||
' , wds.lpn_id ' ||
' , wds.order_header_id ' ||
' , wds.order_line_id ' ||
' , wds.line_item_id ' ||
' , wds.transaction_temp_id ' ||
' , wdd.delivery_detail_id ' ||
' , DECODE( ' ||
' wdd.requested_quantity_uom ' ||
' , msi.primary_uom_code, wdd.requested_quantity ' ||
' , GREATEST( ' ||
' inv_convert.inv_um_convert(NULL, ' ||
' NULL, wdd.requested_quantity, wdd.requested_quantity_uom, ' ||
' msi.primary_uom_code, NULL , NULL) ' ||
' , 0 )) requested_quantity ' ||
' , msi.primary_uom_code ' ||
' , msi.lot_control_code ' ||
' , msi.serial_number_control_code ' ||
' , msi.inventory_item_id ';
l_shipping_attr.DELETE;
SELECT outermost_lpn_id
INTO l_outermost_lpn_id
FROM wms_license_plate_numbers
WHERE lpn_id = l_mtl_reservation_tbl(j).lpn_id;
DEBUG('Call the select statement for rsv lpn_id ' || l_mtl_reservation_tbl(j).lpn_id, 'Stage_LPNs');
SELECT outermost_lpn_id
INTO l_outermost_lpn_id
FROM wms_license_plate_numbers
WHERE lpn_id = l_mtl_reservation_tbl(j).lpn_id;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_delivery_detail_tab(i).transaction_temp_id
FROM DUAL;
UPDATE wms_direct_ship_temp
SET transaction_temp_id = l_delivery_detail_tab(i).transaction_temp_id
WHERE organization_id = l_delivery_detail_tab(i).organization_id
AND lpn_id = l_delivery_detail_tab(i).lpn_id
AND dock_door_id = l_delivery_detail_tab(i).dock_door_id
AND order_header_id = l_delivery_detail_tab(i).order_header_id
AND order_line_id = l_delivery_detail_tab(i).order_line_id;
* Use the new Transaction Temp Id to update WDD
**************************************/
IF(l_serial_number_control_code = 6) THEN
l_invpcinrectype.transaction_temp_id := l_out_transaction_temp_id;
SELECT fm_serial_number
INTO l_shipping_attr(1).serial_number
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = l_invpcinrectype.transaction_temp_id
AND ROWNUM < 2;
DELETE FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = l_invpcinrectype.transaction_temp_id;
wsh_interface.update_shipping_attributes
( p_source_code => 'INV'
, p_changed_attributes => l_shipping_attr
, x_return_status => l_return_status);
DEBUG('after update shipping attributes', 'stage_lpns');
DEBUG('return error from update shipping attributes 3', 'STAGE_LPN');
DEBUG('return unexpected error from update shipping attributes', 'STAGE_LPN');
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_delivery_detail_tab(i).transaction_temp_id
FROM DUAL;
UPDATE wms_direct_ship_temp
SET transaction_temp_id = l_delivery_detail_tab(i).transaction_temp_id
WHERE organization_id = l_delivery_detail_tab(i).organization_id
AND lpn_id = l_delivery_detail_tab(i).lpn_id
AND dock_door_id = l_delivery_detail_tab(i).dock_door_id
AND order_header_id = l_delivery_detail_tab(i).order_header_id
AND order_line_id = l_delivery_detail_tab(i).order_line_id;
DEBUG('return error from update shipping attributes 3', 'stage_lpns');
DEBUG('return unexpected error from update shipping attributes', 'stage_lpns');
* Update WDD with the newly created Transaction_temp_id
********************************************/
l_invpcinrectype.transaction_temp_id := l_out_transaction_temp_id;
SELECT fm_serial_number
INTO l_shipping_attr(1).serial_number
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = l_invpcinrectype.transaction_temp_id
AND ROWNUM < 2;
DELETE FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = l_invpcinrectype.transaction_temp_id;
SELECT fm_serial_number
INTO l_shipping_attr(1).serial_number
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = l_invpcinrectype.transaction_temp_id
AND ROWNUM < 2;
DELETE FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = l_invpcinrectype.transaction_temp_id;
DEBUG('Catch Weight updates ', 'stage_lpns');
DEBUG('Call update shipping attributes', 'stage_lpns');
wsh_interface.update_shipping_attributes(p_source_code => 'INV', p_changed_attributes => l_shipping_attr
, x_return_status => l_return_status);
DEBUG('after update shipping attributes', 'stage_lpns');
DEBUG('return error from update shipping attributes 2', 'stage_lpns');
DEBUG('return unexpected error from update shipping attributes', 'stage_lpns');
l_shipping_attr.DELETE;
wsh_interface.update_shipping_attributes(p_source_code => 'INV', p_changed_attributes => l_shipping_attr
, x_return_status => l_return_status);
DEBUG('Non-Reservable: return error from update shipping attributes', 'stage_lpns');
DEBUG('Non-Reservable: return unexpected error from update shipping attributes', 'stage_lpns');
SELECT COUNT(*)
INTO l_trip_id
FROM wms_shipping_transaction_temp
WHERE outermost_lpn_id = l_outer_lpn.lpn_id;
UPDATE wms_license_plate_numbers
SET lpn_context = 9
WHERE outermost_lpn_id = l_outer_lpn.lpn_id;
DEBUG('Update LPN Context to Loaded to Dock, LPN :' || TO_CHAR(l_outer_lpn.lpn_id), 'Stage_LPNs');
END; -- Populate WSTT and Update the LPN context
SELECT DISTINCT trip_id
FROM wms_shipping_transaction_temp
WHERE dock_door_id = p_dock_door_id
AND organization_id = p_org_id
AND direct_ship_flag = 'Y';
SELECT ship_method_code
, NAME
INTO l_ship_method_code
, l_trip_name
FROM wsh_trips_ob_grp_v
WHERE trip_id = l_trip_cursor.trip_id;
SELECT vehicle_item_id
INTO l_vehicle_item_id
FROM wsh_trips_ob_grp_v
WHERE trip_id = l_trip_cursor.trip_id;
SELECT lpn_id
, subinventory_code
, locator_id
FROM wms_license_plate_numbers
WHERE outermost_lpn_id = p_lpn;
SELECT revision_qty_control_code
, lot_control_code
, serial_number_control_code
INTO l_revision_control
, l_lot_control
, l_serial_control
FROM mtl_system_items
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
SELECT order_source_id
INTO l_order_source_id
FROM oe_order_headers_all
WHERE header_id = p_header_id;
PROCEDURE create_update_containers(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT nocopy VARCHAR2
, p_org_id IN NUMBER
, p_outermost_lpn_id IN NUMBER
, p_delivery_id IN NUMBER DEFAULT NULL
) IS
CURSOR lpn_details IS
SELECT lpn_id
, license_plate_number
, subinventory_code
, locator_id
, inventory_item_id
, revision
, lot_number
, serial_number
, gross_weight_uom_code
, gross_weight
, tare_weight_uom_code
, tare_weight
, content_volume_uom_code
, content_volume
-- Release 12 (K): LPN Synchronization
-- Add following new columns
, container_volume
, container_volume_uom
, organization_id
FROM wms_license_plate_numbers
WHERE organization_id = p_org_id
AND outermost_lpn_id = p_outermost_lpn_id;
wsh_update_tbl WSH_GLBL_VAR_STRCT_GRP.delivery_details_Attr_tbl_Type;
DEBUG('No more LPNs found from lpn_details to process', 'create_update_containers');
DEBUG('Found from lpn_details lpn '||l_lpn_cur.license_plate_number||', lpn_id='||l_lpn_cur.lpn_id, 'create_update_containers');
SELECT wdd.released_status
, wdd.delivery_detail_id
INTO l_status_code
, l_delivery_detail_id
FROM wsh_delivery_details_ob_grp_v wdd
WHERE wdd.container_name = l_lpn_cur.license_plate_number
AND wdd.released_status = 'X'; --Bug#6878521 Made change bt need to chk this
2. Replace API call to wsh_container_grp.update_container
with new API call WSH_WMS_LPN_GRP.Create_Update_Containers
*/
/*
IF (l_debug = 1) THEN
DEBUG('Release status is C, Updating delivery detail to NULL out LPN_ID', 'create_update_containers');
DEBUG(' Calling Create_Update_Containers with caller WMS, action code UPDATE_NULL', 'create_update_containers');
wsh_update_tbl(1) := l_wsh_dd_upd_rec;
l_IN_rec.action_code := 'UPDATE_NULL';
WSH_WMS_LPN_GRP.Create_Update_Containers (
p_api_version => 1.0
, p_init_msg_list => fnd_api.g_false
, p_commit => fnd_api.g_false
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_detail_info_tab => wsh_update_tbl
, p_IN_rec => l_IN_rec
, x_OUT_rec => l_OUT_rec );
DEBUG('WSH_WMS_LPN_GRP.Create_Update_Containers returns error', 'create_update_containers');
DEBUG('WSH_WMS_LPN_GRP.Create_Update_Containers returns unexpected error', 'create_update_containers');
DEBUG('WSH_WMS_LPN_GRP.Create_Update_Containers returns success, AF Container exists, set lpn_id to NULL', 'create_update_containers');
DEBUG('LPN EXISTS: ' || l_count, 'create_update_containers');
with new API call to WSH_WMS_LPN_GRP.Create_Update_Containers
The pre-R12 code was doing in two steps, create_container then update_container
With the new API call, it replaces both previous API calls */
/* Call function to create wsh delivery detail record */
l_wsh_dd_rec := WMS_CONTAINER_PVT.To_DeliveryDetailsRecType(l_lpn_cur);
DEBUG('End of Loop of lpn_details, found '||wsh_create_tbl.count||' records in wsh_create_tbl to process', 'create_update_containers');
DEBUG('Calling WSH_WMS_LPN_GRP.Create_Update_Containers with caller as WMS and CREATE', 'create_update_containers');
WSH_WMS_LPN_GRP.Create_Update_Containers (
p_api_version => 1.0
, p_init_msg_list => fnd_api.g_false
, p_commit => fnd_api.g_false
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_detail_info_tab => wsh_create_tbl
, p_IN_rec => l_IN_rec
, x_OUT_rec => l_OUT_rec );
DEBUG('WSH_WMS_LPN_GRP.Create_Update_Containers returns error', 'create_update_containers');
DEBUG('WSH_WMS_LPN_GRP.Create_Update_Containers returns unexpected error', 'create_update_containers');
DEBUG('WSH_WMS_LPN_GRP.Create_Update_Containers returns success', 'create_update_containers');
DEBUG('The API return status is ' || x_return_status, 'create_update_containers');
DEBUG('Execution Error in Create_Update_Container:' || SUBSTR(SQLERRM, 1, 240), 9);
DEBUG('Unexpected Error in Create_Update_Container:' || SUBSTR(SQLERRM, 1, 240), 'create_update_containers');
debug('Others exception raised: ' || SUBSTR(SQLERRM, 1, 240),'create_update_containers');
fnd_msg_pub.add_exc_msg('WMS_DIRECT_SHIP_PVT', 'create_update_containers');
END create_update_containers;
PROCEDURE update_shipped_quantity(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_delivery_id IN NUMBER
, p_org_id IN NUMBER DEFAULT NULL
) IS
-- Cursor to get the delivery qty (sum of all the delivery_details )
-- for the given delivery_id
CURSOR delivery_item_qty IS
SELECT wdd.inventory_item_id
, wdd.revision
, wdd.lot_number
, SUM(wdd.picked_quantity)
FROM wms_shipping_transaction_temp wstt, wsh_delivery_details_ob_grp_v wdd
WHERE wstt.delivery_id = p_delivery_id
AND wdd.delivery_detail_id = wstt.delivery_detail_id
AND wdd.released_status = 'Y'
GROUP BY wdd.inventory_item_id, wdd.revision, wdd.lot_number
ORDER BY wdd.inventory_item_id, wdd.revision, wdd.lot_number;
SELECT SUM(wlc.quantity)
FROM wms_lpn_contents wlc, wms_license_plate_numbers lpn,
wms_shipping_transaction_temp wstt
WHERE wlc.parent_lpn_id = lpn.lpn_id
and wstt.delivery_id = p_delivery_id
and lpn.outermost_lpn_id = wstt.outermost_lpn_id
and wstt.inventory_item_id = p_item_id
AND wstt.inventory_item_id = wlc.inventory_item_id
AND NVL(wlc.revision, '#') = NVL(p_revision, '#')
AND NVL(wlc.lot_number, '#') = NVL(p_lot_number, '#');
SELECT COUNT(COUNT(*))
INTO l_lpn_item_count
FROM wms_license_plate_numbers wlpn, wms_lpn_contents wlc, wms_shipping_transaction_temp wstt
WHERE wstt.delivery_id = p_delivery_id
AND wstt.outermost_lpn_id = wlpn.outermost_lpn_id
AND wlpn.lpn_id = wlc.parent_lpn_id
GROUP BY wlc.inventory_item_id, wlc.revision;
SELECT COUNT(COUNT(*))
INTO l_delivery_item_count
FROM wms_shipping_transaction_temp wstt, wsh_delivery_details_ob_grp_v wdd
WHERE wstt.delivery_id = p_delivery_id
AND wdd.delivery_detail_id = wstt.delivery_detail_id
AND wdd.released_status = 'Y'
GROUP BY wdd.inventory_item_id, wdd.revision;
DEBUG('LPN contains items not belonging to the delivery.', 'Update_shipped_Quantity');
DEBUG('Cannot ship', 'Update_shipped_Quantity');
DEBUG('LPN has more items than Delivery Details', 'Update_Shipped_Quantity');
DEBUG('Some Items are not assigned to delivery details', 'Update_Shipped_Quantity');
SELECT wdd.delivery_detail_id
, wdd.picked_quantity
INTO l_delivery_detail_id
, l_picked_qty
FROM wms_shipping_transaction_temp wstt, wsh_delivery_details_ob_grp_v wdd
WHERE wstt.delivery_id = p_delivery_id
AND wstt.inventory_item_id = l_item_id
AND wdd.delivery_detail_id = wstt.delivery_detail_id
AND wdd.inventory_item_id = wstt.inventory_item_id
AND NVL(wdd.revision, '#') = NVL(l_revision, '#')
AND NVL(wdd.lot_number, '#') = NVL(l_lot_number, '#')
AND ROWNUM = 1;
wsh_delivery_details_pub.update_shipping_attributes(
p_api_version_number => 1.0
, p_source_code => 'OE'
, p_init_msg_list => l_init_msg_list
, p_commit => l_commit
, p_changed_attributes => l_shipping_attr
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
DEBUG('after calling Update_shipping_attributes', 'Update_shipped_Quantity');
DEBUG('return error from update shipping attributes 2', 'Update_shipped_Quantity');
DEBUG('return unexpected error from update shipping attributes', 'Update_shipped_Quantity');
DEBUG('Shipping attributes updated successfully');
DEBUG('Execution Error in Update_shipped_Quantity', 'Update_shipped_Quantity');
DEBUG('Could not update shipping attributes', 'Update_shipped_Quantity');
DEBUG('Unexpected Error in Update_shipped_Quantity:', 'Update_shipped_Quantity');
DEBUG('Could not update shipping attributes', 'Update_shipped_Quantity');
DEBUG('Could not update shipping attributes', 'Update_shipped_Quantity');
END update_shipped_quantity;
SELECT DISTINCT NVL(parent_lpn_id, outermost_lpn_id)
FROM wms_license_plate_numbers
WHERE organization_id = p_organization_id
AND outermost_lpn_id = p_outermost_lpn_id
AND outermost_lpn_id <> lpn_id;
SELECT lpn_id
FROM wms_license_plate_numbers
WHERE organization_id = p_organization_id
AND parent_lpn_id = l_parent_lpn_id;
SELECT delivery_detail_id
FROM wsh_delivery_details_ob_grp_v
WHERE organization_id = p_organization_id
AND lpn_id = l_lpn_id
AND released_status = 'X'; -- For LPN reuse ER : 6845650
SELECT 1
INTO l_dummy_number
FROM wms_shipping_transaction_temp
WHERE parent_lpn_id = l_parent_lpn_id
AND ROWNUM = 1;
l_wsh_lpn_id_tbl.DELETE;
SELECT wda.delivery_id
INTO l_delivery_id
FROM wsh_delivery_assignments_v wda
WHERE wda.delivery_detail_id = l_par_del_det_tab(1)
AND wda.delivery_id IS NOT NULL;
SELECT wnd.planned_flag
INTO l_delivery_planned_flag
FROM wsh_new_deliveries_ob_grp_v wnd
WHERE wnd.delivery_id = l_delivery_id;
SELECT DISTINCT trip_id
INTO l_trip_id_tab(1)
FROM wms_shipping_transaction_temp
WHERE delivery_id = l_delivery_id;
SELECT parent_lpn_id
, COUNT(parent_lpn_id) cnt
FROM wms_shipping_transaction_temp
WHERE delivery_id = p_delivery_id
GROUP BY parent_lpn_id;
SELECT delivery_detail_id
INTO l_par_del_det_id
FROM wsh_delivery_details_ob_grp_v
WHERE lpn_id = l_lpn_cur.parent_lpn_id
AND released_status = 'X'; -- For LPN reuse ER : 6845650
SELECT COUNT(*)
INTO l_count_del_assign
FROM wsh_delivery_assignments_v wda, wsh_delivery_details_ob_grp_v wdd
WHERE wda.parent_delivery_detail_id = l_par_del_det_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND NVL(wdd.container_flag, 'N') = 'N';
SELECT license_plate_number
INTO lpn_name
FROM wms_license_plate_numbers
WHERE lpn_id = l_lpn_cur.parent_lpn_id;
SELECT COUNT(*)
INTO l_count
FROM wsh_delivery_details_ob_grp_v wdd, wsh_delivery_assignments_v wda, mtl_system_items_kfv msik
WHERE wda.delivery_detail_id = wdd.delivery_detail_id
AND NVL(wdd.container_flag, 'N') = 'N'
AND wda.delivery_id = p_delivery_id
AND wdd.inventory_item_id = msik.inventory_item_id
AND wdd.organization_id = msik.organization_id
AND(
(wda.parent_delivery_detail_id IS NULL
AND msik.mtl_transactions_enabled_flag <> 'N')
OR wdd.released_status IS NULL
OR wdd.released_status NOT IN('X', 'Y')
);
SELECT delivery_detail_id
FROM wsh_delivery_details_ob_grp_v
WHERE lpn_id IN(SELECT parent_lpn_id
FROM wms_shipping_transaction_temp
WHERE delivery_id = p_delivery_id
AND direct_ship_flag = 'Y');
SELECT parent_delivery_detail_id
FROM wsh_delivery_assignments_v
WHERE delivery_id = p_delivery_id
AND delivery_detail_id NOT IN(SELECT delivery_detail_id
FROM wsh_delivery_details_ob_grp_v
WHERE lpn_id IN(SELECT parent_lpn_id
FROM wms_shipping_transaction_temp
WHERE delivery_id = p_delivery_id
AND direct_ship_flag = 'Y'));
SELECT NVL(SUM(DECODE(direct_ship_flag, 'Y', 1)), 0) l_flag_y
, NVL(SUM(DECODE(direct_ship_flag, 'N', 1)), 0) l_flag_n
INTO l_flag_y
, l_flag_n
FROM wms_shipping_transaction_temp wstt
WHERE wstt.delivery_id = p_delivery_id
AND dock_appoint_flag = 'N';
SELECT 1
INTO l_num
FROM DUAL
WHERE EXISTS(
SELECT 1
FROM wsh_delivery_assignments_v wda, wsh_delivery_details_ob_grp_v wdd
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = p_delivery_id
AND wdd.released_status = 'Y'
AND NVL(container_flag, 'N') = 'N'
AND NOT EXISTS(
SELECT 1
FROM wms_shipping_transaction_temp wstt
WHERE wstt.delivery_detail_id = wdd.delivery_detail_id
AND wstt.delivery_id = p_delivery_id
AND wstt.direct_ship_flag = 'Y'
AND wstt.dock_appoint_flag = 'N'));
l_parent_del_detail_ids.DELETE;
SELECT nvl(wdd.source_line_set_id,wdd.source_line_id) into l_line_set_id
FROM wsh_delivery_details wdd
WHERE wdd.source_header_id = p_order_header_id
AND wdd.source_line_id= p_order_line_id
AND rownum<2;
SELECT nvl(sum(nvl(picked_quantity,0)),0) INTO l_staged_qty
FROM wsh_delivery_details wdd
WHERE source_header_id = p_order_header_id
AND released_status='Y'
AND nvl(source_line_set_id,source_line_id) = l_line_set_id;
SELECT nvl(SUM(nvl(primary_reservation_quantity,0)),0) INTO l_total_resvd_qty
FROM mtl_reservations
WHERE organization_id= p_org_id
AND nvl(staged_flag,'N') = 'Y'
AND demand_source_line_id in ( SELECT source_line_id
FROM wsh_delivery_details wdd
WHERE source_header_id = p_order_header_id
AND released_status='Y'
AND nvl(source_line_set_id,source_line_id) = l_line_set_id );
g_lpn_contents_tab.DELETE;
g_lpn_contents_lookup_tab.DELETE;
g_checked_delivery_tab.DELETE;
l_processed_lines_tab.DELETE;
g_del_grp_rls_flags.DELETE;
g_del_grp_rls_fld_value.DELETE;
g_del_grp_rls_fld_temp.DELETE;
g_checked_deliveries.DELETE;
l_skipped_line_tab.DELETE;
g_last_updated_by := g_created_by;
g_last_update_login := fnd_global.login_id;
g_last_update_date := g_creation_date;
SELECT sub.reservable_type
INTO l_sub_reservable_type
FROM mtl_secondary_inventories sub
WHERE sub.secondary_inventory_name = (SELECT subinventory_code
FROM wms_license_plate_numbers
WHERE lpn_id = p_lpn_id)
AND organization_id = p_org_id;
' SELECT wlpn.lpn_id ' ||
' , wlpn.subinventory_code ' ||
' , wlpn.locator_id ' ||
' , wlc.inventory_item_id ' ||
' , wlc.revision ' ||
' , wlc.lot_number ' ||
' , DECODE( ' ||
' wlc.uom_code ' ||
' , msi.primary_uom_code, NVL(msn.quantity, wlc.quantity) ' ||
' , GREATEST(inv_convert.inv_um_convert(NULL, NULL, NVL(msn.quantity, wlc.quantity), wlc.uom_code, msi.primary_uom_code, NULL ' ||
' , NULL) ' ||
' , 0) ' ||
' ) quantity ' ||
' , DECODE(msi.revision_qty_control_code, 2, ''TRUE'', ''FALSE'') revision_control ' ||
' , DECODE(msi.lot_control_code, 2, ''TRUE'', ''FALSE'') lot_control ' ||
' , DECODE(msi.serial_number_control_code, 1, ''FALSE'', ''TRUE'') serial_control ' ||
' , msi.serial_number_control_code serial_control_code ' ||
' , msi.reservable_type ' ||
' , msn.end_item_unit_number ';
' , (SELECT lpn_id ' ||
' , revision ' ||
' , lot_number ' ||
' , inventory_item_id ' ||
' , end_item_unit_number ' ||
' , COUNT(1) quantity ' ||
' FROM mtl_serial_numbers ' ||
' WHERE lpn_id IN(SELECT lpn_id ' ||
' FROM wms_license_plate_numbers ' ||
' WHERE organization_id = :p_org_id ' ||
' AND outermost_lpn_id = :p_lpn_id) ' ||
' GROUP BY lpn_id, revision, lot_number, inventory_item_id, end_item_unit_number) msn ' ||
' WHERE msi.inventory_item_id = wlc.inventory_item_id ' ||
' AND wlpn.lpn_id = wlc.parent_lpn_id ' ||
' AND wlpn.outermost_lpn_id = :p_lpn_id ' ||
' AND wlpn.organization_id = :p_org_id ' ||
' AND msi.organization_id = :p_org_id ' ||
' AND msn.lpn_id(+) = wlc.parent_lpn_id ' ||
' AND msn.inventory_item_id(+) = wlc.inventory_item_id ' ||
' AND NVL(msn.lot_number(+),''#NULL#'') = NVL(wlc.lot_number,''#NULL#'') ' ||
' AND NVL(msn.revision(+),''#NULL#'') = NVL(wlc.revision,''#NULL#'') ' ||
' ORDER BY wlc.inventory_item_id ';
SELECT allow_cross_proj_issues
, allow_cross_unitnum_issues
INTO g_cross_project_allowed
, g_cross_unit_allowed
FROM pjm_org_parameters
WHERE organization_id = p_org_id;
SELECT project_id
, task_id
INTO g_project_id
, g_task_id
FROM mtl_item_locations
WHERE inventory_location_id = g_lpn_contents_tab(1).locator_id;
SELECT group_by_customer_flag
, group_by_fob_flag
, group_by_freight_terms_flag
, group_by_intmed_ship_to_flag
, group_by_ship_method_flag
INTO x_group_by_customer_flag
, x_group_by_fob_flag
, x_group_by_freight_terms_flag
, x_group_by_intmed_ship_flag
, x_group_by_ship_method_flag
FROM wsh_shipping_parameters
WHERE organization_id = p_org_id;
l_mtl_reservation_tab.DELETE;
SELECT oel.header_id
, oel.line_id
, oel.inventory_item_id
, oel.item_revision
, DECODE(
oel.order_quantity_uom
, msi.primary_uom_code, oel.ordered_quantity
, GREATEST(
inv_convert.inv_um_convert(NULL, NULL, oel.ordered_quantity, oel.order_quantity_uom, msi.primary_uom_code, NULL
, NULL)
, 0
)
) ordered_quantity
, oel.project_id
, oel.task_id
, msi.serial_number_control_code
, oel.end_item_unit_number
, oel.flow_status_code
INTO l_order_line_rec
FROM oe_order_lines_all oel, mtl_system_items msi
WHERE msi.organization_id = p_org_id
AND oel.inventory_item_id = msi.inventory_item_id
AND line_id = l_mtl_reservation_tab(resv_index).demand_source_line_id;
SELECT 1
INTO l_order_line_status
FROM DUAL
WHERE EXISTS(SELECT delivery_detail_id
FROM wsh_delivery_details_ob_grp_v
WHERE released_status IN('R', 'B')
AND source_line_id = l_order_line_rec.line_id
UNION ALL -- bug 4232713 - Need to consider staged WDD lines for overpicking scenarios
SELECT wdd.delivery_detail_id
FROM wsh_delivery_details_ob_grp_v wdd, wms_direct_ship_temp wds
WHERE wdd.released_status = 'Y'
AND wdd.source_line_id = wds.order_line_id);
l_skipped_line_tab.DELETE(l_order_line_rec.line_id);
--Need to update the reservation.
l_old_upd_resv_rec.reservation_id := l_mtl_reservation_tab(resv_index).reservation_id;
inv_reservation_pub.update_reservation(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_original_rsv_rec => l_old_upd_resv_rec
, p_to_rsv_rec => l_new_upd_resv_rec
, p_original_serial_number => l_upd_dummy_sn
, p_to_serial_number => l_upd_dummy_sn
, p_validation_flag => fnd_api.g_true
);
inv_staged_reservation_util.update_staged_flag(
x_return_status => l_return_status,
x_msg_count => l_msg_count ,
x_msg_data => l_msg_data,
p_reservation_id => l_mtl_reservation_tab(resv_index).reservation_id ,
p_staged_flag => 'Y'
);
DEBUG('Update reservation failed for reservation_id ' || l_old_upd_resv_rec.reservation_id, 'Process_LPN');
DEBUG('Update reservation failed for reservation_id with status U' ||l_old_upd_resv_rec.reservation_id, 'Process_LPN');
SELECT mtl_material_transactions_s.NEXTVAL
INTO g_group_id
FROM DUAL;
DEBUG('Inserting records into WDS', 'Process_LPN');
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_trx_temp_id
FROM DUAL;
INSERT INTO wms_direct_ship_temp
(
GROUP_ID
, organization_id
, dock_door_id
, lpn_id
, order_header_id
, order_line_id
, line_item_id
, processed_quantity
, processed_flag
, serial_required_flag
, transaction_temp_id
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
)
VALUES (
g_group_id
, p_org_id
, p_dock_door_id
, p_lpn_id
, l_processed_lines_tab(l_temp_index).header_id
, l_processed_lines_tab(l_temp_index).line_id
, l_processed_lines_tab(l_temp_index).inventory_item_id
, l_processed_lines_tab(l_temp_index).processed_quantity
, l_processed_lines_tab(l_temp_index).processed_flag
, l_processed_lines_tab(l_temp_index).serial_required_flag
, l_trx_temp_id
, g_creation_date
, g_created_by
, g_last_update_date
, g_last_updated_by
, g_last_update_login
);
inserts processed line record into WDS.
*/
PROCEDURE process_line(
p_lpn_id IN NUMBER
, p_org_id IN NUMBER
, p_dock_door_id NUMBER
, p_order_header_id IN NUMBER
, p_order_line_id IN NUMBER
, p_inventory_item_id IN NUMBER
, p_revision IN VARCHAR2
, p_end_item_unit_number IN VARCHAR2
, p_ordered_quantity IN NUMBER
, p_processed_quantity IN NUMBER
, p_date_requested IN DATE
, p_primary_uom_code IN VARCHAR2
, x_remaining_quantity OUT NOCOPY NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
-- for query reservation
l_qry_reservation_record inv_reservation_global.mtl_reservation_rec_type;
SELECT order_source_id
INTO l_order_source_id
FROM oe_order_headers_all
WHERE header_id = p_order_header_id;
SELECT primary_reservation_quantity
, reservation_id
INTO l_chk_resv_qty
, l_resv_id
FROM mtl_reservations
WHERE demand_source_line_id = p_order_line_id
AND lpn_id = l_lpn_cont_rec.lpn_id
AND NVL(revision, '@@@') = NVL(l_lpn_cont_rec.revision, '@@@')
AND NVL(lot_number, '@@@') = NVL(l_lpn_cont_rec.lot_number, '@@@')
AND demand_source_line_detail IS NULL;
IF l_chk_resv_qty > 0 THEN -- update reservation
IF (l_debug = 1) THEN
DEBUG('l_chk_resv_qty >0 ', 'Process_Line');
DEBUG('The reservation_id to update = ' || l_old_upd_resv_rec.reservation_id, 'Process_Line');
DEBUG('Before call to update reservation', 'Process_Line');
DEBUG('Quantity to update reservation= ' || l_upd_resv_rec.reservation_quantity, 'Process_Line');
inv_reservation_pub.update_reservation(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_original_rsv_rec => l_old_upd_resv_rec
, p_to_rsv_rec => l_upd_resv_rec
, p_original_serial_number => l_upd_dummy_sn
, p_to_serial_number => l_upd_dummy_sn
, p_validation_flag => fnd_api.g_true
, p_over_reservation_flag => 3
);
DEBUG('Update reservation failed for reservation_id ' || l_reservation_id, 'Process_Line');
DEBUG('Update reservation failed for reservation_id with status U' || l_reservation_id, 'Process_Line');
DEBUG('Update reservation is successful ', 'Process_Line');
DEBUG('After call to update reservation', 'Process_Line');
DEBUG('Inserting data into WDS', 'Process_Line');
SELECT order_line_id
INTO l_temp_var
FROM wms_direct_ship_temp
WHERE organization_id = p_org_id
AND dock_door_id = p_dock_door_id
AND lpn_id = p_lpn_id
AND order_header_id = p_order_header_id
AND order_line_id = p_order_line_id;
UPDATE wms_direct_ship_temp
SET processed_flag = 'Y'
, processed_quantity = l_processed_quantity
WHERE organization_id = p_org_id
AND dock_door_id = p_dock_door_id
AND lpn_id = p_lpn_id
AND order_header_id = p_order_header_id
AND order_line_id = p_order_line_id;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_trx_temp_id
FROM DUAL;
INSERT INTO wms_direct_ship_temp
(
GROUP_ID
, organization_id
, dock_door_id
, lpn_id
, order_header_id
, order_line_id
, line_item_id
, processed_quantity
, processed_flag
, serial_required_flag
, transaction_temp_id
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
)
VALUES (
g_group_id
, p_org_id
, p_dock_door_id
, p_lpn_id
, p_order_header_id
, p_order_line_id
, p_inventory_item_id
, l_processed_quantity
, 'Y'
, l_serial_required_flag
, l_trx_temp_id
, g_creation_date
, g_created_by
, g_last_update_date
, g_last_updated_by
, g_last_update_login
);
END; -- update / insert
reservation as soon as the user selects the order line.So commenting the below code.
ELSE
IF l_mtl_resv_tab_count_temp >0 then Added bug4128854 for overshipping staged lines
Bug#4349836.Before inserting a dummy record,check if one is already there for the same LPN
SELECT count(1) INTO l_count_wdst
FROM wms_direct_ship_temp wdst
WHERE wdst.group_id = g_group_id
AND wdst.organization_id=p_org_id
AND wdst.dock_door_id = p_dock_door_id
AND wdst.lpn_id=p_lpn_id
AND wdst.order_header_id = p_order_header_id
AND wdst.order_line_id = p_order_line_id
AND wdst.line_item_id = p_inventory_item_id;
DEBUG('Processed quantity for line is zero. Record with zero inserted into wds', 'Process_Line');
INSERT INTO wms_direct_ship_temp
(
GROUP_ID
, organization_id
, dock_door_id
, lpn_id
, order_header_id
, order_line_id
, line_item_id
, processed_quantity
, processed_flag
, serial_required_flag
, transaction_temp_id
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
)
VALUES (
g_group_id
, p_org_id
, p_dock_door_id
, p_lpn_id
, p_order_header_id
, p_order_line_id
, p_inventory_item_id
, l_processed_quantity
, 'Y'
, l_serial_required_flag
, decode(l_serial_required_flag,'Y',mtl_material_transactions_s.NEXTVAL,l_trx_temp_id)
, g_creation_date
, g_created_by
, g_last_update_date
, g_last_updated_by
, g_last_update_login
);
DEBUG('Processed quantity for line is zero. Record not inserted into wds', 'Process_Line');
SELECT ship_from_location_id
, ship_to_location_id
, customer_id
, intmed_ship_to_location_id
, ship_method_code
, fob_code
, freight_terms_code
FROM wsh_delivery_details_ob_grp_v
WHERE source_header_id = p_header_id
AND source_line_id = p_line_id;
SELECT wda.delivery_id
FROM wsh_delivery_assignments_v wda, wsh_delivery_details_ob_grp_v wdd, wms_direct_ship_temp wds
WHERE wds.organization_id = p_org_id
AND wds.dock_door_id = p_dock_door_id
AND wds.lpn_id = p_lpn_id
AND wds.order_header_id = p_header_id
AND wds.order_line_id = p_line_id
AND wds.order_line_id = wdd.source_line_id
AND wds.order_header_id = wdd.source_header_id
AND wdd.delivery_detail_id = wda.delivery_detail_id;
1. Update staged flag of all reservations for all the lines packed into LPN.
2. Stage LPN
3. Update Freight Cost for LPN
*/
/* bug#2798970 */
/*
jaysingh : The initail code of this procedure was doing wrong reservation staging.
If two different LPNs was reserved against one line and we try to ship
one LPN through DS, reservations for both the LPNs was staged. We should
stage reservations only for LPNs which are inner LPNs of the outer LPN
and the outermost LPN itself.
*/
PROCEDURE load_lpn(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_lpn_id IN NUMBER
, p_org_id IN NUMBER
, p_dock_door_id IN NUMBER
) IS
--
TYPE number_table IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
SELECT wds.order_header_id
, wds.order_line_id
FROM wms_direct_ship_temp wds
WHERE wds.organization_id = p_org_id
AND wds.dock_door_id = p_dock_door_id
AND wds.lpn_id = p_lpn_id;
SELECT lpn_id
FROM wms_license_plate_numbers
WHERE outermost_lpn_id = p_lpn_id
AND organization_id = p_org_id;
l_lpn_ids_tab.DELETE;
inv_staged_reservation_util.update_staged_flag(
x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_reservation_id => l_mtl_reservation_tab(l_rec).reservation_id
, p_staged_flag => 'Y'
);
DEBUG('Update_staged_flag failed with error', 'Load_LPN');
DEBUG('Update_staged_flag failed with exception', 'Load_LPN');
wms_direct_ship_pvt.update_freight_cost(x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data
, p_lpn_id => p_lpn_id);
DEBUG('Update Freight Cost API failed with status E ', 'Load_LPN');
DEBUG('Update Freight Cost failed with status U', 'Load_LPN');
DEBUG('Update freight cost completed', 'Load_LPN');
update the existing reservation else create new reservation.
*/
PROCEDURE perform_overship_distribution(
p_lpn_id IN NUMBER
, p_org_id IN NUMBER
, p_dock_door_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
CURSOR loaded_lines(p_item_id IN NUMBER) IS
SELECT wds.order_header_id
, wds.order_line_id
, oel.item_revision revision
, oel.end_item_unit_number
, oel.request_date
, msi.primary_uom_code
FROM wms_direct_ship_temp wds, oe_order_lines_all oel, mtl_system_items_kfv msi
WHERE wds.organization_id = p_org_id
AND wds.GROUP_ID = g_group_id
AND wds.dock_door_id = p_dock_door_id
AND wds.lpn_id = p_lpn_id
AND wds.line_item_id = p_item_id
AND oel.header_id = wds.order_header_id
AND oel.line_id = wds.order_line_id
AND msi.organization_id = wds.organization_id
AND msi.inventory_item_id = wds.line_item_id;
l_lpn_cont_qty_used.DELETE;
SELECT end_item_unit_number
INTO l_end_item_unit_number
FROM oe_order_lines_all
WHERE line_id = l_order_line_id;
SELECT SUM(primary_reservation_quantity)
INTO l_total_resvd_qty
FROM mtl_reservations
WHERE demand_source_header_id = l_mtl_reservation_tab(l_index_r).demand_source_header_id
AND demand_source_line_id = l_order_line_id
AND demand_source_line_detail IS NULL
AND nvl(staged_flag,'N') = 'Y'; --Bug#5262108
SELECT order_source_id
INTO l_order_source_id
FROM oe_order_headers_all
-- WHERE header_id = 64801;
SELECT NVL(SUM(picked_quantity),0) INTO l_staged_qty
FROM wsh_delivery_details
WHERE source_header_id = l_order_header_id
AND source_line_id =l_order_line_id
AND released_status='Y';
DEBUG('Quantity update reservation is ' || l_qty_updt_resv, 'Perform_Overship_Distribution');
DEBUG('The reservation_id to update = ' || l_old_upd_resv_rec.reservation_id, 'Perform_Overship_Distribution');
DEBUG('Before call to update reservation', 'Perform_Overship_Distribution');
DEBUG('Quantity to update reservation= ' || l_upd_resv_rec.reservation_quantity, 'Perform_Overship_Distribution');
inv_reservation_pub.update_reservation(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_original_rsv_rec => l_old_upd_resv_rec
, p_to_rsv_rec => l_upd_resv_rec
, p_original_serial_number => l_upd_dummy_sn
, p_to_serial_number => l_upd_dummy_sn
, p_validation_flag => fnd_api.g_true
, p_over_reservation_flag => 3
);
DEBUG('Update reservation failed ', 'Perform_Overship_Distribution');
DEBUG('Update reservation failed', 'Perform_Overship_Distribution');
DEBUG('Update reservation is successful ', 'Perform_Overship_Distribution');
DEBUG('After call to update reservation', 'Perform_Overship_Distribution');
UPDATE wms_direct_ship_temp
SET processed_quantity =(processed_quantity + l_qty_overship)
WHERE organization_id = p_org_id
AND GROUP_ID = g_group_id
AND lpn_id = p_lpn_id
AND order_line_id = l_order_line_id;
DEBUG('Reservation update completed', 'Perform_Overship_Distribution');
SELECT SUM(primary_reservation_quantity)
INTO l_total_resvd_qty
FROM mtl_reservations
WHERE demand_source_header_id = l_demand_source_header_id
AND demand_source_line_id = l_order_line_id
AND demand_source_line_detail IS NULL
AND NVL(staged_flag,'N') = 'Y' ;
SELECT order_source_id
INTO l_order_source_id
FROM oe_order_headers_all
WHERE header_id = l_loaded_lines_rec.order_header_id;
SELECT NVL(SUM(picked_quantity),0) INTO l_staged_qty
FROM wsh_delivery_details
WHERE source_header_id = l_order_header_id
AND source_line_id =l_order_line_id
AND released_status='Y';
UPDATE wms_direct_ship_temp
SET processed_quantity =(processed_quantity + l_qty_overship)
WHERE organization_id = p_org_id
AND GROUP_ID = g_group_id
AND lpn_id = p_lpn_id
AND order_line_id = l_order_line_id;
SELECT delivery_detail_id
INTO l_delivery_detail_id
FROM wsh_delivery_details_ob_grp_v
WHERE source_header_id = p_order_header_id
AND source_line_id = p_order_line_id
AND ROWNUM = 1;
SELECT DISTINCT delivery_id
FROM wms_shipping_transaction_temp
WHERE /*direct_ship_flag = 'Y'
AND*/ organization_id = p_org_id;
SELECT delivery_id
FROM wsh_new_deliveries_ob_grp_v
WHERE delivery_id = p_del_id
AND status_code IN('CL', 'IT');
SELECT lpn_id
FROM wms_license_plate_numbers
WHERE lpn_id IN(SELECT outermost_lpn_id
FROM wms_shipping_transaction_temp
WHERE delivery_id = p_del_id);
DELETE wms_direct_ship_temp
WHERE lpn_id = l_lpn_cur.lpn_id;
DELETE wms_freight_cost_temp
WHERE lpn_id = l_lpn_cur.lpn_id;
DELETE wms_freight_cost_temp
WHERE delivery_id = l_closed_del.delivery_id;
DELETE wms_freight_cost_temp
WHERE trip_id IN(SELECT DISTINCT trip_id
FROM wms_shipping_transaction_temp
WHERE outermost_lpn_id = l_lpn_cur.lpn_id);
DELETE wms_shipping_transaction_temp
WHERE outermost_lpn_id = l_lpn_cur.lpn_id;
SELECT
t.ORG_ID
,t.LPN_ID
,t.INNER_LPN_ID
,t.INVENTORY_ITEM_ID
,t.REVISION
,t.LOT_NUMBER
,DECODE(t.PICKED_UOM_CODE, msi.primary_uom_code, t.PICKED_QUANTITY
,GREATEST(inv_convert.inv_um_convert(NULL, NULL,
t.PICKED_QUANTITY, t.PICKED_UOM_CODE, msi.primary_uom_code,
NULL, NULL), 0)) PICKED_QUANTITY_IN_PRI_UOM
,t.SECONDARY_UOM_CODE
,t.SECONDARY_QUANTITY
FROM mtl_system_items msi, WMS_DS_CT_WT_GTEMP t
WHERE t.INVENTORY_ITEM_ID = C_INVENTORY_ITEM_ID /* THIS HAS INDEX */
AND t.ORG_ID = C_ORG_ID
AND msi.INVENTORY_ITEM_ID = t.INVENTORY_ITEM_ID
AND msi.ORGANIZATION_ID = t.ORG_ID
AND NVL(t.INNER_LPN_ID, t.LPN_ID) = C_LPN_ID
AND NVL(t.REVISION,'#NULL#') = NVL(C_REVISION,'#NULL#')
AND NVL(t.LOT_NUMBER,'#NULL#') = NVL(C_LOT_NUMBER,'#NULL#')
AND t.SECONDARY_QUANTITY IS NOT NULL;