[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_Last_Update_Login NUMBER(15) := FND_GLOBAL.LOGIN_ID;
SELECT mer.Mass_External_Retire_Id,
mer.Book_Type_Code,
mer.Batch_Name,
mer.Asset_Id,
Mer.Transaction_Name,
mer.Date_Retired,
mer.Cost_Retired,
mer.Retirement_Prorate_Convention,
mer.Units,
nvl(mer.Cost_Of_Removal,0),
nvl(mer.Proceeds_Of_Sale,0),
mer.Retirement_Type_Code,
mer.Reference_Num,
mer.Sold_To,
mer.Trade_In_Asset_Id,
mer.calc_gain_loss_flag,
mer.Stl_Method_Code,
mer.Stl_Life_In_Months,
mer.Stl_Deprn_Amount,
mer.Last_Update_Login,
sysdate,
sob.currency_code,
fbc.set_of_books_id,
ad.current_units,
ad.asset_number,
bks.period_counter_fully_retired,
mer.distribution_id,
mer.code_combination_id,
mer.location_id,
mer.assigned_to,
mer.th_attribute_category,
mer.th_attribute1,
mer.th_attribute2,
mer.th_attribute3,
mer.th_attribute4,
mer.th_attribute5,
mer.th_attribute6,
mer.th_attribute7,
mer.th_attribute8,
mer.th_attribute9,
mer.th_attribute10,
mer.th_attribute11,
mer.th_attribute12,
mer.th_attribute13,
mer.th_attribute14,
mer.th_attribute15,
mer.attribute_category,
mer.attribute1,
mer.attribute2,
mer.attribute3,
mer.attribute4,
mer.attribute5,
mer.attribute6,
mer.attribute7,
mer.attribute8,
mer.attribute9,
mer.attribute10,
mer.attribute11,
mer.attribute12,
mer.attribute13,
mer.attribute14,
mer.attribute15
FROM fa_mass_Ext_retirements mer,
fa_books bks,
fa_additions_b ad,
fa_book_controls fbc,
gl_sets_of_books sob
WHERE mer.review_status = 'POST'
AND mer.book_type_code = fbc.book_type_code
AND fbc.set_of_books_id = sob.set_of_books_id
AND mer.batch_name = nvl(px_batch_name,mer.batch_name)
AND mer.book_type_code = P_Book_Type_Code
AND bks.book_type_code = mer.book_type_code
AND bks.asset_id = mer.asset_id
AND bks.date_ineffective is null
AND ad.asset_id = mer.asset_id
and mer.mass_external_retire_id > px_max_mass_ext_retire_id
and MOD(nvl(bks.group_asset_id,mer.asset_id), p_total_requests) = (p_request_number - 1)
order by mer.batch_name, mer.mass_external_retire_id;
L_Last_Update_Login num_tbl_type;
Select ext.*
from fa_ext_inv_retirements ext ,
fa_asset_invoices ai
where ext.mass_external_retire_id = L_Mass_External_Retire_ID
and ext.source_line_id = ai.source_line_id
and ai.date_ineffective is null
and ai.asset_id = G_Asset_Id;
select ext.*, ext.ROWID row_id
from fa_ext_inv_retirements ext,
fa_asset_invoices ai
where ext.mass_external_retire_id = G_Mass_External_Retire_ID
and ext.source_line_id = ai.source_line_id
and ai.asset_id = g_asset_id
and ext.source_line_id_retired is null;
select b.source_line_id
from fa_asset_invoices b
where b.source_line_id in (
select a.source_line_id
from fa_asset_invoices a
start with a.source_Line_id = p_src_line_id
connect by prior a.invoice_transaction_id_out = a.invoice_transaction_id_in )
and b.date_ineffective is null;
select 'Y'
from fa_lookups
where lookup_type = 'RETIREMENT'
and enabled_flag = 'Y'
and nvl(end_date_active,sysdate+1) > sysdate
and lookup_code = G_retirement_type_code;
select rowid,
distribution_id,
book_type_code,
asset_id,
units_assigned,
date_effective,
code_combination_id,
location_id,
transaction_header_id_in,
last_update_date,
last_updated_by,
date_ineffective,
assigned_to,
transaction_header_id_out,
transaction_units,
retirement_id,
last_update_login
from fa_distribution_history
where asset_id = G_asset_id
and code_combination_id = G_Code_Combination_id
and location_id = G_Location_id
and nvl(assigned_to,0) = nvl(G_Assigned_To,0)
and date_ineffective is null;
select
fc.precision
from gl_sets_of_books sob,
fa_book_controls fbc,
fnd_currencies fc
where fc.currency_code = sob.currency_code
and fc.enabled_flag = 'Y'
and fbc.book_type_code = p_book_type_code
and fbc.set_of_books_id = sob.set_of_books_id;
select nvl(count(*),0)
into g_test_ident_distributions
from fa_mass_ext_retirements
where batch_name = G_batch_name
and review_status = 'POST'
and asset_id = g_asset_id
group by code_combination_id, location_id, assigned_to
having count(*) > 1;
p_event varchar2(30) := 'INSERT';
'Before mass ext mainselect ',
'',g_log_level_rec);
L_Last_Update_Login ,
L_Today_Datetime ,
L_Currency_Code ,
L_Set_of_Books_Id ,
L_Current_Units ,
L_Asset_Number ,
L_Period_Counter_Fully_Retired ,
L_Distribution_Id ,
L_Code_combination_id ,
L_location_id ,
L_assigned_to ,
L_TH_Attribute_Category ,
L_TH_Attribute1 ,
L_TH_Attribute2 ,
L_TH_Attribute3 ,
L_TH_Attribute4 ,
L_TH_Attribute5 ,
L_TH_Attribute6 ,
L_TH_Attribute7 ,
L_TH_Attribute8 ,
L_TH_Attribute9 ,
L_TH_Attribute10 ,
L_TH_Attribute11 ,
L_TH_Attribute12 ,
L_TH_Attribute13 ,
L_TH_Attribute14 ,
L_TH_Attribute15 ,
L_Attribute_Category ,
L_Attribute1 ,
L_Attribute2 ,
L_Attribute3 ,
L_Attribute4 ,
L_Attribute5 ,
L_Attribute6 ,
L_Attribute7 ,
L_Attribute8 ,
L_Attribute9 ,
L_Attribute10 ,
L_Attribute11 ,
L_Attribute12 ,
L_Attribute13 ,
L_Attribute14 ,
L_Attribute15
LIMIT l_batch_size;
g_last_update_login := L_Last_Update_Login(i);
select count(*)
into g_num_of_distributions
from fa_mass_ext_retirements
where batch_name = G_batch_name
and review_status = 'POST'
and asset_id = g_asset_id
and code_combination_id is not null
and mass_external_retire_id <> g_mass_external_retire_id;
select count(*)
into g_num_of_identical
from fa_mass_ext_retirements
where batch_name = G_batch_name
and review_status = 'POST'
and asset_id = g_asset_id
and code_combination_id is not null;
l_asset_dist_tbl.delete;
l_inv_tbl.delete;
update fa_ext_inv_retirements
set source_line_id_retired = l_inv_rec.source_line_id
where source_line_id = l_inv_rec.source_line_id;
l_trans_rec.who_info.last_update_date := G_Today_Datetime;
l_trans_rec.who_info.last_updated_by := G_Created_By;
l_trans_rec.who_info.last_update_login := G_last_update_login;
l_dist_trans_rec.who_info.last_update_date := G_Today_Datetime;
l_dist_trans_rec.who_info.last_updated_by := G_Created_By;
l_dist_trans_rec.who_info.last_update_login := G_last_update_login;
l_asset_dist_tbl.delete;
l_subcomp_tbl.delete;
l_asset_dist_tbl.delete;
UPDATE fa_mass_Ext_retirements
SET review_status = 'POSTED',
-- bugfix 2442439 retirement_id = lv_ret_id
retirement_id = l_asset_retire_rec.retirement_id,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE mass_external_retire_id = G_Mass_External_Retire_Id;
UPDATE fa_mass_Ext_retirements
SET review_status = 'ERROR',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE mass_external_retire_id = G_Mass_External_Retire_Id;
UPDATE fa_mass_Ext_retirements
SET review_status = 'ERROR',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE mass_external_retire_id = G_Mass_External_Retire_Id;
UPDATE fa_mass_Ext_retirements
SET review_status = 'ERROR',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE mass_external_retire_id = G_Mass_External_Retire_Id;
UPDATE fa_mass_Ext_retirements
SET review_status = 'ERROR',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE mass_external_retire_id = G_Mass_External_Retire_Id;
UPDATE fa_mass_Ext_retirements
SET review_status = 'ERROR',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE batch_name = nvl(px_batch_name,batch_name)
AND book_type_code = p_book_type_code;
select Mass_External_Retire_ID
from fa_mass_ext_retirements
where review_status in ('DELETE','POSTED')
for update nowait;
Select count(*) into LV_Inv_Count
from fa_ext_inv_retirements
where mass_external_retire_id = LV_Mass_External_Retire_ID;
Delete from fa_ext_inv_retirements
where mass_external_retire_id = LV_Mass_External_Retire_ID;
Delete from fa_mass_ext_retirements
where mass_external_retire_id = LV_Mass_External_Retire_ID;