DECLARE 
/*
**************************************************************************
* The contents of this file are subject to the Compiere Public
* License 1.1 ("License"); You may not use this file except in
* compliance with the License. You may obtain a copy of the License in
* the legal folder of your Openbravo installation.
* Software distributed under the License is distributed on an
* "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
* implied. See the License for the specific language governing rights
* and limitations under the License.
* The Original Code is  Compiere  ERP &  Business Solution
* The Initial Developer of the Original Code is Jorg Janke and ComPiere, Inc.
* Portions created by Jorg Janke are Copyright (C) 1999-2001 Jorg Janke,
* parts created by ComPiere are Copyright (C) ComPiere, Inc.;
* All Rights Reserved.
* Contributor(s): Openbravo SL
* Contributions are Copyright (C) 2001-2008 Openbravo, S.L.
*
* Specifically, this derivative work is based upon the following Compiere
* file and version.
*************************************************************************
* Return quantity on hand for BOM
*/
    v_myWarehouse_ID NUMERIC;
    v_Quantity NUMERIC := 99999; -- unlimited
    v_IsBOM CHAR(1) ;
    v_IsStocked CHAR(1) ;
    v_ProductType CHAR(1) ;
    v_ProductQty NUMERIC;
    v_StdPrecision NUMERIC;

    CUR_BOM RECORD;

  BEGIN
    -- Verificamos los Parametros pasados
    v_myWarehouse_ID := p_Warehouse_ID;
    IF(v_myWarehouse_ID IS NULL) THEN
      IF(p_Locator_ID IS NULL) THEN
        RETURN 0;
      ELSE
        SELECT MAX(M_Warehouse_ID)
        INTO v_myWarehouse_ID
        FROM M_LOCATOR
        WHERE M_Locator_ID = p_Locator_ID;
      END IF;
    END IF;
    IF(v_myWarehouse_ID IS NULL) THEN
      RETURN 0;
    END IF;
	
	-- Obtenemos el producto solicitado y verificamos si existe
	BEGIN
		SELECT IsBOM, ProductType, IsStocked
		INTO v_IsBOM, v_ProductType, v_IsStocked
		FROM M_PRODUCT
		WHERE M_Product_ID = p_Product_ID;
		EXCEPTION WHEN OTHERS THEN	RETURN 0;    -- No se encontró el producto
	END;
	-- Cualquier Producto que no sea un Item Ó, cualquiera que NO sea ALMACENADO SIN SER un BOM tiene como capacidad Ilimitada (99999)
	IF((v_ProductType <> 'I' OR (v_IsStocked = 'N' AND (v_IsBOM = 'N')))) THEN
		RETURN v_Quantity;
	-- Si es un producto de cualquier tipo pero que sea almacenado devolvemos su cantidad
	ELSIF(v_IsStocked = 'Y') THEN
		SELECT COALESCE(SUM(QtyOnHand), 0)
		INTO v_ProductQty
		FROM M_STORAGE_DETAIL s
		WHERE M_Product_ID = p_Product_ID  
		AND EXISTS( 
			SELECT *
			FROM M_LOCATOR l
			WHERE s.M_Locator_ID = l.M_Locator_ID  
			AND l.M_Warehouse_ID = v_myWarehouse_ID
		);
		RETURN v_ProductQty;
	END IF;
  
	-- Si estamos aca es porque es un BOM NO ALMACENADO **************************
	-- Stocked Items "leaf mode" (Es un Producto no almacenado como lista de productos almacenados)
	-- Barremos TODOS SUS ITEMS para ver si es un BOM de varios niveles
	FOR CUR_BOM IN (
		SELECT b.M_ProductBOM_ID, b.BOMQty, p.IsBOM, p.IsStocked, p.ProductType
		FROM M_PRODUCT_BOM b, M_PRODUCT p
		WHERE b.M_ProductBOM_ID = p.M_Product_ID  
		AND b.M_Product_ID = p_Product_ID
    )
	LOOP
		-- Vemos si este item es el ultimo (no es un BOM)
	    IF(CUR_BOM.ProductType = 'I' AND CUR_BOM.IsBOM = 'N') THEN
			-- si es asi obtenemos la cantidad del producto
			SELECT COALESCE(SUM(QtyOnHand), 0)
			INTO v_ProductQty
			FROM M_STORAGE_DETAIL s
			WHERE M_Product_ID = CUR_BOM.M_ProductBOM_ID  
			AND EXISTS( 
				SELECT *	FROM M_LOCATOR l
				WHERE s.M_Locator_ID = l.M_Locator_ID  
				AND l.M_Warehouse_ID = v_myWarehouse_ID
			);
			-- Obtenemos el factor de redondeo
			SELECT COALESCE(MAX(u.StdPrecision), 0)
			INTO v_StdPrecision
			FROM C_UOM u, M_PRODUCT p
			WHERE u.C_UOM_ID = p.C_UOM_ID 
			AND p.M_Product_ID = CUR_BOM.M_ProductBOM_ID;
			-- Vemos cuanto podemos tomar para este producto y lo redondeamos
			IF (COALESCE(CUR_BOM.BOMQty, 0) = 0) THEN
				v_ProductQty := 0;
			ELSE
				v_ProductQty := ROUND(v_ProductQty/CUR_BOM.BOMQty, v_StdPrecision) ;
			END IF;
			-- Vemos si no alcanza la cant Max disp. del item para la cant disp. Max del BOM
			IF(v_ProductQty < v_Quantity) THEN
				v_Quantity := v_ProductQty;
			END IF;
	    
		-- El BOM tiene como elemento otro BOM por lo que utilizamos la recursividad
	    ELSIF(CUR_BOM.IsBOM = 'Y') THEN
			v_ProductQty := Bom_Qty_Onhand(CUR_BOM.M_ProductBOM_ID, v_myWarehouse_ID) ;
			-- Vemos si no alcanza la cant Max disp. del item para la cant disp. Max del BOM
			IF(v_ProductQty < v_Quantity) THEN
				v_Quantity := v_ProductQty;
			END IF;
	    END IF;
	END LOOP;
	-- Redondeamos la cantidad del producto BOM obtenida de sus items
	IF(v_Quantity > 0) THEN
		SELECT COALESCE(MAX(u.StdPrecision), 0)
		INTO v_StdPrecision
		FROM C_UOM u, M_PRODUCT p
		WHERE u.C_UOM_ID = p.C_UOM_ID AND p.M_Product_ID = p_Product_ID;
		RETURN ROUND(v_Quantity, v_StdPrecision) ;
	END IF;
	RETURN 0;
END ;