--- /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>