[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_last_update_date_tbl date_tbl_type;
l_last_updated_by_tbl num_tbl_type;
l_invoice_updated_by_tbl num_tbl_type;
l_last_update_login_tbl num_tbl_type;
select ma.mass_addition_id ,
ma.asset_number ,
ma.tag_number ,
ma.description ,
ma.asset_category_id ,
ma.manufacturer_name ,
ma.serial_number ,
ma.model_number ,
ma.book_type_code ,
ma.date_placed_in_service ,
ma.fixed_assets_cost ,
ma.payables_units ,
ma.fixed_assets_units ,
ma.payables_code_combination_id ,
ma.expense_code_combination_id ,
ma.location_id ,
ma.assigned_to ,
ma.feeder_system_name ,
ma.create_batch_date ,
ma.create_batch_id ,
ma.last_update_date ,
ma.last_updated_by ,
ma.reviewer_comments ,
ma.invoice_number ,
ma.vendor_number ,
ma.po_vendor_id ,
ma.po_number ,
ma.posting_status ,
ma.queue_name ,
ma.invoice_date ,
ma.invoice_created_by ,
ma.invoice_updated_by ,
ma.payables_cost ,
ma.invoice_id ,
ma.payables_batch_name ,
ma.depreciate_flag ,
ma.parent_mass_addition_id ,
ma.parent_asset_id ,
ma.split_merged_code ,
ma.ap_distribution_line_number ,
ma.post_batch_id ,
ma.add_to_asset_id ,
ma.amortize_flag ,
ma.new_master_flag ,
ma.asset_key_ccid ,
ma.asset_type ,
ma.deprn_reserve ,
ma.ytd_deprn ,
ma.beginning_nbv ,
ma.created_by ,
ma.creation_date ,
ma.last_update_login ,
ma.salvage_value ,
ma.accounting_date ,
ma.attribute_category_code ,
ma.fully_rsvd_revals_counter ,
ma.merge_invoice_number ,
ma.merge_vendor_number ,
ma.production_capacity ,
ma.reval_amortization_basis ,
ma.reval_reserve ,
ma.unit_of_measure ,
ma.unrevalued_cost ,
ma.ytd_reval_deprn_expense ,
ma.merged_code ,
ma.split_code ,
ma.merge_parent_mass_additions_id ,
ma.split_parent_mass_additions_id ,
ma.project_asset_line_id ,
ma.project_id ,
ma.task_id ,
ma.sum_units ,
ma.dist_name ,
ma.context ,
ma.inventorial ,
ma.short_fiscal_year_flag ,
ma.conversion_date ,
ma.original_deprn_start_date ,
ma.group_asset_id ,
ma.cua_parent_hierarchy_id ,
ma.units_to_adjust ,
ma.bonus_ytd_deprn ,
ma.bonus_deprn_reserve ,
ma.amortize_nbv_flag ,
ma.amortization_start_date ,
ma.transaction_type_code ,
ma.transaction_date ,
ma.warranty_id ,
ma.lease_id ,
ma.lessor_id ,
ma.property_type_code ,
ma.property_1245_1250_code ,
ma.in_use_flag ,
ma.owned_leased ,
ma.new_used ,
ma.asset_id ,
ma.material_indicator_flag ,
ma.mass_property_flag ,
ma.deprn_method_code , -- additional columns
ma.life_in_months ,
ma.basic_rate ,
ma.adjusted_rate ,
ma.prorate_convention_code ,
ma.bonus_rule ,
ma.salvage_type ,
ma.percent_salvage_value ,
ma.deprn_limit_type ,
ma.allowed_deprn_limit_amount ,
ma.allowed_deprn_limit ,
ma.invoice_distribution_id ,
ma.invoice_line_number ,
ma.po_distribution_id , -- end additional columns
ma.attribute1 ,
ma.attribute2 ,
ma.attribute3 ,
ma.attribute4 ,
ma.attribute5 ,
ma.attribute6 ,
ma.attribute7 ,
ma.attribute8 ,
ma.attribute9 ,
ma.attribute10 ,
ma.attribute11 ,
ma.attribute12 ,
ma.attribute13 ,
ma.attribute14 ,
ma.attribute15 ,
ma.attribute16 ,
ma.attribute17 ,
ma.attribute18 ,
ma.attribute19 ,
ma.attribute20 ,
ma.attribute21 ,
ma.attribute22 ,
ma.attribute23 ,
ma.attribute24 ,
ma.attribute25 ,
ma.attribute26 ,
ma.attribute27 ,
ma.attribute28 ,
ma.attribute29 ,
ma.attribute30 ,
ma.global_attribute1 ,
ma.global_attribute2 ,
ma.global_attribute3 ,
ma.global_attribute4 ,
ma.global_attribute5 ,
ma.global_attribute6 ,
ma.global_attribute7 ,
ma.global_attribute8 ,
ma.global_attribute9 ,
ma.global_attribute10 ,
ma.global_attribute11 ,
ma.global_attribute12 ,
ma.global_attribute13 ,
ma.global_attribute14 ,
ma.global_attribute15 ,
ma.global_attribute16 ,
ma.global_attribute17 ,
ma.global_attribute18 ,
ma.global_attribute19 ,
ma.global_attribute20 ,
ma.global_attribute_category ,
ma.th_attribute1 ,
ma.th_attribute2 ,
ma.th_attribute3 ,
ma.th_attribute4 ,
ma.th_attribute5 ,
ma.th_attribute6 ,
ma.th_attribute7 ,
ma.th_attribute8 ,
ma.th_attribute9 ,
ma.th_attribute10 ,
ma.th_attribute11 ,
ma.th_attribute12 ,
ma.th_attribute13 ,
ma.th_attribute14 ,
ma.th_attribute15 ,
ma.th_attribute_category_code ,
ma.nbv_at_switch, -- Changes made as per the ER No.s 6606548 and 6606552 by Sbyreddy
ma.prior_deprn_limit_type,
ma.prior_deprn_limit_amount,
ma.prior_deprn_limit,
--ma.period_counter_fully_reserved,
--ma.extended_depreciation_period,
ma.period_full_reserve,
ma.period_extd_deprn,
ma.prior_deprn_method,
ma.prior_life_in_months,
ma.prior_basic_rate,
ma.prior_adjusted_rate -- Changes made as per the ER No.s 6606548 and 6606552 by Sbyreddy
,ma.adjusted_cost ----bug#16426081
from fa_mass_additions ma
where ma.posting_status = 'POST'
and ma.book_type_code = p_book_type_code
and ma.request_id = p_parent_request_id
and ma.worker_id = p_request_number
and ma.process_order = p_process_order
order by ma.mass_addition_id;
SELECT set_of_books_id,
exchange_rate,
fixed_assets_cost
from fa_mc_mass_rates
where mass_addition_id = p_massadd_id;
SELECT child.po_vendor_id,
child.mass_addition_id,
child.fixed_assets_cost,
child.po_number,
child.invoice_number,
child.payables_batch_name,
child.payables_code_combination_id,
child.feeder_system_name,
child.create_batch_date,
child.create_batch_id,
child.invoice_date,
child.payables_cost,
child.invoice_id,
child.ap_distribution_line_number,
child.payables_units,
'MC',
child.split_code,
'MC',
child.description,
child.split_parent_mass_additions_id,
child.merge_parent_mass_additions_id,
child.project_id,
child.task_id,
child.project_asset_line_id,
child.ytd_deprn,
child.deprn_reserve,
child.bonus_ytd_deprn,
child.bonus_deprn_reserve,
child.reval_amortization_basis,
child.ytd_reval_deprn_expense,
child.reval_reserve,
child.material_indicator_flag,
child.invoice_distribution_id,
child.invoice_line_number,
child.po_distribution_id
FROM fa_mass_additions child
WHERE child.merge_parent_mass_additions_id = p_massadd_id;
select dist.employee_id,
dist.deprn_expense_ccid,
dist.location_id,
sum(dist.units)
from (select mad.units,
mad.employee_id,
mad.deprn_expense_ccid,
mad.location_id
from fa_massadd_distributions mad
where mad.mass_addition_id = p_massadd_id
union all
select mad.units,
mad.employee_id,
mad.deprn_expense_ccid,
mad.location_id
from fa_massadd_distributions mad,
fa_mass_additions mac,
fa_mass_additions map
where map.sum_units = 'YES'
and map.mass_addition_id = p_massadd_id
and map.mass_addition_id = mac.merge_parent_mass_additions_id
and mad.mass_addition_id = mac.mass_addition_id) dist
group by dist.employee_id,
dist.deprn_expense_ccid,
dist.location_id;
select dh.distribution_id,
dh.units_assigned,
dh.code_combination_id,
dh.location_id,
dh.assigned_to
from fa_distribution_history dh
where dh.asset_id = p_add_to_asset_id
and dh.book_type_code = p_book_type_code
and dh.date_ineffective IS NULL
and dh.retirement_id IS NULL;
select dh.distribution_id,
dh.units_assigned,
dh.code_combination_id,
dh.location_id,
dh.assigned_to
from fa_distribution_history dh
where dh.asset_id = p_add_to_asset_id
and dh.book_type_code = p_book_type_code
and dh.code_combination_id = p_ccid
and dh.location_id = p_locid
and nvl(dh.assigned_to,-99) = nvl(p_empid,-99)
and dh.date_ineffective IS NULL
and dh.retirement_id IS NULL;
l_last_update_date_tbl ,
l_last_updated_by_tbl ,
l_reviewer_comments_tbl ,
l_invoice_number_tbl ,
l_vendor_number_tbl ,
l_po_vendor_id_tbl ,
l_po_number_tbl ,
l_posting_status_tbl ,
l_queue_name_tbl ,
l_invoice_date_tbl ,
l_invoice_created_by_tbl ,
l_invoice_updated_by_tbl ,
l_payables_cost_tbl ,
l_invoice_id_tbl ,
l_payables_batch_name_tbl ,
l_depreciate_flag_tbl ,
l_parent_mass_addition_id_tbl ,
l_parent_asset_id_tbl ,
l_split_merged_code_tbl ,
l_ap_dist_line_num_tbl ,
l_post_batch_id_tbl ,
l_add_to_asset_id_tbl ,
l_amortize_flag_tbl ,
l_new_master_flag_tbl ,
l_asset_key_ccid_tbl ,
l_asset_type_tbl ,
l_deprn_reserve_tbl ,
l_ytd_deprn_tbl ,
l_beginning_nbv_tbl ,
l_created_by_tbl ,
l_creation_date_tbl ,
l_last_update_login_tbl ,
l_salvage_value_tbl ,
l_accounting_date_tbl ,
l_attribute_category_code_tbl ,
l_fully_rsvd_revals_ctr_tbl ,
l_merge_invoice_number_tbl ,
l_merge_vendor_number_tbl ,
l_production_capacity_tbl ,
l_reval_amortization_basis_tbl ,
l_reval_reserve_tbl ,
l_unit_of_measure_tbl ,
l_unrevalued_cost_tbl ,
l_ytd_reval_deprn_expense_tbl ,
l_merged_code_tbl ,
l_split_code_tbl ,
l_merge_parent_massadd_id_tbl ,
l_split_parent_massadd_id_tbl ,
l_project_asset_line_id_tbl ,
l_project_id_tbl ,
l_task_id_tbl ,
l_sum_units_tbl ,
l_dist_name_tbl ,
l_context_tbl ,
l_inventorial_tbl ,
l_short_fiscal_year_flag_tbl ,
l_conversion_date_tbl ,
l_orig_deprn_start_date_tbl ,
l_group_asset_id_tbl ,
l_cua_parent_hierarchy_id_tbl ,
l_units_to_adjust_tbl ,
l_bonus_ytd_deprn_tbl ,
l_bonus_deprn_reserve_tbl ,
l_amortize_nbv_flag_tbl ,
l_amortization_start_date_tbl ,
l_transaction_type_code_tbl ,
l_transaction_date_tbl ,
l_warranty_id_tbl ,
l_lease_id_tbl ,
l_lessor_id_tbl ,
l_property_type_code_tbl ,
l_property_1245_1250_code_tbl ,
l_in_use_flag_tbl ,
l_owned_leased_tbl ,
l_new_used_tbl ,
l_asset_id_tbl ,
l_material_indicator_flag_tbl ,
l_mass_property_flag_tbl ,
l_deprn_method_code_tbl , -- start new fields
l_life_in_months_tbl ,
l_basic_rate_tbl ,
l_adjusted_rate_tbl ,
l_prorate_convention_code_tbl ,
l_bonus_rule_tbl ,
l_salvage_type_tbl ,
l_percent_salvage_value_tbl ,
l_deprn_limit_type_tbl ,
l_allowed_deprn_limit_amt_tbl ,
l_allowed_deprn_limit_tbl ,
l_invoice_distribution_id_tbl ,
l_invoice_line_number_tbl ,
l_po_distribution_id_tbl , -- end new fields
l_attribute1_tbl ,
l_attribute2_tbl ,
l_attribute3_tbl ,
l_attribute4_tbl ,
l_attribute5_tbl ,
l_attribute6_tbl ,
l_attribute7_tbl ,
l_attribute8_tbl ,
l_attribute9_tbl ,
l_attribute10_tbl ,
l_attribute11_tbl ,
l_attribute12_tbl ,
l_attribute13_tbl ,
l_attribute14_tbl ,
l_attribute15_tbl ,
l_attribute16_tbl ,
l_attribute17_tbl ,
l_attribute18_tbl ,
l_attribute19_tbl ,
l_attribute20_tbl ,
l_attribute21_tbl ,
l_attribute22_tbl ,
l_attribute23_tbl ,
l_attribute24_tbl ,
l_attribute25_tbl ,
l_attribute26_tbl ,
l_attribute27_tbl ,
l_attribute28_tbl ,
l_attribute29_tbl ,
l_attribute30_tbl ,
l_global_attribute1_tbl ,
l_global_attribute2_tbl ,
l_global_attribute3_tbl ,
l_global_attribute4_tbl ,
l_global_attribute5_tbl ,
l_global_attribute6_tbl ,
l_global_attribute7_tbl ,
l_global_attribute8_tbl ,
l_global_attribute9_tbl ,
l_global_attribute10_tbl ,
l_global_attribute11_tbl ,
l_global_attribute12_tbl ,
l_global_attribute13_tbl ,
l_global_attribute14_tbl ,
l_global_attribute15_tbl ,
l_global_attribute16_tbl ,
l_global_attribute17_tbl ,
l_global_attribute18_tbl ,
l_global_attribute19_tbl ,
l_global_attribute20_tbl ,
l_global_attribute_cat_tbl ,
l_th_attribute1_tbl ,
l_th_attribute2_tbl ,
l_th_attribute3_tbl ,
l_th_attribute4_tbl ,
l_th_attribute5_tbl ,
l_th_attribute6_tbl ,
l_th_attribute7_tbl ,
l_th_attribute8_tbl ,
l_th_attribute9_tbl ,
l_th_attribute10_tbl ,
l_th_attribute11_tbl ,
l_th_attribute12_tbl ,
l_th_attribute13_tbl ,
l_th_attribute14_tbl ,
l_th_attribute15_tbl ,
l_th_attribute_cat_code_tbl,
l_nbv_at_switch_tbl, -- Changes made as per the ER No.s 6606548 and 6606552 by Sbyreddy
l_prior_deprn_limit_type_tbl,
l_prior_deprn_limit_amt_tbl,
l_prior_deprn_limit_tbl,
l_period_full_reserve_tbl,
l_period_extd_deprn_tbl,
l_prior_deprn_method_tbl,
l_prior_life_in_months_tbl,
l_prior_basic_rate_tbl,
l_prior_adjusted_rate_tbl, -- Changes made as per the ER No.s 6606548 and 6606552 by Sbyreddy
l_adjusted_cost_tbl --bug#16426081
limit l_batch_size;
l_asset_fin_mrc_tbl_new.delete;
l_inv_tbl.delete;
l_asset_deprn_mrc_tbl_new.delete;
l_asset_dist_tbl.delete;
l_c_mass_addition_id_tbl.delete;
l_c_description_tbl.delete;
l_c_payables_units_tbl.delete;
l_c_fixed_assets_cost_tbl.delete;
l_c_payables_ccid_tbl.delete;
l_c_feeder_system_name_tbl.delete;
l_c_create_batch_date_tbl.delete;
l_c_create_batch_id_tbl.delete;
l_c_invoice_number_tbl.delete;
l_c_po_vendor_id_tbl.delete;
l_c_po_number_tbl.delete;
l_c_invoice_date_tbl.delete;
l_c_payables_cost_tbl.delete;
l_c_invoice_id_tbl.delete;
l_c_payables_batch_name_tbl.delete;
l_c_split_merged_code_tbl.delete;
l_c_ap_dist_line_num_tbl.delete;
l_c_deprn_reserve_tbl.delete;
l_c_ytd_deprn_tbl.delete;
l_c_reval_amort_basis_tbl.delete;
l_c_reval_reserve_tbl.delete;
l_c_unrevalued_cost_tbl.delete;
l_c_ytd_reval_deprn_exp_tbl.delete;
l_c_merged_code_tbl.delete;
l_c_split_code_tbl.delete;
l_c_merge_parent_massadd_tbl.delete;
l_c_split_parent_massadd_tbl.delete;
l_c_project_asset_line_id_tbl.delete;
l_c_project_id_tbl.delete;
l_c_task_id_tbl.delete;
l_c_bonus_ytd_deprn_tbl.delete;
l_c_bonus_deprn_reserve_tbl.delete;
l_c_invoice_dist_id_tbl.delete;
l_c_invoice_line_number_tbl.delete;
l_c_po_distribution_id_tbl.delete;
l_set_of_books_id_tbl.delete;
l_exchange_rate_tbl.delete;
l_mc_fixed_assets_cost_tbl.delete;
l_mad_units_tbl.delete;
l_mad_employee_id_tbl.delete;
l_mad_deprn_expense_ccid_tbl.delete;
l_mad_location_id_tbl.delete;
l_dh_distribution_id_tbl.delete;
l_dh_units_assigned_tbl.delete;
l_inv_rec.deleted_flag := 'NO';
l_inv_rec.deleted_flag := 'NO';
update fa_additions_tl
set description = l_description_tbl(i),
source_lang = userenv('LANG')
where asset_id = l_add_to_asset_id_tbl(i)
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
update fa_mass_additions
set posting_status = 'POSTED',
queue_name = 'POSTED',
post_batch_id = p_parent_request_id,
asset_number = l_succ_asset_number,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where mass_addition_id = l_mass_addition_id_tbl(i);
update fa_mass_additions
set posting_status = 'POSTED',
queue_name = 'POSTED',
post_batch_id = p_parent_request_id,
asset_number = l_succ_asset_number,
add_to_asset_id = l_add_to_asset_id_tbl(i),
asset_category_id = l_asset_category_id_tbl(i),
asset_type = l_asset_type_tbl(i),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where merge_parent_mass_additions_id = l_mass_addition_id_tbl(i);
update fa_mass_additions
set posting_status = 'ON HOLD',
queue_name = 'ON HOLD'
where mass_addition_id = l_mass_addition_id_tbl(i);
update fa_mass_additions
set posting_status = 'ON HOLD',
queue_name = 'ON HOLD'
where mass_addition_id = l_mass_addition_id_tbl(i);
l_attribute1_tbl.delete;
l_attribute2_tbl.delete;
l_attribute3_tbl.delete;
l_attribute4_tbl.delete;
l_attribute5_tbl.delete;
l_attribute6_tbl.delete;
l_attribute7_tbl.delete;
l_attribute8_tbl.delete;
l_attribute9_tbl.delete;
l_attribute10_tbl.delete;
l_attribute11_tbl.delete;
l_attribute12_tbl.delete;
l_attribute13_tbl.delete;
l_attribute14_tbl.delete;
l_attribute15_tbl.delete;
l_attribute16_tbl.delete;
l_attribute17_tbl.delete;
l_attribute18_tbl.delete;
l_attribute19_tbl.delete;
l_attribute20_tbl.delete;
l_attribute21_tbl.delete;
l_attribute22_tbl.delete;
l_attribute23_tbl.delete;
l_attribute24_tbl.delete;
l_attribute25_tbl.delete;
l_attribute26_tbl.delete;
l_attribute27_tbl.delete;
l_attribute28_tbl.delete;
l_attribute29_tbl.delete;
l_attribute30_tbl.delete;
l_attribute_category_code_tbl.delete;
l_global_attribute1_tbl.delete;
l_global_attribute2_tbl.delete;
l_global_attribute3_tbl.delete;
l_global_attribute4_tbl.delete;
l_global_attribute5_tbl.delete;
l_global_attribute6_tbl.delete;
l_global_attribute7_tbl.delete;
l_global_attribute8_tbl.delete;
l_global_attribute9_tbl.delete;
l_global_attribute10_tbl.delete;
l_global_attribute11_tbl.delete;
l_global_attribute12_tbl.delete;
l_global_attribute13_tbl.delete;
l_global_attribute14_tbl.delete;
l_global_attribute15_tbl.delete;
l_global_attribute16_tbl.delete;
l_global_attribute17_tbl.delete;
l_global_attribute18_tbl.delete;
l_global_attribute19_tbl.delete;
l_global_attribute20_tbl.delete;
l_global_attribute_cat_tbl.delete;
l_th_attribute1_tbl.delete;
l_th_attribute2_tbl.delete;
l_th_attribute3_tbl.delete;
l_th_attribute4_tbl.delete;
l_th_attribute5_tbl.delete;
l_th_attribute6_tbl.delete;
l_th_attribute7_tbl.delete;
l_th_attribute8_tbl.delete;
l_th_attribute9_tbl.delete;
l_th_attribute10_tbl.delete;
l_th_attribute11_tbl.delete;
l_th_attribute12_tbl.delete;
l_th_attribute13_tbl.delete;
l_th_attribute14_tbl.delete;
l_th_attribute15_tbl.delete;
l_th_attribute_cat_code_tbl.delete;
Select 'Mass Property Asset Exists',
ad.asset_id,
ad.asset_type,
bk.date_placed_in_service,
ad.description,
fy1.start_date,
fy1.end_date
from fa_books bk,
fa_additions ad,
fa_book_controls bc,
fa_fiscal_year fy1,
fa_fiscal_year fy2,
fa_calendar_periods cp1
where ad.asset_category_id = p_category_id
and ad.asset_type = p_asset_type
and bk.book_type_code = p_book_type_code
and bc.book_type_code = bk.book_type_code
and bc.fiscal_year_name = fy1.fiscal_year_name
and bk.asset_id = ad.asset_id
and bk.period_counter_fully_retired is null
and bk.transaction_header_id_out is NULL
and bk.date_placed_in_service = cp1.start_date
and cp1.calendar_type = bc.deprn_calendar
and cp1.period_num = 1
and cp1.start_date between fy1.start_date and fy1.end_date
and fy2.fiscal_year = fy1.fiscal_year
and fy2.fiscal_year_name = bc.fiscal_year_name
and trunc(p_date_placed_in_service) between
fy2.start_date and fy2.end_date
order by ad.asset_id;
G_new_mp_asset_tbl.delete;
Select fy.start_date,
to_char(fy.fiscal_year),
cat.description,
fy.start_date,
fy.end_date
Into l_new_dpis,
l_new_desc_year,
l_new_desc_category,
l_new_start_date,
l_new_end_date
From fa_fiscal_year fy,
fa_book_controls bc,
fa_categories cat
Where px_date_placed_in_service_tbl(i) between fy.start_date and fy.end_date
And fy.fiscal_year_name = bc.fiscal_year_name
And bc.book_type_code = p_book_type_code
And cat.category_id = p_asset_category_id_tbl(i);
select fa_additions_s.nextval
into px_asset_id_tbl(i)
from dual;
update fa_mass_additions
set description = px_description_tbl(i),
date_placed_in_service = px_date_placed_in_service_tbl(i),
asset_id = px_asset_id_tbl(i),
add_to_asset_id = px_add_to_asset_id_tbl(i),
units_to_adjust = px_units_to_adjust_tbl(i),
amortize_flag = px_amortize_flag_tbl(i),
amortization_start_date = px_amortization_start_date_tbl(i),
mass_property_flag = 'Y',
process_order = l_process_order(i)
where rowid = p_rowid_tbl(i);
select mad.rowid,
mad.mass_addition_id,
mad.asset_id,
mad.add_to_asset_id,
mad.asset_category_id,
mad.asset_type,
mad.date_placed_in_service,
mad.amortize_flag,
mad.amortization_start_date,
mad.description,
mad.fixed_assets_units,
mad.units_to_adjust
from fa_mass_additions mad,
fa_category_book_defaults cbd
where mad.book_type_code = p_book_type_code
and mad.posting_status = 'POST'
and mad.add_to_asset_id is null
and mad.merge_parent_mass_additions_id is null
and mad.asset_type <> 'EXPENSED'
and mad.transaction_date is not null
and mad.transaction_date <= l_calendar_period_close_date
and nvl(mad.transaction_type_code, 'FUTURE ADD') in ('FUTURE ADD', 'FUTURE ADJ')
and mad.asset_category_id = cbd.category_id
and mad.book_type_code = cbd.book_type_code
and mad.date_placed_in_service
between start_dpis and nvl(end_dpis,add_months(sysdate,1200))
and cbd.mass_property_flag = 'Y'
order by mad.asset_category_id, mad.date_placed_in_service;
select mad.rowid,
mad.mass_addition_id,
mad.asset_id,
mad.add_to_asset_id,
mad.asset_category_id,
mad.asset_type,
mad.date_placed_in_service,
mad.amortize_flag,
mad.amortization_start_date,
mad.description,
mad.fixed_assets_units,
mad.units_to_adjust
from fa_mass_additions mad,
fa_category_book_defaults cbd
where mad.book_type_code = p_book_type_code
and mad.posting_status = 'POST'
and mad.add_to_asset_id is null
and mad.merge_parent_mass_additions_id is null
and mad.asset_type <> 'EXPENSED'
and mad.transaction_date is null
and mad.asset_category_id = cbd.category_id
and mad.book_type_code = cbd.book_type_code
and mad.date_placed_in_service
between start_dpis and nvl(end_dpis,add_months(sysdate,1200))
and cbd.mass_property_flag = 'Y'
order by mad.asset_category_id, mad.date_placed_in_service;
update fa_mass_additions mad
set group_asset_id =
(select cbd.group_asset_id
from fa_category_book_defaults cbd
where mad.asset_category_id = cbd.category_id
and mad.book_type_code = p_book_type_code
and cbd.book_type_code = p_book_type_code
and mad.date_placed_in_service between
cbd.start_dpis and nvl(cbd.end_dpis, mad.transaction_date)
and cbd.group_asset_id is not null)
where book_type_code = p_book_type_code
and posting_status = 'POST'
and mad.transaction_date is null
and mad.add_to_asset_id is null
and mad.group_asset_id is null
and nvl(mad.transaction_type_code, 'FUTURE ADD') not in ('FUTURE CAP', 'FUTURE REV');
update fa_mass_additions mad
set group_asset_id =
(select group_asset_id
from fa_books bk
where bk.asset_id = mad.add_to_asset_id
and bk.book_type_code = mad.book_type_code
and bk.transaction_header_id_out is null)
where book_type_code = p_book_type_code
and posting_status = 'POST'
and mad.transaction_date is null
and mad.add_to_asset_id is not null
and mad.group_asset_id is null
and nvl(mad.transaction_type_code, 'FUTURE ADD') not in ('FUTURE CAP', 'FUTURE REV');
update fa_mass_additions mad
set mad.request_id = p_parent_request_id,
mad.worker_id = mod(nvl(mad.group_asset_id,
nvl(mad.add_to_asset_id,
nvl(mad.asset_id,
mad.mass_addition_id))),
p_total_requests) + 1,
mad.process_order = decode(mad.process_order,
null, 1,
mad.process_order + 1)
where mad.book_type_code = p_book_type_code
and mad.posting_status = 'POST'
and mad.transaction_date is null;
update fa_mass_additions mad
set group_asset_id =
(select cbd.group_asset_id
from fa_category_book_defaults cbd
where mad.asset_category_id = cbd.category_id
and mad.book_type_code = p_book_type_code
and cbd.book_type_code = p_book_type_code
and mad.date_placed_in_service between
cbd.start_dpis and nvl(cbd.end_dpis, mad.transaction_date)
and cbd.group_asset_id is not null
and mad.add_to_asset_id is null)
where book_type_code = p_book_type_code
and posting_status = 'POST'
and mad.transaction_date is not null
and mad.transaction_date <= l_calendar_period_close_date
and mad.add_to_asset_id is null
and mad.group_asset_id is null
and nvl(mad.transaction_type_code, 'FUTURE ADD') not in ('FUTURE CAP', 'FUTURE REV');
update fa_mass_additions mad
set group_asset_id =
(select group_asset_id
from fa_books bk
where bk.asset_id = mad.add_to_asset_id
and bk.book_type_code = mad.book_type_code
and bk.transaction_header_id_out is null)
where book_type_code = p_book_type_code
and posting_status = 'POST'
and mad.transaction_date is not null
and mad.transaction_date <= l_calendar_period_close_date
and mad.add_to_asset_id is not null
and mad.group_asset_id is null
and nvl(mad.transaction_type_code, 'FUTURE ADD') not in ('FUTURE CAP', 'FUTURE REV');
update fa_mass_additions mad
set mad.request_id = p_parent_request_id,
mad.worker_id = mod(nvl(mad.group_asset_id,
nvl(mad.add_to_asset_id,
nvl(mad.asset_id,
mad.mass_addition_id))),
p_total_requests) + 1,
mad.process_order = decode(mad.process_order,
null, decode(mad.transaction_type_code,
'FUTURE ADD', 2,
'FUTURE ADJ', 3,
'FUTURE CAP', 3,
'FUTURE REV', 3,
NULL),
'FUTURE ADD', 3,
'FUTURE ADJ', 4,
'FUTURE CAP', 4,
'FUTURE REV', 4,
NULL)
where mad.book_type_code = p_book_type_code
and mad.posting_status = 'POST'
and mad.transaction_date is not null
and mad.transaction_date <= l_calendar_period_close_date;
fa_debug_pkg.add(l_calling_fn, 'rows inserted into fa_mass_addition_trxs', sql%rowcount);