Reporte de Facturas Credito / Contado y nota de credito

[Former Member]

BUENAS TARDES COMPAÑEROS Y FELIZ AÑO 2015

Tengo que desarrollar un reporte donde salgan los documentos de ventas,(factura Credito, Contado y Nota de Credito) pero no se como incluir las notas de Credito que me en la misma columna, todos los datos necesario.

este es el desarrollo que hasta el momento he realizado

SELECT T0.DocNum, t0.docdate, t0.cardcode, t0.cardname, T0.SlpCode,T1.SlpName,

case

when T0.groupnum='-1' then 'Contado'

else 'Crédito' END as 'Tipo Venta',

(select x3.PymntGroup from OCTG x3

where T0.GroupNum = x3.GroupNum) as 'Venta',

(select (doctotal - VatSumSy + DiscSum) from oinv

where docnum = t0.docnum)  as 'Sub-Total',T0.DiscSum as 'Descuento',

(select isnull (sum(price),0) from inv1 inner join oitm on inv1.itemcode = oitm.itemcode where inv1.docentry = t0.docentry and oitm.QryGroup64 = 'Y') as 'GASTOS',

(select isnull (sum(price),0) from inv1 inner join oitm on inv1.itemcode = oitm.itemcode where inv1.docentry = t0.docentry and oitm.QryGroup64 = 'Y'AND INV1.ITEMCODE='SEG') as 'Seguro',

(select isnull (sum(price),0) from inv1 inner join oitm on inv1.itemcode = oitm.itemcode where inv1.docentry = t0.docentry and oitm.QryGroup64 = 'Y'AND INV1.ITEMCODE='REEM') as 'Reempaque',

(select isnull (sum(price),0) from inv1 inner join oitm on inv1.itemcode = oitm.itemcode where inv1.docentry = t0.docentry and oitm.QryGroup64 = 'Y'AND INV1.ITEMCODE='SAL') as 'Salida',

(select isnull (sum(price),0) from inv1 inner join oitm on inv1.itemcode = oitm.itemcode where inv1.docentry = t0.docentry and oitm.QryGroup64 = 'Y'AND INV1.ITEMCODE='TR') as 'Traspaso',

(select isnull (sum(price),0) from inv1 inner join oitm on inv1.itemcode = oitm.itemcode where inv1.docentry = t0.docentry and oitm.QryGroup64 = 'Y'AND INV1.ITEMCODE='FLT') as 'Flete',

(select isnull (sum(price),0) from inv1 inner join oitm on inv1.itemcode = oitm.itemcode where inv1.docentry = t0.docentry and oitm.QryGroup64 = 'Y'AND INV1.ITEMCODE='COU') as 'Courrier',

(select isnull (sum(price),0) from inv1 inner join oitm on inv1.itemcode = oitm.itemcode where inv1.docentry = t0.docentry and oitm.QryGroup64 = 'Y'AND INV1.ITEMCODE='D.H.L.') as 'D.H.L.',

(select isnull (sum(price),0) from inv1 inner join oitm on inv1.itemcode = oitm.itemcode where inv1.docentry = t0.docentry and oitm.QryGroup64 = 'Y'AND INV1.ITEMCODE='1.50XBULTO') as '1.50 X Bulto',

(select isnull (sum(price),0) from inv1 inner join oitm on inv1.itemcode = oitm.itemcode where inv1.docentry = t0.docentry and oitm.QryGroup64 = 'Y'AND INV1.ITEMCODE='CO') as 'Certificado Origen',

(select isnull (sum(price),0) from inv1 inner join oitm on inv1.itemcode = oitm.itemcode where inv1.docentry = t0.docentry and oitm.QryGroup64 = 'Y'AND INV1.ITEMCODE='T.CR') as 'Dif. Tarjeta CR',

(select isnull (sum(price),0) from inv1 inner join oitm on inv1.itemcode = oitm.itemcode where inv1.docentry = t0.docentry and oitm.QryGroup64 = 'Y'AND INV1.ITEMCODE='TRANSIT') as 'Transit',

(select isnull (sum(price),0) from inv1 inner join oitm on inv1.itemcode = oitm.itemcode where inv1.docentry = t0.docentry and oitm.QryGroup64 = 'Y'AND INV1.ITEMCODE='OTROS') as 'Otros',

(select doctotal from oinv where docnum = t0.docnum)as 'Total',

(select doctotal from oinv where docnum = t0.docnum and T0.groupnum='-1')as 'Total Contado',

(select doctotal from oinv where docnum = t0.docnum and T0.groupnum <>'-1') as 'Total Credito',

(select doctotal from oinv where docnum = t0.docnum)-

(select isnull (sum(price),0)

from inv1 inner join oitm on inv1.itemcode = oitm.itemcode

where inv1.docentry = t0.docentry and oitm.QryGroup64 = 'Y')as 'Total sin gastos',

(select sum(GrossBuyPr* Quantity)from inv1 where docentry = t0.docentry)  as 'Costo',

((select doctotal from oinv where docnum = t0.docnum)-

(select isnull(sum(price),0)

from inv1 inner join oitm on inv1.itemcode = oitm.itemcode

where inv1.docentry = t0.docentry and oitm.QryGroup64 = 'Y'))-

((select sum(GrossBuyPr* Quantity)

from inv1 where docentry = t0.docentry))as 'diferencia'

FROM OINV T0  INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode

where T0.DocDate >= '01/10/2014' and T0.DocDate <= '12/31/2014'

--T0.DocType='I' and

ORDER BY T0.docnum,

por favor si alguien me presta su ayuda lo mas urgente posible.

Saludos

ER

[Former Member]

Por como describes tu necesidad, te recomiendo crear un nuevo query pero cambia todo lo que tengas de OINV por ORIN y INV1 por RIN1. Al final unes los 2 queries con un "union all". Además, agrega una columna en ambos queries que te ayude a identificar cuáles son registros de notas de crédito y cuáles de factura. En los campos de suma o numericos de notas de crédito puedes ponerle un signo negativo para que al sumar con facturas te de un resultado más correcto.

Saludos,

JC.

[Former Member]

Buenos días JC

el querry quedo de la siguiente manera

SELECT T0.NumAtCard, t0.docdate, t0.cardcode, t0.cardname, T0.SlpCode,T1.SlpName,

case

when T0.groupnum='-1' then 'F.Contado'

else 'F.Crédito' END as 'Tipo Venta',

(select doctotal from oinv where docnum = t0.docnum)as 'Total',

(select doctotal from oinv where docnum = t0.docnum and T0.groupnum='-1')as 'Total Contado',

(select doctotal from oinv where docnum = t0.docnum and T0.groupnum <>'-1') as 'Total Credito'

FROM OINV T0  INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode

UNION ALL

SELECT T0.NumAtCard, t0.docdate, t0.cardcode, t0.cardname, T0.SlpCode,T1.SlpName,

case

when T0.groupnum='-1' then 'Contado'

else 'N / Crédito' END as 'Tipo Venta',

(select doctotal from ORIN where docnum = t0.docnum)as 'Total',

(select doctotal from ORIN where docnum = t0.docnum and T0.groupnum='-1')as 'Total Contado',

(select doctotal from ORIN where docnum = t0.docnum and T0.groupnum <>'-1') as 'Total Credito'

FROM ORIN T0  INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode

where T0.DocDate >=[%0] and T0.DocDate <=[%1]

ORDER BY T0.DocDate

pero al pasarlo al Generador de Cunsulta del SAP. no me funciona.

Tengo la Version de SAP 9 PL 7