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
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 <='[%1]'
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 <= ‘%1’)
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 <= ‘%1’)
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
