ALTER TABLE Sys_TiposDoc ADD IntegraCias BIT DEFAULT(0) NOT NULL
GO
UPDATE  Sys_TiposDoc SET IntegraCias=1 WHERE IdDoc NOT IN ('BON','NOM','PEM','VAL','0')
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTiposDoc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsTiposDoc]
GO

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

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

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomRetIng]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryNomRetIng]
GO
CREATE PROCEDURE paInsTiposDoc @pmIdDoc VARCHAR(3),@pmTipoDoc VARCHAR(50),@pmConsUnico BIT
,@pmLeyenda VARCHAR(250),@pmInactivo BIT,@pmIntegraCias BIT
 AS 
 INSERT INTO Sys_TiposDoc (IdDoc,TipoDoc,ConsUnico,Leyenda,Inactivo,IntegraCias)
 VALUES (@pmIdDoc,@pmTipoDoc,@pmConsUnico,@pmLeyenda,@pmInactivo,@pmIntegraCias)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paUpTiposDoc @pmIdDoc VARCHAR(3),@pmTipoDoc VARCHAR(50),@pmConsUnico BIT
,@pmLeyenda VARCHAR(250),@pmInactivo BIT,@pmIntegraCias BIT
 AS 
 UPDATE Sys_TiposDoc SET TipoDoc=@pmTipoDoc,ConsUnico=@pmConsUnico
,Leyenda=@pmLeyenda,Inactivo=@pmInactivo,IntegraCias=@pmIntegraCias
 WHERE IdDoc=@pmIdDoc
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTiposDoc @pmIdDoc VARCHAR(3)
 AS 
 IF @pmIdDoc IS NULL
  SELECT IdDoc,TipoDoc,ConsUnico,Leyenda,IntegraCias
  FROM Sys_TiposDoc
  WHERE Inactivo=0 
  ORDER BY IdDoc
 ELSE
  SELECT IdDoc,TipoDoc,ConsUnico,Leyenda,IntegraCias,Inactivo
  FROM Sys_TiposDoc
  WHERE IdDoc=@pmIdDoc
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryNomRetIng @pmFecIni SMALLDATETIME,@pmFecFin SMALLDATETIME,@pmIdNom VARCHAR(4)=Null,@pmIdArea VARCHAR(4)=Null
,@pmIdDep VARCHAR(4)=Null,@pmIdInstala VARCHAR(4)=Null,@pmIdCCosto VARCHAR(16)=Null,@pmIdTipCon VARCHAR(4)=Null,@pmIdEmpleado VARCHAR(16)=Null
,@pmNContrato INT=Null,@pmInactivo BIT=Null,@pmEmp_Inactivo BIT=Null
 AS 
 SELECT R.IdEmpleado AS IdEmpldo,Apellidos,Nombres,R.NContrato AS NumContto,MONTH(R.Fecha) AS Nmes,YEAR(R.Fecha) AS NumAnno,R.IdDep AS CodDep,C.IdInstala AS CodInstala
,R.IdCCosto AS CodCcto,R.IdSubCos AS CodSubcc,R.IdNom AS CodNom,R.pVehiculo AS PlacaVeh,R.VrSalario  AS Salbasico,TipoLiq,IdArea,IdTipCon
,D.IdConcepto AS CodConcepto,Concepto
,SUM(Cantidad) AS SCANT,SUM(VrDevngado) AS STotDev,SUM(VrDeducido) AS STotDed,SUM(VrBase) AS SValBase
,SUM(DiasBase) AS DIAS_BASE,SUM(D.DiasNoRem) AS SCanNoRem,SUM(DiasBase-D.DiasNoRem) AS DiasNeto
,SUM(VrBaseLiq) AS SbaseLiq
 FROM Trn_NomRet AS R INNER JOIN  Trn_NomRetDet AS  D ON R.IdEmpleado=D.IdEmpleado AND R.Numero=D.Numero
 INNER JOIN Empleados AS E ON R.IdEmpleado=E.IdEmpleado  INNER JOIN Emp_Contrato AS C ON R.NContrato=C.NContrato
 INNER JOIN NomConceptos AS CN ON D.IdConcepto=CN.IdConcepto
 WHERE Fecha BETWEEN @pmFecIni  AND @pmFecFin  AND ClaseLiq='LIQUDACION'
 AND R.IdNom LIKE ISNULL(@pmIdNom,'%')  AND C.IdArea LIKE ISNULL(@pmIdArea,'%')
 AND R.IdDep LIKE ISNULL(@pmIdDep,'%') AND C.IdInstala LIKE ISNULL(@pmIdInstala,'%')
 AND R.IdCCosto LIKE ISNULL(@pmIdCCosto,'%')   AND IdTipCon LIKE ISNULL(@pmIdTipCon,'%')
 AND R.IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%')  
 AND (R.NContrato>=ISNULL(@pmNContrato,0)  AND R.NContrato<=ISNULL(@pmNContrato,2147483647))
 AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
 AND (E.Inactivo=ISNULL(@pmEmp_Inactivo,0) or E.Inactivo=ISNULL(@pmEmp_Inactivo,1))
 GROUP BY  R.IdEmpleado,Apellidos,Nombres,R.NContrato,MONTH(R.Fecha),YEAR(R.Fecha),R.IdDep,C.IdInstala
,R.IdCCosto,R.IdSubCos,R.IdNom,R.pVehiculo,R.VrSalario,TipoLiq,IdArea,IdTipCon,D.IdConcepto,Concepto
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO