Skip to Content
0
Former Member
Jan 21, 2015 at 06:22 AM

GL wise Stock

88 Views

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