if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsDevFcr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsDevFcr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsFacturas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsFacturas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsKardex]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsKardex]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsOComb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsOComb]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsOpedido]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsOpedido]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsSubGrupos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsSubGrupos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Kdex]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_Kdex]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Kdex_Nts]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_Kdex_Nts]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Kdex_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_Kdex_Sel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Kdex_SelDev]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_Kdex_SelDev]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Kdex_SelDve]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_Kdex_SelDve]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryClaseTarImp]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryClaseTarImp]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDevFcr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryDevFcr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDevFcr_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryDevFcr_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDevFcrLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryDevFcrLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFacturas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryFacturas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFacturas_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryFacturas_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFacturasLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryFacturasLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryKardex]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryKardex]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryKardex_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryKardex_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryKardexSub]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryKardexSub]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryKardexSubOpe]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryKardexSubOpe]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOComb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryOComb]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOComb_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryOComb_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOCombLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryOCombLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOpedido]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryOpedido]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOpedido_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryOpedido_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOpedidoLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryOpedidoLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrySubGrupos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrySubGrupos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrySubGruposLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrySubGruposLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_Kdex]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_Kdex]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_KdexDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_KdexDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_KdexDvf]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_KdexDvf]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_KdexFcr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_KdexFcr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_KdexSub]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_KdexSub]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_KdexSubOpe]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_KdexSubOpe]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpDevFcr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpDevFcr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpFacturas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpFacturas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpOComb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpOComb]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpOpedido]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpOpedido]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpSubGrupos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpSubGrupos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_KdexOcc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_KdexOcc]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_KdexPed]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_KdexPed]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_KdexGui]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_KdexGui]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFacturasNet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryFacturasNet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFacturasNetDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryFacturasNetDet]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryFacturas] @pmTipDoc VARCHAR(3),@pmFactura INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Factura,IdCia,Fecha,IdConcepto,IdCliente,IdAgencia,FechaVence,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrFletes
,VrOtros,VrCargos,VrOtrDcto,VrCostos,VrSobretasa,VrImpGlobal,VrFaltantes,VrAnticipos,VrNeto,VrAplicado,Cantidad,CantPuntos,PuntosAcum,BaseImp,BaseRet,BaseIca,BaseRiv
,TarifaIva,TarifaRet,TarifaIca,TarifaRiv,CodTarIva,CodTarRet,CodTarIca,CodTarRiv,IdCCosto,IdSubCos,IdVend,TarifaCom,CodTarCom,DirEnvio,IdLocEnv,DiasEntraga
,NitContac,NomContac,TelContac,emlContac,CargoContac,IdForma,DetallePago,MulPlazos,IdPlazo,CdMney,NitEmpTrans,EmpTrans,pVehiculo,CdConductor,CdRuta
,TipPed,Pedido,IdCiaPed,TipRem,Remision,IdCiaRem,TipCot,Cotizacion,IdCiaCot,FecPedido,AutzaMora,AutzaCupo,Modalidad,KmtVehic,CodAutoRet,OrigenAdd,TipCom,Comprobante,IdCiaCom
,ZonaFrontera,PesoTotal,UnidTotal,VolTotal,CantFalt,VrCostoRem,VrCostoAfi,VrImpCons,VrReteCREE,VrImpCarbono,TarifaRtc,CodTarRtc,Parqueadero,FecCuotaParq,BaseIvaObsq,VrIvaObsequio
,TipoFE,FechaFE,BaseIvaIgp,VrIvaIngProd,Anulado,NumDev,FecDev,Observacion,IdEstado,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_Facturas
 WHERE TipDoc=@pmTipDoc AND Factura=@pmFactura AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryFacturasLta] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME,@pmFacturaIni INT=Null,@pmFacturaFin INT=Null
,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdConcepto VARCHAR(4)=Null,@pmIdVend VARCHAR(16)=Null,@pmModalidad VARCHAR(10)=Null
,@pmIdEstado VARCHAR(4)=Null,@pmAnulado BIT=Null
 AS 
 SELECT F.Factura,F.IdCia,FE.Prefijo,FE.NumFace,Fecha,IdCliente,T.RazonSocial AS NomCliente,IdAgencia,FechaVence,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrFletes
,VrOtros,VrCargos,VrOtrDcto,VrCostos,VrSobretasa,VrImpGlobal,VrFaltantes,VrAnticipos,VrImpCarbono,VrIvaIngProd,VrNeto,VrAplicado,Cantidad,CantPuntos,PuntosAcum,BaseImp,BaseRet,BaseIca
,BaseRiv,BaseIvaIgp,TarifaIva,TarifaRet,TarifaIca,TarifaRiv,CodTarIva,CodTarRet,CodTarIca,CodTarRiv,F.IdConcepto AS CdConcepto,Concepto,IdCCosto,IdSubCos,IdVend,V.RazonSocial AS Vendedor,TarifaCom,CodTarCom,DirEnvio
,IdLocEnv,DiasEntraga,NitContac,NomContac,TelContac,emlContac,CargoContac,IdForma,DetallePago,MulPlazos,IdPlazo,CdMney,NitEmpTrans
,EmpTrans,pVehiculo,CdConductor,KmtVehic,CdRuta,TipPed,Pedido,IdCiaPed,TipRem,Remision,IdCiaRem,TipCot,Cotizacion,IdCiaCot,FecPedido,AutzaMora,AutzaCupo,Modalidad,CodAutoRet
,OrigenAdd,TipCom,Comprobante,IdCiaCom,ZonaFrontera,PesoTotal,UnidTotal,VolTotal,CantFalt,VrCostoRem,VrCostoAfi,VrImpCons,VrReteCREE,TarifaRtc,CodTarRtc,BaseIvaObsq,VrIvaObsequio
,Parqueadero,FecCuotaParq,TipoFE,FechaFE,Anulado,NumDev,FecDev,F.Observacion AS Observ,F.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,F.IdUsuario AS IdUsuari,Usuario,F.TipDoc
 FROM Trn_Facturas F INNER JOIN Terceros AS T ON F.IdCliente=T.IdTercero
 INNER JOIN Terceros AS V ON F.IdVend=V.IdTercero
 INNER JOIN  Conceptos AS CN ON F.IdConcepto=CN.IdConcepto
 INNER JOIN EstadoDoc AS ED ON F.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON F.IdUsuario=U.IdUsuario 
 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.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND F.Factura BETWEEN ISNULL(@pmFacturaIni,0) AND ISNULL(@pmFacturaFin,2147483647)
 AND F.IdCia LIKE ISNULL(@pmIdCia,'%%') AND F.IdCliente LIKE ISNULL(@pmIdCliente,'%') AND F.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND F.IdVend LIKE ISNULL(@pmIdVend,'%') AND F.Modalidad LIKE ISNULL(@pmModalidad,'%')
 AND F.IdEstado LIKE ISNULL(@pmIdEstado,'%') 
 AND (F.Anulado=ISNULL(@pmAnulado,0) OR F.Anulado=ISNULL(@pmAnulado,1))
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsFacturas] @pmTipDoc VARCHAR(3),@pmFactura INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmFechaVence SMALLDATETIME,@pmVrSubTotal MONEY,@pmVrDescuento MONEY
,@pmVrImpuesto MONEY,@pmVrRetencion MONEY,@pmVrReteICA MONEY,@pmVrReteIVA MONEY,@pmVrFletes MONEY,@pmVrOtros MONEY,@pmVrCargos MONEY,@pmVrOtrDcto MONEY,@pmVrCostos MONEY,@pmVrSobretasa MONEY,@pmVrImpGlobal MONEY,@pmVrFaltantes MONEY,@pmVrAnticipos MONEY
,@pmVrNeto MONEY,@pmVrAplicado MONEY,@pmCantidad DECIMAL(14,4),@pmCantPuntos DECIMAL(14,4),@pmPuntosAcum DECIMAL(14,4),@pmBaseImp MONEY,@pmBaseRet MONEY,@pmBaseIca MONEY,@pmBaseRiv MONEY,@pmTarifaIva DECIMAL(14,4),@pmTarifaRet DECIMAL(14,4),@pmTarifaIca DECIMAL(14,4),@pmTarifaRiv DECIMAL(14,4)
,@pmCodTarIva VARCHAR(4),@pmCodTarRet VARCHAR(4),@pmCodTarIca VARCHAR(4),@pmCodTarRiv VARCHAR(4),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdVend VARCHAR(16),@pmTarifaCom DECIMAL(14,4),@pmCodTarCom VARCHAR(4),@pmDirEnvio VARCHAR(250),@pmIdLocEnv VARCHAR(8)
,@pmDiasEntraga INT,@pmNitContac VARCHAR(16),@pmNomContac VARCHAR(150),@pmTelContac VARCHAR(20),@pmemlContac VARCHAR(100),@pmCargoContac VARCHAR(50),@pmIdForma VARCHAR(4),@pmDetallePago VARCHAR(100),@pmMulPlazos BIT,@pmIdPlazo VARCHAR(4),@pmCdMney VARCHAR(5)
,@pmNitEmpTrans VARCHAR(16),@pmEmpTrans VARCHAR(150),@pmpVehiculo VARCHAR(10),@pmCdConductor VARCHAR(16),@pmCdRuta VARCHAR(4),@pmTipPed VARCHAR(3),@pmPedido INT,@pmIdCiaPed CHAR(2),@pmTipRem VARCHAR(3),@pmRemision INT,@pmIdCiaRem CHAR(2),@pmTipCot VARCHAR(3)
,@pmCotizacion INT,@pmIdCiaCot CHAR(2),@pmFecPedido SMALLDATETIME,@pmAutzaMora INT,@pmAutzaCupo INT,@pmModalidad VARCHAR(10),@pmKmtVehic INT,@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4)
,@pmZonaFrontera INT,@pmPesoTotal DECIMAL(14,4),@pmUnidTotal DECIMAL(14,4),@pmVolTotal DECIMAL(14,4),@pmCantFalt DECIMAL(14,4),@pmVrCostoRem MONEY,@pmVrCostoAfi MONEY,@pmCodAutoRet VARCHAR(4),@pmVrImpCons MONEY,@pmVrReteCREE MONEY,@pmTarifaRtc DECIMAL(14,4),@pmCodTarRtc VARCHAR(4),@pmParqueadero BIT,@pmFecCuotaParq SMALLDATETIME
,@pmBaseIvaObsq MONEY,@pmVrIvaObsequio MONEY,@pmVrImpCarbono MONEY,@pmTipoFE VARCHAR(3),@pmFechaFE SMALLDATETIME,@pmBaseIvaIgp MONEY,@pmVrIvaIngProd MONEY,@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_Facturas (TipDoc,Factura,IdCia,Fecha,IdConcepto,IdCliente,IdAgencia,FechaVence,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrCostos,VrSobretasa,VrImpGlobal,VrFaltantes,VrAnticipos,VrNeto,VrAplicado,Cantidad,CantPuntos,PuntosAcum,BaseImp,BaseRet,BaseIca,BaseRiv,TarifaIva,TarifaRet,TarifaIca,TarifaRiv,CodTarIva,CodTarRet,CodTarIca,CodTarRiv,IdCCosto,IdSubCos,IdVend,TarifaCom,CodTarCom,DirEnvio,IdLocEnv,DiasEntraga,NitContac,NomContac,TelContac,emlContac,CargoContac,IdForma,DetallePago,MulPlazos,IdPlazo,CdMney,NitEmpTrans,EmpTrans,pVehiculo,CdConductor,CdRuta,TipPed,Pedido,IdCiaPed,TipRem,Remision,IdCiaRem
,TipCot,Cotizacion,IdCiaCot,FecPedido,AutzaMora,AutzaCupo,Modalidad,KmtVehic,ZonaFrontera,PesoTotal,UnidTotal,VolTotal,CantFalt,VrCostoRem,VrCostoAfi,CodAutoRet,OrigenAdd,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario,VrImpCons,VrReteCREE,TarifaRtc,CodTarRtc,Parqueadero,FecCuotaParq,BaseIvaObsq,VrIvaObsequio,VrImpCarbono,TipoFE,FechaFE,BaseIvaIgp,VrIvaIngProd)
 VALUES (@pmTipDoc,@pmFactura,@pmIdCia,@pmFecha,@pmIdConcepto,@pmIdCliente,@pmIdAgencia,@pmFechaVence,@pmVrSubTotal,@pmVrDescuento,@pmVrImpuesto,@pmVrRetencion,@pmVrReteICA,@pmVrReteIVA,@pmVrFletes,@pmVrOtros,@pmVrCargos,@pmVrOtrDcto,@pmVrCostos,@pmVrSobretasa,@pmVrImpGlobal,@pmVrFaltantes,@pmVrAnticipos,@pmVrNeto,@pmVrAplicado,@pmCantidad,@pmCantPuntos,@pmPuntosAcum,@pmBaseImp,@pmBaseRet,@pmBaseIca,@pmBaseRiv,@pmTarifaIva,@pmTarifaRet,@pmTarifaIca,@pmTarifaRiv,@pmCodTarIva,@pmCodTarRet,@pmCodTarIca,@pmCodTarRiv,@pmIdCCosto,@pmIdSubCos,@pmIdVend,@pmTarifaCom,@pmCodTarCom,@pmDirEnvio
,@pmIdLocEnv,@pmDiasEntraga,@pmNitContac,@pmNomContac,@pmTelContac,@pmemlContac,@pmCargoContac,@pmIdForma,@pmDetallePago,@pmMulPlazos,@pmIdPlazo,@pmCdMney,@pmNitEmpTrans,@pmEmpTrans,@pmpVehiculo,@pmCdConductor,@pmCdRuta,@pmTipPed,@pmPedido,@pmIdCiaPed,@pmTipRem,@pmRemision,@pmIdCiaRem,@pmTipCot,@pmCotizacion,@pmIdCiaCot,@pmFecPedido,@pmAutzaMora,@pmAutzaCupo,@pmModalidad,@pmKmtVehic,@pmZonaFrontera,@pmPesoTotal,@pmUnidTotal,@pmVolTotal,@pmCantFalt,@pmVrCostoRem,@pmVrCostoAfi,@pmCodAutoRet,@pmOrigenAdd,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmAnulado,@pmNumDev,@pmFecDev,@pmObservacion,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmVrImpCons,@pmVrReteCREE,@pmTarifaRtc,@pmCodTarRtc
,@pmParqueadero,@pmFecCuotaParq,@pmBaseIvaObsq,@pmVrIvaObsequio,@pmVrImpCarbono,@pmTipoFE,@pmFechaFE,@pmBaseIvaIgp,@pmVrIvaIngProd)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpFacturas] @pmTipDoc VARCHAR(3),@pmFactura INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmFechaVence SMALLDATETIME,@pmVrSubTotal MONEY,@pmVrDescuento MONEY,@pmVrImpuesto MONEY,@pmVrRetencion MONEY,@pmVrReteICA MONEY,@pmVrReteIVA MONEY,@pmVrFletes MONEY
,@pmVrOtros MONEY,@pmVrCargos MONEY,@pmVrOtrDcto MONEY,@pmVrCostos MONEY,@pmVrSobretasa MONEY,@pmVrImpGlobal MONEY,@pmVrFaltantes MONEY,@pmVrAnticipos MONEY,@pmVrNeto MONEY,@pmVrAplicado MONEY,@pmCantidad DECIMAL(14,4),@pmCantPuntos DECIMAL(14,4),@pmPuntosAcum DECIMAL(14,4),@pmBaseImp MONEY,@pmBaseRet MONEY,@pmBaseIca MONEY,@pmBaseRiv MONEY,@pmTarifaIva DECIMAL(14,4)
,@pmTarifaRet DECIMAL(14,4),@pmTarifaIca DECIMAL(14,4),@pmTarifaRiv DECIMAL(14,4),@pmCodTarIva VARCHAR(4),@pmCodTarRet VARCHAR(4),@pmCodTarIca VARCHAR(4),@pmCodTarRiv VARCHAR(4),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdVend VARCHAR(16),@pmTarifaCom DECIMAL(14,4),@pmCodTarCom VARCHAR(4),@pmDirEnvio VARCHAR(250),@pmIdLocEnv VARCHAR(8)
,@pmDiasEntraga INT,@pmNitContac VARCHAR(16),@pmNomContac VARCHAR(150),@pmTelContac VARCHAR(20),@pmemlContac VARCHAR(100),@pmCargoContac VARCHAR(50),@pmIdForma VARCHAR(4),@pmDetallePago VARCHAR(100),@pmMulPlazos BIT,@pmIdPlazo VARCHAR(4),@pmCdMney VARCHAR(5),@pmNitEmpTrans VARCHAR(16),@pmEmpTrans VARCHAR(150),@pmpVehiculo VARCHAR(10)
,@pmCdConductor VARCHAR(16),@pmCdRuta VARCHAR(4),@pmTipPed VARCHAR(3),@pmPedido INT,@pmIdCiaPed CHAR(2),@pmTipRem VARCHAR(3),@pmRemision INT,@pmIdCiaRem CHAR(2),@pmTipCot VARCHAR(3),@pmCotizacion INT,@pmIdCiaCot CHAR(2),@pmFecPedido SMALLDATETIME,@pmAutzaMora INT,@pmAutzaCupo INT,@pmModalidad VARCHAR(10),@pmKmtVehic INT
,@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmZonaFrontera INT,@pmPesoTotal DECIMAL(14,4),@pmUnidTotal DECIMAL(14,4),@pmVolTotal DECIMAL(14,4),@pmCantFalt DECIMAL(14,4),@pmVrCostoRem MONEY,@pmVrCostoAfi MONEY,@pmCodAutoRet VARCHAR(4)
,@pmVrImpCons MONEY,@pmVrReteCREE MONEY,@pmTarifaRtc DECIMAL(14,4),@pmCodTarRtc VARCHAR(4),@pmParqueadero BIT,@pmFecCuotaParq SMALLDATETIME,@pmBaseIvaObsq MONEY,@pmVrIvaObsequio MONEY,@pmVrImpCarbono MONEY,@pmTipoFE VARCHAR(3),@pmFechaFE SMALLDATETIME,@pmBaseIvaIgp MONEY,@pmVrIvaIngProd MONEY,@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_Facturas SET Fecha=@pmFecha,IdConcepto=@pmIdConcepto,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,FechaVence=@pmFechaVence,VrSubTotal=@pmVrSubTotal,VrDescuento=@pmVrDescuento,VrImpuesto=@pmVrImpuesto,VrRetencion=@pmVrRetencion,VrReteICA=@pmVrReteICA,VrReteIVA=@pmVrReteIVA,VrFletes=@pmVrFletes,VrOtros=@pmVrOtros,VrCargos=@pmVrCargos,VrOtrDcto=@pmVrOtrDcto,VrCostos=@pmVrCostos
,VrSobretasa=@pmVrSobretasa,VrImpGlobal=@pmVrImpGlobal,VrFaltantes=@pmVrFaltantes,VrAnticipos=@pmVrAnticipos,VrNeto=@pmVrNeto,VrAplicado=@pmVrAplicado,Cantidad=@pmCantidad,CantPuntos=@pmCantPuntos,BaseImp=@pmBaseImp,BaseRet=@pmBaseRet,BaseIca=@pmBaseIca,BaseRiv=@pmBaseRiv,TarifaIva=@pmTarifaIva,TarifaRet=@pmTarifaRet,TarifaIca=@pmTarifaIca,TarifaRiv=@pmTarifaRiv,CodTarIva=@pmCodTarIva
,CodTarRet=@pmCodTarRet,CodTarIca=@pmCodTarIca,CodTarRiv=@pmCodTarRiv,IdCCosto=@pmIdCCosto,IdSubCos=@pmIdSubCos,IdVend=@pmIdVend,TarifaCom=@pmTarifaCom,CodTarCom=@pmCodTarCom,DirEnvio=@pmDirEnvio,IdLocEnv=@pmIdLocEnv,DiasEntraga=@pmDiasEntraga,NitContac=@pmNitContac,NomContac=@pmNomContac,TelContac=@pmTelContac,emlContac=@pmemlContac,CargoContac=@pmCargoContac
,IdForma=@pmIdForma,DetallePago=@pmDetallePago,MulPlazos=@pmMulPlazos,IdPlazo=@pmIdPlazo,CdMney=@pmCdMney,NitEmpTrans=@pmNitEmpTrans,EmpTrans=@pmEmpTrans,pVehiculo=@pmpVehiculo,CdConductor=@pmCdConductor,CdRuta=@pmCdRuta,TipPed=@pmTipPed,Pedido=@pmPedido,IdCiaPed=@pmIdCiaPed,TipRem=@pmTipRem,Remision=@pmRemision,IdCiaRem=@pmIdCiaRem,TipCot=@pmTipCot,Cotizacion=@pmCotizacion,IdCiaCot=@pmIdCiaCot
,FecPedido=@pmFecPedido,AutzaMora=@pmAutzaMora,AutzaCupo=@pmAutzaCupo,Modalidad=@pmModalidad,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,Anulado=@pmAnulado,NumDev=@pmNumDev,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado,PuntosAcum=@pmPuntosAcum,KmtVehic=@pmKmtVehic,ZonaFrontera=@pmZonaFrontera,Parqueadero=@pmParqueadero,FecCuotaParq=@pmFecCuotaParq
,PesoTotal=@pmPesoTotal,UnidTotal=@pmUnidTotal,VolTotal=@pmVolTotal,CantFalt=@pmCantFalt,VrCostoRem=@pmVrCostoRem,VrCostoAfi=@pmVrCostoAfi,CodAutoRet=@pmCodAutoRet,VrImpCons=@pmVrImpCons,VrReteCREE=@pmVrReteCREE,TarifaRtc=@pmTarifaRtc,CodTarRtc=@pmCodTarRtc,BaseIvaObsq=@pmBaseIvaObsq,VrIvaObsequio=@pmVrIvaObsequio,VrImpCarbono=@pmVrImpCarbono,TipoFE=@pmTipoFE,FechaFE=@pmFechaFE,BaseIvaIgp=@pmBaseIvaIgp,VrIvaIngProd=@pmVrIvaIngProd,FecUpdate=@pmFecUpdate
 WHERE TipDoc=@pmTipDoc AND Factura=@pmFactura AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryFacturas_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 FechaDoc,F.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente
,F.IdAgencia AS IdAgenc,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia,FechaVence,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrFletes
,VrOtros,VrCargos,VrOtrDcto,VrCostos,VrSobretasa,VrImpGlobal,VrFaltantes,VrAnticipos,VrImpCarbono,F.VrIvaIngProd,VrNeto,VrAplicado,Cantidad,CantPuntos,PuntosAcum,BaseImp,BaseRet,BaseIca,BaseRiv,BaseIvaIgp
,F.TarifaIva AS TarifIva,F.TarifaRet AS TarifRet,F.TarifaIca AS TarifIca,TarifaRiv,F.IdCCosto AS IdCenCost,CC.CCosto AS CentCosto,F.IdSubCos AS IdSubCent,SC.SubCosto AS SubcCosto,F.IdVend AS NitVend,VN.RazonSocial AS Vendedor,TarifaCom
,DirEnvio,F.IdLocEnv AS IdCiuEnvio,LE.Localidad AS CiudadEnvio,LE.IdDep AS CdDepEnvio,DE.Departamento AS DptoEnvio,DiasEntraga
,F.NitContac AS FacNitContac,F.NomContac AS FacNomContac,F.TelContac AS FacTelContac,F.emlContac AS FacEmailContac,CargoContac,F.IdForma AS CdForma,FormaPago,DetallePago,MulPlazos,F.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago,NitEmpTrans,EmpTrans
,F.pVehiculo AS PlacaVeh,CdConductor,TC.RazonSocial AS Conductor,F.CdRuta,Ruta,TipPed,Pedido,IdCiaPed,TipRem,F.Remision AS NumRemision,F.IdCiaRem AS CdCiaRem,TipCot,F.Cotizacion AS NumCotizacion,F.IdCiaCot AS CiaCotiza,FecPedido
,AutzaMora,AutzaCupo,Modalidad,KmtVehic,PesoTotal,UnidTotal,VolTotal,CantFalt,VrCostoRem,VrCostoAfi,VrImpCons,BaseIvaObsq,VrIvaObsequio,OrigenAdd,TipCom,TipoCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev
,F.Observacion AS Observ,ZonaFrontera,VrReteCREE,TarifaRtc,CodTarRtc,Parqueadero,FecCuotaParq,TipoFE,FechaFE,F.IdEstado AS CdEstado,Estado,F.TimeSys AS Fec_Add,F.FecUpdate AS Fec_Update,IdCiaCrea,F.IdUsuario AS CdUsuario,Usuario,Leyenda
--Información 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 NomCiudad,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
--información del cliente
,NitRepLeg,NomRepLeg,CLI.NitContac AS ClieNitContac,CLI.NomContac AS ClieNomContacto,CLI.TelContac AS ClieTelContac,CLI.emlContac AS ClieEmailContac,CargContac,DiasEntga,CLI.IdSzona AS CdSubzona,Subzona,Zona
,CLI.IdGrupo AS CdGrupoCli,GrupoClie,CLI.IdClase AS CdClaseCta,NumCuenta,CLI.IdBanco AS CdBanco,Banco,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
--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,CdAgencia,KA.Agencia AS KarAgencia,KA.CodAgencia AS KarCodAgencia,KA.NContrato AS KarAgeContrato,KA.Referencia AS KarAgeRefencia,CdLocal,KL.Localidad AS KarCiudad
,CdCCosto,KC.CCosto AS Kar_Ccosto,K.CdSubCos AS CodSubCos,KS.SubCosto AS Kar_Subcentro,K.pVehiculo AS KarPlacaVeh,K.Referencia AS KarReferencia,Descripcion,Comptmntos,CdProdEquiv,TipOrd,NumOrden,IdCiaOrd,K.Cotizacion AS KarNumCotiza,K.IdCiaCot AS KarCiaCotiza,K.Remision AS KarRemision,K.IdCiaRem AS KarCiaRem
,K.Factura AS KarFactura,TipDocDev,NumDocDev,K.IdVend AS KarNitVend,KV.RazonSocial AS KarVendedor,K.Comision AS KarTarifaCom,CdOperario,KO.RazonSocial AS NomOperario,ComisnOper,Sobretasa,TasaNac,TasaDep,TasaMun,Soldicom,ImpGlobal,OtroImpto,Unidades,ItemCombo,Servcios,NoVentas,EsCombo,EsProdBase
,ListaPrec,VrBase,BaseIvp,TarifaIvp,IvaIngProd,CdMoneda,VrTasaCamb,VrDivisa1,VrDivisa2,VrDivisa3,Referencia2,FecOrden
,CdMngra,NumInicial,NumFinal,K.TarifaIco,K.VrImpCon AS DetVrImpCon
--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
--campos de memo
,DM.Comentarios AS Comentario,Nota1,Nota2,Nota3,FE.EstadoFE,FE.Prefijo,FE.NumFace,FE.CUFE,FE.Resolucion,FE.RangoNum,FE.FecVigencia, FE.CUFE_QR AS QR
 FROM Trn_Facturas 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 Terceros AS T ON F.IdCliente=T.IdTercero 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 Plazos AS PZ ON F.IdPlazo=PZ.IdPlazo INNER JOIN  Terceros AS VN ON F.IdVend=VN.IdTercero
 INNER JOIN Agencias  AS A ON F.IdAgencia=A.IdAgencia  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 Bancos AS BC ON CLI.IdBanco=BC.IdBanco
 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 INNER JOIN Marcas AS M ON PM.IdMarca=M.IdMarca
 INNER JOIN Sys_Um AS SU ON PM.UndMed=SU.UndMed 
 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 Rutas AS R ON F.CdRuta=R.IdRuta
 LEFT JOIN CentroCosto AS KC ON K.CdCCosto=KC.IdCCosto LEFT JOIN SubCentros AS KS ON K.CdSubCos=KS.IdSubCos
 LEFT JOIN SubCentros AS SC ON F.IdSubCos=SC.IdSubCos 
 LEFT JOIN Agencias  AS KA ON K.CdAgencia=KA.IdAgencia LEFT JOIN Localidades AS KL ON K.CdLocal=KL.IdLocal
 LEFT JOIN Terceros AS KV ON K.IdVend=KV.IdTercero  LEFT JOIN Terceros AS KO ON K.CdOperario=KO.IdTercero
 LEFT JOIN TiposCom AS TCM ON F.TipCom=TCM.IdCom
 LEFT JOIN Trn_DocMemo AS DM ON F.TipDoc=DM.TipDoc AND F.Factura=DM.Documento AND F.IdCia=DM.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].[paQryKardex_Cr] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmTipDoc VARCHAR(3)=Null
,@pmIdCia CHAR(2)=Null,@pmIdProducto VARCHAR(16)=Null,@pmIdBodega VARCHAR(4)=Null,@pmIdTercero VARCHAR(16)=Null
,@pmTipoRef VARCHAR(10)=Null,@pmIdLinea VARCHAR(8)=Null,@pmIdGrupo VARCHAR(8)=Null,@pmIdSubgrupo VARCHAR(8)=Null
,@pmnAnnoAnt INT=Null,@pmnMesAnt INT=Null
 AS 
 SELECT  K.IdProducto AS CdProducto,DescripProd,TipDoc,Documento,K.IdCia AS CdCia,Compania,Fecha,Item,K.IdBodega AS CdBodega,Bodega
,CdTanque,Entradas,Salidas,K.IdUnd AS CdUnid,UM.Unidad AS UnidMed,VrUnitario,VrCostoEnt,VrCostoSal,VrCostProm,VrPrecio,VrPrecio*(Entradas+Salidas) AS VrTotal,TarifaDct,VrDctoEnt,VrDctoSal
,TarifaIva,VrIvaEnt,VrIvaSal,TarifaRet,VrReteEnt,VrReteSal,TarifaIca,VrIcaEnt,VrIcaSal,Unidades,K.Descripcion AS KarDescripcion,K.Referencia AS Referncia,Referencia2,FecOrden
,K.IdConcepto AS CdConcepto,Concepto,K.IdTercero AS NitTercero,T.RazonSocial AS NomTercero,CdAgencia,Agencia,CodAgencia,K.IdVend AS NitVend,VN.RazonSocial AS Vendedor,Comision
,CdOperario,OP.RazonSocial AS NomOperario,ComisnOper,pVehiculo,CdLocal,LK.Localidad AS KarCiudad,CdCCosto,CCosto,CdSubCos,SubCosto,TipOrd,NumOrden,IdCiaOrd,Factura,Remision,IdCiaRem,Cotizacion,IdCiaCot
,TipDocDev,NumDocDev,CdUbic,NumLote,FechLote,Comptmntos,CdMngra,NumInicial,NumFinal,Sobretasa,TasaNac,TasaDep,TasaMun
,Soldicom,ImpGlobal,OtroImpto,Rec_Costo,MgenCont,BaseIvaCom,ImpCarbono,BaseIvp,TarifaIvp,IvaIngProd,ListaPrec,VrBruto,VrBase,Servcios,NoVentas,EsCombo,EsProdBase,ItemCombo
,CdMoneda,VrTasaCamb,VrDivisa1,VrDivisa2,VrDivisa3,VrImvCosto,TarifaIco,VrImpCon,CodTarIco,TarifaStc,SobtasaCons,CantObseq,VrIvaObseq,IngBaseCom,TimeSys,K.IdUsuario AS IdUsuari,Usuario,TipoDoc
--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 NomCiudad,L.IdDep AS CdDep,D.Departamento AS TercDpto,T.Telefono AS TercTelefono
--datos del producto
,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,Color,PM.UndMed AS CdUndMed,SU.Unidad AS ProdUnidad,VrCostAnt,VrCosto,VrCostPmd,FecUltcom,FecUltVta,IvaInc,IdTarIva,Tarifa,Simbolo
,Seriales,Lotes,Combo,Tanques,DescripLong,DescripAbrv,Precio1,Precio2,Precio3,Precio4,Precio5,IdProv,PV.RazonSocial AS NomProveedor
,ExtciaMin,ExtciaMax,ExtciaAct,Factor1,Factor2,Factor3,Factor4,Factor5,PM.Inactivo AS ProdInactivo
--Información del saldo anterior
,SaldoAntProd,CostoAntProd,SaldoAntBod,CostoAntBod
 FROM Trn_Kardex AS K INNER JOIN Companias AS CN ON K.IdCia=CN.IdCia 
 INNER JOIN Sys_TiposDoc AS TD ON K.TipDoc=TD.IdDoc
 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   INNER JOIN Terceros AS PV ON PM.IdProv=PV.IdTercero
 INNER JOIN  Conceptos AS C ON K.IdConcepto=C.IdConcepto 
 INNER JOIN Terceros AS T ON K.IdTercero=T.IdTercero
 INNER JOIN adm_Usuarios AS U ON K.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 Terceros AS VN ON K.IdVend=VN.IdTercero
 LEFT JOIN Agencias  AS A ON K.CdAgencia=A.IdAgencia
 LEFT JOIN CentroCosto AS CC ON K.CdCCosto=CC.IdCCosto LEFT JOIN SubCentros AS SC ON K.CdSubCos=SC.IdSubCos
 LEFT JOIN  Localidades AS LK ON K.CdLocal=LK.IdLocal 
 LEFT JOIN Terceros AS OP ON K.CdOperario=OP.IdTercero
 LEFT JOIN Tablapor AS TI ON PM.IdTarIva=TI.IdTarifa
--Saldos anteriores
 LEFT JOIN (SELECT IdProducto,SUM(SaldoAnt+Entradas-Salidas) AS SaldoAntProd,SUM(CostoAnt+CostoEnt-CostoSal) AS CostoAntProd 
 FROM  AcuInventario
 WHERE  nAnno=@pmnAnnoAnt AND nMes=@pmnMesAnt
 AND  IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdProducto LIKE ISNULL(@pmIdProducto,'%')
 AND IdBodega LIKE ISNULL(@pmIdBodega,'%')
 GROUP BY IdProducto) AS SP ON K.IdProducto=SP.IdProducto
--Saldos por bodega
 LEFT JOIN (SELECT IdProducto,IdBodega,SUM(SaldoAnt+Entradas-Salidas) AS SaldoAntBod,SUM(CostoAnt+CostoEnt-CostoSal) AS CostoAntBod
 FROM  AcuInventario
 WHERE  nAnno=@pmnAnnoAnt AND nMes=@pmnMesAnt
 AND  IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdProducto LIKE ISNULL(@pmIdProducto,'%')
 AND IdBodega LIKE ISNULL(@pmIdBodega,'%')
 GROUP BY IdProducto,IdBodega) AS SB ON K.IdProducto=SB.IdProducto AND K.IdBodega=SB.IdBodega
 WHERE  Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND K.IdProducto LIKE ISNULL(@pmIdProducto,'%')
 AND K.IdCia LIKE ISNULL(@pmIdCia,'%%') AND K.TipDoc LIKE ISNULL(@pmTipDoc,'%') 
 AND K.IdBodega LIKE ISNULL(@pmIdBodega,'%') AND K.IdTercero LIKE ISNULL(@pmIdTercero,'%')
 AND TipoRef LIKE ISNULL(@pmTipoRef,'%') AND G.IdLinea LIKE ISNULL(@pmIdLinea,'%')
 AND S.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND PM.IdSubgrupo LIKE ISNULL(@pmIdSubgrupo ,'%')
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryDevFcr_Cr] @pmTipDev VARCHAR(3),@pmDevolucionIni INT,@pmDevolucionFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDev,TipoDoc,Devolucion,D.IdCia AS CdCia,Compania,D.Fecha AS FechaDev,D.IdConcepto AS CdConcepto,Concepto,D.TipDoc AS TipFact,D.Factura AS NumFactura
,IdCiaDoc,FecDoc,IdCliente,T.RazonSocial AS NomCliente,D.IdAgencia AS IdAgncia,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion
,VrReteICA,VrReteIVA,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrCostos,VrSobretasa,VrImpGlobal,VrFaltantes,VrAnticipos,D.VrImpCarbono,D.VrIvaIngProd,VrNeto,Cantidad,CantPuntos,BaseImp,BaseRet,D.BaseIvaIgp
,VrReteCREE,TarifaRtc,CodTarRtc,D.IdCCosto AS IdCenCost,CC.CCosto AS CentCosto,D.IdSubCos AS IdSubCent,SC.SubCosto AS SubcCosto,D.IdVend AS NitVend,VN.RazonSocial AS Vendedor
,TarifaCom,D.IdLocEnv AS CodCiuEnvio,LE.Localidad AS CiudadEnvio,LE.IdDep AS CdDepEnvio,DE.Departamento AS DptoEnvio,Pedido,IdCiaPed,D.Remision AS NumRemision,D.IdCiaRem AS CdCiaRem
,D.Cotizacion AS NumCotizacion,D.IdCiaCot AS CdCiaCot,FecPedido,Modalidad,ModdDev,PlacaVehic,KmtVehic,ZonaFrontera,VrImpCons,BaseIvaObsq,VrIvaObsequio,DevMasivo,NumRecibo,CdCiaRec,CdMotDev,MotivoDev
,OrigenAdd,TipCom,TipoCom,Comprobante,IdCiaCom,D.Observacion AS Observ,D.IdEstado AS CdEstado,Estado,D.TimeSys AS Fec_Add,IdCiaCrea,D.IdUsuario AS IdUsuari,Usuario,Leyenda
--Información 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 NomCiudad,L.IdDep AS CdDep,DP.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
--información del cliente
,NitRepLeg,NomRepLeg,CLI.NitContac AS ClieNitContac,CLI.NomContac AS ClieNomContacto,CLI.TelContac AS ClieTelContac,CLI.emlContac AS ClieEmailContac,CargContac,DiasEntga,CLI.IdSzona AS CdSubzona,Subzona,Zona
,CLI.IdGrupo AS CdGrupoCli,GrupoClie,CLI.IdClase AS CdClaseCta,NumCuenta,CLI.IdBanco AS CdBanco,Banco,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
--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,CdAgencia,KA.Agencia AS KarAgencia,KA.CodAgencia AS KarCodAgencia,KA.NContrato AS KarAgeContrato,KA.Referencia AS KarAgeRefencia,CdLocal,KL.Localidad AS KarCiudad
,CdCCosto,KC.CCosto AS Kar_Ccosto,K.CdSubCos AS CodSubCos,KS.SubCosto AS Kar_Subcentro,K.pVehiculo AS KarPlacaVeh,K.Referencia AS KarReferencia,Descripcion,Comptmntos,CdProdEquiv,TipOrd,NumOrden,IdCiaOrd,K.Cotizacion AS KarNumCotiza,K.IdCiaCot AS KarCiaCotiza,K.Remision AS KarRemision,K.IdCiaRem AS KarCiaRem
,K.Factura AS KarFactura,TipDocDev,NumDocDev,K.IdVend AS KarNitVend,KV.RazonSocial AS KarVendedor,K.Comision AS KarTarifaCom,CdOperario,KO.RazonSocial AS NomOperario,ComisnOper,Sobretasa,TasaNac,TasaDep,TasaMun,Soldicom,ImpGlobal,OtroImpto,Unidades,ItemCombo,Servcios,NoVentas,EsCombo,EsProdBase
,ListaPrec,VrBase,BaseIvp,TarifaIvp,IvaIngProd,CdMoneda,VrTasaCamb,VrDivisa1,VrDivisa2,VrDivisa3,Referencia2,FecOrden
,CdMngra,NumInicial,NumFinal
--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
 FROM Trn_DevFcr AS D  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 Sys_TiposDoc AS TD ON D.TipDev=TD.IdDoc
 INNER JOIN Terceros AS T ON D.IdCliente=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  Terceros AS VN ON D.IdVend=VN.IdTercero INNER JOIN Agencias  AS A ON D.IdAgencia=A.IdAgencia 
 INNER JOIN CentroCosto AS CC ON D.IdCCosto=CC.IdCCosto 
 INNER JOIN TercCliente AS CLI ON D.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 Bancos AS BC ON CLI.IdBanco=BC.IdBanco
 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 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
 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  Localidades AS LE ON D.IdLocEnv=LE.IdLocal  LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep 
 LEFT JOIN CentroCosto AS KC ON K.CdCCosto=KC.IdCCosto LEFT JOIN SubCentros AS KS ON K.CdSubCos=KS.IdSubCos
 LEFT JOIN SubCentros AS SC ON D.IdSubCos=SC.IdSubCos 
 LEFT JOIN Agencias  AS KA ON K.CdAgencia=KA.IdAgencia LEFT JOIN Localidades AS KL ON K.CdLocal=KL.IdLocal
 LEFT JOIN Terceros AS KV ON K.IdVend=KV.IdTercero  LEFT JOIN Terceros AS KO ON K.CdOperario=KO.IdTercero
 LEFT JOIN TiposCom AS TCM ON D.TipCom=TCM.IdCom LEFT JOIN TiposMotdev AS MD ON D.CdMotDev=MD.IdMotDev
 WHERE TipDev=@pmTipDev  AND Devolucion BETWEEN @pmDevolucionIni AND @pmDevolucionFin AND D.IdCia=@pmIdCia
 AND EsProdBase=0
 ORDER BY Devolucion,Item
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryOComb_Cr] @pmTipDoc VARCHAR(3),@pmOCargueIni INT,@pmOCargueFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT O.TipDoc AS Tip_Doc,TipoDoc,OCargue,O.IdCia AS CdCia,Compania,O.Fecha AS FechaDoc,FechaDesp,FechaVence
,O.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS IdAgenc,Agencia,CodAgencia
,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrSobretasa,VrImpGlobal,VrImpCarbono,O.VrIvaIngProd,VrNeto
,Cantidad,O.IdVend AS NitVendedor,VN.RazonSocial AS Vendedor,TarifaCom,DirEnvio,O.IdLocal AS CdCiudad,LE.Localidad AS CiudadEnvio,LE.IdDep AS CdDepEnvio,DE.Departamento AS DptoEnvio
,LugarEnv,DiasEntraga,O.IdVehiculo AS PlacaVeh,O.IdConductor AS CedConductor,TC.RazonSocial AS Conductor,LibMilitar,ClaseLib,FechaLib,CertJudicial,FecJudicial,VigJudicial,Licencia,CatLicencia,VigLicencia,NumTarjeta,VigTarjeta
,O.NitEmpTrans,EmpTrans,CdRuta,Ruta,Referncia,O.IdForma AS CdForma,FormaPago,DetallePago,MulPlazos
,O.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago,TipPed,Pedido,IdCiaPed,TipGui,Guia,IdCiaGui,TipFac,O.Factura AS NumFactura,IdCiaFac,FechaFact,AutzaMora,AutzaCupo
,Aprobacion,FechaAprob,DetalleAprob,CdUsuAprob,UA.Usuario AS UsuarioAprob,OrigenAdd,IdTerminal,ZonaFrontera,TipoOrden,Remolque,Anulado,FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado,O.IdUsuario AS IdUsuari,U.Usuario AS NomUsuario,Leyenda
,O.TimeSys AS Fec_Add,O.FecUpdate AS Fech_Update,IdCiaCrea
--Información 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 NomCiudad,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
--información del cliente
,NitRepLeg,NomRepLeg,CLI.NitContac AS ClieNitContac,CLI.NomContac AS ClieNomContacto,CLI.TelContac AS ClieTelContac,CLI.emlContac AS ClieEmailContac,CargContac,DiasEntga,CLI.IdSzona AS CdSubzona,Subzona,Zona
,CLI.IdGrupo AS CdGrupoCli,GrupoClie,CLI.IdClase AS CdClaseCta,CLI.NumCuenta AS NumeroCuenta,CLI.IdBanco AS CdBanco,Banco,ExcIva,LiqFletes,CLI.FactSold AS FactSoldicom,Autoret,VrCupo,VrSaldo,Contrato,CLI.NContrato AS CliNumContrato
,DirAgncia,TelAgncia,FaxAgncia,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia
--vehiculo
,NumVeh,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.IdMarca AS CdMarcaVeh,MV.Marca AS MarcaVeh,Modelo,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,CarrCapac,V.UndCapc AS UnidCapacidad,Comptmtos,CapComp
--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,K.IdTercero AS KarNitTercero,CdAgencia,K.CdCCosto AS KarCodCenCosto,KC.CCosto AS Kar_Ccosto
,K.CdSubCos AS KarCodSubcosto,KS.SubCosto AS Kar_Subcentro,K.pVehiculo AS KarPlacaVeh,K.Referencia AS KarReferencia,K.Descripcion AS KarDescripcion,Comptmntos,CdProdEquiv,TipOrd,NumOrden,IdCiaOrd,Cotizacion,IdCiaCot,K.Remision AS KarRemision,K.IdCiaRem AS CdCiaRem,K.Factura AS KarFactura,TipDocDev,NumDocDev
,Sobretasa,TasaNac,TasaDep,TasaMun,Soldicom,ImpGlobal,OtroImpto,Unidades,ItemCombo,Servcios,NoVentas,EsCombo,EsProdBase,K.ListaPrec AS KarLtaPrec,VrBase,BaseIvp,TarifaIvp,IvaIngProd,CdMoneda,VrTasaCamb,VrDivisa1,VrDivisa2,VrDivisa3,Referencia2,FecOrden
--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,M.Marca AS MarcaProd,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
,A.codsicom,DM.Comentarios
 FROM  Trn_OComb AS O INNER JOIN Trn_Kardex AS K ON O.TipDoc=K.TipDoc AND O.OCargue=K.Documento AND O.IdCia=K.IdCia
 INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia  INNER JOIN Sys_TiposDoc AS TD ON O.TipDoc=TD.IdDoc
 INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN  Conceptos AS C ON O.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado   INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario
 INNER JOIN  Plazos AS PZ ON O.IdPlazo=PZ.IdPlazo INNER JOIN  Terceros AS VN ON O.IdVend=VN.IdTercero
 INNER JOIN Agencias  AS A ON O.IdAgencia=A.IdAgencia  INNER JOIN Formaspago AS FP ON O.IdForma=FP.IdForma
 INNER JOIN Terceros AS TC ON O.IdConductor=TC.IdTercero
 INNER JOIN TercCliente AS CLI ON O.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 Bancos AS BC ON CLI.IdBanco=BC.IdBanco
 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 INNER JOIN Marcas AS M ON PM.IdMarca=M.IdMarca
 INNER JOIN Sys_Um AS SU ON PM.UndMed=SU.UndMed 
 LEFT JOIN  Localidades AS LE ON O.IdLocal=LE.IdLocal  LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep 
 LEFT JOIN Rutas AS R ON O.CdRuta=R.IdRuta
 LEFT JOIN CentroCosto AS KC ON K.CdCCosto=KC.IdCCosto LEFT JOIN SubCentros AS KS ON K.CdSubCos=KS.IdSubCos
 LEFT JOIN adm_Usuarios AS UA ON O.CdUsuAprob=UA.IdUsuario
 LEFT JOIN TercCndtores AS TCD ON O.IdConductor=TCD.IdConductor
 LEFT JOIN Vehiculos AS V ON O.IdVehiculo=V.IdVehiculo
 LEFT JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh
 LEFT JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca
 LEFT JOIN Trn_DocMemo DM On O.TipDoc=DM.TipDoc AND O.Ocargue=DM.Documento AND O.idcia=DM.Idcia
  WHERE O.TipDoc=@pmTipDoc AND OCargue BETWEEN @pmOCargueIni AND @pmOCargueFin AND O.IdCia=@pmIdCia
 AND EsProdBase=0
 ORDER BY OCargue,Item
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryOpedido_Cr] @pmTipDoc VARCHAR(3),@pmPedidoIni INT,@pmPedidoFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT O.TipDoc AS Tip_Doc,TipoDoc,Pedido,O.IdCia AS CdCia,Compania,O.Fecha AS FechaDoc,FechaVence,O.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente
,O.IdAgencia AS IdAgenc,Agencia,CodAgencia,IdClieFact,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrSobretasa,VrImpGlobal,VrImpCarbono,O.VrIvaIngProd,VrNeto,Cantidad
,O.BaseIvaIgp,O.IdVend AS NitVendedor,VN.RazonSocial AS Vendedor,TarifaCom,DirEnvio,O.IdLocEnv AS IdCiuEnvio,LE.Localidad AS CiudadEnvio,LE.IdDep AS CdDepEnvio,DE.Departamento AS DptoEnvio,DiasEntraga,LugarEnvio
,O.NitContac AS NitContacto,O.NomContac AS NomContacto,O.TelContac AS TelContacto,O.emlContac AS EmailContacto,CargoContac,O.IdForma AS CdForma,FormaPago,DetallePago,MulPlazos,O.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago
,NitEmpTrans,EmpTrans,O.pVehiculo AS PlacaVeh,AsignarVeh,CdConductor,TC.RazonSocial AS Conductor,CdRuta,Ruta,RefPedido,TipFac,O.Factura AS NumFactura,IdCiaFac,FechaFact
,TipRem,O.Remision AS NumRemision,O.IdCiaRem AS CdCiaRem,FechaRem,NumCotizac,CdCiaCotizac,NumAutoriza,NumAutCupo,NumAutCheq,Modalidad,Vigencia,NumAprob,IdCiaApr,FecAprob,DetalleAprob,CdUsuAprob,OrigenAdd,ZonaFrontera
,TipoTrans,TipoOrden,TipoModifica,NumAutSicom,Anulado,FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado,O.TimeSys AS Fech_Add,O.FecUpdate AS Fech_Update,IdCiaCrea,O.IdUsuario AS IdUsuari,Usuario,Leyenda
--Información 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 NomCiudad,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
--información del cliente
,NitRepLeg,NomRepLeg,CLI.NitContac AS ClieNitContac,CLI.NomContac AS ClieNomContacto,CLI.TelContac AS ClieTelContac,CLI.emlContac AS ClieEmailContac,CargContac,DiasEntga,CLI.IdSzona AS CdSubzona,Subzona,Zona
,CLI.IdGrupo AS CdGrupoCli,GrupoClie,CLI.IdClase AS CdClaseCta,NumCuenta,CLI.IdBanco AS CdBanco,Banco,ExcIva,LiqFletes,CLI.FactSold AS FactSoldicom,Autoret,VrCupo,VrSaldo,Contrato,CLI.NContrato AS CliNumContrato
,DirAgncia,TelAgncia,FaxAgncia,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia
--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,K.IdTercero AS KarNitTercero,CdAgencia,K.CdCCosto AS KarCodCenCosto,KC.CCosto AS Kar_Ccosto
,K.CdSubCos AS KarCodSubcosto,KS.SubCosto AS Kar_Subcentro,K.pVehiculo AS KarPlacaVeh,K.Referencia AS KarReferencia,Descripcion,Comptmntos,CdProdEquiv,TipOrd,NumOrden,IdCiaOrd,Cotizacion,IdCiaCot,K.Remision AS KarRemision,K.IdCiaRem AS CdCiaRem,K.Factura AS KarFactura,TipDocDev,NumDocDev
,Sobretasa,TasaNac,TasaDep,TasaMun,Soldicom,ImpGlobal,OtroImpto,Unidades,ItemCombo,Servcios,NoVentas,EsCombo,EsProdBase,K.ListaPrec AS KarLtaPrec,VrBase,BaseIvp,TarifaIvp,IvaIngProd,CdMoneda,VrTasaCamb,VrDivisa1,VrDivisa2,VrDivisa3,Referencia2,FecOrden
--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
 FROM Trn_Opedido AS O INNER JOIN Trn_Kardex AS K ON O.TipDoc=K.TipDoc AND O.Pedido=K.Documento AND O.IdCia=K.IdCia
 INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia  INNER JOIN Sys_TiposDoc AS TD ON O.TipDoc=TD.IdDoc
 INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN  Conceptos AS C ON O.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado   INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario
 INNER JOIN  Plazos AS PZ ON O.IdPlazo=PZ.IdPlazo INNER JOIN  Terceros AS VN ON O.IdVend=VN.IdTercero
 INNER JOIN Agencias  AS A ON O.IdAgencia=A.IdAgencia  INNER JOIN Formaspago AS FP ON O.IdForma=FP.IdForma
 INNER JOIN TercCliente AS CLI ON O.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 Bancos AS BC ON CLI.IdBanco=BC.IdBanco
 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 INNER JOIN Marcas AS M ON PM.IdMarca=M.IdMarca
 INNER JOIN Sys_Um AS SU ON PM.UndMed=SU.UndMed 
 LEFT JOIN  Localidades AS LE ON O.IdLocEnv=LE.IdLocal  LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep 
 LEFT JOIN Terceros AS TC ON O.CdConductor=TC.IdTercero LEFT JOIN Rutas AS R ON O.CdRuta=R.IdRuta
 LEFT JOIN CentroCosto AS KC ON K.CdCCosto=KC.IdCCosto LEFT JOIN SubCentros AS KS ON K.CdSubCos=KS.IdSubCos
 WHERE O.TipDoc=@pmTipDoc AND Pedido BETWEEN @pmPedidoIni AND @pmPedidoFin AND O.IdCia=@pmIdCia
 AND EsProdBase=0
 ORDER BY Pedido,Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_KdexFcr] @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmItem,tmIdProducto,DescripProd,tmIdBodega,Bodega,tmCdTanque,tmSalidas,tmVrPrecio,tmTarifaDct,tmVrDcto
,(tmVrPrecio*tmSalidas)-tmVrDcto AS SubTotal,tmTarifaIva,tmVrIva,tmTarifaIco,tmVrImpCon
,((tmVrPrecio*tmSalidas)-tmVrDcto)+tmVrIva AS ValorTotal,((tmVrPrecio*tmSalidas)-tmVrDcto)+tmVrIva+tmVrImpCon AS ValorNeto
,tmSobretasa,tmTasaNac,tmTasaDep,tmTasaMun,tmSoldicom,tmImpGlobal,tmTarifaRet,tmVrRete,tmTarifaIca,tmVrIca
,tmVrUnitario,tmVrUnitario*tmSalidas AS CostoTotal,tmVrBruto,tmUnidades,tmIdUnd,Unidad,tmReferencia,tmDescripcion
,tmIdVend,tmComision,tmCdOperario,tmComisnOper,tmNumLote,tmFechLote,tmFecVceLote,tmCdCCosto,tmCdSubCos,tmComptmntos
,tmCodTarDct,tmCodTarIva,tmCodTarIca,tmCodTarRet,tmCodTarCom,tmCodTarCmc,tmListaPrec,tmVrBase,tmCdMoneda,tmVrTasaCamb
,tmServcios,Tanques,tmNoVentas,tmEsCombo,tmEsProdBase,tmItemCbo,tmTipDoc,tmDocumento,tmIdCia
,tmTipRem,tmRemision,tmIdCiaRem,tmReferencia2,tmFecOrden,tmCdAgencia,tmRec_Costo,tmMgenCont,tmIvaComb,tmImpCarb
,tmCantObseq,tmIvaObseq,tmBaseIvp,tmTarifaIvp,tmIvaIngProd
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_KdexDvf] @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmItem,tmIdProducto,DescripProd,tmIdBodega,Bodega,tmCdTanque,tmEntradas,tmVrPrecio,tmTarifaDct,tmVrDcto
,(tmVrPrecio*tmEntradas)-tmVrDcto AS SubTotal,tmTarifaIva,tmVrIva,((tmVrPrecio*tmEntradas)-tmVrDcto)+tmVrIva AS ValorTotal
,tmSobretasa,tmTasaNac,tmTasaDep,tmTasaMun,tmSoldicom,tmImpGlobal,tmTarifaRet,tmVrRete,tmTarifaIca,tmVrIca,tmTarifaIco,tmVrImpCon
,tmVrUnitario,tmVrUnitario*tmEntradas AS CostoTotal,tmVrBruto,tmUnidades,tmIdUnd,Unidad,tmReferencia,tmDescripcion
,tmIdVend,tmComision,tmCdCCosto,tmCdSubCos,tmCodTarDct,tmCodTarIva,tmCodTarIca,tmCodTarRet
,tmCodTarCom,tmListaPrec,tmVrBase,tmCdMoneda,tmVrTasaCamb
,tmServcios,Tanques,tmNoVentas,tmEsCombo,tmEsProdBase,tmItemCbo,tmTipDoc,tmDocumento,tmIdCia
,tmTipRem,tmRemision,tmIdCiaRem,tmReferencia2,tmFecOrden,tmCdAgencia,tmCantObseq,tmIvaObseq,tmBaseIvp,tmTarifaIvp,tmIvaIngProd
 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 ON
GO
CREATE PROCEDURE [dbo].[paQrySubGruposLta] @pmIdGrupo VARCHAR(8)=Null,@pmIdLinea VARCHAR(8)=Null
,@pmTipoInvent VARCHAR(10)=Null
 AS 
 SELECT IdSubgrupo,Subgrupo,S.IdGrupo AS CdGrupo,Grupo,G.IdLinea AS CdLinea,Linea,CdTarIva
,TP.Tarifa,TP.Simbolo,TP.Descripcion AS DescIva
,CdTarAut,TA.Tarifa AS TarifaAut,TA.Simbolo AS SimbAut,TA.Descripcion AS DescAut
,CdTarRet,TR.Tarifa AS TarifaRet,TR.Simbolo AS SimbRet,TR.Descripcion AS DescRet
,CdTarIca,TI.Tarifa AS TarifaIca,TI.Simbolo AS SimbIca,TI.Descripcion AS DescIca
,CdTarifImv,TM.Tarifa AS TarifaImv,TM.Simbolo AS SimbImv,TM.Descripcion AS DescImv
,CdTarifIco,TC.Tarifa AS TarifaIco,TC.Simbolo AS SimbIco,TC.Descripcion AS DescIco
,S.CdTarIvp,TPR.Tarifa AS TarifaIvp,TPR.Simbolo AS SimbIvp
,S.TipoInvent AS TipoInventario
 FROM SubGrupos AS S INNER JOIN Grupos AS G ON S.IdGrupo=G.IdGrupo
 INNER JOIN Lineas AS L ON G.IdLinea=L.IdLinea
 LEFT JOIN Tablapor AS TP ON S.CdTarIva=TP.IdTarifa
 LEFT JOIN Tablapor AS TR ON S.CdTarRet=TR.IdTarifa
 LEFT JOIN Tablapor AS TI ON S.CdTarIca=TI.IdTarifa
 LEFT JOIN Tablapor AS TA ON S.CdTarAut=TA.IdTarifa
 LEFT JOIN Tablapor AS TM ON S.CdTarifImv=TM.IdTarifa
 LEFT JOIN Tablapor AS TC ON S.CdTarifIco=TC.IdTarifa
 LEFT JOIN Tablapor AS TPR ON S.CdTarIvp=TPR.IdTarifa
 WHERE S.Inactivo=0 AND S.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND G.IdLinea LIKE ISNULL(@pmIdLinea,'%')
 AND S.TipoInvent LIKE ISNULL(@pmTipoInvent,'%')
 ORDER BY Linea,Grupo,Subgrupo
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryDevFcrLta] @pmTipDev VARCHAR(3),@pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME,@pmDevolucionIni INT=Null,@pmDevolucionFin INT=Null
,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdConcepto VARCHAR(4)=Null,@pmIdVend VARCHAR(16)=Null,@pmModalidad VARCHAR(10)=Null
,@pmModdDev VARCHAR(10)=Null
 AS 
 SELECT Devolucion,IdCia,Fecha,D.IdConcepto AS CdConcepto,Concepto,TipDoc,Factura,IdCiaDoc,FecDoc,IdCliente,T.RazonSocial AS NomCliente,IdAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion
,VrReteICA,VrReteIVA,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrCostos,VrSobretasa,VrImpGlobal,VrFaltantes,VrAnticipos,VrImpCarbono,VrIvaIngProd,VrNeto,Cantidad,CantPuntos,BaseImp,BaseRet,BaseIvaIgp,IdCCosto,IdSubCos
,IdVend,V.RazonSocial AS Vendedor,TarifaCom,CodTarCom,IdLocEnv,Pedido,IdCiaPed,Remision,IdCiaRem,Cotizacion,IdCiaCot,FecPedido,PlacaVehic,KmtVehic,VrImpCons,VrReteCREE,TarifaRtc,CodTarRtc,BaseIvaObsq,VrIvaObsequio
,DevMasivo,NumRecibo,CdCiaRec,Modalidad,ModdDev,ZonaFrontera,CdMotDev,OrigenAdd,TipCom,Comprobante,IdCiaCom,D.Observacion AS Observ,D.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,D.IdUsuario AS IdUsuari,Usuario,TipDev
 FROM Trn_DevFcr AS D INNER JOIN Terceros AS T ON D.IdCliente=T.IdTercero
 INNER JOIN Terceros AS V ON D.IdVend=V.IdTercero
 INNER JOIN  Conceptos AS CN ON D.IdConcepto=CN.IdConcepto
 INNER JOIN EstadoDoc AS ED ON D.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario 
 WHERE TipDev=@pmTipDev  AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 AND Devolucion BETWEEN ISNULL(@pmDevolucionIni,0) AND ISNULL(@pmDevolucionFin,2147483647)
 AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdCliente LIKE ISNULL(@pmIdCliente,'%') AND D.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND IdVend LIKE ISNULL(@pmIdVend,'%') AND Modalidad LIKE ISNULL(@pmModalidad,'%')
 AND ModdDev LIKE ISNULL(@pmModdDev,'%') 
 ORDER BY IdCia,Devolucion
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryDevFcr] @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDev,Devolucion,IdCia,Fecha,IdConcepto,TipDoc,Factura,IdCiaDoc,FecDoc,IdCliente,IdAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA
,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrCostos,VrSobretasa,VrImpGlobal,VrFaltantes,VrAnticipos,VrIvaIngProd,VrNeto,Cantidad,CantPuntos,BaseImp,BaseRet,BaseIvaIgp,IdCCosto,IdSubCos,IdVend,TarifaCom
,CodTarCom,IdLocEnv,Pedido,IdCiaPed,Remision,IdCiaRem,Cotizacion,IdCiaCot,FecPedido,PlacaVehic,KmtVehic,Modalidad,ModdDev,OrigenAdd,TipCom,Comprobante,IdCiaCom
,Observacion,IdEstado,ZonaFrontera,VrImpCons,VrReteCREE,TarifaRtc,CodTarRtc,BaseIvaObsq,VrIvaObsequio,VrImpCarbono,DevMasivo,NumRecibo,CdCiaRec,CdMotDev,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_DevFcr
 WHERE TipDev=@pmTipDev AND Devolucion=@pmDevolucion AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpDevFcr] @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmTipDoc VARCHAR(3),@pmFactura INT,@pmIdCiaDoc CHAR(2),@pmFecDoc SMALLDATETIME,@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmVrSubTotal MONEY,@pmVrDescuento MONEY
,@pmVrImpuesto MONEY,@pmVrRetencion MONEY,@pmVrReteICA MONEY,@pmVrReteIVA MONEY,@pmVrFletes MONEY,@pmVrOtros MONEY,@pmVrCargos MONEY,@pmVrOtrDcto MONEY,@pmVrCostos MONEY,@pmVrSobretasa MONEY,@pmVrImpGlobal MONEY,@pmVrFaltantes MONEY,@pmVrAnticipos MONEY,@pmVrNeto MONEY,@pmCantidad DECIMAL(14,4)
,@pmCantPuntos DECIMAL(14,4),@pmBaseImp MONEY,@pmBaseRet MONEY,@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdVend VARCHAR(16),@pmTarifaCom DECIMAL(14,4),@pmCodTarCom VARCHAR(4),@pmIdLocEnv VARCHAR(8),@pmPedido INT,@pmIdCiaPed CHAR(2),@pmRemision INT,@pmIdCiaRem CHAR(2),@pmCotizacion INT
,@pmIdCiaCot CHAR(2),@pmFecPedido SMALLDATETIME,@pmModalidad VARCHAR(10),@pmPlacaVehic VARCHAR(10),@pmKmtVehic INT,@pmModdDev VARCHAR(10),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmZonaFrontera INT,@pmVrImpCons MONEY,@pmVrReteCREE MONEY
,@pmTarifaRtc DECIMAL(14,4),@pmCodTarRtc VARCHAR(4),@pmBaseIvaObsq MONEY,@pmVrIvaObsequio MONEY,@pmVrImpCarbono MONEY,@pmDevMasivo INT,@pmNumRecibo INT,@pmCdCiaRec CHAR(2),@pmCdMotDev VARCHAR(4),@pmBaseIvaIgp MONEY,@pmVrIvaIngProd MONEY,@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_DevFcr SET Fecha=@pmFecha,IdConcepto=@pmIdConcepto,TipDoc=@pmTipDoc,Factura=@pmFactura,IdCiaDoc=@pmIdCiaDoc,FecDoc=@pmFecDoc,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,VrSubTotal=@pmVrSubTotal,VrDescuento=@pmVrDescuento,VrImpuesto=@pmVrImpuesto,VrRetencion=@pmVrRetencion,VrReteICA=@pmVrReteICA
,VrReteIVA=@pmVrReteIVA,VrFletes=@pmVrFletes,VrOtros=@pmVrOtros,VrCargos=@pmVrCargos,VrOtrDcto=@pmVrOtrDcto,VrCostos=@pmVrCostos,VrSobretasa=@pmVrSobretasa,VrImpGlobal=@pmVrImpGlobal,VrFaltantes=@pmVrFaltantes,VrAnticipos=@pmVrAnticipos,VrNeto=@pmVrNeto,Cantidad=@pmCantidad,CantPuntos=@pmCantPuntos,BaseImp=@pmBaseImp
,BaseRet=@pmBaseRet,IdCCosto=@pmIdCCosto,IdSubCos=@pmIdSubCos,IdVend=@pmIdVend,TarifaCom=@pmTarifaCom,CodTarCom=@pmCodTarCom,IdLocEnv=@pmIdLocEnv,Pedido=@pmPedido,IdCiaPed=@pmIdCiaPed,Remision=@pmRemision,IdCiaRem=@pmIdCiaRem,Cotizacion=@pmCotizacion,IdCiaCot=@pmIdCiaCot
,FecPedido=@pmFecPedido,Modalidad=@pmModalidad,ModdDev=@pmModdDev,PlacaVehic=@pmPlacaVehic,KmtVehic=@pmKmtVehic,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,Observacion=@pmObservacion,IdEstado=@pmIdEstado,ZonaFrontera=@pmZonaFrontera,VrImpCons=@pmVrImpCons,FecUpdate=@pmFecUpdate
,VrReteCREE=@pmVrReteCREE,TarifaRtc=@pmTarifaRtc,CodTarRtc=@pmCodTarRtc,BaseIvaObsq=@pmBaseIvaObsq,VrIvaObsequio=@pmVrIvaObsequio,VrImpCarbono=@pmVrImpCarbono,DevMasivo=@pmDevMasivo,NumRecibo=@pmNumRecibo,CdCiaRec=@pmCdCiaRec,CdMotDev=@pmCdMotDev,BaseIvaIgp=@pmBaseIvaIgp,VrIvaIngProd=@pmVrIvaIngProd
 WHERE TipDev=@pmTipDev AND Devolucion=@pmDevolucion AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsDevFcr] @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmTipDoc VARCHAR(3),@pmFactura INT,@pmIdCiaDoc CHAR(2),@pmFecDoc SMALLDATETIME,@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmVrSubTotal MONEY,@pmVrDescuento MONEY,@pmVrImpuesto MONEY
,@pmVrRetencion MONEY,@pmVrReteICA MONEY,@pmVrReteIVA MONEY,@pmVrFletes MONEY,@pmVrOtros MONEY,@pmVrCargos MONEY,@pmVrOtrDcto MONEY,@pmVrCostos MONEY,@pmVrSobretasa MONEY,@pmVrImpGlobal MONEY,@pmVrFaltantes MONEY,@pmVrAnticipos MONEY,@pmVrNeto MONEY,@pmCantidad DECIMAL(14,4),@pmCantPuntos DECIMAL(14,4),@pmBaseImp MONEY
,@pmBaseRet MONEY,@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdVend VARCHAR(16),@pmTarifaCom DECIMAL(14,4),@pmCodTarCom VARCHAR(4),@pmIdLocEnv VARCHAR(8),@pmPedido INT,@pmIdCiaPed CHAR(2),@pmRemision INT,@pmIdCiaRem CHAR(2),@pmCotizacion INT,@pmIdCiaCot CHAR(2),@pmFecPedido SMALLDATETIME,@pmModalidad VARCHAR(10)
,@pmPlacaVehic VARCHAR(10),@pmKmtVehic INT,@pmModdDev VARCHAR(10),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmZonaFrontera INT,@pmVrImpCons MONEY,@pmVrReteCREE MONEY,@pmTarifaRtc DECIMAL(14,4),@pmCodTarRtc VARCHAR(4),@pmBaseIvaObsq MONEY,@pmVrIvaObsequio MONEY,@pmVrImpCarbono MONEY
,@pmDevMasivo INT,@pmNumRecibo INT,@pmCdCiaRec CHAR(2),@pmCdMotDev VARCHAR(4),@pmBaseIvaIgp MONEY,@pmVrIvaIngProd MONEY,@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_DevFcr (TipDev,Devolucion,IdCia,Fecha,IdConcepto,TipDoc,Factura,IdCiaDoc,FecDoc,IdCliente,IdAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrCostos,VrSobretasa,VrImpGlobal,VrFaltantes,VrAnticipos,VrNeto,Cantidad,CantPuntos,BaseImp,BaseRet,IdCCosto,IdSubCos,IdVend,TarifaCom,CodTarCom,IdLocEnv
,Pedido,IdCiaPed,Remision,IdCiaRem,Cotizacion,IdCiaCot,FecPedido,Modalidad,ModdDev,PlacaVehic,KmtVehic,ZonaFrontera,OrigenAdd,TipCom,Comprobante,IdCiaCom,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario,VrImpCons,VrReteCREE,TarifaRtc,CodTarRtc,BaseIvaObsq,VrIvaObsequio,VrImpCarbono,DevMasivo,NumRecibo,CdCiaRec,CdMotDev,BaseIvaIgp,VrIvaIngProd)
 VALUES (@pmTipDev,@pmDevolucion,@pmIdCia,@pmFecha,@pmIdConcepto,@pmTipDoc,@pmFactura,@pmIdCiaDoc,@pmFecDoc,@pmIdCliente,@pmIdAgencia,@pmVrSubTotal,@pmVrDescuento,@pmVrImpuesto,@pmVrRetencion,@pmVrReteICA,@pmVrReteIVA,@pmVrFletes,@pmVrOtros,@pmVrCargos,@pmVrOtrDcto,@pmVrCostos,@pmVrSobretasa,@pmVrImpGlobal,@pmVrFaltantes
,@pmVrAnticipos,@pmVrNeto,@pmCantidad,@pmCantPuntos,@pmBaseImp,@pmBaseRet,@pmIdCCosto,@pmIdSubCos,@pmIdVend,@pmTarifaCom,@pmCodTarCom,@pmIdLocEnv,@pmPedido,@pmIdCiaPed,@pmRemision,@pmIdCiaRem,@pmCotizacion,@pmIdCiaCot,@pmFecPedido,@pmModalidad,@pmModdDev,@pmPlacaVehic,@pmKmtVehic,@pmZonaFrontera,@pmOrigenAdd,@pmTipCom,@pmComprobante,@pmIdCiaCom
,@pmObservacion,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmVrImpCons,@pmVrReteCREE,@pmTarifaRtc,@pmCodTarRtc,@pmBaseIvaObsq,@pmVrIvaObsequio,@pmVrImpCarbono,@pmDevMasivo,@pmNumRecibo,@pmCdCiaRec,@pmCdMotDev,@pmBaseIvaIgp,@pmVrIvaIngProd)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryOCombLta]  @pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME,@pmOCargueIni INT=Null,@pmOCargueFin INT=Null
,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdConcepto VARCHAR(4)=Null,@pmIdVend VARCHAR(16)=Null,@pmIdVehiculo VARCHAR(10)=Null
,@pmIdLocal VARCHAR(8)=Null,@pmIdEstado VARCHAR(4)=Null,@pmAnulado BIT=Null
 AS 
 SELECT OCargue,IdCia,Fecha,FechaDesp,FechaVence,O.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente,IdAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrSobretasa,VrImpGlobal,VrImpCarbono,VrIvaIngProd,VrNeto
