FSQL 1

•        SQL QUERY FOR POPULATES DETAIL INFORMATION OF UNRECONCILED BANK AND BOOK TRANSACTIONS IN ORACLE CLOUD FUSION R13 

•       SQL QUERY FOR  GETTING UNRECONCILED LINES FROM BANK STATEMENT IN ORACLE CLOUD FUSION R13

SELECT  'BANK STATEMENT' ORIGIN,

               SL.TRX_TYPE TRX_TYPE,

               SL.RECON_REFERENCE   DOC_NUMBER,

               TO_CHAR(SL.BOOKING_DATE,'YYYY-MM-DD') TRX_DATE,

               0 ENTERED_BOOKS,

               0 ACCOUNTED_BOOKS,

               DECODE(SL.FLOW_INDICATOR, 'DBIT', -1 * SL.AMOUNT * CE_CEXRECRE_XMLP_PKG.currency_conversion (:P_SET_OF_BOOKS_ID, sh.currency_code , sl.booking_date, :gc_conversion_type)

                                                  , SL.AMOUNT* CE_CEXRECRE_XMLP_PKG.currency_conversion (:P_SET_OF_BOOKS_ID, sh.currency_code , sl.booking_date, :gc_conversion_type) ) BANK,                                      

               to_char(SL.LINE_NUMBER)  STATEMENT_NUMBER

FROM    

               CE_STATEMENT_LINES SL ,

               CE_STATEMENT_HEADERS SH

WHERE   SL.STATEMENT_HEADER_ID = SH.STATEMENT_HEADER_ID

AND     RECON_STATUS = 'UNR'

AND     SH.BANK_ACCOUNT_ID = :P_BANK_ACCOUNT_ID

AND       TRUNC(SL.BOOKING_DATE) BETWEEN :GC_FROM_DATE AND :GC_TO_DATE