Product Structure: Identify U/M Mismatches

About

This query batches component products unit of measure compared with the u/m in the product structure table.

To be compatable these should need either...

• The same unit of measure type (e.g. volume to volume, weight to weight)
• Have an ARTPRODUMS record to relate the products (e.g. Product: Fiber YD, Mix Fiber LB - Prod UMS 1.2 LB to 1.0 YD)
• For Volume to Weight have Specific Gravity setup. (e.g. Product: Water GL, Mix Water LB - Specific Gravity 1.0)

Query

select 1 ct, x.*,p.product_code,p.unit_of_measure produm,pu.ums_type prdUMtype,cu.ums_type cmpUMtype,p.specific_gravity,u.conv_ratio 
,CASE when (Specific_Gravity=0) then 'N' else 'Y' end "SpcGrvYN"
,case when (u.product_code is null) then 'N' ELSE 'Y' end "PrdCnvUMYN"
from artprstr x
inner join artprod p on p.product_code = x.comp_product_code
inner join artums pu on pu.unit_of_measure=p.unit_of_measure
inner join artums cu on cu.unit_of_measure=x.comp_ums
left outer join artprodums u on u.product_code=p.product_code and
  ((u.conv_source_ums=x.comp_ums and u.conv_target_ums=p.unit_of_measure) or 
  (u.conv_target_ums=x.comp_ums and u.conv_source_ums=p.unit_of_measure))

Setup Datascope

DATA:

• CT

Top Axis

• CmpUMtype
• Comp_ums

Left Axis

• PrdUMtype
• prdCnvUMYN
• SpcGrvYN
• ProdUM