QUERY LIBRO MAYOR NIVEL 5 SAP B1

el día de hoy les traigo un query el cual es de mucha utilidad en SAP este funciona para contadores o administradores el cual es muy funcional cuando se trata de cuadrar esta informacion por niveles en la nomenclatura y cuadrar la informacion a cierta fecha.

como ya saben tan sencillo como copy paste en el query manager.

continuación les muestro el query en SQL

/*Declaración de variables*/
declare @Nivel              as Smallint
declare @Longitud_Nivel  as Smallint
declare @Var               as NVarchar(100)
declare @Fecha_Final     as Datetime

set @Nivel = 5
select @Longitud_Nivel = U_Valpar From [dbo].[@PARAMETROS] Where code = 24

/* Select * from [dbo].[JDT1] T0 */
declare @Fecha_Inicial as Datetime 
/* where */
Set @Fecha_Inicial = /* T0.RefDate */ '[%0]'


select @var = convert(char,datepart(year,@Fecha_Inicial)) + '-'
              + convert(char,datepart(month,@Fecha_Inicial)) +'-'+'01'+ ' 00:00'

select @Fecha_Inicial = convert(datetime, @var)

select @var = convert(char,datepart(year,@Fecha_Inicial)) + '-'
              + convert(char,datepart(month,@Fecha_Inicial)+1) +'-'+'01'+ ' 00:00'

select @Fecha_Final = convert(datetime, @var)-1

/* TABLA PARA EL CATALOGO DE CUENTAS */
CREATE TABLE #TEMP_CUENTAS
(
   [Tipo]          [Smallint] NULL
  ,[Nivel]         [Smallint] NULL
  ,[Cuenta]        [Nvarchar](30) NULL
  ,[Nombre_Cuenta] [Nvarchar](100) NULL
  ,[Fecha]         [Datetime] NULL
  ,[Saldo_Inicial] [Numeric](19,6) NULL
  ,[Debito]        [Numeric](19,6) NULL
  ,[Credito]       [Numeric](19,6) NULL
  ,[Saldo_Final]   [Numeric](19,6) NULL
)

/* TABLA PARA EL MOVIMIENTO DE CUENTAS POR DIA */
CREATE TABLE #TEMP_DIA
(
   [Tipo]          [Smallint] NULL
  ,[Nivel]         [Smallint] NULL
  ,[Cuenta]        [Nvarchar](30) NULL
  ,[Nombre_Cuenta] [Nvarchar](100) NULL
  ,[Fecha]         [Datetime] NULL
  ,[Saldo_Inicial] [Numeric](19,6) NULL
  ,[Debito]        [Numeric](19,6) NULL
  ,[Credito]       [Numeric](19,6) NULL
  ,[Saldo_Final]   [Numeric](19,6) NULL
)
INSERT INTO #TEMP_CUENTAS
SELECT T10.Tipo
     , T10.Nivel
     , T10.Cuenta
     , T10.Nombre_Cuenta
     , T10.Fecha
     , T10.Saldo_Inicial
     , T10.Debito
     , T10.Credito
	 , T10.Saldo_Final
FROM
(
   /* Catalogo Contable Ordenado */
   SELECT  1 as 'Tipo'
         , T0.Levels as 'Nivel'
         , Isnull(T0.Formatcode, T0.Acctcode) as 'Cuenta'
         , T0.AcctName as 'Nombre_Cuenta'
         , @Fecha_Inicial as 'Fecha'
         , 0.00 as 'Saldo_Inicial'
         , 0.00 as 'Debito'
         , 0.00 as 'Credito' 
     	 , 0.00 as 'Saldo_Final'
   FROM OACT T0
   WHERE T0.Levels = 5
   UNION ALL
   /* Selección de Saldos Iniciales antes de la Fecha Inicial */
   SELECT T11.Tipo
        , T11.Nivel
        , T11.Cuenta
        , T11.Nombre_Cuenta
        , T11.Fecha
        , SUM(T11.Saldo_Inicial)
        , SUM(T11.Debito)
        , SUM(T11.Credito)
	    , SUM(T11.Saldo_Final)
   FROM
   (
      SELECT  2 as 'Tipo'
            , @Nivel as 'Nivel'
            , Left(T0.FormatCode,@Longitud_Nivel) as 'Cuenta'
            , '    Saldo Mes Anterior ' as 'Nombre_Cuenta'
            , @Fecha_Inicial as 'Fecha'
            , CASE
                 WHEN T0.GroupMask IN (1,5,6,8) THEN
                    (SELECT ISNULL(SUM(ISNULL(T1.Debit,0)) - SUM(ISNULL(T1.Credit,0)),0)
                    FROM JDT1 T1
                    WHERE T1.Account = T0.AcctCode
                    AND T1.Refdate < @Fecha_Inicial
                    )
                 WHEN T0.GroupMask IN (2,3,4,7) THEN
                    (SELECT ISNULL(SUM(ISNULL(T1.Credit,0)) - SUM(ISNULL(T1.Debit,0)),0)
                    FROM JDT1 T1
                    WHERE T1.Account = T0.AcctCode
                    AND T1.Refdate < @Fecha_Inicial
                    )
              END as 'Saldo_Inicial'
            , 0.00 as 'Debito'
            , 0.00 as 'Credito'
	        , 0.00 as 'Saldo_Final'
      FROM OACT T0
      WHERE T0.Levels = 5
      GROUP BY LEFT(T0.FormatCode,@Longitud_Nivel), T0.GroupMask, T0.AcctCode
   ) [T11] 
   GROUP BY T11.Tipo, T11.Nivel, T11.Cuenta, T11.Nombre_Cuenta, T11.Fecha
) [T10]
WHERE T10.Nivel = @Nivel
ORDER BY T10.Cuenta, T10.Fecha

DECLARE @CODIGO_CUENTA as Varchar(30)
      , @SALDO_CUENTA as Decimal(19,6)
      , @TSALDO as Decimal(19,6)
      , @NSALDO as Decimal(19,6)
      , @GROUPMASK as int
      , @FECHA_CUENTA as DateTime
      , @DEBITO as Decimal(19,6)
      , @CREDITO as Decimal(19,6)
      , @Saldo_Final as Decimal(19,6)

DECLARE CUR_CUENTAS CURSOR FOR
SELECT T0.Cuenta
     , T0.Saldo_Inicial
FROM #TEMP_CUENTAS T0
WHERE TIPO = 2
OPEN CUR_CUENTAS
FETCH NEXT FROM CUR_CUENTAS INTO @CODIGO_CUENTA
                               , @SALDO_CUENTA

SET @TSALDO = 0
WHILE (@@FETCH_STATUS = 0)
BEGIN

   SELECT @TSALDO = @SALDO_CUENTA

   DECLARE CUR_DIA CURSOR FOR
   SELECT  T1.GroupMask
         , T0.Refdate
         , ISNULL(SUM(T0.Debit),0)
       	 , ISNULL(SUM (T0.Credit),0)
   FROM JDT1 T0  INNER JOIN OACT T1 ON T0.Account = T1.AcctCode 
   WHERE T0.Refdate Between @Fecha_Inicial AND @Fecha_Final
   AND Left(T1.FormatCode, @Longitud_Nivel) = @CODIGO_CUENTA
   GROUP BY T0.RefDate, T1.GroupMask

   OPEN CUR_DIA
   FETCH NEXT FROM CUR_DIA INTO @GROUPMASK
                              , @FECHA_CUENTA
                              , @DEBITO
                              , @CREDITO
   WHILE (@@FETCH_STATUS = 0)
   BEGIN
      IF @GROUPMASK IN (1,5,6,8)
         SET @NSALDO = @TSALDO + @DEBITO - @CREDITO

      ELSE
         SET @NSALDO = @TSALDO + @CREDITO - @DEBITO

      INSERT INTO #TEMP_DIA (Tipo, Nivel, Cuenta, Nombre_Cuenta, Fecha, Saldo_Inicial, Debito, Credito,Saldo_Final)
      VALUES ('2', @NIVEL, CONVERT(NVARCHAR(30), @CODIGO_CUENTA)
              , CONVERT(NVARCHAR(100), '...............OPERACIONES DEL DÍA : ')
              , @FECHA_CUENTA, @TSALDO, @DEBITO, @CREDITO, @NSALDO)

      FETCH NEXT FROM CUR_DIA INTO @GROUPMASK
                                 , @FECHA_CUENTA
                                 , @DEBITO
                                 , @CREDITO
      SET @TSALDO = @NSALDO
   END
   CLOSE CUR_DIA
   DEALLOCATE CUR_DIA

   SET @TSALDO = 0
   FETCH NEXT FROM CUR_CUENTAS INTO @CODIGO_CUENTA
                                  , @SALDO_CUENTA
END
CLOSE CUR_CUENTAS
DEALLOCATE CUR_CUENTAS

CREATE TABLE #TEMP_LIBRO_DIARIO_MAYOR
(
   [Tipo] [Smallint] NULL ,
   [Nivel] [Smallint] NULL ,
   [Cuenta] [Nvarchar](30) NULL ,
   [Nombre_Cuenta] [Nvarchar](100) NULL ,
   [Fecha] [Datetime] NULL ,
   [Saldo_Inicial] [Numeric](19, 6) NULL ,
   [Debito] [Numeric](19, 6) NULL ,
   [Credito] [Numeric](19, 6) NULL,
   [Saldo_Final] [Numeric](19, 6) NULL)

INSERT INTO #TEMP_LIBRO_DIARIO_MAYOR
SELECT *
FROM
(
   SELECT *
   FROM #TEMP_CUENTAS
   UNION ALL
   SELECT *
   FROM #TEMP_DIA
)[T10]

ORDER BY T10.CUENTA, T10.tipo, T10.FECHA

alter table #TEMP_LIBRO_DIARIO_MAYOR add
   [Imprime] [Nvarchar](1) NULL

CREATE TABLE #TEMP_LIBRO_DIARIO_MAYOR_T
(
   [Tipo] [Smallint] NULL ,
   [Nivel] [Smallint] NULL ,
   [Cuenta] [Nvarchar](30) NULL ,
   [Nombre_Cuenta] [Nvarchar](100) NULL ,
   [Fecha] [Datetime] NULL ,
   [Saldo_Inicial] [Numeric](19, 6) NULL ,
   [Debito] [Numeric](19, 6) NULL ,
   [Credito] [Numeric](19, 6) NULL,
   [Saldo_Final] [Numeric](19, 6) NULL,
   [Imprime] [Nvarchar](1) NULL
)

insert into #TEMP_LIBRO_DIARIO_MAYOR_T
select '0','3',Cuenta,'X',Month(Fecha),Avg(Saldo_Inicial),Sum(Debito),Sum(Credito),Avg(Saldo_Final),''
from #TEMP_LIBRO_DIARIO_MAYOR
group by Cuenta,Month(Fecha)

delete #TEMP_LIBRO_DIARIO_MAYOR_T
where (Saldo_Inicial= 0
and    Debito = 0
and    Credito = 0
and    Saldo_Final = 0)
update #TEMP_LIBRO_DIARIO_MAYOR
set Imprime = 'S'
FROM #TEMP_LIBRO_DIARIO_MAYOR_T a INNER JOIN #TEMP_LIBRO_DIARIO_MAYOR b ON a.Cuenta = b.Cuenta

delete #TEMP_LIBRO_DIARIO_MAYOR
where Imprime is null
alter table #TEMP_LIBRO_DIARIO_MAYOR drop column [Imprime]
update #TEMP_LIBRO_DIARIO_MAYOR set Saldo_Final = Saldo_Inicial
where (Debito = 0 and Credito = 0)



CREATE TABLE #TEMP_LIBRO_DIARIO_MAYOR_F
(
   [Tipo] [Smallint] NULL ,
   [Nivel] [Smallint] NULL ,
   [Cuenta] [varchar](30) NULL ,
   [Nombre_Cuenta] [Nvarchar](150) NULL ,
   [Fecha] [Datetime] NULL ,
   [Saldo_Inicial] [Numeric](19, 6) NULL ,
   [Debito] [Numeric](19, 6) NULL ,
   [Credito] [Numeric](19, 6) NULL,
   [Saldo_Final] [Numeric](19, 6) NULL)



