if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAcuOrdenes_Nit]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsAcuOrdenes_Nit]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAcuOrdenes_Uni]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsAcuOrdenes_Uni]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraOrdenManif]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTraOrdenManif]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEdsRangosFor]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryEdsRangosFor]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNiifCompLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNiifCompLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNiifDetalleLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNiifDetalleLta]
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].[paQrytm_BalPspto]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_BalPspto]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_TraDetConcLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_TraDetConcLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenManif]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenManif]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenPagoLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenPagoLta]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenPagoLta] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdVehiculo VARCHAR(10)=Null,@pmIdPoseedor VARCHAR(16)=Null,@pmIdConductor VARCHAR(16)=Null
 AS 
 SELECT O.OrdPago AS NumOrden,O.IdCia AS CdCia,Compania,Fecha,TipMuc,Manifiesto,IdCiaMuc,M.IdVehiculo AS PlacaVeh,NumVeh,M.IdPropietario AS NitPropietario,T.RazonSocial AS Propietario,M.IdPoseedor AS NitPoseedor,NP.RazonSocial AS Poseedor
,M.IdConductor AS CedConductor,NC.RazonSocial AS Conductor,LiqRemesas,TipoLiq,VrTotalFletes,VrDescuento,VrRetencion,VrReteIca,VrAnticipos,VrFaltantes,VrSeguros,VrFondos
,VrAportes,VrOtrosDctos,VrImpuestos,VrEstampilla,VrOtrosPagos,VrNeto,TarifaTabla,TarifaFlete,UnidTarifa,PesoTotal,Unidades,Volumen,PesoOrigen,UnidOrigen,VolOrigen
,PesoDestino,UnidDestino,VolDestino,BaseRet,BaseIca,BaseSeg,BaseImp,TarifaRet,TarifaIca,TarifaSeg,TarifaImp,CxPagar,MulPlazos,O.IdPlazo AS CdPlazo,Plazo,NVmto,FechaVence
,VrConcPagos,VrConcDctos,VrConcFondo,VrConcSeguro,VrConcAporte,VrConcImpuesto,VrRemesas,VrSeguroRc,VrRecCaja,VrReteCREE,TarifaRtc,CodTarRtc,CdForma,CdCenCosto,CdSubCenCos
,O.IdConcepto AS CdConcepto,Concepto,Modalidad,TipEgr,Egreso,IdCiaEgr,FechaEgr,NumCheque,Referencia,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev
,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,O.IdUsuario AS CdUsuario,Usuario
 FROM Trn_TraOrdenPago AS O INNER JOIN Trn_TraOrdenManif AS M ON O.TipDoc=M.TipDoc AND O.OrdPago=M.OrdPago AND O.IdCia=M.IdCia
 INNER JOIN Companias AS CI ON O.IdCia=CI.IdCia INNER JOIN Conceptos AS C ON O.IdConcepto=C.IdConcepto
 INNER JOIN Vehiculos AS V ON M.IdVehiculo=V.IdVehiculo   INNER JOIN Terceros AS T ON M.IdPropietario=T.IdTercero
 INNER JOIN Terceros AS NC ON M.IdConductor=NC.IdTercero  INNER JOIN Terceros AS NP ON M.IdPoseedor=NP.IdTercero 
 INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario
 LEFT JOIN Plazos AS PZ ON O.IdPlazo=PZ.IdPlazo
 WHERE O.TipDoc=@pmTipDoc AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND O.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND M.IdVehiculo  LIKE ISNULL(@pmIdVehiculo ,'%') AND M.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
 AND  M.IdConductor LIKE ISNULL(@pmIdConductor,'%')
 ORDER BY O.IdCia,O.OrdPago
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_BalPspto] @pmtmEst CHAR(2)
 AS 
 SELECT T.IdCuenta AS CdCuenta,NomCuenta,T.IdCCosto AS CdCcosto,CCosto,T.IdSubCos AS CdSubCentro,SubCosto,T.IdCia AS CdCia,Compania,VrPstoEne,VrSanEne,VrDebEne,VrCreEne,VrPstoFeb,VrSanFeb,VrDebFeb,VrCreFeb,VrPstoMar,VrSanMar,VrDebMar,VrCreMar
,VrPstoAbr,VrSanAbr,VrDebAbr,VrCreAbr,VrPstoMay,VrSanMay,VrDebMay,VrCreMay,VrPstoJun,VrSanJun,VrDebJun,VrCreJun,VrPstoJul,VrSanJul,VrDebJul,VrCreJul,VrPstoAgo,VrSanAgo,VrDebAgo,VrCreAgo,VrPstoSep,VrSanSep,VrDebSep,VrCreSep
,VrPstoOct,VrSanOct,VrDebOct,VrCreOct,VrPstoNov,VrSanNov,VrDebNov,VrCreNov,VrPstoDic,VrSanDic,VrDebDic,VrCreDic,VrPstoAnual
,tmNivel1,tmNivel2,tmNivel4,tmNivel6,tmNivel9,tmNivel12,Naturaleza,CentroCosto,SubCentro
 FROM tm_BalPspto AS T INNER JOIN Puc AS P ON T.IdCuenta=P.IdCuenta
 LEFT JOIN CentroCosto AS CC ON T.IdCCosto=CC.IdCCosto
 LEFT JOIN SubCentros AS SC ON T.IdSubCos=SC.IdSubCos
 LEFT JOIN Companias AS C ON T.IdCia=C.IdCia
 LEFT JOIN tm_NivCue AS NV ON T.IdCuenta=NV.tmIdCuenta
 WHERE T.tmEst=@pmtmEst
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsAcuOrdenes_Nit] @pmIdPoseedor VARCHAR(16),@pmIdPoseedorDos VARCHAR(16),@pmNewIdPoseedor VARCHAR(16)
 AS 
 IF EXISTS (SELECT * FROM AcuOrdenes WHERE IdPoseedor=@pmIdPoseedor OR IdPoseedor=@pmIdPoseedorDos)
  INSERT INTO AcuOrdenes (nAnno,nMes,IdCia,IdPoseedor,IdVehiculo,SaldoAnt,VrIngresos,VrCausac)
  SELECT nAnno,nMes,IdCia,@pmNewIdPoseedor,IdVehiculo,SUM(SaldoAnt),SUM(VrIngresos),SUM(VrCausac)
  FROM AcuOrdenes WHERE IdPoseedor=@pmIdPoseedor OR IdPoseedor=@pmIdPoseedorDos
  GROUP BY nAnno,nMes,IdCia,IdVehiculo
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsAcuOrdenes_Uni] @pmIdVehiculo VARCHAR(10),@pmIdVehiculoDos VARCHAR(10),@pmNewVehiculo VARCHAR(10)
 AS 
 IF EXISTS (SELECT * FROM AcuOrdenes WHERE IdVehiculo=@pmIdVehiculo OR IdVehiculo=@pmIdVehiculoDos)
  INSERT INTO AcuOrdenes (nAnno,nMes,IdCia,IdPoseedor,IdVehiculo,SaldoAnt,VrIngresos,VrCausac)
  SELECT nAnno,nMes,IdCia,IdPoseedor,@pmNewVehiculo,SUM(SaldoAnt),SUM(VrIngresos),SUM(VrCausac)
  FROM AcuOrdenes WHERE IdVehiculo=@pmIdVehiculo OR IdVehiculo=@pmIdVehiculoDos
  GROUP BY nAnno,nMes,IdCia,IdPoseedor
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNiifCompLta] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmTipCom VARCHAR(3)=Null
,@pmIdCia CHAR(2)=Null,@pmTipoNiif VARCHAR(10)=Null
 AS 
 SELECT TipCom,TipoCom,Comprobante,C.IdCia AS CdCia,Compania,Fecha,C.TipoNiif,C.IdTercero AS NitTercero,T.RazonSocial AS NomTercero,VrTotal,C.IdCta AS CodCta,NumeroCta
,CTE.IdBanco AS CodBanco,Banco,EnEfectivo,NumCheque,FecCheque,TipDoc,TipoDoc,Documento,IdCiaDoc,Anulado,NumDev,FecDev,TipEgr,NEgreso,CtbTipo,CtbNumero,FisTipo,FisNumero,pVehiculo,VehPropio
,C.CedCondtor AS NitOtros,CT.RazonSocial AS NomNitOtros,CodConce,Concepto,Beneficiario,Integrado,C.EsEgreso AS EsCompEgreso,Anticipo,C.Observacion AS Observ,C.IdEstado AS CodEstado,Estado,Propddes
,TimeSys,FecUpdate,IdCiaCrea,C.IdUsuario AS IdUsuari,Usuario,CdSustento,Tipo_Pago,Establec,PEmision,Autorizac,FechaAut,VrDivisa1,VrDivisa2,VrDivisa3,TC.IdDiario AS CdDiario,Diario
--información del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion
,T.IdLocal AS TercCdCiudad,Localidad,L.IdDep AS CdDep,Departamento,T.Telefono AS TercTelefono
,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
 FROM Trn_NiifComp AS C INNER JOIN TiposCom AS TC ON C.TipCom=TC.IdCom
 INNER JOIN Diarios AS DR ON TC.IdDiario=DR.IdDiario
 INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia
 INNER JOIN Terceros AS T ON C.IdTercero=T.IdTercero
 INNER JOIN EstadoDoc AS ED ON C.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario
 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
 LEFT JOIN Sys_TiposDoc AS TD ON C.TipDoc=TD.IdDoc
 LEFT JOIN CtasCorrientes AS CTE ON C.IdCta=CTE.IdCta LEFT JOIN Bancos AS B ON CTE.IdBanco=B.IdBanco
 LEFT JOIN Terceros AS CT ON C.CedCondtor=CT.IdTercero
 LEFT JOIN Conceptos AS CN ON C.CodConce=CN.IdConcepto
 WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
 AND (@pmTipCom IS NULL OR TipCom=@pmTipCom)
 AND (@pmIdCia IS NULL OR C.IdCia=@pmIdCia)
 AND (@pmTipoNiif IS NULL OR C.TipoNiif=@pmTipoNiif)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNiifDetalleLta] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmTipCom VARCHAR(3)=Null
,@pmIdCia CHAR(2)=Null,@pmTipoNiif VARCHAR(10)=Null
 AS 
 SELECT C.TipCom AS IdTipoComp,TipoCom,C.Comprobante AS NumComp,C.IdCia AS CdCia,Compania,C.Fecha AS FecComp,C.TipoNiif,C.IdTercero AS Nit,T.RazonSocial AS RaznSocial
