on 2022 Mar 05 10:19 AM
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
Request clarification before answering.
User | Count |
---|---|
17 | |
9 | |
7 | |
6 | |
6 | |
6 | |
5 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.