[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE delete_journal_entries
(p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2
,p_application_id IN INTEGER
,p_event_id IN INTEGER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'delete_journal_entries';
l_log_module := C_DEFAULT_MODULE||'.delete_journal_entries';
trace(p_msg => 'BEGIN of procedure delete_journal_entries',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
SELECT MAX(NVL(gl_transfer_status_code,'N')) -- N, NT, S, Y
INTO l_gl_transfer_status_code
FROM xla_ae_headers
WHERE application_id = p_application_id
AND event_id = p_event_id;
' Journal entries cannot be deleted because it has either been Transferred or set to Not Transferred.',
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
,p_error_msg => 'Journal entries cannot be deleted because it has either been Transferred or set to Not Transferred.');
trace(p_msg => 'Calling xla_journal_entries_pkg.delete_journal_entries.',
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_journal_entries_pkg.delete_journal_entries
(p_application_id => p_application_id
,p_event_id => p_event_id);
trace(p_msg => 'Update xla_events event_id'||p_event_id||' to Unprocessed.',
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
UPDATE XLA_EVENTS
SET EVENT_STATUS_CODE = xla_events_pub_pkg.C_EVENT_UNPROCESSED
,PROCESS_STATUS_CODE = xla_events_pkg.C_INTERNAL_UNPROCESSED
WHERE application_id = p_application_id
AND event_id = p_event_id;
trace(p_msg => 'END of procedure delete_journal_entries',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
END delete_journal_entries;
a) delete the incomplete MPA
b) calls Create_Reversal_Entry of the ae_header_id to create the reversal of
the original entry, returning the new rev_ae_header_id and rev_event_id
i) calls Complete_Journal_Entry with rev_ae_header_id, p_event_id and
p_rev_flag = 'Y' to validate the reversal entry rev_ae_header_id and on
success,
-> calls Create_MRC_Reversal_Entry to create reversal of all other
ledgers and entries related to the original entry p_event_id.
c) Create a new event and entity, and map the original entry to the new
event id and entity id.
==============================================================================*/
PROCEDURE reverse_journal_entries
(p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2
,p_application_id IN INTEGER
,p_event_id IN INTEGER
,p_reversal_method IN VARCHAR2
,p_gl_date IN DATE
,p_post_to_gl_flag IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,x_rev_ae_header_id OUT NOCOPY INTEGER
,x_rev_event_id OUT NOCOPY INTEGER
,x_rev_entity_id OUT NOCOPY INTEGER
,x_new_event_id OUT NOCOPY INTEGER
,x_new_entity_id OUT NOCOPY INTEGER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'reverse_journal_entries';
SELECT xgl.currency_code, xsu.je_source_name,
xah.entity_id, xah.accounting_date,
xah.ledger_id, e.legal_entity_id, xah.accrual_reversal_flag,
xe.budgetary_control_flag
FROM xla_gl_ledgers_v xgl
, xla_ae_headers xah
, xla_subledgers xsu
, xla_transaction_entities e
, xla_events xe
WHERE xgl.ledger_id = xah.ledger_id
AND xsu.application_id = xah.application_id
AND xah.event_id = p_event_id
AND xah.application_id = p_application_id
AND ledger_category_code ='PRIMARY'
AND e.application_id = xah.application_id
AND e.entity_id = xah.entity_id
AND xe.application_id = xah.application_id
AND xe.event_id = xah.event_id
AND xah.accounting_entry_status_code = C_STATUS_FINAL_CODE
AND xah.parent_ae_header_id IS NULL
AND NOT EXISTS (SELECT 1
FROM xla_ae_headers xah2
WHERE xah2.application_id = p_application_id
AND xah2.event_id = p_event_id
AND xah2.accounting_entry_status_code = C_STATUS_FINAL_CODE
AND NVL(xah2.gl_transfer_status_code,'N') IN ('N','NT')); -- can be reversed only if it is transferred
trace(p_msg => 'Delete entries from xla_trial_balances',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
SELECT xah.ae_header_id BULK COLLECT INTO l_array_je_header_id
FROM xla_gl_ledgers_v xgl
, xla_ae_headers xah
, xla_subledgers xsu
, xla_transaction_entities e
, xla_events xe
WHERE xgl.ledger_id = xah.ledger_id
AND xsu.application_id = xah.application_id
AND xah.event_id = p_event_id
AND xah.application_id = p_application_id
AND ledger_category_code ='PRIMARY'
AND e.application_id = xah.application_id
AND e.entity_id = xah.entity_id
AND xe.application_id = xah.application_id
AND xe.event_id = xah.event_id
AND xah.accounting_entry_status_code = C_STATUS_FINAL_CODE
AND xah.parent_ae_header_id IS NULL
AND NOT EXISTS (SELECT 1
FROM xla_ae_headers xah2
WHERE xah2.application_id = p_application_id
AND xah2.event_id = p_event_id
AND xah2.accounting_entry_status_code = C_STATUS_FINAL_CODE
AND NVL(xah2.gl_transfer_status_code,'N') IN ('N','NT'));
SELECT MAX(NVL(MPA_ACCRUAL_ENTRY_FLAG,'N'))
INTO l_mpa_acc_rev_flag
FROM xla_ae_lines
WHERE application_id = p_application_id
AND ae_header_id in (SELECT ae_header_id
FROM xla_ae_headers
WHERE event_id = p_event_id
AND application_id = p_application_id);
FOR i in (SELECT ae_header_id
FROM xla_ae_headers
WHERE application_id = p_application_id
AND event_id = p_event_id
AND parent_ae_header_id IS NOT NULL
AND accounting_entry_status_code <> C_STATUS_FINAL_CODE ) LOOP
IF (C_LEVEL_STATEMENT >= g_log_level) THEN
trace(p_msg => 'Delete journal entry = '||i.ae_header_id,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
DELETE xla_ae_lines
WHERE application_id = p_application_id
AND ae_header_id = i.ae_header_id;
DELETE xla_distribution_links
WHERE application_id = p_application_id
AND ae_header_id = i.ae_header_id;
DELETE xla_ae_headers
WHERE application_id = p_application_id
AND ae_header_id = i.ae_header_id;
update xla_ae_headers
set accounting_entry_type_code = 'MANUAL'
where application_id = p_application_id
and event_id = p_event_id;
SELECT entity_id
INTO x_rev_entity_id
FROM xla_events
WHERE application_id = p_application_id
AND event_id = x_rev_event_id
AND rownum = 1;
UPDATE xla_transaction_entities
SET (entity_code
, source_id_int_1
, source_id_char_1
, 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_2
, source_id_char_2
, source_id_int_3
, source_id_char_3
, source_id_int_4
, source_id_char_4
, valuation_method
, source_application_id
, upg_batch_id
, upg_source_application_id
, upg_valid_flag
-- transaction_number
-- legal_entity_id
-- ledger_id
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login) = (SELECT 'MANUAL' -- entity_code This also prevents transaction to be used in bflow.
,source_id_int_1
,source_id_char_1
,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_2
,source_id_char_2
,source_id_int_3
,source_id_char_3
,source_id_int_4
,source_id_char_4
,valuation_method
,source_application_id
,upg_batch_id
,upg_source_application_id
,upg_valid_flag
-- transaction_number
-- legal_entity_id
-- ledger_id
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.user_id
FROM xla_transaction_entities
WHERE application_id = p_application_id
AND entity_id = l_entity_id)
WHERE application_id = p_application_id
AND entity_id = x_rev_entity_id;
UPDATE xla_events
SET event_status_code = xla_events_pub_pkg.C_EVENT_PROCESSED
, process_status_code = xla_events_pub_pkg.C_EVENT_PROCESSED
,(event_type_code
, event_date
, 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
, upg_batch_id
, upg_source_application_id
, upg_valid_flag
, transaction_date
, budgetary_control_flag
, merge_event_set_id
-- event_number
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_update_date
, program_application_id
, program_id
, request_id) = (SELECT 'MANUAL' -- event_type_code
, event_date
, 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
, upg_batch_id
, upg_source_application_id
, upg_valid_flag
, transaction_date
, budgetary_control_flag
, merge_event_set_id
-- event_number
, sysdate
, fnd_global.user_id
, sysdate
, fnd_global.user_id
, fnd_global.user_id
, sysdate
, -1
, -1
, -1
FROM xla_events
WHERE application_id = p_application_id
AND event_id = p_event_id)
WHERE application_id = p_application_id
AND event_id = x_new_event_id
RETURNING entity_id INTO x_new_entity_id;
UPDATE xla_transaction_entities
SET (entity_code
, source_id_int_1
, source_id_char_1
, 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_2
, source_id_char_2
, source_id_int_3
, source_id_char_3
, source_id_int_4
, source_id_char_4
, valuation_method
, source_application_id
, upg_batch_id
, upg_source_application_id
, upg_valid_flag
-- transaction_number
-- legal_entity_id
-- ledger_id
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login) = (SELECT 'MANUAL' -- entity_code This also prevents transaction to be used in bflow.
,source_id_int_1
,source_id_char_1
,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_2
,source_id_char_2
,source_id_int_3
,source_id_char_3
,source_id_int_4
,source_id_char_4
,valuation_method
,source_application_id
,upg_batch_id
,upg_source_application_id
,upg_valid_flag
-- transaction_number
-- legal_entity_id
-- ledger_id
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.user_id
FROM xla_transaction_entities
WHERE application_id = p_application_id
AND entity_id = l_entity_id)
WHERE application_id = p_application_id
AND entity_id = x_new_entity_id;
trace(p_msg => 'Update xla_ae_headers',
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
UPDATE xla_ae_headers
SET entity_id = x_new_entity_id
,event_id = x_new_event_id
,event_type_code = 'MANUAL'
,description = 'Data fix entry: event_id of '||p_event_id
WHERE application_id = p_application_id
AND event_id = p_event_id
RETURNING ae_header_id BULK COLLECT INTO l_array_ae_header_id;
trace(p_msg => 'Update xla_ae_lines',
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
UPDATE xla_ae_lines
SET description = 'Data fix entry: event_id of '||p_event_id
-- business_class_code = NULL -- This is not needed to prevent use by bflow since the entity_code is now 'MANUAL'
WHERE application_id = p_application_id
AND ae_header_id = l_array_ae_header_id(i);
trace(p_msg => 'Update xla_distribution_links',
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
UPDATE xla_distribution_links
SET event_id = x_new_event_id
WHERE application_id = p_application_id
AND ae_header_id = l_array_ae_header_id(i);
trace(p_msg => 'Update xla_events',
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
UPDATE XLA_EVENTS
SET EVENT_STATUS_CODE = xla_events_pub_pkg.C_EVENT_UNPROCESSED
,PROCESS_STATUS_CODE = xla_events_pkg.C_INTERNAL_UNPROCESSED
WHERE application_id = p_application_id
AND event_id = p_event_id;
trace(p_msg => 'Update descriptions',
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
UPDATE xla_ae_headers
SET description = 'Data fix reversal entry: event_id of '||x_new_event_id
WHERE application_id = p_application_id
AND event_id = x_rev_event_id
RETURNING ae_header_id BULK COLLECT INTO l_array_ae_header_id;
UPDATE xla_ae_lines
SET description = 'Data fix reversal entry: event_id of '||x_new_event_id
WHERE application_id = p_application_id
AND ae_header_id = l_array_ae_header_id(i);
SELECT DECODE(NVL(budgetary_control_flag,'N'),'Y', C_STATUS_FUNDS_RESERVE, C_STATUS_FINAL),process_status_code
INTO l_accounting_mode, l_process_status
FROM xla_events
WHERE application_id = p_application_id
AND event_id = p_event_id;
INSERT INTO xla_acct_prog_events_gt (event_id, ledger_id)
VALUES (p_event_id, null);
SELECT process_status_code
INTO l_process_status
FROM xla_events
WHERE application_id = p_application_id
AND event_id = p_event_id;
UPDATE xla_ae_headers
SET gl_transfer_status_code = 'NT'
WHERE application_id = p_application_id
AND ae_header_id = p_ae_header_id
AND accounting_entry_status_code = C_STATUS_FINAL_CODE
AND gl_transfer_status_code = 'N'; -- if already transferred, S or Y, then do not set to NT.
trace(p_msg => 'Rows updated = '||l_dummy,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
SELECT count(*)
INTO l_dummy
FROM xla_ae_headers
WHERE application_id = p_application_id
AND ae_header_id = p_ae_header_id
AND accounting_entry_status_code = C_STATUS_FINAL_CODE;
FOR i IN (SELECT error_message_name
FROM xla_upg_errors
WHERE application_id = p_application_id
AND ae_header_id = p_ae_header_id) LOOP
Log_error(p_error_name => i.ERROR_MESSAGE_NAME);
UPDATE XLA_AE_HEADERS
SET LAST_UPDATE_DATE = sysdate
,UPG_BATCH_ID = -9999
WHERE application_id = p_application_id
AND ae_header_id = p_ae_header_id;
UPDATE XLA_AE_LINES
SET LAST_UPDATE_DATE = sysdate
,UPG_BATCH_ID = -9999
WHERE application_id = p_application_id
AND ae_header_id = p_ae_header_id
AND ae_line_num = p_ae_line_num;
UPDATE XLA_EVENTS
SET LAST_UPDATE_DATE = sysdate
,UPG_BATCH_ID = -9999
WHERE application_id = p_application_id
AND event_id = p_event_id;
UPDATE XLA_AE_HEADERS
SET LAST_UPDATE_DATE = sysdate
,UPG_BATCH_ID = -9999
WHERE application_id = p_application_id
AND event_id = p_event_id
RETURNING ae_header_id BULK COLLECT INTO l_array_ae_header_id;
UPDATE XLA_AE_LINES
SET LAST_UPDATE_DATE = sysdate
,UPG_BATCH_ID = -9999
WHERE application_id = p_application_id
AND ae_header_id = l_array_ae_header_id(i);