How to Find the Invoices Related to PO Number?

SELECT DISTINCT
a.org_id "ORG ID", 
e.segment1 "VENDOR NUM",
e.vendor_name "SUPPLIER NAME",
UPPER (e.vendor_type_lookup_code) "VENDOR TYPE",
f.vendor_site_code "VENDOR SITE CODE", 
f.address_line1 "ADDRESS",
f.city "CITY", 
f.country "COUNTRY",
TO_CHAR (TRUNC (d.creation_date)) "PO DATE", 
d.segment1 "PO NUM",
d.type_lookup_code "PO TYPE", 
c.quantity_ordered "QTY ORDERED",
c.quantity_cancelled "QTY CANCELLED", 
g1.quantity_received, 
g1.quantity_rejected, 
g1.quantity_billed,
g.item_id "ITEM ID",
g.item_description "ITEM DESCRIPTION", 
g.unit_price "UNIT PRICE",
(NVL (c.quantity_ordered, 0) - NVL (c.quantity_cancelled, 0))* NVL (g.unit_price, 0) "PO LINE AMOUNT",
(SELECT DECODE (ph.approved_flag, 'Y', 'Approved')
FROM po.po_headers_all ph
WHERE ph.po_header_id = d.po_header_id) "PO APPROVED?",
a.invoice_type_lookup_code "INVOICE TYPE",
a.invoice_amount "INVOICE AMOUNT",
TO_CHAR (TRUNC (a.invoice_date)) "INVOICE DATE",
a.invoice_num "INVOICE NUMBER",
(SELECT DECODE (x.match_status_flag,
'A', 'Approved'
)
FROM ap.ap_invoice_distributions_all x
WHERE x.invoice_distribution_id = b.invoice_distribution_id)"INVOICE APPROVED?",
a.amount_paid
FROM 
ap.ap_invoices_all a,
ap.ap_invoice_distributions_all b,
po.po_distributions_all c,
po.po_headers_all d,
ap.ap_suppliers e,
ap.ap_supplier_sites_all f,
po.po_lines_all g,
po.po_line_locations_all g1
WHERE a.invoice_id = b.invoice_id
AND b.po_distribution_id = c.po_distribution_id(+)
AND c.po_header_id = d.po_header_id(+)
AND e.vendor_id(+) = d.vendor_id
AND f.vendor_site_id(+) = d.vendor_site_id
AND d.po_header_id = g.po_header_id
AND g1.po_header_id = c.po_header_id
AND g1.po_line_id = g.po_line_id
AND c.po_line_id = g.po_line_id
AND c.line_location_id = g1.line_location_id
AND g1.inspection_required_flag = 'N'
AND g1.receipt_required_flag = 'N'

2 comments:

  1. If you want to make money with a online business from home, you must be the type of person that is able to learn new things easily. No matter how much you know about the area your home business specializes in, you will still constantly need to learn new things in order to grow your business. If you are not the type of person that is willing to learn, your business is sure to fail. tax invoice template NZ

    ReplyDelete
  2. In so doing these properties increased their hotel profitability and investment returns by not having to offer low rates to attract gamers. situs slot online terbaik 2021

    ReplyDelete