ALTER PROCEDURE [dbo].[paQrytm_Plasueldos] @pmtmIdPeriodo VARCHAR(8),@pmMes VARCHAR(6),@pmtmKeyRegistro VARCHAR(30)=Null,@pmIdEmpleado VARCHAR(16)=Null
 AS 
 SELECT N.IdEmpleado AS IdEmpldo,Apellidos,Nombres,N.VrSalario AS VrSalBasico,tmDias,tmVrBasico,tmVrAuxtrans,tmVrHextras,tmVrRecargos,tmVrComision,tmVrIncapac,tmVrLicencia
,tmVrVacLiquida,tmVrOtrosDev,VrDevengado+tmVrVacLiquida AS Devengado,tmVrDctoPen,tmVrDctoSal,tmVrDctoFsp,tmVrRetencion,tmVrPrestmos,tmVrSanciones,tmVrOtrosDed,tmVrVacPen,tmVrVacSal,tmVrVacFsp,tmVrVacOtr
,VrDeducido+tmVrVacPen+tmVrVacSal+tmVrVacFsp+tmVrVacOtr AS Deducido
,(VrDevengado+tmVrVacLiquida) -(VrDeducido+tmVrVacPen+tmVrVacSal+tmVrVacFsp+tmVrVacOtr) AS VrNetoPagar,tmVrVacacnes,tmObserv,tmIdPeriodo,FecInicial,FecFinal,tmKeyRegistro
--datos nomina
,N.IdDep AS CodDep,Dependencia,N.IdInstala AS CodInst,Instlacion,N.IdCCosto AS CodCcto,CCosto,N.IdSubCos AS CodSubcen,SubCosto,N.IdNom AS CodNom,TipoNomina,Asistencia,NoDevenga
,TipoLiq,N.pVehiculo AS PlacaVeh,TipCom,Comprobante,IdCiaCom,N.Observacion AS Observ,N.IdEstado AS Nom_IdEstdo,EN.Estado AS Nom_Estado,EsRetiro,NLiqRet,Cantidad,CantNoRem
,TimeSys,N.FecUpdate AS FecUpd,N.IdUsuario AS IdUsuari,Usuario
--datos del contrato
,N.NContrato AS NumContrato,C.IdTipCon AS CodTipCon,TipoContrato,FecIngreso,FecRetiro,Indefinido,C.IdCargo AS CodCargo,Cargo,C.IdArea AS CodArea,Area,C.IdClase AS CodClaRie,ClaseRiesgo,CR.Tarifa AS TarifaRie
,VrAuxTrans,SalMinimo,SalIntegral,FPnomina,ATFinMes,SegFinMes,DchDotacion,DchCafeteria,C.IdEstado AS Con_IdEstdo,ECO.Estado AS Con_Estado,C.Inactivo AS Con_inactivo,C.IdJornada AS CodJnada,Jornada,TipoTrabj
--datos del empleado
,Codigo,IdLugarCed,Localidad,E.Direccion AS Dirccion,Telefono,TelMovil,e_mail,E.IdProf AS CodProf,Profesion,E.IdClase AS CodClaCta,ClaseCuenta,NumCuenta,E.IdBanco AS CodBanco,Banco,IdFonPen,FP.Fondo AS FondoPen
,IdFonEps,FS.Fondo AS FondoEps,IdFonArp,FR.Fondo AS FondoArp,IdCajaCom,CC.Fondo AS CajaComp,EsCondtor,FecNacmto,Licencia,CatLicencia,VigLicencia,E.IdLugar AS CodLugar,LugarLic,TallaCam,TallaPan,TallaZap,E.Inactivo AS Emp_Inactvo
,NumVeh,TipoAdmon,VehPropio
--sumas del concepto
,ND.VrToDED,ND.VrToDEV,ISNULL(NDE.DDEV,0) AS DiaDev,ISNULL(NDE.DDED,0)AS DiaDed
 FROM tm_Plasueldos AS PS INNER JOIN Trn_Nomina AS N ON PS.tmIdPeriodo=N.IdPeriodo AND PS.tmKeyRegistro=N.KeyRegistro
 INNER JOIN Empleados AS E ON N.IdEmpleado=E.IdEmpleado INNER JOIN  Emp_Contrato AS C ON N.NContrato=C.NContrato
 INNER JOIN Dependencias AS DP ON N.IdDep=DP.IdDep  INNER JOIN Instalaciones AS I ON N.IdInstala=I.IdInstala
 INNER JOIN CentroCosto AS CO ON N.IdCCosto=CO.IdCCosto  INNER JOIN SubCentros AS SC ON N.IdSubCos=SC.IdSubCos
 INNER JOIN TiposNom AS TN ON N.IdNom=TN.IdNom INNER JOIN EstadoDoc AS EN ON N.IdEstado=EN.IdEstado 
 INNER JOIN adm_Usuarios AS U ON N.IdUsuario=U.IdUsuario INNER JOIN Localidades AS L ON E.IdLugarCed=L.IdLocal
 INNER JOIN Profesiones AS P ON E.IdProf=P.IdProf   INNER JOIN ClaseCta AS CT ON E.IdClase=CT.IdClase
 INNER JOIN Bancos AS B ON E.IdBanco=B.IdBanco  INNER JOIN Fondos AS FP ON E.IdFonPen=FP.IdFondo
 INNER JOIN Fondos AS FS ON E.IdFonEps=FS.IdFondo  INNER JOIN Fondos AS FR ON E.IdFonArp=FR.IdFondo
 INNER JOIN Fondos AS CC ON E.IdCajaCom=CC.IdFondo INNER JOIN ExpLicencias AS EL ON E.IdLugar=EL.IdLugar
 INNER JOIN TiposCon AS TC ON C.IdTipCon=TC.IdTipCon  INNER JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo
 INNER JOIN Areas AS AR ON C.IdArea=AR.IdArea INNER JOIN ClaseRie AS CR ON C.IdClase=CR.IdClase
 INNER JOIN Jornadas AS J ON C.IdJornada=J.IdJornada  INNER JOIN EstadoDoc AS ECO ON C.IdEstado=ECO.IdEstado
 LEFT JOIN (SELECT IdPeriodo,KeyRegistro,SUM(VrTotDed)AS VrToDED,SUM(VrTotDevg)AS VrToDEV  
 FROM Trn_NomDetalle where IdConcepto in ('avp','cmv')GROUP BY IdPeriodo,KeyRegistro) AS ND ON PS.tmIdPeriodo=ND.IdPeriodo AND PS.tmKeyRegistro=ND.KeyRegistro
 LEFT JOIN (select IdPeriodo,KeyRegistro,SUM(CANTDEVG) AS DDEV,SUM(CantDed) AS DDED from Trn_NomDetalle where IdConcepto in('VAC','IRP','IGE','LIC','DNR','LFAM','LUT','SAN','STC')  AND ClaseLiq='NOVEDAD' GROUP BY IdPeriodo,KeyRegistro)
 AS NDE ON PS.tmIdPeriodo=NDE.IdPeriodo AND PS.tmKeyRegistro=NDE.KeyRegistro
 LEFT JOIN NomVehiculos AS VH ON N.pVehiculo=VH.IdVehiculo
 WHERE SUBSTRING(tmIdPeriodo,1,6)=@pmMes AND tmIdPeriodo LIKE ISNULL(@pmtmIdPeriodo,'%') 
 AND tmKeyRegistro LIKE ISNULL(@pmtmKeyRegistro,'%')  AND N.IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%')
 ORDER BY Apellidos,Nombres,N.IdEmpleado