SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[paQryNominaApo] @pmMes VARCHAR(6),@pmIdPeriodo VARCHAR(8)=Null,@pmIdInstala VARCHAR(4)=Null,@pmIdNom VARCHAR(4)=Null
,@pmIdArea VARCHAR(4)=Null,@pmIdDep VARCHAR(4)=Null,@pmEsRetiro BIT=Null,@pmIdConcepto VARCHAR(4)=Null,@pmCodFondo VARCHAR(8)=Null,@pmIdCia CHAR(2)=Null
AS 
 SELECT N.KeyRegistro AS KeyReg,N.IdEmpleado AS IdEmpldo,Apellidos,Nombres,N.NContrato AS NumContto,FecInicial,FecFinal,N.IdDep AS CodDep,N.IdInstala AS CodInstala,N.IdCCosto AS CodCcto
,N.IdSubCos AS CodSubcc,N.IdNom AS CodNom,N.pVehiculo AS PlacaVeh,N.VrSalario AS Salbasico,EsRetiro,NLiqRet,IdFonPen,IdFonEps,IdFonArp,IdFonCes,IdCajaCom,FecIngreso,FecRetiro,IdArea,C.IdClase AS CodClaRie
,CR.Tarifa AS TarRie,TipoTrabj,VrAuxTrans,VrUpc,SalIntegral,SalMinimo,C.Inactivo AS Con_Inctivo,D.IdConcepto AS CodConcepto,Concepto,ClaseCon,CN.Tarifa AS TarifDft,ClaseLiq,CantDevg,CantDed,VrTotDevg,VrTotDed,VrOrigen,D.Tarifa AS Tarfa
,DiasCalc,DiasNov,DiasCalc-DiasNov AS DiasNeto,CodFondo,IdCot,SubTipoCot,DenyPEN,DenyEPS,DenyARP,DenyCaj,DenyDcto
 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 ClaseRie AS CR ON C.IdClase=CR.IdClase  INNER JOIN NomConceptos AS CN ON D.IdConcepto=CN.IdConcepto
 WHERE  SUBSTRING(N.IdPeriodo,1,6)=@pmMes AND NLiqRet>=0 AND D.IdConcepto IN ('PEN','RIE','SAL','FSP','FSU','SEN','ICB','COM','DCP','DCS','AVP','AVA')
 AND N.IdPeriodo LIKE ISNULL(@pmIdPeriodo,'%') AND N.IdInstala LIKE ISNULL(@pmIdInstala,'%') AND N.IdNom LIKE ISNULL(@pmIdNom,'%')
 AND IdArea LIKE ISNULL(@pmIdArea,'%') AND N.IdDep LIKE ISNULL(@pmIdDep,'%')   AND D.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND IdCia LIKE ISNULL(@pmIdCia,'%%')  AND CodFondo LIKE ISNULL(@pmCodFondo,'%')
 AND (EsRetiro=ISNULL(@pmEsRetiro,0) or EsRetiro=ISNULL(@pmEsRetiro,1))
 UNION ALL
 SELECT D.IdPeriodo,V.IdEmpleado,Apellidos,Nombres,V.NContrato,V.FecInicial,V.FecFinal,C.IdDep,C.IdInstala,C.IdCCosto,C.IdSubCos,C.IdNom,V.pVehiculo
,V.VrSalario,0,0,IdFonPen,E.IdFonEps,IdFonArp,IdFonCes,IdCajaCom,C.FecIngreso,C.FecRetiro,C.IdArea,C.IdClase,0,C.TipoTrabj,C.VrAuxTrans,C.VrUpc
,C.SalIntegral,C.SalMinimo,C.Inactivo,D.IdConcepto,Concepto,ClaseCon,CN.Tarifa,'NOMINA',0,D.Cantidad,D.VrDevgado,D.VrDeducido,D.VrBaseLiq,D.Tarifa
,D.Cantidad,0,D.Cantidad,E.IdFonEps,C.IdCot,C.SubTipoCot,C.DenyPEN,C.DenyEPS,C.DenyARP,C.DenyCaj,C.DenyDcto
 FROM Trn_NomVac AS V INNER JOIN Trn_NomVacDet AS D ON V.IdEmpleado=D.IdEmpleado AND V.Numero=D.Numero
 INNER JOIN Empleados AS E ON V.IdEmpleado=E.IdEmpleado INNER JOIN Emp_Contrato AS C ON V.NContrato=C.NContrato
 INNER JOIN NomConceptos AS CN ON D.IdConcepto=CN.IdConcepto
 WHERE SUBSTRING(D.IdPeriodo,1,6)=@pmMes AND D.IdConcepto IN ('DCP','DCS','FSP','FSU') AND V.Anulado=0
 AND D.IdPeriodo LIKE ISNULL(@pmIdPeriodo,'%') AND C.IdInstala LIKE ISNULL(@pmIdInstala,'%') AND C.IdNom LIKE ISNULL(@pmIdNom,'%')
 AND C.IdArea LIKE ISNULL(@pmIdArea,'%') AND C.IdDep LIKE ISNULL(@pmIdDep,'%')
 AND C.IdCia LIKE ISNULL(@pmIdCia,'%%')
 ORDER BY  N.IdEmpleado,N.NContrato,D.IdConcepto