Query to find complete Supplier Details Bank ,tax address and accounting information

Supplier with complete bank  , tax and address details

ap to ZX links
AP to Gl link

AP supplier to Bank link

supplier with Tax codes and bank details

SELECT   distinct ASP.VENDOR_NAME,
         ASP.VENDOR_NAME_ALT,
         ASP.SEGMENT1 ORACLE_VENDOR_NUM,
         ASP.ENABLED_FLAG,
         ASP.VENDOR_TYPE_LOOKUP_CODE,
         ASP.ONE_TIME_FLAG,
         ASP.ALWAYS_TAKE_DISC_FLAG,
         ASP.PAY_DATE_BASIS_LOOKUP_CODE,
         ASP.PAYMENT_PRIORITY,
         ASP.INVOICE_CURRENCY_CODE,
         ASP.PAYMENT_CURRENCY_CODE,
         ASP.HOLD_ALL_PAYMENTS_FLAG,
         ASP.HOLD_FUTURE_PAYMENTS_FLAG,
         ASP.HOLD_FLAG,
         ASP.TERMS_DATE_BASIS,
         ASP.HOLD_UNMATCHED_INVOICES_FLAG,
         ASP.AUTO_CALCULATE_INTEREST_FLAG,
         ASP.EXCLUDE_FREIGHT_FROM_DISCOUNT,
         ASP.MATCH_OPTION,
         HOU.NAME "Operating Unit Name",        
         ASA.VENDOR_SITE_CODE,
         ASA.VENDOR_SITE_CODE_ALT "LEGACY_VENDOR_NAME",
         ASA.PURCHASING_SITE_FLAG,
         ASA.RFQ_ONLY_SITE_FLAG,
         ASA.PAY_SITE_FLAG,
         ASA.ATTENTION_AR_FLAG,
         ASA.ADDRESS_LINE1,
         ASA.ADDRESS_LINE2,
         ASA.ADDRESS_LINE3,
         ASA.ADDRESS_LINE4,
         ASA.CITY,
         ASA.STATE,
         ASA.ZIP,
         ASA.PROVINCE,
         ASA.COUNTY,
         ASA.COUNTRY,
         (SELECT ZXR.REGISTRATION_NUMBER
          FROM   APPS.ZX_PARTY_TAX_PROFILE ZPT,
                 APPS.ZX_REGISTRATIONS ZXR
          WHERE  ZPT.PARTY_TAX_PROFILE_ID = ZXR.PARTY_TAX_PROFILE_ID
          AND    NVL (ZPT.PARTY_TYPE_CODE, 'THIRD_PARTY_SITE') = 'THIRD_PARTY_SITE'
          AND    ZPT.PARTY_ID = ASA.PARTY_SITE_ID
          and ASA.country = zxr.tax_regime_code  
          and rownum = 1) REGISTRATION_NUMBER,
         (SELECT NAME FROM APPS.AP_TERMS WHERE TERM_ID = ASA.TERMS_ID) TERMS,
         ASA.AUTO_TAX_CALC_FLAG,
         ASA.OFFSET_TAX_FLAG,
         ASA.ATTRIBUTE1 "LEGACY_VENDOR_NUMBER",
         ASA.supplier_notif_method,
         ASA.FAX,
         ASA.FAX_AREA_CODE,
         ASA.EMAIL_ADDRESS,  
         CONTACT.FIRST_NAME,
         CONTACT.LAST_NAME,
         CONTACT.PRIMARY_PHONE_NUMBER,
         CONTACT.EMAIL_ADDRESS  CONTACT_EMAIL_ADDRESS,
         ASA.ACCTS_PAY_CODE_COMBINATION_ID,
         (SELECT  GCC1.CONCATENATED_SEGMENTS
          FROM    APPS.GL_CODE_COMBINATIONS_KFV GCC1
          WHERE   GCC1.CODE_COMBINATION_ID = ASA.ACCTS_PAY_CODE_COMBINATION_ID) ACCOUNT_PAY_CODE_COMBINATION,
         ASA.PREPAY_CODE_COMBINATION_ID,
         (SELECT  GCC2.CONCATENATED_SEGMENTS
          FROM    APPS.GL_CODE_COMBINATIONS_KFV GCC2
          WHERE   GCC2.CODE_COMBINATION_ID = ASA.PREPAY_CODE_COMBINATION_ID) PREPAY_CODE_COMBINATION,
         (SELECT PAYMENT_METHOD_CODE
          FROM   APPS.IBY_EXTERNAL_PAYEES_ALL HP,
                 APPS.IBY_EXT_PARTY_PMT_MTHDS IEPM
          WHERE  EXT_PMT_PARTY_ID = EXT_PAYEE_ID
          AND    PRIMARY_FLAG = 'Y'
          AND    SUPPLIER_SITE_ID = ASA.VENDOR_SITE_ID
          and rownum = 1) PAYMENT_METHOD_CODE,
         (SELECT EBA.BANK_ACCOUNT_NUM
          FROM APPS.AP_SUPPLIERS SUP
             , APPS.AP_SUPPLIER_SITES_ALL       SS
             , APPS.IBY_EXTERNAL_PAYEES_ALL EPA
             , APPS.IBY_PMT_INSTR_USES_ALL  PIU
             , APPS.IBY_EXT_BANK_ACCOUNTS   EBA
         WHERE SUP.VENDOR_ID     = SS.VENDOR_ID
           AND SS.VENDOR_SITE_ID = EPA.SUPPLIER_SITE_ID
           AND EPA.EXT_PAYEE_ID  = PIU.EXT_PMT_PARTY_ID      
           AND PIU.INSTRUMENT_ID = EBA.EXT_BANK_ACCOUNT_ID
           AND SUP.VENDOR_ID      = ASP.VENDOR_ID
           AND SS.VENDOR_SITE_ID  = ASA.VENDOR_SITE_ID
         

           AND ROWNUM = 1                  ) BANK_ACCOUNT_NUM,
         (SELECT EBA.FOREIGN_PAYMENT_USE_FLAG
          FROM APPS.AP_SUPPLIERS SUP
             , APPS.AP_SUPPLIER_SITES_ALL       SS
             , APPS.IBY_EXTERNAL_PAYEES_ALL EPA
             , APPS.IBY_PMT_INSTR_USES_ALL  PIU
             , APPS.IBY_EXT_BANK_ACCOUNTS   EBA
         WHERE SUP.VENDOR_ID     = SS.VENDOR_ID
           AND SS.VENDOR_SITE_ID = EPA.SUPPLIER_SITE_ID
           AND EPA.EXT_PAYEE_ID  = PIU.EXT_PMT_PARTY_ID      
           AND PIU.INSTRUMENT_ID = EBA.EXT_BANK_ACCOUNT_ID
           AND SUP.VENDOR_ID      = ASP.VENDOR_ID
           AND SS.VENDOR_SITE_ID  = ASA.VENDOR_SITE_ID
         

           AND FOREIGN_PAYMENT_USE_FLAG = 'Y'
           AND ROWNUM = 1) FOREIGN_PAYMENT_USE_FLAG,
           ASP.VENDOR_ID,
           ASA.VENDOR_SITE_ID,          
         (SELECT EBA.IBAN
          FROM APPS.AP_SUPPLIERS SUP
             , APPS.AP_SUPPLIER_SITES_ALL       SS
             , APPS.IBY_EXTERNAL_PAYEES_ALL EPA
             , APPS.IBY_PMT_INSTR_USES_ALL  PIU
             , APPS.IBY_EXT_BANK_ACCOUNTS   EBA
         WHERE SUP.VENDOR_ID     = SS.VENDOR_ID
           AND SS.VENDOR_SITE_ID = EPA.SUPPLIER_SITE_ID
           AND EPA.EXT_PAYEE_ID  = PIU.EXT_PMT_PARTY_ID      
           AND PIU.INSTRUMENT_ID = EBA.EXT_BANK_ACCOUNT_ID
           AND SUP.VENDOR_ID      = ASP.VENDOR_ID
           AND SS.VENDOR_SITE_ID  = ASA.VENDOR_SITE_ID
         

           AND ROWNUM = 1                   ) IBAN,
         (SELECT CEB.EFT_SWIFT_CODE
          FROM APPS.AP_SUPPLIERS SUP
             , APPS.AP_SUPPLIER_SITES_ALL       SS
             , APPS.IBY_EXTERNAL_PAYEES_ALL EPA
             , APPS.IBY_PMT_INSTR_USES_ALL  PIU
             , APPS.IBY_EXT_BANK_ACCOUNTS   EBA
             , apps.ce_bank_branches_v CEB
         WHERE SUP.VENDOR_ID     = SS.VENDOR_ID
           AND SS.VENDOR_SITE_ID = EPA.SUPPLIER_SITE_ID
           AND EPA.EXT_PAYEE_ID  = PIU.EXT_PMT_PARTY_ID      
           AND PIU.INSTRUMENT_ID = EBA.EXT_BANK_ACCOUNT_ID
           AND SUP.VENDOR_ID      = ASP.VENDOR_ID
           AND SS.VENDOR_SITE_ID  = ASA.VENDOR_SITE_ID
           AND EBA.BRANCH_ID = CEB.PK_ID
         

           AND ROWNUM = 1                     ) SWIFT_CODE,
