This AP Supplier Site Listing report provides comprehensive details about supplier sites, including contact information, address details.
#sqlquery
SELECT
-- SQL4Fusion (An Orbit Analytics Project)
-- This query gathers detailed information about supplier sites.
HAOU.NAME ORGANIZATION_NAME,
PSV.VENDOR_NAME SUPPLIER_NAME,
PSV.VENDOR_TYPE_LOOKUP_CODE SUPPLIER_TYPE,
(
PSSV.VENDOR_SITE_CODE || ' - ' || PSSV.CITY || ' - ' || PSSV.ZIP
) SUPPLIER_SITE_CODE,
PSSV.ADDRESS_LINE1 ADDRESS1,
PSSV.ADDRESS_LINE2 ADDRESS2,
PSSV.ADDRESS_LINE3 ADDRESS3,
PSSV.CREATION_DATE SITE_CREATION_DATE,
PU.USERNAME SITE_CREATED_BY,
PU2.USERNAME SITE_UPDATED_BY,
PSSV.CITY,
SUBSTR (DECODE (PSSV.STATE,NULL,PSSV.PROVINCE || ', ', PSSV.STATE || ', ') || DECODE (PSSV.ZIP,NULL,NULL,PSSV.ZIP || ', ') || PSSV.COUNTRY,1,35 ) STATE_ZIP_COUNTRY,
NVL (PSSV.STATE,PSSV.PROVINCE) STATE,
PSSV.ZIP,
PSSV.COUNTRY COUNTRY_CODE,
SUBSTR (DECODE (PSSV.AREA_CODE,NULL,NULL,PSSV.AREA_CODE || ' ' ) || PSSV.PHONE,1,20 ) SITE_TELEPHONE,
SUBSTR (DECODE (PSSV.FAX_AREA_CODE,NULL,NULL,PSSV.FAX_AREA_CODE || ' ' ) || PSSV.FAX,1,19) SITE_FAX,
AT.NAME PAYMENT_TERMS,
PSSV.PAYMENT_PRIORITY,
DECODE (PSSV.PAY_SITE_FLAG,'Y','Pay' || ',', NULL ) || DECODE (PSSV.RFQ_ONLY_SITE_FLAG,'Y','RFQ Only' || ',',NULL ) || DECODE (PSSV.PURCHASING_SITE_FLAG,'Y','Purchasing' || ',', NULL
) || DECODE (
PSSV.PCARD_SITE_FLAG,
'Y',
'Procurement Card' || ',',
NULL
) SITE_USES,
PSSV.HOLD_UNMATCHED_INVOICES_FLAG,
PSSV.HOLD_FUTURE_PAYMENTS_FLAG,
PSSV.HOLD_ALL_PAYMENTS_FLAG,
PSSV.INACTIVE_DATE,
NVL2 (
PSSV.inactive_date,
'Inactive',
'Active'
) site_status,
PSSV.PAYMENT_CURRENCY_CODE,
PSSV.PAY_GROUP_LOOKUP_CODE PAY_GROUP_CODE,
UPPER (PSCV.LAST_NAME) || UPPER (PSCV.FIRST_NAME) || UPPER (PSCV.MIDDLE_NAME) SORT_CONTACT_NAME,
SUBSTR(DECODE (PSCV.FIRST_NAME,NULL, NULL,PSCV.FIRST_NAME || ' ' ) || DECODE (PSCV.MIDDLE_NAME, NULL,
NULL,
PSCV.MIDDLE_NAME || ' '
) || PSCV.LAST_NAME,
1,
20
) CONTACT_NAME,
PSCV.TITLE POSITION,
SUBSTR (DECODE(PSCV.AREA_CODE,NULL,NULL,'(' || PSCV.AREA_CODE || ') ' ) || PSCV.PHONE,1,20) CONTACT_TELEPHONE,
PSCV.CREATED_BY CONTACT_CREATED_BY
FROM
POZ_SUPPLIER_SITES_V PSSV,
POZ_SUPPLIERS_V PSV,
AP_TERMS AT,
POZ_SUPPLIER_CONTACTS_V PSCV,
PER_USERS PU,
PER_USERS PU2,
HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
1 = 1
AND PSSV.VENDOR_SITE_ID = PSCV.VENDOR_SITE_ID(+)
AND PSV.VENDOR_ID = PSSV.VENDOR_ID
AND PSSV.TERMS_ID = AT.TERM_ID(+)
AND PSSV.CREATED_BY = PU.USERNAME(+)
AND PSSV.LAST_UPDATED_BY = PU2.USERNAME(+)
AND PSSV.PRC_BU_ID = HAOU.ORGANIZATION_ID