[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
' UPDATE xla_events xle
SET xle.event_status_code = ''P''
,xle.process_status_code = ''P''
,xle.last_update_date = sysdate
,xle.last_updated_by = fnd_global.user_id
,xle.last_update_login = fnd_global.login_id
,xle.program_id = fnd_global.conc_program_id
,xle.request_id = nvl(fnd_global.conc_request_id,0)
,xle.program_application_id = fnd_global.prog_appl_id
WHERE event_type_code <> ''MANUAL''
AND event_type_code in
(SELECT xetb.event_type_code
FROM gl_ledgers glg,
xla_acctg_methods_b xam,
xla_acctg_method_rules xamr,
xla_prod_acct_headers xpah,
xla_event_types_b xetb
WHERE glg.sla_accounting_method_code = xam.accounting_method_code
AND glg.sla_accounting_method_type = xam.accounting_method_type_code
AND xam.accounting_method_code = xamr.accounting_method_code
AND xam.accounting_method_type_code =
xamr.accounting_method_type_code
AND xamr.application_id = xle.application_id
AND xetb.application_id = xpah.application_id
AND xetb.entity_code = xpah.entity_code
AND xetb.event_class_code = xpah.event_class_code
AND (substr(xpah.event_type_code,-4) = ''_ALL''
OR xetb.event_type_code = xpah.event_type_code)
AND (NVL(xam.enabled_flag,''N'') <> ''Y''
-- OR xle.event_date < xamr.start_date_active
-- OR xle.event_date > xamr.end_date_active
OR NVL(xpah.accounting_required_flag,''N'') <> ''Y'')
AND xpah.application_id = xamr.application_id
AND xpah.product_rule_type_code = xamr.product_rule_type_code
AND xpah.product_rule_code = xamr.product_rule_code
AND xpah.amb_context_code = xamr.amb_context_code
AND xpah.amb_context_code =
NVL(xla_profiles_pkg.get_value(''XLA_AMB_CONTEXT''),''DEFAULT'')
AND glg.ledger_id IN ($ledger_ids$))
AND xle.event_status_code IN ( ''U'',''I'')
AND xle.process_status_code IN (''I'',''U'',''R'',''D'',''E'')
AND $filters$';
( p_msg => 'Number of events updated'||SQL%ROWCOUNT
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
SELECT gjst.je_source_name
INTO p_je_source_name
FROM xla_subledgers xls, gl_je_sources_tl gjst
WHERE xls.application_id = p_application_id
AND xls.je_source_name = gjst.je_source_name
AND gjst.language = USERENV('LANG');
SELECT start_date, end_date
INTO p_start_date, p_end_date
FROM gl_period_statuses glp
WHERE glp.period_name = p_period_from
AND glp.ledger_id = p_ledger_id
AND glp.adjustment_period_flag = 'N'
AND glp.application_id = gl_appl_id ;
SELECT end_date
INTO p_end_date
FROM gl_period_statuses glp
WHERE glp.period_name = p_period_to
AND glp.ledger_id = p_ledger_id
AND glp.adjustment_period_flag = 'N'
AND glp.application_id = gl_appl_id ;
' SELECT ent.ledger_id LEDGER_ID
,gld.short_name LEDGER_SHORT_NAME
,gld.name LEDGER_NAME
,gld.description LEDGER_DESCRIPTION
,gld.currency_code LEDGER_CURRENCY
,gps.period_year PERIOD_YEAR
,gps.period_num PERIOD_NUMBER
,gps.period_name PERIOD_NAME
,xle.application_id APPLICATION_ID
,gjt.je_source_name JOURNAL_SOURCE
,gjt.user_je_source_name USER_JE_SOURCE
,xcl.event_class_code EVENT_CLASS_CODE
,xcl.name EVENT_CLASS_NAME
,gjct.je_category_name JOURNAL_CATEGORY_NAME
,gjct.user_je_category_name USER_JE_CATEGORY_NAME
,to_char(xle.event_date,''YYYY-MM-DD'') EVENT_DATE
,xle.event_id EVENT_ID
,xle.event_number EVENT_NUMBER
,fnu.user_id CREATED_BY
,fnu.user_name USER_NAME
,to_char(xle.last_update_date,''YYYY-MM-DD'') LAST_UPDATE_DATE
,to_char(xle.creation_date,''YYYY-MM-DD'') CREATION_DATE
,ent.transaction_number TRANSACTION_NUMBER
,to_char(xle.transaction_date,''YYYY-MM-DD'') TRANSACTION_DATE
,xle.on_hold_flag ON_HOLD_FLAG
,xlo2.meaning ON_HOLD
,xtt.event_type_code EVENT_TYPE_CODE
,xtt.name EVENT_TYPE_NAME
,NULL BALANCE_TYPE_CODE
,NULL BALANCE_TYPE
,xlo1.meaning PRINT_STATUS ';
AND NOT EXISTS (SELECT aeh.event_id
FROM XLA_AE_HEADERS aeh
WHERE aeh.application_id = xle.application_id
AND aeh.event_id = xle.event_id
)
AND ent.ledger_id IN ';
' SELECT /*+ leading(aeh) */ aeh.ledger_id LEDGER_ID
,gld.short_name LEDGER_SHORT_NAME
,gld.name LEDGER_NAME
,gld.description LEDGER_DESCRIPTION
,gld.currency_code LEDGER_CURRENCY
,gps.period_year PERIOD_YEAR
,gps.period_num PERIOD_NUMBER
,gps.period_name PERIOD_NAME
,xle.application_id APPLICATION_ID
,gjt.je_source_name JOURNAL_SOURCE
,gjt.user_je_source_name USER_JE_SOURCE
,xcl.event_class_code EVENT_CLASS_CODE
,xcl.name EVENT_CLASS_NAME
,gjct.je_category_name JOURNAL_CATEGORY_NAME
,gjct.user_je_category_name USER_JE_CATEGORY_NAME
,to_char(aeh.accounting_date,''YYYY-MM-DD'') EVENT_DATE
,xle.event_id EVENT_ID
,xle.event_number EVENT_NUMBER
,fnu.user_id CREATED_BY
,fnu.user_name USER_NAME
,to_char(aeh.last_update_date,''YYYY-MM-DD'') LAST_UPDATE_DATE
,to_char(aeh.creation_date,''YYYY-MM-DD'') CREATION_DATE
,ent.transaction_number TRANSACTION_NUMBER
,to_char(xle.transaction_date,''YYYY-MM-DD'') TRANSACTION_DATE
,xle.on_hold_flag ON_HOLD_FLAG
,xlo2.meaning ON_HOLD
,xet.event_type_code EVENT_TYPE_CODE
,xtt.name EVENT_TYPE_NAME
,aeh.balance_type_code BALANCE_TYPE_CODE
,xlo5.meaning BALANCE_TYPE
,xlo4.meaning PRINT_STATUS ';
'SELECT /*+ index(ent XLA_TRANSACTION_ENTITIES_U1) */ DISTINCT
xle.application_id APPLICATION_ID
,xcl.entity_code ENTITY_CODE
,xcl.event_class_code EVENT_CLASS_CODE
,gjct.reporting_view_name REPORTING_VIEW_NAME
FROM xla_events xle
,xla_event_types_b xcl
,xla_event_class_attrs gjct
,xla_transaction_entities ent
,xla_ledger_options xlo
WHERE ent.entity_id = xle.entity_id
AND ent.application_id = xle.application_id
AND ent.ledger_id = xlo.ledger_id
AND ent.application_id = xlo.application_id
AND xlo.capture_event_flag = ''Y''
AND xcl.application_id = xle.application_id
AND xcl.event_type_code = xle.event_type_code
AND xcl.entity_code NOT IN (''MANUAL'',''THIRD_PARTY_MERGE'')
AND gjct.application_id = xcl.application_id
AND gjct.entity_code = xcl.entity_code
AND gjct.event_class_code = xcl.event_class_code
AND xle.event_status_code IN (''I'',''U'')
AND xle.process_status_code IN (''U'',''D'',''E'',''R'',''I'')
AND ent.ledger_id IN $ledger_ids$
$event_filter$
UNION ALL
SELECT DISTINCT
aeh.application_id APPLICATION_ID
,xcl.entity_code ENTITY_CODE
,xcl.event_class_code EVENT_CLASS_CODE
,gjct.reporting_view_name REPORTING_VIEW_NAME
FROM xla_ae_headers aeh
,xla_event_types_b xcl
,xla_event_class_attrs gjct
,xla_transaction_entities ent
WHERE xcl.application_id = aeh.application_id
AND xcl.event_type_code = aeh.event_type_code
AND gjct.application_id = xcl.application_id
AND gjct.entity_code = xcl.entity_code
AND gjct.event_class_code = xcl.event_class_code
AND ent.entity_id = aeh.entity_id
AND ent.application_id = aeh.application_id
AND xcl.entity_code NOT IN (''MANUAL'',''THIRD_PARTY_MERGE'') -- bug 6896350
AND aeh.gl_transfer_status_code IN (''N'',''E'') -- bug 6896350
AND aeh.ledger_id IN $ledger_ids$
$header_filter$';
SELECT application_id
,entity_code
,event_class_code
,reporting_view_name
FROM xla_event_class_attrs;
(SELECT xid.transaction_id_col_name_1 trx_col_1
,xid.transaction_id_col_name_2 trx_col_2
,xid.transaction_id_col_name_3 trx_col_3
,xid.transaction_id_col_name_4 trx_col_4
,xid.source_id_col_name_1 src_col_1
,xid.source_id_col_name_2 src_col_2
,xid.source_id_col_name_3 src_col_3
,xid.source_id_col_name_4 src_col_4
,xem.column_name column_name
,xem.column_title PROMPT
,utc.data_type data_type
FROM xla_entity_id_mappings xid
,xla_event_mappings_vl xem
,user_tab_columns utc
WHERE xid.application_id = l_event_class_set(k).application_id
AND xid.entity_code = l_event_class_set(k).entity_code
AND xem.application_id = l_event_class_set(k).application_id
AND xem.entity_code = l_event_class_set(k).entity_code
AND xem.event_class_code = l_event_class_set(k).event_class_code
AND utc.table_name = l_event_class_set(k).reporting_view_name
AND utc.column_name = xem.column_name
ORDER BY xem.user_sequence)
LOOP
l_index := l_index + 1;
''' THEN ( SELECT '||l_col_string
||' FROM '||l_view_name ||' WHERE '|| l_join_string
||' )' ;
''' THEN ( SELECT '||l_col_string
||' FROM '||l_view_name ||' WHERE '|| l_join_string
||' )' ;
select distinct glr1.target_ledger_id ledger_id
from gl_ledger_relationships glr1
,gl_ledger_relationships glr2
where glr1.source_ledger_id = glr2.source_ledger_id
and glr1.application_id = glr2.application_id
and glr2.target_ledger_id = p_ledger_id
and glr2.application_id = 101
and (g_use_ledger_security = 'N'
or glr1.target_ledger_id in
(select led.ledger_id
from gl_ledgers led, gl_access_set_assignments aset
where aset.ledger_id = led.ledger_id
and aset.access_set_id in
(g_access_set_id, g_sec_access_set_id)));
SELECT distinct glr2.target_ledger_id ledger_id
FROM gl_ledger_set_assignments gla
,gl_ledger_relationships glr1
,gl_ledger_relationships glr2
WHERE gla.ledger_id = glr1.target_ledger_id
AND glr1.source_ledger_id = glr2.source_ledger_id
and glr1.application_id = glr2.application_id
AND gla.ledger_set_id = p_ledger_id
AND gla.ledger_id <> gla.ledger_set_id
AND glr1.application_id = 101
AND (g_use_ledger_security = 'N'
or glr2.target_ledger_id in
(SELECT led.ledger_id
FROM gl_ledgers led, gl_access_set_assignments aset
WHERE aset.ledger_id = led.ledger_id
AND aset.access_set_id in
(g_access_set_id, g_sec_access_set_id)));
SELECT ledger_id
INTO l_period_ledger_id
FROM gl_ledger_set_assignments
WHERE ledger_set_id = p_ledger_id
AND ledger_id <> p_ledger_id
AND ROWNUM = 1;
SELECT 1
INTO l_unprocessed
FROM dual
WHERE EXISTS(select 1
FROM xla_events xle
,xla_transaction_entities xte
,gl_ledger_relationships glr1
,gl_ledger_relationships glr2
,xla_ledger_options xlo
WHERE xle.entity_id = xte.entity_id
AND xle.application_id = xte.application_id
AND xle.event_date BETWEEN l_period_start_date and l_period_end_date
AND glr2.target_ledger_id = p_ledger_id
AND glr2.source_ledger_id = glr1.source_ledger_id
AND glr2.application_id = glr1.application_id
AND glr1.target_ledger_id = xlo.ledger_id
AND xle.application_id = xlo.application_id
AND xlo.capture_event_flag = 'Y'
AND (glr1.target_ledger_id = xte.ledger_id OR
glr1.primary_ledger_id = xte.ledger_id )
AND (glr1.relationship_type_code = 'SUBLEDGER' OR
(glr1.target_ledger_category_code = 'PRIMARY'
AND glr1.relationship_type_code = 'NONE'))
AND glr2.application_id = 101
AND xle.event_status_code IN ('I','U')
AND xle.process_status_code IN ('I','U','R','D','E'));
SELECT count(*)
INTO l_unprocessed
FROM xla_ae_headers aeh
,xla_transaction_entities xte
,gl_ledger_relationships glr1
,gl_ledger_relationships glr2
WHERE aeh.ledger_id = glr2.target_ledger_id
AND glr2.source_ledger_id = glr1.source_ledger_id
AND glr2.application_id = glr1.application_id
AND glr1.target_ledger_id = p_ledger_id
AND glr1.application_id = 101
AND xte.entity_id = aeh.entity_id
AND xte.application_id = aeh.application_id
AND aeh.gl_transfer_status_code IN ('N','E')
AND aeh.accounting_date BETWEEN
l_period_start_date AND l_period_end_date
AND rownum = 1;
SELECT 1
INTO l_unprocessed
FROM dual
WHERE EXISTS(select 1
FROM xla_events xle
,xla_transaction_entities xte
,gl_ledger_relationships glr1
,gl_ledger_relationships glr2
,xla_ledger_options xlo
WHERE xle.entity_id = xte.entity_id
AND xle.application_id = xte.application_id
AND xle.event_date BETWEEN l_period_start_date and l_period_end_date
AND xle.application_id = p_application_id
AND xle.event_status_code IN ('I','U')
AND xle.process_status_code IN ('I','U','R','D','E')
AND glr2.target_ledger_id = p_ledger_id
AND glr2.source_ledger_id = glr1.source_ledger_id
AND glr2.application_id = glr1.application_id
AND glr1.target_ledger_id = xlo.ledger_id
AND xle.application_id = xlo.application_id
AND xlo.capture_event_flag = 'Y'
AND (glr1.target_ledger_id = xte.ledger_id OR
glr1.primary_ledger_id = xte.ledger_id )
AND (glr1.relationship_type_code = 'SUBLEDGER' OR
(glr1.target_ledger_category_code = 'PRIMARY'
AND glr1.relationship_type_code = 'NONE'))
AND glr2.application_id = 101
AND xte.application_id = p_application_id
);
SELECT count(*)
INTO l_unprocessed
FROM xla_ae_headers aeh
,xla_transaction_entities xte
,gl_ledger_relationships glr1
,gl_ledger_relationships glr2
WHERE aeh.ledger_id = glr2.target_ledger_id
AND glr2.source_ledger_id = glr1.source_ledger_id
AND glr2.application_id = glr1.application_id
AND glr1.target_ledger_id = p_ledger_id
AND glr1.application_id = 101
AND xte.entity_id = aeh.entity_id
AND xte.application_id = aeh.application_id
AND aeh.gl_transfer_status_code IN ('N','E')
AND aeh.accounting_date BETWEEN
l_period_start_date AND l_period_end_date
AND xte.application_id = p_application_id
AND rownum = 1;