Declare 
@tipo smallint
,@nombre_cuenta varchar(150)
,@t_saldo_ini  numeric(19,6)
,@largo int
,@t_debito  numeric(19,6)
,@t_credito  numeric(19,6)
,@t_saldo_fin  numeric(19,6)
Declare mov_final cursor for
Select 1,LEVELS, ISNULL(FORMATCODE,ACCTCODE), ACCTNAME, NULL
,0, 0, 0, 0
From OACT
WHERE LEVELS > 1 AND LEVELS <5
UNION ALL
Select 1,LEVELS, convert(varchar,GROUPMASK), ACCTNAME, NULL
,0, 0, 0, 0
FROM OACT 
WHERE LEVELS = 1 
open mov_final
Fetch mov_final into @tipo,@Nivel , @CODIGO_CUENTA ,@nombre_cuenta, @FECHA_CUENTA 
,@SALDO_CUENTA, @DEBITO , @CREDITO  , @Saldo_Final
While @@fetch_status = 0 begin
   Set @largo = len(@codigo_cuenta)
   
   Select @t_saldo_ini = Isnull(sum(saldo_inicial),0)
   From #TEMP_LIBRO_DIARIO_MAYOR
   Where substring(cuenta,1,@largo) = @codigo_cuenta
     and tipo = 2
     and nombre_cuenta = '    Saldo Mes Anterior '
   
   Select @t_debito = Isnull(sum(debito),0)
   From #TEMP_LIBRO_DIARIO_MAYOR
   Where substring(cuenta,1,@largo) = @codigo_cuenta
   and tipo = 2
   and nombre_cuenta <> '    Saldo Mes Anterior '
   Select @t_credito = Isnull(sum(credito),0)
   From #TEMP_LIBRO_DIARIO_MAYOR
   Where substring(cuenta,1,@largo) = @codigo_cuenta
   and tipo = 2
   and nombre_cuenta <> '    Saldo Mes Anterior '
   if substring(@codigo_cuenta,1,1) in ('2','3','4','7') begin
      Select @t_saldo_fin = @t_saldo_ini - @t_debito + @t_credito
   end
   else begin
      Select @t_saldo_fin = @t_saldo_ini +  @t_debito - @t_credito
   end

   
   INSERT INTO #TEMP_LIBRO_DIARIO_MAYOR_F(
   TIPO, NIVEL, CUENTA, NOMBRE_CUENTA, FECHA
   ,SALDO_INICIAL, DEBITO, CREDITO, SALDO_FINAL)
   Select @tipo,@Nivel , @CODIGO_CUENTA ,@nombre_cuenta, @FECHA_CUENTA 
   ,@t_saldo_ini, @t_debito , @t_credito , @t_saldo_fin

   Fetch mov_final into @tipo,@Nivel , @CODIGO_CUENTA ,@nombre_cuenta, @FECHA_CUENTA 
   ,@SALDO_CUENTA, @DEBITO , @CREDITO  , @Saldo_Final
end
close mov_final
deallocate mov_final

INSERT INTO #TEMP_LIBRO_DIARIO_MAYOR_F(
   TIPO, NIVEL, CUENTA, NOMBRE_CUENTA, FECHA
   ,SALDO_INICIAL, DEBITO, CREDITO, SALDO_FINAL)
Select TIPO, NIVEL, CUENTA, NOMBRE_CUENTA, FECHA
   ,SALDO_INICIAL, DEBITO, CREDITO, SALDO_FINAL
from #TEMP_LIBRO_DIARIO_MAYOR

--Delete #TEMP_LIBRO_DIARIO_MAYOR_F
--Where saldo_inicial = 0 and debito = 0 and credito = 0 and saldo_final = 0

SELECT * FROM #TEMP_LIBRO_DIARIO_MAYOR_F
ORDER BY CUENTA,tipo,NIVEL


drop table #TEMP_LIBRO_DIARIO_MAYOR_T
drop table #TEMP_LIBRO_DIARIO_MAYOR_F
--drop table #TEMP_LIBRO_DIARIO_MAYOR
drop table #TEMP_CUENTAS
drop table #TEMP_DIA
drop table #TEMP_LIBRO_DIARIO_MAYOR

Deja una respuesta

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