Query Manager - Pagos Efectuados

[Leandro_Barze]

Buenas tardes compañeros, estoy haciendo el siguiente query manager para pagos efectuados, y necesito agregarle el importe total de las retenciones. Solo veo el de Doctotal que no lo incluye. Con que campo puedo incluirlo?

Muchas gracias

SELECT T0.“DocNum”
, T0.“TaxDate”
, T0.“CardName”
, T0.“DocTotal”
, T0.“JrnlMemo”
FROM OVPM T0
WHERE
T0.“DocDate” >=[%0]
AND
T0.“DocDate” <=[%1]
AND
T0.“JrnlMemo” NOT Like ‘%%Cancelado%%’

[lxrealmtz]

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

Error en Query Pagos Efectuados

Buenas tardes amigos, que tengan un buen día, hoy vengo a pedir de su sabiduría, he estado trabajando en un query de pagos efectuados reconciliados, y todo sale bien, excepto en la parte del where final, donde me dice el problema, “error al intentar convertir fecha a cadena” pero las únicas fechas que uso son 2 y ya use el convert, el cast, pero no encuentro la solución, espero me pueden ayudar. Saludos. Pego el query para que observen su estructura. SELECT P1.DocNum AS 'Factura', …

saludos

[anon63829482]

Saludos cordiales hermano, revisa la tabla pch5, si mas no recuerdo ahí es donde caen las retenciones, de todas maneras el lunes te puedo enviar las tablas que yo uso para ese tipo de reporte, es más lo tengo hecho en crystal report, si te sirve

[Leandro_Barze]

dale, me encantaría que me lo pases!!