The following lines contain the word 'select', 'insert', 'update' or 'delete':
select printer_name
from wsh_report_printers_v wrp
where wrp.level_type_id = '10006'
and application_id = 665
and concurrent_program_id = (
select concurrent_program_id from
fnd_concurrent_programs_vl
where concurrent_program_name =v_concurrent_program_name
and application_id = 665
and rownum = 1 )
and organization_id = v_organization_id
and wrp.subinventory = v_subinventory
and wrp.enabled_flag = 'Y'
order by decode ( nvl( wrp.default_printer_flag ,2) ,
'Y' ,1 , 2 ) ;
select printer_name
from wsh_report_printers_v wrp
where wrp.level_type_id = '10008'
and application_id = 665
and concurrent_program_id = (
select concurrent_program_id from
fnd_concurrent_programs_vl
where concurrent_program_name = v_concurrent_program_name
and application_id = 665
and rownum = 1 )
and level_value_id = v_organization_id
and wrp.enabled_flag = 'Y'
order by decode ( nvl( wrp.default_printer_flag ,2) ,
'Y' ,1 , 2 ) ;
-- First insert the org in the global table
WSH_INV_INTEGRATION_GRP.G_ORGTAB(WSH_INV_INTEGRATION_GRP.G_ORGTAB.count + 1 ):= p_organization_id|| '~' ||v_concurrent_program_name;
tables.The purpose of this procedure is to update the inventory_interfaced_flag on
wsh_delivery_details to 'Y' if inventory interface process has completed successfully
and also to update the pending_interface_flag for the corresponding trip stops to NULL.
*/
PROCEDURE Complete_Inv_Interface(
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_txn_header_id IN NUMBER,
p_txn_batch_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
CURSOR detail_rec_csr IS
SELECT st.stop_id, wdd.delivery_detail_id
FROM wsh_delivery_details wdd,
mtl_transactions_interface mtf,
wsh_delivery_assignments_v da ,
wsh_delivery_legs dg,
wsh_new_deliveries dl,
wsh_trip_stops st
WHERE wdd.delivery_detail_id = mtf.picking_line_id
AND mtf.error_code IS NULL
AND mtf.transaction_header_id = p_txn_header_id
AND nvl(mtf.transaction_batch_id,-1) = nvl(nvl(p_txn_batch_id,mtf.transaction_batch_id),-1)
AND ((mtf.transaction_action_id IN ( 21,3,2,1)
AND mtf.transaction_source_type_id = 8)
OR ((mtf.transaction_action_id = 1) AND
mtf.transaction_source_type_id IN (2,16,13)))
AND wdd.container_flag = 'N'
AND nvl(wdd.inv_interfaced_flag , 'N') <> 'Y'
AND nvl(wdd.inv_interfaced_flag , 'N') <> 'X'
AND wdd.released_status <> 'D'
AND (exists (
SELECT mmt.picking_line_id
FROM mtl_material_transactions mmt
WHERE mmt.picking_line_id = wdd.delivery_detail_id
and transaction_source_type_id in ( 2,8,13,16 )
and trx_source_line_id = wdd.source_line_id
)
)
AND wdd.delivery_detail_id = da.delivery_detail_id
AND dl.delivery_id = da.delivery_id
AND da.delivery_id IS NOT NULL
AND st.stop_id = dg.pick_up_stop_id
AND st.stop_location_id = dl.initial_pickup_location_id
AND dg.delivery_id = dl.delivery_id
AND wdd.source_code in ('OE','OKE', 'WSH')
ORDER BY st.stop_id, wdd.delivery_detail_id
FOR UPDATE of wdd.delivery_detail_id NOWAIT;
SELECT wdd.delivery_detail_id
FROM wsh_trip_stops wts,
wsh_delivery_legs wdl,
wsh_delivery_assignments_v wda,
wsh_delivery_details wdd
WHERE (wdd.inv_interfaced_flag IN ('N', 'P') OR wdd.oe_interfaced_flag <> 'Y')
AND wts.stop_id = p_stop_id
AND wts.stop_location_id = wdd.ship_from_location_id
AND wts.stop_id = wdl.pick_up_stop_id
AND wdl.delivery_id = wda.delivery_id
AND wda.delivery_id IS NOT NULL
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.source_code in ('OE','OKE', 'WSH')
AND wdd.released_status <> 'D'
AND rownum = 1;
UPDATE wsh_delivery_details
SET inv_interfaced_flag = 'Y'
WHERE delivery_detail_id = l_det_rec.delivery_detail_id(i);
WSH_DEBUG_SV.log(l_module_name,'No of Record Update to Y l_row_count',l_row_count);
If yes, then update the pending_interface_flag of the trip to NULL.*/
l_index := l_det_rec.stop_id.FIRST;
SELECT 'X' INTO l_temp
FROM WSH_TRIP_STOPS
WHERE stop_id = l_det_rec.stop_id(l_index)
FOR UPDATE NOWAIT;
UPDATE wsh_trip_stops
SET pending_interface_flag = NULL
WHERE stop_id = l_det_rec.stop_id(l_index);
WSH_DEBUG_SV.log(l_module_name,'pending_interface_flag update to NULL for stop',l_det_rec.stop_id(l_index));