if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsConcDiversos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsConcDiversos]
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].[paInsRutas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsRutas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTercCliePrestamo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTercCliePrestamo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTercCliePrestamo_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTercCliePrestamo_Sel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryConcDiversos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryConcDiversos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryConcDiversosLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryConcDiversosLta]
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
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].[paQryRutas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryRutas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryRutasLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryRutasLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTercCliePrestamo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTercCliePrestamo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTercCliePrestamoLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTercCliePrestamoLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraCausacionLiq]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraCausacionLiq]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpConcDiversos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpConcDiversos]
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].[paUpRutas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpRutas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTercCliePrestamo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTercCliePrestamo]
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
 FROM Trn_Nomina AS N INNER JOIN Trn_NomDetalle AS D ON N.IdPeriodo=D.IdPeriodo AND N.KeyRegistro=D.KeyRegistro
 INNER JOIN Empleados AS E ON N.IdEmpleado=E.IdEmpleado  INNER JOIN Emp_Contrato AS C ON N.NContrato=C.NContrato
 INNER JOIN ClaseRie AS CR ON C.IdClase=CR.IdClase  INNER JOIN NomConceptos AS CN ON D.IdConcepto=CN.IdConcepto
 WHERE  SUBSTRING(N.IdPeriodo,1,6)=@pmMes AND NLiqRet>=0 AND D.IdConcepto IN ('PEN','RIE','SAL','FSP','FSU','SEN','ICB','COM','DCP','DCS','AVP','AVA')
 AND N.IdPeriodo LIKE ISNULL(@pmIdPeriodo,'%') AND N.IdInstala LIKE ISNULL(@pmIdInstala,'%') AND N.IdNom LIKE ISNULL(@pmIdNom,'%')
 AND IdArea LIKE ISNULL(@pmIdArea,'%') AND N.IdDep LIKE ISNULL(@pmIdDep,'%')   AND D.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND IdCia LIKE ISNULL(@pmIdCia,'%%')  AND CodFondo LIKE ISNULL(@pmCodFondo,'%')
 AND (EsRetiro=ISNULL(@pmEsRetiro,0) or EsRetiro=ISNULL(@pmEsRetiro,1))
 UNION ALL
 SELECT D.IdPeriodo,V.IdEmpleado,Apellidos,Nombres,V.NContrato,V.FecInicial,V.FecFinal,C.IdDep,C.IdInstala,C.IdCCosto,C.IdSubCos,C.IdNom,V.pVehiculo
,V.VrSalario,0,0,IdFonPen,E.IdFonEps,IdFonArp,IdFonCes,IdCajaCom,C.FecIngreso,C.FecRetiro,C.IdArea,C.IdClase,0,C.TipoTrabj,C.VrAuxTrans,C.VrUpc
,C.SalIntegral,C.SalMinimo,C.Inactivo,D.IdConcepto,Concepto,ClaseCon,CN.Tarifa,'NOMINA',0,D.Cantidad,D.VrDevgado,D.VrDeducido,D.VrBaseLiq,D.Tarifa
,D.Cantidad,0,D.Cantidad,E.IdFonEps,C.IdCot,C.SubTipoCot,C.DenyPEN,C.DenyEPS,C.DenyARP,C.DenyCaj,C.DenyDcto
 FROM Trn_NomVac AS V INNER JOIN Trn_NomVacDet AS D ON V.IdEmpleado=D.IdEmpleado AND V.Numero=D.Numero
 INNER JOIN Empleados AS E ON V.IdEmpleado=E.IdEmpleado INNER JOIN Emp_Contrato AS C ON V.NContrato=C.NContrato
 INNER JOIN NomConceptos AS CN ON D.IdConcepto=CN.IdConcepto
 WHERE SUBSTRING(D.IdPeriodo,1,6)=@pmMes AND D.IdConcepto IN ('DCP','DCS','FSP','FSU') AND V.Anulado=0
 AND D.IdPeriodo LIKE ISNULL(@pmIdPeriodo,'%') AND C.IdInstala LIKE ISNULL(@pmIdInstala,'%') AND C.IdNom LIKE ISNULL(@pmIdNom,'%')
 AND C.IdArea LIKE ISNULL(@pmIdArea,'%') AND C.IdDep LIKE ISNULL(@pmIdDep,'%')
 AND C.IdCia LIKE ISNULL(@pmIdCia,'%%')
 ORDER BY  N.IdEmpleado,N.NContrato,D.IdConcepto
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTercCliePrestamoLta]  @pmIdGrupo VARCHAR(4)=Null,@pmIdVend VARCHAR(16)=Null,@pmIdSzona VARCHAR(4)=Null
,@pmIdTipoTerc  VARCHAR(4)=Null,@pmFecIngIni SMALLDATETIME=Null,@pmFecIngFin SMALLDATETIME=Null,@pmIdEstado VARCHAR(4)=Null
 AS 
 SELECT IdClie,T.RazonSocial AS Razon_Social,T.TipoId AS TipId,T.Dv AS DvNit,T.NomCial AS NombreCmcial,T.SiglaRaz AS SiglaRazSocial
