Inventory Transfer To A/R Invoice Trail

[narayanis]

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.

Regards

Pradnya S

[narayanis]

Experts, Help is very much needed.

The query is giving duplicates.

[Abdul]

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

[narayanis]

Tried. No Use.