[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| update the effective_period_num in balances table in addition to |
| updating the effective_period_num in xla_ledger_options. |
| |
| |
+======================================================================*/
-- Private exceptions
--
le_resource_busy EXCEPTION;
g_preupdate_flag VARCHAR2 (1);
g_postupdate_flag VARCHAR2 (1);
l_insert_sql VARCHAR2(2000);
SELECT xah.ledger_id
, xah.application_id
, xah.accounting_batch_id
BULK COLLECT INTO
g_ledger_array
, g_application_array
, g_accounting_batch_array
FROM xla_ae_headers xah
, xla_ae_lines xal
, gl_period_statuses gps
, xla_ledger_relationships_v xlr
WHERE xah.application_id = p_application_id
AND xah.ledger_id = p_ledger_id
AND xah.ae_header_id = xal.ae_header_id
AND xah.application_id = xal.application_id
AND xah.accounting_batch_id IS NOT NULL
-- to handle undo case. accounting_batch_id will be null if the entries were created by undo
AND ( xal.analytical_balance_flag = 'P'
OR xal.control_balance_flag = 'P'
)
AND xah.accounting_entry_status_code ='F'
AND xah.ledger_id = xlr.ledger_id
AND gps.period_name = xah.period_name
AND gps.ledger_id = DECODE(xlr.ledger_category_code, 'ALC' , xlr.primary_ledger_id, xlr.ledger_id)
AND gps.application_id=101
AND gps.closing_status in ('O','C','P')
AND gps.adjustment_period_flag = 'N'
GROUP BY xah.application_id
, xah.ledger_id
, xah.accounting_batch_id;
INSERT INTO xla_bal_concurrency_control
(ledger_id
, application_id
, concurrency_class
, accounting_batch_id
, execution_mode
, request_id
) VALUES
( g_ledger_array(i)
, g_application_array(i)
, p_concurrency_class
, g_accounting_batch_array(i)
, p_execution_mode
, p_request_id ) ;
select distinct
ledger_id
,application_id
,accounting_batch_id
BULK COLLECT INTO
g_ledger_array
, g_application_array
, g_accounting_batch_array
from xla_ae_headers
where application_id = p_application_id
and entity_id = p_entity_id;
INSERT INTO xla_bal_concurrency_control
(ledger_id
, application_id
, concurrency_class
, accounting_batch_id
, execution_mode
, request_id
) VALUES
( g_ledger_array(i)
, g_application_array(i)
, p_concurrency_class
, g_accounting_batch_array(i)
, p_execution_mode
, -1*p_entity_id ) ;
select distinct
ledger_id
,application_id
,accounting_batch_id
BULK COLLECT INTO
g_ledger_array
, g_application_array
, g_accounting_batch_array
from xla_ae_headers
where application_id = p_application_id
and ae_header_id = p_ae_header_id;
INSERT INTO xla_bal_concurrency_control
(ledger_id
, application_id
, concurrency_class
, accounting_batch_id
, execution_mode
, request_id
) VALUES
( g_ledger_array(i)
, g_application_array(i)
, p_concurrency_class
, g_accounting_batch_array(i)
, p_execution_mode
, -1*p_ae_header_id ) ;
select distinct
ledger_id
,application_id
,p_accounting_batch_id
BULK COLLECT INTO
g_ledger_array
, g_application_array
, g_accounting_batch_array
from xla_ae_headers
where accounting_batch_id = p_accounting_batch_id
and application_id = p_application_id;
INSERT INTO xla_bal_concurrency_control
(ledger_id
, application_id
, concurrency_class
, accounting_batch_id
, execution_mode
, request_id
) VALUES
( g_ledger_array(i)
, g_application_array(i)
, p_concurrency_class
, g_accounting_batch_array(i)
, p_execution_mode
, p_request_id ) ;
select
p_ledger_id
,p_application_id
,p_accounting_batch_id
BULK COLLECT INTO
g_ledger_array
, g_application_array
, g_accounting_batch_array
FROM dual;
INSERT INTO xla_bal_concurrency_control
(ledger_id
, application_id
, concurrency_class
, accounting_batch_id
, execution_mode
, request_id
) VALUES
( g_ledger_array(i)
, g_application_array(i)
, p_concurrency_class
, g_accounting_batch_array(i)
, p_execution_mode
, p_request_id ) ;
select
p_ledger_id
BULK COLLECT INTO
g_ledger_array
FROM dual;
INSERT INTO xla_bal_concurrency_control
(ledger_id
, concurrency_class
, request_id
) VALUES
( g_ledger_array(i)
, p_concurrency_class
, p_request_id ) ;
, p_msg => '# rows inserted into xla_bal_concurrency_control ' || SQL%ROWCOUNT
, p_level => c_level_procedure
);
l_insert1_count NUMBER;
l_update1_count NUMBER;
l_insert2_count NUMBER;
l_update2_count NUMBER;
l_update_bal VARCHAR2(6000);
l_insert_bal VARCHAR2(6000);
l_update_processed VARCHAR2(5000);
l_summary_stmt VARCHAR2(7000):= 'INSERT INTO xla_ac_bal_interim_gt
( application_id
, ledger_id
, code_combination_id
, analytical_criterion_code
, analytical_criterion_type_code
, amb_context_code
, ac1
, ac2
, ac3
, ac4
, ac5
, period_name
, effective_period_num
, period_balance_dr
, period_balance_cr
, period_year
)
SELECT /*+ $parallel$ use_nl(aeh) use_nl(acs) use_nl(ael) */
ael.application_id
, ael.ledger_id
, ael.code_combination_id
, acs.analytical_criterion_code
, acs.analytical_criterion_type_code
, acs.amb_context_code
, acs.ac1
, acs.ac2
, acs.ac3
, acs.ac4
, acs.ac5
, aeh.period_name
, gps.effective_period_num
, $period_balance_dr$
, $period_balance_cr$
, SUBSTR (gps.effective_period_num, 1, 4) period_year
FROM xla_ae_headers aeh
, xla_ae_lines ael
, xla_ae_line_acs acs
, xla_analytical_hdrs_b xbh
, gl_period_statuses gps
, xla_ledger_options xlo
, xla_ledger_relationships_v xlr
$bal_concurrency$
WHERE aeh.application_id = :'||l_summary_bind_count||'
AND aeh.accounting_entry_status_code = ''F''
AND aeh.balance_type_code = ''A''
AND ael.application_id = aeh.application_id
AND ael.ae_header_id = aeh.ae_header_id
AND ael.analytical_balance_flag = '''||g_preupdate_flag||'''
AND ael.ledger_id = aeh.ledger_id
AND acs.ae_header_id = ael.ae_header_id
AND acs.ae_line_num = ael.ae_line_num
AND xlr.ledger_id = aeh.ledger_id
AND xlo.application_id = aeh.application_id
AND xlo.ledger_id = DECODE (xlr.ledger_category_code , ''ALC''
, xlr.primary_ledger_id , xlr.ledger_id )
AND gps.ledger_id = xlo.ledger_id
AND gps.application_id = 101
AND gps.closing_status IN (''O'', ''C'', ''P'')
AND gps.effective_period_num <= xlo.effective_period_num
AND gps.adjustment_period_flag = ''N''
AND gps.period_name = aeh.period_name
AND xbh.analytical_criterion_code = acs.analytical_criterion_code
AND xbh.analytical_criterion_type_code = acs.analytical_criterion_type_code
AND xbh.amb_context_code = acs.amb_context_code
AND xbh.balancing_flag <> ''N''';
l_insert1_count := SQL%ROWCOUNT;
, p_msg => '# rows inserted in XLA_AC_BAL_INTERIM_GT : '
|| l_insert1_count
, p_level => c_level_procedure
);
IF l_insert1_count = 0
THEN
IF (c_level_procedure >= g_log_level)
THEN
TRACE (p_module => l_log_module
, p_msg => 'No Records to process ' || l_insert1_count
, p_level => c_level_procedure
);
USING (SELECT period_balance_dr
, period_balance_cr
-- Begin Bug 13498442
, decode (year_end_carry_forward_code,'Y',
SUM (lag_dr) OVER (PARTITION BY application_id, ledger_id, code_combination_id
, analytical_criterion_code, analytical_criterion_type_code, amb_context_code
, ac1, ac2, ac3, ac4, ac5 ORDER BY application_id, ledger_id
, code_combination_id
, analytical_criterion_code
, analytical_criterion_type_code
, amb_context_code
, ac1
, ac2
, ac3
, ac4
, ac5
, effective_period_num)
, SUM (lag_dr) OVER (PARTITION BY application_id, ledger_id, code_combination_id
, analytical_criterion_code, analytical_criterion_type_code, amb_context_code
, ac1, ac2, ac3, ac4, ac5, period_year ORDER BY application_id, ledger_id
, code_combination_id
, analytical_criterion_code
, analytical_criterion_type_code
, amb_context_code
, ac1
, ac2
, ac3
, ac4
, ac5
, effective_period_num) ) xal_beginning_balance_dr
, decode (year_end_carry_forward_code,'Y',
SUM (lag_cr) OVER (PARTITION BY application_id, ledger_id, code_combination_id
, analytical_criterion_code, analytical_criterion_type_code, amb_context_code
, ac1, ac2, ac3, ac4, ac5 ORDER BY application_id, ledger_id
, code_combination_id
, analytical_criterion_code
, analytical_criterion_type_code
, amb_context_code
, ac1
, ac2
, ac3
, ac4
, ac5
, effective_period_num)
, SUM (lag_cr) OVER (PARTITION BY application_id, ledger_id, code_combination_id
, analytical_criterion_code, analytical_criterion_type_code, amb_context_code
, ac1, ac2, ac3, ac4, ac5, period_year ORDER BY application_id, ledger_id
, code_combination_id
, analytical_criterion_code
, analytical_criterion_type_code
, amb_context_code
, ac1
, ac2
, ac3
, ac4
, ac5
, effective_period_num) ) xal_beginning_balance_cr
-- End Bug 13498442
, application_id
, ledger_id
, code_combination_id
, analytical_criterion_code
, analytical_criterion_type_code
, amb_context_code
, ac1
, ac2
, ac3
, ac4
, ac5
, period_name
, effective_period_num
, period_year
FROM (SELECT /*+ leading(xag,xal_bal) */
xal_bal.application_id
, xal_bal.ledger_id
, xal_bal.code_combination_id
code_combination_id
, xal_bal.analytical_criterion_code
, xal_bal.analytical_criterion_type_code
, xal_bal.amb_context_code
, xal_bal.ac1
, xal_bal.ac2
, xal_bal.ac3
, xal_bal.ac4
, xal_bal.ac5
, xal_bal.year_end_carry_forward_code --Bug13498442
, xal_bal.period_name period_name
, xal_bal.effective_period_num
, xal_bal.period_balance_dr
, xal_bal.period_balance_cr
, xal_bal.period_year
, LAG (NVL (xal_bal.period_balance_dr, 0)
, 1
, NVL (xal_bal.beginning_balance_dr, 0)
) OVER (PARTITION BY xal_bal.application_id, xal_bal.ledger_id
, xal_bal.code_combination_id, xal_bal.analytical_criterion_type_code
, xal_bal.amb_context_code, xal_bal.ac1, xal_bal.ac2, xal_bal.ac3, xal_bal.ac4
, xal_bal.ac5 ORDER BY xal_bal.application_id
, xal_bal.ledger_id
, xal_bal.code_combination_id
, xal_bal.analytical_criterion_code
, xal_bal.analytical_criterion_type_code
, xal_bal.amb_context_code
, xal_bal.ac1
, xal_bal.ac2
, xal_bal.ac3
, xal_bal.ac4
, xal_bal.ac5
, xal_bal.effective_period_num) lag_dr
, LAG (NVL (xal_bal.period_balance_cr, 0)
, 1
, NVL (xal_bal.beginning_balance_cr, 0)
) OVER (PARTITION BY xal_bal.application_id, xal_bal.ledger_id
, xal_bal.code_combination_id, xal_bal.analytical_criterion_type_code
, xal_bal.amb_context_code, xal_bal.ac1, xal_bal.ac2, xal_bal.ac3
, xal_bal.ac4, xal_bal.ac5 ORDER BY xal_bal.application_id
, xal_bal.ledger_id
, xal_bal.code_combination_id
, xal_bal.analytical_criterion_code
, xal_bal.analytical_criterion_type_code
, xal_bal.amb_context_code
, xal_bal.ac1
, xal_bal.ac2
, xal_bal.ac3
, xal_bal.ac4
, xal_bal.ac5
, xal_bal.effective_period_num) lag_cr
FROM (SELECT tmp.application_id
, tmp.ledger_id
, tmp.code_combination_id
, tmp.analytical_criterion_code
, tmp.analytical_criterion_type_code
, tmp.amb_context_code
, decode (xbh.year_end_carry_forward_code,'Y','Y','A','Y','B',decode(gcc.account_type,'A','Y','L','Y','O','Y','N'),'N') year_end_carry_forward_code --Bug13498442
, MAX
(DECODE
(gps.effective_period_num
, tmp.effective_period_num, tmp.period_balance_dr
, NULL
)
) period_balance_dr
, MAX
(DECODE
(gps.effective_period_num
, tmp.effective_period_num, tmp.period_balance_cr
, NULL
)
) period_balance_cr
, tmp.beginning_balance_dr
, tmp.beginning_balance_cr
, tmp.ac1
, tmp.ac2
, tmp.ac3
, tmp.ac4
, tmp.ac5
, gps.period_name
, gps.effective_period_num
, gps.period_year
FROM gl_period_statuses gps
,xla_analytical_hdrs_b xbh --Bug13498442
, gl_code_combinations gcc --Bug13498442
, xla_ac_bal_interim_gt tmp
, xla_ledger_options xlo
, xla_ledger_relationships_v xlr
WHERE gps.effective_period_num <= xlo.effective_period_num
AND gps.effective_period_num >= tmp.effective_period_num
AND gps.closing_status IN ('O', 'C', 'P')
AND gps.adjustment_period_flag = 'N'
AND gps.application_id = 101
AND gps.ledger_id = xlo.ledger_id
AND gcc.code_combination_id = tmp.code_combination_id
AND xlo.application_id = tmp.application_id
AND tmp.ledger_id = xlr.ledger_id
AND xlo.ledger_id = DECODE(xlr.ledger_category_code, 'ALC'
, xlr.primary_ledger_id, tmp.ledger_id)
--Begin Bug13498442
AND xbh.analytical_criterion_code = tmp.analytical_criterion_code
AND xbh.analytical_criterion_type_code = tmp.analytical_criterion_type_code
AND xbh.amb_context_code = tmp.amb_context_code
--End Bug13498442
GROUP BY tmp.application_id
, tmp.ledger_id
, tmp.code_combination_id
, tmp.analytical_criterion_code
, tmp.analytical_criterion_type_code
, tmp.amb_context_code
, decode (xbh.year_end_carry_forward_code,'Y','Y','A','Y','B',decode(gcc.account_type,'A','Y','L','Y','O','Y','N'),'N') --Bug13498442
, tmp.beginning_balance_dr
, tmp.beginning_balance_cr
, tmp.ac1
, tmp.ac2
, tmp.ac3
, tmp.ac4
, tmp.ac5
, gps.period_name
, gps.effective_period_num
, gps.period_year) xal_bal
ORDER BY xal_bal.application_id
, xal_bal.ledger_id
, xal_bal.code_combination_id
, xal_bal.analytical_criterion_code
, xal_bal.analytical_criterion_type_code
, xal_bal.amb_context_code
, xal_bal.ac1
, xal_bal.ac2
, xal_bal.ac3
, xal_bal.ac4
, xal_bal.ac5
, xal_bal.effective_period_num
, xal_bal.period_year)) tmp
ON ( stmp.application_id = tmp.application_id
AND stmp.ledger_id = tmp.ledger_id
AND stmp.code_combination_id = tmp.code_combination_id
AND stmp.analytical_criterion_code = tmp.analytical_criterion_code
AND stmp.analytical_criterion_type_code = tmp.analytical_criterion_type_code
AND stmp.amb_context_code = tmp.amb_context_code
AND NVL (stmp.ac1, ' ') = NVL (tmp.ac1, ' ')
AND NVL (stmp.ac2, ' ') = NVL (tmp.ac2, ' ')
AND NVL (stmp.ac3, ' ') = NVL (tmp.ac3, ' ')
AND NVL (stmp.ac4, ' ') = NVL (tmp.ac4, ' ')
AND NVL (stmp.ac5, ' ') = NVL (tmp.ac5, ' ')
AND stmp.effective_period_num = tmp.effective_period_num)
WHEN MATCHED THEN
UPDATE
SET stmp.beginning_balance_dr = tmp.xal_beginning_balance_dr
, stmp.beginning_balance_cr = tmp.xal_beginning_balance_cr
WHEN NOT MATCHED THEN
INSERT (stmp.application_id, stmp.ledger_id
, stmp.code_combination_id, stmp.analytical_criterion_code
, stmp.analytical_criterion_type_code
, stmp.amb_context_code, stmp.ac1, stmp.ac2, stmp.ac3
, stmp.ac4, stmp.ac5, stmp.period_balance_dr
, stmp.period_balance_cr, stmp.beginning_balance_dr
, stmp.beginning_balance_cr, stmp.period_name
, stmp.effective_period_num, stmp.period_year)
VALUES (tmp.application_id, tmp.ledger_id
, tmp.code_combination_id, tmp.analytical_criterion_code
, tmp.analytical_criterion_type_code, tmp.amb_context_code
, tmp.ac1, tmp.ac2, tmp.ac3, tmp.ac4, tmp.ac5
, tmp.period_balance_dr, tmp.period_balance_cr
, tmp.xal_beginning_balance_dr
, tmp.xal_beginning_balance_cr, tmp.period_name
, tmp.effective_period_num, tmp.period_year);
l_update_bal := 'UPDATE /*+ index(b,xla_ac_balances_N99) */xla_ac_balances b
SET last_update_date = '''||g_date||'''
, last_updated_by = '||g_user_id||'
, last_update_login = '||g_login_id||'
, program_update_date = '''||g_date||'''
, program_application_id = '||g_prog_appl_id||'
, program_id = '||g_prog_id||'
, request_id = '||g_req_id||'
,(period_balance_dr, period_balance_cr
, beginning_balance_dr, beginning_balance_cr) = (SELECT /*+ $parallel$ index(tmp,xla_ac_bgnbal_gt_U1) */
NVL (b.period_balance_dr, 0)
+ NVL (tmp.period_balance_dr, 0) period_balance_dr
, NVL (b.period_balance_cr, 0)
+ NVL (tmp.period_balance_cr, 0) period_balance_cr
, NVL (b.beginning_balance_dr, 0)
+ NVL (tmp.beginning_balance_dr, 0) beginning_balance_dr
, NVL (b.beginning_balance_cr, 0)
+ NVL (tmp.beginning_balance_cr, 0) beginning_balance_cr
FROM xla_ac_bal_interim_gt tmp
WHERE tmp.application_id = b.application_id
AND tmp.ledger_id = b.ledger_id
AND tmp.code_combination_id = b.code_combination_id
AND tmp.analytical_criterion_code = b.analytical_criterion_code
AND tmp.analytical_criterion_type_code = b.analytical_criterion_type_code
AND tmp.amb_context_code = b.amb_context_code
AND NVL (tmp.ac1, '' '') = NVL (b.ac1, '' '')
AND NVL (tmp.ac2, '' '') = NVL (b.ac2, '' '')
AND NVL (tmp.ac3, '' '') = NVL (b.ac3, '' '')
AND NVL (tmp.ac4, '' '') = NVL (b.ac4, '' '')
AND NVL (tmp.ac5, '' '') = NVL (b.ac5, '' '')
AND tmp.effective_period_num = b.effective_period_num)
WHERE ( b.application_id
, b.ledger_id
, b.code_combination_id
, b.analytical_criterion_code
, b.analytical_criterion_type_code
, b.amb_context_code
, NVL (b.ac1, '' '')
, NVL (b.ac2, '' '')
, NVL (b.ac3, '' '')
, NVL (b.ac4, '' '')
, NVL (b.ac5, '' '')
, b.effective_period_num
) IN (SELECT /*+ $parallel_1$ full(xal_bal1) */
xal_bal1.application_id
, xal_bal1.ledger_id
, xal_bal1.code_combination_id
, xal_bal1.analytical_criterion_code
, xal_bal1.analytical_criterion_type_code
, xal_bal1.amb_context_code
, NVL (xal_bal1.ac1, '' '')
, NVL (xal_bal1.ac2, '' '')
, NVL (xal_bal1.ac3, '' '')
, NVL (xal_bal1.ac4, '' '')
, NVL (xal_bal1.ac5, '' '')
, xal_bal1.effective_period_num
FROM xla_ac_bal_interim_gt xal_bal1)';
l_update_bal := REPLACE(l_update_bal,'$parallel$','parallel(tmp)');
l_update_bal := REPLACE(l_update_bal,'$parallel_1$','parallel(xal_bal1)');
l_update_bal := REPLACE(l_update_bal,'$parallel$','');
l_update_bal := REPLACE(l_update_bal,'$parallel_1$','');
(p_msg => 'AC l_update_bal_1:'||substr(l_update_bal, 1, 1000)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
(p_msg => 'AC l_update_bal_2:'||substr(l_update_bal, 1001, 1000)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
(p_msg => 'AC l_update_bal_3:'||substr(l_update_bal, 2001, 1000)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
(p_msg => 'AC l_update_bal_4:'||substr(l_update_bal, 3001, 1000)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
(p_msg => 'AC l_update_bal_5:'||substr(l_update_bal, 4001, 1000)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
(p_msg => 'AC l_update_bal_6:'||substr(l_update_bal, 5001, 999)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
EXECUTE IMMEDIATE l_update_bal;
l_update1_count := SQL%ROWCOUNT;
, p_msg => '# rows updated in xla_ac_balances : '
|| l_update1_count
, p_level => c_level_procedure
);
IF l_update1_count <> l_merge_count
THEN
-- insert rows only if the rows updated is not equal to the total no of rows in gt table
l_insert_bal := 'INSERT INTO xla_ac_balances xba
( application_id
, ledger_id
, code_combination_id
, analytical_criterion_code
, analytical_criterion_type_code
, amb_context_code
, ac1
, ac2
, ac3
, ac4
, ac5
, period_name
, period_year
, first_period_flag
, period_balance_dr
, period_balance_cr
, beginning_balance_dr
, beginning_balance_cr
, initial_balance_flag
, effective_period_num
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_update_date
, program_application_id
, program_id
, request_id
)
SELECT /*+ $parallel$ */
temp.application_id
, temp.ledger_id
, temp.code_combination_id
, temp.analytical_criterion_code
, temp.analytical_criterion_type_code
, temp.amb_context_code
, temp.ac1
, temp.ac2
, temp.ac3
, temp.ac4
, temp.ac5
, gps.period_name
, gps.period_year
, DECODE (gps.period_num, 1, ''Y'', ''N'') first_period_flag
, temp.period_balance_dr
, temp.period_balance_cr
, temp.beginning_balance_dr
, temp.beginning_balance_cr
, ''N'' initial_balance_flag
, temp.effective_period_num
, '''||g_date||'''
, '||g_user_id||'
, '''||g_date||'''
, '||g_user_id||'
, '||g_login_id||'
, '''||g_date||'''
, '||g_prog_appl_id||'
, '||g_prog_id||'
, '||g_req_id||'
FROM xla_ac_bal_interim_gt temp
, xla_analytical_hdrs_b xbh
, gl_code_combinations gcc
, gl_period_statuses gps
, xla_ledger_relationships_v xlr
WHERE xlr.ledger_id = temp.ledger_id
AND gps.ledger_id = DECODE(xlr.ledger_category_code,''ALC''
,xlr.primary_ledger_id , temp.ledger_id)
AND gps.effective_period_num = temp.effective_period_num
AND gps.application_id = 101
AND gps.adjustment_period_flag = ''N''
AND gps.closing_status IN (''O'', ''C'', ''P'')
AND gcc.code_combination_id = temp.code_combination_id
AND xbh.analytical_criterion_code = temp.analytical_criterion_code
AND xbh.analytical_criterion_type_code = temp.analytical_criterion_type_code
AND xbh.amb_context_code = temp.amb_context_code
AND xbh.balancing_flag <> ''N''
AND ( gps.period_year = SUBSTR (temp.effective_period_num, 1, 4)
OR xbh.year_end_carry_forward_code = ''A''
OR ( xbh.year_end_carry_forward_code = ''B''
AND gcc.account_type IN (''A'', ''L'', ''O'')
)
)
AND NOT EXISTS ( SELECT /*+ no_unnest $parallel_1$ */ 1
FROM xla_ac_balances xba
WHERE xba.application_id = temp.application_id
AND xba.ledger_id = temp.ledger_id
AND xba.code_combination_id = temp.code_combination_id
AND xba.analytical_criterion_code = temp.analytical_criterion_code
AND xba.analytical_criterion_type_code = temp.analytical_criterion_type_code
AND xba.amb_context_code = temp.amb_context_code
AND NVL (xba.ac1, '' '') = NVL (temp.ac1, '' '')
AND NVL (xba.ac2, '' '') = NVL (temp.ac2, '' '')
AND NVL (xba.ac3, '' '') = NVL (temp.ac3, '' '')
AND NVL (xba.ac4, '' '') = NVL (temp.ac4, '' '')
AND NVL (xba.ac5, '' '') = NVL (temp.ac5, '' '')
AND xba.period_name = gps.period_name)';
l_insert_bal := REPLACE(l_insert_bal,'$parallel$','parallel(temp)');
l_insert_bal := REPLACE(l_insert_bal,'$parallel_1$','parallel(xba)');
l_insert_bal := REPLACE(l_insert_bal,'$parallel$','');
l_insert_bal := REPLACE(l_insert_bal,'$parallel_1$','');
(p_msg => 'AC l_insert_bal_1:'||substr(l_insert_bal, 1, 1000)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
(p_msg => 'AC l_insert_bal_2:'||substr(l_insert_bal, 1001, 1000)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
(p_msg => 'AC l_insert_bal_3:'||substr(l_insert_bal, 2001, 1000)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
(p_msg => 'AC l_insert_bal_4:'||substr(l_insert_bal, 3001, 1000)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
(p_msg => 'AC l_insert_bal_5:'||substr(l_insert_bal, 4001, 1000)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
(p_msg => 'AC l_insert_bal_6:'||substr(l_insert_bal, 5001, 999)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
EXECUTE IMMEDIATE l_insert_bal;
l_insert2_count := SQL%ROWCOUNT;
, p_msg => ' # rows inserted into xla_ac_balances : '
|| l_insert2_count
, p_level => c_level_procedure
);
l_update_processed := 'UPDATE /*+ use_nl(ael) */xla_ae_lines ael
SET analytical_balance_flag = '''||g_postupdate_flag||'''
WHERE application_id = :'||l_processed_bind_count||'
AND analytical_balance_flag = '''||g_preupdate_flag||'''
AND (ae_header_id,ae_line_num) IN
( SELECT /*+ $parallel$ leading(aeh) */
ael.ae_header_id
,ael.ae_line_num
FROM xla_ae_headers aeh
, xla_ae_lines ael
, gl_period_statuses gps
, xla_ledger_options xlo
, xla_ledger_relationships_v xlr
$bal_concurrency$
WHERE aeh.accounting_entry_status_code = ''F''
AND aeh.application_id = :'||l_processed_bind_count||'
AND aeh.ledger_id = xlr.ledger_id
AND ael.ae_header_id = aeh.ae_header_id
AND aeh.balance_type_code = ''A''
AND ael.analytical_balance_flag = '''||g_preupdate_flag||'''
AND ael.application_id = aeh.application_id
AND xlo.ledger_id = DECODE(xlr.ledger_category_code, ''ALC''
,xlr.primary_ledger_id, xlr.ledger_id)
AND gps.ledger_id = xlo.ledger_id
AND gps.application_id = 101
AND gps.closing_status IN (''O'', ''C'', ''P'')
AND gps.effective_period_num <= xlo.effective_period_num
AND gps.adjustment_period_flag = ''N''
AND gps.period_name = aeh.period_name
' ;
l_update_processed := REPLACE(l_update_processed,'$bal_concurrency$',',xla_bal_concurrency_control bcc');
l_update_processed := l_update_processed||
' AND bcc.request_id = :'||l_processed_bind_count||'
AND bcc.accounting_batch_id = aeh.accounting_batch_id
AND bcc.application_id = aeh.application_id' ;
l_update_processed := REPLACE(l_update_processed,'$bal_concurrency$','');
l_update_processed := l_update_processed||
' AND aeh.accounting_batch_id = :'||l_processed_bind_count;
l_update_processed := l_update_processed||
' AND aeh.event_id = :'||l_processed_bind_count;
l_update_processed := l_update_processed||
' AND aeh.entity_id = :'||l_processed_bind_count;
l_update_processed := l_update_processed||
' AND aeh.ae_header_id = :'||l_processed_bind_count;
l_update_processed := l_update_processed||
' AND ael.ae_line_num = :'||l_processed_bind_count;
l_update_processed := l_update_processed || '
AND aeh.ledger_id = :'||l_processed_bind_count;
l_update_processed := l_update_processed||')';
l_update_processed := REPLACE(l_update_processed,'$parallel$','parallel(aeh)');
l_update_processed := REPLACE(l_update_processed,'$parallel$','');
(p_msg => 'AC l_update_processed_1:'||substr(l_update_processed, 1, 1000)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
(p_msg => 'AC l_update_processed_2:'||substr(l_update_processed, 1001, 1000)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
(p_msg => 'AC l_update_processed_3:'||substr(l_update_processed, 2001, 1000)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
(p_msg => 'AC l_update_processed_4:'||substr(l_update_processed, 3001, 1000)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
(p_msg => 'AC l_update_processed_5:'||substr(l_update_processed, 4001, 999)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
EXECUTE IMMEDIATE l_update_processed USING l_processed_bind_array(1),l_processed_bind_array(1);
EXECUTE IMMEDIATE l_update_processed USING l_processed_bind_array(1), l_processed_bind_array(1),l_processed_bind_array(2);
EXECUTE IMMEDIATE l_update_processed USING l_processed_bind_array(1),l_processed_bind_array(1),l_processed_bind_array(2),l_processed_bind_array(3);
EXECUTE IMMEDIATE l_update_processed USING l_processed_bind_array(1), l_processed_bind_array(1),l_processed_bind_array(2),l_processed_bind_array(3)
,l_processed_bind_array(4);
EXECUTE IMMEDIATE l_update_processed USING l_processed_bind_array(1),l_processed_bind_array(1),l_processed_bind_array(2),l_processed_bind_array(3)
,l_processed_bind_array(4),l_processed_bind_array(5);
EXECUTE IMMEDIATE l_update_processed USING l_processed_bind_array(1),l_processed_bind_array(1),l_processed_bind_array(2),l_processed_bind_array(3)
,l_processed_bind_array(4),l_processed_bind_array(5),l_processed_bind_array(6);
EXECUTE IMMEDIATE l_update_processed USING l_processed_bind_array(1),l_processed_bind_array(1),l_processed_bind_array(2),l_processed_bind_array(3)
,l_processed_bind_array(4),l_processed_bind_array(5),l_processed_bind_array(6),l_processed_bind_array(7);
EXECUTE IMMEDIATE l_update_processed USING l_processed_bind_array(1),l_processed_bind_array(1),l_processed_bind_array(2),l_processed_bind_array(3)
,l_processed_bind_array(4),l_processed_bind_array(5),l_processed_bind_array(6),l_processed_bind_array(7)
,l_processed_bind_array(8);
l_update2_count := SQL%ROWCOUNT;
, p_msg => '# Rows update in xla_ae_lines' || l_update2_count
, p_level => c_level_procedure
);
l_insert1_count NUMBER;
l_update1_count NUMBER;
l_insert2_count NUMBER;
l_update2_count NUMBER;
l_update_bal VARCHAR2(6000);
l_insert_bal VARCHAR2(6000);
l_update_processed VARCHAR2(5000);
l_summary_stmt VARCHAR2(6000):= 'INSERT INTO xla_ctrl_bal_interim_gt (
application_id
, ledger_id
, code_combination_id
, party_type_code
, party_id
, party_site_id
, period_name
, effective_period_num
, period_balance_dr
, period_balance_cr
, period_year
)
SELECT /*+ $parallel$ use_nl(aeh) use_nl(ael) */
ael.application_id
, ael.ledger_id
, ael.code_combination_id
, ael.party_type_code
, ael.party_id
, nvl(ael.party_site_id,-999) -- bug11887321
, gps.period_name
, gps.effective_period_num
, $period_balance_dr$
, $period_balance_cr$
, SUBSTR (gps.effective_period_num, 1, 4) period_year
FROM xla_ae_headers aeh
, xla_ae_lines ael
, gl_period_statuses gps
, xla_ledger_options xlo
, xla_ledger_relationships_v xlr
$bal_concurrency$
WHERE aeh.application_id = :'||l_summary_bind_count||'
AND aeh.accounting_entry_status_code = ''F''
AND aeh.balance_type_code = ''A''
AND ael.application_id = aeh.application_id
AND ael.ae_header_id = aeh.ae_header_id
AND ael.control_balance_flag = '''||g_preupdate_flag||'''
AND ael.ledger_id = aeh.ledger_id
AND xlr.ledger_id = aeh.ledger_id
AND xlo.application_id = aeh.application_id
AND xlo.ledger_id = DECODE (xlr.ledger_category_code , ''ALC''
, xlr.primary_ledger_id , xlr.ledger_id )
AND gps.ledger_id = xlo.ledger_id
AND gps.application_id = 101
AND gps.closing_status IN (''O'', ''C'', ''P'')
AND gps.effective_period_num <= xlo.effective_period_num
AND gps.adjustment_period_flag = ''N''
AND gps.period_name = aeh.period_name';
l_insert1_count := SQL%ROWCOUNT;
, p_msg => '# rows inserted in xla_ctrl_bal_interim_gt : '
|| l_insert1_count
, p_level => c_level_procedure
);
IF l_insert1_count = 0
THEN
IF (c_level_procedure >= g_log_level)
THEN
TRACE (p_module => l_log_module
, p_msg => 'No Records to process ' || l_insert1_count
, p_level => c_level_procedure
);
USING (SELECT period_balance_dr
, period_balance_cr
, SUM (lag_dr) OVER (PARTITION BY application_id, ledger_id, code_combination_id
, party_type_code, party_id, party_site_id
ORDER BY application_id
, ledger_id
, code_combination_id
, party_type_code
, party_id
, party_site_id
, effective_period_num) xal_beginning_balance_dr
, SUM (lag_cr) OVER (PARTITION BY application_id, ledger_id, code_combination_id
, party_type_code, party_id, party_site_id
ORDER BY application_id
, ledger_id
, code_combination_id
, party_type_code
, party_id
, party_site_id
, effective_period_num) xal_beginning_balance_cr
, application_id
, ledger_id
, code_combination_id
, party_type_code
, party_id
, party_site_id
, period_name
, effective_period_num
, period_year
FROM (SELECT /*+ leading(xag,xal_bal) */
xal_bal.application_id
, xal_bal.ledger_id
, xal_bal.code_combination_id
, xal_bal.party_type_code
, xal_bal.party_id
, xal_bal.party_site_id
, xal_bal.period_name
, xal_bal.effective_period_num
, xal_bal.period_balance_dr
, xal_bal.period_balance_cr
, xal_bal.period_year
, LAG (NVL (xal_bal.period_balance_dr, 0)
, 1
, NVL (xal_bal.beginning_balance_dr, 0)
) OVER (PARTITION BY xal_bal.application_id, xal_bal.ledger_id
, xal_bal.code_combination_id, xal_bal.party_type_code
, xal_bal.party_id, xal_bal.party_site_id
ORDER BY xal_bal.application_id
, xal_bal.ledger_id
, xal_bal.code_combination_id
, xal_bal.party_type_code
, xal_bal.party_id
, xal_bal.party_site_id
, xal_bal.effective_period_num) lag_dr
, LAG (NVL (xal_bal.period_balance_cr, 0)
, 1
, NVL (xal_bal.beginning_balance_cr, 0)
) OVER (PARTITION BY xal_bal.application_id, xal_bal.ledger_id
, xal_bal.code_combination_id, xal_bal.party_type_code
, xal_bal.party_id, xal_bal.party_site_id
ORDER BY xal_bal.application_id
, xal_bal.ledger_id
, xal_bal.code_combination_id
, xal_bal.party_type_code
, xal_bal.party_id
, xal_bal.party_site_id
, xal_bal.effective_period_num) lag_cr
FROM (SELECT tmp.application_id
, tmp.ledger_id
, tmp.code_combination_id
, tmp.party_type_code
, tmp.party_id
, tmp.party_site_id
, MAX
(DECODE
(gps.effective_period_num
, tmp.effective_period_num, tmp.period_balance_dr
, NULL
)
) period_balance_dr
, MAX
(DECODE
(gps.effective_period_num
, tmp.effective_period_num, tmp.period_balance_cr
, NULL
)
) period_balance_cr
, tmp.beginning_balance_dr
, tmp.beginning_balance_cr
, gps.period_name
, gps.effective_period_num
, gps.period_year
FROM gl_period_statuses gps
, xla_ctrl_bal_interim_gt tmp
, xla_ledger_options xlo
, xla_ledger_relationships_v xlr
WHERE gps.effective_period_num <= xlo.effective_period_num
AND gps.effective_period_num >= tmp.effective_period_num
AND gps.closing_status IN ('O', 'C', 'P')
AND gps.adjustment_period_flag = 'N'
AND gps.application_id = 101
AND gps.ledger_id = xlo.ledger_id
AND tmp.application_id = xlo.application_id
AND tmp.ledger_id = xlr.ledger_id
AND xlo.ledger_id = DECODE(xlr.ledger_category_code, 'ALC'
,xlr.primary_ledger_id, xlr.ledger_id)
GROUP BY tmp.application_id
, tmp.ledger_id
, tmp.code_combination_id
, tmp.party_type_code
, tmp.party_id
, tmp.party_site_id
, tmp.beginning_balance_dr
, tmp.beginning_balance_cr
, gps.period_name
, gps.effective_period_num
, gps.period_year) xal_bal
ORDER BY xal_bal.application_id
, xal_bal.ledger_id
, xal_bal.code_combination_id
, xal_bal.party_type_code
, xal_bal.party_id
, xal_bal.party_site_id
, xal_bal.effective_period_num
, xal_bal.period_year)) tmp
ON ( stmp.application_id = tmp.application_id
AND stmp.ledger_id = tmp.ledger_id
AND stmp.code_combination_id = tmp.code_combination_id
AND stmp.party_type_code = tmp.party_type_code
AND stmp.party_id = tmp.party_id
AND stmp.party_site_id = tmp.party_site_id
AND stmp.effective_period_num = tmp.effective_period_num)
WHEN MATCHED THEN
UPDATE
SET stmp.beginning_balance_dr = tmp.xal_beginning_balance_dr
, stmp.beginning_balance_cr = tmp.xal_beginning_balance_cr
WHEN NOT MATCHED THEN
INSERT (stmp.application_id, stmp.ledger_id
, stmp.code_combination_id, stmp.party_type_code
, stmp.party_id, stmp.party_site_id, stmp.period_balance_dr
, stmp.period_balance_cr, stmp.beginning_balance_dr
, stmp.beginning_balance_cr, stmp.period_name
, stmp.effective_period_num, stmp.period_year)
VALUES (tmp.application_id, tmp.ledger_id
, tmp.code_combination_id, tmp.party_type_code
, tmp.party_id, tmp.party_site_id, tmp.period_balance_dr
, tmp.period_balance_cr, tmp.xal_beginning_balance_dr
, tmp.xal_beginning_balance_cr, tmp.period_name
, tmp.effective_period_num, tmp.period_year);
l_update_bal := 'UPDATE /*+ ordered index(b,xla_control_balances_N99) */xla_control_balances b
SET last_update_date = '''||g_date||'''
, last_updated_by = '||g_user_id||'
, last_update_login = '||g_login_id||'
, program_update_date = '''||g_date||'''
, program_application_id = '||g_prog_appl_id||'
, program_id = '||g_prog_id||'
, request_id = '||g_req_id||'
,(period_balance_dr, period_balance_cr, beginning_balance_dr
, beginning_balance_cr) =
(SELECT /*+ $parallel$ index(tmp,xla_ctrl_bal_interim_gt_U1) */
NVL (b.period_balance_dr, 0)
+ NVL (tmp.period_balance_dr, 0) period_balance_dr
, NVL (b.period_balance_cr, 0)
+ NVL (tmp.period_balance_cr, 0) period_balance_cr
, NVL (b.beginning_balance_dr, 0)
+ NVL (tmp.beginning_balance_dr, 0) beginning_balance_dr
, NVL (b.beginning_balance_cr, 0)
+ NVL (tmp.beginning_balance_cr, 0) beginning_balance_cr
FROM xla_ctrl_bal_interim_gt tmp
WHERE tmp.application_id = b.application_id
AND tmp.ledger_id = b.ledger_id
AND tmp.code_combination_id = b.code_combination_id
AND tmp.party_type_code = b.party_type_code
AND tmp.party_id = b.party_id
AND tmp.party_site_id = b.party_site_id
AND tmp.effective_period_num = b.effective_period_num)
WHERE (b.application_id
, b.ledger_id
, b.code_combination_id
, b.party_type_code
, b.party_id
, b.party_site_id
, b.effective_period_num
) IN (
SELECT /*+ $parallel_1$ full(xal_bal1) */
xal_bal1.application_id
, xal_bal1.ledger_id
, xal_bal1.code_combination_id
, xal_bal1.party_type_code
, xal_bal1.party_id
, xal_bal1.party_site_id
, xal_bal1.effective_period_num
FROM xla_ctrl_bal_interim_gt xal_bal1)';
l_update_bal := REPLACE(l_update_bal,'$parallel$','parallel(tmp)');
l_update_bal := REPLACE(l_update_bal,'$parallel_1$','parallel(xal_bal1)');
l_update_bal := REPLACE(l_update_bal,'$parallel$','');
l_update_bal := REPLACE(l_update_bal,'$parallel_1$','');
(p_msg => 'CTRL: l_update_bal_1:'||substr(l_update_bal, 1, 1000)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
(p_msg => 'CTRL: l_update_bal_2:'||substr(l_update_bal, 1001, 1000)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
(p_msg => 'CTRL: l_update_bal_3:'||substr(l_update_bal, 2001, 1000)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
(p_msg => 'CTRL: l_update_bal_4:'||substr(l_update_bal, 3001, 1000)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
(p_msg => 'CTRL: l_update_bal_5:'||substr(l_update_bal, 4001, 1000)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
(p_msg => 'CTRL: l_update_bal_6:'||substr(l_update_bal, 5001, 999)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
EXECUTE IMMEDIATE l_update_bal;
l_update1_count:=SQL%ROWCOUNT;
, p_msg => '# rows updated in xla_control_balances : '
|| l_update1_count
, p_level => c_level_procedure
);
IF l_update1_count <> l_merge_count
THEN
-- insert rows only if the rows updated is not equal to the total no of rows in gt table
l_insert_bal := 'INSERT INTO xla_control_balances xba (
application_id
, ledger_id
, code_combination_id
, party_type_code
, party_id
, party_site_id
, period_name
, period_year
, first_period_flag
, period_balance_dr
, period_balance_cr
, beginning_balance_dr
, beginning_balance_cr
, initial_balance_flag
, effective_period_num
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_update_date
, program_application_id
, program_id
, request_id
)
SELECT /*+ $parallel$ */
temp.application_id
, temp.ledger_id
, temp.code_combination_id
, temp.party_type_code
, temp.party_id
, temp.party_site_id
, gps.period_name
, gps.period_year
, DECODE (gps.period_num, 1, ''Y'', ''N'') first_period_flag
, temp.period_balance_dr
, temp.period_balance_cr
, temp.beginning_balance_dr
, temp.beginning_balance_cr
, ''N'' initial_balance_flag
, temp.effective_period_num
, '''||g_date||'''
, '||g_user_id||'
, '''||g_date||'''
, '||g_user_id||'
, '||g_login_id||'
, '''||g_date||'''
, '||g_prog_appl_id||'
, '||g_prog_id||'
, '||g_req_id||'
FROM xla_ctrl_bal_interim_gt temp
, gl_period_statuses gps
, xla_ledger_relationships_v xlr
WHERE xlr.ledger_id = temp.ledger_id
AND gps.ledger_id = DECODE(xlr.ledger_category_code, ''ALC''
,xlr.primary_ledger_id , xlr.ledger_id)
AND gps.effective_period_num = temp.effective_period_num
AND gps.application_id = 101
AND gps.adjustment_period_flag = ''N''
AND gps.closing_status IN (''O'', ''C'', ''P'')
AND NOT EXISTS ( SELECT /*+ no_unnest $parallel_1$ */ 1
FROM xla_control_balances xba
WHERE xba.application_id = temp.application_id
AND xba.ledger_id = temp.ledger_id
AND xba.code_combination_id = temp.code_combination_id
AND xba.party_type_code = temp.party_type_code
AND xba.party_id = temp.party_id
AND xba.party_site_id = temp.party_site_id
AND xba.period_name = temp.period_name)';
l_insert_bal := REPLACE(l_insert_bal,'$parallel$','parallel(temp)');
l_insert_bal := REPLACE(l_insert_bal,'$parallel_1$','parallel(xba)');
l_insert_bal := REPLACE(l_insert_bal,'$parallel$','');
l_insert_bal := REPLACE(l_insert_bal,'$parallel_1$','');
(p_msg => 'CTRL: l_insert_bal_1:'||substr(l_insert_bal, 1, 1000)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
(p_msg => 'CTRL: l_insert_bal_2:'||substr(l_insert_bal, 1001, 1000)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
(p_msg => 'CTRL: l_insert_bal_3:'||substr(l_insert_bal, 2001, 1000)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
(p_msg => 'CTRL: l_insert_bal_4:'||substr(l_insert_bal, 3001, 1000)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
(p_msg => 'CTRL: l_insert_bal_5:'||substr(l_insert_bal, 4001, 1000)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
(p_msg => 'CTRL: l_insert_bal_6:'||substr(l_insert_bal, 5001, 999)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
EXECUTE IMMEDIATE l_insert_bal;
l_insert2_count := SQL%ROWCOUNT;
, p_msg => ' # rows inserted into xla_control_balances : '
|| l_insert2_count
, p_level => c_level_procedure
);
l_update_processed := 'UPDATE /*+ use_nl(ael) */xla_ae_lines ael
SET control_balance_flag = '''||g_postupdate_flag||'''
WHERE application_id = :'||l_processed_bind_count||'
AND control_balance_flag = '''||g_preupdate_flag||'''
AND (ae_header_id,ae_line_num) IN ( SELECT /*+ $parallel$ leading(aeh) */
ael.ae_header_id
,ael.ae_line_num
FROM xla_ae_headers aeh
, xla_ae_lines ael
, gl_period_statuses gps
, xla_ledger_options xlo
, xla_ledger_relationships_v xlr
$bal_concurrency$
WHERE aeh.accounting_entry_status_code = ''F''
AND aeh.application_id = :'||l_processed_bind_count||'
AND aeh.balance_type_code = ''A''
AND aeh.ledger_id = xlr.ledger_id
AND ael.ae_header_id = aeh.ae_header_id
AND ael.control_balance_flag = '''||g_preupdate_flag||'''
AND ael.application_id = aeh.application_id
AND xlo.ledger_id = DECODE(xlr.ledger_category_code, ''ALC''
,xlr.primary_ledger_id, xlr.ledger_id)
AND gps.ledger_id = xlo.ledger_id
AND gps.application_id = 101
AND gps.closing_status IN (''O'', ''C'', ''P'')
AND gps.effective_period_num <= xlo.effective_period_num
AND gps.adjustment_period_flag = ''N''
AND gps.period_name = aeh.period_name' ;
l_update_processed := REPLACE(l_update_processed,'$bal_concurrency$',',xla_bal_concurrency_control bcc');
l_update_processed := l_update_processed||
' AND bcc.request_id = :'||l_processed_bind_count||'
AND bcc.accounting_batch_id = aeh.accounting_batch_id
AND bcc.application_id = aeh.application_id' ;
l_update_processed := REPLACE(l_update_processed,'$bal_concurrency$','');
l_update_processed := l_update_processed||
' AND aeh.accounting_batch_id = :'||l_processed_bind_count;
l_update_processed := l_update_processed||
' AND aeh.event_id = :'||l_processed_bind_count;
l_update_processed := l_update_processed||
' AND aeh.entity_id = :'||l_processed_bind_count;
l_update_processed := l_update_processed||
' AND aeh.ae_header_id = :'||l_processed_bind_count;
l_update_processed := l_update_processed||
' AND ael.ae_line_num = :'||l_processed_bind_count;
l_update_processed := l_update_processed || '
AND aeh.ledger_id = :'||l_processed_bind_count;
l_update_processed := l_update_processed||')';
l_update_processed := REPLACE(l_update_processed,'$parallel$','parallel(aeh)');
l_update_processed := REPLACE(l_update_processed,'$parallel$','');
(p_msg => 'CTRL: l_update_processed_1:'||substr(l_update_processed, 1, 1000)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
(p_msg => 'CTRL: l_update_processed_2:'||substr(l_update_processed, 1001, 1000)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
(p_msg => 'CTRL: l_update_processed_3:'||substr(l_update_processed, 2001, 1000)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
(p_msg => 'CTRL: l_update_processed_4:'||substr(l_update_processed, 3001, 1000)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
(p_msg => 'CTRL: l_update_processed_5:'||substr(l_update_processed, 4001, 999)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
EXECUTE IMMEDIATE l_update_processed USING l_processed_bind_array(1),l_processed_bind_array(1);
EXECUTE IMMEDIATE l_update_processed USING l_processed_bind_array(1), l_processed_bind_array(1),l_processed_bind_array(2);
EXECUTE IMMEDIATE l_update_processed USING l_processed_bind_array(1),l_processed_bind_array(1),l_processed_bind_array(2),l_processed_bind_array(3);
EXECUTE IMMEDIATE l_update_processed USING l_processed_bind_array(1), l_processed_bind_array(1),l_processed_bind_array(2),l_processed_bind_array(3)
,l_processed_bind_array(4);
EXECUTE IMMEDIATE l_update_processed USING l_processed_bind_array(1),l_processed_bind_array(1),l_processed_bind_array(2),l_processed_bind_array(3)
,l_processed_bind_array(4),l_processed_bind_array(5);
EXECUTE IMMEDIATE l_update_processed USING l_processed_bind_array(1),l_processed_bind_array(1),l_processed_bind_array(2),l_processed_bind_array(3)
,l_processed_bind_array(4),l_processed_bind_array(5),l_processed_bind_array(6);
EXECUTE IMMEDIATE l_update_processed USING l_processed_bind_array(1),l_processed_bind_array(1),l_processed_bind_array(2),l_processed_bind_array(3)
,l_processed_bind_array(4),l_processed_bind_array(5),l_processed_bind_array(6),l_processed_bind_array(7);
EXECUTE IMMEDIATE l_update_processed USING l_processed_bind_array(1),l_processed_bind_array(1),l_processed_bind_array(2),l_processed_bind_array(3)
,l_processed_bind_array(4),l_processed_bind_array(5),l_processed_bind_array(6),l_processed_bind_array(7)
,l_processed_bind_array(8);
l_update2_count := SQL%ROWCOUNT;
, p_msg => ' # rows updated in xla_ae_lines : ' || l_update2_count
, p_level => c_level_procedure
);
, p_update_mode IN VARCHAR2
, p_execution_mode IN VARCHAR2
)
RETURN BOOLEAN
IS
l_log_module VARCHAR2 (240);
, p_msg => 'p_update_mode : ' || p_execution_mode
, p_level => c_level_exception
);
l_open_period_sql := 'SELECT SUM(
DECODE(xlo_effective_period_num, gps_effective_period_num,0,1)
)
FROM (
SELECT DISTINCT xlo.effective_period_num xlo_effective_period_num
,(SELECT MAX(gps.effective_period_num)
FROM gl_period_statuses gps
WHERE gps.application_id = 101
AND gps.ledger_id = xlo.ledger_id
AND gps.closing_status IN (''O'',''C'',''P'')
AND gps.adjustment_period_flag = ''N''
)gps_effective_period_num
, xlo.ledger_id
FROM xla_ledger_options xlo
,xla_ledger_relationships_v xlr
WHERE xlr.ledger_id = '||p_ledger_id || '
AND xlo.ledger_id = DECODE(xlr.ledger_category_code , ''ALC''
,xlr.primary_ledger_id, xlr.ledger_id)
AND xlo.application_id = '||p_application_id||'
)';
l_open_period_sql := 'SELECT SUM(
DECODE(xlo_effective_period_num, gps_effective_period_num,0,1)
)
FROM (
SELECT DISTINCT xlo.effective_period_num xlo_effective_period_num
,(SELECT MAX(gps.effective_period_num)
FROM gl_period_statuses gps
WHERE gps.application_id = 101
AND gps.ledger_id = xlo.ledger_id
AND gps.closing_status IN (''O'',''C'',''P'')
AND gps.adjustment_period_flag = ''N''
)gps_effective_period_num
, xlo.ledger_id
FROM xla_ledger_options xlo
,xla_ledger_relationships_v xlr
,xla_ae_headers xah
WHERE xlo.ledger_id = DECODE(xlr.ledger_category_code , ''ALC''
,xlr.primary_ledger_id, xlr.ledger_id)
AND xlo.application_id = '||p_application_id||'
AND xah.application_id = ' ||p_application_id ||'
AND xlr.ledger_id = xah.ledger_id';
IF p_update_mode IN ('A','F','M')
THEN
l_operation_code := 'A';
ELSIF p_update_mode = 'D'
THEN
l_operation_code := 'R'; --remove
|| 'Invalid value for Update Mode '|| p_update_mode
, p_level => c_level_exception
);
|| 'Invalid value for update mode '||p_update_mode
);
g_preupdate_flag := 'P';
g_postupdate_flag := 'Y';
g_preupdate_flag := 'Y';
g_postupdate_flag := 'P';
SELECT distinct effective_period_num
FROM xla_ledger_options
WHERE ledger_id = p_ledger_id
AND effective_period_num is not null;
SELECT count(1)
INTO l_count
FROM gl_period_statuses
WHERE application_id=101
AND ledger_id = p_ledger_id
AND effective_period_num = p_effective_period_num
AND closing_status in ('O','C','P')
AND adjustment_period_flag = 'N';
INSERT INTO xla_ac_balances
(application_id
, ledger_id
, code_combination_id
, analytical_criterion_code
, analytical_criterion_type_code
, amb_context_code
, ac1
, ac2
, ac3
, ac4
, ac5
, period_name
, first_period_flag
, effective_period_num
, initial_balance_flag
, creation_date
, created_by
, last_update_date
, last_updated_by
, beginning_balance_dr
, beginning_balance_cr
, period_year
, last_update_login
, program_update_date
, program_application_id
, program_id
, request_id
)
SELECT /*+ parallel(bal,24) */
bal.application_id
, bal.ledger_id
, bal.code_combination_id
, bal.analytical_criterion_code
, bal.analytical_criterion_type_code
, bal.amb_context_code
, bal.ac1
, bal.ac2
, bal.ac3
, bal.ac4
, bal.ac5
, gps.period_name
, DECODE (period_num, 1, 'Y', 'N') first_period_flag
, gps.effective_period_num
, 'N' initial_balance_flag
, g_date
, g_user_id
, g_date
, g_user_id
, DECODE (gps.period_year
, SUBSTR (bal.effective_period_num, 1, 4), ( NVL
(bal.beginning_balance_dr
, 0
)
+ NVL
(bal.period_balance_dr
, 0
)
)
, DECODE (SIGN ( ( NVL (bal.beginning_balance_dr, 0)
+ NVL (bal.period_balance_dr, 0)
)
- ( NVL (bal.beginning_balance_cr, 0)
+ NVL (bal.period_balance_cr, 0)
)
)
, 1, ( ( NVL (bal.beginning_balance_dr, 0)
+ NVL (bal.period_balance_dr, 0)
)
- ( NVL (bal.beginning_balance_cr, 0)
+ NVL (bal.period_balance_cr, 0)
)
)
, 0
)
) beginning_balance_dr
, DECODE (gps.period_year
, SUBSTR (bal.effective_period_num, 1, 4), ( NVL
(bal.beginning_balance_cr
, 0
)
+ NVL
(bal.period_balance_cr
, 0
)
)
, DECODE (SIGN ( ( NVL (bal.beginning_balance_dr, 0)
+ NVL (bal.period_balance_dr, 0)
)
- ( NVL (bal.beginning_balance_cr, 0)
+ NVL (bal.period_balance_cr, 0)
)
)
, -1, ( NVL (bal.beginning_balance_cr, 0)
+ NVL (bal.period_balance_cr, 0)
)
- ( NVL (bal.beginning_balance_dr, 0)
+ NVL (bal.period_balance_dr, 0)
)
, 0
)
) beginning_balance_cr
,gps.period_year
,g_login_id
,g_date
,g_prog_appl_id
,g_prog_id
,g_req_id
FROM gl_period_statuses gps
, xla_ac_balances bal
, gl_code_combinations gcc
, xla_analytical_hdrs_b xbh
, (select ledger_id
from xla_ledger_relationships_v
where (ledger_category_code IN ('PRIMARY','ALC')
and primary_ledger_id = p_ledger_id)
or (ledger_category_code = 'SECONDARY'
and ledger_id = p_ledger_id)
) xlr
WHERE gps.application_id = 101
AND gps.ledger_id = p_ledger_id
AND gps.closing_status IN ('O', 'C', 'P')
AND gps.adjustment_period_flag = 'N'
AND gps.effective_period_num <= p_effective_period_num
AND gps.effective_period_num > l_from_effective_period_num
AND bal.effective_period_num = l_from_effective_period_num
AND bal.ledger_id = xlr.ledger_id
AND gcc.code_combination_id = bal.code_combination_id
AND xbh.analytical_criterion_code = bal.analytical_criterion_code
AND xbh.analytical_criterion_type_code =
bal.analytical_criterion_type_code
AND xbh.amb_context_code = bal.amb_context_code
AND xbh.balancing_flag <> 'N'
AND ( gps.period_year = SUBSTR (bal.effective_period_num, 1, 4)
OR xbh.year_end_carry_forward_code = 'A'
OR ( xbh.year_end_carry_forward_code = 'B'
AND gcc.account_type IN ('A', 'L', 'O')
)
);
INSERT INTO xla_control_balances
(application_id
, ledger_id
, code_combination_id
, party_type_code
, party_id
, party_site_id
, period_name
, first_period_flag
, effective_period_num
, initial_balance_flag
, creation_date
, created_by
, last_update_date
, last_updated_by
, beginning_balance_dr
, beginning_balance_cr
, period_year
, last_update_login
, program_update_date
, program_application_id
, program_id
, request_id
)
SELECT /*+ parallel(bal,24) */
bal.application_id
, bal.ledger_id
, bal.code_combination_id
, bal.party_type_code
, bal.party_id
, bal.party_site_id
, gps.period_name
, DECODE (period_num, 1, 'Y', 'N') first_period_flag
, gps.effective_period_num
, 'N' initial_balance_flag
, g_date
, g_user_id
, g_date
, g_user_id
, DECODE (gps.period_year
, SUBSTR (bal.effective_period_num, 1, 4), ( NVL
(bal.beginning_balance_dr
, 0
)
+ NVL
(bal.period_balance_dr
, 0
)
)
, DECODE (SIGN ( ( NVL (bal.beginning_balance_dr, 0)
+ NVL (bal.period_balance_dr, 0)
)
- ( NVL (bal.beginning_balance_cr, 0)
+ NVL (bal.period_balance_cr, 0)
)
)
, 1, ( ( NVL (bal.beginning_balance_dr, 0)
+ NVL (bal.period_balance_dr, 0)
)
- ( NVL (bal.beginning_balance_cr, 0)
+ NVL (bal.period_balance_cr, 0)
)
)
, 0
)
) beginning_balance_dr
, DECODE (gps.period_year
, SUBSTR (bal.effective_period_num, 1, 4), ( NVL
(bal.beginning_balance_cr
, 0
)
+ NVL
(bal.period_balance_cr
, 0
)
)
, DECODE (SIGN ( ( NVL (bal.beginning_balance_dr, 0)
+ NVL (bal.period_balance_dr, 0)
)
- ( NVL (bal.beginning_balance_cr, 0)
+ NVL (bal.period_balance_cr, 0)
)
)
, -1, ( NVL (bal.beginning_balance_cr, 0)
+ NVL (bal.period_balance_cr, 0)
)
- ( NVL (bal.beginning_balance_dr, 0)
+ NVL (bal.period_balance_dr, 0)
)
, 0
)
) beginning_balance_cr
,gps.period_year
,g_login_id
,g_date
,g_prog_appl_id
,g_prog_id
,g_req_id
FROM gl_period_statuses gps
, xla_control_balances bal
,(select ledger_id
from xla_ledger_relationships_v
where (ledger_category_code IN ('PRIMARY','ALC')
and primary_ledger_id = p_ledger_id)
or (ledger_category_code = 'SECONDARY'
and ledger_id = p_ledger_id)
) xlr
WHERE gps.application_id = 101
AND gps.ledger_id = p_ledger_id
AND gps.closing_status IN ('O', 'C', 'P')
AND gps.adjustment_period_flag = 'N'
AND gps.effective_period_num <= p_effective_period_num
AND gps.effective_period_num > l_from_effective_period_num
AND bal.effective_period_num = l_from_effective_period_num
AND bal.ledger_id = xlr.ledger_id;
UPDATE xla_ledger_options
SET effective_period_num = p_effective_period_num
WHERE ledger_id = p_ledger_id
AND nvl(effective_period_num,-1) < p_effective_period_num;
TRACE (p_msg => '# rows updated in xla_ledger_options : ' || SQL%ROWCOUNT
, p_level => c_level_procedure
, p_module => l_log_module
);
TRACE (p_msg => 'xla_ledger_options updated with effective_period_num '||p_effective_period_num
, p_level => c_level_procedure
, p_module => l_log_module
);
SELECT application_id
,ledger_id
FROM xla_bal_concurrency_control
WHERE ledger_id = p_ledger_id
AND application_id IN ( SELECT * FROM TABLE(g_application_array))
FOR UPDATE NOWAIT; --Lock All the applications belonging to this ledger
SELECT l.ledger_category_code
, gps.effective_period_num
, adjustment_period_flag -- Bug 12613841
FROM gl_period_statuses gps
, gl_ledgers l
WHERE l.ledger_id = p_ledger_id
AND gps.ledger_id = l.ledger_id
AND gps.application_id = 101
AND gps.period_name = p_period_name;
SELECT COUNT(1) xlo_effperiod_count
FROM xla_ledger_options
WHERE ledger_id = p_ledger_id
AND effective_period_num is not null;
SELECT COUNT(1) bal_count
FROM dual
WHERE EXISTS (SELECT 1 FROM xla_control_balances where effective_period_num is null)
OR EXISTS (SELECT 1 FROM xla_ac_balances where effective_period_num is null);
SELECT COUNT(1) bal_sob_count
FROM dual
WHERE EXISTS (SELECT 1 FROM xla_control_balances WHERE ledger_id = p_ledger_id)
OR EXISTS (SELECT 1 FROM xla_ac_balances WHERE ledger_id = p_ledger_id);
SELECT count(1)
INTO l_not_nul_count
FROM xla_ledger_options
WHERE effective_period_num IS NOT NULL;
UPDATE xla_ledger_options xlo
SET effective_period_num = (SELECT gps.effective_period_num
FROM gl_period_statuses gps,gl_ledgers gll
WHERE gps.application_id= 101
AND gps.ledger_id = xlo.ledger_id
AND gll.ledger_id = xlo.ledger_id
AND gps.period_name = gll.FIRST_LEDGER_PERIOD_NAME
)
WHERE ledger_id in
(
SELECT ledger_id
FROM xla_ledger_relationships_v xlr
WHERE (xlr.primary_ledger_id = p_ledger_id OR ledger_id = p_ledger_id)
AND xlr.relationship_enabled_flag = 'Y'
AND xlr.ledger_category_code IN('ALC', 'PRIMARY', 'SECONDARY')
)
and effective_period_num is null;
UPDATE xla_ac_balances xab
SET EFFECTIVE_PERIOD_NUM = ( SELECT gps.effective_period_num
FROM gl_period_statuses gps
WHERE gps.ledger_id = xab.ledger_id
AND gps.application_id = 101
AND gps.adjustment_period_flag = 'N'
AND gps.period_name = xab.period_name
)
WHERE effective_period_num is null
AND ledger_id in
(
SELECT ledger_id
FROM xla_ledger_relationships_v xlr
WHERE (xlr.primary_ledger_id = p_ledger_id OR ledger_id = p_ledger_id)
AND xlr.relationship_enabled_flag = 'Y'
AND xlr.ledger_category_code IN('ALC', 'PRIMARY', 'SECONDARY')
);
, p_msg => '# Rows update in xla_ac_balances ' || SQL%ROWCOUNT
, p_level => c_level_procedure
);
UPDATE xla_control_balances xab
SET EFFECTIVE_PERIOD_NUM = ( SELECT gps.effective_period_num
FROM gl_period_statuses gps
WHERE gps.ledger_id = xab.ledger_id
AND gps.application_id = 101
AND gps.adjustment_period_flag = 'N'
AND gps.period_name=xab.period_name
)
WHERE effective_period_num is null
AND ledger_id in
(
SELECT ledger_id
FROM xla_ledger_relationships_v xlr
WHERE (xlr.primary_ledger_id = p_ledger_id OR ledger_id = p_ledger_id)
AND xlr.relationship_enabled_flag = 'Y'
AND xlr.ledger_category_code IN('ALC', 'PRIMARY', 'SECONDARY')
);
, p_msg => '# Rows update in xla_control_balances ' || SQL%ROWCOUNT
, p_level => c_level_procedure
);
Run xlabalupg.sql to use Update Subledger Accounting Balances program'
);
THEN -- No record in balances table. So, update xla_ledger_options and exit
--Bug 12673914
--Added condition effective_period_num < l_effective_period_num
UPDATE xla_ledger_options
SET effective_period_num = l_effective_period_num
WHERE ledger_id in
(
SELECT ledger_id
FROM xla_ledger_relationships_v xlr
WHERE (xlr.primary_ledger_id = p_ledger_id OR ledger_id = p_ledger_id)
AND xlr.relationship_enabled_flag = 'Y'
AND xlr.ledger_category_code IN('ALC', 'PRIMARY', 'SECONDARY')
)
AND nvl(effective_period_num,-1) < l_effective_period_num;
, p_msg => '# Rows update in xla_ledger_options ' || SQL%ROWCOUNT
, p_level => c_level_procedure
);
UPDATE xla_ac_balances xab
SET EFFECTIVE_PERIOD_NUM = ( SELECT gps.effective_period_num
FROM gl_period_statuses gps
WHERE gps.ledger_id = xab.ledger_id
AND gps.application_id = 101
AND gps.adjustment_period_flag = 'N'
AND gps.period_name = xab.period_name
)
WHERE effective_period_num is null
AND ledger_id in
(
SELECT ledger_id
FROM xla_ledger_relationships_v xlr
WHERE (xlr.primary_ledger_id = p_ledger_id OR ledger_id = p_ledger_id)
AND xlr.relationship_enabled_flag = 'Y'
AND xlr.ledger_category_code IN('ALC', 'PRIMARY', 'SECONDARY')
);
, p_msg => '# Rows update in xla_ac_balances ' || SQL%ROWCOUNT
, p_level => c_level_procedure
);
UPDATE xla_control_balances xab
SET EFFECTIVE_PERIOD_NUM = ( SELECT gps.effective_period_num
FROM gl_period_statuses gps
WHERE gps.ledger_id = xab.ledger_id
AND gps.application_id = 101
AND gps.adjustment_period_flag = 'N'
AND gps.period_name=xab.period_name
)
WHERE effective_period_num is null
AND ledger_id in
(
SELECT ledger_id
FROM xla_ledger_relationships_v xlr
WHERE (xlr.primary_ledger_id = p_ledger_id OR ledger_id = p_ledger_id)
AND xlr.relationship_enabled_flag = 'Y'
AND xlr.ledger_category_code IN('ALC', 'PRIMARY', 'SECONDARY')
);
, p_msg => '# Rows update in xla_control_balances ' || SQL%ROWCOUNT
, p_level => c_level_procedure
);
, p_value_2 => 'EXCEPTION:'|| 'Record cannot be inserted into XLA_BAL_CONCURRENCY_CONTROL '
);
delete xla_bal_concurrency_control where request_id = g_req_id; --Bug 13614923
delete xla_bal_concurrency_control where request_id = g_req_id; --Bug 13614923
delete xla_bal_concurrency_control where request_id = g_req_id; -- Bug13614923
delete xla_bal_concurrency_control where request_id = g_req_id; -- Bug13614923
delete xla_bal_concurrency_control where request_id = g_req_id; -- Bug13614923
PROCEDURE massive_update_srs (
p_errbuf OUT NOCOPY VARCHAR2
, p_retcode OUT NOCOPY NUMBER
, p_application_id IN NUMBER
, p_ledger_id IN NUMBER
, p_accounting_batch_id IN NUMBER
, p_update_mode IN VARCHAR2
)
IS
/*======================================================================+
| |
| Public Function |
| |
| Description |
| ----------- |
| Just the SRS wrapper for massive_update in batch mode |
| |
| Pseudo-code |
| ----------- |
| Call massive_update and assign its return code to |
| p_retcode |
| RETURN p_retcode (0=success, 1=warning, 2=error) |
| |
| Open issues |
| ----------- |
| |
| 1) Need to review the value assigned to p_errbuf |
| |
| |
| |
+======================================================================*/
l_commit_flag VARCHAR2 (1);
SELECT application_id
,ledger_id
FROM xla_bal_concurrency_control
WHERE application_id = p_application_id
AND ledger_id IN ( SELECT * FROM TABLE(g_ledger_array))
FOR UPDATE NOWAIT;
l_log_module := c_default_module || '.massive_update_srs';
, p_msg => 'p_update_mode ' || p_update_mode
, p_level => c_level_procedure
);
SELECT count(1)
INTO l_ledger_count
FROM xla_ledger_options
WHERE effective_period_num IS NOT NULL;
,'Balances upgrade script xlabalupg.sql has not been run. Run xlabalupg.sql to use Update Subledger Accounting Balances program'
);
, p_value_1 => 'xla_balances_calc_pkg.massive_update_srs'
, p_token_2 => 'ERROR'
, p_value_2 => 'EXCEPTION:'
|| 'p_application_id cannot be NULL'
);
, p_value_1 => 'xla_balances_calc_pkg.massive_update_srs'
, p_token_2 => 'ERROR'
, p_value_2 => 'EXCEPTION:'
|| 'p_ledger_id and p_accounting_batch_id cannot be NULL'
);
, p_value_1 => 'xla_balances_calc_pkg.massive_update_srs'
, p_token_2 => 'ERROR'
, p_value_2 => 'EXCEPTION:'
|| 'XLA_BAL_CONCURRENCY_CONTROL COULD NOT BE LOCKED. RESOURCE BUSY'
);
, p_update_mode => p_update_mode
, p_execution_mode => l_execution_mode
)
THEN
p_retcode := 0; --bug14255667
DELETE xla_bal_concurrency_control WHERE request_id = g_req_id; --Bug 13614923
DELETE xla_bal_concurrency_control WHERE request_id = g_req_id; --Bug 13614923
for i in ( SELECT distinct application_id
,ledger_id
FROM xla_bal_concurrency_control
WHERE application_id = p_application_id
AND ledger_id IN ( SELECT * FROM TABLE(g_ledger_array)))
loop
fnd_file.put_line
(fnd_file.LOG
, 'There is another request(s) running for the ledger_id : '
|| i.ledger_id
|| ' application_id : '
|| i.application_id);
'Pls. submit Subledger Accounting Balances Update Concurrent Program once the running request is completed');
DELETE xla_bal_concurrency_control WHERE request_id = g_req_id;
DELETE xla_bal_concurrency_control WHERE request_id = g_req_id; --Bug 13614923
DELETE xla_bal_concurrency_control WHERE request_id = g_req_id;
(p_location => 'xla_balances_calc_pkg.massive_update_srs');
END massive_update_srs;
FUNCTION massive_update (
p_application_id IN INTEGER
, p_ledger_id IN INTEGER
, p_entity_id IN INTEGER
, p_event_id IN INTEGER
, p_request_id IN INTEGER
, p_accounting_batch_id IN INTEGER
, p_update_mode IN VARCHAR2
, p_execution_mode IN VARCHAR2
)
RETURN BOOLEAN
IS
/*======================================================================+
| |
| Public Function |
| |
| Description |
| ----------- |
| Called in online accounting flow |
| |
| Pseudo-code |
| ----------- |
| Call massive_update and assign its return code to |
| p_retcode |
| RETURN p_retcode (0=success, 1=warning, 2=error) |
| |
| Open issues |
| ----------- |
| |
| 1) Need to review the value assigned to p_errbuf |
| |
| |
| |
+======================================================================*/
l_commit_flag VARCHAR2 (1);
SELECT application_id
,ledger_id
FROM xla_bal_concurrency_control
WHERE application_id = p_application_id
AND ledger_id IN ( SELECT * FROM TABLE(g_ledger_array))
FOR UPDATE NOWAIT;
l_log_module := c_default_module || '.massive_update';
, p_msg => 'p_update_mode ' || p_update_mode
, p_level => c_level_procedure
);
SELECT count(1)
INTO l_ledger_count
FROM xla_ledger_options
WHERE effective_period_num IS NOT NULL;
,'Balances upgrade script xlabalupg.sql has not been run. Run xlabalupg.sql to use Update Subledger Accounting Balances program'
);
, p_value_1 => 'xla_balances_calc_pkg.massive_update'
, p_token_2 => 'ERROR'
, p_value_2 => 'EXCEPTION:'
|| 'p_application_id cannot be NULL'
);
, argument4 => p_update_mode
);
, p_value_1 => 'xla_balances_calc_pkg.MASSIVE_UPDATE'
, p_token_2 => 'ERROR'
, p_value_2 => 'EXCEPTION:'
|| 'XLA_BAL_CONCURRENCY_CONTROL COULD NOT BE LOCKED. RESOURCE BUSY'
);
, p_update_mode => p_update_mode
, p_execution_mode => p_execution_mode
)
THEN
if p_entity_id is null then
DELETE FROM xla_bal_concurrency_control where request_id=g_req_id;
DELETE FROM xla_bal_concurrency_control where request_id=-1*p_entity_id;
DELETE FROM xla_bal_concurrency_control where request_id=g_req_id;
DELETE FROM xla_bal_concurrency_control where request_id=-1*p_entity_id;
for i in ( SELECT distinct application_id
,ledger_id
FROM xla_bal_concurrency_control
WHERE application_id = p_application_id
AND ledger_id IN ( SELECT * FROM TABLE(g_ledger_array)))
loop
fnd_file.put_line
(fnd_file.LOG
, 'There is another request(s) running for the ledger_id : '
|| i.ledger_id
|| ' application_id : '
|| i.application_id);
'Pls. submit Subledger Accounting Balances Update Concurrent Program once the running request is completed');
DELETE FROM xla_bal_concurrency_control where request_id=g_req_id;
DELETE FROM xla_bal_concurrency_control where request_id=-1*p_entity_id;
DELETE FROM xla_bal_concurrency_control where request_id=g_req_id;
DELETE FROM xla_bal_concurrency_control where request_id=-1*p_entity_id;
DELETE FROM xla_bal_concurrency_control where request_id=g_req_id;
DELETE FROM xla_bal_concurrency_control where request_id=-1*p_entity_id;
(p_location => 'xla_balances_calc_pkg.massive_update');
END massive_update;
FUNCTION single_update
(
p_application_id IN INTEGER
,p_ae_header_id IN INTEGER
,p_ae_line_num IN INTEGER
,p_update_mode IN VARCHAR2
) RETURN BOOLEAN
IS
l_return_value BOOLEAN ;
SELECT application_id
,ledger_id
FROM xla_bal_concurrency_control
WHERE application_id IN ( SELECT * FROM TABLE(g_application_array))
AND ledger_id IN ( SELECT * FROM TABLE(g_ledger_array))
FOR UPDATE NOWAIT;
l_log_module := C_DEFAULT_MODULE||'.single_update';
,p_msg => 'p_update_mode ' || p_update_mode
,p_level => C_LEVEL_PROCEDURE);
SELECT count(1)
INTO l_ledger_count
FROM xla_ledger_options
WHERE effective_period_num is not null;
,'Balances upgrade script xlabalupg.sql has not been run. Run xlabalupg.sql to use Update Subledger Accounting Balances program'
);
IF p_update_mode IS NULL
THEN
IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
trace
(p_module => l_log_module
,p_msg => 'EXCEPTION:' ||'p_update_mode cannot be NULL'
,p_level => C_LEVEL_EXCEPTION
);
,p_value_2 => 'EXCEPTION:' ||'p_update_mode cannot be NULL');
ELSIF p_update_mode NOT IN ('A', 'D', 'F')
THEN
IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
trace
(p_module => l_log_module
,p_msg => 'EXCEPTION:' || 'Unsupported value for p_update_mode: ' || p_update_mode
,p_level => C_LEVEL_EXCEPTION
);
,p_value_2 => 'EXCEPTION:' ||'Unsupported value for p_update_mode: ' || p_update_mode);
, p_value_1 => 'xla_balances_calc_pkg.MASSIVE_UPDATE'
, p_token_2 => 'ERROR'
, p_value_2 => 'EXCEPTION:'
|| 'XLA_BAL_CONCURRENCY_CONTROL COULD NOT BE LOCKED. RESOURCE BUSY'
);
, p_update_mode => p_update_mode
, p_execution_mode => l_execution_mode
);
DELETE FROM xla_bal_concurrency_control where request_id=-1*p_ae_header_id;
DELETE FROM xla_bal_concurrency_control where request_id=-1*p_ae_header_id;
for i in ( SELECT distinct application_id
,ledger_id
FROM xla_bal_concurrency_control
WHERE application_id = p_application_id
AND ledger_id IN ( SELECT * FROM TABLE(g_ledger_array)))
loop
fnd_file.put_line
(fnd_file.LOG
, 'There is another request(s) running for the ledger_id : '
|| i.ledger_id
|| ' application_id : '
|| i.application_id);
'Pls. submit Subledger Accounting Balances Update Concurrent Program once the running request is completed');
DELETE FROM xla_bal_concurrency_control where request_id=-1*p_ae_header_id;
DELETE FROM xla_bal_concurrency_control where request_id=-1*p_ae_header_id;
DELETE FROM xla_bal_concurrency_control where request_id=-1*p_ae_header_id;
(p_location => 'xla_balances_calc_pkg.single_update');
END single_update;
SELECT gcc.reference3
INTO l_qualifier_value
FROM gl_code_combinations gcc
WHERE gcc.code_combination_id = p_code_combination_id;
SELECT xsl.control_account_type_code
INTO l_je_source_name
FROM xla_subledgers xsl
WHERE xsl.application_id = p_application_id;
l_query := 'SELECT count(1)
FROM dual
where exists(select 1
from xla_ae_lines xal
,xla_ae_line_acs xac
,gl_period_statuses gps1
,gl_period_statuses gps2
,xla_ae_headers xah
where xah.application_id = :'||to_number(l_bind_count)||'
and xah.ledger_id = :'||to_number(l_bind_count+1)||'
and xah.accounting_date between gps1.start_date and gps2.end_date
and xal.application_id = xah.application_id
and xal.ae_header_id = xah.ae_header_id
and xal.analytical_balance_flag ='|| '''P'''||'
and xac.ae_header_id = xal.ae_header_id
and xac.ae_line_num = xal.ae_line_num
and xac.analytical_criterion_code like :'||to_number(l_bind_count+2)||'
and xac.analytical_criterion_type_code like :'||to_number(l_bind_count+3)||'
and xac.amb_context_code = :'||to_number(l_bind_count+4)||'
and gps1.application_id = 101
and gps1.ledger_id= :'||to_number(l_bind_count+5)||'
and gps1.effective_period_num = :'||to_number(l_bind_count+6)||'
and gps2.application_id = gps1.application_id
and gps2.ledger_id = gps1.ledger_id
and gps2.effective_period_num = :'||to_number(l_bind_count+7);