query about inventory posting

[Former Member]

Hi experts,

How can i link the OIQR and IQR1 table with OJDT,JDT1,OINM table?

Since I want to get the cal price at OINM table.

What do i need to add to get the calcprice of the item according to their Doc Date (stock posting) in OIQR

select OIGR.Docdate,IGR1.WhsCode,WhsName,IQR1.ItemCode,ItemName,

OnHandBef as Instock,Quantity as different, OINM.Calcpric*quantity as COGS

FROM dbo.OIQR

INNER JOIN dbo.IQR1 ON dbo.OIQR.DocEntry = dbo.IQR1.DocEntry

inner join dbo.OWHS on dbo.OWHS.whscode = dbo.IQR1.WhsCode

inner join dbo.OINM on dbo.OINM.TransType= dbo.OIQR.ObjType

and dbo.OINM.DocDate= dbo.OIQR.DocDate

and dbo.OINM.CreateDate = dbo.OIQR.CreateDate

order by WhsCode, DocDate,IQR1.ItemCode

[kothandaraman_nagarajan]

Hi,

OIQR and IQR tales are related to Inventory Stock Posting (Reconcile) and Inventory Stock Posting (Reconcile) Lines.

I am not sure why you want these table with OINM table?

Thanks & Regards,

Nagarajan

[Former Member]

Thanks for your reply.

In fact, i want to get the inventory posting record for every item because when i run the inventory audit report, I can only get the total amount of the inventory posting record. I want to have the line detail of the inventory posting record.

Then I try to run the query from OIQR and IQR1, i found that the price from IQR1 is different from the audit report because i use last evaluated price for doing the inventory posting. And the audit report would show the CALCPRICE. I want to match the price of the audit report.

So that i need to get the CALCPRICE from OINM table.

It is possible for me to link the OIQR to OINM table?

Many thanks

[kothandaraman_nagarajan]

Hi,

Try this query:

SELECT T0.[DocDate], T0.[BASE_REF], T0.[Warehouse], T0.[InQty], T0.[OutQty], T0.[CalcPrice] FROM OINM T0 WHERE T0.[ItemCode]  = [%0]

Thanks,

Nagarajan