This blog will provide some useful sql scripts required by most of the users on SAP Business one.

h5.
FIFO Item Sales and Buy Price for Batch Items:

Most of the customers those are using FIFO valuation method require the information that which FIFO layer is used at particular invoice.
This query will display the Open FIFO layers of items managed by batches ,will list the buy price and sales price with supplier and customer details. 

SELECT  T0.LineNum,T0.DocEntry,
  T0.VisOrder,
  T1.DocDate,
  T0.Dscription,
  T1.DocNum,

  T6.CardName as ,
  T1.CardName,
  T1.ShipToCode,
  T2.Quantity,
  T9.Price as ,  
  (T0.Price / (CASE IsNULL(T0.Rate, 0) when 0.0 then 1.0

else T0.Rate end)) as ,
  T2.Quantity * T9.Price as ,
  T2.Quantity * (T0.Price / (CASE IsNULL(T0.Rate, 0) when

0.0 then 1.0 else T0.Rate end)) as ,  
  T2.Quantity * (T0.Price / (CASE IsNULL(T0.Rate, 0) when

0.0 then 1.0 else T0.Rate end)) – T2.Quantity * T9.Price as ,
  CASE when T2.Quantity * (T0.Price / (CASE IsNULL(T0.Rate,

0) when 0.0 then 1.0 else T0.Rate end)) = 0 then 0.0 else (T2.Quantity *

(T0.Price / (CASE   IsNULL(T0.Rate, 0) when 0.0 then

1.0 else T0.Rate end)) – T2.Quantity * T9.Price)/ (T2.Quantity *

(T0.Price / (CASE IsNULL(T0.Rate, 0) when 0.0 then 1.0  else

T0.Rate end))) end as ,
  T4.ItmsGrpNam as
FROM dbo.INV1 T0
 INNER JOIN dbo.OINV T1 ON T0.DocEntry = T1.DocEntry
 INNER JOIN dbo.IBT1 T2 ON T2.BaseType = T1.ObjType and

T2.BaseEntry = T0.DocEntry and T2.BaseLinNum=T0.LineNum
 INNER JOIN dbo.OITM T3 ON T0.ItemCode = T3.ItemCode
 INNER JOIN dbo.OITB T4 ON T3.ItmsGrpCod = T4.ItmsGrpCod
 INNER JOIN dbo.OIBT T5 ON T5.ItemCode = T0.ItemCode and

T2.BatchNum = T5.BatchNum and T5.WhsCode = T0.WhsCode

 INNER JOIN (select MIN(TransNum) as , TX.ItemCode,

TX.BatchNum from OIBT TX INNER JOIN OINM TX1 ON TX.BaseType =

TX1.TransType and TX.BaseNum = TX1.    BASE_REF

and TX.BaseLinNum = TX1.DocLineNum and TX.ItemCode=TX1.ItemCode and

TX.WhsCode = TX1.Warehouse
      group by TX.ItemCode, TX.BatchNum) TX6 ON

TX6.ItemCode = T0.ItemCode and TX6.BatchNum = T2.BatchNum
 INNER JOIN dbo.OINM T6 ON TX6.TransNum=T6.TransNum  and

T6.InQty0
 LEFT OUTER JOIN dbo.OPDN T7 ON T7.ObjType = T5.BaseType and

T7.DocEntry = T5.BaseEntry
 LEFT OUTER JOIN dbo.OPCH T8 ON T8.ObjType = T5.BaseType and

T8.DocEntry = T5.BaseEntry  
 INNER JOIN (select SUM(TX.CalcPrice*TX.OutQty)/SUM(TX.OutQty) as

[Price], TX.BASE_REF, TX.TransType, TX.DocLineNum from OINM TX where

TX.TransType=13             group by TX.BASE_REF, TX.TransType,

TX.DocLineNum) T9 ON T9.BASE_REF=T1.DocNum and T9.TransType=T1.ObjType

and T9.DocLineNum=T0.LineNum
WHERE T1.DocNum >='[%0]’ and T1.DocNum

h5.
FIFO Item Sale and Purchase Price for Non Batch Items:

Most of the customers those are using FIFO valuation method require the information that which FIFO layer is used at particular invoice.
This query will display the Open FIFO layers of items not managed by batches ,will list the buy price and sales price with supplier and customer details. 

SELECT T0.LineNum ,T0.DocEntry,
  T0.VisOrder,
  T1.DocDate,
  T0.Dscription,
  T1.DocNum,

  T5.CardName as ,
  T1.CardName,
  T1.ShipToCode,
  T4.OutQty,
  T4.CalcPrice as ,
  (T0.Price / (CASE IsNULL(T0.Rate, 0) when 0.0 then 1.0

else T0.Rate end)) as ,
  T4.OutQty* (T4.CalcPrice) as ,
  T4.OutQty * (T0.Price / (CASE IsNULL(T0.Rate, 0) when 0.0

then 1.0 else T0.Rate end)) as ,
  T4.OutQty * (T0.Price / (CASE IsNULL(T0.Rate, 0) when 0.0

then 1.0 else T0.Rate end)) – T4.OutQty* (T4.CalcPrice) as ,
  CASE when T4.OutQty * (T0.Price / (CASE IsNULL(T0.Rate,

0) when 0.0 then 1.0 else T0.Rate end)) = 0 then 0.0 else (T4.OutQty *

(T0.Price / (CASE IsNULL(T0.Rate, 0) when 0.0 then 1.0 else T0.Rate end))

– T4.OutQty * (T4.CalcPrice))/ (T4.OutQty * (T0.Price / (CASE IsNULL

(T0.Rate, 0) when 0.0 then 1.0 else T0.Rate end))) end as ,
  T3.ItmsGrpNam as
FROM dbo.INV1 T0
 INNER JOIN dbo.OINV T1 ON T0.DocEntry = T1.DocEntry
 INNER JOIN dbo.OITM T2 ON T0.ItemCode = T2.ItemCode
 INNER JOIN dbo.OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod
 INNER JOIN dbo.OINM T4 ON T1.DocNum=T4.BASE_REF and T1.ObjType =

T4.TransType and T0.LineNum = T4.DocLineNum
 INNER JOIN dbo.OINM T5 ON T5.TransNum = (select MIN(TransNum)

FROM OINM
          

 where ItemCode=T0.ItemCode
          

 and Warehouse=T0.WhsCode
          

 and TransNum          

 having SUM(InQty-OutQty)>0
          

 )
 LEFT OUTER JOIN dbo.OPDN T6 ON T6.ObjType = T5.TransType and

T6.DocNum = T5.BASE_REF
 LEFT OUTER JOIN dbo.OPCH T7 ON T7.ObjType = T5.TransType and

T7.DocNum  = T5.BASE_REF
WHERE IsNULL(T2.ManBtchNum, ‘N’)=’N’ and IsNULL(T2.ManSerNum, ‘N’)=’N’
  and T1.DocNum >='[%0]’ and T1.DocNum

h5.
Sales Register Report Query:

This query will give the complete sales details with tax values in particular range.If the customer wants to know about the total sales with tax details in particular date range this will be useful.

SELECT M.DocNum AS ‘AP Inv. #’, M.DocDate as ‘Date’, M.CardName as

’Vendor Name’,M.NumAtCard as ‘Bill No. & Dt.’,L.ItemCode,

L.Dscription,L.LineTotal as ‘Amount’,L.vatsum ,L.PriceBefDi as ‘Rate’,
(Select Sum(LineTotal) FROM INV1 L Where L.DocEntry=M.DocEntry) as ‘Base

Amt.(Rs.)’,
(SELECT Sum(TaxSum) FROM INV4 where statype=-90 and DocEntry=M.DocEntry)

as ‘BED (Rs.)’,
(SELECT Sum(TaxSum) FROM INV4 where statype=7 and DocEntry=M.DocEntry) as

’HSCess_New (Rs.)’,
(SELECT Sum(TaxSum) FROM INV4 where statype=-60 and DocEntry=M.DocEntry)

as ‘EDCS (Rs.)’,
(SELECT Sum(TaxSum) FROM INV4 where statype=-55 and DocEntry=M.DocEntry)

as ‘HECS (Rs.)’,
(SELECT Sum(TaxSum) FROM INV4 where statype=1 and DocEntry=M.DocEntry) as

’ VAT (Rs.) ‘,
(SELECT Sum(TaxSum) FROM INV4 where statype=4 and DocEntry=M.DocEntry) as

’ CST (Rs.) ‘,
(SELECT Sum(TaxSum) FROM INV4 where statype=-10 and DocEntry=M.DocEntry)

as ‘ CVD (Rs.) ‘,
(SELECT Sum(TaxSum) FROM INV4 where statype=5 and DocEntry=M.DocEntry) as

’ Ser.Tax (Rs.) ‘,
(SELECT Sum(TaxSum) FROM INV4 where statype=6 and DocEntry=M.DocEntry) as

’CS on Ser.Tax (Rs.)’,
(SELECT Sum(TaxSum) FROM INV4 where statype=4 and DocEntry=M.DocEntry) as

