ALTER TABLE TiposMotdev ADD EstadoPed BIT DEFAULT(0) NOT NULL,CodEstadoPed VARCHAR(4)
,BodegaDev BIT DEFAULT(0) NOT NULL,CodBodegaDev VARCHAR(4)
GO
CREATE TABLE Trn_FinPrestRefina ( 
TipDoc VARCHAR(3) DEFAULT ('PR1') NOT NULL,
IdPrestamo INT DEFAULT ((0)) NOT NULL,
IdCia CHAR(2) DEFAULT ('01') NOT NULL,
Item INT DEFAULT ((0)) NOT NULL,
NumPreRef INT NOT NULL,
CdCiaRef CHAR(2) NOT NULL,
Concepto VARCHAR(10) DEFAULT ('CAPITAL') NOT NULL,
VrTotal MONEY DEFAULT ((0)) NOT NULL,
VrAbonado MONEY DEFAULT ((0)) NOT NULL,
VrSaldo MONEY DEFAULT ((0)) NOT NULL,
VrBaseLiq MONEY DEFAULT ((0)) NOT NULL
 CONSTRAINT PK_Trn_FinPrestRefina PRIMARY KEY CLUSTERED (TipDoc,IdPrestamo,IdCia,Item),
 CONSTRAINT CK_Trn_FinPrestRefinaCdCiaRef CHECK ((len([CdCiaRef])>(0))),
 CONSTRAINT CK_Trn_FinPrestRefinaConcepto CHECK ((len([Concepto])>(0))),
 CONSTRAINT CK_Trn_FinPrestRefinaIdCia CHECK ((len([IdCia])>(0))),
 CONSTRAINT CK_Trn_FinPrestRefinaTipDoc CHECK ((len([TipDoc])>(0))))
GO
 CREATE NONCLUSTERED INDEX IX_Trn_FinPrestRefinaNumPreRef ON Trn_FinPrestRefina(NumPreRef,CdCiaRef)

GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTiposMotdev]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTiposMotdev]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTiposMotdev]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTiposMotdev]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTiposMotdev]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTiposMotdev]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTiposMotdev] @pmIdMotDev VARCHAR(4)
 AS 
 IF @pmIdMotDev IS NULL
  BEGIN
   SELECT M.IdMotDev,M.MotivoDev,M.TipoFact,M.EstadoPed,M.CodEstadoPed,ED.Estado,M.BodegaDev,M.CodBodegaDev,B.Bodega,M.Inactivo
   FROM TiposMotdev AS M LEFT JOIN EstadoDoc AS ED ON M.CodEstadoPed=ED.IdEstado LEFT JOIN Bodegas AS B ON M.CodBodegaDev=B.IdBodega
  END
 ELSE
  BEGIN
   SELECT IdMotDev,MotivoDev,TipoFact,EstadoPed,CodEstadoPed,BodegaDev,CodBodegaDev,Inactivo
   FROM TiposMotdev
   WHERE IdMotDev=@pmIdMotDev
  END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTiposMotdev] @pmIdMotDev VARCHAR(4),@pmMotivoDev VARCHAR(150)
,@pmTipoFact VARCHAR(3),@pmInactivo BIT,@pmEstadoPed BIT,@pmCodEstadoPed VARCHAR(4),@pmBodegaDev BIT,@pmCodBodegaDev VARCHAR(4)
 AS 
 INSERT INTO TiposMotdev (IdMotDev,MotivoDev,TipoFact,Inactivo,EstadoPed,CodEstadoPed,BodegaDev,CodBodegaDev)
 VALUES (@pmIdMotDev,@pmMotivoDev,@pmTipoFact,@pmInactivo,@pmEstadoPed,@pmCodEstadoPed,@pmBodegaDev,@pmCodBodegaDev)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTiposMotdev] @pmIdMotDev VARCHAR(4),@pmMotivoDev VARCHAR(150),@pmTipoFact VARCHAR(3)
,@pmInactivo BIT,@pmEstadoPed BIT,@pmCodEstadoPed VARCHAR(4),@pmBodegaDev BIT,@pmCodBodegaDev VARCHAR(4)
 AS 
 UPDATE TiposMotdev SET MotivoDev=@pmMotivoDev,TipoFact=@pmTipoFact,Inactivo=@pmInactivo
,EstadoPed=@pmEstadoPed,CodEstadoPed=@pmCodEstadoPed,BodegaDev=@pmBodegaDev,CodBodegaDev=@pmCodBodegaDev
 WHERE IdMotDev=@pmIdMotDev
GO
