if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTerceros]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTerceros]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTerceros_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTerceros_Sel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTercClienteLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTercClienteLta]
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].[paQryTercCndtoresLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTercCndtoresLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTerceros]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTerceros]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTercerosBus]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTercerosBus]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTercerosCod]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTercerosCod]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTercerosCta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTercerosCta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTercerosDso]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTercerosDso]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTercProveeLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTercProveeLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTercVendedorLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTercVendedorLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTerceros]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTerceros]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paDelDocSopDev]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paDelDocSopDev]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsDocSopDev]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsDocSopDev]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsDocSopDevDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsDocSopDevDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDocSopDev]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryDocSopDev]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDocSopDevFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryDocSopDevFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDocSopDevLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryDocSopDevLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpDocSopDev]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpDocSopDev]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenPagoRelDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenPagoRelDet]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTercVendedorLta] @pmIdLocal VARCHAR(8)=Null,@pmIdProf VARCHAR(4)=Null,@pmIdGrupo VARCHAR(4)=Null
,@pmIdSzona VARCHAR(4)=Null,@pmIdTarCms VARCHAR(4)=Null,@pmInactivo BIT=Null,@pmInactivoTer BIT=Null,@pmIdEstado VARCHAR(4)=Null
,@pmIdBanco VARCHAR(4)=Null
 AS 
 SELECT IdVend,Dv,TipoId,RazonSocial,T.Codigo AS CodVend,T.Direccion AS Dreccion,T.IdLocal AS CodCiud,L.Localidad AS Ciudad,D.Departamento AS Departmto,Telefono
,Fax,TelMovil,e_mail,T.IdProf AS CodProf,Profesion,V.IdGrupo AS CdGrupo,GrupoClie,CdJorn,Jornada,IdTarCms,CM.Tarifa AS TarfCms,IdTarCm2,TCM.Tarifa AS TarfCms2,V.IdSzona AS CdSubzona,Subzona
,IdLugarCed,LC.Localidad AS LugarCedula,DC.Departamento AS DptoLugcedula,FecExpCed,NomCial
,V.IdClase AS CdClase,ClaseCuenta,NumCuenta,V.IdBanco AS CdBanco,Banco,B.Direccion AS DirBanco
,EsCliente,EsProveedor,EsPropietario,EsVendedor,EsConductor,EsEmpleado,EsOperario
,Observacion,Comntarios,PathFoto,PathFirma,T.PN_RUT,T.IdEstado AS IdEstTerc,Estado,V.Inactivo AS Vend_inactivo,T.Inactivo AS Ter_Inactvo
,FecAdd,FecUpdate,V.IdUsuario AS IdUsuari,Usuario,IniStgNom,NColor
 FROM TercVendedor AS V INNER JOIN Terceros AS T ON V.IdVend=T.IdTercero  INNER JOIN GruposCli AS G ON V.IdGrupo=G.IdGrupo
 INNER JOIN Tablapor AS CM ON V.IdTarCms=CM.IdTarifa INNER JOIN Tablapor AS TCM ON V.IdTarCm2=TCM.IdTarifa
 INNER JOIN Subzonas AS SZ ON V.IdSzona=SZ.IdSzona  INNER JOIN adm_Usuarios AS U ON V.IdUsuario=U.IdUsuario
 INNER JOIN ClaseCta AS CTA ON  V.IdClase=CTA.IdClase  INNER JOIN Bancos AS B ON V.IdBanco=B.IdBanco
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep
 INNER JOIN EstadoTer AS ET ON T.IdEstado=ET.IdEstado INNER JOIN Localidades AS LC ON T.IdLugarCed=LC.IdLocal
 INNER JOIN Departamentos AS DC ON LC.IdDep=DC.IdDep  INNER JOIN Profesiones AS PF ON T.IdProf=PF.IdProf 
 LEFT JOIN Jornadas AS J ON V.CdJorn=J.IdJornada 
 WHERE T.IdLocal LIKE ISNULL(@pmIdLocal,'%') AND  T.IdProf LIKE ISNULL(@pmIdProf,'%')
 AND V.IdGrupo LIKE ISNULL(@pmIdGrupo,'%')  AND V.IdSzona LIKE ISNULL(@pmIdSzona,'%')
 AND IdTarCms LIKE ISNULL(@pmIdTarCms,'%') AND T.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 AND V.IdBanco LIKE ISNULL(@pmIdBanco,'%')
 AND (V.Inactivo=ISNULL(@pmInactivo,0) or V.Inactivo=ISNULL(@pmInactivo,1))
 AND (T.Inactivo=ISNULL(@pmInactivoTer,0) or T.Inactivo=ISNULL(@pmInactivoTer,1))
ORDER BY RazonSocial
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
,C.FecNacmto,C.Nacionalidad,C.Sexo,C.CdEstCivil,EstCivil,C.NumPerCargo,C.NivelEduc,C.TipoVivienda,C.EstratoSocial,C.IdOcupac,Ocupacion,C.VrPension,C.VrOtrosDev,C.VrPasivo,C.VrPatrimonio
,C.EmpresaLab,C.FecIngLab,C.TelEmpresa,C.DirEmpresa,C.EmailEmpresa,C.BaseAportes,C.CdTarifApo,TA.Tarifa AS TarifApo,C.VrAportes,C.FecInicioApo,C.FecFinalApo,C.DiaCausacApo,C.FecRetiroApo,C.CausalRetiro,C.SinDctoNom,C.SinLiqAporte,C.CabFamilia,C.DebitoAuto
,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.PN_RUT,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 
 LEFT JOIN Tablapor AS TA ON C.CdTarifApo=TA.IdTarifa LEFT JOIN Ocupaciones AS OC ON C.IdOcupac=OC.IdOcupac
 LEFT JOIN EstadoCiv AS EV ON C.CdEstCivil=EV.IdEstado
 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 OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTercProveeLta]  @pmIdLocal VARCHAR(8)=Null,@pmIdSector VARCHAR(8)=Null,@pmIdRegimen VARCHAR(4)=Null,@pmTipoId CHAR(1)=Null
,@pmIdProf VARCHAR(4)=Null,@pmIdGrupo VARCHAR(4)=Null,@pmIdLocOrd VARCHAR(8)=Null,@pmIdForma VARCHAR(4)=Null,@pmIdBanco VARCHAR(4)=Null
,@pmFecIngIni SMALLDATETIME=Null,@pmFecIngFin SMALLDATETIME=Null,@pmIdEstado VARCHAR(4)=Null,@pmIdEstadoTer VARCHAR(4)=Null,@pmInactivo BIT=Null
,@pmInactivoTer BIT=Null
 AS 
 SELECT IdProv,Dv,TipoId,RazonSocial,T.Codigo AS Codtercero,NomCial,SiglaRaz,NitRepLeg,NomRepLeg,T.Direccion AS Dreccion,T.IdLocal AS CodCiud,L.Localidad AS Ciudad,D.Departamento AS Departmto,Telefono,Fax,TelMovil
,DirOrdComp,IdLocOrd,LP.Localidad AS CiudadOrd,DP.Departamento AS DeptoOrd,SitioWeb,e_mail,TipEnte,T.IdSector AS CodSector,SectorEco,T.IdProf AS CodProf,Profesion,T.IdRegimen AS IdRegmen,Regimen,PV.IdGrupo AS CdGrupo,GrupoClie
,NitContac,NomContac,TelContac,emlContac,CargContac,DiasEntga,PV.IdPlazo AS CdPlazo,Plazo,PV.IdForma AS CdForma,FormaPago,PV.IdClase AS CdClase,ClaseCuenta,NumCuenta,PV.IdBanco AS CdBanco,Banco
,B.Direccion AS DirBanco,Contacto,CdMney,CdDct,Tarifa,Simbolo,Autoret,AutoIca,IncRet,IncRiv,IncIca,LiqFletes,CdRet,CdRiv,CodRetIca,GartProd,IdLugarCed,LC.Localidad AS LugarCedula,DC.Departamento AS DptoLugcedula,FecExpCed
,Observacion,Cmntarios,ObsFinanc,ObsJuridica,PathFoto,PathFirma,T.IdEstado AS IdEstTerc,ET.Estado AS EstadTerc,T.Inactivo AS Ter_Inactvo,PV.IdEstado AS IdEstProv,EP.Estado AS EstadProv,PV.Inactivo AS Prv_Inactvo,CodRetCom
,Contrato,NContrato,FecIngreso,FecVigencia,FecRetiro,EsCliente,EsProveedor,EsPropietario,EsAccnista,EsCiaAseg,PV.FechaAdd AS Fec_Add,PV.FechaUpdate AS Fec_Upd,PV.IdUsuario AS IdUsario,Usuario,IniStgNom,EP.NColor AS ClrEstado
,T.FechaNac,T.ImagenDoc1,T.ImagenDoc2,T.ImagenDoc3,CupoElectcom,T.PN_RUT
 FROM TercProvee AS PV INNER JOIN Terceros AS T ON PV.IdProv=T.IdTercero
 INNER JOIN GruposCli AS G ON PV.IdGrupo=G.IdGrupo INNER JOIN Plazos AS PZ ON PV.IdPlazo=PZ.IdPlazo
 INNER JOIN Formaspago AS F ON PV.IdForma=F.IdForma INNER JOIN ClaseCta AS CTA ON  PV.IdClase=CTA.IdClase
 INNER JOIN Bancos AS B ON PV.IdBanco=B.IdBanco INNER JOIN EstadoTer AS EP ON PV.IdEstado=EP.IdEstado
 INNER JOIN adm_Usuarios AS U ON PV.IdUsuario=U.IdUsuario   INNER JOIN Localidades AS LP ON PV.IdLocOrd=LP.IdLocal
 INNER JOIN Departamentos AS DP ON LP.IdDep=DP.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 R ON T.IdRegimen=R.IdRegimen
 INNER JOIN EstadoTer AS ET ON T.IdEstado=ET.IdEstado INNER JOIN Localidades AS LC ON T.IdLugarCed=LC.IdLocal 
 INNER JOIN Departamentos AS DC ON LC.IdDep=DC.IdDep  INNER JOIN Profesiones AS PF ON T.IdProf=PF.IdProf 
 LEFT JOIN Tablapor AS TD ON PV.CdDct=TD.IdTarifa
 WHERE T.IdLocal LIKE ISNULL(@pmIdLocal,'%') AND T.IdSector LIKE ISNULL(@pmIdSector,'%')  AND T.IdRegimen LIKE ISNULL(@pmIdRegimen,'%')
 AND TipoId LIKE ISNULL(@pmTipoId,'%') AND T.IdProf LIKE ISNULL(@pmIdProf,'%') AND PV.IdGrupo LIKE ISNULL(@pmIdGrupo,'%')
 AND IdLocOrd LIKE ISNULL(@pmIdLocOrd,'%') AND PV.IdForma LIKE ISNULL(@pmIdForma,'%') AND PV.IdBanco LIKE ISNULL(@pmIdBanco,'%')
 AND PV.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND T.IdEstado LIKE ISNULL(@pmIdEstadoTer,'%')
 AND (PV.Inactivo=ISNULL(@pmInactivo,0) or PV.Inactivo=ISNULL(@pmInactivo,1))
 AND (T.Inactivo=ISNULL(@pmInactivoTer,0) or T.Inactivo=ISNULL(@pmInactivoTer,1))
 AND (FecIngreso>=ISNULL(@pmFecIngIni,CAST('19100101' AS SMALLDATETIME)) AND FecIngreso<=ISNULL(@pmFecIngFin,CAST('20781230' AS SMALLDATETIME)))
ORDER BY RazonSocial
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTercCndtoresLta] @pmIdLocal VARCHAR(8)=Null,@pmIdProf VARCHAR(4)=Null,@pmIdGrupo VARCHAR(4)=Null
,@pmIdBanco VARCHAR(4)=Null,@pmIdLugar VARCHAR(8)=Null,@pmCatLicencia INT=Null,@pmIdModd VARCHAR(4)=Null,@pmEsEmpleado BIT=Null
,@pmEsPropietario BIT=Null,@pmIdEstado VARCHAR(4)=Null,@pmIdEstadoTer VARCHAR(4)=Null,@pmInactivo BIT=Null,@pmInactivoTer BIT=Null
 AS 
 SELECT IdConductor,RazonSocial,T.Codigo AS CodCondtor,TipoId,Dv,FecExpCed,IdLugarCed,LC.Localidad AS LugarCedula,T.Direccion AS Dirccion,T.IdLocal AS CodCiudad
,L.Localidad AS Ciudad,L.IdDep AS CodDpto,Departamento,Telefono,Fax,TelMovil,e_mail,T.IdProf AS CdProf,Profesion,C.IdGrupo AS CodGrupo,GrupoClie,C.IdModd AS CdModd,Modalidad,TipoSangre,FactorRh
,FecNacmto,LugarNacmto,Sexo,IdEstCivil,EstCivil,NHijos,LibMilitar,ClaseLib,FechaLib,DistMil,CertJudicial,FecJudicial,VigJudicial,Licencia,C.IdLugar AS CodLugarLic,LugarLic,CatLicencia,FecLicencia,VigLicencia,C.IdClase AS CdClaseCta
,Pasporte,VigPaspte,ClaseCuenta,NumCuenta,C.IdBanco AS CodBanco,Banco,CdLocCue,LCT.Localidad AS LugarCuenta,CdFonPen,FP.Fondo AS FondoPen,CdFonEps,FS.Fondo AS Eps,CdFonArp
,FR.Fondo AS FondoRiesgo,pVehiculo,PathFoto,PathFirma,PathLic,Observacion,Cmntarios,EsCliente,EsVendedor,EsPropietario,EsEmpleado,EsOperario,IniStgNom
,C.IdEstado AS CdEstado,EC.Estado AS EstadoCond,C.Inactivo AS Inactiv,T.IdEstado AS IdEstdTerc,ET.Estado AS EstadoTerc,T.Inactivo AS Ter_Inactivo
,NumTarjeta,EdicTarjeta,FecTarjeta,VigTarjeta,PasSeguridad,FecPasSeg,VigPasSeg,NitEmpTrans,C.IdUsuario AS IdUsuari,Usuario,EC.NColor AS EstdNColor,C.FechaAdd AS Fec_Add,C.FechaUpdate AS Fec_Upd
,Num_Contrato,Contrato_Activo,CdTipCon,Fec_Ingreso,Fec_Retiro,Fec_Vigencia,Fec_IngresoAnt,Fec_UltimoAcc,PasadoJudicial,Restriccion,FecPagoPen,FecPagoEps,FecPagoArp,CdTipoOper,TipoOperacion,T.ImagenDoc1,T.ImagenDoc2,T.ImagenDoc3,T.PN_RUT
 FROM TercCndtores AS C INNER JOIN Terceros AS T ON C.IdConductor=T.IdTercero
 INNER JOIN GruposCli AS G ON C.IdGrupo=G.IdGrupo INNER JOIN ExpLicencias AS LL ON C.IdLugar=LL.IdLugar
 INNER JOIN ClaseCta AS CTA ON  C.IdClase=CTA.IdClase  INNER JOIN Bancos AS B ON C.IdBanco=B.IdBanco
 INNER JOIN EstadoTer AS EC ON C.IdEstado=EC.IdEstado INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario
 INNER JOIN TiposMod AS M ON C.IdModd=M.IdModd INNER JOIN EstadoCiv AS ECV ON C.IdEstCivil=ECV.IdEstado
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep
 INNER JOIN Profesiones AS PF ON T.IdProf=PF.IdProf  INNER JOIN EstadoTer AS ET ON T.IdEstado=ET.IdEstado
 INNER JOIN  Localidades AS LC ON T.IdLugarCed=LC.IdLocal 
 LEFT JOIN  Localidades AS LCT ON  C.CdLocCue=LCT.IdLocal 
 LEFT JOIN Fondos AS FP ON C.CdFonPen=FP.IdFondo LEFT JOIN Fondos AS FS ON C.CdFonEps=FS.IdFondo
 LEFT JOIN Fondos AS FR ON C.CdFonArp=FR.IdFondo LEFT JOIN TiposOperac AS TP ON C.CdTipoOper=TP.IdTipoOper
 WHERE T.IdLocal LIKE ISNULL(@pmIdLocal,'%')  AND T.IdProf LIKE ISNULL(@pmIdProf,'%')
 AND C.IdGrupo LIKE ISNULL(@pmIdGrupo,'%')   AND C.IdBanco LIKE ISNULL(@pmIdBanco,'%')
 AND C.IdLugar LIKE ISNULL(@pmIdLugar,'%') AND C.IdModd LIKE ISNULL(@pmIdModd,'%')
 AND C.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND T.IdEstado LIKE ISNULL(@pmIdEstadoTer,'%')
 AND (CatLicencia>=ISNULL(@pmCatLicencia,0)  AND CatLicencia<=ISNULL(@pmCatLicencia,2147483647))
 AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
 AND (T.Inactivo=ISNULL(@pmInactivoTer,0) or T.Inactivo=ISNULL(@pmInactivoTer,1))
 AND (EsEmpleado=ISNULL(@pmEsEmpleado,0) or EsEmpleado=ISNULL(@pmEsEmpleado,1))
 AND (EsPropietario=ISNULL(@pmEsPropietario,0) or EsPropietario=ISNULL(@pmEsPropietario,1))
 ORDER BY T.RazonSocial
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTercClienteLta] @pmIdLocal VARCHAR(8)=Null,@pmIdSector VARCHAR(8)=Null,@pmIdRegimen VARCHAR(4)=Null,@pmTipoId CHAR(1)=Null
,@pmIdProf VARCHAR(4)=Null,@pmIdGrupo VARCHAR(4)=Null,@pmIdLocEnv VARCHAR(8)=Null,@pmIdForma VARCHAR(4)=Null,@pmIdBanco VARCHAR(4)=Null,@pmIdZona VARCHAR(4)=Null
,@pmIdSzona VARCHAR(4)=Null,@pmIdPlazo VARCHAR(4)=Null,@pmIdEstrato VARCHAR(4)=Null,@pmIdVend VARCHAR(16)=Null,@pmIdRuta VARCHAR(4)=Null
,@pmFecIngIni SMALLDATETIME=Null,@pmFecIngFin SMALLDATETIME=Null,@pmIdEstado VARCHAR(4)=Null,@pmIdEstadoTer VARCHAR(4)=Null,@pmInactivo BIT=Null
,@pmInactivoTer BIT=Null
 AS 
 SELECT IdClie,T.RazonSocial AS RaznSocial,T.TipoId AS TipId,T.Dv AS DvNit,T.NomCial AS NombreCmcial,T.SiglaRaz AS SiglaRazSocial
,NitRepLeg,NomRepLeg,NitContac,NomContac,TelContac,emlContac,CargContac,T.Direccion AS Dirccion,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,DirEnv,IdLocEnv,LE.Localidad AS CiuEnvio
,DE.Departamento AS Dptoenvio,DiasEntga,C.IdGrupo AS CdGrupo,GrupoClie,C.IdEstrato AS CdEstrato,Estrato,SZ.IdZona AS CdZona,Zona,C.IdSzona AS CdSubzona,Subzona
,C.IdRuta AS CdRuta,Ruta,C.IdPlazo AS CdPlazo,Plazo,NVmto,PlazosImp,CdPlazoComb,C.IdForma AS CdFrma,FormaPago,T.IdSector AS CdSector,SectorEco,T.IdProf AS CdProf,Profesion,T.IdRegimen AS CdRgmen
,Regimen,T.TipEnte AS TipoEnte,IdVend,TV.RazonSocial AS NomVendedor,NitFact,C.IdClase AS CdClase,ClaseCuenta,NumCuenta,C.IdBanco AS CdBanco,Banco,B.Direccion AS Dirbanco
,CdMney,CdDct,CdRet,CdRiv,CdCms,ExcIva,TrfIntMora,DiasGracia,LiqFletes,FactSold,Autoret,AutoIca,IncRet,IncRiv,IncIca,FactTipo,VrCupo,VrSaldo,UidClie,Contrato,NContrato,CiaContMay,FecIngreso,FecVigencia
,FecRetiro,MatMerc,FecMat,PathFoto,PathFirma,T.Observacion AS Observ,Cmntario1,Cmntario2,Cmntario3,CdBandera,TipoBandera,PrendGarant,FecUpCupo,TipoCliente,CodClieSicom,CdDiaEnt,C.IdEstado AS CdEst_clie,EC.Estado AS EstadoClie,C.Inactivo AS Clie_inactivo
,T.IdEstado AS CdEst_terc,ET.Estado AS Estad_terc,T.Inactivo AS Terc_inactivo,T.Codigo AS CodTerc,T.IdLugarCed AS IdLugCed,LC.Localidad AS LugarCed,T.FecExpCed AS FecExpCedula
,T.EsVendedor AS EsVend,T.EsConductor AS EsCond,T.EsPropietario AS EsProp,T.EsProveedor AS EsProvee,T.EsEmpleado AS EsEmpldo,T.EsOperario AS EsOper,T.EsAccnista AS EsAcc
,T.EsCiaAseg AS EsAseg,CdTipBloq,C.FechaAdd AS FecAdd,C.FechaUpdate AS FecUpd,C.IdUsuario AS IdUsuari,Usuario,T.IniStgNom AS PosNomb,Restric_Cia,CupoGalones,FecPlazoDoc,EdoRadicaDoc
,DescEdoDoc,ComIndustrial,NumLista,Termicas,CodRetCom,CodCCosto,CodSubCosto,T.FechaNac,T.ImagenDoc1,T.ImagenDoc2,T.ImagenDoc3,T.PN_RUT
 FROM TercCliente  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 Estratos AS ETC ON C.IdEstrato=ETC.IdEstrato
 INNER JOIN Subzonas AS SZ ON C.IdSzona=SZ.IdSzona  INNER JOIN Zonas AS Z ON SZ.IdZona=Z.IdZona 
 INNER JOIN Plazos AS PZ ON C.IdPlazo=PZ.IdPlazo INNER JOIN Rutas AS R ON C.IdRuta=R.IdRuta
 INNER JOIN Formaspago AS F ON C.IdForma=F.IdForma
 INNER JOIN ClaseCta AS CTA ON  C.IdClase=CTA.IdClase
 INNER JOIN Bancos AS B ON C.IdBanco=B.IdBanco 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.IdLocEnv=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
 INNER JOIN EstadoTer AS ET ON T.IdEstado=ET.IdEstado INNER JOIN Localidades AS LC ON T.IdLugarCed=LC.IdLocal 
 INNER JOIN Profesiones AS PF ON T.IdProf=PF.IdProf LEFT JOIN TiposBan TB ON C.CdBandera=TB.IdBandera
 WHERE T.IdLocal LIKE ISNULL(@pmIdLocal,'%') AND T.IdSector LIKE ISNULL(@pmIdSector,'%')  AND T.IdRegimen LIKE ISNULL(@pmIdRegimen,'%')
 AND T.TipoId LIKE ISNULL(@pmTipoId,'%') AND T.IdProf LIKE ISNULL(@pmIdProf,'%') AND C.IdGrupo LIKE ISNULL(@pmIdGrupo,'%')
 AND IdLocEnv LIKE ISNULL(@pmIdLocEnv,'%') AND C.IdForma LIKE ISNULL(@pmIdForma,'%') AND C.IdBanco LIKE ISNULL(@pmIdBanco,'%')
 AND SZ.IdZona LIKE ISNULL(@pmIdZona,'%') AND C.IdSzona LIKE ISNULL(@pmIdSzona,'%')
 AND C.IdPlazo LIKE ISNULL(@pmIdPlazo,'%') AND C.IdEstrato LIKE ISNULL(@pmIdEstrato,'%')
 AND IdVend LIKE ISNULL(@pmIdVend,'%') AND C.IdRuta LIKE ISNULL(@pmIdRuta,'%')
 AND C.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND T.IdEstado LIKE ISNULL(@pmIdEstadoTer,'%')
 AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
 AND (T.Inactivo=ISNULL(@pmInactivoTer,0) or T.Inactivo=ISNULL(@pmInactivoTer,1))
 AND (FecIngreso>=ISNULL(@pmFecIngIni,CAST('19100101' AS SMALLDATETIME)) AND FecIngreso<=ISNULL(@pmFecIngFin,CAST('20781230' AS SMALLDATETIME)))
 ORDER BY T.RazonSocial
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTercerosDso] @pmIdEstado VARCHAR(4)=Null
 AS 
 SELECT IdTercero,Dv,TipoId,RazonSocial,T.Codigo AS Codtercero,NomCial,SiglaRaz,T.Direccion AS Dreccion,T.IdLocal AS CodCiud,L.Localidad AS Ciudad,D.Departamento AS Departmto
