Account Payable Invoice and Accounting Details.

-- -------------------------------------------------------------------------------------------------
-- Purpose : Query to get the invoice,Accounting detials based on Batch and Checknumbers
-- Date      : 12.May.2009
-- Created by : Chaitanya
-- .....
-- -------------------------------------------------------------------------------------------------


p>SELECT apc.check_Name,
ai.invoice_num,
ai.invoice_id,
apc.CHECK_NUMBER,
apc.BANK_ACCOUNT_NAME,
apc.status_lookup_code Pay_Batch_status,
(select aae.EVENT_STATUS_CODE from ap_accounting_events_all aae
where aae.SOURCE_ID = apc.CHECK_IDand aae.EVENT_TYPE_CODE = 'PAYMENT CLEARING') Check_Event_STatus,
(select aae.ACCOUNTING_DATE from ap_accounting_events_all aae
where aae.SOURCE_ID = apc.CHECK_ID
and aae.EVENT_TYPE_CODE = 'PAYMENT CLEARING') Check_Accounting_Date,
(select aae.EVENT_STATUS_CODE from ap_accounting_events_all aae
where aae.SOURCE_ID = apc.CHECK_IDand aae.EVENT_TYPE_CODE = 'INVOICE') Invoice_Event_STatus,
(select aae.ACCOUNTING_DATE from ap_accounting_events_all aae
where aae.SOURCE_ID = apc.CHECK_IDand aae.EVENT_TYPE_CODE = 'INVOICE') Invoice_Accounting_Date,
trunc(apc.creation_date) Check_CR_Date,
apc.CHECK_ID
from AP_INVOICE_PAYMENTS_all aip,
AP_PAYMENT_Schedules_all aps,
ap_checks_all apc,ap_invoices_all ai
where 1=1
and aip.invoice_id = aps.invoice_id
and aps.invoice_id = ai.invoice_id
--and apc.STATUS_LOOKUP_CODE = 'CLEARED BUT UNACCOUNTED'
--and apc.CHECKRUN_NAME = '240409KONCERNZZZ'
and aip.CHECK_ID = apc.CHECK_ID;

========================================================