Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

How to get Receipt & Issue Value sumaries of Item from Warehouse Journal (OINM) ?

How to get query Inventory Balance Summary as below from Warehouse Journal (OINM) because i found difference value compare with Trial Balance they are Receipt Value on Landed Cost and Issue Value on Inventory Revaluation such as my queries below . I'm not sure it correct or not ? Please help. OINM keep all transaction ?

Item CodeItem DescriptionOB-QtyOB-ValueReceipt-QtyReceipt-ValueIssue-QtyIssue-ValueCls-QtyCls-Value
AAADescAAA55.000130.660450.0001,082.88039.00095.130466.0001,118.410
BBBDescBBB137.0001,808.5106.00080.58012.000161.160131.0001,727.930
Total192.0001,939.170456.0001,163.46051.000252.290597.0002,846.340

Select

--a.[Group],

--a.[ItmsGrpnam],

a.ItemCode,

a.[Name],

a.[Whse],

SUM(a.[OB-Qty]) [OB-Qty],

SUM(a.[OB-Value]) [OB-Value],

sum(a.[Receipt-Qty]) [Receipt-Qty],

sum(a.[Receipt-Value]) [Receipt-Value],

sum(a.[Issue-Qty]) [Issue-Qty],

sum(a.[Issue-Value]) [Issue-Value],

SUM(a.[Cls-Qty]) [Cls-Qty],

--Max(a.[Avg-Cost]) [Avg-Cost],

SUM(a.[Cls-Value]) [Cls-Value],

a.[Inventory Unit]

From

(select

--SUBSTRING(T0.[ItemCode],1,2) [Group],

--T2.[ItmsGrpNam],

T0.[ItemCode],

max(T1.[ItemName]) [Name],

max(T0.[Warehouse]) [Whse],

(sum(isnull(t0.inqty,0)) - sum(isnull(t0.outqty,0)) ) [OB-Qty],

sum(isnull(t0.transvalue,0)) [OB-Value],

0 [Receipt-Qty],

0 [Receipt-Value],

0 [Issue-Qty],

0 [Issue-Value],

0 [Cls-Qty],

--max(T0.[CalcPrice]) AS 'Avg-Cost',

0 [Cls-Value],

T1.[InvntryUom] AS 'Inventory Unit',

T1.[BuyUnitMsr] AS 'Purchase Unit',

T1.[SalUnitMsr] AS 'Sales Unit'

FROM   

OINM T0

INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode 

INNER JOIN OITB T2 ON T1.ItmsGrpCod = T2.ItmsGrpCod

WHERE

T0.[DocDate] < '[%0]'

AND SUBSTRING(T0.[ItemCode],1,2) <> 'XX'

GROUP BY

--T2.[ItmsGrpNam],

T0.[ItemCode],

T1.[ItemName],

T0.[Warehouse],

T1.[InvntryUom] ,

T1.[BuyUnitMsr] ,

T1.[SalUnitMsr]

--T1.[AvgPrice]

UNION ALL

select

--SUBSTRING(T0.[ItemCode],1,2) [Group],

--T2.[ItmsGrpNam],

T0.[ItemCode],

max(T1.[ItemName]) [Name],

max(T0.[Warehouse]) [Whse],

0 [OB-Qty],

0 [OB-Value],

(sum(isnull(t0.inqty,0))) [Receipt-Qty],

SUM(CASE WHEN T0.[InQty] <> 0 THEN T0.[TransValue]

when T0.TransType = '69' then T0.TransValue

ELSE 0 END) [Receipt-Value],

(sum(isnull(t0.outqty,0))) [Issue-Qty],

SUM(CASE WHEN T0.[OutQty] <> 0 THEN T0.[TransValue]*-1

when T0.TransType = '162' then T0.TransValue*-1

ELSE 0 END) [Issue-Value],

0 [Cls-Qty],

--max(T0.[CalcPrice]) AS 'Avg-Cost',

0 [Cls-Value],

T1.[InvntryUom] AS 'Inventory Unit',

T1.[BuyUnitMsr] AS 'Purchase Unit',

T1.[SalUnitMsr] AS 'Sales Unit'

FROM   

OINM T0

INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode 

INNER JOIN OITB T2 ON T1.ItmsGrpCod = T2.ItmsGrpCod

WHERE

T0.[DocDate] >= '[%0]' and T0.[DocDate] <= '[%1]'

AND SUBSTRING(T0.[ItemCode],1,2) <> 'XX'

GROUP BY

--T2.[ItmsGrpNam],

T0.[ItemCode],

T1.[ItemName],

T0.[Warehouse],

T1.[InvntryUom] ,

T1.[BuyUnitMsr] ,

T1.[SalUnitMsr]

--T1.[AvgPrice]

UNION ALL

select

--SUBSTRING(T0.[ItemCode],1,2) [Group],

--T2.[ItmsGrpNam],

T0.[ItemCode],

max(T1.[ItemName]) [Name],

max(T0.[Warehouse]) [Whse],

0 [OB-Qty],

0 [OB-Value],

0 [Receipt-Qty],

0 [Receipt-Value],

0 [Issue-Qty],

0 [Issue-Value],

(sum(isnull(t0.inqty,0)) - sum(isnull(t0.outqty,0)) ) [Cls-Qty],

--max(T0.[CalcPrice]) AS 'Avg-Cost',

sum(isnull(t0.transvalue,0))  [Cls-Value],

T1.[InvntryUom] AS 'Inventory Unit',

T1.[BuyUnitMsr] AS 'Purchase Unit',

T1.[SalUnitMsr] AS 'Sales Unit'

FROM   

OINM T0

INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode 

INNER JOIN OITB T2 ON T1.ItmsGrpCod = T2.ItmsGrpCod

WHERE

T0.[DocDate] <= '[%1]'

AND SUBSTRING(T0.[ItemCode],1,2) <> 'XX'

GROUP BY

--T2.[ItmsGrpNam],

T0.[ItemCode],

T1.[ItemName],

T0.[Warehouse],

T1.[InvntryUom] ,

T1.[BuyUnitMsr] ,

T1.[SalUnitMsr]

--T1.[AvgPrice])a

Group By

a.ItemCode,

a.[Name],

a.[Whse],

--a.[Group],

--a.[ItmsGrpnam],

a.[Inventory Unit]

replied

Hi

first, you have to find all stock account (if you are managing your stock by warehouse, check the warehouse configuration; if you are managing your stock by ítem groups, check the ítem groups configuration).

When you have the accounts list, you can run:

select T0.TransId, T0.RefDate from OJDT T0 inner join JDT1 T1 on T0.transid = T1.transid where T0.transtype = 30 and T1.Account in (yourlist)

This query shows all manual journal entries using stock accounts.

Kind regards,

Agustín Marcos Cividanes

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question