[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_clause_rule_type VARCHAR2 (30) := 'CLAUSE_SELECTION';
PROCEDURE update_rule_header (
p_rule_header_rec IN OUT NOCOPY rule_header_rec_type
);
PROCEDURE update_rule (p_rule_rec IN OUT NOCOPY rule_rec_type);
PROCEDURE delete_rule_child_entities (
p_rule_child_entities_rec IN OUT NOCOPY rule_child_entities_rec_type
);
SELECT 'Y'
FROM fnd_lookup_values
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code
AND LANGUAGE = 'US'
AND enabled_flag = 'Y'
AND NVL (end_date_active, SYSDATE) >= SYSDATE;
SELECT 'Y'
INTO l_flag
FROM okc_xprt_rule_hdrs_all
WHERE rule_id = p_rule_id AND org_id = g_org_id;
SELECT rule_type
INTO l_rule_type
FROM okc_xprt_rule_hdrs_all
WHERE rule_id = p_rule_id;
SELECT question_datatype, value_set_name
INTO x_question_datatype, x_value_set_name
FROM okc_xprt_questions_b
WHERE question_id = p_question_id;
SELECT 'Y'
FROM okc_xprt_questions_b
WHERE question_type = 'Q' -- Question Type must be 'Q'
AND NVL (disabled_flag, 'N') = 'N'
-- Disabled questions must not be available in the Rule creation/updataion
AND question_intent = p_intent
AND question_id = p_question_id;
SELECT 'Y', var1.value_set_id, var1.value_set_name,
var1.variable_datatype
INTO x_valid_variable, x_vlaue_set_id, x_value_set_name,
x_variable_datatype
FROM (SELECT variable_intent, variable_code, variable_name,
a.description, xprt_value_set_name value_set_name,
variable_datatype, b.longlist_flag, b.validation_type,
b.flex_value_set_id value_set_id, variable_type,
vartype.meaning var_type_meaning
FROM okc_bus_variables_v a,
fnd_flex_value_sets b,
fnd_lookups vartype
WHERE contract_expert_yn = 'Y'
AND variable_datatype <> 'D'
AND a.xprt_value_set_name = b.flex_value_set_name(+)
AND vartype.lookup_type = 'OKC_ART_VAR_TYPE'
AND vartype.lookup_code = variable_type
UNION
SELECT variable_intent, variable_code, variable_name,
a.description, flex_value_set_name value_set_name,
variable_datatype, b.longlist_flag, b.validation_type,
b.flex_value_set_id value_set_id, variable_type,
vartype.meaning var_type_meaning
FROM okc_bus_variables_v a,
fnd_flex_value_sets b,
fnd_lookups vartype
WHERE a.variable_type = 'U'
AND a.value_set_id = b.flex_value_set_id
AND ( ( b.validation_type IN ('I', 'X', 'F')
AND b.format_type = 'C'
)
OR (a.variable_datatype = 'N'
AND b.validation_type = 'N'
)
)
AND vartype.lookup_type = 'OKC_ART_VAR_TYPE'
AND vartype.lookup_code = variable_type) var1
WHERE var1.variable_code = p_variable_code
AND var1.variable_intent = p_intent;
SELECT 'Y'
FROM okc_articles_all
WHERE org_id = g_org_id
AND standard_yn = 'Y'
AND article_id = p_article_id
AND article_intent = p_intent;
SELECT 'Y'
FROM okc_articles_all art,
okc_article_versions ver,
okc_article_adoptions adp
WHERE art.article_id = ver.article_id
AND art.standard_yn = 'Y'
AND ver.global_yn = 'Y'
AND ver.article_status = 'APPROVED'
AND adp.global_article_version_id = ver.article_version_id
AND adp.adoption_type = 'ADOPTED'
AND adp.adoption_status = 'APPROVED'
AND art.article_id = p_article_id
AND art.article_intent = p_intent
AND adp.local_org_id = g_org_id;
SELECT 'Y'
FROM okc_xprt_questions_b
WHERE question_type = 'Q' -- Question Type must be 'Q'
AND NVL (disabled_flag, 'N') = 'N'
-- Disabled questions must not be available in the Rule creation/updataion
AND question_intent = p_intent
AND question_id = p_question_id
AND question_datatype = 'N';
SELECT 'Y'
FROM okc_xprt_questions_b
WHERE question_type = 'C' -- Constant Type must be 'Q'
-- Disabled questions must not be available in the Rule creation/updataion
AND question_intent = p_intent
AND question_id = p_constant_id;
SELECT 'Y'
FROM okc_bus_variables_v
WHERE contract_expert_yn = 'Y'
AND variable_datatype = 'N'
AND variable_intent = p_intent
AND variable_code = p_variable_code;
SELECT 'Y'
FROM okc_xprt_rule_outcomes
WHERE rule_id = p_rule_id
AND object_type = p_object_type
AND object_value_id = p_object_value_id;
SELECT intent
INTO l_intent
FROM okc_xprt_rule_hdrs_all
WHERE rule_id = p_rule_id;
SELECT flex_value_set_id, validation_type
INTO l_value_set_id, l_validation_type
FROM fnd_flex_value_sets
WHERE flex_value_set_name = p_value_set_name;
SELECT object_type, object_code, rule_condition_id
FROM okc_xprt_rule_conditions
WHERE rule_id = p_rule_id;
SELECT TO_NUMBER (object_value_code) clause_id
FROM okc_xprt_rule_cond_vals
WHERE rule_condition_id = p_rule_condition_id;
SELECT 'X'
FROM hr_operating_units ou, hr_organization_information oi
WHERE mo_global.check_access (ou.organization_id) = 'Y'
AND oi.org_information_context = 'OKC_TERMS_LIBRARY_DETAILS'
AND oi.organization_id = ou.organization_id
AND NVL (date_to, SYSDATE) >= SYSDATE
AND ou.organization_id = p_org_id;
IF p_rule_header_rec.last_updated_by = okc_api.g_miss_num
THEN
p_rule_header_rec.last_updated_by := fnd_global.user_id;
IF p_rule_header_rec.last_update_date = okc_api.g_miss_date
THEN
p_rule_header_rec.last_update_date := SYSDATE;
IF p_rule_header_rec.last_update_login = okc_api.g_miss_num
THEN
p_rule_header_rec.last_update_login := fnd_global.login_id;
p_rule_header_rec.program_update_date := NULL;
p_rule_header_rec.program_update_date := SYSDATE;
SELECT 'Y'
FROM okc_xprt_rule_hdrs_all
WHERE rule_name = p_rule_name AND org_id = p_org_id;
SELECT okc_xprt_rule_hdrs_all_s.NEXTVAL
INTO p_rule_header_rec.rule_id
FROM DUAL;*/
INSERT INTO okc_xprt_rule_hdrs_all
(rule_id,
org_id, intent,
status_code,
rule_name,
rule_description,
org_wide_flag,
published_flag,
condition_expr_code,
request_id,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
program_id,
program_application_id,
program_update_date,
rule_type,
line_level_flag
)
VALUES (okc_xprt_rule_hdrs_all_s.NEXTVAL,
p_rule_header_rec.org_id, p_rule_header_rec.intent,
p_rule_header_rec.status_code,
p_rule_header_rec.rule_name,
p_rule_header_rec.rule_description,
p_rule_header_rec.org_wide_flag,
p_rule_header_rec.published_flag,
p_rule_header_rec.condition_expr_code,
p_rule_header_rec.request_id,
p_rule_header_rec.object_version_number,
p_rule_header_rec.created_by,
p_rule_header_rec.creation_date,
p_rule_header_rec.last_updated_by,
p_rule_header_rec.last_update_date,
p_rule_header_rec.last_update_login,
p_rule_header_rec.program_id,
p_rule_header_rec.program_application_id,
p_rule_header_rec.program_update_date,
p_rule_header_rec.rule_type,
p_rule_header_rec.line_level_flag
)
RETURNING rule_id
INTO p_rule_header_rec.rule_id;
system variable drives the clause selection on a business document.
For Policy Deviation rules, use this type if a system or user-defined variable
drives the policy deviation for a business document.
Question : Use this type if a user question should drive the clause selection or
policy deviation on business documents.
*/
PROCEDURE create_rule_condition (
p_rule_condition_rec IN OUT NOCOPY rule_condition_rec_type
)
IS
l_rule_type VARCHAR2 (30);
SELECT 'Y'
FROM fnd_lookup_values
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code
AND LANGUAGE = 'US'
AND enabled_flag = 'Y'
AND NVL (end_date_active, SYSDATE) >= SYSDATE;
SELECT rule_type, intent
INTO l_rule_type, l_rule_intent
FROM okc_xprt_rule_hdrs_all
WHERE rule_id = p_rule_condition_rec.rule_id;
/*SELECT okc_xprt_rule_condition_s.NEXTVAL
INTO p_rule_condition_rec.rule_condition_id
FROM DUAL; */
INSERT INTO okc_xprt_rule_conditions
(rule_condition_id,
rule_id,
object_type,
object_code,
object_code_datatype,
OPERATOR,
object_value_set_name,
object_value_type,
object_value_code, object_version_number,
created_by, creation_date, last_updated_by,
last_update_date, last_update_login
)
VALUES (okc_xprt_rule_condition_s.NEXTVAL,
p_rule_condition_rec.rule_id,
-- Need to see to pass or derive
p_rule_condition_rec.object_type,
p_rule_condition_rec.object_code,
p_rule_condition_rec.object_code_datatype,
p_rule_condition_rec.OPERATOR,
p_rule_condition_rec.object_value_set_name,
p_rule_condition_rec.object_value_type,
p_rule_condition_rec.object_value_code, 1,
fnd_global.user_id, SYSDATE, fnd_global.user_id,
SYSDATE, fnd_global.login_id
)
RETURNING rule_condition_id
INTO p_rule_condition_rec.rule_condition_id;
INSERT INTO okc_xprt_rule_cond_vals
(rule_condition_value_id,
rule_condition_id,
object_value_code, object_version_number,
created_by, creation_date, last_updated_by,
last_update_date, last_update_login
)
VALUES (okc_xprt_rule_cond_vals_s.NEXTVAL
--p_rule_cond_vals_tbl(i).rule_condition_value_id
,
p_rule_condition_id
--p_rule_cond_vals_tbl(i).rule_condition_id
,
p_rule_cond_vals_tbl (i).object_value_code, 1,
fnd_global.user_id, SYSDATE, fnd_global.user_id,
SYSDATE, fnd_global.login_id
)
RETURNING rule_condition_value_id,
rule_condition_id
INTO p_rule_cond_vals_tbl (i).rule_condition_value_id,
p_rule_cond_vals_tbl (i).rule_condition_id;
INSERT INTO okc_xprt_rule_outcomes
(rule_outcome_id,
rule_id,
object_type,
object_value_id, object_version_number,
created_by, creation_date, last_updated_by,
last_update_date, last_update_login,mandatory_yn, mandatory_rwa
)
VALUES (okc_xprt_rule_outcomes_s.NEXTVAL,
p_rule_outcome_rec.rule_id,
p_rule_outcome_rec.object_type,
p_rule_outcome_rec.object_value_id, 1,
fnd_global.user_id, SYSDATE, fnd_global.user_id,
SYSDATE, fnd_global.login_id,p_rule_outcome_rec.mandatory_yn, p_rule_outcome_rec.mandatory_rwa
)
RETURNING rule_outcome_id
INTO p_rule_outcome_rec.rule_outcome_id;
SELECT 'Y'
FROM okc_terms_templates_all temp, fnd_lookups status
WHERE intent = g_rule_intent
AND status.lookup_code = temp.status_code
AND status.lookup_type = 'OKC_TERMS_TMPL_STATUS'
AND contract_expert_enabled = 'Y'
AND NVL (end_date, SYSDATE) >= SYSDATE
AND org_id = g_org_id;
SELECT 'Y'
FROM okc_xprt_template_rules
WHERE template_id = p_template_rules_rec.template_id
AND rule_id = g_rule_id
AND deleted_flag = NVL (p_template_rules_rec.deleted_flag, 'N');
/*SELECT okc_xprt_template_rules_s.NEXTVAL
INTO p_template_rules_rec.template_rule_id
FROM DUAL;*/
p_template_rules_rec.deleted_flag := 'N';
p_template_rules_rec.last_updated_by := fnd_global.user_id;
p_template_rules_rec.last_update_date := SYSDATE;
p_template_rules_rec.last_update_login := fnd_global.login_id;
INSERT INTO okc_xprt_template_rules
(template_rule_id,
template_id,
rule_id,
deleted_flag,
published_flag,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES (okc_xprt_template_rules_s.NEXTVAL,
p_template_rules_rec.template_id,
p_template_rules_rec.rule_id,
p_template_rules_rec.deleted_flag,
p_template_rules_rec.published_flag,
p_template_rules_rec.object_version_number,
p_template_rules_rec.created_by,
p_template_rules_rec.creation_date,
p_template_rules_rec.last_updated_by,
p_template_rules_rec.last_update_date,
p_template_rules_rec.last_update_login
)
RETURNING template_rule_id
INTO p_template_rules_rec.template_rule_id;
The system allows updates to the conditions, results, and template assignments.
However, you cannot update the following fields:
Operating Unit
Rule Type => You cannot change the intent on a rule that has conditions, results or template assignments defined
Name =>
Intent => You cannot change the intent on a rule that has conditions, results or template assignments defined.
Apply to All Templates
When Status is Draft you can not update the following:
OU
Rule Type
Intent
-- Manual status changes are not allowed
*/
PROCEDURE update_rule_header (
p_rule_header_rec IN OUT NOCOPY rule_header_rec_type
)
IS
l_progress VARCHAR2 (3) := '000';
l_proc VARCHAR2 (60) := 'UPDATE_RULE_HEADER';
p_rule_header_rec.last_updated_by := fnd_global.user_id;
p_rule_header_rec.last_update_date := SYSDATE;
p_rule_header_rec.last_update_login := fnd_global.login_id;
SELECT 'Y'
FROM okc_xprt_rule_hdrs_all
WHERE rule_name = p_rule_name AND org_id = p_org_id;
PROCEDURE update_row (
p_rule_header_rec IN OUT NOCOPY rule_header_rec_type
)
IS
l_proc VARCHAR2 (60) := 'UPDATE_ROW';
UPDATE okc_xprt_rule_hdrs_all
SET status_code = p_rule_header_rec.status_code,
rule_name = p_rule_header_rec.rule_name,
rule_description = p_rule_header_rec.rule_description,
org_wide_flag = p_rule_header_rec.org_wide_flag,
condition_expr_code = p_rule_header_rec.condition_expr_code,
object_version_number = object_version_number + 1,
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE,
last_update_login = fnd_global.login_id
WHERE rule_id = p_rule_header_rec.rule_id;
END update_row;
SELECT *
INTO l_rule_header_row
FROM okc_xprt_rule_hdrs_all
WHERE rule_id = p_rule_header_rec.rule_id;
The system allows updates to the conditions, results, and template assignments.
However, you cannot update the following fields:
Operating Unit => Covered in above code
Rule Type => Covered in above code
You cannot change the intent on a rule that has conditions, results or template assignments defined
Name =>
Intent => Covered in above code
You cannot change the intent on a rule that has conditions, results or template assignments defined.
Apply to All Templates =>
*/
l_progress := '075';
DELETE FROM okc_xprt_template_rules
WHERE rule_id = p_rule_header_rec.rule_id;
update_row (p_rule_header_rec => p_rule_header_rec);
END update_rule_header;
PROCEDURE update_rule_header (
p_rule_header_tbl IN OUT NOCOPY rule_header_tbl_type
)
IS
l_proc VARCHAR2 (60) := 'UPDATE_RULE_HEADER';
update_rule_header (p_rule_header_rec => p_rule_header_tbl (i));
END update_rule_header;
PROCEDURE update_rule (p_rule_rec IN OUT NOCOPY rule_rec_type)
IS
l_rule_type okc_xprt_rule_hdrs_all.rule_type%TYPE;
update_rule_header (p_rule_header_rec => p_rule_rec.rule_header_rec);
END update_rule;
PROCEDURE delete_rule_child_entities (
p_rule_child_entities_rec IN OUT NOCOPY rule_child_entities_rec_type
)
IS
l_rule_type VARCHAR2 (60);
SELECT status_code, rule_type
INTO g_rule_status_code, g_rule_type
FROM okc_xprt_rule_hdrs_all
WHERE rule_id = p_rule_child_entities_rec.rule_id;
SELECT 'Y'
INTO l_flag
FROM okc_xprt_rule_conditions
WHERE rule_id = p_rule_child_entities_rec.rule_id
AND rule_condition_id =
p_rule_child_entities_rec.rule_condition_id_tbl (i)
AND ROWNUM = 1;
DELETE FROM okc_xprt_rule_cond_vals
WHERE rule_condition_id =
p_rule_child_entities_rec.rule_condition_id_tbl (i);
DELETE FROM okc_xprt_rule_conditions
WHERE rule_condition_id =
p_rule_child_entities_rec.rule_condition_id_tbl (i);
SELECT COUNT (1)
INTO l_conditions_count
FROM okc_xprt_rule_conditions
WHERE rule_id = p_rule_child_entities_rec.rule_id;
SELECT 'Y'
INTO l_flag
FROM okc_xprt_rule_outcomes
WHERE rule_id = p_rule_child_entities_rec.rule_id
AND rule_outcome_id =
p_rule_child_entities_rec.rule_outcome_id_tbl (i)
AND ROWNUM = 1;
DELETE FROM okc_xprt_rule_outcomes
WHERE rule_outcome_id =
p_rule_child_entities_rec.rule_outcome_id_tbl (i);
SELECT COUNT (1)
INTO l_outcomes_count
FROM okc_xprt_rule_outcomes
WHERE rule_id = p_rule_child_entities_rec.rule_id;
SELECT 'Y'
INTO l_flag
FROM okc_xprt_template_rules
WHERE rule_id = p_rule_child_entities_rec.rule_id
AND template_rule_id =
p_rule_child_entities_rec.template_rule_id_tbl (i)
AND ROWNUM = 1;
DELETE FROM okc_xprt_template_rules
WHERE 1 = 1
AND template_rule_id =
p_rule_child_entities_rec.template_rule_id_tbl (i);
UPDATE okc_xprt_rule_hdrs_all
SET status_code = 'REVISION'
WHERE rule_id = p_rule_child_entities_rec.rule_id;
END delete_rule_child_entities;
PROCEDURE update_rule (
p_rule_tbl IN OUT NOCOPY rule_tbl_type,
p_commit IN VARCHAR2 := fnd_api.g_false
)
IS
l_success_count NUMBER := 0;
l_proc VARCHAR2 (60) := 'UPDATE_RULE';
SAVEPOINT update_rule_sp;
update_rule (p_rule_rec => p_rule_tbl (i));
ROLLBACK TO update_rule_sp;
ROLLBACK TO update_rule_sp;
ROLLBACK TO update_rule_sp;
ROLLBACK TO update_rule_sp;
END update_rule;
PROCEDURE delete_rule_child_entities (
p_rule_child_entities_tbl IN OUT NOCOPY rule_child_entities_tbl_type,
p_commit IN VARCHAR2 := fnd_api.g_false
)
IS
l_success_count NUMBER := 0;
l_proc VARCHAR2 (60) := 'DELETE_RULE_CHILD_ENTITIES';
delete_rule_child_entities
(p_rule_child_entities_rec => p_rule_child_entities_tbl
(i)
);
END delete_rule_child_entities;