The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'X'
FROM cz_rules
WHERE rule_type = 200 -- Statement rule
--AND devl_project_id = p_devl_project_id
AND rule_id = persistent_rule_id
AND SUBSTR(orig_sys_ref,INSTR(orig_sys_ref,':',-1,1)+1) = to_char(p_rule_id) --For Bug# 8240959
AND deleted_flag = 0;
SELECT template_name,
DECODE(parent_template_id, NULL, template_id, parent_template_id),
intent,
name,
org_id
FROM okc_terms_templates_all,
hr_operating_units
WHERE organization_id = org_id
AND template_id = p_template_id ;
UPDATE okc_terms_templates_all
SET xprt_request_id = FND_GLOBAL.CONC_REQUEST_ID,
last_update_login = FND_GLOBAL.LOGIN_ID,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID
WHERE template_id = p_template_id;
SELECT DISTINCT to_char(r.template_id)
FROM okc_terms_templates_all t,
okc_xprt_template_rules r,
okc_xprt_rule_hdrs_all h
WHERE r.template_id = t.template_id
AND r.rule_id = h.rule_id
AND t.status_code IN ('APPROVED','ON_HOLD')
AND h.request_id = FND_GLOBAL.CONC_REQUEST_ID;
SELECT t.template_id
FROM okc_terms_templates_all t
WHERE t.org_id = p_org_id
AND t.intent IN (SELECT DISTINCT intent
FROM okc_xprt_rule_hdrs_all
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
)
AND t.contract_expert_enabled = 'Y'
AND t.status_code IN ('APPROVED','ON_HOLD');
SELECT 'X'
FROM okc_xprt_rule_hdrs_all
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
AND NVL(org_wide_flag,'N') = 'Y';
SELECT org_id
FROM okc_xprt_rule_hdrs_all
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID;
SELECT SUBSTR(req_data,
1,
INSTR(req_data,':',1) -1
) child_req_id,
SUBSTR(req_data,
INSTR(req_data,':',1) + 1
) run_id
FROM dual;
SELECT COUNT(*)
FROM cz_imp_rules
WHERE run_id = G_RUN_ID;
SELECT rule_id
FROM okc_xprt_rule_hdrs_all
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 fnd_profile.Value('OKC_USE_CONTRACTS_RULES_ENGINE') INTO l_okc_rules_engine FROM dual;
Step 1: Update current request Id for all rules to be published
*/
fnd_file.put_line(FND_FILE.LOG,' ');
UPDATE okc_xprt_rule_hdrs_all
SET request_id = FND_GLOBAL.CONC_REQUEST_ID,
program_id = FND_GLOBAL.CONC_PROGRAM_ID,
program_application_id = FND_GLOBAL.PROG_APPL_ID,
program_update_date = SYSDATE,
last_update_login = FND_GLOBAL.LOGIN_ID,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID
WHERE org_id = p_org_id
AND intent = DECODE(NVL(fnd_profile.value('OKC_LIBRARY_ACCESS_INTENT'),'A'),'A',
intent,
fnd_profile.value('OKC_LIBRARY_ACCESS_INTENT')
)
AND status_code = 'PENDINGPUB';
UPDATE okc_xprt_rule_hdrs_all
SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
AND rule_id = csr_pub_rule_rec.rule_id;
UPDATE okc_xprt_rule_hdrs_all
SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
AND rule_id = csr_pub_rule_rec.rule_id;
UPDATE okc_xprt_rule_hdrs_all
SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
AND rule_id = csr_pub_rule_rec.rule_id;
UPDATE okc_xprt_rule_hdrs_all
SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
AND rule_id = csr_pub_rule_rec.rule_id;
UPDATE okc_xprt_rule_hdrs_all
SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
AND rule_id = csr_pub_rule_rec.rule_id;
UPDATE okc_xprt_rule_hdrs_all
SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
AND rule_id = csr_pub_rule_rec.rule_id;
UPDATE okc_xprt_rule_hdrs_all
SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
AND rule_id = csr_pub_rule_rec.rule_id;
UPDATE okc_xprt_rule_hdrs_all
SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
AND rule_id = csr_pub_rule_rec.rule_id;
UPDATE okc_xprt_rule_hdrs_all
SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
AND rule_id = csr_pub_rule_rec.rule_id;
UPDATE okc_xprt_rule_hdrs_all
SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
AND rule_id = csr_pub_rule_rec.rule_id;
UPDATE okc_xprt_rule_hdrs_all
SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
AND rule_id = csr_pub_rule_rec.rule_id;
Step 6: Insert Extension Rules
*/
fnd_file.put_line(FND_FILE.LOG,' ');
fnd_file.put_line(FND_FILE.LOG,'Step 6: Calling API to insert extension rule records');
fnd_file.put_line(FND_FILE.LOG,'Step 6: After Calling API to insert extension rule records');
UPDATE okc_xprt_rule_hdrs_all
SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
AND rule_id = csr_pub_rule_rec.rule_id;
UPDATE okc_xprt_rule_hdrs_all
SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
AND rule_id = csr_pub_rule_rec.rule_id;
UPDATE okc_xprt_rule_hdrs_all
SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
AND rule_id = csr_pub_rule_rec.rule_id;
UPDATE okc_xprt_rule_hdrs_all
SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
AND rule_id = csr_pub_rule_rec.rule_id;
UPDATE okc_xprt_rule_hdrs_all
SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
AND rule_id = csr_pub_rule_rec.rule_id;
UPDATE okc_xprt_rule_hdrs_all
SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
AND rule_id = csr_pub_rule_rec.rule_id;
UPDATE okc_xprt_rule_hdrs_all
SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
AND rule_id = csr_pub_rule_rec.rule_id;
UPDATE okc_xprt_rule_hdrs_all
SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
AND rule_id = csr_pub_rule_rec.rule_id;
Step 1: Update current request Id for all rules to be published
*/
fnd_file.put_line(FND_FILE.LOG,' ');
UPDATE okc_xprt_rule_hdrs_all
SET request_id = FND_GLOBAL.CONC_REQUEST_ID,
program_id = FND_GLOBAL.CONC_PROGRAM_ID,
program_application_id = FND_GLOBAL.PROG_APPL_ID,
program_update_date = SYSDATE,
last_update_login = FND_GLOBAL.LOGIN_ID,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID
WHERE org_id = p_org_id
AND intent = DECODE(NVL(fnd_profile.value('OKC_LIBRARY_ACCESS_INTENT'),'A'),'A',
intent,
fnd_profile.value('OKC_LIBRARY_ACCESS_INTENT')
)
AND status_code = 'PENDINGPUB';
UPDATE okc_xprt_rule_hdrs_all
SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
AND rule_id = csr_pub_rule_rec.rule_id;
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
);
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 = '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,
activation_date = SYSDATE
WHERE rule_id = l_rule_id;
DELETE FROM okc_xprt_rule_outcomes_active WHERE rule_id = l_rule_id;
DELETE FROM okc_xprt_rule_cond_vals_active WHERE rule_condition_id IN (SELECT rule_condition_id FROM okc_xprt_rule_conditions WHERE rule_id = l_rule_id);
DELETE FROM okc_xprt_rule_cond_active WHERE rule_id = l_rule_id;
DELETE FROM okc_xprt_rule_hdrs_all_active WHERE rule_id = l_rule_id;
SELECT SUBSTR(req_data,
1,
INSTR(req_data,':',1) -1
) child_req_id,
SUBSTR(req_data,
INSTR(req_data,':',1) + 1
) run_id
FROM dual;
SELECT COUNT(*)
FROM cz_imp_rules
WHERE run_id = G_RUN_ID;
SELECT rule_id
FROM okc_xprt_rule_hdrs_all
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 fnd_profile.Value('OKC_USE_CONTRACTS_RULES_ENGINE') INTO l_okc_rules_engine FROM dual;
Step 1: Update current request Id for all rules to be published
*/
fnd_file.put_line(FND_FILE.LOG,' ');
UPDATE okc_xprt_rule_hdrs_all
SET request_id = FND_GLOBAL.CONC_REQUEST_ID,
program_id = FND_GLOBAL.CONC_PROGRAM_ID,
program_application_id = FND_GLOBAL.PROG_APPL_ID,
program_update_date = SYSDATE,
last_update_login = FND_GLOBAL.LOGIN_ID,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID
WHERE org_id = p_org_id
AND intent = DECODE(NVL(fnd_profile.value('OKC_LIBRARY_ACCESS_INTENT'),'A'),'A',
intent,
fnd_profile.value('OKC_LIBRARY_ACCESS_INTENT')
)
AND status_code = 'PENDINGDISABLE';
UPDATE okc_xprt_rule_hdrs_all
SET status_code = DECODE(l_rule_exists_flag,'T','ACTIVE',status_code)
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
AND rule_id = csr_dis_rule_rec.rule_id;
UPDATE okc_xprt_rule_hdrs_all
SET status_code = DECODE(l_rule_exists_flag,'T','ACTIVE',status_code)
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
AND rule_id = csr_dis_rule_rec.rule_id;
UPDATE okc_xprt_rule_hdrs_all
SET status_code = DECODE(l_rule_exists_flag,'T','ACTIVE',status_code)
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
AND rule_id = csr_dis_rule_rec.rule_id;
UPDATE okc_xprt_rule_hdrs_all
SET status_code = DECODE(l_rule_exists_flag,'T','ACTIVE',status_code)
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
AND rule_id = csr_dis_rule_rec.rule_id;
UPDATE okc_xprt_rule_hdrs_all
SET status_code = DECODE(l_rule_exists_flag,'T','ACTIVE',status_code)
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
AND rule_id = csr_dis_rule_rec.rule_id;
UPDATE okc_xprt_rule_hdrs_all
SET status_code = DECODE(l_rule_exists_flag,'T','ACTIVE',status_code)
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
AND rule_id = csr_dis_rule_rec.rule_id;
UPDATE okc_xprt_rule_hdrs_all
SET status_code = DECODE(l_rule_exists_flag,'T','ACTIVE',status_code)
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
AND rule_id = csr_dis_rule_rec.rule_id;
UPDATE okc_xprt_rule_hdrs_all
SET status_code = DECODE(l_rule_exists_flag,'T','ACTIVE',status_code)
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
AND rule_id = csr_dis_rule_rec.rule_id;
UPDATE okc_xprt_rule_hdrs_all
SET status_code = DECODE(l_rule_exists_flag,'T','ACTIVE',status_code)
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
AND rule_id = csr_dis_rule_rec.rule_id;
UPDATE okc_xprt_rule_hdrs_all
SET status_code = DECODE(l_rule_exists_flag,'T','ACTIVE',status_code)
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
AND rule_id = csr_dis_rule_rec.rule_id;
UPDATE okc_xprt_rule_hdrs_all
SET status_code = DECODE(l_rule_exists_flag,'T','ACTIVE',status_code)
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
AND rule_id = csr_dis_rule_rec.rule_id;
UPDATE okc_xprt_rule_hdrs_all
SET status_code = DECODE(l_rule_exists_flag,'T','ACTIVE',status_code)
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
AND rule_id = csr_dis_rule_rec.rule_id;
UPDATE okc_xprt_rule_hdrs_all
SET status_code = DECODE(l_rule_exists_flag,'T','ACTIVE',status_code)
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
AND rule_id = csr_dis_rule_rec.rule_id;
UPDATE okc_xprt_rule_hdrs_all
SET status_code = DECODE(l_rule_exists_flag,'T','ACTIVE',status_code)
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
AND rule_id = csr_dis_rule_rec.rule_id;
Step 1: Update current request Id for all rules to be published
*/
fnd_file.put_line(FND_FILE.LOG,' ');
UPDATE okc_xprt_rule_hdrs_all
SET request_id = FND_GLOBAL.CONC_REQUEST_ID,
program_id = FND_GLOBAL.CONC_PROGRAM_ID,
program_application_id = FND_GLOBAL.PROG_APPL_ID,
program_update_date = SYSDATE,
last_update_login = FND_GLOBAL.LOGIN_ID,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID
WHERE org_id = p_org_id
AND intent = DECODE(NVL(fnd_profile.value('OKC_LIBRARY_ACCESS_INTENT'),'A'),'A',
intent,
fnd_profile.value('OKC_LIBRARY_ACCESS_INTENT')
)
AND status_code = 'PENDINGDISABLE';
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 = 'INACTIVE',
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;
DELETE FROM okc_xprt_rule_outcomes_active WHERE rule_id = l_rule_id;
DELETE FROM okc_xprt_rule_cond_vals_active WHERE rule_condition_id IN (SELECT rule_condition_id FROM okc_xprt_rule_conditions WHERE rule_id = l_rule_id);
DELETE FROM okc_xprt_rule_cond_active WHERE rule_id = l_rule_id;
DELETE FROM okc_xprt_rule_hdrs_all_active WHERE rule_id = l_rule_id;
SELECT 'x'
FROM okc_terms_templates_all t
WHERE t.org_id = p_org_id
AND t.intent = p_intent
AND t.template_id = NVL(p_template_id, template_id);
SELECT COUNT(*)
FROM okc_xprt_rule_hdrs_all
WHERE org_id = p_org_id
AND intent = p_intent
AND status_code = 'ACTIVE';
SELECT SUBSTR(req_data,
1,
INSTR(req_data,':',1) -1
) child_req_id,
SUBSTR(req_data,
INSTR(req_data,':',1) + 1
) run_id
FROM dual;
SELECT COUNT(*)
FROM cz_imp_rules
WHERE run_id = G_RUN_ID;
SELECT fnd_profile.Value('OKC_USE_CONTRACTS_RULES_ENGINE') INTO l_okc_rules_engine FROM dual;
Step 2.2: Insert Extension Rules in Template Approval Flow
*/
fnd_file.put_line(FND_FILE.LOG,' ');
fnd_file.put_line(FND_FILE.LOG,'Step 2.2: Calling API to insert extension rule records');
fnd_file.put_line(FND_FILE.LOG,'Step 2.2: After Calling API to insert extension rule records');