Query Antiguedad de Saldos Proveedores SAP 9.2

acontinuacion les traigo un query sql para utilizar en query manager la cual les mostrara el reporte de antiguedad de saldos de SAP de proveedores esta consulta aclarando solo cuadra totalmente con el balance de SAP recordemos que esta es una consulta dificil de cuadrar y va a depender de cuentas contables se tiene asociadas para los socios de negocios esta consulta ya fue validada por mi persona y si funciona correctamente como ya saber tan simple con un copy paste y listo.

DECLARE @VAR INT, @FECHAFIN DATETIME

SET @VAR = (SELECT TOP 1 A.TransId FROM [dbo].[JDT1] A WHERE A.RefDate <='[%0]')

SET @FECHAFIN = '[%0]'

SELECT Y3.SlpName, T0.CardCode, T0.CardName, T1.TransId'Asiento', T4.BaseRef 'Doc Interno', T4.Folionum'Folio', T1.RefDate, T1.TaxDate, T1.DueDate,

CASE

WHEN T3.DebHab = 'D' THEN (T1.Debit-T1.Credit-T3.ReconSum)

WHEN T3.DebHab = 'C' THEN (T1.Debit-T1.Credit+T3.ReconSum)

ELSE (T1.Debit-T1.Credit)

END 'Saldo',

CASE

WHEN DATEDIFF(DAY,T1.RefDate,@FECHAFIN) <= 30 and T3.DebHab = 'D' then T1.Debit-T1.Credit-T3.ReconSum

WHEN DATEDIFF(DAY,T1.RefDate,@FECHAFIN) <= 30 and T3.DebHab = 'C' then T1.Debit-T1.Credit+T3.ReconSum

WHEN DATEDIFF(DAY,T1.RefDate,@FECHAFIN) <= 30 then (T1.Debit-T1.Credit) end '0-30 dias',

CASE

WHEN DATEDIFF(DAY,T1.RefDate,@FECHAFIN) BETWEEN 31 AND 60 and T3.DebHab = 'D' then T1.Debit-T1.Credit-T3.ReconSum

WHEN DATEDIFF(DAY,T1.RefDate,@FECHAFIN) BETWEEN 31 AND 60 and T3.DebHab = 'C' then T1.Debit-T1.Credit+T3.ReconSum

WHEN DATEDIFF(DAY,T1.RefDate,@FECHAFIN) BETWEEN 31 AND 60 then (T1.Debit-T1.Credit) end '31-60 dias',

CASE

WHEN DATEDIFF(DAY,T1.RefDate,@FECHAFIN) BETWEEN 61 AND 90 and T3.DebHab = 'D' then T1.Debit-T1.Credit-T3.ReconSum

WHEN DATEDIFF(DAY,T1.RefDate,@FECHAFIN) BETWEEN 61 AND 90 and T3.DebHab = 'C' then T1.Debit-T1.Credit+T3.ReconSum

WHEN DATEDIFF(DAY,T1.RefDate,@FECHAFIN) BETWEEN 61 AND 90 then (T1.Debit-T1.Credit) end '61-90 dias',

CASE

WHEN DATEDIFF(DAY,T1.RefDate,@FECHAFIN) BETWEEN 91 AND 120 and T3.DebHab = 'D' then T1.Debit-T1.Credit-T3.ReconSum

WHEN DATEDIFF(DAY,T1.RefDate,@FECHAFIN) BETWEEN 91 AND 120 and T3.DebHab = 'C' then T1.Debit-T1.Credit+T3.ReconSum

WHEN DATEDIFF(DAY,T1.RefDate,@FECHAFIN) BETWEEN 91 AND 120 then (T1.Debit-T1.Credit) end '91-120 dias',

CASE

WHEN DATEDIFF(DAY,T1.RefDate,@FECHAFIN) > 120 and T3.DebHab = 'D' then T1.Debit-T1.Credit-T3.ReconSum

WHEN DATEDIFF(DAY,T1.RefDate,@FECHAFIN) > 120 and T3.DebHab = 'C' then T1.Debit-T1.Credit+T3.ReconSum

WHEN DATEDIFF(DAY,T1.RefDate,@FECHAFIN) > 120 then (T1.Debit-T1.Credit) end '+120 dias',

CASE

WHEN DATEDIFF(DAY,T1.RefDate,@FECHAFIN) BETWEEN 121 AND 365 and T3.DebHab = 'D' then (T1.Debit-T1.Credit-T3.ReconSum)*0.75

WHEN DATEDIFF(DAY,T1.RefDate,@FECHAFIN) BETWEEN 121 AND 365 and T3.DebHab = 'C' then (T1.Debit-T1.Credit+T3.ReconSum)*0.75

WHEN DATEDIFF(DAY,T1.RefDate,@FECHAFIN) BETWEEN 121 AND 365 then (T1.Debit-T1.Credit)*0.75

WHEN DATEDIFF(DAY,T1.RefDate,@FECHAFIN) > 365 and T3.DebHab = 'D' then (T1.Debit-T1.Credit-T3.ReconSum)

WHEN DATEDIFF(DAY,T1.RefDate,@FECHAFIN) > 365 and T3.DebHab = 'C' then (T1.Debit-T1.Credit+T3.ReconSum)

WHEN DATEDIFF(DAY,T1.RefDate,@FECHAFIN) > 365 then (T1.Debit-T1.Credit) end 'Deuda Dudosa',

CASE T1.TransType

WHEN '13' THEN (SELECT Y.Comments FROM OINV Y WHERE Y.TransId = T1.TransId)

WHEN '14' THEN (SELECT Y.Comments FROM ORIN Y WHERE Y.TransId = T1.TransId)

ELSE T1.LineMemo

END 'Comentarios'
,Y1.U_Facnum
,Y1.U_FacSerie

FROM dbo.OCRD T0

INNER JOIN dbo.JDT1 T1 ON T1.ShortName = T0.CardCode

INNER JOIN dbo.OACT T2 ON T2.AcctCode = T1.Account

INNER JOIN dbo.OJDT T4 ON T4.TransId = T1.TransId

LEFT JOIN dbo.OPCH Y1 ON Y1.TransId = T1.TransId

LEFT JOIN dbo.ORPC Y2 ON Y2.TransId = T1.TransId

LEFT JOIN dbo.OSLP Y3 ON Y3.SlpCode = Y1.SlpCode OR Y3.SlpCode = Y2.SlpCode

LEFT JOIN (SELECT X0.ShortName 'SN', X0.TransId 'TransId', SUM(X0.ReconSum)'ReconSum', X0.IsCredit 'DebHab', X0.TransRowId 'Linea'

FROM dbo.ITR1 X0

INNER JOIN dbo.OITR X1 ON X1.ReconNum = X0.ReconNum

WHERE X1.ReconDate <= @FECHAFIN AND X1.CancelAbs = ''

GROUP BY X0.ShortName, X0.TransId, X0.IsCredit, X0.TransRowId) T3 ON T3.TransId = T1.TransId AND T3.SN = T1.ShortName AND T3.Linea = T1.Line_ID

WHERE T0.CardType = 'S' AND T1.RefDate <= @FECHAFIN AND

(CASE

WHEN T3.DebHab = 'D' THEN (T1.Debit-T1.Credit-T3.ReconSum)

WHEN T3.DebHab = 'C' THEN (T1.Debit-T1.Credit+T3.ReconSum)

ELSE (T1.Debit-T1.Credit)

END) != '0'

ORDER BY  T0.CardCode, T1.TransId

FOR BROWSE

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *