DROP PROCEDURE GENERA_RESUMENES_MOVIMIENTOS_PROC
GO
CREATE PROCEDURE GENERA_RESUMENES_MOVIMIENTOS_PROC
(
 @FECHAINI	CHAR(15),
 @FECHAFIN	CHAR(15)
)
AS
BEGIN
	-- EDGAR CANCHO RUIZ
	-- ESTE PROCESO SOLO TOMARA EN CUENTA LOS DOCUMENTOS
	-- QUE SE ENCUENTREN DENTRO DEL RAMGO DE FECHAS Y QUE
	-- ESTEN EMITIDOS(RLSED=1), ADEMAS QUE EL CAMPO STKCLOSED=0  
	BEGIN TRAN
	
   	DECLARE  @DATFECHAINI SMALLDATETIME
   	DECLARE  @DATFECHAFIN SMALLDATETIME
	DECLARE  @CADENA	VARCHAR(8000)
	DECLARE  @SITEPROCE	UD_IDSITE

	DECLARE  @CATEGORYFUEL UD_CATEGORY
	DECLARE  @CATEGORYLUBES UD_CATEGORY

	SET @CATEGORYFUEL=(SELECT CATEGORYFUEL FROM OP_SETUP)
	SET @CATEGORYLUBES=(SELECT CATEGORYLUBES FROM OP_SETUP)

	DECLARE @ND_COSTOSETUP Tinyint
	DECLARE @ND_STOCKSETUP Tinyint
	DECLARE @ND_RESULSETUP Tinyint

	DECLARE @NUMREG	INT
	SET @NUMREG=0	

	SET @SITEPROCE=(SELECT SOURCESITE FROM SETUP)

        SET @DATFECHAINI=CAST(RTRIM(LTRIM(@FECHAINI)) AS SMALLDATETIME)
        SET @DATFECHAFIN=CAST(RTRIM(LTRIM(@FECHAFIN)) AS SMALLDATETIME)

  	SET @ND_COSTOSETUP=(Select Floatcost from setup)
  	SET @ND_STOCKSETUP=(Select Floatqty from setup)
	SET @ND_RESULSETUP=(Select FLOATOUTPUT from setup)

	SET @CADENA="SELECT I.SITEID,I.BATNBR,I.TYPEDOCID,I.DOCTYPEID,I.REFNBR,I.VENDIDSS,V.VENDORID,V.TERMID,V.TAXID,V.CURYTYPEID,I.CURYRATE,I.SITEPROCES,I.SITEDEST,I.INTRDATE,I.INTRDATEPROCE,I.FROMCURYID,I.INTRTAXPEN,I.INTRTAXUSD,I.INTRSUBTOTPEN,I.INTRSUBTOTUSD,0,0 " +
		    "FROM IN_INTRAHEAD AS I JOIN IN_VENDOR AS V " +
		    "ON I.VENDIDSS=V.VENDIDSS " +	
	       	    "WHERE SITEID='" + @SITEPROCE + "' AND INTRDATEPROCE BETWEEN '" + 
		    RTRIM(LTRIM(CAST(@DATFECHAINI AS CHAR(100)))) + "' AND '" + RTRIM(LTRIM(CAST(@DATFECHAFIN AS CHAR(100)))) + "' AND STKCLOSED=0 AND RLSED=1 " 
	
	--CREATE TABLE #TEMPORAL(SITEID CHAR(5),BATNBR CHAR(10),TYPEDOCID CHAR(5),VENDIDSS CHAR(20),DOCTYPEID CHAR(3) )


	-- CREO LAS CABECERAS RESUMIDAS DE IN_INTRAHEAD
	INSERT INTO IN_SUMMARY_INTRAHEAD EXEC (@CADENA)
	IF (@@ERROR!=0)
	BEGIN
		ROLLBACK
		RAISERROR 20000 'Error Insertando en IN_SUMMARY_INTRAHEAD'
		RETURN(1)
	END 
	
	DECLARE @SITEID UD_IDSITE
	DECLARE @BATNBR CHAR(10)
	DECLARE @TYPEDOCID UD_TYPEDOC
	DECLARE @VENDIDSS UD_IDVENDOR
	DECLARE @DOCTYPEID UD_DOCTYPEID

	DECLARE @INTRDATEPROCE UD_FECHA
	
	
	DECLARE TMPCURSOR CURSOR	
	FOR 	SELECT SITEID,BATNBR,TYPEDOCID,VENDIDSS,DOCTYPEID,INTRDATEPROCE
		FROM IN_INTRAHEAD
		WHERE SITEID=@SITEPROCE AND INTRDATEPROCE BETWEEN @DATFECHAINI AND @DATFECHAFIN AND STKCLOSED=0 AND RLSED=1 

	OPEN TMPCURSOR
	FETCH NEXT FROM TMPCURSOR
		   INTO @SITEID,@BATNBR,@TYPEDOCID,@VENDIDSS,@DOCTYPEID,@INTRDATEPROCE

	WHILE @@FETCH_STATUS=0
	BEGIN

	-- PRIMERO PREGUNTO SI EXISTEN REGISTROS DE LA CATEGORIA DE COMBUSTIBLE(01) O DE LUBRICANTES(21)
		SELECT A.CLASSID,SUM(I.QTY) AS CANTIDAD
		FROM IN_INTRAITEM AS I JOIN IN_INVENTORY AS A
		ON I.INVTIDSKU=A.INVTIDSKU
		WHERE SITEID=@SITEID AND BATNBR=@BATNBR AND TYPEDOCID=@TYPEDOCID AND (A.CLASSID=@CATEGORYFUEL OR A.CLASSID=@CATEGORYLUBES)
		GROUP BY A.CLASSID

		SET @NUMREG=@@ROWCOUNT 	

	--INSERTO EL DETALLE RESUMIDO POR CADA DOCUMENTO

		IF(@NUMREG=0) -- SE HACE LA AGRUPACION POR CATEGORIA 
		BEGIN
			INSERT INTO IN_SUMMARY_INTRAITEM(SITEID,BATNBR,TYPEDOCID,INVTIDSKU,INVTIDSOLOMON,INTRDATEPROCE,QTY,STDCOSTPEN,STDCOSTUSD,INTRSUBTOTPEN,INTRSUBTOTUSD)
			SELECT 	@SITEID,@BATNBR,@TYPEDOCID,A.CLASSID,C.INVTIDSOLOMON,@INTRDATEPROCE,SUM(I.QTY) AS CANTIDAD,
				ROUND((ROUND(SUM(I.QTY*I.STDCOSTPEN),@ND_RESULSETUP)/SUM(I.QTY)),@ND_COSTOSETUP) AS CostoPEN,
				ROUND((ROUND(SUM(I.QTY*I.STDCOSTUSD),@ND_RESULSETUP)/SUM(I.QTY)),@ND_COSTOSETUP) AS CostoUSD,
				ROUND(SUM(I.QTY*I.STDCOSTPEN),@ND_RESULSETUP) AS TotalPEN,ROUND(SUM(I.QTY*I.STDCOSTUSD),@ND_RESULSETUP) AS TotalUSD 
			FROM (IN_INTRAITEM AS I JOIN IN_INVENTORY AS A
			ON I.INVTIDSKU=A.INVTIDSKU) JOIN IN_CATEGORY AS C ON A.CLASSID=C.CLASSID
			WHERE SITEID=@SITEID AND BATNBR=@BATNBR AND TYPEDOCID=@TYPEDOCID
			GROUP BY A.CLASSID,C.INVTIDSOLOMON
		END
		ELSE -- SE INSERTA COMO ESTA
		BEGIN
		--	
			INSERT INTO IN_SUMMARY_INTRAITEM(SITEID,BATNBR,TYPEDOCID,INVTIDSKU,INVTIDSOLOMON,INTRDATEPROCE,QTY,STDCOSTPEN,STDCOSTUSD,INTRSUBTOTPEN,INTRSUBTOTUSD)
			SELECT  @SITEID,@BATNBR,@TYPEDOCID,I.INVTIDSKU,D.INVTIDSTK,@INTRDATEPROCE,I.QTY,I.STDCOSTPEN,I.STDCOSTUSD,ROUND(I.QTY*I.STDCOSTPEN,@ND_RESULSETUP) AS TotalPEN,
				ROUND(I.QTY*I.STDCOSTUSD,@ND_RESULSETUP) AS TotalUSD
			FROM IN_INTRAITEM AS I JOIN IN_INVENTORYDAT AS D ON
			I.SITEID=D.SITEID AND I.INVTIDSKU=D.INVTIDSKU
			WHERE I.SITEID=@SITEID AND I.BATNBR=@BATNBR AND I.TYPEDOCID=@TYPEDOCID
		--
		END

		IF(@@ERROR!=0 OR @@ROWCOUNT=0)
		BEGIN
			CLOSE TMPCURSOR
			DEALLOCATE TMPCURSOR
			ROLLBACK
			RAISERROR 20000 'Error Insertando en IN_SUMMARY_INTRAITEM'
			RETURN(1)
		END

		-- ACTUALIZANDO IN_INTRAHEAD
		UPDATE IN_INTRAHEAD SET STKCLOSED=1
		WHERE  SITEID=@SITEID AND BATNBR=@BATNBR AND TYPEDOCID=@TYPEDOCID

		IF(@@ERROR!=0 OR @@ROWCOUNT=0)
		BEGIN
			CLOSE TMPCURSOR
			DEALLOCATE TMPCURSOR
			ROLLBACK
			RAISERROR 20000 'Error Actualizando en IN_INTRAHEAD'
			RETURN(1)
		END

		FETCH NEXT FROM TMPCURSOR
			   INTO @SITEID,@BATNBR,@TYPEDOCID,@VENDIDSS,@DOCTYPEID,@INTRDATEPROCE

	END

	CLOSE TMPCURSOR
	DEALLOCATE TMPCURSOR
	COMMIT TRAN
	RETURN(0)
END  


GENERA_RESUMENES_MOVIMIENTOS_PROC '20030201','20030228'

DELETE FROM IN_SUMMARY_INTRAITEM
GO
DELETE FROM IN_SUMMARY_INTRAHEAD

SELECT * FROM IN_INTRAITEM ORDER BY BATNBR,INVTIDSKU

SELECT * FROM IN_INTRAHEAD WHERE BATNBR>='5700000010'
GO
SELECT * FROM IN_SUMMARY_INTRAHEAD
GO
SELECT * FROM IN_SUMMARY_INTRAITEM

SELECT * FROM IN_INVENTORYDAT


SELECT D.INVTIDSKU,I.DESCR,D.INVTIDSTK,D.SITEID,I.CLASSID FROM IN_INVENTORY AS I  JOIN IN_INVENTORYDAT AS D
ON I.INVTIDSKU=D.INVTIDSKU  
WHERE D.SITEID='57' AND I.CLASSID='01'

UPDATE IN_INVENTORYDAT SET INVTIDSTK='84'
WHERE SITEID='57' AND INVTIDSKU='5900001'


UPDATE IN_INTRAHEAD SET STKCLOSED=0

SELECT * FROM IN_INTRAHEAD
SELECT SUM(INTRSUBTOTPEN) AS SUMA FROM IN_SUMMARY_INTRAITEM

SELECT * FROM IN_SUMMARY_INTRAHEAD



SELECT * FROM OP_SETUP 
