10-24-2014, 06:39 PM
Select
GJH.NAME,
GJH.JE_HEADER_ID,
GJL.JE_LINE_NUM,
aia.INVOICE_ID "Invoice Id",
aia.INVOICE_NUM "Invoice Number",
aia.INVOICE_DATE "Invoice Date",
aia.INVOICE_AMOUNT "Amount",
xal.ENTERED_DR "Entered DR in SLA",
xal.ENTERED_CR "Entered CR in SLA",
xal.ACCOUNTED_DR "Accounted DR in SLA",
xal.ACCOUNTED_CR "Accounted CR in SLA",
gjh.je_source ,
gjl.ENTERED_DR "Entered DR in GL",
gjl.ENTERED_CR "Entered CR in GL",
gjl.ACCOUNTED_DR "Accounted DR in GL",
gjl.ACCOUNTED_CR "Accounted CR in GL",
xal.ACCOUNTING_CLASS_CODE "Accounting Class",
gcc.SEGMENT1||'.'||gcc.SEGMENT2||'.'
||gcc.SEGMENT3||'.'||gcc.SEGMENT4||'.'
||gcc.SEGMENT5||'.'||gcc.SEGMENT6||'.'
||gcc.SEGMENT7 "GL_Code Combination",
aia.INVOICE_CURRENCY_CODE "Inv Curr Code",
aia.PAYMENT_CURRENCY_CODE "Pay Curr Code",
aia.GL_DATE "GL Date",
xah.PERIOD_NAME "Period",
aia.PAYMENT_METHOD_CODE "Payment Method",
aia.VENDOR_ID "Vendor Id",
aps.VENDOR_NAME "Vendor Name",
xah.JE_CATEGORY_NAME "JE Category Name"
FROM
apps.ap_invoices_all aia,
xla.xla_transaction_entities XTE,
apps.xla_events xev,
apps.xla_ae_headers XAH,
apps.xla_ae_lines XAL,
apps.GL_IMPORT_REFERENCES gir,
apps.gl_je_headers gjh,
apps.gl_je_lines gjl,
apps.gl_code_combinations gcc,
apps.ap_suppliers aps
Where
aia.INVOICE_ID = xte.source_id_int_1
and xev.entity_id= xte.entity_id
and xah.entity_id= xte.entity_id
and xah.event_id= xev.event_id
and XAH.ae_header_id = XAL.ae_header_id
-- and XAH.gl_transfer_status_code= 'Y'
and XAL.GL_SL_LINK_ID=gir.GL_SL_LINK_ID
and gir.GL_SL_LINK_TABLE = xal.GL_SL_LINK_TABLE
and gjl.JE_HEADER_ID=gjh.JE_HEADER_ID
and gjh.JE_HEADER_ID=gir.JE_HEADER_ID
and gjl.JE_HEADER_ID=gir.JE_HEADER_ID
and gir.JE_LINE_NUM=gjl.JE_LINE_NUM
and gcc.CODE_COMBINATION_ID=XAL.CODE_COMBINATION_ID
and gcc.CODE_COMBINATION_ID=gjl.CODE_COMBINATION_ID
and aia.VENDOR_ID=aps.VENDOR_ID
and aia.INVOICE_DATE between :Invoice_from and :invoice_to
-- and gcc.segment1 = 01
-- and gjh.STATUS='P'
and gjh.Actual_flag='A'
GJH.NAME,
GJH.JE_HEADER_ID,
GJL.JE_LINE_NUM,
aia.INVOICE_ID "Invoice Id",
aia.INVOICE_NUM "Invoice Number",
aia.INVOICE_DATE "Invoice Date",
aia.INVOICE_AMOUNT "Amount",
xal.ENTERED_DR "Entered DR in SLA",
xal.ENTERED_CR "Entered CR in SLA",
xal.ACCOUNTED_DR "Accounted DR in SLA",
xal.ACCOUNTED_CR "Accounted CR in SLA",
gjh.je_source ,
gjl.ENTERED_DR "Entered DR in GL",
gjl.ENTERED_CR "Entered CR in GL",
gjl.ACCOUNTED_DR "Accounted DR in GL",
gjl.ACCOUNTED_CR "Accounted CR in GL",
xal.ACCOUNTING_CLASS_CODE "Accounting Class",
gcc.SEGMENT1||'.'||gcc.SEGMENT2||'.'
||gcc.SEGMENT3||'.'||gcc.SEGMENT4||'.'
||gcc.SEGMENT5||'.'||gcc.SEGMENT6||'.'
||gcc.SEGMENT7 "GL_Code Combination",
aia.INVOICE_CURRENCY_CODE "Inv Curr Code",
aia.PAYMENT_CURRENCY_CODE "Pay Curr Code",
aia.GL_DATE "GL Date",
xah.PERIOD_NAME "Period",
aia.PAYMENT_METHOD_CODE "Payment Method",
aia.VENDOR_ID "Vendor Id",
aps.VENDOR_NAME "Vendor Name",
xah.JE_CATEGORY_NAME "JE Category Name"
FROM
apps.ap_invoices_all aia,
xla.xla_transaction_entities XTE,
apps.xla_events xev,
apps.xla_ae_headers XAH,
apps.xla_ae_lines XAL,
apps.GL_IMPORT_REFERENCES gir,
apps.gl_je_headers gjh,
apps.gl_je_lines gjl,
apps.gl_code_combinations gcc,
apps.ap_suppliers aps
Where
aia.INVOICE_ID = xte.source_id_int_1
and xev.entity_id= xte.entity_id
and xah.entity_id= xte.entity_id
and xah.event_id= xev.event_id
and XAH.ae_header_id = XAL.ae_header_id
-- and XAH.gl_transfer_status_code= 'Y'
and XAL.GL_SL_LINK_ID=gir.GL_SL_LINK_ID
and gir.GL_SL_LINK_TABLE = xal.GL_SL_LINK_TABLE
and gjl.JE_HEADER_ID=gjh.JE_HEADER_ID
and gjh.JE_HEADER_ID=gir.JE_HEADER_ID
and gjl.JE_HEADER_ID=gir.JE_HEADER_ID
and gir.JE_LINE_NUM=gjl.JE_LINE_NUM
and gcc.CODE_COMBINATION_ID=XAL.CODE_COMBINATION_ID
and gcc.CODE_COMBINATION_ID=gjl.CODE_COMBINATION_ID
and aia.VENDOR_ID=aps.VENDOR_ID
and aia.INVOICE_DATE between :Invoice_from and :invoice_to
-- and gcc.segment1 = 01
-- and gjh.STATUS='P'
and gjh.Actual_flag='A'