ALTER TABLE Trn_Face ALTER COLUMN NumFace DECIMAL(16,2)
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,CodTarArf,TarifaArf,VrAutRetFte,CodTarAri,TarifaAri,VrAutRetIca,TipoFE,FechaFE
,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,K.CantObseq,K.VrIvaObseq
--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,FE.CUFE_QR AS QR,FE.FechaValidacion
 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