,Telefono,Fax,TelMovil,SitioWeb,e_mail,EsCliente,EsVendedor,EsConductor,EsPropietario,EsProveedor,EsEmpleado,EsOperario,EsAccnista,EsCiaAseg,EsCliePres,TipEnte,RegimenFE
,T.IdSector AS CodSector,SectorEco,T.IdProf AS CodProf,Profesion,T.IdRegimen AS IdRegmen,Regimen,IdLugarCed,LC.Localidad AS LugarCedula,DC.Departamento AS DptoLugcedula,FecExpCed
,Observacion,T.IdEstado AS IdEstdo,ET.Estado,T.Inactivo AS Inactvo,FechaAdd,FechaUpdate,T.IdUsuario AS IdUsario,Usuario,IniStgNom,ET.NColor,ImgFoto,ImgFirma,ImagenDoc1,ImagenDoc2,ImagenDoc3,FechaNac
,T.IdEstOper AS CdEstOper,EO.Estado AS EstOper,IdLocUbic,DescUbicac,CdGrupoTerc,PN_RUT
 FROM Terceros AS T 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 R ON T.IdRegimen=R.IdRegimen
 INNER JOIN EstadoTer AS ET ON T.IdEstado=ET.IdEstado
 INNER JOIN adm_Usuarios AS U ON T.IdUsuario=U.IdUsuario
 INNER JOIN Localidades AS LC ON T.IdLugarCed=LC.IdLocal INNER JOIN Departamentos AS DC ON LC.IdDep=DC.IdDep
 INNER JOIN Profesiones AS PF ON T.IdProf=PF.IdProf LEFT JOIN EstadoOper AS EO ON T.IdEstOper=EO.IdEstado
 WHERE (@pmIdEstado IS NULL OR T.IdEstado=@pmIdEstado)
ORDER BY RazonSocial
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTercerosCta] @pmIdEstado VARCHAR(4)=Null
 AS 
 SELECT T.IdTercero,Dv,TipoId,RazonSocial,T.Codigo AS Codtercero,NomCial,SiglaRaz,T.Direccion AS Dreccion,T.IdLocal AS CodCiud,L.Localidad AS Ciudad,D.Departamento AS Departmto
,Telefono,Fax,TelMovil,SitioWeb,e_mail,EsCliente,EsVendedor,EsConductor,EsPropietario,EsProveedor,EsEmpleado,EsOperario,EsAccnista,EsCiaAseg,EsCliePres,TipEnte
,T.IdSector AS CodSector,SectorEco,T.IdProf AS CodProf,Profesion,T.IdRegimen AS IdRegmen,Regimen,IdLugarCed,LC.Localidad AS LugarCedula,DC.Departamento AS DptoLugcedula,FecExpCed
,Observacion,T.IdEstado AS IdEstdo,Estado,T.Inactivo AS Inactvo,PN_RUT,T.FechaAdd,T.FechaUpdate,T.IdUsuario AS IdUsario,Usuario,IniStgNom,NColor,ImgFoto,ImgFirma,ImagenDoc1,ImagenDoc2,ImagenDoc3,FechaNac
,Item,TC.IdClase AS CdClase,ClaseCuenta,NumCuenta,TC.IdBanco AS CdBanco,Banco,CdLugarCue,CC.Localidad AS LugarCuenta
 FROM Terceros AS T 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 R ON T.IdRegimen=R.IdRegimen
 INNER JOIN EstadoTer AS ET ON T.IdEstado=ET.IdEstado
 INNER JOIN adm_Usuarios AS U ON T.IdUsuario=U.IdUsuario
 INNER JOIN Localidades AS LC ON T.IdLugarCed=LC.IdLocal INNER JOIN Departamentos AS DC ON LC.IdDep=DC.IdDep
 INNER JOIN Profesiones AS PF ON T.IdProf=PF.IdProf 
 LEFT JOIN TercCuentas AS TC ON T.IdTercero=TC.IdTercero
 LEFT JOIN ClaseCta AS CL ON TC.IdClase=CL.IdClase LEFT JOIN Bancos AS B ON TC.IdBanco=B.IdBanco
 LEFT JOIN Localidades AS CC ON TC.CdLugarCue=CC.IdLocal
 WHERE (@pmIdEstado IS NULL OR T.IdEstado=@pmIdEstado)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTercerosCod] @pmIdTercero VARCHAR(16),@pmCodigo VARCHAR(16)
 AS 
 SELECT IdTercero,RazonSocial,Codigo,TipoId,Dv,NomCial,SiglaRaz,Direccion,IdLocal,Telefono,Fax,TelMovil,SitioWeb,e_mail,EsCliente,EsVendedor,EsConductor
,EsPropietario,EsProveedor,EsEmpleado,EsOperario,EsAccnista,EsCiaAseg,EsCliePres,IdSector,IdRegimen,IdLugarCed,FecExpCed,TipEnte,IdProf
,Observacion,IniStgNom,IdEstado,Inactivo,ImgFoto,ImgFirma,ImagenDoc1,ImagenDoc2,ImagenDoc3,FechaNac,IdEstOper,IdLocUbic,DescUbicac,CdGrupoTerc,RegimenFE,PN_RUT,FechaAdd,FechaUpdate,IdUsuario
 FROM Terceros
 WHERE IdTercero=@pmIdTercero OR Codigo=@pmCodigo
 ORDER BY  IdTercero
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsTerceros_Sel] @pmIdTercero VARCHAR(16),@pmNewTercero VARCHAR(16),@pmNewCodigo VARCHAR(16)
 AS 
 INSERT INTO Terceros (IdTercero,RazonSocial,Codigo,TipoId,Dv,Direccion,IdLocal,Telefono,Fax,TelMovil,SitioWeb,e_mail,EsCliente,EsVendedor,EsConductor,EsPropietario,EsProveedor,EsEmpleado,EsOperario,EsAccnista,EsCiaAseg,EsCliePres,IdSector,IdRegimen
,IdLugarCed,Observacion,IniStgNom,IdEstado,Inactivo,FechaAdd,IdUsuario,NomCial,SiglaRaz,IdProf,TipEnte,FecExpCed,ImgFoto,ImgFirma,ImagenDoc1,ImagenDoc2,ImagenDoc3,FechaNac,IdEstOper,IdLocUbic,DescUbicac,CdGrupoTerc,RegimenFE,PN_RUT)
 SELECT @pmNewTercero,RazonSocial,@pmNewCodigo,TipoId,Dv,Direccion,IdLocal,Telefono,Fax,TelMovil,SitioWeb,e_mail,EsCliente,EsVendedor,EsConductor,EsPropietario,EsProveedor,EsEmpleado,EsOperario,EsAccnista,EsCiaAseg,EsCliePres,IdSector,IdRegimen
,IdLugarCed,Observacion,IniStgNom,IdEstado,Inactivo,FechaAdd,IdUsuario,NomCial,SiglaRaz,IdProf,TipEnte,FecExpCed,ImgFoto,ImgFirma,ImagenDoc1,ImagenDoc2,ImagenDoc3,FechaNac,IdEstOper,IdLocUbic,DescUbicac,CdGrupoTerc,RegimenFE,PN_RUT
 FROM  Terceros 
 WHERE IdTercero=@pmIdTercero
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTerceros] @pmIdTercero VARCHAR(16),@pmRazonSocial VARCHAR(250),@pmCodigo VARCHAR(16),@pmTipoId CHAR(1),@pmDv CHAR(1),@pmDireccion VARCHAR(250),@pmIdLocal VARCHAR(8)
,@pmTelefono VARCHAR(20),@pmFax VARCHAR(20),@pmTelMovil VARCHAR(20),@pmSitioWeb VARCHAR(100),@pme_mail VARCHAR(100),@pmEsCliente BIT,@pmEsVendedor BIT,@pmEsConductor BIT
,@pmEsPropietario BIT,@pmEsProveedor BIT,@pmEsEmpleado BIT,@pmEsOperario BIT,@pmEsAccnista BIT,@pmEsCiaAseg BIT,@pmIdSector VARCHAR(8),@pmIdRegimen VARCHAR(4),@pmIdLugarCed VARCHAR(8),@pmObservacion VARCHAR(250)
,@pmIniStgNom INT,@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmNomCial VARCHAR(250),@pmSiglaRaz VARCHAR(50),@pmIdProf VARCHAR(4),@pmTipEnte CHAR(1),@pmFecExpCed SMALLDATETIME,@pmEsCliePres BIT,@pmImgFoto VARCHAR(30),@pmImgFirma VARCHAR(30)
,@pmImagenDoc1 VARCHAR(50),@pmImagenDoc2 VARCHAR(50),@pmImagenDoc3 VARCHAR(50),@pmFechaNac SMALLDATETIME,@pmIdEstOper VARCHAR(4),@pmIdLocUbic VARCHAR(8),@pmDescUbicac VARCHAR(150),@pmCdGrupoTerc VARCHAR(4),@pmRegimenFE BIT,@pmPN_RUT BIT,@pmFechaAdd SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Terceros (IdTercero,RazonSocial,Codigo,TipoId,Dv,Direccion,IdLocal,Telefono,Fax,TelMovil,SitioWeb,e_mail,EsCliente,EsVendedor,EsConductor,EsPropietario,EsProveedor,EsEmpleado,EsOperario,EsAccnista,EsCiaAseg,EsCliePres,IdSector,IdRegimen
,IdLugarCed,Observacion,IniStgNom,IdEstado,Inactivo,FechaAdd,IdUsuario,NomCial,SiglaRaz,IdProf,TipEnte,FecExpCed,ImgFoto,ImgFirma,ImagenDoc1,ImagenDoc2,ImagenDoc3,FechaNac,IdEstOper,IdLocUbic,DescUbicac,CdGrupoTerc,RegimenFE,PN_RUT)
 VALUES (@pmIdTercero,@pmRazonSocial,@pmCodigo,@pmTipoId,@pmDv,@pmDireccion,@pmIdLocal,@pmTelefono,@pmFax,@pmTelMovil,@pmSitioWeb,@pme_mail,@pmEsCliente,@pmEsVendedor,@pmEsConductor,@pmEsPropietario,@pmEsProveedor
,@pmEsEmpleado,@pmEsOperario,@pmEsAccnista,@pmEsCiaAseg,@pmEsCliePres,@pmIdSector,@pmIdRegimen,@pmIdLugarCed,@pmObservacion,@pmIniStgNom,@pmIdEstado,@pmInactivo,@pmFechaAdd,@pmIdUsuario
,@pmNomCial,@pmSiglaRaz,@pmIdProf,@pmTipEnte,@pmFecExpCed,@pmImgFoto,@pmImgFirma,@pmImagenDoc1,@pmImagenDoc2,@pmImagenDoc3,@pmFechaNac,@pmIdEstOper,@pmIdLocUbic,@pmDescUbicac,@pmCdGrupoTerc,@pmRegimenFE,@pmPN_RUT)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTerceros] @pmIdTercero VARCHAR(16),@pmRazonSocial VARCHAR(250),@pmCodigo VARCHAR(16),@pmTipoId CHAR(1),@pmDv CHAR(1),@pmDireccion VARCHAR(250),@pmIdLocal VARCHAR(8)
,@pmTelefono VARCHAR(20),@pmFax VARCHAR(20),@pmTelMovil VARCHAR(20),@pmSitioWeb VARCHAR(100),@pme_mail VARCHAR(100),@pmEsCliente BIT,@pmEsVendedor BIT,@pmEsConductor BIT
,@pmEsPropietario BIT,@pmEsProveedor BIT,@pmEsEmpleado BIT,@pmEsOperario BIT,@pmEsAccnista BIT,@pmEsCiaAseg BIT,@pmIdSector VARCHAR(8),@pmIdRegimen VARCHAR(4),@pmIdLugarCed VARCHAR(8),@pmObservacion VARCHAR(250)
,@pmIniStgNom INT,@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmNomCial VARCHAR(250),@pmSiglaRaz VARCHAR(50),@pmIdProf VARCHAR(4),@pmTipEnte CHAR(1),@pmFecExpCed SMALLDATETIME,@pmEsCliePres BIT,@pmImgFoto VARCHAR(30),@pmImgFirma VARCHAR(30)
,@pmImagenDoc1 VARCHAR(50),@pmImagenDoc2 VARCHAR(50),@pmImagenDoc3 VARCHAR(50),@pmFechaNac SMALLDATETIME,@pmIdEstOper VARCHAR(4),@pmIdLocUbic VARCHAR(8),@pmDescUbicac VARCHAR(150),@pmCdGrupoTerc VARCHAR(4),@pmRegimenFE BIT,@pmPN_RUT BIT,@pmFechaUpdate SMALLDATETIME
 AS 
 UPDATE Terceros SET RazonSocial=@pmRazonSocial,Codigo=@pmCodigo,TipoId=@pmTipoId,Dv=@pmDv,Direccion=@pmDireccion,IdLocal=@pmIdLocal,Telefono=@pmTelefono,Fax=@pmFax,TelMovil=@pmTelMovil,SitioWeb=@pmSitioWeb,e_mail=@pme_mail
,EsCliente=@pmEsCliente,EsVendedor=@pmEsVendedor,EsConductor=@pmEsConductor,EsPropietario=@pmEsPropietario,EsProveedor=@pmEsProveedor,EsEmpleado=@pmEsEmpleado,EsOperario=@pmEsOperario,EsAccnista=@pmEsAccnista,EsCiaAseg=@pmEsCiaAseg 
,EsCliePres=@pmEsCliePres,IdSector=@pmIdSector,IdRegimen=@pmIdRegimen,IdLugarCed=@pmIdLugarCed,Observacion=@pmObservacion,IniStgNom=@pmIniStgNom,IdEstado=@pmIdEstado,Inactivo=@pmInactivo,FechaUpdate=@pmFechaUpdate
,NomCial=@pmNomCial,SiglaRaz=@pmSiglaRaz,IdProf=@pmIdProf,TipEnte=@pmTipEnte,FecExpCed=@pmFecExpCed,ImgFoto=@pmImgFoto,ImgFirma=@pmImgFirma,ImagenDoc1=@pmImagenDoc1,ImagenDoc2=@pmImagenDoc2,ImagenDoc3=@pmImagenDoc3,FechaNac=@pmFechaNac
,IdEstOper=@pmIdEstOper,IdLocUbic=@pmIdLocUbic,DescUbicac=@pmDescUbicac,CdGrupoTerc=@pmCdGrupoTerc,RegimenFE=@pmRegimenFE,PN_RUT=@pmPN_RUT
 WHERE IdTercero=@pmIdTercero
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTerceros] @pmIdTercero VARCHAR(16)
 AS 
 SELECT IdTercero,RazonSocial,Codigo,TipoId,Dv,NomCial,SiglaRaz,Direccion,IdLocal,Telefono,Fax,TelMovil,SitioWeb,e_mail,EsCliente,EsVendedor,EsConductor
,EsPropietario,EsProveedor,EsEmpleado,EsOperario,EsAccnista,EsCiaAseg,EsCliePres,IdSector,IdProf,IdRegimen,TipEnte,IdLugarCed,FecExpCed
,Observacion,IniStgNom,IdEstado,Inactivo,ImgFoto,ImgFirma,ImagenDoc1,ImagenDoc2,ImagenDoc3,FechaNac,IdEstOper,IdLocUbic,DescUbicac,CdGrupoTerc,RegimenFE,PN_RUT,FechaAdd,FechaUpdate,IdUsuario
 FROM Terceros
 WHERE IdTercero=@pmIdTercero
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTercerosBus] @pmCodigo VARCHAR(16)=Null,@pmTelefono VARCHAR(20)=Null
 AS 
--consultar por codigo o por telefono
 SELECT IdTercero,RazonSocial,Codigo,TipoId,Dv,NomCial,SiglaRaz,Direccion,IdLocal,Telefono,Fax,TelMovil,SitioWeb,e_mail,EsCliente,EsVendedor,EsConductor
