AP Supplier Open Interface (Oracle Fusion SQL)

Enterprise Resource Planning (ERP) Oracle Account Payables (AP-Fusion-SQL's)
1

This AP Supplier Open Interface query provides the status of suppliers processed through the interface, showing both successfully processed and rejected records with corresponding rejection messages.

#sqlquery

SELECT 
 -- SQL4Fusion (An Orbit Analytics Project)
 -- This query retrieves a combined list of processed and rejected supplier interface records.
    SUPPLIER_NUMBER,
    SUPPLIER_NAME,
	MESSAGE,
	STATUS
FROM (SELECT 
  PSI.SEGMENT1 SUPPLIER_NUMBER,
  PSI.VENDOR_NAME SUPPLIER_NAME,
  NULL MESSAGE,
  'PROCESSED' STATUS
FROM
  POZ_SUPPLIERS_INT PSI
WHERE
  1 = 1
  AND PSI.STATUS = 'PROCESSED'
UNION ALL
SELECT
  NVL (PSI.SEGMENT1, ' ') SUPPLIER_NUMBER,
  PSI.VENDOR_NAME SUPPLIER_NAME,
  FNM.MESSAGE_TEXT MESSAGE,
  'REJECTED' STATUS
FROM
  POZ_SUPPLIER_INT_REJECTIONS PSIR,
  POZ_SUPPLIERS_INT PSI,
  FND_NEW_MESSAGES FNM
WHERE
  1 = 1
  AND PSI.STATUS = 'REJECTED'
  AND PSIR.PARENT_ID = PSI.VENDOR_INTERFACE_ID
  AND PSIR.PARENT_TABLE = 'PSI'
  AND FNM.MESSAGE_NAME = PSIR.REJECT_LOOKUP_CODE
  AND FNM.LANGUAGE_CODE = USERENV ('LANG')
ORDER BY
  STATUS)
WHERE 1=1
ORDER BY STATUS, SUPPLIER_NUMBER