INSERT INTO Sys_Fields (IdEntidad,IdCampo,Descripcion,TipoDato,LongMax,Requerido,PmryKey,FmtValue,DftValue,AliasFld,AliasQry2,IndField,TipoEntidad,LstTipo,LstClnaValor,LstClnaLista,LstQuery,Inactivo)
 VALUES ('paQryFacturasRelTra','F.TipoOperTra','','VARCHAR',10,0,0,'','0','TipoOperTra','',155,'SP','LIST','','','10;11;12;101',0)
INSERT INTO Sys_Fields (IdEntidad,IdCampo,Descripcion,TipoDato,LongMax,Requerido,PmryKey,FmtValue,DftValue,AliasFld,AliasQry2,IndField,TipoEntidad,LstTipo,LstClnaValor,LstClnaLista,LstQuery,Inactivo)
 VALUES ('paQryFacturasRelTraDet','F.TipoOperTra','','VARCHAR',10,0,0,'','0','TipoOperTra','',216,'SP','LIST','','','10;11;12;101',0)
INSERT INTO Sys_Fields (IdEntidad,IdCampo,Descripcion,TipoDato,LongMax,Requerido,PmryKey,FmtValue,DftValue,AliasFld,AliasQry2,IndField,TipoEntidad,LstTipo,LstClnaValor,LstClnaLista,LstQuery,Inactivo)
 VALUES ('paQryDevFcrRelTra','F.TipoOperTra','','VARCHAR',10,0,0,'','0','TipoOperTra','',113,'SP','LIST','','','10;11;12;101',0)
INSERT INTO Sys_Fields (IdEntidad,IdCampo,Descripcion,TipoDato,LongMax,Requerido,PmryKey,FmtValue,DftValue,AliasFld,AliasQry2,IndField,TipoEntidad,LstTipo,LstClnaValor,LstClnaLista,LstQuery,Inactivo)
 VALUES ('paQryDevFcrRelTraDet','F.TipoOperTra','','VARCHAR',10,0,0,'','0','TipoOperTra','',181,'SP','LIST','','','10;11;12;101',0)
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDevFcrRelTra]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryDevFcrRelTra]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDevFcrRelTraDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryDevFcrRelTraDet]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryDevFcrRelTra] @pmTipDev VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdVend VARCHAR(16)=Null
 AS 
 SELECT DF.TipDev,DF.Devolucion,DF.IdCia AS CdCia,Compania,DF.Fecha,DF.IdConcepto AS CdConcepto,C.Concepto AS ConcDesc,DF.TipDoc AS TipoFac,DF.Factura AS NumFactura,DF.IdCiaDoc,DF.FecDoc,DF.IdCliente,T.RazonSocial AS NomCliente
