Query para sacar los Pagos Recibidos

SAP Business One
1

Buenas estimados,

Por favor su ayuda necesito un query de pagos recibidos actualmente tengo uno pero no funciona de manera correcta ya que no saca todos los valores correctos.

O hay alguna otra forma de ver los pagos recibidos?

SELECT 
	T0."DocNum",
	T0."CardCode",
	T0."CardName",
	T0."DocDate",
	T0."TrsfrAcct",
	T0."CheckAcct",
	T0."CashAcct",
	T0."DocTotal",
    T0."U_LS_MOTIVO",
    T0."Series",
    T0."U_SYP_PTCC",
    T2."DocNum",
    T2."DocDate",
    T2."DocTotal"
    
FROM "ORCT" T0 
INNER JOIN "RCT2" T1 ON T0."DocEntry" = T1."DocNum"
INNER JOIN "OINV" T2 ON T1."DocEntry" = T2."DocEntry"
WHERE T0."Canceled"='N' and T2."DocDate" Between '[%1]' and '[%2]'
2

Recuerda formatear el código sql adecuadamente, por favor.
En este te lo he editado.

3 
SELECT  T0."DocEntry",T0."CounterRef" as "Recibo",T1."InvoiceId",T0."DocDate" as "Fecha Recibo", T0."CardCode", T0."CardName", T0."DocCurr", T0."Comments",
		case when T1."InvoiceId" = 0 
		then 
			case when T0."DocCurr"='USD' then T0."DocTotalFC" else T0."DocTotal" end
		else 0 end as "Monto_Recibo"
		,T0."U_NoRet", T2."DocDate", T2."U_DoctoNo", T2."NumAtCard",
		case when T0."DocCurr"='USD' then T1."AppliedFC" else T1."SumApplied" end as "Monto_Aplicado",
		case when T0."DocCurr"='USD' then T2."DocTotalFC" else T2."DocTotal" end as "Total Factura",T0."DocType"
FROM ORCT T0
left JOIN RCT2 T1 ON T0."DocEntry" = T1."DocNum"
left join OINV T2 on T1."DocEntry"= T2."DocEntry"  and T1."InvType" =13
left join OCRD T3 on T0."CardCode"=T3."CardCode" 
WHERE T0."DocDate" >= [%1] and  T0."DocDate" <= [%2] and  T0."Canceled" ='N'   and T0."DocType" = 'C' and T1."InvType" =13

union all

SELECT  T0."DocEntry",T0."CounterRef" as "Recibo",T1."InvoiceId",T0."DocDate" as "Fecha Recibo", T0."CardCode", T0."CardName", T0."DocCurr", T0."Comments",
		case when T1."InvoiceId" = 0 
		then 
			case when T0."DocCurr"='USD' then T0."DocTotalFC" else T0."DocTotal" end
		else 0 end as "Monto_Recibo"
		,T0."U_NoRet", T2."DocDate", T2."U_DoctoNo", T2."NumAtCard",
		case when T0."DocCurr"='USD' then T1."AppliedFC"*-1 else T1."SumApplied"*-1 end as "Monto_Aplicado",
		case when T0."DocCurr"='USD' then T2."DocTotalFC"*-1 else T2."DocTotal"*-1 end as "Total Factura",T0."DocType"
FROM ORCT T0
left JOIN RCT2 T1 ON T0."DocEntry" = T1."DocNum"
left join orin T2 on T1."DocEntry"= T2."DocEntry"  and T1."InvType" =14
left join OCRD T3 on T0."CardCode"=T3."CardCode" 
WHERE T0."DocDate" >= [%1] and  T0."DocDate" <= [%2] and  T0."Canceled" ='N'   and T0."DocType" = 'C' and T1."InvType" =14
ORDER BY "DocCurr", "DocEntry" ;

Por si te sirve de algo, este es mi codigo.

1 me gusta