if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsMayBonif]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsMayBonif]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsMayLiqDetalle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsMayLiqDetalle]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsMayLiqTotales]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsMayLiqTotales]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomAlqDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNomAlqDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Aportes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_Aportes]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Aportes_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_Aportes_Sel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryKardexSub]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryKardexSub]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryKardexSubOpe]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryKardexSubOpe]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMayBonif]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMayBonif]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMayBonifDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMayBonifDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMayBonifLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMayBonifLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMayBonifSub]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMayBonifSub]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMayLiqDetalleLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMayLiqDetalleLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMayLiqTotalesAbo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMayLiqTotalesAbo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMayLiqTotalesLog]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMayLiqTotalesLog]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMayLiqTotalesLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMayLiqTotalesLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMayLiqTotalesTot]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMayLiqTotalesTot]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomAlqDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomAlqDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomAutoliq_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomAutoliq_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_Aportes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_Aportes]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_AportesDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_AportesDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_AportesDso]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_AportesDso]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_KdexSub]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_KdexSub]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_KdexSubOpe]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_KdexSubOpe]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpMayBonif]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpMayBonif]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpNomAlqDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpNomAlqDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUptm_AportesDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUptm_AportesDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNominaApo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNominaApo]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMayLiqTotalesLta] @pmNLiquida INT,@pmIdCia CHAR(2)
 AS 
 SELECT T.NContrato AS NumContrato,IdCiaCon,T.IdCliente AS NitCliente,TC.RazonSocial AS NombreCliente,T.IdAgencia AS Id_Agencia,Agencia
,Unidades,VrBonif,BaseIvaBonif,VrIvaBonif,VrAbono,T.DiasGracia AS Dias_Gracia,L.Fecha AS FechaLiq,Item,LogLiquida
--datos del contrato
,CN.Fecha AS FechaContrato,FechaInicio,FechaVence,CN.IdTipo AS CdTipo,TipContrato,CN.IdBonif AS CdBonif,TipoBonif
,CN.IdVend AS CdVend,V.RazonSocial AS Vendedor,CN.IdBandera AS CdBandera,TipoBandera
,TipCom,Comprobante,IdCiaCom,L.Observacion AS Observ,TipoNcr,NotaCred,IdCiaNot,VrCuotaFija
--datos del cliente
,TC.TipoId AS TipIdClie,TC.Dv AS DvCliente,TC.NomCial AS NomCialClie,TC.SiglaRaz AS SiglaClie,TC.IdLocal AS CdCiuClie,LT.Localidad AS CiudadCliente
,DP.Departamento AS DptoClie,A.IdLocal AS CdCiuAge,LA.Localidad AS CiudadAgencia,DA.Departamento AS DptoAgencia
 FROM Trn_MayLiquida AS L INNER JOIN Trn_MayLiqTotales  AS T ON L.NLiquida=T.NLiquida AND L.IdCia=T.IdCia
 INNER JOIN Terceros AS TC ON T.IdCliente=TC.IdTercero
 INNER JOIN Localidades AS LT ON TC.IdLocal=LT.IdLocal  INNER JOIN Departamentos AS DP ON LT.IdDep=DP.IdDep 
 LEFT JOIN Trn_MayContratos AS CN ON T.NContrato=CN.NContrato AND T.IdCiaCon=CN.IdCia
 LEFT JOIN Agencias AS A ON T.IdAgencia=A.IdAgencia
 LEFT JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal  INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 LEFT JOIN Terceros AS V ON CN.IdVend=V.IdTercero
 LEFT JOIN  MayTipos AS TCN ON CN.IdTipo=TCN.IdTipo
 LEFT JOIN  MayTiposBonif AS TB ON CN.IdBonif=TB.IdBonif
 LEFT JOIN  TiposBan AS B ON CN.IdBandera=B.IdBandera
 WHERE L.NLiquida=@pmNLiquida AND L.IdCia=@pmIdCia 
 ORDER BY Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQrytm_Aportes] @pmtmEst VARCHAR(5),@pmtmIdInstala VARCHAR(4)=Null,@pmtmIdDep VARCHAR(4)=Null,@pmtmIdNom VARCHAR(4)=Null
,@pmIdArea VARCHAR(4)=Null
 AS 
 SELECT tmIdEmpleado,Apellidos,Nombres,tmNContto,tmDiasPen,tmBasePen,tmTarfaPen,tmValorPen,tmTarfaFsp,tmValorFsp,tmTarfaFsu,tmValorFsu,tmDiasSal
,tmBaseSal,tmTarfaSal,tmValorSal,tmDiasRie,tmBaseRie,tmTarfaRie,tmValorRie,tmDiasCom,tmBaseCom,tmTarfaCom,tmValorCom,tmTarfaIcb,tmValorIcbf,tmTarfaSen,tmValorSena
,tmTarfaMin,tmValorMin,tmTarfaEsa,tmValorESAP,tmDiasNov,tmNovIng,tmNovRet,tmNovTda,tmNovTaa,tmNovTdp,tmNovTap,tmNovVsp,tmNovVte,tmNovVst,tmNovSln,tmNovIge,tmNovLma,tmNovVac,tmNovAvp,tmNovIrp,tmNovVct,tmNAutInc,tmVrIncap,tmNAutLic,tmVrLicencia,tmNAutIrp,tmVrVolAfi,tmVrVolApo,tmVrNoReten,tmVrUpc,tmVrIncIrp
,tmIdFonPen,FP.Fondo AS FondoPen,tmIdFonSal,FS.Fondo AS FondoEps,tmIdFonRie,FR.Fondo AS FondoArp,tmIdFonCaj,CC.Fondo AS CajaComp,tmIdInstala,Instlacion,tmIdDep,Dependencia,tmIdCCosto,CCosto,tmIdSubCos,SubCosto,tmIdNom,TipoNomina,tmVehiculo,tmVrBasico
,tmCodNewEps,tmCodNewPen,tmCodNewRie,tmCodNewIns,tmCodNewTar,CRN.ClaseRiesgo AS NewClaseRie,CRN.Tarifa AS NewTarifa,tmCodNewGru,TarfaEmp,TarfaTrab,tmEst,tmItem,tmApellido1,tmApellido2,tmNombre1,tmNombre2,tmBaseRef,tmDiasSan,tmDiasDnrPen,tmDiasDnrSal
,tmNovDsr,tmFecNovIng,tmFecNovRet,tmFecNovVsp,tmFecIniSln,tmFecFinSln,tmFecIniIge,tmFecFinIge,tmFecIniLma,tmFecFinLma,tmFecIniVac,tmFecFinVac,tmFecIniVct,tmFecFinVct,tmFecIniIrl,tmFecFinIrl,tmVrIBCparaf,tmCantHoras
--Datos empleado
,Codigo,TipoId,Dv,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,EsCondtor,FecNacmto,LugarNacmto,TipoSangre,FactorRh,Sexo,Licencia,CatLicencia,VigLicencia
,E.IdLugar AS CodLugar,LugarLic,TallaCam,TallaPan,TallaZap,IdEstCivil,EstCivil,NHijos,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,CR.ClaseRiesgo AS ClasRiesgo,CR.Tarifa AS TarifaRie,VrSalario,VrAuxTrans,SalMinimo,SalIntegral,FPnomina,C.TipoLiquida AS TipLiq,FecAfiSeg
,ATFinMes,SegFinMes,DchDotacion,DchCafeteria,C.IdEstado AS Con_IdEstdo,Estado,C.Inactivo AS Con_inactivo,C.IdJornada AS CodJnada,Jornada,TipoTrabj,tmCdFonAvp,CdFonAvp
 FROM tm_Aportes AS A INNER JOIN Empleados AS E ON A.tmIdEmpleado=E.IdEmpleado INNER JOIN  Emp_Contrato AS C ON A.tmNContto=C.NContrato
 INNER JOIN Dependencias AS DP ON A.tmIdDep=DP.IdDep  INNER JOIN Instalaciones AS I ON A.tmIdInstala=I.IdInstala
 INNER JOIN CentroCosto AS CO ON A.tmIdCCosto=CO.IdCCosto  INNER JOIN SubCentros AS SC ON A.tmIdSubCos=SC.IdSubCos
 INNER JOIN TiposNom AS TN ON A.tmIdNom=TN.IdNom INNER JOIN Fondos AS FP ON A.tmIdFonPen=FP.IdFondo
 INNER JOIN Fondos AS FS ON A.tmIdFonSal=FS.IdFondo  INNER JOIN Fondos AS FR ON A.tmIdFonRie=FR.IdFondo
 INNER JOIN Fondos AS CC ON A.tmIdFonCaj=CC.IdFondo 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 EstadoCiv AS EC ON E.IdEstCivil=EC.IdEstado
 INNER JOIN ExpLicencias AS EL ON E.IdLugar=EL.IdLugar
 INNER JOIN TiposCon AS TC ON C.IdTipCon=TC.IdTipCon
 INNER JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo  INNER JOIN Areas AS AR ON C.IdArea=AR.IdArea
 INNER JOIN ClaseRie AS CR ON C.IdClase=CR.IdClase INNER JOIN Jornadas AS J ON C.IdJornada=J.IdJornada
 INNER JOIN EstadoDoc AS ECO ON C.IdEstado=ECO.IdEstado  LEFT JOIN ClaseRie AS CRN ON A.tmCodNewTar=CRN.IdClase
 LEFT JOIN GruposAR AS GAR ON A.tmCodNewGru=GAR.IdGrupo
 WHERE tmEst=@pmtmEst AND tmIdInstala LIKE ISNULL(@pmtmIdInstala,'%') AND tmIdDep LIKE ISNULL(@pmtmIdDep,'%')
 AND tmIdNom LIKE ISNULL(@pmtmIdNom,'%') AND C.IdArea LIKE ISNULL(@pmIdArea,'%')
 ORDER BY Apellidos,Nombres,tmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQrytm_AportesDso] @pmtmEst VARCHAR(5)
 AS 
 SELECT tmItem,tmIdEmpleado,tmApellido1,tmApellido2,tmNombre1,tmNombre2,tmVrBasico,tmDiasPen,tmBasePen,tmTarfaPen,tmValorPen,tmVrVolAfi,tmVrVolApo,tmVrNoReten,tmValorFsp,tmValorFsu
,tmDiasSal,tmBaseSal,tmTarfaSal,tmValorSal,tmVrUpc,tmNAutInc,tmVrIncap,tmNAutLic,tmVrLicencia,tmDiasRie,tmBaseRie,tmTarfaRie,tmValorRie,tmVrIncIrp,tmNAutIrp
,tmDiasCom,tmBaseCom,tmTarfaCom,tmValorCom,tmTarfaIcb,tmValorIcbf,tmTarfaSen,tmValorSena,tmBaseRef,tmTarfaEsa,tmValorESAP,tmTarfaMin,tmValorMin
,tmTarfaFsp,tmTarfaFsu,tmIdFonPen,FP.Fondo AS FondoPen,tmIdFonSal,FS.Fondo AS FondoEps,tmIdFonRie,FR.Fondo AS FondoArp,tmIdFonCaj,CC.Fondo AS CajaComp
,tmIdInstala,Instlacion,tmNContto,tmIdNom,tmCdFonAvp,tmDiasSan,tmDiasDnrPen,tmDiasDnrSal,tmVrIBCparaf,tmCantHoras
 FROM tm_Aportes AS A INNER JOIN Instalaciones AS I ON A.tmIdInstala=I.IdInstala
 INNER JOIN Fondos AS FP ON A.tmIdFonPen=FP.IdFondo INNER JOIN Fondos AS FS ON A.tmIdFonSal=FS.IdFondo
 INNER JOIN Fondos AS FR ON A.tmIdFonRie=FR.IdFondo INNER JOIN Fondos AS CC ON A.tmIdFonCaj=CC.IdFondo
 WHERE tmEst=@pmtmEst
 ORDER BY tmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomAutoliq_Cr] @pmTipoLiq VARCHAR(3),@pmNumero VARCHAR(20),@pmPerAutoliq VARCHAR(6)=Null
AS
 SELECT  A.TipoLiq AS TipoPla,A.Numero AS NumPla,Fecha,PerAutoliq,PerAutoSal,FechaPago,A.IdAportnte AS NitAportnte,NA.RazonSocial AS NomAportante,Presentacion,A.CodCia AS CodSucur,Compania,CodSucsal,DireccSuc,TelefSuc,CI.IdLocal AS CodCiuSuc
,LC.Localidad AS CiudadSuc,LC.IdDep AS CodDepSuc,CodFondo,F.Fondo AS NomFondo,CodArp,FRP.Fondo AS FondoArp,CodOper,EsCorr,PerCorr,NumCorr,TotalEmp,VrNomina,A.VrPension AS Total_Pen,A.VrSalud AS Total_Sal,A.VrRiesgos AS Total_Rie,A.VrCajas AS Total_Caj
,A.VrSena AS Total_Sen,A.VrIcbf AS Total_ICB,A.VrEsap AS Total_ESA,VrMinEduc,TotalAfipen,TotalAfiSal,TotalAfiRie,TotalAfiCaj,TotalAfiSen,TotalAfiIcb,TotalAfiEsa,TotalAfiEduc,TotAdmPen,TotAdmSal,TotAdmRie,TotAdmCom,A.Observacion AS Observ,LogAdmon,RedoMiles
,Anulado,A.IdEstado AS IdEstdo,Estado,TimeSys,A.FecUpdate AS Fec_Update,A.IdUsuario AS IdUsuari,Usuario
--datos del aportante
,NA.TipoId AS Apo_TipID,DvNit,IdRepLegal,RLG.RazonSocial AS NomRepLegal,RLG.TipoId AS RLegTID,RLG.Dv AS RLegDV,NA.Direccion AS Apo_dirccion,NA.IdLocal AS Apo_CodCiu,LA.Localidad AS Apo_NomCiu,LA.IdDep AS Apo_CodDep,Departamento,NA.Telefono AS Apo_tel,NA.Fax AS Apo_Fax,NA.e_mail AS Apo_email
,ClaseApo,TipoEco,TipoPersna,CIIUDane,FecIniCndato,TipAccCndato,FecFinCndato,CodOperInfo,TipoApo,FecMatMerc,ExonParaf,ExonCajas
--datos del detalle
,D.Item AS DetItem,D.IdEmpleado AS IdEmpldo,D.NContrato AS NumContrato,Apellido1,Apellido2,Nombre1,Nombre2,D.IdFonPen AS IdFdoPen,FP.Fondo AS FondoPen,TFP.TipoId AS Pen_TipID,FP.IdTercero AS Pen_Nit,TFP.Dv AS Pen_DV,TFP.RazonSocial AS Pen_Nombre
,IdFonSal,FS.Fondo AS FondoSal,TFS.TipoId AS Sal_TipID,FS.IdTercero AS Sal_Nit,TFS.Dv AS Sal_Dv,TFS.RazonSocial AS Sal_Nombre,IdFonRie,FR.Fondo AS FondoRie,TFR.TipoId AS Rie_TipID,FR.IdTercero AS Rie_nit,TFR.Dv AS Rie_Dv,TFR.RazonSocial AS Rie_nombre
,IdCajCom,FC.Fondo AS FondoCc,TFC.TipoId AS CC_TipId,FC.IdTercero AS CC_Nit,TFC.Dv AS CC_DV,TFC.RazonSocial AS cc_Nombre,SalBasico,VrIBCPen,DiasPen,TarifPen,D.VrPension AS VlrPension,VrApoFsp,VrApoFsu,VrApoVolAfi,VrApoVolApt,VrNoReten,VrIBCSal,DiasSal,TarifSal,D.VrSalud AS VlrSalud,D.VrUpc AS VlrUpc
,VrIncap,VrLicencia,NumAutInc,NumAutLic,VrIBCRie,DiasRie,TarifRie,D.VrRiesgos AS VlrRiesgos,VrIncIrp,NumAutIrp,D.IdInstala AS CodInstla,I.Instlacion AS CentroTrabaj,I.CodCiu AS CiuInstala
,VrIBCRef,VrIBCCajas,DiasCom,TarifCom,D.VrCajas AS VlrCajas,TarifSen,D.VrSena AS VlrSena,TarifIcb,D.VrICBF AS Vlricbf,TarifEsap,D.VrESAP AS VlrESAP,TarifEdu,VrMinEdu,TarifFsp,TarifFsu,VrIBCparaf,CantHoras
--datos de novedades
,NovIng,NovRet,NovTde,NovTae,NovTdp,NovTap,NovVsp,NovVte,NovVst,NovSln,NovIge,NovLma,NovVac,NovAvp,NovVct,NovIrp,CodNewPen,FNP.Fondo AS NewFonPen,CodNewSal,FNS.Fondo AS NewFonEPS,CodNewIns,NI.Instlacion AS NewCentroTrabj,CodNewRie,CRN.Tarifa AS New_TarRie,CodNewGru
,NGR.TarfaTrab AS NewTarfTrab,NGR.TarfaEmp AS NewTarfEmp,DiasNov,DiasSan,DiasDnrPen,DiasDnrSal,DN.NovDsr,DN.FecNovIng,DN.FecNovRet,DN.FecNovVsp,DN.FecIniSln,DN.FecFinSln,DN.FecIniIge,DN.FecFinIge,DN.FecIniLma,DN.FecFinLma
,DN.FecIniVac,DN.FecFinVac,DN.FecIniVct,DN.FecFinVct,DN.FecIniIrl,DN.FecFinIrl
--datos del empleado
,Apellidos,Nombres,E.Codigo AS EmpCodigo,E.TipoId AS Emp_TipID,E.Dv AS Emp_Dv,E.IdLugarCed AS Emp_IdLugrCed,L.Localidad AS Lugar_Ced,L.IdDep AS Emp_CodDep,E.Direccion AS Dirccion,E.Telefono AS Emp_Telfono,E.TelMovil AS Emp_TelMovil,E.e_mail AS Emp_Email,E.IdProf AS CodProf,Profesion,E.IdClase AS CodClaCta,NumCuenta
,E.IdBanco AS CodBanco,EsCondtor,pVehiculo,FecNacmto,LugarNacmto,TipoSangre,FactorRh,Sexo,Licencia,CatLicencia,VigLicencia,TallaCam,TallaPan,TallaZap,IdEstCivil,EstCivil,NHijos,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 CodDepe,Dependencia,C.IdCCosto AS CodCcto,CCosto,C.IdSubCos AS CodScost,SubCosto,C.IdNom AS CodTipNom,TipoNomina,C.IdClase AS CodClaRie,CR.ClaseRiesgo AS ClasRiesgo
,CR.Tarifa AS TarifaRie,VrSalario,VrAuxTrans,C.VrUpc AS Con_VrUpc,SalMinimo,SalIntegral,FPnomina,C.TipoLiquida AS TipLiq,FecAfiSeg,C.IdCot AS CodCotz,Cotizante,C.IdGrupo AS CodGrup,GAR.TarfaTrab AS Con_TrfaTrab,GAR.TarfaEmp AS TrfaEmp,TipoTrabj
,ATFinMes,SegFinMes,C.IdEstado AS Con_IdEstdo,C.Inactivo AS Con_inactivo,IdJornada,DenyPEN,DenyEPS,DenyARP,DenyCaj,D.CdFonAvp AS CodFondoAvp,E.CdFonAvp AS Emp_FondoAVP,TipoPlanilla,SubTipoCot,CR.CodClase AS CodClaseRie,TN.NomDiario
 FROM Trn_NomAutoliq AS A INNER JOIN Trn_NomAlqDet AS D ON A.TipoLiq=D.TipoLiq AND A.Numero=D.Numero 
 INNER JOIN NomAportante AS NA ON A.IdAportnte=NA.IdAportnte  INNER JOIN Terceros AS RLG ON NA.IdRepLegal=RLG.IdTercero
 INNER JOIN EstadoDoc AS ED ON A.IdEstado=ED.IdEstado 
 INNER JOIN adm_Usuarios AS U ON A.IdUsuario=U.IdUsuario INNER JOIN Localidades AS LA ON NA.IdLocal=LA.IdLocal
 INNER JOIN Departamentos AS DE ON LA.IdDep=DE.IdDep
 INNER JOIN Empleados AS E ON D.IdEmpleado=E.IdEmpleado INNER JOIN Fondos AS FP ON D.IdFonPen=FP.IdFondo
 INNER JOIN Fondos AS FS ON D.IdFonSal=FS.IdFondo INNER JOIN Fondos AS FR ON D.IdFonRie=FR.IdFondo
 INNER JOIN Fondos AS FC ON D.IdCajCom=FC.IdFondo INNER JOIN Terceros AS TFP ON FP.IdTercero=TFP.IdTercero
 INNER JOIN Terceros AS TFS ON FS.IdTercero=TFS.IdTercero  INNER JOIN Terceros AS TFR ON FR.IdTercero=TFR.IdTercero
 INNER JOIN Terceros AS TFC ON FC.IdTercero=TFC.IdTercero  INNER JOIN Instalaciones AS I ON D.IdInstala=I.IdInstala
 INNER JOIN Emp_Contrato AS C ON D.NContrato=C.NContrato  INNER JOIN Dependencias AS DP ON C.IdDep=DP.IdDep 
 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 TiposCot AS TCZ ON C.IdCot=TCZ.IdCot
 INNER JOIN Localidades AS L ON E.IdLugarCed=L.IdLocal
 INNER JOIN Profesiones AS P ON E.IdProf=P.IdProf  INNER JOIN EstadoCiv AS EC ON E.IdEstCivil=EC.IdEstado
 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 
 LEFT JOIN GruposAR AS GAR ON C.IdGrupo=GAR.IdGrupo
 LEFT JOIN Trn_NomAlqNov AS DN ON D.TipoLiq=DN.TipoLiq AND D.Numero=DN.Numero AND D.Item=DN.Item
 LEFT JOIN Fondos AS F ON A.CodFondo=F.IdFondo LEFT JOIN Fondos AS FRP ON A.CodArp=FRP.IdFondo
 LEFT JOIN Companias AS CI ON A.CodCia=CI.IdCia LEFT JOIN Localidades AS LC ON CI.IdLocal=LC.IdLocal
 LEFT JOIN Fondos AS FNP ON DN.CodNewPen=FNP.IdFondo  LEFT JOIN Fondos AS FNS ON DN.CodNewSal=FNS.IdFondo
 LEFT JOIN Instalaciones AS NI ON DN.CodNewIns=NI.IdInstala LEFT JOIN ClaseRie AS CRN ON DN.CodNewRie=CRN.IdClase
 LEFT JOIN GruposAR AS NGR ON DN.CodNewGru=NGR.IdGrupo
 WHERE A.TipoLiq=@pmTipoLiq AND A.Numero LIKE ISNULL(@pmNumero,'%') AND PerAutoliq LIKE ISNULL(@pmPerAutoliq,'%')
 ORDER BY A.Numero,Apellidos,Nombres
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsMayLiqDetalle] @pmNLiquida INT,@pmIdCia CHAR(2),@pmNContrato INT,@pmIdCiaCon CHAR(2)
,@pmItem INT,@pmIdSubgrupo VARCHAR(8),@pmCombustible BIT,@pmUnidades DECIMAL(14,4),@pmUnidDev DECIMAL(14,4)
,@pmValorTotal MONEY,@pmValorDev MONEY,@pmIntervalo VARCHAR(50),@pmMargen DECIMAL(14,4)
,@pmTarifa DECIMAL(14,4),@pmTipoTarifa CHAR(1),@pmVrLiquida MONEY,@pmLogDetalle VARCHAR(250),@pmSubsidio  DECIMAL(14,4)
,@pmVenGalones DECIMAL(14,4),@pmVenTotal MONEY,@pmCantExcluido DECIMAL(14,4),@pmMgnNeto DECIMAL(14,4),@pmTipoMargen CHAR(1)
,@pmPorcIvaCom DECIMAL(14,4),@pmVrIvaCom DECIMAL(16,4),@pmIvaIncBonif BIT,@pmCdAgenc VARCHAR(16)
 AS 
 INSERT INTO Trn_MayLiqDetalle (NLiquida,IdCia,NContrato,IdCiaCon,Item,IdSubgrupo,Combustible,Unidades,UnidDev,ValorTotal
,ValorDev,Intervalo,Margen,Tarifa,TipoTarifa,VrLiquida,LogDetalle,Subsidio,VenGalones,VenTotal,CantExcluido,MgnNeto,TipoMargen,PorcIvaCom,VrIvaCom,IvaIncBonif,CdAgenc)
 VALUES (@pmNLiquida,@pmIdCia,@pmNContrato,@pmIdCiaCon,@pmItem,@pmIdSubgrupo,@pmCombustible,@pmUnidades
,@pmUnidDev,@pmValorTotal,@pmValorDev,@pmIntervalo,@pmMargen,@pmTarifa,@pmTipoTarifa,@pmVrLiquida
,@pmLogDetalle,@pmSubsidio,@pmVenGalones,@pmVenTotal,@pmCantExcluido,@pmMgnNeto,@pmTipoMargen,@pmPorcIvaCom,@pmVrIvaCom,@pmIvaIncBonif,@pmCdAgenc)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMayLiqDetalleLta] @pmNLiquida INT,@pmIdCia CHAR(2)
 AS 
 SELECT D.NContrato AS NumContrato,IdCiaCon,Item,D.IdSubgrupo AS CdSubgrupo,Subgrupo,Combustible,VenGalones,Unidades,UnidDev
