if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsSalidas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsSalidas]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTerceros]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsTerceros]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTerceros_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsTerceros_Sel]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryKardexCc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryKardexCc]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryKardexProCc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryKardexProCc]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryRequisicionOrd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryRequisicionOrd]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrySalidas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQrySalidas]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrySalidasLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQrySalidasLta]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrySalidasNet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQrySalidasNet]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrySalidasRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQrySalidasRel]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrySalidas_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQrySalidas_Cr]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTerceros]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTerceros]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTercerosBus]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTercerosBus]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTercerosCod]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTercerosCod]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTercerosDso]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTercerosDso]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_KdexCc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQrytm_KdexCc]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_ResMensualCueN]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQrytm_ResMensualCueN]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTerceros]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpTerceros]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpSalidas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpSalidas]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsSalidas @pmTipDoc VARCHAR(3),@pmSalida INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdTercero VARCHAR(16)
,@pmVrSubTotal MONEY,@pmVrImpuesto MONEY,@pmVrCostos MONEY,@pmVrNeto MONEY,@pmCantidad DECIMAL(14,4),@pmBaseImp MONEY,@pmTarifaIva DECIMAL(14,4),@pmCodTarIva VARCHAR(4)
,@pmTipOrd VARCHAR(3),@pmNumOrden INT,@pmIdCiaOrd CHAR(2),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmpVehiculo VARCHAR(10),@pmModalidad VARCHAR(10)
,@pmCdConductor VARCHAR(16),@pmCdDep VARCHAR(4),@pmNContrato INT,@pmIdCiaCont CHAR(2),@pmNitCliente VARCHAR(16),@pmCdAgencia VARCHAR(16),@pmModRequis VARCHAR(10)
,@pmDirEntrega VARCHAR(250),@pmIdLocEnt VARCHAR(8),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME
,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmGuiaComb INT,@pmCdCiaGuia CHAR(2),@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_Salidas (TipDoc,Salida,IdCia,Fecha,IdConcepto,IdTercero,VrSubTotal,VrImpuesto,VrCostos,VrNeto,Cantidad,BaseImp,TarifaIva,CodTarIva,TipOrd,NumOrden,IdCiaOrd,IdCCosto,IdSubCos
,pVehiculo,CdConductor,CdDep,NContrato,IdCiaCont ,NitCliente,CdAgencia,ModRequis,DirEntrega,IdLocEnt,GuiaComb,CdCiaGuia
,Modalidad,OrigenAdd,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario)
 VALUES (@pmTipDoc,@pmSalida,@pmIdCia,@pmFecha,@pmIdConcepto,@pmIdTercero,@pmVrSubTotal,@pmVrImpuesto,@pmVrCostos,@pmVrNeto,@pmCantidad,@pmBaseImp,@pmTarifaIva
,@pmCodTarIva,@pmTipOrd,@pmNumOrden,@pmIdCiaOrd,@pmIdCCosto,@pmIdSubCos,@pmpVehiculo
,@pmCdConductor,@pmCdDep,@pmNContrato,@pmIdCiaCont,@pmNitCliente,@pmCdAgencia,@pmModRequis,@pmDirEntrega,@pmIdLocEnt,@pmGuiaComb,@pmCdCiaGuia
,@pmModalidad,@pmOrigenAdd,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmAnulado
,@pmNumDev,@pmFecDev,@pmObservacion,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsTerceros @pmIdTercero VARCHAR(16),@pmRazonSocial VARCHAR(250),@pmCodigo VARCHAR(16),@pmTipoId CHAR(1),@pmDv CHAR(1),@pmDireccion VARCHAR(250),@pmIdLocal VARCHAR(8)
,@pmTelefono VARCHAR(20),@pmFax VARCHAR(20),@pmTelMovil VARCHAR(20),@pmSitioWeb VARCHAR(100),@pme_mail VARCHAR(100),@pmEsCliente BIT,@pmEsVendedor BIT,@pmEsConductor BIT
,@pmEsPropietario BIT,@pmEsProveedor BIT,@pmEsEmpleado BIT,@pmEsOperario BIT,@pmEsAccnista BIT,@pmEsCiaAseg BIT,@pmIdSector VARCHAR(8),@pmIdRegimen VARCHAR(4),@pmIdLugarCed VARCHAR(8),@pmObservacion VARCHAR(250)
,@pmIniStgNom INT,@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmNomCial VARCHAR(250),@pmSiglaRaz VARCHAR(50),@pmIdProf VARCHAR(4),@pmTipEnte CHAR(1),@pmFecExpCed SMALLDATETIME,@pmEsCliePres BIT
,@pmFechaAdd SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Terceros (IdTercero,RazonSocial,Codigo,TipoId,Dv,Direccion,IdLocal,Telefono,Fax,TelMovil,SitioWeb,e_mail,EsCliente,EsVendedor,EsConductor,EsPropietario,EsProveedor,EsEmpleado,EsOperario,EsAccnista,EsCiaAseg,EsCliePres,IdSector,IdRegimen
,IdLugarCed,Observacion,IniStgNom,IdEstado,Inactivo,FechaAdd,IdUsuario,NomCial,SiglaRaz,IdProf,TipEnte,FecExpCed)
 VALUES (@pmIdTercero,@pmRazonSocial,@pmCodigo,@pmTipoId,@pmDv,@pmDireccion,@pmIdLocal,@pmTelefono,@pmFax,@pmTelMovil,@pmSitioWeb,@pme_mail,@pmEsCliente,@pmEsVendedor,@pmEsConductor,@pmEsPropietario,@pmEsProveedor
,@pmEsEmpleado,@pmEsOperario,@pmEsAccnista,@pmEsCiaAseg,@pmEsCliePres,@pmIdSector,@pmIdRegimen,@pmIdLugarCed,@pmObservacion,@pmIniStgNom,@pmIdEstado,@pmInactivo,@pmFechaAdd,@pmIdUsuario
,@pmNomCial,@pmSiglaRaz,@pmIdProf,@pmTipEnte,@pmFecExpCed)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsTerceros_Sel @pmIdTercero VARCHAR(16),@pmNewTercero VARCHAR(16),@pmNewCodigo VARCHAR(16)
 AS 
 INSERT INTO Terceros (IdTercero,RazonSocial,Codigo,TipoId,Dv,Direccion,IdLocal,Telefono,Fax,TelMovil,SitioWeb,e_mail,EsCliente,EsVendedor,EsConductor,EsPropietario,EsProveedor,EsEmpleado,EsOperario,EsAccnista,EsCiaAseg,EsCliePres,IdSector,IdRegimen
,IdLugarCed,Observacion,IniStgNom,IdEstado,Inactivo,FechaAdd,IdUsuario,NomCial,SiglaRaz,IdProf,TipEnte,FecExpCed)
 SELECT @pmNewTercero,RazonSocial,@pmNewCodigo,TipoId,Dv,Direccion,IdLocal,Telefono,Fax,TelMovil,SitioWeb,e_mail,EsCliente,EsVendedor,EsConductor,EsPropietario,EsProveedor,EsEmpleado,EsOperario,EsAccnista,EsCiaAseg,EsCliePres,IdSector,IdRegimen
,IdLugarCed,Observacion,IniStgNom,IdEstado,Inactivo,FechaAdd,IdUsuario,NomCial,SiglaRaz,IdProf,TipEnte,FecExpCed
 FROM  Terceros 
 WHERE IdTercero=@pmIdTercero
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryKardexCc @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2)
,@pmIdProducto VARCHAR(16)
 AS 
 SELECT IdProducto AS tmIdProducto,CdCCosto AS tmCdCCosto,CdSubCos AS tmCdSubCos
,CdLocal AS tmCdLocal,CodTarIca AS tmCodTarIca
 FROM  Trn_Kardex
 WHERE   TipDoc=@pmTipDoc AND Documento=@pmDocumento AND IdCia=@pmIdCia
  AND IdProducto LIKE ISNULL(@pmIdProducto,'%')
 ORDER BY Item
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryKardexProCc @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2)
 AS 
 SELECT IdSubgrupo,K.IdProducto AS tmIdProducto,DescripProd,TipoRef,IdProv,CdCCosto,CdSubCos,CodTarIca
,SUM(VrCostoEnt) AS SCOSENT,SUM(VrCostoSal) AS SCOSSAL
,SUM(VrPrecio*Entradas) AS SVALENT,SUM(VrPrecio*Salidas) AS SVALSAL
,SUM(VrBruto*Entradas) AS SBRUENT,SUM(VrBruto*Salidas) AS SBRUSAL
,SUM(CASE WHEN  Salidas>0 THEN VrIvaSal ELSE VrIvaEnt END) AS SIVA
,SUM(CASE WHEN  Salidas>0 THEN VrDctoSal ELSE VrDctoEnt END) AS SDCT
,SUM(CASE WHEN  Salidas>0 THEN VrReteSal ELSE VrReteEnt END) AS SRET
,SUM(CASE WHEN  Salidas>0 THEN VrIcaSal ELSE VrIcaEnt END) AS SICA
 FROM Trn_Kardex AS K INNER JOIN ProdMcias AS P ON K.IdProducto=P.IdProducto
 WHERE TipDoc=@pmTipDoc  AND Documento=@pmDocumento AND IdCia=@pmIdCia
 GROUP BY  IdSubgrupo,K.IdProducto,DescripProd,TipoRef,IdProv,CdCCosto,CdSubCos,CodTarIca
 ORDER BY IdSubgrupo,K.IdProducto
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryRequisicionOrd @pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdRespons VARCHAR(16)=Null,@pmNitCliente VARCHAR(16)=Null
 AS 
 SELECT R.Requisicion AS NumRequision,R.IdCia AS CdCia,Compania,Fecha,FechaVence,R.IdConcepto AS CdConcepto,Concepto,IdRespons,T.RazonSocial AS Responsable
,R.IdCCosto AS CdCentCosto,CCosto,R.IdSubCos AS CdSubCent,SubCosto,R.IdDep AS CdDep,Dependencia,R.NContrato AS NumContrato,IdCiaCont,NitCliente,CL.RazonSocial AS NomCliente,CdAgencia,Modalidad
,VrSubTotal,R.Cantidad AS CantTotal,DirEntrega,IdLocEnt,Localidad,NumAprob,FecAprob,R.Observacion AS Observ,R.IdEstado AS CdEstado,Estado
--detalles
,Item,CdProducto,D.Descripcion AS DescProd,D.IdSubgrupo AS CdSubgrupo,Subgrupo,CdBodega,Bodega,D.Cantidad AS CantArtic,D.IdUnd AS CdUnidad,Unidad,VrUnitario,TipOrd,NumOrden,IdCiaOrd
,D.TipSal AS TipoSal,D.NSalida AS NumSalida,D.IdCiaSal AS CdCiaSal,D.FechaSal AS FecSalida
--datos del cliente
,CL.TipoId AS TercTipo,CL.Dv AS TercDv,CL.Codigo AS TercCodigo,CL.NomCial AS TercNomCial,CL.SiglaRaz AS TercSigal,CL.Direccion AS TercDireccion
 FROM Trn_Requisicion AS R  INNER JOIN  Trn_ReqDetalle AS D ON R.TipDoc=D.TipDoc AND R.Requisicion=D.Requisicion AND R.IdCia=D.IdCia
 INNER JOIN Terceros AS T ON R.IdRespons=T.IdTercero  INNER JOIN  CentroCosto AS CC ON R.IdCCosto=CC.IdCCosto
 INNER JOIN  Conceptos AS C ON R.IdConcepto=C.IdConcepto INNER JOIN EstadoDoc AS ED ON R.IdEstado=ED.IdEstado
 INNER JOIN Companias AS CI ON R.IdCia=CI.IdCia INNER JOIN Dependencias AS DP ON R.IdDep=DP.IdDep
 INNER JOIN  SubGrupos AS SG ON D.IdSubgrupo=SG.IdSubgrupo  INNER JOIN UndMed AS UM ON D.IdUnd=UM.IdUnd 
 LEFT JOIN Terceros AS CL ON R.NitCliente =CL.IdTercero LEFT JOIN TercCliente AS CLI ON  R.NitCliente=CLI.IdClie
 LEFT  JOIN SubCentros AS SC ON R.IdSubCos=SC.IdSubCos LEFT JOIN Localidades AS LC ON R.IdLocEnt=LC.IdLocal
 LEFT JOIN Bodegas AS B ON D.CdBodega=B.IdBodega
 WHERE R.Fecha BETWEEN  @pmFechaIni AND @pmFechaFin AND Anulado=0 AND R.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND  IdRespons LIKE ISNULL(@pmIdRespons,'%') AND NitCliente LIKE ISNULL(@pmNitCliente,'%')
 ORDER BY  R.IdCia,R.Requisicion
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQrySalidas @pmTipDoc VARCHAR(3),@pmSalida INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Salida,IdCia,Fecha,IdConcepto,IdTercero,VrSubTotal,VrImpuesto,VrCostos,VrNeto,Cantidad,BaseImp
,TarifaIva,CodTarIva,TipOrd,NumOrden,IdCiaOrd,IdCCosto,IdSubCos,pVehiculo,Modalidad
,CdConductor,CdDep,NContrato,IdCiaCont ,NitCliente,CdAgencia,ModRequis,DirEntrega,IdLocEnt,GuiaComb,CdCiaGuia,OrigenAdd
,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,IdEstado,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_Salidas
 WHERE TipDoc=@pmTipDoc AND Salida=@pmSalida AND IdCia=@pmIdCia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQrySalidasLta @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME,@pmSalidaIni INT=Null,@pmSalidaFin INT=Null
,@pmIdCia CHAR(2)=Null,@pmIdTercero VARCHAR(16)=Null,@pmIdConcepto VARCHAR(4)=Null,@pmIdEstado VARCHAR(4)=Null,@pmAnulado BIT=Null
,@pmModalidad VARCHAR(10)=Null
 AS 
 SELECT Salida,IdCia,Fecha,S.IdConcepto AS CdConcepto,Concepto,S.IdTercero AS NitTercero,T.RazonSocial AS NomTercero,VrSubTotal,VrImpuesto,VrCostos,VrNeto,Cantidad,BaseImp,TarifaIva,CodTarIva
,TipOrd,NumOrden,IdCiaOrd,GuiaComb,CdCiaGuia,S.IdCCosto AS CdCcosto,CCosto,IdSubCos,pVehiculo,CdConductor,CdDep,NContrato,IdCiaCont ,NitCliente,CL.RazonSocial AS NomCliente,CdAgencia,ModRequis,DirEntrega,IdLocEnt
,Modalidad,OrigenAdd,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,S.Observacion AS Observ,S.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate
,IdCiaCrea,S.IdUsuario AS IdUsuari,Usuario,TipDoc
 FROM Trn_Salidas AS S INNER JOIN Terceros AS T ON S.IdTercero=T.IdTercero
 INNER JOIN  Conceptos AS C ON S.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON S.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON S.IdUsuario=U.IdUsuario
 INNER JOIN CentroCosto AS CC ON  S.IdCCosto=CC.IdCCosto
 LEFT JOIN Terceros AS CL ON S.NitCliente=CL.IdTercero
 WHERE TipDoc=@pmTipDoc AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
 AND Salida BETWEEN ISNULL(@pmSalidaIni,0) AND ISNULL(@pmSalidaFin,2147483647)
 AND IdCia LIKE ISNULL(@pmIdCia,'%%')  AND S.IdTercero LIKE ISNULL(@pmIdTercero,'%')
 AND S.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND S.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 AND Modalidad LIKE ISNULL(@pmModalidad,'%')
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 ORDER BY IdCia,Salida
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQrySalidasNet @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdTercero VARCHAR(16)=Null,@pmIdCCosto VARCHAR(16)=Null,@pmIdLocEnt VARCHAR(8)=Null,@pmModalidad VARCHAR(10)=Null
 AS 
 SELECT TipDoc,Salida,S.IdCia AS CdCia,Compania,Fecha,S.IdConcepto AS CdConcepto,Concepto,S.IdTercero AS NitTercero,T.RazonSocial AS NomTercero,VrSubTotal
,VrImpuesto,VrCostos,VrNeto,Cantidad,BaseImp,TarifaIva,TipOrd,NumOrden,IdCiaOrd,S.IdCCosto AS CdCentCosto,CCosto,S.IdSubCos AS CdSubCenCost,SubCosto
,pVehiculo,CdConductor,CDT.RazonSocial AS NomConductor,S.NContrato AS NumContrato,IdCiaCont,NitCliente,CLI.RazonSocial AS NomCliente
,S.CdAgencia AS IdAgncia,Agencia,CdDep,ModRequis,DirEntrega,IdLocEnt,LE.Localidad AS CiuEntrega
,Modalidad,GuiaComb,CdCiaGuia,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,S.Observacion AS Observ,S.IdEstado AS CdEstado,Estado
,TimeSys,S.FecUpdate AS Fec_Update,IdCiaCrea,S.IdUsuario AS CdUsuario,Usuario
--Información del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,Departamento
,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
 FROM Trn_Salidas AS S INNER JOIN Companias AS CN ON S.IdCia=CN.IdCia 
 INNER JOIN Terceros AS T ON S.IdTercero=T.IdTercero INNER JOIN  Conceptos AS C ON S.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON S.IdEstado=ED.IdEstado   INNER JOIN adm_Usuarios AS U ON S.IdUsuario=U.IdUsuario
 INNER JOIN CentroCosto AS CC ON S.IdCCosto=CC.IdCCosto
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep 
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 LEFT JOIN SubCentros AS SC ON S.IdSubCos=SC.IdSubCos
 LEFT JOIN Terceros AS CLI ON S.NitCliente=CLI.IdTercero
 LEFT JOIN Terceros AS CDT ON S.CdConductor=CDT.IdTercero
 LEFT JOIN Agencias AS A ON S.CdAgencia=A.IdAgencia
 LEFT JOIN Localidades AS LE ON S.IdLocEnt=LE.IdLocal
 WHERE TipDoc=@pmTipDoc  AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 AND S.IdCia LIKE ISNULL(@pmIdCia,'%%') AND S.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND S.IdTercero LIKE ISNULL(@pmIdTercero,'%') AND Modalidad LIKE ISNULL(@pmModalidad,'%')
 AND S.IdCCosto LIKE ISNULL(@pmIdCCosto,'%')  AND IdLocEnt LIKE ISNULL(@pmIdLocEnt,'%')
 UNION 
 SELECT TipDev,Devolucion,D.IdCia AS CdCia,Compania,D.Fecha AS FechaDev,D.IdConcepto AS CdConcepto,Concepto,D.IdTercero AS NitTercero,T.RazonSocial AS NomTercero,D.VrSubTotal AS SubTotal
,D.VrImpuesto AS Impuesto,D.VrCostos AS Costos,D.VrNeto AS TotalNeto,D.Cantidad AS CantDev,D.BaseImp AS VrBaseImp,TarifaIva,D.TipOrd AS TipoOrd,D.NumOrden AS NOrden,D.IdCiaOrd AS CodCiaOrden
,D.IdCCosto AS IdCenCosto,CCosto,D.IdSubCos AS IdSubCosto,SubCosto,D.pVehiculo AS PlacaVeh,CdConductor,CDT.RazonSocial AS NomConductor,D.NContrato AS NumContrato,D.IdCiaCont AS CodCiaContrato
,D.NitCliente AS Nit_Cliente,CLI.RazonSocial AS NomCliente,D.CdAgencia AS Id_Agencia,Agencia,CdDep,D.ModRequis AS ModdReq,DirEntrega,IdLocEnt,LE.Localidad AS CiuEntrega,D.Modalidad AS ModSalida
,GuiaComb,CdCiaGuia,D.TipCom AS TipoComp,D.Comprobante AS NumComp,D.IdCiaCom AS CodCiaComp,0,D.Salida AS NumSalida,FecDoc
,D.Observacion AS Observ,D.IdEstado AS CdEstado,Estado,D.TimeSys AS Fec_Add,D.FecUpdate AS Fec_Update,D.IdCiaCrea AS CdCiaCrea,D.IdUsuario AS IdUsuari,Usuario
--Información del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,Departamento
,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
 FROM Trn_DevSal AS D  INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia 
 INNER JOIN Terceros AS T ON D.IdTercero=T.IdTercero INNER JOIN  Conceptos AS C ON D.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON D.IdEstado=ED.IdEstado   INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
 INNER JOIN CentroCosto AS CC ON D.IdCCosto=CC.IdCCosto
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep 
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 INNER JOIN Trn_Salidas AS S ON D.TipDoc=S.TipDoc AND D.Salida=S.Salida AND D.IdCiaDoc=S.IdCia
 LEFT JOIN Terceros AS CLI ON D.NitCliente=CLI.IdTercero
 LEFT JOIN Terceros AS CDT ON S.CdConductor=CDT.IdTercero
 LEFT JOIN Agencias AS A ON D.CdAgencia=A.IdAgencia
 LEFT JOIN Localidades AS LE ON S.IdLocEnt=LE.IdLocal
 LEFT JOIN SubCentros AS SC ON D.IdSubCos=SC.IdSubCos
 WHERE D.TipDoc=@pmTipDoc  AND D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 AND D.IdCia LIKE ISNULL(@pmIdCia,'%%') AND S.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND D.IdTercero LIKE ISNULL(@pmIdTercero,'%') AND D.Modalidad LIKE ISNULL(@pmModalidad,'%')
 AND D.IdCCosto LIKE ISNULL(@pmIdCCosto,'%')  AND IdLocEnt LIKE ISNULL(@pmIdLocEnt,'%')
 ORDER BY S.IdCia,Salida
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQrySalidasRel @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdTercero VARCHAR(16)=Null,@pmIdCCosto VARCHAR(16)=Null,@pmIdLocEnt VARCHAR(8)=Null,@pmModalidad VARCHAR(10)=Null
,@pmAnulado BIT=Null
 AS 
 SELECT TipDoc,Salida,S.IdCia AS CdCia,Compania,Fecha,S.IdConcepto AS CdConcepto,Concepto,S.IdTercero AS NitTercero,T.RazonSocial AS NomTercero,VrSubTotal
,VrImpuesto,VrCostos,VrNeto,Cantidad,BaseImp,TarifaIva,TipOrd,NumOrden,IdCiaOrd,GuiaComb,CdCiaGuia,S.IdCCosto AS CdCentCosto,CCosto,S.IdSubCos AS CdSubCenCost,SubCosto
,pVehiculo,CdConductor,CDT.RazonSocial AS NomConductor,S.NContrato AS NumContrato,IdCiaCont,NitCliente,CLI.RazonSocial AS NomCliente
,S.CdAgencia AS IdAgncia,Agencia,CdDep,ModRequis,DirEntrega,IdLocEnt,LE.Localidad AS CiuEntrega
,Modalidad,OrigenAdd,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,S.Observacion AS Observ,S.IdEstado AS CdEstado,Estado
,TimeSys,S.FecUpdate AS Fec_Update,IdCiaCrea,S.IdUsuario AS CdUsuario,Usuario
--Información del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,Departamento
,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
 FROM Trn_Salidas AS S INNER JOIN Companias AS CN ON S.IdCia=CN.IdCia 
 INNER JOIN Terceros AS T ON S.IdTercero=T.IdTercero INNER JOIN  Conceptos AS C ON S.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON S.IdEstado=ED.IdEstado   INNER JOIN adm_Usuarios AS U ON S.IdUsuario=U.IdUsuario
 INNER JOIN CentroCosto AS CC ON S.IdCCosto=CC.IdCCosto
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep 
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 LEFT JOIN SubCentros AS SC ON S.IdSubCos=SC.IdSubCos
 LEFT JOIN Terceros AS CLI ON S.NitCliente=CLI.IdTercero
 LEFT JOIN Terceros AS CDT ON S.CdConductor=CDT.IdTercero
 LEFT JOIN Agencias AS A ON S.CdAgencia=A.IdAgencia
 LEFT JOIN Localidades AS LE ON S.IdLocEnt=LE.IdLocal
 WHERE TipDoc=@pmTipDoc  AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 AND S.IdCia LIKE ISNULL(@pmIdCia,'%%') AND S.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND S.IdTercero LIKE ISNULL(@pmIdTercero,'%') AND Modalidad LIKE ISNULL(@pmModalidad,'%')
 AND S.IdCCosto LIKE ISNULL(@pmIdCCosto,'%')  AND IdLocEnt LIKE ISNULL(@pmIdLocEnt,'%')
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 ORDER BY S.IdCia,Salida
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQrySalidas_Cr @pmTipDoc VARCHAR(3),@pmSalidaIni INT,@pmSalidaFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT S.TipDoc AS Tipo_Doc,TipoDoc,Salida,S.IdCia AS CdCia,Compania,S.Fecha AS FechaDoc,S.IdConcepto AS CdConcepto
,Concepto,S.IdTercero AS NitTercero,T.RazonSocial AS NomTercero,VrSubTotal,VrImpuesto,VrCostos,VrNeto,Cantidad,BaseImp,S.TarifaIva AS TarifIva
,S.TipOrd AS TipoOrden,S.NumOrden AS Num_Orden,S.IdCiaOrd AS CdCiaOrden,S.IdCCosto AS CdCentCosto,CC.CCosto AS CentCosto,S.IdSubCos AS CdSubCenCost,SC.SubCosto AS SubcCosto
,S.pVehiculo AS PlacaVeh,CdConductor,CDT.RazonSocial AS NomConductor,S.NContrato AS NumContrato,IdCiaCont
,NitCliente,CLI.RazonSocial AS NomCliente,S.CdAgencia AS IdAgncia,Agencia,CdDep,ModRequis,DirEntrega,IdLocEnt,LE.Localidad AS CiuEntrega,GuiaComb,CdCiaGuia
,Modalidad,OrigenAdd,TipCom,TipoCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,S.Observacion AS Observ,S.IdEstado AS CdEstado,Estado
,S.TimeSys AS Fec_Add,S.FecUpdate AS Fec_Update,IdCiaCrea,S.IdUsuario AS CdUsuario,Usuario,Leyenda
--Información del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,Departamento
,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
--Detalle
,Item,K.IdProducto AS CodProducto,DescripProd,K.IdBodega AS CdBodega,Bodega,Descrip AS BodDescrip,CdTanque,Entradas,Salidas,K.IdUnd AS CdUnid,UM.Unidad AS UnidMedida,VrUnitario,VrPrecio,VrCostProm,K.TarifaIva AS KarTarifIva,VrIvaEnt,VrIvaSal,TarifaDct,VrDctoEnt,VrDctoSal
,VrCostoEnt,VrCostoSal,K.TarifaRet AS KarTarifRet,VrReteEnt,VrReteSal,K.TarifaIca AS KarTarifIca,VrIcaEnt,VrIcaSal,VrBruto,CdUbic,NumLote,FechLote,K.IdTercero AS KarNitTercero,KT.RazonSocial AS KarNomTercero,K.CdAgencia AS KarIdAgencia,K.CdCCosto AS KarCodCenCosto,KC.CCosto AS Kar_Ccosto
,K.CdSubCos AS KarCodSubcosto,KS.SubCosto AS Kar_Subcentro,CdLocal,KL.Localidad AS KarCiudad,K.pVehiculo AS KarPlacaVeh,K.Referencia AS KarReferencia,Descripcion,Comptmntos,CdProdEquiv,K.TipOrd AS KarTipOrden,K.NumOrden AS KarNumOrden,K.IdCiaOrd AS KarCiaOrden,Cotizacion,IdCiaCot
,Remision,IdCiaRem,K.Factura AS KarFactura,TipDocDev,NumDocDev,Sobretasa,TasaNac,TasaDep,TasaMun,Soldicom,ImpGlobal
,OtroImpto,Unidades,ItemCombo,Servcios,EsCombo,EsProdBase,ListaPrec,VrBase,CdMoneda,VrTasaCamb,VrDivisa1,VrDivisa2,VrDivisa3,Referencia2,FecOrden
--productos
,PM.Referencia AS Prod_Referencia,TipoRef,PM.IdSubgrupo AS CdSubgrupo,Subgrupo,SG.IdGrupo AS CdGrupo,Grupo,G.IdLinea AS CdLinea,Linea,PM.IdMarca AS CdMarca,Marca,Tamano,PM.UndMed AS CdUndMed
,SU.Unidad AS ProdUnidad,ExtciaMin,ExtciaMax,ExtciaAct,VrCostAnt,VrCosto,VrCostPmd,Precio1,Precio2,Precio3,Precio4,Precio5,FecUltcom,FecUltVta,Seriales,Lotes,Combo,Tanques,DescripLong
 FROM Trn_Salidas AS S INNER JOIN Trn_Kardex AS K ON S.TipDoc=K.TipDoc AND S.Salida=K.Documento AND S.IdCia=K.IdCia
 INNER JOIN Companias AS CN ON S.IdCia=CN.IdCia  INNER JOIN Sys_TiposDoc AS TD ON S.TipDoc=TD.IdDoc
 INNER JOIN Terceros AS T ON S.IdTercero=T.IdTercero INNER JOIN  Conceptos AS C ON S.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON S.IdEstado=ED.IdEstado   INNER JOIN adm_Usuarios AS U ON S.IdUsuario=U.IdUsuario
 INNER JOIN CentroCosto AS CC ON S.IdCCosto=CC.IdCCosto
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep 
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 INNER JOIN ProdMcias  AS PM ON K.IdProducto=PM.IdProducto INNER JOIN Bodegas AS B ON K.IdBodega=B.IdBodega
 INNER JOIN UndMed AS UM ON K.IdUnd=UM.IdUnd INNER JOIN Terceros AS KT ON K.IdTercero=KT.IdTercero
 INNER JOIN SubGrupos AS SG ON PM.IdSubgrupo=SG.IdSubgrupo INNER JOIN Grupos AS G ON SG.IdGrupo=G.IdGrupo 
 INNER JOIN Lineas AS LN ON G.IdLinea=LN.IdLinea INNER JOIN Marcas AS M ON PM.IdMarca=M.IdMarca
 INNER JOIN Sys_Um AS SU ON PM.UndMed=SU.UndMed 
 LEFT JOIN CentroCosto AS KC ON K.CdCCosto=KC.IdCCosto LEFT JOIN SubCentros AS KS ON K.CdSubCos=KS.IdSubCos
 LEFT JOIN Localidades AS KL ON K.CdLocal=KL.IdLocal
 LEFT JOIN SubCentros AS SC ON S.IdSubCos=SC.IdSubCos
 LEFT JOIN TiposCom AS TC ON S.TipCom=TC.IdCom
 LEFT JOIN Terceros AS CLI ON S.NitCliente=CLI.IdTercero
 LEFT JOIN Terceros AS CDT ON S.CdConductor=CDT.IdTercero
 LEFT JOIN Agencias AS A ON S.CdAgencia=A.IdAgencia
 LEFT JOIN Localidades AS LE ON S.IdLocEnt=LE.IdLocal
 WHERE S.TipDoc=@pmTipDoc AND Salida BETWEEN @pmSalidaIni AND  @pmSalidaFin AND S.IdCia=@pmIdCia
 AND EsProdBase=0
 ORDER BY Salida,Item
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTerceros @pmIdTercero VARCHAR(16)
 AS 
 SELECT IdTercero,RazonSocial,Codigo,TipoId,Dv,NomCial,SiglaRaz,Direccion,IdLocal,Telefono,Fax,TelMovil,SitioWeb,e_mail,EsCliente,EsVendedor,EsConductor
,EsPropietario,EsProveedor,EsEmpleado,EsOperario,EsAccnista,EsCiaAseg,EsCliePres,IdSector,IdProf,IdRegimen,TipEnte,IdLugarCed,FecExpCed
,Observacion,IniStgNom,IdEstado,Inactivo,FechaAdd,FechaUpdate,IdUsuario
 FROM Terceros
 WHERE IdTercero=@pmIdTercero
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTercerosBus @pmCodigo VARCHAR(16)=Null,@pmTelefono VARCHAR(20)=Null
 AS 
--consultar por codigo o por telefono
 SELECT IdTercero,RazonSocial,Codigo,TipoId,Dv,NomCial,SiglaRaz,Direccion,IdLocal,Telefono,Fax,TelMovil,SitioWeb,e_mail,EsCliente,EsVendedor,EsConductor
,EsPropietario,EsProveedor,EsEmpleado,EsOperario,EsAccnista,EsCiaAseg,EsCliePres,IdProf,IdSector,IdRegimen,IdLugarCed,TipEnte,FecExpCed
,Observacion,IniStgNom,IdEstado,Inactivo,FechaAdd,FechaUpdate,IdUsuario
 FROM Terceros
 WHERE Codigo  LIKE ISNULL(@pmCodigo,'%') AND Telefono LIKE ISNULL(@pmTelefono,'%')
 ORDER BY RazonSocial
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTercerosCod @pmIdTercero VARCHAR(16),@pmCodigo VARCHAR(16)
 AS 
 SELECT IdTercero,RazonSocial,Codigo,TipoId,Dv,NomCial,SiglaRaz,Direccion,IdLocal,Telefono,Fax,TelMovil,SitioWeb,e_mail,EsCliente,EsVendedor,EsConductor
,EsPropietario,EsProveedor,EsEmpleado,EsOperario,EsAccnista,EsCiaAseg,EsCliePres,IdSector,IdRegimen,IdLugarCed,FecExpCed,TipEnte,IdProf
,Observacion,IniStgNom,IdEstado,Inactivo,FechaAdd,FechaUpdate,IdUsuario
 FROM Terceros
 WHERE IdTercero=@pmIdTercero OR Codigo=@pmCodigo
 ORDER BY  IdTercero
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTercerosDso @pmIdLocal VARCHAR(8)=Null,@pmIdSector VARCHAR(8)=Null,@pmIdEstado VARCHAR(4)=Null,@pmIdRegimen VARCHAR(4)=Null
,@pmInactivo BIT=Null,@pmEsCliente BIT=Null,@pmEsVendedor BIT=Null,@pmEsProveedor BIT=Null,@pmEsConductor BIT=Null,@pmEsPropietario BIT=Null,@pmEsAccnista BIT=Null
,@pmEsCiaAseg BIT=Null,@pmEsEmpleado BIT=Null,@pmEsOperario BIT=Null,@pmTipoId CHAR(1)=Null,@pmIdProf VARCHAR(4)=Null
 AS 
 SELECT IdTercero,Dv,TipoId,RazonSocial,T.Codigo AS Codtercero,NomCial,SiglaRaz,T.Direccion AS Dreccion,T.IdLocal AS CodCiud,L.Localidad AS Ciudad,D.Departamento AS Departmto,Telefono,Fax,TelMovil,SitioWeb,e_mail,EsCliente,EsVendedor,EsConductor,EsPropietario
,EsProveedor,EsEmpleado,EsOperario,EsAccnista,EsCiaAseg,EsCliePres,TipEnte,T.IdSector AS CodSector,SectorEco,T.IdProf AS CodProf,Profesion,T.IdRegimen AS IdRegmen,Regimen,IdLugarCed,LC.Localidad AS LugarCedula,DC.Departamento AS DptoLugcedula,FecExpCed,Observacion,T.IdEstado AS IdEstdo,Estado
,T.Inactivo AS Inactvo,FechaAdd,FechaUpdate,T.IdUsuario AS IdUsario,Usuario,IniStgNom,NColor
 FROM Terceros AS T 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 R ON T.IdRegimen=R.IdRegimen
 INNER JOIN EstadoTer AS ET ON T.IdEstado=ET.IdEstado
 INNER JOIN adm_Usuarios AS U ON T.IdUsuario=U.IdUsuario
 INNER JOIN Localidades AS LC ON T.IdLugarCed=LC.IdLocal INNER JOIN Departamentos AS DC ON LC.IdDep=DC.IdDep
 INNER JOIN Profesiones AS PF ON T.IdProf=PF.IdProf 
 WHERE T.IdLocal LIKE ISNULL(@pmIdLocal,'%') AND T.IdSector LIKE ISNULL(@pmIdSector,'%') AND T.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 AND T.IdRegimen LIKE ISNULL(@pmIdRegimen,'%') AND TipoId LIKE ISNULL(@pmTipoId,'%')
 AND T.IdProf LIKE ISNULL(@pmIdProf,'%')
AND (T.Inactivo=ISNULL(@pmInactivo,0) or T.Inactivo=ISNULL(@pmInactivo,1))
AND (EsCliente=ISNULL(@pmEsCliente,0) or EsCliente=ISNULL(@pmEsCliente,1))
AND (EsVendedor=ISNULL(@pmEsVendedor,0) or EsVendedor=ISNULL(@pmEsVendedor,1))
AND (EsProveedor=ISNULL(@pmEsProveedor,0) or EsProveedor=ISNULL(@pmEsProveedor,1))
AND (EsConductor=ISNULL(@pmEsConductor,0) or EsConductor=ISNULL(@pmEsConductor,1))
AND (EsPropietario=ISNULL(@pmEsPropietario,0) or EsPropietario=ISNULL(@pmEsPropietario,1))
AND (EsAccnista=ISNULL(@pmEsAccnista,0) or EsAccnista=ISNULL(@pmEsAccnista,1))
AND (EsCiaAseg=ISNULL(@pmEsCiaAseg,0) or EsCiaAseg=ISNULL(@pmEsCiaAseg,1))
AND (EsEmpleado=ISNULL(@pmEsEmpleado,0) or EsEmpleado=ISNULL(@pmEsEmpleado,1))
AND (EsOperario=ISNULL(@pmEsOperario,0) or EsOperario=ISNULL(@pmEsOperario,1))
ORDER BY RazonSocial
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQrytm_KdexCc @pmtmNumero VARCHAR(5),@pmtmIdProducto VARCHAR(16)
 AS 
 SELECT tmNumero,tmItem,tmIdProducto,tmCdCCosto,tmCdSubCos,tmCdLocal,tmCodTarIca
 FROM tm_Kdex
 WHERE tmNumero=@pmtmNumero AND tmIdProducto LIKE ISNULL(@pmtmIdProducto,'%')
 ORDER BY tmItem
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQrytm_ResMensualCueN @pmtmEst CHAR(2),@pmtmTipo INT
 AS 
 SELECT  tmCodigo,NomCuenta,tmIdCia,Compania,tmIdTercero,RazonSocial,tmCdAgencia,A.Agencia AS NomAgencia
,tmCdCCosto,CCosto,tmCdSubCos,SubCosto,tmpVehiculo
,tmEneSaldoAnt,tmEneDebitos,tmEneCreditos,tmFebSaldoAnt,tmFebDebitos,tmFebCreditos,tmMarSaldoAnt,tmMarDebitos,tmMarCreditos,tmAbrSaldoAnt,tmAbrDebitos,tmAbrCreditos
,tmMaySaldoAnt,tmMayDebitos,tmMayCreditos,tmJunSaldoAnt,tmJunDebitos,tmJunCreditos,tmJulSaldoAnt,tmJulDebitos,tmJulCreditos,tmAgoSaldoAnt,tmAgoDebitos,tmAgoCreditos
,tmSepSaldoAnt,tmSepDebitos,tmSepCreditos,tmOctSaldoAnt,tmOctDebitos,tmOctCreditos,tmNovSaldoAnt,tmNovDebitos,tmNovCreditos,tmDicSaldoAnt,tmDicDebitos,tmDicCreditos
,tmM13SaldoAnt,tmM13Debitos,tmM13Creditos
,tmNivel1,tmNivel2,tmNivel4,tmNivel6,tmNivel9,tmNivel12
 FROM tm_ResMensual  AS TR INNER JOIN Puc AS P ON TR.tmCodigo=P.IdCuenta
 LEFT  JOIN Companias AS CN ON  TR.tmIdCia=CN.IdCia  LEFT JOIN Terceros AS T ON TR.tmIdTercero=T.IdTercero
 LEFT JOIN Agencias  AS A ON TR.tmCdAgencia=A.IdAgencia
 LEFT JOIN CentroCosto AS CC ON TR.tmCdCCosto=CC.IdCCosto
 LEFT JOIN SubCentros AS SC ON TR.tmCdSubCos=SC.IdSubCos
 LEFT JOIN tm_NivCue AS NV ON TR.tmCodigo=NV.tmIdCuenta
 WHERE tmEst=@pmtmEst AND  tmTipo=@pmtmTipo 
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paUpTerceros @pmIdTercero VARCHAR(16),@pmRazonSocial VARCHAR(250),@pmCodigo VARCHAR(16),@pmTipoId CHAR(1),@pmDv CHAR(1),@pmDireccion VARCHAR(250),@pmIdLocal VARCHAR(8)
,@pmTelefono VARCHAR(20),@pmFax VARCHAR(20),@pmTelMovil VARCHAR(20),@pmSitioWeb VARCHAR(100),@pme_mail VARCHAR(100),@pmEsCliente BIT,@pmEsVendedor BIT,@pmEsConductor BIT
,@pmEsPropietario BIT,@pmEsProveedor BIT,@pmEsEmpleado BIT,@pmEsOperario BIT,@pmEsAccnista BIT,@pmEsCiaAseg BIT,@pmIdSector VARCHAR(8),@pmIdRegimen VARCHAR(4),@pmIdLugarCed VARCHAR(8),@pmObservacion VARCHAR(250)
,@pmIniStgNom INT,@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmNomCial VARCHAR(250),@pmSiglaRaz VARCHAR(50),@pmIdProf VARCHAR(4),@pmTipEnte CHAR(1),@pmFecExpCed SMALLDATETIME,@pmEsCliePres BIT,@pmFechaUpdate SMALLDATETIME
 AS 
 UPDATE Terceros SET RazonSocial=@pmRazonSocial,Codigo=@pmCodigo,TipoId=@pmTipoId,Dv=@pmDv,Direccion=@pmDireccion,IdLocal=@pmIdLocal,Telefono=@pmTelefono,Fax=@pmFax,TelMovil=@pmTelMovil,SitioWeb=@pmSitioWeb,e_mail=@pme_mail
,EsCliente=@pmEsCliente,EsVendedor=@pmEsVendedor,EsConductor=@pmEsConductor,EsPropietario=@pmEsPropietario,EsProveedor=@pmEsProveedor,EsEmpleado=@pmEsEmpleado,EsOperario=@pmEsOperario,EsAccnista=@pmEsAccnista,EsCiaAseg=@pmEsCiaAseg 
,EsCliePres=@pmEsCliePres,IdSector=@pmIdSector,IdRegimen=@pmIdRegimen,IdLugarCed=@pmIdLugarCed,Observacion=@pmObservacion,IniStgNom=@pmIniStgNom,IdEstado=@pmIdEstado,Inactivo=@pmInactivo,FechaUpdate=@pmFechaUpdate
,NomCial=@pmNomCial,SiglaRaz=@pmSiglaRaz,IdProf=@pmIdProf,TipEnte=@pmTipEnte,FecExpCed=@pmFecExpCed
 WHERE IdTercero=@pmIdTercero
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paUpSalidas @pmTipDoc VARCHAR(3),@pmSalida INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdTercero VARCHAR(16),@pmVrSubTotal MONEY
,@pmVrImpuesto MONEY,@pmVrCostos MONEY,@pmVrNeto MONEY,@pmCantidad DECIMAL(14,4),@pmBaseImp MONEY,@pmTarifaIva DECIMAL(14,4),@pmCodTarIva VARCHAR(4),@pmTipOrd VARCHAR(3)
,@pmNumOrden INT,@pmIdCiaOrd CHAR(2),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmpVehiculo VARCHAR(10),@pmModalidad VARCHAR(10)
,@pmCdConductor VARCHAR(16),@pmCdDep VARCHAR(4),@pmNContrato INT,@pmIdCiaCont CHAR(2),@pmNitCliente VARCHAR(16),@pmCdAgencia VARCHAR(16),@pmModRequis VARCHAR(10)
,@pmDirEntrega VARCHAR(250),@pmIdLocEnt VARCHAR(8),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME
,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmGuiaComb INT,@pmCdCiaGuia CHAR(2),@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_Salidas SET Fecha=@pmFecha,IdConcepto=@pmIdConcepto,IdTercero=@pmIdTercero,VrSubTotal=@pmVrSubTotal,VrImpuesto=@pmVrImpuesto,VrCostos=@pmVrCostos,VrNeto=@pmVrNeto,Cantidad=@pmCantidad
,BaseImp=@pmBaseImp,TarifaIva=@pmTarifaIva,CodTarIva=@pmCodTarIva,TipOrd=@pmTipOrd,NumOrden=@pmNumOrden,IdCiaOrd=@pmIdCiaOrd,IdCCosto=@pmIdCCosto,IdSubCos=@pmIdSubCos,pVehiculo=@pmpVehiculo
,CdConductor=@pmCdConductor,CdDep=@pmCdDep,NContrato=@pmNContrato,IdCiaCont=@pmIdCiaCont,NitCliente=@pmNitCliente,CdAgencia=@pmCdAgencia,ModRequis=@pmModRequis
,DirEntrega=@pmDirEntrega,IdLocEnt=@pmIdLocEnt,Modalidad=@pmModalidad,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,Anulado=@pmAnulado,NumDev=@pmNumDev
,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado,GuiaComb=@pmGuiaComb,CdCiaGuia=@pmCdCiaGuia,FecUpdate=@pmFecUpdate
 WHERE TipDoc=@pmTipDoc AND Salida=@pmSalida AND IdCia=@pmIdCia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO