Skip to Content

Inventory Transfer To A/R Invoice Trail

Apr 14 at 10:52 AM


avatar image
Former Member

Dear Experts,

We are creating A/R Invoices by copying Inventory Transfer to A/R Invoice in case of inter state transfer.

Now, management wants to have the primary OFR report which will give the transfer V/s invoiced data.

Following query is used but, giving an incorrect result as we are unable to locate the common fields between the two documents.

SELECT dbo.OWTR.DocEntry, dbo.OWTR.DocNum, dbo.OWTR.DocDate, dbo.OWTR.Filler, dbo.OWTR.ToWhsCode, dbo.WTR1.ItemCode, dbo.WTR1.Dscription, dbo.WTR1.Quantity, dbo.WTR1.U_DFMRP, dbo.OINV.DocEntry AS Inventry, dbo.OINV.DocNum AS InvoiceNo, dbo.OINV.DocDate AS InvoiceDt, dbo.OINV.CardName, dbo.INV1.ItemCode AS InvoiceItm, dbo.INV1.Dscription AS InvoiceDsc, dbo.INV1.Quantity AS InvoiceQty, dbo.INV1.WhsCode, dbo.INV1.DropShip, dbo.INV1.BaseDocNum FROM dbo.OINV INNER JOIN dbo.INV1 ON dbo.OINV.DocEntry = dbo.INV1.DocEntry INNER JOIN dbo.OWTR INNER JOIN dbo.WTR1 ON dbo.OWTR.DocEntry = dbo.WTR1.DocEntry ON dbo.INV1.ItemCode = dbo.WTR1.ItemCode AND dbo.INV1.BaseDocNum = dbo.OWTR.DocNum WHERE (dbo.INV1.DropShip = 'Y') AND (dbo.OWTR.DocDate >= CONVERT(DATETIME, '2018-04-01 00:00:00', 102)) order by owtr.docnum

Your help is very much needed.


Pradnya S

10 |10000 characters needed characters left characters exceeded
Former Member

Experts, Help is very much needed.

The query is giving duplicates.


Try to ad distinct after select and before fields like Select Distinct OINV.Cardcode from OINV

Former Member

Tried. No Use.

* Please Login or Register to Answer, Follow or Comment.

0 Answers