BI Publisher supports executing PL/SQL anonymous blocks. You can perform calculations in the PL/SQL block and return the result set. BI Publisher uses callable statements to execute anonymous blocks.
The requirements are:
- The PL/SQL block must return a result set of type REF cursor
- You must declare the out variable with the name, xdo_cursor;. This needs to be the first parameter in position in Data model.
- If you do not declare the name properly, the first bind variable is treated as an out variable type and binds with REF cursor.
- Declare the data model parameter with name xdo_cursor. This name is reserved for out variable type for procedure/anonymous blocks.
Sample Script:
DECLARE
TYPE refcursor IS REF CURSOR;
xdo_cursor refcursor;
BEGIN
OPEN :xdo_cursor FOR SELECT gl.name
, fnd_flex_ext.get_segs('GL', 'GL#', fnd.id_flex_num, gc.code_combination_id) concatenated_segments
, decode(nvl(gb.translated_flag, 'X'), 'R',(nvl(gb.begin_balance_dr, 0) - nvl(gb.begin_balance_cr, 0))
, 'X',(nvl(gb.begin_balance_dr_beq, 0) - nvl(gb.begin_balance_cr_beq, 0)), - 99) begin_balance
, decode(nvl(gb.translated_flag, 'X'), 'R', 'No', 'X', 'Yes', 'XXX') is_functional_currency
FROM gl_ledgers gl
, gl_balances gb
, gl_code_combinations gc
, fnd_id_flex_structures_tl fnd
WHERE gc.chart_of_accounts_id = gl.chart_of_accounts_id
AND gl.ledger_id = gb.ledger_id
AND gc.code_combination_id = gb.code_combination_id
AND fnd.id_flex_num = gc.chart_of_accounts_id
AND fnd.application_id = 101
AND fnd.id_flex_code = 'GL#'
AND fnd.language = userenv('LANG')
AND gb.period_name = :p_period_name
AND gc.segment1 = :p_segment1
AND gc.segment3 = :p_segment3
AND gl.ledger_id = :p_pedger_id;
END;
Data Model:
Parameter Window:
Sample Output:
