if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsCompanias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsCompanias]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTercCliente]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsTercCliente]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTercCliente_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsTercCliente_Sel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_BalCom]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_BalCom]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryCompanias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryCompanias]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryCompanias_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryCompanias_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryCompaniasLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryCompaniasLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryKardexSob]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryKardexSob]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTercCliente]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTercCliente]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTercClienteLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTercClienteLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_BalCom]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQrytm_BalCom]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_BalComBases]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQrytm_BalComBases]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpCompanias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpCompanias]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTercCliente]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpTercCliente]
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInstm_BalCom] @pmtmEst CHAR(2),@pmtmIdCuenta VARCHAR(16),@pmtmItem INT,@pmtmIdTercero VARCHAR(16)
,@pmtmIdVehiculo VARCHAR(10),@pmtmIdCCosto VARCHAR(16),@pmtmIdSubCos VARCHAR(16),@pmtmCodAgncia VARCHAR(16)
,@pmtmTipo VARCHAR(4),@pmtmSaldoAnterior MONEY,@pmtmTotalDebitos MONEY,@pmtmTotalCreditos MONEY,@pmtmNuevoSaldo MONEY
,@pmtmVrPresupto MONEY,@pmtmVrCompras MONEY
 AS 
 INSERT INTO tm_BalCom (tmEst,tmIdCuenta,tmItem,tmIdTercero,tmIdVehiculo,tmIdCCosto,tmIdSubCos,tmCodAgncia,tmTipo,tmSaldoAnterior
,tmTotalDebitos,tmTotalCreditos,tmNuevoSaldo,tmVrPresupto,tmVrCompras)
 VALUES (@pmtmEst,@pmtmIdCuenta,@pmtmItem,@pmtmIdTercero,@pmtmIdVehiculo,@pmtmIdCCosto,@pmtmIdSubCos,@pmtmCodAgncia
,@pmtmTipo,@pmtmSaldoAnterior,@pmtmTotalDebitos,@pmtmTotalCreditos,@pmtmNuevoSaldo,@pmtmVrPresupto,@pmtmVrCompras)

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[paQryTercClienteLta] @pmIdLocal VARCHAR(8)=Null,@pmIdSector VARCHAR(8)=Null,@pmIdRegimen VARCHAR(4)=Null,@pmTipoId CHAR(1)=Null
,@pmIdProf VARCHAR(4)=Null,@pmIdGrupo VARCHAR(4)=Null,@pmIdLocEnv VARCHAR(8)=Null,@pmIdForma VARCHAR(4)=Null,@pmIdBanco VARCHAR(4)=Null,@pmIdZona VARCHAR(4)=Null
,@pmIdSzona VARCHAR(4)=Null,@pmIdPlazo VARCHAR(4)=Null,@pmIdEstrato VARCHAR(4)=Null,@pmIdVend VARCHAR(16)=Null,@pmIdRuta VARCHAR(4)=Null
,@pmFecIngIni SMALLDATETIME=Null,@pmFecIngFin SMALLDATETIME=Null,@pmIdEstado VARCHAR(4)=Null,@pmIdEstadoTer VARCHAR(4)=Null,@pmInactivo BIT=Null
,@pmInactivoTer BIT=Null
 AS 
 SELECT IdClie,T.RazonSocial AS RaznSocial,T.TipoId AS TipId,T.Dv AS DvNit,T.NomCial AS NombreCmcial,T.SiglaRaz AS SiglaRazSocial
,NitRepLeg,NomRepLeg,NitContac,NomContac,TelContac,emlContac,CargContac,T.Direccion AS Dirccion,T.IdLocal AS CdCiudad,L.Localidad AS Ciudad,L.IdDep AS CdDpto
,D.Departamento AS Dpto,T.Telefono AS Tlefono,T.Fax AS NumFax,T.TelMovil AS TelCeluar,T.SitioWeb AS WebSite,T.e_mail AS Email,DirEnv,IdLocEnv,LE.Localidad AS CiuEnvio
,DE.Departamento AS Dptoenvio,DiasEntga,C.IdGrupo AS CdGrupo,GrupoClie,C.IdEstrato AS CdEstrato,Estrato,SZ.IdZona AS CdZona,Zona,C.IdSzona AS CdSubzona,Subzona
,C.IdRuta AS CdRuta,Ruta,C.IdPlazo AS CdPlazo,Plazo,NVmto,PlazosImp,CdPlazoComb,C.IdForma AS CdFrma,FormaPago,T.IdSector AS CdSector,SectorEco,T.IdProf AS CdProf,Profesion,T.IdRegimen AS CdRgmen
,Regimen,T.TipEnte AS TipoEnte,IdVend,TV.RazonSocial AS NomVendedor,NitFact,C.IdClase AS CdClase,ClaseCuenta,NumCuenta,C.IdBanco AS CdBanco,Banco,B.Direccion AS Dirbanco
,CdMney,CdDct,CdRet,CdRiv,CdCms,ExcIva,TrfIntMora,DiasGracia,LiqFletes,FactSold,Autoret,IncRet,IncRiv,IncIca,FactTipo,VrCupo,VrSaldo,UidClie,Contrato,NContrato,CiaContMay,FecIngreso,FecVigencia
,FecRetiro,MatMerc,FecMat,PathFoto,PathFirma,T.Observacion AS Observ,Cmntario1,Cmntario2,Cmntario3,CdBandera,PrendGarant,FecUpCupo,TipoCliente,CodClieSicom,C.IdEstado AS CdEst_clie,EC.Estado AS EstadoClie,C.Inactivo AS Clie_inactivo
,T.IdEstado AS CdEst_terc,ET.Estado AS Estad_terc,T.Inactivo AS Terc_inactivo,T.Codigo AS CodTerc,T.IdLugarCed AS IdLugCed,LC.Localidad AS LugarCed,T.FecExpCed AS FecExpCedula
,T.EsVendedor AS EsVend,T.EsConductor AS EsCond,T.EsPropietario AS EsProp,T.EsProveedor AS EsProvee,T.EsEmpleado AS EsEmpldo,T.EsOperario AS EsOper,T.EsAccnista AS EsAcc
,T.EsCiaAseg AS EsAseg,CdTipBloq,C.FechaAdd AS FecAdd,C.FechaUpdate AS FecUpd,C.IdUsuario AS IdUsuari,Usuario,T.IniStgNom AS PosNomb,Restric_Cia,CupoGalones,FecPlazoDoc,EdoRadicaDoc,DescEdoDoc,ComIndustrial,NumLista,Termicas
 FROM TercCliente  AS C INNER JOIN Terceros AS T ON C.IdClie=T.IdTercero  INNER JOIN Terceros AS TV ON C.IdVend=TV.IdTercero
 INNER JOIN GruposCli AS G ON C.IdGrupo=G.IdGrupo INNER JOIN Estratos AS ETC ON C.IdEstrato=ETC.IdEstrato
 INNER JOIN Subzonas AS SZ ON C.IdSzona=SZ.IdSzona  INNER JOIN Zonas AS Z ON SZ.IdZona=Z.IdZona 
 INNER JOIN Plazos AS PZ ON C.IdPlazo=PZ.IdPlazo INNER JOIN Rutas AS R ON C.IdRuta=R.IdRuta
 INNER JOIN Formaspago AS F ON C.IdForma=F.IdForma
 INNER JOIN ClaseCta AS CTA ON  C.IdClase=CTA.IdClase
 INNER JOIN Bancos AS B ON C.IdBanco=B.IdBanco INNER JOIN EstadoTer AS EC ON C.IdEstado=EC.IdEstado
 INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario   INNER JOIN Localidades AS LE ON C.IdLocEnv=LE.IdLocal
 INNER JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep
 INNER JOIN  SectoresEco AS S ON T.IdSector=S.IdSector  INNER JOIN RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen
 INNER JOIN EstadoTer AS ET ON T.IdEstado=ET.IdEstado INNER JOIN Localidades AS LC ON T.IdLugarCed=LC.IdLocal 
 INNER JOIN Profesiones AS PF ON T.IdProf=PF.IdProf 
 WHERE T.IdLocal LIKE ISNULL(@pmIdLocal,'%') AND T.IdSector LIKE ISNULL(@pmIdSector,'%')  AND T.IdRegimen LIKE ISNULL(@pmIdRegimen,'%')
 AND T.TipoId LIKE ISNULL(@pmTipoId,'%') AND T.IdProf LIKE ISNULL(@pmIdProf,'%') AND C.IdGrupo LIKE ISNULL(@pmIdGrupo,'%')
 AND IdLocEnv LIKE ISNULL(@pmIdLocEnv,'%') AND C.IdForma LIKE ISNULL(@pmIdForma,'%') AND C.IdBanco LIKE ISNULL(@pmIdBanco,'%')
 AND SZ.IdZona LIKE ISNULL(@pmIdZona,'%') AND C.IdSzona LIKE ISNULL(@pmIdSzona,'%')
 AND C.IdPlazo LIKE ISNULL(@pmIdPlazo,'%') AND C.IdEstrato LIKE ISNULL(@pmIdEstrato,'%')
 AND IdVend LIKE ISNULL(@pmIdVend,'%') AND C.IdRuta LIKE ISNULL(@pmIdRuta,'%')
 AND C.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND T.IdEstado LIKE ISNULL(@pmIdEstadoTer,'%')
 AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
 AND (T.Inactivo=ISNULL(@pmInactivoTer,0) or T.Inactivo=ISNULL(@pmInactivoTer,1))
 AND (FecIngreso>=ISNULL(@pmFecIngIni,CAST('19100101' AS SMALLDATETIME)) AND FecIngreso<=ISNULL(@pmFecIngFin,CAST('20781230' AS SMALLDATETIME)))
 ORDER BY T.RazonSocial



GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[paQryCompanias] @pmIdCia CHAR(2)
 AS 
 SELECT IdCia,Compania,DirInterface,FechaActual,PeriodoActual,IdCCosto,IdSubCos,IdTercero
