SELECT
TRUNC(SYSDATE) REPORT_DATE,
'AP' TYPE,
XAL.ACCOUNTING_DATE,
XTE.TRANSACTION_NUMBER INVOICE_NUM,
PV.VENDOR_NAME NAME,
xal.ACCOUNTED_DR ACCOUNTED_DR,
xal.ACCOUNTED_CR ACCOUNTED_CR,
xal.ENTERED_DR,
xal.ENTERED_CR,
XAL.DESCRIPTION DESCRIPTION,
GCC.SEGMENT2 ACCOUNT_NUMBER,
gcc.SEGMENT1,
gcc.SEGMENT3,
gcc.SEGMENT4,
ffv.DESCRIPTION Account_Description,
HOU.NAME
FROM
XLA_AE_LINES XAL,
xla.xla_ae_headers xah,
xla.XLA_TRANSACTION_ENTITIES XTE,
xla.xla_events xe,
PO_VENDORS PV,
GL_CODE_COMBINATIONS GCC,
fnd_flex_values_vl ffv,
hr_operating_units hou
WHERE-- xte.APPLICATION_ID = 200
xte.ENTITY_ID = xe.ENTITY_ID
and xal.AE_HEADER_ID = xah.AE_HEADER_ID
and xe.EVENT_ID = xah.EVENT_ID
AND XAL.PARTY_ID = PV.VENDOR_ID
AND XAL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND XTE.SECURITY_ID_INT_1=HOU.ORGANIZATION_ID
and gcc.SEGMENT2 = ffv.FLEX_VALUE_MEANING
and gcc.SEGMENT2 in ( '100801' ) --Account code
and FLEX_VALUE_SET_ID = 1014191 -- Account flex set id
and XAL.ACCOUNTING_DATE between
TO_DATE('01/01/2014','DD/MM/YYYY') and TO_DATE('31/03/2014','DD/MM/YYYY')
-- remove this line if you want full history
