[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_data_adj(p_adjustment_id in igi_iac_adjustments.adjustment_id%type,
p_book_type_code in igi_iac_adjustments.book_type_code%type,
p_code_combination_id in igi_iac_adjustments.code_combination_id%type,
p_set_of_books_id in igi_iac_adjustments.set_of_books_id%type,
p_dr_cr_flag in igi_iac_adjustments.dr_cr_flag%type,
p_amount in igi_iac_adjustments.amount%type,
p_adjustment_type in igi_iac_adjustments.adjustment_type%type,
p_units_assigned in igi_iac_adjustments.units_assigned%type,
p_asset_id in igi_iac_adjustments.asset_id%type,
p_distribution_id in igi_iac_adjustments.distribution_id%type,
p_period_counter in igi_iac_adjustments.period_counter%type,
p_adj_offset_type IN igi_iac_adjustments.adjustment_offset_type%TYPE,
p_report_ccid IN igi_iac_adjustments.report_ccid%TYPE,
p_event_id IN number
)
IS
l_rowid ROWID;
l_path := g_path||'insert_data_adj';
IGI_IAC_ADJUSTMENTS_PKG.insert_row(
x_rowid =>l_rowid,
x_adjustment_id =>p_adjustment_id,
x_book_type_code =>p_book_type_code,
x_code_combination_id =>p_code_combination_id,
x_set_of_books_id =>p_set_of_books_id,
x_dr_cr_flag =>p_dr_cr_flag,
x_amount =>p_amount,
x_adjustment_type =>p_adjustment_type,
x_adjustment_offset_type =>p_adj_offset_type,
x_transfer_to_gl_flag =>'Y',
x_units_assigned =>p_units_assigned,
x_asset_id =>p_asset_id,
x_distribution_id =>p_distribution_id,
x_period_counter =>p_period_counter,
x_report_ccid =>p_report_ccid,
x_mode =>'R',
x_event_id => p_event_id
);
END insert_data_adj;
PROCEDURE insert_data_det(p_adjustment_id in IGI_IAC_DET_BALANCES.adjustment_id%type,
p_asset_id in IGI_IAC_DET_BALANCES.asset_id%type,
p_distribution_id in IGI_IAC_DET_BALANCES.distribution_id%type,
p_period_counter in IGI_IAC_DET_BALANCES.period_counter%type,
p_book_type_code in IGI_IAC_DET_BALANCES.book_type_code%type,
p_adjusted_cost in IGI_IAC_DET_BALANCES.adjustment_cost%type,
p_net_book_value in IGI_IAC_DET_BALANCES.net_book_value%type,
p_reval_reserve in IGI_IAC_DET_BALANCES.reval_reserve_cost%type,
p_reval_reserve_gen_fund in IGI_IAC_DET_BALANCES.reval_reserve_gen_fund%type,
p_reval_reserve_backlog in IGI_IAC_DET_BALANCES.reval_reserve_backlog%type,
p_op_acct in IGI_IAC_DET_BALANCES.operating_acct_cost%type,
p_deprn_reserve in IGI_IAC_DET_BALANCES.deprn_reserve%type,
p_deprn_reserve_backlog in IGI_IAC_DET_BALANCES.deprn_reserve_backlog%type,
p_deprn_ytd in IGI_IAC_DET_BALANCES.deprn_ytd%type,
p_deprn_period in IGI_IAC_DET_BALANCES.deprn_period%type,
p_gen_fund_acc in IGI_IAC_DET_BALANCES.general_fund_acc%type,
p_gen_fund_per in IGI_IAC_DET_BALANCES.general_fund_acc%type,
p_current_reval_factor in IGI_IAC_DET_BALANCES.current_reval_factor%type,
p_cumulative_reval_factor in IGI_IAC_DET_BALANCES.cumulative_reval_factor%type,
p_reval_flag in IGI_IAC_DET_BALANCES.active_flag%type,
p_op_acct_ytd in IGI_IAC_DET_BALANCES.operating_acct_ytd%type,
p_operating_acct_backlog in IGI_IAC_DET_BALANCES.operating_acct_backlog%type,
p_last_reval_date in IGI_IAC_DET_BALANCES.last_reval_date%type
)
IS
l_rowid VARCHAR2(25);
l_path := g_path||'insert_data_det';
IGI_IAC_DET_BALANCES_PKG.insert_row(
x_rowid =>l_rowid,
x_adjustment_id =>p_adjustment_id,
x_asset_id =>p_asset_id,
x_distribution_id =>p_distribution_id,
x_book_type_code =>p_book_type_code,
x_period_counter =>p_period_counter,
x_adjustment_cost =>p_adjusted_cost,
x_net_book_value =>p_net_book_value,
x_reval_reserve_cost =>(p_reval_reserve+p_reval_reserve_backlog+p_reval_reserve_gen_fund),
x_reval_reserve_backlog =>p_reval_reserve_backlog,
x_reval_reserve_gen_fund =>p_reval_reserve_gen_fund,
x_reval_reserve_net =>p_reval_reserve,
x_operating_acct_cost =>(p_op_acct+p_operating_acct_backlog),
x_operating_acct_backlog =>p_operating_acct_backlog,
x_operating_acct_net =>p_op_acct,
x_operating_acct_ytd =>p_op_acct_ytd,
x_deprn_period =>p_deprn_period,
x_deprn_ytd =>p_deprn_ytd,
x_deprn_reserve =>p_deprn_reserve,
x_deprn_reserve_backlog =>p_deprn_reserve_backlog,
x_general_fund_per =>p_gen_fund_per,
x_general_fund_acc =>p_gen_fund_acc,
x_last_reval_date =>p_last_reval_date,
x_current_reval_factor =>p_current_reval_factor,
x_cumulative_reval_factor =>p_cumulative_reval_factor,
x_active_flag =>p_reval_flag,
x_mode =>'R'
);
END insert_data_det;
select book_type_code into l_book_type_code from FA_DISTRIBUTION_HISTORY
where DISTRIBUTION_ID=p_dist_id;
select book_type_code into l_book_type_code from FA_DISTRIBUTION_HISTORY
where DISTRIBUTION_ID=p_dist_id;
select book_type_code into l_book_type_code from FA_DISTRIBUTION_HISTORY
where DISTRIBUTION_ID=p_dist_id;
SELECT iab.asset_id,
iab.book_type_code,
iab.period_counter,
iab.net_book_value,
iab.adjusted_cost,
iab.operating_acct,
iab.reval_reserve,
iab.deprn_amount,
iab.deprn_reserve,
iab.backlog_deprn_reserve,
iab.general_fund,
iab.last_reval_date,
iab.current_reval_factor,
iab.cumulative_reval_factor
FROM igi_iac_asset_balances iab,
(SELECT a.book_type_code,
a.asset_id,
max(a.period_counter) period_counter
FROM igi_iac_asset_balances a
WHERE a.asset_id= p_asset_id
AND a.book_type_code= p_book_type_code
GROUP BY a.book_type_code, a.asset_id) mpc
WHERE iab.asset_id= p_asset_id
AND iab.book_type_code= p_book_type_code
AND iab.asset_id = mpc.asset_id
AND iab.book_type_code = mpc.book_type_code
AND iab.period_counter = mpc.period_counter;
SELECT nvl(sum(iadb.reval_reserve_backlog), 0) reval_reserve_backlog,
nvl(sum(iadb.operating_acct_backlog), 0) operating_acct_backlog,
nvl(sum(iadb.general_fund_per), 0) general_fund_per,
nvl(sum(iadb.deprn_ytd), 0) ytd_deprn
FROM igi_iac_det_balances iadb
WHERE iadb.adjustment_id = cp_adjustment_id
AND iadb.active_flag IS NULL;
SELECT nvl(sum(iadb.deprn_period), 0) deprn_period,
nvl(sum(iadb.deprn_reserve), 0) deprn_reserve,
nvl(sum(iadb.deprn_ytd), 0) deprn_ytd
FROM igi_iac_fa_deprn iadb
WHERE iadb.adjustment_id = cp_adjustment_id
AND iadb.active_flag IS NULL;
SELECT sum(deprn_amount-deprn_adjustment_amount ) deprn_amount
FROM fa_deprn_detail
WHERE book_type_code = p_book_type_code
AND period_counter = cp_period_counter
AND asset_id = p_asset_id;
SELECT nvl(sum(decode(aj.dr_cr_flag, 'CR', 1, -1) *AJ.Amount), 0) op_expense_amt
FROM igi_iac_adjustments aj,
igi_iac_transaction_headers ith
WHERE aj.asset_id = cp_asset_id
AND aj.book_type_code = cp_book_type_code
AND aj.period_counter BETWEEN cp_tfr_prd_counter AND cp_cur_prd_counter
AND aj.adjustment_type = 'OP EXPENSE'
AND aj.adjustment_id = ith.adjustment_id
AND ith.transaction_type_code = 'REVALUATION'
AND ith.adjustment_status NOT IN ('PREVIEW', 'OBSOLETE');
-- Select all inactive distributions created by the previous transaction (keep)
CURSOR c_inactive_dist(c_adjustment_id IGI_IAC_TRANSACTION_HEADERS.adjustment_id%TYPE)
IS
SELECT *
FROM igi_iac_det_balances
WHERE adjustment_id=c_adjustment_id
AND book_type_code = p_book_type_code
AND asset_id = p_asset_id
AND active_flag='N';
SELECT *
FROM igi_iac_fa_deprn
WHERE adjustment_id = cp_adjustment_id
AND book_type_code = p_book_type_code
AND asset_id = p_asset_id
AND active_flag = 'N';
insert_data_det(p_new_adj_id,
l_inactive_dist.asset_id,
l_inactive_dist.distribution_id,
p_curr_prd_cntr,
l_inactive_dist.book_type_code,
l_inactive_dist.adjustment_cost,
l_inactive_dist.net_book_value,
l_inactive_dist.reval_reserve_net,
l_inactive_dist.general_fund_acc,
l_inactive_dist.reval_reserve_backlog,
l_inactive_dist.operating_acct_net,
l_inactive_dist.deprn_reserve,
l_inactive_dist.deprn_reserve_backlog,
l_inactive_dist.deprn_ytd,
l_inactive_dist.deprn_period,
l_inactive_dist.general_fund_acc,
l_inactive_dist.general_fund_per,
l_inactive_dist.current_reval_factor,
l_inactive_dist.cumulative_reval_factor,
l_inactive_dist.active_flag,
l_inactive_dist.operating_acct_ytd,
l_inactive_dist.operating_acct_backlog,
l_inactive_dist.last_reval_date
);
igi_iac_fa_deprn_pkg.insert_row(
x_rowid => l_rowid,
x_book_type_code => l_fa_inactive_dist.book_type_code,
x_asset_id => l_fa_inactive_dist.asset_id,
x_distribution_id => l_fa_inactive_dist.distribution_id,
x_period_counter => p_curr_prd_cntr,
x_adjustment_id => p_new_adj_id,
x_deprn_period => l_fa_inactive_dist.deprn_period,
x_deprn_ytd => l_fa_inactive_dist.deprn_ytd ,
x_deprn_reserve => l_fa_inactive_dist.deprn_reserve,
x_active_flag => l_fa_inactive_dist.active_flag,
x_mode => 'R');
-- Select all distributions for an asset that are active and the distribution
-- impacted by the transfer (keep)
CURSOR c_all_dist
IS
SELECT distribution_id,
transaction_header_id_in,
units_assigned,
transaction_header_id_out,
transaction_units
FROM fa_distribution_history
WHERE asset_id=p_asset_hdr_rec.asset_id
AND book_type_code=p_asset_hdr_rec.book_type_code
AND (transaction_header_id_out IS NULL OR
transaction_header_id_out=p_trans_rec.transaction_header_id);
SELECT count(*) counter
FROM fa_distribution_history
WHERE asset_id=p_asset_hdr_rec.asset_id
AND book_type_code=p_asset_hdr_rec.book_type_code
-- AND transaction_header_id_in=p_trans_rec.transaction_header_id;
SELECT count(*) counter
FROM fa_distribution_history
WHERE asset_id=p_asset_hdr_rec.asset_id
AND book_type_code=p_asset_hdr_rec.book_type_code
AND transaction_header_id_in=p_trans_rec.transaction_header_id;
SELECT count(*) counter
FROM fa_distribution_history
WHERE asset_id=p_asset_hdr_rec.asset_id
AND book_type_code=p_asset_hdr_rec.book_type_code
AND transaction_header_id_out=p_trans_rec.transaction_header_id;
SELECT period_counter
FROM fa_deprn_periods
WHERE c_trx_date BETWEEN period_open_date AND period_close_date
AND book_type_code=p_asset_hdr_rec.book_type_code;
SELECT units
FROM fa_asset_history
WHERE asset_id = p_asset_hdr_rec.asset_id
AND transaction_header_id_out IS NULL;
SELECT sum(units_assigned)
FROM fa_distribution_history
WHERE asset_id=p_asset_hdr_rec.asset_id
AND book_type_code=p_asset_hdr_rec.book_type_code
AND transaction_header_id_in=p_trans_rec.transaction_header_id;
-- select the start period number for a given fiscal year (keep)
CURSOR c_start_period_counter(c_fiscal_year FA_DEPRN_PERIODS.fiscal_year%TYPE)
IS
SELECT (number_per_fiscal_year*c_fiscal_year)+1
FROM fa_calendar_types
WHERE calendar_type=(SELECT deprn_calendar
FROM fa_book_controls
WHERE book_type_code=p_asset_hdr_rec.book_type_code);
SELECT asset_number
FROM fa_additions
WHERE asset_id=p_asset_hdr_rec.asset_id;
SELECT cost,
salvage_value,
period_counter_fully_reserved
FROM fa_books
WHERE asset_id = p_asset_id
AND book_type_code = p_book_type_code
AND date_ineffective IS NULL
AND transaction_header_id_out IS NULL;
SELECT count(*) interco_count
FROM FA_ADJUSTMENTS adj,
FA_BOOK_CONTROLS bc
WHERE adj.book_type_code = p_book_type_code
AND adj.ASSET_ID = p_asset_id
AND adj.SOURCE_TYPE_CODE = 'TRANSFER'
AND adj.ADJUSTMENT_TYPE IN ('INTERCO AP','INTERCO AR')
AND adj.TRANSACTION_HEADER_ID=p_transaction_header_id
AND bc.book_type_code = p_book_type_code
AND nvl(bc.intercompany_posting_flag,'Y') = 'Y';
-- Cursor to select the fa amounts for a distribution (keep)
CURSOR c_fa_dist_data(cp_adjustment_id igi_iac_fa_deprn.adjustment_id%TYPE,
cp_dist_id igi_iac_fa_deprn.distribution_id%TYPE)
IS
SELECT *
FROM igi_iac_fa_deprn
WHERE book_type_code = p_asset_hdr_rec.book_type_code
AND adjustment_id = cp_adjustment_id
AND asset_id = p_asset_hdr_rec.asset_id
AND distribution_id = cp_dist_id;
SELECT *
FROM igi_iac_det_balances
WHERE adjustment_id = cp_adj_id
AND distribution_id = cp_dist_id;
select cumulative_reval_factor, current_reval_factor
from igi_iac_asset_balances
where asset_id = cp_asset_id
and book_type_code = cp_book_type_code
and period_counter = cp_period_counter
;
IGI_IAC_TRANS_HEADERS_PKG.Insert_Row(
x_rowid => l_rowid,
x_adjustment_id => l_adj_id, -- out NOCOPY parameter
x_transaction_header_id => p_trans_rec.transaction_header_id, -- bug 3391000 null,
x_adjustment_id_out => NULL,
x_transaction_type_code => p_trans_rec.transaction_type_code,
x_transaction_date_entered => p_trans_rec.transaction_date_entered,
x_mass_refrence_id => p_trans_rec.mass_reference_id,
x_transaction_sub_type => NULL,
x_book_type_code => p_asset_hdr_rec.book_type_code,
x_asset_id => p_asset_hdr_rec.Asset_id,
x_category_id => p_asset_cat_rec.category_id,
x_adj_deprn_start_date => NULL,
x_revaluation_type_flag => NULL,
x_adjustment_status => 'COMPLETE',
x_period_counter => l_current_period_counter,
x_event_id => p_event_id
);
igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Data inserted into transaction headers');
insert_data_adj(l_adj_id,
p_asset_hdr_rec.book_type_code,
l_expense_ccid,
nvl(p_asset_hdr_rec.set_of_books_id,l_sob_id),
'DR',
l_deprn_expense,
'EXPENSE',
l_all_dist.units_assigned,
p_asset_hdr_rec.asset_id,
l_all_dist.distribution_id,
l_current_period_counter,
null,
null,
p_event_id => p_event_id
);
insert_data_adj(l_adj_id,
p_asset_hdr_rec.book_type_code,
l_op_expense_ccid,
nvl(p_asset_hdr_rec.set_of_books_id,l_sob_id),
'CR',
l_dist_op_exp,
'OP EXPENSE',
l_all_dist.units_assigned,
p_asset_hdr_rec.asset_id,
l_all_dist.distribution_id,
l_current_period_counter,
null,
null,
p_event_id => p_event_id
);
insert_data_adj(l_adj_id,
p_asset_hdr_rec.book_type_code,
l_expense_ccid,
nvl(p_asset_hdr_rec.set_of_books_id,l_sob_id),
'CR',
l_deprn_expense,
'EXPENSE',
l_all_dist.units_assigned,
p_asset_hdr_rec.asset_id,
l_all_dist.distribution_id,
l_current_period_counter,
null,
null,
p_event_id => p_event_id
);
insert_data_adj(l_adj_id,
p_asset_hdr_rec.book_type_code,
l_op_expense_ccid,
nvl(p_asset_hdr_rec.set_of_books_id,l_sob_id),
'DR',
l_dist_op_exp,
'OP EXPENSE',
l_all_dist.units_assigned,
p_asset_hdr_rec.asset_id,
l_all_dist.distribution_id,
l_current_period_counter,
null,
null,
p_event_id => p_event_id
);
insert_data_adj(l_adj_id,
p_asset_hdr_rec.book_type_code,
l_reval_ccid,
nvl(p_asset_hdr_rec.set_of_books_id,l_sob_id),
'CR',
l_reval_reserve,
'REVAL RESERVE',
l_all_dist.units_assigned,
p_asset_hdr_rec.asset_id,
l_all_dist.distribution_id,
l_current_period_counter,
null,
null,
p_event_id => p_event_id
);
insert_data_adj(l_adj_id,
p_asset_hdr_rec.book_type_code,
l_gen_fund_ccid,
nvl(p_asset_hdr_rec.set_of_books_id,l_sob_id),
'CR',
l_general_fund,
'GENERAL FUND',
l_all_dist.units_assigned,
p_asset_hdr_rec.asset_id,
l_all_dist.distribution_id,
l_current_period_counter,
'REVAL RESERVE',
l_reval_ccid, --null
p_event_id => p_event_id
);
insert_data_adj(l_adj_id,
p_asset_hdr_rec.book_type_code,
l_backlog_ccid,
nvl(p_asset_hdr_rec.set_of_books_id,l_sob_id),
'CR',
l_reval_reserve_backlog, -- l_backlog_deprn_reserve,
'BL RESERVE',
l_all_dist.units_assigned,
p_asset_hdr_rec.asset_id,
l_all_dist.distribution_id,
l_current_period_counter,
'REVAL RESERVE',
l_reval_ccid,
p_event_id => p_event_id
);
insert_data_adj(l_adj_id,
p_asset_hdr_rec.book_type_code,
l_backlog_ccid,
nvl(p_asset_hdr_rec.set_of_books_id,l_sob_id),
'CR',
l_operating_acct_backlog, --l_backlog_deprn_reserve,
'BL RESERVE',
l_all_dist.units_assigned,
p_asset_hdr_rec.asset_id,
l_all_dist.distribution_id,
l_current_period_counter,
'OP EXPENSE',
l_op_expense_ccid,
p_event_id => p_event_id
);
insert_data_adj(l_adj_id,
p_asset_hdr_rec.book_type_code,
l_deprn_ccid,
nvl(p_asset_hdr_rec.set_of_books_id,l_sob_id),
'CR',
l_deprn_reserve,
'RESERVE',
l_all_dist.units_assigned,
p_asset_hdr_rec.asset_id,
l_all_dist.distribution_id,
l_current_period_counter,
null,
null,
p_event_id => p_event_id
);
insert_data_adj(l_adj_id,
p_asset_hdr_rec.book_type_code,
l_cost_ccid,
nvl(p_asset_hdr_rec.set_of_books_id,l_sob_id),
'DR',
l_adjusted_cost,
'COST',
l_all_dist.units_assigned,
p_asset_hdr_rec.asset_id,
l_all_dist.distribution_id,
l_current_period_counter,
l_adj_offset_type,
l_report_ccid,
p_event_id => p_event_id
);
insert_data_adj(l_adj_id,
p_asset_hdr_rec.book_type_code,
l_reval_ccid,
nvl(p_asset_hdr_rec.set_of_books_id,l_sob_id),
'DR',
l_reval_reserve,
'REVAL RESERVE',
l_all_dist.units_assigned,
p_asset_hdr_rec.asset_id,
l_all_dist.distribution_id,
l_current_period_counter,
null,
null,
p_event_id => p_event_id
);
insert_data_adj(l_adj_id,
p_asset_hdr_rec.book_type_code,
l_gen_fund_ccid,
nvl(p_asset_hdr_rec.set_of_books_id,l_sob_id),
'DR',
l_general_fund,
'GENERAL FUND',
l_all_dist.units_assigned,
p_asset_hdr_rec.asset_id,
l_all_dist.distribution_id,
l_current_period_counter,
'REVAL RESERVE',
l_reval_ccid, -- null
p_event_id => p_event_id
);
insert_data_adj(l_adj_id,
p_asset_hdr_rec.book_type_code,
l_backlog_ccid,
nvl(p_asset_hdr_rec.set_of_books_id,l_sob_id),
'DR',
l_reval_reserve_backlog, --l_backlog_deprn_reserve,
'BL RESERVE',
l_all_dist.units_assigned,
p_asset_hdr_rec.asset_id,
l_all_dist.distribution_id,
l_current_period_counter,
'REVAL RESERVE',
l_reval_ccid,
p_event_id => p_event_id
);
insert_data_adj(l_adj_id,
p_asset_hdr_rec.book_type_code,
l_backlog_ccid,
nvl(p_asset_hdr_rec.set_of_books_id,l_sob_id),
'DR',
l_operating_acct_backlog, --l_backlog_deprn_reserve,
'BL RESERVE',
l_all_dist.units_assigned,
p_asset_hdr_rec.asset_id,
l_all_dist.distribution_id,
l_current_period_counter,
'OP EXPENSE',
l_op_expense_ccid,
p_event_id => p_event_id
);
insert_data_adj(l_adj_id,
p_asset_hdr_rec.book_type_code,
l_deprn_ccid,
nvl(p_asset_hdr_rec.set_of_books_id,l_sob_id),
'DR',
l_deprn_reserve,
'RESERVE',
l_all_dist.units_assigned,
p_asset_hdr_rec.asset_id,
l_all_dist.distribution_id,
l_current_period_counter,
null,
null,
p_event_id => p_event_id
);
insert_data_adj(l_adj_id,
p_asset_hdr_rec.book_type_code,
l_cost_ccid,
nvl(p_asset_hdr_rec.set_of_books_id,l_sob_id),
'CR',
l_adjusted_cost,
'COST',
l_all_dist.units_assigned,
p_asset_hdr_rec.asset_id,
l_all_dist.distribution_id,
l_current_period_counter,
l_adj_offset_type,
l_report_ccid,
p_event_id => p_event_id
);
igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Data inserted into adjustments for various accounts');
insert_data_adj(l_adj_id,
p_asset_hdr_rec.book_type_code,
l_interco_ccid, -- l_interco_data.code_combination_id,
nvl(p_asset_hdr_rec.set_of_books_id,l_sob_id),
'DR', -- l_interco_data.debit_credit_flag,
l_interco_amount,
'INTERCO AR', --l_interco_data.adjustment_type,
l_all_dist.units_assigned,
p_asset_hdr_rec.asset_id,
l_all_dist.distribution_id,
l_current_period_counter,
null,
null,
p_event_id => p_event_id
);
insert_data_adj(l_adj_id,
p_asset_hdr_rec.book_type_code,
l_interco_ccid, -- l_interco_data.code_combination_id,
nvl(p_asset_hdr_rec.set_of_books_id,l_sob_id),
'CR', -- l_interco_data.debit_credit_flag,
l_interco_amount,
'INTERCO AP', --l_interco_data.adjustment_type,
l_all_dist.units_assigned,
p_asset_hdr_rec.asset_id,
l_all_dist.distribution_id,
l_current_period_counter,
null,
null,
p_event_id => p_event_id
);
insert_data_det(l_adj_id,
p_asset_hdr_rec.asset_id,
l_all_dist.distribution_id,
l_current_period_counter,
p_asset_hdr_rec.book_type_code,
0,
0,
0,
0,
0,
0, -- l_op_acct,
0,
0,
l_ytd_deprn,
0,
0,
0,
l_ab_amounts.current_reval_factor,
l_ab_amounts.cumulative_reval_factor,
'N',
0, -- l_op_acct_ytd,
0, --l_operating_acct_backlog,
l_ab_amounts.last_reval_date
);
igi_iac_fa_deprn_pkg.insert_row(
x_rowid => l_rowid,
x_book_type_code => p_asset_hdr_rec.book_type_code,
x_asset_id => p_asset_hdr_rec.asset_id,
x_distribution_id => l_all_dist.distribution_id,
x_period_counter => l_current_period_counter,
x_adjustment_id => l_adj_id,
x_deprn_period => 0,
x_deprn_ytd => l_fa_deprn_ytd ,
x_deprn_reserve => 0,
x_active_flag => 'N',
x_mode => 'R');
insert_data_det(l_adj_id,
p_asset_hdr_rec.asset_id,
l_all_dist.distribution_id,
l_current_period_counter,
p_asset_hdr_rec.book_type_code,
l_adjusted_cost,
l_net_book_value,
l_reval_reserve,
l_general_fund,
l_reval_reserve_backlog,
l_op_acct, --0,
l_deprn_reserve,
l_backlog_deprn_reserve,
l_ytd_deprn, --0,
l_iac_deprn_period_amount,
l_general_fund,
l_general_fund_per,
l_ab_amounts.current_reval_factor,
l_ab_amounts.cumulative_reval_factor,
null,
0, -- l_op_acct_ytd is no longer maintained
l_operating_acct_backlog, --0,
l_ab_amounts.last_reval_date
);
igi_iac_fa_deprn_pkg.insert_row(
x_rowid => l_rowid,
x_book_type_code => p_asset_hdr_rec.book_type_code,
x_asset_id => p_asset_hdr_rec.asset_id,
x_distribution_id => l_all_dist.distribution_id,
x_period_counter => l_current_period_counter,
x_adjustment_id => l_adj_id,
x_deprn_period => l_fa_deprn_period_amount,
x_deprn_ytd => l_fa_deprn_ytd, -- 0,
x_deprn_reserve => l_fa_deprn_reserve,
x_active_flag => NULL,
x_mode => 'R');
insert_data_det(l_adj_id,
p_asset_hdr_rec.asset_id,
l_all_dist.distribution_id,
l_current_period_counter,
p_asset_hdr_rec.book_type_code,
l_adjusted_cost,
l_net_book_value,
l_reval_reserve,
l_general_fund,
l_reval_reserve_backlog,
l_op_acct,
l_deprn_reserve,
l_backlog_deprn_reserve,
l_ytd_deprn,
l_iac_deprn_period_amount,
l_general_fund,
l_general_fund_per,
l_ab_amounts.current_reval_factor,
l_ab_amounts.cumulative_reval_factor,
null,
0, -- l_op_acct_ytd, iac no longer maintains this value
l_operating_acct_backlog,
l_ab_amounts.last_reval_date
);
igi_iac_fa_deprn_pkg.insert_row(
x_rowid => l_rowid,
x_book_type_code => p_asset_hdr_rec.book_type_code,
x_asset_id => p_asset_hdr_rec.asset_id,
x_distribution_id => l_all_dist.distribution_id,
x_period_counter => l_current_period_counter,
x_adjustment_id => l_adj_id,
x_deprn_period => l_fa_deprn_period_amount,
x_deprn_ytd => l_fa_deprn_ytd ,
x_deprn_reserve => l_fa_deprn_reserve,
x_active_flag => NULL,
x_mode => 'R');
-- End of loop for insert into det_balances table
igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Data inserted into detail balances');
IGI_IAC_ASSET_BALANCES_PKG.update_row (
x_asset_id =>p_asset_hdr_rec.asset_id,
x_book_type_code =>p_asset_hdr_rec.book_type_code,
x_period_counter =>l_current_period_counter,
x_net_book_value =>l_ab_amounts.net_book_value,
x_adjusted_cost =>l_ab_amounts.adjusted_cost,
x_operating_acct =>l_ab_amounts.operating_acct,
x_reval_reserve =>l_ab_amounts.reval_reserve,
x_deprn_amount =>l_iac_deprn_period_amount,
x_deprn_reserve =>l_ab_amounts.deprn_reserve,
x_backlog_deprn_reserve =>l_ab_amounts.backlog_deprn_reserve,
x_general_fund =>l_ab_amounts.general_fund,
x_last_reval_date =>l_ab_amounts.last_reval_date,
x_current_reval_factor =>l_ab_amounts.current_reval_factor,
x_cumulative_reval_factor =>l_ab_amounts.cumulative_reval_factor,
x_mode =>'R'
);
igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Data Updated in Asset balances for the catch up');
IGI_IAC_ASSET_BALANCES_PKG.update_row (
x_asset_id =>p_asset_hdr_rec.asset_id,
x_book_type_code =>p_asset_hdr_rec.book_type_code,
x_period_counter =>l_current_period_counter,
x_net_book_value =>l_ab_amounts.net_book_value,
x_adjusted_cost =>l_ab_amounts.adjusted_cost,
x_operating_acct =>l_ab_amounts.operating_acct,
x_reval_reserve =>l_ab_amounts.reval_reserve,
x_deprn_amount =>l_iac_deprn_period_amount,
x_deprn_reserve =>l_ab_amounts.deprn_reserve,
x_backlog_deprn_reserve =>l_ab_amounts.backlog_deprn_reserve,
x_general_fund =>l_ab_amounts.general_fund,
x_last_reval_date =>l_ab_amounts.last_reval_date,
x_current_reval_factor =>l_ab_amounts.current_reval_factor,
x_cumulative_reval_factor =>l_ab_amounts.cumulative_reval_factor,
x_mode =>'R'
);
IGI_IAC_ASSET_BALANCES_PKG.insert_row (
x_rowid => l_rowid,
x_asset_id =>p_asset_hdr_rec.asset_id,
x_book_type_code =>p_asset_hdr_rec.book_type_code,
x_period_counter =>l_current_period_counter,
x_net_book_value =>l_ab_amounts.net_book_value,
x_adjusted_cost =>l_ab_amounts.adjusted_cost,
x_operating_acct =>l_ab_amounts.operating_acct,
x_reval_reserve =>l_ab_amounts.reval_reserve,
x_deprn_amount =>l_iac_deprn_period_amount,
x_deprn_reserve =>l_ab_amounts.deprn_reserve,
x_backlog_deprn_reserve =>l_ab_amounts.backlog_deprn_reserve,
x_general_fund =>l_ab_amounts.general_fund,
x_last_reval_date =>l_ab_amounts.last_reval_date,
x_current_reval_factor =>l_ab_amounts.current_reval_factor,
x_cumulative_reval_factor =>l_ab_amounts.cumulative_reval_factor,
x_mode =>'R'
);
igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Data Inserted in Asset balances');
IGI_IAC_TRANS_HEADERS_PKG.Update_Row(
x_prev_adjustment_id => l_adj_id_out,
x_adjustment_id => l_adj_id
);
SELECT transaction_date_entered,
mass_reference_id,
transaction_type_code
FROM fa_transaction_headers
WHERE transaction_header_id=p_transaction_header_id;
/* Cursor to select asset data for asset header rec */
CURSOR c_asset_data IS
SELECT set_of_books_id
FROM fa_book_controls
WHERE book_type_code=p_book_type_code;
/* Call the Do_Transfer process to insert data for current open period */
IF NOT(Do_transfer(l_trans_rec,
l_asset_hdr_rec,
l_asset_cat_rec,
'IGI_IAC_TRANSFERS_PKG.Do_prior_transfer',
p_event_id))THEN
app_exception.raise_exception;
| rollback the data inserted by prior transfers in the latest depreciation run if |
| any in that depreciation run . |
========================================================================================+
*/
FUNCTION Do_Rollback_Deprn(
p_book_type_code VARCHAR2,
p_period_counter NUMBER,
p_calling_function VARCHAR2
) return BOOLEAN IS
/* Cursor to select data from transaction headers which need to be rolled back */
CURSOR c_trans_headers IS
SELECT *
FROM igi_iac_transaction_headers
WHERE book_type_code=p_book_type_code
AND period_counter=p_period_counter
AND transaction_type_code='TRANSFER';
SELECT sum(deprn_period)
FROM igi_iac_det_balances
WHERE book_type_code = p_book_type_code
AND asset_id = cp_asset_id
AND adjustment_id = cp_adjustment_id;
/* Cursor to select the previous data */
CURSOR c_prev_data(c_adjustment_id igi_iac_transaction_headers.adjustment_id%type) IS
SELECT *
FROM igi_iac_transaction_headers
WHERE adjustment_id_out=c_adjustment_id;
SELECT *
FROM igi_iac_asset_balances
WHERE asset_id = cp_asset_id
AND book_type_code = p_book_type_code
AND period_counter = p_period_counter;
/* Cursor to select impacted distributions for roll back */
CURSOR c_dist(c_adjustment_id igi_iac_adjustments.adjustment_id%type) IS
SELECT distribution_id
FROM igi_iac_det_balances
WHERE adjustment_id=c_adjustment_id;
SELECT book_type_code,asset_id,period_counter,distribution_id,adjustment_id
FROM igi_iac_fa_deprn
WHERE asset_id = cp_asset_id
AND book_type_code = p_book_type_code
AND adjustment_id = cp_adjustment_id;
/*DELETE from igi_iac_det_balances
WHERE book_type_code=p_book_type_code
AND period_counter=p_period_counter
AND adjustment_id=l_trans_headers.adjustment_id;*/
IGI_IAC_DET_BALANCES_PKG.delete_row(
x_adjustment_id =>l_trans_headers.adjustment_id,
x_asset_id =>l_trans_headers.asset_id,
x_distribution_id =>l_dist.distribution_id,
x_book_type_code =>l_trans_headers.book_type_code,
x_period_counter =>p_period_counter
);
IGI_IAC_FA_DEPRN_PKG.delete_row(
x_adjustment_id =>l_fa_dist.adjustment_id,
x_asset_id =>l_fa_dist.asset_id,
x_distribution_id =>l_fa_dist.distribution_id,
x_book_type_code =>l_fa_dist.book_type_code,
x_period_counter =>l_fa_dist.period_counter
);
/* DELETE from igi_iac_adjustments
WHERE book_type_code=p_book_type_code
AND period_counter=p_period_counter
AND adjustment_id=l_trans_headers.adjustment_id; */
IGI_IAC_ADJUSTMENTS_PKG.delete_row(
x_adjustment_id =>l_trans_headers.adjustment_id
);
IGI_IAC_ASSET_BALANCES_PKG.update_row (
x_asset_id =>l_trans_headers.asset_id,
x_book_type_code =>p_book_type_code,
x_period_counter =>p_period_counter,
x_net_book_value =>l_amounts.net_book_value,
x_adjusted_cost =>l_amounts.adjusted_cost,
x_operating_acct =>l_amounts.operating_acct,
x_reval_reserve =>l_amounts.reval_reserve,
x_deprn_amount =>l_deprn_expense,
x_deprn_reserve =>l_amounts.deprn_reserve,
x_backlog_deprn_reserve =>l_amounts.backlog_deprn_reserve,
x_general_fund =>l_amounts.general_fund,
x_last_reval_date =>l_amounts.last_reval_date,
x_current_reval_factor =>l_amounts.current_reval_factor,
x_cumulative_reval_factor =>l_amounts.cumulative_reval_factor,
x_mode =>'R'
);
IGI_IAC_TRANS_HEADERS_PKG.update_row (
x_prev_adjustment_id =>l_prev_data.adjustment_id,
x_adjustment_id =>null,
x_mode =>'R'
);
IGI_IAC_TRANS_HEADERS_PKG.delete_row (
x_adjustment_id =>l_trans_headers.adjustment_id
);