Supplier
———
SELECT avv.VENDOR_NAME “Supplier Name”
, avv.VENDOR_NUMBER “Supplier Number”
, avv.VENDOR_NAME_ALT “Alternate Name”
, avv.NUM_1099 “Taxpayer ID”
, avv.VAT_REGISTRATION_NUM “Tax Registration Number”
, avv.END_DATE_ACTIVE “Inactive On”
, avv.PARENT_VENDOR_NAME “Parent Supplier Name”
, avv.PARENT_VENDOR_NUMBER “Number”
, avv.CUSTOMER_NUM “Customer Number”
, avv.VENDOR_TYPE_DISP “Type”
, ppf.FULL_NAME “Employee Name”
, ppf.EMPLOYEE_NUMBER “Employee Number”
, avv.ONE_TIME_FLAG “One Time”
, avv.STANDARD_INDUSTRY_CLASS “SIC”
, avv.MINORITY_GROUP_DISP “Minority Owned”
, avv.SMALL_BUSINESS_FLAG “Small Business”
, avv.WOMEN_OWNED_FLAG “Women Owned”
, avv.DISTRIBUTION_SET_NAME “Distribution Name”
, gcck_l.CONCATENATED_SEGMENTS “GL Account Liability”
, gcck_p.CONCATENATED_SEGMENTS “GL Account Prepayment”
, gcck_f.CONCATENATED_SEGMENTS “GL Account Future Dated Paym”
, avv.INVOICE_AMOUNT_LIMIT “Invoice Amount Limit”
, DECODE(avv.MATCH_OPTION
, ‘P’, ‘Purchase Order’
, ‘R’, ‘Receipt’ ) “Invoice Match Option”
, avv.HOLD_ALL_PAYMENTS_FLAG “Hold All Payments”
, avv.HOLD_UNMATCHED_INVOICES_FLAG “Hold Unmatched Invoices”
, avv.HOLD_FUTURE_PAYMENTS_FLAG “Hold Unvalidated Invoices”
, avv.HOLD_REASON “Payment Hold Reason”
, avv.TERMS_NAME “Terms”
, avv.PAY_GROUP_LOOKUP_CODE “Pay Group”
, avv.PAYMENT_PRIORITY “Payment Priority”
, avv.INVOICE_CURRENCY_CODE “Invoice Currency”
, avv.PAYMENT_CURRENCY_CODE “Payment Currency”
, avv.TERMS_DATE_BASIS “Terms Date Basis”
, plc_pd.DISPLAYED_FIELD “Pay Date Basis”
, flvv_pm.MEANING “Payment Method”
, avv.ALWAYS_TAKE_DISC_FLAG “Always Take Discount”
, avv.EXCLUDE_FREIGHT_FROM_DISCOUNT “Exlcude Freight From Discount”
, avv.AUTO_CALCULATE_INTEREST_FLAG “Allow Interest Invoices”
, avv.EXCLUSIVE_PAYMENT_FLAG “Pay Alone”
, avv.EDI_PAYMENT_METHOD “Payment Method”
, avv.EDI_PAYMENT_FORMAT “Payment Format”
, avv.EDI_REMITTANCE_METHOD “Remittance Method”
, avv.EDI_REMITTANCE_INSTRUCTION “Remittance Instructions”
, avv.EDI_TRANSACTION_HANDLING “Transaction Handling”
, avv.VAT_CODE “Invoice Tax Code”
, avv.OFFSET_TAX_FLAG “Use Offset Taxes”
, flvv_tc.MEANING “Calculation Level”
, avv.AUTO_TAX_CALC_OVERRIDE “Allow Calculation Level Overri”
, flvv_tr.MEANING “Rounding Rule”
, avv.AMOUNT_INCLUDES_TAX_FLAG “Distribution Amounts Include T”
, avv.ALLOW_AWT_FLAG “Allow Withholding Tax”
, avv.AWT_GROUP_NAME “Withholding Tax Group”
, avv.FEDERAL_REPORTABLE_FLAG “Federal Reportable”
, avv.TYPE_1099 “Income Tax Type”
, avv.STATE_REPORTABLE_FLAG “State Reportable”
, avv.TAX_REPORTING_NAME “Reporting Name”
, avv.TAX_VERIFICATION_DATE “Verification Date”
, avv.NAME_CONTROL “Name Control”
, avv.ORGANIZATION_TYPE_DISP “Organization Type”
, avv.SHIP_TO_LOCATION_CODE “Ship-To Location”
, avv.BILL_TO_LOCATION_CODE “Bill-To Location”
, avv.SHIP_VIA_LOOKUP_CODE “Ship Via”
, avv.FOB_DISP “FOB”
, avv.FREIGHT_TERMS_DISP “Freight Terms”
, NVL(avv.CREATE_DEBIT_MEMO_FLAG
, ‘N’ ) “Create Debit Memo from RTS Trx”
, avv.HOLD_FLAG “Purchase Order Hold”
, avv.PURCHASING_HOLD_REASON “Purchasing Hold Reason”
, avv.ENFORCE_SHIP_TO_LOCATION_DISP “Enforce Ship-To Location”
, avv.RECEIVING_ROUTING_NAME “Receipt Routing”
, DECODE(avv.INVOICE_MATCHING_FLAG
, ‘NN’, ‘2-Way’
, ‘NY’, ‘3-Way’
, ‘YY’, ‘4-Way’ ) “Match Approval Level”
, avv.QTY_RCV_TOLERANCE “Qty Received Tolerance %”
, avv.QTY_RCV_EXCEPTION_DISP “Qty Received Exception”
, avv.DAYS_EARLY_RECEIPT_ALLOWED “Days Early Receipt Allowed”
, avv.DAYS_LATE_RECEIPT_ALLOWED “Days Late Receipt Allowed”
, avv.ALLOW_SUBSTITUTE_RECEIPTS_FLAG “Allow Substitute Receipts”
, avv.ALLOW_UNORDERED_RECEIPTS_FLAG “Allow Unordered Receipts”
, avv.RECEIPT_DAYS_EXCEPTION_DISP “Receipt Date Exception”
, ” “[Sites]”
, haou.NAME “Operating Unit”
, pvsa.VENDOR_SITE_CODE “Site Name”
, pvsa.VENDOR_SITE_CODE_ALT “Alternate Name”
, ftv.TERRITORY_SHORT_NAME “Country”
, pvsa.ADDRESS_LINE1 “Address Line 1”
, pvsa.ADDRESS_LINE2 “Address Line 2”
, pvsa.ADDRESS_LINE3 “Address Line 3”
, pvsa.ADDRESS_LINE4 “Address Line 4”
, pvsa.ADDRESS_LINES_ALT “Alternate Address”
, pvsa.CITY “City”
, pvsa.STATE “State”
, pvsa.ZIP “Postal Code”
, pvsa.PROVINCE “Procince”
, pvsa.COUNTY “County”
, flv.DESCRIPTION “Language”
, pvsa.INACTIVE_DATE “Inactive Date”
, NVL(pvsa.PAY_SITE_FLAG, ‘N’) “Pay”
, NVL(pvsa.PRIMARY_PAY_SITE_FLAG, ‘N’) “Primary Pay”
, NVL(pvsa.PURCHASING_SITE_FLAG, ‘N’) “Purchasing”
, pvsa.RFQ_ONLY_SITE_FLAG “RFQ Only”
, pvsa.PCARD_SITE_FLAG “Procurement Card”
, pvsa.CUSTOMER_NUM “Customer Number”
, (SELECT hla.LOCATION_CODE
FROM PO_LOCATION_ASSOCIATIONS_ALL plaa
, HR_LOCATIONS_ALL hla
WHERE pvsa.VENDOR_ID = plaa.VENDOR_ID
AND pvsa.VENDOR_SITE_ID = plaa.VENDOR_SITE_ID
AND plaa.LOCATION_ID = hla.LOCATION_ID
) “Shipping Network Location”
, DECODE(pvsa.SUPPLIER_NOTIF_METHOD
, ‘PRINT’, ‘Printed Document’
, ‘FAX’ , ‘Fax’
, ‘EMAIL’, ‘E-mail’
) “Supplier Notification Method”
, pvsa.AREA_CODE “Voice Area Code”
, pvsa.PHONE “Voice Number”
, pvsa.FAX_AREA_CODE “Fax Area Code”
, pvsa.FAX “Fax Number”
, pvsa.TELEX “Telex”
, pvsa.EMAIL_ADDRESS “E-mail”
, adsa.DISTRIBUTION_SET_NAME “Distribution Set”
, gcck_ls.CONCATENATED_SEGMENTS “GL Account Liability”
, gcck_ps.CONCATENATED_SEGMENTS “GL Account Prepayment”
, gcck_fs.CONCATENATED_SEGMENTS “GL Account Future Dated Paym”
, att.TOLERANCE_NAME “Invoice Tolerance”
, pvsa.INVOICE_AMOUNT_LIMIT “Invoice Amount Limit”
, DECODE(pvsa.MATCH_OPTION
, ‘P’, ‘Purchase Order’
, ‘R’, ‘Receipt’ ) “Invoice Match Option”
, pvsa.HOLD_ALL_PAYMENTS_FLAG “Hold All Payments”
, pvsa.HOLD_UNMATCHED_INVOICES_FLAG “Hold Unmatched Invoices”
, pvsa.HOLD_FUTURE_PAYMENTS_FLAG “Hold Unvalidated Payments”
, pvsa.HOLD_REASON “Hold Reason”
, at.NAME “Terms”
, pvsa.PAY_GROUP_LOOKUP_CODE “Pay Group”
, pvsa.PAYMENT_PRIORITY “Payment Priority”
, pvsa.REMITTANCE_EMAIL “Remittance E-mail”
, pvsa.INVOICE_CURRENCY_CODE “Invoice Currency Code”
, pvsa.PAYMENT_CURRENCY_CODE “Payment Currency Code”
, pvsa.TERMS_DATE_BASIS “Terms Date Basis”
, plc_pds.DISPLAYED_FIELD “Pay Date Basis”
, flvv_pms.MEANING “Payment Method”
, pvsa.ALWAYS_TAKE_DISC_FLAG “Always Take Discount”
, pvsa.EXCLUDE_FREIGHT_FROM_DISCOUNT “Exlcude Freight From Discount”
, pvsa.EXCLUSIVE_PAYMENT_FLAG “Pay Alone”
, pvsa.ATTENTION_AR_FLAG “Attention AR”
, pvsa.ECE_TP_LOCATION_CODE “EDI Location”
, pvsa.EDI_ID_NUMBER “EDI ID Number”
, pvsa.EDI_PAYMENT_METHOD “Payment Method”
, pvsa.EDI_PAYMENT_FORMAT “Payment Format”
, pvsa.EDI_REMITTANCE_METHOD “Remittance Method”
, pvsa.EDI_REMITTANCE_INSTRUCTION “Remittance Instructions”
, pvsa.EDI_TRANSACTION_HANDLING “Transaction Handling”
, pvsa.VAT_CODE “Invoice Tax Code”
, pvsa.OFFSET_TAX_FLAG “Use Offset Taxes”
, flvv_tcs.MEANING “Calculation Level”
, pvsa.AUTO_TAX_CALC_OVERRIDE “Allow Calculation Level Overri”
, flvv_trs.MEANING “Rounding Rule”
, pvsa.AMOUNT_INCLUDES_TAX_FLAG “Distribution Amounts Include T”
, pvsa.ALLOW_AWT_FLAG “Allow Withholding Tax”
, aag.NAME “Withholding Tax Group”
, pvsa.VAT_REGISTRATION_NUM “Tax Registration Number”
, pvsa.TAX_REPORTING_SITE_FLAG “Income Tax Reporting Site”
, hla_s.LOCATION_CODE “Ship-To Location”
, hla_b.LOCATION_CODE “Bill-To Location”
, pvsa.SHIP_VIA_LOOKUP_CODE “Ship Via”
, plc_fob.DISPLAYED_FIELD “FOB”
, plc_t.DISPLAYED_FIELD “Freight Terms”
, pvsa.SHIPPING_CONTROL “Transportation Arranged”
, ftv_o.TERRITORY_SHORT_NAME “Country of Origin”
, plc_po.DISPLAYED_FIELD “Pay On”
, pvsa_a.VENDOR_SITE_CODE “Alternate Pay Site”
, plc_por.DISPLAYED_FIELD “Invoice Summary Level”
, NVL(pvsa.CREATE_DEBIT_MEMO_FLAG
, ‘N’ ) “Create Debit Memo from RTS Trx”
, NVL(pvsa.GAPLESS_INV_NUM_FLAG, ‘N’) “Gapless Invoice Numbering”
, pvsa.SELLING_COMPANY_IDENTIFIER “Selling Company Identifier”
FROM AP_VENDORS_V avv
, PER_PEOPLE_F ppf
, GL_CODE_COMBINATIONS_KFV gcck_l
, GL_CODE_COMBINATIONS_KFV gcck_p
, GL_CODE_COMBINATIONS_KFV gcck_f
, PO_LOOKUP_CODES plc_pd
, FND_LOOKUP_VALUES_VL flvv_pm
, FND_LOOKUP_VALUES_VL flvv_tc
, FND_LOOKUP_VALUES_VL flvv_tr
, PO_VENDOR_SITES_ALL pvsa
, HR_ALL_ORGANIZATION_UNITS haou
, FND_TERRITORIES_VL ftv
, FND_LANGUAGES_VL flv
, AP_DISTRIBUTION_SETS_ALL adsa
, GL_CODE_COMBINATIONS_KFV gcck_ls
, GL_CODE_COMBINATIONS_KFV gcck_ps
, GL_CODE_COMBINATIONS_KFV gcck_fs
, AP_TOLERANCE_TEMPLATES att
, AP_TERMS at
, PO_LOOKUP_CODES plc_pds
, FND_LOOKUP_VALUES_VL flvv_pms
, FND_LOOKUP_VALUES_VL flvv_tcs
, FND_LOOKUP_VALUES_VL flvv_trs
, AP_AWT_GROUPS aag
, HR_LOCATIONS_ALL hla_s
, HR_LOCATIONS_ALL hla_b
, PO_LOOKUP_CODES plc_fob
, PO_LOOKUP_CODES plc_t
, FND_TERRITORIES_VL ftv_o
, PO_LOOKUP_CODES plc_po
, PO_VENDOR_SITES_ALL pvsa_a
, PO_LOOKUP_CODES plc_por
WHERE avv.EMPLOYEE_ID = ppf.PERSON_ID (+)
AND avv.ACCTS_PAY_CCID = gcck_l.CODE_COMBINATION_ID (+)
AND avv.PREPAY_CCID = gcck_p.CODE_COMBINATION_ID (+)
AND avv.FUTURE_DATED_PAYMENT_CCID = gcck_f.CODE_COMBINATION_ID (+)
AND avv.PAY_DATE_BASIS_LOOKUP_CODE = plc_pd.LOOKUP_CODE (+)
AND avv.PAYMENT_METHOD_LOOKUP_CODE = flvv_pm.LOOKUP_CODE (+)
AND avv.AUTO_TAX_CALC_FLAG = flvv_tc.LOOKUP_CODE (+)
AND avv.AP_TAX_ROUNDING_RULE = flvv_tr.LOOKUP_CODE (+)
AND avv.VENDOR_ID = pvsa.VENDOR_ID (+)
AND pvsa.ORG_ID = haou.ORGANIZATION_ID (+)
AND pvsa.COUNTRY = ftv.TERRITORY_CODE (+)
AND pvsa.LANGUAGE = flv.NLS_LANGUAGE (+)
AND pvsa.DISTRIBUTION_SET_ID = adsa.DISTRIBUTION_SET_ID (+)
AND pvsa.ACCTS_PAY_CODE_COMBINATION_ID = gcck_ls.CODE_COMBINATION_ID (+)
AND pvsa.PREPAY_CODE_COMBINATION_ID = gcck_ps.CODE_COMBINATION_ID (+)
AND pvsa.FUTURE_DATED_PAYMENT_CCID = gcck_fs.CODE_COMBINATION_ID (+)
AND pvsa.TOLERANCE_ID = att.TOLERANCE_ID (+)
AND pvsa.TERMS_ID = at.TERM_ID (+)
AND pvsa.PAY_DATE_BASIS_LOOKUP_CODE = plc_pds.LOOKUP_CODE (+)
AND pvsa.PAYMENT_METHOD_LOOKUP_CODE = flvv_pms.LOOKUP_CODE (+)
AND pvsa.AUTO_TAX_CALC_FLAG = flvv_tcs.LOOKUP_CODE (+)
AND pvsa.AP_TAX_ROUNDING_RULE = flvv_trs.LOOKUP_CODE (+)
AND pvsa.AWT_GROUP_ID = aag.GROUP_ID (+)
AND pvsa.SHIP_TO_LOCATION_ID = hla_s.LOCATION_ID (+)
AND pvsa.BILL_TO_LOCATION_ID = hla_b.LOCATION_ID (+)
AND pvsa.FOB_LOOKUP_CODE = plc_fob.LOOKUP_CODE (+)
AND pvsa.FREIGHT_TERMS_LOOKUP_CODE = plc_t.LOOKUP_CODE (+)
AND pvsa.COUNTRY_OF_ORIGIN_CODE = ftv_o.TERRITORY_CODE (+)
AND pvsa.PAY_ON_CODE = plc_po.LOOKUP_CODE (+)
AND pvsa.DEFAULT_PAY_SITE_ID = pvsa_a.VENDOR_SITE_ID (+)
AND pvsa.PAY_ON_RECEIPT_SUMMARY_CODE = plc_por.LOOKUP_CODE (+)
AND plc_pd.LOOKUP_TYPE (+) = ‘PAY DATE BASIS’
AND flvv_pm.LOOKUP_TYPE (+) = ‘PAYMENT METHOD’
AND flvv_tc.LOOKUP_TYPE (+) = ‘AP_TAX_CALCULATION_METHOD’
AND flvv_tr.LOOKUP_TYPE (+) = ‘AP_TAX_ROUNDING_RULE’
AND plc_pds.LOOKUP_TYPE (+) = ‘PAY DATE BASIS’
AND flvv_pms.LOOKUP_TYPE (+) = ‘PAYMENT METHOD’
AND flvv_tcs.LOOKUP_TYPE (+) = ‘AP_TAX_CALCULATION_METHOD’
AND flvv_trs.LOOKUP_TYPE (+) = ‘AP_TAX_ROUNDING_RULE’
AND plc_fob.LOOKUP_TYPE (+) = ‘FOB’
AND plc_t.LOOKUP_TYPE (+) = ‘FREIGHT TERMS’
AND plc_po.LOOKUP_TYPE (+) = ‘ERS PAY_ON_CODE’
AND plc_por.LOOKUP_TYPE (+) = ‘ERS INVOICE_SUMMARY’
ORDER BY avv.VENDOR_NAME
, avv.VENDOR_NUMBER
, haou.NAME
, pvsa.VENDOR_SITE_CODE
OraFUN 