[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| all the update APIs. |
| Removed date validation calls and truncated |
| event date in all the APIs |
| 18-Mar-02 S. Singhania Modified the Bulk APIs to improve performance |
| by using a "Temporary table" |
| 08-Apr-02 S. Singhania Removed the not required, redundant APIs |
| 19-Apr-02 S. Singhania Added seperate API to update transaction |
| number. modified the APIs based on changes |
| in "event_source_info" & "entity_source_info |
| 08-May-02 S. Singhania Made changes in the routines to include: |
| NOT NULL ledger_id, NULL valuation_method. |
| legal_entity_id is made NULL in XLA_ENTITIES |
| Removed "legal_entity_id" from XLA_EVENTS |
| Bug # 2351677 |
| 14-May-02 S. Singhania Modified "event_exists" routine and changed |
| cursors in 'cache_application_setups'. |
| 31-May-02 S. Singhania Made changes based on Bug # 2392835. Updated |
| code at all the places to make sure |
| 'source_id_date_n' is not used. |
| Changes based on Bug # 2385846. Changes made |
| to support XLA_ENTITY_ID_MAPPINGS column |
| name changes. Modified APIs 'update_event' |
| and 'update_event_status' to update |
| 'process_status' with 'event_status' |
| Renamed 'create_entity_event' API to |
| 'create_bulk_events' |
| 14-Jun-02 S. Singhania Added the bulk API, 'update_event_status_bulk'|
| to update event/entity status in bulk. This |
| API will be called by Accounting Program |
| 18-Jul-02 S. Singhania Added curosr in 'cache_application_setup' to |
| cache application from xla_subledgers. |
| Commented 'validate_event_date' routine. Date |
| validation is not needed. |
| Commented 'update_entity_status' and 'evaluate|
| _entity_status'. Bug # 2464825. Removed |
| reference to g_entity_status_code. |
| Cleaned up Exception messages. |
| 23-Jul-02 S. Singhania Modified code to handle to issue of 'enabled |
| flags'.( see DLD closed issues). |
| 14-Aug-02 S. Singhania Changed XLA_ENTITES and XLA_ENTITIES_S to |
| XLA_TRANSACTION_ENTITIES and |
| XLA_TRANSACTION_ENTITIES_S |
| 09-Sep-02 S. Singhania Made changes to 'cache_entity_info' to handle |
| MANUAL events. Bug # 2529997. |
| 09-Sep-02 S. Singhania modified 'create_bulk_events' routine (with |
| single array) to handle entities belonging |
| to multiple security contexts. Bug # 2530796 |
| 08-Nov-02 S. Singhania Included and verified 'get_entity_id' API for |
| 'document mode' Accounting Program |
| 21-Feb-03 S. Singhania Added 'Trace' procedure. |
| 10-Apr-03 S. Singhania Made changes due to change in temporary table |
| name (bug # 2897261) |
| 12-Jun-03 S. Singhania Fixed FND Messages (bug # 3001156). |
| Removed commented APIs. |
| 10-Jul-03 S. Singhania Added new APIs for MANUAL events (2899700) |
| - UPDATE_MANUAL_EVENT |
| - CREATE_MANUAL_EVENT |
| - DELETE_PROCESSED_EVENT |
| modified other internal routines to handle the|
| the case of MANUAL events. |
| removed update_event_status_bulk API |
| (accounting program do not use this anymore)|
| 12-Aug-03 S. Singhania Fixed a typo in GET_ID_MAPPING |
| 21-Aug-03 S. Singhania Enhanced the following APIs to fix 2701681 |
| - update_event_status |
| - update_event |
| - delete_event |
| - delete_events |
| - delete_processed_event |
| 28-Aug-03 S. Singhania Modified UPDATE_EVENT to fix 3111204 |
| 04-Sep-03 S. Singhania Enhanced APIs to support 'Source Application':|
| - Added parameter p_source_application_id to|
| CREATE_BULK_EVENTS API |
| - Added validation for source application in|
| CREATE_EVENT and CREATE_BULK_EVENTS |
| - Modified the insert statment to insert |
| source application in CREATE_ENTITY_EVENT |
| 05-Sep-03 S. Singhania To improve performance, the structures to |
| store event_types, event_classes and |
| entity_types are modified. Following were |
| impacted: |
| - CACHE_APPLICATION_SETUP |
| - VALIDATE_EVENT_ENTITY_CODE |
| - VALIDATE_EVNENT_CLASS_CODE |
| - VALIDATE_EVNET_TYPE_CODE. |
| 12-Dec-03 S. Singhania Bug # 3268790. |
| - Modified cursors in CACHE_ENTITY_INFO not |
| to lock rows in xla_transaction_entities. |
| - Routines DELETE_EVENTS and DELETE_EVENT |
| are modified not to delete entites when |
| last STANDARD event is deleted for the |
| entity. |
| 04-Mar-04 W. Shen Gapless event processing project |
| 25-Mar-04 W. Shen add trace |
| 23-Jun-04 W. Shen New API delete_entity to delete entities |
| from xla_transaction_entities(bug 3316535) |
| 10-Aug-04 S. Singhania Added trace messages to help debug the code |
| 23-OCT-04 W. Shen New API to delete/update/create event in bulk |
| 09-Nov-04 S. Singhania Made chnages for valuation method enhancements|
| Following routines were modified: |
| - CACHE_APPLICATION_SETUP |
| - RESET_CACHE |
| - VALIDATE_CONTEXT |
| - VALIDATE_LEDGER (New routine added) |
| 23-OCT-04 W. Shen bulk delete API, when delete transaction |
| entities, make sure only delete those |
| affected by the batch. |
| 1- APR-05 W. Shen Add transaction_date to the following API: |
| create_event, create_manual_event |
| create_bulk_events(two of them) |
| update_event |
| 20-Apr-05 S. Singhania Bug 4312353. |
| - Modified signature of routines in to reflect|
| the change in the way we handle valuation |
| method different from other security columns|
| - The major impact is on the following: |
| - SOURCE_INFO_CHANGED |
| - CACHE_ENTITY_INFO |
| - CREATE_BULK_EVENTS |
| 02-May-05 V. Kumar Removed function create_bulk_events, |
| Bug # 4323140 |
| 22-Jul-05 Swapna Vellani Modified an insert statement in |
| create_bulk_events procedure Bug #4458604 |
| 2- Aug-05 W. Shen remove the validation for p_source_app_id |
| bug 4526089 |
| 30- Aug-05 W. Shen when no entity exists, event_exists will |
| return false instead of raising exception |
| bug 4529563 |
| 30-Aug-05 S. Singhania Bug 4519181: Added call to |
| XLA_SECURITY_PKG.SET_SECURITY_CONTEXT to |
| each public API. |
+===========================================================================*/
--=============================================================================
-- **************** declaraions ********************
--=============================================================================
-------------------------------------------------------------------------------
-- declaring private constants
-------------------------------------------------------------------------------
C_YES CONSTANT VARCHAR2(1) := 'Y'; -- yes flag
C_EVENT_DELETE CONSTANT VARCHAR2(1) := 'D';
C_EVENT_UPDATE CONSTANT VARCHAR2(1) := 'U';
PROCEDURE update_entity_trx_number
(p_transaction_number IN VARCHAR2);
PROCEDURE delete_je;
SELECT application_id
FROM xla_subledgers
WHERE application_id = p_event_source_info.source_application_id;
update_entity_trx_number
(p_transaction_number => p_event_source_info.transaction_number);
update_entity_trx_number
(p_transaction_number => p_event_source_info.transaction_number);
PROCEDURE update_event_status
(p_event_source_info IN xla_events_pub_pkg.t_event_source_info
,p_valuation_method IN VARCHAR2
,p_event_class_code IN VARCHAR2 DEFAULT NULL
,p_event_type_code IN VARCHAR2 DEFAULT NULL
,p_event_date IN DATE DEFAULT NULL
,p_event_status_code IN VARCHAR2) IS
l_event_date DATE;
l_log_module := C_DEFAULT_MODULE||'.update_event_status';
(p_msg => 'BEGIN of procedure update_event_status'
,p_level => C_LEVEL_PROCEDURE
,p_module =>l_log_module);
SAVEPOINT before_event_update;
g_action := C_EVENT_UPDATE;
,p_value_2 => 'xla_events_pkg.update_event_status');
,p_value_2 => 'xla_events_pkg.update_event_status');
SELECT event_id BULK COLLECT
INTO l_array_events
FROM xla_events
WHERE event_status_code <> xla_events_pub_pkg.C_EVENT_PROCESSED
AND event_date = NVL(l_event_date, event_date)
AND event_type_code = NVL(p_event_type_code, event_type_code)
AND entity_id = g_entity_id
AND event_type_code IN
(SELECT event_type_code
FROM xla_event_types_b
WHERE application_id = g_application_id
AND entity_code = g_entity_type_code
AND event_class_code = NVL(p_event_class_code,
event_class_code));
SELECT event_id,
event_status_code,
on_hold_flag,
event_number BULK COLLECT
INTO l_array_events,
l_array_event_status,
l_array_on_hold_flag,
l_array_event_number
FROM xla_events
WHERE event_status_code <> xla_events_pub_pkg.C_EVENT_PROCESSED
AND event_date = NVL(l_event_date, event_date)
AND event_type_code = NVL(p_event_type_code, event_type_code)
AND entity_id = g_entity_id
AND event_type_code IN
(SELECT event_type_code
FROM xla_event_types_b
WHERE application_id = g_application_id
AND entity_code = g_entity_type_code
AND event_class_code = NVL(p_event_class_code,
event_class_code))
Order by event_number;
xla_journal_entries_pkg.delete_journal_entries
(p_event_id => l_array_events(i)
,p_application_id => g_application_id);
UPDATE xla_events
SET event_status_code = p_event_status_code
,process_status_code = C_INTERNAL_UNPROCESSED
,last_update_date = sysdate
,last_updated_by = xla_environment_pkg.g_usr_id
,last_update_login = xla_environment_pkg.g_login_id
,program_update_date = sysdate
,program_application_id = xla_environment_pkg.g_prog_appl_id
,program_id = xla_environment_pkg.g_prog_id
,request_id = xla_environment_pkg.g_Req_Id
WHERE event_id = l_array_events(i);
update xla_events
set on_hold_flag='Y'
where entity_id=g_entity_id
and event_number >l_array_event_number(1)
and on_hold_flag='N';
SELECT event_status_code, event_number BULK COLLECT
INTO g_gapless_array_event_status, g_gapless_event_number
FROM xla_events
Where entity_id = g_entity_id
and event_number>l_array_event_number(i)
Order by event_number;
update xla_events
set on_hold_flag='N'
where entity_id=g_entity_id
and event_number >l_array_event_number(i)
and event_number
(p_msg => 'end of procedure update_event_status'
,p_level => C_LEVEL_PROCEDURE
,p_module =>l_log_module);
ROLLBACK to SAVEPOINT before_event_update;
ROLLBACK to SAVEPOINT before_event_update;
(p_location => 'xla_events_pkg.update_event_status');
END update_event_status;
PROCEDURE update_event
(p_event_source_info IN xla_events_pub_pkg.t_event_source_info
,p_valuation_method IN VARCHAR2
,p_event_id IN INTEGER
,p_event_type_code IN VARCHAR2 DEFAULT NULL
,p_event_date IN DATE DEFAULT NULL
,p_event_status_code IN VARCHAR2 DEFAULT NULL
,p_transaction_date IN DATE DEFAULT NULL
,p_event_number IN INTEGER DEFAULT NULL
,p_reference_info IN xla_events_pub_pkg.t_event_reference_info
DEFAULT NULL
,p_overwrite_event_num IN VARCHAR2 DEFAULT 'N'
,p_overwrite_ref_info IN VARCHAR2 DEFAULT 'N') IS
l_event_date DATE;
l_log_module := C_DEFAULT_MODULE||'.update_event';
(p_msg => 'BEGIN of procedure update_event'
,p_level => C_LEVEL_PROCEDURE
,p_module =>l_log_module);
SAVEPOINT before_event_update;
g_action := C_EVENT_UPDATE;
,p_value_2 => 'xla_events_pkg.update_event');
,p_value_2 => 'xla_events_pkg.update_event');
,p_value_2 => 'xla_events_pkg.update_event');
,p_value_2 => 'xla_events_pkg.update_event');
xla_journal_entries_pkg.delete_journal_entries
(p_event_id => p_event_id
,p_application_id => g_application_id);
select event_status_code, on_hold_flag, event_number
into l_old_event_status_code, l_old_on_hold_flag, l_old_event_number
from xla_events
WHERE event_id = p_event_id
AND entity_id = g_entity_id
AND event_status_code <> xla_events_pub_pkg.C_EVENT_PROCESSED;
'Unable to perform UPDATE on the event. The event ('||p_event_id ||
') is either invalid or has been final accounted.'
,p_token_2 => 'LOCATION'
,p_value_2 => 'xla_events_pkg.update_event');
UPDATE xla_events
SET event_type_code = NVL(p_event_type_code , event_type_code)
,event_date = NVL(l_event_date , event_date)
,transaction_date = NVL(p_transaction_date , transaction_date)
,event_status_code = NVL(p_event_status_code, event_status_code)
,process_status_code = NVL(l_process_status_code,process_status_code)
,event_number = DECODE(p_overwrite_event_num,C_YES,
NVL(p_event_number,g_max_event_number+1),event_number)
,reference_num_1 = DECODE(p_overwrite_ref_info,C_YES,
p_reference_info.reference_num_1,reference_num_1)
,reference_num_2 = DECODE(p_overwrite_ref_info,C_YES,
p_reference_info.reference_num_2,reference_num_2)
,reference_num_3 = DECODE(p_overwrite_ref_info,C_YES,
p_reference_info.reference_num_3,reference_num_3)
,reference_num_4 = DECODE(p_overwrite_ref_info,C_YES,
p_reference_info.reference_num_4,reference_num_4)
,reference_char_1 = DECODE(p_overwrite_ref_info,C_YES,
p_reference_info.reference_char_1,reference_char_1)
,reference_char_2 = DECODE(p_overwrite_ref_info,C_YES,
p_reference_info.reference_char_2,reference_char_2)
,reference_char_3 = DECODE(p_overwrite_ref_info,C_YES,
p_reference_info.reference_char_3,reference_char_3)
,reference_char_4 = DECODE(p_overwrite_ref_info,C_YES,
p_reference_info.reference_char_4,reference_char_4)
,reference_date_1 = DECODE(p_overwrite_ref_info,C_YES,
p_reference_info.reference_date_1,reference_date_1)
,reference_date_2 = DECODE(p_overwrite_ref_info,C_YES,
p_reference_info.reference_date_2,reference_date_2)
,reference_date_3 = DECODE(p_overwrite_ref_info,C_YES,
p_reference_info.reference_date_3,reference_date_3)
,reference_date_4 = DECODE(p_overwrite_ref_info,C_YES,
p_reference_info.reference_date_4,reference_date_4)
,last_update_date = sysdate
,last_updated_by = xla_environment_pkg.g_usr_id
,last_update_login = xla_environment_pkg.g_login_id
,program_update_date = sysdate
,program_application_id= xla_environment_pkg.g_prog_appl_id
,program_id = xla_environment_pkg.g_prog_id
,request_id = xla_environment_pkg.g_Req_Id
WHERE event_id = p_event_id
AND entity_id = g_entity_id
AND event_status_code <> xla_events_pub_pkg.C_EVENT_PROCESSED;
'Unable to perform UPDATE on the event. The event ('||p_event_id ||
') is either invalid or has been final accounted.'
,p_token_2 => 'LOCATION'
,p_value_2 => 'xla_events_pkg.update_event');
update xla_events
set on_hold_flag='Y'
where entity_id=g_entity_id
and event_status_code<> xla_events_pub_pkg.C_EVENT_PROCESSED
and event_number>l_old_event_number;
update xla_events
set on_hold_flag='Y'
where entity_id=g_entity_id
and event_id=p_event_id;
select event_status_code, on_hold_flag
into l_event_status_code, l_on_hold_flag
from xla_events
where entity_id=g_entity_id
and event_number=p_event_number-1;
update xla_events
set on_hold_flag='N'
where event_id=p_event_id;
SELECT event_status_code, event_number BULK COLLECT
INTO g_gapless_array_event_status, g_gapless_event_number
FROM xla_events
Where entity_id = g_entity_id
and event_number>p_event_number
and event_number
update xla_events
set on_hold_flag='N'
where entity_id=g_entity_id
and event_number >p_event_number-1
and event_number
else --event number is not updated, but the status changed
if(p_event_status_code='I' and l_old_on_hold_flag='N') then
-- new gap
update xla_events
set on_hold_flag='Y'
where entity_id=g_entity_id
and event_number>l_old_event_number
and on_hold_flag='N';
SELECT event_status_code, event_number BULK COLLECT
INTO g_gapless_array_event_status, g_gapless_event_number
FROM xla_events
Where entity_id = g_entity_id
and event_number>l_old_event_number
Order by event_number;
update xla_events
set on_hold_flag='N'
where entity_id=g_entity_id
and event_number >l_old_event_number
and event_number
(p_msg => 'end of procedure update_event'
,p_level => C_LEVEL_PROCEDURE
,p_module =>l_log_module);
ROLLBACK to SAVEPOINT before_event_update;
ROLLBACK to SAVEPOINT before_event_update;
(p_location => 'xla_events_pkg.update_event');
END update_event;
PROCEDURE update_manual_event
(p_event_source_info IN xla_events_pub_pkg.t_event_source_info
,p_event_id IN INTEGER
,p_event_type_code IN VARCHAR2 DEFAULT NULL
,p_event_date IN DATE DEFAULT NULL
,p_event_status_code IN VARCHAR2 DEFAULT NULL
,p_process_status_code IN VARCHAR2 DEFAULT NULL
,p_event_number IN INTEGER DEFAULT NULL
,p_reference_info IN xla_events_pub_pkg.t_event_reference_info
DEFAULT NULL
,p_overwrite_event_num IN VARCHAR2 DEFAULT 'N'
,p_overwrite_ref_info IN VARCHAR2 DEFAULT 'N') IS
l_event_date DATE;
l_log_module := C_DEFAULT_MODULE||'.update_manual_event';
(p_msg => 'BEGIN of procedure update_manual_event'
,p_level => C_LEVEL_PROCEDURE
,p_module =>l_log_module);
SAVEPOINT before_event_update;
g_action := C_EVENT_UPDATE;
,p_value_2 => 'xla_events_pkg.update_manual_event');
,p_value_2 => 'xla_events_pkg.update_manual_event');
,p_value_2 => 'xla_events_pkg.update_manual_event');
,p_value_2 => 'xla_events_pkg.update_manual_event');
UPDATE xla_events
SET event_type_code = NVL(p_event_type_code , event_type_code)
,event_date = NVL(l_event_date , event_date)
,event_status_code = NVL(p_event_status_code, event_status_code)
,process_status_code = NVL(p_process_status_code,process_status_code)
,event_number = DECODE(p_overwrite_event_num,C_YES,
NVL(p_event_number,g_max_event_number+1),event_number)
,reference_num_1 = DECODE(p_overwrite_ref_info,C_YES,
p_reference_info.reference_num_1,reference_num_1)
,reference_num_2 = DECODE(p_overwrite_ref_info,C_YES,
p_reference_info.reference_num_2,reference_num_2)
,reference_num_3 = DECODE(p_overwrite_ref_info,C_YES,
p_reference_info.reference_num_3,reference_num_3)
,reference_num_4 = DECODE(p_overwrite_ref_info,C_YES,
p_reference_info.reference_num_4,reference_num_4)
,reference_char_1 = DECODE(p_overwrite_ref_info,C_YES,
p_reference_info.reference_char_1,reference_char_1)
,reference_char_2 = DECODE(p_overwrite_ref_info,C_YES,
p_reference_info.reference_char_2,reference_char_2)
,reference_char_3 = DECODE(p_overwrite_ref_info,C_YES,
p_reference_info.reference_char_3,reference_char_3)
,reference_char_4 = DECODE(p_overwrite_ref_info,C_YES,
p_reference_info.reference_char_4,reference_char_4)
,reference_date_1 = DECODE(p_overwrite_ref_info,C_YES,
p_reference_info.reference_date_1,reference_date_1)
,reference_date_2 = DECODE(p_overwrite_ref_info,C_YES,
p_reference_info.reference_date_2,reference_date_2)
,reference_date_3 = DECODE(p_overwrite_ref_info,C_YES,
p_reference_info.reference_date_3,reference_date_3)
,reference_date_4 = DECODE(p_overwrite_ref_info,C_YES,
p_reference_info.reference_date_4,reference_date_4)
,last_update_date = sysdate
,last_updated_by = xla_environment_pkg.g_usr_id
,last_update_login = xla_environment_pkg.g_login_id
,program_update_date = sysdate
,program_application_id= xla_environment_pkg.g_prog_appl_id
,program_id = xla_environment_pkg.g_prog_id
,request_id = xla_environment_pkg.g_Req_Id
WHERE event_id = p_event_id
AND entity_id = g_entity_id
AND event_status_code <> xla_events_pub_pkg.C_EVENT_PROCESSED;
(p_msg => 'Number for rows updated = '||l_rowcount
,p_level => C_LEVEL_STATEMENT
,p_module =>l_log_module);
'Unable to perform UPDATE on the event. The event ('||p_event_id ||
') is invalid'
,p_token_2 => 'LOCATION'
,p_value_2 => 'xla_events_pkg.update_manual_event');
(p_msg => 'end of procedure update_manual_event'
,p_level => C_LEVEL_PROCEDURE
,p_module =>l_log_module);
ROLLBACK to SAVEPOINT before_event_update;
ROLLBACK to SAVEPOINT before_event_update;
(p_location => 'xla_events_pkg.update_manual_event');
END update_manual_event;
PROCEDURE delete_event
(p_event_source_info IN xla_events_pub_pkg.t_event_source_info
,p_valuation_method IN VARCHAR2
,p_event_id IN INTEGER) IS
l_on_hold_flag xla_events.on_hold_flag%type;
l_log_module := C_DEFAULT_MODULE||'.delete_event';
(p_msg => 'BEGIN of procedure delete_event'
,p_level => C_LEVEL_PROCEDURE
,p_module =>l_log_module);
SAVEPOINT before_event_delete;
g_action := C_EVENT_DELETE;
,p_value_2 => 'xla_events_pkg.delete_event');
,p_value_2 => 'xla_events_pkg.delete_event');
xla_journal_entries_pkg.delete_journal_entries
(p_event_id => p_event_id
,p_application_id => g_application_id);
select on_hold_flag, event_status_code, event_number
into l_on_hold_flag, l_event_status_code, l_event_number
from xla_events
WHERE event_id = p_event_id
AND entity_id = g_entity_id
AND event_status_code <> xla_events_pub_pkg.C_EVENT_PROCESSED;
'Unable to perform DELETE on the event. The event ('|| p_event_id ||
') is either invalid or has been final accounted.'
,p_token_2 => 'LOCATION'
,p_value_2 => 'xla_events_pkg.delete_event');
DELETE xla_events
WHERE event_id = p_event_id
AND entity_id = g_entity_id
AND event_status_code <> xla_events_pub_pkg.C_EVENT_PROCESSED;
(p_msg => 'Number for events deleted = '||l_rowcount
,p_level => C_LEVEL_STATEMENT
,p_module =>l_log_module);
'Unable to perform DELETE on the event. The event ('|| p_event_id ||
') is either invalid or has been final accounted.'
,p_token_2 => 'LOCATION'
,p_value_2 => 'xla_events_pkg.delete_event');
DELETE xla_transaction_entities xte
WHERE xte.entity_id = g_entity_id
AND xte.application_id = g_application_id
AND NOT EXISTS
(SELECT '1' FROM xla_events xe
WHERE xe.entity_id = xte.entity_id
AND xe.application_id = xte.application_id);
(p_msg => 'Number for entities deleted = '||l_rowcount
,p_level => C_LEVEL_STATEMENT
,p_module =>l_log_module);
update xla_events
set on_hold_flag='Y'
where entity_id=g_entity_id
and event_number>l_event_number;
(p_msg => 'end of procedure delete_event'
,p_level => C_LEVEL_PROCEDURE
,p_module =>l_log_module);
ROLLBACK to SAVEPOINT before_event_delete;
ROLLBACK to SAVEPOINT before_event_delete;
(p_location => 'xla_events_pkg.delete_event');
END delete_event;
PROCEDURE delete_processed_event
(p_event_source_info IN xla_events_pub_pkg.t_event_source_info
,p_event_id IN INTEGER) IS
l_log_module VARCHAR2(240);
l_log_module := C_DEFAULT_MODULE||'.delete_processed_event';
(p_msg => 'BEGIN of procedure delete_processed_event'
,p_level => C_LEVEL_PROCEDURE
,p_module =>l_log_module);
trace('> xla_events_pkg.delete_processed_event' , 20);
SAVEPOINT before_event_delete;
g_action := C_EVENT_DELETE;
,p_value_2 => 'xla_events_pkg.delete_processed_event');
,p_value_2 => 'xla_events_pkg.delete_processed_event');
,p_value_2 => 'xla_events_pkg.delete_processed_event');
xla_journal_entries_pkg.delete_journal_entries
(p_event_id => p_event_id
,p_application_id => g_application_id);
DELETE xla_events
WHERE event_id = p_event_id
AND entity_id = g_entity_id
AND event_status_code = xla_events_pub_pkg.C_EVENT_PROCESSED;
(p_msg => 'Number for events deleted = '||l_rowcount
,p_level => C_LEVEL_STATEMENT
,p_module =>l_log_module);
'Unable to perform DELETE on the event. The event ('|| p_event_id ||
') is either invalid or has not been final accounted.'
,p_token_2 => 'LOCATION'
,p_value_2 => 'xla_events_pkg.delete_processed_event');
DELETE xla_transaction_entities xte
WHERE xte.entity_id = g_entity_id
AND xte.application_id = g_application_id
AND NOT EXISTS
(SELECT '1' FROM xla_events xe
WHERE xe.entity_id = xte.entity_id
AND xe.application_id = xte.application_id);
(p_msg => 'Number for entities deleted = '||l_rowcount
,p_level => C_LEVEL_STATEMENT
,p_module =>l_log_module);
(p_msg => 'BEGIN of procedure delete_processed_event'
,p_level => C_LEVEL_PROCEDURE
,p_module =>l_log_module);
ROLLBACK to SAVEPOINT before_event_delete;
ROLLBACK to SAVEPOINT before_event_delete;
(p_location => 'xla_events_pkg.delete_processed_event');
END delete_processed_event;
FUNCTION delete_events
(p_event_source_info IN xla_events_pub_pkg.t_event_source_info
,p_valuation_method IN VARCHAR2
,p_event_class_code IN VARCHAR2 DEFAULT NULL
,p_event_type_code IN VARCHAR2 DEFAULT NULL
,p_event_date IN DATE DEFAULT NULL)
RETURN INTEGER IS
l_event_deleted INTEGER;
l_log_module := C_DEFAULT_MODULE||'.delete_events';
(p_msg => 'BEGIN of procedure delete_events'
,p_level => C_LEVEL_PROCEDURE
,p_module =>l_log_module);
SAVEPOINT before_event_delete;
g_action := C_EVENT_DELETE;
,p_value_2 => 'xla_events_pkg.delete_events');
,p_value_2 => 'xla_events_pkg.delete_events (fn)');
SELECT event_id BULK COLLECT
INTO l_array_events
FROM xla_events
WHERE event_date = NVL(l_event_date, event_date)
AND event_status_code <> xla_events_pub_pkg.C_EVENT_PROCESSED
AND event_type_code = NVL(p_event_type_code, event_type_code)
AND entity_id = g_entity_id
AND event_type_code IN
(SELECT event_type_code
FROM xla_event_types_b
WHERE application_id = g_application_id
AND entity_code = g_entity_type_code
AND event_class_code = NVL(p_event_class_code,
event_class_code));
SELECT event_id, event_number BULK COLLECT
INTO l_array_events, g_gapless_event_number
FROM xla_events
WHERE event_date = NVL(l_event_date, event_date)
AND event_status_code <> xla_events_pub_pkg.C_EVENT_PROCESSED
AND event_type_code = NVL(p_event_type_code, event_type_code)
AND entity_id = g_entity_id
AND event_type_code IN
(SELECT event_type_code
FROM xla_event_types_b
WHERE application_id = g_application_id
AND entity_code = g_entity_type_code
AND event_class_code = NVL(p_event_class_code,
event_class_code))
order by event_number;
xla_journal_entries_pkg.delete_journal_entries
(p_event_id => l_array_events(i)
,p_application_id => g_application_id);
DELETE xla_events
WHERE event_id = l_array_events(i);
l_event_deleted := SQL%ROWCOUNT;
(p_msg => 'Number of events deleted = '||l_event_deleted
,p_level => C_LEVEL_STATEMENT
,p_module =>l_log_module);
if(g_gapless_flag='Y' and l_event_deleted>0 ) then
update xla_events
set on_hold_flag='Y'
where event_number>g_gapless_event_number(1)
and on_hold_flag='N'
and entity_id=g_entity_id;
(p_msg => 'end of procedure delete_events'
,p_level => C_LEVEL_PROCEDURE
,p_module =>l_log_module);
(p_msg => 'return value:'||to_char(l_event_deleted)
,p_level => C_LEVEL_PROCEDURE
,p_module =>l_log_module);
RETURN l_event_deleted;
ROLLBACK to SAVEPOINT before_event_delete;
ROLLBACK to SAVEPOINT before_event_delete;
(p_location => 'xla_events_pkg.delete_events (fn)');
END delete_events;
FUNCTION delete_entity
(p_source_info IN xla_events_pub_pkg.t_event_source_info
,p_valuation_method IN VARCHAR2)
RETURN INTEGER IS
l_log_module VARCHAR2(240);
select 1
from xla_events
where entity_id=l_entity_id;
l_log_module := C_DEFAULT_MODULE||'.delete_entity';
(p_msg => 'BEGIN of procedure delete_entity'
,p_level => C_LEVEL_PROCEDURE
,p_module =>l_log_module);
SAVEPOINT before_entity_delete;
SELECT entity_id
INTO l_entity_id
FROM xla_transaction_entities a
WHERE a.application_id = p_source_info.application_id
AND a.ledger_id = p_source_info.ledger_id
AND a.entity_code = p_source_info.entity_type_code
AND NVL(a.source_id_int_1,C_NUM) =
NVL(p_source_info.source_id_int_1,C_NUM)
AND NVL(a.source_id_int_2,C_NUM) =
NVL(p_source_info.source_id_int_2,C_NUM)
AND NVL(a.source_id_int_3,C_NUM) =
NVL(p_source_info.source_id_int_3,C_NUM)
AND NVL(a.source_id_int_4,C_NUM) =
NVL(p_source_info.source_id_int_4,C_NUM)
AND NVL(a.source_id_char_1,C_CHAR) =
NVL(p_source_info.source_id_char_1,C_CHAR)
AND NVL(a.source_id_char_2,C_CHAR) =
NVL(p_source_info.source_id_char_2,C_CHAR)
AND NVL(a.source_id_char_3,C_CHAR) =
NVL(p_source_info.source_id_char_3,C_CHAR)
AND NVL(a.source_id_char_4,C_CHAR) =
NVL(p_source_info.source_id_char_4,C_CHAR)
AND NVL(a.valuation_method,C_CHAR) =
NVL(p_valuation_method,C_CHAR)
AND NVL(a.security_id_int_1,C_NUM) =
NVL(xla_events_pub_pkg.g_security.security_id_int_1,C_NUM)
AND NVL(a.security_id_int_2,C_NUM) =
NVL(xla_events_pub_pkg.g_security.security_id_int_2,C_NUM)
AND NVL(a.security_id_int_3,C_NUM) =
NVL(xla_events_pub_pkg.g_security.security_id_int_3,C_NUM)
AND NVL(a.security_id_char_1,C_CHAR) =
NVL(xla_events_pub_pkg.g_security.security_id_char_1,C_CHAR)
AND NVL(a.security_id_char_2,C_CHAR) =
NVL(xla_events_pub_pkg.g_security.security_id_char_2,C_CHAR)
AND NVL(a.security_id_char_3,C_CHAR) =
NVL(xla_events_pub_pkg.g_security.security_id_char_3,C_CHAR);
'Unable to perform DELETE on the entity. The entity does not exist'
,p_token_2 => 'LOCATION'
,p_value_2 => 'xla_events_pkg.delete_entity');
(p_msg => 'END of function delete_entity, return 1'
,p_level => C_LEVEL_PROCEDURE
,p_module =>l_log_module);
DELETE xla_transaction_entities
WHERE entity_id = l_entity_id
AND application_id = p_source_info.application_id;
(p_msg => 'Number of entities deleted = '||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module =>l_log_module);
(p_msg => 'END of function delete_entity, return 0'
,p_level => C_LEVEL_PROCEDURE
,p_module =>l_log_module);
ROLLBACK to SAVEPOINT before_entity_delete;
ROLLBACK to SAVEPOINT before_entity_delete;
(p_location => 'xla_events_pkg.delete_entity');
END delete_entity;
trace('> xla_events_pkg.delete_entity' , 20);
SAVEPOINT before_entity_delete;
g_action := C_EVENT_DELETE;
,p_value_2 => 'xla_events_pkg.delete_entity');
trace('< xla_events_pkg.delete_entity' , 20);
ROLLBACK to SAVEPOINT before_entity_delete;
ROLLBACK to SAVEPOINT before_entity_delete;
(p_location => 'xla_events_pkg.delete_entity');
SELECT event_id
,event_number
,event_type_code
,event_date
,event_status_code
,on_hold_flag
,reference_num_1
,reference_num_2
,reference_num_3
,reference_num_4
,reference_char_1
,reference_char_2
,reference_char_3
,reference_char_4
,reference_date_1
,reference_date_2
,reference_date_3
,reference_date_4
INTO l_event_info.event_id
,l_event_info.event_number
,l_event_info.event_type_code
,l_event_info.event_date
,l_event_info.event_status_code
,l_event_info.on_hold_flag
,l_event_info.reference_num_1
,l_event_info.reference_num_2
,l_event_info.reference_num_3
,l_event_info.reference_num_4
,l_event_info.reference_char_1
,l_event_info.reference_char_2
,l_event_info.reference_char_3
,l_event_info.reference_char_4
,l_event_info.reference_date_1
,l_event_info.reference_date_2
,l_event_info.reference_date_3
,l_event_info.reference_date_4
FROM xla_events
WHERE event_id = p_event_id
AND entity_id = g_entity_id;
SELECT event_status_code
FROM xla_events
WHERE event_date = NVL(l_event_date, event_date)
AND event_type_code = NVL(p_event_type_code, event_type_code)
AND event_status_code = NVL(p_event_status_code, event_status_code)
AND event_number = NVL(p_event_number, event_number)
AND event_id = NVL(p_event_id, event_id)
AND entity_id = g_entity_id
AND event_type_code IN (SELECT event_type_code
FROM xla_event_types_b
WHERE application_id = g_application_id
AND entity_code = g_entity_type_code
AND event_class_code = NVL(p_event_class_code,
event_class_code));
PROCEDURE update_transaction_number
(p_event_source_info IN xla_events_pub_pkg.t_event_source_info
,p_valuation_method IN VARCHAR2
,p_transaction_number IN VARCHAR2
,p_event_id IN PLS_INTEGER DEFAULT NULL) IS
l_log_module VARCHAR2(240);
l_log_module := C_DEFAULT_MODULE||'.update_transaction_number';
(p_msg => 'BEGIN of procedure update_transaction_number'
,p_level => C_LEVEL_PROCEDURE
,p_module =>l_log_module);
,p_value_2 => 'xla_events_pkg.update_transaction_number');
g_action := C_EVENT_UPDATE;
,p_value_2 => 'xla_events_pkg.update_transaction_number');
update_entity_trx_number
(p_transaction_number => p_transaction_number);
(p_msg => 'BEGIN of procedure update_transaction_number'
,p_level => C_LEVEL_PROCEDURE
,p_module =>l_log_module);
(p_location => 'xla_events_pkg.update_transaction_number');
END update_transaction_number;
SELECT application_id
FROM xla_subledgers
WHERE application_id = p_source_application_id;
SELECT 1
FROM xla_events_int_gt;
SELECT 1
FROM xla_events_int_gt
WHERE event_number is null or event_number<1;
SELECT entity_id, event_id, event_number, event_status_code
FROM xla_events_int_gt
ORDER BY entity_id, event_number;
SELECT 1 from xla_events_int_gt
WHERE event_status_code not in ('I', 'U', 'N');
SELECT 1
FROM xla_events_int_gt xeg, xla_event_types_b xet
WHERE xet.entity_code(+) = p_entity_code
AND xet.application_id(+) = p_app_id
AND xeg.event_type_code = xet.event_type_code (+)
AND nvl(xet.enabled_flag, 'N') = 'N';
SELECT 1
FROM xla_events_int_gt xeg
WHERE xeg.entity_code <> p_entity_code
OR xeg.application_id <> p_app_id;
l_update_gt_string varchar2(4000);
SELECT xla_transaction_entities_s.nextval
,source_id_int_1
,source_id_int_2
,source_id_int_3
,source_id_int_4
,source_id_char_1
,source_id_char_2
,source_id_char_3
,source_id_char_4
,valuation_method
FROM (
SELECT DISTINCT
source_id_int_1
,source_id_int_2
,source_id_int_3
,source_id_int_4
,source_id_char_1
,source_id_char_2
,source_id_char_3
,source_id_char_4
,valuation_method
FROM xla_events_int_gt);
select count(1) into l_rowcount_gt from xla_events_int_gt;
select enable_gapless_events_flag
into g_gapless_flag
from xla_entity_types_b
where entity_code=p_entity_type_code
and application_id=p_application_id;
'SELECT 1
FROM dual
WHERE EXISTS
(SELECT 1
FROM xla_events_int_gt xeg
WHERE xeg.entity_code <> :1
OR xeg.application_id <> :2
OR xeg.event_status_code not in (''I'', ''U'', ''N'')
OR xeg.event_type_code not in
(SELECT event_type_code
FROM xla_event_types_b
WHERE application_id = :3
AND entity_code = :4
AND enabled_flag = ''Y'') OR '
|| validate_id_where_clause || ')';
(p_msg => 'just before insert into xla_transaction entities'
,p_level => C_LEVEL_STATEMENT
,p_module =>l_log_module);
INSERT INTO xla_transaction_entities
(entity_id
,application_id
,source_application_id
,ledger_id
,legal_entity_id
,entity_code
,transaction_number
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,valuation_method
,security_id_int_1
,security_id_int_2
,security_id_int_3
,security_id_char_1
,security_id_char_2
,security_id_char_3
,source_id_int_1
,source_id_int_2
,source_id_int_3
,source_id_int_4
,source_id_char_1
,source_id_char_2
,source_id_char_3
,source_id_char_4)
(SELECT /*+ index (xe xla_events_int_gt_n1) */
l_array_entity_id(i)
, p_application_id
, nvl(p_source_application_id, p_application_id)
, p_ledger_id
, xe.legal_entity_id /* Bug 4458604*/
, p_entity_type_code
, xe.transaction_number
, sysdate
, xla_environment_pkg.g_usr_id
, sysdate
, xla_environment_pkg.g_usr_id
, xla_environment_pkg.g_login_id
, xe.valuation_method
, xe.security_id_int_1
, xe.security_id_int_2
, xe.security_id_int_3
, xe.security_id_char_1
, xe.security_id_char_2
, xe.security_id_char_3
, xe.source_id_int_1
, xe.source_id_int_2
, xe.source_id_int_3
, xe.source_id_int_4
, xe.source_id_char_1
, xe.source_id_char_2
, xe.source_id_char_3
, xe.source_id_char_4
FROM xla_events_int_gt xe
WHERE NVL( xe.source_id_int_1,-99) = NVL(l_array_source_id_int_1(i),C_NUM)
AND NVL( xe.source_id_int_2,-99) = NVL(l_array_source_id_int_2(i),C_NUM)
AND NVL( xe.source_id_int_3,-99) = NVL(l_array_source_id_int_3(i),C_NUM)
AND NVL( xe.source_id_int_4,-99) = NVL(l_array_source_id_int_4(i),C_NUM)
AND NVL( xe.source_id_char_1,' ') = NVL(l_array_source_id_char_1(i),C_CHAR)
AND NVL( xe.source_id_char_2,' ') = NVL(l_array_source_id_char_2(i),C_CHAR)
AND NVL( xe.source_id_char_3,' ') = NVL(l_array_source_id_char_3(i),C_CHAR)
AND NVL( xe.source_id_char_4,' ') = NVL(l_array_source_id_char_4(i),C_CHAR)
AND NVL( xe.valuation_method,' ') = NVL(l_array_valuation_method(i),C_CHAR)
AND ROWNUM = 1
);
UPDATE /*+ index (xe xla_events_int_gt_n1) */ xla_events_int_gt xe
SET xe.entity_id = l_array_entity_id(i)
, xe.event_id = xla_events_s.nextval
WHERE NVL( xe.source_id_int_1,-99)= NVL(l_array_source_id_int_1(i),C_NUM)
AND NVL( xe.source_id_int_2,-99) = NVL(l_array_source_id_int_2(i),C_NUM)
AND NVL( xe.source_id_int_3,-99) = NVL(l_array_source_id_int_3(i),C_NUM)
AND NVL( xe.source_id_int_4,-99) = NVL(l_array_source_id_int_4(i),C_NUM)
AND NVL( xe.source_id_char_1,' ')= NVL(l_array_source_id_char_1(i),C_CHAR)
AND NVL( xe.source_id_char_2,' ')= NVL(l_array_source_id_char_2(i),C_CHAR)
AND NVL( xe.source_id_char_3,' ')= NVL(l_array_source_id_char_3(i),C_CHAR)
AND NVL( xe.source_id_char_4,' ')= NVL(l_array_source_id_char_4(i),C_CHAR)
AND NVL( xe.valuation_method,' ')=NVL(l_array_valuation_method(i),C_CHAR);
UPDATE xla_events_int_gt
SET on_hold_flag = l_array_on_hold_flag(i)
WHERE event_id=l_array_event_id(i);
(p_msg => 'before insert into xla_events table'
,p_level => C_LEVEL_STATEMENT
,p_module =>l_log_module);
INSERT INTO xla_events
(event_id
,application_id
,event_type_code
,entity_id
,event_number
,event_status_code
,process_status_code
,event_date
,transaction_date
,budgetary_control_flag
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,program_update_date
,program_application_id
,program_id
,request_id
,reference_num_1
,reference_num_2
,reference_num_3
,reference_num_4
,reference_char_1
,reference_char_2
,reference_char_3
,reference_char_4
,reference_date_1
,reference_date_2
,reference_date_3
,reference_date_4
,on_hold_flag)
(SELECT event_id
,application_id
,event_type_code
,entity_id
,event_number
,event_status_code
,C_INTERNAL_UNPROCESSED
,TRUNC(event_date)
,nvl(transaction_date, TRUNC(event_date))
,nvl(xla_events_int_gt.budgetary_control_flag,'N')
,sysdate
,xla_environment_pkg.g_usr_id
,sysdate
,xla_environment_pkg.g_usr_id
,xla_environment_pkg.g_login_id
,sysdate
,xla_environment_pkg.g_prog_appl_id
,xla_environment_pkg.g_prog_id
,xla_environment_pkg.g_req_Id
,reference_num_1
,reference_num_2
,reference_num_3
,reference_num_4
,reference_char_1
,reference_char_2
,reference_char_3
,reference_char_4
,reference_date_1
,reference_date_2
,reference_date_3
,reference_date_4
,on_hold_flag
FROM xla_events_int_gt);
(p_msg => 'before insert into xla_events table nongapless'
,p_level => C_LEVEL_STATEMENT
,p_module =>l_log_module);
INSERT INTO xla_events
(event_id
,application_id
,event_type_code
,entity_id
,event_number
,event_status_code
,process_status_code
,event_date
,transaction_date
,budgetary_control_flag
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,program_update_date
,program_application_id
,program_id
,request_id
,reference_num_1
,reference_num_2
,reference_num_3
,reference_num_4
,reference_char_1
,reference_char_2
,reference_char_3
,reference_char_4
,reference_date_1
,reference_date_2
,reference_date_3
,reference_date_4
,on_hold_flag)
(SELECT event_id
,application_id
,event_type_code
,entity_id
,nvl(event_number, nvl(max(event_number)
over (partition by entity_id), 0)+
ROW_NUMBER() over (PARTITION BY entity_id order by event_id))
,event_status_code
,C_INTERNAL_UNPROCESSED
,TRUNC(event_date)
,nvl(transaction_date, TRUNC(event_date))
,nvl(xla_events_int_gt.budgetary_control_flag,'N')
,sysdate
,xla_environment_pkg.g_usr_id
,sysdate
,xla_environment_pkg.g_usr_id
,xla_environment_pkg.g_login_id
,sysdate
,xla_environment_pkg.g_prog_appl_id
,xla_environment_pkg.g_prog_id
,xla_environment_pkg.g_req_Id
,reference_num_1
,reference_num_2
,reference_num_3
,reference_num_4
,reference_char_1
,reference_char_2
,reference_char_3
,reference_char_4
,reference_date_1
,reference_date_2
,reference_date_3
,reference_date_4
,'N'
FROM xla_events_int_gt);
PROCEDURE update_bulk_event_statuses(p_application_id INTEGER)
IS
TYPE t_array_number IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
SELECT 1
FROM xla_events_int_gt xeg, xla_events xe, xla_entity_types_b xet
WHERE xeg.application_id = xe.application_id (+)
AND xeg.event_id = xe.event_id (+)
AND xeg.entity_code = xet.entity_code (+)
AND xet.application_id(+) = app_id
AND (xeg.entity_code = C_MANUAL_ENTITY
OR xeg.event_status_code not in ('I', 'N', 'U')
OR xe.event_status_code not in ('I', 'N', 'U')
OR xe.application_id is null
OR xe.event_id is null
OR xet.entity_code is null
OR xeg.application_id <> app_id);
SELECT 1
FROM xla_events_int_gt xeg
WHERE xeg.application_id <> app_id;
SELECT 1
FROM xla_events_int_gt xeg, xla_events xe
WHERE xeg.application_id = xe.application_id (+)
AND xeg.event_id = xe.event_id (+)
AND xe.event_id is null;
SELECT 1
FROM xla_events_int_gt xeg, xla_events xe
WHERE xeg.application_id = xe.application_id
AND xeg.event_id = xe.event_id
AND (xe.event_status_code not in ('I', 'N', 'U')
OR xeg.event_status_code not in ('I', 'N', 'U'));
SELECT xte.entity_id
FROM xla_transaction_entities xte
WHERE xte.application_id = p_application_id
AND xte.entity_id in
(SELECT entity_id
FROM xla_events_int_gt xeg, xla_entity_types_b xet
WHERE xeg.application_id = xet.application_id
AND xeg.entity_code = xet.entity_code
AND xet.enable_gapless_events_flag = 'Y')
FOR UPDATE NOWAIT;
Select min(xe.event_number), xe.entity_id, xe.application_id
From xla_events_int_gt xeg,
xla_events xe,
xla_entity_types_b xet
Where xeg.event_id = xe.event_id
And xeg.application_id = xe.application_id
And xeg.entity_code = xet.entity_code
And xeg.application_id = xet.application_id
And xet.enable_gapless_events_flag = 'Y'
And xe.event_status_code <> 'I'
And xeg.event_status_code = 'I'
And xe.on_hold_flag = 'N'
Group by xe.entity_id, xe.application_id;
Select xe.entity_id,
xe.event_id,
xe.event_number,
nvl(xeg.event_status_code, xe.event_status_code)
FROM xla_events xe, xla_events_int_gt xeg
Where xe.event_id = xeg.event_id(+)
And xe.entity_id in (
Select xe.entity_id
From xla_events_int_gt xeg,
xla_events xe,
xla_entity_types_b xet
Where xeg.event_id = xe.event_id
And xet.entity_code = xeg.entity_code
And xet.application_id = xe.application_id
And xet.enable_gapless_events_flag = 'Y'
And xe.event_status_code = 'I'
And xeg.event_status_code <> 'I'
And xe.on_hold_flag = 'N')
Order by entity_id, event_number;
l_log_module := C_DEFAULT_MODULE||'.update_bulk_event_statuses';
(p_msg => 'BEGIN of procedure .update_bulk_event_statuses'
,p_level => C_LEVEL_PROCEDURE
,p_module =>l_log_module);
SAVEPOINT before_update_bulk_statuses;
g_action := C_EVENT_UPDATE;
,p_value_2 => 'xla_events_pkg.update_bulk_event_statuses(fn)');
,p_value_2 => 'xla_events_pkg.update_bulk_event_statuses(fn)');
,p_value_2 => 'xla_events_pkg.update_bulk_event_statuses(fn)');
,p_value_2 => 'xla_events_pkg.update_bulk_event_statuses(fn)');
UPDATE xla_events
SET on_hold_flag = 'Y'
WHERE entity_id = l_current_entity_id
AND event_number > l_current_event_number
AND application_id = l_application_id;
UPDATE xla_events
SET on_hold_flag = l_array_on_hold_flag(i)
WHERE event_id=l_array_event_id(i);
(p_msg => 'before update the table'
,p_level => C_LEVEL_STATEMENT
,p_module =>l_log_module);
UPDATE xla_events xe
SET xe.event_status_code = (
SELECT event_status_code
FROM xla_events_int_gt xeg
WHERE xeg.event_id = xe.event_id),
xe.process_status_code = 'U'
WHERE xe.event_id in (
SELECT event_id
FROM xla_events_int_gt);
(p_msg => 'before calling massive_update'
,p_level => C_LEVEL_STATEMENT
,p_module =>l_log_module);
IF(NOT xla_balances_pkg.massive_update_for_events(p_application_id
=> p_application_id)) THEN
xla_exceptions_pkg.raise_message
(p_appli_s_name => 'XLA'
,p_msg_name => 'XLA_COMMON_ERROR'
,p_token_1 => 'ERROR'
,p_value_1 =>
'Error in the routine that does balance reversals'
,p_token_2 => 'LOCATION'
,p_value_2 => 'xla_events_pkg.update_bulk_event_statuses');
(p_msg => 'before calling delete_je'
,p_level => C_LEVEL_STATEMENT
,p_module =>l_log_module);
delete_je;
(p_msg => 'end of procedure update_bulk_event_statuses'
,p_level => C_LEVEL_PROCEDURE
,p_module =>l_log_module);
ROLLBACK to SAVEPOINT before_update_bulk_statuses;
ROLLBACK to SAVEPOINT before_update_bulk_statuses;
(p_location => 'xla_events_pkg.update_bulk_event_statuses(blk)');
END update_bulk_event_statuses;
PROCEDURE delete_bulk_events(p_application_id INTEGER)
IS
l_log_module VARCHAR2(240);
SELECT 1
FROM xla_events_int_gt xeg, xla_events xe, xla_transaction_entities xte
WHERE xeg.application_id = xe.application_id (+)
AND xeg.event_id = xe.event_id (+)
AND xe.entity_id = xte.entity_id (+)
AND xte.application_id(+) = app_id
AND (xeg.entity_code = C_MANUAL_ENTITY
OR xe.event_status_code not in ('I', 'N', 'U')
OR xe.application_id is null
OR xte.entity_code is null
OR xe.event_id is null
OR xeg.application_id <> app_id);
SELECT 1
FROM xla_events_int_gt xeg
WHERE xeg.application_id <> app_id;
SELECT 1
FROM xla_events_int_gt xeg, xla_events xe
WHERE xeg.application_id = xe.application_id (+)
AND xeg.event_id = xe.event_id (+)
AND xe.event_id is null;
SELECT 1
FROM xla_events_int_gt xeg, xla_events xe
WHERE xeg.application_id = xe.application_id
AND xeg.event_id = xe.event_id
AND xe.event_status_code not in ('I', 'N', 'U');
SELECT xte.entity_id
FROM xla_transaction_entities xte
WHERE xte.application_id = p_application_id
AND xte.entity_id in
(SELECT entity_id
FROM xla_events_int_gt xeg, xla_entity_types_b xet
WHERE xeg.application_id = xet.application_id
AND xeg.entity_code = xet.entity_code
AND xet.enable_gapless_events_flag = 'Y')
FOR UPDATE NOWAIT;
Select min(xe.event_number), xe.entity_id, xe.application_id
From xla_events_int_gt xeg,
xla_events xe,
xla_entity_types_b xet
Where xeg.event_id = xe.event_id
And xeg.application_id = xe.application_id
And xeg.entity_code = xet.entity_code
And xeg.application_id = xet.application_id
And xet.enable_gapless_events_flag = 'Y'
And xe.event_status_code <> 'I'
And xe.on_hold_flag = 'N'
Group by xe.entity_id, xe.application_id;
l_log_module := C_DEFAULT_MODULE||'.delete_bulk_events';
(p_msg => 'BEGIN of procedure .update_bulk_event_statuses'
,p_level => C_LEVEL_PROCEDURE
,p_module =>l_log_module);
SAVEPOINT before_delete_bulk_events;
g_action := C_EVENT_UPDATE;
,p_value_2 => 'xla_events_pkg.update_bulk_event_statuses(fn)');
,p_value_2 => 'xla_events_pkg.update_bulk_event_statuses(fn)');
'The events to be deleted must be in status I, N or U'
,p_token_2 => 'LOCATION'
,p_value_2 => 'xla_events_pkg.update_bulk_event_statuses(fn)');
'Either the entity code does not match with the event id, or the entity code is MANUAL. This API cannot be called to delete event for MANUAL entity'
,p_token_2 => 'LOCATION'
,p_value_2 => 'xla_events_pkg.get_array_event_info (fn)');
select count(1) into l_rowcount_gt from xla_events_int_gt;
(p_msg => 'before calling massive_update'
,p_level => C_LEVEL_STATEMENT
,p_module =>l_log_module);
IF(NOT xla_balances_pkg.massive_update_for_events(p_application_id
=> p_application_id)) THEN
xla_exceptions_pkg.raise_message
(p_appli_s_name => 'XLA'
,p_msg_name => 'XLA_COMMON_ERROR'
,p_token_1 => 'ERROR'
,p_value_1 =>
'Error in the routine that does balance reversals'
,p_token_2 => 'LOCATION'
,p_value_2 => 'xla_events_pkg.delete_bulk_events');
(p_msg => 'before calling delete_je'
,p_level => C_LEVEL_STATEMENT
,p_module =>l_log_module);
delete_je;
UPDATE xla_events
SET on_hold_flag = 'Y'
WHERE entity_id = l_entity_id
AND event_number > l_event_number
AND application_id = l_application_id;
UPDATE xla_events_int_gt xeg
SET xeg.entity_id =
(SELECT xe.entity_id
FROM xla_events xe
WHERE xe.event_id = xeg.event_id);
DELETE xla_events
WHERE event_id in (
SELECT event_id
FROM xla_events_int_gt);
(p_msg => 'Number of events deleted:'||to_char(SQL%ROWCOUNT)
,p_level => C_LEVEL_STATEMENT
,p_module =>l_log_module);
DELETE xla_transaction_entities xte
WHERE not exists (
SELECT 1
FROM xla_events xe
WHERE xe.entity_id = xte.entity_id
AND xe.application_id = xte.application_id
AND xte.application_id = p_application_id)
AND entity_id in (
SELECT entity_id
FROM xla_events_int_gt);
(p_msg => 'Number of transaction entity deleted:'||
to_char(SQL%ROWCOUNT)
,p_level => C_LEVEL_STATEMENT
,p_module =>l_log_module);
(p_msg => 'end of procedure delete_bulk_events'
,p_level => C_LEVEL_PROCEDURE
,p_module =>l_log_module);
ROLLBACK to SAVEPOINT before_delete_bulk_event;
ROLLBACK to SAVEPOINT before_delete_bulk_event;
(p_location => 'xla_events_pkg.delete_bulk_events(blk)');
END delete_bulk_events;
SELECT event_id
,event_number
,event_type_code
,event_date
,event_status_code
,on_hold_flag
,reference_num_1
,reference_num_2
,reference_num_3
,reference_num_4
,reference_char_1
,reference_char_2
,reference_char_3
,reference_char_4
,reference_date_1
,reference_date_2
,reference_date_3
,reference_date_4
FROM xla_events
WHERE event_date = NVL(l_event_date, event_date)
AND event_status_code = NVL(p_event_status_code,event_status_code)
AND event_type_code = NVL(p_event_type_code ,event_type_code)
AND entity_id = g_entity_id
AND event_type_code IN (SELECT event_type_code
FROM xla_event_types_b
WHERE application_id = g_application_id
AND entity_code = g_entity_type_code
AND event_class_code = NVL(p_event_class_code,
event_class_code));
SELECT application_id
FROM xla_subledgers
WHERE application_id = p_application_id;
SELECT t.entity_code
,m.source_id_col_name_1
,m.source_id_col_name_2
,m.source_id_col_name_3
,m.source_id_col_name_4
,t.enabled_flag
FROM xla_entity_types_b t
,xla_entity_id_mappings m
WHERE t.application_id = g_application_id
AND t.application_id = m.application_id
AND t.entity_code = m.entity_code;
SELECT a.entity_code
,a.event_class_code
,a.enabled_flag
FROM xla_event_classes_b a
WHERE a.application_id = g_application_id;
SELECT a.entity_code
,a.event_class_code
,a.event_type_code
,a.enabled_flag
FROM xla_event_types_b a
WHERE a.application_id = g_application_id;
g_entity_type_code_tbl.delete; --clear cache first
g_event_class_code_tbl.delete; --clear cache first
g_event_type_code_tbl.delete; --clear cache first
g_ledger_status_tbl.delete; --clear cache first
IF (g_action = C_EVENT_DELETE) OR (g_action = C_EVENT_QUERY) THEN
g_id_mapping := g_entity_type_code_tbl(p_entity_type_code).id_mapping;
'The entity Type is not enabled. Disabled entity types are not allowed for create/update APIs.'
,p_token_2 => 'LOCATION'
,p_value_2 => 'xla_events_pkg.validate_entity_type_code');
IF (((g_action = C_EVENT_CREATE) OR (g_action = C_EVENT_UPDATE)) AND
(l_enabled_flag <> C_YES)
)
THEN
xla_exceptions_pkg.raise_message
(p_appli_s_name => 'XLA'
,p_msg_name => 'XLA_COMMON_ERROR'
,p_token_1 => 'ERROR'
,p_value_1 =>
'The Event Class is not enabled. Disabled event classes are not allowed for create/update APIs.'
,p_token_2 => 'LOCATION'
,p_value_2 => 'xla_events_pkg.validate_event_class_code');
IF (((g_action = C_EVENT_CREATE) OR (g_action = C_EVENT_UPDATE)) AND
(l_enabled_flag <> C_YES)
)
THEN
xla_exceptions_pkg.raise_message
(p_appli_s_name => 'XLA'
,p_msg_name => 'XLA_COMMON_ERROR'
,p_token_1 => 'ERROR'
,p_value_1 =>
'The Event Type is not enabled. Disabled event types are not allowed for create/update APIs.'
,p_token_2 => 'LOCATION'
,p_value_2 => 'xla_events_pkg.validate_event_type_code');
SELECT DECODE(enabled_flag
,'N','N'
,'Y',capture_event_flag)
INTO l_temp --g_ledger_status_tbl(p_ledger_id)
FROM xla_subledger_options_v
WHERE ledger_id = p_ledger_id
AND application_id = p_application_id;
SELECT xte.entity_id
,xte.entity_code
,xte.transaction_number
,max(xe.event_number)
,xet.enable_gapless_events_flag
FROM xla_transaction_entities xte
,xla_events xe
,xla_entity_types_b xet
WHERE xte.application_id = p_source_info.application_id
AND xte.ledger_id = p_source_info.ledger_id
AND xte.entity_code = p_source_info.entity_type_code
AND NVL(xte.source_id_int_1,-99) = NVL(p_source_info.source_id_int_1,-99)
AND NVL(xte.source_id_int_2,-99) = NVL(p_source_info.source_id_int_2,-99)
AND NVL(xte.source_id_int_3,-99) = NVL(p_source_info.source_id_int_3,-99)
AND NVL(xte.source_id_int_4,-99) = NVL(p_source_info.source_id_int_4,-99)
AND NVL(xte.source_id_char_1,' ') = NVL(p_source_info.source_id_char_1,' ')
AND NVL(xte.source_id_char_2,' ') = NVL(p_source_info.source_id_char_2,' ')
AND NVL(xte.source_id_char_3,' ') = NVL(p_source_info.source_id_char_3,' ')
AND NVL(xte.source_id_char_4,' ') = NVL(p_source_info.source_id_char_4,' ')
AND NVL(xte.valuation_method,' ') = NVL(p_valuation_method,' ')
AND xe.entity_id = xte.entity_id
AND xet.application_id = xte.application_id
AND xte.entity_code = xet.entity_code
GROUP BY
xte.entity_id,
xte.entity_code,
xte.transaction_number,
xet.enable_gapless_events_flag;
SELECT a.enable_gapless_events_flag
FROM xla_entity_types_b a
WHERE a.entity_code=p_source_info.entity_type_code
AND a.application_id=p_source_info.application_id;
SELECT a.entity_id
,a.entity_code
,a.transaction_number
,MAX(b.event_number)
,c.enable_gapless_events_flag
FROM xla_transaction_entities a
,xla_events b
,xla_entity_types_b c
WHERE a.application_id = p_source_info.application_id
AND a.entity_id = g_entity_id
AND b.entity_id = g_entity_id
and a.entity_code=c.entity_code
AND a.application_id = c.application_id
GROUP BY
a.entity_id,
a.entity_code,
a.transaction_number,
c.enable_gapless_events_flag;
SELECT a.entity_id
,a.entity_code
,a.transaction_number
,b.event_number
FROM xla_transaction_entities a
,xla_events b
WHERE b.event_id = eventid
AND a.entity_id = b.entity_id
AND a.application_id = p_source_info.application_id;
SELECT a.entity_id
FROM xla_transaction_entities a
WHERE a.entity_id=entityid
AND a.application_id = p_source_info.application_id
FOR UPDATE NOWAIT;
(g_action = C_EVENT_DELETE or
g_action = C_EVENT_UPDATE or
g_action = C_EVENT_CREATE)) then
-- lock xla_trancation_entity table
open c_lock_te(g_entity_id);
(g_action = C_EVENT_DELETE or
g_action = C_EVENT_UPDATE or
g_action = C_EVENT_CREATE)) then
-- lock xla_trancation_entity table
open c_lock_te(g_entity_id);
INSERT INTO xla_transaction_entities
(entity_id
,application_id
,source_application_id
,ledger_id
,legal_entity_id
,entity_code
,transaction_number
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,valuation_method
,security_id_int_1
,security_id_int_2
,security_id_int_3
,security_id_char_1
,security_id_char_2
,security_id_char_3
,source_id_int_1
,source_id_int_2
,source_id_int_3
,source_id_int_4
,source_id_char_1
,source_id_char_2
,source_id_char_3
,source_id_char_4)
VALUES
(xla_transaction_entities_s.nextval
,p_event_source_info.application_id
,NVL(p_event_source_info.source_application_id,
p_event_source_info.application_id)
,p_event_source_info.ledger_id
,p_event_source_info.legal_entity_id
,p_event_source_info.entity_type_code
,p_event_source_info.transaction_number
,sysdate
,xla_environment_pkg.g_usr_id
,sysdate
,xla_environment_pkg.g_usr_id
,xla_environment_pkg.g_login_id
,p_valuation_method
,xla_events_pub_pkg.g_security.security_id_int_1
,xla_events_pub_pkg.g_security.security_id_int_2
,xla_events_pub_pkg.g_security.security_id_int_3
,xla_events_pub_pkg.g_security.security_id_char_1
,xla_events_pub_pkg.g_security.security_id_char_2
,xla_events_pub_pkg.g_security.security_id_char_3
,p_event_source_info.source_id_int_1
,p_event_source_info.source_id_int_2
,p_event_source_info.source_id_int_3
,p_event_source_info.source_id_int_4
,p_event_source_info.source_id_char_1
,p_event_source_info.source_id_char_2
,p_event_source_info.source_id_char_3
,p_event_source_info.source_id_char_4)
RETURNING entity_id INTO l_entity_id;
PROCEDURE update_entity_trx_number
(p_transaction_number IN VARCHAR2) IS
l_log_module VARCHAR2(240);
l_log_module := C_DEFAULT_MODULE||'.update_entity_trx_number';
(p_msg => 'BEGIN of procedure update_entity_trx_number'
,p_level => C_LEVEL_PROCEDURE
,p_module =>l_log_module);
UPDATE xla_transaction_entities
SET transaction_number = p_transaction_number
WHERE entity_id = g_entity_id
AND application_id = g_application_id;
(p_msg => 'end of procedure update_entity_trx_number'
,p_level => C_LEVEL_PROCEDURE
,p_module =>l_log_module);
(p_location => 'xla_events_pkg.update_entity_trx_number');
END update_entity_trx_number;
select on_hold_flag, event_status_code
into l_on_hold_flag, l_event_status_code
from xla_events
where entity_id=p_entity_id
and event_number=p_event_number-1;
INSERT INTO xla_events
(event_id
,application_id
,event_type_code
,entity_id
,event_number
,transaction_date
,event_status_code
,process_status_code
,event_date
,budgetary_control_flag
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,program_update_date
,program_application_id
,program_id
,request_id
,reference_num_1
,reference_num_2
,reference_num_3
,reference_num_4
,reference_char_1
,reference_char_2
,reference_char_3
,reference_char_4
,reference_date_1
,reference_date_2
,reference_date_3
,reference_date_4
,on_hold_flag)
VALUES
(xla_events_s.nextval
,p_application_id
,p_event_type_code
,p_entity_id
,NVL(p_event_number,g_max_event_number + 1)
,NVL(p_transaction_date, TRUNC(p_event_date))
,p_event_status_code
,p_process_status_code
,TRUNC(p_event_date)
,p_budgetary_control_flag
,sysdate
,xla_environment_pkg.g_usr_id
,sysdate
,xla_environment_pkg.g_usr_id
,xla_environment_pkg.g_login_id
,sysdate
,xla_environment_pkg.g_prog_appl_id
,xla_environment_pkg.g_prog_id
,xla_environment_pkg.g_Req_Id
,p_reference_info.reference_num_1
,p_reference_info.reference_num_2
,p_reference_info.reference_num_3
,p_reference_info.reference_num_4
,p_reference_info.reference_char_1
,p_reference_info.reference_char_2
,p_reference_info.reference_char_3
,p_reference_info.reference_char_4
,p_reference_info.reference_date_1
,p_reference_info.reference_date_2
,p_reference_info.reference_date_3
,p_reference_info.reference_date_4
,l_on_hold_flag)
RETURNING event_id INTO l_event_id;
SELECT event_status_code, event_number BULK COLLECT
INTO g_gapless_array_event_status, g_gapless_event_number
FROM xla_events
Where entity_id = g_entity_id
and event_number> p_event_number
Order by event_number;
update xla_events
set on_hold_flag='N'
where entity_id=p_entity_id
and event_number >p_event_number
and event_number
g_ledger_status_tbl.delete;
PROCEDURE delete_je IS
l_log_module VARCHAR2(240);
l_log_module := C_DEFAULT_MODULE||'.delete_je';
(p_msg => 'BEGIN of procedure DELETE_JE'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
DELETE FROM xla_accounting_errors
WHERE event_id IN
(SELECT xeg.event_id FROM xla_events_int_gt xeg, xla_events xe
WHERE xeg.event_id = xe.event_id
AND xe.event_status_code in ('D', 'I'));
(p_msg => 'Number of errors deleted = '||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
DELETE FROM xla_distribution_links
WHERE application_id= g_application_id and ae_header_id IN
(SELECT xh.ae_header_id
FROM xla_events_int_gt xeg,
xla_ae_headers xh
WHERE
xh.event_id = xeg.event_id AND
xh.application_id = g_application_id AND
xh.accounting_entry_status_code IN ('D','R','RELATED_EVENT_ERROR','I','N')
);
(p_msg => 'Number of distribution links deleted = '||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
DELETE FROM xla_ae_segment_values
WHERE ae_header_id IN
(SELECT xah.ae_header_id
FROM xla_events xe
,xla_ae_headers xah
,xla_events_int_gt xeg
WHERE xe.application_id = xah.application_id
AND xah.event_id = xe.event_id
AND xeg.event_id = xe.event_id);
(p_msg => 'Number of segment values deleted = '||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
DELETE FROM xla_ae_line_acs
WHERE ae_header_id IN
(SELECT xah.ae_header_id
FROM xla_events xe
,xla_ae_headers xah
,xla_events_int_gt xeg
WHERE xe.application_id = xah.application_id
AND xah.event_id = xe.event_id
AND xeg.event_id = xe.event_id);
(p_msg => 'Number of line acs deleted = '||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
DELETE FROM xla_ae_header_acs
WHERE ae_header_id IN
(SELECT xah.ae_header_id
FROM xla_events xe
,xla_ae_headers xah
,xla_events_int_gt xeg
WHERE xe.application_id = xah.application_id
AND xah.event_id = xe.event_id
AND xeg.event_id = xe.event_id);
(p_msg => 'Number of header acs deleted = '||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
DELETE FROM xla_ae_line_details
WHERE ae_header_id IN
(SELECT xah.ae_header_id
FROM xla_events xe
,xla_ae_headers xah
,xla_events_int_gt xeg
WHERE xe.application_id = xah.application_id
AND xah.event_id = xe.event_id
AND xeg.event_id = xe.event_id);
(p_msg => 'Number of line details deleted = '||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
DELETE FROM xla_ae_header_details
WHERE ae_header_id IN
(SELECT xah.ae_header_id
FROM xla_events xe
,xla_ae_headers xah
,xla_events_int_gt xeg
WHERE xe.application_id = xah.application_id
AND xah.event_id = xe.event_id
AND xeg.event_id = xe.event_id);
(p_msg => 'Number of header details deleted = '||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
DELETE FROM xla_ae_lines
WHERE application_id = g_application_id
AND ae_header_id IN
(SELECT xah.ae_header_id
FROM xla_events xe
,xla_ae_headers xah
,xla_events_int_gt xeg
WHERE xe.application_id = xah.application_id
AND xe.application_id = g_application_id
AND xah.application_id = g_application_id
AND xah.event_id = xe.event_id
AND xeg.event_id = xe.event_id);
(p_msg => 'Number of ae lines deleted = '||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
DELETE FROM xla_ae_headers
WHERE application_id = g_application_id
AND ae_header_id IN
(SELECT xah.ae_header_id
FROM xla_events xe
,xla_ae_headers xah
,xla_events_int_gt xeg
WHERE xe.application_id = xah.application_id
AND xah.event_id = xe.event_id
AND xeg.event_id = xe.event_id);
(p_msg => 'Number of ae headers deleted = '||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
(p_msg => 'END of procedure DELETE_JE'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
(p_location => 'xla_events_pkg.delete_je');
END delete_je;
DELETE FROM xla_transfer_ledgers
WHERE group_id IN
(SELECT group_id
FROM gl_je_batches
WHERE status = 'P'
AND default_period_name = p_period_name)
AND (primary_ledger_id is null OR
primary_ledger_id = p_ledger_id OR
secondary_ledger_id = p_ledger_id);
SELECT lower(iso_language),iso_territory
INTO l_iso_language,l_iso_territory
FROM FND_LANGUAGES
WHERE language_code = USERENV('LANG');
SELECT meaning
INTO l_user_je_source_name
FROM xla_lookups
WHERE lookup_type = 'XLA_ADR_SIDE'
AND lookup_code = 'ALL';
SELECT name
INTO l_ledger_name
FROM gl_ledgers
WHERE ledger_id = p_ledger_id;
SELECT fat.application_name
INTO l_application_name
FROM fnd_application_tl fat
WHERE fat.application_id = p_application_id
AND fat.language = nvl(USERENV('LANG'),fat.language);
SELECT name
INTO l_ledger_name
FROM gl_ledgers
WHERE ledger_id = p_ledger_id;