if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paDelNomPrendas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paDelNomPrendas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomDotacion]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNomDotacion]
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].[paInsNomPrendas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNomPrendas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomDotacion]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomDotacion]
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
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].[paUpNomDotacion]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpNomDotacion]
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

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)
 AS 
 INSERT INTO Trn_NomDotacionDet (IdCia,Numero,Item,IdEmpleado,NContrato,IdPrend,Descripcion,Cantidad,Unidad)
 VALUES (@pmIdCia,@pmNumero,@pmItem,@pmIdEmpleado,@pmNContrato,@pmIdPrend,@pmDescripcion,@pmCantidad,@pmUnidad)
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
,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
,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].[paDelNomPrendas] @pmIdPrend VARCHAR(4)
 AS 
 DELETE FROM NomPrendas WHERE IdPrend=@pmIdPrend
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsNomPrendas] @pmIdPrend VARCHAR(4),@pmDescPrenda VARCHAR(100),@pmTipoPrenda VARCHAR(10),@pmInactivo BIT
 AS 
 INSERT INTO NomPrendas (IdPrend,DescPrenda,TipoPrenda,Inactivo)
 VALUES (@pmIdPrend,@pmDescPrenda,@pmTipoPrenda,@pmInactivo)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpNomPrendas] @pmIdPrend VARCHAR(4),@pmDescPrenda VARCHAR(100),@pmTipoPrenda VARCHAR(10),@pmInactivo BIT
 AS 
 UPDATE NomPrendas SET DescPrenda=@pmDescPrenda,TipoPrenda=@pmTipoPrenda,Inactivo=@pmInactivo
 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,Inactivo
 FROM NomPrendas WHERE IdPrend=@pmIdPrend
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsNomDotacion] @pmIdCia CHAR(2),@pmNumero INT,@pmFecha SMALLDATETIME,@pmTipoDotac VARCHAR(10),@pmTipoEntrega INT,@pmPlazoEntrega INT
,@pmFecVence SMALLDATETIME,@pmTipoReg INT,@pmAnulado BIT,@pmObservacion VARCHAR(1000),@pmCritFiltros VARCHAR(250),@pmFechaCrea SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_NomDotacion (IdCia,Numero,Fecha,TipoDotac,TipoEntrega,PlazoEntrega,FecVence,TipoReg,Anulado,FechaCrea,IdUsuario,Observacion,CritFiltros)
 VALUES (@pmIdCia,@pmNumero,@pmFecha,@pmTipoDotac,@pmTipoEntrega,@pmPlazoEntrega,@pmFecVence,@pmTipoReg,@pmAnulado,@pmFechaCrea,@pmIdUsuario,@pmObservacion,@pmCritFiltros)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpNomDotacion] @pmIdCia CHAR(2),@pmNumero INT,@pmFecha SMALLDATETIME,@pmTipoDotac VARCHAR(10),@pmTipoEntrega INT,@pmPlazoEntrega INT
,@pmFecVence SMALLDATETIME,@pmTipoReg INT,@pmAnulado BIT,@pmObservacion VARCHAR(1000),@pmCritFiltros VARCHAR(250)
 AS 
 UPDATE Trn_NomDotacion SET Fecha=@pmFecha,TipoDotac=@pmTipoDotac,TipoEntrega=@pmTipoEntrega,PlazoEntrega=@pmPlazoEntrega,FecVence=@pmFecVence,TipoReg=@pmTipoReg
,Anulado=@pmAnulado,Observacion=@pmObservacion,CritFiltros=@pmCritFiltros
 WHERE IdCia=@pmIdCia AND Numero=@pmNumero
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomDotacion] @pmIdCia CHAR(2),@pmNumero INT
 AS 
 SELECT IdCia,Numero,Fecha,TipoDotac,TipoEntrega,PlazoEntrega,FecVence,TipoReg,Anulado,FechaCrea,IdUsuario,Observacion,CritFiltros
 FROM Trn_NomDotacion
 WHERE IdCia=@pmIdCia AND Numero=@pmNumero
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQrytm_Dotacion] @pmtmEst CHAR(2)
 AS 
 SELECT tmIdEmpleado,Apellidos,Nombres,tmNContrato,tmTipo,tmFecha,tmDiasPlazo,tmFecVence,tmIdCia,CI.Compania,tmNumReg,tmDetalle
,FecIngreso,Indefinido,C.IdNom AS CdNom,TipoNomina,C.IdTipCon AS CdTipCon,TipoContrato,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.TipoLiquida AS TipoLiq,DchDotacion,VrSalario,VrAuxTrans,SalMinimo,SalIntegral
,E.Codigo,e_mail,E.Sexo,E.IdProf AS CodProf,Profesion,FecUltDot,TallaCam,TallaPan,TallaZap
FROM tm_Dotacion AS T INNER JOIN Empleados AS E ON T.tmIdEmpleado=E.IdEmpleado INNER JOIN Profesiones AS P ON E.IdProf=P.IdProf
INNER JOIN Emp_Contrato AS C ON T.tmNContrato=C.NContrato INNER JOIN TiposCon AS TC ON C.IdTipCon=TC.IdTipCon
INNER JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo INNER JOIN Dependencias AS DP ON C.IdDep=DP.IdDep
INNER JOIN Areas AS AR ON C.IdArea=AR.IdArea INNER JOIN Instalaciones AS I ON C.IdInstala=I.IdInstala
INNER JOIN Companias AS CN ON C.IdCia=CN.IdCia INNER JOIN TiposNom AS TN ON C.IdNom=TN.IdNom
LEFT JOIN Companias AS CI ON T.tmIdCia=CI.IdCia
WHERE tmEst=@pmtmEst
GO