,Unidades-UnidDev AS UnidNeto,VenTotal,ValorTotal,ValorDev,ValorTotal-ValorDev AS VrTotal,Intervalo,TipoTarifa,Tarifa,VrLiquida
,PorcIvaCom,VrIvaCom,IvaIncBonif,TipoMargen,Margen,Subsidio,MgnNeto,LogDetalle,CantExcluido,FechaInicio
,IdCliente,RazonSocial,D.CdAgenc AS Id_Agencia,Agencia
 FROM Trn_MayLiqDetalle AS D  INNER JOIN Trn_MayContratos AS CN ON D.NContrato=CN.NContrato AND D.IdCiaCon=CN.IdCia
 INNER JOIN  SubGrupos AS S ON D.IdSubgrupo=S.IdSubgrupo
 INNER JOIN Terceros AS T ON CN.IdCliente=T.IdTercero
 LEFT JOIN Agencias AS A ON D.CdAgenc=A.IdAgencia
 WHERE NLiquida=@pmNLiquida AND D.IdCia=@pmIdCia 
 ORDER BY D.NContrato,IdCiaCon,Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsMayLiqTotales] @pmNLiquida INT,@pmIdCia CHAR(2),@pmItem INT,@pmNContrato INT,@pmIdCiaCon CHAR(2)
,@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmUnidades DECIMAL(14,4),@pmVrBonif MONEY,@pmVrAbono MONEY
,@pmVrCuotaFija MONEY,@pmDiasGracia INT,@pmTipoNcr VARCHAR(3),@pmNotaCred INT,@pmIdCiaNot CHAR(2),@pmLogLiquida VARCHAR(250)
,@pmVrIvaBonif DECIMAL(16,4),@pmBaseIvaBonif DECIMAL(16,4)
 AS 
 INSERT INTO Trn_MayLiqTotales (NLiquida,IdCia,Item,NContrato,IdCiaCon,IdCliente,IdAgencia,Unidades,VrBonif,VrAbono
,VrCuotaFija,DiasGracia,TipoNcr,NotaCred,IdCiaNot,LogLiquida,VrIvaBonif,BaseIvaBonif)
 VALUES (@pmNLiquida,@pmIdCia,@pmItem,@pmNContrato,@pmIdCiaCon,@pmIdCliente,@pmIdAgencia,@pmUnidades
,@pmVrBonif,@pmVrAbono,@pmVrCuotaFija,@pmDiasGracia,@pmTipoNcr,@pmNotaCred,@pmIdCiaNot,@pmLogLiquida,@pmVrIvaBonif,@pmBaseIvaBonif)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMayLiqTotalesLog] @pmNLiquida INT,@pmIdCia CHAR(2)
 AS 
 SELECT Item,T.NContrato AS NumContrato,IdCiaCon,T.IdCliente AS NitCliente,RazonSocial,T.IdAgencia AS Id_Agencia,Agencia
,Unidades,VrBonif,VrAbono,VrCuotaFija,T.DiasGracia AS Dias_Gracia,NotaCred,IdCiaNot,LogLiquida,L.Fecha AS FechaLiq
,IdTipo,IdBonif,CN.IdVend AS CdVend,TC.IdLocal AS CdCiuClie,A.IdLocal AS CdCiuAge,CdCCBonif,CdSubCCBonif,BaseIvaBonif,VrIvaBonif,CN.ClaseContrato
 FROM Trn_MayLiquida AS L INNER JOIN Trn_MayLiqTotales  AS T ON L.NLiquida=T.NLiquida AND L.IdCia=T.IdCia
 INNER JOIN Terceros AS TC ON T.IdCliente=TC.IdTercero
 LEFT JOIN Trn_MayContratos AS CN ON T.NContrato=CN.NContrato AND T.IdCiaCon=CN.IdCia
 LEFT JOIN Agencias AS A ON T.IdAgencia=A.IdAgencia
 WHERE L.NLiquida=@pmNLiquida AND L.IdCia=@pmIdCia AND Estado=0
 AND (VrBonif<>0 OR VrCuotaFija>0) 
 ORDER BY Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryMayLiqTotalesTot] @pmNLiquida INT,@pmIdCia CHAR(2)
 AS 
 SELECT SUM(Unidades) AS STOTUNI,SUM(VrBonif) AS STOTBON,SUM(VrAbono) AS STOTABO
,SUM(VrCuotaFija) AS STOTFIJ,SUM(VrIvaBonif) AS STOTIVA,SUM(BaseIvaBonif) AS STOTBASE
 FROM  Trn_MayLiqTotales
 WHERE NLiquida=@pmNLiquida AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryMayLiqTotalesAbo] @pmNLiquida INT,@pmIdCia CHAR(2),@pmTipoNcr VARCHAR(3)
 AS 
 SELECT Item,T.NContrato AS NumContrato,IdCiaCon,T.IdCliente AS NitCliente,RazonSocial,T.IdAgencia AS Id_Agencia,Agencia
,Unidades,VrBonif,VrAbono,VrCuotaFija,T.DiasGracia AS Dias_Gracia,NotaCred,IdCiaNot
,L.Fecha AS FechaLiq,TipCom,Comprobante,IdCiaCom,L.Observacion AS Observ
,CN.IdVend AS CdVend,TC.IdLocal AS CdCiuClie,A.IdLocal AS CdCiuAge,BaseIvaBonif,VrIvaBonif
 FROM Trn_MayLiquida AS L INNER JOIN Trn_MayLiqTotales  AS T ON L.NLiquida=T.NLiquida AND L.IdCia=T.IdCia
 INNER JOIN Terceros AS TC ON T.IdCliente=TC.IdTercero
 LEFT JOIN Trn_MayContratos AS CN ON T.NContrato=CN.NContrato AND T.IdCiaCon=CN.IdCia
 LEFT JOIN Agencias AS A ON T.IdAgencia=A.IdAgencia
 WHERE L.NLiquida=@pmNLiquida AND L.IdCia=@pmIdCia AND Estado<>2 AND VrAbono>0
 AND TipoNcr=@pmTipoNcr AND NotaCred<=0
 ORDER BY Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_Aportes_Sel]  @pmTipoLiq VARCHAR(3),@pmNumero VARCHAR(20),@pmtmEst VARCHAR(5)
 AS 
 INSERT INTO tm_Aportes (tmEst,tmItem,tmIdEmpleado,tmApellido1,tmApellido2,tmNombre1,tmNombre2,tmNContto,tmDiasPen,tmDiasSal,tmDiasRie,tmDiasCom,tmBasePen,tmBaseSal,tmBaseRie,tmBaseCom,tmBaseRef,tmValorPen,tmValorSal,tmValorRie,tmValorCom,tmValorSena,tmValorIcbf,tmValorFsp,tmValorFsu
,tmTarfaPen,tmTarfaSal,tmTarfaRie,tmTarfaFsp,tmTarfaFsu,tmTarfaEsa,tmTarfaMin,tmTarfaCom,tmTarfaIcb,tmTarfaSen,tmVrIncap,tmVrLicencia,tmVrVolAfi,tmVrVolApo,tmVrNoReten,tmVrUpc,tmVrIncIrp,tmNAutLic,tmNAutInc,tmNAutIrp,tmIdFonPen,tmIdFonSal,tmIdFonRie,tmIdFonCaj,tmIdInstala,tmIdDep,tmIdCCosto
,tmIdSubCos,tmIdNom,tmVehiculo,tmVrBasico,tmValorESAP,tmValorMin,tmCdFonAvp,tmDiasSan,tmDiasDnrPen,tmDiasDnrSal,tmNovDsr,tmFecNovIng,tmFecNovRet,tmFecNovVsp,tmFecIniSln,tmFecFinSln,tmFecIniIge,tmFecFinIge,tmFecIniLma,tmFecFinLma,tmFecIniVac,tmFecFinVac,tmFecIniVct,tmFecFinVct,tmFecIniIrl,tmFecFinIrl,tmVrIBCparaf,tmCantHoras)
 SELECT @pmtmEst,Item,IdEmpleado,Apellido1,Apellido2,Nombre1,Nombre2,NContrato,DiasPen,DiasSal,DiasRie,DiasCom,VrIBCPen,VrIBCSal,VrIBCRie,VrIBCCajas,VrIBCRef,VrPension,VrSalud,VrRiesgos,VrCajas,VrSena,VrICBF,VrApoFsp,VrApoFsu,TarifPen,TarifSal,TarifRie,TarifFsp,TarifFsu,TarifEsap,TarifEdu
,TarifCom,TarifIcb,TarifSen,VrIncap,VrLicencia,VrApoVolAfi,VrApoVolApt,VrNoReten,VrUpc,VrIncIrp,NumAutLic,NumAutInc,NumAutIrp,IdFonPen,IdFonSal,IdFonRie,IdCajCom,IdInstala,'0','0','0','0','',SalBasico,VrESAP,VrMinEdu,CdFonAvp,0,0,0
,0,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,VrIBCparaf,CantHoras
 FROM Trn_NomAlqDet
 WHERE TipoLiq=@pmTipoLiq AND Numero=@pmNumero
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomAlqDet] @pmTipoLiq VARCHAR(3),@pmNumero VARCHAR(20),@pmItem INT
 AS 
 SELECT TipoLiq,Numero,Item,IdEmpleado,NContrato,Apellido1,Apellido2,Nombre1,Nombre2,IdFonPen,IdFonSal,IdFonRie,IdCajCom,SalBasico,VrIBCPen,DiasPen,TarifPen,VrPension,VrApoFsp,VrApoFsu,VrApoVolAfi,VrApoVolApt
