GL wise Stock

[Former Member]

Hi

I want to get GL Account wise Inventory stock and value.

so i take it from Warehouse Journal Table OIVL.

In OIVL table, i found the Account and its Value field which are used while JV  posted for that inventory Transaction.

These fields are such as VairanceAc, VarVal, PriceDifAc, PriceDiff, WipAct, WipVal, and so on.

But i am confusing on which value are used for the following Account.

1) InvntAct, 2) StockAct 3) TrnsfrAct 4) ClearAct 5) ExpCAcc

and which Account Field have to used for the following Value Field

1) SumStock 2) Allocation 3) Expenses 4) ExpAlloc.

I have created the following Query. Please check it

SELECT OIVL.InvntAct AS AcctCode, OIVL.SumStock AS Amt, OIVL.TransType, OIVL.CreatedBy, OIVL.DocDate, OIVL.ItemCode, (OIVL.InQty - OIVL.OutQty) AS Qty FROM OIVL WHERE OIVL.SumStock <> 0

UNION ALL

SELECT OIVL.TrnsfrAct, OIVL.Allocation, OIVL.TransType, OIVL.CreatedBy, OIVL.DocDate, OIVL.ItemCode, (OIVL.InQty - OIVL.OutQty) AS Qty FROM OIVL WHERE OIVL.Allocation <> 0  

UNION ALL

SELECT OIVL.VarianceAc, OIVL.VarVal, OIVL.TransType, OIVL.CreatedBy, OIVL.DocDate, OIVL.ItemCode, (OIVL.InQty - OIVL.OutQty) AS Qty FROM OIVL WHERE OIVL.VarVal <> 0

UNION ALL

SELECT OIVL.ExcRateAct, OIVL.ExchDiff, OIVL.TransType, OIVL.CreatedBy, OIVL.DocDate, OIVL.ItemCode, (OIVL.InQty - OIVL.OutQty) AS Qty FROM OIVL WHERE OIVL.ExchDiff <> 0  

UNION ALL

SELECT OIVL.PriceDifAc, OIVL.PriceDiff, OIVL.TransType, OIVL.CreatedBy, OIVL.DocDate, OIVL.ItemCode, (OIVL.InQty - OIVL.OutQty) AS Qty FROM OIVL WHERE OIVL.PriceDiff <> 0  

UNION ALL

SELECT OIVL.CostAct, OIVL.CogsVal, OIVL.TransType, OIVL.CreatedBy, OIVL.DocDate, OIVL.ItemCode, (OIVL.InQty - OIVL.OutQty) AS Qty FROM OIVL WHERE OIVL.CogsVal <> 0 

UNION ALL

SELECT OIVL.WipAct, OIVL.WipVal, OIVL.TransType, OIVL.CreatedBy, OIVL.DocDate, OIVL.ItemCode, (OIVL.InQty - OIVL.OutQty) AS Qty FROM OIVL WHERE OIVL.WipVal <> 0  

UNION ALL

SELECT OIVL.WipVarAcc, OIVL.WipVarVal, OIVL.TransType, OIVL.CreatedBy, OIVL.DocDate, OIVL.ItemCode, (OIVL.InQty - OIVL.OutQty) AS Qty FROM OIVL WHERE OIVL.WipVarVal <> 0

UNION ALL

SELECT OIVL.IOffIncAcc, OIVL.IOffIncVal, OIVL.TransType, OIVL.CreatedBy, OIVL.DocDate, OIVL.ItemCode, (OIVL.InQty - OIVL.OutQty) AS Qty FROM OIVL WHERE OIVL.IOffIncVal <> 0  

UNION ALL

SELECT OIVL.DOffDecAcc, OIVL.DOffDecVal, OIVL.TransType, OIVL.CreatedBy, OIVL.DocDate, OIVL.ItemCode, (OIVL.InQty - OIVL.OutQty) AS Qty FROM OIVL WHERE OIVL.DOffDecVal <> 0  

UNION ALL

SELECT OIVL.DecAcc, OIVL.DecVal, OIVL.TransType, OIVL.CreatedBy, OIVL.DocDate, OIVL.ItemCode, (OIVL.InQty - OIVL.OutQty) AS Qty FROM OIVL WHERE OIVL.DecVal <> 0

UNION ALL

SELECT OIVL.IncAct, OIVL.IncVal, OIVL.TransType, OIVL.CreatedBy, OIVL.DocDate, OIVL.ItemCode, (OIVL.InQty - OIVL.OutQty) AS Qty FROM OIVL WHERE OIVL.IncVal <> 0

UNION ALL

SELECT OIVL.NegStckAct, OIVL.NegInvAdjs, OIVL.TransType, OIVL.CreatedBy, OIVL.DocDate, OIVL.ItemCode, (OIVL.InQty - OIVL.OutQty) AS Qty FROM OIVL WHERE OIVL.NegInvAdjs <> 0  

UNION ALL

SELECT OIVL.PAOffAcc,    OIVL.PAOffVal, OIVL.TransType, OIVL.CreatedBy, OIVL.DocDate, OIVL.ItemCode, (OIVL.InQty - OIVL.OutQty) AS Qty FROM OIVL WHERE OIVL.PAOffVal <> 0

UNION ALL

SELECT OIVL.PAAcc, OIVL.PAVal, OIVL.TransType, OIVL.CreatedBy, OIVL.DocDate, OIVL.ItemCode, (OIVL.InQty - OIVL.OutQty) AS Qty FROM OIVL WHERE OIVL.PAVal <> 0

Regards

Narendra

[Former Member]

Hi Narendra,

Have you tried the inventory audit report as it has an option where you can run the report based on GL?

Thanks,

Joseph