gistfile1.txt

SELECT DISTINCT 
                      dbo.r_CalculatedMinMax.PartNum, Epicor10.dbo.Part.PartDescription, Epicor10.Erp.Vendor.VendorID, dbo.r_CalculatedMinMax.Plant, 
                      dbo.r_CalculatedMinMax.SourceType, dbo.r_CalculatedMinMax.PUM, dbo.r_CalculatedMinMax.IUM, CASE WHEN (dbo.r_CalculatedMinMax.ConvFactor > 1) 
                      THEN dbo.r_CalculatedMinMax.ConvFactor ELSE '1' END AS ConvFactor, dbo.r_CalculatedMinMax.SugMin, dbo.r_CalculatedMinMax.SugMax, 
                      dbo.r_CalculatedMinMax.MinimumQty AS CurrentMin, dbo.r_CalculatedMinMax.MaximumQty AS CurrentMax, Epicor10.Erp.PartClass.Description AS ClassDescription, 
                      CASE WHEN (VendPart.BaseUnitPrice > 0) AND (dbo.r_CalculatedMinMax.SugMin IS NOT NULL) AND (Epicor10.dbo.Part.IUM <> Epicor10.dbo.Part.PUM) 
                      THEN (((dbo.r_CalculatedMinMax.SugMin - (PartWhse.OnHandQty - PartWhse.DemandQty + dbo.r_CalculatedMinMax.OrderQty)) / dbo.r_CalculatedMinMax.ConvFactor)
                       * VendPart.BaseUnitPrice) WHEN (VendPart.BaseUnitPrice > 0) AND (dbo.r_CalculatedMinMax.SugMin IS NOT NULL) AND 
                      (Epicor10.dbo.Part.IUM = Epicor10.dbo.Part.PUM) 
                      THEN (((dbo.r_CalculatedMinMax.SugMin - (PartWhse.OnHandQty - PartWhse.DemandQty + dbo.r_CalculatedMinMax.OrderQty))) * VendPart.BaseUnitPrice) 
                      WHEN (VendPart.BaseUnitPrice > 0) AND (dbo.r_CalculatedMinMax.SugMin IS NULL) AND (Epicor10.dbo.Part.IUM <> Epicor10.dbo.Part.PUM) 
                      THEN (((dbo.r_CalculatedMinMax.MinimumQty - (PartWhse.OnHandQty - PartWhse.DemandQty + dbo.r_CalculatedMinMax.OrderQty)) 
                      / dbo.r_CalculatedMinMax.ConvFactor) * VendPart.BaseUnitPrice) WHEN (VendPart.BaseUnitPrice > 0) AND (dbo.r_CalculatedMinMax.SugMin IS NULL) AND 
                      (Epicor10.dbo.Part.IUM = Epicor10.dbo.Part.PUM) 
                      THEN (((dbo.r_CalculatedMinMax.MinimumQty - (PartWhse.OnHandQty - PartWhse.DemandQty + dbo.r_CalculatedMinMax.OrderQty))) * VendPart.BaseUnitPrice) 
                      WHEN (VendPart.BaseUnitPrice IS NULL) AND (dbo.r_CalculatedMinMax.SugMin IS NOT NULL) AND (Epicor10.dbo.Part.IUM <> Epicor10.dbo.Part.PUM) 
                      THEN (((dbo.r_CalculatedMinMax.SugMin - (PartWhse.OnHandQty - PartWhse.DemandQty + dbo.r_CalculatedMinMax.OrderQty)) / dbo.r_CalculatedMinMax.ConvFactor)
                       * PartCost.AvgMaterialCost) WHEN (VendPart.BaseUnitPrice IS NULL) AND (dbo.r_CalculatedMinMax.SugMin IS NOT NULL) AND 
                      (Epicor10.dbo.Part.IUM = Epicor10.dbo.Part.PUM) 
                      THEN (((dbo.r_CalculatedMinMax.SugMin - (PartWhse.OnHandQty - PartWhse.DemandQty + dbo.r_CalculatedMinMax.OrderQty))) * PartCost.AvgMaterialCost) 
                      WHEN (VendPart.BaseUnitPrice IS NULL) AND (dbo.r_CalculatedMinMax.SugMin IS NULL) AND (Epicor10.dbo.Part.IUM <> Epicor10.dbo.Part.PUM) 
                      THEN (((dbo.r_CalculatedMinMax.MinimumQty - (PartWhse.OnHandQty - PartWhse.DemandQty + dbo.r_CalculatedMinMax.OrderQty)) 
                      / dbo.r_CalculatedMinMax.ConvFactor) * PartCost.AvgMaterialCost) WHEN (VendPart.BaseUnitPrice IS NULL) AND (dbo.r_CalculatedMinMax.SugMin IS NULL) AND 
                      (Epicor10.dbo.Part.IUM = Epicor10.dbo.Part.PUM) 
                      THEN (((dbo.r_CalculatedMinMax.MinimumQty - (PartWhse.OnHandQty - PartWhse.DemandQty + dbo.r_CalculatedMinMax.OrderQty))) * PartCost.AvgMaterialCost) 
                      ELSE '0' END AS MinSpend, CASE WHEN (VendPart.BaseUnitPrice > 0) AND (dbo.r_CalculatedMinMax.SugMax IS NOT NULL) AND 
                      (Epicor10.dbo.Part.IUM <> Epicor10.dbo.Part.PUM) 
                      THEN (((dbo.r_CalculatedMinMax.SugMax - (PartWhse.OnHandQty - PartWhse.DemandQty + dbo.r_CalculatedMinMax.OrderQty)) 
                      / dbo.r_CalculatedMinMax.ConvFactor) * VendPart.BaseUnitPrice) WHEN (VendPart.BaseUnitPrice > 0) AND (dbo.r_CalculatedMinMax.SugMax IS NOT NULL) AND 
                      (Epicor10.dbo.Part.IUM = Epicor10.dbo.Part.PUM) 
                      THEN (((dbo.r_CalculatedMinMax.SugMax - (PartWhse.OnHandQty - PartWhse.DemandQty + dbo.r_CalculatedMinMax.OrderQty))) * VendPart.BaseUnitPrice) 
                      WHEN (VendPart.BaseUnitPrice > 0) AND (dbo.r_CalculatedMinMax.SugMax IS NULL) AND (Epicor10.dbo.Part.IUM <> Epicor10.dbo.Part.PUM) 
                      THEN (((dbo.r_CalculatedMinMax.MaximumQty - (PartWhse.OnHandQty - PartWhse.DemandQty + dbo.r_CalculatedMinMax.OrderQty)) 
                      / dbo.r_CalculatedMinMax.ConvFactor) * VendPart.BaseUnitPrice) WHEN (VendPart.BaseUnitPrice > 0) AND (dbo.r_CalculatedMinMax.SugMax IS NULL) AND 
                      (Epicor10.dbo.Part.IUM = Epicor10.dbo.Part.PUM) 
                      THEN (((dbo.r_CalculatedMinMax.MaximumQty - (PartWhse.OnHandQty - PartWhse.DemandQty + dbo.r_CalculatedMinMax.OrderQty))) * VendPart.BaseUnitPrice) 
                      WHEN (VendPart.BaseUnitPrice IS NULL) AND (dbo.r_CalculatedMinMax.SugMax IS NOT NULL) AND (Epicor10.dbo.Part.IUM <> Epicor10.dbo.Part.PUM) 
                      THEN (((dbo.r_CalculatedMinMax.SugMax - (PartWhse.OnHandQty - PartWhse.DemandQty + dbo.r_CalculatedMinMax.OrderQty)) 
                      / dbo.r_CalculatedMinMax.ConvFactor) * PartCost.AvgMaterialCost) WHEN (VendPart.BaseUnitPrice IS NULL) AND (dbo.r_CalculatedMinMax.SugMax IS NOT NULL) 
                      AND (Epicor10.dbo.Part.IUM = Epicor10.dbo.Part.PUM) 
                      THEN (((dbo.r_CalculatedMinMax.SugMax - (PartWhse.OnHandQty - PartWhse.DemandQty + dbo.r_CalculatedMinMax.OrderQty))) * PartCost.AvgMaterialCost) 
                      WHEN (VendPart.BaseUnitPrice IS NULL) AND (dbo.r_CalculatedMinMax.SugMax IS NULL) AND (Epicor10.dbo.Part.IUM <> Epicor10.dbo.Part.PUM) 
                      THEN (((dbo.r_CalculatedMinMax.MaximumQty - (PartWhse.OnHandQty - PartWhse.DemandQty + dbo.r_CalculatedMinMax.OrderQty)) 
                      / dbo.r_CalculatedMinMax.ConvFactor) * PartCost.AvgMaterialCost) WHEN (VendPart.BaseUnitPrice IS NULL) AND (dbo.r_CalculatedMinMax.SugMax IS NULL) AND 
                      (Epicor10.dbo.Part.IUM = Epicor10.dbo.Part.PUM) 
                      THEN (((dbo.r_CalculatedMinMax.MaximumQty - (PartWhse.OnHandQty - PartWhse.DemandQty + dbo.r_CalculatedMinMax.OrderQty))) * PartCost.AvgMaterialCost) 
                      ELSE '0' END AS MaxSpend, Epicor10.Erp.VendPart.BaseUnitPrice, dbo.r_CalculatedMinMax.MonthAvg, 
                      Epicor10.Erp.PartWhse.OnHandQty - Epicor10.Erp.PartWhse.DemandQty + dbo.r_CalculatedMinMax.OrderQty AS AvailableWithOrdered, 
                      Epicor10.Erp.PartWhse.OnHandQty - Epicor10.Erp.PartWhse.DemandQty AS AvailableQty, 
                      CASE WHEN dbo.r_CalculatedMinMax.Hits >= 9 THEN 'A' WHEN dbo.r_CalculatedMinMax.Hits >= 6 THEN 'B' WHEN dbo.r_CalculatedMinMax.Hits >= 3 THEN 'C' WHEN
                       db_ddladmin.v_PartNumsWithContracts.NumberOfListCodes >= 1 THEN 'D' WHEN dbo.r_CalculatedMinMax.Hits <= 2 THEN 'R' WHEN Epicor10.dbo.Part.NewProduct_c
                       = 'true' THEN 'N' WHEN dbo.r_CalculatedMinMax.Hits IS NULL THEN 'R' ELSE 'ERROR' END AS ItemMoveType, dbo.r_CalculatedMinMax.Hits, 
                      dbo.r_CalculatedMinMax.Customers, db_ddladmin.v_PartNumsWithContracts.NumberOfListCodes, Epicor10.dbo.Part.NewProduct_c, 
                      db_ddladmin.v_PartNumsWithContracts.ListCode, CASE WHEN (VendPart.BaseUnitPrice > 0) AND (Epicor10.dbo.Part.IUM = Epicor10.dbo.Part.PUM) 
                      THEN ((((PartWhse.OnHandQty - PartWhse.DemandQty + dbo.r_CalculatedMinMax.OrderQty))) * VendPart.BaseUnitPrice) WHEN (VendPart.BaseUnitPrice > 0) AND 
                      (Epicor10.dbo.Part.IUM <> Epicor10.dbo.Part.PUM) THEN ((((PartWhse.OnHandQty - PartWhse.DemandQty + dbo.r_CalculatedMinMax.OrderQty)) 
                      / dbo.r_CalculatedMinMax.ConvFactor) * VendPart.BaseUnitPrice) WHEN (VendPart.BaseUnitPrice IS NULL) AND (Epicor10.dbo.Part.IUM <> Epicor10.dbo.Part.PUM) 
                      THEN ((((PartWhse.OnHandQty - PartWhse.DemandQty + dbo.r_CalculatedMinMax.OrderQty)) / dbo.r_CalculatedMinMax.ConvFactor) * PartCost.AvgMaterialCost) 
                      WHEN (VendPart.BaseUnitPrice IS NULL) AND (Epicor10.dbo.Part.IUM = Epicor10.dbo.Part.PUM) 
                      THEN ((((PartWhse.OnHandQty - PartWhse.DemandQty + dbo.r_CalculatedMinMax.OrderQty))) * PartCost.AvgMaterialCost) ELSE '0' END AS ValueOnHand, 
                      dbo.r_LastRecDate.LastRecDate, dbo.r_LastRecDate.LastPONum, dbo.r_LastRecDate.LastPONum AS Expr1
