cancel
Showing results for 
Search instead for 
Did you mean: 
Read only

Serial & Batch Rejection qty report (GRPO to rejection warehouse)

hemabhushan_g
Explorer
0 Kudos
204

Dear Experts,

I have a report for serial / batch created with GRPO and IT to particular warehouse and available stock.

Here i shared my query, please help us to get the exact details.

GRPO date : 12/02/22

IT date : 15/02/22

IT to rejection warehouse date : 18/02/22

Here i shared my query, please help us to get the exact details.

SELECT T1.ItemCode [SAP Part Code],T1.Dscription [SAP Description],t1.Quantity [Quantity],t5.InvntryUom [UOM]

,convert(nvarchar,T0.DocDate,103) [Rejected date],F.BatchNum [Rejection Batch / Serial no]

,t0.Comments [Reason for Rejection],t1.FromWhsCod [Rejection from WHS Details],t1.WhsCode [Rejection to WHS Details]

,(select y.CardName from OPDN y WHERE y.DocEntry = E.BaseEntry) [Supplier Name]

,(select X.NumAtCard from OPDN X WHERE X.DocEntry = E.BaseEntry) [ Supplier Invoice Number]

,(select convert(nvarchar,z.TaxDate,103) from OPDN z WHERE z.DocEntry = E.BaseEntry) [Supplier Invoice Date]

,(select G.DocNum from OPDN G WHERE G.DocEntry = E.BaseEntry) [Ravel GRN number]

,(select convert(nvarchar,H.DocDate,103) from OPDN H WHERE H.DocEntry = E.BaseEntry) [Ravel GRN Date]

,(select D.Quantity from PDN1 D WHERE D.DocEntry = E.BaseEntry and D.ItemCode = E.ItemCode) [Ravel GRN Qty]

,datediff(day,T0.DocDate, GetDate()) [Due Days]

FROM OWTR T0 With (Nolock)

INNER JOIN WTR1 T1 With (Nolock) ON T0.DocEntry = T1.DocEntry

LEFT JOIN (select xx.ItemCode,xx.BatchNum,yy.BaseEntry,yy.SysNumber,yy.baseLinNum,xx.BaseType,xx.WhsCode

FROM IBT1 xx With (Nolock)

LEFT JOIN OIBT yy With (Nolock) ON xx.ItemCode=yy.ItemCode AND xx.BatchNum = yy.BatchNum AND yy.BaseType='67' and yy.InDate <= @ToDate)

F ON F.BaseEntry=T0.DocEntry AND F.BaseLinNum=T1.LineNum AND F.BaseType=T1.ObjType And F.WhsCode=T1.WhsCode and t1.ItemCode = F.ItemCode

left join (select BB.ItemCode,b.BatchNum,a.BaseEntry,b.SysNumber

from OPDN AA INNER JOIN PDN1 BB ON AA.DocEntry = BB.DocEntry

LEFT JOIN IBT1 A With (Nolock) ON A.BaseEntry=bb.DocEntry AND A.BaseLinNum=bb.LineNum AND A.BaseType=bb.ObjType And A.WhsCode=bb.WhsCode

LEFT JOIN OIBT B With (Nolock) ON B.ItemCode=A.ItemCode AND B.BatchNum = A.BatchNum AND a.BaseType in ('20','59') and B.InDate <= @ToDate

where aa.DocDate <= @ToDate and b.Quantity > 0 and b.Status=0 group by a.BaseEntry,bb.ItemCode,b.BatchNum,b.SysNumber)

E on (e.ItemCode = t1.ItemCode or e.ItemCode = t1.U_ItemCode) and e.BatchNum = F.BatchNum and e.SysNumber = f.SysNumber

LEFT JOIN OITM T5 With (Nolock) ON T5.ItemCode=T1.ItemCode

where T1.WhsCode = 'WH09' --AND F.BaseType='67'

and t0.DocDate >= @FromDate AND T0.DOcDate <= @TODATE

group BY T0.[DocNum],T0.DocDate,T1.ItemCode,T1.Dscription,t5.InvntryUom,t1.FromWhsCod

,F.BatchNum,T1.WhsCode,T0.Comments,f.BaseType,t5.ManBtchNum,t1.Quantity,e.BaseEntry,e.ItemCode

order by t1.ItemCode asc

Accepted Solutions (0)

Answers (0)