[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT logtime,
caller,
message
FROM cz_db_logs
WHERE run_id = p_run_id
ORDER BY logtime;
SELECT imp_table,
disposition,
rec_status,
records
FROM cz_xfr_run_results
WHERE run_id = p_run_id ;
SELECT name,
substr(message,1,200) msg
FROM cz_imp_rules
WHERE rec_status <> 'OK'
AND run_id = p_run_id ;
-- for rule import this entity is not updated to OK
-- Need to roll back the changes to allow PASS status (Bug logged against CZ)
IF csr_xfr_run_results_rec.rec_status NOT IN ('OK','PASS') THEN
x_return_status := G_RET_STS_ERROR;
SELECT NVL(v.line_level_flag,'N') line_level_flag
FROM okc_xprt_rule_hdrs_all rhdr,
okc_xprt_rule_conditions rcon,
okc_bus_variables_b v
WHERE rhdr.rule_id = rcon.rule_id
AND rcon.object_type = 'VARIABLE'
AND rcon.object_code = v.variable_code
AND SUBSTR(rcon.object_code,1,3) = 'OKC'
AND v.line_level_flag = 'Y'
AND rhdr.rule_id = p_rule_id
UNION
SELECT NVL(v.line_level_flag,'N') line_level_flag
FROM okc_xprt_rule_hdrs_all rhdr,
okc_xprt_rule_conditions rcon,
okc_bus_variables_b v
WHERE rhdr.rule_id = rcon.rule_id
AND rcon.object_value_type = 'VARIABLE'
AND rcon.object_code = v.variable_code
AND SUBSTR(rcon.object_code,1,3) = 'OKC'
AND v.line_level_flag = 'Y'
AND rhdr.rule_id = p_rule_id;
SELECT flex_value_set_id
FROM fnd_flex_value_sets
WHERE flex_value_set_name = p_value_set_name;
SELECT 'X'
FROM okc_xprt_template_rules r,
okc_xprt_rule_hdrs_all h
WHERE r.rule_id = h.rule_id
AND h.status_code = 'ACTIVE'
AND template_id = p_template_id ;
SELECT 'X'
FROM okc_terms_templates_all t,
okc_xprt_rule_hdrs_all h
WHERE t.org_id = h.org_id
AND t.intent = h.intent
AND h.status_code = 'ACTIVE'
AND NVL(h.org_wide_flag,'N') = 'Y'
AND t.template_id = p_template_id ;
SELECT NVL(contract_expert_enabled,'N')
FROM okc_terms_templates_all
WHERE template_id = p_template_id;
This API will delete all the test publications for a model
*/
PROCEDURE delete_test_publications
(
p_devl_project_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER
) IS
CURSOR csr_get_publication_dtl IS
SELECT publication_id
FROM cz_model_publications
WHERE model_id = p_devl_project_id
AND deleted_flag = '0'
AND publication_mode ='t'
AND source_target_flag = 'S'; -- only delete 'S' and never 'T'
l_api_name CONSTANT VARCHAR2(30) := 'delete_test_publications';
OKC_XPRT_CZ_INT_PVT.delete_publication
(
p_api_version => l_api_version,
p_init_msg_lst => FND_API.G_FALSE,
p_publication_id => rec_get_publication_dtl.publication_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
END delete_test_publications;
edit test to production 1 p S ( record 1 updated )
edit test to production 2 p T ( record 2 updated )
Don't touch the source_target_flag with 'T'. Only access source_target_flag='S'
If you delete source_target_flag='S', then the source_target_flag='T' will also be deleted.
If you delete a publication , then the purge program auto deletes UIs associated with
publication. So no need to explictly delete UI
OCD shows only source_target_flag='S' as the target record is never shown to user
*/
/*
Procedure : create_test_publication
This procedure does the following
For All templates in the current concurrent request id
Delete Test Publications
Delete Existing UIs except the UI attached to production publication
Create New UI
Generate Logic for the template Model
Create test publication for the Template Model
Publish the test publication
*/
PROCEDURE create_test_publication
(
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER
) IS
CURSOR csr_templates IS
SELECT DECODE(parent_template_id, NULL, template_id, parent_template_id),
template_model_id
FROM okc_terms_templates_all
WHERE xprt_request_id = FND_GLOBAL.CONC_REQUEST_ID ;
SELECT ui_def_id
FROM cz_ui_defs
WHERE devl_project_id = p_model_id
AND deleted_flag = '0'
AND ui_def_id NOT IN ( SELECT ui_def_id
FROM cz_model_publications
WHERE model_id = p_model_id
AND publication_mode = G_PRODUCTION_MODE
AND deleted_flag = '0'
AND source_target_flag = 'S'
);
-- Delete Test Publications
delete_test_publications
(
p_devl_project_id => l_template_model_id,
x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count
);
-- Delete Existing UIs except the UI attached to production publication
OPEN csr_template_ui(p_model_id => l_template_model_id);
-- Call CZ delete UI API
OKC_XPRT_CZ_INT_PVT.delete_ui_def
(
p_api_version => l_api_version,
p_ui_def_id => l_ui_def_id,
x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count
) ;
select org_id, intent from okc_terms_templates_all where template_id = p_Template_Id;
Delete questions from the table OKC_XPRT_QUESTION_ORDERS for the template where the questions do not exist in
the left and right hand side of OKC_XPRT_RULE_CONDITIONS or in OKC_XPRT_RULE_OUTCOMES
for all rules associated to the template in table OKC_XPRT_TEMPLATE_RULES or rules
with ORG_WIDE_FLAG='Y' and status in ('ACTIVE', 'DRAFT', 'REVISION', 'PENDINGPUB')
*/
-- write to debug log file
IF (Fnd_Log.Level_Procedure >= Fnd_Log.g_Current_Runtime_Level) THEN
Fnd_Log.STRING(Fnd_Log.Level_Procedure,
g_Module || l_Api_Name,
'110: Deleting invalid questions from the table Okc_Xprt_Question_Orders');
DELETE FROM Okc_Xprt_Question_Orders
WHERE Template_Id = p_Template_Id
AND NOT EXISTS
(SELECT 1
FROM Okc_Xprt_Rule_Conditions Cond,
okc_xprt_rule_hdrs_all Rules,
Okc_Xprt_Template_Rules Assoc,
Okc_xprt_questions_b quest
WHERE Rules.Rule_Id = Cond.Rule_Id
AND Rules.Rule_Id = Assoc.Rule_Id
AND Assoc.Template_Id = p_Template_Id
AND ( ( to_char(Okc_Xprt_Question_Orders.Question_Id) = Cond.Object_Code
AND Cond.Object_Type = 'QUESTION') OR
(to_char(Okc_Xprt_Question_Orders.Question_Id) = Cond.Object_Value_Code
AND Cond.Object_Value_Type = 'QUESTION')
)
AND quest.question_id = Okc_Xprt_Question_Orders.Question_Id
AND Rules.Status_Code IN ('ACTIVE', 'DRAFT', 'REVISION', 'PENDINGDISABLE','PENDINGPUB')
UNION ALL --perf Bug#5030444 Replaced UNION with UNION ALL
SELECT 1
FROM Okc_Xprt_Rule_Outcomes Outs,
okc_xprt_rule_hdrs_all Rules,
Okc_Xprt_Template_Rules Assoc,
Okc_xprt_questions_b quest
WHERE Rules.Rule_Id = Assoc.Rule_Id
AND Rules.rule_id = Outs.rule_id
AND Assoc.Template_Id = p_Template_Id
AND to_char(Okc_Xprt_Question_Orders.Question_Id) =
Outs.Object_Value_Id
AND Outs.Object_Type = 'QUESTION'
AND quest.question_id = Okc_Xprt_Question_Orders.Question_Id
AND Rules.Status_Code IN ('ACTIVE', 'DRAFT', 'REVISION', 'PENDINGDISABLE','PENDINGPUB')
UNION ALL --perf Bug#5030444 Replaced UNION with UNION ALL
SELECT 1
FROM Okc_Xprt_Rule_Conditions Cond, okc_xprt_rule_hdrs_all Rules,
Okc_xprt_questions_b quest
WHERE Rules.Rule_Id = Cond.Rule_Id
AND Rules.Org_Wide_Flag = 'Y'
AND Rules.Org_Id = l_Org_Id
AND Rules.Intent = l_Intent
AND ( (to_char(Okc_Xprt_Question_Orders.Question_Id) = Cond.Object_Code
AND Cond.Object_Type = 'QUESTION') OR
(to_char(Okc_Xprt_Question_Orders.Question_Id) = Cond.Object_Value_Code
AND Cond.Object_Value_Type = 'QUESTION')
)
AND quest.question_id = Okc_Xprt_Question_Orders.Question_Id
AND Rules.Status_Code IN ('ACTIVE', 'DRAFT', 'REVISION', 'PENDINGDISABLE','PENDINGPUB')
UNION ALL --perf Bug#5030444 Replaced UNION with UNION ALL
SELECT 1
FROM Okc_Xprt_Rule_Outcomes Outs, okc_xprt_rule_hdrs_all Rules,
Okc_xprt_questions_b quest
WHERE Rules.Rule_Id = Outs.Rule_Id
AND Rules.Org_Wide_Flag = 'Y'
AND Rules.Org_Id = l_Org_Id
AND Rules.Intent = l_Intent
AND to_char(Okc_Xprt_Question_Orders.Question_Id) =
Outs.Object_Value_Id
AND Outs.Object_Type = 'QUESTION'
AND quest.question_id = Okc_Xprt_Question_Orders.Question_Id
AND Rules.Status_Code IN ('ACTIVE', 'DRAFT', 'REVISION', 'PENDINGDISABLE','PENDINGPUB')
);
'120: Deleted invalid questions from the table Okc_Xprt_Question_Orders');
UPDATE Okc_Xprt_Question_Orders
SET Question_Rule_Status = 'DRAFT',
Last_Updated_By = l_user_id,
Last_Update_Date = SYSDATE,
Last_Update_Login = l_login_id,
runtime_available_flag = 'N'
WHERE Template_Id = p_Template_Id;
2. Open cursor to select questions for the template
FROM
the left and right hand side of OKC_XPRT_RULE_CONDITIONS or in OKC_XPRT_RULE_OUTCOMES
for all rules associated to the template in table OKC_XPRT_TEMPLATE_RULES or rules with ORG_WIDE_FLAG='Y' and status = 'ACTIVE'
*/
IF (Fnd_Log.Level_Procedure >= Fnd_Log.g_Current_Runtime_Level) THEN
Fnd_Log.STRING(Fnd_Log.Level_Procedure,
g_Module || l_Api_Name,
'140: Start inserting/updating the Active rule questions.');
SELECT * FROM (
SELECT distinct To_Number(Cond.Object_Code) Question_Id, Rules.Status_Code
FROM Okc_Xprt_Rule_Conditions Cond,
okc_xprt_rule_hdrs_all Rules,
Okc_Xprt_Template_Rules Assoc
WHERE Rules.Rule_Id = Cond.Rule_Id
AND Rules.Rule_Id = Assoc.Rule_Id
AND Assoc.Template_Id = p_Template_Id
AND Cond.Object_Type = 'QUESTION'
AND Rules.Status_Code IN ('ACTIVE', 'DRAFT', 'REVISION', 'PENDINGDISABLE')
UNION
SELECT distinct To_Number(Cond.Object_Value_Code) Question_Id, Rules.Status_Code
FROM Okc_Xprt_Rule_Conditions Cond,
okc_xprt_rule_hdrs_all Rules,
Okc_Xprt_Template_Rules Assoc
WHERE Rules.Rule_Id = Cond.Rule_Id
AND Rules.Rule_Id = Assoc.Rule_Id
AND Assoc.Template_Id = p_Template_Id
AND Cond.Object_Value_Type = 'QUESTION'
AND Rules.Status_Code IN ('ACTIVE', 'DRAFT', 'REVISION', 'PENDINGDISABLE')
UNION
SELECT distinct To_Number(Outs.Object_Value_Id) Question_Id, Rules.Status_Code
FROM Okc_Xprt_Rule_Outcomes Outs,
okc_xprt_rule_hdrs_all Rules,
Okc_Xprt_Template_Rules Assoc
WHERE Rules.Rule_Id = Assoc.Rule_Id
AND Assoc.Template_Id = p_Template_Id
AND Outs.Object_Type = 'QUESTION'
AND Outs.rule_id = Rules.rule_id
AND Rules.Status_Code IN ('ACTIVE', 'DRAFT', 'REVISION', 'PENDINGDISABLE')
UNION
SELECT distinct To_Number(Cond.Object_Code) Question_Id, Rules.Status_Code
FROM Okc_Xprt_Rule_Conditions Cond,
okc_xprt_rule_hdrs_all Rules
WHERE Rules.Rule_Id = Cond.Rule_Id
AND Rules.Org_Wide_Flag = 'Y'
AND Rules.Org_Id = l_Org_Id
AND Rules.Intent = l_Intent
AND Cond.Object_Type = 'QUESTION'
AND Rules.Status_Code IN ('ACTIVE', 'DRAFT', 'REVISION', 'PENDINGDISABLE')
UNION
SELECT distinct To_Number(Cond.Object_Value_Code) Question_Id, Rules.Status_Code
FROM Okc_Xprt_Rule_Conditions Cond,
okc_xprt_rule_hdrs_all Rules
WHERE Rules.Rule_Id = Cond.Rule_Id
AND Rules.Org_Wide_Flag = 'Y'
AND Rules.Org_Id = l_Org_Id
AND Rules.Intent = l_Intent
AND Cond.Object_Value_Type = 'QUESTION'
AND Rules.Status_Code IN ('ACTIVE', 'DRAFT', 'REVISION', 'PENDINGDISABLE')
UNION
SELECT distinct To_Number(Outs.Object_Value_Id) Question_Id, Rules.Status_Code
FROM Okc_Xprt_Rule_Outcomes Outs,
okc_xprt_rule_hdrs_all Rules
WHERE Rules.Rule_Id = Outs.Rule_Id
AND Rules.Org_Wide_Flag = 'Y'
AND Rules.Org_Id = l_Org_Id
AND Rules.Intent = l_Intent
AND Outs.Object_Type = 'QUESTION'
AND Outs.rule_id = Rules.rule_id
AND Rules.Status_Code IN ('ACTIVE', 'DRAFT', 'REVISION', 'PENDINGDISABLE')
) ORDER BY Status_Code DESC;
3. Run a loop on the cursor to update/insert records into OKC_XPRT_QUESTION_ORDERS and set Draft_rule_qst_flag = 'ACTIVE' or 'DRAFT' based on the rule status.
*/
FOR Question_Rec IN Active_Draft_Rule_Questions LOOP
l_Question_Id := Question_Rec.Question_Id;
UPDATE Okc_Xprt_Question_Orders
SET Question_Rule_Status = DECODE(Question_Rec.Status_Code, 'ACTIVE','ACTIVE','DRAFT' ),
Last_Updated_By = l_user_id,
Last_Update_Date = SYSDATE,
Last_Update_Login = l_login_id,
runtime_available_flag = DECODE(Question_Rec.Status_Code, 'ACTIVE', 'Y','N')
WHERE Template_Id = p_Template_Id
AND Question_Id = l_Question_Id;
INSERT INTO Okc_Xprt_Question_Orders
(Question_Order_Id,
Template_Id,
Question_Id,
Question_Rule_Status,
runtime_available_flag,
Object_Version_Number,
Created_By,
Creation_Date,
Last_Updated_By,
Last_Update_Date,
Last_Update_Login)
VALUES
(OKC_XPRT_QUESTION_ORDERS_S.NEXTVAL,
p_Template_Id,
l_Question_Id,
DECODE(Question_Rec.Status_Code, 'ACTIVE','ACTIVE','DRAFT'),
DECODE(Question_Rec.Status_Code, 'ACTIVE','Y','N'),
1,
l_user_id,
SYSDATE,
l_user_id,
SYSDATE,
l_login_id);
'150: End inserting/updating the Active rule questions.');
'160: Start inserting/updating the Pending Publication rule questions.');
SELECT distinct To_Number(Cond.Object_Code) Question_Id
FROM Okc_Xprt_Rule_Conditions Cond,
okc_xprt_rule_hdrs_all Rules,
Okc_Xprt_Template_Rules Assoc
WHERE Rules.Rule_Id = Cond.Rule_Id
AND Rules.Rule_Id = Assoc.Rule_Id
AND Assoc.Template_Id = p_Template_Id
AND Cond.Object_Type = 'QUESTION'
AND Rules.Status_Code IN ('PENDINGPUB')
AND Rules.Request_Id = l_conc_request_id
UNION
SELECT distinct To_Number(Cond.Object_Value_Code) Question_Id
FROM Okc_Xprt_Rule_Conditions Cond,
okc_xprt_rule_hdrs_all Rules,
Okc_Xprt_Template_Rules Assoc
WHERE Rules.Rule_Id = Cond.Rule_Id
AND Rules.Rule_Id = Assoc.Rule_Id
AND Assoc.Template_Id = p_Template_Id
AND Cond.Object_Value_Type = 'QUESTION'
AND Rules.Status_Code IN ('PENDINGPUB')
AND Rules.Request_Id = l_conc_request_id
UNION
SELECT distinct To_Number(Outs.Object_Value_Id) Question_Id
FROM Okc_Xprt_Rule_Outcomes Outs,
okc_xprt_rule_hdrs_all Rules,
Okc_Xprt_Template_Rules Assoc
WHERE Rules.Rule_Id = Assoc.Rule_Id
AND Assoc.Template_Id = p_Template_Id
AND Outs.Object_Type = 'QUESTION'
And Outs.rule_id = Rules.rule_id
AND Rules.Status_Code IN ('PENDINGPUB')
AND Rules.Request_Id = l_conc_request_id
UNION
SELECT distinct To_Number(Cond.Object_Code) Question_Id
FROM Okc_Xprt_Rule_Conditions Cond,
okc_xprt_rule_hdrs_all Rules
WHERE Rules.Rule_Id = Cond.Rule_Id
AND Rules.Org_Wide_Flag = 'Y'
AND Rules.Org_Id = l_Org_Id
AND Rules.Intent = l_Intent
AND Cond.Object_Type = 'QUESTION'
AND Rules.Status_Code IN ('PENDINGPUB')
AND Rules.Request_Id = l_conc_request_id
UNION
SELECT distinct To_Number(Cond.Object_Value_Code) Question_Id
FROM Okc_Xprt_Rule_Conditions Cond,
okc_xprt_rule_hdrs_all Rules
WHERE Rules.Rule_Id = Cond.Rule_Id
AND Rules.Org_Wide_Flag = 'Y'
AND Rules.Org_Id = l_Org_Id
AND Rules.Intent = l_Intent
AND Cond.Object_Value_Type = 'QUESTION'
AND Rules.Status_Code IN ('PENDINGPUB')
AND Rules.Request_Id = l_conc_request_id
UNION
SELECT distinct To_Number(Outs.Object_Value_Id) Question_Id
FROM Okc_Xprt_Rule_Outcomes Outs,
okc_xprt_rule_hdrs_all Rules
WHERE Rules.Rule_Id = Outs.Rule_Id
AND Rules.Org_Wide_Flag = 'Y'
AND Rules.Org_Id = l_Org_Id
AND Rules.Intent = l_Intent
AND Outs.Object_Type = 'QUESTION'
AND Outs.rule_id = Rules.rule_id
AND Rules.Status_Code IN ('PENDINGPUB')
AND Rules.Request_Id = l_conc_request_id;
Run a loop on the cursor to update/insert records into OKC_XPRT_QUESTION_ORDERS and set QUESTION_RULE_STATUS = 'PENDINGPUB'.
*/
FOR Question_Rec IN Pending_Rule_Questions LOOP
l_Question_Id := Question_Rec.Question_Id;
UPDATE Okc_Xprt_Question_Orders
SET Question_Rule_Status = DECODE(Question_Rule_Status,'ACTIVE','ACTIVE','PENDINGPUB'),
Last_Updated_By = l_user_id,
Last_Update_Date = SYSDATE,
Last_Update_Login = l_login_id,
runtime_available_flag = DECODE(Question_Rule_Status,'ACTIVE','Y','N')
WHERE Template_Id = p_Template_Id
AND Question_Id = l_Question_Id ;
INSERT INTO Okc_Xprt_Question_Orders
(Question_Order_Id,
Template_Id,
Question_Id,
Question_Rule_Status,
runtime_available_flag,
Object_Version_Number,
Created_By,
Creation_Date,
Last_Updated_By,
Last_Update_Date,
Last_Update_Login)
VALUES
(OKC_XPRT_QUESTION_ORDERS_S.NEXTVAL,
p_Template_Id,
l_Question_Id,
'PENDINGPUB',
'N',
1,
l_user_id,
SYSDATE,
l_user_id,
SYSDATE,
l_login_id);
5. Update all the records for the table and set mandatory_flag = 'N'.
Then set mandatory_flag = 'Y' for those questions which appear only in the conditions and not in the outcome.
In the UI mode, choose all rules for selecting the questions.
For all other modes, only select 'ACTIVE' rules and 'PENDINGPUB' rules for the request id.
*/
UPDATE Okc_Xprt_Question_Orders
SET Mandatory_Flag = 'N',
Last_Updated_By = l_user_id,
Last_Update_Date = SYSDATE,
Last_Update_Login = l_login_id
WHERE Template_Id = p_Template_Id;
UPDATE Okc_Xprt_Question_Orders
SET Mandatory_Flag = 'Y',
Last_Updated_By = l_user_id,
Last_Update_Date = SYSDATE,
Last_Update_Login = l_login_id
WHERE Template_Id = p_Template_Id
AND Question_Id IN
(SELECT Ord.Question_Id
FROM Okc_Xprt_Rule_Conditions Cond,
okc_xprt_rule_hdrs_all Rules,
Okc_Xprt_Template_Rules Assoc,
Okc_Xprt_Question_Orders Ord
WHERE Rules.Rule_Id = Cond.Rule_Id
AND Rules.Rule_Id = Assoc.Rule_Id
AND Assoc.Template_Id = p_Template_Id
AND (
(to_char(Ord.Question_Id) = Cond.Object_Code
AND Cond.Object_Type = 'QUESTION') OR
(to_char(Ord.Question_Id) = Cond.Object_Value_Code
AND Cond.Object_Value_Type = 'QUESTION')
)
);
UPDATE Okc_Xprt_Question_Orders
SET Mandatory_Flag = 'Y',
Last_Updated_By = l_user_id,
Last_Update_Date = SYSDATE,
Last_Update_Login = l_login_id
WHERE Template_Id = p_Template_Id
AND Question_Id IN
(SELECT Ord.Question_Id
FROM Okc_Xprt_Rule_Conditions Cond,
okc_xprt_rule_hdrs_all Rules,
Okc_Xprt_Question_Orders Ord
WHERE Rules.Rule_Id = Cond.Rule_Id
AND Rules.Org_Wide_Flag = 'Y'
AND Rules.Org_Id = l_Org_Id
AND Rules.Intent = l_Intent
AND (
(to_char(Ord.Question_Id) = Cond.Object_Code
AND Cond.Object_Type = 'QUESTION') OR
(to_char(Ord.Question_Id) = Cond.Object_Value_Code
AND Cond.Object_Value_Type = 'QUESTION')
)
);
UPDATE Okc_Xprt_Question_Orders
SET Mandatory_Flag = 'N',
Last_Updated_By = l_user_id,
Last_Update_Date = SYSDATE,
Last_Update_Login = l_login_id
WHERE Template_Id = p_Template_Id
AND Question_Id IN
(SELECT Ord.Question_Id
FROM Okc_Xprt_Rule_Outcomes Outs,
okc_xprt_rule_hdrs_all Rules,
Okc_Xprt_Template_Rules Assoc,
Okc_Xprt_Question_Orders Ord
WHERE Rules.Rule_Id = Assoc.Rule_Id
AND Assoc.Template_Id = p_Template_Id
AND to_char(Ord.Question_Id) = Outs.Object_Value_Id
AND Outs.Object_Type = 'QUESTION'
AND rules.rule_id = outs.rule_id
AND rules.status_code <> 'INACTIVE' --Added for Bug#4108690
);
UPDATE Okc_Xprt_Question_Orders
SET Mandatory_Flag = 'N',
Last_Updated_By = l_user_id,
Last_Update_Date = SYSDATE,
Last_Update_Login = l_login_id
WHERE Template_Id = p_Template_Id
AND Question_Id IN
(SELECT Ord.Question_Id
FROM Okc_Xprt_Rule_Outcomes Outs,
okc_xprt_rule_hdrs_all Rules,
Okc_Xprt_Question_Orders Ord
WHERE Rules.Rule_Id = Outs.Rule_Id
AND Rules.Org_Wide_Flag = 'Y'
AND Rules.Org_Id = l_Org_Id
AND Rules.Intent = l_Intent
AND Rules.status_code <> 'INACTIVE' --Added for Bug#4108690
AND to_char(Ord.Question_Id) = Outs.Object_Value_Id
AND Outs.Object_Type = 'QUESTION'
);
UPDATE Okc_Xprt_Question_Orders
SET Mandatory_Flag = 'Y',
Last_Updated_By = l_user_id,
Last_Update_Date = SYSDATE,
Last_Update_Login = l_login_id
WHERE Template_Id = p_Template_Id
AND Question_Id IN
(SELECT Ord.Question_Id
FROM Okc_Xprt_Rule_Conditions Cond,
okc_xprt_rule_hdrs_all Rules,
Okc_Xprt_Template_Rules Assoc,
Okc_Xprt_Question_Orders Ord
WHERE Rules.Rule_Id = Cond.Rule_Id
AND Rules.Rule_Id = Assoc.Rule_Id
AND Assoc.Template_Id = p_Template_Id
AND (
(to_char(Ord.Question_Id) = Cond.Object_Code
AND Cond.Object_Type = 'QUESTION') OR
(to_char(Ord.Question_Id) = Cond.Object_Value_Code
AND Cond.Object_Value_Type = 'QUESTION')
)
AND Rules.Status_Code = 'ACTIVE'
UNION
SELECT Ord.Question_Id
FROM Okc_Xprt_Rule_Conditions Cond,
okc_xprt_rule_hdrs_all Rules,
Okc_Xprt_Question_Orders Ord
WHERE Rules.Rule_Id = Cond.Rule_Id
AND Rules.Org_Wide_Flag = 'Y'
AND Rules.Org_Id = l_Org_Id
AND Rules.Intent = l_Intent
AND (
(to_char(Ord.Question_Id) = Cond.Object_Code
AND Cond.Object_Type = 'QUESTION') OR
(to_char(Ord.Question_Id) = Cond.Object_Value_Code
AND Cond.Object_Value_Type = 'QUESTION')
)
AND Rules.Status_Code = 'ACTIVE');
UPDATE Okc_Xprt_Question_Orders
SET Mandatory_Flag = 'Y',
Last_Updated_By = l_user_id,
Last_Update_Date = SYSDATE,
Last_Update_Login = l_login_id
WHERE Template_Id = p_Template_Id
AND Question_Id IN
(SELECT Ord.Question_Id
FROM Okc_Xprt_Rule_Conditions Cond,
okc_xprt_rule_hdrs_all Rules,
Okc_Xprt_Template_Rules Assoc,
Okc_Xprt_Question_Orders Ord
WHERE Rules.Rule_Id = Cond.Rule_Id
AND Rules.Rule_Id = Assoc.Rule_Id
AND Assoc.Template_Id = p_Template_Id
AND (
(to_char(Ord.Question_Id) = Cond.Object_Code
AND Cond.Object_Type = 'QUESTION') OR
(to_char(Ord.Question_Id) = Cond.Object_Value_Code
AND Cond.Object_Value_Type = 'QUESTION')
)
AND Rules.Status_Code = 'PENDINGPUB'
AND Rules.Request_Id = l_conc_request_id
UNION
SELECT Ord.Question_Id
FROM Okc_Xprt_Rule_Conditions Cond,
okc_xprt_rule_hdrs_all Rules,
Okc_Xprt_Question_Orders Ord
WHERE Rules.Rule_Id = Cond.Rule_Id
AND Rules.Org_Wide_Flag = 'Y'
AND Rules.Org_Id = l_Org_Id
AND Rules.Intent = l_Intent
AND (
(to_char(Ord.Question_Id) = Cond.Object_Code
AND Cond.Object_Type = 'QUESTION') OR
(to_char(Ord.Question_Id) = Cond.Object_Value_Code
AND Cond.Object_Value_Type = 'QUESTION')
)
AND Rules.Status_Code = 'PENDINGPUB'
AND Rules.Request_Id = l_conc_request_id );
UPDATE Okc_Xprt_Question_Orders
SET Mandatory_Flag = 'N',
Last_Updated_By = l_user_id,
Last_Update_Date = SYSDATE,
Last_Update_Login = l_login_id
WHERE Template_Id = p_Template_Id
AND Question_Id IN
(SELECT Ord.Question_Id
FROM Okc_Xprt_Rule_Outcomes Outs,
okc_xprt_rule_hdrs_all Rules,
Okc_Xprt_Template_Rules Assoc,
Okc_Xprt_Question_Orders Ord
WHERE Rules.Rule_Id = Assoc.Rule_Id
AND Assoc.Template_Id = p_Template_Id
AND to_char(Ord.Question_Id) = Outs.Object_Value_Id
AND Outs.Object_Type = 'QUESTION'
AND Rules.Status_Code = 'ACTIVE'
AND rules.rule_id = outs.rule_id
UNION
SELECT Ord.Question_Id
FROM Okc_Xprt_Rule_Outcomes Outs,
okc_xprt_rule_hdrs_all Rules,
Okc_Xprt_Question_Orders Ord
WHERE Rules.Rule_Id = Outs.Rule_Id
AND Rules.Org_Wide_Flag = 'Y'
AND Rules.Org_Id = l_Org_Id
AND Rules.Intent = l_Intent
AND to_char(Ord.Question_Id) = Outs.Object_Value_Id
AND Outs.Object_Type = 'QUESTION'
AND Rules.Status_Code = 'ACTIVE'
AND rules.rule_id = outs.rule_id );
UPDATE Okc_Xprt_Question_Orders
SET Mandatory_Flag = 'N',
Last_Updated_By = l_user_id,
Last_Update_Date = SYSDATE,
Last_Update_Login = l_login_id
WHERE Template_Id = p_Template_Id
AND Question_Id IN
(SELECT Ord.Question_Id
FROM Okc_Xprt_Rule_Outcomes Outs,
okc_xprt_rule_hdrs_all Rules,
Okc_Xprt_Template_Rules Assoc,
Okc_Xprt_Question_Orders Ord
WHERE Rules.Rule_Id = Assoc.Rule_Id
AND Assoc.Template_Id = p_Template_Id
AND to_char(Ord.Question_Id) = Outs.Object_Value_Id
AND Outs.Object_Type = 'QUESTION'
AND Rules.Status_Code = 'PENDINGPUB'
AND Rules.Request_Id = l_conc_request_id
AND rules.rule_id = outs.rule_id
UNION
SELECT Ord.Question_Id
FROM Okc_Xprt_Rule_Outcomes Outs,
okc_xprt_rule_hdrs_all Rules,
Okc_Xprt_Question_Orders Ord
WHERE Rules.Rule_Id = Outs.Rule_Id
AND Rules.Org_Wide_Flag = 'Y'
AND Rules.Org_Id = l_Org_Id
AND Rules.Intent = l_Intent
AND to_char(Ord.Question_Id) = Outs.Object_Value_Id
AND Outs.Object_Type = 'QUESTION'
AND Rules.Status_Code = 'PENDINGPUB'
AND Rules.Request_Id = l_conc_request_id);
select a cursor for new records and
set sequence_no with sequence_no+1 for each new record,
where the intial sequence_no is max of existing sequence_no for the template .
*/
IF (Fnd_Log.Level_Procedure >= Fnd_Log.g_Current_Runtime_Level) THEN
Fnd_Log.STRING(Fnd_Log.Level_Procedure,
g_Module || l_Api_Name,
'220: End setting the mandatory flag.');
'230: Start populating sequence for newly inserted questions.');
SELECT Question_Id
FROM Okc_Xprt_Question_Orders
WHERE Template_Id = p_Template_Id
AND Sequence_Num IS NULL
ORDER BY Mandatory_Flag DESC;
SELECT MAX(Sequence_Num)
INTO l_Max_Seq
FROM Okc_Xprt_Question_Orders
WHERE Template_Id = p_Template_Id;
UPDATE Okc_Xprt_Question_Orders
SET Sequence_Num = l_Max_Seq
WHERE Template_Id = p_Template_Id
AND Question_Id = l_Question_Id;
'240: End populating sequence for newly inserted questions.');
Function : ok_to_delete_question
This function will return 'Y' for a question id if it is not used in
rule condition or outcome.
If used, it returns 'N'.
*/
FUNCTION Ok_To_Delete_Question(
p_question_id IN NUMBER)
RETURN VARCHAR2 IS
CURSOR used_in_rule_cond_crs IS
SELECT 'N'
FROM OKC_XPRT_RULE_CONDITIONS
WHERE (object_type IN ('QUESTION','CONSTANT') AND
object_code = to_char(p_question_id) ) -- Added for bug 5663927
OR
(object_value_type IN ('QUESTION','CONSTANT') AND
object_value_code = to_char(p_question_id)) -- Added for bug 5663927
UNION
SELECT 'N'
FROM OKC_XPRT_RULE_OUTCOMES
WHERE object_type = 'QUESTION'
AND object_value_id = p_question_id;
END Ok_To_Delete_Question;
Delete Production mode Publication
DELETE the UI attached to the production publication
Update Test mode Publication to Production mode
Update Rule status in the current concurrent request id
Update the runtime_available_flag in okc_xprt_question_orders table
Delete records from okc_xprt_template_rules where deleted_flag = 'Y'
commit the work
This procedure can also be called in Template approval flow with mode 'TEMPLATE_APPROVAL'
This procedure does the following in 'TEMPLATE_APPROVAL' mode:
For the called template
Delete Production mode Publication
Update Test mode Publication to Production mode
Update the runtime_available_flag in okc_xprt_question_orders table
*/
PROCEDURE create_production_publication
(
p_calling_mode IN VARCHAR2,
p_template_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER
) IS
/*Bug 5032199 commented below cursor.Using REF cursor
CURSOR csr_templates IS
SELECT DECODE(parent_template_id, NULL, template_id, parent_template_id) template_id,
template_model_id
FROM okc_terms_templates_all
WHERE template_id = DECODE(p_template_id,NULL,template_id,p_template_id)
AND xprt_request_id = DECODE(p_calling_mode,'TEMPLATE_APPROVAL',xprt_request_id, FND_GLOBAL.CONC_REQUEST_ID);
SELECT rule_id
FROM okc_xprt_rule_hdrs_all
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID ;
SELECT publication_id ,
ui_def_id
FROM cz_model_publications
WHERE model_id = p_template_model_id
AND deleted_flag = '0'
AND publication_mode = p_publication_mode
AND source_target_flag = 'S';
l_sql_stmt := 'SELECT template_model_id
FROM okc_terms_templates_all
WHERE template_id = '||p_template_id||'
AND xprt_request_id = DECODE('''||p_calling_mode||''',
''TEMPLATE_APPROVAL'',xprt_request_id,'||FND_GLOBAL.CONC_REQUEST_ID||')';
l_sql_stmt := 'SELECT template_model_id
FROM okc_terms_templates_all
WHERE xprt_request_id = DECODE('''||p_calling_mode||''',
''TEMPLATE_APPROVAL'',xprt_request_id,'||FND_GLOBAL.CONC_REQUEST_ID||')';
-- Delete Production mode publication
OPEN csr_get_publication_id(p_template_model_id => l_template_model_id,
p_publication_mode => G_PRODUCTION_MODE);
'Calling OKC_XPRT_CZ_INT_PVT.delete_publication with parameters');
-- Call CZ delete publicaton API
OKC_XPRT_CZ_INT_PVT.delete_publication
(
p_api_version => l_api_version,
p_init_msg_lst => FND_API.G_FALSE,
p_publication_id => l_publication_id,
x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count
) ;
'After Calling OKC_XPRT_CZ_INT_PVT.delete_publication');
-- Delete the UI attached to the above production publication
OKC_XPRT_CZ_INT_PVT.delete_ui_def
(
p_api_version => l_api_version,
p_ui_def_id => l_ui_def_id,
x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count
) ;
'Calling OKC_XPRT_CZ_INT_PVT.delete_publication with parameters');
'After Calling OKC_XPRT_CZ_INT_PVT.delete_publication');
UPDATE Okc_Xprt_Question_Orders
SET runtime_available_flag = 'Y',
question_rule_status = 'ACTIVE',
last_updated_by = FND_GLOBAL.USER_ID,
last_update_date = SYSDATE,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE question_rule_status = 'PENDINGPUB'
AND template_id IN ( SELECT template_id
FROM okc_terms_templates_all
WHERE xprt_request_id = FND_GLOBAL.CONC_REQUEST_ID
);
DELETE FROM okc_xprt_template_rules
WHERE NVL(deleted_flag,'N') = 'Y'
AND template_id IN ( SELECT template_id
FROM okc_terms_templates_all
WHERE xprt_request_id = FND_GLOBAL.CONC_REQUEST_ID
);
UPDATE okc_xprt_template_rules
SET published_flag = 'Y'
WHERE template_id IN ( SELECT template_id
FROM okc_terms_templates_all
WHERE xprt_request_id = FND_GLOBAL.CONC_REQUEST_ID
);
UPDATE Okc_Xprt_Question_Orders
SET runtime_available_flag = 'Y',
last_updated_by = FND_GLOBAL.USER_ID,
last_update_date = SYSDATE,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE template_id= p_template_id
AND question_rule_status = 'ACTIVE';
DELETE FROM okc_xprt_template_rules
WHERE NVL(deleted_flag,'N') = 'Y'
AND template_id = p_template_id;
UPDATE okc_xprt_template_rules
SET published_flag = 'Y'
WHERE template_id= p_template_id ;
SELECT okc_xprt_util_pvt.is_rule_line_level(l_rule_id) INTO l_line_level_flag FROM DUAL;
UPDATE okc_xprt_rule_hdrs_all
SET status_code = DECODE (p_calling_mode, 'PUBLISH', 'ACTIVE', 'DISABLE', 'INACTIVE', 'SYNC', 'ACTIVE'),
published_flag = 'Y',
line_level_flag = l_line_level_flag, --is_rule_line_level(l_rule_id),
last_updated_by = FND_GLOBAL.USER_ID,
last_update_date = SYSDATE,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE rule_id = l_rule_id;
SELECT contract_expert_enabled , template_name , XPRT_SCN_CODE
FROM okc_terms_templates_all
WHERE template_id = p_template_id;
SELECT 'X'
FROM okc_xprt_rule_hdrs_all r,
okc_terms_templates_all t
WHERE t.org_id = r.org_id
AND t.intent = r.intent
AND t.template_id = p_template_id
AND r.org_wide_flag = 'Y'
AND r.status_code NOT IN ('ACTIVE','INACTIVE')
UNION ALL
-- Local rules not active
SELECT 'X'
FROM okc_xprt_template_rules tr,
okc_xprt_rule_hdrs_all r
WHERE tr.template_id = p_template_id
AND tr.rule_id = r.rule_id
AND r.status_code NOT IN ('ACTIVE','INACTIVE');
SELECT 'X'
FROM okc_xprt_rule_hdrs_all r,
okc_terms_templates_all t
WHERE t.org_id = r.org_id
AND t.intent = r.intent
AND t.template_id = p_template_id
AND r.org_wide_flag = 'Y'
AND r.status_code = 'ACTIVE'
UNION ALL
-- Local Active rules
SELECT 'X'
FROM okc_xprt_template_rules tr,
okc_xprt_rule_hdrs_all r
WHERE tr.template_id = p_template_id
AND tr.rule_id = r.rule_id
AND r.status_code = 'ACTIVE' ;
SELECT meaning
FROM fnd_lookups
WHERE lookup_type = 'OKC_ARTICLE_SECTION'
AND lookup_code = p_scn_code
AND sysdate not between start_date_active and nvl(end_date_active,sysdate) ;
SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY')
FROM dual;
SELECT org_id,
intent
FROM okc_terms_templates_all
WHERE template_id = p_template_id;
Description : Compares the newly selected Configurator articles
with the Configurator-selected articles currently in
the document. It does the following:
a. For each CZ Document Article not in CZ New Articles
-> Insert message in QA PL/SQL Table
b. For each CZ New Article not in CZ Document Articles
-> Insert message in QA PL/SQL Table
+====================================================================*/
PROCEDURE process_qa_result(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_document_id IN NUMBER,
p_document_type IN VARCHAR2,
p_config_header_id IN NUMBER,
p_config_rev_nbr IN NUMBER,
x_qa_result_tbl IN OUT NOCOPY OKC_TERMS_QA_GRP.qa_result_tbl_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2(30) := 'process_qa_result';
SELECT NVL(PROVISION_ALLOWED_YN,'Y')
FROM OKC_BUS_DOC_TYPES_B
WHERE DOCUMENT_TYPE=p_document_type;
SELECT SUBSTR(orig_sys_ref,INSTR(orig_sys_ref,':',-1,1)+1)
FROM cz_config_items_v
WHERE config_hdr_id = p_config_header_id
AND config_rev_nbr = p_config_rev_nbr
AND orig_sys_ref LIKE 'OKC:CLAUSEMODELOPTION:%' ;
SELECT kart.orig_article_id
FROM OKC_K_ARTICLES_B KART
WHERE document_type = p_document_type
AND document_id = p_document_id
AND source_flag='R'
AND NVL(kart.AMENDMENT_OPERATION_CODE, '?') <> 'DELETED'
AND NVL(kart.SUMMARY_AMEND_OPERATION_CODE, '?') <> 'DELETED'
AND kart.orig_article_id = b_article_id;
SELECT orig_article_id
FROM okc_k_articles_b kart
WHERE kart.document_type = p_document_type
AND kart.document_id = p_document_id
AND kart.source_flag = 'R' -- from Contract Expert
AND NVL(kart.AMENDMENT_OPERATION_CODE, '?') <> 'DELETED'
AND NVL(kart.SUMMARY_AMEND_OPERATION_CODE, '?') <> 'DELETED'
--AND (nvl(nvl(kart.ref_article_id, kart.sav_sae_id), -1) not in (
AND (kart.orig_article_id NOT IN (
SELECT SUBSTR(orig_sys_ref,INSTR(orig_sys_ref,':',-1,1)+1)
FROM cz_config_items_v
WHERE config_hdr_id = p_config_header_id
AND config_rev_nbr = p_config_rev_nbr
AND orig_sys_ref LIKE 'OKC:CLAUSEMODELOPTION:%'
)
);
SELECT NVL(PROVISION_YN,'N')
FROM OKC_ARTICLE_VERSIONS VERS
WHERE vers.article_version_id = p_article_version_id;
SELECT article_version_id
FROM okc_article_versions
WHERE article_id= p_article_id
AND article_status in ('ON_HOLD','APPROVED')
AND start_date = (SELECT MAX(start_date)
FROM okc_article_versions
WHERE article_id= p_article_id
AND article_status in ('ON_HOLD','APPROVED')
);
SELECT NVL(PROVISION_ALLOWED_YN,'Y')
FROM OKC_BUS_DOC_TYPES_B
WHERE DOCUMENT_TYPE=p_document_type;
SELECT SUBSTR(orig_sys_ref,INSTR(orig_sys_ref,':',-1,1)+1)
FROM cz_config_items_v
WHERE config_hdr_id = p_config_header_id
AND config_rev_nbr = p_config_rev_nbr
AND orig_sys_ref LIKE 'OKC:CLAUSEMODELOPTION:%' ;
SELECT NVL(PROVISION_YN,'N')
FROM OKC_ARTICLE_VERSIONS VERS
WHERE vers.article_id = p_article_id;
Procedure Name : update_ce_config
Description : Update Expert Articles in Document. This API
is called from the runtime Contract Expert page during
document authoring. Specifically, it is called
when a user selects the 'Finish' button from Contract
Expert and the resulting configuration is valid and
complete.
-- Sanjay
Before updating the template usage record read the previous
config_header_id and config_rev_nbr and delete the same
Parameters:
p_document_id - id of document id to be updated
p_document_type - type of document to be updated
p_config_header_id - configuration header id of resulting
article configuration
p_config_rev_nbr - configuration number of resutling
article configuration
+====================================================================*/
PROCEDURE update_ce_config(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_document_id IN NUMBER,
p_document_type IN VARCHAR2,
p_config_header_id IN NUMBER,
p_config_rev_nbr IN NUMBER,
p_doc_update_mode IN VARCHAR2,
x_count_articles_dropped OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2(30) := 'update_ce_config';
SELECT SUBSTR(orig_sys_ref,INSTR(orig_sys_ref,':',-1,1)+1)
FROM cz_config_items_v
WHERE config_hdr_id = p_config_header_id
AND config_rev_nbr = p_config_rev_nbr
AND orig_sys_ref LIKE 'OKC:CLAUSEMODELOPTION:%' ;
SELECT config_header_id,
config_revision_number
FROM okc_template_usages
WHERE document_type = p_document_type
AND document_id = p_document_id ;
p_mode => p_doc_update_mode, -- Defaults to 'NORMAL'
x_articles_dropped => x_count_articles_dropped,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
UPDATE okc_template_usages
SET config_header_id = p_config_header_id,
config_revision_number = p_config_rev_nbr,
valid_config_yn = 'Y',
last_update_date = SYSDATE,
last_updated_by= FND_GLOBAL.USER_ID,
last_update_login= FND_GLOBAL.LOGIN_ID
WHERE document_type = p_document_type
AND document_id = p_document_id ;
OKC_TEMPLATE_USAGES_GRP.update_template_usages(
p_api_version => 1,
p_init_msg_list => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_commit => FND_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_document_type => p_document_type,
p_document_id => p_document_id,
p_template_id => NULL,
p_doc_numbering_scheme => NULL,
p_document_number => NULL,
p_article_effective_date => NULL,
p_config_header_id => p_config_header_id,
p_config_revision_number => p_config_rev_nbr,
p_valid_config_yn => 'Y', -- check
p_object_version_number => 1
);
OKC_XPRT_CZ_INT_PVT.delete_configuration(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_config_header_id => l_old_config_header_id,
p_config_rev_nbr => l_old_config_revision_number,
x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count);
END IF; -- delete the old configuration
x_msg_data := 'OKC_EXPRT_UPDATED_WITH_PROVS';
x_msg_data := 'OKC_EXPRT_ARTICLES_UPDATED';
END update_ce_config;
Procedure Name : update_config_id_rev_nbr
Description : Update Configuration ID and Rev Number for document.
This API is called from the runtime Contract Expert page during
document authoring. Specifically, it is called
when a user selects the 'Save' button from Contract
Expert.
-- Sanjay
Before updating the template usage record read the previous
config_header_id and config_rev_nbr and delete the same
Parameters:
p_document_id - id of document id to be updated
p_document_type - type of document to be updated
p_template_id - id of template applied to document to be
updated
p_config_header_id - configuration header id of resulting
article configuration
p_config_rev_nbr - configuration number of resutling
article configuration
+====================================================================*/
PROCEDURE update_config_id_rev_nbr(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_document_id IN NUMBER,
p_document_type IN VARCHAR2,
p_config_header_id IN NUMBER,
p_config_rev_nbr IN NUMBER,
p_template_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2(30) := 'update_config_id_rev_nbr';
SELECT config_header_id,
config_revision_number
FROM okc_template_usages
WHERE document_type = p_document_type
AND document_id = p_document_id ;
UPDATE okc_template_usages
SET config_header_id = p_config_header_id,
config_revision_number = p_config_rev_nbr,
valid_config_yn = 'Y',
last_update_date = SYSDATE,
last_updated_by= FND_GLOBAL.USER_ID,
last_update_login= FND_GLOBAL.LOGIN_ID
WHERE document_type = p_document_type
AND document_id = p_document_id ;
OKC_TEMPLATE_USAGES_GRP.update_template_usages(
p_api_version => 1,
p_init_msg_list => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_commit => FND_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_document_type => p_document_type,
p_document_id => p_document_id,
p_template_id => p_template_id,
p_doc_numbering_scheme => NULL,
p_document_number => NULL,
p_article_effective_date => NULL,
p_config_header_id => p_config_header_id,
p_config_revision_number => p_config_rev_nbr,
p_valid_config_yn => 'Y', -- check
p_object_version_number => 1
);
OKC_XPRT_CZ_INT_PVT.delete_configuration(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_config_header_id => l_old_config_header_id,
p_config_rev_nbr => l_old_config_revision_number,
x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count);
END IF; -- delete the old configuration
END update_config_id_rev_nbr;
select id, article_version_id, 'D' from okc_k_articles_b
where document_id = p_doc_id
and document_type = p_doc_type
and ((sav_sae_id = p_article_id) or (ref_article_id = p_article_id))
and source_flag = 'R'
and rownum < 2;
SELECT ver.article_id, ver.article_version_id, 'L'
FROM okc_articles_all art,
okc_article_versions ver
WHERE art.article_id = p_article_id
AND art.article_id = ver.article_id
AND ver.article_status IN ('APPROVED','ON_HOLD')
AND nvl(effective_date,trunc(SYSDATE)) BETWEEN ver.start_date AND NVL(ver.end_date,TRUNC(SYSDATE));
SELECT ver.article_id, ver.article_version_id, 'L'
FROM okc_article_versions ver
WHERE ver.article_id = p_article_id
AND ver.article_status IN ('APPROVED','ON_HOLD')
AND ver.start_date = (SELECT max(start_date)
FROM okc_article_versions ver1
WHERE ver1.article_id = p_article_id
AND ver1.article_status IN ('APPROVED','ON_HOLD'));
SELECT 'X'
FROM okc_xprt_rule_conditions c,
okc_xprt_rule_cond_vals cv
WHERE c.rule_condition_id = cv.rule_condition_id
AND c.object_value_type = 'CLAUSE'
AND cv.object_value_code = p_clause_id
AND c.rule_id = p_rule_id ;
SELECT 'X'
FROM okc_xprt_rule_outcomes o
WHERE o.rule_id = p_rule_id
AND o.object_type = 'CLAUSE'
AND o.object_value_id = p_clause_id ;
SELECT 'X'
FROM okc_xprt_rule_conditions c,
okc_xprt_rule_cond_vals cv
WHERE c.rule_condition_id = cv.rule_condition_id
AND c.object_value_type = 'VARIABLE'
AND cv.object_value_code = p_variable_code
AND c.rule_id = p_rule_id ;
SELECT 'X'
FROM okc_xprt_rule_conditions c
WHERE c.object_type = 'VARIABLE'
AND c.object_code = p_variable_code
AND c.rule_id = p_rule_id ;
SELECT 'x'
FROM okc_xprt_rule_cond_vals cvals,
okc_xprt_rule_conditions cond
WHERE cond.rule_id = p_rule_id
AND cond.rule_condition_id=cvals.rule_condition_id
AND cond.object_value_type ='QUESTION'
AND cvals.object_value_code=p_question_id
UNION ALL
SELECT 'x'
FROM okc_xprt_rule_conditions cond
WHERE cond.rule_id = p_rule_id
AND cond.object_type ='QUESTION'
AND cond.object_code=to_char(p_question_id)
UNION ALL
SELECT 'x'
FROM okc_xprt_rule_outcomes xro
WHERE xro.rule_id =p_rule_id
AND xro.object_type='QUESTION'
AND xro.object_value_id=p_question_id;
SELECT 'x'
FROM okc_xprt_rule_hdrs_all rule
WHERE rule.rule_id = p_rule_id
AND rule.org_wide_flag = 'Y'
AND (rule.intent, rule.org_id) in (select intent, org_id from okc_terms_templates_all where template_id = p_template_id)
UNION ALL
--template specific rules
SELECT 'x'
FROM okc_xprt_rule_hdrs_all xrh,
okc_xprt_template_rules xtr
WHERE xrh.rule_id = xtr.rule_id
AND xrh.rule_id = p_rule_id
AND template_id=p_template_id;
select 'x'
from okc_xprt_rule_hdrs_all
where org_wide_flag='Y';
Used to display condition name in rules update/view mode
+====================================================================*/
FUNCTION get_object_name (
p_object_name IN VARCHAR2,
p_object_code IN VARCHAR2)
RETURN VARCHAR2 IS
CURSOR c1 IS
SELECT variable_name
FROM okc_bus_variables_v
WHERE variable_code=p_object_code;
SELECT question_name
FROM okc_xprt_questions_vl
WHERE question_id=TO_NUMBER(p_object_code)
AND question_type='Q';
in rules update/view mode
+====================================================================*/
FUNCTION get_value_display (
p_object_value_type IN VARCHAR2,
p_object_value_code IN VARCHAR2,
p_object_value_set_id IN NUMBER,
p_validation_type IN VARCHAR2,
p_longlist_flag IN VARCHAR2,
p_mode IN VARCHAR2 )
RETURN VARCHAR2 IS
CURSOR c1 IS
SELECT article_title
FROM okc_articles_all
WHERE article_id=to_number(p_object_value_code);
SELECT question_name
FROM okc_xprt_questions_vl
WHERE question_id=TO_NUMBER(p_object_value_code)
AND question_type= decode(p_object_value_type,'CONSTANT','C','Q');
SELECT variable_name
FROM okc_bus_variables_v
WHERE variable_code=p_object_value_code;
select value.flex_value
--,value.description
from fnd_flex_values_vl value
where value.FLEX_VALUE_SET_ID = p_object_value_set_id
and enabled_flag='Y'
and trunc(sysdate) between nvl(trunc(start_date_active),trunc(sysdate)) and nvl(trunc(end_date_active),trunc(sysdate))
*/
l_value VARCHAR2(1000) := null;
SELECT Object_Value_Code
FROM OKC_XPRT_RULE_COND_VALS
WHERE rule_condition_id = p_rule_condition_id;
SELECT variable_value
FROM OKC_XPRT_DEVIATIONS_T
WHERE variable_code = p_object_code
AND run_id = p_sequence_id
AND variable_value is not null;
SELECT localized_str translated_value
FROM cz_localized_texts
WHERE orig_sys_ref LIKE 'OKC:VARIABLEMODELOPTION:-99:%:' || p_variable_id || ':' || p_variable_value
AND LANGUAGE = USERENV('LANG');
SELECT config_header_id,config_revision_number
FROM OKC_TERMS_DEVIATIONS_T
WHERE sequence_id = p_sequence_id
AND rownum < 2;
SELECT variable_value
FROM OKC_XPRT_DEVIATIONS_T
WHERE variable_code = l_object_code
AND (line_number = p_line_number or line_number = '-99')
AND run_id = p_sequence_id
AND variable_value is not null;
SELECT localized_str translated_value
FROM cz_localized_texts
WHERE orig_sys_ref LIKE 'OKC:VARIABLEMODELOPTION:-99:%:' || p_variable_id || ':' || p_variable_value
AND LANGUAGE = USERENV('LANG');
SELECT ltxt.localized_str question_value
FROM cz_config_items_v config,
cz_ps_nodes psn,
cz_localized_texts ltxt
WHERE config.config_hdr_id = l_config_header_id
AND config.config_rev_nbr = l_config_rev_nbr
AND config.ps_node_id = psn.ps_node_id
AND psn.intl_text_id = ltxt.intl_text_id
AND ltxt.LANGUAGE = USERENV('LANG')
AND SUBSTR(config.orig_sys_ref, INSTR(config.orig_sys_ref,':',-1,2)+1,
(INSTR(config.orig_sys_ref,':',-1,1) - (INSTR(config.orig_sys_ref,':',-1,2)+1))) = to_char(p_object_code);
SELECT application_table_name,
value_column_name,
id_column_name,
additional_where_clause
FROM fnd_flex_validation_tables
WHERE flex_value_set_id = p_value_set_id;
select question_datatype from okc_xprt_questions_b
where to_char(question_id) = p_object_code;
SELECT to_char(Item_num_val)
FROM cz_config_items_v config
WHERE config.config_hdr_id = l_config_header_id
AND config.config_rev_nbr = l_config_rev_nbr
AND config.orig_sys_ref LIKE 'OKC:TEMPLATEMODELFEATURE:%:' || p_object_code;
l_sql_stmt := 'SELECT '||l_name_col||' , '||l_id_col||
' FROM '||l_table_name||' '||
l_additional_where_clause ;
in rules update/view mode. This function has been overloaded to
handle value display for the condition values row
+====================================================================*/
FUNCTION get_value_display (
p_rule_condition_id IN NUMBER,
p_object_value_code IN VARCHAR2)
RETURN VARCHAR2 IS
CURSOR c1 IS
SELECT RuleCond.Object_value_code,
RuleCond.Object_value_type,
fvs.flex_value_set_id OBJECT_VALUE_SET_ID,
fvs.validation_type,
fvs.longlist_flag
FROM OKC_XPRT_RULE_CONDITIONS RuleCond,
fnd_flex_value_sets fvs
where rtrim(RuleCond.OBJECT_VALUE_SET_NAME) = fvs.flex_value_set_name(+)
and rule_condition_id=p_rule_condition_id;
for rules update/view pages
+====================================================================*/
FUNCTION get_value_desc (
p_rule_condition_id IN NUMBER,
p_object_value_code IN VARCHAR2)
RETURN VARCHAR2 IS
CURSOR c1 IS
SELECT RuleCond.Object_value_code,
RuleCond.Object_value_type,
fvs.flex_value_set_id OBJECT_VALUE_SET_ID,
fvs.validation_type,
fvs.longlist_flag
FROM OKC_XPRT_RULE_CONDITIONS RuleCond,
fnd_flex_value_sets fvs
where RuleCond.OBJECT_VALUE_SET_NAME = fvs.flex_value_set_name(+)
and rule_condition_id=p_rule_condition_id;
SELECT article_description
FROM okc_articles_v art
WHERE article_id=to_number(p_object_value_code)
AND article_version_number=
(select max(article_version_number)
from okc_article_versions
where article_id= Art.article_id);*/
SELECT article_description
FROM okc_article_versions art
WHERE article_id=to_number(p_object_value_code)
AND article_version_number=
(select max(article_version_number)
from okc_article_versions
where article_id= to_number(p_object_value_code));
SELECT description
FROM okc_xprt_questions_vl
WHERE question_id=TO_NUMBER(p_object_value_code)
AND question_type= decode(p_object_value_type,'CONSTANT','C','Q');
SELECT description
FROM okc_bus_variables_v
WHERE variable_code=p_object_value_code;
select value.flex_value
--,value.description
from fnd_flex_values_vl value
where value.FLEX_VALUE_SET_ID = p_object_value_set_id
and enabled_flag='Y'
and trunc(sysdate) between nvl(trunc(start_date_active),trunc(sysdate)) and nvl(trunc(end_date_active),trunc(sysdate))
and value.flex_value_id = to_number(p_object_value_code);
select
val_tab.application_table_name,
val_tab.value_column_name,
val_tab.id_column_name,
val_tab.additional_where_clause,
val_tab.meaning_column_name
from fnd_flex_validation_tables val_tab
where val_tab.FLEX_VALUE_SET_ID = p_object_value_set_id ;
l_select_stmt VARCHAR2(2000);
l_select_stmt := ' SELECT ' || NVL(c2rec.id_column_name,null) ||' as Flex_value_id,'||
NVL(c2rec.value_column_name,'null') ||' as Flex_value,'||
NVL(c2rec.meaning_column_name,'null') ||' as Flex_meaning FROM '||
c2rec.application_table_name ;
l_select_stmt := l_select_stmt||' WHERE';
l_select_stmt :=l_select_stmt||' '|| c2rec.additional_where_clause;
l_select_stmt := 'SELECT FLEX_VALUE FROM ('||l_select_stmt||') WHERE FLEX_VALUE_ID = :1';
DBMS_SQL.PARSE(value_cursor_id,l_select_stmt,DBMS_SQL.NATIVE);
select value.description
from fnd_flex_values_vl value
where value.FLEX_VALUE_SET_ID = p_object_value_set_id
and enabled_flag='Y'
and trunc(sysdate) between nvl(trunc(start_date_active),trunc(sysdate)) and nvl(trunc(end_date_active),trunc(sysdate))
and value.flex_value_id = to_number(p_object_value_code);
select
val_tab.application_table_name,
val_tab.value_column_name,
val_tab.id_column_name,
val_tab.additional_where_clause,
val_tab.meaning_column_name
from fnd_flex_validation_tables val_tab
where val_tab.FLEX_VALUE_SET_ID = p_object_value_set_id ;
l_select_stmt VARCHAR2(2000);
l_select_stmt := ' SELECT ' || NVL(c2rec.id_column_name,null) ||' as Flex_value_id,'||
NVL(c2rec.value_column_name,'null') ||' as Flex_value,'||
NVL(c2rec.meaning_column_name,'null') ||' as Flex_meaning FROM '||
c2rec.application_table_name ;
l_select_stmt := l_select_stmt||' WHERE';
l_select_stmt :=l_select_stmt||' '|| c2rec.additional_where_clause;
l_select_stmt := 'SELECT FLEX_MEANING FROM ('||l_select_stmt||') WHERE FLEX_VALUE_ID = :1';
DBMS_SQL.PARSE(value_cursor_id,l_select_stmt,DBMS_SQL.NATIVE);
UPDATE okc_xprt_rule_hdrs_all
SET status_code = DECODE (p_calling_mode, 'PUBLISH', 'ACTIVE', 'DISABLE', 'INACTIVE', 'SYNC', 'ACTIVE'),
published_flag = 'Y',
last_updated_by = FND_GLOBAL.USER_ID,
last_update_date = SYSDATE,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID;
SELECT rule_id
FROM okc_xprt_rule_hdrs_all
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID ;
SELECT r.rule_id
FROM okc_xprt_template_rules r,
okc_xprt_rule_hdrs_all h
WHERE r.rule_id = h.rule_id
AND h.status_code = 'ACTIVE'
AND r.template_id = p_template_id
UNION ALL
SELECT h.rule_id
FROM okc_terms_templates_all t,
okc_xprt_rule_hdrs_all h
WHERE t.org_id = h.org_id
AND t.intent = h.intent
AND h.status_code = 'ACTIVE'
AND NVL(h.org_wide_flag,'N') = 'Y'
AND t.template_id = p_template_id;
SELECT title,
problem_short_desc,
problem_details,
suggestion
FROM okc_qa_errors_t
WHERE sequence_id = p_sequence_id
AND error_severity = 'E';
SELECT decode(article_status,'ON_HOLD','N','EXPIRED','N','Y') valid_yn
FROM okc_article_versions Art
WHERE article_id= p_object_id
AND article_version_number=
(select max(article_version_number)
from okc_article_versions
where article_id= Art.article_id);
SELECT decode(disabled_flag,'Y','N','Y') disabled_yn
FROM okc_xprt_questions_b
WHERE question_id= p_object_id;
SELECT value_set_id
FROM OKC_BUS_VARIABLES_V
WHERE variable_code = p_object_code
AND variable_type = 'U';
Used in Rules Update/duplicate flow
Parameters : p_rule_condition_id : Condition Id
p_object_code : condition object_code
+====================================================================*/
FUNCTION is_value_valid (
p_object_code IN VARCHAR2,
p_rule_condition_id IN NUMBER)
RETURN VARCHAR2 IS
CURSOR c1 IS
SELECT RuleCond.Object_value_type
FROM OKC_XPRT_RULE_CONDITIONS RuleCond
where rule_condition_id=p_rule_condition_id;
SELECT name
FROM cz_model_usages
WHERE model_usage_id = -1 ; -- seeded for Any Usage
SELECT 'OKC:TEMPLATEMODEL:'||org_id||':'||intent||':'||template_id
FROM okc_terms_templates_all
WHERE template_id = p_template_id;
Function : ok_to_delete_clause
This function will return 'Y' for a article id if it is not used in
rule condition or outcome.
If used, it returns 'N'.
*/
FUNCTION ok_to_delete_clause
(
p_article_id IN NUMBER
) RETURN VARCHAR2 IS
CURSOR csr_clause_exists IS
-- All Clauses from Conditions
SELECT 'N'
FROM okc_xprt_rule_cond_vals v,
okc_xprt_rule_conditions c
WHERE v.rule_condition_id = c.rule_condition_id
AND c.object_type = 'CLAUSE'
AND v.object_value_code = to_char(p_article_id) -- Added to_char function. Fix for Bug 4063007
UNION ALL
-- All Clauses from Outcome
SELECT 'N'
FROM okc_xprt_rule_outcomes o
WHERE o.object_type = 'CLAUSE'
AND o.object_value_id = p_article_id ; -- Removed to_char which is not needed here
l_delete_flag VARCHAR2(1);
FETCH csr_clause_exists INTO l_delete_flag;
l_delete_flag := 'Y' ;
RETURN l_delete_flag;
END ok_to_delete_clause;
SELECT fnd.meaning qa_name,
nvl(qa.severity_flag,G_QA_STS_WARNING) severity_flag ,
decode(fnd.enabled_flag,'N','N','Y',decode(qa.enable_qa_yn,'N','N','Y'),'Y') perform_qa
FROM FND_LOOKUPS FND,
OKC_DOC_QA_LISTS QA
WHERE QA.DOCUMENT_TYPE(+)=p_document_type
AND QA.QA_CODE(+) = FND.LOOKUP_CODE
AND Fnd.LOOKUP_TYPE='OKC_TERM_QA_LIST'
AND Fnd.lookup_code = p_qa_code;
SELECT config_header_id
FROM okc_template_usages
WHERE document_id = p_document_id
AND document_type = p_document_type
AND template_id = p_template_id;
SELECT (nvl(contract_expert_enabled, 'N'))
FROM okc_terms_templates_all
WHERE template_id = p_template_id;
SELECT document_type_class,
NVL(variable_resolution_am,'X')
FROM okc_bus_doc_types_b
WHERE document_type = p_document_type;
PROCEDURE get_expert_selections(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_document_id IN NUMBER,
p_document_type IN VARCHAR2,
p_config_header_id IN NUMBER,
p_config_rev_nbr IN NUMBER,
x_expert_clauses_tbl OUT NOCOPY expert_articles_tbl_type,
x_expert_deviations_tbl OUT NOCOPY dev_rule_tbl_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'get_expert_selections';
SELECT DECODE(SUBSTR(orig_sys_ref,5,3),'CLA',NULL,'VAR',item_val) line_number,
SUBSTR(orig_sys_ref,INSTR(orig_sys_ref,':',-1,1)+1) id,
SUBSTR(orig_sys_ref,5,3) type,
parent_config_item_id
FROM cz_config_items_v
WHERE config_hdr_id = p_config_header_id
AND config_rev_nbr = p_config_rev_nbr
AND (orig_sys_ref LIKE 'OKC:CLAUSEMODELOPTION:%' OR
orig_sys_ref LIKE 'OKC:VARIABLEMODELDEVFEATURE:%');
SELECT NVL(PROVISION_ALLOWED_YN,'Y')
FROM OKC_BUS_DOC_TYPES_B
WHERE DOCUMENT_TYPE=p_document_type;
SELECT NVL(PROVISION_YN,'N')
FROM OKC_ARTICLE_VERSIONS VERS
WHERE vers.article_id = p_article_id;
SELECT NVL(item_val,'-99') line_number
FROM cz_config_items_v
WHERE config_hdr_id = p_config_header_id
AND config_rev_nbr = p_config_rev_nbr
AND parent_config_item_id = l_parent_config_item_id
AND orig_sys_ref LIKE 'OKC:VARIABLEMODELTEXTFEATURE:%LINE_NUMBER%';
-- SELECT ARTICLES FROM CONFIGURATION
--
OPEN csr_art_provision (p_article_id => l_get_xprt_det_from_cz_rec.id);
END get_expert_selections;
SELECT DISTINCT object_type type,
object_code code
FROM okc_xprt_rule_conditions
WHERE rule_id = p_dev_rule_id
AND object_type IN ('QUESTION','VARIABLE')
UNION ALL
SELECT DISTINCT object_value_type type,
object_value_code code
FROM okc_xprt_rule_conditions
WHERE rule_id = p_dev_rule_id
AND object_value_type IN ('QUESTION','VARIABLE');
SELECT variable_value variable_value
FROM okc_xprt_deviations_t
WHERE variable_code = p_variable_id
AND run_id = p_sequence_id;
SELECT localized_str translated_value
FROM cz_localized_texts
WHERE orig_sys_ref LIKE 'OKC:VARIABLEMODELOPTION:-99:%:' || p_variable_id || ':' || p_variable_value
AND LANGUAGE = USERENV('LANG');
SELECT ltxt.localized_str question_value
FROM cz_config_items_v config,
cz_ps_nodes psn,
cz_localized_texts ltxt
WHERE config.config_hdr_id = p_config_header_id
AND config.config_rev_nbr = p_config_rev_nbr
AND config.ps_node_id = psn.ps_node_id
AND psn.intl_text_id = ltxt.intl_text_id
AND ltxt.LANGUAGE = USERENV('LANG')
AND SUBSTR(config.orig_sys_ref, INSTR(config.orig_sys_ref,':',-1,2)+1,
(INSTR(config.orig_sys_ref,':',-1,1) - (INSTR(config.orig_sys_ref,':',-1,2)+1))) = to_char(p_question_id);
/* SELECT ps_node_name question_value
FROM cz_config_items_v
WHERE config_hdr_id = p_config_header_id
AND config_rev_nbr = p_config_rev_nbr
AND SUBSTR(orig_sys_ref, INSTR(orig_sys_ref,':',-1,2)+1,
(INSTR(orig_sys_ref,':',-1,1) - (INSTR(orig_sys_ref,':',-1,2)+1))) = to_char(p_question_id);
SELECT article_title,article_description
FROM okc_articles_all a,okc_article_versions ver
WHERE a.article_id = b_article_id
AND a.article_id = ver.article_id
AND ver.article_version_id = b_article_version_id;
select sav_sae_id, article_version_id from okc_k_articles_b
where document_id = p_document_id
and document_type = p_document_type
and ((sav_sae_id = p_article_id) or (ref_article_id = p_article_id))
and source_flag = 'R'
and rownum < 2;
SELECT nvl(default_section,'UNASSIGNED') scn_code
FROM OKC_ARTICLE_VERSIONS VERS
WHERE vers.article_id = b_article_id
AND vers.article_version_id = b_article_version_id;
SELECT NVL(t.xprt_scn_code,'UNASSIGNED')
FROM okc_template_usages u,
okc_terms_templates_all t
WHERE u.template_id = t.template_id
AND u.document_type = p_document_type
AND u.document_id = p_document_id ;
SELECT heading FROM OKC_SECTIONS_B
WHERE scn_code = b_scn_code
AND rownum=1 ;
SELECT scn.heading
FROM OKC_SECTIONS_B scn,
OKC_K_ARTICLES_B art
WHERE art.document_type=p_document_type
AND art.document_id =p_document_id
AND art.article_version_id = b_article_version_id --Bug#4757731 replaced art.sav_sae_id with art.article_version_id
AND art.scn_id = scn.id
AND rownum=1 ;
SELECT rule_name,
rule_description
FROM okc_xprt_rule_hdrs_all
WHERE rule_id = p_rule_id;
SELECT concatenated_segments,
description
FROM mtl_system_items_vl
WHERE inventory_item_id = p_item_id
AND organization_id = p_org_id;
SELECT item_id,
org_id
FROM okc_xprt_deviations_t
WHERE line_number = p_line_number
and run_id = p_sequence_id;
l_rule_var_values_tbl.DELETE(i);
l_rule_qst_values_tbl.DELETE(i);
-- Insert header variables into okc_terms_deviations_t
INSERT INTO OKC_TERMS_DEVIATIONS_T
(
SEQUENCE_ID,
DOCUMENT_TYPE,
DOCUMENT_ID,
DEVIATION_TYPE,
RULE_ID,
DEVIATION_CODE,
DEVIATION_CODE_MEANING,
LINE_NUMBER,
ITEM_NAME,
ITEM_DESCRIPTION,
OBJECT_TYPE,
OBJECT_CODE,
OBJECT_DESCRIPTION,
DOCUMENT_VALUE,
CONFIG_HEADER_ID,
CONFIG_REVISION_NUMBER
)
VALUES
(
p_sequence_id, --SEQUENCE_ID,
p_document_type, --DOCUMENT_TYPE,
p_document_id, --DOCUMENT_ID,
'P', --DEVIATION_TYPE,
l_rule_var_values_tbl(i).rule_id, --RULE_ID,
l_rule_name, --DEVIATION_CODE,
l_rule_description, --DEVIATION_CODE_MEANING,
l_rule_var_values_tbl(i).line_number, --LINE_NUMBER,
-- Begin: Fix for bug 5182270.If line_number is -99 then putting NULL for Item and Item Desc.So that
-- in VO query Distinct Clause can remove duplicate header level rules
DECODE(l_rule_var_values_tbl(i).line_number,'-99',NULL,l_item_name), --ITEM_NAME,
DECODE(l_rule_var_values_tbl(i).line_number,'-99',NULL,l_item_description), --ITEM_DESCRIPTION,
'VARIABLE', --OBJECT_TYPE,
l_rule_var_values_tbl(i).variable_id, --OBJECT_VALUE,
null, --OBJECT_DESCRIPTION,
DECODE(l_rule_var_values_tbl(i).line_number,'-99',NULL,l_rule_var_values_tbl(i).variable_value), --DOCUMENT_VALUE,
-- End: Fix for bug 5182270
p_config_header_id, --CONFIG_HEADER_ID,
p_config_rev_nbr --CONFIG_REVISION_NUMBER
);
-- Insert header variables into okc_terms_deviations_t
INSERT INTO OKC_TERMS_DEVIATIONS_T
(
SEQUENCE_ID,
DOCUMENT_TYPE,
DOCUMENT_ID,
DEVIATION_TYPE,
RULE_ID,
DEVIATION_CODE,
DEVIATION_CODE_MEANING,
LINE_NUMBER,
ITEM_NAME,
ITEM_DESCRIPTION,
OBJECT_TYPE,
OBJECT_CODE,
OBJECT_DESCRIPTION,
DOCUMENT_VALUE,
CONFIG_HEADER_ID,
CONFIG_REVISION_NUMBER
)
VALUES
(
p_sequence_id, --SEQUENCE_ID,
p_document_type, --DOCUMENT_TYPE,
p_document_id, --DOCUMENT_ID,
'P', --DEVIATION_TYPE,
l_rule_qst_values_tbl(i).rule_id, --RULE_ID,
l_rule_name, --DEVIATION_CODE,
l_rule_description, --DEVIATION_CODE_MEANING,
--NULL , --LINE_NUMBER,
'-99', -- l_rule_qst_values_tbl(i).line_number, --LINE_NUMBER,
null, --ITEM_NAME,
null, --ITEM_DESCRIPTION,
'QUESTION', --OBJECT_TYPE,
l_rule_qst_values_tbl(i).question_id, --OBJECT_VALUE,
null, --OBJECT_DESCRIPTION,
l_rule_qst_values_tbl(i).question_value, --DOCUMENT_VALUE,
p_config_header_id, --CONFIG_HEADER_ID,
p_config_rev_nbr --CONFIG_REVISION_NUMBER
);
-- Insert Clause details into okc_terms_deviations_t
INSERT INTO OKC_TERMS_DEVIATIONS_T
(
SEQUENCE_ID,
DOCUMENT_TYPE,
DOCUMENT_ID,
DEVIATION_TYPE,
DOC_ARTICLE_ID,
DOC_ARTICLE_VERSION_ID,
ARTICLE_TITLE,
ARTICLE_DESCRIPTION,
SECTION_HEADING,
CONFIG_HEADER_ID,
CONFIG_REVISION_NUMBER
)
VALUES
(
p_sequence_id, --SEQUENCE_ID,
p_document_type, --DOCUMENT_TYPE,
p_document_id, --DOCUMENT_ID,
'C', --DEVIATION_TYPE,
p_clause_tbl(i), --DOC_ARTICLE_ID,
NULL, --DOC_ARTICLE_VERSION_ID
l_article_title, --ARTICLE_TITLE,
l_article_description, --ARTICLE_DESCRIPTION,
l_scn_heading, --SECTION_HEADING,
p_config_header_id, --CONFIG_HEADER_ID,
p_config_rev_nbr --CONFIG_REVISION_NUMBER
);
SELECT OKC_TERMS_DEVIATIONS_S1.nextval INTO p_sequence_id from DUAL;
-- Step 2: Insert values into okc_xprt_deviations_gt
IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
G_MODULE||l_api_name,
'120: Step 2: Insert values into okc_xprt_deviations_gt');
-- Insert header variables into okc_xprt_deviations_gt
INSERT INTO OKC_XPRT_DEVIATIONS_T
(
RUN_ID,
LINE_NUMBER,
VARIABLE_CODE,
VARIABLE_VALUE,
ITEM_ID,
ORG_ID,
CREATION_DATE
)
VALUES
(
p_sequence_id, -- RUN_ID
'-99', -- LINE_NUMBER
l_hdr_var_value_tbl(i).variable_code, -- VARIABLE_CODE
l_hdr_var_value_tbl(i).variable_value_id, -- VARIABLE_VALUE
NULL, -- ITEM_ID
NULL, -- ORG_ID
SYSDATE
);
-- Insert line variables into okc_xprt_deviations_t
INSERT INTO OKC_XPRT_DEVIATIONS_T
(
RUN_ID,
LINE_NUMBER,
VARIABLE_CODE,
VARIABLE_VALUE,
ITEM_ID,
ORG_ID,
CREATION_DATE
)
VALUES
(
p_sequence_id,
l_line_sysvar_value_tbl(i).line_number, -- LINE_NUMBER
l_line_sysvar_value_tbl(i).variable_code, -- VARIABLE_CODE
l_line_sysvar_value_tbl(i).variable_value,-- VARIABLE_VALUE
l_line_sysvar_value_tbl(i).item_id, -- ITEM_ID
l_line_sysvar_value_tbl(i).org_id, -- ORG_ID
SYSDATE
);
get_expert_selections(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_document_id => p_document_id,
p_document_type => p_document_type,
p_config_header_id => p_config_header_id,
p_config_rev_nbr => p_config_rev_nbr,
x_expert_clauses_tbl => l_expert_clauses_tbl,
x_expert_deviations_tbl => l_expert_deviations_tbl,
x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count);
-- Step 7: Insert Clauses and Terms deviations into okc_terms_deviations_t
IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
G_MODULE||l_api_name,
'170: Step 7: Insert Clauses and Terms deviations into okc_terms_deviations_t');
SELECT 'X'
FROM okc_xprt_question_orders
WHERE question_rule_status = 'ACTIVE'
AND template_id = p_template_id;
SELECT u.config_header_id,
u.config_revision_number,
t.template_id,
t.contract_expert_enabled
FROM okc_template_usages u,
okc_terms_templates_all t
WHERE t.template_id = u.template_id
AND u.document_type= p_document_type
AND u.document_id = p_document_id;
--If Clause Selection has Rules and mode is AUTHORING
--Add QA message and Return
IF check_rule_type_has_questions(l_template_id,'CLAUSE_SELECTION') = 'Y' AND (p_bv_mode = 'AUTH')
THEN
--
-- Add QA Message for expert needs to be run
--
l_qa_tbl_index := x_qa_result_tbl.COUNT + 1;
If one or more questions on a existing configuration were deleted due to the rule
getting disabled, when we run batch validate in back end, CZ returns valid_configuration
as false.
In 'DEV' mode, we need to continue processing the expert clauses from CZ even if the
valid_configuration is false
*/
IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
THEN
RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
-- delete config before return
-- delete config before return
--
-- Delete BV Configuration from CZ.
--
OKC_XPRT_CZ_INT_PVT.delete_configuration(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_config_header_id => l_new_config_header_id,
p_config_rev_nbr => l_new_config_rev_nbr,
x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count);
-- delete config before return
--
-- Delete BV Configuration from CZ.
--
OKC_XPRT_CZ_INT_PVT.delete_configuration(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_config_header_id => l_new_config_header_id,
p_config_rev_nbr => l_new_config_rev_nbr,
x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count);
-- delete config before return
--
-- Delete BV Configuration from CZ.
--
OKC_XPRT_CZ_INT_PVT.delete_configuration(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_config_header_id => l_new_config_header_id,
p_config_rev_nbr => l_new_config_rev_nbr,
x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count);
-- delete config before return
--
-- Delete BV Configuration from CZ.
--
OKC_XPRT_CZ_INT_PVT.delete_configuration(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_config_header_id => l_new_config_header_id,
p_config_rev_nbr => l_new_config_rev_nbr,
x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count);
IF (p_bv_mode = 'DEV') AND (LOWER(l_valid_config) <> L_VALID) AND (check_rule_type_has_questions(l_template_id,'CLAUSE_SELECTION') = 'Y' AND check_rule_type_has_questions(l_template_id,'TERM_DEVIATION') = 'Y')
THEN
-- delete config before return
--
-- Delete BV Configuration from CZ.
--
OKC_XPRT_CZ_INT_PVT.delete_configuration(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_config_header_id => l_new_config_header_id,
p_config_rev_nbr => l_new_config_rev_nbr,
x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count);
IF check_rule_type_has_questions(l_template_id,'CLAUSE_SELECTION') <> 'Y' OR has_unanswered_questions(p_document_type, p_document_id,'CLAUSE_SELECTION') <> 'Y' THEN
process_qa_result(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_document_id => p_document_id,
p_document_type => p_document_type,
p_config_header_id => l_new_config_header_id,
p_config_rev_nbr => l_new_config_rev_nbr,
x_qa_result_tbl => x_qa_result_tbl,
x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count);
--SELECT OKC_TERMS_DEVIATIONS_S1.nextval INTO l_sequence_id from DUAL;
update_ce_config(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_document_id => p_document_id,
p_document_type => p_document_type,
p_config_header_id => l_new_config_header_id,
p_config_rev_nbr => l_new_config_rev_nbr,
p_doc_update_mode => 'BV',
x_count_articles_dropped => l_count_articles_dropped,
x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count);
--SELECT OKC_TERMS_DEVIATIONS_S1.nextval INTO l_sequence_id from DUAL;
OKC_XPRT_CZ_INT_PVT.delete_configuration(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_config_header_id => l_new_config_header_id,
p_config_rev_nbr => l_new_config_rev_nbr,
x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count);
SELECT 'X'
FROM okc_template_usages
WHERE document_type = p_document_type
AND document_id = p_document_id
AND template_id = p_template_id;
SELECT u.config_header_id,
u.config_revision_number,
t.template_id,
t.contract_expert_enabled
FROM okc_template_usages u,
okc_terms_templates_all t
WHERE t.template_id = u.template_id
AND u.document_type= p_document_type
AND u.document_id = p_document_id
AND t.template_id = p_template_id;
p_rule_type rule type either 'CLAUSE_SELECTION' OR 'TERM_DEVIATION'
+========================================================================================*/
FUNCTION check_rule_type_has_questions (
p_template_id IN NUMBER,
p_rule_type IN VARCHAR2)
RETURN VARCHAR2 IS
CURSOR csr_rules(p_org_id NUMBER, p_intent VARCHAR2) IS
SELECT tr.rule_id
FROM okc_xprt_template_rules tr,
okc_xprt_rule_hdrs_all rhdr
WHERE tr.rule_id = rhdr.rule_id
AND tr.template_id = p_template_id
AND rhdr.rule_type = p_rule_type
AND rhdr.status_code IN ('ACTIVE','PENDING_PUB')
UNION ALL
SELECT r.rule_id
FROM OKC_TERMS_TEMPLATES_ALL t,
okc_xprt_rule_hdrs_all r
WHERE t.org_id = p_org_id
AND t.intent = p_intent
AND r.rule_type = p_rule_type
AND r.status_code IN ('ACTIVE','PENDING_PUB')
AND NVL(r.org_wide_flag,'N') = 'Y'
AND t.template_id = p_template_id;
SELECT object_code question_id
FROM okc_xprt_rule_conditions cond
WHERE cond.rule_id = p_rule_id
AND cond.object_value_type ='QUESTION'
UNION ALL
SELECT object_code question_id
FROM okc_xprt_rule_conditions cond
WHERE cond.rule_id = p_rule_id
AND cond.object_type ='QUESTION'
UNION ALL
SELECT object_value_id||'' question_id
FROM okc_xprt_rule_outcomes xro
WHERE xro.rule_id = p_rule_id
AND xro.object_type='QUESTION';
SELECT org_id, intent
FROM okc_terms_templates_all
WHERE template_id = p_template_id;
SELECT 'x' into l_dummy
FROM okc_xprt_question_orders
WHERE question_rule_status = 'ACTIVE'
AND template_id = p_template_id
AND question_id = rec_question.question_id;
select nvl(config_status, 0)
from cz_config_hdrs_v chv, okc_template_usages otu
where chv.config_hdr_id = otu.config_header_id
and chv.config_rev_nbr = otu.config_revision_number
and otu.document_type = p_document_type
and otu.document_id = p_document_id;
select u.template_id, a.contract_expert_enabled, u.contract_source_code
from okc_template_usages u, okc_terms_templates_all a
where u.template_id = a.template_id
and u.document_type = p_document_type
and u.document_id = p_document_id;