[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select a.default_rule_set_id
, a.rule_set_level
from fa_asset_hierarchy_purpose a
, fa_asset_hierarchy b
where a.asset_hierarchy_purpose_id = b.asset_hierarchy_purpose_id
and b.asset_hierarchy_id = x_parent_node_id
and a.book_type_code = x_book_type_code ;
select cua_rule_set_id
from fa_category_book_defaults
where category_id = x_cat_id_in
and book_type_code = x_book_type_code
and p_rule_set_level = 'ASSET_CATEGORY'
UNION
select a.hierarchy_rule_set_id
from fa_asset_hierarchy a
, fa_asset_hierarchy_purpose b
where a.asset_hierarchy_purpose_id = b.asset_hierarchy_purpose_id
and a.asset_hierarchy_id = x_parent_node_id
and b.book_type_code = x_book_type_code
and p_rule_set_level = 'TOP_NODE'
UNION
select a.hierarchy_rule_set_id
from ( select hierarchy_rule_set_id, asset_hierarchy_purpose_id
from fa_asset_hierarchy
where parent_hierarchy_id IS NULL
start with asset_hierarchy_id = x_parent_node_id
connect by prior asset_hierarchy_id = parent_hierarchy_id ) a
, fa_asset_hierarchy_purpose b
where a.asset_hierarchy_purpose_id = b.asset_hierarchy_purpose_id
and b.book_type_code = x_book_type_code
and p_rule_set_level = 'LOWEST_NODE';
select life_end_date
from fa_asset_hierarchy_values
where book_type_code = x_book_type_code
and asset_hierarchy_id = c_asset_hierarchy_id ;
select a.asset_hierarchy_id src_id
, 'NODE' src_type
, b.level_number hierarchy_level
, a.book_type_code book_type_code
, a.life_end_date life_end_date
, 0 life_in_months
, bc.book_class book_class
from fa_asset_hierarchy_values a,
fa_asset_hierarchy b,
fa_book_controls bc
where a.asset_hierarchy_id = b.asset_hierarchy_id
and bc.book_type_code = a.book_type_code
and a.book_type_code in (x_book_type_code,g_corporate_book)
and b.asset_hierarchy_id in (select x_top_node_id
from dual
where nvl(x_rule_det_rec.include_level, 'ALL') = 'TOP'
union
select x_parent_node_id
from dual
where nvl(x_rule_det_rec.include_level, 'ALL') = 'LOWEST'
union
select d.asset_hierarchy_id
from fa_asset_hierarchy d
where nvl(x_rule_det_rec.include_level, 'ALL') = 'ALL'
start with d.asset_hierarchy_id = x_parent_node_id
connect by d.asset_hierarchy_id = prior d.parent_hierarchy_id
)
and not exists (select 'X'
from fa_exclude_hierarchy_levels c
where c.attribute_name = 'LIFE_END_DATE'
and c.book_type_code= x_book_type_code
and c.hierarchy_rule_set_id = x_rule_det_rec.hierarchy_rule_set_id
and b.level_number = c.level_number
)
and nvl(x_rule_det_rec.include_hierarchy_flag,'N') = 'Y'
UNION ALL
--
select asset_id src_id
, 'ASSET' src_type
, 0 hierarchy_level
, book_type_code
, add_months(prorate_date, life_in_months) life_end_date
, life_in_months
, 'CORPORATE' book_class
from fa_books
where asset_id = x_asset_id
and date_ineffective IS NULL
and nvl(x_rule_det_rec.include_asset_end_date_flag, 'N') = 'Y'
and book_type_code = x_book_type_code
UNION ALL
--
select lease_id src_id
, 'LEASE' src_type
, 0 hierarchy_level
, null book_type_code
, max(flp.end_date) life_end_date
, 0 life_in_months
, 'CORPORATE' book_class
from fa_lease_payments flp,fa_leases fl
where fl.lease_id = decode(x_rule_det_rec.target_flag, 'Y', x_node_lease_id, x_asset_lease_id )
and nvl(x_rule_det_rec.include_lease_end_date_flag, 'N') = 'Y'
and fl.payment_schedule_id = flp.payment_schedule_id
group by lease_id
, 'LEASE'
, 0
, null
, 0
, 'CORPORATE'
UNION ALL
--
select category_id src_id
, 'CATEGORY-LIFE' src_type
, 0 hierarchy_level
, book_type_code
, decode( x_rule_det_rec.target_flag, 'Y',
add_months( nvl(v_prorated_depr_date, x_prorate_date), life_in_months),
add_months(x_prorate_date, life_in_months) ) life_end_date
, life_in_months
, 'CORPORATE' book_class
from fa_category_book_defaults
where category_id = decode(x_rule_det_rec.target_flag, 'Y', x_node_category_id, x_asset_cat_id )
and (trunc(sysdate) between start_dpis and nvl(end_dpis, trunc(sysdate) ) )
and nvl(x_rule_det_rec.include_asset_catg_life_flag, 'N') = 'Y'
and book_type_code = x_book_type_code
UNION ALL
--
select category_id src_id
, 'CATEGORY-LED' src_type
, 0 hierarchy_level
, book_type_code
, cua_life_end_date life_end_date
, life_in_months
, 'CORPORATE' book_class
from fa_category_book_defaults
where category_id = decode(x_rule_det_rec.target_flag, 'Y', x_node_category_id, x_asset_cat_id )
and (trunc(sysdate) between start_dpis and nvl(end_dpis, trunc(sysdate) ) )
and nvl(x_rule_det_rec.include_catg_end_date_flag, 'N') = 'Y'
and book_type_code = x_book_type_code
order by 7 desc;
select depreciation_start_date
from fa_asset_hierarchy
where asset_hierarchy_id = x_parent_node_id;
fa_cua_mass_update1_pkg.calc_life ( x_book_type_code
, x_prorate_date
, to_date( v_led, 'J')
, x_deprn_method_code
, x_life_out
, x_err_code
, x_err_stage
, x_err_stack );
SELECT *
FROM FA_HIERARCHY_RULE_DETAILS a
WHERE hierarchy_rule_set_id = p_rule_set_id
AND attribute_name = p_attribute_name
AND book_type_code = p_book_type_code;
select asset_hierarchy_id
from fa_asset_hierarchy
where parent_hierarchy_id IS NULL
start with asset_hierarchy_id = x_parent_node_id
connect by asset_hierarchy_id = prior parent_hierarchy_id;
select 1
from dual
where not exists ( select asset_hierarchy_id
from fa_asset_hierarchy
where ( parent_hierarchy_id = x_parent_node_id
and asset_id IS NULL)
OR ( asset_hierarchy_id = x_parent_node_id
and asset_id IS NOT NULL ) );
select '1' dummy, asset_hierarchy_id
, asset_category_id
, lease_id
, dist_set_id
, asset_key_ccid
, serial_number
, life_end_date
from fa_asset_hierarchy_values
where asset_hierarchy_id = p_node_id
and book_type_code = x_book_type_code
UNION
select '2' dummy, asset_hierarchy_id
, asset_category_id
, lease_id
, dist_set_id
, asset_key_ccid
, serial_number
, life_end_date
from fa_asset_hierarchy_values
where asset_hierarchy_id = p_node_id
and book_type_code = g_corporate_book
order by 1;
select '1' dummy
, nvl(x_prorate_date,prorate_date) -- Use Asset Pro Rate date if nothing passed
, deprn_method_code
, prorate_convention_code
, life_in_months
from fa_books
where asset_id = x_asset_id
and book_type_code = x_book_type_code
and date_ineffective IS NULL;
select '1' dummy,
deprn_method
, prorate_convention_code
, life_in_months
from fa_category_book_defaults
where category_id = p_cat_id
and book_type_code = x_book_type_code
and ( trunc(sysdate) between start_dpis and nvl(end_dpis, trunc(sysdate)) )
UNION
select '2' dummy
, deprn_method
, prorate_convention_code
, life_in_months
from fa_category_book_defaults
where category_id = p_cat_id
and book_type_code = g_corporate_book
and ( trunc(sysdate) between start_dpis and nvl(end_dpis, trunc(sysdate)) )
ORDER BY 1;
select asset_category_id
from fa_additions
where asset_id = x_asset_id;
select lease_id
from fa_additions
where asset_id = x_asset_id;
select book_class
from fa_book_controls
where book_type_code = x_book_type_code;
select distribution_source_book
from fa_book_controls
where book_type_code = x_book_type_code;
fa_cua_mass_update1_pkg.calc_life ( x_book_type_code
, v_prorate_date
, v_top_attr_val_rec.life_end_date
, v_deprn_method_code
, x_life_in_months_out
, x_err_code
, x_err_stage
, x_err_stack );
fa_cua_mass_update1_pkg.calc_life ( x_book_type_code
, v_prorate_date
, v_lowest_attr_val_rec.life_end_date
, v_deprn_method_code
, x_life_in_months_out
, x_err_code
, x_err_stage
, x_err_stack );
SELECT *
FROM fa_mass_update_batch_headers
where batch_number = x_batch_number
and status_code IN ( 'N', 'IP')
for UPDATE NOWAIT;
update fa_mass_update_batch_headers
set status_code = 'P',
rejection_reason_code = null
where batch_id = hdr_rec.batch_id;
update fa_mass_update_batch_headers
set status_code = 'CP',
rejection_reason_code = null
where batch_id = hdr_rec.batch_id;
update fa_mass_update_batch_headers
set status_code = 'R',
rejection_reason_code = v_message_name
where batch_number = x_batch_number ;
update fa_mass_update_batch_headers
set status_code = 'R',
rejection_reason_code = v_message_name
where batch_number = x_batch_number ;
v_last_update_login NUMBER;
v_last_updated_by NUMBER;
v_insert_flag VARCHAR2(1):= 'N';
select book_class
from fa_book_controls
where book_type_code = x_book_type_code;
v_last_updated_by:= v_created_by;
v_last_update_login:= nvl(TO_NUMBER(fnd_profile.value('LOGIN_ID')),-1);
x_err_stage:= 'Calling insert_mass_update_batch_headers';
FA_CUA_DERIVE_ASSET_ATTR_PKG.insert_mass_update_batch_hdrs (
x_event_code
, x_book_type_code
, 'P'
, x_src_entity_name
, x_src_entity_value
, v_src_attribute_name
, v_src_attr_value_from
, v_src_attr_value_to
, NULL -- x_description
, x_amortize_expense_flg
, x_amortization_date
, v_rejection_reason_code
, v_conc_request_id
, v_created_by
, v_sysdate -- creation_date
, v_last_updated_by
, v_sysdate -- last_update_date
, v_last_update_login
, x_batch_num
, x_batch_id
, x_transaction_name
, x_attribute_category
, x_attribute1
, x_attribute2
, x_attribute3
, x_attribute4
, x_attribute5
, x_attribute6
, x_attribute7
, x_attribute8
, x_attribute9
, x_attribute10
, x_attribute11
, x_attribute12
, x_attribute13
, x_attribute14
, x_attribute15
, x_err_code
, x_err_stage
, x_err_stack );
x_err_stage:= 'Calling select_assets';
FA_CUA_DERIVE_ASSET_ATTR_PKG.select_assets( x_event_code
, x_book_type_code
, v_book_class
, x_src_entity_value
, v_parent_id -- new parent id in case of HR_MASS_TRANSFER
, v_asset_attr_tab
, x_err_code
, x_err_stage
, x_err_stack );
update fa_mass_update_batch_headers
set status_code = 'R',
rejection_reason_code = x_err_code
where batch_id = x_batch_id;
x_err_stage:= 'Insert_mass_update_batch_details: asset_category';
FA_CUA_DERIVE_ASSET_ATTR_PKG.insert_mass_update_batch_dtls(
x_batch_id
, x_book_type_code
, 'CATEGORY'
, v_asset_attr_tab(i).asset_id
, to_char(v_asset_attr_tab(i).asset_category_id)
, to_char(v_cat_id_out)
, 'NODE' --x_derived_from_entity_type
, g_derived_from_entity_rec.category -- x_derived_from_entity_id
, v_asset_attr_tab(i).parent_hierarchy_id_old
, v_status_code
, NULL --v_rejection_reason_code
, 'Y' --x_apply_flag
, NULL --x_effective_date
, NULL --x_fa_period_name
, v_conc_request_id
, v_created_by
, v_sysdate
, v_last_updated_by
, v_sysdate
, v_last_update_login
, x_err_code
, x_err_stage
, x_err_stack );
x_err_stage:= 'Insert_mass_update_batch_details: lease_number';
FA_CUA_DERIVE_ASSET_ATTR_PKG.insert_mass_update_batch_dtls(
x_batch_id
, x_book_type_code
, 'LEASE_NUMBER'
, v_asset_attr_tab(i).asset_id
, to_char(v_asset_attr_tab(i).lease_id)
, to_char(v_lease_id_out)
, 'NODE' --x_derived_from_entity_type
, g_derived_from_entity_rec.lease
, v_asset_attr_tab(i).parent_hierarchy_id_old
, v_status_code
, NULL --x_rejection_reason_code
, 'Y' --x_apply_flag
, NULL --x_effective_date
, NULL --x_fa_period_name
, v_conc_request_id
, v_created_by
, v_sysdate
, v_last_updated_by
, v_sysdate
, v_last_update_login
, x_err_code
, x_err_stage
, x_err_stack );
select count(*) into v_dist_count
from fa_hierarchy_distributions
where dist_set_id = v_distribution_set_id_out;
select count(*)
into v_dist_count2
from fa_distribution_history fmd
, fa_hierarchy_distributions ihd
, fa_additions a
where fmd.asset_id = v_asset_attr_tab(i).asset_id
and fmd.asset_id = a.asset_id
and fmd.date_ineffective is null
and ihd.dist_set_id = v_distribution_set_id_out
and ROUND(ihd.distribution_line_percentage, 2)
||ihd.code_combination_id||ihd.location_id||ihd.assigned_to
= ROUND((fmd.units_assigned * 100/a.current_units), 2)
||fmd.code_combination_id||fmd.location_id||fmd.assigned_to;
x_err_stage:= 'Insert_mass_update_batch_details: distribution_set';
FA_CUA_DERIVE_ASSET_ATTR_PKG.insert_mass_update_batch_dtls(
x_batch_id
, x_book_type_code
, 'DISTRIBUTION'
, v_asset_attr_tab(i).asset_id
, NULL -- x_attribute_old_id; old dist_set_id is passed as null
, v_last_updated_by
, v_sysdate
, v_last_update_login
, x_err_code
, x_err_stage
, x_err_stack );
x_err_stage:= 'Insert_mass_update_batch_details: serial_number';
FA_CUA_DERIVE_ASSET_ATTR_PKG.insert_mass_update_batch_dtls(
x_batch_id
, x_book_type_code
, 'SERIAL_NUMBER'
, v_asset_attr_tab(i).asset_id
, v_asset_attr_tab(i).serial_number
, v_serial_number_out
, 'NODE' --x_derived_from_entity_type
, g_derived_from_entity_rec.serial_number
, v_asset_attr_tab(i).parent_hierarchy_id_old
, v_status_code
, v_rejection_reason_code
, 'Y' --x_apply_flag
, NULL --x_effective_date
, NULL --x_fa_period_name
, v_conc_request_id
, v_created_by
, v_sysdate
, v_last_updated_by
, v_sysdate
, v_last_update_login
, x_err_code
, x_err_stage
, x_err_stack );
x_err_stage:= 'Insert_mass_update_batch_details: asset_key';
FA_CUA_DERIVE_ASSET_ATTR_PKG.insert_mass_update_batch_dtls(
x_batch_id
, x_book_type_code
, 'ASSET_KEY'
, nvl(v_asset_attr_tab(i).asset_id, 0)
, to_char(nvl(v_asset_attr_tab(i).asset_key_ccid, 0))
, to_char(nvl(v_asset_key_ccid_out, 0))
, 'NODE' --x_derived_from_entity_type
, g_derived_from_entity_rec.asset_key
, v_asset_attr_tab(i).parent_hierarchy_id_old
, v_status_code
, v_rejection_reason_code
, 'Y' --x_apply_flag
, NULL --x_effective_date
, NULL --x_fa_period_name
, v_conc_request_id
, v_created_by
, v_sysdate
, v_last_updated_by
, v_sysdate
, v_last_update_login
, x_err_code
, x_err_stage
, x_err_stack );
x_err_stage:= 'Insert_mass_update_batch_details: life_end_date';
FA_CUA_DERIVE_ASSET_ATTR_PKG.insert_mass_update_batch_dtls(
x_batch_id
, x_book_type_code
, 'LIFE_END_DATE'
, nvl(v_asset_attr_tab(i).asset_id, 0)
, to_char(nvl(v_asset_attr_tab(i).life_in_months, 0))
, to_char(nvl(v_life_in_months_out, 0))
, g_derived_from_entity_rec.lim_type --x_derived_from_entity_type
, g_derived_from_entity_rec.life_in_months
, v_asset_attr_tab(i).parent_hierarchy_id_old
, v_status_code
, v_rejection_reason_code
, 'Y' --x_apply_flag
, NULL --x_effective_date
, NULL --x_fa_period_name
, v_conc_request_id
, v_created_by
, v_sysdate
, v_last_updated_by
, v_sysdate
, v_last_update_login
, x_err_code
, x_err_stage
, x_err_stack );
select book_type_code
from fa_book_controls
where ( (book_type_code = x_book_type_code)
OR (distribution_source_book = x_book_type_code) )
and book_class IN ( 'CORPORATE', 'TAX')
order by book_class;
update fa_mass_update_batch_headers
set status_code = 'CP'
where batch_id = x_batch_id;
select batch_id
from fa_mass_update_batch_headers
where book_type_code = x_book_type_code
and batch_id = nvl(x_batch_id, batch_id)
and status_code = 'C'
for update NOWAIT;
Delete from fa_mass_update_batch_details
where batch_id = C1_rec.batch_id;
Delete from fa_mass_update_batch_headers
where batch_id = C1_rec.batch_id;
select parent_hierarchy_id
from fa_asset_hierarchy
where asset_id = p_asset_id;
select asset_category_id
into v_asset_cat_id
from fa_additions
where asset_id = p_asset_id;