if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsEmp_Contrato]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsEmp_Contrato]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomConceptos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNomConceptos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomDetalle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNomDetalle]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomDetalle_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNomDetalle_Sel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomNov]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNomNov]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTiposCon]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTiposCon]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_AuxInv_SelVen]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_AuxInv_SelVen]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_AuxInv_SelVenBod]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_AuxInv_SelVenBod]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_AuxInv_SelVenBodCia]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_AuxInv_SelVenBodCia]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_AuxInv_SelVenCia]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_AuxInv_SelVenCia]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_AuxInv_SelVenTanq]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_AuxInv_SelVenTanq]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_AuxInv_SelVenTanqCia]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_AuxInv_SelVenTanqCia]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Nomina]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_Nomina]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Nomina_Liq]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_Nomina_Liq]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Nomina_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_Nomina_Sel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Nomina_Vac]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_Nomina_Vac]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEmp_Contrato]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryEmp_Contrato]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEmp_ContratoDso]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryEmp_ContratoDso]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEmp_ContratoEca]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryEmp_ContratoEca]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMttoOrdenRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMttoOrdenRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMttoOrdenRelDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMttoOrdenRelDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomConceptos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomConceptos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomConceptosLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomConceptosLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomDetalle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomDetalle]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomDetallePer]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomDetallePer]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomDetalleRes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomDetalleRes]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomDetalleResN]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomDetalleResN]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomNov]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomNov]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomNov_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomNov_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomNovCon]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomNovCon]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTiposCon]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTiposCon]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_Nomina]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_Nomina]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_Nomina_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_Nomina_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_NominaDsa]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_NominaDsa]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_NominaDso]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_NominaDso]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpEmp_Contrato]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpEmp_Contrato]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpNomConceptos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpNomConceptos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpNomDetalle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpNomDetalle]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpNomNov]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpNomNov]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTiposCon]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTiposCon]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUptm_Nomina]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUptm_Nomina]
GO


SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNomDetallePer] @pmIdPeriodo VARCHAR(8),@pmKeyRegistro VARCHAR(30)=Null,@pmIdNom VARCHAR(4)=Null
 AS 
 SELECT D.IdEmpleado AS CedEmpleado,Apellidos,Nombres,Item,D.IdConcepto AS IdConcept,Detalle,ClaseCon,CantDevg,VrUnitario,VrTotDevg,CantDed,VrTotDed
,D.Unidad AS Undad,VrOrigen,D.Tarifa AS Tarfa,VrBaseLiq,DiasCalc,DiasNov,DiasCalc-DiasNov AS DiasNeto,ClaseLiq,NPrestamo,NCuota,OrigCargue,CodFondo,D.KeyRegistro AS Kyregistro
,D.TipoIncLab,D.HoraInicial,D.HoraFinal
 FROM Trn_NomDetalle AS D INNER JOIN Empleados AS E ON D.IdEmpleado=E.IdEmpleado
 INNER JOIN  NomConceptos AS C ON D.IdConcepto=C.IdConcepto
 INNER JOIN Trn_Nomina AS N ON D.IdPeriodo=N.IdPeriodo AND D.KeyRegistro=N.KeyRegistro
 WHERE D.IdPeriodo=@pmIdPeriodo AND D.KeyRegistro LIKE ISNULL(@pmKeyRegistro,'%') AND IdNom like ISNULL(@pmIdNom,'%')
 ORDER BY Apellidos,Nombres,Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInstm_Nomina_Sel] @pmIdPeriodo VARCHAR(8),@pmKeyRegistro VARCHAR(30),@pmtmNumero VARCHAR(5)
 AS 
 INSERT INTO tm_Nomina (tmNumero,tmItem,tmIdEmpleado,tmIdConcepto,tmClaseCon,tmDetalle,tmCantDevg,tmCantDed,tmVrUnitario,tmVrTotDevg,tmVrTotDed,tmUnidad
,tmClaseLiq,tmVrOrigen,tmTarifa,tmDiasCalc,tmDiasNov,tmCodFondo,tmNPrestamo,tmNCuota,tmOrigCargue,tmVrBaseLiq,tmNContto,tmFecIni,tmFecFin,tmIdPeriodo,tmVrPension,tmVrSalud,tmBasExceso,tmNitTercero,tmHoraInicial,tmHoraFinal,tmTipoInc)
 SELECT @pmtmNumero,Item,D.IdEmpleado,D.IdConcepto,ClaseCon,Detalle,CantDevg,CantDed,VrUnitario,VrTotDevg,VrTotDed,D.Unidad,ClaseLiq,VrOrigen,D.Tarifa,DiasCalc
,DiasNov,CodFondo,NPrestamo,NCuota,OrigCargue,VrBaseLiq,NContrato,FecInicial,FecFinal,D.IdPeriodo,0,0,VrBasExceso,NitTercero,HoraInicial,HoraFinal,TipoIncLab
 FROM Trn_NomDetalle AS D INNER JOIN NomConceptos AS C ON D.IdConcepto=C.IdConcepto
 INNER JOIN Trn_Nomina AS N ON D.IdPeriodo=N.IdPeriodo AND D.KeyRegistro=N.KeyRegistro
 WHERE D.IdPeriodo=@pmIdPeriodo AND D.KeyRegistro=@pmKeyRegistro 
 AND  ClaseLiq  IN  ('NOMINA','NOVEDAD')
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNomDetalleRes] @pmFecIni SMALLDATETIME,@pmFecFin SMALLDATETIME,@pmIdPeriodo VARCHAR(8)=Null
,@pmIdEmpleado VARCHAR(16)=Null,@pmNContrato INT=Null,@pmIdArea VARCHAR(4)=Null,@pmIdDep VARCHAR(4)=Null,@pmIdNom VARCHAR(4)=Null
,@pmIdInstala VARCHAR(4)=Null,@pmIdCCosto  VARCHAR(16)=Null,@pmIdEstado VARCHAR(4)=Null,@pmIdTipCon VARCHAR(4)=Null,@pmEsRetiro BIT=Null,@pmNLiqRet INT=Null
,@pmInactivo BIT=Null,@pmEmp_Inactivo BIT=Null,@pmIdConcepto VARCHAR(4)=Null,@pmClaseLiq VARCHAR(10)=Null,@pmClaseCon VARCHAR(10)=Null,@pmNPrestamo INT=Null
 AS 
 SELECT N.IdEmpleado AS IdEmpldo,Apellidos,Nombres,N.NContrato AS NumCntrato,N.KeyRegistro AS KeyReg,N.IdPeriodo AS NumPeriodo,FecInicial,FecFinal,Cantidad,CantNoRem,VrDevengado,VrDeducido
,N.IdNom AS CodNmna,TipoNomina,N.IdDep AS CodDep,Dependencia,N.IdCCosto AS CodCcto,CCosto,N.IdSubCos AS CodSubcos,SubCosto,N.IdInstala AS CodIstala,Instlacion
,N.VrSalario AS SalBasico,N.pVehiculo AS PlacaVeh,TipCom,TipoCom,Comprobante,IdCiaCom,N.Observacion AS Observ,N.IdEstado AS IdEstdo,ED.Estado AS Nom_Estado,EsRetiro,NLiqRet,TipoLiq,TimeSys
,N.FecUpdate AS Fec_Update,N.IdUsuario AS IdUsuari,Usuario
--datos del detalle
,Item,D.IdConcepto AS CodConcept,Concepto,Detalle,CantDevg,CantDed,VrUnitario,VrTotDevg,VrTotDed,ClaseCon,D.Unidad AS Undad,ClaseLiq,VrOrigen,D.Tarifa AS Tarfa,DiasCalc,DiasNov,CodFondo,FCO.Fondo AS NomFondo
,NPrestamo,NCuota,OrigCargue,VrBaseLiq,VrBasExceso,D.HoraInicial,D.HoraFinal,D.TipoIncLab
--Datos 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,FC.Fondo AS CajaComp,EsCondtor,FecNacmto,Licencia,CatLicencia,VigLicencia,E.IdLugar AS CodLugar,LugarLic,TallaCam,TallaPan,TallaZap,E.Inactivo AS Emp_Inactvo
--datos del contrato
,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,C.VrSalario AS VlrSalario,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,C.IdGrupo AS CodGrup
 FROM Trn_Nomina AS N INNER JOIN Empleados AS E ON N.IdEmpleado=E.IdEmpleado
 INNER JOIN TiposNom AS TN ON N.IdNom=TN.IdNom INNER JOIN Dependencias AS DP ON N.IdDep=DP.IdDep
 INNER JOIN CentroCosto AS CC ON N.IdCCosto=CC.IdCCosto INNER JOIN SubCentros AS SC ON N.IdSubCos=SC.IdSubCos
 INNER JOIN EstadoDoc AS ED ON N.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON N.IdUsuario=U.IdUsuario
 INNER JOIN Instalaciones AS I ON N.IdInstala=I.IdInstala  INNER JOIN  Emp_Contrato AS C ON N.NContrato=C.NContrato
 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 FC ON E.IdCajaCom=FC.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 
 INNER JOIN  Trn_NomDetalle AS D ON N.IdPeriodo=D.IdPeriodo AND  N.KeyRegistro=D.KeyRegistro
 INNER JOIN NomConceptos AS NC ON D.IdConcepto=NC.IdConcepto
 LEFT JOIN TiposCom AS TCM ON N.TipCom=TCM.IdCom  LEFT JOIN Fondos AS FCO ON D.CodFondo=FCO.IdFondo
 WHERE FecInicial>=@pmFecIni  AND FecFinal<=@pmFecFin AND N.IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%')  AND  N.IdPeriodo LIKE ISNULL(@pmIdPeriodo,'%')
 AND N.IdNom LIKE ISNULL(@pmIdNom,'%') AND N.IdDep LIKE ISNULL(@pmIdDep,'%') AND N.IdInstala LIKE ISNULL(@pmIdInstala,'%')
 AND N.IdCCosto LIKE ISNULL(@pmIdCCosto,'%') AND N.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND C.IdArea LIKE ISNULL(@pmIdArea,'%')
 AND C.IdTipCon LIKE ISNULL(@pmIdTipCon,'%') AND D.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND ClaseLiq LIKE ISNULL(@pmClaseLiq,'%')
 AND ClaseCon LIKE ISNULL(@pmClaseCon,'%')
 AND (N.NContrato>=ISNULL(@pmNContrato,0)  AND N.NContrato<=ISNULL(@pmNContrato,2147483647))
 AND (NLiqRet>=ISNULL(@pmNLiqRet,-2)  AND NLiqRet<=ISNULL(@pmNLiqRet,2147483647))
 AND (EsRetiro=ISNULL(@pmEsRetiro,0) or EsRetiro=ISNULL(@pmEsRetiro,1))
 AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
 AND (E.Inactivo=ISNULL(@pmEmp_Inactivo,0) or E.Inactivo=ISNULL(@pmEmp_Inactivo,1))
 AND (NPrestamo>=ISNULL(@pmNPrestamo,0)  AND NPrestamo<=ISNULL(@pmNPrestamo,2147483647))
--Descuentos en vacaciones
UNION ALL
 SELECT V.IdEmpleado,Apellidos,Nombres,V.NContrato,V.IdEmpleado,D.IdPeriodo,V.FecInicial,V.FecFinal,V.Cantidad,0,V.VrLiquida,V.VrTotDed
,C.IdNom,TipoNomina,C.IdDep,Dependencia,C.IdCCosto,CCosto,C.IdSubCos,SubCosto,C.IdInstala,Instlacion,V.VrSalario,V.pVehiculo,V.TipCom,TipoCom,V.Comprobante,V.IdCiaCom
,V.Observacion,V.IdEstado,ED.Estado,0,V.Numero,C.TipoLiquida,V.TimeSys,V.FecUpdate,V.IdUsuario,Usuario
,D.Item,D.IdConcepto,Concepto,D.Detalle,0,CASE D.IdConcepto WHEN 'DCP' THEN 0 WHEN 'DCS' THEN 0 WHEN 'FSP' THEN 0 WHEN 'FSU' THEN 0 ELSE D.Cantidad END AS CantDed,0,D.VrDevgado,D.VrDeducido,ClaseCon,'%','NOMINA',D.VrBaseLiq,D.Tarifa
,CASE D.IdConcepto WHEN 'DCP' THEN 0 WHEN 'DCS' THEN 0 WHEN 'FSP' THEN 0 WHEN 'FSU' THEN 0 ELSE D.Cantidad END AS DiasCalc,0,'0','',D.NPrestamo,D.NCuota,'VAC',D.VrBaseLiq,0,Null,Null,''
,E.Codigo,E.IdLugarCed,Localidad,E.Direccion,E.Telefono,E.TelMovil,E.e_mail,E.IdProf,Profesion,E.IdClase,ClaseCuenta,NumCuenta,E.IdBanco,Banco,IdFonPen,FP.Fondo
,IdFonEps,FS.Fondo,IdFonArp,FR.Fondo,IdCajaCom,FC.Fondo,EsCondtor,FecNacmto,Licencia,CatLicencia,VigLicencia,E.IdLugar,LugarLic,TallaCam,TallaPan,TallaZap,E.Inactivo
,C.IdTipCon,TipoContrato,FecIngreso,FecRetiro,Indefinido,C.IdCargo,Cargo,C.IdArea,Area,C.IdClase,ClaseRiesgo,CR.Tarifa,C.VrSalario,VrAuxTrans,SalMinimo,SalIntegral,FPnomina
,ATFinMes,SegFinMes,DchDotacion,DchCafeteria,C.IdEstado,ECO.Estado,C.Inactivo,C.IdJornada,Jornada,TipoTrabj,C.IdGrupo
 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 adm_Usuarios AS U ON V.IdUsuario=U.IdUsuario
 INNER JOIN NomConceptos AS NC ON D.IdConcepto=NC.IdConcepto INNER JOIN TiposNom AS TN ON C.IdNom=TN.IdNom INNER JOIN Dependencias AS DPD ON C.IdDep=DPD.IdDep
 INNER JOIN CentroCosto AS CC ON C.IdCCosto=CC.IdCCosto INNER JOIN Instalaciones AS I ON C.IdInstala=I.IdInstala
 INNER JOIN TiposCon AS TC ON C.IdTipCon=TC.IdTipCon
 INNER JOIN Areas AS AR ON C.IdArea=AR.IdArea INNER JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo
 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 FC ON E.IdCajaCom=FC.IdFondo 
 INNER JOIN ExpLicencias AS EL ON E.IdLugar=EL.IdLugar
 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 INNER JOIN EstadoDoc AS ED ON V.IdEstado=ED.IdEstado
 LEFT JOIN SubCentros AS SC ON C.IdSubCos=SC.IdSubCos LEFT JOIN TiposCom AS TCM ON V.TipCom=TCM.IdCom
 WHERE V.Anulado=0 
 AND (SUBSTRING(D.IdPeriodo,1,6)>=SUBSTRING(CONVERT(VARCHAR(30),@pmFecIni,112),1,6) AND SUBSTRING(D.IdPeriodo,1,6)<=SUBSTRING(CONVERT(VARCHAR(30),@pmFecFin,112),1,6))
 AND D.IdPeriodo LIKE ISNULL(@pmIdPeriodo,'%') AND V.IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%')
 AND C.IdNom LIKE ISNULL(@pmIdNom,'%') AND C.IdDep LIKE ISNULL(@pmIdDep,'%') AND C.IdInstala LIKE ISNULL(@pmIdInstala,'%')
 AND C.IdCCosto LIKE ISNULL(@pmIdCCosto,'%') AND V.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND C.IdArea LIKE ISNULL(@pmIdArea,'%')
 AND C.IdTipCon LIKE ISNULL(@pmIdTipCon,'%') AND D.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND ClaseCon LIKE ISNULL(@pmClaseCon,'%')
 AND (V.NContrato>=ISNULL(@pmNContrato,0)  AND V.NContrato<=ISNULL(@pmNContrato,2147483647))
 AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
 AND (E.Inactivo=ISNULL(@pmEmp_Inactivo,0) or E.Inactivo=ISNULL(@pmEmp_Inactivo,1))
 AND (D.NPrestamo>=ISNULL(@pmNPrestamo,0)  AND D.NPrestamo<=ISNULL(@pmNPrestamo,2147483647))
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsNomDetalle_Sel] @pmIdEmpleado VARCHAR(16),@pmNewCedula VARCHAR(16)
 AS 
 INSERT INTO Trn_NomDetalle (IdPeriodo,KeyRegistro,IdEmpleado,Item,IdConcepto,Detalle,CantDevg,CantDed,VrUnitario,VrTotDevg,VrTotDed,Unidad,ClaseLiq,VrOrigen
,Tarifa,DiasCalc,DiasNov,CodFondo,NPrestamo,NCuota,OrigCargue,VrBaseLiq,VrBasExceso,NitTercero,HoraInicial,HoraFinal,TipoIncLab)
 SELECT D.IdPeriodo,@pmNewCedula +CAST(NContrato AS VARCHAR(10)) +CASE EsRetiro WHEN 1 THEN '_1' ELSE '_0'  END
