[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| 20-Oct-04 Wynne Chan Updated for Journal Lines Definitions |
| |
+======================================================================*/
TYPE t_array_codes IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
| delete_seg_rule_details |
| |
| Deletes all details of the segment rule |
| |
+======================================================================*/
PROCEDURE delete_seg_rule_details
(p_application_id IN NUMBER
,p_amb_context_code IN VARCHAR2
,p_segment_rule_type_code IN VARCHAR2
,p_segment_rule_code IN VARCHAR2)
IS
l_segment_rule_detail_id NUMBER(38);
SELECT segment_rule_detail_id
FROM xla_seg_rule_details
WHERE application_id = p_application_id
AND amb_context_code = p_amb_context_code
AND segment_rule_type_code = p_segment_rule_type_code
AND segment_rule_code = p_segment_rule_code;
xla_utility_pkg.trace('> xla_seg_rules_pkg.delete_seg_rule_details' , 10);
xla_conditions_pkg.delete_condition
(p_context => 'S'
,p_segment_rule_detail_id => l_segment_rule_detail_id);
DELETE
FROM xla_seg_rule_details
WHERE application_id = p_application_id
AND amb_context_code = p_amb_context_code
AND segment_rule_type_code = p_segment_rule_type_code
AND segment_rule_code = p_segment_rule_code;
xla_utility_pkg.trace('< xla_seg_rules_pkg.delete_seg_rule_details' , 10);
(p_location => 'xla_seg_rules_pkg.delete_seg_rule_details');
END delete_seg_rule_details;
l_last_update_date DATE;
l_last_update_login INTEGER;
l_last_updated_by INTEGER;
SELECT segment_rule_detail_id, user_sequence,
value_type_code, value_source_application_id, value_source_type_code,
value_source_code, value_constant, value_code_combination_id,
value_mapping_set_code,
value_flexfield_segment_code, input_source_application_id,
input_source_type_code, input_source_code,
value_segment_rule_appl_id, value_segment_rule_type_code,
value_segment_rule_code, value_adr_version_num
FROM xla_seg_rule_details
WHERE application_id = p_application_id
AND amb_context_code = p_amb_context_code
AND segment_rule_type_code = p_old_segment_rule_type_code
AND segment_rule_code = p_old_segment_rule_code;
SELECT flexfield_application_id, id_flex_code
FROM xla_sources_b
WHERE application_id = l_seg_rule_detail.input_source_application_id
AND source_type_code = l_seg_rule_detail.input_source_type_code
AND source_code = l_seg_rule_detail.input_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 segment_rule_detail_id = l_seg_rule_detail.segment_rule_detail_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_seg_rule_details_s.nextval
INTO l_new_segment_rule_detail_id
FROM DUAL;
INSERT INTO xla_seg_rule_details
(segment_rule_detail_id
,application_id
,amb_context_code
,segment_rule_type_code
,segment_rule_code
,user_sequence
,value_type_code
,value_source_application_id
,value_source_type_code
,value_source_code
,value_constant
,value_mapping_set_code
,value_flexfield_segment_code
,input_source_application_id
,input_source_type_code
,input_source_code
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,value_code_combination_id
,value_segment_rule_appl_id
,value_segment_rule_type_code
,value_segment_rule_code
,value_adr_version_num
)
VALUES
(l_new_segment_rule_detail_id
,p_application_id
,p_amb_context_code
,p_new_segment_rule_type_code
,p_new_segment_rule_code
,l_seg_rule_detail.user_sequence
,l_seg_rule_detail.value_type_code
,l_seg_rule_detail.value_source_application_id
,l_seg_rule_detail.value_source_type_code
,l_seg_rule_detail.value_source_code
,l_seg_rule_detail.value_constant
,l_seg_rule_detail.value_mapping_set_code
,l_value_flexfield_segment_code
,l_seg_rule_detail.input_source_application_id
,l_seg_rule_detail.input_source_type_code
,l_seg_rule_detail.input_source_code
,l_creation_date
,l_created_by
,l_last_update_date
,l_last_updated_by
,l_last_update_login
,l_seg_rule_detail.value_code_combination_id
,l_seg_rule_detail.value_segment_rule_appl_id
,l_seg_rule_detail.value_segment_rule_type_code
,l_seg_rule_detail.value_segment_rule_code
,l_seg_rule_detail.value_adr_version_num
);
SELECT xla_conditions_s.nextval
INTO l_condition_id
FROM DUAL;
INSERT INTO xla_conditions
(condition_id
,user_sequence
,application_id
,amb_context_code
,segment_rule_detail_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_segment_rule_detail_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 event_class_code, event_type_code, line_definition_owner_code, line_definition_code
FROM xla_line_defn_adr_assgns
WHERE application_id = p_application_id
AND amb_context_code = p_amb_context_code
AND segment_rule_type_code = p_segment_rule_type_code
AND segment_rule_code = p_segment_rule_code;
SELECT event_class_code, event_type_code, line_definition_owner_code, line_definition_code
FROM xla_line_defn_adr_assgns s
WHERE application_id = p_application_id
AND amb_context_code = p_amb_context_code
AND segment_rule_type_code = p_segment_rule_type_code
AND segment_rule_code = p_segment_rule_code
AND exists (SELECT 'y'
FROM xla_line_defn_jlt_assgns p
WHERE p.application_id = s.application_id
AND p.amb_context_code = s.amb_context_code
AND p.event_class_code = s.event_class_code
AND p.event_type_code = s.event_type_code
AND p.line_definition_owner_code = s.line_definition_owner_code
AND p.line_definition_code = s.line_definition_code
AND p.accounting_line_type_code = s.accounting_line_type_code
AND p.accounting_line_code = s.accounting_line_code
AND active_flag = 'Y');
IF p_event in ('DELETE','UPDATE') THEN
OPEN c_assignment_exist;
SELECT 'x'
FROM xla_line_defn_adr_assgns s
WHERE application_id = p_application_id
AND amb_context_code = p_amb_context_code
AND segment_rule_type_code = p_segment_rule_type_code
AND segment_rule_code = p_segment_rule_code
AND exists (SELECT 'x'
FROM xla_aad_line_defn_assgns a
,xla_prod_acct_headers h
WHERE h.application_id = a.application_id
AND h.amb_context_code = a.amb_context_code
AND h.product_rule_type_code = a.product_rule_type_code
AND h.product_rule_code = a.product_rule_code
AND h.event_class_code = a.event_class_code
AND h.event_type_code = a.event_type_code
AND h.locking_status_flag = 'Y'
AND a.application_id = s.application_id
AND a.amb_context_code = s.amb_context_code
AND a.event_class_code = s.event_class_code
AND a.event_type_code = s.event_type_code
AND a.line_definition_owner_code = s.line_definition_owner_code
AND a.line_definition_code = s.line_definition_code);
SELECT 'x'
FROM xla_tab_acct_def_details s
WHERE application_id = p_application_id
AND amb_context_code = p_amb_context_code
AND segment_rule_type_code = p_segment_rule_type_code
AND segment_rule_code = p_segment_rule_code
AND exists (SELECT 'x'
FROM xla_tab_acct_defs_b a
WHERE a.application_id = s.application_id
AND a.amb_context_code = s.amb_context_code
AND a.account_definition_type_code = s.account_definition_type_code
AND a.account_definition_code = s.account_definition_code
AND a.locking_status_flag = 'Y');
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_adr_assgns xld
,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 = xld.application_id
AND xal.amb_context_code = xld.amb_context_code
AND xal.event_class_code = xld.event_class_code
AND xal.event_type_code = xld.event_type_code
AND xal.line_definition_owner_code = xld.line_definition_owner_code
AND xal.line_definition_code = xld.line_definition_code
AND xld.application_id = p_application_id
AND xld.amb_context_code = p_amb_context_code
AND xld.segment_rule_type_code = p_segment_rule_type_code
AND xld.segment_rule_code = p_segment_rule_code
FOR UPDATE NOWAIT;
CURSOR c_update_aads IS
SELECT distinct xal.event_class_code
, xal.product_rule_type_code
, xal.product_rule_code
FROM xla_line_defn_adr_assgns xad
,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.event_class_code = xal.event_class_code
AND xpa.event_type_code = xal.event_type_code
AND xal.application_id = xad.application_id
AND xal.amb_context_code = xad.amb_context_code
AND xal.event_class_code = xad.event_class_code
AND xal.event_type_code = xad.event_type_code
AND xal.line_definition_owner_code = xad.line_definition_owner_code
AND xal.line_definition_code = xad.line_definition_code
AND xad.application_id = p_application_id
AND xad.amb_context_code = p_amb_context_code
AND xad.segment_rule_type_code = p_segment_rule_type_code
AND xad.segment_rule_code = p_segment_rule_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_adr_assgns xad
WHERE xad.application_id = p_application_id
AND xad.amb_context_code = p_amb_context_code
AND xad.segment_rule_type_code = p_segment_rule_type_code
AND xad.segment_rule_code = p_segment_rule_code
AND xad.event_class_code = xld.event_class_code
AND xad.event_type_code = xld.event_type_code
AND xad.line_definition_owner_code = xld.line_definition_owner_code
AND xad.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 xpa
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 application_id, amb_context_code, account_definition_code,
account_definition_type_code,
account_type_code
FROM xla_tab_acct_def_details
WHERE application_id = p_application_id
AND amb_context_code = p_amb_context_code
AND segment_rule_type_code = p_segment_rule_type_code
AND segment_rule_code = p_segment_rule_code;
IF p_event in ('DELETE','UPDATE','DISABLE') THEN
OPEN c_assignment_exist;
SELECT application_id, amb_context_code, account_definition_type_code,
account_definition_code
FROM xla_tab_acct_defs_b p
WHERE exists (SELECT 'x'
FROM xla_tab_acct_def_details s
WHERE s.application_id = p_application_id
AND s.amb_context_code = p_amb_context_code
AND s.segment_rule_type_code = p_segment_rule_type_code
AND s.segment_rule_code = p_segment_rule_code
AND s.application_id = p.application_id
AND s.amb_context_code = p.amb_context_code
AND s.account_definition_type_code = p.account_definition_type_code
AND s.account_definition_code = p.account_definition_code);
SELECT value_constant
FROM xla_seg_rule_details seg
WHERE application_id = p_application_id
AND amb_context_code = p_amb_context_code
AND segment_rule_type_code = p_old_segment_rule_type_code
AND segment_rule_code = p_old_segment_rule_code
AND not exists (SELECT 'x'
FROM fnd_flex_values ffv
WHERE ffv.flex_value_set_id = p_new_flex_value_set_id
AND ffv.flex_value = seg.value_constant);
SELECT segment_rule_detail_id, user_sequence,
value_type_code, value_source_application_id, value_source_type_code,
value_source_code, value_constant, value_code_combination_id,
value_mapping_set_code,
value_flexfield_segment_code, input_source_application_id,
input_source_type_code, input_source_code
FROM xla_seg_rule_details
WHERE application_id = p_application_id
AND amb_context_code = p_amb_context_code
AND segment_rule_type_code = p_old_segment_rule_type_code
AND segment_rule_code = p_old_segment_rule_code;
SELECT flexfield_application_id, id_flex_code
FROM xla_sources_b
WHERE application_id = l_seg_rule_detail.input_source_application_id
AND source_type_code = l_seg_rule_detail.input_source_type_code
AND source_code = l_seg_rule_detail.input_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 segment_rule_detail_id = l_seg_rule_detail.segment_rule_detail_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;
SELECT application_id
,amb_context_code
,segment_rule_type_code
,segment_rule_code
FROM xla_seg_rule_details
WHERE amb_context_code = p_amb_context_code
AND value_segment_rule_appl_id = p_application_id
AND value_segment_rule_type_code = p_segment_rule_type_code
AND value_segment_rule_code = p_segment_rule_code;
SELECT application_id
,amb_context_code
,segment_rule_type_code
,segment_rule_code
FROM xla_seg_rule_details s
WHERE value_segment_rule_appl_id = p_application_id
AND value_segment_rule_type_code = p_segment_rule_type_code
AND value_segment_rule_code = p_segment_rule_code;