[Costadj] New triggers
authorEduardo Argal Guibert <eduardo.argal@openbravo.com>
Thu, 25 Sep 2014 19:56:04 +0200
changeset 24946 857829af6d0e
parent 24945 c02abc5cbbc1
child 24947 e4d61188bf13
[Costadj] New triggers

Adds inv update triggers. warehouse changed to NOT NULL. Fix in C_Invoice_Post to avoid duplicates in MatchInv
src-db/database/model/functions/C_INVOICE_POST.xml
src-db/database/model/tables/M_CA_INVENTORYAMTLINE.xml
src-db/database/model/triggers/M_CA_INVAMTLINE_INV_CHK_TRG.xml
src-db/database/model/triggers/M_CA_INVENTORYAMTLINE_CHK_TRG.xml
src-db/database/model/triggers/M_CA_INVENTORYAMT_CHK_TRG.xml
src-db/database/model/triggers/M_LC_MATCHED_CHK_TRG.xml
src-db/database/sourcedata/AD_COLUMN.xml
src/org/openbravo/costing/InventoryAmountUpdateProcess.java
--- a/src-db/database/model/functions/C_INVOICE_POST.xml	Thu Sep 25 19:21:25 2014 +0200
+++ b/src-db/database/model/functions/C_INVOICE_POST.xml	Thu Sep 25 19:56:04 2014 +0200
@@ -1439,8 +1439,10 @@
                ml.M_Product_ID, ml.MovementQty, il.QtyInvoiced, i.DateAcct
            FROM C_INVOICELINE il
                 INNER JOIN M_INOUTLINE ml ON (il.M_InOutLine_ID=ml.M_InOutLine_ID)
+                INNER JOIN M_INOUT m ON (ml.M_InOut_ID=m.M_InOut_ID)
                 INNER JOIN C_INVOICE i ON(il.C_Invoice_ID=i.C_Invoice_ID)
            WHERE il.M_Product_ID=ml.M_Product_ID
+             AND M.Processed = 'Y'
              AND il.C_Invoice_ID=v_Record_ID
            )
         LOOP
--- a/src-db/database/model/tables/M_CA_INVENTORYAMTLINE.xml	Thu Sep 25 19:21:25 2014 +0200
+++ b/src-db/database/model/tables/M_CA_INVENTORYAMTLINE.xml	Thu Sep 25 19:56:04 2014 +0200
@@ -41,7 +41,7 @@
         <default/>
         <onCreateDefault/>
       </column>
-      <column name="M_WAREHOUSE_ID" primaryKey="false" required="true" type="VARCHAR" size="32" autoIncrement="false">
+      <column name="M_WAREHOUSE_ID" primaryKey="false" required="false" type="VARCHAR" size="32" autoIncrement="false">
         <default/>
         <onCreateDefault/>
       </column>
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src-db/database/model/triggers/M_CA_INVAMTLINE_INV_CHK_TRG.xml	Thu Sep 25 19:56:04 2014 +0200
@@ -0,0 +1,58 @@
+<?xml version="1.0"?>
+  <database name="TRIGGER M_CA_INVAMTLINE_INV_CHK_TRG">
+    <trigger name="M_CA_INVAMTLINE_INV_CHK_TRG" table="M_CA_INVENTORYAMTLINE_INV" fires="before" insert="true" update="true" delete="true" foreach="row">
+      <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) 2014 Openbravo SLU
+    * All Rights Reserved.
+    * Contributor(s):  ______________________________________.
+    ************************************************************************/
+  v_Processed VARCHAR(60) ;
+  v_M_CA_Inventoryamtline_ID VARCHAR2(32) ;
+        
+BEGIN
+    
+    IF AD_isTriggerEnabled()='N' THEN RETURN;
+    END IF;
+
+  IF INSERTING THEN
+    v_M_CA_Inventoryamtline_ID:=:new.M_CA_Inventoryamtline_ID;
+  ELSE
+    v_M_CA_Inventoryamtline_ID:=:old.M_CA_Inventoryamtline_ID;
+  END IF;
+  SELECT PROCESSED INTO v_Processed FROM M_CA_Inventoryamt, M_CA_Inventoryamtline WHERE M_CA_Inventoryamt.M_CA_Inventoryamt_ID = M_CA_Inventoryamtline.M_CA_Inventoryamt_ID AND M_CA_Inventoryamtline_ID=v_M_CA_Inventoryamtline_ID;
+    IF UPDATING THEN  
+      IF(v_Processed='Y'
+        AND ((COALESCE(:old.m_ca_Inventoryamtline_id, '0') <> COALESCE(:new.m_ca_Inventoryamtline_id, '0'))
+        OR(COALESCE(:old.m_warehouse_id, '0') <> COALESCE(:new.m_warehouse_id, '0'))
+        OR(COALESCE(:old.close_inventory_id, '0') <> COALESCE(:new.close_inventory_id, '0'))
+        OR(COALESCE(:old.init_inventory_id, '0') <> COALESCE(:new.init_inventory_id, '0'))
+        OR(COALESCE(:old.AD_ORG_ID, '0') <> COALESCE(:new.AD_ORG_ID, '0'))
+        OR(COALESCE(:old.AD_CLIENT_ID, '0') <> COALESCE(:new.AD_CLIENT_ID, '0'))))
+        THEN  RAISE_APPLICATION_ERROR(-20000, '@20501@') ;
+      END IF;
+    END IF;
+    IF(DELETING) THEN
+      IF(v_Processed='Y') THEN
+        RAISE_APPLICATION_ERROR(-20000, '@20501@') ;
+      END IF;
+    END IF;
+    IF(INSERTING) THEN
+      IF(v_Processed='Y') THEN
+        RAISE_APPLICATION_ERROR(-20000, '@20501@') ;
+      END IF;
+    END IF;
+    END M_CA_INVAMTLINE_INV_CHK_TRG
+]]></body>
+    </trigger>
+  </database>
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src-db/database/model/triggers/M_CA_INVENTORYAMTLINE_CHK_TRG.xml	Thu Sep 25 19:56:04 2014 +0200
@@ -0,0 +1,64 @@
+<?xml version="1.0"?>
+  <database name="TRIGGER M_CA_INVENTORYAMTLINE_CHK_TRG">
+    <trigger name="M_CA_INVENTORYAMTLINE_CHK_TRG" table="M_CA_INVENTORYAMTLINE" fires="before" insert="true" update="true" delete="true" foreach="row">
+      <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) 2014 Openbravo SLU
+    * All Rights Reserved.
+    * Contributor(s):  ______________________________________.
+    ************************************************************************/
+  v_Processed VARCHAR(60) ;
+  v_M_CA_INVENTORYAMT_ID VARCHAR2(32) ;
+  v_DateNull DATE := TO_DATE('01-01-1900', 'DD-MM-YYYY');
+        
+BEGIN
+    
+    IF AD_isTriggerEnabled()='N' THEN RETURN;
+    END IF;
+
+  IF INSERTING THEN
+    v_M_CA_INVENTORYAMT_ID:=:new.M_CA_INVENTORYAMT_ID;
+  ELSE
+    v_M_CA_INVENTORYAMT_ID:=:old.M_CA_INVENTORYAMT_ID;
+  END IF;
+  SELECT PROCESSED INTO v_Processed FROM M_CA_INVENTORYAMT WHERE M_CA_INVENTORYAMT_ID=v_M_CA_INVENTORYAMT_ID;
+    IF UPDATING THEN  
+      IF(v_Processed='Y'
+        AND ((COALESCE(:old.m_ca_inventoryamt_id, '0') <> COALESCE(:new.m_ca_inventoryamt_id, '0'))
+        OR (COALESCE(:old.referencedate, v_DateNull) <> COALESCE(:new.referencedate, v_DateNull))
+        OR(COALESCE(:old.m_product_id, '0') <> COALESCE(:new.m_product_id, '0'))
+        OR(COALESCE(:old.m_warehouse_id, '0') <> COALESCE(:new.m_warehouse_id, '0'))
+        OR(COALESCE(:old.inventory_amount, 0) <> COALESCE(:new.inventory_amount, 0))
+        OR(COALESCE(:old.cur_inventory_amount, 0) <> COALESCE(:new.cur_inventory_amount, 0))
+        OR(COALESCE(:old.onhandqty, 0) <> COALESCE(:new.onhandqty, 0))
+        OR(COALESCE(:old.unitcost, 0) <> COALESCE(:new.unitcost, 0))
+        OR(COALESCE(:old.cur_unitcost, 0) <> COALESCE(:new.cur_unitcost, 0))
+        OR(COALESCE(:old.AD_ORG_ID, '0') <> COALESCE(:new.AD_ORG_ID, '0'))
+        OR(COALESCE(:old.AD_CLIENT_ID, '0') <> COALESCE(:new.AD_CLIENT_ID, '0'))))
+        THEN  RAISE_APPLICATION_ERROR(-20000, '@20501@') ;
+      END IF;
+    END IF;
+    IF(DELETING) THEN
+      IF(v_Processed='Y') THEN
+        RAISE_APPLICATION_ERROR(-20000, '@20501@') ;
+      END IF;
+    END IF;
+    IF(INSERTING) THEN
+      IF(v_Processed='Y') THEN
+        RAISE_APPLICATION_ERROR(-20000, '@20501@') ;
+      END IF;
+    END IF;
+    END M_CA_INVENTORYAMTLINE_CHK_TRG
+]]></body>
+    </trigger>
+  </database>
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src-db/database/model/triggers/M_CA_INVENTORYAMT_CHK_TRG.xml	Thu Sep 25 19:56:04 2014 +0200
@@ -0,0 +1,51 @@
+<?xml version="1.0"?>
+  <database name="TRIGGER M_CA_INVENTORYAMT_CHK_TRG">
+    <trigger name="M_CA_INVENTORYAMT_CHK_TRG" table="M_CA_INVENTORYAMT" fires="before" insert="true" update="true" delete="true" foreach="row">
+      <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) 2014 Openbravo SLU
+    * All Rights Reserved.
+    * Contributor(s):  ______________________________________.
+    ************************************************************************/
+    v_DateNull DATE := TO_DATE('01-01-1900', 'DD-MM-YYYY');
+        
+BEGIN
+    
+    IF AD_isTriggerEnabled()='N' THEN RETURN;
+    END IF;
+
+
+    IF UPDATING THEN  
+      IF(:old.Processed='Y'
+        AND ((COALESCE(:old.documentdate, v_DateNull) <> COALESCE(:new.documentdate, v_DateNull))
+        OR(COALESCE(:old.c_doctype_id, '0') <> COALESCE(:new.c_doctype_id, '0'))
+        OR(COALESCE(:old.documentno, '0') <> COALESCE(:new.documentno, '0'))
+        OR(COALESCE(:old.AD_ORG_ID, '0') <> COALESCE(:new.AD_ORG_ID, '0'))
+        OR(COALESCE(:old.AD_CLIENT_ID, '0') <> COALESCE(:new.AD_CLIENT_ID, '0'))))
+        THEN  RAISE_APPLICATION_ERROR(-20000, '@20501@') ;
+      END IF;
+    END IF;
+    IF(DELETING) THEN
+      IF(:old.Processed='Y') THEN
+        RAISE_APPLICATION_ERROR(-20000, '@20501@') ;
+      END IF;
+    END IF;
+    IF(INSERTING) THEN
+      IF(:NEW.Processed='Y') THEN
+        RAISE_APPLICATION_ERROR(-20000, '@20501@') ;
+      END IF;
+    END IF;
+    END M_CA_INVENTORYAMT_CHK_TRG
+]]></body>
+    </trigger>
+  </database>
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src-db/database/model/triggers/M_LC_MATCHED_CHK_TRG.xml	Thu Sep 25 19:56:04 2014 +0200
@@ -0,0 +1,69 @@
+<?xml version="1.0"?>
+  <database name="TRIGGER M_LC_MATCHED_CHK_TRG">
+    <trigger name="M_LC_MATCHED_CHK_TRG" table="M_LC_MATCHED" fires="before" insert="true" update="true" delete="true" foreach="row">
+      <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) 2014 Openbravo SLU
+    * All Rights Reserved.
+    * Contributor(s):  ______________________________________.
+    ************************************************************************/
+  v_Processed VARCHAR(60) ;
+  v_M_LC_Cost_ID VARCHAR2(32) ;
+  v_Posted VARCHAR(60) ;
+        
+BEGIN
+    
+    IF AD_isTriggerEnabled()='N' THEN RETURN;
+    END IF;
+
+  IF INSERTING THEN
+    v_M_LC_Cost_ID:=:new.M_LC_Cost_ID;
+  ELSE
+    v_M_LC_Cost_ID:=:old.M_LC_Cost_ID;
+  END IF;
+  SELECT PROCESSED, POSTED INTO v_Processed, v_Posted FROM M_LC_Cost_ID WHERE M_LC_Cost_ID=v_M_LC_Cost_ID;
+    IF UPDATING THEN  
+      IF(v_Processed='Y'
+        AND ((COALESCE(:old.m_lc_cost_id, '0') <> COALESCE(:new.m_lc_cost_id, '0'))
+        OR(COALESCE(:old.c_invoiceline_id, '0') <> COALESCE(:new.c_invoiceline_id, '0'))
+        OR(COALESCE(:old.amount, 0) <> COALESCE(:new.amount, 0))
+        OR(COALESCE(:old.amt_invoice_currency, 0) <> COALESCE(:new.amt_invoice_currency, 0))
+        OR(COALESCE(:old.AD_ORG_ID, '0') <> COALESCE(:new.AD_ORG_ID, '0'))
+        OR(COALESCE(:old.AD_CLIENT_ID, '0') <> COALESCE(:new.AD_CLIENT_ID, '0'))))
+        THEN  RAISE_APPLICATION_ERROR(-20000, '@20501@') ;
+      END IF;
+    
+      IF(v_Posted='Y' 
+        AND ((COALESCE(:old.m_lc_cost_id, '0') <> COALESCE(:new.m_lc_cost_id, '0'))
+        OR(COALESCE(:old.c_invoiceline_id, '0') <> COALESCE(:new.c_invoiceline_id, '0'))
+        OR(COALESCE(:old.amount, 0) <> COALESCE(:new.amount, 0))
+        OR(COALESCE(:old.amt_invoice_currency, 0) <> COALESCE(:new.amt_invoice_currency, 0))
+        OR(COALESCE(:old.AD_ORG_ID, '0') <> COALESCE(:new.AD_ORG_ID, '0'))
+        OR(COALESCE(:old.AD_CLIENT_ID, '0') <> COALESCE(:new.AD_CLIENT_ID, '0'))))
+        THEN RAISE_APPLICATION_ERROR(-20000, '@20501@') ;
+      END IF;    
+    END IF;
+    IF(DELETING) THEN
+      IF(v_Processed='Y') THEN
+        RAISE_APPLICATION_ERROR(-20000, '@20501@') ;
+      END IF;
+    END IF;
+    IF(INSERTING) THEN
+      IF(v_Processed='Y') THEN
+        RAISE_APPLICATION_ERROR(-20000, '@20501@') ;
+      END IF;
+    END IF;
+    END M_LC_MATCHED_CHK_TRG
+]]></body>
+    </trigger>
+  </database>
--- a/src-db/database/sourcedata/AD_COLUMN.xml	Thu Sep 25 19:21:25 2014 +0200
+++ b/src-db/database/sourcedata/AD_COLUMN.xml	Thu Sep 25 19:56:04 2014 +0200
@@ -232274,9 +232274,9 @@
 <!--24E857B14059424FA2F311C9F3AB7E8F-->  <FIELDLENGTH><![CDATA[32]]></FIELDLENGTH>
 <!--24E857B14059424FA2F311C9F3AB7E8F-->  <ISKEY><![CDATA[N]]></ISKEY>
 <!--24E857B14059424FA2F311C9F3AB7E8F-->  <ISPARENT><![CDATA[N]]></ISPARENT>
