[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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),
da.CAPITAL_ADJ_ACCOUNT_CCID,
da.GENERAL_FUND_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.CAPITAL_ADJ_ACCOUNT_CCID,
xl.GENERAL_FUND_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,
xl.CAPITAL_ADJ_ACCT,
xl.GENERAL_FUND_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),
capital_adj_generated_ccid = l_capital_adj_generated_ccid(i),
general_fund_generated_ccid = l_general_fund_generated_ccid(i)
where rowid = l_rowid(i);
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,
decode
(adjustment_type,
''COST'', ''ASSET_COST_ACCT'',
''CIP COST'', ''CIP_COST_ACCT'',
''COST CLEARING'', decode(xl.asset_type,
''CIP'', ''CIP_CLEARING_ACCT'',
''ASSET_CLEARING_ACCT''),
''EXPENSE'', ''DEPRN_EXPENSE_ACCT'',
''RESERVE'', ''DEPRN_RESERVE_ACCT'',
''BONUS EXPENSE'', ''BONUS_DEPRN_EXPENSE_ACCT'',
''BONUS RESERVE'', ''BONUS_DEPRN_RESERVE_ACCT'',
''REVAL RESERVE'', ''REVAL_RESERVE_ACCT'',
''CAPITAL ADJ'', ''CAPITAL_ADJ_ACCT'',
''GENERAL FUND'', ''GENERAL_FUND_ACCT'',
''IMPAIR EXPENSE'', ''IMPAIR_EXPENSE_ACCT'',
''IMPAIR RESERVE'', ''IMPAIR_RESERVE_ACCT'',
''LINK IMPAIR EXP'', ''IMPAIR_EXPENSE_ACCT'',
''IMPAIR OFF EXP'', ''DEPRN_EXPENSE_ACCT'',
''DEPRN ADJUST'', ''DEPRN_ADJUSTMENT_ACCT'',
''REVAL LOSS'', ''REVAL_LOSS_ACCT'',
''REVAL OFF EXP'', ''DEPRN_EXPENSE_ACCT'',
''PROCEEDS CLR'', ''PROCEEDS_OF_SALE_CLEARING_ACCT'',
''REMOVALCOST CLR'', ''COST_OF_REMOVAL_CLEARING_ACCT'',
''REMOVALCOST'', decode(sign(gain_loss_amount),
-1, ''COST_OF_REMOVAL_LOSS_ACCT'',
''COST_OF_REMOVAL_GAIN_ACCT''),
''PROCEEDS'', decode(sign(gain_loss_amount),
-1, ''PROCEEDS_OF_SALE_LOSS_ACCT'',
''PROCEEDS_OF_SALE_GAIN_ACCT''),
''REVAL RSV RET'', decode(sign(gain_loss_amount),
-1, ''REVAL_RSV_RETIRED_LOSS_ACCT'',
''REVAL_RSV_RETIRED_GAIN_ACCT''),
''NBV RETIRED'', decode(asset_type,
''GROUP'', decode(sign(gain_loss_amount),
-1, ''NBV_RETIRED_LOSS_ACCT'',
''NBV_RETIRED_GAIN_ACCT''),
decode(sign(gain_loss_amount),
-1, ''NBV_RETIRED_LOSS_ACCT'',
''NBV_RETIRED_GAIN_ACCT'')),
NULL),
decode(xl.adjustment_type,
''COST'', nvl(xl.generated_ccid, da.ASSET_COST_ACCOUNT_CCID),
''CIP COST'', nvl(xl.generated_ccid, da.CIP_COST_ACCOUNT_CCID),
''COST CLEARING'', decode(xl.asset_type,
''CIP'', nvl(xl.generated_ccid, da.CIP_CLEARING_ACCOUNT_CCID),
nvl(xl.generated_ccid, da.ASSET_CLEARING_ACCOUNT_CCID)),
''EXPENSE'', nvl(xl.generated_ccid, da.DEPRN_EXPENSE_ACCOUNT_CCID),
''RESERVE'', nvl(xl.generated_ccid, da.DEPRN_RESERVE_ACCOUNT_CCID),
''BONUS EXPENSE'', nvl(xl.generated_ccid, da.BONUS_EXP_ACCOUNT_CCID),
''BONUS RESERVE'', nvl(xl.generated_ccid, da.BONUS_RSV_ACCOUNT_CCID),
''REVAL RESERVE'', nvl(xl.generated_ccid, da.REVAL_RSV_ACCOUNT_CCID),
''CAPITAL ADJ'', nvl(xl.generated_ccid, da.CAPITAL_ADJ_ACCOUNT_CCID),
''GENERAL FUND'', nvl(xl.generated_ccid, da.GENERAL_FUND_ACCOUNT_CCID),
''IMPAIR EXPENSE'', nvl(xl.generated_ccid, da.IMPAIR_EXPENSE_ACCOUNT_CCID),
''IMPAIR RESERVE'', nvl(xl.generated_ccid, da.IMPAIR_RESERVE_ACCOUNT_CCID),
''LINK IMPAIR EXP'', nvl(xl.generated_ccid, da.IMPAIR_EXPENSE_ACCOUNT_CCID),
''IMPAIR OFF EXP'', nvl(xl.generated_ccid, da.DEPRN_EXPENSE_ACCOUNT_CCID),
''REVAL LOSS'', nvl(xl.generated_ccid, da.REVAL_LOSS_ACCOUNT_CCID),
''REVAL OFF EXP'', nvl(xl.generated_ccid, da.DEPRN_EXPENSE_ACCOUNT_CCID),
''DEPRN ADJUST'', nvl(xl.generated_ccid, da.DEPRN_ADJ_ACCOUNT_CCID),
''PROCEEDS CLR'', nvl(xl.generated_ccid, da.PROCEEDS_SALE_CLEARING_CCID),
''REMOVALCOST CLR'', nvl(xl.generated_ccid, da.COST_REMOVAL_CLEARING_CCID),
''PROCEEDS'', decode(sign(xl.gain_loss_amount),
-1, nvl(xl.generated_ccid, da.PROCEEDS_SALE_LOSS_CCID),
nvl(xl.generated_ccid, da.PROCEEDS_SALE_GAIN_CCID)),
''REMOVALCOST'', decode(sign(xl.gain_loss_amount),
-1, nvl(xl.generated_ccid, da.COST_REMOVAL_LOSS_CCID),
nvl(xl.generated_ccid, da.COST_REMOVAL_GAIN_CCID)),
''REVAL RSV RET'', decode(sign(xl.gain_loss_amount),
-1, nvl(xl.generated_ccid, da.REVAL_RSV_LOSS_ACCOUNT_CCID),
nvl(xl.generated_ccid, da.REVAL_RSV_GAIN_ACCOUNT_CCID)),
''NBV RETIRED'', decode(sign(xl.gain_loss_amount),
-1, nvl(xl.generated_ccid, da.NBV_RETIRED_LOSS_CCID),
nvl(xl.generated_ccid, da.NBV_RETIRED_GAIN_CCID)),
NULL),
decode(xl.adjustment_type,
''COST'', xl.ASSET_COST_ACCOUNT_CCID,
''CIP COST'', xl.CIP_COST_ACCOUNT_CCID,
''COST CLEARING'', decode(xl.asset_type,
''CIP'', xl.CIP_CLEARING_ACCOUNT_CCID,
xl.ASSET_CLEARING_ACCOUNT_CCID),
''RESERVE'', xl.RESERVE_ACCOUNT_CCID,
''BONUS RESERVE'', xl.BONUS_RESERVE_ACCT_CCID,
''REVAL RESERVE'', xl.REVAL_RESERVE_ACCOUNT_CCID,
''CAPITAL ADJ'', xl.CAPITAL_ADJ_ACCOUNT_CCID,
''GENERAL FUND'', xl.GENERAL_FUND_ACCOUNT_CCID,
''IMPAIR EXPENSE'', xl.IMPAIR_EXPENSE_ACCOUNT_CCID,
''IMPAIR RESERVE'', xl.IMPAIR_RESERVE_ACCOUNT_CCID,
''LINK IMPAIR EXP'', xl.IMPAIR_EXPENSE_ACCOUNT_CCID,
''IMPAIR OFF EXP'', xl.DEPRN_EXPENSE_ACCOUNT_CCID,
''REVAL LOSS'', xl.REVAL_LOSS_ACCOUNT_CCID,
''REVAL OFF EXP'', xl.DEPRN_EXPENSE_ACCOUNT_CCID,
0),
decode(xl.adjustment_type,
''COST'', xl.ASSET_COST_ACCT,
''CIP COST'', xl.CIP_COST_ACCT,
''COST CLEARING'', decode(xl.asset_type,
''CIP'', xl.CIP_CLEARING_ACCT,
xl.ASSET_CLEARING_ACCT),
''EXPENSE'', xl.DEPRN_EXPENSE_ACCT,
''RESERVE'', xl.DEPRN_RESERVE_ACCT,
''BONUS EXPENSE'', xl.BONUS_DEPRN_EXPENSE_ACCT,
''BONUS RESERVE'', xl.BONUS_RESERVE_ACCT,
''REVAL RESERVE'', xl.REVAL_RESERVE_ACCT,
''CAPITAL ADJ'', xl.CAPITAL_ADJ_ACCT,
''GENERAL FUND'', xl.GENERAL_FUND_ACCT,
''IMPAIR EXPENSE'', xl.IMPAIR_EXPENSE_ACCT,
''IMPAIR RESERVE'', xl.IMPAIR_RESERVE_ACCT,
''LINK IMPAIR EXP'', xl.IMPAIR_EXPENSE_ACCT,
''IMPAIR OFF EXP'', xl.DEPRN_EXPENSE_ACCT,
''REVAL LOSS'', xl.REVAL_LOSS_ACCT,
''REVAL OFF EXP'', xl.DEPRN_EXPENSE_ACCT,
''PROCEEDS CLR'', xb.PROCEEDS_OF_SALE_CLEARING_ACCT,
''REMOVALCOST CLR'', xb.COST_OF_REMOVAL_CLEARING_ACCT,
''NBV RETIRED'', decode(sign(xl.gain_loss_amount),
-1, xb.NBV_RETIRED_LOSS_ACCT,
xb.NBV_RETIRED_GAIN_ACCT),
''PROCEEDS'', decode(sign(xl.gain_loss_amount),
-1, xb.PROCEEDS_OF_SALE_LOSS_ACCT,
xb.PROCEEDS_OF_SALE_GAIN_ACCT),
''REMOVALCOST'', decode(sign(xl.gain_loss_amount),
-1, xb.COST_OF_REMOVAL_LOSS_ACCT,
xb.COST_OF_REMOVAL_GAIN_ACCT),
''REVAL RSV RET'', decode(sign(xl.gain_loss_amount),
-1, xb.REVAL_RSV_RETIRED_LOSS_ACCT,
xb.REVAL_RSV_RETIRED_GAIN_ACCT),
NULL),
decode(xl.adjustment_type,
''EXPENSE'', ''DEPRN_RESERVE_ACCT'',
''BONUS EXPENSE'', ''BONUS_DEPRN_RESERVE_ACCT'',
NULL),
decode(xl.adjustment_type,
''EXPENSE'', da.DEPRN_RESERVE_ACCOUNT_CCID,
''BONUS EXPENSE'', da.BONUS_RSV_ACCOUNT_CCID,
NULL),
decode(xl.adjustment_type,
''EXPENSE'', xl.RESERVE_ACCOUNT_CCID,
''BONUS EXPENSE'', xl.BONUS_RESERVE_ACCT_CCID,
NULL),
decode(xl.adjustment_type,
''EXPENSE'', xl.DEPRN_RESERVE_ACCT,
''BONUS EXPENSE'', xl.BONUS_RESERVE_ACCT,
NULL),
-- Bug 6962827: counter_account_type
decode(adjustment_type,
''BONUS EXPENSE'', ''DEPRN_EXPENSE_ACCT'',
''BONUS RESERVE'', ''DEPRN_RESERVE_ACCT'',
NULL),
-- Bug 6962827: counter_generated_ccid
decode(xl.adjustment_type,
''BONUS EXPENSE'', da.DEPRN_EXPENSE_ACCOUNT_CCID,
''BONUS RESERVE'', da.DEPRN_RESERVE_ACCOUNT_CCID,
NULL),
-- Bug 6962827 : counter_account_ccid
decode(xl.adjustment_type,
''BONUS RESERVE'', xl.RESERVE_ACCOUNT_CCID,
0),
-- Bug 6962827 : counter_account_segment
decode(xl.adjustment_type,
''BONUS EXPENSE'', xl.DEPRN_EXPENSE_ACCT,
''BONUS RESERVE'', xl.DEPRN_RESERVE_ACCT,
NULL),
-- Bug 6962827 : counter_generated_offset_ccid
decode(xl.adjustment_type,
''BONUS EXPENSE'', da.DEPRN_RESERVE_ACCOUNT_CCID,
NULL),
-- Bug 6962827 : counter_offset_account_ccid
decode(xl.adjustment_type,
''BONUS EXPENSE'', xl.RESERVE_ACCOUNT_CCID,
NULL),
-- Bug 6962827 : counter_offset_account_segment
decode(xl.adjustment_type,
''BONUS EXPENSE'', xl.DEPRN_RESERVE_ACCT,
NULL)
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 not in (''DEPRECIATION'', ''DEFERRED'')
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),
counter_generated_ccid = l_counter_generated_ccid(i), -- Bug 6962827
counter_generated_offset_ccid = l_ctr_generated_off_ccid(i) -- Bug 6962827
where rowid = l_rowid(i);
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);