--Hash:6B31C1589F5A718605413FCCD714F5AA

-- Bloqueo exclusivo por base de datos
--//
SET NOCOUNT ON;

DECLARE @__ZiurDbScriptLockResource NVARCHAR(200) = N'ZiurSoft:DbScriptLock:' + CAST(DB_ID() AS NVARCHAR(20));
DECLARE @__ZiurDbScriptLockResult INT;
EXEC @__ZiurDbScriptLockResult = sp_getapplock
    @Resource = @__ZiurDbScriptLockResource,
    @LockMode = N'Exclusive',
    @LockOwner = N'Session',
    @LockTimeout = 600000; -- 10 min

IF (@__ZiurDbScriptLockResult < 0)
BEGIN
    RAISERROR(N'No se pudo obtener el bloqueo exclusivo de actualizacion para esta base de datos.', 16, 1);
    SET NOEXEC ON;
END

/* Idempotencia a nivel de script: si la version ya existe, no ejecutar nada. */
IF EXISTS (SELECT 1 FROM dbo.ZiurVersiones WHERE NumVersion = 956)
BEGIN
    PRINT N'Scripts_956.sql: la version 956 ya esta aplicada. Se omite ejecucion.';
    EXEC sp_releaseapplock @Resource = @__ZiurDbScriptLockResource, @LockOwner = N'Session';
    SET NOEXEC ON;
END

SET XACT_ABORT ON;
GO

SET DATEFORMAT mdy;

--Validar que no existan scripts que no se hayan ejecutado
--//
IF EXISTS
(
	SELECT a.NumVersion + 1 AS HuecoInicio
	FROM ZiurVersiones a
	LEFT JOIN ZiurVersiones b ON a.NumVersion + 1 = b.NumVersion
	WHERE a.NumVersion>830 AND b.NumVersion IS NULL
	AND a.NumVersion + 1 < 956
)
RAISERROR('Error : hay un script que no se ejecuto , debe ejecutarlo manualmente.', 20, -1) with log
--//

--Colocar contenido de script diferencial aqui (Ziur.Database.publish.sql)

GO
/*
 Plantilla de script anterior a la implementación							
--------------------------------------------------------------------------------------
 Este archivo contiene instrucciones de SQL que se ejecutarán antes del script de compilación	
 Use la sintaxis de SQLCMD para incluir un archivo en el script anterior a la implementación			
 Ejemplo:      :r .\miArchivo.sql								
 Use la sintaxis de SQLCMD para hacer referencia a una variable en el script anterior a la implementación		
 Ejemplo:      :setvar TableName miTabla							
               SELECT * FROM [$(TableName)]					
--------------------------------------------------------------------------------------
*/

GO

GO
PRINT N'Dropping Check Constraint [dbo].[EsRango_Audit_Clientes]...';


GO
IF OBJECT_ID(N'dbo.Audit_Clientes', N'U') IS NOT NULL
AND EXISTS (SELECT 1 FROM sys.objects WHERE name = N'EsRango_Audit_Clientes' AND parent_object_id = OBJECT_ID(N'dbo.Audit_Clientes'))
    ALTER TABLE [dbo].[Audit_Clientes] DROP CONSTRAINT [EsRango_Audit_Clientes];


GO
PRINT N'Dropping Check Constraint [dbo].[EsRango_Audit_Inventarios]...';


GO
IF OBJECT_ID(N'dbo.Audit_Inventarios', N'U') IS NOT NULL
AND EXISTS (SELECT 1 FROM sys.objects WHERE name = N'EsRango_Audit_Inventarios' AND parent_object_id = OBJECT_ID(N'dbo.Audit_Inventarios'))
    ALTER TABLE [dbo].[Audit_Inventarios] DROP CONSTRAINT [EsRango_Audit_Inventarios];


GO
PRINT N'Dropping Check Constraint [dbo].[EsRango_Clientes]...';


GO
IF OBJECT_ID(N'dbo.Clientes', N'U') IS NOT NULL
AND EXISTS (SELECT 1 FROM sys.objects WHERE name = N'EsRango_Clientes' AND parent_object_id = OBJECT_ID(N'dbo.Clientes'))
    ALTER TABLE [dbo].[Clientes] DROP CONSTRAINT [EsRango_Clientes];


GO
PRINT N'Dropping Index [dbo].[VInventarios_CostoPromedio_00].[IX_CostoProm]...';


GO
IF EXISTS (
    SELECT 1
    FROM sys.indexes
    WHERE name = N'IX_CostoProm'
      AND object_id = OBJECT_ID(N'dbo.VInventarios_CostoPromedio_00')
)
    DROP INDEX [IX_CostoProm] ON [dbo].[VInventarios_CostoPromedio_00];


GO
PRINT N'Removing schema binding from [dbo].[VInventarios_CostoPromedio_00]...';
IF OBJECT_ID(N'dbo.VInventarios_CostoPromedio_00', N'V') IS NULL
    EXEC(N'CREATE VIEW [dbo].[VInventarios_CostoPromedio_00] AS SELECT 1 AS Dummy;');

GO
ALTER VIEW [dbo].[VInventarios_CostoPromedio_00]
AS
SELECT EncMov.IdInventario,
       EncMovCP.Orden,
       EncMov.IdEncabMov,
       Enc.IdEncab,
       EncMovCP.CostoProm,
       EncMovCP.CantAcum,
       EncMovCP.CostoAcum
FROM   dbo.Encabezados AS Enc
       INNER JOIN
       dbo.Encabezados_Mov AS EncMov
       ON Enc.IdEncab = EncMov.IdEncab
       INNER JOIN
       dbo.Encabezados_Mov_CostoProm AS EncMovCP
       ON EncMov.IdEncabMov = EncMovCP.IdEncabMov
WHERE  (NOT (EncMovCP.Orden IS NULL));


GO
PRINT N'Altering Table [dbo].[Encabezados]...';


GO
IF OBJECT_ID(N'dbo.Encabezados', N'U') IS NOT NULL
AND COL_LENGTH(N'dbo.Encabezados', N'IdCaja') IS NULL
BEGIN
    ALTER TABLE [dbo].[Encabezados]
        ADD [IdCaja] INT NULL;
END


GO
PRINT N'Creating Table [dbo].[Encabezados_Cajas]...';


GO
IF OBJECT_ID(N'dbo.Encabezados_Cajas', N'U') IS NULL
BEGIN
CREATE TABLE [dbo].[Encabezados_Cajas] (
    [IdCaja]          INT            NOT NULL,
    [NomCaja]         NVARCHAR (100) NOT NULL,
    [DescripCaja]     NVARCHAR (200) NULL,
    [EsActivo]        BIT            NOT NULL,
    [PlacaCaja]       NVARCHAR (100) NULL,
    [UbicacionCaja]   NVARCHAR (100) NULL,
    [TipoCaja]        NVARCHAR (100) NULL,
    [CodigoVentaCaja] NVARCHAR (100) NULL,
    [NomPcCaja]       NVARCHAR (100) NULL,
    [MacPcCaja]       NVARCHAR (100) NULL,
    CONSTRAINT [PK_Encabezados_Cajas] PRIMARY KEY CLUSTERED ([IdCaja] ASC)
);
END


GO
PRINT N'Creating Default Constraint [dbo].[DF_Encabezados_Cajas_EsActivo]...';


GO
IF OBJECT_ID(N'dbo.Encabezados_Cajas', N'U') IS NOT NULL
AND NOT EXISTS (SELECT 1 FROM sys.objects WHERE name = N'DF_Encabezados_Cajas_EsActivo' AND parent_object_id = OBJECT_ID(N'dbo.Encabezados_Cajas'))
BEGIN
    ALTER TABLE [dbo].[Encabezados_Cajas]
        ADD CONSTRAINT [DF_Encabezados_Cajas_EsActivo] DEFAULT ((1)) FOR [EsActivo];
END


GO
PRINT N'Creating Foreign Key [dbo].[FK_Encabezados_Cajas]...';


GO
IF OBJECT_ID(N'dbo.Encabezados', N'U') IS NOT NULL
AND NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = N'FK_Encabezados_Cajas' AND parent_object_id = OBJECT_ID(N'dbo.Encabezados'))
BEGIN
    ALTER TABLE [dbo].[Encabezados] WITH NOCHECK
        ADD CONSTRAINT [FK_Encabezados_Cajas] FOREIGN KEY ([IdCaja]) REFERENCES [dbo].[Encabezados_Cajas] ([IdCaja]);
END


GO
PRINT N'Creating Check Constraint [dbo].[EsRango_Audit_Clientes]...';


GO
IF OBJECT_ID(N'dbo.Audit_Clientes', N'U') IS NOT NULL
AND NOT EXISTS (SELECT 1 FROM sys.objects WHERE name = N'EsRango_Audit_Clientes' AND parent_object_id = OBJECT_ID(N'dbo.Audit_Clientes'))
BEGIN
    ALTER TABLE [dbo].[Audit_Clientes] WITH NOCHECK
        ADD CONSTRAINT [EsRango_Audit_Clientes] CHECK ([dbo].[SynEsRango]()=(1));
END


GO
PRINT N'Creating Check Constraint [dbo].[EsRango_Audit_Inventarios]...';


GO
IF OBJECT_ID(N'dbo.Audit_Inventarios', N'U') IS NOT NULL
AND NOT EXISTS (SELECT 1 FROM sys.objects WHERE name = N'EsRango_Audit_Inventarios' AND parent_object_id = OBJECT_ID(N'dbo.Audit_Inventarios'))
BEGIN
    ALTER TABLE [dbo].[Audit_Inventarios] WITH NOCHECK
        ADD CONSTRAINT [EsRango_Audit_Inventarios] CHECK ([dbo].[SynEsRango]()=(1));
END


GO
PRINT N'Creating Check Constraint [dbo].[EsRango_Clientes]...';


GO
IF OBJECT_ID(N'dbo.Clientes', N'U') IS NOT NULL
AND NOT EXISTS (SELECT 1 FROM sys.objects WHERE name = N'EsRango_Clientes' AND parent_object_id = OBJECT_ID(N'dbo.Clientes'))
BEGIN
    ALTER TABLE [dbo].[Clientes] WITH NOCHECK
        ADD CONSTRAINT [EsRango_Clientes] CHECK ([dbo].[SynEsRango]()=(1));
END


GO
PRINT N'Refreshing View [dbo].[VActivos_FijosContables]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VActivos_FijosContables]';


GO
PRINT N'Refreshing View [dbo].[VAgrupadoPorCliVend_01]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VAgrupadoPorCliVend_01]';


GO
PRINT N'Refreshing View [dbo].[VClientesPuntos]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VClientesPuntos]';


GO
PRINT N'Refreshing View [dbo].[VContab_EncMov]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VContab_EncMov]';


GO
PRINT N'Refreshing View [dbo].[VContabDoc____Totales]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VContabDoc____Totales]';


GO
PRINT N'Refreshing View [dbo].[VContabDoc_00]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VContabDoc_00]';


GO
PRINT N'Refreshing View [dbo].[VContabDoc_FP_00]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VContabDoc_FP_00]';


GO
PRINT N'Refreshing View [dbo].[VContablesConcil]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VContablesConcil]';


GO
PRINT N'Refreshing View [dbo].[VCostoPromedio]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VCostoPromedio]';


GO
PRINT N'Refreshing View [dbo].[VCruces_Origen]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VCruces_Origen]';


GO
PRINT N'Refreshing View [dbo].[VCruces_Sumas]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VCruces_Sumas]';


GO
PRINT N'Refreshing View [dbo].[VCruces_TodosDocs]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VCruces_TodosDocs]';


GO
PRINT N'Refreshing View [dbo].[VIdAbonaCheque_02]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VIdAbonaCheque_02]';


GO
PRINT N'Refreshing View [dbo].[VIdAbonaDoc_01]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VIdAbonaDoc_01]';


GO
PRINT N'Refreshing View [dbo].[VIdAbonaDoc_02]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VIdAbonaDoc_02]';


GO
PRINT N'Refreshing View [dbo].[VIngresoProducto]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VIngresoProducto]';


GO
PRINT N'Adding schema binding to [dbo].[VInventarios_CostoPromedio_00]...';
IF OBJECT_ID(N'dbo.VInventarios_CostoPromedio_00', N'V') IS NULL
    EXEC(N'CREATE VIEW [dbo].[VInventarios_CostoPromedio_00] AS SELECT 1 AS Dummy;');

GO
ALTER VIEW VInventarios_CostoPromedio_00
with schemabinding 
AS
SELECT     EncMov.IdInventario, EncMovCP.Orden, EncMov.IdEncabMov, Enc.IdEncab, EncMovCP.CostoProm, EncMovCP.CantAcum, EncMovCP.CostoAcum
FROM         dbo.Encabezados AS Enc INNER JOIN
                      dbo.Encabezados_Mov AS EncMov ON Enc.IdEncab = EncMov.IdEncab INNER JOIN
                      dbo.Encabezados_Mov_CostoProm AS EncMovCP ON EncMov.IdEncabMov = EncMovCP.IdEncabMov
WHERE     (NOT (EncMovCP.Orden IS NULL))
GO
PRINT N'Creating Index [dbo].[VInventarios_CostoPromedio_00].[IX_CostoProm]...';


GO
CREATE UNIQUE CLUSTERED INDEX [IX_CostoProm]
    ON [dbo].[VInventarios_CostoPromedio_00]([IdInventario] ASC, [Orden] ASC, [IdEncabMov] ASC);


GO
PRINT N'Refreshing View [dbo].[VInventarios_Existencia]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VInventarios_Existencia]';


GO
PRINT N'Refreshing View [dbo].[VInventariosLotesCons01]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VInventariosLotesCons01]';


GO
PRINT N'Refreshing View [dbo].[VInvExistPorLote]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VInvExistPorLote]';


GO
PRINT N'Refreshing View [dbo].[vLibroAuxiliar_Mov]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[vLibroAuxiliar_Mov]';


GO
PRINT N'Refreshing View [dbo].[vLibroAuxiliar_Saldos_01]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[vLibroAuxiliar_Saldos_01]';


GO
PRINT N'Refreshing View [dbo].[VMediosMag_Procesos_Principal]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VMediosMag_Procesos_Principal]';


GO
PRINT N'Refreshing View [dbo].[VMesasEnc]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VMesasEnc]';


GO
PRINT N'Refreshing View [dbo].[VNomina_Contabilizacion_00]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VNomina_Contabilizacion_00]';


GO
PRINT N'Refreshing View [dbo].[VNominaCantItems]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VNominaCantItems]';


GO
PRINT N'Refreshing View [dbo].[VNominaIBC]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VNominaIBC]';


GO
PRINT N'Refreshing View [dbo].[VNominaMovimientos]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VNominaMovimientos]';


GO
PRINT N'Refreshing View [dbo].[VNominaUGPP]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VNominaUGPP]';


GO
PRINT N'Refreshing View [dbo].[VSeriales]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VSeriales]';


GO
PRINT N'Refreshing View [dbo].[VSesionesSaldos]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VSesionesSaldos]';


GO
PRINT N'Refreshing View [dbo].[VContabDoc____TRMPromedio]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VContabDoc____TRMPromedio]';


GO
PRINT N'Refreshing View [dbo].[VTot_EncMov]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VTot_EncMov]';


GO
PRINT N'Refreshing View [dbo].[VContabDoc____TotPorCuentas_00]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VContabDoc____TotPorCuentas_00]';


GO
PRINT N'Refreshing View [dbo].[VContabDoc_FP____TotPorCuentas_00]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VContabDoc_FP____TotPorCuentas_00]';


GO
PRINT N'Refreshing View [dbo].[VCostoPromedio_Cruces]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VCostoPromedio_Cruces]';


GO
PRINT N'Refreshing View [dbo].[VCruces_Agrup]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VCruces_Agrup]';


GO
PRINT N'Refreshing View [dbo].[VProduccion]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VProduccion]';


GO
PRINT N'Refreshing View [dbo].[VEncabezadosCont]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VEncabezadosCont]';


GO
PRINT N'Refreshing View [dbo].[VInventarios_Existencia_Recursos]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VInventarios_Existencia_Recursos]';


GO
PRINT N'Refreshing View [dbo].[VInventariosLotesCons02]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VInventariosLotesCons02]';


GO
PRINT N'Refreshing View [dbo].[vLibroAuxiliar_Saldos_02]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[vLibroAuxiliar_Saldos_02]';


GO
PRINT N'Refreshing View [dbo].[VMediosMag_Informes_AgrupPorConceptoTotal]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VMediosMag_Informes_AgrupPorConceptoTotal]';


GO
PRINT N'Refreshing View [dbo].[VMediosMag_Procesos_CuantiasNormales_00]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VMediosMag_Procesos_CuantiasNormales_00]';


GO
PRINT N'Refreshing View [dbo].[VMediosMag_Procesos_Principal_Agrup]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VMediosMag_Procesos_Principal_Agrup]';


GO
PRINT N'Refreshing View [dbo].[VMediosMag_Procesos_Principal_Agrup2]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VMediosMag_Procesos_Principal_Agrup2]';


GO
PRINT N'Refreshing View [dbo].[VMediosMag_Procesos_ReteFteIvaNoDomic_Fin]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VMediosMag_Procesos_ReteFteIvaNoDomic_Fin]';


GO
PRINT N'Refreshing View [dbo].[VNomina_Contabilizacion_CredTot]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VNomina_Contabilizacion_CredTot]';


GO
PRINT N'Refreshing View [dbo].[VNomina_Contabilizacion_DebTot]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VNomina_Contabilizacion_DebTot]';


GO
PRINT N'Refreshing View [dbo].[VNominaCorte]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VNominaCorte]';


GO
PRINT N'Refreshing View [dbo].[VContabDoc____TotPorCuentas_Fin]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VContabDoc____TotPorCuentas_Fin]';


GO
PRINT N'Refreshing View [dbo].[VContabDoc_FP____TotPorCuentas_Fin]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VContabDoc_FP____TotPorCuentas_Fin]';


GO
PRINT N'Refreshing View [dbo].[VEncabezadosMov]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VEncabezadosMov]';


GO
PRINT N'Refreshing View [dbo].[VMediosMag_Procesos_CuantiasNormales_Fin]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VMediosMag_Procesos_CuantiasNormales_Fin]';


GO
PRINT N'Refreshing View [dbo].[VMediosMag_Procesos_ReteFuente_Fin]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VMediosMag_Procesos_ReteFuente_Fin]';


GO
PRINT N'Refreshing View [dbo].[VMediosMag_Procesos_ReteFuenteCREE_Fin]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VMediosMag_Procesos_ReteFuenteCREE_Fin]';


GO
PRINT N'Refreshing View [dbo].[VMediosMag_Procesos_ReteIvaRegComun_Fin]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VMediosMag_Procesos_ReteIvaRegComun_Fin]';


GO
PRINT N'Refreshing View [dbo].[VMediosMag_Procesos_ReteIvaRegSimpC_Fin]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VMediosMag_Procesos_ReteIvaRegSimpC_Fin]';


GO
PRINT N'Refreshing View [dbo].[VMediosMag_Procesos_IvaMayorValor_Fin]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VMediosMag_Procesos_IvaMayorValor_Fin]';


GO
PRINT N'Refreshing View [dbo].[VMovimientosFactElectronica]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VMovimientosFactElectronica]';


GO
PRINT N'Altering View [dbo].[VEncabezados]...';
IF OBJECT_ID(N'dbo.VEncabezados', N'V') IS NULL
    EXEC(N'CREATE VIEW [dbo].[VEncabezados] AS SELECT 1 AS Dummy;');

