[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_interface_errors ( rcv_trx IN OUT NOCOPY rcv_transactions_interface%ROWTYPE,
X_column_name IN VARCHAR2,
X_err_message IN VARCHAR2);
SELECT *
FROM rcv_transactions_interface
WHERE group_id = X_group_id
AND transaction_status_code in ( 'EXPRESS', 'CONFIRM' )
ORDER BY interface_transaction_id;
** The get interface rows cursor is used to select all the rows that
** were inserted during the express transaction from the form. We will
** will loop through each of these rows to ensure that the row can
** be transacted. For now the only feedback that the user will receive
** is how many rows passed validation and how many failed. We should
** add some kind of notification to this so the user can see which records
** had problems.
**
** This function assumes that if you are performing an
** express direct receipt or an express delivery that you will insert all
** the distributions into the transaction_interface table.
**
** The process is read a record into memory, set defaults in memory,
** validate trx, if transaction passes validation then write it out
** to database with all the defaults and updated values, otherwise
** delete it from the interface.
*/
/* Modified this procedure to validate input from the Receive Orders
** Web Page.
*/
BEGIN
X_progress := '010';
** Select the rows from the rcv_transactions_interface table
** that will be transacted with this group_id
*/
X_progress := '020';
select nvl(matching_basis,'QUANTITY')
into l_matching_basis
from po_line_locations
where line_location_id =rcv_trx.po_line_location_id;
SELECT GSB.currency_code,FSP.set_of_books_id
INTO x_base_currency_code,
v_sobid
FROM FINANCIALS_SYSTEM_PARAMETERS FSP,
GL_SETS_OF_BOOKS GSB
WHERE FSP.set_of_books_id = GSB.set_of_books_id
AND FSP.org_id = rcv_trx.org_id;
select match_option
into x_match_option
from po_line_locations
where line_location_id = rcv_trx.po_line_location_id;
SELECT currency_conversion_date,
currency_conversion_rate
INTO v_ratedate,
v_rate
FROM rcv_transactions
WHERE transaction_id = rcv_trx.parent_transaction_id;
rcv_trx_interface_trx_upd_pkg.update_rcv_transaction (rcv_trx);
SELECT column_name, error_message_name
INTO x_column_name, x_message_name
FROM po_interface_errors po
WHERE po.interface_line_id = rcv_trx.interface_transaction_id; --Bug 5230922
** Bug 3438171 - Don't delete the transaction, update it.
** If the transactions fails validation then update it
** in the interface to error
*/
rcv_trx.processing_status_code := 'COMPLETED';
rcv_trx_interface_trx_upd_pkg.update_rcv_transaction (rcv_trx);
DELETE FROM rcv_transactions_interface
WHERE group_id = X_group_id
AND quantity = 0;
inserted into po_interface errors.
*/
/* Bug: 5855096
We neeed to by pass the validation for allow_express_delivery flag
mentioned at item level for the transactions made through the
web page(iProcurement).
For the transactions made through iProcurement possible
transaction_types are 'CONFIRM RECEIPT' and 'CONFIRM RECEIPT(WF)'
and the variables X_txn_from_web or X_txn_from_wf set to TRUE.
Changed nvl(msi.allow_express_delivery_flag,'N') to
nvl(msi.allow_express_delivery_flag,'U'), to bypass the
allow_express_delivery flag validation if that flag value
is to NULL in the Master Items form.
*/
if ( not(X_txn_from_web or X_txn_from_wf) ) then--Bug 5855096
if (nvl(rcv_trx.item_id,0) <> 0) then
select nvl(msi.allow_express_delivery_flag,'U') --Bug 5855096
into x_allow_express_delivery_flag
from mtl_system_items msi
where msi.inventory_item_id =rcv_trx.item_id
and msi.organization_id = rcv_trx.to_organization_id;
rcv_express_sv.insert_interface_errors(rcv_trx,
X_column_name,
X_err_message);
inserted into po_interface errors.
*/
if ( (rcv_trx.destination_type_code = 'INVENTORY' or
(rcv_trx.source_document_code = 'RMA' and
(rcv_trx.transaction_type = 'DELIVER' or rcv_trx.auto_transact_code = 'DELIVER'))) and
nvl(rcv_trx.item_id,0) <> 0) then
select msi.segment1,
msi.stock_enabled_flag
into x_item_name,
x_stock_enabled_flag
from mtl_system_items msi
where msi.inventory_item_id = rcv_trx.item_id
and msi.organization_id = rcv_trx.to_organization_id;
rcv_express_sv.insert_interface_errors(rcv_trx,
X_column_name,
X_err_message);
rcv_express_sv.insert_interface_errors(rcv_trx,
X_column_name,
X_err_message);
select GSOB.SET_OF_BOOKS_ID
into x_sob_id
from
GL_SETS_OF_BOOKS GSOB,
HR_ORGANIZATION_INFORMATION HOI
where
HOI.ORGANIZATION_ID = rcv_trx.to_organization_id
AND ( HOI.ORG_INFORMATION_CONTEXT || '') ='Accounting Information'
AND HOI.ORG_INFORMATION1 = TO_CHAR(GSOB.SET_OF_BOOKS_ID);
rcv_express_sv.insert_interface_errors(rcv_trx,
X_column_name,
X_err_message);
rcv_express_sv.insert_interface_errors(rcv_trx,
X_column_name,
X_err_message);
rcv_express_sv.insert_interface_errors(rcv_trx,
X_column_name,
X_err_message);
** Check for 0 transaction quantity. If it's 0 then fail and delete it.
*/
/* Bug 4891693 fixed.
No need for validation of zero quantity RTI records since we
are deleting them down the line.
IF (rcv_trx.quantity = 0) THEN
X_column_name := 'QUANTITY';
rcv_express_sv.insert_interface_errors(rcv_trx,
X_column_name,
X_err_message);
rcv_express_sv.insert_interface_errors(rcv_trx,
X_column_name,
X_err_message);
rcv_express_sv.insert_interface_errors(rcv_trx,
X_column_name,
X_err_message);
rcv_express_sv.insert_interface_errors(rcv_trx,
X_column_name,
X_err_message);
SELECT msi.revision_qty_control_code
INTO l_item_rev_control
FROM mtl_system_items_kfv msi
WHERE msi.inventory_item_id = rcv_trx.item_id
AND msi.organization_id = rcv_trx.to_organization_id;
SELECT count(*)
INTO l_valid_revision
FROM mtl_item_revisions
WHERE inventory_item_id = rcv_trx.item_id
AND organization_id = rcv_trx.to_organization_id
AND revision = rcv_trx.item_revision;
rcv_express_sv.insert_interface_errors(rcv_trx,
X_column_name,
X_err_message);
rcv_express_sv.insert_interface_errors(rcv_trx,
X_column_name,
X_err_message);
rcv_express_sv.insert_interface_errors(rcv_trx,
X_column_name,
X_err_message);
rcv_express_sv.insert_interface_errors(rcv_trx,
X_column_name,
X_err_message);
rcv_express_sv.insert_interface_errors(rcv_trx,
X_column_name,
X_err_message);
rcv_express_sv.insert_interface_errors(rcv_trx,
X_column_name,
X_err_message);
rcv_express_sv.insert_interface_errors(rcv_trx,
X_column_name,
X_err_message);
rcv_express_sv.insert_interface_errors(rcv_trx,
X_column_name,
X_err_message);
SELECT msi.revision_qty_control_code
INTO X_item_rev_control
FROM mtl_system_items_kfv msi
WHERE rcv_trx.item_id = msi.inventory_item_id
AND rcv_trx.to_organization_id = msi.organization_id;
If the following select returns one then this is the last distribution
and the excess quantity should be allocated against this distribution.
*/
select count(po_distribution_id)
into x_dist_count
from rcv_transactions_interface
where transaction_type = 'EXPRESS DIRECT'
and po_line_location_id = rcv_trx.po_line_location_id
and shipment_line_id = rcv_trx.shipment_line_id;
select uom_code
into x_uom_code
from mtl_units_of_measure
where unit_of_measure = rcv_trx.unit_of_measure;
SELECT project_id, task_id
INTO x_project_id, x_task_id
FROM po_distributions
WHERE po_distribution_id = rcv_trx.po_distribution_id;
SELECT project_id, task_id
INTO x_project_id,x_task_id
FROM po_req_distributions
WHERE requisition_line_id = rcv_trx.requisition_line_id;
PROCEDURE NAME: Insert_Interface_Errors
===========================================================================*/
/*
** Insert into PO_INTERFACE_ERRORS table
*/
PROCEDURE insert_interface_errors ( rcv_trx IN OUT NOCOPY rcv_transactions_interface%ROWTYPE,
X_column_name IN VARCHAR2,
X_err_message IN VARCHAR2) as
X_progress VARCHAR2(3) := '000';
in PO_INTERFACE_ERRORS table and removed the Insert
statements to insert into PO_INTERFACE_ERRORS
table, as the fields error_message, interface_line_id
and interface_header_id are not populated. */
RCV_ERROR_PKG.set_error_message(X_err_message);
end insert_interface_errors;