[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT sc.fa_application_id,
sc.category_flex_structure,
sob.name,
sob.currency_code,
bc.set_of_books_id,
bc.deprn_calendar
INTO l_application_id,
l_category_flex_structure,
l_info_rec.organization_name,
l_info_rec.functional_currency_code,
l_info_rec.set_of_books_id,
l_info_rec.deprn_calendar
FROM fa_system_controls sc,
fa_book_controls bc,
gl_sets_of_books sob,
fnd_currencies cur
WHERE bc.book_type_code = p_book_type_code
AND sob.set_of_books_id = bc.set_of_books_id
AND sob.currency_code = cur.currency_code;
l_info_rec.major_cat_select_stmt,
l_param_where_stmt);
l_info_rec.minor_cat_select_stmt,
l_sql_stmt);
l_info_rec.other_cat_select_stmt,
l_sql_stmt);
SELECT MIN(period_counter),
MAX(period_counter)
INTO l_info_rec.min_period_counter,
l_info_rec.max_period_counter
FROM fa_deprn_periods
WHERE book_type_code = p_book_type_code
AND fiscal_year = l_info_rec.fiscal_year
AND NVL(deprn_run, 'N') = 'Y';
'SELECT
ad.asset_number,
ad.description,
ad.asset_type, '
|| l_info_rec.major_cat_select_stmt || ','
|| l_info_rec.minor_cat_select_stmt || ','
|| l_info_rec.other_cat_select_stmt || ',
bk.date_placed_in_service,
bk.deprn_method_code,
br.rule_name,
bk.tracking_method,
bk.adjusted_rate,
NULL,
NVL(bk.cost, 0) + NVL(bk.cip_cost, 0),
NVL(bk.salvage_value, 0),
NVL(bk.adjusted_recoverable_cost, 0),
NVL(prev.cost, 0) + NVL(prev.cip_cost, 0) - NVL(prev.deprn_reserve, 0),
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
0,
NVL(bk.terminal_gain_loss_amount, 0),
NULL, NULL,
NVL(ds.adjusted_cost, 0),
NULL, NULL,
NVL(ds.ytd_deprn, 0),
NVL(ds.deprn_reserve, 0),
NULL, NULL,
ad.asset_id,
NULL,
DECODE(bk.life_in_months, NULL, NULL,
TO_CHAR(FLOOR(bk.life_in_months / 12)) || ''.'' ||
TO_CHAR(MOD(bk.life_in_months, 12))),
met.deprn_basis_rule,
met.exclude_salvage_value_flag,
NVL(bk.reduction_rate, 0),
bk.depreciation_option,
bk.recognize_gain_loss,
bk.exclude_proceeds_from_basis,
NULL, NULL,
ds.period_counter ';
SELECT NVL(SUM(ret.proceeds_of_sale), 0),
NVL(SUM(ret.cost_of_removal), 0),
NVL(SUM(ret.nbv_retired), 0),
NVL(SUM(ret.cost_retired), 0),
NVL(SUM(ret.reserve_retired), 0),
NVL(SUM(ret.recapture_amount), 0)
INTO l_group_rec.proceeds_of_sale,
l_group_rec.cost_of_removal,
l_group_rec.net_proceeds,
l_group_rec.cost_retired,
l_group_rec.reserve_retired,
l_group_rec.recapture_amount
FROM fa_retirements ret,
fa_book_controls bc,
fa_fiscal_year fy,
fa_transaction_headers thg,
fa_transaction_headers thm
WHERE bc.book_type_code = p_book_type_code
AND fy.fiscal_year = l_info_rec.fiscal_year
AND fy.fiscal_year_name = bc.fiscal_year_name
AND thm.book_type_code = bc.book_type_code
AND thm.transaction_date_entered
BETWEEN fy.start_date and fy.end_date
AND thg.book_type_code = bc.book_type_code
AND thg.asset_id = l_group_rec.asset_id
AND thg.member_transaction_header_id = thm.transaction_header_id
AND ret.transaction_header_id_in = thm.transaction_header_id
AND ret.status <> 'DELETED';
SELECT NVL(SUM(DECODE(adj.debit_credit_flag,
'DR', adj.adjustment_amount,
'CR', -adj.adjustment_amount, 0)), 0),
NVL(SUM(DECODE(GREATEST(thg.transaction_date_entered,
fy.mid_year_date),
thg.transaction_date_entered,
DECODE(adj.debit_credit_flag,
'DR', adj.adjustment_amount,
'CR', -adj.adjustment_amount, 0),
0)), 0)
INTO l_group_adjustment_amount,
l_second_half_grp_adjustment
FROM fa_adjustments adj,
fa_book_controls bc,
fa_fiscal_year fy,
fa_transaction_headers thg
WHERE thg.asset_id = l_group_rec.asset_id
AND thg.book_type_code = p_book_type_code
AND thg.member_transaction_header_id IS NULL
AND thg.transaction_header_id = adj.transaction_header_id
AND adj.period_counter_created
BETWEEN l_info_rec.min_period_counter
and l_info_rec.max_period_counter
AND adj.adjustment_type = 'COST'
AND fy.fiscal_year = l_info_rec.fiscal_year
AND fy.fiscal_year_name = bc.fiscal_year_name
AND bc.book_type_code = p_book_type_code;
SELECT NVL(SUM(bkm.cost), 0) - NVL(SUM(adj.adjustment_amount), 0)
INTO l_group_reclass_in
FROM fa_adjustments adj,
fa_transaction_headers thg,
fa_books bkm,
(SELECT bk_pre.asset_id,
bk_pre.group_asset_id
FROM fa_books bk_pre,
fa_deprn_summary ds_pre,
fa_deprn_periods dp_pre
WHERE bk_pre.book_type_code = p_book_type_code
AND dp_pre.book_type_code = bk_pre.book_type_code
AND dp_pre.period_counter + 1 = l_info_rec.min_period_counter
AND dp_pre.period_close_date BETWEEN bk_pre.date_effective
AND NVL(bk_pre.date_ineffective, dp_pre.period_close_date)
AND ds_pre.book_type_code = bk_pre.book_type_code
AND ds_pre.asset_id = bk_pre.asset_id
AND ds_pre.period_counter = (
SELECT MAX(ds3.period_counter)
FROM fa_deprn_summary ds3
WHERE ds_pre.book_type_code = ds3.book_type_code
AND ds_pre.asset_id = ds3.asset_id
AND ds3.period_counter < l_info_rec.min_period_counter
)
) prev
WHERE adj.asset_id = l_group_rec.asset_id
AND adj.book_type_code = p_book_type_code
AND thg.transaction_header_id = adj.transaction_header_id
AND adj.period_counter_created
BETWEEN l_info_rec.min_period_counter
and l_info_rec.max_period_counter
AND adj.source_type_code = 'ADJUSTMENT'
AND adj.adjustment_type = 'RESERVE'
AND thg.member_transaction_header_id = bkm.transaction_header_id_in
AND NVL(bkm.group_asset_id, -1) = l_group_rec.asset_id
AND prev.asset_id = bkm.asset_id
AND NVL(prev.group_asset_id, -1) <> l_group_rec.asset_id;
SELECT NVL(SUM(bkm.cost), 0) - NVL(SUM(adj.adjustment_amount), 0)
INTO l_group_reclass_out
FROM fa_adjustments adj,
fa_transaction_headers thg,
fa_books bkm
WHERE adj.asset_id = l_group_rec.asset_id
AND adj.book_type_code = p_book_type_code
AND thg.transaction_header_id = adj.transaction_header_id
AND adj.period_counter_created
BETWEEN l_info_rec.min_period_counter
and l_info_rec.max_period_counter
AND adj.source_type_code = 'ADJUSTMENT'
AND adj.adjustment_type = 'RESERVE'
AND thg.member_transaction_header_id = bkm.transaction_header_id_in
AND NVL(bkm.group_asset_id, -1) <> l_group_rec.asset_id;
SELECT COUNT(*)
INTO l_non_cip_num
FROM fa_books bk, fa_additions ad
WHERE bk.book_type_code = p_book_type_code
AND bk.group_asset_id = l_group_rec.asset_id
AND ad.asset_type <> 'CIP'
AND bk.asset_id = ad.asset_id;
insert_data(l_info_rec, l_group_rec, l_member_rec);
x_select_stmt OUT NOCOPY VARCHAR2,
x_where_stmt OUT NOCOPY VARCHAR2)
IS
BEGIN
IF g_print_debug THEN
fa_rx_util_pkg.debug('get_category_sql: '
|| 'p_application_id: ' || p_application_id);
x_select_stmt := 'null';
x_select_stmt :=
fa_rx_flex_pkg.flex_sql(p_application_id, 'CAT#',
p_category_flex_structure, 'cat',
'SELECT', p_qualifier);
x_select_stmt := 'null';
( SELECT bk_pre.asset_id,
bk_pre.group_asset_id,
bk_pre.cost,
bk_pre.cip_cost,
ds_pre.deprn_reserve
FROM fa_books bk_pre,
fa_deprn_summary ds_pre,
fa_deprn_periods dp_pre,
fa_additions ad_pre
WHERE bk_pre.book_type_code = ''' || p_info_rec.book_type_code || '''
AND dp_pre.book_type_code = bk_pre.book_type_code
AND dp_pre.period_counter + 1 = ' || p_info_rec.min_period_counter || '
AND dp_pre.period_close_date BETWEEN bk_pre.date_effective
AND NVL(bk_pre.date_ineffective, dp_pre.period_close_date)
AND ds_pre.book_type_code = bk_pre.book_type_code
AND ds_pre.asset_id = bk_pre.asset_id
AND ds_pre.period_counter = (
SELECT MAX(ds3.period_counter)
FROM fa_deprn_summary ds3
WHERE ds_pre.book_type_code = ds3.book_type_code
AND ds_pre.asset_id = ds3.asset_id
AND ds3.period_counter < ' || p_info_rec.min_period_counter || '
)
AND ad_pre.asset_id = bk_pre.asset_id ';
SELECT MAX(ds2.period_counter)
FROM fa_deprn_summary ds2
WHERE ds2.book_type_code = ds.book_type_code
AND ds2.asset_id = ds.asset_id
AND ds2.period_counter <= ' || p_info_rec.max_period_counter || ' )
AND bk.asset_id = prev.asset_id (+)';
SELECT NVL(SUM(DECODE(
GREATEST(thm.transaction_date_entered, fy.mid_year_date),
thm.transaction_date_entered,
DECODE(adj.source_type_code || ''-'' || adj.adjustment_type
|| ''-'' || adj.debit_credit_flag,
''ADDITION-COST-DR'', ';
x_sql_stmt := '(SELECT adj.asset_id, ';
x_sql_stmt := '(SELECT ret.asset_id,
NVL(SUM(ret.proceeds_of_sale), 0) proceeds_of_sale,
NVL(SUM(ret.cost_of_removal), 0) cost_of_removal,
NVL(SUM(ret.cost_retired), 0) cost_retired,
NVL(SUM(ret.reserve_retired), 0) reserve_retired
FROM fa_retirements ret,
fa_book_controls bc,
fa_fiscal_year fy,
fa_transaction_headers thm,
fa_books bkm
WHERE bkm.group_asset_id = ' || p_group_asset_id || '
AND bc.book_type_code = ''' || p_info_rec.book_type_code || '''
AND fy.fiscal_year = ' || p_info_rec.fiscal_year || '
AND fy.fiscal_year_name = bc.fiscal_year_name
AND thm.book_type_code = bc.book_type_code
AND bkm.book_type_code = bc.book_type_code
AND thm.transaction_date_entered
BETWEEN fy.start_date and fy.end_date
AND ret.asset_id = thm.asset_id
AND bkm.asset_id = thm.asset_id
AND bkm.transaction_header_id_in = thm.transaction_header_id
AND ret.transaction_header_id_in = thm.transaction_header_id
AND ret.status <> ''DELETED''
GROUP BY ret.asset_id) ret ';
PROCEDURE insert_data (
p_info_rec IN info_rec_type,
p_group_rec IN group_rec_type,
p_member_rec IN group_rec_type)
IS
BEGIN
INSERT INTO fa_group_rep_itf (
request_id, created_by, creation_date,
last_updated_by, last_update_date, last_update_login,
organization_name, functional_currency_code,
set_of_books_id, book_type_code, deprn_calendar, fiscal_year,
grp_asset_number, grp_description, grp_asset_type,
grp_major_category, grp_minor_category, grp_other_category,
grp_date_placed_in_service, grp_deprn_method_code,
grp_rule_name, grp_tracking_method,
grp_adjusted_rate, grp_life_year_month,
grp_cost, grp_salvage_value,
grp_adjusted_recoverable_cost, grp_beginning_nbv,
grp_first_half_addition, grp_second_half_addition,
grp_addition_amount, grp_adjustment_amount,
grp_net_proceeds, grp_proceeds_of_sale, grp_cost_of_removal,
grp_cost_retired, grp_reserve_retired,
grp_recapture_amount, grp_terminal_gain_loss_amount,
grp_nbv_before_deprn, grp_deprn_basis_adjustment,
grp_reduced_nbv,
grp_regular_deprn_amount, grp_reduced_deprn_amount,
grp_annual_deprn_amount, grp_deprn_reserve, grp_ending_nbv,
mem_asset_number, mem_description, mem_asset_type,
mem_major_category, mem_minor_category, mem_other_category,
mem_date_placed_in_service, mem_deprn_method_code,
mem_rule_name, mem_adjusted_rate, mem_life_year_month,
mem_cost, mem_salvage_value,
mem_adjusted_recoverable_cost, mem_beginning_nbv,
mem_first_half_addition, mem_second_half_addition,
mem_addition_amount, mem_adjustment_amount,
mem_net_proceeds, mem_proceeds_of_sale, mem_cost_of_removal,
mem_cost_retired, mem_reserve_retired,
mem_nbv_before_deprn, mem_deprn_basis_adjustment,
mem_reduced_nbv,
mem_annual_deprn_amount, mem_deprn_reserve, mem_ending_nbv,
mem_status
) VALUES (
p_info_rec.request_id, p_info_rec.user_id, sysdate,
p_info_rec.user_id, sysdate, p_info_rec.user_id,
p_info_rec.organization_name, p_info_rec.functional_currency_code,
p_info_rec.set_of_books_id, p_info_rec.book_type_code,
p_info_rec.deprn_calendar, p_info_rec.fiscal_year,
p_group_rec.asset_number,
p_group_rec.description,
p_group_rec.asset_type,
p_group_rec.major_category,
p_group_rec.minor_category,
p_group_rec.other_category,
p_group_rec.date_placed_in_service,
p_group_rec.deprn_method_code,
p_group_rec.rule_name,
p_group_rec.tracking_method,
p_group_rec.adjusted_rate,
p_group_rec.life_year_month,
p_group_rec.cost,
p_group_rec.salvage_value,
p_group_rec.adjusted_recoverable_cost,
p_group_rec.beginning_nbv,
p_group_rec.first_half_addition,
p_group_rec.second_half_addition,
p_group_rec.addition_amount,
p_group_rec.adjustment_amount,
p_group_rec.net_proceeds,
p_group_rec.proceeds_of_sale,
p_group_rec.cost_of_removal,
p_group_rec.cost_retired,
p_group_rec.reserve_retired,
p_group_rec.recapture_amount,
p_group_rec.terminal_gain_loss_amount,
p_group_rec.nbv_before_deprn,
p_group_rec.deprn_basis_adjustment,
p_group_rec.reduced_nbv,
p_group_rec.regular_deprn_amount,
p_group_rec.reduced_deprn_amount,
p_group_rec.annual_deprn_amount,
p_group_rec.deprn_reserve,
p_group_rec.ending_nbv,
p_member_rec.asset_number,
p_member_rec.description,
p_member_rec.asset_type,
p_member_rec.major_category,
p_member_rec.minor_category,
p_member_rec.other_category,
p_member_rec.date_placed_in_service,
p_member_rec.deprn_method_code,
p_member_rec.rule_name,
p_member_rec.adjusted_rate,
p_member_rec.life_year_month,
p_member_rec.cost,
p_member_rec.salvage_value,
p_member_rec.adjusted_recoverable_cost,
p_member_rec.beginning_nbv,
p_member_rec.first_half_addition,
p_member_rec.second_half_addition,
p_member_rec.addition_amount,
p_member_rec.adjustment_amount,
p_member_rec.net_proceeds,
p_member_rec.proceeds_of_sale,
p_member_rec.cost_of_removal,
p_member_rec.cost_retired,
p_member_rec.reserve_retired,
p_member_rec.nbv_before_deprn,
p_member_rec.deprn_basis_adjustment,
p_member_rec.reduced_nbv,
p_member_rec.annual_deprn_amount,
p_member_rec.deprn_reserve,
p_member_rec.ending_nbv,
p_member_rec.status);
fa_rx_util_pkg.debug('insert_data: '
|| 'farx_ga.insert_data(EXCEPTION)-');
END insert_data;
'SELECT
ad.asset_number,
ad.description,
ad.asset_type, '
|| p_info_rec.major_cat_select_stmt || ','
|| p_info_rec.minor_cat_select_stmt || ','
|| p_info_rec.other_cat_select_stmt || ',
bk.date_placed_in_service,
bk.deprn_method_code,
br.rule_name,
NULL,
bk.adjusted_rate,
NULL,
NVL(bk.cost, 0) + NVL(bk.cip_cost, 0),
NVL(bk.salvage_value, 0),
NVL(bk.adjusted_recoverable_cost, 0),
NVL(prev.cost, 0) + NVL(prev.cip_cost, 0) - NVL(prev.deprn_reserve, 0),
NULL,
NVL(amt.second_half_addition, 0),
NVL(amt.addition_amount, 0),
NVL(amt.adjustment_amount, 0),
NULL,
NVL(ret.proceeds_of_sale, 0),
NVL(ret.cost_of_removal, 0),
NVL(ret.cost_retired, 0),
NVL(ret.reserve_retired, 0),
NULL, NULL, NULL, NULL,
NVL(ds.adjusted_cost, 0),
NULL, NULL,
NVL(ds.ytd_deprn, 0),
NVL(ds.deprn_reserve, 0),
NULL, NULL,
ad.asset_id,
prev.group_asset_id,
DECODE(bk.life_in_months, NULL, NULL,
TO_CHAR(FLOOR(bk.life_in_months / 12)) || ''.'' ||
TO_CHAR(MOD(bk.life_in_months, 12))),
met.deprn_basis_rule,
met.exclude_salvage_value_flag,
NULL, NULL, NULL, NULL,
bk.period_counter_fully_retired,
bk.period_counter_fully_reserved,
ds.period_counter ';
SELECT NVL(bkm.cost, 0) - NVL(adj.adjustment_amount, 0)
INTO l_group_reclass_out
FROM fa_adjustments adj,
fa_transaction_headers thg,
fa_books bkm
WHERE adj.asset_id = p_group_rec.asset_id
AND adj.book_type_code = p_info_rec.book_type_code
AND thg.transaction_header_id = adj.transaction_header_id
AND adj.period_counter_created
BETWEEN p_info_rec.min_period_counter
and p_info_rec.max_period_counter
AND adj.source_type_code = 'ADJUSTMENT'
AND adj.adjustment_type = 'RESERVE'
AND thg.member_transaction_header_id = bkm.transaction_header_id_in
AND NVL(bkm.group_asset_id, -1) <> p_group_rec.asset_id
AND bkm.asset_id = l_member_rec.asset_id;
SELECT NVL(bkm.cost, 0) - NVL(adj.adjustment_amount, 0)
INTO l_group_reclass_in
FROM fa_adjustments adj,
fa_transaction_headers thg,
fa_books bkm
WHERE adj.asset_id = p_group_rec.asset_id
AND adj.book_type_code = p_info_rec.book_type_code
AND thg.transaction_header_id = adj.transaction_header_id
AND adj.period_counter_created
BETWEEN p_info_rec.min_period_counter
and p_info_rec.max_period_counter
AND adj.source_type_code = 'ADJUSTMENT'
AND adj.adjustment_type = 'RESERVE'
AND thg.member_transaction_header_id = bkm.transaction_header_id_in
AND NVL(bkm.group_asset_id, -1) = p_group_rec.asset_id;
insert_data(p_info_rec, p_group_rec, l_member_rec);
IF l_message = 'FA_SHARED_INSERT_FAIL' THEN
fnd_message.set_token('TABLE', 'FA_GROUP_REP_ITF');