GO
ALTER VIEW dbo.VEncabezados
AS
SELECT        Enc.IdEncab, Enc.IdEmpresa, Enc.IdDocumento, Enc.IdPrefijo, pre.NomPrefijo, Enc.NumDocumento, Enc.Fecha, Enc.IdCliente1, Enc.IdCliente2, Enc.Nota, Enc.IdFormaPago, FP.NomFormaPago, 
                         dbo.Documentos.NomDocumento, Emp.EmpresaNombre, COALESCE (Cli1.Nombre1, N'') + N' ' + COALESCE (Cli1.Nombre2, N'') + N' ' + COALESCE (Cli1.Apellido1, N'') + N' ' + COALESCE (Cli1.Apellido2, N'') 
                         AS NombresCliente1, COALESCE (Cli2.Nombre1, N'') + N' ' + COALESCE (Cli2.Nombre2, N'') + N' ' + COALESCE (Cli2.Apellido1, N'') + N' ' + COALESCE (Cli2.Apellido2, N'') AS NombresCliente2, 
                         Enc.IdCliente1_IdDir, dbo.Clientes_Direcciones.Direccion, dbo.Monedas.NomMoneda1 AS NomMoneda, Enc.Vence, Reparacion_Automotores_1.NumPlaca, Reparacion_Automotores_1.NumChasis, 
                         Enc.IdAutomotor, Cli1.Identificacion, Emp.EmpresaNit, dbo.Clientes_Direcciones.Telefonos, Enc.NomPc, Enc.NumDocumentoExt, dbo.Ciudades.NomCiudad, Reparacion_Automotores_1.NumMotor, 
                         Reparacion_Automotores_1.Kilometraje, Reparacion_Automotores_1.Modelo, Reparacion_Automotores_1.Color, dbo.Reparacion_Marcas.NomMarca, Emp.EmpresaDireccion, Emp.EmpresaTelefono, 
                         Emp.EmpresaCorreoElect, COALESCE (Emp.EmpresaNit, N'') + '' + COALESCE ('-' + CAST(Emp.EmpresaDV AS varchar), N' ') AS NIT, dbo.Clientes_Contactos.Nombres AS NombresContacto, 
                         dbo.Clientes_Contactos.Telefono AS TelefonoContacto, dbo.Clientes_Contactos.Email AS EmailContacto, dbo.Clientes_Contactos.Cargo AS CargoContacto, Enc.ValorTRM, COALESCE (Cli3.Nombre1, N'') 
                         + N' ' + COALESCE (Cli3.Nombre2, N'') + N' ' + COALESCE (Cli3.Apellido1, N'') + N' ' + COALESCE (Cli3.Apellido2, N'') AS NombreVend, COALESCE (CliVend2.Nombre1, N'') + N' ' + COALESCE (CliVend2.Nombre2, 
                         N'') + N' ' + COALESCE (CliVend2.Apellido1, N'') + N' ' + COALESCE (CliVend2.Apellido2, N'') AS NombreVend2, dbo.Documentos.IdPlantilla, Enc.EsBloqueado, Enc.EsAnulado, Emp.EmpresaResolucion, 
                         dbo.Monedas.Abrev, dbo.Encabezados_Estados.NomEstado, Enc.IdEstado, dbo.Clientes_Direcciones.Celular AS CelularCli, dbo.Clientes_Direcciones.EMail AS EmailCli, Cli4.Identificacion AS IdentificacionProp, 
                         COALESCE (Cli4.Nombre1, N'') + N' ' + COALESCE (Cli4.Nombre2, N'') + N' ' + COALESCE (Cli4.Apellido1, N'') + N' ' + COALESCE (Cli4.Apellido2, N'') AS NombreProp, RegCli.NomTipoRegimen AS RegimenCli, 
                         RegProp.NomTipoRegimen AS RegimenProp, dbo.PH_Inmuebles.Direccion AS DireccionInmueble, COALESCE (Cli1.Identificacion, N'') + N'' + COALESCE (N'-' + CAST(Cli1.DVCliente AS varchar), N' ') AS NitCliente, 
                         dbo.VTotalesRete.SumReteFuente, dbo.VTotalesRete.SumIva, dbo.VTotalesRete.SumReteIva, dbo.VTotalesRete.SumReteIca, dbo.VTotalesRete.SumReteFuenteCree, dbo.VTotalesRete.SumReteIvaRSC, 
                         dbo.VTot_EncCont.Credito, dbo.VTot_EncCont.Debito, dbo.VTot_EncMov.SubTotal, dbo.VTot_EncMov.SubTotalSinDcto, dbo.VTot_EncMov.ValorDcto, dbo.VTot_EncMov.SubIVA, dbo.VTot_EncMov.TotDoc, 
                         Enc.IdSucursal, dbo.Sucursales.NomSucursal, dbo.Sucursales.CodSucursal, dbo.VTotalesRete.SumAutoReteCreeC, dbo.VTotalesRete.SumAutoReteCreeD, dbo.VTotalesRete.SumImpuestoalConsumo, 
                         dbo.VTotalesRete.SumReteIvaRegimenSimplificadoD, dbo.VTotalesRete.SumDiferidosPasivo, dbo.VTotalesRete.SumReteIvaNoDomiciliado, dbo.VTotalesRete.SumCuentasPorCobrar, 
                         dbo.VTotalesRete.SumCuentasPorPagar, dbo.VTotalesRete.SumReteIvaRegimenComun, dbo.VTotalesRete.SumTesoreria, dbo.VTotalesRete.SumNormal, dbo.VTotalesRete.SumComisionTarjetas, 0 AS EsDelete, 
                         Reparacion_Automotores_1.NomAutomotor, Enc.ValorPago, tipoDir.NomTipoDir, dbo.Documentos.CodDocumento, dbo.Encabezados_Tipos.NomTipo, dbo.VTot_EncMov.IVA, dbo.VTot_EncMov.SubTotExento, 
                         dbo.VTot_EncMov.SubTotGravado, dbo.VTot_EncMov.Cantidad, clientesesp.Especial_01 AS EspecialTercero01, clientesesp.Especial_02 AS EspecialTercero02, clientesesp.Especial_03 AS EspecialTercero03, 
                         clientesesp.Especial_04 AS EspecialTercero04, clientesesp.Especial_05 AS EspecialTercero05, clientesesp.Especial_06 AS EspecialTercero06, clientesesp.Especial_07 AS EspecialTercero07, 
                         clientesesp.Especial_08 AS EspecialTercero08, clientesesp.Especial_09 AS EspecialTercero09, clientesesp.Especial_10 AS EspecialTercero10, clientesesp.Especial_11 AS EspecialTercero11, 
                         clientesesp.Especial_12 AS EspecialTercero12, clientesesp.Especial_13 AS EspecialTercero13, clientesesp.Especial_14 AS EspecialTercero14, clientesesp.Especial_15 AS EspecialTercero15, 
                         clientesesp.Especial_16 AS EspecialTercero16, clientesesp.Especial_17 AS EspecialTercero17, clientesesp.Especial_18 AS EspecialTercero18, clientesesp.Especial_19 AS EspecialTercero19, 
                         clientesesp.Especial_20 AS EspecialTercero20, clientesesp.Especial_21 AS EspecialTercero21, clientesesp.Especial_22 AS EspecialTercero22, clientesesp.Especial_23 AS EspecialTercero23, 
                         clientesesp.Especial_24 AS EspecialTercero24, clientesesp.Especial_25 AS EspecialTercero25, clientesesp.Especial_26 AS EspecialTercero26, clientesesp.Especial_27 AS EspecialTercero27, 
                         clientesesp.Especial_28 AS EspecialTercero28, clientesesp.Especial_29 AS EspecialTercero29, clientesesp.Especial_30 AS EspecialTercero30, clientesesp.Especial_31 AS EspecialTercero31, 
                         clientesesp.Especial_32 AS EspecialTercero32, clientesesp.Especial_33 AS EspecialTercero33, clientesesp.Especial_34 AS EspecialTercero34, clientesesp.Especial_35 AS EspecialTercero35, 
                         clientesesp.Especial_36 AS EspecialTercero36, clientesesp.Especial_37 AS EspecialTercero37, clientesesp.Especial_38 AS EspecialTercero38, clientesesp.Especial_39 AS EspecialTercero39, 
                         clientesesp.Especial_40 AS EspecialTercero40, encesp.Especial_01 AS Especial01, encesp.Especial_02 AS Especial02, encesp.Especial_03 AS Especial03, encesp.Especial_04 AS Especial04, 
                         encesp.Especial_05 AS Especial05, encesp.Especial_06 AS Especial06, encesp.Especial_07 AS Especial07, encesp.Especial_08 AS Especial08, encesp.Especial_09 AS Especial09, 
                         encesp.Especial_10 AS Especial10, encesp.Especial_11 AS Especial11, encesp.Especial_12 AS Especial12, encesp.Especial_13 AS Especial13, encesp.Especial_14 AS Especial14, 
                         encesp.Especial_15 AS Especial15, encesp.Especial_16 AS Especial16, encesp.Especial_17 AS Especial17, encesp.Especial_18 AS Especial18, encesp.Especial_19 AS Especial19, 
                         encesp.Especial_20 AS Especial20, encesp.Especial_21 AS Especial21, encesp.Especial_22 AS Especial22, encesp.Especial_23 AS Especial23, encesp.Especial_24 AS Especial24, 
                         encesp.Especial_25 AS Especial25, encesp.Especial_26 AS Especial26, encesp.Especial_27 AS Especial27, encesp.Especial_28 AS Especial28, encesp.Especial_29 AS Especial29, 
                         encesp.Especial_30 AS Especial30, encesp.Especial_31 AS Especial31, encesp.Especial_32 AS Especial32, encesp.Especial_33 AS Especial33, encesp.Especial_34 AS Especial34, 
                         encesp.Especial_35 AS Especial35, encesp.Especial_36 AS Especial36, encesp.Especial_37 AS Especial37, encesp.Especial_38 AS Especial38, encesp.Especial_39 AS Especial39, 
                         encesp.Especial_40 AS Especial40, Cli1.DVCliente, Emp.EmpresaDV, VHistCli.IdHistoriaClinica AS IdHistClinPaciente, VHistCli.NomTipoIdenTercero AS TipoIdenHistClinPaciente, 
                         VHistCli.IdenTercero AS IdenHistClinPaciente, VHistCli.NombresTercero AS NombresHistClinPaciente, VHistCli.ApellidosTercero AS ApellidosHistClinPaciente, VHistCli.Edad AS EdadHistClinPaciente, 
                         VHistCli.FechaNacimiento AS FechaNaciHistClinPaciente, VHistCli.Telefonos AS TelefonosHistClinPaciente, VHistCli.MedPrepContrato AS MedPrepContratoHistClin, 
                         VHistCli.IdenPrepagada AS IdenPrepagadaHistClin, VHistCli.NomPrepagada AS NomPrepagadaHistClin, VHistCli.EPSContrato AS EpsContratoHistClin, VHistCli.IdenEPS AS IdenEpsHistClin, 
                         VHistCli.NomEps AS NomEpsHistClin, VHistCli.FechaInicio AS FechaIniHistClinPaciente, VHistCli.FechaFin AS FechaFinHistClinPaciente, VHistCli.GeneroPaciente AS GeneroHistClinPaciente, 
                         VHistCli.Celular AS CelularHistClinPaciente, VHistCli.Direccion AS DireccionHistClinPaciente, Cli1.CodigoInterno,cast(Zona1.IdZona1 as nvarchar(10))+'-'+Zona1.NomZona1 as Zona1, 
						 ValorPuntos,SaldoPuntos AS SaldoPuntosCliente, Enc.FeCUFE, Enc.FeFechaAutorizacion, Enc.FeIdProveedor, FeProvee.NomProveedor, FeEstDian.NomEstado AS FeNomEstadoDian, FeEstProvee.NomEstado AS FeNomEstadoProvee, FeConcep.NomConcepto,
						 Enc.EsFeSincronizada, Emp.NumLocalizacionGlobal, Enc.FeIdEstadoDian, FeTipoOpe.CodOperacion AS TipoOpeEnc, enc.IdDocReferido,  DocReferencia.NomDocumento AS NomDocReferido, FeMedPago.CodMedPago, FeMedPago.IdMedPago, FeTipoOp.CodOperacion AS TipoOpeDoc, Enc.FeCufeND, Enc.FePrefijoND, Enc.FeNumDocND, 
						 Enc.FeFechaEmisionND, Enc.IdMoneda, FeEstProvee.IdEstado AS FeEstProveedor, Enc.FeIdVersion AS FeDocVersion, Enc.IdClienteVend, Enc.FeOrdenComp, Enc.FeDocDesp, Enc.FeIdTipoIngreso, Enc.FeEnvioAnticipo, Enc.FeIdConcepto, Enc.FeNotasFechaIni, Enc.FeNotasFechaFin, Mesas.NomMesa, Mesas.DescripMesa, Enc.IdCaja
FROM            dbo.Sucursales RIGHT OUTER JOIN
                         dbo.Encabezados_Tipos RIGHT OUTER JOIN
                         dbo.Clientes AS Cli1 INNER JOIN
                         dbo.Encabezados AS Enc INNER JOIN
                         dbo.Empresas AS Emp ON Enc.IdEmpresa = Emp.IdEmpresa INNER JOIN
                         dbo.Documentos ON Enc.IdDocumento = dbo.Documentos.IdDocumento ON Cli1.IdCliente = Enc.IdCliente1 ON dbo.Encabezados_Tipos.IdTipo = Enc.IdTipo ON 
                         dbo.Sucursales.IdSucursal = Enc.IdSucursal LEFT OUTER JOIN
                         dbo.VTot_EncMov ON Enc.IdEncab = dbo.VTot_EncMov.IdEncab LEFT OUTER JOIN
                         dbo.VTot_EncCont ON Enc.IdEncab = dbo.VTot_EncCont.IdEncab LEFT OUTER JOIN
                         dbo.VTotalesRete ON Enc.IdEncab = dbo.VTotalesRete.IdEncab LEFT OUTER JOIN
                         dbo.Clientes AS Cli4 ON Enc.IdClienteProp = Cli4.IdCliente LEFT OUTER JOIN
                         dbo.Clientes AS Cli2 ON Enc.IdCliente2 = Cli2.IdCliente LEFT OUTER JOIN
                         dbo.Clientes AS Cli3 ON Enc.IdClienteVend = Cli3.IdCliente LEFT OUTER JOIN
                         dbo.Clientes AS CliVend2 ON Enc.IdClienteVend2 = CliVend2.IdCliente LEFT OUTER JOIN
                         dbo.Clientes_Contactos ON Enc.IdContacto = dbo.Clientes_Contactos.IdContacto LEFT OUTER JOIN
                         dbo.Ciudades RIGHT OUTER JOIN
                         dbo.Clientes_Direcciones ON dbo.Ciudades.IdCiudad = dbo.Clientes_Direcciones.IdCiudad ON Enc.IdCliente1_IdDir = dbo.Clientes_Direcciones.IdClienteDir LEFT OUTER JOIN
                         dbo.Monedas ON Enc.IdMoneda = dbo.Monedas.IdMoneda LEFT OUTER JOIN
                         dbo.Reparacion_Marcas RIGHT OUTER JOIN
                         dbo.Reparacion_Automotores AS Reparacion_Automotores_1 ON dbo.Reparacion_Marcas.IdMarca = Reparacion_Automotores_1.IdMarca ON 
                         Enc.IdAutomotor = Reparacion_Automotores_1.IdAutomotor LEFT OUTER JOIN
                         dbo.FormasPago AS FP ON Enc.IdFormaPago = FP.IdFormaPago LEFT OUTER JOIN
                         dbo.Encabezados_Estados ON Enc.IdEstado = dbo.Encabezados_Estados.IdEstado LEFT OUTER JOIN
                         dbo.Clientes_TiposRegimen AS RegCli ON Cli1.IdTipoRegimen = RegCli.IdTipoRegimen LEFT OUTER JOIN
                         dbo.Clientes_TiposRegimen AS RegProp ON Cli4.IdTipoRegimen = RegProp.IdTipoRegimen LEFT OUTER JOIN
                         dbo.PH_Inmuebles ON dbo.PH_Inmuebles.IdInmueble = Enc.IdInmueble LEFT OUTER JOIN
                         dbo.Clientes_TiposDireccion AS tipoDir ON tipoDir.IdTipoDir = dbo.Clientes_Direcciones.IdTipoDir LEFT OUTER JOIN
                         dbo.Prefijos AS pre ON pre.IdPrefijo = Enc.IdPrefijo LEFT OUTER JOIN
                         dbo.Clientes_Especial AS clientesesp ON Enc.IdCliente1 = clientesesp.IdCliente LEFT OUTER JOIN
                         dbo.EncabezadosEspecial AS encesp ON Enc.IdEncab = encesp.IdEncab LEFT OUTER JOIN
                         dbo.VHistoriaClinica_Datos AS VHistCli ON Enc.IdHistoriaClinica = VHistCli.IdHistoriaClinica  LEFT OUTER JOIN
						 dbo.Clientes_Zona1 AS Zona1 ON Cli1.IdZona1=Zona1.IdZona1   LEFT OUTER JOIN
						 dbo.VClientesPuntos AS puntos ON puntos.IdCliente1=Enc.IdCliente1 LEFT OUTER JOIN
						 dbo.FacturacionElectronica_Proveedores AS FeProvee ON Enc.FeIdProveedor = FeProvee.IdProveedor LEFT OUTER JOIN
						 dbo.FacturacionElectronica_Proveedores_Estados AS FeEstDian ON Enc.FeIdEstadoDian = FeEstDian.IdEstado LEFT OUTER JOIN
						 dbo.FacturacionElectronica_Proveedores_Estados AS FeEstProvee ON Enc.FeIdEstadoProveedor = FeEstProvee.IdEstado  LEFT OUTER JOIN
						 dbo.FacturacionElectronica_Conceptos_Documentos AS FeConcep ON Enc.FeIdConcepto = FeConcep.IdConcepto LEFT OUTER JOIN
						 dbo.FacturacionElectronica_MediosPagoContado AS FeMedPago ON FeMedPago.IdMedPago = Enc.FeIdMedPago LEFT OUTER JOIN
						 dbo.FacturacionElectronica_TipoOperacion AS FeTipoOpe ON FeTipoOpe.IdOperacion = Enc.FeIdOperacion LEFT OUTER JOIN
						 dbo.FacturacionElectronica_TipoOperacion AS FeTipoOp ON FeTipoOp.IdOperacion = Documentos.FeIdOperacion LEFT OUTER JOIN
                         dbo.Documentos AS DocReferencia ON DocReferencia.IdDocumento = Enc.IdDocReferido LEFT OUTER JOIN
                         dbo.Encabezados_Mesas AS Mesas ON Mesas.IdMesa = Enc.IdMesa
GO
PRINT N'Refreshing View [dbo].[VIdAbonaComisiones]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VIdAbonaComisiones]';


GO
PRINT N'Refreshing View [dbo].[VIdAbonaCruces]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VIdAbonaCruces]';


GO
PRINT N'Refreshing View [dbo].[VContabDoc_01]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VContabDoc_01]';


GO
PRINT N'Refreshing View [dbo].[VContabDoc_FP_01]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VContabDoc_FP_01]';


GO
PRINT N'Altering View [dbo].[VEncabezadoFactElectronica]...';
IF OBJECT_ID(N'dbo.VEncabezadoFactElectronica', N'V') IS NULL
    EXEC(N'CREATE VIEW [dbo].[VEncabezadoFactElectronica] AS SELECT 1 AS Dummy;');

GO
ALTER VIEW [dbo].[VEncabezadoFactElectronica]
	AS 

	
SELECT 

--Datos Factura
VEnc.IdEncab,
CONVERT(NVARCHAR(13),VEnc.NumDocumento) AS NumDocumento,
VEnc.Fecha AS FechaEmision,
CONVERT(NVARCHAR(50),VEnc.Fecha,108) AS HoraEmision,
Doc.FeIdDocumentoDian,
Doc.FeIdDocumentoBusqueda,
Abrev AS AbrevDivisaFactura,
Venc.NomPrefijo AS Prefijo,
Venc.IdPrefijo,
Venc.Fecha,
VEnc.Vence,
Emp.IdEmpresa,
Emp.FeTokenDian,
Emp.SeTokenDian,
VEnc.EsFeSincronizada,
VEnc.FeCUFE,
VEnc.FeCufeND,
VEnc.FePrefijoND, 
VEnc.FeNumDocND, 
VEnc.FeFechaEmisionND,
COALESCE(VEnc.TipoOpeEnc, VEnc.TipoOpeDoc) AS FeTipoOpe,
--HKA 
VEnc.FeIdProveedor,
Emp.FeTokenPassword,
VEnc.FeIdEstadoDian,

--Datos Empresa
CliEmpTipReg.FeIdTipoPersonaDian AS EmprTipoPersona,
CliEmp.IdTipoIdent AS EmprTipoIdentificacion,
CliEmp.Identificacion AS EmprNumIdentificacion,
COALESCE (CliEmp.Nombre1, N'') + N' ' + COALESCE (CliEmp.Nombre2, N'') + N' ' + COALESCE (CliEmp.Apellido1, N'') + N' ' + COALESCE (CliEmp.Apellido2, N'') AS EmprNombre1,
CliEmpDpto.NomDpto AS EmprDepartamento,
CliEmpDir.Subdivision AS EmprSubdivisionCiudad,
CliEmpCiu.NomCiudad AS EmprCiudad,
CliEmpDir.Direccion AS EmprDireccion,
CliEmpPais.CodPais AS EmprCodPais,
CliEmpTipReg.FeIdTipoRegimenDian AS EmprRegimen,
CliEmpDir.EMail AS EmpreCorreoElect,
CliEmp.DVCliente AS DVEmpr,
FORMAT(CliEmpDir.IdCiudad, '00000') AS EmprCodMuni, 
CliEmpDir.CodigoPostal AS EmprCodPos,
CliEmpPais.NomPais1 AS EmprNomPais,
CliEmpTipReg.TipoRegDian AS EmprTipoReg,
cliEmpDir.Nota AS EmprNotaDir,
CliEmp.IdCliente AS EmprIdTercero,
CliEmpCiu.IdDpto  AS EmprDptoCod,
CliEmpDir.Telefonos AS EmprTel,
CliEmpDir.Celular AS EmprCel,

--Datos Cliente
CliTipReg.FeIdTipoPersonaDian AS CliTipoPersona,
Cli.IdTipoIdent AS CliTipoIdentificacion,
Cli.Identificacion AS CliNumIdentificacion,
CliDpto.NomDpto AS CliDepartamento,
CliDir.Subdivision AS CliSubdivisionCiudad,
CliCiu.NomCiudad AS CliCiudad,
CliDir.Direccion AS CliDireccion,
CliPais.CodPais AS CliCodPais,
CliTipReg.FeIdTipoRegimenDian AS CliRegimen,
VEnc.NombresCliente1 AS CliNombreComercial,
Cli.Nombre1,
Cli.Apellido1 +' '+ Cli.Apellido2 AS CliApellidos,
Cli.Nombre2,
CliDir.EMail AS CliEmail,
Cli.DVCliente, 
FORMAT(CliDir.IdCiudad, '00000') AS  CliCodMuni,
CliDir.CodigoPostal AS CliCodPos,
CliCiu.IdDpto AS CLiCodDpto, 
CliPais.NomPais1 AS CliNomPais,
CliTipReg.TipoRegDian AS CliTipoReg,
CliDir.Nota AS CliNotaDir,
VEnc.IdCliente1,
CliDir.Telefonos AS CliTel,
CliDir.Celular AS CliCel,

--Vendedor
VEnc.NombreVend,

--Totales Impuestos Factura 
VEnc.SumIva,
VEnc.SumImpuestoalConsumo,
VEnc.SumReteIva,
VEnc.SumReteIca,
VEnc.SumReteFuente,
VEnc.SumReteFuenteCree,
VEnc.SumAutoReteCreeC,
Venc.SumAutoReteCreeD,

--Adicionales
VEnc.Nota,
Venc.IdMoneda,
VEnc.FeOrdenComp,
VEnc.FeDocDesp,
VEnc.FeEnvioAnticipo,
VEnc.IdDocReferido,
VEnc.NomDocReferido,
VEnc.FeIdConcepto,

--Formas de Pago
VEnc.IdFormaPago,
VEnc.NomFormaPago,

--Totales
VEnc.SubTotal,
VEnc.SubTotalSinDcto,
CASE WHEN VEnc.ValorTRM <> 0 THEN VEnc.SubTotalSinDcto / VEnc.ValorTRM END SubTotalSinDctoExt, 
VEnc.ValorDcto,
CASE WHEN VEnc.ValorTRM <> 0 THEN VEnc.ValorDcto / VEnc.ValorTRM END ValorDctoExt, 
CASE WHEN VEnc.SubTotalSinDcto <> 0 THEN (VEnc.ValorDcto / VEnc.SubTotalSinDcto) END factorDcto, 
CASE WHEN VEnc.ValorTRM <> 0 AND VEnc.SubTotalSinDcto <> 0 THEN (VEnc.ValorDcto / VEnc.SubTotalSinDcto) / VEnc.ValorTRM END factorDctoExt, 
VEnc.SubIVA,
VEnc.TotDoc,
VEnc.SubTotExento,
VEnc.SubTotGravado,
VEnc.Debito,

--Resolucion
EmpResol.IdResolFact,
EmpResol.NumeroResolFact,
EmpResol.NumeroInicial AS EmpResolNumeroInicial,
EmpResol.NumeroFinal AS EmpResolNumeroFinal,
EmpResol.IdPrefijo AS PrefijoFE,
--HKA
EmpResol.FechaResolIni AS EmpResolFechaIni,
EmpResol.FechaResolVence AS EmpResolFechaVence,

--Tipo de envio de facturacion electronica
TipoEnv.EsEnvioProd AS EsEnvioProduccionFe,
Emp.FeVersion AS EmprFeVersion,
Venc.FeDocVersion AS DocFeVersion,
VEnc.IdMedPago,
VEnc.CodMedPago,
Doc.IdDocumento,
VEnc.ValorTRM,
TipoIng.CodTipoIngreso,

--Sucursal
VEnc.IdSucursal, 
VEnc.CodSucursal,
VEnc.NomSucursal,

--Fechas de NC y ND para envio electronico
VEnc.FeNotasFechaIni,
VEnc.FeNotasFechaFin,

--Para info caja del pos
Cajas.NomCaja, Cajas.PlacaCaja, Cajas.UbicacionCaja, Cajas.TipoCaja, Cajas.CodigoVentaCaja

FROM VEncabezados AS VEnc INNER JOIN 
Documentos AS Doc ON VEnc.IdDocumento = Doc.IdDocumento INNER JOIN
--Inicio Joins Para los Datos de la Empresa
Empresas AS Emp ON VEnc.IdEmpresa = Emp.IdEmpresa INNER JOIN
Clientes AS CliEmp ON Emp.IdCliente = CliEmp.IdCliente INNER JOIN 
Clientes_TiposRegimen AS CliEmpTipReg ON CliEmp.IdTipoRegimen = CliEmpTipReg.IdTipoRegimen INNER JOIN
Clientes_Direcciones AS CliEmpDir ON CliEmp.IdCliente = CliEmpDir.IdCliente AND CliEmpDir.EsDirPrincipal = 1  LEFT JOIN
Empresas_ResolucionesFacturacion AS EmpResol ON VEnc.IdEmpresa = EmpResol.IdEmpresa AND VEnc.IdDocumento = EmpResol.IdDocumento AND EmpResol.FeResolucion = 1 AND (VEnc.NumDocumento BETWEEN EmpResol.NumeroInicial AND EmpResol.NumeroFinal) AND ((VEnc.IdPrefijo IS NULL AND EmpResol.IdPrefijo IS NULL) OR VEnc.IdPrefijo = EmpResol.IdPrefijo) LEFT JOIN
Ciudades AS CliEmpCiu ON CliEmpDir.IdCiudad = CliEmpCiu.IdCiudad LEFT JOIN
Departamentos AS CliEmpDpto ON CliEmpCiu.IdDpto = CliEmpDpto.IdDpto LEFT JOIN
Paises AS CliEmpPais ON CliEmpDpto.IdPais = CliEmpPais.IdPais INNER JOIN
--Inicio Joins Para los Datos del Cliente
Clientes AS Cli ON VEnc.IdCliente1 = Cli.IdCliente INNER JOIN 
Clientes_TiposRegimen AS CliTipReg ON Cli.IdTipoRegimen = CliTipReg.IdTipoRegimen INNER JOIN
Clientes_Direcciones AS CliDir ON VEnc.IdCliente1_IdDir = CliDir.IdClienteDir LEFT JOIN
Ciudades AS CliCiu ON CliDir.IdCiudad = CliCiu.IdCiudad LEFT JOIN
Departamentos AS CliDpto ON CliCiu.IdDpto = CliDpto.IdDpto LEFT JOIN
Paises AS CliPais ON CliDpto.IdPais = CliPais.IdPais INNER JOIN
FacturacionElectronica_TipoEnvio AS TipoEnv ON TipoEnv.IdTipoEnvio = VEnc.FeDocVersion LEFT OUTER JOIN
FacturacionElectronica_TipoIngreso AS TipoIng ON VEnc.FeIdTipoIngreso = TipoIng.IdTipoIngreso LEFT OUTER JOIN
Encabezados_Cajas AS Cajas ON VEnc.IdCaja = Cajas.IdCaja
GO
PRINT N'Refreshing View [dbo].[VEncabezadosCont_Det]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VEncabezadosCont_Det]';


GO
PRINT N'Refreshing View [dbo].[VEncabezadosMov_Det]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VEncabezadosMov_Det]';


GO
PRINT N'Refreshing View [dbo].[VContabDoc____TotRetes_Fin]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VContabDoc____TotRetes_Fin]';


GO
PRINT N'Refreshing View [dbo].[VContabDoc_01_01]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VContabDoc_01_01]';


GO
PRINT N'Refreshing View [dbo].[VContabDoc_FP____TotRetes_Fin]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VContabDoc_FP____TotRetes_Fin]';


GO
PRINT N'Refreshing View [dbo].[VContabDoc_FP_01_01]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VContabDoc_FP_01_01]';


GO
PRINT N'Refreshing View [dbo].[VCierresDocsCont]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VCierresDocsCont]';


GO
PRINT N'Refreshing View [dbo].[VCierresDocs]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VCierresDocs]';


GO
PRINT N'Refreshing View [dbo].[VContabDoc_02]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VContabDoc_02]';


GO
PRINT N'Refreshing View [dbo].[VContabDoc_FP_02]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VContabDoc_FP_02]';


GO
PRINT N'Refreshing View [dbo].[VContabDoc_03]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VContabDoc_03]';


GO
PRINT N'Refreshing View [dbo].[VContabDoc_FP_03]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VContabDoc_FP_03]';


GO
PRINT N'Refreshing View [dbo].[VContabDoc_Fin]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VContabDoc_Fin]';


GO
PRINT N'Refreshing View [dbo].[VContabDoc_FP_Fin]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[VContabDoc_FP_Fin]';


GO
PRINT N'Refreshing Function [dbo].[CostoPromedioPorInventario_v2]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[CostoPromedioPorInventario_v2]';


GO
PRINT N'Refreshing Function [dbo].[CostoPromedioPorInventario]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[CostoPromedioPorInventario]';


GO
PRINT N'Altering Procedure [dbo].[GetBalancePrueba]...';
IF OBJECT_ID(N'dbo.GetBalancePrueba', N'P') IS NULL
    EXEC(N'CREATE PROCEDURE [dbo].[GetBalancePrueba] AS BEGIN SET NOCOUNT ON; RETURN 0; END');

GO
ALTER PROCEDURE [dbo].[GetBalancePrueba]
	@FechaIni NVARCHAR(10) = '',
	@FechaFin NVARCHAR(10) = '',
	@CodCuentaIni NVARCHAR(200)='',
	@CodCuentaFin NVARCHAR(200)='',
    @Detallar BIT = 1,
	@IdEmpresas NVARCHAR(1000) = '',
	@IdDocsExc NVARCHAR(1000) ='',
	@IdCentroCosto INT = NULL,
	@EsNotGAAP BIT = NULL,
    @EsNotIFRS BIT = NULL,
	@IdCliente INT=NUll,
	@EsAgrupIFRS BIT = NULL	
AS
BEGIN

DECLARE

@IdTipoPUC INT,
@FechaIniT DATETIME,
@FechaFinT DATETIME,
@SepCuenta NVARCHAR(100)

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT @IdTipoPUC = ValParametro FROM PARAMETROS WHERE CodParametro = 1
IF @IdTipoPUC IS NULL
	SET @IdTipoPUC = 1

SELECT @SepCuenta = ValParametro FROM PARAMETROS WHERE CodParametro = 9
IF @SepCuenta IS NULL
	SET @SepCuenta = '-'
	
SET @FechaIniT = CAST(CONVERT(DATETIME, @FechaIni, 103) + ' 00:00:00' AS DATETIME)
SET @FechaFinT = CAST(CONVERT(DATETIME, @FechaFin, 103) + ' 23:59:59' AS DATETIME)

	SELECT 
	Cta.CodCuenta, Cta.NomCuenta, Cta.DetallarCliente, Factor, (@Detallar & Cta.DetallarCliente) as SenDet, CASE WHEN (@Detallar = 1 AND Cta.DetallarCliente = 1) THEN Enc_C.IdCliente ELSE NULL END AS [IdCliente],
	0.0 AS [SaldoIni], 
	CAST(SUM([Debito]) AS NUMERIC(28,6)) AS [Debito], 
	CAST(SUM([Credito]) AS NUMERIC(28,6)) AS [Credito], 	
	0.0 AS [SaldoIniExt],
	CAST(SUM([DebitoExt]) AS NUMERIC(28,6)) AS [DebitoExt], 
	CAST(SUM([CreditoExt]) AS NUMERIC(28,6)) AS [CreditoExt],
	CodCuentaIFRS,
	CAST(sum(case when Enc_C.EsNotGAAP=0 THEN  Debito else 0 end) AS NUMERIC(28,6)) DebitoGAAP,
	CAST(sum(case when Enc_C.EsNotGAAP=0 THEN  Credito else 0 end) AS NUMERIC(28,6))  CreditoGAAP,
	CAST(sum(case when Enc_C.EsIFRS=0 THEN  Debito else 0 end) AS NUMERIC(28,6)) DebitoIFRS,
	CAST(sum(case when Enc_C.EsIFRS=0 THEN  Credito else 0 end) AS NUMERIC(28,6)) CreditoIFRS,
	0.0 AS [SaldoIniGAAP],0.0 AS [SaldoIniIFRS]
	INTO #tmpMov
	FROM 
	dbo.Encabezados AS Enc INNER JOIN dbo.Cuentas AS Cta 
	INNER JOIN dbo.Cuentas_Tipos AS Cta_T ON Cta.IdCuentaTipo = Cta_T.IdCuentaTipo 
	INNER JOIN dbo.Cuentas_Clases AS Cta_C ON Cta_T.IdClase = Cta_C.IdClase 
	INNER JOIN dbo.Encabezados_Cont AS Enc_C ON Cta.IdCuenta = Enc_C.IdCuenta ON Enc.IdEncab = Enc_C.IdEncab
	INNER JOIN (select number from iter$simple_intlist_to_tbl(@IdEmpresas)) T1 on T1.number=Enc.IdEmpresa
	LEFT JOIN (select nstr from iter$charlist_to_tbl(@IdDocsExc,DEFAULT)) T2 on Enc.IdDocumento=T2.nstr
	WHERE
	(Enc.Fecha >= @FechaIniT And Enc.Fecha <= @FechaFinT) AND
	Cta.CodCuenta BETWEEN @CodCuentaIni And @CodCuentaFin AND
	T2.nstr IS NULL AND
	Enc.EsAnulado=0 AND
	(@EsNotGAAP IS NULL OR Enc_C.EsNotGAAP=@EsNotGAAP) AND
	(@EsNotIFRS IS NULL OR Enc_C.EsIFRS=@EsNotIFRS) AND
	(@IdCentroCosto IS NULL OR Enc_C.IdCentroCosto=@IdCentroCosto) AND
	(@IdCliente IS NULL OR Enc_C.IdCliente=@IdCliente)
	GROUP BY
	Cta.CodCuenta, Cta.NomCuenta, Cta.DetallarCliente, Factor, (@Detallar & Cta.DetallarCliente), CASE WHEN (@Detallar = 1 AND Cta.DetallarCliente = 1) THEN Enc_C.IdCliente ELSE NULL END,CodCuentaIFRS

	SELECT 
	Cta.CodCuenta, Cta.NomCuenta, Cta.DetallarCliente, Factor, (@Detallar & Cta.DetallarCliente) as SenDet, CASE WHEN (@Detallar = 1 AND Cta.DetallarCliente = 1) THEN Enc_C.IdCliente ELSE NULL END AS [IdCliente],
	CAST(SUM(([Debito]-[Credito])*[Factor]) AS NUMERIC(28,6)) AS [SaldoIni], 
	0.0 AS [Debito], 
	0.0 AS [Credito], 
	CAST(SUM(([DebitoExt]-[CreditoExt])*[Factor]) AS NUMERIC(28,6)) AS [SaldoIniExt],
	0.0 AS [DebitoExt], 
	0.0 AS [CreditoExt], 	
	CodCuentaIFRS,
	0.0 AS DebitoGAAP,0.0 AS CreditoGAAP,0.0 AS DebitoIFRS,0.0 AS CreditoIFRS,
	CAST(sum(case when Enc_C.EsNotGAAP=0 THEN  Factor * ([Debito]-[Credito]) else 0 end) AS NUMERIC(28,6)) SaldoIniGAAP,
	CAST(sum(case when Enc_C.EsIFRS=0 THEN  Factor * ([Debito]-[Credito]) else 0 end) AS NUMERIC(28,6)) SaldoIniIFRS
	INTO #tmpSaldoIni
	FROM 
	dbo.Encabezados AS Enc 
	INNER JOIN dbo.Cuentas AS Cta 
	INNER JOIN dbo.Cuentas_Tipos AS Cta_T ON Cta.IdCuentaTipo = Cta_T.IdCuentaTipo 
	INNER JOIN dbo.Cuentas_Clases AS Cta_C ON Cta_T.IdClase = Cta_C.IdClase 
	INNER JOIN dbo.Encabezados_Cont AS Enc_C ON Cta.IdCuenta = Enc_C.IdCuenta ON Enc.IdEncab = Enc_C.IdEncab
	INNER JOIN (select number from iter$simple_intlist_to_tbl(@IdEmpresas)) T1 on T1.number=Enc.IdEmpresa
	WHERE
	Enc.Fecha < @FechaIniT AND
	Cta.CodCuenta BETWEEN @CodCuentaIni And @CodCuentaFin AND
	Enc.EsAnulado=0
	AND

	(@EsNotGAAP IS NULL OR Enc_C.EsNotGAAP=@EsNotGAAP) AND
	(@EsNotIFRS IS NULL OR Enc_C.EsIFRS=@EsNotIFRS) AND
	(@IdCentroCosto IS NULL OR Enc_C.IdCentroCosto=@IdCentroCosto) AND
	(@IdCliente IS NULL OR Enc_C.IdCliente=@IdCliente)

	GROUP BY
	Cta.CodCuenta, Cta.NomCuenta, Cta.DetallarCliente, Factor, (@Detallar & Cta.DetallarCliente), CASE WHEN (@Detallar = 1 AND Cta.DetallarCliente = 1) THEN Enc_C.IdCliente ELSE NULL END,CodCuentaIFRS

	select CodAgrup as CodCuenta,
	CASE WHEN @EsAgrupIFRS=1 and min(isnull(NomCuentaIFRS,'')) <>'' then min(isnull(NomCuentaIFRS,'')) else min(isnull(NomCuenta,'')) end NomCuentaBase,
	CASE WHEN @EsAgrupIFRS=1 then min(isnull(CodCuenta,'')) else min(isnull(CodCuentaIFRS,''))end CodCuentaEspejo,
	CASE WHEN @EsAgrupIFRS=1 then min(isnull(NomCuenta,'')) else min(isnull(NomCuentaIFRS,''))end NomCuentaEspejo
	INTO #tempCuentas
	from
	(
		select CodCuenta,NomCuenta,
		CodCuentaIFRS,NomCuentaIFRS,
		case when @EsAgrupIFRS=1 and len(CodCuentaIFRS)> 0 then COALESCE(CodCuentaIFRS,CodCuenta) else CodCuenta end CodAgrup
		from cuentas
		where idtipoPuc=@IdTipoPUC
	)as q1
	group by CodAgrup

SELECT
CodCuenta,
q1.Cuenta_Tercero, q1.Cuenta_Nit, q1.IdTipoIdent, q1.NomTipoIdent, q1.Identificacion, q1.DVCliente, q1.Direccion, q1.DireccionFormato1, q1.DireccionFormato2, q1.IdCiudad, q1.NomCiudad, 
q1.SaldoIni, q1.TotalDebitos, q1.TotalCreditos, q1.SaldoIniExt, q1.TotalDebitosExt, q1.TotalCreditosExt,  q1.SaldoFin, q1.SaldoFinExt, q1.DetallarCliente,
q1.SaldoIniGAAP, q1.TotalDebitosGAAP, q1.TotalCreditosGAAP,q1.SaldoFinGAAP,
q1.SaldoIniIFRS, q1.TotalDebitosIFRS, q1.TotalCreditosIFRS,q1.SaldoFinIFRS,
q1.CodNiv1 AS CodNiv1, CASE WHEN q1.CodNiv2 = q1.CodNiv1 THEN NULL ELSE q1.CodNiv7 END AS CodNiv2, CASE WHEN q1.CodNiv3 = q1.CodNiv2 THEN NULL ELSE q1.CodNiv7 END AS CodNiv3, CASE WHEN q1.CodNiv4 = q1.CodNiv3 THEN NULL ELSE q1.CodNiv7 END AS CodNiv4, CASE WHEN q1.CodNiv5 = q1.CodNiv4 THEN NULL ELSE q1.CodNiv7 END AS CodNiv5, CASE WHEN q1.CodNiv6 = q1.CodNiv5 THEN NULL ELSE q1.CodNiv6 END AS CodNiv6, CASE WHEN q1.CodNiv7 = q1.CodNiv6 THEN NULL ELSE q1.CodNiv7 END AS CodNiv7,
q1.NomCta1, q1.NomCta2, q1.NomCta3, q1.NomCta4, q1.NomCta5, q1.NomCta6, q1.NomCta7,
q1.CodNiv1 + @SepCuenta + q1.NomCta1 AS NomCuenta1, 
CASE WHEN q1.CodNiv2 = q1.CodNiv1 THEN NULL ELSE q1.CodNiv2 + @SepCuenta + q1.NomCta2 END AS NomCuenta2,
CASE WHEN q1.CodNiv3 = q1.CodNiv2 THEN NULL ELSE q1.CodNiv3 + @SepCuenta + q1.NomCta3 END AS NomCuenta3,
CASE WHEN q1.CodNiv4 = q1.CodNiv3 THEN NULL ELSE q1.CodNiv4 + @SepCuenta + q1.NomCta4 END AS NomCuenta4,
CASE WHEN q1.CodNiv5 = q1.CodNiv4 THEN NULL ELSE q1.CodNiv5 + @SepCuenta + q1.NomCta5 END AS NomCuenta5,
CASE WHEN q1.CodNiv6 = q1.CodNiv5 THEN NULL ELSE q1.CodNiv6 + @SepCuenta + q1.NomCta6 END AS NomCuenta6,
CASE WHEN q1.CodNiv7 = q1.CodNiv6 THEN NULL ELSE q1.CodNiv7 + @SepCuenta + q1.NomCta7 END AS NomCuenta7,

q1.CodEsp1 + @SepCuenta + q1.NomCtaEsp1 AS NomCuentaEsp1, 
CASE WHEN q1.CodNiv2 = q1.CodNiv1 THEN NULL ELSE q1.CodEsp2 + @SepCuenta + q1.NomCtaEsp2 END AS NomCuentaEsp2,
CASE WHEN q1.CodNiv3 = q1.CodNiv2 THEN NULL ELSE q1.CodEsp3 + @SepCuenta + q1.NomCtaEsp3 END AS NomCuentaEsp3,
CASE WHEN q1.CodNiv4 = q1.CodNiv3 THEN NULL ELSE q1.CodEsp4 + @SepCuenta + q1.NomCtaEsp4 END AS NomCuentaEsp4,
CASE WHEN q1.CodNiv5 = q1.CodNiv4 THEN NULL ELSE q1.CodEsp5 + @SepCuenta + q1.NomCtaEsp5 END AS NomCuentaEsp5,
CASE WHEN q1.CodNiv6 = q1.CodNiv5 THEN NULL ELSE q1.CodEsp6 + @SepCuenta + q1.NomCtaEsp6 END AS NomCuentaEsp6,
CASE WHEN q1.CodNiv7 = q1.CodNiv6 THEN NULL ELSE q1.CodEsp7 + @SepCuenta + q1.NomCtaEsp7 END AS NomCuentaEsp7,
CodCtaUgpp,
NomCtaUgpp

FROM 
(
	SELECT u.CodCuenta,u.CodCuentaIFRS,
	CASE WHEN u.SenDet = 1 THEN LTRIM(RTRIM(Cli.[Apellidos] + ' ' + Cli.[Nombres])) ELSE u.NomCuenta END AS [Cuenta_Tercero],
	CASE WHEN u.SenDet = 1 THEN LTRIM(RTRIM(Cli.[NomTipoIdent] + ' ' + Cli.[Identificacion])) ELSE '' END AS [Cuenta_Nit],
	CASE WHEN u.SenDet = 1 THEN Cli.IdTipoIdent ELSE '' END AS IdTipoIdent,
	CASE WHEN u.SenDet = 1 THEN Cli.NomTipoIdent ELSE '' END AS NomTipoIdent,
	CASE WHEN u.SenDet = 1 THEN Cli.Identificacion ELSE '' END AS Identificacion,
	CASE WHEN u.SenDet = 1 THEN Cli.DVCliente ELSE '' END AS DVCliente,
	CASE WHEN u.SenDet = 1 THEN CliDir.Direccion ELSE '' END AS Direccion,
	CASE WHEN u.SenDet = 1 THEN CliDir.DireccionFormato1 ELSE '' END AS DireccionFormato1,
	CASE WHEN u.SenDet = 1 THEN CliDir.DireccionFormato2 ELSE '' END AS DireccionFormato2,
	CASE WHEN u.SenDet = 1 THEN CliDir.IdCiudad ELSE '' END AS IdCiudad,
	CASE WHEN u.SenDet = 1 THEN CliDir.NomCiudad ELSE '' END AS NomCiudad,
	u.SaldoIni, u.SaldoIniExt, u.TotalDebitos, u.TotalDebitosExt, u.TotalCreditos, u.TotalCreditosExt, u.SaldoIni + (u.TotalDebitos - u.TotalCreditos) * u.Factor as SaldoFin, 	u.SaldoIniExt + (u.TotalDebitosExt - u.TotalCreditosExt) * u.Factor as SaldoFinExt,	u.SaldoIniGAAP, u.TotalDebitosGAAP, u.TotalCreditosGAAP, u.SaldoIniGAAP + (u.TotalDebitosGAAP - u.TotalCreditosGAAP) * u.Factor as SaldoFinGAAP, 
	u.SaldoIniIFRS, u.TotalDebitosIFRS, u.TotalCreditosIFRS, u.SaldoIniIFRS + (u.TotalDebitosIFRS - u.TotalCreditosIFRS) * u.Factor as SaldoFinIFRS,  
	u.DetallarCliente,
	SUBSTRING(u.CodCuenta,1,1) AS CodNiv1, SUBSTRING(u.CodCuenta,1,2) AS CodNiv2, SUBSTRING(u.CodCuenta,1,4) AS CodNiv3, SUBSTRING(u.CodCuenta,1,6) AS CodNiv4, SUBSTRING(u.CodCuenta,1,8) AS CodNiv5, SUBSTRING(u.CodCuenta,1,10) AS CodNiv6, SUBSTRING(u.CodCuenta,1,12) AS CodNiv7,
	Cta1.NomCuentaBase AS NomCta1, 
	Cta2.NomCuentaBase AS NomCta2, 
	Cta3.NomCuentaBase AS NomCta3, 
	Cta4.NomCuentaBase AS NomCta4, 
	Cta5.NomCuentaBase AS NomCta5, 
	Cta6.NomCuentaBase AS NomCta6, 
	Cta7.NomCuentaBase AS NomCta7,
    Cta1.NomCuentaEspejo AS NomCtaEsp1, 
	Cta2.NomCuentaEspejo AS NomCtaEsp2, 
	Cta3.NomCuentaEspejo AS NomCtaEsp3, 
	Cta4.NomCuentaEspejo AS NomCtaEsp4, 
	Cta5.NomCuentaEspejo AS NomCtaEsp5, 
	Cta6.NomCuentaEspejo AS NomCtaEsp6, 
	Cta7.NomCuentaEspejo AS NomCtaEsp7,
	Cta1.CodCuentaEspejo AS CodEsp1, 
	Cta2.CodCuentaEspejo AS CodEsp2, 
	Cta3.CodCuentaEspejo AS CodEsp3, 
	Cta4.CodCuentaEspejo AS CodEsp4, 
	Cta5.CodCuentaEspejo AS CodEsp5, 
	Cta6.CodCuentaEspejo AS CodEsp6, 
	Cta7.CodCuentaEspejo AS CodEsp7,
	isnull(Cta3.CodCuenta+', ' ,'')+isnull(Cta4.CodCuenta,'')  as CodCtaUgpp,
	isnull(Cta3.NomCuentaBase+', ' ,'')+isnull(Cta4.NomCuentaBase,'')  as NomCtaUgpp
	FROM
	(
		SELECT case when @EsAgrupIFRS=1 and len(CodCuentaIFRS)> 0 then COALESCE(CodCuentaIFRS,CodCuenta) else CodCuenta end CodCuenta, 
		NomCuenta, DetallarCliente, Factor, SenDet, IdCliente, SUM(SaldoIni) as SaldoIni, Sum(SaldoIniExt) as SaldoIniExt, SUM(Debito) as TotalDebitos, SUM(Credito) as TotalCreditos, SUM(DebitoExt) as TotalDebitosExt, SUM(CreditoExt) as TotalCreditosExt,max(isnull(CodCuentaIFRS,''))CodCuentaIFRS,
		SUM(DebitoGAAP) as TotalDebitosGAAP, SUM(CreditoGAAP) as TotalCreditosGAAP,SUM(DebitoIFRS) as TotalDebitosIFRS, SUM(CreditoIFRS) as TotalCreditosIFRS,SUM(SaldoIniGAAP) as SaldoIniGAAP,SUM(SaldoIniIFRS) as SaldoIniIFRS
		FROM 
		(
			SELECT * FROM #tmpMov UNION ALL SELECT * FROM #tmpSaldoIni 
		) t
		GROUP BY
		case when @EsAgrupIFRS=1 and len(CodCuentaIFRS)> 0 then COALESCE(CodCuentaIFRS,CodCuenta) else CodCuenta end, 
		NomCuenta, DetallarCliente, Factor, SenDet, IdCliente
		HAVING 
		NOT(SUM(SaldoIni)=0 And SUM(Debito)= 0 And SUM(Credito) = 0) 
) u	LEFT OUTER JOIN dbo.VClientes2 AS Cli ON u.IdCliente = Cli.IdCliente LEFT OUTER JOIN dbo.VClientesDir AS CliDir ON u.IdCliente = CliDir.IdCliente
	LEFT OUTER JOIN
	#tempCuentas AS Cta1 ON SUBSTRING(u.CodCuenta,1,1) = Cta1.CodCuenta LEFT OUTER JOIN
	#tempCuentas AS Cta2 ON SUBSTRING(u.CodCuenta,1,2) = Cta2.CodCuenta LEFT OUTER JOIN
	#tempCuentas AS Cta3 ON SUBSTRING(u.CodCuenta,1,4) = Cta3.CodCuenta LEFT OUTER JOIN
	#tempCuentas AS Cta4 ON SUBSTRING(u.CodCuenta,1,6) = Cta4.CodCuenta LEFT OUTER JOIN
	#tempCuentas AS Cta5 ON SUBSTRING(u.CodCuenta,1,8) = Cta5.CodCuenta LEFT OUTER JOIN
	#tempCuentas AS Cta6 ON SUBSTRING(u.CodCuenta,1,10) = Cta6.CodCuenta LEFT OUTER JOIN
	#tempCuentas AS Cta7 ON SUBSTRING(u.CodCuenta,1,12) = Cta7.CodCuenta 
) q1
ORDER BY q1.CodCuenta, q1.[Cuenta_Tercero]

OPTION(RECOMPILE)
END
GO
PRINT N'Altering Procedure [dbo].[GetEstadoCuentaFechaCorte]...';
IF OBJECT_ID(N'dbo.GetEstadoCuentaFechaCorte', N'P') IS NULL
    EXEC(N'CREATE PROCEDURE [dbo].[GetEstadoCuentaFechaCorte] AS BEGIN SET NOCOUNT ON; RETURN 0; END');

GO
ALTER PROCEDURE GetEstadoCuentaFechaCorte 
	@FechaFin NVARCHAR(10) = '',
	@IdCliente INT = NULL,
    @CuentaIni NVARCHAR(100)=NULL,
	@CuentaFin NVARCHAR(100)=NULL,
	@IdCuentaTipos NVARCHAR(100),
	@IdEmpresas NVARCHAR(1000) = '',
	@IdVendedor INT = NULL,
	@IdSucursal INT = NULL,
	@IdCentroCosto INT = NULL,
    @EsNotGAAP BIT = NULL,
    @EsIFRS BIT = NULL,
	@FechaIniR NVARCHAR(10) = NULL,
	@FechaFinR NVARCHAR(10) =NULL,
	@esPagos BIT=0,
	@IdDir INT = NULL


AS

BEGIN

DECLARE

@FechaFinT DATETIME,
@DataPar NVARCHAR(200),
@FechaIniRT DATETIME=null,
@FechaFinRT DATETIME=null,
@EsMostrarPagos int=0,
@PorVencerCol1Ini INT, @PorVencerCol1Fin INT, @PorVencerCol2Ini INT, @PorVencerCol2Fin INT, @PorVencerCol3Ini INT, @PorVencerCol3Fin INT, @PorVencerCol4Ini INT,@PorVencerCol4Fin INT,@PorVencerCol5Ini INT,@PorVencerCol5Fin INT,@PorVencerCol6Ini INT,@PorVencerCol6Fin INT,
@VencidaCol1Ini INT,@VencidaCol1Fin INT,@VencidaCol2Ini INT,@VencidaCol2Fin INT,@VencidaCol3Ini INT,@VencidaCol3Fin INT,@VencidaCol4Ini INT,@VencidaCol4Fin INT,@VencidaCol5Ini INT,@VencidaCol5Fin INT,@VencidaCol6Ini INT,@VencidaCol6Fin INT

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SET @FechaFinT = CAST(CONVERT(DATETIME, @FechaFin, 103) + ' 23:59:59' AS DATETIME)

if @FechaIniR is not null
SET @FechaIniRT = CAST(CONVERT(DATETIME, @FechaIniR, 103) + ' 00:00:00' AS DATETIME)

if @FechaFinR is not null
SET @FechaFinRT = CAST(CONVERT(DATETIME, @FechaFinR, 103) + ' 23:59:59' AS DATETIME)

if @esPagos<>0
set @EsMostrarPagos=null

/* Por Vencer Columna 1 */
/* ------------------------------------------- */
SET @DataPar = NULL
SELECT @DataPar = ValParametro FROM PARAMETROS WHERE CodParametro = 31
IF @DataPar IS NULL
	SET @DataPar = '0,30'

SET @PorVencerCol1Ini = CAST(PARSENAME(REPLACE(@DataPar, ',', '.'), 2) AS INT)
SET @PorVencerCol1Fin = CAST(PARSENAME(REPLACE(@DataPar, ',', '.'), 1) AS INT)
/* ------------------------------------------- */

/* Por Vencer Columna 2 */
/* ------------------------------------------- */
SET @DataPar = NULL
SELECT @DataPar = ValParametro FROM PARAMETROS WHERE CodParametro = 32
IF @DataPar IS NULL
	SET @DataPar = '31,60'

SET @PorVencerCol2Ini = CAST(PARSENAME(REPLACE(@DataPar, ',', '.'), 2)  AS INT)
SET @PorVencerCol2Fin = CAST(PARSENAME(REPLACE(@DataPar, ',', '.'), 1) AS INT)
/* ------------------------------------------- */

/* Por Vencer Columna 3 */
/* ------------------------------------------- */
SET @DataPar = NULL
SELECT @DataPar = ValParametro FROM PARAMETROS WHERE CodParametro = 33
IF @DataPar IS NULL
	SET @DataPar = '61,90'

SET @PorVencerCol3Ini = CAST(PARSENAME(REPLACE(@DataPar, ',', '.'), 2)  AS INT)
SET @PorVencerCol3Fin = CAST(PARSENAME(REPLACE(@DataPar, ',', '.'), 1) AS INT)
/* ------------------------------------------- */

/* Por Vencer Columna 4 */
/* ------------------------------------------- */
SET @DataPar = NULL
SELECT @DataPar = ValParametro FROM PARAMETROS WHERE CodParametro = 34
IF @DataPar IS NULL
	SET @DataPar = '91,180'

SET @PorVencerCol4Ini = CAST(PARSENAME(REPLACE(@DataPar, ',', '.'), 2)  AS INT)
SET @PorVencerCol4Fin = CAST(PARSENAME(REPLACE(@DataPar, ',', '.'), 1) AS INT)
/* ------------------------------------------- */

/* Por Vencer Columna 5 */
/* ------------------------------------------- */
SET @DataPar = NULL
SELECT @DataPar = ValParametro FROM PARAMETROS WHERE CodParametro = 35
IF @DataPar IS NULL
	SET @DataPar = '181,360'

SET @PorVencerCol5Ini = CAST(PARSENAME(REPLACE(@DataPar, ',', '.'), 2)  AS INT)
SET @PorVencerCol5Fin = CAST(PARSENAME(REPLACE(@DataPar, ',', '.'), 1) AS INT)
/* ------------------------------------------- */

/* Por Vencer Columna 6 */
/* ------------------------------------------- */
SET @DataPar = NULL
SELECT @DataPar = ValParametro FROM PARAMETROS WHERE CodParametro = 36
IF @DataPar IS NULL
	SET @DataPar = '360,999999'

SET @PorVencerCol6Ini = CAST(PARSENAME(REPLACE(@DataPar, ',', '.'), 2)  AS INT)
SET @PorVencerCol6Fin = CAST(PARSENAME(REPLACE(@DataPar, ',', '.'), 1) AS INT)
/* ------------------------------------------- */


/* Vencida Columna 1 */
/* ------------------------------------------- */
SET @DataPar = NULL
SELECT @DataPar = ValParametro FROM PARAMETROS WHERE CodParametro = 21
IF @DataPar IS NULL
	SET @DataPar = '0,30'

SET @VencidaCol1Ini = CAST(PARSENAME(REPLACE(@DataPar, ',', '.'), 2) AS INT) * -1
SET @VencidaCol1Fin = CAST(PARSENAME(REPLACE(@DataPar, ',', '.'), 1) AS INT) * -1
/* ------------------------------------------- */

/* Vencida Columna 2 */
/* ------------------------------------------- */
SET @DataPar = NULL
SELECT @DataPar = ValParametro FROM PARAMETROS WHERE CodParametro = 22
IF @DataPar IS NULL
	SET @DataPar = '31,60'

SET @VencidaCol2Ini = CAST(PARSENAME(REPLACE(@DataPar, ',', '.'), 2)  AS INT) * -1
SET @VencidaCol2Fin = CAST(PARSENAME(REPLACE(@DataPar, ',', '.'), 1) AS INT) * -1
/* ------------------------------------------- */

/* Vencida Columna 3 */
/* ------------------------------------------- */
SET @DataPar = NULL
SELECT @DataPar = ValParametro FROM PARAMETROS WHERE CodParametro = 23
IF @DataPar IS NULL
	SET @DataPar = '61,90'

SET @VencidaCol3Ini = CAST(PARSENAME(REPLACE(@DataPar, ',', '.'), 2)  AS INT) * -1
SET @VencidaCol3Fin = CAST(PARSENAME(REPLACE(@DataPar, ',', '.'), 1) AS INT) * -1
/* ------------------------------------------- */

/* Vencida Columna 4 */
/* ------------------------------------------- */
SET @DataPar = NULL
SELECT @DataPar = ValParametro FROM PARAMETROS WHERE CodParametro = 24
IF @DataPar IS NULL
	SET @DataPar = '91,180'

SET @VencidaCol4Ini = CAST(PARSENAME(REPLACE(@DataPar, ',', '.'), 2)  AS INT) * -1
SET @VencidaCol4Fin = CAST(PARSENAME(REPLACE(@DataPar, ',', '.'), 1) AS INT) * -1
/* ------------------------------------------- */

/* Vencida Columna 5 */
/* ------------------------------------------- */
SET @DataPar = NULL
SELECT @DataPar = ValParametro FROM PARAMETROS WHERE CodParametro = 25
IF @DataPar IS NULL
	SET @DataPar = '181,360'

SET @VencidaCol5Ini = CAST(PARSENAME(REPLACE(@DataPar, ',', '.'), 2)  AS INT) * -1
SET @VencidaCol5Fin = CAST(PARSENAME(REPLACE(@DataPar, ',', '.'), 1) AS INT) * -1
/* ------------------------------------------- */

/* Vencida Columna 6 */
/* ------------------------------------------- */
SET @DataPar = NULL
SELECT @DataPar = ValParametro FROM PARAMETROS WHERE CodParametro = 26
IF @DataPar IS NULL
	SET @DataPar = '360,999999'

SET @VencidaCol6Ini = CAST(PARSENAME(REPLACE(@DataPar, ',', '.'), 2)  AS INT) * -1
SET @VencidaCol6Fin = CAST(PARSENAME(REPLACE(@DataPar, ',', '.'), 1) AS INT) * -1
/* ------------------------------------------- */


SELECT 
q1.CodCuenta, 
q1.NomCuenta, 
q1.IdCuentaTipo, 
q1.Tipo, 
q1.IdAbonaDoc, 
q1.IdCliente, 
q1.Saldo, 
q1.Vence,
q1.Dias,
q1.Nombres, 
q1.Apellidos, 
q1.NomCompleto,
q1.CodDocumento, 
q1.Fecha,
q1.IdPrefijo, 
q1.NumDocumento,
q1.NumDocumentoExt,
q1.IdEncab,
q1.IdDocumento,
q1.NomZona1,
LTRIM(RTRIM(q1.DescripNum +  ' ' + q1.DescripNum2)) AS DescripNum,
CASE WHEN q1.Dias BETWEEN @PorVencerCol1Ini AND @PorVencerCol1Fin THEN q1.Saldo ELSE 0 END PorVencerCol1,
CASE WHEN q1.Dias BETWEEN @PorVencerCol2Ini AND @PorVencerCol2Fin THEN q1.Saldo ELSE 0 END PorVencerCol2,
CASE WHEN q1.Dias BETWEEN @PorVencerCol3Ini AND @PorVencerCol3Fin THEN q1.Saldo ELSE 0 END PorVencerCol3,
CASE WHEN q1.Dias BETWEEN @PorVencerCol4Ini AND @PorVencerCol4Fin THEN q1.Saldo ELSE 0 END PorVencerCol4,
CASE WHEN q1.Dias BETWEEN @PorVencerCol5Ini AND @PorVencerCol5Fin THEN q1.Saldo ELSE 0 END PorVencerCol5,
CASE WHEN q1.Dias BETWEEN @PorVencerCol6Ini AND @PorVencerCol6Fin THEN q1.Saldo ELSE 0 END PorVencerCol6,
CASE WHEN q1.Dias BETWEEN @VencidaCol1Ini AND @VencidaCol1Fin THEN q1.Saldo ELSE 0 END VencidaCol1,
CASE WHEN q1.Dias BETWEEN @VencidaCol2Ini AND @VencidaCol2Fin THEN q1.Saldo ELSE 0 END VencidaCol2,
CASE WHEN q1.Dias BETWEEN @VencidaCol3Ini AND @VencidaCol3Fin THEN q1.Saldo ELSE 0 END VencidaCol3,
CASE WHEN q1.Dias BETWEEN @VencidaCol4Ini AND @VencidaCol4Fin THEN q1.Saldo ELSE 0 END VencidaCol4,
CASE WHEN q1.Dias BETWEEN @VencidaCol5Ini AND @VencidaCol5Fin THEN q1.Saldo ELSE 0 END VencidaCol5,
CASE WHEN q1.Dias BETWEEN @VencidaCol6Ini AND @VencidaCol6Fin THEN q1.Saldo ELSE 0 END VencidaCol6,
q1.SaldoExt,
q1.ValorTRM,
q1.NomMoneda1,
CASE WHEN q1.Dias BETWEEN @PorVencerCol1Ini AND @PorVencerCol1Fin THEN q1.SaldoExt ELSE 0 END PorVencerCol1Ext,
CASE WHEN q1.Dias BETWEEN @PorVencerCol2Ini AND @PorVencerCol2Fin THEN q1.SaldoExt ELSE 0 END PorVencerCol2Ext,
CASE WHEN q1.Dias BETWEEN @PorVencerCol3Ini AND @PorVencerCol3Fin THEN q1.SaldoExt ELSE 0 END PorVencerCol3Ext,
CASE WHEN q1.Dias BETWEEN @PorVencerCol4Ini AND @PorVencerCol4Fin THEN q1.SaldoExt ELSE 0 END PorVencerCol4Ext,
CASE WHEN q1.Dias BETWEEN @PorVencerCol5Ini AND @PorVencerCol5Fin THEN q1.SaldoExt ELSE 0 END PorVencerCol5Ext,
CASE WHEN q1.Dias BETWEEN @PorVencerCol6Ini AND @PorVencerCol6Fin THEN q1.SaldoExt ELSE 0 END PorVencerCol6Ext,
CASE WHEN q1.Dias BETWEEN @VencidaCol1Ini AND @VencidaCol1Fin THEN q1.SaldoExt ELSE 0 END VencidaCol1Ext,
CASE WHEN q1.Dias BETWEEN @VencidaCol2Ini AND @VencidaCol2Fin THEN q1.SaldoExt ELSE 0 END VencidaCol2Ext,
CASE WHEN q1.Dias BETWEEN @VencidaCol3Ini AND @VencidaCol3Fin THEN q1.SaldoExt ELSE 0 END VencidaCol3Ext,
CASE WHEN q1.Dias BETWEEN @VencidaCol4Ini AND @VencidaCol4Fin THEN q1.SaldoExt ELSE 0 END VencidaCol4Ext,
CASE WHEN q1.Dias BETWEEN @VencidaCol5Ini AND @VencidaCol5Fin THEN q1.SaldoExt ELSE 0 END VencidaCol5Ext,
CASE WHEN q1.Dias BETWEEN @VencidaCol6Ini AND @VencidaCol6Fin THEN q1.SaldoExt ELSE 0 END VencidaCol6Ext,
CreditoDet,DebitoDet, DebitoDet-CreditoDet AS SaldoDet,
NomAgrup1,NomAgrup2,NomAgrup3,
Direccion,Telefonos,Celular,NomCiudad,EMail,[Intereses],[DocReferencia],Identificacion,
TasaMoraParteSuperior,TasaMoraParteInferior,Saldo*((TasaMoraParteSuperior/TasaMoraParteInferior)/100)*Dias as TarifaMora,
DirDocCompleta,Nota,
q1.NomCompletoVend,
q1.IdentificacionVend

FROM
(
	SELECT 
	Cta.CodCuenta, 
	Cta.NomCuenta, 
	Cta.IdCuentaTipo, 
	CASE WHEN IdCuentaTipo = 1 THEN 'COBRAR' ELSE 'PAGAR' END AS Tipo, 
	Saldos.IdAbonaDoc, 
	Saldos.IdCliente, 
	Saldos.Saldo, 
	Saldos.Vence as Vence,
	DATEDIFF(day,Saldos.Vence,@FechaFinT) AS Dias,
	Cli.Nombres, 
	Cli.Apellidos, 
	LTRIM(RTRIM(Cli.Apellidos + ' ' + Cli.Nombres)) AS NomCompleto,
	Doc.CodDocumento, 
	Enc.Fecha,
	Enc.IdPrefijo, 
	Enc.NumDocumento,
	Enc.NumDocumentoExt, 
	Enc.IdEncab,
	Doc.IdDocumento,
	LTRIM(RTRIM(Doc.CodDocumento + ' ' + COALESCE (Enc.IdPrefijo, ''))) + ' ' + CAST(Enc.NumDocumento AS NVARCHAR(200)) AS DescripNum,
	CASE WHEN Enc.IdDocumento = 17 THEN Enc_C.Nota ELSE '' END AS DescripNum2,
	NomZona1,
	Saldos.SaldoExt,
	Enc_C.ValorTRM,
	Mon.NomMoneda1,
	Enc_CDet.Credito as CreditoDet,
	Enc_CDet.Debito as DebitoDet,
	NomAgrup1,NomAgrup2,NomAgrup3,
	dir.Direccion,dir.Telefonos,dir.Celular,dir.NomCiudad,dir.EMail,det.[Intereses],Enc_C.[DocReferencia],Cli.Identificacion,
	Cli.TasaMoraParteSuperior,Cli.TasaMoraParteInferior,
	cast(coalesce(dirDoc.Nota,'') as nvarchar(20))+'-'+ coalesce(dirDoc.NomCiudad,'') +'-'+ dirDoc.Direccion as DirDocCompleta,
	Enc.Nota,
    LTRIM(RTRIM(CliV.Apellidos + ' ' + CliV.Nombres)) AS NomCompletoVend,
	CliV.Identificacion as IdentificacionVend
	FROM
	(
		SELECT EncCont.IdAbonaDoc, EncCont1.IdCliente, cast(EncCont1.Vence as date) AS Vence, EncCont1.IdCentroCosto, SUM(EncCont.Debito - EncCont.Credito) AS Saldo,
		SUM( EncCont.CreditoExt-EncCont.DebitoExt) AS SaldoExt
		FROM   
		dbo.Encabezados_Cont AS EncCont INNER JOIN
        dbo.Encabezados_Cont AS EncCont1 ON EncCont.IdAbonaDoc = EncCont1.IdEncabCont INNER JOIN
        dbo.Cuentas AS Cta ON EncCont.IdCuenta = Cta.IdCuenta INNER JOIN
        dbo.Encabezados AS Enc ON EncCont.IdEncab = Enc.IdEncab INNER JOIN
        dbo.Cuentas_Tipos ON Cta.IdCuentaTipo = dbo.Cuentas_Tipos.IdCuentaTipo INNER JOIN
        dbo.Cuentas_Clases ON dbo.Cuentas_Tipos.IdClase = dbo.Cuentas_Clases.IdClase INNER JOIN
        dbo.Encabezados AS Enc2 ON EncCont1.IdEncab = Enc2.IdEncab
		INNER JOIN (select number from iter$simple_intlist_to_tbl(@IdCuentaTipos)) T1 on T1.number=Cta.IdCuentaTipo
		INNER JOIN (select number from iter$simple_intlist_to_tbl(@IdEmpresas)) T2 on T2.number=Enc.IdEmpresa
		WHERE
		Cta.CodCuenta Between coalesce(@CuentaIni,Cta.CodCuenta) And coalesce(@CuentaFin,Cta.CodCuenta) AND
		Enc.EsAnulado = 0 AND
		Enc2.EsAnulado = 0 AND
		EncCont.EsNotGAAP=coalesce(@EsNotGAAP,EncCont.EsNotGAAP) and EncCont.EsIFRS=coalesce(@EsIFRS, EncCont.EsIFRS) and 
		Enc.Fecha < @FechaFinT
	    GROUP BY EncCont.IdAbonaDoc, EncCont1.IdCliente, cast(EncCont1.Vence as date), EncCont1.IdCentroCosto
		HAVING (SUM(EncCont.Credito - EncCont.Debito) <> coalesce(@EsMostrarPagos,SUM(EncCont.Credito - EncCont.Debito)+1))
	) Saldos
	INNER JOIN
	dbo.Encabezados_Cont AS Enc_C ON Saldos.IdAbonaDoc = Enc_C.IdEncabCont INNER JOIN
	dbo.Cuentas AS Cta ON Enc_C.IdCuenta = Cta.IdCuenta INNER JOIN
	dbo.VClientes2 AS Cli ON Saldos.IdCliente = Cli.IdCliente INNER JOIN
	dbo.Encabezados AS Enc ON Enc_C.IdEncab = Enc.IdEncab INNER JOIN
	dbo.Documentos AS Doc ON Enc.IdDocumento = Doc.IdDocumento LEFT JOIN
	dbo.VClientes2 AS CliV ON Enc.IdClienteVend = CliV.IdCliente LEFT JOIN
	dbo.Clientes_Zona1 zon1 ON Cli.IdZona1=zon1.IdZona1 LEFT JOIN
	dbo.Monedas as Mon on Enc_C.IdMoneda=Mon.IdMoneda  LEFT JOIN
	dbo.Encabezados_Cont AS Enc_CDet ON Saldos.IdAbonaDoc = Enc_CDet.IdAbonaDocDet LEFT JOIN
	[dbo].[Clientes_Agrup1] agrup1 ON Cli.IdAgrup1=agrup1.IdAgrup1 LEFT JOIN
	[dbo].[Clientes_Agrup2] agrup2 ON Cli.IdAgrup2=agrup2.IdAgrup2 LEFT JOIN
	[dbo].[Clientes_Agrup3] agrup3 ON Cli.IdAgrup3=agrup3.IdAgrup3 LEFT JOIN
    [dbo].[VClientesDir] dir ON Cli.IdCliente=dir.IdCliente LEFT JOIN
    [dbo].[VClientesDir_DetFin] dirDoc ON Enc.IdCliente1_IdDir=dirDoc.IdClienteDir LEFT JOIN
	[dbo].[IFRS_Intervalos_Deterioro] det ON coalesce(Cli.IdRiesgo,-1)=coalesce(det.IdRiesgo,-1)
	and coalesce(DATEDIFF(day,Saldos.Vence,@FechaFinT),-100) between det.DiasIni and det.DiasFin
	WHERE
	(@IdCliente is null OR Saldos.IdCliente=@IdCliente)AND 
	(@IdVendedor is null OR Enc.IdClienteVend=@IdVendedor)AND 
	(@IdSucursal is null OR Enc.IdSucursal=@IdSucursal)AND 
	(@IdCentroCosto is null OR Enc_C.IdCentroCosto=@IdCentroCosto)AND 
     Enc.Fecha between COALESCE(@FechaIniRT,Enc.Fecha) and  COALESCE(@FechaFinRT,Enc.Fecha) and
	(@IdDir is null OR Enc.IdCliente1_IdDir=@IdDir) 
)
q1
OPTION(RECOMPILE)
END
GO
PRINT N'Altering Procedure [dbo].[GetInvKardexPorLote]...';
IF OBJECT_ID(N'dbo.GetInvKardexPorLote', N'P') IS NULL
    EXEC(N'CREATE PROCEDURE [dbo].[GetInvKardexPorLote] AS BEGIN SET NOCOUNT ON; RETURN 0; END');

GO
ALTER PROCEDURE [dbo].[GetInvKardexPorLote]

@FechaCorte NVARCHAR(10) = '',
@Lote BIT=0,
@IdEmpresas NVARCHAR(1000) = '',
@IdInventario INT = null,
@IdBodega INT = null,
@IdLote INT = null,
@EsBodega BIT=1,
@EsSerial BIT=0,
@EsSinExist BIT=1,
@EsValidarMax BIT=0

AS
BEGIN


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON;	

DECLARE

@FechaCorteTemp datetime
SET @FechaCorteTemp = CAST(convert(datetime, @FechaCorte, 103) + ' 23:59:59' AS DATETIME)


IF LEN(@IdEmpresas)=0
	SELECT @IdEmpresas= COALESCE(@IdEmpresas + ', ', '') + CAST(IdEmpresa as nvarchar(50)) FROM empresas

SELECT 
isnull(Mov.IdInventario,VCru.IdInventario) AS IdInventario,
isnull(Mov.IdBodega,VCru.IdBodega) AS IdBodega,
isnull(Mov.IdLote,VCru.IdLote) AS IdLote,
isnull(Mov.NumSerial,VCru.NumSerial) AS NumSerial,
isnull(Mov.IdTipoReg,VCru.IdTipoReg) AS IdTipoReg,
SUM(isnull(Mov.Cantidad*Mov.FactorCantidad*Mov.FactorMovim,SumCantidad)*Doc.FactorInventarioTeorico) AS ExistenciaTeo
INTO #tmpSaldoTeo
from dbo.VCruces_TodosDocs VCru 
INNER JOIN Documentos Doc ON VCru.IdDocumento=Doc.IdDocumento LEFT JOIN
Encabezados_Mov as Mov on Vcru.IdEncabMov=Mov.IdEncabMov2
WHERE  
VCru.Fecha <= @FechaCorteTemp AND 
VCru.EsAnulado=0 AND
ISNULL(VCru.IdEstado,0)<>-5 AND
Doc.FactorInventarioTeorico<>0  AND
(@IdInventario IS NULL OR Mov.IdInventario = @IdInventario) AND
(@IdInventario IS NULL OR VCru.IdInventario = @IdInventario) 
GROUP BY   
isnull(Mov.IdInventario,VCru.IdInventario),
isnull(Mov.IdBodega,VCru.IdBodega),
isnull(Mov.IdLote,VCru.IdLote),
isnull(Mov.NumSerial,VCru.NumSerial),
isnull(Mov.IdTipoReg,VCru.IdTipoReg)

SELECT EncMov.IdInventario, 
SUM(EncMov.Cantidad * EncMov.FactorCantidad * Doc.FactorInventario * EncMov.FactorMovim) AS Existencia,
SUM(ISNULL(EncMov.Cantidad1,0) * Doc.FactorInventario * EncMov.FactorMovim) AS Existencia1,  
MAX(EncMov.ValorTRM) AS ValorTRM,NumSerial,IdTipoReg,IdBodega,IdLote
INTO #tmpSaldo
FROM  
dbo.Encabezados AS Enc 
INNER JOIN dbo.Encabezados_Mov AS EncMov ON Enc.IdEncab = EncMov.IdEncab 
INNER JOIN dbo.Documentos AS Doc ON Enc.IdDocumento = Doc.IdDocumento
INNER JOIN (select number from iter$simple_intlist_to_tbl(@IdEmpresas))T1 on T1.number=Enc.IdEmpresa
WHERE
Doc.FactorInventario <> 0 AND 
Enc.Fecha <= @FechaCorteTemp AND 
EncMov.FactorMovim <> 0 AND
Enc.EsAnulado=0 AND
(@IdInventario IS NULL OR EncMov.IdInventario = @IdInventario) AND
(@IdBodega IS NULL OR EncMov.IdBodega = @IdBodega) AND
(@IdLote IS NULL OR EncMov.IdLote = @IdLote) 
GROUP BY  EncMov.IdInventario,EncMov.IdBodega,EncMov.IdLote,EncMov.NumSerial,EncMov.IdTipoReg

--Por Lote, serial y Bodega
SELECT Saldo.IdInventario,Existencia,Existencia1,ValorTRM,Saldo.NumSerial,Saldo.IdTipoReg,Saldo.IdBodega,Saldo.IdLote
INTO #tmpFin
from #tmpSaldo as Saldo
where ISNULL(@EsBodega,0)=1 and ISNULL(@EsSerial,0)=1 and ISNULL(@Lote,0)=1
				
union all 

--Por Lote y serial
SELECT Saldo.IdInventario,SUM(Existencia)Existencia,SUM(Existencia1)Existencia1,MAX(ValorTRM)ValorTRM,Saldo.NumSerial,Saldo.IdTipoReg,null as IdBodega,Saldo.IdLote
from #tmpSaldo as Saldo
where ISNULL(@EsSerial,0)=1 and ISNULL(@Lote,0)=1 and ISNULL(@EsBodega,0)=0
group by Saldo.IdInventario,Saldo.IdLote,Saldo.NumSerial,Saldo.IdTipoReg

union all 

--Por Lote y Bodega
SELECT Saldo.IdInventario,SUM(Existencia)Existencia,SUM(Existencia1)Existencia1,MAX(ValorTRM)ValorTRM,null as NumSerial, null as IdTipoReg,Saldo.IdBodega,Saldo.IdLote
from #tmpSaldo  Saldo
where ISNULL(@EsBodega,0)=1 and ISNULL(@EsSerial,0)=0 and ISNULL(@Lote,0)=1
group by Saldo.IdInventario,Saldo.IdBodega,Saldo.IdLote

union all 

--Solo por lote 
SELECT Saldo.IdInventario,SUM(Existencia)Existencia,SUM(Existencia1)Existencia1,MAX(ValorTRM)ValorTRM,null as NumSerial, null as IdTipoReg, null as IdBodega,Saldo.IdLote
from #tmpSaldo  Saldo
where ISNULL(@EsBodega,0)=0 and ISNULL(@EsSerial,0)=0 and ISNULL(@Lote,0)=1
group by Saldo.IdInventario,Saldo.IdLote

union all 

--Por serial y Bodega
SELECT Saldo.IdInventario,SUM(Existencia)Existencia,SUM(Existencia1)Existencia1,MAX(ValorTRM)ValorTRM,Saldo.NumSerial,Saldo.IdTipoReg,Saldo.IdBodega,null as IdLote
from #tmpSaldo  Saldo
where ISNULL(@EsBodega,0)=1 and ISNULL(@EsSerial,0)=1 and ISNULL(@Lote,0)=0
group by Saldo.IdInventario,Saldo.IdBodega,Saldo.NumSerial,Saldo.IdTipoReg

union all 

--Solo por serial
SELECT Saldo.IdInventario,SUM(Existencia)Existencia,SUM(Existencia1)Existencia1,MAX(ValorTRM)ValorTRM,Saldo.NumSerial,Saldo.IdTipoReg,null as IdBodega,null as IdLote
from #tmpSaldo  Saldo
where ISNULL(@EsBodega,0)=0 and ISNULL(@EsSerial,0)=1 and ISNULL(@Lote,0)=0
group by Saldo.IdInventario,Saldo.NumSerial,Saldo.IdTipoReg

union all 

--Solo por Bodega
SELECT Saldo.IdInventario,SUM(Existencia)Existencia,SUM(Existencia1)Existencia1,MAX(ValorTRM)ValorTRM,null as NumSerial, null as IdTipoReg,Saldo.IdBodega,null as IdLote
from #tmpSaldo  Saldo
where ISNULL(@EsBodega,0)=1 and ISNULL(@EsSerial,0)=0 and ISNULL(@Lote,0)=0
group by Saldo.IdInventario,Saldo.IdBodega

union all 

--Solo Inventario
SELECT Saldo.IdInventario,SUM(Existencia)Existencia,SUM(Existencia1)Existencia1,MAX(ValorTRM)ValorTRM,null as NumSerial, null as IdTipoReg, null as IdBodega,null as IdLote
from #tmpSaldo  Saldo
where ISNULL(@EsBodega,0)=0 and ISNULL(@EsSerial,0)=0 and ISNULL(@Lote,0)=0
group by Saldo.IdInventario

select saldo.IdInventario,saldo.Existencia,saldo.Existencia1, saldo.ValorTRM,saldo.NumSerial,saldo.IdTipoReg,saldo.IdBodega,saldo.IdLote,sum(isnull(ExistenciaTeo,0))ExistenciaTeo,
max(isnull(maximoGeneral.CantMaxima,0)) CantMaxima,max(isnull(maximoGeneral.CantMinima,0))CantMinima,max(isnull(maximoGeneral.PuntoReorden,0))PuntoReorden,
max(cast(isnull(maximoGeneral.EsOk,0) as int)) EsOk,max(cast(isnull(maximoGeneral.EsError,0) as int)) EsError,max(cast(isnull(maximoGeneral.EsWarning,0) as int)) EsWarning, 
max(isnull(maximoGeneral.TimeIni,0)) TimeIni,max(isnull(maximoGeneral.TimeFinal,0)) TimeFinal,max(isnull(maximoGeneral.TimeReminder,0)) TimeReminder,
max(isnull(maximoGeneral.IdMaximo,0)) IdMaximo, MAX(ISNULL(IdUsuarios,''))IdUsuarios,MAX(ISNULL(IdRoles,''))IdRoles
INTO #tmpTotalFin
from #tmpFin as saldo 
left join 
#tmpSaldoTeo as teo on saldo.IdInventario=teo.IdInventario and (saldo.IdBodega=teo.IdBodega OR ISNULL(@EsBodega,0)=0)and
(saldo.IdLote=teo.IdLote OR ISNULL(@Lote,0)=0) and (saldo.NumSerial=teo.NumSerial and saldo.IdTipoReg=teo.IdTipoReg OR  ISNULL(@EsSerial,0)=0)
	left join 
	VMaximosYMinimos as maximoGeneral on saldo.IdInventario=maximoGeneral.IdInventario and ISNULL(@EsBodega,0)=maximoGeneral.EsBodega and isnull(saldo.IdBodega,0)=COALESCE(maximoGeneral.IdBodega,saldo.IdBodega,0)
	and ISNULL(@Lote,0)=maximoGeneral.EsLote and ISNULL(saldo.IdLote,0)=COALESCE(maximoGeneral.IdLote,saldo.IdLote,0) and ISNULL(@EsSerial,0)=maximoGeneral.EsSerial and ISNULL(saldo.NumSerial,0)=COALESCE(maximoGeneral.NumSerial,saldo.NumSerial,0)
and @EsValidarMax=1
group by saldo.IdInventario,saldo.Existencia,saldo.Existencia1, saldo.ValorTRM,saldo.NumSerial,saldo.IdTipoReg,saldo.IdBodega,saldo.IdLote

SELECT q1.*,Existencia1 AS ExistenciaUni1,costPromInv.CostoProm, CASE WHEN q1.ValorTRM = 0 THEN 0 ELSE costPromInv.CostoProm / q1.ValorTRM END AS CostoPromExt
FROM
(
	SELECT EncMov.IdInventario,EncMov.IdLote AS IdLote, Inv_L.CodLote AS CodLote, Inv_L.NomLote AS Nomlote, Inv.IdUnidad, 
	Existencia,
	Existencia1,
	ExistenciaTeo,
	Inv.CodInventario,  (EncMov.ValorTRM) AS ValorTRM, Inv_L.FechaVence AS FechaVence,
	Inv.NomInventario,
	ISNULL(EncMov.CantMaxima,0) CantMaxima,
	ISNULL(EncMov.CantMinima,0) CantMinima,
	Inv_Bod.NomBodega,
	Agr1.NomAgrup1,Agr2.NomAgrup2,Inv.CodInventario + ' ' + Inv.NomInventario AS NomInvCompleto,Inv_Uni.NomUnidad,Inv_Uni.IdTipoUnidad,
	ISNULL(Inv.FactorEscala,Inv_Uni.FactorEscala)AS FactorEscala,
	ISNULL(Inv.FactorEscala1,ISNULL(Inv_Uni1.FactorEscala,0))AS FactorEscala1,
	ISNULL(EncMov.PuntoReorden,0) PuntoReorden,
	CAST(CAST(inv.EsActivo AS int)AS bit) EsActivo,
	NumSerial,
	motor.NomAutomotor,motor.NumPlaca +'-'+marca.NomMarca AS Automotor,
	(talla.NomTalla) AS NomTalla,
	(talla.Agrup1) AS AgrupTalla1,
	(talla.Agrup2) AS AgrupTalla2,
	(talla.Agrup3) AS AgrupTalla3,
	Existencia + ExistenciaTeo as TotalExistencia,
	CAST(ISNULL(EncMov.EsOk,0)AS BIT) EsOk,
	CAST(ISNULL(EncMov.EsError,0) AS BIT)  EsError,
	CAST(ISNULL(EncMov.EsWarning,0) AS BIT) EsWarning,

	EncMov.TimeIni,
	EncMov.TimeFinal ,
	EncMov.TimeReminder,
	IdMaximo ,EncMov.IdBodega,IdUsuarios,IdRoles
			
	FROM  
	#tmpTotalFin as EncMov INNER JOIN
	dbo.Inventarios AS Inv ON EncMov.IdInventario = Inv.IdInventario LEFT OUTER JOIN
	dbo.Inventarios_Agrup1 AS Agr1 ON Agr1.IdAgrup1=Inv.IdAgrup1 LEFT OUTER JOIN
	dbo.Inventarios_Agrup2 AS Agr2 ON Agr2.IdAgrup2=Inv.IdAgrup2 LEFT OUTER JOIN
	dbo.Inventarios_Lotes AS Inv_L ON EncMov.IdLote = Inv_L.IdLote  LEFT OUTER JOIN
	dbo.Inventarios_Bodegas AS Inv_Bod ON EncMov.IdBodega = Inv_Bod.IdBodega LEFT OUTER JOIN
	dbo.Inventarios_Unidades AS Inv_Uni ON Inv.IdUnidad = Inv_Uni.IdUnidad LEFT OUTER JOIN
	dbo.Inventarios_Unidades AS Inv_Uni1 ON Inv.IdUnidad1 = Inv_Uni1.IdUnidad LEFT JOIN
	[dbo].[Reparacion_Automotores] AS motor  ON EncMov.NumSerial = motor.IdAutomotor and EncMov.IdTipoReg=1 LEFT JOIN
	dbo.Inventarios_Colores_Tallas AS Talla ON EncMov.NumSerial = Talla.CodTalla and EncMov.IdTipoReg=2 LEFT JOIN
	[dbo].[Reparacion_Marcas] AS marca ON marca.IdMarca=motor.IdMarca
	WHERE
	Inv.IdTipoInv=1  AND (@EsSinExist=1 or (Existencia<>0 or ExistenciaTeo<>0))
)
q1 LEFT JOIN CostoPromedioPorInventario(@FechaCorte) AS costPromInv ON costPromInv.IdInventario=q1.IdInventario


OPTION(RECOMPILE); 

END
GO
PRINT N'Altering Procedure [dbo].[GetKardexInventario]...';
IF OBJECT_ID(N'dbo.GetKardexInventario', N'P') IS NULL
    EXEC(N'CREATE PROCEDURE [dbo].[GetKardexInventario] AS BEGIN SET NOCOUNT ON; RETURN 0; END');

GO
ALTER PROCEDURE [dbo].[GetKardexInventario]
@FechaIni NVARCHAR(10) = '',
@FechaFin NVARCHAR(10) = '',
@IdInventario INT = null,
@IdEmpresas NVARCHAR(1000) = '',
@Lote bit=0,
@IdBodega INT = null

AS
BEGIN

SET NOCOUNT ON;	

DECLARE

@FechaIniT DATETIME,
@FechaFinT DATETIME, 
@FechaIniT2 DATETIME


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SET @FechaIniT = CAST(CONVERT(DATETIME, @FechaIni, 103) + ' 00:00:00 ' AS DATETIME)
SET @FechaFinT = CAST(CONVERT(DATETIME, @FechaFin, 103) + ' 23:59:59 ' AS DATETIME)
SET @FechaIniT2 = DATEADD(DAY,-1,@FechaIniT)



IF @Lote=1
BEGIN

;with tmpSaldosIni (SenSaldo,IdInventario,NumSerial,IdTipoReg,IdBodega,IdLote,Fecha,IdEncabMov,IdEncab,IdDocumento,CodDocumento,NumDocumento,IdPrefijo,CantConFactor,CantConFactorMov,Cant1ConFactorMov,
			Cantidad,IdUnidadMov,IdCliente,FactorInventario,IdEmpresa,CostoProm, ValorTRM, CostoPromExt,IdCliente1_IdDir,ValorPuntos)
			as(
			--Saldos
			SELECT
			0 AS SenSaldo,
			Enc_Mov.IdInventario,
			NULL AS NumSerial,
			NULL AS IdTipoReg, 
			@IdBodega AS IdBodega,
			0 IdLote,
			@FechaIniT2 AS Fecha,
			0 AS IdEncabMov,
			0 AS IdEncab,  
			0 AS IdDocumento, 
			'' AS CodDocumento, 
			0 AS NumDocumento,		
			'' AS IdPrefijo, 
			SUM(Enc_Mov.Cantidad * Enc_Mov.FactorCantidad * Doc.FactorInventario) AS CantConFactor, 
			SUM(Enc_Mov.Cantidad * Enc_Mov.FactorCantidad * Doc.FactorInventario * Enc_Mov.FactorMovim) AS CantConFactorMov,
			SUM(coalesce(Enc_Mov.Cantidad1,0)* Doc.FactorInventario * Enc_Mov.FactorMovim) AS Cant1ConFactorMov,
			SUM(Enc_Mov.Cantidad) AS Cantidad, 
			null AS IdUnidadMov,
			null AS IdCliente,
			CASE WHEN SUM(Enc_Mov.Cantidad * Enc_Mov.FactorCantidad * Doc.FactorInventario * Enc_Mov.FactorMovim) >= 0 THEN 1 ELSE -1 END AS FactorInventario,
			null AS IdEmpresa,
			avg(EncMov_Costo.CostoProm) as CostoProm,	
			MAX(Enc_Mov.ValorTRM) AS ValorTRM,
			CASE WHEN MAX(Enc_Mov.ValorTRM) = 0 THEN 0 ELSE avg(EncMov_Costo.CostoProm) / MAX(Enc_Mov.ValorTRM) END AS CostoPromExt,
			null as IdCliente1_IdDir,
			0 AS ValorPuntos
			FROM 		dbo.Encabezados_Mov AS Enc_Mov 
			INNER JOIN	dbo.Encabezados AS Enc ON Enc_Mov.IdEncab = Enc.IdEncab 
			INNER JOIN	dbo.Documentos AS Doc ON Enc.IdDocumento = Doc.IdDocumento 
			INNER JOIN  (select number from iter$simple_intlist_to_tbl(@IdEmpresas)) T1 on T1.number=Enc.IdEmpresa
			LEFT JOIN   dbo.Encabezados_Mov_CostoProm AS EncMov_Costo ON Enc_Mov.IdEncabMov = EncMov_Costo.IdEncabMov

			WHERE
			Doc.FactorInventario <> 0 AND 
			Enc_Mov.FactorMovim <> 0 AND
			Enc.Fecha < @FechaIniT AND
			Enc.EsAnulado=0 
			AND
			 (@IdBodega is null OR Enc_Mov.IdBodega = @IdBodega)
			GROUP BY Enc_Mov.IdInventario, Enc_Mov.IdLote
		
		),

		tmpUnion (SenSaldo,IdInventario,NumSerial,IdTipoReg,IdBodega,IdLote,Fecha,IdEncabMov,IdEncab,IdDocumento,CodDocumento,NumDocumento,IdPrefijo,CantConFactor,CantConFactorMov,Cant1ConFactorMov,
			Cantidad,IdUnidadMov,IdCliente,FactorInventario,IdEmpresa,CostoProm, ValorTRM, CostoPromExt,IdCliente1_IdDir,ValorPuntos)
			as
			(
			   select * from tmpSaldosIni

			   union ALL 

				SELECT 
					1 AS SenSaldo,
					Enc_Mov.IdInventario,
					Enc_Mov.NumSerial, 
					Enc_Mov.IdTipoReg, 
					Enc_Mov.IdBodega, 
					Enc_Mov.IdLote, 
					Enc.Fecha, 
					Enc_Mov.IdEncabMov, 
					Enc_Mov.IdEncab,
					Enc.IdDocumento, 
					Doc.CodDocumento, 
					Enc.NumDocumento, 
					Enc.IdPrefijo, 	
					Enc_Mov.Cantidad * Enc_Mov.FactorCantidad AS CantConFactor, 
					Enc_Mov.Cantidad * Enc_Mov.FactorCantidad * Doc.FactorInventario * Enc_Mov.FactorMovim AS CantConFactorMov, 
					coalesce(Enc_Mov.Cantidad1,0) * Doc.FactorInventario * Enc_Mov.FactorMovim AS Cant1ConFactorMov,
					Enc_Mov.Cantidad,
					Enc_Mov.IdUnidad AS IdUnidadMov,
					Enc.IdCliente1 AS IdCliente,
					Enc_Mov.FactorMovim * Doc.FactorInventario as FactorInventario,
					Enc.IdEmpresa,
					EncMov_Costo.CostoProm,
					Enc_Mov.ValorTRM AS ValorTRM,
					CASE WHEN Enc_Mov.ValorTRM = 0 THEN 0 ELSE EncMov_Costo.CostoProm/ Enc_Mov.ValorTRM END AS CostoPromExt,
					enc.IdCliente1_IdDir,Enc_Mov.ValorPuntos
					FROM 		dbo.Encabezados_Mov AS Enc_Mov 
					INNER JOIN	dbo.Encabezados AS Enc ON Enc_Mov.IdEncab = Enc.IdEncab 
					INNER JOIN  dbo.Documentos AS Doc ON Enc.IdDocumento = Doc.IdDocumento 
					INNER JOIN  (select number from iter$simple_intlist_to_tbl(@IdEmpresas)) T1 on T1.number=Enc.IdEmpresa
					LEFT JOIN	dbo.Encabezados_Mov_CostoProm AS EncMov_Costo ON Enc_Mov.IdEncabMov = EncMov_Costo.IdEncabMov	
					WHERE     
					Doc.FactorInventario <> 0 AND 
					Enc_Mov.FactorMovim <> 0 AND
					Enc.Fecha >= @FechaIniT AND Enc.Fecha <= @FechaFinT AND 
					Enc.EsAnulado=0 AND
					Enc_Mov.FactorMovim <> 0 
					AND
					 (@IdBodega is null OR Enc_Mov.IdBodega = @IdBodega)
		 ),
		 	 tmpAgrup (SenSaldo,IdInventario,NumSerial,IdTipoReg,IdBodega,IdLote,Fecha,IdEncabMov,IdEncab,IdDocumento,CodDocumento,NumDocumento,IdPrefijo,CantConFactor,CantConFactorMov,Cant1ConFactorMov,
	Cantidad,IdUnidadMov,IdCliente,FactorInventario,IdEmpresa,CostoProm, ValorTRM, CostoPromExt,IdCliente1_IdDir,ValorPuntos,CantEntrada,CantSalida,CantEntradaUni1,CantSalidaUni1,CantSaldo,CantSaldo1,RowCol)
	as
	(
		select q2.*,CantEntrada-CantSalida as CantSaldo,CanEntradaUni1-CantSalidaUni1 as CantSaldo1, ROW_NUMBER() OVER(PARTITION BY IdInventario,IdLote order by IdInventario,SenSaldo,Cast(Fecha as date),FactorInventario desc,IdEncabMov) RowCol
		from
		(
		   select q1.*,
		   CASE WHEN q1.FactorInventario = 1 THEN q1.CantConFactorMov ELSE 0 END CantEntrada,
		   CASE WHEN q1.FactorInventario =-1 THEN q1.CantConFactorMov *-1 ELSE 0 END CantSalida,
		   CASE WHEN q1.FactorInventario = 1 THEN q1.Cant1ConFactorMov  ELSE 0 END CanEntradaUni1,
		   CASE WHEN q1.FactorInventario =-1 THEN q1.Cant1ConFactorMov*-1 ELSE 0 END CantSalidaUni1
		   from tmpUnion as q1
		 ) as q2
	)
	,

		 tmpAgrupFin (SenSaldo,IdInventario,NumSerial,IdTipoReg,IdBodega,IdLote,Fecha,IdEncabMov,IdEncab,IdDocumento,CodDocumento,NumDocumento,IdPrefijo,CantConFactor,CantConFactorMov,Cant1ConFactorMov,
			Cantidad,IdUnidadMov,IdCliente,FactorInventario,IdEmpresa,CostoProm, ValorTRM, CostoPromExt,IdCliente1_IdDir,ValorPuntos,CantEntrada,CantSalida,CantEntradaUni1,CantSalidaUni1,CantSaldo,CantSaldo1)
			as
			(

			 --Query >= 2012 
				select SenSaldo,IdInventario,NumSerial,IdTipoReg,IdBodega,IdLote,Fecha,IdEncabMov,IdEncab,IdDocumento,CodDocumento,NumDocumento,IdPrefijo,CantConFactor,CantConFactorMov,Cant1ConFactorMov,
				Cantidad,IdUnidadMov,IdCliente,FactorInventario,IdEmpresa,CostoProm, ValorTRM, CostoPromExt,IdCliente1_IdDir,ValorPuntos,CantEntrada,CantSalida,CantEntradaUni1,CantSalidaUni1, 
				SUM(CantSaldo) OVER (PARTITION BY IdInventario order by IdInventario,SenSaldo,Cast(Fecha as DATE),FactorInventario desc,IdEncabMov  ROWS UNBOUNDED PRECEDING) AS CantSaldo,
				SUM(CantSaldo1) OVER (PARTITION BY IdInventario order by IdInventario,SenSaldo,Cast(Fecha as DATE),FactorInventario desc,IdEncabMov  ROWS UNBOUNDED PRECEDING) AS CantSaldo1
			    from tmpAgrup as q1

			 -- Query 2008
			 --   select SenSaldo,qFin.IdInventario,NumSerial,IdTipoReg,IdBodega,IdLote,Fecha,IdEncabMov,IdEncab,IdDocumento,CodDocumento,NumDocumento,IdPrefijo,CantConFactor,CantConFactorMov,Cant1ConFactorMov,
				--Cantidad,IdUnidadMov,IdCliente,FactorInventario,IdEmpresa,CostoProm, ValorTRM, CostoPromExt,IdCliente1_IdDir,ValorPuntos,CantEntrada,CantSalida,CantEntradaUni1,CantSalidaUni1,
				--SaldoFin,SaldoFin1
				--	from
				--	(	
				--	select q2.IdInventario,q2.RowCol,sum(q1.CantSaldo)as SaldoFin,sum(q1.CantSaldo1)as SaldoFin1
				--	from tmpAgrup as q1 inner join tmpAgrup as q2 on q1.IdInventario=q2.IdInventario and q1.RowCol <= q2.RowCol
				--	group by  q2.IdInventario,q2.RowCol
				--	)as qAgrup inner join tmpAgrup as qFin on qFin.IdInventario=qAgrup.IdInventario and qFin.RowCol=qAgrup.RowCol
			),

			tmpMaximos(IdInventario,CantMaxima,CantMinima,PuntoReorden)
			as
			(
			  select IdInventario,max(CantMaxima),min(CantMinima),max(PuntoReorden)
			  from [dbo].[Inventarios_MaximosYMinimos]
			  WHERE
			  EsPrincipal=1 and(
				@IdInventario is null OR 
				IdInventario=@IdInventario) 
				group by IdInventario
			)
	
		SELECT
		maxi.CantMaxima,
		maxi.CantMinima,
		Inv.CodInventario, 
		Inv.NomInventario, 
		Inv.CodInventario + ' ' + Inv.NomInventario AS NomInvCompleto, 
		Inv_Bod.NomBodega, 
		Inv_Lot.CodLote, 
		Inv_Lot.NomLote,
		Inv_agrup1.CodigoAgrup1,
		Inv_agrup1.NomAgrup1,
		Inv_agrup2.CodigoAgrup2,
		Inv_agrup2.NomAgrup2,
		InvUnid.NomUnidad AS NomUnidadInv,
		InvUnid.Abreviatura AS Abreviatura, 
		InvUnidMov.NomUnidad AS NomUnidadMov, 
		Cli.Nombres +' ' + Cli.Apellidos AS NomCompleto,
		Emp.EmpresaNombre, 
		Emp.EmpresaCodigo,
		InvUnid.IdTipoUnidad,
		coalesce(Inv.FactorEscala,InvUnid.FactorEscala)as FactorEscala,
		q1.*,
		CostoProm * CantSaldo as CostoTotal,
		(CantEntrada + CantSalida) * CostoProm as CostoSum,
		coalesce(motor.NomAutomotor,(motor.NumPlaca +'-'+marca.NomMarca)) as Automotor,
		talla.NomTalla,
		talla.Agrup1 as AgrupTalla1,
		talla.Agrup2 as AgrupTalla2,
		talla.Agrup3 as AgrupTalla3,
	    Dir.Direccion,
        Identificacion,
        CodigoInterno,
		NomCiudad

		FROM
		tmpAgrupFin as q1
		INNER JOIN
		dbo.Inventarios AS Inv ON q1.IdInventario = Inv.IdInventario 
		and Inv.IdTipoInv=1 
		INNER JOIN
		dbo.Inventarios_Unidades AS InvUnid ON Inv.IdUnidad = InvUnid.IdUnidad 
		LEFT JOIN
		dbo.Empresas AS Emp ON q1.IdEmpresa = Emp.IdEmpresa 
		LEFT JOIN
		dbo.Inventarios_Bodegas AS Inv_Bod ON q1.IdBodega = Inv_Bod.IdBodega 
		LEFT OUTER JOIN
		dbo.Inventarios_Agrup1 AS Inv_agrup1 ON Inv.IdAgrup1 = Inv_agrup1.IdAgrup1 LEFT OUTER JOIN 
		dbo.Inventarios_Agrup2 AS Inv_agrup2 ON Inv.IdAgrup2 = Inv_agrup2.IdAgrup2 
		LEFT JOIN
		dbo.VClientes2 AS Cli ON q1.IdCliente = Cli.IdCliente   
		LEFT JOIN
		dbo.Inventarios_Unidades AS InvUnidMov ON q1.IdUnidadMov = InvUnidMov.IdUnidad 
		LEFT OUTER JOIN
		dbo.Inventarios_Lotes AS Inv_Lot ON q1.IdLote = Inv_Lot.IdLote  
		LEFT OUTER JOIN
		dbo.Inventarios_Unidades AS InvUnid1 ON Inv.IdUnidad1 = InvUnid1.IdUnidad
		LEFT JOIN
		[dbo].[Reparacion_Automotores] AS motor  ON q1.NumSerial = motor.IdAutomotor and q1.IdTipoReg=1
		LEFT JOIN
		dbo.Inventarios_Colores_Tallas AS Talla ON q1.NumSerial = Talla.CodTalla and q1.IdTipoReg=2
		LEFT JOIN
		[dbo].[Reparacion_Marcas] AS marca ON marca.IdMarca=motor.IdMarca
		LEFT JOIN
		dbo.Clientes_Direcciones AS Dir ON Dir.IdClienteDir=IdCliente1_IdDir
		LEFT JOIN
		dbo.Ciudades AS ciud ON ciud.idciudad=Dir.idCiudad
		LEFT JOIN
		tmpMaximos AS maxi ON maxi.IdInventario=q1.IdInventario	

		WHERE
		@IdInventario is null OR 
		q1.IdInventario=@IdInventario

		OPTION(RECOMPILE); 