,IdAgencia,NomAgencia,NitRepLeg,NomRepLeg,NitContac,NomContac,TelContac,emlContac,CargContac,T.Direccion AS TercDireccion,T.IdLocal AS CdCiudad,L.Localidad AS Ciudad,L.IdDep AS CdDpto
,D.Departamento AS Dpto,T.Telefono AS Tlefono,T.Fax AS NumFax,T.TelMovil AS TelCeluar,T.SitioWeb AS WebSite,T.e_mail AS Email
,DirAgencia,IdLocalAge,LE.Localidad AS CiuAgencia,DE.Departamento AS DptoAgencia,C.IdTipoTerc AS CdTipoTerc,TipoTercero
,SZ.IdZona AS CdZona,Zona,C.IdSzona AS CdSubzona,Subzona,C.IdGrupo AS CdGrupo,GrupoClie,T.IdSector AS CdSector,SectorEco,T.IdProf AS CdProf,Profesion,T.IdRegimen AS CdRgmen,Regimen
,T.TipEnte AS TipoEnte,IdVend,TV.RazonSocial AS NomVendedor,C.IdClase AS CdClase,ClaseCuenta,NumCuenta,C.IdBanco AS CdBanco,Banco
,EsExento,CalcIntMora,DiasGracia,FecIngreso,FecVigencia,FecRetiro,VrSalBasico,TipoSalario,MaxDcto,MatMerc,FecMat,IncRetFte,NumTarj,FecVenceTc,FechaImp,CodCCosto,CodSubCosto
,Comentarios,C.IdEstado AS CdEstado,EC.Estado AS EstadoClie,C.Inactivo AS ClieInactivo,FechaCrea,FechaAct,C.IdUsuario AS CdUsuario,Usuario
,T.IniStgNom AS PosNomb,T.IdLugarCed AS IdLugCed,LC.Localidad AS LugarCed,T.FecExpCed AS FecExpCedula,T.Inactivo AS TercInactivo,T.Codigo AS TercCodigo
 FROM TercCliePrestamo AS C  INNER JOIN Terceros AS T ON C.IdClie=T.IdTercero  INNER JOIN Terceros AS TV ON C.IdVend=TV.IdTercero
 INNER JOIN GruposCli AS G ON C.IdGrupo=G.IdGrupo INNER JOIN TiposTerc AS TC ON C.IdTipoTerc=TC.IdTipoTerc
 INNER JOIN Subzonas AS SZ ON C.IdSzona=SZ.IdSzona  INNER JOIN Zonas AS Z ON SZ.IdZona=Z.IdZona 
 INNER JOIN EstadoTer AS EC ON C.IdEstado=EC.IdEstado INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario
 INNER JOIN Localidades AS LE ON C.IdLocalAge=LE.IdLocal INNER JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep
 INNER JOIN  SectoresEco AS S ON T.IdSector=S.IdSector  INNER JOIN RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen
 LEFT JOIN ClaseCta AS CTA ON  C.IdClase=CTA.IdClase LEFT JOIN Bancos AS B ON C.IdBanco=B.IdBanco 
 LEFT JOIN Localidades AS LC ON T.IdLugarCed=LC.IdLocal  LEFT JOIN Profesiones AS PF ON T.IdProf=PF.IdProf 
 WHERE C.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND IdVend LIKE ISNULL(@pmIdVend,'%')
 AND  C.IdSzona LIKE ISNULL(@pmIdSzona,'%')  AND  C.IdTipoTerc  LIKE ISNULL(@pmIdTipoTerc ,'%')
 AND C.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 AND (FecIngreso>=ISNULL(@pmFecIngIni,CAST('19100101' AS SMALLDATETIME)) AND FecIngreso<=ISNULL(@pmFecIngFin,CAST('20781230' AS SMALLDATETIME)))
 ORDER BY T.RazonSocial
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/**********************************************************************************
-- =============================================
-- Author:		George Ardila
-- Create date: 16/10/2013
-- Description:	Procedimiento para traer otros pagos por cliente en el formato de liquidacion.

* History ------------------------------------------------------------------------
* Author           	|Date        	|Version	|Reason
GEORGE ARDILA		2013/10/19	    1.00 	    se crea procedimiento para traer otros pagos por cliente en el formato de liquidacion
GEROGE ARDILA		2014/01/06		1.01		se cambian parametros y consulta para traer los otros pagos que se cruzan en la liquidacion
-- =============================================
-- =============================================
***********************************************************************************/
CREATE PROCEDURE [dbo].[paQryTraCausacionLiq] 
 @pmTipDoc VARCHAR(3), --CODIGO DEL DOCUMENTO DE LIQUIDACION 
 @pmLiquidacionIni INT, --NUMERO INICIAL DE LIQUIDACION 
 @pmLiquidacionFin INT, --NUMERO FINAL DE LIQUIDACION
 @pmIdCia CHAR(2) --COMPAÑIA EN LA QUE SE REALIZO LA LIQUIDACION
