Ayuda con consulta

[Former Member]

Colegas buena semana,

Tengo dos facturas, unas que están pagadas en efectivo y otras que están pagadas con tarjeta de credito. Quiero sacar un reporte en donde me muestre qué número de facturas fueron pagadas en efectivo y qué números fueron pagadas con tarjeta de crédito.

Cuando mando llamar las facturas pagadas con tarjeta de credito se ejectua correctamente, pero cuando mando llamar las facturas que pagadas en efectivo, salen las pagadas en efectivo Y las pagadas con tarjeta de credito.

Cómo puedo hacer para que me aparezcan las que sólo estan pagadas con efectivo?

Saludos!!

[felipe_loyolarodriguez]

Hola Isabel, probablemente este exista un problema con los operadores de comparacion, seria mas facil si compartes tu query

Saludos

[Former Member]

Mi query es el siguiente,

En el primer Select es para las facturas en efectivo y en el segundo para las de tarjeta de crédito.

Espero que me puedan ayudar.

SELECT Distinct T2.[SlpName] AS Vendedor, T0.[CardName] AS Cliente, T0.DocTotal/1.16 as SubTotal,

((T0.DocTotal)-(T0.DocTotal/1.16)) as IVA, T0.DocTotal as Total,

case when T3.[GroupCode] = '100' then 'Factura-Efectivo' end AS 'Ventas de...', T0.DocNum as 'Número SAP'

FROM OINV T0

INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode

INNER JOIN OCRD T3 ON T0.CardCode = T3.CardCode

LEFT JOIN RCT2 T4 ON T0.DocEntry = T4.DocEntry

LEFT JOIN ORCT T5 ON T4.DocNum = T5.DocEntry

LEFT JOIN RCT3 T6 ON T5.DocEntry = T6.DocNum

WHERE T0.[DocDate] between '[%0]' and '[%1]' and T1.[TargetType] = '-1'

AND T3.[GroupCode] = '100' AND T0.[PaidToDate] = T0.[DocTotal] AND T0.[EDocNum] is null AND T0.Series <> '46' AND T6.[CreditSum] IS NULL

GROUP BY T2.[SlpName], T3.[GroupCode], T0.[CardName], T0.[CardCode], T0.DocNum, T0.Series, T0.DocTotal

UNION

SELECT Distinct T2.[SlpName] AS Vendedor, T0.[CardName] AS Cliente, T0.DocTotal/1.16 as SubTotal,

((T0.DocTotal)-(T0.DocTotal/1.16)) as IVA, T0.DocTotal as Total,

case when T3.[GroupCode] = '100' then 'Factura-Tarjeta' end AS 'Ventas de...', T0.DocNum as 'Número SAP'

FROM OINV T0

INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode

INNER JOIN OCRD T3 ON T0.CardCode = T3.CardCode

INNER JOIN RCT2 T4 ON T0.DocEntry = T4.DocEntry

INNER JOIN ORCT T5 ON T4.DocNum = T5.DocEntry

INNER JOIN RCT3 T6 ON T5.DocEntry = T6.DocNum

WHERE T0.[DocDate] between '[%0]' and '[%1]' and T1.[TargetType] = '-1'

AND T3.[GroupCode] = '100' AND T0.[EDocNum] is null AND T0.Series <> '46' AND T0.PaidSum > '0'

GROUP BY T2.[SlpName], T3.[GroupCode], T0.[CardName], T0.[CardCode], T0.DocNum, T0.Series, T0.DocTotal

[former_member188440]

Quita el join a la RCT3 de la seccion donde estas trayendo las de efectivo unicamente

[angeles804]

select  T5.seriesname as 'Nombre', T0.cardcode as 'proveedor', t0.cardname as 'Nombre', 
T0.DocNum as 'Dcto de pago', 
case when t9.doctype='s' then 'Gastos' else 'Compras' end as 'Tipo de Factura',
T9.DOCNUM AS '#Factura SAP', T0.DocDate as 'Fecha Pago',
t9.docdate as 'Fecha cont factura',t0.doctotal,t0.vatsum,
	CTAEFECTIVO = case when t0.cashsum<>0 then T2.acctname   end,
    IMPORTE= T0.CASHSUM
FROM ORCT T0 	
	INNER JOIN OUSR T1 ON T0.usersign =T1.userid
	INNER JOIN NNM1 T5 ON T0.series =  T5.series  
        LEFT  OUTER JOIN RCT1 T6 ON T0.docnum = T6.docnum
        LEFT  OUTER JOIN OACT T2 ON T2.acctcode=T0.cashacct 
    	INNER join OINV T9 ON T0.DOCNUM=T9.RECEIPTNUM
where t0.canceled = 'N' AND T9.DocDate >= '[%1]'
and T9.DocDate <= '[%2]' AND T0.CASHSUM>0

te pongo una a ver si te sirve. :s pero esto es en base a facturas de ventas.

[Former Member]

Mauricio,

Ya le quite la tabla RCT3 pero aún me siguen saliendo las facturas pagadas en efectivo. A la mejor si uno la tabla del pago en efectivo podría ser pero no la encuentro.

Mary,

Gracias!!!! pero me temo que no es lo que necesito.

Gracias por el apoyo, qué otra cosa se les ocurre?

[former_member188440]

Es necesario el case ='100' en ambos casos? para tarjetas y efectivo?, decias que en una de las opciones si te traia las de efectivo, entonces estas usando las tablas correctas

Intenta donde quieres ver el efectivo que la tabla rct2 diga

T4.SumApplied >0

Edited by: Mauricio I Quintana on Aug 22, 2011 2:08 PM

[Former Member]

Ya agregué esa condición pero me sigue apareciendo las pagadas con Tarjeta de Crédito. =S

[former_member188440]

A ver creo nos estamos haciendo bolas, puedes poner la seccion que te esta dando problemas ? asi como indicar en esa seccion que debe aparecer , si efectivo o tarjeta unicamente

[Former Member]

OK. El reporte esta formado por 2 secciones:

Éste primero tiene la intención de mostrar las facturas que estan pagadas en efectivo, lo diferencío por el comentario de 'Factura-Efectivo'

SELECT Distinct T2.SlpName AS Vendedor, T0.CardName AS Cliente, T0.DocTotal/1.16 as SubTotal,

((T0.DocTotal)-(T0.DocTotal/1.16)) as IVA, T0.DocTotal as Total,

case when T3.GroupCode = '100' then 'Factura-Efectivo' end AS 'Ventas de...', T0.DocNum as 'Número SAP'

FROM OINV T0

INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode

INNER JOIN OCRD T3 ON T0.CardCode = T3.CardCode

LEFT JOIN RCT2 T4 ON T0.DocEntry = T4.DocEntry

LEFT JOIN ORCT T5 ON T4.DocNum = T5.DocEntry

LEFT JOIN RCT3 T6 ON T5.DocEntry = T6.DocNum

WHERE T0.DocDate between '%0' and '%1' and T1.TargetType = '-1'

AND T3.GroupCode = '100' AND T0.PaidToDate = T0.DocTotal AND T0.EDocNum is null AND T0.Series '46' AND T6.CreditSum IS NULL

GROUP BY T2.SlpName, T3.GroupCode, T0.CardName, T0.CardCode, T0.DocNum, T0.Series, T0.DocTotal

La segunda parte del reporte muestra todas las facturas que están pagadas con tarjeta de crédito, lo diferencío por el comentario de 'Factura-Remisión Pagada Tarjeta'

UNION

SELECT Distinct T2.[SlpName] AS Vendedor, T0.[CardName] AS Cliente, T0.DocTotal/1.16 as SubTotal,

((T0.DocTotal)-(T0.DocTotal/1.16)) as IVA, T0.DocTotal as Total,

case when T3.[GroupCode] = '100' then 'Factura-Remisión Pagada Tarjeta' end AS 'Ventas de...', T0.DocNum as 'Número SAP'

FROM OINV T0

INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode

INNER JOIN OCRD T3 ON T0.CardCode = T3.CardCode

INNER JOIN RCT2 T4 ON T0.DocEntry = T4.DocEntry

INNER JOIN ORCT T5 ON T4.DocNum = T5.DocEntry

INNER JOIN RCT3 T6 ON T5.DocEntry = T6.DocNum

WHERE T0.[DocDate] between '[%0]' and '[%1]' and T1.[TargetType] = '-1'

AND T3.[GroupCode] = '100' AND T0.[EDocNum] is null AND T0.Series <> '46' AND T0.PaidSum > '0'

GROUP BY T2.[SlpName], T3.[GroupCode], T0.[CardName], T0.[CardCode], T0.DocNum, T0.Series, T0.DocTotal

Cuando corro el reporte, me aparece lo siguiente por ejemplo.

La factura número 9776 está pagada con Tarjeta de crédito.

