ALTER TABLE Trn_Kardex ADD IngBaseCom DECIMAL(16,4) DEFAULT(0) NOT NULL 
GO
ALTER TABLE tm_Kdex ADD tmIngCombo DECIMAL(16,4) DEFAULT(0) NOT NULL 
GO
CREATE TABLE tm_TraMovAnt ( 
Id BIGINT IDENTITY ( 1,1 ) NOT NULL,
IdEst CHAR(2) NOT NULL,
TipDoc VARCHAR(3) NOT NULL,
Numero INT DEFAULT ((0)) NOT NULL,
IdCia CHAR(2) NOT NULL,
Fecha SMALLDATETIME NOT NULL,
TipMuc VARCHAR(3) NOT NULL,
Manifiesto INT DEFAULT ((0)) NOT NULL,
IdCiaMuc CHAR(2) DEFAULT ('01') NOT NULL,
IdVehiculo VARCHAR(10) NOT NULL,
IdPoseedor VARCHAR(16) NOT NULL,
SaldoAnt MONEY DEFAULT ((0)) NOT NULL,
VrAnticipo MONEY DEFAULT ((0)) NOT NULL,
VrPagos MONEY DEFAULT ((0)) NOT NULL
 CONSTRAINT PK_tm_TraMovAnt PRIMARY KEY NONCLUSTERED (Id),
 CONSTRAINT CK_tm_TraMovAntIdCia CHECK ((len([IdCia])>(0))),
 CONSTRAINT CK_tm_TraMovAntIdCiaMuc CHECK ((len([IdCiaMuc])>(0))),
 CONSTRAINT CK_tm_TraMovAntIdEst CHECK ((len([IdEst])>(0))),
 CONSTRAINT CK_tm_TraMovAntIdPoseedor CHECK ((len([IdPoseedor])>(0))),
 CONSTRAINT CK_tm_TraMovAntIdVehiculo CHECK ((len([IdVehiculo])>(0))),
 CONSTRAINT CK_tm_TraMovAntTipDoc CHECK ((len([TipDoc])>(0))),
 CONSTRAINT CK_tm_TraMovAntTipMuc CHECK ((len([TipMuc])>(0))))
GO
 CREATE CLUSTERED INDEX IX_tm_TraMovAntIdPoseedor ON tm_TraMovAnt(IdEst,IdPoseedor,IdVehiculo,IdCia)
 CREATE NONCLUSTERED INDEX IX_tm_TraMovAntNumero ON tm_TraMovAnt(TipDoc,Numero,IdCia)

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].[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].[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].[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_KdexDetC]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_KdexDetC]
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].[paQrytm_TraMovAnt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_TraMovAnt]
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,ListaPrec,VrBruto,VrBase,Servcios,NoVentas,EsCombo,EsProdBase,ItemCombo
,CdMoneda,VrTasaCamb,VrDivisa1,VrDivisa2,VrDivisa3,VrImvCosto,TarifaIco,VrImpCon,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].[paQrytm_TraMovAnt] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdEst CHAR(2)
 AS 
 SELECT MA.TipDoc,TipoDoc,MA.Numero AS Anticipo,MA.IdCia AS CdCia,Compania,MA.Fecha,T.TipoId AS NitTipId,MA.IdPoseedor AS NitPoseedor,T.Dv AS NitDv,T.RazonSocial AS Poseedor,MA.IdVehiculo AS PlacaVeh,NumVeh
