--INSERT INTO adm_Opciones (IdOpc,Opcion,TipoDato,Valor,NivUp,nModulo)
--VALUES ('FED','CONCEPTO DE FACTURACION ELECTRONICA PREDETERMINADO EN NOTA DEBITO (Dígito Entre 0 y 3)','INTEGER','0',4,'MAIN')
--INSERT INTO adm_Opciones (IdOpc,Opcion,TipoDato,Valor,NivUp,nModulo)
--VALUES ('FEC','CONCEPTO DE FACTURACION ELECTRONICA PREDETERMINADO EN NOTA CREDITO (Dígito Entre 0 y 6)','INTEGER','0',4,'MAIN')
GO
INSERT INTO Sys_ObjetosApp (IdObj,IdGrupo,SubModulo,IndObj,Nombre,Formulario,Permisos,NivelMinimo,NomArchivo)
 VALUES ('FRMLFICA','MAIINF','GEN',11,'Informe General de Ingresos','FRMLFICA','SSSSSSSSSSSSS',0,'')
GO
INSERT INTO  Sys_Report (TipoRep,IdRep,Descripcion,Formato,TipoPapel,Orientacion,VistaPrevia,VerSetup,NumCopias,sp_Nombre,Ind_Location,NomRep)
 VALUES ('LGI','01','Ingresos (Hz)','CrLing.rpt',1,2,1,0,1,'paQryFacturasIca','','Listado General de Ingresos')
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_KdexSal]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_KdexSal]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_KdexDvs]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_KdexDvs]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_KdexSai]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_KdexSai]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_KdexTotFac]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_KdexTotFac]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFacturasIca]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryFacturasIca]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryFacturasIca] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdCliente VARCHAR(16)=Null
AS 
SELECT F.TipDoc,F.Factura,F.IdCia,Compania,F.Fecha,F.IdCliente,T.RazonSocial AS NomCliente,F.IdAgencia,A.Agencia AS NomAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrIvaObsequio,VrRetencion,VrReteICA,VrReteCREE,VrReteIVA,VrImpCons
,VrOtros,VrCargos,VrOtrDcto,VrFletes,F.VrNeto,BaseImp,BaseRet,BaseIca,BaseRiv,BaseIvaObsq,F.TarifaIva,F.TarifaRet,F.TarifaIca,F.TarifaRiv,F.TarifaRtc,F.CodTarIca
,F.IdLocEnv AS IdLocal,LE.Localidad,LE.IdDep AS CdDpto,DE.Departamento,F.IdVend,VN.RazonSocial AS Vendedor,F.TarifaCom,F.Modalidad,F.IdConcepto,Concepto,F.NitContac,F.NomContac,F.TipPed,F.Pedido,F.IdCiaPed,F.TipRem,F.Remision,F.IdCiaRem,F.TipCom,F.Comprobante,F.IdCiaCom
,F.Anulado,'' AS TipDev,F.NumDev,F.IdCiaCrea AS IdCiaDev,F.FecDev,F.Observacion,TipoDoc
--datos terceros
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.IdLocal AS TercCdCiudad,L.Localidad AS TercNomCiudad,L.IdDep AS TercCdDpto,DP.Departamento AS TercDpto,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
,CL.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CL.IdGrupo AS CdGrupoCli,GrupoClie,LEV.IdDep AS ClCdDptoEnv,DEV.Departamento AS ClDptoEnvio,CL.IdLocEnv AS ClIdCiuEnv,LEV.Localidad AS ClCiuEnvio,CL.ExcIva,CL.Autoret
,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto 
FROM Trn_Facturas AS F INNER JOIN Companias AS CN ON F.IdCia=CN.IdCia INNER JOIN Conceptos AS C ON F.IdConcepto=C.IdConcepto
INNER JOIN Terceros AS T ON F.IdCliente=T.IdTercero INNER JOIN Terceros AS VN ON F.IdVend=VN.IdTercero
INNER JOIN TercCliente AS CL ON F.IdCliente=CL.IdClie INNER JOIN Agencias AS A ON F.IdAgencia=A.IdAgencia
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 Subzonas AS SZ ON CL.IdSzona=SZ.IdSzona INNER JOIN Zonas AS Z ON SZ.IdZona=Z.IdZona
INNER JOIN GruposCli AS GC ON CL.IdGrupo=GC.IdGrupo INNER JOIN Sys_TiposDoc AS TD ON F.TipDoc=TD.IdDoc
INNER JOIN Localidades AS LEV ON CL.IdLocEnv=LEV.IdLocal INNER JOIN Departamentos AS DEV ON LEV.IdDep=DEV.IdDep
INNER JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep
INNER JOIN Localidades AS LE ON F.IdLocEnv=LE.IdLocal INNER JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep
WHERE F.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (F.IdCia=@pmIdCia OR @pmIdCia IS NULL)
AND (F.IdCliente=@pmIdCliente OR @pmIdCliente IS NULL)
UNION ALL
SELECT D.TipDev,D.Devolucion,D.IdCia,Compania,D.Fecha,D.IdCliente,T.RazonSocial,D.IdAgencia,A.Agencia,D.VrSubTotal,D.VrDescuento,D.VrImpuesto,D.VrIvaObsequio,D.VrRetencion,D.VrReteICA,D.VrReteCREE,D.VrReteIVA,D.VrImpCons
,D.VrOtros,D.VrCargos,D.VrOtrDcto,D.VrFletes,D.VrNeto,D.BaseImp,D.BaseRet,D.BaseRet,D.VrImpuesto,D.BaseIvaObsq,F.TarifaIva,F.TarifaRet,F.TarifaIca,F.TarifaRiv,D.TarifaRtc,F.CodTarIca
,D.IdLocEnv,LE.Localidad,LE.IdDep,DE.Departamento,D.IdVend,VN.RazonSocial,D.TarifaCom,D.Modalidad,D.IdConcepto,Concepto,'','','PED',D.Pedido,D.IdCiaPed,'REM',D.Remision,D.IdCiaRem,D.TipCom,D.Comprobante,D.IdCiaCom
,0,D.TipDoc,D.Factura,D.IdCiaDoc,D.FecDoc,D.Observacion,TipoDoc
--datos terceros
,T.TipoId,T.Dv,T.IdLocal,L.Localidad,L.IdDep,DP.Departamento,T.IdSector,SectorEco,T.IdRegimen,Regimen,T.TipEnte
,CL.IdSzona,Subzona,SZ.IdZona,Zona,CL.IdGrupo,GrupoClie,LEV.IdDep,DEV.Departamento,CL.IdLocEnv,LEV.Localidad,CL.ExcIva,CL.Autoret
,A.IdLocal,LA.Localidad,LA.IdDep,DA.Departamento
FROM Trn_DevFcr AS D INNER JOIN Trn_Facturas AS F ON D.TipDoc=F.TipDoc AND D.Factura=F.Factura AND D.IdCiaDoc=F.IdCia
INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia INNER JOIN Conceptos AS C ON D.IdConcepto=C.IdConcepto
INNER JOIN Terceros AS T ON D.IdCliente=T.IdTercero INNER JOIN Terceros AS VN ON D.IdVend=VN.IdTercero
INNER JOIN TercCliente AS CL ON D.IdCliente=CL.IdClie INNER JOIN Agencias AS A ON D.IdAgencia=A.IdAgencia
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 Subzonas AS SZ ON CL.IdSzona=SZ.IdSzona INNER JOIN Zonas AS Z ON SZ.IdZona=Z.IdZona
INNER JOIN GruposCli AS GC ON CL.IdGrupo=GC.IdGrupo INNER JOIN Sys_TiposDoc AS TD ON D.TipDev=TD.IdDoc
INNER JOIN Localidades AS LEV ON CL.IdLocEnv=LEV.IdLocal INNER JOIN Departamentos AS DEV ON LEV.IdDep=DEV.IdDep
INNER JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep
INNER JOIN Localidades AS LE ON D.IdLocEnv=LE.IdLocal INNER JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep
WHERE D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (D.IdCia=@pmIdCia OR @pmIdCia IS NULL)
AND (D.IdCliente=@pmIdCliente OR @pmIdCliente IS NULL)
UNION ALL
SELECT F.TipDoc,F.Factura,F.IdCia,Compania,F.Fecha,F.NitCliente,T.RazonSocial,F.CdAgencia,A.Agencia,VrSubTotal,VrDescuento,VrImpuesto,VrIvaObsequio,VrRetencion,VrReteICA,VrReteCREE,VrReteIVA,VrImpCons
,0,VrCargos,VrOtrDcto,VrFletes,VrNeto,BaseImp,BaseRet,BaseIca,BaseRiv,BaseIvaObsq,TarifaIva,TarifaRet,TarifaIca,TarifaRiv,TarifaRtc,F.CodTarIca
,F.IdLocal,LE.Localidad,LE.IdDep,DE.Departamento,F.IdVend,VN.RazonSocial,F.TarifaCom,F.Modalidad,F.IdConcepto,Concepto,F.nClieCon,CT.Nombre,TipPed,Pedido,IdCiaPed,TipRem,Remision,IdCiaRem,TipCom,Comprobante,IdCiaCom
,F.Anulado,'',F.NumDev,F.IdCiaCrea,F.FecDev,F.Observacion,TipoDoc
--datos de terceros
,T.TipoId,T.Dv,T.IdLocal,L.Localidad,L.IdDep,DP.Departamento,T.IdSector,SectorEco,T.IdRegimen,Regimen,T.TipEnte
,CL.IdSzona,Subzona,SZ.IdZona,Zona,CL.IdGrupo,GrupoClie,LEV.IdDep,DEV.Departamento,CL.IdLocEnv,LEV.Localidad,CL.ExcIva,CL.Autoret
,A.IdLocal,LA.Localidad,LA.IdDep,DA.Departamento
FROM Trn_Factcon AS F INNER JOIN Companias AS CN ON F.IdCia=CN.IdCia INNER JOIN Conceptos AS C ON F.IdConcepto=C.IdConcepto
INNER JOIN Terceros AS VN ON F.IdVend=VN.IdTercero INNER JOIN Sys_TiposDoc AS TD ON F.TipDoc=TD.IdDoc
INNER JOIN Localidades AS LE ON F.IdLocal=LE.IdLocal INNER JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep
LEFT JOIN Terceros AS T ON F.NitCliente=T.IdTercero LEFT JOIN TercCliente AS CL ON F.NitCliente=CL.IdClie
LEFT JOIN Agencias AS A ON F.CdAgencia=A.IdAgencia
LEFT JOIN Localidades AS L ON T.IdLocal=L.IdLocal LEFT JOIN Departamentos AS DP ON L.IdDep=DP.IdDep 
LEFT JOIN RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen LEFT JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
LEFT JOIN Subzonas AS SZ ON CL.IdSzona=SZ.IdSzona LEFT JOIN Zonas AS Z ON SZ.IdZona=Z.IdZona
LEFT JOIN GruposCli AS GC ON CL.IdGrupo=GC.IdGrupo 
LEFT JOIN Localidades AS LEV ON CL.IdLocEnv=LEV.IdLocal LEFT JOIN Departamentos AS DEV ON LEV.IdDep=DEV.IdDep
LEFT JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal LEFT JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep
LEFT JOIN ClieContado AS CT ON F.nClieCon=CT.IdCliente
WHERE F.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (F.IdCia=@pmIdCia OR @pmIdCia IS NULL)
AND (F.NitCliente=@pmIdCliente OR @pmIdCliente IS NULL)
UNION ALL
SELECT D.TipDev,D.Devolucion,D.IdCia,Compania,D.Fecha,D.NitCliente,T.RazonSocial,D.CdAgencia,A.Agencia,D.VrSubTotal,D.VrDescuento,D.VrImpuesto,D.VrIvaObsequio,D.VrRetencion,D.VrReteICA,D.VrReteCREE,D.VrReteIVA,D.VrImpCons
,0,D.VrCargos,D.VrOtrDcto,D.VrFletes,D.VrNeto,D.BaseImp,D.BaseRet,D.BaseRet,D.VrImpuesto,D.BaseIvaObsq,F.TarifaIva,F.TarifaRet,F.TarifaIca,F.TarifaRiv,D.TarifaRtc,F.CodTarIca
,D.IdLocal,LE.Localidad,LE.IdDep,DE.Departamento,D.IdVend,VN.RazonSocial,D.TarifaCom,D.Modalidad,D.IdConcepto,Concepto,D.nClieCon,CT.Nombre,'PED',D.Pedido,D.IdCiaPed,'REM',D.Remision,D.IdCiaRem,D.TipCom,D.Comprobante,D.IdCiaCom
,0,D.TipDoc,D.Factura,D.IdCiaDoc,D.FecDoc,D.Observacion,TipoDoc
--datos de terceros
,T.TipoId,T.Dv,T.IdLocal,L.Localidad,L.IdDep,DP.Departamento,T.IdSector,SectorEco,T.IdRegimen,Regimen,T.TipEnte
,CL.IdSzona,Subzona,SZ.IdZona,Zona,CL.IdGrupo,GrupoClie,LEV.IdDep,DEV.Departamento,CL.IdLocEnv,LEV.Localidad,CL.ExcIva,CL.Autoret
,A.IdLocal,LA.Localidad,LA.IdDep,DA.Departamento
FROM Trn_DevFco AS D INNER JOIN Trn_Factcon AS F ON D.TipDoc=F.TipDoc AND D.Factura=F.Factura AND D.IdCiaDoc=F.IdCia
INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia INNER JOIN Conceptos AS C ON D.IdConcepto=C.IdConcepto
INNER JOIN Terceros AS VN ON D.IdVend=VN.IdTercero INNER JOIN Sys_TiposDoc AS TD ON D.TipDev=TD.IdDoc
INNER JOIN Localidades AS LE ON D.IdLocal=LE.IdLocal INNER JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep
LEFT JOIN Terceros AS T ON D.NitCliente=T.IdTercero LEFT JOIN TercCliente AS CL ON D.NitCliente=CL.IdClie
LEFT JOIN Agencias AS A ON D.CdAgencia=A.IdAgencia
LEFT JOIN Localidades AS L ON T.IdLocal=L.IdLocal LEFT JOIN Departamentos AS DP ON L.IdDep=DP.IdDep 
LEFT JOIN RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen LEFT JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
LEFT JOIN Subzonas AS SZ ON CL.IdSzona=SZ.IdSzona LEFT JOIN Zonas AS Z ON SZ.IdZona=Z.IdZona
LEFT JOIN GruposCli AS GC ON CL.IdGrupo=GC.IdGrupo 
LEFT JOIN Localidades AS LEV ON CL.IdLocEnv=LEV.IdLocal LEFT JOIN Departamentos AS DEV ON LEV.IdDep=DEV.IdDep
LEFT JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal LEFT JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep
LEFT JOIN ClieContado AS CT ON D.nClieCon=CT.IdCliente
WHERE D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (D.IdCia=@pmIdCia OR @pmIdCia IS NULL)
AND (D.NitCliente=@pmIdCliente OR @pmIdCliente IS NULL)
UNION ALL
SELECT N.TipDoc,N.NumNota,N.IdCia,Compania,N.Fecha,N.IdCliente,T.RazonSocial,N.IdAgencia,A.Agencia,VrSubTotal,0,VrImpuesto,0,VrRetencion,VrReteICA,VrReteCREE,VrReteIVA,0
,VrOtros,VrSancion,0,0,N.VrNeto,BaseImp,BaseRet,BaseIca,BaseRiv,0,TarifaIva,TarifaRet,TarifaIca,TarifaRiv,TarifaRtc,N.CodTarIca
,N.IdLocal,LE.Localidad,LE.IdDep,DE.Departamento,N.IdVend,VN.RazonSocial,N.TarifaCom,N.Modalidad,N.IdConcepto,Concepto,N.CdConductor,N.Referencia,TipDcm,Documento,IdCiaDcm,'RMT',nRemesa,CdCiaRem,TipCom,Comprobante,IdCiaCom
,0,TipDcm,Documento,IdCiaDcm,FecDcm,N.Observacion,TipoDoc
--datos de terceros
,T.TipoId,T.Dv,T.IdLocal,L.Localidad,L.IdDep,DP.Departamento,T.IdSector,SectorEco,T.IdRegimen,Regimen,T.TipEnte
,CL.IdSzona,Subzona,SZ.IdZona,Zona,CL.IdGrupo,GrupoClie,LEV.IdDep,DEV.Departamento,CL.IdLocEnv,LEV.Localidad,CL.ExcIva,CL.Autoret
,A.IdLocal,LA.Localidad,LA.IdDep,DA.Departamento
FROM Trn_Notas AS N INNER JOIN Companias AS CN ON N.IdCia=CN.IdCia INNER JOIN Conceptos AS C ON N.IdConcepto=C.IdConcepto
INNER JOIN Terceros AS T ON N.IdCliente=T.IdTercero INNER JOIN Terceros AS VN ON N.IdVend=VN.IdTercero
INNER JOIN TercCliente AS CL ON N.IdCliente=CL.IdClie INNER JOIN Agencias AS A ON N.IdAgencia=A.IdAgencia
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 Subzonas AS SZ ON CL.IdSzona=SZ.IdSzona INNER JOIN Zonas AS Z ON SZ.IdZona=Z.IdZona
INNER JOIN GruposCli AS GC ON CL.IdGrupo=GC.IdGrupo INNER JOIN Sys_TiposDoc AS TD ON N.TipDoc=TD.IdDoc
INNER JOIN Localidades AS LEV ON CL.IdLocEnv=LEV.IdLocal INNER JOIN Departamentos AS DEV ON LEV.IdDep=DEV.IdDep
INNER JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep
INNER JOIN Localidades AS LE ON N.IdLocal=LE.IdLocal INNER JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep
WHERE N.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (N.IdCia=@pmIdCia OR @pmIdCia IS NULL)
AND (N.IdCliente=@pmIdCliente OR @pmIdCliente IS NULL)
UNION ALL
SELECT R.TipDoc,R.Recibo,R.IdCia,Compania,R.Fecha,R.IdCliente,T.RazonSocial,R.IdAgencia,A.Agencia,VrSubTotal,VrDescuento,0,0,VrRetencion,VrReteICA,VrReteCREE,VrReteIVA,0
,VrOtros,VrPagosMas,VrOtrDcto,VrAhorro,R.VrNeto,0,BaseRet,BaseIca,BaseRiv,0,0,TarifaRet,TarifaIca,TarifaRiv,TarifaRtc,R.CodTarIca
,R.IdLocal,LE.Localidad,LE.IdDep,DE.Departamento,R.IdVend,VN.RazonSocial,R.TarifaCom,R.Modalidad,R.IdConcepto,Concepto,R.CdConductor,R.Referencia,R.TipDcm,R.Documento,R.IdCiaDcm,'',0,'',R.TipCom,R.Comprobante,R.IdCiaCom
,R.Anulado,'',R.NumDev,R.IdCiaCrea,R.FecDev,R.Observacion,TipoDoc
--datos de terceros
,T.TipoId,T.Dv,T.IdLocal,L.Localidad,L.IdDep,DP.Departamento,T.IdSector,SectorEco,T.IdRegimen,Regimen,T.TipEnte
,CL.IdSzona,Subzona,SZ.IdZona,Zona,CL.IdGrupo,GrupoClie,LEV.IdDep,DEV.Departamento,CL.IdLocEnv,LEV.Localidad,CL.ExcIva,CL.Autoret
,A.IdLocal,LA.Localidad,LA.IdDep,DA.Departamento
FROM Trn_Recibos AS R INNER JOIN Companias AS CN ON R.IdCia=CN.IdCia INNER JOIN Conceptos AS C ON R.IdConcepto=C.IdConcepto
INNER JOIN Terceros AS T ON R.IdCliente=T.IdTercero INNER JOIN Terceros AS VN ON R.IdVend=VN.IdTercero
INNER JOIN TercCliente AS CL ON R.IdCliente=CL.IdClie INNER JOIN Agencias AS A ON R.IdAgencia=A.IdAgencia
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 Subzonas AS SZ ON CL.IdSzona=SZ.IdSzona INNER JOIN Zonas AS Z ON SZ.IdZona=Z.IdZona
INNER JOIN GruposCli AS GC ON CL.IdGrupo=GC.IdGrupo INNER JOIN Sys_TiposDoc AS TD ON R.TipDoc=TD.IdDoc
INNER JOIN Localidades AS LEV ON CL.IdLocEnv=LEV.IdLocal INNER JOIN Departamentos AS DEV ON LEV.IdDep=DEV.IdDep
INNER JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep
INNER JOIN Localidades AS LE ON R.IdLocal=LE.IdLocal INNER JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep
WHERE R.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (R.IdCia=@pmIdCia OR @pmIdCia IS NULL)
AND (R.IdCliente=@pmIdCliente OR @pmIdCliente IS NULL)
UNION ALL
SELECT D.TipDev,D.Devolucion,D.IdCia,Compania,D.Fecha,D.IdCliente,T.RazonSocial,D.IdAgencia,A.Agencia,D.VrSubTotal,D.VrDescuento,0,0,D.VrRetencion,D.VrReteICA,D.VrReteCREE,D.VrReteIVA,0
,D.VrOtros,D.VrPagosMas,D.VrOtrDcto,D.VrAhorro,D.VrNeto,0,D.BaseRet,D.BaseIca,D.BaseRiv,0,0,R.TarifaRet,R.TarifaIca,R.TarifaRiv,D.TarifaRtc,R.CodTarIca
,D.IdLocal,LE.Localidad,LE.IdDep,DE.Departamento,D.IdVend,VN.RazonSocial,D.TarifaCom,D.Modalidad,D.IdConcepto,Concepto,D.CdConductor,D.Referencia,D.TipDcm,D.Documento,D.IdCiaDcm,'',0,'',D.TipCom,D.Comprobante,D.IdCiaCom
,0,D.TipDoc,D.Recibo,D.IdCiaDoc,D.FecDoc,D.Observacion,TipoDoc
--datos de terceros
,T.TipoId,T.Dv,T.IdLocal,L.Localidad,L.IdDep,DP.Departamento,T.IdSector,SectorEco,T.IdRegimen,Regimen,T.TipEnte
,CL.IdSzona,Subzona,SZ.IdZona,Zona,CL.IdGrupo,GrupoClie,LEV.IdDep,DEV.Departamento,CL.IdLocEnv,LEV.Localidad,CL.ExcIva,CL.Autoret
,A.IdLocal,LA.Localidad,LA.IdDep,DA.Departamento
FROM Trn_DevRec AS D INNER JOIN Trn_Recibos AS R ON D.TipDoc=R.TipDoc AND D.Recibo=R.Recibo AND D.IdCiaDoc=R.IdCia
INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia INNER JOIN Conceptos AS C ON D.IdConcepto=C.IdConcepto
INNER JOIN Terceros AS T ON D.IdCliente=T.IdTercero INNER JOIN Terceros AS VN ON D.IdVend=VN.IdTercero
INNER JOIN TercCliente AS CL ON D.IdCliente=CL.IdClie INNER JOIN Agencias AS A ON D.IdAgencia=A.IdAgencia
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 Subzonas AS SZ ON CL.IdSzona=SZ.IdSzona INNER JOIN Zonas AS Z ON SZ.IdZona=Z.IdZona
INNER JOIN GruposCli AS GC ON CL.IdGrupo=GC.IdGrupo INNER JOIN Sys_TiposDoc AS TD ON D.TipDev=TD.IdDoc
INNER JOIN Localidades AS LEV ON CL.IdLocEnv=LEV.IdLocal INNER JOIN Departamentos AS DEV ON LEV.IdDep=DEV.IdDep
INNER JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep
INNER JOIN Localidades AS LE ON D.IdLocal=LE.IdLocal INNER JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep
WHERE D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (D.IdCia=@pmIdCia OR @pmIdCia IS NULL)
AND (D.IdCliente=@pmIdCliente OR @pmIdCliente IS NULL)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_KdexSai] @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmItem,tmIdProducto,DescripProd,tmIdBodega,Bodega,tmSalidas,tmVrUnitario,tmSalidas*tmVrUnitario AS CostoTotal
,tmRemision,tmIdCiaRem,tmTipDoc,tmDocumento,tmIdCia,tmIdUnd,Unidad,tmUnidades,tmReferencia,tmDescripcion
,tmCdCCosto,tmCdSubCos,tmIdTercero,tmIdVend,tmpVehiculo,tmVrPrecio,tmSalidas*tmVrPrecio AS VrTotal,tmTarifaIva,tmVrIva
,tmVrBruto,tmNumLote,tmFecVceLote,Tanques,tmServcios,tmEsCombo,tmCdTanque,tmVrImvCosto
 FROM  tm_Kdex AS K INNER JOIN ProdMcias AS P ON K.tmIdProducto=P.IdProducto 
 INNER JOIN Bodegas AS B ON K.tmIdBodega=B.IdBodega
 LEFT JOIN UndMed AS U ON K.tmIdUnd=U.IdUnd
 WHERE  tmNumero=@pmtmNumero AND tmEsProdBase=0
 ORDER BY tmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_KdexSal] @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmItem,tmIdProducto,DescripProd,tmIdBodega,Bodega,tmCdTanque,tmSalidas,tmVrUnitario
