Offered Amount Quotations against orders

[Former Member]

Hi,

I want to see how much we offer sorted by article group against what is really sold to the customer.

I'm using this query :

SELECT OCRD.CardCode, OCRD.CardName, SUM(INV1.Price * INV1.Quantity) AS Turnover, OITB.ItmsGrpNam, OCRG.GroupName, OITM.ItemCode, OITM.ItemName, OINV.DocDate

FROM  INV1
INNER JOIN OITM ON INV1.ItemCode = OITM.ItemCode
INNER JOIN OITB ON OITB.ItmsGrpCod = OITM.ItmsGrpCod
INNER JOIN OINV ON OINV.DocEntry = INV1.DocEntry
INNER JOIN OCRD ON OINV.CardCode = OCRD.CardCode
INNER JOIN OCRG ON OCRD.GroupCode = OCRG.GroupCode

GROUP BY OCRD.CardCode, OCRD.CardName, OITB.ItmsGrpNam, OCRG.GroupName, OITM.ItemCode, OITM.ItemName, OINV.DocDate

Now I need the values of (OQUT, QUT1.OpenSum) to join in this query but i'm getting always an error

Can somebody help me out, or point me to the right direction ?

Thanks in advance.

[KennedyT21]

Kindly confirm if this is resolved,  If your thread is closed then close the thread with correct or helpful answers.   Cheers!!!

[KennedyT21]

Hi

If your thread is closed then close the thread with correct or helpful answers.

Cheers!!!

[KennedyT21]

Hi Ramon Duunk....

Try this with the basic linking

Select II.Docnum Invoice,I.ItemCode,

  QQ.Docnum 'Quotation',Q.quantity,

  OO.DocNum 'Order',O.Quantity,

  DD.DocNum Delivery,D.Quantity,

  I.Quantity,I.LineTotal

From OINV II inner join INV1 I On I.DocEntry=II.DocEntry

   left outer join DLN1 D on I.BaseEntry=D.DocEntry and I.BaseType=15 and I.BaseLine=D.LineNum

left outer join ODLN DD on DD.DocEntry=D.DocEntry

   left outer join RDR1 O on (I.BaseEntry=O.DocEntry and I.BaseType=17 and I.BaseLine=O.LineNum)

     or (D.BaseEntry=O.DocEntry and D.BaseType=17 and D.BaseLine=O.LineNum)

left outer join ORDR OO on OO.DocEntry=O.DocEntry

   left outer join QUT1 Q on (O.BaseEntry=Q.DocEntry and O.BaseType=23 and O.BaseLine=Q.LineNum)

     or (I.BaseEntry=Q.DocEntry and I.BaseType=23 and I.BaseLine=Q.LineNum)

     or (D.BaseEntry=Q.DocEntry and D.BaseType=23 and D.BaseLine=Q.LineNum)

left outer join OQUT QQ on QQ.DocEntry=Q.DocEntry

Hope helpful

Regards

kennedy

[roberta_caterino]

Hi Kennedy,

my customer wants to know how many quotations and relative order, delivery and invoice for each customer...

Your query first have invoice...

Can you help me?

Thanks

Roberta

[KennedyT21]

Hi Roberta,

In the above query try the reverse way...

Regards

[kothandaraman_nagarajan]

Hi,

Please do not post multiple thread on same topic and will be answered in the main forum.

Thanks & Regards,

Nagarajan