[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_dynamic_inserts VARCHAR2(10);
SELECT meaning
INTO l_mode_meaning
FROM xla_lookups
WHERE lookup_type = 'XLA_HIST_UPG_MODE'
AND lookup_code = p_mode ;
-- all inserts (rates/historic control) are rolled back
retrieve_validate();
INSERT INTO xla_historic_control(primary_ledger,
secondary_alc_ledger,
application_id,
upgrade_id,
relationship_id,
script_name,
start_date,
end_date,
batch_size,
num_workers,
status,
ledger_category_code,
last_update_date,
primary_currency_code,
sec_alc_currency_code,
sec_alc_mau,
sec_alc_precision,
conversion_option,
currency_conversion_type,
currency_conversion_date,
primary_slam,
primary_coa,
primary_slam_type,
primary_aad ,
primary_aad_owner ,
secondary_slam,
secondary_coa,
secondary_slam_type,
secondary_aad ,
secondary_aad_owner ,
mapping_relationship_id,
coa_mapping_name,
primary_cal_set_name,
primary_cal_per_type,
primary_sec_alc_set_name,
primary_sec_alc_per_type,
dynamic_inserts ,
parent_request_id )
VALUES (
g_primary_ledger_id,
g_secondary_alc_ledger_id,
g_application_id,
g_upgrade_id,
g_relationship_id,
g_script_name,
g_start_date,
g_sec_alc_end_date,
g_batch_size,
g_num_workers,
'PHASE-RATE', --NULL,
g_ledger_category_code,
SYSDATE,
g_primary_currency_code,
g_sec_alc_currency_code,
g_sec_alc_mau,
g_sec_alc_precision,
g_conversion_option,
g_currency_conversion_type,
g_currency_conversion_date,
g_primary_slam,
g_primary_coa,
g_primary_slam_type,
g_primary_aad ,
g_primary_aad_owner ,
g_secondary_slam,
g_secondary_coa,
g_secondary_slam_type,
g_secondary_aad ,
g_secondary_aad_owner ,
g_mapping_relationship_id,
g_coa_mapping_name,
g_primary_cal_set_name,
g_primary_cal_per_type,
g_primary_sec_alc_set_name,
g_primary_sec_alc_per_type,
g_dynamic_inserts ,
fnd_global.conc_request_id()
);
INSERT INTO xla_historic_control(primary_ledger,
secondary_alc_ledger,
application_id,
upgrade_id,
relationship_id,
script_name,
start_date,
end_date,
batch_size,
num_workers,
status,
ledger_category_code,
last_update_date,
primary_currency_code,
sec_alc_currency_code,
sec_alc_mau,
sec_alc_precision,
conversion_option,
currency_conversion_type,
currency_conversion_date,
primary_slam,
primary_coa,
primary_slam_type,
primary_aad ,
primary_aad_owner,
secondary_slam,
secondary_coa,
secondary_slam_type,
secondary_aad,
secondary_aad_owner ,
mapping_relationship_id,
coa_mapping_name,
primary_cal_set_name,
primary_cal_per_type,
primary_sec_alc_set_name,
primary_sec_alc_per_type,
dynamic_inserts,
sec_alc_min_acctng_batch_id ,
parent_request_id )
VALUES (
g_primary_ledger_id,
g_secondary_alc_ledger_id,
g_application_id,
g_upgrade_id,
g_relationship_id,
g_script_name,
g_start_date,
g_sec_alc_end_date,
g_batch_size,
g_num_workers,
'PHASE-RATE', --NULL,
g_ledger_category_code,
SYSDATE,
g_primary_currency_code,
g_sec_alc_currency_code,
g_sec_alc_mau,
g_sec_alc_precision,
g_conversion_option,
g_currency_conversion_type,
g_currency_conversion_date,
g_primary_slam,
g_primary_coa,
g_primary_slam_type,
g_primary_aad,
g_primary_aad_owner ,
g_secondary_slam,
g_secondary_coa,
g_secondary_slam_type,
g_secondary_aad ,
g_secondary_aad_owner ,
g_mapping_relationship_id,
g_coa_mapping_name,
g_primary_cal_set_name,
g_primary_cal_per_type,
g_primary_sec_alc_set_name,
g_primary_sec_alc_per_type,
g_dynamic_inserts,
g_sec_alc_min_acctng_batch_id ,
fnd_global.conc_request_id()
);
populate_rates(); /* for different date-range runs, rates will be re-inserted with same
UPDATE xla_historic_control
SET status = 'PHASE-DATA-START'
WHERE primary_ledger = g_primary_ledger_id
AND secondary_alc_ledger = g_secondary_alc_ledger_id
AND application_id = g_application_id
AND relationship_id = g_relationship_id
AND upgrade_id = g_upgrade_id
AND script_name = g_script_name
AND status = 'PHASE-RATE';
(p_msg => 'xla_historic_control updated to PHASE-DATA-START, commit executed'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
insert_data(g_primary_ledger_id,
g_secondary_alc_ledger_id,
g_application_id,
g_relationship_id,
g_upgrade_id,
g_script_name,
g_batch_size,
g_num_workers); /*contains commit after FND submit, and after child workers completed successfully*/
insert_data(g_primary_ledger_id
,g_secondary_alc_ledger_id
,g_application_id
,g_relationship_id
,g_upgrade_id
,g_script_name
,g_batch_size
,g_num_workers); -- call procedure with last run details
SELECT COUNT(*)
INTO g_recovery_failed_runs
FROM xla_historic_control
WHERE primary_ledger = g_primary_ledger_id
AND secondary_alc_ledger = g_secondary_alc_ledger_id
AND application_id = g_application_id
AND status <> 'SUCCESS';
SELECT primary_ledger
,secondary_alc_ledger
,application_id
,relationship_id
,upgrade_id
,script_name
,batch_size
,num_workers
INTO g_primary_ledger_id
,g_secondary_alc_ledger_id
,g_application_id
,g_relationship_id
,g_upgrade_id
,g_script_name
,g_batch_size
,g_num_workers
FROM xla_historic_control
WHERE primary_ledger = g_primary_ledger_id
AND secondary_alc_ledger = g_secondary_alc_ledger_id
AND application_id = g_application_id
AND status = 'PHASE-DATA-START';
SELECT COUNT(*)
INTO g_failed_runs
FROM xla_historic_control
WHERE primary_ledger = g_primary_ledger_id
AND secondary_alc_ledger = g_secondary_alc_ledger_id
AND application_id = g_application_id
AND relationship_id = g_relationship_id
AND status <> 'SUCCESS';
SELECT COUNT(*)
INTO g_success_runs
FROM xla_historic_control
WHERE primary_ledger = g_primary_ledger_id
AND secondary_alc_ledger = g_secondary_alc_ledger_id
AND application_id = g_application_id
AND relationship_id = g_relationship_id
AND status = 'SUCCESS';
SELECT nvl(min(accounting_date-1), to_date('31/12/9999', 'DD/MM/YYYY'))
INTO g_sec_alc_end_date
FROM xla_ae_headers
WHERE application_id = g_application_id
AND ledger_id = g_secondary_alc_ledger_id
AND accounting_entry_status_code = 'F';
SELECT min(accounting_batch_id)-1
INTO g_sec_alc_min_acctng_batch_id
FROM xla_ae_headers
WHERE application_id = g_application_id
AND ledger_id = g_secondary_alc_ledger_id
AND accounting_entry_status_code = 'F';
SELECT xla_accounting_batches_s.NEXTVAL INTO g_sec_alc_min_acctng_batch_id FROM DUAL;
SELECT min(start_date-1) , min(sec_alc_min_acctng_batch_id)
INTO g_sec_alc_end_date , g_sec_alc_min_acctng_batch_id
FROM xla_historic_control
WHERE primary_ledger = g_primary_ledger_id
AND secondary_alc_ledger = g_secondary_alc_ledger_id
AND application_id = g_application_id
AND relationship_id = g_relationship_id
AND status = 'SUCCESS';
SELECT 1
INTO g_untransferred_headers
FROM DUAL
WHERE EXISTS (SELECT /*+ parallel (xla_ae_headers) */ 1
FROM xla_ae_headers
WHERE ledger_id = g_primary_ledger_id
AND accounting_entry_status_code = 'F'
AND application_id = g_application_id
AND balance_type_code in ('A', 'E')
AND event_type_code <> 'MANUAL'
AND gl_transfer_status_code = 'N' -- bug9278306
AND accounting_date >= g_start_date
AND accounting_date <= g_sec_alc_end_date);
SELECT xla_upg_batches_s.NEXTVAL INTO g_upgrade_id FROM DUAL;
SELECT min(relationship_id)
INTO g_relationship_id
FROM gl_ledger_relationships
WHERE primary_ledger_id = g_primary_ledger_id
AND target_ledger_id = g_secondary_alc_ledger_id
AND application_id = 101
AND relationship_enabled_flag = 'Y';
SELECT currency_code
INTO g_primary_currency_code
FROM gl_ledgers
WHERE ledger_id = g_primary_ledger_id;
SELECT ledger_category_code, currency_code
INTO g_ledger_category_code, g_sec_alc_currency_code -- ledger category will store 'SECONDARY' or 'ALC' or 'NONE'
FROM gl_ledgers
WHERE ledger_id = g_secondary_alc_ledger_id;
SELECT minimum_accountable_unit, precision
INTO g_sec_alc_mau, g_sec_alc_precision
FROM fnd_currencies
WHERE currency_code = g_sec_alc_currency_code;
SELECT alc_init_conv_option_code, alc_initializing_rate_type, alc_initializing_rate_date
INTO g_conversion_option, g_currency_conversion_type, g_currency_conversion_date
FROM gl_ledger_relationships
WHERE relationship_id = g_relationship_id;
SELECT period_set_name, accounted_period_type
INTO g_primary_cal_set_name, g_primary_cal_per_type
FROM gl_ledgers
WHERE ledger_id = g_primary_ledger_id;
SELECT period_set_name, accounted_period_type
INTO g_primary_sec_alc_set_name, g_primary_sec_alc_per_type
FROM gl_ledgers
WHERE ledger_id = g_secondary_alc_ledger_id;
SELECT 1
INTO g_alc_exists
FROM DUAL
WHERE EXISTS (SELECT 1
FROM xla_subledgers
WHERE alc_enabled_flag = 'Y'
AND application_id = g_application_id);
SELECT xlr.sla_accounting_method_code
,xlr.chart_of_accounts_id
,xlr.sla_accounting_method_type
,xlr.enable_budgetary_control_flag
,xamr.product_rule_code
,xamr.PRODUCT_RULE_TYPE_CODE
INTO g_primary_slam
,g_primary_coa
,g_primary_slam_type
,g_primary_budget
,g_primary_aad
,g_primary_aad_owner
FROM xla_ledger_relationships_v xlr ,
XLA_ACCTG_METHOD_RULES xamr
WHERE xlr.ledger_id = g_primary_ledger_id
AND xamr.amb_context_code = 'DEFAULT'
AND xlr.sla_ACCOUNTING_METHOD_CODE = xamr.ACCOUNTING_METHOD_CODE
AND xlr.sla_ACCOUNTING_METHOD_TYPE = xamr.ACCOUNTING_METHOD_TYPE_CODE
AND xamr.application_id = g_application_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(xamr.start_date_active) AND TRUNC(NVL(xamr.END_DATE_ACTIVE , SYSDATE));
SELECT xlr.sla_accounting_method_code
,xlr.chart_of_accounts_id
,xlr.sla_accounting_method_type
,xlr.enable_budgetary_control_flag
,xamr.product_rule_code
,xamr.PRODUCT_RULE_TYPE_CODE
INTO g_secondary_slam
,g_secondary_coa
,g_secondary_slam_type
,g_secondary_budget
,g_secondary_aad
,g_secondary_aad_owner
FROM xla_ledger_relationships_v xlr ,
XLA_ACCTG_METHOD_RULES xamr
WHERE xlr.ledger_id = g_secondary_alc_ledger_id
AND xamr.amb_context_code = 'DEFAULT'
AND xlr.sla_ACCOUNTING_METHOD_CODE = xamr.ACCOUNTING_METHOD_CODE
AND xlr.sla_ACCOUNTING_METHOD_TYPE = xamr.ACCOUNTING_METHOD_TYPE_CODE
AND xamr.application_id = g_application_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(xamr.start_date_active) AND TRUNC(NVL(xamr.END_DATE_ACTIVE , SYSDATE));
SELECT sl_coa_mapping_id
INTO g_mapping_relationship_id
FROM xla_ledger_relationships_v
WHERE ledger_id = g_secondary_alc_ledger_id
AND primary_ledger_id = g_primary_ledger_id;
SELECT name
INTO g_coa_mapping_name
FROM gl_coa_mappings
WHERE coa_mapping_id = g_mapping_relationship_id;
SELECT dynamic_inserts_allowed_flag
INTO g_dynamic_inserts
FROM fnd_id_flex_structures_vl
WHERE application_id = 101
AND id_flex_code = 'GL#'
AND id_flex_num = g_primary_coa; -- why is this flag for primary???
(p_msg => 'g_dynamic_inserts = ' || g_dynamic_inserts
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
SELECT /*+ parallel(xah) parallel(xal) leading(xah) */ distinct(XAL.currency_code)
FROM xla_ae_headers XAH,
xla_historic_control XHC,
xla_ae_lines XAL
WHERE XHC.primary_ledger = g_primary_ledger_id
AND XHC.secondary_alc_ledger = g_secondary_alc_ledger_id
AND XHC.upgrade_id = g_upgrade_id
AND XHC.application_id = g_application_id
AND XHC.script_name = g_script_name
AND XHC.relationship_id = g_relationship_id
AND XHC.status = 'PHASE-RATE'
AND XAH.ledger_id = XHC.primary_ledger
AND XAH.application_id = XHC.application_id
AND XAH.accounting_entry_status_code = 'F'
AND XAH.gl_transfer_status_code IN ('Y', 'NT') -- bug9278306
AND XAH.balance_type_code in ('A', 'E')
AND XAH.accounting_date >= XHC.start_date
AND XAH.accounting_date <= XHC.end_date
AND XAL.application_id = XAH.application_id
AND XAL.ae_header_id = XAH.ae_header_id
AND nvl(XAH.accounting_batch_id,0) <= NVL(XHC.sec_alc_min_acctng_batch_id , nvl(XAH.accounting_batch_id,0));
SELECT 1
INTO g_rate_exists
FROM DUAL
WHERE EXISTS (SELECT 1
FROM xla_rc_upgrade_rates
WHERE relationship_id = g_relationship_id
AND upgrade_run_id = g_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(g_relationship_id
,g_upgrade_id
,c_entered_currencies_rec.currency_code
,g_sec_alc_currency_code
,g_denominator
,g_numerator
,g_rate
,null
,null
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,fnd_global.login_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(g_relationship_id
,g_upgrade_id
,g_primary_currency_code
,g_sec_alc_currency_code
,g_denominator
,g_numerator
,g_rate
,null
,null
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,fnd_global.login_id);
SELECT count(*)
INTO g_currency_count
FROM xla_rc_upgrade_rates
WHERE relationship_id = g_relationship_id
AND upgrade_run_id = g_upgrade_id;
(p_msg => 'Rows inserted into xla_rc_upgrade_rates after calling GL API = ' || g_currency_count
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
PROCEDURE insert_data(p_primary_ledger_id IN NUMBER,
p_sec_alc_ledger_id IN NUMBER,
p_application_id IN NUMBER,
p_relationship_id IN NUMBER,
p_upgrade_id IN NUMBER,
p_script_name IN VARCHAR2,
p_batch_size IN NUMBER,
p_num_workers IN NUMBER)
IS
l_log_module VARCHAR2(240);
l_log_module := C_DEFAULT_MODULE||'.insert_data';
(p_msg => 'insert_data procedure start time: ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
fnd_file.put_line(fnd_file.log,'insert_data procedure start time: ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
SELECT DECODE(primary_coa, secondary_coa, 'N', 'Y'),
DECODE(primary_cal_set_name, primary_sec_alc_set_name,
DECODE(primary_cal_per_type, primary_sec_alc_per_type, 'N', 'Y'),
'Y'),
dynamic_inserts,
mapping_relationship_id,
coa_mapping_name
INTO g_ccid_map,
g_calendar_convert,
g_dynamic_flag,
g_mapping_rel_id,
g_coa_map_name
FROM xla_historic_control
WHERE primary_ledger = p_primary_ledger_id
AND secondary_alc_ledger = p_sec_alc_ledger_id
AND application_id = p_application_id
AND relationship_id = p_relationship_id
AND upgrade_id = p_upgrade_id
AND script_name = p_script_name
AND status = 'PHASE-DATA-START';
UPDATE xla_historic_control
SET status = 'SUCCESS'
WHERE primary_ledger = p_primary_ledger_id
AND secondary_alc_ledger = p_sec_alc_ledger_id
AND application_id = p_application_id
AND script_name = p_script_name
AND relationship_id = p_relationship_id
AND g_upgrade_id = p_upgrade_id
AND status = 'PHASE-DATA-START';
ad_parallel_updates_pkg.initialize_rowid_range(
ad_parallel_updates_pkg.ROWID_RANGE,
l_table_owner,
l_table_name,
p_script_name,
p_worker_id,
p_num_workers,
p_batch_size,
0);
ad_parallel_updates_pkg.get_rowid_range(
l_start_rowid,
l_end_rowid,
l_any_rows_to_process,
p_batch_size,
TRUE);
SELECT COUNT(*)
INTO l_gt_count
FROM xla_historic_mapping_gt;
INSERT 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 /*+ rowid(xah) leading(xah) */
XLA_AE_HEADERS_S.nextval
, XAH.APPLICATION_ID
, XHC.secondary_alc_ledger
, XAH.ENTITY_ID
, XAH.EVENT_ID
, XAH.EVENT_TYPE_CODE
, XAH.ACCOUNTING_DATE
, XAH.GL_TRANSFER_STATUS_CODE -- stamped as Y or NT but assumed transferred via balance intialization
, 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 -- inherit primary description
, XAH.DOC_SEQUENCE_ID
, XAH.DOC_SEQUENCE_VALUE
, NULL -- ACCOUNTING_BATCH_ID
, NULL -- COMPLETION_ACCT_SEQ_VERSION_ID
, NULL -- CLOSE_ACCT_SEQ_VERSION_ID
, NULL -- COMPLETION_ACCT_SEQ_VALUE
, NULL -- CLOSE_ACCT_SEQ_VALUE
, XAH.BUDGET_VERSION_ID
, XAH.FUNDS_STATUS_CODE
, XAH.ENCUMBRANCE_TYPE_ID
, XAH.BALANCE_TYPE_CODE -- validation in place to ensure both ledgers budgetary
, XAH.REFERENCE_DATE
, XAH.COMPLETED_DATE
, XAH.PERIOD_NAME -- period name will be converted later if calendar different
, XAH.PACKET_ID
, NULL -- COMPLETION_ACCT_SEQ_ASSIGN_ID
, NULL -- 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
, XAH.GROUP_ID
, XAH.DOC_SEQUENCE_VERSION_ID
, XAH.DOC_SEQUENCE_ASSIGN_ID
, SYSDATE -- CREATION_DATE
, fnd_global.user_id -- CREATED_BY
, SYSDATE -- LAST_UPDATE_DATE
, fnd_global.user_id -- LAST_UPDATED_BY
, fnd_global.login_id -- LAST_UPDATE_LOGIN
, SYSDATE -- PROGRAM_UPDATE_DATE
, fnd_global.prog_appl_id -- PROGRAM_APPLICATION_ID reverted -601
, fnd_global.conc_program_id -- PROGRAM_ID reverted -601
, XHC.upgrade_id -- REQUEST_ID
, XAH.ae_header_id -- UPG_BATCH_ID = AE_HEADER_ID of PRIMARY ledger, cleared later
, -602 -- UPG_SOURCE_APPLICATION_ID
, XAH.UPG_VALID_FLAG
, XAH.ZERO_AMOUNT_FLAG
, XAH.PARENT_AE_HEADER_ID
, XAH.PARENT_AE_LINE_NUM
, XAH.ACCRUAL_REVERSAL_FLAG
, XAH.MERGE_EVENT_ID
FROM xla_ae_headers XAH,
xla_historic_control XHC
WHERE XHC.primary_ledger = p_primary_ledger_id -- worker input
AND XHC.secondary_alc_ledger = p_sec_alc_ledger_id -- worker input
AND XHC.upgrade_id = p_ugprade_id -- worker input
AND XHC.application_id = p_application_id -- worker input
AND XHC.script_name = p_script_name -- worker input
AND XHC.relationship_id = p_relationship_id -- worker input
AND XHC.status = 'PHASE-DATA-START'
AND XAH.ledger_id = XHC.primary_ledger
AND XAH.application_id = XHC.application_id
AND XAH.accounting_entry_status_code = 'F'
AND XAH.gl_transfer_status_code IN ('Y','NT') -- bug9278306
AND XAH.balance_type_code in ('A', 'E')
AND XAH.accounting_date >= XHC.start_date -- next run
AND XAH.accounting_date <= XHC.end_date
--AND XAH.accounting_batch_id <= NVL(XHC.sec_alc_min_acctng_batch_id , XAH.accounting_batch_id) -- modified by vgopiset
AND nvl(XAH.accounting_batch_id,0) <= NVL(XHC.sec_alc_min_acctng_batch_id , nvl(XAH.accounting_batch_id,0)) -- added to handle 11i data
AND XAH.event_type_code <> 'MANUAL' -- added by vgopiset
AND XAH.ROWID BETWEEN l_start_rowid AND l_end_rowid;
(p_msg => 'Rows inserted into xla_ae_headers: ' || l_rows_processed || ' at ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
IF (l_rows_processed <> 0) THEN --insert into GT/XDL/XAL only when headers are inserted.
INSERT INTO xla_historic_mapping_gt(primary_header_id, new_header_id)
SELECT /*+ rowid(xah) leading(xah) index(xahnew xla_ae_headers_n2)*/
XAH.ae_header_id, XAHNEW.ae_header_id
FROM xla_ae_headers XAH,
xla_historic_control XHC,
xla_ae_headers XAHNEW
WHERE XHC.primary_ledger = p_primary_ledger_id -- worker input
AND XHC.secondary_alc_ledger = p_sec_alc_ledger_id -- worker input
AND XHC.upgrade_id = p_ugprade_id -- worker input
AND XHC.application_id = p_application_id -- worker input
AND XHC.script_name = p_script_name -- worker input
AND XHC.relationship_id = p_relationship_id -- worker input
AND XHC.status = 'PHASE-DATA-START'
AND XAH.ledger_id = XHC.primary_ledger
AND XAH.application_id = XHC.application_id
AND XAH.accounting_entry_status_code = 'F'
AND XAH.gl_transfer_status_code IN ('Y','NT') -- bug9278306
AND XAH.balance_type_code in ('A', 'E')
AND XAH.accounting_date >= XHC.start_date
AND XAH.accounting_date <= XHC.end_date
--AND XAH.accounting_batch_id <= NVL(XHC.sec_alc_min_acctng_batch_id , XAH.accounting_batch_id)
AND nvl(XAH.accounting_batch_id,0) <= NVL(XHC.sec_alc_min_acctng_batch_id , nvl(XAH.accounting_batch_id,0)) -- added to handle 11i data
AND XAH.event_type_code <> 'MANUAL' -- added by vgopiset
AND XAH.ROWID BETWEEN l_start_rowid AND l_end_rowid
AND XAHNEW.application_id = XHC.application_id
AND XAHNEW.ledger_id = secondary_alc_ledger
AND XAHNEW.accounting_entry_status_code = 'F'
AND XAHNEW.gl_transfer_status_code IN ('Y','NT') -- bug9278306
AND XAHNEW.balance_type_code in ('A', 'E')
AND XAHNEW.accounting_date >= XHC.start_date
AND XAHNEW.accounting_date <= XHC.end_date
AND XAHNEW.upg_batch_id = XAH.ae_header_id
AND XAHNEW.event_id = XAH.event_id
AND XAHNEW.application_id = XAH.application_id;
(p_msg => 'Rows inserted into xla_historic_mapping_gt: ' || l_mapping_rows || ' at ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
ROLLBACK; -- added by vgopiset as above INSERTED HEADERS should not be COMMITED
INSERT 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 /*+ leading(xmap) */
XDL.APPLICATION_ID
, XDL.EVENT_ID
, XMAP.new_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
, XDL.ref_ae_header_id -- REF_AE_HEADER_ID
, NULL -- REF_TEMP_LINE_NUM
, XDL.ACCOUNTING_LINE_CODE
, XDL.ACCOUNTING_LINE_TYPE_CODE
, XDL.MERGE_DUPLICATE_CODE
-- Taking Absolute Value to ensure we always create reversal entries (even if it means DOUBLE
-- REVERSAL) and adding a LARGE VALUE to ensure that PREPAYMENT ADJUSTED cases where +ve/-ve
-- exists in the SAME HEADER doesn't cause UNIQUE CONSTRAINT EXCEPTION because of ABS value.
, ABS( XDL.TEMP_LINE_NUM + 5000000 )
, XDL.REF_EVENT_ID
, XDL.LINE_DEFINITION_OWNER_CODE
, XDL.LINE_DEFINITION_CODE
, XDL.EVENT_CLASS_CODE
, XDL.EVENT_TYPE_CODE
, XDL.UPG_BATCH_ID
, 'Y' CALCULATE_ACCTD_AMTS_FLAG -- XDL.CALCULATE_ACCTD_AMTS_FLAG commented for bug12329205
, 'Y' CALCULATE_G_L_AMTS_FLAG -- XDL.CALCULATE_G_L_AMTS_FLAG commented for bug12329205
, XDL.ROUNDING_CLASS_CODE
, XDL.DOCUMENT_ROUNDING_LEVEL
, XDL.UNROUNDED_ENTERED_DR -- retain XDL unrounded entered dr
, XDL.UNROUNDED_ENTERED_CR -- retain XDL unrounded entered cr
, XDL.DOC_ROUNDING_ENTERED_AMT
, XDL.DOC_ROUNDING_ACCTD_AMT
,DECODE(XAL.currency_code, XHC.sec_alc_currency_code, XDL.UNROUNDED_ENTERED_CR,
-- if ledger currency transaction for secondary then set unrounded accounted to unrouned entered
-- if not ledger currency transaction for secondary ledger then use either of following
DECODE(XHC.sec_alc_mau,NULL,
((( DECODE(XHC.conversion_option, 'D', NVL(XDL.UNROUNDED_ACCOUNTED_CR, XDL.UNROUNDED_ENTERED_CR),XDL.UNROUNDED_ENTERED_CR)
/XRUR.denominator_rate
) * XRUR.numerator_rate)
),
((( DECODE(XHC.conversion_option, 'D',NVL(XDL.UNROUNDED_ACCOUNTED_CR, XDL.UNROUNDED_ENTERED_CR),XDL.UNROUNDED_ENTERED_CR)
/XRUR.denominator_rate
) * XRUR.numerator_rate)
/XHC.sec_alc_mau)*XHC.sec_alc_mau
)
) -- XDL.UNROUNDED_ACCOUNTED_CR
,DECODE(XAL.currency_code, XHC.sec_alc_currency_code, XDL.UNROUNDED_ENTERED_DR,
-- if ledger currency transaction for secondary then set unrounded accounted to unrouned entered
-- if not ledger currency transaction for secondary ledger then use either of following
DECODE(XHC.sec_alc_mau,NULL,
((( DECODE(XHC.conversion_option, 'D', NVL(XDL.UNROUNDED_ACCOUNTED_DR, XDL.UNROUNDED_ENTERED_DR),XDL.UNROUNDED_ENTERED_DR)
/XRUR.denominator_rate
) *XRUR.numerator_rate)
),
((( DECODE(XHC.conversion_option, 'D',NVL(XDL.UNROUNDED_ACCOUNTED_DR, XDL.UNROUNDED_ENTERED_DR),XDL.UNROUNDED_ENTERED_DR)
/XRUR.denominator_rate
) * XRUR.numerator_rate)
/XHC.sec_alc_mau)*XHC.sec_alc_mau
)
) -- XDL.UNROUNDED_ACCOUNTED_DR
, 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
, GAIN_OR_LOSS_REF
FROM xla_historic_control XHC,
xla_historic_mapping_gt XMAP,
xla_ae_lines XAL,
xla_distribution_links XDL,
xla_rc_upgrade_rates XRUR
WHERE XHC.primary_ledger = p_primary_ledger_id -- worker input
AND XHC.secondary_alc_ledger = p_sec_alc_ledger_id -- worker input
AND XHC.upgrade_id = p_ugprade_id -- worker input
AND XHC.application_id = p_application_id -- worker input
AND XHC.script_name = p_script_name -- worker input
AND XHC.relationship_id = p_relationship_id -- worker input
AND XHC.status = 'PHASE-DATA-START'
AND XMAP.primary_header_id = XAL.ae_header_id
AND XAL.application_id = XHC.application_id
AND XAL.gain_or_loss_flag <> DECODE(XHC.conversion_option, 'D',' ','Y')
AND XDL.application_id = XAL.application_id
AND XDL.ae_header_id = XAL.ae_header_id -- primary header id
AND XDL.ae_line_num = XAL.ae_line_num
AND XRUR.upgrade_run_id = XHC.upgrade_id
AND XRUR.relationship_id = XHC.relationship_id
AND XRUR.to_currency = XHC.sec_alc_currency_code
AND XRUR.from_currency = DECODE(XHC.conversion_option, 'D',
XHC.primary_currency_code, -- functional currency of primary in case of D, single rate
XAL.currency_code); -- entered currency in XAL in case of I, multiple rates
(p_msg => 'Rows inserted into xla_distribution_links: ' || SQL%ROWCOUNT ||' at ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS') -- to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
INSERT 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 /*+ leading(xmap) */
XMAP.new_header_id
, XAL.AE_LINE_NUM
, XAL.APPLICATION_ID
, XAL.CODE_COMBINATION_ID
, XAL.GL_TRANSFER_MODE_CODE
, NULL --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, XHC.sec_alc_currency_code,
ENTERED_DR,
DECODE(XHC.sec_alc_mau,NULL,
ROUND(((DECODE(XHC.conversion_option, 'D',
NVL(XAL.ACCOUNTED_DR, XAL.ENTERED_DR),
XAL.ENTERED_DR)/XRUR.denominator_rate)*
XRUR.numerator_rate),XHC.sec_alc_precision),
ROUND(((DECODE(XHC.conversion_option, 'D',
NVL(XAL.ACCOUNTED_DR, XAL.ENTERED_DR),
XAL.ENTERED_DR)/XRUR.denominator_rate)*
XRUR.numerator_rate)/XHC.sec_alc_mau)*XHC.sec_alc_mau))
, DECODE(XAL.currency_code,XHC.sec_alc_currency_code,
ENTERED_CR,
DECODE(XHC.sec_alc_mau,NULL,
ROUND(((DECODE(XHC.conversion_option, 'D',
NVL(XAL.ACCOUNTED_CR, XAL.ENTERED_CR),
XAL.ENTERED_CR)/XRUR.denominator_rate)*
XRUR.numerator_rate),XHC.sec_alc_precision),
ROUND(((DECODE(XHC.conversion_option, 'D',
NVL(XAL.ACCOUNTED_CR, XAL.ENTERED_CR),
XAL.ENTERED_CR)/XRUR.denominator_rate)*
XRUR.numerator_rate)/XHC.sec_alc_mau)*XHC.sec_alc_mau))
, XAL.DESCRIPTION
, XAL.STATISTICAL_AMOUNT
, XAL.CURRENCY_CODE
, DECODE(XAL.currency_code,XHC.sec_alc_currency_code,
null,
DECODE(XHC.conversion_option, 'D',
NVL(XAL.CURRENCY_CONVERSION_DATE, XAL.ACCOUNTING_DATE),
XHC.currency_conversion_date)) -- CURRENCY_CONVERSION_DATE
, DECODE(XAL.currency_code,XHC.sec_alc_currency_code,
null,
DECODE(XHC.conversion_option, 'D',
NVL(XAL.currency_conversion_rate,1)*XRUR.conversion_rate,
XRUR.conversion_rate)) -- CURRENCY_CONVERSION_RATE
, DECODE(XAL.currency_code,XHC.sec_alc_currency_code,
null,
DECODE(XHC.conversion_option, 'D',
NVL(XAL.CURRENCY_CONVERSION_TYPE, 'EMU FIXED'),
g_currency_conversion_type)) --CURRENCY_CONVERSION_TYPE
, 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
, -602
, -602
, XHC.upgrade_id
, XAL.UPG_BATCH_ID
, XAL.UPG_TAX_REFERENCE_ID1
, XAL.UPG_TAX_REFERENCE_ID2
, XAL.UPG_TAX_REFERENCE_ID3
, DECODE(XAL.currency_code, XHC.sec_alc_currency_code,
UNROUNDED_ENTERED_DR,
DECODE(XHC.sec_alc_mau,null,
(((DECODE(XHC.conversion_option, 'D',
NVL(XAL.UNROUNDED_ACCOUNTED_DR, XAL.UNROUNDED_ENTERED_DR),
XAL.UNROUNDED_ENTERED_DR)/XRUR.denominator_rate)*
XRUR.numerator_rate)),
(((DECODE(XHC.conversion_option, 'D',
NVL(XAL.UNROUNDED_ACCOUNTED_DR, XAL.UNROUNDED_ENTERED_DR),
XAL.UNROUNDED_ENTERED_DR)/XRUR.denominator_rate)*
XRUR.numerator_rate)/XHC.sec_alc_mau)*XHC.sec_alc_mau)) --UNROUNDED_ACCOUNTED_DR
, DECODE(XAL.currency_code, XHC.sec_alc_currency_code,
UNROUNDED_ENTERED_CR,
DECODE(XHC.sec_alc_mau,null,
(((DECODE(XHC.conversion_option, 'D',
NVL(XAL.UNROUNDED_ACCOUNTED_CR, XAL.UNROUNDED_ENTERED_CR),
XAL.UNROUNDED_ENTERED_CR)/XRUR.denominator_rate)*
XRUR.numerator_rate)),
(((DECODE(XHC.conversion_option, 'D',
NVL(XAL.UNROUNDED_ACCOUNTED_CR, XAL.UNROUNDED_ENTERED_CR),
XAL.UNROUNDED_ENTERED_CR)/XRUR.denominator_rate)*
XRUR.numerator_rate)/XHC.sec_alc_mau)*XHC.sec_alc_mau)) --UNROUNDED_ACCOUNTED_CR
, XAL.GAIN_OR_LOSS_FLAG
, XAL.UNROUNDED_ENTERED_DR
, XAL.UNROUNDED_ENTERED_CR
, XAL.SUBSTITUTED_CCID
, XAL.BUSINESS_CLASS_CODE
, XAL.MPA_ACCRUAL_ENTRY_FLAG
, XAL.ENCUMBRANCE_TYPE_ID
, XAL.FUNDS_STATUS_CODE
, XAL.MERGE_CODE_COMBINATION_ID
, XAL.MERGE_PARTY_ID
, XAL.MERGE_PARTY_SITE_ID
, XAL.ACCOUNTING_DATE
, XHC.secondary_alc_ledger
, XAL.SOURCE_TABLE
, XAL.SOURCE_ID
, XAL.ACCOUNT_OVERLAY_SOURCE_ID
FROM xla_historic_control XHC,
xla_historic_mapping_gt XMAP,
xla_ae_lines XAL,
xla_rc_upgrade_rates XRUR
WHERE XHC.primary_ledger = p_primary_ledger_id -- worker input
AND XHC.secondary_alc_ledger = p_sec_alc_ledger_id -- worker input
AND XHC.upgrade_id = p_ugprade_id -- worker input
AND XHC.application_id = p_application_id -- worker input
AND XHC.script_name = p_script_name -- worker input
AND XHC.relationship_id = p_relationship_id -- worker input
AND XHC.status = 'PHASE-DATA-START'
AND XMAP.primary_header_id = XAL.ae_header_id -- primary header id
AND XAL.application_id = XHC.application_id
AND XAL.gain_or_loss_flag <> DECODE(XHC.conversion_option, 'D',' ','Y')
AND XRUR.upgrade_run_id = XHC.upgrade_id
AND XRUR.relationship_id = XHC.relationship_id
AND XRUR.to_currency = XHC.sec_alc_currency_code
AND XRUR.from_currency = DECODE(XHC.conversion_option, 'D',
XHC.primary_currency_code, -- functional currency of primary in case of D, single rate
XAL.currency_code);
(p_msg => 'Rows inserted into xla_ae_lines: ' || SQL%ROWCOUNT || ' at ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS') -- to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
DELETE FROM gl_accts_map_int_gt; -- bug 4564062
(p_msg => 'Rows deleted from gl_accts_map_int_gt = ' || SQL%ROWCOUNT
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
INSERT INTO gl_accts_map_int_gt(coa_mapping_id, from_ccid)
SELECT distinct p_mapping_rel_id, code_combination_id
FROM xla_ae_lines XAL
WHERE application_id = p_application_id
AND EXISTS (SELECT 1
FROM xla_historic_mapping_gt XMAP
WHERE XMAP.new_header_id = XAL.ae_header_id); -- sec/alc header id
(p_msg => 'Rows (distinct ccids) inserted into gl_accts_map_int_gt ' || SQL%ROWCOUNT || ' at ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
,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 = p_mapping_rel_id)
WHERE application_id = p_application_id
AND EXISTS (SELECT 1
FROM xla_historic_mapping_gt XMAP
WHERE XMAP.new_header_id = XAL.ae_header_id); -- sec/alc header id
(p_msg => 'Rows updated in xla_ae_lines with new ccid = ' || SQL%ROWCOUNT || ' at ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
UPDATE xla_ae_headers XAH
SET period_name = (SELECT GLS.period_name
FROM gl_period_statuses GLS
WHERE GLS.ledger_id = p_sec_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
)
, XAH.upg_batch_id = NULL
WHERE XAH.application_id = p_application_id
AND XAH.ledger_id = p_sec_alc_ledger_id
AND EXISTS (SELECT 1
FROM xla_historic_mapping_gt XMAP
WHERE XMAP.new_header_id = XAH.ae_header_id); -- sec/alc header id
(p_msg => 'Rows updated in xla_ae_headers with new period and null upgbatchid = ' || SQL%ROWCOUNT || ' at ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS') -- to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
UPDATE xla_ae_headers XAH
SET XAH.upg_batch_id = NULL
WHERE XAH.application_id = p_application_id
AND XAH.ledger_id = p_sec_alc_ledger_id
AND XAH.upg_batch_id IS NOT NULL
AND EXISTS (SELECT 1
FROM xla_historic_mapping_gt GT
WHERE GT.new_header_id = XAH.ae_header_id);
(p_msg => 'Rows updated in xla_ae_headers with null upgbatchid: ' || SQL%ROWCOUNT || ' at ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
(p_msg => 'No Headers to insert, so insert to GT/XDL/XAL Skipped at ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
ad_parallel_updates_pkg.processed_rowid_range(l_rows_processed, --stamps XAH rowcount only
l_end_rowid);
ad_parallel_updates_pkg.get_rowid_range(l_start_rowid,
l_end_rowid,
l_any_rows_to_process,
p_batch_size,
FALSE);