[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,
sum (nvl (CORP_DD.Deprn_Amount, 0)),
sum (nvl ( TAX_DD.Deprn_Amount, 0)),
nvl(CORP_DD.Period_Counter, -1),
nvl(TAX_DD.Period_Counter, -1),
nvl(sum(nvl(decode(CORP_AJ.debit_credit_flag, 'CR', 1, -1) *
CORP_AJ.Adjustment_Amount, 0)), 0),
nvl(sum(nvl(decode(TAX_AJ.debit_credit_flag, 'CR', 1, -1) *
TAX_AJ.Adjustment_Amount, 0)), 0)
FROM FA_DISTRIBUTION_HISTORY DH,
FA_DEPRN_DETAIL_MRC_V CORP_DD,
FA_DEPRN_DETAIL_MRC_V TAX_DD,
FA_ASSET_HISTORY AH,
FA_DEPRN_PERIODS_MRC_V DP,
fa_adjustments_mrc_v CORP_AJ,
fa_adjustments_mrc_v TAX_AJ,
fa_book_controls_mrc_v corp_bc,
fa_book_controls_mrc_v tax_bc
WHERE DH.Book_Type_Code = p_corp_book
AND CORP_BC.book_type_code = p_corp_book
AND TAX_BC.book_type_code = p_tax_book
AND CORP_DD.Book_Type_Code (+) = p_corp_book
AND CORP_DD.set_of_books_id = corp_bc.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 = tax_bc.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 DP.Period_Counter = p_tax_period_ctr
AND DP.Book_Type_Code = p_tax_book
AND DP.set_of_books_id = tax_bc.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_AJ.Book_Type_Code(+) = p_corp_book
AND CORP_AJ.set_of_books_id = corp_bc.set_of_books_id
AND CORP_AJ.Period_Counter_Created(+) = p_tax_period_ctr
AND CORP_AJ.Distribution_ID(+) = dh.distribution_id
AND nvl(CORP_AJ.Adjustment_Amount,-9999) <> 0
AND CORP_AJ.Adjustment_Type(+) = 'RESERVE'
AND nvl(CORP_AJ.Track_Member_Flag,'N') = 'N'
AND TAX_AJ.Book_Type_Code (+) = p_corp_book
AND TAX_AJ.Period_Counter_Created (+) = p_tax_period_ctr
AND TAX_AJ.Distribution_ID (+) = dh.distribution_id
AND nvl(TAX_AJ.Adjustment_Amount,-9999) <> 0
AND TAX_AJ.Adjustment_Type (+) = 'RESERVE'
AND nvl(TAX_AJ.Track_Member_Flag,'N') = 'N'
GROUP BY DH.Asset_ID,
DH.Distribution_ID,
DH.Code_Combination_ID,
CORP_DD.Period_counter,
TAX_DD.Period_counter,
CORP_BC.set_of_books_id,
TAX_BC.set_of_books_id
ORDER BY DH.Asset_ID,
DH.Distribution_ID,
DH.Code_Combination_ID;
SELECT DH.Asset_ID,
DH.Distribution_ID,
DH.Code_Combination_ID,
sum (nvl (CORP_DD.Deprn_Amount, 0)),
sum (nvl ( TAX_DD.Deprn_Amount, 0)),
nvl(CORP_DD.Period_Counter, -1),
nvl(TAX_DD.Period_Counter, -1),
nvl(sum(nvl(decode(CORP_AJ.debit_credit_flag, 'CR', 1, -1) *
CORP_AJ.Adjustment_Amount, 0)), 0),
nvl(sum(nvl(decode(TAX_AJ.debit_credit_flag, 'CR', 1, -1) *
TAX_AJ.Adjustment_Amount, 0)), 0)
FROM FA_DISTRIBUTION_HISTORY DH,
FA_DEPRN_DETAIL CORP_DD,
FA_DEPRN_DETAIL TAX_DD,
FA_ASSET_HISTORY AH,
FA_DEPRN_PERIODS DP,
fa_adjustments_mrc_v CORP_AJ,
fa_adjustments_mrc_v TAX_AJ
WHERE DH.Book_Type_Code = p_corp_book
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 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_AJ.Book_Type_Code(+) = p_corp_book
AND CORP_AJ.Period_Counter_Created(+) = p_tax_period_ctr
AND CORP_AJ.Distribution_ID(+) = dh.distribution_id
AND nvl(CORP_AJ.Adjustment_Amount,-9999) <> 0
AND CORP_AJ.Adjustment_Type(+) = 'RESERVE'
AND nvl(CORP_AJ.Track_Member_Flag,'N') = 'N'
AND TAX_AJ.Book_Type_Code (+) = p_tax_book
AND TAX_AJ.Period_Counter_Created (+) = p_tax_period_ctr
AND TAX_AJ.Distribution_ID (+) = dh.distribution_id
AND nvl(TAX_AJ.Adjustment_Amount,-9999) <> 0
AND TAX_AJ.Adjustment_Type (+) = 'RESERVE'
AND nvl(TAX_AJ.Track_Member_Flag,'N') = 'N'
GROUP BY DH.Asset_ID,
DH.Distribution_ID,
DH.Code_Combination_ID,
CORP_DD.Period_counter,
TAX_DD.Period_counter
ORDER BY DH.Asset_ID,
DH.Distribution_ID,
DH.Code_Combination_ID;
select deferred_deprn_expense_acct
into l_deferred_deprn_exp_acct
from fa_book_controls
where book_type_code = p_tax_book;
'bulk inserting into fa_deferred_deprn*' || p_mrc_sob_type_code);
INSERT INTO FA_DEFERRED_DEPRN_MRC_V
(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_rsv_adj(i) - l_corp_rsv_adj(i)),
(l_tax_deprn(i) - l_corp_deprn(i)) +
(l_tax_rsv_adj(i) - l_corp_rsv_adj(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_rsv_adj(i) - l_corp_rsv_adj(i)),
(l_tax_deprn(i) - l_corp_deprn(i)) +
(l_tax_rsv_adj(i) - l_corp_rsv_adj(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;
l_corp_rsv_adj.delete;
l_tax_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;
'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;
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;