Query to find AP Invoices matched with receipts
Oracle apps ebs AP Invoices matched with receipts
SELECT 'Receipt' matching,
pv.vendor_name,
pv.segment1 vendor#,
pvs.vendor_site_code vendor_site,
i.invoice_num,
i.invoice_date,
d.accounting_date,
i.invoice_amount,
i.amount_paid,
d.distribution_line_number,
h.receipt_num,
t.transaction_date,
h.shipment_num,
ph.segment1 po#,
pl.line_num po_line_num,
d.line_type_lookup_code,
d.description,
pl.item_description,
d.amount line_amount,
d.quantity_invoiced,
DECODE (d.line_type_lookup_code, 'TAX', 0, pl.quantity) po_line_qty,
DECODE (d.line_type_lookup_code, 'TAX', 0, pl.unit_price) unit_price,
DECODE (d.line_type_lookup_code, 'TAX', 0, t.quantity) receipt_qty,
i.vendor_id,
i.vendor_site_id,
i.invoice_id,
d.invoice_distribution_id,
ph.po_header_id,
pl.po_line_id,
pd.po_distribution_id,
pl.ITEM_ID
FROM ap_invoice_distributions_all d,
ap_invoices_all i,
po_distributions_all pd,
rcv_transactions t,
rcv_shipment_headers h,
po_lines_all pl,
po_headers_all ph,
po_vendors pv,
po_vendor_sites_all pvs
WHERE i.invoice_id = d.invoice_id
AND i.vendor_id = pv.vendor_id
AND i.vendor_site_id = pvs.vendor_site_id
AND d.po_distribution_id = pd.po_distribution_id
AND d.rcv_transaction_id = t.transaction_id
AND h.shipment_header_id = t.shipment_header_id(+)
AND t.po_line_id = pl.po_line_id
AND pl.po_header_id = ph.po_header_id
--AND i.invoice_amount = 0
ORDER BY t.transaction_date DESC
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