[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 = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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);
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);
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 = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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);
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 = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
/*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);
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;
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 = w.application_id
AND xal.amb_context_code = w.amb_context_code
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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(+);
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 : '||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 = 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 : '||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 = 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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||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 = l_ac_dtl_type_codes(i)
AND analytical_criterion_code = l_ac_dtl_codes(i)
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.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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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_dtl_type_codes(i)
AND analytical_criterion_code = l_ac_dtl_codes(i)
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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;
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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
,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'
,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 = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
DELETE FROM xla_mapping_set_values
WHERE amb_context_code = g_staging_context_code;
trace(p_msg => '# xla_mapping_set_values delete : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
DELETE FROM xla_mapping_sets_tl
WHERE amb_context_code = g_staging_context_code;
trace(p_msg => '# xla_mapping_sets_tl delete : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
DELETE FROM xla_mapping_sets_b
WHERE amb_context_code = g_staging_context_code;
trace(p_msg => '# xla_mapping_sets_b delete : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
DELETE FROM xla_analytical_sources
WHERE amb_context_code = g_staging_context_code;
trace(p_msg => '# xla_analytical_sources delete : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
DELETE FROM xla_analytical_dtls_tl
WHERE amb_context_code = g_staging_context_code;
trace(p_msg => '# xla_analytical_dtls_tl delete : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
DELETE FROM xla_analytical_dtls_b
WHERE amb_context_code = g_staging_context_code;
trace(p_msg => '# xla_analytical_dtls_b delete : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
DELETE FROM xla_analytical_hdrs_tl
WHERE amb_context_code = g_staging_context_code;
trace(p_msg => '# xla_analytical_hdrs_tl delete : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
DELETE FROM xla_analytical_hdrs_b
WHERE amb_context_code = g_staging_context_code;
trace(p_msg => '# xla_analytical_hdrs_b delete : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
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 : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
DELETE FROM xla_seg_rule_details
WHERE amb_context_code = g_staging_context_code;
trace(p_msg => '# xla_seg_rules_details delete : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
DELETE FROM xla_seg_rules_tl
WHERE amb_context_code = g_staging_context_code;
trace(p_msg => '# xla_seg_rules_tl delete : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
DELETE FROM xla_seg_rules_b
WHERE amb_context_code = g_staging_context_code;
trace(p_msg => '# xla_seg_rules_b delete : '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);