Queries For Oracle Interface Errors Records.

Below are the queries to get the error records information while processing interface data.

AP Invoices:

  SELECT
   airl.last_update_date process_date,
   HU.NAME,
  nvl(ail.invoice_line_id,aii.invoice_id) process_id,
  'AP' module,
  'Import AP Invoice' interface_name,
  aii.source source,
  NVL(SUBSTR( ail.dist_code_concatenated,1,instr(ail.dist_code_concatenated,'.',1,1)-1),'ALL') company,
  'ERROR' status,
  ail.line_type_lookup_code ,
  airl.parent_table ,
  ail.description ,
  NVL(ail.distribution_set_name,dist_code_concatenated) ,
  pvsa.vendor_site_code ,
  listagg(NVL(airl.reject_lookup_code,'AP_INV_ERROR') ,';') within group (order by NVL(airl.reject_lookup_code,'AP_INV_ERROR')) error_code,
  listagg(flv.meaning,';')  within group (order by NVL(flv.meaning,'AP_INV_ERROR'))error_summary,
  NULL error_details,
  pv.vendor_name ,
  pv.segment1 ,
  aii.invoice_amount,
  aii.invoice_date ,
  ail.amount ,
  at.description ,
  aii.invoice_id ,
  aii.invoice_num
  FROM ap_interface_rejections airl,
  ap_invoice_lines_interface ail,
  ap_invoices_interface aii,
  fnd_lookup_values flv,
  hr_operating_units hu,
  po_vendors pv,
  po_vendor_sites_all pvsa,
  ap_terms at
WHERE 1             =1 --airl.parent_table='AP_INVOICE_LINES_INTERFACE'
AND ( airl.parent_id=ail.invoice_line_id
OR airl.parent_id   =aii.invoice_id )
AND ail.invoice_id  =aii.invoice_id
and   flv.lookup_type = 'REJECT CODE'
AND flv.lookup_code   =airl.reject_lookup_code
AND hu.organization_id=ail.org_id
AND aii.vendor_id     =pv.vendor_id
AND aii.vendor_site_id =pvsa.vendor_site_id
AND AT.TERM_ID(+)     = AII.TERMS_ID
AND aii.status= 'REJECTED'
group by
   airl.last_update_date ,
   HU.NAME ,
  nvl(ail.invoice_line_id,aii.invoice_id) ,
  'AP' ,
  'Import AP Invoice' ,
  aii.source ,
  NVL(SUBSTR( ail.dist_code_concatenated,1,instr(ail.dist_code_concatenated,'.',1,1)-1),'ALL') ,
  'ERROR' ,
  ail.line_type_lookup_code ,
  airl.parent_table ,
  ail.description ,
  NVL(ail.distribution_set_name,dist_code_concatenated) ,
  pvsa.vendor_site_code ,
  NULL ,
  pv.vendor_name ,
  pv.segment1 ,
  aii.invoice_amount,
  aii.invoice_date ,
  ail.amount ,
  at.description ,
  aii.invoice_id ,
  aii.invoice_num ;

Inventory Item Interface:

SELECT msii.last_update_date,
  PROCESS_DATE,
  NVL(
  (SELECT NVL(NAME,'ALL')
  FROM HR_OPERATING_UNITS HOU
  WHERE ORGANIZATION_ID=OOD.OPERATING_UNIT
  ),'ALL') business_unit_name,
  mie.unique_id process_id,
  'INV' module,
  'Item Interface' INTERFACE_NAME,
  MSII.TRANSACTION_TYPE ,
  'Inventory' ,
  'ALL' ,
  'ERROR' status,
  msii.segment1 ,
  msii.description ,
  msii.item_type ,
  msii.item_number ,
  msii.organization_code ,
  mie.error_message ,
  NULL error_details,
  msii.template_id ,
  msii.template_name ,
  NULL ,
  msii.organization_code ,
  msii.LIST_PRICE_PER_UNIT ,
  msii.item_type
FROM mtl_system_items_interface msii,
  mtl_interface_errors mie,
  org_organization_definitions ood
WHERE msii.transaction_id         =mie.transaction_id
AND MSII.REQUEST_ID               =MIE.REQUEST_ID
AND OOD.ORGANIZATION_ID           =MSII.ORGANIZATION_ID
AND MSII.last_update_date        IS NOT NULL
AND mie.unique_id                IS NOT NULL
AND MSII.TRANSACTION_TYPE        IS NOT NULL
AND TRUNC(MSII.last_update_date) >= TRUNC(sysdate) -60;

AR Auto Invoice Errors:

SELECT ril.last_update_date,
  'ERP11i' domain,
  hou.name ,
  ril.interface_line_id ,
  'AR' module,
  'Auto Invoice' Interface_NAME,
  ril.batch_source_name source,
  NVL(
  (SELECT segment1
  FROM ra_interface_distributions_all rid
  WHERE rid.interface_line_id =ril.interface_line_id
  AND rownum                  =1
  ),
  (SELECT gcc.segment1
  FROM gl_code_combinations_kfv gcc,
    ra_interface_distributions_all rid1
  WHERE rid1.interface_line_id  =ril.interface_line_id
  AND rid1.code_combination_id  =gcc.code_combination_id
  AND rid1.code_combination_id IS NOT NULL
  AND rownum                    =1
  )) company,
  'ERROR' status,
  ril.INTERFACE_LINE_ATTRIBUTE1 ,
  ril.INTERFACE_LINE_ATTRIBUTE2 ,
  ril.line_type ,
  REPLACE(REPLACE(ril.description,chr(10)),'|') ,
  rc.customer_name ,
  REPLACE(REPLACE(rie.message_text,chr(10)),'|') error_summary,
  REPLACE(REPLACE(rie.invalid_value ,chr(10)),'|')error_details,
  ril.CUST_TRX_TYPE_NAME ,
  ril.term_name ,
  ril.amount ,
  ril.trx_number ,
  ril.line_number ,
  ril.tax_code ,
  ril.trx_date ,
  ril.batch_source_name ,
  ril.sales_order ,
  ril.sales_order_line
FROM ra_interface_lines_all ril,
  ra_interface_errors_all rie,
  hr_operating_units hou,
  ra_customers rc
WHERE ril.interface_line_id = rie.interface_line_id
AND ril.org_id              =hou.organization_id
AND rc.customer_id(+)       =ril.ORIG_SYSTEM_BILL_CUSTOMER_ID
AND ril.last_update_date   IS NOT NULL
  --AND ril.request_id IS NOT NULL
AND ril.interface_line_id IS NOT NULL
AND ril.batch_source_name IS NOT NULL
AND hou.name              IS NOT NULL;

Material Transactions:


SELECT mti.last_update_date,
  NVL(
  (SELECT NVL(NAME,'ALL')
  FROM HR_OPERATING_UNITS HOU
  WHERE ORGANIZATION_ID=OOD.OPERATING_UNIT
  ),'ALL') business_unit_name,
  mti.transaction_interface_id ,
  'INV' module,
  'Material Transactions' Interface_NAME,
  mti.source_code source,
  NVL(NVL(mti.dst_segment1,
  (SELECT segment1
  FROM gl_code_combinations_kfv
  WHERE code_combination_id=mti.distribution_account_id
  AND rownum               =1
  )),'ALL') company,
  ood.organization_name ,
  msib.description ,
  mti.transaction_reference ,
  NVL(mti.error_code,'MTL_TXN_ERROR') error_code,
  mti.ERROR_EXPLANATION error_summary,
  NULL error_details,
  mtt.transaction_type_name ,
  mti.transaction_quantity ,
  TO_CHAR(mti.transaction_date,'DD-MON-YYYY') ,
  msib.segment1 ,
  mti.subinventory_code ,
  mti.dsp_segment1 ,
  mti.dsp_segment2 ,
  mti.dsp_segment3 ,
  mti.transaction_uom
FROM mtl_transactions_interface mti,
  mtl_transaction_types mtt,
  org_organization_definitions ood,
  mtl_system_items_b msib
WHERE mti.process_flag            =3 --Errored
AND ood.organization_id           =mti.organization_id
AND mtt.transaction_type_id       =mti.transaction_type_id
AND mti.inventory_item_id         =msib.inventory_item_id(+)
AND mti.organization_id           =msib.organization_id(+)
AND mti.LAST_UPDATE_DATE         IS NOT NULL
AND mti.transaction_interface_id IS NOT NULL
AND mti.source_code              IS NOT NULL
AND TRUNC(mti.LAST_UPDATE_DATE)  >= TRUNC(sysdate) -60;


GL Interface Errors:

SELECT gi.date_created,
  'ALL' business_unit_name,
  'GL' module,
  'Journal Import' PROCESS_NAME,
  gi.user_je_source_name source,
  NVL(NVL(gi.segment1,
  (SELECT gcc.segment1
  FROM gl_code_combinations_kfv gcc
  WHERE gcc.code_combination_id =gi.code_combination_id
  AND rownum                    =1
  )),'ALL') company,
  'ERROR' process_status,
  gsb.name ,
  gi.entered_dr ,
  gi.entered_cr ,
  gi.REFERENCE10 ,
  gi.CURRENCY_CODE ,
  'GL_IMPORT_ERROR' error_code,
  flv.meaning error_summary,
  flv.description error_details,
  gi.USER_JE_CATEGORY_NAME ,
  gi.GROUP_ID ,
  gi.REFERENCE1 ,
  gi.REFERENCE4 ,
  gi.ACCOUNTING_DATE ,
  gi.TRANSACTION_DATE ,
  gi.SEGMENT2 ,
  gi.SEGMENT3 ,
  gi.segment4
FROM GL_INTERFACE gi,
  gl_sets_of_books gsb,
  fnd_lookup_values flv
WHERE (gi.status LIKE 'E%'
OR gi.status          = 'CORRECTED')
AND gi.set_of_books_id=gsb.set_of_books_id
AND gi.status         =flv.lookup_code(+)
AND flv.language      =userenv('lang')
AND flv.lookup_type   ='PSP_SUSP_AC_ERRORS'
  --AND gi.set_of_books_id = 2002
AND (gi.USER_JE_SOURCE_NAME LIKE 'WFL_COGS')
AND gi.date_created        IS NOT NULL
AND gi.user_je_source_name IS NOT NULL
AND gsb.name               IS NOT NULL
AND TRUNC(gi.date_created) >= TRUNC(sysdate) -60
ORDER BY gi.user_je_source_name,
  DECODE(gi.status, 'CORRECTED', 2, 1),
  gi.accounting_date,
  gi.currency_code,
  gi.entered_dr,
  gi.entered_cr ;

PO Interface Errors:

SELECT pie.last_update_date,
  NVL(
  (SELECT NVL(NAME,'ALL')
  FROM HR_OPERATING_UNITS HOU
  WHERE ORGANIZATION_ID=NVL(phi.org_id,pdi.org_id)
  ),'ALL')business_unit_name,
  pie.interface_transaction_id ,
  'PO' module,
  'PO_DOCS_OPEN_INTERFACE' INTERFACE_NAME,
  DECODE(phi.action,'ORIGINAL','CREATE',phi.action) CURRENT_ACTIVITY,
  phi.interface_source_code source,
  NVL( NVL(pdi.charge_account_segment1,NVL(
  (SELECT segment1
  FROM gl_code_combinations_kfv
  WHERE code_combination_id=pdi.charge_account_id
  AND ROWNUM               =1
  ), NVL(
  (SELECT pdi1.charge_account_segment1
  FROM PO_DISTRIBUTIONS_INTERFACE pdi1
  WHERE pdi1.interface_header_id    =phi.interface_header_id
  AND rownum                        =1
  AND pdi1.charge_account_segment1 IS NOT NULL
  ),
  (SELECT segment1
  FROM gl_code_combinations_kfv
  WHERE code_combination_id=
    (SELECT pdi1.charge_account_id
    FROM PO_DISTRIBUTIONS_INTERFACE pdi1
    WHERE pdi1.interface_header_id=phi.interface_header_id
    AND rownum                    =1
    )
  )) )),'ALL') company,
  pvs.vendor_site_code attribute1,
  NVL(pie.error_message_name,'PO_INT_ERROR') error_code,
  pie.column_name error_summary,
  pie.error_message error_details,
  phi.document_type_code ,
  pv.vendor_name ,
  pv.segment1 ,
  (SELECT location_code
  FROM hr_locations
  WHERE location_id=phi.ship_to_location_id
  ),
  pli.line_num ,
  (SELECT SEGMENT1
  FROM mtl_system_items_b
  WHERE inventory_item_id=pli.item_id
  AND rownum             =1
  ) item_name ,
  NVL(pli.uom_code,unit_of_measure) ,
  pli.unit_price ,
  pli.FREIGHT_CARRIER
