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

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