Seifware Inc.
1-87-SEIFWARE
Item Master Duplicate Identification
Item Master Duplicate Identification
The item master is an organization record template that has unique specifications (weight, dimensions, type, etc.) representing a spare, consumable, chemicals, repairable assets and other type of materials to perform jobs in production, construction, maintenance, operations and other administrative tasks.
This article demonstrates how end-users can prevent creating duplicate item master records based on the vendor/manufacturer part numbers, item description and item characteristics. Since every organization has its unique requirements. The below suggested solution is intended for general use only. Please contact our professional services for guaranteed solutions/support and in-depth item master analysis.
Organizations continuously create and update their item master catalog to fulfill operational, maintenance and procurement needs. Item master duplication is inevitable as data grows in the absence of related quality management processes to identify identical records and enrich key item characteristics.
One of the simplest forms of item master quality management is to compare and identify records that share similar key data:
-
- Item master records that have similar vendor/manufacturer part numbers (excluding special characters).
- Item master records that have exact descriptions but have no classifications.
- Item master records that have exact characteristics regardless of their descriptions.
By displaying similar vendor/manufacturer parts and items while creating or updating an item master record, end-users will get a chance to easily identify possible duplicates. In the below example, item# 300001 has a similar vendor part# CAT-100096 compared to item# 300589 (Valve, Ball) and has the same characteristics of another item# 300501.
Technical Details:
| Relationship | From | To | Reference |
| SIMILARPARTS | INVVENDOR | INVVENDOR | SIMILARPARTS |
| SIMILARITEMS | ITEM | ITEM | SIMILARITEMS |
References:
- SIMILARPARTS (ORACLE/MSSQL – Adjust for other database types):
itemnum!=:itemnum
and
upper(
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
replace(replace(replace(replace(replace(replace(replace(replace(replace(
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(invvendor.catalogcode,'~',''),' ',''),'<',''),',',''),'>',''),'.',''),'?',''),'/',''),'''',''),'"',''),':',''),';','')
,'}',''),'{',''),'[',''),']',''),'|',''),'\',''),'+',''),'=',''),'_','')
,'-',''),'~',''),'!',''),'@',''),'#',''),'$',''),'%',''),'^',''),'&',''),'*',''),')',''),'(',''),')','')
)=
upper(
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
replace(replace(replace(replace(replace(replace(replace(replace(replace(
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(:catalogcode,'~',''),' ',''),'<',''),',',''),'>',''),'.',''),'?',''),'/',''),'''',''),'"',''),':',''),';','')
,'}',''),'{',''),'[',''),']',''),'|',''),'\',''),'+',''),'=',''),'_','')
,'-',''),'~',''),'!',''),'@',''),'#',''),'$',''),'%',''),'^',''),'&',''),'*',''),')',''),'(',''),')','')
)
and (
case when :vendor is null then
(case when :manufacturer is null then 'N/A' else :manufacturer end )
else :vendor end
)
=(
case when invvendor.vendor is null then
(case when invvendor.manufacturer is null then 'N/A' else invvendor.manufacturer end )
else invvendor.vendor end
)
- SIMILARITEMS (Oracle – Adjust for other database types):
itemnum!=:itemnum and itemsetid=:itemsetid and
(
(
(item.description=:description and item.classstructureid is null and :classstructureid is null)
or
(
:classstructureid is not null and item.classstructureid=:classstructureid and
not exists (
select 1 from itemspec i1 inner join itemspec i2
on i1.assetattrid= i2.assetattrid
and i1.classstructureid= i2.classstructureid
and i1.itemsetid= i2.itemsetid
where i1.itemnum=:itemnum and i2.itemnum=item.itemnum
and
(
(
case when
(case when ''||i1.numvalue is null then i1.alnvalue else ''||i1.numvalue end) is null then 'N/A' else
(case when ''||i1.numvalue is null then i1.alnvalue else ''||i1.numvalue end) end
)
!=
(
case when
(case when ''||i2.numvalue is null then i2.alnvalue else ''||i2.numvalue end) is null then 'N/A' else
(case when ''||i2.numvalue is null then i2.alnvalue else ''||i2.numvalue end) end
)
)
)
)
)
)
- XML Changes:
<table datasrc="similarparts_table" id="similar_parts_table" inputmode="readonly" label="Similar Parts for {0}" parentdatasrc="vendor_details" parentemptylabel="Similar Parts" relationship="similarparts" titleattributes="catalogcode">
<sectionheader id="similarparts_table_sectionheader">
<paramvalues id="similarparts_table__1_sec_params" property="label">
<paramvalue dataattribute="catalogcode" id="similarparts_table__1_sec_param_0" position="0"/>
</paramvalues>
</sectionheader>
<tablebody id="1576735767875">
<tablecol dataattribute="itemnum" id="1576735767890"/>
<tablecol dataattribute="item.description" id="1576815741867"/>
<tablecol dataattribute="catalogcode" id="1576811098133"/>
<tablecol dataattribute="vendor" id="1576811145758"/>
<tablecol dataattribute="manufacturer" id="1576811149289"/>
</tablebody>
<tabledetails id="15767357678902"/>
</table>
<table id="1576813684367" inputmode="readonly" label="Similar Items" relationship="similaritems">
<tablebody id="1576813684383">
<tablecol dataattribute="itemnum" id="1576813730274"/>
<tablecol dataattribute="description" id="1576813739836"/>
</tablebody>
<tabledetails id="1576813684414"/>
</table>
Our approach is to keep things simple and keep you informed of all available options. Contact us for more details!
Tags
About The Author
Posts You might like