,CodSucsal,DireccSuc,TelefSuc,FaxSuc,IdLocal,TipoEDS,CodPlanta,CodBodega,FecModPrest,FechaAdd,FechaUpdate,Inactivo
 FROM Companias
 WHERE IdCia LIKE ISNULL(@pmIdCia,'%%')
 ORDER BY IdCia


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[paUpCompanias] @pmIdCia CHAR(2),@pmCompania VARCHAR(50),@pmDirInterface VARCHAR(255),@pmFechaActual SMALLDATETIME,@pmPeriodoActual SMALLDATETIME,@pmIdCCosto VARCHAR(16)
,@pmIdSubCos VARCHAR(16),@pmIdTercero VARCHAR(16),@pmCodSucsal VARCHAR(10),@pmDireccSuc VARCHAR(250),@pmTelefSuc VARCHAR(20),@pmFaxSuc VARCHAR(20),@pmIdLocal VARCHAR(8)
,@pmTipoEDS VARCHAR(10),@pmCodPlanta VARCHAR(20),@pmCodBodega VARCHAR(4),@pmFecModPrest SMALLDATETIME,@pmFechaUpdate SMALLDATETIME,@pmInactivo BIT
 AS 
 UPDATE Companias SET Compania=@pmCompania,DirInterface=@pmDirInterface,FechaActual=@pmFechaActual,PeriodoActual=@pmPeriodoActual,IdCCosto=@pmIdCCosto
,IdSubCos=@pmIdSubCos,IdTercero=@pmIdTercero,CodSucsal=@pmCodSucsal,DireccSuc=@pmDireccSuc,TelefSuc=@pmTelefSuc,FaxSuc=@pmFaxSuc, IdLocal=@pmIdLocal,TipoEDS=@pmTipoEDS
,CodPlanta=@pmCodPlanta,CodBodega=@pmCodBodega,FechaUpdate=@pmFechaUpdate,Inactivo=@pmInactivo,FecModPrest=@pmFecModPrest
 WHERE IdCia=@pmIdCia


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[paQryCompaniasLta] @pmIdCia CHAR(2)=Null,@pmInactivo BIT=Null
 AS 
 SELECT IdCia,Compania,DirInterface,FechaActual,PeriodoActual,Inactivo,IdCCosto,IdSubCos,IdTercero
,CodSucsal,DireccSuc,TelefSuc,FaxSuc,IdLocal,TipoEDS,CodPlanta,CodBodega,FecModPrest,FechaAdd,FechaUpdate
 FROM Companias
 WHERE IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND (Inactivo=ISNULL(@pmInactivo,0) or Inactivo=ISNULL(@pmInactivo,1))
 ORDER BY IdCia


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[paInsCompanias] @pmIdCia CHAR(2),@pmCompania VARCHAR(50),@pmDirInterface VARCHAR(255),@pmFechaActual SMALLDATETIME
,@pmPeriodoActual SMALLDATETIME,@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdTercero VARCHAR(16)
,@pmCodSucsal VARCHAR(10),@pmDireccSuc VARCHAR(250),@pmTelefSuc VARCHAR(20),@pmFaxSuc VARCHAR(20),@pmIdLocal VARCHAR(8),@pmTipoEDS VARCHAR(10)
,@pmCodPlanta VARCHAR(20),@pmCodBodega VARCHAR(4),@pmFecModPrest SMALLDATETIME,@pmFechaAdd SMALLDATETIME,@pmInactivo BIT
 AS 
 INSERT INTO Companias (IdCia,Compania,DirInterface,FechaActual,PeriodoActual,IdCCosto,IdSubCos,IdTercero
,CodSucsal,DireccSuc,TelefSuc,FaxSuc,IdLocal,TipoEDS,CodPlanta,FechaAdd,Inactivo,CodBodega,FecModPrest)
 VALUES (@pmIdCia,@pmCompania,@pmDirInterface,@pmFechaActual,@pmPeriodoActual,@pmIdCCosto,@pmIdSubCos,@pmIdTercero
,@pmCodSucsal,@pmDireccSuc,@pmTelefSuc,@pmFaxSuc,@pmIdLocal,@pmTipoEDS,@pmCodPlanta,@pmFechaAdd,@pmInactivo,@pmCodBodega,@pmFecModPrest)


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[paQryCompanias_Cr] @pmInactivo BIT=Null
 AS 
 SELECT IdCia,Compania,DirInterface,FechaActual,PeriodoActual,C.IdCCosto AS CodCcosto,CCosto,C.IdSubCos AS CodSubCosto,SubCosto
,IdTercero,CodSucsal,DireccSuc,TelefSuc,FaxSuc,C.IdLocal AS CodCiu,Localidad,TipoEDS,CodPlanta,CodBodega,FecModPrest
,C.FechaAdd AS Fecha_Add,C.FechaUpdate AS FecUpd,C.Inactivo AS Inactvo
 FROM Companias AS C INNER JOIN CentroCosto AS CC ON C.IdCCosto=CC.IdCCosto 
 INNER JOIN  SubCentros AS SC ON C.IdSubCos=SC.IdSubCos
 INNER JOIN Localidades AS L ON C.IdLocal=L.IdLocal
 WHERE (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
 ORDER BY IdCia


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryKardexSob] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME
 AS 
 SELECT CdLocal,K.IdProducto AS CdProducto,DescripProd,ISNULL(ZonaFrontera,0) AS ZonaFront
,SUM(Entradas) AS STOTENT,SUM(Salidas) AS STOTSAL,SUM(VrPrecio*Entradas) AS SVALENT,SUM(VrPrecio*Salidas) AS SVALSAL
,SUM(VrDctoEnt) AS SDCTENT,SUM(VrDctoSal) AS SDCTSAL
,SUM(Sobretasa*Entradas) AS SSOBENT,SUM(Sobretasa*Salidas) AS SSOBSAL
,SUM(TasaNac*Entradas) AS SNACENT,SUM(TasaNac*Salidas) AS SNACSAL
,SUM(TasaDep*Entradas) AS SDEPENT,SUM(TasaDep*Salidas) AS SDEPSAL
,SUM(TasaMun*Entradas) AS SMUNENT,SUM(TasaMun*Salidas) AS SMUNSAL
,SUM(Soldicom*Entradas) AS SSOLENT,SUM(Soldicom*Salidas) AS SSOLSAL
,SUM(ImpGlobal*Entradas) AS SGLOENT,SUM(ImpGlobal*Salidas) AS SGLOSAL
,SUM(VrBase*Entradas) AS SBASEENT,SUM(VrBase*Salidas) AS SBASESAL
,MAX(Sobretasa) AS MAXSOB,MAX(TasaNac) AS MAXNAC,MAX(TasaDep) AS MAXDEP
,MAX(TasaMun) AS MAXMUN
,MAX(VrPrecio) AS MAXPRECIO,MAX(VrBase) AS MAXBASE,MAX(VrBruto) AS MAXBRUTO
 FROM Trn_Kardex AS K INNER JOIN ProdMcias AS P ON K.IdProducto=P.IdProducto
 LEFT JOIN (SELECT TipDoc,Factura,IdCia,ZonaFrontera FROM Trn_Facturas WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND Modalidad='COMBUSTBLE'
 UNION ALL SELECT TipDev,Devolucion,IdCia,ZonaFrontera FROM Trn_DevFcr WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND Modalidad='COMBUSTBLE')
 AS F ON K.TipDoc=F.TipDoc AND K.Documento=F.Factura AND K.IdCia=F.IdCia
 WHERE K.TipDoc IN ('FCR','FC1','FC2','FC3','FC4','FC5','DFC','DF1','DF2','DF3','DF4','DF5','PLA','DVP','PAS','DNP')
 AND  K.Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 AND P.Tanques<>0 AND K.NoVentas>0
 GROUP BY CdLocal,K.IdProducto,DescripProd,ISNULL(ZonaFrontera,0)
 ORDER BY CdLocal,K.IdProducto

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[paQrytm_BalCom] @pmtmEst CHAR(2),@pmtmIdCuenta VARCHAR(16)=Null
,@pmtmIdTercero VARCHAR(16)=Null,@pmtmCodAgncia VARCHAR(16)=Null
 AS 
 SELECT BC.tmIdCuenta AS IdCuent,NomCuenta,tmTipo,tmSaldoAnterior,tmTotalDebitos,tmTotalCreditos
,tmSaldoAnterior+tmTotalDebitos-tmTotalCreditos AS NuevoSaldo
,tmIdTercero,RazonSocial,tmCodAgncia,A.Agencia AS NomAgencia,tmIdVehiculo,tmIdCCosto,CCosto,tmIdSubCos,SubCosto
,tmVrPresupto,tmVrCompras
--información del tercero
,TipoId,Dv,Direccion,T.IdLocal AS CdCiudad,Localidad,L.IdDep AS CdDepto,Departamento,Telefono,EsAccnista,EsCliente,EsPropietario 
--información de agencia y vehículos
,CodAgencia,Referencia--,NumVeh
,tmItem,tmNivel1,tmNivel2,tmNivel4,tmNivel6,tmNivel9,tmNivel12,Tercero,CentroCosto
 FROM tm_BalCom  AS  BC INNER JOIN Puc AS P ON BC.tmIdCuenta=P.IdCuenta
 INNER JOIN Terceros AS T ON BC.tmIdTercero=T.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep
 INNER JOIN CentroCosto AS CC ON BC.tmIdCCosto=CC.IdCCosto
 LEFT JOIN SubCentros AS SC ON BC.tmIdSubCos=SC.IdSubCos
 LEFT JOIN tm_NivCue AS NV ON BC.tmIdCuenta=NV.tmIdCuenta
 LEFT JOIN  Agencias AS A ON BC.tmCodAgncia=A.IdAgencia
-- LEFT JOIN Vehiculos AS V ON BC.tmIdVehiculo=V.IdVehiculo
 WHERE tmEst=@pmtmEst AND BC.tmIdCuenta LIKE ISNULL(@pmtmIdCuenta,'%')
 AND tmIdTercero LIKE ISNULL(@pmtmIdTercero,'%')
 AND tmCodAgncia LIKE ISNULL(@pmtmCodAgncia,'%')
 ORDER BY BC.tmIdCuenta,RazonSocial,tmCodAgncia,tmIdCCosto,tmItem


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[paQryTercCliente] @pmIdClie VARCHAR(16)
 AS 
 SELECT IdClie,NitRepLeg,NomRepLeg,NitContac,NomContac,TelContac,emlContac,CargContac,DirEnv,IdLocEnv,DiasEntga
,IdSzona,IdGrupo,IdPlazo,CdPlazoComb,IdForma,IdEstrato,IdVend,NitFact,IdRuta,IdClase,NumCuenta,IdBanco,CdMney,CdDct,CdRet,CdRiv
,CdCms,PlazosImp,ExcIva,TrfIntMora,DiasGracia,LiqFletes,FactSold,Autoret,IncRet,IncRiv,IncIca,FactTipo,VrCupo,VrSaldo,UidClie,PwdClie
,FecIngreso,FecVigencia,FecRetiro,MatMerc,FecMat,PathFoto,PathFirma,Cmntario1,Cmntario2,CdBandera,CodClieSicom,Contrato,NContrato,CiaContMay,Cmntario3,PrendGarant,FecUpCupo
,TipoCliente,CupoGalones,Restric_Cia,FecPlazoDoc,EdoRadicaDoc,CdTipBloq,DescEdoDoc,ComIndustrial,NumLista,IdEstado,Inactivo,Termicas,FechaAdd,FechaUpdate,IdUsuario
 FROM TercCliente
 WHERE IdClie=@pmIdClie



GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[paUpTercCliente] @pmIdClie VARCHAR(16),@pmNitRepLeg VARCHAR(16),@pmNomRepLeg VARCHAR(150),@pmNitContac VARCHAR(16),@pmNomContac VARCHAR(150),@pmTelContac VARCHAR(20),@pmemlContac VARCHAR(100)
,@pmCargContac VARCHAR(50),@pmDirEnv VARCHAR(250),@pmIdLocEnv VARCHAR(8),@pmDiasEntga INT,@pmIdSzona VARCHAR(4),@pmIdGrupo VARCHAR(4),@pmIdPlazo VARCHAR(4),@pmIdForma VARCHAR(4),@pmIdEstrato VARCHAR(4)
,@pmIdVend VARCHAR(16),@pmNitFact VARCHAR(16),@pmIdRuta VARCHAR(4),@pmIdClase VARCHAR(4),@pmNumCuenta VARCHAR(30),@pmIdBanco VARCHAR(4),@pmCdMney VARCHAR(5),@pmCdDct VARCHAR(4),@pmCdRet VARCHAR(4)
,@pmCdRiv VARCHAR(4),@pmCdCms VARCHAR(4),@pmExcIva BIT,@pmTrfIntMora DECIMAL(14,4),@pmDiasGracia INT,@pmLiqFletes BIT,@pmFactSold BIT,@pmAutoret BIT,@pmIncRet BIT,@pmIncRiv BIT,@pmIncIca BIT,@pmFactTipo INT,@pmVrCupo MONEY
,@pmVrSaldo MONEY,@pmUidClie VARCHAR(16),@pmPwdClie VARCHAR(10),@pmFecIngreso SMALLDATETIME,@pmFecVigencia SMALLDATETIME,@pmFecRetiro SMALLDATETIME,@pmMatMerc VARCHAR(20),@pmFecMat SMALLDATETIME
,@pmPathFoto VARCHAR(30),@pmPathFirma VARCHAR(30),@pmCmntario1 VARCHAR(250),@pmCmntario2 VARCHAR(250),@pmPlazosImp VARCHAR(20),@pmCdBandera VARCHAR(4),@pmContrato BIT,@pmNContrato INT,@pmCiaContMay CHAR(2)
,@pmCmntario3 VARCHAR(250),@pmPrendGarant VARCHAR(150),@pmFecUpCupo SMALLDATETIME,@pmTipoCliente VARCHAR(10),@pmRestric_Cia BIT,@pmCupoGalones DECIMAL(14,4)
,@pmCdPlazoComb VARCHAR(4),@pmCodClieSicom VARCHAR(20),@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmFecPlazoDoc SMALLDATETIME,@pmEdoRadicaDoc INT,@pmCdTipBloq VARCHAR(4),@pmDescEdoDoc VARCHAR(250),@pmComIndustrial INT
,@pmNumLista CHAR(1),@pmTermicas BIT,@pmFechaUpdate SMALLDATETIME 
 AS 
 UPDATE TercCliente SET NitRepLeg=@pmNitRepLeg,NomRepLeg=@pmNomRepLeg,NitContac=@pmNitContac,NomContac=@pmNomContac,TelContac=@pmTelContac,emlContac=@pmemlContac,CargContac=@pmCargContac,DirEnv=@pmDirEnv,IdLocEnv=@pmIdLocEnv
,DiasEntga=@pmDiasEntga,IdSzona=@pmIdSzona,IdGrupo=@pmIdGrupo,IdPlazo=@pmIdPlazo,IdForma=@pmIdForma,IdEstrato=@pmIdEstrato,IdVend=@pmIdVend,NitFact=@pmNitFact,IdRuta=@pmIdRuta,IdClase=@pmIdClase,NumCuenta=@pmNumCuenta,IdBanco=@pmIdBanco
,CdMney=@pmCdMney,CdDct=@pmCdDct,CdRet=@pmCdRet,CdRiv=@pmCdRiv,CdCms=@pmCdCms,ExcIva=@pmExcIva,TrfIntMora=@pmTrfIntMora,DiasGracia=@pmDiasGracia,LiqFletes=@pmLiqFletes,FactSold=@pmFactSold,Autoret=@pmAutoret,IncRet=@pmIncRet,IncRiv=@pmIncRiv
,IncIca=@pmIncIca,FactTipo=@pmFactTipo,VrCupo=@pmVrCupo,VrSaldo=@pmVrSaldo,UidClie=@pmUidClie,PwdClie=@pmPwdClie,FecIngreso=@pmFecIngreso,FecVigencia=@pmFecVigencia,FecRetiro=@pmFecRetiro,MatMerc=@pmMatMerc,FecMat=@pmFecMat,PathFoto=@pmPathFoto
,PathFirma=@pmPathFirma,Cmntario1=@pmCmntario1,Cmntario2=@pmCmntario2,PlazosImp=@pmPlazosImp,CdBandera=@pmCdBandera,Contrato=@pmContrato,NContrato=@pmNContrato,CiaContMay=@pmCiaContMay,Cmntario3=@pmCmntario3,PrendGarant=@pmPrendGarant,FecUpCupo=@pmFecUpCupo
,CdPlazoComb=@pmCdPlazoComb,IdEstado=@pmIdEstado,Inactivo=@pmInactivo,TipoCliente=@pmTipoCliente,FechaUpdate=@pmFechaUpdate,Restric_Cia=@pmRestric_Cia,CupoGalones=@pmCupoGalones,CodClieSicom=@pmCodClieSicom,FecPlazoDoc=@pmFecPlazoDoc,EdoRadicaDoc=@pmEdoRadicaDoc
,CdTipBloq=@pmCdTipBloq,DescEdoDoc=@pmDescEdoDoc,ComIndustrial=@pmComIndustrial,NumLista=@pmNumLista,Termicas=@pmTermicas
 WHERE IdClie=@pmIdClie



GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[paInsTercCliente_Sel] @pmIdClie VARCHAR(16),@pmNewIdClie VARCHAR(16)
 AS 
 IF EXISTS (SELECT IdClie FROM  TercCliente WHERE  IdClie=@pmIdClie)
  INSERT INTO TercCliente (IdClie,NitRepLeg,NomRepLeg,NitContac,NomContac,TelContac,emlContac,CargContac,DirEnv,IdLocEnv,DiasEntga,IdSzona,IdGrupo,IdPlazo,IdForma,IdEstrato,IdVend,NitFact,IdRuta,IdClase,NumCuenta,IdBanco,CdMney,CdDct,CdRet,CdRiv,CdCms,ExcIva,TrfIntMora,DiasGracia,LiqFletes,FactSold,Autoret
 ,IncRet,IncRiv,IncIca,FactTipo,VrCupo,VrSaldo,UidClie,PwdClie,CodClieSicom,FecIngreso,FecVigencia,FecRetiro,MatMerc,FecMat,PathFoto,PathFirma,Cmntario1,Cmntario2,PlazosImp,IdEstado,TipoCliente,Inactivo,CdBandera,Contrato,NContrato,CiaContMay,Cmntario3,PrendGarant,FecUpCupo,FechaAdd,IdUsuario
,CupoGalones,Restric_Cia,CdPlazoComb,FecPlazoDoc,EdoRadicaDoc,CdTipBloq,DescEdoDoc,ComIndustrial,NumLista,Termicas)
  SELECT @pmNewIdClie,NitRepLeg,NomRepLeg,NitContac,NomContac,TelContac,emlContac,CargContac,DirEnv,IdLocEnv,DiasEntga,IdSzona,IdGrupo,IdPlazo,IdForma,IdEstrato,IdVend,NitFact,IdRuta,IdClase,NumCuenta,IdBanco,CdMney,CdDct,CdRet,CdRiv,CdCms,ExcIva,TrfIntMora,DiasGracia,LiqFletes,FactSold,Autoret
 ,IncRet,IncRiv,IncIca,FactTipo,VrCupo,VrSaldo,UidClie,PwdClie,CodClieSicom,FecIngreso,FecVigencia,FecRetiro,MatMerc,FecMat,PathFoto,PathFirma,Cmntario1,Cmntario2,PlazosImp,IdEstado,TipoCliente,Inactivo,CdBandera,Contrato,NContrato,CiaContMay,Cmntario3,PrendGarant,FecUpCupo,FechaAdd,IdUsuario,CupoGalones,Restric_Cia,CdPlazoComb
