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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryNomLiquidaBan @pmTipoLiq VARCHAR(3),@pmFechaIni SMALLDATETIME=Null,@pmFechaFin SMALLDATETIME=Null
,@pmIdNom VARCHAR(4)=Null,@pmIdDep VARCHAR(4)=Null,@pmIdBanco VARCHAR(4)=Null,@pmIdCCosto VARCHAR(16)=Null
AS
SELECT L.TipoLiq AS Tliq,D.IdEmpleado AS IdEmpldo,Apellidos,Nombres,TipoId,Codigo,IdLocal,E.IdClase AS CdClase,ClaseCuenta,NumCuenta
,E.IdBanco AS CdBanco,Banco,CodEntidad,CdLocCue,e_mail,Telefono,D.NContrato AS NumContrato,IdConcepto
,E.Direccion AS EmpDireccion,TelMovil,Cargo 
,SUM(VrLiquida) AS STOTLIQ,SUM(VrDeducido) AS STOTDED,SUM(VrParcial) AS STOTPARC,SUM(VrLiquida-VrDeducido) AS SNETLIQ
 FROM Trn_NomLiquida AS L INNER JOIN Trn_NomLiqDet AS D ON L.TipoLiq=D.TipoLiq AND L.Numero=D.Numero
 INNER JOIN Empleados AS E ON D.IdEmpleado=E.IdEmpleado
 INNER JOIN  Emp_Contrato AS C ON D.NContrato=C.NContrato
 INNER JOIN ClaseCta AS CT ON E.IdClase=CT.IdClase INNER JOIN Bancos AS B ON E.IdBanco=B.IdBanco
 LEFT JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo
 WHERE  Fecha BETWEEN  @pmFechaIni AND @pmFechaFin AND Anulado=0  AND FPnomina='TRANS' 
 --AND IdConcepto IN ('CES','PRI','INT')
 AND L.TipoLiq LIKE ISNULL(@pmTipoLiq,'%')  AND D.IdNom LIKE ISNULL(@pmIdNom,'%') AND D.IdDep LIKE ISNULL(@pmIdDep,'%')
 AND E.IdBanco LIKE ISNULL(@pmIdBanco,'%') AND D.IdCCosto LIKE ISNULL(@pmIdCCosto,'%') 
 GROUP BY L.TipoLiq,D.IdEmpleado,Apellidos,Nombres,TipoId,Codigo,IdLocal,E.IdClase,ClaseCuenta,NumCuenta
 ,E.IdBanco,Banco,CodEntidad,CdLocCue,e_mail,Telefono,D.NContrato,IdConcepto,E.Direccion,TelMovil,Cargo
 ORDER BY D.IdEmpleado,L.TipoLiq
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

