[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
fnd_msg_pub.delete_msg(p_msg_index=>i);
debug('Deleted message at position: ' || i,'process_mobile_msg');
select 'Y'
into lpn_loaded
from dual
where exists (select 1
from wms_shipping_transaction_temp
where dock_door_id = p_dock_door_id
and dock_appoint_flag = p_dock_appoint_flag
and organization_id = p_organization_id
and nvl(direct_ship_flag,'N') = p_direct_ship_flag );
SELECT 1
FROM wms_dock_appointments_b wda, mtl_item_locations_kfv milk,
wsh_delivery_legs_ob_grp_v wdl, wsh_Trip_stops_ob_grp_v wts, mtl_item_locations_kfv milk1,wsh_trips wt
WHERE wda.dock_id = milk.inventory_location_id (+)
and wda.organization_id = milk.organization_id (+)
and wda.trip_stop = wdl.pick_up_stop_id (+)
and wdl.pick_up_stop_id = wts.stop_id (+)
and wt.trip_id (+) = wts.trip_id
and wda.start_time <= SYSDATE
and wda.end_time > SYSDATE
and wts.status_code = 'OP'
and milk.organization_id = p_organization_id
and milk1.organization_id (+) = wda.organization_id
and milk1.inventory_location_id (+) = wda.staging_Lane_Id
and inv_project.get_locsegs(milk.inventory_location_id, milk.organization_id) like (p_dock_door)
and nvl(milk.disable_date,sysdate+1) > sysdate
and rownum<2; --We need to just make sure that there exists a row, atleast.
SELECT 1
FROM wms_dock_appointments_b wda, mtl_item_locations_kfv milk,
wsh_delivery_legs_ob_grp_v wdl,wsh_Trip_stops_ob_grp_v wts, mtl_item_locations_kfv milk1,wsh_trips wt
WHERE wda.dock_id = milk.inventory_location_id (+)
and wda.organization_id = milk.organization_id (+)
and wda.trip_stop = wdl.pick_up_stop_id (+)
and wdl.pick_up_stop_id = wts.stop_id (+)
and wt.trip_id (+) = wts.trip_id
and wda.end_time <= SYSDATE
and wts.status_code = 'OP'
and milk.organization_id = p_organization_id
and milk1.organization_id (+) = wda.organization_id
and milk1.inventory_location_id (+) = wda.staging_Lane_Id
and inv_project.get_locsegs(milk.inventory_location_id, milk.organization_id) like (p_dock_door)
AND nvl(milk.disable_date,sysdate+1) > sysdate
ANd rownum<2; --Just Checking the existance.
select distinct
wda.dock_id
, inv_project.get_locsegs(milk.inventory_location_id, milk.organization_id)
milk_concatenated_segments
, wda.dock_appointment_id
, wda.organization_id
, wda.trip_stop
, wts.trip_id
, milk1.subinventory_code
, milk1.concatenated_segments
, is_loaded(p_organization_id,wda.dock_id,'Y')
, wt.name trip_name
from wms_dock_appointments_b wda
, mtl_item_locations_kfv milk
, wsh_delivery_legs_ob_grp_v wdl
, wsh_Trip_stops_ob_grp_v wts
, mtl_item_locations_kfv milk1
,wsh_trips wt
where wda.dock_id = milk.inventory_location_id (+)
and wda.organization_id = milk.organization_id (+)
and wda.trip_stop = wdl.pick_up_stop_id (+)
and wdl.pick_up_stop_id = wts.stop_id (+)
and wt.trip_id (+) = wts.trip_id
and wda.start_time <= SYSDATE
and wda.end_time > SYSDATE
and wts.status_code = 'OP'
and milk.organization_id = p_organization_id
and milk1.organization_id (+) = wda.organization_id
and milk1.inventory_location_id (+) = wda.staging_Lane_Id
and inv_project.get_locsegs(milk.inventory_location_id, milk.organization_id) like (p_dock_door)
AND nvl(milk.disable_date,sysdate+1) > sysdate
order by milk_concatenated_segments;
SELECT dock_id , milk_concatenated_segments, dock_appointment_id,organization_id,
trip_stop, trip_id, subinventory_code,concatenated_segments , is_loaded , trip_name
FROM (
SELECT distinct wda.dock_id,
inv_project.get_locsegs(milk.inventory_location_id, milk.organization_id) milk_concatenated_segments,
wda.dock_appointment_id, wda.organization_id, wda.trip_stop, wts.trip_id,
milk1.subinventory_code, milk1.concatenated_segments ,
WMS_SHIPPING_TRANSACTION_PUB.is_loaded(p_organization_id,wda.dock_id,'Y') is_loaded ,
wt.name trip_name , ( SYSDATE - wda.end_time ) last_schedule_time_diff
FROM wms_dock_appointments_b wda, mtl_item_locations_kfv milk, wsh_delivery_legs_ob_grp_v wdl,
wsh_Trip_stops_ob_grp_v wts, mtl_item_locations_kfv milk1,wsh_trips wt
WHERE wda.dock_id = milk.inventory_location_id (+)
and wda.organization_id = milk.organization_id (+)
and wda.trip_stop = wdl.pick_up_stop_id (+)
and wdl.pick_up_stop_id = wts.stop_id (+)
and wt.trip_id (+) = wts.trip_id
and wda.end_time <= SYSDATE
and wts.status_code = 'OP'
and milk.organization_id = p_organization_id
and milk1.organization_id (+) = wda.organization_id
and milk1.inventory_location_id (+) = wda.staging_Lane_Id
and inv_project.get_locsegs(milk.inventory_location_id, milk.organization_id) like (p_dock_door)
and nvl(milk.disable_date,sysdate+1) > sysdate
ORDER BY 11 ASC
)
WHERE ROWNUM<2; --We need only the last recent apptmt.
SELECT dock_id , milk_concatenated_segments, dock_appointment_id,organization_id,
trip_stop, trip_id, subinventory_code,concatenated_segments , is_loaded , trip_name
FROM (
SELECT distinct wda.dock_id,
inv_project.get_locsegs(milk.inventory_location_id, milk.organization_id) milk_concatenated_segments,
wda.dock_appointment_id, wda.organization_id, wda.trip_stop, wts.trip_id,
milk1.subinventory_code, milk1.concatenated_segments ,
WMS_SHIPPING_TRANSACTION_PUB.is_loaded(p_organization_id,wda.dock_id,'Y') is_loaded ,
wt.name trip_name , ( wda.start_time - SYSDATE) next_schedule_time_diff
FROM wms_dock_appointments_b wda, mtl_item_locations_kfv milk, wsh_delivery_legs_ob_grp_v wdl,
wsh_Trip_stops_ob_grp_v wts, mtl_item_locations_kfv milk1,wsh_trips wt
WHERE wda.dock_id = milk.inventory_location_id (+)
and wda.organization_id = milk.organization_id (+)
and wda.trip_stop = wdl.pick_up_stop_id (+)
and wdl.pick_up_stop_id = wts.stop_id (+)
and wt.trip_id (+) = wts.trip_id
and wda.start_time > SYSDATE
and wts.status_code = 'OP'
and milk.organization_id = p_organization_id
and milk1.organization_id (+) = wda.organization_id
and milk1.inventory_location_id (+) = wda.staging_Lane_Id
and inv_project.get_locsegs(milk.inventory_location_id, milk.organization_id) like (p_dock_door)
and nvl(milk.disable_date,sysdate+1) > sysdate
ORDER BY 11 ASC
)
WHERE ROWNUM<2; --We need only one (the immediate next apptmt).
select milk.inventory_location_id,
inv_project.get_locsegs(milk.inventory_location_id, milk.organization_id)
milk_concatenated_segments
,0, milk.organization_id,
0,0,'','',is_loaded(p_organization_id,milk.inventory_location_id,'N')
,''
from mtl_item_locations_kfv milk
where inventory_location_type = 1
AND milk.organization_id = p_organization_id
AND inv_project.get_locsegs(milk.inventory_location_id, milk.organization_id) like (p_dock_door)
--AND segment19 IS NULL -- Bug 5336849, As a release policy
-- the code should not depend on FF view during patch installation
-- so changing the logic
AND nvl(milk.physical_location_id,milk.inventory_location_id) = milk.inventory_location_id
AND nvl(milk.disable_date,sysdate+1) > sysdate
ORDER BY milk_concatenated_segments;
SELECT msnt.fm_serial_number
FROM mtl_serial_numbers_temp msnt
WHERE transaction_temp_id = p_transaction_temp_id;
select wdd.delivery_detail_id
, wdd.inventory_item_id
, wdd.subinventory
, wdd.locator_id
, wdd.lot_number
, wdd.serial_number
, wdd.transaction_temp_id
from wms_license_plate_numbers wlpn
, wsh_delivery_details_ob_grp_v wdd
, wsh_delivery_assignments_v wda
, wsh_delivery_details_ob_grp_v wdd2
where wlpn.lpn_id = wdd2.lpn_id
and wlpn.outermost_lpn_id = p_lpn_id
and wdd2.lpn_id is not null -- for performance, bug 2418639
and wda.parent_delivery_detail_id = wdd2.delivery_detail_id
and wda.delivery_detail_id = wdd.delivery_detail_id;
select status_control_flag
into l_trx_status_enabled
from mtl_transaction_types
where transaction_type_id = l_transaction_type_id;
select wdl.delivery_id
from wsh_delivery_legs_ob_grp_v wdl
, wsh_trip_stops_ob_grp_v pickup_stop
, wsh_trip_stops_ob_grp_v dropoff_stop
where wdl.pick_up_stop_id = pickup_stop.stop_id
and wdl.drop_off_stop_id = dropoff_stop.stop_id
and pickup_stop.trip_id = dropoff_stop.trip_id
and pickup_stop.trip_id = p_trip_id;
SELECT wnd.name
, wnd.delivery_id
, nvl(wnd.gross_weight, 0)
, wnd.weight_uom_code
, wnd.waybill
, ' ' trip_name,
INV_SHIPPING_TRANSACTION_PUB.GET_SHIPMETHOD_MEANING(wnd.ship_method_code)
FROM wsh_new_deliveries_ob_grp_v wnd
WHERE wnd.delivery_id = p_delivery_id;
select distinct wda.staging_lane_id, milk.concatenated_segments
from wms_dock_appointments_b wda
, mtl_item_locations_kfv milk
, wsh_trip_Stops_ob_grp_v pickup_stop
where milk.inventory_location_id(+) = wda.staging_lane_id
and milk.organization_id(+) = wda.organization_id
and milk.organization_id = p_organization_id
and milk.subinventory_code = p_sub_code
and wda.dock_appointment_id = p_dock_appointment_id
and wda.trip_stop = pickup_stop.stop_id(+)
and milk.concatenated_segments like (p_staging_lane);
select distinct milk.inventory_location_id, milk.concatenated_segments
from mtl_item_locations_kfv milk
, wms_license_plate_numbers lpn
where milk.inventory_location_id (+) = lpn.locator_id
and milk.organization_id (+) = lpn.organization_id
and milk.organization_id = p_organization_id
and milk.subinventory_code = p_sub_code
and (lpn.lpn_context = wms_globals.lpn_context_inv OR lpn.lpn_context = wms_globals.lpn_context_picked)
and milk.concatenated_segments like (p_staging_lane);
select wdd.delivery_detail_id, wdd.lpn_id
from wsh_delivery_details_ob_grp_v wdd
, wsh_delivery_assignments_v wda
, wsh_delivery_legs_ob_grp_v wdl
, wsh_trip_stops_ob_grp_v pickup_stop
, wsh_trip_stops_ob_grp_v dropoff_stop
where wdd.delivery_detail_id = wda.delivery_detail_id
and wda.delivery_id = wdl.delivery_id
and wdl.pick_up_stop_id = pickup_stop.stop_id
and wdl.drop_off_Stop_id = dropoff_stop.stop_id
and pickup_stop.trip_id = dropoff_Stop.trip_id
and pickup_stop.trip_id = p_trip_id
and wdd.locator_id = p_locator_id
and wdd.organization_id = p_organization_id
and wdd.released_status = 'Y';
select wdd.delivery_detail_id
, lpn.lpn_id
, wdd.inventory_item_id
, wdd.requested_quantity
, wda.delivery_id
, lpn.license_plate_number
, wdd.locator_id
, wdd.released_status
from wsh_delivery_details_ob_grp_v wdd
, wsh_delivery_assignments_v wda
, wms_license_plate_numbers lpn
, wsh_delivery_details_ob_grp_v wdd2
where wdd2.lpn_id = lpn.lpn_id
and wdd2.lpn_id is not null
and wdd2.released_status = 'X' -- For LPN reuse ER : 6845650
and wdd2.delivery_detail_id = wda.parent_delivery_detail_id
and wdd.delivery_detail_id = wda.delivery_detail_id
AND nvl(wdd.container_flag,'N')='N'
AND lpn.outermost_lpn_id = p_lpn_id;
select outermost_lpn_id
into l_outermost_lpn_id
from wms_license_plate_numbers
where lpn_id = l_parent_lpn_id;
select license_plate_number
into l_outermost_lpn
from wms_license_plate_numbers
where lpn_id = l_outermost_lpn_id;
SELECT name, status_code
INTO l_delivery_name, l_delivery_status_code
FROM wsh_new_deliveries_ob_grp_v
WHERE delivery_id = l_delivery_id;
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 = l_delivery_id
AND wdl.pick_up_stop_id = wts.stop_id;
debug( 'inserting WSTT.. ', 'Populate_WSTT');
INSERT INTO wms_shipping_transaction_temp
(organization_id,
dock_door_id,
trip_id,
trip_name,
delivery_id,
delivery_name,
delivery_detail_id,
parent_lpn_id,
parent_lpn,
outermost_lpn_id,
outermost_lpn,
inventory_item_id,
staging_lane_id,
requested_quantity,
dock_appoint_flag,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
ORIG_RELEASE_STATUS,
DIRECT_SHIP_FLAG
) values
(
p_organization_id,
p_dock_door_id,
l_trip_id,
null,
l_delivery_id,
l_delivery_name,
l_delivery_detail_id,
l_parent_lpn_id,
l_parent_lpn,
l_outermost_lpn_id,
l_outermost_lpn,
l_inventory_item_id,
l_locator_id,
l_requested_quantity,
l_dock_appoint_flag,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
l_released_status,
p_direct_ship_flag
);
debug( 'Delivery status is not open ..not insertingWSTT ', 'Populate_WSTT');
select name
into p_name
from wsh_new_deliveries_ob_grp_v
where delivery_id = p_delivery_id;
select distinct lpn.outermost_lpn_id
, wlpn.license_plate_number
, wnd.delivery_id,wnd.name
, nvl(wdd.load_seq_number,0) as load_seq_num
from wms_license_plate_numbers lpn
, wms_license_plate_numbers wlpn
, wsh_new_deliveries_ob_grp_v wnd
, wsh_delivery_legs_ob_grp_v wdl
, wsh_delivery_details_ob_grp_v wdd
, wsh_delivery_assignments_v wda
, wsh_delivery_details_ob_grp_v wdd2
where wdl.pick_up_stop_id = p_trip_stop_id
and wdl.delivery_id = wnd.delivery_id
and wnd.status_code in ('OP', 'PA')
and wnd.delivery_id = wda.delivery_id
and wdd.delivery_detail_id = wda.delivery_detail_id
and wdd2.delivery_detail_id = wda.parent_delivery_detail_id
and wdd2.lpn_id is not null -- for performance, bug 2418639
and wdd2.lpn_id = lpn.lpn_id
and lpn.outermost_lpn_id = wlpn.lpn_id
and ( wlpn.lpn_context = wms_container_pvt.lpn_context_picked
OR wlpn.lpn_context = wms_container_pvt.lpn_loaded_in_stage)
and wnd.status_code in ('OP', 'PA')
and wdd.released_status = 'Y'
and (wdd.inv_interfaced_flag <> 'Y' or wdd.inv_interfaced_flag is null )
and wlpn.license_plate_number like (p_lpn)
order by load_seq_num, wlpn.license_plate_number;
select distinct lpn.outermost_lpn_id
, wlpn.license_plate_number as license_plate_number
, nvl(wda.delivery_id,0)
, get_delivery_name(wda.delivery_id)
, nvl(wdd.load_seq_number,0) as load_seq_num
from wsh_delivery_details_ob_grp_v wdd
, wsh_delivery_assignments_v wda
, wms_license_plate_numbers lpn
,wms_license_plate_numbers wlpn
,wsh_new_deliveries_ob_grp_v wndv
where wdd.delivery_detail_id = wda.delivery_detail_id
and wdd.lpn_id is not null
and wdd.lpn_id = lpn.outermost_lpn_id
and wdd.released_status = 'X' -- For LPN reuse ER : 6845650
and lpn.outermost_lpn_id = wlpn.lpn_id
and (wlpn.lpn_context = wms_container_pvt.lpn_context_picked
OR wlpn.lpn_context = wms_container_pvt.lpn_loaded_in_stage)
and lpn.organization_id = p_organization_id
and (wdd.inv_interfaced_flag <> 'Y' or wdd.inv_interfaced_flag is null )
and wlpn.license_plate_number like (p_lpn)
and wda.delivery_id IS NOT NULL
and wda.delivery_id = wndv.delivery_id
and wndv.status_code in ('OP', 'PA')
UNION
select distinct lpn.outermost_lpn_id
, wlpn.license_plate_number as license_plate_number
, nvl(wda.delivery_id,0)
, get_delivery_name(wda.delivery_id)
, nvl(wdd.load_seq_number,0) as load_seq_num
from wsh_delivery_details_ob_grp_v wdd
, wsh_delivery_assignments_v wda
, wms_license_plate_numbers lpn
, wms_license_plate_numbers wlpn
where wdd.delivery_detail_id = wda.delivery_detail_id
and wdd.lpn_id is not null
and wdd.lpn_id = lpn.outermost_lpn_id
and wdd.released_status = 'X' -- For LPN reuse ER : 6845650
and lpn.outermost_lpn_id = wlpn.lpn_id
and (wlpn.lpn_context = wms_container_pvt.lpn_context_picked
OR wlpn.lpn_context = wms_container_pvt.lpn_loaded_in_stage)
and lpn.organization_id = p_organization_id
and (wdd.inv_interfaced_flag <> 'Y' or wdd.inv_interfaced_flag is null )
and wlpn.license_plate_number like (p_lpn)
and wda.delivery_id IS NULL
UNION
select distinct lpn.outermost_lpn_id
, wlpn.license_plate_number as license_plate_number
, nvl(wda.delivery_id,0)
, get_delivery_name(wda.delivery_id)
, nvl(wdd.load_seq_number,0) as load_seq_num
from wsh_delivery_details_ob_grp_v wdd
, wsh_delivery_assignments_v wda
, wms_license_plate_numbers lpn
,wms_license_plate_numbers wlpn
,wsh_new_deliveries_ob_grp_v wndv
where wdd.delivery_detail_id = wda.delivery_detail_id
and wdd.lpn_id is not null
and wdd.lpn_id = lpn.lpn_id
and wdd.released_status = 'X' -- For LPN reuse ER : 6845650
and lpn.outermost_lpn_id = wlpn.lpn_id
and (wlpn.lpn_context = wms_container_pvt.lpn_context_picked
OR wlpn.lpn_context = wms_container_pvt.lpn_loaded_in_stage)
and lpn.organization_id = p_organization_id
and (wdd.inv_interfaced_flag <> 'Y' or wdd.inv_interfaced_flag is null )
and wlpn.license_plate_number like (p_lpn)
and wda.delivery_id IS NOT NULL
and wda.delivery_id = wndv.delivery_id
and wndv.status_code in ('OP', 'PA')
UNION
select distinct lpn.outermost_lpn_id
, wlpn.license_plate_number as license_plate_number
, nvl(wda.delivery_id,0)
, get_delivery_name(wda.delivery_id)
, nvl(wdd.load_seq_number,0) as load_seq_num
from wsh_delivery_details_ob_grp_v wdd
, wsh_delivery_assignments_v wda
, wms_license_plate_numbers lpn
,wms_license_plate_numbers wlpn
where wdd.delivery_detail_id = wda.delivery_detail_id
and wdd.lpn_id is not null
and wdd.lpn_id = lpn.lpn_id
and wdd.released_status = 'X' -- For LPN reuse ER : 6845650
and lpn.outermost_lpn_id = wlpn.lpn_id
and (wlpn.lpn_context = wms_container_pvt.lpn_context_picked
OR wlpn.lpn_context = wms_container_pvt.lpn_loaded_in_stage)
and lpn.organization_id = p_organization_id
and (wdd.inv_interfaced_flag <> 'Y' or wdd.inv_interfaced_flag is null )
and wlpn.license_plate_number like (p_lpn)
and wda.delivery_id IS NULL
order by load_seq_num, license_plate_number;
select distinct lpn.outermost_lpn_id
, wlpn.license_plate_number
, wnd.delivery_id,wnd.name
from wms_license_plate_numbers lpn
, wms_license_plate_numbers wlpn
, wsh_new_deliveries_ob_grp_v wnd
, wsh_delivery_legs_ob_grp_v wdl
, wsh_delivery_details_ob_grp_v wdd
, wsh_delivery_assignments_v wda
, wsh_delivery_details_ob_grp_v wdd2
where wdl.pick_up_stop_id = p_trip_stop_id
and wdl.delivery_id = wnd.delivery_id
--and wdl.PARENT_DELIVERY_LEG_ID IS NULL -- Added for MDC : if delivery
--is associated to a consol delivery, do not allow to select here
and wnd.status_code in ('OP', 'PA')
and wnd.delivery_id = wda.delivery_id
and wdd.delivery_detail_id = wda.delivery_detail_id
and wdd2.delivery_detail_id = wda.parent_delivery_detail_id
and wdd2.lpn_id is not null -- for performance, bug 2418639
and wdd2.lpn_id = lpn.lpn_id
and lpn.outermost_lpn_id = wlpn.lpn_id
--MR-MDC wlpn.lpn_context <> wms_globals.lpn_loaded_for_shipment
and ( wlpn.lpn_context = wms_container_pvt.lpn_context_picked
OR wlpn.lpn_context = wms_container_pvt.lpn_loaded_in_stage)
-- 5582189 dherring additional criteria added to ensure ship confirmed deliveries
-- do not appear in the lov
and wnd.status_code in ('OP', 'PA')
and wdd.released_status = 'Y'
and (wdd.inv_interfaced_flag <> 'Y' or wdd.inv_interfaced_flag is null )
and wlpn.license_plate_number like (p_lpn)
order by wlpn.license_plate_number;
select distinct lpn.outermost_lpn_id
, wlpn.license_plate_number as license_plate_number
, nvl(wda.delivery_id,0)
, get_delivery_name(wda.delivery_id)
from wsh_delivery_details_ob_grp_v wdd
, wsh_delivery_assignments_v wda
, wms_license_plate_numbers lpn
,wms_license_plate_numbers wlpn
,wsh_new_deliveries_ob_grp_v wndv
where wdd.delivery_detail_id = wda.delivery_detail_id
and wdd.lpn_id is not null -- for performance, bug 2418639
and wdd.lpn_id = lpn.outermost_lpn_id
and wdd.released_status = 'X' -- For LPN reuse ER : 6845650
and lpn.outermost_lpn_id = wlpn.lpn_id
and (wlpn.lpn_context = wms_container_pvt.lpn_context_picked
OR wlpn.lpn_context = wms_container_pvt.lpn_loaded_in_stage)
and lpn.organization_id = p_organization_id
and (wdd.inv_interfaced_flag <> 'Y' or wdd.inv_interfaced_flag is null )
and wlpn.license_plate_number like (p_lpn)
-- 5582189 dherring UNION introduced to avoid cartesian join
and wda.delivery_id IS NOT NULL
and wda.delivery_id = wndv.delivery_id
and wndv.status_code in ('OP', 'PA')
UNION
select distinct lpn.outermost_lpn_id
, wlpn.license_plate_number as license_plate_number
, nvl(wda.delivery_id,0)
, get_delivery_name(wda.delivery_id)
from wsh_delivery_details_ob_grp_v wdd
, wsh_delivery_assignments_v wda
, wms_license_plate_numbers lpn
,wms_license_plate_numbers wlpn
where wdd.delivery_detail_id = wda.delivery_detail_id
and wdd.lpn_id is not null -- for performance, bug 2418639
and wdd.lpn_id = lpn.outermost_lpn_id
and wdd.released_status = 'X' -- For LPN reuse ER : 6845650
and lpn.outermost_lpn_id = wlpn.lpn_id
and (wlpn.lpn_context = wms_container_pvt.lpn_context_picked
OR wlpn.lpn_context = wms_container_pvt.lpn_loaded_in_stage)
and lpn.organization_id = p_organization_id
and (wdd.inv_interfaced_flag <> 'Y' or wdd.inv_interfaced_flag is null )
and wlpn.license_plate_number like (p_lpn)
-- 5582189 dherring UNION introduced to avoid cartesian join
and wda.delivery_id IS NULL
order by license_plate_number;
/* select wstt.trip_id, wstt.delivery_id, wstt.delivery_detail_id, wstt.inventory_item_id, wstt.quantity, wstt.outermost_lpn,
wstt.parent_lpn_id, wstt.parent_lpn, wdd.serial_number,
msik.concatenated_segments, msik.serial_number_control_code, wdd.requested_quantity,
wdd.subinventory, wdd.revision, wdd.locator_id, wdd.lot_number
from wms_shipping_transaction_temp wstt, wsh_delivery_details_ob_grp_v wdd, mtl_system_items_kfv msik
where wstt.delivery_detail_id = wdd.delivery_detail_id
and wstt.outermost_lpn_id = p_outermost_lpn_id
and msik.inventory_item_id = wdd.inventory_item_id
and msik.organization_id = wdd.organization_id
and msik.serial_number_control_code = 6; */
select 0,wda.delivery_id, wdd.delivery_detail_id, wdd.inventory_item_id,wdd.requested_quantity,
wlpn2.license_plate_number,wlpn.lpn_id, wlpn.license_plate_number,wdd.serial_number,
msik.concatenated_segments,
msik.serial_number_control_code, wdd.requested_quantity,wdd.subinventory, wdd.revision,
wdd.locator_id, wdd.lot_number,wdd.picked_quantity
from wms_license_plate_numbers wlpn, wms_license_plate_numbers wlpn2, wsh_delivery_details_ob_grp_v wdd,
mtl_system_items_kfv msik,
wsh_delivery_assignments_v wda, wsh_delivery_details_ob_grp_v wdd2
where wlpn.outermost_lpn_id = p_outermost_lpn_id
and wlpn.lpn_id = wdd2.lpn_id
and wdd2.lpn_id is not null -- for performance, bug 2418639
and wdd2.released_status = 'X' -- For LPN reuse ER : 6845650
and wlpn.outermost_lpn_id = wlpn2.lpn_id
and wdd2.delivery_detail_id = wda.parent_delivery_detail_id
and wda.delivery_detail_id = wdd.delivery_detail_id
and wdd.organization_id = msik.organization_id
and wdd.inventory_item_id = msik.inventory_item_id
and msik.serial_number_control_code = 6
and wdd.transaction_temp_id is null;
select transaction_temp_id
into l_transaction_temp_id
from wsh_delivery_details_ob_grp_v
where delivery_detail_id = l_delivery_detail_id;
select mtl_material_Transactions_s.nextval
into l_InvPCInRecType.transaction_temp_id
from dual;
debug('About to call wsh_interface.update_shipping_attributes','nested_serial_check');
wsh_interface.update_shipping_attributes
(x_return_status => l_return_status,
p_changed_attributes => l_detail_attributes,
p_source_code => 'INV');
debug('wsh_interface.update_shipping_attributes failed'
|| ' with status: ' || l_return_status,'nested_serial_check');
select sum(wdd.requested_quantity), sum(wlc.quantity), wda.delivery_id, wlc.inventory_item_id, wlc.parent_lpn_id
from wsh_delivery_details_ob_grp_v wdd, wsh_delivery_assignments_v wda, wsh_delivery_details_ob_grp_v wdd2,
wms_license_plate_numbers lpn, wms_lpn_contents wlc
where wdd.delivery_detail_id = wda.delivery_detail_id
and wdd2.delivery_detail_id = wda.parent_delivery_detail_id
and wdd2.lpn_id = lpn.lpn_id
and wdd2.released_status = 'X' -- For LPN reuse ER : 6845650
and wlc.parent_lpn_id = lpn.lpn_id
and lpn.outermost_lpn_id = p_outermost_lpn_id
and wlc.inventory_item_id = wdd.inventory_item_id
-- and (wda.delivery_id = p_delivery_id or wda.delivery_id is null)
group by wda.delivery_id, wlc.inventory_item_id, wlc.parent_lpn_id;
select distinct wstt.parent_lpn
into l_parent_lpn
from wms_shipping_transaction_Temp wstt
where wstt.parent_lpn_id = l_parent_lpn_id
and wstt.inventory_item_id = l_inventory_item_id;
select distinct msik.concatenated_segments
into l_item_name
from mtl_system_items_kfv msik
where msik.inventory_item_id = l_inventory_item_id;
select distinct wda.delivery_id
from wsh_delivery_details_ob_grp_v wdd, wsh_delivery_assignments_v wda, wms_license_plate_numbers lpn,
wsh_delivery_details_ob_grp_v wdd2
where lpn.outermost_lpn_id = p_outermost_lpn_id
and wdd2.lpn_id = lpn.lpn_id
and wdd2.released_status = 'X' -- For LPN reuse ER : 6845650
and wdd2.delivery_detail_id = wda.parent_delivery_detail_id
and wdd.delivery_detail_id = wda.delivery_detail_id;
select 1
into x_result
from dual
where exists (select 1
from wms_shipping_transaction_temp
where delivery_id = l_delivery_id
and direct_ship_flag='Y');
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 = l_delivery_id
and wdl.pick_up_stop_id = wts.stop_id;
select 1
into x_result
from dual
where exists (select 1
from wms_shipping_transaction_temp
where trip_id = l_trip_id
and direct_ship_flag='Y');
select distinct wda.delivery_id
from wsh_delivery_details_ob_grp_v wdd, wsh_delivery_assignments_v wda, wms_license_plate_numbers lpn,
wsh_delivery_details_ob_grp_v wdd2
where lpn.outermost_lpn_id = p_outermost_lpn_id
and wdd2.lpn_id = lpn.lpn_id
and wdd2.released_status = 'X' -- For LPN reuse ER : 6845650
and wdd2.lpn_id is not null -- for performance, bug 2418639
and wdd2.delivery_detail_id = wda.parent_delivery_detail_id
and wdd.delivery_detail_id = wda.delivery_detail_id;
select distinct milk.concatenated_segments
from mtl_item_locations_kfv milk,wms_shipping_transaction_temp wstt
where wstt.delivery_id = p_delivery_id
and wstt.organization_id = p_organization_id
and wstt.dock_appoint_flag = 'N'
and wstt.dock_door_id <> p_dock_door_id
and milk.organization_id = p_organization_id
and milk.inventory_location_id =wstt.dock_door_id;
select distinct milk.concatenated_segments
from mtl_item_locations_kfv milk,wms_shipping_transaction_temp wstt
where wstt.trip_id = p_trip_id
and wstt.organization_id = p_organization_id
and wstt.dock_appoint_flag = 'N'
and wstt.dock_door_id <> p_dock_door_id
and milk.organization_id = p_organization_id
and milk.inventory_location_id =wstt.dock_door_id;
select wt.name, milk.concatenated_segments
into x_trip_name, x_loaded_dock_door
from wsh_delivery_legs_ob_grp_v wdl, wsh_trip_stops_ob_grp_v wts,wms_dock_appointments_b wda,
mtl_item_locations_kfv milk, wsh_trips_ob_grp_v wt
where wdl.delivery_id = l_delivery_id
and wdl.pick_up_stop_id = wts.stop_id
and wda.trip_stop = wts.stop_id
and wda.organization_id = p_organization_id
and wda.organization_id = milk.organization_id
and wda.dock_id = milk.inventory_location_id
and wt.trip_id = wts.trip_id;
select name
into x_delivery_name
from wsh_new_deliveries_ob_grp_v
where delivery_id = l_delivery_id;
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 = l_delivery_id
and wdl.pick_up_stop_id = wts.stop_id;
select name
into x_trip_name
from wsh_trips_ob_grp_v
where trip_id = l_trip_id;
select wdd.delivery_detail_id, wdd.source_header_id, wdd.source_line_id
from wms_license_plate_numbers wlpn, wsh_delivery_details_ob_grp_v wdd0, wsh_delivery_assignments_v wda,
wsh_delivery_details_ob_grp_v wdd
where
wlpn.outermost_lpn_id = p_outermost_lpn_id
and wlpn.lpn_id = wdd0.lpn_id
and wdd0.released_status = 'X' -- For LPN reuse ER : 6845650
and wdd0.delivery_detail_id = wda.parent_delivery_detail_id
and wdd.delivery_detail_id = wda.delivery_detail_id
and wdd.lpn_id is null
order by wdd.source_header_id, wdd.source_line_id;
select delivery_detail_id, requested_quantity
from wms_shipping_transaction_Temp
where
outermost_lpn_id = p_outermost_lpn_id;
SELECT lpn_id
FROM WMS_LICENSE_PLATE_NUMBERS
START WITH lpn_id = p_outermost_lpn_id
CONNECT BY parent_lpn_id = PRIOR lpn_id;
select 1
into l_load_before
from DUAL
where exists (
select 1
from wms_shipping_transaction_temp
where outermost_lpn_id = p_outermost_lpn_id
);
select count(distinct wda.delivery_id)
into l_delivery_count
from wsh_delivery_assignments_v wda, wsh_delivery_details_ob_grp_v wdd
, wms_license_plate_numbers wlpn
where wlpn.outermost_lpn_id = p_outermost_lpn_id
and wlpn.lpn_id = wdd.lpn_id
and wdd.lpn_id is not null -- for performance, bug 2418639
and wda.parent_delivery_detail_id = wdd.delivery_detail_id
and wda.delivery_id is not null;
debug('About to delete mmtt/wdt with task type=7 and LPN: ' ||
p_outermost_lpn_id,'LPN_SUBMIT');
DELETE wms_dispatched_tasks wdt
WHERE task_type = 7
AND organization_id = p_organization_id
AND transfer_lpn_id = p_outermost_lpn_id;
IF (l_debug = 1) THEN debug('DELETED WDT FOR LPN: ' || p_outermost_lpn_id,'LPN_SUBMIT'); END IF;
DELETE mtl_material_transactions_temp mmtt
WHERE wms_task_type = 7
AND organization_id = p_organization_id
AND content_lpn_id = p_outermost_lpn_id;
IF (l_debug = 1) THEN debug('DELETED WDT FOR LPN: ' || p_outermost_lpn_id,'LPN_SUBMIT'); END IF;
IF (l_debug = 1) THEN debug('NO Data found to delete MMTT/WDT : ','LPN_SUBMIT' ); END IF;
l_detail_attributes.DELETE;
Debug( 'Update line '||l_detail_attributes(l_index).delivery_detail_id||
' with shipped quantity '|| l_detail_attributes(l_index).shipped_quantity,
'LPN_SUBMIT');
Debug('About to call Shipping API to update the shipped quantity','LPN_SUBMIT');
wsh_delivery_details_pub.update_shipping_attributes
(p_api_version_number => 1.0,
p_init_msg_list => G_TRUE,
p_commit => G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_changed_attributes => l_detail_attributes,
p_source_code => 'OE');
debug('wsh_delivery_details_pub.update_shipping_attributes failed'
|| ' with status: ' || l_return_status, 'LPN_SUBMIT');
UPDATE WMS_LICENSE_PLATE_NUMBERS
SET lpn_context = WMS_Container_PUB.LPN_LOADED_FOR_SHIPMENT,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID
where lpn_id = p_outermost_lpn_id;
UPDATE WMS_LICENSE_PLATE_NUMBERS
SET lpn_context = WMS_Container_PUB.LPN_LOADED_FOR_SHIPMENT,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID
where lpn_id = l_lpn_id.lpn_id;
select distinct delivery_id
from WMS_SHIPPING_TRANSACTION_TEMP
where organization_id = p_organization_id
and dock_door_id = p_dock_door_id
and trip_id = p_trip_id
AND nvl(direct_ship_flag,'N')='N';
select distinct delivery_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 nvl(direct_ship_flag,'N')='N';
SELECT ignore_for_planning, tms_interface_flag
FROM wsh_new_deliveries_ob_grp_v
WHERE delivery_id = l_delivery_id;
select 1
into temp_val
from WMS_SHIPPING_TRANSACTION_TEMP
where organization_id = p_organization_id
and dock_door_id = p_dock_door_id
and trip_id = p_trip_id
and rownum = 1
for update NOWAIT;
select 1
into temp_val
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 rownum = 1
for update NOWAIT;
SELECT wnd.delivery_type
INTO l_delivery_type
FROM wsh_new_deliveries_ob_grp_v wnd
WHERE wnd.delivery_id = p_delivery_id;
select count(distinct delivery_id)
into x_deli_count
from WMS_SHIPPING_TRANSACTION_TEMP
where organization_id = p_organization_id
and dock_door_id = p_dock_door_id
and trip_id = p_trip_id
AND nvl(direct_ship_flag,'N')='N';
select count(distinct delivery_id)
into x_deli_count
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 nvl(direct_ship_flag,'N')='N';
select delivery_detail_id,OUTERMOST_LPN_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 delivery_id is null
and nvl(direct_ship_flag,'N') = p_direct_ship_flag
ORDER BY OUTERMOST_LPN_ID;
select distinct delivery_id
from WMS_SHIPPING_TRANSACTION_TEMP
where outermost_lpn_id = p_lpn_id;
select DISTINCT WSTT.delivery_id
from WMS_SHIPPING_TRANSACTION_TEMP wstt
,wsh_new_deliveries_ob_grp_v wnd
where wstt.delivery_id = wnd.delivery_id
AND wnd.status_code ='OP'
AND WSTT.organization_id = p_organization_id
and WSTT.dock_door_id = p_dock_door_id
and wstt.dock_appoint_flag = 'N'
and nvl(wstt.direct_ship_flag,'N') = 'Y';
select wda.delivery_id, wnd.name
into l_delivery_id, l_delivery_name
from wsh_delivery_assignments_v wda, wsh_new_deliveries_ob_grp_v wnd
where wda.delivery_id = wnd.delivery_id
and wda.delivery_detail_id = l_delivery_detail_id;
update wms_shipping_transaction_temp
set delivery_id = l_delivery_id,
delivery_name = l_delivery_name,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID
where delivery_detail_id = l_delivery_detail_id;
l_del_detail_id.DELETE;
select wda.delivery_id, wnd.name
into l_delivery_id, l_delivery_name
from wsh_delivery_assignments_v wda, wsh_new_deliveries_ob_grp_v wnd
where wda.delivery_id = wnd.delivery_id
and wda.delivery_detail_id = l_del_rows(l_del_count);
update wms_shipping_transaction_temp
set delivery_id = l_delivery_id,
delivery_name = l_delivery_name,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID
where outermost_lpn_id = (SELECT outermost_lpn_id FROM wms_shipping_transaction_temp
WHERE delivery_detail_id = l_del_rows(l_del_count));
SELECT distinct wstt.delivery_name, wnd.delivery_id, wnd.gross_weight, wnd.weight_uom_code,
wnd.waybill,
INV_SHIPPING_TRANSACTION_PUB.GET_SHIPMETHOD_MEANING(wnd.ship_method_code)
FROM wsh_new_deliveries wnd,wms_shipping_transaction_temp wstt
WHERE wnd.delivery_id = wstt.delivery_id
and wstt.trip_id = p_trip_id
and wstt.dock_door_id = p_dock_door_id
and wstt.organization_id = p_organization_id
and nvl(wstt.direct_ship_flag,'N') = 'N'
and wstt.delivery_name like (p_delivery_name)
order by wstt.delivery_name;
SELECT distinct wstt.delivery_name, wnd.delivery_id, wnd.gross_weight, wnd.weight_uom_code,
wnd.waybill,
INV_SHIPPING_TRANSACTION_PUB.GET_SHIPMETHOD_MEANING(wnd.ship_method_code)
FROM wsh_new_deliveries wnd,wms_shipping_transaction_temp wstt
WHERE wnd.delivery_id = wstt.delivery_id
and wstt.dock_appoint_flag = 'N'
and nvl(wstt.direct_ship_flag,'N') = 'N'
and wstt.dock_door_id = p_dock_door_id
and wstt.organization_id = p_organization_id
and wstt.delivery_name like (p_delivery_name)
order by wstt.delivery_name;
PROCEDURE update_trip(p_delivery_id IN NUMBER DEFAULT NULL
,p_trip_id IN NUMBER DEFAULT NULL
,p_ship_method_code IN VARCHAR2
,x_return_status OUT nocopy VARCHAR2
,x_msg_data OUT nocopy VARCHAR2
,x_msg_count OUT nocopy number) IS
l_trip_id NUMBER := null;
|| ' p_ship_method_code: ' || p_ship_method_code,'update_trip');
debug('delivery_id and trip_id both null. Need one of them to update a trip','update_trip');
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
AND wdl.pick_up_stop_id=wts.stop_id
AND ROWNUM=1;
debug('Cannot find trip to update','update_trip');
debug('Trip id to be updated: ' || l_trip_id,'update_trip');
debug('ship method code: ' || p_ship_method_code,'update_trip');
debug('Calling WSH_TRIPS_PUB.create_update_trip','update_trip');
l_trip_info.last_update_date := SYSDATE;
l_trip_info.last_updated_by :=FND_GLOBAL.USER_ID;
l_trip_info.last_update_login :=FND_GLOBAL.USER_ID;
WSH_TRIPS_PUB.Create_Update_Trip
(p_api_version_number => 1.0
,p_init_msg_list => FND_API.G_TRUE
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_action_code => 'UPDATE'
,p_trip_info => l_trip_info
,x_trip_id => l_trip_id
,x_trip_name => l_trip_name);
debug('return status from WSH_TRIPS_PUB.create_update_trip: ' || x_return_status,'update_trip');
debug('Shipping msg count: ' || x_msg_count,'update_trip');
debug('Shipping error msg: ' || x_msg_data,'update_trip');
debug('Committing change','update_trip');
debug('Other exceptions raised!','update_trip');
debug('SQLERRM: ' || SQLERRM,'update_trip');
END update_trip;
PROCEDURE UPDATE_DELIVERY(p_delivery_id IN NUMBER,
p_gross_weight IN NUMBER,
p_weight_uom IN VARCHAR2,
p_waybill IN VARCHAR2,
p_bol IN VARCHAR2,
p_ship_method_code IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
--l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
SELECT ignore_for_planning, tms_interface_flag
FROM wsh_new_deliveries_ob_grp_v
WHERE delivery_id = p_delivery_id;
debug('Inside update_delivery','UPDATE_DELIVERY');
* We cannot update the ship method.
* For non-G-LOG cases also,
* We need not pass the new ship method to this API for an UPDATE,
* instead we will pass it to the ship confirm API and that will take care of
* updating it at all levels, as the case may be.
* BUT, we need to update it at delivery level for LPN Ship, where
* API: SHIP_CONFIRM_LPN_DELIVERIES depends on the delivery level shipmenthod
*/
IF wsh_util_core.gc3_is_installed = 'Y' THEN
IF (l_debug = 1) THEN
debug('G-Log Changes: G-Log installed', 'update_delivery');
debug('No WDDs found for the delivery created ', 'update_delivery');
debug('l_ignore_for_planning : ' || l_ignore_for_planning, 'update_delivery');
debug('l_tms_interface_flag : ' || l_tms_interface_flag, 'update_delivery');
debug('l_delivery_info.ship_method_code ' || l_delivery_info.ship_method_code,'UPDATE_DELIVERY');
debug('l_delivery_info.ship_method_name ' || l_delivery_info.ship_method_name,'UPDATE_DELIVERY');
SELECT wts.trip_id,wt.ship_method_code
INTO l_trip_id,l_trip_ship_method_code
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;
debug('Delivery does not belong to any trip','UPDATE_DELIVERY');
debug('About to call wsh_deliveries_pub.create_update_delivery with'
|| ' gross_weight: ' || l_delivery_info.gross_weight
|| ' waybill : ' || l_delivery_info.waybill
|| ' ship method code: ' || l_delivery_info.ship_method_code
|| ' weight uom code: ' || l_delivery_info.weight_uom_code
|| ' delivery ID: ' || l_delivery_info.delivery_id, 'UPDATE_DELIVERY');
wsh_deliveries_pub.create_update_delivery
(p_api_version_number => 1.0,
p_init_msg_list => fnd_api.g_false,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_action_code => 'UPDATE',
p_delivery_info => l_delivery_info,
x_delivery_id => l_delivery_id,
x_name => l_delivery_name);
debug('return_status from WSH_DELIVERIES_PUB.create_update_delivery: '
|| x_return_status,'UPDATE_DELIVERY');
debug('commiting...','UPDATE_DELIVERY');
|| ' trip ship method: ' || l_trip_ship_method_code,'UPDATE_DELIVERY');
debug('Calling update_trip','UPDATE_DELIVERY');
update_trip(p_trip_id => l_trip_id
,p_ship_method_code => p_ship_method_code
,x_return_status => x_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
debug('return status from update_trip: ' || x_return_status,'UPDATE_DELIVERY');
debug('Shipping msg count: ' || l_msg_count,'UPDATE_DELIVERY');
debug('Shipping error msg: ' || l_msg_data,'UPDATE_DELIVERY');
|| ' Returning W so user can be prompted if necessary.','UPDATE_DELIVERY');
END UPDATE_DELIVERY;
select DISTINCT wlpn.license_plate_number,
lpn.subinventory_code,
milk.concatenated_segments,
wnd.name
from wms_license_plate_numbers lpn, mtl_item_locations_kfv milk,wsh_trip_stops_ob_grp_v pickup_stop,
wsh_delivery_legs_ob_grp_v wdl,wsh_delivery_assignments_v wda,wsh_delivery_details_ob_grp_v wdd,
wsh_delivery_details_ob_grp_v wdd2,wsh_new_deliveries_ob_grp_v wnd,wms_license_plate_numbers wlpn
where wdd.delivery_detail_id = wda.delivery_detail_id
and wdd.released_status = 'Y'
and wdd2.delivery_detail_id = wda.parent_delivery_detail_id
and wda.delivery_id = wdl.delivery_id
and wdl.delivery_id = wnd.delivery_id
and wdl.pick_up_stop_id = pickup_stop.stop_id
and pickup_stop.trip_id = p_trip_id
and lpn.lpn_id = wdd2.lpn_id
and lpn.outermost_lpn_id = wlpn.lpn_id
and wlpn.lpn_context <> wms_globals.lpn_loaded_for_shipment
and lpn.locator_id = milk.inventory_location_id
and lpn.organization_id = milk.organization_id
and wlpn.license_plate_number like (p_lpn)
order by wlpn.license_plate_number;
select DISTINCT wlpn.license_plate_number,
lpn.subinventory_code,
milk.concatenated_segments,
wnd.name
from wms_license_plate_numbers lpn, mtl_item_locations_kfv milk,
wsh_delivery_assignments_v wda,wsh_delivery_details_ob_grp_v wdd,
wsh_delivery_details_ob_grp_v wdd2,wsh_new_deliveries_ob_grp_v wnd,wms_license_plate_numbers wlpn
where wdd.delivery_detail_id = wda.delivery_detail_id
and wdd.released_status = 'Y'
and wdd2.delivery_detail_id = wda.parent_delivery_detail_id
and wda.delivery_id in ( select distinct delivery_id
from wms_shipping_transaction_temp
where organization_id = p_organization_id
and dock_appoint_flag = 'N'
and dock_door_id = p_dock_door_id
and delivery_id is not null
UNION
select distinct wdl.delivery_id
from wsh_delivery_legs_ob_grp_v wdl,wms_shipping_transaction_temp wstt,
wsh_trip_stops_ob_grp_v wts
where wdl.pick_up_stop_id = wts.stop_id
and wts.trip_id = wstt.trip_id
and wstt.dock_door_id = p_dock_door_id
and wstt.organization_id = p_organization_id
and wstt.dock_appoint_flag = 'N' )
and wda.delivery_id = wnd.delivery_id
and lpn.lpn_id = wdd2.lpn_id
and lpn.outermost_lpn_id = wlpn.lpn_id
and wlpn.lpn_context <> wms_globals.lpn_loaded_for_shipment
and lpn.locator_id = milk.inventory_location_id
and lpn.organization_id = milk.organization_id
and wlpn.license_plate_number like (p_lpn)
order by wlpn.license_plate_number;
select count(distinct lpn.outermost_lpn_id)
into l_count
from wsh_trip_stops_ob_grp_v pickup_stop
,wsh_delivery_legs_ob_grp_v wdl
,wsh_delivery_assignments_v wda
,wsh_delivery_details_ob_grp_v wdd
,wsh_delivery_details_ob_grp_v wdd2
,wms_license_plate_numbers lpn
,wms_license_plate_numbers wlpn
-- 5582189 dherring added table in from clause so that
-- the status code can be checked
,wsh_new_deliveries_ob_grp_v wnd
Where pickup_stop.trip_id = p_trip_id
and wdl.pick_up_stop_id = pickup_stop.stop_id
and wda.delivery_id = wdl.delivery_id
and wdd.delivery_detail_id = wda.delivery_detail_id
and wdd.released_status = 'Y'
and wdd2.delivery_detail_id = wda.parent_delivery_detail_id
and wdd2.lpn_id = lpn.lpn_id
and lpn.outermost_lpn_id = wlpn.lpn_id
AND wdd.organization_id = p_organization_id
and ( wlpn.lpn_context = wms_container_pvt.lpn_context_picked
OR wlpn.lpn_context = wms_container_pvt.lpn_loaded_in_stage)
-- 5582189 dherring changed code to select picked lpns
and wnd.status_code in ('OP', 'PA');
open x_missing_lpns FOR select 1 from dual;
select wlpn.license_plate_number
, wlpn.lpn_id
, wdd.delivery_detail_id
, lpn.subinventory_code
, lpn.locator_id
, milk.concatenated_segments
, wnd.name
from wms_license_plate_numbers lpn
, mtl_item_locations_kfv milk
, wsh_trip_stops_ob_grp_v pickup_stop
, wsh_delivery_legs_ob_grp_v wdl
, wsh_delivery_assignments_v wda
, wsh_delivery_details_ob_grp_v wdd
, wsh_delivery_details_ob_grp_v wdd2
, wsh_new_deliveries_ob_grp_v wnd
, wms_license_plate_numbers wlpn
where wdd.delivery_detail_id = wda.delivery_detail_id
and wdd.released_status = 'Y'
and wdd2.delivery_detail_id = wda.parent_delivery_detail_id
and wda.delivery_id = wdl.delivery_id
and wdl.delivery_id = wnd.delivery_id
and wdl.pick_up_stop_id = pickup_stop.stop_id
and pickup_stop.trip_id = p_trip_id
and lpn.lpn_id = wdd2.lpn_id
and lpn.outermost_lpn_id = wlpn.lpn_id
and ( wlpn.lpn_context = wms_container_pvt.lpn_context_picked
OR wlpn.lpn_context = wms_container_pvt.lpn_loaded_in_stage)
--and wlpn.lpn_context <> 9
-- 5582189 dherring changed code to select picked lpns
and wnd.status_code in ('OP', 'PA')
and lpn.locator_id = milk.inventory_location_id
AND wdd.organization_id = p_organization_id
and lpn.organization_id = milk.organization_id;
select count(distinct lpn.outermost_lpn_id)
into l_count
from wsh_delivery_assignments_v wda
,wsh_new_deliveries_ob_grp_v wnd
,wsh_delivery_details_ob_grp_v wdd
,wsh_delivery_details_ob_grp_v wdd2
,wms_license_plate_numbers lpn
,wms_license_plate_numbers wlpn
where
wdd.delivery_detail_id = wda.delivery_detail_id
and wdd.released_status = 'Y'
and wdd2.delivery_detail_id = wda.parent_delivery_detail_id
and wda.delivery_id in ( select distinct delivery_id
from wms_shipping_transaction_temp
where organization_id = p_organization_id
and dock_appoint_flag = 'N'
and dock_door_id = p_dock_door_id
and delivery_id is not null
UNION
select distinct wdl.delivery_id
from wsh_delivery_legs_ob_grp_v wdl
,wms_shipping_transaction_temp wstt
,wsh_trip_stops_ob_grp_v wts
where wdl.pick_up_stop_id = wts.stop_id
and wts.trip_id = wstt.trip_id
and wstt.dock_door_id = p_dock_door_id
and wstt.organization_id = p_organization_id
and wstt.dock_appoint_flag = 'N')
and wdd2.lpn_id = lpn.lpn_id
and wlpn.lpn_id = lpn.outermost_lpn_id
AND wdd.organization_id = p_organization_id
and ( wlpn.lpn_context =
wms_container_pvt.lpn_context_picked
OR wlpn.lpn_context = wms_container_pvt.lpn_loaded_in_stage)
-- 5582189 dherring changed code to select picked lpns
and wda.delivery_id = wnd.delivery_id
and wnd.status_code in ('OP', 'PA');
open x_missing_lpns FOR select 1 from dual;
select wlpn.license_plate_number
, wlpn.lpn_id
, wdd.delivery_detail_id
, lpn.subinventory_code
, lpn.locator_id
, milk.concatenated_segments
, wnd.name
from wms_license_plate_numbers lpn
, mtl_item_locations_kfv milk
, wsh_delivery_assignments_v wda
, wsh_delivery_details_ob_grp_v wdd
, wsh_delivery_details_ob_grp_v wdd2
, wsh_new_deliveries_ob_grp_v wnd
, wms_license_plate_numbers wlpn
where wdd.delivery_detail_id = wda.delivery_detail_id
and wdd.released_status = 'Y'
and wdd2.delivery_detail_id = wda.parent_delivery_detail_id
and wda.delivery_id in ( select distinct delivery_id
from wms_shipping_transaction_temp
where organization_id = p_organization_id
and dock_appoint_flag = 'N'
and nvl(direct_ship_flag,'N') = 'N'
and dock_door_id = p_dock_door_id
and delivery_id is not null
UNION
select distinct wdl.delivery_id
from wsh_delivery_legs_ob_grp_v wdl
,wms_shipping_transaction_temp wstt
,wsh_trip_stops_ob_grp_v wts
where wdl.pick_up_stop_id = wts.stop_id
and wts.trip_id = wstt.trip_id
and wstt.dock_door_id = p_dock_door_id
and wstt.organization_id = p_organization_id
and wstt.dock_appoint_flag = 'N'
and nvl(direct_ship_flag,'N') = 'N')
and wda.delivery_id = wnd.delivery_id
and lpn.lpn_id = wdd2.lpn_id
and lpn.outermost_lpn_id = wlpn.lpn_id
and ( wlpn.lpn_context = wms_container_pvt.lpn_context_picked
OR wlpn.lpn_context = wms_container_pvt.lpn_loaded_in_stage)
--and wlpn.lpn_context <> 9
-- 5582189 dherring changed code to select picked lpns
and wnd.status_code in ('OP', 'PA')
and lpn.locator_id = milk.inventory_location_id
AND wdd.organization_id = p_organization_id
and lpn.organization_id = milk.organization_id;
select count(*)
into l_count
from wsh_delivery_details_ob_grp_v wdd
, wsh_delivery_assignments_v wda
, wsh_delivery_legs_ob_grp_v wdl
, wsh_Trip_stops_ob_grp_v pickup_stop
, mtl_system_items_kfv msik
where
wda.delivery_id = wdl.delivery_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
and wdl.pick_up_stop_id = pickup_stop.stop_id
and pickup_stop.trip_id = p_trip_id
AND wdd.inventory_item_id = msik.inventory_item_id
AND wdd.organization_id = msik.organization_id
AND wdd.organization_id = p_organization_id
and wdd.lpn_id is null
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 FOR select 1 from dual;
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
, wsh_delivery_legs_ob_grp_v wdl
, wsh_trip_Stops_ob_grp_v pickup_stop
, mtl_system_items_kfv msik
where wnd.delivery_id = wda.delivery_id
AND wda.delivery_id = wdl.delivery_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
and wdl.pick_up_stop_id = pickup_stop.stop_id
and pickup_stop.trip_id = p_trip_id
and wdd.lpn_id is null
and wdd.inventory_item_id = msik.inventory_item_id
AND wdd.organization_id = p_organization_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 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 wda.delivery_id in (select distinct delivery_id
from wms_shipping_transaction_temp
where dock_door_id = p_dock_door_id
and organization_id = p_organization_id
and dock_appoint_flag = 'N'
and nvl(direct_ship_flag,'N') = 'N'
and delivery_id is not null
UNION
select distinct wdl.delivery_id
from wsh_delivery_legs_ob_grp_v wdl
,wms_shipping_transaction_temp wstt
,wsh_trip_stops_ob_grp_v wts
where wdl.pick_up_stop_id = wts.stop_id
and wts.trip_id = wstt.trip_id
and wstt.dock_door_id = p_dock_door_id
and wstt.organization_id = p_organization_id
and wstt.dock_appoint_flag = 'N'
and nvl(direct_ship_flag,'N') = 'N')
and wdd.lpn_id is NULL
AND wdd.inventory_item_id = msik.inventory_item_id
AND wdd.organization_id = msik.organization_id
AND wdd.organization_id = p_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 FOR select 1 from dual;
select wnd.name
,wdd.delivery_detail_id
, wdd.inventory_item_id
, wdd.requested_quantity
, msiv.concatenated_segments
, msiv.description
from wsh_delivery_details_ob_grp_v wdd
, wsh_delivery_assignments_v wda
, mtl_system_items_vl msiv
, wsh_new_deliveries_ob_grp_v wnd
where wda.delivery_detail_id = wdd.delivery_detail_id
and wdd.lpn_id is null
and wda.delivery_id in (select distinct delivery_id
from wms_shipping_transaction_temp
where dock_door_id = p_dock_door_id
and organization_id = p_organization_id
and dock_appoint_flag = 'N'
and nvl(direct_ship_flag,'N') = 'N'
and delivery_id is not null
UNION
select distinct wdl.delivery_id
from wsh_delivery_legs_ob_grp_v wdl
,wms_shipping_transaction_temp wstt
,wsh_trip_stops_ob_grp_v wts
where wdl.pick_up_stop_id = wts.stop_id
and wts.trip_id = wstt.trip_id
and wstt.dock_door_id = p_dock_door_id
and wstt.organization_id = p_organization_id
and wstt.dock_appoint_flag = 'N'
and nvl(direct_ship_flag,'N') = 'N')
and wda.delivery_id = wnd.delivery_id
and wdd.organization_id = p_organization_id
and wdd.inventory_item_id = msiv.inventory_item_id
and wdd.organization_id = msiv.organization_id
AND wdd.organization_id = p_organization_id
and ((wda.parent_delivery_detail_id IS NULL
AND msiv.mtl_transactions_enabled_flag <> 'N')
OR wdd.released_status is NULL
OR wdd.released_status NOT IN ('X', 'Y'));
SELECT distinct wdd.ship_set_id
FROM wsh_delivery_details_ob_grp_v wdd,wms_shipping_transaction_temp wstt
WHERE wdd.delivery_detail_id = wstt.delivery_detail_id
AND wdd.ship_set_id is not null
AND wstt.organization_id = p_organization_id
AND wstt.trip_id = p_trip_id
AND wstt.dock_door_id = p_dock_door_id
AND nvl(wstt.direct_ship_flag,'N') = 'N';
SELECT distinct wdd.ship_set_id, wdd.source_header_id
FROM wsh_delivery_details_ob_grp_v wdd,wms_shipping_transaction_temp wstt
WHERE wdd.delivery_detail_id = wstt.delivery_detail_id
AND wdd.ship_set_id is not null
AND wstt.organization_id = p_organization_id
AND wstt.dock_appoint_flag = 'N'
AND nvl(wstt.direct_ship_flag,'N') = p_direct_ship_flag
AND wstt.dock_door_id = p_dock_door_id;
SELECT 1
FROM wsh_delivery_details_ob_grp_v wdd,
oe_order_lines_all oel
WHERE oel.ship_set_id = p_ship_set_id
AND wdd.source_line_id = oel.line_id
AND wdd.source_header_id = oel.header_id
AND wdd.ship_set_id = p_ship_set_id
AND wdd.shipped_quantity is null;
select set_name
into l_ship_set_name
from oe_sets
where set_id = l_ship_set_id;
/* SELECT 1
INTO unshipped_count
FROM DUAL
WHERE exists (
SELECT 1
FROM wsh_delivery_details_ob_grp_v wdd
WHERE
wdd.source_header_id = l_source_header_id
AND wdd.ship_set_id = l_ship_set_id
AND nvl(wdd.shipped_quantity,wdd.picked_quantity) is null
);
SELECT count(delivery_detail_id)
INTO unshipped_count
FROM wsh_delivery_details wdd
WHERE wdd.released_status NOT IN ('C','X','D')
AND NOT EXISTS (select 1
FROM wms_shipping_transaction_temp wstt
WHERE wstt.delivery_detail_id = wdd.delivery_detail_id)
AND wdd.source_header_id = l_source_header_id
AND wdd.ship_set_id = l_ship_set_id
AND wdd.container_flag <> 'Y'
AND wdd.organization_id = p_organization_id;
select set_name
into l_ship_set_name
from oe_sets
where set_id = l_ship_set_id;
select wdd.delivery_detail_id
from wsh_delivery_details_ob_grp_v wdd, wsh_delivery_assignments_v wda
where wda.delivery_id = p_delivery_id
and wda.delivery_detail_id = wdd.delivery_detail_id
and wdd.lpn_id is NULL;
SELECT DISTINCT wda.parent_delivery_detail_id inner_lpn_wdd,
wlpn.outermost_lpn_id outermost_lpn_id
FROM wsh_delivery_details_ob_grp_v wdd,
wsh_delivery_assignments_v wda,
wms_license_plate_numbers wlpn
WHERE wda.delivery_id = p_delivery_id
AND wda.parent_delivery_detail_id = wdd.delivery_detail_id
AND wdd.lpn_id = wlpn.lpn_id;
select delivery_report_set_id
into l_report_set_id
from wsh_shipping_parameters
where organization_id = p_organization_id;
select report_set_id
into l_report_set_id
from wsh_report_sets
where usage_code = 'SHIP_CONFIRM'
and name = 'Ship Confirm Documents';
UPDATE_DELIVERY(p_delivery_id => p_delivery_id,
p_gross_weight => p_gross_weight,
p_weight_uom => p_gross_weight_uom,
p_waybill => p_waybill,
p_bol => p_bol,
p_ship_method_code => p_ship_method_code,
x_return_status => l_return_status);
debug('update_delivery failed with status: '
|| l_return_status, 'SHIP_CONFIRM_ALL');
select distinct wstt.delivery_id, wnd.ship_method_code
from wms_shipping_transaction_temp wstt,wsh_new_deliveries_ob_grp_v wnd
where wstt.trip_id = p_trip_id
and wstt.dock_door_id = p_dock_door_id
and wstt.organization_id = p_organization_id
and nvl(wstt.direct_ship_flag,'N') = 'N'
AND wstt.delivery_id = wnd.delivery_id;
select distinct wstt.delivery_id,wnd.ship_method_code
from wms_shipping_transaction_temp wstt,wsh_new_deliveries_ob_grp_v wnd
where wstt.dock_appoint_flag = 'N'
and nvl(wstt.direct_ship_flag,'N') = 'N'
and wstt.dock_door_id = p_dock_door_id
and wstt.organization_id = p_organization_id
AND wstt.delivery_id = wnd.delivery_id;
select distinct outermost_lpn_id
from wms_shipping_transaction_temp
where trip_id = p_trip_id
and nvl(direct_ship_flag,'N') = 'N'
and organization_id = p_organization_id;
select distinct outermost_lpn_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 nvl(direct_ship_flag,'N') = 'N';
SELECT lpn_id
FROM WMS_LICENSE_PLATE_NUMBERS
START WITH lpn_id = l_outermost_lpn_id
CONNECT BY parent_lpn_id = PRIOR lpn_id;
select distinct wstt.delivery_id,wnd.ship_method_code
from wms_shipping_transaction_temp wstt,wsh_new_deliveries_ob_grp_v wnd
where wstt.dock_appoint_flag = 'N'
and nvl(wstt.direct_ship_flag,'N') = 'N'
and wstt.dock_door_id = p_dock_door_id
and wstt.trip_id is null
and wstt.organization_id = p_organization_id
AND wstt.delivery_id = wnd.delivery_id ;
select distinct wstt.delivery_id,wnd.ship_method_code
from wms_shipping_transaction_temp wstt, wsh_new_deliveries_ob_grp_v wnd
where wstt.dock_appoint_flag = 'N'
and nvl(wstt.direct_ship_flag,'N') = 'N'
and wstt.dock_door_id = p_dock_door_id
and wstt.trip_id is not null
and wstt.organization_id = p_organization_id
AND wstt.delivery_id = wnd.delivery_id;
select distinct wdl.pick_up_stop_id,wts.stop_sequence_number
from wsh_delivery_legs_ob_grp_v wdl,wms_shipping_transaction_temp wstt,
wsh_trip_stops_ob_grp_v wts
where wdl.pick_up_stop_id = wts.stop_id
and wts.trip_id = wstt.trip_id
and wstt.dock_door_id = p_dock_door_id
and wstt.organization_id = p_organization_id
and wstt.dock_appoint_flag = 'N'
and nvl(direct_ship_flag,'N') = 'N'
AND wts.status_code = 'OP'
ORDER BY wts.stop_sequence_number asc;
select distinct wdl.drop_off_stop_id
from wsh_delivery_legs wdl,wms_shipping_transaction_temp wstt,
wsh_trip_stops wts
where wdl.drop_off_stop_id = wts.stop_id
and wts.trip_id = wstt.trip_id
and wstt.dock_door_id = p_dock_door_id
and wstt.organization_id = p_organization_id
and wstt.dock_appoint_flag = p_dock_appoint_flag
and nvl(direct_ship_flag,'N') = 'N';
SELECT wts.trip_id,
wt.ship_method_code
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_delivery_id
AND wdl.pick_up_stop_id = wts.stop_id
AND wt.trip_id = wts.trip_id
AND ROWNUM = 1;
SELECT wsp.ship_confirm_rule_id, wscr.ac_intransit_flag, wscr.ac_close_trip_flag, wscr.ac_defer_interface_flag
FROM wsh_shipping_parameters wsp, wsh_ship_confirm_rules wscr
WHERE wsp.organization_id = p_organization_id
AND wscr.ship_confirm_rule_id = wsp.ship_confirm_rule_id;
SELECT wdt1.trip_id
FROM wsh_delivery_trips_v wdt1,
wsh_new_deliveries wnd
WHERE wdt1.delivery_id <> l_delivery_id
AND wdt1.delivery_id = wnd.delivery_id
AND wnd.status_code = 'OP'
AND wdt1.trip_id IN (SELECT wdt2.trip_id
FROM wsh_delivery_trips_v wdt2
WHERE wdt2.delivery_id = l_delivery_id)
AND rownum = 1;
select organization_id
into l_org_id
from wsh_new_deliveries_ob_grp_v
where delivery_id = l_delivery_id;
select delivery_report_set_id
into l_report_set_id
from wsh_shipping_parameters
where organization_id = l_org_id;
select report_set_id
into l_report_set_id
from wsh_report_sets
where usage_code = 'SHIP_CONFIRM'
and name = 'Ship Confirm Documents';
UPDATE WMS_LICENSE_PLATE_NUMBERS
SET lpn_context = 11, --WMS_Container_PUB.LPN_CONTEXT_INV,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID
where lpn_id = l_outermost_lpn_id;
UPDATE WMS_LICENSE_PLATE_NUMBERS
SET lpn_context = 11, --WMS_Container_PUB.LPN_CONTEXT_INV,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID
where lpn_id = l_lpn_id.lpn_id;
DELETE FROM mtl_material_transactions_temp
WHERE wms_task_type = 7
AND organization_id = p_organization_id
AND content_lpn_id IN
(SELECT outermost_lpn_id
FROM wms_shipping_transaction_temp
WHERE organization_id = p_organization_id
AND trip_id = p_trip_id);
delete from wms_shipping_transaction_temp where trip_id = p_trip_id
and organization_id = p_organization_id;
UPDATE WMS_LICENSE_PLATE_NUMBERS
SET lpn_context = 11, --WMS_Container_PUB.LPN_CONTEXT_INV,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID
where lpn_id = l_outermost_lpn_id;
UPDATE WMS_LICENSE_PLATE_NUMBERS
SET lpn_context = 11, --WMS_Container_PUB.LPN_CONTEXT_INV,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID
where lpn_id = l_lpn_id.lpn_id;
DELETE FROM mtl_material_transactions_temp
WHERE wms_task_type = 7
AND organization_id = p_organization_id
AND content_lpn_id IN
(SELECT outermost_lpn_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 Nvl(direct_ship_flag,'N') = 'N');
delete 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 nvl(direct_ship_flag,'N') = 'N';
l_del_rows.DELETE;
l_ship_method_tbl.DELETE;
l_del_rows.DELETE;
l_ship_method_tbl.DELETE;
l_drop_off_stops.DELETE;
DELETE FROM mtl_material_transactions_temp
WHERE wms_task_type = 7
AND organization_id = p_organization_id
AND content_lpn_id IN
(SELECT outermost_lpn_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 Nvl(direct_ship_flag,'N') = 'N');
delete 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 nvl(direct_ship_flag,'N') = 'N';
SELECT COUNT(DISTINCT wdl.pick_up_stop_id)
INTO l_pick_up_count
FROM wsh_trip_stops wts, wsh_delivery_legs wdl
where wts.stop_id = wdl.pick_up_stop_id
and wts.trip_id = p_trip_id;
l_drop_off_stops.DELETE;
DELETE FROM mtl_material_transactions_temp
WHERE wms_task_type = 7
AND organization_id = p_organization_id
AND content_lpn_id IN
(SELECT outermost_lpn_id
FROM wms_shipping_transaction_temp
WHERE trip_id = p_trip_id
AND organization_id = p_organization_id
AND Nvl(direct_ship_flag,'N') = 'N');
delete from wms_shipping_transaction_temp where trip_id = p_trip_id
and organization_id = p_organization_id
and nvl(direct_ship_flag,'N') = 'N';
DELETE FROM mtl_material_transactions_temp
WHERE wms_task_type = 7
AND organization_id = p_organization_id
AND content_lpn_id IN
(SELECT outermost_lpn_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 Nvl(direct_ship_flag,'N') = 'N');
delete 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 nvl(direct_ship_flag,'N') = 'N';
select serial_number, current_subinventory_code, current_locator_id, lot_number
from mtl_serial_numbers
where inventory_item_id = p_inventory_item_id
and current_organization_id = p_organization_id
and (group_mark_id is null or group_mark_id = -1)
and ((nvl(current_subinventory_code,'@@@') = nvl(p_subinventory_code,'@@@')
and nvl(current_locator_id,-1) = nvl(p_locator_id,-1)
and nvl(lot_number,'@@@') = nvl(p_lot_number,'@@@')
and nvl(revision,'@@@') = nvl(p_revision,'@@@')
and current_status = 3)
or current_status = 1)
and serial_number like (p_serial_number)
order by lpad(serial_number,20);
PROCEDURE insert_serial_numbers
(x_status OUT NOCOPY VARCHAR2,
p_fm_serial_number IN VARCHAR2,
p_to_serial_number IN VARCHAR2,
p_transaction_Temp_id IN NUMBER) IS
l_status VARCHAR2(1) := 'S';
insert into mtl_serial_numbers_temp
(
transaction_Temp_id,
last_update_date,
last_updated_by,
creation_date,
created_By,
last_update_login,
fm_serial_number,
to_serial_number,
serial_prefix
) values
(
p_transaction_temp_id,
sysdate,
l_user,
sysdate,
l_user,
l_login_id,
l_fm_serial_number,
l_to_serial_number,
l_serial_prefix
);
END insert_Serial_Numbers;
select nvl(msik.serial_status_enabled,'N'), nvl(msik.default_Serial_status_id, -1), mst.status_code
into l_serial_status_enabled, l_serial_status_id, l_serial_status_code
from mtl_system_items_kfv msik, mtl_material_statuses_vl mst
where msik.organization_id = p_organization_id
and msik.inventory_item_id = p_inventory_item_id
and msik.default_serial_status_id = mst.status_id(+);
SELECT PLANNED_FLAG
INTO L_PLANNED_FLAG
FROM wsh_new_deliveries
WHERE delivery_id = l_delivery_id_tab(1);
SELECT delivery_id
INTO l_delivery_id_tab(1)
FROM wsh_delivery_assignments
WHERE delivery_detail_id = l_delivery_details(1);
SELECT wnd.planned_flag
INTO l_planned_flag
FROM wsh_new_deliveries wnd,
wsh_delivery_details wdd,
wsh_delivery_assignments wda
WHERE wdd.delivery_detail_id = l_delivery_details(1)
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wda.delivery_id = wnd.delivery_id;
PROCEDURE update_wdd_loc_by_lpn
(x_return_status OUT NOCOPY VARCHAR2,
p_lpn_id NUMBER,
p_subinventory_code VARCHAR2,
p_locator_id NUMBER)
IS
l_delivery_detail_id NUMBER;
SELECT wdd.delivery_detail_id,
ol.header_id,
ol.line_id,
wdd.released_status,
mol.organization_id
FROM wsh_delivery_details_ob_grp_v wdd,
wsh_delivery_assignments_v wda,
wsh_delivery_details_ob_grp_v wdd2,
wms_license_plate_numbers lpn,
mtl_txn_request_lines mol,
oe_order_lines_all ol
WHERE lpn.outermost_lpn_id = p_lpn_id
AND wdd2.lpn_id = lpn.lpn_id
AND wdd2.released_status = 'X' -- For LPN reuse ER : 6845650
AND wdd2.delivery_detail_id = wda.parent_delivery_detail_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND mol.line_id = wdd.move_order_line_id
AND ol.line_id = wdd.source_line_id;
debug('update_wdd_loc_by_lpn 10 - p_lpn_id = '||p_lpn_id,'WMS_SHIPPING_TRANSACTION_PUB');
SAVEPOINT update_wdd_loc_sp;
debug('update_wdd_loc_by_lpn 15 - l_delivery_detail_id = '
||l_delivery_detail_id,'WMS_SHIPPING_TRANSACTION_PUB');
debug('update_wdd_loc_by_lpn 20 - l_wdd_counter = ' || l_wdd_counter, 'WMS_SHIPPING_TRANSACTION_PUB');
WSH_INTERFACE.Update_Shipping_Attributes
(p_source_code => 'INV',
p_changed_attributes => l_shipping_attr_tab,
x_return_status => l_return_status
);
debug('update_wdd_loc_by_lpn 25 - WSH_INTERFACE.Update_Shipping_Attributes returns : '
||l_return_status, 'WMS_SHIPPING_TRANSACTION_PUB');
debug('update_wdd_loc_by_lpn 30 - return expected error from update_shipping_attributes',
'WMS_SHIPPING_TRANSACTION_PUB');
debug('update_wdd_loc_by_lpn 40 - return unexpected error from update_shipping_attributes',
'WMS_SHIPPING_TRANSACTION_PUB');
debug('update_wdd_loc_by_lpn 50 - complete','WMS_SHIPPING_TRANSACTION_PUB');
ROLLBACK TO update_wdd_loc_sp;
FND_MESSAGE.SET_NAME('WMS', 'WMS_UPDATE_WDD_LOC_FAIL');
debug('update_wdd_loc_by_lpn 60 - expected error', 'WMS_SHIPPING_TRANSACTION_PUB');
ROLLBACK TO update_wdd_loc_sp;
FND_MESSAGE.SET_NAME('WMS', 'WMS_UPDATE_WDD_LOC_FAIL');
debug('update_wdd_loc_by_lpn 65 - unexpected error', 'WMS_SHIPPING_TRANSACTION_PUB');
ROLLBACK TO update_wdd_loc_sp;
debug('update_wdd_loc_by_lpn 70 - other error', 'WMS_SHIPPING_TRANSACTION_PUB');
FND_MESSAGE.SET_NAME('WMS', 'WMS_UPDATE_WDD_LOC_FAIL');
inv_mobile_helper_functions.sql_error('WMS_SHIPPING_TRANSACTION_PUB.update_wdd_loc_by_lpn',
l_progress, SQLCODE);
END update_wdd_loc_by_lpn;
select distinct wlpn.license_plate_number, wlpn.lpn_id,
wlpn.subinventory_code, milk.concatenated_segments
from wms_license_plate_numbers wlpn,
wms_shipping_transaction_temp wstt,
mtl_item_locations_kfv milk
WHERE wlpn.organization_id = wstt.organization_id
AND wlpn.organization_id = p_organization_id
AND wlpn.lpn_id = wstt.outermost_lpn_id
and wlpn.lpn_context = wms_globals.lpn_loaded_for_shipment
AND wstt.dock_door_id = p_dock_door_id
AND nvl(wstt.direct_ship_flag,'N') = 'N'
AND milk.organization_id = wlpn.organization_id
AND milk.inventory_location_id = wlpn.locator_id
and wlpn.license_plate_number like (p_lpn)
order by wlpn.license_plate_number;
select DISTINCT milk.inventory_location_id,
inv_project.get_locsegs(milk.inventory_location_id, milk.organization_id)
milk_concatenated_segments,
0, milk.organization_id,
0,0,'','',is_loaded(p_organization_id,milk.inventory_location_id,'N')
,''
from mtl_item_locations_kfv milk
, wms_shipping_transaction_temp wstt
where inventory_location_type = 1
and milk.organization_id = p_organization_id
AND milk.organization_id = wstt.organization_id
AND milk.inventory_location_id = wstt.dock_door_id
and inv_project.get_locsegs(milk.inventory_location_id, milk.organization_id) like (p_dock_door)
order by milk_concatenated_segments;
SELECT delivery_detail_id
FROM wms_shipping_transaction_temp
WHERE organization_id = p_organization_id
AND nvl(direct_ship_flag,'N') = 'N'
AND outermost_lpn_id = p_outermost_lpn_id;
UPDATE wms_license_plate_numbers
SET lpn_context = wms_globals.lpn_context_picked,
last_update_date = Sysdate,
last_updated_by = fnd_global.user_id
WHERE organization_id = p_organization_id
AND outermost_lpn_id = p_outermost_lpn_id;
l_detail_attributes.DELETE;
debug('About to call wsh_delivery_details_pub.update_shipping_attributes','LPN_UNLOAD');
wsh_delivery_details_pub.update_shipping_attributes
(p_api_version_number => 1.0,
p_init_msg_list => G_TRUE,
p_commit => G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_changed_attributes => l_detail_attributes,
p_source_code => 'OE');
debug('wsh_delivery_details_pub.update_shipping_attributes failed'
|| ' with status: ' || l_return_status, 'LPN_UNLOAD');
DELETE FROM mtl_material_transactions_temp
WHERE wms_task_type = 7
AND organization_id = p_organization_id
AND content_lpn_id = p_outermost_lpn_id;
DELETE FROM wms_shipping_transaction_temp
WHERE organization_id = p_organization_id
AND outermost_lpn_id = p_outermost_lpn_id
and nvl(direct_ship_flag,'N') = 'N';
SELECT COUNT(*)
INTO l_count
FROM wsh_delivery_details_ob_grp_v wdd,
wsh_delivery_assignments_v wda,
wsh_new_deliveries_ob_grp_v wnd,
wsh_delivery_legs_ob_grp_v wdl,
wsh_trip_Stops_ob_grp_v pickup_stop,
mtl_system_items_kfv msik
WHERE wnd.delivery_id = wda.delivery_id
AND wda.delivery_id = wdl.delivery_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdl.pick_up_stop_id = pickup_stop.stop_id
AND pickup_stop.trip_id = p_trip_id
AND wdd.lpn_id IS NULL
AND wdd.inventory_item_id = msik.inventory_item_id
AND wdd.organization_id = p_organization_id
AND wdd.organization_id = msik.organization_id
AND msik.mtl_transactions_enabled_flag = 'N';
OPEN x_nt_item FOR SELECT 1 FROM dual;
SELECT wnd.name,
wdd.delivery_detail_id,
wdd.inventory_item_id,
wdd.requested_quantity,
wdd.requested_quantity_uom,
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,
wsh_delivery_legs_ob_grp_v wdl,
wsh_trip_stops_ob_grp_v pickup_stop,
mtl_system_items_kfv msik
WHERE wnd.delivery_id = wda.delivery_id
AND wda.delivery_id = wdl.delivery_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdl.pick_up_stop_id = pickup_stop.stop_id
AND pickup_stop.trip_id = p_trip_id
AND wdd.lpn_id IS NULL
AND wdd.inventory_item_id = msik.inventory_item_id
AND wdd.organization_id = p_organization_id
AND wdd.organization_id = msik.organization_id
AND msik.mtl_transactions_enabled_flag = 'N';
SELECT COUNT(*)
INTO l_count
FROM wsh_delivery_details_ob_grp_v wdd,
wsh_delivery_assignments_v wda,
mtl_system_items_kfv
msik, wsh_new_deliveries_ob_grp_v wnd
WHERE wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.lpn_id IS NULL
AND wda.delivery_id IN (SELECT DISTINCT delivery_id
FROM wms_shipping_transaction_temp
WHERE dock_door_id = p_dock_door_id
AND organization_id = p_organization_id
AND dock_appoint_flag = 'N'
AND delivery_id IS NOT NULL
UNION
SELECT DISTINCT wdl.delivery_id
from wsh_delivery_legs_ob_grp_v wdl,
wms_shipping_transaction_temp wstt,
wsh_trip_stops_ob_grp_v wts
WHERE wdl.pick_up_stop_id = wts.stop_id
AND wts.trip_id = wstt.trip_id
AND wstt.dock_door_id = p_dock_door_id
AND wstt.organization_id = p_organization_id
AND wstt.dock_appoint_flag = 'N'
AND nvl(wstt.direct_ship_flag,'N') = 'N')
AND wda.delivery_id = wnd.delivery_id
AND wdd.organization_id = p_organization_id
AND wdd.inventory_item_id = msik.inventory_item_id
AND wdd.organization_id = msik.organization_id
AND msik.mtl_transactions_enabled_flag = 'N';
OPEN x_nt_item FOR SELECT 1 FROM dual;
SELECT wnd.name,
wdd.delivery_detail_id,
wdd.inventory_item_id,
wdd.requested_quantity,
wdd.requested_quantity_uom,
msik.concatenated_segments,
msik.description
FROM wsh_delivery_details_ob_grp_v wdd,
wsh_delivery_assignments_v wda,
mtl_system_items_kfv
msik, wsh_new_deliveries_ob_grp_v wnd
WHERE wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.lpn_id IS NULL
AND wda.delivery_id IN (SELECT DISTINCT delivery_id
FROM wms_shipping_transaction_temp
WHERE dock_door_id = p_dock_door_id
AND organization_id = p_organization_id
AND dock_appoint_flag = 'N'
AND nvl(direct_ship_flag,'N') = 'N'
AND delivery_id IS NOT NULL
UNION
SELECT DISTINCT wdl.delivery_id
FROM wsh_delivery_legs_ob_grp_v wdl,
wms_shipping_transaction_temp wstt,
wsh_trip_stops_ob_grp_v wts
WHERE wdl.pick_up_stop_id = wts.stop_id
AND wts.trip_id = wstt.trip_id
AND wstt.dock_door_id = p_dock_door_id
AND wstt.organization_id = p_organization_id
AND wstt.dock_appoint_flag = 'N'
AND nvl(direct_ship_flag,'N') = 'N')
AND wda.delivery_id = wnd.delivery_id
AND wdd.organization_id = p_organization_id
AND wdd.inventory_item_id = msik.inventory_item_id
AND wdd.organization_id = msik.organization_id
AND msik.mtl_transactions_enabled_flag = 'N';
SELECT wlpn.license_plate_number
, wlpn.lpn_id
, wlpn.inventory_item_id
, msi.concatenated_segments
, wlpn.gross_weight
, wlpn.gross_weight_uom_code
, wlpn.tare_weight
, wlpn.tare_weight_uom_code
FROM wms_license_plate_numbers wlpn
, mtl_system_items_kfv msi
WHERE wlpn.organization_id = p_organization_id
AND wlpn.license_plate_number LIKE (p_lpn)
AND wlpn.lpn_context = 1 /* Resides in Inventory */
AND wlpn.parent_lpn_id is null
AND wlpn.inventory_item_id = msi.inventory_item_id(+)
AND msi.organization_id(+) = wlpn.organization_id
ORDER BY upper (wlpn.license_plate_number);
* Select statement, they are swapped (6, 5 positions).
* In setReturnValues(), Long.parseLong() is used for 5th position field
* which is Customer Number-Varchar. This fails if Customer Number has
* alphanumeric characters. Customer Id is made position 5 and Customer Number
* for position 6 as the resolution.
*/
PROCEDURE get_order_lov(
x_order_lov OUT NOCOPY t_genref
, p_org_id IN NUMBER
, p_order IN VARCHAR2) IS
BEGIN
open x_order_lov FOR
select distinct wdd.source_header_number
, wdd.source_header_id
, otl.name
, wdd.source_header_type_id
, c.party_name
, hca.cust_account_id
, c.party_number
from wsh_delivery_details_ob_grp_v wdd
--, R12 TCA changes ra_customers c -- added the following tables instead
, hz_parties c , hz_cust_accounts hca
, oe_transaction_types_tl otl
, wms_direct_ship_temp wdst
where wdd.customer_id = hca.cust_account_id
and c.party_id = hca.party_id
and otl.language=userenv('LANG')
and wdd.source_header_number like (p_order)
and otl.transaction_type_id=wdd.source_header_type_id
and wdd.organization_id = p_org_id
and wdd.source_code = 'OE'
and wdd.date_scheduled is not null
and (wdd.released_status in ('B','R','X') --Added bug 4128854
or
(wdst.order_header_id = wdd.source_header_id
and wdd.released_status = ('Y')))
and wdst.organization_id (+) = wdd.organization_id --Added bug 4128854
order by 2,1;
l_select_stmt VARCHAR2(2000);
SELECT oel.line_id
,to_char(oel.line_number)||
'.'||to_char(oel.shipment_number) ||
decode(oel.option_number,NULL,NULL,'.'||to_char(oel.option_number))||
decode(oel.component_number, null, null,decode(oel.option_number, null,'.',null)||
'.'||to_char(oel.component_number)) LINE_NUMBER
, oel.inventory_item_id
, oel.item_revision
, oel.PROJECT_ID
, oel.TASK_ID
, oel.END_ITEM_UNIT_NUMBER
, oel.SHIP_TOLERANCE_ABOVE
, oel.ship_tolerance_below
, oel.FLOW_STATUS_CODE
, oel.SHIPPING_INTERFACED_FLAG
, oel.REQUEST_DATE
, msik.serial_number_control_code
, msik.concatenated_segments
, wdd.src_requested_quantity --Bug 4169926, sum(wdd.requested_quantity)
, wdd.REQUESTED_QUANTITY_UOM
, wdd.SHIP_FROM_LOCATION_ID
, wdd.SHIP_TO_LOCATION_ID
, wdd.CUSTOMER_ID
, wdd.INTMED_SHIP_TO_LOCATION_ID
, wdd.SHIP_METHOD_CODE
, wdd.FOB_CODE
, wdd.FREIGHT_TERMS_CODE
, NVL(wds.processed_flag,'N') processed_flag
, NVL(wds.processed_quantity,0) processed_quantity
, wdd.src_requested_quantity_uom
FROM oe_order_lines_all oel
, wsh_delivery_details_ob_grp_v wdd
, mtl_system_items_kfv msik
, wms_direct_ship_temp wds
WHERE oel.header_id =p_header_id
and oel.ship_from_org_id = p_org_id
and oel.item_type_code in ('STANDARD','CONFIG','INCLUDED','OPTION')
and msik.inventory_item_id = oel.inventory_item_id
and msik.organization_id = oel.ship_from_org_id
and msik.mtl_transactions_enabled_flag <> 'N'
and wdd.source_header_id = oel.header_id
and wdd.source_line_id = oel.line_id
and wdd.released_status in ('B','R','X')
and wds.lpn_id(+)=p_outermost_lpn_id
and oel.LINE_ID=wds.ORDER_LINE_ID (+)
and oel.HEADER_ID = wds.order_header_id (+)
and oel.ship_from_org_id = wds.organization_id (+)
and to_char(oel.line_number)||
'.'||to_char(oel.shipment_number) ||
decode(oel.option_number,NULL,NULL,'.'||to_char(oel.option_number))||
decode(oel.component_number, null, null,decode(oel.option_number, null, '.',null)||
'.'||to_char(oel.component_number)) like (p_order_line)
and exists (select 1
from wms_license_plate_numbers lpn
, wms_lpn_contents lpc
where lpn.outermost_lpn_id = p_outermost_lpn_id
and lpn.lpn_id = lpc.parent_lpn_id
and lpc.inventory_item_id = oel.inventory_item_id
)
-- Bug# 4258360: Do not include order lines with crossdocked WDD records
AND NOT EXISTS (SELECT 'xdock'
FROM wsh_delivery_details wdd_xdock
WHERE wdd_xdock.source_header_id = oel.header_id
AND wdd_xdock.source_line_id = oel.line_id
AND wdd_xdock.released_status = 'S'
AND wdd_xdock.move_order_line_id IS NULL)
GROUP BY oel.line_id
, to_char(oel.line_number) ||'.'||to_char(oel.shipment_number) ||
decode(oel.option_number,NULL,NULL,'.'||to_char(oel.option_number))||
decode(oel.component_number, null, null,decode(oel.option_number, null,'.',null)||
'.'||to_char(oel.component_number))
, oel.inventory_item_id
, oel.item_revision
, oel.PROJECT_ID
, oel.TASK_ID
, oel.END_ITEM_UNIT_NUMBER
, oel.SHIP_TOLERANCE_ABOVE
, oel.ship_tolerance_below
, oel.FLOW_STATUS_CODE
, oel.SHIPPING_INTERFACED_FLAG
, oel.REQUEST_DATE
, msik.serial_number_control_code
, msik.concatenated_segments
, wdd.REQUESTED_QUANTITY_UOM
, wdd.SHIP_FROM_LOCATION_ID
, wdd.SHIP_TO_LOCATION_ID
, wdd.CUSTOMER_ID
, wdd.INTMED_SHIP_TO_LOCATION_ID
, wdd.SHIP_METHOD_CODE
, wdd.FOB_CODE
, wdd.FREIGHT_TERMS_CODE
, wds.processed_flag
, wds.processed_quantity
, wdd.src_requested_quantity --Bug 4169926
, wdd.src_requested_quantity_uom
UNION --Added bug 4128854
SELECT oel.line_id
,to_char(oel.line_number)||
'.'||to_char(oel.shipment_number) ||
decode(oel.option_number,NULL,NULL,'.'||to_char(oel.option_number))||
decode(oel.component_number, null, null,decode(oel.option_number, null,'.',null)||
'.'||to_char(oel.component_number)) LINE_NUMBER
, oel.inventory_item_id
, oel.item_revision
, oel.PROJECT_ID
, oel.TASK_ID
, oel.END_ITEM_UNIT_NUMBER
, oel.SHIP_TOLERANCE_ABOVE
, oel.ship_tolerance_below
, oel.FLOW_STATUS_CODE
, oel.SHIPPING_INTERFACED_FLAG
, oel.REQUEST_DATE
, msik.serial_number_control_code
, msik.concatenated_segments
, 0
, wdd.REQUESTED_QUANTITY_UOM
, wdd.SHIP_FROM_LOCATION_ID
, wdd.SHIP_TO_LOCATION_ID
, wdd.CUSTOMER_ID
, wdd.INTMED_SHIP_TO_LOCATION_ID
, wdd.SHIP_METHOD_CODE
, wdd.FOB_CODE
, wdd.FREIGHT_TERMS_CODE
, 'N' processed_flag
, 0 processed_quantity
, wdd.src_requested_quantity_uom
FROM oe_order_lines_all oel
, wsh_delivery_details_ob_grp_v wdd
, mtl_system_items_kfv msik
, wms_direct_ship_temp wds
WHERE oel.header_id =p_header_id
and oel.ship_from_org_id = p_org_id
and oel.item_type_code in ('STANDARD','CONFIG','INCLUDED','OPTION')
and msik.inventory_item_id = oel.inventory_item_id
and msik.organization_id = oel.ship_from_org_id
and msik.mtl_transactions_enabled_flag <> 'N'
and wdd.source_header_id = oel.header_id
and wdd.source_line_id = oel.line_id
and wdd.released_status in ('Y')
and not exists (select 1
from wsh_delivery_details wdd2
where wdd.source_header_id =wdd2.source_header_id
and wdd.source_line_id = wdd2.source_line_id
and wdd2.released_status in ('B','X','R')
)
and wds.lpn_id(+)=p_outermost_lpn_id
and oel.LINE_ID=wds.ORDER_LINE_ID(+)
and oel.HEADER_ID = wds.order_header_id(+)
and oel.ship_from_org_id = wds.organization_id(+)
and to_char(oel.line_number)||
'.'||to_char(oel.shipment_number) ||
decode(oel.option_number,NULL,NULL,'.'||to_char(oel.option_number))||
decode(oel.component_number, null, null,decode(oel.option_number, null, '.',null)||
'.'||to_char(oel.component_number)) like (p_order_line)
and exists (select 1
from wms_license_plate_numbers lpn
, wms_lpn_contents lpc
where lpn.outermost_lpn_id = p_outermost_lpn_id
and lpn.lpn_id = lpc.parent_lpn_id
and lpc.inventory_item_id = oel.inventory_item_id
)
-- Bug# 4258360: Do not include order lines with crossdocked WDD records
AND NOT EXISTS (SELECT 'xdock'
FROM wsh_delivery_details wdd_xdock
WHERE wdd_xdock.source_header_id = oel.header_id
AND wdd_xdock.source_line_id = oel.line_id
AND wdd_xdock.released_status = 'S'
AND wdd_xdock.move_order_line_id IS NULL)
GROUP BY oel.line_id
, to_char(oel.line_number) ||'.'||to_char(oel.shipment_number) ||
decode(oel.option_number,NULL,NULL,'.'||to_char(oel.option_number))||
decode(oel.component_number, null, null,decode(oel.option_number, null,'.',null)||
'.'||to_char(oel.component_number))
, oel.inventory_item_id
, oel.item_revision
, oel.PROJECT_ID
, oel.TASK_ID
, oel.END_ITEM_UNIT_NUMBER
, oel.SHIP_TOLERANCE_ABOVE
, oel.ship_tolerance_below
, oel.FLOW_STATUS_CODE
, oel.SHIPPING_INTERFACED_FLAG
, oel.REQUEST_DATE
, msik.serial_number_control_code
, msik.concatenated_segments
, wdd.REQUESTED_QUANTITY_UOM
, wdd.SHIP_FROM_LOCATION_ID
, wdd.SHIP_TO_LOCATION_ID
, wdd.CUSTOMER_ID
, wdd.INTMED_SHIP_TO_LOCATION_ID
, wdd.SHIP_METHOD_CODE
, wdd.FOB_CODE
, wdd.FREIGHT_TERMS_CODE
, wds.processed_flag
, wds.processed_quantity
, wdd.src_requested_quantity_uom
ORDER BY 1,2;
SELECT oel.line_id
,to_char(oel.line_number)||
'.'||to_char(oel.shipment_number) ||
decode(oel.option_number,NULL,NULL,'.'||to_char(oel.option_number))||
decode(oel.component_number, null, null,decode(oel.option_number, null,'.',null)||
'.'||to_char(oel.component_number)) LINE_NUMBER
, oel.inventory_item_id
, oel.item_revision
, oel.PROJECT_ID
, oel.TASK_ID
, oel.END_ITEM_UNIT_NUMBER
, oel.SHIP_TOLERANCE_ABOVE
, oel.ship_tolerance_below
, oel.FLOW_STATUS_CODE
, oel.SHIPPING_INTERFACED_FLAG
, oel.REQUEST_DATE
, msik.serial_number_control_code
, msik.concatenated_segments
, wdd.src_requested_quantity --Bug 4169926, sum(wdd.requested_quantity)
, wdd.REQUESTED_QUANTITY_UOM
, wdd.SHIP_FROM_LOCATION_ID
, wdd.SHIP_TO_LOCATION_ID
, wdd.CUSTOMER_ID
, wdd.INTMED_SHIP_TO_LOCATION_ID
, wdd.SHIP_METHOD_CODE
, wdd.FOB_CODE
, wdd.FREIGHT_TERMS_CODE
, NVL(wds.processed_flag,'N') processed_flag
, NVL(wds.processed_quantity,0) processed_quantity
, wdd.src_requested_quantity_uom
FROM oe_order_lines_all oel
, wsh_delivery_details_ob_grp_v wdd
, mtl_system_items_kfv msik
, wms_direct_ship_temp wds
WHERE oel.header_id =p_header_id
and oel.ship_from_org_id = p_org_id
and oel.item_type_code in ('STANDARD','CONFIG','INCLUDED','OPTION')
and msik.inventory_item_id = oel.inventory_item_id
and msik.organization_id = oel.ship_from_org_id
and msik.mtl_transactions_enabled_flag <> 'N'
and wdd.source_header_id = oel.header_id
and wdd.source_line_id = oel.line_id
and wdd.released_status in ('B','R','X')
and wds.lpn_id(+)=p_outermost_lpn_id
and oel.LINE_ID=wds.ORDER_LINE_ID (+)
and oel.HEADER_ID = wds.order_header_id (+)
and oel.ship_from_org_id = wds.organization_id (+)
and to_char(oel.line_number)||
'.'||to_char(oel.shipment_number) ||
decode(oel.option_number,NULL,NULL,'.'||to_char(oel.option_number))||
decode(oel.component_number, null, null,decode(oel.option_number, null, '.',null)||
'.'||to_char(oel.component_number)) like (p_order_line)
and exists (select 1
from wms_license_plate_numbers lpn
, wms_lpn_contents lpc
where lpn.outermost_lpn_id = p_outermost_lpn_id
and lpn.lpn_id = lpc.parent_lpn_id
and lpc.inventory_item_id = oel.inventory_item_id
)
and NVL(oel.project_id,-1)=NVL(p_project_id,-1) and NVL(oel.task_id,-1)=NVL(p_task_id,-1)
-- Bug# 4258360: Do not include order lines with crossdocked WDD records
AND NOT EXISTS (SELECT 'xdock'
FROM wsh_delivery_details wdd_xdock
WHERE wdd_xdock.source_header_id = oel.header_id
AND wdd_xdock.source_line_id = oel.line_id
AND wdd_xdock.released_status = 'S'
AND wdd_xdock.move_order_line_id IS NULL)
GROUP BY oel.line_id
, to_char(oel.line_number) ||'.'||to_char(oel.shipment_number) ||
decode(oel.option_number,NULL,NULL,'.'||to_char(oel.option_number))||
decode(oel.component_number, null, null,decode(oel.option_number, null,'.',null)||
'.'||to_char(oel.component_number))
, oel.inventory_item_id
, oel.item_revision
, oel.PROJECT_ID
, oel.TASK_ID
, oel.END_ITEM_UNIT_NUMBER
, oel.SHIP_TOLERANCE_ABOVE
, oel.ship_tolerance_below
, oel.FLOW_STATUS_CODE
, oel.SHIPPING_INTERFACED_FLAG
, oel.REQUEST_DATE
, msik.serial_number_control_code
, msik.concatenated_segments
, wdd.REQUESTED_QUANTITY_UOM
, wdd.SHIP_FROM_LOCATION_ID
, wdd.SHIP_TO_LOCATION_ID
, wdd.CUSTOMER_ID
, wdd.INTMED_SHIP_TO_LOCATION_ID
, wdd.SHIP_METHOD_CODE
, wdd.FOB_CODE
, wdd.FREIGHT_TERMS_CODE
, wds.processed_flag
, wds.processed_quantity
, wdd.src_requested_quantity --Bug 4169926
, wdd.src_requested_quantity_uom
UNION --Added bug 4128854
SELECT oel.line_id
,to_char(oel.line_number)||
'.'||to_char(oel.shipment_number) ||
decode(oel.option_number,NULL,NULL,'.'||to_char(oel.option_number))||
decode(oel.component_number, null, null,decode(oel.option_number, null,'.',null)||
'.'||to_char(oel.component_number)) LINE_NUMBER
, oel.inventory_item_id
, oel.item_revision
, oel.PROJECT_ID
, oel.TASK_ID
, oel.END_ITEM_UNIT_NUMBER
, oel.SHIP_TOLERANCE_ABOVE
, oel.ship_tolerance_below
, oel.FLOW_STATUS_CODE
, oel.SHIPPING_INTERFACED_FLAG
, oel.REQUEST_DATE
, msik.serial_number_control_code
, msik.concatenated_segments
, 0
, wdd.REQUESTED_QUANTITY_UOM
, wdd.SHIP_FROM_LOCATION_ID
, wdd.SHIP_TO_LOCATION_ID
, wdd.CUSTOMER_ID
, wdd.INTMED_SHIP_TO_LOCATION_ID
, wdd.SHIP_METHOD_CODE
, wdd.FOB_CODE
, wdd.FREIGHT_TERMS_CODE
, 'N' processed_flag
, 0 processed_quantity
, wdd.src_requested_quantity_uom
FROM oe_order_lines_all oel
, wsh_delivery_details_ob_grp_v wdd
, mtl_system_items_kfv msik
, wms_direct_ship_temp wds
WHERE oel.header_id =p_header_id
and oel.ship_from_org_id = p_org_id
and oel.item_type_code in ('STANDARD','CONFIG','INCLUDED','OPTION')
and msik.inventory_item_id = oel.inventory_item_id
and msik.organization_id = oel.ship_from_org_id
and msik.mtl_transactions_enabled_flag <> 'N'
and wdd.source_header_id = oel.header_id
and wdd.source_line_id = oel.line_id
and wdd.released_status in ('Y')
and not exists (select 1
from wsh_delivery_details wdd2
where wdd.source_header_id =wdd2.source_header_id
and wdd.source_line_id = wdd2.source_line_id
and wdd2.released_status in ('B','X','R')
)
and wds.lpn_id(+)=p_outermost_lpn_id
and oel.LINE_ID=wds.ORDER_LINE_ID (+)
and oel.HEADER_ID = wds.order_header_id (+)
and oel.ship_from_org_id = wds.organization_id (+)
and to_char(oel.line_number)||
'.'||to_char(oel.shipment_number) ||
decode(oel.option_number,NULL,NULL,'.'||to_char(oel.option_number))||
decode(oel.component_number, null, null,decode(oel.option_number, null, '.',null)||
'.'||to_char(oel.component_number)) like (p_order_line)
and exists (select 1
from wms_license_plate_numbers lpn
, wms_lpn_contents lpc
where lpn.outermost_lpn_id = p_outermost_lpn_id
and lpn.lpn_id = lpc.parent_lpn_id
and lpc.inventory_item_id = oel.inventory_item_id
)
and NVL(oel.project_id,-1)=NVL(p_project_id,-1) and NVL(oel.task_id,-1)=NVL(p_task_id,-1)
-- Bug# 4258360: Do not include order lines with crossdocked WDD records
AND NOT EXISTS (SELECT 'xdock'
FROM wsh_delivery_details wdd_xdock
WHERE wdd_xdock.source_header_id = oel.header_id
AND wdd_xdock.source_line_id = oel.line_id
AND wdd_xdock.released_status = 'S'
AND wdd_xdock.move_order_line_id IS NULL)
GROUP BY oel.line_id
, to_char(oel.line_number) ||'.'||to_char(oel.shipment_number) ||
decode(oel.option_number,NULL,NULL,'.'||to_char(oel.option_number))||
decode(oel.component_number, null, null,decode(oel.option_number, null,'.',null)||
'.'||to_char(oel.component_number))
, oel.inventory_item_id
, oel.item_revision
, oel.PROJECT_ID
, oel.TASK_ID
, oel.END_ITEM_UNIT_NUMBER
, oel.SHIP_TOLERANCE_ABOVE
, oel.ship_tolerance_below
, oel.FLOW_STATUS_CODE
, oel.SHIPPING_INTERFACED_FLAG
, oel.REQUEST_DATE
, msik.serial_number_control_code
, msik.concatenated_segments
, wdd.REQUESTED_QUANTITY_UOM
, wdd.SHIP_FROM_LOCATION_ID
, wdd.SHIP_TO_LOCATION_ID
, wdd.CUSTOMER_ID
, wdd.INTMED_SHIP_TO_LOCATION_ID
, wdd.SHIP_METHOD_CODE
, wdd.FOB_CODE
, wdd.FREIGHT_TERMS_CODE
, wds.processed_flag
, wds.processed_quantity
, wdd.src_requested_quantity_uom
ORDER BY 1,2;
Select name
, amount
, currency_code
, freight_cost_type_id
From wsh_freight_cost_types
Where sysdate between nvl(start_date_active, sysdate) and
nvl(end_date_active, sysdate)
And name like (p_text)
Order by name;
select freight_terms
, freight_terms_code
from oe_frght_terms_active_v
where freight_terms like (p_text)
order by freight_terms_code;
Select report_set_id
, name
, description
from wsh_report_sets
where usage_code = 'SHIP_CONFIRM'
and trunc(nvl(start_date_active, sysdate)) <= trunc(sysdate)
and trunc(nvl(end_date_active, sysdate+1)) > trunc(sysdate)
and name like (p_text);
Select conversion_type
, user_conversion_type
, description
From gl_daily_conversion_types
Where conversion_type <> 'EMU FIXED'
and user_conversion_type like (p_text)
Order by user_conversion_type, description;
select c.currency_code
, c.name currency_name
, c.precision
from fnd_currencies_vl c
where c.currency_flag='Y'
and c.enabled_flag='Y'
and trunc(nvl(c.start_date_active,sysdate))<=trunc(sysdate)
and trunc(nvl(c.end_date_active,sysdate+1))>trunc(sysdate)
and currency_code like (p_text)
order by c.currency_code;
select distinct wlpn.license_plate_number
, wlpn.lpn_id
, wlpn.subinventory_code
, milk.concatenated_segments
from wms_license_plate_numbers wlpn
, wms_shipping_transaction_temp wstt
, mtl_item_locations_kfv milk
WHERE wlpn.organization_id = wstt.organization_id
AND wlpn.organization_id = p_organization_id
AND wlpn.lpn_id = wstt.outermost_lpn_id
AND nvl(wstt.direct_ship_flag,'N') = 'Y'
/* Uncomment this after this flag is introduced in the table */
AND wlpn.lpn_context = 9
AND wstt.dock_door_id = p_dock_door_id
AND milk.organization_id = wlpn.organization_id
AND milk.inventory_location_id = wlpn.locator_id
AND wlpn.license_plate_number like (p_lpn)
order by wlpn.license_plate_number;
SELECT lpn_id
FROM wms_license_plate_numbers lpn
WHERE outermost_lpn_id = p_lpn_id
AND organization_id = p_org_id
AND lpn_context = 1;
lpn_contents_select_str VARCHAR2(4000);
lpn_contents_select_str := 'SELECT parent_lpn_id, inventory_item_id, quantity, uom_code, revision ';
lpn_contents_select_str := lpn_contents_select_str || ' FROM wms_lpn_contents ';
lpn_contents_select_str := lpn_contents_select_str || ' WHERE parent_lpn_id IN ';
lpn_contents_select_str := lpn_contents_select_str || lpn_id_str;
OPEN x_lpn_contents FOR lpn_contents_select_str;
by which the existing container in shipping is updated.
*/
FUNCTION get_container_name(p_container_name IN VARCHAR2) RETURN VARCHAR2 IS
CURSOR l_container IS
select container_name from wsh_delivery_details_ob_grp_v
where container_name like p_container_name||'-@-%';
select DISTINCT outermost_lpn_id,Nvl(trip_id,0)
from WMS_SHIPPING_TRANSACTION_TEMP
where organization_id = p_organization_id
and dock_door_id = p_dock_door_id
and nvl(direct_ship_flag,'N') = 'N';
PROCEDURE update_lpn_contents
(p_outermost_lpn_id IN NUMBER,
p_org_id IN NUMBER,
x_return_status OUT nocopy VARCHAR2,
x_msg_count OUT nocopy NUMBER,
x_msg_data OUT nocopy VARCHAR2) IS
TYPE lpn_id_tbl_type IS TABLE OF wms_lpn_contents.parent_lpn_id%TYPE;
|| ' p_org_id : ' || p_org_id,'UPDATE_LPN_CONTENTS');
SELECT wlc.parent_lpn_id, wlc.inventory_item_id
bulk collect INTO l_parent_lpn_id, l_inventory_item_id
from wms_lpn_contents wlc, wms_license_plate_numbers wlpn, mtl_system_items_kfv msik
where wlc.organization_id = p_org_id
and msik.organization_id = p_org_id
and wlpn.organization_id = wlc.organization_id
AND wlpn.outermost_lpn_id = p_outermost_lpn_id
and wlpn.lpn_id = wlc.parent_lpn_id
and wlc.inventory_item_id = msik.inventory_item_id
and msik.serial_number_control_code = 6
and wlc.serial_summary_entry <> 1;
debug('Found ' || l_parent_lpn_id.COUNT || ' entries to update','UPDATE_LPN_CONTENTS');
debug('The list of lpn_id to update:','UPDATE_LPN_CONTENTS');
debug(j || ': ' || l_parent_lpn_id(j),'UPDATE_LPN_CONTENTS');
UPDATE wms_lpn_contents
SET serial_summary_entry = 1
WHERE parent_lpn_id = l_parent_lpn_id(i)
AND inventory_item_id = l_inventory_item_id(i);
debug('Did not find any content lpn to update','UPDATE_LPN_CONTENTS');
END update_lpn_contents;