,@pmNewCedula,Item,IdConcepto,Detalle,CantDevg,CantDed,VrUnitario,VrTotDevg,VrTotDed,Unidad,ClaseLiq,VrOrigen
,Tarifa,DiasCalc,DiasNov,CodFondo,NPrestamo,NCuota,OrigCargue,VrBaseLiq,VrBasExceso,NitTercero,HoraInicial,HoraFinal,TipoIncLab
 FROM Trn_NomDetalle AS D INNER JOIN Trn_Nomina AS N ON D.IdPeriodo=N.IdPeriodo AND D.KeyRegistro=N.KeyRegistro
 WHERE D.IdEmpleado=@pmIdEmpleado
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsNomDetalle] @pmIdPeriodo VARCHAR(8),@pmKeyRegistro VARCHAR(30),@pmIdEmpleado VARCHAR(16),@pmItem INT,@pmIdConcepto VARCHAR(4),@pmDetalle VARCHAR(100),@pmCantDevg DECIMAL(10,4),@pmCantDed DECIMAL(10,4),@pmVrUnitario MONEY,@pmVrTotDevg MONEY,@pmVrTotDed MONEY
,@pmUnidad VARCHAR(10),@pmClaseLiq VARCHAR(10),@pmVrOrigen MONEY,@pmTarifa DECIMAL(10,4),@pmDiasCalc DECIMAL(10,4),@pmDiasNov DECIMAL(10,4),@pmCodFondo VARCHAR(8),@pmNPrestamo INT,@pmNCuota INT,@pmOrigCargue VARCHAR(10),@pmVrBaseLiq MONEY,@pmVrBasExceso MONEY,@pmNitTercero VARCHAR(16)
,@pmHoraInicial DATETIME,@pmHoraFinal DATETIME,@pmTipoIncLab VARCHAR(20)
 AS 
 INSERT INTO Trn_NomDetalle (IdPeriodo,KeyRegistro,IdEmpleado,Item,IdConcepto,Detalle,CantDevg,CantDed,VrUnitario,VrTotDevg,VrTotDed,Unidad,ClaseLiq,VrOrigen,Tarifa,DiasCalc,DiasNov,CodFondo,NPrestamo,NCuota,OrigCargue,VrBaseLiq,VrBasExceso,NitTercero,HoraInicial,HoraFinal,TipoIncLab)
 VALUES (@pmIdPeriodo,@pmKeyRegistro,@pmIdEmpleado,@pmItem,@pmIdConcepto,@pmDetalle,@pmCantDevg,@pmCantDed,@pmVrUnitario,@pmVrTotDevg,@pmVrTotDed,@pmUnidad,@pmClaseLiq,@pmVrOrigen,@pmTarifa
,@pmDiasCalc,@pmDiasNov,@pmCodFondo,@pmNPrestamo,@pmNCuota,@pmOrigCargue,@pmVrBaseLiq,@pmVrBasExceso,@pmNitTercero,@pmHoraInicial,@pmHoraFinal,@pmTipoIncLab)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUpNomDetalle] @pmIdPeriodo VARCHAR(8),@pmKeyRegistro VARCHAR(30),@pmItem INT,@pmIdEmpleado VARCHAR(16),@pmIdConcepto VARCHAR(4),@pmDetalle VARCHAR(100),@pmCantDevg DECIMAL(10,4),@pmCantDed DECIMAL(10,4),@pmVrUnitario MONEY,@pmVrTotDevg MONEY
,@pmVrTotDed MONEY,@pmUnidad VARCHAR(10),@pmClaseLiq VARCHAR(10),@pmVrOrigen MONEY,@pmTarifa DECIMAL(10,4),@pmDiasCalc DECIMAL(10,4),@pmDiasNov DECIMAL(10,4),@pmCodFondo VARCHAR(8),@pmNPrestamo INT,@pmNCuota INT,@pmOrigCargue VARCHAR(10),@pmVrBaseLiq MONEY,@pmVrBasExceso MONEY,@pmNitTercero VARCHAR(16)
,@pmHoraInicial DATETIME,@pmHoraFinal DATETIME,@pmTipoIncLab VARCHAR(20)
 AS 
 UPDATE Trn_NomDetalle SET IdEmpleado=@pmIdEmpleado,IdConcepto=@pmIdConcepto,Detalle=@pmDetalle,CantDevg=@pmCantDevg,CantDed=@pmCantDed,VrUnitario=@pmVrUnitario,VrTotDevg=@pmVrTotDevg,VrTotDed=@pmVrTotDed,Unidad=@pmUnidad,ClaseLiq=@pmClaseLiq
,VrOrigen=@pmVrOrigen,Tarifa=@pmTarifa,DiasCalc=@pmDiasCalc,DiasNov=@pmDiasNov,CodFondo=@pmCodFondo,NPrestamo=@pmNPrestamo,NCuota=@pmNCuota,OrigCargue=@pmOrigCargue,VrBaseLiq=@pmVrBaseLiq,VrBasExceso=@pmVrBasExceso,NitTercero=@pmNitTercero,HoraInicial=@pmHoraInicial,HoraFinal=@pmHoraFinal,TipoIncLab=@pmTipoIncLab
 WHERE IdPeriodo=@pmIdPeriodo AND KeyRegistro=@pmKeyRegistro AND Item=@pmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomDetalle] @pmIdPeriodo VARCHAR(8),@pmKeyRegistro VARCHAR(30),@pmItem INT
 AS 
 SELECT IdPeriodo,KeyRegistro,Item,IdEmpleado,IdConcepto,Detalle,CantDevg,CantDed,VrUnitario,VrTotDevg,VrTotDed,Unidad,ClaseLiq,VrOrigen,Tarifa,DiasCalc
,DiasNov,CodFondo,NPrestamo,NCuota,OrigCargue,VrBaseLiq,VrBasExceso,NitTercero,HoraInicial,HoraFinal,TipoIncLab
 FROM Trn_NomDetalle
 WHERE IdPeriodo=@pmIdPeriodo AND KeyRegistro=@pmKeyRegistro 
  AND (Item>=ISNULL(@pmItem,0)  AND Item<=ISNULL(@pmItem,2147483647))
 ORDER BY Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNomDetalleResN] @pmFecIni SMALLDATETIME,@pmFecFin SMALLDATETIME,@pmIdPeriodo VARCHAR(8)=Null
,@pmIdEmpleado VARCHAR(16)=Null,@pmNContrato INT=Null,@pmIdArea VARCHAR(4)=Null,@pmIdDep VARCHAR(4)=Null,@pmIdNom VARCHAR(4)=Null
,@pmIdInstala VARCHAR(4)=Null,@pmIdCCosto  VARCHAR(16)=Null,@pmEsRetiro BIT=Null
,@pmInactivo BIT=Null,@pmEmp_Inactivo BIT=Null,@pmIdConcepto VARCHAR(4)=Null,@pmClaseLiq VARCHAR(10)=Null,@pmClaseCon VARCHAR(10)=Null
 AS 
SELECT N.IdEmpleado AS IdEmple,N.NContrato AS NumContrato,FecInicial,FecFinal,N.IdPeriodo AS IdPerdo,IdArea,N.IdDep AS nDepe,N.IdInstala AS nInstala
,N.IdCCosto AS CodCcosto,N.IdSubCos AS CodSubcentro,N.IdNom AS CdNom,N.VrSalario AS vSalario,VrATransMes,N.pVehiculo AS PlacaVeh,TipCom,Comprobante,IdCiaCom
,N.Observacion AS Observ,EsRetiro,NLiqRet,TipoLiq
--Detales
,D.IdConcepto AS CdConcepto,Item,Detalle,CantDevg,CantDed,VrUnitario,VrTotDevg,VrTotDed,D.Unidad AS Und,ClaseLiq,VrOrigen,D.Tarifa AS Tarif,DiasCalc,DiasNov,CodFondo
,NPrestamo,NCuota,VrBaseLiq,HoraInicial,HoraFinal,TipoIncLab
 FROM Trn_Nomina AS N INNER JOIN Empleados AS E ON N.IdEmpleado=E.IdEmpleado
 INNER JOIN Emp_Contrato AS C ON N.NContrato=C.NContrato
 INNER JOIN Trn_NomDetalle AS D ON N.IdPeriodo=D.IdPeriodo AND N.KeyRegistro=D.KeyRegistro
 INNER JOIN NomConceptos AS NC ON D.IdConcepto=NC.IdConcepto
 WHERE FecInicial>=@pmFecIni  AND FecFinal<=@pmFecFin AND N.IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%')  AND  N.IdPeriodo LIKE ISNULL(@pmIdPeriodo,'%')
 AND IdArea LIKE ISNULL(@pmIdArea,'%') AND N.IdDep LIKE ISNULL(@pmIdDep,'%')
 AND N.IdNom LIKE ISNULL(@pmIdNom,'%')  AND N.IdInstala LIKE ISNULL(@pmIdInstala,'%')
 AND N.IdCCosto LIKE ISNULL(@pmIdCCosto,'%')
 AND D.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND ClaseLiq LIKE ISNULL(@pmClaseLiq,'%')
 AND ClaseCon LIKE ISNULL(@pmClaseCon,'%')
 AND (N.NContrato>=ISNULL(@pmNContrato,0)  AND N.NContrato<=ISNULL(@pmNContrato,2147483647))
 AND (EsRetiro=ISNULL(@pmEsRetiro,0) or EsRetiro=ISNULL(@pmEsRetiro,1))
 AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
 AND (E.Inactivo=ISNULL(@pmEmp_Inactivo,0) or E.Inactivo=ISNULL(@pmEmp_Inactivo,1))
 ORDER BY  FecInicial, N.IdEmpleado,Item
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMttoOrdenRelDet] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdVehiculo VARCHAR(10)=Null
AS 
 SELECT O.TipDoc AS IdTipDoc,TipoDoc,O.NumOrden,O.IdCia AS CdCia,Compania,Fecha,FechaOrden,FechaVence,O.IdVehiculo,NumVeh,kmVehiculo,nRemolque,kmRemolque,O.IdTipoOdt AS CdTipoOdt,TipoOrden
,O.IdCenSer AS CdCenSer,CentroServ,NitCenSer,O.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,IdSupervisor,NSU.RazonSocial AS Supervisor
--<campos de detalles
,D.Item,D.TipoItem,D.IdConc AS CdConc,D.Descripcion,D.Cantidad,D.IdUnd,D.UndMed,D.VrUnitario,D.TipoServ,D.IdOperario AS NitOperario,T.RazonSocial AS Operario,D.IdEstado AS CdEstadoServ,ESV.Estado AS EstadoServ,ESV.NColor AS ES_NumColor
,D.CdMtto,TipoMtto,C.IdSistema AS CdSistema,Sistema,Seccion,Concepto,D.IdProducto,DescripProd,D.FechaServ,D.NumParte,D.TipReq,D.Requisicion,D.IdCiaReq,TipSal,NumSalida,IdCiaSal,CantSalida,D.EstadoReq,C.TarifaClie,Req_Rep,Req_MO
,CASE ISNULL(D.TipoReg,0) WHEN 0 THEN 'SERVICIO' ELSE 'REQ' END AS Tipo_Reg
--campos de detalles>
,O.Modalidad,VrServicios,VrRepuestos,VrManoObra,VrOtros,O.IdEstado AS CdEstado,ED.Estado AS EstadoDoc,ED.NColor AS EdNumColor,FechaCierre,O.Anulado,NumDev,FecDev
,O.Observacion AS Observ,O.TipFact,O.NumFactura,O.IdCiaFact,O.TimeSys AS FechaCrea,O.FecUpdate AS FecActualiza,O.IdCiaCrea AS CdCIaCrea,O.OrigenAdd,O.IdUsuario AS CdUsuario,Usuario,Leyenda
--campos de vehículo
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad
,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,M.Marca AS MarcaVeh,V.IdLinea AS CdLinVeh,LineaVeh,V.IdColor AS CdColor,NomColor,V.IdCrceria AS CdCarr,TipoCar,Modelo,Config,V.IdGrupo AS CdGrupo,GrupoProp
,PesoVacio,NumMotor,SerieChasis,NumSerie,CdRemque,NitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPoseedor AS NitPoseedor,NPO.RazonSocial AS Poseedor
,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora,V.Descripcion AS VehDescripcion,KmInicial,KmActual,Km2Actual AS kmRuta,CentFinal AS KmOtro,V.Ubicacion
,V.IdEstado AS VehCdEstado,EV.Estado AS VehEstado,EV.NColor AS VehEstColor,EV.OutDemand AS VehNoDisponible,V.Inactivo AS VehInactivo
,Licencia,CatLicencia,CT.IdLugar AS CdLugar,LugarLic,FecLicencia,VigLicencia
,ISNULL(RS.ReqAprob,0) AS Req_Aprob,ISNULL(RS.ReqNoAprob,0) AS Req_NoAprob
 FROM Trn_MttoOrden AS O INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado
 INNER JOIN MttoTiposOdt AS TOT ON O.IdTipoOdt=TOT.IdTipoOdt INNER JOIN CentrosServ AS CS ON O.IdCenSer=CS.IdCenSer
 INNER JOIN Terceros AS CDT ON O.IdConductor=CDT.IdTercero INNER JOIN Terceros AS NSU ON O.IdSupervisor=NSU.IdTercero
 INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario INNER JOIN Sys_TiposDoc AS TD ON O.TipDoc=TD.IdDoc
  --<subconsulta detalles
 INNER JOIN (SELECT DS.TipOdt,DS.NumOrden,DS.IdCia,DS.Item,DS.FechaServ,DS.IdConc,DS.Descripcion,DS.Cantidad,DS.VrUnitario,'0' AS IdUnd,DS.UndMed,DS.CdMtto,DS.IdEstado,DS.IdOperario
 ,DS.NumVehic,DS.NumParte,DS.TipoServ,DS.FechaCrea,DS.IdUsuario,'0' AS TipReq,0 AS Requisicion,'00' AS IdCiaReq
 ,CASE DS.TipoServ WHEN 'EXTERNA' THEN 'MO' ELSE 'REPUESTO' END AS TipoItem,'0' AS IdProducto,0 AS EstadoReq,'0' AS TipSal,0 AS NumSalida,'00' AS IdCiaSal,0 AS CantSalida,0 AS TipoReg
,DS.Req_Rep,DS.Req_MO
 FROM Trn_MttoOrdenDet AS DS INNER JOIN Trn_MttoOrden AS O ON DS.TipOdt=O.TipDoc AND DS.NumOrden=O.NumOrden AND DS.IdCia=O.IdCia
 WHERE O.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND DS.IdCia LIKE ISNULL(@pmIdCia,'%%') AND O.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')
 UNION ALL
 SELECT R.TipOdt,R.NumOrden,R.IdCiaOdt,R.Item,R.FechaCrea,R.CdConcServ,R.Descripcion,R.Cantidad,R.VrUnitario,R.IdUnd,Unidad,C.IdMtto
 ,CASE R.EstadoReq WHEN -1 THEN '9999' WHEN 0 THEN '0001' WHEN 1 THEN '9998' ELSE '0001' END,R.IdOperario
 ,R.NumVehic,R.NumParte,'INTERNA',R.FechaCrea,R.IdUsuario,R.TipReq,R.Requisicion,R.IdCia,R.TipoItem,R.IdProducto,R.EstadoReq
 ,R.TipSal,R.NumSalida,R.IdCiaSal,R.CantSalida,1 AS TipoReg,0,0
 FROM Trn_MttoOrdenReq AS R INNER JOIN Trn_MttoOrden AS O ON R.TipOdt=O.TipDoc AND R.NumOrden=O.NumOrden AND R.IdCiaOdt=O.IdCia
 INNER JOIN Trn_Requisicion AS RQ ON R.TipReq=RQ.TipDoc AND R.Requisicion=RQ.Requisicion AND R.IdCia=RQ.IdCia 
 LEFT JOIN MttoConceptos AS C ON R.CdConcServ=C.IdConc LEFT JOIN UndMed AS UM ON R.IdUnd=UM.IdUnd
 WHERE O.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND R.IdCiaOdt LIKE ISNULL(@pmIdCia,'%%') AND O.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') ) AS D ON O.TipDoc=D.TipOdt AND O.NumOrden=D.NumOrden AND O.IdCia=D.IdCia
 --subconsulta detalles>
 INNER JOIN Terceros AS T ON D.IdOperario=T.IdTercero INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 LEFT JOIN Vehiculos AS V ON O.IdVehiculo=V.IdVehiculo LEFT JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 LEFT JOIN Marcas AS M ON V.IdMarca=M.IdMarca LEFT JOIN TiposCol AS CL ON V.IdColor=CL.IdColor 
 LEFT JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria LEFT JOIN Terceros AS NPO ON V.IdPoseedor=NPO.IdTercero
 LEFT JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd LEFT JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN TercCndtores AS CT ON O.IdConductor=CT.IdConductor LEFT JOIN ExpLicencias AS EL ON CT.IdLugar=EL.IdLugar
 LEFT JOIN EstadoVeh AS EV ON V.IdEstado=EV.IdEstado LEFT JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo
 LEFT JOIN MttoConceptos AS C ON D.IdConc=C.IdConc LEFT JOIN MttoSistemas AS S ON C.IdSistema=S.IdSistema
 LEFT JOIN MttoTipos AS TM ON D.CdMtto=TM.IdMtto LEFT JOIN MttoEstados AS ESV ON D.IdEstado=ESV.IdEstado
 LEFT JOIN ProdMcias AS P ON D.IdProducto=P.IdProducto
--<consulta requisiciones aprobadas
 LEFT JOIN (SELECT R.TipOdt,R.NumOrden,R.IdCiaOdt,SUM(CASE WHEN (RQ.IdEstado='0002' OR RQ.NumAprob<=0) THEN 1 ELSE 0 END) AS ReqNoAprob
 ,SUM(CASE WHEN RQ.IdEstado<>'0002' AND RQ.NumAprob>0 THEN 1 ELSE 0 END) AS ReqAprob
  FROM Trn_MttoOrdenReq AS R INNER JOIN Trn_Requisicion AS RQ ON R.TipReq=RQ.TipDoc AND R.Requisicion=RQ.Requisicion AND R.IdCia=RQ.IdCia
  WHERE RQ.Anulado=0 GROUP BY R.TipOdt,R.NumOrden,R.IdCiaOdt) AS RS ON O.TipDoc=RS.TipOdt AND O.NumOrden=RS.NumOrden AND O.IdCia=RS.IdCiaOdt
--fin consulta requisiciones aprobadas>
 WHERE O.TipDoc=@pmTipDoc AND O.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND O.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND O.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMttoOrdenRel] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdVehiculo VARCHAR(10)=Null
