Skip to Content
0
Former Member
Jun 10, 2015 at 08:34 AM

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

29 Views

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]