END

ELSE
BEGIN
		;with tmpSaldosIni (SenSaldo,IdInventario,NumSerial,IdTipoReg,IdBodega,IdLote,Fecha,IdEncabMov,IdEncab,IdDocumento,CodDocumento,NumDocumento,IdPrefijo,CantConFactor,CantConFactorMov,Cant1ConFactorMov,
			Cantidad,IdUnidadMov,IdCliente,FactorInventario,IdEmpresa,CostoProm, ValorTRM, CostoPromExt,IdCliente1_IdDir,ValorPuntos)
			as(
			--Saldos
			SELECT
			0 AS SenSaldo,
			Enc_Mov.IdInventario,
			NULL AS NumSerial,
			NULL AS IdTipoReg, 
			@IdBodega AS IdBodega,
			0 IdLote,
			@FechaIniT2 AS Fecha,
			0 AS IdEncabMov,
			0 AS IdEncab,  
			0 AS IdDocumento, 
			'' AS CodDocumento, 
			0 AS NumDocumento,		
			'' AS IdPrefijo, 
			SUM(Enc_Mov.Cantidad * Enc_Mov.FactorCantidad * Doc.FactorInventario) AS CantConFactor, 
			SUM(Enc_Mov.Cantidad * Enc_Mov.FactorCantidad * Doc.FactorInventario * Enc_Mov.FactorMovim) AS CantConFactorMov,
			SUM(coalesce(Enc_Mov.Cantidad1,0)* Doc.FactorInventario * Enc_Mov.FactorMovim) AS Cant1ConFactorMov,
			SUM(Enc_Mov.Cantidad) AS Cantidad, 
			null AS IdUnidadMov,
			null AS IdCliente,
			CASE WHEN SUM(Enc_Mov.Cantidad * Enc_Mov.FactorCantidad * Doc.FactorInventario * Enc_Mov.FactorMovim) >= 0 THEN 1 ELSE -1 END AS FactorInventario,
			null AS IdEmpresa,
			avg(EncMov_Costo.CostoProm) as CostoProm,	
			MAX(Enc_Mov.ValorTRM) AS ValorTRM,
			CASE WHEN MAX(Enc_Mov.ValorTRM) = 0 THEN 0 ELSE avg(EncMov_Costo.CostoProm) / MAX(Enc_Mov.ValorTRM) END AS CostoPromExt,
			null as IdCliente1_IdDir,
			0 AS ValorPuntos
			FROM        dbo.Encabezados_Mov AS Enc_Mov 
			INNER JOIN	dbo.Encabezados AS Enc ON Enc_Mov.IdEncab = Enc.IdEncab 
			INNER JOIN	dbo.Documentos AS Doc ON Enc.IdDocumento = Doc.IdDocumento 
			INNER JOIN  (select number from iter$simple_intlist_to_tbl(@IdEmpresas)) T1 on T1.number=Enc.IdEmpresa
			LEFT JOIN   dbo.Encabezados_Mov_CostoProm AS EncMov_Costo ON Enc_Mov.IdEncabMov = EncMov_Costo.IdEncabMov
			WHERE
			Doc.FactorInventario <> 0 AND 
			Enc_Mov.FactorMovim <> 0 AND
			Enc.Fecha < @FechaIniT AND
			Enc.EsAnulado=0 
			AND
			 (@IdBodega is null OR Enc_Mov.IdBodega = @IdBodega)
			GROUP BY Enc_Mov.IdInventario
		),

		tmpUnion (SenSaldo,IdInventario,NumSerial,IdTipoReg,IdBodega,IdLote,Fecha,IdEncabMov,IdEncab,IdDocumento,CodDocumento,NumDocumento,IdPrefijo,CantConFactor,CantConFactorMov,Cant1ConFactorMov,
			Cantidad,IdUnidadMov,IdCliente,FactorInventario,IdEmpresa,CostoProm, ValorTRM, CostoPromExt,IdCliente1_IdDir,ValorPuntos)
			as
			(
			   select * from tmpSaldosIni

			   union ALL 

				SELECT 
					1 AS SenSaldo,
					Enc_Mov.IdInventario,
					Enc_Mov.NumSerial, 
					Enc_Mov.IdTipoReg, 
					Enc_Mov.IdBodega, 
					Enc_Mov.IdLote, 
					Enc.Fecha, 
					Enc_Mov.IdEncabMov, 
					Enc_Mov.IdEncab,
					Enc.IdDocumento, 
					Doc.CodDocumento, 
					Enc.NumDocumento, 
					Enc.IdPrefijo, 	
					Enc_Mov.Cantidad * Enc_Mov.FactorCantidad AS CantConFactor, 
					Enc_Mov.Cantidad * Enc_Mov.FactorCantidad * Doc.FactorInventario * Enc_Mov.FactorMovim AS CantConFactorMov, 
					coalesce(Enc_Mov.Cantidad1,0) * Doc.FactorInventario * Enc_Mov.FactorMovim AS Cant1ConFactorMov,
					Enc_Mov.Cantidad,
					Enc_Mov.IdUnidad AS IdUnidadMov,
					Enc.IdCliente1 AS IdCliente,
					Enc_Mov.FactorMovim * Doc.FactorInventario as FactorInventario,
					Enc.IdEmpresa,
					EncMov_Costo.CostoProm,
					Enc_Mov.ValorTRM AS ValorTRM,
					CASE WHEN Enc_Mov.ValorTRM = 0 THEN 0 ELSE EncMov_Costo.CostoProm/ Enc_Mov.ValorTRM END AS CostoPromExt,
					enc.IdCliente1_IdDir,Enc_Mov.ValorPuntos
					FROM 	    dbo.Encabezados_Mov AS Enc_Mov 
					INNER JOIN	dbo.Encabezados AS Enc ON Enc_Mov.IdEncab = Enc.IdEncab 
					INNER JOIN	dbo.Documentos AS Doc ON Enc.IdDocumento = Doc.IdDocumento 
					INNER JOIN  (select number from iter$simple_intlist_to_tbl(@IdEmpresas)) T1 on T1.number=Enc.IdEmpresa
					LEFT JOIN	dbo.Encabezados_Mov_CostoProm AS EncMov_Costo ON Enc_Mov.IdEncabMov = EncMov_Costo.IdEncabMov	
					WHERE     
					Doc.FactorInventario <> 0 AND 
					Enc_Mov.FactorMovim <> 0 AND
					Enc.Fecha >= @FechaIniT AND Enc.Fecha <= @FechaFinT AND 
					Enc.EsAnulado=0 AND
					Enc_Mov.FactorMovim <> 0 
					AND
					 (@IdBodega is null OR Enc_Mov.IdBodega = @IdBodega)
		 ),
		 tmpAgrup (SenSaldo,IdInventario,NumSerial,IdTipoReg,IdBodega,IdLote,Fecha,IdEncabMov,IdEncab,IdDocumento,CodDocumento,NumDocumento,IdPrefijo,CantConFactor,CantConFactorMov,Cant1ConFactorMov,
	Cantidad,IdUnidadMov,IdCliente,FactorInventario,IdEmpresa,CostoProm, ValorTRM, CostoPromExt,IdCliente1_IdDir,ValorPuntos,CantEntrada,CantSalida,CantEntradaUni1,CantSalidaUni1,CantSaldo,CantSaldo1,RowCol)
	as
	(
		select q2.*,CantEntrada-CantSalida as CantSaldo,CanEntradaUni1-CantSalidaUni1 as CantSaldo1,ROW_NUMBER() OVER(PARTITION BY IdInventario order by IdInventario,SenSaldo,Cast(Fecha as date),FactorInventario desc,IdEncabMov) RowCol
		from
		(
		   select q1.*,
		   CASE WHEN q1.FactorInventario = 1 THEN q1.CantConFactorMov ELSE 0 END CantEntrada,
		   CASE WHEN q1.FactorInventario =-1 THEN q1.CantConFactorMov *-1 ELSE 0 END CantSalida,
		   CASE WHEN q1.FactorInventario = 1 THEN q1.Cant1ConFactorMov  ELSE 0 END CanEntradaUni1,
		   CASE WHEN q1.FactorInventario =-1 THEN q1.Cant1ConFactorMov*-1 ELSE 0 END CantSalidaUni1
		   from tmpUnion as q1
		 ) as q2
	)
	,

		  tmpAgrupFin (SenSaldo,IdInventario,NumSerial,IdTipoReg,IdBodega,IdLote,Fecha,IdEncabMov,IdEncab,IdDocumento,CodDocumento,NumDocumento,IdPrefijo,CantConFactor,CantConFactorMov,Cant1ConFactorMov,
		Cantidad,IdUnidadMov,IdCliente,FactorInventario,IdEmpresa,CostoProm, ValorTRM, CostoPromExt,IdCliente1_IdDir,ValorPuntos,CantEntrada,CantSalida,CantEntradaUni1,CantSalidaUni1,CantSaldo,CantSaldo1)
			as
			(
				 ----Query >= 2012 
				select SenSaldo,IdInventario,NumSerial,IdTipoReg,IdBodega,IdLote,Fecha,IdEncabMov,IdEncab,IdDocumento,CodDocumento,NumDocumento,IdPrefijo,CantConFactor,CantConFactorMov,Cant1ConFactorMov,
				Cantidad,IdUnidadMov,IdCliente,FactorInventario,IdEmpresa,CostoProm, ValorTRM, CostoPromExt,IdCliente1_IdDir,ValorPuntos,CantEntrada,CantSalida,CantEntradaUni1,CantSalidaUni1, 
				SUM(CantSaldo) OVER (PARTITION BY IdInventario order by IdInventario,SenSaldo,Cast(Fecha as DATE),FactorInventario desc,IdEncabMov  ROWS UNBOUNDED PRECEDING) AS CantSaldo,
				SUM(CantSaldo1) OVER (PARTITION BY IdInventario order by IdInventario,SenSaldo,Cast(Fecha as DATE),FactorInventario desc,IdEncabMov  ROWS UNBOUNDED PRECEDING) AS CantSaldo1
			    from tmpAgrup as q1

			 -- -- Query 2008
			 --   select SenSaldo,qFin.IdInventario,NumSerial,IdTipoReg,IdBodega,IdLote,Fecha,IdEncabMov,IdEncab,IdDocumento,CodDocumento,NumDocumento,IdPrefijo,CantConFactor,CantConFactorMov,Cant1ConFactorMov,
				--Cantidad,IdUnidadMov,IdCliente,FactorInventario,IdEmpresa,CostoProm, ValorTRM, CostoPromExt,IdCliente1_IdDir,ValorPuntos,CantEntrada,CantSalida,CantEntradaUni1,CantSalidaUni1,
				--SaldoFin,SaldoFin1
				--	from
				--	(	
				--	select q2.IdInventario,q2.RowCol,sum(q1.CantSaldo)as SaldoFin,sum(q1.CantSaldo1)as SaldoFin1
				--	from tmpAgrup as q1 inner join tmpAgrup as q2 on q1.IdInventario=q2.IdInventario and q1.RowCol <= q2.RowCol
				--	group by  q2.IdInventario,q2.RowCol
				--	)as qAgrup inner join tmpAgrup as qFin on qFin.IdInventario=qAgrup.IdInventario and qFin.RowCol=qAgrup.RowCol
			)

				,

			tmpMaximos(IdInventario,CantMaxima,CantMinima,PuntoReorden)
			as
			(
			  select IdInventario,max(CantMaxima),min(CantMinima),max(PuntoReorden)
			  from [dbo].[Inventarios_MaximosYMinimos]
			  WHERE
			  EsPrincipal=1 and(
				@IdInventario is null OR 
				IdInventario=@IdInventario) 
				group by IdInventario
			)
	
	
		SELECT
		maxi.CantMaxima,
		maxi.CantMinima,
		Inv.CodInventario, 
		Inv.NomInventario, 
		Inv.CodInventario + ' ' + Inv.NomInventario AS NomInvCompleto, 
		Inv_Bod.NomBodega, 
		Inv_Lot.CodLote, 
		Inv_Lot.NomLote,
		Inv_agrup1.CodigoAgrup1,
		Inv_agrup1.NomAgrup1,
		Inv_agrup2.CodigoAgrup2,
		Inv_agrup2.NomAgrup2,
		InvUnid.NomUnidad AS NomUnidadInv, 
		InvUnid.Abreviatura AS Abreviatura,
		InvUnidMov.NomUnidad AS NomUnidadMov, 
		Cli.Nombres + ' ' + Cli.Apellidos AS NomCompleto,
		Emp.EmpresaNombre, 
		Emp.EmpresaCodigo,
		InvUnid.IdTipoUnidad,
		coalesce(Inv.FactorEscala,InvUnid.FactorEscala)as FactorEscala,
		q1.*,
		CostoProm * CantSaldo as CostoTotal,
		(CantEntrada + CantSalida) * CostoProm as CostoSum,
		coalesce(motor.NomAutomotor,(motor.NumPlaca +'-'+marca.NomMarca)) as Automotor,
		talla.NomTalla,
		talla.Agrup1 as AgrupTalla1,
		talla.Agrup2 as AgrupTalla2,
		talla.Agrup3 as AgrupTalla3,
		Dir.Direccion,
        Identificacion,
        CodigoInterno,
		NomCiudad

		FROM
		tmpAgrupFin as q1
		INNER JOIN
		dbo.Inventarios AS Inv ON q1.IdInventario = Inv.IdInventario 
		and Inv.IdTipoInv=1 
		INNER JOIN
		dbo.Inventarios_Unidades AS InvUnid ON Inv.IdUnidad = InvUnid.IdUnidad 
		LEFT JOIN
		dbo.Empresas AS Emp ON q1.IdEmpresa = Emp.IdEmpresa 
		LEFT JOIN
		dbo.Inventarios_Bodegas AS Inv_Bod ON q1.IdBodega = Inv_Bod.IdBodega 
		LEFT OUTER JOIN
		dbo.Inventarios_Agrup1 AS Inv_agrup1 ON Inv.IdAgrup1 = Inv_agrup1.IdAgrup1 LEFT OUTER JOIN 
		dbo.Inventarios_Agrup2 AS Inv_agrup2 ON Inv.IdAgrup2 = Inv_agrup2.IdAgrup2 
		LEFT JOIN
		dbo.VClientes2 AS Cli ON q1.IdCliente = Cli.IdCliente   
		LEFT JOIN
		dbo.Inventarios_Unidades AS InvUnidMov ON q1.IdUnidadMov = InvUnidMov.IdUnidad 
		LEFT OUTER JOIN
		dbo.Inventarios_Lotes AS Inv_Lot ON q1.IdLote = Inv_Lot.IdLote  
		LEFT OUTER JOIN
		dbo.Inventarios_Unidades AS InvUnid1 ON Inv.IdUnidad1 = InvUnid1.IdUnidad
		LEFT JOIN
		[dbo].[Reparacion_Automotores] AS motor  ON q1.NumSerial = motor.IdAutomotor and q1.IdTipoReg=1
		LEFT JOIN
		dbo.Inventarios_Colores_Tallas AS Talla ON q1.NumSerial = Talla.CodTalla and q1.IdTipoReg=2
		LEFT JOIN
		[dbo].[Reparacion_Marcas] AS marca ON marca.IdMarca=motor.IdMarca
		LEFT JOIN
		dbo.Clientes_Direcciones AS Dir ON Dir.IdClienteDir=IdCliente1_IdDir
		LEFT JOIN
		dbo.Ciudades AS ciud ON ciud.idciudad=Dir.idCiudad
		LEFT JOIN
		tmpMaximos AS maxi ON maxi.IdInventario=q1.IdInventario


		WHERE
		@IdInventario is null OR 
		q1.IdInventario=@IdInventario

		OPTION(RECOMPILE); 
		END

	END
GO
PRINT N'Altering Procedure [dbo].[GetLibroAuxiliar]...';
IF OBJECT_ID(N'dbo.GetLibroAuxiliar', N'P') IS NULL
    EXEC(N'CREATE PROCEDURE [dbo].[GetLibroAuxiliar] AS BEGIN SET NOCOUNT ON; RETURN 0; END');

GO
ALTER PROCEDURE [dbo].[GetLibroAuxiliar]
	@FechaIni NVARCHAR(10) = '',
	@FechaFin NVARCHAR(10) = '',
	@CuentaIni NVARCHAR(100)='',
	@CuentaFin NVARCHAR(100)='',
    @IdEmpresas NVARCHAR(1000) = '',
    @IdCliente INT = NULL,
    @SenAgrupCC BIT = 0,
    @IdCentroCosto INT = NULL,
    @EsNotGAAP BIT = NULL,
    @EsNotIFRS BIT = NULL,
	@EsAgrupIFRS BIT = NULL,
	@IdSucursal INT = NULL	

    
AS
BEGIN

DECLARE

@FechaIniT DATETIME,
@FechaFinT DATETIME,
@FechaIniT2 DATETIME,

@IdTipoPUC INT

SET NOCOUNT ON;	

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SET @FechaIniT = CAST(CONVERT(DATETIME, @FechaIni, 103) + ' 00:00:00' AS DATETIME)
SET @FechaFinT = CAST(CONVERT(DATETIME, @FechaFin, 103) + ' 23:59:59' AS DATETIME)
SET @FechaIniT2 = DATEADD(DAY,-1,@FechaIniT)


SELECT @IdTipoPUC = ValParametro FROM PARAMETROS WHERE CodParametro = 1
IF @IdTipoPUC IS NULL
	SET @IdTipoPUC = 1

;WITH tempCuentas(CodCuenta,NomCuentaBase)
		as(
			select CodCuenta,
			MAX(NomCuenta)NomCuenta
			from
			(
			select CodCuenta,NomCuenta
			from cuentas
			where idtipoPuc=@IdTipoPUC
			)as q1
			group by CodCuenta
		)

SELECT
0 AS Orden,
YEAR(@FechaIniT2) AS Anno,
MONTH(@FechaIniT2) AS Mess,
CASE WHEN @EsAgrupIFRS=1 and len(CodCuentaIFRS)>0 then coalesce(CodCuentaIFRS,VSaldo1.CodCuenta) else VSaldo1.CodCuenta END AS CodCuenta,
CASE WHEN @EsAgrupIFRS=1 then VSaldo1.CodCuenta else CodCuentaIFRS END AS CodCuenta2,
Cta.NomCuenta,
CASE WHEN @EsAgrupIFRS=1 and len(CodCuentaIFRS)>0 then coalesce(cta.CodCuentaIFRS,VSaldo1.CodCuenta) + '-' + coalesce(cta.NomCuentaIFRS,Cta.NomCuenta)  else VSaldo1.CodCuenta + '-' + Cta.NomCuenta END AS DescripCuenta,
VSaldo1.IdCliente,
LTRIM(RTRIM(Cli.Apellidos + ' ' + Cli.Nombres)) AS NomTercero,
'SALDO INICIAL' AS MesLetras,
@FechaIniT2 AS Fecha,
VSaldo1.Debito AS Debito,
VSaldo1.Credito AS Credito,
VSaldo1.DebitoExt AS DebitoExt,
VSaldo1.CreditoExt AS CreditoExt,
'SALDO INICIAL' AS Nota,
'' AS DocRef, 
'' AS Doc,
'' AS Doc_FC, 
0 AS IdEncabCont,
Cta_C.Factor,
VSaldo1.IdEmpresa,
'' AS CodDocumento,
Cta.IdCuentaTipo,
NULL AS IdInventario,
0 AS IdAbonaDoc,
NULL AS IdAbonaDif,
Emp.EmpresaCodigo,
Cta_T.IdClase,
VSaldo1.IdCentroCosto,
CC.NomCentroCosto,
0 AS IdDocumento,
0 AS IdEncab,
null as RetencionBase,
null as RetencionPorc,
null as RetencionBaseCuenta,
cli.Identificacion,
null as NomEmpleado,
ciud.NomCiudad,
DebitoGAAP,
CreditoGAAP,
DebitoIFRS,
CreditoIFRS,
NULL AS CodCtaUgpp,
NULL AS NomCtaUgpp,
NULL AS NumDocumento,
NULL AS NomDocumento,
NULL AS IdSucursal
FROM
(
	SELECT
	Enc.IdEmpresa,
	Cta.IdCuenta, 
	Cta.CodCuenta,
	CASE WHEN @IdCliente IS NULL THEN NULL ELSE Enc_C.IdCliente END AS IdCliente, 
	SUM(CASE WHEN Factor = 1 THEN ([Debito] - [Credito]) * [Factor] ELSE 0 END) AS Debito,
	SUM(CASE WHEN Factor = - 1 THEN ([Debito] - [Credito]) * [Factor] ELSE 0 END) AS Credito,

	SUM(CASE WHEN Factor = 1 THEN ([DebitoExt] - [CreditoExt]) * [Factor] ELSE 0 END) AS DebitoExt,
	SUM(CASE WHEN Factor = - 1 THEN ([DebitoExt] - [CreditoExt]) * [Factor] ELSE 0 END) AS CreditoExt,

	SUM(CASE WHEN Factor = 1 and Enc_C.EsNotGAAP=0 THEN ([Debito] - [Credito]) * [Factor] ELSE 0 END) AS DebitoGAAP,
	SUM(CASE WHEN Factor = - 1 and Enc_C.EsNotGAAP=0 THEN ([Debito] - [Credito]) * [Factor] ELSE 0 END) AS CreditoGAAP,

	SUM(CASE WHEN Factor = 1 and Enc_C.EsIFRS=0 THEN ([Debito] - [Credito]) * [Factor] ELSE 0 END) AS DebitoIFRS,
	SUM(CASE WHEN Factor = - 1 and Enc_C.EsIFRS=0 THEN ([Debito] - [Credito]) * [Factor] ELSE 0 END) AS CreditoIFRS,
	--Enc_C.IdInventario,
	--Enc_C.IdAbonaDif,
	CASE WHEN @SenAgrupCC = 1 THEN Enc_C.IdCentroCosto ELSE NULL END AS IdCentroCosto
	FROM Encabezados_Cont AS Enc_C 
	INNER JOIN Encabezados AS Enc ON Enc_C.IdEncab = Enc.IdEncab 
	INNER JOIN Cuentas AS Cta ON Enc_C.IdCuenta = Cta.IdCuenta 
	INNER JOIN Cuentas_Tipos AS Cta_T ON Cta.IdCuentaTipo = Cta_T.IdCuentaTipo 
	INNER JOIN Cuentas_Clases AS Cta_C ON Cta_T.IdClase = Cta_C.IdClase
    INNER JOIN (select number from iter$simple_intlist_to_tbl(@IdEmpresas))T1 on T1.number=Enc.IdEmpresa
	WHERE
	Enc.Fecha < @FechaIniT AND
	Cta.CodCuenta Between @CuentaIni And @CuentaFin AND
	Debito - Credito <> 0 AND
	Enc.EsAnulado=0 and 
    (@EsNotGAAP is null OR Enc_C.EsNotGAAP =@EsNotGAAP) AND
    (@EsNotIFRS is null OR Enc_C.EsIFRS =@EsNotIFRS) AND
	(@IdSucursal is null OR Enc.IdSucursal = @IdSucursal)
	GROUP BY 
	Cta.IdCuenta, 
	CASE WHEN @IdCliente IS NULL THEN NULL ELSE Enc_C.IdCliente END, 
	Cta.CodCuenta, 
	Enc.IdEmpresa, 
	--Enc_C.IdInventario, 
	--Enc_C.IdAbonaDif,
	CASE WHEN @SenAgrupCC = 1 THEN Enc_C.IdCentroCosto ELSE NULL END
)
VSaldo1 INNER JOIN
Empresas AS Emp ON VSaldo1.IdEmpresa = Emp.IdEmpresa INNER JOIN 
Cuentas AS Cta ON VSaldo1.IdCuenta = Cta.IdCuenta LEFT OUTER JOIN
VClientes2 AS Cli ON VSaldo1.IdCliente = Cli.IdCliente INNER JOIN
Cuentas_Tipos AS Cta_T ON Cta.IdCuentaTipo = Cta_T.IdCuentaTipo INNER JOIN
Cuentas_Clases AS Cta_C ON Cta_T.IdClase = Cta_C.IdClase LEFT OUTER JOIN
dbo.CentroCostos AS CC ON VSaldo1.IdCentroCosto = CC.IdCentroCosto LEFT OUTER JOIN
dbo.Ciudades as ciud ON ciud.IdCiudad=cli.idciudad
WHERE 

