[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT fadh.distribution_id,
fadh.code_combination_id,
fadh.location_id,
fadh.assigned_to,
'N' retire_rec_found,
0 cost,
0 DEPRN_RSV,
0 REVAL_RSV,
0 BONUS_DEPRN_RSV,
0 IMPAIRMENT_RSV,
0 new_units,
fadh.code_combination_id adj_ccid
FROM fa_distribution_history fadh
where 1=0;
SELECT fadh.distribution_id,
faadj.transaction_header_id,
fadh.code_combination_id,
fadh.location_id,
fadh.assigned_to,
faadj.adjustment_type,
faadj.debit_credit_flag,
faadj.adjustment_amount,
'N' adj_rec_found,
faadj.code_combination_id adj_ccid
FROM fa_adjustments faadj, fa_distribution_history fadh
WHERE 1=0;
SELECT faadj.adjustment_type,
faadj.adjustment_amount,
faadj.debit_credit_flag,
faadj.debit_credit_flag rev_debit_credit_flag,
faadj.code_combination_id adj_ccid
from fa_adjustments faadj
where 1=0;
X_LAST_UPDATE_DATE date := sysdate;
X_last_updated_by number := -1;
X_last_update_login number := -1;
SELECT transaction_header_id
FROM fa_transaction_headers
WHERE member_transaction_header_id = ret.th_id_in
AND asset_id = bk.group_asset_id
AND book_type_code = ret.book;
SELECT
faadj.asset_id,
faadj.distribution_id,
faadj.code_combination_id,
faadj.adjustment_type,
DECODE(faadj.debit_credit_flag, 'CR', 'DR', 'CR'),
faadj.adjustment_amount,
faadj.track_member_flag --Bug8244128
FROM
fa_adjustments faadj, fa_retirements faret, fa_deprn_periods dp
WHERE faadj.transaction_header_id = faret.transaction_header_id_in
AND faadj.asset_id = faret.asset_id
AND faadj.book_type_Code = faret.book_type_code
AND faadj.source_type_code = decode(p_wip_asset, 1, 'CIP RETIREMENT',
'RETIREMENT')
AND faadj.adjustment_type in ('PROCEEDS', 'REMOVALCOST',
'NBV RETIRED', 'REVAL RSV RET',
'PROCEEDS CLR',
'REMOVALCOST CLR',
'CAPITAL ADJ',
'GENERAL FUND') -- Added for Bug 6666666
AND faret.retirement_id = p_ret_id
AND dp.book_type_code = faret.book_type_code
AND faret.date_effective between dp.period_open_date and
nvl(dp.period_close_date, sysdate)
AND faadj.period_counter_created = dp.period_counter
UNION
SELECT
faadj.asset_id,
faadj.distribution_id,
faadj.code_combination_id,
faadj.adjustment_type,
DECODE(faadj.debit_credit_flag, 'CR', 'DR', 'CR'),
faadj.adjustment_amount,
faadj.track_member_flag --Bug8244128
FROM
fa_adjustments faadj, fa_retirements faret, fa_deprn_periods dp
WHERE faadj.transaction_header_id = p_group_thid
AND faadj.asset_id = p_group_asset_id
AND faadj.book_type_Code = faret.book_type_code
AND faadj.source_type_code = decode(p_wip_asset, 1, 'CIP RETIREMENT',
'RETIREMENT')
AND faadj.adjustment_type in ('PROCEEDS', 'REMOVALCOST',
'NBV RETIRED', 'REVAL RSV RET',
'PROCEEDS CLR',
'REMOVALCOST CLR',
'CAPITAL ADJ',
'GENERAL FUND') -- Added for Bug 6666666
AND faret.retirement_id = p_ret_id
AND dp.book_type_code = faret.book_type_code
AND faret.date_effective between dp.period_open_date and
nvl(dp.period_close_date, sysdate)
AND faadj.period_counter_created = dp.period_counter;
SELECT
faadj.asset_id,
faadj.distribution_id,
faadj.code_combination_id,
faadj.adjustment_type,
DECODE(faadj.debit_credit_flag, 'CR', 'DR', 'CR'),
faadj.adjustment_amount,
faadj.track_member_flag --Bug8244128
FROM
fa_mc_adjustments faadj,
fa_mc_retirements faret,
fa_deprn_periods dp
WHERE faadj.transaction_header_id = faret.transaction_header_id_in
AND faadj.asset_id = faret.asset_id
AND faadj.set_of_books_id = ret.set_of_books_id
AND faadj.book_type_Code = faret.book_type_code
AND faadj.source_type_code = decode(p_wip_asset, 1, 'CIP RETIREMENT',
'RETIREMENT')
AND faadj.adjustment_type in ('PROCEEDS', 'REMOVALCOST',
'NBV RETIRED', 'REVAL RSV RET',
'PROCEEDS CLR',
'REMOVALCOST CLR',
'CAPITAL ADJ',
'GENERAL FUND') -- Added for Bug 6666666
AND faret.retirement_id = p_ret_id
AND faret.set_of_books_id = ret.set_of_books_id
AND dp.book_type_code = faret.book_type_code
AND faret.date_effective between dp.period_open_date and
nvl(dp.period_close_date, sysdate)
AND faadj.period_counter_created = dp.period_counter
UNION
SELECT
faadj.asset_id,
faadj.distribution_id,
faadj.code_combination_id,
faadj.adjustment_type,
DECODE(faadj.debit_credit_flag, 'CR', 'DR', 'CR'),
faadj.adjustment_amount,
faadj.track_member_flag --Bug8244128
FROM
fa_mc_adjustments faadj,
fa_mc_retirements faret,
fa_deprn_periods dp
WHERE faadj.transaction_header_id = p_group_thid
AND faadj.asset_id = p_group_asset_id
AND faadj.book_type_Code = faret.book_type_code
AND faadj.set_of_books_id = ret.set_of_books_id
AND faadj.source_type_code = decode(p_wip_asset, 1, 'CIP RETIREMENT',
'RETIREMENT')
AND faadj.adjustment_type in ('PROCEEDS', 'REMOVALCOST',
'NBV RETIRED', 'REVAL RSV RET',
'PROCEEDS CLR',
'REMOVALCOST CLR',
'CAPITAL ADJ',
'GENERAL FUND') -- Added for Bug 6666666
AND faret.retirement_id = p_ret_id
AND faret.set_of_books_id = ret.set_of_books_id
AND dp.book_type_code = faret.book_type_code
AND faret.date_effective between dp.period_open_date and
nvl(dp.period_close_date, sysdate)
AND faadj.period_counter_created = dp.period_counter;
UPDATE FA_RETIREMENTS
SET status = 'DELETED',
last_update_date = today,
last_updated_by = user_id
WHERE retirement_id = ret.retirement_id;
UPDATE FA_MC_RETIREMENTS
SET status = 'DELETED',
last_update_date = today,
last_updated_by = user_id
WHERE retirement_id = ret.retirement_id
AND set_of_books_id = ret.set_of_books_id;
select transaction_header_id_out
into h_th_id_out
from fa_retirements
where retirement_id = h_ret_id;
adj_row.last_update_date := today;
adj_row.selection_mode := fa_std_types.FA_AJ_SINGLE;
adj_row.selection_thid := 0;
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);
* we need to insert adjustment rows in associated tax books to
* move balances to distributions which will be created as a
* result of the reinstatement.
*
| History Jacob John 1/29/97 Created
*======================================================================*/
Function FAGTAX(
RET IN OUT NOCOPY fa_ret_types.ret_struct,
BK IN OUT NOCOPY fa_ret_types.book_struct,
today IN date
, p_log_level_rec IN FA_API_TYPES.log_level_rec_type) return boolean IS
adj_row fa_adjust_type_pkg.fa_adj_row_struct;
X_LAST_UPDATE_DATE date := sysdate;
X_last_updated_by number := -1;
X_last_update_login number := -1;
SELECT
'P',bc.set_of_books_id,
bc.book_type_code,
retire.transaction_header_id_out,
retire.transaction_header_id_in,
bk.group_asset_id,
bk.member_rollup_flag,
bk.tracking_method
FROM fa_book_controls bc, fa_retirements retire, fa_books bk
WHERE
retire.retirement_id = RET.retirement_id
AND retire.units is not null
AND bc.distribution_source_book = retire.book_type_code
AND bc.book_class = 'TAX'
AND bc.date_ineffective is null
AND bk.book_type_code = bc.book_type_code
AND bk.asset_id = RET.asset_id
AND bk.date_ineffective is null
UNION ALL
SELECT
'R',fmcbc.set_of_books_id,
fmcbc.book_type_code,
retire.transaction_header_id_out,
retire.transaction_header_id_in,
bk.group_asset_id,
bk.member_rollup_flag,
bk.tracking_method
FROM fa_mc_book_controls fmcbc,fa_book_controls fbc, fa_retirements retire, fa_mc_books bk
WHERE
retire.retirement_id = RET.retirement_id
AND retire.units is not null
AND fbc.distribution_source_book = retire.book_type_code
AND fbc.book_type_code=fmcbc.book_type_code
AND fmcbc.enabled_flag = 'Y'
AND fbc.set_of_books_id=fmcbc.primary_set_of_books_id
AND fbc.book_class = 'TAX'
AND fbc.date_ineffective is null
AND bk.book_type_code = fmcbc.book_type_code
AND bk.asset_id = RET.asset_id
AND bk.date_ineffective is null
ORDER BY 3,1;
adj_row.last_update_date := today;
adj_row.selection_retid := 0;
SELECT category_id
INTO h_category_id
FROM fa_asset_history
WHERE asset_id = RET.asset_id
AND date_ineffective is null;
SELECT count(*)
INTO
h_is_prior_period
FROM
FA_TRANSACTION_HEADERS TH,
FA_BOOK_CONTROLS BC,
FA_DEPRN_PERIODS DP,
FA_DEPRN_PERIODS DP_NOW
WHERE
TH.ASSET_ID = h_asset_id AND
-- TH.TRANSACTION_TYPE_CODE = 'ADDITION' AND --bug 6129798
TH.TRANSACTION_TYPE_CODE in ('ADDITION', 'CIP ADDITION') AND --bug 6129798
TH.BOOK_TYPE_CODE = BC.BOOK_TYPE_CODE AND
BC.BOOK_TYPE_CODE = h_book 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_COUNTER < DP_NOW.PERIOD_COUNTER AND
DP_NOW.BOOK_TYPE_CODE = TH.BOOK_TYPE_CODE AND
DP_NOW.PERIOD_CLOSE_DATE IS NULL;
else -- added in prior period,insert into fa_adjustments
-- SLA UPTAKE
-- assign an event for the transaction
if (h_mrc_sob_type_code = 'P') then
if (NOT fa_trx_approval_pkg.faxcat
(X_book => h_book,
X_asset_id => ret.asset_id,
X_trx_type => 'REINSTATEMENT',
X_trx_date => greatest(l_period_rec.calendar_period_open_date,
least(sysdate,l_period_rec.calendar_period_close_date)),
X_init_message_flag => 'NO',
p_log_level_rec => p_log_level_rec)) then
raise fagtax_error;
SELECT period_counter
into h_cpd_num
from
fa_deprn_periods
where book_type_code = h_book
and period_close_date is null;
adj_row.selection_thid := h_trans_header_id_out;
adj_row.selection_mode := fa_std_types.FA_AJ_CLEAR;
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.adjustment_amount := adj_row.amount_inserted;
l_temp_amount := adj_row.amount_inserted;
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_CLEAR;
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.adjustment_amount := adj_row.amount_inserted;
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_CLEAR;
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.adjustment_amount := adj_row.amount_inserted;
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_CLEAR;
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.adjustment_amount := adj_row.amount_inserted;
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_CLEAR;
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.adjustment_amount := adj_row.amount_inserted;
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_CLEAR;
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.adjustment_amount := adj_row.amount_inserted;
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_CLEAR;
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.adjustment_amount := adj_row.amount_inserted;
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_CLEAR;
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.adjustment_amount := adj_row.amount_inserted;
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 fadh.distribution_id
FROM fa_distribution_history fadh,
fa_book_controls bc
WHERE fadh.asset_id = h_asset_id
AND fadh.book_type_code = bc.distribution_source_book
AND bc.book_type_code = h_book
AND fadh.date_ineffective is null
ORDER BY fadh.distribution_id;
SELECT dh.distribution_id
FROM fa_distribution_history dh,
fa_book_controls bc,
fa_retirements rt
WHERE dh.asset_id = h_asset_id
AND dh.book_type_code = bc.distribution_source_book
AND bc.book_type_code = h_book
AND rt.asset_id = dh.asset_id
AND rt.book_type_code = h_book
AND dh.date_effective < rt.date_effective
AND dh.date_ineffective >= rt.date_effective;
SELECT dh_old.distribution_id,
dh_new.distribution_id
FROM fa_distribution_history dh_old,
fa_distribution_history dh_new,
fa_book_controls bc,
fa_transaction_headers th
WHERE th.transaction_header_id = h_rethdrout
and th.asset_id = h_asset_id
and th.book_type_code = h_book
and bc.book_type_code = th.book_type_code
/* nvl condition is added by bug 6709967 */
and dh_old.transaction_header_id_out = nvl(th.source_transaction_header_id,dh_old.transaction_header_id_out)
and dh_old.book_type_code = bc.distribution_source_book
and dh_old.asset_id = h_asset_id
and
(dh_old.units_assigned + dh_old.transaction_units = 0 -- FULL RET in dh_old DH row
OR
exists
(select 1 -- PARTIAL RET in dh_pret DH row
from fa_distribution_history dh_pret
where dh_pret.asset_id = dh_old.asset_id
and dh_pret.book_type_code = dh_old.book_type_code
and dh_pret.transaction_header_id_out = DH_OLD.transaction_header_id_in
and DH_OLD.transaction_units is NULL
and dh_pret.units_assigned + dh_pret.transaction_units = dh_old.units_assigned
and dh_pret.code_combination_id = dh_old.code_combination_id
and nvl(dh_pret.assigned_to,-99) = nvl(dh_old.assigned_to,-99)
and dh_pret.location_id = dh_old.location_id
)
--Added for 8741598
OR not exists
(select 1
from fa_distribution_history fdh1
where fdh1.asset_id = dh_old.asset_id
and fdh1.book_type_code = dh_old.book_type_code
and fdh1.transaction_header_id_in < DH_OLD.transaction_header_id_in)
--End of added for 8741598
)
-- and dh_new.transaction_header_id_in = dh_old.transaction_header_id_out
-- and dh_new.location_id = dh_old.location_id
-- and nvl(dh_new.assigned_to,-99) = nvl(dh_old.assigned_to,-99)
-- and dh_new.code_combination_id = dh_old.code_combination_id;
SELECT dh.distribution_id
FROM fa_distribution_history dh
WHERE dh.book_type_code = h_book
AND dh.asset_id = h_asset_id
AND dh.transaction_header_id_out is null
AND exists
(
SELECT 'x'
FROM fa_distribution_history ret
WHERE ret.book_type_code = h_book
AND ret.asset_id = h_asset_id
AND ret.retirement_id = h_ret_id
AND ret.code_combination_id = dh.code_combination_id
AND ret.location_id = dh.location_id
AND nvl (ret.assigned_to, -99) = nvl (dh.assigned_to, -99)
);
SELECT dh.distribution_id,
dh.units_assigned
FROM fa_distribution_history dh
WHERE dh.book_type_code = h_book
AND dh.asset_id = h_asset_id
AND dh.transaction_header_id_out = h_rethdrout
UNION
SELECT r.distribution_id, 0 - nvl (r.transaction_units, 0)
FROM fa_distribution_history r
WHERE r.book_type_code = h_book
AND r.asset_id = h_asset_id
AND r.retirement_id = h_ret_id
AND not exists
(
SELECT 'x'
FROM fa_distribution_history d
WHERE d.book_type_code = h_book
AND d.asset_id = h_asset_id
AND d.transaction_header_id_out = h_rethdrout
AND r.code_combination_id = d.code_combination_id
AND r.location_id = d.location_id
AND nvl (r.assigned_to, -99) = nvl (d.assigned_to, -99)
);
* for all updates in the case of multi-distributed assets
*/
CURSOR CHG_DIST IS
select d1.distribution_id
from fa_distribution_history d1,
fa_distribution_history d2
where d2.book_type_code = h_book
and d2.asset_id = h_asset_id
and d1.book_type_code = d2.book_type_code
and d1.asset_id = d2.asset_id
and d1.transaction_header_id_in =
d2.transaction_header_id_out
and ((abs(d2.transaction_units) =
d1.units_assigned) or
(d2.retirement_id = h_ret_id))
and d2.distribution_id = h_adj_distid
and d1.code_combination_id = d2.code_combination_id -- added for bug 2775057
and d1.location_id = d2.location_id -- added for bug 2775057
and nvl(d1.assigned_to, -99) = nvl(d2.assigned_to, -99); -- added for bug 2775057
SELECT count(*)
INTO h_mrc_primary_book_flag
FROM gl_sets_of_books GL, fa_book_controls FA
WHERE gl.set_of_books_id = fa.set_of_books_id
AND fa.book_type_code = h_book
AND gl.mrc_sob_type_code <> 'R'
AND rownum <= 1;
SELECT
transaction_header_id_out
INTO h_rethdrout
FROM fa_retirements
WHERE retirement_id = h_ret_id;
UPDATE FA_BOOKS
SET date_ineffective = h_today, -- to_date(h_today,'DD/MM/YYYY hh24:mi:ss'),
transaction_header_id_out = h_rethdrout,
last_updated_by = h_user_id,
last_update_date = h_today -- to_date(h_today, 'DD/MM/YYYY hh24:mi:ss')
WHERE book_type_code = h_book
AND asset_id = h_asset_id
AND date_ineffective is null;
select nvl(rate_in_use,0)
into l_rate_in_use
from fa_books
where asset_id = ret.asset_id
and book_type_code = ret.book
and transaction_header_id_out is null;
/*select nvl(rate_in_use,0)
into l_rate_in_use
from fa_mc_books
where asset_id = ret.asset_id
and book_type_code = ret.book
and set_of_books_id = ret.set_of_books_id
and transaction_header_id_out is null;*/
* had null in period_counter_life_complete since this was missing in the following insert.
*/
if ret.mrc_sob_type_code <> 'R' then
INSERT into fa_books
(book_type_Code, asset_id, date_placed_in_service,
transaction_header_id_in, date_effective, transaction_header_id_out,
date_ineffective, deprn_start_date, deprn_method_code,
life_in_months, rate_adjustment_factor, adjusted_cost, cost,
original_cost, salvage_value, period_counter_fully_retired,
period_counter_fully_reserved,period_counter_life_complete,
prorate_convention_code, prorate_date, itc_amount_id,
itc_amount, cost_change_flag,
adjustment_required_status,capitalize_flag,
retirement_id, retirement_pending_flag, depreciate_flag,
last_update_date,
last_updated_by, itc_basis, tax_request_id,
period_counter_capitalized, basic_rate, adjusted_rate, bonus_rule,
ceiling_name, recoverable_cost,
reval_amortization_basis, reval_ceiling,
production_capacity, fully_rsvd_revals_counter,
idled_flag, unit_of_measure, unrevalued_cost, adjusted_capacity,
short_fiscal_year_flag, conversion_date, original_deprn_start_date,
remaining_life1, remaining_life2,
old_adjusted_cost, formula_factor,
annual_deprn_rounding_flag,
percent_salvage_value, allowed_deprn_limit, allowed_deprn_limit_amount
, group_asset_id
, recapture_reserve_flag
, salvage_type
, deprn_limit_type
, super_group_id
, reduce_addition_flag
, reduce_adjustment_flag
, reduce_retirement_flag
, ytd_proceeds
, ltd_proceeds
, reduction_rate
, over_depreciate_option
, limit_proceeds_flag
, terminal_gain_loss
, tracking_method
, exclude_fully_rsv_flag
, excess_allocation_option
, depreciation_option
, member_rollup_flag
, allocate_to_fully_rsv_flag
, allocate_to_fully_ret_flag
, recognize_gain_loss
, terminal_gain_loss_amount
, cip_cost
, ltd_cost_of_removal
, eofy_reserve
, prior_eofy_reserve
, eop_adj_cost
, eop_formula_factor
, exclude_proceeds_from_basis
, retirement_deprn_option
, adjusted_recoverable_cost /* fix for bug 3149457 */
, cash_generating_unit_id
, extended_deprn_flag -- Japan Tax Phase3
, extended_depreciation_period -- Japan Tax Phase3
, nbv_at_switch
, prior_deprn_limit_type
, prior_deprn_limit_amount
, prior_deprn_limit
, prior_deprn_method
, prior_life_in_months
, prior_basic_rate
, prior_adjusted_rate
)
SELECT book_type_code
, asset_id
, date_placed_in_service
, h_rethdrout
, h_today
, null
, null, deprn_start_date, deprn_method_code
, life_in_months, p_asset_fin_rec_new.rate_adjustment_factor
, p_asset_fin_rec_new.adjusted_cost, p_asset_fin_rec_new.cost
, original_cost
, p_asset_fin_rec_new.salvage_value
, null
, decode(group_asset_id,null,period_counter_fully_reserved,null) --Bug 8425794
, period_counter_life_complete
, prorate_convention_code
, prorate_date
, itc_amount_id
, itc_amount, cost_change_flag,
adjustment_required_status, capitalize_flag,
null, 'NO', depreciate_flag,
h_today,
h_user_id, itc_basis, null,
period_counter_capitalized, basic_rate, adjusted_rate, bonus_rule,
ceiling_name, p_asset_fin_rec_new.recoverable_cost,
p_asset_fin_rec_new.reval_amortization_basis, reval_ceiling,--Bug#7478702
production_capacity, fully_rsvd_revals_counter,
idled_flag, unit_of_measure, p_asset_fin_rec_new.unrevalued_cost, adjusted_capacity,
short_fiscal_year_flag, conversion_date, original_deprn_start_date,
remaining_life1, remaining_life2,
old_adjusted_cost, formula_factor,
annual_deprn_rounding_flag,
percent_salvage_value, allowed_deprn_limit, p_asset_fin_rec_new.allowed_deprn_limit_amount
, group_asset_id
, recapture_reserve_flag
, salvage_type
, deprn_limit_type
, super_group_id
, reduce_addition_flag
, reduce_adjustment_flag
, reduce_retirement_flag
, ytd_proceeds
, ltd_proceeds
, reduction_rate
, over_depreciate_option
, limit_proceeds_flag
, terminal_gain_loss
, tracking_method
, exclude_fully_rsv_flag
, excess_allocation_option
, depreciation_option
, member_rollup_flag
, allocate_to_fully_rsv_flag
, allocate_to_fully_ret_flag
, recognize_gain_loss
, terminal_gain_loss_amount
, cip_cost
, ltd_cost_of_removal
, p_asset_fin_rec_new.eofy_reserve /* fix for bug 5260926 */
, prior_eofy_reserve
, eop_adj_cost
, eop_formula_factor
, exclude_proceeds_from_basis
, retirement_deprn_option
, p_asset_fin_rec_new.adjusted_recoverable_cost /* fix for bug 3149457 */
, cash_generating_unit_id
, extended_deprn_flag -- Japan Tax Phase3
, extended_depreciation_period -- Japan Tax Phase3
, nbv_at_switch
, prior_deprn_limit_type
, prior_deprn_limit_amount
, prior_deprn_limit
, prior_deprn_method
, prior_life_in_months
, prior_basic_rate
, prior_adjusted_rate
FROM fa_books
WHERE asset_id = ret.asset_id
AND book_type_code = ret.book
AND transaction_header_id_out = h_rethdrout;
INSERT into fa_mc_books
(book_type_Code, asset_id, date_placed_in_service,
transaction_header_id_in, date_effective, transaction_header_id_out,
date_ineffective, deprn_start_date, deprn_method_code,
life_in_months, rate_adjustment_factor, adjusted_cost, cost,
original_cost, salvage_value, period_counter_fully_retired,
period_counter_fully_reserved,period_counter_life_complete,
prorate_convention_code, prorate_date, itc_amount_id,
itc_amount, cost_change_flag,
adjustment_required_status,capitalize_flag,
retirement_id, retirement_pending_flag, depreciate_flag,
last_update_date,
last_updated_by, itc_basis, tax_request_id,
period_counter_capitalized, basic_rate, adjusted_rate, bonus_rule,
ceiling_name, recoverable_cost,
reval_amortization_basis, reval_ceiling,
production_capacity, fully_rsvd_revals_counter,
idled_flag, unit_of_measure, unrevalued_cost, adjusted_capacity,
short_fiscal_year_flag, conversion_date, original_deprn_start_date,
remaining_life1, remaining_life2,
old_adjusted_cost, formula_factor,
annual_deprn_rounding_flag,
percent_salvage_value, allowed_deprn_limit, allowed_deprn_limit_amount
, group_asset_id
, recapture_reserve_flag
, salvage_type
, deprn_limit_type
, super_group_id
, reduce_addition_flag
, reduce_adjustment_flag
, reduce_retirement_flag
, ytd_proceeds
, ltd_proceeds
, reduction_rate
, over_depreciate_option
, limit_proceeds_flag
, terminal_gain_loss
, tracking_method
, exclude_fully_rsv_flag
, excess_allocation_option
, depreciation_option
, member_rollup_flag
, allocate_to_fully_rsv_flag
, allocate_to_fully_ret_flag
, recognize_gain_loss
, terminal_gain_loss_amount
, cip_cost
, ltd_cost_of_removal
, eofy_reserve
, prior_eofy_reserve
, eop_adj_cost
, eop_formula_factor
, exclude_proceeds_from_basis
, retirement_deprn_option
, adjusted_recoverable_cost /* fix for bug 3149457 */
, cash_generating_unit_id
, set_of_books_id
)
SELECT book_type_code, asset_id, date_placed_in_service,
h_rethdrout, h_today, null,
null, deprn_start_date, deprn_method_code,
life_in_months, p_asset_fin_rec_new.rate_adjustment_factor
, p_asset_fin_rec_new.adjusted_cost, p_asset_fin_rec_new.cost,
original_cost,
p_asset_fin_rec_new.salvage_value, null, decode(group_asset_id,null,period_counter_fully_reserved,null) --Bug 8425794
,period_counter_life_complete,
prorate_convention_code, prorate_date, itc_amount_id,
itc_amount, cost_change_flag,
adjustment_required_status, capitalize_flag,
null, 'NO', depreciate_flag,
h_today,
h_user_id, itc_basis, null,
period_counter_capitalized, basic_rate, adjusted_rate, bonus_rule,
ceiling_name, p_asset_fin_rec_new.recoverable_cost,
p_asset_fin_rec_new.reval_amortization_basis, reval_ceiling,--Bug#7478702
production_capacity, fully_rsvd_revals_counter,
idled_flag, unit_of_measure, unrevalued_cost, adjusted_capacity,
short_fiscal_year_flag, conversion_date, original_deprn_start_date,
remaining_life1, remaining_life2,
old_adjusted_cost, formula_factor,
annual_deprn_rounding_flag,
percent_salvage_value, allowed_deprn_limit, p_asset_fin_rec_new.allowed_deprn_limit_amount
, group_asset_id
, recapture_reserve_flag
, salvage_type
, deprn_limit_type
, super_group_id
, reduce_addition_flag
, reduce_adjustment_flag
, reduce_retirement_flag
, ytd_proceeds
, ltd_proceeds
, reduction_rate
, over_depreciate_option
, limit_proceeds_flag
, terminal_gain_loss
, tracking_method
, exclude_fully_rsv_flag
, excess_allocation_option
, depreciation_option
, member_rollup_flag
, allocate_to_fully_rsv_flag
, allocate_to_fully_ret_flag
, recognize_gain_loss
, terminal_gain_loss_amount
, cip_cost
, ltd_cost_of_removal
, p_asset_fin_rec_new.eofy_reserve /* fix for bug 5260926 */
, prior_eofy_reserve
, eop_adj_cost
, eop_formula_factor
, exclude_proceeds_from_basis
, retirement_deprn_option
, p_asset_fin_rec_new.adjusted_recoverable_cost /* fix for bug 3149457 */
, cash_generating_unit_id
, set_of_books_id
FROM fa_mc_books
WHERE asset_id = ret.asset_id
AND book_type_code = ret.book
AND set_of_books_id = ret.set_of_books_id
AND transaction_header_id_out = h_rethdrout;
update fa_books
set rate_in_use = l_rate_in_use
where asset_id = ret.asset_id
and book_type_code = ret.Book
and transaction_header_id_out is null;
fa_debug_pkg.add(l_calling_fn, 'Updated rate_in_use (P) ', l_rate_in_use);
/*update fa_mc_books
set rate_in_use = l_rate_in_use
where asset_id = ret.asset_id
and book_type_code = ret.Book
and set_of_books_id = ret.set_of_books_id
and transaction_header_id_out is null;*/
fa_debug_pkg.add(l_calling_fn, 'Updated rate_in_use (R) ', l_rate_in_use);
SELECT distinct fadh.TRANSACTION_HEADER_ID_OUT,
DECODE(fadh.TRANSACTION_HEADER_ID_OUT, null, 0, 1)
INTO h_dist_hdrout, h_drflag
FROM FA_DISTRIBUTION_HISTORY fadh
WHERE fadh.retirement_id = h_ret_id;
UPDATE fa_adjustments aj
SET distribution_id = h_tdistid
WHERE aj.asset_id = h_asset_id
AND aj.book_type_code = h_book
AND aj.distribution_id = h_pdistid
AND aj.transaction_header_id = h_rethdrout;
UPDATE fa_mc_adjustments aj
SET distribution_id = h_tdistid
WHERE aj.asset_id = h_asset_id
AND aj.book_type_code = h_book
AND aj.distribution_id = h_pdistid
AND aj.set_of_books_id = ret.set_of_books_id
AND aj.transaction_header_id = h_rethdrout;
UPDATE fa_adjustments aj
SET distribution_id = h_tdistid
WHERE aj.asset_id = h_asset_id
AND aj.book_type_code = h_book
AND aj.distribution_id = h_pdistid
AND aj.transaction_header_id = h_rethdrout;
UPDATE fa_mc_adjustments aj
SET distribution_id = h_tdistid
WHERE aj.asset_id = h_asset_id
AND aj.book_type_code = h_book
AND aj.distribution_id = h_pdistid
AND aj.set_of_books_id = ret.set_of_books_id
AND aj.transaction_header_id = h_rethdrout;
element => 'Update FA_DISTRIBUTION_HISTORY',
value => '', p_log_level_rec => p_log_level_rec);
select count(*)
into h_count
from fa_distribution_history
where transaction_header_id_out = h_dist_hdrout
and book_type_code = h_book
and asset_id = h_asset_id
and retirement_id = h_ret_id;
UPDATE FA_DISTRIBUTION_HISTORY
SET date_ineffective =
h_today, -- to_date(h_today,'DD/MM/YYYY hh24:mi:ss'),
transaction_header_id_out = h_rethdrout,
last_update_date =
h_today, -- to_date(h_today,'DD/MM/YYYY hh24:mi:ss'),
last_updated_by = h_user_id
WHERE distribution_id = h_rdistid;
select rowid
into h_rowid
from fa_asset_history
where asset_id = RET.asset_id
and date_ineffective is null;
update fa_asset_history
set date_ineffective = today,
transaction_header_id_out = h_rethdrout
where rowid = h_rowid;
insert into fa_asset_history
(asset_id, category_id, units, asset_type,
date_effective, date_ineffective, last_update_date,
last_updated_by,transaction_header_id_in
)
select asset_id, category_id, units + RET.units_retired,
asset_type, today,
null, today,
user_id, h_rethdrout
from fa_asset_history
where rowid = h_rowid;
update fa_additions_B
set current_units = current_units + h_units_retired
where asset_id = RET.asset_id;
element => 'Update FA_DISTRIBUTION_HISTORY (FULL RETIREMENT)',
value => '');
UPDATE FA_DISTRIBUTION_HISTORY
SET date_ineffective =
today,
transaction_header_id_out = h_rethdrout,
last_update_date =
today,
last_updated_by = h_user_id
WHERE retirement_id = h_ret_id
AND book_type_code = h_book
AND asset_id = h_asset_id;
element => 'Update FA_DISTRIBUTION_HISTORY (new)',
value => '');
Select FA_DISTRIBUTION_HISTORY_s.nextval
into new_distid
from dual;
SELECT 0 - nvl (ret.transaction_units, 0)
INTO h_units_retired
FROM fa_distribution_history ret
WHERE ret.book_type_code = h_book
AND ret.asset_id = h_asset_id
AND ret.retirement_id = h_ret_id
AND exists
(
SELECT 'x'
FROM fa_distribution_history dh
WHERE dh.book_type_code = h_book
AND dh.asset_id = h_asset_id
AND dh.distribution_id = h_rdistid
AND dh.distribution_id <> ret.distribution_id
AND ret.code_combination_id = dh.code_combination_id
AND ret.location_id = dh.location_id
AND nvl (ret.assigned_to, -99) = nvl (dh.assigned_to, -99)
);
INSERT INTO FA_DISTRIBUTION_HISTORY
(distribution_id, book_type_code, asset_id,
units_assigned, date_effective, date_ineffective,
code_Combination_id,
location_id, assigned_to, transaction_header_id_in,
transaction_header_id_out, transaction_units,
retirement_id, last_update_date, last_updated_by)
SELECT new_distid, book_type_code, asset_id,
nvl(h_units_assigned,0) + nvl(h_units_retired,0), today,
null, code_Combination_id,
location_id, assigned_to, h_rethdrout,
null, null, null, today,
user_id
FROM FA_DISTRIBUTION_HISTORY
WHERE distribution_id = h_rdistid;
UPDATE FA_ADJUSTMENTS
SET DISTRIBUTION_ID = new_distid
WHERE TRANSACTION_HEADER_ID = h_rethdrout
AND DISTRIBUTION_ID =
(
SELECT DISTINCT r.distribution_id
FROM fa_distribution_history r
WHERE r.book_type_code = h_book
AND r.asset_id = h_asset_id
AND r.retirement_id = h_ret_id
AND exists
(
SELECT 'x'
FROM fa_distribution_history d
WHERE d.book_type_code = h_book
AND d.asset_id = h_asset_id
AND d.distribution_id = h_rdistid
AND r.code_combination_id = d.code_combination_id
AND r.location_id = d.location_id
AND nvl (r.assigned_to, -99) = nvl (d.assigned_to, -99)
)
);
/* BUG# 2775057 - added rdistid to the first subselect as well as
* assets with multiple disitributions were causing ora-1722
*/
DECLARE
/* Bug 3116047 - Broke the single update statement and created
this cursor enclosing with the DECLARE/BEGIN/END.
As a result of the High Cost SQL exercise msiddiqu */
/* Bug 4890085: Modified cursor C1 as it was not returning any rows.
The conditions "o.retirement_id = h_ret_id" and
"o.distribution_id = h_rdistid" were contradicting so splitted them. */
Cursor C1 is
SELECT DISTINCT n.distribution_id
FROM fa_distribution_history n
WHERE n.book_type_code = h_book
AND n.asset_id = h_asset_id
AND n.date_ineffective is null
AND exists
(
SELECT 'x'
FROM fa_distribution_history o
WHERE o.book_type_code = h_book
AND o.asset_id = h_asset_id
--AND o.retirement_id = h_ret_id
AND o.distribution_id = h_rdistid -- added for bug 2775057
AND n.code_combination_id = o.code_combination_id
AND n.location_id = o.location_id
AND nvl (n.assigned_to, -99) = nvl (o.assigned_to, -99)
)
AND exists
(
SELECT 'x'
FROM fa_distribution_history o
WHERE o.book_type_code = h_book
AND o.asset_id = h_asset_id
AND o.retirement_id = h_ret_id
--AND o.distribution_id = h_rdistid -- added for bug 2775057
AND n.code_combination_id = o.code_combination_id
AND n.location_id = o.location_id
AND nvl (n.assigned_to, -99) = nvl (o.assigned_to, -99)
);
SELECT DISTINCT r.distribution_id
INTO l_temp_dist_id1
FROM fa_distribution_history r
WHERE r.book_type_code = h_book
AND r.asset_id = h_asset_id
AND r.retirement_id = h_ret_id
AND exists
(
SELECT 'x'
FROM fa_distribution_history d
WHERE d.book_type_code = h_book
AND d.asset_id = h_asset_id
AND d.distribution_id = h_rdistid
AND r.code_combination_id = d.code_combination_id
AND r.location_id = d.location_id
AND nvl (r.assigned_to, -99) = nvl (d.assigned_to, -99)
);
UPDATE FA_MC_ADJUSTMENTS
SET DISTRIBUTION_ID = C1_rec.distribution_id
WHERE TRANSACTION_HEADER_ID = h_rethdrout
AND book_type_code = h_book /* Added for bug 7659930*/
AND DISTRIBUTION_ID = l_temp_dist_id1
AND set_of_books_id = ret.set_of_books_id;
select d1.distribution_id
into h_adj_distid
from fa_distribution_history d1,
fa_distribution_history d2
where d2.book_type_code = h_book
and d2.asset_id = h_asset_id
and d1.book_type_code = d2.book_type_code
and d1.asset_id = d2.asset_id
and d1.transaction_header_id_in =
d2.transaction_header_id_out
and d1.code_combination_id = d2.code_combination_id
and d1.location_id = d2.location_id
and nvl(d1.assigned_to, -99) = nvl(d2.assigned_to, -99)
and d2.distribution_id = h_rdistid;
UPDATE FA_ADJUSTMENTS
SET DISTRIBUTION_ID = h_adj_distid
WHERE TRANSACTION_HEADER_ID = h_rethdrout
AND DISTRIBUTION_ID <> h_adj_distid
AND DISTRIBUTION_ID =
(
SELECT DISTINCT r.distribution_id
FROM fa_distribution_history r
WHERE r.book_type_code = h_book
AND r.asset_id = h_asset_id
AND r.transaction_header_id_in =
(
SELECT DISTINCT transaction_header_id_out
FROM fa_distribution_history
WHERE book_type_code = h_book
AND asset_id = h_asset_id
AND retirement_id = h_ret_id
)
AND exists
(
SELECT 'x'
FROM fa_distribution_history d
WHERE d.book_type_code = h_book
AND d.asset_id = h_asset_id
AND d.distribution_id = h_rdistid
AND r.code_combination_id = d.code_combination_id
AND r.location_id = d.location_id
AND nvl (r.assigned_to, -99) = nvl (d.assigned_to, -99)
)
)
AND not exists
(
SELECT 'x'
FROM fa_distribution_history
WHERE book_type_code = h_book
AND asset_id = h_asset_id
AND retirement_id = h_ret_id
AND distribution_id = h_adj_distid
);
UPDATE FA_MC_ADJUSTMENTS
SET DISTRIBUTION_ID = h_adj_distid
WHERE TRANSACTION_HEADER_ID = h_rethdrout
AND DISTRIBUTION_ID <> h_adj_distid
AND SET_OF_BOOKS_ID = ret.set_of_books_id
AND DISTRIBUTION_ID =
(
SELECT DISTINCT r.distribution_id
FROM fa_distribution_history r
WHERE r.book_type_code = h_book
AND r.asset_id = h_asset_id
AND r.transaction_header_id_in =
(
SELECT DISTINCT transaction_header_id_out
FROM fa_distribution_history
WHERE book_type_code = h_book
AND asset_id = h_asset_id
AND retirement_id = h_ret_id
)
AND exists
(
SELECT 'x'
FROM fa_distribution_history d
WHERE d.book_type_code = h_book
AND d.asset_id = h_asset_id
AND d.distribution_id = h_rdistid
AND r.code_combination_id = d.code_combination_id
AND r.location_id = d.location_id
AND nvl (r.assigned_to, -99) = nvl (d.assigned_to, -99)
)
)
AND not exists
(
SELECT 'x'
FROM fa_distribution_history
WHERE book_type_code = h_book
AND asset_id = h_asset_id
AND retirement_id = h_ret_id
AND distribution_id = h_adj_distid
);
SELECT fadh.distribution_id,
fadh.code_combination_id,
fadh.location_id,
nvl(fadh.assigned_to,-99) assigned_to,
'N' retire_rec_found,
0 cost,
0 DEPRN_RSV,
0 REVAL_RSV,
0 BONUS_DEPRN_RSV,
0 IMPAIRMENT_RSV,
0 new_units,
fadh.code_combination_id adj_ccid
FROM fa_distribution_history fadh
WHERE fadh.asset_id = RET.asset_id
AND fadh.date_ineffective is null
AND fadh.transaction_units is null
order by distribution_id;
SELECT min(fadh.distribution_id) distribution_id,
faadj.transaction_header_id,
fadh.code_combination_id,
fadh.location_id,
nvl(fadh.assigned_to,-99) assigned_to,
faadj.adjustment_type,
decode(sign(sum(decode(faadj.debit_credit_flag,'DR',faadj.adjustment_amount,
-1*faadj.adjustment_amount))),-1,'CR','DR') debit_credit_flag,
abs(sum(decode(faadj.debit_credit_flag,'DR',faadj.adjustment_amount,-1*faadj.adjustment_amount)))
adjustment_amount,
'N' adj_rec_found,
faadj.code_combination_id adj_ccid
FROM fa_adjustments faadj, fa_distribution_history fadh
where fadh.asset_id = RET.asset_id
AND faadj.book_type_code = BK.dis_book
AND faadj.asset_id = RET.asset_id
and fadh.distribution_id = faadj.distribution_id
and faadj.transaction_header_id = ret.th_id_in
AND faadj.source_type_code = decode(RET.wip_asset, 1,
'CIP RETIREMENT','RETIREMENT')
AND faadj.adjustment_type in ('COST', 'CIP COST')
group by
faadj.transaction_header_id,
fadh.code_combination_id,
fadh.location_id,
nvl(fadh.assigned_to,-99),
faadj.adjustment_type,
'N',
faadj.code_combination_id
order by 1,2;
select ret.adjustment_type,
abs(adjustment_amount) adjustment_amount,
decode(sign(adjustment_amount),1,'DR','CR') debit_credit_flag,
decode(sign(adjustment_amount),1,'CR','DR') rev_debit_credit_flag,
ret.adj_ccid
from
(
select adjustment_type,
sum(decode(debit_credit_flag,'DR',adjustment_amount,-1*adjustment_amount )) adjustment_amount,
faadj.code_combination_id adj_ccid
from fa_adjustments faadj
where faadj.asset_id = RET.asset_id
and faadj.transaction_header_id = ret.th_id_in
and faadj.book_type_code = ret.book
and faadj.adjustment_type in ('COST', 'CIP COST')
and faadj.source_type_code = decode(RET.wip_asset, 1,
'CIP RETIREMENT','RETIREMENT')
group by adjustment_type, faadj.code_combination_id
) ret
where ret.adjustment_amount <> 0;
SELECT min(fadh.distribution_id) distribution_id,
faadj.transaction_header_id,
fadh.code_combination_id,
fadh.location_id,
nvl(fadh.assigned_to,-99) assigned_to,
faadj.adjustment_type,
decode(sign(sum(decode(faadj.debit_credit_flag,'DR',faadj.adjustment_amount,
-1*faadj.adjustment_amount))),-1,'CR','DR') debit_credit_flag,
abs(sum(decode(faadj.debit_credit_flag,'DR',faadj.adjustment_amount,-1*faadj.adjustment_amount)))
adjustment_amount,
'N' adj_rec_found,
faadj.code_combination_id adj_ccid
FROM fa_mc_adjustments faadj, fa_distribution_history fadh
where fadh.asset_id = RET.asset_id
AND faadj.book_type_code = BK.dis_book
AND faadj.asset_id = RET.asset_id
and fadh.distribution_id = faadj.distribution_id
and faadj.transaction_header_id = ret.th_id_in
and faadj.set_of_books_id = ret.set_of_books_id
AND faadj.source_type_code = decode(RET.wip_asset, 1,
'CIP RETIREMENT','RETIREMENT')
AND faadj.adjustment_type in ('COST', 'CIP COST')
group by
faadj.transaction_header_id,
fadh.code_combination_id,
fadh.location_id,
nvl(fadh.assigned_to,-99),
faadj.adjustment_type,
'N',
faadj.code_combination_id
order by 1,2;
select ret.adjustment_type,
abs(adjustment_amount) adjustment_amount,
decode(sign(adjustment_amount),1,'DR','CR') debit_credit_flag,
decode(sign(adjustment_amount),1,'CR','DR') rev_debit_credit_flag,
ret.adj_ccid
from
(
select adjustment_type,
sum(decode(debit_credit_flag,'DR',adjustment_amount,-1*adjustment_amount )) adjustment_amount,
faadj.code_combination_id adj_ccid
from fa_mc_adjustments faadj
where faadj.asset_id = RET.asset_id
and faadj.transaction_header_id = ret.th_id_in
and faadj.book_type_code = ret.book
and faadj.set_of_books_id = ret.set_of_books_id
and faadj.adjustment_type in ('COST', 'CIP COST')
and faadj.source_type_code = decode(RET.wip_asset, 1,
'CIP RETIREMENT','RETIREMENT')
group by adjustment_type, faadj.code_combination_id
) ret
where ret.adjustment_amount <> 0;
X_LAST_UPDATE_DATE date := sysdate;
X_last_updated_by number := -1;
X_last_update_login number := -1;
select transaction_header_id_out
into h_th_id_out
from fa_retirements
where retirement_id = h_retirement_id;
adj_row.last_update_date := today;
adj_row.selection_mode := fa_std_types.FA_AJ_SINGLE;
adj_row.selection_thid := 0;
adj_row.selection_retid := 0;
g_tbl_adj_cost.delete;
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 transaction_header_id
FROM fa_transaction_headers
WHERE member_transaction_header_id = ret.th_id_in
AND asset_id = bk.group_asset_id
AND book_type_code = ret.book;
SELECT dh_old.distribution_id,
dh_new.distribution_id,
dh_old.code_combination_id,
dh_new.code_combination_id
FROM fa_distribution_history dh_old,
fa_distribution_history dh_new
WHERE
dh_old.retirement_id = h_retirement_id and
dh_old.book_type_code = RET.book and
dh_old.units_assigned + dh_old.transaction_units <> 0
AND
dh_new.transaction_header_id_in=
dh_old.transaction_header_id_out and
dh_new.location_id = dh_old.location_id and
nvl(dh_new.assigned_to,-99) = nvl(dh_old.assigned_to,-99) and
dh_new.code_combination_id = dh_old.code_combination_id;
SELECT fadh.distribution_id,
fadh.code_combination_id,
fadh.location_id,
nvl(fadh.assigned_to,-99) assigned_to,
'N' retire_rec_found,
0 cost,
0 DEPRN_RSV,
0 REVAL_RSV,
0 BONUS_DEPRN_RSV,
0 IMPAIRMENT_RSV,
0 new_units,
fadh.code_combination_id adj_ccid
FROM fa_distribution_history fadh
WHERE fadh.asset_id = RET.asset_id
AND fadh.date_ineffective is null
AND fadh.transaction_units is null
order by distribution_id;
SELECT fadh.distribution_id,
fadh.code_combination_id,
fadh.location_id,
nvl(fadh.assigned_to,-99) assigned_to,
'N' retire_rec_found,
0 cost,
0 DEPRN_RSV,
0 REVAL_RSV,
0 BONUS_DEPRN_RSV,
0 IMPAIRMENT_RSV,
0 new_units,
fadh.code_combination_id adj_ccid
FROM fa_distribution_history fadh
WHERE fadh.asset_id = RET.asset_id
AND fadh.TRANSACTION_HEADER_ID_OUT
= (select rt.transaction_header_id_out
from fa_retirements rt
where rt.retirement_id = RET.retirement_id
)
order by distribution_id;
SELECT min(fadh.distribution_id) distribution_id,
faadj.transaction_header_id,
fadh.code_combination_id,
fadh.location_id,
nvl(fadh.assigned_to,-99) assigned_to,
faadj.adjustment_type,
decode(sign(sum(decode(faadj.debit_credit_flag,'DR',faadj.adjustment_amount,
-1*faadj.adjustment_amount))),-1,'CR','DR') debit_credit_flag,
abs(sum(decode(faadj.debit_credit_flag,'DR',faadj.adjustment_amount,-1*faadj.adjustment_amount)))
adjustment_amount,
'N' adj_rec_found,
faadj.code_combination_id adj_ccid
FROM fa_adjustments faadj, fa_distribution_history fadh
where fadh.asset_id = RET.asset_id
AND faadj.book_type_code = BK.dis_book
AND faadj.asset_id = RET.asset_id
and fadh.distribution_id = faadj.distribution_id
and faadj.transaction_header_id = ret.th_id_in
and faadj.adjustment_type in ('RESERVE', 'BONUS RESERVE', 'REVAL RESERVE', 'IMPAIR RESERVE')
group by
faadj.transaction_header_id,
fadh.code_combination_id,
fadh.location_id,
nvl(fadh.assigned_to,-99),
faadj.adjustment_type,
'N',
faadj.code_combination_id
order by 1,2;
select cost_ret.adjustment_type,
abs(cost_ret.adjustment_amount) adjustment_amount,
decode(sign(cost_ret.adjustment_amount),1,'DR','CR') debit_credit_flag,
decode(sign(cost_ret.adjustment_amount),1,'CR','DR') rev_debit_credit_flag,
cost_ret.adj_ccid
from
(
select adjustment_type,
sum(decode(debit_credit_flag,'DR',adjustment_amount,-1*adjustment_amount )) adjustment_amount,
faadj.code_combination_id adj_ccid
from fa_adjustments faadj
where ( (faadj.transaction_header_id = ret.th_id_in AND faadj.asset_id = h_asset_id)
)
and faadj.book_type_code = ret.book
and faadj.adjustment_type in ('RESERVE', 'BONUS RESERVE', 'REVAL RESERVE', 'IMPAIR RESERVE')
group by adjustment_type, faadj.code_combination_id
) cost_ret
where cost_ret.adjustment_amount <> 0;
select cost_ret.adjustment_type,
abs(cost_ret.adjustment_amount) adjustment_amount,
decode(sign(cost_ret.adjustment_amount),1,'DR','CR') debit_credit_flag,
decode(sign(cost_ret.adjustment_amount),1,'CR','DR') rev_debit_credit_flag,
cost_ret.adj_ccid
from
(
select adjustment_type,
sum(decode(debit_credit_flag,'DR',adjustment_amount,-1*adjustment_amount )) adjustment_amount,
faadj.code_combination_id adj_ccid
from fa_adjustments faadj
where (
(faadj.transaction_header_id = l_group_thid AND faadj.asset_id = bk.group_asset_id)
)
and faadj.book_type_code = ret.book
and faadj.adjustment_type in ('RESERVE', 'BONUS RESERVE', 'REVAL RESERVE', 'IMPAIR RESERVE')
group by adjustment_type, faadj.code_combination_id
) cost_ret
where cost_ret.adjustment_amount <> 0;
SELECT min(fadh.distribution_id) distribution_id,
faadj.transaction_header_id,
fadh.code_combination_id,
fadh.location_id,
nvl(fadh.assigned_to,-99) assigned_to,
faadj.adjustment_type,
decode(sign(sum(decode(faadj.debit_credit_flag,'DR',faadj.adjustment_amount,
-1*faadj.adjustment_amount))),-1,'CR','DR') debit_credit_flag,
abs(sum(decode(faadj.debit_credit_flag,'DR',faadj.adjustment_amount,-1*faadj.adjustment_amount)))
adjustment_amount,
'N' adj_rec_found,
faadj.code_combination_id adj_ccid
FROM fa_mc_adjustments faadj, fa_distribution_history fadh
where fadh.asset_id = RET.asset_id
AND faadj.book_type_code = BK.dis_book
AND faadj.asset_id = RET.asset_id
and fadh.distribution_id = faadj.distribution_id
and faadj.transaction_header_id = ret.th_id_in
and faadj.set_of_books_id = ret.set_of_books_id
and faadj.adjustment_type in ('RESERVE', 'BONUS RESERVE', 'REVAL RESERVE', 'IMPAIR RESERVE')
group by
faadj.transaction_header_id,
fadh.code_combination_id,
fadh.location_id,
nvl(fadh.assigned_to,-99),
faadj.adjustment_type,
'N',
faadj.code_combination_id
order by 1,2;
select cost_ret.adjustment_type,
abs(cost_ret.adjustment_amount) adjustment_amount,
decode(sign(cost_ret.adjustment_amount),1,'DR','CR') debit_credit_flag,
decode(sign(cost_ret.adjustment_amount),1,'CR','DR') rev_debit_credit_flag,
cost_ret.adj_ccid
from
(
select adjustment_type,
sum(decode(debit_credit_flag,'DR',adjustment_amount,-1*adjustment_amount )) adjustment_amount,
faadj.code_combination_id adj_ccid
from fa_mc_adjustments faadj
where ( (faadj.transaction_header_id = ret.th_id_in AND faadj.asset_id = h_asset_id)
)
and faadj.book_type_code = ret.book
and faadj.set_of_books_id = ret.set_of_books_id
and faadj.adjustment_type in ('RESERVE', 'BONUS RESERVE', 'REVAL RESERVE', 'IMPAIR RESERVE')
group by faadj.adjustment_type, faadj.code_combination_id
) cost_ret
where cost_ret.adjustment_amount <> 0;
select cost_ret.adjustment_type,
abs(cost_ret.adjustment_amount) adjustment_amount,
decode(sign(cost_ret.adjustment_amount),1,'DR','CR') debit_credit_flag,
decode(sign(cost_ret.adjustment_amount),1,'CR','DR') rev_debit_credit_flag,
cost_ret.adj_ccid
from
(
select adjustment_type,
sum(decode(debit_credit_flag,'DR',adjustment_amount,-1*adjustment_amount )) adjustment_amount,
faadj.code_combination_id adj_ccid
from fa_mc_adjustments faadj
where (
(faadj.transaction_header_id = l_group_thid AND faadj.asset_id = bk.group_asset_id)
)
and faadj.book_type_code = ret.book
and faadj.set_of_books_id = ret.set_of_books_id
and faadj.adjustment_type in ('RESERVE', 'BONUS RESERVE', 'REVAL RESERVE', 'IMPAIR RESERVE')
group by faadj.adjustment_type, faadj.code_combination_id
) cost_ret
where cost_ret.adjustment_amount <> 0;
select transaction_header_id_out
into h_th_id_out
from fa_retirements
where retirement_id = h_retirement_id;
adj_row.last_update_date := today;
adj_row.selection_mode := fa_std_types.FA_AJ_SINGLE;
adj_row.selection_thid := 0;
adj_row.selection_retid := 0;
SELECT category_id
INTO h_category_id
FROM fa_asset_history
WHERE asset_id = RET.asset_id
AND date_ineffective is null;
are inserted in fa_adjustments for a group when member asset
is retired
*/
if p_log_level_rec.statement_level then
fa_debug_pkg.add
(fname => l_calling_fn,
element => 'Populate PL-SQL tables',
value => '', p_log_level_rec => p_log_level_rec);
g_tbl_adj_rsv.delete;
l_tbl_adj.delete;
l_tbl_ret.delete;
l_tbl_cost_ret.delete;
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 dh.distribution_id,
dh.code_combination_id,
(dh.units_assigned / ah.units)
* expense_amount -
nvl(adj.adjustment_amount, 0),
(dh.units_assigned / ah.units)
* expense_amount --Bug 6666666
FROM
FA_DISTRIBUTION_HISTORY dh,
FA_ASSET_HISTORY ah,
FA_ADJUSTMENTS adj
WHERE dh.asset_id = RET.asset_id
AND dh.book_type_code = BK.dis_book
AND dh.date_ineffective is null
AND dh.distribution_id = adj.distribution_id(+)
AND ah.asset_id = RET.asset_id
AND ah.date_ineffective is null
AND adj.transaction_header_id(+) = RET.th_id_in
AND adj.source_type_code(+) = 'RETIREMENT'
AND adj.adjustment_type(+) = adj_type
AND adj.debit_credit_flag(+) = 'DR'
AND adj.asset_id(+) = RET.asset_id
AND adj.book_type_code(+) = RET.book
union all
SELECT dh.distribution_id,
dh.code_combination_id,
((1/(1-h_cost_frac))-1) * nvl(adj.adjustment_amount, 0),
0 -- Bug 6666666
FROM FA_DISTRIBUTION_HISTORY dh
,FA_ASSET_HISTORY ah
,FA_TRANSACTION_HEADERS ret_th
,FA_ADJUSTMENTS adj
,FA_TRANSACTION_HEADERS exp_th
WHERE dh.asset_id = RET.asset_id
AND dh.book_type_code = BK.dis_book
AND dh.date_ineffective is null
AND dh.distribution_id = adj.distribution_id
AND ah.asset_id = RET.asset_id
AND ah.date_ineffective is null
AND ret_th.transaction_header_id = RET.th_id_in
AND ret_th.asset_id = dh.asset_id
AND ret_th.book_type_code = RET.book
AND ret_th.transaction_type_code like '%RETIREMENT'
AND adj.transaction_header_id >= RET.th_id_in
AND adj.transaction_header_id <= h_th_id_out
AND adj.source_type_code = 'DEPRECIATION'
AND adj.adjustment_type = adj_type
AND adj.debit_credit_flag = 'DR'
AND adj.asset_id = RET.asset_id
AND adj.book_type_code = RET.book
AND exp_th.transaction_header_id = adj.transaction_header_id
AND exp_th.asset_id = adj.asset_id
AND exp_th.book_type_code = adj.book_type_code
AND exp_th.transaction_subtype = 'EXPENSED'
;
SELECT dh.distribution_id,
dh.code_combination_id,
(dh.units_assigned / ah.units)
* expense_amount -
nvl(adj.adjustment_amount, 0),
(dh.units_assigned / ah.units)
* expense_amount -- Bug 6666666
FROM
FA_DISTRIBUTION_HISTORY dh,
FA_ASSET_HISTORY ah,
FA_MC_ADJUSTMENTS adj
WHERE dh.asset_id = RET.asset_id
AND dh.book_type_code = BK.dis_book
AND dh.date_ineffective is null
AND dh.distribution_id = adj.distribution_id(+)
AND ah.asset_id = RET.asset_id
AND ah.date_ineffective is null
AND adj.transaction_header_id(+) = RET.th_id_in
AND adj.source_type_code(+) = 'RETIREMENT'
AND adj.adjustment_type(+) = adj_type
AND adj.debit_credit_flag(+) = 'DR'
AND adj.set_of_books_id(+) = ret.set_of_books_id --Bug#8761988
AND adj.asset_id(+) = RET.asset_id
AND adj.book_type_code(+) = RET.book
union all
SELECT dh.distribution_id,
dh.code_combination_id,
((1/(1-h_cost_frac))-1) * nvl(adj.adjustment_amount, 0),
0 -- Bug 6666666
FROM
FA_DISTRIBUTION_HISTORY dh,
FA_ASSET_HISTORY ah,
FA_MC_ADJUSTMENTS adj -- bug#5094783 fix
WHERE dh.asset_id = RET.asset_id
AND dh.book_type_code = BK.dis_book
AND dh.date_ineffective is null
AND dh.distribution_id = adj.distribution_id(+)
AND ah.asset_id = RET.asset_id
AND ah.date_ineffective is null
AND adj.transaction_header_id(+) >= RET.th_id_in
AND adj.transaction_header_id(+) <= h_th_id_out
AND adj.source_type_code(+) = 'DEPRECIATION'
AND adj.adjustment_type(+) = adj_type
AND adj.debit_credit_flag(+) = 'DR'
AND adj.set_of_books_id(+) = RET.set_of_books_id
AND adj.asset_id(+) = RET.asset_id
AND adj.book_type_code(+) = RET.book;
SELECT
faadj.distribution_id,
faadj.code_combination_id,
- 1 * faadj.adjustment_amount
FROM
fa_distribution_history fadh,
fa_adjustments faadj
WHERE fadh.asset_id(+) = RET.asset_id
AND fadh.book_type_code(+) = BK.dis_book
AND fadh.date_ineffective(+) is null
AND fadh.distribution_id(+) = faadj.distribution_id
AND fadh.distribution_id is null
AND faadj.transaction_header_id = RET.th_id_in
AND faadj.source_type_code = 'RETIREMENT'
AND faadj.adjustment_type = adj_type
AND faadj.debit_credit_flag = 'DR'
AND faadj.asset_id = RET.asset_id
AND faadj.book_type_code = RET.book;
SELECT
faadj.distribution_id,
faadj.code_combination_id,
- 1 * faadj.adjustment_amount
FROM
fa_distribution_history fadh,
fa_mc_adjustments faadj
WHERE fadh.asset_id(+) = RET.asset_id
AND fadh.book_type_code(+) = BK.dis_book
AND fadh.date_ineffective(+) is null
AND fadh.distribution_id(+) = faadj.distribution_id
AND fadh.distribution_id is null
AND faadj.transaction_header_id = RET.th_id_in
AND faadj.source_type_code = 'RETIREMENT'
AND faadj.adjustment_type = adj_type
AND faadj.debit_credit_flag = 'DR'
AND faadj.asset_id = RET.asset_id
AND faadj.set_of_books_id = RET.set_of_books_id
AND faadj.book_type_code = RET.book;
SELECT
fadh.distribution_id,
fadh.code_combination_id,
(ABS(fadh.transaction_units) / faret.units)
* expense_amount -
NVL(faadj.adjustment_amount, 0),
(ABS(fadh.transaction_units) / faret.units)
* expense_amount -- Bug 6666666
FROM FA_RETIREMENTS faret, fa_distribution_history fadh,
fa_adjustments faadj
WHERE fadh.asset_id = RET.asset_id
AND fadh.book_type_code = BK.dis_book
AND fadh.retirement_id = RET.retirement_id
AND fadh.distribution_id = faadj.distribution_id(+)
AND faadj.transaction_header_id(+) = RET.th_id_in
AND faadj.source_type_code(+) = 'RETIREMENT'
AND faadj.adjustment_type(+) = adj_type
AND faadj.debit_credit_flag(+) = 'DR'
AND faadj.asset_id(+) = RET.asset_id
AND faadj.book_type_code(+) = RET.book
AND faret.retirement_id = RET.retirement_id;
SELECT
fadh.distribution_id,
fadh.code_combination_id,
(ABS(fadh.transaction_units) / faret.units)
* expense_amount -
NVL(faadj.adjustment_amount, 0),
(ABS(fadh.transaction_units) / faret.units)
* expense_amount -- Bug 6666666
FROM FA_MC_RETIREMENTS faret, fa_distribution_history fadh,
fa_mc_adjustments faadj
WHERE fadh.asset_id = RET.asset_id
AND fadh.book_type_code = BK.dis_book
AND fadh.retirement_id = RET.retirement_id
AND fadh.distribution_id = faadj.distribution_id(+)
AND faadj.transaction_header_id(+) = RET.th_id_in
AND faadj.source_type_code(+) = 'RETIREMENT'
AND faadj.adjustment_type(+) = adj_type
AND faadj.debit_credit_flag(+) = 'DR'
AND faadj.asset_id(+) = RET.asset_id
AND faadj.book_type_code(+) = RET.book
AND faadj.set_of_books_id(+) = RET.set_of_books_id
AND faret.set_of_books_id = RET.set_of_books_id
AND faret.retirement_id = RET.retirement_id;
SELECT
faadj.distribution_id,
faadj.code_combination_id,
- 1 * faadj.adjustment_amount
FROM
FA_RETIREMENTS faret,
FA_DISTRIBUTION_HISTORY fadh,
FA_ADJUSTMENTS faadj
WHERE fadh.asset_id(+) = RET.asset_id
AND fadh.book_type_code(+) = BK.dis_book
AND fadh.retirement_id(+) = RET.retirement_id
AND fadh.distribution_id(+) = faadj.distribution_id
AND fadh.distribution_id is null
AND faadj.transaction_header_id = faret.transaction_header_id_in
AND faadj.source_type_code = 'RETIREMENT'
AND faadj.adjustment_type = adj_type
AND faadj.debit_credit_flag = 'DR'
AND faadj.asset_id = RET.asset_id
AND faadj.book_type_code = RET.book
AND faret.retirement_id = RET.retirement_id;
SELECT
faadj.distribution_id,
faadj.code_combination_id,
- 1 * faadj.adjustment_amount
FROM
FA_MC_RETIREMENTS faret,
FA_DISTRIBUTION_HISTORY fadh,
FA_MC_ADJUSTMENTS faadj
WHERE fadh.asset_id(+) = RET.asset_id
AND fadh.book_type_code(+) = BK.dis_book
AND fadh.retirement_id(+) = RET.retirement_id
AND fadh.distribution_id(+) = faadj.distribution_id
AND fadh.distribution_id is null
AND faadj.transaction_header_id = faret.transaction_header_id_in
AND faadj.source_type_code = 'RETIREMENT'
AND faadj.adjustment_type = adj_type
AND faadj.debit_credit_flag = 'DR'
AND faadj.set_of_books_id = RET.set_of_books_id
AND faadj.asset_id = RET.asset_id
AND faadj.book_type_code = RET.book
AND faret.set_of_books_id = RET.set_of_books_id
AND faret.retirement_id = RET.retirement_id;
select transaction_header_id_out
into h_th_id_out
from fa_retirements
where retirement_id = h_retirement_id;
adj_row.last_update_date := today;
adj_row.selection_mode := fa_std_types.FA_AJ_SINGLE;
adj_row.selection_thid := 0;
adj_row.selection_retid := 0;
SELECT category_id
INTO h_category_id
FROM fa_asset_history
WHERE asset_id = RET.asset_id
AND date_ineffective is null;
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);
should be inserted for SORP. The previous value from the
adjustment table must not be taken into account as it has
been already reversed due to the code in FAGIAR.
*/
if FA_CACHE_PKG.fazcbc_record.sorp_enabled_flag = 'Y'
and adj_row.adjustment_type = 'EXPENSE'
and h_exp_adj_amount_sorp <> 0 then
if not FA_SORP_UTIL_PVT.create_sorp_neutral_acct (
p_amount => h_exp_adj_amount_sorp,
p_reversal => 'N',
p_adj => adj_row,
p_created_by => NULL,
p_creation_date => NULL,
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);
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);
should be inserted for SORP. The previous value from the
adjustment table must not be taken into account as it has
been already reversed due to the code in FAGIAR.
*/
if FA_CACHE_PKG.fazcbc_record.sorp_enabled_flag = 'Y'
and adj_row.adjustment_type = 'EXPENSE'
and h_exp_adj_amount_sorp <> 0 then
if not FA_SORP_UTIL_PVT.create_sorp_neutral_acct (
p_amount => h_exp_adj_amount_sorp,
p_reversal => 'N',
p_adj => adj_row,
p_created_by => NULL,
p_creation_date => NULL,
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);
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 PERIOD_NUM , PERIOD_NAME, PERIOD_COUNTER
INTO h_rpdnum, h_rpdname, h_pd_counter
FROM FA_DEPRN_PERIODS fadp
WHERE RET.date_effective
between fadp.period_open_date and
nvl(fadp.period_close_date,
RET.date_effective)
AND fadp.book_type_code = h_book;
SELECT fads.deprn_reserve, fads.bonus_deprn_reserve, fads.reval_reserve,
nvl(fads.prior_fy_expense, 0), nvl(fads.ytd_deprn, 0),
nvl(fads.impairment_reserve, 0)
INTO h_deprn_reserve, h_bonus_deprn_reserve, h_reval_reserve,
h_prior_fy_expense, h_ytd_deprn,
h_impairment_reserve
FROM fa_deprn_summary fads, fa_deprn_periods fadp
WHERE fads.asset_id = h_asset_id
AND fads.book_type_Code = h_book
AND fads.period_counter = fadp.period_counter
AND fadp.period_counter =
(select MAX(DP.PERIOD_COUNTER)
FROM FA_DEPRN_PERIODS DP, FA_DEPRN_SUMMARY DS
WHERE DP.BOOK_TYPE_CODE = h_book
AND DP.PERIOD_COUNTER = DS.PERIOD_COUNTER
AND DP.PERIOD_COUNTER < h_pd_counter
AND DS.BOOK_TYPE_CODE = h_book
AND DS.ASSET_ID = h_asset_id)
AND FADP.BOOK_TYPE_CODE = h_book;
SELECT fads.deprn_reserve, fads.bonus_deprn_reserve, fads.reval_reserve,
nvl(fads.prior_fy_expense, 0), nvl(fads.ytd_deprn, 0),
nvl(fads.impairment_reserve, 0)
INTO h_deprn_reserve, h_bonus_deprn_reserve, h_reval_reserve,
h_prior_fy_expense, h_ytd_deprn,
h_impairment_reserve
FROM fa_mc_deprn_summary fads, fa_deprn_periods fadp
WHERE fads.asset_id = h_asset_id
AND fads.book_type_Code = h_book
AND fads.period_counter = fadp.period_counter
AND fads.set_of_books_id = ret.set_of_books_id
AND fadp.period_counter =
(select MAX(DP.PERIOD_COUNTER)
FROM FA_DEPRN_PERIODS DP, FA_MC_DEPRN_SUMMARY DS
WHERE DP.BOOK_TYPE_CODE = h_book
AND DP.PERIOD_COUNTER = DS.PERIOD_COUNTER
AND DP.PERIOD_COUNTER < h_pd_counter
AND DS.set_of_books_id = ret.set_of_books_id
AND DS.BOOK_TYPE_CODE = h_book
AND DS.ASSET_ID = h_asset_id)
AND FADP.BOOK_TYPE_CODE = h_book;
SELECT SUM(DECODE(faadj.adjustment_type, 'RESERVE',
DECODE(faadj.debit_credit_flag, 'DR',
-1 * faadj.adjustment_amount,
faadj.adjustment_amount),
faadj.adjustment_amount))
INTO h_tot_deprn_adj
FROM fa_adjustments faadj
WHERE faadj.asset_id = RET.asset_id
AND faadj.book_type_code = RET.book
AND faadj.source_type_code = 'RETIREMENT'
AND faadj.adjustment_type in ('RESERVE', 'EXPENSE', 'REVAL EXPENSE')
AND faadj.period_counter_created = h_pd_counter
AND faadj.transaction_header_id <> RET.th_id_in
GROUP BY faadj.asset_id;
SELECT SUM(DECODE(faadj.adjustment_type, 'RESERVE',
DECODE(faadj.debit_credit_flag, 'DR',
-1 * faadj.adjustment_amount,
faadj.adjustment_amount),
faadj.adjustment_amount))
INTO h_tot_deprn_adj
FROM fa_mc_adjustments faadj
WHERE faadj.asset_id = RET.asset_id
AND faadj.book_type_code = RET.book
AND faadj.source_type_code = 'RETIREMENT'
AND faadj.adjustment_type in ('RESERVE', 'EXPENSE', 'REVAL EXPENSE')
AND faadj.period_counter_created = h_pd_counter
AND faadj.set_of_books_id = ret.set_of_books_id
AND faadj.transaction_header_id <> RET.th_id_in
GROUP BY faadj.asset_id;
SELECT SUM(DECODE(faadj.adjustment_type, 'BONUS RESERVE',
DECODE(faadj.debit_credit_flag, 'DR',
-1 * faadj.adjustment_amount,
faadj.adjustment_amount),
faadj.adjustment_amount))
INTO h_tot_bonus_deprn_adj
FROM fa_adjustments faadj
WHERE faadj.asset_id = RET.asset_id
AND faadj.book_type_code = RET.book
AND faadj.source_type_code = 'RETIREMENT'
AND faadj.adjustment_type in ('BONUS RESERVE', 'BONUS EXPENSE')
AND faadj.period_counter_created = h_pd_counter
AND faadj.transaction_header_id <> RET.th_id_in
GROUP BY faadj.asset_id;
SELECT SUM(DECODE(faadj.adjustment_type, 'BONUS RESERVE',
DECODE(faadj.debit_credit_flag, 'DR',
-1 * faadj.adjustment_amount,
faadj.adjustment_amount),
faadj.adjustment_amount))
INTO h_tot_bonus_deprn_adj
FROM fa_mc_adjustments faadj
WHERE faadj.asset_id = RET.asset_id
AND faadj.book_type_code = RET.book
AND faadj.source_type_code = 'RETIREMENT'
AND faadj.adjustment_type in ('BONUS RESERVE', 'BONUS EXPENSE')
AND faadj.period_counter_created = h_pd_counter
AND faadj.set_of_books_id = ret.set_of_books_id
AND faadj.transaction_header_id <> RET.th_id_in
GROUP BY faadj.asset_id;
SELECT SUM(DECODE(faadj.adjustment_type, 'IMPAIR RESERVE',
DECODE(faadj.debit_credit_flag, 'DR',
-1 * faadj.adjustment_amount,
faadj.adjustment_amount),
faadj.adjustment_amount))
INTO h_tot_impairment_adj
FROM fa_adjustments faadj
WHERE faadj.asset_id = RET.asset_id
AND faadj.book_type_code = RET.book
AND faadj.source_type_code = 'RETIREMENT'
AND faadj.adjustment_type in ('IMPAIR RESERVE', 'IMPAIR EXPENSE')
AND faadj.period_counter_created = h_pd_counter
AND faadj.transaction_header_id <> RET.th_id_in
GROUP BY faadj.asset_id;
SELECT SUM(DECODE(faadj.adjustment_type, 'IMPAIR RESERVE',
DECODE(faadj.debit_credit_flag, 'DR',
-1 * faadj.adjustment_amount,
faadj.adjustment_amount),
faadj.adjustment_amount))
INTO h_tot_impairment_adj
FROM fa_mc_adjustments faadj
WHERE faadj.asset_id = RET.asset_id
AND faadj.book_type_code = RET.book
AND faadj.source_type_code = 'RETIREMENT'
AND faadj.adjustment_type in ('IMPAIR RESERVE', 'IMPAIR EXPENSE')
AND faadj.period_counter_created = h_pd_counter
AND faadj.set_of_books_id = ret.set_of_books_id
AND faadj.transaction_header_id <> RET.th_id_in
GROUP BY faadj.asset_id;
SELECT SUM(DECODE(faadj.adjustment_type, 'REVAL RESERVE',
DECODE(faadj.debit_credit_flag, 'DR',
-1 * faadj.adjustment_amount,
faadj.adjustment_amount),
-1 * faadj.adjustment_amount))
INTO h_tot_reval_adj
FROM fa_adjustments faadj
WHERE faadj.asset_id = RET.asset_id
AND faadj.book_type_code = RET.book
AND faadj.source_type_code = decode(RET.wip_asset,1,'CIP RETIREMENT','RETIREMENT')
AND faadj.adjustment_type in ('REVAL RESERVE', 'REVAL AMORT')
AND faadj.period_counter_created = h_pd_counter
AND faadj.transaction_header_id <> RET.th_id_in
GROUP BY faadj.asset_id;
SELECT SUM(DECODE(faadj.adjustment_type, 'REVAL RESERVE',
DECODE(faadj.debit_credit_flag, 'DR',
-1 * faadj.adjustment_amount,
faadj.adjustment_amount),
-1 * faadj.adjustment_amount))
INTO h_tot_reval_adj
FROM fa_mc_adjustments faadj
WHERE faadj.asset_id = RET.asset_id
AND faadj.book_type_code = RET.book
AND faadj.source_type_code = decode(RET.wip_asset,1,'CIP RETIREMENT','RETIREMENT') /*12768930 */
AND faadj.adjustment_type in ('REVAL RESERVE', 'REVAL AMORT')
AND faadj.period_counter_created = h_pd_counter
AND faadj.set_of_books_id = ret.set_of_books_id
AND faadj.transaction_header_id <> RET.th_id_in
GROUP BY faadj.asset_id;
X_LAST_UPDATE_DATE date := sysdate;
X_last_updated_by number := -1;
X_last_update_login number := -1;
SELECT distribution_id
FROM fa_distribution_history
WHERE book_type_code = ret.book
and asset_id = ret.asset_id
and transaction_header_id_out = h_th_id_out;
adj_row.selection_thid := h_th_id_out ;
select transaction_header_id_out
into h_th_id_out
from fa_retirements
where retirement_id = ret.retirement_id;
select transaction_header_id_out
into h_th_id_out
from fa_mc_retirements
where retirement_id = ret.retirement_id
and set_of_books_id = ret.set_of_books_id;
adj_row.selection_thid := h_th_id_out ;
adj_row.last_update_date := today;
adj_row.selection_thid := 0;
adj_row.selection_thid := h_th_id_out ;
SELECT SUM(DECODE(DEBIT_CREDIT_FLAG,'DR',ADJUSTMENT_AMOUNT,-1*ADJUSTMENT_AMOUNT)),
max(CODE_COMBINATION_ID),
max(ADJUSTMENT_TYPE),
max(SOURCE_TYPE_CODE),
max(TRANSACTION_HEADER_ID)
INTO H_MISC_COST,
h_ccid,
h_adj_type,
h_source_type_code,
h_th_id
FROM FA_ADJUSTMENTS
WHERE ASSET_ID = RET.ASSET_id
and book_type_code = ret.book
and distribution_id = h_dist_id
AND adjustment_type in ('COST', 'CIP COST')
group by distribution_id;
SELECT SUM(DECODE(DEBIT_CREDIT_FLAG,'DR',ADJUSTMENT_AMOUNT,-1*ADJUSTMENT_AMOUNT)),
max(CODE_COMBINATION_ID),
max(ADJUSTMENT_TYPE),
max(SOURCE_TYPE_CODE),
max(TRANSACTION_HEADER_ID)
INTO H_MISC_COST,
h_ccid,
h_adj_type,
h_source_type_code,
h_th_id
FROM fa_mc_adjustments
WHERE ASSET_ID = RET.ASSET_id
and book_type_code = ret.book
and distribution_id = h_dist_id
and set_of_books_id = ret.set_of_books_id
AND adjustment_type in ('COST', 'CIP COST')
group by distribution_id;
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.selection_mode := fa_std_types.FA_AJ_ACTIVE_PARTIAL;
adj_row.adjustment_amount := adj_row.amount_inserted;
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_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;
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);
SELECT group_asset_id
INTO l_cur_grp_id
FROM fa_books
WHERE asset_id = ret.asset_id
AND book_type_code = ret.book
AND transaction_header_id_out IS NULL;
SELECT group_asset_id
INTO l_cur_grp_id
FROM fa_mc_books
WHERE asset_id = ret.asset_id
AND book_type_code = ret.book
AND set_of_books_id = ret.set_of_books_id
AND transaction_header_id_out IS NULL;
select sum(decode(debit_credit_flag, 'CR', -1, 1) * adjustment_amount)
into l_reserve_ret
from fa_adjustments
where asset_id = ret.asset_id
and book_type_code = ret.book
and source_type_code = 'RETIREMENT'
and adjustment_type = 'RESERVE'
and transaction_header_id = ret.th_id_in;
select sum(decode(debit_credit_flag, 'CR', -1, 1) * adjustment_amount)
into l_reserve_ret
from fa_mc_adjustments
where asset_id = ret.asset_id
and book_type_code = ret.book
and source_type_code = 'RETIREMENT'
and adjustment_type = 'RESERVE'
and transaction_header_id = ret.th_id_in
and set_of_books_id = ret.set_of_books_id;
select nvl(sum(decode(debit_credit_flag, 'DR', adjustment_amount, -adjustment_amount)), 0)
into l_exp_ret
from fa_adjustments
where asset_id = ret.asset_id
and book_type_code = ret.book
and source_type_code = 'RETIREMENT'
and adjustment_type = 'EXPENSE'
and transaction_header_id = ret.th_id_in;
select nvl(sum(decode(debit_credit_flag, 'DR', adjustment_amount, -adjustment_amount)), 0)
into l_exp_ret
from fa_mc_adjustments
where asset_id = ret.asset_id
and book_type_code = ret.book
and source_type_code = 'RETIREMENT'
and adjustment_type = 'EXPENSE'
and transaction_header_id = ret.th_id_in
and set_of_books_id = ret.set_of_books_id;
select distribution_id,
code_combination_id,
units_assigned
from fa_distribution_history
where asset_id = l_asset_id
and date_ineffective is null
order by distribution_id;
select nvl(l_old_cost,0)
+
decode(p_tbl_ret(i).debit_credit_flag,l_dr_cr_bal, -1*p_tbl_ret(i).adjustment_amount,
p_tbl_ret(i).adjustment_amount)
into l_new_cost
from dual;
SELECT DISTRIBUTION_ID,
CODE_COMBINATION_ID,
TRANSACTION_UNITS
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;
X_LAST_UPDATE_DATE date := sysdate;
X_last_updated_by number := -1;
X_last_update_login number := -1;
select transaction_header_id_out,transaction_type_code
into h_th_id_out,h_th_type_code
from fa_retirements rt,
fa_transaction_headers fth
where retirement_id = ret.retirement_id
and rt.transaction_header_id_IN = fth.transaction_header_id
and rt.asset_id = fth.asset_id
and rt.book_type_code = fth.book_type_code;
adj_row.last_update_date := today;
adj_row.selection_mode := fa_std_types.FA_AJ_SINGLE;
adj_row.selection_thid := 0;
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;
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);