1 Reply Latest reply on Nov 8, 2017 12:25 PM by John_K

    EBS SQL Query

    Moatasem Shebl

      Hello All,

       

      Can you help me how to make that query select the PO_Number and PO_Header also as it just give me data without PO_Number and PO_Header_ID

       

       

      select i.invoice_num,v.vendor_name,i.invoice_date,ps.due_date,

      i.invoice_amount,i.amount_paid,ps.amount_remaining, SUM(i.invoice_amount),sum(ps.amount_remaining)

      FROM    ap_payment_schedules_all ps,

              ap_invoices_all i,

              po_vendors v,

              po_vendor_sites_all vs

      WHERE   i.invoice_id = ps.invoice_id

      AND     i.vendor_id = v.vendor_id

      AND     i.vendor_site_id = vs.vendor_site_id

      group by v.vendor_name,i.invoice_num,i.invoice_date,ps.due_date,i.invoice_amount,i.amount_paid,

      ps.amount_remaining

      Order by v.vendor_name,i.invoice_num)

       

      Thank You

        • 1. Re: EBS SQL Query
          John_K

          Your query isn't at that level - you would need to go to your invoice lines. You might have an invoice that covers multiple PO's too. Plus you'll of course only get results for matched invoices. Something like this:

           

          select i.invoice_num,
                 v.vendor_name,
                 i.invoice_date,
                 ps.due_date,
                 i.invoice_amount,
                 i.amount_paid,
                 ps.amount_remaining,
                 p.po_header_id,
                 p.segment1
            from ap_payment_schedules_all ps,
                 ap_invoices_all i,
                 ap_invoice_lines_all l,
                 po_vendors v,
                 po_vendor_sites_all vs,
                 po_headers_all p
           where i.invoice_id = ps.invoice_id
             and i.vendor_id = v.vendor_id
             and i.vendor_site_id = vs.vendor_site_id
             and l.invoice_id = i.invoice_id
             and p.po_header_id = l.po_header_id
             and i.invoice_id = :invid
          
          1 person found this helpful