http://aporaclepayables.blogspot.com/2017/03/dual-dates-transactions.html
select distribution_line_number,pp.LINE_TYPE_LOOKUP_CODE,
kk.DOC_sequence_value,
kk.INVOICE_NUM,
kk.ORG_ID,
kk.GL_DATE,
kk.INVOICE_ID,
kk.Header_DATE,
pp.Distribution_DATE,
u.user_name dist_user,
kk.CANCELLED_DATE
from (select aia.DOC_sequence_value,
aia.INVOICE_NUM,
aia.ORG_ID,
aia.GL_DATE,
aia.INVOICE_ID, aia.cancelled_date,
aia.GL_DATE Header_DATE
from ap_invoices_all aia) kk,
(select aida.distribution_line_number, aida.LINE_TYPE_LOOKUP_CODE,
aida.ACCOUNTING_DATE,aida.org_id, aida.created_by,
aida.INVOICE_ID, aida.accrual_posted_flag,
aida.ACCOUNTING_DATE Distribution_DATE
from ap_invoice_distributions_all aida) pp, fnd_user u
where kk.invoice_id = pp.invoice_id
and pp.org_id = kk.org_id
and u.USER_ID = pp.created_by
and kk.org_id = :P_ORG_ID
and kk.gl_date between NVL(:P_FROM_DATE,kk.gl_date ) and NVL(:P_TO_DATE,kk.gl_date )
and Header_DATE <> Distribution_DATE
and pp.LINE_TYPE_LOOKUP_CODE in ('ITEM', 'MISCELLANEOUS')
and kk.CANCELLED_DATE is null
--and kk.gl_date > '01-JAN-16'
order by kk.doc_sequence_value desc
