cancel
Showing results for 
Search instead for 
Did you mean: 

Ayuda en Formato de Impresión de Estado de Cuenta Cliente

Former Member
0 Kudos

Buenas Tardes, espero que alguien me pueda ayudar por favor en cuanto al formato de Impresión del Estado de Cuenta de Cliente.

En la Antigüedad de Saldo de Clientes en el Informe de cliente de cobro (una página por cliente) yo modifiqué mi formato de impresión a como me lo solicitaron, pero resulta que la morosidad no quieren verla solamente en 4 intervalos, sino en 13 intervalos o sea 0-30, 31-60, 61-90, 91-120, 121-150, 151-180, 181-210, 211-240, 241-270, 271-300, 301-330,331-360, 361+.

Ahora bien para eso necesitaría más campos de formula o no se porque en SAP están como variables y solamente para 4 intervalos.  Anexo la pantalla del xcel en foto en la parte de abajo de excel los intervalos que tengo que agregarle y el formato que ya modifiqué conforme a lo que SAP me proporciona en la imagen que dice erronea para ver como me pueden ayudar a poder obtener el resultado necesitado.

Saludos y gracias de Antemano.

Accepted Solutions (0)

Answers (1)

Answers (1)

rjovel
Active Participant
0 Kudos

esto te puede ayudar

solo que tendras que hacerlo en crystal reports.

asi cumples con lo que te solicitan!

select

                    case T0.transtype

                                        when '13' then 'Factura'

                                        when '14' then 'Nota Credito'

                                        when '24' then 'Pagos'

                                        else 'Otro'

                                        end 'Tipo Trans',

                    Ref1 'Referencia',

                    CONVERT(VARCHAR(10), RefDate, 103) 'Fecha Docto' ,

                    CONVERT(VARCHAR(10), duedate, 103) 'Vence Docto',

                              isnull(CASE

                                        when (DATEDIFF(dd,refdate,current_timestamp))+1 < 31

                                        then

                                        case

                                        when credit <> 0 then isnull(-credit,0)

                                        else isnull(debit,0)

                                        end

                                        end,0) "0-30 Dias",

                              isnull(case when ((datediff(dd,refdate,current_timestamp))+1 > 30

                                        and (datediff(dd,refdate,current_timestamp))+1< 61)

                                        then

                                        case

                                        when credit <> 0 then isnull(-credit,0)

                                        else isnull(debit,0)

                                        end

                                        end,0) "31 to 60 Dias",

                              isnull(case when ((datediff(dd,refdate,current_timestamp))+1 > 60

                                        and (datediff(dd,refdate,current_timestamp))+1< 91)

                                        then

                                        case

                                        when credit <> 0 then isnull(-credit,0)

                                        else isnull(debit,0)

                                        end

                                        end,0) "61 to 90 Dias",

                              isnull(CASE

                                        when (DATEDIFF(dd,refdate,current_timestamp))+1 > 90

                                        then

                                        case

                                        when credit= 0 then isnull(debit,0)

                                        when debit= 0 then isnull(-credit,0)

                                        end

                                        end,0) "90 + Dias"

from          dbo.JDT1 T0 with(nolock)

                    INNER JOIN

                    dbo.OCRD T1  with(nolock)

                    ON T0.shortname = T1.cardcode

                    and T1.cardtype = 'c'

where          T0.intrnmatch = '0'

                    and T0.BALDUEDEB != T0.BALDUECRED

Former Member
0 Kudos

Hola Roger, y gracias por escribir, realmente en parte me ayuda mucho lo que me mandaste, ahora bien, recuerda que como todo estado de cuentas necesito obnetenerlo por fechas, y si obtengo esto por fechas necesito tener un saldo inicial como viste en mi ejemplo del excel.  Lo que me mandaste lo necesito a 13 meses, así que le agregué a lo que tu me pudiste.  Otra cosa, el depto maneja toda la información, o sea que no es solo de lo pendiente sino el estado debe ser con todas las transacciones, como me puedes ayudar a que si es filtrado por fechas me obtenga un acumulativo para que sea saldo inicial?

