lunes, 24 de noviembre de 2014

Query de Sucursales de Proveedores

Los siguientes scripts son  para obtener las sucursales activas de cierto proveedor tanto para la versión de EBS 11i y R12.

------------11i----------------------

SELECT haou.name                  organizacion,
       pv.vendor_name             proveedor,
       pv.segment1                num_proveedor,
       pv.vendor_type_lookup_code tipo,
       pvsa.vendor_site_code      sucursal,
       pvsa.invoice_currency_code divisa_factura,
       pvsa.payment_currency_code divisa_pago

  FROM po.po_vendors                pv,
       po.po_vendor_sites_all       pvsa,
       hr.hr_all_organization_units haou
 WHERE pv.vendor_id = pvsa.vendor_id
   AND pvsa.org_id = haou.organization_id
   AND pvsa.inactive_date IS NULL
   AND pv.end_date_active IS NULL
   AND pv.segment1 = '&numero_prov'
 ORDER BY haou.name, pv.vendor_name, pvsa.vendor_site_code;

------------R12----------------------

SELECT haou.name                  organizacion,
       pv.vendor_name             proveedor,
       pv.segment1                num_proveedor,
       pv.vendor_type_lookup_code tipo,
       pvsa.vendor_site_code      sucursal,
       pvsa.invoice_currency_code divisa_factura,
       pvsa.payment_currency_code divisa_pago

  FROM ap.ap_suppliers              pv,
       ap.ap_supplier_sites_all     pvsa,
       hr.hr_all_organization_units haou
 WHERE pv.vendor_id = pvsa.vendor_id
   AND pvsa.org_id = haou.organization_id
   AND pvsa.inactive_date IS NULL
   AND pv.end_date_active IS NULL
   AND pv.segment1 = '&numero_prov'


 ORDER BY haou.name, pv.vendor_name, pvsa.vendor_site_code;

2 comentarios:

  1. Al parecer cuenta con un pequeño error, agrego corrección, Saludos:


    SELECT haou.name organizacion,
    pv.vendor_name proveedor,
    pv.segment1 num_proveedor,
    pv.vendor_type_lookup_code tipo,
    pvsa.vendor_site_code sucursal,
    pvsa.invoice_currency_code divisa_factura,
    pvsa.payment_currency_code divisa_pago

    FROM ap.ap_suppliers pv,
    ap.ap_supplier_sites_all pvsa, hr_all_organization_units haou
    WHERE pv.vendor_id = pvsa.vendor_id
    AND pvsa.org_id = haou.organization_id
    AND pvsa.inactive_date IS NULL
    AND pv.end_date_active IS NULL
    AND pv.segment1 = '&numero_prov'

    ORDER BY haou.name, pv.vendor_name, pvsa.vendor_site_code;

    ResponderEliminar