[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
FROM xla_appli_amb_contexts
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
FOR UPDATE OF application_id NOWAIT;
CURSOR c_updated IS
SELECT 1
FROM xla_appli_amb_contexts
WHERE amb_context_code = g_amb_context_code
AND application_id = g_application_id
AND updated_flag = 'N';
SELECT distinct t.name
FROM xla_product_rules_b w
, xla_product_rules_b s
, xla_staging_components_h h
, xla_product_rules_tl t
WHERE w.version_num > h.version_num
AND w.amb_context_code = g_amb_context_code
AND w.application_id = g_application_id
AND w.product_rule_type_code = s.product_rule_type_code
AND w.product_rule_code = s.product_rule_code
--
AND t.application_id = w.application_id
AND t.amb_context_code = w.amb_context_code
AND t.product_rule_type_code = w.product_rule_type_code
AND t.product_rule_code = w.product_rule_code
AND t.language = USERENV('LANG')
--
AND h.staging_amb_context_code = g_staging_context_code
AND h.application_id = g_application_id
AND h.component_owner_code = s.product_rule_type_code
AND h.component_code = s.product_rule_code
AND h.component_type_code = 'AAD'
AND h.version_num = s.version_num
--
AND s.amb_context_code = g_staging_context_code
AND s.application_id = g_application_id;
OPEN c_updated;
FETCH c_updated INTO l_exists;
IF (c_updated%NOTFOUND) THEN
l_retcode := 'WARNING';
,p_msg_name => 'XLA_AAD_MER_AMB_UPDATED');
CLOSE c_updated;
SELECT w.product_rule_code, w.version_num
FROM xla_product_rules_b w
WHERE w.application_id = g_application_id
AND w.amb_context_code = g_amb_context_code
AND w.product_rule_type_code = C_OWNER_SYSTEM
AND NOT EXISTS ( SELECT 1
FROM xla_product_rules_b s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND s.product_rule_type_code = C_OWNER_SYSTEM
AND s.product_rule_code = w.product_rule_code);
INSERT INTO xla_aad_loader_logs
(aad_loader_log_id
,amb_context_code
,application_id
,request_code
,log_type_code
,aad_application_id
,product_rule_code
,product_rule_type_code
,version_to
,object_version_number
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,program_update_date
,program_application_id
,program_id
,request_id)
VALUES
(xla_aad_loader_logs_s.nextval
,g_amb_context_code
,g_application_id
,'IMPORT'
,'DELETED_AAD'
,g_application_id
,l_codes(i)
,C_OWNER_SYSTEM
,l_version_nums(i)
,1
,sysdate
,xla_environment_pkg.g_usr_id
,sysdate
,xla_environment_pkg.g_usr_id
,xla_environment_pkg.g_login_id
,sysdate
,xla_environment_pkg.g_prog_appl_id
,xla_environment_pkg.g_prog_id
,xla_environment_pkg.g_req_Id);
DELETE FROM xla_aad_line_defn_assgns w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND product_rule_type_code = C_OWNER_SYSTEM
AND NOT EXISTS
(SELECT 1
FROM xla_aad_line_defn_assgns s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND s.product_rule_type_code = C_OWNER_SYSTEM
AND s.product_rule_code = w.product_rule_code
AND s.event_class_code = w.event_class_code
AND s.event_type_code = w.event_type_code
AND s.line_definition_owner_code = w.line_definition_owner_code
AND s.line_definition_code = w.line_definition_code);
trace(p_msg => '# xla_aad_line_defn_assgns deleted = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_aad_line_defn_assgns deleted = '||l_num_rows,
p_component_code => 'clean_oracle_aads');
DELETE FROM xla_aad_header_ac_assgns w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND product_rule_type_code = C_OWNER_SYSTEM
AND NOT EXISTS
(SELECT 1
FROM xla_aad_header_ac_assgns s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND s.product_rule_type_code = C_OWNER_SYSTEM
AND s.product_rule_code = w.product_rule_code
AND s.event_class_code = w.event_class_code
AND s.event_type_code = w.event_type_code
AND s.analytical_criterion_type_code = w.analytical_criterion_type_code
AND s.analytical_criterion_code = w.analytical_criterion_code);
trace(p_msg => '# xla_aad_header_ac_assgns deleted = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_aad_header_ac_assgns deleted = '||l_num_rows,
p_component_code => 'clean_oracle_aads');
DELETE FROM xla_aad_hdr_acct_attrs w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND product_rule_type_code = C_OWNER_SYSTEM
AND NOT EXISTS
(SELECT 1
FROM xla_aad_hdr_acct_attrs s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND s.product_rule_type_code = C_OWNER_SYSTEM
AND s.product_rule_code = w.product_rule_code
AND s.event_class_code = w.event_class_code
AND s.event_type_code = w.event_type_code
AND s.accounting_attribute_code = w.accounting_attribute_code);
trace(p_msg => '# xla_aad_hdr_acct_attrs deleted = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_aad_hdr_acct_attrs deleted = '||l_num_rows,
p_component_code => 'clean_oracle_aads');
DELETE FROM xla_prod_acct_headers w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND product_rule_type_code = C_OWNER_SYSTEM
AND NOT EXISTS
(SELECT 1
FROM xla_prod_acct_headers s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND s.product_rule_type_code = C_OWNER_SYSTEM
AND s.product_rule_code = w.product_rule_code
AND s.event_class_code = w.event_class_code
AND s.event_type_code = w.event_type_code);
trace(p_msg => '# xla_prod_acct_headers deleted = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_prod_acct_headers deleted = '||l_num_rows,
p_component_code => 'clean_oracle_aads');
DELETE FROM xla_product_rules_tl w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND product_rule_type_code = C_OWNER_SYSTEM
AND product_rule_code = l_codes(i);
trace(p_msg => '# xla_product_rules_tl deleted = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_product_rules_tl deleted = '||l_num_rows,
p_component_code => 'clean_oracle_aads');
DELETE FROM xla_product_rules_b w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND product_rule_type_code = C_OWNER_SYSTEM
AND product_rule_code = l_codes(i);
trace(p_msg => '# xla_product_rules_b deleted = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_product_rules_b deleted = '||l_num_rows,
p_component_code => 'clean_oracle_aads');
SELECT w.event_class_code
, w.event_type_code
, w.line_definition_code
FROM xla_line_definitions_b w
WHERE w.application_id = g_application_id
AND w.amb_context_code = g_amb_context_code
AND w.line_definition_owner_code = C_OWNER_SYSTEM
AND NOT EXISTS ( SELECT 1
FROM xla_line_definitions_b s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND s.event_class_code = w.event_class_code
AND s.event_type_code = w.event_type_code
AND s.line_definition_owner_code = C_OWNER_SYSTEM
AND s.line_definition_code = w.line_definition_code);
DELETE FROM xla_aad_line_defn_assgns w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND event_class_code = l_event_class_codes(i)
AND event_type_code = l_event_type_codes(i)
AND line_definition_owner_code = C_OWNER_SYSTEM
AND line_definition_code = l_codes(i);
trace(p_msg => '# xla_aad_line_defn_assgns deleted = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_aad_line_defn_assgns deleted = '||l_num_rows,
p_component_code => 'clean_oracle_jlds');
DELETE FROM xla_line_defn_ac_assgns w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND line_definition_owner_code = C_OWNER_SYSTEM
AND NOT EXISTS
(SELECT 1
FROM xla_line_defn_ac_assgns s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND s.event_class_code = w.event_class_code
AND s.event_type_code = w.event_type_code
AND s.line_definition_owner_code = C_OWNER_SYSTEM
AND s.line_definition_code = w.line_definition_code
AND s.accounting_line_type_code = w.accounting_line_type_code
AND s.accounting_line_code = w.accounting_line_code
AND s.analytical_criterion_type_code = w.analytical_criterion_type_code
AND s.analytical_criterion_code = w.analytical_criterion_code);
trace(p_msg => '# xla_line_defn_ac_assgns deleted = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_line_defn_ac_assgns deleted = '||l_num_rows,
p_component_code => 'clean_oracle_jlds');
DELETE FROM xla_line_defn_adr_assgns w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND line_definition_owner_code = C_OWNER_SYSTEM
AND NOT EXISTS
(SELECT 1
FROM xla_line_defn_adr_assgns s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND s.event_class_code = w.event_class_code
AND s.event_type_code = w.event_type_code
AND s.line_definition_owner_code = C_OWNER_SYSTEM
AND s.line_definition_code = w.line_definition_code
AND s.accounting_line_type_code = w.accounting_line_type_code
AND s.accounting_line_code = w.accounting_line_code
AND s.flexfield_segment_code = w.flexfield_segment_code);
trace(p_msg => '# xla_line_defn_adr_assgns deleted = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_line_defn_adr_assgns deleted = '||l_num_rows,
p_component_code => 'clean_oracle_jlds');
DELETE FROM xla_line_defn_jlt_assgns w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND line_definition_owner_code = C_OWNER_SYSTEM
AND NOT EXISTS
(SELECT 1
FROM xla_line_defn_jlt_assgns s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND s.event_class_code = w.event_class_code
AND s.event_type_code = w.event_type_code
AND s.line_definition_owner_code = C_OWNER_SYSTEM
AND s.line_definition_code = w.line_definition_code
AND s.accounting_line_type_code = w.accounting_line_type_code
AND s.accounting_line_code = w.accounting_line_code);
trace(p_msg => '# xla_line_defn_jlt_assgns deleted = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_line_defn_jlt_assgns deleted = '||l_num_rows,
p_component_code => 'clean_oracle_jlds');
DELETE FROM xla_mpa_jlt_assgns w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND line_definition_owner_code = C_OWNER_SYSTEM
AND NOT EXISTS
(SELECT 1
FROM xla_mpa_jlt_assgns s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND s.event_class_code = w.event_class_code
AND s.event_type_code = w.event_type_code
AND s.line_definition_owner_code = C_OWNER_SYSTEM
AND s.line_definition_code = w.line_definition_code
AND s.accounting_line_type_code = w.accounting_line_type_code
AND s.accounting_line_code = w.accounting_line_code
AND s.mpa_accounting_line_type_code = w.mpa_accounting_line_type_code
AND s.mpa_accounting_line_code = w.mpa_accounting_line_code);
trace(p_msg => '# xla_mpa_jlt_assgns deleted = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_mpa_jlt_assgns deleted = '||l_num_rows,
p_component_code => 'clean_oracle_jlds');
DELETE FROM xla_mpa_header_ac_assgns w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND line_definition_owner_code = C_OWNER_SYSTEM
AND NOT EXISTS
(SELECT 1
FROM xla_mpa_header_ac_assgns s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND s.event_class_code = w.event_class_code
AND s.event_type_code = w.event_type_code
AND s.line_definition_owner_code = C_OWNER_SYSTEM
AND s.line_definition_code = w.line_definition_code
AND s.accounting_line_type_code = w.accounting_line_type_code
AND s.accounting_line_code = w.accounting_line_code
AND s.analytical_criterion_type_code = w.analytical_criterion_type_code
AND s.analytical_criterion_code = w.analytical_criterion_code);
trace(p_msg => '# xla_mpa_header_ac_assgns deleted = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_mpa_header_ac_assgns deleted = '||l_num_rows,
p_component_code => 'clean_oracle_jlds');
DELETE FROM xla_mpa_jlt_adr_assgns w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND line_definition_owner_code = C_OWNER_SYSTEM
AND NOT EXISTS
(SELECT 1
FROM xla_mpa_jlt_adr_assgns s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND s.event_class_code = w.event_class_code
AND s.event_type_code = w.event_type_code
AND s.line_definition_owner_code = C_OWNER_SYSTEM
AND s.line_definition_code = w.line_definition_code
AND s.accounting_line_type_code = w.accounting_line_type_code
AND s.accounting_line_code = w.accounting_line_code
AND s.mpa_accounting_line_type_code = w.mpa_accounting_line_type_code
AND s.mpa_accounting_line_code = w.mpa_accounting_line_code
AND s.flexfield_segment_code = w.flexfield_segment_code);
trace(p_msg => '# xla_mpa_jlt_adr_assgns deleted = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_mpa_jlt_adr_assgns deleted = '||l_num_rows,
p_component_code => 'clean_oracle_jlds');
DELETE FROM xla_mpa_jlt_ac_assgns w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND line_definition_owner_code = C_OWNER_SYSTEM
AND NOT EXISTS
(SELECT 1
FROM xla_mpa_jlt_ac_assgns s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND s.event_class_code = w.event_class_code
AND s.event_type_code = w.event_type_code
AND s.line_definition_owner_code = C_OWNER_SYSTEM
AND s.line_definition_code = w.line_definition_code
AND s.accounting_line_type_code = w.accounting_line_type_code
AND s.accounting_line_code = w.accounting_line_code
AND s.mpa_accounting_line_type_code = w.mpa_accounting_line_type_code
AND s.mpa_accounting_line_code = w.mpa_accounting_line_code
AND s.analytical_criterion_type_code = w.analytical_criterion_type_code
AND s.analytical_criterion_code = w.analytical_criterion_code);
trace(p_msg => '# xla_mpa_jlt_ac_assgns deleted = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_mpa_jlt_ac_assgns deleted = '||l_num_rows,
p_component_code => 'clean_oracle_jlds');
DELETE FROM xla_line_definitions_tl w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND event_class_code = l_event_class_codes(i)
AND event_type_code = l_event_type_codes(i)
AND line_definition_owner_code = C_OWNER_SYSTEM
AND line_definition_code = l_codes(i);
trace(p_msg => '# xla_line_definitions_tl deleted = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_line_definitions_tl deleted = '||l_num_rows,
p_component_code => 'clean_oracle_jlds');
DELETE FROM xla_line_definitions_b w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND event_class_code = l_event_class_codes(i)
AND event_type_code = l_event_type_codes(i)
AND line_definition_owner_code = C_OWNER_SYSTEM
AND line_definition_code = l_codes(i);
trace(p_msg => '# xla_line_definitions_b deleted = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_line_definitions_b deleted = '||l_num_rows,
p_component_code => 'clean_oracle_jlds');
SELECT work.event_class_code
, work.accounting_line_code
FROM xla_acct_line_types_b work
WHERE work.application_id = g_application_id
AND work.amb_context_code = g_amb_context_code
AND work.accounting_line_type_code = C_OWNER_SYSTEM
AND NOT EXISTS ( SELECT 1
FROM xla_acct_line_types_b stage
WHERE stage.application_id = g_application_id
AND stage.amb_context_code = g_staging_context_code
AND stage.event_class_code = work.event_class_code
AND stage.accounting_line_type_code = C_OWNER_SYSTEM
AND stage.accounting_line_code = work.accounting_line_code);
DELETE FROM xla_mpa_jlt_adr_assgns w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND event_class_code = l_event_class_codes(i)
AND accounting_line_type_code = C_OWNER_SYSTEM
AND accounting_line_code = l_codes(i);
trace(p_msg => '# xla_mpa_jlt_adr_assgns deleted = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_mpa_jlt_adr_assgns deleted = '||l_num_rows,
p_component_code => 'clean_oracle_jlts');
DELETE FROM xla_mpa_jlt_ac_assgns w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND event_class_code = l_event_class_codes(i)
AND accounting_line_type_code = C_OWNER_SYSTEM
AND accounting_line_code = l_codes(i);
trace(p_msg => '# xla_mpa_jlt_ac_assgns deleted = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_mpa_jlt_ac_assgns deleted = '||l_num_rows,
p_component_code => 'clean_oracle_jlts');
DELETE FROM xla_mpa_header_ac_assgns w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND event_class_code = l_event_class_codes(i)
AND accounting_line_type_code = C_OWNER_SYSTEM
AND accounting_line_code = l_codes(i);
trace(p_msg => '# xla_mpa_header_ac_assgns deleted = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_mpa_header_ac_assgns deleted = '||l_num_rows,
p_component_code => 'clean_oracle_jlts');
DELETE FROM xla_mpa_jlt_assgns w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND event_class_code = l_event_class_codes(i)
AND accounting_line_type_code = C_OWNER_SYSTEM
AND accounting_line_code = l_codes(i);
trace(p_msg => '# xla_mpa_jlt_assgns deleted = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_mpa_jlt_assgns deleted = '||l_num_rows,
p_component_code => 'clean_oracle_jlts');
DELETE FROM xla_line_defn_adr_assgns w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND event_class_code = l_event_class_codes(i)
AND accounting_line_type_code = C_OWNER_SYSTEM
AND accounting_line_code = l_codes(i);
trace(p_msg => '# xla_line_defn_adr_assgns deleted = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_line_defn_adr_assgns deleted = '||l_num_rows,
p_component_code => 'clean_oracle_jlts');
DELETE FROM xla_line_defn_ac_assgns w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND event_class_code = l_event_class_codes(i)
AND accounting_line_type_code = C_OWNER_SYSTEM
AND accounting_line_code = l_codes(i);
trace(p_msg => '# xla_line_defn_ac_assgns deleted = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_line_defn_ac_assgns deleted = '||l_num_rows,
p_component_code => 'clean_oracle_jlts');
DELETE FROM xla_line_defn_jlt_assgns w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND event_class_code = l_event_class_codes(i)
AND accounting_line_type_code = C_OWNER_SYSTEM
AND accounting_line_code = l_codes(i);
trace(p_msg => '# xla_line_defn_jlt_assgns deleted = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_line_defn_jlt_assgns deleted = '||l_num_rows,
p_component_code => 'clean_oracle_jlts');
DELETE FROM xla_jlt_acct_attrs w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND event_class_code = l_event_class_codes(i)
AND accounting_line_type_code = C_OWNER_SYSTEM
AND accounting_line_code = l_codes(i);
trace(p_msg => '# xla_jlt_acct_attrs deleted = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_jlt_acct_attrs deleted = '||l_num_rows,
p_component_code => 'clean_oracle_jlts');
DELETE FROM xla_acct_line_types_b w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND event_class_code = l_event_class_codes(i)
AND accounting_line_type_code = C_OWNER_SYSTEM
AND accounting_line_code = l_codes(i);
trace(p_msg => '# xla_acct_line_types_b deleted = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_acct_line_types_b deleted = '||l_num_rows,
p_component_code => 'clean_oracle_jlts');
DELETE FROM xla_acct_line_types_tl w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND event_class_code = l_event_class_codes(i)
AND accounting_line_type_code = C_OWNER_SYSTEM
AND accounting_line_code = l_codes(i);
trace(p_msg => '# xla_acct_line_types_tl deleted = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_acct_line_types_tl deleted = '||l_num_rows,
p_component_code => 'clean_oracle_jlts');
SELECT work.description_code
FROM xla_descriptions_b work
WHERE work.application_id = g_application_id
AND work.amb_context_code = g_amb_context_code
AND work.description_type_code = C_OWNER_SYSTEM
AND NOT EXISTS ( SELECT 1
FROM xla_descriptions_b stage
WHERE stage.application_id = g_application_id
AND stage.amb_context_code = g_staging_context_code
AND stage.description_type_code = C_OWNER_SYSTEM
AND stage.description_code = work.description_code);
UPDATE xla_line_defn_jlt_assgns
SET description_type_code = NULL
, description_code = NULL
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND description_type_code = C_OWNER_SYSTEM
AND description_code = l_codes(i);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_line_defn_jlt_assgns clear description = '||l_num_rows,
p_component_code => 'clean_oracle_descriptions');
UPDATE xla_prod_acct_headers
SET description_type_code = NULL
, description_code = NULL
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND description_type_code = C_OWNER_SYSTEM
AND description_code = l_codes(i);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_prod_acct_headers clear description = '||l_num_rows,
p_component_code => 'clean_oracle_descriptions');
DELETE FROM xla_descript_details_tl w
WHERE description_detail_id IN
(SELECT description_detail_id
FROM xla_descript_details_b d
, xla_desc_priorities p
WHERE d.description_prio_id = p.description_prio_id
AND p.application_id = g_application_id
AND p.amb_context_code = g_amb_context_code
AND p.description_type_code = C_OWNER_SYSTEM
AND p.description_code = l_codes(i));
trace(p_msg => '# xla_descript_details_tl deleted = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_descript_details_tl deleted = '||l_num_rows,
p_component_code => 'clean_oracle_descriptions');
DELETE FROM xla_descript_details_b w
WHERE description_prio_id IN
(SELECT description_prio_id
FROM xla_desc_priorities p
WHERE p.application_id = g_application_id
AND p.amb_context_code = g_amb_context_code
AND p.description_type_code = C_OWNER_SYSTEM
AND p.description_code = l_codes(i));
trace(p_msg => '# xla_descript_details_b deleted = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_descript_details_b deleted = '||l_num_rows,
p_component_code => 'clean_oracle_descriptions');
DELETE FROM xla_desc_priorities w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND description_type_code = C_OWNER_SYSTEM
AND description_code = l_codes(i);
trace(p_msg => '# xla_desc_priorities deleted = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_desc_priorities deleted = '||l_num_rows,
p_component_code => 'clean_oracle_descriptions');
DELETE FROM xla_descriptions_tl w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND description_type_code = C_OWNER_SYSTEM
AND description_code = l_codes(i);
trace(p_msg => '# xla_descriptions_tl deleted = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_descriptions_tl deleted = '||l_num_rows,
p_component_code => 'clean_oracle_descriptions');
DELETE FROM xla_descriptions_b w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND description_type_code = C_OWNER_SYSTEM
AND description_code = l_codes(i);
trace(p_msg => '# xla_descriptions_b deleted = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'# xla_descriptions_b deleted = '||l_num_rows,
p_component_code => 'clean_oracle_descriptions');
SELECT work.segment_rule_code
FROM xla_seg_rules_b work
WHERE work.application_id = g_application_id
AND work.amb_context_code = g_amb_context_code
AND work.segment_rule_type_code = C_OWNER_SYSTEM
AND NOT EXISTS
(SELECT 1
FROM xla_seg_rules_b stage
WHERE stage.application_id = g_application_id
AND stage.amb_context_code = g_staging_context_code
AND stage.segment_rule_type_code = C_OWNER_SYSTEM
AND stage.segment_rule_code = work.segment_rule_code);
trace(p_msg => '# ADRs to be deleted = '||l_codes.COUNT,
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'# ADRs to be deleted = '||l_codes.COUNT,
p_component_code => 'clean_oracle_adrs');
INSERT INTO xla_aad_loader_logs
(aad_loader_log_id
,amb_context_code
,application_id
,request_code
,log_type_code
,aad_application_id
,component_type_code
,component_owner_code
,component_code
,object_version_number
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,program_update_date
,program_application_id
,program_id
,request_id)
SELECT xla_aad_loader_logs_s.nextval
,g_amb_context_code
,g_application_id
,'IMPORT'
,'DELETED_SETUP'
,application_id
,'AMB_ADR'
,C_OWNER_SYSTEM
,l_codes(i)
,1
,sysdate
,xla_environment_pkg.g_usr_id
,sysdate
,xla_environment_pkg.g_usr_id
,xla_environment_pkg.g_login_id
,sysdate
,xla_environment_pkg.g_prog_appl_id
,xla_environment_pkg.g_prog_id
,xla_environment_pkg.g_req_Id
FROM (SELECT application_id
FROM xla_seg_rule_details s
WHERE application_id <> g_application_id
AND amb_context_code = g_amb_context_code
AND value_segment_rule_appl_id = g_application_id
AND value_segment_rule_type_code = C_OWNER_SYSTEM
AND value_segment_rule_code = l_codes(i)
UNION
SELECT application_id
FROM xla_line_defn_adr_assgns
WHERE application_id <> g_application_id
AND amb_context_code = g_amb_context_code
AND segment_rule_appl_id = g_application_id
AND segment_rule_type_code = C_OWNER_SYSTEM
AND segment_rule_code = l_codes(i)
UNION
SELECT application_id
FROM xla_mpa_jlt_adr_assgns
WHERE application_id <> g_application_id
AND amb_context_code = g_amb_context_code
AND segment_rule_appl_id = g_application_id
AND segment_rule_type_code = C_OWNER_SYSTEM
AND segment_rule_code = l_codes(i));
trace(p_msg => '# xla_aad_loader_logs inserted = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'# xla_aad_loader_logs inserted = '||l_num_rows,
p_component_code => 'clean_oracle_adrs');
DELETE FROM xla_seg_rule_details d
WHERE amb_context_code = g_amb_context_code
AND value_segment_rule_appl_id = g_application_id
AND value_segment_rule_type_code = C_OWNER_SYSTEM
AND value_segment_rule_code = l_codes(i);
trace(p_msg => '# xla_seg_rules_details (value) deleted = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'# xla_seg_rules_details (value) deleted = '||l_num_rows,
p_component_code => 'clean_oracle_adrs');
DELETE FROM xla_mpa_jlt_adr_assgns w
WHERE amb_context_code = g_amb_context_code
AND segment_rule_appl_id = g_application_id
AND segment_rule_type_code = C_OWNER_SYSTEM
AND segment_rule_code = l_codes(i);
trace(p_msg => '# xla_mpa_jlt_adr_assgns deleted = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'# xla_mpa_jlt_adr_assgns deleted = '||l_num_rows,
p_component_code => 'clean_oracle_adrs');
DELETE FROM xla_line_defn_adr_assgns w
WHERE amb_context_code = g_amb_context_code
AND segment_rule_appl_id = g_application_id
AND segment_rule_type_code = C_OWNER_SYSTEM
AND segment_rule_code = l_codes(i);
trace(p_msg => '# xla_line_defn_adr_assgns deleted = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'# xla_line_defn_adr_assgns deleted = '||l_num_rows,
p_component_code => 'clean_oracle_adrs');
DELETE FROM xla_seg_rule_details w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND segment_rule_type_code = C_OWNER_SYSTEM
AND segment_rule_code = l_codes(i);
trace(p_msg => '# xla_seg_rule_details deleted = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'# xla_seg_rule_details deleted = '||l_num_rows,
p_component_code => 'clean_oracle_adrs');
DELETE FROM xla_seg_rules_tl w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND segment_rule_type_code = C_OWNER_SYSTEM
AND segment_rule_code = l_codes(i);
trace(p_msg => '# xla_seg_rules_tl deleted = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'# xla_seg_rules_tl deleted = '||l_num_rows,
p_component_code => 'clean_oracle_adrs');
DELETE FROM xla_seg_rules_b w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND segment_rule_type_code = C_OWNER_SYSTEM
AND segment_rule_code = l_codes(i);
trace(p_msg => '#xla_seg_rules_b deleted = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'#xla_seg_rules_b deleted = '||l_num_rows,
p_component_code => 'clean_oracle_adrs');
SELECT w.analytical_criterion_code
FROM xla_analytical_hdrs_b w
WHERE w.amb_context_code = g_amb_context_code
AND w.application_id = g_application_id
AND w.analytical_criterion_type_code = C_OWNER_SYSTEM
AND NOT EXISTS ( SELECT 1
FROM xla_analytical_hdrs_b s
WHERE s.amb_context_code = g_staging_context_code
AND s.application_id = g_application_id
AND s.analytical_criterion_type_code = C_OWNER_SYSTEM
AND s.analytical_criterion_code = w.analytical_criterion_code);
DELETE FROM xla_aad_header_ac_assgns w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND analytical_criterion_type_code = C_OWNER_SYSTEM
AND analytical_criterion_code = l_codes(i);
trace(p_msg => '# xla_aad_header_ac_assgns deleted = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
DELETE FROM xla_line_defn_ac_assgns w
WHERE amb_context_code = g_amb_context_code
AND analytical_criterion_type_code = C_OWNER_SYSTEM
AND analytical_criterion_code = l_codes(i);
trace(p_msg => '# xla_line_defn_ac_assgns deleted = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
DELETE FROM xla_mpa_header_ac_assgns w
WHERE amb_context_code = g_amb_context_code
AND analytical_criterion_type_code = C_OWNER_SYSTEM
AND analytical_criterion_code = l_codes(i);
trace(p_msg => '# xla_mpa_header_ac_assgns deleted = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
DELETE FROM xla_mpa_jlt_ac_assgns w
WHERE amb_context_code = g_amb_context_code
AND analytical_criterion_type_code = C_OWNER_SYSTEM
AND analytical_criterion_code = l_codes(i);
trace(p_msg => '# xla_mpa_jlt_ac_assgns deleted = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
DELETE FROM xla_analytical_sources w
WHERE amb_context_code = g_amb_context_code
AND analytical_criterion_type_code = C_OWNER_SYSTEM
AND application_id = g_application_id -- added bug6696939
AND NOT EXISTS
(SELECT 1
FROM xla_analytical_sources s
WHERE s.amb_context_code = g_staging_context_code
AND s.application_id = g_application_id
AND s.entity_code = w.entity_code
AND s.event_class_code = w.event_class_code
AND s.source_application_id = w.source_application_id
AND s.source_type_code = w.source_type_code
AND s.source_code = w.source_code
AND s.analytical_criterion_type_code = C_OWNER_SYSTEM
AND s.analytical_criterion_code = w.analytical_criterion_code
AND s.analytical_detail_code = w.analytical_detail_code);
trace(p_msg => '# xla_analytical_sources deleted = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'# xla_analytical_sources deleted = '||l_num_rows,
p_component_code => 'clean_oracle_acs');
/*DELETE FROM xla_analytical_dtls_tl w
WHERE amb_context_code = g_amb_context_code
AND analytical_criterion_type_code = C_OWNER_SYSTEM
AND NOT EXISTS
(SELECT 1
FROM xla_analytical_dtls_b s
WHERE s.amb_context_code = g_staging_context_code
AND s.analytical_criterion_type_code = C_OWNER_SYSTEM
AND s.analytical_criterion_code = w.analytical_criterion_code
AND s.analytical_detail_code = w.analytical_detail_code);
trace(p_msg => '# xla_analytical_dtls_tl deleted = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
DELETE FROM xla_analytical_dtls_b w
WHERE amb_context_code = g_amb_context_code
AND analytical_criterion_type_code = C_OWNER_SYSTEM
AND NOT EXISTS
(SELECT 1
FROM xla_analytical_dtls_b s
WHERE s.amb_context_code = g_staging_context_code
AND s.analytical_criterion_type_code = C_OWNER_SYSTEM
AND s.analytical_criterion_code = w.analytical_criterion_code
AND s.analytical_detail_code = w.analytical_detail_code);
trace(p_msg => '# xla_analytical_dtls_b deleted = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
DELETE FROM xla_analytical_hdrs_tl w
WHERE amb_context_code = g_amb_context_code
AND analytical_criterion_type_code = C_OWNER_SYSTEM
AND analytical_criterion_code = l_codes(i);
trace(p_msg => '# xla_analytical_hdrs_tl deleted = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
DELETE FROM xla_analytical_hdrs_b w
WHERE amb_context_code = g_amb_context_code
AND analytical_criterion_type_code = C_OWNER_SYSTEM
AND analytical_criterion_code = l_codes(i);
trace(p_msg => '# xla_analytical_hdrs_b deleted = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'AAD clean up complete',
p_component_code => 'clean_oracle_aads');
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'JLD clean up complete',
p_component_code => 'clean_oracle_jlds');
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'JLT clean up complete',
p_component_code => 'clean_oracle_jlts');
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'JED clean up complete',
p_component_code => 'clean_oracle_descriptions');
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'ADR clean up complete',
p_component_code => 'clean_oracle_adrs');
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'Analytical Criteria clean up complete',
p_component_code => 'clean_oracle_acs');
INSERT INTO xla_aad_loader_logs
(aad_loader_log_id
,amb_context_code
,application_id
,request_code
,log_type_code
,aad_application_id
,product_rule_code
,product_rule_type_code
,version_from
,version_to
,object_version_number
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,program_update_date
,program_application_id
,program_id
,request_id)
SELECT xla_aad_loader_logs_s.nextval
,g_amb_context_code
,g_application_id
,'IMPORT'
,'MERGED_AAD'
,g_application_id
,w.product_rule_code
,w.product_rule_type_code
,w.version_num
,s.version_num
,1
,sysdate
,xla_environment_pkg.g_usr_id
,sysdate
,xla_environment_pkg.g_usr_id
,xla_environment_pkg.g_login_id
,sysdate
,xla_environment_pkg.g_prog_appl_id
,xla_environment_pkg.g_prog_id
,xla_environment_pkg.g_req_Id
FROM xla_product_rules_b s
, xla_product_rules_b w
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND w.application_id = g_application_id
AND w.amb_context_code = g_amb_context_code
AND w.product_rule_type_code = s.product_rule_type_code
AND w.product_rule_code = s.product_rule_code;
INSERT INTO xla_aads_gt
( product_rule_code,
event_class_code,
event_type_code,
line_definition_code,
table_name
)
select product_rule_code ,
event_class_code ,
event_type_code ,
line_definition_code ,
'XLA_AAD_LINE_DEFN_ASSGNS'
from (select product_rule_code ,
event_class_code ,
event_type_code ,
line_definition_code ,
'XLA_AAD_LINE_DEFN_ASSGNS' ,
amb_context_code,
last_update_date ,
nvl2(lag_date, decode(last_update_date,lag_date, 'True','False'),'False') flag
from
(select product_rule_code ,
event_class_code ,
event_type_code ,
line_definition_code ,
'XLA_AAD_LINE_DEFN_ASSGNS' ,
amb_context_code,
last_update_date ,
lag(last_update_date) over (PARTITION by application_id,
product_rule_code,
product_rule_type_code,
event_class_code,
event_type_code,
line_definition_code,
line_definition_owner_code
order by amb_context_code
) lag_date
from XLA_AAD_LINE_DEFN_ASSGNS
order by amb_context_code
) x
where x.amb_context_code =g_staging_context_code
)
where flag = 'False';
trace(p_msg => 'Number of Rows inserted into aads_gt from xla_aad_line_defn_assgns is :'||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'Number of Rows inserted into aads_gt from xla_aad_line_defn_assgns is :'||l_num_rows,
p_component_code => 'merge_aads');
INSERT INTO xla_aads_gt
( product_rule_code,
event_class_code,
accounting_attribute_code,
source_code,
table_name
)
select product_rule_code ,
event_class_code ,
accounting_attribute_code,
source_code,
'XLA_AAD_HDR_ACCT_ATTRS'
from
(select product_rule_code
,event_class_code
,accounting_attribute_code
,source_code
,'XLA_AAD_HDR_ACCT_ATTRS'
,amb_context_code
,last_update_date
,nvl2(lag_date, decode(last_update_date,lag_date, 'True','False'),'False') flag
from
(select product_rule_code
,event_class_code
,accounting_attribute_code
,source_code
,'XLA_AAD_HDR_ACCT_ATTRS'
,amb_context_code
,last_update_date
,lag(last_update_date) over (PARTITION by application_id
,product_rule_code
,product_rule_type_code
,event_class_code
,accounting_attribute_code
,event_type_code
order by amb_context_code
) lag_date
from XLA_AAD_HDR_ACCT_ATTRS
order by amb_context_code
) x
where x.amb_context_code =g_staging_context_code
)
where flag = 'False';
trace(p_msg => 'Number of Rows inserted into aads_gt from xla_aad_hdr_acct_attrs is :'||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'Number of Rows inserted into aads_gt from xla_aad_hdr_acct_attrs is :'||l_num_rows,
p_component_code => 'merge_aads');
INSERT INTO xla_aads_gt
( product_rule_code,
event_class_code,
event_type_code,
analytical_criterion_code,
table_name
)
select product_rule_code,
event_class_code,
event_type_code,
analytical_criterion_code,
'XLA_AAD_HEADER_AC_ASSGNS'
from (select product_rule_code,
event_class_code,
event_type_code,
analytical_criterion_code,
'XLA_AAD_HEADER_AC_ASSGNS',
amb_context_code,
last_update_date ,
nvl2(lag_date, decode(last_update_date,lag_date, 'True','False'),'False') flag
from
(select product_rule_code,
event_class_code,
event_type_code,
analytical_criterion_code,
'XLA_AAD_HEADER_AC_ASSGNS',
amb_context_code,
last_update_date ,
lag(last_update_date) over (PARTITION by application_id,
product_rule_code,
product_rule_type_code,
event_class_code,
event_type_code,
analytical_criterion_code,
analytical_criterion_type_code
order by amb_context_code
) lag_date
from XLA_AAD_HEADER_AC_ASSGNS
order by amb_context_code
) x
where x.amb_context_code =g_staging_context_code
)
where flag = 'False';
trace(p_msg => 'Number of Rows inserted into aads_gt from xla_aad_header_ac_assgns is :'||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'Number of Rows inserted into aads_gt from xla_aad_header_ac_assgns is :'||l_num_rows,
p_component_code => 'merge_aads');
UPDATE xla_product_rules_b pr
SET compile_status_code= nvl((select compile_status_code
from xla_product_rules_b pr1
where pr1.product_rule_code = pr.product_rule_code
and pr1.product_rule_type_code = pr.product_rule_type_code
and pr1.amb_context_code = g_amb_context_code
and pr1.application_id = g_application_id
),'N') --Bug: 10276169 - If select returns null then update default value 'N'.
WHERE amb_context_code = g_staging_context_code
AND application_id = g_application_id;
trace(p_msg => 'Number of Rows updated in PRODUCT_RULES to original status is :'||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'Number of Rows updated in PRODUCT_RULES to original status is :'||l_num_rows,
p_component_code => 'merge_aads');
DELETE FROM xla_product_rules_b w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND EXISTS (SELECT 1
FROM xla_product_rules_b s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND s.product_rule_type_code = w.product_rule_type_code
AND s.product_rule_code = w.product_rule_code);
trace(p_msg => '# xla_product_rules_b deleted : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'# xla_product_rules_b deleted : '||l_num_rows,
p_component_code => 'merge_aads');
DELETE FROM xla_product_rules_tl w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND EXISTS (SELECT 1
FROM xla_product_rules_tl s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND s.product_rule_type_code = w.product_rule_type_code
AND s.product_rule_code = w.product_rule_code
AND s.language = w.language);
trace(p_msg => '# xla_product_rules_tl deleted : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'# xla_product_rules_tl deleted : '||l_num_rows,
p_component_code => 'merge_aads');
DELETE FROM xla_prod_acct_headers w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND EXISTS (SELECT 1
FROM xla_prod_acct_headers s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND s.product_rule_type_code = w.product_rule_type_code
AND s.product_rule_code = w.product_rule_code
AND s.event_class_code = w.event_class_code
AND s.event_type_code = w.event_type_code);
trace(p_msg => '# xla_prod_acct_headers deleted : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'# xla_prod_acct_headers deleted : '||l_num_rows,
p_component_code => 'merge_aads');
DELETE FROM xla_aad_line_defn_assgns w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND EXISTS (SELECT 1
FROM xla_aad_line_defn_assgns s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND s.product_rule_type_code = w.product_rule_type_code
AND s.product_rule_code = w.product_rule_code
AND s.event_class_code = w.event_class_code
AND s.event_type_code = w.event_type_code
AND s.line_definition_owner_code = w.line_definition_owner_code
AND s.line_definition_code = w.line_definition_code);
trace(p_msg => '# xla_aad_line_defn_assgns deleted : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'# xla_aad_line_defn_assgns deleted : '||l_num_rows,
p_component_code => 'merge_aads');
DELETE FROM xla_aad_hdr_acct_attrs w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND EXISTS (SELECT 1
FROM xla_aad_hdr_acct_attrs s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND s.product_rule_type_code = w.product_rule_type_code
AND s.product_rule_code = w.product_rule_code
AND s.event_class_code = w.event_class_code
AND s.event_type_code = w.event_type_code
AND s.accounting_attribute_code = w.accounting_attribute_code);
DELETE FROM xla_aad_header_ac_assgns w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND EXISTS (SELECT 1
FROM xla_aad_header_ac_assgns s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND s.event_class_code = w.event_class_code
AND s.event_type_code = w.event_type_code
AND s.product_rule_type_code = w.product_rule_type_code
AND s.product_rule_code = w.product_rule_code
AND s.analytical_criterion_type_code = w.analytical_criterion_type_code
AND s.analytical_criterion_code = w.analytical_criterion_code);
trace(p_msg => '# xla_aad_header_ac_assgns deleted : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'# xla_aad_header_ac_assgns deleted : '||l_num_rows,
p_component_code => 'merge_aads');
UPDATE xla_product_rules_b
SET amb_context_code = g_amb_context_code
WHERE application_id = g_application_id
AND amb_context_code = g_staging_context_code;
trace(p_msg => '# xla_product_rules_b updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'# xla_product_rules_b updated : '||l_num_rows,
p_component_code => 'merge_aads');
UPDATE xla_product_rules_tl w
SET amb_context_code = g_amb_context_code
WHERE application_id = g_application_id
AND amb_context_code = g_staging_context_code
AND NOT EXISTS (SELECT 1
FROM xla_product_rules_tl s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_amb_context_code
AND s.product_rule_type_code = w.product_rule_type_code
AND s.name = w.name
AND s.language = w.language);
trace(p_msg => '# xla_product_rules_tl 1 updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'# xla_product_rules_tl 1 updated : '||l_num_rows,
p_component_code => 'merge_aads');
UPDATE xla_product_rules_tl w
SET amb_context_code = g_amb_context_code
, name = substr('('||product_rule_code||') '||name,1,80)
WHERE application_id = g_application_id
AND amb_context_code = g_staging_context_code
AND EXISTS (SELECT 1
FROM xla_product_rules_tl s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_amb_context_code
AND s.product_rule_type_code = w.product_rule_type_code
AND s.name = w.name
AND s.language = w.language);
trace(p_msg => '# xla_product_rules_tl 2 updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'# xla_product_rules_tl 2 updated : '||l_num_rows,
p_component_code => 'merge_aads');
UPDATE xla_prod_acct_headers
SET amb_context_code = g_amb_context_code
WHERE application_id = g_application_id
AND amb_context_code = g_staging_context_code;
trace(p_msg => '# xla_prod_acct_headers updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'# xla_prod_acct_headers updated : '||l_num_rows,
p_component_code => 'merge_aads');
UPDATE xla_aad_line_defn_assgns
SET amb_context_code = g_amb_context_code
WHERE application_id = g_application_id
AND amb_context_code = g_staging_context_code;
trace(p_msg => '# xla_aad_line_defn_assgns updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'# xla_aad_line_defn_assgns updated : '||l_num_rows,
p_component_code => 'merge_aads');
UPDATE xla_aad_hdr_acct_attrs
SET amb_context_code = g_amb_context_code
WHERE application_id = g_application_id
AND amb_context_code = g_staging_context_code;
trace(p_msg => '# xla_aad_hdr_acct_attrs updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'# xla_aad_hdr_acct_attrs updated : '||l_num_rows,
p_component_code => 'merge_aads');
UPDATE xla_aad_header_ac_assgns
SET amb_context_code = g_amb_context_code
WHERE application_id = g_application_id
AND amb_context_code = g_staging_context_code;
trace(p_msg => '# xla_aad_header_ac_assgns updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'# xla_aad_header_ac_assgns updated : '||l_num_rows,
p_component_code => 'merge_aads');
INSERT INTO xla_aads_gt
( event_class_code,
event_type_code,
line_definition_code,
table_name
)
select event_class_code,
event_type_code,
line_definition_code,
'XLA_LINE_DEFINITIONS_B'
from
(select event_class_code,
event_type_code,
line_definition_code,
'XLA_LINE_DEFINITIONS_B',
amb_context_code,
last_update_date ,
nvl2(lag_date, decode(last_update_date,lag_date, 'True','False'),'False') flag
from
(select event_class_code,
event_type_code,
line_definition_code,
'XLA_LINE_DEFINITIONS_B',
amb_context_code,
last_update_date ,
lag(last_update_date) over (PARTITION by application_id,
event_class_code,
event_type_code,
line_definition_code,
line_definition_owner_code
order by amb_context_code
) lag_date
from xla_line_definitions_b
order by amb_context_code
) x
where x.amb_context_code =g_staging_context_code
)
where flag = 'False';
trace(p_msg => 'Number of Rows inserted into aads_gt from xla_line_definitions_b is :'||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'Number of Rows inserted into aads_gt from xla_line_definitions_b is :'||l_num_rows,
p_component_code => 'merge_journal_line_defns');
INSERT INTO xla_aads_gt
( event_class_code,
event_type_code,
line_definition_code,
accounting_line_code,
table_name
)
select event_class_code,
event_type_code,
line_definition_code,
accounting_line_code,
'XLA_LINE_DEFN_JLT_ASSGNS'
from
(select event_class_code,
event_type_code,
line_definition_code,
accounting_line_code,
'XLA_LINE_DEFN_JLT_ASSGNS',
amb_context_code,
last_update_date ,
nvl2(lag_date, decode(last_update_date,lag_date, 'True','False'),'False') flag
from
(select event_class_code,
event_type_code,
line_definition_code,
accounting_line_code,
'XLA_LINE_DEFN_JLT_ASSGNS',
amb_context_code,
last_update_date ,
lag(last_update_date) over (PARTITION by application_id,
event_class_code,
event_type_code,
line_definition_code,
line_definition_owner_code,
accounting_line_code,
accounting_line_type_code
order by amb_context_code
) lag_date
from XLA_LINE_DEFN_JLT_ASSGNS
order by amb_context_code
) x
where x.amb_context_code =g_staging_context_code
)
where flag = 'False';
trace(p_msg => 'Number of Rows inserted into aads_gt from xla_line_defn_jlt_assgns is :'||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'Number of Rows inserted into aads_gt from xla_line_defn_jlt_assgns is :'||l_num_rows,
p_component_code => 'merge_journal_line_defns');
INSERT INTO xla_aads_gt
( event_class_code,
event_type_code,
line_definition_code,
accounting_line_code,
segment_rule_code,
table_name
)
select event_class_code,
event_type_code,
line_definition_code,
accounting_line_code,
segment_rule_code,
'XLA_LINE_DEFN_ADR_ASSGNS'
from
(select event_class_code,
event_type_code,
line_definition_code,
accounting_line_code,
segment_rule_code,
'XLA_LINE_DEFN_ADR_ASSGNS',
amb_context_code,
last_update_date ,
nvl2(lag_date, decode(last_update_date,lag_date, 'True','False'),'False') flag
from
(select event_class_code,
event_type_code,
line_definition_code,
accounting_line_code,
segment_rule_code,
'XLA_LINE_DEFN_ADR_ASSGNS',
amb_context_code,
last_update_date,
lag(last_update_date) over (PARTITION by application_id,
event_class_code,
event_type_code,
line_definition_code,
line_definition_owner_code,
accounting_line_code,
accounting_line_type_code,
segment_rule_code,
segment_rule_type_code
order by amb_context_code
) lag_date
from XLA_LINE_DEFN_ADR_ASSGNS
order by amb_context_code
) x
where x.amb_context_code = g_staging_context_code
)
where flag = 'False';
trace(p_msg => 'Number of Rows inserted into aads_gt from xla_line_defn_adr_assgns is :'||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'Number of Rows inserted into aads_gt from xla_line_defn_adr_assgns is :'||l_num_rows,
p_component_code => 'merge_journal_line_defns');
INSERT INTO xla_aads_gt
( event_class_code,
event_type_code,
line_definition_code,
accounting_line_code,
analytical_criterion_code,
table_name
)
select event_class_code,
event_type_code,
line_definition_code,
accounting_line_code,
analytical_criterion_code,
'XLA_LINE_DEFN_AC_ASSGNS'
from
(select event_class_code,
event_type_code,
line_definition_code,
accounting_line_code,
analytical_criterion_code,
'XLA_LINE_DEFN_AC_ASSGNS',
amb_context_code,
last_update_date ,
nvl2(lag_date, decode(last_update_date,lag_date, 'True','False'),'False') flag
from
(select event_class_code,
event_type_code,
line_definition_code,
accounting_line_code,
analytical_criterion_code,
'XLA_LINE_DEFN_AC_ASSGNS',
amb_context_code,
last_update_date ,
lag(last_update_date) over (PARTITION by application_id,
event_class_code,
event_type_code,
line_definition_code,
line_definition_owner_code,
accounting_line_code,
accounting_line_type_code,
analytical_criterion_code,
analytical_criterion_type_code
order by amb_context_code
) lag_date
from XLA_LINE_DEFN_AC_ASSGNS
order by amb_context_code
) x
where x.amb_context_code = g_staging_context_code
)
where flag = 'False';
trace(p_msg => 'Number of Rows inserted into aads_gt from xla_line_defn_ac_assgns is :'||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'Number of Rows inserted into aads_gt from xla_line_defn_ac_assgns is :'||l_num_rows,
p_component_code => 'merge_journal_line_defns');
INSERT INTO xla_aads_gt
( event_class_code
,event_type_code
,line_definition_code
,accounting_line_code
,mpa_accounting_line_code
,table_name
)
select mpa_jlt_assgns.event_class_code
,mpa_jlt_assgns.event_type_code
,mpa_jlt_assgns.line_definition_code
,mpa_jlt_assgns.accounting_line_code
,mpa_jlt_assgns.mpa_accounting_line_code
,'XLA_MPA_JLT_ASSGNS'
from XLA_MPA_JLT_ASSGNS mpa_jlt_assgns
where mpa_jlt_assgns.application_id = g_application_id
and mpa_jlt_assgns.amb_context_code = g_staging_context_code
AND EXISTS (
SELECT 1
FROM xla_acct_line_types_b xal
WHERE xal.application_id = g_application_id
AND xal.amb_context_code = g_staging_context_code
AND xal.event_class_code = mpa_jlt_assgns.event_class_code
AND xal.accounting_line_type_code = mpa_jlt_assgns.accounting_line_type_code
AND xal.accounting_line_code = mpa_jlt_assgns.accounting_line_code
AND xal.mpa_option_code = 'ACCRUAL')
AND (EXISTS (SELECT 1
FROM XLA_MPA_JLT_ASSGNS mpa_jlt_assgns1
WHERE mpa_jlt_assgns.event_class_code = mpa_jlt_assgns1.event_class_code
and mpa_jlt_assgns.event_type_code = mpa_jlt_assgns1.event_type_code
and mpa_jlt_assgns.line_definition_code = mpa_jlt_assgns1.line_definition_code
and mpa_jlt_assgns.line_definition_owner_code = mpa_jlt_assgns1.line_definition_owner_code
and mpa_jlt_assgns.accounting_line_code = mpa_jlt_assgns1.accounting_line_code
and mpa_jlt_assgns.accounting_line_type_code = mpa_jlt_assgns1.accounting_line_type_code
and mpa_jlt_assgns.mpa_accounting_line_code = mpa_jlt_assgns1.mpa_accounting_line_code
and mpa_jlt_assgns.mpa_accounting_line_type_code = mpa_jlt_assgns1.mpa_accounting_line_type_code
and mpa_jlt_assgns.application_id = mpa_jlt_assgns1.application_id
and mpa_jlt_assgns1.amb_context_code = g_amb_context_code
and to_char(mpa_jlt_assgns.last_update_date,'DD-MON-YYYY') <> to_char(mpa_jlt_assgns1.last_update_date,'DD-MON-YYYY')
)
OR
NOT EXISTS (SELECT 1
FROM XLA_MPA_JLT_ASSGNS mpa_jlt_assgns1
WHERE mpa_jlt_assgns.event_class_code = mpa_jlt_assgns1.event_class_code
and mpa_jlt_assgns.event_type_code = mpa_jlt_assgns1.event_type_code
and mpa_jlt_assgns.line_definition_code = mpa_jlt_assgns1.line_definition_code
and mpa_jlt_assgns.line_definition_owner_code = mpa_jlt_assgns1.line_definition_owner_code
and mpa_jlt_assgns.accounting_line_code = mpa_jlt_assgns1.accounting_line_code
and mpa_jlt_assgns.accounting_line_type_code = mpa_jlt_assgns1.accounting_line_type_code
and mpa_jlt_assgns.mpa_accounting_line_code = mpa_jlt_assgns1.mpa_accounting_line_code
and mpa_jlt_assgns.mpa_accounting_line_type_code = mpa_jlt_assgns1.mpa_accounting_line_type_code
and mpa_jlt_assgns.application_id = mpa_jlt_assgns1.application_id
and mpa_jlt_assgns1.amb_context_code = g_amb_context_code
)
);
trace(p_msg => 'Number of Rows inserted into aads_gt from xla_mpa_jlt_assgns is :'||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'Number of Rows inserted into aads_gt from xla_mpa_jlt_assgns is :'||l_num_rows,
p_component_code => 'merge_journal_line_defns');
INSERT INTO xla_aads_gt
( event_class_code
,event_type_code
,line_definition_code
,accounting_line_code
,analytical_criterion_code
,table_name
)
select mpa_hdr_ac_assgns.event_class_code
,mpa_hdr_ac_assgns.event_type_code
,mpa_hdr_ac_assgns.line_definition_code
,mpa_hdr_ac_assgns.accounting_line_code
,mpa_hdr_ac_assgns.analytical_criterion_code
,'XLA_MPA_HEADER_AC_ASSGNS'
from XLA_MPA_HEADER_AC_ASSGNS mpa_hdr_ac_assgns
where mpa_hdr_ac_assgns.application_id = g_application_id
and mpa_hdr_ac_assgns.amb_context_code = g_staging_context_code
and (EXISTS(SELECT 1
FROM XLA_MPA_HEADER_AC_ASSGNS mpa_hdr_ac_assgns1
WHERE mpa_hdr_ac_assgns.event_class_code = mpa_hdr_ac_assgns1.event_class_code
and mpa_hdr_ac_assgns.event_type_code = mpa_hdr_ac_assgns1.event_type_code
and mpa_hdr_ac_assgns.line_definition_code = mpa_hdr_ac_assgns1.line_definition_code
and mpa_hdr_ac_assgns.line_definition_owner_code = mpa_hdr_ac_assgns1.line_definition_owner_code
and mpa_hdr_ac_assgns.accounting_line_code = mpa_hdr_ac_assgns1.accounting_line_code
and mpa_hdr_ac_assgns.accounting_line_type_code = mpa_hdr_ac_assgns1.accounting_line_type_code
and mpa_hdr_ac_assgns.analytical_criterion_code = mpa_hdr_ac_assgns1.analytical_criterion_code
and mpa_hdr_ac_assgns.analytical_criterion_type_code = mpa_hdr_ac_assgns1.analytical_criterion_type_code
and mpa_hdr_ac_assgns.application_id = mpa_hdr_ac_assgns1.application_id
and mpa_hdr_ac_assgns1.amb_context_code = g_amb_context_code
and to_char(mpa_hdr_ac_assgns.last_update_date,'DD-MON-YYYY') <> to_char(mpa_hdr_ac_assgns1.last_update_date,'DD-MON-YYYY')
UNION
SELECT 1
FROM xla_acct_line_types_b s
WHERE s.application_id = g_application_id
and s.amb_context_code = g_staging_context_code
and s.event_class_code = mpa_hdr_ac_assgns.event_class_code
and s.accounting_line_type_code = mpa_hdr_ac_assgns.accounting_line_type_code
and s.accounting_line_code = mpa_hdr_ac_assgns.accounting_line_code
and s.mpa_option_code = 'NONE'
)
OR
NOT EXISTS(SELECT 1
FROM XLA_MPA_HEADER_AC_ASSGNS mpa_hdr_ac_assgns1
WHERE mpa_hdr_ac_assgns.event_class_code = mpa_hdr_ac_assgns1.event_class_code
and mpa_hdr_ac_assgns.event_type_code = mpa_hdr_ac_assgns1.event_type_code
and mpa_hdr_ac_assgns.line_definition_code = mpa_hdr_ac_assgns1.line_definition_code
and mpa_hdr_ac_assgns.line_definition_owner_code = mpa_hdr_ac_assgns1.line_definition_owner_code
and mpa_hdr_ac_assgns.accounting_line_code = mpa_hdr_ac_assgns1.accounting_line_code
and mpa_hdr_ac_assgns.accounting_line_type_code = mpa_hdr_ac_assgns1.accounting_line_type_code
and mpa_hdr_ac_assgns.analytical_criterion_code = mpa_hdr_ac_assgns1.analytical_criterion_code
and mpa_hdr_ac_assgns.analytical_criterion_type_code = mpa_hdr_ac_assgns1.analytical_criterion_type_code
and mpa_hdr_ac_assgns.application_id = mpa_hdr_ac_assgns1.application_id
and mpa_hdr_ac_assgns1.amb_context_code = g_amb_context_code
UNION
SELECT 1
FROM xla_acct_line_types_b s
WHERE s.application_id = g_application_id
and s.amb_context_code = g_staging_context_code
and s.event_class_code = mpa_hdr_ac_assgns.event_class_code
and s.accounting_line_type_code = mpa_hdr_ac_assgns.accounting_line_type_code
and s.accounting_line_code = mpa_hdr_ac_assgns.accounting_line_code
and s.mpa_option_code = 'NONE'
)
);
trace(p_msg => 'Number of Rows inserted into aads_gt from xla_mpa_header_ac_assgns is :'||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'Number of Rows inserted into aads_gt from xla_mpa_header_ac_assgns is :'||l_num_rows,
p_component_code => 'merge_journal_line_defns');
INSERT INTO xla_aads_gt
(
event_class_code
,event_type_code
,line_definition_code
,accounting_line_code
,mpa_accounting_line_code
,segment_rule_code
,table_name
)
select mpa_jlt_adr_assgns.event_class_code
,mpa_jlt_adr_assgns.event_type_code
,mpa_jlt_adr_assgns.line_definition_code
,mpa_jlt_adr_assgns.accounting_line_code
,mpa_jlt_adr_assgns.mpa_accounting_line_code
,mpa_jlt_adr_assgns.segment_rule_code
,'XLA_MPA_JLT_ADR_ASSGNS'
from XLA_MPA_JLT_ADR_ASSGNS mpa_jlt_adr_assgns
where mpa_jlt_adr_assgns.application_id = g_application_id
and mpa_jlt_adr_assgns.amb_context_code = g_staging_context_code
and EXISTS ( SELECT 1
FROM xla_mpa_jlt_assgns s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND s.event_class_code = mpa_jlt_adr_assgns.event_class_code
AND s.event_type_code = mpa_jlt_adr_assgns.event_type_code
AND s.line_definition_owner_code = mpa_jlt_adr_assgns.line_definition_owner_code
AND s.line_definition_code = mpa_jlt_adr_assgns.line_definition_code
AND s.accounting_line_type_code = mpa_jlt_adr_assgns.accounting_line_type_code
AND s.accounting_line_code = mpa_jlt_adr_assgns.accounting_line_code
AND s.mpa_accounting_line_type_code = mpa_jlt_adr_assgns.mpa_accounting_line_type_code
AND s.mpa_accounting_line_code = mpa_jlt_adr_assgns.mpa_accounting_line_code
UNION
SELECT 1
FROM xla_acct_line_types_b s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND s.event_class_code = mpa_jlt_adr_assgns.event_class_code
AND s.accounting_line_type_code = mpa_jlt_adr_assgns.accounting_line_type_code
AND s.accounting_line_code = mpa_jlt_adr_assgns.accounting_line_code
AND s.mpa_option_code = 'NONE')
and (EXISTS(SELECT 1
FROM XLA_MPA_JLT_ADR_ASSGNS mpa_jlt_adr_assgns1
WHERE mpa_jlt_adr_assgns.event_class_code = mpa_jlt_adr_assgns1.event_class_code
and mpa_jlt_adr_assgns.event_type_code = mpa_jlt_adr_assgns1.event_type_code
and mpa_jlt_adr_assgns.line_definition_code = mpa_jlt_adr_assgns1.line_definition_code
and mpa_jlt_adr_assgns.line_definition_owner_code = mpa_jlt_adr_assgns1.line_definition_owner_code
and mpa_jlt_adr_assgns.accounting_line_code = mpa_jlt_adr_assgns1.accounting_line_code
and mpa_jlt_adr_assgns.accounting_line_type_code = mpa_jlt_adr_assgns1.accounting_line_type_code
and mpa_jlt_adr_assgns.mpa_accounting_line_code = mpa_jlt_adr_assgns1.mpa_accounting_line_code
and mpa_jlt_adr_assgns.mpa_accounting_line_type_code = mpa_jlt_adr_assgns1.mpa_accounting_line_type_code
and mpa_jlt_adr_assgns.segment_rule_code = mpa_jlt_adr_assgns1.segment_rule_code
and mpa_jlt_adr_assgns.segment_rule_type_code = mpa_jlt_adr_assgns1.segment_rule_type_code
and mpa_jlt_adr_assgns.application_id = mpa_jlt_adr_assgns1.application_id
and mpa_jlt_adr_assgns1.amb_context_code = g_amb_context_code
and to_char(mpa_jlt_adr_assgns.last_update_date,'DD-MON-YYYY') <> to_char(mpa_jlt_adr_assgns1.last_update_date,'DD-MON-YYYY')
)
OR
NOT EXISTS(SELECT 1
FROM XLA_MPA_JLT_ADR_ASSGNS mpa_jlt_adr_assgns1
WHERE mpa_jlt_adr_assgns.event_class_code = mpa_jlt_adr_assgns1.event_class_code
and mpa_jlt_adr_assgns.event_type_code = mpa_jlt_adr_assgns1.event_type_code
and mpa_jlt_adr_assgns.line_definition_code = mpa_jlt_adr_assgns1.line_definition_code
and mpa_jlt_adr_assgns.line_definition_owner_code = mpa_jlt_adr_assgns1.line_definition_owner_code
and mpa_jlt_adr_assgns.accounting_line_code = mpa_jlt_adr_assgns1.accounting_line_code
and mpa_jlt_adr_assgns.accounting_line_type_code = mpa_jlt_adr_assgns1.accounting_line_type_code
and mpa_jlt_adr_assgns.mpa_accounting_line_code = mpa_jlt_adr_assgns1.mpa_accounting_line_code
and mpa_jlt_adr_assgns.mpa_accounting_line_type_code = mpa_jlt_adr_assgns1.mpa_accounting_line_type_code
and mpa_jlt_adr_assgns.segment_rule_code = mpa_jlt_adr_assgns1.segment_rule_code
and mpa_jlt_adr_assgns.segment_rule_type_code = mpa_jlt_adr_assgns1.segment_rule_type_code
and mpa_jlt_adr_assgns.application_id = mpa_jlt_adr_assgns1.application_id
and mpa_jlt_adr_assgns1.amb_context_code = g_amb_context_code
)
);
trace(p_msg => 'Number of Rows inserted into aads_gt from xla_mpa_jlt_adr_assgns is :'||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'Number of Rows inserted into aads_gt from xla_mpa_jlt_adr_assgns is :'||l_num_rows,
p_component_code => 'merge_journal_line_defns');
INSERT INTO xla_aads_gt
( event_class_code
,event_type_code
,line_definition_code
,accounting_line_code
,mpa_accounting_line_code
,analytical_criterion_code
,table_name
)
select mpa_jlt_ac_assgns.event_class_code
,mpa_jlt_ac_assgns.event_type_code
,mpa_jlt_ac_assgns.line_definition_code
,mpa_jlt_ac_assgns.accounting_line_code
,mpa_jlt_ac_assgns.mpa_accounting_line_code
,mpa_jlt_ac_assgns.analytical_criterion_code
,'XLA_MPA_JLT_AC_ASSGNS'
from XLA_MPA_JLT_AC_ASSGNS mpa_jlt_ac_assgns
where mpa_jlt_ac_assgns.application_id = g_application_id
and mpa_jlt_ac_assgns.amb_context_code = g_staging_context_code
and (EXISTS(SELECT 1
FROM XLA_MPA_JLT_AC_ASSGNS mpa_jlt_ac_assgns1
WHERE mpa_jlt_ac_assgns.event_class_code = mpa_jlt_ac_assgns1.event_class_code
and mpa_jlt_ac_assgns.event_type_code = mpa_jlt_ac_assgns1.event_type_code
and mpa_jlt_ac_assgns.line_definition_code = mpa_jlt_ac_assgns1.line_definition_code
and mpa_jlt_ac_assgns.line_definition_owner_code = mpa_jlt_ac_assgns1.line_definition_owner_code
and mpa_jlt_ac_assgns.accounting_line_code = mpa_jlt_ac_assgns1.accounting_line_code
and mpa_jlt_ac_assgns.accounting_line_type_code = mpa_jlt_ac_assgns1.accounting_line_type_code
and mpa_jlt_ac_assgns.mpa_accounting_line_code = mpa_jlt_ac_assgns1.mpa_accounting_line_code
and mpa_jlt_ac_assgns.mpa_accounting_line_type_code = mpa_jlt_ac_assgns1.mpa_accounting_line_type_code
and mpa_jlt_ac_assgns.analytical_criterion_code = mpa_jlt_ac_assgns1.analytical_criterion_code
and mpa_jlt_ac_assgns.analytical_criterion_type_code = mpa_jlt_ac_assgns1.analytical_criterion_type_code
and mpa_jlt_ac_assgns.application_id = mpa_jlt_ac_assgns1.application_id
and mpa_jlt_ac_assgns1.amb_context_code = g_amb_context_code
and to_char(mpa_jlt_ac_assgns.last_update_date,'DD-MON-YYYY') <> to_char(mpa_jlt_ac_assgns1.last_update_date,'DD-MON-YYYY')
UNION
SELECT 1
FROM xla_acct_line_types_b s
WHERE s.application_id = g_application_id
and s.amb_context_code = g_staging_context_code
and s.event_class_code = mpa_jlt_ac_assgns.event_class_code
and s.accounting_line_type_code = mpa_jlt_ac_assgns.accounting_line_type_code
and s.accounting_line_code = mpa_jlt_ac_assgns.accounting_line_code
and s.mpa_option_code = 'NONE'
)
OR
NOT EXISTS(SELECT 1
FROM XLA_MPA_JLT_AC_ASSGNS mpa_jlt_ac_assgns1
WHERE mpa_jlt_ac_assgns.event_class_code = mpa_jlt_ac_assgns1.event_class_code
and mpa_jlt_ac_assgns.event_type_code = mpa_jlt_ac_assgns1.event_type_code
and mpa_jlt_ac_assgns.line_definition_code = mpa_jlt_ac_assgns1.line_definition_code
and mpa_jlt_ac_assgns.line_definition_owner_code = mpa_jlt_ac_assgns1.line_definition_owner_code
and mpa_jlt_ac_assgns.accounting_line_code = mpa_jlt_ac_assgns1.accounting_line_code
and mpa_jlt_ac_assgns.accounting_line_type_code = mpa_jlt_ac_assgns1.accounting_line_type_code
and mpa_jlt_ac_assgns.mpa_accounting_line_code = mpa_jlt_ac_assgns1.mpa_accounting_line_code
and mpa_jlt_ac_assgns.mpa_accounting_line_type_code = mpa_jlt_ac_assgns1.mpa_accounting_line_type_code
and mpa_jlt_ac_assgns.analytical_criterion_code = mpa_jlt_ac_assgns1.analytical_criterion_code
and mpa_jlt_ac_assgns.analytical_criterion_type_code = mpa_jlt_ac_assgns1.analytical_criterion_type_code
and mpa_jlt_ac_assgns.application_id = mpa_jlt_ac_assgns1.application_id
and mpa_jlt_ac_assgns1.amb_context_code = g_amb_context_code
UNION
SELECT 1
FROM xla_acct_line_types_b s
WHERE s.application_id = g_application_id
and s.amb_context_code = g_staging_context_code
and s.event_class_code = mpa_jlt_ac_assgns.event_class_code
and s.accounting_line_type_code = mpa_jlt_ac_assgns.accounting_line_type_code
and s.accounting_line_code = mpa_jlt_ac_assgns.accounting_line_code
and s.mpa_option_code = 'NONE'
)
);
trace(p_msg => 'Number of Rows inserted into aads_gt from xla_mpa_jlt_ac_assgns is :'||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'Number of Rows inserted into aads_gt from xla_mpa_jlt_ac_assgns is :'||l_num_rows,
p_component_code => 'merge_journal_line_defns');
DELETE FROM xla_line_definitions_b w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND EXISTS (SELECT 1
FROM xla_line_definitions_b s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND s.event_class_code = w.event_class_code
AND s.event_type_code = w.event_type_code
AND s.line_definition_owner_code = w.line_definition_owner_code
AND s.line_definition_code = w.line_definition_code);
trace(p_msg => '# xla_line_definitions_b deleted : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'# xla_line_definitions_b deleted : '||l_num_rows,
p_component_code => 'merge_journal_line_defns');
DELETE FROM xla_line_definitions_tl w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND EXISTS (SELECT 1
FROM xla_line_definitions_tl s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND s.event_class_code = w.event_class_code
AND s.event_type_code = w.event_type_code
AND s.line_definition_owner_code = w.line_definition_owner_code
AND s.line_definition_code = w.line_definition_code
AND s.language = w.language);
trace(p_msg => '# xla_line_definitions_tl deleted : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'# xla_line_definitions_tl deleted : '||l_num_rows,
p_component_code => 'merge_journal_line_defns');
DELETE FROM xla_line_defn_jlt_assgns w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND EXISTS (SELECT 1
FROM xla_line_defn_jlt_assgns s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND s.event_class_code = w.event_class_code
AND s.event_type_code = w.event_type_code
AND s.line_definition_owner_code = w.line_definition_owner_code
AND s.line_definition_code = w.line_definition_code
AND s.accounting_line_type_code = w.accounting_line_type_code
AND s.accounting_line_code = w.accounting_line_code);
trace(p_msg => '# xla_line_defn_jlt_assgns deleted : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'# xla_line_defn_jlt_assgns deleted : '||l_num_rows,
p_component_code => 'merge_journal_line_defns');
DELETE FROM xla_line_defn_adr_assgns w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND EXISTS (SELECT 1
FROM xla_line_defn_jlt_assgns s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND s.event_class_code = w.event_class_code
AND s.event_type_code = w.event_type_code
AND s.line_definition_owner_code = w.line_definition_owner_code
AND s.line_definition_code = w.line_definition_code
AND s.accounting_line_type_code = w.accounting_line_type_code
AND s.accounting_line_code = w.accounting_line_code);
trace(p_msg => '# xla_line_defn_adr_assgns deleted : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'# xla_line_defn_adr_assgns deleted : '||l_num_rows,
p_component_code => 'merge_journal_line_defns');
DELETE FROM xla_line_defn_ac_assgns w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND EXISTS (SELECT 1
FROM xla_line_defn_ac_assgns s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND s.event_class_code = w.event_class_code
AND s.event_type_code = w.event_type_code
AND s.line_definition_owner_code = w.line_definition_owner_code
AND s.line_definition_code = w.line_definition_code
AND s.accounting_line_type_code = w.accounting_line_type_code
AND s.accounting_line_code = w.accounting_line_code
AND s.analytical_criterion_type_code = w.analytical_criterion_type_code
AND s.analytical_criterion_code = w.analytical_criterion_code);
trace(p_msg => '# xla_line_defn_ac_assgns deleted : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message =>'# xla_line_defn_ac_assgns deleted : '||l_num_rows,
p_component_code => 'merge_journal_line_defns');
DELETE FROM xla_mpa_jlt_assgns w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND EXISTS (
SELECT 1
FROM xla_acct_line_types_b xal
WHERE xal.application_id = g_application_id -- w.application_id -- changed for bug8635648
AND xal.amb_context_code = g_staging_context_code -- w.amb_context_code -- changed for bug8635648
AND xal.event_class_code = w.event_class_code
AND xal.accounting_line_type_code = w.accounting_line_type_code
AND xal.accounting_line_code = w.accounting_line_code
AND xal.mpa_option_code = 'ACCRUAL');
trace(p_msg => '# xla_mpa_jlt_assgns deleted : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_mpa_jlt_assgns deleted : '||l_num_rows,
p_component_code => 'merge_journal_line_defns');
DELETE FROM xla_mpa_header_ac_assgns w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND EXISTS (
SELECT 1
FROM xla_mpa_header_ac_assgns s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND s.event_class_code = w.event_class_code
AND s.event_type_code = w.event_type_code
AND s.line_definition_owner_code = w.line_definition_owner_code
AND s.line_definition_code = w.line_definition_code
AND s.accounting_line_type_code = w.accounting_line_type_code
AND s.accounting_line_code = w.accounting_line_code
AND s.analytical_criterion_type_code = w.analytical_criterion_type_code
AND s.analytical_criterion_code = w.analytical_criterion_code
UNION
SELECT 1
FROM xla_acct_line_types_b s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND s.event_class_code = w.event_class_code
AND s.accounting_line_type_code = w.accounting_line_type_code
AND s.accounting_line_code = w.accounting_line_code
AND s.mpa_option_code = 'NONE');
trace(p_msg => '# xla_mpa_header_ac_assgns deleted : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_mpa_header_ac_assgns deleted : '||l_num_rows,
p_component_code => 'merge_journal_line_defns');
DELETE FROM xla_mpa_jlt_adr_assgns w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND EXISTS (
SELECT 1
FROM xla_mpa_jlt_assgns s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND s.event_class_code = w.event_class_code
AND s.event_type_code = w.event_type_code
AND s.line_definition_owner_code = w.line_definition_owner_code
AND s.line_definition_code = w.line_definition_code
AND s.accounting_line_type_code = w.accounting_line_type_code
AND s.accounting_line_code = w.accounting_line_code
AND s.mpa_accounting_line_type_code = w.mpa_accounting_line_type_code
AND s.mpa_accounting_line_code = w.mpa_accounting_line_code
UNION
SELECT 1
FROM xla_acct_line_types_b s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND s.event_class_code = w.event_class_code
AND s.accounting_line_type_code = w.accounting_line_type_code
AND s.accounting_line_code = w.accounting_line_code
AND s.mpa_option_code = 'NONE');
trace(p_msg => '# xla_mpa_jlt_adr_assgns deleted : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_mpa_jlt_adr_assgns deleted : '||l_num_rows,
p_component_code => 'merge_journal_line_defns');
DELETE FROM xla_mpa_jlt_ac_assgns w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND EXISTS (
SELECT 1
FROM xla_mpa_jlt_ac_assgns s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND s.event_class_code = w.event_class_code
AND s.event_type_code = w.event_type_code
AND s.line_definition_owner_code = w.line_definition_owner_code
AND s.line_definition_code = w.line_definition_code
AND s.accounting_line_type_code = w.accounting_line_type_code
AND s.accounting_line_code = w.accounting_line_code
AND s.mpa_accounting_line_type_code = w.mpa_accounting_line_type_code
AND s.mpa_accounting_line_code = w.mpa_accounting_line_code
AND s.analytical_criterion_type_code = w.analytical_criterion_type_code
AND s.analytical_criterion_code = w.analytical_criterion_code
UNION
SELECT 1
FROM xla_acct_line_types_b s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND s.event_class_code = w.event_class_code
AND s.accounting_line_type_code = w.accounting_line_type_code
AND s.accounting_line_code = w.accounting_line_code
AND s.mpa_option_code = 'NONE');
trace(p_msg => '# xla_mpa_jlt_ac_assgns deleted : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_mpa_jlt_ac_assgns deleted : '||l_num_rows,
p_component_code => 'merge_journal_line_defns');
UPDATE xla_line_definitions_b
SET amb_context_code = g_amb_context_code
WHERE application_id = g_application_id
AND amb_context_code = g_staging_context_code;
trace(p_msg => '# xla_line_definitions_b updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_line_definitions_b updated : '||l_num_rows,
p_component_code => 'merge_journal_line_defns');
UPDATE xla_line_definitions_tl w
SET amb_context_code = g_amb_context_code
WHERE application_id = g_application_id
AND amb_context_code = g_staging_context_code
AND NOT EXISTS (SELECT 1
FROM xla_line_definitions_tl s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_amb_context_code
AND s.event_class_code = w.event_class_code
AND s.event_type_code = w.event_type_code
AND s.line_definition_owner_code = w.line_definition_owner_code
AND s.name = w.name
AND s.language = w.language);
trace(p_msg => '# xla_line_definitions_tl 1 updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_line_definitions_tl 1 updated : '||l_num_rows,
p_component_code => 'merge_journal_line_defns');
UPDATE xla_line_definitions_tl w
SET amb_context_code = g_amb_context_code
, name = substr('('||line_definition_code||') '||name,1,80)
WHERE application_id = g_application_id
AND amb_context_code = g_staging_context_code
AND EXISTS (SELECT 1
FROM xla_line_definitions_tl s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_amb_context_code
AND s.event_class_code = w.event_class_code
AND s.event_type_code = w.event_type_code
AND s.line_definition_owner_code = w.line_definition_owner_code
AND s.name = w.name
AND s.language = w.language);
trace(p_msg => '# xla_line_definitions_tl 2 updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_line_definitions_tl 2 updated : '||l_num_rows,
p_component_code => 'merge_journal_line_defns');
UPDATE xla_line_defn_jlt_assgns
SET amb_context_code = g_amb_context_code
WHERE application_id = g_application_id
AND amb_context_code = g_staging_context_code;
trace(p_msg => '# xla_line_defn_jlt_assgns updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_line_defn_jlt_assgns updated : '||l_num_rows,
p_component_code => 'merge_journal_line_defns');
UPDATE xla_line_defn_adr_assgns
SET amb_context_code = g_amb_context_code
WHERE application_id = g_application_id
AND amb_context_code = g_staging_context_code;
trace(p_msg => '# xla_line_defn_adr_assgns updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_line_defn_adr_assgns updated : '||l_num_rows,
p_component_code => 'merge_journal_line_defns');
UPDATE xla_line_defn_ac_assgns
SET amb_context_code = g_amb_context_code
WHERE application_id = g_application_id
AND amb_context_code = g_staging_context_code;
trace(p_msg => '# xla_line_defn_ac_assgns updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_line_defn_ac_assgns updated : '||l_num_rows,
p_component_code => 'merge_journal_line_defns');
UPDATE xla_mpa_jlt_assgns
SET amb_context_code = g_amb_context_code
WHERE application_id = g_application_id
AND amb_context_code = g_staging_context_code;
trace(p_msg => '# xla_mpa_jlt_assgns updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_mpa_jlt_assgns updated : '||l_num_rows,
p_component_code => 'merge_journal_line_defns');
UPDATE xla_mpa_header_ac_assgns
SET amb_context_code = g_amb_context_code
WHERE application_id = g_application_id
AND amb_context_code = g_staging_context_code;
trace(p_msg => '# xla_mpa_header_ac_assgns updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_mpa_header_ac_assgns updated : '||l_num_rows,
p_component_code => 'merge_journal_line_defns');
UPDATE xla_mpa_jlt_adr_assgns
SET amb_context_code = g_amb_context_code
WHERE application_id = g_application_id
AND amb_context_code = g_staging_context_code;
trace(p_msg => '# xla_mpa_jlt_adr_assgns updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_mpa_jlt_adr_assgns updated : '||l_num_rows,
p_component_code => 'merge_journal_line_defns');
UPDATE xla_mpa_jlt_ac_assgns
SET mpa_inherit_ac_flag = 'N'
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code;
UPDATE xla_mpa_jlt_ac_assgns
SET amb_context_code = g_amb_context_code
WHERE application_id = g_application_id
AND amb_context_code = g_staging_context_code;
trace(p_msg => '# xla_mpa_jlt_ac_assgns updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_mpa_jlt_ac_assgns updated : '||l_num_rows,
p_component_code => 'merge_journal_line_defns');
INSERT INTO xla_aads_gt
( entity_code,
event_class_code,
accounting_line_code,
accounting_class_code,
table_name
)
select entity_code,
event_class_code,
accounting_line_code,
accounting_class_code,
'XLA_ACCT_LINE_TYPES_B'
from
(select entity_code,
event_class_code,
accounting_line_code,
accounting_class_code,
'XLA_ACCT_LINE_TYPES_B',
amb_context_code,
last_update_date ,
nvl2(lag_date, decode(last_update_date,lag_date, 'True','False'),'False') flag
from
(select entity_code,
event_class_code,
accounting_line_code,
accounting_class_code,
'XLA_ACCT_LINE_TYPES_B',
amb_context_code,
last_update_date,
lag(last_update_date) over (PARTITION by application_id,
event_class_code,
accounting_line_code,
accounting_line_type_code
order by amb_context_code
) lag_date
from xla_acct_line_types_b
order by amb_context_code
) x
where x.amb_context_code = g_staging_context_code
)
where flag = 'False';
trace(p_msg => 'Number of Rows inserted into aads_gt from xla_acct_line_types_b is :'||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => 'Number of Rows inserted into aads_gt from xla_acct_line_types_b is :'||l_num_rows,
p_component_code => 'merge_journal_line_types');
INSERT INTO xla_aads_gt
( event_class_code
,accounting_line_code
,accounting_attribute_code
,source_code
,table_name
)
select jlt_acct_attrs.event_class_code
,jlt_acct_attrs.accounting_line_code
,jlt_acct_attrs.accounting_attribute_code
,jlt_acct_attrs.source_code
,'xla_jlt_acct_attrs'
from xla_jlt_acct_attrs jlt_acct_attrs
where jlt_acct_attrs.application_id = g_application_id
and jlt_acct_attrs.amb_context_code = g_staging_context_code
and (EXISTS(SELECT 1
FROM xla_jlt_acct_attrs jlt_acct_attrs1
WHERE jlt_acct_attrs.event_class_code = jlt_acct_attrs1.event_class_code
and jlt_acct_attrs.accounting_line_code = jlt_acct_attrs1.accounting_line_code
and jlt_acct_attrs.accounting_line_type_code = jlt_acct_attrs1.accounting_line_type_code
and nvl(jlt_acct_attrs.accounting_attribute_code,' ') = nvl(jlt_acct_attrs1.accounting_attribute_code,' ')
and nvl(jlt_acct_attrs.source_code,' ') = nvl(jlt_acct_attrs1.source_code,' ')
and jlt_acct_attrs.application_id = jlt_acct_attrs1.application_id
and jlt_acct_attrs1.amb_context_code = g_amb_context_code
and to_char(jlt_acct_attrs.last_update_date,'DD-MON-YYYY') <> to_char(jlt_acct_attrs1.last_update_date,'DD-MON-YYYY')
)
OR
NOT EXISTS(SELECT 1
FROM xla_jlt_acct_attrs jlt_acct_attrs1
WHERE jlt_acct_attrs.event_class_code = jlt_acct_attrs1.event_class_code
and jlt_acct_attrs.accounting_line_code = jlt_acct_attrs1.accounting_line_code
and jlt_acct_attrs.accounting_line_type_code = jlt_acct_attrs1.accounting_line_type_code
and nvl(jlt_acct_attrs.accounting_attribute_code,' ') = nvl(jlt_acct_attrs1.accounting_attribute_code,' ')
and nvl(jlt_acct_attrs.source_code,' ') = nvl(jlt_acct_attrs1.source_code,' ')
and jlt_acct_attrs.application_id = jlt_acct_attrs1.application_id
and jlt_acct_attrs1.amb_context_code = g_amb_context_code
)
);
trace(p_msg => 'Number of Rows inserted into aads_gt from xla_jlt_acct_attrs is :'||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => 'Number of Rows inserted into aads_gt from xla_jlt_acct_attrs is :'||l_num_rows,
p_component_code => 'merge_journal_line_types');
INSERT INTO xla_aads_gt
( entity_code
,event_class_code
,accounting_line_code
,source_code
,table_name
)
select condn.entity_code
,condn.event_class_code
,condn.accounting_line_code
,condn.source_code
,'xla_conditions'
from xla_conditions condn
where condn.application_id = g_application_id
and condn.amb_context_code = g_staging_context_code
and EXISTS (SELECT 1
FROM xla_acct_line_types_b s
WHERE s.application_id = g_application_id
and s.amb_context_code = g_staging_context_code
and s.event_class_code = condn.event_class_code
and s.accounting_line_type_code = condn.accounting_line_type_code
and s.accounting_line_code = condn.accounting_line_code)
and (EXISTS(SELECT 1
FROM xla_conditions condn1
WHERE condn.entity_code = condn1.entity_code
and condn.event_class_code = condn1.event_class_code
and condn.accounting_line_code = condn1.accounting_line_code
and condn.accounting_line_type_code = condn1.accounting_line_type_code
and nvl(condn.source_code,' ') = nvl(condn1.source_code,' ')
and nvl(condn.source_type_code,' ') = nvl(condn1.source_type_code,' ')
and condn.user_sequence = condn1.user_sequence
and condn.application_id = condn1.application_id
and condn1.amb_context_code = g_amb_context_code
and to_char(condn.last_update_date,'DD-MON-YYYY') <> to_char(condn1.last_update_date,'DD-MON-YYYY')
)
OR
NOT EXISTS(SELECT 1
FROM xla_conditions condn1
WHERE nvl(condn.entity_code,' ') = nvl(condn1.entity_code,' ')
and nvl(condn.event_class_code,' ') = nvl(condn1.event_class_code,' ')
and nvl(condn.accounting_line_code,' ') = nvl(condn1.accounting_line_code,' ')
and nvl(condn.accounting_line_type_code,' ') = nvl(condn1.accounting_line_type_code,' ')
and nvl(condn.source_code,' ') = nvl(condn1.source_code,' ')
and nvl(condn.source_type_code,' ') = nvl(condn1.source_type_code,' ')
and condn.user_sequence = condn1.user_sequence
and condn.application_id = condn1.application_id
and condn1.amb_context_code = g_amb_context_code
)
);
trace(p_msg => 'Number of Rows inserted into aads_gt from xla_conditions is :'||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => 'Number of Rows inserted into aads_gt from xla_conditions is :'||l_num_rows,
p_component_code => 'merge_journal_line_types');
DELETE FROM xla_acct_line_types_b w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND EXISTS (SELECT 1
FROM xla_acct_line_types_b s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND s.event_class_code = w.event_class_code
AND s.accounting_line_type_code = w.accounting_line_type_code
AND s.accounting_line_code = w.accounting_line_code);
trace(p_msg => '# xla_acct_line_types_b delete : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_acct_line_types_b delete : '||l_num_rows,
p_component_code => 'merge_journal_line_types');
DELETE FROM xla_acct_line_types_tl w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND EXISTS (SELECT 1
FROM xla_acct_line_types_tl s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND s.event_class_code = w.event_class_code
AND s.accounting_line_type_code = w.accounting_line_type_code
AND s.accounting_line_code = w.accounting_line_code
AND s.language = w.language);
trace(p_msg => '# xla_acct_line_types_tl delete : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_acct_line_types_tl delete : '||l_num_rows,
p_component_code => 'merge_journal_line_types');
DELETE FROM xla_jlt_acct_attrs w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND EXISTS (SELECT 1
FROM xla_jlt_acct_attrs s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND s.event_class_code = w.event_class_code
AND s.accounting_line_type_code = w.accounting_line_type_code
AND s.accounting_line_code = w.accounting_line_code);
trace(p_msg => '# xla_jlt_acct_attrs delete : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_jlt_acct_attrs delete : '||l_num_rows,
p_component_code => 'merge_journal_line_types');
DELETE FROM xla_conditions w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND EXISTS (SELECT 1
FROM xla_acct_line_types_b s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND s.event_class_code = w.event_class_code
AND s.accounting_line_type_code = w.accounting_line_type_code
AND s.accounting_line_code = w.accounting_line_code);
trace(p_msg => '# xla_jlt_acct_attrs delete : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_jlt_acct_attrs delete : '||l_num_rows,
p_component_code => 'merge_journal_line_types');
UPDATE xla_acct_line_types_b
SET amb_context_code = g_amb_context_code
WHERE application_id = g_application_id
AND amb_context_code = g_staging_context_code;
trace(p_msg => '# xla_acct_line_types_b updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_acct_line_types_b updated : '||l_num_rows,
p_component_code => 'merge_journal_line_types');
UPDATE xla_acct_line_types_tl w
SET amb_context_code = g_amb_context_code
WHERE application_id = g_application_id
AND amb_context_code = g_staging_context_code
AND NOT EXISTS (SELECT 1
FROM xla_acct_line_types_tl s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_amb_context_code
AND s.event_class_code = w.event_class_code
AND s.accounting_line_type_code = w.accounting_line_type_code
AND s.name = w.name
AND s.language = w.language);
trace(p_msg => '# xla_acct_line_types_tl 1 updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_acct_line_types_tl 1 updated : '||l_num_rows,
p_component_code => 'merge_journal_line_types');
UPDATE xla_acct_line_types_tl w
SET amb_context_code = g_amb_context_code
, name = substr('('||w.accounting_line_code||') '||name,1,80)
WHERE application_id = g_application_id
AND amb_context_code = g_staging_context_code
AND EXISTS (SELECT 1
FROM xla_acct_line_types_tl s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_amb_context_code
AND s.event_class_code = w.event_class_code
AND s.accounting_line_type_code = w.accounting_line_type_code
AND s.name = w.name
AND s.language = w.language);
trace(p_msg => '# xla_acct_line_types_tl 2 updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_acct_line_types_tl 2 updated : '||l_num_rows,
p_component_code => 'merge_journal_line_types');
UPDATE xla_jlt_acct_attrs
SET amb_context_code = g_amb_context_code
WHERE application_id = g_application_id
AND amb_context_code = g_staging_context_code;
trace(p_msg => '# xla_jlt_acct_attrs updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_jlt_acct_attrs updated : '||l_num_rows,
p_component_code => 'merge_journal_line_types');
UPDATE xla_conditions
SET amb_context_code = g_amb_context_code
WHERE amb_context_code = g_staging_context_code
AND application_id = g_application_id
AND accounting_line_code IS NOT NULL;
trace(p_msg => '# xla_conditions updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_conditions updated : '||l_num_rows,
p_component_code => 'merge_journal_line_types');
INSERT INTO xla_aads_gt
(description_code,
table_name
)
select description_code,
'XLA_DESCRIPTIONS_B'
from
(select description_code,
'XLA_DESCRIPTIONS_B',
amb_context_code,
last_update_date ,
nvl2(lag_date, decode(last_update_date,lag_date, 'True','False'),'False') flag
from
(select description_code,
'XLA_DESCRIPTIONS_B',
amb_context_code,
last_update_date ,
lag(last_update_date) over (PARTITION by application_id,
description_code,
description_type_code
order by amb_context_code
) lag_date
from xla_descriptions_b
order by amb_context_code
) x
where x.amb_context_code = g_staging_context_code
)
where flag = 'False';
trace(p_msg => 'Number of Rows inserted into aads_gt from xla_descriptions_b is :'||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => 'Number of Rows inserted into aads_gt from xla_descriptions_b is :'||l_num_rows,
p_component_code => 'merge_descriptions');
INSERT INTO xla_aads_gt
( description_code,
table_name
)
select description_code,
'XLA_DESC_PRIORITIES'
from
(select description_code,
'XLA_DESC_PRIORITIES',
amb_context_code,
last_update_date ,
nvl2(lag_date, decode(last_update_date,lag_date, 'True','False'),'False') flag
from
(select description_code,
'XLA_DESC_PRIORITIES',
amb_context_code,
last_update_date ,
lag(last_update_date) over (PARTITION by application_id,
description_code,
description_type_code
order by amb_context_code
) lag_date
from xla_desc_priorities
order by amb_context_code
) x
where x.amb_context_code = g_staging_context_code
)
where flag = 'False';
trace(p_msg => 'Number of Rows inserted into aads_gt from xla_desc_priorities is :'||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => 'Number of Rows inserted into aads_gt from xla_desc_priorities is :'||l_num_rows,
p_component_code => 'merge_descriptions');
INSERT INTO xla_aads_gt
( source_code,
table_name
)
SELECT desc_details.source_code,
'xla_descript_details_b'
FROM xla_descript_details_b desc_details
WHERE desc_details.amb_context_code = g_staging_context_code
AND desc_details.description_prio_id IN
(SELECT w.description_prio_id
FROM xla_desc_priorities w
,xla_desc_priorities s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND w.application_id = g_application_id
AND w.amb_context_code = g_amb_context_code
AND w.description_type_code = s.description_type_code
AND w.description_code = s.description_code)
AND (EXISTS (SELECT 1
FROM xla_descript_details_b desc_details1
WHERE desc_details1.amb_context_code = g_amb_context_code
AND desc_details.description_prio_id = desc_details1.description_prio_id
AND to_char(desc_details.last_update_date,'DD-MON-YYYY') <> to_char(desc_details1.last_update_date,'DD-MON-YYYY')
)
OR
NOT EXISTS (SELECT 1
FROM xla_descript_details_b desc_details1
WHERE desc_details1.amb_context_code = g_amb_context_code
AND desc_details.description_prio_id = desc_details1.description_prio_id
)
);
trace(p_msg => 'Number of Rows inserted into aads_gt from xla_descript_details_b is :'||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => 'Number of Rows inserted into aads_gt from xla_descript_details_b is :'||l_num_rows,
p_component_code => 'merge_descriptions');
DELETE FROM xla_descriptions_b w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND EXISTS (SELECT 1
FROM xla_descriptions_b s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND s.description_type_code = w.description_type_code
AND s.description_code = w.description_code);
trace(p_msg => '# xla_descriptions_b delete : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_descriptions_b delete : '||l_num_rows,
p_component_code => 'merge_descriptions');
DELETE FROM xla_descriptions_tl w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND EXISTS (SELECT 1
FROM xla_descriptions_tl s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND s.description_type_code = w.description_type_code
AND s.description_code = w.description_code
AND s.language = w.language);
trace(p_msg => '# xla_descriptions_tl delete : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_descriptions_tl delete : '||l_num_rows,
p_component_code => 'merge_descriptions');
DELETE FROM xla_desc_priorities w
WHERE application_id = g_application_id
AND amb_context_code = g_amb_context_code
AND EXISTS (SELECT 1
FROM xla_desc_priorities s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND s.description_type_code = w.description_type_code
AND s.description_code = w.description_code);
trace(p_msg => '# xla_desc_priorities delete : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_desc_priorities delete : '||l_num_rows,
p_component_code => 'merge_descriptions');
DELETE FROM xla_conditions
WHERE description_prio_id IN
(SELECT w.description_prio_id
FROM xla_desc_priorities w
, xla_desc_priorities s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND w.application_id = g_application_id
AND w.amb_context_code = g_amb_context_code
AND w.description_type_code = s.description_type_code
AND w.description_code = s.description_code);
trace(p_msg => '# xla_conditions delete : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_conditions delete : '||l_num_rows,
p_component_code => 'merge_descriptions');
DELETE FROM xla_descript_details_b
WHERE description_prio_id IN
(SELECT w.description_prio_id
FROM xla_desc_priorities w
, xla_desc_priorities s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND w.application_id = g_application_id
AND w.amb_context_code = g_amb_context_code
AND w.description_type_code = s.description_type_code
AND w.description_code = s.description_code);
trace(p_msg => '# xla_descript_details_b delete : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_descript_details_b delete : '||l_num_rows,
p_component_code => 'merge_descriptions');
DELETE FROM xla_descript_details_tl w
WHERE description_detail_id IN
(SELECT description_detail_id
FROM xla_descript_details_b d
, xla_desc_priorities w
, xla_desc_priorities s
WHERE d.description_prio_id = w.description_prio_id
AND s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND s.application_id = g_application_id
AND s.amb_context_code = g_amb_context_code
AND w.description_type_code = s.description_type_code
AND w.description_code = s.description_code);
trace(p_msg => '# xla_descript_details_tl delete : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_descript_details_tl delete : '||l_num_rows,
p_component_code => 'merge_descriptions');
UPDATE xla_descriptions_b
SET amb_context_code = g_amb_context_code
WHERE application_id = g_application_id
AND amb_context_code = g_staging_context_code;
trace(p_msg => '# xla_descriptions_b updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_descriptions_b updated : '||l_num_rows,
p_component_code => 'merge_descriptions');
UPDATE xla_descriptions_tl w
SET amb_context_code = g_amb_context_code
WHERE application_id = g_application_id
AND amb_context_code = g_staging_context_code
AND NOT EXISTS (SELECT 1
FROM xla_descriptions_tl s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_amb_context_code
AND s.description_type_code = w.description_type_code
AND s.name = w.name
AND s.language = w.language);
trace(p_msg => '# xla_descriptions_tl 1 updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_descriptions_tl 1 updated : '||l_num_rows,
p_component_code => 'merge_descriptions');
UPDATE xla_descriptions_tl w
SET amb_context_code = g_amb_context_code
, name = substr('('||w.description_code||') '||name,1,80)
WHERE application_id = g_application_id
AND amb_context_code = g_staging_context_code
AND EXISTS (SELECT 1
FROM xla_descriptions_tl s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_amb_context_code
AND s.description_type_code = w.description_type_code
AND s.name = w.name
AND s.language = w.language);
trace(p_msg => '# xla_descriptions_tl 2 updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_descriptions_tl 2 updated : '||l_num_rows,
p_component_code => 'merge_descriptions');
UPDATE xla_desc_priorities
SET amb_context_code = g_amb_context_code
WHERE application_id = g_application_id
AND amb_context_code = g_staging_context_code;
trace(p_msg => '# xla_desc_priorities updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_desc_priorities updated : '||l_num_rows,
p_component_code => 'merge_descriptions');
UPDATE xla_conditions
SET amb_context_code = g_amb_context_code
WHERE amb_context_code = g_staging_context_code
AND application_id = g_application_id
AND description_prio_id IS NOT NULL;
trace(p_msg => '# xla_conditions updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_conditions updated : '||l_num_rows,
p_component_code => 'merge_descriptions');
UPDATE xla_descript_details_b
SET amb_context_code = g_amb_context_code
WHERE amb_context_code = g_staging_context_code;
trace(p_msg => '# xla_descript_details_b updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_descript_details_b updated : '||l_num_rows,
p_component_code => 'merge_descriptions');
UPDATE xla_descript_details_tl
SET amb_context_code = g_amb_context_code
WHERE amb_context_code = g_staging_context_code;
trace(p_msg => '# xla_descript_details_tl updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_descript_details_tl updated : '||l_num_rows,
p_component_code => 'merge_descriptions');
SELECT s.analytical_criterion_type_code, s.analytical_criterion_code
FROM xla_analytical_hdrs_b s
, xla_analytical_hdrs_b w
WHERE s.amb_context_code = g_staging_context_code
AND w.amb_context_code(+) = g_amb_context_code
AND s.analytical_criterion_type_code = w.analytical_criterion_type_code(+)
AND s.analytical_criterion_code = w.analytical_criterion_code(+)
AND s.version_num >= w.version_num(+);
SELECT s.analytical_criterion_type_code, s.analytical_criterion_code,s.ANALYTICAL_DETAIL_CODE
FROM xla_analytical_dtls_b s
, xla_analytical_dtls_b w
WHERE s.amb_context_code = g_staging_context_code
AND w.amb_context_code(+) = g_amb_context_code
AND s.analytical_criterion_type_code = w.analytical_criterion_type_code(+)
AND s.analytical_criterion_code = w.analytical_criterion_code(+)
AND s.ANALYTICAL_DETAIL_CODE = w.ANALYTICAL_DETAIL_CODE(+) ;
SELECT s.analytical_criterion_type_code, s.analytical_criterion_code,s.ANALYTICAL_DETAIL_CODE,
s.event_class_code,s.entity_code,s.source_code,s.source_type_code
FROM xla_analytical_sources s
, xla_analytical_sources w
WHERE s.amb_context_code = g_staging_context_code
AND w.amb_context_code(+) = g_amb_context_code
AND s.analytical_criterion_type_code = w.analytical_criterion_type_code(+)
AND s.analytical_criterion_code = w.analytical_criterion_code(+)
AND s.ANALYTICAL_DETAIL_CODE = w.ANALYTICAL_DETAIL_CODE(+)
AND s.event_class_code = w.event_class_code(+)
AND s.entity_code = w.entity_code(+)
AND s.source_code = w.source_code(+)
AND s.source_type_code = w.source_type_code(+);
INSERT INTO xla_aads_gt
( analytical_criterion_code,
table_name
)
select analytical_criterion_code,
'XLA_ANALYTICAL_HDRS_B'
from
(select analytical_criterion_code,
'XLA_ANALYTICAL_HDRS_B',
amb_context_code,
last_update_date ,
nvl2(lag_date, decode(last_update_date,lag_date, 'True','False'),'False') flag
from
(select analytical_criterion_code,
'XLA_ANALYTICAL_HDRS_B',
amb_context_code,
last_update_date ,
lag(last_update_date) over (PARTITION by application_id,
analytical_criterion_code,
analytical_criterion_type_code
order by amb_context_code
) lag_date
from xla_analytical_hdrs_b
order by amb_context_code
) x
where x.amb_context_code = g_staging_context_code
)
where flag = 'False';
trace(p_msg => 'Number of Rows inserted into aads_gt from xla_analytical_hdrs_b is :'||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => 'Number of Rows inserted into aads_gt from xla_analytical_hdrs_b is :'||l_num_rows,
p_component_code => 'merge_analytical_criteria');
INSERT INTO xla_aads_gt
( analytical_criterion_code,
analytical_detail_code,
table_name
)
select analytical_criterion_code,
analytical_detail_code,
'XLA_ANALYTICAL_DTLS_B'
from
(select analytical_criterion_code,
analytical_detail_code,
'XLA_ANALYTICAL_DTLS_B',
amb_context_code,
last_update_date ,
nvl2(lag_date, decode(last_update_date,lag_date, 'True','False'),'False') flag
from
(select analytical_criterion_code,
analytical_detail_code,
'XLA_ANALYTICAL_DTLS_B',
amb_context_code,
last_update_date ,
lag(last_update_date) over (PARTITION by analytical_criterion_code,
analytical_criterion_type_code,
analytical_detail_code
order by amb_context_code
) lag_date
from xla_analytical_dtls_b
order by amb_context_code
) x
where x.amb_context_code = g_staging_context_code
)
where flag = 'False';
trace(p_msg => 'Number of Rows inserted into aads_gt from xla_analytical_dtls_b is :'||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => 'Number of Rows inserted into aads_gt from xla_analytical_dtls_b is :'||l_num_rows,
p_component_code => 'merge_analytical_criteria');
INSERT INTO xla_aads_gt
( analytical_criterion_code,
analytical_detail_code,
entity_code,
event_class_code,
source_code,
table_name
)
select analytical_criterion_code,
analytical_detail_code,
entity_code,
event_class_code,
source_code,
'XLA_ANALYTICAL_SOURCES'
from
(select analytical_criterion_code,
analytical_detail_code,
entity_code,
event_class_code,
source_code,
'XLA_ANALYTICAL_SOURCES',
amb_context_code,
last_update_date ,
nvl2(lag_date, decode(last_update_date,lag_date, 'True','False'),'False') flag
from
(select analytical_criterion_code,
analytical_detail_code,
entity_code,
event_class_code,
source_code,
'XLA_ANALYTICAL_SOURCES',
amb_context_code,
last_update_date ,
lag(last_update_date) over (PARTITION by application_id,
analytical_criterion_code,
analytical_criterion_type_code,
analytical_detail_code,
entity_code,
event_class_code,
source_code,
source_type_code
order by amb_context_code
) lag_date
from xla_analytical_sources
order by amb_context_code
) x
where x.amb_context_code = g_staging_context_code
)
where flag = 'False';
trace(p_msg => 'Number of Rows inserted into aads_gt from xla_analytical_sources is :'||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => 'Number of Rows inserted into aads_gt from xla_analytical_sources is :'||l_num_rows,
p_component_code => 'merge_analytical_criteria');
Need to keep sources separate, as the delete should remove only those sources that r in the ldt NOT sources that
belong to a header/dtl loaded.
*/
OPEN c_ac;
DELETE FROM xla_analytical_hdrs_b w
WHERE amb_context_code = g_amb_context_code
AND analytical_criterion_type_code = l_ac_type_codes(i)
AND analytical_criterion_code = l_ac_codes(i)
AND EXISTS
( SELECT 1
FROM xla_analytical_hdrs_b s
WHERE s.amb_context_code = g_staging_context_code
AND s.analytical_criterion_type_code = w.analytical_criterion_type_code
AND s.analytical_criterion_code = w.analytical_criterion_code );
trace(p_msg => '# xla_analytical_hdrs_b delete : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_analytical_hdrs_b delete : '||l_num_rows,
p_component_code => 'merge_analytical_criteria');
DELETE FROM xla_analytical_hdrs_tl w
WHERE amb_context_code = g_amb_context_code
AND analytical_criterion_type_code = l_ac_type_codes(i)
AND analytical_criterion_code = l_ac_codes(i)
AND EXISTS
( SELECT 1
FROM xla_analytical_hdrs_tl s
WHERE s.amb_context_code = g_staging_context_code
AND s.analytical_criterion_type_code = w.analytical_criterion_type_code
AND s.analytical_criterion_code = w.analytical_criterion_code
AND s.language = w.language);
trace(p_msg => '# xla_analytical_hdrs_tl delete : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_analytical_hdrs_tl delete : '||l_num_rows,
p_component_code => 'merge_analytical_criteria');
DELETE FROM xla_analytical_dtls_b w
WHERE amb_context_code = g_amb_context_code
AND analytical_criterion_type_code = l_ac_dtl_type_codes(i)
AND analytical_criterion_code = l_ac_dtl_codes(i)
AND analytical_detail_code = l_ac_detail_code(i)
AND EXISTS
( SELECT 1
FROM xla_analytical_dtls_b s
WHERE s.amb_context_code = g_staging_context_code
AND s.analytical_criterion_type_code = w.analytical_criterion_type_code
AND s.analytical_criterion_code = w.analytical_criterion_code
AND s.analytical_detail_code = w.analytical_detail_code);
trace(p_msg => '# xla_analytical_dtls_b delete : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'UPLOAD',
p_encoded_message => '# row populated in XLA_STAGING_COMPONENTS_H = '||l_num_rows,
p_component_code => 'populate_history');
DELETE FROM xla_analytical_dtls_tl w
WHERE amb_context_code = g_amb_context_code
AND analytical_criterion_type_code = l_ac_dtl_type_codes(i)
AND analytical_criterion_code = l_ac_dtl_codes(i)
AND analytical_detail_code = l_ac_detail_code(i)
AND EXISTS
( SELECT 1
FROM xla_analytical_dtls_tl s
WHERE s.amb_context_code = g_staging_context_code
AND s.analytical_criterion_type_code = w.analytical_criterion_type_code
AND s.analytical_criterion_code = w.analytical_criterion_code
AND s.analytical_detail_code = w.analytical_detail_code
AND s.language = w.language);
trace(p_msg => '# xla_analytical_dtls_tl delete : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_analytical_dtls_tl delete : '||l_num_rows,
p_component_code => 'merge_analytical_criteria');
DELETE FROM xla_analytical_sources w
WHERE amb_context_code = g_amb_context_code
AND analytical_criterion_type_code = l_ac_src_type_codes(i) --Bug 10060589
AND analytical_criterion_code = l_ac_src_codes(i) --Bug 10060589
AND analytical_detail_code = l_ac_src_detail_code(i) --Bug 10060589 -- Added for bug 8268819
AND event_class_code =l_ac_src_event_class(i)
AND ENTITY_CODE =l_ac_src_entity_code(i)
AND SOURCE_CODE =l_ac_src_source_code(i)
AND SOURCE_TYPE_CODE =l_ac_src_source_type_code(i)
AND EXISTS
( SELECT 1
FROM xla_analytical_sources s
WHERE s.amb_context_code = g_staging_context_code
AND s.analytical_criterion_type_code = w.analytical_criterion_type_code
AND s.analytical_criterion_code = w.analytical_criterion_code
AND s.analytical_detail_code = w.analytical_detail_code -- Added for bug 8268819
AND s.entity_code = w.entity_code
AND s.event_class_code = w.event_class_code
AND s.source_code = w.source_code
AND s.source_type_code = w.source_type_code);
trace(p_msg => '# xla_analytical_sources delete : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_analytical_sources delete : '||l_num_rows,
p_component_code => 'merge_analytical_criteria');
UPDATE xla_analytical_hdrs_b
SET amb_context_code = g_amb_context_code
WHERE amb_context_code = g_staging_context_code
AND analytical_criterion_type_code = l_ac_type_codes(i)
AND analytical_criterion_code = l_ac_codes(i);
trace(p_msg => '# xla_analytical_hdrs_b updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_analytical_hdrs_b updated : '||l_num_rows,
p_component_code => 'merge_analytical_criteria');
UPDATE xla_analytical_hdrs_tl s
SET amb_context_code = g_amb_context_code
WHERE amb_context_code = g_staging_context_code
AND analytical_criterion_type_code = l_ac_type_codes(i)
AND analytical_criterion_code = l_ac_codes(i)
AND NOT EXISTS (SELECT 1
FROM xla_analytical_hdrs_tl w
WHERE w.amb_context_code = g_amb_context_code
AND w.analytical_criterion_type_code = s.analytical_criterion_type_code
AND w.name = s.name
AND w.language = s.language);
trace(p_msg => '# xla_analytical_hdrs_tl 1 updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_analytical_hdrs_tl 1 updated : '||l_num_rows,
p_component_code => 'merge_analytical_criteria');
UPDATE xla_analytical_hdrs_tl s
SET amb_context_code = g_amb_context_code
, name = substr('('||s.analytical_criterion_code||') '||name,1,80)
WHERE amb_context_code = g_staging_context_code
AND analytical_criterion_type_code = l_ac_type_codes(i)
AND analytical_criterion_code = l_ac_codes(i)
AND EXISTS (SELECT 1
FROM xla_analytical_hdrs_tl w
WHERE w.amb_context_code = g_amb_context_code
AND w.analytical_criterion_type_code = s.analytical_criterion_type_code
AND w.name = s.name
AND w.language = s.language);
trace(p_msg => '# xla_analytical_hdrs_tl 2 updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_analytical_hdrs_tl 2 updated : '||l_num_rows,
p_component_code => 'merge_analytical_criteria');
UPDATE xla_analytical_dtls_b
SET amb_context_code = g_amb_context_code
WHERE amb_context_code = g_staging_context_code
AND analytical_criterion_type_code = l_ac_dtl_type_codes(i)
AND analytical_criterion_code = l_ac_dtl_codes(i)
AND analytical_detail_code = l_ac_detail_code(i);
trace(p_msg => '# xla_analytical_dtls_b updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_analytical_dtls_b updated : '||l_num_rows,
p_component_code => 'merge_analytical_criteria');
UPDATE xla_analytical_dtls_tl s
SET amb_context_code = g_amb_context_code
WHERE amb_context_code = g_staging_context_code
AND analytical_criterion_type_code = l_ac_dtl_type_codes(i)
AND analytical_criterion_code = l_ac_dtl_codes(i)
AND analytical_detail_code = l_ac_detail_code(i)
AND NOT EXISTS (SELECT 1
FROM xla_analytical_dtls_tl w
WHERE w.amb_context_code = g_amb_context_code
AND w.analytical_criterion_type_code = s.analytical_criterion_type_code
AND s.analytical_criterion_code = s.analytical_criterion_code
AND s.analytical_detail_code = s.analytical_detail_code
AND w.name = s.name
AND w.language = s.language);
trace(p_msg => '# xla_analytical_dtls_tl 1 updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_analytical_dtls_tl 1 updated : '||l_num_rows,
p_component_code => 'merge_analytical_criteria');
UPDATE xla_analytical_dtls_tl w
SET amb_context_code = g_amb_context_code
, name = substr('('||w.analytical_detail_code||') '||name,1,80)
WHERE amb_context_code = g_staging_context_code
AND analytical_criterion_type_code = l_ac_dtl_type_codes(i)
AND analytical_criterion_code = l_ac_dtl_codes(i)
AND analytical_detail_code = l_ac_detail_code(i)
AND EXISTS (SELECT 1
FROM xla_analytical_dtls_tl s
WHERE s.amb_context_code = g_amb_context_code
AND w.analytical_criterion_type_code = s.analytical_criterion_type_code
AND s.analytical_criterion_code = s.analytical_criterion_code
AND s.analytical_detail_code = s.analytical_detail_code
AND s.name = w.name
AND s.language = w.language);
trace(p_msg => '# xla_analytical_dtls_tl 2 updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_analytical_dtls_tl 2 updated : '||l_num_rows,
p_component_code => 'merge_analytical_criteria');
UPDATE xla_analytical_sources
SET amb_context_code = g_amb_context_code
WHERE amb_context_code = g_staging_context_code
AND analytical_criterion_type_code = l_ac_src_type_codes(i)--Bug 10060589
AND analytical_criterion_code = l_ac_src_codes(i) --Bug 10060589
AND analytical_detail_code = l_ac_src_detail_code(i) --Bug 10060589 -- Added for bug 8268819
AND event_class_code =l_ac_src_event_class(i)
AND ENTITY_CODE =l_ac_src_entity_code(i)
AND SOURCE_CODE =l_ac_src_source_code(i)
AND SOURCE_TYPE_CODE =l_ac_src_source_type_code(i);
trace(p_msg => '# xla_analytical_sources updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_analytical_sources updated : '||l_num_rows,
p_component_code => 'merge_analytical_criteria');
SELECT s.segment_rule_type_code, s.segment_rule_code
FROM xla_seg_rules_b w
, xla_seg_rules_b s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND s.segment_rule_type_code = w.segment_rule_type_code
AND s.segment_rule_code = w.segment_rule_code
AND w.application_id = g_application_id
AND w.amb_context_code = g_amb_context_code;
INSERT INTO xla_aads_gt
( segment_rule_code,
table_name
)
select segment_rule_code,
'XLA_SEG_RULE_DETAILS'
from
(select segment_rule_code,
'XLA_SEG_RULE_DETAILS',
amb_context_code,
last_update_date ,
nvl2(lag_date, decode(last_update_date,lag_date, 'True','False'),'False') flag
from
(select segment_rule_code,
'XLA_SEG_RULE_DETAILS',
amb_context_code,
last_update_date ,
lag(last_update_date) over (PARTITION by application_id,
segment_rule_code,
segment_rule_type_code,
user_sequence
order by amb_context_code
) lag_date
from xla_seg_rule_details
order by amb_context_code
) x
where x.amb_context_code = g_staging_context_code
)
where flag = 'False';
trace(p_msg => 'Number of Rows inserted into aads_gt from xla_seg_rule_details is :'||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => 'Number of Rows inserted into aads_gt from xla_seg_rule_details is :'||l_num_rows,
p_component_code => 'merge_adrs');
INSERT INTO xla_aads_gt
( segment_rule_code,
table_name
)
select segment_rule_code,
'XLA_SEG_RULES_B'
from
(select segment_rule_code,
'XLA_SEG_RULES_B',
amb_context_code,
last_update_date ,
nvl2(lag_date, decode(last_update_date,lag_date, 'True','False'),'False') flag
from
(select segment_rule_code,
'XLA_SEG_RULES_B',
amb_context_code,
last_update_date ,
lag(last_update_date) over (PARTITION by application_id,
segment_rule_code,
segment_rule_type_code
order by amb_context_code
) lag_date
from xla_seg_rules_b
order by amb_context_code
) x
where x.amb_context_code = g_staging_context_code
)
where flag = 'False';
trace(p_msg => 'Number of Rows inserted into aads_gt from xla_seg_rules_b is :'||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => 'Number of Rows inserted into aads_gt from xla_seg_rules_b is :'||l_num_rows,
p_component_code => 'merge_adrs');
DELETE FROM xla_conditions c
WHERE amb_context_code = g_amb_context_code
AND application_id = g_application_id
AND EXISTS (SELECT 1
FROM xla_seg_rule_details w
WHERE c.segment_rule_detail_id = w.segment_rule_detail_id
AND w.application_id = g_application_id
AND w.amb_context_code = g_amb_context_code
AND w.segment_rule_type_code = l_adr_type_codes(i)
AND w.segment_rule_code = l_adr_codes(i));
trace(p_msg => '# xla_conditions delete : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_conditions delete : '||l_num_rows,
p_component_code => 'merge_adrs');
DELETE FROM xla_seg_rule_details w
WHERE amb_context_code = g_amb_context_code
AND application_id = g_application_id
AND segment_rule_type_code = l_adr_type_codes(i)
AND segment_rule_code = l_adr_codes(i);
trace(p_msg => '# xla_seg_rule_details delete : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_seg_rule_details delete : '||l_num_rows,
p_component_code => 'merge_adrs');
DELETE FROM xla_seg_rules_tl w
WHERE amb_context_code = g_amb_context_code
AND application_id = g_application_id
AND segment_rule_type_code = l_adr_type_codes(i)
AND segment_rule_code = l_adr_codes(i);
trace(p_msg => '# xla_seg_rules_tl delete : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_seg_rules_tl delete : '||l_num_rows,
p_component_code => 'merge_adrs');
DELETE FROM xla_seg_rules_b w
WHERE amb_context_code = g_amb_context_code
AND application_id = g_application_id
AND segment_rule_type_code = l_adr_type_codes(i)
AND segment_rule_code = l_adr_codes(i);
trace(p_msg => '# xla_seg_rules_b delete : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_seg_rules_b delete : '||l_num_rows,
p_component_code => 'merge_adrs');
UPDATE xla_seg_rules_b
SET amb_context_code = g_amb_context_code
WHERE amb_context_code = g_staging_context_code
AND application_id = g_application_id;
trace(p_msg => '# xla_seg_rules_b updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_seg_rules_b updated : '||l_num_rows,
p_component_code => 'merge_adrs');
UPDATE xla_seg_rules_tl w
SET amb_context_code = g_amb_context_code
WHERE amb_context_code = g_staging_context_code
AND application_id = g_application_id
AND NOT EXISTS (SELECT 1
FROM xla_seg_rules_tl s
WHERE s.amb_context_code = g_amb_context_code
AND s.segment_rule_type_code = w.segment_rule_type_code
AND s.name = w.name
AND s.language = w.language);
trace(p_msg => '# xla_seg_rules_tl 1 updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_seg_rules_tl 1 updated : '||l_num_rows,
p_component_code => 'merge_adrs');
UPDATE xla_seg_rules_tl w
SET amb_context_code = g_amb_context_code
, name = substr('('||w.segment_rule_code||') '||name,1,80)
WHERE amb_context_code = g_staging_context_code
AND application_id = g_application_id
AND EXISTS (SELECT 1
FROM xla_seg_rules_tl s
WHERE s.amb_context_code = g_amb_context_code
AND s.segment_rule_type_code = w.segment_rule_type_code
AND s.name = w.name
AND s.language = w.language);
trace(p_msg => '# xla_seg_rules_tl 1 updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_seg_rules_tl 1 updated : '||l_num_rows,
p_component_code => 'merge_adrs');
UPDATE xla_seg_rule_details
SET amb_context_code = g_amb_context_code
WHERE amb_context_code = g_staging_context_code
AND application_id = g_application_id;
trace(p_msg => '# xla_seg_rule_details updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_seg_rule_details updated : '||l_num_rows,
p_component_code => 'merge_adrs');
UPDATE xla_conditions
SET amb_context_code = g_amb_context_code
WHERE amb_context_code = g_staging_context_code
AND application_id = g_application_id
AND segment_rule_detail_id IS NOT NULL;
trace(p_msg => '# xla_conditions updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_conditions updated : '||l_num_rows,
p_component_code => 'merge_adrs');
SELECT s.mapping_set_code
FROM xla_mapping_sets_b s
, xla_mapping_sets_b w
WHERE s.amb_context_code = g_staging_context_code
AND w.amb_context_code(+) = g_amb_context_code
AND s.mapping_set_code = w.mapping_set_code(+)
AND s.version_num >= w.version_num(+);
DELETE FROM xla_mapping_sets_b w
WHERE amb_context_code = g_amb_context_code
AND mapping_set_code = l_ms(i);
trace(p_msg => '# xla_mapping_sets_b delete : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_conditions updated : '||l_num_rows,
p_component_code => 'merge_mapping_sets');
DELETE FROM xla_mapping_sets_tl w
WHERE amb_context_code = g_amb_context_code
AND mapping_set_code = l_ms(i)
AND EXISTS (SELECT 1
FROM xla_mapping_sets_tl s
WHERE s.amb_context_code = g_staging_context_code
AND s.mapping_set_code = w.mapping_set_code
AND s.language = w.language);
trace(p_msg => '# xla_mapping_sets_tl delete : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_mapping_sets_tl delete : '||l_num_rows,
p_component_code => 'merge_mapping_sets');
DELETE FROM xla_mapping_set_values w
WHERE amb_context_code = g_amb_context_code
AND mapping_set_code = l_ms(i);
trace(p_msg => '# xla_mapping_set_values delete : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_mapping_set_values delete : '||l_num_rows,
p_component_code => 'merge_mapping_sets');
UPDATE xla_mapping_sets_b
SET amb_context_code = g_amb_context_code
WHERE amb_context_code = g_staging_context_code
AND mapping_set_code = l_ms(i);
trace(p_msg => '# xla_mapping_sets_b updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_mapping_sets_b updated : '||l_num_rows,
p_component_code => 'merge_mapping_sets');
UPDATE xla_mapping_sets_tl s
SET amb_context_code = g_amb_context_code
WHERE amb_context_code = g_staging_context_code
AND mapping_set_code = l_ms(i)
AND NOT EXISTS (SELECT 1
FROM xla_mapping_sets_tl w
WHERE w.amb_context_code = g_amb_context_code
AND w.name = s.name
AND w.language = s.language);
trace(p_msg => '# xla_mapping_sets_tl 1 updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_mapping_sets_tl 1 updated : '||l_num_rows,
p_component_code => 'merge_mapping_sets');
UPDATE xla_mapping_sets_tl s
SET amb_context_code = g_amb_context_code
, name = substr('('||s.mapping_set_code||') '||name,1,80)
WHERE amb_context_code = g_staging_context_code
AND mapping_set_code = l_ms(i)
AND EXISTS (SELECT 1
FROM xla_mapping_sets_tl w
WHERE w.amb_context_code = g_amb_context_code
AND w.name = s.name
AND w.language = s.language);
trace(p_msg => '# xla_mapping_sets_tl 2 updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_mapping_sets_tl 2 updated : '||l_num_rows,
p_component_code => 'merge_mapping_sets');
UPDATE xla_mapping_set_values
SET amb_context_code = g_amb_context_code
WHERE amb_context_code = g_staging_context_code
AND mapping_set_code = l_ms(i);
trace(p_msg => '# xla_mapping_set_values updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_mapping_set_values updated : '||l_num_rows,
p_component_code => 'merge_mapping_sets');
SELECT s.accounting_method_type_code, s.accounting_method_code, s.product_rule_type_code, s.product_rule_code
FROM xla_acctg_method_rules w
, xla_acctg_method_rules s
WHERE s.application_id = g_application_id
AND s.amb_context_code = g_staging_context_code
AND s.accounting_method_type_code = w.accounting_method_type_code
AND s.accounting_method_code = w.accounting_method_code
AND s.product_rule_code = w.product_rule_code
AND s.product_rule_type_code = w.product_rule_type_code
AND w.application_id = g_application_id
AND w.amb_context_code = g_amb_context_code;
a) Deleteing the the SLAM with Default context, if there exists the same SLAM and Product Rule Code in staging area
b) And the Moving the SLAM from Stanging Context Code to Default(Working Area).
*/
OPEN c_slam;
DELETE FROM xla_acctg_method_rules w
WHERE amb_context_code = g_amb_context_code
AND application_id = g_application_id
AND accounting_method_type_code = l_acct_method_type_code(i)
AND accounting_method_code = l_acct_method_code(i)
AND product_rule_type_code = l_product_rule_type_code(i)
AND product_rule_code = l_product_rule_code(i);
trace(p_msg => '# xla_acctg_methods_tl delete : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_acctg_method_rules delete : '||l_num_rows,
p_component_code => 'merge_acctg_methods');
UPDATE xla_acctg_method_rules
SET amb_context_code = g_amb_context_code
WHERE amb_context_code = g_staging_context_code
AND application_id = g_application_id;
trace(p_msg => '# xla_acctg_methods_b updated : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_acctg_method_rules update : '||l_num_rows,
p_component_code => 'merge_acctg_methods');
INSERT INTO xla_acctg_methods_b
(accounting_method_type_code
,accounting_method_code
,transaction_coa_id
,accounting_coa_id
,enabled_flag
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT s.accounting_method_type_code
,s.accounting_method_code
,s.transaction_coa_id
,s.accounting_coa_id
,s.enabled_flag
,sysdate
,xla_environment_pkg.g_usr_id
,sysdate
,xla_environment_pkg.g_usr_id
,xla_environment_pkg.g_login_id
FROM xla_stage_acctg_methods s
LEFT OUTER JOIN xla_acctg_methods_b w
ON w.accounting_method_type_code = s.accounting_method_type_code
AND w.accounting_method_code = s.accounting_method_code
WHERE s.staging_amb_context_code = g_staging_context_code
AND w.accounting_method_type_code IS NULL;
trace(p_msg => '# row inserted in xla_acctg_methods_b = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# row inserted in xla_acctg_methods_b = '||l_num_rows,
p_component_code => 'merge_acctg_methods');
INSERT INTO xla_acctg_methods_tl
(accounting_method_type_code
,accounting_method_code
,language
,name
,description
,source_lang
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT s.accounting_method_type_code
,s.accounting_method_code
,fl.language_code
,s.name
,s.description
,USERENV('LANG')
,sysdate
,xla_environment_pkg.g_usr_id
,sysdate
,xla_environment_pkg.g_usr_id
,xla_environment_pkg.g_login_id
FROM xla_stage_acctg_methods s
JOIN fnd_languages fl
ON fl.installed_flag IN ('I', 'B')
LEFT OUTER JOIN xla_acctg_methods_tl w
ON w.accounting_method_type_code = s.accounting_method_type_code
AND w.accounting_method_code = s.accounting_method_code
AND w.language = fl.language_code
WHERE s.staging_amb_context_code = g_staging_context_code
AND w.accounting_method_type_code IS NULL;
trace(p_msg => '# row inserted in xla_acctg_methods_tl = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# row inserted in xla_acctg_methods_tl = '||l_num_rows,
p_component_code => 'merge_acctg_methods');
UPDATE xla_acctg_method_rules xamr
SET amb_context_code = g_amb_context_code
WHERE amb_context_code = g_staging_context_code
AND NOT EXISTS (SELECT 1
FROM xla_acctg_method_rules xamr2
WHERE xamr2.amb_context_code = g_amb_context_code
AND xamr2.accounting_method_type_code = xamr.accounting_method_type_code
AND xamr2.accounting_method_code = xamr.accounting_method_code
AND xamr2.application_id = g_application_id);
INSERT INTO xla_aad_loader_logs
(aad_loader_log_id
,amb_context_code
,application_id
,request_code
,log_type_code
,encoded_message
,aad_application_id
,product_rule_code
,product_rule_type_code
,component_owner_code
,component_code
,object_version_number
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,program_update_date
,program_application_id
,program_id
,request_id)
SELECT xla_aad_loader_logs_s.nextval
,g_amb_context_code
,g_application_id
,'IMPORT'
,'UNMERGE_AAD_IN_SLAM'
,'Rows inserted in xla_acctg_methods_b for staging context code : '||g_staging_context_code||
', ' ||'application_id : '||g_application_id||' is : '||l_num_rows
,g_application_id
,product_rule_code
,product_rule_type_code
,accounting_method_type_code
,accounting_method_code
,1
,sysdate
,xla_environment_pkg.g_usr_id
,sysdate
,xla_environment_pkg.g_usr_id
,xla_environment_pkg.g_login_id
,sysdate
,xla_environment_pkg.g_prog_appl_id
,xla_environment_pkg.g_prog_id
,xla_environment_pkg.g_req_Id
FROM (SELECT distinct product_rule_type_code
, product_rule_code
, accounting_method_type_code
, accounting_method_code
FROM xla_acctg_method_rules
WHERE amb_context_code = g_staging_context_code);
trace(p_msg => '# row inserted in xla_acctg_method_rules = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# row inserted in xla_acctg_method_rules = '||l_num_rows,
p_component_code => 'merge_acctg_methods');
DELETE FROM xla_mapping_set_values
WHERE amb_context_code = g_staging_context_code;
trace(p_msg => '# xla_mapping_set_values delete : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_mapping_set_values delete : '||l_num_rows,
p_component_code => 'purge_mapping_sets');
DELETE FROM xla_mapping_sets_tl
WHERE amb_context_code = g_staging_context_code;
trace(p_msg => '# xla_mapping_sets_tl delete : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_mapping_sets_tl delete : '||l_num_rows,
p_component_code => 'purge_mapping_sets');
DELETE FROM xla_mapping_sets_b
WHERE amb_context_code = g_staging_context_code;
trace(p_msg => '# xla_mapping_sets_b delete : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_mapping_sets_b delete : '||l_num_rows,
p_component_code => 'purge_mapping_sets');
DELETE FROM xla_analytical_sources
WHERE amb_context_code = g_staging_context_code;
trace(p_msg => '# xla_analytical_sources delete : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_analytical_sources delete : '||l_num_rows,
p_component_code => 'purge_analytical_criteria');
DELETE FROM xla_analytical_dtls_tl
WHERE amb_context_code = g_staging_context_code;
trace(p_msg => '# xla_analytical_dtls_tl delete : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_analytical_dtls_tl delete : '||l_num_rows,
p_component_code => 'purge_analytical_criteria');
DELETE FROM xla_analytical_dtls_b
WHERE amb_context_code = g_staging_context_code;
trace(p_msg => '# xla_analytical_dtls_b delete : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_analytical_dtls_b delete : '||l_num_rows,
p_component_code => 'purge_analytical_criteria');
DELETE FROM xla_analytical_hdrs_tl
WHERE amb_context_code = g_staging_context_code;
trace(p_msg => '# xla_analytical_hdrs_tl delete : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_analytical_hdrs_tl delete : '||l_num_rows,
p_component_code => 'purge_analytical_criteria');
DELETE FROM xla_analytical_hdrs_b
WHERE amb_context_code = g_staging_context_code;
trace(p_msg => '# xla_analytical_hdrs_b delete : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_analytical_hdrs_b delete : '||l_num_rows,
p_component_code => 'purge_analytical_criteria');
DELETE FROM xla_conditions
WHERE amb_context_code = g_staging_context_code
AND segment_rule_detail_id IS NOT NULl;
trace(p_msg => '# xla_conditions delete : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_conditions delete : '||l_num_rows,
p_component_code => 'purge_adrs');
DELETE FROM xla_seg_rule_details
WHERE amb_context_code = g_staging_context_code;
trace(p_msg => '# xla_seg_rules_details delete : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_seg_rules_details delete : '||l_num_rows,
p_component_code => 'purge_adrs');
DELETE FROM xla_seg_rules_tl
WHERE amb_context_code = g_staging_context_code;
trace(p_msg => '# xla_seg_rules_tl delete : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_seg_rules_tl delete : '||l_num_rows,
p_component_code => 'purge_adrs');
DELETE FROM xla_seg_rules_b
WHERE amb_context_code = g_staging_context_code;
trace(p_msg => '# xla_seg_rules_b delete : '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
xla_aad_loader_util_pvt.insert_aadloaderlog(
p_amb_context_code => g_amb_context_code,
p_application_id => g_application_id,
p_request_code => 'IMPORT' ,
p_log_type_code => 'MERGE',
p_encoded_message => '# xla_seg_rules_b delete : '||l_num_rows,
p_component_code => 'purge_adrs');