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

[Former Member]

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 Code	Item Description	OB-Qty	OB-Value	Receipt-Qty	Receipt-Value	Issue-Qty	Issue-Value	Cls-Qty	Cls-Value
AAA	DescAAA	55.000	130.660	450.000	1,082.880	39.000	95.130	466.000	1,118.410
BBB	DescBBB	137.000	1,808.510	6.000	80.580	12.000	161.160	131.000	1,727.930
Total		192.000	1,939.170	456.000	1,163.460	51.000	252.290	597.000	2,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]

[Former Member · Accepted solution]

Please Help

[agustin_marcoscividanes · Accepted solution]

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

[Former Member · Accepted solution]

Thankyou so much

[agustin_marcoscividanes]

Hi

I would try to find manual journal entries using stock accounts.

If you have at least one manual journal entry you have differences between your inventory reports and financial report.

Kind regards.

Agustín Marcos Cividanes