Some useful SQL scripts

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.InQty<>0
 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 <='[%1]’

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 <br />FROM dbo.INV1 T0<br /> INNER JOIN dbo.OINV T1 ON T0.DocEntry = T1.DocEntry<br /> INNER JOIN dbo.OITM T2 ON T0.ItemCode = T2.ItemCode<br /> INNER JOIN dbo.OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod<br /> INNER JOIN dbo.OINM T4 ON T1.DocNum=T4.BASE_REF and T1.ObjType = </p><p>T4.TransType and T0.LineNum = T4.DocLineNum<br /> INNER JOIN dbo.OINM T5 ON T5.TransNum = (select MIN(TransNum) </p><p>FROM OINM <br />          </p><p> where ItemCode=T0.ItemCode<br />          </p><p> and Warehouse=T0.WhsCode<br />          </p><p> and TransNum<T4.TransNum <br />          </p><p> having SUM(InQty-OutQty)>0<br />          </p><p> )<br /> LEFT OUTER JOIN dbo.OPDN T6 ON T6.ObjType = T5.TransType and </p><p>T6.DocNum = T5.BASE_REF<br /> LEFT OUTER JOIN dbo.OPCH T7 ON T7.ObjType = T5.TransType and </p><p>T7.DocNum  = T5.BASE_REF<br />WHERE IsNULL(T2.ManBtchNum, ‘N’)=’N’ and IsNULL(T2.ManSerNum, ‘N’)=’N'<br />  and T1.DocNum >='[%0]’ and T1.DocNum <='[%1]'</p>h5. <br />Sales Register Report Query:

<p>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.</p><p><br />SELECT M.DocNum AS ‘AP Inv. #’, M.DocDate as ‘Date’, M.CardName as </p><p>’Vendor Name’,M.NumAtCard as ‘Bill No. & Dt.’,L.ItemCode, </p><p>L.Dscription,L.LineTotal as ‘Amount’,L.vatsum ,L.PriceBefDi as ‘Rate’,<br />(Select Sum(LineTotal) FROM INV1 L Where L.DocEntry=M.DocEntry) as ‘Base </p><p>Amt.(Rs.)’,<br />(SELECT Sum(TaxSum) FROM INV4 where statype=-90 and DocEntry=M.DocEntry) </p><p>as ‘BED (Rs.)’,<br />(SELECT Sum(TaxSum) FROM INV4 where statype=7 and DocEntry=M.DocEntry) as </p><p>’HSCess_New (Rs.)’,<br />(SELECT Sum(TaxSum) FROM INV4 where statype=-60 and DocEntry=M.DocEntry) </p><p>as ‘EDCS (Rs.)’,<br />(SELECT Sum(TaxSum) FROM INV4 where statype=-55 and DocEntry=M.DocEntry) </p><p>as ‘HECS (Rs.)’,<br />(SELECT Sum(TaxSum) FROM INV4 where statype=1 and DocEntry=M.DocEntry) as </p><p>’ VAT (Rs.) ‘,<br />(SELECT Sum(TaxSum) FROM INV4 where statype=4 and DocEntry=M.DocEntry) as </p><p>’ CST (Rs.) ‘,<br />(SELECT Sum(TaxSum) FROM INV4 where statype=-10 and DocEntry=M.DocEntry) </p><p>as ‘ CVD (Rs.) ‘,<br />(SELECT Sum(TaxSum) FROM INV4 where statype=5 and DocEntry=M.DocEntry) as </p><p>’ Ser.Tax (Rs.) ‘,<br />(SELECT Sum(TaxSum) FROM INV4 where statype=6 and DocEntry=M.DocEntry) as </p><p>’CS on Ser.Tax (Rs.)’,<br />(SELECT Sum(TaxSum) FROM INV4 where statype=4 and DocEntry=M.DocEntry) as </p><p>’HECS_ST (Rs.)’,<br />(Select Sum(LineTotal) From INV3 Q Where Q.DocEntry=M.DocEntry) AS </p><p>’Freight (Rs.)’, <br />M.WTSum AS ‘TDS (Rs.)’, <br />M.DocTotal as ‘Total (Rs.)'<br />FROM OINV M LEFT OUTER JOIN INV1 L on L.DocEntry=M.DocEntry <br />LEFT OUTER JOIN INV4 T on T.DocEntry=L.DocEntry and L.LineNum=T.LineNum <br />LEFT OUTER JOIN INV5 J ON M.DocEntry = J.AbsEntry<br />LEFT OUTER JOIN INV3 Q ON M.DocEntry = Q.DocEntry<br />WHERE (M.DocDate >= ‘%0’ AND M.DocDate <= ‘%1’) <br />GROUP BY <br />M.DocNum,M.DocDate,M.CardName,M.NumAtCard,M.DocEntry,M.DiscSum,M.WTSum,M.</p><p>DocTotal,L.ItemCode,L.Dscription,L.LineTotal,L.vatsum,L.PriceBefDi<br />ORDER BY<br />M.DocNum,M.DocDate,M.CardName,M.NumAtCard,M.DocEntry,M.DiscSum,M.WTSum,M.</p><p>DocTotal,L.LineTotal,L.vatsum,L.PriceBefDi</p>h5. Purchase Register Query