’HECS_ST (Rs.)’,
(Select Sum(LineTotal) From INV3 Q Where Q.DocEntry=M.DocEntry) AS

’Freight (Rs.)’,
M.WTSum AS ‘TDS (Rs.)’,
M.DocTotal as ‘Total (Rs.)’
FROM OINV M LEFT OUTER JOIN INV1 L on L.DocEntry=M.DocEntry
LEFT OUTER JOIN INV4 T on T.DocEntry=L.DocEntry and L.LineNum=T.LineNum
LEFT OUTER JOIN INV5 J ON M.DocEntry = J.AbsEntry
LEFT OUTER JOIN INV3 Q ON M.DocEntry = Q.DocEntry
WHERE (M.DocDate >= ‘%0’ AND M.DocDate GROUP BY
M.DocNum,M.DocDate,M.CardName,M.NumAtCard,M.DocEntry,M.DiscSum,M.WTSum,M.

DocTotal,L.ItemCode,L.Dscription,L.LineTotal,L.vatsum,L.PriceBefDi
ORDER BY
M.DocNum,M.DocDate,M.CardName,M.NumAtCard,M.DocEntry,M.DiscSum,M.WTSum,M.

DocTotal,L.LineTotal,L.vatsum,L.PriceBefDi

h5. Purchase Register Query

Similar to Sales Register report ,Purchase Register report gives the detailed analysis of purchase with tax details in particular date range .

SELECT M.DocNum AS ‘AP Inv. #’, M.DocDate as ‘Date’, M.CardName as

’Vendor Name’,M.NumAtCard as ‘Bill No. & Dt.’,L.ItemCode, L.Dscription,
(Select Sum(LineTotal) FROM PCH1 L Where L.DocEntry=M.DocEntry) as ‘Base

Amt.(Rs.)’,
(SELECT Sum(TaxSum) FROM PCH4 where statype=-90 and DocEntry=M.DocEntry)

as ‘ED (Rs.)’,
(SELECT Sum(TaxSum) FROM PCH4 where statype=7 and DocEntry=M.DocEntry) as

’ACD (Rs.)’,
(SELECT Sum(TaxSum) FROM PCH4 where statype=-60 and DocEntry=M.DocEntry)

as ‘EDCS (Rs.)’,
(SELECT Sum(TaxSum) FROM PCH4 where statype=-55 and DocEntry=M.DocEntry)

as ‘HECS (Rs.)’,
(SELECT Sum(TaxSum) FROM PCH4 where statype=1 and DocEntry=M.DocEntry) as

’ VAT (Rs.) ‘,
(SELECT Sum(TaxSum) FROM PCH4 where statype=4 and DocEntry=M.DocEntry) as

’ CST (Rs.) ‘,
(SELECT Sum(TaxSum) FROM PCH4 where statype=10 and DocEntry=M.DocEntry)

as ‘ CVD (Rs.) ‘,
(SELECT Sum(TaxSum) FROM PCH4 where statype=5 and DocEntry=M.DocEntry) as

’ Ser.Tax (Rs.) ‘,
(SELECT Sum(TaxSum) FROM PCH4 where statype=6 and DocEntry=M.DocEntry) as

’CS on Ser.Tax (Rs.)’,
(SELECT Sum(TaxSum) FROM PCH4 where statype=8 and DocEntry=M.DocEntry) as

’HECS_ST (Rs.)’,
(Select Sum(LineTotal) From PCH3 Q Where Q.DocEntry=M.DocEntry) AS

’Freight (Rs.)’,
M.WTSum AS ‘TDS (Rs.)’,
M.DocTotal as ‘Total (Rs.)’
FROM OPCH M LEFT OUTER JOIN PCH1 L on L.DocEntry=M.DocEntry
LEFT OUTER JOIN PCH4 T on T.DocEntry=L.DocEntry and L.LineNum=T.LineNum
LEFT OUTER JOIN PCH5 J ON M.DocEntry = J.AbsEntry
LEFT OUTER JOIN PCH3 Q ON M.DocEntry = Q.DocEntry
WHERE (M.DocDate >= ‘%0 ‘ AND M.DocDate GROUP BY
M.DocNum,M.DocDate,M.CardName,M.NumAtCard,M.DocEntry,M.DiscSum,M.WTSum,M.

DocTotal,L.ItemCode,L.Dscription
ORDER BY
M.DocNum,M.DocDate,M.CardName,M.NumAtCard,M.DocEntry,M.DiscSum,M.WTSum,M.

DocTotal

Above menioned SQL Scripts are required by most of the clients and Consultants.

Thanks,

New NetWeaver Information at SAP.com

Very Helpfull

User Rating: Be the first one !