Creating a Go Negative Dashboard

[MelissaC (Melissa Couture)]

I am trying to create a dashboard for purchasing that has the date a purchased part goes negative and then the next open PO due date after the go negative date. I can get the go negative date. I’m having trouble getting the next open release date after the go negative date. I can get the next open PO but can’t compare it to the go negative date. Researching CTEs were mentioned but I’ve never used one and don’t quite understand.

	[Part].[PartNum] as [Part_PartNum],
	[Part].[PartDescription] as [Part_PartDescription],
	[Part].[ClassID] as [Part_ClassID],
	[PartPlant].[LeadTime] as [PartPlant_LeadTime],
	[PartPlant].[BuyerID] as [PartPlant_BuyerID],
	[Vendor].[VendorID] as [Vendor_VendorID],
	[Vendor].[Name] as [Vendor_Name],
	[TotalOnHand].[Calculated_OnHandQty] as [Calculated_OnHandQty],
	(min(PartDemandInfo.Calculated_ZeroDate)) as [Calculated_RunOutDate],
	[Part].[BuyerPlannerNotes_c] as [Part_BuyerPlannerNotes_c],
	[OpenPOs].[Calculated_NextPODueDate] as [Calculated_NextPODueDate]
from Erp.Part as Part
inner join Erp.PartPlant as PartPlant on 
	Part.Company = PartPlant.Company
	and Part.PartNum = PartPlant.PartNum
left outer join Erp.Vendor as Vendor on 
	PartPlant.Company = Vendor.Company
	and PartPlant.VendorNum = Vendor.VendorNum
	and ( Vendor.Inactive = false  )

left outer join  (select 
	[Part1].[Company] as [Part1_Company],
	[Part1].[PartNum] as [Part1_PartNum],
	(sum(PartQty.OnHandQty)) as [Calculated_OnHandQty]
from Erp.Part as Part1
inner join Erp.PartQty as PartQty on 
	Part1.Company = PartQty.Company
	and Part1.PartNum = PartQty.PartNum
where (Part1.TypeCode = 'p'  and Part1.InActive = false)
group by [Part1].[Company],
	[Part1].[PartNum])  as TotalOnHand on 
	Part.Company = TotalOnHand.Part1_Company
	and Part.PartNum = TotalOnHand.Part1_PartNum
left outer join  (select 
	[PartDtl].[PartNum] as [PartDtl_PartNum],
	[PartDtl].[Type] as [PartDtl_Type],
	[PartDtl].[RequirementFlag] as [PartDtl_RequirementFlag],
	[PartDtl].[Quantity] as [PartDtl_Quantity],
	[PartDtl].[DueDate] as [PartDtl_DueDate],
	[PartDtl].[SourceFile] as [PartDtl_SourceFile],
	[TotalOnHand1].[Calculated_OnHandQty] as [Calculated_OnHandQty01],
	(CASE 
     WHEN COALESCE(PartDtl.SourceFile, '') = 'JH' THEN COALESCE(PartDtl.Quantity, 0)
     WHEN COALESCE(PartDtl.SourceFile, '') IN ('JM', 'OR') THEN -1 * COALESCE(PartDtl.Quantity, 0)
     ELSE COALESCE(PartDtl.Quantity, 0)
 END) as [Calculated_DemandQty],
	(SUM(COALESCE(DemandQty, 0)) 
 OVER (PARTITION BY PartDtl.PartNum ORDER BY PartDtl.DueDate 
       ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
 + COALESCE(TotalOnHand1.Calculated_OnHandQty, 0)) as [Calculated_RunningQty],
	(CASE WHEN RunningQty < 0 THEN PartDtl.DueDate ELSE NULL END) as [Calculated_ZeroDate],
	[PartDtl].[Company] as [PartDtl_Company]
from Erp.PartDtl as PartDtl
inner join  (select 
	[Part1].[Company] as [Part1_Company],
	[Part1].[PartNum] as [Part1_PartNum],
	(sum(PartQty.OnHandQty)) as [Calculated_OnHandQty]
from Erp.Part as Part1
inner join Erp.PartQty as PartQty on 
	Part1.Company = PartQty.Company
	and Part1.PartNum = PartQty.PartNum
where (Part1.TypeCode = 'p'  and Part1.InActive = false)
group by [Part1].[Company],
	[Part1].[PartNum])  as TotalOnHand1 on 
	PartDtl.Company = TotalOnHand1.Part1_Company
	and PartDtl.PartNum = TotalOnHand1.Part1_PartNum
inner join Erp.Part as Part2 on 
	PartDtl.Company = Part2.Company
	and PartDtl.PartNum = Part2.PartNum
	and ( Part2.TypeCode = 'p'  and Part2.InActive = false  )

group by [PartDtl].[PartNum],
	[PartDtl].[Type],
	[PartDtl].[RequirementFlag],
	[PartDtl].[Quantity],
	[PartDtl].[DueDate],
	[PartDtl].[SourceFile],
	[TotalOnHand1].[Calculated_OnHandQty],
	[PartDtl].[Company])  as PartDemandInfo on 
	Part.Company = PartDemandInfo.PartDtl_Company
	and Part.PartNum = PartDemandInfo.PartDtl_PartNum
left outer join  (select 
	[PODetail].[Company] as [PODetail_Company],
	[PODetail].[PartNum] as [PODetail_PartNum],
	(min(PORel.DueDate)) as [Calculated_NextPODueDate]
from Erp.PORel as PORel
inner join Erp.PODetail as PODetail on 
	PORel.Company = PODetail.Company
	and PORel.PONum = PODetail.PONUM
	and PORel.POLine = PODetail.POLine
where (PORel.OpenRelease = true)
group by [PODetail].[Company],
	[PODetail].[PartNum])  as OpenPOs on 
	Part.PartNum = OpenPOs.PODetail_PartNum
where (Part.InActive = false  and Part.TypeCode = 'p'  and Part.NonStock = false)
 and (OpenPOs.Calculated_NextPODueDate > ANY (select Calculated_ZeroDate from ((select 
	[PartDtl].[PartNum] as [PartDtl_PartNum],
	[PartDtl].[Type] as [PartDtl_Type],
	[PartDtl].[RequirementFlag] as [PartDtl_RequirementFlag],
	[PartDtl].[Quantity] as [PartDtl_Quantity],
	[PartDtl].[DueDate] as [PartDtl_DueDate],
	[PartDtl].[SourceFile] as [PartDtl_SourceFile],
	[TotalOnHand1].[Calculated_OnHandQty] as [Calculated_OnHandQty01],
	(CASE 
     WHEN COALESCE(PartDtl.SourceFile, '') = 'JH' THEN COALESCE(PartDtl.Quantity, 0)
     WHEN COALESCE(PartDtl.SourceFile, '') IN ('JM', 'OR') THEN -1 * COALESCE(PartDtl.Quantity, 0)
     ELSE COALESCE(PartDtl.Quantity, 0)
 END) as [Calculated_DemandQty],
	(SUM(COALESCE(DemandQty, 0)) 
 OVER (PARTITION BY PartDtl.PartNum ORDER BY PartDtl.DueDate 
       ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
 + COALESCE(TotalOnHand1.Calculated_OnHandQty, 0)) as [Calculated_RunningQty],
	(CASE WHEN RunningQty < 0 THEN PartDtl.DueDate ELSE NULL END) as [Calculated_ZeroDate],
	[PartDtl].[Company] as [PartDtl_Company]
from Erp.PartDtl as PartDtl
inner join  (select 
	[Part1].[Company] as [Part1_Company],
	[Part1].[PartNum] as [Part1_PartNum],
	(sum(PartQty.OnHandQty)) as [Calculated_OnHandQty]
from Erp.Part as Part1
inner join Erp.PartQty as PartQty on 
	Part1.Company = PartQty.Company
	and Part1.PartNum = PartQty.PartNum
where (Part1.TypeCode = 'p'  and Part1.InActive = false)
group by [Part1].[Company],
	[Part1].[PartNum])  as TotalOnHand1 on 
	PartDtl.Company = TotalOnHand1.Part1_Company
	and PartDtl.PartNum = TotalOnHand1.Part1_PartNum
inner join Erp.Part as Part2 on 
	PartDtl.Company = Part2.Company
	and PartDtl.PartNum = Part2.PartNum
	and ( Part2.TypeCode = 'p'  and Part2.InActive = false  )

group by [PartDtl].[PartNum],
	[PartDtl].[Type],
	[PartDtl].[RequirementFlag],
	[PartDtl].[Quantity],
	[PartDtl].[DueDate],
	[PartDtl].[SourceFile],
	[TotalOnHand1].[Calculated_OnHandQty],
	[PartDtl].[Company])) as PartDemandInfo))

group by [Part].[PartNum],
	[Part].[PartDescription],
	[Part].[ClassID],
	[PartPlant].[LeadTime],
	[PartPlant].[BuyerID],
	[Vendor].[VendorID],
	[Vendor].[Name],
	[TotalOnHand].[Calculated_OnHandQty],
	[Part].[BuyerPlannerNotes_c],
	[OpenPOs].[Calculated_NextPODueDate]

[Chad_Smith]

@MelissaC

I’m having trouble getting the next open release date after the go negative date

You might have to make another subquery. If you have the calculated Negative Date, you can link to a summarized PORel or PartDtl again where you get the Min on all open releases due on or after your Negative Date. I would think that should work assuming that you only have a single release due on a given day. Not sure what I would do if there are likely to be multiple hits there. Once you have the subquery that returns your single record, you can link that back up to your main query to get the details on that PO if needed.

Edit: There could be issues with late releases that are before your negative date that could be problematic for what you are trying to do here.

On a side note, this is exactly what Time Phased Inquiry does. You must be trying to make a mass review version of that?

[MelissaC (Melissa Couture)]

Yeah. They want to see it all on one screen. Your solution worked. My brain had turned to mush after working on it so long

[Hally (Simon Hall)]

I am curious as why you let inventory go negative…Care to elaborate?

[MelissaC (Melissa Couture)]

It’s not negative on hand qty. They want to see when the current stock will run out based on future demand and already placed POs. Basically what the time phase shows summed up for all purchased parts on one dashboard with a row for each part showing the current stock, the date stock will go negative and the next PO due date after that. I’m assuming so they can pull in PO dates or create new ones.

[gpayne (Greg Payne)]

@MelissaC These dashboard do this from PartDtl if you want another approach.

Multi level job material status

Thankyou, il give that a go converting to BAQ.