[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE create_update_containers
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_detail_info_tab IN OUT NOCOPY
WSH_GLBL_VAR_STRCT_GRP.delivery_details_Attr_tbl_Type,
p_IN_rec IN WSH_GLBL_VAR_STRCT_GRP.detailInRecType,
x_OUT_rec OUT NOCOPY WSH_GLBL_VAR_STRCT_GRP.detailOutRecType
)
IS
--
l_debug_on BOOLEAN;
'.' || 'CREATE_UPDATE_CONTAINERS';
l_api_name CONSTANT VARCHAR2(30):= 'create_update_containers';
SELECT delivery_detail_id
FROM wsh_delivery_details
WHERE lpn_id = v_lpn_id AND
--LPN reuse project
released_status = 'X'
FOR UPDATE NOWAIT;
SELECT 1
FROM wsh_delivery_details
WHERE lpn_id = v_lpn_id
AND
--LPN reuse project
released_status = 'X';
SAVEPOINT create_update_WSHWLGPB;
wsh_debug_sv.log (l_module_name,'g_update_to_containers',g_update_to_containers);
OR (g_update_to_containers = 'N' AND g_call_group_api = 'N' )THEN
RAISE e_success;
IF p_in_rec.action_code = 'UPDATE_NULL' THEN --{
i := p_detail_info_tab.FIRST;
UPDATE wsh_delivery_details
SET lpn_id = NULL
WHERE lpn_id = p_detail_info_tab(i).lpn_id AND
--LPN reuse project
released_status = 'X';
FND_MESSAGE.SET_NAME('WSH','WSH_LPN_UPDATE_FAILED'); --bms new
ELSIF p_in_rec.action_code IN ( 'UPDATE','CREATE') THEN --}{
G_CALLBACK_REQUIRED := 'N';
IF p_in_rec.action_code = 'UPDATE' THEN --{
i := p_detail_info_tab.FIRST;
SELECT delivery_detail_id
INTO p_detail_info_tab(i).delivery_detail_id
FROM WSH_DELIVERY_DETAILS
WHERE lpn_id = p_detail_info_tab(i).lpn_id AND
--LPN reuse project
released_status = 'X';
wsh_delivery_details_grp.create_update_delivery_detail(
p_api_version_number => p_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_detail_info_tab => p_detail_info_tab,
p_IN_rec => p_in_rec,
x_OUT_rec => x_out_rec
);
ROLLBACK TO create_update_WSHWLGPB;
ROLLBACK TO create_update_WSHWLGPB;
ROLLBACK TO create_update_WSHWLGPB;
wsh_util_core.default_handler('WSH_WMS_LPN_GRP.create_update_containers');
END create_update_containers;
p_container_info_rec.last_update_date := FND_API.G_MISS_DATE;
p_container_info_rec.last_update_login := FND_API.G_MISS_NUM;
p_container_info_rec.last_updated_by := FND_API.G_MISS_NUM;
p_container_info_rec.program_update_date := FND_API.G_MISS_DATE;
select 'X'
from wsh_delivery_details
where lpn_id = p_lpn_id
--LPN Reuse project
and released_status = 'X';
select delivery_detail_id,
inventory_item_id
from wsh_delivery_details
where lpn_id = p_lpn_id
--LPN Reuse project
and released_status = 'X';
SELECT released_status,
organization_id,
container_flag,
source_code,
delivery_detail_id,
lpn_id,
customer_id,
inventory_item_id,
ship_from_location_id,
ship_to_location_id,
intmed_ship_to_location_id,
date_requested,
date_scheduled,
ship_method_code,
carrier_id,
shipping_control,
party_id,
line_direction,
source_line_id
FROM wsh_delivery_details
WHERE delivery_detail_id = p_detail_id;
SELECT wdd.released_status,
wdd.organization_id,
wdd.container_flag,
wdd.source_code,
wdd.delivery_detail_id,
wdd.lpn_id,
wdd.customer_id,
wdd.inventory_item_id,
wdd.ship_from_location_id,
wdd.ship_to_location_id,
wdd.intmed_ship_to_location_id,
wdd.date_requested,
wdd.date_scheduled,
wdd.ship_method_code,
wdd.carrier_id,
wdd.shipping_control,
wdd.party_id,
wdd.line_direction,
wdd.source_line_id,
wda.delivery_id,
wda.parent_delivery_detail_id
FROM wsh_delivery_details wdd,
wsh_delivery_assignments wda
WHERE wdd.lpn_id = p_lpn_id
and nvl(wda.type,'S') in ('S', 'C')
and wdd.delivery_detail_id = wda.delivery_detail_id
--LPN Reuse project
and wdd.released_status = 'X';
l_call_for_update_flag BOOLEAN := FALSE;
l_update_sub_loc_flag BOOLEAN := FALSE;
l_api_name CONSTANT VARCHAR2(30):= 'create_update_containers';
IF p_action_prms.action_code NOT IN ('PACK', 'UNPACK', 'ASSIGN', 'UNASSIGN', 'DELETE') THEN
FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_ACTION_CODE');
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_GRP.CREATE_UPDATE_DELIVERY_DETAIL',WSH_DEBUG_SV.C_PROC_LEVEL);
wsh_delivery_details_grp.create_update_delivery_detail(
p_api_version_number => p_api_version_number,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_detail_info_tab => l_detail_info_tab,
p_IN_rec => l_cr_up_in_rec,
x_OUT_rec => l_cr_up_out_rec
);
l_update_sub_loc_flag := TRUE;
l_cr_up_in_rec.action_code := 'UPDATE';
( p_action_prms.action_code = 'DELETE'
and l_det_lpn_id_rec.delivery_id is not null
)
or
( p_action_prms.action_code = 'UNPACK'
and l_det_lpn_id_rec.parent_delivery_detail_id is not null
)
)
THEN
--{
j := j + 1;
IF (p_action_prms.action_code = 'DELETE') THEN
--{
--
k := k + 1;
IF l_action_prms.action_code = 'DELETE' THEN
l_action_prms.action_Code := 'UNASSIGN';
IF (l_call_for_update_flag) THEN
--{
--
l_detail_info_tab(1) := l_action_prms.lpn_rec;
l_cr_up_in_rec.action_code := 'UPDATE';
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_GRP.CREATE_UPDATE_DELIVERY_DETAIL',WSH_DEBUG_SV.C_PROC_LEVEL);
wsh_delivery_details_grp.create_update_delivery_detail(
p_api_version_number => p_api_version_number,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_detail_info_tab => l_detail_info_tab,
p_IN_rec => l_cr_up_in_rec,
x_OUT_rec => l_cr_up_out_rec
);
IF (l_update_sub_loc_flag) THEN
--{
--
--
-- Debug Statements
--
IF l_debug_on THEN
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CONTAINER_ACTIONS.UPDATE_CHILD_INV_INFO',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_CONTAINER_ACTIONS.Update_child_inv_info(
p_container_id => l_exist_detail_id,
p_locator_id => p_action_prms.lpn_rec.locator_id,
p_subinventory => p_action_prms.lpn_rec.subinventory,
x_return_status => l_return_status
);
IF (p_action_prms.action_code = 'DELETE' and l_del_det_id_tbl.count > 0) THEN
--{
l_index := l_del_det_id_tbl.FIRST;
wsh_container_actions.delete_containers (
p_container_id => l_del_det_id_tbl(l_index),
x_return_status => l_return_status);
Select lpn_id FROM wsh_lpn_purge_tmp
WHERE eligible_flag = 'Y';
SELECT 1 INTO l_count
FROM wsh_lpn_purge_tmp;
DELETE FROM wsh_lpn_purge_tmp;
INSERT INTO wsh_lpn_purge_tmp
( lpn_id,
ELIGIBLE_FLAG
)VALUES(
P_lpn_rec.lpn_ids(i),
'Y'
);
Update wsh_lpn_purge_tmp
Set eligible_flag = 'N' where
Lpn_id in (
Select wt.lpn_id from
Wsh_lpn_purge_tmp wt, wsh_delivery_details wdd
Where wt.lpn_id = wdd.lpn_id
And nvl(wt.ELIGIBLE_FLAG ,'Y') = 'Y'
And NVL(wdd.line_direction,'O') IN ('IO','O')
And wdd.released_status <> 'C');
wsh_debug_sv.log (l_module_name,'Rows updated',SQL%rowcount);
Update wsh_lpn_purge_tmp
Set ELIGIBLE_FLAG = 'N'
WHERE
Lpn_id in (select wt.lpn_id
FROM wms_lpn_histories wlh,
wsh_inbound_txn_history wth,
wsh_lpn_purge_tmp wt
where wlh.parent_lpn_id = wt.lpn_id
and nvl(wt.ELIGIBLE_FLAG,'Y') = 'Y'
and wlh.source_type_id = 1
AND wlh.lpn_context = 7
and wlh.source_header_id = wth.shipment_header_id
and wth.transaction_type in ('RECEIPT', 'ASN'));
wsh_debug_sv.log (l_module_name,'Rows updated',SQL%rowcount);
UPDATE wsh_delivery_details
SET lpn_id = NULL WHERE
lpn_id IN (SELECT LPN_ID FROM wsh_lpn_purge_tmp
WHERE eligible_flag = 'Y');
select s.trip_id, l2.delivery_id
from wsh_trip_stops s,
wsh_delivery_legs l1,
wsh_delivery_legs l2,
wsh_new_deliveries d
where d.initial_pickup_location_id = s.stop_location_id
and l1.delivery_id = d.delivery_id
and l1.pick_up_stop_id = s.stop_id
and d.delivery_id = p_del_id
and l1.parent_delivery_leg_id = l2.delivery_leg_id(+);
select s.trip_id
from wsh_trip_stops s,
wsh_delivery_legs l,
wsh_new_deliveries d
where d.initial_pickup_location_id = s.stop_location_id
and l.delivery_id = d.delivery_id
and l.pick_up_stop_id = s.stop_id
and d.delivery_id = p_del_id;