Sales Analisys group by User define tables.

[Former Member]

I need to create a Sales Analisys report but group by some UDF. I created a simply query that adds the quantities in INV1 (with DocType='I') and substract the quantities from RIN1 . But I see that the quantities dont match with the sales analisys from SBO. What else should I use ? Is there another table ?

[Former Member · Accepted solution]

You may post your query here for others to check.

Thanks,

Gordon

[Former Member · Accepted solution]

 
((
select t0.objtype,t0.docnum,t0.docdate,t1.itemcode,
left(t1.itemcode,2)as CodMatriz,left(t1.itemcode,4)as CodCategoria,
left(t1.itemcode,6)as CodSubCategoria,left(t1.itemcode,8)as CodLinea,
t6.InvntItem,(t1.quantity*1) as Cantidad,t1.Dscription,t1.DiscPrcnt,
(t1.INMPrice*t1.quantity/1000) ,(t1.GrssProfit/1000) ,t2.SlpName,t0.U_CatVenta
from oslp t2, oitm t6, oinv t0, INV1 t1
where t0.docentry=t1.docentry and t0.DocType='I' and t0.slpcode=t2.SlpCode and 
t1.itemcode=t6.itemcode
)
union
(
select t0.objtype,t0.docnum,t0.docdate,t1.itemcode,
left(t1.itemcode,2)as CodMatriz,left(t1.itemcode,4)as CodCategoria,
left(t1.itemcode,6)as CodSubCategoria,left(t1.itemcode,8)as CodLinea,
t6.InvntItem,(t1.quantity*-1) as Cantidad,t1.Dscription,t1.DiscPrcnt,
(t1.INMPrice*t1.quantity/1000*-1) ,(t1.GrssProfit/1000*-1),t2.SlpName,T0.U_CatVenta
from oslp t2, oitm t6, ORIN t0, RIN1 t1
where t0.docentry=t1.docentry and t0.DocType='I' and t0.slpcode=t2.SlpCode and 
t1.itemcode=t6.itemcode

))

[Former Member · Accepted solution]

INMPrice is only for item's last sale price. Replace with Price and Rate fields instead.

[Former Member]

Hi..

Check this query and let me know whether this is ur requirement or not..

select

t0.objtype,t0.docnum,t0.docdate,t1.itemcode,

left(t1.itemcode,2)as CodMatriz,left(t1.itemcode,4)as CodCategoria,

left(t1.itemcode,6)as CodSubCategoria,left(t1.itemcode,8)as CodLinea,

t6.InvntItem,(t1.quantity+(t4.quantity*(-1)) ) as Cantidad,t1.Dscription,t1.DiscPrcnt,

((t1.INMPrice(t1.quantity/1000))+ (t4.INMPrice(t4.quantity/1000)*(-1))),

((t1.GrssProfit/1000)+ ((t4.GrssProfit/1000)*(-1)))

, t2.SlpName

from oslp t2 join oinv t0 on t0.slpcode=t2.SlpCode join INV1 t1

on t0.docentry=t1.docentry join ORIN t3 on t3.slpcode=t2.SlpCode join RIN1 t4 on t3.docentry=t4.docentry

join oitm t6 on t1.itemcode=t6.itemcode

where t0.DocType='I' and t3.DocType='I' and t4.itemcode=t6.itemcode