--*Solo si no ha actualizado 4.0.22
--ALTER TABLE Companias ADD TipoEDS VARCHAR(10) DEFAULT('NINGUNO')
--GO
ALTER TABLE Companias ADD CodPlanta VARCHAR(20) DEFAULT('0') NOT NULL
GO
ALTER TABLE Sys_Cnxextna DROP CONSTRAINT CK_Sys_CnxextnaCnxEngine
GO
ALTER TABLE Sys_Cnxextna ALTER COLUMN CnxEngine VARCHAR(10) NOT NULL
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsCompanias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsCompanias]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryCompanias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryCompanias]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryCompaniasLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryCompaniasLta]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryCompanias_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryCompanias_Cr]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpCompanias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpCompanias]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsCnxextna]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsCnxextna]
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryCnxextna]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryCnxextna]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryCnxextnaLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryCnxextnaLta]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryCnxextnaTip]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryCnxextnaTip]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpCnxextna]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpCnxextna]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paInsCompanias @pmIdCia CHAR(2),@pmCompania VARCHAR(50),@pmDirInterface VARCHAR(255),@pmFechaActual SMALLDATETIME
,@pmPeriodoActual SMALLDATETIME,@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdTercero VARCHAR(16)
,@pmCodSucsal VARCHAR(10),@pmDireccSuc VARCHAR(250),@pmTelefSuc VARCHAR(20),@pmFaxSuc VARCHAR(20),@pmIdLocal VARCHAR(8),@pmTipoEDS VARCHAR(10)
,@pmCodPlanta VARCHAR(20),@pmFechaAdd SMALLDATETIME,@pmInactivo BIT
 AS 
 INSERT INTO Companias (IdCia,Compania,DirInterface,FechaActual,PeriodoActual,IdCCosto,IdSubCos,IdTercero
,CodSucsal,DireccSuc,TelefSuc,FaxSuc,IdLocal,TipoEDS,CodPlanta,FechaAdd,Inactivo)
 VALUES (@pmIdCia,@pmCompania,@pmDirInterface,@pmFechaActual,@pmPeriodoActual,@pmIdCCosto,@pmIdSubCos,@pmIdTercero
,@pmCodSucsal,@pmDireccSuc,@pmTelefSuc,@pmFaxSuc,@pmIdLocal,@pmTipoEDS,@pmCodPlanta,@pmFechaAdd,@pmInactivo)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paQryCompanias @pmIdCia CHAR(2)
 AS 
 SELECT IdCia,Compania,DirInterface,FechaActual,PeriodoActual,IdCCosto,IdSubCos,IdTercero
,CodSucsal,DireccSuc,TelefSuc,FaxSuc,IdLocal,TipoEDS,CodPlanta,FechaAdd,FechaUpdate,Inactivo
 FROM Companias
 WHERE IdCia LIKE ISNULL(@pmIdCia,'%%')
 ORDER BY IdCia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryCompaniasLta @pmIdCia CHAR(2)=Null,@pmInactivo BIT=Null
 AS 
 SELECT IdCia,Compania,DirInterface,FechaActual,PeriodoActual,Inactivo,IdCCosto,IdSubCos,IdTercero
,CodSucsal,DireccSuc,TelefSuc,FaxSuc,IdLocal,TipoEDS,CodPlanta,FechaAdd,FechaUpdate
 FROM Companias
 WHERE IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND (Inactivo=ISNULL(@pmInactivo,0) or Inactivo=ISNULL(@pmInactivo,1))
 ORDER BY IdCia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryCompanias_Cr @pmInactivo BIT=Null
 AS 
 SELECT IdCia,Compania,DirInterface,FechaActual,PeriodoActual,C.IdCCosto AS CodCcosto,CCosto,C.IdSubCos AS CodSubCosto,SubCosto
