[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_last_update_date DATE;
g_last_update_login INTEGER;
g_last_updated_by INTEGER;
SELECT 'x'
FROM xla_evt_class_acct_attrs e
WHERE e.application_id = p_application_id
AND e.event_class_code = p_event_class_code
AND e.accounting_attribute_code = 'ACCOUNTING_REVERSAL_OPTION';
SELECT 'x'
FROM xla_analytical_hdrs_b a
WHERE amb_context_code = p_amb_context_code
AND analytical_criterion_code = p_analytical_criterion_code
AND analytical_criterion_type_code = p_anal_criterion_type_code
AND balancing_flag = 'Y';
SELECT 'x'
FROM xla_analytical_sources a
WHERE application_id = p_application_id
AND amb_context_code = p_amb_context_code
AND event_class_code = p_event_class_code
AND analytical_criterion_code = p_analytical_criterion_code
AND analytical_criterion_type_code = p_anal_criterion_type_code;
SELECT a.source_code, a.source_type_code
FROM xla_analytical_sources a
WHERE application_id = p_application_id
AND amb_context_code = p_amb_context_code
AND event_class_code = p_event_class_code
AND analytical_criterion_code = p_analytical_criterion_code
AND analytical_criterion_type_code = p_anal_criterion_type_code
AND source_type_code = 'S'
AND not exists (SELECT 'y'
FROM xla_event_sources s
WHERE s.source_application_id = a.source_application_id
AND s.source_type_code = a.source_type_code
AND s.source_code = a.source_code
AND s.application_id = p_application_id
AND s.event_class_code = p_event_class_code
AND s.active_flag = 'Y'
AND s.level_code = 'H');
SELECT a.source_code, a.source_type_code
FROM xla_analytical_sources a
WHERE application_id = p_application_id
AND amb_context_code = p_amb_context_code
AND event_class_code = p_event_class_code
AND analytical_criterion_code = p_analytical_criterion_code
AND analytical_criterion_type_code = p_anal_criterion_type_code
AND a.source_type_code = 'D';
SELECT d.source_type_code, d.source_code
FROM xla_descript_details_b d, xla_desc_priorities p
WHERE d.description_prio_id = p.description_prio_id
AND p.application_id = p_application_id
AND p.amb_context_code = p_amb_context_code
AND p.description_type_code = p_description_type_code
AND p.description_code = p_description_code
AND d.source_code is not null
AND d.source_type_code = 'S'
AND NOT EXISTS (SELECT 'y'
FROM xla_event_sources s
WHERE s.source_application_id = d.source_application_id
AND s.source_type_code = d.source_type_code
AND s.source_code = d.source_code
AND s.application_id = p_application_id
AND s.entity_code = p_entity_code
AND s.event_class_code = p_event_class_code
AND s.active_flag = 'Y'
AND s.level_code = 'H');
SELECT c.source_type_code source_type_code, c.source_code source_code
FROM xla_conditions c, xla_desc_priorities d
WHERE c.description_prio_id = d.description_prio_id
AND d.application_id = p_application_id
AND d.amb_context_code = p_amb_context_code
AND d.description_type_code = p_description_type_code
AND d.description_code = p_description_code
AND c.source_code is not null
AND c.source_type_code = 'S'
AND NOT EXISTS (SELECT 'y'
FROM xla_event_sources s
WHERE s.source_application_id = c.source_application_id
AND s.source_type_code = c.source_type_code
AND s.source_code = c.source_code
AND s.application_id = p_application_id
AND s.entity_code = p_entity_code
AND s.event_class_code = p_event_class_code
AND s.active_flag = 'Y'
AND s.level_code = 'H')
UNION
SELECT c.value_source_type_code source_type_code, c.value_source_code source_code
FROM xla_conditions c, xla_desc_priorities d
WHERE c.description_prio_id = d.description_prio_id
AND d.application_id = p_application_id
AND d.amb_context_code = p_amb_context_code
AND d.description_type_code = p_description_type_code
AND d.description_code = p_description_code
AND c.value_source_code is not null
AND c.value_source_type_code = 'S'
AND NOT EXISTS (SELECT 'y'
FROM xla_event_sources s
WHERE s.source_application_id = c.value_source_application_id
AND s.source_type_code = c.value_source_type_code
AND s.source_code = c.value_source_code
AND s.application_id = p_application_id
AND s.entity_code = p_entity_code
AND s.event_class_code = p_event_class_code
AND s.active_flag = 'Y'
AND s.level_code = 'H');
SELECT d.source_type_code source_type_code, d.source_code source_code
FROM xla_descript_details_b d, xla_desc_priorities p
WHERE d.description_prio_id = p.description_prio_id
AND p.application_id = p_application_id
AND p.amb_context_code = p_amb_context_code
AND p.description_type_code = p_description_type_code
AND p.description_code = p_description_code
AND d.source_code is not null
AND d.source_type_code = 'D';
SELECT c.source_type_code source_type_code, c.source_code source_code
FROM xla_conditions c, xla_desc_priorities d
WHERE c.description_prio_id = d.description_prio_id
AND d.application_id = p_application_id
AND d.amb_context_code = p_amb_context_code
AND d.description_type_code = p_description_type_code
AND d.description_code = p_description_code
AND c.source_code is not null
AND c.source_type_code = 'D'
UNION
SELECT c.value_source_type_code source_type_code, c.value_source_code source_code
FROM xla_conditions c, xla_desc_priorities d
WHERE c.description_prio_id = d.description_prio_id
AND d.application_id = p_application_id
AND d.amb_context_code = p_amb_context_code
AND d.description_type_code = p_description_type_code
AND d.description_code = p_description_code
AND c.value_source_code is not null
AND c.value_source_type_code = 'D';
| delete_product_rule_details |
| |
| Deletes all details of the product rule |
| |
+======================================================================*/
PROCEDURE delete_product_rule_details
(p_application_id IN NUMBER
,p_amb_context_code IN VARCHAR2
,p_product_rule_type_code IN VARCHAR2
,p_product_rule_code IN VARCHAR2)
IS
BEGIN
xla_utility_pkg.trace('> xla_product_rules_pkg.delete_product_rule_details' , 10);
DELETE
FROM xla_aad_header_ac_assgns
WHERE application_id = p_application_id
AND amb_context_code = p_amb_context_code
AND product_rule_type_code = p_product_rule_type_code
AND product_rule_code = p_product_rule_code;
DELETE
FROM xla_aad_hdr_acct_attrs
WHERE application_id = p_application_id
AND amb_context_code = p_amb_context_code
AND product_rule_type_code = p_product_rule_type_code
AND product_rule_code = p_product_rule_code;
DELETE
FROM xla_aad_line_defn_assgns
WHERE application_id = p_application_id
AND amb_context_code = p_amb_context_code
AND product_rule_type_code = p_product_rule_type_code
AND product_rule_code = p_product_rule_code;
DELETE
FROM xla_prod_acct_headers
WHERE application_id = p_application_id
AND amb_context_code = p_amb_context_code
AND product_rule_type_code = p_product_rule_type_code
AND product_rule_code = p_product_rule_code;
xla_utility_pkg.trace('< xla_product_rules_pkg.delete_product_rule_details' , 10);
(p_location => 'xla_product_rules_pkg.delete_product_rule_details');
END delete_product_rule_details;
| delete_prod_header_details |
| |
| Deletes all details of the event class and event type assignment |
| |
+======================================================================*/
PROCEDURE delete_prod_header_details
(p_application_id IN NUMBER
,p_amb_context_code IN VARCHAR2
,p_product_rule_type_code IN VARCHAR2
,p_product_rule_code IN VARCHAR2
,p_event_class_code IN VARCHAR2
,p_event_type_code IN VARCHAR2)
IS
BEGIN
xla_utility_pkg.trace('> xla_product_rules_pkg.delete_prod_header_details' , 10);
DELETE
FROM xla_aad_header_ac_assgns
WHERE application_id = p_application_id
AND amb_context_code = p_amb_context_code
AND product_rule_type_code = p_product_rule_type_code
AND product_rule_code = p_product_rule_code
AND event_class_code = p_event_class_code
AND event_type_code = p_event_type_code;
DELETE
FROM xla_aad_hdr_acct_attrs
WHERE application_id = p_application_id
AND amb_context_code = p_amb_context_code
AND product_rule_type_code = p_product_rule_type_code
AND product_rule_code = p_product_rule_code
AND event_class_code = p_event_class_code
AND event_type_code = p_event_type_code;
DELETE
FROM xla_aad_line_defn_assgns
WHERE application_id = p_application_id
AND amb_context_code = p_amb_context_code
AND product_rule_type_code = p_product_rule_type_code
AND product_rule_code = p_product_rule_code
AND event_class_code = p_event_class_code
AND event_type_code = p_event_type_code;
xla_utility_pkg.trace('< xla_product_rules_pkg.delete_prod_header_details' , 10);
(p_location => 'xla_product_rules_pkg.delete_prod_header_details');
END delete_prod_header_details;
l_last_update_date DATE;
l_last_update_login INTEGER;
l_last_updated_by INTEGER;
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;
INSERT INTO xla_prod_acct_headers
(application_id
,amb_context_code
,product_rule_type_code
,product_rule_code
,entity_code
,event_class_code
,event_type_code
,description_type_code
,description_code
,accounting_required_flag
,Locking_status_flag
,validation_status_code
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT p_application_id
,p_amb_context_code
,p_new_product_rule_type_code
,p_new_product_rule_code
,entity_code
,event_class_code
,event_type_code
,decode(p_include_header_assignments,'Y',description_type_code,NULL)
,decode(p_include_header_assignments,'Y',description_code,NULL)
,accounting_required_flag
,locking_status_flag
,'N'
,l_creation_date
,l_created_by
,l_last_update_date
,l_last_updated_by
,l_last_update_login
FROM xla_prod_acct_headers
WHERE application_id = p_application_id
AND amb_context_code = p_amb_context_code
AND product_rule_type_code = p_old_product_rule_type_code
AND product_rule_code = p_old_product_rule_code;
INSERT INTO xla_aad_header_ac_assgns
(application_id
,amb_context_code
,product_rule_type_code
,product_rule_code
,event_class_code
,event_type_code
,analytical_criterion_type_code
,analytical_criterion_code
,object_version_number
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT p_application_id
,p_amb_context_code
,p_new_product_rule_type_code
,p_new_product_rule_code
,event_class_code
,event_type_code
,analytical_criterion_type_code
,analytical_criterion_code
,1
,l_creation_date
,l_created_by
,l_last_update_date
,l_last_updated_by
,l_last_update_login
FROM xla_aad_header_ac_assgns
WHERE application_id = p_application_id
AND amb_context_code = p_amb_context_code
AND product_rule_type_code = p_old_product_rule_type_code
AND product_rule_code = p_old_product_rule_code;
INSERT INTO xla_aad_hdr_acct_attrs
(application_id
,amb_context_code
,product_rule_type_code
,product_rule_code
,event_class_code
,event_type_code
,accounting_attribute_code
,source_application_id
,source_type_code
,source_code
,event_class_default_flag
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT p_application_id
,p_amb_context_code
,p_new_product_rule_type_code
,p_new_product_rule_code
,event_class_code
,event_type_code
,accounting_attribute_code
,source_application_id
,source_type_code
,source_code
,event_class_default_flag
,l_creation_date
,l_created_by
,l_last_update_date
,l_last_updated_by
,l_last_update_login
FROM xla_aad_hdr_acct_attrs
WHERE application_id = p_application_id
AND amb_context_code = p_amb_context_code
AND product_rule_type_code = p_old_product_rule_type_code
AND product_rule_code = p_old_product_rule_code;
INSERT INTO xla_aad_line_defn_assgns
(application_id
,amb_context_code
,product_rule_type_code
,product_rule_code
,event_class_code
,event_type_code
,line_definition_owner_code
,line_definition_code
,object_version_number
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT p_application_id
,p_amb_context_code
,p_new_product_rule_type_code
,p_new_product_rule_code
,event_class_code
,event_type_code
,line_definition_owner_code
,line_definition_code
,1
,l_creation_date
,l_created_by
,l_last_update_date
,l_last_updated_by
,l_last_update_login
FROM xla_aad_line_defn_assgns
WHERE application_id = p_application_id
AND amb_context_code = p_amb_context_code
AND product_rule_type_code = p_old_product_rule_type_code
AND product_rule_code = p_old_product_rule_code;
SELECT accounting_method_code, accounting_method_type_code
FROM xla_acctg_method_rules
WHERE application_id = p_application_id
AND amb_context_code = p_amb_context_code
AND product_rule_type_code = p_product_rule_type_code
AND product_rule_code = p_product_rule_code;
IF p_event in ('DELETE','UPDATE','DISABLE') THEN
OPEN c_assignment_exist;
SELECT 'x'
FROM xla_product_rules_b
WHERE application_id = p_application_id
AND amb_context_code = p_amb_context_code
AND product_rule_type_code = p_product_rule_type_code
AND product_rule_code = p_product_rule_code
AND compile_status_code in ('E','N','Y')
AND locking_status_flag = 'N'
FOR UPDATE of compile_status_code NOWAIT;
UPDATE xla_product_rules_b
SET compile_status_code = 'N'
WHERE current of c_prod_rules;
SELECT 'x'
FROM xla_product_rules_b
WHERE application_id = p_application_id
AND amb_context_code = p_amb_context_code
AND product_rule_type_code = p_product_rule_type_code
AND product_rule_code = p_product_rule_code
FOR UPDATE of compile_status_code NOWAIT;
UPDATE xla_product_rules_b
SET compile_status_code = p_status
WHERE current of c_prod_rules;
SELECT 'x'
FROM xla_aad_hdr_acct_attrs
WHERE application_id = p_application_id
AND amb_context_code = p_amb_context_code
AND product_rule_type_code = p_product_rule_type_code
AND product_rule_code = p_product_rule_code
AND event_class_code = p_event_class_code
AND event_type_code = p_event_type_code;
SELECT e.accounting_attribute_code, e.source_application_id,
e.source_type_code, e.source_code
FROM xla_evt_class_acct_attrs e, xla_acct_attributes_b l
WHERE e.application_id = p_application_id
AND e.event_class_code = p_event_class_code
AND e.default_flag = 'Y'
AND e.accounting_attribute_code = l.accounting_attribute_code
AND l.assignment_level_code = 'EVT_CLASS_AAD';
INSERT into xla_aad_hdr_acct_attrs(
application_id
,amb_context_code
,product_rule_type_code
,product_rule_code
,event_class_code
,event_type_code
,accounting_attribute_code
,source_application_id
,source_type_code
,source_code
,event_class_default_flag
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
(SELECT distinct p_application_id
,p_amb_context_code
,p_product_rule_type_code
,p_product_rule_code
,p_event_class_code
,p_event_type_code
,e.accounting_attribute_code
,null
,null
,null
,decode(e.accounting_attribute_code,'ACCRUAL_REVERSAL_GL_DATE'
,'N','Y')
,g_creation_date
,g_created_by
,g_last_update_date
,g_last_updated_by
,g_last_update_login
FROM xla_evt_class_acct_attrs e, xla_acct_attributes_b l
WHERE e.application_id = p_application_id
AND e.event_class_code = p_event_class_code
AND e.accounting_attribute_code = l.accounting_attribute_code
AND l.assignment_level_code = 'EVT_CLASS_AAD'
UNION
SELECT distinct p_application_id
,p_amb_context_code
,p_product_rule_type_code
,p_product_rule_code
,p_event_class_code
,p_event_type_code
,l.accounting_attribute_code
,null
,null
,null
,'N'
,g_creation_date
,g_created_by
,g_last_update_date
,g_last_updated_by
,g_last_update_login
FROM xla_acct_attributes_b l
WHERE l.assignment_level_code = 'AAD_ONLY');
UPDATE xla_aad_hdr_acct_attrs
SET source_application_id = l_arr_source_application_id(i)
,source_type_code = l_arr_source_type_code(i)
,source_code = l_arr_source_code(i)
WHERE application_id = p_application_id
AND amb_context_code = p_amb_context_code
AND product_rule_type_code = p_product_rule_type_code
AND product_rule_code = p_product_rule_code
AND event_class_code = p_event_class_code
AND event_type_code = p_event_type_code
AND accounting_attribute_code = l_arr_acct_attribute_code(i)
AND event_class_default_flag = 'Y';
SELECT e.source_application_id, e.source_type_code, e.source_code,
s.name, l.meaning source_type_dsp
FROM xla_evt_class_acct_attrs e, xla_sources_tl s, xla_lookups l
WHERE e.application_id = p_application_id
AND e.event_class_code = p_event_class_code
AND e.accounting_attribute_code = p_accounting_attribute_code
AND e.default_flag = 'Y'
AND e.source_application_id = s.application_id (+)
AND e.source_type_code = s.source_type_code (+)
AND e.source_code = s.source_code (+)
AND s.language (+) = USERENV('LANG')
AND e.source_type_code = l.lookup_code (+)
AND l.lookup_type (+) = 'XLA_SOURCE_TYPE';
SELECT xpa.entity_code, xpa.event_class_code, xpa.event_type_code,
xpa.product_rule_type_code, xpa.product_rule_code,
xpa.amb_context_code, xpa.locking_status_flag
FROM xla_prod_acct_headers xpa
WHERE xpa.application_id = p_application_id
AND (xpa.validation_status_code NOT IN ('E', 'Y', 'N') OR
xpa.locking_status_flag = 'Y');
UPDATE xla_line_definitions_b xld
SET validation_status_code = 'N'
WHERE xld.application_id = p_application_id
AND xld.validation_status_code <> 'N';
UPDATE xla_prod_acct_headers xpa
SET validation_status_code = 'N'
WHERE xpa.application_id = p_application_id
AND xpa.validation_status_code <> 'N';
UPDATE xla_product_rules_b xpr
SET compile_status_code = 'N'
WHERE xpr.application_id = p_application_id
AND xpr.compile_status_code <> 'N';
g_last_update_date := sysdate;
g_last_update_login := xla_environment_pkg.g_login_id;
g_last_updated_by := xla_environment_pkg.g_usr_id;