The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT org_id,
intent,
DECODE(condition_expr_code,'ALL','AND','OR'),
rule_type
FROM okc_xprt_rule_hdrs_all
WHERE rule_id = p_rule_id;
SELECT c.rule_condition_id,
c.object_type,
DECODE(c.object_type,
'VARIABLE',DECODE(SUBSTR(c.object_code,1,3),'OKC',c.object_code,'USER$' || c.object_code),
'CONSTANT','CONSTANT$' || c.object_code,
c.object_code) object_code,
--c.object_code,
NVL(c.object_code_datatype,'V'),
DECODE(c.operator,'IS','ANYTRUE',
'IN','ANYTRUE',
'IS_NOT','NOTTRUE',
'NOT_IN','NOTTRUE',
c.operator),
c.object_value_type
FROM okc_xprt_rule_conditions c
WHERE c.rule_id = p_rule_id;
SELECT v.object_value_code
FROM okc_xprt_rule_cond_vals v
WHERE v.rule_condition_id = p_rule_condition_id;*/
SELECT DECODE(c.object_value_type,
'VARIABLE',DECODE(SUBSTR(v.object_value_code,1,3),'OKC',v.object_value_code,'USER$' || v.object_value_code),
'CONSTANT','CONSTANT$' || v.object_value_code,
v.object_value_code) object_value_code
--v.object_value_code
FROM okc_xprt_rule_cond_vals v, okc_xprt_rule_conditions c
WHERE v.rule_condition_id = c.rule_condition_id
AND v.rule_condition_id = p_rule_condition_id;
SELECT object_type,
object_value_id
FROM okc_xprt_rule_outcomes
WHERE rule_id = p_rule_id;
Procedure Name : build_and_insert_rule
Description : This API calls build_statement_rule to build the rule statement.
It then inserts the statement into cz_imp_rules
Parameters:
p_rule_id - Rule Id
p_template_id - Template Id to which the rule is attached
p_run_id - Run Id for rules import
p_mode - Publish or Disable i.e P or D
+====================================================================*/
PROCEDURE build_and_insert_rule
(
p_rule_id IN VARCHAR2,
p_template_id IN NUMBER,
p_run_id IN NUMBER,
p_mode IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER
) IS
CURSOR csr_rule_dtls IS
SELECT r.rule_id,
r.org_id,
r.intent,
r.rule_name,
r.rule_description,
DECODE(p_mode,'P','0','D','1') deleted_flag,
r.rule_type
FROM okc_xprt_rule_hdrs_all r
WHERE r.rule_id = p_rule_id ;
SELECT template_name,
DECODE(parent_template_id, NULL, template_id, parent_template_id)
FROM OKC_TERMS_TEMPLATES_ALL
WHERE template_id = p_template_id ;
SELECT devl_project_id ,
orig_sys_ref
FROM cz_devl_projects
WHERE orig_sys_ref = p_orig_sys_ref
AND devl_project_id = persistent_project_id
AND deleted_flag = 0 ;
SELECT L.LANGUAGE_CODE
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG IN ('I', 'B');
l_api_name CONSTANT VARCHAR2(30) := 'build_and_insert_rule';
l_deleted_flag cz_imp_rules.deleted_flag%TYPE;
l_deleted_flag,
l_rule_type;
IF l_deleted_flag = '1' THEN
-- l_rule_text := NULL;
INSERT INTO CZ_IMP_LOCALIZED_TEXTS
(
LAST_UPDATE_LOGIN,
LOCALE_ID,
LOCALIZED_STR,
INTL_TEXT_ID,
CREATION_DATE,
LAST_UPDATE_DATE,
DELETED_FLAG,
EFF_FROM,
EFF_TO,
CREATED_BY,
LAST_UPDATED_BY,
SECURITY_MASK,
EFF_MASK,
CHECKOUT_USER,
LANGUAGE,
ORIG_SYS_REF,
SOURCE_LANG,
RUN_ID,
REC_STATUS,
DISPOSITION,
MODEL_ID,
FSK_DEVLPROJECT_1_1,
MESSAGE,
SEEDED_FLAG
)
VALUES
(
FND_GLOBAL.LOGIN_ID, --LAST_UPDATE_LOGIN
NULL, -- LOCALE_ID
l_rule_name, --LOCALIZED_STR
NULL, -- INTL_TEXT_ID
SYSDATE, -- CREATION_DATE
SYSDATE, -- LAST_UPDATE_DATE
'0', -- DELETED_FLAG
NULL, -- EFF_FROM
NULL, -- EFF_TO
FND_GLOBAL.USER_ID, -- CREATED_BY
FND_GLOBAL.USER_ID, -- LAST_UPDATED_BY
NULL, -- SECURITY_MASK
NULL, -- EFF_MASK
NULL, -- CHECKOUT_USER
l_language, --LANGUAGE
G_RULE_OSR||l_org_id||':'||l_intent||':'||l_template_id||':'||l_rule_type||':'||l_rule_id, --ORIG_SYS_REF
USERENV('LANG'), --SOURCE_LANG
p_run_id, -- RUN_ID
NULL, -- REC_STATUS
NULL, -- DISPOSITION
l_model_id, -- MODEL_ID
l_model_osr, --FSK_DEVLPROJECT_1_1
NULL, -- MESSAGE
NULL -- SEEDED_FLAG
);
l_cz_imp_rules.LAST_UPDATE_DATE := SYSDATE;
l_cz_imp_rules.DELETED_FLAG := l_deleted_flag; -- check
l_cz_imp_rules.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
l_cz_imp_rules.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
INSERT INTO cz_imp_rules
(
RULE_ID,
SUB_CONS_ID,
REASON_ID,
AMOUNT_ID,
GRID_ID,
RULE_FOLDER_ID,
DEVL_PROJECT_ID,
INVALID_FLAG,
DESC_TEXT,
NAME,
ANTECEDENT_ID,
CONSEQUENT_ID,
RULE_TYPE,
EXPR_RULE_TYPE,
COMPONENT_ID,
REASON_TYPE,
DISABLED_FLAG,
ORIG_SYS_REF,
CREATION_DATE,
LAST_UPDATE_DATE,
DELETED_FLAG,
EFF_FROM,
EFF_TO,
CREATED_BY,
LAST_UPDATED_BY,
SECURITY_MASK,
EFF_MASK,
CHECKOUT_USER,
LAST_UPDATE_LOGIN,
EFFECTIVE_USAGE_MASK,
SEQ_NBR,
EFFECTIVE_FROM,
EFFECTIVE_UNTIL,
PERSISTENT_RULE_ID,
EFFECTIVITY_SET_ID,
RULE_FOLDER_TYPE,
UNSATISFIED_MSG_ID,
UNSATISFIED_MSG_SOURCE,
SIGNATURE_ID,
TEMPLATE_PRIMITIVE_FLAG,
PRESENTATION_FLAG,
TEMPLATE_TOKEN,
RULE_TEXT,
NOTES,
CLASS_NAME,
INSTANTIATION_SCOPE,
MODEL_REF_EXPL_ID,
MUTABLE_FLAG,
SEEDED_FLAG,
UI_DEF_ID,
UI_PAGE_ID,
UI_PAGE_ELEMENT_ID,
MESSAGE,
RUN_ID,
DISPOSITION,
REC_STATUS,
FSK_DEVL_PROJECT,
FSK_LOCALIZED_TEXT_1,
FSK_LOCALIZED_TEXT_2,
IMPORT_PROG_VERSION,
FSK_COMPONENT_ID,
FSK_MODEL_REF_EXPL_ID
)
VALUES
(
l_cz_imp_rules.RULE_ID,
l_cz_imp_rules.SUB_CONS_ID,
l_cz_imp_rules.REASON_ID,
l_cz_imp_rules.AMOUNT_ID,
l_cz_imp_rules.GRID_ID,
l_cz_imp_rules.RULE_FOLDER_ID,
l_cz_imp_rules.DEVL_PROJECT_ID,
l_cz_imp_rules.INVALID_FLAG,
l_cz_imp_rules.DESC_TEXT,
l_cz_imp_rules.NAME,
l_cz_imp_rules.ANTECEDENT_ID,
l_cz_imp_rules.CONSEQUENT_ID,
l_cz_imp_rules.RULE_TYPE,
l_cz_imp_rules.EXPR_RULE_TYPE,
l_cz_imp_rules.COMPONENT_ID,
l_cz_imp_rules.REASON_TYPE,
l_cz_imp_rules.DISABLED_FLAG,
l_cz_imp_rules.ORIG_SYS_REF,
l_cz_imp_rules.CREATION_DATE,
l_cz_imp_rules.LAST_UPDATE_DATE,
l_cz_imp_rules.DELETED_FLAG,
l_cz_imp_rules.EFF_FROM,
l_cz_imp_rules.EFF_TO,
l_cz_imp_rules.CREATED_BY,
l_cz_imp_rules.LAST_UPDATED_BY,
l_cz_imp_rules.SECURITY_MASK,
l_cz_imp_rules.EFF_MASK,
l_cz_imp_rules.CHECKOUT_USER,
l_cz_imp_rules.LAST_UPDATE_LOGIN,
l_cz_imp_rules.EFFECTIVE_USAGE_MASK,
l_cz_imp_rules.SEQ_NBR,
l_cz_imp_rules.EFFECTIVE_FROM,
l_cz_imp_rules.EFFECTIVE_UNTIL,
l_cz_imp_rules.PERSISTENT_RULE_ID,
l_cz_imp_rules.EFFECTIVITY_SET_ID,
l_cz_imp_rules.RULE_FOLDER_TYPE,
l_cz_imp_rules.UNSATISFIED_MSG_ID,
l_cz_imp_rules.UNSATISFIED_MSG_SOURCE,
l_cz_imp_rules.SIGNATURE_ID,
l_cz_imp_rules.TEMPLATE_PRIMITIVE_FLAG,
l_cz_imp_rules.PRESENTATION_FLAG,
l_cz_imp_rules.TEMPLATE_TOKEN,
l_cz_imp_rules.RULE_TEXT,
l_cz_imp_rules.NOTES,
l_cz_imp_rules.CLASS_NAME,
l_cz_imp_rules.INSTANTIATION_SCOPE,
l_cz_imp_rules.MODEL_REF_EXPL_ID,
l_cz_imp_rules.MUTABLE_FLAG,
l_cz_imp_rules.SEEDED_FLAG,
l_cz_imp_rules.UI_DEF_ID,
l_cz_imp_rules.UI_PAGE_ID,
l_cz_imp_rules.UI_PAGE_ELEMENT_ID,
l_cz_imp_rules.MESSAGE,
l_cz_imp_rules.RUN_ID,
l_cz_imp_rules.DISPOSITION,
l_cz_imp_rules.REC_STATUS,
l_cz_imp_rules.FSK_DEVL_PROJECT,
l_cz_imp_rules.FSK_LOCALIZED_TEXT_1,
l_cz_imp_rules.FSK_LOCALIZED_TEXT_2,
l_cz_imp_rules.IMPORT_PROG_VERSION,
l_cz_imp_rules.FSK_COMPONENT_ID,
l_cz_imp_rules.FSK_MODEL_REF_EXPL_ID
);
fnd_file.put_line(FND_FILE.LOG,'Unexpected Error in build_and_insert_rule '||SQLERRM);
fnd_file.put_line(FND_FILE.LOG,'Other Error in build_and_insert_rule '||SQLERRM);
END build_and_insert_rule;
SELECT rule_id,
published_flag
FROM okc_xprt_rule_hdrs_all
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID ;
SELECT r.template_id
FROM OKC_TERMS_TEMPLATES_ALL t,
okc_xprt_template_rules r
WHERE r.template_id = t.template_id
AND t.status_code IN ('APPROVED','ON_HOLD')
AND t.contract_expert_enabled = 'Y'
AND NVL(r.deleted_flag,'N') = 'N'
AND r.rule_id = p_rule_id
UNION ALL
-- Org Wide Rules
SELECT t.template_id
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 NVL(r.org_wide_flag,'N') = 'Y'
AND r.rule_id = p_rule_id ;
CURSOR csr_templates_to_delete(p_rule_id IN NUMBER) IS
-- Templates already pushed to CZ in previous publication
-- Changed for R12
SELECT SUBSTR(orig_sys_ref, INSTR(orig_sys_ref,':',-1,3)+1,
(INSTR(orig_sys_ref,':',-1,2) - (INSTR(orig_sys_ref,':',-1,3)+1))
)
FROM cz_rules
WHERE SUBSTR(orig_sys_ref,INSTR(orig_sys_ref,':',-1,1)+1) = to_char(p_rule_id)
AND rule_type = 200 -- Added for Bug 5005681
AND rule_id = persistent_rule_id
AND deleted_flag = '0'
MINUS
-- list of templates to which the rule is currently attached
(
-- Local Rules
SELECT to_char(r.template_id)
FROM OKC_TERMS_TEMPLATES_ALL t,
okc_xprt_template_rules r
WHERE r.template_id = t.template_id
AND t.status_code IN ('APPROVED','ON_HOLD')
AND t.contract_expert_enabled = 'Y'
AND NVL(r.deleted_flag,'N') = 'N'
AND r.rule_id = p_rule_id
UNION ALL
-- Org Wide Rules
SELECT to_char(t.template_id)
FROM OKC_TERMS_TEMPLATES_ALL t,
okc_xprt_rule_hdrs_all r
--,okc_xprt_template_rules tr -- Added for Bug 5005681
WHERE t.org_id = r.org_id
--AND tr.template_id = t.template_id -- Added for Bug 5005681
--AND tr.rule_id = r.rule_id -- Added for Bug 5005681
AND t.intent = r.intent
AND t.contract_expert_enabled = 'Y'
AND t.status_code IN ('APPROVED','ON_HOLD')
AND NVL(r.org_wide_flag,'N') = 'Y'
AND r.rule_id = p_rule_id
);
SELECT cz_xfr_run_infos_s.NEXTVAL
FROM dual;
build_and_insert_rule
(
p_rule_id => l_rule_id,
p_template_id => l_template_id,
p_run_id => x_run_id,
p_mode => 'P', -- Publish
x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count
);
OPEN csr_templates_to_delete(p_rule_id => l_rule_id);
FETCH csr_templates_to_delete INTO l_template_id;
EXIT WHEN csr_templates_to_delete%NOTFOUND;
build_and_insert_rule
(
p_rule_id => l_rule_id,
p_template_id => l_template_id,
p_run_id => x_run_id,
p_mode => 'D', -- to be deleted
x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count
);
END LOOP; -- csr_templates_to_delete
CLOSE csr_templates_to_delete; -- old templates
SELECT rule_id
FROM okc_xprt_rule_hdrs_all
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID ;
and delete the same
*/
CURSOR csr_templates_to_delete(p_rule_id IN NUMBER) IS
-- Templates already pushed to CZ in previous publication
-- Changed for R12
-- Updated the substr for bug 4676800
SELECT SUBSTR(orig_sys_ref, INSTR(orig_sys_ref,':',-1,3)+1,
(INSTR(orig_sys_ref,':',-1,2) - (INSTR(orig_sys_ref,':',-1,3)+1))
)
FROM cz_rules
WHERE SUBSTR(orig_sys_ref,INSTR(orig_sys_ref,':',-1,1)+1) = to_char(p_rule_id)
AND rule_id = persistent_rule_id
AND deleted_flag = '0'
AND rule_type = 200; --Added for perf Bug#5032335
SELECT cz_xfr_run_infos_s.NEXTVAL
FROM dual;
OPEN csr_templates_to_delete(p_rule_id => l_rule_id);
FETCH csr_templates_to_delete INTO l_template_id;
EXIT WHEN csr_templates_to_delete%NOTFOUND;
build_and_insert_rule
(
p_rule_id => l_rule_id,
p_template_id => l_template_id,
p_run_id => x_run_id,
p_mode => 'D', -- to be deleted
x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count
);
END LOOP; -- csr_templates_to_delete
CLOSE csr_templates_to_delete; -- old templates
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 NVL(r.deleted_flag,'N') = 'N'
AND r.template_id = p_template_id
UNION ALL
-- Global Active Rules for the template
SELECT r.rule_id
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 NVL(r.org_wide_flag,'N') = 'Y'
AND r.status_code = 'ACTIVE'
AND t.template_id = p_template_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 NVL(r.deleted_flag,'N') = 'N'
AND r.template_id = p_parent_tmpl_id
MINUS
-- current local rules on revision template
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 NVL(r.deleted_flag,'N') = 'N'
AND r.template_id = p_template_id ;
SELECT parent_template_id
FROM OKC_TERMS_TEMPLATES_ALL
WHERE template_id = p_template_id ;
SELECT cz_xfr_run_infos_s.NEXTVAL
FROM dual;
build_and_insert_rule
(
p_rule_id => l_rule_id,
p_template_id => l_template_id,
p_run_id => x_run_id,
p_mode => 'P',
x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count
);
build_and_insert_rule
(
p_rule_id => l_rule_id,
p_template_id => l_template_id,
p_run_id => x_run_id,
p_mode => 'D',
x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count
);
SELECT devl_project_id
FROM cz_devl_projects
WHERE orig_sys_ref = G_VARIABLE_MODEL_OSR||p_intent
AND devl_project_id = persistent_project_id
AND deleted_flag = 0;
SELECT devl_project_id
FROM cz_devl_projects
WHERE orig_sys_ref = G_CLAUSE_MODEL_OSR||p_org_id||':'||p_intent
AND devl_project_id = persistent_project_id
AND deleted_flag = 0;
SELECT 'X'
FROM cz_rules
WHERE rule_type = 300 -- Extension rule
AND devl_project_id = p_devl_project_id
AND rule_id = persistent_rule_id
AND deleted_flag = 0;
SELECT distinct intent, org_id
FROM okc_xprt_rule_hdrs_all r
WHERE r.request_id = fnd_global.conc_request_id;
SELECT devl_project_id, orig_sys_ref
FROM cz_devl_projects
WHERE orig_sys_ref = G_VARIABLE_MODEL_OSR||l_intent
AND devl_project_id = persistent_project_id
AND deleted_flag = 0;
SELECT devl_project_id, orig_sys_ref
FROM cz_devl_projects
WHERE orig_sys_ref = G_CLAUSE_MODEL_OSR||to_char(l_org_id)||':'||l_intent
AND devl_project_id = persistent_project_id
AND deleted_flag = 0;
SELECT L.LANGUAGE_CODE
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG IN ('I', 'B');
l_deleted_flag cz_imp_rules.deleted_flag%TYPE;
INSERT INTO CZ_IMP_LOCALIZED_TEXTS
(
LAST_UPDATE_LOGIN,
LOCALE_ID,
LOCALIZED_STR,
INTL_TEXT_ID,
CREATION_DATE,
LAST_UPDATE_DATE,
DELETED_FLAG,
EFF_FROM,
EFF_TO,
CREATED_BY,
LAST_UPDATED_BY,
SECURITY_MASK,
EFF_MASK,
CHECKOUT_USER,
LANGUAGE,
ORIG_SYS_REF,
SOURCE_LANG,
RUN_ID,
REC_STATUS,
DISPOSITION,
MODEL_ID,
FSK_DEVLPROJECT_1_1,
MESSAGE,
SEEDED_FLAG
)
VALUES
(
FND_GLOBAL.LOGIN_ID, --LAST_UPDATE_LOGIN
NULL, -- LOCALE_ID
l_rule_name, --LOCALIZED_STR -
NULL, -- INTL_TEXT_ID
SYSDATE, -- CREATION_DATE
SYSDATE, -- LAST_UPDATE_DATE
'0', -- DELETED_FLAG
NULL, -- EFF_FROM
NULL, -- EFF_TO
FND_GLOBAL.USER_ID, -- CREATED_BY
FND_GLOBAL.USER_ID, -- LAST_UPDATED_BY
NULL, -- SECURITY_MASK
NULL, -- EFF_MASK
NULL, -- CHECKOUT_USER
l_language, --LANGUAGE
G_XTN_RULE_OSR||l_intent, --ORIG_SYS_REF
USERENV('LANG'), --SOURCE_LANG
p_run_id, -- RUN_ID
NULL, -- REC_STATUS
NULL, -- DISPOSITION
l_model_id, -- MODEL_ID
l_model_osr, --FSK_DEVLPROJECT_1_1
NULL, -- MESSAGE
NULL -- SEEDED_FLAG
);
l_cz_imp_rules.LAST_UPDATE_DATE := SYSDATE;
l_cz_imp_rules.DELETED_FLAG := 0; -- check
l_cz_imp_rules.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
l_cz_imp_rules.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
INSERT INTO cz_imp_rules
(
RULE_ID,
SUB_CONS_ID,
REASON_ID,
AMOUNT_ID,
GRID_ID,
RULE_FOLDER_ID,
DEVL_PROJECT_ID,
INVALID_FLAG,
DESC_TEXT,
NAME,
ANTECEDENT_ID,
CONSEQUENT_ID,
RULE_TYPE,
EXPR_RULE_TYPE,
COMPONENT_ID,
REASON_TYPE,
DISABLED_FLAG,
ORIG_SYS_REF,
CREATION_DATE,
LAST_UPDATE_DATE,
DELETED_FLAG,
EFF_FROM,
EFF_TO,
CREATED_BY,
LAST_UPDATED_BY,
SECURITY_MASK,
EFF_MASK,
CHECKOUT_USER,
LAST_UPDATE_LOGIN,
EFFECTIVE_USAGE_MASK,
SEQ_NBR,
EFFECTIVE_FROM,
EFFECTIVE_UNTIL,
PERSISTENT_RULE_ID,
EFFECTIVITY_SET_ID,
RULE_FOLDER_TYPE,
UNSATISFIED_MSG_ID,
UNSATISFIED_MSG_SOURCE,
SIGNATURE_ID,
TEMPLATE_PRIMITIVE_FLAG,
PRESENTATION_FLAG,
TEMPLATE_TOKEN,
RULE_TEXT,
NOTES,
CLASS_NAME,
INSTANTIATION_SCOPE,
MODEL_REF_EXPL_ID,
MUTABLE_FLAG,
SEEDED_FLAG,
UI_DEF_ID,
UI_PAGE_ID,
UI_PAGE_ELEMENT_ID,
MESSAGE,
RUN_ID,
DISPOSITION,
REC_STATUS,
FSK_DEVL_PROJECT,
FSK_LOCALIZED_TEXT_1,
FSK_LOCALIZED_TEXT_2,
IMPORT_PROG_VERSION,
FSK_COMPONENT_ID,
FSK_MODEL_REF_EXPL_ID
)
VALUES
(
l_cz_imp_rules.RULE_ID,
l_cz_imp_rules.SUB_CONS_ID,
l_cz_imp_rules.REASON_ID,
l_cz_imp_rules.AMOUNT_ID,
l_cz_imp_rules.GRID_ID,
l_cz_imp_rules.RULE_FOLDER_ID,
l_cz_imp_rules.DEVL_PROJECT_ID,
l_cz_imp_rules.INVALID_FLAG,
l_cz_imp_rules.DESC_TEXT,
l_cz_imp_rules.NAME,
l_cz_imp_rules.ANTECEDENT_ID,
l_cz_imp_rules.CONSEQUENT_ID,
l_cz_imp_rules.RULE_TYPE,
l_cz_imp_rules.EXPR_RULE_TYPE,
l_cz_imp_rules.COMPONENT_ID,
l_cz_imp_rules.REASON_TYPE,
l_cz_imp_rules.DISABLED_FLAG,
l_cz_imp_rules.ORIG_SYS_REF,
l_cz_imp_rules.CREATION_DATE,
l_cz_imp_rules.LAST_UPDATE_DATE,
l_cz_imp_rules.DELETED_FLAG,
l_cz_imp_rules.EFF_FROM,
l_cz_imp_rules.EFF_TO,
l_cz_imp_rules.CREATED_BY,
l_cz_imp_rules.LAST_UPDATED_BY,
l_cz_imp_rules.SECURITY_MASK,
l_cz_imp_rules.EFF_MASK,
l_cz_imp_rules.CHECKOUT_USER,
l_cz_imp_rules.LAST_UPDATE_LOGIN,
l_cz_imp_rules.EFFECTIVE_USAGE_MASK,
l_cz_imp_rules.SEQ_NBR,
l_cz_imp_rules.EFFECTIVE_FROM,
l_cz_imp_rules.EFFECTIVE_UNTIL,
l_cz_imp_rules.PERSISTENT_RULE_ID,
l_cz_imp_rules.EFFECTIVITY_SET_ID,
l_cz_imp_rules.RULE_FOLDER_TYPE,
l_cz_imp_rules.UNSATISFIED_MSG_ID,
l_cz_imp_rules.UNSATISFIED_MSG_SOURCE,
l_cz_imp_rules.SIGNATURE_ID,
l_cz_imp_rules.TEMPLATE_PRIMITIVE_FLAG,
l_cz_imp_rules.PRESENTATION_FLAG,
l_cz_imp_rules.TEMPLATE_TOKEN,
--l_cz_imp_rules.RULE_TEXT,
G_XTN_RULE_TEXT,
l_cz_imp_rules.NOTES,
l_cz_imp_rules.CLASS_NAME,
l_cz_imp_rules.INSTANTIATION_SCOPE,
l_cz_imp_rules.MODEL_REF_EXPL_ID,
l_cz_imp_rules.MUTABLE_FLAG,
l_cz_imp_rules.SEEDED_FLAG,
l_cz_imp_rules.UI_DEF_ID,
l_cz_imp_rules.UI_PAGE_ID,
l_cz_imp_rules.UI_PAGE_ELEMENT_ID,
l_cz_imp_rules.MESSAGE,
l_cz_imp_rules.RUN_ID,
l_cz_imp_rules.DISPOSITION,
l_cz_imp_rules.REC_STATUS,
l_cz_imp_rules.FSK_DEVL_PROJECT,
l_cz_imp_rules.FSK_LOCALIZED_TEXT_1,
l_cz_imp_rules.FSK_LOCALIZED_TEXT_2,
l_cz_imp_rules.IMPORT_PROG_VERSION,
l_cz_imp_rules.FSK_COMPONENT_ID,
l_cz_imp_rules.FSK_MODEL_REF_EXPL_ID
);
'170: After Insert into cz_imp_rules' || '='||l_intent);
CLOSE csr_intents; -- Insert the Extension Rule for this Intent
SELECT intent,org_id
FROM OKC_TERMS_TEMPLATES_ALL t
WHERE t.template_id = p_template_id;
SELECT devl_project_id, orig_sys_ref
FROM cz_devl_projects
WHERE orig_sys_ref = G_VARIABLE_MODEL_OSR||l_intent
AND devl_project_id = persistent_project_id
AND deleted_flag = 0;
SELECT devl_project_id, orig_sys_ref
FROM cz_devl_projects
WHERE orig_sys_ref = G_CLAUSE_MODEL_OSR||l_org_id||':'||l_intent
AND devl_project_id = persistent_project_id
AND deleted_flag = 0;
SELECT L.LANGUAGE_CODE
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG IN ('I', 'B');
l_deleted_flag cz_imp_rules.deleted_flag%TYPE;
INSERT INTO CZ_IMP_LOCALIZED_TEXTS
(
LAST_UPDATE_LOGIN,
LOCALE_ID,
LOCALIZED_STR,
INTL_TEXT_ID,
CREATION_DATE,
LAST_UPDATE_DATE,
DELETED_FLAG,
EFF_FROM,
EFF_TO,
CREATED_BY,
LAST_UPDATED_BY,
SECURITY_MASK,
EFF_MASK,
CHECKOUT_USER,
LANGUAGE,
ORIG_SYS_REF,
SOURCE_LANG,
RUN_ID,
REC_STATUS,
DISPOSITION,
MODEL_ID,
FSK_DEVLPROJECT_1_1,
MESSAGE,
SEEDED_FLAG
)
VALUES
(
FND_GLOBAL.LOGIN_ID, --LAST_UPDATE_LOGIN
NULL, -- LOCALE_ID
l_rule_name, --LOCALIZED_STR -
NULL, -- INTL_TEXT_ID
SYSDATE, -- CREATION_DATE
SYSDATE, -- LAST_UPDATE_DATE
'0', -- DELETED_FLAG
NULL, -- EFF_FROM
NULL, -- EFF_TO
FND_GLOBAL.USER_ID, -- CREATED_BY
FND_GLOBAL.USER_ID, -- LAST_UPDATED_BY
NULL, -- SECURITY_MASK
NULL, -- EFF_MASK
NULL, -- CHECKOUT_USER
l_language, --LANGUAGE
G_XTN_RULE_OSR||l_intent, --ORIG_SYS_REF
USERENV('LANG'), --SOURCE_LANG
p_run_id, -- RUN_ID
NULL, -- REC_STATUS
NULL, -- DISPOSITION
l_model_id, -- MODEL_ID
l_model_osr, --FSK_DEVLPROJECT_1_1
NULL, -- MESSAGE
NULL -- SEEDED_FLAG
);
l_cz_imp_rules.LAST_UPDATE_DATE := SYSDATE;
l_cz_imp_rules.DELETED_FLAG := 0; -- check
l_cz_imp_rules.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
l_cz_imp_rules.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
INSERT INTO cz_imp_rules
(
RULE_ID,
SUB_CONS_ID,
REASON_ID,
AMOUNT_ID,
GRID_ID,
RULE_FOLDER_ID,
DEVL_PROJECT_ID,
INVALID_FLAG,
DESC_TEXT,
NAME,
ANTECEDENT_ID,
CONSEQUENT_ID,
RULE_TYPE,
EXPR_RULE_TYPE,
COMPONENT_ID,
REASON_TYPE,
DISABLED_FLAG,
ORIG_SYS_REF,
CREATION_DATE,
LAST_UPDATE_DATE,
DELETED_FLAG,
EFF_FROM,
EFF_TO,
CREATED_BY,
LAST_UPDATED_BY,
SECURITY_MASK,
EFF_MASK,
CHECKOUT_USER,
LAST_UPDATE_LOGIN,
EFFECTIVE_USAGE_MASK,
SEQ_NBR,
EFFECTIVE_FROM,
EFFECTIVE_UNTIL,
PERSISTENT_RULE_ID,
EFFECTIVITY_SET_ID,
RULE_FOLDER_TYPE,
UNSATISFIED_MSG_ID,
UNSATISFIED_MSG_SOURCE,
SIGNATURE_ID,
TEMPLATE_PRIMITIVE_FLAG,
PRESENTATION_FLAG,
TEMPLATE_TOKEN,
RULE_TEXT,
NOTES,
CLASS_NAME,
INSTANTIATION_SCOPE,
MODEL_REF_EXPL_ID,
MUTABLE_FLAG,
SEEDED_FLAG,
UI_DEF_ID,
UI_PAGE_ID,
UI_PAGE_ELEMENT_ID,
MESSAGE,
RUN_ID,
DISPOSITION,
REC_STATUS,
FSK_DEVL_PROJECT,
FSK_LOCALIZED_TEXT_1,
FSK_LOCALIZED_TEXT_2,
IMPORT_PROG_VERSION,
FSK_COMPONENT_ID,
FSK_MODEL_REF_EXPL_ID
)
VALUES
(
l_cz_imp_rules.RULE_ID,
l_cz_imp_rules.SUB_CONS_ID,
l_cz_imp_rules.REASON_ID,
l_cz_imp_rules.AMOUNT_ID,
l_cz_imp_rules.GRID_ID,
l_cz_imp_rules.RULE_FOLDER_ID,
l_cz_imp_rules.DEVL_PROJECT_ID,
l_cz_imp_rules.INVALID_FLAG,
l_cz_imp_rules.DESC_TEXT,
l_cz_imp_rules.NAME,
l_cz_imp_rules.ANTECEDENT_ID,
l_cz_imp_rules.CONSEQUENT_ID,
l_cz_imp_rules.RULE_TYPE,
l_cz_imp_rules.EXPR_RULE_TYPE,
l_cz_imp_rules.COMPONENT_ID,
l_cz_imp_rules.REASON_TYPE,
l_cz_imp_rules.DISABLED_FLAG,
l_cz_imp_rules.ORIG_SYS_REF,
l_cz_imp_rules.CREATION_DATE,
l_cz_imp_rules.LAST_UPDATE_DATE,
l_cz_imp_rules.DELETED_FLAG,
l_cz_imp_rules.EFF_FROM,
l_cz_imp_rules.EFF_TO,
l_cz_imp_rules.CREATED_BY,
l_cz_imp_rules.LAST_UPDATED_BY,
l_cz_imp_rules.SECURITY_MASK,
l_cz_imp_rules.EFF_MASK,
l_cz_imp_rules.CHECKOUT_USER,
l_cz_imp_rules.LAST_UPDATE_LOGIN,
l_cz_imp_rules.EFFECTIVE_USAGE_MASK,
l_cz_imp_rules.SEQ_NBR,
l_cz_imp_rules.EFFECTIVE_FROM,
l_cz_imp_rules.EFFECTIVE_UNTIL,
l_cz_imp_rules.PERSISTENT_RULE_ID,
l_cz_imp_rules.EFFECTIVITY_SET_ID,
l_cz_imp_rules.RULE_FOLDER_TYPE,
l_cz_imp_rules.UNSATISFIED_MSG_ID,
l_cz_imp_rules.UNSATISFIED_MSG_SOURCE,
l_cz_imp_rules.SIGNATURE_ID,
l_cz_imp_rules.TEMPLATE_PRIMITIVE_FLAG,
l_cz_imp_rules.PRESENTATION_FLAG,
l_cz_imp_rules.TEMPLATE_TOKEN,
--l_cz_imp_rules.RULE_TEXT,
G_XTN_RULE_TEXT,
l_cz_imp_rules.NOTES,
l_cz_imp_rules.CLASS_NAME,
l_cz_imp_rules.INSTANTIATION_SCOPE,
l_cz_imp_rules.MODEL_REF_EXPL_ID,
l_cz_imp_rules.MUTABLE_FLAG,
l_cz_imp_rules.SEEDED_FLAG,
l_cz_imp_rules.UI_DEF_ID,
l_cz_imp_rules.UI_PAGE_ID,
l_cz_imp_rules.UI_PAGE_ELEMENT_ID,
l_cz_imp_rules.MESSAGE,
l_cz_imp_rules.RUN_ID,
l_cz_imp_rules.DISPOSITION,
l_cz_imp_rules.REC_STATUS,
l_cz_imp_rules.FSK_DEVL_PROJECT,
l_cz_imp_rules.FSK_LOCALIZED_TEXT_1,
l_cz_imp_rules.FSK_LOCALIZED_TEXT_2,
l_cz_imp_rules.IMPORT_PROG_VERSION,
l_cz_imp_rules.FSK_COMPONENT_ID,
l_cz_imp_rules.FSK_MODEL_REF_EXPL_ID
);
'170: After Insert into cz_imp_rules' || '='||l_intent);
CLOSE csr_intents; -- Insert the Extension Rule for this Intent