Enterprise Resource Planning Blogs by Members
Gain new perspectives and knowledge about enterprise resource planning in blog posts from community members. Share your own comments and ERP insights today!
cancel
Showing results for 
Search instead for 
Did you mean: 
Former Member
0 Kudos

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]