[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_headers_insert BOOLEAN DEFAULT FALSE;
l_lines_insert BOOLEAN DEFAULT FALSE;
l_rates_insert BOOLEAN DEFAULT FALSE;
l_links_insert BOOLEAN DEFAULT FALSE;
l_ae_insert BOOLEAN DEFAULT FALSE;
SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
INTO l_sql_time
FROM dual;
SELECT end_date --- removed start_date
INTO l_sec_alc_end_date
FROM gl_period_statuses
WHERE period_name = (SELECT latest_opened_period_name -- removed first_ledger_period_name
FROM gl_ledgers
WHERE ledger_id = l_primary_ledger_id) -- removed l_secondary_alc_ledger_id
AND ledger_id = l_primary_ledger_id
AND application_id = 101;
(p_msg => 'error in upg main in end date select' || l_sec_alc_end_date
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
SELECT currency_code
INTO l_primary_currency_code
FROM gl_ledgers
WHERE ledger_id = l_primary_ledger_id;
SELECT ledger_category_code, currency_code
INTO l_ledger_category_code, l_sec_alc_currency_code
FROM gl_ledgers
WHERE ledger_id = l_secondary_alc_ledger_id;
SELECT minimum_accountable_unit, precision
INTO l_sec_alc_mau, l_sec_alc_precision
FROM fnd_currencies
WHERE currency_code = l_sec_alc_currency_code;
SELECT alc_init_conv_option_code, alc_initializing_rate_type, alc_initializing_rate_date
INTO l_conversion_option, l_currency_conversion_type, l_currency_conversion_date
FROM gl_ledger_relationships
WHERE relationship_id = l_relationship_id;
SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
INTO l_sql_time
FROM dual;
alc_insert();
secondary_insert();
insert_links_segments();
UPDATE gl_ledger_relationships
SET hist_conv_status_code = 'SUCCESSFUL'
WHERE relationship_id = l_relationship_id;
(p_msg => 'Upgrade Successfully Completed. Rows Updated in gl_ledger_relationships = ' || SQL%ROWCOUNT
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
INTO l_sql_time
FROM dual;
UPDATE gl_ledger_relationships
SET hist_conv_status_code = 'FAILED'
WHERE relationship_id = l_relationship_id;
(p_msg => 'Ledger id stamped as failed. Rows updated in gl_ledger_relationships = ' || SQL%ROWCOUNT
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
IF l_rates_insert = TRUE THEN
DELETE
FROM xla_rc_upgrade_rates
WHERE relationship_id = l_relationship_id
AND upgrade_run_id = l_upgrade_id;
(p_msg => 'deleted rows from xla_rc_upgrade_rates = ' || SQL%ROWCOUNT
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
IF l_ae_insert = TRUE THEN
DELETE
FROM xla_ae_segment_values
WHERE upg_batch_id = l_upgrade_id;
(p_msg => 'deleted rows from xla_ae_segment_values = ' || SQL%ROWCOUNT
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
IF l_lines_insert = TRUE THEN
DELETE
FROM xla_ae_lines
WHERE request_id = l_upgrade_id;
(p_msg => 'deleted rows from xla_ae_lines = ' || SQL%ROWCOUNT
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
IF l_headers_insert = TRUE THEN
DELETE
FROM xla_ae_headers
WHERE request_id = l_upgrade_id;
(p_msg => 'deleted rows from xla_ae_headers = ' || SQL%ROWCOUNT
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
IF l_links_insert = TRUE THEN
DELETE
FROM xla_distribution_links
WHERE gain_or_loss_ref = to_char(l_upgrade_id);
(p_msg => 'deleted rows from xla_distribution_links = ' || SQL%ROWCOUNT
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
SELECT 1
INTO l_relationship_exists
FROM DUAL
WHERE EXISTS (SELECT 1
FROM gl_ledger_relationships
WHERE primary_ledger_id = l_primary_ledger_id
AND target_ledger_id = l_secondary_alc_ledger_id
AND relationship_enabled_flag = 'Y');
SELECT min(relationship_id)
INTO l_relationship_id
FROM gl_ledger_relationships
WHERE primary_ledger_id = l_primary_ledger_id
AND target_ledger_id = l_secondary_alc_ledger_id
AND relationship_enabled_flag = 'Y';
SELECT hist_conv_status_code
INTO l_previous_status
FROM gl_ledger_relationships
WHERE relationship_id = l_relationship_id;
UPDATE gl_ledger_relationships
SET hist_conv_status_code = 'RUNNING'
WHERE relationship_id = l_relationship_id;
(p_msg => 'Conversion status set to RUNNING, row updated in gl_ledger_relationships = ' || SQL%ROWCOUNT
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
SELECT start_date
INTO l_start_date
FROM gl_period_statuses
WHERE period_name = l_start_period
AND ledger_id = l_secondary_alc_ledger_id
AND application_id = 101;
SELECT 1
INTO l_untransferred_headers
FROM DUAL
WHERE EXISTS (SELECT 1
FROM xla_ae_headers
WHERE ledger_id = l_primary_ledger_id
AND accounting_entry_status_code = 'F'
AND gl_transfer_status_code <> 'Y'
AND accounting_date >= l_start_date
AND accounting_date <= l_sec_alc_end_date);
SELECT 1
INTO l_sec_alc_data_check
FROM DUAL
WHERE EXISTS (SELECT 1
FROM xla_ae_headers
WHERE ledger_id = l_secondary_alc_ledger_id);
SELECT distinct(l.currency_code)
FROM xla_ae_lines l
WHERE exists
(SELECT 1
FROM xla_ae_headers h
WHERE h.ledger_id = l_primary_ledger_id
AND l.ae_header_id = h.ae_header_id
AND l.application_id = h.application_id
AND h.accounting_entry_status_code = 'F'
AND h.accounting_date >= l_start_date
AND h.accounting_date <= l_sec_alc_end_date);
SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
INTO l_sql_time
FROM dual;
SELECT 1
INTO l_rate_exists
FROM DUAL
WHERE EXISTS (SELECT 1
FROM xla_rc_upgrade_rates
WHERE relationship_id = l_relationship_id
AND upgrade_run_id = l_upgrade_id);
INSERT INTO xla_rc_upgrade_rates
(relationship_id
,upgrade_run_id
,from_currency
,to_currency
,denominator_rate
,numerator_rate
,conversion_rate
,precision
,minimum_accountable_unit
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
VALUES(l_relationship_id
,l_upgrade_id
,c_entered_currencies_rec.currency_code
,l_sec_alc_currency_code
,l_denominator
,l_numerator
,l_rate
,null
,null
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,fnd_global.login_id);
l_rates_insert := TRUE;
SELECT count(*)
INTO l_currency_count
FROM xla_rc_upgrade_rates
WHERE relationship_id = l_relationship_id
AND upgrade_run_id = l_upgrade_id;
(p_msg => 'Rows inserted into xla_rc_upgrade_rates after calling GL API = ' || l_currency_count
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
INSERT INTO xla_rc_upgrade_rates
(relationship_id
,upgrade_run_id
,from_currency
,to_currency
,denominator_rate
,numerator_rate
,conversion_rate
,precision
,minimum_accountable_unit
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
VALUES(l_relationship_id
,l_upgrade_id
,l_primary_currency_code
,l_sec_alc_currency_code
,l_denominator
,l_numerator
,l_rate
,null
,null
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,fnd_global.login_id);
l_rates_insert := TRUE;
SELECT count(*)
INTO l_currency_count
FROM xla_rc_upgrade_rates
WHERE relationship_id = l_relationship_id
AND upgrade_run_id = l_upgrade_id;
(p_msg => 'Rows inserted into xla_rc_upgrade_rates after calling GL API = ' || l_currency_count
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
INTO l_sql_time
FROM dual;
SELECT *
FROM xla_subledgers
WHERE alc_enabled_flag = 'Y';
SELECT 1
INTO l_alc_exists
FROM DUAL
WHERE EXISTS (SELECT 1
FROM xla_subledgers
WHERE alc_enabled_flag = 'Y');
SELECT sla_accounting_method_code, chart_of_accounts_id
INTO l_primary_slam, l_primary_coa
FROM xla_ledger_relationships_v
WHERE ledger_id = l_primary_ledger_id;
SELECT sla_accounting_method_code, chart_of_accounts_id
INTO l_secondary_slam, l_secondary_coa
FROM xla_ledger_relationships_v
WHERE ledger_id = l_secondary_alc_ledger_id;
SELECT sl_coa_mapping_id
INTO l_mapping_relationship_id
FROM xla_ledger_relationships_v
WHERE ledger_id = l_secondary_alc_ledger_id
AND primary_ledger_id = l_primary_ledger_id;
SELECT name
INTO l_coa_mapping_name
FROM gl_coa_mappings
WHERE coa_mapping_id = l_mapping_relationship_id;
PROCEDURE alc_insert
IS
l_log_module VARCHAR2(240);
l_log_module := C_DEFAULT_MODULE||'.alc_insert';
SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
INTO l_sql_time
FROM dual;
(p_msg => 'xla_ae_headers insert SQL start: ' || l_sql_time
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
INSERT /*+ append */ INTO XLA_AE_HEADERS
(
AE_HEADER_ID
, APPLICATION_ID
, LEDGER_ID
, ENTITY_ID
, EVENT_ID
, EVENT_TYPE_CODE
, ACCOUNTING_DATE
, GL_TRANSFER_STATUS_CODE
, GL_TRANSFER_DATE
, JE_CATEGORY_NAME
, ACCOUNTING_ENTRY_STATUS_CODE
, ACCOUNTING_ENTRY_TYPE_CODE
, AMB_CONTEXT_CODE
, PRODUCT_RULE_TYPE_CODE
, PRODUCT_RULE_CODE
, PRODUCT_RULE_VERSION
, DESCRIPTION
, DOC_SEQUENCE_ID
, DOC_SEQUENCE_VALUE
, ACCOUNTING_BATCH_ID
, COMPLETION_ACCT_SEQ_VERSION_ID
, CLOSE_ACCT_SEQ_VERSION_ID
, COMPLETION_ACCT_SEQ_VALUE
, CLOSE_ACCT_SEQ_VALUE
, BUDGET_VERSION_ID
, FUNDS_STATUS_CODE
, ENCUMBRANCE_TYPE_ID
, BALANCE_TYPE_CODE
, REFERENCE_DATE
, COMPLETED_DATE
, PERIOD_NAME
, PACKET_ID
, COMPLETION_ACCT_SEQ_ASSIGN_ID
, CLOSE_ACCT_SEQ_ASSIGN_ID
, DOC_CATEGORY_CODE
, ATTRIBUTE_CATEGORY
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, GROUP_ID
, DOC_SEQUENCE_VERSION_ID
, DOC_SEQUENCE_ASSIGN_ID
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, PROGRAM_UPDATE_DATE
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, REQUEST_ID
, UPG_BATCH_ID
, UPG_SOURCE_APPLICATION_ID
, UPG_VALID_FLAG
, ZERO_AMOUNT_FLAG
, PARENT_AE_HEADER_ID
, PARENT_AE_LINE_NUM
, ACCRUAL_REVERSAL_FLAG
, MERGE_EVENT_ID )
SELECT /*+ parallel(xah) */
XLA_AE_HEADERS_S.nextval
, XAH.APPLICATION_ID
, l_secondary_alc_ledger_id
, XAH.ENTITY_ID
, XAH.EVENT_ID
, XAH.EVENT_TYPE_CODE
, XAH.ACCOUNTING_DATE
, XAH.GL_TRANSFER_STATUS_CODE
, XAH.GL_TRANSFER_DATE
, XAH.JE_CATEGORY_NAME
, XAH.ACCOUNTING_ENTRY_STATUS_CODE
, XAH.ACCOUNTING_ENTRY_TYPE_CODE
, XAH.AMB_CONTEXT_CODE
, XAH.PRODUCT_RULE_TYPE_CODE
, XAH.PRODUCT_RULE_CODE
, XAH.PRODUCT_RULE_VERSION
, XAH.DESCRIPTION ---??????
, XAH.DOC_SEQUENCE_ID
, XAH.DOC_SEQUENCE_VALUE
, NULL -- XAH.ACCOUNTING_BATCH_ID
, NULL -- XAH.COMPLETION_ACCT_SEQ_VERSION_ID
, NULL -- XAH.CLOSE_ACCT_SEQ_VERSION_ID
, NULL -- XAH.COMPLETION_ACCT_SEQ_VALUE
, NULL -- XAH.CLOSE_ACCT_SEQ_VALUE
, XAH.BUDGET_VERSION_ID
, XAH.FUNDS_STATUS_CODE
, XAH.ENCUMBRANCE_TYPE_ID
, XAH.BALANCE_TYPE_CODE
, XAH.REFERENCE_DATE
, XAH.COMPLETED_DATE
, XAH.PERIOD_NAME
, XAH.PACKET_ID
, NULL -- XAH.COMPLETION_ACCT_SEQ_ASSIGN_ID
, NULL -- XAH.CLOSE_ACCT_SEQ_ASSIGN_ID
, XAH.DOC_CATEGORY_CODE
, 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
, GROUP_ID --??????
, XAH.DOC_SEQUENCE_VERSION_ID
, XAH.DOC_SEQUENCE_ASSIGN_ID
, SYSDATE
, fnd_global.user_id
, SYSDATE
, fnd_global.user_id
, fnd_global.login_id
, SYSDATE
, -1 --G_PGM_APPL_ID
, -1 --G_PROGRAM_ID
, l_upgrade_id
, XAH.AE_HEADER_ID -- UPG_BATCH_ID = Header ID of parent to be used during lines insertion
, -602 -- XAH.UPG_SOURCE_APPLICATION_ID
, XAH.UPG_VALID_FLAG
, XAH.ZERO_AMOUNT_FLAG
, NULL -- No need to populate this for historical transactions
, NULL -- No need to populate this for historical transactions
, XAH.ACCRUAL_REVERSAL_FLAG
, XAH.MERGE_EVENT_ID
FROM XLA_AE_HEADERS XAH
WHERE
XAH.accounting_date >= l_start_date
AND XAH.accounting_date <= l_sec_alc_end_date
AND XAH.ledger_id = l_primary_ledger_id
AND XAH.accounting_entry_status_code = 'F'
AND XAH.balance_type_code in ('A', 'E')
--This would be only for Reporting ledger
AND EXISTS
(SELECT 1
FROM xla_subledgers s
WHERE s.alc_enabled_flag = 'Y'
AND s.application_id =XAH.application_id);
(p_msg => 'Rows inserted into xla_ae_headers: ' || SQL%ROWCOUNT
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
l_headers_insert := TRUE;
SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
INTO l_sql_time
FROM dual;
(p_msg => 'xla_ae_headers insert SQL end and xla_ae_lines SQL start: ' || l_sql_time
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
INSERT /*+ append */ INTO XLA_AE_LINES
( AE_HEADER_ID
, AE_LINE_NUM
, APPLICATION_ID
, CODE_COMBINATION_ID
, GL_TRANSFER_MODE_CODE
, GL_SL_LINK_ID
, ACCOUNTING_CLASS_CODE
, PARTY_ID
, PARTY_SITE_ID
, PARTY_TYPE_CODE
, ENTERED_DR
, ENTERED_CR
, ACCOUNTED_DR
, ACCOUNTED_CR
, DESCRIPTION
, STATISTICAL_AMOUNT
, CURRENCY_CODE
, CURRENCY_CONVERSION_DATE
, CURRENCY_CONVERSION_RATE
, CURRENCY_CONVERSION_TYPE
, USSGL_TRANSACTION_CODE
, JGZZ_RECON_REF
, CONTROL_BALANCE_FLAG
, ANALYTICAL_BALANCE_FLAG
, ATTRIBUTE_CATEGORY
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, GL_SL_LINK_TABLE
, DISPLAYED_LINE_NUMBER
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, PROGRAM_UPDATE_DATE
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, REQUEST_ID
, UPG_BATCH_ID
, UPG_TAX_REFERENCE_ID1
, UPG_TAX_REFERENCE_ID2
, UPG_TAX_REFERENCE_ID3
, UNROUNDED_ACCOUNTED_DR
, UNROUNDED_ACCOUNTED_CR
, GAIN_OR_LOSS_FLAG
, UNROUNDED_ENTERED_DR
, UNROUNDED_ENTERED_CR
, SUBSTITUTED_CCID
, BUSINESS_CLASS_CODE
, MPA_ACCRUAL_ENTRY_FLAG
, ENCUMBRANCE_TYPE_ID
, FUNDS_STATUS_CODE
, MERGE_CODE_COMBINATION_ID
, MERGE_PARTY_ID
, MERGE_PARTY_SITE_ID
, ACCOUNTING_DATE
, LEDGER_ID
, SOURCE_TABLE
, SOURCE_ID
, ACCOUNT_OVERLAY_SOURCE_ID )
SELECT /*+ parallel(xah) parallel (xal) */
XAH.AE_HEADER_ID
, XAL.AE_LINE_NUM
, XAL.APPLICATION_ID
, XAL.CODE_COMBINATION_ID
, XAL.GL_TRANSFER_MODE_CODE
, NULL --XAL.GL_SL_LINK_ID
, XAL.ACCOUNTING_CLASS_CODE
, XAL.PARTY_ID
, XAL.PARTY_SITE_ID
, XAL.PARTY_TYPE_CODE
, XAL.ENTERED_DR
, XAL.ENTERED_CR
, DECODE(XAL.currency_code,l_sec_alc_currency_code,
ENTERED_DR,
DECODE(l_sec_alc_mau,null,
ROUND(((DECODE(l_conversion_option, 'D',
NVL(XAL.ACCOUNTED_DR, XAL.ENTERED_DR),
XAL.ENTERED_DR)/XRUR.denominator_rate)*
XRUR.numerator_rate),l_sec_alc_precision),
ROUND(((DECODE(l_conversion_option, 'D',
NVL(XAL.ACCOUNTED_DR, XAL.ENTERED_DR),
XAL.ENTERED_DR)/XRUR.denominator_rate)*
XRUR.numerator_rate)/l_sec_alc_mau)*l_sec_alc_mau))
, DECODE(XAL.currency_code,l_sec_alc_currency_code,
ENTERED_CR,
DECODE(l_sec_alc_mau,null,
ROUND(((DECODE(l_conversion_option, 'D',
NVL(XAL.ACCOUNTED_CR, XAL.ENTERED_CR),
XAL.ENTERED_CR)/XRUR.denominator_rate)*
XRUR.numerator_rate),l_sec_alc_precision),
ROUND(((DECODE(l_conversion_option, 'D',
NVL(XAL.ACCOUNTED_CR, XAL.ENTERED_CR),
XAL.ENTERED_CR)/XRUR.denominator_rate)*
XRUR.numerator_rate)/l_sec_alc_mau)*l_sec_alc_mau))
, XAL.DESCRIPTION
, XAL.STATISTICAL_AMOUNT
, XAL.CURRENCY_CODE
, DECODE(XAL.currency_code,l_sec_alc_currency_code,
null,
DECODE(l_conversion_option, 'D',
NVL(XAL.CURRENCY_CONVERSION_DATE, XAL.ACCOUNTING_DATE),
l_currency_conversion_date)) -- CURRENCY_CONVERSION_DATE
, DECODE(XAL.currency_code,l_sec_alc_currency_code,
null,
DECODE(l_conversion_option, 'D',
NVL(XAL.currency_conversion_rate,1)*XRUR.conversion_rate,
XRUR.conversion_rate)) -- CURRENCY_CONVERSION_RATE
, DECODE(XAL.currency_code,l_sec_alc_currency_code,
null,
DECODE(l_conversion_option, 'D',
NVL(XAL.CURRENCY_CONVERSION_TYPE, 'EMU FIXED'),
l_currency_conversion_type)) --CURRENCY_CONVERSION_TYPE
, NULL --XAL.USSGL_TRANSACTION_CODE
, XAL.JGZZ_RECON_REF
, NULL -- XAL.CONTROL_BALANCE_FLAG
, XAL.ANALYTICAL_BALANCE_FLAG
, XAL.ATTRIBUTE_CATEGORY
, 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.GL_SL_LINK_TABLE
, XAL.DISPLAYED_LINE_NUMBER
, SYSDATE
, fnd_global.user_id
, SYSDATE
, fnd_global.user_id
, fnd_global.login_id
, SYSDATE
, -1 --G_PGM_APPL_ID
, -1 --G_PROGRAM_ID
, l_upgrade_id
, XAH.UPG_BATCH_ID --XAL.UPG_BATCH_ID original header in xla_ae_headers
, XAL.UPG_TAX_REFERENCE_ID1
, XAL.UPG_TAX_REFERENCE_ID2
, XAL.UPG_TAX_REFERENCE_ID3
, DECODE(XAL.currency_code,l_sec_alc_currency_code,
UNROUNDED_ENTERED_DR,
DECODE(l_sec_alc_mau,null,
(((DECODE(l_conversion_option, 'D',
NVL(XAL.UNROUNDED_ACCOUNTED_DR, XAL.UNROUNDED_ENTERED_DR),
XAL.UNROUNDED_ENTERED_DR)/XRUR.denominator_rate)*
XRUR.numerator_rate)),
(((DECODE(l_conversion_option, 'D',
NVL(XAL.UNROUNDED_ACCOUNTED_DR, XAL.UNROUNDED_ENTERED_DR),
XAL.UNROUNDED_ENTERED_DR)/XRUR.denominator_rate)*
XRUR.numerator_rate)/l_sec_alc_mau)*l_sec_alc_mau)) --UNROUNDED_ACCOUNTED_DR
, DECODE(XAL.currency_code,l_sec_alc_currency_code,
UNROUNDED_ENTERED_CR,
DECODE(l_sec_alc_mau,null,
(((DECODE(l_conversion_option, 'D',
NVL(XAL.UNROUNDED_ACCOUNTED_CR, XAL.UNROUNDED_ENTERED_CR),
XAL.UNROUNDED_ENTERED_CR)/XRUR.denominator_rate)*
XRUR.numerator_rate)),
(((DECODE(l_conversion_option, 'D',
NVL(XAL.UNROUNDED_ACCOUNTED_CR, XAL.UNROUNDED_ENTERED_CR),
XAL.UNROUNDED_ENTERED_CR)/XRUR.denominator_rate)*
XRUR.numerator_rate)/l_sec_alc_mau)*l_sec_alc_mau)) --UNROUNDED_ACCOUNTED_CR
, XAL.GAIN_OR_LOSS_FLAG
, XAL.UNROUNDED_ENTERED_DR
, XAL.UNROUNDED_ENTERED_CR
, NULL -- XAL.SUBSTITUTED_CCID
, XAL.BUSINESS_CLASS_CODE
, XAL.MPA_ACCRUAL_ENTRY_FLAG
, XAL.ENCUMBRANCE_TYPE_ID
, XAL.FUNDS_STATUS_CODE
, NULL --XAL.MERGE_CODE_COMBINATION_ID
, XAL.MERGE_PARTY_ID
, XAL.MERGE_PARTY_SITE_ID
, XAL.ACCOUNTING_DATE
, l_secondary_alc_ledger_id
, XAL.SOURCE_TABLE
, XAL.SOURCE_ID
, XAL.ACCOUNT_OVERLAY_SOURCE_ID
FROM XLA_AE_HEADERS XAH
,XLA_AE_LINES XAL
,XLA_RC_UPGRADE_RATES XRUR
WHERE
XAH.ledger_id = l_secondary_alc_ledger_id
AND XAL.ledger_id = l_primary_ledger_id
AND XAH.upg_batch_id = XAL.ae_header_id
AND XRUR.relationship_id = l_relationship_id
AND XAH.accounting_date >= l_start_date
AND XAH.accounting_date <= l_sec_alc_end_date
AND XAL.gain_or_loss_flag <> 'Y'
AND XRUR.from_currency = DECODE(l_conversion_option, 'D',
l_primary_currency_code,
XAL.currency_code)
AND XRUR.upgrade_run_id = l_upgrade_id
AND XRUR.to_currency = l_sec_alc_currency_code
AND XAH.application_id = XAL.application_id;
(p_msg => 'Rows inserted into xla_ae_lines: ' || SQL%ROWCOUNT
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
l_lines_insert := TRUE;
SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
INTO l_sql_time
FROM dual;
(p_msg => 'xla_ae_lines insert SQL end: ' || l_sql_time
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
(p_msg => 'inside alc lines insert. SQL error msg = ' || l_error_text
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
PROCEDURE secondary_insert
IS
l_log_module VARCHAR2(240);
l_log_module := C_DEFAULT_MODULE||'.secondary_insert';
SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
INTO l_sql_time
FROM dual;
(p_msg => 'xla_ae_headers insert SQL start: ' || l_sql_time
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
INSERT /*+ append */ INTO XLA_AE_HEADERS
(
AE_HEADER_ID
, APPLICATION_ID
, LEDGER_ID
, ENTITY_ID
, EVENT_ID
, EVENT_TYPE_CODE
, ACCOUNTING_DATE
, GL_TRANSFER_STATUS_CODE
, GL_TRANSFER_DATE
, JE_CATEGORY_NAME
, ACCOUNTING_ENTRY_STATUS_CODE
, ACCOUNTING_ENTRY_TYPE_CODE
, AMB_CONTEXT_CODE
, PRODUCT_RULE_TYPE_CODE
, PRODUCT_RULE_CODE
, PRODUCT_RULE_VERSION
, DESCRIPTION
, DOC_SEQUENCE_ID
, DOC_SEQUENCE_VALUE
, ACCOUNTING_BATCH_ID
, COMPLETION_ACCT_SEQ_VERSION_ID
, CLOSE_ACCT_SEQ_VERSION_ID
, COMPLETION_ACCT_SEQ_VALUE
, CLOSE_ACCT_SEQ_VALUE
, BUDGET_VERSION_ID
, FUNDS_STATUS_CODE
, ENCUMBRANCE_TYPE_ID
, BALANCE_TYPE_CODE
, REFERENCE_DATE
, COMPLETED_DATE
, PERIOD_NAME
, PACKET_ID
, COMPLETION_ACCT_SEQ_ASSIGN_ID
, CLOSE_ACCT_SEQ_ASSIGN_ID
, DOC_CATEGORY_CODE
, ATTRIBUTE_CATEGORY
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, GROUP_ID
, DOC_SEQUENCE_VERSION_ID
, DOC_SEQUENCE_ASSIGN_ID
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, PROGRAM_UPDATE_DATE
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, REQUEST_ID
, UPG_BATCH_ID
, UPG_SOURCE_APPLICATION_ID
, UPG_VALID_FLAG
, ZERO_AMOUNT_FLAG
, PARENT_AE_HEADER_ID
, PARENT_AE_LINE_NUM
, ACCRUAL_REVERSAL_FLAG
, MERGE_EVENT_ID )
SELECT /*+ parallel(xah) */
XLA_AE_HEADERS_S.nextval
, XAH.APPLICATION_ID
, l_secondary_alc_ledger_id
, XAH.ENTITY_ID
, XAH.EVENT_ID
, XAH.EVENT_TYPE_CODE
, XAH.ACCOUNTING_DATE
, XAH.GL_TRANSFER_STATUS_CODE
, XAH.GL_TRANSFER_DATE
, XAH.JE_CATEGORY_NAME
, XAH.ACCOUNTING_ENTRY_STATUS_CODE
, XAH.ACCOUNTING_ENTRY_TYPE_CODE
, XAH.AMB_CONTEXT_CODE
, XAH.PRODUCT_RULE_TYPE_CODE
, XAH.PRODUCT_RULE_CODE
, XAH.PRODUCT_RULE_VERSION
, XAH.DESCRIPTION ---??????
, XAH.DOC_SEQUENCE_ID
, XAH.DOC_SEQUENCE_VALUE
, NULL --XAH.ACCOUNTING_BATCH_ID
, NULL --XAH.COMPLETION_ACCT_SEQ_VERSION_ID
, NULL --XAH.CLOSE_ACCT_SEQ_VERSION_ID
, NULL --XAH.COMPLETION_ACCT_SEQ_VALUE
, NULL --XAH.CLOSE_ACCT_SEQ_VALUE
, XAH.BUDGET_VERSION_ID
, XAH.FUNDS_STATUS_CODE
, XAH.ENCUMBRANCE_TYPE_ID
, XAH.BALANCE_TYPE_CODE
, XAH.REFERENCE_DATE
, XAH.COMPLETED_DATE
, XAH.PERIOD_NAME
, XAH.PACKET_ID
, NULL -- XAH.COMPLETION_ACCT_SEQ_ASSIGN_ID
, NULL --XAH.CLOSE_ACCT_SEQ_ASSIGN_ID
, XAH.DOC_CATEGORY_CODE
, 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
, GROUP_ID --??????
, XAH.DOC_SEQUENCE_VERSION_ID
, XAH.DOC_SEQUENCE_ASSIGN_ID
, SYSDATE
, fnd_global.user_id
, SYSDATE
, fnd_global.user_id
, fnd_global.login_id
, SYSDATE
, -1 --G_PGM_APPL_ID
, -1 --G_PROGRAM_ID
, l_upgrade_id
, XAH.AE_HEADER_ID -- UPG_BATCH_ID = Header ID of parent to be used during lines insertion
, -602 -- XAH.UPG_SOURCE_APPLICATION_ID
, XAH.UPG_VALID_FLAG
, XAH.ZERO_AMOUNT_FLAG
, NULL -- No need to populate this for historical transactions
, NULL -- No need to populate this for historical transactions
, XAH.ACCRUAL_REVERSAL_FLAG
, XAH.MERGE_EVENT_ID
FROM XLA_AE_HEADERS XAH
WHERE
XAH.accounting_date >= l_start_date
AND XAH.accounting_date <= l_sec_alc_end_date
AND XAH.ledger_id = l_primary_ledger_id
AND XAH.accounting_entry_status_code = 'F'
AND XAH.balance_type_code in ('A', 'E');
(p_msg => 'Rows inserted into xla_ae_headers: ' || SQL%ROWCOUNT
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
l_headers_insert := TRUE;
SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
INTO l_sql_time
FROM dual;
(p_msg => 'xla_ae_headers insert SQL end and xla_ae_lines SQL start: ' || l_sql_time
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
INSERT /*+ append */ INTO XLA_AE_LINES
( AE_HEADER_ID
, AE_LINE_NUM
, APPLICATION_ID
, CODE_COMBINATION_ID
, GL_TRANSFER_MODE_CODE
, GL_SL_LINK_ID
, ACCOUNTING_CLASS_CODE
, PARTY_ID
, PARTY_SITE_ID
, PARTY_TYPE_CODE
, ENTERED_DR
, ENTERED_CR
, ACCOUNTED_DR
, ACCOUNTED_CR
, DESCRIPTION
, STATISTICAL_AMOUNT
, CURRENCY_CODE
, CURRENCY_CONVERSION_DATE
, CURRENCY_CONVERSION_RATE
, CURRENCY_CONVERSION_TYPE
, USSGL_TRANSACTION_CODE
, JGZZ_RECON_REF
, CONTROL_BALANCE_FLAG
, ANALYTICAL_BALANCE_FLAG
, ATTRIBUTE_CATEGORY
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, GL_SL_LINK_TABLE
, DISPLAYED_LINE_NUMBER
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, PROGRAM_UPDATE_DATE
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, REQUEST_ID
, UPG_BATCH_ID
, UPG_TAX_REFERENCE_ID1
, UPG_TAX_REFERENCE_ID2
, UPG_TAX_REFERENCE_ID3
, UNROUNDED_ACCOUNTED_DR
, UNROUNDED_ACCOUNTED_CR
, GAIN_OR_LOSS_FLAG
, UNROUNDED_ENTERED_DR
, UNROUNDED_ENTERED_CR
, SUBSTITUTED_CCID
, BUSINESS_CLASS_CODE
, MPA_ACCRUAL_ENTRY_FLAG
, ENCUMBRANCE_TYPE_ID
, FUNDS_STATUS_CODE
, MERGE_CODE_COMBINATION_ID
, MERGE_PARTY_ID
, MERGE_PARTY_SITE_ID
, ACCOUNTING_DATE
, LEDGER_ID
, SOURCE_TABLE
, SOURCE_ID
, ACCOUNT_OVERLAY_SOURCE_ID )
SELECT /*+ parallel(xah) parallel (xal) */
XAH.AE_HEADER_ID
, XAL.AE_LINE_NUM
, XAL.APPLICATION_ID
, XAL.CODE_COMBINATION_ID
, XAL.GL_TRANSFER_MODE_CODE
, NULL --XAL.GL_SL_LINK_ID
, XAL.ACCOUNTING_CLASS_CODE
, XAL.PARTY_ID
, XAL.PARTY_SITE_ID
, XAL.PARTY_TYPE_CODE
, XAL.ENTERED_DR
, XAL.ENTERED_CR
, DECODE(XAL.currency_code,l_sec_alc_currency_code,
ENTERED_DR,
DECODE(l_sec_alc_mau,null,
ROUND(((DECODE(l_conversion_option, 'D',
NVL(XAL.ACCOUNTED_DR, XAL.ENTERED_DR),
XAL.ENTERED_DR)/XRUR.denominator_rate)*
XRUR.numerator_rate),l_sec_alc_precision),
ROUND(((DECODE(l_conversion_option, 'D',
NVL(XAL.ACCOUNTED_DR, XAL.ENTERED_DR),
XAL.ENTERED_DR)/XRUR.denominator_rate)*
XRUR.numerator_rate)/l_sec_alc_mau)*l_sec_alc_mau))
, DECODE(XAL.currency_code,l_sec_alc_currency_code,
ENTERED_CR,
DECODE(l_sec_alc_mau,null,
ROUND(((DECODE(l_conversion_option, 'D',
NVL(XAL.ACCOUNTED_CR, XAL.ENTERED_CR),
XAL.ENTERED_CR)/XRUR.denominator_rate)*
XRUR.numerator_rate),l_sec_alc_precision),
ROUND(((DECODE(l_conversion_option, 'D',
NVL(XAL.ACCOUNTED_CR, XAL.ENTERED_CR),
XAL.ENTERED_CR)/XRUR.denominator_rate)*
XRUR.numerator_rate)/l_sec_alc_mau)*l_sec_alc_mau))
, XAL.DESCRIPTION
, XAL.STATISTICAL_AMOUNT
, XAL.CURRENCY_CODE
, DECODE(XAL.currency_code,l_sec_alc_currency_code,
null,
DECODE(l_conversion_option, 'D',
NVL(XAL.CURRENCY_CONVERSION_DATE, XAL.ACCOUNTING_DATE),
l_currency_conversion_date)) -- CURRENCY_CONVERSION_DATE
, DECODE(XAL.currency_code,l_sec_alc_currency_code,
null,
DECODE(l_conversion_option, 'D',
NVL(XAL.currency_conversion_rate,1)*XRUR.conversion_rate,
XRUR.conversion_rate)) -- CURRENCY_CONVERSION_RATE
, DECODE(XAL.currency_code,l_sec_alc_currency_code,
null,
DECODE(l_conversion_option, 'D',
NVL(XAL.CURRENCY_CONVERSION_TYPE, 'EMU FIXED'),
l_currency_conversion_type)) --CURRENCY_CONVERSION_TYPE
, NULL --XAL.USSGL_TRANSACTION_CODE
, XAL.JGZZ_RECON_REF
, NULL --XAL.CONTROL_BALANCE_FLAG
, XAL.ANALYTICAL_BALANCE_FLAG
, XAL.ATTRIBUTE_CATEGORY
, 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.GL_SL_LINK_TABLE
, XAL.DISPLAYED_LINE_NUMBER
, SYSDATE
, fnd_global.user_id
, SYSDATE
, fnd_global.user_id
, fnd_global.login_id
, SYSDATE
, -1 --G_PGM_APPL_ID
, -1 --G_PROGRAM_ID
, l_upgrade_id
, XAH.UPG_BATCH_ID --XAL.UPG_BATCH_ID original header in xla_ae_headers
, XAL.UPG_TAX_REFERENCE_ID1
, XAL.UPG_TAX_REFERENCE_ID2
, XAL.UPG_TAX_REFERENCE_ID3
, DECODE(XAL.currency_code,l_sec_alc_currency_code,
UNROUNDED_ENTERED_DR,
DECODE(l_sec_alc_mau,null,
(((DECODE(l_conversion_option, 'D',
NVL(XAL.UNROUNDED_ACCOUNTED_DR, XAL.UNROUNDED_ENTERED_DR),
XAL.UNROUNDED_ENTERED_DR)/XRUR.denominator_rate)*
XRUR.numerator_rate)),
(((DECODE(l_conversion_option, 'D',
NVL(XAL.UNROUNDED_ACCOUNTED_DR, XAL.UNROUNDED_ENTERED_DR),
XAL.UNROUNDED_ENTERED_DR)/XRUR.denominator_rate)*
XRUR.numerator_rate)/l_sec_alc_mau)*l_sec_alc_mau)) --UNROUNDED_ACCOUNTED_DR
, DECODE(XAL.currency_code,l_sec_alc_currency_code,
UNROUNDED_ENTERED_CR,
DECODE(l_sec_alc_mau,null,
(((DECODE(l_conversion_option, 'D',
NVL(XAL.UNROUNDED_ACCOUNTED_CR, XAL.UNROUNDED_ENTERED_CR),
XAL.UNROUNDED_ENTERED_CR)/XRUR.denominator_rate)*
XRUR.numerator_rate)),
(((DECODE(l_conversion_option, 'D',
NVL(XAL.UNROUNDED_ACCOUNTED_CR, XAL.UNROUNDED_ENTERED_CR),
XAL.UNROUNDED_ENTERED_CR)/XRUR.denominator_rate)*
XRUR.numerator_rate)/l_sec_alc_mau)*l_sec_alc_mau)) --UNROUNDED_ACCOUNTED_CR
, XAL.GAIN_OR_LOSS_FLAG
, XAL.UNROUNDED_ENTERED_DR
, XAL.UNROUNDED_ENTERED_CR
, NULL -- XAL.SUBSTITUTED_CCID
, XAL.BUSINESS_CLASS_CODE
, XAL.MPA_ACCRUAL_ENTRY_FLAG
, XAL.ENCUMBRANCE_TYPE_ID
, XAL.FUNDS_STATUS_CODE
, NULL --XAL.MERGE_CODE_COMBINATION_ID
, XAL.MERGE_PARTY_ID
, XAL.MERGE_PARTY_SITE_ID
, XAL.ACCOUNTING_DATE
, l_secondary_alc_ledger_id
, XAL.SOURCE_TABLE
, XAL.SOURCE_ID
, XAL.ACCOUNT_OVERLAY_SOURCE_ID
FROM XLA_AE_HEADERS XAH
,XLA_AE_LINES XAL
,XLA_RC_UPGRADE_RATES XRUR
WHERE
XAH.ledger_id = l_secondary_alc_ledger_id
AND XAL.ledger_id = l_primary_ledger_id
AND XAH.upg_batch_id = XAL.ae_header_id
AND XRUR.relationship_id = l_relationship_id
AND XAH.accounting_date >= l_start_date
AND XAH.accounting_date <= l_sec_alc_end_date
AND XAL.gain_or_loss_flag <> 'Y'
AND XRUR.from_currency = DECODE(l_conversion_option, 'D',
l_primary_currency_code,
XAL.currency_code)
AND XRUR.upgrade_run_id = l_upgrade_id
AND XRUR.to_currency = l_sec_alc_currency_code
AND XAH.application_id = XAL.application_id;
(p_msg => 'Rows inserted into xla_ae_lines: ' || SQL%ROWCOUNT
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
l_lines_insert := TRUE;
SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
INTO l_sql_time
FROM dual;
(p_msg => 'xla_ae_lines insert SQL end: ' || l_sql_time
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
(p_msg => 'inside secondary lines insert. SQL error msg = ' || l_error_text
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
l_dynamic_inserts VARCHAR2(10);
SELECT from_ccid
FROM gl_accts_map_int_gt
WHERE coa_mapping_id = l_mapping_relationship_id
AND to_ccid IS NULL;
SELECT dynamic_inserts_allowed_flag
INTO l_dynamic_inserts
FROM fnd_id_flex_structures_vl
WHERE application_id = 101
AND id_flex_code = 'GL#'
AND id_flex_num = l_primary_coa; -- why is this flag for primary???
DELETE FROM gl_accts_map_int_gt; -- bug 4564062
SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
INTO l_sql_time
FROM dual;
INSERT INTO gl_accts_map_int_gt(coa_mapping_id, from_ccid)
SELECT distinct l_mapping_relationship_id, code_combination_id
FROM xla_ae_lines xal
WHERE XAL.gain_or_loss_flag <> 'Y'
AND EXISTS (SELECT 1
FROM xla_ae_headers xah
WHERE xah.ae_header_id = xal.ae_header_id
AND XAH.accounting_date >= l_start_date
AND XAH.accounting_date <= l_sec_alc_end_date
AND XAH.ledger_id = l_primary_ledger_id
AND XAH.accounting_entry_status_code = 'F'
AND XAH.balance_type_code in ('A', 'E')
);
(p_msg => 'Rows (distinct ccids) inserted into gl_accts_map_int_gt = ' || SQL%ROWCOUNT
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
INTO l_sql_time
FROM dual;
,create_ccid => (NVL(l_dynamic_inserts,'N') ='Y' )
,debug => g_log_enabled);
SELECT 1
INTO l_ccid_check
FROM DUAL
WHERE EXISTS (SELECT 1
FROM gl_accts_map_int_gt
WHERE coa_mapping_id = l_mapping_relationship_id
AND to_ccid IS NULL);
SELECT from_ccid
FROM gl_accts_map_int_gt
WHERE coa_mapping_id = l_mapping_relationship_id
AND to_ccid IS NULL;
l_dynamic_inserts VARCHAR2(10);
SELECT dynamic_inserts_allowed_flag
INTO l_dynamic_inserts
FROM fnd_id_flex_structures_vl
WHERE application_id = 101
AND id_flex_code = 'GL#'
AND id_flex_num = l_primary_coa; -- why is this flag for primary???
DELETE FROM gl_accts_map_int_gt; -- bug 4564062
INSERT INTO gl_accts_map_int_gt(coa_mapping_id, from_ccid)
SELECT distinct l_mapping_relationship_id, code_combination_id
FROM xla_ae_lines
WHERE request_id = l_upgrade_id;
(p_msg => 'Rows (distinct ccids) inserted into gl_accts_map_int_gt = ' || SQL%ROWCOUNT
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
,create_ccid => (NVL(l_dynamic_inserts,'N') ='Y' )
,debug => g_log_enabled);
SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
INTO l_sql_time
FROM dual;
(p_msg => 'ccid update SQL start: ' || l_sql_time
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
UPDATE xla_ae_lines xal
SET code_combination_id = (SELECT (nvl(gl_int.to_ccid, -1))
FROM gl_accts_map_int_gt gl_int
WHERE xal.code_combination_id = gl_int.from_ccid
AND gl_int.coa_mapping_id = l_mapping_relationship_id)
WHERE xal.request_id = l_upgrade_id;
(p_msg => 'Rows in lines updated with new ccid = ' || SQL%ROWCOUNT
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
INTO l_sql_time
FROM dual;
(p_msg => 'ccid update SQL end: ' || l_sql_time
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
SELECT 1
INTO l_ccid_check
FROM DUAL
WHERE EXISTS (SELECT 1
FROM xla_ae_lines
WHERE code_combination_id = -1
AND request_id = l_upgrade_id);
SELECT period_set_name, accounted_period_type
INTO l_primary_cal_set_name, l_primary_cal_per_type
FROM gl_ledgers
WHERE ledger_id = l_primary_ledger_id;
SELECT period_set_name, accounted_period_type
INTO l_primary_sec_alc_set_name, l_primary_sec_alc_per_type
FROM gl_ledgers
WHERE ledger_id = l_secondary_alc_ledger_id;
SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
INTO l_sql_time
FROM dual;
UPDATE xla_ae_headers xah
SET period_name = (SELECT gls.period_name
FROM gl_period_statuses gls
WHERE gls.ledger_id = l_secondary_alc_ledger_id
AND gls.application_id = 101
AND gls.adjustment_period_flag = 'N'
AND xah.accounting_date BETWEEN gls.start_date AND gls.end_date
)
WHERE xah.ledger_id = l_secondary_alc_ledger_id
AND xah.request_id = l_upgrade_id;
(p_msg => 'Rows updated in headers with new calendar period = ' || SQL%ROWCOUNT
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
INTO l_sql_time
FROM dual;
SELECT distinct ae_header_id
FROM xla_ae_headers XAH
WHERE XAH.accounting_date >= l_start_date
AND XAH.accounting_date <= l_sec_alc_end_date
AND XAH.ledger_id = l_primary_ledger_id
AND XAH.accounting_entry_status_code = 'F'
AND XAH.balance_type_code in ('A', 'E')
AND EXISTS (SELECT 1
FROM xla_ae_lines xal
WHERE xal.ae_header_id = xah.ae_header_id
AND xal.gain_or_loss_flag = 'Y'
AND xah.application_id = xal.application_id);
SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
INTO l_sql_time
FROM dual;
UPDATE xla_ae_lines xx
SET displayed_line_number = (SELECT new_line from
(SELECT ae_header_id,
decode(nvl(accounted_cr, 0) + nvl(accounted_dr, 0), 0, -1, 1) *
(ROW_NUMBER() over (PARTITION BY ae_header_id order by
DECODE(SIGN(abs(nvl(accounted_dr, 0)) - abs(nvl(accounted_cr, 0))), 1, 3, -1, 2, 0) desc,
abs(nvl(accounted_dr, 0) + nvl(accounted_cr, 0)) desc,
SIGN(nvl(accounted_dr, 0) + nvl(accounted_cr, 0)) desc)) new_line,
ae_line_num,
displayed_line_number
FROM xla_ae_lines
WHERE request_id = l_upgrade_id
AND upg_batch_id = l_array_gain_loss_header(i)
) yy
WHERE yy.ae_header_id = xx.ae_header_id
AND yy.ae_line_num = xx.ae_line_num)
WHERE request_id = l_upgrade_id
AND upg_batch_id = l_array_gain_loss_header(i);
(p_msg => 'Rows updated in lines with new sequence number, only gain loss headers updated = ' || l_array_gain_loss_header.COUNT
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
INTO l_sql_time
FROM dual;
SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
INTO l_sql_time
FROM dual;
PROCEDURE insert_links_segments
IS
l_log_module VARCHAR2(240);
l_log_module := C_DEFAULT_MODULE||'.insert_links_segments';
SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
INTO l_sql_time
FROM dual;
INSERT /*+ append */ INTO XLA_DISTRIBUTION_LINKS
(
APPLICATION_ID
, EVENT_ID
, AE_HEADER_ID
, AE_LINE_NUM
, SOURCE_DISTRIBUTION_TYPE
, SOURCE_DISTRIBUTION_ID_CHAR_1
, SOURCE_DISTRIBUTION_ID_CHAR_2
, SOURCE_DISTRIBUTION_ID_CHAR_3
, SOURCE_DISTRIBUTION_ID_CHAR_4
, SOURCE_DISTRIBUTION_ID_CHAR_5
, SOURCE_DISTRIBUTION_ID_NUM_1
, SOURCE_DISTRIBUTION_ID_NUM_2
, SOURCE_DISTRIBUTION_ID_NUM_3
, SOURCE_DISTRIBUTION_ID_NUM_4
, SOURCE_DISTRIBUTION_ID_NUM_5
, TAX_LINE_REF_ID
, TAX_SUMMARY_LINE_REF_ID
, TAX_REC_NREC_DIST_REF_ID
, STATISTICAL_AMOUNT
, REF_AE_HEADER_ID
, REF_TEMP_LINE_NUM
, ACCOUNTING_LINE_CODE
, ACCOUNTING_LINE_TYPE_CODE
, MERGE_DUPLICATE_CODE
, TEMP_LINE_NUM
, REF_EVENT_ID
, LINE_DEFINITION_OWNER_CODE
, LINE_DEFINITION_CODE
, EVENT_CLASS_CODE
, EVENT_TYPE_CODE
, UPG_BATCH_ID
, CALCULATE_ACCTD_AMTS_FLAG
, CALCULATE_G_L_AMTS_FLAG
, ROUNDING_CLASS_CODE
, DOCUMENT_ROUNDING_LEVEL
, UNROUNDED_ENTERED_DR
, UNROUNDED_ENTERED_CR
, DOC_ROUNDING_ENTERED_AMT
, DOC_ROUNDING_ACCTD_AMT
, UNROUNDED_ACCOUNTED_CR
, UNROUNDED_ACCOUNTED_DR
, APPLIED_TO_APPLICATION_ID
, APPLIED_TO_ENTITY_CODE
, APPLIED_TO_ENTITY_ID
, APPLIED_TO_SOURCE_ID_NUM_1
, APPLIED_TO_SOURCE_ID_NUM_2
, APPLIED_TO_SOURCE_ID_NUM_3
, APPLIED_TO_SOURCE_ID_NUM_4
, APPLIED_TO_SOURCE_ID_CHAR_1
, APPLIED_TO_SOURCE_ID_CHAR_2
, APPLIED_TO_SOURCE_ID_CHAR_3
, APPLIED_TO_SOURCE_ID_CHAR_4
, APPLIED_TO_DISTRIBUTION_TYPE
, APPLIED_TO_DIST_ID_NUM_1
, APPLIED_TO_DIST_ID_NUM_2
, APPLIED_TO_DIST_ID_NUM_3
, APPLIED_TO_DIST_ID_NUM_4
, APPLIED_TO_DIST_ID_NUM_5
, APPLIED_TO_DIST_ID_CHAR_1
, APPLIED_TO_DIST_ID_CHAR_2
, APPLIED_TO_DIST_ID_CHAR_3
, APPLIED_TO_DIST_ID_CHAR_4
, APPLIED_TO_DIST_ID_CHAR_5
, ALLOC_TO_APPLICATION_ID
, ALLOC_TO_ENTITY_CODE
, ALLOC_TO_SOURCE_ID_NUM_1
, ALLOC_TO_SOURCE_ID_NUM_2
, ALLOC_TO_SOURCE_ID_NUM_3
, ALLOC_TO_SOURCE_ID_NUM_4
, ALLOC_TO_SOURCE_ID_CHAR_1
, ALLOC_TO_SOURCE_ID_CHAR_2
, ALLOC_TO_SOURCE_ID_CHAR_3
, ALLOC_TO_SOURCE_ID_CHAR_4
, ALLOC_TO_DISTRIBUTION_TYPE
, ALLOC_TO_DIST_ID_NUM_1
, ALLOC_TO_DIST_ID_NUM_2
, ALLOC_TO_DIST_ID_NUM_3
, ALLOC_TO_DIST_ID_NUM_4
, ALLOC_TO_DIST_ID_NUM_5
, ALLOC_TO_DIST_ID_CHAR_1
, ALLOC_TO_DIST_ID_CHAR_2
, ALLOC_TO_DIST_ID_CHAR_3
, ALLOC_TO_DIST_ID_CHAR_4
, ALLOC_TO_DIST_ID_CHAR_5
, GAIN_OR_LOSS_REF)
SELECT /*+ parallel(xal) parallel(xdl) */
XDL.APPLICATION_ID
, XDL.EVENT_ID
, XAL.AE_HEADER_ID
, XDL.AE_LINE_NUM
, XDL.SOURCE_DISTRIBUTION_TYPE
, XDL.SOURCE_DISTRIBUTION_ID_CHAR_1
, XDL.SOURCE_DISTRIBUTION_ID_CHAR_2
, XDL.SOURCE_DISTRIBUTION_ID_CHAR_3
, XDL.SOURCE_DISTRIBUTION_ID_CHAR_4
, XDL.SOURCE_DISTRIBUTION_ID_CHAR_5
, XDL.SOURCE_DISTRIBUTION_ID_NUM_1
, XDL.SOURCE_DISTRIBUTION_ID_NUM_2
, XDL.SOURCE_DISTRIBUTION_ID_NUM_3
, XDL.SOURCE_DISTRIBUTION_ID_NUM_4
, XDL.SOURCE_DISTRIBUTION_ID_NUM_5
, XDL.TAX_LINE_REF_ID
, XDL.TAX_SUMMARY_LINE_REF_ID
, XDL.TAX_REC_NREC_DIST_REF_ID
, XDL.STATISTICAL_AMOUNT
, decode(XDL.AE_HEADER_ID, XDL.REF_AE_HEADER_ID, XAL.AE_HEADER_ID, -1*XDL.REF_AE_HEADER_ID)
, null -- XDL.REF_TEMP_LINE_NUM What should be inserted
, XDL.ACCOUNTING_LINE_CODE
, XDL.ACCOUNTING_LINE_TYPE_CODE
, XDL.MERGE_DUPLICATE_CODE
, XDL.TEMP_LINE_NUM
, XDL.REF_EVENT_ID
, XDL.LINE_DEFINITION_OWNER_CODE
, XDL.LINE_DEFINITION_CODE
, XDL.EVENT_CLASS_CODE
, XDL.EVENT_TYPE_CODE
, XAL.UPG_BATCH_ID -- original header in xla_ae_headers
, XDL.CALCULATE_ACCTD_AMTS_FLAG
, XDL.CALCULATE_G_L_AMTS_FLAG
, XDL.ROUNDING_CLASS_CODE
, XDL.DOCUMENT_ROUNDING_LEVEL
, XAL.UNROUNDED_ENTERED_DR -- retrieved from lines
, XAL.UNROUNDED_ENTERED_CR -- retrieved from lines
, XDL.DOC_ROUNDING_ENTERED_AMT
, XDL.DOC_ROUNDING_ACCTD_AMT
, XAL.UNROUNDED_ACCOUNTED_CR -- retrieved from lines
, XAL.UNROUNDED_ACCOUNTED_DR -- retrieved from lines
, XDL.APPLIED_TO_APPLICATION_ID
, XDL.APPLIED_TO_ENTITY_CODE
, XDL.APPLIED_TO_ENTITY_ID
, XDL.APPLIED_TO_SOURCE_ID_NUM_1
, XDL.APPLIED_TO_SOURCE_ID_NUM_2
, XDL.APPLIED_TO_SOURCE_ID_NUM_3
, XDL.APPLIED_TO_SOURCE_ID_NUM_4
, XDL.APPLIED_TO_SOURCE_ID_CHAR_1
, XDL.APPLIED_TO_SOURCE_ID_CHAR_2
, XDL.APPLIED_TO_SOURCE_ID_CHAR_3
, XDL.APPLIED_TO_SOURCE_ID_CHAR_4
, XDL.APPLIED_TO_DISTRIBUTION_TYPE
, XDL.APPLIED_TO_DIST_ID_NUM_1
, XDL.APPLIED_TO_DIST_ID_NUM_2
, XDL.APPLIED_TO_DIST_ID_NUM_3
, XDL.APPLIED_TO_DIST_ID_NUM_4
, XDL.APPLIED_TO_DIST_ID_NUM_5
, XDL.APPLIED_TO_DIST_ID_CHAR_1
, XDL.APPLIED_TO_DIST_ID_CHAR_2
, XDL.APPLIED_TO_DIST_ID_CHAR_3
, XDL.APPLIED_TO_DIST_ID_CHAR_4
, XDL.APPLIED_TO_DIST_ID_CHAR_5
, XDL.ALLOC_TO_APPLICATION_ID
, XDL.ALLOC_TO_ENTITY_CODE
, XDL.ALLOC_TO_SOURCE_ID_NUM_1
, XDL.ALLOC_TO_SOURCE_ID_NUM_2
, XDL.ALLOC_TO_SOURCE_ID_NUM_3
, XDL.ALLOC_TO_SOURCE_ID_NUM_4
, XDL.ALLOC_TO_SOURCE_ID_CHAR_1
, XDL.ALLOC_TO_SOURCE_ID_CHAR_2
, XDL.ALLOC_TO_SOURCE_ID_CHAR_3
, XDL.ALLOC_TO_SOURCE_ID_CHAR_4
, XDL.ALLOC_TO_DISTRIBUTION_TYPE
, XDL.ALLOC_TO_DIST_ID_NUM_1
, XDL.ALLOC_TO_DIST_ID_NUM_2
, XDL.ALLOC_TO_DIST_ID_NUM_3
, XDL.ALLOC_TO_DIST_ID_NUM_4
, XDL.ALLOC_TO_DIST_ID_NUM_5
, XDL.ALLOC_TO_DIST_ID_CHAR_1
, XDL.ALLOC_TO_DIST_ID_CHAR_2
, XDL.ALLOC_TO_DIST_ID_CHAR_3
, XDL.ALLOC_TO_DIST_ID_CHAR_4
, XDL.ALLOC_TO_DIST_ID_CHAR_5
, l_upgrade_id -- concurrent request id stored in column XDL.GAIN_OR_LOSS_REF
FROM XLA_DISTRIBUTION_LINKS XDL
,XLA_AE_LINES XAL
WHERE
XAL.upg_batch_id = XDL.ae_header_id
AND XAL.ledger_id = l_secondary_alc_ledger_id
AND XAL.accounting_date >= l_start_date
AND XAL.accounting_date <= l_sec_alc_end_date
AND XAL.gain_or_loss_flag <> 'Y'
AND XAL.ae_line_num = XDL.ae_line_num
AND XAL.application_id = XDL.application_id;
(p_msg => 'Rows inserted into xla_distribution_links = ' || SQL%ROWCOUNT
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
INTO l_sql_time
FROM dual;
(p_msg => 'distribution links insert SQL end, segment insert start (if applicable): ' || l_sql_time
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
l_links_insert := TRUE;
-- section commented bug 7201652. update can be used in post script if needed. query for some cases
-- still throws cannot update to null error. ref_ae_header should not have an impact on reversal/bflow
-- since joins are not based on this column
UPDATE xla_distribution_links xdl
SET ref_ae_header_id = (SELECT ae_header_id
FROM xla_ae_headers xah
WHERE xah.upg_batch_id = -1 * xdl.ref_ae_header_id
--AND xah.application_id = xdl.application_id
AND xah.event_id = xdl.ref_event_id
AND xah.ledger_id = l_secondary_alc_ledger_id
AND xah.request_id = l_upgrade_id
)
WHERE xdl.gain_or_loss_ref = to_char(l_upgrade_id)
AND xdl.ref_ae_header_id < 0
AND xdl.ref_event_id IS NOT NULL
AND xdl.ref_event_id <> -1 * xdl.ref_ae_header_id
AND EXISTS (SELECT 1
FROM xla_ae_headers xah2
WHERE xah2.upg_batch_id = -1* xdl.ref_ae_header_id); -- exists condition added since ref headers might refer to secondary headers we havent created because of start dates or status codes of primary data
(p_msg => 'Rows updated in xla_distribution_links with negative ref headers = ' || SQL%ROWCOUNT
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
INTO l_sql_time
FROM dual;
(p_msg => 'distribution links update SQL end: ' || l_sql_time
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
INSERT INTO XLA_AE_SEGMENT_VALUES
( ae_header_id
, segment_type_code
, segment_value
, ae_lines_count
, upg_batch_id)
SELECT xah.ae_header_id
, xasv.segment_type_code
, xasv.segment_value
, xasv.ae_lines_count
, l_upgrade_id
FROM xla_ae_headers xah
, xla_ae_segment_values xasv
WHERE XAH.upg_batch_id = XASV.ae_header_id
AND XAH.accounting_date >= l_start_date
AND XAH.accounting_date <= l_sec_alc_end_date
AND XAH.ledger_id = l_secondary_alc_ledger_id;
(p_msg => 'Rows inserted into xla_ae_segment_values: ' || SQL%ROWCOUNT
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
INTO l_sql_time
FROM dual;
l_ae_insert := TRUE;
(p_msg => 'Rows not inserted into segment values since conditions not met'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
(p_msg => 'inside distribution links insert. SQL error msg = ' || l_error_text
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
SELECT ae_header_id
FROM xla_ae_lines
WHERE request_id = l_upgrade_id
GROUP BY ae_header_id
HAVING sum(entered_cr) <> sum(entered_dr)
OR sum(accounted_dr) <> sum(accounted_cr)
OR sum(unrounded_entered_cr) <> sum(unrounded_entered_dr)
OR sum(unrounded_accounted_dr) <> sum(unrounded_accounted_cr);
SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
INTO l_sql_time
FROM dual;
SELECT 1
INTO l_error_lines
FROM DUAL
WHERE EXISTS (SELECT 1
FROM xla_ae_lines
WHERE request_id = l_upgrade_id
GROUP BY ae_header_id
HAVING sum(entered_cr) <> sum(entered_dr)
OR sum(accounted_dr) <> sum(accounted_cr)
OR sum(unrounded_entered_cr) <> sum(unrounded_entered_dr)
OR sum(unrounded_accounted_dr) <> sum(unrounded_accounted_cr));
SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
INTO l_sql_time
FROM dual;
SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
INTO l_sql_time
FROM dual;
SELECT 1
INTO l_relationship_exists
FROM DUAL
WHERE EXISTS (SELECT 1
FROM gl_ledger_relationships
WHERE primary_ledger_id = l_primary_ledger_id
AND target_ledger_id = l_secondary_alc_ledger_id
AND relationship_enabled_flag = 'Y');
SELECT min(relationship_id)
INTO l_relationship_id
FROM gl_ledger_relationships
WHERE primary_ledger_id = l_primary_ledger_id
AND target_ledger_id = l_secondary_alc_ledger_id
AND relationship_enabled_flag = 'Y';
SELECT max(upgrade_run_id)
INTO last_crash_run
FROM xla_rc_upgrade_rates rc, gl_ledger_relationships gl
WHERE gl.relationship_id = rc.relationship_id
AND gl.relationship_id = l_relationship_id
AND rc.relationship_id = l_relationship_id -- redundant
AND gl.hist_conv_status_code = 'RUNNING';
l_headers_insert := TRUE;
l_lines_insert := TRUE;
l_rates_insert := TRUE;
l_links_insert := TRUE;
l_ae_insert := TRUE;
SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
INTO l_sql_time
FROM dual;