[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
insert into fa_xla_ext_headers_b_gt (
event_id ,
DEFAULT_CCID ,
BOOK_TYPE_CODE ,
PERIOD_NAME ,
PERIOD_CLOSE_DATE ,
PERIOD_COUNTER ,
ACCOUNTING_DATE ,
TRANSFER_TO_GL_FLAG )
select ctlgd.event_id,
bc.FLEXBUILDER_DEFAULTS_CCID ,
bc.book_type_code ,
dp.PERIOD_NAME ,
dp.CALENDAR_PERIOD_CLOSE_DATE ,
dp.PERIOD_COUNTER ,
ctlgd.event_date ,
decode(bc.GL_POSTING_ALLOWED_FLAG ,
'YES', 'Y','N')
FROM xla_events_gt ctlgd,
fa_deprn_periods dp,
fa_book_controls bc
WHERE ctlgd.entity_code = 'DEPRECIATION'
AND ctlgd.event_type_code = 'DEPRECIATION'
AND dp.book_type_code = ctlgd.source_id_char_1
AND dp.period_counter = ctlgd.source_id_int_2
AND bc.book_type_code = ctlgd.source_id_char_1;
'Rows inserted into headers: ' || to_char(SQL%ROWCOUNT));
INSERT INTO FA_XLA_EXT_HEADERS_B_GT (
event_id ,
period_close_date ,
reversal_flag ,
transfer_to_gl_flag ,
accounting_date )
SELECT ctlgd.event_id ,
dp.CALENDAR_PERIOD_CLOSE_DATE ,
'Y' ,
decode(bc.GL_POSTING_ALLOWED_FLAG ,
'YES', 'Y',
'N'),
dp.CALENDAR_PERIOD_CLOSE_DATE
FROM xla_events_gt ctlgd,
fa_book_controls bc,
fa_deprn_periods dp,
fa_deprn_events ds
WHERE ctlgd.entity_code = 'DEPRECIATION'
AND ctlgd.event_type_code = 'ROLLBACK_DEPRECIATION'
AND ds.asset_id = ctlgd.source_id_int_1
AND ds.book_type_code = ctlgd.source_id_char_1
AND ds.period_counter = ctlgd.source_id_int_2
AND ds.deprn_run_id = ctlgd.source_id_int_3
AND bc.book_type_code = ctlgd.source_id_char_1
-- AND ds.book_type_code = ctlgd.valuation_method
AND ds.reversal_event_id = ctlgd.event_id
AND dp.book_type_code = ds.book_type_code
AND dp.period_counter = ds.period_counter;
insert into fa_xla_ext_lines_b_gt (
EVENT_ID ,
LINE_NUMBER ,
DISTRIBUTION_ID ,
DISTRIBUTION_TYPE_CODE ,
LEDGER_ID ,
CURRENCY_CODE ,
ENTERED_AMOUNT ,
BONUS_ENTERED_AMOUNT ,
REVAL_ENTERED_AMOUNT ,
GENERATED_CCID ,
GENERATED_OFFSET_CCID ,
BONUS_GENERATED_CCID ,
BONUS_GENERATED_OFFSET_CCID ,
REVAL_GENERATED_CCID ,
REVAL_GENERATED_OFFSET_CCID ,
RESERVE_ACCOUNT_CCID ,
DEPRN_EXPENSE_ACCOUNT_CCID ,
BONUS_RESERVE_ACCT_CCID ,
BONUS_EXPENSE_ACCOUNT_CCID ,
REVAL_AMORT_ACCOUNT_CCID ,
REVAL_RESERVE_ACCOUNT_CCID ,
BOOK_TYPE_CODE ,
PERIOD_COUNTER ,
ASSET_ID,
BONUS_DEPRN_EXPENSE_ACCT,
BONUS_RESERVE_ACCT,
DEPRN_RESERVE_ACCT,
REVAL_AMORT_ACCT,
REVAL_RESERVE_ACCT,
DEPRN_RUN_ID,
EXPENSE_ACCOUNT_CCID )
select /*+ ordered use_hash(CB,BC,LE) swap_join_inputs(CB) swap_join_inputs(BC) swap_join_inputs(LE) */ ctlgd.EVENT_ID ,
dd.distribution_id as distribution_id,
dd.distribution_id as dist_id,
'DEPRN' ,
bc.set_of_books_id ,
le.currency_code ,
dd.deprn_amount
- dd.deprn_adjustment_amount , -- BUG# 5094085 removing bonus subtraction intentionally
dd.bonus_deprn_amount
- dd.bonus_deprn_adjustment_amount ,
dd.reval_amortization ,
dd.deprn_expense_ccid ,
dd.deprn_reserve_ccid ,
dd.bonus_deprn_expense_ccid ,
dd.bonus_deprn_reserve_ccid ,
dd.reval_amort_ccid ,
dd.reval_reserve_ccid ,
cb.RESERVE_ACCOUNT_CCID ,
cb.DEPRN_EXPENSE_ACCOUNT_CCID ,
cb.BONUS_RESERVE_ACCT_CCID ,
cb.BONUS_EXPENSE_ACCOUNT_CCID ,
cb.REVAL_AMORT_ACCOUNT_CCID ,
cb.REVAL_RESERVE_ACCOUNT_CCID ,
ctlgd.source_id_char_1 ,
dd.period_counter ,
ad.ASSET_ID,
cb.BONUS_DEPRN_EXPENSE_ACCT,
cb.BONUS_DEPRN_RESERVE_ACCT,
cb.DEPRN_RESERVE_ACCT,
cb.REVAL_AMORTIZATION_ACCT,
cb.REVAL_RESERVE_ACCT,
dd.DEPRN_RUN_ID,
dh.CODE_COMBINATION_ID
from xla_events_gt ctlgd,
fa_deprn_detail dd,
fa_distribution_history dh,
fa_additions_b ad,
fa_asset_history ah,
fa_category_books cb,
fa_book_controls bc,
gl_ledgers le
where ctlgd.entity_code = 'DEPRECIATION'
AND ctlgd.event_type_code = 'DEPRECIATION'
AND dd.asset_id = ctlgd.source_id_int_1
AND dd.book_type_code = ctlgd.source_id_char_1
AND dd.period_counter = ctlgd.source_id_int_2
AND dd.deprn_run_id = ctlgd.source_id_int_3
AND ad.asset_id = ctlgd.source_id_int_1
AND dd.distribution_id = dh.distribution_id
AND ah.asset_id = ctlgd.source_id_int_1
AND AH.Date_Effective < nvl(DH.Date_ineffective, SYSDATE)
AND nvl(DH.Date_ineffective, SYSDATE) <=
nvl(AH.Date_ineffective, SYSDATE)
AND cb.category_id = ah.category_id
AND cb.book_type_code = ctlgd.source_id_char_1
AND ah.asset_type in ('CAPITALIZED', 'GROUP')
AND ad.asset_type in ('CAPITALIZED', 'GROUP')
AND bc.book_type_code = ctlgd.source_id_char_1
AND le.ledger_id = bc.set_of_books_id ;
'Rows inserted into lines: ' || to_char(SQL%ROWCOUNT));
insert into fa_xla_ext_lines_b_gt (
EVENT_ID ,
LINE_NUMBER ,
DISTRIBUTION_ID ,
DISTRIBUTION_TYPE_CODE ,
LEDGER_ID ,
CURRENCY_CODE ,
ENTERED_AMOUNT ,
BONUS_ENTERED_AMOUNT ,
REVAL_ENTERED_AMOUNT ,
GENERATED_CCID ,
GENERATED_OFFSET_CCID ,
BONUS_GENERATED_CCID ,
BONUS_GENERATED_OFFSET_CCID ,
REVAL_GENERATED_CCID ,
REVAL_GENERATED_OFFSET_CCID ,
RESERVE_ACCOUNT_CCID ,
DEPRN_EXPENSE_ACCOUNT_CCID ,
BONUS_RESERVE_ACCT_CCID ,
BONUS_EXPENSE_ACCOUNT_CCID ,
REVAL_AMORT_ACCOUNT_CCID ,
REVAL_RESERVE_ACCOUNT_CCID ,
BOOK_TYPE_CODE ,
PERIOD_COUNTER ,
ASSET_ID,
BONUS_DEPRN_EXPENSE_ACCT,
BONUS_RESERVE_ACCT,
DEPRN_RESERVE_ACCT,
REVAL_AMORT_ACCT,
REVAL_RESERVE_ACCT,
DEPRN_RUN_ID,
EXPENSE_ACCOUNT_CCID )
select /*+ ordered use_hash(CB,BC,LE) swap_join_inputs(CB) swap_join_inputs(BC) swap_join_inputs(LE) */ ctlgd.EVENT_ID ,
dd.distribution_id as distribution_id,
dd.distribution_id as dist_id,
'DEPRN' ,
bc.set_of_books_id ,
le.currency_code ,
dd.deprn_amount
- dd.deprn_adjustment_amount , -- BUG# 5094085 removing bonus subtraction intentionally
dd.bonus_deprn_amount
- dd.bonus_deprn_adjustment_amount ,
dd.reval_amortization ,
dd.deprn_expense_ccid ,
dd.deprn_reserve_ccid ,
dd.bonus_deprn_expense_ccid ,
dd.bonus_deprn_reserve_ccid ,
dd.reval_amort_ccid ,
dd.reval_reserve_ccid ,
cb.RESERVE_ACCOUNT_CCID ,
cb.DEPRN_EXPENSE_ACCOUNT_CCID ,
cb.BONUS_RESERVE_ACCT_CCID ,
cb.BONUS_EXPENSE_ACCOUNT_CCID ,
cb.REVAL_AMORT_ACCOUNT_CCID ,
cb.REVAL_RESERVE_ACCOUNT_CCID ,
ctlgd.source_id_char_1 ,
dd.period_counter ,
ad.ASSET_ID,
cb.BONUS_DEPRN_EXPENSE_ACCT,
cb.BONUS_DEPRN_RESERVE_ACCT,
cb.DEPRN_RESERVE_ACCT,
cb.REVAL_AMORTIZATION_ACCT,
cb.REVAL_RESERVE_ACCT,
dd.DEPRN_RUN_ID,
dh.CODE_COMBINATION_ID
from xla_events_gt ctlgd,
fa_mc_deprn_detail dd,
fa_distribution_history dh,
fa_additions_b ad,
fa_asset_history ah,
fa_category_books cb,
fa_mc_book_controls bc,
gl_ledgers le
where ctlgd.entity_code = 'DEPRECIATION'
AND ctlgd.event_type_code = 'DEPRECIATION'
AND dd.asset_id = ctlgd.source_id_int_1
AND dd.book_type_code = ctlgd.source_id_char_1
AND dd.period_counter = ctlgd.source_id_int_2
AND dd.deprn_run_id = ctlgd.source_id_int_3
AND ad.asset_id = ctlgd.source_id_int_1
AND dd.distribution_id = dh.distribution_id
AND ah.asset_id = ctlgd.source_id_int_1
AND AH.Date_Effective < nvl(DH.Date_ineffective, SYSDATE)
AND nvl(DH.Date_ineffective, SYSDATE) <=
nvl(AH.Date_ineffective, SYSDATE)
AND cb.category_id = ah.category_id
AND cb.book_type_code = ctlgd.source_id_char_1
AND ah.asset_type in ('CAPITALIZED', 'GROUP')
AND ad.asset_type in ('CAPITALIZED', 'GROUP')
AND bc.book_type_code = ctlgd.source_id_char_1
AND le.ledger_id = bc.set_of_books_id
AND dd.set_of_books_id = bc.set_of_books_id;
'Rows inserted into lines: ' || to_char(SQL%ROWCOUNT));
'Rows inserted into mls: ' || to_char(SQL%ROWCOUNT));
select /*+ leading(xg) index(xb, FA_XLA_EXT_HEADERS_B_GT_U1) index(xl, FA_XLA_EXT_LINES_B_GT_U1) */
xl.rowid,
xb.book_type_code,
xl.distribution_id,
xl.EXPENSE_ACCOUNT_CCID,
xl.entered_amount,
xl.bonus_entered_amount,
xl.reval_entered_amount,
nvl(xl.GENERATED_CCID, da.DEPRN_EXPENSE_ACCOUNT_CCID),
nvl(xl.GENERATED_OFFSET_CCID, da.DEPRN_RESERVE_ACCOUNT_CCID),
nvl(xl.BONUS_GENERATED_CCID, da.BONUS_EXP_ACCOUNT_CCID),
nvl(xl.BONUS_GENERATED_OFFSET_CCID, da.BONUS_RSV_ACCOUNT_CCID),
nvl(xl.REVAL_GENERATED_CCID, da.REVAL_AMORT_ACCOUNT_CCID),
nvl(xl.REVAL_GENERATED_OFFSET_CCID, da.REVAL_RSV_ACCOUNT_CCID),
-- xl.DEPRN_EXPENSE_ACCOUNT_CCID,
xl.RESERVE_ACCOUNT_CCID,
-- xl.BONUS_EXP_ACCOUNT_CCID,
xl.BONUS_RESERVE_ACCT_CCID,
xl.REVAL_AMORT_ACCOUNT_CCID,
xl.REVAL_RESERVE_ACCOUNT_CCID,
xl.deprn_expense_acct,
xl.DEPRN_RESERVE_ACCT,
xl.bonus_deprn_expense_acct,
xl.BONUS_RESERVE_ACCT,
xl.REVAL_AMORT_ACCT,
xl.REVAL_RESERVE_ACCT
from xla_events_gt xg,
fa_xla_ext_headers_b_gt xb,
fa_xla_ext_lines_b_gt xl,
fa_distribution_accounts da
where xg.event_class_code = 'DEPRECIATION'
and xb.event_id = xg.event_id
and xl.event_id = xg.event_id
and xl.distribution_id = da.distribution_id(+)
and xl.book_type_code = da.book_type_code(+);
update fa_xla_ext_lines_b_gt
set generated_ccid = l_generated_ccid(i),
generated_offset_ccid = l_generated_offset_ccid(i),
bonus_generated_ccid = l_bonus_generated_ccid(i),
bonus_generated_offset_ccid = l_bonus_generated_offset_ccid(i),
reval_generated_ccid = l_reval_generated_ccid(i),
reval_generated_offset_ccid = l_reval_generated_offset_ccid(i)
where rowid = l_rowid(i);