[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ytd_deprn, deprn_reserve,
reval_reserve, ytd_reval_deprn_expense,
bonus_ytd_deprn, bonus_deprn_reserve,
period_counter
INTO h_ytd,
h_deprn_reserve,
h_reval_reserve,
h_ytd_reval_dep_exp,
h_bonus_ytd,
h_bonus_deprn_reserve,
h_period_counter
FROM fa_mc_deprn_summary
WHERE book_type_code = X_book_type_code
AND asset_id = X_asset_id
AND deprn_source_code = 'BOOKS'
AND set_of_books_id = X_set_of_books_id;
SELECT decode(ad.asset_type,
'GROUP', 0,
bk.cost)
INTO h_cost
FROM FA_MC_BOOKS bk,
FA_ADDITIONS_B ad
WHERE ad.asset_id = X_asset_id
AND ad.asset_id = bk.asset_id
AND bk.book_type_code = X_book_type_code
and bk.transaction_header_id_out is null
AND set_of_books_id = X_set_of_books_id;
SELECT ytd_deprn, deprn_reserve,
reval_reserve, ytd_reval_deprn_expense,
bonus_ytd_deprn, bonus_deprn_reserve,
period_counter
INTO h_ytd,
h_deprn_reserve,
h_reval_reserve,
h_ytd_reval_dep_exp,
h_bonus_ytd,
h_bonus_deprn_reserve,
h_period_counter
FROM fa_deprn_summary
WHERE book_type_code = X_book_type_code
AND asset_id = X_asset_id
AND deprn_source_code = 'BOOKS';
SELECT decode(ad.asset_type,
'GROUP', 0,
bk.cost)
INTO h_cost
FROM FA_BOOKS bk,
FA_ADDITIONS_B ad
WHERE ad.asset_id = X_asset_id
AND ad.asset_id = bk.asset_id
AND bk.book_type_code = X_book_type_code
and bk.transaction_header_id_out is null;
SELECT sum(nvl(deprn_adjustment_amount,0))
INTO h_deprn_adjustment_amount
FROM fa_mc_deprn_detail
WHERE book_type_code = X_book_type_code
AND asset_id = X_asset_id
AND deprn_source_code = 'B'
AND set_of_books_id = X_set_of_books_id;
SELECT sum(nvl(deprn_adjustment_amount,0))
INTO h_deprn_adjustment_amount
FROM fa_deprn_detail
WHERE book_type_code = X_book_type_code
AND asset_id = X_asset_id
AND deprn_source_code = 'B';
SELECT distribution_id, units_assigned
FROM fa_distribution_history
WHERE book_type_code = h_dist_book AND
asset_id = h_asset_id AND
date_ineffective IS NULL
ORDER BY distribution_id;
SELECT distribution_id, units_assigned
FROM fa_distribution_history
WHERE book_type_code = h_dist_book
AND asset_id = h_asset_id
AND h_add_date BETWEEN
date_effective AND NVL(date_ineffective,SYSDATE)
ORDER BY distribution_id;
SELECT dh.distribution_id,
dh.units_assigned,
dp.period_counter
FROM fa_distribution_history dh, fa_deprn_periods dp, fa_transaction_headers th
WHERE dh.asset_id = X_dpr_dtl.asset_id
AND dh.book_type_code = fa_cache_pkg.fazcbc_record.distribution_source_book
AND dp.book_type_code = X_dpr_dtl.book
AND NVL(dh.date_ineffective, sysdate) >= dp.period_open_date
and dh.transaction_header_id_out IS not NULL
and th.book_type_code = dh.book_type_code
and th.asset_id = dh.asset_id
and th.date_Effective between dp.period_open_date and NVL(dp.period_close_date,sysdate)
and th.transaction_type_code in ('TRANSFER','UNIT ADJUSTMENT','RECLASS','REINSTATEMENT')
--Bug# 7291015 - Added Unit Adjustment,Bug# 6701439 - Added Reclass
/*Bug#7836587 - Added REINSTATEMENT */
and dh.transaction_header_id_out = th.transaction_header_id
and dp.fiscal_year =
(select fiscal_year
from fa_deprn_periods dp2
where dp2.period_close_date is NULL
and book_type_code = X_dpr_dtl.book)
ORDER BY dh.distribution_id;
SELECT NVL(SUM(DECODE(adj.debit_credit_flag,'DR', -1 * adj.adjustment_amount, adj.adjustment_amount)),0)
FROM FA_MC_ADJUSTMENTS adj,
FA_TRANSACTION_HEADERS th
WHERE adj.book_type_code(+) = h_book_type_code
AND adj.asset_id(+) = h_asset_id
AND adj.adjustment_type(+) = decode (th.transaction_type_code, 'CIP ADDITION', 'CIP COST', 'ADDITION', 'COST')
AND adj.distribution_id(+) = h_dist_id
AND th.book_type_code = h_book_type_code
AND th.asset_id = h_asset_id
AND th.transaction_type_code in ('CIP ADDITION','ADDITION')
AND th.transaction_header_id = adj.transaction_header_id(+)
GROUP BY adj.adjustment_amount,debit_credit_flag,th.transaction_header_id;
SELECT NVL(SUM(DECODE(adj.debit_credit_flag,'DR', -1 * adj.adjustment_amount, adj.adjustment_amount)),0)
FROM FA_ADJUSTMENTS adj,
FA_TRANSACTION_HEADERS th
WHERE adj.book_type_code(+) = h_book_type_code
AND adj.asset_id(+) = h_asset_id
AND adj.adjustment_type(+) = decode (th.transaction_type_code, 'CIP ADDITION', 'CIP COST', 'ADDITION', 'COST')
AND adj.distribution_id(+) = h_dist_id
AND th.book_type_code = h_book_type_code
AND th.asset_id = h_asset_id
AND th.transaction_type_code in ('CIP ADDITION','ADDITION')
AND th.transaction_header_id = adj.transaction_header_id(+)
GROUP BY adj.adjustment_amount,debit_credit_flag,th.transaction_header_id;
SELECT COUNT(*)
INTO h_count
FROM FA_MC_DEPRN_DETAIL
WHERE book_type_code = h_book_type_code AND
asset_id = h_asset_id AND
period_counter = h_period_counter;
SELECT COUNT(*)
INTO h_count
FROM FA_DEPRN_DETAIL
WHERE book_type_code = h_book_type_code AND
asset_id = h_asset_id AND
period_counter = h_period_counter;
SELECT nvl(SUM(dh.units_assigned),0), COUNT(*)
INTO h_total_units, h_dist_count
FROM fa_distribution_history dh
WHERE dh.book_type_code = h_dist_book AND
dh.asset_id = h_asset_id AND
dh.date_ineffective IS NULL;
SELECT MIN(date_effective)
INTO h_add_date
FROM fa_transaction_headers
WHERE book_type_code = h_book_type_code
AND asset_id = h_asset_id
AND transaction_type_code LIKE '%ADDITION%';
SELECT nvl(SUM(dh.units_assigned),0), COUNT(*)
INTO h_total_units, h_dist_count
FROM fa_distribution_history dh
WHERE dh.book_type_code = h_dist_book
AND dh.asset_id = h_asset_id
AND h_add_date BETWEEN
date_effective AND NVL(date_ineffective,SYSDATE);
DELETE FROM fa_mc_deprn_detail
WHERE book_type_code = h_book_type_code AND
asset_id = h_asset_id AND
period_counter = h_period_counter AND
set_of_books_id = X_set_of_books_id;
DELETE FROM fa_deprn_detail
WHERE book_type_code = h_book_type_code AND
asset_id = h_asset_id AND
period_counter = h_period_counter;
IF (X_source_flag) THEN -- insert B row
h_source_code := 'B';
h_source_code := 'D'; -- insert D row
SELECT ytd_deprn
INTO h_part_ytd
FROM fa_mc_deprn_detail
WHERE book_type_code = h_book_type_code
and asset_id = h_asset_id
and distribution_id = h_dist_id
and period_counter = (select max(period_counter)
from fa_mc_deprn_detail fdd2
where fdd2.asset_id = h_asset_id
and fdd2.distribution_id = h_dist_id
and fdd2.set_of_books_id = X_set_of_books_id
and fdd2.book_type_code = h_book_type_code)
and set_of_books_id = X_set_of_books_id;
SELECT ytd_deprn
INTO h_part_ytd
FROM fa_deprn_detail
WHERE book_type_code = h_book_type_code
and asset_id = h_asset_id
and distribution_id = h_dist_id
and period_counter = (select max(period_counter)
from fa_deprn_detail fdd2
where fdd2.asset_id = h_asset_id
and fdd2.book_type_code = h_book_type_code
and fdd2.distribution_id = h_dist_id);
/*Bug#8421266 - Insert row in deprn table only when transferred out in current period.*/
if h_pc_counter = h_period_counter then
if (X_mrc_sob_type_code = 'R') then
INSERT INTO fa_mc_deprn_detail
(SET_OF_BOOKS_ID,
BOOK_TYPE_CODE,
ASSET_ID,
DISTRIBUTION_ID,
PERIOD_COUNTER,
DEPRN_RUN_DATE,
DEPRN_AMOUNT,
YTD_DEPRN,
ADDITION_COST_TO_CLEAR,
DEPRN_RESERVE,
/* Bug 525654 Modification */
DEPRN_ADJUSTMENT_AMOUNT,
REVAL_RESERVE,
YTD_REVAL_DEPRN_EXPENSE,
COST,
DEPRN_SOURCE_CODE,
BONUS_DEPRN_AMOUNT,
BONUS_YTD_DEPRN,
BONUS_DEPRN_RESERVE,
BONUS_DEPRN_ADJUSTMENT_AMOUNT,
IMPAIRMENT_AMOUNT,
YTD_IMPAIRMENT,
impairment_reserve,
REVAL_AMORTIZATION,
REVAL_DEPRN_EXPENSE)
VALUES (X_set_of_books_id,
h_book_type_code,
h_asset_id,
h_dist_id,
h_period_counter,
h_sysdate,
0,
h_part_ytd, ---ytd_deprn
0, ---cost_to_clear( doubt for POA)
0,
0,
0,
0,
0,
h_source_code,
0,
0,
0,
0,
0,
0,
0,
0,
0);
INSERT INTO fa_deprn_detail(
BOOK_TYPE_CODE,
ASSET_ID,
DISTRIBUTION_ID,
PERIOD_COUNTER,
DEPRN_RUN_DATE,
DEPRN_AMOUNT,
YTD_DEPRN,
ADDITION_COST_TO_CLEAR,
DEPRN_RESERVE,
/* Bug 525654 Modification */
DEPRN_ADJUSTMENT_AMOUNT,
REVAL_RESERVE,
YTD_REVAL_DEPRN_EXPENSE,
COST,
DEPRN_SOURCE_CODE,
BONUS_DEPRN_AMOUNT,
BONUS_YTD_DEPRN,
BONUS_DEPRN_RESERVE,
BONUS_DEPRN_ADJUSTMENT_AMOUNT,
IMPAIRMENT_AMOUNT,
YTD_IMPAIRMENT,
impairment_reserve,
REVAL_AMORTIZATION,
REVAL_DEPRN_EXPENSE)
VALUES (h_book_type_code,
h_asset_id,
h_dist_id,
h_period_counter,
h_sysdate,
0,
h_part_ytd, ---ytd_deprn
0, ---cost_to_clear( doubt for POA)
0,
0,
0,
0,
0,
h_source_code,
0,
0,
0,
0,
0,
0,
0,
0,
0);
fa_debug_pkg.add('FA_INS_DETAIL_PKG.fadpdtl','values before the insert to deprn_detail','', p_log_level_rec => p_log_level_rec);
INSERT INTO fa_mc_deprn_detail
(SET_OF_BOOKS_ID,
BOOK_TYPE_CODE,
ASSET_ID,
DISTRIBUTION_ID,
PERIOD_COUNTER,
DEPRN_RUN_DATE,
DEPRN_AMOUNT,
YTD_DEPRN,
ADDITION_COST_TO_CLEAR,
DEPRN_RESERVE,
/* Bug 525654 Modification */
DEPRN_ADJUSTMENT_AMOUNT,
REVAL_RESERVE,
YTD_REVAL_DEPRN_EXPENSE,
COST,
DEPRN_SOURCE_CODE,
BONUS_DEPRN_AMOUNT,
BONUS_YTD_DEPRN,
BONUS_DEPRN_RESERVE,
BONUS_DEPRN_ADJUSTMENT_AMOUNT,
IMPAIRMENT_AMOUNT,
YTD_IMPAIRMENT,
impairment_reserve,
REVAL_AMORTIZATION,
REVAL_DEPRN_EXPENSE,
CAPITAL_ADJUSTMENT,
GENERAL_FUND)
VALUES (X_set_of_books_id,
h_book_type_code,
h_asset_id,
h_dist_id,
h_period_counter,
h_sysdate,
h_part_deprn_amount,
h_part_ytd,
decode(h_source_code, 'B', decode(h_count,0,h_part_cost,h_cost_to_clear),0), --Bug 9142501
h_part_deprn_reserve,
/* Bug 525654 Modification */
h_part_deprn_adjustment_amount,
h_part_reval_reserve,
h_part_ytd_reval_dep_exp,
decode(h_source_code, 'D',h_part_cost,0),
h_source_code,
h_part_bonus_deprn_amount,
h_part_bonus_ytd,
h_part_bonus_deprn_reserve,
h_part_bonus_deprn_adj_amount,
h_part_impairment_amount,
h_part_ytd_impairment,
h_part_impairment_reserve,
h_part_reval_amortization,
h_part_reval_deprn_expense,
h_part_capital_adjustment,
h_part_general_fund);
INSERT INTO fa_deprn_detail(BOOK_TYPE_CODE,
ASSET_ID,
DISTRIBUTION_ID,
PERIOD_COUNTER,
DEPRN_RUN_DATE,
DEPRN_AMOUNT,
YTD_DEPRN,
ADDITION_COST_TO_CLEAR,
DEPRN_RESERVE,
/* Bug 525654 Modification */
DEPRN_ADJUSTMENT_AMOUNT,
REVAL_RESERVE,
YTD_REVAL_DEPRN_EXPENSE,
COST,
DEPRN_SOURCE_CODE,
BONUS_DEPRN_AMOUNT,
BONUS_YTD_DEPRN,
BONUS_DEPRN_RESERVE,
BONUS_DEPRN_ADJUSTMENT_AMOUNT,
IMPAIRMENT_AMOUNT,
YTD_IMPAIRMENT,
impairment_reserve,
REVAL_AMORTIZATION,
REVAL_DEPRN_EXPENSE,
CAPITAL_ADJUSTMENT,
GENERAL_FUND)
VALUES (h_book_type_code,
h_asset_id,
h_dist_id,
h_period_counter,
h_sysdate,
h_part_deprn_amount,
h_part_ytd,
decode(h_source_code, 'B', decode(h_count,0,h_part_cost,h_cost_to_clear),0), --Bug 9142501
h_part_deprn_reserve,
/* Bug 525654 Modification */
h_part_deprn_adjustment_amount,
h_part_reval_reserve,
h_part_ytd_reval_dep_exp,
decode(h_source_code, 'D',h_part_cost,0),
h_source_code,
h_part_bonus_deprn_amount,
h_part_bonus_ytd,
h_part_bonus_deprn_reserve,
h_part_bonus_deprn_adj_amount,
h_part_impairment_amount,
h_part_ytd_impairment,
h_part_impairment_reserve,
h_part_reval_amortization,
h_part_reval_deprn_expense,
h_part_capital_adjustment,
h_part_general_fund);