[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT a.asset_id,
a.asset_category_id,
a.asset_type,
NVL(FND_DATE.CANONICAL_TO_DATE(a.global_attribute1),b.date_placed_in_service) revaluation_start_date,
b.date_placed_in_service,
b.cost cost,
rownum counter
FROM
fa_category_books c
, fa_additions a
, fa_books b
, fa_transaction_headers th
WHERE b.book_type_code = p_book_type_code
AND b.cost <> 0
AND b.global_attribute1 = 'Y'
AND b.date_ineffective IS NULL
AND b.transaction_header_id_out IS NULL
AND a.asset_id = b.asset_id
AND c.category_id = a.asset_category_id
AND c.book_type_code = p_book_type_code
AND c.global_attribute1 = 'Y'
AND th.book_type_code = p_book_type_code
AND th.asset_id = a.asset_id
-- AND th.transaction_type_code in ('ADDITION','CIP ADDITION')
AND (th.transaction_type_code = 'ADDITION'
OR (th.transaction_type_code = 'CIP ADDITION' AND a.asset_type <> 'CAPITALIZED'))
-- Bug 12737275 Start
-- AND th.date_effective < g_period_open_date
-- Bug 12737275 End
AND th.transaction_header_id <= b.transaction_header_id_in
AND ((g_revalue_cip_assets_flag is NULL AND a.asset_type = 'CAPITALIZED')
OR (g_revalue_cip_assets_flag is NOT NULL))
AND not exists (select 'X' from FA_TRANSACTION_HEADERS th_2
where th_2.book_type_code = p_book_type_code
AND th_2.asset_id = a.asset_id
AND th_2.transaction_type_code = 'REVALUATION'
AND th_2.date_effective >= g_period_open_date)
ORDER BY a.asset_category_id;
g_step := 'DELETE_RULES';
DELETE FROM fa_mass_revaluation_rules
Where mass_reval_id = p_mass_reval_id;
SELECT calendar_period_open_date
, calendar_period_close_date
, fiscal_year
, period_counter
, period_open_date
INTO g_current_period_from_date1
, g_current_period_to_date1
, g_current_fiscal_year
, g_current_period_counter1
, g_period_open_date
FROM fa_deprn_periods
WHERE book_type_code = p_book_type_code
AND period_close_date IS NULL;
SELECT deprn_calendar
INTO g_calendar_type
FROM fa_book_controls
WHERE book_type_code = p_book_type_code;
SELECT number_per_fiscal_year
INTO g_number_per_fy
FROM fa_calendar_types
WHERE calendar_type = g_calendar_type;
SELECT nvl(rr.revalue_cip_assets_flag,'N') revalue_cip_assets_flag,
nvl(rr.default_reval_fully_rsvd_flag,'NO') reval_fully_rsvd_flag,
rr.default_life_extension_factor life_extension_factor,
rr.default_life_extension_ceiling life_extension_ceiling
INTO
g_revalue_cip_assets_flag,
g_reval_fully_rsvd_flag,
g_life_extension_factor,
g_life_extension_ceiling
FROM fa_mass_revaluations rr
WHERE rr.mass_reval_id = p_mass_reval_id;
SELECT period_num
INTO g_period_num
FROM fa_calendar_periods
WHERE calendar_type = g_calendar_type
AND start_date = g_current_period_from_date1;
SELECT start_date,end_date
INTO g_current_period_from_date2,
g_current_period_to_date2
FROM fa_calendar_periods
WHERE calendar_type = g_calendar_type
AND period_num = decode(g_period_num,1,g_number_per_fy,g_period_num-1)
AND end_date = g_current_period_from_date1 - 1;
SELECT price_index_id
INTO g_price_index
FROM fa_category_book_defaults a, fa_price_indexes b
WHERE a.book_type_code = p_book_type_code
AND a.category_id = p_category_id
AND p_date_placed_in_service >= a.start_dpis
AND p_date_placed_in_service <= NVL(a.end_dpis,p_date_placed_in_service)
AND a.price_index_name = b.price_index_name;
SELECT decode(g_country_code, 'CL',(start_date-1), end_date)
INTO l_close_date
FROM fa_calendar_periods
WHERE calendar_type = g_calendar_type
AND trunc(p_date) BETWEEN start_date AND end_date;
SELECT price_index_value
INTO p_index_value
FROM fa_price_index_values
WHERE price_index_id = g_price_index
AND p_period_date BETWEEN from_date AND nvl(to_date,p_period_date);
SELECT min(dp.period_counter)
INTO dummy
FROM fa_deprn_periods dp
, fa_transaction_headers th
, fa_asset_history ah
WHERE ah.asset_id = th.asset_id
AND dp.book_type_code = th.book_type_code
AND th.transaction_header_id >= ah.transaction_header_id_in
AND th.transaction_header_id < nvl(ah.transaction_header_id_out, th.transaction_header_id + 1)
AND th.date_effective between dp.period_open_date
and nvl(dp.period_close_date, th.date_effective)
AND dp.book_type_code = p_book_type_code
AND ah.asset_id = p_asset_id;
SELECT cost_retired,date_retired
INTO p_amount,p_date_retired
FROM fa_transaction_headers b,
fa_retirements a
WHERE a.book_type_code = p_book_type_code
AND a.asset_id = p_asset_id
AND a.status = 'DELETED'
AND b.book_type_code = a.book_type_code
AND b.asset_id = a.asset_id
AND b.transaction_header_id = a.transaction_header_id_out
AND b.transaction_date_entered BETWEEN p_date_ini AND
p_date_end
------------------------------------------------------
-- BUG 4345686. Added to filter out assets retired and
-- reinstated in the same period. We will consider
-- those assets as if they were not retired at all
-- for the purposes of reval rules generator.
------------------------------------------------------
AND b.transaction_type_code = 'REINSTATEMENT'
AND b.transaction_date_entered <> a.date_retired;
g_char := 'values inserted into fa_mass_revaluation_rules:';
INSERT INTO fa_mass_revaluation_rules
(mass_reval_id
, category_id
, asset_id
, reval_percent
, override_defaults_flag
, revalue_cip_assets_flag
, reval_fully_rsvd_flag
, life_extension_factor
, life_extension_ceiling
, last_updated_by
, last_update_date)
VALUES(p_mass_reval_id
, null
, p_asset_id
, p_revaluation_rate
, 'NO'
, g_revalue_cip_assets_flag
, g_reval_fully_rsvd_flag
, g_life_extension_factor
, g_life_extension_ceiling
, fnd_global.user_id
, sysdate);
PROCEDURE update_control_tables (p_mass_reval_id IN NUMBER) IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CONTROL_TABLES';
g_char := 'Procedure update_control_tables';
UPDATE fa_book_controls
SET global_attribute2 = g_current_period_counter1,
global_attribute3 = p_mass_reval_id
WHERE book_type_code = p_book_type_code;
UPDATE fa_mass_revaluations
SET global_attribute1 = g_current_period_counter1
WHERE mass_reval_id = p_mass_reval_id;
END update_control_tables;
update_control_tables (p_mass_reval_id);