[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT wth.Transaction_ID,
wth.Document_Type,
wth.Document_Direction,
wth.Document_Number,
wth.Orig_Document_Number,
wth.Entity_Number,
wth.Entity_Type,
wth.Trading_Partner_ID,
wth.Action_Type,
wth.Transaction_Status,
wth.ECX_Message_ID,
wth.Event_Name,
wth.Event_Key,
wth.Item_Type,
wth.Internal_Control_Number,
wth.document_revision,
wth.Attribute_Category,
wth.Attribute1,
wth.Attribute2,
wth.Attribute3,
wth.Attribute4,
wth.Attribute5,
wth.Attribute6,
wth.Attribute7,
wth.Attribute8,
wth.Attribute9,
wth.Attribute10,
wth.Attribute11,
wth.Attribute12,
wth.Attribute13,
wth.Attribute14,
wth.Attribute15,
NULL -- LSP PROJECT : just added for dependency for client_id
FROM Wsh_Transactions_History wth,
Wsh_New_Del_Interface wndi
WHERE wndi.interface_action_code = g_interface_action_code
AND wndi.delivery_interface_id = to_number(wth.entity_number)
AND wth.transaction_id = p_transaction_id
AND wth.transaction_status = nvl(p_transaction_status, wth.transaction_status);
select transaction_status
from wsh_transactions_history
where transaction_id = v_trx_id;
l_transaction_query := 'SELECT wth.Transaction_ID, ';
l_transaction_query := l_transaction_query || '( SELECT max(wth1.document_revision) document_revision, ';
l_transaction_query := l_transaction_query || ' ( SELECT 1 from wsh_new_del_interface wndi ';
FND_FILE.put_line(FND_FILE.output, 'No. of Shipment Requests selected for processing => ' || l_total);
WSH_DEBUG_SV.log(l_module_name,'No. of Shipment Requests selected for processing ', l_total);
select 'x'
from oe_order_lines_all oel,
wsh_delivery_details wdd
where oel.open_flag = 'Y'
and oel.shipped_quantity is null
and oel.line_id = c_line_id
and wdd.source_code = 'OE'
and wdd.source_line_id = oel.line_id
and wdd.released_status in ( 'N','R','S','Y','B','X' );
SELECT su.org_id,su.order_type_id
FROM
hz_party_sites ps,
hz_cust_acct_sites_all ca,
hz_cust_site_uses_all su
WHERE ps.party_site_id = c_party_site_id
AND su.cust_acct_site_id = ca.cust_acct_site_id
AND ca.party_site_id = ps.party_site_id
AND ca.cust_account_id = c_cust_acct_id;
SELECT transaction_id, entity_number
BULK COLLECT INTO l_txn_history_tbl, l_entity_number_tbl
FROM wsh_transactions_history
WHERE transaction_status in ( 'IP', 'AP', 'ER' )
AND document_type = 'SR'
AND document_direction = 'I'
AND document_revision <= l_transaction_rec.document_revision
AND document_number = l_transaction_rec.document_number
order by document_revision
for update nowait;
SELECT wndi.delivery_interface_id
BULK COLLECT INTO l_delivery_interface_tbl
FROM Wsh_New_Del_Interface wndi,
Wsh_Transactions_History wth
WHERE wndi.interface_action_code = g_interface_action_code
AND wndi.delivery_interface_id = to_number(wth.entity_number)
AND wth.transaction_status in ( 'IP', 'AP', 'ER' )
AND wth.document_type = 'SR'
AND wth.document_direction = 'I'
AND wth.document_revision <= l_transaction_rec.document_revision
AND wth.document_number = l_transaction_rec.document_number
for update of wndi.delivery_interface_id nowait;
SELECT wddi.delivery_detail_interface_id, wdai.del_assgn_interface_id
BULK COLLECT INTO l_detail_interface_tbl, l_del_assgn_interface_tbl
FROM Wsh_Del_Details_Interface wddi,
Wsh_Del_Assgn_Interface wdai,
Wsh_Transactions_History wth
WHERE wddi.interface_action_code = g_interface_action_code
AND wdai.interface_action_code = g_interface_action_code
AND wddi.delivery_detail_interface_id = wdai.delivery_detail_interface_id
AND wdai.delivery_interface_id = wth.entity_number
AND wth.transaction_status in ( 'IP', 'AP', 'ER' )
AND wth.document_type = 'SR'
AND wth.document_direction = 'I'
AND wth.document_revision <= l_transaction_rec.document_revision
AND wth.document_number = l_transaction_rec.document_number
for update of wddi.delivery_detail_interface_id, wdai.del_assgn_interface_id nowait;
SELECT wie.interface_error_id
BULK COLLECT INTO l_del_interface_error_tbl
FROM wsh_interface_errors wie,
wsh_transactions_history wth
WHERE wie.interface_table_name = 'WSH_NEW_DEL_INTERFACE'
AND wie.interface_action_code = g_interface_action_code
AND wth.transaction_status in ( 'IP', 'AP', 'ER' )
AND wth.document_type = 'SR'
AND wth.document_direction = 'I'
AND wth.document_revision <= l_transaction_rec.document_revision
AND wth.document_number = l_transaction_rec.document_number
AND wie.interface_id = to_number(wth.entity_number)
FOR UPDATE NOWAIT;
SELECT wie.interface_error_id
BULK COLLECT INTO l_det_interface_error_tbl
FROM wsh_interface_errors wie
WHERE interface_table_name = 'WSH_DEL_DETAILS_INTERFACE'
AND interface_action_code = g_interface_action_code
AND interface_id in
( select wddi.delivery_detail_interface_id
FROM Wsh_Del_Details_Interface wddi,
Wsh_Del_Assgn_Interface wdai,
wsh_transactions_history wth
WHERE wddi.interface_action_code = g_interface_action_code
AND wdai.interface_action_code = g_interface_action_code
AND wddi.delivery_detail_interface_id = wdai.delivery_detail_interface_id
AND wth.transaction_status in ( 'IP', 'AP', 'ER' )
AND wth.document_type = 'SR'
AND wth.document_direction = 'I'
AND wth.document_revision <= l_transaction_rec.document_revision
AND wth.document_number = l_transaction_rec.document_number
AND wdai.delivery_interface_id = to_number(wth.entity_number) )
FOR UPDATE NOWAIT;
SELECT distinct currency_code
INTO l_temp_currency_code
FROM Wsh_Del_Details_Interface wddi,
Wsh_Del_Assgn_Interface wdai
WHERE wddi.interface_action_code = g_interface_action_code
AND wdai.interface_action_code = g_interface_action_code
AND wddi.currency_code is not null
AND wddi.delivery_detail_interface_id = wdai.delivery_detail_interface_id
AND wdai.delivery_interface_id = l_entity_number;
UPDATE wsh_transactions_history
SET transaction_status = 'SC',
program_application_id = FND_GLOBAL.Prog_Appl_Id,
program_id = FND_GLOBAL.Conc_Program_Id,
request_id = FND_GLOBAL.Conc_Request_Id,
program_update_date = sysdate,
last_updated_by = FND_GLOBAL.User_Id,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.Login_Id
WHERE transaction_id = l_txn_history_tbl(i);
WSH_DEBUG_SV.logmsg(l_module_name, sql%rowcount || ' row(s) updated in wsh_transactions_history');
WSH_DEBUG_SV.logmsg(l_module_name, 'Calling WSH_PROCESS_INTERFACED_PKG.Delete_Interface_Records', WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_PROCESS_INTERFACED_PKG.Delete_Interface_Records (
p_del_interface_id_tbl => l_delivery_interface_tbl,
p_del_det_interface_id_tbl => l_detail_interface_tbl,
p_del_assgn_interface_id_tbl => l_del_assgn_interface_tbl,
p_del_error_interface_id_tbl => l_del_interface_error_tbl,
p_det_error_interface_id_tbl => l_det_interface_error_tbl,
x_return_status => l_return_status);
WSH_DEBUG_SV.logmsg(l_module_name, 'Error occured in Delete_Interface_Records => ' || l_return_status, WSH_DEBUG_SV.C_ERR_LEVEL);
WSH_DEBUG_SV.log(l_module_name, 'Sales order created/updated/cancelled ', x_header_rec.order_number );
l_modify_oe_iface_flag := 'Y'; --Need to update oe_interfaced_flag value to 'X' (om interface is not required).
l_modify_otm_flag := 'Y'; --Need to update ignore for planning value.
l_modify_otm_flag := 'N'; --Should not update ignore for planning value.
UPDATE wsh_delivery_details
SET reference_number = l_details_interface_tab(i).source_header_number,
reference_line_number = l_details_interface_tab(i).source_line_number,
reference_line_quantity = l_details_interface_tab(i).src_requested_quantity,
reference_line_quantity_uom = l_details_interface_tab(i).src_requested_quantity_uom,
original_lot_number = l_details_interface_tab(i).lot_number,
original_revision = l_details_interface_tab(i).revision,
original_locator_id = l_details_interface_tab(i).locator_id,
lot_number = l_details_interface_tab(i).lot_number,
revision = l_details_interface_tab(i).revision,
locator_id = l_details_interface_tab(i).locator_id,
earliest_pickup_date = l_details_interface_tab(i).earliest_pickup_date,
latest_pickup_date = l_details_interface_tab(i).latest_pickup_date,
earliest_dropoff_date = l_details_interface_tab(i).earliest_dropoff_date,
latest_dropoff_date = l_details_interface_tab(i).latest_dropoff_date,
-- LSP PROJECT
client_id = DECODE(l_transaction_rec.action_type,'A',l_client_id,client_id),
ignore_for_planning = DECODE(l_modify_otm_flag,'Y',l_gnore_for_planning,ignore_for_planning),
oe_interfaced_flag = DECODE(l_modify_oe_iface_flag,'Y','X',oe_interfaced_flag),
-- LSP PROJECT
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.User_Id,
program_application_id = FND_GLOBAL.Prog_Appl_Id,
program_id = FND_GLOBAL.Conc_Program_Id,
request_id = FND_GLOBAL.Conc_Request_Id,
program_update_date = sysdate
WHERE source_code = 'OE'
AND source_line_id = l_details_interface_tab(i).line_id
RETURNING delivery_detail_id BULK COLLECT INTO l_delivery_detail_tab;
WSH_DEBUG_SV.logmsg(l_module_name, sql%rowcount || ' row(s) updated for Line Id =>' || l_details_interface_tab(i).line_id);
WSH_DEBUG_SV.log(l_module_name, 'Update transaction history table count', l_upd_txn_history_tbl.count);
UPDATE wsh_transactions_history
SET transaction_status = 'SC',
entity_type = decode(transaction_id, l_transaction_rec.transaction_id, 'ORDER', entity_type),
entity_number = decode(transaction_id, l_transaction_rec.transaction_id, x_header_rec.header_id, entity_number),
program_application_id = FND_GLOBAL.Prog_Appl_Id,
program_id = FND_GLOBAL.Conc_Program_Id,
request_id = FND_GLOBAL.Conc_Request_Id,
program_update_date = sysdate,
last_updated_by = FND_GLOBAL.User_Id,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.Login_Id
WHERE transaction_id = l_upd_txn_history_tbl(i);
WSH_DEBUG_SV.logmsg(l_module_name, sql%rowcount || ' row(s) updated in wsh_transactions_history');
WSH_DEBUG_SV.logmsg(l_module_name, 'Calling WSH_PROCESS_INTERFACED_PKG.Delete_Interface_Records', WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_PROCESS_INTERFACED_PKG.Delete_Interface_Records (
p_del_interface_id_tbl => l_delivery_interface_tbl,
p_del_det_interface_id_tbl => l_detail_interface_tbl,
p_del_assgn_interface_id_tbl => l_del_assgn_interface_tbl,
p_del_error_interface_id_tbl => l_del_interface_error_tbl,
p_det_error_interface_id_tbl => l_det_interface_error_tbl,
x_return_status => l_return_status);
WSH_DEBUG_SV.logmsg(l_module_name, 'Error occured in Delete_Interface_Records', WSH_DEBUG_SV.C_ERR_LEVEL);
l_del_interface_error_tbl.delete;
l_det_interface_error_tbl.delete;
SELECT wie.interface_error_id
BULK COLLECT INTO l_del_interface_error_tbl
FROM wsh_interface_errors wie
WHERE wie.interface_table_name = 'WSH_NEW_DEL_INTERFACE'
AND wie.interface_action_code = g_interface_action_code
AND wie.interface_id = l_entity_number;
SELECT wie.interface_error_id
BULK COLLECT INTO l_det_interface_error_tbl
FROM wsh_interface_errors wie
WHERE interface_table_name = 'WSH_DEL_DETAILS_INTERFACE'
AND interface_action_code = g_interface_action_code
AND interface_id in
( select wddi.delivery_detail_interface_id
FROM Wsh_Del_Details_Interface wddi,
Wsh_Del_Assgn_Interface wdai
WHERE wddi.interface_action_code = g_interface_action_code
AND wdai.interface_action_code = g_interface_action_code
AND wddi.delivery_detail_interface_id = wdai.delivery_detail_interface_id
AND wdai.delivery_interface_id = l_entity_number );
WSH_DEBUG_SV.logmsg(l_module_name, 'Calling WSH_PROCESS_INTERFACED_PKG.Delete_Interface_Records', WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_PROCESS_INTERFACED_PKG.Delete_Interface_Records (
p_del_interface_id_tbl => l_dummy,
p_del_det_interface_id_tbl => l_dummy,
p_del_assgn_interface_id_tbl => l_dummy,
p_del_error_interface_id_tbl => l_del_interface_error_tbl,
p_det_error_interface_id_tbl => l_det_interface_error_tbl,
x_return_status => l_return_status);
WSH_DEBUG_SV.logmsg(l_module_name, 'Error occured in Delete_Interface_Records', WSH_DEBUG_SV.C_ERR_LEVEL);
WSH_DEBUG_SV.logmsg(l_module_name, 'Calling WSH_TRANSACTIONS_HISTORY_PKG.Create_Update_Txns_History', WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_TRANSACTIONS_HISTORY_PKG.Create_Update_Txns_History (
p_txns_history_rec => l_transaction_rec,
x_txns_id => l_transaction_rec.transaction_id,
x_return_status => l_return_status );
wsh_debug_sv.log (l_module_name, 'Return status after Create_Update_Txns_History ', l_return_status);
WSH_DEBUG_SV.logmsg(l_module_name, 'Error occured in Create_Update_Txns_History', WSH_DEBUG_SV.C_ERR_LEVEL);
SELECT delivery_interface_id,
organization_code,
organization_id,
customer_id,
customer_name,
ship_to_customer_id,
ship_to_customer_name,
ship_to_address_id,
ship_to_address1,
ship_to_address2,
ship_to_address3,
ship_to_address4,
ship_to_city,
ship_to_state,
ship_to_country,
ship_to_postal_code,
ship_to_contact_id,
ship_to_contact_name,
ship_to_contact_phone,
invoice_to_customer_id,
invoice_to_customer_name,
invoice_to_address_id,
invoice_to_address1,
invoice_to_address2,
invoice_to_address3,
invoice_to_address4,
invoice_to_city,
invoice_to_state,
invoice_to_country,
invoice_to_postal_code,
invoice_to_contact_id,
invoice_to_contact_name,
invoice_to_contact_phone,
deliver_to_customer_id,
deliver_to_customer_name,
deliver_to_address_id,
deliver_to_address1,
deliver_to_address2,
deliver_to_address3,
deliver_to_address4,
deliver_to_city,
deliver_to_state,
deliver_to_country,
deliver_to_postal_code,
deliver_to_contact_id,
deliver_to_contact_name,
deliver_to_contact_phone,
transaction_type_id,
price_list_id,
null,
currency_code,
carrier_code,
carrier_id,
service_level,
mode_of_transport,
freight_terms_code,
fob_code,
null, -- Shipping Method Code
null, -- Org Id
null, -- Document Revision
null, -- Order Number
client_code -- LSP PROJECT : client_code
FROM Wsh_New_Del_Interface wndi
WHERE wndi.delivery_interface_id = p_delivery_interface_id;
select header_id,
open_flag,
order_type_id,
version_number,
sold_to_org_id,
ship_to_org_id,
invoice_to_org_id,
deliver_to_org_id,
sold_to_contact_id,
ship_to_contact_id,
invoice_to_contact_id,
deliver_to_contact_id,
ship_from_org_id,
price_list_id,
payment_term_id,
shipping_method_code,
freight_terms_code,
fob_point_code
from oe_order_headers_all
where order_number = p_order_number
and order_type_id = p_order_type_id;
select line_id,
decode(open_flag,'N','N',decode(shipped_quantity,null,'Y','N')) open_flag,
ordered_quantity,
inventory_item_id,
ordered_item_id,
order_quantity_uom,
ship_tolerance_above,
ship_tolerance_below,
request_date,
schedule_ship_date,
ship_set_id,
shipping_instructions,
packing_instructions,
shipment_priority_code,
cust_po_number,
subinventory,
unit_selling_price,
rownum
from oe_order_lines_all
where line_number = p_line_number
and header_id = p_header_id;
select set_name
into x_om_line_rec_type.ship_set_name
from oe_sets
where set_id = l_ship_set_id;
select oel.line_id
from oe_order_lines_all oel,
wsh_del_details_interface wddi,
wsh_del_assgn_interface wdai
where oel.header_id = p_header_id
and oel.line_number = wddi.line_number
and wddi.interface_action_code = g_interface_action_code
and wdai.interface_action_code = g_interface_action_code
and wddi.delivery_detail_interface_id = wdai.delivery_detail_interface_id
and wdai.delivery_interface_id = p_delivery_interface_id
for update of oel.line_id nowait;
select oel.line_id
from oe_order_lines_all oel
where oel.header_id = p_header_id
and not exists
( select '1'
from wsh_del_details_interface wddi,
wsh_del_assgn_interface wdai
where oel.line_number = wddi.line_number
and wddi.interface_action_code = g_interface_action_code
and wdai.interface_action_code = g_interface_action_code
and wddi.delivery_detail_interface_id = wdai.delivery_detail_interface_id
and wdai.delivery_interface_id = p_delivery_interface_id )
for update of oel.line_id nowait;
select delivery_detail_id
from wsh_delivery_details
where source_code = 'OE'
and source_line_id = c_line_id
for update nowait;
select header_id
into l_header_id
from oe_order_headers_all
where header_id = p_header_id
for update nowait;
select delivery_detail_id
bulk collect into l_details_tab
from wsh_delivery_details
where source_code = 'OE'
and source_line_id = l_line_rec.line_id
for update nowait;
select delivery_detail_id
bulk collect into l_details_tab
from wsh_delivery_details
where source_code = 'OE'
and source_line_id = l_line_rec.line_id
for update nowait;
select wdd.*
from wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_new_deliveries wnd
where wnd.status_code in ( 'CL', 'IT', 'CO' )
and wnd.delivery_id = wda.delivery_id
and wda.delivery_detail_id = wdd.delivery_detail_id
and source_code = 'OE'
and released_status in ( 'C', 'Y' )
and wdd.source_line_id = c_line_id
and rownum = 1;
IF x_line_tbl(i).operation = OE_GLOBALS.G_OPR_UPDATE THEN
IF nvl(x_header_rec.sold_to_customer_ref, FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR THEN
x_line_tbl(i).sold_to_customer_ref := x_header_rec.sold_to_customer_ref;
SELECT order_source_id
FROM OE_ORDER_SOURCES
WHERE name = p_client_code;
x_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
x_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
select freight_code
into l_carrier_code
from wsh_carriers
where carrier_id = x_del_interface_rec.carrier_id;
select carrier_id
into x_del_interface_rec.carrier_id
from wsh_carriers
where freight_code = x_del_interface_rec.carrier_code;
select 'x'
into l_tmp
from wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_new_deliveries wnd
where wnd.status_code in ( 'CO', 'IT', 'CL' )
and wnd.delivery_id = wda.delivery_id
and wda.delivery_detail_id = wdd.delivery_detail_id
and wdd.source_code = 'OE'
and released_status in ( 'Y', 'C' )
and wdd.source_header_id = l_header_id;
SELECT wddi.delivery_detail_interface_id,
wddi.lot_number,
wddi.subinventory,
wddi.revision,
wddi.locator_id,
wddi.locator_code,
wddi.line_number,
wddi.customer_item_number,
wddi.customer_item_id,
wddi.item_number,
wddi.inventory_item_id,
p_del_interface_rec.organization_id,
wddi.item_description,
wddi.requested_quantity,
wddi.requested_quantity_uom,
wddi.src_requested_quantity,
wddi.src_requested_quantity_uom,
wddi.currency_code,
nvl(wddi.unit_selling_price, 0),
wddi.ship_tolerance_above,
wddi.ship_tolerance_below,
wddi.date_requested,
wddi.date_scheduled,
wddi.earliest_pickup_date,
wddi.latest_pickup_date,
wddi.earliest_dropoff_date,
wddi.latest_dropoff_date,
wddi.ship_set_name,
wddi.packing_instructions,
wddi.shipping_instructions,
wddi.shipment_priority_code,
wddi.source_header_number,
wddi.source_line_number,
wddi.cust_po_number,
null, -- Line Id
'N', -- Schedule Date Changed
'N' -- Changed Flag
FROM Wsh_Del_Details_Interface wddi,
Wsh_Del_Assgn_Interface wdai,
Wsh_New_Del_Interface wndi
WHERE wddi.interface_action_code = g_interface_action_code
AND wdai.interface_action_code = g_interface_action_code
AND wddi.delivery_detail_interface_id = wdai.delivery_detail_interface_id
AND wdai.delivery_interface_id = wndi.delivery_interface_id
AND wndi.delivery_interface_id = p_del_interface_rec.delivery_interface_id
ORDER BY wddi.line_number;
SELECT cust_acct_site_id
INTO l_address_id
FROM hz_cust_site_uses_all
WHERE org_id = p_del_interface_rec.org_id
AND site_use_id = l_ship_to_org_id;
SELECT oel.line_id,
oel.line_number
FROM oe_order_lines_all oel
WHERE header_id = p_om_header_rec_type.header_id
AND NOT EXISTS
( SELECT 'X'
FROM Wsh_Del_Details_Interface wddi,
Wsh_Del_Assgn_Interface wdai,
Wsh_New_Del_Interface wndi
WHERE wddi.line_number = oel.line_number
AND wddi.interface_action_code = g_interface_action_code
AND wdai.interface_action_code = g_interface_action_code
AND wddi.delivery_detail_interface_id = wdai.delivery_detail_interface_id
AND wdai.delivery_interface_id = wndi.delivery_interface_id
AND wndi.delivery_interface_id = p_delivery_interface_rec.delivery_interface_id );
IF (l_line_id is not null) THEN /* Line Update Mode */
x_line_tbl(l_line_cnt).line_id := l_line_id;
x_line_tbl(l_line_cnt).operation := OE_GLOBALS.G_OPR_UPDATE;
SELECT oe_order_lines_S.NEXTVAL into l_line_id from dual;
x_line_tbl(l_line_cnt).operation := OE_GLOBALS.G_OPR_UPDATE;
SELECT wdd.delivery_detail_id,
wdd.original_subinventory,
wdd.original_lot_number,
wdd.original_revision,
wdd.original_locator_id,
wdd.organization_id,
wdd.inventory_item_id,
wdd.earliest_pickup_date,
wdd.latest_pickup_date,
wdd.earliest_dropoff_date,
wdd.latest_dropoff_date,
wdd.source_line_id,
wdd.reference_number,
wdd.reference_line_number,
wdd.reference_line_quantity,
wdd.reference_line_quantity_uom,
wdd.rowid
FROM Wsh_Delivery_Details wdd
WHERE wdd.source_code = 'OE'
AND wdd.source_line_id = c_line_id
AND ROWNUM = 1;
select 'x'
into l_tmp
from wsh_delivery_details wdd
where source_code = 'OE'
and source_line_id = l_line_id
and released_status in ( 'Y', 'C' )
and exists
( select 'y'
from wsh_delivery_assignments wda,
wsh_new_deliveries wnd
where wnd.status_code in ( 'CO', 'IT', 'CL' )
and wnd.delivery_id = wda.delivery_id
and wda.delivery_detail_id = wdd.delivery_detail_id )
and rownum = 1;
select 'x'
into l_tmp
from wsh_delivery_details
where source_code = 'OE'
and source_line_id = l_line_id
and released_status in ( 'S', 'Y', 'C' )
and rownum = 1;