(select 'DFF1: '||attribute1||' DFF2: '||attribute2||
' DFF3: '||attribute3||
' DFF4: '||attribute4||
' DFF5: '||attribute5 Germany_DFF_Z4_Reports from apps.CLE_AP_SUPPLIER_SITES_EXT
where dff_id1=ASA.VENDOR_SITE_ID
and rownum<2) DFF_DE,
ASA.attribute1 CODA_SUPP_NUMBER,
trunc(ASA.creation_date ) site_creation_date,
asa.global_attribute1,
asa.global_attribute2,
(SELECT ZPT.REP_REGISTRATION_NUMBER
   FROM APPS.ZX_PARTY_TAX_PROFILE ZPT,
        APPS.ZX_REGISTRATIONS ZXR
  WHERE ZPT.PARTY_TAX_PROFILE_ID = ZXR.PARTY_TAX_PROFILE_ID
   

    AND ZPT.PARTY_ID = ASA.PARTY_SITE_ID
    and ASA.country = zxr.tax_regime_code  
    and rownum =1) REP_REGISTRATION_NUMBER,
(SELECT description
   from apps.fnd_user aa
   where aa.user_id = asp.created_by) SUPPLIER_CREATED_BY,
(SELECT description
   from apps.fnd_user aa
   where aa.user_id = asp.last_updated_by) SUPPLIER_LAST_UPDATED_BY,
