on 06-22-2010 6:22 AM
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 ?
You may post your query here for others to check.
Thanks,
Gordon
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
((
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
))
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
108 | |
12 | |
11 | |
6 | |
6 | |
4 | |
4 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.