,Cantidad,IdVend,V.RazonSocial AS Vendedor,TarifaCom,CodTarCom,DirEnvio,O.IdLocal AS CdCiudad,Localidad,LugarEnv,DiasEntraga,IdVehiculo,IdConductor,CD.RazonSocial AS Conductor,NitEmpTrans,EmpTrans,CdRuta,Referncia,IdForma,DetallePago,MulPlazos,IdPlazo
,TipPed,Pedido,IdCiaPed,TipGui,Guia,IdCiaGui,TipFac,Factura,IdCiaFac,FechaFact,AutzaMora,AutzaCupo,Aprobacion,FechaAprob,DetalleAprob,CdUsuAprob,OrigenAdd,Anulado,FecDev
,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado,ZonaFrontera,IdTerminal,TipoOrden,Remolque,O.IdUsuario AS IdUsuari,Usuario,TimeSys,IdCiaCrea,FecUpdate,TipDoc
 FROM Trn_OComb AS O  INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero
 INNER JOIN Terceros AS V ON O.IdVend=V.IdTercero
 INNER JOIN Terceros AS CD ON O.IdConductor=CD.IdTercero
 INNER JOIN  Conceptos AS CN ON O.IdConcepto=CN.IdConcepto
 INNER JOIN Localidades AS L ON O.IdLocal=L.IdLocal
 INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario 
 WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 AND OCargue BETWEEN ISNULL(@pmOCargueIni,0) AND ISNULL(@pmOCargueFin,2147483647)
 AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdCliente LIKE ISNULL(@pmIdCliente,'%') AND O.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND O.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND IdVend LIKE ISNULL(@pmIdVend,'%')
 AND IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')  AND O.IdLocal LIKE ISNULL(@pmIdLocal,'%')
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 ORDER BY IdCia,OCargue
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsOComb] @pmTipDoc VARCHAR(3),@pmOCargue INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFechaDesp SMALLDATETIME,@pmFechaVence SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmVrSubTotal MONEY,@pmVrDescuento MONEY
,@pmVrImpuesto MONEY,@pmVrFletes MONEY,@pmVrOtros MONEY,@pmVrCargos MONEY,@pmVrOtrDcto MONEY,@pmVrSobretasa MONEY,@pmVrImpGlobal MONEY,@pmVrNeto MONEY,@pmCantidad DECIMAL(14,4),@pmIdVend VARCHAR(16),@pmTarifaCom DECIMAL(14,4),@pmCodTarCom VARCHAR(4),@pmDirEnvio VARCHAR(250)
,@pmIdLocal VARCHAR(8),@pmLugarEnv VARCHAR(50),@pmDiasEntraga INT,@pmIdVehiculo VARCHAR(10),@pmIdConductor VARCHAR(16),@pmNitEmpTrans VARCHAR(16),@pmEmpTrans VARCHAR(150),@pmCdRuta VARCHAR(4),@pmReferncia VARCHAR(50),@pmIdForma VARCHAR(4),@pmDetallePago VARCHAR(150)
,@pmMulPlazos BIT,@pmIdPlazo VARCHAR(4),@pmTipPed VARCHAR(3),@pmPedido INT,@pmIdCiaPed CHAR(2),@pmTipGui VARCHAR(3),@pmGuia INT,@pmIdCiaGui CHAR(2),@pmTipFac VARCHAR(3),@pmFactura INT,@pmIdCiaFac CHAR(2),@pmFechaFact SMALLDATETIME,@pmAutzaMora INT,@pmAutzaCupo INT,@pmAprobacion INT,@pmFechaAprob SMALLDATETIME
,@pmDetalleAprob VARCHAR(250),@pmCdUsuAprob VARCHAR(11),@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmZonaFrontera INT,@pmIdTerminal VARCHAR(4),@pmTipoOrden INT,@pmRemolque VARCHAR(10),@pmVrImpCarbono MONEY,@pmVrIvaIngProd MONEY
,@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_OComb (TipDoc,OCargue,IdCia,Fecha,FechaDesp,FechaVence,IdConcepto,IdCliente,IdAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrSobretasa,VrImpGlobal,VrNeto,Cantidad,IdVend,TarifaCom,CodTarCom,DirEnvio,IdLocal,LugarEnv,DiasEntraga,IdVehiculo,IdConductor,NitEmpTrans,EmpTrans,CdRuta,Referncia
,IdForma,DetallePago,MulPlazos,IdPlazo,TipPed,Pedido,IdCiaPed,TipGui,Guia,IdCiaGui,TipFac,Factura,IdCiaFac,FechaFact,AutzaMora,AutzaCupo,Aprobacion,FechaAprob,DetalleAprob,CdUsuAprob,IdTerminal,OrigenAdd,ZonaFrontera,TipoOrden,Remolque,Anulado,FecDev,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario,VrImpCarbono,VrIvaIngProd)
 VALUES (@pmTipDoc,@pmOCargue,@pmIdCia,@pmFecha,@pmFechaDesp,@pmFechaVence,@pmIdConcepto,@pmIdCliente,@pmIdAgencia,@pmVrSubTotal,@pmVrDescuento,@pmVrImpuesto,@pmVrFletes,@pmVrOtros,@pmVrCargos,@pmVrOtrDcto,@pmVrSobretasa,@pmVrImpGlobal,@pmVrNeto,@pmCantidad,@pmIdVend,@pmTarifaCom,@pmCodTarCom
,@pmDirEnvio,@pmIdLocal,@pmLugarEnv,@pmDiasEntraga,@pmIdVehiculo,@pmIdConductor,@pmNitEmpTrans,@pmEmpTrans,@pmCdRuta,@pmReferncia,@pmIdForma,@pmDetallePago,@pmMulPlazos,@pmIdPlazo,@pmTipPed,@pmPedido,@pmIdCiaPed,@pmTipGui,@pmGuia,@pmIdCiaGui,@pmTipFac,@pmFactura,@pmIdCiaFac,@pmFechaFact,@pmAutzaMora,@pmAutzaCupo,@pmAprobacion
,@pmFechaAprob,@pmDetalleAprob,@pmCdUsuAprob,@pmIdTerminal,@pmOrigenAdd,@pmZonaFrontera,@pmTipoOrden,@pmRemolque,@pmAnulado,@pmFecDev,@pmObservacion,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmVrImpCarbono,@pmVrIvaIngProd)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpOComb] @pmTipDoc VARCHAR(3),@pmOCargue INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFechaDesp SMALLDATETIME,@pmFechaVence SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16)
,@pmVrSubTotal MONEY,@pmVrDescuento MONEY,@pmVrImpuesto MONEY,@pmVrFletes MONEY,@pmVrOtros MONEY,@pmVrCargos MONEY,@pmVrOtrDcto MONEY,@pmVrSobretasa MONEY,@pmVrImpGlobal MONEY,@pmVrNeto MONEY,@pmCantidad DECIMAL(14,4),@pmIdVend VARCHAR(16)
,@pmTarifaCom DECIMAL(14,4),@pmCodTarCom VARCHAR(4),@pmDirEnvio VARCHAR(250),@pmIdLocal VARCHAR(8),@pmLugarEnv VARCHAR(50),@pmDiasEntraga INT,@pmIdVehiculo VARCHAR(10),@pmIdConductor VARCHAR(16),@pmNitEmpTrans VARCHAR(16),@pmEmpTrans VARCHAR(150)
,@pmCdRuta VARCHAR(4),@pmReferncia VARCHAR(50),@pmIdForma VARCHAR(4),@pmDetallePago VARCHAR(150),@pmMulPlazos BIT,@pmIdPlazo VARCHAR(4),@pmTipPed VARCHAR(3),@pmPedido INT,@pmIdCiaPed CHAR(2),@pmTipGui VARCHAR(3),@pmGuia INT,@pmIdCiaGui CHAR(2),@pmTipFac VARCHAR(3),@pmFactura INT,@pmIdCiaFac CHAR(2)
,@pmFechaFact SMALLDATETIME,@pmAutzaMora INT,@pmAutzaCupo INT,@pmAprobacion INT,@pmFechaAprob SMALLDATETIME,@pmDetalleAprob VARCHAR(250),@pmCdUsuAprob VARCHAR(11),@pmAnulado BIT,@pmFecDev SMALLDATETIME
,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmZonaFrontera INT,@pmIdTerminal VARCHAR(4),@pmTipoOrden INT,@pmRemolque VARCHAR(10),@pmVrImpCarbono MONEY,@pmVrIvaIngProd MONEY,@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_OComb SET Fecha=@pmFecha,FechaDesp=@pmFechaDesp,FechaVence=@pmFechaVence,IdConcepto=@pmIdConcepto,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,VrSubTotal=@pmVrSubTotal,VrDescuento=@pmVrDescuento,VrImpuesto=@pmVrImpuesto,VrFletes=@pmVrFletes,VrOtros=@pmVrOtros,VrCargos=@pmVrCargos
,VrOtrDcto=@pmVrOtrDcto,VrSobretasa=@pmVrSobretasa,VrImpGlobal=@pmVrImpGlobal,VrNeto=@pmVrNeto,Cantidad=@pmCantidad,IdVend=@pmIdVend,TarifaCom=@pmTarifaCom,CodTarCom=@pmCodTarCom,DirEnvio=@pmDirEnvio,IdLocal=@pmIdLocal,LugarEnv=@pmLugarEnv,DiasEntraga=@pmDiasEntraga,IdVehiculo=@pmIdVehiculo
,IdConductor=@pmIdConductor,NitEmpTrans=@pmNitEmpTrans,EmpTrans=@pmEmpTrans,CdRuta=@pmCdRuta,Referncia=@pmReferncia,IdForma=@pmIdForma,DetallePago=@pmDetallePago,MulPlazos=@pmMulPlazos,IdPlazo=@pmIdPlazo,TipGui=@pmTipGui,Guia=@pmGuia,IdCiaGui=@pmIdCiaGui,TipFac=@pmTipFac,Factura=@pmFactura
,IdCiaFac=@pmIdCiaFac,FechaFact=@pmFechaFact,TipPed=@pmTipPed,Pedido=@pmPedido,IdCiaPed=@pmIdCiaPed,AutzaMora=@pmAutzaMora,AutzaCupo=@pmAutzaCupo,Aprobacion=@pmAprobacion,FechaAprob=@pmFechaAprob,DetalleAprob=@pmDetalleAprob,CdUsuAprob=@pmCdUsuAprob,Anulado=@pmAnulado
,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado,ZonaFrontera=@pmZonaFrontera,IdTerminal=@pmIdTerminal,TipoOrden=@pmTipoOrden,Remolque=@pmRemolque,VrImpCarbono=@pmVrImpCarbono,VrIvaIngProd=@pmVrIvaIngProd,FecUpdate=@pmFecUpdate
 WHERE TipDoc=@pmTipDoc AND OCargue=@pmOCargue AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryOComb] @pmTipDoc VARCHAR(3),@pmOCargue INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,OCargue,IdCia,Fecha,FechaDesp,FechaVence,IdConcepto,IdCliente,IdAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros,VrCargos,VrOtrDcto
,VrSobretasa,VrImpGlobal,VrNeto,Cantidad,VrImpCarbono,VrIvaIngProd,IdVend,TarifaCom,CodTarCom,DirEnvio,IdLocal,LugarEnv,DiasEntraga,IdVehiculo,IdConductor,NitEmpTrans,EmpTrans
,CdRuta,Referncia,IdForma,DetallePago,MulPlazos,IdPlazo,TipPed,Pedido,IdCiaPed,TipGui,Guia,IdCiaGui,TipFac,Factura,IdCiaFac,FechaFact,AutzaMora,AutzaCupo,Aprobacion,FechaAprob
,DetalleAprob,CdUsuAprob,OrigenAdd,ZonaFrontera,TipoOrden,IdTerminal,Remolque,Anulado,FecDev,Observacion,IdEstado,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_OComb
 WHERE TipDoc=@pmTipDoc AND OCargue=@pmOCargue AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpSubGrupos] @pmIdSubgrupo VARCHAR(8),@pmSubgrupo VARCHAR(150),@pmIdGrupo VARCHAR(8)
,@pmCdTarIva VARCHAR(4),@pmCdTarAut VARCHAR(4),@pmCdTarRet VARCHAR(4)
,@pmCdTarIca VARCHAR(4),@pmTipoInvent VARCHAR(10),@pmCdTarifImv VARCHAR(4),@pmCdTarifIco VARCHAR(4),@pmCdTarIvp VARCHAR(4),@pmInactivo BIT
 AS 
 UPDATE SubGrupos SET Subgrupo=@pmSubgrupo,IdGrupo=@pmIdGrupo,CdTarIva=@pmCdTarIva
,CdTarAut=@pmCdTarAut,CdTarRet=@pmCdTarRet,CdTarIca=@pmCdTarIca
,TipoInvent=@pmTipoInvent,Inactivo=@pmInactivo,CdTarifImv=@pmCdTarifImv,CdTarifIco=@pmCdTarifIco,CdTarIvp=@pmCdTarIvp
 WHERE IdSubgrupo=@pmIdSubgrupo
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQrySubGrupos] @pmIdSubgrupo VARCHAR(8)
 AS 
 SELECT IdSubgrupo,Subgrupo,IdGrupo,CdTarIva,CdTarAut,CdTarRet,CdTarIca
,TipoInvent,CdTarifImv,CdTarifIco,CdTarIvp,Inactivo
 FROM SubGrupos
 WHERE IdSubgrupo=@pmIdSubgrupo
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsSubGrupos] @pmIdSubgrupo VARCHAR(8),@pmSubgrupo VARCHAR(150),@pmIdGrupo VARCHAR(8)
,@pmCdTarIva VARCHAR(4),@pmCdTarAut VARCHAR(4),@pmCdTarRet VARCHAR(4),@pmCdTarIca VARCHAR(4)
,@pmTipoInvent VARCHAR(10),@pmCdTarifImv VARCHAR(4),@pmCdTarifIco VARCHAR(4),@pmCdTarIvp VARCHAR(4),@pmInactivo BIT
 AS 
 INSERT INTO SubGrupos (IdSubgrupo,Subgrupo,IdGrupo,CdTarIva,CdTarAut,CdTarRet,CdTarIca,TipoInvent,Inactivo,CdTarifImv,CdTarifIco,CdTarIvp)
 VALUES (@pmIdSubgrupo,@pmSubgrupo,@pmIdGrupo,@pmCdTarIva,@pmCdTarAut,@pmCdTarRet,@pmCdTarIca,@pmTipoInvent,@pmInactivo,@pmCdTarifImv,@pmCdTarifIco,@pmCdTarIvp)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_Kdex] @pmtmNumero VARCHAR(5),@pmtmItem INT,@pmtmIdProducto VARCHAR(16),@pmtmIdBodega VARCHAR(4),@pmtmCdTanque VARCHAR(4),@pmtmEntradas DECIMAL(14,4),@pmtmSalidas DECIMAL(14,4),@pmtmIdUnd VARCHAR(4),@pmtmVrUnitario MONEY,@pmtmVrPrecio MONEY,@pmtmVrCostProm MONEY,@pmtmVrProm MONEY
,@pmtmTarifaIva DECIMAL(14,4),@pmtmVrIva MONEY,@pmtmTarifaDct DECIMAL(14,4),@pmtmVrDcto MONEY,@pmtmTarifaRet DECIMAL(14,4),@pmtmVrRete MONEY,@pmtmTarifaIca DECIMAL(14,4),@pmtmVrIca MONEY,@pmtmVrBruto MONEY,@pmtmCdUbic VARCHAR(10),@pmtmNumLote VARCHAR(30),@pmtmFechLote SMALLDATETIME,@pmtmFecVceLote SMALLDATETIME
,@pmtmIdTercero VARCHAR(16),@pmtmCdAgencia VARCHAR(16),@pmtmCdCCosto VARCHAR(16),@pmtmCdSubCos VARCHAR(16),@pmtmCdLocal VARCHAR(8),@pmtmCdSzona VARCHAR(4),@pmtmpVehiculo VARCHAR(10),@pmtmIdVend VARCHAR(16),@pmtmComision DECIMAL(14,4),@pmtmCdOperario VARCHAR(16),@pmtmComisnOper DECIMAL(14,4),@pmtmReferencia VARCHAR(50)
,@pmtmDescripcion VARCHAR(250),@pmtmComptmntos VARCHAR(50),@pmtmCdProdEquiv VARCHAR(16),@pmtmCdMngra VARCHAR(10),@pmtmNumInicial DECIMAL(14,4),@pmtmNumFinal DECIMAL(14,4),@pmtmSobretasa DECIMAL(14,4),@pmtmTasaNac DECIMAL(14,4),@pmtmTasaDep DECIMAL(14,4),@pmtmTasaMun DECIMAL(14,4),@pmtmSoldicom DECIMAL(14,4)
,@pmtmImpGlobal DECIMAL(14,4),@pmtmOtroImpto DECIMAL(14,4),@pmtmUnidades DECIMAL(14,4),@pmtmServcios BIT,@pmtmNoVentas INT,@pmtmEsCombo BIT,@pmtmEsProdBase INT,@pmtmItemCbo INT,@pmtmCodTarDct VARCHAR(4),@pmtmCodTarIva VARCHAR(4),@pmtmCodTarIca VARCHAR(4),@pmtmCodTarRet VARCHAR(4),@pmtmCodTarCom VARCHAR(4)
,@pmtmCodTarCmc VARCHAR(4),@pmtmCdSubgrupo VARCHAR(8),@pmtmListaPrec CHAR(1),@pmtmVrBase MONEY,@pmtmCdMoneda VARCHAR(5),@pmtmVrTasaCamb MONEY,@pmtmTipDoc VARCHAR(3),@pmtmDocumento INT,@pmtmIdCia CHAR(2),@pmtmTipRem VARCHAR(3),@pmtmRemision INT,@pmtmIdCiaRem CHAR(2),@pmtmReferencia2 VARCHAR(50),@pmtmFecOrden SMALLDATETIME
,@pmtmgalsbruto DECIMAL(14,4),@pmtmgalsneto DECIMAL(14,4),@pmtmTemperatura DECIMAL(14,4),@pmtmUmTemp VARCHAR(3),@pmtmDensidad DECIMAL(14,4),@pmtmRec_Costo DECIMAL(14,4),@pmtmMgenCont  DECIMAL(14,4),@pmtmCotizacion INT,@pmtmCiaCotiza CHAR(2),@pmtmVrImvCosto DECIMAL(16,4),@pmtmTarifaIco DECIMAL(14,4),@pmtmVrImpCon DECIMAL(16,4)
,@pmtmCantObseq DECIMAL(14,4),@pmtmIvaObseq MONEY,@pmtmIvaComb DECIMAL(16,4),@pmtmImpCarb DECIMAL(14,4),@pmtmIngCombo DECIMAL(16,4),@pmtmTarifaStc DECIMAL(14,4),@pmtmSobtasaCons DECIMAL(16,4),@pmtmCodTarIco VARCHAR(4),@pmtmBaseIvp MONEY,@pmtmTarifaIvp DECIMAL(14,4),@pmtmIvaIngProd MONEY
 AS 
 INSERT INTO tm_Kdex (tmNumero,tmItem,tmIdProducto,tmIdBodega,tmCdTanque,tmEntradas,tmSalidas,tmIdUnd,tmVrUnitario,tmVrPrecio,tmVrCostProm,tmVrProm,tmTarifaIva,tmVrIva,tmTarifaDct,tmVrDcto,tmTarifaRet,tmVrRete,tmTarifaIca,tmVrIca,tmVrBruto,tmCdUbic,tmNumLote,tmFechLote,tmFecVceLote,tmIdTercero,tmCdAgencia,tmCdCCosto,tmCdSubCos,tmCdLocal,tmCdSzona,tmpVehiculo,tmIdVend
,tmComision,tmCdOperario,tmComisnOper,tmReferencia,tmDescripcion,tmComptmntos,tmCdProdEquiv,tmCdMngra,tmNumInicial,tmNumFinal,tmSobretasa,tmTasaNac,tmTasaDep,tmTasaMun,tmSoldicom,tmImpGlobal,tmOtroImpto,tmUnidades,tmServcios,tmNoVentas,tmEsCombo,tmEsProdBase,tmItemCbo,tmCodTarDct,tmCodTarIva,tmCodTarIca,tmCodTarRet,tmCodTarCom,tmCodTarCmc,tmCdSubgrupo
,tmListaPrec,tmTipDoc,tmDocumento,tmIdCia,tmVrBase,tmCdMoneda,tmVrTasaCamb,tmTipRem,tmRemision,tmIdCiaRem,tmReferencia2,tmFecOrden,tmgalsbruto,tmgalsneto,tmTemperatura,tmUmTemp,tmDensidad,tmRec_Costo,tmMgenCont,tmCotizacion,tmCiaCotiza,tmVrImvCosto,tmTarifaIco,tmVrImpCon,tmCantObseq,tmIvaObseq,tmIvaComb,tmImpCarb,tmIngCombo,tmTarifaStc,tmSobtasaCons,tmCodTarIco
,tmBaseIvp,tmTarifaIvp,tmIvaIngProd)
 VALUES (@pmtmNumero,@pmtmItem,@pmtmIdProducto,@pmtmIdBodega,@pmtmCdTanque,@pmtmEntradas,@pmtmSalidas,@pmtmIdUnd,@pmtmVrUnitario,@pmtmVrPrecio,@pmtmVrCostProm,@pmtmVrProm,@pmtmTarifaIva,@pmtmVrIva,@pmtmTarifaDct,@pmtmVrDcto,@pmtmTarifaRet,@pmtmVrRete,@pmtmTarifaIca,@pmtmVrIca,@pmtmVrBruto,@pmtmCdUbic,@pmtmNumLote,@pmtmFechLote,@pmtmFecVceLote
,@pmtmIdTercero,@pmtmCdAgencia,@pmtmCdCCosto,@pmtmCdSubCos,@pmtmCdLocal,@pmtmCdSzona,@pmtmpVehiculo,@pmtmIdVend,@pmtmComision,@pmtmCdOperario,@pmtmComisnOper,@pmtmReferencia,@pmtmDescripcion,@pmtmComptmntos,@pmtmCdProdEquiv,@pmtmCdMngra,@pmtmNumInicial,@pmtmNumFinal,@pmtmSobretasa,@pmtmTasaNac,@pmtmTasaDep,@pmtmTasaMun,@pmtmSoldicom
,@pmtmImpGlobal,@pmtmOtroImpto,@pmtmUnidades,@pmtmServcios,@pmtmNoVentas,@pmtmEsCombo,@pmtmEsProdBase,@pmtmItemCbo,@pmtmCodTarDct,@pmtmCodTarIva,@pmtmCodTarIca,@pmtmCodTarRet,@pmtmCodTarCom,@pmtmCodTarCmc,@pmtmCdSubgrupo,@pmtmListaPrec,@pmtmTipDoc,@pmtmDocumento,@pmtmIdCia
,@pmtmVrBase,@pmtmCdMoneda,@pmtmVrTasaCamb,@pmtmTipRem,@pmtmRemision,@pmtmIdCiaRem,@pmtmReferencia2,@pmtmFecOrden,@pmtmgalsbruto,@pmtmgalsneto,@pmtmTemperatura,@pmtmUmTemp,@pmtmDensidad,@pmtmRec_Costo,@pmtmMgenCont,@pmtmCotizacion,@pmtmCiaCotiza,@pmtmVrImvCosto,@pmtmTarifaIco,@pmtmVrImpCon,@pmtmCantObseq,@pmtmIvaObseq
,@pmtmIvaComb,@pmtmImpCarb,@pmtmIngCombo,@pmtmTarifaStc,@pmtmSobtasaCons,@pmtmCodTarIco,@pmtmBaseIvp,@pmtmTarifaIvp,@pmtmIvaIngProd)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_Kdex] @pmtmNumero VARCHAR(5),@pmtmItem INT
 AS 
 SELECT tmNumero,tmItem,tmIdProducto,tmIdBodega,tmCdTanque,tmEntradas,tmSalidas,tmIdUnd,tmVrUnitario,tmVrPrecio,tmVrCostProm
,tmVrProm,tmTarifaIva,tmVrIva,tmTarifaDct,tmVrDcto,tmTarifaRet,tmVrRete,tmTarifaIca,tmVrIca,tmVrBruto,tmCdUbic,tmNumLote,tmFechLote
,tmFecVceLote,tmIdTercero,tmCdAgencia,tmCdCCosto,tmCdSubCos,tmCdLocal,tmCdSzona,tmpVehiculo,tmIdVend,tmComision
,tmCdOperario,tmComisnOper,tmReferencia,tmDescripcion,tmComptmntos,tmCdProdEquiv,tmCdMngra,tmNumInicial,tmNumFinal
,tmSobretasa,tmTasaNac,tmTasaDep,tmTasaMun,tmSoldicom,tmImpGlobal,tmOtroImpto,tmUnidades,tmServcios,tmNoVentas
,tmEsCombo,tmEsProdBase,tmItemCbo,tmCodTarDct,tmCodTarIva,tmCodTarIca,tmCodTarRet,tmCodTarCom,tmCodTarCmc
,tmCdSubgrupo,tmListaPrec,tmVrBase,tmCdMoneda,tmVrTasaCamb
,tmTipDoc,tmDocumento,tmIdCia,tmTipRem,tmRemision,tmIdCiaRem,tmReferencia2,tmFecOrden,tmgalsbruto,tmgalsneto,tmTemperatura,tmUmTemp,tmDensidad
,tmRec_Costo,tmMgenCont,tmCotizacion,tmCiaCotiza,tmVrImvCosto,tmTarifaIco,tmVrImpCon,tmCantObseq,tmIvaObseq,tmIvaComb,tmImpCarb,tmIngCombo
,tmTarifaStc,tmSobtasaCons,tmCodTarIco,tmBaseIvp,tmTarifaIvp,tmIvaIngProd
 FROM tm_Kdex
 WHERE tmNumero=@pmtmNumero 
 AND (tmItem>=ISNULL(@pmtmItem,0)  AND tmItem<=ISNULL(@pmtmItem,2147483647))
 ORDER BY  tmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInstm_Kdex_SelDev] @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2),@pmtmNumero VARCHAR(5)
 AS 
 INSERT INTO tm_Kdex (tmNumero,tmItem,tmIdProducto,tmIdBodega,tmCdTanque,tmEntradas,tmSalidas,tmIdUnd,tmVrUnitario,tmVrPrecio,tmVrCostProm,tmVrProm,tmTarifaIva,tmVrIva,tmTarifaDct,tmVrDcto,tmTarifaRet,tmVrRete
,tmTarifaIca,tmVrIca,tmVrBruto,tmCdUbic,tmNumLote,tmFechLote,tmFecVceLote,tmIdTercero,tmCdAgencia,tmCdCCosto,tmCdSubCos,tmCdLocal,tmCdSzona,tmpVehiculo,tmIdVend
,tmComision,tmCdOperario,tmComisnOper,tmReferencia,tmDescripcion,tmComptmntos,tmCdProdEquiv,tmCdMngra,tmNumInicial,tmNumFinal,tmSobretasa,tmTasaNac,tmTasaDep,tmTasaMun,tmSoldicom,tmImpGlobal,tmOtroImpto
,tmUnidades,tmServcios,tmNoVentas,tmEsCombo,tmEsProdBase,tmItemCbo,tmCodTarDct,tmCodTarIva,tmCodTarIca,tmCodTarRet,tmCodTarCom,tmCodTarCmc,tmCdSubgrupo
,tmListaPrec,tmTipDoc,tmDocumento,tmIdCia,tmVrBase,tmCdMoneda,tmVrTasaCamb,tmTipRem,tmRemision,tmIdCiaRem,tmReferencia2,tmFecOrden,tmgalsbruto,tmgalsneto,tmTemperatura,tmUmTemp,tmDensidad,tmRec_Costo,tmMgenCont
,tmCotizacion,tmCiaCotiza,tmVrImvCosto,tmTarifaIco,tmVrImpCon,tmCantObseq,tmIvaObseq,tmIvaComb,tmImpCarb,tmIngCombo,tmTarifaStc,tmSobtasaCons,tmCodTarIco,tmBaseIvp,tmTarifaIvp,tmIvaIngProd)
SELECT @pmtmNumero,Item,K.IdProducto,K.IdBodega,CdTanque,CASE WHEN CantObseq>0 AND (TipDoc='DVE' OR TipDoc='DEI' OR SUBSTRING(TipDoc,1,2)='FC' OR SUBSTRING(TipDoc,1,2)='FO') THEN Salidas-CantObseq ELSE Salidas END
,CASE WHEN CantObseq>0 AND (TipDoc='ENT' OR TipDoc='COM' OR SUBSTRING(TipDoc,1,2)='DF' OR SUBSTRING(TipDoc,1,2)='DO') THEN Entradas-CantObseq ELSE Entradas END
,K.IdUnd,VrUnitario,VrPrecio,VrCostProm,0,TarifaIva,CASE WHEN CantObseq>0 AND VrIvaObseq>0 THEN (VrIvaEnt+VrIvaSal)-VrIvaObseq ELSE VrIvaEnt+VrIvaSal END,TarifaDct,VrDctoEnt+VrDctoSal,TarifaRet,VrReteEnt+VrReteSal
,TarifaIca,VrIcaEnt+VrIcaSal,VrBruto,CdUbic,NumLote,FechLote,FechLote,IdTercero,CdAgencia,CdCCosto,CdSubCos,CdLocal,CdSzona,pVehiculo,IdVend
,Comision,CdOperario,ComisnOper,K.Referencia,Descripcion,Comptmntos,CdProdEquiv,CdMngra,NumInicial,NumFinal,Sobretasa,TasaNac,TasaDep,TasaMun,Soldicom,ImpGlobal,OtroImpto
,Unidades,Servcios,NoVentas,EsCombo,EsProdBase,ItemCombo,CodTarDct,CodTarIva,CodTarIca,CodTarRet,CodTarCom,CodTarCmc,IdSubgrupo,ListaPrec,TipOrd,NumOrden,IdCiaOrd,VrBase,CdMoneda,VrTasaCamb
,'REM',Remision,IdCiaRem,Referencia2,FecOrden,galsbruto,galsneto,Temperatura,UmTemp,Densidad,Rec_Costo,MgenCont,Cotizacion,IdCiaCot,VrImvCosto,TarifaIco,VrImpCon,CantObseq,VrIvaObseq
,BaseIvaCom,ImpCarbono,IngBaseCom,TarifaStc,SobtasaCons,CodTarIco,BaseIvp,TarifaIvp,IvaIngProd
 FROM Trn_Kardex AS K INNER JOIN ProdMcias AS P ON K.IdProducto=P.IdProducto
 WHERE TipDoc=@pmTipDoc AND Documento=@pmDocumento AND IdCia=@pmIdCia  AND  EsProdBase<=1
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInstm_Kdex_Nts] @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2),@pmtmNumero VARCHAR(5)
 AS 
 INSERT INTO tm_Kdex (tmNumero,tmItem,tmIdProducto,tmIdBodega,tmCdTanque,tmEntradas,tmSalidas,tmIdUnd,tmVrUnitario,tmVrPrecio,tmVrCostProm,tmVrProm,tmTarifaIva,tmVrIva,tmTarifaDct,tmVrDcto,tmTarifaRet,tmVrRete
,tmTarifaIca,tmVrIca,tmVrBruto,tmCdUbic,tmNumLote,tmFechLote,tmFecVceLote,tmIdTercero,tmCdAgencia,tmCdCCosto,tmCdSubCos,tmCdLocal,tmCdSzona,tmpVehiculo,tmIdVend
,tmComision,tmCdOperario,tmComisnOper,tmReferencia,tmDescripcion,tmComptmntos,tmCdProdEquiv,tmCdMngra,tmNumInicial,tmNumFinal,tmSobretasa,tmTasaNac,tmTasaDep,tmTasaMun,tmSoldicom,tmImpGlobal,tmOtroImpto
,tmUnidades,tmServcios,tmNoVentas,tmEsCombo,tmEsProdBase,tmItemCbo,tmCodTarDct,tmCodTarIva,tmCodTarIca,tmCodTarRet,tmCodTarCom,tmCodTarCmc,tmCdSubgrupo
,tmListaPrec,tmTipDoc,tmDocumento,tmIdCia,tmVrBase,tmCdMoneda,tmVrTasaCamb,tmTipRem,tmRemision,tmIdCiaRem,tmReferencia2,tmFecOrden,tmgalsbruto,tmgalsneto,tmTemperatura,tmUmTemp,tmDensidad,tmRec_Costo,tmMgenCont
,tmCotizacion,tmCiaCotiza,tmVrImvCosto,tmTarifaIco,tmVrImpCon,tmCantObseq,tmIvaObseq,tmIvaComb,tmImpCarb,tmIngCombo,tmTarifaStc,tmSobtasaCons,tmCodTarIco,tmBaseIvp,tmTarifaIvp,tmIvaIngProd)
SELECT @pmtmNumero,Item,K.IdProducto,K.IdBodega,CdTanque,Entradas,Salidas,K.IdUnd,VrUnitario,VrPrecio,VrCostProm,0,TarifaIva,VrIvaEnt+VrIvaSal,TarifaDct,VrDctoEnt+VrDctoSal,TarifaRet,VrReteEnt+VrReteSal
,TarifaIca,VrIcaEnt+VrIcaSal,VrBruto,CdUbic,NumLote,FechLote,FechLote,IdTercero,CdAgencia,CdCCosto,CdSubCos,CdLocal,CdSzona,pVehiculo,IdVend
,Comision,CdOperario,ComisnOper,K.Referencia,Descripcion,Comptmntos,CdProdEquiv,CdMngra,NumInicial,NumFinal,Sobretasa,TasaNac,TasaDep,TasaMun,Soldicom,ImpGlobal,OtroImpto
,Unidades,Servcios,NoVentas,EsCombo,EsProdBase,ItemCombo,CodTarDct,CodTarIva,CodTarIca,CodTarRet,CodTarCom,CodTarCmc,IdSubgrupo,ListaPrec,TipDoc,Documento,IdCia,VrBase,CdMoneda,VrTasaCamb
,'REM',Remision,IdCiaRem,Referencia2,FecOrden,0,0,0,'',0,Rec_Costo,MgenCont,Cotizacion,IdCiaCot,VrImvCosto,TarifaIco,VrImpCon,CantObseq,VrIvaObseq,BaseIvaCom,ImpCarbono,IngBaseCom,TarifaStc,SobtasaCons,CodTarIco
,BaseIvp,TarifaIvp,IvaIngProd
 FROM Trn_Kardex AS K INNER JOIN ProdMcias AS P ON K.IdProducto=P.IdProducto
 WHERE TipDoc=@pmTipDoc AND Documento=@pmDocumento AND IdCia=@pmIdCia 
 AND EsProdBase=0 AND Salidas>0
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInstm_Kdex_Sel] @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2),@pmtmNumero VARCHAR(5)
 AS 
 INSERT INTO tm_Kdex (tmNumero,tmItem,tmIdProducto,tmIdBodega,tmCdTanque,tmEntradas,tmSalidas,tmIdUnd,tmVrUnitario,tmVrPrecio,tmVrCostProm,tmVrProm,tmTarifaIva,tmVrIva,tmTarifaDct,tmVrDcto,tmTarifaRet,tmVrRete
,tmTarifaIca,tmVrIca,tmVrBruto,tmCdUbic,tmNumLote,tmFechLote,tmFecVceLote,tmIdTercero,tmCdAgencia,tmCdCCosto,tmCdSubCos,tmCdLocal,tmCdSzona,tmpVehiculo,tmIdVend
,tmComision,tmCdOperario,tmComisnOper,tmReferencia,tmDescripcion,tmComptmntos,tmCdProdEquiv,tmCdMngra,tmNumInicial,tmNumFinal,tmSobretasa,tmTasaNac,tmTasaDep,tmTasaMun,tmSoldicom,tmImpGlobal,tmOtroImpto
,tmUnidades,tmServcios,tmNoVentas,tmEsCombo,tmEsProdBase,tmItemCbo,tmCodTarDct,tmCodTarIva,tmCodTarIca,tmCodTarRet,tmCodTarCom,tmCodTarCmc,tmCdSubgrupo
,tmListaPrec,tmTipDoc,tmDocumento,tmIdCia,tmVrBase,tmCdMoneda,tmVrTasaCamb,tmTipRem,tmRemision,tmIdCiaRem,tmReferencia2,tmFecOrden,tmgalsbruto,tmgalsneto,tmTemperatura,tmUmTemp,tmDensidad
,tmRec_Costo,tmMgenCont,tmCotizacion,tmCiaCotiza,tmVrImvCosto,tmTarifaIco,tmVrImpCon,tmCantObseq,tmIvaObseq,tmIvaComb,tmImpCarb,tmIngCombo,tmTarifaStc,tmSobtasaCons,tmCodTarIco,tmBaseIvp,tmTarifaIvp,tmIvaIngProd)
SELECT @pmtmNumero,Item,K.IdProducto,K.IdBodega,CdTanque,CASE WHEN CantObseq>0 AND (TipDoc='ENT' OR TipDoc='COM' OR SUBSTRING(TipDoc,1,2)='DF' OR SUBSTRING(TipDoc,1,2)='DO') THEN Entradas-CantObseq ELSE Entradas END
,CASE WHEN CantObseq>0 AND (TipDoc='DVE' OR TipDoc='DEI' OR SUBSTRING(TipDoc,1,2)='FC' OR SUBSTRING(TipDoc,1,2)='FO') THEN Salidas-CantObseq ELSE Salidas END
,K.IdUnd,VrUnitario,VrPrecio,VrCostProm,0,TarifaIva,CASE WHEN CantObseq>0 AND VrIvaObseq>0 THEN (VrIvaEnt+VrIvaSal)-VrIvaObseq ELSE VrIvaEnt+VrIvaSal END
,TarifaDct,VrDctoEnt+VrDctoSal,TarifaRet,VrReteEnt+VrReteSal
,TarifaIca,VrIcaEnt+VrIcaSal,VrBruto,CdUbic,NumLote,FechLote,FechLote,IdTercero,CdAgencia,CdCCosto,CdSubCos,CdLocal,CdSzona,pVehiculo,IdVend
,Comision,CdOperario,ComisnOper,K.Referencia,Descripcion,Comptmntos,CdProdEquiv,CdMngra,NumInicial,NumFinal,Sobretasa,TasaNac,TasaDep,TasaMun,Soldicom,ImpGlobal,OtroImpto
,Unidades,Servcios,NoVentas,EsCombo,EsProdBase,ItemCombo,CodTarDct,CodTarIva,CodTarIca,CodTarRet,CodTarCom,CodTarCmc,IdSubgrupo,ListaPrec,TipOrd,NumOrden,IdCiaOrd,VrBase,CdMoneda,VrTasaCamb
,'REM',Remision,IdCiaRem,Referencia2,FecOrden,galsbruto,galsneto,Temperatura,UmTemp,Densidad,Rec_Costo,MgenCont,Cotizacion,IdCiaCot,VrImvCosto,TarifaIco,VrImpCon,CantObseq,VrIvaObseq
,BaseIvaCom,ImpCarbono,IngBaseCom,TarifaStc,SobtasaCons,CodTarIco,BaseIvp,TarifaIvp,IvaIngProd
 FROM Trn_Kardex AS K INNER JOIN ProdMcias AS P ON K.IdProducto=P.IdProducto
 WHERE TipDoc=@pmTipDoc AND Documento=@pmDocumento AND IdCia=@pmIdCia AND EsProdBase=0
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_KdexSubOpe] @pmtmNumero VARCHAR(5)
 AS 
 SELECT IdSubgrupo,tmIdProducto,DescripProd,TipoRef,Combo,tmEsProdBase,IdProv,ExcluidoImp,Electrocomb,IvaDetCombo,tmCdOperario
,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(tmEntradas*tmImpGlobal) AS SGLOENT,SUM(tmSalidas*tmImpGlobal) AS SGLOSAL
,SUM(tmSobretasa*tmEntradas) AS SSOBENT,SUM(tmSobretasa*tmSalidas) AS SSOBSAL
,SUM(tmTasaNac*tmEntradas) AS SNACENT,SUM(tmTasaNac*tmSalidas) AS SNACSAL
,SUM(tmTasaDep*tmEntradas) AS SDEPENT,SUM(tmTasaDep*tmSalidas) AS SDEPSAL
,SUM(tmTasaMun*tmEntradas) AS SMUNENT,SUM(tmTasaMun*tmSalidas) AS SMUNSAL
,SUM(tmSoldicom*tmEntradas) AS SSOLENT,SUM(tmSoldicom*tmSalidas) AS SSOLSAL
,SUM(tmOtroImpto*tmEntradas) AS SOTRENT,SUM(tmOtroImpto*tmSalidas) AS SOTRSAL
--Para los ajustes con cantidad en cero (0)
,SUM(tmVrUnitario) AS SCOSAJU,SUM(tmOtroImpto) AS SDVEAJU
,SUM(tmRec_Costo*tmEntradas) AS SRCOSENT,SUM(tmRec_Costo*tmSalidas) AS SRCOSSAL
,SUM(tmMgenCont*tmEntradas) AS SMGENENT,SUM(tmMgenCont*tmSalidas) AS SMGENSAL
,SUM(tmVrImvCosto) AS SIMVCOS,SUM(tmVrImpCon) AS SVICO
,SUM(CASE WHEN tmCantObseq>0 THEN tmIvaObseq ELSE 0 END) AS SIVAOBSQ
,SUM(tmCantObseq*tmVrUnitario) AS SBASCOSOBSQ,SUM(tmCantObseq*tmVrPrecio) AS SBASEOBSQ
,SUM(tmIvaComb*tmEntradas) AS BASEIVAENT,SUM(tmIvaComb*tmSalidas) AS BASEIVASAL 
,SUM(tmImpCarb*tmEntradas) AS IMPCARBENT,SUM(tmImpCarb*tmSalidas) AS IMPCARBSAL 
,SUM(CASE WHEN (tmTarifaIva>0 AND P.TipoZonaFront='F') THEN ((tmEntradas*tmIvaComb)*tmTarifaIva)/100 ELSE 0 END) AS SIVAZFENT
,SUM(CASE WHEN (tmTarifaIva>0 AND P.TipoZonaFront='F') THEN ((tmSalidas*tmIvaComb)*tmTarifaIva)/100 ELSE 0 END) AS SIVAZFSAL
,SUM(CASE WHEN Combo=0 AND tmEsProdBase=1 THEN tmIvaComb ELSE 0 END) AS BASEIVACOM
,SUM(CASE WHEN Combo=1 AND IvaDetCombo=1 THEN tmIngCombo ELSE 0 END) AS INGBASECOM
,SUM(CASE WHEN Combo=0 AND tmEsProdBase=1 THEN tmIngCombo ELSE 0 END) AS SBASEING
,SUM(tmSobtasaCons) AS SOBTCON,SUM(tmBaseIvp*tmEntradas) AS BASEIVPENT,SUM(tmBaseIvp*tmSalidas) AS BASEIVAING,SUM(tmIvaIngProd) AS SIVAINGP,MAX(tmTarifaIvp) AS TARIVAING
 FROM tm_Kdex AS K INNER JOIN ProdMcias AS P ON K.tmIdProducto=P.IdProducto
 WHERE tmNumero=@pmtmNumero
 GROUP BY IdSubgrupo,tmIdProducto,DescripProd,TipoRef,Combo,tmEsProdBase,IdProv,ExcluidoImp,Electrocomb,IvaDetCombo,tmCdOperario
 ORDER BY IdSubgrupo,tmIdProducto
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_KdexSub] @pmtmNumero VARCHAR(5)
 AS 
 SELECT IdSubgrupo,tmIdProducto,DescripProd,TipoRef,Combo,tmEsProdBase,IdProv,ExcluidoImp,Electrocomb,IvaDetCombo
,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(tmEntradas*tmImpGlobal) AS SGLOENT,SUM(tmSalidas*tmImpGlobal) AS SGLOSAL
,SUM(tmSobretasa*tmEntradas) AS SSOBENT,SUM(tmSobretasa*tmSalidas) AS SSOBSAL
,SUM(tmTasaNac*tmEntradas) AS SNACENT,SUM(tmTasaNac*tmSalidas) AS SNACSAL
,SUM(tmTasaDep*tmEntradas) AS SDEPENT,SUM(tmTasaDep*tmSalidas) AS SDEPSAL
,SUM(tmTasaMun*tmEntradas) AS SMUNENT,SUM(tmTasaMun*tmSalidas) AS SMUNSAL
,SUM(tmSoldicom*tmEntradas) AS SSOLENT,SUM(tmSoldicom*tmSalidas) AS SSOLSAL
,SUM(tmOtroImpto*tmEntradas) AS SOTRENT,SUM(tmOtroImpto*tmSalidas) AS SOTRSAL
--Para los ajustes con cantidad en cero (0)
,SUM(tmVrUnitario) AS SCOSAJU,SUM(tmOtroImpto) AS SDVEAJU
,SUM(tmRec_Costo*tmEntradas) AS SRCOSENT,SUM(tmRec_Costo*tmSalidas) AS SRCOSSAL
,SUM(tmMgenCont*tmEntradas) AS SMGENENT,SUM(tmMgenCont*tmSalidas) AS SMGENSAL
,SUM(tmVrImvCosto) AS SIMVCOS,SUM(tmVrImpCon) AS SVICO
,SUM(CASE WHEN tmCantObseq>0 THEN tmIvaObseq ELSE 0 END) AS SIVAOBSQ
,SUM(tmCantObseq*tmVrUnitario) AS SBASCOSOBSQ,SUM(tmCantObseq*tmVrPrecio) AS SBASEOBSQ
,SUM(tmIvaComb*tmEntradas) AS BASEIVAENT,SUM(tmIvaComb*tmSalidas) AS BASEIVASAL 
,SUM(tmImpCarb*tmEntradas) AS IMPCARBENT,SUM(tmImpCarb*tmSalidas) AS IMPCARBSAL 
,SUM(CASE WHEN (tmTarifaIva>0 AND P.TipoZonaFront='F') THEN ((tmEntradas*tmIvaComb)*tmTarifaIva)/100 ELSE 0 END) AS SIVAZFENT
,SUM(CASE WHEN (tmTarifaIva>0 AND P.TipoZonaFront='F') THEN ((tmSalidas*tmIvaComb)*tmTarifaIva)/100 ELSE 0 END) AS SIVAZFSAL
,SUM(CASE WHEN Combo=0 AND tmEsProdBase=1 THEN tmIvaComb ELSE 0 END) AS BASEIVACOM
,SUM(CASE WHEN Combo=1 AND IvaDetCombo=1 THEN tmIngCombo ELSE 0 END) AS INGBASECOM
,SUM(CASE WHEN Combo=0 AND tmEsProdBase=1 THEN tmIngCombo ELSE 0 END) AS SBASEING
,SUM(tmSobtasaCons) AS SOBTCON
,SUM(tmBaseIvp*tmEntradas) AS BASEIVPENT,SUM(tmBaseIvp*tmSalidas) AS BASEIVAING,SUM(tmIvaIngProd) AS SIVAINGP,MAX(tmTarifaIvp) AS TARIVAING
 FROM tm_Kdex AS K INNER JOIN ProdMcias AS P ON K.tmIdProducto=P.IdProducto
 WHERE tmNumero=@pmtmNumero
 GROUP BY IdSubgrupo,tmIdProducto,DescripProd,TipoRef,Combo,tmEsProdBase,IdProv,ExcluidoImp,Electrocomb,IvaDetCombo
 ORDER BY IdSubgrupo,tmIdProducto
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_KdexDet] @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmItem,tmIdProducto,tmIdBodega,tmCdTanque,tmEntradas,tmSalidas,tmIdUnd,tmVrUnitario,tmVrPrecio,tmVrCostProm
,tmVrProm,tmTarifaIva,tmVrIva,tmTarifaDct,tmVrDcto,tmTarifaRet,tmVrRete,tmTarifaIca,tmVrIca,tmVrBruto,tmCdUbic,tmNumLote,tmFechLote
,tmFecVceLote,tmIdTercero,tmCdAgencia,tmCdCCosto,tmCdSubCos,tmCdLocal,tmCdSzona,tmpVehiculo,tmIdVend,tmComision
,tmCdOperario,tmComisnOper,tmReferencia,tmDescripcion,tmComptmntos,tmCdProdEquiv,tmCdMngra,tmNumInicial,tmNumFinal
,tmSobretasa,tmTasaNac,tmTasaDep,tmTasaMun,tmSoldicom,tmImpGlobal,tmOtroImpto,tmUnidades,tmServcios,tmNoVentas
,tmEsCombo,tmEsProdBase,tmItemCbo,tmCodTarDct,tmCodTarIva,tmCodTarIca,tmCodTarRet,tmCodTarCom,tmCodTarCmc
,tmCdSubgrupo,tmListaPrec,tmVrBase,tmCdMoneda,tmVrTasaCamb,tmTipDoc,tmDocumento,tmIdCia
,tmTipRem,tmRemision,tmIdCiaRem,tmReferencia2,tmFecOrden,tmgalsbruto,tmgalsneto,tmTemperatura,tmUmTemp
,tmDensidad,tmRec_Costo,tmMgenCont,tmCotizacion,tmCiaCotiza,tmVrImvCosto,tmTarifaIco,tmVrImpCon,tmCantObseq,tmIvaObseq
,tmIvaComb,tmImpCarb,tmIngCombo,tmTarifaStc,tmSobtasaCons,tmCodTarIco,tmBaseIvp,tmTarifaIvp,tmIvaIngProd
--variables de productos
,DescripProd,TipoRef,IdSubgrupo,Seriales,Lotes,Combo,Tanques
 FROM  tm_Kdex AS K INNER JOIN ProdMcias AS P ON K.tmIdProducto=P.IdProducto 
 WHERE  tmNumero=@pmtmNumero 
 ORDER BY tmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInstm_Kdex_SelDve] @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2),@pmtmNumero VARCHAR(5)
 AS 
 INSERT INTO tm_Kdex (tmNumero,tmItem,tmIdProducto,tmIdBodega,tmCdTanque,tmEntradas,tmSalidas,tmIdUnd,tmVrUnitario,tmVrPrecio,tmVrCostProm,tmVrProm,tmTarifaIva,tmVrIva,tmTarifaDct,tmVrDcto,tmTarifaRet,tmVrRete
,tmTarifaIca,tmVrIca,tmVrBruto,tmCdUbic,tmNumLote,tmFechLote,tmFecVceLote,tmIdTercero,tmCdAgencia,tmCdCCosto,tmCdSubCos,tmCdLocal,tmCdSzona,tmpVehiculo,tmIdVend
,tmComision,tmCdOperario,tmComisnOper,tmReferencia,tmDescripcion,tmComptmntos,tmCdProdEquiv,tmCdMngra,tmNumInicial,tmNumFinal,tmSobretasa,tmTasaNac,tmTasaDep,tmTasaMun,tmSoldicom,tmImpGlobal,tmOtroImpto
,tmUnidades,tmServcios,tmNoVentas,tmEsCombo,tmEsProdBase,tmItemCbo,tmCodTarDct,tmCodTarIva,tmCodTarIca,tmCodTarRet,tmCodTarCom,tmCodTarCmc,tmCdSubgrupo
,tmListaPrec,tmTipDoc,tmDocumento,tmIdCia,tmVrBase,tmCdMoneda,tmVrTasaCamb,tmTipRem,tmRemision,tmIdCiaRem,tmReferencia2,tmFecOrden,tmgalsbruto,tmgalsneto,tmTemperatura,tmUmTemp
,tmDensidad,tmRec_Costo,tmMgenCont,tmCotizacion,tmCiaCotiza,tmVrImvCosto,tmTarifaIco,tmVrImpCon,tmCantObseq,tmIvaObseq,tmIvaComb,tmImpCarb,tmIngCombo,tmTarifaStc,tmSobtasaCons,tmCodTarIco,tmBaseIvp,tmTarifaIvp,tmIvaIngProd)
SELECT @pmtmNumero,Item,K.IdProducto,K.IdBodega,CdTanque,CASE WHEN CantObseq>0 AND (TipDoc='DVE' OR TipDoc='DEI') THEN Salidas-CantObseq ELSE Salidas END
,CASE WHEN CantObseq>0 AND (TipDoc='ENT' OR TipDoc='COM') THEN Entradas-CantObseq ELSE Entradas END
,K.IdUnd,CASE WHEN NumInicial>0 THEN VrUnitario-NumInicial ELSE VrUnitario END,CASE WHEN NumInicial>0 THEN VrPrecio-NumInicial ELSE VrPrecio END
,VrCostProm,0,TarifaIva,CASE WHEN CantObseq>0 AND VrIvaObseq>0 THEN (VrIvaEnt+VrIvaSal)-VrIvaObseq ELSE VrIvaEnt+VrIvaSal END
,TarifaDct,VrDctoEnt+VrDctoSal,TarifaRet,VrReteEnt+VrReteSal
,TarifaIca,VrIcaEnt+VrIcaSal,VrBruto,CdUbic,NumLote,FechLote,FechLote,IdTercero,CdAgencia,CdCCosto,CdSubCos,CdLocal,CdSzona,pVehiculo,IdVend
,Comision,CdOperario,ComisnOper,K.Referencia,Descripcion,Comptmntos,CdProdEquiv,CdMngra,NumInicial,NumFinal,Sobretasa,TasaNac,TasaDep,TasaMun,Soldicom,ImpGlobal,OtroImpto
,Unidades,Servcios,NoVentas,EsCombo,EsProdBase,ItemCombo,CodTarDct,CodTarIva,CodTarIca,CodTarRet,CodTarCom,CodTarCmc,IdSubgrupo,ListaPrec,TipOrd,NumOrden,IdCiaOrd,VrBase,CdMoneda,VrTasaCamb
,'REM',Remision,IdCiaRem,Referencia2,FecOrden,galsbruto,galsneto,Temperatura,UmTemp,Densidad,Rec_Costo,MgenCont,Cotizacion,IdCiaCot,VrImvCosto,TarifaIco,VrImpCon,CantObseq,VrIvaObseq
,BaseIvaCom,ImpCarbono,IngBaseCom,TarifaStc,SobtasaCons,CodTarIco,BaseIvp,TarifaIvp,IvaIngProd
 FROM Trn_Kardex AS K INNER JOIN ProdMcias AS P ON K.IdProducto=P.IdProducto
 WHERE TipDoc=@pmTipDoc AND Documento=@pmDocumento AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryOpedidoLta] @pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME,@pmPedidoIni INT=Null,@pmPedidoFin INT=Null
,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdConcepto VARCHAR(4)=Null,@pmIdVend VARCHAR(16)=Null,@pmModalidad VARCHAR(10)=Null
,@pmVigencia VARCHAR(10)=Null,@pmIdEstado VARCHAR(4)=Null,@pmAnulado BIT=Null
 AS
 SELECT Pedido,IdCia,Fecha,FechaVence,P.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente,IdAgencia,IdClieFact,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros,VrOtrDcto
,VrCargos,VrSobretasa,VrImpGlobal,VrImpCarbono,VrIvaIngProd,VrNeto,Cantidad,BaseIvaIgp,IdVend,V.RazonSocial AS Vendedor,TarifaCom,CodTarCom,DirEnvio,IdLocEnv,LugarEnvio,DiasEntraga,NitContac,NomContac,TelContac,emlContac,CargoContac
,IdForma,DetallePago,MulPlazos,IdPlazo,CdMney,TipoTrans,NitEmpTrans,EmpTrans,pVehiculo,AsignarVeh,CdConductor,CdRuta,ListaPrec,RefPedido,TipFac,Factura,IdCiaFac,FechaFact,TipRem,Remision,IdCiaRem,FechaRem
,NumAutoriza,NumAutCupo,NumAutCheq,Modalidad,Vigencia,NumAprob,IdCiaApr,FecAprob,CdUsuAprob,DetalleAprob,TipoOrden,TipoModifica,NumCotizac,CdCiaCotizac,NumAutSicom,OrigenAdd,ZonaFrontera,Anulado,FecDev,P.Observacion AS Observ
,P.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,P.IdUsuario AS IdUsuari,Usuario
 FROM Trn_Opedido AS P INNER JOIN Terceros AS T ON P.IdCliente=T.IdTercero
 INNER JOIN Terceros AS V ON P.IdVend=V.IdTercero
 INNER JOIN  Conceptos AS CN ON P.IdConcepto=CN.IdConcepto
 INNER JOIN EstadoDoc AS ED ON P.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario 
 WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND Pedido BETWEEN ISNULL(@pmPedidoIni,0) AND ISNULL(@pmPedidoFin,2147483647)
 AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdCliente LIKE ISNULL(@pmIdCliente,'%') AND P.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND P.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND IdVend LIKE ISNULL(@pmIdVend,'%')
 AND Modalidad LIKE ISNULL(@pmModalidad,'%') AND Vigencia LIKE ISNULL(@pmVigencia,'%')
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 ORDER BY IdCia,Pedido
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryClaseTarImp]
 AS 
  SELECT IdClase,NomClase
  FROM ClaseTar
  WHERE Inactivo=0 AND IdClase IN ('DEP','GLO','ICA','IVA','MUN','NAC','RET','RIV','SOB','SOL','MGM','PRE','TSN','TSD','TSM','SUB','MAR','COS','IVI','IGP','MGC','MGN','CSE','INC','BIM','BIC','ICO','BIP','IVP')
  ORDER BY NomClase
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryOpedido] @pmTipDoc VARCHAR(3),@pmPedido INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Pedido,IdCia,Fecha,FechaVence,IdConcepto,IdCliente,IdAgencia,IdClieFact,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrSobretasa
,VrImpGlobal,VrIvaIngProd,VrNeto,Cantidad,BaseIvaIgp,IdVend,TarifaCom,CodTarCom,DirEnvio,IdLocEnv,LugarEnvio,DiasEntraga,NitContac,NomContac,TelContac,emlContac,CargoContac,IdForma
,DetallePago,MulPlazos,IdPlazo,CdMney,NitEmpTrans,EmpTrans,AsignarVeh,pVehiculo,CdConductor,CdRuta,ListaPrec,RefPedido,Modalidad,Vigencia,NumAutoriza,NumAutCupo
,NumAutCheq,NumAprob,IdCiaApr,FecAprob,DetalleAprob,CdUsuAprob,TipFac,Factura,IdCiaFac,FechaFact,TipRem,Remision,IdCiaRem,FechaRem,NumCotizac,CdCiaCotizac,OrigenAdd,Anulado
,FecDev,ZonaFrontera,TipoTrans,TipoOrden,TipoModifica,NumAutSicom,VrImpCarbono,Observacion,IdEstado,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_Opedido
 WHERE TipDoc=@pmTipDoc AND Pedido=@pmPedido AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsOpedido] @pmTipDoc VARCHAR(3),@pmPedido INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFechaVence SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdClieFact VARCHAR(16),@pmVrSubTotal MONEY,@pmVrDescuento MONEY,@pmVrImpuesto MONEY
,@pmVrFletes MONEY,@pmVrOtros MONEY,@pmVrCargos MONEY,@pmVrOtrDcto MONEY,@pmVrSobretasa MONEY,@pmVrImpGlobal MONEY,@pmVrNeto MONEY,@pmCantidad DECIMAL(14,4),@pmIdVend VARCHAR(16),@pmTarifaCom DECIMAL(14,4),@pmCodTarCom VARCHAR(4),@pmDirEnvio VARCHAR(250),@pmIdLocEnv VARCHAR(8),@pmLugarEnvio VARCHAR(50)
,@pmDiasEntraga INT,@pmNitContac VARCHAR(16),@pmNomContac VARCHAR(150),@pmTelContac VARCHAR(20),@pmemlContac VARCHAR(100),@pmCargoContac VARCHAR(50),@pmIdForma VARCHAR(4),@pmDetallePago VARCHAR(100),@pmMulPlazos BIT,@pmIdPlazo VARCHAR(4),@pmCdMney VARCHAR(5),@pmNitEmpTrans VARCHAR(16),@pmEmpTrans VARCHAR(150)
,@pmAsignarVeh BIT,@pmpVehiculo VARCHAR(10),@pmCdConductor VARCHAR(16),@pmCdRuta VARCHAR(4),@pmListaPrec CHAR(1),@pmRefPedido VARCHAR(50),@pmModalidad VARCHAR(10),@pmVigencia VARCHAR(10),@pmNumAutoriza INT,@pmNumAutCupo INT,@pmNumAutCheq INT,@pmNumAprob INT,@pmIdCiaApr CHAR(2),@pmFecAprob SMALLDATETIME
,@pmDetalleAprob VARCHAR(250),@pmCdUsuAprob VARCHAR(11),@pmTipFac VARCHAR(3),@pmFactura INT,@pmIdCiaFac CHAR(2),@pmFechaFact SMALLDATETIME,@pmTipRem VARCHAR(3),@pmRemision INT,@pmIdCiaRem CHAR(2),@pmFechaRem SMALLDATETIME,@pmAnulado BIT,@pmFecDev SMALLDATETIME
,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmZonaFrontera INT,@pmTipoTrans INT,@pmTipoOrden VARCHAR(3),@pmTipoModifica VARCHAR(10),@pmNumCotizac INT,@pmCdCiaCotizac CHAR(2),@pmNumAutSicom VARCHAR(50),@pmVrImpCarbono MONEY,@pmBaseIvaIgp MONEY,@pmVrIvaIngProd MONEY,@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_Opedido (TipDoc,Pedido,IdCia,Fecha,FechaVence,IdConcepto,IdCliente,IdAgencia,IdClieFact,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrSobretasa,VrImpGlobal,VrNeto,Cantidad,IdVend,TarifaCom,CodTarCom,DirEnvio,IdLocEnv,LugarEnvio,DiasEntraga,NitContac,NomContac,TelContac,emlContac,CargoContac,IdForma,DetallePago
,MulPlazos,IdPlazo,CdMney,NitEmpTrans,EmpTrans,AsignarVeh,pVehiculo,CdConductor,CdRuta,ListaPrec,RefPedido,Modalidad,Vigencia,NumAutoriza,NumAutCupo,NumAutCheq,NumAprob,IdCiaApr,FecAprob,DetalleAprob,CdUsuAprob,TipFac,Factura,IdCiaFac,FechaFact,TipRem,Remision,IdCiaRem,FechaRem,NumCotizac,CdCiaCotizac,OrigenAdd,ZonaFrontera,TipoTrans,TipoOrden,TipoModifica,Anulado,FecDev,Observacion,IdEstado
,TimeSys,IdCiaCrea,IdUsuario,NumAutSicom,VrImpCarbono,BaseIvaIgp,VrIvaIngProd)
 VALUES (@pmTipDoc,@pmPedido,@pmIdCia,@pmFecha,@pmFechaVence,@pmIdConcepto,@pmIdCliente,@pmIdAgencia,@pmIdClieFact,@pmVrSubTotal,@pmVrDescuento,@pmVrImpuesto,@pmVrFletes,@pmVrOtros,@pmVrCargos,@pmVrOtrDcto,@pmVrSobretasa,@pmVrImpGlobal,@pmVrNeto,@pmCantidad,@pmIdVend,@pmTarifaCom,@pmCodTarCom,@pmDirEnvio,@pmIdLocEnv
,@pmLugarEnvio,@pmDiasEntraga,@pmNitContac,@pmNomContac,@pmTelContac,@pmemlContac,@pmCargoContac,@pmIdForma,@pmDetallePago,@pmMulPlazos,@pmIdPlazo,@pmCdMney,@pmNitEmpTrans,@pmEmpTrans,@pmAsignarVeh,@pmpVehiculo,@pmCdConductor,@pmCdRuta,@pmListaPrec,@pmRefPedido,@pmModalidad,@pmVigencia,@pmNumAutoriza,@pmNumAutCupo
,@pmNumAutCheq,@pmNumAprob,@pmIdCiaApr,@pmFecAprob,@pmDetalleAprob,@pmCdUsuAprob,@pmTipFac,@pmFactura,@pmIdCiaFac,@pmFechaFact,@pmTipRem,@pmRemision,@pmIdCiaRem,@pmFechaRem,@pmNumCotizac,@pmCdCiaCotizac,@pmOrigenAdd,@pmZonaFrontera,@pmTipoTrans,@pmTipoOrden,@pmTipoModifica,@pmAnulado,@pmFecDev,@pmObservacion,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmNumAutSicom,@pmVrImpCarbono,@pmBaseIvaIgp,@pmVrIvaIngProd)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpOpedido] @pmTipDoc VARCHAR(3),@pmPedido INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFechaVence SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdClieFact VARCHAR(16),@pmVrSubTotal MONEY,@pmVrDescuento MONEY,@pmVrImpuesto MONEY,@pmVrFletes MONEY,@pmVrOtros MONEY,@pmVrCargos MONEY
,@pmVrOtrDcto MONEY,@pmVrSobretasa MONEY,@pmVrImpGlobal MONEY,@pmVrNeto MONEY,@pmCantidad DECIMAL(14,4),@pmIdVend VARCHAR(16),@pmTarifaCom DECIMAL(14,4),@pmCodTarCom VARCHAR(4),@pmDirEnvio VARCHAR(250),@pmIdLocEnv VARCHAR(8),@pmLugarEnvio VARCHAR(50),@pmDiasEntraga INT,@pmNitContac VARCHAR(16),@pmNomContac VARCHAR(150),@pmTelContac VARCHAR(20)
,@pmemlContac VARCHAR(100),@pmCargoContac VARCHAR(50),@pmIdForma VARCHAR(4),@pmDetallePago VARCHAR(100),@pmMulPlazos BIT,@pmIdPlazo VARCHAR(4),@pmCdMney VARCHAR(5),@pmNitEmpTrans VARCHAR(16),@pmEmpTrans VARCHAR(150),@pmAsignarVeh BIT,@pmpVehiculo VARCHAR(10),@pmCdConductor VARCHAR(16),@pmCdRuta VARCHAR(4),@pmListaPrec CHAR(1),@pmRefPedido VARCHAR(50)
,@pmModalidad VARCHAR(10),@pmVigencia VARCHAR(10),@pmNumAutoriza INT,@pmNumAutCupo INT,@pmNumAutCheq INT,@pmNumAprob INT,@pmIdCiaApr CHAR(2),@pmFecAprob SMALLDATETIME,@pmDetalleAprob VARCHAR(250),@pmCdUsuAprob VARCHAR(11),@pmTipFac VARCHAR(3),@pmFactura INT,@pmIdCiaFac CHAR(2),@pmFechaFact SMALLDATETIME,@pmTipRem VARCHAR(3),@pmRemision INT,@pmIdCiaRem CHAR(2)
,@pmFechaRem SMALLDATETIME,@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmZonaFrontera INT,@pmTipoTrans INT,@pmTipoOrden VARCHAR(3),@pmTipoModifica VARCHAR(10),@pmNumCotizac INT,@pmCdCiaCotizac CHAR(2),@pmNumAutSicom VARCHAR(50),@pmVrImpCarbono MONEY,@pmBaseIvaIgp MONEY,@pmVrIvaIngProd MONEY,@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_Opedido SET Fecha=@pmFecha,FechaVence=@pmFechaVence,IdConcepto=@pmIdConcepto,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,IdClieFact=@pmIdClieFact,VrSubTotal=@pmVrSubTotal,VrDescuento=@pmVrDescuento,VrImpuesto=@pmVrImpuesto,VrFletes=@pmVrFletes,VrOtros=@pmVrOtros,VrCargos=@pmVrCargos,VrOtrDcto=@pmVrOtrDcto,VrSobretasa=@pmVrSobretasa,VrImpGlobal=@pmVrImpGlobal,VrNeto=@pmVrNeto
,Cantidad=@pmCantidad,IdVend=@pmIdVend,TarifaCom=@pmTarifaCom,CodTarCom=@pmCodTarCom,DirEnvio=@pmDirEnvio,IdLocEnv=@pmIdLocEnv,LugarEnvio=@pmLugarEnvio,DiasEntraga=@pmDiasEntraga,NitContac=@pmNitContac,NomContac=@pmNomContac,TelContac=@pmTelContac,emlContac=@pmemlContac,CargoContac=@pmCargoContac,IdForma=@pmIdForma,DetallePago=@pmDetallePago,MulPlazos=@pmMulPlazos,IdPlazo=@pmIdPlazo
,CdMney=@pmCdMney,NitEmpTrans=@pmNitEmpTrans,EmpTrans=@pmEmpTrans,AsignarVeh=@pmAsignarVeh,pVehiculo=@pmpVehiculo,CdConductor=@pmCdConductor,CdRuta=@pmCdRuta,ListaPrec=@pmListaPrec,RefPedido=@pmRefPedido,Modalidad=@pmModalidad,Vigencia=@pmVigencia,NumAutoriza=@pmNumAutoriza,NumAutCupo=@pmNumAutCupo,NumAutCheq=@pmNumAutCheq,NumAprob=@pmNumAprob,IdCiaApr=@pmIdCiaApr
,FecAprob=@pmFecAprob,DetalleAprob=@pmDetalleAprob,CdUsuAprob=@pmCdUsuAprob,TipFac=@pmTipFac,Factura=@pmFactura,IdCiaFac=@pmIdCiaFac,FechaFact=@pmFechaFact,TipRem=@pmTipRem,Remision=@pmRemision,IdCiaRem=@pmIdCiaRem,FechaRem=@pmFechaRem,NumCotizac=@pmNumCotizac,CdCiaCotizac=@pmCdCiaCotizac,Anulado=@pmAnulado,FecDev=@pmFecDev,Observacion=@pmObservacion,TipoModifica=@pmTipoModifica,IdEstado=@pmIdEstado
,ZonaFrontera=@pmZonaFrontera,TipoTrans=@pmTipoTrans,TipoOrden=@pmTipoOrden, FecUpdate=@pmFecUpdate,NumAutSicom=@pmNumAutSicom,VrImpCarbono=@pmVrImpCarbono,BaseIvaIgp=@pmBaseIvaIgp,VrIvaIngProd=@pmVrIvaIngProd
 WHERE TipDoc=@pmTipDoc AND Pedido=@pmPedido AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryKardexSubOpe] @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2)
 AS 
 SELECT IdSubgrupo,K.IdProducto AS tmIdProducto,DescripProd,TipoRef,Combo,EsProdBase AS tmEsProdBase,IdProv,ExcluidoImp,Electrocomb,IvaDetCombo,CdOperario AS tmCdOperario
,COUNT(Item) AS SCANT,SUM(Entradas) AS SENT,SUM(Salidas) AS SSAL
,SUM(CASE TipDoc WHEN 'ENT' THEN VrCostoEnt-(NumInicial*Entradas) WHEN 'COM' THEN VrCostoEnt-(NumInicial*Entradas)
 ELSE VrCostoEnt END) AS SCOSENT
,SUM(CASE TipDoc WHEN 'DVE' THEN VrCostoSal-(NumInicial*Salidas) WHEN 'DEI' THEN VrCostoSal-(NumInicial*Salidas)
 ELSE VrCostoSal END) AS SCOSSAL
,SUM(VrPrecio*Entradas) AS SVALENT,SUM(VrPrecio*Salidas) AS SVALSAL
,SUM(VrBruto*Entradas) AS SBRUENT,SUM(VrBruto*Salidas) AS SBRUSAL
,SUM(Entradas*ImpGlobal) AS SGLOENT,SUM(Salidas*ImpGlobal) AS SGLOSAL
,SUM(Sobretasa*Entradas) AS SSOBENT,SUM(Sobretasa*Salidas) AS SSOBSAL
,SUM(TasaNac*Entradas) AS SNACENT,SUM(TasaNac*Salidas) AS SNACSAL
,SUM(TasaDep*Entradas) AS SDEPENT,SUM(TasaDep*Salidas) AS SDEPSAL
,SUM(TasaMun*Entradas) AS SMUNENT,SUM(TasaMun*Salidas) AS SMUNSAL
,SUM(Soldicom*Entradas) AS SSOLENT,SUM(Soldicom*Salidas) AS SSOLSAL
,SUM(CASE WHEN  Salidas>0 THEN VrIvaSal ELSE VrIvaEnt END) AS SIVA
,SUM(CASE WHEN  Salidas>0 THEN VrDctoSal ELSE VrDctoEnt END) AS SDCT
,SUM(CASE WHEN  Salidas>0 THEN VrReteSal ELSE VrReteEnt END) AS SRET
,SUM(CASE WHEN  Salidas>0 THEN VrIcaSal ELSE VrIcaEnt END) AS SICA
,SUM(OtroImpto*Entradas) AS SOTRENT,SUM(OtroImpto*Salidas) AS SOTRSAL
,SUM(Rec_Costo*Entradas) AS SRCOSENT,SUM(Rec_Costo*Salidas) AS SRCOSSAL
,SUM(MgenCont*Entradas) AS SMGENENT,SUM(MgenCont*Salidas) AS SMGENSAL,SUM(VrImpCon) AS SVICO
,SUM(CASE WHEN CantObseq>0 THEN VrIvaObseq ELSE 0 END) AS SIVAOBSQ
,SUM(CantObseq*VrUnitario) AS SBASCOSOBSQ,SUM(CantObseq*VrPrecio) AS SBASEOBSQ
,SUM(BaseIvaCom*Entradas) AS BASEIVAENT,SUM(BaseIvaCom*Salidas) AS BASEIVASAL 
,SUM(ImpCarbono*Entradas) AS IMPCARBENT,SUM(ImpCarbono*Salidas) AS IMPCARBSAL 
,SUM(CASE WHEN (TarifaIva>0 AND P.TipoZonaFront='F') THEN ((Entradas*BaseIvaCom)*TarifaIva)/100 ELSE 0 END) AS SIVAZFENT
,SUM(CASE WHEN (TarifaIva>0 AND P.TipoZonaFront='F') THEN ((Salidas*BaseIvaCom)*TarifaIva)/100 ELSE 0 END) AS SIVAZFSAL
--Para los ajustes con cantidad en cero (0)
,SUM(VrUnitario) AS SCOSAJU,SUM(OtroImpto) AS SDVEAJU,SUM(VrImvCosto) AS SIMVCOS
,SUM(CASE WHEN Combo=0 AND EsProdBase=1 THEN BaseIvaCom ELSE 0 END) AS BASEIVACOM
,SUM(CASE WHEN Combo=1 AND IvaDetCombo=1 THEN IngBaseCom ELSE 0 END) AS INGBASECOM
,SUM(CASE WHEN Combo=0 AND EsProdBase=1 THEN IngBaseCom ELSE 0 END) AS SBASEING
,SUM(SobtasaCons) AS SOBTCON
,SUM(BaseIvp*Entradas) AS BASEIVPENT,SUM(BaseIvp*Salidas) AS BASEIVAING,SUM(IvaIngProd) AS SIVAINGP,MAX(TarifaIvp) AS TARIVAING
 FROM Trn_Kardex AS K INNER JOIN ProdMcias AS P ON K.IdProducto=P.IdProducto
 WHERE TipDoc=@pmTipDoc AND Documento=@pmDocumento AND IdCia=@pmIdCia
 GROUP BY  IdSubgrupo,K.IdProducto,DescripProd,TipoRef,Combo,EsProdBase,IdProv,ExcluidoImp,Electrocomb,IvaDetCombo,CdOperario
 ORDER BY IdSubgrupo,K.IdProducto
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryKardexSub] @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2)
 AS 
 SELECT IdSubgrupo,K.IdProducto AS tmIdProducto,DescripProd,TipoRef,Combo,EsProdBase AS tmEsProdBase
,IdProv,ExcluidoImp,Electrocomb,IvaDetCombo,COUNT(Item) AS SCANT,SUM(Entradas) AS SENT,SUM(Salidas) AS SSAL
,SUM(CASE TipDoc WHEN 'ENT' THEN VrCostoEnt-(NumInicial*Entradas) WHEN 'COM' THEN VrCostoEnt-(NumInicial*Entradas)
 ELSE VrCostoEnt END) AS SCOSENT
,SUM(CASE TipDoc WHEN 'DVE' THEN VrCostoSal-(NumInicial*Salidas) WHEN 'DEI' THEN VrCostoSal-(NumInicial*Salidas)
 ELSE VrCostoSal END) AS SCOSSAL
,SUM(VrPrecio*Entradas) AS SVALENT,SUM(VrPrecio*Salidas) AS SVALSAL
,SUM(VrBruto*Entradas) AS SBRUENT,SUM(VrBruto*Salidas) AS SBRUSAL
,SUM(Entradas*ImpGlobal) AS SGLOENT,SUM(Salidas*ImpGlobal) AS SGLOSAL
,SUM(Sobretasa*Entradas) AS SSOBENT,SUM(Sobretasa*Salidas) AS SSOBSAL
,SUM(TasaNac*Entradas) AS SNACENT,SUM(TasaNac*Salidas) AS SNACSAL
,SUM(TasaDep*Entradas) AS SDEPENT,SUM(TasaDep*Salidas) AS SDEPSAL
,SUM(TasaMun*Entradas) AS SMUNENT,SUM(TasaMun*Salidas) AS SMUNSAL
,SUM(Soldicom*Entradas) AS SSOLENT,SUM(Soldicom*Salidas) AS SSOLSAL
,SUM(CASE WHEN  Salidas>0 THEN VrIvaSal ELSE VrIvaEnt END) AS SIVA
,SUM(CASE WHEN  Salidas>0 THEN VrDctoSal ELSE VrDctoEnt END) AS SDCT
,SUM(CASE WHEN  Salidas>0 THEN VrReteSal ELSE VrReteEnt END) AS SRET
,SUM(CASE WHEN  Salidas>0 THEN VrIcaSal ELSE VrIcaEnt END) AS SICA
,SUM(OtroImpto*Entradas) AS SOTRENT,SUM(OtroImpto*Salidas) AS SOTRSAL
,SUM(Rec_Costo*Entradas) AS SRCOSENT,SUM(Rec_Costo*Salidas) AS SRCOSSAL
,SUM(MgenCont*Entradas) AS SMGENENT,SUM(MgenCont*Salidas) AS SMGENSAL,SUM(VrImpCon) AS SVICO
,SUM(CASE WHEN CantObseq>0 THEN VrIvaObseq ELSE 0 END) AS SIVAOBSQ
,SUM(CantObseq*VrUnitario) AS SBASCOSOBSQ,SUM(CantObseq*VrPrecio) AS SBASEOBSQ
,SUM(BaseIvaCom*Entradas) AS BASEIVAENT,SUM(BaseIvaCom*Salidas) AS BASEIVASAL 
,SUM(ImpCarbono*Entradas) AS IMPCARBENT,SUM(ImpCarbono*Salidas) AS IMPCARBSAL 
,SUM(CASE WHEN (TarifaIva>0 AND P.TipoZonaFront='F') THEN ((Entradas*BaseIvaCom)*TarifaIva)/100 ELSE 0 END) AS SIVAZFENT
,SUM(CASE WHEN (TarifaIva>0 AND P.TipoZonaFront='F') THEN ((Salidas*BaseIvaCom)*TarifaIva)/100 ELSE 0 END) AS SIVAZFSAL
--Para los ajustes con cantidad en cero (0)
,SUM(VrUnitario) AS SCOSAJU,SUM(OtroImpto) AS SDVEAJU,SUM(VrImvCosto) AS SIMVCOS
,SUM(CASE WHEN Combo=0 AND EsProdBase=1 THEN BaseIvaCom ELSE 0 END) AS BASEIVACOM
,SUM(CASE WHEN Combo=1 AND IvaDetCombo=1 THEN IngBaseCom ELSE 0 END) AS INGBASECOM
,SUM(CASE WHEN Combo=0 AND EsProdBase=1 THEN IngBaseCom ELSE 0 END) AS SBASEING
,SUM(SobtasaCons) AS SOBTCON
,SUM(BaseIvp*Entradas) AS BASEIVPENT,SUM(BaseIvp*Salidas) AS BASEIVAING,SUM(IvaIngProd) AS SIVAINGP,MAX(TarifaIvp) AS TARIVAING
 FROM Trn_Kardex AS K INNER JOIN ProdMcias AS P ON K.IdProducto=P.IdProducto
 WHERE TipDoc=@pmTipDoc AND Documento=@pmDocumento AND IdCia=@pmIdCia
 GROUP BY  IdSubgrupo,K.IdProducto,DescripProd,TipoRef,Combo,EsProdBase,IdProv,ExcluidoImp,Electrocomb,IvaDetCombo
 ORDER BY IdSubgrupo,K.IdProducto
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsKardex] @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2),@pmItem INT,@pmFecha SMALLDATETIME,@pmIdProducto VARCHAR(16),@pmIdBodega VARCHAR(4),@pmCdTanque VARCHAR(4),@pmEntradas DECIMAL(14,4),@pmSalidas DECIMAL(14,4),@pmIdUnd VARCHAR(4),@pmVrUnitario MONEY,@pmVrPrecio MONEY,@pmVrCostProm MONEY,@pmTarifaIva DECIMAL(14,4),@pmVrIvaEnt MONEY,@pmVrIvaSal MONEY,@pmTarifaDct DECIMAL(14,4),@pmVrDctoEnt MONEY,@pmVrDctoSal MONEY,@pmVrCostoEnt MONEY
,@pmVrCostoSal MONEY,@pmTarifaRet DECIMAL(14,4),@pmVrReteEnt MONEY,@pmVrReteSal MONEY,@pmTarifaIca DECIMAL(14,4),@pmVrIcaEnt MONEY,@pmVrIcaSal MONEY,@pmVrBruto MONEY,@pmCdUbic VARCHAR(10),@pmNumLote VARCHAR(30),@pmFechLote SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdTercero VARCHAR(16),@pmCdAgencia VARCHAR(16),@pmCdCCosto VARCHAR(16),@pmCdSubCos VARCHAR(16),@pmCdLocal VARCHAR(8),@pmCdSzona VARCHAR(4),@pmpVehiculo VARCHAR(10),@pmIdVend VARCHAR(16),@pmComision DECIMAL(14,4)
,@pmCdOperario VARCHAR(16),@pmComisnOper DECIMAL(14,4),@pmReferencia VARCHAR(50),@pmDescripcion VARCHAR(250),@pmComptmntos VARCHAR(50),@pmCdProdEquiv VARCHAR(16),@pmTipOrd VARCHAR(3),@pmNumOrden INT,@pmIdCiaOrd CHAR(2),@pmCotizacion INT,@pmIdCiaCot CHAR(2),@pmRemision INT,@pmIdCiaRem CHAR(2),@pmFactura VARCHAR(15),@pmTipDocDev VARCHAR(3),@pmNumDocDev INT,@pmCdMngra VARCHAR(10),@pmNumInicial DECIMAL(14,4),@pmNumFinal DECIMAL(14,4),@pmSobretasa DECIMAL(14,4),@pmTasaNac DECIMAL(14,4),@pmTasaDep DECIMAL(14,4)
,@pmTasaMun DECIMAL(14,4),@pmSoldicom DECIMAL(14,4),@pmImpGlobal DECIMAL(14,4),@pmOtroImpto DECIMAL(14,4),@pmUnidades DECIMAL(14,4),@pmItemCombo INT,@pmServcios BIT,@pmNoVentas INT,@pmEsCombo BIT,@pmEsProdBase INT,@pmCodTarDct VARCHAR(4),@pmCodTarIva VARCHAR(4),@pmCodTarIca VARCHAR(4),@pmCodTarRet VARCHAR(4),@pmCodTarCom VARCHAR(4),@pmCodTarCmc VARCHAR(4),@pmListaPrec CHAR(1),@pmVrBase MONEY,@pmCdMoneda VARCHAR(5),@pmVrTasaCamb MONEY,@pmVrDivisa1 MONEY,@pmVrDivisa2 MONEY
,@pmVrDivisa3 MONEY,@pmReferencia2 VARCHAR(50),@pmFecOrden SMALLDATETIME,@pmgalsbruto DECIMAL(14,4),@pmgalsneto DECIMAL(14,4), @pmTemperatura DECIMAL(14,4),@pmUmTemp VARCHAR(3),@pmDensidad DECIMAL(14,4),@pmTimeSys SMALLDATETIME,@pmIdUsuario VARCHAR(11),@pmRec_Costo DECIMAL(14,4),@pmMgenCont  DECIMAL(14,4),@pmVrImvCosto DECIMAL(16,4),@pmTarifaIco DECIMAL(14,4),@pmVrImpCon DECIMAL(16,4),@pmCantObseq DECIMAL(14,4),@pmVrIvaObseq MONEY,@pmBaseIvaCom DECIMAL(16,4),@pmImpCarbono DECIMAL(14,4),@pmIngBaseCom DECIMAL(16,4)
,@pmTarifaStc DECIMAL(14,4),@pmSobtasaCons DECIMAL(16,4),@pmCodTarIco VARCHAR(4),@pmBaseIvp MONEY,@pmTarifaIvp DECIMAL(14,4),@pmIvaIngProd MONEY 
 AS 
 INSERT INTO Trn_Kardex (TipDoc,Documento,IdCia,Item,Fecha,IdProducto,IdBodega,CdTanque,Entradas,Salidas,IdUnd,VrUnitario,VrPrecio,VrCostProm,TarifaIva,VrIvaEnt,VrIvaSal,TarifaDct,VrDctoEnt,VrDctoSal,VrCostoEnt,VrCostoSal,TarifaRet,VrReteEnt,VrReteSal,TarifaIca,VrIcaEnt,VrIcaSal,VrBruto,CdUbic,NumLote,FechLote,IdConcepto,IdTercero,CdAgencia,CdCCosto,CdSubCos,CdLocal,CdSzona,pVehiculo,IdVend,Comision,CdOperario,ComisnOper,Referencia,Descripcion,Comptmntos,CdProdEquiv,TipOrd,NumOrden,IdCiaOrd,Cotizacion,IdCiaCot,Remision,IdCiaRem,Factura,TipDocDev,NumDocDev,CdMngra,NumInicial
,NumFinal,Sobretasa,TasaNac,TasaDep,TasaMun,Soldicom,ImpGlobal,OtroImpto,Unidades,ItemCombo,Servcios,NoVentas,EsCombo,EsProdBase,CodTarDct,CodTarIva,CodTarIca,CodTarRet,CodTarCom,CodTarCmc,ListaPrec,VrBase,CdMoneda,VrTasaCamb,VrDivisa1,VrDivisa2,VrDivisa3,Referencia2,FecOrden,galsbruto,galsneto,Temperatura,UmTemp,Densidad,TimeSys,IdUsuario,Rec_Costo,MgenCont,VrImvCosto,TarifaIco,VrImpCon,CantObseq,VrIvaObseq,BaseIvaCom,ImpCarbono,IngBaseCom,TarifaStc,SobtasaCons,CodTarIco,BaseIvp,TarifaIvp,IvaIngProd)
 VALUES (@pmTipDoc,@pmDocumento,@pmIdCia,@pmItem,@pmFecha,@pmIdProducto,@pmIdBodega,@pmCdTanque,@pmEntradas,@pmSalidas,@pmIdUnd,@pmVrUnitario,@pmVrPrecio,@pmVrCostProm,@pmTarifaIva,@pmVrIvaEnt,@pmVrIvaSal,@pmTarifaDct,@pmVrDctoEnt,@pmVrDctoSal,@pmVrCostoEnt,@pmVrCostoSal,@pmTarifaRet,@pmVrReteEnt,@pmVrReteSal,@pmTarifaIca,@pmVrIcaEnt,@pmVrIcaSal,@pmVrBruto,@pmCdUbic,@pmNumLote,@pmFechLote,@pmIdConcepto,@pmIdTercero,@pmCdAgencia,@pmCdCCosto,@pmCdSubCos,@pmCdLocal,@pmCdSzona,@pmpVehiculo,@pmIdVend
,@pmComision,@pmCdOperario,@pmComisnOper,@pmReferencia,@pmDescripcion,@pmComptmntos,@pmCdProdEquiv,@pmTipOrd,@pmNumOrden,@pmIdCiaOrd,@pmCotizacion,@pmIdCiaCot,@pmRemision,@pmIdCiaRem,@pmFactura,@pmTipDocDev,@pmNumDocDev,@pmCdMngra,@pmNumInicial,@pmNumFinal,@pmSobretasa,@pmTasaNac,@pmTasaDep,@pmTasaMun,@pmSoldicom,@pmImpGlobal,@pmOtroImpto,@pmUnidades,@pmItemCombo,@pmServcios,@pmNoVentas,@pmEsCombo,@pmEsProdBase,@pmCodTarDct,@pmCodTarIva,@pmCodTarIca,@pmCodTarRet,@pmCodTarCom,@pmCodTarCmc
,@pmListaPrec,@pmVrBase,@pmCdMoneda,@pmVrTasaCamb,@pmVrDivisa1,@pmVrDivisa2,@pmVrDivisa3,@pmReferencia2,@pmFecOrden,@pmgalsbruto,@pmgalsneto,@pmTemperatura,@pmUmTemp,@pmDensidad,@pmTimeSys,@pmIdUsuario,@pmRec_Costo,@pmMgenCont,@pmVrImvCosto,@pmTarifaIco,@pmVrImpCon,@pmCantObseq,@pmVrIvaObseq,@pmBaseIvaCom,@pmImpCarbono,@pmIngBaseCom,@pmTarifaStc,@pmSobtasaCons,@pmCodTarIco,@pmBaseIvp,@pmTarifaIvp,@pmIvaIngProd)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryKardex] @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2),@pmItem INT
 AS 
 SELECT TipDoc,Documento,IdCia,Item,Fecha,IdProducto,IdBodega,CdTanque,Entradas,Salidas,IdUnd,VrUnitario,VrPrecio,VrCostProm,TarifaIva,VrIvaEnt,VrIvaSal,TarifaDct,VrDctoEnt,VrDctoSal
,VrCostoEnt,VrCostoSal,TarifaRet,VrReteEnt,VrReteSal,TarifaIca,VrIcaEnt,VrIcaSal,VrBruto,CdUbic,NumLote,FechLote,IdConcepto,IdTercero,CdAgencia,CdCCosto,CdSubCos,CdLocal,CdSzona
,pVehiculo,IdVend,Comision,CdOperario,ComisnOper,Referencia,Descripcion,Comptmntos,CdProdEquiv,TipOrd,NumOrden,IdCiaOrd,Cotizacion,IdCiaCot,Remision,IdCiaRem,Factura,TipDocDev
,NumDocDev,CdMngra,NumInicial,NumFinal,Sobretasa,TasaNac,TasaDep,TasaMun,Soldicom,ImpGlobal,OtroImpto,Unidades,ItemCombo,Servcios,NoVentas,EsCombo,EsProdBase,CodTarDct
,CodTarIva,CodTarIca,CodTarRet,CodTarCom,CodTarCmc,ListaPrec,VrBase,CdMoneda,VrTasaCamb,VrDivisa1,VrDivisa2,VrDivisa3,Referencia2,FecOrden,galsbruto,galsneto,Temperatura
,UmTemp,Densidad,TimeSys,IdUsuario,Rec_Costo,MgenCont,VrImvCosto,TarifaIco,VrImpCon,CantObseq,VrIvaObseq,BaseIvaCom,ImpCarbono,IngBaseCom,TarifaStc,SobtasaCons,CodTarIco,BaseIvp,TarifaIvp,IvaIngProd
 FROM Trn_Kardex
 WHERE TipDoc=@pmTipDoc AND Documento=@pmDocumento AND IdCia=@pmIdCia
 AND (Item>=ISNULL(@pmItem,0)  AND Item<=ISNULL(@pmItem,2147483647))
 ORDER BY Item
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryFacturasNetDet] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdAgencia VARCHAR(16)=Null,@pmIdVend VARCHAR(16)=Null,@pmIdLocEnv VARCHAR(8)=Null
,@pmModalidad VARCHAR(10)=Null,@pmIdProducto VARCHAR(16)=Null,@pmIdLinea VARCHAR(8)=Null,@pmIdGrupo VARCHAR(8)=Null,@pmIdSubgrupo VARCHAR(8)=Null
,@pmTipoRef VARCHAR(10)=Null
 AS 
 SELECT F.TipDoc AS TipoFact,F.Factura AS NumFactura,F.IdCia AS CdCia,Compania,F.Fecha AS FechaDoc,F.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente
,F.IdAgencia AS IdAgenc,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia,FechaVence,VrRetencion,VrReteICA,VrReteIVA,VrFletes,VrOtros,VrCargos,VrOtrDcto
,VrNeto,CantPuntos,PuntosAcum,F.IdLocEnv AS IdCiuEnvio,LE.Localidad AS CiudadEnvio,LE.IdDep AS CdDepEnvio,DE.Departamento AS DptoEnvio
,MulPlazos,F.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago,NitEmpTrans,EmpTrans,F.pVehiculo AS PlacaVeh,CdConductor,TC.RazonSocial AS Conductor,F.CdRuta,Ruta
,TipPed,Pedido,IdCiaPed,TipRem,F.Remision AS NumRemision,F.IdCiaRem AS CdCiaRem,TipCot,F.Cotizacion AS NumCotizacion,F.IdCiaCot AS CdCiaCotiza,FecPedido
,Modalidad,KmtVehic,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,F.Observacion AS Observ,F.IdEstado AS CdEstado,Estado,F.TimeSys AS Fecha_Add,IdCiaCrea,F.IdUsuario AS CdUsuario,Usuario
--detalles
,K.IdProducto AS CdProducto,DescripProd,Item,K.IdBodega AS CdBodega,Bodega,CdTanque,Entradas,Salidas,K.IdUnd AS CdUnid,UM.Unidad AS UnidMed,VrUnitario
,VrCostoEnt,VrCostoSal,VrCostProm,VrPrecio,VrPrecio*(Entradas+Salidas) AS VrTotal,TarifaDct,VrDctoEnt,VrDctoSal,K.TarifaIva AS TarifIva,VrIvaEnt,VrIvaSal,K.TarifaRet AS TarifRet,VrReteEnt,VrReteSal
,K.TarifaIca AS TarifIca,VrIcaEnt,VrIcaSal,Sobretasa,TasaNac,TasaDep,TasaMun,Soldicom,ImpGlobal,Unidades,K.Descripcion AS KarDescripcion,K.Referencia AS Referncia,Referencia2,FecOrden
,CdAgencia,KA.Agencia AS KarAgencia,KA.CodAgencia AS KarCodAgencia,K.IdVend AS NitVend,VN.RazonSocial AS Vendedor,Comision,CdOperario,OP.RazonSocial AS NomOperario,ComisnOper
,K.pVehiculo AS KarPlacaVeh,CdLocal,LK.Localidad AS KarCiudad,CdCCosto,CCosto,K.CdSubCos AS CodSubCos,SubCosto,TipOrd,NumOrden,IdCiaOrd,K.Remision AS KarNumRemision,K.IdCiaRem AS KarCdCiaRem,K.Cotizacion AS KarNumCotizacion
,K.IdCiaCot AS KarCiaCotiza,ListaPrec,VrBruto,VrBase,BaseIvp,TarifaIvp,IvaIngProd,Servcios,NoVentas,EsCombo,EsProdBase,ItemCombo
,PM.IdSubgrupo AS CdSubgrupo,Subgrupo,S.IdGrupo AS CdGrupo,Grupo,G.IdLinea AS CdLinea,Linea,PM.IdMarca AS CdMarca,Marca
,DescripLong,DescripAbrv,ExtciaMin,ExtciaMax,ExtciaAct,Factor1,Factor2,Factor3,Factor4,Factor5
--Información 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 NomCiudad,L.IdDep AS CdDep,D.Departamento AS TercDpto,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
--información del cliente
,CLI.IdSzona AS CdSubzona,SZ.Subzona,SZ.IdZona AS CdZona,Z.Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie,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,A.TipoZona AS TipoZona,A.IdSzona AS AgeCdSubzona,SZA.Subzona AS AgeSubzona,SZA.IdZona AS AgeCdZona,ZA.Zona AS AgeZona
 FROM Trn_Facturas AS F  INNER JOIN Companias AS CN ON F.IdCia=CN.IdCia 
 INNER JOIN Terceros AS T ON F.IdCliente=T.IdTercero 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 Plazos AS PZ ON F.IdPlazo=PZ.IdPlazo  INNER JOIN Agencias  AS A ON F.IdAgencia=A.IdAgencia 
 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 L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep 
 INNER JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal  INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 INNER JOIN RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 INNER JOIN Trn_Kardex AS K ON F.TipDoc=K.TipDoc AND F.Factura=K.Documento AND F.IdCia=K.IdCia
 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 Terceros AS VN ON K.IdVend=VN.IdTercero LEFT JOIN Agencias  AS KA ON K.CdAgencia=KA.IdAgencia
 LEFT JOIN CentroCosto AS CC ON K.CdCCosto=CC.IdCCosto LEFT JOIN SubCentros AS SC ON K.CdSubCos=SC.IdSubCos
 LEFT JOIN  Localidades AS LK ON K.CdLocal=LK.IdLocal 
 LEFT JOIN Terceros AS OP ON K.CdOperario=OP.IdTercero
 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 Rutas AS R ON F.CdRuta=R.IdRuta
 LEFT JOIN FuentesPago AS FPG ON A.CdFntePago=FPG.IdFuente
 LEFT JOIN Subzonas AS SZA ON A.IdSzona=SZA.IdSzona LEFT JOIN Zonas AS ZA ON SZA.IdZona=ZA.IdZona 
 WHERE F.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND EsProdBase=0 AND F.TipDoc LIKE ISNULL(@pmTipDoc,'%')
 AND F.IdCia LIKE ISNULL(@pmIdCia,'%%') AND F.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND IdCliente LIKE ISNULL(@pmIdCliente,'%')  AND F.IdAgencia LIKE ISNULL(@pmIdAgencia ,'%')
 AND F.IdVend LIKE ISNULL(@pmIdVend,'%') AND F.IdLocEnv LIKE ISNULL(@pmIdLocEnv,'%')
 AND Modalidad LIKE ISNULL(@pmModalidad,'%') AND  K.IdProducto LIKE ISNULL(@pmIdProducto,'%')
 AND TipoRef LIKE ISNULL(@pmTipoRef,'%') AND G.IdLinea LIKE ISNULL(@pmIdLinea,'%')
 AND S.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND PM.IdSubgrupo LIKE ISNULL(@pmIdSubgrupo ,'%')
 UNION
 SELECT TipDev,Devolucion,D.IdCia AS CdCia,Compania,D.Fecha AS FechaDev,D.IdConcepto AS CdConcepto,Concepto,D.IdCliente AS NitCliente,T.RazonSocial AS NomCliente
,D.IdAgencia AS IdAgncia,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia,FechaVence,D.VrRetencion AS ReteFte,D.VrReteICA AS ReteIca,D.VrReteIVA AS ReteIVA,D.VrFletes AS Fletes
,D.VrOtros AS Otros,D.VrCargos AS OtrosCargos,D.VrOtrDcto AS OtrosDctos,D.VrNeto AS TotalNeto,D.CantPuntos AS CantPuntDev,0,D.IdLocEnv AS CodCiuEnvio,LE.Localidad AS CiudadEnvio
,LE.IdDep AS CdDepEnvio,DE.Departamento AS DptoEnvio,MulPlazos,F.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago,NitEmpTrans,EmpTrans,F.pVehiculo AS PlacaVeh,CdConductor,TC.RazonSocial AS Conductor
,F.CdRuta,Ruta,TipPed,D.Pedido AS NumPedido,D.IdCiaPed AS CdCiaPed,TipRem,D.Remision AS NumRemision,D.IdCiaRem AS CdCiaRem,TipCot,D.Cotizacion AS NumCotizacion,D.IdCiaCot AS CdCiaCotiza
,D.FecPedido AS FechaPed,D.Modalidad AS ModFactura,D.KmtVehic AS DevKmtVeh,D.TipCom AS TipoComp,D.Comprobante AS NumComp,D.IdCiaCom AS CodCiaCom,0,D.Factura AS NumFactura,FecDoc
,D.Observacion AS Observ,D.IdEstado AS CdEstado,Estado,D.TimeSys AS Fech_Sys,D.IdCiaCrea AS CdCiaCrea,D.IdUsuario AS IdUsuari,Usuario
--detalles
,K.IdProducto AS CdProducto,DescripProd,Item,K.IdBodega AS CdBodega,Bodega,CdTanque,Entradas,Salidas,K.IdUnd AS CdUnid,UM.Unidad AS UnidMed,VrUnitario
,VrCostoEnt,VrCostoSal,VrCostProm,VrPrecio,VrPrecio*(Entradas+Salidas) AS VrTotal,TarifaDct,VrDctoEnt,VrDctoSal,K.TarifaIva AS TarifIva,VrIvaEnt,VrIvaSal,K.TarifaRet AS TarifRet,VrReteEnt,VrReteSal
,K.TarifaIca AS TarifIca,VrIcaEnt,VrIcaSal,Sobretasa,TasaNac,TasaDep,TasaMun,Soldicom,ImpGlobal,Unidades,K.Descripcion AS KarDescripcion,K.Referencia AS Referncia,Referencia2,FecOrden
,CdAgencia,KA.Agencia AS KarAgencia,KA.CodAgencia AS KarCodAgencia,K.IdVend AS NitVend,VN.RazonSocial AS Vendedor,Comision,CdOperario,OP.RazonSocial AS NomOperario,ComisnOper
,K.pVehiculo AS KarPlacaVeh,CdLocal,LK.Localidad AS KarCiudad,CdCCosto,CCosto,K.CdSubCos,SubCosto,TipOrd,NumOrden,IdCiaOrd,K.Remision AS KarNumRemision,K.IdCiaRem AS KarCdCiaRem,K.Cotizacion AS KarNumCotizacion
,K.IdCiaCot AS KarCiaCotiza,ListaPrec,VrBruto,VrBase,BaseIvp,TarifaIvp,IvaIngProd,Servcios,NoVentas,EsCombo,EsProdBase,ItemCombo
,PM.IdSubgrupo AS CdSubgrupo,Subgrupo,S.IdGrupo AS CdGrupo,Grupo,G.IdLinea AS CdLinea,Linea,PM.IdMarca AS CdMarca,Marca
,DescripLong,DescripAbrv,ExtciaMin,ExtciaMax,ExtciaAct,Factor1,Factor2,Factor3,Factor4,Factor5
--Información 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 NomCiudad,L.IdDep AS CdDep,DP.Departamento AS TercDpto,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
--información del cliente
,CLI.IdSzona AS CdSubzona,SZ.Subzona,SZ.IdZona AS CdZona,Z.Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie,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,A.TipoZona,A.IdSzona,SZA.Subzona,SZA.IdZona,ZA.Zona
 FROM Trn_DevFcr AS D  INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia 
 INNER JOIN Terceros AS T ON D.IdCliente=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 Agencias  AS A ON D.IdAgencia=A.IdAgencia 
 INNER JOIN TercCliente AS CLI ON D.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 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 Trn_Facturas AS F ON D.TipDoc=F.TipDoc AND D.Factura=F.Factura AND D.IdCiaDoc=F.IdCia
 INNER JOIN Plazos AS PZ ON F.IdPlazo=PZ.IdPlazo
 INNER JOIN Trn_Kardex AS K ON D.TipDev=K.TipDoc AND D.Devolucion=K.Documento AND D.IdCia=K.IdCia
 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 Terceros AS VN ON K.IdVend=VN.IdTercero LEFT JOIN Agencias  AS KA ON K.CdAgencia=KA.IdAgencia
 LEFT JOIN CentroCosto AS CC ON K.CdCCosto=CC.IdCCosto LEFT JOIN SubCentros AS SC ON K.CdSubCos=SC.IdSubCos
 LEFT JOIN  Localidades AS LK ON K.CdLocal=LK.IdLocal  LEFT JOIN Terceros AS OP ON K.CdOperario=OP.IdTercero
 LEFT JOIN  Localidades AS LE ON D.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 Rutas AS R ON F.CdRuta=R.IdRuta
 LEFT JOIN FuentesPago AS FPG ON A.CdFntePago=FPG.IdFuente
 LEFT JOIN Subzonas AS SZA ON A.IdSzona=SZA.IdSzona LEFT JOIN Zonas AS ZA ON SZA.IdZona=ZA.IdZona 
 WHERE D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND EsProdBase=0 AND D.TipDoc LIKE ISNULL(@pmTipDoc ,'%') 
 AND D.IdCia LIKE ISNULL(@pmIdCia,'%%') AND F.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND D.IdCliente LIKE ISNULL(@pmIdCliente,'%')  AND D.IdAgencia LIKE ISNULL(@pmIdAgencia ,'%')
 AND D.IdVend LIKE ISNULL(@pmIdVend,'%') AND D.IdLocEnv LIKE ISNULL(@pmIdLocEnv,'%')
 AND D.Modalidad LIKE ISNULL(@pmModalidad,'%') AND  K.IdProducto LIKE ISNULL(@pmIdProducto,'%')
 AND TipoRef LIKE ISNULL(@pmTipoRef,'%') AND G.IdLinea LIKE ISNULL(@pmIdLinea,'%')
 AND S.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND PM.IdSubgrupo LIKE ISNULL(@pmIdSubgrupo ,'%')
 ORDER BY F.IdCia,F.Factura
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryFacturasNet] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdAgencia VARCHAR(16)=Null,@pmIdVend VARCHAR(16)=Null,@pmIdLocEnv VARCHAR(8)=Null
,@pmModalidad VARCHAR(10)=Null
 AS 
 SELECT TipDoc,Factura,F.IdCia AS CdCia,Compania,Fecha,F.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente,F.IdAgencia AS IdAgenc,A.Agencia AS NomAgencia
,A.CodAgencia AS Cod_Agencia,FechaVence,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrCostos,VrSobretasa,VrImpGlobal
,VrFaltantes,VrAnticipos,VrIvaIngProd,VrNeto,VrAplicado,Cantidad,CantPuntos,PuntosAcum,BaseImp,BaseRet,BaseIvaIgp,TarifaIva,TarifaRet,TarifaIca,TarifaRiv,TarifaRtc,F.IdCCosto AS IdCenCost,CCosto
,F.IdSubCos AS IdSubCent,SubCosto,F.IdVend AS NitVend,VN.RazonSocial AS Vendedor,TarifaCom,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,NitEmpTrans,EmpTrans,pVehiculo
,CdConductor,TC.RazonSocial AS Conductor,F.CdRuta,Ruta,TipPed,Pedido,IdCiaPed,TipRem,Remision,IdCiaRem,TipCot,Cotizacion,IdCiaCot,FecPedido,KmtVehic,VrImpCons,VrReteCREE,Modalidad,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev
,F.Observacion AS Observ,F.IdEstado AS CdEstado,Estado,TimeSys,F.FecUpdate AS Fec_Update,IdCiaCrea,F.IdUsuario AS CdUsuario,Usuario
--Información 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 NomCiudad,L.IdDep AS CdDep,D.Departamento AS TercDpto,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
--información del cliente
,CLI.IdSzona AS CdSubzona,SZ.Subzona,SZ.IdZona AS CdZona,Z.Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie,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.IdSzona AS AgeCdSubzona,SZA.Subzona AS AgeSubzona,SZA.IdZona AS AgeCdZona,ZA.Zona AS AgeZona
 FROM Trn_Facturas AS F  INNER JOIN Companias AS CN ON F.IdCia=CN.IdCia 
 INNER JOIN Terceros AS T ON F.IdCliente=T.IdTercero 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 Plazos AS PZ ON F.IdPlazo=PZ.IdPlazo INNER JOIN  Terceros AS VN ON F.IdVend=VN.IdTercero
 INNER JOIN Agencias  AS A ON F.IdAgencia=A.IdAgencia  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 Rutas AS R ON F.CdRuta=R.IdRuta
 LEFT JOIN SubCentros AS SC ON F.IdSubCos=SC.IdSubCos LEFT JOIN FuentesPago AS FPG ON A.CdFntePago=FPG.IdFuente
 LEFT JOIN Subzonas AS SZA ON A.IdSzona=SZA.IdSzona LEFT JOIN Zonas AS ZA ON SZA.IdZona=ZA.IdZona 
 WHERE TipDoc LIKE ISNULL(@pmTipDoc,'%') AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 AND F.IdCia LIKE ISNULL(@pmIdCia,'%%') AND F.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND IdCliente LIKE ISNULL(@pmIdCliente,'%')  AND F.IdAgencia LIKE ISNULL(@pmIdAgencia ,'%')
 AND F.IdVend LIKE ISNULL(@pmIdVend,'%') AND F.IdLocEnv LIKE ISNULL(@pmIdLocEnv,'%')
 AND Modalidad LIKE ISNULL(@pmModalidad,'%')
 UNION
 SELECT TipDev,Devolucion,D.IdCia AS CdCia,Compania,D.Fecha AS FechaDev,D.IdConcepto AS CdConcepto,Concepto,D.IdCliente AS NitCliente,T.RazonSocial AS NomCliente
,D.IdAgencia AS IdAgncia,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia,FechaVence,D.VrSubTotal AS SubTotal,D.VrDescuento AS Descuentos,D.VrImpuesto AS Impuestos
,D.VrRetencion AS ReteFte,D.VrReteICA AS ReteIca,D.VrReteIVA AS ReteIVA,D.VrFletes AS Fletes,D.VrOtros AS Otros,D.VrCargos AS OtrosCargos,D.VrOtrDcto AS OtrosDctos,D.VrCostos AS TotalCostos
,D.VrSobretasa AS Sobretasas,D.VrImpGlobal AS ImpGlobal,D.VrFaltantes AS Faltantes,D.VrAnticipos AS Anticipos,D.VrIvaIngProd,D.VrNeto AS TotalNeto,0,D.Cantidad AS CantDev,D.CantPuntos AS CantPuntDev,0
,D.BaseImp AS BaseIVA,D.BaseRet AS BaseRetFte,D.BaseIvaIgp,TarifaIva,TarifaRet,TarifaIca,TarifaRiv,D.TarifaRtc,D.IdCCosto AS IdCenCost,CCosto,D.IdSubCos AS IdSubCent,SubCosto,D.IdVend AS NitVend,VN.RazonSocial AS Vendedor
,D.TarifaCom AS TarifCom,DirEnvio,D.IdLocEnv AS CodCiuEnvio,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,NitEmpTrans,EmpTrans,pVehiculo,CdConductor,TC.RazonSocial AS Conductor,F.CdRuta,Ruta,TipPed,D.Pedido AS NumPedido,D.IdCiaPed AS CdCiaPed
,TipRem,D.Remision AS NumRemision,D.IdCiaRem AS CdCiaRem,TipCot,D.Cotizacion AS NumCotizacion,D.IdCiaCot AS CdCiaCotiza,D.FecPedido AS FechaPed,D.KmtVehic AS DevKmtVeh,D.VrImpCons,D.VrReteCREE,D.Modalidad AS ModFactura
,D.TipCom AS TipoComp,D.Comprobante AS NumComp,D.IdCiaCom AS CodCiaCom,0,D.Factura AS NumFactura,FecDoc,D.Observacion AS Observ,D.IdEstado AS CdEstado,Estado,D.TimeSys AS Fech_Sys
,D.FecUpdate AS Fech_Update,D.IdCiaCrea AS CdCiaCrea,D.IdUsuario AS IdUsuari,Usuario
--Información 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 NomCiudad,L.IdDep AS CdDep,DP.Departamento AS TercDpto,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
--información del cliente
,CLI.IdSzona AS CdSubzona,SZ.Subzona,SZ.IdZona AS CdZona,Z.Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie,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.IdSzona,SZA.Subzona,SZA.IdZona,ZA.Zona
 FROM Trn_DevFcr AS D INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia 
 INNER JOIN Terceros AS T ON D.IdCliente=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 Terceros AS VN ON D.IdVend=VN.IdTercero INNER JOIN Agencias AS A ON D.IdAgencia=A.IdAgencia 
 INNER JOIN CentroCosto AS CC ON D.IdCCosto=CC.IdCCosto 
 INNER JOIN TercCliente AS CLI ON D.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 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 Trn_Facturas AS F ON D.TipDoc=F.TipDoc AND D.Factura=F.Factura AND D.IdCiaDoc=F.IdCia
 INNER JOIN Formaspago AS FP ON F.IdForma=FP.IdForma INNER JOIN  Plazos AS PZ ON F.IdPlazo=PZ.IdPlazo
 LEFT JOIN  Localidades AS LE ON D.IdLocEnv=LE.IdLocal  LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep 
 LEFT JOIN SubCentros AS SC ON D.IdSubCos=SC.IdSubCos LEFT JOIN FuentesPago AS FPG ON A.CdFntePago=FPG.IdFuente 
 LEFT JOIN Terceros AS TC ON F.CdConductor=TC.IdTercero LEFT JOIN Rutas AS R ON F.CdRuta=R.IdRuta
 LEFT JOIN Subzonas AS SZA ON A.IdSzona=SZA.IdSzona LEFT JOIN Zonas AS ZA ON SZA.IdZona=ZA.IdZona 
 WHERE D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND D.TipDoc LIKE ISNULL(@pmTipDoc ,'%') 
 AND D.IdCia LIKE ISNULL(@pmIdCia,'%%') AND F.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND D.IdCliente LIKE ISNULL(@pmIdCliente,'%')  AND D.IdAgencia LIKE ISNULL(@pmIdAgencia ,'%')
 AND D.IdVend LIKE ISNULL(@pmIdVend,'%') AND D.IdLocEnv LIKE ISNULL(@pmIdLocEnv,'%')
 AND D.Modalidad LIKE ISNULL(@pmModalidad,'%')
 ORDER BY F.IdCia,Factura
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_KdexOcc] @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmItem,tmIdProducto,DescripProd,tmCdTanque,tmSalidas,tmVrPrecio,(tmSalidas*tmVrPrecio)-tmVrDcto AS VrSubTotal
,tmTarifaIva,tmVrIva,((tmSalidas*tmVrPrecio)-tmVrDcto)+tmVrIva AS VrTotal,tmComptmntos,tmSobretasa,tmImpGlobal,tmSoldicom
,tmTasaNac,tmTasaDep,tmTasaMun,tmUnidades,tmReferencia,tmDescripcion,tmIdBodega,Bodega,tmListaPrec,tmVrBruto,tmVrUnitario
,tmServcios,Tanques,tmVrBase,tmCdMoneda,tmVrTasaCamb,tmCdLocal,tmIdVend,tmComision,tmCodTarCom,tmpVehiculo,tmEsCombo
,tmReferencia2,tmRec_Costo,tmMgenCont,tmIvaComb,tmImpCarb,tmBaseIvp,tmTarifaIvp,tmIvaIngProd
 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_KdexGui] @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmItem,tmIdProducto,DescripProd,tmCdTanque,tmSalidas,tmVrPrecio,tmSalidas*tmVrPrecio AS VrSubTotal,tmComptmntos
,tmUnidades,tmReferencia,tmDescripcion,tmIdBodega,Bodega,tmVrBruto,tmVrUnitario,tmListaPrec
,tmServcios,Tanques,tmVrBase,tmCdMoneda,tmVrTasaCamb,tmCdLocal,tmIdVend,tmComision,tmCodTarCom,tmpVehiculo,tmEsCombo
,tmgalsbruto,tmgalsneto,tmTemperatura,tmUmTemp,tmDensidad,tmBaseIvp,tmTarifaIvp,tmIvaIngProd
 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_KdexPed] @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmItem,tmIdProducto,DescripProd,tmIdBodega,Bodega,tmCdTanque,tmSalidas,tmVrPrecio,tmTarifaDct,tmVrDcto,(tmSalidas*tmVrPrecio)-tmVrDcto AS VrSubTotal
,tmTarifaIva,tmVrIva,((tmSalidas*tmVrPrecio)-tmVrDcto)+tmVrIva AS VrTotal,tmComptmntos,tmSobretasa,tmImpGlobal,tmSoldicom,tmTasaNac,tmTasaDep,tmTasaMun
,tmVrBruto,tmListaPrec,tmUnidades,tmReferencia,tmDescripcion,tmCodTarDct
,tmIdVend,tmComision,tmCodTarCom,tmServcios,Tanques,tmVrUnitario,tmVrBase,tmCdMoneda,tmVrTasaCamb,tmTipDoc,tmDocumento,tmIdCia,tmRec_Costo,tmMgenCont
,tmIvaComb,tmImpCarb,tmCdOperario,tmComisnOper,tmCodTarCmc,tmpVehiculo,tmBaseIvp,tmTarifaIvp,tmIvaIngProd
 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 
 ORDER BY tmItem
GO