,VrTotal,C.IdCta AS CodCta,NumeroCta,CTE.IdBanco AS CodBanco,Banco,EnEfectivo,C.NumCheque AS NroCheque,FecCheque,C.TipDoc AS IdTipoDoc,TipoDoc,C.Documento AS NroDoc,C.IdCiaDoc AS CdCiaDoc
,Anulado,NumDev,FecDev,TipEgr,NEgreso,pVehiculo,C.VehPropio AS EsVehPropio,C.CodConce AS CdConcep,Concepto,Beneficiario,C.Integrado AS EsIntegrado,C.EsEgreso AS EsCompEgreso
,Anticipo,C.Observacion AS Observ,C.IdEstado AS CodEstado,Estado,Propddes,FisTipo,FisNumero,pVehiculo
--detalles de comprobantes
,Item,D.IdCuenta AS CdCuenta,P.NomCuenta,CodCuentaCG,Detalle,VrDebito,VrCredito,D.IdTercero AS NitTercero,DT.RazonSocial AS NomTercero,IdVehiculo
,D.IdCCosto AS CdCentCosto,CCosto,D.IdSubCos AS CdSubCentro,SubCosto,VrBase,TarifaBase
,D.TipDoc AS DetTipDoc,D.Documento AS DetNumDoc,D.IdCiaDoc AS DetCiaDoc,D.CodConce AS DetConcept,TipFac,Factura,IdCiaFac,ItemFac,FecVence,CodCta,D.NumCheque AS DetNumCheque
,TipoAplica,Consolida,CodCargo,NitOtros,NIO.RazonSocial AS NomNitOtros,CodSubgpo,Subgrupo,CiuOrigen,LD.Localidad AS NomCiudad,CodAgncia,Referncia
,NitDoc,ND.RazonSocial AS NomNitDoc,TipDocRef,DocRef,IdCiaRef
,C.TimeSys AS FechaAdd,C.IdCiaCrea AS CdCiaCrea,C.IdUsuario AS IdUsuari,Usuario,VrDivisa1,VrDivisa2,VrDivisa3,TC.IdDiario AS CdDiario,Diario
--información del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion
,T.IdLocal AS TercCdCiudad,L.Localidad AS TercCiudad,L.IdDep AS CdDep,Departamento,T.Telefono AS TercTelefono
,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
 FROM Trn_NiifComp AS C INNER JOIN Trn_NiifDetalle AS D ON C.TipCom=D.TipCom AND C.Comprobante=D.Comprobante AND C.IdCia=D.IdCia
 INNER JOIN TiposCom AS TC ON C.TipCom=TC.IdCom
 INNER JOIN Diarios AS DR ON TC.IdDiario=DR.IdDiario
 INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia
 INNER JOIN Terceros AS T ON C.IdTercero=T.IdTercero
 INNER JOIN EstadoDoc AS ED ON C.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario
 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 PucNiif AS P ON D.IdCuenta=P.IdCuenta
 INNER JOIN Terceros AS DT ON D.IdTercero=DT.IdTercero
 INNER JOIN CentroCosto AS O ON D.IdCCosto=O.IdCCosto
 LEFT JOIN CtasCorrientes AS CTE ON C.IdCta=CTE.IdCta LEFT JOIN Bancos AS B ON CTE.IdBanco=B.IdBanco
 LEFT JOIN Sys_TiposDoc AS TD ON C.TipDoc=TD.IdDoc
 LEFT JOIN SubCentros AS SC ON D.IdSubCos=SC.IdSubCos
 LEFT JOIN Terceros AS NIO ON D.NitOtros=NIO.IdTercero
 LEFT JOIN Terceros AS ND ON D.NitDoc=ND.IdTercero
 LEFT JOIN Conceptos AS CN ON C.CodConce=CN.IdConcepto
 LEFT JOIN Localidades AS LD ON D.CiuOrigen=LD.IdLocal
 LEFT JOIN SubGrupos AS SG ON  D.CodSubgpo=SG.IdSubgrupo
 WHERE C.Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
 AND (@pmTipCom IS NULL OR C.TipCom=@pmTipCom)
 AND (@pmIdCia IS NULL OR C.IdCia=@pmIdCia)
 AND (@pmTipoNiif IS NULL OR C.TipoNiif=@pmTipoNiif)
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,FechaAdd,FechaUpdate,T.IdUsuario AS IdUsario,Usuario,IniStgNom,NColor,ImgFoto,ImgFirma
,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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQrytm_TraDetConcLta] @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmNumero,tmItem,tmIdConcepto,tmDescripcion,tmTipoConc,tmVrDebito,tmVrCredito,tmCdCuenta,NomCuenta,tmNitTercero,RazonSocial
,tmCdCCosto,CCosto,tmCdSubCos,SubCosto,tmpVehiculo,tmTipoAfiVehic,tmVrBase,tmTarifBase,tmReferencia
,tmTipDocRef,tmDocRef,tmTipFac,tmFactura,tmIdCiaFac,tmItemFac,tmFecVence,tmCodAgncia,tmCantComb
 FROM tm_TraDetConc AS T LEFT JOIN CentroCosto AS CC ON T.tmCdCCosto=CC.IdCCosto
 LEFT JOIN SubCentros AS SC ON T.tmCdSubCos=SC.IdSubCos
 LEFT JOIN Terceros  AS N ON T.tmNitTercero=N.IdTercero LEFT JOIN Puc AS P ON T.tmCdCuenta=P.IdCuenta
 WHERE tmNumero=@pmtmNumero
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryEdsRangosFor] @pmTipoRango VARCHAR(3),@pmNumSerie VARCHAR(5)
,@pmNumForma INT
 AS 
 SELECT TipoRango,IdRango,IdForma,Fecha,FecVence,IdCliente,IdAgencia,pVehiculo,nVehiculo,IdCuenta,NumSerie,NumInicial,NumFinal
,NumTarjeta,VrCupo,VrConsumos,TipoConsumo,ReqPlaca,ReqProd,ReqGalones,VldAgencia,VldPlaca
,Modalidad,Anulado,Observacion,IdEstado,NumAuto,NumActual,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_EdsRangos
 WHERE TipoRango=@pmTipoRango AND NumSerie=@pmNumSerie
 AND @pmNumForma BETWEEN NumInicial AND NumFinal AND Anulado=0
 ORDER BY IdRango
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
,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,FechaAdd,FechaUpdate,T.IdUsuario AS IdUsario,Usuario,IniStgNom,NColor,ImgFoto,ImgFirma
 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 
 WHERE (@pmIdEstado IS NULL OR T.IdEstado=@pmIdEstado)
ORDER BY RazonSocial
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenManif] @pmTipDoc VARCHAR(3),@pmOrdPago INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,OrdPago,IdCia,Item,TipMuc,Manifiesto,IdCiaMuc,IdVehiculo,IdConductor,IdPropietario,IdPoseedor,VrTotalFletes,VrDescuento,VrRetencion
,VrReteIca,VrAnticipos,VrFaltantes,VrSeguros,VrFondos,VrAportes,VrOtrosDctos,VrImpuestos,VrEstampilla,VrOtrosPagos,VrNeto,TarifaTabla,TarifaFlete,UnidTarifa,PesoTotal,Unidades
,Volumen,PesoOrigen,UnidOrigen,VolOrigen,PesoDestino,UnidDestino,VolDestino,BaseRet,BaseIca,BaseSeg,BaseImp,TarifaRet,TarifaIca,TarifaSeg,TarifaImp,CodTarRet,CodTarIca
,TipoLiq,TipEgr,Egreso,IdCiaEgr,NumCheque,Referencia,CantFaltante,UnidadFalt,TolFaltNeto,VrConcPagos,VrConcDctos
,VrConcFondo,VrConcSeguro,VrConcAporte,VrConcImpuesto,EdoLiqCausac,VrRemesas,VrRecCaja,CdForma,FechaEgr,VrSeguroRc,VrReteCREE,TarifaRtc,CodTarRtc,CdCenCosto,CdSubCenCos
 FROM Trn_TraOrdenManif
 WHERE TipDoc=@pmTipDoc AND OrdPago=@pmOrdPago AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTraOrdenManif] @pmTipDoc VARCHAR(3),@pmOrdPago INT,@pmIdCia CHAR(2),@pmItem INT,@pmTipMuc VARCHAR(3),@pmManifiesto INT,@pmIdCiaMuc CHAR(2),@pmIdVehiculo VARCHAR(10),@pmIdConductor VARCHAR(16),@pmIdPropietario VARCHAR(16),@pmIdPoseedor VARCHAR(16),@pmVrTotalFletes MONEY,@pmVrDescuento MONEY,@pmVrRetencion MONEY,@pmVrReteIca MONEY
