[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT corp_bc.set_of_books_id,
tax_bc.set_of_books_id,
DH.Asset_ID,
DH.Distribution_ID,
DH.Code_Combination_ID,
nvl(CORP_DD.Deprn_Amount, 0),
nvl(TAX_DD.Deprn_Amount, 0),
nvl(CORP_DD.Period_Counter, -1),
nvl(TAX_DD.Period_Counter, -1)
FROM FA_DISTRIBUTION_HISTORY DH,
FA_ASSET_HISTORY AH,
FA_MC_DEPRN_PERIODS DP,
FA_MC_DEPRN_DETAIL CORP_DD,
FA_MC_DEPRN_DETAIL TAX_DD,
fa_mc_book_controls corp_bc,
fa_mc_book_controls tax_bc
WHERE DH.Book_Type_Code = p_corp_book
AND DP.Period_Counter = p_tax_period_ctr
AND DP.Book_Type_Code = p_tax_book
AND DP.set_of_books_id = p_set_of_books_id
AND AH.Asset_ID = DH.Asset_ID
AND AH.Date_Effective < DP.Period_Close_Date
AND DP.Period_Close_Date <= NVL(AH.Date_Ineffective,
DP.Period_Close_Date)
AND AH.Asset_Type in ('CAPITALIZED', 'GROUP')
AND CORP_BC.book_type_code = p_corp_book
AND CORP_BC.set_of_books_id = p_set_of_books_id
AND TAX_BC.book_type_code = p_tax_book
AND TAX_BC.set_of_books_id = p_set_of_books_id
AND CORP_DD.Book_Type_Code (+) = p_corp_book
AND CORP_DD.set_of_books_id(+) = p_set_of_books_id
AND CORP_DD.Period_Counter (+) = p_tax_period_ctr
AND CORP_DD.Distribution_ID (+) = DH.Distribution_ID
AND CORP_DD.Deprn_Source_Code (+) <> 'T'
AND TAX_DD.Book_Type_Code (+) = p_tax_book
AND TAX_DD.set_of_books_id(+) = p_set_of_books_id
AND TAX_DD.Period_Counter (+) = p_tax_period_ctr
AND TAX_DD.Distribution_ID (+) = DH.Distribution_ID
AND TAX_DD.Deprn_Source_Code (+) <> 'T'
AND TAX_DD.asset_id(+) = DH.asset_id
AND (TAX_DD.asset_id is not null OR CORP_DD.asset_id is not null)
AND exists
(select 1
from fa_books bk
where bk.asset_id = DH.asset_id
and bk.book_type_code = p_tax_book
and bk.transaction_header_id_out is null)
ORDER BY DH.Asset_ID,
DH.Distribution_ID,
DH.Code_Combination_ID;
SELECT DH.Asset_ID,
DH.Distribution_ID,
DH.Code_Combination_ID,
nvl(CORP_DD.Deprn_Amount, 0),
nvl(TAX_DD.Deprn_Amount, 0),
nvl(CORP_DD.Period_Counter, -1),
nvl(TAX_DD.Period_Counter, -1)
FROM FA_DISTRIBUTION_HISTORY DH,
FA_ASSET_HISTORY AH,
FA_DEPRN_PERIODS DP,
FA_DEPRN_DETAIL CORP_DD,
FA_DEPRN_DETAIL TAX_DD
WHERE DH.Book_Type_Code = p_corp_book
AND DP.Period_Counter = p_tax_period_ctr
AND DP.Book_Type_Code = p_tax_book
AND AH.Asset_ID = DH.Asset_ID
AND AH.Date_Effective < DP.Period_Close_Date
AND DP.Period_Close_Date <= NVL(AH.Date_Ineffective,
DP.Period_Close_Date)
AND AH.Asset_Type in ('CAPITALIZED', 'GROUP')
AND CORP_DD.Book_Type_Code (+) = p_corp_book
AND CORP_DD.Period_Counter (+) = p_tax_period_ctr
AND CORP_DD.Distribution_ID (+) = DH.Distribution_ID
AND CORP_DD.Deprn_Source_Code (+) <> 'T'
AND TAX_DD.Book_Type_Code (+) = p_tax_book
AND TAX_DD.Period_Counter (+) = p_tax_period_ctr
AND TAX_DD.Distribution_ID (+) = DH.Distribution_ID
AND TAX_DD.Deprn_Source_Code (+) <> 'T'
AND TAX_DD.asset_id(+) = DH.asset_id
AND (TAX_DD.asset_id is not null OR CORP_DD.asset_id is not null)
AND exists
(select 1
from fa_books bk
where bk.asset_id = DH.asset_id
and bk.book_type_code = p_tax_book
and bk.transaction_header_id_out is null)
ORDER BY DH.Asset_ID,
DH.Distribution_ID,
DH.Code_Combination_ID;
SELECT asset_id,
distribution_id,
nvl(sum(nvl(decode(p_book_class,
'TAX', decode(AJ.debit_credit_flag,
'CR', 1, -1),
decode(AJ.debit_credit_flag,
'DR', 1, -1)) *
AJ.Adjustment_Amount, 0)), 0)
from fa_adjustments AJ
WHERE AJ.Book_Type_Code (+) = p_book
AND AJ.Period_Counter_Created (+) = p_period_ctr
AND nvl(AJ.Adjustment_Amount,-9999) <> 0
AND AJ.Adjustment_Type (+) = 'RESERVE'
AND nvl(AJ.Track_Member_Flag,'N') = 'N'
AND exists
(select 1
from fa_deferred_deprn def
where def.asset_id = AJ.asset_id
and def.corp_book_type_code = p_corp_book
and def.tax_book_type_code = p_tax_book
and def.corp_period_counter = p_corp_period_ctr
and def.tax_period_counter = p_tax_period_ctr)
GROUP BY asset_id,
distribution_id;
SELECT set_of_books_id,
asset_id,
distribution_id,
nvl(sum(nvl(decode(p_book_class,
'TAX', decode(AJ.debit_credit_flag,
'CR', 1, -1),
decode(AJ.debit_credit_flag,
'DR', 1, -1)) *
AJ.Adjustment_Amount, 0)), 0)
from fa_mc_adjustments AJ
WHERE AJ.Book_Type_Code (+) = p_book
AND AJ.Period_Counter_Created (+) = p_period_ctr
AND AJ.set_of_books_id = p_set_of_books_id
AND nvl(AJ.Adjustment_Amount,-9999) <> 0
AND AJ.Adjustment_Type (+) = 'RESERVE'
AND nvl(AJ.Track_Member_Flag,'N') = 'N'
AND exists
(select 1
from fa_deferred_deprn def
where def.asset_id = AJ.asset_id
and def.corp_book_type_code = p_corp_book
and def.tax_book_type_code = p_tax_book
and def.corp_period_counter = p_corp_period_ctr
and def.tax_period_counter = p_tax_period_ctr)
GROUP BY set_of_books_id,
asset_id,
distribution_id;
'bulk inserting into fa_deferred_deprn*' || p_mrc_sob_type_code);
INSERT INTO FA_MC_DEFERRED_DEPRN
(set_of_books_id,
corp_book_type_code,
tax_book_type_code,
asset_id,
distribution_id,
deferred_deprn_expense_ccid,
deferred_deprn_reserve_ccid,
deferred_deprn_expense_amount,
deferred_deprn_reserve_amount,
corp_period_counter,
tax_period_counter,
expense_je_line_num,
reserve_je_line_num)
values
(l_tax_sob_id(i),
p_corp_book,
p_tax_book,
l_asset_id(i),
l_dist_id(i),
null, -- l_deferred_deprn_expense_ccid,
null, -- l_deferred_deprn_reserve_ccid,
(l_tax_deprn(i) - l_corp_deprn(i)),
(l_tax_deprn(i) - l_corp_deprn(i)),
p_corp_period_ctr,
p_tax_period_ctr,
0,
0);
INSERT INTO FA_DEFERRED_DEPRN
(corp_book_type_code,
tax_book_type_code,
asset_id,
distribution_id,
deferred_deprn_expense_ccid,
deferred_deprn_reserve_ccid,
deferred_deprn_expense_amount,
deferred_deprn_reserve_amount,
corp_period_counter,
tax_period_counter,
expense_je_line_num,
reserve_je_line_num)
values
(p_corp_book,
p_tax_book,
l_asset_id(i),
l_dist_id(i),
null, -- l_deferred_deprn_expense_ccid,
null, -- l_deferred_deprn_reserve_ccid,
(l_tax_deprn(i) - l_corp_deprn(i)),
(l_tax_deprn(i) - l_corp_deprn(i)),
p_corp_period_ctr,
p_tax_period_ctr,
0,
0);
l_corp_sob_id.delete;
l_tax_sob_id.delete;
l_asset_id.delete;
l_dist_id.delete;
l_dh_ccid.delete;
l_corp_deprn.delete;
l_tax_deprn.delete;
l_corp_dd_period_counter.delete;
l_tax_dd_period_counter.delete;
UPDATE FA_MC_DEFERRED_DEPRN
SET deferred_deprn_expense_amount = deferred_deprn_expense_amount + l_rsv_adj(i),
deferred_deprn_reserve_amount = deferred_deprn_reserve_amount + l_rsv_adj(i)
WHERE set_of_books_id = l_tax_sob_id(i)
AND corp_book_type_code = p_corp_book
AND tax_book_type_code = p_tax_book
AND corp_period_counter = p_corp_period_ctr
AND tax_period_counter = p_tax_period_ctr
AND asset_id = l_asset_id(i)
AND distribution_id = l_dist_id(i);
UPDATE FA_DEFERRED_DEPRN
SET deferred_deprn_expense_amount = deferred_deprn_expense_amount + l_rsv_adj(i),
deferred_deprn_reserve_amount = deferred_deprn_reserve_amount + l_rsv_adj(i)
WHERE corp_book_type_code = p_corp_book
AND tax_book_type_code = p_tax_book
AND corp_period_counter = p_corp_period_ctr
AND tax_period_counter = p_tax_period_ctr
AND asset_id = l_asset_id(i)
AND distribution_id = l_dist_id(i);
l_sob_id.delete;
l_asset_id.delete;
l_dist_id.delete;
l_rsv_adj.delete;
select asset_id, min(rowid)
from fa_deferred_deprn
where corp_book_type_code = p_corp_book
and tax_book_type_code = p_tax_book
and corp_period_counter = p_corp_period_counter
and tax_period_counter = p_tax_period_counter
and event_id is null
group by asset_id;
select asset_id, min(rowid)
from fa_mc_deferred_deprn
where corp_book_type_code = p_corp_book
and tax_book_type_code = p_tax_book
and corp_period_counter = p_corp_period_counter
and tax_period_counter = p_tax_period_counter
and set_of_books_id = p_set_of_books_id
and event_id is null
group by asset_id;
'bulk inserting into fa_deferred_deprn_events');
INSERT into fa_deferred_deprn_events
(asset_id ,
corp_book_type_code ,
tax_book_type_code ,
corp_period_counter ,
tax_period_counter ,
event_id
)
VALUES
(l_asset_id_tbl(l_count),
p_corp_book,
p_tax_book,
p_corp_period_counter,
p_tax_period_counter,
l_event_id_tbl(l_count));
update fa_deferred_deprn
set event_id = l_event_id_tbl(l_count)
where asset_id = l_asset_id_tbl(l_count)
AND corp_book_type_code = p_corp_book
AND tax_book_type_code = p_tax_book
AND corp_period_counter = p_corp_period_counter
AND tax_period_counter = p_tax_period_counter;
update fa_mc_deferred_deprn
SET event_id = l_event_id_tbl(l_count)
WHERE asset_id = l_asset_id_tbl(l_count)
AND corp_book_type_code = p_corp_book
AND tax_book_type_code = p_tax_book
AND corp_period_counter = p_corp_period_counter
AND tax_period_counter = p_tax_period_counter;
delete from xla_events_int_gt;
'bulk inserting into fa_deferred_deprn_events');
INSERT into fa_deferred_deprn_events
(asset_id ,
corp_book_type_code ,
tax_book_type_code ,
corp_period_counter ,
tax_period_counter ,
event_id
)
VALUES
(l_asset_id_tbl(l_count),
p_corp_book,
p_tax_book,
p_corp_period_counter,
p_tax_period_counter,
l_event_id_tbl(l_count));
UPDATE FA_MC_DEFERRED_DEPRN
SET event_id = l_event_id_tbl(l_count)
WHERE asset_id = l_asset_id_tbl(l_count)
AND corp_book_type_code = p_corp_book
AND tax_book_type_code = p_tax_book
AND corp_period_counter = p_corp_period_counter
AND tax_period_counter = p_tax_period_counter;
delete from xla_events_int_gt;
SELECT 0,
set_of_books_id
FROM fa_book_controls
WHERE book_type_code = p_book_type_code
UNION ALL
SELECT 1, bcm.set_of_books_id
FROM fa_book_controls bc,
fa_mc_book_controls bcm
WHERE bc.book_type_code = p_book_type_code
AND bc.mc_source_flag = 'Y'
AND bcm.book_type_code = bc.book_type_code
AND bcm.primary_set_of_books_id = bc.set_of_books_id
AND bcm.enabled_flag = 'Y'
ORDER BY 1 DESC, 2; -- Process the reporting books first
select period_counter
into l_tax_period_counter
from fa_deprn_periods
where book_type_code = p_tax_book_type_code
and period_name = p_tax_period_name;
select period_counter
into l_corp_period_counter
from fa_deprn_periods
where book_type_code =
fa_cache_pkg.fazcbc_record.distribution_source_book
and period_name = p_corp_period_name;
select 1
into l_deferred_exists_count
from fa_deferred_deprn
where tax_book_type_code = p_tax_book_type_code
and corp_book_type_code =
fa_cache_pkg.fazcbc_record.distribution_source_book
and tax_period_counter = l_tax_period_counter
and corp_period_counter = l_tax_period_counter
and rownum = 1;