Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

GL wise Stock

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
Not what you were looking for? View more on this topic or Ask a question