SQL query to find details of active vendors from Oracle Account Payables

This query will give you details of active vendors, their active sites, their address, active bank account etc.

This query is designed to exclude employee vendors.
Please refer another post -  SQL query to find details of active employee vendors from Oracle Account Payables



SELECT DISTINCT
PV.VENDOR_NAME NAME,
NVL(PV.INDIVIDUAL_1099,PV.NUM_1099) TAX_ID,
PV.SEGMENT1 ,
PV.TAX_REPORTING_NAME NAME1099,
TRIM((PVSA.AREA_CODE||' '||PVSA.PHONE)) PHONE1,
TRIM((PVSA.FAX_AREA_CODE||' '||PVSA.FAX)) FAX ,
PVSA.EMAIL_ADDRESS EMAIL1,
PVSA.ADDRESS_LINE1 ADDRESS1,
TRIM((PVSA.ADDRESS_LINE2||' '||PVSA.ADDRESS_LINE3||' '||PVSA.ADDRESS_LINE4)) ADDRESS2,
PVSA.CITY,
PVSA.STATE,
PVSA.ZIP,
ABB.BANK_NUM ACH_BANK_ROUTING_NUMBER,
APAA.BANK_ACCOUNT_NUM ACH_ACCOUNT_NUMBER,
DECODE(APAA.BANK_ACCOUNT_TYPE,'CHECKING','CHECKING ACCOUNT','SAVINGS','SAVINGS ACCOUNT') ACH_ACCOUNT_TYPE,
PVSA.VENDOR_SITE_CODE REFERENCE
FROM PO.PO_VENDORS PV LEFT OUTER JOIN APPS.PO_LOOKUP_CODES PLC 
ON PV.VENDOR_TYPE_LOOKUP_CODE = PLC.LOOKUP_CODE AND PLC.LOOKUP_TYPE = 'VENDOR TYPE',
PO.PO_VENDOR_SITES_ALL PVSA LEFT OUTER JOIN APPS.FND_TERRITORIES_TL FTL
ON FTL.TERRITORY_CODE = PVSA.COUNTRY
LEFT OUTER JOIN AP.AP_BANK_ACCOUNT_USES_ALL APAUA ON PVSA.VENDOR_ID=APAUA.VENDOR_ID AND (APAUA.END_DATE IS NULL OR APAUA.END_DATE > SYSDATE) AND (APAUA.VENDOR_SITE_ID IS NULL OR APAUA.VENDOR_SITE_ID=PVSA.VENDOR_SITE_ID)
LEFT OUTER JOIN AP.AP_BANK_ACCOUNTS_ALL APAA  ON APAUA.EXTERNAL_BANK_ACCOUNT_ID=APAA.BANK_ACCOUNT_ID --AND (APAA.INACTIVE_DATE IS NULL OR APAA.INACTIVE_DATE > SYSDATE)
LEFT OUTER JOIN AP.AP_BANK_BRANCHES ABB ON APAA.BANK_BRANCH_ID=ABB.BANK_BRANCH_ID --And (Abb.End_Date Is Null Or Abb.End_Date > Sysdate)
WHERE PV.VENDOR_ID = PVSA.VENDOR_ID
AND PV.ENABLED_FLAG <>'N'
AND (PV.VENDOR_TYPE_LOOKUP_CODE IS NULL OR UPPER(PV.VENDOR_TYPE_LOOKUP_CODE) <> 'EMPLOYEE')
AND (PV.END_DATE_ACTIVE IS NULL OR PV.END_DATE_ACTIVE > SYSDATE)
AND (PVSA.INACTIVE_DATE IS NULL OR PVSA.INACTIVE_DATE > SYSDATE)
ORDER BY VENDOR_NAME