[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select count(*)
into l_count
from fa_book_controls
where set_of_books_id = p_ledger_id
and book_class = 'CORPORATE';
,'inserting'
,'category accounts into GT'
,p_log_level_rec => g_log_level_rec);
insert into fa_category_accounts_gt
(clearing_acct
,book_type_code
,asset_type)
select clearing_acct
,book_type_code
,decode(max(acct_type),
1, 'CIP',
'CAPITALIZED')
from (select asset_clearing_acct clearing_acct
,book_type_code
, 2 acct_type
from fa_category_books cb,
fa_categories fc
where book_type_code = p_book_type_code
and cb.category_id = fc.category_id
and fc.enabled_flag = 'Y'
UNION
select cip_clearing_acct , book_type_code, 1
from fa_category_books cb,
fa_categories fc
where cip_clearing_acct is not null
and book_type_code = p_book_type_code
and cb.category_id = fc.category_id
and fc.enabled_flag = 'Y')
group by clearing_acct, book_type_code;
insert into fa_category_accounts_gt
(clearing_acct
,book_type_code
,asset_type)
select clearing_acct
,book_type_code
,decode(max(acct_type),
1, 'CIP',
'CAPITALIZED')
from (select asset_clearing_acct clearing_acct
,cb.book_type_code
,2 acct_type
from fa_category_books cb,
fa_book_controls bc,
fa_categories fc
where cb.book_type_code = bc.book_type_code
and bc.book_class = 'CORPORATE'
and bc.set_of_books_id = p_ledger_id
and cb.category_id = fc.category_id
and fc.enabled_flag = 'Y'
UNION
select cip_clearing_acct
,cb.book_type_code
,1
from fa_category_books cb,
fa_book_controls bc,
fa_categories fc
where cip_clearing_acct is not null
and cb.book_type_code = bc.book_type_code
and bc.book_class = 'CORPORATE'
and bc.set_of_books_id = p_ledger_id
and cb.category_id = fc.category_id
and fc.enabled_flag = 'Y')
group by clearing_acct, book_type_code;
,'No of Records Inserted '
,to_char(l_count)
,p_log_level_rec => g_log_level_rec);
delete
from fa_category_accounts_gt gt1
where gt1.book_type_code <> p_book_type_code
and exists
(select /*+ index (gt2 FA_CATEGORY_ACCOUNTS_GT_N1) */ 1
from fa_category_accounts_gt gt2
where gt2.book_type_code = p_book_type_code
and gt2.clearing_acct = gt1.clearing_acct);
,'No of Records Deleted '
,to_char(l_count)
,p_log_level_rec => g_log_level_rec);
delete
from fa_category_accounts_gt gt1
where not exists
(select /*+ index (gt2 FA_CATEGORY_ACCOUNTS_GT_N1) */ 1
from fa_category_accounts_gt gt2
where gt2.book_type_code = p_book_type_code
and gt2.clearing_acct = gt1.clearing_acct)
and gt1.rowid <>
(select min(rowid)
from fa_category_accounts_gt gt3
where gt3.clearing_acct = gt1.clearing_acct);
,'No of Records Deleted '
,to_char(l_count)
,p_log_level_rec => g_log_level_rec);
update /*+ index(FA_MASS_ADDITIONS_GT FA_MASS_ADDITIONS_GT_N4) */fa_mass_additions_gt
set mass_addition_id = fa_mass_additions_s.nextval
,book_type_code = p_book_type_code
,line_status = p_line_status_tbl(i)
,posting_status = decode (nvl(fa_cache_pkg.fazcbc_record.IMPORT_ALLOC_INV_LINES_AS_NEW,'N'), 'N', p_posting_status_tbl(i), 'NEW') /* ER 14739752 */
,queue_name = decode (nvl(fa_cache_pkg.fazcbc_record.IMPORT_ALLOC_INV_LINES_AS_NEW,'N'), 'N', p_queue_name_tbl(i), 'NEW')
,asset_type = p_asset_type_tbl(i)
,split_merged_code = decode (nvl(fa_cache_pkg.fazcbc_record.IMPORT_ALLOC_INV_LINES_AS_NEW,'N'), 'N', p_merged_code_tbl(i),NULL) /* ER 14739752 */
,merged_code = decode (nvl(fa_cache_pkg.fazcbc_record.IMPORT_ALLOC_INV_LINES_AS_NEW,'N'), 'N', p_merged_code_tbl(i),NULL)
,parent_mass_addition_id = decode (nvl(fa_cache_pkg.fazcbc_record.IMPORT_ALLOC_INV_LINES_AS_NEW,'N'), 'N', p_mass_add_id_tbl(i),NULL)
,merge_parent_mass_additions_id = decode (nvl(fa_cache_pkg.fazcbc_record.IMPORT_ALLOC_INV_LINES_AS_NEW,'N'), 'N', p_mass_add_id_tbl(i),NULL)
,add_to_asset_id = decode (nvl(fa_cache_pkg.fazcbc_record.IMPORT_ALLOC_INV_LINES_AS_NEW,'N'), 'N', p_asset_id_tbl(i),NULL)
where parent_invoice_dist_id = p_invoice_dist_id_tbl(i)
and line_status = 'NEW'
and ledger_category_code = 'P'
returning invoice_distribution_id
,parent_mass_addition_id
,add_to_asset_id
,line_status
,posting_status
,queue_name
,asset_type
,merged_code
bulk collect
into l_invoice_dist_id_tbl
,l_mass_add_id_tbl
,l_asset_id_tbl
,l_line_status_tbl
,l_posting_status_tbl
,l_queue_name_tbl
,l_asset_type_tbl
,l_merged_code_tbl;
,'No of updated records fetched for iteration' || to_char(G_child_iteration_count)
, to_char(l_invoice_dist_id_tbl.count)
,p_log_level_rec => g_log_level_rec);
update /*+ index(FA_MASS_ADDITIONS_GT FA_MASS_ADDITIONS_GT_N4) */ fa_mass_additions_gt
set mass_addition_id = fa_mass_additions_s.nextval
,book_type_code = p_book_type_code
,line_status = 'VALID'
,posting_status = 'NEW'
,queue_name = 'NEW'
,asset_type = p_asset_type_tbl(i)
where parent_invoice_dist_id = p_invoice_dist_id_tbl(i)
and line_status = 'NEW'
and ledger_category_code = 'P'
returning invoice_distribution_id, mass_addition_id, asset_type
bulk collect
into l_invoice_dist_id_tbl, l_mass_add_id_tbl, l_asset_type_tbl;
,'No of updated records updated for split MAD/AI lines'
, l_invoice_dist_id_tbl.count
,p_log_level_rec => g_log_level_rec);
update /*+ index(gt FA_MASS_ADDITIONS_GT_N3) */ fa_mass_additions_gt gt
set split_merged_code = decode (nvl(fa_cache_pkg.fazcbc_record.IMPORT_ALLOC_INV_LINES_AS_NEW,'N'), 'N', 'MP',NULL), /* ER 14739752 */
merged_code = decode (nvl(fa_cache_pkg.fazcbc_record.IMPORT_ALLOC_INV_LINES_AS_NEW,'N'), 'N', 'MP',NULL)
where mass_addition_id = l_mass_add_id_tbl(i)
and book_type_code = p_book_type_code
and invoice_payment_id is null -- exclude discounts
and ledger_category_code = 'P'
and exists
(select 1
from fa_mass_additions_gt gt2
where gt2.parent_invoice_dist_id = gt.invoice_distribution_id
and gt2.ledger_category_code = 'P' --Bug#10263900
and gt2.rowid <> gt.rowid);
select mad.invoice_distribution_id,
nvl(mad.parent_mass_addition_id,mad.mass_addition_id)
from fa_mass_additions mad
where book_type_code = p_book_type_code
and posting_status = 'DELETE'
and not exists
(select 1
from fa_mass_additions mad2
where mad2.book_type_code = p_book_type_code
and mad2.invoice_distribution_id = mad.invoice_distribution_id
and mad2.posting_status not in ('DELETE', 'SPLIT'))
union
select mad_c.invoice_distribution_id,
nvl(mad_c.parent_mass_addition_id,mad_c.mass_addition_id)
from fa_mass_additions mad_c,
fa_mass_additions mad_p
where mad_p.book_type_code = p_book_type_code
and mad_p.posting_status = 'DELETE'
and mad_c.parent_mass_addition_id = mad_p.mass_addition_id
and not exists
(select 1
from fa_mass_additions mad2
where mad2.book_type_code = p_book_type_code
and mad2.invoice_distribution_id = mad_p.invoice_distribution_id
and mad2.posting_status not in ('DELETE', 'SPLIT')) ;
select /*+ index(gt FA_MASS_ADDITIONS_GT_N2) */ rowid,
fa_mass_additions_s.nextval
from fa_mass_additions_gt gt
where (gt.line_type_lookup_code in ('ITEM', 'ACCRUAL') OR
(gt.line_type_lookup_code in ('NONREC_TAX','FREIGHT','MISCELLANEOUS') and
gt.parent_invoice_dist_id is null and
nvl(fa_cache_pkg.fazcbc_record.allow_unallocated_lines_flag,'N') = 'Y')
)
and gt.ledger_category_code = 'P'
and gt.line_status = 'NEW'
and gt.invoice_payment_id is null; -- exclude discounts
select mad.invoice_distribution_id,
min(nvl(mad.parent_mass_addition_id,mad.mass_addition_id)),
min(mad.add_to_asset_id),
min(ad.asset_id),
min(mad.posting_status),
max(mad.asset_type),
count(distinct mad.rowid)
from fa_mass_additions_gt gt,
fa_mass_additions mad,
fa_additions_b ad
where ad.asset_number(+) = mad.asset_number
and mad.book_type_code = p_book_type_code
and mad.invoice_distribution_id = gt.parent_invoice_dist_id
-- BUG# 9162562 - see discussion for why we can only
-- join by dist_id here...
-- and mad.invoice_id = gt.invoice_id
and mad.posting_status not in ('SPLIT', 'DELETE')
and mad.invoice_payment_id is null -- do not merge to discount
and gt.ledger_category_code = 'P'
and gt.line_status = 'NEW'
group by mad.invoice_distribution_id;
select ai.invoice_distribution_id,
min(ai.asset_id),
max(ad.asset_type),
count(distinct ai.rowid),
count(distinct ai.asset_id)
from fa_asset_invoices ai,
fa_additions_b ad,
fa_mass_additions_gt gt
where ai.invoice_distribution_id = gt.parent_invoice_dist_id
and ad.asset_id = ai.asset_id
and gt.ledger_category_code = 'P'
and gt.line_status = 'NEW'
group by ai.invoice_distribution_id;
,'due to parents in the delete queue'
,p_log_level_rec => g_log_level_rec);
update fa_mass_additions_gt gt
set (asset_type, line_status, book_type_code, mass_addition_id) =
(select /*+ index(fca FA_CATEGORY_ACCOUNTS_GT_N2 */
decode(gt.asset_type,
null, decode(glcc.account_type,
''E'', ''EXPENSED'',
nvl(fca.asset_type, gt.asset_type)),
gt.asset_type),
decode(glcc.account_type,
''E'', ''VALID'',
decode(gt.book_type_code,
null, decode(fca.book_type_code,
:h_book_type_code, ''VALID'',
null, ''REJECTED'',
''OTHER BOOK''),
decode(gt.book_type_code,
:h_book_type_code, decode(fca.book_type_code,
:h_book_type_code, ''VALID'',
''REJECTED''),
''OTHER BOOK''))),
decode(glcc.account_type,
''E'', :h_book_type_code,
decode(gt.book_type_code,
null, decode(fca.book_type_code,
:h_book_type_code, :h_book_type_code,
null),
gt.book_type_code)),
:mass_add_id
from gl_code_combinations glcc,
fa_category_accounts_gt fca
where gt.payables_code_combination_id = glcc.code_combination_id
and fca.clearing_acct(+) = ' ||
' glcc.' || p_column_name || '
)
where rowid = :l_rowid
returning invoice_distribution_id, mass_addition_id, line_status,
asset_type
into :inv_tbl, :massadd_tbl, :line_status_tbl,
:asset_type_tbl';
,'No of item/accrual lines Updated '
,l_invoice_dist_id_tbl.count
,p_log_level_rec => g_log_level_rec);
/*10209969 - Need to update inside loop as l_mass_add_id_tbl is fetched in each iteration */
-- flag the parent as MP
forall i in 1..l_mass_add_id_tbl.count
update fa_mass_additions_gt gt
set split_merged_code = decode (nvl(fa_cache_pkg.fazcbc_record.IMPORT_ALLOC_INV_LINES_AS_NEW,'N'), 'N', 'MP',NULL), /* ER 14739752 */
merged_code = decode (nvl(fa_cache_pkg.fazcbc_record.IMPORT_ALLOC_INV_LINES_AS_NEW,'N'), 'N', 'MP',NULL)
where mass_addition_id = l_mass_add_id_tbl(i)
and book_type_code = p_book_type_code
and invoice_payment_id is null
and ledger_category_code = 'P'
and exists
(select 1
from fa_mass_additions_gt gt2
where gt2.parent_invoice_dist_id = gt.invoice_distribution_id
and gt2.ledger_category_code = 'P' --Bug#10263900
and gt2.rowid <> gt.rowid);
l_child_inv_dist_id1_tbl.delete;
l_child_mass_add_id1_tbl.delete;
l_child_asset_id1_tbl.delete;
l_child_line_status1_tbl.delete;
l_child_posting_status1_tbl.delete;
l_child_queue_name1_tbl.delete;
l_child_asset_type1_tbl.delete;
l_child_merged_code1_tbl.delete;
l_child_inv_dist_id2_tbl.delete;
l_child_asset_type2_tbl.delete;
update fa_mass_additions
set split_merged_code = decode (nvl(fa_cache_pkg.fazcbc_record.IMPORT_ALLOC_INV_LINES_AS_NEW,'N'), 'N', 'MP',NULL), /* ER 14739752 */
merged_code = decode (nvl(fa_cache_pkg.fazcbc_record.IMPORT_ALLOC_INV_LINES_AS_NEW,'N'), 'N', 'MP',NULL)
where mass_addition_id = l_child_mass_add_id1_tbl(i)
and book_type_code = p_book_type_code;
l_child_inv_dist_id1_tbl.delete;
l_child_mass_add_id1_tbl.delete;
l_child_asset_id1_tbl.delete;
l_child_line_status1_tbl.delete;
l_child_posting_status1_tbl.delete;
l_child_queue_name1_tbl.delete;
l_child_asset_type1_tbl.delete;
l_child_merged_code1_tbl.delete;
l_child_inv_dist_id1A_tbl.delete;
l_child_asset_id1A_tbl.delete;
l_child_line_status1A_tbl.delete;
l_child_asset_type1A_tbl.delete;
l_child_inv_dist_id2_tbl.delete;
l_child_asset_type2_tbl.delete;
update /*+ index(FA_MASS_ADDITIONS_GT FA_MASS_ADDITIONS_GT_N4) */ fa_mass_additions_gt
set mass_addition_id = fa_mass_additions_s.nextval,
--book_type_code = p_book_type_code,
line_status = 'VALID',
posting_status = decode (nvl(fa_cache_pkg.fazcbc_record.IMPORT_ALLOC_INV_LINES_AS_NEW,'N'), 'N', 'ON HOLD','NEW'), /* ER 14739752 */
queue_name = decode (nvl(fa_cache_pkg.fazcbc_record.IMPORT_ALLOC_INV_LINES_AS_NEW,'N'), 'N', 'ON HOLD','NEW'),
asset_type = l_child_asset_type1_tbl(i), --decode (nvl(fa_cache_pkg.fazcbc_record.IMPORT_ALLOC_INV_LINES_AS_NEW,'N'), 'N', l_child_asset_type1_tbl(i),asset_type),
split_merged_code = NULL,
merged_code = NULL,
parent_mass_addition_id = NULL,
merge_parent_mass_additions_id = NULL,
add_to_asset_id = decode (nvl(fa_cache_pkg.fazcbc_record.IMPORT_ALLOC_INV_LINES_AS_NEW,'N'), 'N', l_child_asset_id1_tbl(i),NULL)
where parent_invoice_dist_id = l_child_inv_dist_id1_tbl(i)
and line_status = 'NEW'
and ledger_category_code = 'P'
and book_type_code = p_book_type_code
returning mass_addition_id
, invoice_distribution_id
, add_to_asset_id
, line_status
, asset_type bulk collect
into l_child_mass_add_id1_tbl
, l_child_inv_dist_id1A_tbl
, l_child_asset_id1A_tbl
, l_child_line_status1A_tbl
, l_child_asset_type1A_tbl;
update /*+ index(gt FA_MASS_ADDITIONS_GT_N3) */ fa_mass_additions_gt gt
set split_merged_code = decode (nvl(fa_cache_pkg.fazcbc_record.IMPORT_ALLOC_INV_LINES_AS_NEW,'N'), 'N', 'MP',NULL), /* ER 14739752 */
merged_code = decode (nvl(fa_cache_pkg.fazcbc_record.IMPORT_ALLOC_INV_LINES_AS_NEW,'N'), 'N', 'MP',NULL)
where invoice_distribution_id = l_child_inv_dist_id1A_tbl(i)
and invoice_payment_id is null -- exclude discounts
and book_type_code = p_book_type_code
and ledger_category_code = 'P'
and exists
(select 1
from fa_mass_additions_gt gt2
where gt2.parent_invoice_dist_id = gt.invoice_distribution_id
and gt2.rowid <> gt.rowid);
update /*+ index(gt FA_MASS_ADDITIONS_GT_N1) */ fa_mass_additions_gt gt
set depreciate_flag =
(select decode(gt.asset_type
,'EXPENSED','NO'
,nvl(CBD.depreciate_flag, 'YES'))
from fa_category_book_defaults CBD
where CBD.book_type_code(+) = p_book_type_code
and CBD.category_id(+)= gt.asset_category_id
and p_def_dpis_dt between CBD.start_DPIS(+)
and nvl(CBD.end_DPIS(+),p_def_dpis_dt)),
inventorial =
(select nvl(inventorial, 'YES')
from fa_categories_b c
where c.category_id(+) = gt.asset_category_id)
where gt.book_type_code = p_book_type_code
and gt.line_status = 'VALID'
and gt.ledger_category_code = 'P'
and gt.asset_category_id is not null
and gt.add_to_asset_id is null;
,'No of Records Updated'
,to_char(l_count)
,p_log_level_rec => g_log_level_rec);
update fa_system_controls
set last_mass_additions = l_request_id;
,'Insert FA_MASS_ADDITIONS lines for primary ledger '
,p_book_type_code
,p_log_level_rec => g_log_level_rec);
insert into fa_mass_additions(
mass_addition_id ,
asset_number ,
tag_number ,
description ,
asset_category_id ,
manufacturer_name ,
serial_number ,
model_number ,
book_type_code ,
date_placed_in_service ,
fixed_assets_cost ,
payables_units ,
fixed_assets_units ,
payables_code_combination_id ,
expense_code_combination_id ,
location_id ,
assigned_to ,
feeder_system_name ,
create_batch_date ,
create_batch_id ,
last_update_date ,
last_updated_by ,
reviewer_comments ,
invoice_number ,
vendor_number ,
po_vendor_id ,
po_number ,
posting_status ,
queue_name ,
invoice_date ,
invoice_created_by ,
invoice_updated_by ,
payables_cost ,
invoice_id ,
payables_batch_name ,
depreciate_flag ,
parent_mass_addition_id ,
parent_asset_id ,
split_merged_code ,
ap_distribution_line_number ,
post_batch_id ,
add_to_asset_id ,
amortize_flag ,
new_master_flag ,
asset_key_ccid ,
asset_type ,
deprn_reserve ,
ytd_deprn ,
beginning_nbv ,
created_by ,
creation_date ,
last_update_login ,
salvage_value ,
accounting_date ,
attribute_category_code ,
fully_rsvd_revals_counter ,
merge_invoice_number ,
merge_vendor_number ,
production_capacity ,
reval_amortization_basis ,
reval_reserve ,
unit_of_measure ,
unrevalued_cost ,
ytd_reval_deprn_expense ,
merged_code ,
split_code ,
merge_parent_mass_additions_id ,
split_parent_mass_additions_id ,
project_asset_line_id ,
project_id ,
task_id ,
sum_units ,
dist_name ,
context ,
inventorial ,
short_fiscal_year_flag ,
conversion_date ,
original_deprn_start_date ,
group_asset_id ,
cua_parent_hierarchy_id ,
units_to_adjust ,
bonus_ytd_deprn ,
bonus_deprn_reserve ,
amortize_nbv_flag ,
amortization_start_date ,
transaction_type_code ,
transaction_date ,
warranty_id ,
lease_id ,
lessor_id ,
property_type_code ,
property_1245_1250_code ,
in_use_flag ,
owned_leased ,
new_used ,
asset_id ,
material_indicator_flag ,
invoice_distribution_id ,
invoice_line_number ,
invoice_payment_id ,
warranty_number)
select /*+ index(gt FA_MASS_ADDITIONS_GT_N1) */
gt.mass_addition_id , --fa_mass_additions_s.nextval,
gt.asset_number ,
gt.tag_number ,
gt.description ,
gt.asset_category_id ,
gt.manufacturer_name ,
gt.serial_number ,
gt.model_number ,
p_book_type_code ,
decode(l_def_dpis_enabled, 1, gt.invoice_date, l_def_dpis_dt ) ,
gt.fixed_assets_cost ,
gt.payables_units ,
gt.fixed_assets_units ,
gt.payables_code_combination_id ,
gt.expense_code_combination_id ,
gt.location_id ,
gt.assigned_to ,
gt.feeder_system_name ,
gt.create_batch_date ,
gt.create_batch_id ,
gt.last_update_date ,
gt.last_updated_by ,
gt.reviewer_comments ,
gt.invoice_number ,
gt.vendor_number ,
gt.po_vendor_id ,
gt.po_number ,
gt.posting_status ,
gt.queue_name ,
gt.invoice_date ,
gt.invoice_created_by ,
gt.invoice_updated_by ,
gt.payables_cost ,
gt.invoice_id ,
gt.payables_batch_name ,
gt.depreciate_flag ,
gt.parent_mass_addition_id ,
gt.parent_asset_id ,
gt.split_merged_code ,
gt.ap_distribution_line_number ,
gt.post_batch_id ,
gt.add_to_asset_id ,
gt.amortize_flag ,
gt.new_master_flag ,
gt.asset_key_ccid ,
gt.asset_type , -- reinstated
gt.deprn_reserve ,
gt.ytd_deprn ,
gt.beginning_nbv ,
gt.created_by ,
gt.creation_date ,
gt.last_update_login ,
gt.salvage_value ,
gt.accounting_date ,
gt.attribute_category_code ,
gt.fully_rsvd_revals_counter ,
gt.merge_invoice_number ,
gt.merge_vendor_number ,
gt.production_capacity ,
gt.reval_amortization_basis ,
gt.reval_reserve ,
gt.unit_of_measure ,
gt.unrevalued_cost ,
gt.ytd_reval_deprn_expense ,
gt.merged_code ,
gt.split_code ,
gt.merge_parent_mass_additions_id ,
gt.split_parent_mass_additions_id ,
gt.project_asset_line_id ,
gt.project_id ,
gt.task_id ,
/* gt.sum_units */
null,
gt.dist_name ,
gt.context ,
gt.inventorial ,
gt.short_fiscal_year_flag ,
gt.conversion_date ,
gt.original_deprn_start_date ,
gt.group_asset_id ,
gt.cua_parent_hierarchy_id ,
gt.units_to_adjust ,
gt.bonus_ytd_deprn ,
gt.bonus_deprn_reserve ,
gt.amortize_nbv_flag ,
gt.amortization_start_date ,
/* transaction_type_code - only future add in future period */
decode(sign(decode(l_def_dpis_enabled, 1, gt.invoice_date, l_def_dpis_dt)
- l_calendar_period_close_date), 1, 'FUTURE ADD', NULL ),
/* transaction date */
decode(sign(decode(l_def_dpis_enabled, 1, gt.invoice_date, l_def_dpis_dt)
- l_calendar_period_close_date), 1, decode(l_def_dpis_enabled,
1, invoice_date, l_def_dpis_dt), null ),
gt.warranty_id ,
gt.lease_id ,
gt.lessor_id ,
gt.property_type_code ,
gt.property_1245_1250_code ,
gt.in_use_flag ,
gt.owned_leased ,
gt.new_used ,
gt.asset_id ,
gt.material_indicator_flag ,
gt.invoice_distribution_id ,
gt.invoice_line_number ,
gt.invoice_payment_id ,
gt.warranty_number
from fa_mass_additions_gt gt
where gt.book_type_code = p_book_type_code
and gt.ledger_category_code = 'P'
and gt.line_status = 'VALID';
,'No of Records Inserted '
,to_char(l_count)
,p_log_level_rec => g_log_level_rec);
,'Inserting into FA_MC_MASS_RATES for reporting ledger(s) '
,p_book_type_code
,p_log_level_rec => g_log_level_rec);
Insert into fa_mc_mass_rates
( set_of_books_id,
mass_addition_id,
fixed_assets_cost,
exchange_rate)
select /*+ leading(gt) */
gt.ledger_id,
mad.mass_addition_id,
gt.fixed_assets_cost,
0
from fa_mass_additions mad,
fa_mass_additions_gt gt,
fa_mc_book_controls mcbc
where mad.book_type_code = p_book_type_code
and mad.invoice_distribution_id = gt.invoice_distribution_id
and nvl(mad.invoice_payment_id, -99) = nvl(gt.invoice_payment_id, -99)
and gt.ledger_category_code = 'ALC'
and mcbc.book_type_code = mad.book_type_code
and mcbc.set_of_books_id = gt.ledger_id
and mcbc.enabled_flag = 'Y'
and mcbc.mrc_converted_flag = 'Y';
,'No of Records Inserted '
,to_char(l_count)
,p_log_level_rec => g_log_level_rec);
update /*+ index(gt FA_MASS_ADDITIONS_GT_N1) */ fa_mass_additions_gt gt
set gt.line_status = 'PROCESSED'
where book_type_code = p_book_type_code
and line_status = 'VALID'
and ledger_category_code = 'P'
and exists
( select 1
from fa_mass_additions mad
where mad.mass_addition_id = gt.mass_addition_id);