INSERT INTO Sys_Entidades (IdTabla,NivelRel,CamposPK,FngKey,Sp_Qry,Sp_Qpmt,Sp_Ins,Sp_Upd,Sp_Del)
 VALUES ('TiposRespFis',1,'IdResFis',0,'','','','','')
GO
INSERT INTO Sys_Relaciones  (IdTabla,TablaForanea,CampoForaneo,BasedeDatos,NoValidar)
 VALUES ('Terceros','TercRespFiscal','IdTercero','DB',0)
INSERT INTO Sys_Relaciones  (IdTabla,TablaForanea,CampoForaneo,BasedeDatos,NoValidar)
 VALUES ('TiposRespFis','TercRespFiscal','IdResFis','DB',0)
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEntradasNetDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryEntradasNetDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrySalidasNetDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrySalidasNetDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEdsFormasCos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryEdsFormasCos]
GO


SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryEntradasNetDet] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
AS 
 SELECT E.TipDoc,E.Entrada,E.IdCia AS CdCia,Compania,E.Fecha,E.IdConcepto AS CdConcepto,Concepto,E.IdProv AS NitPrevee,T.RazonSocial AS NomProveedor,E.Factura,E.FechaFac,E.FechaVence
,E.VrSubTotal,E.VrDescuento,E.VrImpuesto,E.VrRetencion,E.VrReteICA,E.VrReteIVA,E.VrReteCREE,E.VrFletes,E.VrOtros,E.VrSobretasa,E.VrImpGlobal,E.VrCargos,E.VrOtrDcto,E.VrBomberil,E.VrNeto
,E.NitFletes,TF.RazonSocial AS NomFletes,E.VrRetFlete,E.VrIcaFlete,E.FletesCosto,E.TipOdc,E.OCompra,E.IdCiaOdc,E.MulPlazos,E.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago,E.CxPagar,E.DocEquiv,E.BaseImp,E.BaseRet
,E.TarifaRet,E.TarifaIca,E.TarifaRiv,E.TarifaRtc,E.TarifaBom,E.VrIvaMvc,E.VrImpuCons,E.VrSobtasaCons,E.Modalidad
--detalles
 ,K.Item,K.IdProducto AS CdProducto,DescripProd,K.IdBodega AS CdBodega,Bodega,K.Entradas,K.Salidas,K.IdUnd AS CdUnid,UM.Unidad AS UnidMed,K.VrUnitario
,VrCostoEnt,VrCostoSal,K.TarifaDct,VrDctoEnt,VrDctoSal,K.TarifaIva,VrIvaEnt,VrIvaSal,K.Sobretasa,K.Soldicom,K.ImpGlobal,K.VrCostProm,K.Unidades,K.Descripcion,K.Referencia,Referencia2
,K.TipOrd,K.NumOrden,K.IdCiaOrd,TipDocDev,K.NumDocDev,OtroImpto,K.VrBruto,K.VrBase,K.Servcios,K.VrImvCosto,K.TarifaIco,K.VrImpCon,K.CantObseq,K.VrIvaObseq,K.BaseIvp,K.TarifaIvp,K.IvaIngProd,K.CdTanque,K.CdCCosto,CCosto,K.CdSubCos,SubCosto
,E.TipCom,E.Comprobante,E.IdCiaCom,E.Anulado,E.NumDev,FecDev,E.Observacion AS Observ,E.IdEstado AS CdEstado,Estado,E.TimeSys,E.FecUpdate AS Fec_Update,IdCiaCrea,E.IdUsuario AS CdUsuario,Usuario
--Información del tercero
,T.TipoId AS TercTipoId,T.Dv AS TercDV,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCodLocal,Localidad,L.IdDep AS CdDep,Departamento
,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail,T.IdSector AS TercCodSector,SectorEco,T.IdRegimen AS TercCodRegimen,Regimen,T.TipEnte AS TercTipoEnte
--Información del proveedor
,TP.IdGrupo AS ProvGrupo,GrupoClie,NitContac,NomContac,TelContac,emlContac,CargContac,DirOrdComp,DiasEntga,Autoret
 FROM Trn_Entradas AS E INNER JOIN Trn_Kardex AS K ON E.TipDoc=K.TipDoc AND E.Entrada=K.Documento AND E.IdCia=K.IdCia
 INNER JOIN Companias AS CN ON E.IdCia=CN.IdCia INNER JOIN Terceros AS T ON E.IdProv=T.IdTercero
 INNER JOIN Conceptos AS C ON E.IdConcepto=C.IdConcepto INNER JOIN EstadoDoc AS ED ON E.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON E.IdUsuario=U.IdUsuario INNER JOIN  Plazos AS PZ ON E.IdPlazo=PZ.IdPlazo
 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
 LEFT JOIN CentroCosto AS CC ON K.CdCCosto=CC.IdCCosto LEFT JOIN SubCentros AS SC ON K.CdSubCos=SC.IdSubCos
 LEFT JOIN TercProvee AS TP ON E.IdProv=TP.IdProv LEFT JOIN GruposCli AS GP ON TP.IdGrupo=GP.IdGrupo
 LEFT JOIN Terceros AS TF ON E.NitFletes=TF.IdTercero
 WHERE E.TipDoc=@pmTipDoc AND E.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND E.IdCia LIKE ISNULL(@pmIdCia,'%%')
 UNION ALL
 SELECT D.TipDev,D.Devolucion,D.IdCia,Compania,D.Fecha,D.IdConcepto,Concepto,D.IdProv,T.RazonSocial,D.Factura,E.FechaFac,E.FechaVence
