[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE updateDlvyWeightVolume
(
p_deliveryId IN NUMBER,
p_diff_gross_weight IN NUMBER,
p_diff_net_weight IN NUMBER,
p_diff_volume IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
)
IS
--{
--
--
l_num_warnings NUMBER := 0;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'updateDlvyWeightVolume';
UPDATE wsh_new_deliveries
SET gross_weight = NVL(gross_weight,0) - NVL(p_diff_gross_weight,0),
net_weight = NVL(net_weight,0) - NVL(p_diff_net_weight,0),
volume = NVL(volume,0) - NVL(p_diff_volume,0),
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHERE delivery_id = p_deliveryId;
wsh_util_core.default_handler('WSH_ASN_RECEIPT_PVT.updateDlvyWeightVolume');
END updateDlvyWeightVolume;
PROCEDURE updateLPNWeightVolume
(
p_LPNId IN NUMBER,
p_diff_gross_weight IN NUMBER,
p_diff_net_weight IN NUMBER,
p_diff_volume IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
)
IS
--{
--
--
l_num_warnings NUMBER := 0;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'updateLPNWeightVolume';
UPDATE wsh_delivery_details
SET gross_weight = NVL(gross_weight,0) - NVL(p_diff_gross_weight,0),
net_weight = NVL(net_weight,0) - NVL(p_diff_net_weight,0),
filled_volume = NVL(filled_volume,0) - NVL(p_diff_volume,0),
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHERE delivery_detail_id = p_LPNId;
wsh_util_core.default_handler('WSH_ASN_RECEIPT_PVT.updateLPNWeightVolume');
END updateLPNWeightVolume;
PROCEDURE updateWeightVolume
(
p_entity IN VARCHAR2,
x_GWTcachetbl IN OUT NOCOPY WSH_UTIL_CORE.key_value_tab_type,
x_GWTcacheExttbl IN OUT NOCOPY WSH_UTIL_CORE.key_value_tab_type,
x_NWTcachetbl IN OUT NOCOPY WSH_UTIL_CORE.key_value_tab_type,
x_NWTcacheExttbl IN OUT NOCOPY WSH_UTIL_CORE.key_value_tab_type,
x_VOLcachetbl IN OUT NOCOPY WSH_UTIL_CORE.key_value_tab_type,
x_VOLcacheExttbl IN OUT NOCOPY WSH_UTIL_CORE.key_value_tab_type,
x_return_status OUT NOCOPY VARCHAR2
)
IS
--{
--
--
l_num_warnings NUMBER := 0;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'updateWeightVolume';
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ASN_RECEIPT_PVT.updateDlvyWeightVolume',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_ASN_RECEIPT_PVT.updateDlvyWeightVolume
(
p_deliveryId => l_delivery_lpn_id,
p_diff_gross_weight => l_diff_gross_weight,
p_diff_net_weight => l_diff_net_weight,
p_diff_volume => l_diff_volume,
x_return_status => l_return_status
);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ASN_RECEIPT_PVT.updateLPNWeightVolume',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_ASN_RECEIPT_PVT.updateLPNWeightVolume
(
p_LPNId => l_delivery_lpn_id,
p_diff_gross_weight => l_diff_gross_weight,
p_diff_net_weight => l_diff_net_weight,
p_diff_volume => l_diff_volume,
x_return_status => l_return_status
);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ASN_RECEIPT_PVT.updateDlvyWeightVolume',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_ASN_RECEIPT_PVT.updateDlvyWeightVolume
(
p_deliveryId => l_delivery_lpn_id,
p_diff_gross_weight => l_diff_gross_weight,
p_diff_net_weight => l_diff_net_weight,
p_diff_volume => l_diff_volume,
x_return_status => l_return_status
);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ASN_RECEIPT_PVT.updateLPNWeightVolume',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_ASN_RECEIPT_PVT.updateLPNWeightVolume
(
p_LPNId => l_delivery_lpn_id,
p_diff_gross_weight => l_diff_gross_weight,
p_diff_net_weight => l_diff_net_weight,
p_diff_volume => l_diff_volume,
x_return_status => l_return_status
);
wsh_util_core.default_handler('WSH_ASN_RECEIPT_PVT.updateWeightVolume');
END updateWeightVolume;
select src_requested_quantity2,
src_requested_quantity_uom2,
src_requested_quantity,
src_requested_quantity_uom
from WSH_DELIVERY_DETAILS
where
source_line_id = p_line_id and --performance
po_shipment_line_id = p_line_location_id and
source_code = 'PO'
order by decode(Released_status,'X',1,2);
select sum(requested_quantity)
from wsh_delivery_details
where
source_line_id = p_line_id and --performance
po_shipment_line_id = p_line_location_id and
source_code = 'PO';
SELECT t.trip_id
FROM wsh_new_deliveries dl,
wsh_delivery_legs dg,
wsh_trip_stops st,
wsh_trips t
WHERE dl.delivery_id = p_del_id_c1 AND
dl.delivery_id = dg.delivery_id AND
dg.drop_off_stop_id = st.stop_id AND
st.stop_location_id = dl.ULTIMATE_DROPOFF_LOCATION_ID AND
st.trip_id = t.trip_id;
SELECT '1'
FROM wsh_new_deliveries dl,
wsh_delivery_legs dg
WHERE dl.delivery_id = p_del_id_c2 AND
dl.delivery_id = dg.delivery_id;
SELECT wdd.gross_weight, wdd.net_weight, wdd.volume,
NVL(wdd.wv_frozen_flag,'Y') wv_frozen_flag,
wda.parent_delivery_detail_id,
wda.delivery_id,
NVL(wdd1.wv_frozen_flag,'Y') lpn_wv_frozen_flag,
NVL(wnd.wv_frozen_flag,'Y') dlvy_wv_frozen_flag,
NVL
(
wdd.shipped_quantity,
NVL
(
wdd.picked_quantity,
wdd.requested_quantity
)
) wv_qty,
wdd.requested_quantity requested_quantity
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
wsh_delivery_details wdd1,
wsh_new_deliveries wnd
where wdd.delivery_detail_id = p_delivery_detail_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
--AND wda.delivery_id = wnd.delivery_id
AND wda.parent_delivery_detail_id = wdd1.delivery_detail_id (+)
AND wda.delivery_id = wnd.delivery_id (+);
SELECT parent_delivery_Detail_id
FROM wsh_delivery_assignments_v
WHERE delivery_Detail_id = p_wdd_id;
l_update_dd_rec update_dd_rec_type;
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit populate_update_dd_rec',WSH_DEBUG_SV.C_PROC_LEVEL);
populate_update_dd_rec(
p_dd_rec => p_dd_rec,
p_index => i,
p_line_rec => p_line_rec,
p_gross_weight => l_old_dd_gross_weight,
p_net_weight => l_old_dd_net_weight,
p_volume => l_old_dd_volume,
x_release_status => l_release_status,
l_update_dd_rec => l_update_dd_rec,
x_lpnIdCacheTbl => l_lpnIdCacheTbl,
x_lpnIdCacheExtTbl => l_lpnIdCacheExtTbl,
x_return_status => l_return_status);
-- WSH_BULK_PROCESS_PVT.bulk_insert_details API to do the insert operation.
--
-- Debug Statements
--
IF l_debug_on THEN
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PO_CMG_PVT.POPULATE_ADDITIONAL_LINE_INFO',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_BULK_PROCESS_PVT.BULK_INSERT_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
-- Calling WSH_BULK_PROCESS_PVT.bulk_insert_details
WSH_BULK_PROCESS_PVT.bulk_insert_details(
P_line_rec => P_line_rec,
p_index => p_dd_rec.shpmt_line_id_idx_tab(i),
p_action_prms => l_action_prms,--change by arun
p_additional_line_info_rec => l_additional_line_info_rec,
X_return_status => l_return_status);
-- Updating wsh_delivery_assignments_v after the insert into wsh_delivery_details.
update wsh_delivery_assignments_v
set delivery_id = p_dd_rec.delivery_id_tab(i),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
where delivery_detail_id =
p_line_rec.delivery_detail_id(p_dd_rec.shpmt_line_id_idx_tab(i));
p_dd_rec.last_update_date_tab(i) := NULL;
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit populate_update_dd_rec',WSH_DEBUG_SV.C_PROC_LEVEL);
-- for bulk update.
populate_update_dd_rec(
p_dd_rec => p_dd_rec,
p_index => i,
p_line_rec => p_line_rec,
x_release_status => l_release_status,
l_update_dd_rec => l_update_dd_rec,
x_lpnIdCacheTbl => l_lpnIdCacheTbl,
x_lpnIdCacheExtTbl => l_lpnIdCacheExtTbl,
x_return_status => l_return_status);
-- will be cosolidated and a single record will be inserted into wsh_delivery_details
-- in case if there are no open delivery_details present for the same.
IF p_dd_rec.transaction_type = 'ASN' THEN
--{
IF p_dd_rec.shipped_qty_tab(p_dd_rec.child_index_tab(k)) IS NULL AND
p_dd_rec.received_qty_tab(p_dd_rec.child_index_tab(k)) IS NULL
THEN
--{
p_dd_info.released_status := 'X';
WSH_DEBUG_SV.log(l_module_name,'requested qty of record inserted is',p_dd_info.requested_quantity);
WSH_DEBUG_SV.log(l_module_name,'received qty of record inserted is',p_dd_info.received_quantity);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit populate_update_dd_rec',WSH_DEBUG_SV.C_PROC_LEVEL);
populate_update_dd_rec(
p_dd_rec => p_dd_rec,
p_index => i,
p_line_rec => p_line_rec,
p_gross_weight => l_old_dd_gross_weight,
p_net_weight => l_old_dd_net_weight,
p_volume => l_old_dd_volume,
x_release_status => l_release_status,
l_update_dd_rec => l_update_dd_rec,
x_lpnIdCacheTbl => l_lpnIdCacheTbl,
x_lpnIdCacheExtTbl => l_lpnIdCacheExtTbl,
x_return_status => l_return_status);
-- need to insert record into wdd.
--
-- Debug Statements
--
IF l_debug_on THEN
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PO_CMG_PVT.POPULATE_ADDITIONAL_LINE_INFO',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_BULK_PROCESS_PVT.BULK_INSERT_DETAILS - 2 ',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_BULK_PROCESS_PVT.bulk_insert_details (
P_line_rec => P_line_rec,
p_index => p_dd_rec.shpmt_line_id_idx_tab(i),
p_action_prms => l_action_prms,--change by arun
p_additional_line_info_rec => l_additional_line_info_rec,
X_return_status => l_return_status);
update wsh_delivery_assignments_v
set delivery_id = p_dd_rec.delivery_id_tab(i),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
where delivery_detail_id = p_line_rec.delivery_detail_id(p_dd_rec.shpmt_line_id_idx_tab(i));
p_dd_rec.last_update_date_tab(i) := NULL;
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit populate_update_dd_rec',WSH_DEBUG_SV.C_PROC_LEVEL);
-- Once delviery detail is created, populate the l_update_dd_rec to do the bulk_update at the end
populate_update_dd_rec(
p_dd_rec => p_dd_rec,
p_index => i,
p_line_rec => p_line_rec,
x_release_status => l_release_status,
l_update_dd_rec => l_update_dd_rec,
x_lpnIdCacheTbl => l_lpnIdCacheTbl,
x_lpnIdCacheExtTbl => l_lpnIdCacheExtTbl,
x_return_status => l_return_status);
WSH_DEBUG_SV.log(l_module_name,'l_update_dd_rec.delivery_detail_id.COUNT',l_update_dd_rec.delivery_detail_id.COUNT);
l_itemp := l_update_dd_rec.delivery_detail_id.first;
WSH_DEBUG_SV.log(l_module_name,'l_update_dd_rec.requested_quantity(l_itemp)',l_update_dd_rec.requested_quantity(l_itemp));
WSH_DEBUG_SV.log(l_module_name,'l_update_dd_rec.received_quantity(l_itemp)',l_update_dd_rec.received_quantity(l_itemp));
WSH_DEBUG_SV.log(l_module_name,'l_update_dd_rec.delivery_detail_id(l_itemp)',l_update_dd_rec.delivery_detail_id(l_itemp));
WSH_DEBUG_SV.log(l_module_name,'l_update_dd_rec.released_status(l_itemp)',l_update_dd_rec.released_status(l_itemp));
WSH_DEBUG_SV.log(l_module_name,'l_update_dd_rec.released_status_db(l_itemp)',l_update_dd_rec.released_status_db(l_itemp));
WSH_DEBUG_SV.log(l_module_name,'l_update_dd_rec.gross_weight(l_itemp)',l_update_dd_rec.gross_weight(l_itemp));
WSH_DEBUG_SV.log(l_module_name,'l_update_dd_rec.net_weight(l_itemp)',l_update_dd_rec.net_weight(l_itemp));
WSH_DEBUG_SV.log(l_module_name,'l_update_dd_rec.volume(l_itemp)',l_update_dd_rec.volume(l_itemp));
WSH_DEBUG_SV.log(l_module_name,'l_update_dd_rec.ship_from_location_id(l_itemp)',l_update_dd_rec.ship_from_location_id(l_itemp));
WSH_DEBUG_SV.log(l_module_name,'l_update_dd_rec.last_update_date(l_itemp)',l_update_dd_rec.last_update_date(l_itemp));
l_itemp := l_update_dd_rec.delivery_detail_id.next(l_itemp);
FORALL i in 1..l_update_dd_rec.delivery_detail_id.COUNT
update wsh_delivery_details
set requested_quantity = l_update_dd_rec.requested_quantity(i),
shipped_quantity = l_update_dd_rec.shipped_quantity(i),
returned_quantity = l_update_dd_rec.returned_quantity(i),
received_quantity = l_update_dd_rec.received_quantity(i),
requested_quantity2 = l_update_dd_rec.requested_quantity2(i),
shipped_quantity2 = l_update_dd_rec.shipped_quantity2(i),
returned_quantity2 = l_update_dd_rec.returned_quantity2(i),
received_quantity2 = l_update_dd_rec.received_quantity2(i),
released_status = l_update_dd_rec.released_status(i),
rcv_shipment_line_id = l_update_dd_rec.rcv_shipment_line_id(i),
inventory_item_id = l_update_dd_rec.inventory_item_id(i),
ship_from_location_id = NVL(l_update_dd_rec.ship_from_location_id(i),ship_from_location_id),
item_description = l_update_dd_rec.item_description(i),
tracking_number = l_update_dd_rec.waybill_num(i),
gross_weight = NVL(l_update_dd_rec.gross_weight(i), gross_weight),
net_weight = NVL(l_update_dd_rec.net_weight(i), net_weight),
volume = NVL(l_update_dd_rec.volume(i), volume),
earliest_pickup_date = NVL(earliest_pickup_date, l_update_dd_rec.shipped_date(i)),
latest_pickup_date = NVL(latest_pickup_date, l_update_dd_rec.shipped_date(i)),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
where delivery_detail_id = l_update_dd_rec.delivery_detail_id(i)
and released_status = l_update_dd_rec.released_status_db(i)
AND last_update_date = NVL(l_update_dd_rec.last_update_date(i), last_update_date)
RETURNING delivery_detail_id BULK COLLECT INTO l_detail_tab;
IF SQL%ROWCOUNT <> l_update_dd_rec.delivery_detail_id.COUNT THEN
IF l_debug_on THEN
WSH_DEBUG_SV.logmsg(l_module_name,'NUMBER OF ROWS UPDATED IS NOT EQUAL TO THE NUMBER OF ROWS THAT HAD TO BE BULK UPDATED');
WSH_DEBUG_SV.log(l_module_name,'l_update_dd_rec.delivery_detail_id.COUNT',l_update_dd_rec.delivery_detail_id.COUNT);
WSH_INTEGRATION.DBI_Update_Detail_Log
(p_delivery_detail_id_tab => l_detail_tab,
p_dml_type => 'UPDATE',
x_return_status => l_dbi_rs);
SELECT
Wdd.delivery_detail_id,
Wda.parent_delivery_detail_id,
wdd.po_shipment_line_id
FROM
WSH_DELIVERY_DETAILS WDD,
wsh_delivery_assignments_v WDA,
WSH_NEW_DELIVERIES WND
WHERE wdd.delivery_detail_id = wda.delivery_detail_id and
wdd.line_direction not in ('O','IO') and
Wda.delivery_id = wnd.delivery_id and
Wnd.asn_shipment_header_id = p_header_id;*/
SELECT
Delivery_id
FROM
Wsh_new_deliveries
WHERE ASN_SHIPMENT_HEADER_ID = p_header_id;
UPDATE WSH_DELIVERY_DETAILS
SET released_status = 'X',
Shipped_quantity = NULL,
received_quantity = NULL,
shipped_quantity2 = NULL,
received_quantity2 = NULL,
rcv_shipment_line_id = NULL,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
where delivery_detail_id in (select wdd.delivery_detail_id
from wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
wsh_new_deliveries wnd
where wdd.delivery_detail_id = wda.delivery_detail_id
and wda.delivery_id = wnd.delivery_id
and wnd.asn_shipment_header_id = p_header_id
and wdd.line_direction not in ('O','IO'))
and released_status = 'C'
returning delivery_detail_id,po_shipment_line_id,ship_from_location_id, picked_quantity
BULK COLLECT INTO l_dd_list.delivery_detail_id,l_dd_list.po_shipment_line_id,l_sf_locn_id_tbl, l_picked_qty_tbl;
WSH_INTEGRATION.DBI_Update_Detail_Log
(p_delivery_detail_id_tab => l_detail_tab,
p_dml_type => 'UPDATE',
x_return_status => l_dbi_rs);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ASN_RECEIPT_PVT.UPDATE_STATUS',WSH_DEBUG_SV.C_PROC_LEVEL);
update_status(
p_action_prms => p_action_prms,
p_del_ids => l_delivery_ids,
p_trip_ids => l_trip_ids,
p_stop_ids => l_stop_ids,
p_shipment_header_id_tab => l_shipment_header_id_tab,
p_initial_pickup_date_tab => l_initial_pickup_date_tab,
p_expected_receipt_date_tab => l_expected_receipt_date_tab,
p_rcv_carrier_id_tab => l_rcv_carrier_id_tab,
p_local_dd_rec => l_local_ddrec,
x_lpnGWTcachetbl => l_lpnGWTcachetbl, --NNP-WV
x_lpnGWTcacheExttbl => l_lpnGWTcacheExttbl,
x_lpnNWTcachetbl => l_lpnNWTcachetbl,
x_lpnNWTcacheExttbl => l_lpnNWTcacheExttbl,
x_lpnVOLcachetbl => l_lpnVOLcachetbl,
x_lpnVOLcacheExttbl => l_lpnVOLcacheExttbl,
x_dlvyGWTcachetbl => l_dlvyGWTcachetbl,
x_dlvyGWTcacheExttbl => l_dlvyGWTcacheExttbl,
x_dlvyNWTcachetbl => l_dlvyNWTcachetbl,
x_dlvyNWTcacheExttbl => l_dlvyNWTcacheExttbl,
x_dlvyVOLcachetbl => l_dlvyVOLcachetbl,
x_dlvyVOLcacheExttbl => l_dlvyVOLcacheExttbl,
x_return_status => l_return_status);
SELECT
wdd.delivery_detail_id
FROM
wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
WHERE
wdd.delivery_detail_id = wda.delivery_detail_id AND
wda.delivery_id = p_del_id AND
wdd.released_status = 'X';
SELECT
wdd.delivery_detail_id, nvl(wdd.container_flag,'N') container_flag
FROM
wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
WHERE
wdd.delivery_detail_id = wda.delivery_detail_id AND
wda.delivery_id = v_del_id AND
wdd.released_status in ('X', 'C');
SELECT
wdd.delivery_detail_id, nvl(wdd.container_flag,'N') container_flag
FROM
wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
wsh_new_deliveries wnd
WHERE
wdd.delivery_detail_id = wda.delivery_detail_id AND
wda.delivery_id = wnd.delivery_id AND
wnd.asn_shipment_header_id = p_shipment_header_id AND
wdd.released_status in ( 'C')
and not exists
(
SELECT 1
FROM wsh_delivery_details wdd1,
wsh_delivery_assignments_v wda1
WHERE wdd1.delivery_detail_id = wda1.delivery_detail_id
AND wda1.delivery_id = wnd.delivery_id
AND wdd1.released_status in ( 'L')
);
DELETE wsh_delivery_assignments_v
WHERE delivery_detail_id = lpn_ids_tab(i);
WSH_DEBUG_SV.log(l_module_name,'SQL%ROWCOUNT After delete LPN from WDA',SQL%ROWCOUNT);
DELETE WSH_DELIVERY_DETAILS
WHERE delivery_detail_id = lpn_ids_tab(i);
WSH_DEBUG_SV.log(l_module_name,'SQL%ROWCOUNT After delete LPN from WDD',SQL%ROWCOUNT);
UPDATE wsh_delivery_assignments_v
SET parent_delivery_detail_id = NULL
WHERE parent_delivery_detail_id = lpn_ids_tab(i);
WSH_DEBUG_SV.log(l_module_name,'SQL%ROWCOUNT After update contents on WDD',SQL%ROWCOUNT);
select wda.delivery_id,
wt.trip_id
FROM wsh_delivery_assignments_v wda,
wsh_delivery_legs wdl,
wsh_trip_stops wts,
wsh_trips wt
WHERE wda.delivery_detail_id = p_del_detail_id
AND wda.delivery_id = wdl.delivery_id
AND wdl.pick_up_stop_id = wts.stop_id
AND wts.trip_id = wt.trip_id;
select wt.trip_id
FROM wsh_delivery_assignments_v wda,
wsh_delivery_legs wdl,
wsh_trip_stops wts,
wsh_trips wt
WHERE wda.delivery_id = p_delivery_id
AND wda.delivery_id = wdl.delivery_id
AND wdl.pick_up_stop_id = wts.stop_id
AND wts.trip_id = wt.trip_id;
l_del_rows.delete;
UPDATE WSH_TRIPS
SET carrier_id = l_carrier_id,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE trip_id = l_new_trip_ids(i);
WSH_UTIL_CORE.Default_Handler('WSH_ASN_RECEIPT_PVT.UPDATE_STATUS');
SELECT
stop_id
FROM
WSH_TRIP_STOPS WTS
WHERE WTS.trip_id = l_cur_trip_id;
SELECT delivery_id, ultimate_dropoff_date, name, status_code
FROM wsh_new_deliveries wnd
WHERE wnd.asn_shipment_header_id = p_shipment_header_id
AND wnd.status_code = 'IT'
AND NOT EXISTS (
select 1
FROM wsh_delivery_assignments_v wda
WHERE wda.delivery_id = wnd.delivery_id
);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit CREATE_UPDATE_WAYBILL_PSNO_BOL 1',WSH_DEBUG_SV.C_PROC_LEVEL);
create_update_waybill_psno_bol(
p_local_dd_rec=> l_local_dd_rec,
l_loop_index => i,
pack_ids => pack_ids,
curr_del => curr_del,
curr_bol => curr_bol,
curr_lpn => curr_lpn,
curr_lpn_name => curr_lpn_name,
curr_del_det => curr_del_det,
l_psno => l_psno,
l_waybill => l_waybill,
l_psno_flag => l_psno_flag,
l_trigger => l_trigger,
l_waybill_flag=> l_waybill_flag ,
temp_dels => temp_dels,
p_action_prms => p_action_prms,
x_return_status=> l_return_status);
pack_ids.delete;
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit CREATE_UPDATE_WAYBILL_PSNO_BOL 2',WSH_DEBUG_SV.C_PROC_LEVEL);
create_update_waybill_psno_bol(
p_local_dd_rec => l_local_dd_rec,
l_loop_index => i,
pack_ids => pack_ids,
curr_del => curr_del,
curr_bol => curr_bol,
curr_lpn => curr_lpn,
curr_lpn_name => curr_lpn_name,
curr_del_det => curr_del_det,
l_psno => l_psno,
l_waybill => l_waybill,
l_psno_flag => l_psno_flag,
l_trigger => l_trigger,
l_waybill_flag=> l_waybill_flag ,
temp_dels => temp_dels,
p_action_prms => p_action_prms,
x_return_status=> l_return_status);
UPDATE wsh_trips
SET vehicle_number = prev_truck_num
WHERE trip_id = prev_trip_id;
UPDATE wsh_trips
SET vehicle_number = curr_truck_num
WHERE trip_id = curr_trip_id;
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit CREATE_UPDATE_WAYBILL_PSNO_BOL 3',WSH_DEBUG_SV.C_PROC_LEVEL);
create_update_waybill_psno_bol(
p_local_dd_rec => l_local_dd_rec,
l_loop_index => l_local_dd_rec.LAST,
pack_ids => pack_ids,
curr_del => curr_del,
curr_bol => curr_bol,
curr_lpn => curr_lpn,
curr_lpn_name => curr_lpn_name,
curr_del_det => curr_del_det,
l_psno => l_psno,
l_waybill => l_waybill,
l_psno_flag => l_psno_flag,
l_trigger => l_trigger,
l_waybill_flag=> l_waybill_flag ,
temp_dels => temp_dels,
p_action_prms => p_action_prms,
x_return_status=> l_return_status);
update_status(
p_action_prms => p_action_prms,
p_del_ids => temp_dels,
p_trip_ids => unique_trips,
p_stop_ids => l_stop_ids_tab,
p_shipment_header_id_tab => l_header_ids_for_del_ids_tab,
p_initial_pickup_date_tab => l_initial_pickup_date_tab,
p_expected_receipt_date_tab => l_expected_receipt_date_tab,
p_rcv_carrier_id_tab => l_rcv_carrier_id_tab,
p_local_dd_rec => l_local_dd_rec,
x_lpnGWTcachetbl => x_lpnGWTcachetbl, --NNP-WV
x_lpnGWTcacheExttbl => x_lpnGWTcacheExttbl,
x_lpnNWTcachetbl => x_lpnNWTcachetbl,
x_lpnNWTcacheExttbl => x_lpnNWTcacheExttbl,
x_lpnVOLcachetbl => x_lpnVOLcachetbl,
x_lpnVOLcacheExttbl => x_lpnVOLcacheExttbl,
x_dlvyGWTcachetbl => x_dlvyGWTcachetbl,
x_dlvyGWTcacheExttbl => x_dlvyGWTcacheExttbl,
x_dlvyNWTcachetbl => x_dlvyNWTcachetbl,
x_dlvyNWTcacheExttbl => x_dlvyNWTcacheExttbl,
x_dlvyVOLcachetbl => x_dlvyVOLcachetbl,
x_dlvyVOLcacheExttbl => x_dlvyVOLcacheExttbl,
x_return_status => l_return_status);
select
WDG.delivery_leg_id,
wdi.sequence_number bol
from
wsh_delivery_legs wdg,
wsh_document_instances wdi
where
wdg.delivery_leg_id = wdi.entity_id and
wdi.entity_name = 'WSH_DELIVERY_LEGS' AND
wdg.delivery_id = p_del_id AND
wdi.document_type= 'BOL';
UPDATE wsh_document_instances
SET sequence_number = p_bol,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE entity_name = 'WSH_DELIVERY_LEGS'
AND entity_id = l_delivery_leg_id
AND document_type= 'BOL';
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ASN_RECEIPT_PVT.CREATE_UPDATE_INBOUND_DOCUMENT',WSH_DEBUG_SV.C_PROC_LEVEL);
create_update_inbound_document (
p_document_number => p_bol,
p_entity_name => 'WSH_DELIVERY_LEGS',
p_delivery_id => p_del_id,
p_transaction_type => p_action_prms.action_code,
x_return_status => l_return_status);
PROCEDURE update_status (
p_action_prms IN OUT NOCOPY WSH_GLBL_VAR_STRCT_GRP.dd_action_parameters_rec_type,
p_del_ids IN wsh_util_core.id_tab_type,
p_trip_ids IN wsh_util_core.id_tab_type,
p_stop_ids IN wsh_util_core.id_tab_type,
p_shipment_header_id_tab IN wsh_util_core.id_tab_type,
p_initial_pickup_date_tab IN wsh_util_core.Date_Tab_Type ,
p_expected_receipt_date_tab IN wsh_util_core.Date_Tab_Type ,
p_rcv_carrier_id_tab IN wsh_util_core.Id_Tab_Type,
p_local_dd_rec IN LOCAL_DD_REC_TABLE_TYPE,
x_lpnGWTcachetbl IN OUT NOCOPY WSH_UTIL_CORE.key_value_tab_type,
x_lpnGWTcacheExttbl IN OUT NOCOPY WSH_UTIL_CORE.key_value_tab_type,
x_lpnNWTcachetbl IN OUT NOCOPY WSH_UTIL_CORE.key_value_tab_type,
x_lpnNWTcacheExttbl IN OUT NOCOPY WSH_UTIL_CORE.key_value_tab_type,
x_lpnVOLcachetbl IN OUT NOCOPY WSH_UTIL_CORE.key_value_tab_type,
x_lpnVOLcacheExttbl IN OUT NOCOPY WSH_UTIL_CORE.key_value_tab_type,
x_dlvyGWTcachetbl IN OUT NOCOPY WSH_UTIL_CORE.key_value_tab_type,
x_dlvyGWTcacheExttbl IN OUT NOCOPY WSH_UTIL_CORE.key_value_tab_type,
x_dlvyNWTcachetbl IN OUT NOCOPY WSH_UTIL_CORE.key_value_tab_type,
x_dlvyNWTcacheExttbl IN OUT NOCOPY WSH_UTIL_CORE.key_value_tab_type,
x_dlvyVOLcachetbl IN OUT NOCOPY WSH_UTIL_CORE.key_value_tab_type,
x_dlvyVOLcacheExttbl IN OUT NOCOPY WSH_UTIL_CORE.key_value_tab_type,
x_return_status OUT NOCOPY VARCHAR2) IS
l_delivery_info WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type;
select wcs.ship_method_code
from wsh_new_deliveries wnd,
wsh_carriers_v wcv,
wsh_carrier_services wcs,
wsh_org_carrier_services wocs
where wnd.delivery_id = p_delivery_id
and wcv.carrier_id = p_carrier_id
and wcv.active = 'A'
and wocs.organization_id = wnd.organization_id
and nvl(wcs.enabled_flag, 'N') = 'Y'
and nvl(wocs.enabled_flag, 'N')= 'Y'
and wcv.carrier_id = wcs.carrier_id
and wcs.service_level = wnd.service_level(+)
and wcs.mode_of_transport = wnd.mode_of_transport(+);
l_update_smc_flag VARCHAR2(1) := 'N';
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_STATUS';
SAVEPOINT UPDATE_STATUS_PVT;
l_update_smc_flag := NULL;
l_update_smc_flag := 'Y';
UPDATE WSH_NEW_DELIVERIES
SET status_code = 'IT',
ASN_SHIPMENT_HEADER_ID = p_shipment_header_id_tab(i),
INITIAL_PICKUP_DATE = nvl(p_initial_pickup_date_tab(i),INITIAL_PICKUP_DATE),
ULTIMATE_DROPOFF_DATE = GREATEST
(
nvl
(
p_initial_pickup_date_tab(i),
nvl
(
INITIAL_PICKUP_DATE,
nvl(p_expected_receipt_date_tab(i),ULTIMATE_DROPOFF_DATE)
)
),
nvl
(
p_expected_receipt_date_tab(i),
NVL
(
ULTIMATE_DROPOFF_DATE,
NVL(p_initial_pickup_date_tab(i), INITIAL_PICKUP_DATE)
)
)
),
carrier_id = nvl(p_rcv_carrier_id_tab(i), carrier_id),
ship_method_code = decode(l_update_smc_flag, 'Y', NULL,nvl(l_ship_method_code, ship_method_code)),
service_level = decode(l_update_smc_flag, 'Y', NULL,service_level),
mode_of_transport = decode(l_update_smc_flag, 'Y', NULL,mode_of_transport),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
Where DELIVERY_ID = p_del_ids(i)
RETURNING organization_id into l_organization_id ;
UPDATE WSH_NEW_DELIVERIES
SET status_code = 'OP',
Asn_shipment_header_id = NULL,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
Where DELIVERY_ID = p_del_ids(i)
RETURNING organization_id into l_organization_id ;
UPDATE WSH_NEW_DELIVERIES
SET status_code = 'CL',
RCV_SHIPMENT_HEADER_ID = p_shipment_header_id_tab(i),
--INITIAL_PICKUP_DATE = p_initial_pickup_date_tab(i),
--INITIAL_PICKUP_DATE = nvl(p_initial_pickup_date_tab(i),INITIAL_PICKUP_DATE),
--ULTIMATE_DROPOFF_DATE = GREATEST(nvl(p_initial_pickup_date_tab(i),nvl(INITIAL_PICKUP_DATE, nvl(p_expected_receipt_date_tab(i),ULTIMATE_DROPOFF_DATE))),
--nvl(p_expected_receipt_date_tab(i),ULTIMATE_DROPOFF_DATE)),
INITIAL_PICKUP_DATE =
LEAST
(
nvl
(
p_initial_pickup_date_tab(i),
nvl
(
INITIAL_PICKUP_DATE,
nvl(p_expected_receipt_date_tab(i),ULTIMATE_DROPOFF_DATE)
)
),
nvl
(
p_expected_receipt_date_tab(i),
NVL
(
ULTIMATE_DROPOFF_DATE,
NVL(p_initial_pickup_date_tab(i), INITIAL_PICKUP_DATE)
)
)
),
ULTIMATE_DROPOFF_DATE = NVL
(
p_expected_receipt_date_tab(i),
NVL
(
ULTIMATE_DROPOFF_DATE,
NVL(p_initial_pickup_date_tab(i), INITIAL_PICKUP_DATE)
)
),
carrier_id = nvl(p_rcv_carrier_id_tab(i), carrier_id),
ship_method_code = decode(l_update_smc_flag, 'Y', NULL,nvl(l_ship_method_code, ship_method_code)),
service_level = decode(l_update_smc_flag, 'Y', NULL,service_level),
mode_of_transport = decode(l_update_smc_flag, 'Y', NULL,mode_of_transport),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
Where DELIVERY_ID = p_del_ids(i)
RETURNING organization_id into l_organization_id ;
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.UPDATE_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_NEW_DELIVERIES_PVT.Update_Delivery
(p_rowid => l_delivery_info.ROWID,
p_delivery_info => l_delivery_info,
x_return_status => l_return_status);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ASN_RECEIPT_PVT.updateWeightVolume',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_ASN_RECEIPT_PVT.updateWeightVolume
(
p_entity => 'DLVY',
x_GWTcachetbl => x_dlvyGWTcachetbl,
x_GWTcacheExttbl => x_dlvyGWTcacheExttbl,
x_NWTcachetbl => x_dlvyNWTcachetbl,
x_NWTcacheExttbl => x_dlvyNWTcacheExttbl,
x_VOLcachetbl => x_dlvyVOLcachetbl,
x_VOLcacheExttbl => x_dlvyVOLcacheExttbl,
x_return_status => l_return_status
);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ASN_RECEIPT_PVT.updateWeightVolume',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_ASN_RECEIPT_PVT.updateWeightVolume
(
p_entity => 'LPN',
x_GWTcachetbl => x_LPNGWTcachetbl,
x_GWTcacheExttbl => x_LPNGWTcacheExttbl,
x_NWTcachetbl => x_LPNNWTcachetbl,
x_NWTcacheExttbl => x_LPNNWTcacheExttbl,
x_VOLcachetbl => x_LPNVOLcachetbl,
x_VOLcacheExttbl => x_LPNVOLcacheExttbl,
x_return_status => l_return_status
);
ROLLBACK TO UPDATE_STATUS_PVT;
WSH_UTIL_CORE.Default_Handler('WSH_ASN_RECEIPT_PVT.UPDATE_STATUS',l_module_name);
END Update_status;
Procedure populate_update_dd_rec(
p_dd_rec IN OUT NOCOPY WSH_IB_UI_RECON_GRP.asn_rcv_del_det_rec_type,
p_index IN NUMBER,
p_line_rec IN OE_WSH_BULK_GRP.line_rec_type,
p_gross_weight IN NUMBER DEFAULT NULL,
p_net_weight IN NUMBER DEFAULT NULL,
p_volume IN NUMBER DEFAULT NULL,
x_release_status IN VARCHAR2,
l_update_dd_rec IN OUT NOCOPY update_dd_rec_type,
x_lpnIdCacheTbl IN OUT NOCOPY WSH_UTIL_CORE.key_value_tab_type,
x_lpnIdCacheExtTbl IN OUT NOCOPY WSH_UTIL_CORE.key_value_tab_type,
x_return_status OUT NOCOPY VARCHAR2
)
IS
i NUMBER;
SELECT parent_delivery_detail_id, wdd.last_update_date
FROM wsh_delivery_assignments_v wda, wsh_delivery_details wdd
WHERE wda.delivery_detail_id = p_delivery_detail_id
AND wdd.delivery_detail_id = wda.parent_delivery_detail_id;
l_lpn_last_update_date DATE;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'POPULATE_UPDATE_DD_REC';
WSH_DEBUG_SV.log(l_module_name,'l_update_dd_rec.delivery_detail_id.COUNT',l_update_dd_rec.delivery_detail_id.COUNT);
i:= (l_update_dd_rec.delivery_detail_id.COUNT)+1;
l_update_dd_rec.delivery_detail_id.EXTEND;
l_update_dd_rec.requested_quantity.EXTEND;
l_update_dd_rec.shipped_quantity.EXTEND;
l_update_dd_rec.returned_quantity.EXTEND;
l_update_dd_rec.received_quantity.EXTEND;
l_update_dd_rec.requested_quantity2.EXTEND;
l_update_dd_rec.shipped_quantity2.EXTEND;
l_update_dd_rec.returned_quantity2.EXTEND;
l_update_dd_rec.received_quantity2.EXTEND;
l_update_dd_rec.inventory_item_id.EXTEND;
l_update_dd_rec.ship_from_location_id.EXTEND;
l_update_dd_rec.item_description.EXTEND;
l_update_dd_rec.released_status.EXTEND;
l_update_dd_rec.rcv_shipment_line_id.EXTEND;
l_update_dd_rec.waybill_num.EXTEND;
l_update_dd_rec.released_status_db.EXTEND;
l_update_dd_rec.gross_weight.EXTEND;
l_update_dd_rec.net_weight.EXTEND;
l_update_dd_rec.volume.EXTEND;
l_update_dd_rec.last_update_date.EXTEND;
l_update_dd_rec.shipped_date.EXTEND;
WSH_DEBUG_SV.log(l_module_name,'p_dd_rec.last_update_date_tab(p_index)',p_dd_rec.last_update_date_tab(p_index));
l_update_dd_rec.delivery_detail_id(i) := p_dd_rec.del_detail_id_tab(p_index);
l_update_dd_rec.requested_quantity(i) := least(p_dd_rec.requested_qty_tab(p_index),l_shp_rcv_qty);
l_update_dd_rec.shipped_quantity(i) := p_dd_rec.shipped_qty_tab(p_index);
l_update_dd_rec.returned_quantity(i) := p_dd_rec.returned_qty_tab(p_index);
l_update_dd_rec.received_quantity(i) := p_dd_rec.received_qty_tab(p_index);
l_update_dd_rec.requested_quantity2(i) := least(p_dd_rec.requested_qty2_tab(p_index),l_shp_rcv_qty2); -- NNP
l_update_dd_rec.shipped_quantity2(i) := p_dd_rec.shipped_qty2_tab(p_index);
l_update_dd_rec.returned_quantity2(i) := p_dd_rec.returned_qty2_tab(p_index);
l_update_dd_rec.received_quantity2(i) := p_dd_rec.received_qty2_tab(p_index);
l_update_dd_rec.inventory_item_id(i) := nvl(p_line_rec.rcv_inventory_item_id(l_index),p_line_rec.inventory_item_id(l_index));
l_update_dd_rec.item_description(i) := nvl(p_line_rec.rcv_item_description(l_index),p_line_rec.item_description(l_index));
l_update_dd_rec.released_status(i) := x_release_status;
l_update_dd_rec.rcv_shipment_line_id(i):= p_dd_rec.shipment_line_id_tab(p_index);
l_update_dd_rec.ship_from_location_id(i):= p_dd_rec.ship_from_location_id_tab(p_index);
l_update_dd_rec.waybill_num(i) := p_line_rec.tracking_number(l_index);
l_update_dd_rec.released_status_db(i) := p_dd_rec.released_status_tab(p_index);
l_update_dd_rec.gross_weight(i) := p_gross_weight;
l_update_dd_rec.net_weight(i) := p_net_weight;
l_update_dd_rec.volume(i) := p_volume;
l_update_dd_rec.last_update_date(i) := p_dd_rec.last_update_date_tab(p_index);
l_update_dd_rec.shipped_date(i) := p_line_rec.shipped_date(l_index);
OPEN lpn_csr (p_delivery_detail_id => l_update_dd_rec.delivery_detail_id(i) );
FETCH lpn_csr INTO l_lpn_id, l_lpn_last_update_date;
WSH_DEBUG_SV.log(l_module_name,'l_lpn_lasT_update_date',l_lpn_last_update_date);
WSH_DEBUG_SV.log(l_module_name,'contents - WDD ID ',l_update_dd_rec.delivery_detail_id(i));
i:= (l_update_dd_rec.delivery_detail_id.COUNT)+1;
l_update_dd_rec.delivery_detail_id.EXTEND;
l_update_dd_rec.requested_quantity.EXTEND;
l_update_dd_rec.shipped_quantity.EXTEND;
l_update_dd_rec.returned_quantity.EXTEND;
l_update_dd_rec.received_quantity.EXTEND;
l_update_dd_rec.requested_quantity2.EXTEND;
l_update_dd_rec.shipped_quantity2.EXTEND;
l_update_dd_rec.returned_quantity2.EXTEND;
l_update_dd_rec.received_quantity2.EXTEND;
l_update_dd_rec.inventory_item_id.EXTEND;
l_update_dd_rec.ship_from_location_id.EXTEND;
l_update_dd_rec.item_description.EXTEND;
l_update_dd_rec.released_status.EXTEND;
l_update_dd_rec.rcv_shipment_line_id.EXTEND;
l_update_dd_rec.waybill_num.EXTEND;
l_update_dd_rec.released_status_db.EXTEND;
l_update_dd_rec.gross_weight.EXTEND;
l_update_dd_rec.net_weight.EXTEND;
l_update_dd_rec.volume.EXTEND;
l_update_dd_rec.last_update_date.EXTEND;
l_update_dd_rec.shipped_date.EXTEND;
l_update_dd_rec.delivery_detail_id(i) := l_lpn_id;
l_update_dd_rec.requested_quantity(i) := 1;
l_update_dd_rec.shipped_quantity(i) := 1;
l_update_dd_rec.returned_quantity(i) := NULL;
l_update_dd_rec.received_quantity(i) := 1;
l_update_dd_rec.requested_quantity2(i) := NULL;
l_update_dd_rec.shipped_quantity2(i) := NULL;
l_update_dd_rec.returned_quantity2(i) := NULL;
l_update_dd_rec.received_quantity2(i) := NULL;
l_update_dd_rec.inventory_item_id(i) := NULL;
l_update_dd_rec.item_description(i) := NULL;
l_update_dd_rec.released_status(i) := x_release_status;
l_update_dd_rec.rcv_shipment_line_id(i):= p_dd_rec.shipment_line_id_tab(p_index);
l_update_dd_rec.ship_from_location_id(i):= p_dd_rec.ship_from_location_id_tab(p_index);
l_update_dd_rec.waybill_num(i) := NULL;
l_update_dd_rec.released_status_db(i) := 'C';
l_update_dd_rec.gross_weight(i) := NULL;
l_update_dd_rec.net_weight(i) := NULL;
l_update_dd_rec.volume(i) := NULL;
l_update_dd_rec.last_update_date(i) := l_lpn_last_update_date;
l_update_dd_rec.shipped_date(i) := p_line_rec.shipped_date(l_index);
WSH_UTIL_CORE.Default_Handler('WSH_ASN_RECEIPT_PVT.POPULATE_UPDATE_DD_REC',l_module_name);
END populate_update_dd_rec;
PROCEDURE create_update_waybill_psno_bol(
p_local_dd_rec IN OUT NOCOPY LOCAL_DD_REC_TABLE_TYPE,
l_loop_index IN NUMBER,
pack_ids IN OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
curr_del IN OUT NOCOPY NUMBER,
curr_bol IN OUT NOCOPY VARCHAR2,
curr_lpn IN OUT NOCOPY NUMBER,
curr_lpn_name IN OUT NOCOPY VARCHAR2,
curr_del_det IN OUT NOCOPY NUMBER,
l_psno IN OUT NOCOPY VARCHAR2,
l_waybill IN OUT NOCOPY VARCHAR2,
l_psno_flag IN OUT NOCOPY NUMBER,
l_trigger IN OUT NOCOPY NUMBER,
l_waybill_flag IN OUT NOCOPY NUMBER,
temp_dels IN OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
p_action_prms IN OUT NOCOPY WSH_GLBL_VAR_STRCT_GRP.dd_action_parameters_rec_type,
x_return_status OUT NOCOPY VARCHAR2
)
IS
--Cursor to get the Delivery Leg ID for a given Delivery.
CURSOR get_delivery_info(p_delivery_id NUMBER) IS
SELECT dg.delivery_leg_id
FROM wsh_new_deliveries dl,
wsh_delivery_legs dg,
wsh_trip_stops st,
wsh_trips t
WHERE dl.delivery_id = p_delivery_id AND
dl.delivery_id = dg.delivery_id AND
dg.pick_up_stop_id = st.stop_id AND
st.trip_id = t.trip_id;
SELECT wdl.delivery_leg_id
FROM wsh_new_deliveries wnd,
wsh_delivery_legs wdl,
wsh_trip_stops wts,
wsh_trips wt
WHERE wnd.delivery_id = p_delivery_id AND
wnd.delivery_id = wdl.delivery_id AND
wdl.drop_off_stop_id = wts.stop_id AND
wnd.ultimate_dropoff_location_id = wts.stop_location_id AND
wts.trip_id = wt.trip_id;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_UPDATE_WAYBILL_PSNO_BOL';
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ASN_RECEIPT_PVT.CREATE_UPDATE_INBOUND_DOCUMENT ',WSH_DEBUG_SV.C_PROC_LEVEL);
create_update_inbound_document (
p_document_number => curr_bol,
p_entity_name => 'WSH_DELIVERY_LEGS',
p_delivery_id => l_new_del_id, --code changed to use the newly created Delivery ID
p_transaction_type => p_action_prms.action_code,
x_return_status => l_return_status);
pack_ids.delete;
UPDATE wsh_document_instances
SET
sequence_number = l_psno,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
where
entity_id = l_stored_del_id
AND entity_name = 'WSH_NEW_DELIVERIES'
AND document_type= 'PACK_TYPE';
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ASN_RECEIPT_PVT.CREATE_UPDATE_INBOUND_DOCUMENT',WSH_DEBUG_SV.C_PROC_LEVEL);
create_update_inbound_document (
p_document_number => l_psno,
p_entity_name => 'WSH_NEW_DELIVERIES',
p_delivery_id => l_stored_del_id,
p_transaction_type => p_action_prms.action_code,
x_return_status => l_return_status);
UPDATE
wsh_new_deliveries
SET
waybill = l_waybill,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE
delivery_id = l_stored_del_id;
WSH_UTIL_CORE.Default_Handler('WSH_ASN_RECEIPT_PVT.create_update_waybill_psno_bol',l_module_name);
END create_update_waybill_psno_bol;
PROCEDURE create_update_inbound_document (
p_document_number IN VARCHAR2,
p_entity_name IN VARCHAR2,
p_delivery_id IN NUMBER,
p_transaction_type IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
--Cursor to get a Deliveries Leg ID,ship method Code etc.
CURSOR get_delivery_info IS
SELECT dg.delivery_leg_id,
dl.initial_pickup_location_id,
dl.ULTIMATE_DROPOFF_LOCATION_ID,
t.ship_method_code,
dl.organization_id,
t.name
FROM wsh_new_deliveries dl,
wsh_delivery_legs dg,
wsh_trip_stops st,
wsh_trips t
WHERE dl.delivery_id = p_delivery_id AND
dl.delivery_id = dg.delivery_id AND
dg.drop_off_stop_id = st.stop_id AND
st.stop_location_id = dl.ULTIMATE_DROPOFF_LOCATION_ID AND
st.trip_id = t.trip_id;
select to_number(ORG_INFORMATION1)
from
HR_ORGANIZATION_INFORMATION
where
ORGANIZATION_ID = l_org_id AND
(ORG_INFORMATION_CONTEXT || '') ='Accounting Information';
select doc_sequence_category_id
from wsh_doc_sequence_categories
where document_type = l_doc_type;
select
'1'
from
wsh_document_instances wdi
where
wdi.entity_id = l_ent_id AND
wdi.entity_name = l_ent_name AND
wdi.document_type= l_doc_type;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_UPDATE_INBOUND_DOCUMENT';
UPDATE wsh_document_instances
SET sequence_number = p_document_number,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE entity_name = p_entity_name
AND entity_id = l_entity_id
AND document_type= l_document_type;
INSERT INTO wsh_document_instances
( document_instance_id
, document_type
, sequence_number
, status
, final_print_date
, entity_name
, entity_id
, doc_sequence_category_id
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
, program_application_id
, program_id
, program_update_date
, request_id
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
)
VALUES
( wsh_document_instances_s.nextval
, l_document_type
, p_document_number
, l_status
, null
, p_entity_name
, l_entity_id
, l_doc_sequence_category_id
, fnd_global.user_id
, sysdate
, fnd_global.user_id
, sysdate
, fnd_global.login_id
, l_appl_num --Bug# 3789154
, null
, null
, null
, null
, null
, null
, null
, null
, null
, null
, null
, null
, null
, null
, null
, null
, null
, null
, null
);
WSH_UTIL_CORE.Default_Handler('WSH_ASN_RECEIPT_PVT.create_update_inbound_document ',l_module_name);
end create_update_inbound_document ;