AS
--CONSULTA PARA TRAER OTROS PAGOS QUE SE REALIZARON EN LA LIQUIDACION
SELECT C.TipDoc,C.Causacion,C.IdCia,C.Fecha,CD.FecVence,C.IdProv,CD.IdConcepto,CD.Descripcion,CD.TipoConc,CD.VrCredito,CD.CdCuenta,CD.NitTercero,CD.pVehiculo,CD.TipoAfiVehic,CD.TipFac,CD.Factura,CD.IdCiaFac,CD.ItemFac
,EGR.TipCom,EGR.Comprobante,EGR.IdCia AS IdCiaCom,EGR.Item,EGR.EsEgreso,EGR.DetAbono,EGR.Fecha AS FecEgr,ISNULL(EGR.VrAbono,0)AS VrAbono
,L.TipDoc AS TipLiq,L.Liquidacion,L.IdCia AS IdCiaLiq,L.FecIniOdp,L.FecFinOdp
FROM Trn_TraLiquida AS L INNER JOIN Trn_TraCauDetalle AS CD ON L.TipDoc=CD.TipoLiq AND L.Liquidacion=CD.NumLiquida AND L.IdCia=CD.CiaLiquida
INNER JOIN Trn_TraCausacion AS C ON CD.TipDoc=C.TipDoc AND CD.Causacion=C.Causacion AND CD.IdCia=C.IdCia
LEFT JOIN Trn_EgrFactura AS EGR ON CD.TipFac=EGR.TipFac AND CD.Factura=EGR.Factura AND CD.IdCiaFac=EGR.IdCiaFac AND CD.ItemFac=EGR.ItemFac AND CD.NitTercero=EGR.IdProveedor
WHERE  L.TipDoc=@pmTipDoc AND L.Liquidacion BETWEEN @pmLiquidacionIni AND @pmLiquidacionFin AND L.IdCia=@pmIdCia AND TipoConc='OTROSPAGOS'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTercCliePrestamo] @pmIdClie VARCHAR(16),@pmIdAgencia VARCHAR(16)
 AS 
 SELECT IdClie,IdAgencia,NitRepLeg,NomRepLeg,NitContac,NomContac,TelContac,emlContac,CargContac,NomAgencia,DirAgencia,IdLocalAge,IdSzona,IdGrupo,IdTipoTerc,IdVend,IdClase,NumCuenta,IdBanco
,EsExento,CalcIntMora,DiasGracia,FecIngreso,FecVigencia,FecRetiro,VrSalBasico,MaxDcto,TipoSalario,MatMerc,FecMat,Comentarios,IdEstado,Inactivo,NumTarj,PwdTarj,FecVenceTc,IncRetFte
,FechaCrea,FechaAct,FechaImp,CodCCosto,CodSubCosto,IdUsuario
 FROM TercCliePrestamo
 WHERE IdClie=@pmIdClie AND IdAgencia=@pmIdAgencia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTercCliePrestamo] @pmIdClie VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmNitRepLeg VARCHAR(16),@pmNomRepLeg VARCHAR(150),@pmNitContac VARCHAR(16),@pmNomContac VARCHAR(150),@pmTelContac VARCHAR(20),@pmemlContac VARCHAR(100)
,@pmCargContac VARCHAR(50),@pmIdSzona VARCHAR(4),@pmIdGrupo VARCHAR(4),@pmIdTipoTerc VARCHAR(4),@pmIdVend VARCHAR(16),@pmIdClase VARCHAR(4),@pmNumCuenta VARCHAR(30),@pmIdBanco VARCHAR(4),@pmEsExento BIT,@pmCalcIntMora BIT,@pmDiasGracia INT
,@pmFecIngreso SMALLDATETIME,@pmFecVigencia SMALLDATETIME,@pmFecRetiro SMALLDATETIME,@pmVrSalBasico MONEY,@pmMaxDcto DECIMAL(14,4),@pmMatMerc VARCHAR(20),@pmFecMat SMALLDATETIME,@pmNomAgencia VARCHAR(150),@pmDirAgencia VARCHAR(250)
,@pmIdLocalAge VARCHAR(8),@pmComentarios VARCHAR(2000),@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmTipoSalario CHAR(1),@pmNumTarj VARCHAR(30),@pmPwdTarj VARCHAR(30),@pmFecVenceTc SMALLDATETIME,@pmIncRetFte BIT,@pmFechaImp SMALLDATETIME
,@pmCodCCosto VARCHAR(16),@pmCodSubCosto VARCHAR(16),@pmFechaCrea SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO TercCliePrestamo (IdClie,IdAgencia,IdSzona,IdGrupo,IdTipoTerc,IdVend,IdClase,NumCuenta,IdBanco,NomAgencia,DirAgencia,IdLocalAge,NitRepLeg,NomRepLeg,NitContac,NomContac,TelContac,emlContac,CargContac,EsExento,CalcIntMora,DiasGracia,FecIngreso,FecVigencia,FecRetiro,VrSalBasico
,MaxDcto,TipoSalario,MatMerc,FecMat,Comentarios,IdEstado,Inactivo,FechaCrea,IdUsuario,NumTarj,PwdTarj,FecVenceTc,IncRetFte,FechaImp,CodCCosto,CodSubCosto)
 VALUES (@pmIdClie,@pmIdAgencia,@pmIdSzona,@pmIdGrupo,@pmIdTipoTerc,@pmIdVend,@pmIdClase,@pmNumCuenta,@pmIdBanco,@pmNomAgencia,@pmDirAgencia,@pmIdLocalAge,@pmNitRepLeg,@pmNomRepLeg,@pmNitContac,@pmNomContac,@pmTelContac,@pmemlContac,@pmCargContac,@pmEsExento
,@pmCalcIntMora,@pmDiasGracia,@pmFecIngreso,@pmFecVigencia,@pmFecRetiro,@pmVrSalBasico,@pmMaxDcto,@pmTipoSalario,@pmMatMerc,@pmFecMat,@pmComentarios,@pmIdEstado,@pmInactivo,@pmFechaCrea,@pmIdUsuario,@pmNumTarj,@pmPwdTarj,@pmFecVenceTc,@pmIncRetFte,@pmFechaImp,@pmCodCCosto,@pmCodSubCosto)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTercCliePrestamo] @pmIdClie VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmNitRepLeg VARCHAR(16),@pmNomRepLeg VARCHAR(150),@pmNitContac VARCHAR(16),@pmNomContac VARCHAR(150),@pmTelContac VARCHAR(20),@pmemlContac VARCHAR(100),@pmCargContac VARCHAR(50)
,@pmIdSzona VARCHAR(4),@pmIdGrupo VARCHAR(4),@pmIdTipoTerc VARCHAR(4),@pmIdVend VARCHAR(16),@pmIdClase VARCHAR(4),@pmNumCuenta VARCHAR(30),@pmIdBanco VARCHAR(4),@pmEsExento BIT,@pmCalcIntMora BIT,@pmDiasGracia INT,@pmFecIngreso SMALLDATETIME,@pmFecVigencia SMALLDATETIME
,@pmFecRetiro SMALLDATETIME,@pmVrSalBasico MONEY,@pmMaxDcto DECIMAL(14,4),@pmMatMerc VARCHAR(20),@pmFecMat SMALLDATETIME,@pmNomAgencia VARCHAR(150),@pmDirAgencia VARCHAR(250),@pmIdLocalAge VARCHAR(8),@pmComentarios VARCHAR(2000),@pmIdEstado VARCHAR(4)
,@pmInactivo BIT,@pmTipoSalario CHAR(1),@pmNumTarj VARCHAR(30),@pmPwdTarj VARCHAR(30),@pmFecVenceTc SMALLDATETIME,@pmIncRetFte BIT,@pmFechaImp SMALLDATETIME,@pmCodCCosto VARCHAR(16),@pmCodSubCosto VARCHAR(16),@pmFechaAct SMALLDATETIME
 AS 
 UPDATE TercCliePrestamo SET NitRepLeg=@pmNitRepLeg,NomRepLeg=@pmNomRepLeg,NitContac=@pmNitContac,NomContac=@pmNomContac,TelContac=@pmTelContac,emlContac=@pmemlContac,CargContac=@pmCargContac,IdSzona=@pmIdSzona,IdGrupo=@pmIdGrupo,IdTipoTerc=@pmIdTipoTerc,IdVend=@pmIdVend
