Query to Get Supplier Details in Oracle Apps R12 - Orapoint

This Query is helpful to find supplier details with certain columns like vendor name, vendor number, vendor type, vat number, vendor site, address, state in Oracle Application R12

Main Table of Supplier

AP_SUPPLIERS

AP_SUPPLIER_SITES_ALL

hz_party_sites

AP_CHECKS_ALL

HR_OPERATING_UNITS

select  distinct
        VENDOR_NAME,
         VENDOR_NUM,
         'No' Purchase_order_hold,
        VENDOR_TYPE,
         LOCATION,
         country,
         VAT_REGISTRATION_NUM,
         VENDOR_SITE,
         ADDRESS1,
         ADDRESS2,
         ADDRESS3,
         CITY,
         STATE,
         ZIP,
         TAX_REPORTING_NAME,
         TAX_REPORTING_SITE_FLAG,
         address_name,
        Addressee
from (SELECT   
         AP.VENDOR_NAME VENDOR_NAME,
         AP.SEGMENT1 VENDOR_NUM,
         'No' Purchase_order_hold,
         AP.VENDOR_TYPE_LOOKUP_CODE VENDOR_TYPE,
         OU.NAME LOCATION,
         ap.VAT_REGISTRATION_NUM,
         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,
         hzps.party_site_name address_name,
           hzps.Addressee
FROM     AP_SUPPLIERS AP,
         AP_SUPPLIER_SITES_ALL APS,
         hz_party_sites hzps,
         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
and      aps.party_site_id = hzps.party_site_id
AND NVL (hzps.end_date_active, SYSDATE) >= SYSDATE
GROUP BY AP.VENDOR_NAME,
         AP.VENDOR_TYPE_LOOKUP_CODE,
         OU.NAME,
         ap.VAT_REGISTRATION_NUM,
         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,
         aps.country,
         hzps.party_site_name,
           hzps.Addressee
ORDER BY AP.VENDOR_NAME, ACA.CHECK_DATE DESC)t;