asp.last_update_date SUPPLIER_LAST_UPDATE_DATE,  
(SELECT description
   from apps.fnd_user aa
   where aa.user_id = asa.created_by) SUP_SITE_CREATED_BY,
(SELECT description
   from apps.fnd_user aa
   where aa.user_id = asa.last_updated_by) SUP_SITE_LAST_UPDATED_BY,
asa.last_update_date SUP_SITE_LAST_UPDATE_DATE                
FROM   APPS.AP_SUPPLIERS ASP,
       APPS.AP_SUPPLIER_SITES_ALL ASA,
       APPS.FND_USER FNU,
       APPS.HR_OPERATING_UNITS HOU,
         (SELECT HP.PERSON_FIRST_NAME FIRST_NAME,
                 HP.PERSON_MIDDLE_NAME MIDDLENAME,
                 HP.PERSON_LAST_NAME LAST_NAME,
                 HP2.PRIMARY_PHONE_NUMBER,
                 HP2.EMAIL_ADDRESS,
                 APS.VENDOR_NAME,
                 APS.ATTRIBUTE1,
                 APS.VENDOR_ID ,
                 PVC.VENDOR_SITE_ID,
                 pvc.org_party_site_id
            FROM APPS.AP_SUPPLIER_CONTACTS PVC,
                 APPS.HZ_PARTIES HP,
                 APPS.HZ_RELATIONSHIPS HPR,
                 APPS.HZ_ORG_CONTACTS HOC,
                 APPS.HZ_PARTIES HP2,
                 APPS.AP_SUPPLIERS APS
           WHERE     PVC.PER_PARTY_ID = HP.PARTY_ID
                 AND PVC.REL_PARTY_ID = HP2.PARTY_ID
                 AND PVC.ORG_CONTACT_ID = HOC.ORG_CONTACT_ID(+)
                 AND PVC.RELATIONSHIP_ID = HPR.RELATIONSHIP_ID
                 AND HPR.DIRECTIONAL_FLAG = 'F'
                 AND APS.PARTY_ID = HPR.OBJECT_ID
                 AND HPR.RELATIONSHIP_CODE = 'CONTACT_OF'
                 AND HPR.OBJECT_TYPE = 'ORGANIZATION'
                 AND NVL (APS.VENDOR_TYPE_LOOKUP_CODE, 'DUMMY') <> 'EMPLOYEE') CONTACT
       

WHERE  ASP.VENDOR_ID = ASA.VENDOR_ID(+)
AND    ASA.PARTY_SITE_ID = CONTACT.org_party_site_id(+)
AND    ASA.ORG_ID = HOU.ORGANIZATION_ID(+)    
AND    ASP.CREATED_BY = FNU.USER_ID




AND    ASA.ORG_ID = :OrgId  


ORDER BY trunc(ASA.creation_date ),ASP.SEGMENT1, HOU.NAME