Hi All,

Why i am writing this document is to share some information with you.

I have some query based reports, Please find Below.

Top 10 Suppliers with the most Goods Returns-

SELECT TOP 10 T0.[CardName], SUM(T0.[DocTotal]) AS ‘Returned value’ FROM ORPD T0 GROUP BY T0.[CardName] ORDER BY SUM(T0.[DocTotal]) DESC

__________

Stock Ledger-

Declare @FDate DateTime, @TDate DateTime, @Whs Varchar (10)

Select @FDate = Min(AA.DocDate) from OINM AA Where AA.DocDate>=[%0]

Select @TDate = Max(AB.DocDate) from OINM AB Where AB.DocDate<=[%1]

Select @Whs = Max(AC.Warehouse) from OINM AC Where AC.Warehouse Like ‘%[%2]%’

Set @FDate = ‘[%0]’

Set @TDate = ‘[%1]’

;WITH STKLOG AS

(

Select A.ItemCode[Item_Code], B.ItemName[Item_Name],A.Warehouse[Warehouse_Code]

  ,0[OB_Qty]

  ,0[OB_Value]

  ,Sum(A.InQty)[ReceiptQty]

  ,Case When A.TransValue>0 then Sum(A.TransValue) else 0 end [ReceiptValue]

  ,Sum(A.OutQty)[OutQty]

  ,Case When A.TransValue<=0 then Sum(A.TransValue) else 0 end [OutValue]

  ,0[Cls_Qty]

  ,0[Cls_Value]

from dbo.OINM A

  Inner Join OITM B on A.ItemCode = B.ItemCode

Where A.DocDate>=@FDate and A.DocDate<=@TDate and A.Warehouse Like ‘%[%2]%’

Group By A.ItemCode, B.ItemName,A.Warehouse, A.TransValue

Union All

SELECT T0.ItemCode[Item_Code], T1.ItemName[Item_Name], T0.Warehouse[Warehouse_Code]

  ,Case When T0.DocDate <@FDate then Sum(T0.InQty-T0.OutQty) else 0 end [OB_Qty]

  ,Case When T0.DocDate <@FDate then Sum(T0.TransValue) else 0 end [OB_Value]

  ,0[ReceiptQty]

  ,0[ReceiptValue]

  ,0[OutQty]

  ,0[OutValue]

  ,Case When T0.DocDate <=@TDate then Sum(T0.InQty-T0.OutQty) else 0 end [Cls_Qty]

  ,Case When T0.DocDate <=@TDate then Sum(T0.TransValue) else 0 end [Cls_Value]

FROM OINM T0

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

WHERE T0.DocDate <=@TDate and T0.Warehouse Like ‘%[%2]%’

GROUP BY T0.[ItemCode], T1.ItemName, T0.Warehouse, T0.DocDate

)

—-

Select @FDate[From Date], @TDate [To Date]

  , STKLOG.Item_Code

  , STKLOG.Item_Name, STKLOG.Warehouse_Code

  ,Sum(STKLOG.OB_Qty)[OBQTY]

  ,Sum(STKLOG.OB_Value)[OBValue]

  ,Sum(STKLoG.ReceiptQty)[RecQty]

  , Sum(STKLOG.ReceiptValue)[RecValue]

  ,Sum(STKLoG.OutQty)[OutQty]

  ,Sum(STKLOG.OutValue)[OutValue]

  ,Sum(STKLOG.Cls_Qty)[ClsQty], Sum(STKLOG.Cls_Value)[ClsValue]

From STKLOG

group By STKLOG.Item_Code, STKLOG.Item_Name, STKLOG.Warehouse_Code

_________

Sales Order to AR Invoice Tracking-

Select A.DocNum[SO No], A.DocEntry [SO DocEntry], A.DocDate [SO DocDate], H.ItmsGrpNam, G.FrgnName, G.ItemCode, G.ItemName

  , D.DocDate [DC Date], D.DocNum [DC No], F.DocDate [Invoice Date], F.DocNum [Invoice No]

From ORDR A

  Inner Join RDR1 B on A.docEntry = B.DocEntry

  left Outer Join DLN1 C on C.BaseType = 17 and C.BaseEntry = B.DocEntry and C.BaseLine = B.LineNum

  left Outer Join ODLN D on C.docEntry = D.DocEntry and D.Canceled = ‘N’

  left Outer Join INV1 E on E.BaseType = 15 and E.BaseEntry = C.DocEntry and E.BaseLine = C.LineNum

  left Outer Join OINV F on E.docEntry = F.DocEntry  and F.Canceled = ‘N’

  Inner Join OITM G on B.ItemCode = G.ItemCode

  Inner Join OITB H on G.ItmsGrpCod = H.ItmsGrpCod

Where A.DocDate >=[%0]

  and A.DocDate <=[%1]

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply