cancel
Showing results for 
Search instead for 
Did you mean: 

SAP Business One sales query

millicentdark
Contributor
0 Kudos

Dear Experts,

I will like a query with a join on sales order, sales delivery and sales invoice.

Customer can prepare a sales order and then copies it to sales delivery and finally moves it to sales invoice. The customer can at the same time prepare sales order and finally move it to sales invoice.

I want a query to handle both scenario in the image below;

Regards,

Justice Doku

Accepted Solutions (1)

Accepted Solutions (1)

kothandaraman_nagarajan
Active Contributor

Hi,

Please check this thread https://archive.sap.com/discussions/thread/3583628,

Regards.

Nagarajan

Answers (2)

Answers (2)

agustin_marcoscividanes
Active Contributor
0 Kudos

Hi

you have to write two SQL sentences: the first must link the sales orders, deliveries and invoices tables; the second, the sales orders and invoices tables.

Use UNION ALL to obtain your final results.

Kind regards

Agustín

millicentdark
Contributor
0 Kudos

I have done that but there is a duplication.

Please see below;

select DR.DocNum , DR.DocDate , R1.ItemCode , R1.Quantity , R1.LineStatus ,dr.DocStatus,CAST ('' AS VARCHAR(500)) AS 'LN.DocEntry',NV.DocEntry ,NV.DocNum ,NV.DocDate ,V1.ItemCode , V1.Quantity ,V1.Price ,nv.DiscPrcnt ,NV.TotalExpFC ,V1.LineStatus,NV.DocStatus from ORDR DR inner join RDR1 R1 on DR.DocEntry=R1.DocEntry left join INV1 V1 on V1.BaseEntry=R1.DocEntry and V1.BaseLine=R1.LineNum and R1.Itemcode=V1.Itemcode left join OINV NV on NV.DocEntry=V1.DocEntry where DR.DocNum = 1228

UNION

select DR.DocNum , DR.DocDate , R1.ItemCode , R1.Quantity , R1.LineStatus , dr.DocStatus , LN.DocEntry , NV.DocEntry ,NV.DocNum ,NV.DocDate ,V1.ItemCode , V1.Quantity ,V1.Price ,nv.DiscPrcnt ,NV.TotalExpFC ,V1.LineStatus,NV.DocStatus from ORDR DR inner join RDR1 R1 on DR.DocEntry=R1.DocEntry left join DLN1 N1 on N1.BaseEntry=R1.DocEntry and R1.LineNum=N1.BaseLine and R1.Itemcode=N1.Itemcode --left join QUT1 T1 on T1.DocEntry=R1.BaseEntry and T1.ItemCode=R1.ItemCode and T1.Linenum=R1.BaseLine left join ODLN LN on LN.DocEntry=N1.DocEntry left join INV1 V1 on V1.BaseEntry=N1.DocEntry and V1.BaseLine=N1.LineNum and N1.Itemcode=V1.Itemcode left join OINV NV on NV.DocEntry=V1.DocEntry where DR.DocNum = 1228 GROUP BY DR.DocNum ,DR.DocDate ,R1.ItemCode ,R1.Quantity ,R1.LineStatus ,dr.DocStatus ,LN.DocEntry ,NV.DocEntry ,NV.DocNum ,NV.DocDate ,V1.ItemCode , V1.Quantity ,V1.Price ,nv.DiscPrcnt ,NV.TotalExpFC ,V1.LineStatus,NV.DocStatus

agustin_marcoscividanes
Active Contributor
0 Kudos
Hi

use inner join, not left join.

Kind regards

Agustín

0 Kudos

Hi Justice,

Could you check this query ? Could you edit this ? I hope it works

select

T1.DocEntry ,

DR.DocNum ,

DR.DocDate ,

R1.ItemCode ,

R1.Quantity ,

R1.LineStatus ,

dr.DocStatus ,

LN.DocEntry ,

LN.DocNum,

LN.DocDate ,

N1.ItemCode ,

N1.Quantity ,

N1.LineStatus ,

LN.DocStatus , NV.DocEntry ,NV.DocNum ,NV.DocDate ,V1.ItemCode , V1.Quantity ,V1.Price ,nv.DiscPrcnt ,NV.TotalExpFC ,V1.LineStatus,NV.DocStatus from ORDR DR inner join RDR1 R1 on DR.DocEntry=R1.DocEntry left join DLN1 N1 on N1.BaseEntry=R1.DocEntry and R1.LineNum=N1.BaseLine and R1.Itemcode=N1.Itemcode left join QUT1 T1 on T1.DocEntry=R1.BaseEntry and T1.ItemCode=R1.ItemCode and T1.Linenum=R1.BaseLine left join ODLN LN on LN.DocEntry=N1.DocEntry left join INV1 V1 on V1.BaseEntry=N1.DocEntry and V1.BaseLine=N1.LineNum and N1.Itemcode=V1.Itemcode left join OINV NV on NV.DocEntry=V1.DocEntry where (DR.DocDate between [%0] and [%1] )

for browse

millicentdark
Contributor
0 Kudos

Helpful but did not work with my requirement.

Any other help?