select
[PartUOM].[Company] as [PartUOM_Company],
[PartUOM].[PartNum] as [PartUOM_PartNum],
[PartUOM].[UOMCode] as [PartUOM_UOMCode],
[PartUOM].[ConvFactor] as [PartUOM_ConvFactor],
[PartUOM].[Active] as [PartUOM_Active],
[UOMClass].[UOMClassID] as [UOMClass_UOMClassID],
[UOMConv].[UOMCode] as [UOMConv_UOMCode],
[UOMConv].[ConvFactor] as [UOMConv_ConvFactor],
[UOMConv].[PartSpecific] as [UOMConv_PartSpecific]
from Erp.PartUOM as PartUOM
inner join Erp.Part as Part on
PartUOM.Company = Part.Company
and PartUOM.PartNum = Part.PartNum
inner join Erp.UOMClass as UOMClass on
Part.Company = UOMClass.Company
and Part.UOMClassID = UOMClass.UOMClassID
inner join Erp.UOMConv as UOMConv on
UOMClass.Company = UOMConv.Company
and UOMClass.UOMClassID = UOMConv.UOMClassID
where (PartUOM.UOMCode = UOMConv.UOMCode and UOMConv.PartSpecific = 0)
I believe the standard measures aren’t stored they are calculated on the fly. (With the exception of if you are tracking that UOM for inventory purposes)
I inherited a not-yet-live system with what may be irreparably messed up UOMs. Is it fair to say that if this BAQ returns anything, something is very wrong? Or is there some legitimate reason why there would be PartUOMs for conversions that aren’t part-specific?
@KevinK If the UOMClass is not dependent on the container size then those conversion factors are not in PartUOM. IE class is Length FT tp Meters and Inches are always the same so they are not, but they appear on the Parts uom page just like they were.
Class Tape depends on the length of the roll to FT and IN, so those are part specific and are in the table.
If I understand you correctly, you’re explaining why I’d use a part-specific UOM conversion. I know this kind of conversion is stored in PartUOM. But are part-specific UOM conversions the only reason a PartUOM row should exist? Because (unless I goofed in writing my BAQ) I have thousands of PartUOM rows for conversions that are not marked part-specific in UOMConv.
@KevinK It appears my assumptions are incorrect. There appear to be several other cases where a non part specific row will exist. I have 22K of these and have no issues, so I don’t think you have a concern.
It looks like the base uom of a class with other part specific uoms will exist as not part specific.
My example above of FT, M and IN was wrong. They are all not part specific and in the table.
I have a class with PR and EAP as not part specific PR is not in the table, but EAP is. ??
That’s good to know. I started looking at this after I identified a couple referential integrity issues related to UOMs:
Epicor allows you to remove a UOM from a UOM class when the combination of class and UOM is still referenced by a part’s IUM/SalesUM/PUM. Referencing the part on an order then explodes. This has been acknowledged as PRB0242806.
And removing a UOM from a UOM class leaves orphaned PartUOM rows for parts in that class. Support said they could give me a data fix if I gave them a list of rows. That’s when I noticed rows for non-part-specific conversions. I thought maybe someone checked and unchecked “part specific” on the UOMConv, creating these seemingly orphaned rows, but maybe there’s some other explanation for them being there.
I created a test part in my Volume class, which has only non-part-specific UOMConvs. The non-part-specific conversions were shown on the UOMs tab in Part, but no actual PartUOM rows were created. I added this part to an OrderDtl and saved the order quantity with different UOMs. Still no PartUOM was created.
Then I created another test part in my Counted class. PartUOMs for part-specific conversions were created when the part was saved.
So now I’m leaning back toward skepticism that PartUOMs are supposed to exist for non-part-specific UOMConvs…
You are right @kevink. I just checked the partuom table. When I replied earlier I just looked at the Part Tracker, which does show the uom conversion for non part specific uoms also.
So to put records straight partuom is only populating in our case for part specific uoms. We do have UOM classes for part specific UOMs set up as Type “Other”.
My latest experiments give me hope that my UOMs can be fixed. Here’s what I’ve discovered so far. Note that all these experiments are with conversions that have not been marked used.
Adding Parts
Creating a part creates PartUOM rows for all part-specific UOMConvs in the part’s UOM class. This seems to be unnecessary database clutter.
Adding UOMs
Subsequently adding a part-specific UOMConv to the class does not create PartUOM rows for parts already in the class. This does not seem to cause any problems, which is why creating the PartUOM rows when a part is created is just clutter. The new part-specific UOMConv appears in the part UI even though there’s no PartUOM row. The conversion is usable for the part as if a PartUOM row existed. The conversion factor is the default from UOMConv. Using it still does not create a PartUOM row. Only editing the factor actually creates a PartUOM row.
Deleting UOMConvs
Removing a part-specific UOMConv from a class leaves orphaned PartUOM rows. There is no indication in PartUOM that the row is orphaned. Even PartUOM.Active is still true. But the UI view model apparently combines PartUOM and UOMConv. The orphaned PartUOM is still visible in the part’s UOM tab, but it’s no longer editable, and part-specific appears unchecked despite it still being true in PartUOM. Most importantly, the UOM is no longer selectable for quantities of the part. So again, database clutter but no real harm aside from a slightly confusing presentation in the UOM tab.
Unchecking Part-Specific
If a PartUOM row exists and you uncheck part-specific on the UOMConv, the PartUOM row is not removed. PartUOM.Active remains true. But as with a deleted UOMConv, the UOM tab shows the conversion as non-part-specific. If the PartUOM factor differed from the UOMConv factor, the UI reverts to the UOMConv factor. Again, database clutter but no real harm.
The database clutter only becomes an issue if you’re writing BAQs or otherwise accessing the PartUOM table. You cannot tell from a PartUOM row whether a part actually has an effective part-specific conversion. You can only determine if a PartUOM row is “real” by joining on UOMConv.
Well, I hit another wall. It appears that my predecessor created standard UOMConvs like gallons-to-liters as part-specific, created parts, and then unchecked part-specific on the UOMConv. The effective conversion factor displayed in the UOM tab on Part did not revert to the factor from the UOMConv. It remains incorrect and cannot be edited or deleted. This is possibly because the PartUOM also has Track checked, which cannot be unchecked. In the UI, the conversion is shown as not part-specific, but track is shown as checked, as if the view model is partly ignoring the orphaned PartUOM and partly not.