FROM         db_ddladmin.v_PartNumsWithContracts RIGHT OUTER JOIN
                      dbo.r_LastRecDate RIGHT OUTER JOIN
                      Epicor10.Erp.PartWhse RIGHT OUTER JOIN
                      dbo.r_CalculatedMinMax ON Epicor10.Erp.PartWhse.WarehouseCode = dbo.r_CalculatedMinMax.Whse AND 
                      Epicor10.Erp.PartWhse.PartNum = dbo.r_CalculatedMinMax.PartNum ON dbo.r_LastRecDate.Plant = dbo.r_CalculatedMinMax.Plant AND 
                      dbo.r_LastRecDate.PartNum = dbo.r_CalculatedMinMax.PartNum LEFT OUTER JOIN
                      Epicor10.Erp.PartCost ON dbo.r_CalculatedMinMax.PartNum = Epicor10.Erp.PartCost.PartNum AND 
                      dbo.r_CalculatedMinMax.Plant = Epicor10.Erp.PartCost.CostID LEFT OUTER JOIN
                      Epicor10.Erp.Vendor ON dbo.r_CalculatedMinMax.VendorNum = Epicor10.Erp.Vendor.VendorNum LEFT OUTER JOIN
                      Epicor10.Erp.VendPart ON dbo.r_CalculatedMinMax.VendorNum = Epicor10.Erp.VendPart.VendorNum AND 
                      dbo.r_CalculatedMinMax.PartNum = Epicor10.Erp.VendPart.PartNum LEFT OUTER JOIN
                      Epicor10.Erp.PartClass RIGHT OUTER JOIN
                      Epicor10.dbo.Part ON Epicor10.Erp.PartClass.ClassID = Epicor10.dbo.Part.ClassID ON dbo.r_CalculatedMinMax.PartNum = Epicor10.dbo.Part.PartNum ON 
                      db_ddladmin.v_PartNumsWithContracts.PartNum = dbo.r_CalculatedMinMax.PartNum