Query in RSPI

Post date: Mar 19, 2013 11:36:34 AM

select distinct gjh.NAME JOURNAL_NAME

,gjb.NAME BATCH_NAME

,gjh.JE_SOURCE SOURCE

,gjh.JE_CATEGORY CATEGORY

,gjh.currency_code

,gcc.SEGMENT1||'.'||gcc.SEGMENT2||'.'||gcc.SEGMENT3||'.'||

gcc.SEGMENT4 ACCOUNT

,NVL(gjl.ENTERED_DR,0) DEBIT_VALAS

,NVL(gjl.ENTERED_CR,0) CREDIT_VALAS

,NVL(gjl.ACCOUNTED_DR,0) DEBIT

,NVL(gjl.ACCOUNTED_CR,0) CREDIT

,xah.EVENT_TYPE_CODE

,xal.ACCOUNTING_CLASS_CODE

,NVL(xal.ENTERED_DR,0) DEBIT_VALAS_XLA

,NVL(xal.ENTERED_CR,0) CREDIT_VALAS_XLA

,NVL(xal.ACCOUNTED_DR,0) DEBIT_XLA

,NVL(xal.ACCOUNTED_CR,0) CREDIT_XLA

,aia.INVOICE_NUM

,aia.INVOICE_AMOUNT

,aia.ATTRIBUTE10 Nomor_PO

,nvl(aia.EXCHANGE_RATE,1) KURS

,XAL.GL_SL_LINK_ID

from gl_je_headers gjh

,gl_je_lines gjl

,gl_je_batches gjb

,gl_import_references gir

,gl_code_combinations gcc

,xla_ae_lines xal

,xla_ae_headers xah

,ap_invoice_distributions_all aida

,ap_invoices_all aia

where gjh.JE_HEADER_ID = gjl.JE_HEADER_ID

and gjh.JE_BATCH_ID = gjb.JE_BATCH_ID

and gjh.JE_HEADER_ID = gir.JE_HEADER_ID

and gjl.JE_LINE_NUM = gir.JE_LINE_NUM

and gjl.CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID

and gir.GL_SL_LINK_ID = xal.GL_SL_LINK_ID

and gir.GL_SL_LINK_TABLE = xal.GL_SL_LINK_TABLE

and xal.AE_HEADER_ID = xah.AE_HEADER_ID

and xal.APPLICATION_ID = xah.APPLICATION_ID

and xah.EVENT_ID = aida.ACCOUNTING_EVENT_ID

and aida.INVOICE_ID = aia.invoice_id

--AND GJH.PERIOD_NAME in ('Dec-12')--,'Jan-10','Feb-10','Mar-10','Apr-10','May-10','Jun-10','Jul-10','Aug-10','Oct-10','Nov-10','Dec-10')

AND gcc.segment2 in ('21010020')--,'211112','211113','211114','216112')

AND gcc.segment1 in ('01')

AND GJH.ACTUAL_FLAG = 'A'

AND GJH.STATUS = 'P'

AND GCC.ENABLED_FLAG = 'Y'

-- and aia.INVOICE_NUM = 'GF-GT06-00143'

-- and aia.ATTRIBUTE10='GF-PO06-14908'

and (nvl(XAL.ACCOUNTED_DR,0) -nvl( xal.accounted_cr,0)) <> 0

--ORDER BY xah.AE_HEADER_ID, xal.AE_LINE_NUM