,VrNoReten,VrIBCSal,DiasSal,TarifSal,VrSalud,VrUpc,VrIncap,VrLicencia,NumAutInc,NumAutLic,VrIBCRie,DiasRie,TarifRie,VrRiesgos,VrIncIrp,NumAutIrp,IdInstala,VrIBCRef,VrIBCCajas,DiasCom,TarifCom,VrCajas,TarifSen,VrSena,TarifIcb
,VrICBF,TarifEsap,VrESAP,TarifEdu,VrMinEdu,TarifFsp,TarifFsu,CdFonAvp,VrIBCparaf,CantHoras
 FROM Trn_NomAlqDet
 WHERE TipoLiq=@pmTipoLiq AND Numero=@pmNumero
  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].[paUpNomAlqDet] @pmTipoLiq VARCHAR(3),@pmNumero VARCHAR(20),@pmItem INT,@pmIdEmpleado VARCHAR(16),@pmNContrato INT,@pmApellido1 VARCHAR(50),@pmApellido2 VARCHAR(50),@pmNombre1 VARCHAR(50),@pmNombre2 VARCHAR(50),@pmIdFonPen VARCHAR(8),@pmIdFonSal VARCHAR(8),@pmIdFonRie VARCHAR(8),@pmIdCajCom VARCHAR(8),@pmSalBasico MONEY,@pmVrIBCPen MONEY
,@pmDiasPen DECIMAL(10,4),@pmTarifPen DECIMAL(10,4),@pmVrPension MONEY,@pmVrApoFsp MONEY,@pmVrApoFsu MONEY,@pmVrApoVolAfi MONEY,@pmVrApoVolApt MONEY,@pmVrNoReten MONEY,@pmVrIBCSal MONEY,@pmDiasSal DECIMAL(10,4),@pmTarifSal DECIMAL(10,4),@pmVrSalud MONEY,@pmVrUpc MONEY,@pmVrIncap MONEY,@pmVrLicencia MONEY,@pmNumAutInc VARCHAR(30),@pmNumAutLic VARCHAR(30),@pmVrIBCRie MONEY
,@pmDiasRie DECIMAL(10,4),@pmTarifRie DECIMAL(10,4),@pmVrRiesgos MONEY,@pmVrIncIrp MONEY,@pmNumAutIrp VARCHAR(30),@pmIdInstala VARCHAR(4),@pmVrIBCRef MONEY,@pmVrIBCCajas MONEY,@pmDiasCom DECIMAL(10,4),@pmTarifCom DECIMAL(10,4),@pmVrCajas MONEY,@pmTarifSen DECIMAL(10,4),@pmVrSena MONEY,@pmTarifIcb DECIMAL(10,4),@pmVrICBF MONEY,@pmTarifEsap DECIMAL(10,4),@pmVrESAP MONEY
,@pmTarifEdu DECIMAL(10,4),@pmVrMinEdu MONEY,@pmTarifFsp DECIMAL(10,4),@pmTarifFsu DECIMAL(10,4),@pmCdFonAvp VARCHAR(8),@pmVrIBCparaf MONEY,@pmCantHoras INT
 AS 
 UPDATE Trn_NomAlqDet SET IdEmpleado=@pmIdEmpleado,NContrato=@pmNContrato,Apellido1=@pmApellido1,Apellido2=@pmApellido2,Nombre1=@pmNombre1,Nombre2=@pmNombre2,IdFonPen=@pmIdFonPen,IdFonSal=@pmIdFonSal,IdFonRie=@pmIdFonRie,IdCajCom=@pmIdCajCom,SalBasico=@pmSalBasico,VrIBCPen=@pmVrIBCPen,DiasPen=@pmDiasPen,TarifPen=@pmTarifPen,VrPension=@pmVrPension,VrApoFsp=@pmVrApoFsp,VrApoFsu=@pmVrApoFsu
,VrApoVolAfi=@pmVrApoVolAfi,VrApoVolApt=@pmVrApoVolApt,VrNoReten=@pmVrNoReten,VrIBCSal=@pmVrIBCSal,DiasSal=@pmDiasSal,TarifSal=@pmTarifSal,VrSalud=@pmVrSalud,VrUpc=@pmVrUpc,VrIncap=@pmVrIncap,VrLicencia=@pmVrLicencia,NumAutInc=@pmNumAutInc,NumAutLic=@pmNumAutLic,VrIBCRie=@pmVrIBCRie,DiasRie=@pmDiasRie,TarifRie=@pmTarifRie,VrRiesgos=@pmVrRiesgos,VrIncIrp=@pmVrIncIrp,NumAutIrp=@pmNumAutIrp,IdInstala=@pmIdInstala
,VrIBCRef=@pmVrIBCRef,VrIBCCajas=@pmVrIBCCajas,DiasCom=@pmDiasCom,TarifCom=@pmTarifCom,VrCajas=@pmVrCajas,TarifSen=@pmTarifSen,VrSena=@pmVrSena,TarifIcb=@pmTarifIcb,VrICBF=@pmVrICBF,TarifEsap=@pmTarifEsap,VrESAP=@pmVrESAP,TarifEdu=@pmTarifEdu,VrMinEdu=@pmVrMinEdu,TarifFsp=@pmTarifFsp,TarifFsu=@pmTarifFsu,CdFonAvp=@pmCdFonAvp,VrIBCparaf=@pmVrIBCparaf,CantHoras=@pmCantHoras
 WHERE TipoLiq=@pmTipoLiq AND Numero=@pmNumero AND Item=@pmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsNomAlqDet] @pmTipoLiq VARCHAR(3),@pmNumero VARCHAR(20),@pmItem INT,@pmIdEmpleado VARCHAR(16),@pmNContrato INT,@pmApellido1 VARCHAR(50),@pmApellido2 VARCHAR(50),@pmNombre1 VARCHAR(50),@pmNombre2 VARCHAR(50),@pmIdFonPen VARCHAR(8),@pmIdFonSal VARCHAR(8),@pmIdFonRie VARCHAR(8),@pmIdCajCom VARCHAR(8),@pmSalBasico MONEY
,@pmVrIBCPen MONEY,@pmDiasPen DECIMAL(10,4),@pmTarifPen DECIMAL(10,4),@pmVrPension MONEY,@pmVrApoFsp MONEY,@pmVrApoFsu MONEY,@pmVrApoVolAfi MONEY,@pmVrApoVolApt MONEY,@pmVrNoReten MONEY,@pmVrIBCSal MONEY,@pmDiasSal DECIMAL(10,4),@pmTarifSal DECIMAL(10,4),@pmVrSalud MONEY,@pmVrUpc MONEY,@pmVrIncap MONEY,@pmVrLicencia MONEY,@pmNumAutInc VARCHAR(30)
,@pmNumAutLic VARCHAR(30),@pmVrIBCRie MONEY,@pmDiasRie DECIMAL(10,4),@pmTarifRie DECIMAL(10,4),@pmVrRiesgos MONEY,@pmVrIncIrp MONEY,@pmNumAutIrp VARCHAR(30),@pmIdInstala VARCHAR(4),@pmVrIBCRef MONEY,@pmVrIBCCajas MONEY,@pmDiasCom DECIMAL(10,4),@pmTarifCom DECIMAL(10,4),@pmVrCajas MONEY,@pmTarifSen DECIMAL(10,4),@pmVrSena MONEY,@pmTarifIcb DECIMAL(10,4),@pmVrICBF MONEY
,@pmTarifEsap DECIMAL(10,4),@pmVrESAP MONEY,@pmTarifEdu DECIMAL(10,4),@pmVrMinEdu MONEY,@pmTarifFsp DECIMAL(10,4),@pmTarifFsu DECIMAL(10,4),@pmCdFonAvp VARCHAR(8),@pmVrIBCparaf MONEY,@pmCantHoras INT
 AS 
 INSERT INTO Trn_NomAlqDet (TipoLiq,Numero,Item,IdEmpleado,NContrato,Apellido1,Apellido2,Nombre1,Nombre2,IdFonPen,IdFonSal,IdFonRie,IdCajCom,SalBasico,VrIBCPen,DiasPen,TarifPen,VrPension,VrApoFsp,VrApoFsu,VrApoVolAfi,VrApoVolApt,VrNoReten,VrIBCSal,DiasSal,TarifSal,VrSalud,VrUpc,VrIncap,VrLicencia,NumAutInc,NumAutLic,VrIBCRie,DiasRie,TarifRie,VrRiesgos,VrIncIrp,NumAutIrp,IdInstala,VrIBCRef,VrIBCCajas,DiasCom,TarifCom,VrCajas
,TarifSen,VrSena,TarifIcb,VrICBF,TarifEsap,VrESAP,TarifEdu,VrMinEdu,TarifFsp,TarifFsu,CdFonAvp,VrIBCparaf,CantHoras)
 VALUES (@pmTipoLiq,@pmNumero,@pmItem,@pmIdEmpleado,@pmNContrato,@pmApellido1,@pmApellido2,@pmNombre1,@pmNombre2,@pmIdFonPen,@pmIdFonSal,@pmIdFonRie,@pmIdCajCom,@pmSalBasico,@pmVrIBCPen,@pmDiasPen,@pmTarifPen,@pmVrPension,@pmVrApoFsp,@pmVrApoFsu,@pmVrApoVolAfi,@pmVrApoVolApt,@pmVrNoReten,@pmVrIBCSal,@pmDiasSal,@pmTarifSal,@pmVrSalud,@pmVrUpc,@pmVrIncap,@pmVrLicencia
,@pmNumAutInc,@pmNumAutLic,@pmVrIBCRie,@pmDiasRie,@pmTarifRie,@pmVrRiesgos,@pmVrIncIrp,@pmNumAutIrp,@pmIdInstala,@pmVrIBCRef,@pmVrIBCCajas,@pmDiasCom,@pmTarifCom,@pmVrCajas,@pmTarifSen,@pmVrSena,@pmTarifIcb,@pmVrICBF,@pmTarifEsap,@pmVrESAP,@pmTarifEdu,@pmVrMinEdu,@pmTarifFsp,@pmTarifFsu,@pmCdFonAvp,@pmVrIBCparaf,@pmCantHoras)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMayBonifLta] @pmNContratoIni INT,@pmNContratoFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT Item,TipoProd,VrInicial,VrFinal,Unidad,Periodo,IntvExcluido,TipoTarifa,Tarifa,TipoMargen
,CdSubgrupo,Subgrupo,IntvGlobal,IvaIncluido,NContrato,IdCia
 FROM Trn_MayBonif  AS  B  LEFT JOIN SubGrupos AS S ON B.CdSubgrupo=S.IdSubgrupo
 WHERE NContrato BETWEEN @pmNContratoIni AND @pmNContratoFin AND  IdCia=@pmIdCia  
 ORDER BY Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_KdexSub] @pmtmNumero VARCHAR(5)
 AS 
 SELECT IdSubgrupo,tmIdProducto,DescripProd,TipoRef,Combo,tmEsProdBase,IdProv,ExcluidoImp,Electrocomb
