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 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)+(VrImpuBa*Entradas)+(VrImpuCup*Entradas)) WHEN 'COM' THEN VrCostoEnt-((NumInicial*Entradas)+(VrImpuBa*Entradas)+(VrImpuCup*Entradas)) ELSE VrCostoEnt END) AS SCOSENT ,SUM(CASE TipDoc WHEN 'DVE' THEN VrCostoSal-((NumInicial*Salidas)+(VrImpuBa*Salidas)+(VrImpuCup*Salidas)) WHEN 'DEI' THEN VrCostoSal-((NumInicial*Salidas)+(VrImpuBa*Salidas)+(VrImpuCup*Salidas)) ELSE VrCostoSal END) AS SCOSSAL ,SUM(CASE WHEN CantObseq>0 AND Entradas>0 THEN VrPrecio*(Entradas-CantObseq) ELSE VrPrecio*Entradas END) AS SVALENT ,SUM(CASE WHEN CantObseq>0 AND Salidas>0 THEN VrPrecio*(Salidas-CantObseq) ELSE VrPrecio*Salidas END) AS SVALSAL ,SUM(CASE WHEN CantObseq>0 AND Entradas>0 THEN VrBruto*(Entradas-CantObseq) ELSE VrBruto*Entradas END) AS SBRUENT ,SUM(CASE WHEN CantObseq>0 AND Salidas>0 THEN VrBruto*(Salidas-CantObseq) ELSE VrBruto*Salidas END) 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 (CASE WHEN CantObseq>0 THEN VrIvaSal-VrIvaObseq ELSE VrIvaSal END) ELSE (CASE WHEN CantObseq>0 THEN VrIvaEnt-VrIvaObseq ELSE VrIvaEnt END) END) AS SIVA --,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 ,SUM(VrImpuBa*(Entradas+Salidas+CantObseq)) AS SIMPUBA,SUM(VrImpuCup*(Entradas+Salidas+CantObseq)) AS SIMPUCUP ,SUM(VrAviTab) AS SIAT,SUM(VrSobBom) AS SBOM 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)+(VrImpuBa*Entradas)+(VrImpuCup*Entradas)) WHEN 'COM' THEN VrCostoEnt-((NumInicial*Entradas)+(VrImpuBa*Entradas)+(VrImpuCup*Entradas)) ELSE VrCostoEnt END) AS SCOSENT ,SUM(CASE TipDoc WHEN 'DVE' THEN VrCostoSal-((NumInicial*Salidas)+(VrImpuBa*Salidas)+(VrImpuCup*Salidas)) WHEN 'DEI' THEN VrCostoSal-((NumInicial*Salidas)+(VrImpuBa*Salidas)+(VrImpuCup*Salidas)) ELSE VrCostoSal END) AS SCOSSAL ,SUM(CASE WHEN CantObseq>0 AND Entradas>0 THEN VrPrecio*(Entradas-CantObseq) ELSE VrPrecio*Entradas END) AS SVALENT ,SUM(CASE WHEN CantObseq>0 AND Salidas>0 THEN VrPrecio*(Salidas-CantObseq) ELSE VrPrecio*Salidas END) AS SVALSAL ,SUM(CASE WHEN CantObseq>0 AND Entradas>0 THEN VrBruto*(Entradas-CantObseq) ELSE VrBruto*Entradas END) AS SBRUENT ,SUM(CASE WHEN CantObseq>0 AND Salidas>0 THEN VrBruto*(Salidas-CantObseq) ELSE VrBruto*Salidas END) 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 (CASE WHEN CantObseq>0 THEN VrIvaSal-VrIvaObseq ELSE VrIvaSal END) ELSE (CASE WHEN CantObseq>0 THEN VrIvaEnt-VrIvaObseq ELSE VrIvaEnt END) END) AS SIVA --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 ,SUM(VrImpuBa*(Entradas+Salidas+CantObseq)) AS SIMPUBA,SUM(VrImpuCup*(Entradas+Salidas+CantObseq)) AS SIMPUCUP ,SUM(VrAviTab) AS SIAT,SUM(VrSobBom) AS SBOM 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