,FecPlazoDoc,EdoRadicaDoc,CdTipBloq,DescEdoDoc,ComIndustrial,NumLista,Termicas
  FROM  TercCliente 
  WHERE IdClie=@pmIdClie



GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[paInsTercCliente] @pmIdClie VARCHAR(16),@pmNitRepLeg VARCHAR(16),@pmNomRepLeg VARCHAR(150),@pmNitContac VARCHAR(16),@pmNomContac VARCHAR(150),@pmTelContac VARCHAR(20),@pmemlContac VARCHAR(100),@pmCargContac VARCHAR(50),@pmDirEnv VARCHAR(250)
,@pmIdLocEnv VARCHAR(8),@pmDiasEntga INT,@pmIdSzona VARCHAR(4),@pmIdGrupo VARCHAR(4),@pmIdPlazo VARCHAR(4),@pmIdForma VARCHAR(4),@pmIdEstrato VARCHAR(4),@pmIdVend VARCHAR(16),@pmNitFact VARCHAR(16),@pmIdRuta VARCHAR(4),@pmIdClase VARCHAR(4),@pmNumCuenta VARCHAR(30)
,@pmIdBanco VARCHAR(4),@pmCdMney VARCHAR(5),@pmCdDct VARCHAR(4),@pmCdRet VARCHAR(4),@pmCdRiv VARCHAR(4),@pmCdCms VARCHAR(4),@pmExcIva BIT,@pmTrfIntMora DECIMAL(14,4),@pmDiasGracia INT,@pmLiqFletes BIT,@pmFactSold BIT,@pmAutoret BIT,@pmIncRet BIT,@pmIncRiv BIT
,@pmIncIca BIT,@pmFactTipo INT,@pmVrCupo MONEY,@pmVrSaldo MONEY,@pmUidClie VARCHAR(16),@pmPwdClie VARCHAR(10),@pmFecIngreso SMALLDATETIME,@pmFecVigencia SMALLDATETIME,@pmFecRetiro SMALLDATETIME,@pmMatMerc VARCHAR(20),@pmFecMat SMALLDATETIME
,@pmPathFoto VARCHAR(30),@pmPathFirma VARCHAR(30),@pmCmntario1 VARCHAR(250),@pmCmntario2 VARCHAR(250),@pmPlazosImp VARCHAR(20),@pmCdBandera VARCHAR(4),@pmContrato BIT,@pmNContrato INT,@pmCiaContMay CHAR(2),@pmCmntario3 VARCHAR(250),@pmPrendGarant VARCHAR(150)
,@pmFecUpCupo SMALLDATETIME,@pmTipoCliente VARCHAR(10),@pmRestric_Cia BIT,@pmCupoGalones DECIMAL(14,4),@pmCdPlazoComb VARCHAR(4),@pmCodClieSicom VARCHAR(20),@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmFecPlazoDoc SMALLDATETIME,@pmEdoRadicaDoc INT,@pmCdTipBloq VARCHAR(4)
,@pmDescEdoDoc VARCHAR(250),@pmComIndustrial INT,@pmNumLista CHAR(1),@pmTermicas BIT,@pmFechaAdd SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO TercCliente (IdClie,NitRepLeg,NomRepLeg,NitContac,NomContac,TelContac,emlContac,CargContac,DirEnv,IdLocEnv,DiasEntga,IdSzona,IdGrupo,IdPlazo,IdForma,IdEstrato,IdVend,NitFact,IdRuta,IdClase,NumCuenta,IdBanco,CdMney,CdDct,CdRet,CdRiv,CdCms,ExcIva,TrfIntMora,DiasGracia,LiqFletes,FactSold,Autoret
,IncRet,IncRiv,IncIca,FactTipo,VrCupo,VrSaldo,UidClie,PwdClie,CodClieSicom,FecIngreso,FecVigencia,FecRetiro,MatMerc,FecMat,PathFoto,PathFirma,Cmntario1,Cmntario2,PlazosImp,IdEstado,TipoCliente,Inactivo,CdBandera,Contrato,NContrato,CiaContMay,Cmntario3,PrendGarant,FecUpCupo,FechaAdd,IdUsuario
,CupoGalones,Restric_Cia,CdPlazoComb,FecPlazoDoc,EdoRadicaDoc,CdTipBloq,DescEdoDoc,ComIndustrial,NumLista,Termicas)
 VALUES (@pmIdClie,@pmNitRepLeg,@pmNomRepLeg,@pmNitContac,@pmNomContac,@pmTelContac,@pmemlContac,@pmCargContac,@pmDirEnv,@pmIdLocEnv,@pmDiasEntga,@pmIdSzona,@pmIdGrupo,@pmIdPlazo,@pmIdForma,@pmIdEstrato,@pmIdVend,@pmNitFact,@pmIdRuta,@pmIdClase,@pmNumCuenta
,@pmIdBanco,@pmCdMney,@pmCdDct,@pmCdRet,@pmCdRiv,@pmCdCms,@pmExcIva,@pmTrfIntMora,@pmDiasGracia,@pmLiqFletes,@pmFactSold,@pmAutoret,@pmIncRet,@pmIncRiv,@pmIncIca,@pmFactTipo,@pmVrCupo,@pmVrSaldo,@pmUidClie,@pmPwdClie,@pmCodClieSicom,@pmFecIngreso,@pmFecVigencia,@pmFecRetiro
,@pmMatMerc,@pmFecMat,@pmPathFoto,@pmPathFirma,@pmCmntario1,@pmCmntario2,@pmPlazosImp,@pmIdEstado,@pmTipoCliente,@pmInactivo,@pmCdBandera,@pmContrato,@pmNContrato,@pmCiaContMay,@pmCmntario3,@pmPrendGarant,@pmFecUpCupo,@pmFechaAdd,@pmIdUsuario,@pmCupoGalones,@pmRestric_Cia
,@pmCdPlazoComb,@pmFecPlazoDoc,@pmEdoRadicaDoc,@pmCdTipBloq,@pmDescEdoDoc,@pmComIndustrial,@pmNumLista,@pmTermicas)



GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[paQrytm_BalComBases] @pmtmEst CHAR(2),@pmtmIdCuenta VARCHAR(16)=Null
,@pmtmIdTercero VARCHAR(16)=Null,@pmtmCodAgncia VARCHAR(16)=Null,@pmFechaIni SMALLDATETIME=Null,@pmFechaFin SMALLDATETIME=Null
,@pmIdCia CHAR(2)=Null
 AS 
 SELECT BC.tmIdCuenta AS IdCuent,NomCuenta,tmTipo,tmSaldoAnterior,tmTotalDebitos,tmTotalCreditos
,tmSaldoAnterior+tmTotalDebitos-tmTotalCreditos AS NuevoSaldo,tmVrPresupto,tmVrCompras
--bases acumuladas del año
,CASE P.BaseGravable WHEN 1 THEN ISNULL(CASE tmTipo WHEN 'N' THEN dbo.FuncComDetalleBasesNit(BC.tmIdCuenta,tmIdTercero,@pmFechaIni,@pmFechaFin,@pmIdCia)
WHEN 'NC' THEN dbo.FuncComDetalleBasesNitCc(BC.tmIdCuenta,tmIdTercero,tmIdCCosto,@pmFechaIni,@pmFechaFin,@pmIdCia)
WHEN 'NCS' THEN dbo.FuncComDetalleBasesNitCcSub(BC.tmIdCuenta,tmIdTercero,tmIdCCosto,tmIdSubCos,@pmFechaIni,@pmFechaFin,@pmIdCia)
WHEN 'CS' THEN dbo.FuncComDetalleBasesCcSub(BC.tmIdCuenta,tmIdCCosto,tmIdSubCos,@pmFechaIni,@pmFechaFin,@pmIdCia)
WHEN 'C' THEN dbo.FuncComDetalleBasesCc(BC.tmIdCuenta,tmIdCCosto,@pmFechaIni,@pmFechaFin,@pmIdCia)
ELSE 
 dbo.FuncComDetalleBasesCue (BC.tmIdCuenta,@pmFechaIni,@pmFechaFin,@pmIdCia)
END,0) ELSE 0 END AS TotalBase,Porcntaje
,tmIdTercero,RazonSocial,tmCodAgncia,A.Agencia AS NomAgencia,tmIdVehiculo,tmIdCCosto,CCosto,tmIdSubCos,SubCosto
--información del tercero
,TipoId,Dv,Direccion,T.IdLocal AS CdCiudad,Localidad,L.IdDep AS CdDepto,Departamento,Telefono,EsAccnista,EsCliente,EsPropietario 
--información de agencia y vehículos
,CodAgencia,Referencia--,NumVeh
,tmItem,tmNivel1,tmNivel2,tmNivel4,tmNivel6,tmNivel9,tmNivel12,Tercero,CentroCosto
 FROM tm_BalCom  AS  BC INNER JOIN Puc AS P ON BC.tmIdCuenta=P.IdCuenta
 INNER JOIN Terceros AS T ON BC.tmIdTercero=T.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep
 INNER JOIN CentroCosto AS CC ON BC.tmIdCCosto=CC.IdCCosto
 LEFT JOIN SubCentros AS SC ON BC.tmIdSubCos=SC.IdSubCos
 LEFT JOIN tm_NivCue AS NV ON BC.tmIdCuenta=NV.tmIdCuenta
 LEFT JOIN  Agencias AS A ON BC.tmCodAgncia=A.IdAgencia
-- LEFT JOIN Vehiculos AS V ON BC.tmIdVehiculo=V.IdVehiculo
 WHERE tmEst=@pmtmEst AND BC.tmIdCuenta LIKE ISNULL(@pmtmIdCuenta,'%')
 AND tmIdTercero LIKE ISNULL(@pmtmIdTercero,'%')
 AND tmCodAgncia LIKE ISNULL(@pmtmCodAgncia,'%')
 ORDER BY BC.tmIdCuenta,RazonSocial,tmCodAgncia,tmIdCCosto,tmItem