[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 'X'
from okc_xprt_rule_hdrs_all
where org_wide_flag = 'Y'
and line_level_flag = 'Y'
and org_id = p_org_id;
select 'X'
from okc_xprt_rule_hdrs_all rhdr,
okc_template_usages tuse,
okc_xprt_template_rules trule
where tuse.document_id = p_doc_id
and tuse.document_type = p_doc_type
and tuse.template_id = trule.template_id
and trule.rule_id = rhdr.rule_id
and rhdr.line_level_flag ='Y';
SELECT 'USER$' || var.variable_code variable_code,
NVL(var.variable_value_id,var.variable_value) variable_value -- Added NVL for Bug 5233775
FROM okc_k_art_variables var,
okc_k_articles_b art, -- Added for Bug 4728565
okc_bus_variables_b bvar
WHERE art.id = var.cat_id -- Added for Bug 4728565
AND art.document_id = p_doc_id -- Added for Bug 4728565
AND art.document_type = p_doc_type -- Added for Bug 4728565
AND var.variable_code = bvar.variable_code
AND bvar.variable_source = 'M'
AND bvar.clm_source IS NULL --CLM Changes
AND var.variable_code IN ( SELECT rcon.object_code variable_code -- LHS of Condition from Template rule
FROM okc_xprt_rule_hdrs_all rhdr,
okc_xprt_rule_conditions rcon,
okc_template_usages tuse,
okc_xprt_template_rules trule
WHERE tuse.document_id = p_doc_id
AND tuse.document_type = p_doc_type
AND tuse.template_id = trule.template_id
AND trule.rule_id = rhdr.rule_id
AND rhdr.rule_id = rcon.rule_id
AND rcon.object_type = 'VARIABLE'
AND rhdr.status_code <> 'DRAFT'
AND SUBSTR(rcon.object_code,1,3) <> 'OKC'
GROUP BY rcon.object_code
UNION
SELECT rcon.object_value_code variable_code -- RHS of Condition from Template rule
FROM okc_xprt_rule_hdrs_all rhdr,
okc_xprt_rule_conditions rcon,
okc_template_usages tuse,
okc_xprt_template_rules trule
WHERE tuse.document_id = p_doc_id
AND tuse.document_type = p_doc_type
AND tuse.template_id = trule.template_id
AND trule.rule_id = rhdr.rule_id
AND rhdr.rule_id = rcon.rule_id
AND rcon.object_value_type = 'VARIABLE'
AND rhdr.status_code <> 'DRAFT'
AND SUBSTR(rcon.object_value_code,1,3) <> 'OKC'
GROUP BY rcon.object_value_code
UNION
SELECT rcon.object_code variable_code -- LHS of Condition from Global Rule
FROM okc_xprt_rule_hdrs_all rhdr,
okc_xprt_rule_conditions rcon
WHERE rhdr.rule_id = rcon.rule_id
AND rhdr.org_id = p_org_id
AND rhdr.intent = p_intent
AND rhdr.org_wide_flag = 'Y'
AND rcon.object_type = 'VARIABLE'
AND rhdr.status_code <> 'DRAFT'
AND SUBSTR(rcon.object_code,1,3) <> 'OKC'
GROUP BY rcon.object_code
UNION
SELECT rcon.object_value_code variable_code -- RHS of Condition from Global Rule
FROM okc_xprt_rule_hdrs_all rhdr,
okc_xprt_rule_conditions rcon
WHERE rhdr.rule_id = rcon.rule_id
AND rhdr.org_id = p_org_id
AND rhdr.intent = p_intent
AND rhdr.org_wide_flag = 'Y'
AND rcon.object_value_type = 'VARIABLE'
AND rhdr.status_code <> 'DRAFT'
AND SUBSTR(rcon.object_value_code,1,3) <> 'OKC'
GROUP BY rcon.object_value_code);
SELECT 'CONSTANT$' || v.object_value_code constant_id, --
q.default_value
FROM okc_xprt_rule_cond_vals v,
okc_xprt_rule_conditions c,
okc_xprt_rule_hdrs_all r,
okc_xprt_questions_b q
WHERE v.rule_condition_id = c.rule_condition_id
AND c.rule_id = r.rule_id
AND to_char(q.question_id) = v.object_value_code
AND c.object_value_type = 'CONSTANT'
AND r.intent = p_intent
AND r.status_code <> 'DRAFT'
GROUP BY v.object_value_code, q.default_value;
SELECT var.variable_code variable_code, --Removed USER$ to resolve Rule firing for UDV with Procedures
procedure_name procedure_name
FROM okc_bus_variables_b var
WHERE var.variable_source = 'P'
AND var.variable_code IN
(SELECT distinct rcon.object_code variable_code -- LHS of Condition from Template rule
FROM okc_xprt_rule_hdrs_all rhdr,
okc_xprt_rule_conditions rcon,
okc_template_usages tuse,
okc_xprt_template_rules trule
WHERE tuse.document_id = p_doc_id
AND tuse.document_type = p_doc_type
AND tuse.template_id = trule.template_id
AND trule.rule_id = rhdr.rule_id
AND rhdr.rule_id = rcon.rule_id
AND rcon.object_type = 'VARIABLE'
AND rhdr.status_code <> 'DRAFT'
AND SUBSTR(rcon.object_code,1,3) <> 'OKC'
GROUP BY rcon.object_code
UNION
SELECT distinct rcon.object_value_code variable_code -- RHS of Condition from Template rule
FROM okc_xprt_rule_hdrs_all rhdr,
okc_xprt_rule_conditions rcon,
okc_template_usages tuse,
okc_xprt_template_rules trule
WHERE tuse.document_id = p_doc_id
AND tuse.document_type = p_doc_type
AND tuse.template_id = trule.template_id
AND trule.rule_id = rhdr.rule_id
AND rhdr.rule_id = rcon.rule_id
AND rcon.object_value_type = 'VARIABLE'
AND rhdr.status_code <> 'DRAFT'
AND SUBSTR(rcon.object_value_code,1,3) <> 'OKC'
GROUP BY rcon.object_value_code
UNION
SELECT distinct rcon.object_code variable_code -- LHS of Condition from Global Rule
FROM okc_xprt_rule_hdrs_all rhdr,
okc_xprt_rule_conditions rcon
WHERE rhdr.rule_id = rcon.rule_id
AND rhdr.org_id = p_org_id
AND rhdr.intent = p_intent
AND rhdr.org_wide_flag = 'Y'
AND rcon.object_type = 'VARIABLE'
AND rhdr.status_code <> 'DRAFT'
AND SUBSTR(rcon.object_code,1,3) <> 'OKC'
GROUP BY rcon.object_code
UNION
SELECT distinct rcon.object_value_code variable_code -- RHS of Condition from Global Rule
FROM okc_xprt_rule_hdrs_all rhdr,
okc_xprt_rule_conditions rcon
WHERE rhdr.rule_id = rcon.rule_id
AND rhdr.org_id = p_org_id
AND rhdr.intent = p_intent
AND rhdr.org_wide_flag = 'Y'
AND rcon.object_value_type = 'VARIABLE'
AND rhdr.status_code <> 'DRAFT'
AND SUBSTR(rcon.object_value_code,1,3) <> 'OKC'
GROUP BY rcon.object_value_code);
SELECT distinct procedure_name procedure_name
FROM okc_xprt_deviations_t
WHERE run_id = p_sequence_id;
SELECT distinct variable_code variable_code
FROM okc_xprt_deviations_t
WHERE run_id = p_sequence_id
AND procedure_name = p_procedure_name;
SELECT OKC_TERMS_DEVIATIONS_S1.nextval INTO l_sequence_id from DUAL;
INSERT INTO OKC_XPRT_DEVIATIONS_T
(
RUN_ID,
LINE_NUMBER,
VARIABLE_CODE,
VARIABLE_VALUE,
ITEM_ID,
ORG_ID,
CREATION_DATE,
PROCEDURE_NAME
)
VALUES
(
l_sequence_id, -- RUN_ID
NULL, -- LINE_NUMBER
csr_get_udv_with_proc_rec.variable_code, -- VARIABLE_CODE
NULL, -- VARIABLE_VALUE
NULL, -- ITEM_ID
NULL, -- ORG_ID
NULL, -- CREATION_DATE
csr_get_udv_with_proc_rec.procedure_name -- PROCEDURE_NAME
);
variableCode_tbl.DELETE;
l_udf_var_value_tbl.DELETE(i);
SELECT intent
FROM okc_bus_doc_types_b b
WHERE b.document_type = p_doc_type;
SELECT t.org_id org_id
FROM okc_template_usages u,
okc_terms_templates_all t
WHERE u.document_type = p_doc_type
AND u.document_id = p_doc_id
AND u.template_id = t.template_id;
SELECT DISTINCT OBJECT_VALUE_CODE
FROM okc_xprt_rule_cond_vals val
WHERE InStr(OBJECT_VALUE_CODE,p_variable_value_id||'_',1,1) = 1
AND OBJECT_VALUE_CODE <> p_variable_value_id
AND RULE_CONDITION_ID IN (SELECT rule_condition_id
FROM OKC_XPRT_RULE_CONDITIONS
WHERE OBJECT_CODE = 'OKC$S_ITEM'
AND rule_id IN (SELECT rhdr.rule_id
FROM okc_xprt_rule_hdrs_all rhdr,
okc_template_usages tuse,
okc_xprt_template_rules trule
WHERE tuse.document_id = p_doc_id
AND tuse.document_type = p_doc_type
AND tuse.template_id = trule.template_id
AND trule.rule_id = rhdr.rule_id
AND rhdr.status_code <> 'DRAFT'
UNION
SELECT rhdr.rule_id
FROM okc_xprt_rule_hdrs_all rhdr
WHERE rhdr.org_id = l_org_id
AND rhdr.intent = l_intent
AND rhdr.org_wide_flag = 'Y'
AND rhdr.status_code <> 'DRAFT'
)
);