[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO po_interface_errors
(interface_type,
interface_transaction_id,
error_message,
processing_date,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
SELECT 'RECEIVING',
rti.interface_transaction_id,
p_msg,
sysdate,
rti.creation_date,
rti.created_by,
rti.last_update_date,
rti.last_updated_by,
rti.last_update_login
FROM rcv_transactions_interface rti
WHERE rti.group_id = p_group_id;
UPDATE rcv_transactions_interface
SET processing_status_code = 'COMPLETED',
transaction_status_code = 'ERROR'
WHERE group_id = p_group_id;
SELECT wlh.parent_lpn_id
, wlh.inventory_item_id
, wlh.revision
, wlh.lot_number
, wlh.serial_number
, wlh.quantity
, wlh.uom_code
, wlh.organization_id
, wlh.source_name
FROM wms_lpn_histories wlh
WHERE wlh.source_header_id = p_group_id;
UPDATE wms_license_plate_numbers
SET lpn_context = 7
WHERE lpn_id = l_lpn_id;
UPDATE wms_license_plate_numbers
SET lpn_context = 6,
organization_id = l_organization_id
WHERE lpn_id = l_lpn_id;
UPDATE wms_lpn_contents
SET organization_id = l_organization_id
WHERE parent_lpn_id = l_lpn_id;
UPDATE mtl_serial_numbers
SET current_organization_id = l_organization_id,
group_mark_id = null,
current_subinventory_code = null,
current_locator_id = null
WHERE lpn_id = l_lpn_id;
SELECT from_organization_id
, receipt_source_code
, source_document_code
INTO l_from_organization_id
, l_receipt_source_code
, l_source_document_code
FROM rcv_transactions_interface
WHERE group_id = p_group_id
AND ROWNUM < 2;
UPDATE mtl_serial_numbers
SET current_status = Nvl(previous_status, current_status)
, group_mark_id = -1
, previous_status = NULL
WHERE inventory_item_id = l_inventory_item_id
AND serial_number = l_serial_number
AND current_organization_id = l_organization_id;
SELECT serial_number_control_code
INTO l_serial_control_at_from_org
FROM mtl_system_items
WHERE inventory_item_id = l_inventory_item_id
AND organization_id = l_from_organization_id;
-- delete if it is a newly created dynamic serial
DELETE mtl_serial_numbers
WHERE inventory_item_id = l_inventory_item_id
AND serial_number = l_serial_number
AND current_organization_id = l_organization_id
AND previous_status IS NULL;
UPDATE mtl_serial_numbers
SET current_status = Nvl(previous_status, current_status)
, group_mark_id = -1 -- This line and next line for Bug#2368323
, current_organization_id = Decode(previous_status, NULL,
Decode(l_serial_control_at_from_org,
1, current_organization_id,
6, current_organization_id,
Nvl (l_from_organization_id,current_organization_id)),
current_organization_id)
, previous_status = NULL
WHERE inventory_item_id = l_inventory_item_id
AND serial_number = l_serial_number
AND current_organization_id = l_organization_id;
print_debug('rcv_txn_clean_up 40: delete MO Lines RTI ',4);
DELETE mtl_txn_request_lines
WHERE line_id IN
(SELECT line_id
FROM rcv_transactions_interface rti
, mtl_txn_request_lines mol
WHERE rti.group_id = p_group_id
AND mol.txn_source_id = rti.interface_transaction_id
AND mol.organization_id = rti.to_organization_id
AND mol.inventory_item_id = rti.item_id);
SELECT 'ONLINE'
INTO l_txn_mode_code
FROM dual
WHERE exists (SELECT 1
FROM rcv_transactions_interface
WHERE interface_transaction_id = p_group_id
AND processing_mode_code = 'ONLINE');
FOR l_rti_rec IN (SELECT interface_transaction_id
, transaction_type
, mmtt_temp_id
, processing_mode_code
, parent_transaction_id
, item_id
, lpn_id
, item_revision
, item_description
, to_organization_id
FROM rcv_transactions_interface
WHERE interface_transaction_id =
p_group_id)
LOOP
IF (l_rti_rec.processing_mode_code <> 'ONLINE') THEN
IF l_rti_rec.mmtt_temp_id IS NOT NULL THEN
--Call Cleanup Op Instance
wms_atf_runtime_pub_apis.cleanup_operation_instance
(x_return_status => x_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
,x_error_code => l_error_code
,p_source_task_id => l_rti_rec.mmtt_temp_id
,p_activity_type_id => 1);
--update mol and msn
--In R12, the line between MOL.TXN_SOURCE_ID and RT.TRANSACTION_ID
--is removed. So when unmarking the wme_process_flag here,
--we cannot join on MOL.TXN_SOURCE_ID. Instead, we make
--use of rti.mmtt_temp_id if present. If not, we can only
--update all MOL for the given org/item combination
IF (l_rti_rec.mmtt_temp_id IS NOT NULL) THEN
UPDATE mtl_txn_request_lines
SET wms_process_flag = 1
WHERE line_id = (SELECT move_order_line_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = l_rti_rec.mmtt_temp_id);
UPDATE mtl_txn_request_lines
SET wms_process_flag = 1
WHERE organization_id = l_rti_rec.to_organization_id
AND lpn_id = l_rti_rec.lpn_id
AND wms_process_flag = 2;
UPDATE mtl_txn_request_lines
SET wms_process_flag = 1
WHERE organization_id = l_rti_rec.to_organization_id
AND inventory_item_id = l_rti_rec.item_id
AND Nvl(revision,'#$!') = Nvl(l_rti_rec.item_revision,'#$!')
AND wms_process_flag = 2;
print_debug('Number of MOL updated: '||SQL%rowcount,4);
update /*+ ROWID */ mtl_serial_numbers msn
set group_mark_id = NULL,
line_mark_id = NULL,
lot_line_mark_id = NULL
where msn.ROWID in ( select msn1.ROWID
from mtl_serial_numbers msn1 ,
mtl_serial_numbers_interface msni
where msn1.inventory_item_id = l_rti_rec.item_id
and msni.product_code = 'RCV'
and msni.product_transaction_id = l_rti_rec.interface_transaction_id
and msn1.serial_number between msni.fm_serial_number and msni.to_serial_number
and length(msn1.serial_number) = length(msni.fm_serial_number)
and length(msni.fm_serial_number) = length(nvl(msni.to_serial_number,msni.fm_serial_number)));
update /*+ ROWID */ mtl_serial_numbers msn
set group_mark_id = NULL,
line_mark_id = NULL,
lot_line_mark_id = NULL
where msn.ROWID in ( select msn1.ROWID
from mtl_serial_numbers msn1 ,
mtl_serial_numbers_temp msnt
where msn1.inventory_item_id = l_rti_rec.item_id
and msnt.product_code = 'RCV'
and msnt.product_transaction_id = l_rti_rec.interface_transaction_id
and msn1.serial_number between msnt.fm_serial_number and msnt.to_serial_number
and length(msn1.serial_number) = length(msnt.fm_serial_number)
and length(msnt.fm_serial_number) = length(nvl(msnt.to_serial_number,msnt.fm_serial_number)));
END LOOP; --FOR l_rti_rec IN (SELECT interface_transaction_id
FOR l_rti_rec IN (SELECT interface_transaction_id
, transaction_type
, mmtt_temp_id
, processing_mode_code
, parent_transaction_id
, item_id
, to_organization_id
, lpn_id
, item_description
, item_revision
FROM rcv_transactions_interface
WHERE lpn_group_id =
p_group_id)
LOOP
IF (l_rti_rec.processing_mode_code <> 'ONLINE') THEN
IF l_rti_rec.mmtt_temp_id IS NOT NULL THEN
--Call Cleanup Op Instance
wms_atf_runtime_pub_apis.cleanup_operation_instance
(x_return_status => x_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
,x_error_code => l_error_code
,p_source_task_id => l_rti_rec.mmtt_temp_id
,p_activity_type_id => 1);
--update mol and msn
--In R12, the line between MOL.TXN_SOURCE_ID and RT.TRANSACTION_ID
--is removed. So when unmarking the wme_process_flag here,
--we cannot join on MOL.TXN_SOURCE_ID. Instead, we make
--use of rti.mmtt_temp_id if present. If not, we can only
--update all MOL for the given org/item combination
IF (l_rti_rec.mmtt_temp_id IS NOT NULL) THEN
UPDATE mtl_txn_request_lines
SET wms_process_flag = 1
WHERE line_id = (SELECT move_order_line_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = l_rti_rec.mmtt_temp_id);
UPDATE mtl_txn_request_lines
SET wms_process_flag = 1
WHERE organization_id = l_rti_rec.to_organization_id
AND lpn_id = l_rti_rec.lpn_id
AND wms_process_flag = 2;
UPDATE mtl_txn_request_lines
SET wms_process_flag = 1
WHERE organization_id = l_rti_rec.to_organization_id
AND inventory_item_id = l_rti_rec.item_id
AND wms_process_flag = 2
AND Nvl(revision,'#$!') = Nvl(l_rti_rec.item_revision,'#$!');
print_debug('Number of MOL updated: '||SQL%rowcount,4);
update /*+ ROWID */ mtl_serial_numbers msn
set group_mark_id = NULL,
line_mark_id = NULL,
lot_line_mark_id = NULL
where msn.ROWID in ( select msn1.ROWID
from mtl_serial_numbers msn1 ,
mtl_serial_numbers_interface msni
where msn1.inventory_item_id = l_rti_rec.item_id
and msni.product_code = 'RCV'
and msni.product_transaction_id = l_rti_rec.interface_transaction_id
and msn1.serial_number between msni.fm_serial_number and msni.to_serial_number
and length(msn1.serial_number) = length(msni.fm_serial_number)
and length(msni.fm_serial_number) = length(nvl(msni.to_serial_number,msni.fm_serial_number)));
update /*+ ROWID */ mtl_serial_numbers msn
set group_mark_id = NULL,
line_mark_id = NULL,
lot_line_mark_id = NULL
where msn.ROWID in ( select msn1.ROWID
from mtl_serial_numbers msn1 ,
mtl_serial_numbers_temp msnt
where msn1.inventory_item_id = l_rti_rec.item_id
and msnt.product_code = 'RCV'
and msnt.product_transaction_id = l_rti_rec.interface_transaction_id
and msn1.serial_number between msnt.fm_serial_number and msnt.to_serial_number
and length(msn1.serial_number) = length(msnt.fm_serial_number)
and length(msnt.fm_serial_number) = length(nvl(msnt.to_serial_number,msnt.fm_serial_number)));
END LOOP; --FOR l_rti_rec IN (SELECT interface_transaction_id
FOR l_rti_rec IN (SELECT interface_transaction_id
, transaction_type
, mmtt_temp_id
, processing_mode_code
, parent_transaction_id
, item_id
, to_organization_id
, lpn_id
, item_description
, item_revision
FROM rcv_transactions_interface
WHERE header_interface_id = p_group_id)
LOOP
IF (l_rti_rec.processing_mode_code <> 'ONLINE') THEN
IF l_rti_rec.mmtt_temp_id IS NOT NULL THEN
--Call Cleanup Op Instance
wms_atf_runtime_pub_apis.cleanup_operation_instance
(x_return_status => x_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
,x_error_code => l_error_code
,p_source_task_id => l_rti_rec.mmtt_temp_id
,p_activity_type_id => 1);
--update mol and msn
--In R12, the line between MOL.TXN_SOURCE_ID and RT.TRANSACTION_ID
--is removed. So when unmarking the wme_process_flag here,
--we cannot join on MOL.TXN_SOURCE_ID. Instead, we make
--use of rti.mmtt_temp_id if present. If not, we can only
--update all MOL for the given org/item combination
IF (l_rti_rec.mmtt_temp_id IS NOT NULL) THEN
UPDATE mtl_txn_request_lines
SET wms_process_flag = 1
WHERE line_id = (SELECT move_order_line_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = l_rti_rec.mmtt_temp_id);
UPDATE mtl_txn_request_lines
SET wms_process_flag = 1
WHERE organization_id = l_rti_rec.to_organization_id
AND lpn_id = l_rti_rec.lpn_id
AND wms_process_flag = 2;
UPDATE mtl_txn_request_lines
SET wms_process_flag = 1
WHERE organization_id = l_rti_rec.to_organization_id
AND inventory_item_id = l_rti_rec.item_id
AND wms_process_flag = 2
AND Nvl(revision,'#$!') = Nvl(l_rti_rec.item_revision,'#$!');
print_debug('Number of MOL updated: '||SQL%rowcount,4);
update /*+ ROWID */ mtl_serial_numbers msn
set group_mark_id = NULL,
line_mark_id = NULL,
lot_line_mark_id = NULL
where msn.ROWID in ( select msn1.ROWID
from mtl_serial_numbers msn1 ,
mtl_serial_numbers_interface msni
where msn1.inventory_item_id = l_rti_rec.item_id
and msni.product_code = 'RCV'
and msni.product_transaction_id = l_rti_rec.interface_transaction_id
and msn1.serial_number between msni.fm_serial_number and msni.to_serial_number
and length(msn1.serial_number) = length(msni.fm_serial_number)
and length(msni.fm_serial_number) = length(nvl(msni.to_serial_number,msni.fm_serial_number)));
update /*+ ROWID */ mtl_serial_numbers msn
set group_mark_id = NULL,
line_mark_id = NULL,
lot_line_mark_id = NULL
where msn.ROWID in ( select msn1.ROWID
from mtl_serial_numbers msn1 ,
mtl_serial_numbers_temp msnt
where msn1.inventory_item_id = l_rti_rec.item_id
and msnt.product_code = 'RCV'
and msnt.product_transaction_id = l_rti_rec.interface_transaction_id
and msn1.serial_number between msnt.fm_serial_number and msnt.to_serial_number
and length(msn1.serial_number) = length(msnt.fm_serial_number)
and length(msnt.fm_serial_number) = length(nvl(msnt.to_serial_number,msnt.fm_serial_number)));
END LOOP; --FOR l_rti_rec IN (SELECT interface_transaction_id
FOR l_rti_rec IN (SELECT interface_transaction_id
, transaction_type
, mmtt_temp_id
, processing_mode_code
, parent_transaction_id
, item_id
, to_organization_id
, lpn_group_id
, lpn_id
, item_description
, item_revision
FROM rcv_transactions_interface
WHERE group_id =
p_group_id)
LOOP
IF (l_rti_rec.processing_mode_code <> 'ONLINE') THEN
IF l_rti_rec.mmtt_temp_id IS NOT NULL THEN
--Call Cleanup Op Instance
wms_atf_runtime_pub_apis.cleanup_operation_instance
(x_return_status => x_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
,x_error_code => l_error_code
,p_source_task_id => l_rti_rec.mmtt_temp_id
,p_activity_type_id => 1);
--update mol and msn
--In R12, the line between MOL.TXN_SOURCE_ID and RT.TRANSACTION_ID
--is removed. So when unmarking the wme_process_flag here,
--we cannot join on MOL.TXN_SOURCE_ID. Instead, we make
--use of rti.mmtt_temp_id if present. If not, we can only
--update all MOL for the given org/item combination
IF (l_rti_rec.mmtt_temp_id IS NOT NULL) THEN
UPDATE mtl_txn_request_lines
SET wms_process_flag = 1
WHERE line_id = (SELECT move_order_line_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = l_rti_rec.mmtt_temp_id);
UPDATE mtl_txn_request_lines
SET wms_process_flag = 1
WHERE organization_id = l_rti_rec.to_organization_id
AND lpn_id = l_rti_rec.lpn_id
AND wms_process_flag = 2;
UPDATE mtl_txn_request_lines
SET wms_process_flag = 1
WHERE organization_id = l_rti_rec.to_organization_id
AND inventory_item_id = l_rti_rec.item_id
AND wms_process_flag = 2
AND Nvl(revision,'#$!') = Nvl(l_rti_rec.item_revision,'#$!');
print_debug('Number of MOL updated: '||SQL%rowcount,4);
update /*+ ROWID */ mtl_serial_numbers msn
set group_mark_id = NULL,
line_mark_id = NULL,
lot_line_mark_id = NULL
where msn.ROWID in ( select msn1.ROWID
from mtl_serial_numbers msn1 ,
mtl_serial_numbers_interface msni
where msn1.inventory_item_id = l_rti_rec.item_id
and msni.product_code = 'RCV'
and msni.product_transaction_id = l_rti_rec.interface_transaction_id
and msn1.serial_number between msni.fm_serial_number and msni.to_serial_number
and length(msn1.serial_number) = length(msni.fm_serial_number)
and length(msni.fm_serial_number) = length(nvl(msni.to_serial_number,msni.fm_serial_number)));
update /*+ ROWID */ mtl_serial_numbers msn
set group_mark_id = NULL,
line_mark_id = NULL,
lot_line_mark_id = NULL
where msn.ROWID in ( select msn1.ROWID
from mtl_serial_numbers msn1 ,
mtl_serial_numbers_temp msnt
where msn1.inventory_item_id = l_rti_rec.item_id
and msnt.product_code = 'RCV'
and msnt.product_transaction_id = l_rti_rec.interface_transaction_id
and msn1.serial_number between msnt.fm_serial_number and msnt.to_serial_number
and length(msn1.serial_number) = length(msnt.fm_serial_number)
and length(msnt.fm_serial_number) = length(nvl(msnt.to_serial_number,msnt.fm_serial_number)));
DELETE FROM mtl_transaction_lots_interface
WHERE product_code = 'RCV'
AND product_transaction_id = l_rti_rec.interface_transaction_id;
DELETE FROM mtl_transaction_lots_temp
WHERE product_code = 'RCV'
AND product_transaction_id = l_rti_rec.interface_transaction_id;
DELETE FROM mtl_serial_numbers_interface
WHERE product_code = 'RCV'
AND product_transaction_id = l_rti_rec.interface_transaction_id;
DELETE FROM mtl_serial_numbers_temp
WHERE product_code = 'RCV'
AND product_transaction_id = l_rti_rec.interface_transaction_id;
DELETE FROM wms_lpn_interface
WHERE source_group_id = l_rti_rec.lpn_group_id;
END LOOP; --FOR l_rti_rec IN (SELECT interface_transaction_id
SELECT transaction_type
INTO l_transaction_type
FROM rcv_transactions
WHERE lpn_group_id = p_group_id
AND transaction_date >= (Sysdate - 1) --BUG 3444137: RT
--will have INDEX ON transaction_date AND lpn_group_id
AND transaction_type IN ('CORRECT','RETURN TO VENDOR',
'RETURN TO RECEIVING','RETURN TO CUSTOMER')
AND ROWNUM < 2;
SELECT transaction_type
INTO l_transaction_type
FROM rcv_transactions
WHERE group_id = p_group_id
AND transaction_type IN ('CORRECT','RETURN TO VENDOR',
'RETURN TO RECEIVING','RETURN TO CUSTOMER')
AND ROWNUM < 2;
print_debug('Delete records from WLPN with context 6, operation_mode of -99999 and group_id', 4);
DELETE FROM wms_lpn_histories
WHERE source_header_id = p_group_id
AND lpn_context = 6
AND operation_mode = -99999;
SELECT DISTINCT rti.mmtt_temp_id
FROM rcv_transactions_interface rti
WHERE rti.group_id = p_group_id;
SELECT count(Nvl(rti.mobile_txn, 'N'))
INTO l_mobile_txn_count
FROM rcv_transactions_interface rti
WHERE rti.group_id = p_group_id
AND processing_mode_code = 'ONLINE'
AND processing_status_code = 'PENDING'
AND transaction_status_code = 'PENDING';
UPDATE wms_lpn_contents
SET txn_error_flag = 'Y'
WHERE source_header_id = p_group_id;
UPDATE mtl_serial_numbers
SET lpn_txn_error_flag = 'Y'
WHERE ROWID IN (SELECT msn.ROWID
FROM mtl_serial_numbers msn
, rcv_transactions_interface rti
WHERE msn.last_txn_source_id = p_group_id
AND rti.group_id = p_group_id
AND rti.item_id = msn.inventory_item_id);
-- Delete/Clear mtl_serial_numbers_temp rows
-- Delete/Clear mtl_transaction_lots_temp rows
-- If the Transaction Fails
IF (l_debug = 1) THEN
print_debug('TXN_MOBILE_TIMEOUT_CLEANUP - cleanup msnt 1',4);
delete from mtl_serial_numbers_temp msnt
where msnt.transaction_temp_id in
( select interface_transaction_id
from rcv_transactions_interface
where group_id = p_group_id )
;
delete from mtl_serial_numbers_temp msnt
where msnt.transaction_temp_id in
( select mtlt.serial_transaction_temp_id
from mtl_transaction_lots_temp mtlt
where mtlt.transaction_temp_id in (
select interface_transaction_id
from rcv_transactions_interface
where group_id = p_group_id )
);
delete from mtl_transaction_lots_temp mtlt
where mtlt.transaction_temp_id
in ( select interface_transaction_id
from rcv_transactions_interface
where group_id = p_group_id );