Tuesday, 3 November 2015

last Item wise Purchase order details Query

SELECT pha.segment1 po, pvv.vendor_name vendor_name, msi.segment1 item_code,
       pla.unit_price
  FROM po_headers_all pha,
       po_lines_all pla,
       po_vendors pvv,
       mtl_system_items msi
 WHERE pha.po_header_id = pla.po_header_id
   AND pha.vendor_id = pvv.vendor_id
   AND pla.item_id = msi.inventory_item_id
   AND msi.organization_id = :p_organization_id
   AND (TRUNC (pha.creation_date), pha.vendor_id, pla.item_id, pha.org_id) IN (
          SELECT *
            FROM (SELECT   MAX (TRUNC (ph.creation_date)) ct_date,
                           ph.vendor_id, pl.item_id, ph.org_id
                      FROM po_headers_all ph, po_vendors pv, po_lines_all pl
                     WHERE ph.vendor_id = pv.vendor_id
                       AND ph.po_header_id = pl.po_header_id
                       AND pl.item_id = :p_item_id
                       AND ph.org_id = :p_org_id
                  GROUP BY pl.item_id, ph.org_id, ph.vendor_id))

How to Link between RTN to Credit Memo in Payable

SELECT jat.dm_invoice_num
  FROM jai_ap_tds_invoices jat,
       ap_invoices_all aia,
       ap_invoice_distributions_all aida
 WHERE aia.invoice_id = aida.invoice_id
   AND jat.invoice_id = aia.invoice_id
   AND aida.invoice_distribution_id IN (
          SELECT aid.prepay_distribution_id
            FROM jai_ap_tds_thhold_trxs jtx, ap_invoice_distributions_all aid
           WHERE jtx.invoice_distribution_id = aid.invoice_distribution_id
             AND invoice_to_vendor_id = 516912) --- RTN Invoice ID