eXact Price used in JE using Goods Issue

[former_member541807]

hello experts...

I have this issue need to be resolved. need your help..

Price list used in Goods Issue = last Evaluated Price
System Valuation Method = FIFO

im using Goods Issue, and when i check the price in IGE1, it does not tally when multiplied by the quantity when compare to the Journal Entry amount. where can i find the exact price that will tally on the amount in Journal Entry?

Please help what field should i use so that my total amount will be equal in the JE amount.

Looking forward for your expert opinions.

Thanks
Avelino Fidel

[msundararaja_perumal]

Hello,

You may try the below query to get the desired result.

SELECT A.BASE_REF as DocNum, A.DocDate,A.ItemCode ,A.OutQty as Qty,A.InvntAct as Account,
(SELECT AcctName From OACT Where A.InvntAct =FormatCode) as AcctName,
B.CalcPrice as Price,Abs(B.TransValue) as TransValue  FROM dbo.OIVL  A INNER JOIN IVL1 B ON A.TransSeq = B.TransSeq WHERE A.TransType =60

If you are using version below 9.0 then you may need to use OINM-Warehouse Journal table to retrieve your desired result.

Thanks.

[former_member541807]

thank you Hrvoje Dolenec for the response...

i have tried to use the IGE1.StockPrice * IGE1.Quantity but i not able to get the exact amount in JE... what i should have is the exact amount posted in JE, since what the value posted in JE is the one posted in GL account. to explain my side let me show you my screenshot (see image)

this is my query

SELECT 
T0.[DocNum], 
T0.[DocDate], 
T1.[ItemCode], 
T1.[Dscription], 
T1.[Quantity], 
T2.[AcctName],
T2.[FormatCode],
T0.[Comments]
FROM [dbo].[OIGE]  T0 INNER JOIN [dbo].[IGE1]  T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OACT T2 ON T1.[AcctCode] = T2.AcctCode 
WHERE T2.FormatCode = '[%0]' and T0.[DocDate] BETWEEN '[%1]' AND '[%2]'
Order by T0.DocDate

and this is the result window

Now here is the Query result

SO Number: 18168393

Amount: 50,893.44

Qty: 416

Price: 122.34

now lets proceed to Goods Issue Window (See image)

Good Issue total amount is : 50,897.60 ----- Note its not the same with my Query result using (IGE1.StockPrice * IGE1.Quantity)

and we will proceed to JE Window(See Image)

here is the JE WIndow..

this is the Amount i should need.

JE Amount : 50,892.86

to summarise here

Query Result amount: 50,893.44

Goods Issue Amount: 50,897.6

JE Amount: 50,892.86

the JE Amount is what i should really need. please do advice how will i achieve this thank you Very much..

looking forward for more valuable inputs.

Thanks

AvelinoFidel

[hdolenec]

You can ignore Total on Goods Issue because this is calculated as Quantity * Price. You need quantity * item cost.

As for difference between query and journal entry, I think it's because of rounding settings or decimal places settings (can't be sure without further investigation). Because query returns stock price 122,34 but it's actually 50.892,86/416=122,3386

[former_member541807]

hi Hrvoje,

i have it Query in my MS SQL Studio but is not on Rounding i think (see Image)

do you think is possible if i will use OINM?

[hdolenec]

Actually, it is a decimal places rounding issue. You probably had two decimal places active at the time this transaction was created, so the system rounded item cost to 122,34. The problem is, if you increase number of decimal places now, the system will only use that setting for new transactions. Previous transactions will have the same value but with more zeros. I would recommend that you always setup your database to use 5 decimal places for price, that way you won't have this kind of problems.

You could another table as you suggested (OIVL or OINM), but keep in mind that those table only contain data for inventory items. There is no data for non-inventory items and resources, in case you need to print issue for production.

[hdolenec]

Price list and price itself are relevant for goods receipt because they determine the value of received items. If you are doing goods issue, system calculates inventory value depending on valuation method (FIFO in your case) and that value is not connected to price list. This value is calculated after adding the document and can be seen in field "Item Cost". If you need this value in query, than you need to use IGE1.StockPrice (multiply by quantity to get total value).