Tuesday, 13 January 2015

Query to find supplier info :

SELECT   pov.vendor_id,
           pov.vendor_name supplier,
           pov.vendor_type_lookup_code,
           sl.location_code shipto_location,
           bl.location_code billto_location,
           pov.customer_num,
           pov.ship_via_lookup_code,
           pov.fob_lookup_code,
           rt.name terms,
           pov.set_of_books_id,
           pov.credit_status_lookup_code,
           pov.credit_limit
    FROM   ra_terms rt,
           hr_locations bl,
           hr_locations sl,
           po_vendors pov
   WHERE       pov.vendor_name LIKE 'Abb%'
           AND pov.ship_to_location_id = sl.location_id(+)
           AND pov.bill_to_location_id = bl.location_id(+)
           AND pov.terms_id = rt.term_id(+)
ORDER BY   1
/

Query to find Supplier sites :

SELECT   pov.vendor_name Supplier,
           povs.vendor_site_id,
           povs.vendor_site_code Site,
           povs.address_line1 A1ddress,
           povs.address_line2 A2ddress,
           povs.address_line3 A3ddress,
           povs.city || ', ' || povs.state || ' ' || povs.zip A4ddress,
           povs.ship_to_location_id,
           povs.bill_to_location_id,
           povs.ship_via_lookup_code,
           povs.freight_terms_lookup_code,
           povs.fob_lookup_code
    FROM   po_vendors pov, po_vendor_sites povs
   WHERE   pov.vendor_id = 601 AND pov.vendor_id = povs.vendor_id
ORDER BY   1

Query to find Supplier contacts :

  SELECT   vc.vendor_contact_id,
           vc.vendor_site_id,
           vc.first_name,
           vc.middle_name,
           vc.last_name,
           vc.prefix,
           vc.title,
           vc.mail_stop,
           vc.area_code,
           vc.phone,
           vc.department,
           vc.email_address,
           vc.url,
           vc.alt_area_code,
           vc.alt_phone,
           vc.fax_area_code,
           vc.inactive_date,
           vc.fax
    FROM   po_vendor_contacts vc
   WHERE   vc.vendor_site_id = 4556
ORDER BY   1

1 comment:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, BI Publisher, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete