[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
TYPE typeMinimumSelected IS TABLE OF cz_ps_nodes.minimum_selected%TYPE INDEX BY BINARY_INTEGER;
TYPE typeMaximumSelected IS TABLE OF cz_ps_nodes.maximum_selected%TYPE INDEX BY BINARY_INTEGER;
EXECUTE IMMEDIATE 'SELECT count(*) FROM org_organization_definitions@' || inLinkName
INTO nGeneric;
SELECT fndnam_link_name, local_name INTO linkName, instanceName
FROM cz_servers
WHERE server_local_id = sourceServer;
tabMinimumSelected typeMinimumSelected;
tabMaximumSelected typeMaximumSelected;
debug('Update candidate entry: ps_node_id = ' || tabPsNodeId(j) || ', item_id = ' || tabItemId(j) || ', intl_text_id = ' || tabIntlTextId(j));
FOR c_prop IN (SELECT name, data_type, property_value, property_num_value , p.src_application_id
FROM cz_properties p, cz_item_property_values v
WHERE p.deleted_flag = FLAG_NOT_DELETED
AND p.orig_sys_ref IS NOT NULL
AND v.deleted_flag = FLAG_NOT_DELETED
AND v.item_id = tabItemId(j)
AND p.property_id = v.property_id
AND p.data_type NOT IN(TL_TEXT_TYPE)
UNION
SELECT name, data_type,def_value as property_value,def_num_value as property_num_value,src_application_id
FROM cz_properties p
WHERE p.property_id IN(
SELECT it.property_id FROM cz_item_type_properties it
WHERE it.item_type_id IN(SELECT im.item_type_id FROM CZ_ITEM_MASTERS im
WHERE im.item_id=tabItemId(j) AND
im.deleted_flag=FLAG_NOT_DELETED) AND
it.deleted_flag=FLAG_NOT_DELETED
) AND
NOT EXISTS(SELECT NULL FROM cz_item_property_values iv
WHERE iv.property_id=p.property_id AND iv.item_id=tabItemId(j) AND
iv.deleted_flag=FLAG_NOT_DELETED) AND
p.deleted_flag = FLAG_NOT_DELETED AND
p.src_application_id = APC_APPL_ID AND --Bug13829564 checking the data which comes from PIM
p.orig_sys_ref IS NOT NULL AND
p.data_type NOT IN(TL_TEXT_TYPE)
) LOOP
BEGIN
l_src_application_id:=c_prop.src_application_id;
'SELECT distinct item_catalog_group_id '||
' FROM mtl_system_items_b'|| targetLinkName ||
' WHERE organization_id = :1 '||
' AND inventory_item_id = :2 '||
' AND item_catalog_group_id IS NOT NULL'
INTO l_item_catalog_group_id
USING targetOrgId,itemInventoryId;
'SELECT distinct database_column, attr_group_id, attr_id'||
' FROM cz_exv_apc_properties'|| targetLinkName ||
' WHERE application_id = :1 '||
' AND attr_group_name = :2 '||
' AND attr_name = :3 '||
' AND item_catalog_group_id IN (SELECT item_catalog_group_id '||
' FROM mtl_item_catalog_groups'|| targetLinkName ||' CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id '||
' START WITH item_catalog_group_id = :4 )'
INTO l_database_column , l_attr_group_id ,l_attr_id
USING APC_APPL_ID , substr(c_prop.name,1,position-1) , substr(c_prop.name,position+1) ,l_item_catalog_group_id;
'SELECT to_char('|| l_database_column ||') FROM CZ_EXV_ITEM_APC_PROP_VALUES' || targetLinkName ||
' WHERE INVENTORY_ITEM_ID= :1 and ORGANIZATION_ID = :2 and ITEM_CATALOG_GROUP_ID =:3 '||
' and ATTR_GROUP_ID =:4'
INTO elementValue
USING itemInventoryId ,targetOrgId,l_item_catalog_group_id,l_attr_group_id ;
'SELECT default_value '||
' FROM cz_exv_apc_properties'|| targetLinkName ||
' WHERE application_id = :1 '||
' AND attr_group_id = :2 '||
' AND attr_id = :3 '
INTO elementValue
USING APC_APPL_ID , l_attr_group_id , l_attr_id;
'SELECT NVL(element_value, 0) FROM mtl_descr_element_values' || targetLinkName ||
' WHERE inventory_item_id = :1' ||
' AND element_name = :2'
INTO elementValue
USING itemInventoryId, c_prop.name;
'SELECT b.bill_sequence_id, b.common_bill_sequence_id, i.inventory_item_id, i.item_catalog_group_id' ||
' FROM bom_bill_of_materials' || targetLinkName || ' b, mtl_system_items_vl' || targetLinkName || ' i' ||
' WHERE i.concatenated_segments = :1' ||
' AND i.organization_id = :2' ||
' AND b.assembly_item_id = i.inventory_item_id' ||
' AND b.organization_id = i.organization_id' ||
' AND b.alternate_bom_designator IS NULL'
INTO billSequenceId, billCommonSequenceId, itemInventoryId, itemCatalogGroupId
USING tabRefPartNbr(j), targetOrgId;
'SELECT i.inventory_item_id, i.concatenated_segments, b.effectivity_date, b.disable_date, b.component_quantity,' ||
' b.component_sequence_id, b.high_quantity, b.low_quantity, b.mutually_exclusive_options,' ||
' b.bom_item_type, b.optional, i.item_catalog_group_id' ||
' FROM mtl_system_items_vl' || targetLinkName || ' i, bom_inventory_components' || targetLinkName || ' b' ||
' WHERE b.bill_sequence_id = :1' ||
' AND b.implementation_date IS NOT NULL' ||
' AND i.organization_id = :2' ||
' AND (b.optional = :3 OR b.bom_item_type <= :4)' ||
' AND i.inventory_item_id = b.component_item_id' ||
' ORDER BY i.concatenated_segments, b.effectivity_date'
USING useSequenceId, targetOrgId, ORACLE_YES, ORACLE_BOM_OPTIONCLASS;
nodeQuantity := NVL(tabMinimumSelected(slidePointer), 0);
nodeQuantity := NVL(tabMaximumSelected(slidePointer), 0);
(tabMaximumSelected(slidePointer) IS NULL OR tabMaximumSelected(slidePointer) < 1))THEN
--'Maximum selected value does not match for item ''%ITEMNAME'' with parent ''%PARENTNAME'' in configuration model ''%MODELNAME'''
report(CZ_UTILS.GET_TEXT('CZ_SYNC_MAXIMUM_SELECTED', 'ITEMNAME', tabRefPartNbr(slidePointer), 'PARENTNAME', localParentName, 'MODELNAME', generate_name), URGENCY_WARNING);
SELECT p.ps_node_id, p.parent_id, p.ps_node_type, p.item_id, p.effective_from,
p.effective_until, p.minimum, maximum, p.minimum_selected, p.maximum_selected,
p.initial_value, p.initial_num_value, p.reference_id, p.name, p.orig_sys_ref, p.component_sequence_path, --sselahi
p.component_sequence_id, p.intl_text_id, p.bom_required_flag, t.orig_sys_ref,
i.ref_part_nbr, i.orig_sys_ref, i.item_type_id
BULK COLLECT INTO tabPsNodeId, tabParentId, tabPsNodeType, tabItemId, tabEffectiveFrom,
tabEffectiveUntil, tabMinimum, tabMaximum, tabMinimumSelected, tabMaximumSelected,
tabInitialValue, tabInitNumVal, tabReferenceId, tabPsNodeName, tabOrigSysRef, tabSequencePath, -- sselahi
tabSequenceId, tabIntlTextId, tabBomRequiredFlag, tabTextOrigSysRef,
tabRefPartNbr, tabItemOrigSysRef, tabItemMasterTypeId
FROM cz_item_masters i, cz_ps_nodes p, cz_localized_texts t
WHERE p.devl_project_id = p_model_id
AND p.deleted_flag = FLAG_NOT_DELETED
AND p.orig_sys_ref IS NOT NULL
AND p.src_application_id = 702
AND t.language (+) = baseLanguageCode
AND p.item_id = i.item_id (+)
AND p.intl_text_id = t.intl_text_id (+)
ORDER BY p.parent_id, i.ref_part_nbr, p.effective_from;
modelNameStack.DELETE(modelNameStack.COUNT);
SELECT orig_sys_ref, name, NVL ( config_engine_type, 'L' ) INTO modelOrigSysRef, modelName, modelEngineType
FROM cz_devl_projects
WHERE deleted_flag = FLAG_NOT_DELETED
AND devl_project_id = p_model_id;
SELECT source_server INTO sourceServer
FROM cz_xfr_project_bills
WHERE model_ps_node_id = p_model_id
AND deleted_flag = FLAG_NOT_DELETED;
EXECUTE IMMEDIATE 'SELECT organization_id FROM org_organization_definitions' || targetLinkName ||
' WHERE UPPER(organization_name) = ' ||
' (SELECT UPPER(organization_name) FROM org_organization_definitions' || sourceLinkName ||
' WHERE organization_id = :1)'
INTO targetOrgId USING sourceOrgId;
FOR c_pub IN (SELECT publication_id, top_item_id, organization_id, product_key
FROM cz_model_publications
WHERE model_id = p_model_id
AND deleted_flag = FLAG_NOT_DELETED)LOOP
tabCandidatePubl(tabCandidatePubl.COUNT + 1) := c_pub.publication_id;
FOR c_model IN (SELECT component_id FROM cz_model_ref_expls
WHERE model_id = p_model_id
AND deleted_flag = FLAG_NOT_DELETED
AND ps_node_type = PS_NODE_TYPE_REFERENCE
AND node_depth = 1)LOOP
execute_model(c_model.component_id);
SELECT fndnam_link_name, server_local_id INTO linkName, g_target_instance
FROM cz_servers
WHERE UPPER(local_name) = UPPER(p_target_name);
SELECT DISTINCT source_server INTO sourceServer
FROM cz_xfr_project_bills b, cz_devl_projects r
WHERE b.deleted_flag = FLAG_NOT_DELETED
AND r.deleted_flag = FLAG_NOT_DELETED
AND b.model_ps_node_id = r.devl_project_id;
FOR c_pub IN (SELECT publication_id FROM cz_model_publications
WHERE deleted_flag = FLAG_NOT_DELETED
AND export_status = PUBLICATION_STATUS_PROCESSING)LOOP
--'Synchronization cannot be done while a publication is processing. At least one publication (%PUBLICATIONID) is currently in processing status'
report(CZ_UTILS.GET_TEXT('CZ_SYNC_PUB_PROCESSING'), URGENCY_ERROR);
component_item_id_map.DELETE;
component_seq_id_map.DELETE;
catalog_group_id_map.DELETE;
organization_id_map.DELETE;
UPDATE cz_ps_nodes SET
orig_sys_ref = hashRbNodeOrigSysRef(tabCandidateNode(i)),
component_sequence_path = hashRbNodeSequencePath(tabCandidateNode(i)),
component_sequence_id = hashRbNodeSequenceId(tabCandidateNode(i))
WHERE ps_node_id = tabCandidateNode(i);
debug('Table cz_ps_nodes updates rolled back');
UPDATE cz_item_masters SET
orig_sys_ref = hashRbItemOrigSysRef(tabCandidateItem(i))
WHERE item_id = tabCandidateItem(i);
debug('Table cz_item_masters updates rolled back');
UPDATE cz_devl_projects SET
orig_sys_ref = hashRbDevlOrigSysRef(tabCandidateDevl(i))
WHERE devl_project_id = tabCandidateDevl(i);
debug('Table cz_devl_projects updates rolled back');
UPDATE cz_localized_texts SET
orig_sys_ref = hashRbTextOrigSysRef(tabCandidateText(i))
WHERE intl_text_id = tabCandidateText(i);
debug('Table cz_localized_texts updates rolled back');
UPDATE cz_item_types SET
orig_sys_ref = hashRbTypeOrigSysRef(tabCandidateType(i))
WHERE item_type_id = tabCandidateType(i);
debug('Table cz_item_types updates rolled back');
UPDATE cz_xfr_project_bills SET
organization_id = hashRbOrganizationId(tabCandidateProj(i)),
top_item_id = hashRbTopItemId(tabCandidateProj(i)),
component_item_id = hashRbComponentItemId(tabCandidateProj(i)),
source_server = hashRbSourceServer(tabCandidateProj(i))
WHERE model_ps_node_id = tabCandidateProj(i);
debug('Table cz_xfr_project_bills updates rolled back');
UPDATE cz_model_publications SET
organization_id = hashRbPubOrganizationId(tabCandidatePubl(i)),
top_item_id = hashRbPubTopItemId(tabCandidatePubl(i)),
product_key = hashRbPubProductKey(tabCandidatePubl(i))
WHERE publication_id = tabCandidatePubl(i);
debug('Table cz_model_publications updates rolled back');
UPDATE cz_item_property_values SET
orig_sys_ref = tabRbItmPropValOrigSysRef(i)
WHERE item_id = tabRbItmPropValItemId(i)
and property_id=tabRbItmPropValPropId(i)
and deleted_flag='0';
debug('Table cz_item_property_values updates rolled back');
UPDATE CZ_ITEM_TYPE_PROPERTIES SET
orig_sys_ref = tabRbItmTypPropOrigSysRef(i)
WHERE item_type_id = tabRbItmTypPropItTypeId(i)
and property_id=tabRbItmTypPropId(i)
and deleted_flag='0';
debug('Table CZ_ITEM_TYPE_PROPERTIES updates rolled back');
SELECT TO_NUMBER(value) INTO CommitBlockSize
FROM cz_db_settings
WHERE UPPER(setting_id) = COMMIT_BLOCK_SETTING_ID
AND UPPER(section_name) = DBSETTINGS_SECTION_NAME;
debug('Updating cz_ps_nodes table, ' || localCount || ' update candidate records, time started: ' || TO_CHAR(SYSDATE,'HH24:MI:SS'));
UPDATE cz_ps_nodes SET
orig_sys_ref = NVL(psnode_origSysRef(orig_sys_ref), orig_sys_ref),
component_sequence_path = NVL(psnode_compSeqPath(component_sequence_path), component_sequence_path),
component_sequence_id = NVL(psnode_compSeqId(component_sequence_id), component_sequence_id)
WHERE ps_node_id = tabCandidateNode(i);
debug('Updating cz_item_masters table, ' || localCount || ' update candidate records, time started: ' || TO_CHAR(SYSDATE,'HH24:MI:SS'));
UPDATE cz_item_masters SET
orig_sys_ref = NVL(itemMaster_origSysRef(orig_sys_ref), orig_sys_ref)
WHERE item_id = tabCandidateItem(i);
debug('Updating cz_devl_projects table, ' || localCount || ' update candidate records, time started: ' || TO_CHAR(SYSDATE,'HH24:MI:SS'));
UPDATE cz_devl_projects SET
orig_sys_ref = NVL(devlProject_origSysRef(orig_sys_ref), orig_sys_ref)
WHERE devl_project_id = tabCandidateDevl(i);
debug('Updating cz_localized_texts table, ' || localCount || ' update candidate records, time started: ' || TO_CHAR(SYSDATE,'HH24:MI:SS'));
UPDATE cz_localized_texts SET
orig_sys_ref = NVL(locText_origSysRef(orig_sys_ref), orig_sys_ref)
WHERE intl_text_id = tabCandidateText(i);
debug('Updating cz_item_types table, ' || localCount || ' update candidate records, time started: ' || TO_CHAR(SYSDATE,'HH24:MI:SS'));
UPDATE cz_item_types SET
orig_sys_ref = NVL(itemtype_origSysRef(orig_sys_ref), orig_sys_ref)
WHERE item_type_id = tabCandidateType(i);
debug('Updating cz_xfr_project_bills table, ' || localCount || ' update candidate records, time started: ' || TO_CHAR(SYSDATE,'HH24:MI:SS'));
UPDATE cz_xfr_project_bills SET
organization_id = NVL(projectBill_orgId(organization_id), organization_id),
top_item_id = NVL(projectBill_topItemId(top_item_id), top_item_id),
component_item_id = NVL(projectBill_compItemId(component_item_id), component_item_id),
source_server = NVL(projectBill_sourceServer(source_server), source_server)
WHERE model_ps_node_id = tabCandidateProj(i);
debug('Updating cz_model_publications table, ' || localCount || ' update candidate records, time started: ' || TO_CHAR(SYSDATE,'HH24:MI:SS'));
UPDATE cz_model_publications SET
organization_id = NVL(modelPublication_orgId(organization_id), organization_id),
top_item_id = NVL(modelPublication_topItemId(top_item_id), top_item_id),
product_key = NVL(modelPublication_productKey(product_key), product_key)
WHERE publication_id = tabCandidatePubl(i);
debug('Updating cz_item_property_values table, ' || localCount || ' update candidate records, time started: ' || TO_CHAR(SYSDATE,'HH24:MI:SS'));
select ITEM_ID,PROPERTY_ID,ORIG_SYS_REF
BULK COLLECT INTO l_tabRbItmPropValItemId,l_tabRbItmPropValPropId,l_tabRbItmPropValOrigSysRef
FROM cz_item_property_values where item_id = tabCandidateItem(i)
and deleted_flag='0';
UPDATE cz_item_property_values SET
orig_sys_ref = NVL(itemPropValues_origSysRef(orig_sys_ref),orig_sys_ref)
WHERE item_id = tabCandidateItem(i)
and deleted_flag='0';
debug('Updating CZ_ITEM_TYPE_PROPERTIES table, ' || localCount || ' update candidate records, time started: ' || TO_CHAR(SYSDATE,'HH24:MI:SS'));
select ITEM_TYPE_ID,PROPERTY_ID,ORIG_SYS_REF
BULK COLLECT INTO l_tabRbItmTypPropItTypeId,l_tabRbItmTypPropId,l_tabRbItmTypPropOrigSysRef
FROM cz_item_type_properties where item_type_id = tabCandidateType(i)
and deleted_flag='0';
UPDATE CZ_ITEM_TYPE_PROPERTIES SET
orig_sys_ref = NVL(itemTypeProp_origSysRef(orig_sys_ref),orig_sys_ref)
WHERE item_type_id = tabCandidateType(i)
and deleted_flag='0';
SELECT cz_xfr_run_infos_s.NEXTVAL INTO p_run_id FROM DUAL;
SELECT DECODE(UPPER(value), '1', 1, 'ON', 1, 'Y', 1, 'YES', 1,'TRUE', 1, 'ENABLE', 1,
'0', 0, 'OFF', 0, 'N', 0, 'NO', 0,'FALSE', 0, 'DISABLE', 0,
1) --default value
INTO VerifyItemProperties
FROM cz_db_settings
WHERE UPPER(setting_id) = VERIFY_PROPERTIES_SETTING_ID
AND UPPER(section_name) = DBSETTINGS_SECTION_NAME;
SELECT TO_NUMBER(value) INTO DaysTillEpochEnd
FROM cz_db_settings
WHERE UPPER(setting_id) = DAYSTILLEPOCHEND_SETTING_ID
AND UPPER(section_name) = DBSETTINGS_SECTION_NAME;
SELECT language_code INTO baseLanguageCode
FROM fnd_languages
WHERE installed_flag = FND_LANGUAGES_BASE;
SELECT count(*) + 1 INTO numberOfLanguages
FROM fnd_languages
WHERE installed_flag = FND_LANGUAGES_INSTALLED;
SELECT item_type_id, name, orig_sys_ref
BULK COLLECT INTO tabItemTypeId, tabItemTypeName, tabItemTypeOrigSysRef
FROM cz_item_types
WHERE deleted_flag = FLAG_NOT_DELETED
AND orig_sys_ref IS NOT NULL
AND REPLACE(TRANSLATE(orig_sys_ref, '0123456789', '0000000000'), '0', NULL) IS NULL;
FOR c_model IN (SELECT object_id FROM cz_rp_entries
WHERE deleted_flag = FLAG_NOT_DELETED
AND object_type = REPOSITORY_TYPE_PROJECT) LOOP
execute_model(c_model.object_id);
FOR c_model IN (SELECT model_id FROM cz_model_publications
WHERE deleted_flag = FLAG_NOT_DELETED
AND source_target_flag = PUBLICATION_TARGET_FLAG
AND export_status = PUBLICATION_STATUS_OK) LOOP
execute_model(c_model.model_id);
UPDATE cz_item_masters item SET deleted_flag = '1'
WHERE deleted_flag = FLAG_NOT_DELETED
AND src_application_id = 401
AND NOT EXISTS
(SELECT NULL FROM cz_ps_nodes
WHERE deleted_flag = FLAG_NOT_DELETED
AND item_id = item.item_id);