Te mando el query depurado para que si me puedes ayudar sea sobre este que ya modifiqué.

Saludos y gracias

select

                    case T0.transtype

                                        when '13' then 'Factura'

                                        when '14' then 'Nota Credito'

                                        when '24' then 'Pagos'

                                        when '30' then 'Ajustes'

                                        else 'Otro'

                                        end 'Tipo Trans',

 

                    isnull(Ref1,'SI') 'Referencia',

                    CONVERT(VARCHAR(10), RefDate, 103) 'Fecha Docto' ,

                    CONVERT(VARCHAR(10), duedate, 103) 'Vence Docto',

                   T1.Balance,T0.Debit, T0.Credit,

                              isnull(CASE

                                        when (DATEDIFF(dd,refdate,current_timestamp))+1 < 31

                                        then

                                        case

                                        when credit <> 0 then isnull(-credit,0)

                                        else isnull(debit,0)

                                        end

                                        end,0) "0-30 Dias",

                              isnull(case when ((datediff(dd,refdate,current_timestamp))+1 > 30

                                        and (datediff(dd,refdate,current_timestamp))+1< 61)

                                        then

                                        case

                                        when credit <> 0 then isnull(-credit,0)

                                        else isnull(debit,0)

                                        end

                                        end,0) "31 to 60 Dias",

                              isnull(case when ((datediff(dd,refdate,current_timestamp))+1 > 60

                                        and (datediff(dd,refdate,current_timestamp))+1< 91)

                                        then

                                        case

                                        when credit <> 0 then isnull(-credit,0)

                                        else isnull(debit,0)

                                        end

                                        end,0) "61 to 90 Dias",

                                 isnull(case when ((datediff(dd,refdate,current_timestamp))+1 > 90

                                        and (datediff(dd,refdate,current_timestamp))+1< 121)

                                        then

                                        case

                                        when credit <> 0 then isnull(-credit,0)

                                        else isnull(debit,0)

                                        end

                                        end,0) "91 to 120 Dias",

                                 isnull(case when ((datediff(dd,refdate,current_timestamp))+1 > 120

                                        and (datediff(dd,refdate,current_timestamp))+1< 151)

                                        then

                                        case

                                        when credit <> 0 then isnull(-credit,0)

                                        else isnull(debit,0)

                                        end

                                        end,0) "121 to 150 Dias", 

                                isnull(case when ((datediff(dd,refdate,current_timestamp))+1 > 150

                                        and (datediff(dd,refdate,current_timestamp))+1< 181)

                                        then

                                        case

                                        when credit <> 0 then isnull(-credit,0)

                                        else isnull(debit,0)

                                        end

                                        end,0) "151 to 180 Dias", 

                               isnull(case when ((datediff(dd,refdate,current_timestamp))+1 > 180

                                        and (datediff(dd,refdate,current_timestamp))+1< 211)

                                        then

                                        case

                                        when credit <> 0 then isnull(-credit,0)

                                        else isnull(debit,0)

                                        end

                                        end,0) "181 to 210 Dias", 

                              isnull(case when ((datediff(dd,refdate,current_timestamp))+1 > 210

                                        and (datediff(dd,refdate,current_timestamp))+1< 241)

                                        then

                                        case

                                        when credit <> 0 then isnull(-credit,0)

                                        else isnull(debit,0)

                                        end

                                        end,0) "211 to 240 Dias",

                              isnull(case when ((datediff(dd,refdate,current_timestamp))+1 > 240

                                        and (datediff(dd,refdate,current_timestamp))+1< 271)

                                        then

                                        case

                                        when credit <> 0 then isnull(-credit,0)

                                        else isnull(debit,0)

                                        end

                                        end,0) "241 to 270 Dias", 

                              isnull(case when ((datediff(dd,refdate,current_timestamp))+1 > 270

                                        and (datediff(dd,refdate,current_timestamp))+1< 301)

                                        then

                                        case

                                        when credit <> 0 then isnull(-credit,0)

                                        else isnull(debit,0)

                                        end

                                        end,0) "271 to 300 Dias", 

                             isnull(case when ((datediff(dd,refdate,current_timestamp))+1 > 300

                                        and (datediff(dd,refdate,current_timestamp))+1< 331)

                                        then

                                        case

                                        when credit <> 0 then isnull(-credit,0)

                                        else isnull(debit,0)

                                        end

                                        end,0) "301 to 330 Dias",

                              isnull(case when ((datediff(dd,refdate,current_timestamp))+1 > 330

                                        and (datediff(dd,refdate,current_timestamp))+1< 361)

                                        then

                                        case

                                        when credit <> 0 then isnull(-credit,0)

                                        else isnull(debit,0)

                                        end

                                        end,0) "331 to 360 Dias",                                              

                              isnull(CASE

                                        when (DATEDIFF(dd,refdate,current_timestamp))+1 > 360

                                        then

                                        case

                                        when credit= 0 then isnull(debit,0)

                                        when debit= 0 then isnull(-credit,0)

                                        end

                                        end,0) "360 + Dias"

                                   

from          dbo.JDT1 T0 with(nolock)

                    INNER JOIN

                    dbo.OCRD T1  with(nolock)

                    ON T0.shortname = T1.cardcode

                    and T1.cardtype = 'c'

where          T0.intrnmatch = '0'

                    and T0.BALDUEDEB != T0.BALDUECRED

               /* and T1.cardcode ='[%0]' */

order by 3

rjovel
Active Participant
0 Kudos

prueba con esto por favor.

declare @f1 datetime, @f2 datetime

set @f1='2013-01-01'

set @f2='2013-06-30'

select

                    case T0.transtype

                                        when '13' then 'Factura'

                                        when '14' then 'Nota Credito'

                                        when '24' then 'Pagos'

                                        when '30' then 'Ajustes'

                                        else 'Otro'

                                        end 'Tipo Trans',

                    isnull(Ref1,'SI') 'Referencia',

                    CONVERT(VARCHAR(10), RefDate, 103) 'Fecha Docto' ,

                    CONVERT(VARCHAR(10), duedate, 103) 'Vence Docto',

                   (select sum(debit-credit) from jdt1 where ShortName=t0.ShortName and refdate<@f1) as 'Saldo Inicial',

                                           T1.Balance,

                                           T0.Debit as 'Debito',

                                           T0.Credit as 'Credito',

                                           T0.BALDUECRED as 'Saldo Vencido',

                              isnull(CASE

                                        when (DATEDIFF(dd,refdate,current_timestamp))+1 < 31

                                        then

                                        case

                                        when credit <> 0 then isnull(-credit,0)

                                        else isnull(debit,0)

                                        end

                                        end,0) "0-30 Dias",

                              isnull(case when ((datediff(dd,refdate,current_timestamp))+1 > 30

                                        and (datediff(dd,refdate,current_timestamp))+1< 61)

                                        then

                                        case

                                        when credit <> 0 then isnull(-credit,0)

                                        else isnull(debit,0)

                                        end

                                        end,0) "31 to 60 Dias",

                              isnull(case when ((datediff(dd,refdate,current_timestamp))+1 > 60

                                        and (datediff(dd,refdate,current_timestamp))+1< 91)

                                        then

                                        case

                                        when credit <> 0 then isnull(-credit,0)

                                        else isnull(debit,0)

                                        end

                                        end,0) "61 to 90 Dias",

                                 isnull(case when ((datediff(dd,refdate,current_timestamp))+1 > 90

                                        and (datediff(dd,refdate,current_timestamp))+1< 121)

                                        then

                                        case

                                        when credit <> 0 then isnull(-credit,0)

                                        else isnull(debit,0)

                                        end

                                        end,0) "91 to 120 Dias",

                                 isnull(case when ((datediff(dd,refdate,current_timestamp))+1 > 120

                                        and (datediff(dd,refdate,current_timestamp))+1< 151)

                                        then

                                        case

                                        when credit <> 0 then isnull(-credit,0)

                                        else isnull(debit,0)

                                        end

                                        end,0) "121 to 150 Dias",

                                isnull(case when ((datediff(dd,refdate,current_timestamp))+1 > 150

                                        and (datediff(dd,refdate,current_timestamp))+1< 181)

                                        then

                                        case

                                        when credit <> 0 then isnull(-credit,0)

                                        else isnull(debit,0)

                                        end

                                        end,0) "151 to 180 Dias",

                               isnull(case when ((datediff(dd,refdate,current_timestamp))+1 > 180

                                        and (datediff(dd,refdate,current_timestamp))+1< 211)

                                        then

                                        case

                                        when credit <> 0 then isnull(-credit,0)

                                        else isnull(debit,0)

                                        end

                                        end,0) "181 to 210 Dias",

                              isnull(case when ((datediff(dd,refdate,current_timestamp))+1 > 210

                                        and (datediff(dd,refdate,current_timestamp))+1< 241)

                                        then

                                        case

                                        when credit <> 0 then isnull(-credit,0)

                                        else isnull(debit,0)

                                        end

                                        end,0) "211 to 240 Dias",

                              isnull(case when ((datediff(dd,refdate,current_timestamp))+1 > 240

                                        and (datediff(dd,refdate,current_timestamp))+1< 271)

                                        then

                                        case

                                        when credit <> 0 then isnull(-credit,0)

                                        else isnull(debit,0)

                                        end

                                        end,0) "241 to 270 Dias",

                              isnull(case when ((datediff(dd,refdate,current_timestamp))+1 > 270

                                        and (datediff(dd,refdate,current_timestamp))+1< 301)

                                        then

                                        case

                                        when credit <> 0 then isnull(-credit,0)

                                        else isnull(debit,0)

                                        end

                                        end,0) "271 to 300 Dias",

                             isnull(case when ((datediff(dd,refdate,current_timestamp))+1 > 300

                                        and (datediff(dd,refdate,current_timestamp))+1< 331)

                                        then

                                        case

                                        when credit <> 0 then isnull(-credit,0)

                                        else isnull(debit,0)

                                        end

                                        end,0) "301 to 330 Dias",

                              isnull(case when ((datediff(dd,refdate,current_timestamp))+1 > 330

                                        and (datediff(dd,refdate,current_timestamp))+1< 361)

                                        then

                                        case

                                        when credit <> 0 then isnull(-credit,0)

                                        else isnull(debit,0)

                                        end

                                        end,0) "331 to 360 Dias",                                             

                              isnull(CASE

                                        when (DATEDIFF(dd,refdate,current_timestamp))+1 > 360

                                        then

                                        case

                                        when credit= 0 then isnull(debit,0)

                                        when debit= 0 then isnull(-credit,0)

                                        end

                                        end,0) "360 + Dias"

                                  

from          dbo.JDT1 T0 with(nolock)

                    INNER JOIN

                    dbo.OCRD T1  with(nolock)

                    ON T0.shortname = T1.cardcode

                    and T1.cardtype = 'c'

where          --T0.intrnmatch = '0'

                    --and

                                                  --T0.BALDUEDEB != T0.BALDUECRED

               t0.RefDate>=@f1 and t0.RefDate<=@f2

                                 and T1.cardcode ='CL100728'

order by 3

Former Member
0 Kudos

Hola Roger muchas gracias por tu ayuda, me ha sido bastante buena la verdad.

Me preguntaba si tienes alguna idea de como puedo realizar esta parte ya que he intentado con un procedimiento almacenado pero no hace nada.

Buenos Días, espero me puedan ayudar ya que tengo que ponerle unas validaciones a mis documentos preliminares en la entrega.

Resulta que yo tengo un campo UDF que se llama Total de Tallas (U_UDF_Tallas), este debe ser el mismo que mi cantidad.  Ahora bien tengo 2 problemas,

1. Cuando total de tallas (suma mis udf de tallas) suma lo que tienen mis udf de tallas (lo hace bien) solo lo hace si yo modifico algún campo en especifico o cuando aprieto la búsqueda formulada, no hay forma que el a medida que yo vaya introduciendo las cantidades en los diferentes campos de talla me los vaya cambiando en el campo de total de talla sin yo tener que estar apretando a cada rato esa lupita para ver lo que hay?

Aqui pongo mi consulta que tiene formulada en la consulta total de tallas.

Select (Select  $[DLN1.U_CantTalla1.number]) +

          (Select $[DLN1.U_CantTalla2.number]) +

          (Select $[DLN1.U_CantTalla3.number]) +

          (Select $[DLN1.U_CantTalla4.number]) +

          (Select $[DLN1.U_CantTalla5.number]) +

          (Select $[DLN1.U_CantTalla6.number]) +

          (Select $[DLN1.U_CantTalla7.number]) +

          (Select $[DLN1.U_CantTalla8.number]) +

          (Select $[DLN1.U_CantTalla9.number])

2.  Necesito que por medio de un Transaction en la entrega que es objeto 15, antes de yo darle grabar como preliminar me valide que si la cantidad no es igual al total de tallas no me deje crear el preliminar ya que de este preliminar yo arrojo un packing que por error humano puede que el usuario no se fije que puso demás en las tallas o de menos y el Total de Tallas (U_UDF_Tallas) antes de generar el documento preliminar de entrega debe ser igual a la cantidad de la línea (Quantity).

Alguien me puede ayudar a hacer esta validación? existe un número de objeto para los preliminares (tabla ODRF, RDF1)

Espero me hayan comprendido.

Saludos y Gracias de Antemano.

En el transaction hice:

If @object_type = '112' and @transaction_type in ('A', 'U')

Begin

          If @object_type= '15'

           Begin

     If ((select T0.Quantity from DLN1 T0 INNER JOIN ODLN T1 ON T0.DocEntry = T1.DocEntry

     where T1.DocEntry = @list_of_cols_val_tab_del)<> (select T0.U_UDF_Tallas from DLN1 T0 INNER JOIN ODLN T1 ON T0.DocEntry = T1.DocEntry

     where T1.DocEntry = @list_of_cols_val_tab_del))

      begin

    Set @error = -1

    Set @error_message = 'La cantidad es Diferente a la Cantidad de las Tallas '

    end

    End

End

Former Member
0 Kudos

Hola Roger, una pregunta,, como puedo hacer para que en el crystal el saldo acumulado me sume solo la primera vez el saldo inicial y las demás veces no me lo sume?

He tratado con varias formas, pero me dice que no puedo compararlos cuando el registro es igual a 1 porque es una ventana, o sea como que es algo virtual del crystal y no lo puedo tomar como parámetro.

Saludos y Gracias

Former Member
0 Kudos

Roger como hago para que el saldo inicial solo me salga en la primera fila, o sea que solo me salga el saldo inicial en el primero registro y en los demás me salga 0, para cuando lo trraigo al crystal y poder sumar el saldo acumulado solo me tome el primer registro el saldo inicial y en los demás no me lo considere.

Es lo único que me falta para poder terminar mi informe.  En el mismo query que me enviaste que el saldo inicial solo me lo muestre el primer registro del informe y en los demás registros que me salga 0, que no me repita el saldo inicial en todos los registros, solo en el primero,

Saludos y Gracias

Former Member
0 Kudos

Ya programé la solución Roger, gracias de todas formas.

Saludos

Former Member
0 Kudos

Hola Roger espero que estés bien.

Una pregunta,, en cuanto a lo que me mandaste veo que solo involucra la tabla de transacciones o sea la de los asientos, pero que pasaría si mis clientes me exigen en vez de ver mis anticipos, ver el valor de mis facturas?

