Skip to Content
Author's profile photo Unnikrishnan M B

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

Assigned Tags

      20 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo AndakondaRamudu A
      AndakondaRamudu A

      Hi Unnikrishnan Balan

      Thanks for Shared, Valuable Reports Query's..

      It's very Help full Us...

      Author's profile photo Unnikrishnan Balan
      Unnikrishnan Balan
      Blog Post Author

      Hi Ramudu,

      Many thanks for your comments.

      Unnikrishnan

      Author's profile photo Former Member
      Former Member

      Thanks sir, you post a helpful quarry

      Reg.

      Chetan Vora

      Author's profile photo Unnikrishnan Balan
      Unnikrishnan Balan
      Blog Post Author

      Thanks Chetan

      Author's profile photo Former Member
      Former Member

      Hello ,

      Its really a very nice effort, & very helpful also.

      Thank you for your work.

      warm regards,

      Aaar Veee

      Author's profile photo Unnikrishnan Balan
      Unnikrishnan Balan
      Blog Post Author

      Thank you so much Aaar Veee

      Author's profile photo Unnikrishnan Balan
      Unnikrishnan Balan
      Blog Post Author

      Many thanks Ramasamy

      Author's profile photo Former Member
      Former Member

      Hi Unni ,     
      Its a nice effort & ur postings are valubable & Helpful for us.. Thanks for ur great Effort Regards, Ramasamy

      Author's profile photo Former Member
      Former Member

      Hi Balan,

      Its a good work done, appreciate your initiation to share such kind of valuable and needed information in day-to-day implementation requirement/support.

      This will gonna help all in any way out

      Regards,

      Bhushan Verma

      Author's profile photo Unnikrishnan Balan
      Unnikrishnan Balan
      Blog Post Author

      Thanks Bhushan for your valuable coments...
      Sure, I will try my level best

      Regards

      Unnikrishnan

      Author's profile photo Former Member
      Former Member

      Hi Unnikrishnan,

      Good work. It will definitely helpful to forum users.

      Author's profile photo Bhanu Pratap Sharma
      Bhanu Pratap Sharma

      Hi Balan,

      Thanks for sharing these report.

      Regards

      Bhanu

      Author's profile photo Unnikrishnan Balan
      Unnikrishnan Balan
      Blog Post Author

      Thanks Bhanu.......

      Author's profile photo Former Member
      Former Member

      hi Unnikrishnan,

                             Thanks for sharing these queries and ur excellent efforts.

      Author's profile photo Unnikrishnan Balan
      Unnikrishnan Balan
      Blog Post Author

      Thanks Alok.

      Author's profile photo Former Member
      Former Member

      Hi Unnikrishnan,

      Thank you so much for sharing your valuable information in SCN.

      Author's profile photo Former Member
      Former Member

      Hi unnikrishnan,

      Thank you sharing such post. Actually these queries will be useful for all the forum users.

      Great work!!!

      Author's profile photo Former Member
      Former Member

      Hi,

      Thank you for sharing your knowledge in SCN .

      Author's profile photo Saufilbadli Ya'cob
      Saufilbadli Ya'cob

      Hi Unnikrishnan,


      Thank you for sharing and very helpful


      Thanks,

      Saufil


      Author's profile photo Former Member
      Former Member

      Thank you so much.  Great reports... all of them. g