**goo
SELECT T0.[ItemCode],T1.[Itemname], T0.[Quantity],T2.LastPurPrc, T0.[WhsCode], T3.
[ItmsGrpNam], T1.[DistNumber]
FROM OBTQ T0
INNER JOIN OBTN T1 ON T0.AbsEntry = T1.AbsEntry
left JOIN OITM T2 ON T0.ItemCode = T2.ItemCode
left JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod
WHERE T0.[WhsCode] =[%0] ORDER BY T3.[ItmsGrpNam], T0.[ItemCode]
**goo
SELECT T0.[ItemCode] AS 'Item No.', T0.[itemName] AS 'Item Description', T1.
[OnHand] AS 'In Stock', T0.[DistNumber] AS 'Batch Number'
FROM [dbo].[OBTN] T0
INNER JOIN [dbo].[OITM] T1 ON T1.[ItemCode] = T0.[ItemCode]
MY**serial number wise stock report ?
SELECT T0.[ItemCode],T0.[itemName], T0.[DistNumber] ,T1.[OnHand] FROM [dbo].[OSRN]
T0 INNER JOIN [dbo].[OITM] T1 ON T0.[ItemCode] = T1.[ItemCode]
WHERE T1.[OnHand] >0 AND T0.[DistNumber] IS NOT NULL AND T0.[ItemCode] =[%0]
-----------------------------------------------------------------------------------
---------------
**INVENTORY BATCHNUM QUERY ?
SELECT T0.ItemCode,T0.[Product Group],T0.WhsCode,T0.BatchNum ,T0.InDate,
SUM(CASE WHEN T0.Direction=1 then -1 else 1 END*T0.Quantity) 'Quantity'
FROM (
SELECT MIN(b.LogEntry) as LogEntry,
MIN(b.ItemCode) as [ItemCode],
MIN(c.DistNumber) as [BatchNum],
MIN(ISNULL(C.InDate,C.CreateDate)) InDate,
MIN(b.LocCode) as [WhsCode],MIN(b.ItemName) as [ItemName],
MIN(IG.ItmsGrpNam) 'Product Group',
MIN(b.ApplyType) as [BaseType],
MIN(b.ApplyEntry) as [BaseEntry],
MIN(b.AppDocNum) as [BaseNum],
MIN(b.ApplyLine) as [BaseLinNum],
MIN(b.DocDate) as [DocDate],
(CASE WHEN ABS(SUM(a.Quantity)) = 0 THEN SUM(a.AllocQty) ELSE
ABS(SUM(a.Quantity)) END ) as [Quantity],
(CASE WHEN SUM(a.Quantity) > 0 THEN 0 WHEN SUM(a.Quantity) < 0 THEN 1 ELSE 2
END) as [Direction]
FROM ITL1 a WITH(NOLOCK)
INNER JOIN OITL b WITH(NOLOCK) ON a.LogEntry = b.LogEntry
INNER JOIN OBTN c WITH(NOLOCK) ON a.ItemCode = c.ItemCode and a.SysNumber =
c.SysNumber AND A.MdAbsEntry=C.AbsEntry
INNER JOIN OITM I WITH(NOLOCK) ON B.ItemCode=I.ItemCode
INNER JOIN OITB IG WITH(NOLOCK) ON I.ItmsGrpCod=IG.ItmsGrpCod
WHERE B.ITEMCODE='[%0]'
GROUP BY b.ItemCode, a.SysNumber, b.ApplyType, b.ApplyEntry, b.ApplyLine,
b.LocCode
)t0
GROUP BY T0.ItemCode,T0.WhsCode,T0.BatchNum,T0.InDate,T0.[Product Group]
-----------------------------------------------------------------------------------
---------------
**SERIAL Number wise stock report ?
Select DISTINCT 'SERIAL' "Type","ApplyType","ApplyEntry",ApplyLine,
X1."ItemCode"
,
STUFF((SELECT DISTINCT ',' + DistNumber
FROM ITL1 a
INNER JOIN OITL b ON a."LogEntry" = b."LogEntry"
INNER JOIN OSRN c ON a."ItemCode" = c."ItemCode" AND a."SysNumber" = c."SysNumber"
AND A."MdAbsEntry" = C."AbsEntry"
Where B.ApplyEntry=X.ApplyEntry and B.ApplyLine=X.ApplyLine and
B.ApplyType=X.ApplyType
FOR XML PATH (''))
, 1, 1, '') 'SERIAL'
,X1.LineNum,x.Quantity
From (
SELECT MIN(b."LogEntry") AS "LogEntry", MIN(b."ItemCode") AS "ItemCode",
MIN(c."DistNumber") AS "SerialNum", MIN(ISNULL(C."InDate", C."CreateDate")) AS
"InDate",
MIN(b."LocCode") AS "WhsCode", MIN(b."ItemName") AS "ItemName",
MIN(IG."ItmsGrpNam") AS "Product Group",
MIN(b."ApplyType") AS "ApplyType", MIN(b."ApplyEntry") AS "ApplyEntry",
MIN(b."AppDocNum") AS "BaseNum",
MIN(b."ApplyLine") AS "ApplyLine", MIN(b."DocDate") AS "DocDate",
ABS(SUM(a."Quantity")) AS "Quantity",
(CASE WHEN SUM(a."Quantity") > 0 THEN 0 WHEN SUM(a."Quantity") < 0 THEN 1 ELSE 2
END) AS "Direction"
,MIN(C."ExpDate") "ExpDate",I."InvntryUom"
,C.SysNumber,C.AbsEntry
FROM ITL1 a
INNER JOIN OITL b ON a."LogEntry" = b."LogEntry"
INNER JOIN OSRN c ON a."ItemCode" = c."ItemCode" AND a."SysNumber" =
c."SysNumber" AND A."MdAbsEntry" = C."AbsEntry"
INNER JOIN OITM I ON B."ItemCode" = I."ItemCode"
INNER JOIN OITB IG ON I."ItmsGrpCod" = IG."ItmsGrpCod"
Where ApplyType='15'
--and ApplyEntry=1
GROUP BY b."ItemCode",I."InvntryUom",a."SysNumber", b."ApplyType",
b."ApplyEntry",
b."ApplyLine", b."LocCode", b."StockEff",I."ManBtchNum" ,C.SysNumber,C.AbsEntry
HAVING (SUM(b."DocQty") <> 0) OR (SUM(b."DefinedQty") <> 0) OR (SUM(b."DocQty") =
0 AND b."StockEff" = 2
AND MIN(b."BaseType") <> 17 AND MIN(b."BaseType") <> 13)
) X
LEFT JOIN INV1 X1 ON X1.BaseEntry=X.ApplyEntry AND X1.BaseType=X.ApplyType AND
X1.BaseLine=X.ApplyLine
Where
( Case When "Direction"=0 Then X."Quantity" Else 0 End <>0 OR
Case When "Direction"=1 Then X."Quantity" Else 0 End <>0 ) and
ApplyType='15'
-----------------------------------------------------------------------------------
---------------
* Inventory Transfer ?
SELECT J."DocEntry",A."DocNum"
,W1."DocNum" "ReqNo"
,A."DocDate"
,d."WhsName",E."WhsName",U."Debit"-U."Credit"
,w."FromWhsCod",B."ItemCode",B."Dscription",b."UomEntry", B."unitMsr" "UomCode"
,C."BatchNum",C1."ExpDate",case when ISNULL(C."BatchNum",'')='' then
B."Quantity" else C."Quantity" end "Quantity1"
,B."Quantity"
,b."Price",(case when ISNULL(C."BatchNum",'')='' then B."Quantity" else
C."Quantity" end * I."Price") "LineTotal"
,A."Comments",a."ToWhsCode",a."Filler",B."StockPrice"
,ISNULL(D."WhsName",'') "FROM WHS"
,ISNULL(E."WhsName",'')"TO WHS"
,CASE WHEN ISNULL(W1."DocNum",0) = 0 THEN k1."DocNum" ELSE W1."DocNum" END AS
"Reqno"
,B."VendorNum"
,B."NumPerMsr",(VL1."SumStock"/VL1."InQty") "StockPrice"
FROM OWTR A
INNER JOIN WTR1 B ON A."DocEntry"=B."DocEntry"
LEFT JOIN WTQ1 W ON W."DocEntry"=B."BaseEntry" AND W."LineNum"=B."BaseLine"
LEFT JOIN OWTQ W1 ON W."DocEntry"=W1."DocEntry"
LEFT JOIN IBT1 C ON B."ItemCode"=C."ItemCode" AND B."DocEntry"=C."BaseEntry" AND
B."ObjType"=C."BaseType" AND C."Direction"=0 AND B."LineNum"=C."BaseLinNum"
left JOIN OBTN C1 ON C."ItemCode"=C1."ItemCode" and c."BatchNum"=c1."DistNumber"
LEFT JOIN OWHS D ON A."Filler"=D."WhsCode"
LEFT JOIN OWHS E ON A."ToWhsCode"=E."WhsCode"
LEFT JOIN ITM1 I ON B."ItemCode"=I."ItemCode" and i."PriceList"=11
LEFT JOIN WTR1 J ON B."BaseEntry" = J."DocEntry" AND B."BaseType" = J."ObjType" AND
B."BaseLine" =J."LineNum"
LEFT JOIN WTQ1 K ON J."BaseEntry" = K."DocEntry" AND J."BaseType" = K."ObjType" AND
J."BaseLine" = K."LineNum"
LEFT JOIN OWTQ K1 ON K1."DocEntry"=K."DocEntry"
LEFT JOIN OIVL VL1 ON VL1."TransType"=B."ObjType" AND VL1."CreatedBy"=B."DocEntry"
AND VL1."DocLineNum"=B."LineNum" AND VL1."SumStock">0
LEFT JOIN OJDT R on A."DocEntry"=R."CreatedBy"AND a."ObjType" = R."TransType"
LEFT JOIN JDT1 U ON R."TransId"=U."TransId"
--WHERE A."DocEntry"='[%0]'
-----------------------------------------------------------------------------------
---------------
** Sales Analysis Report ?
--/*A Select From OINV A */
--Declare FD Date;
--Declare TD Date;
--FD:=/* A."DocDate" */[%0];
--TD:=/* A."DocDate" */[%1];
--FD:='20230301';
--TD:='20230301';
SELECT
'A/R Invoice' "DocType",A."DocEntry",A."DocNum",C."SeriesName",A."DocDate",
Month(A."DocDate") As "Month",
A."NumAtCard",
A."CardCode",
A."CardName",A."Address" "Bill To",F."GroupName" "Customer Group",
A."Comments",
S."SlpName",
B."WhsCode",
B."OcrCode" "Dept",
D."InvntItem",
B."NoInvtryMv",
A."DocCur",B."TreeType",
B."ItemCode",
B."Dscription",
B."Quantity",
B."PriceBefDi" "Gross Unit Price",(((B."Quantity" * B."PriceBefDi") *
B."DiscPrcnt") / 100) AS "Discount Amount",B."Price" "Net Price",
B."StockPrice" "Cost",ISNULL(B."Quantity",0)*ISNULL(B."StockPrice",0) "Total Cost",
(case A."CurSource" when 'L' then B."LineTotal" when 'S' then B."TotalSumSy"
when 'C' then b."TotalFrgn" end) "LineTotal",
B."VatSum" "VatAmnt", ((case A."CurSource" when 'L' then B."LineTotal" when
'S' then B."TotalSumSy" when 'C' then b."TotalFrgn" end)+ISNULL(B."VatSum",0)) "Net
Price With VAT"
FROM OINV A
INNER JOIN INV1 B ON A."DocEntry" = B."DocEntry"
LEFT OUTER JOIN NNM1 C ON C."Series" = A."Series"
LEFT OUTER JOIN OITM D ON D."ItemCode" = B."ItemCode"
LEFT OUTER JOIN OITB G ON D."ItmsGrpCod"=G."ItmsGrpCod"
LEFT OUTER JOIN OSLP S ON A."SlpCode"=S."SlpCode"
lEFT OUTER JOIN OBPL BL ON A."BPLId" = BL."BPLId"
Left Outer Join OCRD E ON E."CardCode"=A."CardCode"
Left Outer Join OCRG F On F."GroupCode"=E."GroupCode"
Where A."CANCELED"='N'
--AND (A."DocDate">=FD AND A."DocDate"<=TD)
UNION ALL
SELECT
'A/R CreditNote' "DocType",A."DocEntry",
A."DocNum",
C."SeriesName",
A."DocDate",
Month(A."DocDate") As "Month",
A."NumAtCard",
A."CardCode",
A."CardName",A."Address" "Bill To",F."GroupName" "Customer Group",
A."Comments",
S."SlpName",
B."WhsCode",
B."OcrCode" "Dept",
D."InvntItem",
B."NoInvtryMv",
A."DocCur",B."TreeType",
B."ItemCode",
B."Dscription",
-B."Quantity",
-B."PriceBefDi" "Gross Unit Price",-(((B."Quantity" * B."PriceBefDi") *
B."DiscPrcnt") / 100) AS "Discount Amount",-B."Price" "Net Price",
-B."StockPrice" "Cost",-(ISNULL(B."Quantity",0)*ISNULL(B."StockPrice",0)) "Total
Cost",
-(case A."CurSource" when 'L' then B."LineTotal" when 'S' then B."TotalSumSy"
when 'C' then b."TotalFrgn" end) "LineTotal",
-B."VatSum" "VatAmnt", -((case A."CurSource" when 'L' then B."LineTotal" when
'S' then B."TotalSumSy" when 'C' then b."TotalFrgn" end)+ISNULL(B."VatSum",0)) "Net
Price With VAT"
FROM ORIN A
INNER JOIN RIN1 B ON A."DocEntry" = B."DocEntry"
LEFT OUTER JOIN NNM1 C ON C."Series" = A."Series"
LEFT OUTER JOIN OITM D ON D."ItemCode" = B."ItemCode"
LEFT OUTER JOIN OITB G ON D."ItmsGrpCod"=G."ItmsGrpCod"
LEFT OUTER JOIN OSLP S ON A."SlpCode"=S."SlpCode"
lEFT OUTER JOIN OBPL BL ON A."BPLId" = BL."BPLId"
Left Outer join RIN21 T On T."DocEntry"=A."DocEntry"
Left Outer Join OCRD E ON E."CardCode"=A."CardCode"
Left Outer Join OCRG F On F."GroupCode"=E."GroupCode"
Where A."CANCELED"='N' AND (CASE When B."BaseType"=-1 Then T."RefObjType" Else
B."BaseType" End)='13'
--AND (A."DocDate">=FD AND A."DocDate"<=TD)
UNION ALL
SELECT
'Return Request' "DocType",A."DocEntry",
A."DocNum",
C."SeriesName",
A."DocDate",
Month(A."DocDate") As "Month",
A."NumAtCard",
A."CardCode",
A."CardName",A."Address" "Bill To",F."GroupName" "Customer Group",
A."Comments",
S."SlpName",
B."WhsCode",
B."OcrCode" "Dept",
D."InvntItem",
B."NoInvtryMv",
A."DocCur",B."TreeType",
B."ItemCode",
B."Dscription",
-B."Quantity",
-B."PriceBefDi" "Gross Unit Price",(((B."Quantity" * B."PriceBefDi") *
B."DiscPrcnt") / 100) AS "Discount Amount",-B."Price" "Net Price",
-B."StockPrice" "Cost",-(ISNULL(B."Quantity",0)*ISNULL(B."StockPrice",0)) "Total
Cost",
-(case A."CurSource" when 'L' then B."LineTotal" when 'S' then B."TotalSumSy"
when 'C' then b."TotalFrgn" end) "LineTotal",
-B."VatSum" "VatAmnt", -((case A."CurSource" when 'L' then B."LineTotal" when
'S' then B."TotalSumSy" when 'C' then b."TotalFrgn" end)+ISNULL(B."VatSum",0)) "Net
Price With VAT"
FROM ORRR A
INNER JOIN RRR1 B ON A."DocEntry" = B."DocEntry"
LEFT OUTER JOIN NNM1 C ON C."Series" = A."Series"
LEFT OUTER JOIN OITM D ON D."ItemCode" = B."ItemCode"
LEFT OUTER JOIN OITB G ON D."ItmsGrpCod"=G."ItmsGrpCod"
LEFT OUTER JOIN OSLP S ON A."SlpCode"=S."SlpCode"
lEFT OUTER JOIN OBPL BL ON A."BPLId" = BL."BPLId"
Left Outer Join OCRD E ON E."CardCode"=A."CardCode"
Left Outer Join OCRG F On F."GroupCode"=E."GroupCode"
Where A."CANCELED"='N' AND B."BaseType"='13'
--AND (A."DocDate">=FD AND A."DocDate"<=TD)
-----------------------------------------------------------------------------------
---------------------------------------
----> INVENTORY AUDIT REPORT ?
SELECT T0.[DocDate], case when T0.[TransType] = 13 then 'AR'
when T0.[TransType] = 14 then 'ARCredit'when T0.[TransType] = 15 then 'Delivery'
when T0.[TransType] = 16 then 'SalesReturn'when T0.[TransType] = 203 then 'ARDown'
when T0.[TransType] = 20 then 'Goods Receipt'when T0.[TransType] = 21 then 'Goods
Return'
when T0.[TransType] = 204 then 'APDown'when T0.[TransType] = 18 then 'APInvoice'
when T0.[TransType] = 13 then 'AP Credit Memo'when T0.[TransType] = 69 then
'LandedCost'
when T0.[TransType] = 24 then 'Incoming Payment'when T0.[TransType] = 25 then
'Deposit'
when T0.[TransType] = 46 then 'VendorPayment'when T0.[TransType] = 57 then 'Check
for Payment'
when T0.[TransType] = 76 then 'Postdated Check'when T0.[TransType] = 58 then
'Inventory list'
when T0.[TransType] = 59 then 'T0.JrnlMemo'when T0.[TransType] = 60 then
'T0.JrnlMemo'
when T0.[TransType] = 67 then 'Inventory Transfer'when T0.[TransType] = 68 then
'Work Instruction'
when T0.[TransType] = 162 then 'Inventory Valuation'when T0.[TransType] = 202 then
'Production order'
when T0.[TransType] = -2 then 'Opening Balance'when T0.[TransType] = -3 then
'Closing Balance'
when T0.[TransType] = 30 then 'Journal Entry'when T0.[TransType] = 321 then
'Internal Reconilation'
when T0.[TransType] = 10000046 then 'Data archive'when T0.[TransType] = 310000001
then 'Initial Qty'
when T0.[TransType] = 10000071 then 'Inventory posting' end as 'Doc Type',
T0.[BASE_REF] as Document, T0.[Warehouse] as Whse, T1.[FormatCode] as 'G/L Acct/BP
Code', T1.[AcctName] as 'G/L Acct/BP Name', T0.ItemCode, T0.Dscription,
sum(T0.[InQty]) as 'Rec.Qty', sum(T0.[OutQty]) as 'Iss. Qty', T0.[Currency], T0.
[Price] as 'Price after Disc.',sum(T0.[InQty] - T0.[OutQty]) as 'Balance.'
FROM OINM T0 INNER JOIN OACT T1 ON t0.cardcode = T1.AcctCode
--WHERE T0.DocDate >= [%1] and T0.DocDate <= [%2]
group by T0.[DocDate],T0.[TransType],T0.[BASE_REF],T0.[Warehouse],T1.
[FormatCode],T1.[AcctName],T0.[Currency], T0.
[Price],T0.ItemCode,T0.Dscription,T0.JrnlMemo
union all
SELECT T0.[DocDate], case when T0.[TransType] = 13 then 'AR'
when T0.[TransType] = 14 then 'ARCredit'when T0.[TransType] = 15 then 'Delivery'
when T0.[TransType] = 16 then 'SalesReturn'when T0.[TransType] = 203 then 'ARDown'
when T0.[TransType] = 20 then 'Goods Receipt'when T0.[TransType] = 21 then 'Goods
Return'
when T0.[TransType] = 204 then 'APDown'when T0.[TransType] = 18 then 'APInvoice'
when T0.[TransType] = 13 then 'AP Credit Memo'when T0.[TransType] = 69 then
'LandedCost'
when T0.[TransType] = 24 then 'Incoming Payment'when T0.[TransType] = 25 then
'Deposit'
when T0.[TransType] = 46 then 'VendorPayment'when T0.[TransType] = 57 then 'Check
for Payment'
when T0.[TransType] = 76 then 'Postdated Check'when T0.[TransType] = 58 then
'Inventory list'
when T0.[TransType] = 59 then 'T0.JrnlMemo'when T0.[TransType] = 60 then
'T0.JrnlMemo'
when T0.[TransType] = 67 then 'Inventory Transfer'when T0.[TransType] = 68 then
'Work Instruction'
when T0.[TransType] = 162 then 'Inventory Valuation'when T0.[TransType] = 202 then
'Production order'
when T0.[TransType] = -2 then 'Opening Balance'when T0.[TransType] = -3 then
'Closing Balance'
when T0.[TransType] = 30 then 'Journal Entry'when T0.[TransType] = 321 then
'Internal Reconilation'
when T0.[TransType] = 10000046 then 'Data archive'when T0.[TransType] = 310000001
then 'Initial Qty'
when T0.[TransType] = 10000071 then 'Inventory posting' end as 'Doc Type',
T0.[BASE_REF] as Document, T0.[Warehouse] as Whse, T1.[cardCode] as 'G/L Acct/BP
Code', T1.[cardName] as 'G/L Acct/BP Name', T0.ItemCode, T0.Dscription,
sum(T0.[InQty]) as 'Rec.Qty', sum(T0.[OutQty]) as 'Iss. Qty', T0.[Currency], T0.
[Price] as 'Price after Disc.',sum(T0.[InQty] - T0.[OutQty]) as 'Balance.'
FROM OINM T0 INNER JOIN OCRD T1 ON t0.cardcode = T1.cardcode
----WHERE T0.DocDate >= [%1] and T0.DocDate <= [%2]
group by T0.[DocDate],T0.[TransType],T0.[BASE_REF],T0.[Warehouse],T1.[cardCode],T1.
[cardName],T0.[Currency], T0.[Price],T0.ItemCode,T0.Dscription,T0.JrnlMemo
Order by T0.DocDate, T0.ItemCode
-----------------------------------------------------------------------------------
---------------------------------------
----> AGEING INVENTORY
/* Select From Dbo.OITL XX */
Declare @FD Datetime
Declare @Whs Nvarchar(100)
--Select @FD=/* XX.DocDate */[%0]
--Select @FD='20220707'
SELECT ItemCode,ItemName,[Item Group],BatchNo,Location,
SUM(QTY) QTY
,SUM(Qty*Price) Value
,Sum(Case When AgingDay>=0 And AgingDay<=30 Then Qty Else 0 End) [0-30 Day Qty]
,Sum(Case When AgingDay>=0 And AgingDay<=30 Then Qty*Price Else 0 End) [0-30 Day
Value]
,Sum(Case When AgingDay>=31 And AgingDay<=60 Then Qty Else 0 End) [31-60 Day Qty]
,Sum(Case When AgingDay>=31 And AgingDay<=60 Then Qty*Price Else 0 End) [31-60 Day
Value]
,Sum(Case When AgingDay>=61 And AgingDay<=90 Then Qty Else 0 End) [61-90 Day Qty]
,Sum(Case When AgingDay>=61 And AgingDay<=90 Then Qty*Price Else 0 End) [61-90 Day
Value]
,Sum(Case When AgingDay>=91 And AgingDay<=120 Then Qty Else 0 End) [91-120 Day Qty]
,Sum(Case When AgingDay>=91 And AgingDay<=120 Then Qty*Price Else 0 End) [91-120
Day Value]
,Sum(Case When AgingDay>=121 And AgingDay<=150 Then Qty Else 0 End) [121-150 Day
Qty]
,Sum(Case When AgingDay>=121 And AgingDay<=150 Then Qty*Price Else 0 End) [121-150
Day Value]
,Sum(Case When AgingDay>=151 And AgingDay<=180 Then Qty Else 0 End) [151-180 Day
Qty]
,Sum(Case When AgingDay>=151 And AgingDay<=180 Then Qty*Price Else 0 End) [151-180
Day Value]
,Sum(Case When AgingDay>=181 And AgingDay<=360 Then Qty Else 0 End) [181-360 Day
Qty]
,Sum(Case When AgingDay>=181 And AgingDay<=360 Then Qty*Price Else 0 End) [181-360
Day Value]
,Sum(Case When AgingDay>=361 And AgingDay<=730 Then Qty Else 0 End) [361-730 Day
Qty]
,Sum(Case When AgingDay>=361 And AgingDay<=730 Then Qty*Price Else 0 End) [361-730
Day Value]
,Sum(Case When AgingDay>=731 Then Qty Else 0 End) [731 Above Qty]
,Sum(Case When AgingDay>=731 Then Qty*Price Else 0 End) [731 Above Value]
FROM(
SELECT C.DistNumber BatchNo,B.ItemCode,MAX(I.ItemName) ItemName,IG.ItmsGrpNam 'Item
Group',B.LocCode
,SUM(A.Quantity) Qty,DATEDIFF(DD,(C.INDate),@FD) AgingDay,X.AvgPrice 'Price'
,'Tamil Nadu' Location
FROM ITL1 A WITH(NOLOCK)
INNER JOIN OITL B WITH(NOLOCK) ON a.LogEntry = b.LogEntry
INNER JOIN OBTN c WITH(NOLOCK) ON a.ItemCode = c.ItemCode and a.SysNumber =
c.SysNumber AND A.MdAbsEntry=C.AbsEntry
INNER JOIN OITM I WITH(NOLOCK) ON B.ItemCode=I.ItemCode
INNER JOIN OITB IG WITH(NOLOCK) ON I.ItmsGrpCod=IG.ItmsGrpCod
INNER JOIN OITW X WITH(NOLOCK) ON X.WhsCode=B.LocCode AND X.ItemCode=B.ItemCode
INNER JOIN OWHS E WITH(NOLOCK) ON E.WhsCode=B.LocCode
WHERE E.Location=43 AND B.DocDate<=@FD
GROUP BY c.DistNumber,B.ItemCode,B.LocCode,IG.ItmsGrpNam,X.AvgPrice,DATEDIFF(DD,
(C.INDate),@FD)
HAVING SUM(A.Quantity)<>0
)X
Group By ItemCode,ItemName,[Item Group],BatchNo,Location
-----------------------------------------------------------------------------------
---------------------------------------
