details: https://code.openbravo.com/erp/devel/pi/rev/cfa8ffeab1dd
changeset: 22941:cfa8ffeab1dd
user: Gorka Ion Damián <gorkaion.damian <at> openbravo.com>
date: Tue Apr 29 10:19:32 2014 +0200
summary: Fixed issue 25420.Allow to reactivate a requisition to modify the
order.
diffstat:
src-db/database/model/functions/M_REQUISITIONLINE_STATUS.xml | 10 +-
src-db/database/model/tables/M_REQUISITIONORDER.xml | 5 +-
src-db/database/model/triggers/C_ORDERLINE2_TRG.xml | 84 +++++++++++-
src-db/database/model/triggers/C_ORDERLINE_TRG.xml | 8 +-
src-db/database/model/triggers/M_REQUISITIONLINE_TRG.xml | 7 +-
src-db/database/model/triggers/M_REQUISITIONORDER_TRG.xml | 10 +-
src-db/database/model/triggers/M_REQUISITION_TRG.xml | 21 +-
src-db/database/sourcedata/AD_MESSAGE.xml | 24 +++
8 files changed, 148 insertions(+), 21 deletions(-)
diffs (truncated from 341 to 300 lines):
diff -r 3a3619678a3c -r cfa8ffeab1dd
src-db/database/model/functions/M_REQUISITIONLINE_STATUS.xml
--- a/src-db/database/model/functions/M_REQUISITIONLINE_STATUS.xml Tue May
06 10:08:33 2014 +0200
+++ b/src-db/database/model/functions/M_REQUISITIONLINE_STATUS.xml Tue Apr
29 10:19:32 2014 +0200
@@ -22,7 +22,7 @@
* under the License.
* The Original Code is Openbravo ERP.
* The Initial Developer of the Original Code is Openbravo SLU
-* All portions are Copyright (C) 2008 Openbravo SLU
+* All portions are Copyright (C) 2008-2014 Openbravo SLU
* All Rights Reserved.
* Contributor(s): ______________________________________.
************************************************************************/
@@ -61,6 +61,14 @@
v_Record_ID:=p_RequisitionLine_ID;
v_User_ID := p_User_ID;
END IF;
+ -- Reset requisition status to CO in case it is closed.
+ UPDATE M_Requisition
+ SET DocStatus = 'CO',
+ Updated = now(),
+ UpdatedBy = v_User_ID
+ WHERE M_Requisition_ID = (SELECT M_Requisition_ID FROM M_RequisitionLine
WHERE M_RequisitionLine_ID = v_Record_ID)
+ AND docstatus = 'CL';
+
v_ResultStr := 'Updating reqstatus';
UPDATE M_RequisitionLine
SET ReqStatus = (CASE ReqStatus
diff -r 3a3619678a3c -r cfa8ffeab1dd
src-db/database/model/tables/M_REQUISITIONORDER.xml
--- a/src-db/database/model/tables/M_REQUISITIONORDER.xml Tue May 06
10:08:33 2014 +0200
+++ b/src-db/database/model/tables/M_REQUISITIONORDER.xml Tue Apr 29
10:19:32 2014 +0200
@@ -51,12 +51,15 @@
<foreign-key foreignTable="AD_ORG" name="M_REQUISITIONORDER_AD_ORG">
<reference local="AD_ORG_ID" foreign="AD_ORG_ID"/>
</foreign-key>
- <foreign-key foreignTable="C_ORDERLINE"
name="M_REQUISITIONORDER_C_ORDERLINE">
+ <foreign-key foreignTable="C_ORDERLINE"
name="M_REQUISITIONORDER_C_ORDERLINE" onDelete="cascade">
<reference local="C_ORDERLINE_ID" foreign="C_ORDERLINE_ID"/>
</foreign-key>
<foreign-key foreignTable="M_REQUISITIONLINE"
name="M_REQUISITIONORDER_M_REQUISITI">
<reference local="M_REQUISITIONLINE_ID"
foreign="M_REQUISITIONLINE_ID"/>
</foreign-key>
+ <index name="M_REQUISITIONORDER_ORDLINE_IDX" unique="false">
+ <index-column name="C_ORDERLINE_ID"/>
+ </index>
<index name="M_REQUISITIONORDER_REQLINE_IDX" unique="false">
<index-column name="M_REQUISITIONLINE_ID"/>
</index>
diff -r 3a3619678a3c -r cfa8ffeab1dd
src-db/database/model/triggers/C_ORDERLINE2_TRG.xml
--- a/src-db/database/model/triggers/C_ORDERLINE2_TRG.xml Tue May 06
10:08:33 2014 +0200
+++ b/src-db/database/model/triggers/C_ORDERLINE2_TRG.xml Tue Apr 29
10:19:32 2014 +0200
@@ -18,7 +18,7 @@
* under the License.
* The Original Code is Openbravo ERP.
* The Initial Developer of the Original Code is Openbravo SLU
-* All portions are Copyright (C) 2001-2012 Openbravo SLU
+* All portions are Copyright (C) 2001-2014 Openbravo SLU
* All Rights Reserved.
* Contributor(s): ______________________________________.
************************************************************************/
@@ -31,6 +31,7 @@
v_isSOTrx CHAR(1);
v_QtyReservedPO NUMBER;
v_QtyOrderReservedPO NUMBER;
+ v_count NUMBER;
BEGIN
@@ -128,6 +129,87 @@
END IF;
END IF;
END IF;
+
+ IF (UPDATING) THEN
+ -- Update Requisition ordered quantity when needed.
+ SELECT count(*) INTO v_count
+ FROM DUAL
+ WHERE EXISTS (SELECT 1 FROM m_requisitionorder WHERE c_orderline_id =
:old.c_orderline_id);
+ IF (v_count > 0 AND :old.qtyordered > :new.qtyordered) THEN
+ -- Quantity order decrease. Remove quantity from requisitions.
+ DECLARE
+ TYPE RECORD IS REF CURSOR;
+ cur_requisitions RECORD;
+
+ v_requisitionqty NUMBER;
+ v_qtypendingtoremove NUMBER;
+ v_qtytoremove NUMBER;
+ BEGIN
+ SELECT sum(qty) INTO v_requisitionqty
+ FROM m_requisitionorder
+ WHERE c_orderline_id = :OLD.c_orderline_id;
+ IF (v_requisitionqty > :NEW.qtyordered) THEN
+ v_qtypendingtoremove := v_requisitionqty - :new.qtyordered;
+ FOR cur_requisitions IN (
+ SELECT ro.m_requisitionorder_id, ro.qty
+ FROM m_requisitionorder ro
+ JOIN m_requisitionline rl ON ro.m_requisitionline_id =
rl.m_requisitionline_id
+ WHERE ro.c_orderline_id = :old.c_orderline_id
+ ORDER BY CASE rl.reqstatus WHEN 'O' THEN 0 ELSE 1 END,
rl.needbydate DESC, rl.created DESC
+ ) LOOP
+ v_qtytoremove := LEAST(v_qtypendingtoremove, cur_requisitions.qty);
+ -- Do not remove if quantity is set to zero to keep relation
+ UPDATE m_requisitionorder
+ SET qty = qty - v_qtytoremove
+ WHERE m_requisitionorder_id =
cur_requisitions.m_requisitionorder_id;
+ v_qtypendingtoremove := v_qtypendingtoremove - v_qtytoremove;
+ IF (v_qtypendingtoremove = 0) THEN
+ EXIT;
+ END IF;
+ END LOOP;
+ END IF;
+ END;
+ ELSIF (v_count > 0 AND :old.qtyordered < :new.qtyordered) THEN
+ -- Quantity order increase. Add quantity to requisitions.
+ DECLARE
+ TYPE RECORD IS REF CURSOR;
+ cur_requisitions RECORD;
+
+ v_requisitionpendingqty NUMBER;
+ v_qtypendingtoadd NUMBER;
+ v_qtytoadd NUMBER;
+ BEGIN
+ SELECT sum(rl.qty - rl.orderedqty) INTO v_requisitionpendingqty
+ FROM m_requisitionline rl
+ WHERE rl.m_requisitionline_id IN (SELECT ro.m_requisitionline_id
+ FROM m_requisitionorder ro
+ WHERE c_orderline_id =
:old.c_orderline_id)
+ AND rl.qty > rl.orderedqty;
+ IF (v_requisitionpendingqty > 0) THEN
+ v_qtypendingtoadd := LEAST(v_requisitionpendingqty, :new.qtyordered
- :old.qtyordered);
+ FOR cur_requisitions IN (
+ SELECT MAX(m_requisitionorder_id) AS m_requisitionorder_id, rl.qty
- rl.orderedqty AS pendingqty
+ FROM m_requisitionorder ro
+ JOIN m_requisitionline rl ON ro.m_requisitionline_id =
rl.m_requisitionline_id
+ WHERE ro.c_orderline_id = :old.c_orderline_id
+ AND rl.qty > rl.orderedqty
+ AND rl.reqstatus = 'O'
+ GROUP BY rl.m_requisitionline_id, rl.needbydate, rl.created,
rl.qty, rl.orderedqty, rl.reqstatus
+ ORDER BY rl.needbydate ASC, rl.created ASC
+ ) LOOP
+ v_qtytoadd := LEAST(v_qtypendingtoadd,
cur_requisitions.pendingqty);
+ UPDATE m_requisitionorder
+ SET qty = qty + v_qtytoadd
+ WHERE m_requisitionorder_id =
cur_requisitions.m_requisitionorder_id;
+ v_qtypendingtoadd := v_qtypendingtoadd - v_qtytoadd;
+ IF (v_qtypendingtoadd = 0) THEN
+ EXIT;
+ END IF;
+ END LOOP;
+ END IF;
+ END;
+ END IF;
+ END IF;
END C_ORDERLINE2_TRG
]]></body>
diff -r 3a3619678a3c -r cfa8ffeab1dd
src-db/database/model/triggers/C_ORDERLINE_TRG.xml
--- a/src-db/database/model/triggers/C_ORDERLINE_TRG.xml Tue May 06
10:08:33 2014 +0200
+++ b/src-db/database/model/triggers/C_ORDERLINE_TRG.xml Tue Apr 29
10:19:32 2014 +0200
@@ -17,7 +17,7 @@
* parts created by ComPiere are Copyright (C) ComPiere, Inc.;
* All Rights Reserved.
* Contributor(s): Openbravo SLU
- * Contributions are Copyright (C) 2001-2012 Openbravo, S.L.U.
+ * Contributions are Copyright (C) 2001-2014 Openbravo, S.L.U.
*
* Specifically, this derivative work is based upon the following Compiere
* file and version.
@@ -113,6 +113,12 @@
IF (v_count > 0) THEN
RAISE_APPLICATION_ERROR(-20000, '@20206@');
END IF;
+ SELECT count(*) INTO v_count
+ FROM m_requisitionorder
+ WHERE c_orderline_id = :old.c_orderline_id;
+ IF (v_count > 0) THEN
+ RAISE_APPLICATION_ERROR(-20000, '@ProductChangeLineInRequisition@');
+ END IF;
END IF;
END IF;
END IF;
diff -r 3a3619678a3c -r cfa8ffeab1dd
src-db/database/model/triggers/M_REQUISITIONLINE_TRG.xml
--- a/src-db/database/model/triggers/M_REQUISITIONLINE_TRG.xml Tue May 06
10:08:33 2014 +0200
+++ b/src-db/database/model/triggers/M_REQUISITIONLINE_TRG.xml Tue Apr 29
10:19:32 2014 +0200
@@ -14,7 +14,7 @@
* under the License.
* The Original Code is Openbravo ERP.
* The Initial Developer of the Original Code is Openbravo SLU
-* All portions are Copyright (C) 2008-2013 Openbravo SLU
+* All portions are Copyright (C) 2008-2014 Openbravo SLU
* All Rights Reserved.
* Contributor(s): ______________________________________.
************************************************************************/
@@ -40,7 +40,7 @@
WHERE M_Requisition_ID = :old.M_Requisition_ID;
END IF;
- IF (v_DocStatus = 'CL') THEN
+ IF (v_DocStatus = 'CL') THEN
RAISE_APPLICATION_ERROR(-20000, '@20527@');
END IF;
@@ -56,7 +56,8 @@
RAISE_APPLICATION_ERROR(-20000, '@20526@');
END IF;
- IF (v_ReqStatus <> 'O') THEN
+ IF (v_ReqStatus <> 'O'
+ AND NOT (UPDATING AND :new.reqstatus = 'O')) THEN
RAISE_APPLICATION_ERROR(-20000, '@20520@');
END IF;
diff -r 3a3619678a3c -r cfa8ffeab1dd
src-db/database/model/triggers/M_REQUISITIONORDER_TRG.xml
--- a/src-db/database/model/triggers/M_REQUISITIONORDER_TRG.xml Tue May 06
10:08:33 2014 +0200
+++ b/src-db/database/model/triggers/M_REQUISITIONORDER_TRG.xml Tue Apr 29
10:19:32 2014 +0200
@@ -15,7 +15,7 @@
* under the License.
* The Original Code is Openbravo ERP.
* The Initial Developer of the Original Code is Openbravo SLU
- * All portions are Copyright (C) 2008-2012 Openbravo SLU
+ * All portions are Copyright (C) 2008-2014 Openbravo SLU
* All Rights Reserved.
* Contributor(s): ______________________________________.
************************************************************************/
@@ -31,18 +31,18 @@
IF INSERTING THEN
- SELECT DocStatus INTO v_DocStatus
+ SELECT DocStatus, ReqStatus INTO v_DocStatus, v_reqstatus
FROM M_Requisition, M_RequisitionLine
WHERE M_REquisitionLine_ID = :new.M_RequisitionLine_ID
AND M_Requisition.M_Requisition_ID = M_RequisitionLine.M_Requisition_ID;
ELSE
- SELECT DocStatus INTO v_DocStatus
+ SELECT DocStatus, ReqStatus INTO v_DocStatus, v_reqstatus
FROM M_Requisition, M_RequisitionLine
WHERE M_REquisitionLine_ID = :old.M_RequisitionLine_ID
AND M_Requisition.M_Requisition_ID = M_RequisitionLine.M_Requisition_ID;
END IF;
-IF (INSERTING OR UPDATING) THEN
+IF (INSERTING) THEN
SELECT COUNT(*) INTO v_Count
FROM M_REQUISITIONLINE A, C_ORDERLINE B
WHERE A.M_REQUISITIONLINE_ID = :new.M_REQUISITIONLINE_ID
@@ -54,7 +54,7 @@
END IF;
IF ((v_DocStatus <> 'CO') OR (v_ReqStatus <> 'O')) THEN
- RAISE_APPLICATION_ERROR(-20000, '@20501@');
+ RAISE_APPLICATION_ERROR(-20000, '@RequisitionProcessed@');
END IF;
IF (UPDATING OR DELETING) THEN
diff -r 3a3619678a3c -r cfa8ffeab1dd
src-db/database/model/triggers/M_REQUISITION_TRG.xml
--- a/src-db/database/model/triggers/M_REQUISITION_TRG.xml Tue May 06
10:08:33 2014 +0200
+++ b/src-db/database/model/triggers/M_REQUISITION_TRG.xml Tue Apr 29
10:19:32 2014 +0200
@@ -14,11 +14,12 @@
* under the License.
* The Original Code is Openbravo ERP.
* The Initial Developer of the Original Code is Openbravo SLU
-* All portions are Copyright (C) 2008-2012 Openbravo SLU
+* All portions are Copyright (C) 2008-2014 Openbravo SLU
* All Rights Reserved.
* Contributor(s): ______________________________________.
************************************************************************/
- v_DocStatus VARCHAR(60);
+ v_OldDocStatus VARCHAR(60) := '-1';
+ v_NewDocStatus VARCHAR(60) := '-1';
BEGIN
@@ -26,18 +27,20 @@
END IF;
- IF INSERTING THEN
- v_DocStatus := :new.DocStatus;
- ELSE
- v_DocStatus := :old.DocStatus;
+ IF INSERTING OR UPDATING THEN
+ v_NewDocStatus := :new.DocStatus;
+ END IF;
+ IF UPDATING OR DELETING THEN
+ v_OldDocStatus := :old.DocStatus;
END IF;
- IF ((v_DocStatus = 'CL')
- OR ((v_DocStatus = 'CO') AND NOT UPDATING)) THEN
+ IF ((INSERTING AND v_NewDocStatus IN ('CL', 'CO'))
+ OR (UPDATING AND v_olddocstatus = 'CL' AND v_newdocstatus != 'CO')
+ OR (DELETING AND v_olddocstatus IN ('CL', 'CO')))THEN
RAISE_APPLICATION_ERROR(-20000, '@20501@');
END IF;
- IF (v_DocStatus = 'CO' AND UPDATING) THEN
+ IF (v_newDocStatus = 'CO' AND UPDATING) THEN
IF ((COALESCE(:old.DocumentNo, '.') <> COALESCE(:new.DocumentNo,'.'))
OR (COALESCE(:old.C_BPartner_ID, '0') <> COALESCE(:new.C_BPartner_ID,
'0'))
OR (COALESCE(:old.M_PriceList_ID, '0') <> COALESCE(:new.M_PriceList_ID,
'0'))
------------------------------------------------------------------------------
Is your legacy SCM system holding you back? Join Perforce May 7 to find out:
• 3 signs your SCM is hindering your productivity
• Requirements for releasing software faster
• Expert tips and advice for migrating your SCM now
http://p.sf.net/sfu/perforce
_______________________________________________
Openbravo-commits mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/openbravo-commits
