[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
X_LAST_UPDATE_DATE date := sysdate;
X_last_updated_by number := -888;
X_last_update_login number := -888;
adj.last_update_date := p_trans_rec.who_info.last_update_date;
adj.selection_mode := fa_adjust_type_pkg.FA_AJ_SINGLE;
adj.selection_thid := 0;
adj.selection_retid := 0;
/* Insert FA_ADJUSTMENTS rows for all nonzero elements, and for */
/* Deprn Expense even if zero */
if TRUE then
account := fa_cache_pkg.fazccb_record.DEPRN_EXPENSE_ACCT;
X_last_update_date,
X_last_updated_by,
X_last_update_login, p_log_level_rec => p_log_level_rec)) then
raise error_found;
X_last_update_date,
X_last_updated_by,
X_last_update_login, p_log_level_rec => p_log_level_rec)) then
raise error_found;
X_last_update_date,
X_last_updated_by,
X_last_update_login, p_log_level_rec => p_log_level_rec)) then
raise error_found;
X_last_update_date,
X_last_updated_by,
X_last_update_login, p_log_level_rec => p_log_level_rec)) then
raise error_found;
X_last_update_date,
X_last_updated_by,
X_last_update_login, p_log_level_rec => p_log_level_rec)) then
raise error_found;
X_last_update_date,
X_last_updated_by,
X_last_update_login, p_log_level_rec => p_log_level_rec)) then
raise error_found;
X_last_update_date,
X_last_updated_by,
X_last_update_login, p_log_level_rec => p_log_level_rec)) then
raise error_found;
SELECT aj.period_counter_created per_ctr_created,
decode (aj.debit_credit_flag,
'DR', aj.adjustment_amount,
'CR', -aj.adjustment_amount,
0) adj_dr,
decode(sign(
decode (aj.debit_credit_flag,
'DR',aj.adjustment_amount,
'CR', -aj.adjustment_amount,
0) ),
1,aj.annualized_adjustment,-1,-aj.annualized_adjustment,0) ann_adj_dr,
decode (aj.adjustment_type,
'EXPENSE', 1,
'REVAL EXPENSE', 2,
'REVAL AMORT', 3, -- BUG# 3233299
'RESERVE', 4,
'REVAL RESERVE', 5,
'BONUS EXPENSE', 6,
'BONUS RESERVE', 7,
0) adj_type
FROM fa_adjustments aj
WHERE aj.book_type_code = p_asset_hdr_rec.book_type_code
AND aj.asset_id = p_asset_hdr_rec.asset_id
AND aj.distribution_id = p_asset_dist_rec.distribution_id
AND aj.period_counter_created between
from_per_ctr and cur_per_ctr
AND aj.adjustment_type||'' <> 'RESERVE'
AND decode (aj.adjustment_type,
'EXPENSE', 1,
'REVAL EXPENSE', 2,
'REVAL AMORT', 3,
'RESERVE', 4,
'REVAL RESERVE', 5,
'BONUS EXPENSE', 6,
'BONUS RESERVE', 7,
0) <> 0;
SELECT aj.period_counter_created per_ctr_created,
decode (aj.debit_credit_flag,
'DR', aj.adjustment_amount,
'CR', -aj.adjustment_amount,
0) adj_dr,
decode(sign(
decode (aj.debit_credit_flag,
'DR',aj.adjustment_amount,
'CR', -aj.adjustment_amount,
0) ),
1,aj.annualized_adjustment,-1,-aj.annualized_adjustment,0) ann_adj_dr,
decode (aj.adjustment_type,
'EXPENSE', 1,
'REVAL EXPENSE', 2,
'REVAL AMORT', 3, -- BUG# 3233299
'RESERVE', 4,
'REVAL RESERVE', 5,
'BONUS EXPENSE', 6,
'BONUS RESERVE', 7,
0) adj_type
FROM fa_mc_adjustments aj
WHERE aj.book_type_code = p_asset_hdr_rec.book_type_code
AND aj.asset_id = p_asset_hdr_rec.asset_id
AND aj.distribution_id = p_asset_dist_rec.distribution_id
AND aj.set_of_books_id = p_asset_hdr_rec.set_of_books_id
AND aj.period_counter_created between
from_per_ctr and cur_per_ctr
AND aj.adjustment_type||'' <> 'RESERVE'
AND decode (aj.adjustment_type,
'EXPENSE', 1,
'REVAL EXPENSE', 2,
'REVAL AMORT', 3,
'RESERVE', 4,
'REVAL RESERVE', 5,
'BONUS EXPENSE', 6,
'BONUS RESERVE', 7,
0) <> 0;
SELECT aj.period_counter_created per_ctr_created,
decode (aj.debit_credit_flag,
'DR', aj.adjustment_amount,
'CR', -aj.adjustment_amount,
0) adj_dr,
decode(sign(
decode (aj.debit_credit_flag,
'DR',aj.adjustment_amount,
'CR', -aj.adjustment_amount,
0) ),
1,aj.annualized_adjustment,-1,-aj.annualized_adjustment,0) ann_adj_dr,
decode (aj.adjustment_type,
'EXPENSE', 1,
'REVAL EXPENSE', 2,
'REVAL AMORT', 3, -- BUG# 3233299
'RESERVE', 4,
'REVAL RESERVE', 5,
'BONUS EXPENSE', 6,
'BONUS RESERVE', 7,
0) adj_type
FROM fa_adjustments aj
WHERE aj.book_type_code = p_asset_hdr_rec.book_type_code
AND aj.asset_id = p_asset_hdr_rec.asset_id
AND aj.distribution_id = p_asset_dist_rec.distribution_id
AND aj.period_counter_created = cur_per_ctr
AND aj.adjustment_type||'' <> 'RESERVE'
AND decode (aj.adjustment_type,
'EXPENSE', 1,
'REVAL EXPENSE', 2,
'REVAL AMORT', 3,
'RESERVE', 4,
'REVAL RESERVE', 5,
'BONUS EXPENSE', 6,
'BONUS RESERVE', 7,
0) <> 0;
SELECT aj.period_counter_created per_ctr_created,
decode (aj.debit_credit_flag,
'DR', aj.adjustment_amount,
'CR', -aj.adjustment_amount,
0) adj_dr,
decode(sign(
decode (aj.debit_credit_flag,
'DR',aj.adjustment_amount,
'CR', -aj.adjustment_amount,
0) ),
1,aj.annualized_adjustment,-1,-aj.annualized_adjustment,0) ann_adj_dr,
decode (aj.adjustment_type,
'EXPENSE', 1,
'REVAL EXPENSE', 2,
'REVAL AMORT', 3, -- BUG# 3233299
'RESERVE', 4,
'REVAL RESERVE', 5,
'BONUS EXPENSE', 6,
'BONUS RESERVE', 7,
0) adj_type
FROM fa_mc_adjustments aj
WHERE aj.book_type_code = p_asset_hdr_rec.book_type_code
AND aj.asset_id = p_asset_hdr_rec.asset_id
AND aj.distribution_id = p_asset_dist_rec.distribution_id
AND aj.set_of_books_id = p_asset_hdr_rec.set_of_books_id
AND aj.period_counter_created = cur_per_ctr
AND aj.adjustment_type||'' <> 'RESERVE'
AND decode (aj.adjustment_type,
'EXPENSE', 1,
'REVAL EXPENSE', 2,
'REVAL AMORT', 3,
'RESERVE', 4,
'REVAL RESERVE', 5,
'BONUS EXPENSE', 6,
'BONUS RESERVE', 7,
0) <> 0;
SELECT nvl (sum (dd.deprn_amount), 0),
nvl (sum (dd.reval_deprn_expense), 0),
nvl (sum (dd.reval_amortization), 0),
nvl (sum (nvl(dd.bonus_deprn_amount,0)), 0)
INTO dd_deprn_exp,
dd_reval_deprn_exp,
dd_reval_amo,
dd_bonus_deprn_exp
FROM fa_mc_deprn_detail dd
WHERE dd.book_type_code = p_asset_hdr_rec.book_type_code
AND dd.asset_id = p_asset_hdr_rec.asset_id
AND dd.distribution_id = p_asset_dist_rec.distribution_id
AND dd.period_counter between
from_per_ctr and cur_per_ctr
AND dd.set_of_books_id = p_asset_hdr_rec.set_of_books_id;
SELECT nvl (sum (dd.deprn_amount), 0),
nvl (sum (dd.reval_deprn_expense), 0),
nvl (sum (dd.reval_amortization), 0),
nvl (sum (nvl(dd.bonus_deprn_amount,0)), 0)
INTO dd_deprn_exp,
dd_reval_deprn_exp,
dd_reval_amo,
dd_bonus_deprn_exp
FROM fa_deprn_detail dd
WHERE dd.book_type_code = p_asset_hdr_rec.book_type_code
AND dd.asset_id = p_asset_hdr_rec.asset_id
AND dd.distribution_id = p_asset_dist_rec.distribution_id
AND dd.period_counter between
from_per_ctr and cur_per_ctr;
select max(trx.transaction_header_id)
into prev_trx_id
from fa_transaction_headers trx
where trx.book_type_code = p_asset_hdr_rec.book_type_code
and trx.asset_id = p_asset_hdr_rec.asset_id
and trx.transaction_type_code = 'TRANSFER'
and trx.transaction_header_id < p_trans_rec.transaction_header_id
and exists
(select 1
from fa_transaction_headers trx2,
fa_deprn_periods dp_trx,
fa_deprn_periods dp_eff
where trx2.transaction_header_id=trx.transaction_header_id
and trx2.transaction_date_entered between dp_trx.calendar_period_open_date
and dp_trx.calendar_period_close_date
and dp_trx.book_type_code=trx2.book_type_code
and trx2.date_effective between dp_eff.period_open_date
and dp_eff.period_close_date
and dp_eff.book_type_code=trx2.book_type_code
and dp_trx.period_counter < dp_eff.period_counter
and from_per_ctr > dp_trx.period_counter
and from_per_ctr < dp_eff.period_counter
)
;
select dp1.period_counter
,dp2.period_counter
into prev_from_ctr_trx
,prev_from_ctr_eff
from fa_transaction_headers trx,
fa_deprn_periods dp1,
fa_deprn_periods dp2
where trx.transaction_header_id=prev_trx_id
and dp1.book_type_code = trx.book_type_code
and trx.transaction_date_entered between dp1.CALENDAR_PERIOD_OPEN_DATE
and dp1.CALENDAR_PERIOD_CLOSE_DATE
and dp2.book_type_code = trx.book_type_code
and trx.date_effective between dp2.period_open_date
and dp2.period_close_date
;
select dp1.period_counter - dp2.period_counter,
dp2.period_counter,dp3.period_counter,
decode (th.transaction_subtype,'AMORTIZED',1,0)
into h_no_of_per_to_exclude,
h_amort_per_ctr,
h_add_per_ctr,
h_trx_type
from fa_deprn_periods dp1,
fa_deprn_periods dp2,
fa_deprn_periods dp3,
fa_transaction_headers th
where dp1.book_type_code = p_asset_hdr_rec.book_type_code
and dp2.book_type_code = dp1.book_type_code
and dp3.book_type_code = dp1.book_type_code
and dp1.period_counter = from_per_ctr
and th.book_type_code = p_asset_hdr_rec.book_type_code
and th.asset_id = p_asset_hdr_rec.asset_id
and th.transaction_type_code ='ADDITION'
and nvl(th.amortization_start_date,th.transaction_date_entered) between dp2.calendar_period_open_date
and dp2.calendar_period_close_date
and th.date_effective between dp3.period_open_date and dp3.period_close_date;
insert_drs fa_std_types.fa_deprn_row_struct;
select distribution_id
from fa_distribution_history
where asset_id = p_asset_id
and nvl(assigned_to,-9999) = nvl(p_assigned_to,-9999)
and code_combination_id = p_expense_ccid
and location_id = p_location_id
and date_ineffective is null;
SELECT p_psob_id AS sob_id,
1 AS index_id
FROM dual
UNION
SELECT set_of_books_id AS sob_id,
2 AS index_id
FROM fa_mc_book_controls
WHERE book_type_code = p_book_type_code
AND primary_set_of_books_id = p_psob_id
AND enabled_flag = 'Y'
ORDER BY 2;
SELECT cp.period_num + (pers_per_yr * fy.fiscal_year)
INTO ppd_ctr
FROM fa_calendar_periods cp,
fa_calendar_types ct,
fa_fiscal_year fy
WHERE p_trans_rec.transaction_date_entered
between cp.start_date and cp.end_date
AND cp.calendar_type = deprn_calendar
AND ct.calendar_type = cp.calendar_type
AND p_trans_rec.transaction_date_entered
between fy.start_date and fy.end_date
AND fy.fiscal_year_name = fy_name;
b := faxzdrs (insert_drs, p_log_level_rec);
insert_drs.deprn_exp := total_backout_drs.deprn_exp *
(l_asset_dist_rec.transaction_units /
total_ua_backout);
b := fa_utils_pkg.faxrnd(insert_drs.deprn_exp, p_asset_hdr_rec.book_type_code, l_asset_hdr_rec.set_of_books_id,p_log_level_rec => p_log_level_rec);
taken_drs.deprn_exp := taken_drs.deprn_exp + insert_drs.deprn_exp;
insert_drs.reval_deprn_exp := total_backout_drs.reval_deprn_exp *
(l_asset_dist_rec.transaction_units /
total_ua_backout);
b:=fa_utils_pkg.faxrnd(insert_drs.reval_deprn_exp, p_asset_hdr_rec.book_type_code, l_asset_hdr_rec.set_of_books_id, p_log_level_rec => p_log_level_rec);
insert_drs.reval_deprn_exp;
insert_drs.reval_deprn_exp;
insert_drs.reval_amo := total_backout_drs.reval_amo *
(l_asset_dist_rec.transaction_units /
total_ua_backout);
b := fa_utils_pkg.faxrnd(insert_drs.reval_amo, p_asset_hdr_rec.book_type_code,l_asset_hdr_rec.set_of_books_id, p_log_level_rec => p_log_level_rec);
taken_drs.reval_amo := taken_drs.reval_amo + insert_drs.reval_amo;
insert_drs.deprn_rsv := total_backout_drs.deprn_rsv *
(l_asset_dist_rec.transaction_units /
total_ua_backout);
b := fa_utils_pkg.faxrnd(insert_drs.deprn_rsv, p_asset_hdr_rec.book_type_code, l_asset_hdr_rec.set_of_books_id, p_log_level_rec => p_log_level_rec);
taken_drs.deprn_rsv := taken_drs.deprn_rsv + insert_drs.deprn_rsv;
insert_drs.reval_rsv := total_backout_drs.reval_rsv *
(l_asset_dist_rec.transaction_units /
total_ua_backout);
b := fa_utils_pkg.faxrnd(insert_drs.reval_rsv, p_asset_hdr_rec.book_type_code, l_asset_hdr_rec.set_of_books_id, p_log_level_rec => p_log_level_rec);
taken_drs.reval_rsv := taken_drs.reval_rsv + insert_drs.reval_rsv;
insert_drs.bonus_deprn_amount := total_backout_drs.bonus_deprn_amount *
(l_asset_dist_rec.transaction_units /
total_ua_backout);
b := fa_utils_pkg.faxrnd (insert_drs.bonus_deprn_amount, p_asset_hdr_rec.book_type_code,l_asset_hdr_rec.set_of_books_id, p_log_level_rec => p_log_level_rec);
insert_drs.bonus_deprn_amount;
insert_drs.bonus_deprn_rsv := total_backout_drs.bonus_deprn_rsv *
(l_asset_dist_rec.transaction_units /
total_ua_backout);
b := fa_utils_pkg.faxrnd (insert_drs.bonus_deprn_rsv, p_asset_hdr_rec.book_type_code, l_asset_hdr_rec.set_of_books_id, p_log_level_rec => p_log_level_rec);
insert_drs.bonus_deprn_rsv;
insert_drs,
FALSE,
l_mrc_sob_type_code,
p_log_level_rec) then
raise error_found;
insert_drs.deprn_exp := total_backout_drs.deprn_exp - taken_drs.deprn_exp;
insert_drs.reval_deprn_exp := total_backout_drs.reval_deprn_exp -
taken_drs.reval_deprn_exp;
insert_drs.reval_amo := total_backout_drs.reval_amo-taken_drs.reval_amo;
insert_drs.deprn_rsv := total_backout_drs.deprn_rsv-taken_drs.deprn_rsv;
insert_drs.reval_rsv := total_backout_drs.reval_rsv-taken_drs.reval_rsv;
insert_drs.bonus_deprn_amount := total_backout_drs.bonus_deprn_amount -
taken_drs.bonus_deprn_amount;
insert_drs.bonus_deprn_rsv := total_backout_drs.bonus_deprn_rsv -
taken_drs.bonus_deprn_rsv;
insert_drs,
FALSE,
l_mrc_sob_type_code,
p_log_level_rec) then
raise error_found;
| Also, added faduxx to update FA_BOOKS with new adjusted rates, bonus |
| rule, bonus rates for NBV assets |
| |
| NOTES |
| obsolete for SLA |
+===========================================================================*/
/*===========================================================================+
| |
| fadpaa |
| |
| FA Depreciation Process Adjustments Array |
| |
| Inserts a row into fa_deprn_detail for each row in the Adjustments |
| array; then inserts one row into fa_deprn_summary for |
| 1. updates deprn_detail and deprn_summary records by adding the |
| adjustments records to them where the deprn records exist. |
| 2. inserts deprn_detail and deprn_summary records by copying |
| the adjustments records where the deprn records don't exist. |
| |
| NOTES |
| this remains in pro*c and is not part of SLA |
+===========================================================================*/
END FA_TRANSFER_PVT;