[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select PERIOD_COUNTER,IMPAIRMENT_DATE,
impairment_amount,
decode(SPLIT_IMPAIR_FLAG,'N','NO','Y','YES','NO'),
DECODE(INSTR (p_impairment_id, '.'),'0',impairment_amount,
DECODE (SUBSTR (p_impairment_id, -1,LENGTH (p_impairment_id)),
'1', split1_loss_amount,
'2', split2_loss_amount,
'3', split3_loss_amount
)) split_impairm_amount
from fa_ITF_impairments
where asset_id = p_asset_id and
impairment_id=fa_sorp_process_imp_id_fn(p_impairment_id) and
book_type_code=p_book_type_code;
select * from
fa_books
where book_type_code = p_book_type_code and
asset_id = p_asset_id and
transaction_header_id_out = (select transaction_header_id
from
fa_transaction_headers
where MASS_TRANSACTION_ID = fa_sorp_process_imp_id_fn(p_impairment_id));
select cp.period_num
, fy.fiscal_year
from fa_fiscal_year fy
, fa_calendar_periods cp
where cp.calendar_type = fa_cache_pkg.fazcbc_record.deprn_calendar
and fy.fiscal_year_name = fa_cache_pkg.fazcbc_record.fiscal_year_name
and cp.start_date between fy.start_date and fy.end_date
and c_start_date between cp.start_date and cp.end_date;
select period_num,fiscal_year,PERIOD_COUNTER
from fa_deprn_periods
where book_type_code = p_book_type_code
and PERIOD_CLOSE_DATE is null;
select SUM(decode(ADJUSTMENT_TYPE,'RESERVE',adjustment_amount,0)),
sum(decode(ADJUSTMENT_TYPE,'EXPENSE',adjustment_amount,0)),
PERIOD_COUNTER_CREATED
from fa_adjustments
where asset_id = p_asset_id AND
book_type_code = p_book_type_code AND
PERIOD_COUNTER_CREATED >= c_period_counter_impaired and
SOURCE_TYPE_CODE='REVALUATION'
group by PERIOD_COUNTER_CREATED;
select sum(REVAL_DEPRN_EXPENSE),count(*)
from fa_deprn_summary
where asset_id = p_asset_id and
PERIOD_COUNTER >= c_period_counter_impaired
and book_type_code=p_book_type_code;
SELECT DECODE (NVL (INSTR (p_impairment_id, '.'), 0),
0, TO_NUMBER (p_impairment_id),
TO_NUMBER (SUBSTR (p_impairment_id,
1,
(LENGTH (p_impairment_id) - 2)
)
)
)
FROM DUAL;
SELECT deprn_sum.book_type_code, deprn_sum.asset_id,
(deprn_sum.deprn_amount - nvl(deprn_sum.deprn_adjustment_amount,0)), deprn_sum.deprn_reserve,
deprn_periods.period_name, imp.period_counter_impaired,
DECODE
(INSTR (p_impairment_id, '.'),
'0', ROUND (( ( ( itf.impairment_amount
+ NVL (itf.reval_reserve_adj_amount, 0)
)
* (deprn_sum.deprn_amount - nvl(deprn_sum.deprn_adjustment_amount,0))
)
/ imp.net_book_value
),
2
),
DECODE (SUBSTR (p_impairment_id,
-1,
LENGTH (p_impairment_id)
),
'1', ROUND (( ( ( itf.split1_loss_amount
+ NVL
(itf.split1_reval_reserve,
0
)
)
* (deprn_sum.deprn_amount - nvl(deprn_sum.deprn_adjustment_amount,0))
)
/ imp.net_book_value
),
2
),
'2', ROUND (( ( ( itf.split2_loss_amount
+ NVL
(itf.split2_reval_reserve,
0
)
)
* (deprn_sum.deprn_amount - nvl(deprn_sum.deprn_adjustment_amount,0))
)
/ imp.net_book_value
),
2
),
'3', ROUND (( ( ( itf.split3_loss_amount
+ NVL
(itf.split3_reval_reserve,
0
)
)
* (deprn_sum.deprn_amount - nvl(deprn_sum.deprn_adjustment_amount,0))
)
/ imp.net_book_value
),
2
)
)
) deprn_delta
FROM fa_deprn_summary deprn_sum,
fa_deprn_periods deprn_periods,
fa_impairments imp,
fa_itf_impairments itf
WHERE deprn_sum.book_type_code = deprn_periods.book_type_code
AND deprn_sum.period_counter = deprn_periods.period_counter
AND deprn_sum.period_counter = imp.period_counter_impaired
AND deprn_sum.book_type_code = imp.book_type_code
AND deprn_sum.asset_id = imp.asset_id
AND imp.impairment_id = itf.impairment_id
AND imp.asset_id = itf.asset_id
AND imp.impairment_id =
fa_sorp_process_imp_id_fn (p_impairment_id);
SELECT deprn_sum.book_type_code, deprn_sum.asset_id,
(deprn_sum.deprn_amount - nvl(deprn_sum.deprn_adjustment_amount,0)), deprn_sum.deprn_reserve,
deprn_periods.period_name, imp.period_counter_impaired,
DECODE
(INSTR (p_impairment_id, '.'),
'0', ROUND (( ( ( itf.impairment_amount
- ( NVL (itf.reversed_imp_amt, 0)
+ NVL (itf.reversed_deprn_impact, 0)
)
)
* (deprn_sum.deprn_amount - nvl(deprn_sum.deprn_adjustment_amount,0))
)
/ imp.net_book_value
),
2
),
DECODE (SUBSTR (p_impairment_id,
-1,
LENGTH (p_impairment_id)
),
'1', ROUND
(( ( ( itf.split1_loss_amount
- ( NVL (itf.reversed_imp_amt_s1,
0
)
+ NVL
(itf.reversed_deprn_impact_s1,
0
)
)
)
* (deprn_sum.deprn_amount - nvl(deprn_sum.deprn_adjustment_amount,0))
)
/ imp.net_book_value
),
2
),
'2', ROUND
(( ( ( itf.split2_loss_amount
- ( NVL (itf.reversed_imp_amt_s2,
0
)
+ NVL
(itf.reversed_deprn_impact_s2,
0
)
)
)
* (deprn_sum.deprn_amount - nvl(deprn_sum.deprn_adjustment_amount,0))
)
/ imp.net_book_value
),
2
),
'3', ROUND
(( ( ( itf.split3_loss_amount
- ( NVL (itf.reversed_imp_amt_s3,
0
)
+ NVL
(itf.reversed_deprn_impact_s3,
0
)
)
)
* (deprn_sum.deprn_amount - nvl(deprn_sum.deprn_adjustment_amount,0))
)
/ imp.net_book_value
),
2
)
)
) deprn_delta
FROM fa_deprn_summary deprn_sum,
fa_deprn_periods deprn_periods,
fa_impairments imp,
fa_itf_impairments itf
WHERE deprn_sum.book_type_code = deprn_periods.book_type_code
AND deprn_sum.period_counter = deprn_periods.period_counter
AND deprn_sum.period_counter = imp.period_counter_impaired
AND deprn_sum.book_type_code = imp.book_type_code
AND deprn_sum.asset_id = imp.asset_id
AND imp.impairment_id = itf.impairment_id
AND imp.asset_id = itf.asset_id
AND imp.impairment_id =
fa_sorp_process_imp_id_fn (p_impairment_id);
SELECT deprn_sum.book_type_code, deprn_sum.asset_id,
(deprn_sum.deprn_amount - nvl(deprn_sum.deprn_adjustment_amount,0)), deprn_sum.deprn_reserve,
deprn_periods.period_name, imp.period_counter_impaired,
ROUND (( ((p_amount) * (deprn_sum.deprn_amount - nvl(deprn_sum.deprn_adjustment_amount,0)))
/ imp.net_book_value
),
2
) deprn_delta
FROM fa_deprn_summary deprn_sum,
fa_deprn_periods deprn_periods,
fa_impairments imp,
fa_itf_impairments itf
WHERE deprn_sum.book_type_code = deprn_periods.book_type_code
AND deprn_sum.period_counter = deprn_periods.period_counter
AND deprn_sum.period_counter = imp.period_counter_impaired
AND deprn_sum.book_type_code = imp.book_type_code
AND deprn_sum.asset_id = imp.asset_id
AND imp.impairment_id = itf.impairment_id
AND imp.asset_id = itf.asset_id
AND imp.impairment_id =
fa_sorp_process_imp_id_fn (p_impairment_id);
SELECT COUNT (1)
INTO v_deprn_cnt
FROM fa_deprn_summary deprn_sum, fa_deprn_periods deprn_periods
WHERE deprn_sum.book_type_code = deprn_periods.book_type_code
AND deprn_sum.period_counter = deprn_periods.period_counter
AND deprn_periods.period_close_date IS NOT NULL
AND deprn_sum.book_type_code = p_book_type_code
AND deprn_sum.asset_id = p_asset_id
AND deprn_sum.period_counter > v_period_counter_impaired;
SELECT DECODE (INSTR (p_impairment_id, '.'),
'0', ROUND (NVL (itf.impairment_amount, 0), 2),
DECODE (SUBSTR (p_impairment_id,
-1,
LENGTH (p_impairment_id)
),
'1', ROUND (NVL (itf.split1_loss_amount, 0),
2),
'2', ROUND (NVL (itf.split2_loss_amount, 0),
2),
'3', ROUND (NVL (itf.split3_loss_amount, 0),
2)
)
) impair_loss_amount,
DECODE (INSTR (p_impairment_id, '.'),
'0', ROUND (NVL (itf.reval_reserve_adj_amount, 0), 2),
DECODE (SUBSTR (p_impairment_id,
-1,
LENGTH (p_impairment_id)
),
'1', ROUND (NVL (itf.split1_reval_reserve, 0),
2
),
'2', ROUND (NVL (itf.split2_reval_reserve, 0),
2
),
'3', ROUND (NVL (itf.split3_reval_reserve, 0),
2
)
)
) reserve_adj_amount
INTO v_impair_loss_amount,
v_reserve_adj_amount
FROM fa_itf_impairments itf
WHERE impairment_id = fa_sorp_process_imp_id_fn (p_impairment_id);
SELECT COUNT (1)
INTO v_deprn_cnt
FROM fa_deprn_summary deprn_sum, fa_deprn_periods deprn_periods
WHERE deprn_sum.book_type_code = deprn_periods.book_type_code
AND deprn_sum.period_counter = deprn_periods.period_counter
AND deprn_periods.period_close_date IS NOT NULL
AND deprn_sum.book_type_code = p_book_type_code
AND deprn_sum.asset_id = p_asset_id
AND deprn_sum.period_counter > v_period_counter_impaired;
SELECT COUNT (1)
INTO v_deprn_cnt
FROM fa_deprn_summary deprn_sum, fa_deprn_periods deprn_periods
WHERE deprn_sum.book_type_code = deprn_periods.book_type_code
AND deprn_sum.period_counter = deprn_periods.period_counter
AND deprn_periods.period_close_date IS NOT NULL
AND deprn_sum.book_type_code = p_book_type_code
AND deprn_sum.asset_id = p_asset_id
AND deprn_sum.period_counter > v_period_counter_impaired;
SELECT impairment_id, reval_reserve_impact_flag, impair_loss_impact,
imp_deprn_effect, NVL (impair_loss_acct, 'N'),
calc_imp_reverse_amt, calc_imp_reverse_deprn_effect,
rsv_reverse_amt, rsv_reverse_deprn_effect
FROM fa_sorp_link_reval_itf
WHERE request_id = p_request_id
AND asset_id = p_asset_id
AND run_mode = 'RUN';
SELECT impairment_id, split_number, book_type_code, asset_id,
IMP_LOSS_AMOUNT,decode(SPLIT_IMPAIR_FLAG,'N','NO','Y','YES','NO')
/*Bug# 7392015-Added to columns for prorate */
,unused_imp_loss_amount, mass_reval_id,
( unused_imp_loss_amount
- fa_imp_deprn_eff_fn (impairment_id,
book_type_code,
asset_id,
'C',
NULL
)
) imp_impact, -- Impairtment Amount -- Deprn Impact due to impairment amount
fa_imp_deprn_eff_fn (impairment_id,
book_type_code,
asset_id,
'C',
NULL
) imp_deprn_effect, -- Deprn Impact due to impairment amount
calc_imp_loss_amount,
( calc_imp_loss_amount
- fa_imp_deprn_eff_fn (impairment_id,
book_type_code,
asset_id,
'I',
NULL
)
) calc_imp_impact, -- (I/E Impairment amount - depreciation impact on the I/E)
fa_imp_deprn_eff_fn (impairment_id,
book_type_code,
asset_id,
'I',
NULL
) calc_imp_deprn_effect, --(depreciation impact on the I/E)
impair_loss_acct, NULL category_id,
reval_reserve_impact_flag, NVL (reval_rsv_adj_amount, 0),
impair_class, reason
FROM fa_sorp_asset_link_reval_v
WHERE asset_id = p_asset_id
AND book_type_code = p_book_type_code
AND mass_reval_id = p_mass_reval_id
AND imp_include_flag = 'Y'
ORDER BY impairment_date DESC,
impair_class,
unused_imp_loss_amount,
imp_impact;
SELECT *
FROM (SELECT impairment_id, split_number, book_type_code,
asset_id,unused_imp_loss_amount, mass_reval_id,
( unused_imp_loss_amount
- fa_imp_deprn_eff_fn (impairment_id,
book_type_code,
asset_id,
'C',
NULL
)
) imp_impact, -- Impairtment Amount -- Deprn Impact due to impairment amount
fa_imp_deprn_eff_fn
(impairment_id,
book_type_code,
asset_id,
'C',
NULL
) imp_deprn_effect, -- Deprn Impact due to impairment amount
calc_imp_loss_amount,
( calc_imp_loss_amount
- fa_imp_deprn_eff_fn (impairment_id,
book_type_code,
asset_id,
'I',
NULL
)
) calc_imp_impact, -- (I/E Impairment amount - depreciation impact on the I/E)
fa_imp_deprn_eff_fn
(impairment_id,
book_type_code,
asset_id,
'I',
NULL
) calc_imp_deprn_effect, --(depreciation impact on the I/E)
NVL (reval_rsv_adj_amount, 0), impair_loss_acct,
category_id, reval_reserve_impact_flag,
impair_class, reason
FROM fa_sorp_cat_link_reval_v
WHERE asset_id = p_asset_id
AND book_type_code = p_book_type_code
AND mass_reval_id = p_mass_reval_id
AND category_id = p_category_id
AND unused_imp_loss_amount > 0
ORDER BY impairment_date DESC,
impair_class,
unused_imp_loss_amount,
imp_impact)
WHERE ROWNUM = 1;
INSERT INTO fa_sorp_link_reval_itf
(request_id, mass_reval_id, asset_id,
category_id, book_type_code,
impairment_id,
split_number, impairment_loss_amount,
impair_loss_impact, impair_loss_acct,
imp_deprn_effect, run_mode,
reval_reserve_impact_flag, impair_class_type,
reason,
calc_imp_amount,
calc_imp_deprn_effect,
reval_rsv_adj_amount,
reval_rsv_adj_deprn_effect,
calc_imp_reverse_amt,
calc_imp_reverse_deprn_effect,
rsv_reverse_amt,
rsv_reverse_deprn_effect,
reval_gain,
created_by,
creation_date
)
VALUES (p_request_id, v_mass_reval_id, v_asset_id,
v_category_id, v_book_type_code,
fa_sorp_process_imp_id_fn (v_impairment_id),
v_split_number, v_reverse_imp_amt,
v_imp_impact, v_impair_loss_acct,
v_imp_deprn_effect, p_run_mode,
v_reval_reserve_impact_flag, v_impair_class,
v_reason,
v_calc_imp_impact,
v_calc_imp_deprn_effect,
v_reval_rsv_adj_amount,
l_reval_rsv_deprn_effect,
v_calc_imp_impact, -- calc_imp_reverse_amt - calculated impairment amount to be reversed
v_calc_imp_deprn_effect, -- calc_imp_reverse_deprn_effect - calulated deprn impact due to calculated impairment amount
l_reverse_rsv_amount, -- rsv_reverse_amt - Reval Reserve Adjustment amount to be reversed
l_reval_rsv_deprn_effect, -- rsv_reverse_deprn_effect - deprn impact due to Reval Reserve Adjustment amount to be reversed
l_reval_gain, -- reval_gain - Revaluation Gain
'-1',
SYSDATE
);
INSERT INTO fa_sorp_link_reval_itf
(request_id, mass_reval_id, asset_id,
category_id, book_type_code,
impairment_id,
split_number, impairment_loss_amount,
impair_loss_impact, impair_loss_acct,
imp_deprn_effect, run_mode,
reval_reserve_impact_flag, impair_class_type,
reason,
calc_imp_amount,
calc_imp_deprn_effect,
reval_rsv_adj_amount,
reval_rsv_adj_deprn_effect,
calc_imp_reverse_amt,
calc_imp_reverse_deprn_effect,
rsv_reverse_amt,
rsv_reverse_deprn_effect,
reval_gain,
created_by,
creation_date
)
VALUES (p_request_id, v_mass_reval_id, v_asset_id,
v_category_id, v_book_type_code,
fa_sorp_process_imp_id_fn (v_impairment_id),
v_split_number, v_reverse_imp_amt,
v_imp_impact, v_impair_loss_acct,
v_imp_deprn_effect, p_run_mode,
v_reval_reserve_impact_flag, v_impair_class,
v_reason,
v_calc_imp_impact,
v_calc_imp_deprn_effect,
v_reval_rsv_adj_amount,
l_reval_rsv_deprn_effect,
l_new_reverse_amt, -- calc_imp_reverse_amt - calculated impairment amount to be reversed
v_temp_imp_deprn_effect, -- calc_imp_reverse_deprn_effect - calulated deprn impact due to calculated impairment amount
l_reverse_rsv_amount, -- rsv_reverse_amt - Reval Reserve Adjustment amount to be reversed
l_reval_rsv_deprn_effect, -- rsv_reverse_deprn_effect - deprn impact due to Reval Reserve Adjustment amount to be reversed
l_reval_gain, -- reval_gain - Revaluation Gain
'-1',
SYSDATE
);
INSERT INTO fa_sorp_link_reval_itf
(request_id, mass_reval_id, asset_id,
category_id, book_type_code,
impairment_id,
split_number, impairment_loss_amount,
impair_loss_impact, impair_loss_acct,
imp_deprn_effect, run_mode,
reval_reserve_impact_flag, impair_class_type,
reason,
calc_imp_amount,
calc_imp_deprn_effect,
reval_rsv_adj_amount,
reval_rsv_adj_deprn_effect,
calc_imp_reverse_amt,
calc_imp_reverse_deprn_effect,
rsv_reverse_amt,
rsv_reverse_deprn_effect,
reval_gain, created_by,
creation_date
)
VALUES (p_request_id, v_mass_reval_id, v_asset_id,
v_category_id, v_book_type_code,
fa_sorp_process_imp_id_fn (v_impairment_id),
v_split_number, v_reverse_imp_amt,
v_imp_impact, v_impair_loss_acct,
v_imp_deprn_effect, p_run_mode,
v_reval_reserve_impact_flag, v_impair_class,
v_reason,
v_calc_imp_impact,
v_calc_imp_deprn_effect,
v_reval_rsv_adj_amount,
l_reval_rsv_deprn_effect,
v_calc_imp_impact, -- calc_imp_reverse_amt - calculated impairment amount to be reversed
v_temp_imp_deprn_effect, -- calc_imp_reverse_deprn_effect - calulated deprn impact due to calculated impairment amount
l_reverse_rsv_amount,-- rsv_reverse_amt - Reval Reserve Adjustment amount to be reversed
l_reval_rsv_deprn_effect, -- rsv_reverse_deprn_effect - deprn impact due to Reval Reserve Adjustment amount to be reversed
l_reval_gain, -- reval_gain - Revaluation Gain
'-1',
SYSDATE
);
INSERT INTO fa_sorp_link_reval_itf
(request_id, mass_reval_id, asset_id,
category_id, book_type_code,
impairment_id,
split_number, impairment_loss_amount,
impair_loss_impact, impair_loss_acct,
imp_deprn_effect, run_mode,
reval_reserve_impact_flag, impair_class_type,
reason,
calc_imp_amount,
calc_imp_deprn_effect,
reval_rsv_adj_amount,
reval_rsv_adj_deprn_effect,
calc_imp_reverse_amt,
calc_imp_reverse_deprn_effect,
rsv_reverse_amt,
rsv_reverse_deprn_effect,
reval_gain,
created_by,
creation_date
)
VALUES (p_request_id, v_mass_reval_id, v_asset_id,
v_category_id, v_book_type_code,
fa_sorp_process_imp_id_fn (v_impairment_id),
v_split_number, v_reverse_imp_amt,
v_imp_impact, v_impair_loss_acct,
v_imp_deprn_effect, p_run_mode,
v_reval_reserve_impact_flag, v_impair_class,
v_reason,
v_calc_imp_impact,
v_calc_imp_deprn_effect,
v_reval_rsv_adj_amount,
l_reval_rsv_deprn_effect,
l_new_reverse_amt, -- calc_imp_reverse_amt - calculated impairment amount to be reversed
v_temp_imp_deprn_effect, -- calc_imp_reverse_deprn_effect - calulated deprn impact due to calculated impairment amount
l_reverse_rsv_amount,-- rsv_reverse_amt - Reval Reserve Adjustment amount to be reversed
l_reval_rsv_deprn_effect, -- rsv_reverse_deprn_effect - deprn impact due to Reval Reserve Adjustment amount to be reversed
l_reval_gain, -- reval_gain - Revaluation Gain
'-1',
SYSDATE
);
/* Procedure updates FA_ITF_IMPAIRMENTS with reversed amounts
*/
PROCEDURE fa_imp_itf_upd (
p_request_id NUMBER,
p_book_type_code VARCHAR2,
p_asset_id NUMBER,
p_last_updated_by NUMBER,
p_last_update_date DATE
, p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
IS
CURSOR c_reval_link_itf_cur
IS
SELECT impairment_id, split_number, calc_imp_reverse_amt,
calc_imp_reverse_deprn_effect, rsv_reverse_amt,
rsv_reverse_deprn_effect
FROM fa_sorp_link_reval_itf
WHERE request_id = p_request_id
AND book_type_code = p_book_type_code
AND asset_id = p_asset_id
AND run_mode = 'RUN';
UPDATE fa_itf_impairments
SET reversed_imp_amt = nvl(reversed_imp_amt,0)+v_calc_imp_reverse_amt,
reversed_deprn_impact = nvl(reversed_deprn_impact,0)+v_calc_imp_rev_deprn_effect,
last_updated_by = p_last_updated_by,
last_update_date = p_last_update_date
WHERE impairment_id = v_impairment_id;
UPDATE fa_itf_impairments
SET reversed_reval_amt = nvl(reversed_reval_amt,0)+v_rsv_reverse_amt,
reversed_reval_impact = nvl(reversed_reval_impact,0)+v_rsv_reverse_deprn_effect,
last_updated_by = p_last_updated_by,
last_update_date = p_last_update_date
WHERE impairment_id = v_impairment_id;
UPDATE fa_itf_impairments
SET reversed_imp_amt_s1 = nvl(reversed_imp_amt_s1,0)+v_calc_imp_reverse_amt,
reversed_deprn_impact_s1 = nvl(reversed_deprn_impact_s1,0)+v_calc_imp_rev_deprn_effect,
last_updated_by = p_last_updated_by,
last_update_date = p_last_update_date
WHERE impairment_id = v_impairment_id;
UPDATE fa_itf_impairments
SET reversed_reval_amt_s1 = nvl(reversed_reval_amt_s1,0)+v_rsv_reverse_amt,
reversed_reval_impact_s1 = nvl(reversed_reval_impact_s1,0)+v_rsv_reverse_deprn_effect,
last_updated_by = p_last_updated_by,
last_update_date = p_last_update_date
WHERE impairment_id = v_impairment_id;
UPDATE fa_itf_impairments
SET reversed_imp_amt_s2 = nvl(reversed_imp_amt_s2,0)+v_calc_imp_reverse_amt,
reversed_deprn_impact_s2 = nvl(reversed_deprn_impact_s2,0)+v_calc_imp_rev_deprn_effect,
last_updated_by = p_last_updated_by,
last_update_date = p_last_update_date
WHERE impairment_id = v_impairment_id;
UPDATE fa_itf_impairments
SET reversed_reval_amt_s2 = nvl(reversed_reval_amt_s2,0)+v_rsv_reverse_amt,
reversed_reval_impact_s2 = nvl(reversed_reval_impact_s2,0)+v_rsv_reverse_deprn_effect,
last_updated_by = p_last_updated_by,
last_update_date = p_last_update_date
WHERE impairment_id = v_impairment_id;
UPDATE fa_itf_impairments
SET reversed_imp_amt_s3 = nvl(reversed_imp_amt_s3,0)+v_calc_imp_reverse_amt,
reversed_deprn_impact_s3 = nvl(reversed_deprn_impact_s3,0)+v_calc_imp_rev_deprn_effect,
last_updated_by = p_last_updated_by,
last_update_date = p_last_update_date
WHERE impairment_id = v_impairment_id;
UPDATE fa_itf_impairments
SET reversed_reval_amt_s3 = nvl(reversed_reval_amt_s3,0)+v_rsv_reverse_amt,
reversed_reval_impact_s3 = nvl(reversed_reval_impact_s3,0)+v_rsv_reverse_deprn_effect,
last_updated_by = p_last_updated_by,
last_update_date = p_last_update_date
WHERE impairment_id = v_impairment_id;
SELECT adj.transaction_header_id,
sum(adj.adjustment_amount),
adj.period_counter_created,
fbs.reval_loss_balance
FROM
FA_ADJUSTMENTS adj,
fa_books_summary fbs,
fa_transaction_headers fth
WHERE adj.asset_id = p_asset_hdr_rec.asset_id
AND adj.book_type_code = p_asset_hdr_rec.book_type_code
AND adj.adjustment_type in ('REVAL LOSS','REVAL RESERVE')
AND adj.debit_credit_flag = 'DR'
AND adj.asset_id = fth.asset_id
AND adj.book_type_code = fth.book_type_code
AND adj.transaction_header_id = fth.transaction_header_id
AND fth.transaction_key = 'RL'
AND adj.period_counter_created = fbs.period_counter
AND fbs.asset_id = adj.asset_id
AND fbs.book_type_code = adj.book_type_code
AND fbs.reval_loss_balance <> 0
AND (( fbs.period_counter <= c_imp_pc and p_flag = 'P') or
( fbs.period_counter > c_imp_pc and p_flag = 'A') or
( p_flag = 'G'))
GROUP by adj.transaction_header_id,
adj.period_counter_created,
fbs.reval_loss_balance
ORDER by 1;
SELECT adj.transaction_header_id,
sum(adj.adjustment_amount),
adj.period_counter_created,
fbs.reval_loss_balance
FROM
FA_MC_ADJUSTMENTS adj,
fa_mc_books_summary fbs,
fa_transaction_headers fth
WHERE adj.asset_id = p_asset_hdr_rec.asset_id
AND adj.book_type_code = p_asset_hdr_rec.book_type_code
AND adj.adjustment_type in ('REVAL LOSS','REVAL RESERVE')
AND adj.debit_credit_flag = 'DR'
AND adj.asset_id = fth.asset_id
AND adj.book_type_code = fth.book_type_code
AND adj.transaction_header_id = fth.transaction_header_id
AND fth.transaction_key = 'RL'
AND adj.set_of_books_id = p_set_of_books_id
AND adj.period_counter_created = fbs.period_counter
AND fbs.asset_id = adj.asset_id
AND fbs.book_type_code = adj.book_type_code
AND fbs.set_of_books_id = adj.set_of_books_id
AND fbs.reval_loss_balance <> 0
AND (( fbs.period_counter <= c_imp_pc and p_flag = 'P') or
( fbs.period_counter > c_imp_pc and p_flag = 'A') or
( p_flag = 'G'))
GROUP by adj.transaction_header_id,
adj.period_counter_created,
fbs.reval_loss_balance
ORDER by 1;
SELECT sum(adj.adjustment_amount)
FROM
FA_ADJUSTMENTS adj
WHERE adj.asset_id = p_asset_hdr_rec.asset_id
AND adj.book_type_code = p_asset_hdr_rec.book_type_code
AND adj.adjustment_type = 'REVAL LOSS'
AND adj.debit_credit_flag = 'DR'
AND adj.period_counter_created = c_loss_period_counter
AND adj.transaction_header_id > c_th_id;
SELECT sum(adj.adjustment_amount)
FROM
FA_MC_ADJUSTMENTS adj
WHERE adj.asset_id = p_asset_hdr_rec.asset_id
AND adj.book_type_code = p_asset_hdr_rec.book_type_code
AND adj.adjustment_type = 'REVAL LOSS'
AND adj.debit_credit_flag = 'DR'
AND adj.set_of_books_id = p_set_of_books_id
AND adj.period_counter_created = c_loss_period_counter
AND adj.transaction_header_id > c_th_id;
SELECT period_counter
FROM FA_DEPRN_PERIODS
WHERE book_type_code = p_asset_hdr_rec.book_type_code
AND period_name in
(SELECT fa_period
FROM fa_sorp_asset_link_reval_v
WHERE asset_id = p_asset_hdr_rec.asset_id
AND book_type_code = p_asset_hdr_rec.book_type_code
AND mass_reval_id = p_reval_options_rec.mass_reval_id);
SELECT period_counter
FROM FA_DEPRN_PERIODS fdp,
fa_sorp_asset_link_reval_v salrv
WHERE fdp.book_type_code = p_asset_hdr_rec.book_type_code
AND salrv.asset_id = p_asset_hdr_rec.asset_id
AND salrv.book_type_code = p_asset_hdr_rec.book_type_code
AND salrv.mass_reval_id = p_reval_options_rec.mass_reval_id
AND salrv.impairment_date between fdp.calendar_period_open_Date and fdp.calendar_period_close_Date
AND rownum = 1
order by salrv.impairment_date ;
update fa_books_summary set reval_loss_balance = reval_loss_balance - (l_net_reval_loss + l_deprn_exp)
where asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and period_counter >= c_loss_period_counter ;
update fa_mc_books_summary set reval_loss_balance = reval_loss_balance - (l_net_reval_loss + l_deprn_exp)
where asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and set_of_books_id = p_set_of_books_id
and period_counter >= c_loss_period_counter ;
SELECT book_type_code,asset_id,recoverable_cost,salvage_value,adjusted_recoverable_cost,adjusted_cost,old_adjusted_cost,
formula_factor,rate_Adjustment_factor,eofy_reserve,deprn_method_code,life_in_months,adjusted_rate,production_capacity,
adjusted_capacity,bonus_rule,ceiling_name,reval_amortization_basis,date_placed_in_service,prorate_date,original_deprn_start_date,
tracking_method,period_counter_life_complete,annual_deprn_rounding_flag
FROM FA_BOOKS
WHERE TRANSACTION_HEADER_ID_OUT = p_th_id;
SELECT book_type_code,asset_id,recoverable_cost,salvage_value,adjusted_recoverable_cost,adjusted_cost,old_adjusted_cost,
formula_factor,rate_Adjustment_factor,eofy_reserve,deprn_method_code,life_in_months,adjusted_rate,production_capacity,
adjusted_capacity,bonus_rule,ceiling_name,reval_amortization_basis,date_placed_in_service,prorate_date,original_deprn_start_date,
tracking_method,period_counter_life_complete,annual_deprn_rounding_flag
FROM FA_MC_BOOKS
WHERE TRANSACTION_HEADER_ID_OUT = p_th_id
AND SET_OF_BOOKS_ID = p_set_of_books_id;
SELECT book_type_code,asset_id,recoverable_cost,salvage_value,adjusted_recoverable_cost,adjusted_cost,old_adjusted_cost,
formula_factor,rate_Adjustment_factor,eofy_reserve,deprn_method_code,life_in_months,adjusted_rate,production_capacity,
adjusted_capacity,bonus_rule,ceiling_name,reval_amortization_basis,date_placed_in_service,prorate_date,ORIGINAL_DEPRN_START_DATE,
tracking_method,period_counter_life_complete,annual_deprn_rounding_flag
FROM FA_BOOKS
WHERE TRANSACTION_HEADER_ID_IN = p_th_id;
SELECT book_type_code,asset_id,recoverable_cost,salvage_value,adjusted_recoverable_cost,adjusted_cost,old_adjusted_cost,
formula_factor,rate_Adjustment_factor,eofy_reserve,deprn_method_code,life_in_months,adjusted_rate,production_capacity,
adjusted_capacity,bonus_rule,ceiling_name,reval_amortization_basis,date_placed_in_service,prorate_date,ORIGINAL_DEPRN_START_DATE,
tracking_method,period_counter_life_complete,annual_deprn_rounding_flag
FROM FA_MC_BOOKS
WHERE TRANSACTION_HEADER_ID_IN = p_th_id
AND SET_OF_BOOKS_ID = p_set_of_books_id;
Select fiscal_year,period_num
from FA_DEPRN_PERIODS
where book_type_code = p_asset_hdr_rec.book_type_code
and period_counter = p_loss_pc;