forma de pago de factura

[Former Member]

hola, quisiera saber como hago para:

1. identificar como fue pagada una factura de reserva, si en efectivo,cheque o tarjeta, necesito sacar un informe de los pagos realizados en cierta fecha pero discriminando el medio de pago, lo que pasa es que me complico cuando el pago es mixto.

tengo el siguiente query pero me falta discriminar cuanto entro en efectivo y cuanto en cheque para la misma factura

2. lo mismo pasa con los pagos recibidos manuales tengo para esto el siguiente query pero no se como hacer cuando el pago es mixto: parte en efectivo y en tarjeta porque lo q hace es duplicarme el valor

SELECT distinct T0.[RefDate],T0.[BaseRef]'Recibo de Caja',T0.[Ref2],T0.[LocTotal]'Total efectivo', '0' as 'Total Cheque', ' 0' as 'Total Tarjeta' FROM OJDT T0  INNER JOIN ORCT T1 ON T0.TransId = T1.TransId INNER JOIN JDT1 T2 ON T0.TransId = T2.TransId WHERE left(T0.[Ref2],2) in ('ZI','Y','N','P','A','S','M','JC','L','R','J') and T0.[RefDate]=[%0] and  T1.[Canceled] ='N' AND T2.[Account]='11050505' 
UNION
SELECT distinct T0.[RefDate],T0.[BaseRef]'Recibo de Caja',T0.[Ref2],'0 ' as 'Total efectivo', '0 ' as 'Total Cheque', T0.[LocTotal] as 'Total Tarjeta' FROM OJDT T0  INNER JOIN ORCT T1 ON T0.TransId = T1.TransId INNER JOIN JDT1 T2 ON T0.TransId = T2.TransId WHERE left(T0.[Ref2],2) in ('ZI','Y','N','P','A','S','M','JC','L','R','J') and T0.[RefDate]=[%0] and  T1.[Canceled] ='N' AND T2.[Account]='11050515' 
UNION
SELECT distinct T0.[RefDate],T0.[BaseRef]'Recibo de Caja',T0.[Ref2],'0' as 'Total efectivo', T0.[LocTotal] as 'Total Cheque', '0' as 'Total Tarjeta' FROM OJDT T0  INNER JOIN ORCT T1 ON T0.TransId = T1.TransId INNER JOIN JDT1 T2 ON T0.TransId = T2.TransId WHERE left(T0.[Ref2],2) in ('ZI','Y','N','P','A','S','M','JC','L','R','J') and T0.[RefDate]=[%0] and  T1.[Canceled] ='N' AND T2.[Account]='11050510'

[Former Member]

y este para recaudos

SELECT distinct T4.[RefDate] ,
       T4.[BaseRef]'Recibo de Caja',
	T4.[Ref2],
	T4.[LocTotal],
	T5.[Account] 'Cuenta', 
	Case when T5.[Account] ='11050505' Then T5.[Debit] else 0 end [Efectivo],
	'0' as 'Cheque al Dia',
	'0' as 'Cheque Posf',
	'0' as 'Cheque Dev' ,Case when T5.[Account] ='11050515' Then T5.[Debit] else 0 end [Tarjeta]

FROM OJDT T4  INNER JOIN JDT1 T5 ON T4.TransId = T5.TransId INNER JOIN ORCT T6 ON T4.TransId = T6.TransId WHERE left(T4.[Ref2],2) in ('ZI','Y','N','P','A','S','M','JC','L','R','J','Cheque%') and T4.[RefDate]=[%0] and  left(T5.[Account],8) in('11050505','11050515')and T6.[Canceled] ='N'

UNION


