AP Active Suppliers Report (SQL Script)

Enterprise Resource Planning (ERP) Account Payables (AP)
1

AP Active Suppliers Report Provides a comprehensive view of active supplier master data, including supplier names, sites, contact details, tax registration info.

#sqlquery

SELECT
   --SQL4Fusion (An Orbit Analytics Project)
   --This query retrieves detailed information about active suppliers
  PSV.VENDOR_NAME SUPPLIER_NAME,
  PSV.VENDOR_NAME_ALT ALTERNATE_SUPPLIER_NAME,
  PSV.SEGMENT1 SUPPLIER_NUMBER,
  PSSM.VENDOR_SITE_CODE SUPPLIER_SITE_CODE,
  PSA.ADDRESS1 ADDRESS,
  PSA.CITY ,
  PSA.COUNTRY ,
  PSCV.PHONE,
  PSCV.LAST_NAME,
  PSCV.FIRST_NAME,
  PSCV.MIDDLE_NAME,
  PSCV.EMAIL_ADDRESS,
  PSV.START_DATE_ACTIVE ,
  PSV.END_DATE_ACTIVE ,
  PSSM.VAT_REGISTRATION_NUM,
  PSV.STANDARD_INDUSTRY_CLASS ,
  PSV.ONE_TIME_FLAG ,
  PSV.MINORITY_GROUP_LOOKUP_CODE MINORITY_GROUP,
  PSV.SMALL_BUSINESS_FLAG ,
  PSV.WOMEN_OWNED_FLAG ,
  PSV.CUSTOMER_NUM CUSTOMER_NUMBER,
  PSV.FEDERAL_REPORTABLE_FLAG ,
  PSV.TYPE_1099 ,
  PSV.STATE_REPORTABLE_FLAG ,
  PSV.TAX_REPORTING_NAME,
  PSV.TAX_VERIFICATION_DATE ,
  HAOU.NAME ORGANIZATION_NAME,
  HAOU.ORGANIZATION_ID
 FROM
  POZ_SUPPLIERS_V PSV,
  POZ_SUPPLIER_SITES_ALL_M PSSM,
  POZ_SUPPLIER_CONTACTS_V PSCV,
  POZ_SUPPLIER_ADDRESS_V PSA,
  HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
  1 = 1
  AND PSV.VENDOR_ID = PSSM.VENDOR_ID
  AND PSA.VENDOR_ID = PSV.VENDOR_ID
  AND PSSM.VENDOR_SITE_ID = PSCV.VENDOR_SITE_ID(+)  
  AND PSSM.PRC_BU_ID = HAOU.ORGANIZATION_ID 
  AND PSV.ENABLED_FLAG = 'Y'  
  AND (PSV.END_DATE_ACTIVE IS NULL OR PSV.END_DATE_ACTIVE > SYSDATE);