,D.VrSubTotal,D.VrDescuento,D.VrImpuesto,D.VrRetencion,D.VrReteICA,D.VrReteIVA,D.VrReteCREE,D.VrFletes,D.VrOtros,D.VrSobretasa,D.VrImpGlobal,D.VrCargos,D.VrOtrDcto,D.VrBomberil,D.VrNeto
,D.NitFletes,TF.RazonSocial,D.VrRetFlete,D.VrIcaFlete,D.FletesCosto,E.TipOdc,E.OCompra,E.IdCiaOdc,MulPlazos,E.IdPlazo,Plazo,NVmto,DiasPago,D.CxPagar,D.DocEquiv,D.BaseImp,D.BaseRet
,E.TarifaRet,E.TarifaIca,E.TarifaRiv,D.TarifaRtc,D.TarifaBom,D.VrIvaMvc,D.VrImpuCons,D.VrSobtasaCons,D.Modalidad
,K.Item,K.IdProducto,DescripProd,K.IdBodega,Bodega,K.Entradas,K.Salidas,K.IdUnd,UM.Unidad,K.VrUnitario
,VrCostoEnt,VrCostoSal,K.TarifaDct,VrDctoEnt,VrDctoSal,K.TarifaIva,VrIvaEnt,VrIvaSal,K.Sobretasa,K.Soldicom,K.ImpGlobal,K.VrCostProm,K.Unidades,K.Descripcion,K.Referencia,Referencia2
,K.TipOrd,K.NumOrden,K.IdCiaOrd,TipDocDev,K.NumDocDev,OtroImpto,K.VrBruto,K.VrBase,K.Servcios,K.VrImvCosto,K.TarifaIco,K.VrImpCon,K.CantObseq,K.VrIvaObseq,K.BaseIvp,K.TarifaIvp,K.IvaIngProd,K.CdTanque,K.CdCCosto,CCosto,K.CdSubCos,SubCosto
,D.TipCom,D.Comprobante,D.IdCiaCom,0,D.Entrada,FecDoc,D.Observacion,D.IdEstado,Estado,D.TimeSys,D.FecUpdate,D.IdCiaCrea,D.IdUsuario,Usuario
,T.TipoId,T.Dv,T.Codigo,T.NomCial,T.Direccion,T.IdLocal,Localidad,L.IdDep,Departamento,T.Telefono,T.Fax,T.e_mail,T.IdSector,SectorEco,T.IdRegimen,Regimen,T.TipEnte
,TP.IdGrupo,GrupoClie,NitContac,NomContac,TelContac,emlContac,CargContac,DirOrdComp,DiasEntga,Autoret
 FROM Trn_DevEnt AS D INNER JOIN Trn_Entradas AS E ON D.TipDoc=E.TipDoc AND D.Entrada=E.Entrada AND D.IdCiaDoc=E.IdCia
 INNER JOIN Trn_Kardex AS K ON D.TipDev=K.TipDoc AND D.Devolucion=K.Documento AND D.IdCia=K.IdCia
 INNER JOIN Terceros AS T ON D.IdProv=T.IdTercero INNER JOIN  Conceptos AS CN ON D.IdConcepto=CN.IdConcepto 
 INNER JOIN Companias AS CI ON D.IdCia=CI.IdCia INNER JOIN EstadoDoc AS ED ON D.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario 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 Plazos AS PZ ON E.IdPlazo=PZ.IdPlazo
 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
 LEFT JOIN CentroCosto AS CC ON K.CdCCosto=CC.IdCCosto LEFT JOIN SubCentros AS SC ON K.CdSubCos=SC.IdSubCos
 LEFT JOIN TercProvee AS TP ON D.IdProv=TP.IdProv  LEFT JOIN GruposCli AS GP ON TP.IdGrupo=GP.IdGrupo
 LEFT JOIN Terceros AS TF ON D.NitFletes=TF.IdTercero
 WHERE D.TipDoc=@pmTipDoc AND D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND D.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrySalidasNetDet] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
AS 
 SELECT S.TipDoc,S.Salida,S.IdCia AS CdCia,Compania,S.Fecha,S.IdConcepto AS CdConcepto,Concepto,S.IdTercero AS NitTercero,T.RazonSocial AS NomTercero
,S.VrSubTotal,S.VrImpuesto,S.VrCostos,S.VrNeto,S.BaseImp,S.TarifaIva,S.TipOrd,S.NumOrden,S.IdCiaOrd
,S.pVehiculo,S.CdConductor,CDT.RazonSocial AS NomConductor,S.NContrato AS NumContrato,S.IdCiaCont,S.NitCliente,CLI.RazonSocial AS NomCliente
,S.CdAgencia AS IdAgncia,Agencia,S.CdDep AS CdDepend,S.ModRequis,S.DirEntrega,S.IdLocEnt,LE.Localidad AS CiuEntrega,S.Modalidad,S.GuiaComb,S.CdCiaGuia
--Detalles
,K.Item,K.IdProducto AS CdProducto,DescripProd,K.IdBodega AS CdBodega,Bodega,K.Entradas,K.Salidas,K.IdUnd AS CdUnid,UM.Unidad AS UnidMed,K.VrUnitario
,VrCostoEnt,VrCostoSal,K.TarifaIva AS DetTarifIva,VrIvaEnt,VrIvaSal,K.VrCostProm,K.Unidades,K.Descripcion,K.Referencia,Referencia2
,K.TipOrd as DetTipOrd,K.NumOrden AS DetNumOrden,K.IdCiaOrd AS DetIdCiaOrd,K.TipDocDev,K.NumDocDev,K.VrBruto,K.VrBase,K.Servcios,K.EsCombo,K.EsProdBase,K.NumLote,K.FechLote,K.CdTanque,K.CdCCosto AS CdCentCosto,CCosto,K.CdSubCos AS CdSubCenCost,SubCosto
,S.TipCom,S.Comprobante,S.IdCiaCom,S.Anulado,S.NumDev,S.FecDev,S.Observacion AS Observ,S.IdEstado AS CdEstado,Estado,S.TimeSys,S.FecUpdate AS Fec_Update,S.IdCiaCrea,S.IdUsuario AS CdUsuario,Usuario
,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 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 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 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
 LEFT JOIN CentroCosto AS CC ON K.CdCCosto=CC.IdCCosto LEFT JOIN SubCentros AS SC ON K.CdSubCos=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 S.TipDoc=@pmTipDoc  AND S.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND S.IdCia LIKE ISNULL(@pmIdCia,'%%')
 UNION ALL
 SELECT D.TipDev,D.Devolucion,D.IdCia,Compania,D.Fecha,D.IdConcepto,Concepto,D.IdTercero,T.RazonSocial,D.VrSubTotal
,D.VrImpuesto,D.VrCostos,D.VrNeto,D.BaseImp,S.TarifaIva,D.TipOrd,D.NumOrden,D.IdCiaOrd
,D.pVehiculo,S.CdConductor,CDT.RazonSocial,D.NContrato,D.IdCiaCont
,D.NitCliente,CLI.RazonSocial,D.CdAgencia,Agencia,CdDep,D.ModRequis,DirEntrega,IdLocEnt,LE.Localidad,D.Modalidad,GuiaComb,CdCiaGuia
,K.Item,K.IdProducto,DescripProd,K.IdBodega,Bodega,K.Entradas,K.Salidas,K.IdUnd,UM.Unidad,K.VrUnitario
,VrCostoEnt,VrCostoSal,K.TarifaIva,VrIvaEnt,VrIvaSal,K.VrCostProm,K.Unidades,K.Descripcion,K.Referencia,Referencia2
,K.TipOrd,K.NumOrden,K.IdCiaOrd,TipDocDev,K.NumDocDev,K.VrBruto,K.VrBase,K.Servcios,K.EsCombo,K.EsProdBase,K.NumLote,K.FechLote,K.CdTanque,K.CdCCosto,CCosto,K.CdSubCos,SubCosto
,D.TipCom,D.Comprobante,D.IdCiaCom,0,D.Salida,D.FecDoc,D.Observacion,D.IdEstado,Estado,D.TimeSys,D.FecUpdate,D.IdCiaCrea,D.IdUsuario,Usuario
,T.TipoId,T.Dv,T.Codigo,T.NomCial,T.Direccion,T.IdLocal,L.Localidad,L.IdDep,Departamento
,T.Telefono,T.Fax,T.e_mail,T.IdSector,SectorEco,T.IdRegimen,Regimen,T.TipEnte
 FROM Trn_DevSal AS D INNER JOIN Trn_Salidas AS S ON D.TipDoc=S.TipDoc AND D.Salida=S.Salida AND D.IdCiaDoc=S.IdCia
 INNER JOIN Trn_Kardex AS K ON D.TipDev=K.TipDoc AND D.Devolucion=K.Documento AND D.IdCia=K.IdCia
 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 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 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
 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 CentroCosto AS CC ON K.CdCCosto=CC.IdCCosto LEFT JOIN SubCentros AS SC ON K.CdSubCos=SC.IdSubCos
 WHERE D.TipDoc=@pmTipDoc  AND D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND D.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryEdsFormasCos] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
SELECT F.Planilla,F.IdCia,Compania,F.Fecha,F.IdCliente,T.RazonSocial AS NomCliente,F.IdAgencia,A.Agencia AS NomAgencia,F.CdProducto,DescripProd,F.Cantidad,F.VrTotal
,K.VrUnitario AS CostoUnid,K.VrUnitario*F.Cantidad AS CostoTotal,F.TipoRango,F.NumSerie,F.NumForma,F.Referencia,F.NumRef2,F.IdVend,V.RazonSocial AS Vendedor,F.pVehiculo,F.nVehiculo
FROM Trn_EdsFormas AS F LEFT JOIN (SELECT TipDoc,Documento,IdCia,Item,IdProducto,CdTanque,Entradas,Salidas,VrUnitario,VrPrecio,VrCostoEnt,VrCostoSal
FROM Trn_Kardex WHERE TipDoc='PLA' AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (IdCia=@pmIdCia OR @pmIdCia IS NULL)
) AS K ON F.Planilla=K.Documento AND F.IdCia=K.IdCia AND F.CdProducto=K.IdProducto
INNER JOIN Companias AS C ON F.IdCia=C.IdCia
INNER JOIN Terceros AS T ON F.IdCliente=T.IdTercero INNER JOIN Terceros AS V ON F.IdVend=V.IdTercero
INNER JOIN ProdMcias AS P ON F.CdProducto=P.IdProducto 
LEFT JOIN Agencias AS A ON F.IdAgencia=A.IdAgencia
WHERE F.Fecha BETWEEN @pmFechaIni AND @pmFechaFin --F.TipoRango='VAL'
AND (F.IdCia=@pmIdCia OR @pmIdCia IS NULL) AND F.Anulado=0
UNION ALL
SELECT P.Documento,P.IdCia,Compania,P.Fecha,P.NitCliente,T.RazonSocial,P.CdAgencia,A.Agencia,P.CdProducto,DescripProd,P.Cant_Gals,P.VrPagado
,K.VrUnitario AS CostoUnid,K.VrUnitario*P.Cant_Gals AS CostoTotal,P.IdForma,'',P.NumForma,P.Referncia1,P.Referncia2,'','',P.Referncia2,''
FROM Trn_Pagos AS P LEFT JOIN (SELECT TipDoc,Documento,IdCia,Item,IdProducto,CdTanque,Entradas,Salidas,VrUnitario,VrPrecio,VrCostoEnt,VrCostoSal
FROM Trn_Kardex WHERE TipDoc='PLA' AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (IdCia=@pmIdCia OR @pmIdCia IS NULL)
) AS K ON P.TipDoc=K.TipDoc AND P.Documento=K.Documento AND P.IdCia=K.IdCia AND P.CdProducto=K.IdProducto
INNER JOIN Companias AS C ON P.IdCia=C.IdCia INNER JOIN Terceros AS T ON P.NitCliente=T.IdTercero 
INNER JOIN ProdMcias AS PM ON P.CdProducto=PM.IdProducto 
LEFT JOIN Agencias AS A ON P.CdAgencia=A.IdAgencia
WHERE P.TipDoc='PLA' AND P.Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
AND (P.IdCia=@pmIdCia OR @pmIdCia IS NULL) AND P.FE_Ticket=1
GO