[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT LOWER (iso_language), UPPER(iso_territory)
FROM fnd_languages
WHERE language_code = NVL (USERENV ('LANG'),'US');
select fy.fiscal_year * ct.NUMBER_PER_FISCAL_YEAR + cp.PERIOD_NUM period_counter
, cp.start_date calendar_period_open_date
, cp.end_date calendar_period_close_date
, cp.period_num period_num
, fy.fiscal_year fiscal_year
from fa_book_controls bc
, fa_fiscal_year fy
, fa_calendar_types ct
, fa_calendar_periods cp
where bc.book_type_code = p_book_type_code
and bc.deprn_calendar = ct.calendar_type
and bc.fiscal_year_name = fy.fiscal_year_name
and ct.fiscal_year_name = bc.fiscal_year_name
and ct.calendar_type = cp.calendar_type
and cp.start_date between fy.start_date and fy.end_date
and bc.last_period_counter + 1 >= fy.fiscal_year * ct.NUMBER_PER_FISCAL_YEAR + cp.PERIOD_NUM
and l_transaction_date between cp.start_date and cp.end_date;
select fy.fiscal_year * ct.NUMBER_PER_FISCAL_YEAR + cp.PERIOD_NUM period_counter
, cp.start_date calendar_period_open_date
, cp.end_date calendar_period_close_date
, cp.period_num period_num
, fy.fiscal_year fiscal_year
from fa_book_controls bc
, fa_mc_book_controls mbc
, fa_fiscal_year fy
, fa_calendar_types ct
, fa_calendar_periods cp
where bc.book_type_code = p_book_type_code
and mbc.book_type_code = p_book_type_code
and mbc.set_of_books_id = l_set_of_books_id
and bc.deprn_calendar = ct.calendar_type
and bc.fiscal_year_name = fy.fiscal_year_name
and ct.fiscal_year_name = bc.fiscal_year_name
and ct.calendar_type = cp.calendar_type
and cp.start_date between fy.start_date and fy.end_date
and bc.last_period_counter + 1 >= fy.fiscal_year * ct.NUMBER_PER_FISCAL_YEAR + cp.PERIOD_NUM
and l_transaction_date between cp.start_date and cp.end_date;
select asset_id
from fa_impairments imp
where imp.book_type_code = p_book_type_code
and imp.request_id = c_request_id
and imp.asset_id is not null
and not exists
(select 'POSTED'
from fa_impairments imp2
where status = 'POSTED'
and ((imp2.asset_id = imp.asset_id) or
imp2.cash_generating_unit_id = (select cash_generating_unit_id
from fa_books fbs
where fbs.asset_id = imp.asset_id
and fbs.book_type_code = imp.book_type_code
and fbs.transaction_header_id_out is null))
and imp2.book_type_code = p_book_type_code
AND PERIOD_COUNTER_IMPAIRED = c_period_counter )
UNION
select bk.asset_id
from fa_impairments imp,
fa_books bk
where bk.cash_generating_unit_id = imp.cash_generating_unit_id
and bk.book_type_code = imp.book_type_code
and imp.book_type_code = p_book_type_code
and imp.request_id = c_request_id
and imp .asset_id is null
and not exists
(select 'POSTED'
from fa_impairments imp2
where status = 'POSTED'
and ((imp2.cash_generating_unit_id = imp.cash_generating_unit_id) or
imp2.asset_id in (select asset_id
from fa_books fbs
where fbs.cash_generating_unit_id = imp.cash_generating_unit_id
and fbs.book_type_code = imp.book_type_code
and fbs.transaction_header_id_out is null))
and imp2.book_type_code = p_book_type_code
and PERIOD_COUNTER_IMPAIRED = c_period_counter );
UPDATE FA_IMPAIRMENTS imp
SET imp.STATUS = l_internal_mode
, imp.REQUEST_ID = l_request_id
, imp.PERIOD_COUNTER_IMPAIRED =
(select nvl(imp.PERIOD_COUNTER_IMPAIRED,
fy.fiscal_year * ct.NUMBER_PER_FISCAL_YEAR + cp.PERIOD_NUM)
from fa_fiscal_year fy
, fa_calendar_types ct
, fa_calendar_periods cp
where fa_cache_pkg.fazcbc_record.deprn_calendar = ct.calendar_type
and fa_cache_pkg.fazcbc_record.fiscal_year_name = fy.fiscal_year_name
and ct.fiscal_year_name = fa_cache_pkg.fazcbc_record.fiscal_year_name
and ct.calendar_type = cp.calendar_type
and cp.start_date between fy.start_date and fy.end_date
and fa_cache_pkg.fazcbc_record.last_period_counter + 1 >=
fy.fiscal_year * ct.NUMBER_PER_FISCAL_YEAR + cp.PERIOD_NUM
and imp.impairment_date between cp.start_date and cp.end_date)
WHERE imp.STATUS = p_mode
-- Bug#7264536 - To handle situation when impairment is posted simultaneously for different books
AND imp.BOOK_TYPE_CODE = p_book_type_code
RETURNING imp.IMPAIRMENT_DATE BULK COLLECT INTO t_imp_date;
UPDATE FA_IMPAIRMENTS imp
SET imp.STATUS = l_internal_mode
, imp.REQUEST_ID = l_request_id
, imp.PERIOD_COUNTER_IMPAIRED =
(select nvl(imp.PERIOD_COUNTER_IMPAIRED,
fy.fiscal_year * ct.NUMBER_PER_FISCAL_YEAR + cp.PERIOD_NUM)
from fa_fiscal_year fy
, fa_calendar_types ct
, fa_calendar_periods cp
where fa_cache_pkg.fazcbc_record.deprn_calendar = ct.calendar_type
and fa_cache_pkg.fazcbc_record.fiscal_year_name = fy.fiscal_year_name
and ct.fiscal_year_name = fa_cache_pkg.fazcbc_record.fiscal_year_name
and ct.calendar_type = cp.calendar_type
and cp.start_date between fy.start_date and fy.end_date
and fa_cache_pkg.fazcbc_record.last_period_counter + 1 >=
fy.fiscal_year * ct.NUMBER_PER_FISCAL_YEAR + cp.PERIOD_NUM
and imp.impairment_date between cp.start_date and cp.end_date)
WHERE imp.impairment_id = p_impairment_id
RETURNING imp.IMPAIRMENT_DATE BULK COLLECT INTO t_imp_date;
SELECT count(IMPAIRMENT_ID)
INTO l_new_count
FROM FA_IMPAIRMENTS
WHERE STATUS = 'NEW'
AND REQUEST_ID is null;
UPDATE FA_MC_IMPAIRMENTS imp
SET imp.STATUS = l_internal_mode
, imp.REQUEST_ID = l_request_id
, imp.PERIOD_COUNTER_IMPAIRED = l_period_rec.PERIOD_COUNTER
WHERE imp.SET_OF_BOOKS_ID = l_set_of_books_id
AND imp.BOOK_TYPE_CODE = p_book_type_code
AND exists (SELECT IMPAIRMENT_ID
FROM FA_IMPAIRMENTS imp2
WHERE imp2.status = l_internal_mode
AND imp2.BOOK_TYPE_CODE = p_book_type_code
AND imp2.impairment_id = IMP.IMPAIRMENT_ID);
UPDATE FA_MC_IMPAIRMENTS imp
SET imp.STATUS = l_internal_mode
, imp.REQUEST_ID = l_request_id
, imp.PERIOD_COUNTER_IMPAIRED = l_period_rec.PERIOD_COUNTER
WHERE imp.SET_OF_BOOKS_ID = l_set_of_books_id
AND imp.BOOK_TYPE_CODE = p_book_type_code
AND exists (SELECT IMPAIRMENT_ID
FROM FA_IMPAIRMENTS imp2
WHERE imp2.status = l_internal_mode
AND imp2.BOOK_TYPE_CODE = p_book_type_code
AND imp2.impairment_id = IMP.IMPAIRMENT_ID);
if not FA_IMPAIRMENT_DELETE_PVT.delete_post(
p_request_id => l_request_id,
p_book_type_code => p_book_type_code,
p_period_rec => l_period_rec,
p_worker_id => 0,
p_mrc_sob_type_code => l_mrc_sob_type_code,
p_set_of_books_id => l_set_of_books_id,
p_calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec) then
raise imp_err;
if not FA_IMPAIRMENT_DELETE_PVT.delete_post(
p_request_id => p_parent_request_id,
p_book_type_code => p_book_type_code,
p_period_rec => l_period_rec,
p_worker_id => p_request_number,
p_mrc_sob_type_code => l_mrc_sob_type_code,
p_set_of_books_id => l_set_of_books_id,
p_calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec) then
raise imp_err;
l_internal_mode := 'DELETED';
UPDATE FA_IMPAIRMENTS
SET STATUS = l_internal_mode
WHERE REQUEST_ID = l_request_id
AND PERIOD_COUNTER_IMPAIRED = l_imp_period_rec.period_counter;
UPDATE FA_IMPAIRMENTS IMP
SET STATUS='RUNNING DEPRN FAILED'
WHERE REQUEST_ID = l_request_id
AND EXISTS
(SELECT 'DUPLICATE RECORD'
FROM FA_ITF_IMPAIRMENTS ITF
WHERE ITF.PERIOD_OF_ADDITION_FLAG = 'F'
AND ITF.IMPAIRMENT_ID = IMP.IMPAIRMENT_ID);
/*8666930 start - There could be more than one set_of_books_id attached.Need to update for all */
FOR l_sob_index in 0..l_sob_tbl.count LOOP
fa_debug_pkg.add(l_calling_fn,'GIRIRAJ', 'inside for', p_log_level_rec => g_log_level_rec);
UPDATE FA_MC_IMPAIRMENTS
SET STATUS = l_internal_mode
WHERE REQUEST_ID = l_request_id
AND PERIOD_COUNTER_IMPAIRED = l_imp_period_rec.period_counter
AND SET_OF_BOOKS_ID = l_set_of_books_id;
UPDATE FA_MC_IMPAIRMENTS IMP
SET STATUS='RUNNING DEPRN FAILED'
WHERE REQUEST_ID = l_request_id
AND SET_OF_BOOKS_ID = l_set_of_books_id
AND IMPAIRMENT_ID IN
(SELECT IMPAIRMENT_ID
FROM FA_MC_ITF_IMPAIRMENTS
WHERE PERIOD_OF_ADDITION_FLAG = 'F'
AND REQUEST_ID = l_request_id
AND SET_OF_BOOKS_ID = l_set_of_books_id);
SELECT curr.precision
FROM fnd_currencies curr
, gl_sets_of_books sob
WHERE sob.set_of_books_id = c_set_of_books_id
AND curr.currency_code = sob.currency_code;
insert into fa_mc_impairments(
IMPAIRMENT_ID
, IMPAIRMENT_NAME
, DESCRIPTION
, REQUEST_ID
, STATUS
, BOOK_TYPE_CODE
, CASH_GENERATING_UNIT_ID
, ASSET_ID
, NET_BOOK_VALUE
, NET_SELLING_PRICE
, VALUE_IN_USE
, GOODWILL_ASSET_ID
, GOODWILL_AMOUNT
, USER_DATE
, IMPAIRMENT_DATE
, PERIOD_COUNTER_IMPAIRED
, IMPAIRMENT_AMOUNT
, DATE_INEFFECTIVE
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, SET_OF_BOOKS_ID
, IMPAIR_CLASS -- Start of Bug 6666666
, REASON
, IMPAIR_LOSS_ACCT
, SPLIT_IMPAIR_FLAG
, SPLIT1_IMPAIR_CLASS
, SPLIT1_REASON
, SPLIT1_PERCENT
, SPLIT1_LOSS_ACCT
, SPLIT2_IMPAIR_CLASS
, SPLIT2_REASON
, SPLIT2_PERCENT
, SPLIT2_LOSS_ACCT
, SPLIT3_IMPAIR_CLASS
, SPLIT3_REASON
, SPLIT3_PERCENT
, SPLIT3_LOSS_ACCT -- End of Bug 6666666
) select IMPAIRMENT_ID
, IMPAIRMENT_NAME
, DESCRIPTION
, p_request_id -- REQUEST_ID
, STATUS
, p_book_type_code -- BOOK_TYPE_CODE
, CASH_GENERATING_UNIT_ID
, ASSET_ID
, round(NET_BOOK_VALUE*l_rate, l_precision) -- NET_BOOK_VALUE
, round(NET_SELLING_PRICE*l_rate, l_precision) -- NET_SELLING_PRICE
, round(VALUE_IN_USE*l_rate, l_precision) -- VALUE_IN_USE
, GOODWILL_ASSET_ID
, round(GOODWILL_AMOUNT*l_rate, l_precision) -- GOODWILL_AMOUNT
, USER_DATE
, IMPAIRMENT_DATE
, PERIOD_COUNTER_IMPAIRED
, round(IMPAIRMENT_AMOUNT*l_rate, l_precision) -- IMPAIRMENT_AMOUNT
, null -- DATE_INEFFECTIVE
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, null -- LAST_UPDATE_LOGIN
, p_set_of_books_id --SET_OF_BOOKS_ID
, IMPAIR_CLASS -- Start of Bug 6666666
, REASON
, IMPAIR_LOSS_ACCT
, SPLIT_IMPAIR_FLAG
, SPLIT1_IMPAIR_CLASS
, SPLIT1_REASON
, SPLIT1_PERCENT
, SPLIT1_LOSS_ACCT
, SPLIT2_IMPAIR_CLASS
, SPLIT2_REASON
, SPLIT2_PERCENT
, SPLIT2_LOSS_ACCT
, SPLIT3_IMPAIR_CLASS
, SPLIT3_REASON
, SPLIT3_PERCENT
, SPLIT3_LOSS_ACCT -- End of Bug 6666666
from fa_impairments -- 8666930 changed to fa_impairments from fa_mc_impairments
where request_id = p_request_id
and book_type_code = p_book_type_code
and PERIOD_COUNTER_IMPAIRED = p_period_rec.period_counter
-- and set_of_books_id = p_set_of_books_id --8666930 commented
;
delete from fa_mc_itf_impairments itmp
where set_of_books_id = p_set_of_books_id
and exists
(select 'Uploaded impairment'
from fa_mc_books bk , fa_mc_impairments imp
where imp.book_type_code = p_book_type_code
and imp.status = l_mode
and imp.request_id = p_request_id
and imp.set_of_books_id = p_set_of_books_id
and bk.book_type_code = p_book_type_code
and bk.set_of_books_id = p_set_of_books_id
and (bk.period_counter_fully_retired is null or bk.adjustment_required_status <> 'NONE')
/* Bug#7581881 Removed condition on fully reserve asset and show warning */
and bk.deprn_start_date <= p_period_rec.calendar_period_close_date
and (imp.asset_id = bk.asset_id or
bk.cash_generating_unit_id = imp.cash_generating_unit_id)
and bk.transaction_header_id_out is null
and itmp.impairment_id = imp.impairment_id);
insert into fa_mc_itf_impairments(
SET_OF_BOOKS_ID
, REQUEST_ID
, IMPAIRMENT_ID
, BOOK_TYPE_CODE
, ASSET_ID
, CASH_GENERATING_UNIT_ID
, GOODWILL_ASSET_FLAG
, ADJUSTED_COST
, PERIOD_COUNTER
, COST
, IMPAIRMENT_AMOUNT
, YTD_IMPAIRMENT
, impairment_reserve
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, IMPAIRMENT_DATE
, WORKER_ID
, PROCESS_ORDER
, IMPAIR_CLASS -- Start of Bug 6666666
, REASON
, IMPAIR_LOSS_ACCT
, SPLIT_IMPAIR_FLAG
, SPLIT1_IMPAIR_CLASS
, SPLIT1_REASON
, SPLIT1_PERCENT
, SPLIT1_LOSS_ACCT
, SPLIT2_IMPAIR_CLASS
, SPLIT2_REASON
, SPLIT2_PERCENT
, SPLIT2_LOSS_ACCT
, SPLIT3_IMPAIR_CLASS
, SPLIT3_REASON
, SPLIT3_PERCENT
, SPLIT3_LOSS_ACCT -- End of Bug 6666666
) select p_set_of_books_id --SET_OF_BOOKS_ID
, p_request_id --REQUEST_ID
, imp.impairment_id --IMPAIRMENT_ID
, p_book_type_code --BOOK_TYPE_CODE
, bk.ASSET_ID --ASSET_ID
, imp.CASH_GENERATING_UNIT_ID --CASH_GENERATING_UNIT_ID
, decode(bk.asset_id,imp.goodwill_asset_id, 'Y', null) --GOODWILL_ASSET_FLAG
, bk.ADJUSTED_COST --ADJUSTED_COST
, p_period_rec.period_counter --PERIOD_COUNTER
, bk.COST --COST
, 0 -- IMPAIRMENT_AMOUNT
, 0 --YTD_IMPAIRMENT
, 0 --impairment_reserve
, p_prev_sysdate --CREATION_DATE
, p_login_id --CREATED_BY
, p_prev_sysdate --LAST_UPDATE_DATE
, p_login_id --LAST_UPDATED_BY
, nvl(imp.impairment_date, p_transaction_date) --IMPAIRMENT_DATE
, 0 --mod(rank() over(order by bk.asset_id), p_total_requests) --WORKER_ID --Bug5736200
, l_process_order --PROCESS_ORDER
, imp.IMPAIR_CLASS -- Start of Bug 6666666
, imp.REASON
, imp.IMPAIR_LOSS_ACCT
, imp.SPLIT_IMPAIR_FLAG
, imp.SPLIT1_IMPAIR_CLASS
, imp.SPLIT1_REASON
, imp.SPLIT1_PERCENT
, imp.SPLIT1_LOSS_ACCT
, imp.SPLIT2_IMPAIR_CLASS
, imp.SPLIT2_REASON
, imp.SPLIT2_PERCENT
, imp.SPLIT2_LOSS_ACCT
, imp.SPLIT3_IMPAIR_CLASS
, imp.SPLIT3_REASON
, imp.SPLIT3_PERCENT
, imp.SPLIT3_LOSS_ACCT -- End of Bug 6666666
from fa_mc_books bk
, fa_mc_impairments imp
where imp.book_type_code = p_book_type_code
and imp.status = l_mode
and imp.request_id = p_request_id
and imp.set_of_books_id = p_set_of_books_id
and bk.book_type_code = p_book_type_code
and (bk.period_counter_fully_retired is null or bk.adjustment_required_status <> 'NONE')
/* Bug#7581881 Removed condition on fully reserve asset and show warning */
and bk.deprn_start_date <= p_period_rec.calendar_period_close_date
and (imp.asset_id = bk.asset_id or
bk.cash_generating_unit_id = imp.cash_generating_unit_id)
and bk.transaction_header_id_out is null
and bk.set_of_books_id = p_set_of_books_id;
delete from fa_mc_itf_impairments itmp
where set_of_books_id = p_set_of_books_id
and exists
(select 'Uploaded impairment'
from fa_mc_books bk , fa_mc_impairments imp
where imp.book_type_code = p_book_type_code
and imp.status = l_mode
and imp.request_id = p_request_id
and imp.set_of_books_id = p_set_of_books_id
and bk.book_type_code = p_book_type_code
and bk.set_of_books_id = p_set_of_books_id
and (bk.period_counter_fully_retired is null or bk.adjustment_required_status <> 'NONE')
/* Bug#7581881 Removed condition on fully reserve asset and show warning */
and bk.deprn_start_date <= p_period_rec.calendar_period_close_date
and imp.goodwill_asset_id = bk.asset_id
and bk.transaction_header_id_out is null
and itmp.impairment_id = imp.impairment_id);
insert into fa_mc_itf_impairments(
SET_OF_BOOKS_ID
, REQUEST_ID
, IMPAIRMENT_ID
, BOOK_TYPE_CODE
, ASSET_ID
, CASH_GENERATING_UNIT_ID
, GOODWILL_ASSET_FLAG
, ADJUSTED_COST
, PERIOD_COUNTER
, COST
, IMPAIRMENT_AMOUNT
, YTD_IMPAIRMENT
, impairment_reserve
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, IMPAIRMENT_DATE
, WORKER_ID
, PROCESS_ORDER
, IMPAIR_CLASS -- Start of Bug 6666666
, REASON
, IMPAIR_LOSS_ACCT
, SPLIT_IMPAIR_FLAG
, SPLIT1_IMPAIR_CLASS
, SPLIT1_REASON
, SPLIT1_PERCENT
, SPLIT1_LOSS_ACCT
, SPLIT2_IMPAIR_CLASS
, SPLIT2_REASON
, SPLIT2_PERCENT
, SPLIT2_LOSS_ACCT
, SPLIT3_IMPAIR_CLASS
, SPLIT3_REASON
, SPLIT3_PERCENT
, SPLIT3_LOSS_ACCT -- End of Bug 6666666
) select
p_set_of_books_id
, p_request_id --REQUEST_ID
, imp.impairment_id --IMPAIRMENT_ID
, p_book_type_code --BOOK_TYPE_CODE
, bk.ASSET_ID --ASSET_ID
, bk.CASH_GENERATING_UNIT_ID --CASH_GENERATING_UNIT_ID
, 'Y' --GOODWILL_ASSET_FLAG
, bk.ADJUSTED_COST --ADJUSTED_COST
, p_period_rec.period_counter --PERIOD_COUNTER
, bk.COST --COST
, imp.GOODWILL_AMOUNT --IMPAIRMENT_AMOUNT
, imp.GOODWILL_AMOUNT --YTD_IMPAIRMENT
, imp.GOODWILL_AMOUNT --impairment_reserve
, p_prev_sysdate --CREATION_DATE
, p_login_id --CREATED_BY
, p_prev_sysdate --LAST_UPDATE_DATE
, p_login_id --LAST_UPDATED_BY
, nvl(imp.impairment_date, p_transaction_date) --IMPAIRMENT_DATE
, 0 --mod(rank() over(order by bk.asset_id), p_total_requests) --WORKER_ID --Bug5736200
, l_process_order --PROCESS_ORDER
, imp.IMPAIR_CLASS -- Start of Bug 6666666
, imp.REASON
, imp.IMPAIR_LOSS_ACCT
, imp.SPLIT_IMPAIR_FLAG
, imp.SPLIT1_IMPAIR_CLASS
, imp.SPLIT1_REASON
, imp.SPLIT1_PERCENT
, imp.SPLIT1_LOSS_ACCT
, imp.SPLIT2_IMPAIR_CLASS
, imp.SPLIT2_REASON
, imp.SPLIT2_PERCENT
, imp.SPLIT2_LOSS_ACCT
, imp.SPLIT3_IMPAIR_CLASS
, imp.SPLIT3_REASON
, imp.SPLIT3_PERCENT
, imp.SPLIT3_LOSS_ACCT -- End of Bug 6666666
from fa_mc_books bk
, fa_mc_impairments imp
where imp.book_type_code = p_book_type_code
and imp.status = l_mode
and imp.request_id = p_request_id
and imp.set_of_books_id = p_set_of_books_id
and bk.book_type_code = p_book_type_code
and bk.set_of_books_id = p_set_of_books_id
and (bk.period_counter_fully_retired is null or bk.adjustment_required_status <> 'NONE')
/* Bug#7581881 Removed condition on fully reserve asset and show warning */
and bk.deprn_start_date <= p_period_rec.calendar_period_close_date
and imp.goodwill_asset_id = bk.asset_id
and bk.transaction_header_id_out is null;
delete from fa_itf_impairments itmp
where exists
(select 'Uploaded impairment'
from fa_books bk , fa_impairments imp
where imp.book_type_code = p_book_type_code
and imp.status = l_mode
and imp.request_id = p_request_id
and bk.book_type_code = p_book_type_code
and (bk.period_counter_fully_retired is null or bk.adjustment_required_status <> 'NONE')
/* Bug#7581881 Removed condition on fully reserve asset and show warning */
and bk.deprn_start_date <= p_period_rec.calendar_period_close_date
and (imp.asset_id = bk.asset_id or
bk.cash_generating_unit_id = imp.cash_generating_unit_id)
and bk.transaction_header_id_out is null
and itmp.impairment_id = imp.impairment_id);
insert into fa_itf_impairments(
REQUEST_ID
, IMPAIRMENT_ID
, BOOK_TYPE_CODE
, ASSET_ID
, CASH_GENERATING_UNIT_ID
, GOODWILL_ASSET_FLAG
, ADJUSTED_COST
, PERIOD_COUNTER
, COST
, IMPAIRMENT_AMOUNT
, YTD_IMPAIRMENT
, impairment_reserve
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, IMPAIRMENT_DATE
, WORKER_ID
, PROCESS_ORDER
, IMPAIR_CLASS -- Start of Bug 6666666
, REASON
, IMPAIR_LOSS_ACCT
, SPLIT_IMPAIR_FLAG
, SPLIT1_IMPAIR_CLASS
, SPLIT1_REASON
, SPLIT1_PERCENT
, SPLIT1_LOSS_ACCT
, SPLIT2_IMPAIR_CLASS
, SPLIT2_REASON
, SPLIT2_PERCENT
, SPLIT2_LOSS_ACCT
, SPLIT3_IMPAIR_CLASS
, SPLIT3_REASON
, SPLIT3_PERCENT
, SPLIT3_LOSS_ACCT -- End of Bug 6666666
) select p_request_id --REQUEST_ID
, imp.impairment_id --IMPAIRMENT_ID
, p_book_type_code --BOOK_TYPE_CODE
, bk.ASSET_ID --ASSET_ID
, imp.CASH_GENERATING_UNIT_ID --CASH_GENERATING_UNIT_ID
, decode(bk.asset_id,imp.goodwill_asset_id, 'Y', null) --GOODWILL_ASSET_FLAG
, bk.ADJUSTED_COST --ADJUSTED_COST
, p_period_rec.period_counter --PERIOD_COUNTER
, bk.COST --COST
, 0 -- IMPAIRMENT_AMOUNT
, 0 --YTD_IMPAIRMENT
, 0 --impairment_reserve
, p_prev_sysdate --CREATION_DATE
, p_login_id --CREATED_BY
, p_prev_sysdate --LAST_UPDATE_DATE
, p_login_id --LAST_UPDATED_BY
, nvl(imp.impairment_date, p_transaction_date) --IMPAIRMENT_DATE
, 0 --mod(rank() over(order by bk.asset_id), p_total_requests) --WORKER_ID --Bug5736200
, l_process_order --PROCESS_ORDER
, imp.IMPAIR_CLASS -- Start of Bug 6666666
, imp.REASON
, imp.IMPAIR_LOSS_ACCT
, imp.SPLIT_IMPAIR_FLAG
, imp.SPLIT1_IMPAIR_CLASS
, imp.SPLIT1_REASON
, imp.SPLIT1_PERCENT
, imp.SPLIT1_LOSS_ACCT
, imp.SPLIT2_IMPAIR_CLASS
, imp.SPLIT2_REASON
, imp.SPLIT2_PERCENT
, imp.SPLIT2_LOSS_ACCT
, imp.SPLIT3_IMPAIR_CLASS
, imp.SPLIT3_REASON
, imp.SPLIT3_PERCENT
, imp.SPLIT3_LOSS_ACCT -- End of Bug 6666666
from fa_books bk
, fa_impairments imp
where imp.book_type_code = p_book_type_code
and imp.status = l_mode
and imp.request_id = p_request_id
and bk.book_type_code = p_book_type_code
and (bk.period_counter_fully_retired is null or bk.adjustment_required_status <> 'NONE')
/* Bug#7581881 Removed condition on fully reserve asset and show warning */
and bk.deprn_start_date <= p_period_rec.calendar_period_close_date
and (imp.asset_id = bk.asset_id or
bk.cash_generating_unit_id = imp.cash_generating_unit_id)
and bk.transaction_header_id_out is null;
delete from fa_itf_impairments itmp
where exists
(select 'Uploaded impairment'
from fa_books bk , fa_impairments imp
where imp.book_type_code = p_book_type_code
and imp.status = l_mode
and imp.request_id = p_request_id
and bk.book_type_code = p_book_type_code
and (bk.period_counter_fully_retired is null or bk.adjustment_required_status <> 'NONE')
/* Bug#7581881 Removed condition on fully reserve asset and show warning */
and bk.deprn_start_date <= p_period_rec.calendar_period_close_date
and imp.goodwill_asset_id = bk.asset_id
and bk.transaction_header_id_out is null
and itmp.impairment_id = imp.impairment_id);
insert into fa_itf_impairments(
REQUEST_ID
, IMPAIRMENT_ID
, BOOK_TYPE_CODE
, ASSET_ID
, CASH_GENERATING_UNIT_ID
, GOODWILL_ASSET_FLAG
, ADJUSTED_COST
, PERIOD_COUNTER
, COST
, IMPAIRMENT_AMOUNT
, YTD_IMPAIRMENT
, impairment_reserve
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, IMPAIRMENT_DATE
, WORKER_ID
, PROCESS_ORDER
, IMPAIR_CLASS -- Start of Bug 6666666
, REASON
, IMPAIR_LOSS_ACCT
, SPLIT_IMPAIR_FLAG
, SPLIT1_IMPAIR_CLASS
, SPLIT1_REASON
, SPLIT1_PERCENT
, SPLIT1_LOSS_ACCT
, SPLIT2_IMPAIR_CLASS
, SPLIT2_REASON
, SPLIT2_PERCENT
, SPLIT2_LOSS_ACCT
, SPLIT3_IMPAIR_CLASS
, SPLIT3_REASON
, SPLIT3_PERCENT
, SPLIT3_LOSS_ACCT -- End of Bug 6666666
) select
p_request_id --REQUEST_ID
, imp.impairment_id --IMPAIRMENT_ID
, p_book_type_code --BOOK_TYPE_CODE
, bk.ASSET_ID --ASSET_ID
, bk.CASH_GENERATING_UNIT_ID --CASH_GENERATING_UNIT_ID
, 'Y' --GOODWILL_ASSET_FLAG
, bk.ADJUSTED_COST --ADJUSTED_COST
, p_period_rec.period_counter --PERIOD_COUNTER
, bk.COST --COST
, imp.GOODWILL_AMOUNT --IMPAIRMENT_AMOUNT
, imp.GOODWILL_AMOUNT --YTD_IMPAIRMENT
, imp.GOODWILL_AMOUNT --impairment_reserve
, p_prev_sysdate --CREATION_DATE
, p_login_id --CREATED_BY
, p_prev_sysdate --LAST_UPDATE_DATE
, p_login_id --LAST_UPDATED_BY
, nvl(imp.impairment_date, p_transaction_date) --IMPAIRMENT_DATE
, 0 --mod(rank() over(order by bk.asset_id), p_total_requests) --WORKER_ID --Bug5736200
, l_process_order --PROCESS_ORDER
, imp.IMPAIR_CLASS -- Start of Bug 6666666
, imp.REASON
, imp.IMPAIR_LOSS_ACCT
, imp.SPLIT_IMPAIR_FLAG
, imp.SPLIT1_IMPAIR_CLASS
, imp.SPLIT1_REASON
, imp.SPLIT1_PERCENT
, imp.SPLIT1_LOSS_ACCT
, imp.SPLIT2_IMPAIR_CLASS
, imp.SPLIT2_REASON
, imp.SPLIT2_PERCENT
, imp.SPLIT2_LOSS_ACCT
, imp.SPLIT3_IMPAIR_CLASS
, imp.SPLIT3_REASON
, imp.SPLIT3_PERCENT
, imp.SPLIT3_LOSS_ACCT -- End of Bug 6666666
from fa_books bk
, fa_impairments imp
where imp.book_type_code = p_book_type_code
and imp.status = l_mode
and imp.request_id = p_request_id
and bk.book_type_code = p_book_type_code
and (bk.period_counter_fully_retired is null or bk.adjustment_required_status <> 'NONE')
/* Bug#7581881 Removed condition on fully reserve asset and show warning */
and bk.deprn_start_date <= p_period_rec.calendar_period_close_date
and imp.goodwill_asset_id = bk.asset_id
and bk.transaction_header_id_out is null;
select count(*)
into l_je_post_count
from fa_journal_entries je,
fa_mc_book_controls bc
where bc.book_type_code = p_book_type_code
and bc.set_of_books_id = p_set_of_books_id
and je.set_of_books_id = p_set_of_books_id
and bc.book_type_code = je.book_type_code
and je.period_counter = p_period_rec.period_counter
and je.je_status in ('C', 'E')
and ((addition_batch_id is NOT NULL) or
(adjustment_batch_id is NOT NULL) or
(depreciation_batch_id is NOT NULL) or
(reclass_batch_id is NOT NULL) or
(retirement_batch_id is NOT NULL) or
(reval_batch_id is NOT NULL) or
(transfer_batch_id is NOT NULL) or
(cip_addition_batch_id is NOT NULL) or
(cip_adjustment_batch_id is NOT NULL) or
(cip_reclass_batch_id is NOT NULL) or
(cip_retirement_batch_id is NOT NULL) or
(cip_reval_batch_id is NOT NULL) or
(cip_transfer_batch_id is NOT NULL) or
(deprn_adjustment_batch_id is NOT NULL));
select count(*)
into l_dp_post_count
from fa_mc_deprn_periods
where book_type_code = p_book_type_code
and period_counter = p_period_rec.period_counter
and set_of_books_id = p_set_of_books_id
and ((addition_batch_id is NOT NULL) or
(adjustment_batch_id is NOT NULL) or
(depreciation_batch_id is NOT NULL) or
(reclass_batch_id is NOT NULL) or
(retirement_batch_id is NOT NULL) or
(reval_batch_id is NOT NULL) or
(transfer_batch_id is NOT NULL) or
(cip_addition_batch_id is NOT NULL) or
(cip_adjustment_batch_id is NOT NULL) or
(cip_reclass_batch_id is NOT NULL) or
(cip_retirement_batch_id is NOT NULL) or
(cip_reval_batch_id is NOT NULL) or
(cip_transfer_batch_id is NOT NULL) or
(deprn_adjustment_batch_id is NOT NULL));
select count(*)
into l_je_post_count
from fa_journal_entries je,
fa_book_controls bc
where bc.book_type_code = p_book_type_code
and bc.set_of_books_id = je.set_of_books_id
and bc.book_type_code = je.book_type_code
and je.period_counter = p_period_rec.period_counter
and je.je_status in ('C', 'E')
and ((addition_batch_id is NOT NULL) or
(adjustment_batch_id is NOT NULL) or
(depreciation_batch_id is NOT NULL) or
(reclass_batch_id is NOT NULL) or
(retirement_batch_id is NOT NULL) or
(reval_batch_id is NOT NULL) or
(transfer_batch_id is NOT NULL) or
(cip_addition_batch_id is NOT NULL) or
(cip_adjustment_batch_id is NOT NULL) or
(cip_reclass_batch_id is NOT NULL) or
(cip_retirement_batch_id is NOT NULL) or
(cip_reval_batch_id is NOT NULL) or
(cip_transfer_batch_id is NOT NULL) or
(deprn_adjustment_batch_id is NOT NULL));
select count(*)
into l_dp_post_count
from fa_deprn_periods
where book_type_code = p_book_type_code
and period_counter = p_period_rec.period_counter
and ((addition_batch_id is NOT NULL) or
(adjustment_batch_id is NOT NULL) or
(depreciation_batch_id is NOT NULL) or
(reclass_batch_id is NOT NULL) or
(retirement_batch_id is NOT NULL) or
(reval_batch_id is NOT NULL) or
(transfer_batch_id is NOT NULL) or
(cip_addition_batch_id is NOT NULL) or
(cip_adjustment_batch_id is NOT NULL) or
(cip_reclass_batch_id is NOT NULL) or
(cip_retirement_batch_id is NOT NULL) or
(cip_reval_batch_id is NOT NULL) or
(cip_transfer_batch_id is NOT NULL) or
(deprn_adjustment_batch_id is NOT NULL));
update fa_impairments
set status = l_new_status
where status = l_status
and request_id = p_request_id;
update fa_mc_impairments
set status = l_new_status
where status = l_status
and request_id = p_request_id
and set_of_books_id = p_set_of_books_id;