This PO Supplier Sites query retrieves detailed information about suppliers and their associated supplier sites, including key payment terms and tax-related data. It helps in managing supplier relationships by providing insight into payment conditions and the tax structure tied to each supplier. This data is essential for financial operations, ensuring compliance and accurate payment processing.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query retrieves supplier and supplier site details including payment and tax-related information.
PSV.VENDOR_NAME AS SUPPLIER_NAME,
PSSAM.VENDOR_SITE_CODE AS SUPPLIER_SITE_NAME,
PSSAM.VENDOR_SITE_CODE_ALT AS ALTERNATE_SUPPLIER_SITE_NAME,
PSSAM.CREATION_DATE AS CREATED_DATE,
PSSAM.INACTIVE_DATE AS INACTIVE_DATE,
DECODE(PSSAM.PURCHASING_SITE_FLAG, 'N', 'No', 'Y', 'Yes', NULL) AS PURCHASING_SITE_FLAG,
DECODE(PSSAM.RFQ_ONLY_SITE_FLAG, 'N', 'No', 'Y', 'Yes', NULL) AS RFQ_ONLY_SITE_FLAG,
PSSAM.AREA_CODE AS AREA_CODE,
PSSAM.PHONE AS PHONE_NUMBER,
PSSAM.FAX AS FAX_NUMBER,
PSSAM.VAT_CODE AS INVOICE_TAX_NAME,
PSSAM.AP_TAX_ROUNDING_RULE AS TAX_ROUNDING_RULE,
DECODE(PSSAM.HOLD_ALL_PAYMENTS_FLAG, 'N', 'No', 'Y', 'Yes', NULL) AS HOLD_ALL_PAYMENTS,
DECODE(PSSAM.HOLD_FUTURE_PAYMENTS_FLAG, 'N', 'No', 'Y', 'Yes', NULL) AS HOLD_FUTURE_PAYMENTS,
DECODE(PSSAM.HOLD_UNMATCHED_INVOICES_FLAG, 'N', 'No', 'Y', 'Yes', NULL) AS HOLD_UNMATCHED_INVOICES,
PSSAM.HOLD_REASON AS HOLD_REASON,
DECODE(
PSSAM.PAY_GROUP_LOOKUP_CODE,
'ALAP',
'Alap',
'BOE',
'BOE',
'CTX ACH',
'CTX ACH Vendor',
'CZ GROUP',
'CZ Group',
'DOMESTIC',
'Domestic',
'DTA',
'DTA',
'ECS ACH',
'ECS ACH Vendor',
'ECS CK',
'ECS Check',
'EDI',
'EDI',
'EFT',
'EFT',
'EFT ITALY',
'Italian EFT Payments',
'EURO FOREIGN',
'EURO Foreign',
'EURO NATIONAL',
'National EFT EURO',
'Employee',
'Employee',
'FOREIGN',
'Foreign',
'GPS',
'GPS Pay Group for INSS',
'GR_PAY1',
'Local Suppliers after order',
'GR_PAY2',
'Local Suppliers without order',
'GR_PAY3',
'Foreign Suppliers',
'GR_PAY4',
'Employee - Greece',
'GR_PAY5',
'VAT',
'Government',
'Government',
'IA',
'Interagency',
'INLAND REVENUE',
'Inland Revenue',
'INTERCOMPANY',
'Intercompany',
'International',
'International',
'MISC',
'Supplier Misc',
'MULTI FOREIGN',
'Foreign EFT EURO',
'NO DOMESTIC',
'NO Domestic',
'NO EMPLOYEES',
'NO Employees',
'NO FOREIGN',
'NO Foreign',
'Priority',
'Priority',
'SAD',
'SAD',
'SIZZLE',
'Sizzle',
'SSC 1',
'SSC 1',
'SSC 2',
'SSC 2',
'Standard',
'Standard',
'Utilities',
'Utilities',
NULL
) AS PAY_GROUP,
FCV.NAME AS INVOICE_CURRENCY,
NVL(PSSAM.INVOICE_AMOUNT_LIMIT, 0) AS INVOICE_AMOUNT_LIMIT,
PSSAM.PAYMENT_PRIORITY AS PAYMENT_PRIORITY,
PSSAM.EMAIL_ADDRESS AS EMAIL_ADDRESS,
PSSAM.MATCH_OPTION AS MATCH_OPTION,
DECODE(PSSAM.CREATE_DEBIT_MEMO_FLAG, 'N', 'No', 'Y', 'Yes', NULL) AS CREATE_DEBIT_MEMO_FLAG
FROM
POZ_SUPPLIERS_V PSV,
POZ_SUPPLIER_SITES_ALL_M PSSAM,
FND_CURRENCIES_VL FCV,
HR_LOCATIONS HL,
AP_TERMS AT,
POZ_SUPPLIER_SITES_ALL_M PSSAM1
WHERE
PSSAM.VENDOR_ID = PSV.VENDOR_ID
AND PSSAM.PAYMENT_CURRENCY_CODE = FCV.CURRENCY_CODE(+)
AND PSSAM.TERMS_ID = AT.TERM_ID(+)
AND PSSAM.DEFAULT_PAY_SITE_ID = PSSAM1.VENDOR_SITE_ID(+)
AND PSSAM.LOCATION_ID = HL.LOCATION_ID(+)
;