FSQL 2

SQL QUERY FOR PAYABLES NON-RECONCILED JOURNALS STATEMENT IN ORACLE CLOUD FUSION R13

SELECT  DISTINCT 'PAYABLES'   ORIGIN ,

               CTTM.TRX_TYPE  TRX_TYPE,

               to_char(AC.CHECK_ID) DOC_NUMBER,

               TO_CHAR(NVL(AC.CLEARED_DATE, AC.CHECK_DATE),'YYYY-MM-DD') TRX_DATE,

                  XAL.ENTERED_CR  ENTERED_BOOKS,

               -1*XAL.ACCOUNTED_CR ACCOUNTED_BOOKS,

               0  BANK,

               to_char(AC.CHECK_NUMBER)  STATEMENT_NUMBER

FROM    GL_JE_HEADERS GJH,

                   GL_JE_LINES GJL,

        GL_JE_BATCHES GJB           ,

                              GL_LEDGERS  GL, 

                              GL_IMPORT_REFERENCES GLIR,

                              XLA_AE_HEADERS XAH ,

                              XLA_AE_LINES XAL,

                              AP_CHECKS AC,

                              CE_TRX_TYPE_MAPPING CTTM ,

                              XLA_TRANSACTION_ENTITIES TRX,

                              XLA_EVENTS XE

WHERE   GJH.JE_HEADER_ID = GJL.JE_HEADER_ID

AND       GJH.JE_SOURCE = 'Payables'

AND       GJL.LEDGER_ID = GL.LEDGER_ID

AND       GL.LEDGER_CATEGORY_CODE    = 'PRIMARY'

AND GLIR.JE_HEADER_ID = GJL.JE_HEADER_ID

AND GLIR.JE_LINE_NUM = GJL.JE_LINE_NUM

   AND GJB.JE_BATCH_ID   = GJH.JE_BATCH_ID 

   AND GJB.STATUS = 'P'                    

   AND XE.EVENT_DATE BETWEEN :GC_FROM_DATE AND :GC_TO_DATE 

AND       XAH.AE_HEADER_ID = XAL.AE_HEADER_ID

AND       XAL.GL_SL_LINK_ID = GJL.GL_SL_LINK_ID

   AND XAL.GL_SL_LINK_ID = GLIR.GL_SL_LINK_ID

   and xah.application_id = 789                                

   and xah.application_id = xal.application_id

   and xal.gl_sl_link_table = GLIR.GL_SL_LINK_table

   AND GJB.JE_BATCH_ID = GLIR.JE_BATCH_ID

AND       TRX.SOURCE_ID_INT_1 = AC.CHECK_ID

AND       XE.ENTITY_ID = TRX.ENTITY_ID

AND       XAH.EVENT_ID = XE.EVENT_ID

AND       AC.PAYMENT_METHOD_LOOKUP_CODE = CTTM.PMT_RCT_METHOD

AND       TRX.APPLICATION_ID =789

AND       AC.RECON_FLAG = 'N'

AND       TRX.APPLICATION_ID = XE.APPLICATION_ID

AND       TRX.ENTITY_CODE = 'AP_PAYMENTS' 

AND       XAL.GL_SL_LINK_TABLE IN ('XLAJEL')

AND       gjl.&p_where