Queries Reports for SAP B1

Hi All,

I am Unnikrishnan from India-Kerala State, having 5+ years of experience in SAP Business One.

Here I am posting some queries reports which may helpful for newcomers.

1. Detailed Sales Analysis

Declare @FDate DateTime, @TDate DateTime, @CardCode Varchar (20), @ItmGrpN Varchar (100),  @ItemCode Varchar (100)

Select @FDate = min(Q.Refdate) from OJDT Q where Q.Refdate >='[%0]’

Select @TDate = max(R.Refdate) from OJDT R where R.Refdate <='[%1]’

Select @CardCode = Min(S.CardCode) from OCRD S where S.CardCode like N’%[%3]%’

Select @ItmGrpN = Min(T.ItmsGrpNam) from OITB T where T.ItmsGrpNam like N’%[%4]%’

Select @ItemCode = Min(U.ItemCode) from OITM U where U.ItemCode like N’%[%5]%’

–Select @FDate, @TDate, @ItmGrpN, @ItemName

Select ‘AR Invoice'[Type]

,case A.DocType When ‘I’ then ‘Item’ else ‘Service’ end[DocType]

,A.DocCur

,A.DocDate

, A.DocNum

,A.CardCode, C.CardName,D.GroupName[BP group]

,B.ItemCode

,Case A.DocType When ‘I’ then E.ItemName else B.Dscription end [ItemName/Description]

,B.Quantity[Quantity]

,B.Price

,B.Rate

, B.Currency[Price Currency]

,B.LineTotal- (B.LineTotal*isnull(A.DiscPrcnt,0)/100)[LineTotalLC],B.GrssProfit

,B.TotalFrgn- (B.TotalFrgn*isnull(A.DiscPrcnt,0)/100)[LineTotalFC]

,B.GTotalFC

,Case When B.SlpCode<>’-1′ then F.SlpName

  When B.SlpCode=’-1′ and A.SlpCode<>’-1′ then (Select M.SlpName from OSLP M where A.SlpCode = M.SlpCode) else ” end [Sales Person]

from OINV A

  Left Outer Join INV1 B on A.Docentry = B.DocEntry

  Left Outer Join OCRD C on A.CardCode = C.CardCode

  Left Outer Join OCRG D on C.GroupCode = D.GroupCode

  left outer join OITM E on B.ItemCode = E.ItemCode

  Left Outer Join OSLP F on B.SlpCode = F.SlpCode

  Left Outer Join OITB G on E.ItmsGrpCod = G.ItmsGrpCod

Where A.Canceled = ‘N’

  and A.DocDate >=@FDate and A.DocDate  <=@TDate

  and D.GroupName Like N’%[%2]%’

  and C.CardCode  Like ‘%[%3]%’

  and (G.ItmsGrpNam Like N’%[%4]%’ or G.ItmsGrpNam is null)

  and (B.ItemCode Like ‘%[%5]%’ or B.ItemCode is null)

Union All

Select ‘AR Credit Note'[Type]

,case A.DocType When ‘I’ then ‘Item’ else ‘Service’ end[DocType]

,A.DocCur

,A.DocDate

, A.DocNum

,A.CardCode, C.CardName,D.GroupName[BP group]

,B.ItemCode

,Case A.DocType When ‘I’ then E.ItemName else B.Dscription end [ItemName/Description]

, Case when B.NoInvtryMv =’Y’ then 0 else -B.Quantity end [Quantity]

,B.Price

,B.Rate

, B.Currency [Price Currency]

,-B.LineTotal+ (B.LineTotal*isnull(A.DiscPrcnt,0)/100)[LineTotal],-B.GrssProfit

,-B.TotalFrgn+ (B.TotalFrgn*isnull(A.DiscPrcnt,0)/100)[LineTotalFC]

,-B.GTotalFC

,Case When B.SlpCode<>’-1′ then F.SlpName

  When B.SlpCode=’-1′ and A.SlpCode<>’-1′ then (Select M.SlpName from OSLP M where A.SlpCode = M.SlpCode) else ” end [Sales Person]

from ORIN A

  Left Outer Join RIN1 B on A.Docentry = B.DocEntry

  Left Outer Join OCRD C on A.CardCode = C.CardCode

  Left Outer Join OCRG D on C.GroupCode = D.GroupCode

  left outer join OITM E on B.ItemCode = E.ItemCode

  Left Outer Join OSLP F on B.SlpCode = F.SlpCode

  Left Outer Join OITB G on E.ItmsGrpCod = G.ItmsGrpCod

Where A.Canceled = ‘N’

  and A.DocDate >=@FDate and A.DocDate  <=@TDate

  and D.GroupName Like N’%[%2]%’

  and C.CardCode  Like ‘%[%3]%’

  and (G.ItmsGrpNam Like N’%[%4]%’ or G.ItmsGrpNam is null)

  and (B.ItemCode Like ‘%[%5]%’ or B.ItemCode is null)

Order BY 3

2. 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]

3. Approval Process tracking for AP Down payment request

SELECT T5.U_NAME “RequestedBy”

,T6.”U_NAME” “Originator”

, T0.”DocNum”, T0.”DocStatus”, T0.”DocDate”

,T13.U_Name [Authorized By]

FROM ODPO T0 

Left Outer Join ODRF T4 ON T0.[draftKey] = T4.[DocEntry] and T4.ObjType = 204

Left Outer Join OUSR T5 on T4.UserSign = T5.USERID

LEFT oUTER jOIN OUSR T6 on T0.UserSign = T6.USERID

Left Outer Join OWDD T11 on T11.ObjType = 204and T11.DocEntry = T0.DocEntry

Left Outer join  WDD1 T12 on T12.WddCode= T11.WddCode and T12.Status=’Y’

LEFT oUTER jOIN OUSR T13 on T12.UserID = T13.USERID

WHERE T0.”CANCELED”=’N’

Order By T0.”DocDate”, T0.”DocNum”

4. Comparision of AP Invoice Price with PO Price

SELECT  T0.DocDate,T0.NumAtCard,T0.DocNum,O1.CardName,T1.ItemCode, I1.ItemName,T4.Price [POrate],T1.Price [AP Invoice Rate],(T4.Price-T1.Price) Diff

FROM OPCH T0

inner join PCH1 T1 ON T0.[DocEntry]=T1.[DocEntry]

Inner Join OCRD O1 on T0.CardCode = O1.CardCode

Inner Join OITM I1 on T1.ItemCode = I1.ItemCode

inner join  PDN1 T2 ON T1.BaseType = 20 and T1.[BaseEntry]=T2.[DocEntry] AND T1.BaseLine = T2.LineNum

inner JOIN OPDN T3 ON T3.[DocEntry]=T2.[DocEntry]

inner JOIN POR1 T4 ON T2.BaseType = 22 and T2.[BaseEntry]=T4.[DocEntry] AND T2.BaseLine = T4.LineNum

inner JOIN OPOR T5 ON T4.[DocEntry]=T5.[DocEntry]

Where T0.DocDate >=[%0] and T0.DocDate <=[%1]

5. 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

————————

Please share your feedback

Thanks

Unnikrishnan