Tuesday, June 26, 2012

Interface error tables in Oracle Apps


Following are the interface error tables used in oracle apps:

This table stores the processing messages which will be generated during Order Entry concurrent program execution or UI process. This is a multi-lingual table.

SELECT OPT.*
  FROM OE_PROCESSING_MSGS OPM, OE_PROCESSING_MSGS_TL OPT
 WHERE     OPM.TRANSACTION_ID = OPT.TRANSACTION_ID
       AND OPT.LANGUAGE = 'US'
       AND ORIGINAL_SYS_DOCUMENT_REF = LV_DOC_REF


PO_INTERFACE_ERRORS contains error message information generated by the open interface programs in Oracle Purchasing. For every interface transaction row with errors, this table contains a row for each column that has an error.

SELECT *
  FROM PO_INTERFACE_ERRORS
 WHERE INTERFACE_LINE_ID = LN_INT_TRX_ID -- interface transaction id


MTL_TRANSACTIONS_INTERFACE allows calling applications to post material transactions (movements, issues, receipts etc. to Oracle Inventory transaction module.

The Transaction Manager picks up the rows to process based on the LOCK_FLAG, TRANSACTION_MODE, PROCESS_FLAG. Only records with TRANSACTION_MODE of '3', LOCK_FLAG of '2', and PROCESS_FLAG of '1' will be picked up by the Transaction Manager and assigned to a Transaction Worker. If a record fails to process completely, then PROCESS_FLAG will be set to '3' and ERROR_CODE and ERROR_EXPLANATION will be populated with the cause for the error.

SELECT ERROR_CODE,
            ERROR_EXPLANATION
  FROM MTL_TRANSACTIONS_INTERFACE
 WHERE TRANSACTION_REFERENCE = LN_TRX_REF


Interface data that failed in AutoInvoice validation are populated with the error details

SELECT *
  FROM RA_INTERFACE_ERRORS_ALL
 WHERE INTERFACE_LINE_ID = LN_INTF_ID


The HZ_PARTY_INTERFACE_ERRORS table contains the error messages that are generated while mapping data from the HZ_PARTY_INTERFACE table into other HZ tables.

SELECT *
  FROM HZ_PARTY_INTERFACE_ERRORS
 WHERE PARTY_INTERFACE_ID = LN_PARTY_INTEFACE_ID


MTL_INTERFACE_ERRORS stores errors that occur during the item interface process reporting where the errors occurred along with the error messages.

SELECT *
  FROM MTL_INTERFACE_ERRORS
 WHERE TRANSACTION_ID = LN_TRX_ID --Row identifier for row in MTL_SYSTEM_ITEMS_INTERFACE

  
WIP_INTERFACE_ERRORS stores the text of errors or warnings issued when loading information through the WIP Scheduling and Open Job and Schedule interfaces. It also stores information about the load requests that generate the errors. When the WIP Mass Load program loads records from the WIP Scheduling Interface, you can print a report showing these errors.

SELECT *
  FROM WIP_INTERFACE_ERRORS
 WHERE INTERFACE_ID = LN_INTF_ID


AP_INTERFACE_REJECTIONS stores information about invoice data from the AP_INVOICES_INTERFACE and AP_INVOICE_LINES_INTERFACE tables which could not be processed by Payables Open Interface Import.

SELECT * FROM AP_INTERFACE_REJECTIONS