,COUNT(tmItem) AS SCANT,SUM(tmEntradas) AS SENT,SUM(tmSalidas) AS SSAL
,SUM(tmEntradas*tmVrUnitario) AS SCOSENT,SUM(tmSalidas*tmVrUnitario) AS SCOSSAL
,SUM(tmVrPrecio*tmEntradas) AS SVALENT,SUM(tmVrPrecio*tmSalidas) AS SVALSAL
,SUM(tmVrIva) AS SIVA,SUM(tmVrDcto) AS SDCT,SUM(tmVrRete) AS SRET,SUM(tmVrIca) AS SICA
,SUM(tmVrBruto*tmEntradas) AS SBRUENT,SUM(tmVrBruto*tmSalidas) AS SBRUSAL
,SUM(tmEntradas*tmImpGlobal) AS SGLOENT,SUM(tmSalidas*tmImpGlobal) AS SGLOSAL
,SUM(tmSobretasa*tmEntradas) AS SSOBENT,SUM(tmSobretasa*tmSalidas) AS SSOBSAL
,SUM(tmTasaNac*tmEntradas) AS SNACENT,SUM(tmTasaNac*tmSalidas) AS SNACSAL
,SUM(tmTasaDep*tmEntradas) AS SDEPENT,SUM(tmTasaDep*tmSalidas) AS SDEPSAL
,SUM(tmTasaMun*tmEntradas) AS SMUNENT,SUM(tmTasaMun*tmSalidas) AS SMUNSAL
,SUM(tmSoldicom*tmEntradas) AS SSOLENT,SUM(tmSoldicom*tmSalidas) AS SSOLSAL
,SUM(tmOtroImpto*tmEntradas) AS SOTRENT,SUM(tmOtroImpto*tmSalidas) AS SOTRSAL
--Para los ajustes con cantidad en cero (0)
,SUM(tmVrUnitario) AS SCOSAJU,SUM(tmOtroImpto) AS SDVEAJU
,SUM(tmRec_Costo*tmEntradas) AS SRCOSENT,SUM(tmRec_Costo*tmSalidas) AS SRCOSSAL
,SUM(tmMgenCont*tmEntradas) AS SMGENENT,SUM(tmMgenCont*tmSalidas) AS SMGENSAL
,SUM(tmVrImvCosto) AS SIMVCOS,SUM(tmVrImpCon) AS SVICO
,SUM(CASE WHEN tmCantObseq>0 THEN tmIvaObseq ELSE 0 END) AS SIVAOBSQ
,SUM(tmCantObseq*tmVrUnitario) AS SBASCOSOBSQ,SUM(tmCantObseq*tmVrPrecio) AS SBASEOBSQ
,SUM(tmIvaComb*tmEntradas) AS BASEIVAENT,SUM(tmIvaComb*tmSalidas) AS BASEIVASAL 
,SUM(tmImpCarb*tmEntradas) AS IMPCARBENT,SUM(tmImpCarb*tmSalidas) AS IMPCARBSAL 
,SUM(CASE WHEN (tmTarifaIva>0 AND P.TipoZonaFront='F') THEN ((tmEntradas*tmIvaComb)*tmTarifaIva)/100 ELSE 0 END) AS SIVAZFENT
,SUM(CASE WHEN (tmTarifaIva>0 AND P.TipoZonaFront='F') THEN ((tmSalidas*tmIvaComb)*tmTarifaIva)/100 ELSE 0 END) AS SIVAZFSAL
 FROM tm_Kdex AS K INNER JOIN ProdMcias AS P ON K.tmIdProducto=P.IdProducto
 WHERE tmNumero=@pmtmNumero
 GROUP BY IdSubgrupo,tmIdProducto,DescripProd,TipoRef,Combo,tmEsProdBase,IdProv,ExcluidoImp,Electrocomb
 ORDER BY IdSubgrupo,tmIdProducto
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_KdexSubOpe] @pmtmNumero VARCHAR(5)
 AS 
 SELECT IdSubgrupo,tmIdProducto,DescripProd,TipoRef,Combo,tmEsProdBase,IdProv,ExcluidoImp,Electrocomb,tmCdOperario
,COUNT(tmItem) AS SCANT,SUM(tmEntradas) AS SENT,SUM(tmSalidas) AS SSAL
,SUM(tmEntradas*tmVrUnitario) AS SCOSENT,SUM(tmSalidas*tmVrUnitario) AS SCOSSAL
,SUM(tmVrPrecio*tmEntradas) AS SVALENT,SUM(tmVrPrecio*tmSalidas) AS SVALSAL
,SUM(tmVrIva) AS SIVA,SUM(tmVrDcto) AS SDCT,SUM(tmVrRete) AS SRET,SUM(tmVrIca) AS SICA
,SUM(tmVrBruto*tmEntradas) AS SBRUENT,SUM(tmVrBruto*tmSalidas) AS SBRUSAL
,SUM(tmEntradas*tmImpGlobal) AS SGLOENT,SUM(tmSalidas*tmImpGlobal) AS SGLOSAL
,SUM(tmSobretasa*tmEntradas) AS SSOBENT,SUM(tmSobretasa*tmSalidas) AS SSOBSAL
,SUM(tmTasaNac*tmEntradas) AS SNACENT,SUM(tmTasaNac*tmSalidas) AS SNACSAL
,SUM(tmTasaDep*tmEntradas) AS SDEPENT,SUM(tmTasaDep*tmSalidas) AS SDEPSAL
,SUM(tmTasaMun*tmEntradas) AS SMUNENT,SUM(tmTasaMun*tmSalidas) AS SMUNSAL
,SUM(tmSoldicom*tmEntradas) AS SSOLENT,SUM(tmSoldicom*tmSalidas) AS SSOLSAL
,SUM(tmOtroImpto*tmEntradas) AS SOTRENT,SUM(tmOtroImpto*tmSalidas) AS SOTRSAL
--Para los ajustes con cantidad en cero (0)
,SUM(tmVrUnitario) AS SCOSAJU,SUM(tmOtroImpto) AS SDVEAJU
,SUM(tmRec_Costo*tmEntradas) AS SRCOSENT,SUM(tmRec_Costo*tmSalidas) AS SRCOSSAL
,SUM(tmMgenCont*tmEntradas) AS SMGENENT,SUM(tmMgenCont*tmSalidas) AS SMGENSAL
,SUM(tmVrImvCosto) AS SIMVCOS,SUM(tmVrImpCon) AS SVICO
,SUM(CASE WHEN tmCantObseq>0 THEN tmIvaObseq ELSE 0 END) AS SIVAOBSQ
,SUM(tmCantObseq*tmVrUnitario) AS SBASCOSOBSQ,SUM(tmCantObseq*tmVrPrecio) AS SBASEOBSQ
,SUM(tmIvaComb*tmEntradas) AS BASEIVAENT,SUM(tmIvaComb*tmSalidas) AS BASEIVASAL 
,SUM(tmImpCarb*tmEntradas) AS IMPCARBENT,SUM(tmImpCarb*tmSalidas) AS IMPCARBSAL 
,SUM(CASE WHEN (tmTarifaIva>0 AND P.TipoZonaFront='F') THEN ((tmEntradas*tmIvaComb)*tmTarifaIva)/100 ELSE 0 END) AS SIVAZFENT
,SUM(CASE WHEN (tmTarifaIva>0 AND P.TipoZonaFront='F') THEN ((tmSalidas*tmIvaComb)*tmTarifaIva)/100 ELSE 0 END) AS SIVAZFSAL
 FROM tm_Kdex AS K INNER JOIN ProdMcias AS P ON K.tmIdProducto=P.IdProducto
 WHERE tmNumero=@pmtmNumero
 GROUP BY IdSubgrupo,tmIdProducto,DescripProd,TipoRef,Combo,tmEsProdBase,IdProv,ExcluidoImp,Electrocomb,tmCdOperario
 ORDER BY IdSubgrupo,tmIdProducto
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUptm_AportesDet] @pmtmEst VARCHAR(5),@pmtmItem INT,@pmtmIdEmpleado VARCHAR(16),@pmtmApellido1 VARCHAR(50),@pmtmApellido2 VARCHAR(50),@pmtmNombre1 VARCHAR(50),@pmtmNombre2 VARCHAR(50),@pmtmNContto INT,@pmtmDiasPen DECIMAL(10,4),@pmtmDiasSal DECIMAL(10,4),@pmtmDiasRie DECIMAL(10,4),@pmtmDiasCom DECIMAL(10,4),@pmtmBasePen MONEY,@pmtmBaseSal MONEY,@pmtmBaseRie MONEY,@pmtmBaseCom MONEY,@pmtmBaseRef MONEY,@pmtmValorPen MONEY,@pmtmValorSal MONEY,@pmtmValorRie MONEY,@pmtmValorCom MONEY,@pmtmValorSena MONEY,@pmtmValorIcbf MONEY,@pmtmValorFsp MONEY,@pmtmValorFsu MONEY,@pmtmValorESAP MONEY,@pmtmValorMin MONEY,@pmtmTarfaPen DECIMAL(10,4),@pmtmTarfaSal DECIMAL(10,4),@pmtmTarfaRie DECIMAL(10,4)
,@pmtmTarfaFsp DECIMAL(10,4),@pmtmTarfaFsu DECIMAL(10,4),@pmtmTarfaEsa DECIMAL(10,4),@pmtmTarfaMin DECIMAL(10,4),@pmtmTarfaCom DECIMAL(10,4),@pmtmTarfaIcb DECIMAL(10,4),@pmtmTarfaSen DECIMAL(10,4),@pmtmVrIncap MONEY,@pmtmVrLicencia MONEY,@pmtmVrVolAfi MONEY,@pmtmVrVolApo MONEY,@pmtmVrNoReten MONEY
,@pmtmVrUpc MONEY,@pmtmVrIncIrp MONEY,@pmtmNAutLic VARCHAR(30),@pmtmNAutInc VARCHAR(30),@pmtmNAutIrp VARCHAR(30),@pmtmIdFonPen VARCHAR(8),@pmtmIdFonSal VARCHAR(8),@pmtmIdFonRie VARCHAR(8),@pmtmIdFonCaj VARCHAR(8),@pmtmIdInstala VARCHAR(4),@pmtmIdDep VARCHAR(4),@pmtmIdCCosto VARCHAR(16),@pmtmIdSubCos VARCHAR(16),@pmtmIdNom VARCHAR(4),@pmtmVehiculo VARCHAR(10),@pmtmVrBasico MONEY
,@pmtmCdFonAvp VARCHAR(8),@pmtmDiasSan DECIMAL(14,4),@pmtmDiasDnrPen INT,@pmtmDiasDnrSal INT,@pmtmNovDsr INT,@pmtmFecNovIng SMALLDATETIME,@pmtmFecNovRet SMALLDATETIME,@pmtmFecNovVsp SMALLDATETIME,@pmtmFecIniSln SMALLDATETIME,@pmtmFecFinSln SMALLDATETIME
,@pmtmFecIniIge SMALLDATETIME,@pmtmFecFinIge SMALLDATETIME,@pmtmFecIniLma SMALLDATETIME,@pmtmFecFinLma SMALLDATETIME,@pmtmFecIniVac SMALLDATETIME,@pmtmFecFinVac SMALLDATETIME,@pmtmFecIniVct SMALLDATETIME,@pmtmFecFinVct SMALLDATETIME,@pmtmFecIniIrl SMALLDATETIME,@pmtmFecFinIrl SMALLDATETIME,@pmtmVrIBCparaf MONEY,@pmtmCantHoras INT
 AS 
 UPDATE tm_Aportes SET tmIdEmpleado=@pmtmIdEmpleado,tmApellido1=@pmtmApellido1,tmApellido2=@pmtmApellido2,tmNombre1=@pmtmNombre1,tmNombre2=@pmtmNombre2,tmNContto=@pmtmNContto,tmDiasPen=@pmtmDiasPen,tmDiasSal=@pmtmDiasSal,tmDiasRie=@pmtmDiasRie,tmDiasCom=@pmtmDiasCom,tmBasePen=@pmtmBasePen,tmBaseSal=@pmtmBaseSal,tmBaseRie=@pmtmBaseRie,tmBaseCom=@pmtmBaseCom,tmBaseRef=@pmtmBaseRef,tmValorPen=@pmtmValorPen,tmValorSal=@pmtmValorSal,tmValorRie=@pmtmValorRie,tmValorCom=@pmtmValorCom,tmValorSena=@pmtmValorSena,tmValorIcbf=@pmtmValorIcbf,tmValorFsp=@pmtmValorFsp,tmValorFsu=@pmtmValorFsu,tmValorESAP=@pmtmValorESAP
