[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| -XLA_BALANCE_PKG.MASSIVE_UPDATE FOR |
| UPDATE OF balance |
| -post_commit_procedure bug #2957496 |
| 06/16/2003 S. Singhania Added code TO LOCK entities BEFORE |
| updating EVENTS TABLE. PROCEDURE |
| modified : BATCH_ACCOUNTING |
| Modified THE CURSOR TO REF CURSOR IN |
| ENQUEUE_MESSAGES. included filters |
| based ON xla_events. |
| 06/17/2003 S. Singhania Changed THE NAME OF THE VIEW used |
| XLA_EVENT_ENTITIES_V TO |
| XLA_ENTITY_EVENTS_V |
| 06/26/2003 S. Singhania Bug fix FOR bug # 3022532. (TABLE NAME |
| changes.) |
| 07/05/2003 S. Singhania Modified code 'spawn child' TO make sure |
| children are NOT spawned IF there are |
| NO EVENTS TO process. |
| 07/17/2003 S. Singhania Fix FOR Bug # 3051978. THE STATEMENT FOR |
| UPDATE OF EVENTS IN 'complete_entries' |
| IS modified. |
| 07/22/2003 S. Singhania Removed THE USE OF CHR FROM THE code |
| 07/29/2003 S. Singhania NAME CHANGE FOR objects used IN THE queue|
| Removed THE code that does PAD |
| incompatibility CHECK. |
| Added code TO INSERT program LEVEL errors|
| INTO xla_accounting_errors. |
| Commented OUT code IN xla_accounting_log |
| routine. |
| Added THE funtion IS_PARENT_RUNNING |
| 07/30/2003 S. Singhania Modified ACCOUNTING_PROGRAM_BATCH, |
| UNIT_PROCESSOR_BATCH TO RETURN THE |
| p_retcode = 1 IN CASE OF EVENTS IN |
| error (bug # 2709397) |
| 07/31/2003 S. Singhania Added anonymous BLOCK around EXECUTE |
| IMMEDIATE calls. |
| 08/05/2003 S. Singhania Added parameter P_ACCOUNTING_FLAG TO |
| ACCOUNTING_PROGRAM_DOCUMENT |
| Document MODE routines are rewritten. |
| Added code IN UNIT_PROCESSOR TO CHECK THE|
| error count AND EXIT OUT OF THE LOOP |
| AND STOP processing. |
| 08/06/2003 S. Singhania Correct PARAMETERS are passed TO THE |
| calls TO THE 'pre-processing procedure,|
| 'post-processing procedure' AND 'post- |
| COMMIT PROCEDURE'. |
| 09/09/2003 S. Singhania TO SET error SOURCE, changed THE CALL TO |
| XLA_ACCOUNTING_ERR_PKG.SET_ERROR_SOURCE|
| 09/17/2003 S. Singhania Performance changes (bug # 3118344) |
| - Modified ACCOUNTING_PROGRAM_BATCH TO |
| build WHERE condition FOR dynamic SQL|
| based ON security PARAMETERS AND |
| process CATEGORY code |
| - Modified BATCH_ACCOUNTING TO WRITE |
| dynamic SQL TO prevent NVL. |
| - Modified cursors IN ENQUEUE_MESSAGES |
| Added filter FOR MANUAL EVENTS. |
| 10/01/2003 S. Singhania NOTE:THIS IS BASED ON xlaapeng.pkb 116.12|
| 10/01/2003 S. Singhania Made SOURCE Application Changes. |
| (major REWRITE) |
| Added semicolon TO THE EXIT STATEMENT. |
| (Bug # 3165900) |
| Handle THE CASE WHEN Extract PROCEDURE IS|
| NOT defined FOR an application. |
| (Bug # 3182763) |
| 10/15/2003 S. Singhania Fix FOR bug # 2709397 TO SET THE correct |
| request status. |
| - FOR this defined AND used EXCEPTION |
| 'normal_termination' |
| 10/31/2003 Shishir Joshi Bug 3220355. Modified INSERT INTO THE |
| XLA_EVENTS_GT TABLE. |
| 11/18/2003 S. Singhania Bug 3220355. Modified INSERT INTO THE |
| XLA_EVENTS_GT TABLE. |
| Removed THE NOWAIT FROM THE FOR UPDATE |
| statements IN THE batch MODE. |
| (Bug # 2697222) |
| 11/19/2003 S. Singhania Initilaized 'g_report_request_id' IN THE |
| ACCOUNTING_PROGRAM_DOCUMENT so that THE|
| EVENTS are stamped correctly WITH THE |
| request_id IN THE OFFLINE MODE. |
| 11/24/2003 Shishir Joshi Bug 3275659. Modified INSERT INTO THE |
| XLA_EVENTS_GT TABLE. |
| 11/24/2003 S. Singhania Bug 3275659. |
| - Modified INSERT INTO XLA_EVENTS_GT. |
| - Added 'p_report_request_id' param TO |
| UNIT_PROCESSOR_BATCH |
| - Modified SPAWN_CHILD_PROCESSES TO |
| included THE NEW parameter WHILE |
| submitting XLAACCUP. |
| 11/24/2003 S. Singhania Bug 3239212. |
| - Added more IF condition TO SET OUT |
| variables IN ACCOUNTING_PROGRAM_BATCH|
| - Added two OUT PARAMETERS TO routnie |
| WAIT_FOR_REQUESTS |
| 12/19/2003 S. Singhania Added calls TO sequencing apis FOR THE |
| batch MODE. Bug 3000020. |
| - modified specs UNIT_PROCESSOR_BATCH |
| - added routine SEQUENCING_BATCH_INIT |
| - added calls TO sequencing apis IN |
| - POST_ACCOUNTING |
| - COMPLETE_ENTRIES |
| moved COMMIT down AFTER CALL TO THE |
| pre-processing PROCEDURE |
| 12/19/2003 S. Singhania Modified THE CURSOR's where clause in |
| ENQUEUE_MESSAGES. Bug 3327641 |
| 01/12/2004 S. Singhania Bug # 3365680. Added hint TO THE UPDATE |
| STATEMENT so that INDEX XLA_EVENTS_U1 |
| IS always used. |
| 01/26/2004 W. Shen Bug # 3339505. replace THE program hook |
| WITH workflow business event. |
| 02/13/2004 S. Singhania FIXED NOCOPY warnings. |
| 02/28/2004 S. Singhania Bug 3416534. Added FND_LOG messages. |
| 03/23/2004 S. Singhania Added a parameter p_module TO THE TRACE |
| calls AND THE PROCEDURE. |
| Made changes FOR handling THE accounting |
| OF THE gapless EVENTS. Ffg modified: |
| - PRE_ACCOUNTING |
| - DOCUMENT_PROCESSOR |
| 03/26/2004 S. Singhania Bug 3498491. Added CLEAR messages, WHERE |
| ever possible, TO make sure IF there IS|
| an EXCEPTION a CLEAR message IS given |
| IN THE log FILE. |
| 04/28/2004 S. Singhania Cleaned THE FILE, removed commented code|
| 05/05/2004 S. Singhania Made changes TO CALL THE NEW api FOR |
| balance UPDATE/reversal. |
| Verified that ALL THE workflow EVENTS are|
| raised IN FINAL AND DRAFT modes. |
| Document MODE accounting: |
| - Reviewed THE code path |
| - Verified CALL TO THE workflows EVENTS|
| - Verified CALL TO THE Sequencing apis |
| 07/15/2004 W. Shen NEW event status 'R' introduced. |
| 08/03/2004 S. Singhania Bug 3808349. |
| Modified THE code that build THE string|
| g_security_condition TO USE THE COLUMN |
| 'valuation_method' |
| 09/28/2004 K. Boussema Modified procedures TO cleanup Accounting |
| Event Extract Diagnostics data: |
| - delete_request_je() AND |
| - delete_transaction_je() |
| 12/08/2004 K. Boussema Renamed THE diagnostic framework TABLES: |
| - xla_extract_events BY xla_diag_events |
| - xla_extract_ledgers BY xla_diag_ledgers |
| - xla_extract_sources BY xla_diag_sources |
| 01/04/2005 S. Singhania Bug 3928357. g_total_error_Count IS SET TO|
| have THE right value IN document MODE |
| API. |
| Bug 3571389. Made changes TO refer TO THE |
| XLA_ACCTOUNTING_QTAB queue TABLE IN XLA |
| SCHEMA. |
| Made changes TO refer TO THE TYPE |
| XLA_QUEUE_MSG_TYPE IN APPS SCHEMA |
| 01/04/2005 S. Singhania Bug 4364612. WHILE loading xla_events_gt, |
| valuation method COLUMN IS populated. |
| 05/27/2005 V. Kumar Bug 4339454 Added handle_accounting_hook |
| procedure to replace business event by |
| APIS in accounting program |
| 01/06/2005 W. Shen Modify insert into xla_events_gt to insert|
| transaction_date |
| 06/06/2005 M. Asada Bug 4259032 Processing Unit Enhancement |
| 14/06/2005 V. Swapna Bug 4426342 Enabled business events for |
| all products |
| 15/06/2005 V. Swapna Bug 3071916 Made changes to call balance |
| calculation routine only if there are |
| valid entries |
| 06/24/2005 S. Singhania Bug 3443872. Added code to set security |
| context in unit_processor_batch for |
| child threads (XLAACCUP) |
| 06/30/2005 V. Kumar Bug 4459117 Added Cash Management in |
| handle_accounting_hooks |
| 07/11/2005 A. Wan Bug 4262811 MPA Project |
| 08/01/2005 W. Chan 4458381 - Public Sector Enhancement |
| 09/12/2005 V.Swapna 4599690 - Added Process Manufacturing |
| Financials to handle_accounting_hooks |
| 09/14/2005 W.Chan 4606566 - Pass budgetary_control_mode to |
| AP hook if calling for BC mode |
| 10/12/2005 A.Wan 4645092 - MPA report changes |
| 18-Oct-2005 V. Kumar Removed code for Analytical Criteria |
| 28-Oct-2005 W. Shen Third party merge, add logic to prevent |
| third party merge event been processed |
| here |
| 07-Nov-2005 S. Singhania Modofied event_application_manager to |
| print accounting, transfer time in log |
| 16-Nov-2005 Shishir Joshi Bug #4677032. Performance Improvement. |
| 17-Nov-2005 V. Kumar Bug#4736699 Added hint for performance |
| 23-Nov-2005 V. Kumar Bug#4752936 Added join on application_id |
| 30-Nov-2005 V.Swapna 4745309 - Added Payroll |
| to handle_accounting_hooks |
| 30-Nov-2005 V. Kumar Bug#4769388/4769270 Added hints |
| 12-Dec-2005 S. Singhania Bug 4883192. Modified the delete statement|
| that deletes from xla_distribution_links|
| 14-Dec-2005 V. Kumar Bug#4727703 Added condition to avoid unne-|
| -cessary execution of DELETE statements |
| 29-Dec-2005 V. Kumar Bug#4879954 Added hint for performance |
| 12-Jan-2006 V. Kumar Modified the logic to call GL transfer API|
| within child thread when transfer mode is |
| COMBINED (accounting and transfering to GL|
| 01-Feb-2006 V. Swapna Bug 4963736: Modified the call to |
| event_application_manager |
| 10-Feb-2006 A.Wan Bug 4670097 - budgetary control='N' |
| 12-Feb-2006 A.Wan 4860037 - anytime process order issue |
| 17-Feb-2006 V.Kumar 5034929 - Modified Cursor csr_event_class |
| 23-Feb-2006 V.Kumar 5056659 - Changed date mask to HH24:MI:SS |
| 02-Mar-2006 V. Swapna Bug 5018098: Added an exception to handle |
| no data found error in batch_accounting |
| 19-Apr-2006 A.Wan Bug 5149363 - performance fix |
| 20-Apr-2006 A.Wan Bug 5054831 - duplicate entires |
| 11-May-2006 A.Wan Bug 5221578 - log message is too long |
| 08/31/2006 V. Swapna Bug: 5257343. Add a new parameter to |
| unit_processor_batch, also, add this in |
| the call to submit the child program. |
| 14-Sep-2006 A.Wan Bug 5531502 - AAD_dbase_invalid need to |
| check if it is called in BC mode. If so |
| then validate for _BC_PKG, else _PKG. |
| 13-Oct-2009 VGOPISET bug:8423174 UNIT_PROCESSOR cursor changed |
| to exclude budgetary events |
| 13-JUN-2012 VKANTETI bug:14105024 To eliminate the duplicates |
| while building event classes list |
| 17-JUL-2012 NMIKKILI Bug 14307411 Codefix to support new |
| costing security function |
+===========================================================================*/
--=============================================================================
-- **************** declarations ********************
--=============================================================================
-------------------------------------------------------------------------------
-- declaring private constants and structures
-------------------------------------------------------------------------------
C_QUEUE_TABLE CONSTANT VARCHAR2(30) := 'xla_accounting_qtab';
SELECT /*+ leading(tab,evt) use_nl(evt) */
evt.event_id
FROM xla_events evt
,xla_event_types_b xet
,TABLE(CAST(:1 AS xla_array_number_type)) tab
WHERE evt.application_id = :2
AND evt.event_date <= :3
AND evt.entity_id = tab.column_value
AND evt.application_id = xet.application_id
AND evt.event_type_code = xet.event_type_code
AND xet.event_class_code IN ($event_classes$)
AND evt.event_type_code NOT IN (''FULL_MERGE'', ''PARTIAL_MERGE'')
AND evt.process_status_code IN (''U'',''D'',''E'',''R'',''I'')
AND evt.event_status_code IN (''U'', DECODE(:4, ''F'',''N'',''U''))
AND nvl(evt.budgetary_control_flag, ''N'') = ''N'' -- bug:8423174
FOR UPDATE OF evt.event_id skip locked
';
INSERT INTO xla_events_gt
(entity_id
,application_id
,ledger_id
,legal_entity_id
,entity_code
,transaction_number
,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
,event_id
,event_class_code
,event_type_code
,event_number
,event_date
,transaction_date
,event_status_code
,process_status_code
,valuation_method
,budgetary_control_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)
SELECT /*+ LEADING(EVT,XET,ENT,ECA) USE_NL(EVT,XET,ENT,ECA) */
evt.entity_id
,evt.application_id
,ent.ledger_id
,ent.legal_entity_id
,ent.entity_code
,ent.transaction_number
,ent.source_id_int_1
,ent.source_id_int_2
,ent.source_id_int_3
,ent.source_id_int_4
,ent.source_id_char_1
,ent.source_id_char_2
,ent.source_id_char_3
,ent.source_id_char_4
,evt.event_id
,xet.event_class_code
,evt.event_type_code
,evt.event_number
,evt.event_date
,evt.transaction_date
,evt.event_status_code
,evt.process_status_code
,ent.valuation_method
,NVL(evt.budgetary_control_flag,''N'')
,evt.reference_num_1
,evt.reference_num_2
,evt.reference_num_3
,evt.reference_num_4
,evt.reference_char_1
,evt.reference_char_2
,evt.reference_char_3
,evt.reference_char_4
,evt.reference_date_1
,evt.reference_date_2
,evt.reference_date_3
,evt.reference_date_4
FROM xla_events evt
,xla_transaction_entities ent
,xla_event_types_b xet
,xla_event_class_attrs eca
WHERE evt.application_id = :1
AND evt.event_date <= :2
AND evt.event_id = :3
AND evt.application_id = ent.application_id
AND evt.entity_id = ent.entity_id
AND evt.application_id = xet.application_id
AND evt.event_type_code = xet.event_type_code
AND eca.application_id = xet.application_id
AND eca.entity_code = xet.entity_code
AND eca.event_class_code = xet.event_class_code
AND xet.event_class_code IN ($event_classes$)
AND evt.event_type_code NOT IN (''FULL_MERGE'', ''PARTIAL_MERGE'')
AND evt.process_status_code IN (''U'',''D'',''E'',''R'',''I'')
AND evt.event_status_code IN
(''U'', DECODE(:4, ''F'',''N'',''U''))
AND nvl(evt.budgetary_control_flag, ''N'') = ''N'' -- bug:8423174
';
INSERT INTO xla_events_gt
(entity_id
,application_id
,ledger_id
,legal_entity_id
,entity_code
,transaction_number
,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
,event_id
,event_class_code
,event_type_code
,event_number
,event_date
,transaction_date
,event_status_code
,process_status_code
,valuation_method
,budgetary_control_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)
SELECT /*+ LEADING(EVT,XET,ENT,ECA) USE_NL(EVT,XET,ENT,ECA) */
evt.entity_id
,evt.application_id
,ent.ledger_id
,ent.legal_entity_id
,ent.entity_code
,ent.transaction_number
,ent.source_id_int_1
,ent.source_id_int_2
,ent.source_id_int_3
,ent.source_id_int_4
,ent.source_id_char_1
,ent.source_id_char_2
,ent.source_id_char_3
,ent.source_id_char_4
,evt.event_id
,xet.event_class_code
,evt.event_type_code
,evt.event_number
,evt.event_date
,evt.transaction_date
,evt.event_status_code
,evt.process_status_code
,ent.valuation_method
,NVL(evt.budgetary_control_flag,''N'')
,evt.reference_num_1
,evt.reference_num_2
,evt.reference_num_3
,evt.reference_num_4
,evt.reference_char_1
,evt.reference_char_2
,evt.reference_char_3
,evt.reference_char_4
,evt.reference_date_1
,evt.reference_date_2
,evt.reference_date_3
,evt.reference_date_4
FROM xla_events evt
,xla_transaction_entities ent
,xla_event_types_b xet
,xla_event_class_attrs eca
WHERE evt.application_id = :1
AND evt.event_date <= :2
AND evt.event_id = :3
AND evt.application_id = ent.application_id
AND evt.entity_id = ent.entity_id
AND evt.application_id = xet.application_id
AND evt.event_type_code = xet.event_type_code
AND eca.application_id = xet.application_id
AND eca.entity_code = xet.entity_code
AND eca.event_class_code = xet.event_class_code
AND xet.event_class_code IN ($event_classes$)
AND evt.event_type_code NOT IN (''FULL_MERGE'', ''PARTIAL_MERGE'')
AND evt.process_status_code IN (''U'',''D'',''E'',''R'',''I'')
AND nvl(evt.budgetary_control_flag, ''N'') = ''N'' -- bug:8423174
AND evt.event_status_code IN
(''U'', DECODE(:4, ''F'',''N'',''U''))
AND evt.event_number <
(SELECT NVL(MIN(evt2.event_number), evt.event_number + 1)
FROM xla_events evt2
,xla_transaction_entities_upg ent2
,xla_event_types_b xet2
,xla_event_class_attrs eca2
WHERE evt2.application_id = evt.application_id
AND evt2.entity_id = evt.entity_id
AND evt2.event_date = :5
AND evt2.application_id = ent2.application_id
AND evt2.entity_id = ent2.entity_id
AND evt2.application_id = xet2.application_id
AND evt2.event_type_code = xet2.event_type_code
AND eca2.application_id = xet2.application_id
AND eca2.entity_code = xet2.entity_code
AND eca2.event_class_code = xet2.event_class_code
AND xet2.event_class_code NOT IN ($event_class_current_order$)
AND xet2.event_class_code NOT IN ($event_class_anytime_order$)
AND evt2.event_type_code NOT IN (''FULL_MERGE'', ''PARTIAL_MERGE'')
AND evt2.process_status_code IN (''U'',''D'',''E'',''R'',''I'')
AND evt2.event_status_code IN
(''U'', DECODE(:6, ''F'',''N'',''U''))
)
';
(selected_entity_count NUMBER
,dequeued_msg_count NUMBER
,selected_event_count NUMBER);
PROCEDURE delete_batch_je;
PROCEDURE delete_request_je;
PROCEDURE delete_transaction_je
(p_entity_id IN NUMBER);
SELECT xla_accounting_batches_s.NEXTVAL INTO g_accounting_batch_id FROM DUAL;
SELECT
DECODE(p_valuation_method,NULL,NULL,'and valuation_method = '''||p_valuation_method||''' ')||
--DECODE(p_security_id_int_1,NULL,NULL,'and security_id_int_1 = '||p_security_id_int_1||' ')|| --14307411
DECODE(p_security_id_int_1,NULL,NULL
,DECODE(p_application_id,707, 'and NVL(security_id_int_1,'||p_security_id_int_1||') = '||p_security_id_int_1||' ',
'and security_id_int_1 = '||p_security_id_int_1||' '))|| --14307411
DECODE(p_security_id_int_2,NULL,NULL,'and security_id_int_2 = '||p_security_id_int_2||' ')||
DECODE(p_security_id_int_3,NULL,NULL,'and security_id_int_3 = '||p_security_id_int_3||' ')||
DECODE(p_security_id_char_1,NULL,NULL,'and security_id_char_1 = '''||p_security_id_char_1||''' ')||
DECODE(p_security_id_char_2,NULL,NULL,'and security_id_char_2 = '''||p_security_id_char_2||''' ')||
DECODE(p_security_id_char_3,NULL,NULL,'and security_id_char_3 = '''||p_security_id_char_3||''' ')
INTO g_security_condition
FROM DUAL;
SELECT
DECODE(p_process_category,NULL,NULL,'and event_class_group_code = '''||p_process_category||'''')
INTO g_process_category_condition
FROM DUAL;
SELECT
DECODE(p_source_application_id,NULL,NULL,'and source_application_id = '||p_source_application_id)
INTO g_source_appl_condition
FROM DUAL;
'SELECT application_id
FROM xla_entity_events_v
WHERE source_application_id = :2
AND ledger_id = :3
AND event_date <= :4
AND process_status_code IN (''R'',''I'',''E'',DECODE(:5,''N'',''D'',''E'')
,DECODE(:6,''N'',''U'',''E'')
)
AND event_status_code IN (''U'',DECODE(:7,''F'',''N'',''U''))
AND entity_code <> '''||C_MANUAL||'''
AND NVL(budgetary_control_flag,''N'') = ''N'' '||
g_security_condition||' '||
g_process_category_condition||' '||
'GROUP BY application_id';
SELECT lower(iso_language),iso_territory
INTO l_iso_language,l_iso_territory
FROM FND_LANGUAGES
WHERE language_code = USERENV('LANG');
xla_accounting_err_pkg.insert_errors;
xla_accounting_err_pkg.insert_errors; */
SELECT 1 INTO l_code FROM dual WHERE EXISTS
(SELECT 1 FROM xla_events xe, xla_ae_headers xah WHERE
xe.application_id= p_application_id AND
xe.application_id=xah.application_id AND
xe.process_status_code in ('I','R') AND
xe.event_status_code ='U' AND
xah.ledger_id = p_ledger_id AND
xah.accounting_batch_id = g_accounting_batch_id AND
xah.accounting_entry_status_code in ('R','I'));
xla_accounting_err_pkg.insert_errors;
xla_accounting_err_pkg.insert_errors;
xla_accounting_err_pkg.insert_errors;
SELECT
DECODE(p_valuation_method,NULL,NULL,'and valuation_method = '''||p_valuation_method||''' ')||
--DECODE(p_security_id_int_1,NULL,NULL,'and security_id_int_1 = '||p_security_id_int_1||' ')|| --14307411
DECODE(p_security_id_int_1,NULL,NULL
,DECODE(p_application_id,707, 'and NVL(security_id_int_1,p_security_id_int_1) = '||p_security_id_int_1||' ',
'and security_id_int_1 = '||p_security_id_int_1||' '))|| --14307411
DECODE(p_security_id_int_2,NULL,NULL,'and security_id_int_2 = '||p_security_id_int_2||' ')||
DECODE(p_security_id_int_3,NULL,NULL,'and security_id_int_3 = '||p_security_id_int_3||' ')||
DECODE(p_security_id_char_1,NULL,NULL,'and security_id_char_1 = '''||p_security_id_char_1||''' ')||
DECODE(p_security_id_char_2,NULL,NULL,'and security_id_char_2 = '''||p_security_id_char_2||''' ')||
DECODE(p_security_id_char_3,NULL,NULL,'and security_id_char_3 = '''||p_security_id_char_3||''' ')
INTO g_security_condition
FROM DUAL;
SELECT
DECODE(p_process_category,NULL,NULL,'and event_class_group_code = '''||p_process_category||'''')
INTO g_process_category_condition
FROM DUAL;
SELECT
DECODE(p_source_application_id,NULL,NULL,'and source_application_id = '||p_source_application_id)
INTO g_source_appl_condition
FROM DUAL;
xla_accounting_err_pkg.insert_errors;
xla_accounting_err_pkg.insert_errors;
xla_accounting_err_pkg.insert_errors;
xla_accounting_err_pkg.insert_errors;
g_child_data.selected_entity_count := 0;
g_child_data.selected_event_count := 0;
SELECT gl.ledger_id, gl.name ledger_name
FROM xla_acct_prog_events_gt xpa
, gl_ledgers gl
WHERE xpa.ledger_id = gl.ledger_id
AND enable_budgetary_control_flag = 'N'
AND ROWNUM = 1;
SELECT xgl.ledger_id
, xgl.name ledger_name
, xam.name slam_name
FROM xla_acct_prog_events_gt xap
, xla_gl_ledgers_v xgl
, xla_acctg_methods_tl xam
, xla_acctg_method_rules xar
, xla_aad_line_defn_assgns xal
, xla_line_definitions_b xld
WHERE xld.application_id(+) = xal.application_id
AND xld.amb_context_code(+) = xal.amb_context_code
AND xld.event_class_code(+) = xal.event_class_code
AND xld.event_type_code(+) = xal.event_type_code
AND xld.line_definition_owner_code(+) = xal.line_definition_owner_code
AND xld.line_definition_code(+) = xal.line_definition_code
AND xld.budgetary_control_flag(+) = 'Y'
AND xal.application_id(+) = xar.application_id
AND xal.amb_context_code(+) = xar.amb_context_code
AND xal.product_rule_type_code(+) = xar.product_rule_type_code
AND xal.product_rule_code(+) = xar.product_rule_code
AND xar.accounting_method_type_code(+) = xgl.sla_accounting_method_type
AND xar.accounting_method_code(+) = xgl.sla_accounting_method_code
AND xar.application_id(+) = p_application_id
AND xar.amb_context_code(+) = NVL(fnd_profile.value('XLA_AMB_CONTEXT'),'DEFAULT')
AND xam.accounting_method_type_code(+) = xgl.sla_accounting_method_type
AND xam.accounting_method_code(+) = xgl.sla_accounting_method_code
AND xam.language(+) = USERENV('LANG')
AND xgl.ledger_id = xap.ledger_id
GROUP BY xgl.ledger_id
, xgl.name
, xam.name
HAVING count(*) = 0;
SELECT /*+ LEADING (XAP) USE_NL (XAP XE XTE) */
xe.event_id
FROM xla_transaction_entities xte
, xla_events xe
, xla_acct_prog_events_gt xap
WHERE xte.application_id = xe.application_id
AND xte.entity_id = xe.entity_id
AND xe.application_id = p_application_id
AND xe.event_id = xap.event_id
FOR UPDATE NOWAIT;
SELECT distinct entity_id
FROM xla_acct_prog_events_gt xap
, xla_events xe
WHERE xe.application_id = p_application_id
AND xe.event_id = xap.event_id;
SELECT DISTINCT ledger_id
FROM xla_acct_prog_events_gt;
l_ret_flag_bal_update BOOLEAN;
SELECT xla_accounting_batches_s.nextval INTO p_accounting_batch_id FROM DUAL;
l_ret_flag_bal_update := xla_balances_pkg.massive_update_for_events
(p_application_id => g_application_id);
(p_msg => 'Fucntion XLA_BALANCES_PKG.MASSIVE_UPDATE executed'
,p_level => C_LEVEL_EVENT
,p_module => l_log_module);
(p_msg => 'l_ret_flag_bal_update = '||CASE WHEN l_ret_flag_bal_update
THEN 'TRUE'
ELSE 'FALSE' END
,p_level => C_LEVEL_EVENT
,p_module => l_log_module);
IF NOT l_ret_flag_bal_update THEN
xla_accounting_err_pkg.build_message
(p_appli_s_name => 'XLA'
,p_msg_name => 'XLA_AP_BAL_UPDATE_FAILED'
,p_entity_id => NULL
,p_event_id => NULL);
('Technical problem : Problem in submitting request for balance update');
,p_msg_name => 'XLA_AP_BAL_UPDATE_FAILED');
delete_batch_je;
UPDATE xla_events xe
SET process_status_code = 'U'
WHERE xe.on_hold_flag = 'N'
AND xe.process_status_code <> 'P'
AND xe.event_type_code not in ('FULL_MERGE', 'PARTIAL_MERGE')
AND xe.event_id = l_array_event_id(i);
SELECT DISTINCT xla_evt_class_orders_gt.processing_order
FROM xla_acct_prog_events_gt ,
xla_events ,
xla_event_types_b ,
xla_transaction_entities,
xla_evt_class_orders_gt
WHERE xla_events.event_id = xla_acct_prog_events_gt.event_id
AND xla_events.application_id = p_application_id
AND xla_transaction_entities.application_id = p_application_id
AND xla_events.entity_id = xla_transaction_entities.entity_id
AND xla_event_types_b.application_id = p_application_id
AND xla_transaction_entities.entity_code = xla_event_types_b.entity_code
AND xla_events.event_type_code = xla_event_types_b.event_type_code
AND xla_event_types_b.event_class_code = xla_evt_class_orders_gt.event_class_code
AND xla_events.process_status_code <> 'P' --condition added, bug8680284
ORDER BY xla_evt_class_orders_gt.processing_order ASC)
LOOP
IF (C_LEVEL_EVENT >= g_log_level) THEN
trace
(p_msg => 'BEGIN LOOP: event processor for order = ' || x.processing_order
,p_level => C_LEVEL_EVENT
,p_module => l_log_module);
l_ret_flag_bal_update := xla_balances_pkg.massive_update
(p_application_id => g_application_id
,p_ledger_id => NULL
,p_entity_id => NULL
,p_event_id => NULL
,p_request_id => NULL
,p_accounting_batch_id => g_accounting_batch_id
,p_update_mode => 'A'
,p_execution_mode => 'O');
l_ret_flag_bal_update := xla_balances_calc_pkg.massive_update
(p_application_id => g_application_id
,p_ledger_id => g_ledger_id
,p_entity_id => NULL
,p_event_id => NULL
,p_request_id => NULL
,p_accounting_batch_id => g_accounting_batch_id
,p_update_mode => 'A'
,p_execution_mode => 'O');
(p_msg => 'l_ret_flag_bal_update = '||CASE WHEN l_ret_flag_bal_update
THEN 'TRUE'
ELSE 'FALSE' END
,p_level => C_LEVEL_EVENT
,p_module => l_log_module);
IF NOT l_ret_flag_bal_update THEN
--bug 11666797
/*xla_accounting_err_pkg.build_message
(p_appli_s_name => 'XLA'
,p_msg_name => 'XLA_AP_BAL_UPDATE_FAILED'
,p_entity_id => NULL
,p_event_id => NULL);*/
('Technical problem : Problem in submitting request for balance update');*/
,p_msg_name => 'XLA_AP_BAL_UPDATE_FAILED');*/
xla_accounting_err_pkg.insert_errors;
xla_accounting_err_pkg.insert_errors;
xla_accounting_err_pkg.insert_errors;
INSERT INTO xla_acct_prog_events_gt (event_id, ledger_id)
SELECT xe.event_id, xte.ledger_id
FROM xla_events xe
, xla_transaction_entities xte
WHERE xte.application_id = p_application_id
AND xte.entity_id = p_entity_id
AND xe.application_id = p_application_id
AND xe.entity_id = p_entity_id
AND (p_accounting_flag = 'N' OR
NVL(xe.budgetary_control_flag,'N') = DECODE(p_accounting_mode
,'D','N'
,'F','N'
,'Y'));
(p_msg => 'Rows inserted into xla_acct_prog_events_gt = '||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
INSERT INTO xla_evt_class_orders_gt
(event_class_code
,processing_order
)
SELECT xec.event_class_code
, NVL(t.max_level, -1)
FROM xla_event_classes_b xec
, (SELECT application_id, event_class_code, max(LEVEL) AS max_level
FROM (SELECT application_id, event_class_code, prior_event_class_code
FROM xla_event_class_predecs
WHERE application_id = p_application_id
UNION
SELECT application_id, prior_event_class_code, NULL
FROM xla_event_class_predecs
WHERE application_id = p_application_id) xep
CONNECT BY application_id = PRIOR application_id
AND prior_event_class_code = PRIOR event_class_code
GROUP BY application_id, event_class_code) t
WHERE xec.event_class_code = t.event_class_code(+)
AND xec.application_id = t.application_id(+)
AND xec.application_id = p_application_id
AND xec.event_class_code <> 'MANUAL';
(p_msg => 'Number of rows inserted into xla_evt_class_orders_gt = '||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
l_ret_flag_bal_update BOOLEAN := FALSE;
l_str_update_events VARCHAR2(2000);
SELECT xso.error_limit
,NVL(xso.processes,1)
,NVL(xso.processing_unit_size,1)
INTO g_error_limit
,g_process_count
,g_unit_size
FROM xla_subledger_options_v xso
WHERE xso.application_id = g_application_id
AND xso.ledger_id = g_ledger_id;
'Please run Update Subledger Accounting Options program for your application.'
,p_token_2 => 'LOCATION'
,p_value_2 => 'xla_accounting_pkg.batch_accounting');
SELECT COUNT(1) INTO l_acct_batch_entries FROM DUAL
WHERE EXISTS
(SELECT 'Y'
FROM xla_events
WHERE application_id = g_application_id
AND request_id = g_report_request_id
AND process_status_code IN ('P')
);
(p_msg => 'Calling function XLA_BALANCES_PKG.MASSIVE_UPDATE'
,p_level => C_LEVEL_EVENT
,p_module => l_log_module);
l_ret_flag_bal_update :=
xla_balances_pkg.massive_update
(p_application_id => g_application_id --NULL
,p_ledger_id => NULL
,p_entity_id => NULL
,p_event_id => NULL
,p_request_id => NULL
,p_accounting_batch_id => g_accounting_batch_id
,p_update_mode => 'A'
,p_execution_mode => 'C');
(p_msg => 'Fucntion XLA_BALANCES_PKG.MASSIVE_UPDATE executed'
,p_level => C_LEVEL_EVENT
,p_module => l_log_module);
(p_msg => 'Calling function xla_balances_calc_pkg.MASSIVE_UPDATE'
,p_level => C_LEVEL_EVENT
,p_module => l_log_module);
l_ret_flag_bal_update :=
xla_balances_calc_pkg.massive_update
(p_application_id => g_application_id --NULL
,p_ledger_id => g_ledger_id
,p_entity_id => NULL
,p_event_id => NULL
,p_request_id => NULL
,p_accounting_batch_id => g_accounting_batch_id
,p_update_mode => 'A'
,p_execution_mode => 'C');
(p_msg => 'Fucntion xla_balances_calc_pkg.MASSIVE_UPDATE executed'
,p_level => C_LEVEL_EVENT
,p_module => l_log_module);
IF NOT l_ret_flag_bal_update THEN
IF (C_LEVEL_STATEMENT >= g_log_level) THEN
trace
(p_msg => 'l_ret_flag_bal_update = FALSE'
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
,p_msg_name => 'XLA_AP_BAL_UPDATE_FAILED'
,p_entity_id => NULL
,p_event_id => NULL);
print_logfile('Technical problem : Problem in submitting request for balance update');
,p_msg_name => 'XLA_AP_BAL_UPDATE_FAILED');
(p_msg => 'l_ret_flag_bal_update = TRUE'
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
l_str_update_events VARCHAR2(2000);
PROCEDURE delete_request_je IS
l_log_module VARCHAR2(240);
l_delete_count NUMBER;
l_log_module := C_DEFAULT_MODULE||'.delete_request_je';
(p_msg => 'BEGIN of procedure DELETE_REQUEST_JE'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
DELETE FROM xla_accounting_errors
WHERE event_id IN
(SELECT event_id FROM xla_events_gt);
(p_msg => 'Number of errors deleted = '||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
DELETE /*+ index(xdl,XLA_DISTRIBUTION_LINKS_N3) */ FROM xla_distribution_links xdl
WHERE ae_header_id IN
(SELECT /*+ cardinality(XE,10) leading(XE) use_nl(XH) unnest */ xh.ae_header_id
FROM xla_events_gt xe,
xla_ae_headers xh
WHERE xe.process_status_code in ('D','E','R','I')
AND xh.application_id = xe.application_id
AND xh.event_id = xe.event_id
)
AND application_id = g_application_id;
l_delete_count := SQL%ROWCOUNT;
(p_msg => 'Number of distribution links deleted = '||l_delete_count
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
IF l_delete_count > 0 THEN
--
-- Delete from xla_ae_segment_values
--
DELETE /*+ index(XLA_AE_SEGMENT_VALUES, XLA_AE_SEGMENT_VALUES_U1) */
FROM xla_ae_segment_values
WHERE ae_header_id IN
(SELECT xh.ae_header_id
FROM xla_events_gt xe,
xla_ae_headers xh
WHERE xe.process_status_code in ('D','E','R', 'I')
AND xh.application_id = xe.application_id
AND xh.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 /*+ use_nl_with_index(XLA_AE_LINE_ACS,XLA_AE_LINE_ACS_U1) leading(VW_NSO_1) */
FROM xla_ae_line_acs
WHERE ae_header_id IN
(SELECT/*+ cardinality(evt,10) unnest */ aeh.ae_header_id
FROM xla_events_gt evt
,xla_ae_headers aeh
WHERE evt.process_status_code in ('D','E','R','I')
AND aeh.application_id = evt.application_id
AND aeh.event_id = evt.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 aeh.ae_header_id
FROM xla_events_gt evt
,xla_ae_headers aeh
WHERE evt.process_status_code in ('D','E','R','I')
AND aeh.application_id = evt.application_id
AND aeh.event_id = evt.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_lines
WHERE application_id = g_application_id
AND ae_header_id IN
(SELECT xh.ae_header_id
FROM xla_events_gt xe,
xla_ae_headers xh
WHERE xe.process_status_code in ('D','E','R','I')
AND xh.application_id = xe.application_id
AND xh.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 /*+ index(aeh, xla_ae_headers_n2) */
FROM xla_ae_headers aeh
WHERE application_id = g_application_id
AND event_id IN (SELECT event_id
FROM xla_events_gt
WHERE process_status_code IN ('D','E','R','I'));
(p_msg => 'Number of ae headers deleted = '||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
DELETE FROM xla_diag_sources
WHERE event_id IN
(SELECT event_id FROM xla_events_gt);
(p_msg => 'Number of Extract sources rows deleted = '||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
DELETE FROM xla_diag_events
WHERE event_id IN
(SELECT event_id FROM xla_events_gt);
(p_msg => 'Number of Extract events deleted = '||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
DELETE FROM xla_diag_ledgers d
WHERE d.application_id = g_application_id
AND NOT EXISTS
(SELECT ledger_id, request_id
FROM xla_diag_events
WHERE application_id = d.application_id
AND request_id = d.accounting_request_id
AND ledger_id = d.primary_ledger_id
);
(p_msg => 'Number of Extract ledgers deleted = '||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
(p_msg => 'END of procedure DELETE_REQUEST_JE'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
(p_location => 'xla_accounting_pkg.delete_request_je');
END delete_request_je;
'SELECT /*+ index(evt,XLA_EVENTS_N3) */ MIN(nvl(xjc.processing_unit_size, :1))
FROM xla_events evt,
xla_transaction_entities ent,
xla_event_types_b xet,
xla_event_class_attrs eca,
xla_evt_class_orders_gt xpo,
xla_je_categories xjc
WHERE ent.application_id = :3
AND ent.ledger_id = :4
AND evt.application_id = :5
AND evt.entity_id = ent.entity_id
AND xet.application_id = evt.application_id
AND xet.event_type_code = evt.event_type_code
AND eca.application_id = xet.application_id
AND eca.entity_code = xet.entity_code
AND eca.event_class_code = xet.event_class_code
AND eca.event_class_group_code = nvl(:6, eca.event_class_group_code)
AND evt.event_type_code NOT IN(''FULL_MERGE'', ''PARTIAL_MERGE'')
AND evt.process_status_code IN(''I'', ''E'', ''R'', decode(:7, ''N'', ''D'', ''E''), decode(:8, ''N'', ''U'', ''E''))
AND evt.event_status_code IN(''U'', decode(:9, ''F'', ''N'', ''U''))
AND evt.on_hold_flag = ''N''
AND evt.event_date <= :10
AND ent.entity_code <> :11
AND nvl(evt.budgetary_control_flag, ''N'') = ''N''
AND xet.event_class_code = xpo.event_class_code
AND xpo.processing_order = :2
AND xjc.application_id = :12
AND xjc.ledger_id = :13
AND xjc.event_class_code = xpo.event_class_code '
||g_security_condition;
'SELECT /*+ leading(evt) use_nl(ent) index(evt,XLA_EVENTS_N3) */ -- Bug 5529420 reverted bug6369888 modified hint bug9192859
DISTINCT evt.entity_id
FROM xla_events evt
,xla_transaction_entities ent
,xla_event_types_b xet
,xla_event_class_attrs eca
,xla_evt_class_orders_gt xpo
WHERE ent.application_id = :1
AND ent.ledger_id = :2
AND evt.application_id = :3
AND evt.entity_id = ent.entity_id
AND xet.application_id = evt.application_id
AND xet.event_type_code = evt.event_type_code
AND eca.application_id = xet.application_id
AND eca.entity_code = xet.entity_code
AND eca.event_class_code = xet.event_class_code
AND eca.event_class_group_code = NVL(:4, eca.event_class_group_code)
AND evt.event_type_code NOT IN (''FULL_MERGE'', ''PARTIAL_MERGE'')
AND evt.process_status_code IN (''I'',''E'', ''R'',DECODE(:5,''N'',''D'',''E'')
,DECODE(:6,''N'',''U'',''E'')
)
AND evt.event_status_code IN (''U'',DECODE(:7,''F'',''N'',''U''))
AND evt.on_hold_flag = ''N''
AND evt.event_date <= :8
AND ent.entity_code <> :9
AND NVL(evt.budgetary_control_flag,''N'') = ''N''
AND xet.event_class_code = xpo.event_class_code
AND xpo.processing_order = :10 '
||g_security_condition;
SELECT MAX(xjc.processing_unit_size)
INTO l_unit_size
FROM xla_je_categories xjc,
(
SELECT
xpo.event_class_code
FROM xla_evt_class_orders_gt xpo
WHERE xpo.processing_order = p_processing_order
) tab1
WHERE xjc.application_id = g_application_id
AND xjc.ledger_id = g_ledger_id
AND xjc.event_class_code = tab1.event_class_code;
Please run Update Subledger Accounting Options program for your
application '||'ledger_id = '||g_ledger_id||
' application_id = '|| g_application_id
,p_token_2 => 'LOCATION'
,p_value_2 => 'xla_accounting_pkg.enqueue_messages');
INSERT INTO xla_evt_class_orders_gt
(event_class_code
,processing_order
)
SELECT xec.event_class_code
, NVL(t.max_level, -1)
FROM xla_event_classes_b xec
, (SELECT application_id, event_class_code, max(LEVEL) AS max_level
FROM (SELECT application_id, event_class_code, prior_event_class_code
FROM xla_event_class_predecs
WHERE application_id = g_application_id
UNION
SELECT application_id, prior_event_class_code, NULL
FROM xla_event_class_predecs
WHERE application_id = g_application_id) xep
CONNECT BY application_id = PRIOR application_id
AND prior_event_class_code = PRIOR event_class_code
GROUP BY application_id, event_class_code) t
WHERE xec.event_class_code = t.event_class_code(+)
AND xec.application_id = t.application_id(+)
AND xec.application_id = g_application_id
AND xec.event_class_code <> 'MANUAL';
SELECT max(processing_order)
INTO l_max_processing_order
FROM xla_evt_class_orders_gt;
SELECT status
FROM all_objects
WHERE object_name = c_pad_name
and owner = user
ORDER BY STATUS asc;
select decode(o.status, 0, 'N/A', 1, 'VALID', 'INVALID')
from sys.obj$ o, sys.user$ u
where o.owner# = u.user#
and o.linkname is null
and (o.type# not in (1 ,
10 )
or
(o.type# = 1 and 1 = (select 1
from sys.ind$ i
where i.obj# = o.obj#
and i.type# in (1, 2, 3, 4, 6, 7, 9))))
and o.name <> '_NEXT_OBJECT'
and o.name <> '_default_auditing_options_'
and o.name = p_pad_name
and u.name ='APPS'
order by 1 asc;
SELECT ledger_category_code,enable_budgetary_control_flag
INTO l_ledger_category_code,l_enable_bc_flag
FROM gl_ledgers
WHERE ledger_id = l_array_ledgers(i);
SELECT ledger_category_code,enable_budgetary_control_flag
INTO l_ledger_category_code,l_enable_bc_flag
FROM gl_ledgers
WHERE ledger_id = l_array_ledgers(i);
l_str1_insert_events VARCHAR2(32000);
l_str2_insert_events VARCHAR2(32000);
l_event_insert_count number;
SELECT gl_interface_control_s.NEXTVAL, l_array_base_ledgers(i)
INTO g_array_group_id(i), g_array_ledger_id(i)
FROM DUAL;
g_child_data.selected_entity_count :=
g_child_data.selected_entity_count + g_message.entity_ids.COUNT;
l_str1_insert_events := C_CURR_INS_EVENTS;
l_str1_insert_events := REPLACE(l_str1_insert_events
,'$event_classes$'
,l_class_current_order);
(p_msg => 'First l_str1_insert_events = '|| l_str1_insert_events
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
l_str2_insert_events := C_ANYTIME_INS_EVENTS;
l_str2_insert_events := REPLACE(l_str2_insert_events
,'$event_classes$'
,l_class_anytime_order);
l_str2_insert_events := REPLACE(l_str2_insert_events
,'$event_class_current_order$'
,l_class_current_order);
l_str2_insert_events := REPLACE(l_str2_insert_events
,'$event_class_anytime_order$'
,l_class_anytime_order); -- 4860037
(p_msg => 'Second l_str2_insert_events = '|| l_str2_insert_events
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
EXECUTE IMMEDIATE l_str1_insert_events
USING g_application_id
,g_end_date
,l_array_events(i)
,g_accounting_mode;
l_event_insert_count := SQL%ROWCOUNT;
(p_msg => 'l_event_insert_count = '||l_event_insert_count
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
l_event_count := l_event_count + l_event_insert_count;
EXECUTE IMMEDIATE l_str2_insert_events
USING g_application_id
,g_end_date
,l_array_events(i)
,g_accounting_mode
,g_end_date
,g_accounting_mode;
l_event_insert_count := SQL%ROWCOUNT;
(p_msg => 'l_event_insert_count = '||l_event_insert_count
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
l_event_count := l_event_count + l_event_insert_count;
delete_request_je;
xla_accounting_err_pkg.insert_errors;
SELECT xlr.ledger_id BULK COLLECT
INTO l_seq_context_value
FROM xla_ledger_relationships_v xlr
,xla_subledger_options_v xso
WHERE xlr.relationship_enabled_flag = 'Y'
AND xlr.ledger_category_code IN ('ALC','PRIMARY','SECONDARY')
AND DECODE(xso.valuation_method_flag
,'N',xlr.primary_ledger_id
,DECODE(xlr.ledger_category_code
,'ALC',xlr.primary_ledger_id
,xlr.ledger_id)
) = g_ledger_id
AND xso.application_id = g_application_id
AND xso.ledger_id = DECODE(xlr.ledger_category_code
,'ALC',xlr.primary_ledger_id
,xlr.ledger_id)
AND xso.enabled_flag = 'Y';
SELECT distinct event_date
FROM xla_events a
,xla_acct_prog_events_gt b
WHERE a.application_id = g_application_id
AND a.event_id = b.event_id
AND a.process_status_code = 'U'
AND a.event_status_code IN ('U',DECODE(g_accounting_mode,'F','N','U'))
AND a.on_hold_flag = 'N'
AND a.event_type_code not in ('FULL_MERGE', 'PARTIAL_MERGE')
AND b.ledger_id = g_ledger_id
ORDER BY event_date asc;
FOR i in (select a.* from xla_acct_prog_events_gt b, xla_events a WHERE a.application_id = g_application_id
AND a.event_id = b.event_id
AND a.process_status_code = 'U'
AND a.event_status_code IN ('U',DECODE(g_accounting_mode,'F','N','U'))
AND a.on_hold_flag = 'N'
AND a.event_type_code not in ('FULL_MERGE', 'PARTIAL_MERGE')
AND b.ledger_id = g_ledger_id) LOOP
trace
(p_msg => 'event_id='||i.event_id||' event_date='||i.event_date||' status='||i.process_status_code
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
DELETE FROM XLA_EVENTS_GT;
(p_msg => '# rows deleted XLA_EVENTS_GT = '||SQL%ROWCOUNT
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
DELETE FROM XLA_AE_LINES_GT;
(p_msg => '# rows deleted XLA_AE_LINES_GT = '||SQL%ROWCOUNT
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
DELETE FROM XLA_AE_HEADERS_GT;
(p_msg => '# rows deleted XLA_AE_HEADERS_GT = '||SQL%ROWCOUNT
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
DELETE FROM XLA_VALIDATION_LINES_GT;
(p_msg => '# rows deleted XLA_VALIDATION_LINES_GT = '||SQL%ROWCOUNT
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
INSERT INTO xla_events_gt
(entity_id
,application_id
,ledger_id
,legal_entity_id
,entity_code
,transaction_number
,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
,event_id
,event_class_code
,event_type_code
,event_number
,event_date
,transaction_date
,event_status_code
,process_status_code
,valuation_method
,budgetary_control_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)
SELECT xev.entity_id
,xev.application_id
,xev.ledger_id
,xev.legal_entity_id
,xev.entity_code
,xev.transaction_number
,xev.source_id_int_1
,xev.source_id_int_2
,xev.source_id_int_3
,xev.source_id_int_4
,xev.source_id_char_1
,xev.source_id_char_2
,xev.source_id_char_3
,xev.source_id_char_4
,xev.event_id
,xev.event_class_code
,xev.event_type_code
,xev.event_number
,xev.event_date
,xev.transaction_date
,xev.event_status_code
,xev.process_status_code
,xev.valuation_method
,NVL(xev.budgetary_control_flag,'N')
,xev.reference_num_1
,xev.reference_num_2
,xev.reference_num_3
,xev.reference_num_4
,xev.reference_char_1
,xev.reference_char_2
,xev.reference_char_3
,xev.reference_char_4
,xev.reference_date_1
,xev.reference_date_2
,xev.reference_date_3
,xev.reference_date_4
FROM xla_entity_events_v xev
, xla_acct_prog_events_gt xap
WHERE xev.application_id = g_application_id
AND xev.event_id = xap.event_id
AND xev.process_status_code = 'U'
AND xev.event_status_code IN ('U',DECODE(g_accounting_mode,'F','N','U'))
AND xev.on_hold_flag = 'N'
AND xev.event_type_code not in ('FULL_MERGE', 'PARTIAL_MERGE')
AND xap.ledger_id = g_ledger_id
AND xap.event_id IN (
SELECT xla_events.event_id
FROM xla_acct_prog_events_gt ,
xla_events ,
xla_event_types_b ,
xla_transaction_entities,
xla_evt_class_orders_gt
WHERE xla_events.event_id = xla_acct_prog_events_gt.event_id
AND xla_events.application_id = g_application_id
AND xla_transaction_entities.application_id = g_application_id
AND xla_events.entity_id = xla_transaction_entities.entity_id
AND xla_event_types_b.application_id = g_application_id
AND xla_transaction_entities.entity_code = xla_event_types_b.entity_code
AND xla_events.event_type_code = xla_event_types_b.event_type_code
AND xla_event_types_b.event_class_code = xla_evt_class_orders_gt.event_class_code
AND xla_events.process_status_code <> 'P' --condition added, bug8680284
AND xla_evt_class_orders_gt.processing_order = l_processing_order
)
ORDER BY xev.entity_id, xev.event_number;
(p_msg => '# rows inserted into xla_events_gt = '||SQL%ROWCOUNT
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
SELECT max(event_date)
INTO l_max_event_date
FROM xla_events_gt;
PROCEDURE delete_batch_je
IS
CURSOR c_headers IS
SELECT /*+ LEADING (XAP) USE_NL (XAP XE XAH) */
xah.ae_header_id
FROM xla_ae_headers xah
, xla_events xe
, xla_acct_prog_events_gt xap
WHERE xah.application_id = xe.application_id
AND xah.event_id = xe.event_id
AND xah.accounting_entry_status_code <> 'F'
AND xe.application_id = g_application_id
AND xe.process_status_code <> 'P'
AND xe.event_id = xap.event_id;
l_log_module := C_DEFAULT_MODULE||'.delete_batch_je';
trace(p_msg => 'BEGIN of procedure delete_batch_je'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
SELECT /*+ leading(xap,xe) use_nl(xe) index(xe,XLA_EVENTS_U1) */
xe.event_id
BULK COLLECT INTO l_array_event_id
FROM xla_events xe
, xla_acct_prog_events_gt xap
WHERE xe.application_id = g_application_id
AND xe.process_status_code <> 'P'
AND xe.event_id = xap.event_id;
DELETE FROM xla_accounting_errors
WHERE application_id = g_application_id
AND event_id = l_array_event_id(i);
(p_msg => '# xla_accounting_errors deleted = '||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
DELETE FROM xla_diag_sources
WHERE event_id = l_array_event_id(i);
trace(p_msg => '# xla_diag_sources deleted = '||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
DELETE FROM xla_diag_events
WHERE application_id = g_application_id
AND event_id = l_array_event_id(i);
trace(p_msg => '# xla_diag_events deleted = '||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
DELETE FROM xla_diag_ledgers d
WHERE d.application_id = g_application_id
AND NOT EXISTS
(SELECT ledger_id, request_id
FROM xla_diag_events
WHERE application_id = d.application_id
AND request_id = d.accounting_request_id
AND ledger_id = d.primary_ledger_id
);
(p_msg => '# xla_diag_ledgers deleted = '||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
DELETE FROM gl_bc_packets
WHERE application_id = g_application_id
AND event_id = l_array_event_id(i)
RETURNING packet_id BULK COLLECT INTO l_array_packet_id;
trace(p_msg => '# gl_bc_packets deleted = '||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
DELETE FROM gl_bc_packet_arrival_order
WHERE packet_id = l_array_packet_id(i);
trace(p_msg => '# gl_bc_packet_arrival_order 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 = l_array_header_id(i);
trace(p_msg => '# xla_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 = l_array_header_id(i);
trace (p_msg => '# xla_ae_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 = l_array_header_id(i);
trace (p_msg => '# xla_ae_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 = l_array_header_id(i);
trace (p_msg => '# xla_ae_header_acs 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 = l_array_header_id(i);
trace(p_msg => '# xla_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 = l_array_header_id(i);
trace(p_msg => '# xla_ae_headers deleted = '||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
trace(p_msg => 'END of procedure delete_batch_je'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
(p_location => 'xla_accounting_pkg.delete_batch_je');
END delete_batch_je;
FOR c1 IN (SELECT /*+ leading(xsl,xeg,aeh) use_nl(aeh) index(xla_ae_headers_n2) */
aeh.ae_header_id ae_header_id
,aeh.ledger_id ledger_id
,aeh.balance_type_code balance_type_code
,xsl.je_source_name je_source_name
,aeh.je_category_name je_category_name
,aeh.doc_category_code doc_category_code
,aeh.event_type_code accounting_event_type_code
,aeh.accounting_entry_type_code accounting_entry_type_code
,aeh.accounting_date gl_date
,aeh.completed_date completion_date
FROM xla_ae_headers aeh
,xla_events_gt xeg
,xla_subledgers xsl
WHERE aeh.application_id = xeg.application_id
AND aeh.event_id = xeg.event_id
AND xsl.application_id = xeg.application_id
AND xsl.application_id = g_application_id
AND nvl(aeh.zero_amount_flag, 'N') = 'N')
LOOP
l_index := l_index + 1;
UPDATE xla_ae_headers aeh
SET aeh.completed_date = l_array_completion_date(i)
,aeh.completion_acct_seq_assign_id = l_array_assignment_id(i)
,aeh.completion_acct_seq_version_id = l_array_seq_version_id(i)
,aeh.completion_acct_seq_value = l_array_sequence_number(i)
WHERE aeh.ae_header_id = l_array_ae_header_id(i);
UPDATE (SELECT /*+ leading(tmp) index(evt, XLA_EVENTS_U1) use_nl(evt)*/ --4769388
evt.event_status_code
,evt.process_status_code
,evt.last_update_date
,evt.last_updated_by
,evt.last_update_login
,evt.program_update_date
,evt.program_application_id
,evt.program_id
,evt.request_id
,evt.reference_char_4 --bug 13811614
--,DECODE(tmp.process_status_code,'P','P','U') new_event_status_code -- bug 4961401
,CASE WHEN evt.event_status_code = 'N' OR tmp.event_status_code = 'N' THEN 'N' --bug 13811614
WHEN tmp.process_status_code = 'P' THEN 'P'
ELSE 'U' END new_event_status_code
--,tmp.process_status_code new_process_status_code -- bug 4961401
,DECODE(evt.event_status_code,'N','P',tmp.process_status_code) new_process_status_code
,tmp.reference_char_4 new_reference_char_4 --bug 13811614
FROM xla_events evt
,xla_events_gt tmp
WHERE evt.event_id = tmp.event_id
AND evt.application_id = g_application_id
)
SET event_status_code = new_event_status_code
,process_status_code = new_process_status_code
,reference_char_4 = new_reference_char_4
,last_update_date = sysdate
,last_updated_by = xla_environment_pkg.g_usr_id
,last_update_login = xla_environment_pkg.g_login_id
,request_id = g_report_request_id;
(p_msg => 'Number of events updated = '||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
UPDATE xla_events xe
SET xe.event_status_code = 'P'
,xe.process_status_code = 'P'
,xe.last_update_date = sysdate
,xe.last_updated_by = xla_environment_pkg.g_usr_id
,xe.last_update_login = xla_environment_pkg.g_login_id
,xe.request_id = g_report_request_id
WHERE xe.event_id IN (SELECT xle.event_id
FROM gl_ledgers glg
,xla_acctg_methods_b xam
,xla_acctg_method_rules xamr
,xla_prod_acct_headers xpah
,xla_event_types_b xetb
,xla_aad_line_defn_assgns xald
,xla_line_definitions_b xldb
,xla_ledger_relationships_v xlr
,xla_ledger_options xlo
,xla_events_gt xle
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 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 xetb.event_type_code = xle.event_type_code
AND xle.event_status_code = 'U'
AND xle.process_status_code = 'U'
AND glg.ledger_id = xlr.ledger_id
AND xlr.primary_ledger_id = xle.ledger_id
AND xlr.relationship_enabled_flag = 'Y'
AND xlr.ledger_id = xlo.ledger_id
AND xlo.application_id = xle.application_id
AND xlo.enabled_flag = 'Y'
AND xald.application_id(+) = xpah.application_id
AND xald.amb_context_code(+) = xpah.amb_context_code
AND xald.product_rule_type_code(+) = xpah.product_rule_type_code
AND xald.product_rule_code(+) = xpah.product_rule_code
AND xald.event_class_code(+) = xpah.event_class_code
AND xald.event_type_code(+) = xpah.event_type_code
AND xald.application_id = xldb.application_id(+)
AND xald.amb_context_code = xldb.amb_context_code(+)
AND xald.event_class_code = xldb.event_class_code (+)
AND xald.event_type_code = xldb.event_type_code(+)
AND xald.line_definition_owner_code = xldb.line_definition_owner_code(+)
AND xald.line_definition_code = xldb.line_definition_code(+)
AND xldb.enabled_flag(+) = 'Y'
GROUP BY xle.event_id
HAVING Sum(CASE
WHEN xle.event_date BETWEEN Nvl(xamr.start_date_active,xle.event_date)
AND Nvl(xamr.end_date_active,xle.event_date)
THEN Decode(Nvl(xpah.accounting_required_flag,'N'),'Y'
,Decode(Nvl(xldb.budgetary_control_flag,'X'),xle.budgetary_control_flag,1,0)
,0)
ELSE 0
END) = 0)
AND xe.event_status_code = 'U'
AND xe.process_status_code = 'U'
AND xe.application_id = g_application_id;
(p_msg => 'Number of events updated (accounting not needed): = '||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
l_parameter_list.DELETE;
l_parameter_list.DELETE;
FOR c IN (SELECT event_class_code FROM xla_evt_class_orders_gt WHERE processing_order = p_processing_order) LOOP
IF l_concat_classes IS NULL THEN
l_concat_classes := C_QUOTE;
FOR c IN (SELECT xec.event_class_code
FROM xla_evt_class_orders_gt xec
,xla_event_class_attrs xea
WHERE xea.application_id = g_application_id
AND xea.event_class_code = xec.event_class_code
AND xea.event_class_group_code = g_process_category
AND xec.processing_order = p_processing_order) LOOP
IF l_concat_classes IS NULL THEN
l_concat_classes := C_QUOTE;