,IdClase=@pmIdClase,NumCuenta=@pmNumCuenta,IdBanco=@pmIdBanco,EsExento=@pmEsExento,CalcIntMora=@pmCalcIntMora,DiasGracia=@pmDiasGracia,FecIngreso=@pmFecIngreso,FecVigencia=@pmFecVigencia,FecRetiro=@pmFecRetiro,VrSalBasico=@pmVrSalBasico,MaxDcto=@pmMaxDcto,MatMerc=@pmMatMerc,FecMat=@pmFecMat
,Comentarios=@pmComentarios,IdEstado=@pmIdEstado,NomAgencia=@pmNomAgencia,DirAgencia=@pmDirAgencia,IdLocalAge=@pmIdLocalAge,Inactivo=@pmInactivo,FechaAct=@pmFechaAct,TipoSalario=@pmTipoSalario,NumTarj=@pmNumTarj,PwdTarj=@pmPwdTarj,FecVenceTc=@pmFecVenceTc
,IncRetFte=@pmIncRetFte,FechaImp=@pmFechaImp,CodCCosto=@pmCodCCosto,CodSubCosto=@pmCodSubCosto
 WHERE IdClie=@pmIdClie AND IdAgencia=@pmIdAgencia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTercCliePrestamo_Sel] @pmIdClie VARCHAR(16),@pmNewIdClie VARCHAR(16)
 AS 
 IF EXISTS (SELECT IdClie FROM  TercCliePrestamo WHERE  IdClie=@pmIdClie)
  INSERT INTO TercCliePrestamo (IdClie,IdAgencia,IdSzona,IdGrupo,IdTipoTerc,IdVend,IdClase,NumCuenta,IdBanco,NomAgencia,DirAgencia,IdLocalAge,NitRepLeg,NomRepLeg,NitContac,NomContac,TelContac,emlContac,CargContac,EsExento,CalcIntMora,DiasGracia,FecIngreso,FecVigencia,FecRetiro,VrSalBasico
 ,MaxDcto,TipoSalario,MatMerc,FecMat,Comentarios,IdEstado,Inactivo,FechaCrea,IdUsuario,NumTarj,PwdTarj,FecVenceTc,IncRetFte,FechaImp,CodCCosto,CodSubCosto)
 SELECT @pmNewIdClie,IdAgencia,IdSzona,IdGrupo,IdTipoTerc,IdVend,IdClase,NumCuenta,IdBanco,NomAgencia,DirAgencia,IdLocalAge,NitRepLeg,NomRepLeg,NitContac,NomContac,TelContac,emlContac,CargContac,EsExento,CalcIntMora,DiasGracia,FecIngreso,FecVigencia,FecRetiro,VrSalBasico
 ,MaxDcto,TipoSalario,MatMerc,FecMat,Comentarios,IdEstado,Inactivo,FechaCrea,IdUsuario,NumTarj,PwdTarj,FecVenceTc,IncRetFte,FechaImp,CodCCosto,CodSubCosto
 FROM TercCliePrestamo WHERE IdClie=@pmIdClie
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryRutasLta]  @pmIdLocOri VARCHAR(8)=Null,@pmIdLocDes VARCHAR(8)=Null,@pmEsLocal BIT=Null
 AS 
 SELECT IdRuta,Ruta,IdLocOri,LO.Localidad AS CiuOrigen,LO.IdDep AS CdDptoOrig,D.Departamento AS DptoOrigen,IdLocDes
,LD.Localidad AS CiuDestino,LD.IdDep AS CdDptoDest,DD.Departamento AS DptoDestino,EsLocal,Distkmt,TmHoras,CodigoMt,FleteTon,CdCiaTurno,R.Inactivo AS Inactvo
 FROM Rutas AS R INNER JOIN Localidades AS LO ON R.IdLocOri=LO.IdLocal
 INNER JOIN Departamentos AS D ON LO.IdDep=D.IdDep
 INNER JOIN Localidades AS LD ON R.IdLocDes=LD.IdLocal
 INNER JOIN Departamentos AS DD ON LD.IdDep=DD.IdDep
 WHERE IdLocOri like ISNULL(@pmIdLocOri,'%') AND IdLocDes LIKE ISNULL(@pmIdLocDes,'%')
 AND (EsLocal=ISNULL(@pmEsLocal,0) OR EsLocal=ISNULL(@pmEsLocal,1))
 ORDER BY IdRuta
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),@pmTimeSys SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_NomNov (IdPeriodo,IdEmpleado,Item,IdConcepto,Detalle,Cantidad,VrUnitario,VrTotal,VrOrigen,Tarifa,OrigCargue,TimeSys,IdUsuario,NumDocRef)
 VALUES (@pmIdPeriodo,@pmIdEmpleado,@pmItem,@pmIdConcepto,@pmDetalle,@pmCantidad,@pmVrUnitario,@pmVrTotal,@pmVrOrigen,@pmTarifa,@pmOrigCargue, @pmTimeSys,@pmIdUsuario,@pmNumDocRef)
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
 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),@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,FecUpdate=@pmFecUpdate
 WHERE IdPeriodo=@pmIdPeriodo AND IdEmpleado=@pmIdEmpleado AND Item=@pmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryConcDiversosLta] @pmGrupo VARCHAR(20)=Null,@pmEsServicio BIT=Null
 AS 
 SELECT IdConcepto,Concepto,Grupo,EsServicio,DeManfsto,CdIva,TI.Tarifa AS TarfIva,CdRet
