FSQL 10

SQL QUERY FOR populates Close Balance for Bank Transactions IN ORACLE CLOUD FUSION R13

SELECT balance_amount CLOSE_BAL_BANK

FROM   ce_stmt_balances csb          ,

       fnd_lookups cel                ,

       ce_statement_headers csh

WHERE  csb.balance_code        = cel.lookup_code

   AND cel.lookup_type         = 'CE_INTERNAL_BALANCE_CODES'

   AND cel.lookup_code         = 'CLBD'

   AND csh.statement_header_id = csb.statement_header_id

   AND csh.bank_account_id     = :p_bank_account_id

   and nvl(csh.intraday_flag, 'N') <> 'Y'  

   AND balance_date           IN

                                  ( SELECT MAX(balance_date)

                                  FROM    ce_stmt_balances csb1 ,

                                          ce_statement_headers csh

                                  WHERE   csh.bank_account_id     = :p_bank_account_id

                                      AND csh.statement_header_id = csb1.statement_header_id

                                                      and nvl(csh.intraday_flag, 'N') <> 'Y'

                                      AND TRUNC(balance_date) BETWEEN :gc_from_date AND :gc_to_date

                                      AND csb1.balance_code = csb.balance_code  

                                  )