[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
UPDATE igi_imp_iac_interface_ctrl ct
SET ct.transfer_status = p_status
WHERE ct.book_type_code = p_book_type_code
AND ct.category_id = p_category_id ;
SELECT *
FROM igi_imp_iac_interface ii
WHERE ii.book_type_code = p_book_type_code
AND ii.category_id = p_Category_id
AND ii.transferred_flag = 'N'
AND nvl(ii.valid_flag,'N') = 'N';
SELECT depreciate_flag
INTO l_depreciate
FROM fa_books
WHERE book_type_code = arec.book_type_code
AND asset_id = arec.asset_id
AND date_ineffective is NULL; -- Bug 5383551
SELECT decode(
fab.conversion_date,
NULL,
fab.life_in_months - floor(months_between(
fdp.CALENDAR_PERIOD_CLOSE_DATE,
fab.prorate_date)),
fab.life_in_months - floor(months_between(
fdp.CALENDAR_PERIOD_CLOSE_DATE,
fab.deprn_start_date)))
INTO l_remaining_periods
FROM fa_books fab, fa_deprn_periods fdp
WHERE fab.book_type_code = arec.book_type_code
AND fdp.book_type_code = arec.book_type_code
AND fab.asset_id = arec.asset_id
AND fab.date_ineffective is null
AND fdp.PERIOD_CLOSE_DATE is null;
SELECT (period_num - 1)
INTO l_elapsed_periods_curr_yr
FROM fa_deprn_periods
WHERE book_type_code = arec.book_type_code
AND PERIOD_CLOSE_DATE IS NULL;
UPDATE igi_imp_iac_interface
SET deprn_exp_mhca = l_deprn_exp_mhca,
general_fund_per_mhca = arec.deprn_exp_mhca - arec.deprn_exp_hist,
operating_account_mhca = (arec.operating_account_cost - arec.operating_account_backlog),
nbv_mhca = arec.cost_mhca - arec.accum_deprn_mhca - arec.backlog_mhca,
valid_flag ='Y'
WHERE book_type_code = arec.book_type_code
AND asset_id = arec.asset_id;
UPDATE igi_imp_iac_interface
SET deprn_exp_mhca = l_deprn_exp_mhca,
general_fund_per_mhca = arec.deprn_exp_mhca - arec.deprn_exp_hist,
operating_account_mhca = (arec.operating_account_cost - arec.operating_account_backlog),
nbv_mhca = arec.cost_mhca - arec.accum_deprn_mhca - arec.backlog_mhca,
valid_flag = 'Y'
WHERE book_type_code = arec.book_type_code
AND asset_id = arec.asset_id;
UPDATE igi_imp_iac_interface
SET deprn_exp_mhca = arec.deprn_exp_hist,
operating_account_mhca = (arec.operating_account_cost - arec.operating_account_backlog),
nbv_mhca = arec.nbv_hist,
valid_flag = 'Y'
WHERE book_type_code = arec.book_type_code
AND asset_id = arec.asset_id;
SELECT max(period_counter)
from fa_deprn_periods
where book_type_code=p_book_type_code;
SELECT period_counter
FROM igi_imp_iac_controls
where book_type_code=p_book_type_code;
SELECT count(*)
FROM fa_transaction_headers ft ,
fa_deprn_periods dp
WHERE ft.book_type_Code = P_book_type_code
AND dp.book_type_Code = P_book_type_code
AND dp.period_close_Date IS NULL
AND ft.date_effective >= dp.period_open_date ;
SELECT fiscal_year
FROM fa_deprn_periods
WHERE book_type_code = p_book_type_code AND
period_counter = p_period_counter-1;
SELECT
dh.units_assigned,
dp.calendar_period_open_date,
dp.period_counter,
dh.distribution_id,
dh.code_combination_id,
dh.date_ineffective
FROM
fa_distribution_history dh,
fa_deprn_periods dp
WHERE
dh.asset_id= p_asset_id AND
dh.book_type_code=p_book_type_code AND
(nvl(dh.date_ineffective,dp.period_open_date)>=dp.period_open_date) AND
dp.Book_type_code=p_book_type_code AND
dp.fiscal_year=p_fiscal_year AND
dp.period_num=(SELECT min(period_num)
FROM fa_deprn_periods
WHERE fiscal_year=p_fiscal_year and
book_type_code=p_book_type_code);
SELECT period_counter
FROM igi_imp_iac_controls
WHERE Book_Type_Code = p_run_book;
SELECT dp.period_counter
FROM fa_deprn_periods dp,
fa_distribution_history dh
WHERE (dh.date_ineffective between dp.period_open_date and dp.period_close_date) AND
dp.book_type_code=p_book_type_code AND
dp.fiscal_year=p_fiscal_year AND
dh.book_type_code=p_book_type_code AND
dh.distribution_id=p_distribution_id;
SELECT ytd_deprn
FROM fa_deprn_detail
WHERE
distribution_id=p_distribution_id AND
book_type_code =p_book_type_code AND
asset_id =p_asset_id AND
-- Bug 3575041 start (1) --
period_counter=(select max(period_counter)
from fa_deprn_detail
where distribution_id = p_distribution_id
and book_type_code = p_book_type_code
and asset_id = p_asset_id ) ;
SELECT ytd_deprn
FROM fa_deprn_summary
WHERE asset_id=p_asset_id AND
book_type_code=p_book_type_code AND
period_counter=p_max_period_counter-1;
SELECT current_units
FROM fa_additions
WHERE asset_id=p_asset_id;
SELECT count(*)
FROM fa_distribution_history
WHERE book_type_code=p_book_type_code AND
asset_id=p_asset_id AND
date_ineffective IS NULL;
SELECT depreciate_flag
FROM FA_BOOKS
WHERE book_type_code = p_book_type_code
AND asset_id = p_asset_id
AND transaction_header_id_out is NULL;
igi_iac_det_balances_pkg.insert_row (
x_rowid => l_out_rowid ,
x_adjustment_id => p_adj_id ,
x_asset_id => p_asset_id ,
x_distribution_id => l_all_dist.distribution_id ,
x_book_type_code => p_book_type_code ,
x_period_counter => l_period_counter ,
x_adjustment_cost => l_det_adjustment_cost ,
x_net_book_value => l_det_net_book_value ,
x_reval_reserve_cost => l_det_reval_reserve_cost ,
x_reval_reserve_backlog => l_det_reval_reserve_backlog ,
x_reval_reserve_gen_fund => l_det_reval_reserve_gen_fund ,
x_reval_reserve_net => l_det_reval_reserve_net ,
x_operating_acct_cost => l_det_operating_acct_cost ,
x_operating_acct_backlog => l_det_operating_acct_backlog ,
x_operating_acct_net => l_det_operating_acct_net ,
x_operating_acct_ytd => l_det_operating_acct_ytd ,
x_deprn_period => l_det_deprn_period ,
x_deprn_ytd => l_det_deprn_ytd ,
x_deprn_reserve => l_det_deprn_reserve ,
x_deprn_reserve_backlog => l_det_deprn_reserve_backlog ,
x_general_fund_per => l_det_general_fund_per ,
x_general_fund_acc => l_det_general_fund_acc ,
x_last_reval_date => l_det_last_reval_date ,
x_current_reval_factor => l_det_current_reval_factor ,
x_cumulative_reval_factor => l_det_cumulative_reval_factor ,
x_active_flag => l_flag ,
x_mode => 'R' );
igi_iac_fa_deprn_pkg.insert_row(
x_rowid => l_out_rowid ,
x_book_type_code => p_book_type_code ,
x_asset_id => p_asset_id ,
x_period_counter => p_prd_rec.period_counter ,
x_adjustment_id => p_adj_id ,
x_distribution_id => l_all_dist.distribution_id ,
x_deprn_period => l_det_hist_deprn_amount ,
x_deprn_ytd => l_det_hist_deprn_ytd ,
x_deprn_reserve => l_det_hist_deprn_reserve ,
x_active_flag => l_flag ,
x_mode => 'R' );
SELECT *
FROM igi_iac_det_balances db
WHERE db.book_type_Code = p_book_type_code
AND db.asset_id = p_asset_id
AND db.adjustment_id = p_adj_id
AND db.active_flag is NULL;
SELECT units_assigned
INTO l_units
FROM fa_distribution_history
WHERE distribution_id = drec.distribution_id ;
igi_iac_adjustments_pkg.insert_row
(x_rowid => l_out_rowid ,
x_adjustment_id => p_adj_id ,
x_book_type_code => p_book_type_code ,
x_code_combination_id => -1 ,
x_set_of_books_id => -1 ,
x_dr_cr_flag => ' ' ,
x_amount => 0 ,
x_adjustment_type => ' ' ,
x_transfer_to_gl_flag => 'I' ,
x_units_assigned => l_units ,
x_asset_id => p_asset_id ,
x_distribution_id => drec.distribution_id ,
x_period_counter => p_period_counter ,
x_adjustment_offset_type => Null,
x_report_ccid => Null,
x_mode => 'R',
x_event_id => p_event_id ) ;
igi_iac_adjustments_pkg.insert_row (
x_rowid => l_out_rowid ,
x_adjustment_id => p_adj_id ,
x_book_type_code => p_book_type_code ,
x_code_combination_id => l_ccid ,
x_set_of_books_id => l_set_of_books_id ,
x_dr_cr_flag => l_dr_cr_flag ,
x_amount => l_amount ,
x_adjustment_type => l_adj_type ,
x_transfer_to_gl_flag => 'I' ,
x_units_assigned => l_units ,
x_asset_id => p_asset_id ,
x_distribution_id => drec.distribution_id ,
x_period_counter => p_period_counter ,
x_adjustment_offset_type => l_adjustment_offset_type,
x_report_ccid => l_report_ccid,
x_mode => 'R' ,
x_event_id => p_event_id) ;
SELECT *
FROM igi_imp_iac_interface_ctrl ic
WHERE ic.book_type_code = p_book_type_code
AND ic.category_id = p_Category_id ;
SELECT *
FROM igi_imp_iac_interface ii
WHERE ii.book_type_code = p_book_type_code
AND ii.category_id = p_Category_id
AND ii.transferred_flag = 'N'
AND ii.valid_flag = 'Y'; --Fix for Bug 5137813
Select book_class
from fa_booK_controls
where book_type_code = p_booK_type_code;
SELECT 'Y'
FROM igi_iac_transaction_headers it
WHERE it.book_type_code = cp_book
AND it.category_id = p_Category_id
AND NOT ( nvl(it.transaction_sub_type,'AA') = 'IMPLEMENTATION')
AND rownum = 1 ;
select current_reval_factor,cummulative_reval_factor
from igi_imp_iac_interface_py_add
where book_type_code = cp_book
and asset_id =cp_asset_id;
Select bk.asset_id,
bk.date_placed_in_service,
bk.life_in_months,
nvl(bk.cost,0) cost,
nvl(bk.adjusted_cost,0) adjusted_cost,
nvl(bk.original_cost,0) original_cost,
nvl(bk.salvage_value,0) salvage_value,
nvl(bk.adjusted_recoverable_cost, 0) adjusted_recoverable_cost,
nvl(bk.recoverable_cost,0) recoverable_cost,
bk.deprn_start_date,
bk.cost_change_flag,
bk.rate_adjustment_factor,
bk.depreciate_flag,
bk.fully_rsvd_revals_counter,
bk.period_counter_fully_reserved,
bk.period_counter_fully_retired
From fa_books bk
Where bk.book_type_code = cp_book
and bk.asset_id = cp_asset_id
and bk.transaction_header_id_out is null;
Select period_counter,deprn_reserve
from fa_deprn_summary fds
where book_type_code =cp_book
and asset_id = cp_asset_id
and period_counter = ( select max(period_counter)
from fa_deprn_summary
where book_type_code =fds.book_type_code
and asset_id = fds.asset_id);
SELECT ic.period_counter , ic.corp_book
INTO l_period_counter , l_corporate_book
FROM igi_imp_iac_controls ic
WHERE ic.book_type_code = p_book_type_code ;
igi_iac_revaluations_pkg.insert_row (
X_rowid => l_out_rowid ,
X_revaluation_id => l_out_reval_id ,
X_book_type_code => l_corporate_book ,
X_revaluation_date => l_prd_rec.period_end_date ,
X_revaluation_period => l_prd_rec.period_counter ,
X_status => 'COMPLETE' ,
X_reval_request_id => NULL ,
X_create_request_id => NULL ,
X_calling_program => 'IMPLEMENTATION' ,
X_mode => 'R',
x_event_id => p_event_id
) ;
INSERT INTO igi_iac_reval_categories
(
REVALUATION_ID ,
BOOK_TYPE_CODE ,
CATEGORY_ID ,
SELECT_CATEGORY ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATE_LOGIN ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY
)
VALUES
(
l_out_reval_id ,
l_corporate_book ,
p_category_id ,
'Y' ,
fnd_global.user_id ,
sysdate ,
fnd_global.login_id ,
sysdate ,
fnd_global.user_id
);
igi_iac_trans_headers_pkg.insert_row (
x_rowid => l_out_rowid ,
x_adjustment_id => l_prev_out_adj_id ,
x_transaction_header_id => null ,
x_adjustment_id_out => null ,
x_transaction_type_code => 'DEPRECIATION' ,
x_transaction_date_entered => l_prev_prd_rec.period_end_date ,
x_mass_refrence_id => l_out_reval_id ,
x_transaction_sub_type => 'IMPLEMENTATION' ,
x_book_type_code => l_corporate_book ,
x_asset_id => arec.asset_id ,
x_category_id => arec.category_id ,
x_adj_deprn_start_date => sysdate ,
x_revaluation_type_flag => 'P' ,
x_adjustment_status => 'COMPLETE' ,
x_period_counter => l_prev_prd_rec.period_counter,
x_mode => 'R',
x_event_id => p_event_id
) ;
SELECT igi_iac_transaction_headers_s.NEXTVAL
INTO l_out_adj_id
FROM sys.dual;
UPDATE igi_iac_transaction_headers
SET adjustment_id_out = l_out_adj_id
WHERE asset_id = arec.asset_id
AND book_type_code = l_corporate_book
AND adjustment_id_out IS NULL ;
igi_iac_trans_headers_pkg.insert_row (
x_rowid => l_out_rowid ,
x_adjustment_id => l_out_adj_id ,
x_transaction_header_id => null , -- mass ref id will be populated with reval id
x_adjustment_id_out => null ,
x_transaction_type_code => 'REVALUATION' ,
-- Bug 10180962 start
x_transaction_date_entered => l_prev_prd_rec.period_end_date ,
-- Bug 10180962 end
x_mass_refrence_id => l_out_reval_id ,
x_transaction_sub_type => 'IMPLEMENTATION' ,
x_book_type_code => l_corporate_book ,
x_asset_id => arec.asset_id ,
x_category_id => arec.category_id ,
x_adj_deprn_start_date => sysdate , --????
x_revaluation_type_flag => 'P' , --??? -- Setting to occassional
x_adjustment_status => 'COMPLETE' ,
-- Bug 10180962 start
x_period_counter => l_prev_prd_rec.period_counter,
-- Bug 10180962 end
x_mode => 'R',
x_event_id => p_event_id
) ;
igi_iac_asset_balances_pkg.insert_row (
x_rowid => l_out_rowid ,
x_asset_id => arec.asset_id ,
x_book_type_code => l_corporate_book ,
x_period_counter => l_prev_prd_rec.period_counter ,
x_net_book_value => l_net_book_value ,
x_adjusted_cost => l_adjusted_cost ,
x_operating_acct => l_operating_acct ,
x_reval_reserve => l_reval_reserve ,
x_deprn_amount => l_deprn_amount ,
x_deprn_reserve => l_deprn_reserve ,
x_backlog_deprn_reserve => l_backlog_deprn_reserve ,
x_general_fund => l_general_fund ,
x_last_reval_date => l_prev_prd_rec.period_end_date ,
x_current_reval_factor => l_current_reval_factor ,
x_cumulative_reval_factor => l_cumulative_reval_factor ,
x_mode => 'R'
);
igi_iac_asset_balances_pkg.insert_row (
x_rowid => l_out_rowid ,
x_asset_id => arec.asset_id ,
x_book_type_code => l_corporate_book ,
x_period_counter => l_prd_rec.period_counter ,
x_net_book_value => l_net_book_value ,
x_adjusted_cost => l_adjusted_cost ,
x_operating_acct => l_operating_acct ,
x_reval_reserve => l_reval_reserve ,
x_deprn_amount => l_deprn_amount ,
x_deprn_reserve => l_deprn_reserve ,
x_backlog_deprn_reserve => l_backlog_deprn_reserve ,
x_general_fund => l_general_fund ,
x_last_reval_date => l_prd_rec.period_end_date ,
x_current_reval_factor => l_current_reval_factor ,
x_cumulative_reval_factor => l_cumulative_reval_factor ,
x_mode => 'R'
);
SELECT number_per_fiscal_year
INTO l_num_per_fiscal_year
FROM fa_calendar_types ct ,
fa_book_controls bc
WHERE ct.calendar_type = bc.deprn_calendar
AND bc.book_type_code = l_corporate_book ;
SELECT period_num_for_catchup
INTO l_period_num_for_catchup
FROM igi_iac_book_controls ib
WHERE ib.book_type_code = l_corporate_book ;
INSERT INTO igi_iac_revaluation_rates
(
ASSET_ID ,
BOOK_TYPE_CODE ,
REVALUATION_ID ,
PERIOD_COUNTER ,
REVAL_TYPE ,
CURRENT_REVAL_FACTOR ,
CUMULATIVE_REVAL_FACTOR ,
PROCESSED_FLAG ,
LATEST_RECORD ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATE_LOGIN ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
ADJUSTMENT_ID
)
VALUES
(
arec.asset_id ,
l_corporate_book ,
l_out_reval_id ,
l_period_for_rates ,
'P' ,
l_current_reval_factor ,
l_cumulative_reval_factor ,
'Y' ,
'Y' ,
fnd_global.user_id ,
sysdate ,
fnd_global.login_id ,
sysdate ,
fnd_global.user_id ,
l_out_adj_id
);
UPDATE igi_imp_iac_interface --Fix for Bug 5137813
SET transferred_flag = 'Y'
WHERE book_type_code = arec.book_type_code
AND asset_id = arec.asset_id;
UPDATE igi_iac_category_books c
SET c.imp_run_number = nvl(c.imp_run_number ,0) + 1 ,
c.imp_period_counter = l_prd_rec.period_counter ,
c.imp_date = sysdate
WHERE c.book_type_code = l_corporate_book
AND c.category_id = p_category_id ;
p_string => SQL%rowcount || ' rows updated.');