Looking for a quick repeatable method remove an identified Item from All BOMS it is associated with. We have a lot of parts where the UOM and QTY are incorrect, but to correct them they need to be removed from all BOM's, and some items may exist in hundreds of BOMs, so opening each one is not practical.
Solved! Go to Solution.
Qty looks to be updated in a similar vein. Just to sanity check what I intend on doing.
I am selecting puid from PITEM, where pitem_id ="My Item Number"
from here I update ruom_tagu and ruom_tagc with data (puid) from PUNITOFMEASURE
Also updating both ruom_tagu and ruom_tagc against all rows of PPSOCCURRENCE where the rchild_itemu is the puid number from initial PITEM lookup. The Qty looks to be also in the PPSOCCURRENCE table.
I'm hoping this makes sense.
Excellent. You only need update ruom_tagu:
! Get old UOM for validation SELECT RUOM_TAGU FROM dbo.PITEM WHERE PITEM_ID = 'C-12345'; QPHBsnnp1kBOfB ! Query PUNITOFMEASURE. The old UOM (QPHBsnnp1kBOfB) is EA. The new UOM (w8$4M7$O1kBOfB) is FT. SELECT * FROM PUNITOFMEASURE; w8$4M7v_1kBOfB SF Square Feet w8$4M7$O1kBOfB FT Feet w8$4M7$a1kBOfB SH Sheet w8$4M7$m1kBOfB LY Linear Yards gv288Fyi1kBOfB PS Process Spec ANCBcZub1kBOfB ML Milliliter QPHBsnnp1kBOfB EA Each gqDFq2e51kBOfB QT Quart gqGFq2e51kBOfB PT Pint gqJFq2e51kBOfB GA Gallon ! Change the UOM. UPDATE dbo.PITEM SET RUOM_TAGU = 'w8$4M7$O1kBOfB' WHERE PITEM_ID = 'C-12345'; ! To set RUOM_TAGU to NULL use 'AAAAAAAAAAAAAA'; UPDATE dbo.PITEM SET RUOM_TAGU = 'AAAAAAAAAAAAAA' WHERE PITEM_ID = 'C-12345'; COMMIT;
All the usual warnings apply. This sql is from a much older implementation so use your own due dilligence.
In order to correct UOM, you need not to remove it from ALL BOMs.
UI does not allow you to modify UOM if item already exists in some BOM or released.
However, you can consider using POM calls to update UOM on item and all its existing PSOccurrences.
There is a sample code provided by GTAC, you can refer it to get some idea
After playing with our Dev servers, this seems to be the best outcome
SET ruom_tagu = 'UNIT OF MEASURE PUID', ruom_tagc='CHECK YOUR DB'
WHERE pitem_id = 'YOUR ITEM ID'
SET ruom_tagu = 'UNIT OF MEASURE PUID', ruom_tagc = 'CHECK YOUR DB'
WHERE PPSOCCURRENCE.rchild_itemu = (select puid from PITEM where pitem_id='YOUR ITEM ID')
You can also update the dbo.PPSOCCURRENCE.pqty_value, but you need to ensure you are using BOM Sync, and ensure you have the right qty set against each BOM it belongs to in PPSOCCURRENCE, but that is for another day.