if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNominaBanlv]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNominaBanlv]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNominaBanl]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNominaBanl]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNominaBanNv]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNominaBanNv]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNominaBanlv] @pmMes VARCHAR(6),@pmIdPeriodo VARCHAR(8),@pmIdNom VARCHAR(4)=Null
,@pmIdDep VARCHAR(4)=Null,@pmIdBanco VARCHAR(4)=Null,@pmInactivo BIT=Null,@pmFechaIni SMALLDATETIME=Null,@pmFechaFin SMALLDATETIME=Null
 AS 
--Nomina + liq prima+intereses+vac
 SELECT N.IdEmpleado AS IdEmpldo,Apellidos,Nombres,TipoId,Codigo,IdLocal,E.IdClase AS CdClase,ClaseCuenta,NumCuenta
,E.IdBanco AS CdBanco,Banco,CodEntidad,CdLocCue,E.Direccion AS EmpDireccion,TelMovil,e_mail,Telefono,N.NContrato AS NumContrato,TipoLiq,Cargo,'NOM' AS TipProc,'NOMI' AS CodConcepto
,SUM(VrTotDevg) AS STOTDEV,SUM(VrTotDed) AS STOTDED,SUM(VrOrigen) AS SVALBAS,SUM(VrTotDevg-VrTotDed) AS SNETLIQ
 FROM Trn_Nomina AS N INNER JOIN Trn_NomDetalle AS D ON N.IdPeriodo=D.IdPeriodo AND N.KeyRegistro=D.KeyRegistro
 INNER JOIN Empleados AS E ON N.IdEmpleado=E.IdEmpleado INNER JOIN  Emp_Contrato AS C ON N.NContrato=C.NContrato
 INNER JOIN Bancos AS B ON E.IdBanco=B.IdBanco INNER JOIN ClaseCta AS CTA ON E.IdClase=CTA.IdClase
 LEFT JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo
 WHERE SUBSTRING(N.IdPeriodo,1,6)=@pmMes  AND FPnomina='TRANS' AND ClaseLiq IN ('NOMINA','NOVEDAD')
 AND N.IdPeriodo LIKE ISNULL(@pmIdPeriodo,'%')
 AND N.IdNom LIKE ISNULL(@pmIdNom,'%') AND N.IdDep LIKE ISNULL(@pmIdDep,'%')
 AND E.IdBanco LIKE ISNULL(@pmIdBanco,'%')
 AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
 GROUP BY  N.IdEmpleado,Apellidos,Nombres,TipoId,Codigo,IdLocal,E.IdClase,ClaseCuenta,NumCuenta
,E.IdBanco,Banco,CodEntidad,CdLocCue,E.Direccion,TelMovil,e_mail,Telefono,N.NContrato,TipoLiq,Cargo
UNION ALL
SELECT D.IdEmpleado,Apellidos,Nombres,TipoId,Codigo,IdLocal,E.IdClase,ClaseCuenta,NumCuenta
,E.IdBanco,Banco,CodEntidad,CdLocCue,E.Direccion,TelMovil,e_mail,Telefono,D.NContrato,C.TipoLiquida,Cargo,L.TipoLiq,D.IdConcepto
,SUM(VrLiquida),SUM(VrDeducido),SUM(VrParcial),SUM(VrLiquida-VrDeducido) 
 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 L.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND ((L.TipoLiq='CES' AND Semstre=0) OR L.TipoLiq='PRI') 
 AND L.Anulado=0 AND FPnomina='TRANS'
 AND D.IdNom LIKE ISNULL(@pmIdNom,'%') AND D.IdDep LIKE ISNULL(@pmIdDep,'%')
 AND E.IdBanco LIKE ISNULL(@pmIdBanco,'%')
 AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
GROUP BY D.IdEmpleado,Apellidos,Nombres,TipoId,Codigo,IdLocal,E.IdClase,ClaseCuenta,NumCuenta
,E.IdBanco,Banco,CodEntidad,CdLocCue,E.Direccion,TelMovil,e_mail,Telefono,D.NContrato,C.TipoLiquida,Cargo,L.TipoLiq,D.IdConcepto
--vacaciones
UNION ALL
SELECT V.IdEmpleado,Apellidos,Nombres,TipoId,Codigo,IdLocal,E.IdClase,ClaseCuenta,NumCuenta
,E.IdBanco,Banco,CodEntidad,CdLocCue,E.Direccion,TelMovil,e_mail,Telefono,V.NContrato,C.TipoLiquida,Cargo,'VAC','VA'
,VrLiquida,VrTotDed,VrRemndo,(VrLiquida+VrRemndo)-VrTotDed
 FROM Trn_NomVac AS V INNER JOIN Empleados AS E ON V.IdEmpleado=E.IdEmpleado  
 INNER JOIN Emp_Contrato AS C ON V.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 V.Fecha BETWEEN  @pmFechaIni AND @pmFechaFin AND Anulado=0 AND FPnomina='TRANS' 
 AND C.IdNom LIKE ISNULL(@pmIdNom,'%') AND C.IdDep LIKE ISNULL(@pmIdDep,'%')
 AND E.IdBanco LIKE ISNULL(@pmIdBanco,'%')
 AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
ORDER BY Apellidos,Nombres,N.IdEmpleado
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNominaBanl] @pmMes VARCHAR(6),@pmIdPeriodo VARCHAR(8),@pmIdNom VARCHAR(4)=Null
,@pmIdDep VARCHAR(4)=Null,@pmIdBanco VARCHAR(4)=Null,@pmInactivo BIT=Null,@pmFechaIni SMALLDATETIME=Null,@pmFechaFin SMALLDATETIME=Null
 AS 
--Nomina + liq prima+intereses
 SELECT N.IdEmpleado AS IdEmpldo,Apellidos,Nombres,TipoId,Codigo,IdLocal,E.IdClase AS CdClase,ClaseCuenta,NumCuenta
,E.IdBanco AS CdBanco,Banco,CodEntidad,CdLocCue,E.Direccion AS EmpDireccion,TelMovil,e_mail,Telefono,N.NContrato AS NumContrato,TipoLiq,Cargo,'NOM' AS TipProc,'NOMI' AS CodConcepto
,SUM(VrTotDevg) AS STOTDEV,SUM(VrTotDed) AS STOTDED,SUM(VrOrigen) AS SVALBAS,SUM(VrTotDevg-VrTotDed) AS SNETLIQ
 FROM Trn_Nomina AS N INNER JOIN Trn_NomDetalle AS D ON N.IdPeriodo=D.IdPeriodo AND N.KeyRegistro=D.KeyRegistro
 INNER JOIN Empleados AS E ON N.IdEmpleado=E.IdEmpleado INNER JOIN  Emp_Contrato AS C ON N.NContrato=C.NContrato
 INNER JOIN Bancos AS B ON E.IdBanco=B.IdBanco INNER JOIN ClaseCta AS CTA ON E.IdClase=CTA.IdClase
 LEFT JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo
 WHERE SUBSTRING(N.IdPeriodo,1,6)=@pmMes  AND FPnomina='TRANS' AND ClaseLiq IN ('NOMINA','NOVEDAD')
 AND N.IdPeriodo LIKE ISNULL(@pmIdPeriodo,'%')
 AND N.IdNom LIKE ISNULL(@pmIdNom,'%') AND N.IdDep LIKE ISNULL(@pmIdDep,'%')
 AND E.IdBanco LIKE ISNULL(@pmIdBanco,'%')
 AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
 GROUP BY  N.IdEmpleado,Apellidos,Nombres,TipoId,Codigo,IdLocal,E.IdClase,ClaseCuenta,NumCuenta
,E.IdBanco,Banco,CodEntidad,CdLocCue,E.Direccion,TelMovil,e_mail,Telefono,N.NContrato,TipoLiq,Cargo
UNION ALL
SELECT D.IdEmpleado,Apellidos,Nombres,TipoId,Codigo,IdLocal,E.IdClase,ClaseCuenta,NumCuenta
,E.IdBanco,Banco,CodEntidad,CdLocCue,E.Direccion,TelMovil,e_mail,Telefono,D.NContrato,C.TipoLiquida,Cargo,L.TipoLiq,D.IdConcepto
,SUM(VrLiquida),SUM(VrDeducido),SUM(VrParcial),SUM(VrLiquida-VrDeducido) 
 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 L.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND ((L.TipoLiq='CES' AND Semstre=0) OR L.TipoLiq='PRI') 
 AND L.Anulado=0 AND FPnomina='TRANS'
 AND D.IdNom LIKE ISNULL(@pmIdNom,'%') AND D.IdDep LIKE ISNULL(@pmIdDep,'%')
 AND E.IdBanco LIKE ISNULL(@pmIdBanco,'%')
GROUP BY D.IdEmpleado,Apellidos,Nombres,TipoId,Codigo,IdLocal,E.IdClase,ClaseCuenta,NumCuenta
,E.IdBanco,Banco,CodEntidad,CdLocCue,E.Direccion,TelMovil,e_mail,Telefono,D.NContrato,C.TipoLiquida,Cargo,L.TipoLiq,D.IdConcepto
ORDER BY Apellidos,Nombres,N.IdEmpleado
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNominaBanNv] @pmMes VARCHAR(6),@pmIdPeriodo VARCHAR(8),@pmIdNom VARCHAR(4)=Null
,@pmIdDep VARCHAR(4)=Null,@pmIdBanco VARCHAR(4)=Null,@pmInactivo BIT=Null,@pmFechaIni SMALLDATETIME=Null,@pmFechaFin SMALLDATETIME=Null
 AS 
--Nomina +vacaciones
 SELECT N.IdEmpleado AS IdEmpldo,Apellidos,Nombres,TipoId,Codigo,IdLocal,E.IdClase AS CdClase,ClaseCuenta,NumCuenta
,E.IdBanco AS CdBanco,Banco,CodEntidad,CdLocCue,E.Direccion AS EmpDireccion,TelMovil,e_mail,Telefono,N.NContrato AS NumContrato,TipoLiq,Cargo,'NOM' AS TipProc,'NOMI' AS CodConcepto
,SUM(VrTotDevg) AS STOTDEV,SUM(VrTotDed) AS STOTDED,SUM(VrOrigen) AS SVALBAS,SUM(VrTotDevg-VrTotDed) AS SNETLIQ
 FROM Trn_Nomina AS N INNER JOIN Trn_NomDetalle AS D ON N.IdPeriodo=D.IdPeriodo AND N.KeyRegistro=D.KeyRegistro
 INNER JOIN Empleados AS E ON N.IdEmpleado=E.IdEmpleado INNER JOIN  Emp_Contrato AS C ON N.NContrato=C.NContrato
 INNER JOIN Bancos AS B ON E.IdBanco=B.IdBanco INNER JOIN ClaseCta AS CTA ON E.IdClase=CTA.IdClase
 LEFT JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo
 WHERE SUBSTRING(N.IdPeriodo,1,6)=@pmMes  AND FPnomina='TRANS' AND ClaseLiq IN ('NOMINA','NOVEDAD')
 AND N.IdPeriodo LIKE ISNULL(@pmIdPeriodo,'%')
 AND N.IdNom LIKE ISNULL(@pmIdNom,'%') AND N.IdDep LIKE ISNULL(@pmIdDep,'%')
 AND E.IdBanco LIKE ISNULL(@pmIdBanco,'%')
 AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
 GROUP BY  N.IdEmpleado,Apellidos,Nombres,TipoId,Codigo,IdLocal,E.IdClase,ClaseCuenta,NumCuenta
,E.IdBanco,Banco,CodEntidad,CdLocCue,E.Direccion,TelMovil,e_mail,Telefono,N.NContrato,TipoLiq,Cargo
--vacaciones
UNION ALL
SELECT V.IdEmpleado,Apellidos,Nombres,TipoId,Codigo,IdLocal,E.IdClase,ClaseCuenta,NumCuenta
,E.IdBanco,Banco,CodEntidad,CdLocCue,E.Direccion,TelMovil,e_mail,Telefono,V.NContrato,C.TipoLiquida,Cargo,'VAC','VA'
,VrLiquida,VrTotDed,VrRemndo,(VrLiquida+VrRemndo)-VrTotDed
 FROM Trn_NomVac AS V INNER JOIN Empleados AS E ON V.IdEmpleado=E.IdEmpleado  
 INNER JOIN Emp_Contrato AS C ON V.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 V.Fecha BETWEEN  @pmFechaIni AND @pmFechaFin AND Anulado=0 AND FPnomina='TRANS' 
 AND C.IdNom LIKE ISNULL(@pmIdNom,'%') AND C.IdDep LIKE ISNULL(@pmIdDep,'%')
 AND E.IdBanco LIKE ISNULL(@pmIdBanco,'%')
 AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
ORDER BY Apellidos,Nombres,N.IdEmpleado
GO