[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select cp.period_name period_name
, cp.period_num period_num
, fy.fiscal_year fiscal_year
from fa_fiscal_year fy
, fa_calendar_periods cp
where fy.fiscal_year_name = fa_cache_pkg.fazcbc_record.fiscal_year_name
and cp.calendar_type = fa_cache_pkg.fazcbc_record.deprn_calendar
and cp.start_date between fy.start_date and fy.end_date
and l_st_period_counter <= fy.fiscal_year * fa_cache_pkg.fazcct_record.NUMBER_PER_FISCAL_YEAR + cp.PERIOD_NUM
and l_ed_period_counter >= fy.fiscal_year * fa_cache_pkg.fazcct_record.NUMBER_PER_FISCAL_YEAR + cp.PERIOD_NUM
order by fiscal_year, period_num;
select sysdate into h_current_time from dual;
SELECT decode (mt.rate_source_rule,
'CALCULATED', bk.prorate_date,
'FORMULA', bk.prorate_date,
'TABLE', bk.deprn_start_date,
'FLAT', decode (mt.deprn_basis_rule,
'COST', bk.prorate_date,
'NBV', bk.deprn_start_date),
'PROD', bk.date_placed_in_service),
to_number (to_char (bk.prorate_date, 'J')),
to_number (to_char (bk.date_placed_in_service, 'J')),
to_number (to_char (bk.deprn_start_date, 'J')),
decode(mt.rate_source_rule, 'FLAT', bk.life_in_months,
nvl(bk.life_in_months, 0)),
bk.recoverable_cost,
bk.adjusted_cost,
bk.cost,
nvl(bk.reval_amortization_basis, 0),
bk.rate_adjustment_factor,
nvl(bk.adjusted_rate, 0),
bk.ceiling_name,
bk.bonus_rule,
nvl (bk.production_capacity, 0),
nvl (bk.adjusted_capacity, 0),
mt.method_code,
ad.asset_number,
nvl (bk.adjusted_recoverable_cost, bk.recoverable_cost),
bk.salvage_value,
bk.period_counter_life_complete,
bk.adjustment_required_status,
bk.annual_deprn_rounding_flag,
bk.itc_amount_id,
bk.itc_basis,
ceilt.ceiling_type,
nvl(bk.formula_factor, 1),
nvl(bk.short_fiscal_year_flag, 'NO'),
bk.conversion_date,
bk.original_deprn_start_date,
bk.prorate_date
, ad.asset_type
, bk.eofy_reserve -- Bug#1248589
, bk.prior_eofy_reserve --Bug#16045341
INTO h_dpr_date,
dpr_in.prorate_jdate,
dpr_in.jdate_in_service,
dpr_in.deprn_start_jdate,
dpr_in.life,
dpr_in.rec_cost,
dpr_in.adj_cost,
h_current_cost,
dpr_in.reval_amo_basis,
dpr_in.rate_adj_factor,
dpr_in.adj_rate,
dpr_in.ceil_name,
dpr_in.bonus_rule,
dpr_in.capacity,
dpr_in.adj_capacity,
dpr_in.method_code,
dpr_in.asset_num,
dpr_in.adj_rec_cost,
dpr_in.salvage_value,
dpr_in.pc_life_end,
l_adjustment_required_status,
dpr_in.deprn_rounding_flag,
h_itc_amount_id,
h_itc_basis,
h_ceiling_Type,
dpr_in.formula_factor,
dpr_in.short_fiscal_year_flag,
dpr_in.conversion_date,
dpr_in.orig_deprn_start_date,
dpr_in.prorate_date
, dpr_in.asset_type
, dpr_in.eofy_reserve -- Bug#1248589
, h_prior_eofy_reserve --Bug#16045341
FROM fa_ceiling_types ceilt,
fa_methods mt,
fa_category_books cb,
fa_books bk,
fa_additions_b ad
WHERE cb.book_type_code = X_book
AND ad.asset_category_id = cb.category_id
AND ceilt.ceiling_name(+) = bk.ceiling_name
AND mt.method_code = bk.deprn_method_code
AND bk.book_type_code = X_book
AND bk.asset_id = X_asset_id
AND bk.transaction_header_id_out is null
AND nvl (mt.life_in_months, -9999) = nvl (bk.life_in_months, -9999)
AND ad.asset_id = bk.asset_id;
SELECT decode (mt.rate_source_rule,
'CALCULATED', bk.prorate_date,
'FORMULA', bk.prorate_date,
'TABLE', bk.deprn_start_date,
'FLAT', decode (mt.deprn_basis_rule,
'COST', bk.prorate_date,
'NBV', bk.deprn_start_date),
'PROD', bk.date_placed_in_service),
to_number (to_char (bk.prorate_date, 'J')),
to_number (to_char (bk.date_placed_in_service, 'J')),
to_number (to_char (bk.deprn_start_date, 'J')),
decode(mt.rate_source_rule, 'FLAT', bk.life_in_months,
nvl(bk.life_in_months, 0)),
bk.recoverable_cost,
bk.adjusted_cost,
bk.cost,
nvl(bk.reval_amortization_basis, 0),
bk.rate_adjustment_factor,
nvl(bk.adjusted_rate, 0),
bk.ceiling_name,
bk.bonus_rule,
nvl (bk.production_capacity, 0),
nvl (bk.adjusted_capacity, 0),
mt.method_code,
ad.asset_number,
nvl (bk.adjusted_recoverable_cost, bk.recoverable_cost),
bk.salvage_value,
bk.period_counter_life_complete,
bk.adjustment_required_status,
bk.annual_deprn_rounding_flag,
bk.itc_amount_id,
bk.itc_basis,
ceilt.ceiling_type,
nvl(bk.formula_factor, 1),
nvl(bk.short_fiscal_year_flag, 'NO'),
bk.conversion_date,
bk.original_deprn_start_date,
bk.prorate_date
, ad.asset_type
, bk.eofy_reserve -- Bug#1248589
, bk.prior_eofy_reserve --Bug#16045341
INTO h_dpr_date,
dpr_in.prorate_jdate,
dpr_in.jdate_in_service,
dpr_in.deprn_start_jdate,
dpr_in.life,
dpr_in.rec_cost,
dpr_in.adj_cost,
h_current_cost,
dpr_in.reval_amo_basis,
dpr_in.rate_adj_factor,
dpr_in.adj_rate,
dpr_in.ceil_name,
dpr_in.bonus_rule,
dpr_in.capacity,
dpr_in.adj_capacity,
dpr_in.method_code,
dpr_in.asset_num,
dpr_in.adj_rec_cost,
dpr_in.salvage_value,
dpr_in.pc_life_end,
l_adjustment_required_status,
dpr_in.deprn_rounding_flag,
h_itc_amount_id,
h_itc_basis,
h_ceiling_Type,
dpr_in.formula_factor,
dpr_in.short_fiscal_year_flag,
dpr_in.conversion_date,
dpr_in.orig_deprn_start_date,
dpr_in.prorate_date
, dpr_in.asset_type
, dpr_in.eofy_reserve -- Bug#1248589
, h_prior_eofy_reserve --Bug#16045341
FROM fa_ceiling_types ceilt,
fa_methods mt,
fa_category_books cb,
fa_mc_books bk,
fa_additions_b ad
WHERE cb.book_type_code = X_book
AND ad.asset_category_id = cb.category_id
AND ceilt.ceiling_name(+) = bk.ceiling_name
AND mt.method_code = bk.deprn_method_code
AND bk.book_type_code = X_book
AND bk.asset_id = X_asset_id
AND bk.transaction_header_id_out is null
AND bk.set_of_books_id = FARX_C_WD.sob_id
AND nvl (mt.life_in_months, -9999) = nvl (bk.life_in_months, -9999)
AND ad.asset_id = bk.asset_id;
SELECT cbd.life_in_months,
cbd.deprn_method,
cbd.prorate_convention_code,
cbd.adjusted_rate,
cbd.bonus_rule,
cbd.ceiling_name
INTO dpr_in.life,
dpr_in.method_code,
h_prorate_conv,
dpr_in.adj_rate,
dpr_in.bonus_rule,
dpr_in.ceil_name
FROM FA_CATEGORY_BOOK_DEFAULTS cbd
WHERE cbd.book_type_code = X_book
AND cbd.category_id = X_category_id
AND X_dpis BETWEEN CBD.START_DPIS AND
NVL(CBD.END_DPIS,TO_DATE('31-12-4712','DD-MM-YYYY'));
SELECT to_number(to_char(conv.prorate_date,'J'))
INTO dpr_in.prorate_jdate
FROM fa_conventions conv
WHERE conv.prorate_convention_code = h_prorate_conv
AND X_dpis between conv.start_date and conv.end_date;
select category_id, units, asset_type
into fin_info.category_id, fin_info.units, fin_info.asset_type
from fa_asset_history
where asset_id = X_asset_id and date_ineffective is null;
select count(*) into h_count from fa_methods
where method_code = X_method_code
and rate_source_rule in ('TABLE','CALCULATED','FORMULA')
and rownum < 2;
select prorate_date into fin_info.prorate_date
from fa_conventions
where prorate_convention_code = X_prorate_conv
and fin_info.date_placed_in_svc between start_date and end_date;
select least(fin_info.cost - fin_info.salvage_value,
nvl(ce.limit, fin_info.cost - fin_info.salvage_value))
into fin_info.rec_cost
from fa_ceilings ce
where ce.ceiling_name = fin_info.ceiling_name
and fin_info.date_placed_in_svc
between ce.start_date
and nvl(ce.end_date, fin_info.date_placed_in_svc);
select least(fin_info.cost - fin_info.salvage_value -
h_itc_basis * ir.basis_reduction_rate,
nvl(ce.limit, fin_info.cost - fin_info.salvage_value -
h_itc_basis * ir.basis_reduction_rate))
into fin_info.rec_cost
from fa_ceilings ce, fa_itc_rates ir
where ir.itc_amount_id = h_itc_amount_id
and ce.ceiling_name = fin_info.ceiling_name
and fin_info.date_placed_in_svc
between ce.start_date
and nvl(ce.end_date, fin_info.date_placed_in_svc);
select fin_info.cost - fin_info.salvage_value -
h_itc_basis * ir.basis_reduction_rate
into fin_info.rec_cost
from fa_itc_rates ir
where ir.itc_amount_id = h_itc_amount_id;
SELECT CBD.USE_DEPRN_LIMITS_FLAG
, CBD.ALLOWED_DEPRN_LIMIT
, CBD.SPECIAL_DEPRN_LIMIT_AMOUNT
INTO h_use_deprn_limits_flag
, h_allowed_deprn_limit
, h_allowed_deprn_limit_amt
FROM FA_ADDITIONS_B FAD
, FA_CATEGORY_BOOK_DEFAULTS CBD
WHERE FAD.ASSET_ID = fin_info.asset_id
AND CBD.CATEGORY_ID = FAD.ASSET_CATEGORY_ID
AND CBD.BOOK_TYPE_CODE = fin_info.book
AND fin_info.date_placed_in_svc
BETWEEN CBD.START_DPIS
AND NVL(CBD.END_DPIS,TO_DATE('31-12-4712','DD-MM-YYYY'));
select fiscal_year
into first_fy
from fa_deprn_periods where
book_type_code = X_book
and period_counter = ( select period_counter + 1
from fa_deprn_summary where
book_type_code = X_book
and asset_id = X_asset_id
and deprn_source_code = 'BOOKS'); */
select count(*)
into first_fy
from dual
where EXISTS (
select 'Y'
from fa_deprn_periods fadp,
fa_deprn_summary fads
where fadp.book_type_code = X_book
and fads.book_type_code = X_book
and fads.asset_id = X_asset_id
and fads.deprn_source_code = 'BOOKS'
and fads.deprn_reserve = 0
and fadp.period_counter = fads.period_counter + 1
and fadp.fiscal_year = dpr_in.y_begin);
select cp.period_num
, fy.fiscal_year
, cp.start_date
into h_start_per_num
, h_start_per_fy
, l_cp_start_date
from fa_calendar_periods cp, fa_fiscal_year fy
where cp.period_name = X_start_per
and cp.calendar_type = h_calendar_type
and cp.start_date >= fy.start_date
and cp.end_date <= fy.end_date
and fy.fiscal_year_name = h_fy_name;
SELECT cp.period_num
, fy.fiscal_year
into dpr_in.p_cl_begin
, dpr_in.y_begin
FROM fa_calendar_periods cp
, fa_fiscal_year fy
, fa_book_controls fb
, fa_books fk
WHERE cp.calendar_type = fb.deprn_calendar
AND fb.book_type_code = X_book
AND cp.start_date >= fy.start_date
AND cp.end_date <= fy.end_date
AND fk.book_type_code = fb.book_type_code
AND fk.asset_id = X_asset_id
AND fk.date_ineffective IS NULL
AND fy.fiscal_year_name = fb.fiscal_year_name
AND (fy.fiscal_year * h_num_per_fy + cp.period_num) = fk.extended_depreciation_period;
Select cal.period_num , cal.fiscal_year
into dpr_in.p_cl_begin , dpr_in.y_begin
From fa_books fk,
(
Select cp.period_num period_num,
fy.fiscal_year fiscal_year
From fa_fiscal_year fy
, fa_calendar_periods cp
WHERE cp.calendar_type = h_calendar_type
AND cp.start_date >= fy.start_date
AND cp.end_date <= fy.end_date
AND fy.fiscal_year_name = h_fy_name
) cal
where fk.book_type_code = X_book
AND fk.asset_id = X_asset_id
AND fk.date_ineffective IS NULL
AND fk.extended_depreciation_period = cal.period_num + (cal.fiscal_year * h_num_per_fy);
select 'Y'
into h_deprn_run
from fa_deprn_summary
where asset_id = dpr_in.asset_id
and book_type_code = dpr_in.book
and period_counter = fa_cache_pkg.fazcdp_record.period_counter --Bug#16045341
and deprn_source_code = 'DEPRN';
select system_deprn_amount
into adj_amt
from fa_deprn_summary
where asset_id = dpr_in.asset_id
and book_type_code = dpr_in.book
and period_counter = dpr_row.period_ctr;
SELECT COUNT(1)
INTO h_count
from fa_deprn_summary
where book_type_code = dpr_in.book
and asset_id = dpr_in.asset_id
and period_counter = fa_cache_pkg.fazcdp_record.period_counter - 1;
dpr_arr.delete;
g_deprn.delete;
g_deprn.delete;
function whatif_insert_itf (
X_asset_id in number,
X_book in varchar2,
X_request_id in number,
X_num_pers in number,
X_acct_struct in number,
X_key_struct in number,
X_cat_struct in number,
X_loc_struct in number,
X_precision in number,
X_user_id in number,
X_login_id in number,
X_last_asset in boolean default false,
x_return_status out nocopy number) return boolean is
h_dist_book fa_book_controls.distribution_source_book%TYPE;
Select distribution_source_book
From fa_book_controls
Where book_type_code = X_book;
select dh.units_assigned, dh.code_combination_id, dh.location_id,
emp.employee_number, emp.full_name
from fa_distribution_history dh, per_all_people_f emp
where emp.person_id (+) = dh.assigned_to
and trunc(sysdate) between emp.effective_start_date(+) and emp.effective_end_date(+)
and dh.book_type_code = h_dist_book
and dh.asset_id = X_asset_id
and dh.date_ineffective is null;
select description, tag_number, serial_number, asset_number,
asset_key_ccid, current_units, asset_category_id
into h_description, h_tag_number, h_serial_number, h_asset_number,
h_asset_key_id, h_total_units, h_category_id
from fa_additions
where asset_id = X_asset_id;
select cost, prorate_convention_code, deprn_method_code,
life_in_months, basic_rate, adjusted_rate, salvage_value, bonus_rule,
date_placed_in_service
into h_current_cost, h_current_prorate_conv, h_current_method,
h_current_life, h_current_basic_rate, h_current_adjusted_rate,
h_current_salvage_value, h_current_bonus_rule,
h_current_dpis
from fa_books
where asset_id = X_asset_id
and book_type_code = X_book
and transaction_header_id_out is null;
select cost, prorate_convention_code, deprn_method_code,
life_in_months, basic_rate, adjusted_rate, salvage_value, bonus_rule,
date_placed_in_service
into h_current_cost, h_current_prorate_conv, h_current_method,
h_current_life, h_current_basic_rate, h_current_adjusted_rate,
h_current_salvage_value, h_current_bonus_rule,
h_current_dpis
from fa_mc_books
where asset_id = X_asset_id
and book_type_code = X_book
and transaction_header_id_out is null
and set_of_books_id = FARX_C_WD.sob_id;
select currency_code
into h_currency
from gl_sets_of_books
where set_of_books_id = FARX_C_WD.sob_id;
h_mesg_name := 'FA_SHARED_INSERT_FAIL';
t_last_update_date(i) := sysdate;
t_last_updated_by(i) := X_user_id;
t_last_update_login(i) := X_login_id;
t_tot_new_bonus_deprn.delete;
t_tot_bonus_deprn.delete;
t_tot_new_rsv.delete;
t_tot_new_deprn.delete;
t_tot_deprn.delete;
INSERT INTO FA_WHATIF_ITF(
request_id
, book_type_code
, asset_id
, asset_number
, description
, tag_number
, serial_number
, period_name
, fiscal_year
, expense_acct
, location
, units
, employee_name
, employee_number
, asset_key
, current_cost
, current_prorate_conv
, current_method
, current_life
, current_basic_rate
, current_adjusted_rate
, current_salvage_value
, depreciation
, new_depreciation
, created_by
, creation_date
, last_update_date
, last_updated_by
, last_update_login
, date_placed_in_service
, category
, accumulated_deprn
, bonus_depreciation
, new_bonus_depreciation
, current_bonus_rule
, period_num
, currency_code)
VALUES( t_request_id(j)
, t_book_type_code(j)
, t_asset_id(j)
, t_asset_number(j)
, t_description(j)
, t_tag_number(j)
, t_serial_number(j)
, t_period_name(j)
, t_fiscal_year(j)
, t_expense_acct(j)
, t_location(j)
, t_units(j)
, t_employee_name(j)
, t_employee_number(j)
, t_asset_key(j)
, t_current_cost(j)
, t_current_prorate_conv(j)
, t_current_method(j)
, t_current_life(j)
, t_current_basic_rate(j)
, t_current_adjusted_rate(j)
, t_current_salvage_value(j)
, t_depreciation(j)
, t_new_depreciation(j)
, t_created_by(j)
, t_creation_date(j)
, t_last_update_date(j)
, t_last_updated_by(j)
, t_last_update_login(j)
, t_date_placed_in_service(j)
, t_category(j)
, t_accumulated_deprn(j)
, t_bonus_depreciation(j)
, t_new_bonus_depreciation(j)
, t_current_bonus_rule(j)
, t_period_num(j)
, t_currency_code(j));
t_request_id.delete;
t_book_type_code.delete;
t_asset_id.delete;
t_asset_number.delete;
t_description.delete;
t_tag_number.delete;
t_serial_number.delete;
t_period_name.delete;
t_fiscal_year.delete;
t_expense_acct.delete;
t_location.delete;
t_units.delete;
t_employee_name.delete;
t_employee_number.delete;
t_asset_key.delete;
t_current_cost.delete;
t_current_prorate_conv.delete;
t_current_method.delete;
t_current_life.delete;
t_current_basic_rate.delete;
t_current_adjusted_rate.delete;
t_current_salvage_value.delete;
t_depreciation.delete;
t_new_depreciation.delete;
t_created_by.delete;
t_creation_date.delete;
t_last_update_date.delete;
t_last_updated_by.delete;
t_last_update_login.delete;
t_date_placed_in_service.delete;
t_category.delete;
t_accumulated_deprn.delete;
t_bonus_depreciation.delete;
t_new_bonus_depreciation.delete;
t_current_bonus_rule.delete;
t_period_num.delete;
t_currency_code.delete;
g_deprn.delete;
if h_mesg_name = 'FA_SHARED_INSERT_FAIL' then
fnd_message.set_token('TABLE','FA_WHATIF_ITF',FALSE);
end whatif_insert_itf;