,TR.Tarifa AS TarfRet,CdIca,TC.Tarifa AS TarfIca,CdAutoret,IdCueDeb,PD.NomCuenta AS NomCueDeb,IdCueCre,PC.NomCuenta AS NomCueCre
,D.Inactivo AS Inactvo,OrdenServ,NovedadNom,CdConcDeveng,CdConcDed,D.IdUsuario AS IdUsuari,Usuario,D.FechaAdd AS Fech_Add,D.FechaUpdate  AS Fech_Update
 FROM ConcDiversos AS D INNER JOIN  adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
 LEFT JOIN  Tablapor AS TI ON D.CdIva=TI.IdTarifa
 LEFT JOIN  Tablapor AS TR ON D.CdRet=TR.IdTarifa
 LEFT JOIN  Tablapor AS TC ON D.CdIca=TC.IdTarifa
 LEFT JOIN Puc AS PD ON D.IdCueDeb=PD.IdCuenta
 LEFT JOIN Puc AS PC ON D.IdCueCre=PC.IdCuenta
 WHERE Grupo LIKE ISNULL(@pmGrupo,'%')
 AND  (EsServicio=ISNULL(@pmEsServicio,0) or EsServicio=ISNULL(@pmEsServicio,1))
 ORDER BY Concepto
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsRutas] @pmIdRuta VARCHAR(4),@pmRuta VARCHAR(100),@pmIdLocOri VARCHAR(8),@pmIdLocDes VARCHAR(8)
,@pmEsLocal BIT,@pmDistkmt DECIMAL(14,4),@pmTmHoras VARCHAR(8),@pmCodigoMt VARCHAR(5),@pmFleteTon MONEY,@pmCdCiaTurno CHAR(2),@pmInactivo BIT
 AS 
 INSERT INTO Rutas (IdRuta,Ruta,IdLocOri,IdLocDes,EsLocal,Distkmt,TmHoras,CodigoMt,FleteTon,Inactivo,CdCiaTurno)
 VALUES (@pmIdRuta,@pmRuta,@pmIdLocOri,@pmIdLocDes,@pmEsLocal,@pmDistkmt,@pmTmHoras,@pmCodigoMt,@pmFleteTon,@pmInactivo,@pmCdCiaTurno)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpRutas] @pmIdRuta VARCHAR(4),@pmRuta VARCHAR(100),@pmIdLocOri VARCHAR(8),@pmIdLocDes VARCHAR(8),@pmEsLocal BIT
,@pmDistkmt DECIMAL(14,4),@pmTmHoras VARCHAR(8),@pmCodigoMt VARCHAR(5),@pmFleteTon MONEY,@pmCdCiaTurno CHAR(2),@pmInactivo BIT
 AS 
 UPDATE Rutas SET Ruta=@pmRuta,IdLocOri=@pmIdLocOri,IdLocDes=@pmIdLocDes,EsLocal=@pmEsLocal,Distkmt=@pmDistkmt
