Skip to content

Instantly share code, notes, and snippets.

@chadsten

chadsten/withvendpart

Created September 12, 2017 16:21
Show Gist options
  • Save chadsten/5015ad6f64e6a2b5f69e2335b8fab3ed to your computer and use it in GitHub Desktop.
Save chadsten/5015ad6f64e6a2b5f69e2335b8fab3ed to your computer and use it in GitHub Desktop.
Download ZIP
Raw
withvendpart
--- with vendpart
SELECT SH.PartNum, SH.MonthAvg, SH.Hits, SH.Customers, PP.LeadTime, SH.Plant, ISNULL(PW.OnHandQty, 0) + ISNULL(PO.OrderQty, 0) - ISNULL(PW.DemandQty, 0)
AS AvailableQty, SH.MonthAvg / 28 AS DailyDemand, VP.BaseUnitPrice
FROM Epicor10.Erp.PartPlant AS PP LEFT OUTER JOIN
Epicor10.Erp.VendPart AS VP ON PP.PartNum = VP.PartNum AND PP.VendorNum = VP.VendorNum LEFT OUTER JOIN
Epicor10.Erp.PartWhse AS PW ON PP.PrimWhse = PW.WarehouseCode AND PP.PartNum = PW.PartNum LEFT OUTER JOIN
dbo.pa_2_1_parts_on_po AS PO ON PP.PartNum = PO.PartNum AND PP.Plant = PO.Plant RIGHT OUTER JOIN
dbo.pa_2_0_year_sales_history AS SH ON PP.Plant = SH.Plant AND PP.PartNum = SH.PartNum
WHERE (PP.BuyToOrder <> 'true') AND (VP.ExpirationDate >= GETDATE())
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment