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

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

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

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


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

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpFondos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpFondos]
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsFondos @pmIdFondo VARCHAR(8),@pmFondo VARCHAR(100),@pmIdClase VARCHAR(4)
,@pmIdTercero VARCHAR(16),@pmCodigoEps VARCHAR(10),@pmCodigoPen VARCHAR(10),@pmCodigoArp VARCHAR(10),@pmInactivo BIT
 AS 
 INSERT INTO Fondos (IdFondo,Fondo,IdClase,IdTercero,CodigoEps,CodigoPen,CodigoArp,Inactivo)
 VALUES (@pmIdFondo,@pmFondo,@pmIdClase,@pmIdTercero,@pmCodigoEps,@pmCodigoPen,@pmCodigoArp,@pmInactivo)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryFondos @pmIdFondo VARCHAR(8)
 AS 
  SELECT IdFondo,Fondo,IdClase,IdTercero,CodigoEps,CodigoPen,CodigoArp,Inactivo
  FROM Fondos
  WHERE IdFondo=@pmIdFondo
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryFondosCla @pmIdClase VARCHAR(4)
 AS 
  SELECT IdFondo,Fondo,IdClase,IdTercero,CodigoEps,CodigoPen,CodigoArp,Inactivo
  FROM Fondos
  WHERE IdClase=@pmIdClase
  ORDER BY IdFondo
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryFondosLta @pmIdClase VARCHAR(4)=Null,@pmInactivo BIT=Null
 AS
  SELECT IdFondo,Fondo,F.IdClase AS CodClase,ClaseFondo,IdTercero,CodigoEps,CodigoPen,CodigoArp,F.Inactivo AS Inctivo
  FROM Fondos AS F INNER JOIN ClaseFon AS C ON F.IdClase=C.IdClase
  WHERE F.IdClase LIKE ISNULL(@pmIdClase,'%') 
  AND (F.Inactivo=ISNULL(@pmInactivo,0) or F.Inactivo=ISNULL(@pmInactivo,1))
  ORDER BY Fondo
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paUpFondos @pmIdFondo VARCHAR(8),@pmFondo VARCHAR(100)
,@pmIdClase VARCHAR(4),@pmIdTercero VARCHAR(16),@pmCodigoEps VARCHAR(10),@pmCodigoPen VARCHAR(10),@pmCodigoArp VARCHAR(10),@pmInactivo BIT
 AS 
 UPDATE Fondos SET Fondo=@pmFondo,IdClase=@pmIdClase,IdTercero=@pmIdTercero
,CodigoEps=@pmCodigoEps,CodigoPen=@pmCodigoPen,CodigoArp=@pmCodigoArp,Inactivo=@pmInactivo
 WHERE IdFondo=@pmIdFondo
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryNomPrmSaludLta @pmIdEmpleado VARCHAR(16)=Null,@pmnAnno INT=Null
 AS 
 SELECT N.IdEmpleado AS CdEmpleado,Apellidos,Nombres,nAnno,VrBase,nMeses,VrPromedio
 FROM Trn_NomPrmSalud AS N INNER JOIN Empleados AS E ON N.IdEmpleado=E.IdEmpleado
 WHERE N.IdEmpleado like ISNULL(@pmIdEmpleado,'%')
 AND (nAnno>=ISNULL(@pmnAnno,0)  AND nAnno<=ISNULL(@pmnAnno,2147483647))
 ORDER BY Apellidos,Nombres,nAnno

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
