[Warehouse]Add unique locator and attribute warehouse rules.
authorGorka Ion Damián <gorkaion.damian@openbravo.com>
Tue, 09 Oct 2012 15:34:15 +0200
changeset 18640 b436bc7d3b64
parent 18639 455f07682422
child 18641 1a1231b55075
[Warehouse]Add unique locator and attribute warehouse rules.
referencedata/standard/Core_Warehouse_Rules.xml
src-db/database/model/functions/M_UNIQUEATTRIBUTE_RULE.xml
src-db/database/model/functions/M_UNIQUELOCATOR_RULE.xml
src-db/database/sourcedata/AD_MESSAGE.xml
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/referencedata/standard/Core_Warehouse_Rules.xml	Tue Oct 09 15:34:15 2012 +0200
@@ -0,0 +1,17 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<ob:Openbravo xmlns:ob="http://www.openbravo.com" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
+  <WarehouseRule id="33DAC92D21624E0A9D8C537382F2D28C" identifier="Unique Storage Bin">
+    <id>33DAC92D21624E0A9D8C537382F2D28C</id>
+    <active>true</active>
+    <name>Unique Storage Bin</name>
+    <procedure>M_UNIQUELOCATOR_RULE</procedure>
+    <description xsi:nil="true"/>
+  </WarehouseRule>
+  <WarehouseRule id="5F23F9B25A834B78A0549AF028E29051" identifier="Unique Attribute">
+    <id>5F23F9B25A834B78A0549AF028E29051</id>
+    <active>true</active>
+    <name>Unique Attribute</name>
+    <procedure>M_UNIQUEATTRIBUTE_RULE</procedure>
+    <description xsi:nil="true"/>
+  </WarehouseRule>
+</ob:Openbravo>
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src-db/database/model/functions/M_UNIQUEATTRIBUTE_RULE.xml	Tue Oct 09 15:34:15 2012 +0200
@@ -0,0 +1,108 @@
+<?xml version="1.0"?>
+  <database name="FUNCTION M_UNIQUEATTRIBUTE_RULE">
+    <function name="M_UNIQUEATTRIBUTE_RULE" type="NULL">
+      <parameter name="pinstance_id" type="VARCHAR" mode="in">
+        <default/>
+      </parameter>
+      <parameter name="p_warehouse_id" type="VARCHAR" mode="in">
+        <default/>
+      </parameter>
+      <body><![CDATA[/*************************************************************************
+* The contents of this file are subject to the Openbravo  Public  License
+* Version  1.1  (the  "License"),  being   the  Mozilla   Public  License
+* Version 1.1  with a permitted attribution clause; you may not  use this
+* file except in compliance with the License. You  may  obtain  a copy of
+* the License at http://www.openbravo.com/legal/license.html
+* 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 Openbravo ERP.
+* The Initial Developer of the Original Code is Openbravo SLU
+* All portions are Copyright (C) 2012 Openbravo SLU
+* All Rights Reserved.
+* Contributor(s):  ______________________________________.
+************************************************************************/
+  v_ResultStr VARCHAR2(2000):='';
+  v_Message VARCHAR2(2000):='';
+
+  TYPE RECORD IS REF CURSOR;
+  Cur_Parameter RECORD;
+
+  v_quantity NUMBER;
+  v_ProductID   VARCHAR2(32);
+  v_SelectedAttSetInstanceID VARCHAR2(32) ;
+  v_ProductName M_Product.NAME%TYPE;
+BEGIN
+
+  v_ResultStr:='ReadingParameters';
+  FOR Cur_Parameter IN (
+      SELECT i.record_id, p.parametername, p.p_string, p.p_number, p.p_date, i.createdby AS ad_user_id
+      FROM ad_pinstance i
+      LEFT JOIN ad_pinstance_para p ON i.ad_pinstance_id=p.ad_pinstance_id
+      WHERE i.ad_pinstance_id=pinstance_id
+        AND p.parametername IN ('Quantity', 'M_Product_ID')
+      ORDER BY p.seqno
+  ) LOOP
+
+    IF (Cur_Parameter.ParameterName = 'Quantity') THEN
+      v_Quantity:=Cur_Parameter.P_Number;
+      DBMS_OUTPUT.PUT_LINE(' Quantity=' || v_Quantity);
+    ELSIF (Cur_Parameter.ParameterName = 'M_Product_ID') THEN
+      v_ProductID:=Cur_Parameter.P_String;
+      DBMS_OUTPUT.PUT_LINE(' M_Product_ID=' || v_ProductID);
+    ELSE
+      DBMS_OUTPUT.PUT_LINE('*** Unknown Parameter=' || Cur_Parameter.ParameterName) ;
+    END IF;
+  END LOOP; -- Get Parameter
+
+  DELETE FROM m_stock_proposed ms
+  WHERE ad_pinstance_id = pinstance_id
+    AND EXISTS (SELECT 1
+                FROM m_storage_detail t
+                WHERE t.m_storage_detail_id = ms.m_storage_detail_id
+                AND t.m_attributesetinstance_id = '0');
+  v_SelectedAttSetInstanceID := NULL;
+
+  FOR Cur_MStock IN (
+      SELECT A.m_attributesetinstance_id
+      FROM (
+        SELECT SUM(ms.quantity) AS qty, t.m_attributesetinstance_id, MIN(priority) AS priority
+        FROM m_stock_proposed ms
+             JOIN m_storage_detail t ON ms.m_storage_detail_id = t.m_storage_detail_id
+        WHERE ad_pinstance_id = pinstance_id
+        GROUP BY t.m_attributesetinstance_id
+        HAVING sum(ms.quantity) >= v_quantity
+        ) A
+      ORDER BY A.priority
+  ) LOOP
+    v_selectedattsetinstanceid := cur_mstock.m_attributesetinstance_id;
+    EXIT;
+  END LOOP;
+
+  DELETE FROM m_stock_proposed
+  WHERE m_stock_proposed_id IN (SELECT m_stock_proposed_id
+                                FROM m_stock_proposed ms
+                                    JOIN m_storage_detail sd ON ms.m_storage_detail_id = sd.m_storage_detail_id
+                                    JOIN m_locator l ON sd.m_locator_id = l.m_locator_id
+                                WHERE ms.ad_pinstance_id = pinstance_id
+                                  AND sd.m_attributesetinstance_id <> COALESCE(v_selectedattsetinstanceid, '-1')
+                                  AND l.m_warehouse_id = p_warehouse_id);
+
+  IF (v_selectedattsetinstanceid IS NULL) THEN
+    SELECT name INTO v_productname
+    FROM m_product
+    WHERE m_product_id = v_productid;
+    v_Message := '@Product@: ' || v_productname ||'. @NotEnoughAttUniqueStock@';
+    AD_UPDATE_PINSTANCE(PInstance_ID, NULL, 'N', 0, v_Message, 'N');
+    RETURN;
+  END IF;
+EXCEPTION
+WHEN OTHERS THEN
+  v_ResultStr:= '@ERROR=' || SQLERRM;
+  AD_UPDATE_PINSTANCE(PInstance_ID, NULL, 'N', 0, v_ResultStr, 'N');
+  RETURN;
+END M_UNIQUEATTRIBUTE_RULE
+]]></body>
+    </function>
+  </database>
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src-db/database/model/functions/M_UNIQUELOCATOR_RULE.xml	Tue Oct 09 15:34:15 2012 +0200
@@ -0,0 +1,98 @@
+<?xml version="1.0"?>
+  <database name="FUNCTION M_UNIQUELOCATOR_RULE">
+    <function name="M_UNIQUELOCATOR_RULE" type="NULL">
+      <parameter name="pinstance_id" type="VARCHAR" mode="in">
+        <default/>
+      </parameter>
+      <parameter name="p_warehouse_id" type="VARCHAR" mode="in">
+        <default/>
+      </parameter>
+      <body><![CDATA[/*************************************************************************
+* The contents of this file are subject to the Openbravo  Public  License
+* Version  1.1  (the  "License"),  being   the  Mozilla   Public  License
+* Version 1.1  with a permitted attribution clause; you may not  use this
+* file except in compliance with the License. You  may  obtain  a copy of
+* the License at http://www.openbravo.com/legal/license.html
+* 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 Openbravo ERP.
+* The Initial Developer of the Original Code is Openbravo SLU
+* All portions are Copyright (C) 2012 Openbravo SLU
+* All Rights Reserved.
+* Contributor(s):  ______________________________________.
+************************************************************************/
+  v_ResultStr VARCHAR2(2000):='';
+  v_Message VARCHAR2(2000):='';
+
+  TYPE RECORD IS REF CURSOR;
+  Cur_Parameter RECORD;
+
+  v_quantity NUMBER;
+  v_ProductID   VARCHAR2(32);
+  v_SelectedLocatorID VARCHAR2(32) ;
+  v_ProductName M_Product.NAME%TYPE;
+BEGIN
+
+  v_ResultStr:='ReadingParameters';
+  FOR Cur_Parameter IN (
+      SELECT i.record_id, p.parametername, p.p_string, p.p_number, p.p_date, i.createdby AS ad_user_id
+      FROM ad_pinstance i
+      LEFT JOIN ad_pinstance_para p ON i.ad_pinstance_id=p.ad_pinstance_id
+      WHERE i.ad_pinstance_id=pinstance_id
+        AND p.parametername IN ('Quantity', 'M_Product_ID')
+      ORDER BY p.seqno
+  ) LOOP
+
+    IF (Cur_Parameter.ParameterName = 'Quantity') THEN
+      v_Quantity:=Cur_Parameter.P_Number;
+      DBMS_OUTPUT.PUT_LINE(' Quantity=' || v_Quantity);
+    ELSIF (Cur_Parameter.ParameterName = 'M_Product_ID') THEN
+      v_ProductID:=Cur_Parameter.P_String;
+      DBMS_OUTPUT.PUT_LINE(' M_Product_ID=' || v_ProductID);
+    END IF;
+  END LOOP; -- Get Parameter
+
+  FOR Cur_MStock IN (
+      SELECT sum(ms.quantity) AS qty, t.m_locator_id, MIN(priority) AS priority
+      FROM m_stock_proposed ms
+           JOIN m_storage_detail t ON ms.m_storage_detail_id = t.m_storage_detail_id
+           JOIN m_locator l ON t.m_locator_id = l.m_locator_id
+      WHERE ad_pinstance_id = pinstance_id
+        AND l.m_warehouse_id = p_warehouse_id
+      GROUP BY t.m_locator_id
+      HAVING sum(ms.quantity) >= v_quantity
+      ORDER BY ms.priority
+  ) LOOP
+    v_selectedlocatorid := cur_mstock.m_locator_id;
+    EXIT;
+  END LOOP;
+
+  DELETE FROM m_stock_proposed
+  WHERE m_stock_proposed_id IN (SELECT m_stock_proposed_id
+                                FROM m_stock_proposed ms
+                                    JOIN m_storage_detail sd ON ms.m_storage_detail_id = sd.m_storage_detail_id
+                                    JOIN m_locator l ON sd.m_locator_id = l.m_locator_id
+                                WHERE ms.ad_pinstance_id = pinstance_id
+                                  AND sd.m_locator_id <> COALESCE(v_selectedlocatorid, '-1')
+                                  AND l.m_warehouse_id = p_warehouse_id);
+
+  IF (v_selectedlocatorid IS NULL) THEN
+    SELECT name INTO v_productname
+    FROM m_product
+    WHERE m_product_id = v_productid;
+    v_Message := '@Product@: ' || v_productname ||'. @NotEnoughLocatorUniqueStock@';
+    AD_UPDATE_PINSTANCE(PInstance_ID, NULL, 'N', 0, v_Message, 'N');
+    RETURN;
+  END IF;
+
+EXCEPTION
+WHEN OTHERS THEN
+  v_ResultStr:= '@ERROR=' || SQLERRM;
+  AD_UPDATE_PINSTANCE(PInstance_ID, NULL, 'N', 0, v_ResultStr, 'N');
+  RETURN;
+END M_UNIQUELOCATOR_RULE
+]]></body>
+    </function>
+  </database>
--- a/src-db/database/sourcedata/AD_MESSAGE.xml	Tue Oct 09 15:33:52 2012 +0200
+++ b/src-db/database/sourcedata/AD_MESSAGE.xml	Tue Oct 09 15:34:15 2012 +0200
@@ -14574,6 +14574,17 @@
 <!--04EEC6B52A7B4C2CACA1F767217FAFB7-->  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
 <!--04EEC6B52A7B4C2CACA1F767217FAFB7--></AD_MESSAGE>
 
