[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_last_updated_by number;
G_last_update_date date;
G_last_update_login number;
select pw.rowid,
pw.asset_id,
pw.asset_number,
pw.asset_type,
ad.asset_category_id,
ad.current_units,
rr.reval_percent,
rr.override_defaults_flag,
DECODE(rr.override_defaults_flag,
'YES', rr.reval_fully_rsvd_flag,
g_def_reval_fully_rsvd_flag),
DECODE(rr.override_defaults_flag,
'YES', rr.life_extension_factor,
g_def_life_extension_factor),
DECODE(rr.override_defaults_flag,
'YES', rr.life_extension_ceiling,
g_def_life_extension_ceiling),
DECODE(rr.override_defaults_flag,
'YES', rr.max_fully_rsvd_revals,
g_def_max_fully_rsvd_revals),
rr.attribute1,
rr.attribute2,
rr.attribute3,
rr.attribute4,
rr.attribute5,
rr.attribute6,
rr.attribute7,
rr.attribute8,
rr.attribute9,
rr.attribute10,
rr.attribute11,
rr.attribute12,
rr.attribute13,
rr.attribute14,
rr.attribute15,
rr.attribute_category_code,
rr.reval_attribute_category,
DECODE(rr.override_defaults_flag,
'YES', rr.revalue_cip_assets_flag,
g_def_revalue_cip_assets_flag)
FROM fa_parallel_workers pw,
fa_mass_revaluation_rules rr,
fa_additions_b ad
WHERE pw.request_id = p_parent_request_id
AND pw.worker_number = p_request_number
AND pw.process_status = 'UNPROCESSED'
AND pw.asset_category_id is null
AND rr.mass_reval_id = p_mass_reval_id
AND rr.asset_id = pw.asset_id
AND ad.asset_id = pw.asset_id;
select pw.rowid,
pw.asset_id,
pw.asset_number,
pw.asset_type,
ad.asset_category_id,
ad.current_units,
rr.reval_percent,
rr.override_defaults_flag,
DECODE(rr.override_defaults_flag,
'YES', rr.reval_fully_rsvd_flag,
g_def_reval_fully_rsvd_flag),
DECODE(rr.override_defaults_flag,
'YES', rr.life_extension_factor,
g_def_life_extension_factor),
DECODE(rr.override_defaults_flag,
'YES', rr.life_extension_ceiling,
g_def_life_extension_ceiling),
DECODE(rr.override_defaults_flag,
'YES', rr.max_fully_rsvd_revals,
g_def_max_fully_rsvd_revals),
rr.attribute1,
rr.attribute2,
rr.attribute3,
rr.attribute4,
rr.attribute5,
rr.attribute6,
rr.attribute7,
rr.attribute8,
rr.attribute9,
rr.attribute10,
rr.attribute11,
rr.attribute12,
rr.attribute13,
rr.attribute14,
rr.attribute15,
rr.attribute_category_code,
rr.reval_attribute_category,
DECODE(rr.override_defaults_flag,
'YES', rr.revalue_cip_assets_flag,
g_def_revalue_cip_assets_flag)
FROM fa_parallel_workers pw,
fa_mass_revaluation_rules rr,
fa_additions_b ad
WHERE pw.request_id = p_parent_request_id
AND pw.worker_number = p_request_number
AND pw.process_status = 'UNPROCESSED'
AND pw.asset_category_id is not null
AND rr.mass_reval_id = p_mass_reval_id
AND rr.category_id = pw.asset_category_id
AND ad.asset_id = pw.asset_id;
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.last_update_login := FND_GLOBAL.CONC_LOGIN_ID;
update fa_parallel_workers fpw
set process_status = l_process_status(i)
where rowid = l_rowid(i);
fa_debug_pkg.add(l_calling_fn, 'rows updated in fa_parallel_workers for status', l_rowid.count,
p_log_level_rec => g_log_level_rec);
fnd_msg_pub.delete_msg();
select mrvl.BOOK_TYPE_CODE ,
mrvl.DESCRIPTION ,
mrvl.REVAL_DATE ,
mrvl.DEFAULT_REVAL_FULLY_RSVD_FLAG ,
mrvl.DEFAULT_LIFE_EXTENSION_FACTOR ,
mrvl.DEFAULT_LIFE_EXTENSION_CEILING ,
mrvl.DEFAULT_MAX_FULLY_RSVD_REVALS ,
mrvl.STATUS ,
mrvl.LAST_REQUEST_ID ,
mrvl.ATTRIBUTE1 ,
mrvl.ATTRIBUTE2 ,
mrvl.ATTRIBUTE3 ,
mrvl.ATTRIBUTE4 ,
mrvl.ATTRIBUTE5 ,
mrvl.ATTRIBUTE6 ,
mrvl.ATTRIBUTE7 ,
mrvl.ATTRIBUTE8 ,
mrvl.ATTRIBUTE9 ,
mrvl.ATTRIBUTE10 ,
mrvl.ATTRIBUTE11 ,
mrvl.ATTRIBUTE12 ,
mrvl.ATTRIBUTE13 ,
mrvl.ATTRIBUTE14 ,
mrvl.ATTRIBUTE15 ,
mrvl.ATTRIBUTE_CATEGORY_CODE ,
mrvl.CREATED_BY ,
mrvl.CREATION_DATE ,
mrvl.LAST_UPDATED_BY ,
mrvl.LAST_UPDATE_DATE ,
mrvl.LAST_UPDATE_LOGIN ,
mrvl.GLOBAL_ATTRIBUTE1 ,
mrvl.GLOBAL_ATTRIBUTE2 ,
mrvl.GLOBAL_ATTRIBUTE3 ,
mrvl.GLOBAL_ATTRIBUTE4 ,
mrvl.GLOBAL_ATTRIBUTE5 ,
mrvl.GLOBAL_ATTRIBUTE6 ,
mrvl.GLOBAL_ATTRIBUTE7 ,
mrvl.GLOBAL_ATTRIBUTE8 ,
mrvl.GLOBAL_ATTRIBUTE9 ,
mrvl.GLOBAL_ATTRIBUTE10 ,
mrvl.GLOBAL_ATTRIBUTE11 ,
mrvl.GLOBAL_ATTRIBUTE12 ,
mrvl.GLOBAL_ATTRIBUTE13 ,
mrvl.GLOBAL_ATTRIBUTE14 ,
mrvl.GLOBAL_ATTRIBUTE15 ,
mrvl.GLOBAL_ATTRIBUTE16 ,
mrvl.GLOBAL_ATTRIBUTE17 ,
mrvl.GLOBAL_ATTRIBUTE18 ,
mrvl.GLOBAL_ATTRIBUTE19 ,
mrvl.GLOBAL_ATTRIBUTE20 ,
mrvl.GLOBAL_ATTRIBUTE_CATEGORY ,
mrvl.REVALUE_CIP_ASSETS_FLAG
from fa_mass_revaluations mrvl
where mrvl.mass_reval_id = p_mass_reval_id;
G_last_updated_by ,
G_last_update_date ,
G_last_update_login ,
G_global_attribute1 ,
G_global_attribute2 ,
G_global_attribute3 ,
G_global_attribute4 ,
G_global_attribute5 ,
G_global_attribute6 ,
G_global_attribute7 ,
G_global_attribute8 ,
G_global_attribute9 ,
G_global_attribute10 ,
G_global_attribute11 ,
G_global_attribute12 ,
G_global_attribute13 ,
G_global_attribute14 ,
G_global_attribute15 ,
G_global_attribute16 ,
G_global_attribute17 ,
G_global_attribute18 ,
G_global_attribute19 ,
G_global_attribute20 ,
G_global_attribute_category ,
G_def_revalue_cip_assets_flag ;
fa_debug_pkg.add(l_calling_fn, 'inserting initial transactions at', sysdate,
p_log_level_rec => g_log_level_rec);
fa_debug_pkg.add(l_calling_fn, 'rows inserted into fa_parallel_workers - asset based', sql%rowcount,
p_log_level_rec => g_log_level_rec);
insert into fa_parallel_workers
(request_id ,
asset_id ,
asset_number ,
asset_type ,
-- asset_category_id ,
book_type_code ,
worker_number ,
process_order ,
process_status )
select p_parent_request_id,
ad.asset_id,
ad.asset_number,
ad.asset_type,
-- ad.asset_category_id,
p_book_type_code,
mod(ad.asset_id, p_total_requests) + 1,
1,
'UNPROCESSED'
FROM fa_additions_b ad,
fa_books bk,
fa_mass_revaluation_rules rr
WHERE rr.mass_reval_id = p_mass_reval_id
AND rr.category_id IS NULL
AND rr.asset_id is not null
AND ad.asset_id = rr.asset_id
AND bk.asset_id = rr.asset_id
AND bk.book_type_code = G_book_type_code
AND bk.transaction_header_id_out IS NULL
AND bk.group_asset_id IS NULL
AND bk.period_counter_fully_retired IS NULL
AND NVL(bk.period_counter_fully_reserved, 99) = NVL(bk.
period_counter_life_complete, 99)
AND bk.conversion_date IS NULL
AND ad.asset_type = DECODE(NVL(rr.revalue_cip_assets_flag,
g_def_revalue_cip_assets_flag), NULL, 'CAPITALIZED', 'N', 'CAPITALIZED',
ad.asset_type)
AND ad.asset_type <> 'GROUP'
AND NOT EXISTS (SELECT 1
FROM fa_books oldbk
WHERE oldbk.asset_id = rr.asset_id
AND oldbk.book_type_code = G_book_type_code
AND oldbk.date_ineffective IS NOT NULL
AND oldbk.group_asset_id IS NOT NULL)
AND NOT EXISTS (SELECT 1
FROM fa_transaction_headers th_rev
WHERE th_rev.asset_id = rr.asset_id
AND th_rev.book_type_code = G_book_type_code
AND th_rev.transaction_type_code = 'REVALUATION'
AND th_rev.mass_transaction_id = p_mass_reval_id);
fa_debug_pkg.add(l_calling_fn, 'rows inserted into fa_parallel_workers2', sql%rowcount,
p_log_level_rec => g_log_level_rec);
insert into fa_parallel_workers
(request_id ,
asset_id ,
asset_number ,
asset_type ,
asset_category_id ,
book_type_code ,
worker_number ,
process_order ,
process_status )
select p_parent_request_id,
ad.asset_id,
ad.asset_number,
ad.asset_type,
ad.asset_category_id,
p_book_type_code,
mod(ad.asset_id, p_total_requests) + 1,
1,
'UNPROCESSED'
FROM fa_additions_b ad,
fa_books bk,
fa_mass_revaluation_rules rr
WHERE rr.mass_reval_id = p_mass_reval_id
AND rr.category_id = ad.asset_category_id
AND rr.category_id IS not NULL
AND rr.asset_id is null
AND bk.asset_id = ad.asset_id
AND bk.book_type_code = G_book_type_code
AND bk.transaction_header_id_out IS NULL
AND bk.group_asset_id IS NULL
AND bk.period_counter_fully_retired IS NULL
AND NVL(bk.period_counter_fully_reserved, 99) =
NVL(bk.period_counter_life_complete, 99)
AND bk.conversion_date IS NULL
AND ad.asset_type = DECODE(NVL(rr.revalue_cip_assets_flag,
g_def_revalue_cip_assets_flag),
NULL, 'CAPITALIZED',
'N', 'CAPITALIZED',
ad.asset_type)
AND ad.asset_type <> 'GROUP'
AND NOT EXISTS (SELECT 1
FROM fa_books oldbk
WHERE oldbk.asset_id = ad.asset_id
AND oldbk.book_type_code = G_book_type_code
AND oldbk.date_ineffective IS NOT NULL
AND oldbk.group_asset_id IS NOT NULL)
AND NOT EXISTS (SELECT 1
FROM fa_transaction_headers th_rev
WHERE th_rev.asset_id = ad.asset_id
AND th_rev.book_type_code = G_book_type_code
AND th_rev.transaction_type_code = 'REVALUATION'
AND th_rev.mass_transaction_id = p_mass_reval_id)
AND NOT EXISTS (SELECT 1
FROM fa_parallel_workers pw
WHERE pw.request_id = p_parent_request_id
AND pw.asset_id = ad.asset_id);