cuando corro el reporte aparece dos veces así:

9776 Factura-Efectivo

9776 Factura-Remisión Pagada Tarjeta

Esto es incorrecto ya que sólo debería de aparecer la línea de: 9776 Factura-Remisión Pagada Tarjeta

qué problema tendría en el Select de la primera parte?

Mil Gracias por tu ayuda!!

[former_member188440]

A ver asi

SELECT Distinct T2.SlpName AS Vendedor, T0.CardName AS Cliente, T0.DocTotal/1.16 as SubTotal,
((T0.DocTotal)-(T0.DocTotal/1.16)) as IVA, T0.DocTotal as Total, 
case when T3.GroupCode = '100' then 'Factura-Efectivo' end AS 'Ventas de...', T0.DocNum as 'Número SAP'
FROM OINV T0 
INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry 
INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode 
INNER JOIN OCRD T3 ON T0.CardCode = T3.CardCode 
LEFT JOIN RCT2 T4 ON T0.DocEntry = T4.DocEntry 
LEFT JOIN ORCT T5 ON T4.DocNum = T5.DocEntry 
--LEFT JOIN RCT3 T6 ON T5.DocEntry = T6.DocNum
WHERE T0.DocDate between '%0' and '%1' and T1.TargetType = '-1' and T4.SumApplied >0
AND T3.GroupCode = '100' AND T0.PaidToDate = T0.DocTotal AND T0.EDocNum is null AND T0.Series !='46' --AND T6.CreditSum IS NULL
GROUP BY T2.SlpName, T3.GroupCode, T0.CardName, T0.CardCode, T0.DocNum, T0.Series, T0.DocTotal

La segunda parte del reporte muestra todas las facturas que están pagadas con tarjeta de crédito, lo diferencío por el comentario de 'Factura-Remisión Pagada Tarjeta'
UNION
SELECT Distinct T2.SlpName AS Vendedor, T0.CardName AS Cliente, T0.DocTotal/1.16 as SubTotal,
((T0.DocTotal)-(T0.DocTotal/1.16)) as IVA, T0.DocTotal as Total, 
case when T3.GroupCode = '100' then 'Factura-Remisión Pagada Tarjeta' end AS 'Ventas de...', T0.DocNum as 'Número SAP'
FROM OINV T0 
INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry 
INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode 
INNER JOIN OCRD T3 ON T0.CardCode = T3.CardCode 
INNER JOIN RCT3 T4 ON T0.DocEntry = T4.DocNum
INNER JOIN ORCT T5 ON T4.DocNum = T5.DocEntry 
INNER JOIN RCT3 T6 ON T5.DocEntry = T6.DocNum
WHERE T0.DocDate between '%0' and '%1' and T1.TargetType = '-1' 
AND T3.GroupCode = '100' AND T0.EDocNum is null AND T0.Series !='46' AND T0.PaidSum > '0'
GROUP BY T2.SlpName, T3.GroupCode, T0.CardName, T0.CardCode, T0.DocNum, T0.Series, T0.DocTotal

[Former Member]

Pues todo me sale como Factura-Efectivo, y tengo como 10 facturas pagadas con tarjeta de crédito.

[former_member188440]

Modifique la seccion de las tarjetas

Intenta probarlo asi

SELECT 
Distinct T2.SlpName AS Vendedor,
 T0.CardName AS Cliente, T0.DocTotal/1.16 as SubTotal,
((T0.DocTotal)-(T0.DocTotal/1.16)) as IVA, T0.DocTotal as Total, 
case when T3.GroupCode = '100' then 'Factura-Remisión Pagada Tarjeta' end AS 'Ventas de...', T0.DocNum as 'Número SAP'
FROM OINV T0
INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry 
INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode 
INNER JOIN OCRD T3 ON T0.CardCode = T3.CardCode 
inner JOIN RCT3 T4 ON T0.DocEntry = T4.DocNum
inner JOIN ORCT T5 ON T4.DocNum = T5.DocEntry 
WHERE T0.DocDate between '2010-01-01' and '2011-08-12'
 and T1.TargetType = '-1' 
AND T3.GroupCode = '100' 
 AND T0.Series !='1'
 
GROUP BY T2.SlpName, T3.GroupCode, T0.CardName, T0.CardCode, T0.DocNum, T0.Series, T0.DocTotal

[former_member188440]

Como esta tu codigo?, la rct2 tiene esa informacion no?