,tmValorMin=@pmtmValorMin,tmTarfaPen=@pmtmTarfaPen,tmTarfaSal=@pmtmTarfaSal,tmTarfaRie=@pmtmTarfaRie,tmTarfaFsp=@pmtmTarfaFsp,tmTarfaFsu=@pmtmTarfaFsu,tmTarfaEsa=@pmtmTarfaEsa,tmTarfaMin=@pmtmTarfaMin,tmTarfaCom=@pmtmTarfaCom,tmTarfaIcb=@pmtmTarfaIcb,tmTarfaSen=@pmtmTarfaSen,tmVrIncap=@pmtmVrIncap,tmVrLicencia=@pmtmVrLicencia,tmVrVolAfi=@pmtmVrVolAfi,tmVrVolApo=@pmtmVrVolApo,tmVrNoReten=@pmtmVrNoReten,tmVrUpc=@pmtmVrUpc,tmVrIncIrp=@pmtmVrIncIrp,tmNAutLic=@pmtmNAutLic,tmNAutInc=@pmtmNAutInc,tmNAutIrp=@pmtmNAutIrp,tmIdFonPen=@pmtmIdFonPen,tmIdFonSal=@pmtmIdFonSal,tmIdFonRie=@pmtmIdFonRie,tmIdFonCaj=@pmtmIdFonCaj,tmIdInstala=@pmtmIdInstala
,tmIdDep=@pmtmIdDep,tmIdCCosto=@pmtmIdCCosto,tmIdSubCos=@pmtmIdSubCos,tmIdNom=@pmtmIdNom,tmVehiculo=@pmtmVehiculo,tmVrBasico=@pmtmVrBasico,tmCdFonAvp=@pmtmCdFonAvp,tmDiasSan=@pmtmDiasSan,tmDiasDnrPen=@pmtmDiasDnrPen,tmDiasDnrSal=@pmtmDiasDnrSal,tmNovDsr=@pmtmNovDsr,tmFecNovIng=@pmtmFecNovIng,tmFecNovRet=@pmtmFecNovRet,tmFecNovVsp=@pmtmFecNovVsp,tmFecIniSln=@pmtmFecIniSln,tmFecFinSln=@pmtmFecFinSln,tmFecIniIge=@pmtmFecIniIge,tmFecFinIge=@pmtmFecFinIge,tmFecIniLma=@pmtmFecIniLma,tmFecFinLma=@pmtmFecFinLma,tmFecIniVac=@pmtmFecIniVac,tmFecFinVac=@pmtmFecFinVac 
,tmFecIniVct=@pmtmFecIniVct,tmFecFinVct=@pmtmFecFinVct,tmFecIniIrl=@pmtmFecIniIrl,tmFecFinIrl=@pmtmFecFinIrl,tmVrIBCparaf=@pmtmVrIBCparaf,tmCantHoras=@pmtmCantHoras
 WHERE tmEst=@pmtmEst AND tmItem=@pmtmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQrytm_AportesDet] @pmtmEst VARCHAR(5),@pmtmItem INT=Null
 AS 
 SELECT tmEst,tmItem,tmIdEmpleado,tmApellido1,tmApellido2,tmNombre1,tmNombre2,tmNContto,tmDiasPen,tmDiasSal,tmDiasRie,tmDiasCom,tmBasePen,tmBaseSal,tmBaseRie,tmBaseCom,tmBaseRef,tmValorPen,tmValorSal,tmValorRie,tmValorCom,tmValorSena
,tmValorIcbf,tmValorFsp,tmValorFsu,tmValorESAP,tmValorMin,tmTarfaPen,tmTarfaSal,tmTarfaRie,tmTarfaFsp,tmTarfaFsu,tmTarfaEsa,tmTarfaMin,tmTarfaCom,tmTarfaIcb,tmTarfaSen,tmNovIng,tmNovRet,tmNovTda,tmNovTaa,tmNovTdp,tmNovTap,tmNovVsp,tmNovVte
,tmNovVst,tmNovSln,tmNovIge,tmNovLma,tmNovVac,tmNovAvp,tmNovIrp,tmNovVct,tmCodNewEps,tmCodNewPen,tmCodNewRie,tmCodNewIns,tmCodNewTar,tmCodNewGru,tmVrIncap,tmVrLicencia,tmVrVolAfi,tmVrVolApo,tmVrNoReten,tmVrUpc,tmVrIncIrp,tmNAutLic,tmNAutInc,tmNAutIrp
,tmIdFonPen,tmIdFonSal,tmIdFonRie,tmIdFonCaj,tmIdInstala,tmIdDep,tmIdCCosto,tmIdSubCos,tmIdNom,tmVehiculo,tmVrBasico,tmDiasNov,tmCdFonAvp,tmDiasSan,tmDiasDnrPen,tmDiasDnrSal
,tmNovDsr,tmFecNovIng,tmFecNovRet,tmFecNovVsp,tmFecIniSln,tmFecFinSln,tmFecIniIge,tmFecFinIge,tmFecIniLma,tmFecFinLma,tmFecIniVac,tmFecFinVac,tmFecIniVct,tmFecFinVct,tmFecIniIrl,tmFecFinIrl,tmVrIBCparaf,tmCantHoras
 FROM tm_Aportes
 WHERE tmEst=@pmtmEst
 AND (tmItem>=ISNULL(@pmtmItem,0)  AND tmItem<=ISNULL(@pmtmItem,2147483647))
 ORDER BY tmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_Aportes] @pmtmEst VARCHAR(5),@pmtmItem INT,@pmtmIdEmpleado VARCHAR(16),@pmtmApellido1 VARCHAR(50),@pmtmApellido2 VARCHAR(50),@pmtmNombre1 VARCHAR(50),@pmtmNombre2 VARCHAR(50),@pmtmNContto INT,@pmtmDiasPen DECIMAL(10,4),@pmtmDiasSal DECIMAL(10,4),@pmtmDiasRie DECIMAL(10,4),@pmtmDiasCom DECIMAL(10,4),@pmtmBasePen MONEY,@pmtmBaseSal MONEY,@pmtmBaseRie MONEY,@pmtmBaseCom MONEY,@pmtmBaseRef MONEY,@pmtmValorPen MONEY,@pmtmValorSal MONEY,@pmtmValorRie MONEY,@pmtmValorCom MONEY,@pmtmValorSena MONEY
