[Reservations]Implement m_get_stock in m_production_run.
authorGorka Ion Damián <gorkaion.damian@openbravo.com>
Fri, 24 Aug 2012 11:58:19 +0200
changeset 18622 dcbe12538f69
parent 18621 d30a66caee9c
child 18623 1ae62a77b80e
[Reservations]Implement m_get_stock in m_production_run.
src-db/database/model/functions/M_PRODUCTION_RUN.xml
--- a/src-db/database/model/functions/M_PRODUCTION_RUN.xml	Fri Aug 24 09:28:08 2012 +0200
+++ b/src-db/database/model/functions/M_PRODUCTION_RUN.xml	Fri Aug 24 11:58:19 2012 +0200
@@ -61,23 +61,19 @@
   p_User VARCHAR2(32);
   v_Product_Name VARCHAR2(2000):='';
   -- Stocked BOMs
-  CURSOR CUR_STOCK (Product_ID VARCHAR, UOM VARCHAR, Warehouse_ID VARCHAR, Org_ID VARCHAR) IS
-      SELECT T.M_PRODUCT_ID, T.M_LOCATOR_ID, T.M_ATTRIBUTESETINSTANCE_ID,
-        T.C_UOM_ID, T.M_PRODUCT_UOM_ID,
-        COALESCE(T.QtyOnHand, 0) AS Qty, T.QtyOrderOnHand AS QtyOrder
-      FROM M_STORAGE_DETAIL T
-        LEFT OUTER JOIN M_ATTRIBUTESETINSTANCE A ON T.M_ATTRIBUTESETINSTANCE_ID = A.M_ATTRIBUTESETINSTANCE_ID,
-        M_LOCATOR L, M_WAREHOUSE W
-      WHERE T.M_PRODUCT_ID=Product_ID
-        AND T.C_UOM_ID=UOM
-        AND T.M_LOCATOR_ID=L.M_LOCATOR_ID
-        AND COALESCE(T.QtyOnHand, 0)>0
-        AND L.M_WAREHOUSE_ID = W.M_WAREHOUSE_ID
-        AND ad_isorgincluded(Org_ID, W.AD_ORG_ID, W.AD_CLIENT_ID) <> -1
-      ORDER BY(
-        CASE L.M_WAREHOUSE_ID WHEN Warehouse_ID THEN 1 ELSE 2
-        END
-        ), L.PRIORITYNO, A.CREATED, T.CREATED, T.M_PRODUCT_UOM_ID;
+  -- Proposed stock from given warehouse is priorized.
+  CURSOR CUR_STOCK (v_ad_pinstance_id VARCHAR, v_warehouse_id VARCHAR) IS
+      SELECT sd.m_product_id, sd.m_locator_id, sd.m_attributesetinstance_id,
+          sd.c_uom_id, sd.m_product_uom_id, u.stdprecision,
+          ms.quantity AS qty, ms.qtyorder AS qtyorder,
+          ms.priority
+      FROM m_stock_proposed ms 
+            LEFT JOIN m_storage_detail sd ON ms.m_storage_detail_id = sd.m_storage_detail_id
+            LEFT JOIN m_product_uom pu ON sd.m_product_uom_id = pu.m_product_uom_id
+            LEFT JOIN c_uom u ON pu.c_uom_id = u.c_uom_id
+            LEFT JOIN m_locator l ON sd.m_locator_id = l.m_locator_id
+      WHERE ms.ad_pinstance_id = v_ad_pinstance_id
+      ORDER BY (CASE l.m_warehouse_id WHEN v_warehouse_id THEN 0 ELSE 1 END), ms.priority;
   v_storage CUR_STOCK%ROWTYPE;
   NEXT_PRODUCT BOOLEAN:=false;
   CUR_STOCK_ISOPEN BOOLEAN:=false;
@@ -212,11 +208,11 @@
       v_Product_old VARCHAR2(32);
       v_Warehouse_old VARCHAR2(32);
       v_UOM_old VARCHAR2(32);
-      v_Warehouse_old_org VARCHAR2(32);
       v_Qty NUMBER;
       v_QtyStorage NUMBER:=0;
       v_QtyAcumulated NUMBER;
       v_QtyOrder NUMBER;
+      v_pinstance_id VARCHAR2(32);
       CUR_BOM RECORD;
     BEGIN
       FOR CUR_BOM IN (
@@ -236,31 +232,68 @@
         IF (CUR_BOM.IsStocked = 'Y') THEN
           v_QtyAcumulated:=0;
           IF ((NOT CUR_STOCK_ISOPEN) 
-              OR (v_Product_old<>CUR_BOM.M_ProductBOM_ID OR v_Warehouse_old<>cur_bom.m_warehouse_id OR v_UOM_old<>CUR_BOM.C_UOM_ID)) THEN
-            v_Product_old := CUR_BOM.M_ProductBOM_ID;
-            v_Warehouse_old := cur_bom.m_warehouse_id;
-            v_UOM_old := CUR_BOM.C_UOM_ID;
-            SELECT ad_org_id INTO v_Warehouse_old_org
-            FROM m_warehouse
-            WHERE m_warehouse_id = v_Warehouse_old;
+              OR (v_Product_old <> CUR_BOM.M_ProductBOM_ID OR v_Warehouse_old <> cur_bom.m_warehouse_id OR v_UOM_old <> CUR_BOM.C_UOM_ID)) THEN
+            v_product_old := cur_bom.m_productbom_id;
+            v_warehouse_old := cur_bom.m_warehouse_id;
+            v_uom_old := cur_bom.c_uom_id;
             IF (CUR_STOCK_ISOPEN) THEN
               CLOSE CUR_STOCK;
               CUR_STOCK_ISOPEN:=false;
             END IF;
-            OPEN CUR_STOCK(v_Product_old, v_UOM_old, v_Warehouse_old, v_warehouse_old_org);
+            
+            -- Call M_GET_STOCK
+            DECLARE
+              v_pinstance_result AD_PInstance.result%TYPE;
+              v_pinstance_msg AD_PInstance.errormsg%TYPE;
+            BEGIN
+              v_pinstance_id := get_uuid();
+              INSERT INTO ad_pinstance (
+                  ad_pinstance_id, ad_process_id, record_id, isactive, 
+                  ad_user_id, ad_client_id, ad_org_id, created, createdby,
+                  updated, updatedby
+              ) VALUES (
+                  v_pinstance_id, 'FF80818132C964E30132C9747257002E', v_record_id, 'Y',
+                  p_user, v_client_id, v_org_id, now(), p_user,
+                  now(), p_user
+              );
+
+              AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '10', 'AD_Client_ID', v_client_id, null, null, null, null, null);
+              AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '20', 'AD_Org_ID', cur_bom.org_id, null, null, null, null, null);
+              AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '30', 'M_Product_ID', v_Product_old, null, null, null, null, null);
+              AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '40', 'C_Uom_ID', v_UOM_old, null, null, null, null, null);
+              AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '50', 'M_Product_Uom_ID', null, null, null, null, null, null);
+              -- Records in m_aux_stock are not considered in BOM production.
+              --AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '60', 'AuxID', v_record_id, null, null, null, null, null);
+              --AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '70', 'TableId', '325', null, null, null, null, null);
+              AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '80', 'LineNo', null, null, 10, null, null, null);
+              AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '90', 'ProcessID', '137', null, null, null, null, null);
+
+              M_GET_STOCK(v_pinstance_ID, 'N');
+              -- Check result
+              SELECT result, errormsg
+                INTO v_pinstance_result, v_pinstance_msg
+              FROM ad_pinstance
+              WHERE ad_pinstance_id = v_pinstance_id;
+              IF (v_pinstance_result = 0) THEN
+                -- Error on m_get_stock
+                RAISE_APPLICATION_ERROR(-20000, v_pinstance_msg);
+              END IF;
+            END; -- End Call M_GET_STOCK
+            
+            OPEN CUR_STOCK(v_pinstance_id, v_Warehouse_old);
             CUR_STOCK_ISOPEN:=true;
             FETCH CUR_STOCK INTO v_storage;
             IF (CUR_STOCK%NOTFOUND) THEN
               NEXT_PRODUCT:=true;
             END IF;
-            v_QtyStorage:=v_storage.Qty;
+            v_qtystorage:=v_storage.qty;
           END IF;
           IF (NOT NEXT_PRODUCT) THEN
             LOOP
               v_Qty := LEAST(v_QtyStorage, CUR_BOM.ProductionQty * CUR_BOM.BOMQty - v_QtyAcumulated) ;
               v_QtyStorage := v_QtyStorage - v_Qty;
               v_QtyAcumulated := v_QtyAcumulated + v_Qty;
-              v_QtyOrder := v_storage.QtyOrder*(v_Qty/v_storage.Qty);
+              v_QtyOrder := ROUND(v_storage.QtyOrder*(v_Qty/v_storage.Qty), v_storage.stdprecision);
               SELECT COALESCE(MAX(Line), 0) + 10 INTO v_Line
               FROM M_PRODUCTIONLINE
               WHERE M_ProductionPlan_ID=CUR_BOM.M_ProductionPlan_ID;