[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
2. Select - Distinct - items
3. Select - Distinct - item_property_value
4. Call cz_ref.delete_duplicates after populate_table
5. extr_intl_text change to query bill_sequence_id and use in query
********************************/
G_BOM_APPLICATION_ID CONSTANT NUMBER := 702;
SELECT p.model_ps_node_id FROM cz_xfr_project_bills p, cz_devl_projects d
WHERE p.organization_id = inOrgId
AND p.top_item_id = inTopId
AND p.explosion_type = inExplType
AND d.deleted_flag = '0'
AND d.devl_project_id = d.persistent_project_id
AND p.model_ps_node_id = d.devl_project_id;
SELECT UPPER(VALUE) FROM CZ_DB_SETTINGS
WHERE UPPER(SECTION_NAME) = v_section_name
AND UPPER(SETTING_ID) = v_settings_id;
TYPE tDeletedFlag IS TABLE OF cz_imp_item_master.deleted_flag%TYPE INDEX BY BINARY_INTEGER;
iDeletedFlag tDeletedFlag;
insert_end_time number;
SELECT DISTINCT INVENTORY_ITEM_ID, ORGANIZATION_ID, SEGMENT1, CONCATENATED_SEGMENTS, ITEM_DESC,
FIXED_LEAD_TIME, BOM_ITEM_TYPE, CUSTOMER_ORDER_ENABLED_FLAG,
INVENTORY_ITEM_STATUS_CODE, PRIMARY_UOM_CODE, INDIVISIBLE_FLAG, ITEM_CATALOG_GROUP_ID,
INV_APPLICATION_ID
BULK COLLECT INTO
InventoryItemId, OrganizationId, Segment1, ConcatenatedSegments, ItemDesc,
FixedLeadTime, BomItemType, CustomerOrderFlag,
ItemStatusCode, PrimaryUomCode, IndivisibleFlag, CatalogId,
SrcApplicationId
FROM CZ_EXV_ITEMS
WHERE ORGANIZATION_ID=nOrg_ID AND TOP_ITEM_ID=nTop_ID
AND EXPLOSION_TYPE=sExpl_type;
iDeletedFlag(nIndex) := '1';
iDeletedFlag(nIndex) := '0';
INSERT /*+ APPEND */ INTO CZ_IMP_ITEM_MASTER
(ORIG_SYS_REF, REF_PART_NBR, DESC_TEXT, LEAD_TIME,
QUOTEABLE_FLAG, DELETED_FLAG,
RUN_ID, PRIMARY_UOM_CODE, DECIMAL_QTY_FLAG,
FSK_ITEMTYPE_1_1, FSK_ITEMTYPE_1_EXT,
SRC_APPLICATION_ID,SRC_TYPE_CODE)
VALUES
(iOrigSysRef(i), iRefPartNbr(i), ItemDesc(i), iLeadTime(i),
iQuoteableFlag(i), iDeletedFlag(i),
inRun_ID, iPrimaryUomCode(i), iDecimalQtyFlag(i),
iFskItemType(i), iFskItemType(i),
iSrcApplicationId(i), iSrcTypeCode(i));
insert_end_time := dbms_utility.get_time();
TYPE tLastUpdateDate IS TABLE OF cz_exv_item_master.last_update_date%TYPE INDEX BY BINARY_INTEGER;
TYPE tLastUpdatedBy IS TABLE OF cz_exv_item_master.last_updated_by%TYPE INDEX BY BINARY_INTEGER;
TYPE tMinimumSelected IS TABLE OF cz_imp_ps_nodes.minimum_selected%TYPE INDEX BY BINARY_INTEGER;
TYPE tMaximumSelected IS TABLE OF cz_imp_ps_nodes.maximum_selected%TYPE INDEX BY BINARY_INTEGER;
LastUpdateDate tLastUpdateDate;
LastUpdatedBy tLastUpdatedBy;
iLastUpdateDate tLastUpdateDate;
iLastUpdatedBy tLastUpdatedBy;
iMinimumSelected tMinimumSelected;
iMaximumSelected tMaximumSelected;
insert_end_time number;
SELECT
NVL(COMMON_COMPONENT_SEQUENCE_ID, COMPONENT_SEQUENCE_ID), COMPONENT_CODE, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE,
LAST_UPDATED_BY, EFFECTIVITY_DATE, DISABLE_DATE, ITEM_DESC, LOW_QUANTITY, HIGH_QUANTITY,
SORT_ORDER, BOM_ITEM_TYPE, COMPONENT_ITEM_ID, MUTUALLY_EXCLUSIVE_OPTIONS, plan_level,
PICK_COMPONENTS_FLAG, COMPONENT_QUANTITY, OPTIONAL, organization_id, top_item_id, explosion_type,
INDIVISIBLE_FLAG, CUSTOMER_ORDER_ENABLED_FLAG, PRIMARY_UOM_CODE, MODEL_TYPE, COMMS_NL_TRACKABLE_FLAG,
CONFIG_MODEL_TYPE, BOM_APPLICATION_ID, INV_APPLICATION_ID, IB_LINK_ITEM_FLAG,
DECODE(SHIPPABLE_ITEM_FLAG,'Y','1','N','0','0'),
DECODE(MTL_TRANSACTIONS_ENABLED_FLAG,'Y','1','N','0','0'),
DECODE(REPLENISH_TO_ORDER_FLAG,'Y','1','N','0','0'),
SERIAL_NUMBER_CONTROL_CODE
FROM cz_exv_item_master
WHERE organization_id = nOrg_ID
AND top_item_id = nTop_ID
AND explosion_type = sExpl_type
AND language = inLang
ORDER BY sort_order, component_code;
SELECT fsk_psnode_3_1, COUNT(*) FROM cz_imp_ps_nodes
WHERE run_id = inRun_ID
AND rec_status IS NULL
AND optional = OraNo
GROUP BY fsk_psnode_3_1;
SELECT TO_NUMBER(value) INTO MemoryBulkSize
FROM cz_db_settings
WHERE LOWER(setting_id) = v_settings_id
AND LOWER(section_name) = v_section_name;
ComponentSequenceId.DELETE;
ComponentCode.DELETE;
CreationDate.DELETE;
CreatedBy.DELETE;
LastUpdateDate.DELETE;
LastUpdatedBy.DELETE;
EffectivityDate.DELETE;
DisableDate.DELETE;
Description.DELETE;
LowQuantity.DELETE;
HighQuantity.DELETE;
SortOrder.DELETE;
BomItemType.DELETE;
ComponentItemId.DELETE;
MutuallyExclusiveOptions.DELETE;
PlanLevel.DELETE;
PickComponentsFlag.DELETE;
ComponentQuantity.DELETE;
v_Optional.DELETE;
OrganizationId.DELETE;
TopItemId.DELETE;
ExplosionType.DELETE;
IndivisibleFlag.DELETE;
CustomerOrderEnabledFlag.DELETE;
PrimaryUomCode.DELETE;
ConfigModelType.DELETE;
ModelType.DELETE;
SrcApplicationId.DELETE;
FSKItemMaster22.DELETE;
IBLinkItemFlag.DELETE;
ShippableItemFlag.DELETE;
TransEnabledFlag.DELETE;
ReplenishToOrderFlag.DELETE;
SerialNumberControlCode.DELETE;
iName.DELETE;
iOrigSysRef.DELETE;
iMinimum.DELETE;
iMaximum.DELETE;
iTreeSeq.DELETE;
iPsNodeType.DELETE;
iBomTreatment.DELETE;
iUiOmit.DELETE;
iUiSection.DELETE;
iProductFlag.DELETE;
ifskIntlText.DELETE;
ifskIntlTextExt.DELETE;
ifskItemMaster.DELETE;
ifskItemMasterExt.DELETE;
ifskItemMaster22.DELETE;
ifskParentNode.DELETE;
ifskParentNodeExt.DELETE;
iMutuallyExclusive.DELETE;
iOptional.DELETE;
ifskDevlProject.DELETE;
ifskDevlProjectExt.DELETE;
iCreationDate.DELETE;
iCreatedBy.DELETE;
iLastUpdateDate.DELETE;
iLastUpdatedBy.DELETE;
iEffectiveFrom.DELETE;
iEffectiveUntil.DELETE;
iComponentSequenceId.DELETE;
iComponentCode.DELETE;
iPlanLevel.DELETE;
iRunId.DELETE;
iSoItemTypeCode.DELETE;
iMinimumSelected.DELETE;
iBomRequired.DELETE;
iInitialValue.DELETE;
iOrganizationId.DELETE;
iTopItemId.DELETE;
iExplosionType.DELETE;
ifskReference.DELETE;
iMaximumSelected.DELETE;
iDecimalQtyFlag.DELETE;
iQuoteableFlag.DELETE;
iPrimaryUomCode.DELETE;
iBomSortOrder.DELETE;
iComponentSequencePath.DELETE;
iSrcApplicationId.DELETE;
iIBLinkItemFlag.DELETE;
iShippableItemFlag.DELETE;
iInventoryTransactableFlag.DELETE;
iAssembleToOrder.DELETE;
iSerializableItemFlag.DELETE;
ComponentSequenceId, ComponentCode, CreationDate, CreatedBy, LastUpdateDate,
LastUpdatedBy, EffectivityDate, DisableDate, Description, LowQuantity, HighQuantity,
SortOrder, BomItemType, ComponentItemId, MutuallyExclusiveOptions, PlanLevel,
PickComponentsFlag, ComponentQuantity, v_Optional, OrganizationId, TopItemId, ExplosionType,
IndivisibleFlag, CustomerOrderEnabledFlag, PrimaryUomCode, ModelType, TrackableFlag, ConfigModelType,
SrcApplicationId, FSKItemMaster22, IBLinkItemFlag,
ShippableItemFlag, TransEnabledFlag, ReplenishToOrderFlag, SerialNumberControlCode -- changes for TSO
LIMIT MemoryBulkSize;
iLastUpdateDate(nIndex) := LastUpdateDate(i);
iLastUpdatedBy(nIndex) := LastUpdatedBy(i);
iMinimumSelected(nIndex) := NULL;
iMinimumSelected(nIndex) := 0;
iMinimumSelected(nIndex) := LowQuantity(i);
iMaximumSelected(nIndex) := -1;
iMaximumSelected(nIndex) := HighQuantity(i);
iLastUpdateDate(nIndex) := LastUpdateDate(i);
iLastUpdatedBy(nIndex) := LastUpdatedBy(i);
iMinimumSelected(nIndex) := 0;
iMaximumSelected(nIndex) := NULL;
setFCEMinMax (minVal => iMINIMUMSELECTED(j),
maxVal => iMAXIMUMSELECTED(j),
defaultVal => iInitNumVal(j),
p_decimal_item_flag => iDecimalQtyFlag(j),
p_use_defaults => l_use_defaults,
p_set_decimals => l_set_decimals,
p_default_dec => l_default_dec,
p_default_int => l_default_int );
INSERT INTO cz_imp_ps_nodes
(NAME, ORIG_SYS_REF, MINIMUM, MAXIMUM, TREE_SEQ, PS_NODE_TYPE, BOM_TREATMENT, UI_OMIT,
UI_SECTION, PRODUCT_FLAG, FSK_INTLTEXT_1_1, FSK_INTLTEXT_1_EXT, FSK_ITEMMASTER_2_1,
FSK_ITEMMASTER_2_EXT, FSK_PSNODE_3_1, FSK_PSNODE_3_EXT, MUTUALLY_EXCLUSIVE_OPTIONS, OPTIONAL,
FSK_DEVLPROJECT_5_1, FSK_DEVLPROJECT_5_EXT, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE,
LAST_UPDATED_BY, EFFECTIVE_FROM, EFFECTIVE_UNTIL, COMPONENT_SEQUENCE_ID, COMPONENT_CODE,
PLAN_LEVEL, RUN_ID, SO_ITEM_TYPE_CODE, MINIMUM_SELECTED, BOM_REQUIRED, initial_num_value,
ORGANIZATION_ID, TOP_ITEM_ID, EXPLOSION_TYPE, fsk_psnode_6_1, MAXIMUM_SELECTED,
DECIMAL_QTY_FLAG, QUOTEABLE_FLAG,PRIMARY_UOM_CODE,BOM_SORT_ORDER,
COMPONENT_SEQUENCE_PATH,IB_TRACKABLE,SRC_APPLICATION_ID, FSK_ITEMMASTER_2_2, IB_LINK_ITEM_FLAG,
SHIPPABLE_ITEM_FLAG,INVENTORY_TRANSACTABLE_FLAG, ASSEMBLE_TO_ORDER_FLAG,SERIALIZABLE_ITEM_FLAG) -- changes for TSO
VALUES
(iName(i), iOrigSysRef(i), iMinimum(i), iMaximum(i), iTreeSeq(i), iPsNodeType(i), iBomTreatment(i),
iUiOmit(i), iUiSection(i), iProductFlag(i), ifskIntlText(i), ifskIntlTextExt(i), ifskItemMaster(i),
ifskItemMasterExt(i), ifskParentNode(i), ifskParentNodeExt(i), iMutuallyExclusive(i), iOptional(i),
ifskDevlProject(i), ifskDevlProjectExt(i), iCreationDate(i), iCreatedBy(i), iLastUpdateDate(i),
iLastUpdatedBy(i), iEffectiveFrom(i), iEffectiveUntil(i), iComponentSequenceId(i),
iComponentCode(i), iPlanLevel(i), iRunId(i), iSoItemTypeCode(i), iMinimumSelected(i),
iBomRequired(i), iInitNumVal(i), iOrganizationId(i), iTopItemId(i), iExplosionType(i),
ifskReference(i), iMaximumSelected(i), iDecimalQtyFlag(i),
iQuoteableFlag(i),iPrimaryUomCode(i),iBomSortOrder(i), iComponentSequencePath(i),iBTrackableFlag(i),
iSrcApplicationId(i), ifskItemMaster22(i), iIBLinkItemFlag(i),
iShippableItemFlag(i), iInventoryTransactableFlag(i), iAssembleToOrder(i), iSerializableItemFlag(i)); -- changes for TSO
d_str := inRun_Id || ' Insert ps nodes (' || nTop_Id || ') :' || (end_time-st_time)/100.00;
SELECT decode(upper(VALUE),'TRUE','1','FALSE','0','T','1','F','0','1','1','0','0','YES','1','NO','0','Y','1','N','0','0')
INTO genStatisticsCz FROM CZ_DB_SETTINGS
WHERE upper(SETTING_ID)=v_settings_id AND SECTION_NAME=v_section_name;
UPDATE cz_imp_ps_nodes SET maximum_selected = 1
WHERE run_id = inRun_ID
AND rec_status IS NULL
AND mutually_exclusive_options = OraYes
AND ps_node_type = bomOptionClass;
parentOrigSysRef.DELETE;
childCount.DELETE;
UPDATE cz_imp_ps_nodes SET maximum_selected = childCount(i) + 1
WHERE run_id = inRun_ID
AND rec_status IS NULL
AND mutually_exclusive_options = OraYes
AND orig_sys_ref = parentOrigSysRef(i);
x_error:=cz_utils.log_report(d_str,1,'CZ_IMP_SINGLE.maximum_selected',11276,inRun_Id);
SELECT B.ORIG_SYS_REF, B.COMPONENT_SEQUENCE_PATH, B.ps_node_id, B.ps_node_type
FROM CZ_XFR_PROJECT_BILLS P,CZ_PS_NODES B
WHERE B.DEVL_PROJECT_ID=P.model_ps_node_id
AND P.organization_id = nORg_ID
AND P.top_item_id = nTop_ID
AND P.explosion_type = sExpl_type
AND P.DELETED_FLAG='0'
AND B.DELETED_FLAG='0'
AND B.ORIG_SYS_REF IS NOT NULL
AND B.PS_NODE_TYPE <> 259
AND B.devl_project_id = nModelId
FOR UPDATE OF B.DELETED_FLAG;
SELECT 'F' FROM CZ_IMP_PS_NODES
WHERE ORIG_SYS_REF=sOnlOrigSysRef AND RUN_ID=inRun_ID
AND NVL(COMPONENT_SEQUENCE_PATH, -1) = NVL(v_SequencePath, -1)
AND REC_STATUS IS NULL;
UPDATE CZ_PS_NODES SET
DELETED_FLAG='1'
WHERE CURRENT OF c_onl_ps_node;
cz_refs.delete_Node(v_PsNodeId, v_PsNodeType, p_out_err, '1');
SELECT message INTO d_str FROM cz_db_logs WHERE run_id = p_out_err;
d_str := inRun_Id || ' Deleted ps node check (' || nTop_Id || ') :' || (end_time-st_time)/100.00;
x_error :=cz_utils.log_report(d_str,1,'CZ_IMP_SINGLE.EXTR_PS_NODE: delete obsolete nodes',11276,inRun_Id);
x_error:=cz_utils.log_report(d_str,1,'CZ_IMP_SINGLE.EXTR_PS_NODE: delete obsolete nodes',11276,inRun_Id);
insert_end_time number;
SELECT DISTINCT component_item_id,
NVL(common_component_sequence_id, component_sequence_id) AS component_sequence_id,
component_code,
description, language, source_lang
FROM cz_exv_intl_text
WHERE organization_id = nOrg_ID
AND top_item_id = nTop_ID
AND explosion_type = sExpl_type;
SELECT TO_NUMBER(value) INTO MemoryBulkSize
FROM cz_db_settings
WHERE LOWER(setting_id) = v_settings_id
AND LOWER(section_name) = v_section_name;
iOrigSysRef.DELETE;
iLocalizedStr.DELETE;
iLanguage.DELETE;
iSrcLang.DELETE;
CompSeqId.DELETE;
CompItemId.DELETE;
CompCode.DELETE;
INSERT INTO CZ_IMP_LOCALIZED_TEXTS
(LOCALIZED_STR, LANGUAGE, SOURCE_LANG, RUN_ID, ORIG_SYS_REF, FSK_DEVLPROJECT_1_1)
VALUES (iLocalizedStr(i), iLanguage(i), iSrcLang(i), inRun_ID,
CompItemId(i)||':'||sExpl_type||':'||nOrg_ID||':'||CompSeqId(i), iFSKDevlProject);
INSERT INTO CZ_IMP_LOCALIZED_TEXTS
(LOCALIZED_STR, LANGUAGE, SOURCE_LANG, RUN_ID, ORIG_SYS_REF, FSK_DEVLPROJECT_1_1)
SELECT description, language, source_lang, inRun_ID,
gModelItemId_tbl(j)||':'||sExpl_type||':'||nOrg_ID||':'||
NVL(common_component_sequence_id, component_sequence_id),
CZ_ORAAPPS_INTEGRATE.PROJECT_SURROGATE_KEY(sExpl_type,to_char(nOrg_ID),to_char(gModelItemId_tbl(j)))
FROM cz_exv_intl_text
WHERE organization_id = nOrg_ID
AND top_item_id = gModelItemId_tbl(j)
AND explosion_type = sExpl_type
AND component_item_id = top_item_id;
insert_end_time number;
gModelItemId_tbl.delete;
SELECT
ORGANIZATION_ID, COMPONENT_ITEM_ID, EXPLOSION_TYPE,
DESCRIPTION, PLAN_LEVEL, MODEL_TYPE, CONFIG_MODEL_TYPE
BULK COLLECT INTO
OrganizationId, ComponentItemId, ExplosionType, Description, PlanLevel, ModelType, ConfigModelType
FROM CZ_EXV_ITEM_MASTER WHERE bom_item_type = cz_imp_ps_node.cnModel
AND ORGANIZATION_ID=nOrg_ID
AND TOP_ITEM_ID=nTop_ID AND EXPLOSION_TYPE=sExpl_type
AND language = l_lang;
INSERT INTO CZ_IMP_DEVL_PROJECT
(ORGANIZATION_ID, TOP_ITEM_ID, EXPLOSION_TYPE, ORIG_SYS_REF, VERSION,
RUN_ID, NAME, FSK_INTLTEXT_1_1, DESC_TEXT, PLAN_LEVEL, MODEL_ID, MODEL_TYPE,
INVENTORY_ITEM_ID, PRODUCT_KEY, BOM_CAPTION_RULE_ID, NONBOM_CAPTION_RULE_ID, CONFIG_ENGINE_TYPE)
VALUES
(nOrg_ID, iTopItemId(i), sExpl_type, iOrigSysRef(i), 1,
inRun_ID, iName(i), iDescText(i), iDescText(i), iPlanLevel(i), nModelId, iModelType(i),
iInventoryItemId(i), iProductKey(i), G_CAPTION_RULE_DESC, G_CAPTION_RULE_NAME, g_CONFIG_ENGINE_TYPE);
insert_end_time := dbms_utility.get_time();
d_str := inRun_Id || ' Insert projects (' || nTop_Id || ') :' || (insert_end_time-loop_end_time)/100.00;
insert_end_time number;
INSERT INTO CZ_IMP_ITEM_TYPE
(DESC_TEXT, NAME, ORIG_SYS_REF,SRC_APPLICATION_ID,RUN_ID)
SELECT DESCRIPTION, CATALOG_CONCAT_SEGS, ITEM_CATALOG_GROUP_ID, G_BOM_APPLICATION_ID, inRUN_ID
FROM CZ_EXV_ITEM_TYPES
WHERE ITEM_CATALOG_GROUP_ID = itemCatalogGroupId(i);
INSERT INTO CZ_IMP_ITEM_TYPE
(DESC_TEXT, NAME, ORIG_SYS_REF, SRC_APPLICATION_ID, RUN_ID)
SELECT DESCRIPTION, DESCRIPTION, ITEM_CATALOG_GROUP_ID, G_BOM_APPLICATION_ID, inRUN_ID
FROM CZ_EXV_ITEM_TYPES
WHERE ITEM_CATALOG_GROUP_ID = itemCatalogGroupId(i);
d_str := inRun_Id || ' Insert item type (' || nTop_Id || ') :' || (end_time-st_time)/100.00;
insert_end_time NUMBER;
SELECT COUNT(*) INTO l_check_for_stub_views FROM CZ_EXV_APC_PROPERTIES
WHERE ATTR_GROUP_ID = -1 AND
ITEM_CATALOG_GROUP_ID = -1 AND
ATTR_ID = -1 AND
APPLICATION_ID = -1;
DELETE FROM CZ_IMP_TMP_ITEMCAT WHERE run_id=p_run_id;
FOR i IN(SELECT item_catalog_group_id,parent_catalog_group_id,description,catalog_concat_segs
FROM CZ_EXV_ITEM_TYPES
START WITH item_catalog_group_id = itemCatalogGroupId(item_index)
CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id)
LOOP
l_prev_item_catalog_tbl.extend;
l_item_cat_tbl.DELETE;
INSERT INTO CZ_IMP_TMP_ITEMCAT(run_id, item_catalog_group_id)
SELECT p_run_id,item_catalog_group_id
FROM (SELECT DISTINCT item_catalog_group_id
FROM TABLE(CAST(l_prev_item_catalog_tbl AS SYSTEM.CZ_ITEM_CATALOG_TBL)) WHERE item_catalog_group_id IS NOT NULL);
l_prev_item_catalog_tbl.DELETE;
FOR attr IN(SELECT attr_group_id, attr_group_name||'.'||attr_name AS attribute_name,
DECODE(data_type_code,'N',DECIMAL_TYPE,'C',TEXT_TYPE,'A',TL_TEXT_TYPE,TEXT_TYPE) AS property_type,
database_column,description,apcprops.item_catalog_group_id,apcprops.default_value
FROM CZ_EXV_APC_PROPERTIES apcprops,
CZ_IMP_TMP_ITEMCAT itemtypes
WHERE apcprops.item_catalog_group_id=itemtypes.item_catalog_group_id AND
itemtypes.run_id=p_run_id
ORDER BY attr_group_id)
LOOP
l_rec_counter := l_rec_counter + 1;
DELETE FROM CZ_IMP_TMP_ITEMCAT where run_id=p_run_id;
FOR ii IN (SELECT item_catalog_group_id FROM CZ_IMP_TMP_ITEMCAT WHERE run_id=p_run_id)
LOOP
IF l_hier_item_cat_tbl.EXISTS(ii.item_catalog_group_id) THEN
l_item_cat_tbl := l_hier_item_cat_tbl(ii.item_catalog_group_id);
INSERT INTO CZ_IMP_PROPERTY
(DESC_TEXT,NAME,ORIG_SYS_REF,RUN_ID,DATA_TYPE,DEF_VALUE,DEF_NUM_VALUE,SRC_APPLICATION_ID)
VALUES (l_description_tbl(i),
l_attr_name_tbl(i),
l_attr_name_tbl(i)||'-'||TO_CHAR(G_EGO_APPLICATION_ID),
p_run_id,
l_property_type_tbl(i),
l_def_value_tbl(i),
DECODE(l_property_type_tbl(i),DECIMAL_TYPE,TO_NUMBER(l_def_value_tbl(i)),NULL),
G_EGO_APPLICATION_ID);
INSERT INTO CZ_IMP_ITEM_TYPE_PROPERTY
(FSK_ITEMTYPE_1_1,FSK_ITEMTYPE_1_EXT,FSK_PROPERTY_2_1,FSK_PROPERTY_2_EXT,RUN_ID,
ORIG_SYS_REF,SRC_APPLICATION_ID)
VALUES (l_item_catalog_group_id_tbl(i),
l_item_catalog_group_id_tbl(i),
l_attr_name_tbl(i)||'-'||TO_CHAR(G_EGO_APPLICATION_ID),
l_attr_name_tbl(i)||'-'||TO_CHAR(G_EGO_APPLICATION_ID),
p_run_id,
TO_CHAR(l_item_catalog_group_id_tbl(i)) || ':' ||l_attr_name_tbl(i)||'-'||TO_CHAR(G_EGO_APPLICATION_ID),
G_EGO_APPLICATION_ID);
FOR ii IN (SELECT item_catalog_group_id FROM CZ_IMP_TMP_ITEMCAT WHERE run_id=p_run_id)
LOOP
IF l_attr_item_cat_tbl.EXISTS(ii.item_catalog_group_id) THEN
l_attr_list_temp_tbl := l_attr_item_cat_tbl(ii.item_catalog_group_id);
INSERT INTO CZ_IMP_ITEM_TYPE_PROPERTY
(FSK_ITEMTYPE_1_1,FSK_ITEMTYPE_1_EXT,FSK_PROPERTY_2_1,FSK_PROPERTY_2_EXT,RUN_ID,
ORIG_SYS_REF,SRC_APPLICATION_ID)
VALUES (ii.item_catalog_group_id,
ii.item_catalog_group_id,
l_attr_list_temp_tbl(jj)||'-'||TO_CHAR(G_EGO_APPLICATION_ID),
l_attr_list_temp_tbl(jj)||'-'||TO_CHAR(G_EGO_APPLICATION_ID),
p_run_id,
TO_CHAR(ii.item_catalog_group_id)|| ':'||
l_attr_list_temp_tbl(jj)||'-'||TO_CHAR(G_EGO_APPLICATION_ID) || '-' || l_item_cat_path_tbl(ii.item_catalog_group_id) ,
G_EGO_APPLICATION_ID);
l_att_names_tbl.DELETE;
l_db_columns_tbl.DELETE;
l_db_column_types_tbl.DELETE;
l_att_names_tbl.DELETE;
l_db_columns_tbl.DELETE;
l_db_column_types_tbl.DELETE;
l_sql := 'SELECT apcpropvals.* FROM CZ_EXV_ITEM_APC_PROP_VALUES apcpropvals,'||
'CZ_IMP_TMP_ITEMCAT itemtypes '||
'WHERE apcpropvals.item_catalog_group_id = itemtypes.item_catalog_group_id AND inventory_item_id IS NOT NULL ORDER BY INVENTORY_ITEM_ID';
INSERT INTO CZ_IMP_LOCALIZED_TEXTS
(LOCALIZED_STR, LANGUAGE, SOURCE_LANG, RUN_ID, ORIG_SYS_REF, MODEL_ID)
VALUES
(l_column_value,l_language, l_source_lang, p_run_id, l_localtext_orig_sys_ref_tbl(l_rec_counter),0);
INSERT INTO CZ_IMP_ITEM_PROPERTY_VALUE
(PROPERTY_VALUE, FSK_PROPERTY_1_1,FSK_PROPERTY_1_EXT,
FSK_ITEMMASTER_2_1,FSK_ITEMMASTER_2_EXT,RUN_ID,ORIG_SYS_REF,FSK_LOCALIZEDTEXT_3_1,PROPERTY_NUM_VALUE,
SRC_APPLICATION_ID)
VALUES
(l_prop_values_tbl(i), l_prop_orig_sys_ref_tbl(i),l_prop_orig_sys_ref_tbl(i),
l_item_master_orig_sys_ref_tbl(i), l_item_master_orig_sys_ref_tbl(i),
p_run_id,
l_item_master_orig_sys_ref_tbl(i) || ':' || l_prop_orig_sys_ref_tbl(i),
l_localtext_orig_sys_ref_tbl(i),
l_num_value_tbl(i),
G_EGO_APPLICATION_ID);
INSERT INTO CZ_IMP_LOCALIZED_TEXTS
(LOCALIZED_STR, LANGUAGE, SOURCE_LANG, RUN_ID, ORIG_SYS_REF, MODEL_ID)
SELECT
LOCALIZED_STR, LANG.LANGUAGE_CODE, SOURCE_LANG, RUN_ID, ORIG_SYS_REF, MODEL_ID
FROM CZ_IMP_LOCALIZED_TEXTS intl,fnd_languages lang
WHERE intl.run_id=p_run_id AND intl.orig_sys_ref=l_localtext_orig_sys_ref_tbl(i) AND installed_flag in( 'B', 'I')
AND LANGUAGE_CODE<>SOURCE_LANG
AND EXISTS
(SELECT 1 FROM CZ_IMP_LOCALIZED_TEXTS loc,fnd_languages lang
WHERE loc.run_id=p_run_id AND orig_sys_ref = intl.orig_sys_ref
AND lang.language_code <> loc.language
);
l_prop_values_tbl.DELETE;
l_prop_orig_sys_ref_tbl.DELETE;
l_item_master_orig_sys_ref_tbl.DELETE;
l_num_value_tbl.DELETE;
l_localtext_orig_sys_ref_tbl.DELETE;
DELETE FROM CZ_IMP_TMP_ITEMCAT WHERE run_id=p_run_id;
DELETE FROM CZ_IMP_TMP_ITEMCAT WHERE run_id=p_run_id;
SELECT ORIG_SYS_REF FROM CZ_IMP_PROPERTY WHERE RUN_ID=inRUN_ID AND REC_STATUS IS NULL
FOR UPDATE;
insert_end_time number;
l_noupdate cz_xfr_fields.noupdate%TYPE;
INSERT INTO CZ_IMP_PROPERTY
(DESC_TEXT,NAME,ORIG_SYS_REF,RUN_ID,DATA_TYPE,SRC_APPLICATION_ID)
SELECT DESCRIPTION, ELEMENT_NAME, ELEMENT_NAME, inRUN_ID, TEXT_TYPE, G_BOM_APPLICATION_ID
FROM CZ_EXV_ITEM_PROPERTIES
WHERE ITEM_CATALOG_GROUP_ID = itemCatalogGroupId(i);
d_str := inRun_Id || ' Insert property (' || nTop_Id || ') :' || (end_time-st_time)/100.00;
SELECT VALUE INTO sResolve FROM CZ_DB_SETTINGS
WHERE SETTING_ID=v_settings_id AND SECTION_NAME=v_section_name;
SELECT noupdate INTO l_noupdate
FROM cz_xfr_fields f, cz_xfr_tables t
WHERE t.order_seq=f.order_seq
AND t.dst_table='CZ_PROPERTIES'
AND f.xfr_group='IMPORT'
AND t.xfr_group=f.xfr_group
AND f.dst_field='DATA_TYPE'
AND rownum < 2;
l_noupdate:='0';
SELECT data_type INTO l_onl_type
FROM cz_properties
WHERE orig_sys_ref=sName
AND deleted_flag='0'
AND rownum <2;
ELSIF (l_onl_type = TEXT_TYPE AND l_noupdate = '1') THEN
l_check_values := FALSE;
ELSIF (l_onl_type = TEXT_TYPE AND l_noupdate = '0' AND sResolve='YES') THEN
l_check_values := TRUE;
ELSIF (l_onl_type = DECIMAL_TYPE AND l_noupdate = '1') THEN
l_check_values := FALSE;
ELSIF (l_onl_type = DECIMAL_TYPE AND l_noupdate = '0') THEN
l_check_values := TRUE;
tabValues.DELETE;
SELECT TO_NUMBER(element_value) BULK COLLECT INTO tabValues
FROM cz_exv_descr_element_values
WHERE element_name = sName;
UPDATE CZ_IMP_PROPERTY SET DATA_TYPE=DECIMAL_TYPE,DEF_NUM_VALUE=0
WHERE CURRENT OF C_DATATYPE;
insert_end_time number;
INSERT INTO CZ_IMP_ITEM_TYPE_PROPERTY
(FSK_ITEMTYPE_1_1,FSK_ITEMTYPE_1_EXT,FSK_PROPERTY_2_1,FSK_PROPERTY_2_EXT,RUN_ID,
ORIG_SYS_REF,SRC_APPLICATION_ID)
SELECT ITEM_CATALOG_GROUP_ID, ITEM_CATALOG_GROUP_ID, ELEMENT_NAME, ELEMENT_NAME, inRUN_ID,
TO_CHAR(ITEM_CATALOG_GROUP_ID) || ':' || ELEMENT_NAME, G_BOM_APPLICATION_ID
FROM CZ_EXV_ITEM_PROPERTIES
WHERE ITEM_CATALOG_GROUP_ID = itemCatalogGroupId(i);
d_str := inRun_Id || ' Insert item Type prop (' || nTop_Id || ') :' || (end_time-st_time)/100.00;
INSERT INTO CZ_IMP_ITEM_PROPERTY_VALUE
(PROPERTY_VALUE, FSK_PROPERTY_1_1, FSK_PROPERTY_1_EXT,
FSK_ITEMMASTER_2_1,
FSK_ITEMMASTER_2_EXT,
RUN_ID,
ORIG_SYS_REF, SRC_APPLICATION_ID)
SELECT ELEMENT_VALUE, ELEMENT_NAME, ELEMENT_NAME,
CZ_ORAAPPS_INTEGRATE.ITEM_SURROGATE_KEY(to_char(INVENTORY_ITEM_ID), to_char(ORGANIZATION_ID)),
CZ_ORAAPPS_INTEGRATE.ITEM_SURROGATE_KEY(to_char(INVENTORY_ITEM_ID), to_char(ORGANIZATION_ID)),
inRun_ID,
CZ_ORAAPPS_INTEGRATE.ITEM_SURROGATE_KEY(to_char(INVENTORY_ITEM_ID), to_char(ORGANIZATION_ID))|| ':' || ELEMENT_NAME,
G_BOM_APPLICATION_ID
FROM CZ_EXV_ITEM_PROPERTY_VALUES
WHERE INVENTORY_ITEM_ID = inventoryItemId(i)
AND ORGANIZATION_ID = nOrg_ID
AND ELEMENT_VALUE IS NOT NULL;
Inserts PLS_INTEGER;
Updates PLS_INTEGER;
Inserts, Updates, x_failed, Dups, inXFR_GROUP);
Inserts, Updates, x_failed, Dups, inXFR_GROUP);
Inserts, Updates, x_failed, Dups, inXFR_GROUP);
Inserts, Updates, x_failed, Dups,
inXFR_GROUP,p_rp_folder_id);
Inserts, Updates, x_failed, Dups, inXFR_GROUP);
Inserts, Updates, x_failed, Dups, inXFR_GROUP);
Inserts, Updates, x_failed, Dups, inXFR_GROUP);
Inserts, Updates, x_failed, Dups, inXFR_GROUP, p_rp_folder_id);
SELECT server_local_id FROM CZ_SERVERS
WHERE local_name = v_local
AND import_enabled = v_enabled;
vString := 'select count(*) from all_tables' ||fndLinkName||
' where owner='''||v_inv_oracle_schema||''' AND table_name = ''MTL_SYSTEM_ITEMS_TL'' AND ROWNUM<2';
SELECT component_item_id, component_code FROM cz_exv_item_master
WHERE top_item_id = inTopId
AND organization_id = inOrgId
AND explosion_type = inExplType
AND bom_item_type = 1
AND plan_level > 0
AND language = inLang
order by plan_level;
SELECT CZ_XFR_PROJECT_BILLS_S.NEXTVAL INTO nModelId FROM DUAL;
INSERT INTO CZ_XFR_PROJECT_BILLS
(ORGANIZATION_ID,TOP_ITEM_ID,EXPLOSION_TYPE,DELETED_FLAG,MODEL_PS_NODE_ID,copy_addl_child_models,
source_server)
SELECT inOrgId,inTopId,inExplType,'0',nModelId,inCopyChildModels,inServerId
FROM DUAL;
SELECT p.model_ps_node_id INTO nModelId
FROM cz_xfr_project_bills p, cz_devl_projects d
WHERE p.organization_id = inOrgId
AND p.top_item_id = inTopId
AND p.explosion_type = inExplType
AND d.deleted_flag = '0'
AND d.devl_project_id = d.persistent_project_id
AND p.model_ps_node_id = d.devl_project_id;
UPDATE cz_xfr_project_bills SET
deleted_flag = '0',
copy_addl_child_models = inCopyChildModels,
source_server = inServerId
WHERE model_ps_node_id = nModelId;
SELECT CZ_XFR_PROJECT_BILLS_S.NEXTVAL INTO nModelId FROM DUAL;
INSERT INTO CZ_XFR_PROJECT_BILLS
(ORGANIZATION_ID,TOP_ITEM_ID,EXPLOSION_TYPE,DELETED_FLAG,MODEL_PS_NODE_ID,copy_addl_child_models,
source_server)
SELECT inOrgId,inTopId,inExplType,'0',nModelId,inCopyChildModels,inServerId
FROM DUAL;
INSERT INTO CZ_XFR_PROJECT_BILLS
(ORGANIZATION_ID,TOP_ITEM_ID,EXPLOSION_TYPE,DELETED_FLAG,MODEL_PS_NODE_ID,copy_addl_child_models,
source_server)
SELECT inOrgId,inTopId,inExplType,'0',inRefreshModelId,inCopyChildModels,inServerId
FROM DUAL WHERE NOT EXISTS
(SELECT NULL FROM CZ_XFR_PROJECT_BILLS WHERE model_ps_node_id = inRefreshModelId);
UPDATE cz_xfr_project_bills SET
deleted_flag = '0',
copy_addl_child_models = inCopyChildModels,
source_server = inServerId
WHERE model_ps_node_id = inRefreshModelId;
DELETE FROM bom_explosions WHERE explosion_type = inExplType AND organization_id = inOrgId
AND top_item_id = inTopId;
SELECT 1 INTO l_check FROM cz_exv_bom_explosions
WHERE top_item_id = inTopId
AND organization_id= inOrgId
AND explosion_type = inExplType
AND rexplode_flag = 1
AND ROWNUM = 1;
l_update_model_timestamp BOOLEAN := FALSE;
SELECT DST_TABLE FROM CZ_XFR_TABLES
WHERE XFR_GROUP='EXTRACT' AND DISABLED='0'
AND DST_TABLE IN ('CZ_ITEM_TYPES','CZ_PROPERTIES','CZ_ITEM_TYPE_PROPERTIES',
'CZ_ITEM_MASTERS','CZ_ITEM_PROPERTY_VALUES','CZ_LOCALIZED_TEXTS','CZ_DEVL_PROJECTS')
ORDER BY ORDER_SEQ;
SELECT DST_TABLE FROM CZ_XFR_TABLES
WHERE XFR_GROUP='IMPORT' AND DISABLED='0'
AND DST_TABLE IN ('CZ_ITEM_TYPES','CZ_PROPERTIES','CZ_ITEM_TYPE_PROPERTIES',
'CZ_ITEM_MASTERS','CZ_ITEM_PROPERTY_VALUES','CZ_LOCALIZED_TEXTS','CZ_DEVL_PROJECTS',
'CZ_PS_NODES')
ORDER BY ORDER_SEQ;
SELECT 'F' FROM CZ_EXV_BILL_OF_MATERIALS
WHERE ORGANIZATION_ID=nOrg_ID AND ASSEMBLY_ITEM_ID=nTop_ID;
NOUPDATE_SOURCE_BILL_DELETED NUMBER;
SELECT NVL(d.config_engine_type,'L') INTO g_CONFIG_ENGINE_TYPE
FROM cz_xfr_project_bills p, cz_devl_projects d
WHERE p.organization_id = nOrg_ID
AND p.top_item_id = nTop_ID
AND p.explosion_type = sExpl_type
AND d.deleted_flag = '0'
AND d.devl_project_id = d.persistent_project_id
AND p.model_ps_node_id = d.devl_project_id;
SELECT NVL(config_engine_type,'L') INTO g_CONFIG_ENGINE_TYPE FROM CZ_DEVL_PROJECTS
WHERE devl_project_id=REFRESH_MODEL_ID;
SELECT server_local_id INTO server_id
FROM cz_servers
WHERE import_enabled = v_enabled;
SELECT decode(upper(VALUE),'TRUE','1','FALSE','0','T','1','F','0','1','1','0','0','YES','1','NO','0','Y','1','N','0','1')
INTO importUnchangedChildModels FROM CZ_DB_SETTINGS
WHERE upper(SETTING_ID)=v_settings_id AND SECTION_NAME=v_section_name;
SELECT decode(upper(VALUE),'TRUE','1','FALSE','0','T','1','F','0','1','1','0','0','YES','1','NO','0','Y','1','N','0','0')
INTO genStatisticsBom FROM CZ_DB_SETTINGS
WHERE upper(SETTING_ID)=v_settings_id AND SECTION_NAME=v_section_name;
SELECT decode(upper(VALUE),'TRUE','1','FALSE','0','T','1','F','0','1','1','0','0','YES','1','NO','0','Y','1','N','0','0')
INTO genStatisticsCz FROM CZ_DB_SETTINGS
WHERE upper(SETTING_ID)=v_settings_id AND SECTION_NAME=v_section_name;
SELECT decode(upper(VALUE),'TRUE',1,'FALSE',0,'T',1,'F',0,'1',1,'0',0,'YES',1,'NO',0,'Y',1,'N',0,0)
INTO allowDecimalOptionClass FROM CZ_DB_SETTINGS
WHERE upper(SETTING_ID)=v_settings_id AND SECTION_NAME=v_section_name;
SELECT CZ_XFR_RUN_INFOS_S.NEXTVAL INTO genRun_ID FROM DUAL;
INSERT INTO CZ_XFR_RUN_INFOS (RUN_ID,STARTED,LAST_ACTIVITY,COMPLETED)
VALUES (genRun_ID,SYSDATE,SYSDATE,'0');
SELECT DECODE(UPPER(value), 'YES', 1, 'Y', 1, '1', 1, 'TRUE', 1, 'T', 1,
'NO', 0, 'N', 0, '0', 0, 'FALSE', 0, 'F', 0,
0)
INTO nRunExploder FROM CZ_DB_SETTINGS
WHERE UPPER(SECTION_NAME) = v_section_name
AND UPPER(SETTING_ID) = v_settings_id;
SELECT VALUE INTO sVersion FROM CZ_DB_SETTINGS
WHERE UPPER(SECTION_NAME) = v_section_name
AND UPPER(SETTING_ID) = v_settings_id;
SELECT DECODE(SUBSTR(sVersion,1,INSTR(sVersion,'.',1,2)-1),
'11.5','YYYY/MM/DD HH24:MI','DD-MON-RR HH24:MI')
INTO dDateFormat FROM DUAL;
SELECT VALUE INTO nCommit_size FROM CZ_DB_SETTINGS
WHERE UPPER(SETTING_ID) = v_settings_id
AND UPPER(SECTION_NAME) = v_section_name;
SELECT VALUE INTO nMax_err FROM CZ_DB_SETTINGS
WHERE UPPER(SETTING_ID) = v_settings_id
AND UPPER(SECTION_NAME) = v_section_name;
NOUPDATE_SOURCE_BILL_DELETED:=CZ_UTILS.GET_NOUPDATE_FLAG('CZ_XFR_PROJECT_BILLS','SOURCE_BILL_DELETED', 'IMPORT');
processed_expls_tbl.DELETE;
processed_expls_tbl.DELETE;
FOR j IN (SELECT devl_project_id FROM cz_devl_projects
WHERE devl_project_id = nModelId AND deleted_flag='0')
LOOP
cz_security_pvt.lock_model(
p_api_version => 1.0,
p_model_id => nModelId,
p_lock_child_models => FND_API.G_TRUE,
p_commit_flag => FND_API.G_TRUE,
x_locked_entities => l_locked_models,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
UPDATE CZ_XFR_PROJECT_BILLS SET
SOURCE_BILL_DELETED=DECODE(NOUPDATE_SOURCE_BILL_DELETED,0,'0',SOURCE_BILL_DELETED)
WHERE ORGANIZATION_ID=nOrg_ID AND TOP_ITEM_ID=nTop_ID AND EXPLOSION_TYPE=sExpl_type;
UPDATE CZ_XFR_PROJECT_BILLS SET
SOURCE_BILL_DELETED=DECODE(NOUPDATE_SOURCE_BILL_DELETED,0,'1',SOURCE_BILL_DELETED)
WHERE ORGANIZATION_ID=nOrg_ID AND TOP_ITEM_ID=nTop_ID AND EXPLOSION_TYPE=sExpl_type;
processed_expls_tbl.DELETE;
processed_expls_tbl.DELETE;
itemCatalogGroupId.DELETE;
repCatalogGroupId.DELETE;
inventoryItemId.DELETE;
repItemId.DELETE;
hashCatalog.DELETE;
SELECT item_catalog_group_id, inventory_item_id
BULK COLLECT INTO repCatalogGroupId, repItemId
FROM cz_exv_item_master
WHERE organization_id = nOrg_ID
AND top_item_id = nTop_ID
AND explosion_type = sExpl_type
AND item_catalog_group_id IS NOT NULL
AND language = l_lang;
hashCatalog.DELETE;
FOR model IN (SELECT DISTINCT component_id FROM cz_model_ref_expls WHERE model_id = nModelId
AND deleted_flag = '0' AND (ps_node_type IN (263, 264) OR parent_expl_node_id IS NULL))LOOP
FOR j IN (SELECT devl_project_id FROM cz_devl_projects
WHERE devl_project_id = model.component_id AND deleted_flag='0')
LOOP
l_compare_detailed_types := TRUE;
FOR i IN (SELECT ps_node_id, detailed_type_id
FROM CZ_UI_TYPEDPSN_V
WHERE devl_project_Id = model.component_id
AND ps_node_type IN (CZ_TYPES.PS_NODE_TYPE_BOM_MODEL, CZ_TYPES.PS_NODE_TYPE_BOM_OPTION_CLASS)
AND deleted_flag = '0')
LOOP
l_detailed_types_tbl(i.ps_node_id) := i.detailed_type_id;
FOR i IN (SELECT src_table FROM cz_xfr_tables
WHERE xfr_group='IMPORT'
AND disabled='0') LOOP
fnd_stats.GATHER_TABLE_STATS('CZ',i.src_table);
SELECT model_ps_node_id INTO nModelId
FROM cz_xfr_project_bills
WHERE organization_id = nOrg_ID
AND top_item_id = nTop_ID
AND explosion_type = sExpl_type
AND last_import_run_id = genRun_ID;
UPDATE CZ_XFR_RUN_INFOS SET
LAST_ACTIVITY=SYSDATE,
COMPLETED='1'
WHERE RUN_ID=genRun_ID;
FOR model IN (SELECT devl_project_id FROM cz_imp_devl_project WHERE run_id = genRun_ID
AND rec_status = 'OK')LOOP
FOR i IN (SELECT ps_node_id, detailed_type_id
FROM CZ_UI_TYPEDPSN_V
WHERE devl_project_Id = model.devl_project_id
AND ps_node_type IN (CZ_TYPES.PS_NODE_TYPE_BOM_MODEL, CZ_TYPES.PS_NODE_TYPE_BOM_OPTION_CLASS)
AND creation_date <= l_current_date
AND deleted_flag = '0')
LOOP
IF l_detailed_types_tbl.EXISTS( i.ps_node_id ) THEN
IF l_detailed_types_tbl(i.ps_node_id) <> i.detailed_type_id THEN
UPDATE CZ_PS_NODES set UI_TIMESTAMP_CHANGETYPE = SYSDATE
WHERE devl_project_id = model.devl_project_id
AND ps_node_id = i.ps_node_id;
l_update_model_timestamp := TRUE;
IF l_update_model_timestamp THEN
UPDATE cz_devl_projects set ui_timestamp_struct_update = SYSDATE
WHERE devl_project_id = model.devl_project_id;