if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paDelTiposMotdev]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paDelTiposMotdev]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsDevFco]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsDevFco]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsDevFcr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsDevFcr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTiposMotdev]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTiposMotdev]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDevFco]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryDevFco]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDevFco_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryDevFco_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDevFcoLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryDevFcoLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDevFcoRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryDevFcoRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDevFcr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryDevFcr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDevFcr_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryDevFcr_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDevFcrLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryDevFcrLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDevFcrRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryDevFcrRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTiposMotdev]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTiposMotdev]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTiposMotdevDso]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTiposMotdevDso]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_KdexOdc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_KdexOdc]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpDevFco]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpDevFco]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpDevFcr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpDevFcr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTiposMotdev]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTiposMotdev]
GO


SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryDevFcoRel] @pmTipDev VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmnClieCon VARCHAR(16)=Null,@pmIdCajero VARCHAR(11)=Null,@pmIdVend  VARCHAR(16)=Null
,@pmNitCliente VARCHAR(16)=Null,@pmEnEfectivo BIT=Null,@pmModalidad VARCHAR(10)=Null,@pmModdDev VARCHAR(10)=Null
 AS 
 SELECT TipDev,Devolucion,D.IdCia AS CdCia,Compania,Fecha,D.IdConcepto AS CdConcepto,Concepto,TipDoc,Factura,IdCiaDoc,FecDoc
,nClieCon,Nombre,CC.TipoId AS CliTipoId,CC.Dv AS CliDV,CC.Direccion AS CliDireccion,CC.IdLocal AS CliCodCiudad,LE.Localidad AS CiudadClie
,LE.IdDep AS CdDepClie,DE.Departamento AS DptoClie,CC.Telefono AS CliTelefono,CC.email AS ClieEmail,Excento
,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrFletes,VrCargos,VrOtrDcto,VrCostos,VrNeto,VrEfectivo
,VrPagosOtr,Cantidad,CantPuntos,BaseImp,BaseRet,IdCajero,UC.Usuario AS Cajero,D.IdVend AS NitVendedor,V.RazonSocial AS Vendedor
,TarifaCom,NitCliente,T.RazonSocial AS NomCliente,D.CdAgencia AS IdAgncia,Agencia,CodAgencia,D.IdCCosto AS IdCentCost,CCosto,D.IdSubCos AS IdSubCent,SubCosto
,D.IdLocal AS CodCiudad,LD.Localidad AS DocCiudad,EnEfectivo,Pedido,IdCiaPed,Remision,IdCiaRem,FecPedido,Modalidad,ModdDev,NumAutoriza,PlacaVehic,KmtVehic,VrImpCons
,VrReteCREE,TarifaRtc,CodTarRtc,BaseIvaObsq,VrIvaObsequio,D.CdMotDev,MotivoDev,OrigenAdd,TipCom,Comprobante,IdCiaCom,D.Observacion AS Observ,D.IdEstado AS CdEstado,Estado
,TimeSys,IdCiaCrea,D.IdUsuario AS IdUsuari,U.Usuario AS NomUsuario
--datos del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,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.Fax AS TercFax,T.e_mail AS TercEmail
,T.SitioWeb AS TercSitioWeb,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie
 FROM Trn_DevFco AS D INNER JOIN ClieContado AS CC ON D.nClieCon=CC.IdCliente
 INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia INNER JOIN  Conceptos AS C ON D.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON D.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario 
 INNER JOIN adm_Usuarios AS UC ON D.IdCajero=UC.IdUsuario
 INNER JOIN Terceros AS V ON D.IdVend=V.IdTercero  INNER JOIN  Localidades AS LD ON D.IdLocal=LD.IdLocal
 INNER JOIN Localidades AS LE ON CC.IdLocal=LE.IdLocal  INNER JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep 
 LEFT JOIN Terceros AS T ON D.NitCliente=T.IdTercero  LEFT JOIN Agencias  AS A ON D.CdAgencia=A.IdAgencia
 LEFT JOIN Localidades AS L ON T.IdLocal=L.IdLocal  LEFT JOIN Departamentos AS DP ON L.IdDep=DP.IdDep
 LEFT JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen LEFT JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 LEFT JOIN CentroCosto AS CT ON D.IdCCosto=CT.IdCCosto LEFT JOIN SubCentros AS SC ON D.IdSubCos=SC.IdSubCos
 LEFT JOIN TercCliente AS CLI ON D.NitCliente=CLI.IdClie LEFT JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona 
 LEFT JOIN Zonas AS Z ON SZ.IdZona=Z.IdZona  LEFT JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo
 LEFT JOIN TiposMotdev AS MD ON D.CdMotDev=MD.IdMotDev
 WHERE  Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND TipDev LIKE ISNULL(@pmTipDev,'%')
 AND D.IdCia LIKE ISNULL(@pmIdCia,'%%') AND D.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND nClieCon LIKE ISNULL(@pmnClieCon,'%') AND IdCajero LIKE ISNULL(@pmIdCajero,'%')
 AND D.IdVend LIKE ISNULL(@pmIdVend,'%') AND NitCliente LIKE ISNULL(@pmNitCliente ,'%')
 AND Modalidad LIKE ISNULL(@pmModalidad,'%')  AND ModdDev LIKE ISNULL(@pmModdDev,'%')
 AND (EnEfectivo=ISNULL(@pmEnEfectivo,0) or EnEfectivo=ISNULL(@pmEnEfectivo,1))
 ORDER BY D.IdCia,Devolucion
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryDevFcr_Cr] @pmTipDev VARCHAR(3),@pmDevolucionIni INT,@pmDevolucionFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDev,TipoDoc,Devolucion,D.IdCia AS CdCia,Compania,D.Fecha AS FechaDev,D.IdConcepto AS CdConcepto,Concepto,D.TipDoc AS TipFact,D.Factura AS NumFactura
,IdCiaDoc,FecDoc,IdCliente,T.RazonSocial AS NomCliente,D.IdAgencia AS IdAgncia,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion
,VrReteICA,VrReteIVA,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrCostos,VrSobretasa,VrImpGlobal,VrFaltantes,VrAnticipos,D.VrImpCarbono,VrNeto,Cantidad,CantPuntos,BaseImp,BaseRet
,VrReteCREE,TarifaRtc,CodTarRtc,D.IdCCosto AS IdCenCost,CC.CCosto AS CentCosto,D.IdSubCos AS IdSubCent,SC.SubCosto AS SubcCosto,D.IdVend AS NitVend,VN.RazonSocial AS Vendedor
,TarifaCom,D.IdLocEnv AS CodCiuEnvio,LE.Localidad AS CiudadEnvio,LE.IdDep AS CdDepEnvio,DE.Departamento AS DptoEnvio,Pedido,IdCiaPed,D.Remision AS NumRemision,D.IdCiaRem AS CdCiaRem
,D.Cotizacion AS NumCotizacion,D.IdCiaCot AS CdCiaCot,FecPedido,Modalidad,ModdDev,PlacaVehic,KmtVehic,ZonaFrontera,VrImpCons,BaseIvaObsq,VrIvaObsequio,DevMasivo,NumRecibo,CdCiaRec,CdMotDev,MotivoDev
,OrigenAdd,TipCom,TipoCom,Comprobante,IdCiaCom,D.Observacion AS Observ,D.IdEstado AS CdEstado,Estado,D.TimeSys AS Fec_Add,IdCiaCrea,D.IdUsuario AS IdUsuari,Usuario,Leyenda
--Información del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion
,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,DP.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail
,T.SitioWeb AS TercSitioWeb,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
--información del cliente
,NitRepLeg,NomRepLeg,CLI.NitContac AS ClieNitContac,CLI.NomContac AS ClieNomContacto,CLI.TelContac AS ClieTelContac,CLI.emlContac AS ClieEmailContac,CargContac,DiasEntga,CLI.IdSzona AS CdSubzona,Subzona,Zona
,CLI.IdGrupo AS CdGrupoCli,GrupoClie,CLI.IdClase AS CdClaseCta,NumCuenta,CLI.IdBanco AS CdBanco,Banco,ExcIva,LiqFletes,CLI.FactSold AS FactSoldicom,Autoret,VrCupo,VrSaldo,Contrato,CLI.NContrato AS CliNumContrato
,A.DirAgncia AS AgeDireccion,A.TelAgncia AS AgeTelefono,A.FaxAgncia AS AgeFax,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia
--Detalles
,Item,K.IdProducto AS CodProducto,DescripProd,K.IdBodega AS CdBodega,Bodega,Descrip AS BodDescrip,CdTanque,Entradas,Salidas,K.IdUnd AS CdUnid,UM.Unidad AS UnidMedida,VrUnitario,VrPrecio,VrCostProm,K.TarifaIva AS KarTarifIva,VrIvaEnt,VrIvaSal,TarifaDct,VrDctoEnt,VrDctoSal
,VrCostoEnt,VrCostoSal,K.TarifaRet AS KarTarifRet,VrReteEnt,VrReteSal,K.TarifaIca AS KarTarifIca,VrIcaEnt,VrIcaSal,VrBruto,CdUbic,NumLote,FechLote,CdAgencia,KA.Agencia AS KarAgencia,KA.CodAgencia AS KarCodAgencia,KA.NContrato AS KarAgeContrato,KA.Referencia AS KarAgeRefencia,CdLocal,KL.Localidad AS KarCiudad
,CdCCosto,KC.CCosto AS Kar_Ccosto,K.CdSubCos AS CodSubCos,KS.SubCosto AS Kar_Subcentro,K.pVehiculo AS KarPlacaVeh,K.Referencia AS KarReferencia,Descripcion,Comptmntos,CdProdEquiv,TipOrd,NumOrden,IdCiaOrd,K.Cotizacion AS KarNumCotiza,K.IdCiaCot AS KarCiaCotiza,K.Remision AS KarRemision,K.IdCiaRem AS KarCiaRem
,K.Factura AS KarFactura,TipDocDev,NumDocDev,K.IdVend AS KarNitVend,KV.RazonSocial AS KarVendedor,K.Comision AS KarTarifaCom,CdOperario,KO.RazonSocial AS NomOperario,ComisnOper,Sobretasa,TasaNac,TasaDep,TasaMun,Soldicom,ImpGlobal,OtroImpto,Unidades,ItemCombo,Servcios,NoVentas,EsCombo,EsProdBase
,ListaPrec,VrBase,CdMoneda,VrTasaCamb,VrDivisa1,VrDivisa2,VrDivisa3,Referencia2,FecOrden
,CdMngra,NumInicial,NumFinal
--productos
,PM.Referencia AS Prod_Referencia,TipoRef,PM.IdSubgrupo AS CdSubgrupo,Subgrupo,S.IdGrupo AS CdGrupo,Grupo,G.IdLinea AS CdLinea,Linea,PM.IdMarca AS CdMarca,Marca,Tamano,PM.UndMed AS CdUndMed
,SU.Unidad AS ProdUnidad,ExtciaMin,ExtciaMax,ExtciaAct,VrCostAnt,VrCosto,VrCostPmd,FecUltcom,FecUltVta,Seriales,Lotes,Combo,Tanques,DescripLong,Precio1,Precio2,Precio3,Precio4,Precio5
 FROM Trn_DevFcr AS D  INNER JOIN Trn_Kardex AS K ON D.TipDev=K.TipDoc AND D.Devolucion=K.Documento AND D.IdCia=K.IdCia
 INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia  INNER JOIN Sys_TiposDoc AS TD ON D.TipDev=TD.IdDoc
 INNER JOIN Terceros AS T ON D.IdCliente=T.IdTercero INNER JOIN  Conceptos AS C ON D.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON D.IdEstado=ED.IdEstado   INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
 INNER JOIN  Terceros AS VN ON D.IdVend=VN.IdTercero INNER JOIN Agencias  AS A ON D.IdAgencia=A.IdAgencia 
 INNER JOIN CentroCosto AS CC ON D.IdCCosto=CC.IdCCosto 
 INNER JOIN TercCliente AS CLI ON D.IdCliente=CLI.IdClie INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona 
 INNER JOIN  Zonas AS Z ON SZ.IdZona=Z.IdZona  INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo
 INNER JOIN Bancos AS BC ON CLI.IdBanco=BC.IdBanco
 INNER JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal  INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 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 ProdMcias  AS PM ON K.IdProducto=PM.IdProducto INNER JOIN Bodegas AS B ON K.IdBodega=B.IdBodega
 INNER JOIN UndMed AS UM ON K.IdUnd=UM.IdUnd
 INNER JOIN SubGrupos AS S ON PM.IdSubgrupo=S.IdSubgrupo INNER JOIN Grupos AS G ON S.IdGrupo=G.IdGrupo 
 INNER JOIN Lineas AS LN ON G.IdLinea=LN.IdLinea INNER JOIN Marcas AS M ON PM.IdMarca=M.IdMarca
 INNER JOIN Sys_Um AS SU ON PM.UndMed=SU.UndMed 
 LEFT JOIN  Localidades AS LE ON D.IdLocEnv=LE.IdLocal  LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep 
 LEFT JOIN CentroCosto AS KC ON K.CdCCosto=KC.IdCCosto LEFT JOIN SubCentros AS KS ON K.CdSubCos=KS.IdSubCos
 LEFT JOIN SubCentros AS SC ON D.IdSubCos=SC.IdSubCos 
 LEFT JOIN Agencias  AS KA ON K.CdAgencia=KA.IdAgencia LEFT JOIN Localidades AS KL ON K.CdLocal=KL.IdLocal
 LEFT JOIN Terceros AS KV ON K.IdVend=KV.IdTercero  LEFT JOIN Terceros AS KO ON K.CdOperario=KO.IdTercero
 LEFT JOIN TiposCom AS TCM ON D.TipCom=TCM.IdCom LEFT JOIN TiposMotdev AS MD ON D.CdMotDev=MD.IdMotDev
 WHERE TipDev=@pmTipDev  AND Devolucion BETWEEN @pmDevolucionIni AND @pmDevolucionFin AND D.IdCia=@pmIdCia
 AND EsProdBase=0
 ORDER BY Devolucion,Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryDevFcrRel] @pmTipDev VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdAgencia VARCHAR(16)=Null,@pmIdVend VARCHAR(16)=Null,@pmIdLocEnv VARCHAR(8)=Null
,@pmModalidad VARCHAR(10)=Null,@pmModdDev VARCHAR(10)=Null
 AS 
 SELECT TipDev,Devolucion,D.IdCia AS CdCia,Compania,Fecha,D.IdConcepto AS CdConcepto,Concepto,TipDoc,Factura,IdCiaDoc,FecDoc,IdCliente,T.RazonSocial AS NomCliente
,D.IdAgencia AS IdAgncia,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrFletes,VrOtros,VrCargos
,VrOtrDcto,VrCostos,VrSobretasa,VrImpGlobal,D.VrImpCarbono,VrFaltantes,VrAnticipos,VrNeto,Cantidad,CantPuntos,BaseImp,BaseRet,D.IdCCosto AS IdCenCost,CCosto,D.IdSubCos AS IdSubCent,SubCosto
,D.IdVend AS NitVend,VN.RazonSocial AS Vendedor,TarifaCom,D.IdLocEnv AS CodCiuEnvio,LE.Localidad AS CiudadEnvio,LE.IdDep AS CdDepEnvio,DE.Departamento AS DptoEnvio
,Pedido,IdCiaPed,Remision,IdCiaRem,Cotizacion,IdCiaCot,FecPedido,PlacaVehic,KmtVehic,Modalidad,ModdDev,ZonaFrontera,VrImpCons,VrReteCREE,TarifaRtc,CodTarRtc,BaseIvaObsq,VrIvaObsequio,DevMasivo,NumRecibo,CdCiaRec
,D.CdMotDev,MotivoDev,OrigenAdd,TipCom,Comprobante,IdCiaCom,D.Observacion AS Observ,D.IdEstado AS CdEstado,Estado,TimeSys,IdCiaCrea,D.IdUsuario AS IdUsuari,Usuario
--Información del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion
,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,DP.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail
,T.SitioWeb AS TercSitioWeb,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
--información del cliente
,NitRepLeg,NomRepLeg,CLI.NitContac AS ClieNitContac,CLI.NomContac AS ClieNomContacto,CLI.TelContac AS ClieTelContac,CLI.emlContac AS ClieEmailContac,CargContac,DiasEntga,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona
,CLI.IdGrupo AS CdGrupoCli,GrupoClie,CLI.IdClase AS CdClaseCta,NumCuenta,CLI.IdBanco AS CdBanco,Banco,ExcIva,LiqFletes,CLI.FactSold AS FactSoldicom,Autoret,VrCupo,VrSaldo,Contrato,CLI.NContrato AS CliNumContrato
,A.DirAgncia AS AgeDireccion,A.TelAgncia AS AgeTelefono,A.FaxAgncia AS AgeFax,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia,CdFntePago,FuentePago
 FROM Trn_DevFcr AS D INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia 
 INNER JOIN Terceros AS T ON D.IdCliente=T.IdTercero INNER JOIN  Conceptos AS C ON D.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON D.IdEstado=ED.IdEstado   INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
 INNER JOIN  Terceros AS VN ON D.IdVend=VN.IdTercero INNER JOIN Agencias  AS A ON D.IdAgencia=A.IdAgencia 
 INNER JOIN CentroCosto AS CC ON D.IdCCosto=CC.IdCCosto 
 INNER JOIN TercCliente AS CLI ON D.IdCliente=CLI.IdClie INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona 
 INNER JOIN  Zonas AS Z ON SZ.IdZona=Z.IdZona  INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo
 INNER JOIN Bancos AS BC ON CLI.IdBanco=BC.IdBanco
 INNER JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal  INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 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  Localidades AS LE ON D.IdLocEnv=LE.IdLocal  LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep 
 LEFT JOIN SubCentros AS SC ON D.IdSubCos=SC.IdSubCos LEFT JOIN FuentesPago AS FPG ON A.CdFntePago=FPG.IdFuente
 LEFT JOIN TiposMotdev AS MD ON D.CdMotDev=MD.IdMotDev
 WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND TipDev LIKE ISNULL(@pmTipDev ,'%') 
 AND D.IdCia LIKE ISNULL(@pmIdCia,'%%') AND D.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND IdCliente LIKE ISNULL(@pmIdCliente,'%')  AND D.IdAgencia LIKE ISNULL(@pmIdAgencia ,'%')
 AND D.IdVend LIKE ISNULL(@pmIdVend,'%') AND D.IdLocEnv LIKE ISNULL(@pmIdLocEnv,'%')
 AND Modalidad LIKE ISNULL(@pmModalidad,'%') AND ModdDev LIKE ISNULL(@pmModdDev,'%')
 ORDER BY D.IdCia,Devolucion
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_KdexOdc] @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmItem,tmIdProducto,DescripProd,tmIdBodega,tmCdTanque,tmEntradas,tmVrPrecio,tmTarifaDct,tmVrDcto,tmVrUnitario
,(tmEntradas*tmVrPrecio)-tmVrDcto AS VrTotal,tmTarifaIva,tmVrIva,tmTarifaRet,tmVrRete,tmTarifaIca,tmVrIca,tmUnidades,Bodega,tmReferencia,tmDescripcion,tmImpGlobal,tmSobretasa
,tmCdCCosto,tmCdSubCos,tmpVehiculo,tmCodTarDct,tmCodTarRet,tmCodTarIca,tmServcios,Tanques,tmTipDoc,tmDocumento,tmIdCia,tmItemCbo AS ItemReq,tmVrImvCosto
 FROM  tm_Kdex AS K INNER JOIN ProdMcias AS P ON K.tmIdProducto=P.IdProducto INNER JOIN Bodegas AS B ON K.tmIdBodega=B.IdBodega
 WHERE  tmNumero=@pmtmNumero 
 ORDER BY tmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryDevFco_Cr] @pmTipDev VARCHAR(3),@pmDevolucionIni INT,@pmDevolucionFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDev,TipoDoc,Devolucion,D.IdCia AS CdCia,Compania,D.Fecha AS FechaDev,D.IdConcepto AS CdConcepto,Concepto,D.TipDoc AS TipoFac,D.Factura AS NumFactura,IdCiaDoc,FecDoc
,nClieCon,Nombre,CC.TipoId AS CliTipoId,CC.Dv AS CliDV,CC.Direccion AS CliDireccion,CC.IdLocal AS CliCodCiudad,LE.Localidad AS CiudadClie
,LE.IdDep AS CdDepClie,DE.Departamento AS DptoClie,CC.Telefono AS CliTelefono,CC.email AS ClieEmail,Excento
,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrFletes,VrCargos,VrOtrDcto,VrCostos,VrNeto,VrEfectivo
,VrPagosOtr,Cantidad,CantPuntos,BaseImp,BaseRet,IdCajero,UC.Usuario AS Cajero,D.IdVend AS NitVendedor,V.RazonSocial AS Vendedor
,TarifaCom,NitCliente,T.RazonSocial AS NomCliente,D.CdAgencia AS IdAgncia,Agencia,CodAgencia,D.IdCCosto AS IdCentCost,CCosto,D.IdSubCos AS IdSubCent,SubCosto
,D.IdLocal AS CodCiudad,LD.Localidad AS DocCiudad,EnEfectivo,Pedido,IdCiaPed,D.Remision AS NumRemision,D.IdCiaRem AS CdCiaRem,FecPedido,Modalidad,ModdDev,NumAutoriza,PlacaVehic,KmtVehic
,VrImpCons,VrReteCREE,TarifaRtc,CodTarRtc,BaseIvaObsq,VrIvaObsequio,OrigenAdd,TipCom,TipoCom,Comprobante,IdCiaCom,D.Observacion AS Observ,D.IdEstado AS CdEstado,Estado
,D.CdMotDev,MotivoDev,D.TimeSys AS Fec_Add,IdCiaCrea,D.IdUsuario AS IdUsuari,U.Usuario AS NomUsuario,Leyenda
--datos del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,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.Fax AS TercFax,T.e_mail AS TercEmail
,T.SitioWeb AS TercSitioWeb,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
--Detalles
,Item,K.IdProducto AS CodProducto,DescripProd,K.IdBodega AS CdBodega,Bodega,Descrip AS BodDescrip,CdTanque,Entradas,Salidas,K.IdUnd AS CdUnid,UM.Unidad AS UnidMedida,VrUnitario,VrPrecio,VrCostProm,K.TarifaIva AS KarTarifIva,VrIvaEnt,VrIvaSal,TarifaDct,VrDctoEnt,VrDctoSal
,VrCostoEnt,VrCostoSal,K.TarifaRet AS KarTarifRet,VrReteEnt,VrReteSal,K.TarifaIca AS KarTarifIca,VrIcaEnt,VrIcaSal,VrBruto,CdUbic,NumLote,FechLote,CdCCosto,CdSubCos,pVehiculo,K.Referencia AS KarReferencia,Descripcion,TipOrd,NumOrden,IdCiaOrd
,K.Factura AS KarFactura,OtroImpto,Unidades,ItemCombo,Servcios,NoVentas,EsCombo,EsProdBase,ListaPrec,VrBase,CdMoneda,VrTasaCamb,VrDivisa1,VrDivisa2,VrDivisa3,Referencia2
--productos
,PM.Referencia AS Prod_Referencia,TipoRef,PM.IdSubgrupo AS CdSubgrupo,Subgrupo,S.IdGrupo AS CdGrupo,Grupo,G.IdLinea AS CdLinea,Linea,PM.IdMarca AS CdMarca,Marca,Tamano,PM.UndMed AS CdUndMed
,SU.Unidad AS ProdUnidad,ExtciaMin,ExtciaMax,ExtciaAct,VrCostAnt,VrCosto,VrCostPmd,FecUltcom,FecUltVta,Seriales,Lotes,Combo,Tanques,DescripLong,Precio1,Precio2,Precio3,Precio4,Precio5
 FROM Trn_DevFco AS D INNER JOIN Trn_Kardex AS K ON D.TipDev=K.TipDoc AND D.Devolucion=K.Documento AND D.IdCia=K.IdCia
 INNER JOIN ClieContado AS CC ON D.nClieCon=CC.IdCliente
 INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia  INNER JOIN Sys_TiposDoc AS TD ON D.TipDev=TD.IdDoc
 INNER JOIN  Conceptos AS C ON D.IdConcepto=C.IdConcepto  INNER JOIN EstadoDoc AS ED ON D.IdEstado=ED.IdEstado 
 INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario INNER JOIN adm_Usuarios AS UC ON D.IdCajero=UC.IdUsuario
 INNER JOIN  Terceros AS V ON D.IdVend=V.IdTercero  INNER JOIN  Localidades AS LD ON D.IdLocal=LD.IdLocal
 INNER JOIN  Localidades AS LE ON CC.IdLocal=LE.IdLocal  INNER JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep 
 INNER JOIN ProdMcias  AS PM ON K.IdProducto=PM.IdProducto INNER JOIN Bodegas AS B ON K.IdBodega=B.IdBodega
 INNER JOIN UndMed AS UM ON K.IdUnd=UM.IdUnd
 INNER JOIN SubGrupos AS S ON PM.IdSubgrupo=S.IdSubgrupo INNER JOIN Grupos AS G ON S.IdGrupo=G.IdGrupo 
 INNER JOIN Lineas AS LN ON G.IdLinea=LN.IdLinea INNER JOIN Marcas AS M ON PM.IdMarca=M.IdMarca
 INNER JOIN Sys_Um AS SU ON PM.UndMed=SU.UndMed 
 LEFT JOIN Terceros AS T ON D.NitCliente=T.IdTercero  LEFT JOIN Agencias  AS A ON D.CdAgencia=A.IdAgencia
 LEFT JOIN Localidades AS L ON T.IdLocal=L.IdLocal  LEFT JOIN Departamentos AS DP ON L.IdDep=DP.IdDep
 LEFT JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen LEFT JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 LEFT JOIN TiposCom AS TC ON D.TipCom=TC.IdCom
 LEFT JOIN CentroCosto AS CT ON D.IdCCosto=CT.IdCCosto LEFT JOIN SubCentros AS SC ON D.IdSubCos=SC.IdSubCos
 LEFT JOIN TiposMotdev AS MD ON D.CdMotDev=MD.IdMotDev
 WHERE TipDev=@pmTipDev  AND Devolucion BETWEEN  @pmDevolucionIni AND @pmDevolucionFin  AND D.IdCia=@pmIdCia
 AND EsProdBase=0
 ORDER BY Devolucion,Item
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paDelTiposMotdev] @pmIdMotDev VARCHAR(4)
 AS 
 DELETE FROM TiposMotdev WHERE IdMotDev=@pmIdMotDev
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTiposMotdevDso] @pmTipoFact VARCHAR(4)=Null
 AS 
 --@pmTipoFact NO APLICA AUN
 SELECT IdMotDev,MotivoDev,TipoFact 
 FROM TiposMotdev
 WHERE Inactivo=0
 ORDER BY MotivoDev
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTiposMotdev] @pmIdMotDev VARCHAR(4),@pmMotivoDev VARCHAR(150),@pmTipoFact VARCHAR(3),@pmInactivo BIT
 AS 
 INSERT INTO TiposMotdev (IdMotDev,MotivoDev,TipoFact,Inactivo)
 VALUES (@pmIdMotDev,@pmMotivoDev,@pmTipoFact,@pmInactivo)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTiposMotdev] @pmIdMotDev VARCHAR(4),@pmMotivoDev VARCHAR(150),@pmTipoFact VARCHAR(3),@pmInactivo BIT
 AS 
 UPDATE TiposMotdev SET MotivoDev=@pmMotivoDev,TipoFact=@pmTipoFact,Inactivo=@pmInactivo
 WHERE IdMotDev=@pmIdMotDev
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTiposMotdev] @pmIdMotDev VARCHAR(4)
 AS 
 IF @pmIdMotDev IS NULL
  BEGIN
   SELECT IdMotDev,MotivoDev,TipoFact 
   FROM TiposMotdev
   WHERE Inactivo=0
  END
 ELSE
  BEGIN
   SELECT IdMotDev,MotivoDev,TipoFact,Inactivo
   FROM TiposMotdev
   WHERE IdMotDev=@pmIdMotDev
  END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryDevFcr] @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDev,Devolucion,IdCia,Fecha,IdConcepto,TipDoc,Factura,IdCiaDoc,FecDoc,IdCliente,IdAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA
,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrCostos,VrSobretasa,VrImpGlobal,VrFaltantes,VrAnticipos,VrNeto,Cantidad,CantPuntos,BaseImp,BaseRet,IdCCosto,IdSubCos,IdVend,TarifaCom
,CodTarCom,IdLocEnv,Pedido,IdCiaPed,Remision,IdCiaRem,Cotizacion,IdCiaCot,FecPedido,PlacaVehic,KmtVehic,Modalidad,ModdDev,OrigenAdd,TipCom,Comprobante,IdCiaCom
,Observacion,IdEstado,ZonaFrontera,VrImpCons,VrReteCREE,TarifaRtc,CodTarRtc,BaseIvaObsq,VrIvaObsequio,VrImpCarbono,DevMasivo,NumRecibo,CdCiaRec,CdMotDev,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_DevFcr
 WHERE TipDev=@pmTipDev AND Devolucion=@pmDevolucion AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryDevFcrLta] @pmTipDev VARCHAR(3),@pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME,@pmDevolucionIni INT=Null,@pmDevolucionFin INT=Null
,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdConcepto VARCHAR(4)=Null,@pmIdVend VARCHAR(16)=Null,@pmModalidad VARCHAR(10)=Null
,@pmModdDev VARCHAR(10)=Null
 AS 
 SELECT Devolucion,IdCia,Fecha,D.IdConcepto AS CdConcepto,Concepto,TipDoc,Factura,IdCiaDoc,FecDoc,IdCliente,T.RazonSocial AS NomCliente,IdAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion
,VrReteICA,VrReteIVA,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrCostos,VrSobretasa,VrImpGlobal,VrFaltantes,VrAnticipos,VrImpCarbono,VrNeto,Cantidad,CantPuntos,BaseImp,BaseRet,IdCCosto,IdSubCos
,IdVend,V.RazonSocial AS Vendedor,TarifaCom,CodTarCom,IdLocEnv,Pedido,IdCiaPed,Remision,IdCiaRem,Cotizacion,IdCiaCot,FecPedido,PlacaVehic,KmtVehic,VrImpCons,VrReteCREE,TarifaRtc,CodTarRtc,BaseIvaObsq,VrIvaObsequio
,DevMasivo,NumRecibo,CdCiaRec,Modalidad,ModdDev,ZonaFrontera,CdMotDev,OrigenAdd,TipCom,Comprobante,IdCiaCom,D.Observacion AS Observ,D.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,D.IdUsuario AS IdUsuari,Usuario,TipDev
 FROM Trn_DevFcr AS D INNER JOIN Terceros AS T ON D.IdCliente=T.IdTercero
 INNER JOIN Terceros AS V ON D.IdVend=V.IdTercero
 INNER JOIN  Conceptos AS CN ON D.IdConcepto=CN.IdConcepto
 INNER JOIN EstadoDoc AS ED ON D.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario 
 WHERE TipDev=@pmTipDev  AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 AND Devolucion BETWEEN ISNULL(@pmDevolucionIni,0) AND ISNULL(@pmDevolucionFin,2147483647)
 AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdCliente LIKE ISNULL(@pmIdCliente,'%') AND D.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND IdVend LIKE ISNULL(@pmIdVend,'%') AND Modalidad LIKE ISNULL(@pmModalidad,'%')
 AND ModdDev LIKE ISNULL(@pmModdDev,'%') 
 ORDER BY IdCia,Devolucion
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpDevFcr] @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmTipDoc VARCHAR(3),@pmFactura INT,@pmIdCiaDoc CHAR(2),@pmFecDoc SMALLDATETIME,@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmVrSubTotal MONEY,@pmVrDescuento MONEY
,@pmVrImpuesto MONEY,@pmVrRetencion MONEY,@pmVrReteICA MONEY,@pmVrReteIVA MONEY,@pmVrFletes MONEY,@pmVrOtros MONEY,@pmVrCargos MONEY,@pmVrOtrDcto MONEY,@pmVrCostos MONEY,@pmVrSobretasa MONEY,@pmVrImpGlobal MONEY,@pmVrFaltantes MONEY,@pmVrAnticipos MONEY,@pmVrNeto MONEY,@pmCantidad DECIMAL(14,4)
,@pmCantPuntos DECIMAL(14,4),@pmBaseImp MONEY,@pmBaseRet MONEY,@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdVend VARCHAR(16),@pmTarifaCom DECIMAL(14,4),@pmCodTarCom VARCHAR(4),@pmIdLocEnv VARCHAR(8),@pmPedido INT,@pmIdCiaPed CHAR(2),@pmRemision INT,@pmIdCiaRem CHAR(2),@pmCotizacion INT
,@pmIdCiaCot CHAR(2),@pmFecPedido SMALLDATETIME,@pmModalidad VARCHAR(10),@pmPlacaVehic VARCHAR(10),@pmKmtVehic INT,@pmModdDev VARCHAR(10),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmZonaFrontera INT,@pmVrImpCons MONEY,@pmVrReteCREE MONEY
,@pmTarifaRtc DECIMAL(14,4),@pmCodTarRtc VARCHAR(4),@pmBaseIvaObsq MONEY,@pmVrIvaObsequio MONEY,@pmVrImpCarbono MONEY,@pmDevMasivo INT,@pmNumRecibo INT,@pmCdCiaRec CHAR(2),@pmCdMotDev VARCHAR(4),@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_DevFcr SET Fecha=@pmFecha,IdConcepto=@pmIdConcepto,TipDoc=@pmTipDoc,Factura=@pmFactura,IdCiaDoc=@pmIdCiaDoc,FecDoc=@pmFecDoc,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,VrSubTotal=@pmVrSubTotal,VrDescuento=@pmVrDescuento,VrImpuesto=@pmVrImpuesto,VrRetencion=@pmVrRetencion,VrReteICA=@pmVrReteICA
,VrReteIVA=@pmVrReteIVA,VrFletes=@pmVrFletes,VrOtros=@pmVrOtros,VrCargos=@pmVrCargos,VrOtrDcto=@pmVrOtrDcto,VrCostos=@pmVrCostos,VrSobretasa=@pmVrSobretasa,VrImpGlobal=@pmVrImpGlobal,VrFaltantes=@pmVrFaltantes,VrAnticipos=@pmVrAnticipos,VrNeto=@pmVrNeto,Cantidad=@pmCantidad,CantPuntos=@pmCantPuntos,BaseImp=@pmBaseImp
,BaseRet=@pmBaseRet,IdCCosto=@pmIdCCosto,IdSubCos=@pmIdSubCos,IdVend=@pmIdVend,TarifaCom=@pmTarifaCom,CodTarCom=@pmCodTarCom,IdLocEnv=@pmIdLocEnv,Pedido=@pmPedido,IdCiaPed=@pmIdCiaPed,Remision=@pmRemision,IdCiaRem=@pmIdCiaRem,Cotizacion=@pmCotizacion,IdCiaCot=@pmIdCiaCot
,FecPedido=@pmFecPedido,Modalidad=@pmModalidad,ModdDev=@pmModdDev,PlacaVehic=@pmPlacaVehic,KmtVehic=@pmKmtVehic,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,Observacion=@pmObservacion,IdEstado=@pmIdEstado,ZonaFrontera=@pmZonaFrontera,VrImpCons=@pmVrImpCons,FecUpdate=@pmFecUpdate
,VrReteCREE=@pmVrReteCREE,TarifaRtc=@pmTarifaRtc,CodTarRtc=@pmCodTarRtc,BaseIvaObsq=@pmBaseIvaObsq,VrIvaObsequio=@pmVrIvaObsequio,VrImpCarbono=@pmVrImpCarbono,DevMasivo=@pmDevMasivo,NumRecibo=@pmNumRecibo,CdCiaRec=@pmCdCiaRec,CdMotDev=@pmCdMotDev
 WHERE TipDev=@pmTipDev AND Devolucion=@pmDevolucion AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsDevFcr] @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmTipDoc VARCHAR(3),@pmFactura INT,@pmIdCiaDoc CHAR(2),@pmFecDoc SMALLDATETIME,@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmVrSubTotal MONEY,@pmVrDescuento MONEY,@pmVrImpuesto MONEY
,@pmVrRetencion MONEY,@pmVrReteICA MONEY,@pmVrReteIVA MONEY,@pmVrFletes MONEY,@pmVrOtros MONEY,@pmVrCargos MONEY,@pmVrOtrDcto MONEY,@pmVrCostos MONEY,@pmVrSobretasa MONEY,@pmVrImpGlobal MONEY,@pmVrFaltantes MONEY,@pmVrAnticipos MONEY,@pmVrNeto MONEY,@pmCantidad DECIMAL(14,4),@pmCantPuntos DECIMAL(14,4),@pmBaseImp MONEY
,@pmBaseRet MONEY,@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdVend VARCHAR(16),@pmTarifaCom DECIMAL(14,4),@pmCodTarCom VARCHAR(4),@pmIdLocEnv VARCHAR(8),@pmPedido INT,@pmIdCiaPed CHAR(2),@pmRemision INT,@pmIdCiaRem CHAR(2),@pmCotizacion INT,@pmIdCiaCot CHAR(2),@pmFecPedido SMALLDATETIME,@pmModalidad VARCHAR(10)
,@pmPlacaVehic VARCHAR(10),@pmKmtVehic INT,@pmModdDev VARCHAR(10),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmZonaFrontera INT,@pmVrImpCons MONEY,@pmVrReteCREE MONEY,@pmTarifaRtc DECIMAL(14,4),@pmCodTarRtc VARCHAR(4),@pmBaseIvaObsq MONEY,@pmVrIvaObsequio MONEY,@pmVrImpCarbono MONEY
,@pmDevMasivo INT,@pmNumRecibo INT,@pmCdCiaRec CHAR(2),@pmCdMotDev VARCHAR(4),@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_DevFcr (TipDev,Devolucion,IdCia,Fecha,IdConcepto,TipDoc,Factura,IdCiaDoc,FecDoc,IdCliente,IdAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrCostos,VrSobretasa,VrImpGlobal,VrFaltantes,VrAnticipos,VrNeto,Cantidad,CantPuntos,BaseImp,BaseRet,IdCCosto,IdSubCos,IdVend,TarifaCom,CodTarCom,IdLocEnv
,Pedido,IdCiaPed,Remision,IdCiaRem,Cotizacion,IdCiaCot,FecPedido,Modalidad,ModdDev,PlacaVehic,KmtVehic,ZonaFrontera,OrigenAdd,TipCom,Comprobante,IdCiaCom,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario,VrImpCons,VrReteCREE,TarifaRtc,CodTarRtc,BaseIvaObsq,VrIvaObsequio,VrImpCarbono,DevMasivo,NumRecibo,CdCiaRec,CdMotDev)
 VALUES (@pmTipDev,@pmDevolucion,@pmIdCia,@pmFecha,@pmIdConcepto,@pmTipDoc,@pmFactura,@pmIdCiaDoc,@pmFecDoc,@pmIdCliente,@pmIdAgencia,@pmVrSubTotal,@pmVrDescuento,@pmVrImpuesto,@pmVrRetencion,@pmVrReteICA,@pmVrReteIVA,@pmVrFletes,@pmVrOtros,@pmVrCargos,@pmVrOtrDcto,@pmVrCostos,@pmVrSobretasa,@pmVrImpGlobal,@pmVrFaltantes
,@pmVrAnticipos,@pmVrNeto,@pmCantidad,@pmCantPuntos,@pmBaseImp,@pmBaseRet,@pmIdCCosto,@pmIdSubCos,@pmIdVend,@pmTarifaCom,@pmCodTarCom,@pmIdLocEnv,@pmPedido,@pmIdCiaPed,@pmRemision,@pmIdCiaRem,@pmCotizacion,@pmIdCiaCot,@pmFecPedido,@pmModalidad,@pmModdDev,@pmPlacaVehic,@pmKmtVehic,@pmZonaFrontera,@pmOrigenAdd,@pmTipCom,@pmComprobante,@pmIdCiaCom
,@pmObservacion,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmVrImpCons,@pmVrReteCREE,@pmTarifaRtc,@pmCodTarRtc,@pmBaseIvaObsq,@pmVrIvaObsequio,@pmVrImpCarbono,@pmDevMasivo,@pmNumRecibo,@pmCdCiaRec,@pmCdMotDev)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryDevFcoLta] @pmTipDev VARCHAR(3),@pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME,@pmDevolucionIni INT=Null,@pmDevolucionFin INT=Null
,@pmIdCia CHAR(2)=Null,@pmnClieCon VARCHAR(16)=Null,@pmIdConcepto VARCHAR(4)=Null,@pmIdVend VARCHAR(16)=Null,@pmIdCajero VARCHAR(11)=Null
,@pmNitCliente VARCHAR(16)=Null,@pmModalidad VARCHAR(10)=Null,@pmModdDev VARCHAR(10)=Null
 AS 
 SELECT Devolucion,IdCia,Fecha,D.IdConcepto AS CdConcepto,Concepto,TipDoc,Factura,IdCiaDoc,FecDoc,nClieCon,Nombre,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrFletes
,VrCargos,VrOtrDcto,VrCostos,VrNeto,VrEfectivo,VrPagosOtr,Cantidad,CantPuntos,VrImpCons,VrReteCREE,TarifaRtc,CodTarRtc,BaseImp,BaseRet,IdCajero,UC.Usuario AS Cajero,IdVend,V.RazonSocial AS Vendedor,TarifaCom,CodTarCom,NitCliente
,T.RazonSocial AS NomCliente,CdAgencia,IdCCosto,IdSubCos,D.IdLocal AS CodCiudad,EnEfectivo,Pedido,IdCiaPed,Remision,IdCiaRem,FecPedido,Modalidad,ModdDev,NumAutoriza,PlacaVehic,KmtVehic,BaseIvaObsq,VrIvaObsequio,CdMotDev
,OrigenAdd,TipCom,Comprobante,IdCiaCom,D.Observacion AS Observ,D.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,D.IdUsuario AS IdUsuari,U.Usuario AS NomUsuario,TipDev
 FROM Trn_DevFco AS D INNER JOIN Conceptos AS CN ON D.IdConcepto=CN.IdConcepto
 INNER JOIN EstadoDoc AS ED ON D.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario 
 INNER JOIN adm_Usuarios AS UC ON D.IdCajero=UC.IdUsuario 
 INNER JOIN Terceros AS V ON D.IdVend=V.IdTercero
 INNER JOIN ClieContado AS C ON D.nClieCon=C.IdCliente
 LEFT JOIN  Terceros AS T ON D.NitCliente=T.IdTercero
 WHERE TipDev=@pmTipDev  AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 AND Devolucion BETWEEN ISNULL(@pmDevolucionIni,0) AND ISNULL(@pmDevolucionFin,2147483647)
 AND IdCia LIKE ISNULL(@pmIdCia,'%%')  AND nClieCon LIKE ISNULL(@pmnClieCon,'%')
 AND IdCajero LIKE ISNULL(@pmIdCajero,'%') AND NitCliente LIKE ISNULL(@pmNitCliente,'%') 
 AND D.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')  AND IdVend LIKE ISNULL(@pmIdVend,'%')
 AND Modalidad LIKE ISNULL(@pmModalidad,'%') AND ModdDev LIKE ISNULL(@pmModdDev,'%') 
 ORDER BY IdCia,Devolucion
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryDevFco] @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDev,Devolucion,IdCia,Fecha,IdConcepto,TipDoc,Factura,IdCiaDoc,FecDoc,nClieCon,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrFletes
,VrCargos,VrOtrDcto,VrCostos,VrNeto,VrEfectivo,VrPagosOtr,Cantidad,CantPuntos,BaseImp,BaseRet,IdCajero,IdVend,TarifaCom,CodTarCom,NitCliente,CdAgencia,IdCCosto
,IdSubCos,IdLocal,EnEfectivo,Pedido,IdCiaPed,Remision,IdCiaRem,FecPedido,Modalidad,ModdDev,OrigenAdd,NumAutoriza,PlacaVehic,KmtVehic,VrImpCons,VrReteCREE,TarifaRtc,CodTarRtc
,BaseIvaObsq,VrIvaObsequio,CdMotDev,TipCom,Comprobante,IdCiaCom,Observacion,IdEstado,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_DevFco
 WHERE TipDev=@pmTipDev AND Devolucion=@pmDevolucion AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsDevFco] @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmTipDoc VARCHAR(3),@pmFactura INT,@pmIdCiaDoc CHAR(2),@pmFecDoc SMALLDATETIME,@pmnClieCon VARCHAR(16),@pmVrSubTotal MONEY
,@pmVrDescuento MONEY,@pmVrImpuesto MONEY,@pmVrRetencion MONEY,@pmVrReteICA MONEY,@pmVrReteIVA MONEY,@pmVrFletes MONEY,@pmVrCargos MONEY,@pmVrOtrDcto MONEY,@pmVrCostos MONEY,@pmVrNeto MONEY,@pmVrEfectivo MONEY,@pmVrPagosOtr MONEY,@pmCantidad DECIMAL(14,4)
,@pmCantPuntos DECIMAL(14,4),@pmBaseImp MONEY,@pmBaseRet MONEY,@pmIdCajero VARCHAR(11),@pmIdVend VARCHAR(16),@pmTarifaCom DECIMAL(14,4),@pmCodTarCom VARCHAR(4),@pmNitCliente VARCHAR(16),@pmCdAgencia VARCHAR(16),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16)
,@pmIdLocal VARCHAR(8),@pmEnEfectivo BIT,@pmPedido INT,@pmIdCiaPed CHAR(2),@pmRemision INT,@pmIdCiaRem CHAR(2),@pmFecPedido SMALLDATETIME,@pmModalidad VARCHAR(10),@pmModdDev VARCHAR(10),@pmNumAutoriza INT,@pmPlacaVehic VARCHAR(10),@pmKmtVehic INT,@pmTipCom VARCHAR(3)
,@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmVrImpCons MONEY,@pmVrReteCREE MONEY,@pmTarifaRtc DECIMAL(14,4),@pmCodTarRtc VARCHAR(4),@pmBaseIvaObsq MONEY,@pmVrIvaObsequio MONEY,@pmCdMotDev VARCHAR(4)
,@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_DevFco (TipDev,Devolucion,IdCia,Fecha,IdConcepto,TipDoc,Factura,IdCiaDoc,FecDoc,nClieCon,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrFletes,VrCargos,VrOtrDcto,VrCostos,VrNeto,VrEfectivo,VrPagosOtr,Cantidad,CantPuntos,BaseImp,BaseRet,IdCajero,IdVend,TarifaCom
,CodTarCom,NitCliente,CdAgencia,IdCCosto,IdSubCos,IdLocal,EnEfectivo,Pedido,IdCiaPed,Remision,IdCiaRem,FecPedido,Modalidad,ModdDev,PlacaVehic,KmtVehic,OrigenAdd,TipCom,Comprobante,IdCiaCom,Observacion,IdEstado,NumAutoriza,TimeSys,IdCiaCrea,IdUsuario,VrImpCons,VrReteCREE,TarifaRtc,CodTarRtc,BaseIvaObsq,VrIvaObsequio,CdMotDev)
 VALUES (@pmTipDev,@pmDevolucion,@pmIdCia,@pmFecha,@pmIdConcepto,@pmTipDoc,@pmFactura,@pmIdCiaDoc,@pmFecDoc,@pmnClieCon,@pmVrSubTotal,@pmVrDescuento,@pmVrImpuesto,@pmVrRetencion,@pmVrReteICA,@pmVrReteIVA,@pmVrFletes,@pmVrCargos,@pmVrOtrDcto,@pmVrCostos
,@pmVrNeto,@pmVrEfectivo,@pmVrPagosOtr,@pmCantidad,@pmCantPuntos,@pmBaseImp,@pmBaseRet,@pmIdCajero,@pmIdVend,@pmTarifaCom,@pmCodTarCom,@pmNitCliente,@pmCdAgencia,@pmIdCCosto,@pmIdSubCos,@pmIdLocal,@pmEnEfectivo,@pmPedido,@pmIdCiaPed,@pmRemision
,@pmIdCiaRem,@pmFecPedido,@pmModalidad,@pmModdDev,@pmPlacaVehic,@pmKmtVehic,@pmOrigenAdd,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmObservacion,@pmIdEstado,@pmNumAutoriza,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmVrImpCons,@pmVrReteCREE,@pmTarifaRtc,@pmCodTarRtc,@pmBaseIvaObsq,@pmVrIvaObsequio,@pmCdMotDev)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpDevFco] @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmTipDoc VARCHAR(3),@pmFactura INT,@pmIdCiaDoc CHAR(2),@pmFecDoc SMALLDATETIME,@pmnClieCon VARCHAR(16),@pmVrSubTotal MONEY
,@pmVrDescuento MONEY,@pmVrImpuesto MONEY,@pmVrRetencion MONEY,@pmVrReteICA MONEY,@pmVrReteIVA MONEY,@pmVrFletes MONEY,@pmVrCargos MONEY,@pmVrOtrDcto MONEY,@pmVrCostos MONEY,@pmVrNeto MONEY,@pmVrEfectivo MONEY,@pmVrPagosOtr MONEY,@pmCantidad DECIMAL(14,4)
,@pmCantPuntos DECIMAL(14,4),@pmBaseImp MONEY,@pmBaseRet MONEY,@pmIdCajero VARCHAR(11),@pmIdVend VARCHAR(16),@pmTarifaCom DECIMAL(14,4),@pmCodTarCom VARCHAR(4),@pmNitCliente VARCHAR(16),@pmCdAgencia VARCHAR(16),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16)
,@pmIdLocal VARCHAR(8),@pmEnEfectivo BIT,@pmPedido INT,@pmIdCiaPed CHAR(2),@pmRemision INT,@pmIdCiaRem CHAR(2),@pmFecPedido SMALLDATETIME,@pmModalidad VARCHAR(10),@pmModdDev VARCHAR(10),@pmNumAutoriza INT,@pmPlacaVehic VARCHAR(10),@pmKmtVehic INT,@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2)
,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmVrImpCons MONEY,@pmVrReteCREE MONEY,@pmTarifaRtc DECIMAL(14,4),@pmCodTarRtc VARCHAR(4),@pmBaseIvaObsq MONEY,@pmVrIvaObsequio MONEY,@pmCdMotDev VARCHAR(4),@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_DevFco SET Fecha=@pmFecha,IdConcepto=@pmIdConcepto,TipDoc=@pmTipDoc,Factura=@pmFactura,IdCiaDoc=@pmIdCiaDoc,FecDoc=@pmFecDoc,nClieCon=@pmnClieCon,VrSubTotal=@pmVrSubTotal,VrDescuento=@pmVrDescuento,VrImpuesto=@pmVrImpuesto,VrRetencion=@pmVrRetencion
,VrReteICA=@pmVrReteICA,VrReteIVA=@pmVrReteIVA,VrFletes=@pmVrFletes,VrCargos=@pmVrCargos,VrOtrDcto=@pmVrOtrDcto,VrCostos=@pmVrCostos,VrNeto=@pmVrNeto,VrEfectivo=@pmVrEfectivo,VrPagosOtr=@pmVrPagosOtr,Cantidad=@pmCantidad,CantPuntos=@pmCantPuntos,BaseImp=@pmBaseImp
,BaseRet=@pmBaseRet,IdCajero=@pmIdCajero,IdVend=@pmIdVend,TarifaCom=@pmTarifaCom,CodTarCom=@pmCodTarCom,NitCliente=@pmNitCliente,CdAgencia=@pmCdAgencia,IdCCosto=@pmIdCCosto,IdSubCos=@pmIdSubCos,IdLocal=@pmIdLocal,EnEfectivo=@pmEnEfectivo,PlacaVehic=@pmPlacaVehic,KmtVehic=@pmKmtVehic
,Pedido=@pmPedido,IdCiaPed=@pmIdCiaPed,Remision=@pmRemision,IdCiaRem=@pmIdCiaRem,FecPedido=@pmFecPedido,Modalidad=@pmModalidad,ModdDev=@pmModdDev,NumAutoriza=@pmNumAutoriza,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,Observacion=@pmObservacion
,IdEstado=@pmIdEstado,VrImpCons=@pmVrImpCons,VrReteCREE=@pmVrReteCREE,TarifaRtc=@pmTarifaRtc,CodTarRtc=@pmCodTarRtc,BaseIvaObsq=@pmBaseIvaObsq,VrIvaObsequio=@pmVrIvaObsequio,CdMotDev=@pmCdMotDev,FecUpdate=@pmFecUpdate
 WHERE TipDev=@pmTipDev AND Devolucion=@pmDevolucion AND IdCia=@pmIdCia
GO