[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
,p_last_update_date OUT NOCOPY DATE
,p_last_updated_by OUT NOCOPY INTEGER
,p_last_update_login OUT NOCOPY INTEGER
,p_transfer_request_id OUT NOCOPY INTEGER
,p_retcode OUT NOCOPY VARCHAR2
,p_msg_mode IN VARCHAR2 DEFAULT xla_exceptions_pkg.C_STANDARD_MESSAGE
,p_rev_flag IN VARCHAR2 DEFAULT 'N'
,p_rev_method IN VARCHAR2 DEFAULT 'N'
,p_rev_orig_event_id IN NUMBER DEFAULT -1);
PROCEDURE delete_mrc_entries
(p_event_id IN INTEGER
,p_application_id IN INTEGER
,p_ledger_id IN INTEGER);
PROCEDURE update_event_status
(p_info IN t_je_info
,p_completion_option IN VARCHAR2);
PROCEDURE update_segment_values
(p_ae_header_id IN INTEGER
,p_seg_type IN VARCHAR2
,p_seg_value IN VARCHAR2
,p_action IN VARCHAR2);
PROCEDURE validate_delete_mode
(p_status_code IN VARCHAR2
,p_mode IN VARCHAR2
,p_msg_mode IN VARCHAR2);
PROCEDURE delete_distribution_link
(p_application_id IN INTEGER
,p_ae_header_id IN INTEGER
,p_ref_ae_header_id IN INTEGER
,p_temp_line_num IN INTEGER);
PROCEDURE update_distribution_link
(p_application_id IN INTEGER
,p_ae_header_id IN INTEGER
,p_ref_ae_header_id IN INTEGER
,p_temp_line_num IN INTEGER
,p_unrounded_entered_dr IN NUMBER
,p_unrounded_entered_cr IN NUMBER
,p_unrounded_accounted_dr IN NUMBER
,p_undournde_accounted_cr IN NUMBER
,p_statistical_amount IN NUMBER);
C_GL_TRANSFER_MODE_SELECTED CONSTANT VARCHAR2(30) := 'S';
C_BALANCE_DELETE CONSTANT VARCHAR2(1) := 'D';
C_DELETE_FORCE_MODE CONSTANT VARCHAR2(1) := 'F';
,p_last_update_date OUT NOCOPY DATE
,p_last_updated_by OUT NOCOPY INTEGER
,p_last_update_login OUT NOCOPY INTEGER
,p_retcode OUT NOCOPY INTEGER
,p_msg_mode IN VARCHAR2 DEFAULT xla_exceptions_pkg.C_STANDARD_MESSAGE)
IS
CURSOR c IS
SELECT enable_budgetary_control_flag
FROM gl_ledgers
WHERE ledger_id = p_ledger_id;
,p_last_update_date => p_last_update_date
,p_last_updated_by => p_last_updated_by
,p_last_update_login => p_last_update_login
,p_retcode => p_retcode
,p_msg_mode => p_msg_mode);
,p_last_update_date OUT NOCOPY DATE
,p_last_updated_by OUT NOCOPY INTEGER
,p_last_update_login OUT NOCOPY INTEGER
,p_retcode OUT NOCOPY INTEGER
,p_msg_mode IN VARCHAR2 DEFAULT xla_exceptions_pkg.C_STANDARD_MESSAGE)
IS
l_event_source_info xla_events_pub_pkg.t_event_source_info;
SELECT entity_id
INTO l_entity_id
FROM xla_events
WHERE event_id = p_event_id;
INSERT INTO xla_ae_headers
(ae_header_id
,application_id
,ledger_id
,entity_id
,event_id
,event_type_code
,accounting_date
,reference_date
,balance_type_code
,budget_version_id
,gl_transfer_status_code
,je_category_name
,accounting_entry_status_code
,accounting_entry_type_code
,description
,period_name
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,accrual_reversal_flag -- 4262811
)
values
(xla_ae_headers_s.NEXTVAL
,p_application_id
,p_ledger_id
,l_entity_id
,p_event_id
,C_EVENT_TYPE_CODE_MANUAL
,p_gl_date
,p_reference_date
,p_balance_type_code
,l_budget_version_id
,C_GL_TRANSFER_MODE_NO
,p_je_category_name
,l_status_code
,p_accounting_entry_type_code
,p_description
,p_period_name
,p_attribute_category
,p_attribute1
,p_attribute2
,p_attribute3
,p_attribute4
,p_attribute5
,p_attribute6
,p_attribute7
,p_attribute8
,p_attribute9
,p_attribute10
,p_attribute11
,p_attribute12
,p_attribute13
,p_attribute14
,p_attribute15
,sysdate
,nvl(xla_environment_pkg.g_usr_id,-1)
,sysdate
,nvl(xla_environment_pkg.g_usr_id,-1)
,nvl(xla_environment_pkg.g_login_id,-1)
,'N') -- 4262811 accrual_reversal_flag
RETURNING ae_header_id
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
INTO p_ae_header_id
,p_creation_date
,p_created_by
,p_last_update_date
,p_last_updated_by
,p_last_update_login;
PROCEDURE update_journal_entry_header
(p_ae_header_id IN INTEGER
,p_application_id IN INTEGER
,p_legal_entity_id IN INTEGER DEFAULT NULL
,p_gl_date IN DATE
,p_accounting_entry_type_code IN VARCHAR2
,p_description IN VARCHAR2
,p_je_category_name IN VARCHAR2
,p_budget_version_id IN INTEGER DEFAULT NULL
,p_reference_date IN DATE DEFAULT NULL
,p_attribute_category IN VARCHAR2 DEFAULT NULL
,p_attribute1 IN VARCHAR2 DEFAULT NULL
,p_attribute2 IN VARCHAR2 DEFAULT NULL
,p_attribute3 IN VARCHAR2 DEFAULT NULL
,p_attribute4 IN VARCHAR2 DEFAULT NULL
,p_attribute5 IN VARCHAR2 DEFAULT NULL
,p_attribute6 IN VARCHAR2 DEFAULT NULL
,p_attribute7 IN VARCHAR2 DEFAULT NULL
,p_attribute8 IN VARCHAR2 DEFAULT NULL
,p_attribute9 IN VARCHAR2 DEFAULT NULL
,p_attribute10 IN VARCHAR2 DEFAULT NULL
,p_attribute11 IN VARCHAR2 DEFAULT NULL
,p_attribute12 IN VARCHAR2 DEFAULT NULL
,p_attribute13 IN VARCHAR2 DEFAULT NULL
,p_attribute14 IN VARCHAR2 DEFAULT NULL
,p_attribute15 IN VARCHAR2 DEFAULT NULL
,p_period_name OUT NOCOPY VARCHAR2
,p_last_update_date OUT NOCOPY DATE
,p_last_updated_by OUT NOCOPY INTEGER
,p_last_update_login OUT NOCOPY INTEGER
,p_retcode OUT NOCOPY INTEGER
,p_msg_mode IN VARCHAR2 DEFAULT xla_exceptions_pkg.C_STANDARD_MESSAGE)
IS
l_info t_je_info;
l_log_module := C_DEFAULT_MODULE||'.update_journal_entry_header';
trace(p_msg => 'BEGIN of procedure update_journal_entry_header',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
UPDATE xla_transaction_entities
SET legal_entity_id = p_legal_entity_id
WHERE application_id = p_application_id -- 4928660
AND entity_id = l_info.entity_id;
UPDATE xla_events
SET event_date = p_gl_date
WHERE application_id = p_application_id
AND event_id = l_info.event_id;
UPDATE xla_ae_lines
SET accounting_date = p_gl_date
WHERE application_id = p_application_id
AND ae_header_id = p_ae_header_id;
UPDATE xla_ae_headers
SET reference_date = p_reference_date
,budget_version_id = l_budget_version_id
,accounting_entry_type_code = p_accounting_entry_type_code
,accounting_entry_status_code = l_status_code
,accounting_date = p_gl_date
,period_name = p_period_name
,je_category_name = p_je_category_name
,description = p_description
,last_update_date = sysdate
,last_updated_by = nvl(xla_environment_pkg.g_usr_id,-1)
,last_update_login = nvl(xla_environment_pkg.g_login_id,-1)
,attribute_category = p_attribute_category
,attribute1 = p_attribute1
,attribute2 = p_attribute2
,attribute3 = p_attribute3
,attribute4 = p_attribute4
,attribute5 = p_attribute5
,attribute6 = p_attribute6
,attribute7 = p_attribute7
,attribute8 = p_attribute8
,attribute9 = p_attribute9
,attribute10 = p_attribute10
,attribute11 = p_attribute11
,attribute12 = p_attribute12
,attribute13 = p_attribute13
,attribute14 = p_attribute14
,attribute15 = p_attribute15
WHERE ae_header_id = p_ae_header_id
AND application_id = p_application_id
RETURNING last_update_date
,last_updated_by
,last_update_login
INTO p_last_update_date
,p_last_updated_by
,p_last_update_login;
trace(p_msg => 'END of procedure update_journal_entry_header',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
(p_location => 'xla_journal_entries_pkg.update_journal_entry_header');
END update_journal_entry_header;
PROCEDURE delete_journal_entry
(p_ae_header_id IN INTEGER
,p_application_id IN INTEGER
,p_mode IN VARCHAR2 DEFAULT C_DELETE_NORMAL_MODE
,p_msg_mode IN VARCHAR2 DEFAULT xla_exceptions_pkg.C_STANDARD_MESSAGE)
IS
l_event_source_info xla_events_pub_pkg.t_event_source_info;
l_log_module := C_DEFAULT_MODULE||'.delete_journal_entry';
trace(p_msg => 'BEGIN of procedure delete_journal_entry',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
validate_delete_mode(l_info.status_code, p_mode, p_msg_mode);
XLA_EVENTS_PKG.delete_processed_event
(p_event_source_info => l_event_source_info
,p_event_id => l_info.event_id);
XLA_EVENTS_PKG.delete_event
(p_event_source_info => l_event_source_info
,p_valuation_method => NULL
,p_event_id => l_info.event_id);
trace(p_msg => 'END of procedure delete_journal_entry',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
(p_location => 'xla_journal_entries_pkg.update_journal_entry_header');
END delete_journal_entry;
PROCEDURE delete_journal_entries
(p_event_id IN INTEGER
,p_application_id IN INTEGER)
IS
CURSOR c_entries IS
SELECT h.ae_header_id ae_header_id
,e.ledger_id ledger_id
,h.accounting_entry_status_code status_code
,h.accounting_entry_type_code type_code
,h.funds_status_code funds_status_code
,h.packet_id packet_id
,h.entity_id entity_id
FROM xla_ae_headers h
,xla_transaction_entities e
WHERE e.application_id = p_application_id
AND e.entity_id = h.entity_id
AND h.application_id = p_application_id
AND h.event_id = p_event_id
FOR UPDATE NOWAIT;
SELECT 'exist'
FROM xla_ae_headers h
,gl_period_statuses p
WHERE p.application_id(+) = C_GL_APPLICATION_ID
AND p.ledger_id(+) = h.ledger_id
AND p.adjustment_period_flag(+) = 'N'
AND p.period_name(+) = h.period_name
AND nvl(p.closing_status, 'C') NOT in ('0', 'F')
AND h.accounting_entry_status_code = C_AE_STATUS_FINAL
AND h.application_id = p_application_id
AND h.event_id = p_event_id;
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);
IF (NOT xla_balances_pkg.massive_update
(p_application_id => p_application_id
,p_ledger_id => NULL
,p_entity_id => l_entry.entity_id
,p_event_id => NULL
,p_request_id => NULL
,p_accounting_batch_id => NULL
,p_update_mode => C_BALANCE_DELETE
,p_execution_mode => C_BALANCE_ONLINE)) THEN
xla_exceptions_pkg.raise_message
(p_appli_s_name => 'XLA'
,p_msg_name => 'XLA_INTERNAL_ERROR'
,p_token_1 => 'MESSAGE'
,p_value_1 => 'Error in balance calculation'
,p_token_2 => 'LOCATION'
,p_value_2 => 'XLA_JOURNAL_ENTRIES_PKG.delete_journal_entries'
,p_msg_mode => xla_exceptions_pkg.C_STANDARD_MESSAGE);
DELETE FROM xla_accounting_errors
WHERE event_id = p_event_id;
DELETE FROM xla_distribution_links
WHERE application_id = p_application_id
AND ae_header_id IN (SELECT ae_header_id
FROM xla_ae_headers
WHERE application_id = p_application_id
AND event_id = p_event_id);
DELETE FROM xla_ae_segment_values
WHERE ae_header_id = l_ae_header_ids(k);
IF (NOT xla_analytical_criteria_pkg.single_update_detail_value
(p_application_id => p_application_id
,p_ae_header_id => l_ae_header_ids(k)
,p_ae_line_num => NULL
,p_anacri_code => NULL
,p_anacri_type_code => NULL
,p_amb_context_code => NULL
,p_update_mode => 'D')) THEN
ROLLBACK to SAVEPOINT DELETE_JOURNAL_ENTRIES;
,p_value_1 => 'Error in xla_analytical_criteria_pkg.single_update_detail_value'
,p_token_2 => 'LOCATION'
,p_value_2 => 'XLA_JOURNAL_ENTRIES_PKG.delete_journal_entries'
,p_msg_mode => xla_exceptions_pkg.C_STANDARD_MESSAGE);
DELETE FROM xla_ae_line_acs
WHERE ae_header_id = l_ae_header_ids(k);
DELETE FROM xla_ae_header_acs
WHERE ae_header_id = l_ae_header_ids(k);
DELETE FROM xla_ae_lines
WHERE application_id = p_application_id
AND ae_header_id = l_ae_header_ids(k);
DELETE xla_ae_headers
WHERE application_id = p_application_id
AND ae_header_id = l_ae_header_ids(k);
trace(p_msg => 'END of procedure delete_journal_entries',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
(p_location => 'xla_journal_entries_pkg.delete_journal_entries');
END delete_journal_entries;
,p_last_update_date OUT NOCOPY DATE
,p_last_updated_by OUT NOCOPY INTEGER
,p_last_update_login OUT NOCOPY INTEGER
,p_retcode OUT NOCOPY INTEGER
,p_msg_mode IN VARCHAR2 DEFAULT xla_exceptions_pkg.C_STANDARD_MESSAGE)
IS
l_info t_je_info;
CURSOR c IS SELECT max(ae_line_num)+1
FROM xla_ae_lines
WHERE application_id = p_application_id
AND ae_header_id = p_ae_header_id;
SELECT enabled_flag, encumbrance_type
FROM gl_encumbrance_types e
WHERE e.encumbrance_type_id = l_encumbrance_type_id;
INSERT INTO xla_ae_lines
(ae_header_id
,displayed_line_number
,ae_line_num
,code_combination_id
,gl_transfer_mode_code
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,party_type_code
,party_id
,party_site_id
,entered_dr
,entered_cr
,accounted_dr
,accounted_cr
,unrounded_accounted_dr
,unrounded_accounted_cr
,unrounded_entered_dr
,unrounded_entered_cr
,accounting_class_code
,description
,statistical_amount
,currency_code
,currency_conversion_type
,currency_conversion_date
,currency_conversion_rate
,jgzz_recon_ref
,control_balance_flag
,analytical_balance_flag
,gl_sl_link_table
,attribute_category
,encumbrance_type_id
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,application_id
,gain_or_loss_flag
,ledger_id
,accounting_date
,mpa_accrual_entry_flag) -- 4262811
values
(p_ae_header_id
,p_displayed_line_number
,p_ae_line_num
,p_code_combination_id
,l_gl_transfer_mode
,sysdate
,nvl(xla_environment_pkg.g_usr_id,-1)
,sysdate
,nvl(xla_environment_pkg.g_usr_id,-1)
,nvl(xla_environment_pkg.g_login_id,-1)
,p_party_type_code
,p_party_id
,p_party_site_id
,p_entered_dr
,p_entered_cr
,p_accounted_dr
,p_accounted_cr
,l_unrounded_accted_dr
,l_unrounded_accted_cr
,l_unrounded_entered_dr
,l_unrounded_entered_cr
,p_accounting_class_code
,p_description
,p_statistical_amount
,p_currency_code
,p_conversion_type
,p_conversion_date
,p_conversion_rate
,p_jgzz_recon_ref
,NULL
,NULL
,'XLAJEL'
,p_attribute_category
,l_encumbrance_type_id
,p_attribute1
,p_attribute2
,p_attribute3
,p_attribute4
,p_attribute5
,p_attribute6
,p_attribute7
,p_attribute8
,p_attribute9
,p_attribute10
,p_attribute11
,p_attribute12
,p_attribute13
,p_attribute14
,p_attribute15
,l_info.application_id
,'N'
,l_info.ledger_id
,l_info.gl_date
,'N') -- 4262811 mpa_accrual_entry_flag
RETURNING creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
INTO p_creation_date
,p_created_by
,p_last_update_date
,p_last_updated_by
,p_last_update_login;
trace(p_msg => '# row inserted = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
update_segment_values(p_ae_header_id, C_SEG_BALANCING, l_bal_seg, C_ACTION_ADD);
update_segment_values(p_ae_header_id, C_SEG_MANAGEMENT, l_mgt_seg, C_ACTION_ADD);
update xla_ae_headers
set accounting_entry_status_code = l_status_code
WHERE ae_header_id = p_ae_header_id
AND application_id = p_application_id;
PROCEDURE update_journal_entry_line
(p_ae_header_id IN INTEGER
,p_ae_line_num IN INTEGER
,p_displayed_line_number IN INTEGER
,p_application_id IN INTEGER
,p_code_combination_id IN INTEGER
,p_gl_transfer_mode IN VARCHAR2
,p_accounting_class_code IN VARCHAR2
,p_entered_dr IN OUT NOCOPY NUMBER
,p_entered_cr IN OUT NOCOPY NUMBER
,p_currency_code IN OUT NOCOPY VARCHAR2
,p_accounted_dr IN OUT NOCOPY NUMBER
,p_accounted_cr IN OUT NOCOPY NUMBER
,p_conversion_type IN OUT NOCOPY VARCHAR2
,p_conversion_date IN OUT NOCOPY DATE
,p_conversion_rate IN OUT NOCOPY NUMBER
,p_party_type_code IN VARCHAR2 DEFAULT NULL
,p_party_id IN INTEGER DEFAULT NULL
,p_party_site_id IN INTEGER DEFAULT NULL
,p_description IN VARCHAR2 DEFAULT NULL
,p_statistical_amount IN NUMBER DEFAULT NULL
,p_jgzz_recon_ref IN VARCHAR2 DEFAULT NULL
,p_attribute_category IN VARCHAR2 DEFAULT NULL
,p_encumbrance_type_id IN INTEGER DEFAULT NULL
,p_attribute1 IN VARCHAR2 DEFAULT NULL
,p_attribute2 IN VARCHAR2 DEFAULT NULL
,p_attribute3 IN VARCHAR2 DEFAULT NULL
,p_attribute4 IN VARCHAR2 DEFAULT NULL
,p_attribute5 IN VARCHAR2 DEFAULT NULL
,p_attribute6 IN VARCHAR2 DEFAULT NULL
,p_attribute7 IN VARCHAR2 DEFAULT NULL
,p_attribute8 IN VARCHAR2 DEFAULT NULL
,p_attribute9 IN VARCHAR2 DEFAULT NULL
,p_attribute10 IN VARCHAR2 DEFAULT NULL
,p_attribute11 IN VARCHAR2 DEFAULT NULL
,p_attribute12 IN VARCHAR2 DEFAULT NULL
,p_attribute13 IN VARCHAR2 DEFAULT NULL
,p_attribute14 IN VARCHAR2 DEFAULT NULL
,p_attribute15 IN VARCHAR2 DEFAULT NULL
,p_last_update_date OUT NOCOPY DATE
,p_last_updated_by OUT NOCOPY INTEGER
,p_last_update_login OUT NOCOPY INTEGER
,p_retcode OUT NOCOPY INTEGER
,p_msg_mode IN VARCHAR2 DEFAULT xla_exceptions_pkg.C_STANDARD_MESSAGE)
IS
l_info t_je_info;
SELECT code_combination_id
FROM xla_ae_lines
WHERE application_id = p_application_id
AND ae_header_id = p_ae_header_id
AND ae_line_num = p_ae_line_num;
l_log_module := C_DEFAULT_MODULE||'.update_journal_entry_line';
trace(p_msg => 'BEGIN of procedure update_journal_entry_line',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
update_distribution_link
(p_application_id => p_application_id
,p_ae_header_id => p_ae_header_id
,p_ref_ae_header_id => l_ref_ae_header_id
,p_temp_line_num => l_temp_line_num
,p_unrounded_entered_dr => l_unrounded_entered_dr
,p_unrounded_entered_cr => l_unrounded_entered_cr
,p_unrounded_accounted_dr => l_unrounded_accted_dr
,p_undournde_accounted_cr => l_unrounded_accted_cr
,p_statistical_amount => p_statistical_amount);
UPDATE xla_ae_lines
SET code_combination_id = p_code_combination_id
,displayed_line_number = p_displayed_line_number
,gl_transfer_mode_code = l_gl_transfer_mode
,party_id = p_party_id
,party_site_id = p_party_site_id
,party_type_code = p_party_type_code
,entered_dr = p_entered_dr
,entered_cr = p_entered_cr
,unrounded_entered_dr = l_unrounded_entered_dr
,unrounded_entered_cr = l_unrounded_entered_cr
,accounted_dr = p_accounted_dr
,accounted_cr = p_accounted_cr
,unrounded_accounted_dr = l_unrounded_accted_dr
,unrounded_accounted_cr = l_unrounded_accted_cr
,description = p_description
,accounting_class_code = p_accounting_class_code
,statistical_amount = p_statistical_amount
,currency_code = p_currency_code
,currency_conversion_type = p_conversion_type
,currency_conversion_date = p_conversion_date
,currency_conversion_rate = p_conversion_rate
,jgzz_recon_ref = p_jgzz_recon_ref
,attribute_category = p_attribute_category
,encumbrance_type_id = l_encumbrance_type_id
,attribute1 = p_attribute1
,attribute2 = p_attribute2
,attribute3 = p_attribute3
,attribute4 = p_attribute4
,attribute5 = p_attribute5
,attribute6 = p_attribute6
,attribute7 = p_attribute7
,attribute8 = p_attribute8
,attribute9 = p_attribute9
,attribute10 = p_attribute10
,attribute11 = p_attribute11
,attribute12 = p_attribute12
,attribute13 = p_attribute13
,attribute14 = p_attribute14
,attribute15 = p_attribute15
,ledger_id = l_info.ledger_id
,accounting_date = l_info.gl_date
,last_update_date = sysdate
,last_updated_by = nvl(xla_environment_pkg.g_usr_id,-1)
,last_update_login = nvl(xla_environment_pkg.g_login_id,-1)
WHERE ae_header_id = p_ae_header_id
AND ae_line_num = p_ae_line_num
AND application_id = p_application_id
RETURNING last_update_date
,last_updated_by
,last_update_login
INTO p_last_update_date
,p_last_updated_by
,p_last_update_login;
update_segment_values(p_ae_header_id, C_SEG_BALANCING, l_bal_seg_old, C_ACTION_DEL);
update_segment_values(p_ae_header_id, C_SEG_BALANCING, l_bal_seg_old, C_ACTION_DEL);
update_segment_values(p_ae_header_id, C_SEG_BALANCING, l_bal_seg, C_ACTION_ADD);
update_segment_values(p_ae_header_id, C_SEG_MANAGEMENT, l_mgt_seg_old, C_ACTION_DEL);
update_segment_values(p_ae_header_id, C_SEG_MANAGEMENT, l_mgt_seg_old, C_ACTION_DEL);
update_segment_values(p_ae_header_id, C_SEG_MANAGEMENT, l_mgt_seg, C_ACTION_ADD);
UPDATE xla_ae_headers
set accounting_entry_status_code = l_status_code
WHERE ae_header_id = p_ae_header_id
AND application_id = l_info.application_id;
trace(p_msg => 'END of procedure update_journal_entry_line',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
(p_location => 'xla_journal_entries_pkg.update_journal_entry_line');
END update_journal_entry_line;
PROCEDURE delete_journal_entry_line
(p_ae_header_id IN INTEGER
,p_ae_line_num IN INTEGER
,p_application_id IN INTEGER
,p_msg_mode IN VARCHAR2 DEFAULT xla_exceptions_pkg.C_STANDARD_MESSAGE)
IS
l_info t_je_info;
l_log_module := C_DEFAULT_MODULE||'.delete_journal_entry_line';
trace(p_msg => 'BEGIN of procedure delete_journal_entry_line',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
UPDATE xla_ae_headers
set accounting_entry_status_code = l_status_code
WHERE ae_header_id = p_ae_header_id
AND application_id = p_application_id;
IF (NOT xla_analytical_criteria_pkg.single_update_detail_value
(p_application_id => p_application_id
,p_ae_header_id => p_ae_header_id
,p_ae_line_num => p_ae_line_num
,p_analytical_detail_value_id => NULL
,p_anacri_code => NULL
,p_anacri_type_code => NULL
,p_amb_context_code => NULL
,p_update_mode => 'D')) THEN
ROLLBACK to SAVEPOINT DELETE_JOURNAL_ENTRY;
,p_value_1 => 'Error in xla_analytical_criteria_pkg.single_update_detail_value'
,p_token_2 => 'LOCATION'
,p_value_2 => 'XLA_JOURNAL_ENTRIES_PKG.delete_journal_entry'
,p_msg_mode => p_msg_mode);
delete_distribution_link
(p_application_id => p_application_id
,p_ae_header_id => p_ae_header_id
,p_ref_ae_header_id => p_ae_header_id
,p_temp_line_num => p_ae_line_num);
DELETE xla_ae_lines
WHERE ae_header_id = p_ae_header_id
AND ae_line_num = p_ae_line_num
AND application_id = p_application_id;
update_segment_values(p_ae_header_id, C_SEG_BALANCING, l_bal_seg, C_ACTION_DEL);
update_segment_values(p_ae_header_id, C_SEG_MANAGEMENT, l_mgt_seg, C_ACTION_DEL);
trace(p_msg => 'END of procedure delete_journal_entry_line',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
(p_location => 'xla_journal_entries_pkg.delete_journal_entry_line');
END delete_journal_entry_line;
,p_last_update_date OUT NOCOPY DATE
,p_last_updated_by OUT NOCOPY INTEGER
,p_last_update_login OUT NOCOPY INTEGER
,p_transfer_request_id OUT NOCOPY INTEGER
,p_retcode OUT NOCOPY VARCHAR2
,p_msg_mode IN VARCHAR2 DEFAULT xla_exceptions_pkg.C_STANDARD_MESSAGE)
IS
l_log_module VARCHAR2(240);
,p_last_update_date => p_last_update_date
,p_last_updated_by => p_last_updated_by
,p_last_update_login => p_last_update_login
,p_transfer_request_id => p_transfer_request_id
,p_retcode => p_retcode
,p_rev_flag => 'N'
,p_rev_method => 'N'
,p_rev_orig_event_id => -1
,p_msg_mode => p_msg_mode);
,p_last_update_date OUT NOCOPY DATE
,p_last_updated_by OUT NOCOPY INTEGER
,p_last_update_login OUT NOCOPY INTEGER
,p_transfer_request_id OUT NOCOPY INTEGER
,p_retcode OUT NOCOPY VARCHAR2
,p_msg_mode IN VARCHAR2 DEFAULT xla_exceptions_pkg.C_STANDARD_MESSAGE
,p_rev_flag IN VARCHAR2 DEFAULT 'N'
,p_rev_method IN VARCHAR2 DEFAULT 'N'
,p_rev_orig_event_id IN NUMBER DEFAULT -1)
IS
l_info t_je_info;
l_bal_update_mode VARCHAR2(30) := NULL;
DELETE FROM xla_ae_lines
WHERE application_id = p_application_id
AND ae_header_id = p_ae_header_id
AND accounting_class_code IN ('ROUNDING', 'BALANCE', 'INTRA', 'INTER');
trace(p_msg => '# balancing line deleted = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
delete_mrc_entries
(p_event_id => l_info.event_id
,p_application_id => l_info.application_id
,p_ledger_id => l_info.ledger_id);
DELETE FROM xla_accounting_errors WHERE event_id = l_info.event_id;
xla_accounting_err_pkg.insert_errors;
p_last_update_date := sysdate;
p_last_updated_by := nvl(xla_environment_pkg.g_usr_id,-1);
p_last_update_login := nvl(xla_environment_pkg.g_login_id,-1);
p_gl_transfer_status_code := C_GL_TRANSFER_MODE_SELECTED;
trace(p_msg => 'Update header entry headers',
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
UPDATE xla_ae_headers
SET accounting_entry_status_code = l_status_codes(i)
,funds_status_code = p_funds_status_code
,completion_acct_seq_value = DECODE(l_seq_values(i),-1,NULL,l_seq_values(i))
,completion_acct_seq_version_id = DECODE(l_seq_version_ids(i),-1,NULL,l_seq_version_ids(i))
,completion_acct_seq_assign_id = DECODE(l_seq_assign_ids(i),-1,NULL,l_seq_assign_ids(i))
,completed_date = p_completed_date
,packet_id = l_info.packet_id
,gl_transfer_status_code = p_gl_transfer_status_code
,last_update_date = p_last_update_date
,last_updated_by = p_last_updated_by
,last_update_login = p_last_update_login
WHERE ae_header_id = l_ae_header_ids(i)
AND application_id = p_application_id;
trace(p_msg => 'p_completion_option = FINAL - Update gl_sl_link_id',
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
UPDATE xla_ae_lines
SET gl_sl_link_id = XLA_GL_SL_LINK_ID_S.nextval
,last_update_date = p_last_update_date
,last_updated_by = p_last_updated_by
,last_update_login = p_last_update_login
WHERE ae_header_id = l_ae_header_ids(i)
AND application_id = p_application_id
AND gl_sl_link_id IS NULL;
l_bal_update_mode := C_BALANCE_DELETE;
l_bal_update_mode := C_BALANCE_D_TO_F;
l_bal_update_mode := C_BALANCE_ADD;
l_bal_update_mode := NULL;
trace(p_msg => 'l_bal_update_mode:'||l_bal_update_mode,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
IF (l_bal_update_mode IS NOT NULL) THEN
--FORALL i IN 1 .. l_ae_header_ids.COUNT
UPDATE xla_ae_lines l
SET control_balance_flag =
(SELECT DECODE(l.accounting_class_code,
'INTER', NULL,
'INTRA', NULL,
DECODE(NVL(ccid.reference3,'N'),'N',NULL,
DECODE(ccid.account_type, 'A', 'P'
, 'L', 'P'
, 'O', 'P'
, NULL)))
FROM gl_code_combinations ccid
WHERE ccid.code_combination_id = l.code_combination_id)
,analytical_balance_flag =
(SELECT DECODE(count(1),0,NULL,'P')
FROM xla_ae_line_acs ac
WHERE ac.ae_header_id(+) = l.ae_header_id
AND ac.ae_line_num(+) = l.ae_line_num)
,last_update_date = p_last_update_date
,last_updated_by = p_last_updated_by
,last_update_login = p_last_update_login
WHERE l.application_id = p_application_id
AND l.ae_header_id = p_ae_header_id;
IF (NOT xla_balances_pkg.massive_update
(p_application_id => l_info.application_id
,p_ledger_id => NULL
,p_entity_id => l_info.entity_id
,p_event_id => NULL
,p_request_id => NULL
,p_accounting_batch_id => NULL
,p_update_mode => l_bal_update_mode
,p_execution_mode => C_BALANCE_ONLINE)) THEN
xla_exceptions_pkg.raise_message
(p_appli_s_name => 'XLA'
,p_msg_name => 'XLA_INTERNAL_ERROR'
,p_token_1 => 'MESSAGE'
,p_value_1 => 'Error in balance calculation'
,p_token_2 => 'LOCATION'
,p_value_2 => 'XLA_JOURNAL_ENTRIES_PKG.delete_journal_entries'
,p_msg_mode => xla_exceptions_pkg.C_STANDARD_MESSAGE);
/* Bug 7011889 - Call the update event status only if Reversal event id is not NULL and if it
is not already updated */
IF g_rev_event_id IS NOT NULL THEN
SELECT event_status_code
INTO l_status_code
FROM xla_events
WHERE event_id = g_rev_event_id
AND application_id = p_application_id;
update_event_status
(p_info => l_info
,p_completion_option => p_completion_option);
l_last_update_date DATE;
l_last_updated_by INTEGER;
l_last_update_login INTEGER;
/* Bug 7011889 - Looping through for all header ids selected for an event for reversal */
FOR i in p_array_je_header_id.FIRST..p_array_je_header_id.LAST
LOOP
l_info := get_header_info(p_array_je_header_id(i),p_application_id, p_msg_mode);
,p_last_update_date => l_last_update_date
,p_last_updated_by => l_last_updated_by
,p_last_update_login => l_last_update_login
,p_transfer_request_id => p_transfer_request_id
,p_retcode => p_completion_retcode
,p_rev_flag => 'Y'
,p_rev_method => p_reversal_method
,p_rev_orig_event_id => l_info.event_id
,p_msg_mode => p_msg_mode);
/* Bug 7011889 - End of loop for all header ids selected for an event for reversal */
IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
trace(p_msg => 'END of procedure reverse_journal_entry',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
l_last_update_date DATE;
l_last_updated_by INTEGER;
l_last_update_login INTEGER;
,p_last_update_date => l_last_update_date
,p_last_updated_by => l_last_updated_by
,p_last_update_login => l_last_update_login
,p_transfer_request_id => p_transfer_request_id
,p_retcode => p_completion_retcode
,p_rev_flag => 'Y'
,p_rev_method => p_reversal_method
,p_rev_orig_event_id => l_info.event_id
,p_msg_mode => p_msg_mode);
SELECT xah.ae_header_id
,xah.ledger_id
,xte.legal_entity_id
,xah.application_id
,xah.entity_id
,xah.event_id
,xah.accounting_date
,xah.accounting_entry_status_code
,xah.accounting_entry_type_code
,xah.description
,xah.balance_type_code
,xah.budget_version_id
,xah.reference_date
,xah.funds_status_code
,xah.je_category_name
,xah.packet_id
,xah.amb_context_code
,xah.event_type_code
,xah.completed_date
,xah.gl_transfer_status_code
,xah.accounting_batch_id
,xah.period_name
,xah.product_rule_code
,xah.product_rule_type_code
,xah.product_rule_version
,xah.gl_transfer_date
,xah.doc_sequence_id
,xah.doc_sequence_value
,xah.close_acct_seq_version_id
,xah.close_acct_seq_value
,xah.close_acct_seq_assign_id
,xah.completion_acct_seq_version_id
,xah.completion_acct_seq_value
,xah.completion_acct_seq_assign_id
,NVL(xah.accrual_reversal_flag,'N') -- 4262811
,xe.budgetary_control_flag
,xah.attribute_category
,xah.attribute1
,xah.attribute2
,xah.attribute3
,xah.attribute4
,xah.attribute5
,xah.attribute6
,xah.attribute7
,xah.attribute8
,xah.attribute9
,xah.attribute10
,xah.attribute11
,xah.attribute12
,xah.attribute13
,xah.attribute14
,xah.attribute15
FROM xla_ae_headers xah
,xla_events xe
,xla_transaction_entities xte
WHERE xte.entity_id = xah.entity_id
AND xte.application_id = xah.application_id
AND xe.event_id = xah.event_id
AND xe.application_id = xah.application_id
AND xah.ae_header_id = p_ae_header_id
AND xah.application_id = p_application_id
FOR UPDATE NOWAIT;
SELECT code_combination_id
FROM gl_code_combinations
WHERE code_combination_id = p_code_combination_id;
SELECT ae_line_num
FROM xla_ae_lines
WHERE ae_header_id = p_header_id
AND ae_line_num = p_line_num
AND application_id = p_application_id;
SELECT ae_line_num
FROM xla_ae_lines
WHERE ae_header_id = p_header_id
AND ae_line_num <> nvl(p_line_num,-1)
AND displayed_line_number = p_display_line_num
AND application_id = p_application_id;
SELECT ae_line_num, accounting_class_code, gl_transfer_mode_code
FROM xla_ae_lines
WHERE ae_header_id = p_ae_header_id
AND application_id = p_application_id;
SELECT ledger_id, nvl(enable_budgetary_control_flag, 'N')
FROM gl_ledgers
WHERE ledger_id = p_ledger_id;
IF (NOT xla_balances_pkg.massive_update
(p_application_id => p_info.application_id
,p_ledger_id => NULL
,p_event_id => NULL
,p_entity_id => p_info.entity_id
,p_request_id => NULL
,p_accounting_batch_id => NULL
,p_update_mode => C_BALANCE_DELETE
,p_execution_mode => C_BALANCE_ONLINE)) THEN
xla_exceptions_pkg.raise_message
(p_appli_s_name => 'XLA'
,p_msg_name => 'XLA_INTERNAL_ERROR'
,p_token_1 => 'MESSAGE'
,p_value_1 => 'Error in balance calculation'
,p_token_2 => 'LOCATION'
,p_value_2 => 'XLA_JOURNAL_ENTRIES_PKG.update_journal_entry_header'
,p_msg_mode => xla_exceptions_pkg.C_STANDARD_MESSAGE);
delete_mrc_entries(p_info.event_id, p_info.application_id, p_info.ledger_id);
xla_events_pkg.update_manual_event
(p_event_source_info => l_event_source_info
,p_event_id => p_info.event_id
,p_event_status_code => xla_events_pub_pkg.C_EVENT_UNPROCESSED
,p_process_status_code => xla_events_pkg.C_INTERNAL_UNPROCESSED);
l_last_updated_by INTEGER;
l_last_update_login INTEGER;
SELECT xah.*
FROM xla_ae_headers xah
, xla_alt_curr_ledgers_v l
WHERE xah.application_id = p_info.application_id
AND xah.event_id = p_orig_event_id
AND xah.ledger_id = l.ledger_id
AND l.primary_ledger_id = p_info.ledger_id
AND l.enabled_flag = 'Y';
SELECT xah.*
FROM xla_ae_headers xah
, xla_ledger_relationships_v l
WHERE xah.application_id = p_info.application_id
AND xah.event_id = p_orig_event_id
AND xah.ledger_id = l.ledger_id
AND l.primary_ledger_id = p_info.ledger_id
AND (l.LEDGER_CATEGORY_CODE IN ('ALC','SECONDARY')
OR (l.LEDGER_CATEGORY_CODE= 'PRIMARY' AND xah.parent_ae_header_id IS NOT NULL));
l_last_updated_by := nvl(xla_environment_pkg.g_usr_id,-1);
l_last_update_login := nvl(xla_environment_pkg.g_login_id,-1);
INSERT INTO xla_ae_headers
(ae_header_id
,application_id
,ledger_id
,entity_id
,event_id
,event_type_code
,accounting_date
,period_name
,reference_date
,balance_type_code
,budget_version_id
,encumbrance_type_id
,gl_transfer_status_code
,je_category_name
,accounting_entry_status_code
,accounting_entry_type_code
,description
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,accrual_reversal_flag) -- 5109240
values
(xla_ae_headers_s.NEXTVAL
,l_mrc_header.application_id
,l_mrc_header.ledger_id
,p_info.entity_id
,p_info.event_id
,C_EVENT_TYPE_CODE_MANUAL
,p_info.gl_date
,p_info.period_name
,p_info.reference_date
,l_mrc_header.balance_type_code
,l_mrc_header.budget_version_id
,l_mrc_header.encumbrance_type_id
,C_GL_TRANSFER_MODE_NO
,l_mrc_header.je_category_name
,C_AE_STATUS_INCOMPLETE
,p_info.type_code
,l_reversal_label||': '||l_mrc_header.description
,sysdate
,l_last_updated_by
,sysdate
,l_last_updated_by
,l_last_update_login
,NVL(l_mrc_header.accrual_reversal_flag,'N')) -- 5109240
RETURNING ae_header_id INTO p_rev_ae_header_ids(i);
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 p_rev_ae_header_ids(i)
,analytical_criterion_code
,analytical_criterion_type_code
,amb_context_code
,ac1
,ac2
,ac3
,ac4
,ac5
,1
FROM xla_ae_header_acs
WHERE ae_header_id = l_mrc_header.ae_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
,unrounded_entered_dr
,unrounded_entered_cr
,entered_dr
,entered_cr
,unrounded_accounted_dr
,unrounded_accounted_cr
,accounted_dr
,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_table
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,ledger_id
,accounting_date
,gain_or_loss_flag
,mpa_accrual_entry_flag) -- 4262811
SELECT
application_id
,p_rev_ae_header_ids(i)
,ae_line_num
,displayed_line_number
,code_combination_id
,gl_transfer_mode_code
,sysdate
,l_last_updated_by
,sysdate
,l_last_updated_by
,l_last_update_login
,party_id
,party_site_id
,party_type_code
,DECODE(p_reversal_method, C_REVERSAL_SWITCH_DR_CR,
unrounded_entered_cr, -unrounded_entered_dr)
,DECODE(p_reversal_method, C_REVERSAL_SWITCH_DR_CR,
unrounded_entered_dr, -unrounded_entered_cr)
,DECODE(p_reversal_method, C_REVERSAL_SWITCH_DR_CR,
entered_cr, -entered_dr)
,DECODE(p_reversal_method, C_REVERSAL_SWITCH_DR_CR,
entered_dr, -entered_cr)
,DECODE(p_reversal_method, C_REVERSAL_SWITCH_DR_CR,
unrounded_accounted_cr, -unrounded_accounted_dr)
,DECODE(p_reversal_method, C_REVERSAL_SWITCH_DR_CR,
unrounded_accounted_dr, -unrounded_accounted_cr)
,DECODE(p_reversal_method, C_REVERSAL_SWITCH_DR_CR,
accounted_cr, -accounted_dr)
,DECODE(p_reversal_method, C_REVERSAL_SWITCH_DR_CR,
accounted_dr, -accounted_cr)
,description
,statistical_amount
,currency_code
,currency_conversion_type
,currency_conversion_date
,currency_conversion_rate
,accounting_class_code
,jgzz_recon_ref
,'XLAJEL'
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,l_mrc_header.ledger_id
,p_info.gl_date
,gain_or_loss_flag
,NVL(mpa_accrual_entry_flag,'N')
FROM xla_ae_lines
WHERE application_id = p_info.application_id
AND ae_header_id = l_mrc_header.ae_header_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 p_rev_ae_header_ids(i)
,ae_line_num
,analytical_criterion_code
,analytical_criterion_type_code
,amb_context_code
,ac1
,ac2
,ac3
,ac4
,ac5
,1
FROM xla_ae_line_acs
WHERE ae_header_id = l_mrc_header.ae_header_id;
l_last_updated_by INTEGER;
l_last_update_login INTEGER;
SELECT entity_id
INTO g_entity_id
FROM xla_events
WHERE event_id = p_rev_event_id;
l_last_updated_by := nvl(xla_environment_pkg.g_usr_id,-1);
l_last_update_login := nvl(xla_environment_pkg.g_login_id,-1);
INSERT INTO xla_ae_headers
(ae_header_id
,application_id
,ledger_id
,entity_id
,event_id
,event_type_code
,accounting_date
,period_name
,reference_date
,balance_type_code
,budget_version_id
,gl_transfer_status_code
,je_category_name
,accounting_entry_status_code
,accounting_entry_type_code
,description
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,accrual_reversal_flag) -- 4262811
values
(xla_ae_headers_s.NEXTVAL
,p_info.application_id
,p_info.ledger_id
,l_entity_id
,p_rev_event_id
,C_EVENT_TYPE_CODE_MANUAL
,p_gl_date
,l_period_name
,p_info.reference_date
,p_info.balance_type_code
,p_info.budget_version_id
,C_GL_TRANSFER_MODE_NO
,p_info.je_category_name
,C_AE_STATUS_INCOMPLETE
,p_info.type_code
,l_reversal_label||': '||p_info.description
,sysdate
,l_last_updated_by
,sysdate
,l_last_updated_by
,l_last_update_login
,NVL(p_info.accrual_reversal_flag,'N')) -- 4262811 accrual_reversal_flag
RETURNING ae_header_id INTO p_rev_header_id;
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 p_rev_header_id
,analytical_criterion_code
,analytical_criterion_type_code
,amb_context_code
,ac1
,ac2
,ac3
,ac4
,ac5
,1
FROM xla_ae_header_acs
WHERE ae_header_id = p_info.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 -- 5109240
,unrounded_entered_cr -- 5109240
,unrounded_accounted_dr -- 5109240
,unrounded_accounted_cr -- 5109240
,description
,statistical_amount
,currency_code
,currency_conversion_type
,currency_conversion_date
,currency_conversion_rate
,accounting_class_code
,jgzz_recon_ref
,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) -- 4262811
SELECT
application_id
,p_rev_header_id
,ae_line_num
,displayed_line_number
,code_combination_id
,gl_transfer_mode_code
,sysdate
,l_last_updated_by
,sysdate
,l_last_updated_by
,l_last_update_login
,party_id
,party_site_id
,party_type_code
,DECODE(p_reversal_method, C_REVERSAL_SWITCH_DR_CR,
entered_cr, -entered_dr)
,DECODE(p_reversal_method, C_REVERSAL_SWITCH_DR_CR,
entered_dr, -entered_cr)
,DECODE(p_reversal_method, C_REVERSAL_SWITCH_DR_CR,
accounted_cr, -accounted_dr)
,DECODE(p_reversal_method, C_REVERSAL_SWITCH_DR_CR,
accounted_dr, -accounted_cr)
-- 5109240 unrounded amounts
,DECODE(p_reversal_method, C_REVERSAL_SWITCH_DR_CR,
unrounded_entered_cr, -unrounded_entered_dr)
,DECODE(p_reversal_method, C_REVERSAL_SWITCH_DR_CR,
unrounded_entered_dr, -unrounded_entered_cr)
,DECODE(p_reversal_method, C_REVERSAL_SWITCH_DR_CR,
unrounded_accounted_cr, -unrounded_accounted_dr)
,DECODE(p_reversal_method, C_REVERSAL_SWITCH_DR_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
,'XLAJEL'
,attribute_category
,encumbrance_type_id
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,gain_or_loss_flag
,p_info.ledger_id
,p_gl_date
,NVL(mpa_accrual_entry_flag,'N') -- 4262811 mpa_accrual_entry_flag
FROM xla_ae_lines
WHERE application_id = p_info.application_id
AND ae_header_id = p_info.header_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 p_rev_header_id
,ae_line_num
,analytical_criterion_code
,analytical_criterion_type_code
,amb_context_code
,ac1
,ac2
,ac3
,ac4
,ac5
,1
FROM xla_ae_line_acs
WHERE ae_header_id = p_info.header_id;
SELECT ae_line_num
FROM xla_ae_lines
WHERE application_id = p_application_id
AND ae_header_id = p_ae_header_id
ORDER BY ae_line_num;
UPDATE xla_ae_lines
set displayed_line_number = l_displayed_nums(j)
WHERE application_id = p_application_id
AND ae_header_id = p_ae_header_id
AND ae_line_num = l_ae_line_nums(j);
SELECT gl.name, gl.currency_code, gl.ledger_category_code, xlo.rounding_rule_code
FROM gl_ledgers gl, xla_ledger_options xlo
WHERE gl.ledger_id = p_trx_ledger_id
AND xlo.application_id = p_application_id
AND xlo.ledger_id = p_trx_ledger_id;
SELECT xlr.target_ledger_id ledger_id
, xlr.name ledger_name
, xlr.currency_code ledger_currency
, xlr.ALC_DEFAULT_CONV_RATE_TYPE
, xlr.ALC_INHERIT_CONVERSION_TYPE
, decode(xlr.ALC_NO_RATE_ACTION_CODE, 'FIND_RATE', nvl(xlr.ALC_MAX_DAYS_ROLL_RATE, -1), 0) max_roll_days
FROM xla_ledger_relationships_v xlr
,fnd_currencies fcu
WHERE xlr.primary_ledger_id = p_info.ledger_id
AND xlr.relationship_enabled_flag = 'Y'
AND xlr.ledger_category_code = 'ALC'
AND fcu.currency_code = xlr.currency_code;
SELECT xal.*
, decode( fc.derive_type, 'EURO', 'EURO', 'EMU',
decode( sign( trunc(nvl(xal.currency_conversion_date, xal.accounting_date)) -
trunc(fc.derive_effective)), -1, 'OTHER', 'EMU'), 'OTHER' ) from_type
, decode( fc1.derive_type, 'EURO', 'EURO', 'EMU',
decode( sign( trunc(nvl(xal.currency_conversion_date, xal.accounting_date)) -
trunc(fc1.derive_effective)), -1, 'OTHER', 'EMU'), 'OTHER' ) to_type
, decode( fc2.derive_type, 'EURO', 'EURO', 'EMU',
decode( sign( trunc(nvl(xal.currency_conversion_date, xal.accounting_date)) -
trunc(fc2.derive_effective)), -1, 'OTHER', 'EMU'), 'OTHER' ) primary_type
FROM xla_ae_lines xal
, fnd_currencies fc
, fnd_currencies fc1
, fnd_currencies fc2
WHERE xal.ae_header_id = p_info.header_id
AND xal.application_id = p_info.application_id
AND fc.currency_code = xal.currency_code
AND fc1.currency_code = p_mrc_currency
and fc2.currency_code = p_primary_currency
ORDER BY xal.currency_code
,xal.currency_conversion_type
,xal.currency_conversion_date
,xal.currency_conversion_rate;
l_last_updated_by INTEGER;
l_last_update_login INTEGER;
l_last_updated_by := nvl(xla_environment_pkg.g_usr_id,-1);
l_last_update_login := nvl(xla_environment_pkg.g_login_id,-1);
INSERT INTO xla_ae_headers
(ae_header_id
,application_id
,amb_context_code
,ledger_id
,entity_id
,event_id
,event_type_code
,accounting_date
,completed_date
,reference_date
,balance_type_code
,budget_version_id
,gl_transfer_status_code
,je_category_name
,accounting_entry_status_code
,accounting_entry_type_code
,description
,accounting_batch_id
,period_name
,packet_id
,product_rule_code
,product_rule_type_code
,product_rule_version
,gl_transfer_date
,doc_sequence_id
,doc_sequence_value
,close_acct_seq_version_id
,close_acct_seq_value
,close_acct_seq_assign_id
,funds_status_code
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,accrual_reversal_flag) -- 4262811
values( xla_ae_headers_s.NEXTVAL
,p_info.application_id
,p_info.amb_context_code
,l_mrc_ledger.ledger_id
,p_info.entity_id
,p_info.event_id
,p_info.event_type_code
,p_info.gl_date
,p_info.completed_date
,p_info.reference_date
,p_info.balance_type_code
,p_info.budget_version_id
,p_info.gl_transfer_status_code
,p_info.je_category_name
,p_info.status_code
,p_info.type_code
,p_info.description
,p_info.accounting_batch_id
,p_info.period_name
,p_info.packet_id
,p_info.product_rule_code
,p_info.product_rule_type_code
,p_info.product_rule_version
,p_info.gl_transfer_date
,p_info.doc_sequence_id
,p_info.doc_sequence_value
,p_info.close_acct_seq_version_id
,p_info.close_acct_seq_value
,p_info.close_acct_seq_assign_id
,p_info.funds_status_code
,p_info.attribute_category
,p_info.attribute1
,p_info.attribute2
,p_info.attribute3
,p_info.attribute4
,p_info.attribute5
,p_info.attribute6
,p_info.attribute7
,p_info.attribute8
,p_info.attribute9
,p_info.attribute10
,p_info.attribute11
,p_info.attribute12
,p_info.attribute13
,p_info.attribute14
,p_info.attribute15
,sysdate
,l_last_updated_by
,sysdate
,l_last_updated_by
,l_last_update_login
,NVL(p_info.accrual_reversal_flag,'N')) -- 4262811 accrual_reversal_flag
RETURNING ae_header_id
INTO p_ae_header_ids(i);
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 p_ae_header_ids(i)
,analytical_criterion_code
,analytical_criterion_type_code
,amb_context_code
,ac1
,ac2
,ac3
,ac4
,ac5
,1
FROM xla_ae_header_acs
WHERE ae_header_id = p_info.header_id;
INSERT INTO xla_ae_lines
(ae_header_id
,ae_line_num
,displayed_line_number
,application_id
,code_combination_id
,gl_transfer_mode_code
,accounting_class_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
,jgzz_recon_ref
,ussgl_transaction_code
,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) -- 4262811
VALUES
(p_ae_header_ids(i)
,l.ae_line_num
,l.displayed_line_number
,p_info.application_id
,l.code_combination_id
,l.gl_transfer_mode_code
,l.accounting_class_code
,sysdate
,l_last_updated_by
,sysdate
,l_last_updated_by
,l_last_update_login
,l.party_id
,l.party_site_id
,l.party_type_code
,l.entered_dr
,l.entered_cr
,l_accounted_dr
,l_accounted_cr
,l.unrounded_entered_dr -- unrounded entered dr
,l.unrounded_entered_cr -- unrounded entered cr
,DECODE(l_trx_ledger_currency,l_mrc_ledger.ledger_currency
,l.unrounded_accounted_dr,l.unrounded_entered_dr * l_conv_rate) -- unrounded accounted dr
,DECODE(l_trx_ledger_currency,l_mrc_ledger.ledger_currency
,l.unrounded_accounted_cr,l.unrounded_entered_cr * l_conv_rate) -- unrounded accounted cr
,l.description
,l.statistical_amount
,l.currency_code
,decode(l.currency_code, l_mrc_ledger.ledger_currency, NULL, l_conv_type)
,decode(l.currency_code, l_mrc_ledger.ledger_currency, NULL, l_conv_date)
,decode(l.currency_code, l_mrc_ledger.ledger_currency, NULL, l_conv_rate)
,l.jgzz_recon_ref
,l.ussgl_transaction_code
,'XLAJEL'
,l.attribute_category
,l.encumbrance_type_id
,l.attribute1
,l.attribute2
,l.attribute3
,l.attribute4
,l.attribute5
,l.attribute6
,l.attribute7
,l.attribute8
,l.attribute9
,l.attribute10
,l.attribute11
,l.attribute12
,l.attribute13
,l.attribute14
,l.attribute15
,l.gain_or_loss_flag
,l_mrc_ledger.ledger_id
,p_info.gl_date
,NVL(l.mpa_accrual_entry_flag,'N')); -- 4262811 mpa_accrual_entry_flag
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 p_ae_header_ids(i)
,ae_line_num
,analytical_criterion_code
,analytical_criterion_type_code
,amb_context_code
,ac1
,ac2
,ac3
,ac4
,ac5
,1
FROM xla_ae_line_acs
WHERE ae_header_id = p_info.header_id;
PROCEDURE delete_mrc_entries
(p_event_id IN INTEGER
,p_application_id IN INTEGER
,p_ledger_id IN INTEGER)
IS
CURSOR c_entries IS
SELECT h.ae_header_id
FROM xla_ae_headers h
WHERE h.event_id = p_event_id
AND h.application_id = p_application_id
AND h.ledger_id <> p_ledger_id;
l_log_module := C_DEFAULT_MODULE||'.delete_mrc_entries';
trace(p_msg => 'BEGIN of procedure delete_mrc_entries',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
trace(p_msg => 'BEGIN LOOP - delete mrc entries',
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
trace(p_msg => 'LOOP - delete MRC entry: ae_header_id = '||l_entry.ae_header_id,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
trace(p_msg => 'END LOOP - delete mrc entries',
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
DELETE FROM xla_ae_line_acs
WHERE ae_header_id = l_header_ids(j);
DELETE FROM xla_ae_header_acs
WHERE ae_header_id = l_header_ids(j);
DELETE xla_distribution_links
WHERE ae_header_id = l_header_ids(j)
AND application_id = p_application_id;
DELETE FROM xla_ae_segment_values
WHERE ae_header_id = l_header_ids(j);
DELETE FROM xla_ae_lines
WHERE ae_header_id = l_header_ids(j)
AND application_id = p_application_id;
DELETE xla_ae_headers
WHERE ae_header_id = l_header_ids(j)
AND application_id = p_application_id;
trace(p_msg => 'END of procedure delete_mrc_entries',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
(p_location => 'xla_journal_entries_pkg.delete_mrc_entries');
END delete_mrc_entries;
PROCEDURE update_event_status
(p_info IN t_je_info
,p_completion_option IN VARCHAR2)
IS
l_event_source_info xla_events_pub_pkg.t_event_source_info;
l_log_module := C_DEFAULT_MODULE||'.update_event_status';
trace(p_msg => 'BEGIN of procedure update_event_status',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
xla_events_pkg.update_manual_event
(p_event_source_info => l_event_source_info
,p_event_id => p_info.event_id
,p_event_status_code => l_event_status
,p_process_status_code => l_process_status);
trace(p_msg => 'BEGIN of procedure update_event_status',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
(p_location => 'xla_journal_entries_pkg.update_event_status');
END update_event_status;
SELECT decode(p_rounding_rule_code
,'UP'
,ceil(p_amount/nvl(minimum_accountable_unit, power(10, -1* precision)))*
nvl(minimum_accountable_unit, power(10, -1* precision))
,'DOWN'
,floor(p_amount/nvl(minimum_accountable_unit, power(10, -1* precision)))*
nvl(minimum_accountable_unit, power(10, -1* precision))
,decode(minimum_accountable_unit, NULL,
round(p_amount, precision),
round(p_amount/minimum_accountable_unit) * minimum_accountable_unit))
INTO l_rounded_amount
FROM fnd_currencies
WHERE currency_code = p_currency_code;
CURSOR c IS SELECT legal_entity_id
FROM xle_fp_ou_ledger_v
WHERE legal_entity_id = p_legal_entity_id;
CURSOR c IS SELECT nvl(sum(nvl(accounted_dr,0)),0)
, nvl(sum(nvl(accounted_cr,0)),1)
, nvl(sum(CASE WHEN accounted_dr IS NULL THEN 0 ELSE 1 end),0)
, nvl(sum(CASE WHEN accounted_cr IS NULL THEN 0 ELSE 1 end),0)
, nvl(sum(CASE WHEN currency_code = 'STAT' THEN 1 ELSE 0 end),0)
FROM xla_ae_lines
WHERE application_id = p_application_id
AND ae_header_id = p_ae_header_id;
PROCEDURE validate_delete_mode
(p_status_code IN VARCHAR2
,p_mode IN VARCHAR2
,p_msg_mode IN VARCHAR2)
IS
l_log_module VARCHAR2(240);
l_log_module := C_DEFAULT_MODULE||'.validate_delete_mode';
trace(p_msg => 'BEGIN of procedure validate_delete_mode',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
IF (p_mode NOT in (C_DELETE_NORMAL_MODE, C_DELETE_FORCE_MODE)) THEN
IF (C_LEVEL_ERROR >= g_log_level) THEN
trace(p_msg => 'Error: XLA_MJE_INVALID_DELETE_MODE',
p_module => l_log_module,
p_level => C_LEVEL_ERROR);
,p_msg_name => 'XLA_MJE_INVALID_DELETE_MODE'
,p_msg_mode => p_msg_mode);
p_mode <> C_DELETE_FORCE_MODE) THEN
IF (C_LEVEL_ERROR >= g_log_level) THEN
trace(p_msg => 'Error: XLA_MJE_INVALID_DELETE_FINAL',
p_module => l_log_module,
p_level => C_LEVEL_ERROR);
,p_msg_name => 'XLA_MJE_INVALID_DELETE_FINAL'
,p_msg_mode => p_msg_mode);
trace(p_msg => 'END of procedure validate_delete_mode',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
(p_location => 'xla_journal_entries_pkg.validate_delete_mode');
END validate_delete_mode;
SELECT ae_header_id
FROM xla_accounting_errors
WHERE ae_header_id = p_ae_header_id
AND ROWNUM = 1;
DELETE FROM xla_accounting_errors
WHERE ae_header_id = p_ae_header_id
AND event_id = p_event_id
AND nvl(ae_line_num,-1) = nvl(p_ae_line_num,-1);
SELECT gl.currency_code,
xlo.rounding_rule_code,
DECODE(gl.bal_seg_column_name, 'SEGMENT1', ccid.segment1,
'SEGMENT2', ccid.segment2,
'SEGMENT3', ccid.segment3,
'SEGMENT4', ccid.segment4,
'SEGMENT5', ccid.segment5,
'SEGMENT6', ccid.segment6,
'SEGMENT7', ccid.segment7,
'SEGMENT8', ccid.segment8,
'SEGMENT9', ccid.segment9,
'SEGMENT10', ccid.segment10,
'SEGMENT11', ccid.segment11,
'SEGMENT12', ccid.segment12,
'SEGMENT13', ccid.segment13,
'SEGMENT14', ccid.segment14,
'SEGMENT15', ccid.segment15,
'SEGMENT16', ccid.segment16,
'SEGMENT17', ccid.segment17,
'SEGMENT18', ccid.segment18,
'SEGMENT19', ccid.segment19,
'SEGMENT20', ccid.segment20,
'SEGMENT21', ccid.segment21,
'SEGMENT22', ccid.segment22,
'SEGMENT23', ccid.segment23,
'SEGMENT24', ccid.segment24,
'SEGMENT25', ccid.segment25,
'SEGMENT26', ccid.segment26,
'SEGMENT27', ccid.segment27,
'SEGMENT28', ccid.segment28,
'SEGMENT29', ccid.segment29,
'SEGMENT30', ccid.segment30),
DECODE(gl.mgt_seg_column_name, 'SEGMENT1', ccid.segment1,
'SEGMENT2', ccid.segment2,
'SEGMENT3', ccid.segment3,
'SEGMENT4', ccid.segment4,
'SEGMENT5', ccid.segment5,
'SEGMENT6', ccid.segment6,
'SEGMENT7', ccid.segment7,
'SEGMENT8', ccid.segment8,
'SEGMENT9', ccid.segment9,
'SEGMENT10', ccid.segment10,
'SEGMENT11', ccid.segment11,
'SEGMENT12', ccid.segment12,
'SEGMENT13', ccid.segment13,
'SEGMENT14', ccid.segment14,
'SEGMENT15', ccid.segment15,
'SEGMENT16', ccid.segment16,
'SEGMENT17', ccid.segment17,
'SEGMENT18', ccid.segment18,
'SEGMENT19', ccid.segment19,
'SEGMENT20', ccid.segment20,
'SEGMENT21', ccid.segment21,
'SEGMENT22', ccid.segment22,
'SEGMENT23', ccid.segment23,
'SEGMENT24', ccid.segment24,
'SEGMENT25', ccid.segment25,
'SEGMENT26', ccid.segment26,
'SEGMENT27', ccid.segment27,
'SEGMENT28', ccid.segment28,
'SEGMENT29', ccid.segment29,
'SEGMENT30', ccid.segment30)
FROM gl_ledgers gl,
gl_code_combinations ccid,
xla_ledger_options xlo
WHERE ccid.code_combination_id = p_code_combination_id
AND gl.ledger_id = p_ledger_id
AND xlo.ledger_id = p_ledger_id
AND xlo.application_id = p_application_id
;
SELECT decode(transfer_to_gl_mode_code,'D','D','S')
FROM xla_ledger_options
WHERE application_id = p_application_id
AND ledger_id = p_ledger_id;
PROCEDURE update_segment_values
(p_ae_header_id IN INTEGER
,p_seg_type IN VARCHAR2
,p_seg_value IN VARCHAR2
,p_action IN VARCHAR2)
IS
CURSOR c_seg IS
SELECT ae_lines_count
FROM xla_ae_segment_values
WHERE segment_type_code = p_seg_type
AND ae_header_id = p_ae_header_id
AND segment_value = p_seg_value;
l_log_module := C_DEFAULT_MODULE||'.update_segment_values';
trace(p_msg => 'BEGIN of procedure update_segment_values',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
DELETE xla_ae_segment_values
WHERE ae_header_id = p_ae_header_id
AND segment_type_code = p_seg_type
AND segment_value = p_seg_value;
UPDATE xla_ae_segment_values
SET ae_lines_count = ae_lines_count - 1
WHERE ae_header_id = p_ae_header_id
AND segment_type_code = p_seg_type
AND segment_value = p_seg_value;
INSERT INTO xla_ae_segment_values(ae_header_id, segment_type_code, segment_value, ae_lines_count)
VALUES(p_ae_header_id, p_seg_type, p_seg_value, 1);
UPDATE xla_ae_segment_values
SET ae_lines_count = ae_lines_count + 1
WHERE ae_header_id = p_ae_header_id
AND segment_type_code = p_seg_type
AND segment_value = p_seg_value;
trace(p_msg => 'END of procedure update_segment_values',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
(p_location => 'xla_journal_entries_pkg.update_segment_values');
END update_segment_values;
SELECT je_category_name
FROM gl_je_categories
WHERE je_category_name = p_je_category_name;
SELECT application_id
FROM xla_subledgers
WHERE application_id = p_application_id;
SELECT name, ledger_category_code, latest_encumbrance_year, enable_budgetary_control_flag
FROM gl_ledgers
WHERE ledger_id = p_ledger_id;
SELECT ae_line_num
,entered_dr
,entered_cr
,currency_code
,accounted_dr
,accounted_cr
,currency_conversion_type conv_type
,currency_conversion_date conv_date
,currency_conversion_rate conv_rate
FROM xla_ae_lines
WHERE ae_header_id = p_ae_header_id
AND application_id = p_application_id;
SELECT closing_status, period_name, period_type
FROM gl_period_statuses
WHERE application_id = C_GL_APPLICATION_ID
AND ledger_id = p_ledger_id
AND adjustment_period_flag = 'N'
AND p_accounting_date BETWEEN start_date AND end_date;
DELETE
FROM PSA_BC_REPORT_EVENTS_GT;
INSERT
INTO PSA_BC_REPORT_EVENTS_GT(packet_id)
VALUES(p_packet_id);
SELECT PSA_BC_XML_REPORT_S.nextval
INTO l_sequence_id
FROM DUAL;
PROCEDURE update_data
(p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2
,p_application_id IN INTEGER
,p_ae_header_id IN INTEGER
,p_ae_line_num IN INTEGER DEFAULT NULL
,p_item_name IN VARCHAR2
,p_value_varchar2 IN VARCHAR2 DEFAULT NULL
,p_value_date IN DATE DEFAULT NULL
,p_value_number IN NUMBER DEFAULT NULL
,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) := 'update_data';
l_log_module := C_DEFAULT_MODULE||'.update_data';
trace(p_msg => 'BEGIN of procedure update_data',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
SELECT MIN(entity_id), MAX(gl_transfer_status_code)
INTO l_entity_id, l_gl_status
FROM xla_ae_headers
WHERE application_id = p_application_id
AND ae_header_id = p_ae_header_id
AND accounting_entry_status_code = 'F';
trace(p_msg => 'Update details',
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
trace(p_msg => 'Need ae_line_num to update '||p_item_name||'.',
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
,p_error_msg => 'Need ae_line_num to update '||p_item_name||'.');
SELECT count(*)
INTO l_dummy
FROM xla_ae_lines
WHERE application_id = p_application_id
AND ae_header_id = p_ae_header_id
AND ae_line_num = p_ae_line_num;
trace(p_msg => 'Calling xla_balances_pkg.massive_update to delete',
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
IF (NOT xla_balances_pkg.massive_update
(p_application_id => p_application_id
,p_ledger_id => NULL
,p_event_id => NULL
,p_entity_id => l_entity_id
,p_request_id => NULL
,p_accounting_batch_id => NULL
,p_update_mode => 'D' -- Delete
,p_execution_mode => 'O')) THEN -- Online
IF (C_LEVEL_STATEMENT >= g_log_level) THEN
trace(p_msg => 'Error from massive_update.',
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
trace(p_msg => 'Returned from xla_balances_pkg.massive_update',
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
trace(p_msg => 'update header details',
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
Update XLA_AE_HEADERS
SET DESCRIPTION = DECODE(p_item_name, C_ITEM_HEADER_DESCRIPTION, p_value_varchar2, DESCRIPTION)
,ACCOUNTING_DATE = DECODE(p_item_name, C_ITEM_GL_DATE, p_value_date, ACCOUNTING_DATE)
,REFERENCE_DATE = DECODE(p_item_name, C_ITEM_REFERENCE_DATE, p_value_date, REFERENCE_DATE)
WHERE application_id = p_application_id
AND ae_header_id = p_ae_header_id;
trace(p_msg => 'update line details',
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
Update XLA_AE_LINES
SET CODE_COMBINATION_ID = DECODE(p_item_name, C_ITEM_ACCOUNT, p_value_number, CODE_COMBINATION_ID)
,ACCOUNTED_DR = DECODE(p_item_name, C_ITEM_ACCOUNTED_DR, p_value_number, ACCOUNTED_DR)
,ACCOUNTED_CR = DECODE(p_item_name, C_ITEM_ACCOUNTED_CR, p_value_number, ACCOUNTED_CR)
,CURRENCY_CODE = DECODE(p_item_name, C_ITEM_CURRENCY_CODE, p_value_varchar2, CURRENCY_CODE)
,CURRENCY_CONVERSION_TYPE = DECODE(p_item_name, C_ITEM_CURR_CONV_TYPE, p_value_varchar2, CURRENCY_CONVERSION_TYPE)
,CURRENCY_CONVERSION_RATE = DECODE(p_item_name, C_ITEM_CURR_CONV_RATE, p_value_number, CURRENCY_CONVERSION_RATE)
,CURRENCY_CONVERSION_DATE = DECODE(p_item_name, C_ITEM_CURR_CONV_DATE, p_value_date, CURRENCY_CONVERSION_DATE)
,DESCRIPTION = DECODE(p_item_name, C_ITEM_LINE_DESCRIPTION, p_value_varchar2, DESCRIPTION)
,ENTERED_DR = DECODE(p_item_name, C_ITEM_ENTERED_DR, p_value_number, ENTERED_DR)
,ENTERED_CR = DECODE(p_item_name, C_ITEM_ENTERED_CR, p_value_number, ENTERED_CR)
,ACCOUNTING_DATE = DECODE(p_item_name, C_ITEM_GL_DATE, p_value_date, ACCOUNTING_DATE)
,ACCOUNTING_CLASS_CODE = DECODE(p_item_name, C_ITEM_ACCOUNTING_CLASS, p_value_varchar2, ACCOUNTING_CLASS_CODE)
WHERE application_id = p_application_id
AND ae_header_id = p_ae_header_id
AND ae_line_num = DECODE(p_item_name, C_ITEM_GL_DATE, ae_line_num, p_ae_line_num);
trace(p_msg => 'Calling xla_balances_pkg.massive_update to add',
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
IF (NOT xla_balances_pkg.massive_update
(p_application_id => p_application_id
,p_ledger_id => NULL
,p_event_id => NULL
,p_entity_id => l_entity_id
,p_request_id => NULL
,p_accounting_batch_id => NULL
,p_update_mode => 'A' -- Add
,p_execution_mode => 'O')) THEN -- Online
xla_datafixes_pub.Log_error(p_module => l_log_module
,p_error_msg => 'Error when adding control balance calculation');
trace(p_msg => 'Returned from xla_balances_pkg.massive_update',
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
trace(p_msg => 'END of procedure update_data',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
END update_data;
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)
SELECT p_application_id
,xah.event_id
,p_ae_header_id
,p_ae_line_num -- ae line num
,'XLA_MANUAL' -- source distribution type
,xal.statistical_amount -- statistical Amount
,p_ref_ae_header_id -- ref ae header id
,p_ref_temp_line_num -- ref temp line num
,'N' -- merge duplicate code
,p_temp_line_num -- temp line num
,p_ref_event_id -- ref event id
,C_EVENT_CLASS_CODE_MANUAL -- event class code
,C_EVENT_TYPE_CODE_MANUAL -- event type code
,xal.unrounded_entered_dr
,xal.unrounded_entered_cr
,xal.unrounded_accounted_dr
,xal.unrounded_accounted_cr
FROM xla_ae_headers xah
,xla_ae_lines xal
WHERE xah.application_id = p_application_id
AND xah.ae_header_id = p_ae_header_id
AND xal.application_id = xah.application_id
AND xal.ae_header_id = xah.ae_header_id
AND xal.ae_line_num = p_ae_line_num;
PROCEDURE delete_distribution_link
(p_application_id IN INTEGER
,p_ae_header_id IN INTEGER
,p_ref_ae_header_id IN INTEGER
,p_temp_line_num IN INTEGER)
IS
l_log_module VARCHAR2(240);
l_log_module := C_DEFAULT_MODULE||'.delete_distribution_link';
trace(p_msg => 'BEGIN of procedure delete_distribution_link',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
DELETE xla_distribution_links
WHERE application_id = p_application_id
AND ref_ae_header_id = p_ref_ae_header_id
AND temp_line_num = p_temp_line_num
AND ae_header_id = p_ae_header_id;
trace(p_msg => 'END of procedure delete_distribution_link',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
(p_location => 'xla_journal_entries_pkg.delete_distribution_link');
END delete_distribution_link;
PROCEDURE update_distribution_link
(p_application_id IN INTEGER
,p_ae_header_id IN INTEGER
,p_ref_ae_header_id IN INTEGER
,p_temp_line_num IN INTEGER
,p_unrounded_entered_dr IN NUMBER
,p_unrounded_entered_cr IN NUMBER
,p_unrounded_accounted_dr IN NUMBER
,p_undournde_accounted_cr IN NUMBER
,p_statistical_amount IN NUMBER)
IS
l_log_module VARCHAR2(240);
l_log_module := C_DEFAULT_MODULE||'.update_distribution_link';
trace(p_msg => 'BEGIN of procedure update_distribution_link',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
UPDATE xla_distribution_links
SET unrounded_entered_dr = p_unrounded_entered_dr
,unrounded_entered_cr = p_unrounded_entered_cr
,unrounded_accounted_dr = p_unrounded_accounted_dr
,unrounded_accounted_cr = p_undournde_accounted_cr
,statistical_amount = p_statistical_amount
WHERE application_id = p_application_id
AND ref_ae_header_id = p_ref_ae_header_id
AND temp_line_num = p_temp_line_num
AND ae_header_id = p_ae_header_id;
trace(p_msg => 'END of procedure update_distribution_link',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
(p_location => 'xla_journal_entries_pkg.update_distribution_link');
END update_distribution_link;
SELECT event_id
INTO l_ref_event_id
FROM xla_ae_headers
WHERE application_id = p_application_id
AND ae_header_id = p_ref_ae_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)
SELECT p_application_id
,xah.event_id
,p_ae_header_id
,ae_line_num
,'XLA_MANUAL' -- source distribution type
,xal.statistical_amount -- statistical amount
,p_ref_ae_header_id -- ref ae header id
,ae_line_num -- ref temp line num
,'N' -- merge duplicate code
,-1 * ae_line_num -- temp line num
,l_ref_event_id -- ref event id
,C_EVENT_CLASS_CODE_MANUAL -- event class code
,C_EVENT_TYPE_CODE_MANUAL -- event type code
,xal.unrounded_entered_dr
,xal.unrounded_entered_cr
,xal.unrounded_accounted_dr
,xal.unrounded_accounted_cr
FROM xla_ae_headers xah
,xla_ae_lines xal
WHERE xah.application_id = p_application_id
AND xah.ae_header_id = p_ae_header_id
AND xal.application_id = xah.application_id
AND xal.ae_header_id = xah.ae_header_id;
SELECT COUNT(1)
INTO l_cnt
FROM xla_distribution_links
WHERE application_id = p_application_id
AND ae_header_id = p_ae_header_id
AND temp_line_num = -1 * p_temp_line_num
AND ROWNUM <=1;
SELECT ref_ae_header_id
,ref_event_id
,temp_line_num
INTO l_ref_ae_header_id
,l_ref_event_id
,l_temp_line_num
FROM xla_distribution_links
WHERE application_id = p_application_id
AND ae_header_id = p_ae_header_id
AND temp_line_num = -1 * p_temp_line_num
AND ROWNUM <= 1;
SELECT ae_header_id
,event_id
,-1 * temp_line_num
,temp_line_num
INTO l_ref_ae_header_id
,l_ref_event_id
,l_temp_line_num
,l_ref_temp_line_num
FROM xla_distribution_links
WHERE event_id =
(SELECT ref_event_id
FROM xla_distribution_links
WHERE application_id = p_application_id -- non mrc
AND ae_header_id = p_ae_header_id -- non mrc
AND temp_line_num = -1 * p_temp_line_num -- non mrc
AND ROWNUM <= 1)
AND ae_header_id <>
(SELECT ref_ae_header_id
FROM xla_distribution_links
WHERE application_id = p_application_id -- non mrc
AND ae_header_id = p_ae_header_id -- non mrc
AND temp_line_num = -1 * p_temp_line_num -- non mrc
AND ROWNUM <= 1)
AND temp_line_num = p_temp_line_num;