if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAcuCuentas_San]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsAcuCuentas_San]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAcuCuentasAge_San]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsAcuCuentasAge_San]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAcuCuentasAux_San]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsAcuCuentasAux_San]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAcuCuentasCc_San]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsAcuCuentasCc_San]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAcuCuentasNit_San]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsAcuCuentasNit_San]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAcuCuentasVeh_San]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsAcuCuentasVeh_San]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAcuCuentasVeht_San]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsAcuCuentasVeht_San]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAcuFisCuentas_San]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsAcuFisCuentas_San]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAcuFisCuentasAge_San]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsAcuFisCuentasAge_San]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAcuFisCuentasAux_San]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsAcuFisCuentasAux_San]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAcuFisCuentasCc_San]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsAcuFisCuentasCc_San]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAcuFisCuentasNit_San]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsAcuFisCuentasNit_San]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAcuFisCuentasVeh_San]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsAcuFisCuentasVeh_San]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAcuFisCuentasVeht_San]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsAcuFisCuentasVeht_San]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAcuNiifCuentas_San]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsAcuNiifCuentas_San]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAcuNiifCuentasAge_San]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsAcuNiifCuentasAge_San]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAcuNiifCuentasAux_San]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsAcuNiifCuentasAux_San]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAcuNiifCuentasCc_San]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsAcuNiifCuentasCc_San]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAcuNiifCuentasNit_San]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsAcuNiifCuentasNit_San]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAcuNiifCuentasVeh_San]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsAcuNiifCuentasVeh_San]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAcuNiifCuentasVeht_San]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsAcuNiifCuentasVeht_San]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAutoriza]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsAutoriza]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryAcuPropietariosSalCue]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryAcuPropietariosSalCue]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryAcuPropietariosSalPro]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryAcuPropietariosSalPro]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryAutoriza]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryAutoriza]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryAutoriza_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryAutoriza_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryAutorizaDoc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryAutorizaDoc]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryAutorizaLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryAutorizaLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryAutorizaNum]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryAutorizaNum]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFacturasFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryFacturasFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraRemesaFac]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraRemesaFac]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraRemesaMuc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraRemesaMuc]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryVehiculosCau]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryVehiculosCau]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryVehiculosSalCue]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryVehiculosSalCue]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryVehiculosSalPro]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryVehiculosSalPro]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpAcuCuentasAgeSA]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpAcuCuentasAgeSA]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpAcuCuentasAuxSA]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpAcuCuentasAuxSA]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpAcuCuentasAuxSan]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpAcuCuentasAuxSan]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpAcuCuentasCcSA]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpAcuCuentasCcSA]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpAcuCuentasNitSA]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpAcuCuentasNitSA]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpAcuCuentasSA]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpAcuCuentasSA]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpAcuCuentasVehSA]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpAcuCuentasVehSA]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpAcuCuentasVehtSA]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpAcuCuentasVehtSA]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpAcuFisCuentasAgeSA]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpAcuFisCuentasAgeSA]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpAcuFisCuentasAuxSA]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpAcuFisCuentasAuxSA]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpAcuFisCuentasCcSA]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpAcuFisCuentasCcSA]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpAcuFisCuentasNitSA]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpAcuFisCuentasNitSA]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpAcuFisCuentasSA]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpAcuFisCuentasSA]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpAcuFisCuentasVehSA]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpAcuFisCuentasVehSA]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpAcuFisCuentasVehtSA]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpAcuFisCuentasVehtSA]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpAcuNiifCuentasAgeSA]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpAcuNiifCuentasAgeSA]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpAcuNiifCuentasAuxSA]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpAcuNiifCuentasAuxSA]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpAcuNiifCuentasCcSA]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpAcuNiifCuentasCcSA]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpAcuNiifCuentasNitSA]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpAcuNiifCuentasNitSA]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpAcuNiifCuentasSA]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpAcuNiifCuentasSA]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpAcuNiifCuentasVehSA]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpAcuNiifCuentasVehSA]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpAcuNiifCuentasVehtSA]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpAcuNiifCuentasVehtSA]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpAutoriza]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpAutoriza]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUpAcuCuentasAuxSA] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmTipoAcum VARCHAR(4),@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 UPDATE AcuCuentasAux SET AcuCuentasAux.SaldoAnterior=ISNULL(SA.NuevoSaldo,0)
 FROM AcuCuentasAux INNER JOIN (SELECT nAnno,nMes,IdCia,nReg,IdCuenta,IdTercero,CodAgncia,IdCCosto,IdSubCos,SubAuxiliar,TipoAfiVehic,TipoAcum,SaldoAnterior+TotalDebitos-TotalCreditos AS NuevoSaldo
 FROM AcuCuentasAux WHERE nAnno=@pmAnnoAnt AND nMes=@pmMesAnt AND TipoAcum=@pmTipoAcum) AS SA ON AcuCuentasAux.IdCia=SA.IdCia AND AcuCuentasAux.IdCuenta=SA.IdCuenta
 AND AcuCuentasAux.IdTercero=SA.IdTercero AND AcuCuentasAux.CodAgncia=SA.CodAgncia AND AcuCuentasAux.IdCCosto=SA.IdCCosto AND AcuCuentasAux.IdSubCos=SA.IdSubCos
 AND AcuCuentasAux.SubAuxiliar=SA.SubAuxiliar AND AcuCuentasAux.TipoAfiVehic=SA.TipoAfiVehic
 WHERE AcuCuentasAux.nAnno=@pmnAnno AND AcuCuentasAux.nMes=@pmnMes AND AcuCuentasAux.TipoAcum=@pmTipoAcum
 AND (@pmIdCia IS NULL OR AcuCuentasAux.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR AcuCuentasAux.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUpAcuNiifCuentasAuxSA] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmTipoAcum VARCHAR(4),@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 UPDATE AcuNiifCuentasAux SET AcuNiifCuentasAux.SaldoAnterior=ISNULL(SA.NuevoSaldo,0)
 FROM AcuNiifCuentasAux INNER JOIN (SELECT nAnno,nMes,IdCia,nReg,IdCuenta,IdTercero,CodAgncia,IdCCosto,IdSubCos,SubAuxiliar,TipoAfiVehic,TipoAcum,SaldoAnterior+TotalDebitos-TotalCreditos AS NuevoSaldo
 FROM AcuNiifCuentasAux WHERE nAnno=@pmAnnoAnt AND nMes=@pmMesAnt AND TipoAcum=@pmTipoAcum) AS SA ON AcuNiifCuentasAux.IdCia=SA.IdCia AND AcuNiifCuentasAux.IdCuenta=SA.IdCuenta
 AND AcuNiifCuentasAux.IdTercero=SA.IdTercero AND AcuNiifCuentasAux.CodAgncia=SA.CodAgncia AND AcuNiifCuentasAux.IdCCosto=SA.IdCCosto AND AcuNiifCuentasAux.IdSubCos=SA.IdSubCos
 AND AcuNiifCuentasAux.SubAuxiliar=SA.SubAuxiliar AND AcuNiifCuentasAux.TipoAfiVehic=SA.TipoAfiVehic
 WHERE AcuNiifCuentasAux.nAnno=@pmnAnno AND AcuNiifCuentasAux.nMes=@pmnMes AND AcuNiifCuentasAux.TipoAcum=@pmTipoAcum
 AND (@pmIdCia IS NULL OR AcuNiifCuentasAux.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR AcuNiifCuentasAux.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUpAcuFisCuentasAuxSA] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmTipoAcum VARCHAR(4),@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 UPDATE AcuFisCuentasAux SET AcuFisCuentasAux.SaldoAnterior=ISNULL(SA.NuevoSaldo,0)
 FROM AcuFisCuentasAux INNER JOIN (SELECT nAnno,nMes,IdCia,nReg,IdCuenta,IdTercero,CodAgncia,IdCCosto,IdSubCos,SubAuxiliar,TipoAfiVehic,TipoAcum,SaldoAnterior+TotalDebitos-TotalCreditos AS NuevoSaldo
 FROM AcuFisCuentasAux WHERE nAnno=@pmAnnoAnt AND nMes=@pmMesAnt AND TipoAcum=@pmTipoAcum) AS SA ON AcuFisCuentasAux.IdCia=SA.IdCia AND AcuFisCuentasAux.IdCuenta=SA.IdCuenta
 AND AcuFisCuentasAux.IdTercero=SA.IdTercero AND AcuFisCuentasAux.CodAgncia=SA.CodAgncia AND AcuFisCuentasAux.IdCCosto=SA.IdCCosto AND AcuFisCuentasAux.IdSubCos=SA.IdSubCos
 AND AcuFisCuentasAux.SubAuxiliar=SA.SubAuxiliar AND AcuFisCuentasAux.TipoAfiVehic=SA.TipoAfiVehic
 WHERE AcuFisCuentasAux.nAnno=@pmnAnno AND AcuFisCuentasAux.nMes=@pmnMes AND AcuFisCuentasAux.TipoAcum=@pmTipoAcum
 AND (@pmIdCia IS NULL OR AcuFisCuentasAux.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR AcuFisCuentasAux.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryFacturasFmt] @pmTipDoc VARCHAR(3),@pmFacturaIni INT,@pmFacturaFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT F.TipDoc AS Tip_Doc,TipoDoc,F.Factura AS NumFactura,F.IdCia AS CdCia,Compania,F.Fecha AS FechaDoc,F.IdConcepto AS CdConcepto,C.Concepto AS DescConcepto,F.IdCliente,T.RazonSocial AS NomCliente
