[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT category_id,allow_indexed_reval_flag,allow_prof_reval_flag
FROM igi_iac_category_books
WHERE book_type_code = l_asset_hdr_rec.book_type_code
AND category_id = c_category_id;
SELECT capitalize_flag,category_type
FROM fa_categories
WHERE category_id = c_category_id;
SELECT 'X'
FROM igi_iac_asset_balances
WHERE asset_id=l_asset_hdr_rec.asset_id
AND book_type_code = l_asset_hdr_rec.book_type_code;
| Procedure To Insert Data into IGI_IAC_TRANSACTION_HEADERS |
================================================================================+
*/
PROCEDURE Insert_data_trans_hdr(p_adjustment_id in out NOCOPY IGI_IAC_TRANSACTION_HEADERS.adjustment_id%type,
p_transaction_header_id in IGI_IAC_TRANSACTION_HEADERS.transaction_header_id%type,
p_adjustment_id_out in IGI_IAC_TRANSACTION_HEADERS.adjustment_id_out%type,
p_transaction_type_code in IGI_IAC_TRANSACTION_HEADERS.transaction_type_code%type,
p_transaction_date_entered in IGI_IAC_TRANSACTION_HEADERS.transaction_date_entered%type,
p_mass_reference_id in IGI_IAC_TRANSACTION_HEADERS.mass_reference_id%type,
p_book_type_code in IGI_IAC_TRANSACTION_HEADERS.book_type_code%type,
p_asset_id in IGI_IAC_TRANSACTION_HEADERS.asset_id%type,
p_revaluation_flag in IGI_IAC_TRANSACTION_HEADERS.revaluation_type_flag%type,
p_adjustment_status in IGI_IAC_TRANSACTION_HEADERS.adjustment_status%type,
p_category_id in IGI_IAC_TRANSACTION_HEADERS.category_id%type,
p_period_counter in IGI_IAC_TRANSACTION_HEADERS.period_counter%type,
p_event_id in number
) IS
l_rowid VARCHAR2(25);
l_path_name := g_path||'insert_data_trans_hdr';
/* Call the TBH for inserting data into IGI_IAC_TRANSACTION_HEADERS */
IGI_IAC_TRANS_HEADERS_PKG.insert_row(
x_rowid =>l_rowid,
x_adjustment_id =>p_adjustment_id ,
x_transaction_header_id =>p_transaction_header_id ,
x_adjustment_id_out =>p_adjustment_id_out,
x_transaction_type_code =>p_transaction_type_code ,
x_transaction_date_entered =>p_transaction_date_entered ,
x_mass_refrence_id =>p_mass_reference_id,
x_transaction_sub_type =>null,
x_book_type_code =>p_book_type_code,
x_asset_id =>p_asset_id,
x_category_id =>p_category_id,
x_adj_deprn_start_date =>null,
x_revaluation_type_flag =>p_revaluation_flag,
x_adjustment_status =>p_adjustment_status,
x_period_counter =>p_period_counter,
x_mode =>'R',
x_event_id =>p_event_id
);
END insert_data_trans_hdr;
| Procedure to insert data into IGI_IAC_DET_BALANCES |
================================================================================+
*/
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_reval_reserve_net in IGI_IAC_DET_BALANCES.reval_reserve_net%type,
p_op_acct in IGI_IAC_DET_BALANCES.operating_acct_cost%type,
p_op_acct_net in IGI_IAC_DET_BALANCES.operating_acct_net%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_name := g_path||'insert_data_det';
/* Call to TBH for insert into IGI_IAC_DET_BALANCES */
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,
x_reval_reserve_backlog =>p_reval_reserve_backlog,
x_reval_reserve_gen_fund =>p_reval_reserve_gen_fund,
-- Bug 2767992 Sekhar Modified for reval reserve net
x_reval_reserve_net =>p_reval_reserve_net,
x_operating_acct_cost =>p_op_acct,
x_operating_acct_backlog =>p_operating_acct_backlog,
x_operating_acct_net =>p_op_acct_net,
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 units_assigned
into l_units_assigned
from fa_distribution_history
where book_type_code = fp_det_balances.book_type_code
and asset_id = fp_det_balances.asset_id
and distribution_id = fp_det_balances.distribution_id
;
IGI_IAC_ADJUSTMENTS_PKG.insert_row (
x_rowid => l_rowid,
x_adjustment_id => fp_det_balances.adjustment_id,
x_book_type_code => fp_det_balances.book_type_code,
x_code_combination_id => l_ccid,
x_set_of_books_id => l_set_of_books_id,
x_dr_cr_flag => l_cr_dr_flag,
x_amount => p_amount,
x_adjustment_type => l_adjust_type,
x_transfer_to_gl_flag => 'Y',
x_units_assigned => l_units_assigned,
x_asset_id => fp_det_balances.asset_id,
x_distribution_id => fp_det_balances.distribution_id,
x_period_counter => fp_det_balances.period_counter,
x_adjustment_offset_type => l_adjust_offset_type,
x_report_ccid => l_report_ccid,
x_mode => 'R',
x_event_id => p_event_id
) ;
/* Cursor to select all distributions for an asset that are active and the distribution
impacted by the reclass */
CURSOR c_all_dist IS
SELECT distribution_id,
transaction_header_id_in,
units_assigned
FROM fa_distribution_history
WHERE asset_id=l_asset_hdr_rec.asset_id
AND book_type_code=l_asset_hdr_rec.book_type_code
AND transaction_header_id_out=l_trans_rec.transaction_header_id;
/* Cursor to select the distribution(s) undergoing the reclasss */
CURSOR c_old_dist(c_dist_id IN FA_DISTRIBUTION_HISTORY.distribution_id%TYPE) IS
SELECT distribution_id
FROM fa_distribution_history
WHERE asset_id=l_asset_hdr_rec.asset_id
AND book_type_code=l_asset_hdr_rec.book_type_code
AND transaction_header_id_out=l_trans_rec.transaction_header_id
AND distribution_id=c_dist_id;
/* Cursor to select the new distribution(s) undergoing the reclasss */
CURSOR c_new_dist(c_dist_id IN FA_DISTRIBUTION_HISTORY.distribution_id%TYPE) IS
SELECT distribution_id
FROM fa_distribution_history
WHERE asset_id=l_asset_hdr_rec.asset_id
AND book_type_code=l_asset_hdr_rec.book_type_code
AND transaction_header_id_in=l_trans_rec.transaction_header_id
AND distribution_id=c_dist_id;
/* Cursor to select the details of impacted distribution in this reclass*/
CURSOR c_impacted_dist(c_imp_dist_id FA_DISTRIBUTION_HISTORY.distribution_id%TYPE) IS
SELECT a.distribution_id,
a.units_assigned
FROM fa_distribution_history a, fa_distribution_history b
WHERE a.asset_id=l_asset_hdr_rec.asset_id
AND a.book_type_code=l_asset_hdr_rec.book_type_code
AND a.asset_id = b.asset_id
AND a.book_type_code = b.book_type_code
AND a.transaction_header_id_in=l_trans_rec.transaction_header_id
AND b.transaction_header_id_out = l_trans_rec.transaction_header_id
AND nvl(a.location_id,-1) = nvl(b.location_id,-1)
AND a.units_assigned = b.units_assigned
AND b.distribution_id=c_imp_dist_id;
SELECT a.distribution_id,
a.units_assigned
FROM fa_distribution_history a, fa_distribution_history b
WHERE a.asset_id=l_asset_hdr_rec.asset_id
AND a.book_type_code=l_asset_hdr_rec.book_type_code
AND a.asset_id = b.asset_id
AND a.book_type_code = b.book_type_code
AND a.transaction_header_id_in=l_trans_rec.transaction_header_id
AND b.transaction_header_id_out = l_trans_rec.transaction_header_id
AND nvl(a.location_id,-1) = nvl(b.location_id,-1)
AND a.units_assigned = b.units_assigned
AND b.distribution_id=c_imp_dist_id
AND a.distribution_id > c_imp_dist_id_new;
/* Cursor to select adjustment_id or the previous transaction */
CURSOR c_prev_data IS
SELECT a.rowid,a.adjustment_id
FROM igi_iac_transaction_headers a
WHERE a.adjustment_id_out IS NULL
AND a.asset_id = l_asset_hdr_rec.asset_id;
SELECT *
FROM igi_iac_asset_balances
WHERE asset_id=l_asset_hdr_rec.asset_id
AND book_type_code=l_asset_hdr_rec.book_type_code
AND period_counter=(SELECT max(period_counter)
FROM igi_iac_asset_balances
WHERE asset_id=l_asset_hdr_rec.asset_id
AND book_type_code=l_asset_hdr_rec.book_type_code);
SELECT sum(units_assigned)
FROM fa_distribution_history
WHERE asset_id=l_asset_hdr_rec.asset_id
AND book_type_code=l_asset_hdr_rec.book_type_code
AND transaction_header_id_out IS NULL;
SELECT sum(units_assigned)
FROM fa_distribution_history
WHERE asset_id=l_asset_hdr_rec.asset_id
AND book_type_code=l_asset_hdr_rec.book_type_code
AND transaction_header_id_in=l_trans_rec.transaction_header_id;
SELECT nvl(sum(deprn_amount),0) deprn_amount
FROM igi_iac_asset_balances
WHERE book_type_code=l_asset_hdr_rec.book_type_code
AND asset_id=l_asset_hdr_rec.asset_id
AND period_counter between c_start_counter and c_current_counter;
SELECT nvl(sum(operating_acct),0) operating_acct
FROM igi_iac_asset_balances
WHERE book_type_code=l_asset_hdr_rec.book_type_code
AND asset_id=l_asset_hdr_rec.asset_id
AND period_counter BETWEEN c_start_counter AND c_current_counter;
/* Cursor to select the start period number for a given fiscal year */
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=l_asset_hdr_rec.book_type_code);
/* Cursor to select the adjustment_id from the sequence for a new record */
CURSOR c_adj_id IS
SELECT igi_iac_transaction_headers_s.nextval
FROM dual;
/* To select the asset's deprn_expense from fa_books */
CURSOR c_deprn_expense(c_period_counter FA_DEPRN_SUMMARY.period_counter%TYPE) IS
SELECT deprn_amount
FROM fa_deprn_summary
WHERE book_type_code = l_asset_hdr_rec.book_type_code
AND period_counter = c_period_counter
AND asset_id=l_asset_hdr_rec.asset_id;
/* Cursor to select the reval reserve backlog,op acct backlog and gen fund per for the dist */
CURSOR c_backlog_data(c_current_period_Counter fa_deprn_periods.period_counter%TYPE) IS
SELECT sum(nvl(iadb.reval_reserve_backlog,0)) reval_reserve_backlog,
sum(nvl(iadb.operating_acct_backlog,0)) operating_acct_backlog,
sum(nvl(iadb.general_fund_per,0)) general_fund_per
FROM igi_iac_det_balances iadb,fa_distribution_history fdh
WHERE iadb.book_type_code = l_asset_hdr_rec.book_type_code
AND iadb.period_counter = c_current_period_counter
AND iadb.asset_id=l_asset_hdr_rec.asset_id
AND iadb.asset_id=fdh.asset_id
AND iadb.book_type_code =fdh.book_type_code
AND fdh.transaction_header_id_out=l_trans_rec.transaction_header_id
AND fdh.distribution_id=iadb.distribution_id;
SELECT asset_number
FROM fa_additions
WHERE asset_id=l_asset_hdr_rec.asset_id;
SELECT *
FROM igi_iac_det_balances
WHERE asset_id=l_asset_hdr_rec.asset_id
AND book_type_code = l_asset_hdr_rec.book_type_code
AND distribution_id = p_old_dist
AND adjustment_id = p_adjustment_id;
SELECT *
FROM igi_iac_det_balances
WHERE asset_id=l_asset_hdr_rec.asset_id
AND book_type_code = l_asset_hdr_rec.book_type_code
AND adjustment_id = C_prev_data_adjustment_id;
SELECT *
FROM IGI_IAC_FA_DEPRN
WHERE book_type_code = c_book_type_code
AND asset_id = c_asset_id
AND Distribution_id = c_distribution_id
AND adjustment_id = c_adjustment_id;
-- Insert into transaction headers
insert_data_trans_hdr(l_adj_id,
l_trans_rec.transaction_header_id,
NULL,
l_trans_rec.transaction_type_code,
l_trans_rec.transaction_date_entered,
l_trans_rec.mass_reference_id,
l_asset_hdr_rec.book_type_code,
l_asset_hdr_rec.Asset_id,
NULL,
'COMPLETE',
l_asset_cat_rec_old.category_id,
-- l_asset_cat_rec_new.category_id,
l_current_period_counter,
p_event_id
);
p_string => ' + After Insert into trans header' );
IGI_IAC_TRANS_HEADERS_PKG.update_row (
x_prev_adjustment_id =>l_adjustment_id_out,
x_adjustment_id =>l_adj_id,
x_mode =>'R'
);
UPDATE igi_iac_transaction_headers
SET category_id = l_asset_cat_rec_old.category_id
WHERE adjustment_id = l_adj_id;
insert_data_det(p_adjustment_id =>l_adj_id,
p_asset_id =>l_asset_hdr_rec.asset_id,
p_distribution_id =>l_closing_det_balances.distribution_id,
p_period_counter =>l_closing_det_balances.period_counter,
p_book_type_code =>l_asset_hdr_rec.book_type_code,
p_adjusted_cost =>0,
p_net_book_value =>0,
p_reval_reserve =>0,
p_reval_reserve_gen_fund =>0,
p_reval_reserve_backlog =>0,
p_reval_reserve_net =>0,
p_op_acct =>0,
p_op_acct_net =>0,
p_deprn_reserve =>0,
p_deprn_reserve_backlog =>0,
p_deprn_ytd =>l_closing_det_balances.DEPRN_YTD,
p_deprn_period =>0,
p_gen_fund_acc =>0,
p_gen_fund_per =>0,
p_current_reval_factor =>l_closing_det_balances.current_reval_factor,
p_cumulative_reval_factor =>l_closing_det_balances.cumulative_reval_factor,
p_reval_flag => 'N',
p_op_acct_ytd =>l_closing_det_balances.OPERATING_ACCT_YTD,
p_operating_acct_backlog =>0,
p_last_reval_date =>l_closing_det_balances.last_reval_date);
IGI_IAC_FA_DEPRN_PKG.insert_row(
x_rowid =>l_rowid,
x_adjustment_id =>l_adj_id,
x_book_type_code =>l_asset_hdr_rec.book_type_code,
x_asset_id =>l_asset_hdr_rec.asset_id,
x_distribution_id =>l_closing_det_balances.distribution_id,
x_period_counter =>l_closing_det_balances.period_counter,
x_deprn_period => 0,
x_deprn_ytd =>l_get_deprn_dist.deprn_ytd,
x_deprn_reserve => 0,
x_active_flag => 'N',
x_mode =>'R'
);
UPDATE igi_iac_transaction_headers
SET category_id = l_asset_cat_rec_new.category_id
WHERE adjustment_id = l_adj_id;
insert_data_det(p_adjustment_id =>l_adj_id,
p_asset_id =>l_asset_hdr_rec.asset_id,
p_distribution_id =>l_impacted_dist.distribution_id,
p_period_counter =>l_current_period_counter,
p_book_type_code =>l_asset_hdr_rec.book_type_code,
p_adjusted_cost =>0,
p_net_book_value =>0,
p_reval_reserve =>0,
p_reval_reserve_gen_fund =>0,
p_reval_reserve_backlog =>0,
p_reval_reserve_net =>0,
p_op_acct =>0,
p_op_acct_net =>0,
p_deprn_reserve =>0,
p_deprn_reserve_backlog =>0,
p_deprn_ytd =>0,
p_deprn_period =>0,
p_gen_fund_acc =>0,
p_gen_fund_per =>0,
p_current_reval_factor =>1,
p_cumulative_reval_factor =>1,
p_reval_flag => NULL,
p_op_acct_ytd =>0,
p_operating_acct_backlog =>0,
p_last_reval_date =>l_closing_det_balances.last_reval_date);
/* Call to TBH for insert into IGI_IAC_ADJUSTMENTS */
IGI_IAC_FA_DEPRN_PKG.insert_row(
x_rowid =>l_rowid,
x_adjustment_id =>l_adj_id,
x_book_type_code =>l_asset_hdr_rec.book_type_code,
x_asset_id =>l_asset_hdr_rec.asset_id,
x_distribution_id =>l_impacted_dist.distribution_id,
x_period_counter =>l_current_period_counter,
x_deprn_period => 0,
x_deprn_ytd => 0,
x_deprn_reserve => 0,
x_active_flag => NULL,
x_mode =>'R'
);
/* Update the asset balanaces to zero in case new category has no indexed revalutions */
IF NOT (ALLOW_INDEX_REVAL_FLAG) THEN
igi_iac_asset_balances_pkg.update_row(
X_asset_id => l_asset_hdr_rec.asset_id,
X_book_type_code => l_asset_hdr_rec.book_type_code,
X_period_counter => l_current_period_counter ,
X_net_book_value => 0,
X_adjusted_cost => 0,
X_operating_acct => 0,
X_reval_reserve => 0,
X_deprn_amount => 0,
X_deprn_reserve => 0,
X_backlog_deprn_reserve => 0,
X_general_fund => 0,
X_last_reval_date => Null,
X_current_reval_factor => 1,
X_cumulative_reval_factor => 1) ;
insert_data_det(p_adjustment_id =>l_adj_id,
p_asset_id =>l_asset_hdr_rec.asset_id,
p_distribution_id =>l_get_all_prev_dist.distribution_id,
p_period_counter =>l_current_period_counter,
p_book_type_code =>l_get_all_prev_dist.book_type_code,
p_adjusted_cost =>l_get_all_prev_dist.adjustment_cost,
p_net_book_value =>l_get_all_prev_dist.net_book_value,
p_reval_reserve =>l_get_all_prev_dist.reval_reserve_cost,
p_reval_reserve_gen_fund =>l_get_all_prev_dist.reval_reserve_gen_fund,
p_reval_reserve_backlog =>l_get_all_prev_dist.reval_reserve_backlog,
p_op_acct =>l_get_all_prev_dist.OPERATING_ACCT_COST,
p_deprn_reserve =>l_get_all_prev_dist.deprn_reserve,
p_deprn_reserve_backlog =>l_get_all_prev_dist.deprn_reserve_backlog,
p_deprn_ytd => l_get_all_prev_dist.deprn_ytd,
p_reval_reserve_net =>l_get_all_prev_dist.reval_reserve_net,
p_op_acct_net =>l_get_all_prev_dist.OPERATING_ACCT_net,
p_deprn_period =>l_get_all_prev_dist.deprn_period,
p_gen_fund_acc =>l_get_all_prev_dist.general_fund_acc,
p_gen_fund_per =>l_get_all_prev_dist.general_fund_per,
p_current_reval_factor =>l_get_all_prev_dist.current_reval_factor,
p_cumulative_reval_factor =>l_get_all_prev_dist.cumulative_reval_factor,
p_reval_flag => l_get_all_prev_dist.active_flag,
p_op_acct_ytd =>l_get_all_prev_dist.OPERATING_ACCT_YTD,
p_operating_acct_backlog =>l_get_all_prev_dist.OPERATING_ACCT_BACKLOG,
p_last_reval_date =>l_get_all_prev_dist.last_reval_date);
/* Call to TBH for insert into IGI_IAC_ADJUSTMENTS */
IGI_IAC_FA_DEPRN_PKG.insert_row(
x_rowid =>l_rowid,
x_adjustment_id =>l_adj_id,
x_book_type_code =>l_asset_hdr_rec.book_type_code,
x_asset_id =>l_asset_hdr_rec.asset_id,
x_distribution_id =>l_get_all_prev_dist.distribution_id,
x_period_counter =>l_closing_det_balances.period_counter,
x_deprn_period => l_get_deprn_dist.deprn_period,
x_deprn_ytd =>l_get_deprn_dist.deprn_ytd,
x_deprn_reserve =>l_get_deprn_dist.deprn_reserve,
x_active_flag => 'N',
x_mode =>'R'
);
/* Cursor to select all distributions for an asset that are active and the distribution
impacted by the reclass */
CURSOR c_all_dist IS
SELECT distribution_id,
transaction_header_id_in,
units_assigned
FROM fa_distribution_history
WHERE asset_id=l_asset_hdr_rec.asset_id
AND book_type_code=l_asset_hdr_rec.book_type_code
AND transaction_header_id_out=l_trans_rec.transaction_header_id;
/* Cursor to select the distribution(s) undergoing the reclasss */
CURSOR c_old_dist(c_dist_id in FA_DISTRIBUTION_HISTORY.distribution_id%type) IS
SELECT distribution_id
FROM fa_distribution_history
WHERE asset_id=l_asset_hdr_rec.asset_id
AND book_type_code=l_asset_hdr_rec.book_type_code
AND transaction_header_id_out=l_trans_rec.transaction_header_id
AND distribution_id=c_dist_id;
/* Cursor to select the new distribution(s) undergoing the reclasss */
CURSOR c_new_dist(c_dist_id in FA_DISTRIBUTION_HISTORY.distribution_id%type) IS
SELECT distribution_id
FROM fa_distribution_history
WHERE asset_id=l_asset_hdr_rec.asset_id
AND book_type_code=l_asset_hdr_rec.book_type_code
AND transaction_header_id_in=l_trans_rec.transaction_header_id
AND distribution_id=c_dist_id;
/* Cursor to select the details of impacted distribution in this reclass*/
CURSOR c_impacted_dist(c_imp_dist_id FA_DISTRIBUTION_HISTORY.distribution_id%type) IS
SELECT a.distribution_id,
a.units_assigned
FROM fa_distribution_history a, fa_distribution_history b
WHERE a.asset_id=l_asset_hdr_rec.asset_id
AND a.book_type_code=l_asset_hdr_rec.book_type_code
AND a.asset_id = b.asset_id
AND a.book_type_code = b.book_type_code
AND a.transaction_header_id_in=l_trans_rec.transaction_header_id
AND b.transaction_header_id_out = l_trans_rec.transaction_header_id
AND nvl(a.location_id,-1) = nvl(b.location_id,-1)
AND a.units_assigned = b.units_assigned
AND b.distribution_id=c_imp_dist_id;
SELECT a.distribution_id,
a.units_assigned
FROM fa_distribution_history a, fa_distribution_history b
WHERE a.asset_id=l_asset_hdr_rec.asset_id
AND a.book_type_code=l_asset_hdr_rec.book_type_code
AND a.asset_id = b.asset_id
AND a.book_type_code = b.book_type_code
AND a.transaction_header_id_in=l_trans_rec.transaction_header_id
AND b.transaction_header_id_out = l_trans_rec.transaction_header_id
AND nvl(a.location_id,-1) = nvl(b.location_id,-1)
AND a.units_assigned = b.units_assigned
AND b.distribution_id=c_imp_dist_id
AND a.distribution_id > c_imp_dist_id_new;
/* Cursor to select adjustment_id or the previous transaction */
CURSOR c_prev_data IS
SELECT a.rowid,a.adjustment_id
FROM igi_iac_transaction_headers a
WHERE a.adjustment_id_out is null
and a.asset_id = l_asset_hdr_rec.asset_id;
SELECT *
FROM igi_iac_asset_balances
WHERE asset_id=l_asset_hdr_rec.asset_id
AND book_type_code=l_asset_hdr_rec.book_type_code
AND period_counter=(select max(period_counter)
from igi_iac_asset_balances
WHERE asset_id=l_asset_hdr_rec.asset_id
AND book_type_code=l_asset_hdr_rec.book_type_code)
;
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 sum(units_assigned)
FROM fa_distribution_history
WHERE asset_id=l_asset_hdr_rec.asset_id
AND book_type_code=l_asset_hdr_rec.book_type_code
AND transaction_header_id_out is null;
SELECT sum(units_assigned)
FROM fa_distribution_history
WHERE asset_id=l_asset_hdr_rec.asset_id
AND book_type_code=l_asset_hdr_rec.book_type_code
AND transaction_header_id_in=l_trans_rec.transaction_header_id;
SELECT nvl(sum(deprn_amount),0) deprn_amount
FROM igi_iac_asset_balances
WHERE book_type_code=l_asset_hdr_rec.book_type_code
AND asset_id=l_asset_hdr_rec.asset_id
AND period_counter between c_start_counter and c_current_counter;
SELECT nvl(sum(operating_acct),0) operating_acct
FROM igi_iac_asset_balances
WHERE book_type_code=l_asset_hdr_rec.book_type_code
AND asset_id=l_asset_hdr_rec.asset_id
AND period_counter between c_start_counter and c_current_counter;
/* Cursor to select the start period number for a given fiscal year */
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=l_asset_hdr_rec.book_type_code);
/* Cursor to select the adjustment_id from the sequence for a new record */
CURSOR c_adj_id IS
SELECT igi_iac_transaction_headers_s.nextval
FROM dual;
/* To select the asset's deprn_expense from fa_books */
CURSOR c_deprn_expense(c_period_counter FA_DEPRN_SUMMARY.period_counter%type) IS
SELECT deprn_amount
FROM fa_deprn_summary
WHERE book_type_code = l_asset_hdr_rec.book_type_code
AND period_counter = c_period_counter
AND asset_id=l_asset_hdr_rec.asset_id;
/* Cursor to select the reval reserve backlog,op acct backlog and gen fund per for the dist */
CURSOR c_backlog_data(c_current_period_Counter fa_deprn_periods.period_counter%type) IS
SELECT sum(nvl(iadb.reval_reserve_backlog,0)) reval_reserve_backlog,
sum(nvl(iadb.operating_acct_backlog,0)) operating_acct_backlog,
sum(nvl(iadb.general_fund_per,0)) general_fund_per
FROM igi_iac_det_balances iadb,fa_distribution_history fdh
WHERE iadb.book_type_code = l_asset_hdr_rec.book_type_code
AND iadb.period_counter = c_current_period_counter
AND iadb.asset_id=l_asset_hdr_rec.asset_id
AND iadb.asset_id=fdh.asset_id
AND iadb.book_type_code =fdh.book_type_code
AND fdh.transaction_header_id_out=l_trans_rec.transaction_header_id
AND fdh.distribution_id=iadb.distribution_id;
SELECT asset_number
FROM fa_additions
WHERE asset_id=l_asset_hdr_rec.asset_id;
SELECT *
FROM igi_iac_det_balances
WHERE asset_id=l_asset_hdr_rec.asset_id
AND book_type_code = l_asset_hdr_rec.book_type_code
AND distribution_id = p_old_dist
AND adjustment_id = p_adjustment_id;
SELECT *
FROM igi_iac_det_balances
WHERE asset_id=l_asset_hdr_rec.asset_id
AND book_type_code = l_asset_hdr_rec.book_type_code
AND adjustment_id = C_prev_data_adjustment_id;
SELECT *
FROM IGI_IAC_FA_DEPRN
WHERE book_type_code = c_book_type_code
AND asset_id = c_asset_id
AND Distribution_id = c_distribution_id
AND adjustment_id = c_adjustment_id;
p_string => ' + before Insert into trans header' );
/*Insert into transaction headers */
insert_data_trans_hdr(l_adj_id,
l_trans_rec.transaction_header_id,
NULL,
l_trans_rec.transaction_type_code,
l_trans_rec.transaction_date_entered,
l_trans_rec.mass_reference_id,
l_asset_hdr_rec.book_type_code,
l_asset_hdr_rec.Asset_id,
null,
'COMPLETE',
l_asset_cat_rec_old.category_id,
-- l_asset_cat_rec_new.category_id,
l_current_period_counter,
p_event_id
);
p_string => ' + After Insert into trans header' );
/* To select the backlog data for the old distribution to be prorated into new */
-- Bug 2588308 sekhar --
-- Need to close the previous transaction to pick the latest catgeory --
IGI_IAC_TRANS_HEADERS_PKG.update_row (
x_prev_adjustment_id =>l_adjustment_id_out,
x_adjustment_id =>l_adj_id,
x_mode =>'R'
);
Update igi_iac_transaction_headers
set category_id = l_asset_cat_rec_old.category_id
where adjustment_id = l_adj_id;
insert_data_det(p_adjustment_id =>l_adj_id,
p_asset_id =>l_asset_hdr_rec.asset_id,
p_distribution_id =>l_closing_det_balances.distribution_id,
p_period_counter =>l_closing_det_balances.period_counter,
p_book_type_code =>l_asset_hdr_rec.book_type_code,
p_adjusted_cost =>0,
p_net_book_value =>0,
p_reval_reserve =>0,
p_reval_reserve_gen_fund =>0,
p_reval_reserve_backlog =>0,
p_reval_reserve_net =>0,
p_op_acct =>0,
p_op_acct_net =>0,
p_deprn_reserve =>0,
p_deprn_reserve_backlog =>0,
p_deprn_ytd =>l_closing_det_balances.DEPRN_YTD,
p_deprn_period =>0,
p_gen_fund_acc =>0,
p_gen_fund_per =>0,
p_current_reval_factor =>l_closing_det_balances.current_reval_factor,
p_cumulative_reval_factor =>l_closing_det_balances.cumulative_reval_factor,
p_reval_flag => 'N',
p_op_acct_ytd =>0,
p_operating_acct_backlog =>0,
p_last_reval_date =>l_closing_det_balances.last_reval_date);
/* Call to TBH for insert into IGI_IAC_ADJUSTMENTS */
IGI_IAC_FA_DEPRN_PKG.insert_row(
x_rowid =>l_rowid,
x_adjustment_id =>l_adj_id,
x_book_type_code =>l_asset_hdr_rec.book_type_code,
x_asset_id =>l_asset_hdr_rec.asset_id,
x_distribution_id =>l_closing_det_balances.distribution_id,
x_period_counter =>l_closing_det_balances.period_counter,
x_deprn_period => 0,
x_deprn_ytd =>l_get_deprn_dist.deprn_ytd,
x_deprn_reserve => 0,
x_active_flag => 'N',
x_mode =>'R'
);
Update igi_iac_transaction_headers
set category_id = l_asset_cat_rec_new.category_id
where adjustment_id = l_adj_id;
/* insert_data_det(p_adjustment_id =>l_adj_id,
p_asset_id =>l_asset_hdr_rec.asset_id,
p_distribution_id =>l_impacted_dist.distribution_id,
p_period_counter =>l_closing_det_balances.period_counter,
p_book_type_code =>l_asset_hdr_rec.book_type_code,
p_adjusted_cost =>0,
p_net_book_value =>0,
p_reval_reserve =>0,
p_reval_reserve_gen_fund =>0,
p_reval_reserve_backlog =>0,
p_op_acct =>0,
p_deprn_reserve =>0,
p_deprn_reserve_backlog =>0,
p_deprn_ytd =>0,
p_deprn_period =>0,
p_gen_fund_acc =>0,
p_gen_fund_per =>0,
p_current_reval_factor =>0,
p_cumulative_reval_factor =>0,
p_reval_flag => Null,
p_op_acct_ytd =>0,
p_operating_acct_backlog =>0,
p_last_reval_date =>l_closing_det_balances.last_reval_date);
insert_data_det(p_adjustment_id =>l_adj_id,
p_asset_id =>l_asset_hdr_rec.asset_id,
p_distribution_id =>l_impacted_dist.distribution_id,
p_period_counter =>l_current_period_counter,
p_book_type_code =>l_asset_hdr_rec.book_type_code,
p_adjusted_cost =>l_closing_det_balances.adjustment_cost,
p_net_book_value =>l_closing_det_balances.net_book_value,
p_reval_reserve =>l_closing_det_balances.reval_reserve_cost,
p_reval_reserve_gen_fund =>l_closing_det_balances.reval_reserve_gen_fund,
p_reval_reserve_backlog =>l_closing_det_balances.reval_reserve_backlog,
-- Bug 2767992 Sekhar Modified for reval reserve net
p_reval_reserve_net =>l_closing_det_balances.reval_reserve_net,
p_op_acct =>l_closing_det_balances.OPERATING_ACCT_COST,
p_op_acct_net =>l_closing_det_balances.OPERATING_ACCT_net,
p_deprn_reserve =>l_closing_det_balances.deprn_reserve,
p_deprn_reserve_backlog =>l_closing_det_balances.deprn_reserve_backlog,
p_deprn_ytd =>l_closing_det_balances.deprn_YTD,
p_deprn_period =>l_closing_det_balances.deprn_period,
p_gen_fund_acc =>l_closing_det_balances.general_fund_acc,
p_gen_fund_per =>l_closing_det_balances.general_fund_per,
p_current_reval_factor =>l_closing_det_balances.current_reval_factor,
p_cumulative_reval_factor =>l_closing_det_balances.cumulative_reval_factor,
p_reval_flag => Null,
p_op_acct_ytd =>l_closing_det_balances.OPERATING_ACCT_YTD,
p_operating_acct_backlog =>l_closing_det_balances.OPERATING_ACCT_BACKLOG,
p_last_reval_date =>l_closing_det_balances.last_reval_date);
/* Call to TBH for insert into IGI_IAC_ADJUSTMENTS */
IGI_IAC_FA_DEPRN_PKG.insert_row(
x_rowid =>l_rowid,
x_adjustment_id =>l_adj_id,
x_book_type_code =>l_asset_hdr_rec.book_type_code,
x_asset_id =>l_asset_hdr_rec.asset_id,
x_distribution_id =>l_closing_det_balances.distribution_id,
x_period_counter =>l_closing_det_balances.period_counter,
x_deprn_period => l_get_deprn_dist.deprn_period,
x_deprn_ytd =>0,
x_deprn_reserve =>l_get_deprn_dist.deprn_reserve,
x_active_flag => Null,
x_mode =>'R'
);
p_string => ' + After insert into det balances ' || l_old_dist.distribution_id);
insert_data_det(p_adjustment_id =>l_adj_id,
p_asset_id =>l_asset_hdr_rec.asset_id,
p_distribution_id =>l_impacted_dist.distribution_id,
p_period_counter =>l_current_period_counter,
p_book_type_code =>l_asset_hdr_rec.book_type_code,
p_adjusted_cost =>0,
p_net_book_value =>0,
p_reval_reserve =>0,
p_reval_reserve_net =>0,
p_reval_reserve_gen_fund =>0,
p_reval_reserve_backlog =>0,
p_op_acct =>0,
p_op_acct_net =>0,
p_deprn_reserve =>0,
p_deprn_reserve_backlog =>0,
p_deprn_ytd =>0,
p_deprn_period =>0,
p_gen_fund_acc =>0,
p_gen_fund_per =>0,
p_current_reval_factor =>0,
p_cumulative_reval_factor =>0,
p_reval_flag => Null,
p_op_acct_ytd =>0,
p_operating_acct_backlog =>0,
p_last_reval_date =>l_closing_det_balances.last_reval_date);
/* End of loop for insert into det_balances table */
End Loop;
/* Update the asset balanaces to zero in case new category is has no prof and indexed revalutions */
IF NOT (ALLOW_INDEX_REVAL_FLAG OR ALLOW_PROF_REVAL_FLAG) THEN
igi_iac_asset_balances_pkg.update_row(
X_asset_id => l_asset_hdr_rec.asset_id,
X_book_type_code => l_asset_hdr_rec.book_type_code,
X_period_counter => l_current_period_counter ,
X_net_book_value => 0,
X_adjusted_cost => 0,
X_operating_acct => 0,
X_reval_reserve => 0,
X_deprn_amount => 0,
X_deprn_reserve => 0,
X_backlog_deprn_reserve => 0,
X_general_fund => 0,
X_last_reval_date => Null,
X_current_reval_factor => 0,
X_cumulative_reval_factor => 0) ;
insert_data_det(p_adjustment_id =>l_adj_id,
p_asset_id =>l_asset_hdr_rec.asset_id,
p_distribution_id =>l_get_all_prev_dist.distribution_id,
p_period_counter =>l_current_period_counter,
p_book_type_code =>l_get_all_prev_dist.book_type_code,
p_adjusted_cost =>l_get_all_prev_dist.adjustment_cost,
p_net_book_value =>l_get_all_prev_dist.net_book_value,
p_reval_reserve =>l_get_all_prev_dist.reval_reserve_cost,
p_reval_reserve_gen_fund =>l_get_all_prev_dist.reval_reserve_gen_fund,
p_reval_reserve_backlog =>l_get_all_prev_dist.reval_reserve_backlog,
p_op_acct =>l_get_all_prev_dist.OPERATING_ACCT_COST,
p_deprn_reserve =>l_get_all_prev_dist.deprn_reserve,
p_deprn_reserve_backlog =>l_get_all_prev_dist.deprn_reserve_backlog,
p_deprn_ytd => l_get_all_prev_dist.deprn_ytd,
-- Bug 2767992 Sekhar Modified for reval reserve net
p_reval_reserve_net =>l_get_all_prev_dist.reval_reserve_net,
p_op_acct_net =>l_get_all_prev_dist.OPERATING_ACCT_net,
-- p_deprn_ytd => 0,
p_deprn_period =>l_get_all_prev_dist.deprn_period,
p_gen_fund_acc =>l_get_all_prev_dist.general_fund_acc,
p_gen_fund_per =>l_get_all_prev_dist.general_fund_per,
p_current_reval_factor =>l_get_all_prev_dist.current_reval_factor,
p_cumulative_reval_factor =>l_get_all_prev_dist.cumulative_reval_factor,
p_reval_flag => l_get_all_prev_dist.active_flag,
p_op_acct_ytd =>l_get_all_prev_dist.OPERATING_ACCT_YTD,
p_operating_acct_backlog =>l_get_all_prev_dist.OPERATING_ACCT_BACKLOG,
p_last_reval_date =>l_get_all_prev_dist.last_reval_date);
/* Call to TBH for insert into IGI_IAC_ADJUSTMENTS */
IGI_IAC_FA_DEPRN_PKG.insert_row(
x_rowid =>l_rowid,
x_adjustment_id =>l_adj_id,
x_book_type_code =>l_asset_hdr_rec.book_type_code,
x_asset_id =>l_asset_hdr_rec.asset_id,
x_distribution_id =>l_get_all_prev_dist.distribution_id,
x_period_counter =>l_closing_det_balances.period_counter,
x_deprn_period => l_get_deprn_dist.deprn_period,
x_deprn_ytd =>l_get_deprn_dist.deprn_ytd,
x_deprn_reserve =>l_get_deprn_dist.deprn_reserve,
x_active_flag => 'N',
x_mode =>'R'
);
IGI_IAC_TRANS_HEADERS_PKG.update_row (
x_prev_adjustment_id =>l_adjustment_id_out,
x_adjustment_id =>l_adj_id,
x_mode =>'R'
);
SELECT allow_indexed_reval_flag
FROM igi_iac_category_books
WHERE book_type_code = p_book_type_code
AND category_id = p_category_id;
SELECT period_num_for_catchup
FROM igi_iac_book_controls
WHERE book_type_code = p_book_type_code;
SELECT revaluation_type,revaluation_period,revaluation_factor,new_cost,current_cost
FROM igi_iac_revaluations rev,
igi_iac_reval_asset_rules rul
WHERE rev.revaluation_id = rul.revaluation_id
AND rev.book_type_code = l_asset_hdr_rec.BOOK_TYPE_CODE
AND rev.book_type_code = rul.book_type_code
AND rul.revaluation_type in ('O','P')
-- bug 2844230 Sekhar
-- Not required unable to fecth previous revaluations
-- AND rul.category_id = l_asset_cat_rec_new.category_id
AND rul.asset_id = l_asset_hdr_rec.ASSET_ID
order by revaluation_period;
SELECT salvage_value,cost
FROM fa_books
WHERE book_type_code = p_book_type_code
AND asset_id = p_asset_id
AND transactioN_header_id_out is NULL ;
/* Last revaluation - Insert records into revaluation tables*/
/* SELECT igi_iac_revaluations_s.nextval
INTO l_revaluation_id
FROM DUAL;
INSERT INTO igi_iac_revaluations
(revaluation_id,
book_type_code,
revaluation_date,
revaluation_period,
status,
reval_request_id,
create_request_id,
calling_program,
last_update_date,
created_by,
last_update_login,
last_updated_by,
creation_date)
VALUES (l_revaluation_id,
p_book_type_code,
sysdate,
l_reval_asset_params(l_idx1).period_counter,
'NEW',
NULL,
NULL,
'RECLASS',
sysdate,
l_user_id,
l_login_id,
l_user_id,
sysdate);
Debug(0,'Inserting into igi_iac_reval_asset_rules');
INSERT INTO igi_iac_reval_asset_rules
(revaluation_id,
book_type_code,
category_id,
asset_id,
revaluation_factor,
revaluation_type,
new_cost,
current_cost,
selected_for_reval_flag,
selected_for_calc_flag,
created_by,
creation_date,
last_update_login,
last_update_date,
last_updated_by,
allow_prof_update)
VALUES (l_revaluation_id,
l_reval_asset_params(l_idx1).book_type_code,
l_reval_asset_params(l_idx1).category_id,
l_reval_asset_params(l_idx1).asset_id,
l_reval_asset_rules(l_idx1).revaluation_factor,
l_reval_asset_rules(l_idx1).revaluation_type,
l_reval_asset_rules(l_idx1).new_cost,
l_reval_input_asset(l_idx2).adjusted_cost,
'Y',
'N',
l_user_id,
sysdate,
l_login_id,
sysdate,
l_user_id,
NULL);*/
/* Last revaluation - Insert records into revaluation tables*/
igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
p_full_path => l_path_name,
p_string => ' Last Revaluation - Inserting into igi_iac_revaluations');
igi_iac_revaluations_pkg.insert_row
(l_rowid,
l_revaluation_id,
p_book_type_code,
sysdate,
l_reval_asset_params(l_idx1).period_counter,
'NEW',
NULL,
NULL,
'ADDITION',
X_event_id => p_event_id);
p_string => ' Inserting into igi_iac_reval_asset_rules');
igi_iac_reval_asset_rules_pkg.insert_row
(l_rowid,
l_revaluation_id,
l_reval_asset_params(l_idx1).book_type_code,
l_reval_asset_params(l_idx1).category_id,
l_reval_asset_params(l_idx1).asset_id,
l_reval_asset_rules(l_idx1).revaluation_factor,
l_reval_asset_rules(l_idx1).revaluation_type,
l_reval_asset_rules(l_idx1).new_cost,
l_reval_input_asset(l_idx2).adjusted_cost,
'Y',
'N',
NULL);
UPDATE igi_iac_revaluation_rates
SET period_counter = l_last_reval_period
WHERE revaluation_id = l_revaluation_id
AND asset_id = p_asset_id
AND book_type_code = p_book_type_code;
p_string => ' Records in reval rates updated for correct period');
p_string => '*** No record found in reval rates table to update');
UPDATE IGI_IAC_ADJUSTMENTS
SET EVENT_ID = P_EVENT_ID
WHERE BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
AND ASSET_ID = P_ASSET_ID
AND PERIOD_COUNTER = l_open_period.period_counter
AND ADJUSTMENT_ID IN (SELECT ADJUSTMENT_ID
FROM IGI_IAC_TRANSACTION_HEADERS
WHERE BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
AND ASSET_ID = P_ASSET_ID
AND PERIOD_COUNTER = l_open_period.period_counter
AND TRANSACTION_TYPE_CODE = 'RECLASS'
AND EVENT_ID IS NULL);
UPDATE IGI_IAC_TRANSACTION_HEADERS
SET EVENT_ID = P_EVENT_ID
WHERE BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
AND ASSET_ID = P_ASSET_ID
AND PERIOD_COUNTER = l_open_period.period_counter
AND TRANSACTION_TYPE_CODE = 'RECLASS'
AND EVENT_ID IS NULL;
p_string => ' Updated all reclass trasactions with event_id');
/* bug 2502128 need to update the reval rates ..only one record should have staus = 'Y' for an asset */
Cursor C_Reval_Rates is
SELECT max(adjustment_id)
FROM igi_iac_transaction_headers ith
WHERE ith.book_type_code =l_asset_hdr_rec.book_type_code
AND ith.asset_id = l_asset_hdr_rec.asset_id
AND (ith.transaction_type_code = 'RECLASS' AND ith.Transaction_sub_type ='REVALUATION');
/* bug 2502128 need to update the reval rates ..only one record should have staus = 'Y' for an asset */
OPEN C_Reval_Rates;
IF NOT IGI_IAC_REVAL_CRUD.update_reval_rates (fp_adjustment_id => l_get_latest_adjustment_id) THEN
igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
p_full_path => l_path_name,
p_string => 'FAILED UPDATE REVAL RATES');
SELECT price_index_id
FROM igi_iac_category_books
WHERE book_type_code = l_asset_hdr_rec.book_type_code
AND category_id = c_category_id;