DROP PROCEDURE CARGA_CTRLFUELDET_PROC
GO
CREATE PROCEDURE CARGA_CTRLFUELDET_PROC
( 	@SITEID		UD_IDSITE,
	@DATEPROCE	UD_FECHA,
	@CORRNBR	INT
)
AS
BEGIN
	BEGIN TRAN
	
	DECLARE @INVTIDSKU	UD_IDCORPORATIVO
	DECLARE @CODFAMFUEL	UD_CATEGORY

	DECLARE @STOCKINITIAL	UD_STOCK	
	DECLARE @INQTY		UD_STOCK
	DECLARE @OUTQTY		UD_STOCK
	DECLARE @QTYSALES	UD_STOCK
	DECLARE @UNITMEDFUEL	UD_STKUNITID
	
	SET @INQTY=0
	SET @OUTQTY=0
	SET @QTYSALES=0
	SET @CODFAMFUEL=(SELECT CATEGORYFUEL FROM OP_SETUP)
	SET @UNITMEDFUEL=(SELECT UNITMEDFUEL FROM OP_SETUP)

	DECLARE CUR_CTRLFUEL	CURSOR	
	FOR 	SELECT INVTIDSKU FROM IN_INVENTORY
		WHERE CLASSID=@CODFAMFUEL
		ORDER BY INVTIDSKU
	OPEN CUR_CTRLFUEL
   	FETCH NEXT FROM CUR_CTRLFUEL INTO @INVTIDSKU

      	WHILE @@FETCH_STATUS=0
        BEGIN    

		-- OBTENIENDO EL STOCK INICIAL DEL PRODUCTO
		SET @STOCKINITIAL=ISNULL((SELECT QTYAVAIL FROM OP_CTRLFUELDET
			    WHERE INVTIDSKU=@INVTIDSKU AND SITEID=@SITEID AND DATEPROCE=(@DATEPROCE-1)),0) 
 
		--SELECCION PARA INGRESOS DE ARTICULO
		SET @INQTY=ISNULL((SELECT SUM(I.QTY)
	 		    FROM IN_INTRAITEM AS I JOIN IN_TYPEDOC AS D ON I.TYPEDOCID=D.TYPEDOCID
			    WHERE D.INOUT=1 AND I.INVTIDSKU=@INVTIDSKU AND I.SITEID=@SITEID AND INTRDATEPROCE=@DATEPROCE),0)
	
		--SELECCION PARA SALIDAS DE ARTICULO
		SET @OUTQTY=ISNULL((SELECT SUM(I.QTY)
	 	    	    FROM IN_INTRAITEM AS I JOIN IN_TYPEDOC AS D ON I.TYPEDOCID=D.TYPEDOCID
		    	    WHERE D.INOUT=0 AND I.INVTIDSKU=@INVTIDSKU AND I.SITEID=@SITEID AND INTRDATEPROCE=@DATEPROCE),0)

		SET @QTYSALES=ISNULL((SELECT SUM(QTY) FROM OP_SALESDET WHERE SITEID=@SITEID AND DATEPROCESALES=@DATEPROCE AND INVTIDSKU=@INVTIDSKU AND DOCSTATUSID<>'AN'),0)

		INSERT INTO TMP_OP_CTRLFUELDET(CORRNBR,SITEID,DATEPROCE,INVTIDSKU,STOCKINITIAL,QTYIMPUT,QTYOUTPUT,QTYSALES,
			                       QTYAVAIL,STKUINITID,VALUEUNIMED,QTYPHISICAL,QTYDIFFERENCE,PORCENTDIFERENCE) 
		VALUES(@CORRNBR,@SITEID,@DATEPROCE,@INVTIDSKU,@STOCKINITIAL,@INQTY,@OUTQTY,@QTYSALES,
			                       @STOCKINITIAL+@INQTY-@OUTQTY-@QTYSALES,@UNITMEDFUEL,0,0,0,0)

    		FETCH NEXT FROM CUR_CTRLFUEL INTO  @INVTIDSKU
        
      	END /* Termina while */

	COMMIT TRAN
  	CLOSE CUR_CTRLFUEL
  	DEALLOCATE CUR_CTRLFUEL

	RETURN(0)
END 