O sea analizando veo que me lo puede solucionar hacer desaparecer los anticipos y que mis facturas tengan el valor real de la factura y que no salgan en 0, como podría hacer esto?

Saludos

Former Member
0 Kudos

Ya programé la solución Roger, gracias de todas formas.

Saludos

rjovel
Active Participant
0 Kudos

busca el tipo de objeto de anticipos y omitelo en el reporte para que no te salgan esos valores y solo las facturas.

Former Member
0 Kudos

No es tan sencillo porque si los omito debo considerar las facturas con el valor real ya que estan en cero.  Ahora bien ya lo conseguí pero ahora tengo problemas con las fechas en cuanto al saldo inicial porque sino lo considero debo acumular lo que esta en saldo inicial con el valor de la factura tambien.  Te paso el query a ver si me puedes ayudar.

Saludos

USE EUFA

declare @code as nvarchar(20)

set @code ='CE-BO-0024'

declare @f1 as Date

set @f1 ='20121030'

declare @f2 as Date

set @f2 ='20130709'

select                                        ROW_NUMBER() over (order by T0.RefDate)as 'Número',

                                                  T1.CardCode as 'CLIENTE',

                    case when T0.transtype= '13' then 'Factura'

                         when T0.transtype='14' then 'Nota Credito'

                         when ((T0.transtype='24') and (T0.Credit>'0.00')) then 'Pagos'

                         when ((T0.transtype='24') and (T0.Debit>'0.00')) then 'Anula Pagos'

                         when T0.transtype= '30' then 'Ajustes'

                         else 'Otro'

                         end 'Tipo Trans',

                    case when

                   T0.ContraAct='301-02-07' THEN 'SI'

                   ELSE T0.Ref1

                   END AS'Referencia', T2.U_Marca 'Marca',

                    CONVERT(VARCHAR(10), RefDate, 103) 'Fecha Docto' ,

                    CONVERT(VARCHAR(10), duedate, 103) 'Vence Docto',

                

                  case when ROW_NUMBER() over (order by T0.RefDate)=1 then

                   isnull((select sum(debit-credit) from jdt1

                   where ShortName=T0.ShortName and RefDate<@f1),0)

                   else 0

                    end as 'Saldo Inicial',

                                           T1.Balance,

                                           CASE when ((T0.transtype=13)and (T0.Debit='0.00')) then T2.Max1099

                                           when ((T0.transtype=13)and(T0.Debit<T2.Max1099)) then (T0.Debit+T2.DpmAmnt)

                                           else T0.Debit

                                           end as 'Debito',

                                           

                                           T0.Credit as 'Credito',

                              isnull(CASE

                                        when (DATEDIFF(dd,refdate,current_timestamp))+1 < 31

                                        then

                                        case

                                        when BalDueCred <> 0 then isnull(-BalDueCred,0)

                                        else isnull(BalDueDeb,0)

                                        end

                                        end,0) "0-30 Dias",

                              isnull(case when ((datediff(dd,refdate,current_timestamp))+1 > 30

                                        and (datediff(dd,refdate,current_timestamp))+1< 61)

                                        then

                                        case

                                        when BalDueCred <> 0 then isnull(-BalDueCred,0)

                                        else isnull(BalDueDeb,0)

                                        end

                                        end,0) "31 to 60 Dias",

                              isnull(case when ((datediff(dd,refdate,current_timestamp))+1 > 60

                                        and (datediff(dd,refdate,current_timestamp))+1< 91)

                                        then

                                        case

                                        when BalDueCred <> 0 then isnull(-BalDueCred,0)

                                        else isnull(BalDueDeb,0)

                                        end

                                        end,0) "61 to 90 Dias",

                                 isnull(case when ((datediff(dd,refdate,current_timestamp))+1 > 90

                                        and (datediff(dd,refdate,current_timestamp))+1< 121)

                                        then

                                        case

                                        when BalDueCred <> 0 then isnull(-BalDueCred,0)

                                        else isnull(BalDueDeb,0)

                                        end

                                        end,0) "91 to 120 Dias",

                                 isnull(case when ((datediff(dd,refdate,current_timestamp))+1 > 120

                                        and (datediff(dd,refdate,current_timestamp))+1< 151)

                                        then

                                        case

                                        when BalDueCred <> 0 then isnull(-BalDueCred,0)

                                        else isnull(BalDueDeb,0)

                                        end

                                        end,0) "121 to 150 Dias",

                                isnull(case when ((datediff(dd,refdate,current_timestamp))+1 > 150

                                        and (datediff(dd,refdate,current_timestamp))+1< 181)

                                        then

                                        case

                                        when BalDueCred <> 0 then isnull(-BalDueCred,0)

                                        else isnull(BalDueDeb,0)

                                        end

                                        end,0) "151 to 180 Dias",

                               isnull(case when ((datediff(dd,refdate,current_timestamp))+1 > 180

                                        and (datediff(dd,refdate,current_timestamp))+1< 211)

                                        then

                                        case

                                        when BalDueCred <> 0 then isnull(-BalDueCred,0)

                                        else isnull(BalDueDeb,0)

                                        end

                                        end,0) "181 to 210 Dias",

                              isnull(case when ((datediff(dd,refdate,current_timestamp))+1 > 210

                                        and (datediff(dd,refdate,current_timestamp))+1< 241)

                                        then

                                        case

                                        when BalDueCred <> 0 then isnull(-BalDueCred,0)

                                        else isnull(BalDueDeb,0)

                                        end

                                        end,0) "211 to 240 Dias",

                              isnull(case when ((datediff(dd,refdate,current_timestamp))+1 > 240

                                        and (datediff(dd,refdate,current_timestamp))+1< 271)

                                        then

                                        case

                                        when BalDueCred <> 0 then isnull(-BalDueCred,0)

                                        else isnull(BalDueDeb,0)

                                        end

                                        end,0) "241 to 270 Dias",

                              isnull(case when ((datediff(dd,refdate,current_timestamp))+1 > 270

                                        and (datediff(dd,refdate,current_timestamp))+1< 301)

                                        then

                                        case

                                        when BalDueCred <> 0 then isnull(-BalDueCred,0)

                                        else isnull(BalDueDeb,0)

                                        end

                                        end,0) "271 to 300 Dias",

                             isnull(case when ((datediff(dd,refdate,current_timestamp))+1 > 300

                                        and (datediff(dd,refdate,current_timestamp))+1< 331)

                                        then

                                        case

                                        when BalDueCred <> 0 then isnull(-BalDueCred,0)

                                        else isnull(BalDueDeb,0)

                                        end

                                        end,0) "301 to 330 Dias",

                              isnull(case when ((datediff(dd,refdate,current_timestamp))+1 > 330

                                        and (datediff(dd,refdate,current_timestamp))+1< 361)

                                        then

                                        case

                                        when BalDueCred <> 0 then isnull(-BalDueCred,0)

                                        else isnull(BalDueDeb,0)

                                        end

                                        end,0) "331 to 360 Dias",                                             

                              isnull(CASE

                                        when (DATEDIFF(dd,refdate,current_timestamp))+1 > 360

                                        then

                                        case

                                        when BalDueCred= 0 then isnull(BalDueDeb,0)

                                        when BalDueDeb= 0 then isnull(-BalDueCred,0)

                                        end

                                        end,0) "360 + Dias"

                                  

from          dbo.JDT1 T0 with(nolock)

                    INNER JOIN

                    dbo.OCRD T1  with(nolock)

                    ON T0.shortname = T1.cardcode

                    and T1.cardtype = 'c'

                    LEFT JOIN dbo.OINV T2 with(nolock)

                    ON T0.TransId = T2.TransId

where        

         t0.RefDate>=@f1 and t0.RefDate<=@f2

                                 and T1.cardcode = @code

                                 and T0.TransType <>203

order by 1 asc