[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| FUNCTION Set selection_mode accordingly for Tax book
|
|
| History YYOON 05/23/06 Created
| added for the bug 5149832 and 5231996
|===========================================================================*/
Function faginfo(
RET IN fa_ret_types.ret_struct,
BK IN fa_ret_types.book_struct,
cpd_ctr IN NUMBER,
today IN DATE,
user_id IN NUMBER,
calling_module IN varchar,
candidate_mode IN varchar,
set_adj_row IN boolean,
unit_ret_in_corp OUT nocopy boolean,
ret_id_in_corp OUT nocopy number,
th_id_out_in_corp OUT nocopy number,
balance_tfr_in_tax OUT nocopy number,
adj_row IN OUT nocopy FA_ADJUST_TYPE_PKG.fa_adj_row_struct,
p_log_level_rec IN FA_API_TYPES.log_level_rec_type) return boolean IS
l_unit_ret_in_corp boolean;
l_selection_retid number;
select r.retirement_id
,r.units
into l_ret_id_in_corp
,l_units_retired
from fa_transaction_headers sth
,fa_retirements r
,fa_book_controls bc
where sth.transaction_header_id = RET.th_id_in
and sth.asset_id = RET.asset_id
and sth.book_type_code = RET.book
and bc.book_type_code = sth.book_type_code
and bc.book_class = 'TAX'
and r.asset_id = sth.asset_id
and r.transaction_header_id_in = nvl(sth.source_transaction_header_id, sth.transaction_header_id)
and rownum = 1;
select transaction_header_id_out
into l_id_out
from fa_distribution_history
where retirement_id =
(select r.retirement_id
from fa_transaction_headers sth
,fa_retirements r
where sth.transaction_header_id = RET.th_id_in
and sth.asset_id = RET.asset_id
and sth.book_type_code = RET.book
and r.asset_id = sth.asset_id
and r.transaction_header_id_in = nvl(sth.source_transaction_header_id, sth.transaction_header_id)
)
and transaction_header_id_out is not null
and rownum = 1;
select count(*)
into l_balance_tfr_in_tax
from fa_adjustments
where book_type_code = RET.book
and asset_id = RET.asset_id
and transaction_header_id = l_id_out -- Corp's TRANSFER OUT THID
and source_type_code in ('TRANSFER', 'RETIREMENT')
and adjustment_amount <> 0
and rownum = 1;
select count(*)
into l_balance_tfr_in_tax
from fa_mc_adjustments
where book_type_code = RET.book
and asset_id = RET.asset_id
and transaction_header_id = l_id_out -- Corp's TRANSFER OUT THID
and source_type_code in ('TRANSFER', 'RETIREMENT') -- TRANSFER for part-ret, RETIREMENT for reinst of full retirement
and adjustment_amount <> 0
and set_of_books_id = ret.set_of_books_id
and rownum = 1;
select 2
into l_balance_tfr_in_tax
from fa_distribution_history
where asset_id = RET.asset_id
and transaction_header_id_out is NULL
and rownum = 1
and distribution_id =
nvl((select max(distribution_id)
from fa_adjustments
where book_type_code = RET.book
and asset_id = RET.asset_id
and adjustment_amount <> 0
and transaction_header_id < RET.th_id_in
and distribution_id in( /*12905004 - Select max dist_id in active dists */
select distribution_id
from fa_distribution_history
where asset_id = RET.asset_id
and transaction_header_id_out is NULL)),distribution_id);
select 2 -- this has to be set to 2 to differ from 1 for a regular balance tfr; used for fagurt
nvl((select max(distribution_id) /*Bug 8301287. There may be a case where there exists no Records*/
from fa_mc_adjustments
where book_type_code = RET.book
and asset_id = RET.asset_id
-- and source_type_code in ('ADDITION')
and adjustment_amount <> 0
and set_of_books_id = ret.set_of_books_id
and transaction_header_id < RET.th_id_in
and distribution_id in( /*12905004 - Select max dist_id in active dists */
select distribution_id
from fa_distribution_history
where asset_id = RET.asset_id
and transaction_header_id_out is NULL)),distribution_id);
adj_row.selection_thid := 0;
adj_row.selection_mode := FA_STD_TYPES.FA_AJ_ACTIVE;
fa_debug_pkg.add(l_calling_fn, '++++ selection_mode', 'FA_STD_TYPES.FA_AJ_ACTIVE', p_log_level_rec => p_log_level_rec);
adj_row.selection_retid := l_ret_id_in_corp;
adj_row.selection_mode := FA_STD_TYPES.FA_AJ_RETIRE;
fa_debug_pkg.add(l_calling_fn, '++++ selection_mode', 'FA_STD_TYPES.FA_AJ_RETIRE', p_log_level_rec => p_log_level_rec);
adj_row.selection_thid := l_id_out;
adj_row.selection_mode := FA_STD_TYPES.FA_AJ_CLEAR_PARTIAL;
fa_debug_pkg.add(l_calling_fn, '++++ selection_mode', 'FA_STD_TYPES.FA_AJ_CLEAR_PARTIAL', p_log_level_rec => p_log_level_rec);
adj_row.selection_thid := 0;
adj_row.selection_mode := FA_STD_TYPES.FA_AJ_ACTIVE;
fa_debug_pkg.add(l_calling_fn, '++++ in ELSE: selection_mode', 'FA_STD_TYPES.FA_AJ_ACTIVE', p_log_level_rec => p_log_level_rec);
SELECT (MONTHS_BETWEEN(trunc(h_date_retired),
trunc(h_date_placed)) / 12) + 1
INTO h_years_kept
FROM FA_RETIREMENTS
WHERE RETIREMENT_ID = h_retirement_id;
SELECT farecap.itc_recapture_id ,
h_itc_amount * farecap.itc_recapture_rate *
h_cost_frac
INTO
h_itc_recapture_id,
h_recaptured
FROM fa_itc_recapture_rates farecap,
fa_itc_rates farate
WHERE farecap.tax_year = farate.tax_year
AND farecap.life_in_months = farate.life_in_months
AND farecap.year_of_retirement = h_years_kept
AND farate.itc_amount_id = h_itc_amount_id;
UPDATE fa_retirements fr
SET fr.itc_recaptured = h_recaptured,
fr.itc_recapture_id = h_itc_recapture_id
WHERE fr.retirement_id = h_retirement_id;
UPDATE fa_mc_retirements fr
SET fr.itc_recaptured = h_recaptured,
fr.itc_recapture_id = h_itc_recapture_id
WHERE fr.retirement_id = h_retirement_id
AND fr.set_of_books_id = ret.set_of_books_id;
| It calculates GAIN/LOSS, NBV_RETIRED, STL_DEPRN_AMOUNT. Update the |
| status in FA_RETIREMENTS table from 'PENDING' to 'PROCESSED'. It |
| also inserts GAIN/LOSS, PROCEEDS_OF_SALE, and COST_OF_REMOVAL to |
| FA_ADJUSTMENTS table. |
| |
| HISTORY 01/12/89 R Rumanang Created |
| 08/30/89 R Rumanang Updated to insert to |
| FA_ADJUSTMENTS. |
| 01/31/90 R Rumanang Insert PROCEEDS_OF_SALE to |
| ADJUSTMENT |
| 05/03/91 M Chan Rewrote for MPL 9 |
| 12/30/96 S Behura Rewriting in PL/SQL |
*============================================================================*/
FUNCTION fagurt(ret in out nocopy fa_ret_types.ret_struct,
bk in out nocopy fa_ret_types.book_struct,
cpd_ctr number,
dpr in out nocopy FA_STD_TYPES.dpr_struct,
cost_frac in number,
retpdnum in out nocopy number,
today in date,
user_id number,
p_log_level_rec in FA_API_TYPES.log_level_rec_type) return boolean IS
reval_deprn_amt number;
X_LAST_UPDATE_DATE date := sysdate;
X_last_updated_by number := -1;
X_last_update_login number := -1;
SELECT DISTRIBUTION_ID,
CODE_COMBINATION_ID,
LOCATION_ID,
ASSIGNED_TO, -- bug 3519644
TRANSACTION_HEADER_ID_OUT
FROM FA_DISTRIBUTION_HISTORY dist,
FA_BOOK_CONTROLS bc
-- Bug 5149832 WHERE RETIREMENT_ID = ret.retirement_id
WHERE RETIREMENT_ID = nvl(l_ret_id_in_corp, ret.retirement_id)
AND ASSET_ID = ret.asset_id
-- Bug 5149832 AND BOOK_TYPE_CODE = ret.book;
SELECT DISTRIBUTION_ID
FROM FA_DISTRIBUTION_HISTORY dist,
FA_BOOK_CONTROLS bc
WHERE dist.TRANSACTION_HEADER_ID_IN = c_th_id_out
AND dist.TRANSACTION_HEADER_ID_OUT is NULL
AND dist.CODE_COMBINATION_ID = c_ccid
AND dist.LOCATION_ID = c_location_id
AND dist.ASSET_ID = ret.asset_id
-- Bug 5149832 AND BOOK_TYPE_CODE = ret.book
AND bc.book_type_code = RET.book
AND dist.BOOK_TYPE_CODE = bc.distribution_source_book
AND nvl (dist.assigned_to, -99) = nvl (c_assigned_to, -99); -- bug 3519644
SELECT DISTRIBUTION_ID
FROM FA_DISTRIBUTION_HISTORY dist,
FA_BOOK_CONTROLS bc
WHERE
dist.CODE_COMBINATION_ID = c_ccid
AND dist.LOCATION_ID = c_location_id
AND dist.ASSET_ID = RET.asset_id
AND bc.book_type_code = RET.book
AND bc.book_class = 'TAX'
AND dist.BOOK_TYPE_CODE = bc.distribution_source_book
AND dist.transaction_header_id_in =
(select max(adj.transaction_header_id) -- get the latest THID in the same period that caused DIST ID to change
from fa_adjustments adj
where adj.book_type_code = RET.book
and adj.asset_id = RET.asset_id
and adj.source_type_code in ('RETIREMENT', 'TRANSFER') -- RETIREMENT: balance tfr for Reinstatement, TRANSFER: balance tfr for Retirement
and adj.period_counter_created = cpd_ctr
and adj.adjustment_type = 'COST'
and not exists -- check to see if adj.THID is from Corp
(select 1
from fa_transaction_headers th
where th.transaction_header_id = adj.transaction_header_id
and th.book_type_code = RET.book
and th.asset_id = RET.asset_id
)
)
;
SELECT DISTRIBUTION_ID,
CODE_COMBINATION_ID
FROM FA_DISTRIBUTION_HISTORY dist,
FA_BOOK_CONTROLS bc
WHERE TRANSACTION_HEADER_ID_OUT is NULL
AND ASSET_ID = c_asset_id
AND bc.book_type_code = ret.book
AND dist.book_type_code = bc.distribution_source_book;
SELECT SUM(NVL(DECODE(DEBIT_CREDIT_FLAG,
'CR', ADJUSTMENT_AMOUNT,
-1 * ADJUSTMENT_AMOUNT), 0)*
DECODE(ADJUSTMENT_TYPE, 'RESERVE', -1,
'IMPAIR RESERVE', -1, 1)) --hh: 6867027
FROM FA_ADJUSTMENTS
WHERE (DISTRIBUTION_ID = c_dist_id
OR DISTRIBUTION_ID = c_new_dist_id)
AND BOOK_TYPE_CODE = ret.book
AND PERIOD_COUNTER_CREATED = cpd_ctr
AND SOURCE_TYPE_CODE = c_source_type_code
AND ADJUSTMENT_TYPE = c_adjustment_type
AND TRANSACTION_HEADER_ID = ret.th_id_in;
SELECT SUM(NVL(DECODE(DEBIT_CREDIT_FLAG,
'CR', ADJUSTMENT_AMOUNT,
-1 * ADJUSTMENT_AMOUNT), 0)*
DECODE(ADJUSTMENT_TYPE, 'RESERVE', -1,
'IMPAIR RESERVE', -1, 1)) --hh: 6867027
FROM FA_MC_ADJUSTMENTS
WHERE (DISTRIBUTION_ID = c_dist_id
OR DISTRIBUTION_ID = c_new_dist_id)
AND BOOK_TYPE_CODE = ret.book
AND PERIOD_COUNTER_CREATED = cpd_ctr
AND SOURCE_TYPE_CODE = c_source_type_code
AND ADJUSTMENT_TYPE = c_adjustment_type
AND set_of_books_id = ret.set_of_books_id
AND TRANSACTION_HEADER_ID = ret.th_id_in;
select units
from fa_asset_history
where asset_id = c_asset_id
and transaction_header_id_out is null;
select adjustment_amount
from fa_adjustments
where asset_id = bk.group_asset_id
and book_type_code = ret.book
and transaction_header_id = ret.th_id_in
and adjustment_type = 'RESERVE';
select adjustment_amount
from fa_mc_adjustments
where asset_id = bk.group_asset_id
and book_type_code = ret.book
and transaction_header_id = ret.th_id_in
and adjustment_type = 'RESERVE'
and set_of_books_id = ret.set_of_books_id;
select sum(decode(adjustment_type
,'COST', decode(debit_credit_flag,'CR', nvl(adjustment_amount,0), -1 * nvl(adjustment_amount,0))
,'RESERVE', decode(debit_credit_flag,'DR', -1 * nvl(adjustment_amount,0), nvl(adjustment_amount,0))
,'IMPAIR RESERVE', decode(debit_credit_flag,'DR', -1 * nvl(adjustment_amount,0), nvl(adjustment_amount,0))
,0))
from fa_adjustments
where asset_id = ret.asset_id
and book_type_code = ret.book
and transaction_header_id = ret.th_id_in
and source_type_code='RETIREMENT'
and adjustment_type in ('COST', 'RESERVE', 'IMPAIR RESERVE'); --hh: 6867027
select sum(decode(adjustment_type
,'COST', decode(debit_credit_flag,'CR', nvl(adjustment_amount,0), -1 * nvl(adjustment_amount,0))
,'RESERVE', decode(debit_credit_flag,'DR', -1 * nvl(adjustment_amount,0), nvl(adjustment_amount,0))
,'IMPAIR RESERVE', decode(debit_credit_flag,'DR', -1 * nvl(adjustment_amount,0), nvl(adjustment_amount,0))
,0))
from fa_mc_adjustments
where asset_id = ret.asset_id
and book_type_code = ret.book
and transaction_header_id = ret.th_id_in
and source_type_code='RETIREMENT'
and set_of_books_id = ret.set_of_books_id
and adjustment_type in ('COST', 'RESERVE', 'IMPAIR RESERVE'); --hh: 6867027
select bc.proceeds_of_sale_clearing_acct,
bc.proceeds_of_sale_gain_acct,
bc.proceeds_of_sale_loss_acct,
bc.cost_of_removal_clearing_acct,
bc.cost_of_removal_gain_acct,
bc.cost_of_removal_loss_acct,
bc.nbv_retired_gain_acct,
bc.nbv_retired_loss_acct,
bc.reval_rsv_retired_gain_acct,
bc.reval_rsv_retired_loss_acct,
decode(bc.retire_reval_reserve_flag,'NO',0,1),
ad.asset_category_id
into h_proc_of_sale_clearing_acct,
h_proceeds_of_sale_gain_acct,
h_proceeds_of_sale_loss_acct,
h_cost_of_removal_clr_acct,
h_cost_of_removal_gain_acct,
h_cost_of_removal_loss_acct,
h_nbv_retired_gain_acct,
h_nbv_retired_loss_acct,
h_reval_rsv_retired_gain_acct,
h_reval_rsv_retired_loss_acct,
h_retire_reval_flag,
l_asset_cat_rec_m.category_id
from fa_book_controls bc,
fa_additions_b ad, fa_category_books cb
where ad.asset_id = h_asset_id
and cb.category_id = ad.asset_category_id
and cb.book_type_code = h_book
and bc.book_type_code = cb.book_type_code;
UPDATE fa_retirements fr
SET fr.nbv_retired = nvl(h_g_nbv_retired, h_nbv_retired), --Bug11886090
fr.gain_loss_amount = h_gain_loss,
fr.stl_deprn_amount = h_stl_deprn,
fr.reval_reserve_retired = h_reval_rsv_retired,
bonus_reserve_retired = h_bonus_rsv_retired,
impair_reserve_retired = h_impair_rsv_retired,
fr.unrevalued_cost_retired =
h_unrevalued_cost_retired,
fr.reserve_retired = nvl(fr.reserve_retired,ret.reserve_retired),
fr.status = 'PROCESSED',
fr.last_update_date = h_today,
fr.last_updated_by = h_user_id
WHERE
fr.retirement_id = h_retirement_id;
UPDATE fa_mc_retirements fr
SET fr.nbv_retired = nvl(h_g_nbv_retired, h_nbv_retired), --Bug11886090
fr.gain_loss_amount = h_gain_loss,
fr.stl_deprn_amount = h_stl_deprn,
fr.reval_reserve_retired = h_reval_rsv_retired,
bonus_reserve_retired = h_bonus_rsv_retired,
impair_reserve_retired = h_impair_rsv_retired,
fr.unrevalued_cost_retired =
h_unrevalued_cost_retired,
fr.reserve_retired = nvl(fr.reserve_retired,ret.reserve_retired),
fr.status = 'PROCESSED',
fr.last_update_date = h_today,
fr.last_updated_by = h_user_id
WHERE
fr.retirement_id = h_retirement_id
AND set_of_books_id = ret.set_of_books_id;
The amount that we inserted into the table must be positive.
*/
-- Setting l_unit_ret_in_corp
if NOT faginfo(
RET, BK, cpd_ctr,today, user_id
,calling_module => l_calling_fn
,candidate_mode => 'RETIRE'
,set_adj_row => FALSE -- just to get l_unit_ret_in_corp and h_id_out
,unit_ret_in_corp => l_unit_ret_in_corp
,ret_id_in_corp => l_ret_id_in_corp
,th_id_out_in_corp => h_id_out
,balance_tfr_in_tax => l_balance_tfr_in_tax
,adj_row => adj_row
,p_log_level_rec => p_log_level_rec) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
adj_row.last_update_date := today;
adj_row.selection_thid := 0;
adj_row.selection_mode := FA_STD_TYPES.FA_AJ_ACTIVE;
adj_row.selection_retid := 0;
adj_row.selection_mode := FA_STD_TYPES.FA_AJ_ACTIVE;
adj_row.selection_mode := FA_STD_TYPES.FA_AJ_RETIRE;
adj_row.selection_retid := ret.retirement_id;
X_last_update_date,
X_last_updated_by,
X_last_update_login,
p_log_level_rec => p_log_level_rec)) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
X_last_update_date,
X_last_updated_by,
X_last_update_login,
p_log_level_rec => p_log_level_rec)) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
X_last_update_date,
X_last_updated_by,
X_last_update_login,
p_log_level_rec => p_log_level_rec)) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
X_last_update_date,
X_last_updated_by,
X_last_update_login,
p_log_level_rec => p_log_level_rec)) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
adj_row.selection_mode := fa_adjust_type_pkg.FA_AJ_TRANSFER_SINGLE;
SELECT count(*)
INTO tot_dist_lines
FROM FA_DISTRIBUTION_HISTORY dist,
FA_BOOK_CONTROLS bc
WHERE TRANSACTION_HEADER_ID_OUT is NULL
AND ASSET_ID = RET.asset_id
AND bc.book_type_code = RET.book
AND dist.book_type_code = bc.distribution_source_book;
SELECT count(*)
INTO tot_dist_lines
FROM FA_DISTRIBUTION_HISTORY dist,
FA_BOOK_CONTROLS bc
WHERE dist.RETIREMENT_ID = nvl(l_ret_id_in_corp, ret.retirement_id)
AND dist.ASSET_ID = RET.asset_id
AND bc.book_type_code = RET.book
AND dist.book_type_code = bc.distribution_source_book;
X_last_update_date,
X_last_updated_by,
X_last_update_login, p_log_level_rec => p_log_level_rec)) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
X_last_update_date,
X_last_updated_by,
X_last_update_login, p_log_level_rec => p_log_level_rec) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
X_last_update_date,
X_last_updated_by,
X_last_update_login, p_log_level_rec => p_log_level_rec) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
adj_row.selection_mode := FA_STD_TYPES.FA_AJ_ACTIVE;
adj_row.selection_retid := 0;
X_last_update_date,
X_last_updated_by,
X_last_update_login,
p_log_level_rec => p_log_level_rec)) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
adj_row.selection_mode := FA_STD_TYPES.FA_AJ_ACTIVE;
adj_row.selection_retid := 0;
adj_row.selection_mode := FA_STD_TYPES.FA_AJ_RETIRE;
adj_row.selection_retid := ret.retirement_id;
value => adj_row.selection_mode, p_log_level_rec => p_log_level_rec);
X_last_update_date,
X_last_updated_by,
X_last_update_login,
p_log_level_rec => p_log_level_rec)) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
adj_row.selection_mode := FA_STD_TYPES.FA_AJ_ACTIVE;
adj_row.selection_retid := 0;
X_last_update_date,
X_last_updated_by,
X_last_update_login,
p_log_level_rec => p_log_level_rec)) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
X_last_update_date,
X_last_updated_by,
X_last_update_login,
p_log_level_rec => p_log_level_rec)) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
X_last_update_date,
X_last_updated_by,
X_last_update_login,
p_log_level_rec => p_log_level_rec)) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
X_last_update_date,
X_last_updated_by,
X_last_update_login,
p_log_level_rec => p_log_level_rec)) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
X_LAST_UPDATE_DATE date := sysdate;
X_last_updated_by number := -1;
X_last_update_login number := -1;
select asset_cost_acct,
nvl(cip_cost_acct, '0')
into h_asset_cost_acct,
h_cip_cost_acct
from fa_additions_b faadd,
fa_category_books facb
where faadd.asset_id = h_asset_id
and facb.category_id = faadd.asset_category_id
and facb.book_type_code = h_book;
adj_row.last_update_date := today;
adj_row.selection_retid := 0;
adj_row.selection_thid := 0;
adj_row.selection_mode := FA_STD_TYPES.FA_AJ_CLEAR;
adj_row.selection_mode := FA_STD_TYPES.FA_AJ_ACTIVE;
fa_debug_pkg.add(l_calling_fn, '++ adj_row.selection_thid', adj_row.selection_thid, p_log_level_rec => p_log_level_rec);
X_last_update_date,
X_last_updated_by,
X_last_update_login
, p_log_level_rec => p_log_level_rec)) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
select distinct nvl(transaction_header_id_out,0)
into h_id_out
from fa_distribution_history
where asset_id = h_asset_id
and book_type_code = h_book
and retirement_id = h_ret_id;
adj_row.selection_thid := h_id_out;
adj_row.selection_mode := FA_STD_TYPES.FA_AJ_CLEAR_PARTIAL;
X_last_update_date,
X_last_updated_by,
X_last_update_login,
p_log_level_rec => p_log_level_rec)) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
adj_row.adjustment_amount := adj_row.amount_inserted-
ret.cost_retired;
select nvl(units,0)
into h_cur_units
from fa_asset_history
where asset_id = h_asset_id
and date_ineffective is null;
select 1
into l_dummy
from fa_distribution_history
where asset_id = h_asset_id
and date_ineffective is null
and transaction_header_id_in = h_id_out
and rownum = 1;
select sum(nvl(units_assigned,0))
into h_cur_units
from fa_distribution_history
where asset_id = h_asset_id
and date_ineffective is null
and transaction_header_id_in = h_id_out;
adj_row.selection_thid := h_id_out;
adj_row.selection_mode := FA_STD_TYPES.FA_AJ_ACTIVE_PARTIAL;
X_last_update_date,
X_last_updated_by,
X_last_update_login
, p_log_level_rec => p_log_level_rec)) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
| insert them into fa_adjustments table. |
| |
| History 11/13/92 L. Sun Created |
| |
| 12/31/96 S. Behura Rewrote into PL/SQL |
| 11/08/97 S. Behura Rewrote into PL/SQL(10.7)|
*=====================================================================*/
FUNCTION farboe(asset_id number, book in varchar2,
current_fiscal_yr number, cost_frac in number,
start_pdnum number, end_pdnum number,
adj_type in varchar2, pds_per_year number,
dpr_evenly number, fiscal_year_name in varchar2,
units_retired number, th_id_in number,
cpd_ctr number, today in date,
current_units number, retirement_id number, d_cal in varchar2,
dpr in out nocopy FA_STD_TYPES.dpr_struct, p_cal in varchar2,
pds_catchup number, depreciate_lastyr boolean,
start_pp number, end_pp number,
mrc_sob_type_code in varchar2,
ret in fa_ret_types.ret_struct,
bk in out nocopy fa_ret_types.book_struct,
p_log_level_rec in FA_API_TYPES.log_level_rec_type) Return BOOLEAN IS
farboe_err exception;
X_LAST_UPDATE_DATE date := sysdate;
X_last_updated_by number := -1;
X_last_update_login number := -1;
* rows yet, and the original select statement would not have
* returned these adjustment amounts. --y.i.
*/
CURSOR DEPRN_ADJ IS
SELECT fadd.distribution_id,
fadh.code_combination_id,
-1 * h_cost_frac *
(decode (h_adj_type,
'EXPENSE', fadd.deprn_amount,
'BONUS EXPENSE', fadd.bonus_deprn_amount,
'IMPAIR EXPENSE', fadd.impairment_amount,
'REVAL EXPENSE', fadd.reval_deprn_expense,
'REVAL AMORT', fadd.reval_amortization) -
nvl(sum(decode (faadj.debit_credit_flag, 'DR', 1, -1) *
faadj.adjustment_amount), 0)),
nvl(SUM(-1 * h_cost_frac *
decode (faadj.debit_credit_flag, 'DR', 1, -1) *
faadj.adjustment_amount), 0),
nvl(SUM(-1 * h_cost_frac *
decode (faadj.debit_credit_flag, 'DR', 1, -1) *
faadj.annualized_adjustment), 0),
nvl(-1 * h_cost_frac *
(decode (h_adj_type,
'EXPENSE',(fadd.deprn_amount -
fadd.deprn_adjustment_amount),
'BONUS EXPENSE',(fadd.bonus_deprn_amount -
fadd.bonus_deprn_adjustment_amount),
'IMPAIR EXPENSE', fadd.impairment_amount,
'REVAL EXPENSE', fadd.reval_deprn_expense,
'REVAL AMORT', fadd.reval_amortization)),0),
nvl(-1 * h_cost_frac *
(decode (h_adj_type,
'EXPENSE', fadd.deprn_amount,
'BONUS EXPENSE', fadd.bonus_deprn_amount,
'IMPAIR EXPENSE', fadd.impairment_amount,
'REVAL EXPENSE', fadd.reval_deprn_expense,
'REVAL AMORT', fadd.reval_amortization)),0),
nvl(faadj.source_type_code, 'DEPRECIATION'),
fadp.period_counter,
faadj.transaction_header_id
FROM
fa_distribution_history fadh,
fa_deprn_detail fadd,
fa_deprn_periods fadp,
fa_adjustments faadj
WHERE
fadd.asset_id = h_asset_id
AND fadd.distribution_id = fadh.distribution_id
AND fadd.book_type_code = h_book
AND fadd.deprn_source_code = 'D'
AND fadd.period_counter = fadp.period_counter
AND fadp.period_num = h_i
AND fadp.book_type_code = h_book
AND fadp.fiscal_year = h_current_fiscal_yr
AND faadj.distribution_id(+) = fadd.distribution_id
AND faadj.book_type_code(+) = fadd.book_type_code
AND faadj.asset_id(+) = fadd.asset_id
AND faadj.period_counter_created(+) = fadd.period_counter
AND faadj.adjustment_type(+) = h_adj_type
GROUP BY
fadd.distribution_id,
fadh.code_combination_id,
fadd.deprn_amount,
fadd.deprn_adjustment_amount,
fadd.bonus_deprn_amount,
fadd.bonus_deprn_adjustment_amount,
fadd.impairment_amount,
fadd.reval_deprn_expense,
fadd.reval_amortization,
faadj.distribution_id,
faadj.source_type_Code,
faadj.adjustment_amount,
fadp.period_counter,
faadj.transaction_header_id
UNION
SELECT fadh.distribution_id,
fadh.code_combination_id,
0,
nvl(SUM(-1 * h_cost_frac *
decode (faadj.debit_credit_flag, 'DR', 1, -1) *
faadj.adjustment_amount), 0),
nvl(SUM(-1 * h_cost_frac *
decode (faadj.debit_credit_flag, 'DR', 1, -1) *
faadj.annualized_adjustment), 0),
0,
0,
nvl(faadj.source_type_code, 'DEPRECIATION'),
fadp.period_counter,
faadj.transaction_header_id
FROM
fa_distribution_history fadh,
fa_deprn_periods fadp,
fa_adjustments faadj
WHERE
fadp.period_num = h_i
AND fadp.book_type_code = h_book
AND fadp.fiscal_year = h_current_fiscal_yr
AND fadp.period_counter = h_cpd_ctr
AND faadj.distribution_id = fadh.distribution_id
AND faadj.book_type_code = fadp.book_type_code
AND faadj.asset_id = h_asset_id
AND faadj.period_counter_created = fadp.period_counter
AND faadj.adjustment_type = h_adj_type
GROUP BY
fadh.distribution_id,
fadh.code_combination_id,
faadj.distribution_id,
faadj.source_type_Code,
faadj.adjustment_amount,
fadp.period_counter,
faadj.transaction_header_id;
SELECT fadd.distribution_id,
fadh.code_combination_id,
-1 * h_cost_frac *
(decode (h_adj_type,
'EXPENSE', fadd.deprn_amount,
'BONUS EXPENSE', fadd.bonus_deprn_amount,
'IMPAIR EXPENSE', fadd.impairment_amount,
'REVAL EXPENSE', fadd.reval_deprn_expense,
'REVAL AMORT', fadd.reval_amortization) -
nvl(sum(decode (faadj.debit_credit_flag, 'DR', 1, -1) *
faadj.adjustment_amount), 0)),
nvl(SUM(-1 * h_cost_frac *
decode (faadj.debit_credit_flag, 'DR', 1, -1) *
faadj.adjustment_amount), 0),
nvl(SUM(-1 * h_cost_frac *
decode (faadj.debit_credit_flag, 'DR', 1, -1) *
faadj.annualized_adjustment), 0),
nvl(-1 * h_cost_frac *
(decode (h_adj_type,
'EXPENSE',(fadd.deprn_amount -
fadd.deprn_adjustment_amount),
'BONUS EXPENSE',(fadd.bonus_deprn_amount -
fadd.bonus_deprn_adjustment_amount),
'IMPAIR EXPENSE', fadd.impairment_amount,
'REVAL EXPENSE', fadd.reval_deprn_expense,
'REVAL AMORT', fadd.reval_amortization)),0),
nvl(-1 * h_cost_frac *
(decode (h_adj_type,
'EXPENSE', fadd.deprn_amount,
'BONUS EXPENSE', fadd.bonus_deprn_amount,
'IMPAIR EXPENSE', fadd.impairment_amount,
'REVAL EXPENSE', fadd.reval_deprn_expense,
'REVAL AMORT', fadd.reval_amortization)),0),
nvl(faadj.source_type_code, 'DEPRECIATION'),
fadp.period_counter,
faadj.transaction_header_id
FROM
fa_distribution_history fadh,
fa_mc_deprn_detail fadd,
fa_deprn_periods fadp,
fa_mc_adjustments faadj
WHERE
fadd.asset_id = h_asset_id
AND fadd.distribution_id = fadh.distribution_id
AND fadd.book_type_code = h_book
AND fadd.deprn_source_code = 'D'
AND fadd.period_counter = fadp.period_counter
AND fadd.set_of_books_id = ret.set_of_books_id
AND fadp.period_num = h_i
AND fadp.book_type_code = h_book
AND fadp.fiscal_year = h_current_fiscal_yr
AND faadj.distribution_id(+) = fadd.distribution_id
AND faadj.book_type_code(+) = fadd.book_type_code
AND faadj.asset_id(+) = fadd.asset_id
AND faadj.period_counter_created(+) = fadd.period_counter
AND faadj.adjustment_type(+) = h_adj_type
AND faadj.set_of_books_id(+) = ret.set_of_books_id
GROUP BY
fadd.distribution_id,
fadh.code_combination_id,
fadd.deprn_amount,
fadd.deprn_adjustment_amount,
fadd.bonus_deprn_amount,
fadd.bonus_deprn_adjustment_amount,
fadd.impairment_amount,
fadd.reval_deprn_expense,
fadd.reval_amortization,
faadj.distribution_id,
faadj.source_type_Code,
faadj.adjustment_amount,
fadp.period_counter,
faadj.transaction_header_id
UNION
SELECT fadh.distribution_id,
fadh.code_combination_id,
0,
nvl(SUM(-1 * h_cost_frac *
decode (faadj.debit_credit_flag, 'DR', 1, -1) *
faadj.adjustment_amount), 0),
nvl(SUM(-1 * h_cost_frac *
decode (faadj.debit_credit_flag, 'DR', 1, -1) *
faadj.annualized_adjustment), 0),
0,
0,
nvl(faadj.source_type_code, 'DEPRECIATION'),
fadp.period_counter,
faadj.transaction_header_id
FROM
fa_distribution_history fadh,
fa_deprn_periods fadp,
fa_mc_adjustments faadj
WHERE
fadp.period_num = h_i
AND fadp.book_type_code = h_book
AND fadp.fiscal_year = h_current_fiscal_yr
AND fadp.period_counter = h_cpd_ctr
AND faadj.distribution_id = fadh.distribution_id
AND faadj.book_type_code = fadp.book_type_code
AND faadj.asset_id = h_asset_id
AND faadj.period_counter_created = fadp.period_counter
AND faadj.adjustment_type = h_adj_type
AND faadj.set_of_books_id = ret.set_of_books_id
GROUP BY
fadh.distribution_id,
fadh.code_combination_id,
faadj.distribution_id,
faadj.source_type_Code,
faadj.adjustment_amount,
fadp.period_counter,
faadj.transaction_header_id;
SELECT ytd_deprn, period_num, bonus_ytd_deprn, fiscal_year,
ytd_impairment, deprn_reserve
INTO h_ytd_deprn, h_pd_num, h_bonus_ytd_deprn, h_fiscal_year,
h_ytd_impairment, h_Brow_deprn_reserve
FROM
fa_deprn_summary ds,
fa_deprn_periods dp
WHERE
ds.asset_id = h_asset_id
AND ds.book_type_code = h_book
AND ds.deprn_source_code = 'BOOKS'
AND dp.book_type_code = h_book
AND dp.period_counter = ds.period_counter;
SELECT ytd_deprn,period_num, bonus_ytd_deprn, fiscal_year,
ytd_impairment, deprn_reserve
INTO h_ytd_deprn, h_pd_num, h_bonus_ytd_deprn, h_fiscal_year,
h_ytd_impairment, h_Brow_deprn_reserve
FROM
fa_mc_deprn_summary ds,
fa_deprn_periods dp
WHERE
ds.asset_id = h_asset_id
AND ds.book_type_code = h_book
AND ds.deprn_source_code = 'BOOKS'
AND ds.set_of_books_id = ret.set_of_books_id
AND dp.book_type_code = h_book
AND dp.period_counter = ds.period_counter;
SELECT count(*)
INTO h_ret_count
FROM
fa_deprn_periods fadp,
fa_adjustments faadj
WHERE
fadp.period_num = h_i
AND fadp.book_type_code = h_book
AND fadp.fiscal_year = h_current_fiscal_yr
AND faadj.book_type_code = h_book
AND faadj.asset_id = h_asset_id
AND faadj.period_counter_created = fadp.period_counter
AND faadj.adjustment_type = h_adj_type
AND faadj.source_type_code = 'RETIREMENT'
AND faadj.adjustment_amount <> 0;
SELECT count(*)
INTO h_adj_count
FROM
fa_deprn_periods fadp,
fa_adjustments faadj
WHERE
fadp.period_num = h_i
AND fadp.book_type_code = h_book
AND fadp.fiscal_year = h_current_fiscal_yr
AND faadj.book_type_code = h_book
AND faadj.asset_id = h_asset_id
AND faadj.period_counter_created = fadp.period_counter
AND faadj.adjustment_type = h_adj_type
AND faadj.source_type_code <> 'RETIREMENT'
AND faadj.adjustment_amount <> 0;
SELECT count(*)
INTO h_ret_count
FROM
fa_deprn_periods fadp,
fa_mc_adjustments faadj
WHERE
fadp.period_num = h_i
AND fadp.book_type_code = h_book
AND fadp.fiscal_year = h_current_fiscal_yr
AND faadj.book_type_code = h_book
AND faadj.asset_id = h_asset_id
AND faadj.period_counter_created = fadp.period_counter
AND faadj.adjustment_type = h_adj_type
AND faadj.set_of_books_id = ret.set_of_books_id
AND faadj.source_type_code = 'RETIREMENT'
AND faadj.adjustment_amount <> 0;
SELECT count(*)
INTO h_adj_count
FROM
fa_deprn_periods fadp,
fa_mc_adjustments faadj
WHERE
fadp.period_num = h_i
AND fadp.book_type_code = h_book
AND fadp.fiscal_year = h_current_fiscal_yr
AND faadj.book_type_code = h_book
AND faadj.asset_id = h_asset_id
AND faadj.period_counter_created = fadp.period_counter
AND faadj.adjustment_type = h_adj_type
AND faadj.set_of_books_id = ret.set_of_books_id
AND faadj.source_type_code <> 'RETIREMENT'
AND faadj.adjustment_amount <> 0;
SELECT count(*)
INTO h_prior_pd_tfr
FROM fa_deprn_periods dp1,
fa_deprn_periods dp2,
fa_transaction_headers th
WHERE th.asset_id = h_asset_id
AND th.book_type_code = h_book
AND th.transaction_type_code = 'TRANSFER'
AND th.date_effective between dp1.period_open_date
and nvl(dp1.period_close_date, sysdate)
AND dp1.book_type_code = th.book_type_code
AND dp1.period_num = h_i
AND dp1.fiscal_year = h_current_fiscal_yr
AND th.transaction_date_entered between
dp2.calendar_period_open_date and
dp2.calendar_period_close_date
AND dp2.book_type_code = th.book_type_code
AND dp2.period_num < h_i;
select fcp2.period_num + 1 - fcp1.period_num, fcp3.end_date + 1 - fcp3.start_date
into h_ret_pd_nums, h_tot_days_in_mon
from fa_calendar_periods fcp1,
fa_calendar_periods fcp2,
fa_calendar_periods fcp3,
fa_fiscal_year fy
where fcp1.calendar_type = p_cal
and bk.ret_prorate_date between fcp1.start_date and fcp1.end_date
and fcp2.calendar_type = fcp1.calendar_type
and fcp3.calendar_type = d_cal
and fcp3.period_num = h_i
and fcp3.end_date between fcp2.start_date and fcp2.end_date
and fy.fiscal_year_name = h_fiscal_year_name
and bk.ret_prorate_date between fy.start_date and fy.end_date
and fcp3.start_date between fy.start_date and fy.end_date;
select cp.end_date + 1 - cp.start_date
into h_tot_days_in_mon
from fa_calendar_periods cp,
fa_fiscal_year fy
where cp.calendar_type = d_cal
and cp.period_num = h_i
and fy.fiscal_year_name = h_fiscal_year_name
and bk.ret_prorate_date between fy.start_date and fy.end_date
and cp.start_date between fy.start_date and fy.end_date;
select 'Y'
into l_same_year
from fa_transaction_headers th,
fa_fiscal_year fy,
fa_deprn_periods dp
where th.asset_id = h_asset_id
and th.book_type_code = h_book
and dp.book_type_code = th.book_type_code
and th.transaction_type_code = 'ADDITION'
and fy.fiscal_year_name = h_fiscal_year_name
and th.date_effective between dp.period_open_date and nvl(dp.period_close_date,sysdate)
and dp.fiscal_year = fy.fiscal_year
and th.transaction_date_entered between fy.start_date and fy.end_date;
select to_char(trunc(dp.calendar_period_open_date),'J')-
to_char(trunc(th.transaction_date_entered),'J')
into l_tot_days
from fa_transaction_headers th,
fa_deprn_periods dp
where th.asset_id = h_asset_id
and th.book_type_code = h_book
and th.transaction_type_code = 'ADDITION'
and dp.book_type_code = th.book_type_code
and th.date_effective between dp.period_open_date and nvl(dp.period_close_date,sysdate)
and to_char(th.transaction_date_entered,'RRRR') = dp.fiscal_year;
select 'Y'
into l_same_deprn_start_yr
from fa_transaction_headers th,
fa_fiscal_year fy,
fa_deprn_periods dp
where th.asset_id = h_asset_id
and th.book_type_code = h_book
and dp.book_type_code = th.book_type_code
and th.transaction_type_code = 'ADDITION'
and fy.fiscal_year_name = h_fiscal_year_name
and th.date_effective between dp.period_open_date and nvl(dp.period_close_date,sysdate)
and dp.fiscal_year = fy.fiscal_year
and bk.deprn_start_date between fy.start_date and fy.end_date;
select fcp2.period_num + 1 - fcp1.period_num, fcp3.end_date + 1 - fcp3.start_date
into h_ret_pd_nums, h_tot_days_in_mon
from fa_calendar_periods fcp1,
fa_calendar_periods fcp2,
fa_calendar_periods fcp3,
fa_fiscal_year ffy1,
fa_fiscal_year ffy3
where fcp1.calendar_type = p_cal
and bk.ret_prorate_date between fcp1.start_date and fcp1.end_date
and fcp2.calendar_type = fcp1.calendar_type
and fcp3.calendar_type = d_cal
and fcp3.period_num = h_i
and fcp3.end_date between fcp2.start_date and fcp2.end_date
and fcp3.start_date between ffy3.start_date and ffy3.end_date
and bk.ret_prorate_date between ffy1.start_date and ffy1.end_date
and ffy1.fiscal_year_name = h_fiscal_year_name
and ffy3.fiscal_year_name = h_fiscal_year_name
and ffy1.fiscal_year = ffy3.fiscal_year;
select cp.end_date + 1 - cp.start_date
into h_tot_days_in_mon
from fa_calendar_periods cp,
fa_fiscal_year fy
where cp.calendar_type = d_cal
and cp.period_num = h_i
and fy.fiscal_year_name = h_fiscal_year_name
and bk.ret_prorate_date between fy.start_date and fy.end_date
and cp.start_date between fy.start_date and fy.end_date;
select 'Y'
into l_same_year
from fa_transaction_headers th,
fa_deprn_periods dp,
fa_fiscal_year ffy
where th.asset_id = h_asset_id
and th.book_type_code = h_book
and th.transaction_type_code = 'ADDITION'
and dp.book_type_code = th.book_type_code
and th.date_effective between dp.period_open_date and dp.period_close_date
and th.transaction_date_entered between ffy.start_date and ffy.end_date
and ffy.fiscal_year_name = h_fiscal_year_name
and ffy.fiscal_year = dp.fiscal_year;
select to_char(trunc(dp.calendar_period_open_date),'J')-
to_char(trunc(th.transaction_date_entered),'J')
into l_tot_days
from fa_transaction_headers th,
fa_deprn_periods dp,
fa_fiscal_year ffy
where th.asset_id = h_asset_id
and th.book_type_code = h_book
and th.transaction_type_code = 'ADDITION'
and dp.book_type_code = th.book_type_code
and th.date_effective between dp.period_open_date and nvl(dp.period_close_date,sysdate)
and th.transaction_date_entered between ffy.start_date and ffy.end_date
and ffy.fiscal_year_name = h_fiscal_year_name
and ffy.fiscal_year = dp.fiscal_year;
select ( cp1.period_num) ,( cp2.period_num)
into h_new_ret_pn,h_old_ret_pn
from fa_retirements new_ret
,fa_conventions conv
,fa_calendar_periods cp1
,fa_retirements old_ret
,fa_conventions conv2
,fa_calendar_periods cp2
where new_ret.transaction_header_id_in=ret.th_id_in
and conv.prorate_convention_code=new_ret.retirement_prorate_convention
and new_ret.date_retired between conv.start_date and conv.end_date
and cp1.calendar_type = d_cal
and conv.prorate_date between cp1.start_Date and cp1.end_date
and old_ret.transaction_header_id_in= h_transaction_header_id
and conv2.prorate_convention_code=old_ret.retirement_prorate_convention
and old_ret.date_retired between conv2.start_date and conv2.end_date
and cp2.calendar_type = d_cal
and conv2.prorate_date between cp2.start_Date and cp2.end_date;
select count(*)
into h_annual_adj_check
from fa_transaction_headers th
where th.transaction_header_id = h_transaction_header_id
and th.transaction_key is null
and nvl(th.transaction_subtype,'XX') = 'EXPENSED';
select h_pc - (fy1.fiscal_year * pds_per_year + cp1.period_num)
into l_num_d_periods
from fa_calendar_periods cp1
, fa_fiscal_year fy1
where cp1.calendar_type = d_cal
and bk.prorate_date between cp1.start_Date and cp1.end_date
and fy1.fiscal_year_name = h_fiscal_year_name
and bk.prorate_date between fy1.start_Date and fy1.end_date;
SELECT count(*)
INTO h_curr_pd_add
FROM
fa_deprn_periods dp,
fa_transaction_headers th
WHERE th.asset_id = h_asset_id
AND th.book_type_code = h_book
AND th.transaction_type_code || '' = 'ADDITION'
AND th.date_effective between dp.period_open_date
and nvl(dp.period_close_date, sysdate)
AND dp.book_type_code = th.book_type_code
AND dp.period_num = h_i
AND dp.fiscal_year = h_current_fiscal_yr;
SELECT count(*)
INTO h_curr_pd_reinst
FROM
fa_deprn_periods dp,
fa_transaction_headers th
WHERE th.asset_id = h_asset_id
AND th.book_type_code = h_book
AND th.transaction_type_code || '' = 'REINSTATEMENT'
AND th.date_effective between dp.period_open_date
and nvl(dp.period_close_date, sysdate)
AND dp.book_type_code = th.book_type_code
AND dp.period_num = h_i
AND dp.fiscal_year = h_current_fiscal_yr;
SELECT sum(nvl(adjustment_amount,0))--bug fix 3905436
INTO h_adj_exp_row
FROM
fa_adjustments adj,
fa_deprn_periods dp,
fa_transaction_headers th
WHERE
th.asset_id = h_asset_id
AND th.book_type_code = h_book
AND th.transaction_type_code || '' = 'ADDITION'
AND adj.source_type_code || '' = 'DEPRECIATION'
AND adj.ADJUSTMENT_type || '' = 'EXPENSE'
AND adj.asset_id = h_asset_id
AND adj.book_type_code = h_book
AND adj.distribution_id = h_dist_id --bug fix 3905436
AND adj.period_counter_created = dp.period_counter
AND th.date_effective between dp.period_open_date
and nvl(dp.period_close_date, sysdate)
AND dp.book_type_code = th.book_type_code
AND dp.period_num = h_i
AND dp.fiscal_year = h_current_fiscal_yr;
SELECT sum(nvl(adjustment_amount,0))--bug fix 3905436
INTO h_adj_exp_row
FROM
fa_mc_adjustments adj,
fa_deprn_periods dp,
fa_transaction_headers th
WHERE
th.asset_id = h_asset_id
AND th.book_type_code = h_book
AND th.transaction_type_code || '' = 'ADDITION'
AND adj.source_type_code || '' = 'DEPRECIATION'
AND adj.ADJUSTMENT_type || '' = 'EXPENSE'
AND adj.asset_id = h_asset_id
AND adj.book_type_code = h_book
AND adj.set_of_books_id = ret.set_of_books_id
AND adj.distribution_id = h_dist_id --bug fix 3905436
AND adj.period_counter_created = dp.period_counter
AND th.date_effective between dp.period_open_date
and nvl(dp.period_close_date, sysdate)
AND dp.book_type_code = th.book_type_code
AND dp.period_num = h_i
AND dp.fiscal_year = h_current_fiscal_yr;
SELECT PRIOR_FY_EXPENSE
INTO h_prior_fy_exp
FROM
fa_deprn_summary ds,
fa_deprn_periods dp,
fa_transaction_headers th
WHERE
th.asset_id = h_asset_id
AND th.book_type_code = h_book
AND th.transaction_type_code || '' = 'ADDITION'
AND th.date_effective between dp.period_open_date
and nvl(dp.period_close_date, sysdate)
AND ds.asset_id = h_asset_id
AND ds.book_type_code = h_book
AND ds.period_counter = dp.period_counter
AND dp.book_type_code = th.book_type_code
AND dp.period_num = h_i
AND dp.fiscal_year = h_current_fiscal_yr;
SELECT PRIOR_FY_EXPENSE
INTO h_prior_fy_exp
FROM
fa_mc_deprn_summary ds,
fa_deprn_periods dp,
fa_transaction_headers th
WHERE
th.asset_id = h_asset_id
AND th.book_type_code = h_book
AND th.transaction_type_code || '' = 'ADDITION'
AND th.date_effective between dp.period_open_date
and nvl(dp.period_close_date, sysdate)
AND ds.asset_id = h_asset_id
AND ds.book_type_code = h_book
AND ds.period_counter = dp.period_counter
AND ds.set_of_books_id = ret.set_of_books_id
AND dp.book_type_code = th.book_type_code
AND dp.period_num = h_i
AND dp.fiscal_year = h_current_fiscal_yr;
SELECT sum(adjustment_amount), max(th.transaction_header_id)
INTO h_adj_exp_row, h_old_reinst_trx_id
FROM
fa_adjustments adj,
fa_deprn_periods dp,
fa_transaction_headers th
WHERE
th.asset_id = h_asset_id
AND th.book_type_code = h_book
AND th.transaction_type_code || '' = 'REINSTATEMENT'
AND adj.source_type_code || '' = 'RETIREMENT'
AND adj.ADJUSTMENT_type || '' = 'EXPENSE'
AND adj.asset_id = h_asset_id
AND adj.book_type_code = h_book
AND adj.distribution_id = h_dist_id --bug fix 3905436
AND adj.period_counter_created = dp.period_counter
AND th.date_effective between dp.period_open_date
and nvl(dp.period_close_date, sysdate)
AND dp.book_type_code = th.book_type_code
AND dp.period_num = h_i
AND dp.fiscal_year = h_current_fiscal_yr;
SELECT sum(adjustment_amount), max(th.transaction_header_id)
INTO h_adj_exp_row, h_old_reinst_trx_id
FROM
fa_mc_adjustments adj,
fa_deprn_periods dp,
fa_transaction_headers th
WHERE
th.asset_id = h_asset_id
AND th.book_type_code = h_book
AND th.transaction_type_code || '' = 'REINSTATEMENT'
AND adj.source_type_code || '' = 'RETIREMENT'
AND adj.ADJUSTMENT_type || '' = 'EXPENSE'
AND adj.asset_id = h_asset_id
AND adj.book_type_code = h_book
AND adj.distribution_id = h_dist_id --bug fix 3905436
AND adj.period_counter_created = dp.period_counter
AND adj.set_of_books_id = ret.set_of_books_id
AND th.date_effective between dp.period_open_date
and nvl(dp.period_close_date, sysdate)
AND dp.book_type_code = th.book_type_code
AND dp.period_num = h_i
AND dp.fiscal_year = h_current_fiscal_yr;
select dp1.period_counter
,dp2.period_counter
into h_old_reinst_pc
,h_old_ret_pc
from fa_transaction_headers trx,
fa_deprn_periods dp1,
fa_retirements old_ret,
fa_deprn_periods dp2,
fa_conventions conv
where trx.transaction_header_id = h_old_reinst_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 old_ret.transaction_header_id_out = trx.transaction_header_id
and dp2.book_type_code = trx.book_type_code
and conv.prorate_convention_code=old_ret.retirement_prorate_convention
and old_ret.date_retired between conv.start_date and conv.end_date
and conv.prorate_date between dp2.CALENDAR_PERIOD_OPEN_DATE
and dp2.CALENDAR_PERIOD_CLOSE_DATE;
select dp.period_counter
into h_ret_prorate_pc
from fa_retirements new_ret
,fa_conventions conv
,fa_deprn_periods dp
where new_ret.transaction_header_id_in=ret.th_id_in
and conv.prorate_convention_code=new_ret.retirement_prorate_convention
and new_ret.date_retired between conv.start_date and conv.end_date
and dp.book_type_code = new_ret.book_type_code
and conv.prorate_date between dp.CALENDAR_PERIOD_OPEN_DATE
and dp.CALENDAR_PERIOD_CLOSE_DATE
;
select dp1.period_counter
,dp2.period_counter
into h_old_reinst_pc
,h_old_ret_pc
from fa_transaction_headers trx,
fa_deprn_periods dp1,
fa_mc_retirements old_ret,
fa_deprn_periods dp2,
fa_conventions conv
where trx.transaction_header_id = h_old_reinst_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 old_ret.transaction_header_id_out = trx.transaction_header_id
and old_ret.set_of_books_id = ret.set_of_books_id
and dp2.book_type_code = trx.book_type_code
and conv.prorate_convention_code=old_ret.retirement_prorate_convention
and old_ret.date_retired between conv.start_date and conv.end_date
and conv.prorate_date between dp2.CALENDAR_PERIOD_OPEN_DATE
and dp2.CALENDAR_PERIOD_CLOSE_DATE;
select dp.period_counter
into h_ret_prorate_pc
from fa_mc_retirements new_ret
,fa_conventions conv
,fa_deprn_periods dp
where new_ret.transaction_header_id_in=ret.th_id_in
and conv.prorate_convention_code=new_ret.retirement_prorate_convention
and new_ret.date_retired between conv.start_date and conv.end_date
and new_ret.set_of_books_id = ret.set_of_books_id
and dp.book_type_code = new_ret.book_type_code
and conv.prorate_date between dp.CALENDAR_PERIOD_OPEN_DATE
and dp.CALENDAR_PERIOD_CLOSE_DATE
;
select nvl(cb.bonus_deprn_expense_acct,'0')
into h_bonus_deprn_exp_acct
from fa_additions_b ad,
fa_category_books cb
where ad.asset_id = h_asset_id
and cb.category_id = ad.asset_category_id
and cb.book_type_code = h_book;
select nvl(cb.impair_expense_acct,'0')
into h_impair_exp_acct
from fa_additions_b ad,
fa_category_books cb
where ad.asset_id = h_asset_id
and cb.category_id = ad.asset_category_id
and cb.book_type_code = h_book;
select facb.deprn_expense_acct
into h_deprn_exp_acct
from fa_additions_b faadd,
fa_category_books facb,
fa_book_controls bc
where faadd.asset_id = h_asset_id
and facb.category_id = faadd.asset_category_id
and facb.book_type_code = h_book
and bc.book_type_code = facb.book_type_code;
adj_row.last_update_date := today;
adj_row.selection_thid := 0;
SELECT pcal.period_num
INTO h_start_pd_endpp
FROM fa_calendar_periods pcal,
fa_deprn_periods dp
WHERE calendar_type = p_cal
AND dp.book_type_code = h_book
AND dp.fiscal_year = h_current_fiscal_yr
AND dp.period_num = h_start_pdnum
AND dp.calendar_period_close_date
between start_date and end_date;
SELECT decode(fy1.fiscal_year, fy2.fiscal_year,1,0),
decode(BC.DEPR_FIRST_YEAR_RET_FLAG, 'YES', 1, 0),
decode(ctype.depr_when_acquired_flag,'YES',1,0),
decode(mt.rate_source_rule,
'CALCULATED', 1,
'TABLE', 2,
'FLAT', 3)
INTO h_same_fy, h_depr_first_year_ret,
h_dwacq, h_rate_source_rule
FROM FA_FISCAL_YEAR fy1,
FA_FISCAL_YEAR fy2,
FA_BOOKS bk,
FA_RETIREMENTS rt,
FA_CONVENTION_TYPES ctype,
FA_METHODS mt,
FA_BOOK_CONTROLS bc
WHERE rt.date_retired between
fy1.start_date and fy1.end_date
AND bk.deprn_start_date between
fy2.start_date and fy2.end_date
AND rt.asset_id = h_asset_id
AND bk.asset_id = h_asset_id
AND bk.book_type_code = bc.book_type_code
AND bc.book_type_code = h_book
AND rt.retirement_id = h_retirement_id
AND bk.retirement_id = rt.retirement_id
AND bk.transaction_header_id_out is not null
AND bk.deprn_method_code = mt.method_code
AND nvl(bk.life_in_months,1) = nvl(mt.life_in_months,1)
AND bk.prorate_convention_code = ctype.prorate_convention_code
AND fy1.fiscal_year_name = bc.fiscal_year_name
AND fy2.fiscal_year_name = bc.fiscal_year_name;
select decode(fy1.fiscal_year, fy2.fiscal_year,1,0),
decode(BC.DEPR_FIRST_YEAR_RET_FLAG, 'YES', 1, 0),
decode(ctype.depr_when_acquired_flag,'YES',1,0),
decode(mt.rate_source_rule,
'CALCULATED', 1,
'TABLE', 2,
'FLAT', 3)
INTO h_same_fy, h_depr_first_year_ret,
h_dwacq, h_rate_source_rule
FROM FA_FISCAL_YEAR fy1,
FA_FISCAL_YEAR fy2,
FA_MC_BOOKS bk,
FA_MC_RETIREMENTS rt,
FA_CONVENTION_TYPES ctype,
FA_METHODS mt,
FA_BOOK_CONTROLS bc
WHERE rt.date_retired between
fy1.start_date and fy1.end_date
AND bk.deprn_start_date between
fy2.start_date and fy2.end_date
AND bk.set_of_books_id = ret.set_of_books_id
AND rt.set_of_books_id = ret.set_of_books_id
AND rt.asset_id = h_asset_id
AND bk.asset_id = h_asset_id
AND bk.book_type_code = bc.book_type_code
AND bc.book_type_code = h_book
AND rt.retirement_id = h_retirement_id
AND bk.retirement_id = rt.retirement_id
AND bk.transaction_header_id_out is not null
AND bk.deprn_method_code = mt.method_code
AND nvl(bk.life_in_months,1) = nvl(mt.life_in_months,1)
AND bk.prorate_convention_code = ctype.prorate_convention_code
AND fy1.fiscal_year_name = bc.fiscal_year_name
AND fy2.fiscal_year_name = bc.fiscal_year_name;
select period_counter + 1
into h_asset_addition_pc
from fa_deprn_summary
where asset_id = h_asset_id
and book_type_code = h_book
and deprn_source_code = 'BOOKS';
select decode(dp1.fiscal_year,dp2.fiscal_year,1,0)
into l_same_fy
from fa_deprn_periods dp1,
fa_deprn_periods dp2
where dp1.book_type_code = h_book
and dp2.book_type_code = dp1.book_type_code
and dp1.period_counter = h_asset_addition_pc
and dp2.period_counter = h_start_pd_pc;
Select count(*)
into l_adj_overlap
from dual
where EXISTS (select 'Ret Overlapping Adj'
from fa_transaction_headers th,
fa_deprn_periods dp
where dp.book_type_code = h_book
and th.book_type_code = h_book
and th.asset_id = h_asset_id
and dp.period_counter = h_start_pd_pc - 1
and th.transaction_type_code = 'ADJUSTMENT'
and th.transaction_subtype = 'AMORTIZED'
and th.date_effective > dp.period_close_date);
select period_counter + 1
into h_asset_addition_pc
from fa_mc_deprn_summary
where asset_id = h_asset_id
and book_type_code = h_book
and set_of_books_id = ret.set_of_books_id
and deprn_source_code = 'BOOKS';
select decode(dp1.fiscal_year,dp2.fiscal_year,1,0)
into l_same_fy
from fa_mc_deprn_periods dp1,
fa_mc_deprn_periods dp2
where dp1.book_type_code = h_book
and dp1.set_of_books_id = ret.set_of_books_id
and dp2.set_of_books_id = dp1.set_of_books_id
and dp2.book_type_code = dp1.book_type_code
and dp1.period_counter = h_asset_addition_pc
and dp2.period_counter = h_start_pd_pc;
Select count(*)
into l_adj_overlap
from dual
where EXISTS ( Select 'Ret Overlapping Adj'
from fa_transaction_headers th,
fa_mc_deprn_periods dp
where dp.book_type_code = h_book
and dp.set_of_books_id = ret.set_of_books_id
and th.book_type_code = h_book
and th.asset_id = h_asset_id
and dp.period_counter = h_start_pd_pc - 1
and th.transaction_type_code = 'ADJUSTMENT'
and th.transaction_subtype = 'AMORTIZED'
and th.date_effective > dp.period_close_date);
select pcal.period_num
into h_fully_rsv_pd
from fa_calendar_periods pcal,
fa_deprn_periods dp
where calendar_type = p_cal
and dp.book_type_code = h_book
and dp.fiscal_year = h_current_fiscal_yr
and dp.period_counter = bk.pc_fully_reserved
and dp.calendar_period_close_date
between start_date and end_date;
select count(*)
into h_daily_annual_adj_check
from fa_transaction_headers th,
fa_deprn_periods dp
where dp.book_type_code = h_book
and dp.period_counter = h_start_pd_pc - 1
and th.book_type_code = h_book
and th.asset_id = h_asset_id
and th.transaction_key is null
and nvl(th.transaction_subtype,'XX') = 'EXPENSED'
and th.date_effective > dp.period_close_date;
select count(*)
into h_daily_annual_adj_check
from fa_transaction_headers th,
fa_mc_deprn_periods dp
where dp.book_type_code = h_book
and dp.period_counter = h_start_pd_pc - 1
and dp.set_of_books_id = ret.set_of_books_id
and th.book_type_code = h_book
and th.asset_id = h_asset_id
and th.transaction_key is null
and nvl(th.transaction_subtype,'XX') = 'EXPENSED'
and th.date_effective > dp.period_close_date;
select 1
into l_poa_ret
from fa_deprn_summary ds,
fa_deprn_periods dp
where ds.book_type_code = h_book
and ds.asset_id = h_asset_id
and ds.deprn_source_code = 'BOOKS'
and dp.book_type_code = h_book
and dp.fiscal_year = h_current_fiscal_yr
and dp.period_counter = ds.period_counter + 1
and bk.ret_prorate_date between dp.calendar_period_open_date and dp.calendar_period_close_date;
select 1
into l_poa_ret
from fa_mc_deprn_summary ds,
fa_mc_deprn_periods dp
where ds.book_type_code = h_book
and ds.set_of_books_id = ret.set_of_books_id
and ds.asset_id = h_asset_id
and ds.deprn_source_code = 'BOOKS'
and dp.book_type_code = h_book
and dp.set_of_books_id = ret.set_of_books_id
and dp.fiscal_year = h_current_fiscal_yr
and dp.period_counter = ds.period_counter + 1
and bk.ret_prorate_date between dp.calendar_period_open_date and dp.calendar_period_close_date;
select period_counter + 1
into h_asset_addition_pc
from fa_deprn_summary
where asset_id = h_asset_id
and book_type_code = h_book
and deprn_source_code = 'BOOKS';
select period_counter + 1
into h_asset_addition_pc
from fa_mc_deprn_summary
where asset_id = h_asset_id
and book_type_code = h_book
and set_of_books_id = ret.set_of_books_id
and deprn_source_code = 'BOOKS';
adj_row.selection_retid := 0;
adj_row.selection_mode := FA_STD_TYPES.FA_AJ_ACTIVE;
X_last_update_date,
X_last_updated_by,
X_last_update_login,
p_log_level_rec => p_log_level_rec)) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
adj_row.selection_retid := retirement_id;
adj_row.selection_mode := FA_STD_TYPES.FA_AJ_RETIRE;
X_last_update_date,
X_last_updated_by,
X_last_update_login
, p_log_level_rec => p_log_level_rec)) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
p_last_update_date => X_last_update_date,
p_last_updated_by => X_last_updated_by,
p_last_update_login => X_last_update_login,
p_who_mode => 'UPDATE',
p_log_level_rec => p_log_level_rec) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
| allocated to each cost center (distribution). It inserts the amount into |
| the FA_ADJUSTMENTS table. |
| If the number of periods to be catchup is negative or the |
| DEPRECIATE_LAST_YEAR_FLAG is set to 'NO', we need to back out depreciation|
| When the flag is set to NO, we need to back out the whole depreciation |
| taken so far this year. |
| |
| |
| HISTORY 1/12/89 R Rumanang Created |
| 6/23/89 R Rumanang Standarized |
| 8/24/89 R Rumanang Insert to FA_ADJUSTMENTS |
| 04/15/91 M Chan Rewritten for MPL 9 |
| 01/02/96 S Behura Rewritten into PL/SQL |
*===========================================================================*/
FUNCTION fagpdp(ret in out nocopy fa_ret_types.ret_struct,
bk in out nocopy fa_ret_types.book_struct,
dpr in out nocopy FA_STD_TYPES.dpr_struct,
today in date, pds_catchup number,
cpd_ctr number, cpdnum number,
cost_frac in number, deprn_amt in out nocopy number,
bonus_deprn_amt in out nocopy number,
impairment_amt in out nocopy number,
impairment_reserve in out nocopy number,
reval_deprn_amt in out nocopy number, reval_amort in out number,
reval_reserve in out nocopy number, user_id number,
p_log_level_rec in FA_API_TYPES.log_level_rec_type) Return BOOLEAN IS
fagpdp_err exception;
X_LAST_UPDATE_DATE date := sysdate;
X_last_updated_by number := -1;
X_last_update_login number := -1;
select fy.fiscal_year
into l_first_fiscal_year
from fa_fiscal_year fy,
fa_book_controls bc,
fa_books bks
where bc.book_type_code = ret.book
and bc.fiscal_year_name = fy.fiscal_year_name
and bks.book_type_code = ret.book
and bks.asset_id = ret.asset_id
and bks.transaction_header_id_out is null
and least(bks.date_placed_in_service,bks.prorate_date) between
fy.start_date and fy.end_date;
SELECT cp.period_num
INTO h_work_pdnum
FROM fa_calendar_periods cp
WHERE h_ret_p_date
between cp.start_date and cp.end_date
AND cp.calendar_type = h_d_cal;
end; -- end of - select
SELECT to_number (to_char (dp.calendar_period_open_date, 'J'))
INTO :h_cpp_jstartdate
FROM fa_deprn_periods dp
WHERE dp.book_type_code = :h_book
AND dp.fiscal_year = :h_current_fiscal_yr
AND dp.period_num = :h_work_pdnum;
SELECT to_number (to_char (dcp.start_date, 'J'))
INTO h_cpp_jstartdate
FROM fa_calendar_periods dcp,
fa_fiscal_year fy,
fa_book_controls bc
WHERE bc.book_type_code = h_book
AND bc.fiscal_year_name = fy.fiscal_year_name
AND dcp.calendar_type = h_d_cal
AND fy.fiscal_year = h_current_fiscal_yr
AND dcp.period_num = h_work_pdnum
AND dcp.start_date
between fy.start_date and fy.end_date;
SELECT to_number (to_char (bk.prorate_date, 'J'))
INTO h_dpis_pr_jdt
FROM fa_books bk,
fa_calendar_periods dcp
WHERE bk.book_type_code = ret.book
AND transaction_header_id_out is null
AND asset_id = ret.asset_id
AND dcp.calendar_type = h_d_cal
AND bk.prorate_date
BETWEEN dcp.start_date AND dcp.end_date
AND h_ret_p_date
BETWEEN dcp.start_date AND dcp.end_date;
SELECT period_num
INTO h_startpp
FROM fa_calendar_periods
WHERE calendar_type = h_p_cal
AND to_date (h_cpp_jstartdate,'J')
between start_date and end_date;
SELECT to_number (to_char (dp.calendar_period_open_date, 'J'))
INTO h_cpp_jenddate
FROM fa_deprn_periods dp
WHERE dp.book_type_code = h_book
AND dp.fiscal_year = h_current_fiscal_yr
AND dp.period_num = h_stop_pdnum;
SELECT period_num
INTO h_endpp
FROM fa_calendar_periods
WHERE calendar_type = h_p_cal
AND to_date (h_cpp_jenddate,'J')
between start_date and end_date;
select facb.deprn_expense_acct
into h_deprn_exp_acct
from fa_additions_b faadd,
fa_category_books facb,
fa_book_controls bc
where faadd.asset_id = h_asset_id
and facb.category_id = faadd.asset_category_id
and facb.book_type_code = h_book
and bc.book_type_code = facb.book_type_code;
adj_row.last_update_date := today;
adj_row.selection_thid := 0;
element => 'Insert cost into fa_adj',
value => '', p_log_level_rec => p_log_level_rec);
adj_row.selection_mode := FA_STD_TYPES.FA_AJ_ACTIVE;
adj_row.selection_retid := 0;
X_last_update_date,
X_last_updated_by,
X_last_update_login,
p_log_level_rec => p_log_level_rec)) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
p_last_update_date => X_last_update_date,
p_last_updated_by => X_last_updated_by,
p_last_update_login => X_last_update_login,
p_who_mode => 'UPDATE',
p_log_level_rec => p_log_level_rec) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
select nvl(cb.bonus_deprn_expense_acct,'0')
into h_bonus_deprn_exp_acct
from fa_additions_b ad,
fa_category_books cb
where ad.asset_id = h_asset_id
and cb.category_id = ad.asset_category_id
and cb.book_type_code = h_book;
adj_row.selection_mode := FA_STD_TYPES.FA_AJ_ACTIVE;
adj_row.selection_retid := 0;
X_last_update_date,
X_last_updated_by,
X_last_update_login
, p_log_level_rec => p_log_level_rec)) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
select nvl(cb.impair_expense_acct,'0')
into h_impair_exp_acct
from fa_additions_b ad,
fa_category_books cb
where ad.asset_id = h_asset_id
and cb.category_id = ad.asset_category_id
and cb.book_type_code = h_book;
adj_row.selection_mode := FA_STD_TYPES.FA_AJ_ACTIVE;
adj_row.selection_retid := 0;
X_last_update_date,
X_last_updated_by,
X_last_update_login
, p_log_level_rec => p_log_level_rec)) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
X_last_update_date,
X_last_updated_by,
X_last_update_login,
p_log_level_rec => p_log_level_rec)) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
adj_row.selection_mode := FA_STD_TYPES.FA_AJ_RETIRE;
adj_row.selection_retid := ret.retirement_id;
X_last_update_date,
X_last_updated_by,
X_last_update_login,
p_log_level_rec => p_log_level_rec)) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
p_last_update_date => X_last_update_date,
p_last_updated_by => X_last_updated_by,
p_last_update_login => X_last_update_login,
p_who_mode => 'UPDATE',
p_log_level_rec => p_log_level_rec) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
select nvl(cb.bonus_deprn_expense_acct,'0')
into h_bonus_deprn_exp_acct
from fa_additions_b ad,
fa_category_books cb
where ad.asset_id = h_asset_id
and cb.category_id = ad.asset_category_id
and cb.book_type_code = h_book;
adj_row.selection_mode := FA_STD_TYPES.FA_AJ_ACTIVE;
adj_row.selection_retid := 0;
X_last_update_date,
X_last_updated_by,
X_last_update_login,
p_log_level_rec => p_log_level_rec)) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
select nvl(cb.impair_expense_acct,'0')
into h_impair_exp_acct
from fa_additions_b ad,
fa_category_books cb
where ad.asset_id = h_asset_id
and cb.category_id = ad.asset_category_id
and cb.book_type_code = h_book;
adj_row.selection_mode := FA_STD_TYPES.FA_AJ_ACTIVE;
adj_row.selection_retid := 0;
X_last_update_date,
X_last_updated_by,
X_last_update_login
, p_log_level_rec => p_log_level_rec)) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
X_last_update_date,
X_last_updated_by,
X_last_update_login,
p_log_level_rec => p_log_level_rec)) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
X_last_update_date,
X_last_updated_by,
X_last_update_login,
p_log_level_rec => p_log_level_rec)) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
SELECT SUM(faadj.adjustment_amount)
INTO h_deprn_amt
FROM FA_ADJUSTMENTS faadj
WHERE
faadj.transaction_header_id = h_th_id_in
AND faadj.source_type_code = 'RETIREMENT'
AND faadj.adjustment_type = 'EXPENSE'
AND faadj.book_type_Code = h_book
AND faadj.asset_id = h_asset_id
AND faadj.period_counter_created = h_cpd_ctr
GROUP BY faadj.transaction_header_id;
SELECT SUM(faadj.adjustment_amount)
INTO h_deprn_amt
FROM FA_MC_ADJUSTMENTS faadj
WHERE
faadj.transaction_header_id = h_th_id_in
AND faadj.source_type_code = 'RETIREMENT'
AND faadj.adjustment_type = 'EXPENSE'
AND faadj.book_type_Code = h_book
AND faadj.asset_id = h_asset_id
AND faadj.period_counter_created = h_cpd_ctr
AND faadj.set_of_books_id = ret.set_of_books_id
GROUP BY faadj.transaction_header_id;
SELECT SUM(faadj.adjustment_amount)
INTO h_bonus_deprn_amt
FROM FA_ADJUSTMENTS faadj
WHERE
faadj.transaction_header_id = h_th_id_in
AND faadj.source_type_code = 'RETIREMENT'
AND faadj.adjustment_type = 'BONUS EXPENSE'
AND faadj.book_type_Code = h_book
AND faadj.asset_id = h_asset_id
AND faadj.period_counter_created = h_cpd_ctr
GROUP BY faadj.transaction_header_id;
SELECT SUM(faadj.adjustment_amount)
INTO h_impairment_amt
FROM FA_ADJUSTMENTS faadj
WHERE
faadj.transaction_header_id = h_th_id_in
AND faadj.source_type_code = 'RETIREMENT'
AND faadj.adjustment_type = 'IMPAIR EXPENSE'
AND faadj.book_type_Code = h_book
AND faadj.asset_id = h_asset_id
AND faadj.period_counter_created = h_cpd_ctr
GROUP BY faadj.transaction_header_id;
SELECT SUM(faadj.adjustment_amount)
INTO h_bonus_deprn_amt
FROM FA_MC_ADJUSTMENTS faadj
WHERE
faadj.transaction_header_id = h_th_id_in
AND faadj.source_type_code = 'RETIREMENT'
AND faadj.adjustment_type = 'BONUS EXPENSE'
AND faadj.book_type_Code = h_book
AND faadj.asset_id = h_asset_id
AND faadj.period_counter_created = h_cpd_ctr
AND faadj.set_of_books_id = ret.set_of_books_id
GROUP BY faadj.transaction_header_id;
SELECT SUM(faadj.adjustment_amount)
INTO h_impairment_amt
FROM FA_MC_ADJUSTMENTS faadj
WHERE
faadj.transaction_header_id = h_th_id_in
AND faadj.source_type_code = 'RETIREMENT'
AND faadj.adjustment_type = 'IMPAIR EXPENSE'
AND faadj.book_type_Code = h_book
AND faadj.asset_id = h_asset_id
AND faadj.period_counter_created = h_cpd_ctr
AND faadj.set_of_books_id = ret.set_of_books_id
GROUP BY faadj.transaction_header_id;
SELECT SUM(faadj.adjustment_amount)
INTO h_reval_deprn_amt
FROM FA_ADJUSTMENTS faadj
WHERE
faadj.transaction_header_id = h_th_id_in
AND faadj.source_type_code = 'RETIREMENT'
AND faadj.adjustment_type = 'REVAL EXPENSE'
AND faadj.book_type_Code = h_book
AND faadj.asset_id = h_asset_id
AND faadj.period_counter_created = h_cpd_ctr
GROUP BY faadj.transaction_header_id;
SELECT SUM(faadj.adjustment_amount)
INTO h_reval_deprn_amt
FROM FA_MC_ADJUSTMENTS faadj
WHERE
faadj.transaction_header_id = h_th_id_in
AND faadj.source_type_code = 'RETIREMENT'
AND faadj.adjustment_type = 'REVAL EXPENSE'
AND faadj.book_type_Code = h_book
AND faadj.asset_id = h_asset_id
AND faadj.period_counter_created = h_cpd_ctr
AND faadj.set_of_books_id = ret.set_of_books_id
GROUP BY faadj.transaction_header_id;
SELECT SUM(faadj.adjustment_amount)
INTO h_reval_amort
FROM FA_ADJUSTMENTS faadj
WHERE
faadj.transaction_header_id = h_th_id_in
AND faadj.source_type_code = 'RETIREMENT'
AND faadj.adjustment_type = 'REVAL AMORT'
AND faadj.book_type_Code = h_book
AND faadj.asset_id = h_asset_id
AND faadj.period_counter_created = h_cpd_ctr
GROUP BY faadj.transaction_header_id;
SELECT SUM(faadj.adjustment_amount)
INTO h_reval_amort
FROM FA_MC_ADJUSTMENTS faadj
WHERE
faadj.transaction_header_id = h_th_id_in
AND faadj.source_type_code = 'RETIREMENT'
AND faadj.adjustment_type = 'REVAL AMORT'
AND faadj.book_type_Code = h_book
AND faadj.asset_id = h_asset_id
AND faadj.period_counter_created = h_cpd_ctr
AND faadj.set_of_books_id = ret.set_of_books_id
GROUP BY faadj.transaction_header_id;
| FUNCTION Calculate reserve retired and insert it into FA_ADJUSTMENTS. |
| It returns the current depreciation reserve before adjusted. |
| |
| HISTORY 08/30/89 R Rumanang Created |
| 11/21/89 R Rumanang Put distribution_id in adjustments |
| 05/03/91 M Chan Rewrote for MPL 9 |
| 01/03/96 S Behura Rewrote using PL/SQL |
| |
*============================================================================*/
FUNCTION fagprv(ret in out nocopy fa_ret_types.ret_struct,
bk in out nocopy fa_ret_types.book_struct,
cpd_ctr number, cost_frac in number,
today in date, user_id number,
deprn_amt in out nocopy number, reval_deprn_amt in out number,
reval_amort in out nocopy number, deprn_reserve in out number,
reval_reserve in out nocopy number,
bonus_deprn_amt in out nocopy number,
bonus_deprn_reserve in out nocopy number,
impairment_amt in out nocopy number,
impairment_reserve in out nocopy number,
g_deprn_amt in number, --Bug11886090
g_bonus_deprn_amt in number, --Bug11886090
p_log_level_rec in FA_API_TYPES.log_level_rec_type) Return BOOLEAN IS
CURSOR c_get_unit is
select units
from fa_asset_history
where asset_id = bk.group_asset_id
and transaction_header_id_out is null;
select cost
from fa_books
where asset_id = bk.group_asset_id
and book_type_code = ret.book
and date_ineffective is null;
The adj_amount for each of the rows will be updated
with that of the corresponding row. */
fagprv_err exception;
X_LAST_UPDATE_DATE date := sysdate;
X_last_updated_by number := -1;
X_last_update_login number := -1;
SELECT DISTRIBUTION_ID,
CODE_COMBINATION_ID,
TRANSACTION_UNITS,
UNITS_ASSIGNED
FROM FA_DISTRIBUTION_HISTORY
WHERE ASSET_ID = RET.asset_id
AND BOOK_TYPE_CODE = h_dist_book
AND RETIREMENT_ID = RET.retirement_id
ORDER BY DISTRIBUTION_ID;
select retirement_id
into l_id
from fa_retirements
where asset_id=ret.asset_id
and book_type_code=ret.book;
select adjusted_recoverable_cost
into l_prev_adj_rec_cost
from fa_books
where transaction_header_id_out = ret.th_id_in;
select adjusted_recoverable_cost
into l_new_adj_rec_cost
from fa_books
where transaction_header_id_in = ret.th_id_in;
select facb.deprn_reserve_acct,
facb.reval_reserve_acct,
facb.bonus_deprn_reserve_acct,
facb.impair_reserve_acct,
decode(bc.retire_reval_reserve_flag,'NO',0,1)
into h_deprn_rsv_acct,
h_reval_rsv_acct,
h_bonus_deprn_rsv_acct,
h_impairment_rsv_acct,
h_retire_reval_flag
from fa_additions_b faadd,
fa_category_books facb,
fa_book_controls bc
where faadd.asset_id = h_asset_id
and facb.category_id = faadd.asset_category_id
and facb.book_type_code = h_book
and bc.book_type_code = facb.book_type_code;
adj_row.last_update_date := today;
adj_row.selection_retid := 0;
adj_row.selection_thid := 0;
adj_row.selection_mode := FA_STD_TYPES.FA_AJ_CLEAR;
adj_row.selection_mode := FA_STD_TYPES.FA_AJ_ACTIVE;
fa_debug_pkg.add(l_calling_fn, '++ adj_row.selection_thid', adj_row.selection_thid, p_log_level_rec => p_log_level_rec);
X_last_update_date,
X_last_updated_by,
X_last_update_login,
p_log_level_rec => p_log_level_rec)) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
X_last_update_date,
X_last_updated_by,
X_last_update_login,
p_log_level_rec => p_log_level_rec)) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
X_last_update_date,
X_last_updated_by,
X_last_update_login
, p_log_level_rec => p_log_level_rec)) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
adj_row.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE_REVAL;
X_last_update_date,
X_last_updated_by,
X_last_update_login,
p_log_level_rec => p_log_level_rec)) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
select distinct nvl(transaction_header_id_out,0)
into l_id
from fa_distribution_history
where asset_id = h_asset_id
and book_type_code = h_book
and retirement_id = h_ret_id;
select distinct nvl(transaction_header_id_out,0)
into h_id_out
from fa_distribution_history
where asset_id = h_asset_id
and book_type_code = h_book
and retirement_id = h_ret_id;
select sum(nvl(units_assigned,0))
into h_sum_of_part_active_units
from fa_distribution_history
where asset_id = h_asset_id
and transaction_header_id_in = h_id_out
and date_ineffective is null;
adj_row.selection_thid := h_id_out;
adj_row.selection_mode := FA_STD_TYPES.FA_AJ_CLEAR_PARTIAL;
X_last_update_date,
X_last_updated_by,
X_last_update_login,
p_log_level_rec => p_log_level_rec)) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
adj_row.adjustment_amount := adj_row.amount_inserted -
tot_deprn_reserve;
value => adj_row.amount_inserted);
select nvl(units,0)
into h_cur_units
from fa_asset_history
where asset_id = h_asset_id
and date_ineffective is null;
select 1
into l_dummy
from fa_distribution_history
where asset_id = h_asset_id
and date_ineffective is null
and transaction_header_id_in = h_id_out
and rownum = 1;
select sum(nvl(units_assigned,0))
into h_cur_units
from fa_distribution_history
where asset_id = h_asset_id
and date_ineffective is null
and transaction_header_id_in = h_id_out;
adj_row.selection_thid := h_id_out;
adj_row.selection_mode := FA_STD_TYPES.FA_AJ_ACTIVE_PARTIAL;
adj_row.selection_mode := FA_STD_TYPES.FA_AJ_ACTIVE;
X_last_update_date,
X_last_updated_by,
X_last_update_login,
p_log_level_rec => p_log_level_rec)) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
adj_row.amount_inserted := 0;
adj_row.selection_thid := h_id_out;
adj_row.selection_mode := FA_STD_TYPES.FA_AJ_CLEAR;
element => 'Insert fa_adjustments in fagprv2,accnt_type',
value => adj_row.account_type, p_log_level_rec => p_log_level_rec);
X_last_update_date,
X_last_updated_by,
X_last_update_login
, p_log_level_rec => p_log_level_rec)) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
adj_row.adjustment_amount := adj_row.amount_inserted -
tot_bonus_deprn_reserve;
adj_row.selection_thid := 0;
adj_row.selection_mode := FA_STD_TYPES.FA_AJ_ACTIVE;
X_last_update_date,
X_last_updated_by,
X_last_update_login,
p_log_level_rec => p_log_level_rec)) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
adj_row.amount_inserted := 0;
adj_row.selection_thid := h_id_out;
adj_row.selection_mode := FA_STD_TYPES.FA_AJ_CLEAR;
element => 'Insert fa_adjustments in fagprv2,accnt_type',
value => adj_row.account_type, p_log_level_rec => p_log_level_rec);
X_last_update_date,
X_last_updated_by,
X_last_update_login,
p_log_level_rec => p_log_level_rec)) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
adj_row.adjustment_amount := adj_row.amount_inserted -
tot_impairment_reserve;
adj_row.selection_thid := 0;
adj_row.selection_mode := FA_STD_TYPES.FA_AJ_ACTIVE;
X_last_update_date,
X_last_updated_by,
X_last_update_login,
p_log_level_rec => p_log_level_rec)) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
adj_row.selection_thid := h_id_out;
/* Bug#7646218 - partial unit retirement Need to insert row in fa_adj for distribution id transferred out only.*/
adj_row.selection_mode := FA_STD_TYPES.FA_AJ_CLEAR_PARTIAL;
X_last_update_date,
X_last_updated_by,
X_last_update_login,
p_log_level_rec => p_log_level_rec)) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
adj_row.adjustment_amount := adj_row.amount_inserted -
tot_reval_reserve;
adj_row.selection_thid := 0;
adj_row.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE_REVAL;
X_last_update_date,
X_last_updated_by,
X_last_update_login,
p_log_level_rec => p_log_level_rec)) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
adj_row.selection_thid := 0;
adj_row.selection_mode := FA_STD_TYPES.FA_AJ_SINGLE;
adj_row.selection_retid := 0;
X_last_update_date,
X_last_updated_by,
X_last_update_login,
p_log_level_rec => p_log_level_rec)) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
adj_row.selection_mode := fa_std_types.FA_AJ_ACTIVE;
adj_row.selection_retid := 0;
X_last_update_date,
X_last_updated_by,
X_last_update_login
, p_log_level_rec => p_log_level_rec)) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
adj_row.selection_thid := 0;
adj_row.selection_mode := FA_STD_TYPES.FA_AJ_ACTIVE;
X_last_update_date,
X_last_updated_by,
X_last_update_login,
p_log_level_rec => p_log_level_rec)) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
adj_row.selection_thid := 0;
adj_row.selection_mode := FA_STD_TYPES.FA_AJ_ACTIVE;
X_last_update_date,
X_last_updated_by,
X_last_update_login,
p_log_level_rec => p_log_level_rec)) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
update fa_mc_books_summary
set expense_adjustment_amount = nvl(expense_adjustment_amount, 0) + nvl(g_deprn_amt, 0)
, deprn_amount = nvl(deprn_amount, 0) + nvl(g_deprn_amt, 0)
, ytd_deprn = nvl(ytd_deprn, 0) + nvl(g_deprn_amt, 0)
, reserve_adjustment_amount = nvl(reserve_adjustment_amount, 0) - nvl(g_deprn_amt, 0)
where asset_id = bk.group_asset_id
and book_type_code = ret.book
and period_counter = cpd_ctr
and set_of_books_id = ret.set_of_books_id;
update fa_books_summary
set expense_adjustment_amount = nvl(expense_adjustment_amount, 0) + nvl(g_deprn_amt, 0)
, deprn_amount = nvl(deprn_amount, 0) + nvl(g_deprn_amt, 0)
, ytd_deprn = nvl(ytd_deprn, 0) + nvl(g_deprn_amt, 0)
, reserve_adjustment_amount = nvl(reserve_adjustment_amount, 0) - nvl(g_deprn_amt, 0)
where asset_id = bk.group_asset_id
and book_type_code = ret.book
and period_counter = cpd_ctr;
adj_row.selection_thid := 0;
adj_row.selection_mode := FA_STD_TYPES.FA_AJ_ACTIVE;
X_last_update_date,
X_last_updated_by,
X_last_update_login,
p_log_level_rec => p_log_level_rec)) then
fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);