if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMttoConceptos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMttoConceptos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMttoConceptosLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMttoConceptosLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpMttoConceptos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpMttoConceptos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsMttoConceptos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsMttoConceptos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFactcon_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryFactcon_Cr]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryFactcon_Cr] @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 FechaFac,F.IdConcepto AS CdConcepto,Concepto
,nClieCon,Nombre,CC.TipoId AS CliTipoId,CC.Dv AS CliDV,CC.Direccion AS CliDireccion,CC.IdLocal AS CliCodCiudad,LE.Localidad AS CiudadClie,LE.IdDep AS CdDepClie,DE.Departamento AS DptoClie
,CC.Telefono AS CliTelefono,CC.email AS ClieEmail,Excento,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrFletes,VrCargos,VrOtrDcto,VrCostos,VrNeto,VrEfectivo,VrPagosOtr
,Cantidad,CantPuntos,PuntosAcum,BaseImp,BaseRet,BaseIca,BaseRiv,F.TarifaIva AS TarifIva,F.TarifaRet AS TarifRet,F.TarifaIca AS TarifIca,TarifaRiv,IdCajero,UC.Usuario AS UsuCajero,UC.IdEstacion AS UsuEstacion
,F.IdVend AS NitVendedor,VN.RazonSocial AS Vendedor,TarifaCom,NitCliente,T.RazonSocial AS NombreClie,F.CdAgencia AS CodAgencClie,Agencia,CodAgencia
,EnEfectivo,CdForma,NumForma,DetallePago,Referncia1,Referncia2,CdBanco,Banco,TipPed,Pedido,IdCiaPed,TipRem,F.Remision AS NumRemision,F.IdCiaRem AS CdCiaRem,FecPedido
,Modalidad,PlacaVehic,KmtVehic,VrImpCons,VrReteCREE,TarifaRtc,CodTarRtc,BaseIvaObsq,VrIvaObsequio,OrigenAdd,TipCom,Comprobante,TipoCom,IdCiaCom,Anulado,NumDev,FecDev,F.Observacion AS Observ,F.IdEstado AS CdEstado,Estado
,F.TimeSys AS Fec_Add,FecUpdate,IdCiaCrea,F.IdUsuario AS IdUsuari,U.Usuario AS NomUsuario,Leyenda
--datos del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion
,T.IdLocal AS TercCdCiudad,L.Localidad AS TercCiudad,L.IdDep AS CdDep,D.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
--Detalles
,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,CdCCosto,CdSubCos,pVehiculo,K.Referencia AS KarReferencia,Descripcion,TipOrd,NumOrden,IdCiaOrd
,K.Factura AS KarFactura,OtroImpto,Unidades,ItemCombo,Servcios,NoVentas,EsCombo,EsProdBase,ListaPrec,VrBase,CdMoneda,VrTasaCamb,VrDivisa1,VrDivisa2,VrDivisa3,Referencia2,K.TarifaIco,K.VrImpCon AS VrImpuCon
--productos
,PM.Referencia AS Prod_Referencia,TipoRef,PM.IdSubgrupo AS CdSubgrupo,Subgrupo,S.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,FecUltcom,FecUltVta,Seriales,Lotes,Combo,Tanques,DescripLong,Precio1,Precio2,Precio3,Precio4,Precio5,CantImp
,FE.EstadoFE, FE.Prefijo,FE.NumFace,FE.CUFE,FE.Resolucion,FE.RangoNum,FE.FecVigencia
 FROM Trn_Factcon AS F INNER JOIN Trn_Kardex AS K ON F.TipDoc=K.TipDoc AND F.Factura=K.Documento AND F.IdCia=K.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 EstadoDoc AS ED ON F.IdEstado=ED.IdEstado 
 INNER JOIN adm_Usuarios AS U ON F.IdUsuario=U.IdUsuario INNER JOIN adm_Usuarios AS UC ON F.IdCajero=UC.IdUsuario
 INNER JOIN Terceros AS VN ON F.IdVend=VN.IdTercero
 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 SubGrupos AS S ON PM.IdSubgrupo=S.IdSubgrupo INNER JOIN Grupos AS G ON S.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 ClieContado AS CC ON F.nClieCon=CC.IdCliente
 LEFT JOIN  Localidades AS LE ON CC.IdLocal=LE.IdLocal  LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep 
 LEFT JOIN Terceros AS T ON F.NitCliente=T.IdTercero  LEFT JOIN Agencias  AS A ON F.CdAgencia=A.IdAgencia
 LEFT JOIN Localidades AS L ON T.IdLocal=L.IdLocal  LEFT JOIN Departamentos AS D ON L.IdDep=D.IdDep 
 LEFT JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen LEFT JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 LEFT JOIN Bancos AS BF ON F.CdBanco=BF.IdBanco LEFT JOIN TiposCom AS TC ON F.TipCom=TC.IdCom
 LEFT JOIN Trn_DocMemo AS MM ON F.TipDoc=MM.TipDoc AND F.Factura=MM.Documento AND F.IdCia=MM.IdCia
 LEFT JOIN Trn_Face AS FE ON F.TipDoc=FE.TipDoc AND F.Factura=FE.Documento AND F.IdCia=FE.IdCia
 WHERE F.TipDoc=@pmTipDoc AND F.Factura BETWEEN @pmFacturaIni AND @pmFacturaFin AND F.IdCia=@pmIdCia
 AND EsProdBase=0
 ORDER BY F.Factura,Item
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsMttoConceptos] @pmIdConc VARCHAR(4),@pmConcepto VARCHAR(150),@pmIdSistema VARCHAR(4),@pmIdMtto VARCHAR(4),@pmTipoControl INT
,@pmIntervalo_km INT,@pmIntervalo_dias INT,@pmReq_Parte BIT,@pmInactivo BIT,@pmCodigoMO VARCHAR(16),@pmIntervalo_Horas INT
,@pmCantHoras DECIMAL(14,2),@pmTarifaCosto DECIMAL(16,4),@pmTarifaClie DECIMAL(16,4),@pmIntvAlarma VARCHAR(50),@pmFechaCrea SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO MttoConceptos (IdConc,Concepto,IdSistema,IdMtto,TipoControl,Intervalo_km,Intervalo_dias,Intervalo_Horas,Req_Parte,CodigoMO,CantHoras,TarifaCosto,Inactivo,FechaCrea,IdUsuario,TarifaClie,IntvAlarma)
 VALUES (@pmIdConc,@pmConcepto,@pmIdSistema,@pmIdMtto,@pmTipoControl,@pmIntervalo_km,@pmIntervalo_dias,@pmIntervalo_Horas,@pmReq_Parte,@pmCodigoMO,@pmCantHoras,@pmTarifaCosto,@pmInactivo,@pmFechaCrea,@pmIdUsuario,@pmTarifaClie,@pmIntvAlarma)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpMttoConceptos] @pmIdConc VARCHAR(4),@pmConcepto VARCHAR(150),@pmIdSistema VARCHAR(4),@pmIdMtto VARCHAR(4),@pmTipoControl INT
,@pmIntervalo_km INT,@pmIntervalo_dias INT,@pmReq_Parte BIT,@pmInactivo BIT,@pmCodigoMO VARCHAR(16),@pmIntervalo_Horas INT,@pmCantHoras DECIMAL(14,2)
,@pmTarifaCosto DECIMAL(16,4),@pmTarifaClie DECIMAL(16,4),@pmIntvAlarma VARCHAR(50)
 AS 
 UPDATE MttoConceptos SET Concepto=@pmConcepto,IdSistema=@pmIdSistema,IdMtto=@pmIdMtto,TipoControl=@pmTipoControl,Intervalo_km=@pmIntervalo_km
,Intervalo_dias=@pmIntervalo_dias,Req_Parte=@pmReq_Parte,Inactivo=@pmInactivo,CodigoMO=@pmCodigoMO,Intervalo_Horas=@pmIntervalo_Horas 
,CantHoras=@pmCantHoras,TarifaCosto=@pmTarifaCosto,TarifaClie=@pmTarifaClie,IntvAlarma=@pmIntvAlarma
 WHERE IdConc=@pmIdConc
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMttoConceptos] @pmIdConc VARCHAR(4)
 AS 
 SELECT IdConc,Concepto,IdSistema,IdMtto,TipoControl,Intervalo_km,Intervalo_dias,Req_Parte,Intervalo_Horas,CodigoMO
,CantHoras,TarifaCosto,TarifaClie,IntvAlarma,Inactivo,FechaCrea,IdUsuario
 FROM MttoConceptos
 WHERE IdConc=@pmIdConc
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMttoConceptosLta] @pmIdSistema VARCHAR(4)=Null,@pmIdMtto VARCHAR(4)=Null
 AS 
 SELECT IdConc,Concepto,C.IdSistema AS CdSistema,Sistema,C.IdMtto AS CdMtto,TipoMtto,TipoControl
,Intervalo_km,Intervalo_dias,Intervalo_Horas,Req_Parte,CodigoMO,DescripProd,CantHoras,TarifaCosto,TarifaClie,IntvAlarma
,C.Inactivo,C.FechaCrea,C.IdUsuario AS CdUsuario,Usuario
 FROM MttoConceptos AS C INNER JOIN MttoSistemas AS S ON C.IdSistema=S.IdSistema
 INNER JOIN MttoTipos AS TM ON C.IdMtto=TM.IdMtto
 INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario
 LEFT JOIN ProdMcias AS P ON C.CodigoMO=P.IdProducto
 WHERE C.IdSistema LIKE ISNULL(@pmIdSistema,'%') AND C.IdMtto LIKE ISNULL(@pmIdMtto,'%')
GO