,DF.IdAgencia AS IdAgncia,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia,DF.VrSubTotal,DF.VrDescuento AS VrDcto,DF.VrImpuesto AS VrIva,DF.VrRetencion AS VrRetFte,DF.VrReteICA AS VrRetIca,DF.VrReteIVA,DF.VrFletes,DF.VrOtros,DF.VrCargos
,DF.VrOtrDcto,DF.VrCostos,DF.VrSobretasa,DF.VrImpGlobal,DF.VrFaltantes,DF.VrAnticipos,DF.VrNeto,DF.Cantidad AS CantTotal,DF.CantPuntos,DF.BaseImp,DF.BaseRet,DF.VrImpCons AS VrAutoICA,DF.ZonaFrontera AS EstAutoICA
,DF.IdVend AS NitVend,VN.RazonSocial AS Vendedor,DF.TarifaCom,DF.IdLocEnv AS CodCiuEnvio,LE.Localidad AS CiudadEnvio,LE.IdDep AS CdDepEnvio,DE.Departamento AS DptoEnvio
,DF.Pedido,DF.IdCiaPed,DF.Cotizacion,DF.IdCiaCot,DF.FecPedido,DF.VrReteCREE,DF.TarifaRtc,DF.CodTarRtc,DF.ImpAviTab,DF.TarifaAvta,DF.VrBomberil,DF.TarifaBom,DF.VrAutRetIat,DF.VrAutRetBom,DF.Modalidad,F.TipoOperTra,DF.ModdDev,DF.OrigenAdd,DF.TipCom,DF.Comprobante,DF.IdCiaCom,DF.Observacion AS Observ,DF.IdEstado AS CdEstado
,DF.TimeSys,DF.IdCiaCrea,DF.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,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
 FROM Trn_DevFcr AS DF INNER JOIN Trn_Facturas AS F ON DF.TipDoc=F.TipDoc AND DF.Factura=F.Factura AND DF.IdCiaDoc=F.IdCia
 INNER JOIN Companias AS CN ON DF.IdCia=CN.IdCia INNER JOIN Terceros AS T ON DF.IdCliente=T.IdTercero 
 INNER JOIN Conceptos AS C ON DF.IdConcepto=C.IdConcepto INNER JOIN adm_Usuarios AS U ON DF.IdUsuario=U.IdUsuario
 INNER JOIN Terceros AS VN ON DF.IdVend=VN.IdTercero INNER JOIN Agencias AS A ON DF.IdAgencia=A.IdAgencia 
 INNER JOIN TercCliente AS CLI ON DF.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 DF.IdLocEnv=LE.IdLocal LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep
 WHERE  DF.Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND  DF.TipDev LIKE ISNULL(@pmTipDev,'%')
 AND DF.IdCia LIKE ISNULL(@pmIdCia,'%%') AND DF.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND DF.IdCliente LIKE ISNULL(@pmIdCliente,'%')  AND DF.IdVend LIKE ISNULL(@pmIdVend,'%')
 ORDER BY DF.IdCia,DF.Devolucion
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryDevFcrRelTraDet] @pmTipDev VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdVend VARCHAR(16)=Null
 AS 
 SELECT DF.TipDev,DF.Devolucion,DF.IdCia AS CdCia,Compania,DF.Fecha,DF.IdConcepto AS CdConcepto,C.Concepto AS ConcDesc,DF.TipDoc AS TipoFac,DF.Factura AS NumFactura,DF.IdCiaDoc,DF.FecDoc,DF.IdCliente,T.RazonSocial AS NomCliente
