参考自表单FAXASSET/Program Unit:INQUIRY_BOOKS.post_query
CREATE OR REPLACE PROCEDURE bhsc_aremlyf(p_book_type_code IN VARCHAR2,
p_asset_id IN NUMBER,
p_period_name IN VARCHAR2 DEFAULT NULL,
x_remaining_life OUT NUMBER,
x_remaining_life_dpis OUT NUMBER) IS
l_period_counter NUMBER;
v_num_per_fiscal_year NUMBER;
v_num_per_fiscal_year_prorate NUMBER;
l_prorate_date DATE;
l_min_cpod DATE;
v_depr_when_acquired_flag VARCHAR2(3);
CURSOR cur_remlyf_1(c_book_type_code IN VARCHAR2,
c_asset_id IN NUMBER,
c_period_counter IN NUMBER) IS
SELECT fab.life_in_months -
((fafy1.fiscal_year * 12 + fcp1.period_num) -
(fafy2.fiscal_year * 12 + fcp2.period_num)) from_prorate,
fab.life_in_months -
((fafy1.fiscal_year * 12 + fcp1.period_num) -
(fafy4.fiscal_year * 12 + fcp4.period_num)) from_dpis
FROM fa_books fab,
fa_calendar_periods fcp1, -- open
fa_calendar_periods fcp2, -- prorate
fa_calendar_periods fcp3, -- deprn_start
fa_calendar_periods fcp4, -- DPIS
fa_book_controls fabc,
fa_deprn_periods fdp,
fa_fiscal_year fafy1,
fa_fiscal_year fafy2,
fa_fiscal_year fafy3,
fa_fiscal_year fafy4
WHERE fab.asset_id = c_asset_id
AND fab.book_type_code = c_book_type_code
AND fab.transaction_header_id_out IS NULL
AND fabc.book_type_code = fab.book_type_code
AND fdp.period_counter = c_period_counter
AND fdp.book_type_code = fab.book_type_code
AND fcp1.calendar_type =
decode(fab.conversion_date,
NULL,
fabc.prorate_calendar,
fabc.deprn_calendar)
AND fdp.calendar_period_open_date BETWEEN fcp1.start_date AND
fcp1.end_date
AND fcp2.calendar_type = fabc.prorate_calendar
AND fab.prorate_date BETWEEN fcp2.start_date AND fcp2.end_date
AND fcp3.calendar_type = fabc.deprn_calendar
AND fab.deprn_start_date BETWEEN fcp3.start_date AND fcp3.end_date
AND fcp4.calendar_type = fabc.deprn_calendar
AND fab.date_placed_in_service BETWEEN fcp4.start_date AND
fcp4.end_date
AND fabc.fiscal_year_name = fafy1.fiscal_year_name
AND fcp1.end_date BETWEEN fafy1.start_date AND fafy1.end_date
AND fabc.fiscal_year_name = fafy2.fiscal_year_name
AND fcp2.end_date BETWEEN fafy2.start_date AND fafy2.end_date
AND fabc.fiscal_year_name = fafy3.fiscal_year_name
AND fcp3.end_date BETWEEN fafy3.start_date AND fafy3.end_date
AND fabc.fiscal_year_name = fafy4.fiscal_year_name
AND fcp4.end_date BETWEEN fafy4.start_date AND fafy4.end_date;
CURSOR cur_remlyf_2(c_book_type_code IN VARCHAR2,
c_asset_id IN NUMBER,
c_period_counter IN NUMBER) IS
SELECT fab.life_in_months -
floor(months_between(fdp.calendar_period_close_date,
fab.prorate_date)) from_prorate,
fab.life_in_months -
floor(months_between(fdp.calendar_period_close_date,
fab.date_placed_in_service)) from_dpis
FROM fa_books fab, fa_deprn_periods fdp
WHERE fab.book_type_code = c_book_type_code
AND fdp.book_type_code = c_book_type_code
AND fab.asset_id = c_asset_id
AND fab.date_ineffective IS NULL
AND fdp.period_counter = c_period_counter;
CURSOR cur_remlyf_5(c_book_type_code IN VARCHAR2,
c_asset_id IN NUMBER,
c_period_counter IN NUMBER) IS
SELECT ceil(months_between(fcp1.end_date, fcp2.start_date)) from_prorate,
ceil(months_between(fcp1.end_date, fcp4.start_date)) from_dpis
FROM fa_books fab,
fa_calendar_periods fcp1, -- open
fa_calendar_periods fcp2, -- prorate
fa_calendar_periods fcp4, -- DPIS
fa_book_controls fabc,
fa_deprn_periods fdp,
fa_fiscal_year fafy1,
fa_fiscal_year fafy2,
fa_fiscal_year fafy4,
fa_calendar_types fact
WHERE fab.asset_id = c_asset_id
AND fab.book_type_code = c_book_type_code
AND fab.transaction_header_id_out IS NULL
AND fabc.book_type_code = fab.book_type_code
AND fact.calendar_type = fabc.deprn_calendar
AND fdp.period_counter = c_period_counter - 1
AND fdp.book_type_code = fab.book_type_code
AND fcp1.calendar_type = fabc.deprn_calendar
AND fdp.calendar_period_open_date BETWEEN fcp1.start_date AND
fcp1.end_date
AND fcp2.calendar_type = fabc.prorate_calendar
AND fab.prorate_date BETWEEN fcp2.start_date AND fcp2.end_date
AND fcp4.calendar_type = fabc.deprn_calendar
AND fab.date_placed_in_service BETWEEN fcp4.start_date AND
fcp4.end_date
AND fabc.fiscal_year_name = fafy1.fiscal_year_name
AND fcp1.end_date BETWEEN fafy1.start_date AND fafy1.end_date
AND fabc.fiscal_year_name = fafy2.fiscal_year_name
AND fcp2.end_date BETWEEN fafy2.start_date AND fafy2.end_date
AND fabc.fiscal_year_name = fafy4.fiscal_year_name
AND fcp4.end_date BETWEEN fafy4.start_date AND fafy4.end_date;
BEGIN
FOR inquiry_books IN (SELECT fbbcv.type,
fbbcv.prorate_date,
fbbcv.sob_id,
fbbcv.life_in_months,
fbbcv.asset_id,
fbbcv.book_type_code,
fb.conversion_date
FROM fa_books_book_controls_v fbbcv, fa_books fb
WHERE fbbcv.asset_id = p_asset_id
AND fbbcv.book_type_code = p_book_type_code
AND fbbcv.transaction_header_id_out IS NULL
AND fb.asset_id = p_asset_id
AND fb.book_type_code = p_book_type_code
AND fb.transaction_header_id_out IS NULL) LOOP
/* -- updated by yusuf 12-jan-2023
SELECT MAX(dp.period_counter)
INTO l_period_counter
FROM fa_deprn_periods dp
WHERE dp.book_type_code = inquiry_books.book_type_code
AND dp.period_name = nvl(p_period_name, dp.period_name);*/
SELECT MAX(fdp.period_counter)
INTO l_period_counter
FROM fa_calendar_periods fcp,
fa_book_controls fbc,
fa_deprn_periods fdp
WHERE fcp.period_name = NVL(p_period_name, fcp.period_name)
AND fcp.calendar_type =
decode(inquiry_books.conversion_date,
NULL,
fbc.prorate_calendar,
fbc.deprn_calendar)
AND fbc.book_type_code = inquiry_books.book_type_code
AND fdp.book_type_code = inquiry_books.book_type_code
AND fdp.calendar_period_open_date <= fcp.end_date;
-- end update
IF (inquiry_books.type = 'Primary') THEN
SELECT prorate_date
INTO l_prorate_date
FROM fa_books
WHERE book_type_code = inquiry_books.book_type_code
AND asset_id = inquiry_books.asset_id
AND date_ineffective IS NULL;
ELSE
SELECT prorate_date
INTO l_prorate_date
FROM fa_mc_books
WHERE book_type_code = inquiry_books.book_type_code
AND asset_id = inquiry_books.asset_id
AND set_of_books_id = inquiry_books.sob_id
AND date_ineffective IS NULL;
END IF;
SELECT MIN(calendar_period_open_date)
INTO l_min_cpod
FROM fa_deprn_periods
WHERE book_type_code = inquiry_books.book_type_code;
SELECT number_per_fiscal_year
INTO v_num_per_fiscal_year
FROM fa_calendar_types
WHERE calendar_type =
(SELECT decode(fab.conversion_date,
NULL,
fabc.deprn_calendar,
fabc.prorate_calendar)
FROM fa_book_controls fabc, fa_books fab
WHERE fabc.book_type_code = inquiry_books.book_type_code
AND fab.asset_id = inquiry_books.asset_id
AND fab.book_type_code = fabc.book_type_code
AND fab.transaction_header_id_out IS NULL);
SELECT number_per_fiscal_year
INTO v_num_per_fiscal_year_prorate
FROM fa_calendar_types
WHERE calendar_type =
(SELECT fabc.prorate_calendar
FROM fa_book_controls fabc, fa_books fab
WHERE fabc.book_type_code = inquiry_books.book_type_code
AND fab.asset_id = inquiry_books.asset_id
AND fab.book_type_code = fabc.book_type_code
AND fab.transaction_header_id_out IS NULL);
SELECT fct.depr_when_acquired_flag
INTO v_depr_when_acquired_flag
FROM fa_convention_types fct, fa_books fb
WHERE fb.book_type_code = inquiry_books.book_type_code
AND fb.prorate_convention_code = fct.prorate_convention_code
AND fb.asset_id = inquiry_books.asset_id
AND fb.transaction_header_id_out IS NULL;
IF (v_num_per_fiscal_year = 12) THEN
IF (l_prorate_date <= l_min_cpod) OR
(v_depr_when_acquired_flag = 'YES') THEN
IF (((v_num_per_fiscal_year_prorate = 12) AND
(l_prorate_date < l_min_cpod)) OR
((v_num_per_fiscal_year_prorate = 12) AND
(v_depr_when_acquired_flag = 'YES'))) THEN
OPEN cur_remlyf_1(inquiry_books.book_type_code,
inquiry_books.asset_id,
l_period_counter);
FETCH cur_remlyf_1
INTO x_remaining_life, x_remaining_life_dpis;
CLOSE cur_remlyf_1;
ELSE
OPEN cur_remlyf_2(inquiry_books.book_type_code,
inquiry_books.asset_id,
l_period_counter);
FETCH cur_remlyf_2
INTO x_remaining_life, x_remaining_life_dpis;
CLOSE cur_remlyf_2;
END IF;
ELSIF (v_depr_when_acquired_flag = 'NO') THEN
OPEN cur_remlyf_1(inquiry_books.book_type_code,
inquiry_books.asset_id,
l_period_counter);
FETCH cur_remlyf_1
INTO x_remaining_life, x_remaining_life_dpis;
CLOSE cur_remlyf_1;
ELSE
OPEN cur_remlyf_2(inquiry_books.book_type_code,
inquiry_books.asset_id,
l_period_counter);
FETCH cur_remlyf_2
INTO x_remaining_life, x_remaining_life_dpis;
CLOSE cur_remlyf_2;
END IF;
ELSE
OPEN cur_remlyf_5(inquiry_books.book_type_code,
inquiry_books.asset_id,
l_period_counter);
FETCH cur_remlyf_5
INTO x_remaining_life, x_remaining_life_dpis;
CLOSE cur_remlyf_5;
IF x_remaining_life < 0 THEN
x_remaining_life := inquiry_books.life_in_months;
ELSE
x_remaining_life := inquiry_books.life_in_months -
x_remaining_life;
END IF;
IF x_remaining_life_dpis < 0 THEN
x_remaining_life_dpis := inquiry_books.life_in_months;
ELSE
x_remaining_life_dpis := inquiry_books.life_in_months -
x_remaining_life_dpis;
END IF;
END IF;
x_remaining_life := greatest(x_remaining_life, 0);
x_remaining_life_dpis := greatest(x_remaining_life_dpis, 0);
END LOOP;
END bhsc_qryaremlyf;
使用方法
1、指定截止到某个期间
2、直接查资产在当前的剩余寿命