CREATE TABLE tm_FactNeto ( 
tmEst CHAR(2) DEFAULT ('01') NOT NULL,
tmTipFac VARCHAR(3) NOT NULL,
tmFactura INT DEFAULT ((0)) NOT NULL,
tmIdCia CHAR(2) DEFAULT ('01') NOT NULL,
tmItem INT DEFAULT ((0)) NOT NULL,
tmSubTotal MONEY DEFAULT ((0)) NOT NULL,
tmDescuento MONEY DEFAULT ((0)) NOT NULL,
tmImpuesto MONEY DEFAULT ((0)) NOT NULL,
tmRetencion MONEY DEFAULT ((0)) NOT NULL,
tmReteICA MONEY DEFAULT ((0)) NOT NULL,
tmReteIVA MONEY DEFAULT ((0)) NOT NULL,
tmFletes MONEY DEFAULT ((0)) NOT NULL,
tmOtros MONEY DEFAULT ((0)) NOT NULL,
tmCargos MONEY DEFAULT ((0)) NOT NULL,
tmOtrDcto MONEY DEFAULT ((0)) NOT NULL,
tmCostos MONEY DEFAULT ((0)) NOT NULL,
tmSobretasa MONEY DEFAULT ((0)) NOT NULL,
tmImpGlobal MONEY DEFAULT ((0)) NOT NULL,
tmFaltantes MONEY DEFAULT ((0)) NOT NULL,
tmAnticipos MONEY DEFAULT ((0)) NOT NULL,
tmNeto MONEY DEFAULT ((0)) NOT NULL,
tmImpCons MONEY DEFAULT ((0)) NOT NULL,
tmReteCREE MONEY DEFAULT ((0)) NOT NULL,
tmEntradas DECIMAL(16,4) DEFAULT ((0)) NOT NULL,
tmSalidas DECIMAL(16,4) DEFAULT ((0)) NOT NULL,
tmTasaNac DECIMAL(16,4) DEFAULT ((0)) NOT NULL,
tmTasaDep DECIMAL(16,4) DEFAULT ((0)) NOT NULL,
tmTasaMun DECIMAL(16,4) DEFAULT ((0)) NOT NULL,
tmRec_Costo DECIMAL(16,4) DEFAULT ((0)) NOT NULL,
tmMgenCont DECIMAL(16,4) DEFAULT ((0)) NOT NULL,
tmIvaInf DECIMAL(16,4) DEFAULT ((0)) NOT NULL
 CONSTRAINT PK_tm_FactNeto PRIMARY KEY CLUSTERED (tmEst,tmTipFac,tmFactura,tmIdCia,tmItem),
 CONSTRAINT CK_tm_FactNetotmEst CHECK ((len([tmEst])>(0))),
 CONSTRAINT CK_tm_FactNetotmIdCia CHECK ((len([tmIdCia])>(0))),
 CONSTRAINT CK_tm_FactNetotmTipFac CHECK ((len([tmTipFac])>(0))))
GO
INSERT INTO Sys_ObjetosApp (IdObj,IdGrupo,SubModulo,IndObj,Nombre,Formulario,Permisos,NivelMinimo,NomArchivo)
 VALUES ('FRMLFCC','MAIINF','INV',19,'Resumen de Facturas - Neto','FRMLFCC','SNNNNNNNNNNNN',0,'')
GO
INSERT INTO Sys_Report (TipoRep,IdRep,Descripcion,Formato,TipoPapel,Orientacion,VistaPrevia,VerSetup,NumCopias,sp_Nombre,Ind_Location,NomRep)
 VALUES ('RFN','01','Predeterminado','CrLfn.rpt',1,2,1,0,1,'paQrytm_FactNeto','','Resumen de Facturas neto')
INSERT INTO Sys_Report (TipoRep,IdRep,Descripcion,Formato,TipoPapel,Orientacion,VistaPrevia,VerSetup,NumCopias,sp_Nombre,Ind_Location,NomRep)
 VALUES ('RFN','10','Detalles','CrLfnDet.rpt',1,2,1,0,1,'paQrytm_FactNetoDet','','Resumen de Facturas neto')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQrytm_FactNeto] @pmtmEst CHAR(2)
 AS 
 SELECT tmTipFac,tmFactura,tmIdCia,Compania,F.Fecha,F.FechaVence,T.TipoId,F.IdCliente,T.Dv,T.RazonSocial AS NomCliente,F.IdAgencia,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia
,tmEntradas,tmSalidas,tmSubTotal,tmDescuento,tmImpuesto,tmRetencion,tmReteICA,tmReteIVA,tmReteCREE,tmSobretasa,tmImpGlobal,tmOtros,tmCargos,tmOtrDcto,tmFletes,tmCostos
,tmFaltantes,tmAnticipos,tmImpCons,tmNeto AS VrNeto,F.BaseImp,F.BaseRet,F.TarifaIva,F.TarifaRet,F.TarifaIca,F.TarifaRiv,F.TarifaRtc,F.TipPed,F.Pedido,F.IdCiaPed
,F.TipRem,F.Remision,F.IdCiaRem,F.TipCot,F.Cotizacion,F.IdCiaCot,F.FecPedido,F.IdVend AS NitVend,VN.RazonSocial AS Vendedor,TarifaCom,F.pVehiculo AS PlacaVeh
,F.CdConductor,TC.RazonSocial AS Conductor,NitEmpTrans,EmpTrans,DirEnvio,F.IdLocEnv AS IdCiuEnvio,LE.Localidad AS CiudadEnvio,LE.IdDep AS CdDepEnvio,DE.Departamento AS DptoEnvio
,DiasEntraga,F.IdForma AS CdForma,FormaPago,DetallePago,MulPlazos,F.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago
,F.IdCCosto AS IdCenCost,CCosto,F.IdSubCos AS IdSubCent,SubCosto,Modalidad,F.IdConcepto AS CdConcepto,Concepto,F.TipCom,F.Comprobante,F.IdCiaCom
,F.Observacion,F.NumDev,F.FecDev,F.TimeSys AS FechaCrea,F.FecUpdate AS FechaAct,F.IdCiaCrea,F.IdUsuario AS CdUsuario,Usuario
--Información del tercero
,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,D.Departamento AS TercDpto
,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.TelMovil AS TercCelular,T.e_mail AS TercEmail,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
--información del cliente
,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie,CLI.TipoCliente,ExcIva,LiqFletes,CLI.FactSold AS FactSoldicom,Autoret,VrCupo,VrSaldo
,Contrato,CLI.NContrato AS CliNumContrato
,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
,A.CdFntePago AS CdFuente,FuentePago,TipoZona,A.CodSicom
,V.Observacion AS VehObservacion,V.Descripcion AS VehDescripcion
FROM tm_FactNeto AS TF INNER JOIN Trn_Facturas AS F ON TF.tmTipFac=F.TipDoc AND TF.tmFactura=F.Factura AND TF.tmIdCia=F.IdCia
INNER JOIN Companias AS CN ON TF.tmIdCia=CN.IdCia INNER JOIN Terceros AS T ON F.IdCliente=T.IdTercero
INNER JOIN Agencias AS A ON F.IdAgencia=A.IdAgencia INNER JOIN adm_Usuarios AS U ON F.IdUsuario=U.IdUsuario
INNER JOIN Conceptos AS C ON F.IdConcepto=C.IdConcepto INNER JOIN Plazos AS PZ ON F.IdPlazo=PZ.IdPlazo
INNER JOIN Terceros AS VN ON F.IdVend=VN.IdTercero INNER JOIN Formaspago AS FP ON F.IdForma=FP.IdForma
INNER JOIN CentroCosto AS CC ON F.IdCCosto=CC.IdCCosto 
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 Localidades AS LA ON A.IdLocal=LA.IdLocal INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
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 Localidades AS LE ON F.IdLocEnv=LE.IdLocal LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep
LEFT JOIN Terceros AS TC ON F.CdConductor=TC.IdTercero LEFT JOIN Vehiculos AS V ON F.pVehiculo=V.IdVehiculo 
LEFT JOIN SubCentros AS SC ON F.IdSubCos=SC.IdSubCos LEFT JOIN FuentesPago AS FPG ON A.CdFntePago=FPG.IdFuente
WHERE tmEst=@pmtmEst
--Detalles tmItem,tmTasaNac,tmTasaDep,tmTasaMun,tmRec_Costo,tmMgenCont,tmIvaInf
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQrytm_FactNetoDet] @pmtmEst CHAR(2)
 AS 
 SELECT tmTipFac,tmFactura,tmIdCia,Compania,F.Fecha,F.FechaVence,T.TipoId,F.IdCliente,T.Dv,T.RazonSocial AS NomCliente,F.IdAgencia,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia
,tmItem,K.IdProducto AS CdProducto,DescripProd,K.IdBodega AS CdBodega,Bodega,CdTanque,tmEntradas,tmSalidas,tmSubTotal,tmDescuento,tmImpuesto,tmRetencion,tmReteICA,tmReteIVA,tmReteCREE,tmSobretasa,tmTasaNac,tmTasaDep,tmTasaMun
,tmImpGlobal,tmOtros,tmRec_Costo,tmMgenCont,tmIvaInf,tmCargos,tmOtrDcto,tmFletes,tmCostos,tmFaltantes,tmAnticipos,tmImpCons,tmNeto AS VrNeto
,K.IdUnd AS CdUnid,UM.Unidad AS UnidMed,K.VrPrecio,K.Unidades,K.Referencia,K.Referencia2,K.CdLocal AS KarCdLocal,LE.Localidad AS KarLocalidad,LE.IdDep AS KarCodDep,DE.Departamento AS KarDpto
,K.CdCCosto,CCosto,K.CdSubCos,SubCosto,ListaPrec,K.VrBruto,K.VrBase,K.Servcios,K.NoVentas,K.EsCombo,K.EsProdBase,K.ItemCombo,PM.IdSubgrupo AS CdSubgrupo,Subgrupo,S.IdGrupo AS CdGrupo,Grupo
,G.IdLinea AS CdLinea,Linea,PM.CodBarras,DescripLong,DescripAbrv
,F.BaseImp,F.BaseRet,F.TarifaIva,F.TarifaRet,F.TarifaIca,F.TarifaRiv,F.TarifaRtc,F.TipPed,F.Pedido,F.IdCiaPed,F.TipRem,F.Remision,F.IdCiaRem,F.TipCot,F.Cotizacion,F.IdCiaCot,F.FecPedido
,F.IdVend AS NitVend,VN.RazonSocial AS Vendedor,F.TarifaCom,F.pVehiculo AS PlacaVeh,F.CdConductor,TC.RazonSocial AS Conductor,NitEmpTrans,EmpTrans,DirEnvio,DiasEntraga,F.IdForma AS CdForma,FormaPago,DetallePago
,MulPlazos,F.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago,Modalidad,F.IdConcepto AS CdConcepto,Concepto,F.TipCom,F.Comprobante,F.IdCiaCom
,F.Observacion,F.NumDev,F.FecDev,F.TimeSys AS FechaCrea,F.FecUpdate AS FechaAct,F.IdCiaCrea,F.IdUsuario AS CdUsuario,Usuario
--Información del tercero
,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,D.Departamento AS TercDpto
,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.TelMovil AS TercCelular,T.e_mail AS TercEmail,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
--información del cliente
,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie,CLI.TipoCliente,ExcIva,LiqFletes,CLI.FactSold AS FactSoldicom,Autoret,VrCupo,VrSaldo
,Contrato,CLI.NContrato AS CliNumContrato
,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
,A.CdFntePago AS CdFuente,FuentePago,TipoZona,A.CodSicom
,V.Observacion AS VehObservacion,V.Descripcion AS VehDescripcion
FROM tm_FactNeto AS TF INNER JOIN Trn_Facturas AS F ON TF.tmTipFac=F.TipDoc AND TF.tmFactura=F.Factura AND TF.tmIdCia=F.IdCia
INNER JOIN Trn_Kardex AS K ON TF.tmTipFac=K.TipDoc AND TF.tmFactura=K.Documento AND TF.tmIdCia=K.IdCia AND TF.tmItem=K.Item
INNER JOIN Companias AS CN ON TF.tmIdCia=CN.IdCia INNER JOIN Terceros AS T ON F.IdCliente=T.IdTercero
INNER JOIN Agencias AS A ON F.IdAgencia=A.IdAgencia INNER JOIN adm_Usuarios AS U ON F.IdUsuario=U.IdUsuario
INNER JOIN Conceptos AS C ON F.IdConcepto=C.IdConcepto INNER JOIN Plazos AS PZ ON F.IdPlazo=PZ.IdPlazo
INNER JOIN Terceros AS VN ON F.IdVend=VN.IdTercero INNER JOIN Formaspago AS FP ON F.IdForma=FP.IdForma
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 Localidades AS LA ON A.IdLocal=LA.IdLocal INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
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 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 
LEFT JOIN Terceros AS TC ON F.CdConductor=TC.IdTercero LEFT JOIN Vehiculos AS V ON F.pVehiculo=V.IdVehiculo 
LEFT JOIN FuentesPago AS FPG ON A.CdFntePago=FPG.IdFuente
LEFT JOIN CentroCosto AS CC ON K.CdCCosto=CC.IdCCosto LEFT JOIN SubCentros AS SC ON K.CdSubCos=SC.IdSubCos 
LEFT JOIN Localidades AS LE ON K.CdLocal=LE.IdLocal LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep
WHERE tmEst=@pmtmEst
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_FactNeto] @pmtmEst CHAR(2),@pmtmTipFac VARCHAR(3),@pmtmFactura INT,@pmtmIdCia CHAR(2),@pmtmItem INT,@pmtmSubTotal MONEY,@pmtmDescuento MONEY,@pmtmImpuesto MONEY,@pmtmRetencion MONEY,@pmtmReteICA MONEY,@pmtmReteIVA MONEY,@pmtmFletes MONEY,@pmtmOtros MONEY,@pmtmCargos MONEY,@pmtmOtrDcto MONEY,@pmtmCostos MONEY,@pmtmSobretasa MONEY,@pmtmImpGlobal MONEY,@pmtmFaltantes MONEY
,@pmtmAnticipos MONEY,@pmtmNeto MONEY,@pmtmImpCons MONEY,@pmtmReteCREE MONEY,@pmtmEntradas DECIMAL(16,4),@pmtmSalidas DECIMAL(16,4),@pmtmTasaNac DECIMAL(16,4),@pmtmTasaDep DECIMAL(16,4),@pmtmTasaMun DECIMAL(16,4),@pmtmRec_Costo DECIMAL(16,4),@pmtmMgenCont DECIMAL(16,4),@pmtmIvaInf DECIMAL(16,4)
 AS 
 INSERT INTO tm_FactNeto (tmEst,tmTipFac,tmFactura,tmIdCia,tmItem,tmSubTotal,tmDescuento,tmImpuesto,tmRetencion,tmReteICA,tmReteIVA,tmFletes,tmOtros,tmCargos,tmOtrDcto,tmCostos,tmSobretasa,tmImpGlobal,tmFaltantes,tmAnticipos,tmNeto,tmImpCons,tmReteCREE,tmEntradas,tmSalidas,tmTasaNac,tmTasaDep,tmTasaMun,tmRec_Costo,tmMgenCont,tmIvaInf)
 VALUES (@pmtmEst,@pmtmTipFac,@pmtmFactura,@pmtmIdCia,@pmtmItem,@pmtmSubTotal,@pmtmDescuento,@pmtmImpuesto,@pmtmRetencion,@pmtmReteICA,@pmtmReteIVA,@pmtmFletes,@pmtmOtros,@pmtmCargos,@pmtmOtrDcto,@pmtmCostos,@pmtmSobretasa,@pmtmImpGlobal,@pmtmFaltantes,@pmtmAnticipos,@pmtmNeto,@pmtmImpCons,@pmtmReteCREE,@pmtmEntradas,@pmtmSalidas,@pmtmTasaNac,@pmtmTasaDep,@pmtmTasaMun,@pmtmRec_Costo,@pmtmMgenCont,@pmtmIvaInf)
GO