[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| delete_description_details |
| |
| Deletes all details of the description |
| |
+======================================================================*/
PROCEDURE delete_description_details
(p_application_id IN NUMBER
,p_amb_context_code IN VARCHAR2
,p_description_type_code IN VARCHAR2
,p_description_code IN VARCHAR2)
IS
l_description_prio_id NUMBER(38);
SELECT description_prio_id
FROM xla_desc_priorities
WHERE application_id = p_application_id
AND amb_context_code = p_amb_context_code
AND description_type_code = p_description_type_code
AND description_code = p_description_code;
l_log_module := C_DEFAULT_MODULE||'.delete_description_details';
trace(p_msg => 'BEGIN of procedure delete_description_details'
,p_module => l_log_module
,p_level => C_LEVEL_PROCEDURE);
xla_conditions_pkg.delete_condition
(p_context => 'D'
,p_description_prio_id => l_description_prio_id);
xla_descript_details_pkg.delete_desc_prio_details
(p_description_prio_id => l_description_prio_id);
DELETE
FROM xla_desc_priorities
WHERE application_id = p_application_id
AND amb_context_code = p_amb_context_code
AND description_type_code = p_description_type_code
AND description_code = p_description_code;
trace(p_msg => 'END of procedure delete_description_details'
,p_module => l_log_module
,p_level => C_LEVEL_PROCEDURE);
(p_location => 'xla_descriptions_pkg.delete_description_details');
END delete_description_details;
l_last_update_date DATE;
l_last_update_login INTEGER;
l_last_updated_by INTEGER;
SELECT description_prio_id, user_sequence
FROM xla_desc_priorities
WHERE application_id = p_application_id
AND amb_context_code = p_amb_context_code
AND description_type_code = p_old_description_type_code
AND description_code = p_old_description_code;
SELECT user_sequence, value_type_code, literal, source_application_id,
source_type_code, source_code, flexfield_segment_code, display_description_flag
FROM xla_descript_details_vl
WHERE description_prio_id = l_description_priority.description_prio_id;
SELECT flexfield_application_id, id_flex_code
FROM xla_sources_b
WHERE application_id = l_description_detail.source_application_id
AND source_type_code = l_description_detail.source_type_code
AND source_code = l_description_detail.source_code;
SELECT user_sequence, bracket_left_code, bracket_right_code, value_type_code,
source_application_id, source_type_code, source_code,
flexfield_segment_code, value_flexfield_segment_code,
value_source_application_id, value_source_type_code,
value_source_code, value_constant, line_operator_code,
logical_operator_code, independent_value_constant
FROM xla_conditions
WHERE description_prio_id = l_description_priority.description_prio_id;
SELECT flexfield_application_id, id_flex_code
FROM xla_sources_b
WHERE application_id = l_detail_condition.source_application_id
AND source_type_code = l_detail_condition.source_type_code
AND source_code = l_detail_condition.source_code;
SELECT flexfield_application_id, id_flex_code
FROM xla_sources_b
WHERE application_id = l_detail_condition.value_source_application_id
AND source_type_code = l_detail_condition.value_source_type_code
AND source_code = l_detail_condition.value_source_code;
l_last_update_date := sysdate;
l_last_update_login := xla_environment_pkg.g_login_id;
l_last_updated_by := xla_environment_pkg.g_usr_id;
SELECT xla_desc_priorities_s.nextval
INTO l_new_description_prio_id
FROM DUAL;
INSERT INTO xla_desc_priorities
(description_prio_id
,application_id
,amb_context_code
,description_type_code
,description_code
,user_sequence
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
VALUES
(l_new_description_prio_id
,p_application_id
,p_amb_context_code
,p_new_description_type_code
,p_new_description_code
,l_description_priority.user_sequence
,l_creation_date
,l_created_by
,l_last_update_date
,l_last_updated_by
,l_last_update_login);
SELECT xla_descript_details_s.nextval
INTO l_description_detail_id
FROM DUAL;
xla_descript_details_f_pkg.insert_row
(x_rowid => l_row_id
,x_description_detail_id => l_description_detail_id
,x_description_prio_id => l_new_description_prio_id
,x_amb_context_code => p_amb_context_code
,x_user_sequence => l_description_detail.user_sequence
,x_value_type_code => l_description_detail.value_type_code
,x_source_application_id => l_description_detail.source_application_id
,x_source_type_code => l_description_detail.source_type_code
,x_source_code => l_description_detail.source_code
,x_flexfield_segment_code => l_flexfield_segment_code
,x_literal => l_description_detail.literal
,x_display_description_flag => l_description_detail.display_description_flag
,x_creation_date => l_creation_date
,x_created_by => l_created_by
,x_last_update_date => l_last_update_date
,x_last_updated_by => l_last_updated_by
,x_last_update_login => l_last_update_login);
SELECT xla_conditions_s.nextval
INTO l_condition_id
FROM DUAL;
INSERT INTO xla_conditions
(condition_id
,user_sequence
,application_id
,amb_context_code
,description_prio_id
,bracket_left_code
,bracket_right_code
,value_type_code
,source_application_id
,source_type_code
,source_code
,flexfield_segment_code
,value_flexfield_segment_code
,value_source_application_id
,value_source_type_code
,value_source_code
,value_constant
,line_operator_code
,logical_operator_code
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,independent_value_constant)
VALUES
(l_condition_id
,l_detail_condition.user_sequence
,p_application_id
,p_amb_context_code
,l_new_description_prio_id
,l_detail_condition.bracket_left_code
,l_detail_condition.bracket_right_code
,l_detail_condition.value_type_code
,l_detail_condition.source_application_id
,l_detail_condition.source_type_code
,l_detail_condition.source_code
,l_con_flexfield_segment_code
,l_con_v_flexfield_segment_code
,l_detail_condition.value_source_application_id
,l_detail_condition.value_source_type_code
,l_detail_condition.value_source_code
,l_detail_condition.value_constant
,l_detail_condition.line_operator_code
,l_detail_condition.logical_operator_code
,l_creation_date
,l_created_by
,l_last_update_date
,l_last_updated_by
,l_last_update_login
,l_detail_condition.independent_value_constant);
SELECT application_id, amb_context_code, product_rule_type_code, product_rule_code,
entity_code, event_class_code
FROM xla_prod_acct_headers
WHERE application_id = p_application_id
AND amb_context_code = p_amb_context_code
AND description_type_code = p_description_type_code
AND description_code = p_description_code;
SELECT line_definition_owner_code, line_definition_code,
event_class_code, event_type_code
FROM xla_line_defn_jlt_assgns
WHERE application_id = p_application_id
AND amb_context_code = p_amb_context_code
AND description_type_code = p_description_type_code
AND description_code = p_description_code;
SELECT line_definition_owner_code, line_definition_code,
event_class_code, event_type_code
FROM xla_line_defn_jlt_assgns
WHERE application_id = p_application_id
AND amb_context_code = p_amb_context_code
AND description_type_code = p_description_type_code
AND description_code = p_description_code
AND active_flag = 'Y';
IF p_event in ('DELETE','UPDATE') THEN
OPEN c_header_assign_exist;
SELECT 'x'
FROM xla_prod_acct_headers h
WHERE application_id = p_application_id
AND amb_context_code = p_amb_context_code
AND description_type_code = p_description_type_code
AND description_code = p_description_code
AND locking_status_flag = 'Y';
SELECT 'x'
FROM xla_line_defn_jlt_assgns xjl
,xla_aad_line_defn_assgns xal
,xla_prod_acct_headers xpa
WHERE xpa.application_id = xal.application_id
AND xpa.amb_context_code = xal.amb_context_code
AND xpa.product_rule_type_code = xal.product_rule_type_code
AND xpa.product_rule_code = xal.product_rule_code
AND xpa.event_class_code = xal.event_class_code
AND xpa.event_type_code = xal.event_type_code
AND xpa.locking_status_flag = 'Y'
AND xal.application_id = xjl.application_id
AND xal.amb_context_code = xjl.amb_context_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 xjl.application_id = p_application_id
AND xjl.amb_context_code = p_amb_context_code
AND xjl.description_type_code = p_description_type_code
AND xjl.description_code = p_description_code;
SELECT xpa.entity_code
, xpa.event_class_code
, xpa.event_type_code
, xpa.product_rule_type_code
, xpa.product_rule_code
, xpa.locking_status_flag
, xpa.validation_status_code
FROM xla_line_defn_jlt_assgns xjl
,xla_aad_line_defn_assgns xal
,xla_prod_acct_headers xpa
WHERE xpa.application_id = xal.application_id
AND xpa.amb_context_code = xal.amb_context_code
AND xpa.product_rule_type_code = xal.product_rule_type_code
AND xpa.product_rule_code = xal.product_rule_code
AND xpa.event_class_code = xal.event_class_code
AND xpa.event_type_code = xal.event_type_code
AND xal.application_id = xjl.application_id
AND xal.amb_context_code = xjl.amb_context_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 xjl.application_id = p_application_id
AND xjl.amb_context_code = p_amb_context_code
AND xjl.description_type_code = p_description_type_code
AND xjl.description_code = p_description_code
FOR UPDATE NOWAIT;
SELECT xpa.entity_code
, xpa.event_class_code
, xpa.event_type_code
, xpa.product_rule_type_code
, xpa.product_rule_code
, xpa.locking_status_flag
, xpa.validation_status_code
FROM xla_prod_acct_headers xpa
WHERE xpa.application_id = p_application_id
AND xpa.amb_context_code = p_amb_context_code
AND xpa.description_type_code = p_description_type_code
AND xpa.description_code = p_description_code
FOR UPDATE NOWAIT;
CURSOR c_update_aads IS
SELECT xpa.event_class_code
, xpa.product_rule_type_code
, xpa.product_rule_code
FROM xla_prod_acct_headers xpa
WHERE xpa.application_id = p_application_id
AND xpa.amb_context_code = p_amb_context_code
AND xpa.description_type_code = p_description_type_code
AND xpa.description_code = p_description_code
UNION
SELECT xpa.event_class_code
, xpa.product_rule_type_code
, xpa.product_rule_code
FROM xla_prod_acct_headers xpa
,xla_aad_line_defn_assgns xal
,xla_line_defn_jlt_assgns xjl
WHERE xpa.application_id = xal.application_id
AND xpa.amb_context_code = xal.amb_context_code
AND xpa.product_rule_type_code = xal.product_rule_type_code
AND xpa.product_rule_code = xal.product_rule_code
AND xpa.event_class_code = xal.event_class_code
AND xpa.event_type_code = xal.event_type_code
AND xal.application_id = xjl.application_id
AND xal.amb_context_code = xjl.amb_context_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 xjl.application_id = p_application_id
AND xjl.amb_context_code = p_amb_context_code
AND xjl.description_type_code = p_description_type_code
AND xjl.description_code = p_description_code;
UPDATE xla_line_definitions_b xld
SET validation_status_code = 'N'
, last_update_date = sysdate
, last_updated_by = xla_environment_pkg.g_usr_id
, last_update_login = xla_environment_pkg.g_login_id
WHERE xld.application_id = p_application_id
AND xld.amb_context_code = p_amb_context_code
AND xld.validation_status_code <> 'N'
AND EXISTS
(SELECT 1
FROM xla_line_defn_jlt_assgns xjl
WHERE xjl.application_id = p_application_id
AND xjl.amb_context_code = p_amb_context_code
AND xjl.description_type_code = p_description_type_code
AND xjl.description_code = p_description_code
AND xjl.event_class_code = xld.event_class_code
AND xjl.event_type_code = xld.event_type_code
AND xjl.line_definition_owner_code = xld.line_definition_owner_code
AND xjl.line_definition_code = xld.line_definition_code);
OPEN c_update_aads;
FETCH c_update_aads BULK COLLECT INTO l_event_class_codes
,l_product_rule_type_codes
,l_product_rule_codes;
CLOSE c_update_aads;
UPDATE xla_product_rules_b
SET compile_status_code = 'N'
, updated_flag = 'Y'
, 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 = l_product_rule_type_codes(i)
AND product_rule_code = l_product_rule_codes(i)
AND (compile_status_code <> 'N' OR
updated_flag <> 'Y');
UPDATE xla_prod_acct_headers
SET validation_status_code = 'N'
, 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 event_class_code = l_event_class_codes(i)
AND product_rule_type_code = l_product_rule_type_codes(i)
AND product_rule_code = l_product_rule_codes(i)
AND validation_status_code <> 'N';
UPDATE xla_appli_amb_contexts
SET updated_flag = 'Y'
, 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 updated_flag <> 'Y';
IF c_update_aads%ISOPEN THEN
CLOSE c_update_aads;
IF c_update_aads%ISOPEN THEN
CLOSE c_update_aads;
SELECT description_prio_id, user_sequence
FROM xla_desc_priorities
WHERE application_id = p_application_id
AND amb_context_code = p_amb_context_code
AND description_type_code = p_old_description_type_code
AND description_code = p_old_description_code;
SELECT user_sequence, value_type_code, literal, source_application_id,
source_type_code, source_code, flexfield_segment_code, display_description_flag
FROM xla_descript_details_vl
WHERE description_prio_id = l_description_priority.description_prio_id;
SELECT flexfield_application_id, id_flex_code
FROM xla_sources_b
WHERE application_id = l_description_detail.source_application_id
AND source_type_code = l_description_detail.source_type_code
AND source_code = l_description_detail.source_code;
SELECT user_sequence, bracket_left_code, bracket_right_code, value_type_code,
source_application_id, source_type_code, source_code,
flexfield_segment_code, value_flexfield_segment_code,
value_source_application_id, value_source_type_code,
value_source_code, value_constant, line_operator_code,
logical_operator_code, independent_value_constant
FROM xla_conditions
WHERE description_prio_id = l_description_priority.description_prio_id;
SELECT flexfield_application_id, id_flex_code
FROM xla_sources_b
WHERE application_id = l_detail_condition.source_application_id
AND source_type_code = l_detail_condition.source_type_code
AND source_code = l_detail_condition.source_code;
SELECT flexfield_application_id, id_flex_code
FROM xla_sources_b
WHERE application_id = l_detail_condition.value_source_application_id
AND source_type_code = l_detail_condition.value_source_type_code
AND source_code = l_detail_condition.value_source_code;