Skip to Content
0
Former Member
Jan 19, 2018 at 01:48 PM

Relation between ODLN, ORDN, ORIN

215 Views

I have created a query that shows all customers deliveries (ODLN), Credit Memos (ORIN), Returns (ORDN) transactions. Please find query below:

Select dbo.ODLN.DocDate, dbo.ODLN.CANCELED, dbo.ODLN.CardName, dbo.DLN1.ItemCode, dbo.DLN1.Dscription, dbo.DLN1.Quantity, dbo.DLN1.LineTotal

From dbo.ODLN INNER JOIN DLN1 ON dbo.ODLN.DocEntry = dbo.DLN1.DocEntry

WHERE (CONVERT(date, dbo.ODLN.DocDate) BETWEEN {?FromDate} AND {?ToDate}) AND (dbo.ODLN.CANCELED = 'N')

UNION

Select dbo.ORDN.DocDate, dbo.ORDN.CANCELED, dbo.ORDN.CardName, dbo.RDN1.ItemCode, dbo.RDN1.Dscription, -dbo.RDN1.Quantity, dbo.RDN1.LineTotal

From dbo.ORDN INNER JOIN RDN1 ON dbo.ORDN.DocEntry = dbo.RDN1.DocEntry

WHERE (CONVERT(date, dbo.ORDN.DocDate) BETWEEN {?FromDate} AND {?ToDate}) AND (dbo.ORDN.CANCELED = 'N')

UNION

Select dbo.ORIN.DocDate, dbo.ORIN.CANCELED, dbo.ORIN.CardName, dbo.RIN1.ItemCode, dbo.RIN1.Dscription, -dbo.RIN1.Quantity, dbo.RIN1.LineTotal

From dbo.ORIN INNER JOIN RIN1 ON dbo.ORIN.DocEntry = dbo.RIN1.DocEntry

WHERE (CONVERT(date, dbo.ORIN.DocDate) BETWEEN {?FromDate} AND {?ToDate}) AND (dbo.ORIN.CANCELED = 'N')

But many delivery transactions did not appear. After an investigation I found the following: If we have two different delivery transactions, in the same date, with the same product, and the same quantity for one customer, only ONE of the transactions is appearing in the report. However we need both the appear.To be more clear show the example below:

Actual Problem:

Customer Name Item Code Quantity (Kg) Delivery Number Customer X Item001 10000 161801167 Customer Y Item002 5000 161801168

Solution needed:

Customer Name Item Code Quantity (Kg) Delivery Number Customer X Item001 10000 161801167 Customer X Item001 10000 161801200 Customer Y Item002 5000 161801168 Customer Y Item002 5000 161801191

Can somebody give me hints to help?

Thanks