(@IdCliente is null OR VSaldo1.IdCliente =@IdCliente) AND
(@IdCentroCosto is null OR VSaldo1.IdCentroCosto =@IdCentroCosto) 



UNION ALL

select Orden, Anno, Mess,
u.CodCuenta,
CodCuenta2,
NomCuenta,
DescripCuenta,
IdCliente, NomTercero, MesLetras, Fecha, Debito, Credito, DebitoExt, CreditoExt, Nota, DocRef, Doc, Doc_FC, IdEncabCont, Factor, IdEmpresa, CodDocumento, IdCuentaTipo, IdInventario, IdAbonaDoc, IdAbonaDif, EmpresaCodigo, IdClase, IdCentroCosto, NomCentroCosto,IdDocumento,IdEncab,
RetencionBase,RetencionPorc,RetencionBase as RetencionBaseCuenta,Identificacion,NomEmpleado,NomCiudad,
DebitoGAAP,
CreditoGAAP,
DebitoIFRS,
CreditoIFRS,
isnull(Cta3.CodCuenta+', ' ,'')+isnull(Cta4.CodCuenta,'')  as CodCtaUgpp,
isnull(Cta3.NomCuentaBase+', ' ,'')+isnull(Cta4.NomCuentaBase,'')  as NomCtaUgpp,
NumDocumento,NomDocumento,Sucursal
from
(
	SELECT Orden, Anno, Mess, 
		CASE WHEN @EsAgrupIFRS=1 and len(CodCuentaIFRS)>0 then coalesce(CodCuentaIFRS,CodCuenta) else CodCuenta END AS CodCuenta,
		CASE WHEN @EsAgrupIFRS=1 then CodCuenta else  CodCuentaIFRS END AS CodCuenta2,
		NomCuenta, 
		CASE WHEN @EsAgrupIFRS=1 and len(CodCuentaIFRS)>0  then coalesce(CodCuentaIFRS,CodCuenta) + '-' + coalesce(NomCuentaIFRS,NomCuenta)  else CodCuenta + '-' + NomCuenta END AS DescripCuenta,
		IdCliente, NomTercero, MesLetras, Fecha, Debito, Credito, DebitoExt, CreditoExt, Nota, DocRef, Doc, Doc_FC, IdEncabCont, Factor, IdEmpresa, CodDocumento, IdCuentaTipo, IdInventario, IdAbonaDoc, IdAbonaDif, EmpresaCodigo, IdClase, IdCentroCosto, NomCentroCosto,IdDocumento,IdEncab,
		case when coalesce(RetencionPorc,0)> 0 then abs(Debito-Credito)*100/RetencionPorc else 0 end RetencionBase,RetencionPorc,RetencionBase as RetencionBaseCuenta,Identificacion,NomEmpleado,NomCiudad,
		case when VMov1.EsNotGAAP=0 THEN  Debito else 0 end DebitoGAAP,
		case when VMov1.EsNotGAAP=0 THEN  Credito else 0 end  CreditoGAAP,
		case when VMov1.EsIFRS=0 THEN  Debito else 0 end DebitoIFRS,
		case when VMov1.EsIFRS=0 THEN  Credito else 0 end CreditoIFRS,
		NumDocumento,NomDocumento,Sucursal
	FROM vLibroAuxiliar_Mov VMov1
	INNER JOIN (select number from iter$simple_intlist_to_tbl(@IdEmpresas))T1 on T1.number=VMov1.IdEmpresa
	WHERE
	VMov1.Fecha>=@FechaIniT AND VMov1.Fecha<=@FechaFinT AND
	VMov1.CodCuenta Between @CuentaIni And @CuentaFin AND

	(@IdCliente is null OR VMov1.IdCliente =@IdCliente) AND
	(@IdCentroCosto is null OR VMov1.IdCentroCosto =@IdCentroCosto) AND
	(@EsNotGAAP is null OR VMov1.EsNotGAAP =@EsNotGAAP) AND
	(@EsNotIFRS is null OR VMov1.EsIFRS =@EsNotIFRS) AND	
	(@IdSucursal is null OR VMov1.IdSucursal = @IdSucursal) 
)as u
LEFT OUTER JOIN
	tempCuentas AS Cta3 ON SUBSTRING(u.CodCuenta,1,4) = Cta3.CodCuenta LEFT JOIN
	tempCuentas AS Cta4 ON SUBSTRING(u.CodCuenta,1,6) = Cta4.CodCuenta 

ORDER BY CodCuenta, Orden, Fecha, IdEncabCont

OPTION(RECOMPILE)

END
GO
PRINT N'Altering Procedure [dbo].[GetVEncabezados]...';
IF OBJECT_ID(N'dbo.GetVEncabezados', N'P') IS NULL
    EXEC(N'CREATE PROCEDURE [dbo].[GetVEncabezados] AS BEGIN SET NOCOUNT ON; RETURN 0; END');

GO
ALTER PROCEDURE [dbo].[GetVEncabezados]
	-- Add the parameters for the stored procedure here
	@IdEmpresa INT,
	@IdDocumento INT,
	@NumDocumento INT,
	@IdCliente1 INT,
	@FechaIni DATETIME,
	@FechaFin DATETIME,
    @IdSucursal INT

AS
BEGIN

SET NOCOUNT ON;	
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

--DECLARE

--@IdEmpresa INT,
--@IdDocumento INT,
--@NumDocumento INT,
--@IdCliente1 INT,
--@FechaIni DATETIME,
--@FechaFin DATETIME,
--@IdSucursal INT



--SET @IdEmpresa=@IdEmpresa
--SET @IdDocumento=@IdDocumento
--SET @NumDocumento=@NumDocumento
--SET @IdCliente1=@IdCliente1
--SET @FechaIni=@FechaIni
--SET @FechaFin=@FechaFin
--SET @IdSucursal=@IdSucursal


	select q.IdEncab, SUM(SubTotal) AS SubTotal, SUM(SubTotalSinDcto) AS SubTotalSinDcto, SUM(ValorDcto) AS ValorDcto, SUM(SubIVA) AS SubIVA, SUM((SubTotal + SubIVA) 
                         + (FactorImpoConsPorc * SubTotal / 100 + FactorImpoConsValor * CantConFactor) * ValorImptoConsumo) AS TotDoc, MAX(ValorIva) AS IVA, SUM(SubTotExento) AS SubTotExento, SUM(SubTotGravado) 
                         AS SubTotGravado, SUM(CantConFactor) AS Cantidad,SUM(ValorPuntos)AS ValorPuntos
    INTO #tempMovimientos
 	from
	(  
		SELECT  EncMov.IdEncabMov, EncMov.IdEncab, EncMov.IdInventario, EncMov.IdBodega, EncMov.IdLote, EncMov.Cantidad, EncMov.Cantidad * EncMov.FactorCantidad AS CantConFactor, EncMov.CantidadFisica, 
								EncMov.IdUnidad, EncMov.FactorCantidad, EncMov.FactorMovim, EncMov.ValorUnitario, EncMov.ValorUnitarioExt, EncMov.ValorIva, EncMov.TipoImptoConsumo, isnull (EncMov.ValorImptoConsumo, 0) 
								AS ValorImptoConsumo, EncMov.TotalMov, EncMov.IdCliente, EncMov.Vence, EncMov.IdCentroCosto, EncMov.Dcto, EncMov.EsIvaAlCosto, EncMov.ValorUnitario * EncMov.Dcto / 100 AS ValorDctoUnit, 
								(EncMov.Cantidad * EncMov.FactorCantidad * EncMov.ValorUnitario) * (1 - EncMov.Dcto / 100) AS SubTotal, EncMov.Cantidad * EncMov.FactorCantidad * EncMov.ValorUnitario AS SubTotalSinDcto, 
								EncMov.Cantidad * EncMov.FactorCantidad * EncMov.ValorUnitario * EncMov.Dcto / 100 AS ValorDcto, (EncMov.Cantidad * EncMov.FactorCantidad * EncMov.ValorUnitario) * (1 - EncMov.Dcto / 100) 
								* EncMov.ValorIva / 100 AS SubIVA, CASE WHEN EncMov.TipoImptoConsumo = 0 THEN 1 ELSE 0 END AS FactorImpoConsPorc, 
								CASE WHEN EncMov.TipoImptoConsumo = 1 THEN 1 ELSE 0 END AS FactorImpoConsValor, EncMov.ValorTRM, EncMov.IdMoneda, 
								CASE WHEN EncMov.ValorIva = 0 THEN (EncMov.Cantidad * EncMov.FactorCantidad * EncMov.ValorUnitario) * (1 - EncMov.Dcto / 100) ELSE 0 END AS SubTotExento, 
								CASE WHEN EncMov.ValorIva > 0 THEN (EncMov.Cantidad * EncMov.FactorCantidad * EncMov.ValorUnitario) * (1 - EncMov.Dcto / 100) ELSE 0 END AS SubTotGravado,
								ValorPuntos
		FROM   
		dbo.Encabezados_Mov AS EncMov 
		INNER JOIN dbo.Encabezados AS Enc ON EncMov.IdEncab = Enc.IdEncab
		WHERE        
		(@IdEmpresa is null or enc.IdEmpresa=@IdEmpresa) AND
		(@IdDocumento is null or enc.IdDocumento=@IdDocumento) AND
		(@NumDocumento is null or enc.NumDocumento=@NumDocumento) AND
		(@IdCliente1 is null or enc.IdCliente1=@IdCliente1) AND
		(@FechaIni is null or enc.Fecha>=@FechaIni) AND
		(@FechaFin is null or enc.Fecha<=@FechaFin) AND
		(@IdSucursal is null or enc.IdSucursal=@IdSucursal) AND
		(EncMov.EsVisible = CASE WHEN Enc.FinanDebe = 1 AND EncMov.FinanNumCuotas >= 0 THEN 0 ELSE 1 END)
	)as q
	group by q.IdEncab

	--Crear primary key en tabla temporal
    alter table #tempMovimientos add primary key ([IdEncab])

	SELECT  IdEncab, SUM(Credito) AS Credito, SUM(Debito) AS Debito,SUM(Normal) AS SumNormal, SUM(Tesoreria) AS SumTesoreria, SUM(ReteFuente) AS SumReteFuente, SUM(Iva) AS SumIva, SUM(ReteIva) AS SumReteIva, SUM(ReteIca) AS SumReteIca, 
                        SUM(ReteIvaRegimenComun) AS SumReteIvaRegimenComun, SUM(ReteIvaRegimenSimplificadoD) AS SumReteIvaRegimenSimplificadoD, SUM(ReteivaRSC) AS SumReteIvaRSC, SUM(ImpuestoalConsumo) 
                        AS SumImpuestoalConsumo, SUM(DiferidosPasivo) AS SumDiferidosPasivo, SUM(ReteIvaNoDomiciliado) AS SumReteIvaNoDomiciliado, SUM(CuentasPorPagar) AS SumCuentasPorPagar, 
                        SUM(CuentasPorCobrar) AS SumCuentasPorCobrar, SUM(ReteFuenteCree) AS SumReteFuenteCree, SUM(ComisionTarjetas) AS SumComisionTarjetas, SUM(AutoReteCreeC) AS SumAutoReteCreeC, 
                        SUM(AutoReteCreeD) AS SumAutoReteCreeD
    INTO #tempCont
	FROM
	(
		SELECT  encCont.IdEncab, encCont.Credito AS Credito, encCont.Debito AS Debito,
		 Cuen.IdGrupoCuenta, CASE WHEN Cuen.IdGrupoCuenta = 1 THEN encCont.Credito + encCont.Debito ELSE 0 END AS Normal, 
                                                    CASE WHEN Cuen.IdGrupoCuenta = 2 THEN encCont.Credito + encCont.Debito ELSE 0 END AS Tesoreria, CASE WHEN Cuen.IdGrupoCuenta = 4 THEN encCont.Credito + encCont.Debito ELSE 0 END AS ReteFuente,
                                                    CASE WHEN Cuen.IdGrupoCuenta = 5 THEN encCont.Credito + encCont.Debito ELSE 0 END AS Iva, CASE WHEN Cuen.IdGrupoCuenta = 6 THEN encCont.Credito + encCont.Debito ELSE 0 END AS ReteIva, 
                                                    CASE WHEN Cuen.IdGrupoCuenta = 7 THEN encCont.Credito + encCont.Debito ELSE 0 END AS ReteIca, 
                                                    CASE WHEN Cuen.IdGrupoCuenta = 8 THEN encCont.Credito + encCont.Debito ELSE 0 END AS ReteIvaRegimenComun, 
                                                    CASE WHEN Cuen.IdGrupoCuenta = 9 THEN encCont.Credito + encCont.Debito ELSE 0 END AS ReteIvaRegimenSimplificadoD, 
                                                    CASE WHEN Cuen.IdGrupoCuenta = 10 THEN encCont.Credito + encCont.Debito ELSE 0 END AS ReteivaRSC, 
                                                    CASE WHEN Cuen.IdGrupoCuenta = 11 THEN encCont.Credito + encCont.Debito ELSE 0 END AS ImpuestoalConsumo, 
                                                    CASE WHEN Cuen.IdGrupoCuenta = 12 THEN encCont.Credito + encCont.Debito ELSE 0 END AS DiferidosPasivo, 
                                                    CASE WHEN Cuen.IdGrupoCuenta = 13 THEN encCont.Credito + encCont.Debito ELSE 0 END AS ReteIvaNoDomiciliado, 
                                                    CASE WHEN Cuen.IdGrupoCuenta = 20 THEN encCont.Credito + encCont.Debito ELSE 0 END AS CuentasPorPagar, 
                                                    CASE WHEN Cuen.IdGrupoCuenta = 21 THEN encCont.Credito + encCont.Debito ELSE 0 END AS CuentasPorCobrar, 
                                                    CASE WHEN Cuen.IdGrupoCuenta = 30 THEN encCont.Credito + encCont.Debito ELSE 0 END AS ReteFuenteCree, 
                                                    CASE WHEN Cuen.IdGrupoCuenta = 31 THEN encCont.Credito + encCont.Debito ELSE 0 END AS ComisionTarjetas, 
                                                    CASE WHEN Cuen.IdGrupoCuenta = 33 THEN encCont.Credito + encCont.Debito ELSE 0 END AS AutoReteCreeC, 
                                                    CASE WHEN Cuen.IdGrupoCuenta = 34 THEN encCont.Credito + encCont.Debito ELSE 0 END AS AutoReteCreeD
		FROM            dbo.Encabezados_Cont as encCont 
						INNER JOIN	dbo.Cuentas AS Cuen ON encCont.IdCuenta =Cuen.IdCuenta 
						LEFT JOIN	dbo.Cuentas_Grupos ON Cuen.IdGrupoCuenta = dbo.Cuentas_Grupos.IdGrupoCuenta 
						INNER JOIN	dbo.Encabezados Enc on encCont.IdEncab=Enc.IdEncab
		WHERE
		(@IdEmpresa is null or enc.IdEmpresa=@IdEmpresa) AND
		(@IdDocumento is null or enc.IdDocumento=@IdDocumento) AND
		(@NumDocumento is null or enc.NumDocumento=@NumDocumento) AND
		(@IdCliente1 is null or enc.IdCliente1=@IdCliente1) AND
		(@FechaIni is null or enc.Fecha>=@FechaIni) AND
		(@FechaFin is null or enc.Fecha<=@FechaFin) AND
		(@IdSucursal is null or enc.IdSucursal=@IdSucursal) 
	)AS q
	GROUP BY q.IdEncab

	alter table #tempCont add primary key ([IdEncab])

	SELECT        Enc.IdEncab, Enc.IdEmpresa, Enc.IdDocumento, Enc.IdPrefijo, pre.NomPrefijo, Enc.NumDocumento, Enc.Fecha, Enc.IdCliente1, Enc.IdCliente2, Enc.Nota, Enc.IdFormaPago, FP.NomFormaPago, 
                         dbo.Documentos.NomDocumento, Emp.EmpresaNombre, COALESCE (Cli1.Nombre1, N'') + N' ' + COALESCE (Cli1.Nombre2, N'') + N' ' + COALESCE (Cli1.Apellido1, N'') + N' ' + COALESCE (Cli1.Apellido2, N'') 
                         AS NombresCliente1, COALESCE (Cli2.Nombre1, N'') + N' ' + COALESCE (Cli2.Nombre2, N'') + N' ' + COALESCE (Cli2.Apellido1, N'') + N' ' + COALESCE (Cli2.Apellido2, N'') AS NombresCliente2, 
                         Enc.IdCliente1_IdDir, dbo.Clientes_Direcciones.Direccion, dbo.Monedas.NomMoneda1 AS NomMoneda, Enc.Vence, Reparacion_Automotores_1.NumPlaca, Reparacion_Automotores_1.NumChasis, 
                         Enc.IdAutomotor, Cli1.Identificacion, Emp.EmpresaNit, dbo.Clientes_Direcciones.Telefonos, Enc.NomPc, Enc.NumDocumentoExt, dbo.Ciudades.NomCiudad, Reparacion_Automotores_1.NumMotor, 
                         Reparacion_Automotores_1.Kilometraje, Reparacion_Automotores_1.Modelo, Reparacion_Automotores_1.Color, dbo.Reparacion_Marcas.NomMarca, Emp.EmpresaDV, Emp.EmpresaDireccion, 
                         Emp.EmpresaTelefono, Emp.EmpresaCorreoElect, COALESCE (Emp.EmpresaNit, N'') + '' + COALESCE ('-' + CAST(Emp.EmpresaDV AS varchar), N' ') AS NIT, dbo.Clientes_Contactos.Nombres AS NombresContacto, 
                         dbo.Clientes_Contactos.Telefono AS TelefonoContacto, dbo.Clientes_Contactos.Email AS EmailContacto, dbo.Clientes_Contactos.Cargo AS CargoContacto, Enc.ValorTRM, COALESCE (Cli3.Nombre1, N'') 
                         + N' ' + COALESCE (Cli3.Nombre2, N'') + N' ' + COALESCE (Cli3.Apellido1, N'') + N' ' + COALESCE (Cli3.Apellido2, N'') AS NombreVend, COALESCE (CliVend2.Nombre1, N'') + N' ' + COALESCE (CliVend2.Nombre2, 
                         N'') + N' ' + COALESCE (CliVend2.Apellido1, N'') + N' ' + COALESCE (CliVend2.Apellido2, N'') AS NombreVend2, dbo.Documentos.IdPlantilla, Enc.EsBloqueado, Enc.EsAnulado, Emp.EmpresaResolucion, 
                         dbo.Monedas.Abrev, dbo.Encabezados_Estados.NomEstado, Enc.IdEstado, dbo.Clientes_Direcciones.Celular AS CelularCli, dbo.Clientes_Direcciones.EMail AS EmailCli, Cli4.Identificacion AS IdentificacionProp, 
                         COALESCE (Cli4.Nombre1, N'') + N' ' + COALESCE (Cli4.Nombre2, N'') + N' ' + COALESCE (Cli4.Apellido1, N'') + N' ' + COALESCE (Cli4.Apellido2, N'') AS NombreProp, RegCli.NomTipoRegimen AS RegimenCli, 
                         RegProp.NomTipoRegimen AS RegimenProp, dbo.PH_Inmuebles.Direccion AS DireccionInmueble, Cli1.DVCliente, COALESCE (Cli1.Identificacion, N'') + N'' + COALESCE (N'-' + CAST(Cli1.DVCliente AS varchar), N' ')
                          AS NitCliente, VTot_EncCont.Credito, VTot_EncCont.Debito, VTot_EncCont.SumReteFuente, VTot_EncCont.SumIva, VTot_EncCont.SumReteIva, VTot_EncCont.SumReteIca, VTot_EncCont.SumReteFuenteCree, 
                         VTot_EncCont.SumReteIvaRSC, VTot_EncMov.SubTotal, VTot_EncMov.SubTotalSinDcto, VTot_EncMov.ValorDcto, VTot_EncMov.SubIVA, 
                         VTot_EncMov.TotDoc, Enc.IdSucursal, dbo.Sucursales.NomSucursal, dbo.Sucursales.CodSucursal, VTot_EncCont.SumAutoReteCreeC, VTot_EncCont.SumAutoReteCreeD, 
                         VTot_EncCont.SumImpuestoalConsumo, VTot_EncCont.SumReteIvaRegimenSimplificadoD, VTot_EncCont.SumDiferidosPasivo, VTot_EncCont.SumReteIvaNoDomiciliado, 
                         VTot_EncCont.SumCuentasPorCobrar, VTot_EncCont.SumCuentasPorPagar, VTot_EncCont.SumReteIvaRegimenComun, VTot_EncCont.SumTesoreria, VTot_EncCont.SumNormal, 
                         VTot_EncCont.SumComisionTarjetas, 0 AS EsDelete, Reparacion_Automotores_1.NomAutomotor, Enc.ValorPago, tipoDir.NomTipoDir, dbo.Documentos.CodDocumento, dbo.Encabezados_Tipos.NomTipo, 
						 VTot_EncMov.IVA, VTot_EncMov.SubTotExento, VTot_EncMov.SubTotGravado, VTot_EncMov.Cantidad, 
						 '' AS EspecialTercero01, '' AS EspecialTercero02, '' AS EspecialTercero03, '' AS EspecialTercero04, '' AS EspecialTercero05, '' AS EspecialTercero06, '' AS EspecialTercero07, '' AS EspecialTercero08,
						 '' AS EspecialTercero09, '' AS EspecialTercero10, '' AS EspecialTercero11, '' AS EspecialTercero12, '' AS EspecialTercero13, '' AS EspecialTercero14, '' AS EspecialTercero15, '' AS EspecialTercero16,
						 '' AS EspecialTercero17, '' AS EspecialTercero18, '' AS EspecialTercero19, '' AS EspecialTercero20, '' AS EspecialTercero21, '' AS EspecialTercero22, '' AS EspecialTercero23, '' AS EspecialTercero24,
						 '' AS EspecialTercero25, '' AS EspecialTercero26, '' AS EspecialTercero27, '' AS EspecialTercero28, '' AS EspecialTercero29, '' AS EspecialTercero30, '' AS EspecialTercero31, '' AS EspecialTercero32,
						 '' AS EspecialTercero33, '' AS EspecialTercero34, '' AS EspecialTercero35, '' AS EspecialTercero36, '' AS EspecialTercero37, '' AS EspecialTercero38, '' AS EspecialTercero39, '' AS EspecialTercero40,
						 '' AS Especial01, '' AS Especial02, '' AS Especial03, '' AS Especial04, '' AS Especial05, '' AS Especial06, '' AS Especial07, '' AS Especial08, '' AS Especial09, '' AS Especial10,
						 '' AS Especial11, '' AS Especial12, '' AS Especial13, '' AS Especial14, '' AS Especial15, '' AS Especial16, '' AS Especial17, '' AS Especial18, '' AS Especial19, '' AS Especial20,
						 '' AS Especial21, '' AS Especial22, '' AS Especial23, '' AS Especial24, '' AS Especial25, '' AS Especial26, '' AS Especial27, '' AS Especial28, '' AS Especial29, '' AS Especial30,
						 '' AS Especial31, '' AS Especial32, '' AS Especial33, '' AS Especial34, '' AS Especial35, '' AS Especial36, '' AS Especial37, '' AS Especial38, '' AS Especial39, '' AS Especial40,
						 NULL AS IdHistClinPaciente, '' AS TipoIdenHistClinPaciente, '' AS IdenHistClinPaciente, '' AS NombresHistClinPaciente, '' AS ApellidosHistClinPaciente, NULL AS EdadHistClinPaciente, NULL AS FechaNaciHistClinPaciente,
						 '' AS DireccionHistClinPaciente, '' AS GeneroHistClinPaciente, '' AS CelularHistClinPaciente,'' AS TelefonosHistClinPaciente,'' AS MedPrepContratoHistClin, '' AS IdenPrepagadaHistClin,
						 '' AS NomPrepagadaHistClin, '' AS EpsContratoHistClin, '' AS IdenEpsHistClin,'' AS NomEpsHistClin, NULL AS FechaIniHistClinPaciente, NULL AS FechaFinHistClinPaciente,'' AS CodigoInterno,
						 cast(Zona1.IdZona1 as nvarchar(10))+'-'+Zona1.NomZona1 as Zona1, VTot_EncMov.ValorPuntos,CAST(0 AS numeric(28,6)) SaldoPuntosCliente, Enc.FeCUFE, Enc.FeFechaAutorizacion, Enc.FeIdProveedor, FeProvee.NomProveedor,
						 FeEstDian.NomEstado AS FeNomEstadoDian, FeEstProvee.NomEstado AS FeNomEstadoProvee, FeConcep.NomConcepto, Enc.EsFeSincronizada, Emp.NumLocalizacionGlobal,Enc.FeIdEstadoDian, FeTipoOpe.CodOperacion AS TipoOpeEnc, Enc.IdDocReferido, DocReferencia.NomDocumento AS NomDocReferido,
						 FeMedPago.CodMedPago, FeMedPago.IdMedPago, FeTipoOp.CodOperacion AS TipoOpeDoc, Enc.FeCufeND, Enc.FePrefijoND, Enc.FeNumDocND, Enc.FeFechaEmisionND, Enc.IdMoneda,  FeEstProvee.IdEstado AS FeEstProveedor, Enc.FeIdVersion AS FeDocVersion, Enc.IdClienteVend, Enc.FeOrdenComp, 
						 Enc.FeDocDesp, Enc.FeIdTipoIngreso, Enc.FeEnvioAnticipo, Enc.FeIdConcepto, Enc.FeNotasFechaIni, Enc.FeNotasFechaFin, Mesas.NomMesa, Mesas.DescripMesa, Enc.IdCaja

	FROM  dbo.Encabezados AS Enc INNER JOIN
		  dbo.Empresas AS Emp ON Enc.IdEmpresa = Emp.IdEmpresa INNER JOIN
		  dbo.Documentos ON Enc.IdDocumento = dbo.Documentos.IdDocumento INNER JOIN
		  dbo.Clientes as Cli1 ON Cli1.IdCliente = Enc.IdCliente1 LEFT  JOIN
          #tempMovimientos AS VTot_EncMov ON Enc.IdEncab = VTot_EncMov.IdEncab LEFT  JOIN
          #tempCont as VTot_EncCont ON Enc.IdEncab = VTot_EncCont.IdEncab LEFT  JOIN
          dbo.Clientes AS Cli4 ON Enc.IdClienteProp = Cli4.IdCliente LEFT  JOIN
          dbo.Clientes AS Cli2 ON Enc.IdCliente2 = Cli2.IdCliente LEFT  JOIN
          dbo.Clientes AS Cli3 ON Enc.IdClienteVend = Cli3.IdCliente LEFT  JOIN
          dbo.Clientes AS CliVend2 ON Enc.IdClienteVend2 = CliVend2.IdCliente LEFT  JOIN
		  dbo.Sucursales ON dbo.Sucursales.IdSucursal = Enc.IdSucursal LEFT  JOIN
		  dbo.Clientes_Contactos ON Enc.IdContacto = dbo.Clientes_Contactos.IdContacto LEFT  JOIN	
		  dbo.Clientes_Direcciones ON Enc.IdCliente1_IdDir = dbo.Clientes_Direcciones.IdClienteDir LEFT  JOIN
		  dbo.Ciudades ON dbo.Ciudades.IdCiudad = dbo.Clientes_Direcciones.IdCiudad  LEFT  JOIN
		  dbo.Monedas ON Enc.IdMoneda = dbo.Monedas.IdMoneda LEFT  JOIN
		  dbo.Reparacion_Automotores AS Reparacion_Automotores_1 ON  Enc.IdAutomotor = Reparacion_Automotores_1.IdAutomotor  LEFT  JOIN
		  dbo.Reparacion_Marcas ON dbo.Reparacion_Marcas.IdMarca = Reparacion_Automotores_1.IdMarca LEFT  JOIN
          dbo.FormasPago AS FP ON Enc.IdFormaPago = FP.IdFormaPago LEFT  JOIN
          dbo.Encabezados_Estados ON Enc.IdEstado = dbo.Encabezados_Estados.IdEstado LEFT  JOIN
          dbo.Clientes_TiposRegimen AS RegCli ON Cli1.IdTipoRegimen = RegCli.IdTipoRegimen LEFT  JOIN
          dbo.Clientes_TiposRegimen AS RegProp ON Cli4.IdTipoRegimen = RegProp.IdTipoRegimen LEFT  JOIN
          dbo.PH_Inmuebles ON dbo.PH_Inmuebles.IdInmueble = Enc.IdInmueble LEFT  JOIN
          dbo.Clientes_TiposDireccion AS tipoDir ON tipoDir.IdTipoDir = dbo.Clientes_Direcciones.IdTipoDir LEFT  JOIN
          dbo.Prefijos AS pre ON pre.IdPrefijo = Enc.IdPrefijo LEFT  JOIN
		  dbo.Encabezados_Tipos ON  dbo.Encabezados_Tipos.IdTipo=Enc.IdTipo LEFT JOIN
		  dbo.Clientes_Zona1 AS Zona1 ON Cli1.IdZona1=Zona1.IdZona1 LEFT JOIN 
		  dbo.FacturacionElectronica_Proveedores AS FeProvee ON Enc.FeIdProveedor = FeProvee.IdProveedor LEFT  JOIN
		  dbo.FacturacionElectronica_Proveedores_Estados AS FeEstDian ON Enc.FeIdEstadoDian = FeEstDian.IdEstado LEFT  JOIN
		  dbo.FacturacionElectronica_Proveedores_Estados AS FeEstProvee ON Enc.FeIdEstadoProveedor = FeEstProvee.IdEstado  LEFT  JOIN
		  dbo.FacturacionElectronica_Conceptos_Documentos AS FeConcep ON Enc.FeIdConcepto = FeConcep.IdConcepto LEFT OUTER JOIN
		  dbo.FacturacionElectronica_MediosPagoContado AS FeMedPago ON FeMedPago.IdMedPago = Enc.FeIdMedPago LEFT OUTER JOIN
		  dbo.FacturacionElectronica_TipoOperacion AS FeTipoOpe ON FeTipoOpe.IdOperacion = Enc.FeIdOperacion LEFT OUTER JOIN
		  dbo.FacturacionElectronica_TipoOperacion AS FeTipoOp ON FeTipoOp.IdOperacion = Documentos.FeIdOperacion LEFT OUTER JOIN
		  dbo.Documentos AS DocReferencia ON DocReferencia.IdDocumento = Enc.IdDocReferido LEFT OUTER JOIN
          dbo.Encabezados_Mesas AS Mesas ON Mesas.IdMesa = Enc.IdMesa
		  		 
	WHERE
	(@IdEmpresa is null or enc.IdEmpresa=@IdEmpresa) AND
	(@IdDocumento is null or enc.IdDocumento=@IdDocumento) AND
	(@NumDocumento is null or enc.NumDocumento=@NumDocumento) AND
	(@IdCliente1 is null or enc.IdCliente1=@IdCliente1) AND
	(@FechaIni is null or enc.Fecha>=@FechaIni) AND
	(@FechaFin is null or enc.Fecha<=@FechaFin) AND
	(@IdSucursal is null or enc.IdSucursal=@IdSucursal)
	
	OPTION(RECOMPILE)