,F.IdAgencia AS IdAgenc,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia,FechaVence,VrSubTotal,F.VrDescuento AS VrDcto,F.VrImpuesto AS VrIva,F.VrRetencion AS VrRetFte,F.VrReteICA AS VrRetIca,F.VrReteIVA,F.VrFletes
,F.VrOtros,F.VrCargos,F.VrOtrDcto,F.VrCostos,F.VrSobretasa,F.VrImpGlobal,F.VrFaltantes,F.VrAnticipos,F.VrNeto,VrAplicado,F.Cantidad AS CantTotal,F.PesoTotal,F.UnidTotal,F.VolTotal,CantFalt,F.VrCostoRem,F.VrCostoAfi,F.BaseImp,F.BaseRet,F.BaseIca,F.BaseRiv
,F.TarifaIva AS TarifIva,F.TarifaRet AS TarifRet,F.TarifaIca AS TarifIca,TarifaRiv,F.IdVend AS NitVend,VN.RazonSocial AS Vendedor,TarifaCom
,DirEnvio,F.IdLocEnv AS IdCiuEnvio,LE.Localidad AS CiudadEnvio,DiasEntraga,F.NitContac AS FacNitContac,F.NomContac AS FacNomContac,F.TelContac AS FacTelContac,F.emlContac AS FacEmailContac,CargoContac
,F.IdForma AS CdForma,F.DetallePago,MulPlazos,F.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago,NitEmpTrans,EmpTrans,F.TipPed,F.Pedido,F.IdCiaPed,F.TipRem AS TipRemision,F.Remision AS NumRemision,TipCot,F.Cotizacion AS NumCotizacion,F.IdCiaCot AS CiaCotiza,F.FecPedido
,AutzaMora,AutzaCupo,F.Modalidad,KmtVehic,F.TipCom,TipoCom,F.Comprobante,F.IdCiaCom,F.Anulado AS FacAnulado,F.NumDev,F.FecDev,F.Observacion AS Observ,VrReteCREE,TarifaRtc,CodTarRtc,F.IdEstado AS CdEstado,F.TimeSys AS Fec_Add,F.FecUpdate AS Fec_Update,F.IdCiaCrea,F.IdUsuario AS CdUsuario,Usuario,Leyenda
--detales
,Item,TipoReg,FechaFact,D.TipRem AS TipRemesa,D.Remesa AS NumRemesa,D.IdCiaRem AS CdCiaRem,ItemRem,FecRemesa,D.Descripcion,D.Cantidad AS Cant,VrUnitario,VrCosto,UndTarifa,UndCosto,Unidades,PesoNeto,D.UndMed AS CdUmPeso,UMP.Unidad AS UmPeso,Volumen,UndVol,Cases,Cajas,Palets
,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,VrFaltante,D.Remision AS DetNumRemsion,DocCliente,Referencia1,Referencia2,Referencia3,CdMercancia,DescripMcia
,D.CdConcepto,CF.Concepto AS DetConcepto,D.CdCCosto,CCosto,D.CdSubCos AS CdSubCentro,SubCosto,NitTercero,NT.RazonSocial AS DetTercero,CdAgencia,D.pVehiculo AS PlacaVeh,D.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,VrDeclMcia,TarifaSeg,VrSeguroRem,NitAsegurad,R.CdRutaTarif,RT.Ruta
--Información del cliente
,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
,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,ExcIva,LiqFletes,Autoret,VrCupo,VrSaldo
,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_Facturas AS F INNER JOIN  Trn_TraFacRemesas AS D ON F.TipDoc=D.TipDoc  AND F.Factura=D.Factura AND F.IdCia=D.IdCia
 INNER JOIN Companias AS CN ON F.IdCia=CN.IdCia INNER JOIN Sys_TiposDoc AS TD ON F.TipDoc=TD.IdDoc
 INNER JOIN Conceptos AS C ON F.IdConcepto=C.IdConcepto INNER JOIN adm_Usuarios AS U ON F.IdUsuario=U.IdUsuario
 INNER JOIN Plazos AS PZ ON F.IdPlazo=PZ.IdPlazo INNER JOIN  Terceros AS VN ON F.IdVend=VN.IdTercero
 INNER JOIN Terceros AS T ON F.IdCliente=T.IdTercero  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 TercCliente AS CLI ON F.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 Agencias AS A ON F.IdAgencia=A.IdAgencia 
 INNER JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal  INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 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 F.IdLocEnv=LE.IdLocal   LEFT JOIN TiposCom AS TCM ON F.TipCom=TCM.IdCom  
 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
 LEFT JOIN Trn_TraRemesa AS R ON D.TipRem=R.TipDoc AND D.Remesa=R.NumOrden AND D.IdCiaRem=R.IdCia
 LEFT JOIN Rutas AS RT ON R.CdRutaTarif=RT.IdRuta
 WHERE F.TipDoc=@pmTipDoc AND F.Factura BETWEEN @pmFacturaIni AND @pmFacturaFin AND F.IdCia=@pmIdCia
 ORDER BY F.Factura
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraRemesaFac]  @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdCliente VARCHAR(16)=Null
 AS 
SELECT FR.TipDoc AS CdTipDoc,TipoDoc,Factura,FR.IdCia AS CdCia,CN.Compania AS NomCiaFact,FechaFact,Item,TipoReg,TipRem,Remesa,IdCiaRem,CR.Compania AS NomCiaRem,ItemRem,FecRemesa,FR.Descripcion AS DescripRem
,FR.Cantidad AS CantFact,VrUnitario,UndTarifa,VrCosto,UndCosto,TarifaDct,VrDescuento,TarifaIva,VrImpuesto,TarifaRet,VrRetencion,TarifaIca,VrReteIca,VrFaltante,CantidadFalt,UnidadFalt,Unidades,PesoNeto,FR.UndMed AS CdUmPeso,Unidad
,Volumen,UndVol,Cases,Cajas,Palets,FR.CdConcepto AS CdConcept,CDV.Concepto AS DescConcept,CdMercancia,DescripMcia,CdAgencia,Agencia,CdCCosto,CCosto,FR.CdSubCos AS CdSubCentro,SubCosto,FR.TipoAfiVehic AS TipoAfiVeh,pVehiculo
,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
,Remision,DocCliente,Referencia1,Referencia2,Referencia3,FR.Anulado AS FacAnulado,TipDocRef,NumDocRef,IdCiaRef,FecDocRef,NumPedRem,CiaPedRem
--datos de remesa
,Modalidad,IdRemitente,NomRemite,IdDestinatario,NomDestino,R.IdConductor AS RemCedCond,CDT.RazonSocial AS RemConductor,nRemolque,NumManif,IdCiaManif,R.Observacion AS RemObserv,RA.TipoRuta,RA.TipoMintrans
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo
--datos de factura encabezados
,FacCdConc,CF.Concepto AS FacConcepto,T.TipoId AS TercTipo,FacNitClie,T.Dv AS TercDv,T.RazonSocial AS NombreClie,FacNitVend,VN.RazonSocial AS Vendedor,FacCdCiudad,LF.Localidad AS FacCiudad,LF.IdDep AS FacCdDpto,DF.Departamento AS FacDpto,FacVrNeto
,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie
 FROM Trn_TraFacRemesas AS FR INNER JOIN Sys_TiposDoc AS TD ON FR.TipDoc=TD.IdDoc INNER JOIN Companias AS CN ON FR.IdCia=CN.IdCia 
 INNER JOIN Localidades AS LO ON FR.IdOrigen=LO.IdLocal INNER JOIN Departamentos AS DPO ON LO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS LD ON FR.IdDestino=LD.IdLocal INNER JOIN Departamentos AS DPD ON LD.IdDep=DPD.IdDep
 --SUBQUERY FACTURAS
 INNER JOIN (SELECT F.TipDoc AS FacTipo,F.Factura AS FacNumero,F.IdCia AS FacCdCia,F.IdConcepto AS FacCdConc,F.IdCliente AS FacNitClie,F.IdAgencia AS FacIdAgenc
 ,F.IdVend AS FacNitVend,F.IdLocEnv AS FacCdCiudad,F.VrCargos AS FacVrCargos,F.VrOtrDcto AS FacVrOtrDctos,F.VrNeto AS FacVrNeto
 FROM Trn_Facturas AS F WHERE F.Fecha BETWEEN @pmFechaIni  AND @pmFechaFin AND  F.Modalidad='TRANSPORTE'  AND F.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND F.IdCliente LIKE ISNULL(@pmIdCliente,'%')
 UNION ALL
 SELECT D.TipDev,D.Devolucion,D.IdCia,D.IdConcepto,D.IdCliente,D.IdAgencia,D.IdVend,D.IdLocEnv,D.VrCargos,D.VrOtrDcto,D.VrNeto
 FROM Trn_DevFcr AS D WHERE D.Fecha BETWEEN @pmFechaIni  AND @pmFechaFin AND D.Modalidad='TRANSPORTE' AND D.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND D.IdCliente LIKE ISNULL(@pmIdCliente,'%') 
 UNION ALL
 SELECT N.TipDoc,N.NumNota,N.IdCia,N.IdConcepto,N.IdCliente,N.IdAgencia,N.IdVend,N.IdLocal,N.VrOtros,0,N.VrNeto
 FROM Trn_Notas AS N WHERE N.Fecha BETWEEN @pmFechaIni  AND @pmFechaFin AND N.Modalidad='REMESAS' AND  N.IdCia LIKE ISNULL(@pmIdCia,'%%')
  AND N.IdCliente LIKE ISNULL(@pmIdCliente,'%')  ) AS F ON FR.TipDoc=F.FacTipo AND FR.Factura=F.FacNumero AND FR.IdCia=F.FacCdCia
 INNER JOIN Terceros AS T ON F.FacNitClie=T.IdTercero INNER JOIN TercCliente AS CLI ON F.FacNitClie=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 Terceros AS VN ON F.FacNitVend=VN.IdTercero INNER JOIN Conceptos AS CF ON F.FacCdConc=CF.IdConcepto
 LEFT JOIN Localidades AS LF ON F.FacCdCiudad=LF.IdLocal LEFT JOIN Departamentos AS DF ON LF.IdDep=DF.IdDep
 LEFT JOIN Companias AS CR ON FR.IdCiaRem=CR.IdCia  LEFT JOIN Sys_Um AS UMP ON FR.UndMed=UMP.UndMed
 LEFT JOIN ConcDiversos AS CDV ON FR.CdConcepto=CDV.IdConcepto  LEFT JOIN Agencias  AS A ON FR.CdAgencia=A.IdAgencia
 LEFT JOIN CentroCosto AS CC ON FR.CdCCosto=CC.IdCCosto  LEFT JOIN SubCentros AS SC ON FR.CdSubCos=SC.IdSubCos
 LEFT JOIN Trn_TraRemesa AS R ON FR.TipRem=R.TipDoc AND FR.Remesa=R.NumOrden AND FR.IdCiaRem=R.IdCia
 LEFT JOIN Trn_TraRemAnexo AS RA ON FR.TipRem=RA.TipDoc AND FR.Remesa=RA.NumOrden AND FR.IdCiaRem=RA.IdCia
 LEFT JOIN Terceros AS CDT ON R.IdConductor=CDT.IdTercero
 LEFT JOIN Vehiculos AS V ON FR.pVehiculo=V.IdVehiculo LEFT JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 LEFT JOIN Mercancias AS MC ON FR.CdMercancia=MC.IdMercancia
 WHERE FR.FechaFact BETWEEN @pmFechaIni AND @pmFechaFin AND FR.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND F.FacNitClie LIKE ISNULL(@pmIdCliente,'%')
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsAcuNiifCuentasVeht_San] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmTipoAcum VARCHAR(3),@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 INSERT INTO AcuNiifCuentasVeht (nAnno,nMes,IdCia,IdCuenta,IdTercero,IdVehiculo,TipoAfiVehic,TipoAcum,SaldoAnterior,TotalDebitos,TotalCreditos)
 SELECT @pmnAnno,@pmnMes,S.IdCia,S.IdCuenta,S.IdTercero,S.IdVehiculo,S.TipoAfiVehic,S.TipoAcum,S.SaldoAnterior+S.TotalDebitos-S.TotalCreditos,0,0
 FROM AcuNiifCuentasVeht AS S WHERE S.nAnno=@pmAnnoAnt AND S.nMes=@pmMesAnt AND S.TipoAcum=@pmTipoAcum
 AND NOT EXISTS (SELECT * FROM AcuNiifCuentasVeht AS AC WHERE AC.nAnno=@pmnAnno AND AC.nMes=@pmnMes AND AC.TipoAcum=@pmTipoAcum
 AND AC.IdCia=S.IdCia AND AC.IdCuenta=S.IdCuenta AND AC.IdTercero=S.IdTercero AND AC.IdVehiculo=S.IdVehiculo AND AC.TipoAfiVehic=S.TipoAfiVehic)
 AND (@pmIdCia IS NULL OR S.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR S.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUpAcuNiifCuentasVehtSA] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmTipoAcum VARCHAR(3),@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 UPDATE AcuNiifCuentasVeht SET AcuNiifCuentasVeht.SaldoAnterior=ISNULL(SA.NuevoSaldo,0)
 FROM AcuNiifCuentasVeht INNER JOIN (SELECT nAnno,nMes,IdCia,IdCuenta,IdTercero,IdVehiculo,TipoAfiVehic,TipoAcum,SaldoAnterior+TotalDebitos-TotalCreditos AS NuevoSaldo
 FROM AcuNiifCuentasVeht WHERE nAnno=@pmAnnoAnt AND nMes=@pmMesAnt AND TipoAcum=@pmTipoAcum) AS SA ON AcuNiifCuentasVeht.IdCia=SA.IdCia AND AcuNiifCuentasVeht.IdCuenta=SA.IdCuenta
 AND AcuNiifCuentasVeht.IdTercero=SA.IdTercero AND AcuNiifCuentasVeht.IdVehiculo=SA.IdVehiculo AND AcuNiifCuentasVeht.TipoAfiVehic=SA.TipoAfiVehic AND AcuNiifCuentasVeht.TipoAcum=SA.TipoAcum
 WHERE AcuNiifCuentasVeht.nAnno=@pmnAnno AND AcuNiifCuentasVeht.nMes=@pmnMes AND AcuNiifCuentasVeht.TipoAcum=@pmTipoAcum
 AND (@pmIdCia IS NULL OR AcuNiifCuentasVeht.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR AcuNiifCuentasVeht.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsAcuCuentasVeht_San] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmTipoAcum VARCHAR(3),@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 INSERT INTO AcuCuentasVeht (nAnno,nMes,IdCia,IdCuenta,IdTercero,IdVehiculo,TipoAfiVehic,TipoAcum,SaldoAnterior,TotalDebitos,TotalCreditos)
 SELECT @pmnAnno,@pmnMes,S.IdCia,S.IdCuenta,S.IdTercero,S.IdVehiculo,S.TipoAfiVehic,S.TipoAcum,S.SaldoAnterior+S.TotalDebitos-S.TotalCreditos,0,0
 FROM AcuCuentasVeht AS S WHERE S.nAnno=@pmAnnoAnt AND S.nMes=@pmMesAnt AND S.TipoAcum=@pmTipoAcum
 AND NOT EXISTS (SELECT * FROM AcuCuentasVeht AS AC WHERE AC.nAnno=@pmnAnno AND AC.nMes=@pmnMes AND AC.TipoAcum=@pmTipoAcum
 AND AC.IdCia=S.IdCia AND AC.IdCuenta=S.IdCuenta AND AC.IdTercero=S.IdTercero AND AC.IdVehiculo=S.IdVehiculo AND AC.TipoAfiVehic=S.TipoAfiVehic)
 AND (@pmIdCia IS NULL OR S.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR S.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUpAcuCuentasVehtSA] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmTipoAcum VARCHAR(3),@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 UPDATE AcuCuentasVeht SET AcuCuentasVeht.SaldoAnterior=ISNULL(SA.NuevoSaldo,0)
 FROM AcuCuentasVeht INNER JOIN (SELECT nAnno,nMes,IdCia,IdCuenta,IdTercero,IdVehiculo,TipoAfiVehic,TipoAcum,SaldoAnterior+TotalDebitos-TotalCreditos AS NuevoSaldo
 FROM AcuCuentasVeht WHERE nAnno=@pmAnnoAnt AND nMes=@pmMesAnt AND TipoAcum=@pmTipoAcum) AS SA ON AcuCuentasVeht.IdCia=SA.IdCia AND AcuCuentasVeht.IdCuenta=SA.IdCuenta
 AND AcuCuentasVeht.IdTercero=SA.IdTercero AND AcuCuentasVeht.IdVehiculo=SA.IdVehiculo AND AcuCuentasVeht.TipoAfiVehic=SA.TipoAfiVehic AND AcuCuentasVeht.TipoAcum=SA.TipoAcum
 WHERE AcuCuentasVeht.nAnno=@pmnAnno AND AcuCuentasVeht.nMes=@pmnMes AND AcuCuentasVeht.TipoAcum=@pmTipoAcum
 AND (@pmIdCia IS NULL OR AcuCuentasVeht.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR AcuCuentasVeht.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE  [dbo].[paQryAcuPropietariosSalCue] @pmnAnno INT,@pmnMes INT
,@pmSaldoActual MONEY,@pmIdAdmon VARCHAR(4)
AS
 SELECT V.IdPoseedor AS NitPropietario,SUM(A.SaldoAnterior+A.TotalDebitos-A.TotalCreditos) AS StotSal
 FROM  AcuPropietarios AS A INNER JOIN Vehiculos AS V ON A.IdVehiculo=V.IdVehiculo
 WHERE nAnno=@pmnAnno AND nMes=@pmnMes AND V.IdAdmon<>@pmIdAdmon AND V.TipoAfil<>'PROPIO'
 GROUP BY V.IdPoseedor
 HAVING  SUM(A.SaldoAnterior+A.TotalDebitos-A.TotalCreditos)<=@pmSaldoActual
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE  [dbo].[paQryAcuPropietariosSalPro] @pmnAnno INT,@pmnMes INT, @pmIdPropietario VARCHAR(16)
,@pmIdAdmon VARCHAR(4)
AS
 SELECT V.IdVehiculo AS PlacaVeh,V.IdPoseedor AS NitPropietario,NumVeh,IdAdmon,TipoAfil
,IdConductor,RazonSocial,IdGrupo,SUM(A.SaldoAnterior+A.TotalDebitos-A.TotalCreditos) AS StotSal
 FROM  AcuPropietarios AS A INNER JOIN Vehiculos AS V ON A.IdVehiculo=V.IdVehiculo
 INNER JOIN Terceros AS T ON V.IdConductor=T.IdTercero
 WHERE nAnno=@pmnAnno AND nMes=@pmnMes AND V.IdPoseedor=@pmIdPropietario
 AND V.IdAdmon<>@pmIdAdmon AND V.TipoAfil<>'PROPIO'
 GROUP BY  V.IdVehiculo,V.IdPoseedor,NumVeh,IdAdmon,TipoAfil,IdConductor,RazonSocial,IdGrupo
 ORDER BY  V.IdVehiculo
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryVehiculosCau] @pmIdVehiculo VARCHAR(10),@pmIdEstado VARCHAR(4)
AS
 SELECT IdVehiculo,NumVeh,CdTarifa,IdTipoVeh,PasjerosPie,PasjerosSen,IdPropietario,IdPoseedor,IdConductor,IdPpd,IdGrupo,TipoAfil,IdAdmon,NContrato
