[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT d_cal.number_per_fiscal_year,
p_cal.number_per_fiscal_year,
bc.deprn_calendar
INTO h_pds_per_year,
h_p_pds_per_year,
h_period_type
FROM fa_calendar_types d_cal,
fa_calendar_types p_cal,
fa_book_controls bc
WHERE bc.deprn_calendar = d_cal.calendar_type
AND bc.prorate_calendar = p_cal.calendar_type
AND bc.book_type_code = h_book_type;
SELECT fadp.period_num
INTO h_cpdnum
FROM fa_deprn_periods fadp
WHERE fadp.book_type_Code = h_book_type
AND fadp.period_name = h_cpdname;
SELECT
decode(m.depreciate_lastyear_flag,'YES',1,0),
nvl(book.life_in_months,0),
decode(book.capitalize_flag, 'YES', 1, 0),
decode(book.depreciate_flag, 'YES', 1, 0),
decode(book.period_counter_fully_reserved, null, 0, 1),
nvl(book.itc_amount_id, 0),
ah.units,
bc.current_fiscal_year,
bc.distribution_source_book,
book.rate_adjustment_factor,
nvl(book.adjusted_rate,0),
book.adjusted_cost,
book.cost,
book.recoverable_cost,
book.itc_amount,
nvl(book.salvage_value,0),
trunc(book.prorate_date),
to_number(to_char(book.prorate_date, 'J')),
decode(instr(m.rate_source_rule, 'CALCULATED'), 0,
trunc(book.deprn_start_date), trunc(book.prorate_date)),
decode(instr(m.rate_source_rule, 'CALCULATED'), 0,
to_number(to_char(book.deprn_start_date, 'J')),
to_number(to_char(book.prorate_date, 'J'))),
trunc(book.date_placed_in_service),
to_number(to_char(book.date_placed_in_service, 'J')),
bc.prorate_calendar,
m.method_code,
decode(bc.DEPR_FIRST_YEAR_RET_FLAG, 'YES', 1, 0),
conv.prorate_date,
trunc(bc.initial_date),
bc.deprn_calendar,
nvl(book.ceiling_name, null),
nvl(book.bonus_rule, null),
decode(m.rate_source_rule, 'CALCULATED', 1, 'TABLE', 2,
'FLAT', 3),
decode(m.deprn_basis_rule, 'COST', 1, 'NBV', 2),
decode(bc.book_class, 'TAX', 1, 0),
decode(ah.asset_type, 'CIP', 1, 0),
decode(ctype.depr_when_acquired_flag,'YES',1,0),
nvl(book.reval_amortization_basis,0),
book.unrevalued_cost,
nvl(book.adjusted_capacity,0),
nvl(book.production_capacity,0),
bc.fiscal_year_name,
nvl (book.adjusted_recoverable_cost, book.recoverable_cost),
decode(book.annual_deprn_rounding_flag, NULL, 0, 'ADD', 1,
'ADJ', 2, 'RET', 3, 'REV', 4, 'TFR', 5,'RES', 6, 'OVE', 7, -1),
nvl(book.short_fiscal_year_flag, 'NO'),
book.conversion_date,
book.original_deprn_start_date,
nvl(book.old_adjusted_cost, 1),
nvl(book.formula_factor, 1),
book.allowed_deprn_limit_amount,
book.group_asset_id,
book.recognize_gain_loss,
book.recapture_reserve_flag,
book.limit_proceeds_flag,
book.terminal_gain_loss,
book.tracking_method,
book.exclude_fully_rsv_flag,
book.excess_allocation_option,
book.depreciation_option,
book.member_rollup_flag,
book.ltd_proceeds,
book.allocate_to_fully_rsv_flag,
book.allocate_to_fully_ret_flag,
book.eofy_reserve,
book.cip_cost,
book.ltd_cost_of_removal,
book.prior_eofy_reserve,
book.eop_adj_cost,
book.eop_formula_factor,
book.exclude_proceeds_from_basis,
book.retirement_deprn_option,
book.terminal_gain_loss_amount,
book.period_counter_fully_reserved,
decode(book.period_counter_fully_extended, null, 0, 1),
book.period_counter_fully_extended,
decode(book.extended_deprn_flag,'Y', 1, 0),
book.extended_depreciation_period,
bc.last_period_counter + 1
INTO
h_depreciate_lastyr,
h_lifemonths,
h_capitalize,
h_depreciate,
h_fully_reserved,
h_itc_used,
h_cur_units,
h_cpd_fiscal_year,
h_dis_book,
h_raf,
h_adj_rate,
h_adjusted_cost,
h_current_cost,
h_recoverable_cost,
h_itc_amount,
h_salvage_value,
h_prorate_date,
h_prorate_jdate,
h_deprn_start_date,
h_deprn_start_jdate,
h_date_in_srv,
h_jdis,
h_p_cal,
h_method_code,
h_depr_first_year_ret,
h_ret_prorate_date,
h_initial_date,
h_deprn_cal,
h_ceiling_name,
h_bonus_rule,
h_rate_source_rule,
h_deprn_basis_rule,
h_book_class,
h_wip_asset,
h_dwacq,
h_reval_amort_basis,
h_unrevalued_cost,
h_adj_capacity,
h_capacity,
h_fiscal_year_name,
h_adj_rec_cost,
h_annual_deprn_rounding_flag,
h_short_fiscal_year_flag,
h_conversion_date,
h_orig_deprn_start_date,
h_old_adj_cost,
h_formula_factor,
h_allowed_deprn_limit_amount,
h_group_asset_id,
h_recognize_gain_loss,
h_recapture_reserve_flag,
h_limit_proceeds_flag,
h_terminal_gain_loss,
h_tracking_method,
h_exclude_fully_rsv_flag,
h_excess_allocation_option,
h_depreciation_option,
h_member_rollup_flag,
h_ltd_proceeds,
h_allocate_to_fully_rsv_flag,
h_allocate_to_fully_ret_flag,
h_eofy_reserve,
h_cip_cost,
h_ltd_cost_of_removal,
h_prior_eofy_reserve,
h_eop_adj_cost,
h_eop_formula_factor,
h_exclude_proceeds_from_basis,
h_retirement_deprn_option,
h_terminal_gain_loss_amount,
h_pc_fully_reserved,
h_fully_extended, -- Bug 6660490
h_pc_fully_extended, -- Bug 6660490
h_extended_flag, -- Bug 6660490
h_pc_extended, -- Bug 8211842
h_current_pc -- Bug 8211842
FROM
fa_books book,
fa_methods m,
fa_conventions conv,
fa_convention_types ctype,
fa_book_controls bc,
fa_asset_history ah
WHERE
book.retirement_id = h_retirement_id
AND book.asset_id = h_asset_id
AND book.book_type_code = h_book
AND book.deprn_method_code = m.method_code
AND nvl(book.life_in_months,1) = nvl(m.life_in_months,1)
AND
bc.book_type_code = h_book
AND
ah.asset_id = h_asset_id
AND
book.transaction_header_id_out <=
nvl(ah.transaction_header_id_out,
book.transaction_header_id_out)
AND
book.transaction_header_id_out >
ah.transaction_header_id_in
AND trunc(h_date_retired) between
conv.start_date and conv.end_date
AND h_ret_p_conv = conv.prorate_convention_code
AND ctype.prorate_convention_code = h_ret_p_conv;
SELECT
decode(m.depreciate_lastyear_flag,'YES',1,0),
nvl(book.life_in_months,0),
decode(book.capitalize_flag, 'YES', 1, 0),
decode(book.depreciate_flag, 'YES', 1, 0),
decode(book.period_counter_fully_reserved, null, 0, 1),
nvl(book.itc_amount_id, 0),
ah.units,
bc.current_fiscal_year,
bc_primary.distribution_source_book,
book.rate_adjustment_factor,
nvl(book.adjusted_rate,0),
book.adjusted_cost,
book.cost,
book.recoverable_cost,
book.itc_amount,
nvl(book.salvage_value,0),
trunc(book.prorate_date),
to_number(to_char(book.prorate_date, 'J')),
decode(instr(m.rate_source_rule, 'CALCULATED'), 0,
trunc(book.deprn_start_date), trunc(book.prorate_date)),
decode(instr(m.rate_source_rule, 'CALCULATED'), 0,
to_number(to_char(book.deprn_start_date, 'J')),
to_number(to_char(book.prorate_date, 'J'))),
trunc(book.date_placed_in_service),
to_number(to_char(book.date_placed_in_service, 'J')),
bc_primary.prorate_calendar,
m.method_code,
decode(bc_primary.DEPR_FIRST_YEAR_RET_FLAG, 'YES', 1, 0),
conv.prorate_date,
trunc(bc_primary.initial_date),
bc_primary.deprn_calendar,
nvl(book.ceiling_name, null),
nvl(book.bonus_rule, null),
decode(m.rate_source_rule, 'CALCULATED', 1, 'TABLE', 2,
'FLAT', 3),
decode(m.deprn_basis_rule, 'COST', 1, 'NBV', 2),
decode(bc_primary.book_class, 'TAX', 1, 0),
decode(ah.asset_type, 'CIP', 1, 0),
decode(ctype.depr_when_acquired_flag,'YES',1,0),
nvl(book.reval_amortization_basis,0),
book.unrevalued_cost,
nvl(book.adjusted_capacity,0),
nvl(book.production_capacity,0),
bc_primary.fiscal_year_name,
nvl (book.adjusted_recoverable_cost, book.recoverable_cost),
decode(book.annual_deprn_rounding_flag, NULL, 0, 'ADD', 1,
'ADJ', 2, 'RET', 3, 'REV', 4, 'TFR', 5,'RES', 6, 'OVE', 7, -1),
nvl(book.short_fiscal_year_flag, 'NO'),
book.conversion_date,
book.original_deprn_start_date,
nvl(book.old_adjusted_cost, 1),
nvl(book.formula_factor, 1),
book.allowed_deprn_limit_amount,
book.group_asset_id,
book.recognize_gain_loss,
book.recapture_reserve_flag,
book.limit_proceeds_flag,
book.terminal_gain_loss,
book.tracking_method,
book.exclude_fully_rsv_flag,
book.excess_allocation_option,
book.depreciation_option,
book.member_rollup_flag,
book.ltd_proceeds,
book.allocate_to_fully_rsv_flag,
book.allocate_to_fully_ret_flag,
book.eofy_reserve,
book.cip_cost,
book.ltd_cost_of_removal,
book.prior_eofy_reserve,
book.eop_adj_cost,
book.eop_formula_factor,
book.exclude_proceeds_from_basis,
book.retirement_deprn_option,
book.terminal_gain_loss_amount,
book.period_counter_fully_reserved
INTO
h_depreciate_lastyr,
h_lifemonths,
h_capitalize,
h_depreciate,
h_fully_reserved,
h_itc_used,
h_cur_units,
h_cpd_fiscal_year,
h_dis_book,
h_raf,
h_adj_rate,
h_adjusted_cost,
h_current_cost,
h_recoverable_cost,
h_itc_amount,
h_salvage_value,
h_prorate_date,
h_prorate_jdate,
h_deprn_start_date,
h_deprn_start_jdate,
h_date_in_srv,
h_jdis,
h_p_cal,
h_method_code,
h_depr_first_year_ret,
h_ret_prorate_date,
h_initial_date,
h_deprn_cal,
h_ceiling_name,
h_bonus_rule,
h_rate_source_rule,
h_deprn_basis_rule,
h_book_class,
h_wip_asset,
h_dwacq,
h_reval_amort_basis,
h_unrevalued_cost,
h_adj_capacity,
h_capacity,
h_fiscal_year_name,
h_adj_rec_cost,
h_annual_deprn_rounding_flag,
h_short_fiscal_year_flag,
h_conversion_date,
h_orig_deprn_start_date,
h_old_adj_cost,
h_formula_factor,
h_allowed_deprn_limit_amount,
h_group_asset_id,
h_recognize_gain_loss,
h_recapture_reserve_flag,
h_limit_proceeds_flag,
h_terminal_gain_loss,
h_tracking_method,
h_exclude_fully_rsv_flag,
h_excess_allocation_option,
h_depreciation_option,
h_member_rollup_flag,
h_ltd_proceeds,
h_allocate_to_fully_rsv_flag,
h_allocate_to_fully_ret_flag,
h_eofy_reserve,
h_cip_cost,
h_ltd_cost_of_removal,
h_prior_eofy_reserve,
h_eop_adj_cost,
h_eop_formula_factor,
h_exclude_proceeds_from_basis,
h_retirement_deprn_option,
h_terminal_gain_loss_amount,
h_pc_fully_reserved
FROM
fa_mc_books book,
fa_methods m,
fa_conventions conv,
fa_convention_types ctype,
fa_mc_book_controls bc,
fa_book_controls bc_primary,
fa_asset_history ah
WHERE
book.retirement_id = h_retirement_id
AND book.asset_id = h_asset_id
AND book.book_type_code = h_book
AND book.deprn_method_code = m.method_code
AND nvl(book.life_in_months,1) = nvl(m.life_in_months,1)
AND
bc.book_type_code = h_book
AND
ah.asset_id = h_asset_id
AND
book.transaction_header_id_out <=
nvl(ah.transaction_header_id_out,
book.transaction_header_id_out)
AND
book.transaction_header_id_out >
ah.transaction_header_id_in
AND trunc(h_date_retired) between
conv.start_date and conv.end_date
AND h_ret_p_conv = conv.prorate_convention_code
AND book.set_of_books_id = ret.set_of_books_id
AND bc.set_of_books_id = ret.set_of_books_id
AND bc_primary.book_type_code = bc.book_type_code
AND ctype.prorate_convention_code = h_ret_p_conv;
select decode(fy1.fiscal_year, fy2.fiscal_year,1,0)
INTO h_same_fy
FROM FA_FISCAL_YEAR FY1, FA_FISCAL_YEAR FY2
WHERE trunc(h_date_retired) between fy1.start_date and fy1.end_date
AND trunc(h_deprn_start_date)
between fy2.start_date and fy2.end_date
AND fy1.fiscal_year_name = h_fiscal_year_name
AND fy2.fiscal_year_name = h_fiscal_year_name;
SELECT FISCAL.FISCAL_YEAR
INTO h_ret_fiscalyr
FROM FA_FISCAL_YEAR FISCAL
WHERE trunc(h_ret_prorate_date)
between START_DATE and END_DATE
AND fiscal_year_name = h_fiscal_year_name;
select trunc(start_date)
into h_ret_prorate_date
from fa_fiscal_year
where fiscal_year = h_ret_fiscalyr
and fiscal_year_name = h_fiscal_year_name;
SELECT start_date
INTO h_ret_prorate_date
FROM fa_fiscal_year
where fiscal_year = h_cpd_fiscal_year
and fiscal_year_name = h_fiscal_year_name;
SELECT h_deprn_start_date
INTO h_ret_prorate_date
FROM dual;
SELECT h_deprn_start_date
INTO h_ret_prorate_date
FROM dual
where trunc(h_ret_prorate_date) < trunc(h_deprn_start_date);
SELECT h_prorate_date
INTO h_ret_prorate_date
FROM dual
WHERE trunc(h_ret_prorate_date) < trunc(h_prorate_date);
SELECT h_date_retired
INTO h_ret_prorate_date
from fa_deprn_periods
where book_type_code = h_book
and period_close_date is null
and (h_ret_prorate_date < calendar_period_open_date or h_ret_prorate_date > calendar_period_close_date);