,@pmVrAnticipos MONEY,@pmVrFaltantes MONEY,@pmVrSeguros MONEY,@pmVrFondos MONEY,@pmVrAportes MONEY,@pmVrOtrosDctos MONEY,@pmVrImpuestos MONEY,@pmVrEstampilla MONEY,@pmVrOtrosPagos MONEY,@pmVrNeto MONEY,@pmTarifaTabla MONEY,@pmTarifaFlete MONEY,@pmUnidTarifa VARCHAR(10),@pmPesoTotal DECIMAL(14,4),@pmUnidades DECIMAL(14,4),@pmVolumen DECIMAL(14,4),@pmPesoOrigen DECIMAL(14,4)
,@pmUnidOrigen DECIMAL(14,4),@pmVolOrigen DECIMAL(14,4),@pmPesoDestino  DECIMAL(14,4),@pmUnidDestino DECIMAL(14,4),@pmVolDestino DECIMAL(14,4),@pmBaseRet MONEY,@pmBaseIca MONEY,@pmBaseSeg MONEY,@pmBaseImp MONEY,@pmTarifaRet DECIMAL(14,4),@pmTarifaIca DECIMAL(14,4),@pmTarifaSeg DECIMAL(14,4),@pmTarifaImp DECIMAL(14,4),@pmCodTarRet VARCHAR(4),@pmCodTarIca VARCHAR(4),@pmTipoLiq INT
,@pmTipEgr VARCHAR(3),@pmEgreso INT,@pmIdCiaEgr CHAR(2),@pmNumCheque VARCHAR(20),@pmReferencia VARCHAR(50),@pmCantFaltante DECIMAL(14,4),@pmUnidadFalt VARCHAR(10),@pmTolFaltNeto DECIMAL(14,4),@pmVrConcPagos MONEY,@pmVrConcDctos MONEY,@pmVrConcFondo MONEY,@pmVrConcSeguro MONEY,@pmVrConcAporte MONEY,@pmVrConcImpuesto MONEY
,@pmEdoLiqCausac INT,@pmVrRemesas MONEY,@pmVrRecCaja MONEY,@pmCdForma VARCHAR(4),@pmFechaEgr SMALLDATETIME,@pmVrSeguroRc MONEY,@pmVrReteCREE MONEY,@pmTarifaRtc DECIMAL(14,4),@pmCodTarRtc VARCHAR(4),@pmCdCenCosto VARCHAR(16),@pmCdSubCenCos VARCHAR(16)
 AS 
 INSERT INTO Trn_TraOrdenManif (TipDoc,OrdPago,IdCia,Item,TipMuc,Manifiesto,IdCiaMuc,IdVehiculo,IdConductor,IdPropietario,IdPoseedor,VrTotalFletes,VrDescuento,VrRetencion,VrReteIca,VrAnticipos,VrFaltantes,VrSeguros,VrFondos,VrAportes,VrOtrosDctos,VrImpuestos,VrEstampilla,VrOtrosPagos,VrNeto,TarifaTabla
,TarifaFlete,UnidTarifa,PesoTotal,Unidades,Volumen,PesoOrigen,UnidOrigen,VolOrigen,PesoDestino,UnidDestino,VolDestino,BaseRet,BaseIca,BaseSeg,BaseImp,TarifaRet,TarifaIca,TarifaSeg,TarifaImp,CodTarRet,CodTarIca,TipoLiq,TipEgr,Egreso,IdCiaEgr,NumCheque,Referencia,CantFaltante,UnidadFalt,TolFaltNeto
,VrConcPagos,VrConcDctos,VrConcFondo,VrConcSeguro,VrConcAporte,VrConcImpuesto,EdoLiqCausac,VrRemesas,VrRecCaja,CdForma,FechaEgr,VrSeguroRc,VrReteCREE,TarifaRtc,CodTarRtc,CdCenCosto,CdSubCenCos)
 VALUES (@pmTipDoc,@pmOrdPago,@pmIdCia,@pmItem,@pmTipMuc,@pmManifiesto,@pmIdCiaMuc,@pmIdVehiculo,@pmIdConductor,@pmIdPropietario,@pmIdPoseedor,@pmVrTotalFletes,@pmVrDescuento,@pmVrRetencion,@pmVrReteIca,@pmVrAnticipos,@pmVrFaltantes,@pmVrSeguros
,@pmVrFondos,@pmVrAportes,@pmVrOtrosDctos,@pmVrImpuestos,@pmVrEstampilla,@pmVrOtrosPagos,@pmVrNeto,@pmTarifaTabla,@pmTarifaFlete,@pmUnidTarifa,@pmPesoTotal,@pmUnidades,@pmVolumen,@pmPesoOrigen,@pmUnidOrigen,@pmVolOrigen,@pmPesoDestino,@pmUnidDestino,@pmVolDestino,@pmBaseRet,@pmBaseIca,@pmBaseSeg,@pmBaseImp,@pmTarifaRet,@pmTarifaIca,@pmTarifaSeg,@pmTarifaImp,@pmCodTarRet,@pmCodTarIca,@pmTipoLiq
,@pmTipEgr,@pmEgreso,@pmIdCiaEgr,@pmNumCheque,@pmReferencia,@pmCantFaltante,@pmUnidadFalt,@pmTolFaltNeto,@pmVrConcPagos,@pmVrConcDctos,@pmVrConcFondo,@pmVrConcSeguro,@pmVrConcAporte,@pmVrConcImpuesto,@pmEdoLiqCausac,@pmVrRemesas,@pmVrRecCaja,@pmCdForma,@pmFechaEgr,@pmVrSeguroRc,@pmVrReteCREE,@pmTarifaRtc,@pmCodTarRtc,@pmCdCenCosto,@pmCdSubCenCos)
GO