Reporte de Ventas

[Former Member]

Quiero sacar un reporte de ventas. Lo que intento es sacar todas las facturas en un periodo de tiempo restandole las Notas de crédito. INV1 - RIN1

Mi consulta solo tiene un problemita, no me aparecen las facturas donde algunos de sus productos tienen nota de creditos. Es decir,si una factura de 6 prodcutos tiene una NC por solo una, no aparece nignuna.

Esta es mi consulta:

select SELECT t3.NumAtCard, t2.docentry, t2.basecard, t4.cardname, t2.itemcode, t2.dscription, t2.priceBefDi, t2.currency, t2.quantity, t2.opensum, t2.opensumFC, t2.vatsum, t2.vatsumFrgn, t2.gtotal, t2.gtotalFC, t2.docdate from oinv t3 inner join inv1 t2 on t3.docentry=t2.docentry inner join ocrd t4 on t2.basecard=t4.cardcode where t2.docdate>='[%1]' and t2.docdate<='[%2]' and

T2.DocEntry not in (SELECT DISTINCT

T1.BaseEntry

FROM

.[ORIN] T0 INNER JOIN

.[RIN1] T1 ON T0.DocEntry = T1.DocEntry LEFT JOIN

.[INV1] T2 ON T1.BaseEntry = T2.DocEntry AND T1.BaseLine = T2.LineNum AND T1.BaseType = 13 INNER JOIN

.[OINV] T3 ON T2.DocEntry = T3.DocEntry)

order by t2.docdate

[Former Member]

Ronald Buenos dias quisiera saber si pudiste resolver el tema de tu reporte de ventas que mostrara las factura emitidas en un periodo de tiempo restando las notas de credito, ojala y puedas ayudarme ya que tengo el mismo problema

Saludos..

[Former Member]

es muy facil, todas las facturas que fueron copiadas a una nota de credito tienen como campo [TargetType] el valor 14, elimina esos doctos con una condicion.

si haces notas de credito por fuera entonces si deberas enlazar mas tablas.

[Former Member]

YO TE RECOMIENDO EL UNION

SELECT t3.NumAtCard, 
               t2.docentry, 
               t2.basecard, 
               t4.cardname, 
               t2.itemcode, 
               t2.dscription, 
               t2.priceBefDi, 
               t2.currency, 
               t2.quantity, 
               t2.opensum, 
               t2.opensumFC, 
               t2.vatsum, 
               t2.vatsumFrgn, 
               t2.gtotal, 
               t2.gtotalFC, 
               t2.docdate 
from oinv t3 
	inner join inv1 t2 on t3.docentry=t2.docentry 
	inner join ocrd t4 on t2.basecard=t4.cardcode 
where t2.docdate>='%1' and t2.docdate<='%2' 
GROUP BY t3.NumAtCard, 
               t2.docentry, 
               t2.basecard, 
               t4.cardname, 
               t2.itemcode, 
               t2.dscription, 
               t2.priceBefDi, 
               t2.currency, 
               t2.quantity, 
               t2.opensum, 
               t2.opensumFC, 
               t2.vatsum, 
               t2.vatsumFrgn, 
               t2.gtotal, 
               t2.gtotalFC, 
               t2.docdate
               
UNION ALL

SELECT t3.NumAtCard, 
               t2.docentry, 
               t2.basecard, 
               t4.cardname, 
               t2.itemcode, 
               t2.dscription, 
               t2.priceBefDi, 
               t2.currency, 
               t2.quantity, 
               t2.opensum, 
               t2.opensumFC, 
               t2.vatsum, 
               t2.vatsumFrgn, 
               t2.gtotal, 
               t2.gtotalFC, 
               t2.docdate 
from oinv t3 
	inner join inv1 t2 on t3.docentry=t2.docentry 
	inner join ocrd t4 on t2.basecard=t4.cardcode 
where t2.docdate>='%1' and t2.docdate<='%2' 
GROUP BY t3.NumAtCard, 
               t2.docentry, 
               t2.basecard, 
               t4.cardname, 
               t2.itemcode, 
               t2.dscription, 
               t2.priceBefDi, 
               t2.currency, 
               t2.quantity, 
               t2.opensum, 
               t2.opensumFC, 
               t2.vatsum, 
               t2.vatsumFrgn, 
               t2.gtotal, 
               t2.gtotalFC, 
               t2.docdate

ESTA ES EN BASE A UNA CONSULTA SIMILAR CON LA QUE YO TRABAJO PERO TOME TU BASE CUALQUIER CONSLTA ESTOY A LA ORDEN

[Former Member]

Muchas gracias.

En tu consulta no veo nignún enlace a la tabla de Nota de crédito. Como eliminas las facturas que tienen notas de crédito?

[Former Member]

Hola,

Prueba esta a ver si te sirve:

SELECT 'Factura' as 'Documento', t3.NumAtCard , t2.docentry , t2.basecard , t4.cardname , t2.itemcode , t2.dscription , t2.priceBefDi , t2.currency , t2.quantity , t2.opensum , t2.opensumFC , t2.vatsum , t2.vatsumFrgn , t2.gtotal , t2.gtotalFC , t2.docdate from oinv t3 inner join inv1 t2 on t3.docentry=t2.docentry inner join ocrd t4 on t2.basecard=t4.cardcode where t2.docdate>='[%1]' and t2.docdate<='[%2]'

Union

SELECT 'Abono' as 'Documento', t3.NumAtCard , t2.docentry , t2.basecard , t4.cardname , t2.itemcode , t2.dscription , t2.priceBefDi , t2.currency , (t2.quantity * (-1)) , t2.opensum , t2.opensumFC , (t2.vatsum * (-1)) , (t2.vatsumFrgn * (-1)) , (t2.gtotal * (-1)) , t2.gtotalFC , t2.docdate from orin t3 inner join rin1 t2 on t3.docentry=t2.docentry inner join ocrd t4 on t2.basecard=t4.cardcode where t2.docdate>='[%1]' and t2.docdate<='[%2]'

Un saludo,

Tere

[angeles804]

este tal vez te puede servir:s

-- Reporte de Facturas 
Select  T0.DocNum,
case when t0.doctype='I' then 'Articulo' 
when t0.doctype='s' then 'servicio' end as 'Tipo'
, t1.seriesname as 'Sucursal',T0.FolioPref, T0.FolioNum,   
CASE WHEN T0.OBJTYPE=13 and t0.docsubtype<>'DN' and t0.isins<>'y' THEN 
                'FACTURA' 
          when t0.objtype=13 and t0.docsubtype='DN' then 
                 'N/D' 
           when t0.objtype=13 and t0.isins='y' then 'FACT. RESERVA' END as 'Documento', T0.DocDate, T0.CardCode, T0.CardName, T0.DocTotal,  t2.PymntGroup 
FROM OINV T0 
	INNER JOIN  NNM1 T1 ON T0.series=T1.series
	INNER JOIN OCTG T2 ON T2.GroupNum= T0.GroupNum
WHERE  T0.DocDate >= '[%1]' and T0.DocDate <= '[%2]'
UNION ALL
Select t0.docnum,
case when t0.doctype='I' then 'Articulo' 
when t0.doctype='s' then 'servicio' end as 'tipo',
t1.seriesname as 'Sucursal',t0.foliopref, t0.folionum, CASE WHEN T0.OBJTYPE=14 THEN 'NOTA DE CREDITO' END as 'Documento' , t0.docdate,t0.cardcode,t0.cardname,- t0.doctotal,t2.pymntgroup
FROM  ORIN T0
	INNER JOIN NNM1 T1 ON T0.SERIES=T1.SERIES
	INNER JOIN OCTG T2 ON T2.GROUPNUM=T0.GROUPNUM
WHERE  T0.DOCDATE>='[%1]' and t0.docdate <='[%2]' AND T0.DOCNUM<5500000
UNION ALL
SelecT t0.docnum,
case when t0.doctype='I' then 'Articulo' 
when t0.doctype='s' then 'servicio' end as 'Tipo',
 t1.seriesname as 'Sucursal',t0.foliopref, t0.folionum, CASE WHEN T0.OBJTYPE=16 THEN 'DEVOLUCION' END as 'Documento', t0.docdate,t0.cardcode, t0.cardname,
-T0.doctotal, t2.pymntgroup
FROM  ORDN T0
	INNER JOIN NNM1 T1 ON T0.SERIES=T1.SERIES
	INNER JOIN OCTG T2 ON T2.GROUPNUM=T0.GROUPNUM
WHERE T0.DOCDATE>='[%1]' AND T0.DOCDATE<='[%2]'
UNION ALL
SELECT T0.DocNum ,
case when t0.doctype='I' then 'Articulo' 
when t0.doctype='s' then 'servicio' end as 'Tipo',
t4.seriesname as 'Sucursal',T2.Foliopref, T2.docnum,CASE WHEN T0.Objtype=14 THEN 'NC CANCEL' END as 'Documento', T0.DocDate as 'Fecha contabilización', T0.cardcode,t0.cardname, -T0.Doctotal,T3.pymntgroup
FROM ORIN T0  
INNER JOIN OJDT T1 ON T0.TransId = T1.TransId 
INNER JOIN OINV T2 ON t1.[ref2]=t2.[docnum] 
INNER JOIN OCTG T3 ON T3.GROUPNUM=T0.GROUPNUM
INNER JOIN NNM1 T4 ON T4.SERIES=T2.SERIES
WHERE (t0.docnum>4999999 and t0.docnum<6000000) and (t2.cardcode=t0.cardcode) and (t0.docdate>='[%1]' and t0.docdate<='[%2]')