[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select add_months(x_prorate_date,x_life)
from dual;
select max(cp.end_date)
from fa_calendar_periods cp,
fa_calendar_types ct,
fa_book_controls bc
where bc.book_type_code = X_book_type_code and
bc.date_ineffective is null and
ct.calendar_type = bc.prorate_calendar and
cp.calendar_type = ct.calendar_type and
cp.end_date <= add_months(x_prorate_date,x_life);
select add_months(x_prorate_date,x_life)
from dual;
select /*+ ordered */ round
(nvl(sum
(decode (bc.deprn_allocation_code,'E',
1/ct.number_per_fiscal_year,
(cp.end_date + 1 - cp.start_date) /
(fy.end_date + 1 - fy.start_date))),0) * 12, 0)
from fa_calendar_periods cp,
fa_calendar_types ct,
fa_book_controls bc,
fa_fiscal_year fy
where bc.book_type_code = X_book_type_code and
bc.date_ineffective is null and
ct.calendar_type = bc.prorate_calendar and
ct.fiscal_year_name = bc.fiscal_year_name
and cp.calendar_type = ct.calendar_type and
( (cp.start_date >= x_prorate_date and
cp.end_date <= x_end_date)
or
(cp.start_date <= x_prorate_date
and cp.end_date >= x_prorate_date
and cp.start_date <= x_end_date
and cp.end_date <= x_end_date))
and fy.fiscal_year_name = bc.fiscal_year_name
and fy.start_date <= cp.start_date
and fy.end_date >= cp.end_date;
select rate_source_rule
from fa_methods
where method_code = x_deprn_method;
select 'X'
from fa_methods
where method_code = x_deprn_method
and nvl(life_in_months,0) = x_life;
select decode(x_life,null,1,0,1,x_life)
into x_life
from dual;
FA_METHODS_PKG.Insert_Row(
X_Rowid => l_rowid,
X_Method_Id => l_method_id,
X_Method_Code => x_deprn_method,
X_Life_In_Months => x_life,
X_Depreciate_Lastyear_Flag => 'YES',
X_STL_Method_Flag => 'YES',
X_Rate_Source_Rule => 'CALCULATED',
X_Deprn_Basis_Rule => 'COST',
X_Prorate_Periods_Per_Year => NULL,
X_Name => 'Straight-Line',
X_Last_Update_Date => sysdate,
X_Last_Updated_By => FND_GLOBAL.LOGIN_ID,
X_Created_By => FND_GLOBAL.LOGIN_ID,
X_Creation_Date => sysdate,
X_Last_Update_Login => FND_GLOBAL.LOGIN_ID,
X_Attribute1 => null,
X_Attribute2 => null,
X_Attribute3 => null,
X_Attribute4 => null,
X_Attribute5 => null,
X_Attribute6 => null,
X_Attribute7 => null,
X_Attribute8 => null,
X_Attribute9 => null,
X_Attribute10 => null,
X_Attribute11 => null,
X_Attribute12 => null,
X_Attribute13 => null,
X_Attribute14 => null,
X_Attribute15 => null,
X_Attribute_Category_Code => null,
X_Calling_Fn => 'CALC_LIFE');
select bc.book_type_code into ll_corp_book
from fa_books bk, fa_book_controls bc
where bc.book_class = 'CORPORATE'
and bk.asset_id = X_Asset_Id
and bk.book_type_code = bc.book_type_code
and bk.date_ineffective is null;
select count(*)
into ll_count
from fa_category_books
where category_id = X_new_cat_ID
and book_type_code = ll_corp_book
and cip_cost_acct is not null
and cip_clearing_acct is not null;
select count(*)
into ll_count
from fa_category_books
where book_type_code = ll_corp_book and
category_id = X_new_cat_ID;
select count(*) into ll_count
from fa_additions
where lease_id = X_Lease_Id
and asset_category_id in
(select category_id from fa_categories
where category_type = 'LEASEHOLD IMPROVEMENT');
x_err_code := 'FA_ADD_DELETE_LHOLD_BEFORE_RCL';
select count(*) into ll_count
from fa_leases
where lease_id = X_Lease_Id;
x_err_code := 'FA_ADD_DELETE_LEASE_BEFORE_RCL';
select count(*)
into ll_count
from fa_retirements
where asset_id = X_Asset_Id
and status in ('PENDING', 'REINSTATE', 'PARTIAL');
Procedure update_category
(x_asset_id in number,
x_old_cat_id in number,
x_new_cat_id in number,
x_err_code in out nocopy varchar2 ,
x_err_stage in out nocopy varchar2 ,
x_err_stack in out nocopy varchar2 ) IS
--l_status Boolean := TRUE;
l_calling_fn VARCHAR2(20) := 'update_category';
select bc.book_type_code
into l_asset_hdr_rec.book_type_code
from fa_books bk,
fa_book_controls bc
where bk.asset_id = x_asset_id
and bk.book_type_code = bc.book_type_code
and bk.transaction_header_id_out is null
and bc.book_class = 'CORPORATE';
SELECT
DH.ROWID,
DH.DISTRIBUTION_ID,
DH.CODE_COMBINATION_ID,
DH.UNITS_ASSIGNED,
DH.LOCATION_ID,
DH.DATE_EFFECTIVE,
DH.ASSIGNED_TO,
DH.TRANSACTION_HEADER_ID_IN
FROM
FA_DISTRIBUTION_HISTORY DH,
FA_BOOK_CONTROLS BC
WHERE
DH.ASSET_ID = p_asset_id AND
DH.BOOK_TYPE_CODE = BC.DISTRIBUTION_SOURCE_BOOK AND
BC.BOOK_TYPE_CODE = p_book_type_code AND
DH.DATE_INEFFECTIVE IS NULL AND
DH.RETIREMENT_ID IS NULL;
SELECT
(DH.DISTRIBUTION_LINE_PERCENTAGE/100)* p_txn_units new_units,
DH.CODE_COMBINATION_ID expense_ccid,
DH.LOCATION_ID,
DH.ASSIGNED_TO
FROM
FA_HIERARCHY_DISTRIBUTIONS DH,
FA_ADDITIONS A
WHERE
A.ASSET_ID = p_asset_id AND
DH.DIST_SET_ID = p_new_hr_dist_set_id;
l_asset_dist_tbl.delete;
select basic_rate,
adjusted_rate,
production_capacity,
deprn_method_code
from fa_books
where asset_id = px_asset_hdr_rec.asset_id
and book_type_code = px_asset_hdr_rec.book_type_code
and date_ineffective is null;
p_calling_fn => 'fa_cua_mass_update1_pkg.do_adjustment',
px_trans_rec => px_trans_rec,
px_asset_hdr_rec => px_asset_hdr_rec,
p_asset_fin_rec_adj => l_asset_fin_rec_adj,
x_asset_fin_rec_new => l_asset_fin_rec_new,
x_asset_fin_mrc_tbl_new => l_asset_fin_mrc_tbl_new,
px_inv_trans_rec => l_inv_trans_rec,
px_inv_tbl => l_inv_tbl,
p_asset_deprn_rec_adj => l_asset_deprn_rec_adj,
x_asset_deprn_rec_new => l_asset_deprn_rec_new,
x_asset_deprn_mrc_tbl_new => l_asset_deprn_mrc_tbl_new,
p_group_reclass_options_rec => l_group_reclass_options_rec );
SELECT batch_id,
decode(amortize_flag,'Y','YES','NO') amortize_flag,
amortization_date,
transaction_name,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
FROM fa_mass_update_batch_headers
WHERE status_code in ('IP','P','PP','R')
AND batch_number >= nvl(X_from_Batch_number,batch_number)
AND batch_number <= nvl(X_to_batch_number,batch_number)
order by creation_date;
l_trans_rec.who_info.last_update_date := sysdate;
l_trans_rec.who_info.last_updated_by := fnd_global.user_id;
l_trans_rec.who_info.created_by := l_trans_rec.who_info.last_updated_by;
l_trans_rec.who_info.last_update_login := fnd_global.login_id;
update fa_mass_update_batch_headers
set status_code = 'IP'
where batch_id = l_batch_rec.batch_id;
select 'Y'
into l_dummy
from fa_mass_update_batch_details
where batch_id = l_batch_rec.batch_id
and status_code in ('P','R')
and rownum = 1;
update fa_mass_update_batch_headers
set status_code = 'R' -- Rejected Processed
, concurrent_request_id = l_Request_ID
, last_updated_by = fnd_global.login_id
, last_update_date = sysdate
, last_update_login = fnd_global.login_id
where batch_id = l_batch_rec.batch_id;
update fa_mass_update_batch_headers
set status_code = 'CP' -- Completetly Processed
, concurrent_request_id = l_Request_ID
, last_updated_by = fnd_global.login_id
, last_update_date = sysdate
, last_update_login = fnd_global.login_id
where batch_id = l_batch_rec.batch_id;
ERRBUF := 'Failed to Submit Mass Update report';
SELECT distinct book_type_code
from fa_mass_update_batch_details
WHERE batch_id = p_Batch_ID
and status_code in ('P','R')
and apply_flag = 'Y';
SELECT distinct asset_id
from fa_mass_update_batch_details
WHERE batch_id = p_Batch_ID
and book_type_code = l_book
and status_code in ('P','R')
and apply_flag = 'Y';
select greatest(calendar_period_open_date,
least(sysdate, calendar_period_close_date))
into l_transaction_date
from fa_deprn_periods
where book_type_code = l_book
and period_close_date is null;
/* moved this update here for bugfix 1389275 */
-- Changes the status of the Asset detail records to In Process
update fa_mass_update_batch_details
set status_code = 'IP'
where batch_id = p_batch_id
and asset_id = l_asset_id
and book_type_code = l_book;
update fa_mass_update_batch_details
set rejection_reason = l_error_tab(l_asset_id).rejection_reason,
concurrent_request_id = px_trans_rec.mass_reference_id,
status_code = 'R',
last_updated_by = px_trans_rec.who_info.last_updated_by,
last_update_date = px_trans_rec.who_info.last_update_date,
last_update_login = px_trans_rec.who_info.last_update_login
where asset_id = l_asset_id
and book_type_code = l_book
and batch_id = p_batch_id
and attribute_name = nvl(l_attribute_name, attribute_name)
and status_code in ('P','R') -- since rollback will revert the update to 'IP'
and nvl(apply_flag,'N') = 'Y';
else -- No Asset record failed. Therefore update the status to Accepted for all records
update fa_mass_update_batch_details
set rejection_reason = null,
concurrent_request_id = px_trans_rec.mass_reference_id,
status_code = 'A',
last_updated_by = px_trans_rec.who_info.last_updated_by,
last_update_date = px_trans_rec.who_info.last_update_date,
last_update_login = px_trans_rec.who_info.last_update_login
where batch_id = p_batch_id
and status_code = 'IP' -- changed to 'IP'from ( 'P', 'R')- bugfix 1389275
and nvl(apply_flag,'N') = 'Y';
SELECT attribute_name,
attribute_old_id,
attribute_new_id,
derived_from_entity_id,
derived_from_entity,
status_code
FROM fa_mass_update_batch_details
WHERE batch_id = p_batch_id
and asset_id = p_asset_id
and book_type_code = p_book
AND apply_flag = 'Y'
AND STATUS_CODE in ('IP', 'P', 'R') -- added 'IP' as part of 138927
order by decode(attribute_name,
'LIFE_END_DATE',1,
'SERIAL_NUMBER',2,
'ASSET_KEY',3,
'LEASE_NUMBER',4,
'DISTRIBUTION',5,
'CATEGORY',6)
FOR UPDATE NOWAIT;
l_update_attribute varchar2(1):= 'N';
l_update_attribute := 'N';
update_category( p_asset_id,
to_number(l_get_lines_rec.attribute_old_id) ,
to_number(l_get_lines_rec.attribute_new_id),
x_err_code,l_err_stage,l_err_stack);
l_update_attribute := 'Y';
l_update_attribute := 'Y';
l_update_attribute := 'Y';
if l_update_attribute = 'Y' then
FA_ASSET_DESC_PUB.update_desc(
p_api_version => l_api_version,
p_init_msg_list => l_msg_list,
p_commit => l_commit_flag,
p_validation_level => l_validation_level,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_calling_fn => 'fa_cua_mass_update1_pkg.process_asset_batch',
px_trans_rec => px_trans_rec,
px_asset_hdr_rec => l_asset_hdr_rec,
px_asset_desc_rec_new => l_asset_desc_rec,
px_asset_cat_rec_new => l_asset_cat_rec);
update fa_mass_update_batch_details
set status_code = l_get_lines_rec.status_code
where batch_id = p_batch_id
and asset_id = p_asset_id
and book_type_code = p_book;