[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_cz_imp_devl_project_rec.LAST_UPDATE_DATE:= SYSDATE;
l_cz_imp_devl_project_rec.DELETED_FLAG:= '0'; -- '0' Not deleted
l_cz_imp_devl_project_rec.LAST_UPDATED_BY:= FND_GLOBAL.USER_ID;
l_cz_imp_devl_project_rec.LAST_UPDATE_LOGIN:= FND_GLOBAL.LOGIN_ID;
INSERT INTO cz_imp_devl_project
(
DEVL_PROJECT_ID,
INTL_TEXT_ID,
ORGANIZATION_ID,
NAME,
GSL_FILENAME,
TOP_ITEM_ID,
VERSION,
EXPLOSION_TYPE,
DESC_TEXT,
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,
RUN_ID,
REC_STATUS,
DISPOSITION,
FSK_INTLTEXT_1_1,
MODEL_ID,
PLAN_LEVEL,
PERSISTENT_PROJECT_ID,
MODEL_TYPE,
INVENTORY_ITEM_ID,
PRODUCT_KEY,
LAST_UPDATE_LOGIN,
BOM_CAPTION_RULE_ID,
NONBOM_CAPTION_RULE_ID,
SEEDED_FLAG
)
VALUES
(
l_cz_imp_devl_project_rec.DEVL_PROJECT_ID,
l_cz_imp_devl_project_rec.INTL_TEXT_ID,
l_cz_imp_devl_project_rec.ORGANIZATION_ID,
l_cz_imp_devl_project_rec.NAME,
l_cz_imp_devl_project_rec.GSL_FILENAME,
l_cz_imp_devl_project_rec.TOP_ITEM_ID,
l_cz_imp_devl_project_rec.VERSION,
l_cz_imp_devl_project_rec.EXPLOSION_TYPE,
l_cz_imp_devl_project_rec.DESC_TEXT,
l_cz_imp_devl_project_rec.ORIG_SYS_REF,
l_cz_imp_devl_project_rec.CREATION_DATE,
l_cz_imp_devl_project_rec.LAST_UPDATE_DATE,
l_cz_imp_devl_project_rec.DELETED_FLAG,
l_cz_imp_devl_project_rec.EFF_FROM,
l_cz_imp_devl_project_rec.EFF_TO,
l_cz_imp_devl_project_rec.CREATED_BY,
l_cz_imp_devl_project_rec.LAST_UPDATED_BY,
l_cz_imp_devl_project_rec.SECURITY_MASK,
l_cz_imp_devl_project_rec.EFF_MASK,
l_cz_imp_devl_project_rec.CHECKOUT_USER,
l_cz_imp_devl_project_rec.RUN_ID,
l_cz_imp_devl_project_rec.REC_STATUS,
l_cz_imp_devl_project_rec.DISPOSITION,
l_cz_imp_devl_project_rec.FSK_INTLTEXT_1_1,
l_cz_imp_devl_project_rec.MODEL_ID,
l_cz_imp_devl_project_rec.PLAN_LEVEL,
l_cz_imp_devl_project_rec.PERSISTENT_PROJECT_ID,
l_cz_imp_devl_project_rec.MODEL_TYPE,
l_cz_imp_devl_project_rec.INVENTORY_ITEM_ID,
l_cz_imp_devl_project_rec.PRODUCT_KEY,
l_cz_imp_devl_project_rec.LAST_UPDATE_LOGIN,
l_cz_imp_devl_project_rec.BOM_CAPTION_RULE_ID,
l_cz_imp_devl_project_rec.NONBOM_CAPTION_RULE_ID,
l_cz_imp_devl_project_rec.SEEDED_FLAG
);
SELECT L.LANGUAGE_CODE
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG IN ('I', 'B');
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
p_template_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_TEMPLATE_MODEL_TOPNODE_OSR||p_org_id||':'||p_intent||':'||p_template_id, --ORIG_SYS_REF
USERENV('LANG'), --SOURCE_LANG
G_RUN_ID, -- RUN_ID
NULL, -- REC_STATUS
NULL, -- DISPOSITION
NULL, -- MODEL_ID
G_TEMPLATE_MODEL_OSR||p_org_id||':'||p_intent||':'||p_template_id, --FSK_DEVLPROJECT_1_1
NULL, -- MESSAGE
NULL -- SEEDED_FLAG
);
l_cz_imp_ps_nodes_rec.DELETED_FLAG := 0;
l_cz_imp_ps_nodes_rec.LAST_UPDATE_DATE:= SYSDATE;
l_cz_imp_ps_nodes_rec.LAST_UPDATED_BY:= FND_GLOBAL.USER_ID;
l_cz_imp_ps_nodes_rec.MINIMUM_SELECTED:= NULL;
l_cz_imp_ps_nodes_rec.MAXIMUM_SELECTED:= NULL;
l_cz_imp_ps_nodes_rec.LAST_UPDATE_LOGIN:= FND_GLOBAL.LOGIN_ID;
INSERT INTO cz_imp_ps_nodes
(
PS_NODE_ID,
DEVL_PROJECT_ID,
FROM_POPULATOR_ID,
PROPERTY_BACKPTR,
ITEM_TYPE_BACKPTR,
INTL_TEXT_ID,
SUB_CONS_ID,
ORGANIZATION_ID,
ITEM_ID,
EXPLOSION_TYPE,
NAME,
ORIG_SYS_REF,
RESOURCE_FLAG,
TOP_ITEM_ID,
INITIAL_VALUE,
PARENT_ID,
MINIMUM,
MAXIMUM,
PS_NODE_TYPE,
FEATURE_TYPE,
PRODUCT_FLAG,
REFERENCE_ID,
MULTI_CONFIG_FLAG,
ORDER_SEQ_FLAG,
SYSTEM_NODE_FLAG,
TREE_SEQ,
COUNTED_OPTIONS_FLAG,
UI_OMIT,
UI_SECTION,
BOM_TREATMENT,
RUN_ID,
REC_STATUS,
DISPOSITION,
DELETED_FLAG ,
EFF_FROM,
EFF_TO,
EFF_MASK,
USER_STR01,
USER_STR02,
USER_STR03,
USER_STR04,
USER_NUM01,
USER_NUM02,
USER_NUM03,
USER_NUM04,
CHECKOUT_USER,
CREATION_DATE,
LAST_UPDATE_DATE,
CREATED_BY,
LAST_UPDATED_BY,
SECURITY_MASK,
FSK_INTLTEXT_1_1,
FSK_INTLTEXT_1_EXT,
FSK_ITEMMASTER_2_1,
FSK_ITEMMASTER_2_EXT,
FSK_PSNODE_3_1,
FSK_PSNODE_3_EXT,
FSK_PSNODE_4_1,
FSK_PSNODE_4_EXT,
FSK_DEVLPROJECT_5_1,
FSK_DEVLPROJECT_5_EXT,
COMPONENT_SEQUENCE_ID,
COMPONENT_CODE,
PLAN_LEVEL,
BOM_ITEM_TYPE,
SO_ITEM_TYPE_CODE,
MINIMUM_SELECTED,
MAXIMUM_SELECTED,
BOM_REQUIRED,
MUTUALLY_EXCLUSIVE_OPTIONS,
OPTIONAL,
FSK_EXPLNODE_1_1,
FSK_PSNODE_6_1,
EFFECTIVE_FROM,
EFFECTIVE_UNTIL,
EFFECTIVE_USAGE_MASK,
EFFECTIVITY_SET_ID,
FSK_EFFSET_7_1,
DECIMAL_QTY_FLAG,
QUOTEABLE_FLAG,
PRIMARY_UOM_CODE,
COMPONENT_SEQUENCE_PATH,
BOM_SORT_ORDER,
IB_TRACKABLE,
LAST_UPDATE_LOGIN,
INITIAL_NUM_VALUE,
SRC_APPLICATION_ID,
FSK_ITEMMASTER_2_2,
INSTANTIABLE_FLAG,
DISPLAY_IN_SUMMARY_FLAG
)
VALUES
(
l_cz_imp_ps_nodes_rec.PS_NODE_ID,
l_cz_imp_ps_nodes_rec.DEVL_PROJECT_ID,
l_cz_imp_ps_nodes_rec.FROM_POPULATOR_ID,
l_cz_imp_ps_nodes_rec.PROPERTY_BACKPTR,
l_cz_imp_ps_nodes_rec.ITEM_TYPE_BACKPTR,
l_cz_imp_ps_nodes_rec.INTL_TEXT_ID,
l_cz_imp_ps_nodes_rec.SUB_CONS_ID,
l_cz_imp_ps_nodes_rec.ORGANIZATION_ID,
l_cz_imp_ps_nodes_rec.ITEM_ID,
l_cz_imp_ps_nodes_rec.EXPLOSION_TYPE,
l_cz_imp_ps_nodes_rec.NAME,
l_cz_imp_ps_nodes_rec.ORIG_SYS_REF,
l_cz_imp_ps_nodes_rec.RESOURCE_FLAG,
l_cz_imp_ps_nodes_rec.TOP_ITEM_ID,
l_cz_imp_ps_nodes_rec.INITIAL_VALUE,
l_cz_imp_ps_nodes_rec.PARENT_ID,
l_cz_imp_ps_nodes_rec.MINIMUM,
l_cz_imp_ps_nodes_rec.MAXIMUM,
l_cz_imp_ps_nodes_rec.PS_NODE_TYPE,
l_cz_imp_ps_nodes_rec.FEATURE_TYPE,
l_cz_imp_ps_nodes_rec.PRODUCT_FLAG,
l_cz_imp_ps_nodes_rec.REFERENCE_ID,
l_cz_imp_ps_nodes_rec.MULTI_CONFIG_FLAG,
l_cz_imp_ps_nodes_rec.ORDER_SEQ_FLAG,
l_cz_imp_ps_nodes_rec.SYSTEM_NODE_FLAG,
l_cz_imp_ps_nodes_rec.TREE_SEQ,
l_cz_imp_ps_nodes_rec.COUNTED_OPTIONS_FLAG,
l_cz_imp_ps_nodes_rec.UI_OMIT,
l_cz_imp_ps_nodes_rec.UI_SECTION,
l_cz_imp_ps_nodes_rec.BOM_TREATMENT,
l_cz_imp_ps_nodes_rec.RUN_ID,
l_cz_imp_ps_nodes_rec.REC_STATUS,
l_cz_imp_ps_nodes_rec.DISPOSITION,
l_cz_imp_ps_nodes_rec.DELETED_FLAG ,
l_cz_imp_ps_nodes_rec.EFF_FROM,
l_cz_imp_ps_nodes_rec.EFF_TO,
l_cz_imp_ps_nodes_rec.EFF_MASK,
l_cz_imp_ps_nodes_rec.USER_STR01,
l_cz_imp_ps_nodes_rec.USER_STR02,
l_cz_imp_ps_nodes_rec.USER_STR03,
l_cz_imp_ps_nodes_rec.USER_STR04,
l_cz_imp_ps_nodes_rec.USER_NUM01,
l_cz_imp_ps_nodes_rec.USER_NUM02,
l_cz_imp_ps_nodes_rec.USER_NUM03,
l_cz_imp_ps_nodes_rec.USER_NUM04,
l_cz_imp_ps_nodes_rec.CHECKOUT_USER,
l_cz_imp_ps_nodes_rec.CREATION_DATE,
l_cz_imp_ps_nodes_rec.LAST_UPDATE_DATE,
l_cz_imp_ps_nodes_rec.CREATED_BY,
l_cz_imp_ps_nodes_rec.LAST_UPDATED_BY,
l_cz_imp_ps_nodes_rec.SECURITY_MASK,
l_cz_imp_ps_nodes_rec.FSK_INTLTEXT_1_1,
l_cz_imp_ps_nodes_rec.FSK_INTLTEXT_1_EXT,
l_cz_imp_ps_nodes_rec.FSK_ITEMMASTER_2_1,
l_cz_imp_ps_nodes_rec.FSK_ITEMMASTER_2_EXT,
l_cz_imp_ps_nodes_rec.FSK_PSNODE_3_1,
l_cz_imp_ps_nodes_rec.FSK_PSNODE_3_EXT,
l_cz_imp_ps_nodes_rec.FSK_PSNODE_4_1,
l_cz_imp_ps_nodes_rec.FSK_PSNODE_4_EXT,
l_cz_imp_ps_nodes_rec.FSK_DEVLPROJECT_5_1,
l_cz_imp_ps_nodes_rec.FSK_DEVLPROJECT_5_EXT,
l_cz_imp_ps_nodes_rec.COMPONENT_SEQUENCE_ID,
l_cz_imp_ps_nodes_rec.COMPONENT_CODE,
l_cz_imp_ps_nodes_rec.PLAN_LEVEL,
l_cz_imp_ps_nodes_rec.BOM_ITEM_TYPE,
l_cz_imp_ps_nodes_rec.SO_ITEM_TYPE_CODE,
l_cz_imp_ps_nodes_rec.MINIMUM_SELECTED,
l_cz_imp_ps_nodes_rec.MAXIMUM_SELECTED,
l_cz_imp_ps_nodes_rec.BOM_REQUIRED,
l_cz_imp_ps_nodes_rec.MUTUALLY_EXCLUSIVE_OPTIONS,
l_cz_imp_ps_nodes_rec.OPTIONAL,
l_cz_imp_ps_nodes_rec.FSK_EXPLNODE_1_1,
l_cz_imp_ps_nodes_rec.FSK_PSNODE_6_1,
l_cz_imp_ps_nodes_rec.EFFECTIVE_FROM,
l_cz_imp_ps_nodes_rec.EFFECTIVE_UNTIL,
l_cz_imp_ps_nodes_rec.EFFECTIVE_USAGE_MASK,
l_cz_imp_ps_nodes_rec.EFFECTIVITY_SET_ID,
l_cz_imp_ps_nodes_rec.FSK_EFFSET_7_1,
l_cz_imp_ps_nodes_rec.DECIMAL_QTY_FLAG,
l_cz_imp_ps_nodes_rec.QUOTEABLE_FLAG,
l_cz_imp_ps_nodes_rec.PRIMARY_UOM_CODE,
l_cz_imp_ps_nodes_rec.COMPONENT_SEQUENCE_PATH,
l_cz_imp_ps_nodes_rec.BOM_SORT_ORDER,
l_cz_imp_ps_nodes_rec.IB_TRACKABLE,
l_cz_imp_ps_nodes_rec.LAST_UPDATE_LOGIN,
l_cz_imp_ps_nodes_rec.INITIAL_NUM_VALUE,
l_cz_imp_ps_nodes_rec.SRC_APPLICATION_ID,
l_cz_imp_ps_nodes_rec.FSK_ITEMMASTER_2_2,
l_cz_imp_ps_nodes_rec.INSTANTIABLE_FLAG,
l_cz_imp_ps_nodes_rec.DISPLAY_IN_SUMMARY_FLAG
);
SELECT q.question_id,
DECODE(q.prompt,NULL,q.question_name,q.prompt),
q.language,
q.source_lang
FROM okc_xprt_questions_tl q,
okc_xprt_question_orders o
WHERE q.question_id = o.question_id
AND o.question_rule_status IN ('ACTIVE','PENDINGPUB')
AND o.template_id = p_template_id;
OKC content template has a seeded display rule which only displays features if 'Selected'
is TRUE. Features with minimum as 0, will not be initially display in UI
CZ does not have any API or mechanism to hide Numeric or Boolean features using seeded
display rule.
If feature type is Option feature (LOV or Boolean) then
If Question Can be Ordered(independent) then
minimum = 1
Else
-- Question Cannot be Ordered (dependent)
minimum = 0
Else
-- Question datatype is Numeric
minimum = NULL
End If;
SELECT o.question_id,
o.sequence_num,
DECODE(q.question_datatype,'N',NULL,
DECODE(NVL(o.mandatory_flag,'N'),'Y',1,0)), -- For LOV or Boolean create 1/1 or 0/1
DECODE(q.question_datatype,'N',NULL,1), --For Decimal Feature, create MAX as NULL
q.value_set_name,
DECODE(q.question_datatype,'N',2,
0) --FEATURE_TYPE 2:Decimal 0:option
FROM okc_xprt_questions_b q,
okc_xprt_question_orders o
WHERE q.question_id = o.question_id
AND o.question_rule_status IN ('ACTIVE','PENDINGPUB')
AND o.template_id = p_template_id
ORDER BY sequence_num;
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
TmplQstName_tbl(i), --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
language_tbl(i), --LANGUAGE
G_TEMPLATE_MODEL_FEATURE_OSR||p_org_id||':'||
p_intent||':'||
p_derived_template_id||':'||
TmplQstId_tbl(i), -- ORIG_SYS_REF
sourceLang_tbl(i), --SOURCE_LANG
G_RUN_ID, -- RUN_ID
NULL, -- REC_STATUS
NULL, -- DISPOSITION
NULL, -- MODEL_ID
G_TEMPLATE_MODEL_OSR||p_org_id||':'||
p_intent||':'||
p_derived_template_id, -- FSK_DEVLPROJECT_1_1
NULL, -- MESSAGE
NULL -- SEEDED_FLAG
);
l_cz_imp_ps_nodes_rec.DELETED_FLAG := 0;
l_cz_imp_ps_nodes_rec.LAST_UPDATE_DATE:= SYSDATE;
l_cz_imp_ps_nodes_rec.LAST_UPDATED_BY:= FND_GLOBAL.USER_ID;
l_cz_imp_ps_nodes_rec.MINIMUM_SELECTED:= NULL;
l_cz_imp_ps_nodes_rec.MAXIMUM_SELECTED:= NULL;
l_cz_imp_ps_nodes_rec.LAST_UPDATE_LOGIN:= FND_GLOBAL.LOGIN_ID;
INSERT INTO cz_imp_ps_nodes
(
PS_NODE_ID,
DEVL_PROJECT_ID,
FROM_POPULATOR_ID,
PROPERTY_BACKPTR,
ITEM_TYPE_BACKPTR,
INTL_TEXT_ID,
SUB_CONS_ID,
ORGANIZATION_ID,
ITEM_ID,
EXPLOSION_TYPE,
NAME,
ORIG_SYS_REF,
RESOURCE_FLAG,
TOP_ITEM_ID,
INITIAL_VALUE,
PARENT_ID,
MINIMUM,
MAXIMUM,
PS_NODE_TYPE,
FEATURE_TYPE,
PRODUCT_FLAG,
REFERENCE_ID,
MULTI_CONFIG_FLAG,
ORDER_SEQ_FLAG,
SYSTEM_NODE_FLAG,
TREE_SEQ,
COUNTED_OPTIONS_FLAG,
UI_OMIT,
UI_SECTION,
BOM_TREATMENT,
RUN_ID,
REC_STATUS,
DISPOSITION,
DELETED_FLAG ,
EFF_FROM,
EFF_TO,
EFF_MASK,
USER_STR01,
USER_STR02,
USER_STR03,
USER_STR04,
USER_NUM01,
USER_NUM02,
USER_NUM03,
USER_NUM04,
CHECKOUT_USER,
CREATION_DATE,
LAST_UPDATE_DATE,
CREATED_BY,
LAST_UPDATED_BY,
SECURITY_MASK,
FSK_INTLTEXT_1_1,
FSK_INTLTEXT_1_EXT,
FSK_ITEMMASTER_2_1,
FSK_ITEMMASTER_2_EXT,
FSK_PSNODE_3_1,
FSK_PSNODE_3_EXT,
FSK_PSNODE_4_1,
FSK_PSNODE_4_EXT,
FSK_DEVLPROJECT_5_1,
FSK_DEVLPROJECT_5_EXT,
COMPONENT_SEQUENCE_ID,
COMPONENT_CODE,
PLAN_LEVEL,
BOM_ITEM_TYPE,
SO_ITEM_TYPE_CODE,
MINIMUM_SELECTED,
MAXIMUM_SELECTED,
BOM_REQUIRED,
MUTUALLY_EXCLUSIVE_OPTIONS,
OPTIONAL,
FSK_EXPLNODE_1_1,
FSK_PSNODE_6_1,
EFFECTIVE_FROM,
EFFECTIVE_UNTIL,
EFFECTIVE_USAGE_MASK,
EFFECTIVITY_SET_ID,
FSK_EFFSET_7_1,
DECIMAL_QTY_FLAG,
QUOTEABLE_FLAG,
PRIMARY_UOM_CODE,
COMPONENT_SEQUENCE_PATH,
BOM_SORT_ORDER,
IB_TRACKABLE,
LAST_UPDATE_LOGIN,
INITIAL_NUM_VALUE,
SRC_APPLICATION_ID,
FSK_ITEMMASTER_2_2,
INSTANTIABLE_FLAG,
DISPLAY_IN_SUMMARY_FLAG
)
VALUES
(
l_cz_imp_ps_nodes_rec.PS_NODE_ID,
l_cz_imp_ps_nodes_rec.DEVL_PROJECT_ID,
l_cz_imp_ps_nodes_rec.FROM_POPULATOR_ID,
l_cz_imp_ps_nodes_rec.PROPERTY_BACKPTR,
l_cz_imp_ps_nodes_rec.ITEM_TYPE_BACKPTR,
l_cz_imp_ps_nodes_rec.INTL_TEXT_ID,
l_cz_imp_ps_nodes_rec.SUB_CONS_ID,
l_cz_imp_ps_nodes_rec.ORGANIZATION_ID,
l_cz_imp_ps_nodes_rec.ITEM_ID,
l_cz_imp_ps_nodes_rec.EXPLOSION_TYPE,
l_cz_imp_ps_nodes_rec.NAME,
l_cz_imp_ps_nodes_rec.ORIG_SYS_REF,
l_cz_imp_ps_nodes_rec.RESOURCE_FLAG,
l_cz_imp_ps_nodes_rec.TOP_ITEM_ID,
l_cz_imp_ps_nodes_rec.INITIAL_VALUE,
l_cz_imp_ps_nodes_rec.PARENT_ID,
l_cz_imp_ps_nodes_rec.MINIMUM,
l_cz_imp_ps_nodes_rec.MAXIMUM,
l_cz_imp_ps_nodes_rec.PS_NODE_TYPE,
l_cz_imp_ps_nodes_rec.FEATURE_TYPE,
l_cz_imp_ps_nodes_rec.PRODUCT_FLAG,
l_cz_imp_ps_nodes_rec.REFERENCE_ID,
l_cz_imp_ps_nodes_rec.MULTI_CONFIG_FLAG,
l_cz_imp_ps_nodes_rec.ORDER_SEQ_FLAG,
l_cz_imp_ps_nodes_rec.SYSTEM_NODE_FLAG,
l_cz_imp_ps_nodes_rec.TREE_SEQ,
l_cz_imp_ps_nodes_rec.COUNTED_OPTIONS_FLAG,
l_cz_imp_ps_nodes_rec.UI_OMIT,
l_cz_imp_ps_nodes_rec.UI_SECTION,
l_cz_imp_ps_nodes_rec.BOM_TREATMENT,
l_cz_imp_ps_nodes_rec.RUN_ID,
l_cz_imp_ps_nodes_rec.REC_STATUS,
l_cz_imp_ps_nodes_rec.DISPOSITION,
l_cz_imp_ps_nodes_rec.DELETED_FLAG ,
l_cz_imp_ps_nodes_rec.EFF_FROM,
l_cz_imp_ps_nodes_rec.EFF_TO,
l_cz_imp_ps_nodes_rec.EFF_MASK,
l_cz_imp_ps_nodes_rec.USER_STR01,
l_cz_imp_ps_nodes_rec.USER_STR02,
l_cz_imp_ps_nodes_rec.USER_STR03,
l_cz_imp_ps_nodes_rec.USER_STR04,
l_cz_imp_ps_nodes_rec.USER_NUM01,
l_cz_imp_ps_nodes_rec.USER_NUM02,
l_cz_imp_ps_nodes_rec.USER_NUM03,
l_cz_imp_ps_nodes_rec.USER_NUM04,
l_cz_imp_ps_nodes_rec.CHECKOUT_USER,
l_cz_imp_ps_nodes_rec.CREATION_DATE,
l_cz_imp_ps_nodes_rec.LAST_UPDATE_DATE,
l_cz_imp_ps_nodes_rec.CREATED_BY,
l_cz_imp_ps_nodes_rec.LAST_UPDATED_BY,
l_cz_imp_ps_nodes_rec.SECURITY_MASK,
l_cz_imp_ps_nodes_rec.FSK_INTLTEXT_1_1,
l_cz_imp_ps_nodes_rec.FSK_INTLTEXT_1_EXT,
l_cz_imp_ps_nodes_rec.FSK_ITEMMASTER_2_1,
l_cz_imp_ps_nodes_rec.FSK_ITEMMASTER_2_EXT,
l_cz_imp_ps_nodes_rec.FSK_PSNODE_3_1,
l_cz_imp_ps_nodes_rec.FSK_PSNODE_3_EXT,
l_cz_imp_ps_nodes_rec.FSK_PSNODE_4_1,
l_cz_imp_ps_nodes_rec.FSK_PSNODE_4_EXT,
l_cz_imp_ps_nodes_rec.FSK_DEVLPROJECT_5_1,
l_cz_imp_ps_nodes_rec.FSK_DEVLPROJECT_5_EXT,
l_cz_imp_ps_nodes_rec.COMPONENT_SEQUENCE_ID,
l_cz_imp_ps_nodes_rec.COMPONENT_CODE,
l_cz_imp_ps_nodes_rec.PLAN_LEVEL,
l_cz_imp_ps_nodes_rec.BOM_ITEM_TYPE,
l_cz_imp_ps_nodes_rec.SO_ITEM_TYPE_CODE,
l_cz_imp_ps_nodes_rec.MINIMUM_SELECTED,
l_cz_imp_ps_nodes_rec.MAXIMUM_SELECTED,
l_cz_imp_ps_nodes_rec.BOM_REQUIRED,
l_cz_imp_ps_nodes_rec.MUTUALLY_EXCLUSIVE_OPTIONS,
l_cz_imp_ps_nodes_rec.OPTIONAL,
l_cz_imp_ps_nodes_rec.FSK_EXPLNODE_1_1,
l_cz_imp_ps_nodes_rec.FSK_PSNODE_6_1,
l_cz_imp_ps_nodes_rec.EFFECTIVE_FROM,
l_cz_imp_ps_nodes_rec.EFFECTIVE_UNTIL,
l_cz_imp_ps_nodes_rec.EFFECTIVE_USAGE_MASK,
l_cz_imp_ps_nodes_rec.EFFECTIVITY_SET_ID,
l_cz_imp_ps_nodes_rec.FSK_EFFSET_7_1,
l_cz_imp_ps_nodes_rec.DECIMAL_QTY_FLAG,
l_cz_imp_ps_nodes_rec.QUOTEABLE_FLAG,
l_cz_imp_ps_nodes_rec.PRIMARY_UOM_CODE,
l_cz_imp_ps_nodes_rec.COMPONENT_SEQUENCE_PATH,
l_cz_imp_ps_nodes_rec.BOM_SORT_ORDER,
l_cz_imp_ps_nodes_rec.IB_TRACKABLE,
l_cz_imp_ps_nodes_rec.LAST_UPDATE_LOGIN,
l_cz_imp_ps_nodes_rec.INITIAL_NUM_VALUE,
l_cz_imp_ps_nodes_rec.SRC_APPLICATION_ID,
l_cz_imp_ps_nodes_rec.FSK_ITEMMASTER_2_2,
l_cz_imp_ps_nodes_rec.INSTANTIABLE_FLAG,
l_cz_imp_ps_nodes_rec.DISPLAY_IN_SUMMARY_FLAG
);
SELECT validation_type
FROM fnd_flex_value_sets
WHERE flex_value_set_id = p_value_set_id;
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 NVL(description, flex_value_meaning),
flex_value_id, -- flex_value,
rownum
FROM fnd_flex_values_vl
WHERE flex_value_set_id = p_value_set_id
AND enabled_flag = 'Y'
AND SYSDATE BETWEEN NVL(start_date_active,SYSDATE) AND NVL(end_date_active,SYSDATE+1);
SELECT question_name
FROM okc_xprt_questions_tl
WHERE question_id = p_question_id
AND language = USERENV('LANG');
SELECT L.LANGUAGE_CODE
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG IN ('I', 'B');
l_sql_stmt := 'SELECT '||l_name_col||' , '||l_id_col||
' FROM '||l_table_name||' '||
l_additional_where_clause ;
l_sequence_sql_stmt := 'SELECT rownum FROM ( '||l_sql_stmt||' )' ;
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
NameList_tbl(i), --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_TEMPLATE_MODEL_OPTION_OSR||
p_org_id||':'||
p_intent||':'||
p_derived_template_id||':'||
p_question_id||':'||
IdList_tbl(i), --ORIG_SYS_REF
USERENV('LANG'), --SOURCE_LANG
G_RUN_ID, -- RUN_ID
NULL, -- REC_STATUS
NULL, -- DISPOSITION
NULL, -- MODEL_ID
G_TEMPLATE_MODEL_OSR||
p_org_id||':'||
p_intent||':'||
p_derived_template_id , -- FSK_DEVLPROJECT_1_1
NULL, -- MESSAGE
NULL -- SEEDED_FLAG
);
INSERT INTO cz_imp_ps_nodes
(
PS_NODE_ID,
DEVL_PROJECT_ID,
FROM_POPULATOR_ID,
PROPERTY_BACKPTR,
ITEM_TYPE_BACKPTR,
INTL_TEXT_ID,
SUB_CONS_ID,
ORGANIZATION_ID,
ITEM_ID,
EXPLOSION_TYPE,
NAME,
ORIG_SYS_REF,
RESOURCE_FLAG,
TOP_ITEM_ID,
INITIAL_VALUE,
PARENT_ID,
MINIMUM,
MAXIMUM,
PS_NODE_TYPE,
FEATURE_TYPE,
PRODUCT_FLAG,
REFERENCE_ID,
MULTI_CONFIG_FLAG,
ORDER_SEQ_FLAG,
SYSTEM_NODE_FLAG,
TREE_SEQ,
COUNTED_OPTIONS_FLAG,
UI_OMIT,
UI_SECTION,
BOM_TREATMENT,
RUN_ID,
REC_STATUS,
DISPOSITION,
DELETED_FLAG ,
EFF_FROM,
EFF_TO,
EFF_MASK,
USER_STR01,
USER_STR02,
USER_STR03,
USER_STR04,
USER_NUM01,
USER_NUM02,
USER_NUM03,
USER_NUM04,
CHECKOUT_USER,
CREATION_DATE,
LAST_UPDATE_DATE,
CREATED_BY,
LAST_UPDATED_BY,
SECURITY_MASK,
FSK_INTLTEXT_1_1,
FSK_INTLTEXT_1_EXT,
FSK_ITEMMASTER_2_1,
FSK_ITEMMASTER_2_EXT,
FSK_PSNODE_3_1,
FSK_PSNODE_3_EXT,
FSK_PSNODE_4_1,
FSK_PSNODE_4_EXT,
FSK_DEVLPROJECT_5_1,
FSK_DEVLPROJECT_5_EXT,
COMPONENT_SEQUENCE_ID,
COMPONENT_CODE,
PLAN_LEVEL,
BOM_ITEM_TYPE,
SO_ITEM_TYPE_CODE,
MINIMUM_SELECTED,
MAXIMUM_SELECTED,
BOM_REQUIRED,
MUTUALLY_EXCLUSIVE_OPTIONS,
OPTIONAL,
FSK_EXPLNODE_1_1,
FSK_PSNODE_6_1,
EFFECTIVE_FROM,
EFFECTIVE_UNTIL,
EFFECTIVE_USAGE_MASK,
EFFECTIVITY_SET_ID,
FSK_EFFSET_7_1,
DECIMAL_QTY_FLAG,
QUOTEABLE_FLAG,
PRIMARY_UOM_CODE,
COMPONENT_SEQUENCE_PATH,
BOM_SORT_ORDER,
IB_TRACKABLE,
LAST_UPDATE_LOGIN,
INITIAL_NUM_VALUE,
SRC_APPLICATION_ID,
FSK_ITEMMASTER_2_2,
INSTANTIABLE_FLAG,
DISPLAY_IN_SUMMARY_FLAG
)
VALUES
(
NULL, --PS_NODE_ID,
NULL, --DEVL_PROJECT_ID,
NULL, --FROM_POPULATOR_ID,
NULL, --PROPERTY_BACKPTR,
NULL, --ITEM_TYPE_BACKPTR,
NULL, --INTL_TEXT_ID,
NULL, --SUB_CONS_ID,
p_org_id, --ORGANIZATION_ID
NULL, --ITEM_ID,
NULL, --EXPLOSION_TYPE,
IdList_tbl(i), --NAME
G_TEMPLATE_MODEL_OPTION_OSR||
p_org_id||':'||
p_intent||':'||
p_derived_template_id||':'||
p_question_id||':'||
IdList_tbl(i), --ORIG_SYS_REF
NULL, --RESOURCE_FLAG
1, --TOP_ITEM_ID -- same value as in cz_imp_devl_projects
NULL, --INITIAL_VALUE
NULL, --PARENT_ID
1, --MINIMUM
1, --MAXIMUM
262, --PS_NODE_TYPE 262:Option
NULL, --FEATURE_TYPE
NULL, --PRODUCT_FLAG,
NULL, --REFERENCE_ID,
NULL, --MULTI_CONFIG_FLAG,
NULL, --ORDER_SEQ_FLAG,
NULL, --SYSTEM_NODE_FLAG
SeqNoList_tbl(i), --TREE_SEQ
'0', --COUNTED_OPTIONS_FLAG
'0', --UI_OMIT
0, --UI_SECTION
NULL, --BOM_TREATMENT,
G_RUN_ID, --RUN_ID
NULL, --REC_STATUS,
NULL, --DISPOSITION,
'0', --DELETED_FLAG
NULL, --EFF_FROM,
NULL, --EFF_TO,
NULL, --EFF_MASK,
NULL, --USER_STR01,
NULL, --USER_STR02,
NULL, --USER_STR03,
NULL, --USER_STR04,
NULL, --USER_NUM01,
NULL, --USER_NUM02,
NULL, --USER_NUM03,
NULL, --USER_NUM04,
NULL, --CHECKOUT_USER,
SYSDATE, --CREATION_DATE
SYSDATE, --LAST_UPDATE_DATE
FND_GLOBAL.USER_ID, --CREATED_BY
FND_GLOBAL.USER_ID, --LAST_UPDATED_BY
NULL, --SECURITY_MASK,
G_TEMPLATE_MODEL_OPTION_OSR||
p_org_id||':'||
p_intent||':'||
p_derived_template_id||':'||
p_question_id||':'||
IdList_tbl(i), --FSK_INTLTEXT_1_1
NULL, --FSK_INTLTEXT_1_EXT,
NULL, --FSK_ITEMMASTER_2_1,
NULL, --FSK_ITEMMASTER_2_EXT,
G_TEMPLATE_MODEL_FEATURE_OSR||
p_org_id||':'||
p_intent||':'||
p_derived_template_id||':'||
p_question_id, --FSK_PSNODE_3_1
NULL, --FSK_PSNODE_3_EXT,
NULL, --FSK_PSNODE_4_1,
NULL, --FSK_PSNODE_4_EXT,
G_TEMPLATE_MODEL_OSR||
p_org_id||':'||
p_intent||':'||
p_derived_template_id , --FSK_DEVLPROJECT_5_1
NULL, --FSK_DEVLPROJECT_5_EXT,
NULL, --COMPONENT_SEQUENCE_ID,
NULL, --COMPONENT_CODE,
2, --PLAN_LEVEL --Plan Level for Option:2
NULL, --BOM_ITEM_TYPE,
NULL, --SO_ITEM_TYPE_CODE,
NULL, --MINIMUM_SELECTED,
NULL, --MAXIMUM_SELECTED,
NULL, --BOM_REQUIRED,
NULL, --MUTUALLY_EXCLUSIVE_OPTIONS,
NULL, --OPTIONAL,
NULL, --FSK_EXPLNODE_1_1,
NULL, --FSK_PSNODE_6_1,
OKC_XPRT_CZ_INT_PVT.G_CZ_EPOCH_BEGIN, --EFFECTIVE_FROM
OKC_XPRT_CZ_INT_PVT.G_CZ_EPOCH_END, --EFFECTIVE_UNTIL
NULL, --EFFECTIVE_USAGE_MASK,
NULL, --EFFECTIVITY_SET_ID,
NULL, --FSK_EFFSET_7_1,
'0', --DECIMAL_QTY_FLAG -- 0 for all nodes
NULL, --QUOTEABLE_FLAG
NULL, --PRIMARY_UOM_CODE,
NULL, --COMPONENT_SEQUENCE_PATH, -- Must be NULL
NULL, --BOM_SORT_ORDER,
NULL, --IB_TRACKABLE,
FND_GLOBAL.LOGIN_ID, --LAST_UPDATE_LOGIN,
NULL, --INITIAL_NUM_VALUE,
G_APPLICATION_ID, --SRC_APPLICATION_ID
NULL, --FSK_ITEMMASTER_2_2,
NULL, --INSTANTIABLE_FLAG,
NULL --DISPLAY_IN_SUMMARY_FLAG
);
SELECT L.LANGUAGE_CODE
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG IN ('I', 'B');
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_clause_model_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_TMPL_MODEL_CM_REF_NODE_OSR|| p_org_id||':'||p_intent||':'||p_template_id, -- ORIG_SYS_REF
USERENV('LANG'), --SOURCE_LANG
G_RUN_ID, -- RUN_ID
NULL, -- REC_STATUS
NULL, -- DISPOSITION
NULL, -- MODEL_ID
G_TEMPLATE_MODEL_OSR||p_org_id||':'||p_intent||':'||p_template_id, --FSK_DEVLPROJECT_1_1
NULL, -- MESSAGE
NULL -- SEEDED_FLAG
);
l_cz_imp_ps_nodes_rec.DELETED_FLAG := 0;
l_cz_imp_ps_nodes_rec.LAST_UPDATE_DATE:= SYSDATE;
l_cz_imp_ps_nodes_rec.LAST_UPDATED_BY:= FND_GLOBAL.USER_ID;
l_cz_imp_ps_nodes_rec.MINIMUM_SELECTED:= NULL;
l_cz_imp_ps_nodes_rec.MAXIMUM_SELECTED:= NULL;
l_cz_imp_ps_nodes_rec.LAST_UPDATE_LOGIN:= FND_GLOBAL.LOGIN_ID;
INSERT INTO cz_imp_ps_nodes
(
PS_NODE_ID,
DEVL_PROJECT_ID,
FROM_POPULATOR_ID,
PROPERTY_BACKPTR,
ITEM_TYPE_BACKPTR,
INTL_TEXT_ID,
SUB_CONS_ID,
ORGANIZATION_ID,
ITEM_ID,
EXPLOSION_TYPE,
NAME,
ORIG_SYS_REF,
RESOURCE_FLAG,
TOP_ITEM_ID,
INITIAL_VALUE,
PARENT_ID,
MINIMUM,
MAXIMUM,
PS_NODE_TYPE,
FEATURE_TYPE,
PRODUCT_FLAG,
REFERENCE_ID,
MULTI_CONFIG_FLAG,
ORDER_SEQ_FLAG,
SYSTEM_NODE_FLAG,
TREE_SEQ,
COUNTED_OPTIONS_FLAG,
UI_OMIT,
UI_SECTION,
BOM_TREATMENT,
RUN_ID,
REC_STATUS,
DISPOSITION,
DELETED_FLAG ,
EFF_FROM,
EFF_TO,
EFF_MASK,
USER_STR01,
USER_STR02,
USER_STR03,
USER_STR04,
USER_NUM01,
USER_NUM02,
USER_NUM03,
USER_NUM04,
CHECKOUT_USER,
CREATION_DATE,
LAST_UPDATE_DATE,
CREATED_BY,
LAST_UPDATED_BY,
SECURITY_MASK,
FSK_INTLTEXT_1_1,
FSK_INTLTEXT_1_EXT,
FSK_ITEMMASTER_2_1,
FSK_ITEMMASTER_2_EXT,
FSK_PSNODE_3_1,
FSK_PSNODE_3_EXT,
FSK_PSNODE_4_1,
FSK_PSNODE_4_EXT,
FSK_DEVLPROJECT_5_1,
FSK_DEVLPROJECT_5_EXT,
COMPONENT_SEQUENCE_ID,
COMPONENT_CODE,
PLAN_LEVEL,
BOM_ITEM_TYPE,
SO_ITEM_TYPE_CODE,
MINIMUM_SELECTED,
MAXIMUM_SELECTED,
BOM_REQUIRED,
MUTUALLY_EXCLUSIVE_OPTIONS,
OPTIONAL,
FSK_EXPLNODE_1_1,
FSK_PSNODE_6_1,
EFFECTIVE_FROM,
EFFECTIVE_UNTIL,
EFFECTIVE_USAGE_MASK,
EFFECTIVITY_SET_ID,
FSK_EFFSET_7_1,
DECIMAL_QTY_FLAG,
QUOTEABLE_FLAG,
PRIMARY_UOM_CODE,
COMPONENT_SEQUENCE_PATH,
BOM_SORT_ORDER,
IB_TRACKABLE,
LAST_UPDATE_LOGIN,
INITIAL_NUM_VALUE,
SRC_APPLICATION_ID,
FSK_ITEMMASTER_2_2,
INSTANTIABLE_FLAG,
DISPLAY_IN_SUMMARY_FLAG
)
VALUES
(
l_cz_imp_ps_nodes_rec.PS_NODE_ID,
l_cz_imp_ps_nodes_rec.DEVL_PROJECT_ID,
l_cz_imp_ps_nodes_rec.FROM_POPULATOR_ID,
l_cz_imp_ps_nodes_rec.PROPERTY_BACKPTR,
l_cz_imp_ps_nodes_rec.ITEM_TYPE_BACKPTR,
l_cz_imp_ps_nodes_rec.INTL_TEXT_ID,
l_cz_imp_ps_nodes_rec.SUB_CONS_ID,
l_cz_imp_ps_nodes_rec.ORGANIZATION_ID,
l_cz_imp_ps_nodes_rec.ITEM_ID,
l_cz_imp_ps_nodes_rec.EXPLOSION_TYPE,
l_cz_imp_ps_nodes_rec.NAME,
l_cz_imp_ps_nodes_rec.ORIG_SYS_REF,
l_cz_imp_ps_nodes_rec.RESOURCE_FLAG,
l_cz_imp_ps_nodes_rec.TOP_ITEM_ID,
l_cz_imp_ps_nodes_rec.INITIAL_VALUE,
l_cz_imp_ps_nodes_rec.PARENT_ID,
l_cz_imp_ps_nodes_rec.MINIMUM,
l_cz_imp_ps_nodes_rec.MAXIMUM,
l_cz_imp_ps_nodes_rec.PS_NODE_TYPE,
l_cz_imp_ps_nodes_rec.FEATURE_TYPE,
l_cz_imp_ps_nodes_rec.PRODUCT_FLAG,
l_cz_imp_ps_nodes_rec.REFERENCE_ID,
l_cz_imp_ps_nodes_rec.MULTI_CONFIG_FLAG,
l_cz_imp_ps_nodes_rec.ORDER_SEQ_FLAG,
l_cz_imp_ps_nodes_rec.SYSTEM_NODE_FLAG,
l_cz_imp_ps_nodes_rec.TREE_SEQ,
l_cz_imp_ps_nodes_rec.COUNTED_OPTIONS_FLAG,
l_cz_imp_ps_nodes_rec.UI_OMIT,
l_cz_imp_ps_nodes_rec.UI_SECTION,
l_cz_imp_ps_nodes_rec.BOM_TREATMENT,
l_cz_imp_ps_nodes_rec.RUN_ID,
l_cz_imp_ps_nodes_rec.REC_STATUS,
l_cz_imp_ps_nodes_rec.DISPOSITION,
l_cz_imp_ps_nodes_rec.DELETED_FLAG ,
l_cz_imp_ps_nodes_rec.EFF_FROM,
l_cz_imp_ps_nodes_rec.EFF_TO,
l_cz_imp_ps_nodes_rec.EFF_MASK,
l_cz_imp_ps_nodes_rec.USER_STR01,
l_cz_imp_ps_nodes_rec.USER_STR02,
l_cz_imp_ps_nodes_rec.USER_STR03,
l_cz_imp_ps_nodes_rec.USER_STR04,
l_cz_imp_ps_nodes_rec.USER_NUM01,
l_cz_imp_ps_nodes_rec.USER_NUM02,
l_cz_imp_ps_nodes_rec.USER_NUM03,
l_cz_imp_ps_nodes_rec.USER_NUM04,
l_cz_imp_ps_nodes_rec.CHECKOUT_USER,
l_cz_imp_ps_nodes_rec.CREATION_DATE,
l_cz_imp_ps_nodes_rec.LAST_UPDATE_DATE,
l_cz_imp_ps_nodes_rec.CREATED_BY,
l_cz_imp_ps_nodes_rec.LAST_UPDATED_BY,
l_cz_imp_ps_nodes_rec.SECURITY_MASK,
l_cz_imp_ps_nodes_rec.FSK_INTLTEXT_1_1,
l_cz_imp_ps_nodes_rec.FSK_INTLTEXT_1_EXT,
l_cz_imp_ps_nodes_rec.FSK_ITEMMASTER_2_1,
l_cz_imp_ps_nodes_rec.FSK_ITEMMASTER_2_EXT,
l_cz_imp_ps_nodes_rec.FSK_PSNODE_3_1,
l_cz_imp_ps_nodes_rec.FSK_PSNODE_3_EXT,
l_cz_imp_ps_nodes_rec.FSK_PSNODE_4_1,
l_cz_imp_ps_nodes_rec.FSK_PSNODE_4_EXT,
l_cz_imp_ps_nodes_rec.FSK_DEVLPROJECT_5_1,
l_cz_imp_ps_nodes_rec.FSK_DEVLPROJECT_5_EXT,
l_cz_imp_ps_nodes_rec.COMPONENT_SEQUENCE_ID,
l_cz_imp_ps_nodes_rec.COMPONENT_CODE,
l_cz_imp_ps_nodes_rec.PLAN_LEVEL,
l_cz_imp_ps_nodes_rec.BOM_ITEM_TYPE,
l_cz_imp_ps_nodes_rec.SO_ITEM_TYPE_CODE,
l_cz_imp_ps_nodes_rec.MINIMUM_SELECTED,
l_cz_imp_ps_nodes_rec.MAXIMUM_SELECTED,
l_cz_imp_ps_nodes_rec.BOM_REQUIRED,
l_cz_imp_ps_nodes_rec.MUTUALLY_EXCLUSIVE_OPTIONS,
l_cz_imp_ps_nodes_rec.OPTIONAL,
l_cz_imp_ps_nodes_rec.FSK_EXPLNODE_1_1,
l_cz_imp_ps_nodes_rec.FSK_PSNODE_6_1,
l_cz_imp_ps_nodes_rec.EFFECTIVE_FROM,
l_cz_imp_ps_nodes_rec.EFFECTIVE_UNTIL,
l_cz_imp_ps_nodes_rec.EFFECTIVE_USAGE_MASK,
l_cz_imp_ps_nodes_rec.EFFECTIVITY_SET_ID,
l_cz_imp_ps_nodes_rec.FSK_EFFSET_7_1,
l_cz_imp_ps_nodes_rec.DECIMAL_QTY_FLAG,
l_cz_imp_ps_nodes_rec.QUOTEABLE_FLAG,
l_cz_imp_ps_nodes_rec.PRIMARY_UOM_CODE,
l_cz_imp_ps_nodes_rec.COMPONENT_SEQUENCE_PATH,
l_cz_imp_ps_nodes_rec.BOM_SORT_ORDER,
l_cz_imp_ps_nodes_rec.IB_TRACKABLE,
l_cz_imp_ps_nodes_rec.LAST_UPDATE_LOGIN,
l_cz_imp_ps_nodes_rec.INITIAL_NUM_VALUE,
l_cz_imp_ps_nodes_rec.SRC_APPLICATION_ID,
l_cz_imp_ps_nodes_rec.FSK_ITEMMASTER_2_2,
l_cz_imp_ps_nodes_rec.INSTANTIABLE_FLAG,
l_cz_imp_ps_nodes_rec.DISPLAY_IN_SUMMARY_FLAG
);
SELECT cz_xfr_run_infos_s.NEXTVAL
FROM dual;
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 ;
SELECT devl_project_id
FROM cz_devl_projects
WHERE orig_sys_ref = p_orig_sys_ref
AND devl_project_id = persistent_project_id
AND deleted_flag = 0;
SELECT object_id
FROM cz_rp_entries
WHERE enclosing_folder= OKC_XPRT_CZ_INT_PVT.G_TEMPLATE_FOLDER_ID
AND object_type = 'FLD'
AND deleted_flag=0
AND name = p_name;
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;
-- Template Import was successful, update template record with the model_id
/*
OPEN csr_template_model_id(p_org_id => l_org_id,
p_intent => l_intent,
p_tmpl_id => l_template_id);
UPDATE okc_terms_templates_all
SET template_model_id = l_template_model_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
UNION
-- templates already pushed to CZ
SELECT DISTINCT SUBSTR(orig_sys_ref, INSTR(orig_sys_ref,':',-1,3)+1,
(INSTR(orig_sys_ref,':',1,5) - (INSTR(orig_sys_ref,':',1,4)+1))
/*SELECT DISTINCT SUBSTR(cz.orig_sys_ref, INSTR(cz.orig_sys_ref,':',-1,2)+1,
(INSTR(cz.orig_sys_ref,':',-1,1) - (INSTR(cz.orig_sys_ref,':',-1,2)+1)) */
)
FROM cz_rules cz,
okc_xprt_rule_hdrs_all h
WHERE SUBSTR(cz.orig_sys_ref,INSTR(cz.orig_sys_ref,':',-1,1)+1) = to_char(h.rule_id)
AND h.request_id = FND_GLOBAL.CONC_REQUEST_ID
AND cz.deleted_flag = '0'
AND cz.rule_type = 200; --Perf Bug#5030272 Added rule_type = 200
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 t.template_id
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)
AND t.contract_expert_enabled = 'Y'
AND t.status_code IN ('APPROVED','ON_HOLD') ;