[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT lpn_context ,
outermost_lpn_id,
license_plate_number
INTO l_lpn_context ,
x_outermost_lpn_id,
x_license_plate_number
FROM wms_license_plate_numbers
WHERE lpn_id = p_lpn_id;
delete wms_wsh_wdd_gtemp;
insert into wms_wsh_wdd_gtemp
(delivery_detail_id,move_order_line_id)
values
( p_delivery_detail_tbl(i).delivery_detail_id,p_delivery_detail_tbl(i).move_order_line_id);
SELECT 1
INTO l_cd_exists
FROM dual
WHERE exists (
SELECT mtrl.line_id
FROM mtl_txn_request_lines mtrl
, mtl_material_transactions_temp mmtt
, wms_wsh_wdd_gtemp wwwg
, mtl_txn_request_headers mtrh
WHERE mmtt.move_order_line_id =
wwwg.move_order_line_id
AND mtrl.line_id = mmtt.move_order_line_id
AND mtrl.header_id = mtrh.header_id
AND mtrh.move_order_type = 6
AND mtrl.backorder_delivery_detail_id IS NOT NULL
AND mtrl.line_status = 7);
select 1
into l_exists
from dual
where exists(
select move_order_line_id
from mtl_material_transactions_temp mmtt
where -- first case, line is loaded into same lpn or has the same carton
mmtt.transfer_lpn_id is not null and -- the line is loaded
mmtt.transfer_lpn_id in -- loaded into a LPN which is included in the inputted lines
(Select nvl(transfer_lpn_id,cartonization_id)
From mtl_material_transactions_temp mmtt1,WMS_WSH_WDD_GTEMP wwwg
Where mmtt1.move_order_line_id = wwwg.move_order_line_id
and nvl(mmtt1.transfer_lpn_id,mmtt1.cartonization_id) is not null)
and not exists( select 1
from WMS_WSH_WDD_GTEMP www
where www.move_order_line_id = mmtt.move_order_line_id)
and mmtt.parent_line_id is null -- excluding the bulk tasks
and mmtt.move_order_line_id is not null
);
select 1
into l_exists
from dual
where exists(
select move_order_line_id
from mtl_material_transactions_temp mmtt
where mmtt.transfer_lpn_id is null
and mmtt.cartonization_id in
(Select nvl(transfer_lpn_id,cartonization_id)
From mtl_material_transactions_temp mmtt1,WMS_WSH_WDD_GTEMP wwwg
Where mmtt1.move_order_line_id = wwwg.move_order_line_id
and nvl(mmtt1.transfer_lpn_id,mmtt1.cartonization_id) is not null)
and not exists( select 1
from WMS_WSH_WDD_GTEMP www
where www.move_order_line_id = mmtt.move_order_line_id)
and mmtt.parent_line_id is null -- excluding the bulk tasks
and mmtt.move_order_line_id is not null
);
l_shipping_in_rec.action_code := 'UPDATE';
print_debug('Calling Create_Update_Delivery_Detail deldet count='||l_shipping_attr.COUNT );
WSH_INTERFACE_EXT_GRP.Create_Update_Delivery_Detail (
p_api_version_number => 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 => l_shipping_attr
, p_IN_rec => l_shipping_in_rec
, x_OUT_rec => l_shipping_out_rec );
print_debug('Error calling Create_Update_Delivery_Detail');
--Therefore, entries will have been deleted from the temp table, and this
--procedure will not fail.
-- MRANA : 4576909: The above is not true anymore.. we are now going to
-- update WSTT with the new Trip id or null depending on the action code.
SELECT DISTINCT trip_id
INTO l_trip_id
FROM wms_shipping_transaction_temp
WHERE delivery_id = p_dlvy_trip_tbl(i).delivery_id;
/* NCR Bug : 4576909 : Instead of returning error, update WSTT
* p_dlvy_trip_tbl(i).return_status := 'E';
UPDATE wms_shipping_transaction_temp
SET trip_id = p_dlvy_trip_tbl(i).trip_id
WHERE delivery_id = p_dlvy_trip_tbl(i).delivery_id;
print_debug(l_procname || ': wstt.l_trip_id: updated ' );
print_debug(l_procname || ': wstt could not be updated: ' || SQLCODE ||':' || SQLERRM);
/* NCR Bug : 4576909 : Instead of returning error, update WSTT
p_dlvy_trip_tbl(i).return_status := 'E';
UPDATE wms_shipping_transaction_temp
SET trip_id = NULL
WHERE delivery_id = p_dlvy_trip_tbl(i).delivery_id;
print_debug(l_procname || ': wstt.l_trip_id: updated ' );
print_debug(l_procname || ': wstt could not be updated: ' || SQLCODE ||':' || SQLERRM);
IF p_action = wms_shipping_interface_grp.g_action_update THEN
IF NOT fnd_api.compatible_api_call(1.0
,p_api_version
,'PROCESS_SERIAL_NUMBER'
,g_pkg_name) THEN
print_debug('PROCESS_SERIAL_NUMBER: Incompatible API call');
print_debug('PROCESS_SERIAL_NUMBER: Update MSN table');
UPDATE mtl_serial_numbers
SET lpn_id = l_lpn_id_tbl(i)
,last_update_date = Sysdate
,last_updated_by = l_user_id
WHERE inventory_item_id = l_inventory_item_id_tbl(i)
AND serial_number = l_serial_number_tbl(i)
AND current_organization_id = l_current_org_id_tbl(i);
print_debug('PROCES_SERIAL_NUMBER: Total rows updated: ' || SQL%ROWCOUNT);
print_debug('PROCESS_SERIAL_NUMBER: Row ' || i || ' updated ' || SQL%bulk_rowcount(i) || ' rows.');
SELECT 1
INTO l_delivery_status
FROM dual
WHERE EXISTS ( SELECT lpn_context
FROM wms_license_plate_numbers wlpn
, wms_shipping_transaction_temp wstt
WHERE wlpn.lpn_id = wstt.outermost_lpn_id
AND wstt.delivery_id = x_dlvy_trip_tbl(i).delivery_id
AND wlpn.lpn_context = 9 );
select 'Y' into l_return from dual where exists
(select 1 from wms_wp_wave_lines
where delivery_detail_id = p_delivery_detail_id
and nvl(remove_from_wave_flag, 'N') <> 'Y');
select wwl.wave_header_id
,wwl.wave_line_id
,wwl.wave_line_source
,wwl.wave_line_status
,wwl.organization_id
,wwl.demand_source_header_id
,wwl.remove_from_wave_flag
,wwl.planned_fill_rate
,wwl.crossdock_quantity
,wwl.crossdock_uom
,wdd.requested_quantity --needed to calculate new crossdock quantity...
from wms_wp_wave_lines wwl, wsh_delivery_details wdd
where wdd.delivery_detail_id = p_orig_delivery_detail_id
and wdd.delivery_detail_id = wwl.delivery_detail_id
and wwl.organization_id = wdd.organization_id
and nvl(remove_from_wave_flag, 'N') <> 'Y';
update wms_wp_wave_lines
set crossdock_quantity = requested_quantity
where wave_line_id = l_split_from_wave_line.wave_line_id;
insert into wms_wp_wave_lines
(WAVE_HEADER_ID,
WAVE_LINE_ID,
WAVE_LINE_SOURCE,
WAVE_LINE_STATUS,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
DELIVERY_DETAIL_ID,
ORGANIZATION_ID,
demand_source_header_id,
REMOVE_FROM_WAVE_FLAG,
planned_fill_rate,
crossdock_quantity,
crossdock_uom)
values
(l_split_from_wave_line.wave_header_id,
WMS_WP_WAVE_LINES_S.NEXTVAL,
l_split_from_wave_line.WAVE_LINE_SOURCE,
l_split_from_wave_line.WAVE_LINE_STATUS,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
p_new_delivery_detail_id,
l_split_from_wave_line.organization_id,
l_split_from_wave_line.demand_source_header_id, --???????? Demand Source Header Id
l_split_from_wave_line.remove_from_wave_flag,
l_split_from_wave_line.planned_fill_rate,
l_new_crossdock_quantity,
l_split_from_wave_line.crossdock_uom)
returning WAVE_LINE_ID into l_new_wave_line_id;
wms_wave_planning_pvt.update_line_pick_fill_rate(l_new_wave_line_id);
wms_wave_planning_pvt.update_line_pick_fill_rate(l_split_from_wave_line.wave_line_id);
wms_wave_planning_pvt.update_line_actual_fill_rate(l_new_wave_line_id);
wms_wave_planning_pvt.update_line_actual_fill_rate(l_split_from_wave_line.wave_line_id);