Ayuda con Query de Pagos Vencidos

[former_member429163]

Buen dia estimados(as);

Nuevamente acudo a ustedes para que me brinde apoyo con el siguiente tema, hemos estado trabajando con query de pagos vencidos, pero al momento de montarlo al query manager y ejecutarlo me indica el error que adjunto. En el SQL el query se ejecuta bien.

Saludos,

[former_member188471 · Accepted solution]

Puedes postear el scritpt porfa.

[former_member429163 · Accepted solution]

Buenos dias CAB CANUL JULIAN;

Agradezco tu ayuda, te detallo el query:

DECLARE @Desde DATE, @Hasta DATE

SET @Desde = '20060101'

SET @Hasta = '20160131'

;WITH INV AS

(

  SELECT DocEntry, DocNum, CardCode, CardName, DocTotal, S.SeriesName AS Operacion, C.PymntGroup AS CondicionPago, DocDate, DocDueDate, I.GroupNum, ABS(I.DocTotal - I.Paidtodate) AS "Saldo"

  FROM

  OINV AS I

  INNER JOIN NNM1 S On S.Series = I.Series

  INNER JOIN OCTG C ON C.GroupNum = I.GroupNum

  WHERE

  ABS(I.DocTotal - I.Paidtodate)BETWEEN 0 AND 0.99

  AND I.DocDate >= @Desde

  AND I.DocDate < DATEADD(DD, 1, @Hasta)

  AND I.GroupNum NOT IN(19, 43, 47)

  AND I.DocSubType = '--'

  AND I.Canceled = 'N'

)

  SELECT I.CardCode, I.CardName, I.Operacion, I.DocNum, I.CondicionPago, I.DocTotal, I.DocDate, I.DocDueDate, MAX(PH.DocDate) AS UltimoPago, DATEDIFF(DD, I.DocDueDate, MAX(PH.DocDate)) AS DiasAtraso

  FROM

  RCT2 AS PD

  INNER JOIN INV I ON I.DocEntry = PD.DocEntry

  INNER JOIN ORCT PH ON PH.DocEntry = PD.DocNum

  WHERE

  PH.Canceled = 'N'

  GROUP BY I.CardCode, I.CardName, I.Operacion, I.DocNum, I.CondicionPago, I.DocTotal, I.DocDate, I.DocDueDate, PD.DocEntry, Saldo

[former_member188471 · Accepted solution]

****INTENTALO ASI***

declare @Desde datetime

declare @Hasta datetime

declare @tempo  int

SET @tempo=(SELECT * FROM OINV T0 WHERE T0.[DocDate] >=[%0] AND T0.[DocDate] <=[%1])

SET @Desde=(SELECT '[%0]')

SET @Hasta=(SELECT '[%1]')

;WITH INV AS

(

  SELECT DocEntry, DocNum, CardCode, CardName, DocTotal, S.SeriesName AS Operacion, C.PymntGroup AS CondicionPago, DocDate, DocDueDate, I.GroupNum, ABS(I.DocTotal - I.Paidtodate) AS "Saldo"

  FROM

  OINV AS I

  INNER JOIN NNM1 S On S.Series = I.Series

  INNER JOIN OCTG C ON C.GroupNum = I.GroupNum

  WHERE

  ABS(I.DocTotal - I.Paidtodate)BETWEEN 0 AND 0.99

  AND I.DocDate >= @Desde

  AND I.DocDate < DATEADD(DD, 1, @Hasta)

  AND I.GroupNum NOT IN(19, 43, 47)

  AND I.DocSubType = '--'

  AND I.Canceled = 'N'

)

  SELECT I.CardCode, I.CardName, I.Operacion, I.DocNum, I.CondicionPago, I.DocTotal, I.DocDate, I.DocDueDate, MAX(PH.DocDate) AS UltimoPago, DATEDIFF(DD, I.DocDueDate, MAX(PH.DocDate)) AS DiasAtraso

  FROM

  RCT2 AS PD

  INNER JOIN INV I ON I.DocEntry = PD.DocEntry

  INNER JOIN ORCT PH ON PH.DocEntry = PD.DocNum

  WHERE

  PH.Canceled = 'N'

  GROUP BY I.CardCode, I.CardName, I.Operacion, I.DocNum, I.CondicionPago, I.DocTotal, I.DocDate, I.DocDueDate, PD.DocEntry, Saldo

[former_member429163 · Accepted solution]

Buenos días CAB CANUL JULIAN;

Te comento que provee la modificación que me indicaste y me funciono a la perfección ya al momento de ejecutar el query en SAP ya me aparece los campos de agregar las fechas correspondientes.

Te agradezco la ayuda.

Saludos,

[former_member188471 · Accepted solution]

Excelente!