,DF.IdAgencia AS IdAgncia,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia,DF.VrSubTotal,DF.VrDescuento AS VrDcto,DF.VrImpuesto AS VrIva,DF.VrRetencion AS VrRetFte,DF.VrReteICA AS VrRetIca,DF.VrReteIVA,DF.VrFletes,DF.VrOtros,DF.VrCargos
,DF.VrOtrDcto,DF.VrCostos,DF.VrSobretasa,DF.VrImpGlobal,DF.VrFaltantes,DF.VrAnticipos,DF.VrNeto,DF.Cantidad AS CantTotal,DF.CantPuntos,DF.BaseImp,DF.BaseRet
,DF.IdVend AS NitVend,VN.RazonSocial AS Vendedor,DF.TarifaCom,DF.IdLocEnv AS CodCiuEnvio,LE.Localidad AS CiudadEnvio,LE.IdDep AS CdDepEnvio,DE.Departamento AS DptoEnvio
,DF.Pedido,DF.IdCiaPed,DF.Cotizacion,DF.IdCiaCot,DF.FecPedido,DF.VrReteCREE,DF.TarifaRtc,DF.CodTarRtc,DF.VrImpCons AS VrAutoICA,DF.ZonaFrontera AS EstAutoICA,DF.ImpAviTab,DF.TarifaAvta,DF.VrBomberil,DF.TarifaBom,DF.VrAutRetIat,DF.VrAutRetBom
,DF.Modalidad,F.TipoOperTra,DF.ModdDev,DF.OrigenAdd,DF.TipCom,DF.Comprobante,DF.IdCiaCom,DF.Observacion AS Observ,DF.IdEstado AS CdEstado
,DF.TimeSys,DF.IdCiaCrea,DF.IdUsuario AS IdUsuari,Usuario
--detalles
,Item,TipoReg,FechaFact,D.TipRem AS TipRemesa,D.Remesa AS NumRemesa,D.IdCiaRem AS CdCiaRem,ItemRem,FecRemesa,Descripcion,D.Cantidad AS Cant,VrUnitario,VrCosto,UndTarifa,UndCosto,Unidades,PesoNeto,D.UndMed AS CdUmPeso,UMP.Unidad AS UmPeso,Volumen,UndVol,Cases,Cajas,Palets,CantPago
,D.TarifaIva AS DetTarifIva,D.VrImpuesto AS DetVrIva,TarifaDct,D.VrDescuento AS DetVrDcto,D.TarifaRet AS DetTarifRet,D.VrRetencion AS DetVrRetFte,D.TarifaIca AS DetTarifIca,D.VrReteIca AS DetVrRetIca,D.VrAutRetIca AS DetAutoICA,VrFaltante,D.Remision AS DetNumRemsion,DocCliente,Referencia1,Referencia2,Referencia3
,CdMercancia,DescripMcia,CdConcepto,CF.Concepto AS DetConcepto,CdCCosto,CCosto,D.CdSubCos AS CdSubCosto,SubCosto,NitTercero,NT.RazonSocial AS DetTercero,CdAgencia,D.pVehiculo AS PlacaVeh,TipoAfiVehic,IdOrigen,LO.Localidad AS CiudadOrigen,LO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,IdDestino,LD.Localidad AS CiudadDestino,LD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,D.Anulado AS DetAnulado,TipDocRef,NumDocRef,IdCiaRef,FecDocRef,CantidadFalt,UnidadFalt,NumPedRem,CiaPedRem
--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
 FROM Trn_DevFcr AS DF INNER JOIN Trn_Facturas AS F ON DF.TipDoc=F.TipDoc AND DF.Factura=F.Factura AND DF.IdCiaDoc=F.IdCia
 INNER JOIN Trn_TraFacRemesas AS D ON DF.TipDev=D.TipDoc AND DF.Devolucion=D.Factura  AND DF.IdCia=D.IdCia
 INNER JOIN Companias AS CN ON DF.IdCia=CN.IdCia INNER JOIN Terceros AS T ON DF.IdCliente=T.IdTercero 
 INNER JOIN Conceptos AS C ON DF.IdConcepto=C.IdConcepto INNER JOIN adm_Usuarios AS U ON DF.IdUsuario=U.IdUsuario
 INNER JOIN Terceros AS VN ON DF.IdVend=VN.IdTercero INNER JOIN Agencias  AS A ON DF.IdAgencia=A.IdAgencia 
 INNER JOIN TercCliente AS CLI ON DF.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 Localidades AS LO ON D.IdOrigen=LO.IdLocal INNER JOIN Departamentos AS DPO ON LO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS LD ON D.IdDestino=LD.IdLocal INNER JOIN Departamentos AS DPD ON LD.IdDep=DPD.IdDep
 LEFT JOIN Localidades AS LE ON DF.IdLocEnv=LE.IdLocal LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep 
 LEFT JOIN Sys_Um AS UMP ON D.UndMed=UMP.UndMed
 LEFT JOIN Mercancias AS MC ON D.CdMercancia=MC.IdMercancia LEFT JOIN  ConcDiversos AS CF ON D.CdConcepto=CF.IdConcepto
 LEFT JOIN CentroCosto AS CC ON D.CdCCosto=CC.IdCCosto  LEFT JOIN SubCentros AS SC ON D.CdSubCos=SC.IdSubCos
 LEFT JOIN Terceros AS NT ON D.NitTercero=NT.IdTercero
 WHERE DF.Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND  DF.TipDev LIKE ISNULL(@pmTipDev,'%')
 AND DF.IdCia LIKE ISNULL(@pmIdCia,'%%') AND DF.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND DF.IdCliente LIKE ISNULL(@pmIdCliente,'%')  AND DF.IdVend LIKE ISNULL(@pmIdVend,'%')
 ORDER BY DF.IdCia,DF.Devolucion
GO