[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FUNCTION delete_post(
p_request_id IN NUMBER,
p_book_type_code IN VARCHAR2,
p_period_rec IN FA_API_TYPES.period_rec_type,
p_worker_id IN NUMBER,
p_mrc_sob_type_code IN VARCHAR2,
p_set_of_books_id IN NUMBER,
p_calling_fn IN VARCHAR2
, p_log_level_rec IN FA_API_TYPES.log_level_rec_type) RETURN BOOLEAN IS
l_calling_fn varchar2(60) := 'fa_process_impairment_pvt.process_depreciation';
select transaction_header_id_in,asset_id from
fa_mc_books
where transaction_header_id_out is null
and book_type_code = p_book_type_code
and set_of_books_id = p_set_of_books_id
and asset_id in (select itf.asset_id
from fa_mc_impairments imp
, fa_mc_itf_impairments itf
where itf.impairment_id = imp.impairment_id
and itf.book_type_code = p_book_type_code
and itf.worker_id = p_worker_id
and imp.request_id = p_request_id
and imp.set_of_books_id = p_set_of_books_id
and itf.set_of_books_id = p_set_of_books_id
and imp.status = 'DELETING POST');
select transaction_header_id_in,asset_id from
fa_books
where transaction_header_id_out is null
and book_type_code = p_book_type_code
and asset_id in (select itf.asset_id
from fa_impairments imp
, fa_itf_impairments itf
where itf.impairment_id = imp.impairment_id
and itf.book_type_code = p_book_type_code
and itf.worker_id = p_worker_id
and imp.request_id = p_request_id
and imp.status = 'DELETING POST');
delete from fa_mc_books
where transaction_header_id_out is null
and book_type_code = p_book_type_code
and set_of_books_id = p_set_of_books_id
and asset_id in (select itf.asset_id
from fa_mc_impairments imp
, fa_mc_itf_impairments itf
where itf.impairment_id = imp.impairment_id
and itf.book_type_code = p_book_type_code
and itf.worker_id = p_worker_id
and itf.set_of_books_id = p_set_of_books_id
and imp.request_id = p_request_id
and imp.status = 'DELETING POST'
and imp.set_of_books_id = p_set_of_books_id)
returning transaction_header_id_in, asset_id
bulk collect into t_thid
, t_asset_id;
delete from fa_mc_adjustments
where transaction_header_id = t_thid(i)
and asset_id = t_asset_id(i)
and book_type_code = p_book_type_code
and period_counter_created = p_period_rec.period_counter
and set_of_books_id = p_set_of_books_id;
delete from fa_mc_deprn_detail
where asset_id = t_asset_id(i)
and 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;
delete from fa_mc_deprn_summary
where asset_id = t_asset_id(i)
and 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;
update fa_transaction_headers
set transaction_type_code ='ADDITION'
where transaction_header_id = (
select transaction_header_id_in
from fa_mc_books
where transaction_header_id_out = t_thid(i)
and set_of_books_id = p_set_of_books_id)
and transaction_type_code = 'ADDITION/VOID'
and book_type_code = p_book_type_code;
update fa_mc_books
set date_ineffective = null
, transaction_header_id_out = null
where asset_id = t_asset_id(i)
and book_type_code = p_book_type_code
and transaction_header_id_out = t_thid(i)
and set_of_books_id = p_set_of_books_id ;
delete from fa_mc_itf_impairments
where asset_id = t_asset_id(i)
and book_type_code = p_book_type_code
and worker_id = p_worker_id
and period_counter = p_period_rec.period_counter
and set_of_books_id = p_set_of_books_id;
delete from fa_books
where transaction_header_id_out is null
and book_type_code = p_book_type_code
and asset_id in (select itf.asset_id
from fa_impairments imp
, fa_itf_impairments itf
where itf.impairment_id = imp.impairment_id
and itf.book_type_code = p_book_type_code
and itf.worker_id = p_worker_id
and imp.request_id = p_request_id
and imp.status = 'DELETING POST')
returning transaction_header_id_in, asset_id
bulk collect into t_thid
, t_asset_id;
delete from fa_adjustments
where transaction_header_id = t_thid(i)
and asset_id = t_asset_id(i)
and book_type_code = p_book_type_code
and period_counter_created = p_period_rec.period_counter;
delete from fa_deprn_detail
where asset_id = t_asset_id(i)
and book_type_code = p_book_type_code
and period_counter = p_period_rec.period_counter;
delete from fa_deprn_summary
where asset_id = t_asset_id(i)
and book_type_code = p_book_type_code
and period_counter = p_period_rec.period_counter;
update fa_transaction_headers
set transaction_type_code='ADDITION'
where transaction_header_id in(
select transaction_header_id_in
from fa_books
where transaction_header_id_out = t_thid(i))
and transaction_type_code = 'ADDITION/VOID'
and book_type_code = p_book_type_code;
update fa_books
set date_ineffective = null
, transaction_header_id_out = null
where asset_id = t_asset_id(i)
and book_type_code = p_book_type_code
and transaction_header_id_out = t_thid(i);
delete from fa_transaction_headers
where transaction_header_id = t_thid(i);
delete from fa_itf_impairments
where asset_id = t_asset_id(i)
and book_type_code = p_book_type_code
and worker_id = p_worker_id
and period_counter = p_period_rec.period_counter;
update fa_deprn_override
set status = 'POST'
where book_type_code = p_book_type_code
and asset_id = t_asset_id(i)
and period_name = p_period_rec.period_name
and used_by = 'DEPRECIATION'
and status = 'POSTED' ;
END delete_post;
l_calling_fn varchar2(60) := 'FA_IMPAIRMENT_DELETE_PVT.process_impair_event';
fa_debug_pkg.add(l_calling_fn, 'Before inserting into : ', 'xla_events_int_gt',p_log_level_rec => p_log_level_rec);
insert into xla_events_int_gt
(APPLICATION_ID ,
LEDGER_ID ,
LEGAL_ENTITY_ID ,
ENTITY_CODE ,
event_type_code ,
event_date ,
event_number ,
event_status_code ,
transaction_number ,
source_id_int_1 ,
source_id_char_1 ,
-- source_id_int_2 ,
--source_id_int_3 ,
valuation_method
)
values
(140 ,
p_set_of_books_id,
l_legal_entity_id ,
'TRANSACTIONS' ,
'IMPAIRMENT' ,
l_event_date ,
NULL ,
XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED,
to_char(p_thid(i)) ,
p_thid(i) ,
p_book_type_code ,
-- p_period_rec.period_counter ,
-- NULL ,
p_book_type_code
);
select event_id,source_id_int_1 bulk collect
into l_event_id_tbl,l_thid
from xla_events_int_gt;
UPDATE FA_TRANSACTION_HEADERS
SET event_id = l_event_id_tbl(i)
WHERE TRANSACTION_HEADER_ID = l_thid(i)
AND BOOK_TYPE_CODE = p_book_type_code;
l_calling_fn varchar2(60) := 'FA_IMPAIRMENT_DELETE_PVT.rollback_deprn_event';
select event_id, deprn_run_id
into l_event_id,
l_deprn_run_id
from fa_deprn_events_v de
where de.book_type_code = p_book_type_code
and de.asset_id = p_asset_id
and de.period_counter = l_period_rec.period_counter
and de.reversal_event_id is null;
create/update/delete event for secondary ledger*/
if (l_secondary_event_id is not null) then
l_secondary_deprn_source_info.application_id := 140;
XLA_EVENTS_PUB_PKG.delete_event
(p_event_source_info => l_secondary_deprn_source_info,
p_event_id => l_secondary_event_id,
p_valuation_method => p_book_type_code,
p_security_context => l_security_context);
l_result := XLA_EVENTS_PUB_PKG.delete_entity
(p_source_info => l_secondary_deprn_source_info,
p_valuation_method => p_book_type_code,
p_security_context => l_security_context);
fa_debug_pkg.add(l_calling_fn, 'Unable to delete entity for rb event',l_secondary_event_id, p_log_level_rec => p_log_level_rec);
XLA_EVENTS_PUB_PKG.delete_event
(p_event_source_info => l_deprn_source_info,
p_event_id => l_event_id,
p_valuation_method => p_book_type_code,
p_security_context => l_security_context);
l_result := XLA_EVENTS_PUB_PKG.delete_entity
(p_source_info => l_deprn_source_info,
p_valuation_method => p_book_type_code,
p_security_context => l_security_context);
fa_debug_pkg.add(l_calling_fn, 'Unable to delete entity for rb event',l_event_id, p_log_level_rec => p_log_level_rec);
DELETE from fa_deprn_events
where asset_id = p_asset_id
and book_type_code = p_book_type_code
and period_counter = l_period_rec.period_counter
and reversal_event_id is null;
update fa_deprn_events
set reversal_event_id = l_rev_event_id,
reversal_date = l_sysdate
where asset_id = p_asset_id
and book_type_code = p_book_type_code
and period_counter = l_period_rec.period_counter
and deprn_run_id = l_deprn_run_id;
insert into fa_deprn_summary_h
(BOOK_TYPE_CODE ,
ASSET_ID ,
DEPRN_RUN_DATE ,
DEPRN_AMOUNT ,
YTD_DEPRN ,
DEPRN_RESERVE ,
DEPRN_SOURCE_CODE ,
ADJUSTED_COST ,
BONUS_RATE ,
LTD_PRODUCTION ,
PERIOD_COUNTER ,
PRODUCTION ,
REVAL_AMORTIZATION ,
REVAL_AMORTIZATION_BASIS ,
REVAL_DEPRN_EXPENSE ,
REVAL_RESERVE ,
YTD_PRODUCTION ,
YTD_REVAL_DEPRN_EXPENSE ,
PRIOR_FY_EXPENSE ,
BONUS_DEPRN_AMOUNT ,
BONUS_YTD_DEPRN ,
BONUS_DEPRN_RESERVE ,
PRIOR_FY_BONUS_EXPENSE ,
DEPRN_OVERRIDE_FLAG ,
SYSTEM_DEPRN_AMOUNT ,
SYSTEM_BONUS_DEPRN_AMOUNT ,
IMPAIRMENT_AMOUNT ,
YTD_IMPAIRMENT ,
IMPAIRMENT_RESERVE ,
CAPITAL_ADJUSTMENT ,
GENERAL_FUND ,
REVAL_LOSS_BALANCE ,
EVENT_ID ,
DEPRN_RUN_ID ,
REVERSAL_EVENT_ID ,
REVERSAL_DATE )
select
BOOK_TYPE_CODE ,
ASSET_ID ,
DEPRN_RUN_DATE ,
DEPRN_AMOUNT ,
YTD_DEPRN ,
DEPRN_RESERVE ,
DEPRN_SOURCE_CODE ,
ADJUSTED_COST ,
BONUS_RATE ,
LTD_PRODUCTION ,
PERIOD_COUNTER ,
PRODUCTION ,
REVAL_AMORTIZATION ,
REVAL_AMORTIZATION_BASIS ,
REVAL_DEPRN_EXPENSE ,
REVAL_RESERVE ,
YTD_PRODUCTION ,
YTD_REVAL_DEPRN_EXPENSE ,
PRIOR_FY_EXPENSE ,
BONUS_DEPRN_AMOUNT ,
BONUS_YTD_DEPRN ,
BONUS_DEPRN_RESERVE ,
PRIOR_FY_BONUS_EXPENSE ,
DEPRN_OVERRIDE_FLAG ,
SYSTEM_DEPRN_AMOUNT ,
SYSTEM_BONUS_DEPRN_AMOUNT ,
IMPAIRMENT_AMOUNT ,
YTD_IMPAIRMENT ,
IMPAIRMENT_RESERVE ,
CAPITAL_ADJUSTMENT ,
GENERAL_FUND ,
REVAL_LOSS_BALANCE ,
EVENT_ID ,
DEPRN_RUN_ID ,
l_rev_event_id ,
l_sysdate
from fa_deprn_summary ds
where ds.book_type_code = p_book_type_code
and ds.asset_id = p_asset_id
and ds.period_counter = l_period_rec.period_counter
and ds.deprn_source_code in ('DEPRN','TRACK');
insert into fa_deprn_detail_h
(BOOK_TYPE_CODE ,
ASSET_ID ,
PERIOD_COUNTER ,
DISTRIBUTION_ID ,
DEPRN_SOURCE_CODE ,
DEPRN_RUN_DATE ,
DEPRN_AMOUNT ,
YTD_DEPRN ,
DEPRN_RESERVE ,
ADDITION_COST_TO_CLEAR ,
COST ,
DEPRN_ADJUSTMENT_AMOUNT ,
REVAL_AMORTIZATION ,
REVAL_DEPRN_EXPENSE ,
REVAL_RESERVE ,
YTD_REVAL_DEPRN_EXPENSE ,
BONUS_DEPRN_AMOUNT ,
BONUS_YTD_DEPRN ,
BONUS_DEPRN_RESERVE ,
BONUS_DEPRN_ADJUSTMENT_AMOUNT ,
IMPAIRMENT_AMOUNT ,
YTD_IMPAIRMENT ,
IMPAIRMENT_RESERVE ,
CAPITAL_ADJUSTMENT ,
GENERAL_FUND ,
EVENT_ID ,
DEPRN_RUN_ID ,
REVERSAL_EVENT_ID ,
REVERSAL_DATE )
select
BOOK_TYPE_CODE ,
ASSET_ID ,
PERIOD_COUNTER ,
DISTRIBUTION_ID ,
DEPRN_SOURCE_CODE ,
DEPRN_RUN_DATE ,
DEPRN_AMOUNT ,
YTD_DEPRN ,
DEPRN_RESERVE ,
ADDITION_COST_TO_CLEAR ,
COST ,
DEPRN_ADJUSTMENT_AMOUNT ,
REVAL_AMORTIZATION ,
REVAL_DEPRN_EXPENSE ,
REVAL_RESERVE ,
YTD_REVAL_DEPRN_EXPENSE ,
BONUS_DEPRN_AMOUNT ,
BONUS_YTD_DEPRN ,
BONUS_DEPRN_RESERVE ,
BONUS_DEPRN_ADJUSTMENT_AMOUNT ,
IMPAIRMENT_AMOUNT ,
YTD_IMPAIRMENT ,
IMPAIRMENT_RESERVE ,
CAPITAL_ADJUSTMENT ,
GENERAL_FUND ,
EVENT_ID ,
DEPRN_RUN_ID ,
l_rev_event_id ,
l_sysdate
from fa_deprn_detail ds
where ds.book_type_code = p_book_type_code
and ds.asset_id = p_asset_id
and ds.period_counter = l_period_rec.period_counter
and ds.deprn_source_code in ('D','T');
insert into fa_mc_deprn_summary_h
(SET_OF_BOOKS_ID ,
BOOK_TYPE_CODE ,
ASSET_ID ,
DEPRN_RUN_DATE ,
DEPRN_AMOUNT ,
YTD_DEPRN ,
DEPRN_RESERVE ,
DEPRN_SOURCE_CODE ,
ADJUSTED_COST ,
BONUS_RATE ,
LTD_PRODUCTION ,
PERIOD_COUNTER ,
PRODUCTION ,
REVAL_AMORTIZATION ,
REVAL_AMORTIZATION_BASIS ,
REVAL_DEPRN_EXPENSE ,
REVAL_RESERVE ,
YTD_PRODUCTION ,
YTD_REVAL_DEPRN_EXPENSE ,
PRIOR_FY_EXPENSE ,
BONUS_DEPRN_AMOUNT ,
BONUS_YTD_DEPRN ,
BONUS_DEPRN_RESERVE ,
PRIOR_FY_BONUS_EXPENSE ,
DEPRN_OVERRIDE_FLAG ,
SYSTEM_DEPRN_AMOUNT ,
SYSTEM_BONUS_DEPRN_AMOUNT ,
IMPAIRMENT_AMOUNT ,
YTD_IMPAIRMENT ,
IMPAIRMENT_RESERVE ,
CAPITAL_ADJUSTMENT ,
GENERAL_FUND ,
REVAL_LOSS_BALANCE ,
EVENT_ID ,
DEPRN_RUN_ID ,
REVERSAL_EVENT_ID ,
REVERSAL_DATE )
select
SET_OF_BOOKS_ID ,
BOOK_TYPE_CODE ,
ASSET_ID ,
DEPRN_RUN_DATE ,
DEPRN_AMOUNT ,
YTD_DEPRN ,
DEPRN_RESERVE ,
DEPRN_SOURCE_CODE ,
ADJUSTED_COST ,
BONUS_RATE ,
LTD_PRODUCTION ,
PERIOD_COUNTER ,
PRODUCTION ,
REVAL_AMORTIZATION ,
REVAL_AMORTIZATION_BASIS ,
REVAL_DEPRN_EXPENSE ,
REVAL_RESERVE ,
YTD_PRODUCTION ,
YTD_REVAL_DEPRN_EXPENSE ,
PRIOR_FY_EXPENSE ,
BONUS_DEPRN_AMOUNT ,
BONUS_YTD_DEPRN ,
BONUS_DEPRN_RESERVE ,
PRIOR_FY_BONUS_EXPENSE ,
DEPRN_OVERRIDE_FLAG ,
SYSTEM_DEPRN_AMOUNT ,
SYSTEM_BONUS_DEPRN_AMOUNT ,
IMPAIRMENT_AMOUNT ,
YTD_IMPAIRMENT ,
IMPAIRMENT_RESERVE ,
CAPITAL_ADJUSTMENT ,
GENERAL_FUND ,
REVAL_LOSS_BALANCE ,
EVENT_ID ,
DEPRN_RUN_ID ,
decode(set_of_books_id,l_secondary_sob_id, l_secondary_rev_event_id,l_rev_event_id) ,
l_sysdate
from fa_mc_deprn_summary ds
where ds.book_type_code = p_book_type_code
and ds.asset_id = p_asset_id
and ds.period_counter = l_period_rec.period_counter
and ds.deprn_source_code in ('DEPRN','TRACK');
insert into fa_mc_deprn_detail_h
(SET_OF_BOOKS_ID ,
BOOK_TYPE_CODE ,
ASSET_ID ,
PERIOD_COUNTER ,
DISTRIBUTION_ID ,
DEPRN_SOURCE_CODE ,
DEPRN_RUN_DATE ,
DEPRN_AMOUNT ,
YTD_DEPRN ,
DEPRN_RESERVE ,
ADDITION_COST_TO_CLEAR ,
COST ,
DEPRN_ADJUSTMENT_AMOUNT ,
REVAL_AMORTIZATION ,
REVAL_DEPRN_EXPENSE ,
REVAL_RESERVE ,
YTD_REVAL_DEPRN_EXPENSE ,
BONUS_DEPRN_AMOUNT ,
BONUS_YTD_DEPRN ,
BONUS_DEPRN_RESERVE ,
BONUS_DEPRN_ADJUSTMENT_AMOUNT ,
IMPAIRMENT_AMOUNT ,
YTD_IMPAIRMENT ,
IMPAIRMENT_RESERVE ,
CAPITAL_ADJUSTMENT ,
GENERAL_FUND ,
EVENT_ID ,
DEPRN_RUN_ID ,
REVERSAL_EVENT_ID ,
REVERSAL_DATE )
select
SET_OF_BOOKS_ID ,
BOOK_TYPE_CODE ,
ASSET_ID ,
PERIOD_COUNTER ,
DISTRIBUTION_ID ,
DEPRN_SOURCE_CODE ,
DEPRN_RUN_DATE ,
DEPRN_AMOUNT ,
YTD_DEPRN ,
DEPRN_RESERVE ,
ADDITION_COST_TO_CLEAR ,
COST ,
DEPRN_ADJUSTMENT_AMOUNT ,
REVAL_AMORTIZATION ,
REVAL_DEPRN_EXPENSE ,
REVAL_RESERVE ,
YTD_REVAL_DEPRN_EXPENSE ,
BONUS_DEPRN_AMOUNT ,
BONUS_YTD_DEPRN ,
BONUS_DEPRN_RESERVE ,
BONUS_DEPRN_ADJUSTMENT_AMOUNT ,
IMPAIRMENT_AMOUNT ,
YTD_IMPAIRMENT ,
IMPAIRMENT_RESERVE ,
CAPITAL_ADJUSTMENT ,
GENERAL_FUND ,
EVENT_ID ,
DEPRN_RUN_ID ,
decode(set_of_books_id,l_secondary_sob_id, l_secondary_rev_event_id,l_rev_event_id),
l_sysdate
from fa_mc_deprn_detail ds
where ds.book_type_code = p_book_type_code
and ds.asset_id = p_asset_id
and ds.period_counter = l_period_rec.period_counter
and ds.deprn_source_code in ('D','T');
l_calling_fn varchar2(60) := 'FA_IMPAIRMENT_DELETE_PVT.rollback_impair_event';
l_amount_inserted number;
SELECT adj.code_combination_id ,
adj.distribution_id ,
adj.debit_credit_flag ,
adj.adjustment_amount ,
adj.adjustment_type ,
adj.source_type_code ,
ad.current_units
FROM fa_mc_adjustments adj,
fa_additions_b ad
WHERE transaction_header_id = p_thid
AND set_of_books_id = p_set_of_books_id
AND ad.asset_id = adj.asset_id;
SELECT adj.code_combination_id ,
adj.distribution_id ,
adj.debit_credit_flag ,
adj.adjustment_amount ,
adj.adjustment_type ,
adj.source_type_code ,
ad.current_units
FROM fa_adjustments adj,
fa_additions_b ad
WHERE transaction_header_id = p_thid
AND ad.asset_id = adj.asset_id;
/*8666930 - For mrc we will not find event as it was already deleted for primary if uprocessed*/
BEGIN
select event_id
into l_event_id
from fa_transaction_headers
where asset_id = p_asset_id
and book_type_code = p_book_type_code
and transaction_header_id = p_thid;
if not fa_xla_events_pvt.delete_transaction_event
(p_ledger_id => p_set_of_books_id,
p_transaction_header_id => p_thid,
p_book_type_code => p_book_type_code,
p_calling_fn => l_calling_fn
,p_log_level_rec => p_log_level_rec) then
if (p_log_level_rec.statement_level) then
fa_debug_pkg.add(l_calling_fn,'Failed ','delete_transaction_event',p_log_level_rec => p_log_level_rec);
fa_debug_pkg.add(l_calling_fn,'deleted accounting impacts for impairment thid',p_thid,p_log_level_rec => p_log_level_rec);
delete from fa_books
where transaction_header_id_out is null
and transaction_header_id_in = p_thid
and book_type_code = p_book_type_code
and asset_id = p_asset_id ;
delete from fa_adjustments
where transaction_header_id = p_thid
and asset_id = p_asset_id
and book_type_code = p_book_type_code
and period_counter_created = l_period_rec.period_counter;
update fa_books
set date_ineffective = null
, transaction_header_id_out = null
where asset_id = p_asset_id
and book_type_code = p_book_type_code
and transaction_header_id_out = p_thid;
delete from fa_transaction_headers
where transaction_header_id = p_thid;
UPDATE FA_TRANSACTION_HEADERS
SET ATTRIBUTE15 = ATTRIBUTE15
WHERE ASSET_ID = p_asset_id
AND BOOK_TYPE_CODE = p_book_type_code
AND TRANSACTION_TYPE_CODE = 'ADJUSTMENT'
AND TRANSACTION_SUBTYPE = 'AMORTIZED'
AND TRANSACTION_KEY = 'IM'
AND CALLING_INTERFACE = 'FAPIMP'
AND MASS_TRANSACTION_ID = p_request_id
RETURNING TRANSACTION_HEADER_ID INTO l_thid;
INSERT INTO FA_TRANSACTION_HEADERS(
TRANSACTION_HEADER_ID
, BOOK_TYPE_CODE
, ASSET_ID
, TRANSACTION_TYPE_CODE
, TRANSACTION_DATE_ENTERED
, DATE_EFFECTIVE
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, TRANSACTION_SUBTYPE
, TRANSACTION_KEY
, AMORTIZATION_START_DATE
, CALLING_INTERFACE
, MASS_TRANSACTION_ID
) VALUES (
FA_TRANSACTION_HEADERS_S.NEXTVAL
, p_book_type_code
, p_asset_id
, 'ADJUSTMENT' /*This must be some new transaction */
, l_period_rec.calendar_period_open_date /* need to modify to populate correct who info */
, sysdate
, SYSDATE
, fnd_global.user_id
, 'AMORTIZED'
, 'RM' --8582979
, l_period_rec.calendar_period_open_date
, 'FAPIMP'
, p_request_id ) RETURNING transaction_header_id INTO l_thid;
update fa_transaction_headers
set event_id = l_rev_event_id
where transaction_header_id = l_thid;
UPDATE FA_MC_BOOKS
SET DATE_INEFFECTIVE = sysdate
, TRANSACTION_HEADER_ID_OUT = l_thid
WHERE ASSET_ID = p_asset_id
AND BOOK_TYPE_CODE = p_book_type_code
AND TRANSACTION_HEADER_ID_in = p_thid
AND TRANSACTION_HEADER_ID_OUT is null
AND SET_OF_BOOKS_ID = p_set_of_books_id;
UPDATE FA_BOOKS
SET DATE_INEFFECTIVE = sysdate
, TRANSACTION_HEADER_ID_OUT = l_thid
WHERE ASSET_ID = p_asset_id
AND BOOK_TYPE_CODE = p_book_type_code
AND TRANSACTION_HEADER_ID_in = p_thid
AND TRANSACTION_HEADER_ID_OUT is null;
INSERT INTO FA_MC_BOOKS( SET_OF_BOOKS_ID
, BOOK_TYPE_CODE
, ASSET_ID
, DATE_PLACED_IN_SERVICE
, DATE_EFFECTIVE
, DEPRN_START_DATE
, DEPRN_METHOD_CODE
, LIFE_IN_MONTHS
, RATE_ADJUSTMENT_FACTOR
, ADJUSTED_COST
, COST
, ORIGINAL_COST
, SALVAGE_VALUE
, PRORATE_CONVENTION_CODE
, PRORATE_DATE
, COST_CHANGE_FLAG
, ADJUSTMENT_REQUIRED_STATUS
, CAPITALIZE_FLAG
, RETIREMENT_PENDING_FLAG
, DEPRECIATE_FLAG
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, TRANSACTION_HEADER_ID_IN
, ITC_AMOUNT_ID
, ITC_AMOUNT
, RETIREMENT_ID
, TAX_REQUEST_ID
, ITC_BASIS
, BASIC_RATE
, ADJUSTED_RATE
, BONUS_RULE
, CEILING_NAME
, RECOVERABLE_COST
, ADJUSTED_CAPACITY
, FULLY_RSVD_REVALS_COUNTER
, IDLED_FLAG
, PERIOD_COUNTER_CAPITALIZED
, PERIOD_COUNTER_FULLY_RESERVED
, PERIOD_COUNTER_FULLY_RETIRED
, PRODUCTION_CAPACITY
, REVAL_AMORTIZATION_BASIS
, REVAL_CEILING
, UNIT_OF_MEASURE
, UNREVALUED_COST
, ANNUAL_DEPRN_ROUNDING_FLAG
, PERCENT_SALVAGE_VALUE
, ALLOWED_DEPRN_LIMIT
, ALLOWED_DEPRN_LIMIT_AMOUNT
, PERIOD_COUNTER_LIFE_COMPLETE
, ADJUSTED_RECOVERABLE_COST
, ANNUAL_ROUNDING_FLAG
, GLOBAL_ATTRIBUTE1
, GLOBAL_ATTRIBUTE2
, GLOBAL_ATTRIBUTE3
, GLOBAL_ATTRIBUTE4
, GLOBAL_ATTRIBUTE5
, GLOBAL_ATTRIBUTE6
, GLOBAL_ATTRIBUTE7
, GLOBAL_ATTRIBUTE8
, GLOBAL_ATTRIBUTE9
, GLOBAL_ATTRIBUTE10
, GLOBAL_ATTRIBUTE11
, GLOBAL_ATTRIBUTE12
, GLOBAL_ATTRIBUTE13
, GLOBAL_ATTRIBUTE14
, GLOBAL_ATTRIBUTE15
, GLOBAL_ATTRIBUTE16
, GLOBAL_ATTRIBUTE17
, GLOBAL_ATTRIBUTE18
, GLOBAL_ATTRIBUTE19
, GLOBAL_ATTRIBUTE20
, GLOBAL_ATTRIBUTE_CATEGORY
, EOFY_ADJ_COST
, EOFY_FORMULA_FACTOR
, SHORT_FISCAL_YEAR_FLAG
, CONVERSION_DATE
, ORIGINAL_DEPRN_START_DATE
, REMAINING_LIFE1
, REMAINING_LIFE2
, OLD_ADJUSTED_COST
, FORMULA_FACTOR
, GROUP_ASSET_ID
, SALVAGE_TYPE
, DEPRN_LIMIT_TYPE
, REDUCTION_RATE
, REDUCE_ADDITION_FLAG
, REDUCE_ADJUSTMENT_FLAG
, REDUCE_RETIREMENT_FLAG
, RECOGNIZE_GAIN_LOSS
, RECAPTURE_RESERVE_FLAG
, LIMIT_PROCEEDS_FLAG
, TERMINAL_GAIN_LOSS
, TRACKING_METHOD
, EXCLUDE_FULLY_RSV_FLAG
, EXCESS_ALLOCATION_OPTION
, DEPRECIATION_OPTION
, MEMBER_ROLLUP_FLAG
, ALLOCATE_TO_FULLY_RSV_FLAG
, ALLOCATE_TO_FULLY_RET_FLAG
, TERMINAL_GAIN_LOSS_AMOUNT
, CIP_COST
, YTD_PROCEEDS
, LTD_PROCEEDS
, LTD_COST_OF_REMOVAL
, EOFY_RESERVE
, PRIOR_EOFY_RESERVE
, EOP_ADJ_COST
, EOP_FORMULA_FACTOR
, EXCLUDE_PROCEEDS_FROM_BASIS
, RETIREMENT_DEPRN_OPTION
, TERMINAL_GAIN_LOSS_FLAG
, SUPER_GROUP_ID
, OVER_DEPRECIATE_OPTION
, DISABLED_FLAG
, CASH_GENERATING_UNIT_ID
, RATE_IN_USE
) SELECT SET_OF_BOOKS_ID
, BOOK_TYPE_CODE
, ASSET_ID
, DATE_PLACED_IN_SERVICE
, SYSDATE -- DATE_EFFECTIVE
, DEPRN_START_DATE
, DEPRN_METHOD_CODE
, LIFE_IN_MONTHS
, RATE_ADJUSTMENT_FACTOR --RATE_ADJUSTMENT_FACTOR
, ADJUSTED_COST -- ADJUSTED_COST
, COST
, ORIGINAL_COST
, SALVAGE_VALUE
, PRORATE_CONVENTION_CODE
, PRORATE_DATE
, COST_CHANGE_FLAG
, ADJUSTMENT_REQUIRED_STATUS
, CAPITALIZE_FLAG
, RETIREMENT_PENDING_FLAG
, DEPRECIATE_FLAG
, sysdate -- LAST_UPDATE_DATE
, fnd_global.user_id -- LAST_UPDATED_BY
, l_thid -- TRANSACTION_HEADER_ID_IN
, ITC_AMOUNT_ID
, ITC_AMOUNT
, RETIREMENT_ID
, TAX_REQUEST_ID
, ITC_BASIS
, BASIC_RATE
, ADJUSTED_RATE
, BONUS_RULE
, CEILING_NAME
, RECOVERABLE_COST
, ADJUSTED_CAPACITY
, FULLY_RSVD_REVALS_COUNTER
, IDLED_FLAG
, PERIOD_COUNTER_CAPITALIZED
, PERIOD_COUNTER_FULLY_RESERVED
, PERIOD_COUNTER_FULLY_RETIRED
, PRODUCTION_CAPACITY
, REVAL_AMORTIZATION_BASIS
, REVAL_CEILING
, UNIT_OF_MEASURE
, UNREVALUED_COST
, ANNUAL_DEPRN_ROUNDING_FLAG
, PERCENT_SALVAGE_VALUE
, ALLOWED_DEPRN_LIMIT
, ALLOWED_DEPRN_LIMIT_AMOUNT
, PERIOD_COUNTER_LIFE_COMPLETE
, ADJUSTED_RECOVERABLE_COST
, ANNUAL_ROUNDING_FLAG
, GLOBAL_ATTRIBUTE1
, GLOBAL_ATTRIBUTE2
, GLOBAL_ATTRIBUTE3
, GLOBAL_ATTRIBUTE4
, GLOBAL_ATTRIBUTE5
, GLOBAL_ATTRIBUTE6
, GLOBAL_ATTRIBUTE7
, GLOBAL_ATTRIBUTE8
, GLOBAL_ATTRIBUTE9
, GLOBAL_ATTRIBUTE10
, GLOBAL_ATTRIBUTE11
, GLOBAL_ATTRIBUTE12
, GLOBAL_ATTRIBUTE13
, GLOBAL_ATTRIBUTE14
, GLOBAL_ATTRIBUTE15
, GLOBAL_ATTRIBUTE16
, GLOBAL_ATTRIBUTE17
, GLOBAL_ATTRIBUTE18
, GLOBAL_ATTRIBUTE19
, GLOBAL_ATTRIBUTE20
, GLOBAL_ATTRIBUTE_CATEGORY
, EOFY_ADJ_COST
, EOFY_FORMULA_FACTOR
, SHORT_FISCAL_YEAR_FLAG
, CONVERSION_DATE
, ORIGINAL_DEPRN_START_DATE
, REMAINING_LIFE1
, REMAINING_LIFE2
, OLD_ADJUSTED_COST
, formula_factor --FORMULA_FACTOR
, GROUP_ASSET_ID
, SALVAGE_TYPE
, DEPRN_LIMIT_TYPE
, REDUCTION_RATE
, REDUCE_ADDITION_FLAG
, REDUCE_ADJUSTMENT_FLAG
, REDUCE_RETIREMENT_FLAG
, RECOGNIZE_GAIN_LOSS
, RECAPTURE_RESERVE_FLAG
, LIMIT_PROCEEDS_FLAG
, TERMINAL_GAIN_LOSS
, TRACKING_METHOD
, EXCLUDE_FULLY_RSV_FLAG
, EXCESS_ALLOCATION_OPTION
, DEPRECIATION_OPTION
, MEMBER_ROLLUP_FLAG
, ALLOCATE_TO_FULLY_RSV_FLAG
, ALLOCATE_TO_FULLY_RET_FLAG
, TERMINAL_GAIN_LOSS_AMOUNT
, CIP_COST
, YTD_PROCEEDS
, LTD_PROCEEDS
, LTD_COST_OF_REMOVAL
, eofy_reserve --EOFY_RESERVE
, PRIOR_EOFY_RESERVE
, EOP_ADJ_COST
, EOP_FORMULA_FACTOR
, EXCLUDE_PROCEEDS_FROM_BASIS
, RETIREMENT_DEPRN_OPTION
, TERMINAL_GAIN_LOSS_FLAG
, SUPER_GROUP_ID
, OVER_DEPRECIATE_OPTION
, DISABLED_FLAG
, CASH_GENERATING_UNIT_ID
, RATE_IN_USE
FROM FA_MC_BOOKS
WHERE TRANSACTION_HEADER_ID_out = p_thid
AND SET_OF_BOOKS_ID = p_set_of_books_id ;
INSERT INTO FA_BOOKS( BOOK_TYPE_CODE
, ASSET_ID
, DATE_PLACED_IN_SERVICE
, DATE_EFFECTIVE
, DEPRN_START_DATE
, DEPRN_METHOD_CODE
, LIFE_IN_MONTHS
, RATE_ADJUSTMENT_FACTOR
, ADJUSTED_COST
, COST
, ORIGINAL_COST
, SALVAGE_VALUE
, PRORATE_CONVENTION_CODE
, PRORATE_DATE
, COST_CHANGE_FLAG
, ADJUSTMENT_REQUIRED_STATUS
, CAPITALIZE_FLAG
, RETIREMENT_PENDING_FLAG
, DEPRECIATE_FLAG
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, TRANSACTION_HEADER_ID_IN
, ITC_AMOUNT_ID
, ITC_AMOUNT
, RETIREMENT_ID
, TAX_REQUEST_ID
, ITC_BASIS
, BASIC_RATE
, ADJUSTED_RATE
, BONUS_RULE
, CEILING_NAME
, RECOVERABLE_COST
, ADJUSTED_CAPACITY
, FULLY_RSVD_REVALS_COUNTER
, IDLED_FLAG
, PERIOD_COUNTER_CAPITALIZED
, PERIOD_COUNTER_FULLY_RESERVED
, PERIOD_COUNTER_FULLY_RETIRED
, PRODUCTION_CAPACITY
, REVAL_AMORTIZATION_BASIS
, REVAL_CEILING
, UNIT_OF_MEASURE
, UNREVALUED_COST
, ANNUAL_DEPRN_ROUNDING_FLAG
, PERCENT_SALVAGE_VALUE
, ALLOWED_DEPRN_LIMIT
, ALLOWED_DEPRN_LIMIT_AMOUNT
, PERIOD_COUNTER_LIFE_COMPLETE
, ADJUSTED_RECOVERABLE_COST
, ANNUAL_ROUNDING_FLAG
, GLOBAL_ATTRIBUTE1
, GLOBAL_ATTRIBUTE2
, GLOBAL_ATTRIBUTE3
, GLOBAL_ATTRIBUTE4
, GLOBAL_ATTRIBUTE5
, GLOBAL_ATTRIBUTE6
, GLOBAL_ATTRIBUTE7
, GLOBAL_ATTRIBUTE8
, GLOBAL_ATTRIBUTE9
, GLOBAL_ATTRIBUTE10
, GLOBAL_ATTRIBUTE11
, GLOBAL_ATTRIBUTE12
, GLOBAL_ATTRIBUTE13
, GLOBAL_ATTRIBUTE14
, GLOBAL_ATTRIBUTE15
, GLOBAL_ATTRIBUTE16
, GLOBAL_ATTRIBUTE17
, GLOBAL_ATTRIBUTE18
, GLOBAL_ATTRIBUTE19
, GLOBAL_ATTRIBUTE20
, GLOBAL_ATTRIBUTE_CATEGORY
, EOFY_ADJ_COST
, EOFY_FORMULA_FACTOR
, SHORT_FISCAL_YEAR_FLAG
, CONVERSION_DATE
, ORIGINAL_DEPRN_START_DATE
, REMAINING_LIFE1
, REMAINING_LIFE2
, OLD_ADJUSTED_COST
, FORMULA_FACTOR
, GROUP_ASSET_ID
, SALVAGE_TYPE
, DEPRN_LIMIT_TYPE
, REDUCTION_RATE
, REDUCE_ADDITION_FLAG
, REDUCE_ADJUSTMENT_FLAG
, REDUCE_RETIREMENT_FLAG
, RECOGNIZE_GAIN_LOSS
, RECAPTURE_RESERVE_FLAG
, LIMIT_PROCEEDS_FLAG
, TERMINAL_GAIN_LOSS
, TRACKING_METHOD
, EXCLUDE_FULLY_RSV_FLAG
, EXCESS_ALLOCATION_OPTION
, DEPRECIATION_OPTION
, MEMBER_ROLLUP_FLAG
, ALLOCATE_TO_FULLY_RSV_FLAG
, ALLOCATE_TO_FULLY_RET_FLAG
, TERMINAL_GAIN_LOSS_AMOUNT
, CIP_COST
, YTD_PROCEEDS
, LTD_PROCEEDS
, LTD_COST_OF_REMOVAL
, EOFY_RESERVE
, PRIOR_EOFY_RESERVE
, EOP_ADJ_COST
, EOP_FORMULA_FACTOR
, EXCLUDE_PROCEEDS_FROM_BASIS
, RETIREMENT_DEPRN_OPTION
, TERMINAL_GAIN_LOSS_FLAG
, SUPER_GROUP_ID
, OVER_DEPRECIATE_OPTION
, DISABLED_FLAG
, CASH_GENERATING_UNIT_ID
, RATE_IN_USE
) SELECT BOOK_TYPE_CODE
, ASSET_ID
, DATE_PLACED_IN_SERVICE
, SYSDATE -- DATE_EFFECTIVE
, DEPRN_START_DATE
, DEPRN_METHOD_CODE
, LIFE_IN_MONTHS
, RATE_ADJUSTMENT_FACTOR --RATE_ADJUSTMENT_FACTOR
, ADJUSTED_COST -- ADJUSTED_COST
, COST
, ORIGINAL_COST
, SALVAGE_VALUE
, PRORATE_CONVENTION_CODE
, PRORATE_DATE
, COST_CHANGE_FLAG
, ADJUSTMENT_REQUIRED_STATUS
, CAPITALIZE_FLAG
, RETIREMENT_PENDING_FLAG
, DEPRECIATE_FLAG
, sysdate -- LAST_UPDATE_DATE
, fnd_global.user_id -- LAST_UPDATED_BY
, l_thid -- TRANSACTION_HEADER_ID_IN
, ITC_AMOUNT_ID
, ITC_AMOUNT
, RETIREMENT_ID
, TAX_REQUEST_ID
, ITC_BASIS
, BASIC_RATE
, ADJUSTED_RATE
, BONUS_RULE
, CEILING_NAME
, RECOVERABLE_COST
, ADJUSTED_CAPACITY
, FULLY_RSVD_REVALS_COUNTER
, IDLED_FLAG
, PERIOD_COUNTER_CAPITALIZED
, PERIOD_COUNTER_FULLY_RESERVED
, PERIOD_COUNTER_FULLY_RETIRED
, PRODUCTION_CAPACITY
, REVAL_AMORTIZATION_BASIS
, REVAL_CEILING
, UNIT_OF_MEASURE
, UNREVALUED_COST
, ANNUAL_DEPRN_ROUNDING_FLAG
, PERCENT_SALVAGE_VALUE
, ALLOWED_DEPRN_LIMIT
, ALLOWED_DEPRN_LIMIT_AMOUNT
, PERIOD_COUNTER_LIFE_COMPLETE
, ADJUSTED_RECOVERABLE_COST
, ANNUAL_ROUNDING_FLAG
, GLOBAL_ATTRIBUTE1
, GLOBAL_ATTRIBUTE2
, GLOBAL_ATTRIBUTE3
, GLOBAL_ATTRIBUTE4
, GLOBAL_ATTRIBUTE5
, GLOBAL_ATTRIBUTE6
, GLOBAL_ATTRIBUTE7
, GLOBAL_ATTRIBUTE8
, GLOBAL_ATTRIBUTE9
, GLOBAL_ATTRIBUTE10
, GLOBAL_ATTRIBUTE11
, GLOBAL_ATTRIBUTE12
, GLOBAL_ATTRIBUTE13
, GLOBAL_ATTRIBUTE14
, GLOBAL_ATTRIBUTE15
, GLOBAL_ATTRIBUTE16
, GLOBAL_ATTRIBUTE17
, GLOBAL_ATTRIBUTE18
, GLOBAL_ATTRIBUTE19
, GLOBAL_ATTRIBUTE20
, GLOBAL_ATTRIBUTE_CATEGORY
, EOFY_ADJ_COST
, EOFY_FORMULA_FACTOR
, SHORT_FISCAL_YEAR_FLAG
, CONVERSION_DATE
, ORIGINAL_DEPRN_START_DATE
, REMAINING_LIFE1
, REMAINING_LIFE2
, OLD_ADJUSTED_COST
, formula_factor --FORMULA_FACTOR
, GROUP_ASSET_ID
, SALVAGE_TYPE
, DEPRN_LIMIT_TYPE
, REDUCTION_RATE
, REDUCE_ADDITION_FLAG
, REDUCE_ADJUSTMENT_FLAG
, REDUCE_RETIREMENT_FLAG
, RECOGNIZE_GAIN_LOSS
, RECAPTURE_RESERVE_FLAG
, LIMIT_PROCEEDS_FLAG
, TERMINAL_GAIN_LOSS
, TRACKING_METHOD
, EXCLUDE_FULLY_RSV_FLAG
, EXCESS_ALLOCATION_OPTION
, DEPRECIATION_OPTION
, MEMBER_ROLLUP_FLAG
, ALLOCATE_TO_FULLY_RSV_FLAG
, ALLOCATE_TO_FULLY_RET_FLAG
, TERMINAL_GAIN_LOSS_AMOUNT
, CIP_COST
, YTD_PROCEEDS
, LTD_PROCEEDS
, LTD_COST_OF_REMOVAL
, eofy_reserve --EOFY_RESERVE
, PRIOR_EOFY_RESERVE
, EOP_ADJ_COST
, EOP_FORMULA_FACTOR
, EXCLUDE_PROCEEDS_FROM_BASIS
, RETIREMENT_DEPRN_OPTION
, TERMINAL_GAIN_LOSS_FLAG
, SUPER_GROUP_ID
, OVER_DEPRECIATE_OPTION
, DISABLED_FLAG
, CASH_GENERATING_UNIT_ID
, RATE_IN_USE
FROM FA_BOOKS
WHERE TRANSACTION_HEADER_ID_out = p_thid;
l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_SINGLE;
l_adj.selection_thid := 0;
l_adj.selection_retid := 0;
l_adj.last_update_date := sysdate ; --px_trans_rec.who_info.last_update_date;
/* not sure whether we need to update event_id to null for impairment transaction if event is already processed.*/
end if;--Event status
/*if event is already deleted for primary currency,this block will execute for reporting currency */
delete from fa_mc_books
where transaction_header_id_out is null
and transaction_header_id_in = p_thid
and book_type_code = p_book_type_code
and asset_id = p_asset_id
and set_of_books_id = p_set_of_books_id;
delete from fa_mc_adjustments
where transaction_header_id = p_thid
and asset_id = p_asset_id
and book_type_code = p_book_type_code
and period_counter_created = l_period_rec.period_counter
and set_of_books_id = p_set_of_books_id;
update fa_mc_books
set date_ineffective = null
, transaction_header_id_out = null
where asset_id = p_asset_id
and book_type_code = p_book_type_code
and transaction_header_id_out = p_thid
and set_of_books_id = p_set_of_books_id;