<p>Similar to Sales Register report ,Purchase Register report gives the detailed analysis of purchase with tax details in particular date range .</p><p>SELECT M.DocNum AS ‘AP Inv. #’, M.DocDate as ‘Date’, M.CardName as </p><p>’Vendor Name’,M.NumAtCard as ‘Bill No. & Dt.’,L.ItemCode, L.Dscription,<br />(Select Sum(LineTotal) FROM PCH1 L Where L.DocEntry=M.DocEntry) as ‘Base </p><p>Amt.(Rs.)’,<br />(SELECT Sum(TaxSum) FROM PCH4 where statype=-90 and DocEntry=M.DocEntry) </p><p>as ‘ED (Rs.)’,<br />(SELECT Sum(TaxSum) FROM PCH4 where statype=7 and DocEntry=M.DocEntry) as </p><p>’ACD (Rs.)’,<br />(SELECT Sum(TaxSum) FROM PCH4 where statype=-60 and DocEntry=M.DocEntry) </p><p>as ‘EDCS (Rs.)’,<br />(SELECT Sum(TaxSum) FROM PCH4 where statype=-55 and DocEntry=M.DocEntry) </p><p>as ‘HECS (Rs.)’,<br />(SELECT Sum(TaxSum) FROM PCH4 where statype=1 and DocEntry=M.DocEntry) as </p><p>’ VAT (Rs.) ‘,<br />(SELECT Sum(TaxSum) FROM PCH4 where statype=4 and DocEntry=M.DocEntry) as </p><p>’ CST (Rs.) ‘,<br />(SELECT Sum(TaxSum) FROM PCH4 where statype=10 and DocEntry=M.DocEntry) </p><p>as ‘ CVD (Rs.) ‘,<br />(SELECT Sum(TaxSum) FROM PCH4 where statype=5 and DocEntry=M.DocEntry) as </p><p>’ Ser.Tax (Rs.) ‘,<br />(SELECT Sum(TaxSum) FROM PCH4 where statype=6 and DocEntry=M.DocEntry) as </p><p>’CS on Ser.Tax (Rs.)’,<br />(SELECT Sum(TaxSum) FROM PCH4 where statype=8 and DocEntry=M.DocEntry) as </p><p>’HECS_ST (Rs.)’,<br />(Select Sum(LineTotal) From PCH3 Q Where Q.DocEntry=M.DocEntry) AS </p><p>’Freight (Rs.)’, <br />M.WTSum AS ‘TDS (Rs.)’, <br />M.DocTotal as ‘Total (Rs.)'<br />FROM OPCH M LEFT OUTER JOIN PCH1 L on L.DocEntry=M.DocEntry <br />LEFT OUTER JOIN PCH4 T on T.DocEntry=L.DocEntry and L.LineNum=T.LineNum <br />LEFT OUTER JOIN PCH5 J ON M.DocEntry = J.AbsEntry<br />LEFT OUTER JOIN PCH3 Q ON M.DocEntry = Q.DocEntry<br />WHERE (M.DocDate >= ‘%0 ‘ AND M.DocDate <= ‘%1’) <br />GROUP BY <br />M.DocNum,M.DocDate,M.CardName,M.NumAtCard,M.DocEntry,M.DiscSum,M.WTSum,M.</p><p>DocTotal,L.ItemCode,L.Dscription<br />ORDER BY<br />M.DocNum,M.DocDate,M.CardName,M.NumAtCard,M.DocEntry,M.DiscSum,M.WTSum,M.</p><p>DocTotal</p><p>Above menioned SQL Scripts are required by most of the clients and Consultants.</p><p>Thanks,</p>