Skip to content

Instantly share code, notes, and snippets.

@SidVal

SidVal/saldos-favor-clientes-v1.sql Secret

Created July 17, 2018 18:57
Download ZIP

Revisions

  1. SidVal created this gist Jul 17, 2018.
    62 changes: 62 additions & 0 deletions saldos-favor-clientes-v1.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,62 @@
    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'
    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.OINV Y1 ON Y1.TransId = T1.TransId
    LEFT JOIN dbo.ORIN 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 = 'C' 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 Y3.SlpCode, T0.CardCode, T1.TransId
    FOR BROWSE