Email Bursting Report with Queries

 

Negative Bank Balance Report

 Query :
---------------

SELECT SYSDATE,

       cib.bank_account_num,

       cib.bank_account_name,

       gcc.segment3      AS acct_code,

       gcc.segment6      AS div_code,

       ( gb.begin_balance_cr *- 1 ) + gb.begin_balance_dr + (

       gb.period_net_cr *- 1 ) +

       gb.period_net_dr  AS bal,

       ffvv1.description AS bb

FROM   gl_balances gb,

       --gl_periods gp,

       fnd_flex_values_vl ffvv1,

       fnd_flex_value_sets ffvs,

       gl_code_combinations gcc,

       ce_internal_bank_accts_v cib

WHERE  1 = 1

       AND gcc.code_combination_id = gb.code_combination_id

       AND gcc.segment3 = ffvv1.flex_value(+)

       AND ffvv1.flex_value_set_id = ffvs.flex_value_set_id(+)

       AND ffvv1.enabled_flag(+) = 'Y'

       AND ffvs.flex_value_set_name(+) = 'NOMINAL ACCOUNT '

       AND gb.code_combination_id = cib.asset_code_combination_id

       AND ( gb.begin_balance_cr *- 1 ) + gb.begin_balance_dr +

           ( gb.period_net_cr *- 1 ) +

               gb.period_net_dr < 0

       AND Nvl(gb.translated_flag, 'X') != 'R'

       AND ( gb.period_year * 100 ) + gb.period_num = (SELECT Max(

           ( gb1.period_year * 100 ) + gb1.period_num)

            FROM   gl_balances gb1

            WHERE

               gb1.code_combination_id = gb.code_combination_id) 

bursting query 
-----------------

select 

bank_account_num as KEY,

'xx12' TEMPLATE,

:xdo_user_report_locale LOCALE,

'PDF' OUTPUT_FORMAT,

'EMAIL' DEL_CHANNEL,

email PARAMETER1,

email PARAMETER2,

email PARAMETER3,

'GTPL Negative Cash Balance Report' PARAMETER4,

'Hi '||bank_account_name||chr(13)|| 'Please find attached your

Negative Cash Balance Report.' PARAMETER5,

'true' PARAMETER6,

email PARAMETER7 

from  (SELECT SYSDATE,

       cib.bank_account_num,

       cib.bank_account_name,

       gcc.segment3      AS acct_code,

       gcc.segment6      AS div_code,

       ( gb.begin_balance_cr *- 1 ) + gb.begin_balance_dr + (

       gb.period_net_cr *- 1 ) +

       gb.period_net_dr  AS bal,

       ffvv1.description AS bb

FROM   gl_balances gb,

       --gl_periods gp,

       fnd_flex_values_vl ffvv1,

       fnd_flex_value_sets ffvs,

       gl_code_combinations gcc,

       ce_internal_bank_accts_v cib

WHERE  1 = 1

       AND gcc.code_combination_id = gb.code_combination_id

       AND gcc.segment3 = ffvv1.flex_value(+)

       AND ffvv1.flex_value_set_id = ffvs.flex_value_set_id(+)

       AND ffvv1.enabled_flag(+) = 'Y'

       AND ffvs.flex_value_set_name(+) = 'NOMINAL ACCOUNT '

       AND gb.code_combination_id = cib.asset_code_combination_id

       AND ( gb.begin_balance_cr *- 1 ) + gb.begin_balance_dr +

           ( gb.period_net_cr *- 1 ) +

               gb.period_net_dr < 0

       AND Nvl(gb.translated_flag, 'X') != 'R'

       AND ( gb.period_year * 100 ) + gb.period_num = (SELECT Max(

           ( gb1.period_year * 100 ) + gb1.period_num)

            FROM   gl_balances gb1

            WHERE

               gb1.code_combination_id = gb.code_combination_id) )