ALTER TABLE Trn_NomDotacionDet ADD VrUnitario MONEY DEFAULT(0) NOT NULL
GO
ALTER TABLE NomPrendas ADD VrCostoUnd MONEY DEFAULT(0) NOT NULL
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomPrendas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomPrendas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpNomPrendas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpNomPrendas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomPrendas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNomPrendas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomDotacionDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNomDotacionDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomDotacion_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomDotacion_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomDotacionLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomDotacionLta]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomDotacionLta] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT DT.IdCia AS CdCia,CI.Compania,DT.Numero AS NumReg,DT.Fecha AS FechaEnt,TipoDotac,TipoEntrega,PlazoEntrega,FecVence
,TipoReg,DT.Anulado,FechaCrea,DT.IdUsuario AS CdUsuario,Usuario,DT.Observacion AS Observ,CritFiltros
,Item,D.IdEmpleado AS CdEmpleado,Apellidos,Nombres,D.NContrato AS NumCont,D.IdPrend AS CdPrend,DescPrenda,D.Descripcion,Cantidad,Unidad,D.VrUnitario
,C.IdTipCon AS CdTipCon,TipoContrato,FecIngreso,C.IdArea AS CdArea,Area,C.IdDep AS CdDep,Dependencia,C.IdInstala AS IdInstla,Instlacion,C.IdCargo AS CodCargo,Cargo
,C.IdCia AS ConCdCia,CN.Compania AS ConCompania,C.IdNom AS CdNom,TipoNomina,C.TipoLiquida AS TipoLiq,DchDotacion,VrSalario,VrAuxTrans,SalMinimo,SalIntegral
,E.Codigo,e_mail,E.Sexo,E.IdProf AS CodProf,Profesion,FecUltDot
 FROM Trn_NomDotacion AS DT INNER JOIN Trn_NomDotacionDet AS D ON DT.IdCia=D.IdCia AND DT.Numero=D.Numero
 INNER JOIN Companias AS CI ON DT.IdCia=CI.IdCia INNER JOIN adm_Usuarios AS U ON DT.IdUsuario=U.IdUsuario
 INNER JOIN NomPrendas AS PN ON D.IdPrend=PN.IdPrend
 INNER JOIN Empleados AS E ON D.IdEmpleado=E.IdEmpleado INNER JOIN Profesiones AS P ON E.IdProf=P.IdProf 
 LEFT JOIN Emp_Contrato AS C ON D.NContrato=C.NContrato LEFT JOIN TiposCon AS TC ON C.IdTipCon=TC.IdTipCon
 LEFT JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo LEFT JOIN Dependencias AS DP ON C.IdDep=DP.IdDep
 LEFT JOIN Areas AS AR ON C.IdArea=AR.IdArea LEFT JOIN Instalaciones AS I ON C.IdInstala=I.IdInstala
 LEFT JOIN Companias AS CN ON C.IdCia=CN.IdCia  LEFT JOIN TiposNom AS TN ON C.IdNom=TN.IdNom
 WHERE DT.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND DT.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomDotacion_Cr] @pmIdCia CHAR(2),@pmNumeroIni INT,@pmNumeroFin INT
 AS 
 SELECT DT.IdCia AS CdCia,CI.Compania,DT.Numero AS NumReg,DT.Fecha AS FechaEnt,TipoDotac,TipoEntrega,PlazoEntrega,FecVence
