Wednesday, 9 November 2011

How to Find Suppliers or Vendors in Oracel Apps a Single Query or View

  SELECT   ap.segment1 vendor_code,
           ap.vendor_name Suppliers_Name,
           aps.vendor_site_code Supplier_Site_Code,
           ap.VENDOR_TYPE_LOOKUP_CODE Type_Code,
           aps.address_line1,
           aps.address_line2,
           aps.address_line3,
           aps.city,
           aps.state,
           aps.zip,
           aps.Country,
           apc.AREA_CODE,
           apc.PHONE,
           apc.FAX_AREA_CODE,
           apc.FAX,
           apc.EMAIL_ADDRESS,
           ap.PAYMENT_CURRENCY_CODE CURRENCY_CODE,
           jat.PAN_NO,
           jcv.ST_REG_NO,
           jcv.CST_REG_NO,
           jcv.VAT_REG_NO,
           jcv.SERVICE_TAX_REGNO
    FROM   ap_suppliers ap,
           ap_supplier_sites_all aps,
           AP_SUPPLIER_CONTACTS apc,
           JAI_CMN_VENDOR_SITES jcv,
           JAI_AP_TDS_vendor_hdrs jat
   WHERE       ap.vendor_id = aps.vendor_id
           AND apc.VENDOR_CONTACT_ID(+) = ap.VENDOR_ID
           AND ap.VENDOR_TYPE_LOOKUP_CODE <>  'EMPLOYEE'
           AND aps.vendor_site_code NOT IN 'OFFICE'
           AND (aps.VENDOR_ID = jcv.VENDOR_ID(+)
                AND aps.VENDOR_SITE_ID = jcv.VENDOR_SITE_ID(+))
           AND (aps.VENDOR_ID = jat.VENDOR_ID(+)
                AND aps.VENDOR_SITE_ID = jat.VENDOR_SITE_ID(+))
ORDER BY   ap.SEGMENT1, ap.vendor_name

No comments:

Post a Comment