,FecIngreso, FecRetiro,FecVigencia,DocCompleta,ObligaTProd,KmActual,CentInicial,CentFinal,VrLmtCred,VrSaldoAct,IdEstado
 FROM  Vehiculos
 WHERE IdEstado<>@pmIdEstado AND  IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')
 ORDER BY IdVehiculo
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE  [dbo].[paQryVehiculosSalCue] @pmSaldoActual MONEY,@pmIdAdmon VARCHAR(4)
AS
 SELECT  IdPoseedor AS IdPropietario, SUM(VrSaldoAct) AS StotSal
 FROM  Vehiculos
 WHERE IdAdmon<>@pmIdAdmon AND TipoAfil<>'PROPIO'
 GROUP BY IdPoseedor
 HAVING  SUM(VrSaldoAct)<=@pmSaldoActual
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE  [dbo].[paQryVehiculosSalPro] @pmIdPropietario VARCHAR(16),@pmIdAdmon VARCHAR(4)
AS
 SELECT IdPoseedor AS IdPropietario,IdVehiculo,NumVeh,IdAdmon,TipoAfil,IdConductor,RazonSocial,IdGrupo,VrSaldoAct
 FROM  Vehiculos INNER JOIN Terceros ON Vehiculos.IdConductor=Terceros.IdTercero
 WHERE IdPoseedor=@pmIdPropietario AND IdAdmon<>@pmIdAdmon AND TipoAfil<>'PROPIO'
 ORDER  BY IdPoseedor,IdVehiculo
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryAutoriza_Cr] @pmTipAut VARCHAR(3),@pmAutorizaIni INT,@pmAutorizaFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipAut,TD.TipoDoc AS TipoDocAut,Autoriza,A.IdCia AS CodCia,Compania,Fecha,A.IdConcepto AS CdConcepto,ConceptoAut,NumAutoriza,IdUsuarioAut,UA.Usuario AS UsuarioAtzdo
,UA.IdGrupo AS NivelUsuario,UA.IdCargo AS CdCago,CA.Cargo AS CargoUsuario,A.IdEstacion AS CdEstacion,NitTercero,RazonSocial,pVehiculo,A.TipoDoc AS Tip_Doc,Documento,IdCiaDoc,FechaDoc,DocRef
,VrAprobado,OrigenAdd,Anulado,FecDev,A.Observacion AS Observ,A.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,A.IdUsuario AS IdUsuari,U.Usuario AS NomUsuario
,U.IdGrupo AS UsuNivel,U.IdCargo AS UsuIdCargo,CU.Cargo AS UsuNomCargo,Leyenda
 FROM Trn_Autoriza AS A INNER JOIN Sys_Autoriza AS C ON A.IdConcepto=C.IdConcepto
 INNER JOIN Terceros AS T ON A.NitTercero=T.IdTercero
 INNER JOIN EstadoDoc AS ED ON A.IdEstado=ED.IdEstado
 INNER JOIN Companias AS CN ON A.IdCia=CN.IdCia
 INNER JOIN adm_Usuarios AS UA ON A.IdUsuarioAut=UA.IdUsuario 
 INNER JOIN adm_Usuarios AS U ON A.IdUsuario=U.IdUsuario 
 INNER JOIN Sys_TiposDoc AS TD ON A.TipAut=TD.IdDoc
 LEFT JOIN Cargos AS CU ON U.IdCargo=CU.IdCargo
 LEFT JOIN Cargos AS CA ON UA.IdCargo=CA.IdCargo
 WHERE TipAut=@pmTipAut AND Autoriza BETWEEN @pmAutorizaIni AND @pmAutorizaFin AND A.IdCia=@pmIdCia
 ORDER BY A.IdCia,Autoriza
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpAcuCuentasAuxSan] @pmnAnno INT,@pmnMes INT,@pmIdCia CHAR(2)
,@pmnReg INT,@pmIdCuenta VARCHAR(16),@pmSaldoAnterior MONEY
 AS 
 UPDATE AcuCuentasAux SET  SaldoAnterior=@pmSaldoAnterior
 WHERE nAnno=@pmnAnno AND nMes=@pmnMes AND IdCia=@pmIdCia AND [Id]=@pmnReg
 AND IdCuenta=@pmIdCuenta
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsAcuCuentasAux_San] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmTipoAcum VARCHAR(4),@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 INSERT INTO AcuCuentasAux (nAnno,nMes,IdCia,nReg,IdCuenta,IdTercero,CodAgncia,IdCCosto,IdSubCos,SubAuxiliar,TipoAcum,SaldoAnterior,TotalDebitos,TotalCreditos,TipoAfiVehic)
 SELECT @pmnAnno,@pmnMes,S.IdCia,0,S.IdCuenta,S.IdTercero,S.CodAgncia,S.IdCCosto,S.IdSubCos,S.SubAuxiliar,S.TipoAcum,S.SaldoAnterior+S.TotalDebitos-S.TotalCreditos,0,0,0
 FROM AcuCuentasAux AS S WHERE S.nAnno=@pmAnnoAnt AND S.nMes=@pmMesAnt AND S.TipoAcum=@pmTipoAcum
 AND NOT EXISTS (SELECT * FROM AcuCuentasAux AS AC WHERE AC.nAnno=@pmnAnno AND AC.nMes=@pmnMes AND AC.TipoAcum=@pmTipoAcum
 AND AC.IdCia=S.IdCia AND AC.IdCuenta=S.IdCuenta AND AC.IdTercero=S.IdTercero AND AC.CodAgncia=S.CodAgncia AND AC.IdCCosto=S.IdCCosto AND AC.IdSubCos=S.IdSubCos
 AND AC.SubAuxiliar=S.SubAuxiliar AND AC.TipoAfiVehic=S.TipoAfiVehic)
 AND (@pmIdCia IS NULL OR S.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR S.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsAcuFisCuentas_San] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 INSERT INTO AcuFisCuentas (nAnno,nMes,IdCia,IdCuenta,SaldoAnterior,TotalDebitos,TotalCreditos)
 SELECT @pmnAnno,@pmnMes,S.IdCia,S.IdCuenta,S.SaldoAnterior+S.TotalDebitos-S.TotalCreditos,0,0
 FROM AcuFisCuentas AS S WHERE S.nAnno=@pmAnnoAnt AND S.nMes=@pmMesAnt 
 AND NOT EXISTS (SELECT * FROM AcuFisCuentas AS AC WHERE AC.nAnno=@pmnAnno AND AC.nMes=@pmnMes  
 AND AC.IdCia=S.IdCia AND AC.IdCuenta=S.IdCuenta)
 AND (@pmIdCia IS NULL OR S.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR S.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUpAcuFisCuentasSA] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 UPDATE AcuFisCuentas SET AcuFisCuentas.SaldoAnterior=ISNULL(SA.NuevoSaldo,0)
 FROM AcuFisCuentas INNER JOIN (SELECT nAnno,nMes,IdCia,IdCuenta,SaldoAnterior+TotalDebitos-TotalCreditos AS NuevoSaldo
 FROM AcuFisCuentas WHERE nAnno=@pmAnnoAnt AND nMes=@pmMesAnt) AS SA ON AcuFisCuentas.IdCia=SA.IdCia AND AcuFisCuentas.IdCuenta=SA.IdCuenta
 WHERE AcuFisCuentas.nAnno=@pmnAnno AND AcuFisCuentas.nMes=@pmnMes
 AND (@pmIdCia IS NULL OR AcuFisCuentas.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR AcuFisCuentas.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsAutoriza] @pmTipAut VARCHAR(3),@pmAutoriza INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmNumAutoriza INT,@pmIdUsuarioAut VARCHAR(11)
,@pmIdEstacion CHAR(2),@pmNitTercero VARCHAR(16),@pmpVehiculo VARCHAR(10),@pmTipoDoc VARCHAR(3),@pmDocumento INT,@pmIdCiaDoc CHAR(2),@pmFechaDoc SMALLDATETIME,@pmDocRef INT
,@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmVrAprobado MONEY,@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_Autoriza (TipAut,Autoriza,IdCia,Fecha,IdConcepto,NumAutoriza,IdUsuarioAut,IdEstacion,NitTercero,pVehiculo,TipoDoc,Documento,IdCiaDoc,FechaDoc,DocRef,OrigenAdd,Anulado,FecDev,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario,VrAprobado)
 VALUES (@pmTipAut,@pmAutoriza,@pmIdCia,@pmFecha,@pmIdConcepto,@pmNumAutoriza,@pmIdUsuarioAut,@pmIdEstacion,@pmNitTercero,@pmpVehiculo,@pmTipoDoc,@pmDocumento,@pmIdCiaDoc,@pmFechaDoc,@pmDocRef,@pmOrigenAdd
,@pmAnulado,@pmFecDev,@pmObservacion,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmVrAprobado)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUpAutoriza] @pmTipAut VARCHAR(3),@pmAutoriza INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmNumAutoriza INT,@pmIdUsuarioAut VARCHAR(11),@pmIdEstacion CHAR(2),@pmNitTercero VARCHAR(16)
,@pmpVehiculo VARCHAR(10),@pmTipoDoc VARCHAR(3),@pmDocumento INT,@pmIdCiaDoc CHAR(2),@pmFechaDoc SMALLDATETIME,@pmDocRef INT,@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250)
,@pmIdEstado VARCHAR(4),@pmVrAprobado MONEY,@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_Autoriza SET Fecha=@pmFecha,IdConcepto=@pmIdConcepto,NumAutoriza=@pmNumAutoriza,IdUsuarioAut=@pmIdUsuarioAut,IdEstacion=@pmIdEstacion,NitTercero=@pmNitTercero,pVehiculo=@pmpVehiculo
,TipoDoc=@pmTipoDoc,Documento=@pmDocumento,IdCiaDoc=@pmIdCiaDoc,FechaDoc=@pmFechaDoc,DocRef=@pmDocRef,Anulado=@pmAnulado,FecDev=@pmFecDev,Observacion=@pmObservacion
,VrAprobado=@pmVrAprobado,IdEstado=@pmIdEstado,FecUpdate=@pmFecUpdate
 WHERE TipAut=@pmTipAut AND Autoriza=@pmAutoriza AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryAutorizaDoc] @pmTipoDoc VARCHAR(3),@pmDocumento INT,@pmIdCiaDoc CHAR(2)
 AS 
 SELECT TipAut,Autoriza,IdCia,Fecha,IdConcepto,NumAutoriza,IdUsuarioAut,IdEstacion,NitTercero,pVehiculo,TipoDoc,Documento
,IdCiaDoc,FechaDoc,DocRef,VrAprobado,OrigenAdd,Anulado,FecDev,Observacion,IdEstado,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_Autoriza
 WHERE TipoDoc=@pmTipoDoc AND Documento=@pmDocumento AND IdCiaDoc LIKE ISNULL(@pmIdCiaDoc,'%%')
 ORDER BY IdCia,Autoriza
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryAutorizaNum] @pmNumAutoriza INT,@pmIdCia CHAR(2)=Null,@pmAnulado BIT=Null
 AS 
 SELECT TipAut,Autoriza,IdCia,Fecha,IdConcepto,NumAutoriza,IdUsuarioAut,IdEstacion,NitTercero,pVehiculo,TipoDoc,Documento
,IdCiaDoc,FechaDoc,DocRef,VrAprobado,OrigenAdd,Anulado,FecDev,Observacion,IdEstado,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_Autoriza
 WHERE NumAutoriza=@pmNumAutoriza AND IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND  (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 ORDER BY Autoriza
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryAutorizaLta]  @pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME,@pmAutorizaIni INT=Null,@pmAutorizaFin INT=Null
,@pmIdCia CHAR(2)=Null,@pmIdConcepto VARCHAR(4)=Null,@pmIdUsuarioAut VARCHAR(11)=Null,@pmNitTercero VARCHAR(16)=Null,@pmTipoDoc VARCHAR(3)=Null
,@pmIdEstado VARCHAR(4)=Null,@pmAnulado BIT=Null,@pmIdUsuario VARCHAR(11)=Null
 AS 
 SELECT Autoriza,IdCia,Fecha,A.IdConcepto AS CdConcepto,ConceptoAut,NumAutoriza,IdUsuarioAut,UA.Usuario AS UsuarioAtzdo,A.IdEstacion AS CdEstacion,NitTercero,RazonSocial,pVehiculo
,TipoDoc,Documento,IdCiaDoc,FechaDoc,DocRef,VrAprobado,OrigenAdd,Anulado,FecDev,A.Observacion AS Observ,A.IdEstado AS CdEstado,Estado,A.IdUsuario AS IdUsuari,U.Usuario AS NomUsuario,TimeSys,FecUpdate,IdCiaCrea
 FROM Trn_Autoriza AS A INNER JOIN Sys_Autoriza AS C ON A.IdConcepto=C.IdConcepto
 INNER JOIN Terceros AS T ON A.NitTercero=T.IdTercero
 INNER JOIN EstadoDoc AS ED ON A.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS UA ON A.IdUsuarioAut=UA.IdUsuario 
 INNER JOIN adm_Usuarios AS U ON A.IdUsuario=U.IdUsuario 
 WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND Autoriza BETWEEN ISNULL(@pmAutorizaIni,0) AND ISNULL(@pmAutorizaFin,2147483647)
 AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND A.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') 
 AND IdUsuarioAut LIKE ISNULL(@pmIdUsuarioAut,'%') AND NitTercero LIKE ISNULL(@pmNitTercero,'%')
 AND TipoDoc LIKE ISNULL(@pmTipoDoc,'%')  AND A.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 AND A.IdUsuario LIKE ISNULL(@pmIdUsuario,'%')
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 ORDER BY IdCia,Autoriza
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryAutoriza] @pmTipAut VARCHAR(3),@pmAutoriza INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipAut,Autoriza,IdCia,Fecha,IdConcepto,NumAutoriza,IdUsuarioAut,IdEstacion,NitTercero,pVehiculo,TipoDoc,Documento
,IdCiaDoc,FechaDoc,DocRef,OrigenAdd,Anulado,FecDev,Observacion,IdEstado,VrAprobado,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_Autoriza
 WHERE TipAut=@pmTipAut AND Autoriza=@pmAutoriza AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUpAcuFisCuentasAgeSA] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmTipoAcum CHAR(2),@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 UPDATE AcuFisCuentasAge SET AcuFisCuentasAge.SaldoAnterior=ISNULL(SA.NuevoSaldo,0)
 FROM AcuFisCuentasAge INNER JOIN (SELECT nAnno,nMes,IdCia,IdCuenta,IdTercero,CodAgncia,TipoAcum,SaldoAnterior+TotalDebitos-TotalCreditos AS NuevoSaldo
 FROM AcuFisCuentasAge WHERE nAnno=@pmAnnoAnt AND nMes=@pmMesAnt AND TipoAcum=@pmTipoAcum) AS SA ON AcuFisCuentasAge.IdCia=SA.IdCia AND AcuFisCuentasAge.IdCuenta=SA.IdCuenta
 AND AcuFisCuentasAge.IdTercero=SA.IdTercero AND AcuFisCuentasAge.CodAgncia=SA.CodAgncia AND AcuFisCuentasAge.TipoAcum=SA.TipoAcum
 WHERE AcuFisCuentasAge.nAnno=@pmnAnno AND AcuFisCuentasAge.nMes=@pmnMes AND AcuFisCuentasAge.TipoAcum=@pmTipoAcum
 AND (@pmIdCia IS NULL OR AcuFisCuentasAge.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR AcuFisCuentasAge.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsAcuFisCuentasAge_San] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmTipoAcum CHAR(2),@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 INSERT INTO AcuFisCuentasAge (nAnno,nMes,IdCia,IdCuenta,IdTercero,CodAgncia,TipoAcum,SaldoAnterior,TotalDebitos,TotalCreditos)
 SELECT @pmnAnno,@pmnMes,S.IdCia,S.IdCuenta,S.IdTercero,S.CodAgncia,S.TipoAcum,S.SaldoAnterior+S.TotalDebitos-S.TotalCreditos,0,0
 FROM AcuFisCuentasAge AS S WHERE S.nAnno=@pmAnnoAnt AND S.nMes=@pmMesAnt AND S.TipoAcum=@pmTipoAcum
 AND NOT EXISTS (SELECT * FROM AcuFisCuentasAge AS AC WHERE AC.nAnno=@pmnAnno AND AC.nMes=@pmnMes AND AC.TipoAcum=@pmTipoAcum
 AND AC.IdCia=S.IdCia AND AC.IdCuenta=S.IdCuenta AND AC.IdTercero=S.IdTercero AND AC.CodAgncia=S.CodAgncia)
 AND (@pmIdCia IS NULL OR S.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR S.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsAcuNiifCuentas_San] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 INSERT INTO AcuNiifCuentas (nAnno,nMes,IdCia,IdCuenta,SaldoAnterior,TotalDebitos,TotalCreditos)
 SELECT @pmnAnno,@pmnMes,S.IdCia,S.IdCuenta,S.SaldoAnterior+S.TotalDebitos-S.TotalCreditos,0,0
 FROM AcuNiifCuentas AS S WHERE S.nAnno=@pmAnnoAnt AND S.nMes=@pmMesAnt 
 AND NOT EXISTS (SELECT * FROM AcuNiifCuentas AS AC WHERE AC.nAnno=@pmnAnno AND AC.nMes=@pmnMes  
 AND AC.IdCia=S.IdCia AND AC.IdCuenta=S.IdCuenta)
 AND (@pmIdCia IS NULL OR S.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR S.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUpAcuNiifCuentasSA] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 UPDATE AcuNiifCuentas SET AcuNiifCuentas.SaldoAnterior=ISNULL(SA.NuevoSaldo,0)
 FROM AcuNiifCuentas INNER JOIN (SELECT nAnno,nMes,IdCia,IdCuenta,SaldoAnterior+TotalDebitos-TotalCreditos AS NuevoSaldo
 FROM AcuNiifCuentas WHERE nAnno=@pmAnnoAnt AND nMes=@pmMesAnt) AS SA ON AcuNiifCuentas.IdCia=SA.IdCia AND AcuNiifCuentas.IdCuenta=SA.IdCuenta
 WHERE AcuNiifCuentas.nAnno=@pmnAnno AND AcuNiifCuentas.nMes=@pmnMes
 AND (@pmIdCia IS NULL OR AcuNiifCuentas.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR AcuNiifCuentas.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsAcuCuentas_San] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 INSERT INTO AcuCuentas (nAnno,nMes,IdCia,IdCuenta,SaldoAnterior,TotalDebitos,TotalCreditos)
 SELECT @pmnAnno,@pmnMes,S.IdCia,S.IdCuenta,S.SaldoAnterior+S.TotalDebitos-S.TotalCreditos,0,0
 FROM AcuCuentas AS S WHERE S.nAnno=@pmAnnoAnt AND S.nMes=@pmMesAnt 
 AND NOT EXISTS (SELECT * FROM AcuCuentas AS AC WHERE AC.nAnno=@pmnAnno AND AC.nMes=@pmnMes  
 AND AC.IdCia=S.IdCia AND AC.IdCuenta=S.IdCuenta)
 AND (@pmIdCia IS NULL OR S.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR S.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUpAcuCuentasSA] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 UPDATE AcuCuentas SET AcuCuentas.SaldoAnterior=ISNULL(SA.NuevoSaldo,0)
 FROM AcuCuentas INNER JOIN (SELECT nAnno,nMes,IdCia,IdCuenta,SaldoAnterior+TotalDebitos-TotalCreditos AS NuevoSaldo
 FROM AcuCuentas WHERE nAnno=@pmAnnoAnt AND nMes=@pmMesAnt) AS SA ON AcuCuentas.IdCia=SA.IdCia AND AcuCuentas.IdCuenta=SA.IdCuenta
 WHERE AcuCuentas.nAnno=@pmnAnno AND AcuCuentas.nMes=@pmnMes
 AND (@pmIdCia IS NULL OR AcuCuentas.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR AcuCuentas.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUpAcuNiifCuentasAgeSA] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmTipoAcum CHAR(2),@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 UPDATE AcuNiifCuentasAge SET AcuNiifCuentasAge.SaldoAnterior=ISNULL(SA.NuevoSaldo,0)
 FROM AcuNiifCuentasAge INNER JOIN (SELECT nAnno,nMes,IdCia,IdCuenta,IdTercero,CodAgncia,TipoAcum,SaldoAnterior+TotalDebitos-TotalCreditos AS NuevoSaldo
 FROM AcuNiifCuentasAge WHERE nAnno=@pmAnnoAnt AND nMes=@pmMesAnt AND TipoAcum=@pmTipoAcum) AS SA ON AcuNiifCuentasAge.IdCia=SA.IdCia AND AcuNiifCuentasAge.IdCuenta=SA.IdCuenta
 AND AcuNiifCuentasAge.IdTercero=SA.IdTercero AND AcuNiifCuentasAge.CodAgncia=SA.CodAgncia AND AcuNiifCuentasAge.TipoAcum=SA.TipoAcum
 WHERE AcuNiifCuentasAge.nAnno=@pmnAnno AND AcuNiifCuentasAge.nMes=@pmnMes AND AcuNiifCuentasAge.TipoAcum=@pmTipoAcum
 AND (@pmIdCia IS NULL OR AcuNiifCuentasAge.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR AcuNiifCuentasAge.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsAcuNiifCuentasAge_San] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmTipoAcum CHAR(2),@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 INSERT INTO AcuNiifCuentasAge (nAnno,nMes,IdCia,IdCuenta,IdTercero,CodAgncia,TipoAcum,SaldoAnterior,TotalDebitos,TotalCreditos)
 SELECT @pmnAnno,@pmnMes,S.IdCia,S.IdCuenta,S.IdTercero,S.CodAgncia,S.TipoAcum,S.SaldoAnterior+S.TotalDebitos-S.TotalCreditos,0,0
 FROM AcuNiifCuentasAge AS S WHERE S.nAnno=@pmAnnoAnt AND S.nMes=@pmMesAnt AND S.TipoAcum=@pmTipoAcum
 AND NOT EXISTS (SELECT * FROM AcuNiifCuentasAge AS AC WHERE AC.nAnno=@pmnAnno AND AC.nMes=@pmnMes AND AC.TipoAcum=@pmTipoAcum
 AND AC.IdCia=S.IdCia AND AC.IdCuenta=S.IdCuenta AND AC.IdTercero=S.IdTercero AND AC.CodAgncia=S.CodAgncia)
 AND (@pmIdCia IS NULL OR S.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR S.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUpAcuCuentasAgeSA] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmTipoAcum CHAR(2),@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 UPDATE AcuCuentasAge SET AcuCuentasAge.SaldoAnterior=ISNULL(SA.NuevoSaldo,0)
 FROM AcuCuentasAge INNER JOIN (SELECT nAnno,nMes,IdCia,IdCuenta,IdTercero,CodAgncia,TipoAcum,SaldoAnterior+TotalDebitos-TotalCreditos AS NuevoSaldo
 FROM AcuCuentasAge WHERE nAnno=@pmAnnoAnt AND nMes=@pmMesAnt AND TipoAcum=@pmTipoAcum) AS SA ON AcuCuentasAge.IdCia=SA.IdCia AND AcuCuentasAge.IdCuenta=SA.IdCuenta
 AND AcuCuentasAge.IdTercero=SA.IdTercero AND AcuCuentasAge.CodAgncia=SA.CodAgncia AND AcuCuentasAge.TipoAcum=SA.TipoAcum
 WHERE AcuCuentasAge.nAnno=@pmnAnno AND AcuCuentasAge.nMes=@pmnMes AND AcuCuentasAge.TipoAcum=@pmTipoAcum
 AND (@pmIdCia IS NULL OR AcuCuentasAge.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR AcuCuentasAge.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsAcuCuentasAge_San] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmTipoAcum CHAR(2),@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 INSERT INTO AcuCuentasAge (nAnno,nMes,IdCia,IdCuenta,IdTercero,CodAgncia,TipoAcum,SaldoAnterior,TotalDebitos,TotalCreditos)
 SELECT @pmnAnno,@pmnMes,S.IdCia,S.IdCuenta,S.IdTercero,S.CodAgncia,S.TipoAcum,S.SaldoAnterior+S.TotalDebitos-S.TotalCreditos,0,0
 FROM AcuCuentasAge AS S WHERE S.nAnno=@pmAnnoAnt AND S.nMes=@pmMesAnt AND S.TipoAcum=@pmTipoAcum
 AND NOT EXISTS (SELECT * FROM AcuCuentasAge AS AC WHERE AC.nAnno=@pmnAnno AND AC.nMes=@pmnMes AND AC.TipoAcum=@pmTipoAcum
 AND AC.IdCia=S.IdCia AND AC.IdCuenta=S.IdCuenta AND AC.IdTercero=S.IdTercero AND AC.CodAgncia=S.CodAgncia)
 AND (@pmIdCia IS NULL OR S.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR S.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsAcuNiifCuentasAux_San] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmTipoAcum VARCHAR(4),@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 INSERT INTO AcuNiifCuentasAux (nAnno,nMes,IdCia,nReg,IdCuenta,IdTercero,CodAgncia,IdCCosto,IdSubCos,SubAuxiliar,TipoAcum,SaldoAnterior,TotalDebitos,TotalCreditos,TipoAfiVehic)
 SELECT @pmnAnno,@pmnMes,S.IdCia,0,S.IdCuenta,S.IdTercero,S.CodAgncia,S.IdCCosto,S.IdSubCos,S.SubAuxiliar,S.TipoAcum,S.SaldoAnterior+S.TotalDebitos-S.TotalCreditos,0,0,0
 FROM AcuNiifCuentasAux AS S WHERE S.nAnno=@pmAnnoAnt AND S.nMes=@pmMesAnt AND S.TipoAcum=@pmTipoAcum
 AND NOT EXISTS (SELECT * FROM AcuNiifCuentasAux AS AC WHERE AC.nAnno=@pmnAnno AND AC.nMes=@pmnMes AND AC.TipoAcum=@pmTipoAcum
 AND AC.IdCia=S.IdCia AND AC.IdCuenta=S.IdCuenta AND AC.IdTercero=S.IdTercero AND AC.CodAgncia=S.CodAgncia AND AC.IdCCosto=S.IdCCosto AND AC.IdSubCos=S.IdSubCos
 AND AC.SubAuxiliar=S.SubAuxiliar AND AC.TipoAfiVehic=S.TipoAfiVehic)
 AND (@pmIdCia IS NULL OR S.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR S.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsAcuFisCuentasCc_San] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmTipoAcum CHAR(2),@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 INSERT INTO AcuFisCuentasCc (nAnno,nMes,IdCia,IdCuenta,IdCCosto,IdSubCos,TipoAcum,SaldoAnterior,TotalDebitos,TotalCreditos)
 SELECT @pmnAnno,@pmnMes,S.IdCia,S.IdCuenta,S.IdCCosto,S.IdSubCos,S.TipoAcum,S.SaldoAnterior+S.TotalDebitos-S.TotalCreditos,0,0
 FROM AcuFisCuentasCc AS S WHERE S.nAnno=@pmAnnoAnt AND S.nMes=@pmMesAnt AND S.TipoAcum=@pmTipoAcum
 AND NOT EXISTS (SELECT * FROM AcuFisCuentasCc AS AC WHERE AC.nAnno=@pmnAnno AND AC.nMes=@pmnMes AND AC.TipoAcum=@pmTipoAcum
 AND AC.IdCia=S.IdCia AND AC.IdCuenta=S.IdCuenta AND AC.IdCCosto=S.IdCCosto AND AC.IdSubCos=S.IdSubCos)
 AND (@pmIdCia IS NULL OR S.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR S.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUpAcuFisCuentasCcSA] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmTipoAcum CHAR(2),@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 UPDATE AcuFisCuentasCc SET AcuFisCuentasCc.SaldoAnterior=ISNULL(SA.NuevoSaldo,0)
 FROM AcuFisCuentasCc INNER JOIN (SELECT nAnno,nMes,IdCia,IdCuenta,IdCCosto,IdSubCos,TipoAcum,SaldoAnterior+TotalDebitos-TotalCreditos AS NuevoSaldo
 FROM AcuFisCuentasCc WHERE nAnno=@pmAnnoAnt AND nMes=@pmMesAnt AND TipoAcum=@pmTipoAcum) AS SA ON AcuFisCuentasCc.IdCia=SA.IdCia AND AcuFisCuentasCc.IdCuenta=SA.IdCuenta
 AND AcuFisCuentasCc.IdCCosto=SA.IdCCosto AND AcuFisCuentasCc.IdSubCos=SA.IdSubCos AND AcuFisCuentasCc.TipoAcum=SA.TipoAcum
 WHERE AcuFisCuentasCc.nAnno=@pmnAnno AND AcuFisCuentasCc.nMes=@pmnMes AND AcuFisCuentasCc.TipoAcum=@pmTipoAcum
 AND (@pmIdCia IS NULL OR AcuFisCuentasCc.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR AcuFisCuentasCc.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsAcuFisCuentasAux_San] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmTipoAcum VARCHAR(4),@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 INSERT INTO AcuFisCuentasAux (nAnno,nMes,IdCia,nReg,IdCuenta,IdTercero,CodAgncia,IdCCosto,IdSubCos,SubAuxiliar,TipoAcum,SaldoAnterior,TotalDebitos,TotalCreditos,TipoAfiVehic)
 SELECT @pmnAnno,@pmnMes,S.IdCia,0,S.IdCuenta,S.IdTercero,S.CodAgncia,S.IdCCosto,S.IdSubCos,S.SubAuxiliar,S.TipoAcum,S.SaldoAnterior+S.TotalDebitos-S.TotalCreditos,0,0,0
 FROM AcuFisCuentasAux AS S WHERE S.nAnno=@pmAnnoAnt AND S.nMes=@pmMesAnt AND S.TipoAcum=@pmTipoAcum
 AND NOT EXISTS (SELECT * FROM AcuFisCuentasAux AS AC WHERE AC.nAnno=@pmnAnno AND AC.nMes=@pmnMes AND AC.TipoAcum=@pmTipoAcum
 AND AC.IdCia=S.IdCia AND AC.IdCuenta=S.IdCuenta AND AC.IdTercero=S.IdTercero AND AC.CodAgncia=S.CodAgncia AND AC.IdCCosto=S.IdCCosto AND AC.IdSubCos=S.IdSubCos
 AND AC.SubAuxiliar=S.SubAuxiliar AND AC.TipoAfiVehic=S.TipoAfiVehic)
 AND (@pmIdCia IS NULL OR S.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR S.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsAcuNiifCuentasCc_San] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmTipoAcum CHAR(2),@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 INSERT INTO AcuNiifCuentasCc (nAnno,nMes,IdCia,IdCuenta,IdCCosto,IdSubCos,TipoAcum,SaldoAnterior,TotalDebitos,TotalCreditos)
 SELECT @pmnAnno,@pmnMes,S.IdCia,S.IdCuenta,S.IdCCosto,S.IdSubCos,S.TipoAcum,S.SaldoAnterior+S.TotalDebitos-S.TotalCreditos,0,0
 FROM AcuNiifCuentasCc AS S WHERE S.nAnno=@pmAnnoAnt AND S.nMes=@pmMesAnt AND S.TipoAcum=@pmTipoAcum
 AND NOT EXISTS (SELECT * FROM AcuNiifCuentasCc AS AC WHERE AC.nAnno=@pmnAnno AND AC.nMes=@pmnMes AND AC.TipoAcum=@pmTipoAcum
 AND AC.IdCia=S.IdCia AND AC.IdCuenta=S.IdCuenta AND AC.IdCCosto=S.IdCCosto AND AC.IdSubCos=S.IdSubCos)
 AND (@pmIdCia IS NULL OR S.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR S.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUpAcuNiifCuentasCcSA] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmTipoAcum CHAR(2),@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 UPDATE AcuNiifCuentasCc SET AcuNiifCuentasCc.SaldoAnterior=ISNULL(SA.NuevoSaldo,0)
 FROM AcuNiifCuentasCc INNER JOIN (SELECT nAnno,nMes,IdCia,IdCuenta,IdCCosto,IdSubCos,TipoAcum,SaldoAnterior+TotalDebitos-TotalCreditos AS NuevoSaldo
 FROM AcuNiifCuentasCc WHERE nAnno=@pmAnnoAnt AND nMes=@pmMesAnt AND TipoAcum=@pmTipoAcum) AS SA ON AcuNiifCuentasCc.IdCia=SA.IdCia AND AcuNiifCuentasCc.IdCuenta=SA.IdCuenta
 AND AcuNiifCuentasCc.IdCCosto=SA.IdCCosto AND AcuNiifCuentasCc.IdSubCos=SA.IdSubCos AND AcuNiifCuentasCc.TipoAcum=SA.TipoAcum
 WHERE AcuNiifCuentasCc.nAnno=@pmnAnno AND AcuNiifCuentasCc.nMes=@pmnMes AND AcuNiifCuentasCc.TipoAcum=@pmTipoAcum
 AND (@pmIdCia IS NULL OR AcuNiifCuentasCc.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR AcuNiifCuentasCc.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsAcuCuentasCc_San] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmTipoAcum CHAR(2),@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 INSERT INTO AcuCuentasCc (nAnno,nMes,IdCia,IdCuenta,IdCCosto,IdSubCos,TipoAcum,SaldoAnterior,TotalDebitos,TotalCreditos)
 SELECT @pmnAnno,@pmnMes,S.IdCia,S.IdCuenta,S.IdCCosto,S.IdSubCos,S.TipoAcum,S.SaldoAnterior+S.TotalDebitos-S.TotalCreditos,0,0
 FROM AcuCuentasCc AS S WHERE S.nAnno=@pmAnnoAnt AND S.nMes=@pmMesAnt AND S.TipoAcum=@pmTipoAcum
 AND NOT EXISTS (SELECT * FROM AcuCuentasCc AS AC WHERE AC.nAnno=@pmnAnno AND AC.nMes=@pmnMes AND AC.TipoAcum=@pmTipoAcum
 AND AC.IdCia=S.IdCia AND AC.IdCuenta=S.IdCuenta AND AC.IdCCosto=S.IdCCosto AND AC.IdSubCos=S.IdSubCos)
 AND (@pmIdCia IS NULL OR S.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR S.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUpAcuCuentasCcSA] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmTipoAcum CHAR(2),@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 UPDATE AcuCuentasCc SET AcuCuentasCc.SaldoAnterior=ISNULL(SA.NuevoSaldo,0)
 FROM AcuCuentasCc INNER JOIN (SELECT nAnno,nMes,IdCia,IdCuenta,IdCCosto,IdSubCos,TipoAcum,SaldoAnterior+TotalDebitos-TotalCreditos AS NuevoSaldo
 FROM AcuCuentasCc WHERE nAnno=@pmAnnoAnt AND nMes=@pmMesAnt AND TipoAcum=@pmTipoAcum) AS SA ON AcuCuentasCc.IdCia=SA.IdCia AND AcuCuentasCc.IdCuenta=SA.IdCuenta
 AND AcuCuentasCc.IdCCosto=SA.IdCCosto AND AcuCuentasCc.IdSubCos=SA.IdSubCos AND AcuCuentasCc.TipoAcum=SA.TipoAcum
 WHERE AcuCuentasCc.nAnno=@pmnAnno AND AcuCuentasCc.nMes=@pmnMes AND AcuCuentasCc.TipoAcum=@pmTipoAcum
 AND (@pmIdCia IS NULL OR AcuCuentasCc.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR AcuCuentasCc.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUpAcuFisCuentasNitSA] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 UPDATE AcuFisCuentasNit SET AcuFisCuentasNit.SaldoAnterior=ISNULL(SA.NuevoSaldo,0)
 FROM AcuFisCuentasNit INNER JOIN (SELECT nAnno,nMes,IdCia,IdCuenta,IdTercero,SaldoAnterior+TotalDebitos-TotalCreditos AS NuevoSaldo
 FROM AcuFisCuentasNit WHERE nAnno=@pmAnnoAnt AND nMes=@pmMesAnt) AS SA ON AcuFisCuentasNit.IdCia=SA.IdCia AND AcuFisCuentasNit.IdCuenta=SA.IdCuenta
 AND AcuFisCuentasNit.IdTercero=SA.IdTercero
 WHERE AcuFisCuentasNit.nAnno=@pmnAnno AND AcuFisCuentasNit.nMes=@pmnMes
 AND (@pmIdCia IS NULL OR AcuFisCuentasNit.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR AcuFisCuentasNit.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsAcuFisCuentasNit_San] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 INSERT INTO AcuFisCuentasNit (nAnno,nMes,IdCia,IdCuenta,IdTercero,SaldoAnterior,TotalDebitos,TotalCreditos)
 SELECT @pmnAnno,@pmnMes,S.IdCia,S.IdCuenta,S.IdTercero,S.SaldoAnterior+S.TotalDebitos-S.TotalCreditos,0,0
 FROM AcuFisCuentasNit AS S WHERE S.nAnno=@pmAnnoAnt AND S.nMes=@pmMesAnt 
 AND NOT EXISTS (SELECT * FROM AcuFisCuentasNit AS AC WHERE AC.nAnno=@pmnAnno AND AC.nMes=@pmnMes  
 AND AC.IdCia=S.IdCia AND AC.IdCuenta=S.IdCuenta AND AC.IdTercero=S.IdTercero)
 AND (@pmIdCia IS NULL OR S.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR S.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsAcuFisCuentasVeh_San] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 INSERT INTO AcuFisCuentasVeh (nAnno,nMes,IdCia,IdCuenta,IdVehiculo,SaldoAnterior,TotalDebitos,TotalCreditos)
 SELECT @pmnAnno,@pmnMes,S.IdCia,S.IdCuenta,S.IdVehiculo,S.SaldoAnterior+S.TotalDebitos-S.TotalCreditos,0,0
 FROM AcuFisCuentasVeh AS S WHERE S.nAnno=@pmAnnoAnt AND S.nMes=@pmMesAnt 
 AND NOT EXISTS (SELECT * FROM AcuFisCuentasVeh AS AC WHERE AC.nAnno=@pmnAnno AND AC.nMes=@pmnMes  
 AND AC.IdCia=S.IdCia AND AC.IdCuenta=S.IdCuenta AND AC.IdVehiculo=S.IdVehiculo)
 AND (@pmIdCia IS NULL OR S.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR S.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUpAcuFisCuentasVehSA] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 UPDATE AcuFisCuentasVeh SET AcuFisCuentasVeh.SaldoAnterior=ISNULL(SA.NuevoSaldo,0)
 FROM AcuFisCuentasVeh INNER JOIN (SELECT nAnno,nMes,IdCia,IdCuenta,IdVehiculo,SaldoAnterior+TotalDebitos-TotalCreditos AS NuevoSaldo
 FROM AcuFisCuentasVeh WHERE nAnno=@pmAnnoAnt AND nMes=@pmMesAnt) AS SA ON AcuFisCuentasVeh.IdCia=SA.IdCia AND AcuFisCuentasVeh.IdCuenta=SA.IdCuenta
 AND AcuFisCuentasVeh.IdVehiculo=SA.IdVehiculo
 WHERE AcuFisCuentasVeh.nAnno=@pmnAnno AND AcuFisCuentasVeh.nMes=@pmnMes
 AND (@pmIdCia IS NULL OR AcuFisCuentasVeh.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR AcuFisCuentasVeh.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUpAcuNiifCuentasNitSA] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 UPDATE AcuNiifCuentasNit SET AcuNiifCuentasNit.SaldoAnterior=ISNULL(SA.NuevoSaldo,0)
 FROM AcuNiifCuentasNit INNER JOIN (SELECT nAnno,nMes,IdCia,IdCuenta,IdTercero,SaldoAnterior+TotalDebitos-TotalCreditos AS NuevoSaldo
 FROM AcuNiifCuentasNit WHERE nAnno=@pmAnnoAnt AND nMes=@pmMesAnt) AS SA ON AcuNiifCuentasNit.IdCia=SA.IdCia AND AcuNiifCuentasNit.IdCuenta=SA.IdCuenta
 AND AcuNiifCuentasNit.IdTercero=SA.IdTercero
 WHERE AcuNiifCuentasNit.nAnno=@pmnAnno AND AcuNiifCuentasNit.nMes=@pmnMes
 AND (@pmIdCia IS NULL OR AcuNiifCuentasNit.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR AcuNiifCuentasNit.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsAcuNiifCuentasNit_San] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 INSERT INTO AcuNiifCuentasNit (nAnno,nMes,IdCia,IdCuenta,IdTercero,SaldoAnterior,TotalDebitos,TotalCreditos)
 SELECT @pmnAnno,@pmnMes,S.IdCia,S.IdCuenta,S.IdTercero,S.SaldoAnterior+S.TotalDebitos-S.TotalCreditos,0,0
 FROM AcuNiifCuentasNit AS S WHERE S.nAnno=@pmAnnoAnt AND S.nMes=@pmMesAnt 
 AND NOT EXISTS (SELECT * FROM AcuNiifCuentasNit AS AC WHERE AC.nAnno=@pmnAnno AND AC.nMes=@pmnMes  
 AND AC.IdCia=S.IdCia AND AC.IdCuenta=S.IdCuenta AND AC.IdTercero=S.IdTercero)
 AND (@pmIdCia IS NULL OR S.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR S.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsAcuCuentasNit_San] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 INSERT INTO AcuCuentasNit (nAnno,nMes,IdCia,IdCuenta,IdTercero,SaldoAnterior,TotalDebitos,TotalCreditos)
 SELECT @pmnAnno,@pmnMes,S.IdCia,S.IdCuenta,S.IdTercero,S.SaldoAnterior+S.TotalDebitos-S.TotalCreditos,0,0
 FROM AcuCuentasNit AS S WHERE S.nAnno=@pmAnnoAnt AND S.nMes=@pmMesAnt 
 AND NOT EXISTS (SELECT * FROM AcuCuentasNit AS AC WHERE AC.nAnno=@pmnAnno AND AC.nMes=@pmnMes  
 AND AC.IdCia=S.IdCia AND AC.IdCuenta=S.IdCuenta AND AC.IdTercero=S.IdTercero)
 AND (@pmIdCia IS NULL OR S.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR S.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUpAcuCuentasNitSA] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 UPDATE AcuCuentasNit SET AcuCuentasNit.SaldoAnterior=ISNULL(SA.NuevoSaldo,0)
 FROM AcuCuentasNit INNER JOIN (SELECT nAnno,nMes,IdCia,IdCuenta,IdTercero,SaldoAnterior+TotalDebitos-TotalCreditos AS NuevoSaldo
 FROM AcuCuentasNit WHERE nAnno=@pmAnnoAnt AND nMes=@pmMesAnt) AS SA ON AcuCuentasNit.IdCia=SA.IdCia AND AcuCuentasNit.IdCuenta=SA.IdCuenta
 AND AcuCuentasNit.IdTercero=SA.IdTercero
 WHERE AcuCuentasNit.nAnno=@pmnAnno AND AcuCuentasNit.nMes=@pmnMes
 AND (@pmIdCia IS NULL OR AcuCuentasNit.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR AcuCuentasNit.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsAcuCuentasVeh_San] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 INSERT INTO AcuCuentasVeh (nAnno,nMes,IdCia,IdCuenta,IdVehiculo,SaldoAnterior,TotalDebitos,TotalCreditos)
 SELECT @pmnAnno,@pmnMes,S.IdCia,S.IdCuenta,S.IdVehiculo,S.SaldoAnterior+S.TotalDebitos-S.TotalCreditos,0,0
 FROM AcuCuentasVeh AS S WHERE S.nAnno=@pmAnnoAnt AND S.nMes=@pmMesAnt 
 AND NOT EXISTS (SELECT * FROM AcuCuentasVeh AS AC WHERE AC.nAnno=@pmnAnno AND AC.nMes=@pmnMes  
 AND AC.IdCia=S.IdCia AND AC.IdCuenta=S.IdCuenta AND AC.IdVehiculo=S.IdVehiculo)
 AND (@pmIdCia IS NULL OR S.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR S.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUpAcuCuentasVehSA] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 UPDATE AcuCuentasVeh SET AcuCuentasVeh.SaldoAnterior=ISNULL(SA.NuevoSaldo,0)
 FROM AcuCuentasVeh INNER JOIN (SELECT nAnno,nMes,IdCia,IdCuenta,IdVehiculo,SaldoAnterior+TotalDebitos-TotalCreditos AS NuevoSaldo
 FROM AcuCuentasVeh WHERE nAnno=@pmAnnoAnt AND nMes=@pmMesAnt) AS SA ON AcuCuentasVeh.IdCia=SA.IdCia AND AcuCuentasVeh.IdCuenta=SA.IdCuenta
 AND AcuCuentasVeh.IdVehiculo=SA.IdVehiculo
 WHERE AcuCuentasVeh.nAnno=@pmnAnno AND AcuCuentasVeh.nMes=@pmnMes
 AND (@pmIdCia IS NULL OR AcuCuentasVeh.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR AcuCuentasVeh.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsAcuFisCuentasVeht_San] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmTipoAcum VARCHAR(3),@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 INSERT INTO AcuFisCuentasVeht (nAnno,nMes,IdCia,IdCuenta,IdTercero,IdVehiculo,TipoAfiVehic,TipoAcum,SaldoAnterior,TotalDebitos,TotalCreditos)
 SELECT @pmnAnno,@pmnMes,S.IdCia,S.IdCuenta,S.IdTercero,S.IdVehiculo,S.TipoAfiVehic,S.TipoAcum,S.SaldoAnterior+S.TotalDebitos-S.TotalCreditos,0,0
 FROM AcuFisCuentasVeht AS S WHERE S.nAnno=@pmAnnoAnt AND S.nMes=@pmMesAnt AND S.TipoAcum=@pmTipoAcum
 AND NOT EXISTS (SELECT * FROM AcuFisCuentasVeht AS AC WHERE AC.nAnno=@pmnAnno AND AC.nMes=@pmnMes AND AC.TipoAcum=@pmTipoAcum
 AND AC.IdCia=S.IdCia AND AC.IdCuenta=S.IdCuenta AND AC.IdTercero=S.IdTercero AND AC.IdVehiculo=S.IdVehiculo AND AC.TipoAfiVehic=S.TipoAfiVehic)
 AND (@pmIdCia IS NULL OR S.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR S.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUpAcuFisCuentasVehtSA] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmTipoAcum VARCHAR(3),@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 UPDATE AcuFisCuentasVeht SET AcuFisCuentasVeht.SaldoAnterior=ISNULL(SA.NuevoSaldo,0)
 FROM AcuFisCuentasVeht INNER JOIN (SELECT nAnno,nMes,IdCia,IdCuenta,IdTercero,IdVehiculo,TipoAfiVehic,TipoAcum,SaldoAnterior+TotalDebitos-TotalCreditos AS NuevoSaldo
 FROM AcuFisCuentasVeht WHERE nAnno=@pmAnnoAnt AND nMes=@pmMesAnt AND TipoAcum=@pmTipoAcum) AS SA ON AcuFisCuentasVeht.IdCia=SA.IdCia AND AcuFisCuentasVeht.IdCuenta=SA.IdCuenta
 AND AcuFisCuentasVeht.IdTercero=SA.IdTercero AND AcuFisCuentasVeht.IdVehiculo=SA.IdVehiculo AND AcuFisCuentasVeht.TipoAfiVehic=SA.TipoAfiVehic AND AcuFisCuentasVeht.TipoAcum=SA.TipoAcum
 WHERE AcuFisCuentasVeht.nAnno=@pmnAnno AND AcuFisCuentasVeht.nMes=@pmnMes AND AcuFisCuentasVeht.TipoAcum=@pmTipoAcum
 AND (@pmIdCia IS NULL OR AcuFisCuentasVeht.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR AcuFisCuentasVeht.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUpAcuNiifCuentasVehSA] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 UPDATE AcuNiifCuentasVeh SET AcuNiifCuentasVeh.SaldoAnterior=ISNULL(SA.NuevoSaldo,0)
 FROM AcuNiifCuentasVeh INNER JOIN (SELECT nAnno,nMes,IdCia,IdCuenta,IdVehiculo,SaldoAnterior+TotalDebitos-TotalCreditos AS NuevoSaldo
 FROM AcuNiifCuentasVeh WHERE nAnno=@pmAnnoAnt AND nMes=@pmMesAnt) AS SA ON AcuNiifCuentasVeh.IdCia=SA.IdCia AND AcuNiifCuentasVeh.IdCuenta=SA.IdCuenta
 AND AcuNiifCuentasVeh.IdVehiculo=SA.IdVehiculo
 WHERE AcuNiifCuentasVeh.nAnno=@pmnAnno AND AcuNiifCuentasVeh.nMes=@pmnMes
 AND (@pmIdCia IS NULL OR AcuNiifCuentasVeh.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR AcuNiifCuentasVeh.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsAcuNiifCuentasVeh_San] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 INSERT INTO AcuNiifCuentasVeh (nAnno,nMes,IdCia,IdCuenta,IdVehiculo,SaldoAnterior,TotalDebitos,TotalCreditos)
 SELECT @pmnAnno,@pmnMes,S.IdCia,S.IdCuenta,S.IdVehiculo,S.SaldoAnterior+S.TotalDebitos-S.TotalCreditos,0,0
 FROM AcuNiifCuentasVeh AS S WHERE S.nAnno=@pmAnnoAnt AND S.nMes=@pmMesAnt 
 AND NOT EXISTS (SELECT * FROM AcuNiifCuentasVeh AS AC WHERE AC.nAnno=@pmnAnno AND AC.nMes=@pmnMes  
 AND AC.IdCia=S.IdCia AND AC.IdCuenta=S.IdCuenta AND AC.IdVehiculo=S.IdVehiculo)
 AND (@pmIdCia IS NULL OR S.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR S.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraRemesaMuc] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null
 AS 
SELECT R.TipDoc AS CdTipDoc,R.NumOrden AS NumRemesa,R.IdCia AS CdCia,Compania,R.Fecha AS FecRemesa,FecDespacho,IdCliente,T.RazonSocial AS NomCliente,R.IdAgencia AS CdAgencia,Agencia
,R.IdVehiculo AS PlacaVeh,R.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,R.TipoAfiVehic AS TipoAfiVeh,R.Modalidad AS TipoRemesa,R.TipDcm AS TipOdc,R.NumDocmto AS nOCargue,R.IdCiaDcm AS CdCiaOdc,R.FechaDcm AS FecOCargue
,EstCumplido,EstFactura,CdConcepto,Concepto,SerieGuia,NumGuia,R.Observacion AS Observ,R.IdEstado AS CdEdstado,RA.TipoRuta,RA.TipoMintrans
--detalles
,Item,IdMercancia,DescripMcias,D.Cantidad AS Cant,PesoNeto,D.UndMed AS CdUmPeso,UMP.Unidad AS UmPeso,D.Volumen AS Volmen,UndVol,D.IdUnd AS CdUnd,UM.Unidad AS UndMedida,Cases,Cajas,Palets
,NitRemite,Remitente,NitDestntario,Destinatario,DirOrigen,IdOrigen,LO.Localidad AS CiuOrigen,LO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,DirDestino,IdDestino,LD.Localidad AS CiuDestino,LD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,TarifClie,D.TarifPago AS RemTarifPago
,D.TarifTabla AS RemTarifTabla,D.VrDeclarado AS ValDeclarado,D.VrSeguro AS ValSeguro,TarifSeguro,UndTarifa,UndTarifPago
,Remision,DocCliente,Referencia1,Referencia2,Referencia3,Cumplido,IdCiaCump,FechaCump,DetalleCump
,CantidadCump,PesoCump,VolCump,CasesCump,CajasCump,PaletsCump,TarifCump,PagoCump,UndTarCump,UndTarPagoCump
--columnas ocultas
--,NumManif,IdCiaManif,TipOdp,NumeroOdp,IdCiaOdp,TarifOdp
--datos del pedido
,NumPedido,IdCiaPed,R.FechaPed AS FecPedido,ModalidadPed
--datos de factura
,TipFac,Factura,IdCiaFac,FechaFac,TarifClieFac,NumFactura,NumDevFact,FacCantidad,FacValorTotal,FacCostoTotal,FacFaltantes,FacPesoNeto
--datos de manifiesto
,TipoMuc,ISNULL(MucNumero,0) AS MucNumero,MucCdCia,FechaMuc,FecDespMuc,MucPlacaVeh,MucCdPoseedor,NP.RazonSocial AS NomPoseedor,MucCdRuta,MucTarifTabla,MucTarifPago,MUC.VrFletes AS VrTotFletes,MUC.VrRetencion AS MucVrRetencion
,MUC.VrReteIca AS MucVrReteIca,MUC.VrDescuento AS MucVrDcto,MUC.VrAnticipo AS MucVrAnticipo,VrAntAdic,MUC.TarifaRet AS MucTarifRet,MUC.TarifaIca AS MucTarifIca
,NumMintrans,EdoMintrans,MucObserv,MucTipOdp,ISNULL(MucOrdPago,0) AS MucOrdPago,MucCdCiaOdp,TipEgr,Egreso,IdCiaEgr,NumCheque,TotalEgresos
,dbo.FuncMucListaAntcipos(ISNULL(MucNumero,0),MucCdCia) AS Anticipos
--datos del cliente
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie
 FROM Trn_TraRemesa AS R INNER JOIN Trn_TraRemMcias AS D ON R.TipDoc=D.TipDoc AND R.NumOrden=D.NumOrden AND R.IdCia=D.IdCia
 INNER JOIN Companias AS CN ON R.IdCia=CN.IdCia  INNER JOIN Terceros AS T ON R.IdCliente=T.IdTercero 
 INNER JOIN Vehiculos AS V ON R.IdVehiculo=V.IdVehiculo INNER JOIN Terceros AS CDT ON R.IdConductor=CDT.IdTercero 
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN TercCliente AS CLI ON R.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 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
 INNER JOIN UndMed AS UM ON D.IdUnd=UM.IdUnd LEFT JOIN Sys_Um AS UMP ON D.UndMed=UMP.UndMed
 LEFT JOIN Agencias AS A ON R.IdAgencia=A.IdAgencia LEFT JOIN Conceptos AS C ON R.CdConcepto=C.IdConcepto 
 LEFT JOIN Trn_TraRemAnexo AS RA ON R.TipDoc=RA.TipDoc AND R.NumOrden=RA.NumOrden AND R.IdCia=RA.IdCia
