Blanket Agreement Open Quantity

[kedalenechong]

Hi all

Please share if you have any modified Query or report done to show Blank Agreement Open Quantity together with Inventory Item Master Data Sales Order Committed Quantity?

Kedalene

[former_member212181 · Accepted solution]

Hi Kedalene,

Please try below query

SELECT T0.[AbsID], T0.[BpCode], T2.[CardName], T0.[StartDate], T0.[EndDate], T0.[Status], T1.[ItemCode], T3.[ItemName], T1.[PlanQty], T1.[CumQty]

  ,(Select Sum(A.OpenQty) from RDR1 A Where A.AgrNo = T0.[AbsID] and A.ItemCode = T1.ItemCode) [SO_QTY]

  ,T1.[CumQty]+(Select Sum(A.OpenQty) from RDR1 A Where A.AgrNo = T0.[AbsID] and A.ItemCode = T1.ItemCode)[CumQty_Includ_SO]

FROM OOAT T0

INNER JOIN OAT1 T1 ON T0.[AbsID] = T1.[AgrNo]

INNER JOIN OCRD T2 ON T0.[BpCode] = T2.[CardCode]

INNER JOIN OITM T3 ON T1.[ItemCode] = T3.[ItemCode]

Thanks

Unnikrishnan

[former_member212181 · Accepted solution]

Hi Kedalene,

Added few where conditions here.

SELECT T0.AbsID, T0.BpCode, T2.CardName, T0.StartDate, T0.EndDate, T0.Status, T1.ItemCode, T3.ItemName, T1.PlanQty, T1.CumQty

  ,(Select Sum(A.OpenQty) from RDR1 A Where A.AgrNo = T0.AbsID and A.ItemCode = T1.ItemCode)SO_QTY

  ,T1.CumQty+(Select Sum(A.OpenQty) from RDR1 A Where A.AgrNo = T0.AbsID and A.ItemCode = T1.ItemCode)CumQty_Includ_SO

FROM dbo.OOAT T0

INNER JOIN OAT1 T1 ON T0.AbsID = T1.AgrNo

INNER JOIN OCRD T2 ON T0.BpCode = T2.CardCode

INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode

Where T0.Status = '[%0]'

  and T0.StartDate >= '[%1]'

  and T0.EndDate <= '[%2]'

Thanks

Unnikrishnan