Skip to Content
avatar image
Former Member

Relation between ODLN, ORDN, ORIN

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

0 Answers