[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
FROM xla_appli_amb_contexts
WHERE application_id = p_application_id
AND amb_context_code = p_amb_context_code
FOR UPDATE OF application_id NOWAIT;
SELECT distinct t.name
FROM xla_aads_h h
,xla_product_rules_b b
,xla_product_rules_tl t
WHERE t.application_id = b.application_id
AND t.amb_context_code = b.amb_context_code
AND t.product_rule_type_code = b.product_rule_type_code
AND t.product_rule_code = b.product_rule_code
AND t.language = USERENV('LANG')
AND ((h.version_num > b.version_num AND
h.leapfrog_flag = 'N') OR
(h.version_num = b.version_num AND
h.leapfrog_flag = 'Y'))
AND h.application_id = b.application_id
AND h.product_rule_type_code = b.product_rule_type_code
AND h.product_rule_code = b.product_rule_code
AND b.application_id = p_application_id
AND b.amb_context_code = p_amb_context_code;
SELECT distinct t.name
FROM xla_amb_components_h h
,xla_seg_rules_b b
,xla_seg_rules_tl t
WHERE t.amb_context_code = b.amb_context_code
AND t.application_id = b.application_id
AND t.segment_rule_type_code = b.segment_rule_type_code
AND t.segment_rule_code = b.segment_rule_code
AND t.language = USERENV('LANG')
AND ((h.version_num > b.version_num AND
h.leapfrog_flag = 'N') OR
(h.version_num = b.version_num AND
h.leapfrog_flag = 'Y'))
AND h.component_type_code = 'AMB_ADR'
AND h.application_id = b.application_id
AND h.component_owner_code = b.segment_rule_type_code
AND h.component_code = b.segment_rule_code
AND b.application_id = p_application_id
AND b.amb_context_code = p_amb_context_code;
SELECT distinct t.name
FROM xla_amb_components_h h
,xla_analytical_hdrs_b b
,xla_analytical_hdrs_tl t
WHERE t.amb_context_code = b.amb_context_code
AND t.analytical_criterion_type_code = b.analytical_criterion_type_code
AND t.analytical_criterion_code = b.analytical_criterion_code
AND t.language = USERENV('LANG')
AND ((h.version_num > b.version_num AND
h.leapfrog_flag = 'N') OR
(h.version_num = b.version_num AND
h.leapfrog_flag = 'Y'))
AND h.component_type_code = 'ANALYTICAL_CRITERION'
AND h.component_owner_code = b.analytical_criterion_type_code
AND h.component_code = b.analytical_criterion_code
AND b.amb_context_code = p_amb_context_code
AND (EXISTS (SELECT 1
FROM xla_aad_header_ac_assgns ac
WHERE b.amb_context_code = ac.amb_context_code
AND b.analytical_criterion_type_code = ac.analytical_criterion_type_code
AND b.analytical_criterion_code = ac.analytical_criterion_code
AND ac.amb_context_code = p_amb_context_code
AND ac.application_id = p_application_id) OR
EXISTS (SELECT 1
FROM xla_line_defn_ac_assgns ac
, xla_aad_line_defn_assgns xal
WHERE b.amb_context_code = ac.amb_context_code
AND b.analytical_criterion_type_code = ac.analytical_criterion_type_code
AND b.analytical_criterion_code = ac.analytical_criterion_code
AND ac.application_id = xal.application_id
AND ac.amb_context_code = xal.amb_context_code
AND ac.event_class_code = xal.event_class_code
AND ac.event_type_code = xal.event_type_code
AND ac.line_definition_owner_code = xal.line_definition_owner_code
AND ac.line_definition_code = xal.line_definition_code
AND xal.amb_context_code = p_amb_context_code
AND xal.application_id = p_application_id));
SELECT distinct t.name
FROM xla_amb_components_h h
,xla_mapping_sets_b b
,xla_mapping_sets_tl t
WHERE t.amb_context_code = b.amb_context_code
AND t.mapping_set_code = b.mapping_set_code
AND t.language = USERENV('LANG')
AND ((h.version_num > b.version_num AND
h.leapfrog_flag = 'N') OR
(h.version_num = b.version_num AND
h.leapfrog_flag = 'Y'))
AND h.component_type_code = 'MAPPING_SET'
AND h.component_code = b.mapping_set_code
AND b.amb_context_code = p_amb_context_code
AND EXISTS (SELECT 1
FROM xla_seg_rule_details dtl
, xla_line_defn_adr_assgns adr
, xla_aad_line_defn_assgns xal
WHERE b.mapping_set_code = dtl.value_mapping_set_code
AND dtl.amb_context_code = adr.amb_context_code
AND dtl.application_id = adr.segment_rule_appl_id
AND dtl.segment_rule_type_code = adr.segment_rule_type_code
AND dtl.segment_rule_code = adr.segment_rule_code
AND adr.application_id = xal.application_id
AND adr.amb_context_code = xal.amb_context_code
AND adr.event_class_code = xal.event_class_code
AND adr.event_type_code = xal.event_type_code
AND adr.line_definition_owner_code = xal.line_definition_owner_code
AND adr.line_definition_code = xal.line_definition_code
AND xal.amb_context_code = p_amb_context_code
AND xal.application_id = p_application_id);
SELECT 1
FROM xla_aads_h h
,xla_product_rules_b b
WHERE h.application_id = b.application_id
AND h.product_rule_type_code = b.product_rule_type_code
AND h.product_rule_code = b.product_rule_code
AND ((h.version_num > b.version_num AND
h.leapfrog_flag = 'N') OR
(h.version_num = b.version_num AND
h.leapfrog_flag = 'Y'))
AND b.application_id = p_application_id
AND b.amb_context_code = p_amb_context_code;
SELECT 1
FROM xla_amb_components_h h
,xla_seg_rules_b b
WHERE h.component_type_code = 'AMB_ADR'
AND h.application_id = b.application_id
AND h.component_owner_code = b.segment_rule_type_code
AND h.component_code = b.segment_rule_code
AND ((h.version_num > b.version_num AND
h.leapfrog_flag = 'N') OR
(h.version_num = b.version_num AND
h.leapfrog_flag = 'Y'))
AND b.application_id = p_application_id
AND b.amb_context_code = p_amb_context_code;
SELECT 1
FROM xla_amb_components_h h
,xla_mapping_sets_b b
WHERE h.component_type_code = 'MAPPING_SET'
AND h.component_code = b.mapping_set_code
AND ((h.version_num > b.version_num AND
h.leapfrog_flag = 'N') OR
(h.version_num = b.version_num AND
h.leapfrog_flag = 'Y'))
AND b.amb_context_code = p_amb_context_code
AND EXISTS (SELECT 1
FROM xla_seg_rule_details dtl
, xla_line_defn_adr_assgns adr
, xla_aad_line_defn_assgns xal
WHERE b.mapping_set_code = dtl.value_mapping_set_code
AND dtl.amb_context_code = adr.amb_context_code
AND dtl.application_id = adr.segment_rule_appl_id
AND dtl.segment_rule_type_code = adr.segment_rule_type_code
AND dtl.segment_rule_code = adr.segment_rule_code
AND adr.application_id = xal.application_id
AND adr.amb_context_code = xal.amb_context_code
AND adr.event_class_code = xal.event_class_code
AND adr.event_type_code = xal.event_type_code
AND adr.line_definition_owner_code = xal.line_definition_owner_code
AND adr.line_definition_code = xal.line_definition_code
AND xal.amb_context_code = p_amb_context_code
AND xal.application_id = p_application_id);
FUNCTION update_group_number
(p_product_rule_code VARCHAR2
,p_group_number INTEGER)
RETURN BOOLEAN
IS
l_retcode BOOLEAN;
l_log_module := C_DEFAULT_MODULE||'.update_group_number';
trace(p_msg => 'BEGIN of function update_group_number: '||
p_product_rule_code||','||p_group_number,
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
trace(p_msg => 'END of function update_group_number : '||
'l_retcode = '||CASE WHEN l_retcode THEN 'TRUE'
ELSE 'FALSE' END,
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
,p_value_1 => 'xla_aad_export_pvt.update_group_number'
,p_token_2 => 'ERROR'
,p_value_2 => 'unhandled exception');
END update_group_number;
SELECT distinct
b.product_rule_code
,b.version_num
,b.updated_flag
,NVL(h.leapfrog_flag,'N') leapfrog_flag
FROM xla_product_rules_b b
JOIN xla_aads_h h
ON h.product_rule_code = b.product_rule_code
AND h.application_id = p_application_id
AND h.product_rule_type_code = p_owner_type
JOIN (SELECT product_rule_code, max(version_num) max_version_num
FROM xla_aads_h
WHERE application_id = p_application_id
AND product_rule_type_code = p_owner_type
GROUP BY product_rule_code) h2
ON h.product_rule_code = h2.product_rule_code
AND h.version_num = h2.max_version_num
WHERE b.application_id = p_application_id
AND b.amb_context_code = p_amb_context_code
AND b.product_rule_type_code = p_owner_type
UNION
SELECT distinct
b.product_rule_code
,b.version_num
,b.updated_flag
,NVL(h.leapfrog_flag,'N') leapfrog_flag
FROM xla_product_rules_b b
LEFT OUTER JOIN xla_aads_h h
ON h.product_rule_code = b.product_rule_code
AND h.application_id = p_application_id
AND h.product_rule_type_code = p_owner_type
WHERE b.application_id = p_application_id
AND b.amb_context_code = p_amb_context_code
AND b.product_rule_type_code = p_owner_type
AND h.product_rule_code IS NULL;
SELECT xal.product_rule_code
FROM xla_aad_line_defn_assgns xal
WHERE xal.application_id = p_application_id
AND xal.amb_context_code = p_amb_context_code
AND xal.product_rule_type_code = p_owner_type
AND EXISTS (SELECT 1
FROM xla_aad_line_defn_assgns xal2
,TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
WHERE xal2.application_id = xal.application_id
AND xal2.amb_context_code = xal.amb_context_code
AND xal2.event_class_code = xal.event_class_code
AND xal2.event_type_code = xal.event_type_code
AND xal2.line_definition_owner_code = xal.line_definition_owner_code
AND xal2.line_definition_code = xal.line_definition_code
AND xal2.product_rule_type_code = p_owner_type
AND xal2.product_rule_code = grp.product_rule_code
AND grp.group_num = l_curr_group_num)
UNION
SELECT h.product_rule_code -- header description
FROM xla_prod_acct_headers h
WHERE h.application_id = p_application_id
AND h.amb_context_code = p_amb_context_code
AND h.product_rule_type_code = p_owner_type
AND EXISTS (SELECT 1
FROM xla_prod_acct_headers pah
,TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
WHERE pah.application_id = p_application_id
AND pah.amb_context_code = p_amb_context_code
AND pah.description_type_code = h.description_type_code
AND pah.description_code = h.description_code
AND pah.product_rule_type_code = p_owner_type
AND pah.product_rule_code = grp.product_rule_code
AND grp.group_num = l_curr_group_num
UNION
SELECT 1
FROM xla_aad_line_defn_assgns xal
,xla_line_defn_jlt_assgns xjl
,TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
WHERE xjl.application_id = p_application_id
AND xjl.amb_context_code = p_amb_context_code
AND xjl.description_type_code = h.description_type_code
AND xjl.description_code = h.description_code
AND xal.application_id = p_application_id
AND xal.amb_context_code = p_amb_context_code
AND xal.product_rule_type_code = p_owner_type
AND xal.product_rule_code = grp.product_rule_code
AND xal.event_class_code = xjl.event_class_code
AND xal.event_type_code = xjl.event_type_code
AND xal.line_definition_owner_code = xjl.line_definition_owner_code
AND xal.line_definition_code = xjl.line_definition_code
AND grp.group_num = l_curr_group_num
UNION
SELECT 1
FROM xla_aad_line_defn_assgns xal
,xla_line_defn_jlt_assgns xjl
,TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
WHERE xjl.application_id = p_application_id
AND xjl.amb_context_code = p_amb_context_code
AND xjl.mpa_header_desc_type_code = h.description_type_code
AND xjl.mpa_header_desc_code = h.description_code
AND xal.application_id = p_application_id
AND xal.amb_context_code = p_amb_context_code
AND xal.product_rule_type_code = p_owner_type
AND xal.product_rule_code = grp.product_rule_code
AND xal.event_class_code = xjl.event_class_code
AND xal.event_type_code = xjl.event_type_code
AND xal.line_definition_owner_code = xjl.line_definition_owner_code
AND xal.line_definition_code = xjl.line_definition_code
AND grp.group_num = l_curr_group_num
UNION
SELECT 1
FROM xla_aad_line_defn_assgns xal
,xla_mpa_jlt_assgns xjl
,TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
WHERE xjl.application_id = p_application_id
AND xjl.amb_context_code = p_amb_context_code
AND xjl.description_type_code = h.description_type_code
AND xjl.description_code = h.description_code
AND xal.application_id = p_application_id
AND xal.amb_context_code = p_amb_context_code
AND xal.product_rule_type_code = p_owner_type
AND xal.product_rule_code = grp.product_rule_code
AND xal.event_class_code = xjl.event_class_code
AND xal.event_type_code = xjl.event_type_code
AND xal.line_definition_owner_code = xjl.line_definition_owner_code
AND xal.line_definition_code = xjl.line_definition_code
AND grp.group_num = l_curr_group_num)
UNION
SELECT xal.product_rule_code
FROM xla_line_defn_jlt_assgns h -- line description
,xla_aad_line_defn_assgns xal
WHERE h.application_id = xal.application_id
AND h.amb_context_code = xal.amb_context_code
AND h.event_class_code = xal.event_class_code
AND h.event_type_code = xal.event_type_code
AND h.line_definition_owner_code = xal.line_definition_owner_code
AND h.line_definition_code = xal.line_definition_code
AND xal.application_id = p_application_id
AND xal.amb_context_code = p_amb_context_code
AND xal.product_rule_type_code = p_owner_type
AND EXISTS (SELECT 1
FROM xla_prod_acct_headers pah
,TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
WHERE pah.application_id = p_application_id
AND pah.amb_context_code = p_amb_context_code
AND pah.description_type_code = h.description_type_code
AND pah.description_code = h.description_code
AND pah.product_rule_type_code = p_owner_type
AND pah.product_rule_code = grp.product_rule_code
AND grp.group_num = l_curr_group_num
UNION
SELECT 1
FROM xla_aad_line_defn_assgns xad
,xla_line_defn_jlt_assgns xjl
,TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
WHERE xjl.application_id = p_application_id
AND xjl.amb_context_code = p_amb_context_code
AND xjl.description_type_code = h.description_type_code
AND xjl.description_code = h.description_code
AND xad.application_id = p_application_id
AND xad.amb_context_code = p_amb_context_code
AND xad.product_rule_type_code = p_owner_type
AND xad.product_rule_code = grp.product_rule_code
AND xad.event_class_code = xjl.event_class_code
AND xad.event_type_code = xjl.event_type_code
AND xad.line_definition_owner_code = xjl.line_definition_owner_code
AND xad.line_definition_code = xjl.line_definition_code
AND grp.group_num = l_curr_group_num
UNION
SELECT 1
FROM xla_aad_line_defn_assgns xal
,xla_line_defn_jlt_assgns xjl
,TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
WHERE xjl.application_id = p_application_id
AND xjl.amb_context_code = p_amb_context_code
AND xjl.mpa_header_desc_type_code = h.description_type_code
AND xjl.mpa_header_desc_code = h.description_code
AND xal.application_id = p_application_id
AND xal.amb_context_code = p_amb_context_code
AND xal.product_rule_type_code = p_owner_type
AND xal.product_rule_code = grp.product_rule_code
AND xal.event_class_code = xjl.event_class_code
AND xal.event_type_code = xjl.event_type_code
AND xal.line_definition_owner_code = xjl.line_definition_owner_code
AND xal.line_definition_code = xjl.line_definition_code
AND grp.group_num = l_curr_group_num
UNION
SELECT 1
FROM xla_aad_line_defn_assgns xal
,xla_mpa_jlt_assgns xjl
,TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
WHERE xjl.application_id = p_application_id
AND xjl.amb_context_code = p_amb_context_code
AND xjl.description_type_code = h.description_type_code
AND xjl.description_code = h.description_code
AND xal.application_id = p_application_id
AND xal.amb_context_code = p_amb_context_code
AND xal.product_rule_type_code = p_owner_type
AND xal.product_rule_code = grp.product_rule_code
AND xal.event_class_code = xjl.event_class_code
AND xal.event_type_code = xjl.event_type_code
AND xal.line_definition_owner_code = xjl.line_definition_owner_code
AND xal.line_definition_code = xjl.line_definition_code
AND grp.group_num = l_curr_group_num)
UNION
SELECT xal.product_rule_code
FROM xla_line_defn_jlt_assgns h -- MPA header description
,xla_aad_line_defn_assgns xal
WHERE h.application_id = xal.application_id
AND h.amb_context_code = xal.amb_context_code
AND h.event_class_code = xal.event_class_code
AND h.event_type_code = xal.event_type_code
AND h.line_definition_owner_code = xal.line_definition_owner_code
AND h.line_definition_code = xal.line_definition_code
AND xal.application_id = p_application_id
AND xal.amb_context_code = p_amb_context_code
AND xal.product_rule_type_code = p_owner_type
AND EXISTS (SELECT 1
FROM xla_prod_acct_headers pah
,TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
WHERE pah.application_id = p_application_id
AND pah.amb_context_code = p_amb_context_code
AND pah.description_type_code = h.mpa_header_desc_type_code
AND pah.description_code = h.mpa_header_desc_code
AND pah.product_rule_type_code = p_owner_type
AND pah.product_rule_code = grp.product_rule_code
AND grp.group_num = l_curr_group_num
UNION
SELECT 1
FROM xla_aad_line_defn_assgns xad
,xla_line_defn_jlt_assgns xjl
,TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
WHERE xjl.application_id = p_application_id
AND xjl.amb_context_code = p_amb_context_code
AND xjl.description_type_code = h.mpa_header_desc_type_code
AND xjl.description_code = h.mpa_header_desc_code
AND xad.application_id = p_application_id
AND xad.amb_context_code = p_amb_context_code
AND xad.product_rule_type_code = p_owner_type
AND xad.product_rule_code = grp.product_rule_code
AND xad.event_class_code = xjl.event_class_code
AND xad.event_type_code = xjl.event_type_code
AND xad.line_definition_owner_code = xjl.line_definition_owner_code
AND xad.line_definition_code = xjl.line_definition_code
AND grp.group_num = l_curr_group_num
UNION
SELECT 1
FROM xla_aad_line_defn_assgns xal
,xla_line_defn_jlt_assgns xjl
,TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
WHERE xjl.application_id = p_application_id
AND xjl.amb_context_code = p_amb_context_code
AND xjl.mpa_header_desc_type_code = h.mpa_header_desc_type_code
AND xjl.mpa_header_desc_code = h.mpa_header_desc_code
AND xal.application_id = p_application_id
AND xal.amb_context_code = p_amb_context_code
AND xal.product_rule_type_code = p_owner_type
AND xal.product_rule_code = grp.product_rule_code
AND xal.event_class_code = xjl.event_class_code
AND xal.event_type_code = xjl.event_type_code
AND xal.line_definition_owner_code = xjl.line_definition_owner_code
AND xal.line_definition_code = xjl.line_definition_code
AND grp.group_num = l_curr_group_num
UNION
SELECT 1
FROM xla_aad_line_defn_assgns xal
,xla_mpa_jlt_assgns xjl
,TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
WHERE xjl.application_id = p_application_id
AND xjl.amb_context_code = p_amb_context_code
AND xjl.description_type_code = h.mpa_header_desc_type_code
AND xjl.description_code = h.mpa_header_desc_code
AND xal.application_id = p_application_id
AND xal.amb_context_code = p_amb_context_code
AND xal.product_rule_type_code = p_owner_type
AND xal.product_rule_code = grp.product_rule_code
AND xal.event_class_code = xjl.event_class_code
AND xal.event_type_code = xjl.event_type_code
AND xal.line_definition_owner_code = xjl.line_definition_owner_code
AND xal.line_definition_code = xjl.line_definition_code
AND grp.group_num = l_curr_group_num)
UNION
SELECT xal.product_rule_code
FROM xla_mpa_jlt_assgns h -- MPA line description
,xla_aad_line_defn_assgns xal
WHERE h.application_id = xal.application_id
AND h.amb_context_code = xal.amb_context_code
AND h.event_class_code = xal.event_class_code
AND h.event_type_code = xal.event_type_code
AND h.line_definition_owner_code = xal.line_definition_owner_code
AND h.line_definition_code = xal.line_definition_code
AND xal.application_id = p_application_id
AND xal.amb_context_code = p_amb_context_code
AND xal.product_rule_type_code = p_owner_type
AND EXISTS (SELECT 1
FROM xla_prod_acct_headers pah
,TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
WHERE pah.application_id = p_application_id
AND pah.amb_context_code = p_amb_context_code
AND pah.description_type_code = h.description_type_code
AND pah.description_code = h.description_code
AND pah.product_rule_type_code = p_owner_type
AND pah.product_rule_code = grp.product_rule_code
AND grp.group_num = l_curr_group_num
UNION
SELECT 1
FROM xla_aad_line_defn_assgns xad
,xla_line_defn_jlt_assgns xjl
,TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
WHERE xjl.application_id = p_application_id
AND xjl.amb_context_code = p_amb_context_code
AND xjl.description_type_code = h.description_type_code
AND xjl.description_code = h.description_code
AND xad.application_id = p_application_id
AND xad.amb_context_code = p_amb_context_code
AND xad.product_rule_type_code = p_owner_type
AND xad.product_rule_code = grp.product_rule_code
AND xad.event_class_code = xjl.event_class_code
AND xad.event_type_code = xjl.event_type_code
AND xad.line_definition_owner_code = xjl.line_definition_owner_code
AND xad.line_definition_code = xjl.line_definition_code
AND grp.group_num = l_curr_group_num
UNION
SELECT 1
FROM xla_aad_line_defn_assgns xal
,xla_line_defn_jlt_assgns xjl
,TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
WHERE xjl.application_id = p_application_id
AND xjl.amb_context_code = p_amb_context_code
AND xjl.mpa_header_desc_type_code = h.description_type_code
AND xjl.mpa_header_desc_code = h.description_code
AND xal.application_id = p_application_id
AND xal.amb_context_code = p_amb_context_code
AND xal.product_rule_type_code = p_owner_type
AND xal.product_rule_code = grp.product_rule_code
AND xal.event_class_code = xjl.event_class_code
AND xal.event_type_code = xjl.event_type_code
AND xal.line_definition_owner_code = xjl.line_definition_owner_code
AND xal.line_definition_code = xjl.line_definition_code
AND grp.group_num = l_curr_group_num
UNION
SELECT 1
FROM xla_aad_line_defn_assgns xal
,xla_mpa_jlt_assgns xjl
,TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
WHERE xjl.application_id = p_application_id
AND xjl.amb_context_code = p_amb_context_code
AND xjl.description_type_code = h.description_type_code
AND xjl.description_code = h.description_code
AND xal.application_id = p_application_id
AND xal.amb_context_code = p_amb_context_code
AND xal.product_rule_type_code = p_owner_type
AND xal.product_rule_code = grp.product_rule_code
AND xal.event_class_code = xjl.event_class_code
AND xal.event_type_code = xjl.event_type_code
AND xal.line_definition_owner_code = xjl.line_definition_owner_code
AND xal.line_definition_code = xjl.line_definition_code
AND grp.group_num = l_curr_group_num)
UNION
SELECT xal.product_rule_code
FROM xla_line_defn_jlt_assgns h -- JLT
,xla_aad_line_defn_assgns xal
WHERE h.application_id = xal.application_id
AND h.amb_context_code = xal.amb_context_code
AND h.event_class_code = xal.event_class_code
AND h.event_type_code = xal.event_type_code
AND h.line_definition_owner_code = xal.line_definition_owner_code
AND h.line_definition_code = xal.line_definition_code
AND xal.application_id = p_application_id
AND xal.amb_context_code = p_amb_context_code
AND xal.product_rule_type_code = p_owner_type
AND EXISTS (SELECT 1
FROM xla_aad_line_defn_assgns xad
,xla_line_defn_jlt_assgns xjl
,TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
WHERE xjl.application_id = p_application_id
AND xjl.amb_context_code = p_amb_context_code
AND xjl.event_class_code = h.event_class_code
AND xjl.accounting_line_type_code = h.accounting_line_type_code
AND xjl.accounting_line_code = h.accounting_line_code
AND xad.event_class_code = xjl.event_class_code
AND xad.event_type_code = xjl.event_type_code
AND xad.line_definition_owner_code = xjl.line_definition_owner_code
AND xad.line_definition_code = xjl.line_definition_code
AND xad.application_id = p_application_id
AND xad.amb_context_code = p_amb_context_code
AND xad.product_rule_type_code = p_owner_type
AND xad.product_rule_code = grp.product_rule_code
AND grp.group_num = l_curr_group_num
UNION
SELECT 1
FROM xla_aad_line_defn_assgns xad
,xla_mpa_jlt_assgns xjl
,TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
WHERE xjl.application_id = p_application_id
AND xjl.amb_context_code = p_amb_context_code
AND xjl.event_class_code = h.event_class_code
AND xjl.mpa_accounting_line_type_code = h.accounting_line_type_code
AND xjl.mpa_accounting_line_code = h.accounting_line_code
AND xad.event_class_code = xjl.event_class_code
AND xad.event_type_code = xjl.event_type_code
AND xad.line_definition_owner_code = xjl.line_definition_owner_code
AND xad.line_definition_code = xjl.line_definition_code
AND xad.application_id = p_application_id
AND xad.amb_context_code = p_amb_context_code
AND xad.product_rule_type_code = p_owner_type
AND xad.product_rule_code = grp.product_rule_code
AND grp.group_num = l_curr_group_num)
UNION
SELECT xal.product_rule_code
FROM xla_mpa_jlt_assgns h -- MPA JLT
,xla_aad_line_defn_assgns xal
WHERE h.application_id = xal.application_id
AND h.amb_context_code = xal.amb_context_code
AND h.event_class_code = xal.event_class_code
AND h.event_type_code = xal.event_type_code
AND h.line_definition_owner_code = xal.line_definition_owner_code
AND h.line_definition_code = xal.line_definition_code
AND xal.application_id = p_application_id
AND xal.amb_context_code = p_amb_context_code
AND xal.product_rule_type_code = p_owner_type
AND EXISTS (SELECT 1
FROM xla_aad_line_defn_assgns xad
,xla_line_defn_jlt_assgns xjl
,TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
WHERE xjl.application_id = p_application_id
AND xjl.amb_context_code = p_amb_context_code
AND xjl.event_class_code = h.event_class_code
AND xjl.accounting_line_type_code = h.accounting_line_type_code
AND xjl.accounting_line_code = h.accounting_line_code
AND xad.event_class_code = xjl.event_class_code
AND xad.event_type_code = xjl.event_type_code
AND xad.line_definition_owner_code = xjl.line_definition_owner_code
AND xad.line_definition_code = xjl.line_definition_code
AND xad.application_id = p_application_id
AND xad.amb_context_code = p_amb_context_code
AND xad.product_rule_type_code = p_owner_type
AND xad.product_rule_code = grp.product_rule_code
AND grp.group_num = l_curr_group_num
UNION
SELECT 1
FROM xla_aad_line_defn_assgns xad
,xla_mpa_jlt_assgns xjl
,TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
WHERE xjl.application_id = p_application_id
AND xjl.amb_context_code = p_amb_context_code
AND xjl.event_class_code = h.event_class_code
AND xjl.mpa_accounting_line_type_code = h.accounting_line_type_code
AND xjl.mpa_accounting_line_code = h.accounting_line_code
AND xad.event_class_code = xjl.event_class_code
AND xad.event_type_code = xjl.event_type_code
AND xad.line_definition_owner_code = xjl.line_definition_owner_code
AND xad.line_definition_code = xjl.line_definition_code
AND xad.application_id = p_application_id
AND xad.amb_context_code = p_amb_context_code
AND xad.product_rule_type_code = p_owner_type
AND xad.product_rule_code = grp.product_rule_code
AND grp.group_num = l_curr_group_num);
SELECT product_rule_code
FROM TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type))
WHERE group_num = 0;
l_updated BOOLEAN;
',updated_flag='||l_aad.updated_flag||
',leapfrog_flag='||l_aad.leapfrog_flag
,p_module => l_log_module
,p_level => C_LEVEL_ERROR);
,l_aad.updated_flag
,l_aad.leapfrog_flag
,NULL);
l_updated := FALSE;
IF (update_group_number(l_code
,l_curr_group_num)) THEN
l_updated := TRUE;
IF (NOT l_updated) THEN
IF (C_LEVEL_ERROR >= g_log_level) THEN
trace(p_msg => 'l_updated = FALSE, EXIT',
p_module => l_log_module,
p_level => C_LEVEL_ERROR);
l_updated := update_group_number(l_code
,l_curr_group_num);
PROCEDURE update_aad_version
(p_application_id IN INTEGER
,p_amb_context_code IN VARCHAR2
,p_owner_type IN VARCHAR2
,p_versioning_mode IN VARCHAR2
,p_user_version IN VARCHAR2
,p_version_comment IN VARCHAR2)
IS
CURSOR c_aad_version IS
SELECT distinct
grp.product_rule_code
,grp.version_num version_from
,(MAX(NVL(h.version_num,0)) OVER (PARTITION BY grp.group_num))+1 version_to
FROM TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
, xla_aads_h h
WHERE h.application_id(+) = p_application_id
AND h.product_rule_type_code(+) = p_owner_type
AND h.product_rule_code(+) = grp.product_rule_code
AND grp.group_num IN
(SELECT grp2.group_num
FROM TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp2
WHERE grp2.updated_flag = 'Y'
OR grp2.leapfrog_flag = 'Y');
SELECT grp.product_rule_code
, max(h.version_num) version_to
FROM TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp
, xla_aads_h h
WHERE h.application_id = p_application_id
AND h.product_rule_type_code = p_owner_type
AND h.product_rule_code = grp.product_rule_code
AND NOT EXISTS
(SELECT 1
FROM TABLE(CAST(g_aad_groups AS xla_aad_group_tbl_type)) grp2
WHERE (grp2.updated_flag = 'Y' OR
grp2.leapfrog_flag = 'Y')
AND grp2.group_num = grp.group_num)
GROUP BY grp.product_rule_code;
l_log_module := C_DEFAULT_MODULE||'.update_aad_version';
trace(p_msg => 'BEGIN of procedure update_aad_version',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
INSERT INTO xla_aads_h
(application_id
,product_rule_type_code
,product_rule_code
,version_num
,base_version_num
,user_version
,version_comment
,leapfrog_flag
,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
(p_application_id
,p_owner_type
,l_aad_codes(i)
,l_versions_to(i)
,l_versions_from(i)
,p_user_version
,p_version_comment
,DECODE(p_versioning_mode,'LEAPFROG','Y','N')
,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);
trace(p_msg => '# row inserted in xla_aads_h = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
UPDATE xla_product_rules_b
SET version_num = l_versions_to(i)
,updated_flag = 'N'
,product_rule_version = p_user_version
,creation_date = sysdate
,created_by = xla_environment_pkg.g_usr_id
,last_update_date = sysdate
,last_updated_by = xla_environment_pkg.g_usr_id
,last_update_login = xla_environment_pkg.g_login_id
WHERE application_id = p_application_id
AND amb_context_code = p_amb_context_code
AND product_rule_type_code = p_owner_type
AND product_rule_code = l_aad_codes(i);
trace(p_msg => '# row updated in xla_product_rules_b = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
UPDATE xla_aads_h
SET user_version = p_user_version
, version_comment = p_version_comment
, program_update_date = sysdate
, program_application_id = xla_environment_pkg.g_prog_appl_id
, program_id = xla_environment_pkg.g_prog_id
, request_id = xla_environment_pkg.g_req_Id
WHERE application_id = p_application_id
AND product_rule_type_code = p_owner_type
AND product_rule_code = l_aad_codes(i)
AND version_num = l_versions_to(i)
AND (NVL(user_version,C_CHAR) <> NVL(p_user_version,C_CHAR) OR
NVL(version_comment,C_CHAR) <> NVL(p_version_comment,C_CHAR));
trace(p_msg => '# row updated in xla_aads_h = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
trace(p_msg => 'END of procedure update_aad_version',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
,p_value_1 => 'xla_aad_export_pvt.update_aad_version'
,p_token_2 => 'ERROR'
,p_value_2 => 'unhandled exception');
END update_aad_version;
PROCEDURE update_ac_version
(p_application_id IN INTEGER
,p_amb_context_code IN VARCHAR2
,p_versioning_mode IN VARCHAR2)
IS
CURSOR c_ac IS
SELECT b.analytical_criterion_type_code
,b.analytical_criterion_code
,b.version_num version_from
,MAX(NVL(h.version_num,0))+1 version_to
FROM xla_analytical_hdrs_b b
,xla_amb_components_h h
WHERE h.component_owner_code(+) = b.analytical_criterion_type_code
AND h.component_code(+) = b.analytical_criterion_code
AND h.component_type_code(+) = 'ANALYTICAL_CRITERION'
AND b.updated_flag = 'Y'
AND EXISTS
(SELECT 1
FROM xla_aad_header_ac_assgns a
WHERE a.application_id = p_application_id
AND a.amb_context_code = p_amb_context_code
AND b.amb_context_code = a.amb_context_code
AND b.analytical_criterion_type_code = a.analytical_criterion_type_code
AND b.analytical_criterion_code = a.analytical_criterion_code
UNION
SELECT 1
FROM xla_aad_line_defn_assgns l
, xla_line_defn_ac_assgns a
WHERE l.application_id = p_application_id
AND l.amb_context_code = p_amb_context_code
AND a.application_id = l.application_id
AND a.amb_context_code = l.amb_context_code
AND a.event_class_code = l.event_class_code
AND a.event_type_code = l.event_type_code
AND a.line_definition_owner_code = l.line_definition_owner_code
AND a.line_definition_code = l.line_definition_code
AND b.amb_context_code = a.amb_context_code
AND b.analytical_criterion_type_code = a.analytical_criterion_type_code
AND b.analytical_criterion_code = a.analytical_criterion_code)
GROUP BY b.analytical_criterion_type_code, b.analytical_criterion_code, b.version_num;
l_log_module := C_DEFAULT_MODULE||'.update_ac_version';
trace(p_msg => 'BEGIN of procedure update_ac_version',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
INSERT INTO xla_amb_components_h
(component_type_code
,component_owner_code
,component_code
,application_id
,version_num
,base_version_num
,leapfrog_flag
,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
('ANALYTICAL_CRITERION'
,l_ac_owner_codes(i)
,l_ac_codes(i)
,-1
,l_ac_version_to(i)
,l_ac_version_from(i)
,DECODE(p_versioning_mode,'LEAPFROG','Y','N')
,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);
trace(p_msg => '# row inserted in xla_amb_components_h = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
UPDATE xla_analytical_hdrs_b
SET version_num = l_ac_version_to(i)
,updated_flag = 'N'
,creation_date = sysdate
,created_by = xla_environment_pkg.g_usr_id
,last_update_date = sysdate
,last_updated_by = xla_environment_pkg.g_usr_id
,last_update_login = xla_environment_pkg.g_login_id
WHERE analytical_criterion_type_code = l_ac_owner_codes(i)
AND analytical_criterion_code = l_ac_codes(i)
AND amb_context_code = p_amb_context_code;
trace(p_msg => '# row updated in xla_analytical_hdrs_b = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
trace(p_msg => 'END of procedure update_ac_version',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
,p_value_1 => 'xla_aad_export_pvt.update_ac_version'
,p_token_2 => 'ERROR'
,p_value_2 => 'unhandled exception');
END update_ac_version;
PROCEDURE update_adr_version
(p_application_id IN INTEGER
,p_amb_context_code IN VARCHAR2
,p_versioning_mode IN VARCHAR2)
IS
CURSOR c_adr IS
SELECT b.segment_rule_type_code
,b.segment_rule_code
,b.version_num version_from
,MAX(NVL(h.version_num,0))+1 version_to
FROM xla_seg_rules_b b
,xla_amb_components_h h
WHERE h.application_id(+) = b.application_id
AND h.component_owner_code(+) = b.segment_rule_type_code
AND h.component_code(+) = b.segment_rule_code
AND h.component_type_code(+) = 'AMB_ADR'
AND b.amb_context_code = p_amb_context_code
AND b.application_id = p_application_id
AND b.updated_flag = 'Y'
GROUP BY b.segment_rule_type_code, b.segment_rule_code, b.version_num;
l_log_module := C_DEFAULT_MODULE||'.update_adr_version';
trace(p_msg => 'BEGIN of procedure update_adr_version',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
INSERT INTO xla_amb_components_h
(application_id
,component_type_code
,component_owner_code
,component_code
,version_num
,base_version_num
,leapfrog_flag
,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
(p_application_id
,'AMB_ADR'
,l_adr_owner_codes(i)
,l_adr_codes(i)
,l_adr_version_to(i)
,l_adr_version_from(i)
,DECODE(p_versioning_mode,'LEAPFROG','Y','N')
,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);
trace(p_msg => '# row inserted in xla_amb_components_h = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
UPDATE xla_seg_rules_b
SET version_num = l_adr_version_to(i)
,updated_flag = 'N'
,creation_date = sysdate
,created_by = xla_environment_pkg.g_usr_id
,last_update_date = sysdate
,last_updated_by = xla_environment_pkg.g_usr_id
,last_update_login = xla_environment_pkg.g_login_id
WHERE segment_rule_type_code = l_adr_owner_codes(i)
AND segment_rule_code = l_adr_codes(i)
AND application_id = p_application_id
AND amb_context_code = p_amb_context_code;
trace(p_msg => '# row updated in xla_seg_rules_b = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
trace(p_msg => 'END of procedure update_adr_version',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
,p_value_1 => 'xla_aad_export_pvt.update_adr_version'
,p_token_2 => 'ERROR'
,p_value_2 => 'unhandled exception');
END update_adr_version;
PROCEDURE update_ms_version
(p_application_id IN INTEGER
,p_amb_context_code IN VARCHAR2
,p_versioning_mode IN VARCHAR2)
IS
CURSOR c_ms IS
SELECT b.mapping_set_code
,b.version_num version_from
,MAX(NVL(h.version_num,0))+1 version_to
FROM xla_mapping_sets_b b
,xla_amb_components_h h
WHERE h.component_code(+) = b.mapping_set_code
AND h.component_type_code(+) = 'MAPPING_SET'
AND b.updated_flag = 'Y'
AND b.amb_context_code = p_amb_context_code
GROUP BY b.mapping_set_code, b.version_num;
l_log_module := C_DEFAULT_MODULE||'.update_ms_version';
trace(p_msg => 'BEGIN of procedure update_ms_version',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
INSERT INTO xla_amb_components_h
(component_type_code
,component_owner_code
,component_code
,application_id
,version_num
,base_version_num
,leapfrog_flag
,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
('MAPPING_SET'
,'X'
,l_ms_codes(i)
,-1
,l_ms_version_to(i)
,l_ms_version_from(i)
,DECODE(p_versioning_mode,'LEAPFROG','Y','N')
,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);
trace(p_msg => '# row inserted in xla_amb_components_h = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
UPDATE xla_mapping_sets_b
SET version_num = l_ms_version_to(i)
,updated_flag = 'N'
,creation_date = sysdate
,created_by = xla_environment_pkg.g_usr_id
,last_update_date = sysdate
,last_updated_by = xla_environment_pkg.g_usr_id
,last_update_login = xla_environment_pkg.g_login_id
WHERE mapping_set_code = l_ms_codes(i)
AND amb_context_code = p_amb_context_code;
trace(p_msg => '# row updated in xla_mapping_sets_b = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
trace(p_msg => 'END of procedure update_ms_version',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
,p_value_1 => 'xla_aad_export_pvt.update_ms_version'
,p_token_2 => 'ERROR'
,p_value_2 => 'unhandled exception');
END update_ms_version;
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)
SELECT xla_aad_loader_logs_s.nextval
,p_amb_context_code
,p_application_id
,'EXPORT'
,'EXPORTED_AAD'
,application_id
,product_rule_code
,product_rule_type_code
,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
WHERE application_id = p_application_id
AND amb_context_code = p_amb_context_code;
trace(p_msg => '# row inserted in xla_aad_loader_logs = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
update_aad_version
(p_application_id => p_application_id
,p_amb_context_code => p_amb_context_code
,p_owner_type => p_owner_type
,p_versioning_mode => p_versioning_mode
,p_user_version => p_user_version
,p_version_comment => p_version_comment);
update_ac_version
(p_application_id => p_application_id
,p_amb_context_code => p_amb_context_code
,p_versioning_mode => p_versioning_mode);
update_adr_version
(p_application_id => p_application_id
,p_amb_context_code => p_amb_context_code
,p_versioning_mode => p_versioning_mode);
update_ms_version
(p_application_id => p_application_id
,p_amb_context_code => p_amb_context_code
,p_versioning_mode => p_versioning_mode);
SELECT application_short_name
FROM fnd_application
WHERE application_id = p_application_id;