END
GO
PRINT N'Refreshing Function [dbo].[ufnContabilizacion_FormasPago]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[ufnContabilizacion_FormasPago]';


GO
PRINT N'Refreshing Function [dbo].[ufnContabilizacion_Normal]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[ufnContabilizacion_Normal]';


GO
PRINT N'Refreshing Function [dbo].[ufnVacacionesDisponibles]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[ufnVacacionesDisponibles]';


GO
PRINT N'Refreshing Function [dbo].[ufnConciliacion_Contables]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[ufnConciliacion_Contables]';


GO
PRINT N'Refreshing Function [dbo].[ufnMediosMagneticos_CuantiasMenores]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[ufnMediosMagneticos_CuantiasMenores]';


GO
PRINT N'Refreshing Function [dbo].[ufnMediosMagneticos_CuantiasMenores_Saldos]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[ufnMediosMagneticos_CuantiasMenores_Saldos]';


GO
PRINT N'Refreshing Procedure [dbo].[TrasladarTerceros]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[TrasladarTerceros]';


GO
PRINT N'Refreshing Procedure [dbo].[CrucesDocumentosDeleteALL]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[CrucesDocumentosDeleteALL]';


GO
PRINT N'Refreshing Procedure [dbo].[CrucesDocumentosInsertar]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[CrucesDocumentosInsertar]';


GO
PRINT N'Refreshing Procedure [dbo].[BloquearDocumentos]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[BloquearDocumentos]';


GO
PRINT N'Refreshing Procedure [dbo].[EncabezadosDeleteALL]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[EncabezadosDeleteALL]';


GO
PRINT N'Refreshing Procedure [dbo].[TrasladarInventarios]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[TrasladarInventarios]';


GO
PRINT N'Refreshing Procedure [dbo].[DesbloquearDocumentos]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[DesbloquearDocumentos]';


GO
PRINT N'Refreshing Procedure [dbo].[ContabilizarDoc]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[ContabilizarDoc]';


GO
PRINT N'Refreshing Procedure [dbo].[CalcularDiferenciaCambio]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[CalcularDiferenciaCambio]';


GO
PRINT N'Refreshing Procedure [dbo].[TrasladarCuentas]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[TrasladarCuentas]';


GO
PRINT N'Refreshing Procedure [dbo].[CancelarCuentasResultado]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[CancelarCuentasResultado]';


GO
PRINT N'Refreshing Procedure [dbo].[GenerarCierreInventario]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GenerarCierreInventario]';


GO
PRINT N'Refreshing Procedure [dbo].[CalcularCostoVenta]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[CalcularCostoVenta]';


GO
PRINT N'Refreshing Procedure [dbo].[CopiarDocumentos]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[CopiarDocumentos]';


GO
PRINT N'Refreshing Procedure [dbo].[CopiarDocumentosBasados]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[CopiarDocumentosBasados]';


GO
PRINT N'Refreshing Procedure [dbo].[CalcularCostoPromedio]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[CalcularCostoPromedio]';


GO
PRINT N'Refreshing Procedure [dbo].[CalcularCostoPromedioGeneral]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[CalcularCostoPromedioGeneral]';


GO
PRINT N'Refreshing Procedure [dbo].[CalcularRutasProduccionMov]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[CalcularRutasProduccionMov]';


GO
PRINT N'Refreshing Procedure [dbo].[CierresDocsUpdate]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[CierresDocsUpdate]';


GO
PRINT N'Refreshing Procedure [dbo].[ContabilizarDoc_Validar]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[ContabilizarDoc_Validar]';


GO
PRINT N'Refreshing Procedure [dbo].[CostoPromHistoDeleteALL]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[CostoPromHistoDeleteALL]';


GO
PRINT N'Refreshing Procedure [dbo].[GenerarAlertaMantenimientos]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GenerarAlertaMantenimientos]';


GO
PRINT N'Refreshing Procedure [dbo].[GenerarAlertaResolucionPorNumeracion]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GenerarAlertaResolucionPorNumeracion]';


GO
PRINT N'Refreshing Procedure [dbo].[GenerarImptosDet]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GenerarImptosDet]';


GO
PRINT N'Refreshing Procedure [dbo].[GenerarInteresesCesantias]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GenerarInteresesCesantias]';


GO
PRINT N'Refreshing Procedure [dbo].[GetActivosFijosAgrup]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetActivosFijosAgrup]';


GO
PRINT N'Refreshing Procedure [dbo].[GetActivosFijosContable]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetActivosFijosContable]';


GO
PRINT N'Refreshing Procedure [dbo].[GetArqueoCaja]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetArqueoCaja]';


GO
PRINT N'Refreshing Procedure [dbo].[GetBalanceGeneral]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetBalanceGeneral]';


GO
PRINT N'Refreshing Procedure [dbo].[GetBalanceGeneralPorCentroCosto]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetBalanceGeneralPorCentroCosto]';


GO
PRINT N'Refreshing Procedure [dbo].[GetBalancePruebaPorCentroCosto]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetBalancePruebaPorCentroCosto]';


GO
PRINT N'Refreshing Procedure [dbo].[GetCalcularDiasLiquidados]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetCalcularDiasLiquidados]';


GO
PRINT N'Refreshing Procedure [dbo].[GetCarteraMovimientos]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetCarteraMovimientos]';


GO
PRINT N'Refreshing Procedure [dbo].[GetClientesPuntos]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetClientesPuntos]';


GO
PRINT N'Refreshing Procedure [dbo].[GetComisiones]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetComisiones]';


GO
PRINT N'Refreshing Procedure [dbo].[GetConciliacionContable]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetConciliacionContable]';


GO
PRINT N'Refreshing Procedure [dbo].[GetConsecutivosFaltantes]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetConsecutivosFaltantes]';


GO
PRINT N'Refreshing Procedure [dbo].[GetCostoPromedioDet]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetCostoPromedioDet]';


GO
PRINT N'Refreshing Procedure [dbo].[GetDocsDiferencias]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetDocsDiferencias]';


GO
PRINT N'Refreshing Procedure [dbo].[GetDocumentosCierre]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetDocumentosCierre]';


GO
PRINT N'Refreshing Procedure [dbo].[GetEstadoCuenta]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetEstadoCuenta]';


GO
PRINT N'Refreshing Procedure [dbo].[GetEstadoResultados]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetEstadoResultados]';


GO
PRINT N'Refreshing Procedure [dbo].[GetEstadoResultadosPorCentroCosto]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetEstadoResultadosPorCentroCosto]';


GO
PRINT N'Refreshing Procedure [dbo].[GetExistenciasPorProducto]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetExistenciasPorProducto]';


GO
PRINT N'Refreshing Procedure [dbo].[GetImptoRetenciones]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetImptoRetenciones]';


GO
PRINT N'Refreshing Procedure [dbo].[GetInformeRIPS]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetInformeRIPS]';


GO
PRINT N'Refreshing Procedure [dbo].[GetInformeUGPP]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetInformeUGPP]';


GO
PRINT N'Refreshing Procedure [dbo].[GetInventarios_Existencia_RecursosOrig]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetInventarios_Existencia_RecursosOrig]';


GO
PRINT N'Refreshing Procedure [dbo].[GetInventarioYBalances]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetInventarioYBalances]';


GO
PRINT N'Refreshing Procedure [dbo].[GetInventarioYBalancesBorrar]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetInventarioYBalancesBorrar]';


GO
PRINT N'Refreshing Procedure [dbo].[GetKardexInventarioPorAgrupaciones]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetKardexInventarioPorAgrupaciones]';


GO
PRINT N'Refreshing Procedure [dbo].[GetLiquidarPrestaciones]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetLiquidarPrestaciones]';


GO
PRINT N'Refreshing Procedure [dbo].[GetMayorYBalances]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetMayorYBalances]';


GO
PRINT N'Refreshing Procedure [dbo].[GetMediosMagneticos]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetMediosMagneticos]';


GO
PRINT N'Refreshing Procedure [dbo].[GetPagoSeguridadSocial]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetPagoSeguridadSocial]';


GO
PRINT N'Refreshing Procedure [dbo].[GetPresupuesto]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetPresupuesto]';


GO
PRINT N'Refreshing Procedure [dbo].[GetReteFteDet]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetReteFteDet]';


GO
PRINT N'Refreshing Procedure [dbo].[GetReteFteTotal]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetReteFteTotal]';


GO
PRINT N'Refreshing Procedure [dbo].[GetTablaAmortizacion]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetTablaAmortizacion]';


GO
PRINT N'Refreshing Procedure [dbo].[GetVacaciones]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetVacaciones]';


GO
PRINT N'Refreshing Procedure [dbo].[GetVentasAgrupadoPorCliVend]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetVentasAgrupadoPorCliVend]';


GO
PRINT N'Refreshing Procedure [dbo].[GetVentasPorMes]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetVentasPorMes]';


GO
PRINT N'Refreshing Procedure [dbo].[LiqPrestacionesNomina]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[LiqPrestacionesNomina]';


GO
PRINT N'Refreshing Procedure [dbo].[LiquidarVacacionesNomina]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[LiquidarVacacionesNomina]';


GO
PRINT N'Refreshing Procedure [dbo].[NominaCantDet]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[NominaCantDet]';


GO
PRINT N'Refreshing Procedure [dbo].[NominaElectronicaInsertarEmpleados]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[NominaElectronicaInsertarEmpleados]';


GO
PRINT N'Refreshing Procedure [dbo].[SaldoVentasClientesPorProducto]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[SaldoVentasClientesPorProducto]';


GO
PRINT N'Refreshing Procedure [dbo].[UltimoValorUnitario]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[UltimoValorUnitario]';


GO
PRINT N'Refreshing Procedure [dbo].[Ziur_CrearClienteDeSistema]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[Ziur_CrearClienteDeSistema]';


GO
PRINT N'Refreshing Procedure [dbo].[ValorUnitarioPorCostoPromedio]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[ValorUnitarioPorCostoPromedio]';


GO
PRINT N'Refreshing Procedure [dbo].[ActivosMatchManual]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[ActivosMatchManual]';


GO
PRINT N'Refreshing Procedure [dbo].[ConciliacionManual]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[ConciliacionManual]';


GO
PRINT N'Refreshing Procedure [dbo].[ConciliacionMatch]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[ConciliacionMatch]';


GO
PRINT N'Refreshing Procedure [dbo].[GetDocsSinCruzar]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetDocsSinCruzar]';


GO
PRINT N'Refreshing Procedure [dbo].[ActualizarAudit]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[ActualizarAudit]';


GO
PRINT N'Refreshing Procedure [dbo].[GetLibroDiario]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetLibroDiario]';


GO
PRINT N'Refreshing Procedure [dbo].[GetTareasCitas]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetTareasCitas]';


GO
PRINT N'Refreshing Procedure [dbo].[GetInventarios_Existencia_Recursos]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetInventarios_Existencia_Recursos]';


GO
PRINT N'Refreshing Procedure [dbo].[GetCostoActual]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetCostoActual]';


GO
PRINT N'Refreshing Procedure [dbo].[GenerarCrucesCont]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GenerarCrucesCont]';


GO
PRINT N'Refreshing Procedure [dbo].[GetActivosFijosDet]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetActivosFijosDet]';


GO
PRINT N'Refreshing Procedure [dbo].[ContabilizarNomina]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[ContabilizarNomina]';


GO
PRINT N'Refreshing Procedure [dbo].[GetMovimientosFactElec]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetMovimientosFactElec]';


GO
PRINT N'Refreshing Procedure [dbo].[GenerarFormulaPorInventario]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GenerarFormulaPorInventario]';


GO
PRINT N'Refreshing Procedure [dbo].[GetLiquidarNomina]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[GetLiquidarNomina]';


GO
PRINT N'Refreshing Procedure [dbo].[ComisionesPorDoc]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[ComisionesPorDoc]';


GO
PRINT N'Refreshing Procedure [dbo].[DispersarNomina]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[DispersarNomina]';


GO
	/*
	Plantilla de script posterior a la implementación							
	--------------------------------------------------------------------------------------
	 Este archivo contiene instrucciones de SQL que se anexarán al script de compilación.		
	 Use la sintaxis de SQLCMD para incluir un archivo en el script posterior a la implementación.			
	 Ejemplo:      :r .\miArchivo.sql								
	 Use la sintaxis de SQLCMD para hacer referencia a una variable en el script posterior a la implementación.		
	 Ejemplo:      :setvar TableName miTabla							
				   SELECT * FROM [$(TableName)]					
	--------------------------------------------------------------------------------------
	*/

	--
	IF NOT EXISTS (SELECT * FROM FacturacionElectronica_TipoOperacion WHERE IdOperacion = 23)
	BEGIN
		INSERT INTO FacturacionElectronica_TipoOperacion (IdOperacion, CodOperacion, NomOperacion, EsActivo, IdDocumento, CodZiurOne)
		VALUES (23, 9, 'AIU', 1, 46, 9);
	END


	IF NOT EXISTS (SELECT * FROM FacturacionElectronica_TipoOperacion WHERE IdOperacion = 24)
	BEGIN
		INSERT INTO FacturacionElectronica_TipoOperacion (IdOperacion, CodOperacion, NomOperacion, EsActivo, IdDocumento, CodZiurOne)
		VALUES (24, 10, 'Estándar', 1, 46, 10);
	END

	IF NOT EXISTS (SELECT * FROM FacturacionElectronica_TipoOperacion WHERE IdOperacion = 25)
	BEGIN
		INSERT INTO FacturacionElectronica_TipoOperacion (IdOperacion, CodOperacion, NomOperacion, EsActivo, IdDocumento, CodZiurOne)
		VALUES (25, 11, 'Mandatos', 1, 46, 11);
	END

	IF NOT EXISTS (SELECT * FROM FacturacionElectronica_TipoOperacion WHERE IdOperacion = 26)
	BEGIN
		INSERT INTO FacturacionElectronica_TipoOperacion (IdOperacion, CodOperacion, NomOperacion, EsActivo, IdDocumento, CodZiurOne)
		VALUES (26, 12, 'Transporte', 1, 46, 21);
	END

	IF NOT EXISTS (SELECT * FROM FacturacionElectronica_TipoOperacion WHERE IdOperacion = 27)
	BEGIN
		INSERT INTO FacturacionElectronica_TipoOperacion (IdOperacion, CodOperacion, NomOperacion, EsActivo, IdDocumento, CodZiurOne)
		VALUES (27, 13, 'Cambiario', 1, 46, 22);
	END
GO

GO
PRINT N'Checking existing data against newly created constraints';




GO
IF OBJECT_ID(N'dbo.Encabezados', N'U') IS NOT NULL
AND EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = N'FK_Encabezados_Cajas' AND parent_object_id = OBJECT_ID(N'dbo.Encabezados'))
    ALTER TABLE [dbo].[Encabezados] WITH CHECK CHECK CONSTRAINT [FK_Encabezados_Cajas];

GO
PRINT N'Update complete.';


GO


--Actualizar datos de la version
IF NOT EXISTS (SELECT 1 FROM dbo.ZiurVersiones WHERE NumVersion = 956)
BEGIN
    INSERT INTO dbo.ZiurVersiones (IdVersion, NumVersion, Descripcion)
    VALUES (366, 956, 'Documento Equivalente POS');
END
GO
/* Liberar bloqueo exclusivo por base de datos */
DECLARE @__ZiurDbScriptLockResourceEnd NVARCHAR(200) = N'ZiurSoft:DbScriptLock:' + CAST(DB_ID() AS NVARCHAR(20));
EXEC sp_releaseapplock
    @Resource = @__ZiurDbScriptLockResourceEnd,
    @LockOwner = N'Session';
GO