,@pmtmValorIcbf MONEY,@pmtmValorFsp MONEY,@pmtmValorFsu MONEY,@pmtmValorESAP MONEY,@pmtmValorMin MONEY,@pmtmTarfaPen DECIMAL(10,4),@pmtmTarfaSal DECIMAL(10,4),@pmtmTarfaRie DECIMAL(10,4),@pmtmTarfaFsp DECIMAL(10,4),@pmtmTarfaFsu DECIMAL(10,4),@pmtmTarfaEsa DECIMAL(10,4),@pmtmTarfaMin DECIMAL(10,4),@pmtmTarfaCom DECIMAL(10,4),@pmtmTarfaIcb DECIMAL(10,4),@pmtmTarfaSen DECIMAL(10,4),@pmtmNovIng INT,@pmtmNovRet INT,@pmtmNovTda INT,@pmtmNovTaa INT,@pmtmNovTdp INT,@pmtmNovTap INT,@pmtmNovVsp INT,@pmtmNovVte INT,@pmtmNovVst INT,@pmtmNovSln INT,@pmtmNovIge INT,@pmtmNovLma INT,@pmtmNovVac INT
,@pmtmNovAvp INT,@pmtmNovIrp INT,@pmtmNovVct INT,@pmtmCodNewEps VARCHAR(8),@pmtmCodNewPen VARCHAR(8),@pmtmCodNewRie VARCHAR(8),@pmtmCodNewIns VARCHAR(4),@pmtmCodNewTar VARCHAR(4),@pmtmCodNewGru VARCHAR(4),@pmtmVrIncap MONEY,@pmtmVrLicencia MONEY,@pmtmVrVolAfi MONEY,@pmtmVrVolApo MONEY,@pmtmVrNoReten MONEY,@pmtmVrUpc MONEY,@pmtmVrIncIrp MONEY,@pmtmNAutLic VARCHAR(30),@pmtmNAutInc VARCHAR(30),@pmtmNAutIrp VARCHAR(30),@pmtmIdFonPen VARCHAR(8),@pmtmIdFonSal VARCHAR(8),@pmtmIdFonRie VARCHAR(8),@pmtmIdFonCaj VARCHAR(8),@pmtmIdInstala VARCHAR(4)
,@pmtmIdDep VARCHAR(4),@pmtmIdCCosto VARCHAR(16),@pmtmIdSubCos VARCHAR(16),@pmtmIdNom VARCHAR(4),@pmtmVehiculo VARCHAR(10),@pmtmVrBasico MONEY,@pmtmDiasNov DECIMAL(10,4),@pmtmCdFonAvp VARCHAR(8),@pmtmDiasSan DECIMAL(14,4),@pmtmDiasDnrPen INT,@pmtmDiasDnrSal INT,@pmtmNovDsr INT,@pmtmFecNovIng SMALLDATETIME,@pmtmFecNovRet SMALLDATETIME,@pmtmFecNovVsp SMALLDATETIME,@pmtmFecIniSln SMALLDATETIME,@pmtmFecFinSln SMALLDATETIME
,@pmtmFecIniIge SMALLDATETIME,@pmtmFecFinIge SMALLDATETIME,@pmtmFecIniLma SMALLDATETIME,@pmtmFecFinLma SMALLDATETIME,@pmtmFecIniVac SMALLDATETIME,@pmtmFecFinVac SMALLDATETIME,@pmtmFecIniVct SMALLDATETIME,@pmtmFecFinVct SMALLDATETIME,@pmtmFecIniIrl SMALLDATETIME,@pmtmFecFinIrl SMALLDATETIME,@pmtmVrIBCparaf MONEY,@pmtmCantHoras INT
 AS 
 INSERT INTO tm_Aportes (tmEst,tmItem,tmIdEmpleado,tmApellido1,tmApellido2,tmNombre1,tmNombre2,tmNContto,tmDiasPen,tmDiasSal,tmDiasRie,tmDiasCom,tmBasePen,tmBaseSal,tmBaseRie,tmBaseCom,tmBaseRef,tmValorPen,tmValorSal,tmValorRie,tmValorCom,tmValorSena,tmValorIcbf,tmValorFsp,tmValorFsu,tmTarfaPen,tmTarfaSal,tmTarfaRie,tmTarfaFsp,tmTarfaFsu,tmTarfaEsa,tmTarfaMin,tmTarfaCom,tmTarfaIcb,tmTarfaSen,tmNovIng,tmNovRet,tmNovTda,tmNovTaa,tmNovTdp,tmNovTap,tmNovVsp,tmNovVte,tmNovVst,tmNovSln,tmNovIge,tmNovLma,tmNovVac,tmNovAvp,tmNovIrp,tmNovVct,tmCodNewEps,tmCodNewPen,tmCodNewRie,tmCodNewIns,tmCodNewTar,tmCodNewGru
,tmVrIncap,tmVrLicencia,tmVrVolAfi,tmVrVolApo,tmVrNoReten,tmVrUpc,tmVrIncIrp,tmNAutLic,tmNAutInc,tmNAutIrp,tmIdFonPen,tmIdFonSal,tmIdFonRie,tmIdFonCaj,tmIdInstala,tmIdDep,tmIdCCosto,tmIdSubCos,tmIdNom,tmVehiculo,tmVrBasico,tmDiasNov,tmValorESAP,tmValorMin,tmCdFonAvp,tmDiasSan,tmDiasDnrPen,tmDiasDnrSal,tmNovDsr,tmFecNovIng,tmFecNovRet,tmFecNovVsp,tmFecIniSln,tmFecFinSln,tmFecIniIge,tmFecFinIge,tmFecIniLma,tmFecFinLma,tmFecIniVac,tmFecFinVac,tmFecIniVct,tmFecFinVct,tmFecIniIrl,tmFecFinIrl,tmVrIBCparaf,tmCantHoras)
 VALUES (@pmtmEst,@pmtmItem,@pmtmIdEmpleado,@pmtmApellido1,@pmtmApellido2,@pmtmNombre1,@pmtmNombre2,@pmtmNContto,@pmtmDiasPen,@pmtmDiasSal,@pmtmDiasRie,@pmtmDiasCom,@pmtmBasePen,@pmtmBaseSal,@pmtmBaseRie,@pmtmBaseCom,@pmtmBaseRef,@pmtmValorPen,@pmtmValorSal,@pmtmValorRie,@pmtmValorCom,@pmtmValorSena,@pmtmValorIcbf,@pmtmValorFsp,@pmtmValorFsu,@pmtmTarfaPen,@pmtmTarfaSal,@pmtmTarfaRie,@pmtmTarfaFsp,@pmtmTarfaFsu,@pmtmTarfaEsa,@pmtmTarfaMin,@pmtmTarfaCom,@pmtmTarfaIcb,@pmtmTarfaSen,@pmtmNovIng,@pmtmNovRet,@pmtmNovTda,@pmtmNovTaa,@pmtmNovTdp,@pmtmNovTap
,@pmtmNovVsp,@pmtmNovVte,@pmtmNovVst,@pmtmNovSln,@pmtmNovIge,@pmtmNovLma,@pmtmNovVac,@pmtmNovAvp,@pmtmNovIrp,@pmtmNovVct,@pmtmCodNewEps,@pmtmCodNewPen,@pmtmCodNewRie,@pmtmCodNewIns,@pmtmCodNewTar,@pmtmCodNewGru,@pmtmVrIncap,@pmtmVrLicencia,@pmtmVrVolAfi,@pmtmVrVolApo,@pmtmVrNoReten,@pmtmVrUpc,@pmtmVrIncIrp,@pmtmNAutLic,@pmtmNAutInc,@pmtmNAutIrp,@pmtmIdFonPen,@pmtmIdFonSal,@pmtmIdFonRie,@pmtmIdFonCaj,@pmtmIdInstala,@pmtmIdDep,@pmtmIdCCosto,@pmtmIdSubCos,@pmtmIdNom,@pmtmVehiculo,@pmtmVrBasico,@pmtmDiasNov,@pmtmValorESAP,@pmtmValorMin,@pmtmCdFonAvp,@pmtmDiasSan,@pmtmDiasDnrPen,@pmtmDiasDnrSal
,@pmtmNovDsr,@pmtmFecNovIng,@pmtmFecNovRet,@pmtmFecNovVsp,@pmtmFecIniSln,@pmtmFecFinSln,@pmtmFecIniIge,@pmtmFecFinIge,@pmtmFecIniLma,@pmtmFecFinLma,@pmtmFecIniVac,@pmtmFecFinVac,@pmtmFecIniVct,@pmtmFecFinVct,@pmtmFecIniIrl,@pmtmFecFinIrl,@pmtmVrIBCparaf,@pmtmCantHoras)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpMayBonif] @pmNContrato INT,@pmIdCia CHAR(2),@pmItem INT,@pmTipoProd VARCHAR(20),@pmVrInicial DECIMAL(14,4),@pmVrFinal DECIMAL(14,4),@pmUnidad VARCHAR(10)
,@pmTarifa DECIMAL(14,4),@pmTipoTarifa CHAR(1),@pmPeriodo VARCHAR(10),@pmCdProducto VARCHAR(16),@pmCdSubgrupo VARCHAR(8)
,@pmTipoMargen CHAR(1),@pmIntvExcluido BIT,@pmIntvGlobal BIT,@pmIvaIncluido BIT
 AS 
 UPDATE Trn_MayBonif SET TipoProd=@pmTipoProd,VrInicial=@pmVrInicial,VrFinal=@pmVrFinal,Unidad=@pmUnidad,Tarifa=@pmTarifa,TipoTarifa=@pmTipoTarifa
,Periodo=@pmPeriodo,CdProducto=@pmCdProducto,CdSubgrupo=@pmCdSubgrupo,TipoMargen=@pmTipoMargen,IntvExcluido=@pmIntvExcluido,IntvGlobal=@pmIntvGlobal,IvaIncluido=@pmIvaIncluido
 WHERE NContrato=@pmNContrato AND IdCia=@pmIdCia AND Item=@pmItem
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMayBonif] @pmNContrato INT,@pmIdCia CHAR(2),@pmItem INT
 AS 
 SELECT NContrato,IdCia,Item,TipoProd,VrInicial,VrFinal,Unidad,Tarifa,TipoTarifa,Periodo
,CdProducto,CdSubgrupo,TipoMargen,IntvExcluido,IntvGlobal,IvaIncluido
 FROM Trn_MayBonif
 WHERE NContrato=@pmNContrato AND IdCia=@pmIdCia  
 AND (Item>=ISNULL(@pmItem,-1)  AND Item<=ISNULL(@pmItem,2147483647))
 ORDER BY Item
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsMayBonif] @pmNContrato INT,@pmIdCia CHAR(2),@pmItem INT,@pmTipoProd VARCHAR(20),@pmVrInicial DECIMAL(14,4),@pmVrFinal DECIMAL(14,4)
,@pmUnidad VARCHAR(10),@pmTarifa DECIMAL(14,4),@pmTipoTarifa CHAR(1),@pmPeriodo VARCHAR(10),@pmCdProducto VARCHAR(16),@pmCdSubgrupo VARCHAR(8)
,@pmTipoMargen CHAR(1),@pmIntvExcluido BIT,@pmIntvGlobal BIT,@pmIvaIncluido BIT
 AS 
 INSERT INTO Trn_MayBonif (NContrato,IdCia,Item,TipoProd,VrInicial,VrFinal,Unidad,Tarifa,TipoTarifa,Periodo,CdProducto,CdSubgrupo,TipoMargen,IntvExcluido,IntvGlobal,IvaIncluido)
 VALUES (@pmNContrato,@pmIdCia,@pmItem,@pmTipoProd,@pmVrInicial,@pmVrFinal,@pmUnidad,@pmTarifa,@pmTipoTarifa,@pmPeriodo,@pmCdProducto,@pmCdSubgrupo,@pmTipoMargen,@pmIntvExcluido,@pmIntvGlobal,@pmIvaIncluido)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMayBonifDet] @pmNContrato INT,@pmIdCia CHAR(2),@pmTipoProd VARCHAR(20)=Null
 AS 
 SELECT Item,TipoProd,VrInicial,VrFinal,Unidad,Tarifa,TipoTarifa,Periodo,CdSubgrupo,TipoMargen,IntvExcluido,IntvGlobal,IvaIncluido
 FROM Trn_MayBonif
 WHERE NContrato=@pmNContrato AND IdCia=@pmIdCia  AND CdSubgrupo<>'0'
 AND TipoProd LIKE ISNULL(@pmTipoProd,'%')
 ORDER BY Item
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMayBonifSub] @pmNContrato INT,@pmIdCia CHAR(2),@pmCdSubgrupo VARCHAR(8)=Null
,@pmTipoProd VARCHAR(20)=Null
 AS 
 SELECT Item,TipoProd,VrInicial,VrFinal,Unidad,Tarifa,TipoTarifa,Periodo,CdSubgrupo,TipoMargen,IntvExcluido,IntvGlobal,IvaIncluido
 FROM Trn_MayBonif
 WHERE NContrato=@pmNContrato AND IdCia=@pmIdCia  
  AND CdSubgrupo LIKE ISNULL(@pmCdSubgrupo,'%')
  AND TipoProd LIKE ISNULL(@pmTipoProd,'%')
 ORDER BY Item
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryKardexSubOpe] @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2)
 AS 
 SELECT IdSubgrupo,K.IdProducto AS tmIdProducto,DescripProd,TipoRef,Combo,EsProdBase AS tmEsProdBase,IdProv,ExcluidoImp,Electrocomb,CdOperario AS tmCdOperario
,COUNT(Item) AS SCANT,SUM(Entradas) AS SENT,SUM(Salidas) AS SSAL
,SUM(CASE TipDoc WHEN 'ENT' THEN VrCostoEnt-(NumInicial*Entradas) WHEN 'COM' THEN VrCostoEnt-(NumInicial*Entradas)
 ELSE VrCostoEnt END) AS SCOSENT
,SUM(CASE TipDoc WHEN 'DVE' THEN VrCostoSal-(NumInicial*Salidas) WHEN 'DEI' THEN VrCostoSal-(NumInicial*Salidas)
 ELSE VrCostoSal END) AS SCOSSAL
,SUM(VrPrecio*Entradas) AS SVALENT,SUM(VrPrecio*Salidas) AS SVALSAL
,SUM(VrBruto*Entradas) AS SBRUENT,SUM(VrBruto*Salidas) AS SBRUSAL
,SUM(Entradas*ImpGlobal) AS SGLOENT,SUM(Salidas*ImpGlobal) AS SGLOSAL
,SUM(Sobretasa*Entradas) AS SSOBENT,SUM(Sobretasa*Salidas) AS SSOBSAL
,SUM(TasaNac*Entradas) AS SNACENT,SUM(TasaNac*Salidas) AS SNACSAL
,SUM(TasaDep*Entradas) AS SDEPENT,SUM(TasaDep*Salidas) AS SDEPSAL
,SUM(TasaMun*Entradas) AS SMUNENT,SUM(TasaMun*Salidas) AS SMUNSAL
,SUM(Soldicom*Entradas) AS SSOLENT,SUM(Soldicom*Salidas) AS SSOLSAL
,SUM(CASE WHEN  Salidas>0 THEN VrIvaSal ELSE VrIvaEnt END) AS SIVA
,SUM(CASE WHEN  Salidas>0 THEN VrDctoSal ELSE VrDctoEnt END) AS SDCT
,SUM(CASE WHEN  Salidas>0 THEN VrReteSal ELSE VrReteEnt END) AS SRET
,SUM(CASE WHEN  Salidas>0 THEN VrIcaSal ELSE VrIcaEnt END) AS SICA
,SUM(OtroImpto*Entradas) AS SOTRENT,SUM(OtroImpto*Salidas) AS SOTRSAL
,SUM(Rec_Costo*Entradas) AS SRCOSENT,SUM(Rec_Costo*Salidas) AS SRCOSSAL
,SUM(MgenCont*Entradas) AS SMGENENT,SUM(MgenCont*Salidas) AS SMGENSAL,SUM(VrImpCon) AS SVICO
,SUM(CASE WHEN CantObseq>0 THEN VrIvaObseq ELSE 0 END) AS SIVAOBSQ
,SUM(CantObseq*VrUnitario) AS SBASCOSOBSQ,SUM(CantObseq*VrPrecio) AS SBASEOBSQ
,SUM(BaseIvaCom*Entradas) AS BASEIVAENT,SUM(BaseIvaCom*Salidas) AS BASEIVASAL 
,SUM(ImpCarbono*Entradas) AS IMPCARBENT,SUM(ImpCarbono*Salidas) AS IMPCARBSAL 
,SUM(CASE WHEN (TarifaIva>0 AND P.TipoZonaFront='F') THEN ((Entradas*BaseIvaCom)*TarifaIva)/100 ELSE 0 END) AS SIVAZFENT
,SUM(CASE WHEN (TarifaIva>0 AND P.TipoZonaFront='F') THEN ((Salidas*BaseIvaCom)*TarifaIva)/100 ELSE 0 END) AS SIVAZFSAL
--Para los ajustes con cantidad en cero (0)
,SUM(VrUnitario) AS SCOSAJU,SUM(OtroImpto) AS SDVEAJU,SUM(VrImvCosto) AS SIMVCOS
 FROM Trn_Kardex AS K INNER JOIN ProdMcias AS P ON K.IdProducto=P.IdProducto
 WHERE TipDoc=@pmTipDoc AND Documento=@pmDocumento AND IdCia=@pmIdCia
 GROUP BY  IdSubgrupo,K.IdProducto,DescripProd,TipoRef,Combo,EsProdBase,IdProv,ExcluidoImp,Electrocomb,CdOperario
 ORDER BY IdSubgrupo,K.IdProducto
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryKardexSub] @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2)
 AS 
 SELECT IdSubgrupo,K.IdProducto AS tmIdProducto,DescripProd,TipoRef,Combo,EsProdBase AS tmEsProdBase
