[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT book_type_code,
book_type_name,
set_of_books_id,
initial_date,
last_deprn_run_date,
amortize_flag,
fully_reserved_flag,
deprn_calendar,
book_class,
gl_posting_allowed_flag,
current_fiscal_year,
allow_mass_changes,
allow_deprn_adjustments,
accounting_flex_structure,
last_update_date,
last_updated_by,
prorate_calendar,
date_ineffective,
je_retirement_category,
je_depreciation_category,
je_reclass_category,
gl_je_source,
je_addition_category,
je_adjustment_category,
distribution_source_book,
je_transfer_category,
copy_retirements_flag,
copy_adjustments_flag,
deprn_request_id,
allow_cost_ceiling,
allow_deprn_exp_ceiling,
calculate_nbv,
run_year_end_program,
je_deferred_deprn_category,
itc_allowed_flag,
created_by,
creation_date,
last_update_login,
allow_mass_copy,
allow_purge_flag,
allow_reval_flag,
amortize_reval_reserve_flag,
ap_intercompany_acct,
ar_intercompany_acct,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute_category_code,
capital_gain_threshold,
copy_salvage_value_flag,
cost_of_removal_clearing_acct,
cost_of_removal_gain_acct,
cost_of_removal_loss_acct,
default_life_extension_ceiling,
default_life_extension_factor,
default_max_fully_rsvd_revals,
default_reval_fully_rsvd_flag,
deferred_deprn_expense_acct,
deferred_deprn_reserve_acct,
deprn_allocation_code,
deprn_status,
fiscal_year_name,
initial_period_counter,
je_cip_adjustment_category,
je_cip_addition_category,
je_cip_reclass_category,
je_cip_retirement_category,
je_cip_reval_category,
je_cip_transfer_category,
je_reval_category,
last_mass_copy_period_counter,
last_period_counter,
last_purge_period_counter,
mass_copy_source_book,
mass_request_id,
nbv_amount_threshold,
nbv_fraction_threshold,
nbv_retired_gain_acct,
nbv_retired_loss_acct,
proceeds_of_sale_clearing_acct,
proceeds_of_sale_gain_acct,
proceeds_of_sale_loss_acct,
revalue_on_retirement_flag,
reval_deprn_reserve_flag,
reval_rsv_retired_gain_acct,
reval_rsv_retired_loss_acct,
deprn_adjustment_acct,
immediate_copy_flag,
je_deprn_adjustment_category,
depr_first_year_ret_flag,
flexbuilder_defaults_ccid,
retire_reval_reserve_flag,
use_current_nbv_for_deprn,
copy_additions_flag,
use_percent_salvage_value_flag,
reval_posting_flag,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
global_attribute_category,
mc_source_flag,
reval_ytd_deprn_flag,
allow_cip_assets_flag,
org_id,
allow_group_deprn_flag,
allow_cip_dep_group_flag,
allow_interco_group_flag,
copy_group_addition_flag,
copy_group_assignment_flag,
allow_cip_member_flag,
allow_member_tracking_flag,
INTERCOMPANY_POSTING_FLAG,
allow_cost_sign_change_flag,
sorp_enabled_flag,
allow_impairment_flag,
copy_amort_adaj_exp_flag,
copy_group_change_flag,
prevent_prior_period_txns_flag,
allow_unallocated_lines_flag,
IMPORT_ALLOC_INV_LINES_AS_NEW
INTO fazcbc_record.book_type_code,
fazcbc_record.book_type_name,
fazcbc_record.set_of_books_id,
fazcbc_record.initial_date,
fazcbc_record.last_deprn_run_date,
fazcbc_record.amortize_flag,
fazcbc_record.fully_reserved_flag,
fazcbc_record.deprn_calendar,
fazcbc_record.book_class,
fazcbc_record.gl_posting_allowed_flag,
fazcbc_record.current_fiscal_year,
fazcbc_record.allow_mass_changes,
fazcbc_record.allow_deprn_adjustments,
fazcbc_record.accounting_flex_structure,
fazcbc_record.last_update_date,
fazcbc_record.last_updated_by,
fazcbc_record.prorate_calendar,
fazcbc_record.date_ineffective,
fazcbc_record.je_retirement_category,
fazcbc_record.je_depreciation_category,
fazcbc_record.je_reclass_category,
fazcbc_record.gl_je_source,
fazcbc_record.je_addition_category,
fazcbc_record.je_adjustment_category,
fazcbc_record.distribution_source_book,
fazcbc_record.je_transfer_category,
fazcbc_record.copy_retirements_flag,
fazcbc_record.copy_adjustments_flag,
fazcbc_record.deprn_request_id,
fazcbc_record.allow_cost_ceiling,
fazcbc_record.allow_deprn_exp_ceiling,
fazcbc_record.calculate_nbv,
fazcbc_record.run_year_end_program,
fazcbc_record.je_deferred_deprn_category,
fazcbc_record.itc_allowed_flag,
fazcbc_record.created_by,
fazcbc_record.creation_date,
fazcbc_record.last_update_login,
fazcbc_record.allow_mass_copy,
fazcbc_record.allow_purge_flag,
fazcbc_record.allow_reval_flag,
fazcbc_record.amortize_reval_reserve_flag,
fazcbc_record.ap_intercompany_acct,
fazcbc_record.ar_intercompany_acct,
fazcbc_record.attribute1,
fazcbc_record.attribute2,
fazcbc_record.attribute3,
fazcbc_record.attribute4,
fazcbc_record.attribute5,
fazcbc_record.attribute6,
fazcbc_record.attribute7,
fazcbc_record.attribute8,
fazcbc_record.attribute9,
fazcbc_record.attribute10,
fazcbc_record.attribute11,
fazcbc_record.attribute12,
fazcbc_record.attribute13,
fazcbc_record.attribute14,
fazcbc_record.attribute15,
fazcbc_record.attribute_category_code,
fazcbc_record.capital_gain_threshold,
fazcbc_record.copy_salvage_value_flag,
fazcbc_record.cost_of_removal_clearing_acct,
fazcbc_record.cost_of_removal_gain_acct,
fazcbc_record.cost_of_removal_loss_acct,
fazcbc_record.default_life_extension_ceiling,
fazcbc_record.default_life_extension_factor,
fazcbc_record.default_max_fully_rsvd_revals,
fazcbc_record.default_reval_fully_rsvd_flag,
fazcbc_record.deferred_deprn_expense_acct,
fazcbc_record.deferred_deprn_reserve_acct,
fazcbc_record.deprn_allocation_code,
fazcbc_record.deprn_status,
fazcbc_record.fiscal_year_name,
fazcbc_record.initial_period_counter,
fazcbc_record.je_cip_adjustment_category,
fazcbc_record.je_cip_addition_category,
fazcbc_record.je_cip_reclass_category,
fazcbc_record.je_cip_retirement_category,
fazcbc_record.je_cip_reval_category,
fazcbc_record.je_cip_transfer_category,
fazcbc_record.je_reval_category,
fazcbc_record.last_mass_copy_period_counter,
fazcbc_record.last_period_counter,
fazcbc_record.last_purge_period_counter,
fazcbc_record.mass_copy_source_book,
fazcbc_record.mass_request_id,
fazcbc_record.nbv_amount_threshold,
fazcbc_record.nbv_fraction_threshold,
fazcbc_record.nbv_retired_gain_acct,
fazcbc_record.nbv_retired_loss_acct,
fazcbc_record.proceeds_of_sale_clearing_acct,
fazcbc_record.proceeds_of_sale_gain_acct,
fazcbc_record.proceeds_of_sale_loss_acct,
fazcbc_record.revalue_on_retirement_flag,
fazcbc_record.reval_deprn_reserve_flag,
fazcbc_record.reval_rsv_retired_gain_acct,
fazcbc_record.reval_rsv_retired_loss_acct,
fazcbc_record.deprn_adjustment_acct,
fazcbc_record.immediate_copy_flag,
fazcbc_record.je_deprn_adjustment_category,
fazcbc_record.depr_first_year_ret_flag,
fazcbc_record.flexbuilder_defaults_ccid,
fazcbc_record.retire_reval_reserve_flag,
fazcbc_record.use_current_nbv_for_deprn,
fazcbc_record.copy_additions_flag,
fazcbc_record.use_percent_salvage_value_flag,
fazcbc_record.reval_posting_flag,
fazcbc_record.global_attribute1,
fazcbc_record.global_attribute2,
fazcbc_record.global_attribute3,
fazcbc_record.global_attribute4,
fazcbc_record.global_attribute5,
fazcbc_record.global_attribute6,
fazcbc_record.global_attribute7,
fazcbc_record.global_attribute8,
fazcbc_record.global_attribute9,
fazcbc_record.global_attribute10,
fazcbc_record.global_attribute11,
fazcbc_record.global_attribute12,
fazcbc_record.global_attribute13,
fazcbc_record.global_attribute14,
fazcbc_record.global_attribute15,
fazcbc_record.global_attribute16,
fazcbc_record.global_attribute17,
fazcbc_record.global_attribute18,
fazcbc_record.global_attribute19,
fazcbc_record.global_attribute20,
fazcbc_record.global_attribute_category,
fazcbc_record.mc_source_flag,
fazcbc_record.reval_ytd_deprn_flag,
fazcbc_record.allow_cip_assets_flag,
fazcbc_record.org_id,
fazcbc_record.allow_group_deprn_flag,
fazcbc_record.allow_cip_dep_group_flag,
fazcbc_record.allow_interco_group_flag,
fazcbc_record.copy_group_addition_flag,
fazcbc_record.copy_group_assignment_flag,
fazcbc_record.allow_cip_member_flag,
fazcbc_record.allow_member_tracking_flag,
fazcbc_record.INTERCOMPANY_POSTING_FLAG,
fazcbc_record.allow_cost_sign_change_flag,
fazcbc_record.sorp_enabled_flag,
fazcbc_record.allow_impairment_flag,
fazcbc_record.copy_amort_adaj_exp_flag,
fazcbc_record.copy_group_change_flag,
fazcbc_record.prevent_prior_period_txns_flag,
fazcbc_record.allow_unallocated_lines_flag,
fazcbc_record.IMPORT_ALLOC_INV_LINES_AS_NEW
FROM fa_book_controls
WHERE book_type_code = X_book;
SELECT DEPRN_REQUEST_ID
, LAST_UPDATE_LOGIN
, DEPRN_STATUS
, LAST_PERIOD_COUNTER
, MASS_REQUEST_ID
, NBV_AMOUNT_THRESHOLD
, BOOK_TYPE_CODE
, SET_OF_BOOKS_ID
, LAST_DEPRN_RUN_DATE
, CURRENT_FISCAL_YEAR
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
INTO fazcbcs_record.DEPRN_REQUEST_ID
, fazcbcs_record.LAST_UPDATE_LOGIN
, fazcbcs_record.DEPRN_STATUS
, fazcbcs_record.LAST_PERIOD_COUNTER
, fazcbcs_record.MASS_REQUEST_ID
, fazcbcs_record.NBV_AMOUNT_THRESHOLD
, fazcbcs_record.BOOK_TYPE_CODE
, fazcbcs_record.SET_OF_BOOKS_ID
, fazcbcs_record.LAST_DEPRN_RUN_DATE
, fazcbcs_record.CURRENT_FISCAL_YEAR
, fazcbcs_record.LAST_UPDATE_DATE
, fazcbcs_record.LAST_UPDATED_BY
FROM fa_mc_book_controls
WHERE book_type_code = X_book
AND set_of_books_id = X_set_of_books_id;
fazcbc_table.delete(fazcbc_index);
fazcbc_table.delete(l_count + 1);
fazcbcs_table.delete(fazcbcs_index);
fazcbcs_table.delete(l_count + 1);
SELECT *
INTO fazcct_record
FROM fa_calendar_types
WHERE calendar_type = X_calendar;
SELECT decode (substr(h_deprn_alloc_code,1,1),
'E', 1.0 / h_pers_per_yr,
(cp.end_date + 1 - cp.start_date) /
(fy.end_date + 1 - fy.start_date)),
to_number (to_char (cp.start_date, 'J')),
to_number (to_char (cp.end_date, 'J'))
FROM fa_calendar_periods cp, fa_fiscal_year fy
WHERE fy.fiscal_year = X_fy
AND fy.fiscal_year_name = h_fiscal_year_name
AND cp.calendar_type = X_calendar
AND cp.start_date BETWEEN fy.start_date AND fy.end_date
AND cp.end_date BETWEEN fy.start_date AND fy.end_date
ORDER BY period_num;
* logic should delete all rows for the book/cal/fy combo
* though this may get tricky and we might need to bump max_size
-- do not let array get to big.. once it is at the
-- max begin clearing values from the array
if fazcff_table.count >= G_max_array_size then
-- get the first record
-- clear the first record
fazcff_record.book_type_code := 'NULL';
fazcff_table.delete(1);
fazcff_table.delete(l_count + 1);
SELECT cur_one.limit
INTO fazccl_record.ceiling
FROM fa_ceilings cur_one,
fa_ceilings this_one,
fa_ceilings next_one
WHERE cur_one.ceiling_name = X_target_ceiling_name
AND this_one.ceiling_name = X_target_ceiling_name
AND next_one.ceiling_name (+) = X_target_ceiling_name
AND to_date (X_target_jdate, 'J') BETWEEN
cur_one.start_date AND
nvl (cur_one.end_date, to_date (X_target_jdate, 'J'))
AND to_date (X_target_jdate, 'J') BETWEEN
this_one.start_date AND
nvl (this_one.end_date, to_date (X_target_jdate, 'J'))
AND to_date (X_target_jdate, 'J') BETWEEN
next_one.start_date (+) AND
nvl (next_one.end_date (+),
to_date (X_target_jdate, 'J'))
AND X_target_year >= nvl (this_one.year_of_life, 0)
AND nvl (cur_one.year_of_life, 0) < next_one.year_of_life (+)
GROUP BY cur_one.limit, cur_one.year_of_life,
cur_one.start_date, cur_one.end_date
HAVING nvl (cur_one.year_of_life, 0) =
nvl (max (this_one.year_of_life), 0);
SELECT bonus_rate,
deprn_factor,
alternate_deprn_factor
INTO fazcbr_record.bonus_rate,
fazcbr_record.deprn_factor,
fazcbr_record.alternate_deprn_factor
FROM fa_bonus_rates
WHERE bonus_rule = X_target_bonus_rule
AND X_target_year BETWEEN
start_year AND nvl (end_year, X_target_year);
SELECT to_number (to_char (cp.start_date, 'J')),
cp.period_num,
fy.fiscal_year
INTO fazccp_record.start_jdate,
fazccp_record.period_num,
fazccp_record.fiscal_year
FROM fa_calendar_periods cp,
fa_fiscal_year fy
WHERE fy.fiscal_year_name = X_target_fy_name
AND cp.calendar_type = X_target_calendar
AND x_target_date between fy.start_date and fy.end_date
AND cp.start_date between fy.start_date and fy.end_date
AND cp.end_date between fy.start_date and fy.end_date
AND x_target_date between cp.start_date and cp.end_date;
SELECT *
INTO fazccb_record
FROM fa_category_books
WHERE book_type_code = X_book
AND category_id = X_cat_id;
SELECT *
INTO fazccmt_record
FROM fa_methods
WHERE method_code = X_method
AND life_in_months = X_life;
SELECT *
INTO fazccmt_record
FROM fa_methods
WHERE method_code = X_method
AND (life_in_months is null OR
life_in_months = 0);
select *
into fazcfor_record
from fa_formulas
where method_id = fazccmt_record.method_id;
select deprn_basis_rule_id,
rule_name,
user_rule_name,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
rate_source,
deprn_basis,
enabled_flag,
program_name,
description
into fazcdbr_record.deprn_basis_rule_id,
fazcdbr_record.rule_name,
fazcdbr_record.user_rule_name,
fazcdbr_record.last_update_date,
fazcdbr_record.last_updated_by,
fazcdbr_record.created_by,
fazcdbr_record.creation_date,
fazcdbr_record.last_update_login,
fazcdbr_record.rate_source,
fazcdbr_record.deprn_basis,
fazcdbr_record.enabled_flag,
fazcdbr_record.program_name,
fazcdbr_record.description
from fa_deprn_basis_rules
where deprn_basis_rule_id = fazccmt_record.deprn_basis_rule_id;
fazcdrd_record.period_update_flag := null;
select *
into fazcdrd_record
from fa_deprn_rule_details
where deprn_basis_rule_id = fazccmt_record.deprn_basis_rule_id
and rate_source_rule = fazccmt_record.rate_source_rule
and deprn_basis_rule = fazccmt_record.deprn_basis_rule;
SELECT set_of_books_id,
mrc_sob_type_code
INTO fazcsob_record.set_of_books_id,
fazcsob_record.mrc_sob_type_code
FROM gl_sets_of_books
WHERE set_of_books_id = X_set_of_books_id;
fazccbd_table.delete(1);
fazccbd_table.delete(l_count + 1);
SELECT *
INTO fazccbd_record
FROM fa_category_book_defaults
WHERE book_type_code = X_book
AND category_id = X_cat_id
AND to_date (X_jdpis, 'J') BETWEEN
start_dpis AND
nvl (end_dpis, to_date (X_jdpis, 'J'));
fazcat_table.delete(1);
fazcat_table.delete(l_count + 1);
SELECT *
INTO fazcat_record
FROM fa_categories
WHERE category_id = X_cat_id;
SELECT *
INTO fazsys_record
FROM fa_system_controls;
select distribution_source_book,
book_type_code,
nvl(allow_cip_assets_flag, 'NO') allow_cip_assets_flag,
nvl(immediate_copy_flag, 'NO') immediate_copy_flag,
nvl(copy_group_addition_flag, 'N') copy_group_addition_flag
from fa_book_controls
where book_class = 'TAX'
and distribution_source_book = x_corp_book
and date_ineffective is null;
fazctbk_tax_tbl.delete;
SELECT set_of_books_id
FROM fa_mc_book_controls
WHERE book_type_code = x_book_type_code
AND enabled_flag = 'Y'
AND mrc_converted_flag = 'Y';
fazcrsob_sob_tbl.delete;
fazccvt_table.delete(1);
fazccvt_table.delete(l_count + 1);
SELECT *
INTO fazccvt_record
FROM fa_convention_types
WHERE prorate_convention_code = X_prorate_convention_code
AND fiscal_year_name = X_fiscal_year_name;
fazcfy_table.delete(1);
fazcfy_table.delete(l_count + 1);
SELECT *
INTO fazcfy_record
FROM fa_fiscal_year
WHERE fiscal_year_name = X_fiscal_year_name
AND fiscal_year = X_fiscal_year;
fazcdp_table.delete(1);
fazcdp_table.delete(l_count + 1);
SELECT book_type_code,
period_name,
period_counter,
fiscal_year,
period_num,
period_open_date,
period_close_date,
calendar_period_open_date,
calendar_period_close_date,
deprn_run
INTO fazcdp_record
FROM fa_deprn_periods
WHERE book_type_code = X_book_type_code
AND period_counter = X_period_counter;
SELECT book_type_code,
period_name,
period_counter,
fiscal_year,
period_num,
period_open_date,
period_close_date,
calendar_period_open_date,
calendar_period_close_date,
deprn_run
INTO fazcdp_record
FROM fa_deprn_periods
WHERE book_type_code = X_book_type_code
AND x_effective_date between
period_open_date and nvl(period_close_date, sysdate);
SELECT book_type_code,
period_name,
period_counter,
fiscal_year,
period_num,
period_open_date,
period_close_date,
calendar_period_open_date,
calendar_period_close_date,
deprn_run
INTO fazcdp_record
FROM fa_deprn_periods
WHERE book_type_code = X_book_type_code
AND period_close_date is null;
fazcdp_table.delete(fazcdp_index);
fazcdp_table.delete(l_count + 1);
select super_group_id,
book_type_code,
start_period_counter,
end_period_counter,
deprn_method_code,
basic_rate,
adjusted_rate,
percent_salvage_value
from fa_super_group_rules
where super_group_id = X_super_group_id
and book_type_code = X_book_type_code
and X_period_counter between start_period_counter
and nvl(end_period_counter, X_period_counter)
and date_ineffective is null;
select super_group_id,
book_type_code,
0,
start_period_counter -1,
deprn_method_code,
0,
0,
1
from fa_super_group_rules
where super_group_id = X_super_group_id
and book_type_code = X_book_type_code
and date_ineffective is null
order by start_period_counter;
fazcsgr_table.delete(1);
fazcsgr_table.delete(l_count + 1);