select distinct pv.segment1,
pv.vendor_name,
receipt_num,
ai.invoice_num,
poh.segment1,
ai.invoice_date,
ai.invoice_amount,
ai.invoice_currency_code
from rcv_shipment_headers rsh,
rcv_transactions rt,
ap_invoices_all ai,
ap_invoice_distributions_all aid,
po_headers_all poh ,
po_vendors pv
where
--rsh.shipment_header_id =3151 and
rt.shipment_header_id = rsh.shipment_header_id and
ai.invoice_id = aid.invoice_id and
rt.transaction_id = aid.rcv_transaction_id and
poh.po_header_id(+) = rt.po_header_id and
pv.vendor_id = ai.vendor_id
invoice payment details
----------------------
SELECT
AI.INVOICE_ID,AI.INVOICE_NUM,AI.INVOICE_DATE,AI.INVOICE_CURRENCY_CODE,AI.INVOICE_AMOUNT,
AC.CHECK_NUMBER,AC.CHECK_DATE,AIP.AMOUNT,AIP.ACCOUNTING_DATE,
ABB.BANK_NAME,ABB.BANK_BRANCH_NAME,ABA.BANK_ACCOUNT_NUM,ABA.BANK_ACCOUNT_NAME
FROM
AP_INVOICES_ALL AI ,
AP_INVOICE_PAYMENTS_ALL AIP,
AP_CHECKS_ALL AC,
AP_BANK_ACCOUNTS_ALL ABA,
AP_BANK_BRANCHES ABB
WHERE AI.INVOICE_ID = AIP.INVOICE_ID AND
AC.CHECK_ID = AIP.CHECK_ID AND
ABA.BANK_ACCOUNT_ID = AC.BANK_ACCOUNT_ID AND
ABB.BANK_BRANCH_ID = ABA.BANK_BRANCH_ID
Accounted invoices
-----------------
SELECT ALA.* FROM AP_ACCOUNTING_EVENTS_ALL AAE,
AP_AE_HEADERS_ALL AHA,
AP_AE_LINES_ALL ALA
WHERE --AAE.SOURCE_ID=10250
AAE.SOURCE_TABLE = 'AP_INVOICES'
AND AAE.ACCOUNTING_EVENT_ID = AHA.ACCOUNTING_EVENT_ID
AND AHA.AE_HEADER_ID = ALA.AE_HEADER_ID
AND AHA.AE_HEADER_ID=10374
GL drill down
-------------
SELECT AI.INVOICE_NUM,AI.INVOICE_DATE,GJH.NAME,GJH.STATUS,gjh.je_header_id
FROM AP_INVOICES_ALL AI,
AP_ACCOUNTING_EVENTS_ALL AAE,
AP_AE_HEADERS_ALL AHA,
AP_AE_LINES_ALL ALA,
GL_JE_HEADERS GJH,
GL_JE_LINES GJL
WHERE --AAE.SOURCE_ID=10250
AI.INVOICE_ID = AAE.SOURCE_ID
AND AAE.SOURCE_TABLE = 'AP_INVOICES'
AND AAE.ACCOUNTING_EVENT_ID = AHA.ACCOUNTING_EVENT_ID
AND AHA.AE_HEADER_ID = ALA.AE_HEADER_ID
AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
AND ALA.GL_SL_LINK_ID= GJL.GL_SL_LINK_ID
--AND AHA.AE_HEADER_ID=10374
and ai.invoice_num='ERS-9273-19995'
No comments:
Post a Comment