-<!--24E857B14059424FA2F311C9F3AB7E8F-->  <ISMANDATORY><![CDATA[Y]]></ISMANDATORY>
+<!--24E857B14059424FA2F311C9F3AB7E8F-->  <ISMANDATORY><![CDATA[N]]></ISMANDATORY>
 <!--24E857B14059424FA2F311C9F3AB7E8F-->  <ISUPDATEABLE><![CDATA[Y]]></ISUPDATEABLE>
-<!--24E857B14059424FA2F311C9F3AB7E8F-->  <READONLYLOGIC><![CDATA[@Processed@='Y']]></READONLYLOGIC>
+<!--24E857B14059424FA2F311C9F3AB7E8F-->  <READONLYLOGIC><![CDATA[@Processed@='Y' | @isWarehouseDimension@ = 'N']]></READONLYLOGIC>
 <!--24E857B14059424FA2F311C9F3AB7E8F-->  <ISIDENTIFIER><![CDATA[Y]]></ISIDENTIFIER>
 <!--24E857B14059424FA2F311C9F3AB7E8F-->  <SEQNO><![CDATA[110]]></SEQNO>
 <!--24E857B14059424FA2F311C9F3AB7E8F-->  <ISTRANSLATED><![CDATA[N]]></ISTRANSLATED>
--- a/src/org/openbravo/costing/InventoryAmountUpdateProcess.java	Thu Sep 25 19:21:25 2014 +0200
+++ b/src/org/openbravo/costing/InventoryAmountUpdateProcess.java	Thu Sep 25 19:56:04 2014 +0200
@@ -76,9 +76,6 @@
       String invAmtUpdId = jsonData.getString("M_Ca_Inventoryamt_ID");
       InventoryAmountUpdate invAmtUpd = OBDal.getInstance().get(InventoryAmountUpdate.class,
           invAmtUpdId);
-      invAmtUpd.setProcessed(true);
-      OBDal.getInstance().save(invAmtUpd);
-      OBDal.getInstance().flush();
       final OBCriteria<InventoryAmountUpdateLine> qLines = OBDal.getInstance().createCriteria(
           InventoryAmountUpdateLine.class);
       qLines.add(Restrictions.eq(InventoryAmountUpdateLine.PROPERTY_CAINVENTORYAMT, invAmtUpd));
@@ -108,8 +105,11 @@
             // is big
             OBDal.getInstance().getSession().clear();
           }
-
         }
+        invAmtUpd = OBDal.getInstance().get(InventoryAmountUpdate.class, invAmtUpdId);
+        invAmtUpd.setProcessed(true);
+        OBDal.getInstance().save(invAmtUpd);
+        OBDal.getInstance().flush();
 
         try {
           Thread.sleep(2000);
@@ -321,7 +321,9 @@
     if (date != null) {
       stockLinesQry.setTimestamp("date", date);
     }
-    stockLinesQry.setParameter("warehouse", warehouse);
+    if (warehouse != null) {
+      stockLinesQry.setParameter("warehouse", warehouse);
+    }
     stockLinesQry.setParameter("product", product);
     stockLinesQry.setFetchSize(1000);
     ScrollableResults stockLines = stockLinesQry.scroll(ScrollMode.FORWARD_ONLY);