cancel
Showing results for 
Search instead for 
Did you mean: 

Query For Sales

Former Member
0 Kudos

Hai ,

I am working on a Query To know the Total Sales Ordered Quantity,Ordered Value,Delivered Quantitiy,Delivered Value,Pending Qty and Value(Ordered - Delivered), and Crediit Note Quantity ,Credit Note Value,Total Stock in Particular Warehouse,Warehouse No.

I am Struck to join all these Tables (Sales Order,A/R Delivery,A/R Invoice,A/R Credit Note) against Each Item in the given Date Range.

Regards,

Vamsi.

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi Vamsi,

It is better get the same info from multiple queries instead of one. Links will probably not work for all of them.

Thanks,

Gordon

Former Member
0 Kudos

Hi Gordon,

I tried the below query and almost i got the result but not succedded in fetching the result from ORIN and RIN1 Tables.

The scenario is there will be some invoices canclled by passing a credit note and i want those details in this query in seperate column.

and some sale orders will be cancelled by using right click and that should not be reflected in the report .


SELECT T1.DOcnum,t1.cardname, t0.itemcode,T0.QUANTITY 'O Qty', 
             T0.Dscription 'Item Description',
 t3.docnum,t3.cardname,t2.itemcode,t2.quantity,
t5.docnum,t5.cardname,t4.itemcode,t4.quantity,
t8.docnum,t8.cardname,t7.itemcode,t7.quantity
FROM DBO.rdr1 T0
INNER JOIN DBO.ORdr T1 ON T1.DOCENTRY = T0.DOCENTRY
LEFT JOIN DBO.dln1 T2 ON T2.BASEENTRY = T0.DOCENTRY AND T2.BaseLine = T0.LineNum
LEFT JOIN DBO.OdlN T3 ON T2.DOCENTRY = T3.DOCENTRY 
left join dbo.inv1 t4 on t4.baseentry = t2.docentry and t4.baseline = t2.linenum
left join dbo.oinv t5 on t4.docentry = t5.docentry
left join dbo.rin1 t7 on t7.baseentry = t4.docentry and t7.baseline =t4.linenum
left join dbo.orin t8 on t7.docentry = t8.docentry

inner join oitm t6 on t0.itemcode =t6.itemcode 

WHERE T1.DocDate between '[%0]' and '[%1]'

Former Member
0 Kudos

Try this:


SELECT T1.DOcnum 'SO Number',t1.cardname, t0.itemcode,T6.WhsCode,T6.Onhand,T0.QUANTITY 'O Qty', 
             T0.Dscription 'Item Description',
t3.docnum 'Delivery#',t3.cardname,t2.quantity,
t5.docnum 'Invoice#',t5.cardname,t4.quantity,
t8.docnum 'Credit Memo#',t8.cardname,-t7.quantity
FROM DBO.rdr1 T0
INNER JOIN DBO.OITW T6 ON T6.ItemCode = T0.ItemCode AND T6.WhsCOde = T0.WhsCode
INNER JOIN DBO.ORdr T1 ON T1.DOCENTRY = T0.DOCENTRY
LEFT JOIN DBO.dln1 T2 ON T2.BASEENTRY = T0.DOCENTRY AND T2.BaseLine = T0.LineNum
LEFT JOIN DBO.OdlN T3 ON T2.DOCENTRY = T3.DOCENTRY 
left join dbo.inv1 t4 on t4.baseentry = t2.docentry and t4.baseline = t2.linenum
left join dbo.oinv t5 on t4.docentry = t5.docentry
left join dbo.rin1 t7 on t7.baseentry = t4.docentry and t7.baseline =t4.linenum
left join dbo.orin t8 on t7.docentry = t8.docentry 
WHERE T1.DocDate between '[%0]' and '[%1]'

Your link is no problem. However, I doubt you do not have any links for those credit memos so that they won't show up.