Consulta para ver Importe Aplicado en Conciliación

[Raul_Gallardo]

Buen día:

Estoy tratando de obtener un Query que indique las facturas que fueron pagadas tanto con un pago directo a una factura o un pago como anticipo y después conciliado manual. ya tengo el enlace entre la OINV, ORCT y la ITR1. el problema es que con un pago o un anticipo cancelaron 1 o mas facturas y necesito saber que aporte le dio cada pago a cada factura. al ver las transacciones aplicadas en la factura obtengo ese dato pero a nivel BD no se donde obtenerlo. me pueden ayudar, en la imagen es el que resalto.

Query

SELECT rf.ExpSumFC, R.Reconnum, F.docnum, F.NumAtCard, RF.ReconSumFC, FrgnCurr, F.DocNum, FROM dbo.OITR AS R 
INNER JOIN dbo.ITR1 AS RF ON RF.ReconNum = R.ReconNum AND RF.SrcObjTyp = 13 and InitObjAbs is null and R.Canceled = 'N' 
INNER JOIN dbo.OINV AS F ON F.DocEntry = RF.SrcObjAbs
where F.CardCode = 'xxxx'
ORDER BY F.DocNum

por su ayuda muchas gracias.

[Willy_Caldero]

en tu query veo que haces referencia a la ITR1 que es el detalle de las reconciliaciones, el campo “ReconSum” o “ReconSumFC” es el monto reconciliado por cada documento o facctura.
El importe total de la reconciliación lo sacas de la OITR, campo “Total”. No entiendo que dato te esta faltando?

[Raul_Gallardo]

Gracias por tomarte el tiempo,

Es correcto ya tengo el dato del monto conciliado, mi pregunta directa seria de que pago o documento viene esa conciliación. no veo como enlazar el # de pago con el importe aplicado de una conciliación.

Estoy pensando algo asi:

QUERY

SELECT T3.DOCNUM AS 'NUMERO DE PAGO', T1.ExpSumFC, T0.Reconnum, T2.docnum, T2.NumAtCard, T1.ReconSumFC, FrgnCurr FROM dbo.OITR AS T0 
INNER JOIN dbo.ITR1 AS T1 ON T1.ReconNum = T0.ReconNum AND T1.SrcObjTyp = 13 and InitObjAbs is null and T0.Canceled = 'N' 
INNER JOIN dbo.OINV AS T2 ON T2.DocEntry = T1.SrcObjAbs
INNER join dbo.RCT2 AS T3 ON T2.DOCENTRY = T3.DOCENTRY
INNER JOIN DBO.ORCT AS T4 ON T4.DOCENTRY = T3.DOCNUM 
where T2.CardCode = 'XXXX'
ORDER BY T2.DocNum

pero solo sale el pago que si es aplicado directo a la factura.

te dejo un caso:

Tengo la factura siguiente con un pago que no completo el monto de la factura, por tanto hay otro pago u documento aplicado.

cuando veo las transacciones aplicadas efectivamente esta el pago de la linea 2 y hay otro pago, el de la linea 3:

Este ultimo es un pago recibido pero no se aplico a ninguna factura (es un anticipo) y posteriormente se aplico a varias facturas.

transacciones aplicadas a este pago:

Al final necesito saber de ese pago (anticipo) a que facturas se aplico y que monto aporto a cada una.

[Willy_Caldero]

Primero toma en cuenta que tienes 2 tipos de reconciliaciones:

1. las automáticas que están relacionadas a un documento, grlte un pago, esto lo identificas por el campo “IsSystem” de la OITR, si es “Y” es automático. En estos casos podrias sacar el pago desde los campos “SrcObjAbs” = 24 (Pagos recibidos), y con el campo “InitObjAbs” tienes el Identificador del Pago. Claro que si el Pago conecta un anticipo, este ya no aparecería en tu reporte.

2. en el caso de los manuales es mas complejo, por que puedes seleccionar 2 o mas pagos, y conectar a 2 o mas facturas, con lo que no tienes manera de identificar a que pago especificamente corresponde el monto conciliado, ahi solo te quedaria referenciar o el ID de la reconciliacion, o hacer una concatenacion de los pagos o anticipos.

La otra opciones ver que hace SAP cuando sacas las transacciones aplicadas a una factura.
Ahi te aparecen 2 querys, uno para la linea cabecera de la factura con el totalizador de importes:

SELECT T0.[TransType], T0.[BaseRef], T0.[TransId], T0.[Line_ID], MIN(T2.[ReconType]), MIN(T2.[ReconRule1]), 
       MIN(T2.[ReconRule2]), MIN(T2.[ReconRule3]), T0.[RefDate], T0.[DueDate], MAX(T2.[ReconDate]), 
	   T0.[BatchNum], T0.[CreatedBy], T0.[Ref1], T0.[Ref2], T0.[Ref3Line], T0.[ContraAct], T0.[LineMemo], 
	   SUM(T1.[CashDisSum]), T0.[Debit], T0.[Credit], T0.[BalDueDeb], T0.[BalDueCred], 
	   SUM(T1.[ReconSum]), T0.[SYSCred], T0.[SYSDeb], T0.[BalScDeb], T0.[BalScCred], 
	   SUM(T1.[ReconSumSC]), T0.[FCDebit], T0.[FCCredit], T0.[BalFcDeb], T0.[BalFcCred], 
	   SUM(T1.[ReconSumFC]), MAX(T2.[ReconNum]), T0.[ShortName], T0.[Account], T0.[FCCurrency], 
	   T2.[ReconCurr], COUNT(T2.[ReconNum]), T1.[IsCredit], MAX(T2.[OldMatNum]) 
FROM  [dbo].[JDT1] T0   
LEFT OUTER  JOIN [dbo].[ITR1] T1  ON  T0.[Line_ID] = T1.[TransRowId]  AND  T0.[TransId] = T1.[TransId]    
LEFT OUTER  JOIN [dbo].[OITR] T2  ON  T1.[ReconNum] = T2.[ReconNum]   
WHERE (T0.[TransId] = (@TransID_Asiento_de_la_factura) ) AND  T0.[ShortName] <> T0.[Account]  
GROUP BY T0.[TransType], T0.[BaseRef], T0.[TransId], T0.[Line_ID], T0.[RefDate], T0.[DueDate], 
         T0.[BatchNum], T0.[CreatedBy], T0.[Ref1], T0.[Ref2], T0.[Ref3Line], T0.[ContraAct], 
		 T0.[LineMemo], T0.[Debit], T0.[Credit], T0.[BalDueDeb], T0.[BalDueCred], T0.[SYSCred], 
		 T0.[SYSDeb], T0.[BalScDeb], T0.[BalScCred], T0.[FCDebit], T0.[FCCredit], T0.[BalFcDeb], 
		 T0.[BalFcCred], T0.[ShortName], T0.[Account], T0.[FCCurrency], T2.[ReconCurr], T1.[IsCredit] 
ORDER BY T0.[TransId],T0.[Line_ID]

Y el otro query que te da el detalle de las transacciones aplicadas:

SELECT T1.[TransType], T1.[BaseRef], T1.[TransId], T1.[Line_ID], T2.[ReconType], 
       T2.[ReconRule1], T2.[ReconRule2], T2.[ReconRule3], T1.[RefDate], T1.[DueDate], 
	   T2.[ReconDate], T1.[BatchNum], T1.[CreatedBy], T1.[Ref1], T1.[Ref2], T1.[Ref3Line], 
	   T1.[ContraAct], T1.[LineMemo], T0.[CashDisSum], T1.[Debit], T1.[Credit], T1.[BalDueDeb], 
	   T1.[BalDueCred], T0.[ReconSum], T1.[SYSCred], T1.[SYSDeb], T1.[BalScDeb], T1.[BalScCred], 
	   T0.[ReconSumSC], T1.[FCDebit], T1.[FCCredit], T1.[BalFcDeb], T1.[BalFcCred], T0.[ReconSumFC], 
	   T2.[ReconNum], T1.[ShortName], T1.[Account], T1.[FCCurrency], T2.[ReconCurr], T2.[ReconNum], 
	   T0.[IsCredit], T2.[OldMatNum], T3.[ReportID], T3.[ActStatus] 
FROM  [dbo].[ITR1] T0  
INNER  JOIN [dbo].[JDT1] T1  ON  T0.[TransRowId] = T1.[Line_ID]  AND  T0.[TransId] = T1.[TransId]   
INNER  JOIN [dbo].[OITR] T2  ON  T0.[ReconNum] = T2.[ReconNum]    
LEFT OUTER  JOIN [dbo].[ECM2] T3  ON  T2.[ReconNum] = T3.[SrcObjAbs]  AND  T3.[SrcObjType] = 321  
WHERE  EXISTS (
              SELECT U0.[ReconNum] 
			    FROM  [dbo].[ITR1] U0  
			   WHERE U0.[TransId] = (@TransID_Asiento_de_la_factura) AND  U0.[TransRowId] = (0)  AND  T2.[ReconNum] = U0.[ReconNum]  
			   )  
		AND  (T1.[TransId] <> (@TransID_Asiento_de_la_factura)  OR  T1.[Line_ID] <> (0) )  
ORDER BY T2.[CreateDate]

Son algo complicado estos querys, pero por ahí va el tema.

[Raul_Gallardo]

Muchas gracias amigo, creo que con estos querys podre trabajar, aunque como bien decis se complica cuando hay una conciliación dentro de otra. cuando termine te regreso mi resultado final para que lo tengas también.

saludos desde Guatemala