,EsPropietario,EsProveedor,EsEmpleado,EsOperario,EsAccnista,EsCiaAseg,EsCliePres,IdProf,IdSector,IdRegimen,IdLugarCed,TipEnte,FecExpCed
,Observacion,IniStgNom,ImgFoto,ImgFirma,ImagenDoc1,ImagenDoc2,ImagenDoc3,FechaNac,IdEstOper,IdLocUbic,DescUbicac,CdGrupoTerc,RegimenFE,PN_RUT
,IdEstado,Inactivo,FechaAdd,FechaUpdate,IdUsuario
 FROM Terceros
 WHERE Codigo  LIKE ISNULL(@pmCodigo,'%') AND Telefono LIKE ISNULL(@pmTelefono,'%')
 ORDER BY RazonSocial
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryDocSopDevLta] @pmTipDev VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT DV.TipDev,DV.Devolucion,DV.IdCia,CI.Compania,DV.Fecha,DV.TipDoc,DV.NumDoc,DV.IdCiaDoc,DV.FecDoc,DV.IdTercero,T.RazonSocial,DV.SubTotal,DV.Descuento,DV.Retencion,DV.ReteIca,DV.Bomberil,DV.ImpAviTab
,DV.OtrosCargos,DV.OtrosDctos,DV.ValorTotal,DV.BaseRet,DV.BaseBom,DS.NumFactura,DS.Modalidad,DV.IdConcepto,Concepto,DV.CodRes,DV.DescServicio,DV.CdCCosto,CCosto,DV.CdSubCos,SubCosto
,DV.TipDocRef,DV.NumDocRef,DV.IdCiaRef,DV.NitCueProv,NP.RazonSocial AS NomProveedor,DV.CodCueProv,DV.TipCom,DV.Comprobante,DV.IdCiaCom,DV.Observacion,DV.OrigenAdd AS OrigenCrea,DV.TimeSys AS FechaCrea,DV.IdCiaCrea,DV.IdUsuario,Usuario
 FROM Trn_DocSopDev AS DV INNER JOIN Companias AS CI ON DV.IdCia=CI.IdCia INNER JOIN Terceros AS T ON DV.IdTercero=T.IdTercero
 INNER JOIN adm_Usuarios AS U ON DV.IdUsuario=U.IdUsuario
 INNER JOIN Trn_DocSoporte AS DS ON DV.TipDoc=DS.TipDoc AND DV.NumDoc=DS.NumDoc AND DV.IdCiaDoc=DS.IdCia
 LEFT JOIN CentroCosto AS CC ON DV.CdCCosto=CC.IdCCosto LEFT JOIN SubCentros AS SC ON DV.CdSubCos=SC.IdSubCos 
 LEFT JOIN Terceros AS NP ON DV.NitCueProv=NP.IdTercero LEFT JOIN Conceptos AS CN ON DV.IdConcepto=CN.IdConcepto
 WHERE DV.TipDev=@pmTipDev AND DV.Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 AND (DV.IdCia=@pmIdCia OR @pmIdCia IS NULL)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryDocSopDevFmt] @pmTipDev VARCHAR(3),@pmDevolucionIni INT,@pmDevolucionFin INT,@pmIdCia CHAR(2)
AS 
 SELECT DV.TipDev,TD.TipoDoc,DV.Devolucion,DV.IdCia,CI.Compania,DV.Fecha,DV.TipDoc,TS.TipoDoc AS DescTipDoc,DV.NumDoc,DV.IdCiaDoc,CD.Compania AS CiaDoc,DV.FecDoc,DV.IdTercero,T.RazonSocial,DV.SubTotal,DV.Descuento,DV.Retencion,DV.ReteIca,DV.Bomberil,DV.ImpAviTab
,DV.OtrosCargos,DV.OtrosDctos,DV.ValorTotal,DV.BaseRet,DV.BaseBom,DS.TarifaRet,DS.TarifaIca,DS.TarifaBom,DS.TarifaAvta,DS.CodTarRet,DS.CodTarIca,DS.CodTarifBom,DS.CodTarifAvta
,DS.IdMedPago,MedioPago,DS.MetPago,DS.NumFactura,DS.FechaFac,DS.FechaVence,DS.IdAdquiriente,N.RazonSocial AS Adquiriente,DV.IdConcepto,Concepto,DV.DescServicio,DV.CdCCosto,CCosto,DV.CdSubCos,SubCosto
,DV.TipDocRef,DV.NumDocRef,DV.IdCiaRef,DV.NitCueProv,NP.RazonSocial AS NomProveedor,DV.CodCueProv,DV.TipCom,TipoCom,DV.Comprobante,DV.IdCiaCom,DV.Observacion,DV.OrigenAdd AS OrigenCrea,DV.TimeSys AS FechaCrea,DV.IdCiaCrea,DV.IdUsuario,Usuario
--detalles
,DT.Item,DT.IdProducto,P.DescripProd,DT.Descripcion,DT.Cantidad,DT.VrUnitario,DT.ValorOper,DT.VrDcto,DT.VrRetencion,DT.VrRetIca,DT.VrBomberil,DT.VrAviTab,DT.TarifaDct AS PorcDcto
,DT.TarifaRet AS PorcRet,DT.TarifaIca AS PorcICA,DT.TarifaBom AS PorcBom,DT.TarifaAvta AS PorcAvta,DT.CodTarDct AS DetCodDcto,DT.CodTarRet AS DetCodRet
,DT.CodTarIca AS DetCodIca,DT.CodTarifBom AS DetCodBom,DT.CodTarifAvta AS DetCodAvta,DT.ItemCom,DT.CdCuenta,PC.NomCuenta,DT.CdCueCre,DT.PlacaVeh,DT.Veh_Propio,DT.NitCliente,DT.CodAgencia
--Datos resolución
,DV.CodRes,R.Resolucion,R.Prefijo,R.NumInicial,R.NumFinal,R.FechaExpRes,R.FechaVigencia,R.Establecimiento
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS TercCiudad
,L.IdDep AS CdDep,DP.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.e_mail AS TercEmail,T.SitioWeb AS TercSitioWeb,T.IdSector AS CdSector,SectorEco
,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
,FC.EstadoFE,FC.Prefijo AS FEPrefijo,FC.NumFace,FC.CUFE,FC.CUFE_QR,FC.Resolucion AS FENumResol,FC.RangoNum AS FERangoNum,FC.FecVigencia AS FEVigencia,FC.FechaValidacion AS FEFecValida
FROM Trn_DocSopDev AS DV INNER JOIN Sys_TiposDoc AS TD ON DV.TipDev=TD.IdDoc INNER JOIN Companias AS CI ON DV.IdCia=CI.IdCia
INNER JOIN Sys_TiposDoc AS TS ON DV.TipDoc=TS.IdDoc INNER JOIN Companias AS CD ON DV.IdCiaDoc=CD.IdCia
INNER JOIN Terceros AS T ON DV.IdTercero=T.IdTercero INNER JOIN adm_Usuarios AS U ON DV.IdUsuario=U.IdUsuario
INNER JOIN Trn_DocSoporte AS DS ON DV.TipDoc=DS.TipDoc AND DV.NumDoc=DS.NumDoc AND DV.IdCiaDoc=DS.IdCia
INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep 
INNER JOIN RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
INNER JOIN Trn_DocSopDevDet AS DT ON DV.TipDev=DT.TipDev AND DV.Devolucion=DT.Devolucion AND DV.IdCia=DT.IdCia
LEFT JOIN ProdMcias AS P ON DT.IdProducto=P.IdProducto
LEFT JOIN Conceptos AS CN ON DV.IdConcepto=CN.IdConcepto LEFT JOIN NomMediosPago AS MP ON DS.IdMedPago=MP.IdMedPago
LEFT JOIN TiposComRes AS R ON DV.CodRes=R.IdRes LEFT JOIN Terceros AS N ON DS.IdAdquiriente=N.IdTercero
LEFT JOIN TiposCom AS TC ON DV.TipCom=TC.IdCom LEFT JOIN CentroCosto AS CC ON DV.CdCCosto=CC.IdCCosto
LEFT JOIN SubCentros AS SC ON DV.CdSubCos=SC.IdSubCos LEFT JOIN Puc AS PC ON DT.CdCuenta=PC.IdCuenta
LEFT JOIN Terceros AS NP ON DV.NitCueProv=NP.IdTercero
LEFT JOIN Trn_Face AS FC ON DV.TipDev=FC.TipDoc AND DV.Devolucion=FC.Documento AND DV.IdCia=FC.IdCia
WHERE DV.TipDev=@pmTipDev AND DV.Devolucion BETWEEN @pmDevolucionIni AND @pmDevolucionFin AND DV.IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsDocSopDev] @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmTipDoc VARCHAR(3),@pmNumDoc INT,@pmIdCiaDoc CHAR(2),@pmFecDoc SMALLDATETIME,@pmIdTercero VARCHAR(16),@pmSubTotal DECIMAL(16,6),@pmDescuento DECIMAL(16,6),@pmRetencion DECIMAL(16,6),@pmReteIca DECIMAL(16,6),@pmBomberil DECIMAL(16,6),@pmImpAviTab DECIMAL(16,6),@pmOtrosCargos DECIMAL(16,6),@pmOtrosDctos DECIMAL(16,6),@pmValorTotal DECIMAL(16,6)
,@pmBaseRet DECIMAL(16,6),@pmBaseBom DECIMAL(16,6),@pmCodRes VARCHAR(4),@pmDescServicio VARCHAR(500),@pmCdCCosto VARCHAR(16),@pmCdSubCos VARCHAR(16),@pmTipDocRef VARCHAR(3),@pmNumDocRef INT,@pmIdCiaRef CHAR(2),@pmNitCueProv VARCHAR(16),@pmCodCueProv VARCHAR(16),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmModdDev VARCHAR(10),@pmObservacion VARCHAR(500),@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_DocSopDev (TipDev,Devolucion,IdCia,Fecha,IdConcepto,TipDoc,NumDoc,IdCiaDoc,FecDoc,IdTercero,SubTotal,Descuento,Retencion,ReteIca,Bomberil,ImpAviTab,OtrosCargos,OtrosDctos,ValorTotal,BaseRet,BaseBom,CodRes,DescServicio,CdCCosto,CdSubCos,TipDocRef,NumDocRef,IdCiaRef,NitCueProv,CodCueProv,TipCom,Comprobante,IdCiaCom,ModdDev,Observacion,OrigenAdd,TimeSys,IdCiaCrea,IdUsuario)
 VALUES (@pmTipDev,@pmDevolucion,@pmIdCia,@pmFecha,@pmIdConcepto,@pmTipDoc,@pmNumDoc,@pmIdCiaDoc,@pmFecDoc,@pmIdTercero,@pmSubTotal,@pmDescuento,@pmRetencion,@pmReteIca,@pmBomberil,@pmImpAviTab,@pmOtrosCargos,@pmOtrosDctos,@pmValorTotal,@pmBaseRet,@pmBaseBom,@pmCodRes,@pmDescServicio,@pmCdCCosto,@pmCdSubCos,@pmTipDocRef,@pmNumDocRef,@pmIdCiaRef,@pmNitCueProv,@pmCodCueProv,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmModdDev,@pmObservacion,@pmOrigenAdd,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpDocSopDev] @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmTipDoc VARCHAR(3),@pmNumDoc INT,@pmIdCiaDoc CHAR(2),@pmFecDoc SMALLDATETIME,@pmIdTercero VARCHAR(16),@pmSubTotal DECIMAL(16,6),@pmDescuento DECIMAL(16,6),@pmRetencion DECIMAL(16,6),@pmReteIca DECIMAL(16,6),@pmBomberil DECIMAL(16,6),@pmImpAviTab DECIMAL(16,6),@pmOtrosCargos DECIMAL(16,6),@pmOtrosDctos DECIMAL(16,6),@pmValorTotal DECIMAL(16,6)
,@pmBaseRet DECIMAL(16,6),@pmBaseBom DECIMAL(16,6),@pmCodRes VARCHAR(4),@pmDescServicio VARCHAR(500),@pmCdCCosto VARCHAR(16),@pmCdSubCos VARCHAR(16),@pmTipDocRef VARCHAR(3),@pmNumDocRef INT,@pmIdCiaRef CHAR(2),@pmNitCueProv VARCHAR(16),@pmCodCueProv VARCHAR(16),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmModdDev VARCHAR(10),@pmObservacion VARCHAR(500)
 AS 
 UPDATE Trn_DocSopDev SET Fecha=@pmFecha,IdConcepto=@pmIdConcepto,TipDoc=@pmTipDoc,NumDoc=@pmNumDoc,IdCiaDoc=@pmIdCiaDoc,FecDoc=@pmFecDoc,IdTercero=@pmIdTercero,SubTotal=@pmSubTotal,Descuento=@pmDescuento,Retencion=@pmRetencion,ReteIca=@pmReteIca,Bomberil=@pmBomberil,ImpAviTab=@pmImpAviTab,OtrosCargos=@pmOtrosCargos,OtrosDctos=@pmOtrosDctos,ValorTotal=@pmValorTotal,BaseRet=@pmBaseRet,BaseBom=@pmBaseBom,CodRes=@pmCodRes,DescServicio=@pmDescServicio,CdCCosto=@pmCdCCosto,CdSubCos=@pmCdSubCos
,TipDocRef=@pmTipDocRef,NumDocRef=@pmNumDocRef,IdCiaRef=@pmIdCiaRef,NitCueProv=@pmNitCueProv,CodCueProv=@pmCodCueProv,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,ModdDev=@pmModdDev,Observacion=@pmObservacion
 WHERE TipDev=@pmTipDev AND Devolucion=@pmDevolucion AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryDocSopDev] @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDev,Devolucion,IdCia,Fecha,IdConcepto,TipDoc,NumDoc,IdCiaDoc,FecDoc,IdTercero,SubTotal,Descuento,Retencion,ReteIca,Bomberil,ImpAviTab,OtrosCargos,OtrosDctos,ValorTotal,BaseRet,BaseBom,CodRes,DescServicio,CdCCosto,CdSubCos,TipDocRef,NumDocRef,IdCiaRef,NitCueProv,CodCueProv,TipCom,Comprobante,IdCiaCom,ModdDev,Observacion,OrigenAdd,TimeSys,IdCiaCrea,IdUsuario
 FROM Trn_DocSopDev
 WHERE TipDev=@pmTipDev AND Devolucion=@pmDevolucion AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paDelDocSopDev] @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2)
 AS 
 DELETE FROM Trn_DocSopDev
 WHERE TipDev=@pmTipDev AND Devolucion=@pmDevolucion AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsDocSopDevDet] @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2),@pmItem INT,@pmIdProducto VARCHAR(16),@pmDescripcion VARCHAR(1000),@pmCantidad DECIMAL(16,6),@pmVrUnitario DECIMAL(16,6),@pmValorOper DECIMAL(16,6),@pmVrDcto DECIMAL(16,6),@pmVrRetencion DECIMAL(16,6),@pmVrRetIca DECIMAL(16,6),@pmVrBomberil DECIMAL(16,6),@pmVrAviTab DECIMAL(16,6),@pmTarifaDct DECIMAL(14,4),@pmTarifaRet DECIMAL(14,4),@pmTarifaIca DECIMAL(14,4),@pmTarifaBom DECIMAL(14,4),@pmTarifaAvta DECIMAL(14,4),@pmCodTarDct VARCHAR(4)
,@pmCodTarRet VARCHAR(4),@pmCodTarIca VARCHAR(4),@pmCodTarifBom VARCHAR(4),@pmCodTarifAvta VARCHAR(4),@pmItemCom INT,@pmCdCuenta VARCHAR(16),@pmCdCueCre VARCHAR(16),@pmCodCC VARCHAR(16),@pmCodSubCc VARCHAR(16),@pmPlacaVeh VARCHAR(10),@pmVeh_Propio INT,@pmNitCliente VARCHAR(16),@pmCodAgencia VARCHAR(16),@pmDetalle VARCHAR(500),@pmNitTercero VARCHAR(16)
 AS 
 INSERT INTO Trn_DocSopDevDet (TipDev,Devolucion,IdCia,Item,IdProducto,Descripcion,Cantidad,VrUnitario,ValorOper,VrDcto,VrRetencion,VrRetIca,VrBomberil,VrAviTab,TarifaDct,TarifaRet,TarifaIca,TarifaBom,TarifaAvta,CodTarDct,CodTarRet,CodTarIca,CodTarifBom,CodTarifAvta,ItemCom,CdCuenta,CdCueCre,CodCC,CodSubCc,PlacaVeh,Veh_Propio,NitCliente,CodAgencia,Detalle,NitTercero)
 VALUES (@pmTipDev,@pmDevolucion,@pmIdCia,@pmItem,@pmIdProducto,@pmDescripcion,@pmCantidad,@pmVrUnitario,@pmValorOper,@pmVrDcto,@pmVrRetencion,@pmVrRetIca,@pmVrBomberil,@pmVrAviTab,@pmTarifaDct,@pmTarifaRet,@pmTarifaIca,@pmTarifaBom,@pmTarifaAvta,@pmCodTarDct,@pmCodTarRet,@pmCodTarIca,@pmCodTarifBom,@pmCodTarifAvta,@pmItemCom,@pmCdCuenta,@pmCdCueCre,@pmCodCC,@pmCodSubCc,@pmPlacaVeh,@pmVeh_Propio,@pmNitCliente,@pmCodAgencia,@pmDetalle,@pmNitTercero)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenPagoRelDet] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdConductor VARCHAR(16)=Null,@pmIdPoseedor VARCHAR(16)=Null
