Follow us: Subscribe via RSS Feed Connect on YouTube Connect on YouTube

Pages

Sunday, 5 February 2017

Project Accounting and Purchasing Order Detail Query

SELECT   poh.segment1 "PO Number",
         TO_CHAR (poh.creation_date, 'DD-MON-YYYY') "PO Creation Date",
         pv.vendor_name "Supplier Name", pol.line_num "Line Number",
         SUM (pol.quantity * pol.unit_price) "PO Amount", poh.po_header_id,
         aia.invoice_num, aia.invoice_amount "Invoice Amount",
         aia.amount_paid "Paid Amount",
         ((aia.invoice_amount) - (aia.amount_paid)) "Amount Remaining",
         ppa.segment1 "Project Number", ppa.NAME "Project Name"
    FROM po_headers_all poh,
         po_lines_all pol,
         po_distributions_all pd,
         po_vendors pv,
         ap_invoice_distributions_all aid,
         ap_invoices_all aia,
         pa_projects_all ppa        
   WHERE poh.po_header_id = pol.po_header_id
     --AND poh.segment1='4329985'
     AND poh.po_header_id = pd.po_header_id
     AND pol.po_line_id = pd.po_line_id
     AND poh.vendor_id = pv.vendor_id
     AND pd.po_distribution_id = aid.po_distribution_id
     AND aia.invoice_id = aid.invoice_id
     AND ppa.project_id = aid.project_id
     AND ppa.project_id = pd.project_id
--   AND ppa.segment1 = '182222'
GROUP BY poh.segment1,
         poh.creation_date,
         pv.vendor_name,
         poh.po_header_id,
         aia.invoice_amount,
         aia.amount_paid,
         ppa.segment1,
         ppa.NAME,
         aia.invoice_num,
         pol.line_num
Share this article :

0 comments:

Post a Comment