,MA.SaldoAnt,MA.VrAnticipo,MA.VrPagos AS VrAbonos,A.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor
,MA.TipMuc,MA.Manifiesto,MA.IdCiaMuc,A.Modalidad,A.TipoPago,A.NumCheque,A.FecCheque,A.Beneficiario,A.Anulado,A.Observacion AS Detalle,A.IdConcepto AS CdConcepto,Concepto,A.TipCom,A.Comprobante,A.IdCiaCom
,A.TipoAplica,A.VrPago,A.TimeSys
FROM tm_TraMovAnt AS MA INNER JOIN Companias AS CN ON MA.IdCia=CN.IdCia INNER JOIN Terceros AS T ON MA.IdPoseedor=T.IdTercero
INNER JOIN Vehiculos AS V ON MA.IdVehiculo=V.IdVehiculo
LEFT JOIN (SELECT TipDoc,Anticipo,IdCia,Fecha,IdConcepto,TipMuc,Manifiesto,IdCiaMuc,IdVehiculo,IdPoseedor,IdConductor,VrAnticipo,VrAbonado,Modalidad,TipoPago,NumCheque,FecCheque,IdCta,IdCuenta,Beneficiario
,CedBenef,FechaVence,NumPresAnt,PreAnticipo,NumCargue,CiaCargue,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,TimeSys,IdUsuario,'' AS TipoAplica,0 AS VrPago 
FROM Trn_TraAnticipos WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin
UNION ALL
SELECT P.TipDoc,P.Documento,P.IdCia,P.Fecha,A.IdConcepto,A.TipMuc,A.Manifiesto,A.IdCiaMuc,A.IdVehiculo,A.IdPoseedor,A.IdConductor,A.VrAnticipo,A.VrAbonado,A.Modalidad,A.TipoPago,A.NumCheque,A.FecCheque,A.IdCta,P.CdCuentaAnt,A.Beneficiario
,A.CedBenef,A.FechaVence,A.NumPresAnt,A.PreAnticipo,A.NumCargue,A.CiaCargue,P.TipAnt,P.Anticipo,P.IdCiaAnt,A.Anulado,A.NumDev,A.FecDev,P.Detalle,A.TimeSys,A.IdUsuario,P.TipoAplica,P.VrAbono
FROM Trn_TraAntAbonos AS P INNER JOIN Trn_TraAnticipos AS A ON P.TipAnt=A.TipDoc AND P.Anticipo=A.Anticipo AND P.IdCiaAnt=A.IdCia
WHERE P.Fecha BETWEEN @pmFechaIni AND @pmFechaFin
UNION ALL
SELECT A.TipDoc,A.Anticipo,A.IdCia,A.Fecha,A.IdConcepto,A.TipOds,A.NumOrden,A.IdCiaOds,O.IdVehiculo,O.IdPoseedor,O.IdConductor,A.VrAnticipo,A.VrAbonado,A.TipoAncpo,A.TipoPago,A.NumCheque,A.FecCheque,A.IdCta,A.IdCuenta,A.Beneficiario
,A.CedBenef,A.FechaVence,A.NumPresAnt,0,0,'',A.TipCom,A.Comprobante,A.IdCiaCom,A.Anulado,A.NumDev,A.FecDev,A.Observacion,A.TimeSys,A.IdUsuario,'',0
FROM Trn_TraOrdenAnt AS A INNER JOIN Trn_TraOrdenServ AS O ON A.TipOds=O.TipDoc AND A.NumOrden=O.NumOrden AND A.IdCiaOds=O.IdCia
WHERE A.Fecha BETWEEN @pmFechaIni AND @pmFechaFin
UNION ALL
SELECT P.TipDoc,P.Documento,P.IdCia,P.Fecha,A.IdConcepto,A.TipOds,A.NumOrden,A.IdCiaOds,O.IdVehiculo,O.IdPoseedor,O.IdConductor,A.VrAnticipo,A.VrAbonado,A.TipoAncpo,A.TipoPago,A.NumCheque,A.FecCheque,A.IdCta,P.CodCuenta,A.Beneficiario
,A.CedBenef,A.FechaVence,A.NumPresAnt,0,0,'',P.TipAnt,P.Anticipo,P.IdCiaAnt,A.Anulado,A.NumDev,A.FecDev,P.Observacion,A.TimeSys,A.IdUsuario,P.TipoAplica,P.VrAbono
FROM Trn_TraOrdenAbo AS P INNER JOIN Trn_TraOrdenAnt AS A ON P.TipAnt=A.TipDoc AND P.Anticipo=A.Anticipo AND P.IdCiaAnt=A.IdCia
INNER JOIN Trn_TraOrdenServ AS O ON A.TipOds=O.TipDoc AND A.NumOrden=O.NumOrden AND A.IdCiaOds=O.IdCia
WHERE P.Fecha BETWEEN @pmFechaIni AND @pmFechaFin
--Transporte especial
--UNION ALL 
--SELECT TipDoc,Anticipo,IdCia,Fecha,IdConcepto,TipRes,Reserva,IdCiaRes,IdVehiculo,IdPoseedor,IdConductor,VrAnticipo,VrAbonado,Modalidad,TipoPago,NumCheque,FecCheque,IdCta,IdCuenta,Beneficiario
--,CedBenef,FechaVence,NumPresAnt,PreAnticipo,0,'',TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,TimeSys,IdUsuario,'' AS TipoAplica,0 AS VrPago 
--FROM Trn_TraEspAnticipos
--WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
--UNION ALL 
--SELECT P.TipDoc,P.Documento,P.IdCia,P.Fecha,A.IdConcepto,A.TipRes,A.Reserva,A.IdCiaRes,A.IdVehiculo,A.IdPoseedor,A.IdConductor,A.VrAnticipo,A.VrAbonado,A.Modalidad,A.TipoPago,A.NumCheque,A.FecCheque,A.IdCta,P.CdCuentaAnt,A.Beneficiario
--,A.CedBenef,A.FechaVence,A.NumPresAnt,A.PreAnticipo,0,'',P.TipAnt,P.Anticipo,P.IdCiaAnt,A.Anulado,A.NumDev,A.FecDev,P.Detalle,A.TimeSys,A.IdUsuario,P.TipoAplica,P.VrAbono
--FROM Trn_TraEspAntAbonos AS P INNER JOIN Trn_TraEspAnticipos AS A ON P.TipAnt=A.TipDoc AND P.Anticipo=A.Anticipo AND P.IdCiaAnt=A.IdCia
--WHERE P.Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
) AS A ON MA.TipDoc=A.TipDoc AND MA.Numero=A.Anticipo AND MA.IdCia=A.IdCia
LEFT JOIN Sys_TiposDoc AS TD ON MA.TipDoc=TD.IdDoc
LEFT JOIN Terceros AS CDT ON A.IdConductor=CDT.IdTercero LEFT JOIN Conceptos AS C ON A.IdConcepto=C.IdConcepto
WHERE MA.IdEst=@pmIdEst
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)
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
 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 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)
 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)
 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)
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
 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].[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
 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].[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)
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
 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_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)
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
 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_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
--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].[paQrytm_KdexDetC] @pmtmNumero VARCHAR(5)
 AS 
--traslado de productos combos
 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,tmRec_Costo,tmMgenCont,tmCotizacion,tmCiaCotiza,tmVrImvCosto
,tmTarifaIco,tmVrImpCon,tmCantObseq,tmIvaObseq,tmIvaComb,tmImpCarb,tmIngCombo
--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 AND tmEsCombo<>0 AND Combo<>0 AND TipoRef<>'SERVICIO'
 AND  tmEsProdBase=0
 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)
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
 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 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
 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 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
 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 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
 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 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)
 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)
 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)
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
 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