[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_insert_buf VARCHAR2(4000); -- holds dynamic insert stmt
SELECT
conversion_status,
period_counter_start,
period_counter_converted,
fixed_rate_conversion
FROM
fa_mc_conversion_history
WHERE
set_of_books_id = p_rsob_id AND
book_type_code = p_book_type_code;
SELECT
mbc.primary_set_of_books_id,
gls.set_of_books_id,
mbc.primary_currency_code,
bc.book_class
FROM
gl_sets_of_books gls,
fa_book_controls bc,
fa_mc_book_controls mbc
WHERE
mbc.book_type_code = p_book_type_code AND
mbc.set_of_books_id = gls.set_of_books_id AND
bc.book_type_code = mbc.book_type_code AND
gls.name = p_reporting_book;
insert_balances(
l_rsob_id);
insert_ret_earnings(
p_book_type_code,
l_rsob_id);
SELECT count(*)
FROM
fa_book_controls bc,
fa_deprn_periods dp
WHERE
bc.book_type_code = p_book_type_code AND
dp.book_type_code = p_book_type_code AND
dp.period_counter between
(bc.initial_period_counter + 1) and
p_end_pc - 1 AND
(dp.depreciation_batch_id is NULL AND
dp.retirement_batch_id is NULL AND
dp.reclass_batch_id is NULL AND
dp.transfer_batch_id is NULL AND
dp.addition_batch_id is NULL AND
dp.adjustment_batch_id is NULL AND
dp.deferred_deprn_batch_id is NULL AND
dp.cip_addition_batch_id is NULL AND
dp.cip_adjustment_batch_id is NULL AND
dp.cip_reclass_batch_id is NULL AND
dp.cip_retirement_batch_id is NULL AND
dp.cip_reval_batch_id is NULL AND
dp.cip_transfer_batch_id is NULL AND
dp.reval_batch_id is NULL AND
dp.deprn_adjustment_batch_id is NULL);
SELECT bc.gl_posting_allowed_flag
INTO l_allow_posting
FROM fa_book_controls bc
WHERE bc.book_type_code = p_book_type_code;
SELECT nvl(sum(maj.adjustment_amount),0),
nvl(sum(aj.adjustment_amount),0),
aj.code_combination_id,
aj.debit_credit_flag
FROM
fa_mc_adjustments maj,
fa_adjustments aj,
fa_mc_conversion_rates cr
WHERE
cr.set_of_books_id = p_rsob_id AND
maj.set_of_books_id = cr.set_of_books_id AND
cr.book_type_code = p_book_type_code AND
maj.book_type_code = cr.book_type_code AND
aj.asset_id = cr.asset_id AND
aj.code_combination_id = maj.code_combination_id AND
aj.debit_credit_flag = maj.debit_credit_flag AND
aj.distribution_id = maj.distribution_id AND
aj.asset_id = maj.asset_id AND
aj.book_type_code = cr.book_type_code AND
aj.adjustment_type = maj.adjustment_type AND
aj.adjustment_type not in ('RESERVE',
'EXPENSE',
'REVAL RESERVE')
GROUP BY
aj.code_combination_id,
aj.debit_credit_flag;
SELECT distinct last_period_counter
FROM fa_mc_conversion_rates
WHERE
set_of_books_id = p_rsob_id AND
book_type_code = p_book_type_code;
SELECT distinct deprn_reserve_je_line_num, je_header_id
FROM fa_mc_deprn_detail
where period_counter = l_pc AND
book_type_code = p_book_type_code AND
set_of_books_id = p_rsob_id AND
deprn_reserve_je_line_num is not null AND
je_header_id is not null;
select
gjl.code_combination_id
from
gl_je_lines gjl
where
gjl.je_header_id = l_header_id AND
gjl.je_line_num = l_line_num;
SELECT
sum(mdd.deprn_reserve),
sum(dd.deprn_reserve)
FROM
fa_deprn_detail dd,
fa_mc_deprn_detail mdd,
fa_mc_conversion_rates cr
WHERE
cr.last_period_counter = l_pc AND
cr.set_of_books_id = p_rsob_id AND
cr.book_type_code = p_book_type_code AND
mdd.period_counter = cr.last_period_counter AND
mdd.set_of_books_id = cr.set_of_books_id AND
mdd.book_type_code = cr.book_type_code AND
mdd.deprn_reserve_je_line_num = l_line_num AND
dd.deprn_reserve_je_line_num =
mdd.deprn_reserve_je_line_num AND
dd.period_counter = mdd.period_counter AND
dd.book_type_code = mdd.book_type_code AND
mdd.asset_id = cr.asset_id AND
dd.asset_id = mdd.asset_id AND
dd.distribution_id = mdd.distribution_id;
SELECT
dd.distribution_id,
dh.code_combination_id,
cb.deprn_reserve_acct,
cb.reserve_account_ccid
INTO
l_dist_id,
l_dist_ccid,
l_rsv_account,
l_account_ccid
FROM
fa_deprn_detail dd,
fa_category_books cb,
fa_asset_history ah,
fa_distribution_history dh
WHERE
dd.book_type_code = p_book_type_code AND
dd.period_counter = l_pc AND
dd.deprn_reserve_je_line_num = l_line_num AND
dd.distribution_id = dh.distribution_id AND
dd.asset_id = ah.asset_id AND
ah.category_id = cb.category_id AND
ah.date_ineffective is null AND
cb.book_type_code = dd.book_type_code AND
rownum = 1;
SELECT distinct deprn_expense_je_line_num, je_header_id
FROM fa_mc_deprn_detail
where period_counter = l_pc AND
book_type_code = p_book_type_code AND
set_of_books_id = p_rsob_id and
deprn_expense_je_line_num is not null and
je_header_id is not null;
SELECT
gjl.code_combination_id
FROM
gl_je_lines gjl
WHERE
gjl.je_header_id = l_header_id AND
gjl.je_line_num = l_line_num;
SELECT
nvl(sum(dd.deprn_amount),0),
nvl(sum(mdd.deprn_amount),0)
FROM
fa_deprn_detail dd,
fa_mc_deprn_detail mdd
WHERE
mdd.period_counter = l_pc AND
mdd.set_of_books_id = p_rsob_id AND
mdd.book_type_code = p_book_type_code AND
mdd.deprn_expense_je_line_num = l_line_num AND
dd.period_counter = mdd.period_counter AND
dd.book_type_code = mdd.book_type_code AND
dd.deprn_expense_je_line_num =
mdd.deprn_expense_je_line_num AND
mdd.asset_id = dd.asset_id AND
mdd.distribution_id = dd.distribution_id;
SELECT
aj.code_combination_id,
nvl(sum(dd.reval_reserve),0),
nvl(sum(mdd.reval_reserve),0)
FROM
fa_adjustments aj,
fa_deprn_detail dd,
fa_mc_deprn_detail mdd,
fa_mc_conversion_rates cr
WHERE
cr.set_of_books_id = p_rsob_id AND
cr.book_type_code = p_book_type_code AND
cr.asset_id = dd.asset_id AND
cr.last_period_counter = dd.period_counter AND
dd.book_type_code = p_book_type_code AND
dd.asset_id = aj.asset_id AND
dd.book_type_code = aj.book_type_code AND
dd.distribution_id = aj.distribution_id AND
mdd.period_counter = dd.period_counter AND
mdd.book_type_code = dd.book_type_code AND
mdd.asset_id = dd.asset_id AND
mdd.distribution_id = dd.distribution_id AND
mdd.set_of_books_id = p_rsob_id AND
aj.adjustment_type = 'REVAL RESERVE'
GROUP BY
aj.code_combination_id;
SELECT distinct reval_amort_je_line_num
FROM fa_mc_deprn_detail
where period_counter = l_pc AND
book_type_code = p_book_type_code AND
set_of_books_id = p_rsob_id AND
reval_amort_je_line_num is not null;
SELECT
gs.code_combination_id
FROM
gl_je_lines gs,
gl_je_headers gh,
gl_je_batches gb,
fa_deprn_periods dp
WHERE
dp.period_counter = l_pc AND
dp.book_type_code = p_book_type_code AND
dp.reval_batch_id = gb.je_batch_id AND
gh.je_batch_id = gb.je_batch_id AND
gs.je_header_id = gh.je_header_id AND
gs.je_line_num = l_line_num;
SELECT
nvl(sum(dd.reval_amortization),0),
nvl(sum(mdd.reval_amortization),0)
FROM
fa_mc_deprn_detail mdd,
fa_deprn_detail dd
WHERE
mdd.book_type_code = p_book_type_code AND
mdd.period_counter = l_pc AND
mdd.set_of_books_id = p_rsob_id AND
mdd.reval_amort_je_line_num = l_line_num AND
dd.book_type_code = mdd.book_type_code AND
dd.period_counter = mdd.period_counter AND
dd.reval_amort_je_line_num =
mdd.reval_amort_je_line_num AND
mdd.asset_id = dd.asset_id AND
mdd.distribution_id = dd.distribution_id;
SELECT
nvl(sum(dd.deferred_deprn_reserve_amount),0),
nvl(sum(mdd.deferred_deprn_reserve_amount),0),
dd.deferred_deprn_reserve_ccid
FROM
fa_deferred_deprn dd,
fa_mc_deferred_deprn mdd,
fa_mc_conversion_rates cr
WHERE
cr.set_of_books_id = p_rsob_id AND
cr.book_type_code = p_book_type_code AND
cr.asset_id = dd.asset_id AND
dd.tax_book_type_code = cr.book_type_code AND
mdd.tax_book_type_code = cr.book_type_code AND
mdd.asset_id = dd.asset_id AND
mdd.deferred_deprn_reserve_ccid =
dd.deferred_deprn_reserve_ccid AND
dd.distribution_id = mdd.distribution_id AND
dd.tax_period_counter = mdd.tax_period_counter AND
dd.corp_period_counter = mdd.corp_period_counter AND
dd.je_header_id = mdd.je_header_id AND
dd.reserve_je_line_num = mdd.reserve_je_line_num
GROUP BY
dd.deferred_deprn_reserve_ccid;
SELECT
nvl(sum(dd.deferred_deprn_expense_amount),0),
nvl(sum(mdd.deferred_deprn_expense_amount),0),
dd.deferred_deprn_expense_ccid
FROM
fa_deferred_deprn dd,
fa_mc_deferred_deprn mdd,
fa_mc_conversion_rates cr
WHERE
cr.set_of_books_id = p_rsob_id AND
cr.book_type_code = p_book_type_code AND
cr.asset_id = dd.asset_id AND
dd.tax_book_type_code = cr.book_type_code AND
mdd.tax_book_type_code = cr.book_type_code AND
mdd.asset_id = dd.asset_id AND
mdd.deferred_deprn_expense_ccid =
dd.deferred_deprn_expense_ccid AND
dd.distribution_id = mdd.distribution_id AND
dd.tax_period_counter = mdd.tax_period_counter AND
dd.corp_period_counter = mdd.corp_period_counter AND
dd.je_header_id = mdd.je_header_id AND
dd.expense_je_line_num = mdd.expense_je_line_num AND
dd.tax_period_counter between G_start_pc and
G_end_pc
GROUP BY
dd.deferred_deprn_expense_ccid;
SELECT
gls.chart_of_accounts_id,
gls.ret_earn_code_combination_id
INTO G_coa_id,
G_re_ccid
FROM
gl_sets_of_books gls,
fa_mc_book_controls bc
WHERE
bc.book_type_code = p_book_type_code AND
bc.set_of_books_id = p_rsob_id AND
gls.set_of_books_id = bc.primary_set_of_books_id;
SELECT count(*)
INTO l_segcount
FROM fnd_id_flex_segments
WHERE enabled_flag = 'Y'
AND id_flex_num = G_coa_id
AND application_id = 101
AND id_flex_code = 'GL#';
PROCEDURE insert_rec(
p_rsob_id IN NUMBER,
p_entered_cr IN NUMBER,
p_entered_dr IN NUMBER,
p_accounted_cr IN NUMBER,
p_accounted_dr IN NUMBER,
p_ccid IN NUMBER) IS
/* ************************************************************************
This procedure inserts a row into gl_interface and obtains the segment
values from gl_code_combination using the CCID. We get all the segment
values for each CCID since it is required for the GROUP BY in
insert_ret_earnings procedure. We insert the entered and accounted
amounts for each CCID.
************************************************************************ */
BEGIN
INSERT INTO gl_interface (
set_of_books_id,
status,
code_combination_id,
user_je_source_name,
user_je_category_name,
date_created,
accounting_date,
entered_cr,
entered_dr,
accounted_cr,
accounted_dr,
currency_code,
actual_flag,
created_by,
group_id,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20,
segment21,
segment22,
segment23,
segment24,
segment25,
segment26,
segment27,
segment28,
segment29,
segment30)
SELECT
p_rsob_id,
G_status,
p_ccid,
G_source_name,
G_category_name,
sysdate,
G_accounting_date,
p_entered_cr,
p_entered_dr,
p_accounted_cr,
p_accounted_dr,
G_from_currency,
G_actual_flag,
-1,
G_group_id,
glcc.segment1,
glcc.segment2,
glcc.segment3,
glcc.segment4,
glcc.segment5,
glcc.segment6,
glcc.segment7,
glcc.segment8,
glcc.segment9,
glcc.segment10,
glcc.segment11,
glcc.segment12,
glcc.segment13,
glcc.segment14,
glcc.segment15,
glcc.segment16,
glcc.segment17,
glcc.segment18,
glcc.segment19,
glcc.segment20,
glcc.segment21,
glcc.segment22,
glcc.segment23,
glcc.segment24,
glcc.segment25,
glcc.segment26,
glcc.segment27,
glcc.segment28,
glcc.segment29,
glcc.segment30
FROM gl_code_combinations glcc
WHERE glcc.code_combination_id = p_ccid AND
glcc.chart_of_accounts_id = G_coa_id;
calling_fn => 'fa_mc_upg3_pkg.insert_rec');
END insert_rec;
PROCEDURE insert_ret_earnings (
p_book_type_code IN VARCHAR2,
p_rsob_id IN NUMBER) IS
/* ************************************************************************
This procedure created the balancing entry to retained earnings for a
given balancing segment. After all the CCID balances have been obtained
and inserted into GL_INTERFACE, we will have to balance each balancing
segment. This is because we do not obtain the balances of revenue/expense
accounts for past years and this will cause the entries in GL_INTERFACE
to be out of balance. We use the template RE ccid in gl_sets_of_books
in the dynamic sql and group by the balancing segment to create the plug
to the correct retained earning account.
************************************************************************ */
insert_cursor INTEGER; -- Handles the insert cursor
'Inserting retained earnings in GL_INTERFACE',
'start');
G_insert_buf := '';
G_insert_buf := G_insert_buf ||
'INSERT INTO gl_interface(' ||
'status,' ||
'set_of_books_id,' ||
'user_je_source_name,' ||
'user_je_category_name,' ||
'currency_code,' ||
'date_created,' ||
'created_by,' ||
'accounting_date,' ||
'actual_flag,' ||
'entered_cr,' ||
'entered_dr,' ||
'accounted_cr,' ||
'accounted_dr,' ||
G_flex_buf ||
'group_id) ' ;
G_insert_buf := G_insert_buf ||
'SELECT ' ||
':status' || ',' ||
':rsob_id' || ',' ||
':source' || ',' ||
':category' || ',' ||
':fcurrency' || ',' ||
':date_created' || ',' ||
-1 || ',' ||
':acc_date'|| ',' ||
':actual_flag' || ',' ||
'decode(sign(sum(nvl(gli.entered_dr, 0) - ' ||
'nvl(gli.entered_cr, 0))), 1, ' ||
'(sum(nvl(gli.entered_dr, 0) - ' ||
'nvl(gli.entered_cr,0))), 0), ' ||
'decode(sign(sum(nvl(gli.entered_dr, 0) - ' ||
'nvl(gli.entered_cr, 0))), -1, ' ||
'(sum(nvl(gli.entered_cr, 0) - ' ||
'nvl(gli.entered_dr, 0))), 0), ' ||
'decode(sign(sum(nvl(gli.accounted_dr, 0) - ' ||
'nvl(gli.accounted_cr, 0))), 1, ' ||
'sum(nvl(gli.accounted_dr, 0) - ' ||
'nvl(gli.accounted_cr, 0)), 0), '||
'decode(sign(sum(nvl(gli.accounted_dr, 0) - ' ||
'nvl(gli.accounted_cr, 0))), -1, ' ||
'sum(nvl(gli.accounted_cr, 0) - ' ||
'nvl(gli.accounted_dr, 0)), 0), ';
G_insert_buf := G_insert_buf ||
'min(gli.' || G_bal_seg_col || '),';
G_insert_buf := G_insert_buf ||
'min(glcc.' ||
coa_structure(i) || '),';
G_insert_buf := G_insert_buf || ':group_id' ;
G_insert_buf := G_insert_buf ||
' FROM ' ||
'gl_code_combinations glcc,' ||
'gl_interface gli ' ||
'WHERE ' ||
'gli.user_je_source_name = :source and ' ||
'gli.set_of_books_id = :rsob_id and ' ||
'gli.group_id = :group_id and ' ||
'glcc.chart_of_accounts_id = :coa_id and ' ||
'glcc.template_id is NULL and ' ||
'glcc.code_combination_id = :re_ccid ' ||
'GROUP BY ' ||
'gli.' || G_bal_seg_col ;
buf1 := SUBSTRB(G_insert_buf,200,200);
buf2 := SUBSTRB(G_insert_buf,400,200);
buf3 := SUBSTRB(G_insert_buf,600,200);
buf4 := SUBSTRB(G_insert_buf,800,200);
buf5 := SUBSTRB(G_insert_buf,1000,200);
buf6 := SUBSTRB(G_insert_buf,1200,200);
buf7 := SUBSTRB(G_insert_buf,1400,200);
buf8 := SUBSTRB(G_insert_buf,1600,200);
buf9 := SUBSTRB(G_insert_buf,1800,200);
buf10 := SUBSTRB(G_insert_buf,2000,200);
fa_rx_conc_mesg_pkg.log('Insert buf is: ' ||
SUBSTRB(G_insert_buf,1,199));
dbms_output.put_line('Insert buf is: ' || SUBSTRB(G_insert_buf,1,199));
-- OPEN the insert cursor
insert_cursor := dbms_sql.open_cursor;
fa_debug_pkg.add('insert_ret_earnings',
'Open cursor',
'success');
-- parse the insert smt
dbms_sql.parse(insert_cursor, G_insert_buf, dbms_sql.v7);
fa_debug_pkg.add('insert_ret_earnings',
'Parse cursor',
'success');
dbms_sql.bind_variable(insert_cursor, ':category',G_category_name );
dbms_sql.bind_variable(insert_cursor, ':status', G_status);
dbms_sql.bind_variable(insert_cursor, ':actual_flag', G_actual_flag);
dbms_sql.bind_variable(insert_cursor, ':fcurrency', G_from_currency);
dbms_sql.bind_variable(insert_cursor, ':date_created', sysdate);
dbms_sql.bind_variable(insert_cursor, ':acc_date',G_accounting_date);
dbms_sql.bind_variable(insert_cursor, ':source',G_source_name );
dbms_sql.bind_variable(insert_cursor, ':group_id', G_group_id);
dbms_sql.bind_variable(insert_cursor, ':rsob_id', p_rsob_id);
dbms_sql.bind_variable(insert_cursor, ':coa_id', G_coa_id);
dbms_sql.bind_variable(insert_cursor, ':re_ccid', G_re_ccid);
l_row_count := dbms_sql.execute(insert_cursor);
fa_debug_pkg.add('insert_ret_earnings',
'Number of retained earnings rows inserted',
l_row_count);
dbms_sql.close_cursor(insert_cursor);
'Inserting retained earnings in GL_INTERFACE',
'success');
calling_fn => 'fa_mc_upg3_pkg.insert_ret_earnings');
END insert_ret_earnings;
PROCEDURE insert_balances(
p_rsob_id IN NUMBER) IS
/* ************************************************************************
This procedure loops thro each of the global arrays that hold the CCID's
and the entered and accounted amounts and calls inert_rec to insert the
row in GL_INTERFACE.
************************************************************************ */
BEGIN
if (g_print_debug) then
fa_debug_pkg.add('calculate_balances',
'Inserting account balances in GL_INTERFACE',
'start');
SELECT gl_interface_control_s.nextval
INTO G_group_id
FROM dual;
SELECT user_je_category_name
INTO G_category_name
FROM GL_JE_CATEGORIES
WHERE je_category_name = 'MRC Open Balances';
SELECT user_je_source_name
INTO G_source_name
FROM GL_JE_SOURCES
WHERE je_source_name = 'Assets';
-- LOOP thro all ccid arrays and insert rows into gl_interface
FOR i IN 1..G_adj_ccid_count LOOP
insert_rec(
p_rsob_id,
adjustments_ccids(i).entered_cr,
adjustments_ccids(i).entered_dr,
adjustments_ccids(i).accounted_cr,
adjustments_ccids(i).accounted_dr,
adjustments_ccids(i).ccid);
insert_rec(
p_rsob_id,
deprn_reserve_ccids(i).entered_cr,
deprn_reserve_ccids(i).entered_dr,
deprn_reserve_ccids(i).accounted_cr,
deprn_reserve_ccids(i).accounted_dr,
deprn_reserve_ccids(i).ccid);
insert_rec(
p_rsob_id,
deprn_expense_ccids(i).entered_cr,
deprn_expense_ccids(i).entered_dr,
deprn_expense_ccids(i).accounted_cr,
deprn_expense_ccids(i).accounted_dr,
deprn_expense_ccids(i).ccid);
insert_rec(
p_rsob_id,
reval_reserve_ccids(i).entered_cr,
reval_reserve_ccids(i).entered_dr,
reval_reserve_ccids(i).accounted_cr,
reval_reserve_ccids(i).accounted_dr,
reval_reserve_ccids(i).ccid);
insert_rec(
p_rsob_id,
reval_amort_ccids(i).entered_cr,
reval_amort_ccids(i).entered_dr,
reval_amort_ccids(i).accounted_cr,
reval_amort_ccids(i).accounted_dr,
reval_amort_ccids(i).ccid);
insert_rec(
p_rsob_id,
def_deprn_rsv_ccids(i).entered_cr,
def_deprn_rsv_ccids(i).entered_dr,
def_deprn_rsv_ccids(i).accounted_cr,
def_deprn_rsv_ccids(i).accounted_dr,
def_deprn_rsv_ccids(i).ccid);
insert_rec(
p_rsob_id,
def_deprn_exp_ccids(i).entered_cr,
def_deprn_exp_ccids(i).entered_dr,
def_deprn_exp_ccids(i).accounted_cr,
def_deprn_exp_ccids(i).accounted_dr,
def_deprn_exp_ccids(i).ccid);
'Inserting account balances in GL_INTERFACE',
'success');
calling_fn => 'fa_mc_upg3_pkg.insert_balances');
END insert_balances;