[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Update statement called in update_rti_error
UPDATE rcv_transactions_interface
SET processing_status_code = 'ERROR',
processing_request_id = g_request_id
WHERE header_interface_id = p_rti_row.header_interface_id
AND processing_status_code IN('PENDING', 'RUNNING')
AND transaction_status_code = 'PENDING';
RCV_ROI_PREPROCESSOR.update_rti_error(p_group_id => p_rti_row.group_id,
p_interface_id => NULL,
p_header_interface_id => p_rti_row.header_interface_id,
p_lpn_group_id => NULL
);
RCV_ROI_PREPROCESSOR.update_rti_error(p_group_id => p_rti_row.group_id,
p_interface_id => NULL,
p_header_interface_id => NULL,
p_lpn_group_id => p_rti_row.lpn_group_id
);
RCV_ROI_PREPROCESSOR.update_rti_error(p_group_id => p_rti_row.group_id,
p_interface_id => p_rti_row.interface_transaction_id,
p_header_interface_id => NULL,
p_lpn_group_id => NULL
);
rcv_table_functions.update_rti_row(p_rti_row);
SELECT count(*)
INTO x_rti_count
FROM rcv_transactions_interface
WHERE header_interface_id = p_rhi_row.header_interface_id
AND processing_status_code in ('PENDING','RUNNING')
AND transaction_status_code = 'PENDING';
rcv_table_functions.update_rhi_row(p_rhi_row);
BEGIN --update block
BEGIN --exception handling block
rcv_default_pkg.default_header(p_rhi_row);
UPDATE rcv_transactions_interface
SET processing_status_code = 'ERROR',
processing_request_id = g_request_id
WHERE header_interface_id = p_rhi_row.header_interface_id
AND processing_status_code IN('PENDING', 'RUNNING')
AND transaction_status_code = 'PENDING';
rcv_table_functions.update_rhi_row(p_rhi_row);
BEGIN --this is the update block
BEGIN --this is the exception catching block
IF (p_rti_row.GROUP_ID IS NULL) THEN
p_rti_row.GROUP_ID := g_group_id;
rcv_table_functions.update_rti_row(p_rti_row);
rcv_table_functions.update_rti_row(p_rti_row);
select transaction_id,
transaction_type
into l_parent_transaction_id,
l_parent_transaction_type
from rcv_transactions
where transaction_type = 'UNORDERED'
connect by prior
parent_transaction_id = transaction_id
start with transaction_id = p_rti_row.parent_transaction_id;
select count(1)
into l_match_count
from rcv_transactions
where transaction_type = 'MATCH'
and parent_transaction_id = l_parent_transaction_id;
rcv_table_functions.update_rhi_row(x_rhi_row);
select count(1)
into l_lpn_group_rti_count
from rcv_transactions_interface rti
where rti.group_id = p_rti_row.group_id
and rti.lpn_group_id is not null
and rti.lpn_group_id = p_rti_row.lpn_group_id
and rti.interface_transaction_id <> p_rti_row.interface_transaction_id -- Bug 8343139
and (rti.transaction_type = 'RECEIVE'
or (rti.transaction_type = 'SHIP'
and rti.auto_transact_code in ('RECEIVE','DELIVER'))) -- Bug 8343139
and rti.source_document_code = 'PO'
and exists (select 'LCM Shipment' from po_line_locations_all pll
where pll.line_location_id = rti.po_line_location_id
and lcm_flag = 'Y')
and (rti.lcm_shipment_line_id is null or rti.unit_landed_cost is null);
select count(1)
into l_lpn_group_rti_count
from rcv_transactions_interface rti
where rti.group_id = x_rhi_row.group_id
and rti.header_interface_id = x_rhi_row.header_interface_id
and rti.interface_transaction_id <> p_rti_row.interface_transaction_id -- Bug 8343139
and (rti.transaction_type = 'RECEIVE'
or (rti.transaction_type = 'SHIP'
and rti.auto_transact_code in ('RECEIVE','DELIVER'))) -- Bug 8343139
and rti.source_document_code = 'PO'
and exists (select 'LCM Shipment' from po_line_locations_all pll
where pll.line_location_id = rti.po_line_location_id
and lcm_flag = 'Y')
and (rti.lcm_shipment_line_id is null or rti.unit_landed_cost is null);
rcv_table_functions.update_rhi_row(x_rhi_row);
rcv_table_functions.update_rhi_row(x_rhi_row);
rcv_table_functions.update_rhi_row(x_rhi_row);
rcv_table_functions.update_rti_row(p_rti_row);
UPDATE rcv_transactions_interface
SET processing_status_code = 'RUNNING',
processing_request_id = g_request_id
WHERE processing_status_code = 'PENDING'
AND ( processing_request_id = g_request_id
OR processing_request_id IS NULL)
AND ( mo_global.check_access(org_id) = 'Y'
OR org_id IS NULL)
AND ( lpn_group_id IS NOT NULL
OR lpn_id IS NOT NULL
OR license_plate_number IS NOT NULL
OR interface_transaction_id IN (SELECT interface_transaction_id
FROM wms_lpn_contents_interface));
UPDATE rcv_transactions_interface
SET processing_status_code = 'RUNNING',
processing_request_id = g_request_id
WHERE processing_status_code = 'PENDING'
AND ( processing_request_id = g_request_id
OR processing_request_id IS NULL)
AND org_id = g_org_id
AND ( lpn_group_id IS NOT NULL
OR lpn_id IS NOT NULL
OR license_plate_number IS NOT NULL
OR interface_transaction_id IN (SELECT interface_transaction_id
FROM wms_lpn_contents_interface));
UPDATE rcv_transactions_interface
SET processing_status_code = 'PENDING'
WHERE processing_status_code = 'RUNNING'
AND processing_request_id = g_request_id;
UPDATE rcv_transactions_interface
SET processing_status_code = 'PENDING'
WHERE processing_status_code = 'RUNNING'
AND processing_request_id = g_request_id
AND org_id = g_org_id;
UPDATE rcv_transactions_interface
SET processing_status_code = 'RUNNING',
processing_request_id = g_request_id,
group_id = g_group_id
WHERE processing_status_code = 'PENDING'
AND ( processing_request_id = g_request_id
OR processing_request_id IS NULL)
AND group_id = g_group_id
AND ( lpn_group_id IS NOT NULL
OR lpn_id IS NOT NULL
OR license_plate_number IS NOT NULL
OR interface_transaction_id IN (SELECT interface_transaction_id
FROM wms_lpn_contents_interface));
UPDATE rcv_transactions_interface
SET processing_status_code = 'RUNNING',
processing_request_id = g_request_id,
group_id = g_group_id
WHERE processing_status_code = 'PENDING'
AND ( processing_request_id = g_request_id
OR processing_request_id IS NULL)
AND group_id = g_group_id
AND org_id = g_org_id
AND ( lpn_group_id IS NOT NULL
OR lpn_id IS NOT NULL
OR license_plate_number IS NOT NULL
OR interface_transaction_id IN (SELECT interface_transaction_id
FROM wms_lpn_contents_interface));
UPDATE rcv_transactions_interface
SET processing_status_code = 'PENDING'
WHERE processing_status_code = 'RUNNING'
AND processing_request_id = g_request_id
AND group_id = g_group_id;
UPDATE rcv_transactions_interface
SET processing_status_code = 'PENDING'
WHERE processing_status_code = 'RUNNING'
AND processing_request_id = g_request_id
AND group_id = g_group_id
AND org_id = g_org_id;
SELECT rcv_interface_groups_s.NEXTVAL
FROM DUAL;
SELECT *
FROM rcv_headers_interface
WHERE processing_status_code = 'PENDING'
AND ( mo_global.check_access(org_id) = 'Y'
OR org_id IS NULL)
ORDER BY group_id, header_interface_id; -- added by bug 16393104 to avoid deadlock
SELECT *
FROM rcv_headers_interface
WHERE processing_status_code = 'PENDING'
AND GROUP_ID = g_group_id
ORDER BY header_interface_id; -- added by bug 16393104 to avoid deadlock
SELECT * FROM ( --Bug 12594135
SELECT *
FROM rcv_transactions_interface
WHERE processing_status_code = 'PENDING'
AND processing_mode_code = g_processing_mode -- Bug 6311798
AND ( mo_global.check_access(org_id) = 'Y'
OR org_id IS NULL)
) rcv
CONNECT BY PRIOR rcv.interface_transaction_id = rcv.parent_interface_txn_id
START WITH rcv.parent_interface_txn_id IS NULL --Reverted the initial fix of 12718851 and keeping the original code
ORDER BY group_id, interface_transaction_id; -- added by bug 16393104 to avoid deadlock
SELECT * FROM ( --Bug 12594135
SELECT *
FROM rcv_transactions_interface
WHERE processing_status_code = 'PENDING'
AND processing_mode_code = g_processing_mode -- Bug 6311798
AND GROUP_ID = g_group_id
) rcv
CONNECT BY PRIOR rcv.interface_transaction_id = rcv.parent_interface_txn_id
START WITH rcv.parent_interface_txn_id IS NULL --Reverted the initial fix of 12718851 and keeping the original code
ORDER BY interface_transaction_id; -- added by bug 16393104 to avoid deadlock
SELECT *
FROM rcv_transactions_interface rti
WHERE rti.processing_status_code = 'PENDING'
AND rti.processing_mode_code = g_processing_mode -- Bug 6311798
AND ( mo_global.check_access(rti.org_id) = 'Y'
OR rti.org_id IS NULL)
CONNECT BY PRIOR rti.interface_transaction_id = parent_interface_txn_id
START WITH rti.interface_transaction_id IN (SELECT rti1.interface_transaction_id --Bug:5473673
FROM rcv_transactions_interface rti1
WHERE rti1.parent_interface_txn_id IS NOT NULL -- Bug 8745599
AND rti1.PROCESSING_MODE_CODE = g_processing_mode --Bug 12594135
AND rti1. parent_interface_txn_id NOT IN(SELECT rti2.interface_transaction_id
FROM rcv_transactions_interface rti2))
ORDER BY rti.group_id, rti.interface_transaction_id; -- added by bug 16393104 to avoid deadlock
SELECT *
FROM rcv_transactions_interface rti
WHERE rti.processing_status_code = 'PENDING'
AND rti.processing_mode_code = g_processing_mode -- Bug 6311798
AND rti.GROUP_ID = g_group_id
CONNECT BY PRIOR rti.interface_transaction_id = rti.parent_interface_txn_id
START WITH rti.interface_transaction_id IN (SELECT rti1.interface_transaction_id --Bug: 5473673
FROM rcv_transactions_interface rti1
WHERE rti1.parent_interface_txn_id IS NOT NULL -- Bug 8745599
AND rti1.PROCESSING_MODE_CODE = g_processing_mode --Bug 12594135
AND rti1.GROUP_ID = g_group_id --Bug 12594135
AND rti1.parent_interface_txn_id NOT IN(SELECT rti2.interface_transaction_id
FROM rcv_transactions_interface rti2))
ORDER BY rti.interface_transaction_id; -- added by bug 16393104 to avoid deadlock
SELECT *
FROM rcv_headers_interface
WHERE processing_status_code = 'PENDING'
AND org_id = p_org_id
ORDER BY group_id,header_interface_id; -- added by bug 16393104 to avoid deadlock
SELECT * FROM (
SELECT *
FROM rcv_transactions_interface
WHERE processing_status_code = 'PENDING'
AND processing_mode_code = g_processing_mode
AND org_id = p_org_id
) rcv
CONNECT BY PRIOR rcv.interface_transaction_id = rcv.parent_interface_txn_id
START WITH rcv.parent_interface_txn_id IS NULL
ORDER BY group_id, interface_transaction_id; -- added by bug 16393104 to avoid deadlock
SELECT *
FROM rcv_transactions_interface rti
WHERE rti.processing_status_code = 'PENDING'
AND rti.processing_mode_code = g_processing_mode
AND rti.org_id = p_org_id
CONNECT BY PRIOR rti.interface_transaction_id = rti.parent_interface_txn_id
START WITH rti.interface_transaction_id IN (SELECT rti1.interface_transaction_id
FROM rcv_transactions_interface rti1
WHERE rti1.parent_interface_txn_id IS NOT NULL
AND rti1.processing_mode_code = g_processing_mode
AND rti1.org_id = p_org_id
AND rti1.parent_interface_txn_id NOT IN (SELECT rti2.interface_transaction_id
FROM rcv_transactions_interface rti2))
ORDER BY rti.group_id, rti.interface_transaction_id; -- added by bug 16393104 to avoid deadlock
SELECT *
FROM rcv_headers_interface
WHERE processing_status_code = 'PENDING'
AND group_id = g_group_id
AND org_id = p_org_id
ORDER BY header_interface_id; -- added by bug 16393104 to avoid deadlock
SELECT * FROM (
SELECT *
FROM rcv_transactions_interface
WHERE processing_status_code = 'PENDING'
AND processing_mode_code = g_processing_mode
AND group_id = g_group_id
AND org_id = p_org_id
) rcv
CONNECT BY PRIOR rcv.interface_transaction_id = rcv.parent_interface_txn_id
START WITH rcv.parent_interface_txn_id IS NULL
ORDER BY interface_transaction_id; -- added by bug 16393104 to avoid deadlock
SELECT *
FROM rcv_transactions_interface rti
WHERE rti.processing_status_code = 'PENDING'
AND rti.processing_mode_code = g_processing_mode
AND rti.org_id = p_org_id
CONNECT BY PRIOR rti.interface_transaction_id = rti.parent_interface_txn_id
START WITH rti.interface_transaction_id IN (SELECT rti1.interface_transaction_id
FROM rcv_transactions_interface rti1
WHERE rti1.parent_interface_txn_id IS NOT NULL
AND rti1.processing_mode_code = g_processing_mode
AND rti1.group_id = g_group_id
AND rti1.org_id = p_org_id
AND rti1.parent_interface_txn_id NOT IN (SELECT rti2.interface_transaction_id
FROM rcv_transactions_interface rti2))
ORDER BY rti.interface_transaction_id; -- added by bug 16393104 to avoid deadlock
SELECT name
INTO g_ou_name
FROM hr_organization_units
WHERE organization_id = g_org_id;
UPDATE rcv_headers_interface
SET org_id = p_org_id
WHERE processing_status_code = 'PENDING'
AND org_id IS NULL
AND operating_unit = g_ou_name;
UPDATE rcv_transactions_interface
SET org_id = p_org_id
WHERE processing_status_code = 'PENDING'
AND validation_flag = 'Y'
AND org_id IS NULL
AND operating_unit = g_ou_name;
SELECT *
INTO x_rhi_row1
FROM rcv_headers_interface WHERE header_interface_id = x_rhi_row.header_interface_id
FOR UPDATE NOWAIT;
SELECT *
INTO x_rti_row1
FROM rcv_transactions_interface WHERE interface_transaction_id = x_rti_row.interface_transaction_id
FOR UPDATE NOWAIT;
SELECT *
INTO x_rti_row1
FROM rcv_transactions_interface WHERE interface_transaction_id = x_rti_row.interface_transaction_id
FOR UPDATE NOWAIT;
UPDATE rcv_headers_interface
SET org_id = p_org_id
WHERE processing_status_code = 'PENDING'
AND group_id = g_group_id
AND org_id IS NULL
AND operating_unit = g_ou_name;
UPDATE rcv_transactions_interface
SET org_id = p_org_id
WHERE processing_status_code = 'PENDING'
AND group_id = g_group_id
AND validation_flag = 'Y'
AND org_id IS NULL
AND operating_unit = g_ou_name;
SELECT *
INTO x_rhi_row1
FROM rcv_headers_interface WHERE header_interface_id = x_rhi_row.header_interface_id
FOR UPDATE NOWAIT;
SELECT *
INTO x_rti_row1
FROM rcv_transactions_interface WHERE interface_transaction_id = x_rti_row.interface_transaction_id
FOR UPDATE NOWAIT;
SELECT *
INTO x_rti_row1
FROM rcv_transactions_interface WHERE interface_transaction_id = x_rti_row.interface_transaction_id
FOR UPDATE NOWAIT;
SELECT *
INTO x_rhi_row1
FROM rcv_headers_interface WHERE header_interface_id = x_rhi_row.header_interface_id
FOR UPDATE NOWAIT;
SELECT *
INTO x_rti_row1
FROM rcv_transactions_interface WHERE interface_transaction_id = x_rti_row.interface_transaction_id
FOR UPDATE NOWAIT;
SELECT *
INTO x_rti_row1
FROM rcv_transactions_interface WHERE interface_transaction_id = x_rti_row.interface_transaction_id
FOR UPDATE NOWAIT;
SELECT *
INTO x_rhi_row1
FROM rcv_headers_interface WHERE header_interface_id = x_rhi_row.header_interface_id
FOR UPDATE NOWAIT;
SELECT *
INTO x_rti_row1
FROM rcv_transactions_interface WHERE interface_transaction_id = x_rti_row.interface_transaction_id
FOR UPDATE NOWAIT;
SELECT *
INTO x_rti_row1
FROM rcv_transactions_interface WHERE interface_transaction_id = x_rti_row.interface_transaction_id
FOR UPDATE NOWAIT;