+<!--055CE8C3899346499AB8E10E7A7108DA--><AD_MESSAGE>
+<!--055CE8C3899346499AB8E10E7A7108DA-->  <AD_MESSAGE_ID><![CDATA[055CE8C3899346499AB8E10E7A7108DA]]></AD_MESSAGE_ID>
+<!--055CE8C3899346499AB8E10E7A7108DA-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
+<!--055CE8C3899346499AB8E10E7A7108DA-->  <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID>
+<!--055CE8C3899346499AB8E10E7A7108DA-->  <ISACTIVE><![CDATA[Y]]></ISACTIVE>
+<!--055CE8C3899346499AB8E10E7A7108DA-->  <VALUE><![CDATA[NotEnoughLocatorUniqueStock]]></VALUE>
+<!--055CE8C3899346499AB8E10E7A7108DA-->  <MSGTEXT><![CDATA[Not enough stock from unique Storage Bin]]></MSGTEXT>
+<!--055CE8C3899346499AB8E10E7A7108DA-->  <MSGTYPE><![CDATA[E]]></MSGTYPE>
+<!--055CE8C3899346499AB8E10E7A7108DA-->  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
+<!--055CE8C3899346499AB8E10E7A7108DA--></AD_MESSAGE>
+
 <!--0647DAB48B604FB99CE5AC4339762854--><AD_MESSAGE>
 <!--0647DAB48B604FB99CE5AC4339762854-->  <AD_MESSAGE_ID><![CDATA[0647DAB48B604FB99CE5AC4339762854]]></AD_MESSAGE_ID>
 <!--0647DAB48B604FB99CE5AC4339762854-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>