The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT meaning
FROM fnd_lookups
WHERE lookup_type = 'OKC_XPRT_RULES_QA_LIST'
AND lookup_code = p_qa_code;
PROCEDURE insert_qa_errors_t
(
p_qa_errors_t_rec IN OKC_QA_ERRORS_T%ROWTYPE,
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) := 'insert_qa_errors_t';
INSERT INTO OKC_QA_ERRORS_T
(
DOCUMENT_TYPE,
DOCUMENT_ID,
SEQUENCE_ID,
ERROR_RECORD_TYPE,
TITLE,
ERROR_SEVERITY,
QA_CODE,
MESSAGE_NAME,
PROBLEM_SHORT_DESC,
PROBLEM_DETAILS_SHORT,
PROBLEM_DETAILS,
SUGGESTION,
ARTICLE_ID,
DELIVERABLE_ID,
SECTION_NAME,
REFERENCE_COLUMN1,
REFERENCE_COLUMN2,
REFERENCE_COLUMN3,
REFERENCE_COLUMN4,
REFERENCE_COLUMN5,
CREATION_DATE,
ERROR_RECORD_TYPE_NAME,
ERROR_SEVERITY_NAME,
RULE_ID
)
VALUES
(
p_qa_errors_t_rec.DOCUMENT_TYPE,
p_qa_errors_t_rec.DOCUMENT_ID,
p_qa_errors_t_rec.SEQUENCE_ID,
p_qa_errors_t_rec.ERROR_RECORD_TYPE,
p_qa_errors_t_rec.TITLE,
p_qa_errors_t_rec.ERROR_SEVERITY,
p_qa_errors_t_rec.QA_CODE,
p_qa_errors_t_rec.MESSAGE_NAME,
p_qa_errors_t_rec.PROBLEM_SHORT_DESC,
p_qa_errors_t_rec.PROBLEM_DETAILS_SHORT,
p_qa_errors_t_rec.PROBLEM_DETAILS,
p_qa_errors_t_rec.SUGGESTION,
p_qa_errors_t_rec.ARTICLE_ID,
p_qa_errors_t_rec.DELIVERABLE_ID,
p_qa_errors_t_rec.SECTION_NAME,
p_qa_errors_t_rec.REFERENCE_COLUMN1,
p_qa_errors_t_rec.REFERENCE_COLUMN2,
p_qa_errors_t_rec.REFERENCE_COLUMN3,
p_qa_errors_t_rec.REFERENCE_COLUMN4,
p_qa_errors_t_rec.REFERENCE_COLUMN5,
p_qa_errors_t_rec.CREATION_DATE,
p_qa_errors_t_rec.ERROR_RECORD_TYPE_NAME,
p_qa_errors_t_rec.ERROR_SEVERITY_NAME,
p_qa_errors_t_rec.RULE_ID
);
END insert_qa_errors_t;
insert_cursor circ_rec;
SELECT rule_name
FROM okc_xprt_rule_hdrs_all
WHERE rule_id = p_rule_id;
select distinct(temp.template_id) Template_Id, temp.org_id, temp.intent
from OKC_XPRT_TEMPLATE_RULES rules, okc_terms_templates_all temp
where rules.rule_id = p_rule_id
and rules.template_id = temp.template_id
and (sysdate between nvl(temp.start_date, sysdate) and nvl(temp.end_date, sysdate ))
UNION
-- Org Wide Rule templates. Reverted back the changes done for 5019422 by removing
-- join to okc_xprt_template_rules
SELECT t.template_id, t.org_id, t.intent
FROM okc_terms_templates_all t,
okc_xprt_rule_hdrs_all r
WHERE t.org_id = r.org_id
AND t.intent = r.intent
AND t.contract_expert_enabled = 'Y'
-- AND t.status_code IN ('APPROVED','ON_HOLD')
AND (sysdate between nvl(t.start_date, sysdate) and nvl(t.end_date, sysdate ))
AND NVL(r.org_wide_flag,'N') = 'Y'
AND r.rule_id = p_rule_id;
select condition_question_id,outcome_question_id,level
from OKC_XPRT_QUESTION_REF_T
connect by prior condition_question_id = outcome_question_id ;
l_sql_string := 'select distinct to_char(cond.object_code) condition_question_id, to_char(object_value_id) outcome_question_id
from OKC_XPRT_RULE_CONDITIONS cond, OKC_XPRT_RULE_OUTCOMES outcome
where cond.object_type = ''QUESTION''
and outcome.object_type = ''QUESTION''
and cond.rule_id = outcome.rule_id
and cond.rule_id in (
select distinct assoc.rule_id from OKC_XPRT_TEMPLATE_RULES assoc, OKC_XPRT_RULE_HDRS_ALL rules
where assoc.rule_id = rules.rule_id
and assoc.template_id = ' ||l_Template_Id ||
' and rules.status_code in (''ACTIVE'',''PENDINGPUB'',''REVISION'',''PENDINGDISABLE'')
UNION ALL
select distinct rule_id from OKC_XPRT_RULE_HDRS_ALL
where status_code in (''ACTIVE'',''PENDINGPUB'',''REVISION'',''PENDINGDISABLE'')
and org_wide_flag = ''Y''
and org_id = ' ||l_Org_Id ||
' and intent = ''' ||l_Intent|| '''' ||
' UNION ALL
select distinct rule_id from OKC_XPRT_RULE_HDRS_ALL
where org_wide_flag = ''Y''
and org_id = ' ||l_Org_Id||
' and intent = ''' ||l_Intent|| '''' ||
' and rule_id in '||l_request_rule_ids||
' UNION ALL
select distinct assoc.rule_id from OKC_XPRT_TEMPLATE_RULES assoc, OKC_XPRT_RULE_HDRS_ALL rules
where assoc.rule_id = rules.rule_id
and assoc.template_id = ' ||l_Template_Id||
' and rules.rule_id in ' ||l_request_rule_ids||
' )
UNION ALL
select distinct to_char(cond.object_value_code) condition_question_id, to_char(object_value_id) outcome_question_id
from OKC_XPRT_RULE_CONDITIONS cond, OKC_XPRT_RULE_OUTCOMES outcome
where cond.object_value_type = ''QUESTION''
and outcome.object_type = ''QUESTION''
and cond.rule_id = outcome.rule_id
and cond.rule_id in (
select distinct assoc.rule_id from OKC_XPRT_TEMPLATE_RULES assoc, OKC_XPRT_RULE_HDRS_ALL rules
where assoc.rule_id = rules.rule_id
and assoc.template_id = ' ||l_Template_Id||
' and rules.status_code in (''ACTIVE'',''PENDINGPUB'',''REVISION'',''PENDINGDISABLE'')
UNION ALL
select distinct rule_id from OKC_XPRT_RULE_HDRS_ALL
where status_code in (''ACTIVE'',''PENDINGPUB'',''REVISION'',''PENDINGDISABLE'')
and org_wide_flag = ''Y''
and org_id = ' ||l_Org_Id||
' and intent = ''' ||l_Intent|| '''' ||
' UNION ALL
select distinct rule_id from OKC_XPRT_RULE_HDRS_ALL
where org_wide_flag = ''Y''
and org_id = ' ||l_Org_Id||
' and intent = ''' ||l_Intent|| '''' ||
' and rule_id in ' ||l_request_rule_ids||
' UNION ALL
select distinct assoc.rule_id from OKC_XPRT_TEMPLATE_RULES assoc, OKC_XPRT_RULE_HDRS_ALL rules
where assoc.rule_id = rules.rule_id
and assoc.template_id = ' ||l_Template_Id||
' and rules.rule_id in ' ||l_request_rule_ids||
' )';
OPEN insert_cursor FOR l_sql_string;
FETCH insert_cursor INTO condition_question_id, outcome_question_id;
EXIT WHEN insert_cursor%NOTFOUND;
insert into OKC_XPRT_QUESTION_REF_T
(
condition_question_id,
outcome_question_id
)
values
(
condition_question_id,
outcome_question_id
);
CLOSE insert_cursor;
delete from OKC_XPRT_QUESTION_REF_T;
delete from OKC_XPRT_QUESTION_REF_T;
-- insert into okc_qa_errors_t
insert_qa_errors_t
(
p_qa_errors_t_rec => l_qa_errors_t_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
SELECT rule_name
FROM okc_xprt_rule_hdrs_all
WHERE rule_id = p_rule_id;
SELECT article_id,
article_title
FROM
(
-- All DISTINCT Clauses from Conditions
SELECT v.object_value_code article_id,
a.article_title
FROM okc_xprt_rule_cond_vals v,
okc_xprt_rule_conditions c,
okc_xprt_rule_hdrs_all r,
okc_articles_all a
WHERE v.rule_condition_id = c.rule_condition_id
AND c.rule_id = r.rule_id
AND a.article_id = to_number(v.object_value_code) -- Fixed for Bug 4935811. Removed to_char on article_id
AND c.object_type = 'CLAUSE'
AND r.rule_id = p_rule_id
GROUP BY v.object_value_code, a.article_title
UNION
-- All DISTINCT Clauses from Outcome
SELECT to_char(o.object_value_id) article_id,
a.article_title
FROM okc_xprt_rule_outcomes o,
okc_xprt_rule_hdrs_all r,
okc_articles_all a
WHERE o.rule_id = r.rule_id
AND a.article_id = o.object_value_id
AND o.object_type = 'CLAUSE'
AND r.rule_id = p_rule_id
GROUP BY o.object_value_id, a.article_title
) ;
SELECT v.article_status
FROM okc_article_versions v,
okc_articles_all a
WHERE a.article_id = v.article_id
AND a.article_id = p_article_id
AND v.article_status IN ('APPROVED','ON_HOLD')
AND sysdate BETWEEN v.start_date AND NVL(v.end_date,sysdate+1);
SELECT 'x'
FROM okc_article_versions v,
okc_articles_all a
WHERE a.article_id = v.article_id
AND a.article_id = p_article_id
AND v.article_status IN ('APPROVED','ON_HOLD');
-- insert into okc_qa_errors_t
insert_qa_errors_t
(
p_qa_errors_t_rec => l_qa_errors_t_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
-- insert into okc_qa_errors_t
insert_qa_errors_t
(
p_qa_errors_t_rec => l_qa_errors_t_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
SELECT rule_name
FROM okc_xprt_rule_hdrs_all
WHERE rule_id = p_rule_id;
SELECT article_id,
article_title
FROM
(
-- All DISTINCT Clauses from Conditions
SELECT v.object_value_code article_id,
a.article_title
FROM okc_xprt_rule_cond_vals v,
okc_xprt_rule_conditions c,
okc_xprt_rule_hdrs_all r,
okc_articles_all a
WHERE v.rule_condition_id = c.rule_condition_id
AND c.rule_id = r.rule_id
AND to_char(a.article_id) = v.object_value_code
AND c.object_type = 'CLAUSE'
AND r.rule_id = p_rule_id
GROUP BY v.object_value_code, a.article_title
UNION
-- All DISTINCT Clauses from Outcome
SELECT to_char(o.object_value_id) article_id,
a.article_title
FROM okc_xprt_rule_outcomes o,
okc_xprt_rule_hdrs_all r,
okc_articles_all a
WHERE o.rule_id = r.rule_id
AND a.article_id = o.object_value_id
AND o.object_type = 'CLAUSE'
AND r.rule_id = p_rule_id
GROUP BY o.object_value_id, a.article_title
) ;
SELECT 'x'
FROM okc_article_versions v,
okc_articles_all a
WHERE a.article_id = v.article_id
AND a.article_id = p_article_id
AND v.article_status IN ('DRAFT','PENDING_APPROVAL','REJECTED')
-- AND sysdate BETWEEN v.start_date AND NVL(v.end_date,sysdate+1)
AND NOT EXISTS
(
SELECT 'x'
FROM okc_article_versions v,
okc_articles_all a
WHERE a.article_id = v.article_id
AND a.article_id = p_article_id
AND v.article_status IN ('APPROVED','ON_HOLD')
)
;
-- insert into okc_qa_errors_t
insert_qa_errors_t
(
p_qa_errors_t_rec => l_qa_errors_t_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
SELECT rule_name
FROM okc_xprt_rule_hdrs_all
WHERE rule_id = p_rule_id;
SELECT DISTINCT q.question_name question_name,
c.object_value_set_name value_set_name,
q.question_id question_id
FROM okc_xprt_rule_conditions c,
okc_xprt_questions_vl q
WHERE q.question_id = c.object_code
AND c.object_type='QUESTION'
AND c.object_code_datatype IN ('L','B')
AND c.rule_id = p_rule_id
UNION
-- Questions in Outcome
SELECT DISTINCT q.question_name question_name,
q.value_set_name value_set_name,
q.question_id question_id
FROM okc_xprt_rule_outcomes o,
okc_xprt_questions_vl q
WHERE q.question_id = o.object_value_id
AND o.object_type='QUESTION'
AND q.question_datatype IN ('L','B')
AND o.rule_id = p_rule_id;
SELECT v.object_value_code question_value
FROM okc_xprt_rule_conditions c,
okc_xprt_rule_cond_vals v
WHERE c.rule_condition_id = v.rule_condition_id
AND c.object_type = 'QUESTION'
AND c.object_code_datatype IN ('L','B')
AND c.rule_id = p_rule_id
AND c.object_code = to_char(p_question_id);
SELECT validation_type,
flex_value_set_id
FROM FND_FLEX_VALUE_SETS
WHERE flex_value_set_name = p_value_set_name;
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 'x'
FROM fnd_flex_values_vl
WHERE flex_value_id = p_flex_value_id
AND enabled_flag = 'Y'
AND SYSDATE BETWEEN NVL(start_date_active,SYSDATE) AND NVL(end_date_active,SYSDATE+1);
-- Value Set deleted
l_qa_errors_t_rec.SEQUENCE_ID := p_sequence_id;
-- insert into okc_qa_errors_t
insert_qa_errors_t
(
p_qa_errors_t_rec => l_qa_errors_t_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
END IF; -- valueset deleted
l_sql_stmt := 'SELECT '||l_name_col||' , '||l_id_col||
' FROM '||l_table_name||' '||
l_additional_where_clause ;
-- insert into okc_qa_errors_t
insert_qa_errors_t
(
p_qa_errors_t_rec => l_qa_errors_t_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
-- insert into okc_qa_errors_t
insert_qa_errors_t
(
p_qa_errors_t_rec => l_qa_errors_t_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
-- insert into okc_qa_errors_t
insert_qa_errors_t
(
p_qa_errors_t_rec => l_qa_errors_t_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
SELECT rule_name
FROM okc_xprt_rule_hdrs_all
WHERE rule_id = p_rule_id;
SELECT DISTINCT okc_xprt_util_pvt.get_object_name(c.OBJECT_TYPE,c.OBJECT_CODE) variable_name,
c.object_code variable_code,
c.object_value_set_name value_set_name
FROM okc_xprt_rule_conditions c
WHERE c.rule_id = p_rule_id
AND c.object_type = 'VARIABLE'
AND c.object_code_datatype = 'V';
SELECT v.object_value_code variable_value
FROM okc_xprt_rule_conditions c,
okc_xprt_rule_cond_vals v
WHERE c.rule_condition_id = v.rule_condition_id
AND c.object_type = 'VARIABLE'
AND c.object_code_datatype = 'V'
AND c.rule_id = p_rule_id
AND c.object_code = p_variable_name;
SELECT validation_type,
flex_value_set_id
FROM FND_FLEX_VALUE_SETS
WHERE flex_value_set_name = p_value_set_name;
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 'x'
FROM fnd_flex_values_vl
WHERE flex_value_id = p_flex_value_id
AND enabled_flag = 'Y'
AND SYSDATE BETWEEN NVL(start_date_active,SYSDATE) AND NVL(end_date_active,SYSDATE+1);
select 'X'
from MTL_SYSTEM_ITEMS_VL
where organization_id =
TO_NUMBER(oe_sys_parameters.value('MASTER_ORGANIZATION_ID', to_number(fnd_profile.value('ORG_ID'))))
AND (bom_item_type = 1 OR bom_item_type = 4)
AND vendor_warranty_flag = 'N'
AND primary_uom_code <> 'ENR'
AND concatenated_segments = p_concatenated_segments
order by 1;
-- Value Set deleted
l_qa_errors_t_rec.SEQUENCE_ID := p_sequence_id;
-- insert into okc_qa_errors_t
insert_qa_errors_t
(
p_qa_errors_t_rec => l_qa_errors_t_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
END IF; -- valueset deleted
-- insert into okc_qa_errors_t
insert_qa_errors_t
(
p_qa_errors_t_rec => l_qa_errors_t_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
l_sql_stmt := 'SELECT '||l_name_col||' , '||l_id_col||
' FROM '||l_table_name||' '||
l_additional_where_clause ;
fnd_file.put_line(FND_FILE.LOG,'SELECT '||l_name_col||' , '||l_id_col);
-- insert into okc_qa_errors_t
insert_qa_errors_t
(
p_qa_errors_t_rec => l_qa_errors_t_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
-- insert into okc_qa_errors_t
insert_qa_errors_t
(
p_qa_errors_t_rec => l_qa_errors_t_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
-- insert into okc_qa_errors_t
insert_qa_errors_t
(
p_qa_errors_t_rec => l_qa_errors_t_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
SELECT rule_name
FROM okc_xprt_rule_hdrs_all
WHERE rule_id = p_rule_id;
SELECT DISTINCT q.question_name question_name
FROM okc_xprt_rule_conditions c,
okc_xprt_questions_vl q
WHERE q.question_id = c.object_code
AND c.object_type='QUESTION'
AND c.rule_id = p_rule_id
AND q.disabled_flag = 'Y'
UNION
-- Questions in Conditions RHS
SELECT DISTINCT q.question_name question_name
FROM okc_xprt_rule_conditions c,
okc_xprt_questions_vl q
WHERE q.question_id = c.object_value_code
AND c.object_value_type='QUESTION'
AND c.rule_id = p_rule_id
AND q.disabled_flag = 'Y'
UNION
-- Questions in Outcome
SELECT DISTINCT q.question_name question_name
FROM okc_xprt_rule_outcomes o,
okc_xprt_questions_vl q
WHERE q.question_id = o.object_value_id
AND o.object_type='QUESTION'
AND o.rule_id = p_rule_id
AND q.disabled_flag = 'Y' ;
-- insert into okc_qa_errors_t
insert_qa_errors_t
(
p_qa_errors_t_rec => l_qa_errors_t_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
SELECT rule_name, NVL(org_wide_flag,'N')
FROM okc_xprt_rule_hdrs_all
WHERE rule_id = p_rule_id;
SELECT 'x'
FROM okc_xprt_template_rules
WHERE rule_id = p_rule_id
AND NVL(deleted_flag,'N') = 'N' ;
-- insert into okc_qa_errors_t
insert_qa_errors_t
(
p_qa_errors_t_rec => l_qa_errors_t_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
SELECT distinct v.procedure_name procedure_name,
rcon.object_code variable_code, -- LHS of Condition
t.variable_name variable_name,
rhdr.rule_name rule_name
FROM okc_xprt_rule_hdrs_all rhdr,
okc_xprt_rule_conditions rcon,
okc_bus_variables_b v,
okc_bus_variables_tl t
WHERE rhdr.rule_id = rcon.rule_id
AND rhdr.rule_id = p_rule_id
AND rcon.object_type = 'VARIABLE'
AND rcon.object_code = v.variable_code
AND v.variable_code = t.variable_code
AND v.variable_source = 'P'
AND t.LANGUAGE = USERENV('LANG')
UNION
SELECT distinct v.procedure_name procedure_name,
rcon.object_value_code variable_code, -- RHS of Condition
t.variable_name variable_name,
rhdr.rule_name rule_name
FROM okc_xprt_rule_hdrs_all rhdr,
okc_xprt_rule_conditions rcon,
okc_bus_variables_b v,
okc_bus_variables_tl t
WHERE rhdr.rule_id = rcon.rule_id
AND rhdr.rule_id = p_rule_id
AND rcon.object_value_type = 'VARIABLE'
AND rcon.object_code = v.variable_code
AND v.variable_code = t.variable_code
AND v.variable_source = 'P'
AND t.LANGUAGE = USERENV('LANG');
SELECT status
FROM all_objects
WHERE object_name = SUBSTR(p_procedure_name,
INSTR(p_procedure_name,'.')+1,
(INSTR(p_procedure_name,'.',1,2) -
INSTR(p_procedure_name,'.') - 1))
AND object_type = 'PACKAGE'
AND owner = SUBSTR(p_procedure_name,1,INSTR(p_procedure_name,'.')-1);
SELECT status
FROM all_objects
WHERE object_name = SUBSTR(p_procedure_name,
INSTR(p_procedure_name,'.')+1,
(INSTR(p_procedure_name,'.',1,2) -
INSTR(p_procedure_name,'.') - 1))
AND object_type = 'PACKAGE BODY'
AND owner = SUBSTR(p_procedure_name,1,INSTR(p_procedure_name,'.')-1);
SELECT 'X'
FROM all_source
WHERE name = SUBSTR(p_procedure_name,
INSTR(p_procedure_name,'.')+1,
(INSTR(p_procedure_name,'.',1,2) -
INSTR(p_procedure_name,'.') - 1))
AND type = 'PACKAGE'
AND owner = SUBSTR(p_procedure_name,1,INSTR(p_procedure_name,'.')-1)
-- Added +1 for Instr for bug 5964390
AND text LIKE '%' || SUBSTR(p_procedure_name,INSTR(p_procedure_name,'.',1,2)+1) || '%';
-- INSERT INTO OKC_QA_ERRORS_T
INSERT_QA_ERRORS_T
(
P_QA_ERRORS_T_REC => L_QA_ERRORS_T_REC,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA
);
-- INSERT INTO OKC_QA_ERRORS_T
INSERT_QA_ERRORS_T
(
P_QA_ERRORS_T_REC => L_QA_ERRORS_T_REC,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA
);
SELECT OKC_QA_ERRORS_T_S.NEXTVAL
FROM DUAL;
SELECT rules.org_id
FROM okc_xprt_rule_hdrs_all rules
WHERE rules.rule_id = p_rule_id;
-- If the rule passed all QAs successfully then insert into okc_qa_errors_t as success
IF l_rule_qa_status = 'S' THEN
l_qa_errors_t_rec.SEQUENCE_ID := l_sequence_id;
-- insert into okc_qa_errors_t
insert_qa_errors_t
(
p_qa_errors_t_rec => l_qa_errors_t_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
Depending on the p_sync_mode this API will update the rule status and kick-off the
concurrent program to publish or disable rules
*/
PROCEDURE sync_rules
(
p_sync_mode IN VARCHAR2,
p_org_id IN NUMBER,
p_ruleid_tbl IN RuleIdList,
x_request_id 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) := 'sync_rules';
SELECT name
FROM wf_roles
WHERE name = FND_GLOBAL.USER_NAME;
UPDATE okc_xprt_rule_hdrs_all
SET status_code = DECODE(p_sync_mode,'PUBLISH','PENDINGPUB',
'DISABLE','PENDINGDISABLE',
p_sync_mode)
WHERE rule_id = p_ruleid_tbl(i);