on 08-22-2011 6:48 PM
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!!
Hola Isabel, probablemente este exista un problema con los operadores de comparacion, seria mas facil si compartes tu query
Saludos
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
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
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!!
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
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
Como esta tu codigo?, la rct2 tiene esa informacion no?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
91 | |
11 | |
10 | |
6 | |
5 | |
5 | |
5 | |
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.