[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT max(th.transaction_header_id) ,
max(dp.period_counter)
INTO l_transaction_header_id,
l_period_counter
FROM fa_books fb ,
fa_calendar_periods cp ,
fa_deprn_periods dp ,
fa_transaction_headers th ,
fa_asset_history ah ,
fa_additions ad ,
fa_book_controls bc
WHERE ah.asset_id = ad.asset_id
AND fb.book_type_code = bc.book_type_code
AND fb.asset_id = ad.asset_id
AND fb.transaction_header_id_in = th.transaction_header_id
AND dp.book_type_code = bc.book_type_code
AND cp.calendar_type = bc.deprn_calendar
AND th.asset_id = ad.asset_id
AND th.book_type_code= dp.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.transaction_date_entered between cp.start_date and cp.end_date
AND th.date_effective between dp.period_open_date and nvl(dp.period_close_date,th.date_effective)
AND bc.book_type_code = p_book_type_code
AND ad.asset_id = p_asset_id
AND dp.period_counter <= p_period_counter;
SELECT fb.cost
INTO p_asset_cost
FROM fa_books_mrc_v fb ,
fa_calendar_periods cp ,
fa_deprn_periods_mrc_v dp ,
fa_transaction_headers th ,
fa_asset_history ah ,
fa_additions ad ,
fa_book_controls_mrc_v bc
WHERE ah.asset_id = ad.asset_id
AND fb.book_type_code = bc.book_type_code
AND fb.asset_id = ad.asset_id
AND fb.transaction_header_id_in = th.transaction_header_id
AND dp.book_type_code = bc.book_type_code
AND cp.calendar_type = bc.deprn_calendar
AND th.asset_id = ad.asset_id
AND th.book_type_code= dp.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.transaction_date_entered between cp.start_date and cp.end_date
AND th.date_effective between dp.period_open_date and nvl(dp.period_close_date,th.date_effective)
AND bc.book_type_code = p_book_type_code
AND ad.asset_id = p_asset_id
AND dp.period_counter = l_period_counter
AND th.transaction_header_id = l_transaction_header_id;
SELECT fb.cost
INTO p_asset_cost
FROM fa_books fb ,
fa_calendar_periods cp ,
fa_deprn_periods dp ,
fa_transaction_headers th ,
fa_asset_history ah ,
fa_additions ad ,
fa_book_controls bc
WHERE ah.asset_id = ad.asset_id
AND fb.book_type_code = bc.book_type_code
AND fb.asset_id = ad.asset_id
AND fb.transaction_header_id_in = th.transaction_header_id
AND dp.book_type_code = bc.book_type_code
AND cp.calendar_type = bc.deprn_calendar
AND th.asset_id = ad.asset_id
AND th.book_type_code= dp.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.transaction_date_entered between cp.start_date and cp.end_date
AND th.date_effective between dp.period_open_date and nvl(dp.period_close_date,th.date_effective)
AND bc.book_type_code = p_book_type_code
AND ad.asset_id = p_asset_id
AND dp.period_counter = l_period_counter
AND th.transaction_header_id = l_transaction_header_id;
SELECT faa.description
INTO p_asset_desc
FROM fa_additions faa
WHERE faa.asset_number = p_asset_number;
SELECT nvl(sum(decode(debit_credit_flag,'DR',adjustment_amount,0)),0)
INTO p_adjustment
FROM fa_adjustments_mrc_v
WHERE book_type_code = p_book_type_code
AND asset_id = p_asset_id
AND period_counter_created = p_period_counter
AND source_type_code = l_source_type_code
AND adjustment_type = p_adjustment_type;
SELECT nvl(sum(decode(debit_credit_flag,'DR',adjustment_amount,-1 * adjustment_amount)),0)
INTO p_adjustment
FROM fa_adjustments_mrc_v
WHERE book_type_code = p_book_type_code
AND asset_id = p_asset_id
AND period_counter_created = p_period_counter
AND source_type_code = l_source_type_code
AND adjustment_type = p_adjustment_type;
SELECT nvl(sum(decode(debit_credit_flag,'DR',adjustment_amount,0)),0)
INTO p_adjustment
FROM fa_adjustments
WHERE book_type_code = p_book_type_code
AND asset_id = p_asset_id
AND period_counter_created = p_period_counter
AND source_type_code = l_source_type_code
AND adjustment_type = p_adjustment_type;
SELECT nvl(sum(decode(debit_credit_flag,'DR',adjustment_amount,-1 * adjustment_amount)),0)
INTO p_adjustment
FROM fa_adjustments
WHERE book_type_code = p_book_type_code
AND asset_id = p_asset_id
AND period_counter_created = p_period_counter
AND source_type_code = l_source_type_code
AND adjustment_type = p_adjustment_type;
SELECT nvl(bk.cost,0)
INTO p_historical_cost_begin_period
FROM fa_books_mrc_v bk,
fa_deprn_periods_mrc_v dp
WHERE bk.book_type_code = p_hist_book_type_code
AND bk.asset_id = p_asset_id
AND bk.book_type_code = dp.book_type_code
AND dp.period_open_date between bk.date_effective and nvl(bk.date_ineffective,dp.period_open_date)
AND dp.period_counter = p_period_counter_from;
SELECT NVL(a.deprn_reserve - a.ytd_deprn,0)
INTO p_accum_depr_begin_period
FROM fa_deprn_summary_mrc_v a
WHERE a.book_type_code = p_hist_book_type_code
AND a.asset_id = p_asset_id
AND a.period_counter between p_period_counter_from AND p_period_counter_to
AND a.deprn_source_code = 'BOOKS';
SELECT NVL(a.deprn_reserve,0)
INTO p_accum_depr_begin_period
FROM fa_deprn_summary_mrc_v a
WHERE a.book_type_code = p_hist_book_type_code
AND a.asset_id = p_asset_id
AND a.period_counter in (SELECT max(b.period_counter)
FROM fa_deprn_summary_mrc_v b
WHERE b.book_type_code = p_hist_book_type_code
AND b.asset_id = p_asset_id
AND b.period_counter <= p_period_counter_from - 1);
SELECT nvl(bk.cost,0)
INTO p_historical_cost_end_period
FROM fa_books_mrc_v bk,
fa_deprn_periods_mrc_v dp
WHERE bk.book_type_code = p_hist_book_type_code
AND bk.asset_id = p_asset_id
AND bk.book_type_code = dp.book_type_code
AND dp.period_close_date between bk.date_effective and nvl(bk.date_ineffective,dp.period_close_date)
AND dp.period_counter = p_period_counter_to;
SELECT nvl(bk.cost,0)
INTO p_adjusted_cost_end_period
FROM fa_books_mrc_v bk,
fa_deprn_periods_mrc_v dp
WHERE bk.book_type_code = p_adj_book_type_code
AND bk.asset_id = p_asset_id
AND bk.book_type_code = dp.book_type_code
AND dp.period_close_date between bk.date_effective and nvl(bk.date_ineffective,dp.period_close_date)
AND dp.period_counter = p_period_counter_to;
SELECT NVL(a.deprn_reserve,0)
INTO p_hist_accum_depr_end_period
FROM fa_deprn_summary_mrc_v a
WHERE a.book_type_code = p_hist_book_type_code
AND a.asset_id = p_asset_id
AND a.period_counter in (SELECT max(b.period_counter)
FROM fa_deprn_summary_mrc_v b
WHERE b.book_type_code = p_hist_book_type_code
AND b.asset_id = p_asset_id
AND b.period_counter <= p_period_counter_to);
SELECT NVL(a.deprn_reserve,0)
INTO p_adj_accum_depr_end_period
FROM fa_deprn_summary_mrc_v a
WHERE a.book_type_code = p_adj_book_type_code
AND a.asset_id = p_asset_id
AND a.period_counter in (SELECT max(b.period_counter)
FROM fa_deprn_summary_mrc_v b
WHERE b.book_type_code = p_adj_book_type_code
AND b.asset_id = p_asset_id
AND b.period_counter <= p_period_counter_to);
SELECT NVL(a.ytd_deprn,0)
INTO l_depr_rpt_period
FROM fa_deprn_summary_mrc_v a
WHERE a.book_type_code = p_hist_book_type_code
AND a.asset_id = p_asset_id
AND a.deprn_source_code = 'BOOKS'
AND a.period_counter between p_period_counter_from and p_period_counter_to;
SELECT NVL(SUM(NVL(a.deprn_amount,0)),0)
INTO p_depr_rpt_period
FROM fa_deprn_summary_mrc_v a
WHERE a.book_type_code = p_hist_book_type_code
AND a.asset_id = p_asset_id
AND a.deprn_source_code = 'DEPRN'
AND a.period_counter between p_period_counter_from and p_period_counter_to;
SELECT nvl(bk.cost,0)
INTO p_historical_cost_begin_period
FROM fa_books bk,
fa_deprn_periods dp
WHERE bk.book_type_code = p_hist_book_type_code
AND bk.asset_id = p_asset_id
AND bk.book_type_code = dp.book_type_code
AND dp.period_open_date between bk.date_effective and nvl(bk.date_ineffective,dp.period_open_date)
AND dp.period_counter = p_period_counter_from;
SELECT NVL(a.deprn_reserve - a.ytd_deprn,0)
INTO p_accum_depr_begin_period
FROM fa_deprn_summary a
WHERE a.book_type_code = p_hist_book_type_code
AND a.asset_id = p_asset_id
AND a.period_counter between p_period_counter_from AND p_period_counter_to
AND a.deprn_source_code = 'BOOKS';
SELECT NVL(a.deprn_reserve,0)
INTO p_accum_depr_begin_period
FROM fa_deprn_summary a
WHERE a.book_type_code = p_hist_book_type_code
AND a.asset_id = p_asset_id
AND a.period_counter in (SELECT max(b.period_counter)
FROM fa_deprn_summary b
WHERE b.book_type_code = p_hist_book_type_code
AND b.asset_id = p_asset_id
AND b.period_counter <= p_period_counter_from - 1);
SELECT nvl(bk.cost,0)
INTO p_historical_cost_end_period
FROM fa_books bk,
fa_deprn_periods dp
WHERE bk.book_type_code = p_hist_book_type_code
AND bk.asset_id = p_asset_id
AND bk.book_type_code = dp.book_type_code
AND dp.period_close_date between bk.date_effective and nvl(bk.date_ineffective,dp.period_close_date)
AND dp.period_counter = p_period_counter_to;
SELECT nvl(bk.cost,0)
INTO p_adjusted_cost_end_period
FROM fa_books bk,
fa_deprn_periods dp
WHERE bk.book_type_code = p_adj_book_type_code
AND bk.asset_id = p_asset_id
AND bk.book_type_code = dp.book_type_code
AND dp.period_close_date between bk.date_effective and nvl(bk.date_ineffective,dp.period_close_date)
AND dp.period_counter = p_period_counter_to;
SELECT NVL(a.deprn_reserve,0)
INTO p_hist_accum_depr_end_period
FROM fa_deprn_summary a
WHERE a.book_type_code = p_hist_book_type_code
AND a.asset_id = p_asset_id
AND a.period_counter in (SELECT max(b.period_counter)
FROM fa_deprn_summary b
WHERE b.book_type_code = p_hist_book_type_code
AND b.asset_id = p_asset_id
AND b.period_counter <= p_period_counter_to);
SELECT NVL(a.deprn_reserve,0)
INTO p_adj_accum_depr_end_period
FROM fa_deprn_summary a
WHERE a.book_type_code = p_adj_book_type_code
AND a.asset_id = p_asset_id
AND a.period_counter in (SELECT max(b.period_counter)
FROM fa_deprn_summary b
WHERE b.book_type_code = p_adj_book_type_code
AND b.asset_id = p_asset_id
AND b.period_counter <= p_period_counter_to);
SELECT NVL(a.ytd_deprn,0)
INTO l_depr_rpt_period
FROM fa_deprn_summary a
WHERE a.book_type_code = p_hist_book_type_code
AND a.asset_id = p_asset_id
AND a.deprn_source_code = 'BOOKS'
AND a.period_counter between p_period_counter_from and p_period_counter_to;
SELECT NVL(SUM(NVL(a.deprn_amount,0)),0)
INTO p_depr_rpt_period
FROM fa_deprn_summary a
WHERE a.book_type_code = p_hist_book_type_code
AND a.asset_id = p_asset_id
AND a.deprn_source_code = 'DEPRN'
AND a.period_counter between p_period_counter_from and p_period_counter_to;
SELECT ah.category_id
INTO l_category_id
FROM fa_asset_history ah,
fa_deprn_periods dp
WHERE ah.asset_id = p_asset_id
AND dp.book_type_code = p_adj_book_type_code
AND dp.period_counter = p_period_counter_from
AND dp.period_close_date between ah.date_effective and nvl(ah.date_ineffective, dp.period_close_date);
SELECT price_index_id
INTO l_price_index
FROM fa_category_book_defaults a, fa_price_indexes b
WHERE a.book_type_code = p_adj_book_type_code
AND a.category_id = l_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 rev.reval_date
INTO l_reval_date
FROM fa_mass_revaluations rev,
fa_deprn_periods dp
WHERE dp.period_counter = p_period_counter_from
AND dp.book_type_code = p_adj_book_type_code
AND dp.book_type_code = rev.book_type_code
AND dp.calendar_period_open_date <= rev.reval_date
AND rev.reval_date <= nvl(dp.calendar_period_close_date, rev.reval_date)
AND rev.status = 'COMPLETED';
SELECT price_index_value
INTO l_index_value_from
FROM fa_price_index_values
WHERE price_index_id = l_price_index
AND l_reval_date BETWEEN from_date AND nvl(to_date,l_reval_date);
SELECT max(rev.reval_date)
INTO l_reval_date
FROM fa_mass_revaluations rev,
fa_deprn_periods dp
WHERE dp.period_counter between p_period_counter_from and p_period_counter_to
AND dp.book_type_code = p_adj_book_type_code
AND dp.book_type_code = rev.book_type_code
AND dp.calendar_period_open_date <= rev.reval_date
AND rev.reval_date <= nvl(dp.calendar_period_close_date, rev.reval_date)
AND rev.status = 'COMPLETED';
SELECT price_index_value
INTO l_index_value_to
FROM fa_price_index_values
WHERE price_index_id = l_price_index
AND l_reval_date BETWEEN from_date AND nvl(to_date,l_reval_date);
SELECT bk.cost,
bk.period_counter_fully_retired
INTO l_hist_cost_retirement,
l_period_counter_fully_retired
FROM fa_books_mrc_v bk, fa_transaction_headers th
WHERE bk.book_type_code = p_hist_book_type_code
AND bk.asset_id = p_asset_id
AND bk.period_counter_fully_retired = (SELECT max(bk1.period_counter_fully_retired)
FROM fa_books bk1
WHERE bk1.book_type_code = bk.book_type_code
AND bk1.asset_id = bk.asset_id
AND bk1.period_counter_fully_retired
between p_period_counter_from and p_period_counter_to)
AND bk.book_type_code = th.book_type_code
AND bk.asset_id = th.asset_id
AND bk.transaction_header_id_out = th.transaction_header_id
AND th.transaction_type_code = 'FULL RETIREMENT';
SELECT bk.cost,
bk.date_placed_in_service
INTO l_adj_cost_retirement,
l_date_placed_in_service
FROM fa_books_mrc_v bk, fa_transaction_headers th
WHERE bk.book_type_code = p_adj_book_type_code
AND bk.asset_id = p_asset_id
AND bk.period_counter_fully_retired = (SELECT max(bk1.period_counter_fully_retired)
FROM fa_books bk1
WHERE bk1.book_type_code = bk.book_type_code
AND bk1.asset_id = bk.asset_id
AND bk1.period_counter_fully_retired
between p_period_counter_from and p_period_counter_to)
AND bk.book_type_code = th.book_type_code
AND bk.asset_id = th.asset_id
AND bk.transaction_header_id_out = th.transaction_header_id
AND th.transaction_type_code = 'FULL RETIREMENT';
SELECT bk.cost,
bk.period_counter_fully_retired
INTO l_hist_cost_retirement,
l_period_counter_fully_retired
FROM fa_books bk, fa_transaction_headers th
WHERE bk.book_type_code = p_hist_book_type_code
AND bk.asset_id = p_asset_id
AND bk.period_counter_fully_retired = (SELECT max(bk1.period_counter_fully_retired)
FROM fa_books bk1
WHERE bk1.book_type_code = bk.book_type_code
AND bk1.asset_id = bk.asset_id
AND bk1.period_counter_fully_retired
between p_period_counter_from and p_period_counter_to)
AND bk.book_type_code = th.book_type_code
AND bk.asset_id = th.asset_id
AND bk.transaction_header_id_out = th.transaction_header_id
AND th.transaction_type_code = 'FULL RETIREMENT';
SELECT bk.cost,
bk.date_placed_in_service
INTO l_adj_cost_retirement,
l_date_placed_in_service
FROM fa_books bk, fa_transaction_headers th
WHERE bk.book_type_code = p_adj_book_type_code
AND bk.asset_id = p_asset_id
AND bk.period_counter_fully_retired = (SELECT max(bk1.period_counter_fully_retired)
FROM fa_books bk1
WHERE bk1.book_type_code = bk.book_type_code
AND bk1.asset_id = bk.asset_id
AND bk1.period_counter_fully_retired
between p_period_counter_from and p_period_counter_to)
AND bk.book_type_code = th.book_type_code
AND bk.asset_id = th.asset_id
AND bk.transaction_header_id_out = th.transaction_header_id
AND th.transaction_type_code = 'FULL RETIREMENT';
SELECT exb.exhibit_group_id
INTO p_exhibit_group_id
FROM jl_ar_fa_exhibit_groups exb
WHERE exb.cip_group = 'Y';
SELECT cat.global_attribute16
INTO p_exhibit_group_id
FROM fa_category_books cat
WHERE cat.book_type_code = p_corp_book
AND cat.category_id = l_category_id;
SELECT ah.category_id,
ah.asset_type
INTO p_old_category_id,
l_asset_type
FROM fa_asset_history ah
WHERE ah.asset_id = p_asset_id
AND ah.transaction_header_id_out = p_transaction_header_id;
SELECT exb.exhibit_group_id
INTO l_cip_exhibit_group_id
FROM jl_ar_fa_exhibit_groups exb
WHERE exb.cip_group = 'Y';
PROCEDURE insert_db_records IS
row_count BINARY_INTEGER;
l_api_name CONSTANT VARCHAR2(30) := 'INSERT_DB_RECORDS';
insert into JL_AR_FA_EXHIBIT_REPORT ( conc_request_id
, exhibit_group_id
, category_id
, asset_id
, begin_cost
, additions
, retirements
, transfers
, end_cost
, begin_accum_depr
, accum_depr_retirements
, accum_depr_transfers
, accum_depr_rpt_period
, deprn_reserve
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
)
values ( report_table(row_count).conc_request_id
, report_table(row_count).exhibit_group_id
, report_table(row_count).category_id
, report_table(row_count).asset_id
, report_table(row_count).begin_cost
, report_table(row_count).additions
, report_table(row_count).retirements
, report_table(row_count).transfers
, report_table(row_count).end_cost
, report_table(row_count).begin_accum_depr
, report_table(row_count).accum_depr_retirements
, report_table(row_count).accum_depr_transfers
, report_table(row_count).accum_depr_rpt_period
, report_table(row_count).deprn_reserve
, sysdate
, fnd_global.user_id
, sysdate
, fnd_global.user_id
, fnd_global.user_id
);
END insert_db_records;
delete from JL_AR_FA_EXHIBIT_REPORT;
SELECT th.asset_id,
ah.category_id,
dp.period_counter,
th.book_type_code,
th.transaction_type_code,
th.transaction_header_id,
ah.asset_type,
nvl (ah.units,0)
FROM fa_transaction_headers th,
fa_deprn_periods_mrc_v dp,
fa_asset_history ah
WHERE dp.book_type_code = p_corp_book
AND dp.period_counter between p_period_counter_from and p_period_counter_to
AND th.book_type_code = dp.book_type_code
AND dp.period_open_date <= th.date_effective
AND th.date_effective <= dp.period_close_date
AND th.transaction_type_code IN ('TRANSFER OUT',
'RECLASS',
'CIP RECLASS',
-- 'TRANSFER',
-- 'CIP TRANSFER',
'UNIT ADJUSTMENT',
'ADDITION',
'CIP ADDITION',
'ADJUSTMENT',
'CIP ADJUSTMENT',
'FULL RETIREMENT',
'PARTIAL RETIREMENT',
'REINSTATEMENT',
-- 'TRANSFER IN',
'ADDITION/VOID'
)
AND ah.asset_id = th.asset_id
AND ah.date_effective <= th.date_effective
AND th.date_effective < nvl (ah.date_ineffective, th.date_effective + 1)
UNION
SELECT ah.asset_id,
ah.category_id,
dp.period_counter - 1,
dp.book_type_code,
null,
-1,
ah.asset_type,
nvl (ah.units,0)
FROM fa_books bk,
fa_deprn_periods_mrc_v dp,
fa_asset_history ah
WHERE dp.book_type_code = p_corp_book
AND bk.book_type_code = dp.book_type_code
AND bk.asset_id = ah.asset_id
AND dp.period_counter = p_period_counter_from
AND dp.period_open_date between ah.date_effective AND nvl(ah.date_ineffective,dp.period_open_date)
AND dp.period_open_date between bk.date_effective AND nvl(bk.date_ineffective,dp.period_open_date)
ORDER BY 1,3,5;
SELECT th.asset_id,
ah.category_id,
dp.period_counter,
th.book_type_code,
th.transaction_type_code,
th.transaction_header_id,
ah.asset_type,
nvl (ah.units,0)
FROM fa_transaction_headers th,
fa_deprn_periods dp,
fa_asset_history ah
WHERE dp.book_type_code = p_corp_book
AND dp.period_counter between p_period_counter_from and p_period_counter_to
AND th.book_type_code = dp.book_type_code
AND dp.period_open_date <= th.date_effective
AND th.date_effective <= dp.period_close_date
AND th.transaction_type_code IN ('TRANSFER OUT',
'RECLASS',
'CIP RECLASS',
-- 'TRANSFER',
-- 'CIP TRANSFER',
'UNIT ADJUSTMENT',
'ADDITION',
'CIP ADDITION',
'ADJUSTMENT',
'CIP ADJUSTMENT',
'FULL RETIREMENT',
'PARTIAL RETIREMENT',
'REINSTATEMENT',
-- 'TRANSFER IN',
'ADDITION/VOID'
)
AND ah.asset_id = th.asset_id
AND ah.date_effective <= th.date_effective
AND th.date_effective < nvl (ah.date_ineffective, th.date_effective + 1)
UNION
SELECT ah.asset_id,
ah.category_id,
dp.period_counter - 1,
dp.book_type_code,
null,
-1,
ah.asset_type,
nvl (ah.units,0)
FROM fa_books bk,
fa_deprn_periods dp,
fa_asset_history ah
WHERE dp.book_type_code = p_corp_book
AND bk.book_type_code = dp.book_type_code
AND bk.asset_id = ah.asset_id
AND dp.period_counter = p_period_counter_from
AND dp.period_open_date between ah.date_effective AND nvl(ah.date_ineffective,dp.period_open_date)
AND dp.period_open_date between bk.date_effective AND nvl(bk.date_ineffective,dp.period_open_date)
ORDER BY 1,3,5;
insert_db_records;
report_table.delete;
SELECT ah.category_id
INTO l_current_category_id
FROM fa_asset_history ah,
fa_deprn_periods dp
WHERE ah.asset_id = asset_txns_rec.asset_id
AND dp.book_type_code = p_corp_book
AND dp.period_close_date between ah.date_effective and nvl(ah.date_ineffective,dp.period_close_date)
AND dp.period_counter = p_period_counter_to;
SELECT 'Y'
INTO l_ignore_retirement
FROM fa_retirements ret
WHERE ret.book_type_code = asset_txns_rec.book_type_code
AND ret.asset_id = asset_txns_rec.asset_id
AND ret.transaction_header_id_in = asset_txns_rec.transaction_header_id
AND ret.status = 'DELETED'
AND EXISTS (SELECT th.transaction_header_id
FROM fa_transaction_headers th,
fa_deprn_periods dp
WHERE dp.book_type_code = asset_txns_rec.book_type_code
AND dp.period_counter between p_period_counter_from and p_period_counter_to
AND th.book_type_code = dp.book_type_code
AND dp.period_open_date <= th.date_effective
AND th.date_effective <= dp.period_close_date
AND th.transaction_header_id = ret.transaction_header_id_out);
SELECT 'Y'
INTO l_ignore_reinstatement
FROM fa_retirements ret
WHERE ret.book_type_code = asset_txns_rec.book_type_code
AND ret.asset_id = asset_txns_rec.asset_id
AND ret.transaction_header_id_out = asset_txns_rec.transaction_header_id
AND ret.status = 'DELETED'
AND EXISTS (SELECT th.transaction_header_id
FROM fa_transaction_headers th,
fa_deprn_periods dp
WHERE dp.book_type_code = asset_txns_rec.book_type_code
AND dp.period_counter between p_period_counter_from and p_period_counter_to
AND th.book_type_code = dp.book_type_code
AND dp.period_open_date <= th.date_effective
AND th.date_effective <= dp.period_close_date
AND th.transaction_header_id = ret.transaction_header_id_in);
insert_db_records;