,IdTercero,CodSucsal,DireccSuc,TelefSuc,FaxSuc,C.IdLocal AS CodCiu,Localidad,TipoEDS,CodPlanta,C.FechaAdd AS Fecha_Add,C.FechaUpdate AS FecUpd,C.Inactivo AS Inactvo
 FROM Companias AS C INNER JOIN CentroCosto AS CC ON C.IdCCosto=CC.IdCCosto 
 INNER JOIN  SubCentros AS SC ON C.IdSubCos=SC.IdSubCos
 INNER JOIN Localidades AS L ON C.IdLocal=L.IdLocal
 WHERE (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
 ORDER BY IdCia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paUpCompanias @pmIdCia CHAR(2),@pmCompania VARCHAR(50),@pmDirInterface VARCHAR(255),@pmFechaActual SMALLDATETIME,@pmPeriodoActual SMALLDATETIME,@pmIdCCosto VARCHAR(16)
,@pmIdSubCos VARCHAR(16),@pmIdTercero VARCHAR(16),@pmCodSucsal VARCHAR(10),@pmDireccSuc VARCHAR(250),@pmTelefSuc VARCHAR(20),@pmFaxSuc VARCHAR(20),@pmIdLocal VARCHAR(8)
,@pmTipoEDS VARCHAR(10),@pmCodPlanta VARCHAR(20),@pmFechaUpdate SMALLDATETIME,@pmInactivo BIT
 AS 
 UPDATE Companias SET Compania=@pmCompania,DirInterface=@pmDirInterface,FechaActual=@pmFechaActual,PeriodoActual=@pmPeriodoActual,IdCCosto=@pmIdCCosto
,IdSubCos=@pmIdSubCos,IdTercero=@pmIdTercero,CodSucsal=@pmCodSucsal,DireccSuc=@pmDireccSuc,TelefSuc=@pmTelefSuc,FaxSuc=@pmFaxSuc, IdLocal=@pmIdLocal,TipoEDS=@pmTipoEDS
,CodPlanta=@pmCodPlanta,FechaUpdate=@pmFechaUpdate,Inactivo=@pmInactivo
 WHERE IdCia=@pmIdCia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsCnxextna @pmIdReg INT,@pmCnxEngine VARCHAR(10),@pmCnxDso VARCHAR(250),@pmCnxDbname VARCHAR(50),@pmCnxAutentic VARCHAR(3)
,@pmCnxUid VARCHAR(50),@pmCnxPwd VARCHAR(50),@pmCnxOwner VARCHAR(20),@pmCnxObserv VARCHAR(250),@pmCnxTipo VARCHAR(10),@pmCnxPpties VARCHAR(250)
,@pmCnxDriver VARCHAR(100),@pmCnxPort VARCHAR(30)
 AS 
 INSERT INTO Sys_Cnxextna (IdReg,CnxTipo,CnxEngine,CnxDso,CnxDbname,CnxAutentic,CnxUid,CnxPwd,CnxOwner,CnxPpties,CnxObserv,CnxDriver,CnxPort)
 VALUES (@pmIdReg,@pmCnxTipo,@pmCnxEngine,@pmCnxDso,@pmCnxDbname,@pmCnxAutentic,@pmCnxUid,@pmCnxPwd,@pmCnxOwner,@pmCnxPpties,@pmCnxObserv,@pmCnxDriver,@pmCnxPort)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryCnxextna @pmIdReg INT
 AS 
 SELECT IdReg,CnxTipo,CnxEngine,CnxDso,CnxDbname,CnxAutentic,CnxUid,CnxPwd
,CnxOwner,CnxObserv,CnxPpties,CnxDriver,CnxPort
 FROM Sys_Cnxextna
 WHERE IdReg=@pmIdReg
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryCnxextnaLta @pmCnxTipo VARCHAR(10)=Null
 AS 
 SELECT IdReg,CnxTipo,CnxEngine,CnxDso,CnxDbname,CnxAutentic,CnxUid,CnxOwner,CnxObserv
,CnxPpties,CnxDriver,CnxPort,CnxPwd
 FROM Sys_Cnxextna
 WHERE  CnxTipo LIKE ISNULL(@pmCnxTipo,'%')
 ORDER BY IdReg
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryCnxextnaTip @pmCnxTipo VARCHAR(10)
 AS 
 SELECT IdReg,CnxTipo,CnxEngine,CnxDso,CnxDbname,CnxAutentic,CnxUid,CnxPwd,CnxOwner,CnxObserv
,CnxPpties,CnxDriver,CnxPort
 FROM Sys_Cnxextna
 WHERE  CnxTipo=@pmCnxTipo
 ORDER BY IdReg
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paUpCnxextna @pmIdReg INT,@pmCnxEngine VARCHAR(10),@pmCnxDso VARCHAR(250),@pmCnxDbname VARCHAR(50),@pmCnxAutentic VARCHAR(3)
,@pmCnxUid VARCHAR(50),@pmCnxPwd VARCHAR(50),@pmCnxOwner VARCHAR(20),@pmCnxObserv VARCHAR(250),@pmCnxTipo VARCHAR(10),@pmCnxPpties VARCHAR(250)
,@pmCnxDriver VARCHAR(100),@pmCnxPort VARCHAR(30)
 AS 
 UPDATE Sys_Cnxextna SET CnxEngine=@pmCnxEngine,CnxDso=@pmCnxDso,CnxDbname=@pmCnxDbname,CnxAutentic=@pmCnxAutentic,CnxUid=@pmCnxUid,CnxPwd=@pmCnxPwd
,CnxOwner=@pmCnxOwner,CnxObserv=@pmCnxObserv,CnxTipo=@pmCnxTipo,CnxPpties=@pmCnxPpties,CnxDriver=@pmCnxDriver,CnxPort=@pmCnxPort
 WHERE IdReg=@pmIdReg
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO