[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_last_updated_by number := FND_GLOBAL.USER_ID;
G_last_update_login number := FND_GLOBAL.CONC_LOGIN_ID;
SELECT fdh.assigned_to,
fdh.location_id,
gcc.segment1, gcc.segment2,
gcc.segment3, gcc.segment4,
gcc.segment5, gcc.segment6,
gcc.segment7, gcc.segment8,
gcc.segment9, gcc.segment10,
gcc.segment11, gcc.segment12,
gcc.segment13, gcc.segment14,
gcc.segment15, gcc.segment16,
gcc.segment17, gcc.segment18,
gcc.segment19, gcc.segment20,
gcc.segment21, gcc.segment22,
gcc.segment23, gcc.segment24,
gcc.segment25, gcc.segment26,
gcc.segment27, gcc.segment28,
gcc.segment29, gcc.segment30
FROM fa_distribution_history fdh,
gl_code_combinations gcc
WHERE fdh.asset_id = X_Asset_Id
AND fdh.code_combination_id = gcc.code_combination_id
AND fdh.date_ineffective IS NULL;
FUNCTION Insert_details( p_asset_id IN NUMBER,
p_units_assigned IN NUMBER,
p_code_combination_id IN NUMBER,
p_location_id IN NUMBER,
p_assigned_to IN NUMBER,
p_cost IN NUMBER,
p_current_units IN NUMBER
,p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
RETURN BOOLEAN IS
error varchar2(100);
insert into fa_mass_ext_retirements
(batch_name,
mass_external_retire_id,
book_type_code,
review_status,
asset_id,
calc_gain_loss_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
cost_retired,
cost_of_removal,
proceeds_of_sale,
retirement_type_code,
date_retired,
transaction_name,
units,
code_combination_id,
location_id,
assigned_to
)
VALUES
(
g_batch_name,
fa_mass_ext_retirements_s.nextval,
g_book_type_code,
'POST',
p_asset_id,
'YES', -- calc_gain_loss_flag
g_last_updated_by,
sysdate,
g_last_updated_by,
sysdate,
g_last_update_login,
((p_units_assigned / p_current_units) * p_cost),
0,
0,
G_retirement_type_code,
G_retirement_date,
G_transaction_name,
p_units_assigned,
p_code_combination_id,
p_location_id,
p_assigned_to);
'Insert_Details',
error,
''
,p_log_level_rec => p_log_level_rec);
END Insert_details;
SELECT fad.code_combination_id,
fad.location_id,
fad.assigned_to,
fad.units_assigned
FROM fa_distribution_history fad,
gl_code_combinations gcc
WHERE fad.asset_id = x_asset_id
AND fad.date_ineffective IS NULL
AND fad.code_combination_id = gcc.code_combination_id
AND (fad.assigned_to = G_Employee_Id
OR G_Employee_Id IS NULL)
AND (fad.location_id = G_Location_Id
OR G_Location_Id IS NULL)
AND ((gcc.segment1 BETWEEN G_Segment1_Low
AND G_Segment1_High
OR G_Segment1_Low IS NULL)
AND (gcc.segment2 BETWEEN G_Segment2_Low
AND G_Segment2_High
OR G_Segment2_Low IS NULL)
AND (gcc.segment3 BETWEEN G_Segment3_Low
AND G_Segment3_High
OR G_Segment3_Low IS NULL)
AND (gcc.segment4 BETWEEN G_Segment4_Low
AND G_Segment4_High
OR G_Segment4_Low IS NULL)
AND (gcc.segment5 BETWEEN G_Segment5_Low
AND G_Segment5_High
OR G_Segment5_Low IS NULL)
AND (gcc.segment6 BETWEEN G_Segment6_Low
AND G_Segment6_High
OR G_Segment6_Low IS NULL)
AND (gcc.segment7 BETWEEN G_Segment7_Low
AND G_Segment7_High
OR G_Segment7_Low IS NULL)
AND (gcc.segment8 BETWEEN G_Segment8_Low
AND G_Segment8_High
OR G_Segment8_Low IS NULL)
AND (gcc.segment9 BETWEEN G_Segment9_Low
AND G_Segment9_High
OR G_Segment9_Low IS NULL)
AND (gcc.segment10 BETWEEN G_Segment10_Low
AND G_Segment10_High
OR G_Segment10_Low IS NULL)
AND (gcc.segment11 BETWEEN G_Segment11_Low
AND G_Segment11_High
OR G_Segment11_Low IS NULL)
AND (gcc.segment12 BETWEEN G_Segment12_Low
AND G_Segment12_High
OR G_Segment12_Low IS NULL)
AND (gcc.segment13 BETWEEN G_Segment13_Low
AND G_Segment13_High
OR G_Segment13_Low IS NULL)
AND (gcc.segment14 BETWEEN G_Segment14_Low
AND G_Segment14_High
OR G_Segment14_Low IS NULL)
AND (gcc.segment15 BETWEEN G_Segment15_Low
AND G_Segment15_High
OR G_Segment15_Low IS NULL)
AND (gcc.segment16 BETWEEN G_Segment16_Low
AND G_Segment16_High
OR G_Segment16_Low IS NULL)
AND (gcc.segment17 BETWEEN G_Segment17_Low
AND G_Segment17_High
OR G_Segment17_Low IS NULL)
AND (gcc.segment18 BETWEEN G_Segment18_Low
AND G_Segment18_High
OR G_Segment18_Low IS NULL)
AND (gcc.segment19 BETWEEN G_Segment19_Low
AND G_Segment19_High
OR G_segment19_Low IS NULL)
AND (gcc.segment20 BETWEEN G_Segment20_Low
AND G_Segment20_High
OR G_segment20_Low IS NULL)
AND (gcc.segment21 BETWEEN G_Segment21_Low
AND G_Segment21_High
OR G_segment21_Low IS NULL)
AND (gcc.segment22 BETWEEN G_Segment22_Low
AND G_Segment22_High
OR G_segment22_Low IS NULL)
AND (gcc.segment23 BETWEEN G_Segment23_Low
AND G_Segment23_High
OR G_segment23_Low IS NULL)
AND (gcc.segment24 BETWEEN G_Segment24_Low
AND G_Segment24_High
OR G_segment24_Low IS NULL)
AND (gcc.segment25 BETWEEN G_Segment25_Low
AND G_Segment25_High
OR G_segment25_Low IS NULL)
AND (gcc.segment26 BETWEEN G_Segment26_Low
AND G_Segment26_High
OR G_segment26_Low IS NULL)
AND (gcc.segment27 BETWEEN G_Segment27_Low
AND G_Segment27_High
OR G_segment27_Low IS NULL)
AND (gcc.segment28 BETWEEN G_Segment28_Low
AND G_Segment28_High
OR G_segment28_Low IS NULL)
And (gcc.segment29 BETWEEN G_Segment29_Low
AND G_Segment29_High
OR G_segment29_Low IS NULL)
AND (gcc.segment30 BETWEEN G_Segment30_Low
AND G_Segment30_High
OR G_segment30_Low IS NULL)
);
if (p_message = 'FA_SHARED_INSERT_DEBUG') then
fnd_message.set_token('TABLE', 'retirement batch');
if p_message <> 'FA_SHARED_INSERT_DEBUG' then
if p_message = 'FA_MASSRET_NOT_ENOUGH_UNITS' then
fa_srvr_msg.add_message
(calling_fn => l_calling_fn,
name => p_message,
token1 => 'UNITS',
value1 => p_token1,
token2 => 'TOTALUNITS',
value2 => p_token2
,p_log_level_rec => p_log_level_rec);
select 'FA_SHARED_PENDING_RETIREMENT'
from fa_retirements frt
where frt.asset_id = p_Asset_Id
AND frt.book_type_code = G_Book_Type_Code
AND frt.status IN ('PENDING','REINSTATE');
select 'FA_REC_RETIRED'
from fa_retirements frt,
fa_books bk
where frt.asset_id = p_Asset_Id
AND bk.asset_id = frt.asset_id
AND bk.period_counter_fully_retired is NOT NULL
AND bk.transaction_header_id_in =
frt.transaction_header_id_in
AND bk.date_ineffective is null
AND frt.transaction_header_id_out is NULL
AND frt.status = 'PROCESSED'
AND frt.book_type_code = G_Book_Type_Code
AND bk.book_type_code = frt.book_type_code;
select 'FA_RET_CANT_RET_NONDEPRN'
from fa_transaction_headers th,
fa_book_controls bc,
fa_deprn_periods dp
where th.asset_id = p_Asset_id
AND th.book_type_code = G_Book_Type_Code
AND bc.book_type_code = th.book_type_code
AND th.transaction_type_code||''
= decode(bc.book_class,'CORPORATE','TRANSFER IN',
'ADDITION')
AND th.date_effective
BETWEEN dp.period_open_date
AND nvl(dp.period_close_date,sysdate)
AND dp.book_type_code = th.book_type_code
AND dp.period_close_date is NULL;
select 'FA_RET_CANT_RET_NONDEPRN'
from FA_BOOKS fab,
FA_DEPRN_PERIODS dp,
FA_TRANSACTION_HEADERS th
where
fab.transaction_header_id_in = th.transaction_header_id
and fab.book_type_code = G_Book_Type_Code
and fab.asset_id = p_Asset_id
and fab.book_type_code = dp.book_type_code
and fab.period_counter_capitalized = dp.period_counter
and th.date_effective between dp.period_open_date and
NVL(dp.period_close_date, sysdate)
and dp.period_close_date is NULL;
select 'FA_SHARED_OTHER_TRX'
from fa_transaction_headers fth
where fth.asset_id = p_Asset_id
and fth.book_type_code = G_Book_Type_Code
and (fth.transaction_date_entered > G_Retirement_Date
and fth.transaction_type_code in ('TAX', 'REVALUATION'));
l_check := 'Not inserted';
l_check := 'Already inserted';
if l_check <> 'Already inserted' then
p_num_msg := p_num_msg + 1;
select 'EXTENDED LIFE'
from fa_books bk,
fa_deprn_periods dp
where bk.asset_id = X_Asset_Id
AND bk.book_type_code = G_Book_Type_Code
AND nvl(period_Counter_fully_reserved,99) <> bk.period_counter_life_complete
AND dp.book_type_code = bk.book_type_code
AND bk.period_counter_life_complete is not NULL
AND bk.date_ineffective is null
AND dp.period_close_date is null
AND G_Retirement_Date < dp.calendar_period_open_date;
Select ad.asset_id,
ad.asset_number,
ad.current_units,
bk.cost,
mer.units mer_units,
mer.mass_external_retire_id,
mer.code_combination_id,
mer.location_id,
mer.assigned_to
From fa_mass_ext_retirements mer,
fa_books bk,
fa_additions ad
Where mer.batch_name = G_batch_name -- current batch
And mer.asset_id = bk.asset_id
And mer.book_type_code = bk.book_type_code
And bk.date_ineffective is null
And bk.date_placed_in_service
between nvl(temp_from_dpis, bk.date_placed_in_service -1)
and nvl(temp_to_dpis, bk.date_placed_in_service +1)
and ad.asset_id = mer.asset_id
order by bk.date_placed_in_service, bk.asset_id;
SELECT
faa.asset_id,
faa.asset_number,
fab.date_placed_in_service,
fab.cost,
faa.current_units
FROM fa_book_controls fbc,
fa_books fab,
fa_additions_b faa
WHERE faa.asset_id = fab.asset_id
AND (faa.asset_key_ccid = G_Asset_Key_Id
OR G_Asset_Key_Id IS NULL)
AND faa.asset_category_id = nvl(G_Category_Id,faa.asset_category_id)
AND fab.cost >= nvl(G_From_Cost,fab.cost)
AND fab.cost <= nvl(G_To_Cost,fab.cost)
AND ((G_group_asset_id = -1 and -- group change
fab.group_asset_id is null) OR -- group change
(G_group_asset_id = -99) OR -- group change
(G_group_asset_id > 0 and -- group change
nvl(fab.group_asset_id, -999) = g_group_asset_id)) -- group change
AND nvl(fab.period_counter_fully_reserved,-99999) =
decode(G_Fully_Rsvd_Flag,
'YES',fab.period_counter_fully_reserved,
'NO',-99999,
nvl(fab.period_counter_fully_reserved,-99999))
AND faa.asset_number >=
nvl(G_From_Asset_Number, faa.asset_number)
AND faa.asset_number <=
nvl(G_To_Asset_Number, faa.asset_number)
AND fab.date_placed_in_service
NOT BETWEEN nvl(Temp_From_DPIS,fab.date_placed_in_service-1)
AND nvl(Temp_To_DPIS ,fab.date_placed_in_service+1)
AND (faa.model_number = G_model_number
OR G_model_number IS NULL)
AND (faa.serial_number = G_serial_number
OR G_serial_number IS NULL)
AND (faa.tag_number = G_tag_number
OR G_tag_number IS NULL)
AND (faa.manufacturer_name = G_manufacturer_name
OR G_manufacturer_name IS NULL)
AND fab.book_type_code = fbc.book_type_code
AND fbc.date_ineffective is null
AND EXISTS (SELECT null
FROM fa_distribution_history fad,
gl_code_combinations gcc
WHERE fad.asset_id = faa.asset_id
AND fad.code_combination_id = gcc.code_combination_id
AND (fad.assigned_to = G_Employee_Id
OR G_Employee_Id IS NULL)
AND (fad.location_id = G_Location_Id
OR G_Location_Id IS NULL)
AND fad.date_ineffective IS NULL
AND (gcc.segment1 BETWEEN G_Segment1_Low
AND G_Segment1_High
OR G_Segment1_Low IS NULL)
AND (gcc.segment2 BETWEEN G_Segment2_Low
AND G_Segment2_High
OR G_Segment2_Low IS NULL)
AND (gcc.segment3 BETWEEN G_Segment3_Low
AND G_Segment3_High
OR G_Segment3_Low IS NULL)
AND (gcc.segment4 BETWEEN G_Segment4_Low
AND G_Segment4_High
OR G_Segment4_Low IS NULL)
AND (gcc.segment5 BETWEEN G_Segment5_Low
AND G_Segment5_High
OR G_Segment5_Low IS NULL)
AND (gcc.segment6 BETWEEN G_Segment6_Low
AND G_Segment6_High
OR G_Segment6_Low IS NULL)
AND (gcc.segment7 BETWEEN G_Segment7_Low
AND G_Segment7_High
OR G_Segment7_Low IS NULL)
AND (gcc.segment8 BETWEEN G_Segment8_Low
AND G_Segment8_High
OR G_Segment8_Low IS NULL)
AND (gcc.segment9 BETWEEN G_Segment9_Low
AND G_Segment9_High
OR G_Segment9_Low IS NULL)
AND (gcc.segment10 BETWEEN G_Segment10_Low
AND G_Segment10_High
OR G_Segment10_Low IS NULL)
AND (gcc.segment11 BETWEEN G_Segment11_Low
AND G_Segment11_High
OR G_Segment11_Low IS NULL)
AND (gcc.segment12 BETWEEN G_Segment12_Low
AND G_Segment12_High
OR G_Segment12_Low IS NULL)
AND (gcc.segment13 BETWEEN G_Segment13_Low
AND G_Segment13_High
OR G_Segment13_Low IS NULL)
AND (gcc.segment14 BETWEEN G_Segment14_Low
AND G_Segment14_High
OR G_Segment14_Low IS NULL)
AND (gcc.segment15 BETWEEN G_Segment15_Low
AND G_Segment15_High
OR G_Segment15_Low IS NULL)
AND (gcc.segment16 BETWEEN G_Segment16_Low
AND G_Segment16_High
OR G_Segment16_Low IS NULL)
AND (gcc.segment17 BETWEEN G_Segment17_Low
AND G_Segment17_High
OR G_Segment17_Low IS NULL)
AND (gcc.segment18 BETWEEN G_Segment18_Low
AND G_Segment18_High
OR G_Segment18_Low IS NULL)
AND (gcc.segment19 BETWEEN G_Segment19_Low
AND G_Segment19_High
OR G_segment19_Low IS NULL)
AND (gcc.segment20 BETWEEN G_Segment20_Low
AND G_Segment20_High
OR G_segment20_Low IS NULL)
AND (gcc.segment21 BETWEEN G_Segment21_Low
AND G_Segment21_High
OR G_segment21_Low IS NULL)
AND (gcc.segment22 BETWEEN G_Segment22_Low
AND G_Segment22_High
OR G_segment22_Low IS NULL)
AND (gcc.segment23 BETWEEN G_Segment23_Low
AND G_Segment23_High
OR G_segment23_Low IS NULL)
AND (gcc.segment24 BETWEEN G_Segment24_Low
AND G_Segment24_High
OR G_segment24_Low IS NULL)
AND (gcc.segment25 BETWEEN G_Segment25_Low
AND G_Segment25_High
OR G_segment25_Low IS NULL)
AND (gcc.segment26 BETWEEN G_Segment26_Low
AND G_Segment26_High
OR G_segment26_Low IS NULL)
AND (gcc.segment27 BETWEEN G_Segment27_Low
AND G_Segment27_High
OR G_segment27_Low IS NULL)
AND (gcc.segment28 BETWEEN G_Segment28_Low
AND G_Segment28_High
OR G_segment28_Low IS NULL)
And (gcc.segment29 BETWEEN G_Segment29_Low
AND G_Segment29_High
OR G_segment29_Low IS NULL)
AND (gcc.segment30 BETWEEN G_Segment30_Low
AND G_Segment30_High
OR G_segment30_Low IS NULL))
AND (faa.asset_type = G_Asset_Type OR G_Asset_Type IS NULL)
AND faa.asset_type IN ('CIP','CAPITALIZED','EXPENSED')
AND fbc.book_type_code = G_Book_Type_Code
AND fab.date_ineffective IS NULL
ORDER BY fab.date_placed_in_service;
select units
from fa_mass_ext_retirements
where book_type_code = G_book_type_code
and asset_id = l_asset_id
and review_status = 'POST'
and units is not null
and batch_name <> G_batch_name;
select *
from fa_distribution_history dh
where dh.asset_id = l_asset_id
and dh.location_id = nvl(aurec.location_id, dh.location_id)
and dh.code_combination_id = nvl(aurec.code_combination_id, dh.code_combination_id)
and nvl(dh.assigned_to, -9999) = nvl(aurec.assigned_to, -9999)
and dh.date_ineffective is null
order by distribution_id;
msg_tbl.delete;
Update fa_mass_ext_retirements
Set review_status = 'POST',
calc_gain_loss_flag = 'YES'
Where mass_external_retire_id = aurec.mass_external_retire_id;
select nvl(sum(units),0)
into l_asset_post_units
from fa_mass_ext_retirements
where book_type_code = g_book_type_code
and asset_id = l_asset_id
and review_status = 'POST'
and batch_name <> g_batch_name;
select nvl(sum(units),0)
into l_dist_post_units
from fa_mass_ext_retirements
where book_type_code = g_book_type_code
and asset_id = l_asset_id
and review_status = 'POST'
and batch_name <> g_batch_name
and code_combination_id= dhrec.code_combination_id
and location_id = dhrec.location_id
and nvl(assigned_to,-99) = nvl(dhrec.assigned_to,-99);
if not insert_details(
aurec.asset_id,
l_dist_diff,
dhrec.code_combination_id,
dhrec.location_id,
dhrec.assigned_to,
aurec.cost,
aurec.current_units
,p_log_level_rec => p_log_level_rec) then
raise dml_error;
fa_debug_pkg.add(l_calling_fn,'insert details A',
l_dist_diff
,p_log_level_rec => p_log_level_rec);
if not insert_details(
aurec.asset_id,
l_dist_diff,
dhrec.code_combination_id,
dhrec.location_id,
dhrec.assigned_to,
aurec.cost,
aurec.current_units
,p_log_level_rec => p_log_level_rec) then
raise dml_error;
fa_debug_pkg.add(l_calling_fn,'insert details B',
l_dist_diff
,p_log_level_rec => p_log_level_rec);
select nvl(sum(units),0)
into l_dist_post_units
from fa_mass_ext_retirements
where book_type_code = g_book_type_code
and asset_id = l_asset_id
and review_status = 'POST'
and batch_name <> g_batch_name
and code_combination_id =
aurec.code_combination_id
and location_id =
aurec.location_id
and nvl(assigned_to,-99) =
nvl(aurec.assigned_to,-99);
if not insert_details(
aurec.asset_id,
l_remaining_units,
aurec.code_combination_id,
aurec.location_id,
aurec.assigned_to,
aurec.cost,
aurec.current_units
,p_log_level_rec => p_log_level_rec) then
raise dml_error;
if not insert_details(
aurec.asset_id,
aurec.mer_units - l_dist_post_units,
aurec.code_combination_id,
aurec.location_id,
aurec.assigned_to,
aurec.cost,
aurec.current_units
,p_log_level_rec => p_log_level_rec) then
raise dml_error;
fa_debug_pkg.add(l_calling_fn,'insert details C',
l_slask
,p_log_level_rec => p_log_level_rec);
'Before Insert details B, units_assigned',
dhrec.units_assigned
,p_log_level_rec => p_log_level_rec);
-- insert partial unit retirement
if not insert_details(
aurec.asset_id,
l_remaining_units,
dhrec.code_combination_id,
dhrec.location_id,
dhrec.assigned_to,
aurec.cost,
aurec.current_units
,p_log_level_rec => p_log_level_rec) then
raise dml_error;
fa_debug_pkg.add(l_calling_fn,'insert details D',
l_remaining_units
,p_log_level_rec => p_log_level_rec);
'Before Insert details C, units_assigned',
dhrec.units_assigned
,p_log_level_rec => p_log_level_rec);
if not insert_details(
aurec.asset_id,
dhrec.units_assigned,
dhrec.code_combination_id,
dhrec.location_id,
dhrec.assigned_to,
aurec.cost,
aurec.current_units
,p_log_level_rec => p_log_level_rec) then
raise dml_error;
fa_debug_pkg.add(l_calling_fn,'insert details E',
dhrec.units_assigned
,p_log_level_rec => p_log_level_rec);
select nvl(sum(units),0)
into l_asset_post_units
from fa_mass_ext_retirements
where book_type_code = g_book_type_code
and asset_id = l_asset_id
and review_status = 'POST'
and batch_name <> g_batch_name;
select nvl(sum(units),0)
into l_dist_post_units
from fa_mass_ext_retirements
where book_type_code = g_book_type_code
and asset_id = l_asset_id
and review_status = 'POST'
and batch_name <> g_batch_name
and code_combination_id= dhrec.code_combination_id
and location_id = dhrec.location_id
and nvl(assigned_to,-99) = nvl(dhrec.assigned_to,-99);
if not insert_details(
aurec.asset_id,
l_dist_diff,
dhrec.code_combination_id,
dhrec.location_id,
dhrec.assigned_to,
aurec.cost,
aurec.current_units
,p_log_level_rec => p_log_level_rec) then
raise dml_error;
fa_debug_pkg.add(l_calling_fn,'insert details F',
l_dist_diff
,p_log_level_rec => p_log_level_rec);
if not insert_details(
aurec.asset_id,
l_remaining_units,
dhrec.code_combination_id,
dhrec.location_id,
dhrec.assigned_to,
aurec.cost,
aurec.current_units
,p_log_level_rec => p_log_level_rec) then
raise dml_error;
fa_debug_pkg.add(l_calling_fn,'insert details FF',
l_remaining_units
,p_log_level_rec => p_log_level_rec);
if not insert_details(
aurec.asset_id,
l_dist_diff,
dhrec.code_combination_id,
dhrec.location_id,
dhrec.assigned_to,
aurec.cost,
aurec.current_units
,p_log_level_rec => p_log_level_rec) then
raise dml_error;
fa_debug_pkg.add(l_calling_fn,'insert details G',
l_dist_diff
,p_log_level_rec => p_log_level_rec);
if not insert_details(
aurec.asset_id,
l_remaining_units,
dhrec.code_combination_id,
dhrec.location_id,
dhrec.assigned_to,
aurec.cost,
aurec.current_units
,p_log_level_rec => p_log_level_rec) then
raise dml_error;
fa_debug_pkg.add(l_calling_fn,'insert details GG',
l_remaining_units
,p_log_level_rec => p_log_level_rec);
select nvl(sum(units),0)
into l_dist_post_units
from fa_mass_ext_retirements
where book_type_code = g_book_type_code
and asset_id = l_asset_id
and review_status = 'POST'
and batch_name <> g_batch_name
and code_combination_id =
nvl(aurec.code_combination_id,code_combination_id)
and location_id =
nvl(aurec.location_id, location_id)
and nvl(assigned_to,-99) =
nvl(aurec.assigned_to,-99);
if not insert_details(
aurec.asset_id,
l_remaining_units,
aurec.code_combination_id,
aurec.location_id,
aurec.assigned_to,
aurec.cost,
aurec.current_units
,p_log_level_rec => p_log_level_rec) then
raise dml_error;
if not insert_details(
aurec.asset_id,
aurec.mer_units - l_dist_post_units,
aurec.code_combination_id,
aurec.location_id,
aurec.assigned_to,
aurec.cost,
aurec.current_units
,p_log_level_rec => p_log_level_rec) then
raise dml_error;
fa_debug_pkg.add(l_calling_fn,'insert details H',
l_slask
,p_log_level_rec => p_log_level_rec);
l_asset_dist_tbl.delete;
fa_debug_pkg.add(l_calling_fn, 'Before Insert details A, units',
l_asset_dist_tbl(l_dist_count).units_assigned);
if not insert_details(
l_2nd_asset_id,
l_asset_dist_tbl(l_dist_count).units_assigned,
l_asset_dist_tbl(l_dist_count).expense_ccid,
l_asset_dist_tbl(l_dist_count).location_ccid,
l_asset_dist_tbl(l_dist_count).assigned_to,
l_2nd_cost_retired,
l_asset_dist_tbl(l_dist_count).units_assigned) then
raise dml_error;
fa_debug_pkg.add(l_calling_fn,'insert details I',
l_asset_dist_tbl(l_dist_count).units_assigned);
select nvl(sum(units),0)
into l_asset_post_units
from fa_mass_ext_retirements
where book_type_code = g_book_type_code
and asset_id = l_asset_id
and review_status = 'POST'
and batch_name <> g_batch_name;
select nvl(sum(units),0)
into l_dist_post_units
from fa_mass_ext_retirements
where book_type_code = g_book_type_code
and asset_id = l_asset_id
and review_status = 'POST'
and batch_name <> g_batch_name
and code_combination_id= dhrec.code_combination_id
and location_id = dhrec.location_id
and nvl(assigned_to,-99) = nvl(dhrec.assigned_to,-99);
if not insert_details(
l_2nd_asset_id,
l_dist_diff,
dhrec.code_combination_id,
dhrec.location_id,
dhrec.assigned_to,
l_2nd_cost_retired,
l_2nd_current_units
,p_log_level_rec => p_log_level_rec) then
raise dml_error;
fa_debug_pkg.add(l_calling_fn,'insert details J',l_dist_diff
,p_log_level_rec => p_log_level_rec);
if not insert_details(
l_2nd_asset_id,
l_dist_diff,
dhrec.code_combination_id,
dhrec.location_id,
dhrec.assigned_to,
l_2nd_cost_retired,
l_2nd_current_units
,p_log_level_rec => p_log_level_rec) then
raise dml_error;
fa_debug_pkg.add(l_calling_fn,'insert details K',l_dist_diff
,p_log_level_rec => p_log_level_rec);
select nvl(sum(units),0)
into l_dist_post_units
from fa_mass_ext_retirements
where book_type_code = g_book_type_code
and asset_id = l_asset_id
and review_status = 'POST'
and batch_name <> g_batch_name
and code_combination_id =
l_asset_dist_tbl(l_dist_count).expense_ccid
and location_id =
l_asset_dist_tbl(l_dist_count).location_ccid
and nvl(assigned_to,-99) =
nvl(l_asset_dist_tbl(l_dist_count).assigned_to,-99);
if not insert_details(
l_2nd_asset_id,
l_remaining_units,
l_asset_dist_tbl(l_dist_count).expense_ccid,
l_asset_dist_tbl(l_dist_count).location_ccid,
l_asset_dist_tbl(l_dist_count).assigned_to,
l_2nd_cost_retired,
l_2nd_current_units) then
raise dml_error;
if not insert_details(
l_2nd_asset_id,
l_asset_dist_tbl(l_dist_count).units_assigned - l_dist_post_units,
l_asset_dist_tbl(l_dist_count).expense_ccid,
l_asset_dist_tbl(l_dist_count).location_ccid,
l_asset_dist_tbl(l_dist_count).assigned_to,
l_2nd_cost_retired,
l_2nd_current_units) then
raise dml_error;
fa_debug_pkg.add(l_calling_fn,'insert details KK',l_slask
,p_log_level_rec => p_log_level_rec);
fa_debug_pkg.add(l_calling_fn, 'Before Insert details B, units',
l_asset_dist_tbl(l_dist_count).units_assigned);
-- insert partial unit retirement
if not insert_details(
l_2nd_asset_id,
l_remaining_units,
dhrec.code_combination_id,
dhrec.location_id,
dhrec.assigned_to,
l_2nd_cost_retired,
l_2nd_current_units
,p_log_level_rec => p_log_level_rec) then
raise dml_error;
fa_debug_pkg.add(l_calling_fn,'insert details L',l_remaining_units
,p_log_level_rec => p_log_level_rec);
fa_debug_pkg.add(l_calling_fn, 'Before Insert details C, units',
l_asset_dist_tbl(l_dist_count).units_assigned);
if not insert_details(
l_2nd_asset_id,
dhrec.units_assigned,
dhrec.code_combination_id,
dhrec.location_id,
dhrec.assigned_to,
l_2nd_cost_retired,
l_2nd_current_units
,p_log_level_rec => p_log_level_rec) then
raise dml_error;
fa_debug_pkg.add(l_calling_fn,'insert details M', dhrec.units_assigned
,p_log_level_rec => p_log_level_rec);
select nvl(sum(units),0)
into l_asset_post_units
from fa_mass_ext_retirements
where book_type_code = g_book_type_code
and asset_id = l_asset_id
and review_status = 'POST'
and batch_name <> g_batch_name;
select nvl(sum(units),0)
into l_dist_post_units
from fa_mass_ext_retirements
where book_type_code = g_book_type_code
and asset_id = l_asset_id
and review_status = 'POST'
and batch_name <> g_batch_name
and code_combination_id= dhrec.code_combination_id
and location_id = dhrec.location_id
and nvl(assigned_to,-99) = nvl(dhrec.assigned_to,-99);
if not insert_details(
l_2nd_asset_id,
l_dist_diff,
dhrec.code_combination_id,
dhrec.location_id,
dhrec.assigned_to,
l_2nd_cost_retired,
l_2nd_current_units
,p_log_level_rec => p_log_level_rec) then
raise dml_error;
fa_debug_pkg.add(l_calling_fn,'insert details M',l_dist_diff
,p_log_level_rec => p_log_level_rec);
if not insert_details(
l_2nd_asset_id,
l_remaining_units,
dhrec.code_combination_id,
dhrec.location_id,
dhrec.assigned_to,
l_2nd_cost_retired,
l_2nd_current_units
,p_log_level_rec => p_log_level_rec) then
raise dml_error;
fa_debug_pkg.add(l_calling_fn,'insert details O',l_dist_diff
,p_log_level_rec => p_log_level_rec);
if not insert_details(
l_2nd_asset_id,
l_dist_diff,
dhrec.code_combination_id,
dhrec.location_id,
dhrec.assigned_to,
l_2nd_cost_retired,
l_2nd_current_units
,p_log_level_rec => p_log_level_rec) then
raise dml_error;
fa_debug_pkg.add(l_calling_fn,'insert details O',l_dist_diff
,p_log_level_rec => p_log_level_rec);
if not insert_details(
l_2nd_asset_id,
l_remaining_units,
dhrec.code_combination_id,
dhrec.location_id,
dhrec.assigned_to,
l_2nd_cost_retired,
l_2nd_current_units
,p_log_level_rec => p_log_level_rec) then
raise dml_error;
fa_debug_pkg.add(l_calling_fn,'insert details O',l_dist_diff
,p_log_level_rec => p_log_level_rec);
select nvl(sum(units),0)
into l_dist_post_units
from fa_mass_ext_retirements
where book_type_code = g_book_type_code
and asset_id = l_asset_id
and review_status = 'POST'
and batch_name <> g_batch_name
and code_combination_id =
l_asset_dist_tbl(l_dist_count).expense_ccid
and location_id =
l_asset_dist_tbl(l_dist_count).location_ccid
and nvl(assigned_to,-99) =
nvl(l_asset_dist_tbl(l_dist_count).assigned_to,-99);
if not insert_details(
l_2nd_asset_id,
l_remaining_units,
l_asset_dist_tbl(l_dist_count).expense_ccid,
l_asset_dist_tbl(l_dist_count).location_ccid,
l_asset_dist_tbl(l_dist_count).assigned_to,
l_2nd_cost_retired,
l_2nd_current_units) then
raise dml_error;
if not insert_details(
l_2nd_asset_id,
l_asset_dist_tbl(l_dist_count).units_assigned - l_dist_post_units,
l_asset_dist_tbl(l_dist_count).expense_ccid,
l_asset_dist_tbl(l_dist_count).location_ccid,
l_asset_dist_tbl(l_dist_count).assigned_to,
l_2nd_cost_retired,
l_2nd_current_units) then
raise dml_error;
fa_debug_pkg.add(l_calling_fn,'insert details P',l_slask
,p_log_level_rec => p_log_level_rec);
-- Now Update status to the mass retirements batch
If l_remaining_units > 0 then -- not all units have been prorated. Abort.
if (g_log_level_rec.statement_level) then
fa_debug_pkg.add(l_calling_fn, 'Update status',l_remaining_units
,p_log_level_rec => p_log_level_rec);
Delete from fa_mass_ext_retirements
Where batch_name = g_batch_name;
Update fa_mass_retirements
Set status = 'ON_HOLD'
Where mass_retirement_id = G_Mass_Retirement_id;
msg_tbl.delete;
fa_debug_pkg.add(l_calling_fn, 'Update status <= 0 ',l_remaining_units
,p_log_level_rec => p_log_level_rec);
Delete from fa_mass_ext_retirements
Where batch_name = g_batch_name
And review_status = 'DELETE';
Write_Message(msg_tbl(i).asset_number,'FA_SHARED_INSERT_DEBUG',
'','');
Update fa_mass_retirements
Set status = 'CREATED_RET'
Where mass_retirement_id = G_Mass_Retirement_id;
SELECT fmr.mass_retirement_id,
fmr.book_type_code,
fmr.retirement_date,
substrb(fmr.description, 1, 30),
fmr.retire_subcomponents_flag,
fmr.status,
nvl(fmr.proceeds_of_sale,0),
nvl(fmr.cost_of_removal,0) ,
fmr.retirement_type_code,
fmr.asset_type,
fmr.location_id,
fmr.employee_id,
fmr.category_id,
fmr.asset_key_id,
fmr.from_asset_number,
fmr.to_asset_number,
fmr.from_date_placed_in_service,
fmr.to_date_placed_in_service,
fmr.model_number,
fmr.serial_number,
fmr.tag_number,
fmr.manufacturer_name,
fmr.units_to_retire,
fmr.attribute1,
fmr.attribute2,
fmr.attribute3, fmr.attribute4,
fmr.attribute5, fmr.attribute6,
fmr.attribute7, fmr.attribute8,
fmr.attribute9, fmr.attribute10,
fmr.attribute11, fmr.attribute12,
fmr.attribute13, fmr.attribute14,
fmr.attribute15, fmr.attribute_category_code,
fmr.segment1_low, fmr.segment2_low,
fmr.segment3_low, fmr.segment4_low,
fmr.segment5_low, fmr.segment6_low,
fmr.segment7_low, fmr.segment8_low,
fmr.segment9_low, fmr.segment10_low,
fmr.segment11_low, fmr.segment12_low,
fmr.segment13_low, fmr.segment14_low,
fmr.segment15_low, fmr.segment16_low,
fmr.segment17_low, fmr.segment18_low,
fmr.segment19_low, fmr.segment20_low,
fmr.segment21_low, fmr.segment22_low,
fmr.segment23_low, fmr.segment24_low,
fmr.segment25_low, fmr.segment26_low,
fmr.segment27_low, fmr.segment28_low,
fmr.segment29_low, fmr.segment30_low,
fmr.segment1_high, fmr.segment2_high,
fmr.segment3_high, fmr.segment4_high,
fmr.segment5_high, fmr.segment6_high,
fmr.segment7_high, fmr.segment8_high,
fmr.segment9_high, fmr.segment10_high,
fmr.segment11_high, fmr.segment12_high,
fmr.segment13_high, fmr.segment14_high,
fmr.segment15_high, fmr.segment16_high,
fmr.segment17_high, fmr.segment18_high,
fmr.segment19_high, fmr.segment20_high,
fmr.segment21_high, fmr.segment22_high,
fmr.segment23_high, fmr.segment24_high,
fmr.segment25_high, fmr.segment26_high,
fmr.segment27_high, fmr.segment28_high,
fmr.segment29_high, fmr.segment30_high,
sob.currency_code,
fbc.book_class,
fmr.from_cost,
fmr.to_cost,
fmr.include_fully_rsvd_flag,
fmr.group_asset_id, -- crl, no reason to break out
fmr.group_association
FROM fa_mass_retirements fmr,
fa_book_controls fbc,
gl_sets_of_books sob
WHERE fmr.mass_retirement_id = p_Mass_Retirement_Id
AND fmr.book_type_code = fbc.book_type_code
AND fbc.set_of_books_id = sob.set_of_books_id;
SELECT
faa.asset_id,
faa.asset_number,
fab.cost,
faa.current_units
FROM fa_book_controls fbc,
fa_books fab,
fa_additions_b faa
WHERE faa.asset_id = fab.asset_id
AND (faa.asset_key_ccid = G_Asset_Key_Id
OR G_Asset_Key_Id IS NULL)
AND faa.asset_category_id = nvl(G_Category_Id,faa.asset_category_id)
AND fab.cost >= nvl(G_From_Cost,fab.cost)
AND fab.cost <= nvl(G_To_Cost,fab.cost)
-- crl - no reason to make this conditional
AND ((G_group_asset_id = -1 and -- group change
fab.group_asset_id is null) OR -- group change
(G_group_asset_id = -99) OR -- group change
(G_group_asset_id > 0 and -- group change
nvl(fab.group_asset_id, -999) = g_group_asset_id)) -- group change
AND nvl(fab.period_counter_fully_reserved,-99999) =
decode(G_Fully_Rsvd_Flag,
'YES',fab.period_counter_fully_reserved,
'NO',-99999,
nvl(fab.period_counter_fully_reserved,-99999))
AND faa.asset_number >=
nvl(G_From_Asset_Number, faa.asset_number)
AND faa.asset_number <=
nvl(G_To_Asset_Number, faa.asset_number)
AND fab.date_placed_in_service
BETWEEN nvl(G_From_DPIS,fab.date_placed_in_service-1)
AND nvl(G_To_DPIS ,fab.date_placed_in_service+1)
AND (faa.model_number = G_model_number
OR G_model_number IS NULL)
AND (faa.serial_number = G_serial_number
OR G_serial_number IS NULL)
AND (faa.tag_number = G_tag_number
OR G_tag_number IS NULL)
AND (faa.manufacturer_name = G_manufacturer_name
OR G_manufacturer_name IS NULL)
AND fab.book_type_code = fbc.book_type_code
AND fbc.date_ineffective is null
AND EXISTS (SELECT null
FROM fa_distribution_history fad,
gl_code_combinations gcc
WHERE fad.asset_id = faa.asset_id
AND fad.code_combination_id = gcc.code_combination_id
AND (fad.assigned_to = G_Employee_Id
OR G_Employee_Id IS NULL)
AND (fad.location_id = G_Location_Id
OR G_Location_Id IS NULL)
AND fad.date_ineffective IS NULL
AND (gcc.segment1 BETWEEN G_Segment1_Low
AND G_Segment1_High
OR G_Segment1_Low IS NULL)
AND (gcc.segment2 BETWEEN G_Segment2_Low
AND G_Segment2_High
OR G_Segment2_Low IS NULL)
AND (gcc.segment3 BETWEEN G_Segment3_Low
AND G_Segment3_High
OR G_Segment3_Low IS NULL)
AND (gcc.segment4 BETWEEN G_Segment4_Low
AND G_Segment4_High
OR G_Segment4_Low IS NULL)
AND (gcc.segment5 BETWEEN G_Segment5_Low
AND G_Segment5_High
OR G_Segment5_Low IS NULL)
AND (gcc.segment6 BETWEEN G_Segment6_Low
AND G_Segment6_High
OR G_Segment6_Low IS NULL)
AND (gcc.segment7 BETWEEN G_Segment7_Low
AND G_Segment7_High
OR G_Segment7_Low IS NULL)
AND (gcc.segment8 BETWEEN G_Segment8_Low
AND G_Segment8_High
OR G_Segment8_Low IS NULL)
AND (gcc.segment9 BETWEEN G_Segment9_Low
AND G_Segment9_High
OR G_Segment9_Low IS NULL)
AND (gcc.segment10 BETWEEN G_Segment10_Low
AND G_Segment10_High
OR G_Segment10_Low IS NULL)
AND (gcc.segment11 BETWEEN G_Segment11_Low
AND G_Segment11_High
OR G_Segment11_Low IS NULL)
AND (gcc.segment12 BETWEEN G_Segment12_Low
AND G_Segment12_High
OR G_Segment12_Low IS NULL)
AND (gcc.segment13 BETWEEN G_Segment13_Low
AND G_Segment13_High
OR G_Segment13_Low IS NULL)
AND (gcc.segment14 BETWEEN G_Segment14_Low
AND G_Segment14_High
OR G_Segment14_Low IS NULL)
AND (gcc.segment15 BETWEEN G_Segment15_Low
AND G_Segment15_High
OR G_Segment15_Low IS NULL)
AND (gcc.segment16 BETWEEN G_Segment16_Low
AND G_Segment16_High
OR G_Segment16_Low IS NULL)
AND (gcc.segment17 BETWEEN G_Segment17_Low
AND G_Segment17_High
OR G_Segment17_Low IS NULL)
AND (gcc.segment18 BETWEEN G_Segment18_Low
AND G_Segment18_High
OR G_Segment18_Low IS NULL)
AND (gcc.segment19 BETWEEN G_Segment19_Low
AND G_Segment19_High
OR G_segment19_Low IS NULL)
AND (gcc.segment20 BETWEEN G_Segment20_Low
AND G_Segment20_High
OR G_segment20_Low IS NULL)
AND (gcc.segment21 BETWEEN G_Segment21_Low
AND G_Segment21_High
OR G_segment21_Low IS NULL)
AND (gcc.segment22 BETWEEN G_Segment22_Low
AND G_Segment22_High
OR G_segment22_Low IS NULL)
AND (gcc.segment23 BETWEEN G_Segment23_Low
AND G_Segment23_High
OR G_segment23_Low IS NULL)
AND (gcc.segment24 BETWEEN G_Segment24_Low
AND G_Segment24_High
OR G_segment24_Low IS NULL)
AND (gcc.segment25 BETWEEN G_Segment25_Low
AND G_Segment25_High
OR G_segment25_Low IS NULL)
AND (gcc.segment26 BETWEEN G_Segment26_Low
AND G_Segment26_High
OR G_segment26_Low IS NULL)
AND (gcc.segment27 BETWEEN G_Segment27_Low
AND G_Segment27_High
OR G_segment27_Low IS NULL)
AND (gcc.segment28 BETWEEN G_Segment28_Low
AND G_Segment28_High
OR G_segment28_Low IS NULL)
And (gcc.segment29 BETWEEN G_Segment29_Low
AND G_Segment29_High
OR G_segment29_Low IS NULL)
AND (gcc.segment30 BETWEEN G_Segment30_Low
AND G_Segment30_High
OR G_segment30_Low IS NULL))
AND (faa.asset_type = G_Asset_Type OR G_Asset_Type IS NULL)
AND faa.asset_type IN ('CIP','CAPITALIZED','EXPENSED')
-- YYOON 12/13/01: Performance Bug#2134816: Changed driving table from fa_books to fa_book_controls
AND fbc.book_type_code = G_Book_Type_Code
AND fab.date_ineffective IS NULL
ORDER BY fab.date_placed_in_service, faa.asset_number;
SELECT asset_id,
asset_number,
parent_asset_id,
current_units
FROM fa_additions_b
WHERE parent_asset_id is not null
AND parent_asset_id = p_parent_asset_id
MINUS
SELECT faa.asset_id, faa.asset_number, faa.parent_asset_id, faa.current_units
FROM fa_books fab,
fa_additions_b faa
WHERE faa.parent_asset_id is not null
AND faa.parent_asset_id = p_parent_asset_id
AND faa.asset_id = fab.asset_id
AND (faa.asset_key_ccid = G_Asset_Key_Id OR G_Asset_Key_Id IS NULL)
AND faa.asset_category_id = nvl(G_Category_Id,faa.asset_category_id)
AND fab.period_counter_fully_retired IS NULL
AND ((G_group_asset_id = -1 and -- group change
fab.group_asset_id is null) OR -- group change
(G_group_asset_id = -99) OR -- group change
(G_group_asset_id > 0 and -- group change
nvl(fab.group_asset_id, -999) = g_group_asset_id)) -- group change
AND faa.asset_number >=
nvl(G_From_Asset_Number, faa.asset_number)
AND faa.asset_number <=
nvl(G_To_Asset_Number, faa.asset_number)
AND fab.date_placed_in_service
BETWEEN nvl(G_From_DPIS,fab.date_placed_in_service-1)
AND nvl(G_To_DPIS ,fab.date_placed_in_service+1)
AND (faa.model_number = G_model_number
OR G_model_number IS NULL)
AND (faa.serial_number = G_serial_number
OR G_serial_number IS NULL)
AND (faa.tag_number = G_tag_number
OR G_tag_number IS NULL)
AND (faa.manufacturer_name = G_manufacturer_name
OR G_manufacturer_name IS NULL)
AND NOT EXISTS (SELECT null
FROM FA_TRANSACTION_HEADERS fth
WHERE fth.asset_id = fab.asset_id
AND fth.book_type_code = fab.book_type_code
AND (fth.transaction_date_entered > G_Retirement_Date and
fth.transaction_type_code not in ('FULL RETIREMENT','REINSTATEMENT')))
AND faa.asset_id in (SELECT faa2.asset_id
FROM fa_additions_b faa2,
gl_code_combinations gcc,
fa_distribution_history fad
WHERE fad.asset_id = faa2.asset_id
AND fad.code_combination_id = gcc.code_combination_id
AND (fad.assigned_to = G_Employee_Id
OR G_Employee_Id IS NULL)
AND (fad.location_id = G_Location_ID
OR G_Location_Id IS NULL)
AND fad.date_ineffective IS NULL
AND (gcc.segment1 BETWEEN G_Segment1_Low
AND G_Segment1_High
OR G_Segment1_Low IS NULL)
AND (gcc.segment2 BETWEEN G_Segment2_Low
AND G_Segment2_High
OR G_Segment2_Low IS NULL)
AND (gcc.segment3 BETWEEN G_Segment3_Low
AND G_Segment3_High
OR G_Segment3_Low IS NULL)
AND (gcc.segment4 BETWEEN G_Segment4_Low
AND G_Segment4_High
OR G_Segment4_Low IS NULL)
AND (gcc.segment5 BETWEEN G_Segment5_Low
AND G_Segment5_High
OR G_Segment5_Low IS NULL)
AND (gcc.segment6 BETWEEN G_Segment6_Low
AND G_Segment6_High
OR G_Segment6_Low IS NULL)
AND (gcc.segment7 BETWEEN G_Segment7_Low
AND G_Segment7_High
OR G_Segment7_Low IS NULL)
AND (gcc.segment8 BETWEEN G_Segment8_Low
AND G_Segment8_High
OR G_Segment8_Low IS NULL)
AND (gcc.segment9 BETWEEN G_Segment9_Low
AND G_Segment9_High
OR G_Segment9_Low IS NULL)
AND (gcc.segment10 BETWEEN G_Segment10_Low
AND G_Segment10_High
OR G_Segment10_Low IS NULL)
AND (gcc.segment11 BETWEEN G_Segment11_Low
AND G_Segment11_High
OR G_Segment11_Low IS NULL)
AND (gcc.segment12 BETWEEN G_Segment12_Low
AND G_Segment12_High
OR G_Segment12_Low IS NULL)
AND (gcc.segment13 BETWEEN G_Segment13_Low
AND G_Segment13_High
OR G_Segment13_Low IS NULL)
AND (gcc.segment14 BETWEEN G_Segment14_Low
AND G_Segment14_High
OR G_Segment14_Low IS NULL)
AND (gcc.segment15 BETWEEN G_Segment15_Low
AND G_Segment15_High
OR G_Segment15_Low IS NULL)
AND (gcc.segment16 BETWEEN G_Segment16_Low
AND G_Segment16_High
OR G_Segment16_Low IS NULL)
AND (gcc.segment17 BETWEEN G_Segment17_Low
AND G_Segment17_High
OR G_Segment17_Low IS NULL)
AND (gcc.segment18 BETWEEN G_Segment18_Low
AND G_Segment18_High
OR G_Segment18_Low IS NULL)
AND (gcc.segment19 BETWEEN G_Segment19_Low
AND G_Segment19_High
OR G_segment19_Low IS NULL)
AND (gcc.segment20 BETWEEN G_Segment20_Low
AND G_Segment20_High
OR G_segment20_Low IS NULL)
AND (gcc.segment21 BETWEEN G_Segment21_Low
AND G_Segment21_High
OR G_segment21_Low IS NULL)
AND (gcc.segment22 BETWEEN G_Segment22_Low
AND G_Segment22_High
OR G_segment22_Low IS NULL)
AND (gcc.segment23 BETWEEN G_Segment23_Low
AND G_Segment23_High
OR G_segment23_Low IS NULL)
AND (gcc.segment24 BETWEEN G_Segment24_Low
AND G_Segment24_High
OR G_segment24_Low IS NULL)
AND (gcc.segment25 BETWEEN G_Segment25_Low
AND G_Segment25_High
OR G_segment25_Low IS NULL)
AND (gcc.segment26 BETWEEN G_Segment26_Low
AND G_Segment26_High
OR G_segment26_Low IS NULL)
AND (gcc.segment27 BETWEEN G_Segment27_Low
AND G_Segment27_High
OR G_segment27_Low IS NULL)
AND (gcc.segment28 BETWEEN G_Segment28_Low
AND G_Segment28_High
OR G_segment28_Low IS NULL)
And (gcc.segment29 BETWEEN G_Segment29_Low
AND G_Segment29_High
OR G_segment29_Low IS NULL)
AND (gcc.segment30 BETWEEN G_Segment30_Low
AND G_Segment30_High
OR G_segment30_Low IS NULL))
AND (FAA.asset_type = G_Asset_Type OR G_Asset_Type IS NULL)
AND faa.asset_type IN ('CIP','CAPITALIZED','EXPENSED')
AND fab.book_type_code = G_Book_Type_Code
AND fab.date_ineffective IS NULL;
SELECT fab.cost
FROM fa_additions_b faa,
fa_books fab
WHERE faa.asset_id = fab.asset_id
AND faa.asset_id = p_asset_id
AND fab.period_counter_fully_retired IS NULL
AND faa.asset_type IN ('CIP','CAPITALIZED','EXPENSED')
AND fab.book_type_code = G_Book_Type_Code
And fab.date_ineffective IS NULL;
SELECT asset_id,
cost_retired
FROM fa_mass_ext_retirements
WHERE batch_name = p_batch_name;
delete from fa_mass_ext_retirements
where batch_name = g_batch_name;
SELECT Precision, Extended_precision, MINIMUM_ACCOUNTABLE_UNIT
INTO G_Precision, G_Ext_Precision, G_Min_Acct_Unit
FROM FND_CURRENCIES
WHERE Currency_code = G_Currency_Code;
Write_Message(l_sc_Asset_Number(l_loop_count_sub),'FA_SHARED_INSERT_DEBUG','','');
Write_Message(l_Asset_Number(l_loop_count),'FA_SHARED_INSERT_DEBUG','','');
l_asset_dist_tbl.delete;
'Before insert into fa_mass_ext_ret ccid',
l_asset_dist_tbl(l_dist_count).expense_ccid, p_log_level_rec => g_log_level_rec );
insert into fa_mass_ext_retirements
(batch_name,
mass_external_retire_id,
book_type_code,
review_status,
asset_id,
calc_gain_loss_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
cost_retired,
cost_of_removal,
proceeds_of_sale,
retirement_type_code,
date_retired,
transaction_name,
units,
code_combination_id,
location_id,
assigned_to,
attribute_category, --bug#7287382
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
th_attribute_category, --bug#7287382
th_attribute1,
th_attribute2,
th_attribute3,
th_attribute4,
th_attribute5,
th_attribute6,
th_attribute7,
th_attribute8,
th_attribute9,
th_attribute10,
th_attribute11,
th_attribute12,
th_attribute13,
th_attribute14,
th_attribute15
)
values
(g_batch_name,
fa_mass_ext_retirements_s.nextval,
G_book_type_code,
decode(nvl(g_units,0),'0','POST','DELETE'),
l_candidate_asset_id(l_count),
'NO',
g_last_updated_by,
sysdate,
g_last_updated_by,
sysdate,
g_last_update_login,
l_candidate_cost_retired(l_count),
0,
0,
G_retirement_type_code,
G_retirement_date,
G_transaction_name,
l_asset_dist_tbl(l_dist_count).units_assigned,
l_asset_dist_tbl(l_dist_count).expense_ccid,
l_asset_dist_tbl(l_dist_count).location_ccid,
l_asset_dist_tbl(l_dist_count).assigned_to,
g_attribute_category_code, --bug#7287382
g_attribute1,
g_attribute2,
g_attribute3,
g_attribute4,
g_attribute5,
g_attribute6,
g_attribute7,
g_attribute8,
g_attribute9,
g_attribute10,
g_attribute11,
g_attribute12,
g_attribute13,
g_attribute14,
g_attribute15,
g_attribute_category_code, --bug#7287382
g_attribute1,
g_attribute2,
g_attribute3,
g_attribute4,
g_attribute5,
g_attribute6,
g_attribute7,
g_attribute8,
g_attribute9,
g_attribute10,
g_attribute11,
g_attribute12,
g_attribute13,
g_attribute14,
g_attribute15);
'After insert into FA_MASS_EXT_RETIREMENTS table',
l_asset_dist_tbl(l_dist_count).units_assigned,p_log_level_rec => g_log_level_rec );
'After insert into FA_MASS_EXT_RETIREMENTS table',
l_candidate_units(l_count), p_log_level_rec => g_log_level_rec );
fa_debug_pkg.add(l_calling_fn, 'After dh-insert loop', '', p_log_level_rec => g_log_level_rec);
l_asset_dist_tbl.delete;
l_asset_id.delete;
l_asset_number.delete;
l_cost_retired.delete;
l_SC_Asset_Id.delete;
l_SC_Asset_Number.delete;
l_parent_asset_id.delete;
l_candidate_asset_id.delete;
l_candidate_cost_retired.delete;
l_candidate_units.delete;
select count(*),
sum( abs(cost_retired) )
into l_total_count_retired,
l_total_cost_retired
from fa_mass_ext_retirements
where batch_name = g_batch_name;
select count(*)
into l_msg_count
from fa_mass_ext_retirements
where batch_name = g_batch_name;
UPDATE fa_mass_ext_retirements
SET cost_of_removal = l_prorated_cost_of_removal(l_count),
proceeds_of_sale = l_prorated_proceeds_of_sale(l_count)
where asset_id = l_asset_id(l_count)
and batch_name = g_batch_name;
l_asset_id.delete;
l_cost_retired.delete;
l_prorated_cost_of_removal.delete;
l_prorated_proceeds_of_sale.delete;
if nvl(g_units,0) = 0 then -- status already inserted in allocate_units.
update fa_mass_retirements
set status = 'CREATED_RET'
where mass_retirement_id = G_Mass_Retirement_ID;
update fa_mass_retirements
set status = 'FAILED_CRE'
where mass_retirement_id = G_Mass_Retirement_ID;
update fa_mass_retirements
set status = 'FAILED_CRE'
where mass_retirement_id = G_Mass_Retirement_ID;