SELECT T0.[DocDate], 
        T0.[DocNum]'Recibo de Caja',
        T2.[Ref2],
 	T0.[DocTotal], 
	T1.[CheckAct]'Cuenta',
	'0' as 'Efectivo',
	
	Case when T1.[U_Manejo] ='DIA' Then T1.[CheckSum] else 0 end [Cheque al Dia],
	Case when T1.[U_Manejo] ='POST'  Then T1.[CheckSum] else 0 end [Cheque Posf],
	'0' as 'Cheque Dev','0' as 'Tarjeta'
	
	
 
FROM ORCT T0  INNER JOIN RCT1 T1 ON T0.DocEntry = T1.DocNum inner join OJDT T2 ON T0.TransId = T2.TransId
WHERE T0.[DocDate]=[%0] AND left(T2.[Ref2],2) in ('ZI','Y','N','P','A','S','M','JC','L','R','J') and T0.[Canceled]='N'

UNION

SELECT  T0.[RefDate], 
	T0.[BaseRef]'Recibo de Caja', 
	T0.[Ref2], 
	T0.[LocTotal], 
	T1.[Account]'Cuenta', 
	'0' as 'Efectivo',
	'0' as 'Cheque al Dia',
	'0' as 'Cheque Posf',
	T1.[Credit]'Cheque Dev','0' as 'Tarjeta' 

FROM OJDT T0  INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId
WHERE T0.[RefDate]=[%0] and T0.[Memo]='Cheque Devuelto'and  T1.[Account] like  '1120%' 

union
SELECT  T0.[RefDate], 
	T0.[BaseRef]'Recibo de Caja', 
	T0.[Ref2], 
	T0.[LocTotal], 
	T1.[Account]'Cuenta', 
	'0' as 'Efectivo',
	'0' as 'Tarjetas',
	'0' as 'Cheque al Dia',
	'0' as 'Cheque Posf',
	T1.[Credit]'Cheque Dev' 

FROM OJDT T0  INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId INNER JOIN ORCT T6 ON T0.TransId = T6.TransId
WHERE T0.[RefDate]=[%0] and T0.[Memo]='Cheque Devuelto'and  T1.[Account] like  '1110%' and T6.[Canceled] ='N'

union

SELECT distinct T4.[RefDate] ,
       T4.[BaseRef]'Recibo de Caja',
	T4.[Ref2],
	T4.[LocTotal],
	T5.[Account] 'Cuenta', 
	Case when T5.[Account] ='11050505' Then T5.[Debit] else 0 end [Efectivo],
	'0' as 'Cheque al Dia',
	'0' as 'Cheque Posf',
	'0' as 'Cheque Dev' ,'0' as 'Tarjeta'

FROM OJDT T4  INNER JOIN JDT1 T5 ON T4.TransId = T5.TransId  WHERE T4.[Ref2] like 'PROM%' and  T4.[RefDate]=[%0] and T5.[Account]='11050505' and T4.[AutoStorno]= 'N'

[Former Member]

para los que les interesen aqui les dejo el query:

SELECT T0.[DocDate] 'Fecha Factura', T2.[SeriesName] 'Serie' , T0.[DocNum]' No. Factura',  T0.[DocTotal]'Total Factura', T0.[PaidSum]'Total Pagos', T3.[DocNum]' No.Recibo', T5.[Account] 'Cuenta', 
Case when T5.[Account] ='11050505' Then T5.[Debit] else 0 end [Efectivo],Case when T5.[Account] ='11050510' Then T5.[Debit] else 0 end [Cheque],Case when T5.[Account] ='11050515' Then T5.[Debit] else 0 end [Tarjetas]
 FROM [dbo].[OINV]  T0 INNER JOIN [dbo].[NNM1]  T2 ON T0.Series = T2.Series INNER JOIN ORCT T3 ON T0.ReceiptNum = T3.DocEntry INNER JOIN OJDT T4 ON T3.TransId = T4.TransId INNER JOIN JDT1 T5 ON T4.TransId = T5.TransId  WHERE T5.[Debit]<>0 AND T0.[DocDate] =[%0] and  left(T2.[SeriesName],2) in ('1P','2P','1E','2E')