SAP B1: Sales and Returns in one query

[egeorge]

Hello,

I'm a new in SAP B1.

Have a task to show the Sales and Returns for the period by BP in one query and after to create the Crystal Report based on data.

ORDR and RDR1 - Sales

ORDN and RDN1 - Returns.

Can you please advise how can I join them? Thanks in advance.

[kothandaraman_nagarajan]

Hi,

Your query should be based on Delivery and Return, not from sales order and Return.

Try this query,

SELECT T0.[DocNum], T0.[DocDate], T1.[ItemCode], T1.[Dscription], T1.[Quantity] FROM ODLN T0 INNER JOIN DLN1 T1 ON T0.[DocEntry] = T1.[DocEntry] WHERE T0.[CardCode] = [%0] and T0.[DocDate] between [%1] and [%2]

UNION ALL

SELECT T0.[DocNum], T0.[DocDate], T1.[ItemCode], T1.[Dscription], -1* T1.[Quantity] FROM ORDN T0 INNER JOIN RDN1 T1 ON T0.[DocEntry] = T1.[DocEntry] WHERE T0.[CardCode] = [%0] and T0.[DocDate] between [%1] and [%2]

Regards,

Nagarajan

[former_member11696]

Hi Igor,

Business One has their own Incident Portal, I suggest you log one there. B1 Rep's don't come here and it's an OEM version of standard CR which most people don't know the B1 Databases.

Don

[egeorge]

Hi Don.

It's not an incident.
But if you could provide me a link to the SAP Incident Portal, it would be great.

Igor.