Query Manager - Pagos Efectuados

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%%’

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

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

1 me gusta

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

Este tema se cerró automáticamente 30 días después de la última publicación. No se permiten nuevas respuestas.