AS 
 SELECT O.TipDoc AS IdTipDoc,TipoDoc,O.NumOrden,O.IdCia AS CdCia,Compania,Fecha,FechaOrden,FechaVence,O.IdVehiculo,NumVeh,kmVehiculo,nRemolque,kmRemolque,O.IdTipoOdt AS CdTipoOdt,TipoOrden,O.IdCenSer AS CdCenSer,CentroServ
,O.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,O.IdOperario AS NitOperario,T.RazonSocial AS NomOperario,IdSupervisor,NSU.RazonSocial AS Supervisor,O.Modalidad
,VrServicios,VrRepuestos,VrManoObra,VrOtros,FechaCierre,NitCenSer,O.CdEstadoVeh,EV.Estado AS EstadoVeh,EV.NColor AS EdvNColor,EV.OutDemand,O.IdEstado AS CdEstado,ED.Estado AS EstadoDoc,ED.NColor AS EdNumColor
,O.Anulado,NumDev,FecDev,O.Observacion AS Observ,O.TipFact,O.NumFactura,O.IdCiaFact,O.TimeSys AS FechaCrea,O.FecUpdate AS FecActualiza,O.IdCiaCrea AS CdCIaCrea,O.OrigenAdd,O.IdUsuario AS CdUsuario,Usuario,Leyenda
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad
,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,M.Marca AS MarcaVeh,V.IdLinea AS CdLinVeh,LineaVeh,V.IdColor AS CdColor,NomColor,V.IdCrceria AS CdCarr,TipoCar,Modelo,Config
,PesoVacio,NumMotor,SerieChasis,NumSerie,CdRemque,NitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPoseedor AS NitPoseedor,NPO.RazonSocial AS Poseedor
,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora,V.Descripcion AS VehDescripcion,KmInicial,KmActual,Km2Actual,V.Ubicacion
,V.IdEstado AS VehCdEstado,EVH.Estado AS VehEstado,EVH.NColor AS VehEstColor,EVH.OutDemand AS VehNoDisponible,V.Inactivo AS VehInactivo,V.IdGrupo AS CdGrupo,GrupoProp
,Licencia,CatLicencia,CT.IdLugar AS CdLugar,LugarLic,FecLicencia,VigLicencia
,ISNULL(RS.ReqAprob,0) AS Req_Aprob,ISNULL(RS.ReqNoAprob,0) AS Req_NoAprob
 FROM Trn_MttoOrden AS O INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado
 INNER JOIN MttoTiposOdt AS TOT ON O.IdTipoOdt=TOT.IdTipoOdt INNER JOIN CentrosServ AS CS ON O.IdCenSer=CS.IdCenSer
 INNER JOIN Terceros AS T ON O.IdOperario=T.IdTercero INNER JOIN Terceros AS CDT ON O.IdConductor=CDT.IdTercero INNER JOIN Terceros AS NSU ON O.IdSupervisor=NSU.IdTercero
 INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario INNER JOIN Sys_TiposDoc AS TD ON O.TipDoc=TD.IdDoc
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 LEFT JOIN EstadoVeh AS EV ON O.CdEstadoVeh=EV.IdEstado 
 LEFT JOIN Vehiculos AS V ON O.IdVehiculo=V.IdVehiculo LEFT JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 LEFT JOIN Marcas AS M ON V.IdMarca=M.IdMarca LEFT JOIN TiposCol AS CL ON V.IdColor=CL.IdColor 
 LEFT JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria LEFT JOIN Terceros AS NPO ON V.IdPoseedor=NPO.IdTercero
 LEFT JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd LEFT JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN TercCndtores AS CT ON O.IdConductor=CT.IdConductor LEFT JOIN ExpLicencias AS EL ON CT.IdLugar=EL.IdLugar
 LEFT JOIN EstadoVeh AS EVH ON V.IdEstado=EVH.IdEstado LEFT JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo
--consulta requisiciones aprobadas
 LEFT JOIN (SELECT R.TipOdt,R.NumOrden,R.IdCiaOdt,SUM(CASE WHEN (RQ.IdEstado='0002' OR RQ.NumAprob<=0) THEN 1 ELSE 0 END) AS ReqNoAprob
 ,SUM(CASE WHEN RQ.IdEstado<>'0002' AND RQ.NumAprob>0 THEN 1 ELSE 0 END) AS ReqAprob
  FROM Trn_MttoOrdenReq AS R INNER JOIN Trn_Requisicion AS RQ ON R.TipReq=RQ.TipDoc AND R.Requisicion=RQ.Requisicion AND R.IdCia=RQ.IdCia
  WHERE RQ.Anulado=0 GROUP BY R.TipOdt,R.NumOrden,R.IdCiaOdt) AS RS ON O.TipDoc=RS.TipOdt AND O.NumOrden=RS.NumOrden AND O.IdCia=RS.IdCiaOdt
--fin consulta requisiciones aprobadas
 WHERE O.TipDoc=@pmTipDoc AND O.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND O.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND O.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_Nomina_Cr] @pmtmNumero VARCHAR(5),@pmtmIdEmpleado VARCHAR(16)=Null
 AS 
 SELECT tmIdEmpleado,Apellidos,Nombres,tmItem,tmIdConcepto,tmDetalle,Concepto,tmClaseCon,tmCantDevg,tmUnidad,tmVrUnitario,tmVrTotDevg,tmCantDed,tmVrUnitario AS VrUndDed
,tmVrTotDed,tmNPrestamo,tmNCuota,tmVrOrigen,tmTarifa,tmDiasCalc,tmDiasNov,tmDiasCalc-tmDiasNov AS Dias,tmCodFondo,FD.Fondo AS tm_Fondo,tmVrBaseLiq,tmFecIni,tmFecFin,tmIdPeriodo
,tmFecUltLiq,tmFecIniPmd,tmFecFinPmd,tmLogPmdio,tmClaseLiq,tmOrigCargue,tmBasExceso,tmNitTercero,tmHoraInicial,tmHoraFinal,tmTipoInc
--Datos 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,FecNacmto,EsCondtor,Licencia,CatLicencia,VigLicencia,E.IdLugar AS CodLugar,LugarLic,TallaCam,TallaPan,TallaZap,E.Inactivo AS Emp_Inactvo
--datos del contrato
,E.NContrato AS NumContrato,C.IdTipCon AS CodTipCon,TipoContrato,FecIngreso,FecRetiro,Indefinido,C.IdCargo AS CodCargo,Cargo,C.IdArea AS CodArea,Area,C.IdDep AS CodDep,Dependencia,C.IdInstala AS CodInst,Instlacion,C.IdCCosto AS CodCcto,CCosto
,C.IdSubCos AS CodSubcen,SubCosto,C.IdNom AS CodNom,TipoNomina,C.TipoLiquida AS TipLiq,Asistencia,NoDevenga,C.IdClase AS CodClaRie,ClaseRiesgo,CR.Tarifa AS TarifaRie,VrSalario,VrAuxTrans,SalMinimo,SalIntegral,FPnomina,ATFinMes
,SegFinMes,DchDotacion,DchCafeteria,ModoRet,C.IdEstado AS IdEstdo,Estado,C.Inactivo AS Con_inactivo,C.IdJornada AS CodJnada,Jornada,C.IdUsuario AS Con_IdUsuari,Usuario
,pVehiculo,NumVeh,TipoAdmon,VehPropio
 FROM tm_Nomina AS N INNER JOIN Empleados AS E ON N.tmIdEmpleado=E.IdEmpleado
 INNER JOIN NomConceptos AS CN ON N.tmIdConcepto=CN.IdConcepto
 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 Localidades AS L ON E.IdLugarCed=L.IdLocal
 INNER JOIN  Emp_Contrato AS C ON E.NContrato=C.NContrato  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 Dependencias AS DP ON C.IdDep=DP.IdDep  INNER JOIN Instalaciones AS I ON C.IdInstala=I.IdInstala
 INNER JOIN CentroCosto AS CO ON C.IdCCosto=CO.IdCCosto  INNER JOIN SubCentros AS SC ON C.IdSubCos=SC.IdSubCos
 INNER JOIN TiposNom AS TN ON C.IdNom=TN.IdNom INNER JOIN EstadoDoc AS EN ON C.IdEstado=EN.IdEstado
 INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario  INNER JOIN ClaseRie AS CR ON C.IdClase=CR.IdClase 
 INNER JOIN Jornadas AS J ON C.IdJornada=J.IdJornada  LEFT JOIN Fondos AS FD ON N.tmCodFondo=FD.IdFondo
 LEFT JOIN NomVehiculos AS VH ON E.pVehiculo=VH.IdVehiculo
 WHERE tmNumero=@pmtmNumero  AND tmIdEmpleado like ISNULL(@pmtmIdEmpleado,'%')
 ORDER BY Apellidos,Nombres,tmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryEmp_ContratoEca] @pmIdCargo VARCHAR(4)=Null,@pmIdDep VARCHAR(4)=Null,@pmIdArea VARCHAR(4)=Null,@pmIdInstala VARCHAR(4)=Null,@pmIdNom VARCHAR(4)=Null
,@pmIdEstado VARCHAR(4)=Null,@pmInactivo BIT=Null,@pmIdProf VARCHAR(4)=Null,@pmSexo CHAR(1)=Null,@pmEsCondtor BIT=Null,@pmFecNacmtoIni SMALLDATETIME=Null,@pmFecNacmtoFin SMALLDATETIME=Null
AS 
 SELECT C.IdEmpleado AS IdEmplado,Apellidos,Nombres,C.NContrato AS NCntrato,TipoId,Codigo,E.Direccion AS Dirccion,E.IdLocal AS CodCiu,L.Localidad AS Ciudad,Telefono,TelMovil,e_mail,VvdaPropia,FecNacmto,LugarNacmto,TipoSangre,FactorRh,Sexo
