[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* to be deleted from spec and body */
PROCEDURE log(p_mesg IN VARCHAR2)IS
BEGIN
IF FND_PROFILE.VALUE('IGI_DEBUG_OPTION') = 'Y' THEN
fnd_file.put_line(fnd_file.log, p_mesg);
SELECT 'X'
FROM fa_books
WHERE book_type_code = p_book
AND asset_id = p_asset_id
AND period_counter_fully_reserved IS NOT NULL;
SELECT adjusted_cost
INTO l_adjusted_cost
FROM igi_iac_asset_balances
WHERE book_type_code = p_book
AND asset_id = p_asset_id
AND period_counter = (SELECT MAX(period_counter)
FROM igi_iac_asset_balances
WHERE book_type_code = p_book
AND asset_id = p_asset_id);
SELECT adjusted_cost
INTO l_adjusted_cost
FROM igi_iac_asset_balances
WHERE book_type_code = p_book
AND asset_id = p_asset_id
AND period_counter = p_period_counter;
SELECT adjusted_cost
INTO l_adjusted_cost
FROM igi_iac_asset_balances
WHERE book_type_code = p_book
AND asset_id = p_asset_id
AND period_counter = (SELECT MAX(period_counter)
FROM igi_iac_asset_balances
WHERE book_type_code = p_book
AND asset_id = p_asset_id);
PROCEDURE insert_exceptions(p_revaluation_id IN igi_iac_revaluations.revaluation_id%TYPE,
p_asset_id IN igi_iac_reval_asset_rules.asset_id%TYPE,
p_category_id IN igi_iac_reval_categories.category_id%TYPE,
p_book_type_code IN igi_iac_revaluations.book_type_code%TYPE,
p_exception_type IN VARCHAR2
)
IS
l_user_id NUMBER;
igi_iac_debug_pkg.debug_other_msg(g_error_level,g_path||'insert_exceptions',FALSE);
igi_iac_debug_pkg.debug_other_msg(g_error_level,g_path||'insert_exceptions',FALSE);
INSERT INTO igi_iac_exceptions (revaluation_id,
asset_id,
category_id,
book_type_code,
exception_message,
created_by,
creation_date,
last_Update_date,
last_updated_by)
SELECT p_revaluation_id,
p_asset_id,
p_category_id,
p_book_type_code,
l_str,
l_user_id,
sysdate,
sysdate,
l_user_id
FROM sys.dual
WHERE NOT EXISTS(SELECT 'X'
FROM igi_iac_exceptions
WHERE revaluation_id = p_revaluation_id
AND book_type_code = p_book_type_code
AND category_id = p_category_id
AND asset_id = p_asset_id);
SELECT rc.category_id,
DECODE(cb.allow_indexed_reval_flag,'Y','O',
DECODE(cb.allow_prof_reval_flag,'Y','P')) reval_type,
NVL(cb.allow_indexed_reval_flag, 'N')allow_indexed_reval_flag,
NVL(cb.allow_prof_reval_flag, 'N')allow_prof_reval_flag,
rc.revaluation_id
FROM igi_iac_reval_categories rc, igi_iac_category_books cb
WHERE rc.category_id = cb.category_id
AND rc.book_type_code = cb.book_type_code
AND rc.book_type_code = p_book_type_code
AND rc.revaluation_id = p_revaluation_id
AND rc.select_category ='Y'
AND ( NVL(cb.allow_indexed_reval_flag,'N') = 'Y'
OR NVL(cb.allow_prof_reval_flag, 'N')='Y');
SELECT a.asset_id,
b.cost
FROM fa_additions a,
fa_books b
WHERE a.asset_id = b.asset_id
AND b.book_type_code = p_book_type_code
AND a.asset_category_id = p_cat_id
AND a.asset_type <> 'CIP' -- bug 3416315
AND b.transaction_header_id_out IS NULL
AND b.date_placed_in_service <= p_revaluation_date
AND NOT EXISTS(SELECT 'X'
FROM igi_iac_revaluation_rates rr,
igi_iac_revaluations r
WHERE r.revaluation_id = rr.revaluation_id
AND rr.asset_id = a.asset_id
AND r.book_type_code = rr.book_type_code
AND r.book_type_code = p_book_type_code
AND r.status IN ('PREVIEWED','COMPLETED','UPDATED','FAILED_RUN')
AND rr.period_counter = p_period_counter
AND rr.reval_type = 'O'
AND p_allow_indexed = 'Y'
AND p_allow_prof = 'N')
AND NOT EXISTS(SELECT 'X'
FROM igi_iac_reval_asset_rules ar
WHERE a.asset_id = ar.asset_id
AND ar. revaluation_id = p_revaluation_id)
AND NOT EXISTS(SELECT 'X'
FROM fa_transaction_headers t,
fa_retirements r
WHERE t.book_type_code = b.book_type_code
AND t.asset_id = a.asset_id
AND t.transaction_header_id = r.transaction_header_id_in
AND r.transaction_header_id_out IS NULL
AND t.transaction_type_code = 'FULL RETIREMENT'
);
SELECT period_counter
FROM igi_iac_revaluation_rates
WHERE asset_id = p_asset_id
AND latest_record = 'Y'
AND book_type_code = p_book_type_code;
SELECT date_placed_in_service
FROM fa_books
WHERE asset_id = p_asset_id
AND book_type_code = p_book_type_code
AND date_ineffective IS NULL;
insert_exceptions(p_revaluation_id,
l_asset_id(l_loop_count),
r_get_categories.category_id,
p_book_type_code,
'NEGATIVE_ASSET'
);
igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'get_assets','insert_exceptions called');
insert_exceptions(p_revaluation_id,
l_asset_id(l_loop_count),
r_get_categories.category_id,
p_book_type_code,
'PERIOD_INDEX'
);
igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'get_assets','insert_exceptions called');
igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'get_assets','inserting into igi_iac_reval_asset_rules');
INSERT INTO igi_iac_reval_asset_rules
(REVALUATION_ID,
BOOK_TYPE_CODE,
CATEGORY_ID,
ASSET_ID,
REVALUATION_FACTOR,
REVALUATION_TYPE,
NEW_COST,
CURRENT_COST,
SELECTED_FOR_REVAL_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
VALUES(p_revaluation_id,
p_book_type_code,
r_get_categories.category_id,
l_asset_id(l_loop_count),
l_reval_factor,
r_get_categories.reval_type,
l_new_cost,
l_current_cost,
'Y',
l_user_id,
sysdate,
sysdate,
l_user_id);
igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'get_assets','end of insert for this asset');
END IF; -- insert asset