,TipoReg,DT.Anulado,FechaCrea,DT.IdUsuario AS CdUsuario,Usuario,DT.Observacion AS Observ,CritFiltros
,Item,D.IdEmpleado AS CdEmpleado,Apellidos,Nombres,D.NContrato AS NumCont,D.IdPrend AS CdPrend,DescPrenda,D.Descripcion,Cantidad,Unidad,D.VrUnitario
,C.IdTipCon AS CdTipCon,TipoContrato,FecIngreso,C.IdArea AS CdArea,Area,C.IdDep AS CdDep,Dependencia,C.IdInstala AS IdInstla,Instlacion,C.IdCargo AS CodCargo,Cargo
,C.IdCia AS ConCdCia,CN.Compania AS ConCompania,C.IdNom AS CdNom,TipoNomina,C.TipoLiquida AS TipoLiq,DchDotacion,VrSalario,VrAuxTrans,SalMinimo,SalIntegral
,E.Codigo,e_mail,E.Sexo,E.IdProf AS CodProf,Profesion,FecUltDot
 FROM Trn_NomDotacion AS DT INNER JOIN Trn_NomDotacionDet AS D ON DT.IdCia=D.IdCia AND DT.Numero=D.Numero
 INNER JOIN Companias AS CI ON DT.IdCia=CI.IdCia INNER JOIN adm_Usuarios AS U ON DT.IdUsuario=U.IdUsuario
 INNER JOIN NomPrendas AS PN ON D.IdPrend=PN.IdPrend
 INNER JOIN Empleados AS E ON D.IdEmpleado=E.IdEmpleado INNER JOIN Profesiones AS P ON E.IdProf=P.IdProf 
 LEFT JOIN Emp_Contrato AS C ON D.NContrato=C.NContrato LEFT JOIN TiposCon AS TC ON C.IdTipCon=TC.IdTipCon
 LEFT JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo LEFT JOIN Dependencias AS DP ON C.IdDep=DP.IdDep
 LEFT JOIN Areas AS AR ON C.IdArea=AR.IdArea LEFT JOIN Instalaciones AS I ON C.IdInstala=I.IdInstala
 LEFT JOIN Companias AS CN ON C.IdCia=CN.IdCia  LEFT JOIN TiposNom AS TN ON C.IdNom=TN.IdNom
 WHERE DT.IdCia=@pmIdCia AND DT.Numero BETWEEN @pmNumeroIni AND @pmNumeroFin
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsNomPrendas] @pmIdPrend VARCHAR(4),@pmDescPrenda VARCHAR(100),@pmTipoPrenda VARCHAR(10),@pmVrCostoUnd MONEY,@pmInactivo BIT
 AS 
 INSERT INTO NomPrendas (IdPrend,DescPrenda,TipoPrenda,Inactivo,VrCostoUnd)
 VALUES (@pmIdPrend,@pmDescPrenda,@pmTipoPrenda,@pmInactivo,@pmVrCostoUnd)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpNomPrendas] @pmIdPrend VARCHAR(4),@pmDescPrenda VARCHAR(100),@pmTipoPrenda VARCHAR(10),@pmVrCostoUnd MONEY,@pmInactivo BIT
 AS 
 UPDATE NomPrendas SET DescPrenda=@pmDescPrenda,TipoPrenda=@pmTipoPrenda,Inactivo=@pmInactivo,VrCostoUnd=@pmVrCostoUnd
 WHERE IdPrend=@pmIdPrend
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomPrendas] @pmIdPrend VARCHAR(4)
 AS 
 SELECT IdPrend,DescPrenda,TipoPrenda,VrCostoUnd,Inactivo
 FROM NomPrendas WHERE IdPrend=@pmIdPrend
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsNomDotacionDet] @pmIdCia CHAR(2),@pmNumero INT,@pmItem INT,@pmIdEmpleado VARCHAR(16),@pmNContrato INT,@pmIdPrend VARCHAR(4)
,@pmDescripcion VARCHAR(250),@pmCantidad DECIMAL(14,4),@pmUnidad VARCHAR(10),@pmVrUnitario MONEY
 AS 
 INSERT INTO Trn_NomDotacionDet (IdCia,Numero,Item,IdEmpleado,NContrato,IdPrend,Descripcion,Cantidad,Unidad,VrUnitario)
 VALUES (@pmIdCia,@pmNumero,@pmItem,@pmIdEmpleado,@pmNContrato,@pmIdPrend,@pmDescripcion,@pmCantidad,@pmUnidad,@pmVrUnitario)
GO