,IdEstCivil,EstCivil,NHijos,TallaCam,TallaPan,TallaZap,UsaGafas,E.IdProf AS CodProf,Profesion,LibMilitar,ClaseLib,FechaLib,DistMil,CertJudicial,FecJudicial,VigJudicial,Pasporte,VigPaspte,Licencia,E.IdLugar AS CodLugar,LugarLic,CatLicencia,FecLicencia,VigLicencia
,IdLugarCed,LC.Localidad AS LugarCed,Conyugue,ConyFecNac,ConyTrabaja,ConyEmpresa,ConyCargo,ConyFing,ConyTel,E.IdClase AS CodClase,ClaseCuenta,NumCuenta,E.IdBanco AS CodBco,Banco,IdFonPen,FP.Fondo AS FondPen,IdFonEps,FS.Fondo AS FondEps
,IdFonArp,FR.Fondo AS FondArp,IdFonCes,FC.Fondo AS FondCes,IdCajaCom,CC.Fondo AS CajaComp,FecVincula,FecUltAum,FecUltCes,FecUltPri,FecUltVac,FecUltDot,FecUltExm,FecUltCap,NCAnterior,FecIngAnt,FecRetAnt,EsCondtor,pVehiculo
,FotoEmp,FotoLic,FotoFirma,DsoSabado,FecFinPrueba,TipoPrendDot,TipoMedSent,Observacion,E.IdEstado AS Emp_IdEstado,ET.Estado AS Emp_estado,E.Inactivo AS Emp_Inactivo,ET.NColor AS EmpEstcolor,E.IdUsuario AS Emp_IdUsuario,UE.Usuario AS Emp_Usuario
--datos del contrato
,C.IdTipCon AS CodTCon,TipoContrato,FecIngreso,FecVigencia,FecRetiro,Indefinido,C.IdCargo AS CodCargo,Cargo,C.IdJornada AS IdJrnada,Jornada,C.IdArea AS CodArea,Area,C.IdDep AS CodDep,Dependencia,C.IdInstala AS IdInstla,Instlacion
,C.IdCCosto AS IdCCto,CCosto,C.IdSubCos AS IdSubcen,SubCosto,C.IdCia AS CodCia,Compania,C.IdClase AS CodClaRie,ClaseRiesgo,Tarifa,TipoTrabj,C.IdNom AS CodNom,TipoNomina,C.TipoLiquida AS TipoLiq,VrSalario,VrAuxTrans,SalMinimo,SalIntegral
,DchDotacion,DchCafeteria,Reclmto,Notas,C.TipoTraspaso,C.FechaTraspaso,C.ContratoRef,C.IdEstado AS IdEstdCon,EC.Estado AS EstdoCon,EC.NColor AS ConEstcolor,C.Inactivo AS Inactvo,C.IdUsuario AS IdUsuari,U.Usuario AS Con_Usuario,SubTipoCot,C.IdMedPago
 FROM Emp_Contrato AS C INNER JOIN Empleados AS E ON C.IdEmpleado=E.IdEmpleado INNER JOIN TiposCon AS TC ON C.IdTipCon=TC.IdTipCon
 INNER JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo INNER JOIN Jornadas AS J ON C.IdJornada=J.IdJornada
 INNER JOIN Dependencias AS D ON C.IdDep=D.IdDep  INNER JOIN Areas AS AR ON C.IdArea=AR.IdArea
 INNER JOIN Instalaciones AS I ON C.IdInstala=I.IdInstala  INNER JOIN CentroCosto AS CO ON C.IdCCosto=CO.IdCCosto
 INNER JOIN SubCentros AS SC ON C.IdSubCos=SC.IdSubCos INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia
 INNER JOIN TiposNom AS TN ON C.IdNom=TN.IdNom INNER JOIN EstadoDoc AS EC ON C.IdEstado=EC.IdEstado 
 INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario  INNER JOIN ClaseRie AS CR ON C.IdClase=CR.IdClase
 INNER JOIN Localidades AS L ON E.IdLocal=L.IdLocal INNER JOIN Localidades AS LC ON E.IdLugarCed=LC.IdLocal
 INNER JOIN EstadoCiv AS ECV ON E.IdEstCivil=ECV.IdEstado INNER JOIN ExpLicencias AS LL ON E.IdLugar=LL.IdLugar
 INNER JOIN Profesiones AS P ON E.IdProf=P.IdProf 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 FC ON E.IdFonCes=FC.IdFondo INNER JOIN Fondos AS CC ON E.IdCajaCom=CC.IdFondo
 INNER JOIN ClaseCta AS CT ON E.IdClase=CT.IdClase  INNER JOIN Bancos AS B ON E.IdBanco=B.IdBanco
 INNER JOIN EstadoTer AS ET ON E.IdEstado=ET.IdEstado INNER JOIN adm_Usuarios AS UE ON E.IdUsuario=UE.IdUsuario
 WHERE   C.IdCargo LIKE ISNULL(@pmIdCargo,'%') AND C.IdDep LIKE ISNULL(@pmIdDep,'%') AND C.IdArea LIKE ISNULL(@pmIdArea,'%')
 AND C.IdInstala LIKE ISNULL(@pmIdInstala,'%')  AND C.IdNom LIKE ISNULL(@pmIdNom,'%')  AND C.IdEstado LIKE ISNULL(@pmIdEstado,'%') 
 AND Sexo LIKE ISNULL(@pmSexo,'%') AND E.IdProf LIKE ISNULL(@pmIdProf,'%')
 AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
 AND (EsCondtor=ISNULL(@pmEsCondtor,0) or EsCondtor=ISNULL(@pmEsCondtor,1))
 AND (FecNacmto>=ISNULL(@pmFecNacmtoIni,CAST('19100101' AS SMALLDATETIME)) AND FecNacmto<=ISNULL(@pmFecNacmtoFin,CAST('20781230' AS SMALLDATETIME)))
 ORDER BY Apellidos,Nombres,C.NContrato
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNomNov_Cr] @pmIdPeriodo VARCHAR(8),@pmIdEmpleado VARCHAR(16)=Null
 AS 
 SELECT IdPeriodo,N.IdEmpleado AS IdEmpledo,Apellidos,Nombres,E.NContrato AS NumContrato,Item,N.IdConcepto AS IdConcept,Concepto,Detalle,Cantidad,VrUnitario,VrTotal,N.Tarifa AS TrfaBase,VrOrigen,ClaseCon,Unidad,TimeSys
,N.FecUpdate AS FecUpd,N.IdUsuario AS IdUsuari,Usuario,OrigCargue,Num_Contrato,Nit_Tercero,T.RazonSocial AS NomTercero,N.HoraInicial,N.HoraFinal
--Datos empleado
,E.Codigo,E.IdLugarCed,Localidad,E.Direccion AS Dirccion,E.Telefono,E.TelMovil,E.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
--datos del contrato
,C.IdTipCon AS CodTipCon,TipoContrato,FecIngreso,FecRetiro,Indefinido,C.IdCargo AS CodCargo,Cargo,C.IdArea AS CodArea,Area,C.IdDep AS CodDep,Dependencia,C.IdInstala AS CodInst,Instlacion,C.IdCCosto AS CodCcto,CCosto
,C.IdSubCos AS CodSubcen,SubCosto,C.IdNom AS CodNom,TipoNomina,C.TipoLiquida AS TipLiq,Asistencia,NoDevenga,C.IdClase AS CodClaRie,ClaseRiesgo,CR.Tarifa AS TarifaRie,VrSalario,VrAuxTrans,SalMinimo,SalIntegral,FPnomina,ATFinMes
,SegFinMes,DchDotacion,DchCafeteria,C.IdEstado AS IdEstdo,Estado,C.Inactivo AS Con_inactivo,C.IdJornada AS CodJnada,Jornada
 FROM Trn_NomNov AS N INNER JOIN Empleados AS E ON N.IdEmpleado=E.IdEmpleado  INNER JOIN  NomConceptos AS CN ON N.IdConcepto=CN.IdConcepto
 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
 LEFT JOIN  Emp_Contrato AS C ON E.NContrato=C.NContrato  LEFT JOIN TiposCon AS TC ON C.IdTipCon=TC.IdTipCon
 LEFT JOIN Dependencias AS DP ON C.IdDep=DP.IdDep  LEFT JOIN Instalaciones AS I ON C.IdInstala=I.IdInstala
 LEFT JOIN CentroCosto AS CO ON C.IdCCosto=CO.IdCCosto  LEFT JOIN SubCentros AS SC ON C.IdSubCos=SC.IdSubCos
 LEFT JOIN TiposNom AS TN ON C.IdNom=TN.IdNom LEFT JOIN EstadoDoc AS EN ON C.IdEstado=EN.IdEstado 
 LEFT JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo LEFT JOIN Areas AS AR ON C.IdArea=AR.IdArea
 LEFT JOIN ClaseRie AS CR ON C.IdClase=CR.IdClase LEFT JOIN Jornadas AS J ON C.IdJornada=J.IdJornada
 LEFT JOIN Terceros AS T ON N.Nit_Tercero=T.IdTercero
 WHERE IdPeriodo=@pmIdPeriodo AND N.IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%')
 ORDER BY  N.IdEmpleado,Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryEmp_ContratoDso] @pmIdEmpleado VARCHAR(16)=Null,@pmIndefinido BIT=Null,@pmIdTipCon VARCHAR(4)=Null,@pmIdCargo VARCHAR(4)=Null,@pmIdDep VARCHAR(4)=Null
,@pmIdArea VARCHAR(4)=Null,@pmIdInstala VARCHAR(4)=Null,@pmIdCCosto VARCHAR(16)=Null,@pmIdSubCos VARCHAR(16)=Null,@pmIdNom VARCHAR(4)=Null,@pmTipoLiquida CHAR(1)=Null
,@pmSalMinimo BIT=Null,@pmSalIntegral BIT=Null,@pmFPnomina VARCHAR(10)=Null,@pmModoRet INT=Null,@pmIdClase VARCHAR(4)=Null,@pmInactivo BIT=Null,@pmIdEstado VARCHAR(4)=Null
,@pmNContrato INT=Null,@pmFecIngresoIni SMALLDATETIME=Null,@pmFecIngresoFin SMALLDATETIME=Null,@pmATFinMes BIT=Null,@pmSegFinMes BIT=Null,@pmDchDotacion BIT=Null
--parametros de empleados
,@pmTipoSangre CHAR(2)=Null,@pmFactorRh CHAR(1)=Null,@pmSexo CHAR(1)=Null,@pmIdEstCivil VARCHAR(4)=Null,@pmClaseLib INT=Null, @pmIdProf VARCHAR(4)=Null,@pmTallaCam VARCHAR(5)=Null
,@pmTallaPan VARCHAR(5)=Null,@pmTallaZap VARCHAR(5)=Null,@pmIdFonPen VARCHAR(8)=Null,@pmIdFonEps VARCHAR(8)=Null,@pmIdFonArp VARCHAR(8)=Null,@pmIdFonCes VARCHAR(8)=Null,@pmIdCajaCom VARCHAR(8)=Null
,@pmIdBanco VARCHAR(4)=Null,@pmEmp_IdEstado VARCHAR(4)=Null,@pmEmp_Inactivo BIT=Null,@pmEsCondtor BIT=Null,@pmFecNacmtoIni SMALLDATETIME=Null,@pmFecNacmtoFin SMALLDATETIME=Null
 AS 
 SELECT  C.IdEmpleado AS IdEmplado,Apellidos,Nombres,C.NContrato AS NCntrato,C.IdTipCon AS CodTCon,TipoContrato,FecIngreso,FecVigencia,FecRetiro,Indefinido,C.IdCargo AS CodCargo,Cargo
,C.IdJornada AS IdJrnada,Jornada,C.IdArea AS CodArea,Area,C.IdDep AS CodDep,Dependencia,C.IdInstala AS IdInstla,Instlacion,C.IdCCosto AS IdCCto,CCosto,C.IdSubCos AS IdSubcen,SubCosto,C.IdCia AS CodCia,Compania
,C.IdClase AS CodClaRie,ClaseRiesgo,Tarifa,TipoTrabj,C.IdNom AS CodNom,TipoNomina,C.TipoLiquida AS TipoLiq,ModPromdio,VrSalario,VrAuxTrans,SalMinimo,SalIntegral,FPnomina,ModoRet,FecAfiSeg,ATFinMes,SegFinMes,FspFinmes,DenyPEN,DenyEPS,DenyARP
,DenyCaj,DenyCes,DenyPri,DenyVac,DenyHex,DenyDcto,BasePrest,BaseSeg,DchDotacion,DchCafeteria,Reclmto,Notas,DsoSabado,FecFinPrueba,TipoPrendDot,TipoMedSent,BenefPacto,C.TipoTraspaso,C.FechaTraspaso,C.ContratoRef,C.IdMedPago,MedioPago
,C.IdEstado AS IdEstdCon,EC.Estado AS EstdoCon,EC.NColor AS ConEstcolor,C.Inactivo AS Inactvo,C.FecAdd AS Fec_Add,C.FecUpdate AS Fec_Update,C.IdUsuario AS IdUsuari,U.Usuario AS Con_Usuario,IdCot,C.IdGrupo AS GrupAR,VrUpc
--empleados
,E.Direccion AS Dirccion,E.IdLocal AS CodCiu,L.Localidad AS Ciudad,Telefono,TelMovil,e_mail,VvdaPropia,FecNacmto,LugarNacmto,TipoSangre,FactorRh,Sexo,IdEstCivil,EstCivil,NHijos,TallaCam,TallaPan,TallaZap,UsaGafas,E.IdProf AS CodProf,Profesion
,LibMilitar,ClaseLib,FechaLib,DistMil,CertJudicial,FecJudicial,VigJudicial,Pasporte,VigPaspte,Licencia,E.IdLugar AS CodLugar,LugarLic,CatLicencia,FecLicencia,VigLicencia,IdLugarCed,LC.Localidad AS LugarCed,Conyugue,ConyFecNac,ConyTrabaja
,ConyEmpresa,ConyCargo,ConyFing,ConyTel,E.IdClase AS CodClase,ClaseCuenta,NumCuenta,E.IdBanco AS CodBco,Banco,IdFonPen,FP.Fondo AS FondPen,IdFonEps,FS.Fondo AS FondEps,IdFonArp,FR.Fondo AS FondArp,IdFonCes,FC.Fondo AS FondCes
,IdCajaCom,CC.Fondo AS CajaComp,FecVincula,FecUltAum,FecUltCes,FecUltPri,FecUltVac,FecUltDot,FecUltExm,FecUltCap,NCAnterior,FecIngAnt,FecRetAnt,EsCondtor,pVehiculo,FotoEmp,FotoLic,FotoFirma,Observacion,E.IdEstado AS Emp_IdEstado
,ET.Estado AS Emp_estado,E.Inactivo AS Emp_Inactivo,TipoId,Codigo,ET.NColor AS EmpEstcolor,E.FechaAdd AS EmpFecAdd,E.FechaUpdate AS EmpFecupd,E.IdUsuario AS Emp_IdUsuario,UE.Usuario AS Emp_Usuario,SubTipoCot
 FROM Emp_Contrato AS C INNER JOIN Empleados AS E ON C.IdEmpleado=E.IdEmpleado INNER JOIN TiposCon AS TC ON C.IdTipCon=TC.IdTipCon
 INNER JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo INNER JOIN Jornadas AS J ON C.IdJornada=J.IdJornada
 INNER JOIN Dependencias AS D ON C.IdDep=D.IdDep  INNER JOIN Areas AS AR ON C.IdArea=AR.IdArea
 INNER JOIN Instalaciones AS I ON C.IdInstala=I.IdInstala  INNER JOIN CentroCosto AS CO ON C.IdCCosto=CO.IdCCosto
 INNER JOIN SubCentros AS SC ON C.IdSubCos=SC.IdSubCos INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia
 INNER JOIN TiposNom AS TN ON C.IdNom=TN.IdNom INNER JOIN EstadoDoc AS EC ON C.IdEstado=EC.IdEstado 
 INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario  INNER JOIN ClaseRie AS CR ON C.IdClase=CR.IdClase
 INNER JOIN Localidades AS L ON E.IdLocal=L.IdLocal INNER JOIN Localidades AS LC ON E.IdLugarCed=LC.IdLocal
 INNER JOIN EstadoCiv AS ECV ON E.IdEstCivil=ECV.IdEstado INNER JOIN ExpLicencias AS LL ON E.IdLugar=LL.IdLugar
 INNER JOIN Profesiones AS P ON E.IdProf=P.IdProf 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 FC ON E.IdFonCes=FC.IdFondo INNER JOIN Fondos AS CC ON E.IdCajaCom=CC.IdFondo
 INNER JOIN ClaseCta AS CT ON E.IdClase=CT.IdClase  INNER JOIN Bancos AS B ON E.IdBanco=B.IdBanco
 INNER JOIN EstadoTer AS ET ON E.IdEstado=ET.IdEstado INNER JOIN adm_Usuarios AS UE ON E.IdUsuario=UE.IdUsuario
 LEFT JOIN NomMediosPago AS MP ON C.IdMedPago=MP.IdMedPago
 WHERE C.IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%')  AND C.IdTipCon LIKE ISNULL(@pmIdTipCon,'%') AND C.IdCargo LIKE ISNULL(@pmIdCargo,'%')
 AND C.IdDep LIKE ISNULL(@pmIdDep,'%') AND C.IdArea LIKE ISNULL(@pmIdArea,'%') AND C.IdInstala LIKE ISNULL(@pmIdInstala,'%')
 AND C.IdCCosto LIKE ISNULL(@pmIdCCosto,'%') AND C.IdSubCos LIKE ISNULL(@pmIdSubCos,'%')  AND C.IdNom LIKE ISNULL(@pmIdNom,'%') 
 AND C.TipoLiquida LIKE ISNULL(@pmTipoLiquida,'%') AND FPnomina LIKE ISNULL(@pmFPnomina,'%')  AND C.IdClase LIKE ISNULL(@pmIdClase,'%')
 AND C.IdEstado LIKE ISNULL(@pmIdEstado,'%') 
 AND (C.NContrato>=ISNULL(@pmNContrato,0)  AND C.NContrato<=ISNULL(@pmNContrato,2147483647)) 
 AND (ModoRet>=ISNULL(@pmModoRet,0)  AND ModoRet<=ISNULL(@pmModoRet,2147483647)) 
 AND (SalMinimo=ISNULL(@pmSalMinimo,0) or SalMinimo=ISNULL(@pmSalMinimo,1))
 AND (Indefinido=ISNULL(@pmIndefinido,0) or Indefinido=ISNULL(@pmIndefinido,1))
 AND (SalIntegral=ISNULL(@pmSalIntegral,0) or SalIntegral=ISNULL(@pmSalIntegral,1))
 AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
 AND (ATFinMes=ISNULL(@pmATFinMes,0) or ATFinMes=ISNULL(@pmATFinMes,1))
 AND (SegFinMes=ISNULL(@pmSegFinMes,0) or SegFinMes=ISNULL(@pmSegFinMes,1))
 AND (DchDotacion=ISNULL(@pmDchDotacion,0) or DchDotacion=ISNULL(@pmDchDotacion,1))
 AND (FecIngreso>=ISNULL(@pmFecIngresoIni,CAST('19100101' AS SMALLDATETIME)) AND FecIngreso<=ISNULL(@pmFecIngresoFin,CAST('20781230' AS SMALLDATETIME)))
 AND TipoSangre LIKE ISNULL(@pmTipoSangre,'%%') AND FactorRh LIKE ISNULL(@pmFactorRh,'%') AND Sexo LIKE ISNULL(@pmSexo,'%')
 AND IdEstCivil LIKE ISNULL(@pmIdEstCivil,'%') AND E.IdProf LIKE ISNULL(@pmIdProf,'%') AND TallaCam LIKE ISNULL(@pmTallaCam,'%%')
 AND TallaPan LIKE ISNULL(@pmTallaPan,'%%') AND TallaZap LIKE ISNULL(@pmTallaZap,'%%') AND IdFonPen LIKE ISNULL(@pmIdFonPen,'%')
 AND IdFonCes LIKE ISNULL(@pmIdFonCes,'%') AND IdFonArp LIKE ISNULL(@pmIdFonArp,'%') AND IdFonEps LIKE ISNULL(@pmIdFonEps,'%')
 AND IdCajaCom LIKE ISNULL(@pmIdCajaCom ,'%') AND E.IdBanco LIKE ISNULL(@pmIdBanco,'%') AND E.IdEstado LIKE ISNULL(@pmEmp_IdEstado,'%')
 AND (ClaseLib>=ISNULL(@pmClaseLib,0)  AND ClaseLib<=ISNULL(@pmClaseLib,2147483647)) 
 AND (E.Inactivo=ISNULL(@pmEmp_Inactivo,0) or E.Inactivo=ISNULL(@pmEmp_Inactivo,1))
 AND (EsCondtor=ISNULL(@pmEsCondtor,0) or EsCondtor=ISNULL(@pmEsCondtor,1))
 AND (FecNacmto>=ISNULL(@pmFecNacmtoIni,CAST('19100101' AS SMALLDATETIME)) AND FecNacmto<=ISNULL(@pmFecNacmtoFin,CAST('20781230' AS SMALLDATETIME)))
 ORDER BY Apellidos,Nombres,C.NContrato
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInstm_Nomina_Vac]  @pmIdEmpleado VARCHAR(16),@pmNumero INT,@pmtmNumero VARCHAR(5)
 AS 
 INSERT INTO tm_Nomina (tmNumero,tmItem,tmIdEmpleado,tmIdConcepto,tmClaseCon,tmDetalle,tmCantDevg,tmCantDed,tmVrUnitario,tmVrTotDevg,tmVrTotDed,tmUnidad
,tmClaseLiq,tmVrOrigen,tmTarifa,tmDiasCalc,tmDiasNov,tmCodFondo,tmNPrestamo,tmNCuota,tmOrigCargue,tmVrBaseLiq,tmNContto,tmFecIni,tmFecFin,tmIdPeriodo,tmVrPension,tmVrSalud,tmBasExceso,tmNitTercero,tmHoraInicial,tmHoraFinal,tmTipoInc)
 SELECT @pmtmNumero,Item,IdEmpleado,D.IdConcepto,ClaseCon,Detalle,0,Cantidad,0,VrDevgado,VrDeducido,Unidad,'NOMINA',VrBaseLiq,D.Tarifa,0,0,'',NPrestamo,NCuota,'VAC',VrCertfcado
,0,getdate(),getdate(),IdPeriodo,VrPension,VrSalud,0,'',Null,Null,''
 FROM Trn_NomVacDet  AS D INNER JOIN NomConceptos AS C ON D.IdConcepto=C.IdConcepto
 WHERE IdEmpleado=@pmIdEmpleado AND Numero=@pmNumero
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInstm_Nomina_Liq]  @pmIdEmpleado VARCHAR(16),@pmNumero INT,@pmtmNumero VARCHAR(5)
 AS 
 INSERT INTO tm_Nomina (tmNumero,tmItem,tmIdEmpleado,tmIdConcepto,tmClaseCon,tmDetalle,tmCantDevg,tmCantDed,tmVrUnitario,tmVrTotDevg,tmVrTotDed,tmUnidad
,tmClaseLiq,tmVrOrigen,tmTarifa,tmDiasCalc,tmDiasNov,tmCodFondo,tmNPrestamo,tmNCuota,tmOrigCargue,tmVrBaseLiq,tmNContto,tmFecIni,tmFecFin,tmIdPeriodo,tmFecUltLiq
,tmFecIniPmd,tmFecFinPmd,tmLogPmdio,tmVrPension,tmVrSalud,tmBasExceso,tmNitTercero,tmHoraInicial,tmHoraFinal,tmTipoInc)
 SELECT @pmtmNumero,Item,D.IdEmpleado,D.IdConcepto,ClaseCon,Detalle
,'CntDev'= CASE  WHEN VrDeducido<=0 THEN Cantidad ELSE 0 END
,'CntDed'= CASE  WHEN VrDeducido>0 THEN Cantidad ELSE 0  END
,VrUnitario,VrDevngado,VrDeducido,Unidad,ClaseLiq,VrBase,D.Tarifa,DiasBase,D.DiasNoRem,CodFondo
,NPrestamo,NCuota,'LIQUDACION',VrBaseLiq,NContrato,FecDesde,FecHasta,IdPeriodo,FecUltLiq,FecIniPmd,FecFinPmd,LogPmdio,0,0,0,'',Null,Null,''
 FROM Trn_NomRetDet AS D INNER JOIN Trn_NomRet AS R ON D.IdEmpleado=R.IdEmpleado AND D.Numero=R.Numero
 INNER JOIN  NomConceptos AS C ON D.IdConcepto=C.IdConcepto
 WHERE D.IdEmpleado=@pmIdEmpleado AND D.Numero=@pmNumero  AND  ClaseLiq NOT IN  ('APORTE','PROVISION')
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQrytm_Nomina] @pmtmNumero VARCHAR(5),@pmtmIdEmpleado VARCHAR(16),@pmtmItem INT
 AS 
 SELECT tmNumero,tmIdEmpleado,tmItem,tmIdConcepto,tmClaseCon,tmDetalle,tmCantDevg,tmCantDed,tmVrUnitario,tmVrTotDevg,tmVrTotDed,tmUnidad,tmClaseLiq
,tmVrOrigen,tmTarifa,tmDiasCalc,tmDiasNov,tmCodFondo,tmNPrestamo,tmNCuota,tmOrigCargue,tmVrBaseLiq,tmNContto,tmFecIni,tmFecFin,tmIdPeriodo
,tmFecUltLiq,tmFecIniPmd,tmFecFinPmd,tmLogPmdio,tmVrPension,tmVrSalud,tmBasExceso,tmNitTercero,tmHoraInicial,tmHoraFinal,tmTipoInc
 FROM tm_Nomina
 WHERE tmNumero=@pmtmNumero AND tmIdEmpleado like ISNULL(@pmtmIdEmpleado,'%')
 AND (tmItem>=ISNULL(@pmtmItem,0)  AND tmItem<=ISNULL(@pmtmItem,2147483647))
ORDER BY tmIdEmpleado,tmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_NominaDsa] @pmtmNumero VARCHAR(5),@pmtmItem INT,@pmtmIdEmpleado VARCHAR(16)=Null
 AS 
 SELECT tmIdEmpleado,Apellidos,Nombres,tmItem,tmIdConcepto,tmDetalle,tmClaseCon,tmCantDevg,tmUnidad,tmVrUnitario,tmVrTotDevg,tmCantDed,tmVrUnitario AS VrUndDed
,tmVrTotDed,tmNPrestamo,tmNCuota,tmVrOrigen,tmTarifa,tmDiasCalc,tmDiasNov,tmDiasCalc-tmDiasNov AS Dias,tmCodFondo,tmVrBaseLiq,tmHoraInicial,tmHoraFinal,tmTipoInc
 FROM tm_Nomina AS N INNER JOIN Empleados AS E ON N.tmIdEmpleado=E.IdEmpleado
 WHERE tmNumero=@pmtmNumero  AND tmItem>=@pmtmItem
 AND tmIdEmpleado like ISNULL(@pmtmIdEmpleado,'%')  --AND tmClaseLiq IN ('NOMINA','NOVEDAD')
 ORDER BY Apellidos,Nombres,tmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_NominaDso] @pmtmNumero VARCHAR(5),@pmtmIdEmpleado VARCHAR(16)
 AS 
 SELECT tmItem,tmIdConcepto,tmDetalle,tmClaseCon,tmCantDevg,tmUnidad,tmVrUnitario,tmVrTotDevg,tmCantDed,tmVrUnitario AS VrUndDed
,tmVrTotDed,tmNPrestamo,tmNCuota,tmVrOrigen,tmTarifa,tmDiasCalc,tmDiasNov,tmDiasCalc-tmDiasNov AS Dias,tmIdEmpleado
,tmHoraInicial,tmHoraFinal,tmTipoInc
 FROM tm_Nomina
 WHERE tmNumero=@pmtmNumero  AND tmClaseLiq IN ('NOMINA','NOVEDAD')
 AND tmIdEmpleado like ISNULL(@pmtmIdEmpleado,'%')
ORDER BY tmIdEmpleado,tmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUptm_Nomina] @pmtmNumero VARCHAR(5),@pmtmIdEmpleado VARCHAR(16),@pmtmItem INT,@pmtmIdConcepto VARCHAR(4),@pmtmClaseCon VARCHAR(10),@pmtmDetalle VARCHAR(100),@pmtmCantDevg DECIMAL(10,4)
,@pmtmCantDed DECIMAL(10,4),@pmtmVrUnitario MONEY,@pmtmVrTotDevg MONEY,@pmtmVrTotDed MONEY,@pmtmUnidad VARCHAR(10),@pmtmClaseLiq VARCHAR(10),@pmtmVrOrigen MONEY,@pmtmTarifa DECIMAL(10,4),@pmtmDiasCalc DECIMAL(10,4)
,@pmtmDiasNov DECIMAL(10,4),@pmtmCodFondo VARCHAR(8),@pmtmNPrestamo INT,@pmtmNCuota INT,@pmtmOrigCargue VARCHAR(10),@pmtmVrBaseLiq MONEY,@pmtmNContto INT,@pmtmFecIni SMALLDATETIME,@pmtmFecFin SMALLDATETIME,@pmtmIdPeriodo VARCHAR(8)
,@pmtmVrPension MONEY,@pmtmVrSalud MONEY,@pmtmBasExceso MONEY,@pmtmNitTercero VARCHAR(16),@pmtmHoraInicial DATETIME,@pmtmHoraFinal DATETIME,@pmtmTipoInc VARCHAR(20)
 AS 
 UPDATE tm_Nomina SET tmIdConcepto=@pmtmIdConcepto,tmClaseCon=@pmtmClaseCon,tmDetalle=@pmtmDetalle,tmCantDevg=@pmtmCantDevg,tmCantDed=@pmtmCantDed,tmVrUnitario=@pmtmVrUnitario,tmVrTotDevg=@pmtmVrTotDevg,tmVrTotDed=@pmtmVrTotDed
,tmUnidad=@pmtmUnidad,tmClaseLiq=@pmtmClaseLiq,tmVrOrigen=@pmtmVrOrigen,tmTarifa=@pmtmTarifa,tmDiasCalc=@pmtmDiasCalc,tmDiasNov=@pmtmDiasNov,tmCodFondo=@pmtmCodFondo,tmNPrestamo=@pmtmNPrestamo,tmNCuota=@pmtmNCuota,tmOrigCargue=@pmtmOrigCargue
,tmVrBaseLiq=@pmtmVrBaseLiq,tmNContto=@pmtmNContto,tmFecIni=@pmtmFecIni,tmFecFin=@pmtmFecFin,tmIdPeriodo=@pmtmIdPeriodo,tmVrPension=@pmtmVrPension,tmVrSalud=@pmtmVrSalud,tmBasExceso=@pmtmBasExceso,tmNitTercero=@pmtmNitTercero
,tmHoraInicial=@pmtmHoraInicial,tmHoraFinal=@pmtmHoraFinal,tmTipoInc=@pmtmTipoInc
 WHERE tmNumero=@pmtmNumero AND tmIdEmpleado=@pmtmIdEmpleado AND tmItem=@pmtmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInstm_Nomina] @pmtmNumero VARCHAR(5),@pmtmIdEmpleado VARCHAR(16),@pmtmItem INT,@pmtmIdConcepto VARCHAR(4),@pmtmClaseCon VARCHAR(10),@pmtmDetalle VARCHAR(100),@pmtmCantDevg DECIMAL(10,4),@pmtmCantDed DECIMAL(10,4),@pmtmVrUnitario MONEY,@pmtmVrTotDevg MONEY,@pmtmVrTotDed MONEY
