--Nuevo reporte de comparativo inventario: paQrytm_SaldosProd
INSERT INTO Sys_Report (TipoRep,IdRep,Descripcion,Formato,TipoPapel,Orientacion,VistaPrevia,VerSetup,NumCopias,sp_Nombre,Ind_Location,NomRep)
 VALUES ('CSA','20','Comprobación de Saldos/Detalle productos (Hz)','CrLcsK.rpt',1,2,1,0,1,'paQrytm_Saldos','','Comprobación de saldos')
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[paInstm_Saldos_Kar] @pmtmEst CHAR(2),@pmDetCia BIT,@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME
,@pmIdCia CHAR(2)=Null,@pmTipoRef VARCHAR(10)=Null,@pmIdSubgrupo VARCHAR(8)=Null
 AS 
 IF @pmDetCia=1 
 BEGIN 
  INSERT INTO tm_Saldos (tmEst,tmIdCia,tmIdCuenta,tmIdTercero,tmCdSubgrupo,tmSanCue,tmDebitos,tmCreditos,tmCostoAnt,tmCostoEnt,tmCostoSal,tmSaldoAnt,tmEntradas,tmSalidas)
  SELECT @pmtmEst,IdCia,IdCuenta,'0',P.IdSubgrupo,0,0,0,0,SUM(VrCostoEnt),SUM(VrCostoSal),0,SUM(Entradas),SUM(Salidas)
  FROM Trn_Kardex AS K INNER JOIN ProdMcias AS P ON K.IdProducto=P.IdProducto
  LEFT JOIN (SELECT IdSubgrupo,IdCuenta  FROM SubgruposCue WHERE IdClase='0001') AS SC ON P.IdSubgrupo=SC.IdSubgrupo
  WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND  TipDoc NOT IN ('ODC','PED','REM','COT','GUI','ODS','OCC','VCC','NTS')
  AND TipoRef<>'SERVICIO'  AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND TipoRef LIKE ISNULL(@pmTipoRef,'%')
  AND P.IdSubgrupo LIKE ISNULL(@pmIdSubgrupo,'%')
  GROUP BY IdCia,IdCuenta,P.IdSubgrupo 
 END
ELSE
 BEGIN 
  INSERT INTO tm_Saldos (tmEst,tmIdCia,tmIdCuenta,tmIdTercero,tmCdSubgrupo,tmSanCue,tmDebitos,tmCreditos,tmCostoAnt,tmCostoEnt,tmCostoSal,tmSaldoAnt,tmEntradas,tmSalidas)
  SELECT @pmtmEst,'00',IdCuenta,'0',P.IdSubgrupo,0,0,0,0,SUM(VrCostoEnt),SUM(VrCostoSal),0,SUM(Entradas),SUM(Salidas)
  FROM Trn_Kardex AS K INNER JOIN ProdMcias AS P ON K.IdProducto=P.IdProducto
  LEFT JOIN (SELECT IdSubgrupo,IdCuenta  FROM SubgruposCue WHERE IdClase='0001') AS SC ON P.IdSubgrupo=SC.IdSubgrupo
  WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND  TipDoc NOT IN ('ODC','PED','REM','COT','GUI','ODS','OCC','VCC','NTS')
  AND TipoRef<>'SERVICIO'  AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND TipoRef LIKE ISNULL(@pmTipoRef,'%')
  AND P.IdSubgrupo LIKE ISNULL(@pmIdSubgrupo,'%')
  GROUP BY  IdCuenta,P.IdSubgrupo 
 END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_SaldosProd] @pmtmEst CHAR(2),@pmFechaIni SMALLDATETIME=Null,@pmFechaFin SMALLDATETIME=Null,@pmCias BIT=Null
 AS 
 IF @pmCias=1 --Detalle por compañias
  BEGIN 
   SELECT tmIdCuenta,NomCuenta,G.IdLinea AS CdLinea,Linea,SG.IdGrupo AS CdGrupo,Grupo,tmCdSubgrupo,Subgrupo,tmIdCia,Compania
   ,tmCostoAnt,tmCostoEnt,tmCostoSal,tmSaldoAnt,tmEntradas,tmSalidas,tmSanCue,tmDebitos,tmCreditos
   ,KD.IdProducto AS CodProducto,KD.DescripProd,KD.CantEntradas,KD.CantSalidas,KD.CostoEnt,KD.CostoSal
   FROM tm_Saldos AS S LEFT JOIN Puc AS P ON S.tmIdCuenta=P.IdCuenta
   LEFT JOIN Companias AS CN ON  S.tmIdCia=CN.IdCia LEFT JOIN SubGrupos AS SG ON S.tmCdSubgrupo=SG.IdSubgrupo 
   LEFT JOIN Grupos AS G ON SG.IdGrupo=G.IdGrupo LEFT JOIN Lineas AS LN ON G.IdLinea=LN.IdLinea
   LEFT JOIN (SELECT K.IdProducto,P.DescripProd,P.IdSubgrupo,K.IdCia,SUM(K.Entradas) AS CantEntradas,SUM(K.Salidas) AS CantSalidas,SUM(K.VrCostoEnt) AS CostoEnt,SUM(K.VrCostoSal) AS CostoSal
   FROM Trn_Kardex AS K INNER JOIN ProdMcias AS P ON K.IdProducto=P.IdProducto
   WHERE K.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND K.TipDoc NOT IN ('ODC','PED','REM','COT','GUI','ODS','OCC','VCC','NTS')
   AND P.TipoRef<>'SERVICIO' GROUP BY K.IdProducto,P.DescripProd,P.IdSubgrupo,K.IdCia) AS KD ON S.tmCdSubgrupo=KD.IdSubgrupo AND S.tmIdCia=KD.IdCia
   WHERE tmEst=@pmtmEst 
  END
 ELSE
  BEGIN 
   SELECT tmIdCuenta,NomCuenta,G.IdLinea AS CdLinea,Linea,SG.IdGrupo AS CdGrupo,Grupo,tmCdSubgrupo,Subgrupo,tmIdCia,Compania
   ,tmCostoAnt,tmCostoEnt,tmCostoSal,tmSaldoAnt,tmEntradas,tmSalidas,tmSanCue,tmDebitos,tmCreditos
   ,KD.IdProducto AS CodProducto,KD.DescripProd,KD.CantEntradas,KD.CantSalidas,KD.CostoEnt,KD.CostoSal
   FROM tm_Saldos AS S LEFT JOIN Puc AS P ON S.tmIdCuenta=P.IdCuenta
   LEFT JOIN Companias AS CN ON  S.tmIdCia=CN.IdCia LEFT JOIN SubGrupos AS SG ON S.tmCdSubgrupo=SG.IdSubgrupo 
   LEFT JOIN Grupos AS G ON SG.IdGrupo=G.IdGrupo LEFT JOIN Lineas AS LN ON G.IdLinea=LN.IdLinea
   LEFT JOIN (SELECT K.IdProducto,P.DescripProd,P.IdSubgrupo,SUM(K.Entradas) AS CantEntradas,SUM(K.Salidas) AS CantSalidas,SUM(K.VrCostoEnt) AS CostoEnt,SUM(K.VrCostoSal) AS CostoSal
   FROM Trn_Kardex AS K INNER JOIN ProdMcias AS P ON K.IdProducto=P.IdProducto
   WHERE K.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND K.TipDoc NOT IN ('ODC','PED','REM','COT','GUI','ODS','OCC','VCC','NTS')
   AND P.TipoRef<>'SERVICIO' GROUP BY K.IdProducto,P.DescripProd,P.IdSubgrupo) AS KD ON S.tmCdSubgrupo=KD.IdSubgrupo
   WHERE tmEst=@pmtmEst 
  END
GO