--==== Consulta de pedidos
 LEFT JOIN (SELECT P.TipDoc AS TipoPed,P.Pedido AS nPedido,P.IdCia AS CdCiaPed,P.FecDespacho AS FechaPed,P.IdVend AS CdVend,P.Modalidad AS ModalidadPed
,Cotizacion,IdCiaCot,NumAprob,IdCiaApr,FecAprob,P.Observacion AS PedObserv,D.IdMercancia AS PedCdMcia,D.DescripMcias AS PedDescMcia
,SUM(D.Cantidad) AS PedCantidad,SUM(D.PesoNeto) AS PedPesoNeto,SUM(D.Volumen) AS PedVolumen,SUM(D.Cases) AS PedCases
,SUM(D.Cajas) AS PedCajas,SUM(D.Palets) AS PedPalets,MAX(D.TarifClie) AS PedTarifClie,MAX(D.TarifPago) AS PedTarifPago
 FROM Trn_TraPedido AS P INNER JOIN Trn_TraPedMcias AS D ON P.TipDoc=D.TipDoc AND P.Pedido=D.Pedido AND P.IdCia=D.IdCia
 WHERE P.TipDoc='PDT' AND P.Anulado=0 GROUP BY P.TipDoc,P.Pedido,P.IdCia,P.FecDespacho,P.IdVend,P.Modalidad,Cotizacion,IdCiaCot
,NumAprob,IdCiaApr,FecAprob,P.Observacion,D.IdMercancia,D.DescripMcias) AS PD ON R.NumPedido=PD.nPedido AND R.IdCiaPed=PD.CdCiaPed AND D.IdMercancia=PD.PedCdMcia
--==== consulta de facturas-detalles
 LEFT JOIN (SELECT TipRem,Remesa,IdCiaRem,ItemRem,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Cantidad*-1 ELSE Cantidad END) AS FacCantidad
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN (Cantidad*VrUnitario)*-1 ELSE Cantidad*VrUnitario END) AS FacValorTotal
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN (Cantidad*VrCosto)*-1 ELSE Cantidad*VrCosto END) AS FacCostoTotal
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Unidades*-1 ELSE Unidades END) AS FacUnidades
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN PesoNeto*-1 ELSE PesoNeto END) AS FacPesoNeto
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Volumen*-1 ELSE Volumen END) AS FacVolumen
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Cases*-1 ELSE Cases END) AS FacCases
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Cajas*-1 ELSE Cajas END) AS FacCajas
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Palets*-1 ELSE Palets END) AS FacPalets
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN VrFaltante*-1 ELSE VrFaltante END) AS FacFaltantes
,MAX(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN 0 ELSE Factura END) AS NumFactura
,MAX(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Factura ELSE 0 END) AS NumDevFact
FROM Trn_TraFacRemesas GROUP BY TipRem,Remesa,IdCiaRem,ItemRem) AS RF ON R.TipDoc=RF.TipRem AND R.NumOrden=RF.Remesa AND R.IdCia=RF.IdCiaRem AND D.Item=RF.ItemRem
--==== consulta de manifiestos 
 LEFT JOIN (SELECT RM.TipRem AS MucTipoRem,RM.Remesa AS MucNumRemesa,RM.IdCiaRem AS MucCdCiaRem,RM.ItemRem AS MucItemRem,RM.TipDoc AS TipoMuc,RM.Manifiesto AS MucNumero,RM.IdCia AS MucCdCia
,RM.TarifTabla AS MucTarifTabla,RM.TarifPago AS MucTarifPago,RemMintrans,M.Fecha AS FechaMuc,M.FecDespacho AS FecDespMuc,M.IdRuta AS MucCdRuta,M.IdOrigen AS MucCdOrigen,M.IdDestino AS MucCdDestino,M.IdVehiculo AS MucPlacaVeh
,M.IdConductor AS MucCedCond,M.nRemolque AS MucnRemolque,TipoAfiVehic,M.IdPoseedor AS MucCdPoseedor,VrFletes,VrRetencion,VrReteIca,VrDescuento,VrAnticipo,VrAntAdic,VrNeto,VrPagos
,VrCargos,VrDctos,TarifaFlete,TarifaRet,TarifaIca,NumMintrans,EdoMintrans,M.TipOdp AS MucTipOdp,M.OrdPago AS MucOrdPago,M.IdCiaOdp AS MucCdCiaOdp,M.FechaOdp AS MucFecOdp,M.Observacion AS MucObserv
FROM Trn_TraManifRem AS RM INNER JOIN Trn_TraManifiesto AS M ON RM.TipDoc=M.TipDoc AND RM.Manifiesto=M.Manifiesto AND RM.IdCia=M.IdCia
WHERE M.Anulado=0) AS MUC ON R.TipDoc=MUC.MucTipoRem AND R.NumOrden=MUC.MucNumRemesa AND R.IdCia=MUC.MucCdCiaRem AND D.Item=MUC.MucItemRem
--===== Orden de pago
 LEFT JOIN (SELECT M.TipMuc AS OdpTipMuc,M.Manifiesto AS OdpManif,M.IdCiaMuc AS OdpCdCiaMuc,M.TipDoc AS TipOdp,M.OrdPago AS NumOPago,M.IdCia AS OdpCdCia,OP.Fecha AS FechaOdp
,VrTotalFletes,VrDescuento,VrRetencion,VrReteIca,VrAnticipos,VrFaltantes,VrSeguros,VrFondos,VrAportes,VrOtrosDctos,VrImpuestos,VrEstampilla,VrOtrosPagos,VrNeto
,TarifaTabla,TarifaFlete,UnidTarifa,PesoTotal,Unidades,Volumen,TarifaRet,TarifaIca,TipoLiq,TipEgr,Egreso,IdCiaEgr,NumCheque,Referencia,CantFaltante,UnidadFalt,TolFaltNeto,OP.Observacion AS OdpObserv
 FROM Trn_TraOrdenManif AS M INNER JOIN Trn_TraOrdenPago AS OP ON M.TipDoc=OP.TipDoc AND M.OrdPago=OP.OrdPago AND M.IdCia=OP.IdCia
 WHERE OP.Anulado=0) AS ODP ON MUC.MucTipOdp=ODP.TipOdp AND MUC.MucOrdPago=ODP.NumOPago AND MUC.MucCdCiaOdp=ODP.OdpCdCia
 LEFT JOIN Terceros AS NP ON MUC.MucCdPoseedor=NP.IdTercero 
-- Egresos
 LEFT JOIN (SELECT EO.TipOdp AS TipOrden,EO.OrdPago AS NumOPago,EO.IdCiaOdp AS CdCiaOpago,SUM(VrAbonado) AS TotalEgresos
 FROM Trn_TraEgrOrden AS EO INNER JOIN Trn_Comprobantes AS E ON EO.TipCom=E.TipCom AND EO.NumEgreso=E.Comprobante AND EO.IdCia=E.IdCia
 WHERE E.Anulado=0 GROUP BY EO.TipOdp,EO.OrdPago,EO.IdCiaOdp) AS EGT ON MUC.MucTipOdp=EGT.TipOrden AND MUC.MucOrdPago=EGT.NumOPago AND MUC.MucCdCiaOdp=EGT.CdCiaOpago

 WHERE R.TipDoc='RMT' AND R.Fecha BETWEEN @pmFechaIni AND  @pmFechaFin AND R.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND R.IdCliente LIKE ISNULL(@pmIdCliente,'%') AND R.Anulado=0
GO