Query to find Purchase Order and Invoice Details
Oracle apps ebs Purchase Order and Invoice link
oracle purchase order query
oracle purchase order tables
SELECT a.org_id "ORG ID",
e.vendor_name "VENDOR NAME",
UPPER (e.vendor_type_lookup_code) "VENDOR TYPE",
f.vendor_site_code "VENDOR SITE",
f.address_line1 "ADDRESS",
f.city "CITY",
f.country "COUNTRY",
TO_CHAR (TRUNC (d.creation_date)) "PO DATE",
d.segment1 "PO NUMBER",
d.type_lookup_code "PO TYPE",
c.quantity_ordered "QTY ORDERED",
c.quantity_cancelled "QTY CANCALLED",
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 STATUS",
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,
h.amount,
i.check_number "CHEQUE NUMBER",
TO_CHAR (TRUNC (i.check_date)) "PAYMENT DATE"
FROM ap.ap_invoices_all a,
ap.ap_invoice_distributions_all b,
po.po_distributions_all c,
po.po_headers_all d,
po.po_vendors e,
po.po_vendor_sites_all f,
po.po_lines_all g,
ap.ap_invoice_payments_all h,
ap.ap_checks_all i
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 c.po_line_id = g.po_line_id
AND a.invoice_id = h.invoice_id
AND h.check_id = i.check_id
AND f.vendor_site_id = i.vendor_site_id
AND c.po_header_id IS NOT NULL
AND a.payment_status_flag = 'Y'
AND d.type_lookup_code != 'BLANKET'
Related posts:
- How to Retrieve Period to Date (PTD) and Year to Date (YTD) Balances of GL Account in Oracle R12
- Supervisor Hierarchy and Approval Limits in Oracle EBS R12
- Query to find all Expense report entry delegations
- Query to find vacation rules in Oracle R12
- Purchase Order Receipt details query in Oracle apps
- Oracle apps query to find approval limits
- View accounting query in oracle apps R12
- Query to get AR invoice SLA and GL details
- Accounting entries not transferred to the General Ledger
- Query to find all APIs of Oracle Apps modules
- R12 AR cash receipt SLA and GL details query
- Oracle apps AR Applications and SLA details
- Oracle EBS query to get AP Invoice SLA Details
- Oracle EBS query to get AR Invoice SLA Details
- Oracle AR Remit to address query
- Oracle Apps iExpense credit card details query
- Query to get customer tax registration details in R12
- Purchase Requisition Vertex debug xml query in Oracle EBS
- Purchase Order Vertex debug xml query in Oracle EBS
- AP Invoice Vertex debug xml query in Oracle EBS
- AR Invoice Vertex debug xml query in Oracle EBS
- Query to find AP invoice workflow item key in oracle apps
- Query to find serial number material transactions details
- Query to find period status for Inventory General Ledger Purchasing Payables Receivables Fixed Assets
- Oracle apps EBS sql to get Fixed Asset (FA) details with Net Book Value (NBV) and Depreciation
- Query to find Sales Orders that are shipped but not invoiced
- Query to get Customer Bank Account Details
- Query to get description of GL Code Combination
- Query to find Purchase Order and Invoice Details
- Query to find AP Invoices matched with receipts
- Query to find Supplier Bank Details
- Query to find Credit Memos on an AP invoice
- Query to find the AP invoices that are applied to multiple Purchase Orders
- AR Invoice grouping rule from RA_INTERFACE_LINES_ALL
- Query to find shipset and fulfilment set