Query Manager - Pagos Efectuados

SAP Business One
2

Checa el Query espero te ayude

DECLARE @FecIni AS DATETIME 
DECLARE @FecFin AS DATETIME 
-- SELECT @FecIni = F0.DocDate, @FecFin = F0.DocDate FROM OVPM F0 WHERE F0.DocDate = '[%0]' AND F0.DocDate = '[%1]'
SET @FecIni = CONVERT(DATETIME, '[%0]', 112) 
SET @FecFin = CONVERT(DATETIME, '[%1]', 112)

SELECT     P1.DocNum AS 'Factura',
           P1.DocDate AS 'Fecha Factura',
           P1.CardName AS 'Proveedor', 
           P1.LicTradNum As 'RFC', 
           P1.NumAtCard AS 'Factura',
           P1.Importe, 
           P1.IVA, 
           P1.WTSum AS 'Retención IVA', 
           P1.Max1099 as 'Total', 
           P2.DocTotal as 'Importe pagado', 
           'Pago' = 'PP'+CAST(P2.DocNum AS VARCHAR)+' - '+'ID'+CAST(P1.ReconNum AS VARCHAR),
           P2.DocDate AS 'Fecha pago'
FROM 
 (SELECT DISTINCT T0.DocNum, 
             T1.ReconNum, 
             T0.CardName, 
             T0.DocDate, 
             T3.LicTradNum, 
             T0.NumAtCard,
             T0.Canceled,
             T2.IsSystem,
             T2.ReconType, 
             'Importe' = IIF(T0.DpmAmnt = 0, T0.Max1099 - T0.VatSum, T0.DpmAmnt - T0.VatSum), 
             'IVA' = IIF(T0.DpmAmnt = 0, T0.VatSum, T0.VatSum + T0.Max1099 - T0.DpmAmnt), 
             T0.WTSum, 
             T0.Max1099  
 FROM OPCH T0 
  INNER JOIN ITR1 T1 ON T0.TransID = T1.TransID 
  INNER JOIN OITR T2 ON T1.ReconNum = T2.ReconNum 
  LEFT JOIN OCRD T3 ON T0.CardCode = T3.CardCode 
 WHERE T0.Canceled = 'N' AND 
           T2.IsSystem = 'N' AND
           T2.ReconType = '0'  AND 
           T1.SrcObjTyp <> '204' AND 
           T1.SrcObjTyp <> '19' AND 
           T1.SrcObjTyp <> '30' AND 
           T1.SrcObjTyp <> '14' AND 
           T1.SrcObjTyp <> '321' AND 
           T1.SrcObjTyp <> '24' AND 
           T1.SrcObjTyp <> '13' AND  
           T1.SrcObjTyp <> '57') P1

   INNER JOIN

 (SELECT DISTINCT T0.DocNum, 
             T1.ReconNum,       
             T0.DocTotal,
             T0.DocDate,
             T1.SrcObjTyp
  FROM OVPM T0 
   INNER JOIN ITR1 T1 ON T0.TransID = T1.TransID 
   INNER JOIN OITR T2 ON T1.ReconNum = T2.ReconNum 
WHERE T1.SrcObjTyp <> '204' AND 
           T1.SrcObjTyp <> '19' AND 
           T1.SrcObjTyp <> '30' AND 
           T1.SrcObjTyp <> '14' AND 
           T1.SrcObjTyp <> '321' AND 
           T1.SrcObjTyp <> '24' AND 
           T1.SrcObjTyp <> '13' AND  
           T1.SrcObjTyp <> '57' AND T2.ReconType = '0' AND T2.IsSystem = 'N')  P2 ON P1.ReconNum = P2.ReconNum
WHERE  P2.DocDate Between @FecIni and @FecFin
SET @FecIni = CONVERT(DATETIME, '[%0]', 112) 
SET @FecFin = CONVERT(DATETIME, '[%1]', 112)
SELECT     P1.DocNum AS 'Factura',
           P1.DocDate AS 'Fecha Factura',
           P1.CardName AS 'Proveedor', 
           P1.LicTradNum As 'RFC', 
           P1.NumAtCard AS 'Factura',
           P1.Importe, 
           P1.IVA, 
           P1.WTSum AS 'Retención IVA', 
           P1.Max1099 as 'Total', 
           P2.DocTotal as 'Importe pagado', 
           'Pago' = 'PP'+CAST(P2.DocNum AS VARCHAR)+' - '+'ID'+CAST(P1.ReconNum AS VARCHAR),
           P2.DocDate AS 'Fecha pago'
FROM 
 (SELECT DISTINCT T0.DocNum, 
             T1.ReconNum, 
             T0.CardName, 
             T0.DocDate, 
             T3.LicTradNum, 
             T0.NumAtCard,
             T0.Canceled,
             T2.IsSystem,
             T2.ReconType, 
             'Importe' = IIF(T0.DpmAmnt = 0, T0.Max1099 - T0.VatSum, T0.DpmAmnt - T0.VatSum), 
             'IVA' = IIF(T0.DpmAmnt = 0, T0.VatSum, T0.VatSum + T0.Max1099 - T0.DpmAmnt), 
             T0.WTSum, 
             T0.Max1099  
 FROM OPCH T0 
  INNER JOIN ITR1 T1 ON T0.TransID = T1.TransID 
  INNER JOIN OITR T2 ON T1.ReconNum = T2.ReconNum 
  LEFT JOIN OCRD T3 ON T0.CardCode = T3.CardCode 
 WHERE T0.Canceled = 'N' AND 
           T2.IsSystem = 'N' AND
           T2.ReconType = '0'  AND 
           T1.SrcObjTyp <> '204' AND 
           T1.SrcObjTyp <> '19' AND 
           T1.SrcObjTyp <> '30' AND 
           T1.SrcObjTyp <> '14' AND 
           T1.SrcObjTyp <> '321' AND 
           T1.SrcObjTyp <> '24' AND 
           T1.SrcObjTyp <> '13' AND  
           T1.SrcObjTyp <> '57') P1

   INNER JOIN

 (SELECT DISTINCT T0.DocNum, 
             T1.ReconNum,       
             T0.DocTotal,
             T0.DocDate,
             T1.SrcObjTyp
  FROM OVPM T0 
   INNER JOIN ITR1 T1 ON T0.TransID = T1.TransID 
   INNER JOIN OITR T2 ON T1.ReconNum = T2.ReconNum 
WHERE T1.SrcObjTyp <> '204' AND 
           T1.SrcObjTyp <> '19' AND 
           T1.SrcObjTyp <> '30' AND 
           T1.SrcObjTyp <> '14' AND 
           T1.SrcObjTyp <> '321' AND 
           T1.SrcObjTyp <> '24' AND 
           T1.SrcObjTyp <> '13' AND  
           T1.SrcObjTyp <> '57' AND T2.ReconType = '0' AND T2.IsSystem = 'N')  P2 ON P1.ReconNum = P2.ReconNum
WHERE  P2.DocDate Between @FecIni and @FecFin

o bien checa este link se trata del tema que buscas

saludos