[Warehouse]Implement priority warehouse in get_stock.
authorGorka Ion Damián <gorkaion.damian@openbravo.com>
Thu, 25 Oct 2012 17:43:09 +0200
changeset 18664 096e9ef090a6
parent 18663 157c92ecd0e2
child 18665 a7ab41ccd828
[Warehouse]Implement priority warehouse in get_stock.
src-db/database/model/functions/M_GET_STOCK.xml
src-db/database/model/functions/M_INOUT_CREATE.xml
src-db/database/model/functions/M_PRODUCTION_RUN.xml
--- a/src-db/database/model/functions/M_GET_STOCK.xml	Thu Oct 25 17:20:43 2012 +0200
+++ b/src-db/database/model/functions/M_GET_STOCK.xml	Thu Oct 25 17:43:09 2012 +0200
@@ -41,6 +41,7 @@
   v_ProductID   VARCHAR2(32);
   v_LocatorID   VARCHAR2(32);
   v_WarehouseID VARCHAR2(32) ;
+  v_PriorityWarehouseID VARCHAR2(32);
   v_OrgID VARCHAR2(32) ;
   v_AttributesetinstanceID  VARCHAR2(32) ;
   v_AD_User_ID VARCHAR2(32) ;
@@ -91,6 +92,9 @@
     ELSIF (Cur_Parameter.ParameterName = 'M_Warehouse_ID') THEN
       v_WarehouseID:=Cur_Parameter.P_String;
       DBMS_OUTPUT.PUT_LINE(' M_Warehouse_ID=' || v_WarehouseID);
+    ELSIF (Cur_Parameter.ParameterName = 'Priority_Warehouse_ID') THEN
+      v_PriorityWarehouseID:=Cur_Parameter.P_String;
+      DBMS_OUTPUT.PUT_LINE(' Priority_Warehouse_ID=' || v_WarehouseID);
     ELSIF (Cur_Parameter.ParameterName = 'AD_Client_ID') THEN
       v_ClientID:=Cur_Parameter.P_String;
       DBMS_OUTPUT.PUT_LINE(' AD_Client_ID=' || v_ClientID);
@@ -143,7 +147,7 @@
         AND COALESCE(w.m_warehouse_id, '-1') = COALESCE(v_warehouseid, w.m_warehouse_id, '-1')
         AND ow.ad_org_id = v_orgid
         AND COALESCE(ms.quantity, 0)>0
-      ORDER BY ow.priority, l.priorityno, asi.created, t.created
+      ORDER BY CASE w.m_warehouse_id WHEN COALESCE(v_PriorityWarehouseID, '-1') THEN 0 ELSE 1 END, ow.priority, l.priorityno, asi.created, t.created
   ) LOOP
     INSERT INTO m_stock_proposed (
         m_stock_proposed_id, ad_client_id, ad_org_id, isactive, 
@@ -175,7 +179,7 @@
         AND COALESCE(w.m_warehouse_id, '-1') = COALESCE(v_warehouseid, w.m_warehouse_id, '-1')
         AND ow.ad_org_id = v_orgid
         AND COALESCE(t.qtyonhand, 0)>0
-      ORDER BY ow.priority, l.priorityno, asi.created, t.created
+      ORDER BY CASE w.m_warehouse_id WHEN COALESCE(v_PriorityWarehouseID, '-1') THEN 0 ELSE 1 END, ow.priority, l.priorityno, asi.created, t.created
   ) LOOP
     INSERT INTO m_stock_proposed (
         m_stock_proposed_id, ad_client_id, ad_org_id, isactive, 
--- a/src-db/database/model/functions/M_INOUT_CREATE.xml	Thu Oct 25 17:20:43 2012 +0200
+++ b/src-db/database/model/functions/M_INOUT_CREATE.xml	Thu Oct 25 17:43:09 2012 +0200
@@ -420,7 +420,7 @@
                   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', v_Product_UOM_old, null, null, null, null, null);
-                  AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '60', 'M_Warehouse_ID', v_Warehouse_old, null, null, null, null, null);
+                  AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '60', 'Priority_Warehouse_ID', v_Warehouse_old, null, null, null, null, null);
                   AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '70', 'M_AttributesetInstance_ID', v_Attribute_old, null, null, null, null, null);
                   AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '80', 'Quantity', null, null, v_Qty, null, null, null);
                   AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '90', 'ProcessID', '118', null, null, null, null, null);
--- a/src-db/database/model/functions/M_PRODUCTION_RUN.xml	Thu Oct 25 17:20:43 2012 +0200
+++ b/src-db/database/model/functions/M_PRODUCTION_RUN.xml	Thu Oct 25 17:43:09 2012 +0200
@@ -62,7 +62,7 @@
   v_Product_Name VARCHAR2(2000):='';
   -- Stocked BOMs
   -- Proposed stock from given warehouse is priorized.
-  CURSOR CUR_STOCK (v_ad_pinstance_id VARCHAR, v_warehouse_id VARCHAR) IS
+  CURSOR CUR_STOCK (v_ad_pinstance_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,
@@ -73,7 +73,7 @@
             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;
+      ORDER BY ms.priority;
   v_storage CUR_STOCK%ROWTYPE;
   NEXT_PRODUCT BOOLEAN:=false;
   CUR_STOCK_ISOPEN BOOLEAN:=false;
@@ -265,6 +265,7 @@
               -- 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, '60', 'Priority_Warehouse_ID', v_Warehouse_old, 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);
 
@@ -280,7 +281,7 @@
               END IF;
             END; -- End Call M_GET_STOCK
             
-            OPEN CUR_STOCK(v_pinstance_id, v_Warehouse_old);
+            OPEN CUR_STOCK(v_pinstance_id);
             CUR_STOCK_ISOPEN:=true;
             FETCH CUR_STOCK INTO v_storage;
             IF (CUR_STOCK%NOTFOUND) THEN