FROM po_interface_errors pie,
  po_headers_interface phi,
  po_lines_interface pli,
  PO_DISTRIBUTIONS_INTERFACE pdi,
  po_vendors pv,
  po_vendor_sites_all pvs
WHERE pie.interface_type            ='PO_DOCS_OPEN_INTERFACE'
AND phi.interface_header_id(+)      =pie.interface_header_id
AND pli.interface_line_id(+)        =pie.interface_line_id
AND pdi.interface_distribution_id(+)=pie.INTERFACE_DISTRIBUTION_ID
AND pdi.interface_header_id(+)      =pie.interface_header_id
AND pdi.interface_line_id(+)        =pie.interface_line_id
AND phi.vendor_id                   = pv.vendor_id
AND phi.vendor_site_id              = pvs.vendor_site_id
AND pie.last_update_date           IS NOT NULL
AND pie.interface_transaction_id   IS NOT NULL
AND phi.interface_source_code      IS NOT NULL
AND Phi.action                     IS NOT NULL
AND TRUNC(pie.last_update_date)    >= TRUNC(sysdate) -60;

AR Receipts Errors:

SELECT I.last_update_date,
  HOU.NAME BUSINESS_UNIT_NAME,
  I.TRANSMISSION_RECORD_ID process_id,
  'AR' module,
  'AR Receipts' interface_name,
  'Auto Receipt' source,
  NVL(
  (SELECT gcc.segment1
  FROM AR_RECEIPT_METHOD_ACCOUNTS_ALL ARMA,
    gl_code_combinations_kfv gcc
  WHERE arma.receipt_method_id           =I.receipt_method_id
  AND NVL(arma.cash_ccid,on_account_ccid)=gcc.code_combination_id
  AND rownum                             =1
  ), 'ALL')company,
  'ERROR' status,
  I.RECEIPT_METHOD ,
  I.RECEIPT_DATE ,
  I.REMITTANCE_BANK_NAME ,
  I.REMITTANCE_BANK_BRANCH_NAME ,
  I.CUSTOMER_BANK_NAME ,
  NVL(i.status,'AR_RECEIPT_ERRORS') error_code,
  fnm.message_text error_summary,
  C.ACCOUNT_NUMBER ,
  I.RECORD_TYPE ,
  I.TRANSMISSION_AMOUNT ,
  I.DESTINATION_ACCOUNT ,
  I.LOCKBOX_NUMBER ,
  I.BATCH_NAME ,
  I.BATCH_AMOUNT ,
  I.CURRENCY_CODE ,
  I.REMITTANCE_AMOUNT ,
  T.transmission_name
FROM AR_PAYMENTS_INTERFACE_ALL I,
  AR_TRANSMISSIONS_all T,
  AR_TRANS_RECORD_FORMATS F,
  HZ_CUST_ACCOUNTS C,
  HZ_PARTIES PARTY,
  FND_NEW_MESSAGES FNM,
  HR_OPERATING_UNITS HOU
WHERE I.TRANSMISSION_ID         = T.TRANSMISSION_ID
AND T.REQUESTED_TRANS_FORMAT_ID = F.TRANSMISSION_FORMAT_ID
AND I.RECORD_TYPE               = F.RECORD_IDENTIFIER
AND I.CUSTOMER_ID               = C.CUST_ACCOUNT_ID(+)
AND C.PARTY_ID                  = PARTY.PARTY_ID(+)
AND HOU.ORGANIZATION_ID         =I.ORG_ID
AND I.STATUS                    = FNM.MESSAGE_NAME
AND I.STATUS NOT LIKE '%/_OK%' ESCAPE '/'
AND I.STATUS NOT             IN ('AR_PLB_NEW_RECORD','AR_PLB_ALT_MATCH_NONE','AR_PLB_ALT_MATCH_CONFIRMED','AR_PLB_GL_DATE_OK','AR_PLB_RECEIPT_OK','AR_PLB_APP_OK')
AND I.last_update_date       IS NOT NULL
AND HOU.NAME                 IS NOT NULL
AND I.TRANSMISSION_RECORD_ID IS NOT NULL
AND I.GL_DATE BETWEEN TRUNC(TRUNC(sysdate-30,'MM')-1,'MM') AND LAST_DAY(SYSDATE);