,tmSalidas*tmVrUnitario AS CostoTotal,tmVrPrecio,tmSalidas*tmVrPrecio AS VrTotal,tmTarifaIva,tmVrIva
,tmUnidades,tmIdUnd,Unidad,tmReferencia,tmDescripcion,tmCdCCosto,tmCdSubCos,tmIdTercero,tmpVehiculo
,tmVrBruto,tmNumLote,tmFecVceLote,Tanques,tmServcios,tmEsCombo,tmTipDoc,tmDocumento,tmIdCia,tmRemision,tmIdCiaRem
,tmVrImvCosto
 FROM  tm_Kdex AS K INNER JOIN ProdMcias AS P ON K.tmIdProducto=P.IdProducto 
 INNER JOIN Bodegas AS B ON K.tmIdBodega=B.IdBodega
 LEFT JOIN UndMed AS U ON K.tmIdUnd=U.IdUnd
 WHERE  tmNumero=@pmtmNumero AND tmEsProdBase=0
 ORDER BY tmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_KdexDvs] @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmItem,tmIdProducto,DescripProd,tmIdBodega,Bodega,tmCdTanque,tmEntradas,tmVrUnitario,tmEntradas*tmVrUnitario AS CostoTotal
,tmVrPrecio,tmEntradas*tmVrPrecio AS ValorTotal,tmUnidades,tmReferencia,tmDescripcion,tmCdCCosto,tmCdSubCos,tmpVehiculo
,tmNumLote,tmFecVceLote,tmEsCombo,tmServcios,Tanques,tmTipDoc,tmDocumento,tmIdCia,tmVrImvCosto
 FROM  tm_Kdex AS K INNER JOIN ProdMcias AS P ON K.tmIdProducto=P.IdProducto INNER JOIN Bodegas AS B ON K.tmIdBodega=B.IdBodega
 WHERE  tmNumero=@pmtmNumero  AND  tmEsProdBase=0
 ORDER BY tmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_KdexTotFac] @pmtmNumero VARCHAR(5)
 AS 
--totaliza el valor de ventas de la factura o salida, de los productos no combos
 SELECT COUNT(tmItem) AS SCANT,SUM(tmEntradas) AS SENT,SUM(tmSalidas) AS SSAL
,SUM(tmEntradas*tmVrUnitario) AS SCOSENT,SUM(tmSalidas*tmVrUnitario) AS SCOSSAL
,SUM(tmVrPrecio*tmEntradas) AS SVALENT,SUM(tmVrPrecio*tmSalidas) AS SVALSAL
,SUM(tmVrIva) AS SIVA,SUM(tmVrDcto) AS SDCT,SUM(tmVrRete) AS SRET,SUM(tmVrIca) AS SICA
,SUM(tmVrBruto*tmEntradas) AS SBRUENT,SUM(tmVrBruto*tmSalidas) AS SBRUSAL
,SUM(tmVrImpCon) AS SIMPCON,SUM(tmIvaObseq) AS SIVAOBSEQ,SUM(tmVrImvCosto) AS SIVAMVC
 FROM  tm_Kdex
 WHERE tmNumero=@pmtmNumero AND tmEsProdBase=0 AND tmEsCombo=0
GO