Getting Opening Balance Inventory audit report

[sburgos]

Greetings, I would like to ask if there is any table, query or any other way that I can extract the information of the Opening Balance and the Cumulative Quantity to show it in a report.

I am creating a report for my customer in which many of the information is almost a copy from the Inventory Audit Report, however, he wants to show the result of the cumulative quantity instead of the value in the "Cantidad (Kilos)" in the Crystal Reports

The thing is, I was able to get the difference between those values (7106.74 - 3411 = 3695.74 which is that appears in the result above) but I want to show those two values and I am unable to get them

After checking some other questions in the forum, it says that table OINM (or probably view since this customer is using SAP 9.2 PL07) has all the information, which is fine and it gives me many of the results I need except those two values. My query is the following:

SELECT T0."ItemCode",
T0."Dscription",
ISNULL(SUM(T0."InQty"),0) AS TotalInQty,
ISNULL(SUM(T0."OutQty"),0) AS TotalOutQty,
ISNULL(SUM(T0."InQty") - SUM(T0."OutQty"),0) AS TotalQty,
T1."AvgPrice"
FROM OINM T0 INNER JOIN OITM T1 ON T0."ItemCode" = T1."ItemCode"
WHERE T1."ItmsGrpCod" = 100
AND T0."ItemCode" LIKE 'MPD%'
AND T0."CreateDate" >= {?BeginDate} --Parameter requested, initial date
AND T0."CreateDate" <= {?EndDate} --Parameter requested, final date
GROUP BY T0."ItemCode",
T0."Dscription",
T1."AvgPrice"
ORDER BY T0."ItemCode"

If there is anything I am missing, please let me know, my apologies if the content is in spanish but I am working on a customer test environment

Kind regards and thanks in advance

[former_member312729 · Accepted solution]

Hi ,

Try the below query works for SQL db and include the double quote if required

declare  @fromdate as date
Set @fromdate =/* select min(T0.[Docate]) from [dbo].[OINM]  T0 where T0.[Docdate]=*/ [%0]
declare  @ToDate as date
set @ToDate = /* select min(T0.[Docate]) from [dbo].[OINM]  T0 where T0.[Docdate]=*/ [%1]
SELECT T0.ItemCode,
T0.Dscription,
(SELECT sum(A.[InQty])-sum(A.[OutQty])FROM OINM A where A.ItemCode=T0.ItemCode and A.DocDate <@fromdate)OPStock,
ISNULL(SUM(T0.InQty),0) AS TotalInQty,
ISNULL(SUM(T0.OutQty),0) AS TotalOutQty,
ISNULL(SUM(T0.InQty) - SUM(T0.OutQty),0) AS TotalQty,
T1.AvgPrice ,
(SELECT sum(A.[InQty])-sum(A.[OutQty])FROM OINM A where A.ItemCode=T0.ItemCode and A.DocDate <=@ToDate)ClosingStock
FROM OINM T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode
WHERE  T0.DocDate >= @fromdate
AND T0.DocDate <=@ToDate  and T1.ItmsGrpCod = 100AND T0.ItemCode LIKE 'MPD%'
GROUP BY T0.ItemCode,
T0.Dscription,
T1.AvgPrice
ORDER BY T0.ItemCode

Regards:

Balaji.S

[sburgos · Accepted solution]

Thank you very much Balaji, your query was very helpful on my report, only if I may bother you a little bit, is it possible that you can add the final value of the cumulative quantity and the final average price of the item? To the previous query I added the following field:

(SELECT ISNULL(SUM(A."InQty") - SUM(A."OutQty"),0) FROM OINM A WHERE A."ItemCode"=T0."ItemCode" AND A."DocDate" <= @EndDate) * T1."AvgPrice" AS CumQty, --Cumulative Quantity

AVG(T0."CalcPrice"), --Final average price of all items

However, the result differs by many decimals which, in turn, makes the final sum to differ by thousands (approximately 6000) and the average price doesn't give me the same amount

As for the rest of my requirements (and customer as well) this did a great job. Kind regards

Santiago Burgos

[former_member312729 · Accepted solution]

Hi ,

You can use the Tranvalue from OINM

declare  @fromdate as date
Set @fromdate =/* select min(T0.[Docate]) from [dbo].[OINM]  T0 where T0.[Docdate]=*/ [%0]
declare  @ToDate as date
set @ToDate = /* select min(T0.[Docate]) from [dbo].[OINM]  T0 where T0.[Docdate]=*/ [%1]
SELECT T0.ItemCode,
T0.Dscription,
(SELECT sum(A.[InQty])-sum(A.[OutQty])FROM OINM A where A.ItemCode=T0.ItemCode and A.DocDate <@fromdate)OPStock,
(SELECT sum(A.[TransValue])FROM OINM A where A.ItemCode=T0.ItemCode and A.DocDate <@fromdate)OPStock Value,
ISNULL(SUM(T0.InQty),0) AS TotalInQty,
ISNULL(SUM(T0.OutQty),0) AS TotalOutQty,
ISNULL(SUM(T0.InQty) - SUM(T0.OutQty),0) AS TotalQty,
(SELECT sum(A.[InQty])-sum(A.[OutQty])FROM OINM A where A.ItemCode=T0.ItemCode and A.DocDate <=@ToDate)ClosingStock,
(SELECT sum(A.[TransValue])FROM OINM A where A.ItemCode=T0.ItemCode and A.DocDate <=@Todate) ClosingStockValue
FROM OINM T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode
WHERE  T0.DocDate >= @fromdate
AND T0.DocDate <=@ToDate  and T1.ItmsGrpCod = 100AND T0.ItemCode LIKE 'MPD%'
GROUP BY T0.ItemCode,
T0.Dscription

ORDER BY T0.ItemCode

[sburgos]

My apologies for not answering you before, your query helped me with my requirement perfectly