[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select distinct wt.trip_id
from wsh_trips wt,
wsh_trip_stops wts,
wsh_delivery_legs wdl,
wsh_new_deliveries wnd
where wnd.RCV_SHIPMENT_HEADER_ID = p_shpmt_header_id
and wnd.delivery_id = wdl.delivery_id
and wdl.pick_up_stop_id = wts.stop_id
and wts.trip_id = wt.trip_id;
update wsh_trips
set status_code = 'IT'
where trip_id = l_trip_id_tab(i);
select 'Y'
from wsh_inbound_txn_history wth1,
wsh_inbound_txn_history wth2
where wth1.shipment_header_id = p_shipment_header_id
and wth1.transaction_type = 'ASN'
and wth2.parent_shipment_header_id = wth1.shipment_header_id;
update wsh_new_deliveries
set rcv_shipment_header_id = NULL,
status_code = p_status_code_tab(i)
where delivery_id = p_delivery_id_tab(i)
RETURNING organization_id into l_organization_id ; --Added for Pick To POD WF
p_update_flag => 'Y',
p_calc_wv_if_frozen => 'N',
x_return_status => l_return_status);
select 'Y'
from wsh_inbound_txn_history wth1,
wsh_inbound_txn_history wth2
where wth1.shipment_header_id = p_shipment_header_id
and wth1.transaction_type = 'ASN'
and wth2.parent_shipment_header_id = wth1.shipment_header_id;
select wdd.source_line_id,
wdd.po_shipment_line_id,
wdd.delivery_detail_id,
wdd.ship_from_location_id,
wdd.rcv_shipment_line_id,
wdd.released_status,
wnd.delivery_id,
wnd.asn_shipment_header_id,
wnd.rcv_shipment_header_id,
wnd.status_code,
wdd.picked_quantity,
wdd.shipped_quantity
from wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
wsh_new_deliveries wnd
where wnd.rcv_shipment_header_id = p_shipment_header_id
and wnd.delivery_id = wda.delivery_id
and wda.delivery_detail_id = wdd.delivery_detail_id
and wdd.source_code = 'PO'
order by wdd.source_line_id, wdd.po_shipment_line_id;
select sum(returned_quantity),
sum(returned_quantity2)
from wsh_delivery_details wdd
where source_line_id = p_source_line_id
and po_shipment_line_id = p_po_line_loc_id
and released_status = 'L'
and source_code = 'PO';
select sum(requested_quantity), sum(requested_quantity2)
from wsh_delivery_details wdd
where source_line_id = p_source_line_id
and po_shipment_line_id = p_po_line_loc_id
and ( released_status = 'X'
or
(released_status = 'L' and received_quantity is null)
)
and source_code = 'PO'
and routing_req_id is null;
select wdd.delivery_detail_id
from wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
where wdd.source_line_id = p_source_line_id
and wdd.container_flag = 'N'
and wdd.source_code = 'PO'
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.parent_delivery_detail_id is not null;
select wdd.po_shipment_line_id,
wdd.delivery_detail_id
from wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
wsh_new_deliveries wnd
where wnd.rcv_shipment_header_id = p_shipment_header_id
and wnd.delivery_id = wda.delivery_id
and wda.delivery_detail_id = wdd.delivery_detail_id
and wdd.source_code = 'PO';
select 'Y'
from wsh_delivery_details
where source_line_id = p_source_line_id
and po_shipment_line_id = p_po_shipment_line_id
and source_code = 'PO'
and released_status = 'P'
and rownum = 1;
cursor l_delete_det_csr (p_source_line_id IN NUMBER,
p_po_shpmt_line_id IN NUMBER) is
select delivery_detail_id
from wsh_delivery_details
where source_line_id = p_source_line_id
and po_shipment_line_id = p_po_shpmt_line_id
and ( released_status = 'X'
or
(released_status = 'L' and received_quantity is null)
)
and routing_req_id is null
for update of delivery_detail_id nowait;
l_delete_det_tbl wsh_util_core.id_tab_type;
open l_delete_det_csr(l_source_line_id_tab(i),
l_po_line_loc_id_tab(i));
fetch l_delete_det_csr bulk collect into l_delete_det_tbl;
close l_delete_det_csr;
IF (l_delete_det_tbl.count > 0) THEN
--{
-- We can delete all the open lines for the po line location.
-- first deleting the assignments
FORALL i IN l_delete_det_tbl.FIRST..l_delete_det_tbl.LAST
DELETE wsh_delivery_assignments_v
WHERE delivery_detail_id = l_delete_det_tbl(i);
FORALL i IN l_delete_det_tbl.FIRST..l_delete_det_tbl.LAST
DELETE WSH_DELIVERY_DETAILS
WHERE delivery_detail_id = l_delete_det_tbl(i);
l_delete_det_tbl.delete;
delete from wsh_delivery_assignments_v
where delivery_detail_id in
(select delivery_detail_id
from wsh_delivery_details
where source_line_id = l_source_line_id_tab(i)
and po_shipment_line_id = l_po_line_loc_id_tab(i)
and ( released_status = 'X'
or
(released_status = 'L' and received_quantity is null)
)
and routing_req_id is null);
delete from wsh_delivery_details
where source_line_id = l_source_line_id_tab(i)
and po_shipment_line_id = l_po_line_loc_id_tab(i)
and ( released_status = 'X'
or
(released_status = 'L' and received_quantity is null)
)
and routing_req_id is null;
update wsh_delivery_details
set released_status = 'X'
where released_status = 'L'
and source_code = 'PO'
and po_shipment_line_id = l_po_line_loc_id_tab(i)
and source_line_id = l_source_line_id_tab(i)
and ship_from_location_id = -1
and rcv_shipment_line_id is null
RETURNING delivery_detail_id BULK COLLECT INTO l_detail_tab;
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,'Before doing the update');
update wsh_delivery_details
set returned_quantity = NULL,
returned_quantity2 = NULL,
received_quantity =NULL,
received_quantity2 = NULL,
released_status = l_released_status_tab(i),
rcv_shipment_line_id = l_rcv_shipment_line_id_tab(i),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.user_id
where delivery_detail_id = l_del_det_id_tab(i)
and source_code = 'PO';
WSH_DEBUG_SV.logmsg(l_module_name,'After doing the update');
WSH_DEBUG_SV.logmsg(l_module_name,'After doing the update');
WSH_INTEGRATION.DBI_Update_Detail_Log
(p_delivery_detail_id_tab => l_del_det_id_tab,
p_dml_type => 'UPDATE',
x_return_status => l_dbi_rs);
update wsh_delivery_details
set returned_quantity = NULL,
returned_quantity2 = NULL,
received_quantity =NULL,
received_quantity2 = NULL,
released_status = 'C',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.user_id
where delivery_detail_id in (select wda.delivery_detail_id
from wsh_delivery_assignments_v wda,
wsh_new_deliveries wnd
where wnd.rcv_shipment_header_id = p_shipment_header_id
and wda.delivery_id = wnd.delivery_id)
and container_flag = 'Y'
returning delivery_detail_id
bulk collect into l_cnt_det_id_tab;
WSH_INTEGRATION.DBI_Update_Detail_Log
(p_delivery_detail_id_tab => l_cnt_det_id_tab,
p_dml_type => 'UPDATE',
x_return_status => l_dbi_rs);
x_delete_rec_flag OUT NOCOPY VARCHAR2,
x_max_rcv_txn_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2
)
IS
--{
--
cursor l_max_txn_id_csr (p_shipment_line_id IN NUMBER) is
select max(transaction_id)
from rcv_fte_transactions_v
where shipment_line_id = p_shipment_line_id;
select transaction_id,
parent_transaction_id,
primary_quantity,
primary_uom_code,
secondary_quantity,
secondary_uom_code,
DECODE(transaction_type,'MATCH','RECEIPT','UNORDERED','RECEIPT','MATCHED','RECEIPT', 'RECEIVE','RECEIPT', transaction_type) transaction_type,
DECODE(parent_transaction_type, 'MATCH','RECEIPT','UNORDERED','RECEIPT','MATCHED','RECEIPT', 'RECEIVE','RECEIPT', parent_transaction_type) parent_transaction_type
from rcv_fte_transactions_v
where shipment_line_id = p_shipment_line_id
order by nvl(parent_transaction_id, transaction_id), transaction_id;
x_delete_rec_flag := 'N';
WSH_DEBUG_SV.log(l_module_name,'x_delete_rec_flag',x_delete_rec_flag);
select rfl.shipment_line_id,
rfl.item_id,
rfl.item_revision,
rfl.item_description,
rfl.truck_num,
rfl.quantity_shipped,
rfl.quantity_received,
null quantity_returned,
rfl.uom_code,
rfl.unit_of_measure,
rfl.po_release_id,
rfl.packing_slip,
rfl.container_num,
rfl.line_num,
rfl.shipment_unit_price,
rfl.secondary_quantity_shipped,
rfl.secondary_quantity_received,
null secondary_quantity_returned,
rfl.secondary_uom_code,
rfl.secondary_unit_of_measure,
rfl.po_header_id,
rfl.po_line_id,
rfl.po_line_location_id,
rfl.po_segment1 po_order_num,
rfl.po_ship_to_location_id,
rfl.po_shipment_num,
rfl.po_line_number,
msiv.concatenated_segments item_number,
porel.release_num,
rfl.asn_line_flag,
rfl.revision_num po_revision_num,
porel.revision_num rel_revision_num
from rcv_fte_lines_v rfl,
mtl_system_items_vl msiv,
po_releases_all porel,
rcv_shipment_lines rsl
where rfl.shipment_header_id = p_shipment_header_id
and rsl.shipment_header_id = p_shipment_header_id
and rsl.shipment_line_id = rfl.shipment_line_id
and rfl.item_id = msiv.inventory_item_id(+)
and rfl.organization_id = msiv.organization_id(+)
and rfl.po_release_id = porel.po_release_id(+)
order by rfl.po_line_id,
rfl.po_line_location_id,
rfl.shipment_line_id;
select distinct rfl.shipment_line_id,
rfl.item_id,
rfl.item_revision,
rfl.item_description,
rfl.truck_num,
rfl.quantity_shipped,
rfl.quantity_received,
null quantity_returned,
rfl.uom_code,
rfl.unit_of_measure,
rfl.po_release_id,
rfl.packing_slip,
rfl.container_num,
rfl.line_num,
rfl.shipment_unit_price,
rfl.secondary_quantity_shipped,
rfl.secondary_quantity_received,
null secondary_quantity_returned,
rfl.secondary_uom_code,
rfl.secondary_unit_of_measure,
rfl.po_header_id,
rfl.po_line_id,
rfl.po_line_location_id,
rfl.po_segment1 po_order_num,
rfl.po_ship_to_location_id,
rfl.po_shipment_num,
rfl.po_line_number,
msiv.concatenated_segments item_number,
porel.release_num,
rfl.asn_line_flag,
rfl.revision_num po_revision_num,
porel.revision_num rel_revision_num
from rcv_fte_lines_v rfl,
mtl_system_items_vl msiv,
po_releases_all porel,
wsh_inbound_txn_history wth,
rcv_shipment_lines rsl
where rfl.shipment_header_id = p_shipment_header_id
and rsl.shipment_header_id = p_shipment_header_id
and rsl.shipment_line_id = rfl.shipment_line_id
and rfl.item_id = msiv.inventory_item_id(+)
and rfl.organization_id = msiv.organization_id(+)
and rfl.po_release_id = porel.po_release_id(+)
and rfl.shipment_line_id = wth.shipment_line_id
and wth.shipment_header_id = p_shipment_header_id
and wth.transaction_type IN ('RECEIPT_CORRECTION','RTV', 'RTV_CORRECTION','RTV_CORRECTION_NEGATIVE','RTV_CORRECTION_POSITIVE','RECEIPT_ADD', 'RECEIPT_CORRECTION_NEGATIVE', 'RECEIPT_CORRECTION_POSITIVE')
order by rfl.po_line_id,
rfl.po_line_location_id,
rfl.shipment_line_id;
select 'Y'
from wsh_inbound_txn_history
where shipment_header_id = p_shipment_header_id
and transaction_type = 'RECEIPT'
and status like 'MATCHED%';
select wdd.inventory_item_id,
wdd.requested_quantity_uom,
wdd.organization_id,
muom.unit_of_measure,
wdd.src_requested_quantity,
wdd.src_requested_quantity2
from wsh_delivery_details wdd,
mtl_units_of_measure muom
where wdd.source_line_id = p_po_line_id
and wdd.po_shipment_line_id = p_po_line_location_id
and wdd.source_code = 'PO'
and wdd.requested_quantity_uom = muom.uom_code
and rownum =1;
select DISTINCT DECODE(transaction_type, 'RECEIPT_ADD',1, 'RECEIPT_CORRECTION_POSITIVE',2,'RECEIPT_CORRECTION_NEGATIVE', 3, 'RTV',4, 'RTV_CORRECTION_NEGATIVE',5, 'RTV_CORRECTION_POSITIVE',6,7) txn_type
from wsh_inbound_txn_history
where shipment_line_id = p_shipment_line_id
and shipment_header_id = p_shipment_header_id
and transaction_type IN ('RECEIPT_ADD', 'RECEIPT_CORRECTION_POSITIVE', 'RECEIPT_CORRECTION_NEGATIVE', 'RTV', 'RTV_CORRECTION_NEGATIVE', 'RTV_CORRECTION_POSITIVE')
order by txn_type;
select 'X'
from rcv_transactions
where shipment_line_id = p_shipment_line_id
and transaction_type in ('RECEIVE', 'MATCH')
and rownum=1;
select 'X'
from wsh_inbound_txn_history
where shipment_header_id = p_shipment_header_id
and transaction_type = p_transaction_type
FOR UPDATE OF STATUS NOWAIT;
select max(transaction_id)
from wsh_inbound_txn_history
where shipment_line_id = p_shipment_line_id
and shipment_header_id = p_shipment_header_id;
select 'Y'
from wsh_inbound_txn_history
where shipment_header_id = p_shipment_header_id
and transaction_type = 'ASN';
select 'Y'
from wsh_delivery_details
where source_header_id = p_source_header_id
and source_line_id = p_source_line_id
and po_shipment_line_id = p_po_shpmt_line_id
and rcv_shipment_line_id = p_rcv_shpmt_line_id
and rownum = 1;
l_delete_rec_flag VARCHAR2(1);
FND_MESSAGE.SET_NAME('WSH','WSH_IB_TXN_UPDATE_ERROR');
x_delete_rec_flag => l_delete_rec_flag,
x_max_rcv_txn_id => l_max_rcv_txn_id,
x_return_status => l_return_status
);
select status, receipt_number
from wsh_inbound_txn_history
where shipment_header_id = p_shipment_header_id
and transaction_type = 'RECEIPT';
select 'Y'
from wsh_inbound_txn_history
where shipment_header_id = p_shipment_header_id
and transaction_type = 'ASN';
WSH_PO_CMG_PVT.Update_Attributes(
p_line_rec => l_line_rec,
p_action_prms => l_po_action_prms,
p_dd_list => l_dd_list,
p_dd_id_unassigned => l_dd_id_unassigned,
p_wt_vol_dd_id => l_wt_vol_dd_id,
x_return_status => l_return_status);
select delivery_detail_id,
delivery_id,
shipment_line_id,
child_index,
requested_quantity,
shipped_quantity,
received_quantity,
returned_quantity,
requested_quantity_db,
shipped_quantity_db,
received_quantity_db,
returned_quantity_db,
requested_quantity2,
shipped_quantity2,
received_quantity2,
returned_quantity2,
requested_quantity2_db,
shipped_quantity2_db,
received_quantity2_db,
returned_quantity2_db,
shipment_line_id_db,
ship_from_location_id,
po_shipment_line_id,
source_line_id,
process_corr_rtv_flag,
process_asn_rcv_flag,
requested_quantity_uom,
requested_quantity_uom2,
source_header_id,
released_status,
parent_delivery_detail_id,
picked_quantity,
picked_quantity2,
picked_quantity picked_quantity_db,
picked_quantity2 picked_quantity2_db,
dd_last_update_date
from wsh_inbound_del_lines_temp;
select ship_from_location_id
from wsh_inbound_txn_history
where shipment_header_id = p_shipment_header_id
AND transaction_type IN ('ASN','RECEIPT');
update wsh_inbound_del_lines_temp
set ship_from_location_id = l_header_ship_from_loc_id
where ship_from_location_id is null
or ship_from_location_id = WSH_UTIL_CORE.C_NULL_SF_LOCN_ID;
l_ib_del_det_rec.last_update_date_tab;
WSH_DEBUG_SV.log(l_module_name,'last_update_date_tab(' || i || ')',l_ib_del_det_rec.last_update_date_tab(i));
delete from wsh_inbound_txn_history
where shipment_line_id = p_shipment_line_id_tab(i)
and transaction_type NOT IN ('ASN','RECEIPT')
and transaction_id <= p_max_txn_id_tab(i);