,IdProv,ExcluidoImp,Electrocomb,COUNT(Item) AS SCANT,SUM(Entradas) AS SENT,SUM(Salidas) AS SSAL
,SUM(CASE TipDoc WHEN 'ENT' THEN VrCostoEnt-(NumInicial*Entradas) WHEN 'COM' THEN VrCostoEnt-(NumInicial*Entradas)
 ELSE VrCostoEnt END) AS SCOSENT
,SUM(CASE TipDoc WHEN 'DVE' THEN VrCostoSal-(NumInicial*Salidas) WHEN 'DEI' THEN VrCostoSal-(NumInicial*Salidas)
 ELSE VrCostoSal END) AS SCOSSAL
,SUM(VrPrecio*Entradas) AS SVALENT,SUM(VrPrecio*Salidas) AS SVALSAL
,SUM(VrBruto*Entradas) AS SBRUENT,SUM(VrBruto*Salidas) AS SBRUSAL
,SUM(Entradas*ImpGlobal) AS SGLOENT,SUM(Salidas*ImpGlobal) AS SGLOSAL
,SUM(Sobretasa*Entradas) AS SSOBENT,SUM(Sobretasa*Salidas) AS SSOBSAL
,SUM(TasaNac*Entradas) AS SNACENT,SUM(TasaNac*Salidas) AS SNACSAL
,SUM(TasaDep*Entradas) AS SDEPENT,SUM(TasaDep*Salidas) AS SDEPSAL
,SUM(TasaMun*Entradas) AS SMUNENT,SUM(TasaMun*Salidas) AS SMUNSAL
,SUM(Soldicom*Entradas) AS SSOLENT,SUM(Soldicom*Salidas) AS SSOLSAL
,SUM(CASE WHEN  Salidas>0 THEN VrIvaSal ELSE VrIvaEnt END) AS SIVA
,SUM(CASE WHEN  Salidas>0 THEN VrDctoSal ELSE VrDctoEnt END) AS SDCT
,SUM(CASE WHEN  Salidas>0 THEN VrReteSal ELSE VrReteEnt END) AS SRET
,SUM(CASE WHEN  Salidas>0 THEN VrIcaSal ELSE VrIcaEnt END) AS SICA
,SUM(OtroImpto*Entradas) AS SOTRENT,SUM(OtroImpto*Salidas) AS SOTRSAL
,SUM(Rec_Costo*Entradas) AS SRCOSENT,SUM(Rec_Costo*Salidas) AS SRCOSSAL
,SUM(MgenCont*Entradas) AS SMGENENT,SUM(MgenCont*Salidas) AS SMGENSAL,SUM(VrImpCon) AS SVICO
,SUM(CASE WHEN CantObseq>0 THEN VrIvaObseq ELSE 0 END) AS SIVAOBSQ
,SUM(CantObseq*VrUnitario) AS SBASCOSOBSQ,SUM(CantObseq*VrPrecio) AS SBASEOBSQ
,SUM(BaseIvaCom*Entradas) AS BASEIVAENT,SUM(BaseIvaCom*Salidas) AS BASEIVASAL 
,SUM(ImpCarbono*Entradas) AS IMPCARBENT,SUM(ImpCarbono*Salidas) AS IMPCARBSAL 
,SUM(CASE WHEN (TarifaIva>0 AND P.TipoZonaFront='F') THEN ((Entradas*BaseIvaCom)*TarifaIva)/100 ELSE 0 END) AS SIVAZFENT
,SUM(CASE WHEN (TarifaIva>0 AND P.TipoZonaFront='F') THEN ((Salidas*BaseIvaCom)*TarifaIva)/100 ELSE 0 END) AS SIVAZFSAL
--Para los ajustes con cantidad en cero (0)
,SUM(VrUnitario) AS SCOSAJU,SUM(OtroImpto) AS SDVEAJU,SUM(VrImvCosto) AS SIMVCOS
 FROM Trn_Kardex AS K INNER JOIN ProdMcias AS P ON K.IdProducto=P.IdProducto
 WHERE TipDoc=@pmTipDoc AND Documento=@pmDocumento AND IdCia=@pmIdCia
 GROUP BY  IdSubgrupo,K.IdProducto,DescripProd,TipoRef,Combo,EsProdBase,IdProv,ExcluidoImp,Electrocomb
 ORDER BY IdSubgrupo,K.IdProducto
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNominaApo] @pmMes VARCHAR(6),@pmIdPeriodo VARCHAR(8)=Null,@pmIdInstala VARCHAR(4)=Null,@pmIdNom VARCHAR(4)=Null
,@pmIdArea VARCHAR(4)=Null,@pmIdDep VARCHAR(4)=Null,@pmEsRetiro BIT=Null,@pmIdConcepto VARCHAR(4)=Null,@pmCodFondo VARCHAR(8)=Null,@pmIdCia CHAR(2)=Null
AS 
 SELECT N.KeyRegistro AS KeyReg,N.IdEmpleado AS IdEmpldo,Apellidos,Nombres,N.NContrato AS NumContto,FecInicial,FecFinal,N.IdDep AS CodDep,N.IdInstala AS CodInstala,N.IdCCosto AS CodCcto
,N.IdSubCos AS CodSubcc,N.IdNom AS CodNom,N.pVehiculo AS PlacaVeh,N.VrSalario AS Salbasico,EsRetiro,NLiqRet,IdFonPen,IdFonEps,IdFonArp,IdFonCes,IdCajaCom,FecIngreso,FecRetiro,IdArea,C.IdClase AS CodClaRie
,CR.Tarifa AS TarRie,TipoTrabj,VrAuxTrans,VrUpc,SalIntegral,SalMinimo,C.Inactivo AS Con_Inctivo,D.IdConcepto AS CodConcepto,Concepto,ClaseCon,CN.Tarifa AS TarifDft,ClaseLiq,CantDevg,CantDed,VrTotDevg,VrTotDed,VrOrigen,D.Tarifa AS Tarfa
,DiasCalc,DiasNov,DiasCalc-DiasNov AS DiasNeto,CodFondo,IdCot,SubTipoCot,DenyPEN,DenyEPS,DenyARP,DenyCaj,DenyDcto,LiqEspSalud,DctoSalud,PagoSalud,D.VrBaseLiq,TN.NomDiario
 FROM Trn_Nomina AS N INNER JOIN Trn_NomDetalle AS D ON N.IdPeriodo=D.IdPeriodo AND N.KeyRegistro=D.KeyRegistro
 INNER JOIN Empleados AS E ON N.IdEmpleado=E.IdEmpleado  INNER JOIN Emp_Contrato AS C ON N.NContrato=C.NContrato
 INNER JOIN ClaseRie AS CR ON C.IdClase=CR.IdClase  INNER JOIN NomConceptos AS CN ON D.IdConcepto=CN.IdConcepto
 INNER JOIN TiposNom AS TN ON C.IdNom=TN.IdNom
 WHERE  SUBSTRING(N.IdPeriodo,1,6)=@pmMes AND NLiqRet>=0 AND D.IdConcepto IN ('PEN','RIE','SAL','FSP','FSU','SEN','ICB','COM','DCP','DCS','AVP','AVA')
 AND N.IdPeriodo LIKE ISNULL(@pmIdPeriodo,'%') AND N.IdInstala LIKE ISNULL(@pmIdInstala,'%') AND N.IdNom LIKE ISNULL(@pmIdNom,'%')
 AND IdArea LIKE ISNULL(@pmIdArea,'%') AND N.IdDep LIKE ISNULL(@pmIdDep,'%') AND D.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND IdCia LIKE ISNULL(@pmIdCia,'%%')  AND CodFondo LIKE ISNULL(@pmCodFondo,'%')
 AND (EsRetiro=ISNULL(@pmEsRetiro,0) or EsRetiro=ISNULL(@pmEsRetiro,1))
 UNION ALL
 SELECT D.IdPeriodo,V.IdEmpleado,Apellidos,Nombres,V.NContrato,V.FecInicial,V.FecFinal,C.IdDep,C.IdInstala,C.IdCCosto,C.IdSubCos,C.IdNom,V.pVehiculo
,V.VrSalario,0,0,IdFonPen,E.IdFonEps,IdFonArp,IdFonCes,IdCajaCom,C.FecIngreso,C.FecRetiro,C.IdArea,C.IdClase,0,C.TipoTrabj,C.VrAuxTrans,C.VrUpc
,C.SalIntegral,C.SalMinimo,C.Inactivo,D.IdConcepto,Concepto,ClaseCon,CN.Tarifa,'NOMINA',0,D.Cantidad,D.VrDevgado,D.VrDeducido,D.VrBaseLiq,D.Tarifa
,D.Cantidad,0,D.Cantidad,E.IdFonEps,C.IdCot,C.SubTipoCot,C.DenyPEN,C.DenyEPS,C.DenyARP,C.DenyCaj,C.DenyDcto,LiqEspSalud,DctoSalud,PagoSalud,D.VrBaseLiq,TN.NomDiario
 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 TiposNom AS TN ON C.IdNom=TN.IdNom INNER JOIN NomConceptos AS CN ON D.IdConcepto=CN.IdConcepto
 WHERE SUBSTRING(D.IdPeriodo,1,6)=@pmMes AND D.IdConcepto IN ('DCP','DCS','FSP','FSU') AND V.Anulado=0
 AND D.IdPeriodo LIKE ISNULL(@pmIdPeriodo,'%') AND C.IdInstala LIKE ISNULL(@pmIdInstala,'%') AND C.IdNom LIKE ISNULL(@pmIdNom,'%')
 AND C.IdArea LIKE ISNULL(@pmIdArea,'%') AND C.IdDep LIKE ISNULL(@pmIdDep,'%')
 AND C.IdCia LIKE ISNULL(@pmIdCia,'%%') AND D.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 ORDER BY  N.IdEmpleado,N.NContrato,D.IdConcepto
GO