,TmHoras=@pmTmHoras,CodigoMt=@pmCodigoMt,FleteTon=@pmFleteTon,CdCiaTurno=@pmCdCiaTurno,Inactivo=@pmInactivo
 WHERE IdRuta=@pmIdRuta
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryRutas] @pmIdRuta VARCHAR(4)
 AS 
 SELECT IdRuta,Ruta,IdLocOri,IdLocDes,EsLocal,Distkmt,TmHoras,CodigoMt,FleteTon,CdCiaTurno,Inactivo
 FROM Rutas
 WHERE IdRuta=@pmIdRuta
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryConcDiversos] @pmIdConcepto VARCHAR(4)
 AS 
 SELECT IdConcepto,Concepto,Grupo,EsServicio,DeManfsto,CdIva,CdRet,CdIca,IdCueDeb,IdCueCre,Inactivo
,CdAutoret,OrdenServ,NovedadNom,CdConcDeveng,CdConcDed,FechaAdd,FechaUpdate,IdUsuario
 FROM ConcDiversos
 WHERE IdConcepto=@pmIdConcepto
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsConcDiversos] @pmIdConcepto VARCHAR(4),@pmConcepto VARCHAR(250),@pmGrupo VARCHAR(20),@pmEsServicio BIT,@pmDeManfsto BIT,@pmCdIva VARCHAR(4)
,@pmCdRet VARCHAR(4),@pmCdIca VARCHAR(4),@pmIdCueDeb VARCHAR(16),@pmIdCueCre VARCHAR(16),@pmInactivo BIT
,@pmCdAutoret VARCHAR(4),@pmOrdenServ INT,@pmNovedadNom BIT,@pmCdConcDeveng VARCHAR(4),@pmCdConcDed VARCHAR(4),@pmFechaAdd SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO ConcDiversos (IdConcepto,Concepto,Grupo,EsServicio,DeManfsto,CdIva,CdRet,CdIca,CdAutoret,IdCueDeb,IdCueCre,Inactivo,FechaAdd,IdUsuario,OrdenServ,NovedadNom,CdConcDeveng,CdConcDed)
 VALUES (@pmIdConcepto,@pmConcepto,@pmGrupo,@pmEsServicio,@pmDeManfsto,@pmCdIva,@pmCdRet,@pmCdIca,@pmCdAutoret,@pmIdCueDeb,@pmIdCueCre,@pmInactivo,@pmFechaAdd
,@pmIdUsuario,@pmOrdenServ,@pmNovedadNom,@pmCdConcDeveng,@pmCdConcDed)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpConcDiversos] @pmIdConcepto VARCHAR(4),@pmConcepto VARCHAR(250),@pmGrupo VARCHAR(20),@pmEsServicio BIT
,@pmDeManfsto BIT,@pmCdIva VARCHAR(4),@pmCdRet VARCHAR(4),@pmCdIca VARCHAR(4),@pmIdCueDeb VARCHAR(16),@pmIdCueCre VARCHAR(16)
,@pmInactivo BIT,@pmCdAutoret VARCHAR(4),@pmOrdenServ INT,@pmNovedadNom BIT,@pmCdConcDeveng VARCHAR(4),@pmCdConcDed VARCHAR(4),@pmFechaUpdate SMALLDATETIME
 AS 
 UPDATE ConcDiversos SET Concepto=@pmConcepto,Grupo=@pmGrupo,EsServicio=@pmEsServicio,DeManfsto=@pmDeManfsto,CdIva=@pmCdIva
,CdRet=@pmCdRet,CdIca=@pmCdIca,Inactivo=@pmInactivo,IdCueDeb=@pmIdCueDeb,IdCueCre=@pmIdCueCre,CdAutoret=@pmCdAutoret
,NovedadNom=@pmNovedadNom,CdConcDeveng=@pmCdConcDeveng,CdConcDed=@pmCdConcDed,FechaUpdate=@pmFechaUpdate,OrdenServ=@pmOrdenServ
 WHERE IdConcepto=@pmIdConcepto
GO