[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select distinct valuation_method,
ENTITY_CODE,
EVENT_TYPE_CODE
from xla_events_gt;
select 1
into l_special_secondary_count
from dual
where exists
(select 1
from fa_mc_book_controls mcbc,
gl_ledgers gl
where mcbc.book_type_code = t_book_type_code(i)
and gl.ledger_id = mcbc.set_of_books_id
and gl.LEDGER_CATEGORY_CODE = 'SECONDARY');
update fa_book_controls
set create_accounting_request_id = fnd_global.conc_request_id
where set_of_books_id = p_ledger_id
and book_class <> 'BUDGET';
update fa_book_controls
set create_accounting_request_id = fnd_global.conc_request_id
where book_type_code = p_book_type_code;
update fa_book_controls
set create_accounting_request_id = null
where set_of_books_id = p_ledger_id
and book_class <> 'BUDGET';
update fa_book_controls
set create_accounting_request_id = null
where book_type_code = p_book_type_code;
PROCEDURE update_nonaccountable_events
(p_book_type_code varchar2,
p_process_category varchar2,
p_ledger_id number) IS
l_appl_id number := 140;
l_procedure_name varchar2(80) := 'update_nonaccountable_events';
select 1
into l_trx
from dual
where exists(
select 1
from xla_event_class_attrs
where application_id = 140
and entity_code in ('TRANSACTIONS', 'INTER_ASSET_TRANSACTIONS')
and EVENT_CLASS_GROUP_CODE = nvl(p_process_category,
EVENT_CLASS_GROUP_CODE));
insert into xla_events_int_gt
(event_id,
event_status_code,
application_id,
ledger_id,
entity_code,
valuation_method)
select /*+ leading(EV,TE) use_nl(EV TE TH BC) */
ev.event_id,
'N',
140,
bc.set_of_books_id,
'TRANSACTIONS',
bc.book_type_code
from xla_transaction_entities te,
xla_events ev,
fa_transaction_headers th,
fa_book_controls bc
where te.application_id = l_appl_id
and te.ledger_id = p_ledger_id
and te.entity_code = 'TRANSACTIONS'
and te.valuation_method = nvl(p_book_type_code, te.valuation_method)
and ev.application_id = l_appl_id
and ev.process_status_code in ('U','I','E')
and ev.event_status_code = 'U'
and ev.event_type_code <> 'INFLATION_REVALUATION'
and ev.entity_id = te.entity_id
and th.transaction_header_id = te.source_id_int_1
and bc.book_type_code = te.source_id_char_1
and bc.set_of_books_id = te.ledger_id
and not exists
(select /*+ no_unnest */ 1
from fa_adjustments adj
where adj.transaction_header_id = th.transaction_header_id
and adj.book_type_code = bc.book_type_code
and adj.adjustment_amount <> 0
and nvl(adj.track_member_flag, 'N') <> 'Y')
and not exists
(select /*+ no_unnest leading(th2) index(adj FA_ADJUSTMENTS_U1) */ 1
from fa_adjustments adj,
fa_transaction_headers th2
where th2.member_transaction_header_id = th.transaction_header_id
and adj.transaction_header_id = th2.transaction_header_id
and adj.book_type_code = bc.book_type_code
and adj.adjustment_amount <> 0
and nvl(adj.track_member_flag, 'N') <> 'Y');
insert into xla_events_int_gt
(event_id,
event_status_code,
application_id,
ledger_id,
entity_code,
valuation_method)
select /*+ leading(EV,TE) use_nl(EV TE TH BC) */
ev.event_id,
'N',
140,
bc.set_of_books_id,
'TRANSACTIONS',
bc.book_type_code
from xla_transaction_entities te,
xla_events ev,
fa_transaction_headers th,
fa_book_controls bc
where te.application_id = l_appl_id
and te.ledger_id = p_ledger_id
and te.entity_code = 'TRANSACTIONS'
and te.valuation_method = nvl(p_book_type_code, te.valuation_method)
and ev.application_id = l_appl_id
and ev.process_status_code in ('U','I','E')
and ev.event_status_code = 'U'
and ev.event_type_code <> 'INFLATION_REVALUATION'
and ev.entity_id = te.entity_id
and th.transaction_header_id = te.source_id_int_1
and bc.book_type_code = te.source_id_char_1
and bc.set_of_books_id = te.ledger_id
and not exists
(select /*+ no_unnest */ 1
from fa_adjustments adj
where adj.transaction_header_id = th.transaction_header_id
and adj.book_type_code = bc.book_type_code
and adj.adjustment_amount <> 0
and nvl(adj.track_member_flag, 'N') <> 'Y');
'Rows inserted into gt for non-accountable - trx: ' || to_char(l_trx_count));
insert into xla_events_int_gt
(event_id,
event_status_code,
application_id,
ledger_id,
entity_code,
valuation_method)
select /*+ leading(EV,TE) use_nl(EV TE TRX BC) */
ev.event_id,
'N',
140,
bc.set_of_books_id,
'INTER_ASSET_TRANSACTIONS',
bc.book_type_code
from xla_events ev,
xla_transaction_entities te,
fa_trx_references trx,
fa_book_controls bc
where te.application_id = l_appl_id
and te.ledger_id = p_ledger_id
and te.entity_code = 'INTER_ASSET_TRANSACTIONS'
and te.valuation_method = nvl(p_book_type_code, te.valuation_method)
and ev.application_id = l_appl_id
and ev.process_status_code in ('U','I','E')
and ev.event_status_code = 'U'
and ev.entity_id = te.entity_id
and trx.trx_reference_id = te.source_id_int_1
and bc.book_type_code = te.source_id_char_1
and bc.set_of_books_id = te.ledger_id
and not exists
(select /*+ no_unnest */ 1
from fa_adjustments adj
where adj.transaction_header_id in
(trx.src_transaction_header_id, trx.dest_transaction_header_id)
and adj.book_type_code = bc.book_type_code
and adj.adjustment_amount <> 0
and nvl(adj.track_member_flag, 'N') <> 'Y')
and not exists
(select /*+ no_unnest leading(th2) index(adj FA_ADJUSTMENTS_U1) */ 1
from fa_adjustments adj,
fa_transaction_headers th2
where th2.member_transaction_header_id in
(trx.src_transaction_header_id, trx.dest_transaction_header_id)
and adj.transaction_header_id = th2.transaction_header_id
and adj.book_type_code = bc.book_type_code
and adj.adjustment_amount <> 0
and nvl(adj.track_member_flag, 'N') <> 'Y');
'Rows inserted into gt for non-accountable - intertrx: ' || to_char(l_inter_trx_count));
'calling: ' || 'xla_events_pub_pkg.update_bulk_event_statuses');
xla_events_pub_pkg.update_bulk_event_statuses(p_application_id => 140);
END update_nonaccountable_events;