[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FUNCTION insert_rule (
p_rule_name VARCHAR2
, p_description VARCHAR2
, p_base_rule_id NUMBER
, p_appl_id NUMBER
, p_resp_id NUMBER
, p_user_id NUMBER
, p_terr_id NUMBER
, p_resource_type VARCHAR2
, p_resource_id NUMBER
, p_rule_rank NUMBER
, p_enabled_flag VARCHAR2
, p_rule_doc XMLTYPE
)
RETURN NUMBER IS
l_new_rule_id NUMBER;
SELECT meaning FROM fnd_lookups
WHERE lookup_type = 'JTF_NOTE_TYPE' AND lookup_code = 'CN_SYSGEN';
INSERT INTO csr_rules_b (
rule_id
, object_version_number
, base_rule_id
, appl_id
, resp_id
, user_id
, terr_id
, resource_type
, resource_id
, enabled_flag
, rule_rank
, rule_doc
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
)
VALUES(
csr_rules_b_s.NEXTVAL
, 1
, NVL(p_base_rule_id, -1)
, NVL(p_appl_id, g_not_specified)
, NVL(p_resp_id, g_not_specified)
, NVL(p_user_id, g_not_specified)
, NVL(p_terr_id, g_not_specified)
, NVL(p_resource_type, '-')
, NVL(p_resource_id, g_not_specified)
, p_enabled_flag
, p_rule_rank
, p_rule_doc
, fnd_global.user_id
, SYSDATE
, fnd_global.user_id
, SYSDATE
, fnd_global.login_id
)
RETURNING rule_id INTO l_new_rule_id;
INSERT INTO csr_rules_tl (
rule_id
, language
, source_lang
, rule_name
, description
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
)
SELECT l_new_rule_id
, l.language_code
, userenv('LANG')
, l_rule_name
, p_description
, fnd_global.user_id
, SYSDATE
, fnd_global.user_id
, SYSDATE
, fnd_global.login_id
FROM fnd_languages l
WHERE l.installed_flag in ('I','B')
AND NOT EXISTS (
SELECT 1 FROM csr_rules_tl tl
WHERE tl.rule_id = l_new_rule_id
AND tl.language = l.language_code
);
END insert_rule;
SELECT DISTINCT t.terr_id, LEVEL terr_level, r.rule_id
FROM jtf_terr_all t, csr_rules_b r
WHERE t.terr_id = r.terr_id (+)
AND NVL(r.rule_rank, 16) = 16
START WITH t.terr_id = p_terr_id
CONNECT BY NOCYCLE PRIOR t.parent_territory_id = t.terr_id
ORDER BY terr_level DESC;
insert_rule(
p_rule_name => NULL
, p_description => NULL
, p_base_rule_id => l_parent_terr_rule_id
, p_appl_id => NULL
, p_resp_id => NULL
, p_user_id => NULL
, p_terr_id => v_terr.terr_id
, p_resource_type => NULL
, p_resource_id => NULL
, p_rule_rank => 16
, p_enabled_flag => 'S'
, p_rule_doc => p_rule_doc
);
insert_rule(
p_rule_name => NULL
, p_description => NULL
, p_base_rule_id => l_base_rule_id
, p_appl_id => l_appl_id
, p_resp_id => l_resp_id
, p_user_id => l_user_id
, p_terr_id => l_terr_id
, p_resource_type => l_resource_type
, p_resource_id => l_resource_id
, p_rule_rank => l_rule_rank
, p_enabled_flag => 'S'
, p_rule_doc => p_rule_doc
);
SELECT rule_id, rule_name
FROM csr_rules_vl
WHERE rule_id <> NVL(p_rule_id, g_not_specified)
AND appl_id = NVL(p_appl_id, g_not_specified)
AND resp_id = NVL(p_resp_id, g_not_specified)
AND user_id = NVL(p_user_id, g_not_specified)
AND terr_id = NVL(p_terr_id, g_not_specified)
AND resource_type = NVL(p_resource_type, '-')
AND resource_id = NVL(p_resource_id, g_not_specified)
AND ROWNUM = 1;
l_base_rule_query := 'SELECT rule_id, rule_name FROM csr_rules_vl WHERE ';
SELECT csr_rule_windows_tl_s.NEXTVAL INTO l_window_id FROM dual;
INSERT INTO csr_rule_windows_tl (
window_id
, language
, source_lang
, window_name
, description
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
)
SELECT l_window_id
, l.language_code
, userenv('LANG')
, p_window_names(i)
, p_window_descriptions(i)
, fnd_global.user_id
, SYSDATE
, fnd_global.user_id
, SYSDATE
, fnd_global.login_id
FROM fnd_languages l
WHERE l.installed_flag in ('I','B');
UPDATE csr_rule_windows_tl
SET window_name = p_window_names(i)
, description = p_window_descriptions(i)
, last_updated_by = fnd_global.user_id
, last_update_date = SYSDATE
, last_update_login = fnd_global.login_id
WHERE window_id = l_window_id
AND userenv('LANG') IN (language, source_lang);
DELETE csr_rule_windows_tl WHERE window_id = l_window_id;
UPDATE csr_rules_b
SET rule_doc = p_child_doc
WHERE rule_id = p_child_rule_id;
* from an Updated Scheduler Rule to all its Children.
*
* The method exploits Depth First Search Traversal (DFS) approach to
* explore all the Child Rules which can be updated based on the changes
* done in the Base Rule.
* A XML Element in the Child Rule is modified if it has been inherited
* from the Base Rule and still the inheritance exists. But if the argument
* p_force is 'Y' then the Child Rule is updated irrespective of inheritance.
*/
PROCEDURE propagate_to_child_rules(
p_rule_id NUMBER
, p_base_doc XMLType
, p_child_doc XMLTYPE
, p_force VARCHAR2
) IS
CURSOR c_child_rules IS
SELECT rule_id, rule_doc
FROM csr_rules_b
WHERE base_rule_id = p_rule_id;
insert_rule(
p_rule_name => p_rule_name
, p_description => p_description
, p_base_rule_id => l_base_rule_id
, p_appl_id => p_appl_id
, p_resp_id => p_resp_id
, p_user_id => p_user_id
, p_terr_id => p_terr_id
, p_resource_type => p_resource_type
, p_resource_id => p_resource_id
, p_rule_rank => l_rule_rank
, p_enabled_flag => p_enabled_flag
, p_rule_doc => l_rule_doc
);
PROCEDURE update_rule(
p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2 DEFAULT NULL
, p_commit IN VARCHAR2 DEFAULT NULL
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_data OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, p_rule_id IN NUMBER
, p_object_version_number IN OUT NOCOPY NUMBER
, p_rule_name IN VARCHAR2 DEFAULT NULL
, p_description IN VARCHAR2 DEFAULT NULL
, p_base_rule_id IN NUMBER DEFAULT NULL
, p_appl_id IN NUMBER DEFAULT NULL
, p_resp_id IN NUMBER DEFAULT NULL
, p_user_id IN NUMBER DEFAULT NULL
, p_terr_id IN NUMBER DEFAULT NULL
, p_resource_type IN VARCHAR2 DEFAULT NULL
, p_resource_id IN NUMBER DEFAULT NULL
, p_enabled_flag IN VARCHAR2 DEFAULT NULL
, p_rule_doc IN XMLTYPE DEFAULT NULL
, p_window_names IN jtf_varchar2_table_300 DEFAULT NULL
, p_window_descriptions IN jtf_varchar2_table_1500 DEFAULT NULL
, p_version_msgs IN jtf_varchar2_table_4000
, p_force_propagation IN VARCHAR2 DEFAULT NULL
, x_new_rule_doc OUT NOCOPY CLOB
) IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RULE';
SELECT b.rule_id
, b.object_version_number
, b.base_rule_id
, b.appl_id
, b.resp_id
, b.user_id
, b.terr_id
, b.resource_type
, b.resource_id
, b.enabled_flag
, b.rule_rank
, b.rule_doc
, t.rule_name
, t.description
FROM csr_rules_b b, csr_rules_tl t
WHERE b.rule_id = p_rule_id
AND b.rule_id = t.rule_id
AND t.language = userenv('LANG')
FOR UPDATE NOWAIT;
SAVEPOINT csr_rule_update;
UPDATE csr_rules_b
SET object_version_number = p_object_version_number
, base_rule_id = NVL(l_rule.base_rule_id, -1)
, appl_id = NVL(l_rule.appl_id, g_not_specified)
, resp_id = NVL(l_rule.resp_id, g_not_specified)
, user_id = NVL(l_rule.user_id, g_not_specified)
, terr_id = NVL(l_rule.terr_id, g_not_specified)
, resource_type = NVL(l_rule.resource_type, '-')
, resource_id = NVL(l_rule.resource_id, g_not_specified)
, enabled_flag = l_rule.enabled_flag
, rule_rank = l_rule.rule_rank
, rule_doc = l_rule.rule_doc
, last_updated_by = fnd_global.user_id
, last_update_date = SYSDATE
, last_update_login = fnd_global.login_id
WHERE rule_id = p_rule_id;
UPDATE csr_rules_tl
SET rule_name = NVL(p_rule_name, rule_name)
, description = NVL(p_description, description)
, last_updated_by = fnd_global.user_id
, last_update_date = SYSDATE
, last_update_login = fnd_global.login_id
WHERE rule_id = p_rule_id
AND userenv('LANG') IN (language, source_lang);
ROLLBACK TO csr_rule_update;
debug('Update Rule Errored with ' || x_msg_data, l_api_name, fnd_log.level_error);
ROLLBACK TO csr_rule_update;
debug('Update Rule Errored with ' || x_msg_data, l_api_name, fnd_log.level_error);
ROLLBACK TO csr_rule_update;
debug('Update Rule Errored with ' || x_msg_data, l_api_name, fnd_log.level_error);
END update_rule;
PROCEDURE delete_rule(
p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2 DEFAULT NULL
, p_commit IN VARCHAR2 DEFAULT NULL
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_data OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, p_rule_id IN NUMBER
) IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RULE';
SELECT rule_id, base_rule_id, LEVEL
FROM csr_rules_b
START WITH rule_id = p_rule_id
CONNECT BY base_rule_id = PRIOR rule_id;
SELECT n.jtf_note_id
FROM jtf_notes_b n
WHERE n.source_object_code = 'CSR_RULES'
AND n.source_object_id = v_rule_id;
SAVEPOINT csr_rule_delete;
jtf_notes_pub.secure_delete_note(
p_api_version => 1.0
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_jtf_note_id => v_note.jtf_note_id
, p_use_AOL_security => fnd_api.g_false
);
DELETE csr_rules_tl WHERE rule_id = v_child_rule.rule_id;
DELETE csr_rules_b WHERE rule_id = v_child_rule.rule_id;
ROLLBACK TO csr_rule_delete;
ROLLBACK TO csr_rule_delete;
ROLLBACK TO csr_rule_delete;
END delete_rule;
SELECT extractValue(r.rule_doc, v_param_path)
FROM csr_rules_b r
WHERE r.appl_id IN (p_appl_id, g_not_specified)
AND r.resp_id IN (p_resp_id, g_not_specified)
AND r.user_id IN (p_user_id, g_not_specified)
AND r.terr_id = g_not_specified
AND r.resource_type IN (p_resource_type, '-')
AND r.resource_id IN (p_resource_id, g_not_specified)
AND r.enabled_flag = 'Y'
AND existsNode(r.rule_doc, v_param_path) = 1
ORDER BY rule_rank DESC;
SELECT extractValue(r.rule_doc, v_param_path)
FROM csr_rules_b r
, (
SELECT terr_id, LEVEL terr_level
FROM jtf_terr_all
START WITH terr_id = p_terr_id
CONNECT BY NOCYCLE PRIOR parent_territory_id = terr_id
) t
WHERE r.appl_id IN (p_appl_id, g_not_specified)
AND r.resp_id IN (p_resp_id, g_not_specified)
AND r.user_id IN (p_user_id, g_not_specified)
AND r.terr_id IN (t.terr_id, g_not_specified)
AND r.resource_type IN (p_resource_type, '-')
AND r.resource_id IN (p_resource_id, g_not_specified)
AND r.enabled_flag = 'Y'
AND existsNode(r.rule_doc, v_param_path) = 1
ORDER BY rule_rank DESC;
'SELECT r.*
FROM csr_rules_vl r
WHERE r.appl_id IN (:appl_id, -9999)
AND r.resp_id IN (:resp_id, -9999)
AND r.user_id IN (:user_id, -9999)
AND r.terr_id = -9999
AND r.resource_type = ''-''
AND r.resource_id = -9999
AND r.enabled_flag = ''Y''
ORDER BY r.rule_rank DESC';
'SELECT r.*
FROM csr_rules_vl r
WHERE r.appl_id IN (:appl_id, -9999)
AND r.resp_id IN (:resp_id, -9999)
AND r.user_id IN (:user_id, -9999)
AND r.terr_id = -9999
AND r.resource_type IN (:res_type, ''-'')
AND r.resource_id IN (:res_id, -9999)
AND r.enabled_flag = ''Y''
ORDER BY r.rule_rank DESC';
'SELECT r.*
FROM csr_rules_vl r
, (SELECT terr_id, LEVEL terr_level FROM jtf_terr_all
START WITH terr_id = :terr_id
CONNECT BY NOCYCLE PRIOR parent_territory_id = terr_id
UNION ALL SELECT -9999, 9999999 FROM DUAL
) t
WHERE r.appl_id IN (:appl_id, -9999)
AND r.resp_id IN (:resp_id, -9999)
AND r.user_id IN (:user_id, -9999)
AND r.terr_id = t.terr_id
AND r.resource_type IN (:res_type, ''-'')
AND r.resource_id IN (:res_id, -9999)
AND r.enabled_flag = ''Y''
ORDER BY r.rule_rank DESC, t.terr_level ASC';
l_rule_tbl.DELETE;
SELECT b.rule_doc, b.rule_rank, t.rule_name, t.description
FROM csr_rules_b b, csr_rules_tl t
WHERE b.rule_id = p_rule_id
FOR UPDATE OF b.rule_doc, t.rule_name NOWAIT;
IF p_action = 'UPDATE' THEN
OPEN c_rule_details;
update_rule(
p_api_version => 1.0
, p_init_msg_list => fnd_api.g_true
, p_commit => fnd_api.g_true
, x_return_status => l_return_status
, x_msg_data => l_msg_data
, x_msg_count => l_msg_count
, p_rule_id => p_rule_id
, p_object_version_number => l_object_version_num
, p_rule_name => p_rule_name
, p_description => p_description
, p_enabled_flag => p_enabled_flag
, p_rule_doc => l_rule.rule_doc
, p_version_msgs => NULL
, x_new_rule_doc => l_new_rule_doc
);
DELETE FROM csr_rules_tl t
WHERE NOT EXISTS (SELECT NULL FROM csr_rules_b b WHERE b.rule_id = t.rule_id);
UPDATE csr_rules_tl csrt
SET (csrt.rule_name, csrt.description) = (
SELECT csrtl.rule_name, csrtl.description
FROM csr_rules_tl csrtl
WHERE csrtl.rule_id = csrt.rule_id
AND csrtl.language = csrt.source_lang
)
WHERE (csrt.rule_id, csrt.language) IN (
SELECT subt.rule_id, subt.language
FROM csr_rules_tl subb, csr_rules_tl subt
WHERE subb.rule_id = subt.rule_id
AND subb.language = subt.source_lang
AND (
subb.rule_name <> subt.rule_name
OR subb.description <> subt.description
OR (subb.description IS NULL AND subt.description IS NOT NULL)
OR (subb.description IS NOT NULL AND subt.description IS NULL)
)
);
INSERT INTO csr_rules_tl (
rule_id
, rule_name
, description
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
, language
, source_lang
)
SELECT csrt.rule_id
, csrt.rule_name
, csrt.description
, csrt.created_by
, csrt.creation_date
, csrt.last_updated_by
, csrt.last_update_date
, csrt.last_update_login
, l.language_code
, csrt.source_lang
FROM csr_rules_tl csrt
, fnd_languages l
WHERE l.installed_flag IN ('I', 'B')
AND csrt.language = userenv('LANG')
AND NOT EXISTS (
SELECT NULL
FROM csr_rules_tl t
WHERE t.rule_id = csrt.rule_id
AND t.language = l.language_code
);
DELETE FROM CSR_RULE_WINDOWS_TL t
WHERE NOT EXISTS (SELECT NULL FROM CSR_RULE_WINDOWS_TL b WHERE b.window_id = t.window_id);
UPDATE CSR_RULE_WINDOWS_TL csrt
SET (csrt.window_name, csrt.description) = (
SELECT csrtl.window_name, csrtl.description
FROM CSR_RULE_WINDOWS_TL csrtl
WHERE csrtl.window_id = csrt.window_id
AND csrtl.language = csrt.source_lang
)
WHERE (csrt.window_id, csrt.language) IN (
SELECT subt.window_id, subt.language
FROM CSR_RULE_WINDOWS_TL subb, CSR_RULE_WINDOWS_TL subt
WHERE subb.window_id = subt.window_id
AND subb.language = subt.source_lang
AND (
subb.window_name <> subt.window_name
OR subb.description <> subt.description
OR (subb.description IS NULL AND subt.description IS NOT NULL)
OR (subb.description IS NOT NULL AND subt.description IS NULL)
)
);
INSERT INTO CSR_RULE_WINDOWS_TL (
window_id
, window_name
, description
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
, language
, source_lang
)
SELECT csrt.window_id
, csrt.window_name
, csrt.description
, csrt.created_by
, csrt.creation_date
, csrt.last_updated_by
, csrt.last_update_date
, csrt.last_update_login
, l.language_code
, csrt.source_lang
FROM CSR_RULE_WINDOWS_TL csrt
, fnd_languages l
WHERE l.installed_flag IN ('I', 'B')
AND csrt.language = userenv('LANG')
AND NOT EXISTS (
SELECT NULL
FROM CSR_RULE_WINDOWS_TL t
WHERE t.window_id = csrt.window_id
AND t.language = l.language_code
);