,@pmtmUnidad VARCHAR(10),@pmtmClaseLiq VARCHAR(10),@pmtmVrOrigen MONEY,@pmtmTarifa DECIMAL(10,4),@pmtmDiasCalc DECIMAL(10,4),@pmtmDiasNov DECIMAL(10,4),@pmtmCodFondo VARCHAR(8),@pmtmNPrestamo INT,@pmtmNCuota INT,@pmtmOrigCargue VARCHAR(10),@pmtmVrBaseLiq MONEY,@pmtmNContto INT,@pmtmFecIni SMALLDATETIME
,@pmtmFecFin SMALLDATETIME,@pmtmIdPeriodo VARCHAR(8),@pmtmFecUltLiq SMALLDATETIME,@pmtmFecIniPmd SMALLDATETIME,@pmtmFecFinPmd SMALLDATETIME,@pmtmLogPmdio VARCHAR(600),@pmtmVrPension MONEY,@pmtmVrSalud MONEY,@pmtmBasExceso MONEY,@pmtmNitTercero VARCHAR(16),@pmtmHoraInicial DATETIME,@pmtmHoraFinal DATETIME,@pmtmTipoInc VARCHAR(20)
 AS 
 INSERT INTO tm_Nomina (tmNumero,tmIdEmpleado,tmItem,tmIdConcepto,tmClaseCon,tmDetalle,tmCantDevg,tmCantDed,tmVrUnitario,tmVrTotDevg,tmVrTotDed,tmUnidad,tmClaseLiq,tmVrOrigen,tmTarifa,tmDiasCalc,tmDiasNov,tmCodFondo,tmNPrestamo,tmNCuota,tmOrigCargue,tmVrBaseLiq,tmNContto,tmFecIni,tmFecFin
,tmIdPeriodo,tmFecUltLiq,tmFecIniPmd,tmFecFinPmd,tmLogPmdio,tmVrPension,tmVrSalud,tmBasExceso,tmNitTercero,tmHoraInicial,tmHoraFinal,tmTipoInc)
 VALUES (@pmtmNumero,@pmtmIdEmpleado,@pmtmItem,@pmtmIdConcepto,@pmtmClaseCon,@pmtmDetalle,@pmtmCantDevg,@pmtmCantDed,@pmtmVrUnitario,@pmtmVrTotDevg,@pmtmVrTotDed,@pmtmUnidad,@pmtmClaseLiq,@pmtmVrOrigen,@pmtmTarifa
,@pmtmDiasCalc,@pmtmDiasNov,@pmtmCodFondo,@pmtmNPrestamo,@pmtmNCuota,@pmtmOrigCargue,@pmtmVrBaseLiq,@pmtmNContto,@pmtmFecIni,@pmtmFecFin,@pmtmIdPeriodo,@pmtmFecUltLiq,@pmtmFecIniPmd,@pmtmFecFinPmd,@pmtmLogPmdio,@pmtmVrPension,@pmtmVrSalud,@pmtmBasExceso,@pmtmNitTercero,@pmtmHoraInicial,@pmtmHoraFinal,@pmtmTipoInc)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomConceptos] @pmIdConcepto VARCHAR(4)
 AS 
 SELECT IdConcepto,Concepto,ClaseCon,Unidad,Tarifa,BaseTrfa,Pdtermnado,Inactivo,TarifaEmp,DiasEmp,IdConcNE,FechaAdd,FechaUpdate,IdUsuario
 FROM NomConceptos
 WHERE IdConcepto=@pmIdConcepto
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNomConceptosLta] @pmClaseCon VARCHAR(10)=Null,@pmPdtermnado BIT=Null,@pmInactivo BIT=Null
 AS 
 SELECT C.IdConcepto,C.Concepto,C.ClaseCon,C.Unidad,C.Tarifa,C.BaseTrfa,C.Pdtermnado,C.TarifaEmp,C.DiasEmp
,C.IdConcNE,ConceptoNE,C.Inactivo,C.FechaAdd,C.FechaUpdate,C.IdUsuario
 FROM NomConceptos AS C LEFT JOIN NomConcDian AS CD ON C.IdConcNE=CD.IdConcNE
 WHERE C.ClaseCon LIKE ISNULL(@pmClaseCon,'%')
  AND  (Pdtermnado=ISNULL(@pmPdtermnado,0) or Pdtermnado=ISNULL(@pmPdtermnado,1))
  AND  (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
 ORDER BY C.Concepto
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsNomConceptos] @pmIdConcepto VARCHAR(4),@pmConcepto VARCHAR(100),@pmClaseCon VARCHAR(10),@pmUnidad VARCHAR(10),@pmTarifa DECIMAL(10,4)
,@pmBaseTrfa CHAR(2),@pmPdtermnado BIT,@pmInactivo BIT,@pmTarifaEmp DECIMAL(14,4),@pmDiasEmp INT,@pmIdConcNE VARCHAR(50),@pmFechaAdd SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO NomConceptos (IdConcepto,Concepto,ClaseCon,Unidad,Tarifa,BaseTrfa,Pdtermnado,Inactivo,FechaAdd,IdUsuario,TarifaEmp,DiasEmp,IdConcNE)
 VALUES (@pmIdConcepto,@pmConcepto,@pmClaseCon,@pmUnidad,@pmTarifa,@pmBaseTrfa,@pmPdtermnado,@pmInactivo,@pmFechaAdd,@pmIdUsuario,@pmTarifaEmp,@pmDiasEmp,@pmIdConcNE)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUpNomConceptos] @pmIdConcepto VARCHAR(4),@pmConcepto VARCHAR(100),@pmClaseCon VARCHAR(10),@pmUnidad VARCHAR(10),@pmTarifa DECIMAL(10,4)
,@pmBaseTrfa CHAR(2),@pmPdtermnado BIT,@pmInactivo BIT,@pmTarifaEmp DECIMAL(14,4),@pmDiasEmp INT,@pmIdConcNE VARCHAR(50),@pmFechaUpdate SMALLDATETIME
 AS 
 UPDATE NomConceptos SET Concepto=@pmConcepto,ClaseCon=@pmClaseCon,Unidad=@pmUnidad,Tarifa=@pmTarifa,BaseTrfa=@pmBaseTrfa,Pdtermnado=@pmPdtermnado,Inactivo=@pmInactivo
,TarifaEmp=@pmTarifaEmp,DiasEmp=@pmDiasEmp,IdConcNE=@pmIdConcNE,FechaUpdate=@pmFechaUpdate
 WHERE IdConcepto=@pmIdConcepto
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_AuxInv_SelVenCia] @pmtmEst CHAR(2),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME
,@pmIdCia CHAR(2)=Null,@pmIdProducto VARCHAR(16)=Null,@pmIdBodega VARCHAR(4)=Null,@pmTipoRef VARCHAR(10)=Null
,@pmIdLinea VARCHAR(8)=Null,@pmIdGrupo VARCHAR(8)=Null,@pmIdSubgrupo VARCHAR(8)=Null,@pmTanques BIT=Null
,@pmInactivo BIT=Null
 AS 
 INSERT INTO tm_AuxInv (tmEst,tmIdProducto,tmIdCia,tmIdBodega,tmNumLote,tmCdTanque,tmEntradas,tmSalidas,tmCostoEnt,tmCostoSal
 ,tmVtaCant,tmVtaCantDev,tmVtaTotal,tmVtaTotalDev,tmVtaCosto,tmVtaCostoDev,tmVtaIva,tmVtaIvaDev,tmVtaDcto,tmVtaDctoDev
 ,tmVtaRet,tmVtaRetDev,tmVtaIca,tmVtaIcaDev,tmVtaTasa,tmVtaTasaDev,tmVtaGlobal,tmVtaGlobalDev,tmVtaSol,tmVtaSolDev) 
 SELECT @pmtmEst,K.IdProducto,IdCia,'0','0','0',SUM(Entradas),SUM(Salidas),SUM(VrCostoEnt),SUM(VrCostoSal)
--variables de ventas
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN Salidas ELSE 0 END ) AS VtaCant
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN Entradas ELSE 0 END ) AS VtaCantDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrPrecio*(Salidas-ISNULL(CantObseq,0)) ELSE 0 END ) AS VtaTotal
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrPrecio*(Entradas-ISNULL(CantObseq,0)) ELSE 0 END ) AS VtaTotalDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrCostoSal ELSE 0 END ) AS VtaCosto
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrCostoEnt ELSE 0 END ) AS VtaCostoDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrIvaSal ELSE 0 END ) AS VtaIva
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrIvaEnt ELSE 0 END ) AS VtaIvaDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrDctoSal ELSE 0 END ) AS VtaDcto
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrDctoEnt ELSE 0 END ) AS VtaDctoDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrReteSal ELSE 0 END ) AS VtaRet
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrReteEnt ELSE 0 END ) AS VtaRetDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrIcaSal ELSE 0 END ) AS VtaIca
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrIcaEnt ELSE 0 END ) AS VtaIcaDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN Sobretasa*Salidas ELSE 0 END ) AS VtaTasa
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN Sobretasa*Entradas ELSE 0 END ) AS VtaTasaDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN ImpGlobal*Salidas ELSE 0 END ) AS VtaGlobal
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN ImpGlobal*Entradas ELSE 0 END ) AS VtaGlobalDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN Soldicom*Salidas ELSE 0 END ) AS VtaSol
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN Soldicom*Entradas ELSE 0 END ) AS VtaSolDev
  FROM Trn_Kardex  AS K INNER JOIN ProdMcias  AS PM ON K.IdProducto=PM.IdProducto
  INNER JOIN SubGrupos AS S ON PM.IdSubgrupo=S.IdSubgrupo
  INNER JOIN Grupos AS G ON S.IdGrupo=G.IdGrupo  INNER JOIN Lineas AS LN ON G.IdLinea=LN.IdLinea
  WHERE TipDoc NOT IN ('ODC','PED','REM','COT','GUI','ODS','OCC','VCC')  AND  Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
  AND TipoRef<>'INSUMO'  AND  TipoRef LIKE ISNULL(@pmTipoRef,'%')
  AND  K.IdCia LIKE ISNULL(@pmIdCia,'%%') AND K.IdProducto LIKE ISNULL(@pmIdProducto,'%')  AND K.IdBodega LIKE ISNULL(@pmIdBodega,'%')
  AND G.IdLinea LIKE ISNULL(@pmIdLinea,'%')  AND S.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND PM.IdSubgrupo LIKE ISNULL(@pmIdSubgrupo ,'%')
  AND (Tanques=ISNULL(@pmTanques,0) or Tanques=ISNULL(@pmTanques,1))
  AND (PM.Inactivo=ISNULL(@pmInactivo,0) or PM.Inactivo=ISNULL(@pmInactivo,1))
 GROUP BY K.IdProducto,IdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_AuxInv_SelVenBod] @pmtmEst CHAR(2),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME
,@pmIdCia CHAR(2)=Null,@pmIdProducto VARCHAR(16)=Null,@pmIdBodega VARCHAR(4)=Null,@pmTipoRef VARCHAR(10)=Null
,@pmIdLinea VARCHAR(8)=Null,@pmIdGrupo VARCHAR(8)=Null,@pmIdSubgrupo VARCHAR(8)=Null,@pmTanques BIT=Null
,@pmInactivo BIT=Null
 AS 
 INSERT INTO tm_AuxInv (tmEst,tmIdProducto,tmIdCia,tmIdBodega,tmNumLote,tmCdTanque,tmEntradas,tmSalidas,tmCostoEnt,tmCostoSal
 ,tmVtaCant,tmVtaCantDev,tmVtaTotal,tmVtaTotalDev,tmVtaCosto,tmVtaCostoDev,tmVtaIva,tmVtaIvaDev,tmVtaDcto,tmVtaDctoDev
 ,tmVtaRet,tmVtaRetDev,tmVtaIca,tmVtaIcaDev,tmVtaTasa,tmVtaTasaDev,tmVtaGlobal,tmVtaGlobalDev,tmVtaSol,tmVtaSolDev) 
 SELECT @pmtmEst,K.IdProducto,'00',K.IdBodega,'0','0',SUM(Entradas),SUM(Salidas),SUM(VrCostoEnt),SUM(VrCostoSal)
--variables de ventas
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN Salidas ELSE 0 END ) AS VtaCant
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN Entradas ELSE 0 END ) AS VtaCantDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrPrecio*(Salidas-ISNULL(CantObseq,0)) ELSE 0 END ) AS VtaTotal
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrPrecio*(Entradas-ISNULL(CantObseq,0)) ELSE 0 END ) AS VtaTotalDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrCostoSal ELSE 0 END ) AS VtaCosto
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrCostoEnt ELSE 0 END ) AS VtaCostoDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrIvaSal ELSE 0 END ) AS VtaIva
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrIvaEnt ELSE 0 END ) AS VtaIvaDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrDctoSal ELSE 0 END ) AS VtaDcto
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrDctoEnt ELSE 0 END ) AS VtaDctoDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrReteSal ELSE 0 END ) AS VtaRet
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrReteEnt ELSE 0 END ) AS VtaRetDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrIcaSal ELSE 0 END ) AS VtaIca
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrIcaEnt ELSE 0 END ) AS VtaIcaDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN Sobretasa*Salidas ELSE 0 END ) AS VtaTasa
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN Sobretasa*Entradas ELSE 0 END ) AS VtaTasaDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN ImpGlobal*Salidas ELSE 0 END ) AS VtaGlobal
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN ImpGlobal*Entradas ELSE 0 END ) AS VtaGlobalDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN Soldicom*Salidas ELSE 0 END ) AS VtaSol
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN Soldicom*Entradas ELSE 0 END ) AS VtaSolDev
  FROM Trn_Kardex  AS K INNER JOIN ProdMcias  AS PM ON K.IdProducto=PM.IdProducto
  INNER JOIN SubGrupos AS S ON PM.IdSubgrupo=S.IdSubgrupo
  INNER JOIN Grupos AS G ON S.IdGrupo=G.IdGrupo  INNER JOIN Lineas AS LN ON G.IdLinea=LN.IdLinea
  WHERE TipDoc NOT IN ('ODC','PED','REM','COT','GUI','ODS','OCC','VCC')  AND  Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
  AND TipoRef<>'INSUMO'  AND  TipoRef LIKE ISNULL(@pmTipoRef,'%')
  AND  K.IdCia LIKE ISNULL(@pmIdCia,'%%') AND K.IdProducto LIKE ISNULL(@pmIdProducto,'%')  AND K.IdBodega LIKE ISNULL(@pmIdBodega,'%')
  AND G.IdLinea LIKE ISNULL(@pmIdLinea,'%')  AND S.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND PM.IdSubgrupo LIKE ISNULL(@pmIdSubgrupo ,'%')
  AND (Tanques=ISNULL(@pmTanques,0) or Tanques=ISNULL(@pmTanques,1))
  AND (PM.Inactivo=ISNULL(@pmInactivo,0) or PM.Inactivo=ISNULL(@pmInactivo,1))
 GROUP BY K.IdProducto,K.IdBodega
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_AuxInv_SelVen] @pmtmEst CHAR(2),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME
,@pmIdCia CHAR(2)=Null,@pmIdProducto VARCHAR(16)=Null,@pmIdBodega VARCHAR(4)=Null,@pmTipoRef VARCHAR(10)=Null
,@pmIdLinea VARCHAR(8)=Null,@pmIdGrupo VARCHAR(8)=Null,@pmIdSubgrupo VARCHAR(8)=Null,@pmTanques BIT=Null
,@pmInactivo BIT=Null
 AS 
 INSERT INTO tm_AuxInv (tmEst,tmIdProducto,tmIdCia,tmIdBodega,tmNumLote,tmCdTanque,tmEntradas,tmSalidas,tmCostoEnt,tmCostoSal
 ,tmVtaCant,tmVtaCantDev,tmVtaTotal,tmVtaTotalDev,tmVtaCosto,tmVtaCostoDev,tmVtaIva,tmVtaIvaDev,tmVtaDcto,tmVtaDctoDev
 ,tmVtaRet,tmVtaRetDev,tmVtaIca,tmVtaIcaDev,tmVtaTasa,tmVtaTasaDev,tmVtaGlobal,tmVtaGlobalDev,tmVtaSol,tmVtaSolDev) 
 SELECT @pmtmEst,K.IdProducto,'00','0','0','0',SUM(Entradas),SUM(Salidas),SUM(VrCostoEnt),SUM(VrCostoSal)
