Scribd
Upload
217 views

SQL Scripts

This document contains SQL queries that are selecting supplier data from various tables to import into another system. The queries are gathering information like supplier names, addresses, sites, tax IDs, and assigned ship-to and bill-to locations.

Uploaded by

Nitya Priya
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
217 views

SQL Scripts

This document contains SQL queries that are selecting supplier data from various tables to import into another system. The queries are gathering information like supplier names, addresses, sites, tax IDs, and assigned ship-to and bill-to locations.

Uploaded by

Nitya Priya
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 2

--XXPOZ_SUPPLIERS_INT

SELECT 'CREATE' import_action,


aps.vendor_name "Supplier Name",
aps.type_1099 "Tax Organization Type",
aps.vendor_type_lookup_code "Supplier Type",
'SPEND_AUTHORIZED' Business_Relationship,
apss.duns_number "D-U-N-S Number",
--"Taxpayer Country"
aps.num_1099 "Taxpayer ID",
aps.payment_method_lookup_code "Payment Method"
FROM AP_SUPPLIERS aps,
AP_SUPPLIER_SITES_ALL apss
WHERE aps.vendor_id = apss.vendor_id;
--XXPOZ_SUPPLIER_SITES_INT
select 'CREATE' Import_Action,
aps.vendor_name "Supplier Name",
--Procurement BU*
ptysite.party_site_name "Address Name",
apss.vendor_site_code "Supplier Site",
apss.rfq_only_site_flag "Sourcing only",
apss.purchasing_site_flag "Purchasing",
apss.pcard_site_flag "Procurement card",
apss.pay_site_flag "Pay",
apss.primary_pay_site_flag "Primary Pay",
apss.supplier_notif_method "Communication Method",
apss.email_address "E-Mail",
--Fax Country -Code
apss.fax_area_code "Fax Area Code",
apss.fax "Fax",
apss.default_pay_site_id "Default Pay Site",
apss.invoice_currency_code "Invoice Currency"
FROM AP_SUPPLIERS aps,
AP_SUPPLIER_SITES_ALL apss ,
HZ_PARTY_SITES ptysite
WHERE aps.vendor_id = apss.vendor_id
and apss.party_site_id = ptysite.party_site_id
and aps.party_id =ptysite.party_id
--XXPOZ_SUPPLIER_ADDRESSES_INT
SELECT 'CREATE' import_action,
aps.vendor_name "Supplier Name",
ptysite.party_site_name "Address Name",
loc.Country "Country",
loc.ADDRESS1 "Address Line 1",
loc.ADDRESS2 "Address Line 2",
loc.ADDRESS3 "Address Line 3",
loc.ADDRESS4 "Address Line 4",
loc.city "City",
loc.state "State",
loc.Province "Province",
loc.County "County",
loc.Postal_Code "Postal code",
hz.Primary_Phone_Country_Code "Phone Country Code",
hz.Primary_Phone_Area_Code "Phone Area Code",
apss.phone "Phone",
--RFQ Or Bidding (RFQ_OR_BIDDING_PURPOSE_FLAG)

--Ordering (ordering_purpose_flag)
--Pay (remit_to_purpose_flag)
hz.EMAIL_ADDRESS "E-Mail"
FROM AP_SUPPLIERS aps,
AP_SUPPLIER_SITES_ALL apss,
hz_parties hz,
hz_party_sites ptysite,
hz_locations loc
WHERE aps.vendor_id = apss.vendor_id
and apss.party_site_id = ptysite.party_site_id
and aps.party_id =ptysite.party_id
and hz.party_id = ptysite.party_id
and loc.location_id = ptysite.location_id;
--XXPOZ_SITE_ASSIGNMENTS_INT
select 'CREATE' import_action,
aps.vendor_name "Supplier Name",
apss.vendor_site_code "Supplier Site",
--"Procurement BU",
--"Client BU",
--"Bill-to BU",
ht1.location_code "Ship-to Location",
ht2.location_code "Bill-to Location"
FROM AP_SUPPLIERS aps,
AP_SUPPLIER_SITES_ALL apss
,HR_LOCATIONS ht1
,HR_LOCATIONS ht2
WHERE aps.vendor_id = apss.vendor_id
and apss.ship_to_location_id = ht1.location_id
and apss.bill_to_location_id = ht2.location_id ;

You might also like