,@pmOrdPagoIni INT=Null,@pmOrdPagoFin INT=Null
 AS 
 SELECT OP.TipDoc AS TipoOdp,OP.OrdPago AS NumOdp,OP.IdCia AS CdCia,Compania,OP.Fecha AS FechaOdp,OP.IdConcepto AS CdConcepto,Concepto,OP.Modalidad,LiqRemesas,CxPagar,MulPlazos,OP.IdPlazo AS CdPlazo,Plazo,DiasPago,FechaVence
,OP.TipCom AS TipComp,TipoCom,OP.Comprobante AS NumComp,OP.IdCiaCom AS CdCiaComp,OP.Anulado AS Anuldo,NumDev,OP.FecDev AS FechaDev,OP.Observacion AS Observ,OP.IdEstado AS CdEstado,OP.TimeSys AS FechaCrea,OP.FecUpdate AS FechaAct,OP.IdCiaCrea AS CdCiaCrea,OP.IdUsuario AS CdUsuario,Usuario
,O.TipMuc AS TipManif,O.Manifiesto AS NumManif,O.IdCiaMuc AS CdCiaManif,O.IdVehiculo AS PlacaVeh,O.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,O.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario,O.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor
,VrTotalFletes,O.VrDescuento AS Vr_Dcto,O.VrRetencion AS Vr_RetFte,O.VrReteIca AS Vr_RetIca,VrAnticipos,VrFaltantes,VrSeguros,VrFondos,VrAportes,VrOtrosDctos,VrImpuestos,VrEstampilla,VrOtrosPagos,O.VrNeto AS Vr_Neto
,TarifaTabla,O.TarifaFlete AS Tarifa_Flete,UnidTarifa,O.PesoTotal AS Peso_Total,Unidades,O.Volumen AS VolmTotal,PesoOrigen,UnidOrigen,VolOrigen,PesoDestino,UnidDestino,VolDestino,O.BaseRet,O.BaseIca,BaseSeg,BaseImp,O.TarifaRet,O.TarifaIca,TarifaSeg,TarifaImp
,TipoLiq,TipEgr,Egreso,IdCiaEgr,FechaEgr,O.NumCheque,O.Referencia,CantFaltante,UnidadFalt,TolFaltNeto,VrConcPagos,VrConcDctos,VrConcFondo,VrConcSeguro,VrConcAporte,VrConcImpuesto,VrRemesas,VrSeguroRc,VrRecCaja,VrReteCREE,TarifaRtc,CodTarRtc,VrImpAvTa,CodTarifAvta,TarifaAvta,EdoLiqCausac
,M.Fecha AS FecManif,M.FecDespacho,M.FecEntrega,M.IdOrigen AS CdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,M.IdDestino AS CdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,M.IdRuta AS CdRuta,R.Ruta AS DescRuta,M.nRemolque,M.TipoAfiVehic
,M.IdLocFletes,CF.Localidad AS LugarFletes,M.Observacion AS MucObserv
--detalles
,ORM.TipRem,ORM.Remesa AS NumRemesa,ORM.IdCiaRem AS CdCiaRem,ORM.ItemRem,ORM.Cantidad AS Cant,ORM.PesoNeto,ORM.UndMed AS CdUMed,UMP.Unidad AS UmPeso,ORM.Volumen AS Volmen,ORM.UndVol,ORM.Cases,ORM.Cajas,ORM.Palets,ORM.TarifPago,ORM.TarifTabla,ORM.UndTarifa,ORM.Detalle,RD.Referencia1,RD.Referencia2,RD.Referencia3
,RM.IdCliente AS CdCliente,NC.RazonSocial AS NomCliente,RM.IdAgencia AS CdAgencia,A.Agencia
--Datos del vehiculo
,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
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,MV.Marca AS MarcaVeh,V.IdLinea AS CdLinVeh,LineaVeh,V.IdColor AS CdColor,NomColor,V.IdCrceria AS CdCarr,TipoCar,Modelo,Config
,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,NitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion,V.IdGrupo AS CdGrupoPro,GrupoProp
,ISNULL(VC.VrTotalAbo,0) AS CxpAbonado,ISNULL(VC.VrSaldo,0) AS CxpSaldo
 FROM Trn_TraOrdenPago AS OP INNER JOIN Trn_TraOrdenManif AS O ON OP.TipDoc=O.TipDoc AND OP.OrdPago=O.OrdPago AND OP.IdCia=O.IdCia
 INNER JOIN Trn_TraManifiesto AS M ON O.TipMuc=M.TipDoc AND O.Manifiesto=M.Manifiesto AND O.IdCiaMuc=M.IdCia
 INNER JOIN Companias AS CN ON OP.IdCia=CN.IdCia  INNER JOIN adm_Usuarios AS U ON OP.IdUsuario=U.IdUsuario
 INNER JOIN Conceptos AS C ON OP.IdConcepto=C.IdConcepto INNER JOIN Plazos AS PZ ON OP.IdPlazo=PZ.IdPlazo
 INNER JOIN Terceros AS  CDT ON O.IdConductor=CDT.IdTercero INNER JOIN Terceros AS T ON O.IdPoseedor=T.IdTercero
 INNER JOIN Terceros AS NP ON O.IdPropietario=NP.IdTercero  INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN Localidades AS CO ON  M.IdOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON M.IdDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN Rutas AS R ON M.IdRuta=R.IdRuta    INNER JOIN Localidades AS CF ON M.IdLocFletes=CF.IdLocal
 INNER JOIN Vehiculos AS V ON O.IdVehiculo=V.IdVehiculo  INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca INNER JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor  INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria 
 INNER JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd
 LEFT JOIN Terceros AS NE ON V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN TiposCom AS TCM ON OP.TipCom=TCM.IdCom 
 LEFT JOIN Trn_TraOrdenRemesas AS ORM ON O.TipDoc=ORM.TipDoc AND O.OrdPago=ORM.OrdPago AND O.IdCia=ORM.IdCia 
 AND O.TipMuc=ORM.TipMuc AND O.Manifiesto=ORM.Manifiesto AND O.IdCiaMuc=ORM.IdCiaMuc
 LEFT JOIN Sys_Um AS UMP ON ORM.UndMed=UMP.UndMed LEFT JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo
 LEFT JOIN Trn_TraRemesa AS RM ON ORM.TipRem=RM.TipDoc AND ORM.Remesa=RM.NumOrden AND ORM.IdCiaRem=RM.IdCia
 LEFT JOIN Trn_TraRemMcias AS RD ON ORM.TipRem=RD.TipDoc AND ORM.Remesa=RD.NumOrden AND ORM.IdCiaRem=RD.IdCia AND ORM.ItemRem=RD.Item
 LEFT JOIN Terceros AS NC ON RM.IdCliente=NC.IdTercero LEFT JOIN Agencias AS A ON RM.IdAgencia=A.IdAgencia
  --subconsulta abonos cuentas por pagar
 LEFT JOIN (SELECT TipFac,Factura,IdCia,IdProveedor,Documento,SUM(VrFactura) AS VrTotalFac,SUM(VrAbonado) AS VrTotalAbo,SUM(VrFactura-VrAbonado) AS VrSaldo FROM Trn_ComFactura
 WHERE TipFac=@pmTipDoc AND TipDoc=@pmTipDoc AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdProveedor LIKE ISNULL(@pmIdPoseedor,'%')
 GROUP BY TipFac,Factura,IdCia,IdProveedor,Documento) AS VC ON O.TipDoc=VC.TipFac AND O.OrdPago=VC.Documento AND O.IdCia=VC.IdCia AND O.IdPoseedor=VC.IdProveedor
 WHERE OP.TipDoc=@pmTipDoc AND OP.OrdPago BETWEEN ISNULL(@pmOrdPagoIni,0) AND ISNULL(@pmOrdPagoFin,2147483647)
 AND OP.Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
 --Mayo 10/2018 Parametros Obsoletos
 --AND OP.IdCia LIKE ISNULL(@pmIdCia,'%%')
 --AND OP.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND O.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND O.IdConductor LIKE ISNULL(@pmIdConductor,'%')
 --AND O.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')