--variables de ventas
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN Salidas ELSE 0 END ) AS VtaCant
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN Entradas ELSE 0 END ) AS VtaCantDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrPrecio*(Salidas-ISNULL(CantObseq,0)) ELSE 0 END ) AS VtaTotal
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrPrecio*(Entradas-ISNULL(CantObseq,0)) ELSE 0 END ) AS VtaTotalDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrCostoSal ELSE 0 END ) AS VtaCosto
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrCostoEnt ELSE 0 END ) AS VtaCostoDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrIvaSal ELSE 0 END ) AS VtaIva
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrIvaEnt ELSE 0 END ) AS VtaIvaDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrDctoSal ELSE 0 END ) AS VtaDcto
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrDctoEnt ELSE 0 END ) AS VtaDctoDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrReteSal ELSE 0 END ) AS VtaRet
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrReteEnt ELSE 0 END ) AS VtaRetDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrIcaSal ELSE 0 END ) AS VtaIca
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrIcaEnt ELSE 0 END ) AS VtaIcaDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN Sobretasa*Salidas ELSE 0 END ) AS VtaTasa
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN Sobretasa*Entradas ELSE 0 END ) AS VtaTasaDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN ImpGlobal*Salidas ELSE 0 END ) AS VtaGlobal
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN ImpGlobal*Entradas ELSE 0 END ) AS VtaGlobalDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN Soldicom*Salidas ELSE 0 END ) AS VtaSol
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN Soldicom*Entradas ELSE 0 END ) AS VtaSolDev
  FROM Trn_Kardex  AS K INNER JOIN ProdMcias  AS PM ON K.IdProducto=PM.IdProducto
  INNER JOIN SubGrupos AS S ON PM.IdSubgrupo=S.IdSubgrupo
  INNER JOIN Grupos AS G ON S.IdGrupo=G.IdGrupo  INNER JOIN Lineas AS LN ON G.IdLinea=LN.IdLinea
  WHERE TipDoc NOT IN ('ODC','PED','REM','COT','GUI','ODS','OCC','VCC')  AND  Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
  AND TipoRef<>'INSUMO'  AND  TipoRef LIKE ISNULL(@pmTipoRef,'%')
  AND  K.IdCia LIKE ISNULL(@pmIdCia,'%%') AND K.IdProducto LIKE ISNULL(@pmIdProducto,'%')  AND K.IdBodega LIKE ISNULL(@pmIdBodega,'%')
  AND G.IdLinea LIKE ISNULL(@pmIdLinea,'%')  AND S.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND PM.IdSubgrupo LIKE ISNULL(@pmIdSubgrupo ,'%')
  AND (Tanques=ISNULL(@pmTanques,0) or Tanques=ISNULL(@pmTanques,1))
  AND (PM.Inactivo=ISNULL(@pmInactivo,0) or PM.Inactivo=ISNULL(@pmInactivo,1))
 GROUP BY K.IdProducto
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_AuxInv_SelVenTanqCia] @pmtmEst CHAR(2),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME
,@pmIdCia CHAR(2)=Null,@pmIdProducto VARCHAR(16)=Null,@pmIdBodega VARCHAR(4)=Null,@pmTipoRef VARCHAR(10)=Null
,@pmIdLinea VARCHAR(8)=Null,@pmIdGrupo VARCHAR(8)=Null,@pmIdSubgrupo VARCHAR(8)=Null,@pmInactivo BIT=Null
 AS 
 INSERT INTO tm_AuxInv (tmEst,tmIdProducto,tmIdCia,tmIdBodega,tmNumLote,tmCdTanque,tmEntradas,tmSalidas,tmCostoEnt,tmCostoSal
 ,tmVtaCant,tmVtaCantDev,tmVtaTotal,tmVtaTotalDev,tmVtaCosto,tmVtaCostoDev,tmVtaIva,tmVtaIvaDev,tmVtaDcto,tmVtaDctoDev
 ,tmVtaRet,tmVtaRetDev,tmVtaIca,tmVtaIcaDev,tmVtaTasa,tmVtaTasaDev,tmVtaGlobal,tmVtaGlobalDev,tmVtaSol,tmVtaSolDev) 
 SELECT @pmtmEst,K.IdProducto,IdCia,CASE CdTanque WHEN Null THEN '0' WHEN '' THEN '0' ELSE CdTanque  END AS CdBodga
,'0',CASE CdTanque WHEN Null THEN '0' WHEN '' THEN '0' ELSE CdTanque  END AS NumTanque
,SUM(Entradas),SUM(Salidas),SUM(VrCostoEnt),SUM(VrCostoSal)
--variables de ventas
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN Salidas ELSE 0 END ) AS VtaCant
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN Entradas ELSE 0 END ) AS VtaCantDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrPrecio*(Salidas-ISNULL(CantObseq,0)) ELSE 0 END ) AS VtaTotal
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrPrecio*(Entradas-ISNULL(CantObseq,0)) ELSE 0 END ) AS VtaTotalDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrCostoSal ELSE 0 END ) AS VtaCosto
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrCostoEnt ELSE 0 END ) AS VtaCostoDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrIvaSal ELSE 0 END ) AS VtaIva
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrIvaEnt ELSE 0 END ) AS VtaIvaDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrDctoSal ELSE 0 END ) AS VtaDcto
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrDctoEnt ELSE 0 END ) AS VtaDctoDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrReteSal ELSE 0 END ) AS VtaRet
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrReteEnt ELSE 0 END ) AS VtaRetDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrIcaSal ELSE 0 END ) AS VtaIca
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrIcaEnt ELSE 0 END ) AS VtaIcaDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN Sobretasa*Salidas ELSE 0 END ) AS VtaTasa
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN Sobretasa*Entradas ELSE 0 END ) AS VtaTasaDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN ImpGlobal*Salidas ELSE 0 END ) AS VtaGlobal
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN ImpGlobal*Entradas ELSE 0 END ) AS VtaGlobalDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN Soldicom*Salidas ELSE 0 END ) AS VtaSol
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN Soldicom*Entradas ELSE 0 END ) AS VtaSolDev
  FROM Trn_Kardex  AS K INNER JOIN ProdMcias  AS PM ON K.IdProducto=PM.IdProducto
  INNER JOIN SubGrupos AS S ON PM.IdSubgrupo=S.IdSubgrupo
  INNER JOIN Grupos AS G ON S.IdGrupo=G.IdGrupo  INNER JOIN Lineas AS LN ON G.IdLinea=LN.IdLinea
  WHERE TipDoc NOT IN ('ODC','PED','REM','COT','GUI','ODS','OCC','VCC')  AND  Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
  AND TipoRef<>'INSUMO'  AND Tanques<>0 AND  TipoRef LIKE ISNULL(@pmTipoRef,'%')
  AND  K.IdCia LIKE ISNULL(@pmIdCia,'%%') AND K.IdProducto LIKE ISNULL(@pmIdProducto,'%')  AND K.IdBodega LIKE ISNULL(@pmIdBodega,'%')
  AND G.IdLinea LIKE ISNULL(@pmIdLinea,'%')  AND S.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND PM.IdSubgrupo LIKE ISNULL(@pmIdSubgrupo ,'%')
  AND (PM.Inactivo=ISNULL(@pmInactivo,0) or PM.Inactivo=ISNULL(@pmInactivo,1))
 GROUP BY K.IdProducto,IdCia,CdTanque
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_AuxInv_SelVenBodCia] @pmtmEst CHAR(2),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME
,@pmIdCia CHAR(2)=Null,@pmIdProducto VARCHAR(16)=Null,@pmIdBodega VARCHAR(4)=Null,@pmTipoRef VARCHAR(10)=Null
,@pmIdLinea VARCHAR(8)=Null,@pmIdGrupo VARCHAR(8)=Null,@pmIdSubgrupo VARCHAR(8)=Null,@pmTanques BIT=Null
,@pmInactivo BIT=Null
 AS 
 INSERT INTO tm_AuxInv (tmEst,tmIdProducto,tmIdCia,tmIdBodega,tmNumLote,tmCdTanque,tmEntradas,tmSalidas,tmCostoEnt,tmCostoSal
 ,tmVtaCant,tmVtaCantDev,tmVtaTotal,tmVtaTotalDev,tmVtaCosto,tmVtaCostoDev,tmVtaIva,tmVtaIvaDev,tmVtaDcto,tmVtaDctoDev
 ,tmVtaRet,tmVtaRetDev,tmVtaIca,tmVtaIcaDev,tmVtaTasa,tmVtaTasaDev,tmVtaGlobal,tmVtaGlobalDev,tmVtaSol,tmVtaSolDev) 
 SELECT @pmtmEst,K.IdProducto,IdCia,K.IdBodega,'0','0',SUM(Entradas),SUM(Salidas),SUM(VrCostoEnt),SUM(VrCostoSal)
--variables de ventas
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN Salidas ELSE 0 END ) AS VtaCant
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN Entradas ELSE 0 END ) AS VtaCantDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrPrecio*(Salidas-ISNULL(CantObseq,0)) ELSE 0 END ) AS VtaTotal
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrPrecio*(Entradas-ISNULL(CantObseq,0)) ELSE 0 END ) AS VtaTotalDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrCostoSal ELSE 0 END ) AS VtaCosto
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrCostoEnt ELSE 0 END ) AS VtaCostoDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrIvaSal ELSE 0 END ) AS VtaIva
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrIvaEnt ELSE 0 END ) AS VtaIvaDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrDctoSal ELSE 0 END ) AS VtaDcto
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrDctoEnt ELSE 0 END ) AS VtaDctoDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrReteSal ELSE 0 END ) AS VtaRet
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrReteEnt ELSE 0 END ) AS VtaRetDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrIcaSal ELSE 0 END ) AS VtaIca
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrIcaEnt ELSE 0 END ) AS VtaIcaDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN Sobretasa*Salidas ELSE 0 END ) AS VtaTasa
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN Sobretasa*Entradas ELSE 0 END ) AS VtaTasaDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN ImpGlobal*Salidas ELSE 0 END ) AS VtaGlobal
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN ImpGlobal*Entradas ELSE 0 END ) AS VtaGlobalDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN Soldicom*Salidas ELSE 0 END ) AS VtaSol
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN Soldicom*Entradas ELSE 0 END ) AS VtaSolDev
  FROM Trn_Kardex  AS K INNER JOIN ProdMcias  AS PM ON K.IdProducto=PM.IdProducto
  INNER JOIN SubGrupos AS S ON PM.IdSubgrupo=S.IdSubgrupo
  INNER JOIN Grupos AS G ON S.IdGrupo=G.IdGrupo  INNER JOIN Lineas AS LN ON G.IdLinea=LN.IdLinea
  WHERE TipDoc NOT IN ('ODC','PED','REM','COT','GUI','ODS','OCC','VCC')  AND  Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
  AND TipoRef<>'INSUMO'  AND  TipoRef LIKE ISNULL(@pmTipoRef,'%')
  AND  K.IdCia LIKE ISNULL(@pmIdCia,'%%') AND K.IdProducto LIKE ISNULL(@pmIdProducto,'%')  AND K.IdBodega LIKE ISNULL(@pmIdBodega,'%')
  AND G.IdLinea LIKE ISNULL(@pmIdLinea,'%')  AND S.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND PM.IdSubgrupo LIKE ISNULL(@pmIdSubgrupo ,'%')
  AND (Tanques=ISNULL(@pmTanques,0) or Tanques=ISNULL(@pmTanques,1))
  AND (PM.Inactivo=ISNULL(@pmInactivo,0) or PM.Inactivo=ISNULL(@pmInactivo,1))
 GROUP BY K.IdProducto,IdCia,K.IdBodega
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_AuxInv_SelVenTanq] @pmtmEst CHAR(2),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME
,@pmIdCia CHAR(2)=Null,@pmIdProducto VARCHAR(16)=Null,@pmIdBodega VARCHAR(4)=Null,@pmTipoRef VARCHAR(10)=Null
,@pmIdLinea VARCHAR(8)=Null,@pmIdGrupo VARCHAR(8)=Null,@pmIdSubgrupo VARCHAR(8)=Null,@pmInactivo BIT=Null
 AS 
 INSERT INTO tm_AuxInv (tmEst,tmIdProducto,tmIdCia,tmIdBodega,tmNumLote,tmCdTanque,tmEntradas,tmSalidas,tmCostoEnt,tmCostoSal
 ,tmVtaCant,tmVtaCantDev,tmVtaTotal,tmVtaTotalDev,tmVtaCosto,tmVtaCostoDev,tmVtaIva,tmVtaIvaDev,tmVtaDcto,tmVtaDctoDev
 ,tmVtaRet,tmVtaRetDev,tmVtaIca,tmVtaIcaDev,tmVtaTasa,tmVtaTasaDev,tmVtaGlobal,tmVtaGlobalDev,tmVtaSol,tmVtaSolDev) 
 SELECT @pmtmEst,K.IdProducto,'00',CASE CdTanque WHEN Null THEN '0' WHEN '' THEN '0' ELSE CdTanque  END AS CdBodga,'0'
,CASE CdTanque WHEN Null THEN '0' WHEN '' THEN '0' ELSE CdTanque  END AS NumTanque
,SUM(Entradas),SUM(Salidas),SUM(VrCostoEnt),SUM(VrCostoSal)
--variables de ventas
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN Salidas ELSE 0 END ) AS VtaCant
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN Entradas ELSE 0 END ) AS VtaCantDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrPrecio*(Salidas-ISNULL(CantObseq,0)) ELSE 0 END ) AS VtaTotal
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrPrecio*(Entradas-ISNULL(CantObseq,0)) ELSE 0 END ) AS VtaTotalDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrCostoSal ELSE 0 END ) AS VtaCosto
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrCostoEnt ELSE 0 END ) AS VtaCostoDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrIvaSal ELSE 0 END ) AS VtaIva
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrIvaEnt ELSE 0 END ) AS VtaIvaDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrDctoSal ELSE 0 END ) AS VtaDcto
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrDctoEnt ELSE 0 END ) AS VtaDctoDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrReteSal ELSE 0 END ) AS VtaRet
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrReteEnt ELSE 0 END ) AS VtaRetDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrIcaSal ELSE 0 END ) AS VtaIca
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrIcaEnt ELSE 0 END ) AS VtaIcaDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN Sobretasa*Salidas ELSE 0 END ) AS VtaTasa
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN Sobretasa*Entradas ELSE 0 END ) AS VtaTasaDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN ImpGlobal*Salidas ELSE 0 END ) AS VtaGlobal
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN ImpGlobal*Entradas ELSE 0 END ) AS VtaGlobalDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN Soldicom*Salidas ELSE 0 END ) AS VtaSol
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN Soldicom*Entradas ELSE 0 END ) AS VtaSolDev
  FROM Trn_Kardex  AS K INNER JOIN ProdMcias  AS PM ON K.IdProducto=PM.IdProducto
  INNER JOIN SubGrupos AS S ON PM.IdSubgrupo=S.IdSubgrupo
  INNER JOIN Grupos AS G ON S.IdGrupo=G.IdGrupo  INNER JOIN Lineas AS LN ON G.IdLinea=LN.IdLinea
  WHERE TipDoc NOT IN ('ODC','PED','REM','COT','GUI','ODS','OCC','VCC')  AND  Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
  AND TipoRef<>'INSUMO'  AND Tanques<>0 AND  TipoRef LIKE ISNULL(@pmTipoRef,'%')
  AND  K.IdCia LIKE ISNULL(@pmIdCia,'%%') AND K.IdProducto LIKE ISNULL(@pmIdProducto,'%')  AND K.IdBodega LIKE ISNULL(@pmIdBodega,'%')
  AND G.IdLinea LIKE ISNULL(@pmIdLinea,'%')  AND S.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND PM.IdSubgrupo LIKE ISNULL(@pmIdSubgrupo ,'%')
  AND (PM.Inactivo=ISNULL(@pmInactivo,0) or PM.Inactivo=ISNULL(@pmInactivo,1))
 GROUP BY K.IdProducto,CdTanque
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNomNovCon] @pmIdPeriodo VARCHAR(8),@pmIdEmpleado VARCHAR(16) 
,@pmIdConcepto VARCHAR(4),@pmOrigCargue VARCHAR(10)=Null
 AS 
 SELECT Item,IdConcepto,Detalle,Cantidad,VrUnitario,VrTotal,Tarifa,VrOrigen,IdPeriodo,IdEmpleado
