[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT invoice_distribution_id
,parent_invoice_dist_id
,book_type_code
,line_status
,line_type_lookup_code
FROM fa_mass_additions_gt
ORDER BY 2,1;
select count(*)
into l_count
from fa_book_controls
where set_of_books_id = p_ledger_id
and book_class = 'CORPORATE';
DebugLog( 'inserting', 'category accounts into GT');
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
where book_type_code = p_book_type_code
UNION
select cip_clearing_acct , book_type_code, 1
from fa_category_books
where cip_clearing_acct is not null
and book_type_code = p_book_type_code)
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
where cb.book_type_code = bc.book_type_code
and bc.book_class = 'CORPORATE'
and bc.set_of_books_id = p_ledger_id
UNION
select cip_clearing_acct , cb.book_type_code, 1
from fa_category_books cb,
fa_book_controls bc
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)
group by clearing_acct, book_type_code;
DebugLog( 'No of Records Inserted ', to_char(l_count));
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);
DebugLog( 'No of Records Deleted ', to_char(l_count));
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);
DebugLog( 'No of Records Deleted ', to_char(l_count));
update /*+ index(gt FA_MASS_ADDITIONS_GT_N2) */ fa_mass_additions_gt gt
set (asset_type, line_status, book_type_code) =
(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, ''VALID'',
''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))
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.segment' ||
to_char(p_segment_num) || '
)
where gt.line_type_lookup_code in (''ITEM'', ''ACCRUAL'')
and gt.ledger_category_code = ''P''
and gt.line_status = ''NEW'' ';
DebugLog( 'No of Records Updated ', to_char(l_count));
update /*+ index(gt1 FA_MASS_ADDITIONS_GT_N1) */ fa_mass_additions_gt gt1
set (asset_type, line_status , book_type_code) =
(select /*+ index(gt2 FA_MASS_ADDITIONS_GT_N3) */
gt2.asset_type,
decode(gt2.line_status,
'VALID', 'VALID_CHILD1',
gt2.line_status), -- OTHER BOOK / REJECTED
nvl(gt1.book_type_code,
gt2.book_type_code)
from fa_mass_additions_gt gt2
where gt2.invoice_distribution_id = gt1.parent_invoice_dist_id)
where gt1.line_type_lookup_code not in ('ITEM', 'ACCRUAL')
and gt1.ledger_category_code = 'P'
and gt1.line_status = 'NEW'
and exists
(select /*+ index(gt3 FA_MASS_ADDITIONS_GT_N3) */ 1
from fa_mass_additions_gt gt3
where gt3.invoice_distribution_id = gt1.parent_invoice_dist_id);
DebugLog( 'No of Records Updated ', to_char(l_count));
update /*+ index(gt1 FA_MASS_ADDITIONS_GT_N1) */ fa_mass_additions_gt gt1
set (asset_type, line_status) =
(select distinct mad.asset_type,
decode(mad.posting_status,
'POSTED', 'VALID_ORPHAN1',
'DELETED', 'VALID_ORPHAN2',
'VALID_CHILD2')
from fa_mass_additions mad
where mad.book_type_code(+) = p_book_type_code
and mad.invoice_distribution_id(+) = gt1.parent_invoice_dist_id
and mad.invoice_id(+) = gt1.invoice_id
and mad.parent_mass_addition_id(+) is null
)
where book_type_code = p_book_type_code
and gt1.line_type_lookup_code not in ('ITEM', 'ACCRUAL')
and gt1.ledger_category_code = 'P'
and gt1.line_status = 'NEW'
and exists
(select 1
from fa_mass_additions mad
where mad.book_type_code(+) = p_book_type_code
and mad.invoice_distribution_id(+) = gt1.parent_invoice_dist_id
and mad.invoice_id(+) = gt1.invoice_id
and mad.parent_mass_addition_id(+) is null);
DebugLog( 'No of Records Updated ', to_char(l_count));
update /*+ index(gt FA_MASS_ADDITIONS_GT_N2) */ fa_mass_additions_gt gt
set parent_payables_ccid =
(select min(payables_code_combination_id)
from fa_asset_invoices ai
where invoice_distribution_id = gt.parent_invoice_dist_id)
where gt.line_type_lookup_code not in ('ITEM', 'ACCRUAL')
and gt.ledger_category_code = 'P'
and gt.line_status = 'NEW';
update /*+ index(gt FA_MASS_ADDITIONS_GT_N2) */ fa_mass_additions_gt gt
set (asset_type, line_status, book_type_code) =
(select /*+ index(fca FA_CATEGORY_ACCOUNTS_GT_N2 */
decode(asset_type,
null, decode(glcc.account_type,
''E'', ''EXPENSED'',
nvl(fca.asset_type, gt.asset_type)),
asset_type),
decode(glcc.account_type,
''E'', ''VALID_ORPHAN3'',
decode(gt.book_type_code,
null, decode(fca.book_type_code,
:h_book_type_code, ''VALID_ORPHAN3'',
null, ''REJECTED'',
''OTHER BOOK''),
decode(gt.book_type_code,
:h_book_type_code, ''VALID_ORPHAN3'',
''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))
from gl_code_combinations glcc,
fa_category_accounts_gt fca
where gt.parent_payables_ccid = glcc.code_combination_id
and fca.clearing_acct(+) = ' ||
' glcc.segment' ||
to_char(p_segment_num) || '
)
where gt.line_type_lookup_code not in (''ITEM'', ''ACCRUAL'')
and gt.ledger_category_code = ''P''
and gt.line_status = ''NEW''
and gt.parent_payables_ccid is not null ';
DebugLog( 'No of Records Updated ', to_char(l_count));
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 like 'VALID%'
and gt.asset_category_id is not null;
DebugLog( 'No of Records Updated ', to_char(l_count));
update fa_system_controls
set last_mass_additions = l_request_id;
DebugLog('Insert FA_MASS_ADDITIONS with ITEM/ACCRUAL lines for primary ledger ',p_book_type_code );
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) */ 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 */
null ,
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 */
null,
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.line_type_lookup_code in ('ITEM', 'ACCRUAL')
and gt.line_status = 'VALID';
DebugLog( 'No of Records Inserted ', to_char(l_count) );
update /*+ leading(gt) index(gt FA_MASS_ADDITIONS_GT_N1) */ fa_mass_additions_gt gt1
set (posting_status
,parent_mass_addition_id
,split_merged_code
,merged_code
,merge_parent_mass_additions_id) =
(select decode(mad.posting_status,
'POSTED', gt.posting_status,
'DELETE', gt.posting_status,
null, gt.posting_status ,
'MERGED'),
decode(mad.posting_status,
'POSTED', null,
'DELETE', null,
mad.mass_addition_id),
decode(mad.posting_status,
'POSTED', null,
'DELETE', null,
null, null,
'MC'),
decode(mad.posting_status,
'POSTED', null,
'DELETE', null,
null, null,
'MC'),
decode(mad.posting_status,
'POSTED', null,
'DELETE', null,
mad.mass_addition_id)
from fa_mass_additions mad,
fa_mass_additions_gt gt
where gt.rowid = gt1.rowid
and mad.invoice_distribution_id(+) = gt.parent_invoice_dist_id
and mad.book_type_code(+) = p_book_type_code
and mad.invoice_distribution_id(+) is not null
and mad.invoice_id(+) = gt.invoice_id
and mad.parent_mass_addition_id(+) is null)
where gt1.book_type_code = p_book_type_code
and gt1.line_status in ('VALID_CHILD1', 'VALID_CHILD2');
DebugLog( 'Inserting FA_MASS_ADDITIONS with non-ITEM/ACCRUAL lines for primary', p_book_type_code );
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) */ 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 ,
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 ,
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,
gt.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.line_status in ('VALID_CHILD1', 'VALID_CHILD2',
'VALID_ORPHAN1', 'VALID_ORPHAN2', 'VALID_ORPHAN3');
DebugLog( 'No of NON-ITEM Records Inserted ', to_char(l_count) );
update fa_mass_additions ma
set ma.split_merged_code = 'MP',
ma.merged_code = 'MP',
ma.sum_units = 'NO'
where ma.posting_status not in ('POSTED', 'DELETE')
and ma.book_type_code = p_book_type_code
and ma.parent_mass_addition_id is null
and ma.merged_code is null
and ma.split_merged_code is null
and ma.invoice_payment_id is null
and exists
(select 1
from fa_mass_additions mac,
fa_mass_additions_gt gt
where mac.posting_status = 'MERGED'
and mac.book_type_code = p_book_type_code
and mac.merged_code = 'MC'
and mac.merge_parent_mass_additions_id = ma.mass_addition_id
and mac.invoice_distribution_id = gt.invoice_distribution_id);
DebugLog('No of Records Updated ', to_char(l_count));
DebugLog('Inserting into FA_MC_MASS_RATES for reporting ledger(s) ', p_book_type_code);
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
where mad.book_type_code = p_book_type_code
and mad.invoice_distribution_id = gt.invoice_distribution_id
and gt.book_type_code = p_book_type_code
and gt.ledger_category_code <> 'P';
DebugLog( 'No of Records Inserted ', to_char(l_count));
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 like 'VALID%'
and gt.invoice_distribution_id IN
( select mad.invoice_distribution_id
from fa_mass_additions mad
where mad.invoice_distribution_id = gt.invoice_distribution_id
and mad.book_type_code = p_book_type_code
and mad.create_batch_id = gt.create_batch_id
);