[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_asset_error1_tbl num_tbl; -- incremental used for bulk insert
select fpw.rowid,
fpw.asset_id,
fpw.asset_number,
fpw.asset_type,
fpw.transaction_type_code,
fpw.corp_transaction_header_id,
fpw.tax_transaction_header_id,
af.asset_id same_asset_id_fail
from fa_parallel_workers fpw,
fa_asset_failures_gt af
where fpw.request_id = p_parent_request_id
and fpw.process_status = 'UNASSIGNED'
and fpw.worker_number = p_request_number
and fpw.process_order = p_process_order
and af.asset_id(+) = fpw.asset_id
order by fpw.corp_transaction_header_id;
g_asset_error1_tbl.delete;
g_asset_error2_tbl.delete;
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);
insert into fa_asset_failures_gt (asset_id)
select distinct column_value
from TABLE(CAST(fa_asset_id_fail_tab AS fa_num15_tbl_type)) trx
minus
select asset_id
from fa_asset_failures_gt;
fa_debug_pkg.add(l_calling_fn, 'rows inserted into fa_asset_failures', g_asset_error1_tbl.count,
p_log_level_rec => g_log_level_rec);
select asset_category_id
into l_category_id
from fa_additions_b
where asset_id = p_asset_id;
select count(*)
into l_count
from fa_books corp_bk,
fa_deprn_periods dp,
fa_transaction_headers corp_th
where corp_bk.transaction_header_id_in = corp_th.transaction_header_id
and corp_bk.book_type_code = fa_cache_pkg.fazcbc_record.distribution_source_book
and corp_bk.asset_id = p_asset_id
and corp_bk.book_type_code = dp.book_type_code
and corp_bk.period_counter_capitalized = dp.period_counter
and corp_th.date_effective between
dp.period_open_date and nvl(dp.period_close_date, sysdate)
and corp_th.transaction_type_code like '%RETIREMENT';
select decode(p_asset_type,
'GROUP', 0,
cost),
date_placed_in_service,
group_asset_id,
salvage_type,
percent_salvage_value,
salvage_value
into l_asset_fin_rec.cost,
l_asset_fin_rec.date_placed_in_service,
l_asset_fin_rec.group_asset_id,
l_asset_fin_rec.salvage_type,
l_asset_fin_rec.percent_salvage_value,
l_asset_fin_rec.salvage_value
from fa_books
where asset_id = p_asset_id
and book_type_code = fa_cache_pkg.fazcbc_record.distribution_source_book
and transaction_header_id_in = p_corp_thid;
select corp_th.asset_id,
ad.asset_category_id,
corp_th.transaction_date_entered,
nvl(corp_th.transaction_subtype, 'EXPENSED'),
tax_bk.date_placed_in_service, -- changed as shouldn't this be tax for ccbd cache
nvl(ad.parent_asset_id, -1),
corp_bk_old.cost,
corp_bk_old.salvage_type,
corp_bk_old.salvage_value,
corp_bk_old.percent_salvage_value,
nvl(corp_bk_old.production_capacity, 0),
corp_bk_old.unrevalued_cost,
corp_bk_new.cost,
corp_bk_new.salvage_type,
corp_bk_new.salvage_value,
corp_bk_new.percent_salvage_value,
nvl(corp_bk_new.production_capacity, 0),
corp_bk_new.unrevalued_cost,
corp_bk_old.deprn_method_code,
corp_bk_new.deprn_method_code,
corp_bk_old.life_in_months,
corp_bk_new.life_in_months,
corp_bk_old.group_asset_id,
corp_bk_new.group_asset_id,
tax_bk.cost,
tax_bk.salvage_type,
tax_bk.salvage_value,
tax_bk.percent_salvage_value,
nvl(tax_bk.production_capacity, 0),
tax_bk.unrevalued_cost,
tax_bk.deprn_method_code,
tax_bk.life_in_months,
decode(tax_bk.period_counter_fully_reserved,null,
(nvl(tax_bk.period_counter_life_complete,0)), 0),
tax_bk.group_asset_id
from fa_asset_history ah,
fa_transaction_headers corp_th,
fa_additions_b ad,
fa_books corp_bk_new,
fa_books corp_bk_old,
fa_books tax_bk
where corp_th.transaction_header_id = p_corp_thid
and corp_th.asset_id = ah.asset_id
and ah.date_ineffective is null
and ah.asset_type = 'CAPITALIZED'
and ad.asset_id = corp_th.asset_id
and corp_bk_new.transaction_header_id_in = p_corp_thid
and corp_bk_old.transaction_header_id_out = p_corp_thid
and tax_bk.asset_id = corp_th.asset_id
and tax_bk.book_type_code = p_tax_book
and tax_bk.date_ineffective is null;
select count (*)
into l_count
from fa_transaction_headers th
where th.book_type_code = p_tax_book
and th.asset_id = l_asset_id
and th.transaction_type_code in
('FULL RETIREMENT', 'PARTIAL RETIREMENT', 'REINSTATEMENT'
)
and th.source_transaction_header_id is null;
select m.amortization_start_date
into l_trans_rec.amortization_start_date
from fa_transaction_headers m
where m.transaction_header_id = p_corp_thid;
select max(retirement_id)
from fa_retirements
where book_type_code = p_tax_book
and asset_id = p_asset_id
and transaction_header_id_out is null;
select corp_th.transaction_date_entered,
corp_th.date_effective,
corp_th.transaction_type_code,
ah.category_id,
ad.asset_number,
corp_bk.cost,
corp_rt.cost_retired,
corp_rt.retirement_id,
tax_bk.cost,
tax_bk.date_placed_in_service,
tax_bk.period_counter_fully_retired,
corp_rt.cost_of_removal,
corp_rt.proceeds_of_sale,
corp_rt.retirement_type_code,
corp_rt.itc_recapture_id,
corp_rt.reference_num,
corp_rt.sold_to,
corp_rt.trade_in_asset_id
from fa_transaction_headers corp_th,
fa_books corp_bk,
fa_books tax_bk,
fa_retirements corp_rt,
fa_additions_b ad,
fa_asset_history ah
where corp_th.transaction_header_id = p_corp_thid
and corp_th.asset_id = ah.asset_id
and corp_th.date_effective < nvl(ah.date_ineffective,
sysdate)
and corp_th.date_effective >= ah.date_effective
and corp_th.transaction_header_id = corp_bk.transaction_header_id_out
and corp_th.transaction_header_id = decode(corp_th.transaction_type_code,
'REINSTATEMENT', corp_rt.transaction_header_id_out,
corp_rt.transaction_header_id_in)
and corp_rt.asset_id = p_asset_id
and corp_rt.book_type_code = p_corp_book
and tax_bk.asset_id = p_asset_id
and tax_bk.book_type_code = p_tax_book
and tax_bk.date_ineffective is null
and ah.asset_type = 'CAPITALIZED'
and ad.asset_id = corp_th.asset_id;
l_mesg_name := 'FA_MCP_ASSET_NOT_IN_TAX'; -- 'FA_MCP_RET_SELECT_DEFAULTS';
select count(*)
into l_count
from fa_transaction_headers th,
fa_retirements ret
where th.book_type_code = p_tax_book
and th.asset_id = p_asset_id
and ret.book_type_code(+) = p_tax_book
and ret.asset_id(+) = p_asset_id
and th.transaction_header_id = ret.transaction_header_id_in(+)
and ret.status(+) not in ('REINSTATE', 'DELETED')
and transaction_type_code not in ('ADDITION/VOID', 'CIP ADDITION VOID')
and th.transaction_date_entered > l_trx_date_entered;
select count(*)
into l_count
from fa_transaction_headers
where asset_id = p_asset_id
and book_type_code = p_tax_book
and transaction_type_code not in ('ADDITION/VOID', 'CIP ADDITION VOID')
and transaction_date_entered > l_trx_date_entered;
select count(*)
into l_count
from fa_conventions conv1,
fa_conventions conv2,
fa_calendar_periods cal1,
fa_calendar_periods cal2
where conv1.prorate_convention_code =
fa_cache_pkg.fazccbd_record.retirement_prorate_convention
and conv2.prorate_convention_code =
fa_cache_pkg.fazccbd_record.retirement_prorate_convention
and l_trx_date_entered
between conv1.start_date and conv1.end_date
and fa_cache_pkg.fazcdp_record.calendar_period_close_date
between conv2.start_date and conv2.end_date
and cal1.calendar_type = fa_cache_pkg.fazcbc_record.prorate_calendar
and cal2.calendar_type = fa_cache_pkg.fazcbc_record.prorate_calendar
and conv1.prorate_date between cal1.start_date and cal1.end_date
and conv2.prorate_date between cal2.start_date and cal2.end_date
and cal1.end_date = cal2.end_date;
select count(*)
into l_count
from fa_retirements
where book_type_code = p_tax_book
and asset_id = p_asset_id
and status in ('REINSTATE', 'PENDING');
select count(*)
into l_count
from fa_retirements rt,
fa_transaction_headers th
where rt.transaction_header_id_out = p_corp_thid
and th.book_type_code = p_tax_book
and th.asset_id = p_asset_id
and th.transaction_type_code in
('FULL RETIREMENT', 'PARTIAL RETIREMENT'
)
and th.source_transaction_header_id =
rt.transaction_header_id_in;
select status
into l_ret_status
from fa_retirements
where book_type_code = p_tax_book
and asset_id = p_asset_id
and retirement_id = l_asset_retire_rec.retirement_id;
l_ret_status = 'DELETED') then
l_mesg_name := 'FA_MCP_RET_MANUAL_TAX';
select count(*)
into l_count
from fa_transaction_headers th
where th.book_type_code = p_tax_book
and th.asset_id = p_asset_id
and th.transaction_type_code in
('FULL RETIREMENT', 'PARTIAL RETIREMENT', 'REINSTATEMENT'
)
and th.source_transaction_header_id is null;
select distinct
fpw_p.asset_id,
fpw_p.worker_number
from fa_parallel_workers fpw_p,
fa_parallel_workers fpw_c
where fpw_p.request_id = p_parent_request_id
and fpw_p.transaction_type_code = 'ADDITION'
and (fpw_p.parent_asset_id is null or
not exists
(select 1
from fa_parallel_workers fpw_p1
where fpw_p1.request_id = p_parent_request_id
and fpw_p1.asset_id = fpw_p.parent_asset_id
and fpw_p1.transaction_type_code = 'ADDITION'))
and fpw_c.request_id = fpw_p.request_id
and fpw_c.parent_asset_id = fpw_p.asset_id
and fpw_c.transaction_type_code = 'ADDITION';
select fpw1.asset_id,
level
from fa_parallel_workers fpw1
start with fpw1.asset_id = p_parent_asset_id
and fpw1.request_id = p_parent_request_id
and fpw1.transaction_type_code = 'ADDITION'
connect by prior fpw1.asset_id = fpw1.parent_asset_id
and prior fpw1.request_id = fpw1.request_id
and prior fpw1.transaction_type_code = 'ADDITION';
fa_debug_pkg.add(l_calling_fn, 'inserting initial transactions at', sysdate,
p_log_level_rec => g_log_level_rec);
insert into fa_parallel_workers
(request_id ,
asset_id ,
asset_number ,
asset_type ,
asset_category_id ,
parent_asset_id ,
book_type_code ,
transaction_date_entered ,
corp_transaction_header_id ,
tax_transaction_header_id ,
transaction_type_code ,
old_group_asset_id ,
new_group_asset_id ,
worker_number ,
process_order ,
process_status )
select p_parent_request_id,
assets.asset_id,
assets.asset_number,
assets.asset_type,
assets.asset_category_id,
assets.parent_asset_id,
p_book_type_code,
assets.date_placed_in_service,
assets.transaction_header_id_in,
NULL tax_transaction_header_id,
'ADDITION' transaction_type_code,
NULL,
decode(fa_cache_pkg.fazcbc_record.copy_group_assignment_flag,
'Y', assets.group_asset_id,
cbd.group_asset_id),
decode(asset_type, 'GROUP', 1,
decode(fa_cache_pkg.fazcbc_record.copy_group_assignment_flag,
'Y', decode(assets.group_asset_id,
null, mod(assets.asset_id, p_total_requests) + 1,
1),
decode(cbd.group_asset_id,
null, mod(assets.asset_id, p_total_requests) + 1,
1))),
decode(asset_type, 'GROUP', 1,
decode(fa_cache_pkg.fazcbc_record.copy_group_assignment_flag,
'Y', decode(assets.group_asset_id,
null, 1,
2),
decode(cbd.group_asset_id,
null, 1,
2))),
'UNASSIGNED'
from (select ad.asset_id,
ad.asset_number,
-- ad.asset_type,
ah.asset_type, -- bug fix 5925965
ad.asset_category_id,
ad.parent_asset_id,
books.book_type_code,
books.group_asset_id,
books.date_placed_in_service,
books.transaction_header_id_in,
books.period_counter_fully_retired
from fa_books books,
fa_additions_b ad,
fa_deprn_periods dp,
fa_asset_history ah -- bug fix 5925965
where books.date_effective <= nvl(l_corp_period_rec.period_close_date, sysdate)
and nvl(books.date_ineffective, sysdate) > nvl(l_corp_period_rec.period_close_date, sysdate - 1)
and books.book_type_code = fa_cache_pkg.fazcbc_record.distribution_source_book
-- bug fix 5925965 (Initial Mass Copy copies capitalized assets to wrong fiscal year and period in TAX book)
and ah.asset_id = books.asset_id
and ah.date_effective <= l_date_effective
and nvl(ah.date_ineffective, sysdate+1) > l_date_effective
and ah.asset_type in ('CAPITALIZED', 'GROUP')
-- End bug fix 5925965
and dp.book_type_code (+) = fa_cache_pkg.fazcbc_record.distribution_source_book
and dp.period_counter (+) = books.period_counter_fully_retired
and nvl(dp.period_counter,
l_corp_period_rec.period_counter + 1) > l_corp_period_rec.period_counter
and ad.asset_type in ('CAPITALIZED', 'GROUP')
and ad.asset_id = books.asset_id) assets,
fa_books taxbk,
fa_category_book_defaults cbd
where taxbk.asset_id(+) = assets.asset_id
and taxbk.book_type_code(+) = p_book_type_code
and taxbk.transaction_header_id_out(+) is null
and taxbk.asset_id is null
and cbd.category_id(+) = assets.asset_category_id
and cbd.book_type_code(+) = p_book_type_code
and assets.date_placed_in_service between cbd.start_dpis(+) and nvl(cbd.end_dpis(+), assets.date_placed_in_service);
insert into fa_parallel_workers
(request_id ,
asset_id ,
asset_number ,
asset_type ,
asset_category_id ,
parent_asset_id ,
book_type_code ,
transaction_date_entered ,
corp_transaction_header_id ,
tax_transaction_header_id ,
transaction_type_code ,
old_group_asset_id ,
new_group_asset_id ,
worker_number ,
process_order ,
process_status )
select p_parent_request_id,
assets.asset_id,
assets.asset_number,
assets.asset_type,
assets.asset_category_id,
decode(tax_bk.transaction_header_id_in, -- if asset exists in tax, parent is irrelevant
null, assets.parent_asset_id,
null),
p_book_type_code,
assets.transaction_date_entered,
assets.transaction_header_id,
tax_bk.transaction_header_id_in,
assets.transaction_type_code,
tax_bk.group_asset_id,
decode(tax_bk.asset_id,
null, decode(fa_cache_pkg.fazcbc_record.copy_group_assignment_flag,
'Y', nvl(new_group_asset_id, cbd.group_asset_id),
cbd.group_asset_id),
tax_bk.group_asset_id),
decode(asset_type,
'GROUP', 1,
decode(tax_bk.asset_id,
null, decode(fa_cache_pkg.fazcbc_record.copy_group_assignment_flag,
'Y', decode(nvl(new_group_asset_id, cbd.group_asset_id),
null, mod(assets.asset_id, p_total_requests) + 1,
1),
decode(cbd.group_asset_id,
null, mod(assets.asset_id, p_total_requests) + 1,
1)),
decode(tax_bk.group_asset_id,
null, mod(assets.asset_id, p_total_requests) + 1,
1))),
decode(asset_type,
'GROUP', 1,
decode(tax_bk.asset_id,
null, decode(fa_cache_pkg.fazcbc_record.copy_group_assignment_flag,
'Y', decode(nvl(new_group_asset_id, cbd.group_asset_id),
null, 1,
2),
decode(cbd.group_asset_id,
null, 1,
2)),
decode(tax_bk.group_asset_id,
null, 1,
2))),
'UNASSIGNED'
from (select ad.asset_id,
ad.asset_number,
ad.asset_type,
ad.asset_category_id,
ad.parent_asset_id,
corp_th.transaction_date_entered,
corp_th.transaction_header_id,
corp_th.transaction_type_code,
corp_bk_old.group_asset_id old_group_asset_id,
corp_bk.group_asset_id new_group_asset_id
from fa_additions_b ad,
fa_transaction_headers corp_th,
fa_books corp_bk,
fa_books corp_bk_old,
TABLE(CAST(fa_trx_types_tab AS fa_char30_tbl_type)) trx
where corp_th.book_type_code = fa_cache_pkg.fazcbc_record.distribution_source_book
and corp_th.transaction_type_code = trx.column_value
and corp_th.date_effective <= nvl(l_corp_period_rec.period_close_date, sysdate)
and corp_th.date_effective >= l_corp_period_rec.period_open_date
and corp_th.source_transaction_header_id is null
and ad.asset_type in('CAPITALIZED', 'GROUP')
and ad.asset_id = corp_th.asset_id
and corp_bk.asset_id = corp_th.asset_id
and corp_bk.book_type_code = corp_th.book_type_code
and corp_bk.transaction_header_id_in = corp_th.transaction_header_id
and corp_bk_old.asset_id(+) = corp_th.asset_id
and corp_bk_old.book_type_code(+) = corp_th.book_type_code
and corp_bk_old.transaction_header_id_out(+) = corp_th.transaction_header_id) assets,
fa_transaction_headers tax_th,
fa_books tax_bk,
fa_category_book_defaults cbd
where tax_th.book_type_code(+) = p_book_type_code
and tax_th.asset_id(+) = assets.asset_id
and tax_th.source_transaction_header_id(+) = assets.transaction_header_id
and tax_th.source_transaction_header_id is null
and tax_bk.asset_id(+) = assets.asset_id
and tax_bk.book_type_code(+) = p_book_type_code
and tax_bk.transaction_header_id_out(+) is null
and cbd.category_id(+) = assets.asset_category_id
and cbd.book_type_code(+) = p_book_type_code
and assets.transaction_date_entered between cbd.start_dpis(+) and nvl(cbd.end_dpis(+), assets.transaction_date_entered);
fa_debug_pkg.add(l_calling_fn, 'rows inserted into fa_parallel_workers', sql%rowcount,
p_log_level_rec => g_log_level_rec);
update fa_parallel_workers
set worker_number = l_child_worker_number(i),
process_order = l_child_process_order(i) + l_group_increment
where request_id = p_parent_request_id
and asset_id = l_child_asset_id(i);