Query to Find Supplier Payment Information in Oracle Apps R12 - Orapoint

This query is useful to get Supplier payment related information in Oracle Apps R12, this query will extract supplier payment details like vendor name, vendor number, org type, vendor type, location, vendor site, address, amount paid, check date and more

Important table of Supplier Payment in Oracle Apps R12

AP_SUPPLIERS

AP_SUPPLIER_SITES_ALL

AP_CHECKS_ALL

HR_OPERATING_UNITS

SELECT   AP.VENDOR_NAME VENDOR_NAME,
         AP.SEGMENT1 VENDOR_NUM,
         AP.ORGANIZATION_TYPE_LOOKUP_CODE ORGANIZATION_TYPE,
         AP.VENDOR_TYPE_LOOKUP_CODE VENDOR_TYPE,
         OU.NAME LOCATION,
         APS.VENDOR_SITE_CODE VENDOR_SITE,
         APS.ADDRESS_LINE1 ADDRESS1,
         APS.ADDRESS_LINE2 ADDRESS2,
         APS.ADDRESS_LINE3 ADDRESS3,
         APS.CITY CITY,
         APS.STATE STATE,
         APS.ZIP ZIP,
         APS.COUNTRY COUNTRY,
         AP.TAX_REPORTING_NAME TAX_REPORTING_NAME,
         APS.TAX_REPORTING_SITE_FLAG TAX_REPORTING_SITE_FLAG,
         TO_CHAR(ACA.CHECK_DATE,'DD-MON-YYYY')   CHECK_DATE,
         ACA.AMOUNT AMOUNT_PAID,
         ACA.STATUS_LOOKUP_CODE,
FROM     AP_SUPPLIERS AP,
         AP_SUPPLIER_SITES_ALL APS,
         AP_CHECKS_ALL ACA,
         HR_OPERATING_UNITS OU
WHERE    1 = 1
AND      AP.VENDOR_ID = APS.VENDOR_ID
AND      APS.ORG_ID = OU.ORGANIZATION_ID
AND      ACA.VENDOR_ID(+) = APS.VENDOR_ID
AND      ACA.VENDOR_SITE_ID(+) = APS.VENDOR_SITE_ID
GROUP BY AP.VENDOR_NAME,
         AP.VENDOR_TYPE_LOOKUP_CODE,
         OU.NAME,
         APS.VENDOR_SITE_CODE,
         APS.VENDOR_SITE_ID,
         APS.ADDRESS_LINE1,
         APS.ADDRESS_LINE2,
         APS.ADDRESS_LINE3,
         APS.CITY,
         APS.STATE,
         APS.ZIP,
         APS.COUNTRY,
         AP.TAX_REPORTING_NAME,
         AP.ORGANIZATION_TYPE_LOOKUP_CODE,
         AP.SEGMENT1,
         APS.TAX_REPORTING_SITE_FLAG,
         ACA.STATUS_LOOKUP_CODE,
         ACA.CHECK_DATE,
         ACA.AMOUNT
ORDER BY AP.VENDOR_NAME, ACA.CHECK_DATE DESC;