,TimeSys,FecUpdate,IdUsuario,OrigCargue,Num_Contrato,Nit_Tercero,HoraInicial,HoraFinal
 FROM Trn_NomNov
 WHERE IdPeriodo=@pmIdPeriodo AND IdEmpleado=@pmIdEmpleado
 AND IdConcepto=@pmIdConcepto
 AND OrigCargue LIKE ISNULL(@pmOrigCargue,'%')
 ORDER BY Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomNov] @pmIdPeriodo VARCHAR(8),@pmIdEmpleado VARCHAR(16),@pmItem INT
 AS 
 SELECT Item,IdConcepto,Detalle,Cantidad,VrUnitario,VrTotal,Tarifa,VrOrigen,IdPeriodo,IdEmpleado
,TimeSys,FecUpdate,IdUsuario,OrigCargue,NumDocRef,Num_Contrato,Nit_Tercero,HoraInicial,HoraFinal
 FROM Trn_NomNov
 WHERE IdPeriodo=@pmIdPeriodo AND IdEmpleado=@pmIdEmpleado
 AND (Item>=ISNULL(@pmItem,0)  AND Item<=ISNULL(@pmItem,2147483647))
 ORDER BY Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpNomNov] @pmIdPeriodo VARCHAR(8),@pmIdEmpleado VARCHAR(16),@pmItem INT,@pmIdConcepto VARCHAR(4),@pmDetalle VARCHAR(100),@pmCantidad DECIMAL(10,4),@pmVrUnitario MONEY
,@pmVrTotal MONEY,@pmVrOrigen MONEY,@pmTarifa DECIMAL(10,4),@pmOrigCargue VARCHAR(10),@pmNumDocRef VARCHAR(20),@pmNum_Contrato INT,@pmNit_Tercero VARCHAR(16),@pmHoraInicial DATETIME,@pmHoraFinal DATETIME,@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_NomNov SET IdConcepto=@pmIdConcepto,Detalle=@pmDetalle,Cantidad=@pmCantidad,VrUnitario=@pmVrUnitario,VrTotal=@pmVrTotal,VrOrigen=@pmVrOrigen
,Tarifa=@pmTarifa,OrigCargue=@pmOrigCargue,NumDocRef=@pmNumDocRef,Num_Contrato=@pmNum_Contrato,FecUpdate=@pmFecUpdate,Nit_Tercero=@pmNit_Tercero,HoraInicial=@pmHoraInicial,HoraFinal=@pmHoraFinal
 WHERE IdPeriodo=@pmIdPeriodo AND IdEmpleado=@pmIdEmpleado AND Item=@pmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsNomNov] @pmIdPeriodo VARCHAR(8),@pmIdEmpleado VARCHAR(16),@pmItem INT,@pmIdConcepto VARCHAR(4),@pmDetalle VARCHAR(100),@pmCantidad DECIMAL(10,4),@pmVrUnitario MONEY
,@pmVrTotal MONEY,@pmVrOrigen MONEY,@pmTarifa DECIMAL(10,4),@pmOrigCargue VARCHAR(10),@pmNumDocRef VARCHAR(20),@pmNum_Contrato INT,@pmNit_Tercero VARCHAR(16),@pmHoraInicial DATETIME,@pmHoraFinal DATETIME,@pmTimeSys SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_NomNov (IdPeriodo,IdEmpleado,Item,IdConcepto,Detalle,Cantidad,VrUnitario,VrTotal,VrOrigen,Tarifa,OrigCargue,TimeSys,IdUsuario,NumDocRef,Num_Contrato,Nit_Tercero,HoraInicial,HoraFinal)
 VALUES (@pmIdPeriodo,@pmIdEmpleado,@pmItem,@pmIdConcepto,@pmDetalle,@pmCantidad,@pmVrUnitario,@pmVrTotal,@pmVrOrigen,@pmTarifa,@pmOrigCargue, @pmTimeSys,@pmIdUsuario,@pmNumDocRef,@pmNum_Contrato,@pmNit_Tercero,@pmHoraInicial,@pmHoraFinal)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsTiposCon] @pmIdTipCon VARCHAR(4),@pmTipoContrato VARCHAR(100)
,@pmDuracion INT,@pmVincLabUgpp VARCHAR(250),@pmCodDIAN VARCHAR(3),@pmInactivo BIT
 AS 
 INSERT INTO TiposCon (IdTipCon,TipoContrato,Duracion,Inactivo,VincLabUgpp,CodDIAN)
 VALUES (@pmIdTipCon,@pmTipoContrato,@pmDuracion,@pmInactivo,@pmVincLabUgpp,@pmCodDIAN)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUpTiposCon] @pmIdTipCon VARCHAR(4),@pmTipoContrato VARCHAR(100)
,@pmDuracion INT,@pmVincLabUgpp VARCHAR(250),@pmCodDIAN VARCHAR(3),@pmInactivo BIT
 AS 
 UPDATE TiposCon SET TipoContrato=@pmTipoContrato,Duracion=@pmDuracion,Inactivo=@pmInactivo
,VincLabUgpp=@pmVincLabUgpp,CodDIAN=@pmCodDIAN
 WHERE IdTipCon=@pmIdTipCon
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTiposCon] @pmIdTipCon VARCHAR(4)
 AS 
 IF @pmIdTipCon IS NULL
  SELECT IdTipCon,TipoContrato,Duracion,VincLabUgpp,CodDIAN
  FROM TiposCon
  WHERE Inactivo=0
  ORDER BY IdTipCon
 ELSE
  SELECT IdTipCon,TipoContrato,Duracion,VincLabUgpp,CodDIAN,Inactivo
  FROM TiposCon
  WHERE IdTipCon=@pmIdTipCon
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryEmp_Contrato] @pmNContrato INT
 AS 
 SELECT NContrato,IdEmpleado,IdTipCon,FecIngreso,FecVigencia,FecRetiro,Indefinido,IdCargo,IdJornada,IdDep,IdArea,IdInstala,IdCCosto,IdSubCos,IdCia,IdNom
,IdClase,TipoTrabj,IdCot,IdGrupo,VrUpc,TipoLiquida,ModPromdio,VrSalario,VrAuxTrans,SalMinimo,SalIntegral,FPnomina,ModoRet,FecAfiSeg,ATFinMes,SegFinMes,DenyPEN,DenyEPS,DenyARP
,DenyCaj,DenyCes,DenyPri,DenyVac,DenyHex,DenyDcto,BasePrest,BaseSeg,DchDotacion,DchCafeteria,Reclmto,Notas,IdEstado,Inactivo,SubTipoCot,DsoSabado,FspFinmes,FecFinPrueba
,TipoPrendDot,TipoMedSent,BenefPacto,TipoTraspaso,FechaTraspaso,ContratoRef,IdMedPago,FecAdd,FecUpdate,IdUsuario
 FROM Emp_Contrato
 WHERE NContrato=@pmNContrato
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsEmp_Contrato] @pmNContrato INT,@pmIdEmpleado VARCHAR(16),@pmIdTipCon VARCHAR(4),@pmFecIngreso SMALLDATETIME,@pmFecVigencia SMALLDATETIME,@pmFecRetiro SMALLDATETIME,@pmIndefinido BIT,@pmIdCargo VARCHAR(4),@pmIdJornada VARCHAR(4)
,@pmIdDep VARCHAR(4),@pmIdArea VARCHAR(4),@pmIdInstala VARCHAR(4),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdCia CHAR(2),@pmIdNom VARCHAR(4),@pmTipoLiquida CHAR(1),@pmModPromdio CHAR(1),@pmVrSalario MONEY,@pmVrAuxTrans MONEY,@pmSalMinimo BIT,@pmSalIntegral BIT
,@pmFPnomina VARCHAR(10),@pmModoRet INT,@pmFecAfiSeg SMALLDATETIME,@pmATFinMes BIT,@pmSegFinMes BIT,@pmDenyPEN BIT,@pmDenyEPS BIT,@pmDenyARP BIT,@pmDenyCaj BIT,@pmDenyCes BIT,@pmDenyPri BIT,@pmDenyVac BIT,@pmDenyHex BIT,@pmBasePrest CHAR(2)
,@pmBaseSeg CHAR(2),@pmDchDotacion BIT,@pmDchCafeteria BIT,@pmReclmto VARCHAR(50),@pmNotas VARCHAR(250),@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmIdClase VARCHAR(4),@pmTipoTrabj INT,@pmIdCot VARCHAR(4),@pmVrUpc MONEY,@pmIdGrupo VARCHAR(4)
,@pmDenyDcto BIT,@pmSubTipoCot VARCHAR(3),@pmDsoSabado BIT,@pmFspFinmes BIT,@pmFecFinPrueba SMALLDATETIME,@pmTipoPrendDot VARCHAR(10),@pmTipoMedSent VARCHAR(4),@pmBenefPacto VARCHAR(100),@pmTipoTraspaso INT,@pmFechaTraspaso SMALLDATETIME,@pmContratoRef INT,@pmIdMedPago VARCHAR(4),@pmFecAdd SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Emp_Contrato (NContrato,IdEmpleado,IdTipCon,FecIngreso,FecVigencia,FecRetiro,Indefinido,IdCargo,IdJornada,IdDep,IdArea,IdInstala,IdCCosto,IdCia,IdNom,TipoLiquida,ModPromdio,VrSalario,VrAuxTrans,SalMinimo,SalIntegral,FPnomina,ModoRet,FecAfiSeg,ATFinMes,SegFinMes,DenyPEN,DenyEPS
,DenyARP,DenyCaj,DenyCes,DenyPri,DenyVac,DenyHex,BasePrest,BaseSeg,DchDotacion,DchCafeteria,Reclmto,Notas,IdEstado,Inactivo,IdSubCos,IdClase,TipoTrabj,FecAdd,IdUsuario,IdCot,VrUpc,IdGrupo,DenyDcto,SubTipoCot,DsoSabado,FspFinmes,FecFinPrueba,TipoPrendDot,TipoMedSent,BenefPacto,TipoTraspaso,FechaTraspaso,ContratoRef,IdMedPago)
 VALUES (@pmNContrato,@pmIdEmpleado,@pmIdTipCon,@pmFecIngreso,@pmFecVigencia,@pmFecRetiro,@pmIndefinido,@pmIdCargo,@pmIdJornada,@pmIdDep,@pmIdArea,@pmIdInstala,@pmIdCCosto,@pmIdCia,@pmIdNom,@pmTipoLiquida,@pmModPromdio,@pmVrSalario,@pmVrAuxTrans,@pmSalMinimo
,@pmSalIntegral,@pmFPnomina,@pmModoRet,@pmFecAfiSeg,@pmATFinMes,@pmSegFinMes,@pmDenyPEN,@pmDenyEPS,@pmDenyARP,@pmDenyCaj,@pmDenyCes,@pmDenyPri,@pmDenyVac,@pmDenyHex,@pmBasePrest,@pmBaseSeg,@pmDchDotacion,@pmDchCafeteria,@pmReclmto,@pmNotas
,@pmIdEstado,@pmInactivo,@pmIdSubCos,@pmIdClase,@pmTipoTrabj,@pmFecAdd,@pmIdUsuario,@pmIdCot,@pmVrUpc,@pmIdGrupo,@pmDenyDcto,@pmSubTipoCot,@pmDsoSabado,@pmFspFinmes,@pmFecFinPrueba,@pmTipoPrendDot,@pmTipoMedSent,@pmBenefPacto,@pmTipoTraspaso,@pmFechaTraspaso,@pmContratoRef,@pmIdMedPago)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpEmp_Contrato] @pmNContrato INT,@pmIdEmpleado VARCHAR(16),@pmIdTipCon VARCHAR(4),@pmFecIngreso SMALLDATETIME,@pmFecVigencia SMALLDATETIME,@pmFecRetiro SMALLDATETIME,@pmIndefinido BIT,@pmIdCargo VARCHAR(4),@pmIdJornada VARCHAR(4),@pmIdDep VARCHAR(4),@pmIdArea VARCHAR(4),@pmIdInstala VARCHAR(4),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16)
,@pmIdCia CHAR(2),@pmIdNom VARCHAR(4),@pmTipoLiquida CHAR(1),@pmModPromdio CHAR(1),@pmVrSalario MONEY,@pmVrAuxTrans MONEY,@pmSalMinimo BIT,@pmSalIntegral BIT,@pmFPnomina VARCHAR(10),@pmModoRet INT,@pmFecAfiSeg SMALLDATETIME,@pmATFinMes BIT,@pmSegFinMes BIT,@pmDenyPEN BIT,@pmDenyEPS BIT,@pmDenyARP BIT,@pmDenyCaj BIT,@pmDenyCes BIT,@pmDenyPri BIT
,@pmDenyVac BIT,@pmDenyHex BIT,@pmBasePrest CHAR(2),@pmBaseSeg CHAR(2),@pmDchDotacion BIT,@pmDchCafeteria BIT,@pmReclmto VARCHAR(50),@pmNotas VARCHAR(250),@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmIdClase VARCHAR(4),@pmTipoTrabj INT,@pmIdCot VARCHAR(4),@pmVrUpc MONEY,@pmIdGrupo VARCHAR(4),@pmDenyDcto BIT,@pmSubTipoCot VARCHAR(3),@pmDsoSabado BIT
,@pmFspFinmes BIT,@pmFecFinPrueba SMALLDATETIME,@pmTipoPrendDot VARCHAR(10),@pmTipoMedSent VARCHAR(4),@pmBenefPacto VARCHAR(100),@pmTipoTraspaso INT,@pmFechaTraspaso SMALLDATETIME,@pmContratoRef INT,@pmIdMedPago VARCHAR(4),@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Emp_Contrato SET IdEmpleado=@pmIdEmpleado,IdTipCon=@pmIdTipCon,FecIngreso=@pmFecIngreso,FecVigencia=@pmFecVigencia,FecRetiro=@pmFecRetiro,Indefinido=@pmIndefinido,IdCargo=@pmIdCargo,IdJornada=@pmIdJornada,IdDep=@pmIdDep,IdArea=@pmIdArea,IdInstala=@pmIdInstala,IdCCosto=@pmIdCCosto,IdCia=@pmIdCia,IdNom=@pmIdNom,TipoLiquida=@pmTipoLiquida,ModPromdio=@pmModPromdio
,VrSalario=@pmVrSalario,VrAuxTrans=@pmVrAuxTrans,SalMinimo=@pmSalMinimo,SalIntegral=@pmSalIntegral,FPnomina=@pmFPnomina,ModoRet=@pmModoRet,FecAfiSeg=@pmFecAfiSeg,ATFinMes=@pmATFinMes,SegFinMes=@pmSegFinMes,DenyPEN=@pmDenyPEN,DenyEPS=@pmDenyEPS,DenyARP=@pmDenyARP,DenyCaj=@pmDenyCaj,DenyCes=@pmDenyCes,DenyPri=@pmDenyPri,DenyVac=@pmDenyVac,DenyHex=@pmDenyHex
,BasePrest=@pmBasePrest,BaseSeg=@pmBaseSeg,DchDotacion=@pmDchDotacion,DchCafeteria=@pmDchCafeteria,Reclmto=@pmReclmto,Notas=@pmNotas,IdEstado=@pmIdEstado,Inactivo=@pmInactivo,FecUpdate=@pmFecUpdate,IdSubCos=@pmIdSubCos,IdClase=@pmIdClase,TipoTrabj=@pmTipoTrabj ,IdCot=@pmIdCot,VrUpc=@pmVrUpc,IdGrupo=@pmIdGrupo,DenyDcto=@pmDenyDcto,SubTipoCot =@pmSubTipoCot
,DsoSabado=@pmDsoSabado,FspFinmes=@pmFspFinmes,FecFinPrueba=@pmFecFinPrueba,TipoPrendDot=@pmTipoPrendDot,TipoMedSent=@pmTipoMedSent,BenefPacto=@pmBenefPacto,TipoTraspaso=@pmTipoTraspaso,FechaTraspaso=@pmFechaTraspaso,ContratoRef=@pmContratoRef,IdMedPago=@pmIdMedPago
 WHERE NContrato=@pmNContrato
GO