[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 ,
DEFERRED_DEPRN_EXPENSE_ACCT,
DEFERRED_DEPRN_RESERVE_ACCT )
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 ,
'Y' ,
bc.DEFERRED_DEPRN_EXPENSE_ACCT,
bc.DEFERRED_DEPRN_RESERVE_ACCT
FROM xla_events_gt ctlgd,
fa_deprn_periods dp,
fa_book_controls bc
WHERE ctlgd.entity_code = 'DEFERRED_DEPRECIATION'
AND ctlgd.event_type_code = 'DEFERRED_DEPRECIATION'
AND bc.book_type_code = ctlgd.source_id_char_1
AND dp.book_type_code = ctlgd.source_id_char_1
AND dp.period_counter = ctlgd.source_id_int_2
AND bc.set_of_books_id = ctlgd.ledger_id ;
'Rows inserted into headers: ' || to_char(SQL%ROWCOUNT));
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 ,
DEFERRED_DEPRN_EXPENSE_ACCT,
DEFERRED_DEPRN_RESERVE_ACCT )
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 ,
'Y' ,
bc.DEFERRED_DEPRN_EXPENSE_ACCT,
bc.DEFERRED_DEPRN_RESERVE_ACCT
FROM xla_events_gt ctlgd,
fa_deprn_periods dp,
fa_book_controls bc ,
fa_mc_book_controls mcbc ,
gl_ledgers le
WHERE ctlgd.entity_code = 'DEFERRED_DEPRECIATION'
AND ctlgd.event_type_code = 'DEFERRED_DEPRECIATION'
AND bc.book_type_code = ctlgd.source_id_char_1
AND dp.book_type_code = ctlgd.source_id_char_1
AND dp.period_counter = ctlgd.source_id_int_2
AND mcbc.book_type_code = bc.book_type_code
AND mcbc.set_of_books_id = ctlgd.ledger_id
AND le.ledger_id = mcbc.set_of_books_id ;
'Rows inserted into headers: ' || 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 ,
CAT_ID ,
ENTERED_AMOUNT ,
BOOK_TYPE_CODE ,
TAX_BOOK_TYPE_CODE ,
GENERATED_CCID ,
GENERATED_OFFSET_CCID ,
ASSET_ID,
EXPENSE_ACCOUNT_CCID )
select ctlgd.EVENT_ID ,
df.distribution_id as distribution_id,
df.distribution_id as dist_id,
'DEFERRED' ,
bc.set_of_books_id ,
le.currency_code ,
ah.category_id ,
df.deferred_deprn_expense_amount ,
df.corp_book_type_code ,
df.tax_book_type_code ,
df.deferred_deprn_expense_ccid ,
df.deferred_deprn_reserve_ccid ,
ad.ASSET_ID,
dh.CODE_COMBINATION_ID
from fa_additions_b ad,
fa_asset_history ah,
fa_book_controls bc,
fa_category_books cb,
fa_distribution_history dh,
fa_deferred_deprn df,
gl_ledgers le,
xla_events_gt ctlgd
where ctlgd.entity_code = 'DEFERRED_DEPRECIATION'
AND ctlgd.event_type_code = 'DEFERRED_DEPRECIATION'
AND df.asset_id = ctlgd.source_id_int_1
AND df.corp_book_type_code = ctlgd.source_id_char_1
AND df.corp_period_counter = ctlgd.source_id_int_2
AND df.tax_book_type_code = ctlgd.source_id_char_2
AND df.event_id = ctlgd.event_id
AND ad.asset_id = ctlgd.source_id_int_1
AND dh.distribution_id = df.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 ,
CAT_ID ,
ENTERED_AMOUNT ,
BOOK_TYPE_CODE ,
TAX_BOOK_TYPE_CODE ,
GENERATED_CCID ,
GENERATED_OFFSET_CCID ,
ASSET_ID,
EXPENSE_ACCOUNT_CCID )
select ctlgd.EVENT_ID ,
df.distribution_id as distribution_id,
df.distribution_id as dist_id,
'DEFERRED' ,
bc.set_of_books_id ,
le.currency_code ,
ah.category_id ,
df.deferred_deprn_expense_amount ,
df.corp_book_type_code ,
df.tax_book_type_code ,
df.deferred_deprn_expense_ccid ,
df.deferred_deprn_reserve_ccid ,
ad.ASSET_ID,
dh.CODE_COMBINATION_ID
from fa_additions_b ad,
fa_asset_history ah,
fa_mc_book_controls bc,
fa_category_books cb,
fa_distribution_history dh,
fa_mc_deferred_deprn df,
gl_ledgers le,
xla_events_gt ctlgd
where ctlgd.entity_code = 'DEFERRED_DEPRECIATION'
AND ctlgd.event_type_code = 'DEFERRED_DEPRECIATION'
AND df.asset_id = ctlgd.source_id_int_1
AND df.corp_book_type_code = ctlgd.source_id_char_1
AND df.corp_period_counter = ctlgd.source_id_int_2
AND df.tax_book_type_code = ctlgd.source_id_char_2
AND df.event_id = ctlgd.event_id
AND ad.asset_id = ctlgd.source_id_int_1
AND dh.distribution_id = df.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 df.set_of_books_id = bc.set_of_books_id;
'Rows inserted into alc 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,
nvl(xl.generated_ccid, da.DEFERRED_EXP_ACCOUNT_CCID),
nvl(xl.generated_offset_ccid, da.DEFERRED_RSV_ACCOUNT_CCID),
xb.DEFERRED_DEPRN_EXPENSE_ACCT,
xb.DEFERRED_DEPRN_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 = 'DEFERRED DEPRECIATION'
and xb.event_id = xg.event_id
and xl.event_id = xg.event_id
and xl.distribution_id = da.distribution_id(+)
and xl.tax_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)
where rowid = l_rowid(i);