[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| Delete_Incomplete_Reversal |
+===========================================================================*/
--=============================================================================
-- *********** Local Trace Routine **********
--=============================================================================
C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
g_last_updated_by NUMBER;
g_last_update_login NUMBER;
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
,g_application_id
,g_application_id
,g_ledger_id
,NULL
,C_TYPE_MANUAL
,g_batch_code
,sysdate
,g_last_updated_by -- xla_environment_pkg.g_usr_id
,sysdate
,g_last_updated_by -- xla_environment_pkg.g_usr_id
,g_last_update_login -- xla_environment_pkg.g_login_id
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,g_accounting_batch_id
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL )
RETURNING entity_id INTO g_manual_entity_id ;
PROCEDURE delete_incomplete_journals
IS
l_log_module VARCHAR2(240);
l_log_module := C_DEFAULT_MODULE||'.delete_incomplete_journals';
(p_msg => 'BEGIN of procedure delete_incomplete_journals '
,p_level => C_LEVEL_PROCEDURE
,p_module =>l_log_module);
DELETE FROM xla_accounting_errors
WHERE event_id IN
( SELECT reference_num_2 FROM xla_events_gt
WHERE reference_char_1 = 'E'
AND reference_num_2 IS NOT NULL );
(p_msg => 'Number of errors deleted = '||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
DELETE FROM xla_distribution_links
WHERE ae_header_id IN
(SELECT /*+ leading(XE) use_nl(XH) unnest index(xh,XLA_AE_HEADERS_N2) */ xh.ae_header_id
FROM xla_events_gt xe,
xla_ae_headers xh
WHERE xe.reference_char_1 = 'E'
AND xh.application_id = xe.application_id
AND xh.event_id = xe.reference_num_2
AND xh.accounting_entry_status_code <> 'F'
AND xe.reference_num_2 IS NOT NULL
AND xh.application_id = g_application_id
)
AND application_id = g_application_id;
(p_msg => 'Number of distribution links deleted = '||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
DELETE /*+ index(XLA_AE_SEGMENT_VALUES, XLA_AE_SEGMENT_VALUES_U1) */
FROM xla_ae_segment_values
WHERE ae_header_id IN
(SELECT /*+ leading(XE) use_nl(XH) unnest index(xh,XLA_AE_HEADERS_N2) */ xh.ae_header_id
FROM xla_events_gt xe,
xla_ae_headers xh
WHERE xe.reference_char_1 = 'E'
AND xh.application_id = xe.application_id
AND xh.event_id = xe.reference_num_2
AND xh.accounting_entry_status_code <> 'F'
AND xe.reference_num_2 IS NOT NULL
AND xh.application_id = g_application_id
);
(p_msg => 'Number of segment values deleted = '||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
DELETE /*+ index(xal_acs,XLA_AE_LINE_ACS_U1) */
FROM xla_ae_line_acs xal_acs
WHERE xal_acs.ae_header_id IN
(SELECT/*+ leading(evt) use_nl(aeh) unnest index(aeh,XLA_AE_HEADERS_N2) */ aeh.ae_header_id
FROM xla_events_gt evt
,xla_ae_headers aeh
WHERE evt.reference_char_1 = 'E'
AND aeh.application_id = evt.application_id
AND aeh.event_id = evt.reference_num_2
AND aeh.accounting_entry_status_code <> 'F'
AND evt.reference_num_2 IS NOT NULL
AND aeh.application_id = g_application_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 /*+ leading(evt) use_nl(aeh) unnest index(aeh,XLA_AE_HEADERS_N2) */ aeh.ae_header_id
FROM xla_events_gt evt
,xla_ae_headers aeh
WHERE evt.reference_char_1 = 'E'
AND aeh.application_id = evt.application_id
AND aeh.event_id = evt.reference_num_2
AND aeh.accounting_entry_status_code <> 'F'
AND evt.reference_num_2 IS NOT NULL
AND aeh.application_id = g_application_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 /*+ leading(xe) use_nl(xh) unnest index(xh,XLA_AE_HEADERS_N2) */ xh.ae_header_id
FROM xla_events_gt xe,
xla_ae_headers xh
WHERE xe.reference_char_1 = 'E'
AND xh.application_id = xe.application_id
AND xh.event_id = xe.reference_num_2
AND xh.accounting_entry_status_code <> 'F'
AND xe.reference_num_2 IS NOT NULL
AND xh.application_id = g_application_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 reference_num_2
FROM xla_events_gt
WHERE reference_char_1 = 'E'
AND reference_num_2 IS NOT NULL )
AND accounting_entry_status_code <> 'F' ;
(p_msg => 'Number of ae headers deleted = '||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
(p_msg => 'END procedure delete_incomplete_journals'
,p_level => C_LEVEL_PROCEDURE
,p_module =>l_log_module);
(p_location => 'xla_reverse_events_pvt_pkg.delete_incomplete_journals');
END delete_incomplete_journals ;
SELECT /*+ leading(evt) use_nl(evt,xah,xte) INDEX(xah,xla_ae_headers_n2)INDEX(xte,XLA_TRANSACTION_ENTITIES_U1) */
DISTINCT xte.ledger_id trx_ledger_id ,
xte.entity_code trx_entity_code ,
xah.gl_transfer_status_code ,
evt.*
FROM xla_events_gt evt ,
xla_ae_headers xah ,
XLA_TRANSACTION_ENTITIES_UPG xte
WHERE xah.application_id(+) = evt.application_id
AND xah.event_id(+) = evt.event_number
AND xte.APPLICATION_ID(+) = xah.application_id
AND xte.entity_id(+) = xah.entity_id
AND xah.application_id(+) = g_application_id
AND xte.application_id(+) = g_application_id
AND (
( xah.gl_transfer_status_code <> 'Y' OR xah.gl_transfer_status_code IS NULL )
OR
evt.BUDGETARY_CONTROL_FLAG = 'Y'
OR
xte.ledger_id <> evt.ledger_id
OR
xte.entity_code <> evt.entity_code
OR
evt.BUDGETARY_CONTROL_FLAG IS NULL -- will be NULL only when Event_id,application_id is incorrect in the rest we default to N even if its NULL
) ;
UPDATE xla_events_gt t
SET t.process_status_code = 'E'
WHERE t.event_id = i.event_id ;
l_rows_inserted NUMBER ;
INSERT INTO XLA_EVENTS
( EVENT_ID,
APPLICATION_ID,
EVENT_TYPE_CODE,
EVENT_DATE,
ENTITY_ID,
EVENT_STATUS_CODE,
PROCESS_STATUS_CODE,
EVENT_NUMBER,
ON_HOLD_FLAG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
PROGRAM_UPDATE_DATE,
PROGRAM_APPLICATION_ID,
TRANSACTION_DATE,
BUDGETARY_CONTROL_FLAG,
UPG_BATCH_ID ,
REFERENCE_NUM_1
)
SELECT evt.event_id ,
g_APPLICATION_ID,
C_TYPE_MANUAL ,
evt.EVENT_DATE,
evt.ENTITY_ID,
evt.EVENT_STATUS_CODE,
evt.PROCESS_STATUS_CODE,
g_event_number + ROWNUM EVENT_NUMBER,
'N' ON_HOLD_FLAG,
SYSDATE CREATION_DATE,
g_last_updated_by CREATED_BY,
SYSDATE LAST_UPDATE_DATE,
g_last_updated_by LAST_UPDATED_BY,
g_last_update_login LAST_UPDATE_LOGIN,
SYSDATE PROGRAM_UPDATE_DATE,
g_application_id PROGRAM_APPLICATION_ID,
evt.event_date TRANSACTION_DATE,
evt.BUDGETARY_CONTROL_FLAG ,
-9999 UPG_BATCH_ID ,
evt.event_number REFERENCE_NUM_1
FROM xla_events_gt evt
WHERE 1 = 1
AND NOT EXISTS
( SELECT /*+ INDEX(xe,XLA_EVENTS_U1) */1
FROM xla_events xe
WHERE xe.application_id = evt.application_id
AND xe.event_id = evt.event_id
AND xe.application_id = g_application_id ) ;
l_rows_inserted := SQL%ROWCOUNT ;
(p_msg => 'Number of Events Created : ' || l_rows_inserted
,p_level => C_LEVEL_PROCEDURE
,p_module =>l_log_module);
g_event_number := g_event_number + l_rows_inserted ;
INSERT INTO XLA_AE_HEADERS_GT
( AE_HEADER_ID
,LEDGER_ID
,ENTITY_ID
,EVENT_ID
,EVENT_TYPE_CODE
,ACCOUNTING_DATE
,GL_TRANSFER_STATUS_CODE
,JE_CATEGORY_NAME
,ACCOUNTING_ENTRY_STATUS_CODE
,ACCOUNTING_ENTRY_TYPE_CODE
,DESCRIPTION
,DOC_SEQUENCE_ID
,DOC_SEQUENCE_VALUE
,BUDGET_VERSION_ID
,BALANCE_TYPE_CODE
,PERIOD_NAME
,DOC_CATEGORY_CODE
,PARENT_HEADER_ID
,ACCRUAL_REVERSAL_FLAG
,EVENT_NUMBER
)
SELECT /*+ leading(xe) use_nl(xe,xah) INDEX(xah,XLA_AE_HEADERS_N2) */
XLA_AE_HEADERS_S.NEXTVAL
,xah.LEDGER_ID
,xe.ENTITY_ID
,xe.EVENT_ID
,xe.EVENT_TYPE_CODE
,xe.EVENT_DATE
,DECODE(NVL(g_gl_transfer_flag,'Y'),'Y','N','NT')
,xah.JE_CATEGORY_NAME
,'N' ACCOUNTING_ENTRY_STATUS_CODE
, C_TYPE_MANUAL ACCOUNTING_ENTRY_TYPE_CODE
, l_reversal_label || ':' || xah.DESCRIPTION
,xah.DOC_SEQUENCE_ID
,xah.DOC_SEQUENCE_VALUE
,NULL
,xah.BALANCE_TYPE_CODE
,( SELECT gps.PERIOD_NAME
FROM GL_PERIOD_STATUSES gps
WHERE 1 = 1
AND gps.ledger_id = xah.ledger_id
AND xe.event_date between gps.start_date and gps.end_Date
AND gps.application_id = 101
AND gps.adjustment_period_flag = 'N'
)
,xah.DOC_CATEGORY_CODE
,xah.AE_HEADER_ID
,'N'
,xah.event_id
FROM XLA_AE_HEADERS xah
,XLA_EVENTS_GT xe
WHERE xe.application_id = xah.application_id
AND xe.event_number = xah.event_id
AND xah.application_id = g_application_id
AND xe.PROCESS_STATUS_CODE = 'U'
AND xah.ledger_id IN ( SELECT /*+ un_nest */xlr.ledger_id
FROM xla_ledger_relationships_v xlr
WHERE xlr.primary_ledger_id = g_ledger_id
AND xlr.relationship_enabled_flag = 'Y'
AND EXISTS (SELECT 1
FROM xla_ledger_options xlo
WHERE application_id = g_application_id
AND DECODE(xlr.ledger_category_code ,'ALC',xlr.ledger_id ,xlo.ledger_id) = xlr.ledger_id
AND DECODE(xlr.ledger_category_code ,'SECONDARY',xlo.capture_event_flag ,'N') = 'N'
AND DECODE(xlr.ledger_category_code ,'ALC','Y',xlo.enabled_flag) = 'Y' )
UNION ALL
SELECT gl.ledger_id
FROM gl_ledgers gl
WHERE gl.ledger_id = g_ledger_id
AND gl.ledger_category_code = 'SECONDARY'
);
INSERT INTO XLA_AE_HEADERS
( APPLICATION_ID
,AE_HEADER_ID
,LEDGER_ID
,ENTITY_ID
,EVENT_ID
,EVENT_TYPE_CODE
,ACCOUNTING_DATE
,GL_TRANSFER_STATUS_CODE
,JE_CATEGORY_NAME
,ACCOUNTING_ENTRY_STATUS_CODE
,ACCOUNTING_ENTRY_TYPE_CODE
,DESCRIPTION
,DOC_SEQUENCE_ID
,DOC_SEQUENCE_VALUE
,ACCOUNTING_BATCH_ID
,BUDGET_VERSION_ID
,BALANCE_TYPE_CODE
,PERIOD_NAME
,DOC_CATEGORY_CODE
,ACCRUAL_REVERSAL_FLAG
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,upg_batch_id
)
SELECT g_application_id
,AE_HEADER_ID
,LEDGER_ID
,ENTITY_ID
,EVENT_ID
,EVENT_TYPE_CODE
,ACCOUNTING_DATE
,GL_TRANSFER_STATUS_CODE
,JE_CATEGORY_NAME
,ACCOUNTING_ENTRY_STATUS_CODE
,ACCOUNTING_ENTRY_TYPE_CODE
,DESCRIPTION
,DOC_SEQUENCE_ID
,DOC_SEQUENCE_VALUE
,g_accounting_batch_id
,BUDGET_VERSION_ID
,BALANCE_TYPE_CODE
,PERIOD_NAME
,DOC_CATEGORY_CODE
,ACCRUAL_REVERSAL_FLAG
,sysdate
,g_last_updated_by
,sysdate
,g_last_updated_by
,g_last_update_login
,-9999
FROM XLA_AE_HEADERS_GT
;
INSERT INTO xla_ae_header_acs(
ae_header_id
,analytical_criterion_code
,analytical_criterion_type_code
,amb_context_code
,ac1
,ac2
,ac3
,ac4
,ac5
,object_version_number)
SELECT xah.ae_header_id
,xah_acs.analytical_criterion_code
,xah_acs.analytical_criterion_type_code
,xah_acs.amb_context_code
,xah_acs.ac1
,xah_acs.ac2
,xah_acs.ac3
,xah_acs.ac4
,xah_acs.ac5
,1
FROM xla_ae_header_acs xah_acs ,
xla_ae_headers_gt xah
WHERE xah_acs.ae_header_id = xah.parent_header_id;
INSERT INTO xla_ae_lines
(application_id
,ae_header_id
,ae_line_num
,displayed_line_number
,code_combination_id
,gl_transfer_mode_code
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,party_id
,party_site_id
,party_type_code
,entered_dr
,entered_cr
,accounted_dr
,accounted_cr
,unrounded_entered_dr
,unrounded_entered_cr
,unrounded_accounted_dr
,unrounded_accounted_cr
,description
,statistical_amount
,currency_code
,currency_conversion_type
,currency_conversion_date
,currency_conversion_rate
,accounting_class_code
,jgzz_recon_ref
,gl_sl_link_id
,gl_sl_link_table
,attribute_category
,encumbrance_type_id
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,gain_or_loss_flag
,ledger_id
,accounting_date
,mpa_accrual_entry_flag
,control_balance_flag
,analytical_balance_flag
,upg_batch_id ) -- 4262811
SELECT /*+ leading(xah) USE_NL_WITH_INDEX(xal,XLA_AE_LINES_U1) */
xal.application_id
,xah.ae_header_id
,xal.ae_line_num
,xal.displayed_line_number
,xal.code_combination_id
,xal.gl_transfer_mode_code
,sysdate
,g_last_updated_by
,sysdate
,g_last_updated_by
,g_last_update_login
,xal.party_id
,xal.party_site_id
,xal.party_type_code
,DECODE(xlo.ACCT_REVERSAL_OPTION_CODE, C_REVERSAL_SWITCH_DR_CR,xal.entered_cr, -1 * xal.entered_dr)
,DECODE(xlo.ACCT_REVERSAL_OPTION_CODE, C_REVERSAL_SWITCH_DR_CR,xal.entered_dr, -1 * xal.entered_cr)
,DECODE(xlo.ACCT_REVERSAL_OPTION_CODE, C_REVERSAL_SWITCH_DR_CR,xal.accounted_cr, -1 * xal.accounted_dr)
,DECODE(xlo.ACCT_REVERSAL_OPTION_CODE, C_REVERSAL_SWITCH_DR_CR,xal.accounted_dr, -1 * xal.accounted_cr)
,DECODE(xlo.ACCT_REVERSAL_OPTION_CODE, C_REVERSAL_SWITCH_DR_CR,NVL(xal.unrounded_entered_cr,xal.entered_cr),
-1 * NVL(xal.unrounded_entered_dr,xal.entered_dr))
,DECODE(xlo.ACCT_REVERSAL_OPTION_CODE, C_REVERSAL_SWITCH_DR_CR,NVL(xal.unrounded_entered_dr,xal.entered_dr) ,
-1 * NVL(xal.unrounded_entered_cr,xal.entered_cr))
,DECODE(xlo.ACCT_REVERSAL_OPTION_CODE, C_REVERSAL_SWITCH_DR_CR,NVL(xal.unrounded_accounted_cr,xal.accounted_cr),
-1 * NVL(xal.unrounded_accounted_dr,xal.accounted_dr))
,DECODE(xlo.ACCT_REVERSAL_OPTION_CODE, C_REVERSAL_SWITCH_DR_CR,NVL(xal.unrounded_accounted_dr,xal.accounted_dr),
-1 * NVL(xal.unrounded_accounted_cr,xal.accounted_cr))
,l_reversal_label || ':' || xal.description
,xal.statistical_amount
,xal.currency_code
,xal.currency_conversion_type
,xal.currency_conversion_date
,xal.currency_conversion_rate
,xal.accounting_class_code
,xal.jgzz_recon_ref
,XLA_GL_SL_LINK_ID_S.NEXTVAL
,'XLAJEL'
,xal.attribute_category
,xal.encumbrance_type_id
,xal.attribute1
,xal.attribute2
,xal.attribute3
,xal.attribute4
,xal.attribute5
,xal.attribute6
,xal.attribute7
,xal.attribute8
,xal.attribute9
,xal.attribute10
,xal.attribute11
,xal.attribute12
,xal.attribute13
,xal.attribute14
,xal.attribute15
,xal.gain_or_loss_flag
,xah.ledger_id
,xah.accounting_date
,NVL(xal.mpa_accrual_entry_flag,'N')
,( SELECT DECODE(xal.accounting_class_code,
'INTER', NULL,
'INTRA', NULL,
DECODE(NVL(ccid.reference3,'N'),'N',NULL,
'R', NULL,
DECODE(ccid.account_type
, 'A', 'P'
, 'L', 'P'
, 'O', 'P'
, NULL)
))
FROM gl_code_combinations ccid
WHERE ccid.code_combination_id = xal.code_combination_id
) CONTROL_BALANCE_FLAG
,DECODE(NVL(xal.analytical_balance_flag ,'N'),'N',NULL,'P')
, -9999
FROM xla_ae_lines xal ,
xla_ae_headers_gt xah ,
xla_ledger_options xlo ,
gl_ledgers gl
WHERE xal.application_id = g_application_id
AND xal.ae_header_id = xah.parent_header_id
AND xah.ledger_id = gl.ledger_id
AND xlo.application_id = xal.application_id
AND xlo.ledger_id = DECODE(gl.ledger_category_code,'ALC',g_ledger_id,xal.ledger_id)
;
INSERT INTO xla_ae_line_acs(
ae_header_id
,ae_line_num
,analytical_criterion_code
,analytical_criterion_type_code
,amb_context_code
,ac1
,ac2
,ac3
,ac4
,ac5
,object_version_number)
SELECT /*+ leading(xah) USE_NL_WITH_INDEX(xal_acs,XLA_AE_LINE_ACS_U1) */xah.ae_header_id
,xal_acs.ae_line_num
,xal_acs.analytical_criterion_code
,xal_acs.analytical_criterion_type_code
,xal_acs.amb_context_code
,xal_acs.ac1
,xal_acs.ac2
,xal_acs.ac3
,xal_acs.ac4
,xal_acs.ac5
,1
FROM xla_ae_line_acs xal_acs
, xla_ae_headers_gt xah
WHERE xal_acs.ae_header_id = xah.parent_header_id ;
INSERT INTO xla_distribution_links
(application_id
,event_id
,ae_header_id
,ae_line_num
,source_distribution_type
,statistical_amount
,ref_ae_header_id
,ref_temp_line_num
,merge_duplicate_code
,temp_line_num
,ref_event_id
,event_class_code
,event_type_code
,unrounded_entered_dr
,unrounded_entered_cr
,unrounded_accounted_dr
,unrounded_accounted_cr
,upg_batch_id )
SELECT /*+ leading(xah) USE_NL_WITH_INDEX(xal,XLA_AE_LINES_U1) */
xal.application_id
,xah.event_id
,xal.ae_header_id
,xal.ae_line_num
,C_TYPE_MANUAL -- source distribution type
,xal.statistical_amount -- statistical amount
,xah.parent_header_id -- ref ae header id
,xal.ae_line_num -- ref temp line num
,'N' -- merge duplicate code
,ae_line_num -- temp line num
,xah.EVENT_NUMBER -- ref event id
,xah.event_type_code -- event class code
,xah.event_type_code -- event type code
,xal.unrounded_entered_dr
,xal.unrounded_entered_cr
,xal.unrounded_accounted_dr
,xal.unrounded_accounted_cr
,-9999
FROM xla_ae_headers_gt xah
,xla_ae_lines xal
WHERE 1 = 1
AND xal.application_id = g_application_id
AND xal.ae_header_id = xah.ae_header_id;
UPDATE SET int.process_status_code = DECODE(evt.process_status_code ,'U','P','E')
,int.REVERSAL_ENTITY_ID = evt.entity_id
,int.REVERSAL_EVENT_ID = evt.event_id
,int.ACCOUNTING_BATCH_ID = DECODE(evt.process_status_code ,'U',g_accounting_batch_id,NULL)
;
( p_msg => 'Number of Rows Updated in XLA_REVERSE_EVENTS_INTERFACE ' || SQL%ROWCOUNT
,p_level => C_LEVEL_PROCEDURE
,p_module =>l_log_module);
UPDATE SET xe.event_status_code = DECODE(evt.process_status_code ,'U','P','U')
,xe.process_status_code = DECODE(evt.process_status_code ,'U','P','I')
WHERE xe.event_status_code <> 'P' ;
( p_msg => 'Number of Rows Updated in XLA_EVENTS ' || SQL%ROWCOUNT
,p_level => C_LEVEL_PROCEDURE
,p_module =>l_log_module);
UPDATE SET xah.accounting_entry_status_code = DECODE(evt.process_status_code ,'U','F',xah.accounting_entry_status_code )
WHERE xah.accounting_entry_status_code <> 'F' ;
( p_msg => 'Number of Rows Updated in XLA_AE_HEADERS ' || SQL%ROWCOUNT
,p_level => C_LEVEL_PROCEDURE
,p_module =>l_log_module);
xla_accounting_err_pkg.insert_errors;
INSERT INTO xla_events_gt
(entity_id
,application_id
,ledger_id
,entity_code
,event_id
,event_class_code
,event_type_code
,event_number
,event_date
,transaction_date
,event_status_code
,process_status_code
,budgetary_control_flag
,reference_num_1
,reference_num_2
,reference_char_1
)
VALUES (
NVL(p_array_event_data.reference_num_1(i) , g_manual_entity_id ) -- use the existing entity_id in the interface or use the new one
,p_array_event_data.application_id(i)
,p_array_event_data.ledger_id(i) -- contains the LEDGER_ID from Interface
,p_array_event_data.entity_code(i) -- contains the ENTITY_CODE from Interface
,NVL(p_array_event_data.event_id(i), XLA_EVENTS_S.NEXTVAL ) -- use the existing event_id in the interface or use the new one
,C_TYPE_MANUAL
,C_TYPE_MANUAL
,p_array_event_data.event_number(i) -- contains the ORIGINAL EVENT_ID
,p_array_event_data.event_date(i) -- contains the REVERSAL_GL_DATE from Interface
,p_array_event_data.transaction_date(i)
,p_array_event_data.event_status_code(i)
,p_array_event_data.process_status_code(i)
,p_array_event_data.budgetary_control_flag(i)
,p_array_event_data.reference_num_1(i) -- contains the REVERSAL_ENTITY_ID from Interface
,p_array_event_data.reference_num_2(i) -- contains the REVERSAL_EVENT_ID from Interface
,p_array_event_data.reference_char_1(i) -- contains the PROCESS_STATUS_CODE from Interface
);
(p_msg => 'Events Inserted Count:'||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
SELECT MAX(EVENT_DATE) , MAX(event_number)
INTO l_max_event_Date ,
l_max_event_id
FROM xla_events_gt ;
UPDATE xla_transaction_entities_upg xtem
SET xtem.valuation_method = ( select xte.valuation_method
from xla_transaction_entities_upg xte ,
xla_events xe
where xe.application_id = g_application_id
and xe.event_id = l_max_event_id
and xte.application_id = g_application_id
and xte.entity_id = xe.entity_id
)
WHERE xtem.application_id = g_application_id
AND xtem.entity_id = g_manual_entity_id
AND xtem.valuation_method IS NULL ;
delete_incomplete_journals ;
SELECT application_id
FROM xla_subledgers
WHERE application_id = p_application_id ;
SELECT /*+ USE_NL(evt,xe) INDEX(xe,XLA_EVENTS_U1) */null entity_id ,
evt.APPLICATION_ID ,
evt.LEDGER_ID ,
evt.ENTITY_CODE ,
evt.REVERSAL_EVENT_ID EVENT_ID ,
evt.EVENT_ID EVENT_NUMBER ,
evt.REVERSAL_GL_DATE EVENT_DATE,
SYSDATE TRANSACTION_DATE,
''U'' EVENT_STATUS_CODE,
''U'' PROCESS_STATUS_CODE,
NVL2(xe.application_id,NVL(xe.BUDGETARY_CONTROL_FLAG,''N''),NULL) BUDGETARY_CONTROL_FLAG,
evt.REVERSAL_ENTITY_ID REFERENCE_NUM_1 ,
evt.REVERSAL_EVENT_ID REFERENCE_NUM_2 ,
evt.process_status_code REFERENCE_CHAR_1 ,
NULL REFERENCE_CHAR_2
FROM XLA_REVERSE_EVENTS_INTERFACE evt
,xla_events xe
WHERE evt.application_id = :1
AND evt.ledger_id = :2
AND evt.batch_code = :3
AND evt.process_status_code IN ( ''U'',''E'')
AND xe.application_id(+) = :4
AND xe.event_id(+) = evt.event_id
ORDER BY evt.event_id
FOR UPDATE OF xe.event_id SKIP LOCKED
';
SELECT XLA_ACCOUNTING_BATCHES_S.NEXTVAL
INTO g_accounting_batch_id
FROM DUAL ;
g_last_updated_by := nvl(xla_environment_pkg.g_usr_id,-1);
g_last_update_login := nvl(xla_environment_pkg.g_login_id,-1);
PROCEDURE delete_incomplete_reversal( p_application_id IN NUMBER
, p_batch_code IN VARCHAR2
, p_ledger_id IN NUMBER
, p_delete_flag IN VARCHAR2 DEFAULT 'N'
, p_batch_commit_size IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
) IS
CURSOR csr_application_id IS
SELECT application_id
FROM xla_subledgers
WHERE application_id = p_application_id ;
SELECT /*+use_nl(xeg,xe,xte) INDEX(xe,XLA_EVENTS_U1) INDEX(xte,XLA_TRANSACTION_ENTITIES_U1)*/
xe.event_status_code ,
xe.event_id ,
xte.entity_code
FROM xla_events_gt xeg, xla_events xe, xla_transaction_entities_upg 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 xe.application_id(+) = app_id
AND xeg.event_id IS NOT NULL
AND ( xe.event_status_code not in ('I', 'N', 'U')
OR xte.entity_code is null
OR xe.event_id is null
OR xte.entity_code <> C_TYPE_MANUAL );
SELECT intf.reversal_entity_id entity_id
,intf.application_id
,intf.ledger_id
,NULL entity_code
,intf.reversal_event_id event_id
,intf.reversal_event_id event_number
,intf.reversal_gl_date event_date
,intf.reversal_gl_date transaction_date
,''U'' event_status_code
,intf.process_status_code
,''N'' budgetary_control_flag
,intf.reversal_entity_id reference_num_1
,intf.reversal_event_id reference_num_2
,intf.process_status_code reference_char_1
,intf.rowid reference_char_2
FROM xla_reverse_events_interface intf
WHERE intf.batch_code = :1
AND intf.application_id = :2
AND intf.ledger_id = :3
AND intf.process_status_code IN( ''U'', ''E'' )
ORDER BY intf.reversal_event_id
FOR UPDATE OF intf.reversal_event_id SKIP LOCKED
';
l_delete_count NUMBER;
l_deleted_count NUMBER;
l_log_module := C_DEFAULT_MODULE||'.delete_incomplete_reversal';
(p_msg => 'BEGIN of procedure delete_incomplete_reversal'
,p_level => C_LEVEL_PROCEDURE
,p_module =>l_log_module);
(p_msg => 'p_delete_flag = '||p_delete_flag
,p_level => C_LEVEL_PROCEDURE
,p_module =>l_log_module);
IF p_delete_flag IS NULL or p_delete_flag NOT IN ('Y','N') THEN
x_return_status := FND_API.G_RET_STS_ERROR ;
,p_value_1 => 'Delete Flag has an invalid value. It can have either Y or N as valid values.'
);
INSERT INTO xla_events_gt
(entity_id
,application_id
,ledger_id
,entity_code
,event_id
,event_number
,event_date
,transaction_date
,event_status_code
,process_status_code
,reference_num_1
,reference_num_2
,reference_char_1
,REFERENCE_CHAR_2
)
VALUES (
l_array_del_event_data.entity_id(i)
,l_array_del_event_data.application_id(i)
,l_array_del_event_data.ledger_id(i)
,C_TYPE_MANUAL -- entity_code
,l_array_del_event_data.event_id(i)
,l_array_del_event_data.event_number(i)
,l_array_del_event_data.event_date(i)
,l_array_del_event_data.transaction_date(i)
,l_array_del_event_data.event_status_code(i)
,l_array_del_event_data.process_status_code(i)
,l_array_del_event_data.reference_num_1(i)
,l_array_del_event_data.reference_num_2(i)
,l_array_del_event_data.reference_char_1(i)
,l_array_del_event_data.reference_char_2(i)
);
(p_msg => 'Rows Inserted into XLA_EVENTS_GT' || l_rowcount_gt
,p_level => C_LEVEL_PROCEDURE
,p_module =>l_log_module);
,p_value_1 => 'The event('|| i.event_id||') to be deleted must be in status I, N or U.Error in Iteration Number '||
l_iteration_count||'.'
);
,p_value_1 => 'Entity code for reversal event('||i.event_id||')is not MANUAL. This API can be called to delete event for MANUAL entity.'||
'Error in Iteration Number '||l_iteration_count||'.'
);
delete_incomplete_journals ;
(p_msg => 'returned from procedure delete_incomplete_journals'
,p_level => C_LEVEL_PROCEDURE
,p_module =>l_log_module);
SELECT COUNT(1)
INTO l_delete_count
FROM xla_events_gt
WHERE reference_num_2 IS NOT NULL ;
(p_msg => 'Rows to be deleted from Events table: ' || l_delete_count
,p_level => C_LEVEL_PROCEDURE
,p_module =>l_log_module);
DELETE
FROM xla_events xe
WHERE application_id = g_application_id
AND event_id IN (SELECT reference_num_2
FROM xla_events_gt
WHERE reference_char_1 = 'E'
AND reference_num_2 IS NOT NULL )
AND NOT EXISTS
( SELECT 1
FROM xla_ae_headers xah
WHERE xah.application_id = xe.application_id
AND xah.event_id = xe.event_id
) ;
l_deleted_count := SQL%ROWCOUNT ;
(p_msg => 'Number of events deleted = '||l_deleted_count
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
IF l_deleted_count <> l_delete_count
THEN
x_return_status := FND_API.G_RET_STS_ERROR ;
,p_value_1 => 'Number of Events Deleted('||l_deleted_count||') is not same as the Events eligible for delete('||
l_delete_count||') in the interface table for the iteration number '||l_iteration_count||'.'
);
IF p_delete_flag = 'Y' THEN
DELETE /*+ rowid(xet) */
FROM XLA_REVERSE_EVENTS_INTERFACE xet
WHERE xet.rowid IN ( SELECT reference_char_2
FROM xla_events_gt
) ;
(p_msg => 'Number of rows DELETE FROM Interface = '|| SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
UPDATE /*+ rowid(xet) */XLA_REVERSE_EVENTS_INTERFACE xet
SET xet.REVERSAL_EVENT_ID = NULL
,xet.REVERSAL_ENTITY_ID = NULL
,xet.ACCOUNTING_BATCH_ID = NULL
,xet.process_status_code = 'U'
WHERE xet.rowid IN ( SELECT reference_char_2
FROM xla_events_gt
) ;
(p_msg => 'Number of rows UPDATED in Interface = '|| SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
DELETE FROM xla_events_gt;
(p_msg => 'End of delete_incomplete_reversal '
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
(p_location => 'xla_reverse_events_pvt_pkg.delete_incomplete_reversal');
END delete_incomplete_reversal ;