fixed bug 19581: Implement incremental Average Cost generation
authorAsier Lostalé <asier.lostale@openbravo.com>
Fri, 27 Jan 2012 14:16:41 +0100
changeset 15529 d3a7d777931e
parent 15528 f5a68090da7c
child 15530 c691ab2b4518
fixed bug 19581: Implement incremental Average Cost generation
src-db/database/model/functions/M_GENERATE_AVERAGE_COSTS.xml
src-db/database/sourcedata/AD_ELEMENT.xml
src-db/database/sourcedata/AD_MESSAGE.xml
src-db/database/sourcedata/AD_PROCESS_PARA.xml
--- a/src-db/database/model/functions/M_GENERATE_AVERAGE_COSTS.xml	Thu Feb 16 16:59:53 2012 +0100
+++ b/src-db/database/model/functions/M_GENERATE_AVERAGE_COSTS.xml	Fri Jan 27 14:16:41 2012 +0100
@@ -16,7 +16,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-2009 Openbravo SLU
+* All portions are Copyright (C) 2001-2012 Openbravo SLU
 * All Rights Reserved.
 * Contributor(s):  ______________________________________.
 ************************************************************************/
@@ -44,6 +44,8 @@
   v_PriceNew NUMBER;
   v_Client VARCHAR2(32);
   v_BaseCurrency VARCHAR2(32);
+  v_Incremental BOOLEAN := FALSE;
+  v_doNothing BOOLEAN := FALSE;
   -- Parameter
   TYPE RECORD IS REF CURSOR;
     Cur_Parameter RECORD;
@@ -85,6 +87,9 @@
       v_Record_ID:=Cur_Parameter.Record_ID;
       v_User:=Cur_Parameter.AD_User_ID;
       v_Client:=Cur_Parameter.AD_Client_ID;
+      IF (Cur_Parameter.ParameterName = 'IsIncremental') THEN
+        v_Incremental := COALESCE(Cur_Parameter.P_String, 'N')='Y';
+      END IF;
     END LOOP; --  Get Parameter
     SELECT AD_CLIENT.C_CURRENCY_ID 
       INTO v_BaseCurrency
@@ -94,16 +99,43 @@
     -- BEGIN CHECK IF COST USED TO POST A DOCUMENT
     -- If a document has been posted, and in the entry the costing has been used to calculate any amount,
     -- process is not allowed to be launched.
-    SELECT MIN(DATEFROM)
-    INTO v_MinDateCosting
-    FROM M_COSTING
-    WHERE ISPERMANENT='N'
-      AND ISMANUAL='N'
-      AND COSTTYPE='AV'
-      AND ISPRODUCTION = 'N'
-      AND AD_CLIENT_ID = v_Client;
+
+    IF (v_Incremental) THEN
+      -- In case of incremental computation, calculate since first m_inOut.MovementDate from the ones
+      -- not included yet in any costing
+      SELECT MIN(M_INOUT.MOVEMENTDATE)
+      INTO v_MinDateCosting
+      FROM M_INOUT, M_INOUTLINE, M_PRODUCT
+      WHERE M_INOUT.M_INOUT_ID=M_INOUTLINE.M_INOUT_ID
+        AND M_INOUTLINE.M_PRODUCT_ID=M_PRODUCT.M_PRODUCT_ID
+        AND M_PRODUCT.COSTTYPE='AV'
+        AND M_INOUT.ISSOTRX='N'
+        AND M_INOUT.PROCESSED='Y'
+        AND M_INOUTLINE.MOVEMENTQTY>0
+        AND M_INOUT.DOCSTATUS <> 'DR' AND M_INOUT.DOCSTATUS <> 'RE' AND M_INOUT.DOCSTATUS <> 'VO'
+        AND M_INOUT.AD_CLIENT_ID = v_Client
+        AND NOT EXISTS
+           (SELECT 1 FROM M_COSTING WHERE M_INOUTLINE_ID=M_INOUTLINE.M_INOUTLINE_ID);
+       IF (v_MinDateCosting IS NOT NULL) THEN
+         v_MinDateCosting := TRUNC(v_MinDateCosting);
+       ELSE
+         -- There is no M_InOut with lines without costing, nothing to do
+         v_Message := '@NoNewCostingGenerationNeeded@';
+         v_doNothing := TRUE;
+       END IF;
+    ELSE
+      SELECT MIN(DATEFROM)
+      INTO v_MinDateCosting
+      FROM M_COSTING
+      WHERE ISPERMANENT='N'
+        AND ISMANUAL='N'
+        AND COSTTYPE='AV'
+        AND ISPRODUCTION = 'N'
+        AND AD_CLIENT_ID = v_Client;
+    END IF;
 
     v_CheckResultStr := '';
+   IF (NOT v_doNothing) THEN
     SELECT MAX(MOVEMENTDATE) INTO v_Date FROM M_MOVEMENT WHERE AD_CLIENT_ID=v_Client AND POSTED='Y';
     IF (v_MinDateCosting <= v_Date) THEN
       v_FirstIteration:=TRUE;
@@ -127,7 +159,7 @@
       END LOOP;
       v_CheckResultStr:=v_CheckResultStr || '.';
     END IF;
-
+   
     SELECT MAX(MOVEMENTDATE) INTO v_Date FROM M_INVENTORY WHERE AD_CLIENT_ID=v_Client AND POSTED='Y';
     IF (v_MinDateCosting <= v_Date) THEN
       v_FirstIteration:=TRUE;
@@ -230,24 +262,27 @@
       END LOOP;
       v_CheckResultStr:=v_CheckResultStr || '.';
     END IF;
-
+  
     IF (COALESCE(length(v_CheckResultStr),0) > 0) THEN
       RAISE_APPLICATION_ERROR(-20000, v_CheckResultStr);
     END IF;
     -- END CHECK IF COST USED TO POST A DOCUMENT
 
-    DELETE
-    FROM M_COSTING
-    WHERE ISPERMANENT='N'
-      AND ISMANUAL='N'
-      AND COSTTYPE='AV'
-      AND ISPRODUCTION = 'N'
-      AND AD_CLIENT_ID = v_Client;
-    v_rowcount:=SQL%ROWCOUNT;
-    v_Deleted:=v_Deleted + v_rowcount;
-    v_rowcount:=SQL%ROWCOUNT;
-    DBMS_OUTPUT.PUT_LINE('  deleted=' || v_rowcount) ;
-    --RECALCULATE THE DATETO OF MANUAL COSTS
+    IF (NOT v_Incremental) THEN
+      DELETE
+      FROM M_COSTING
+      WHERE ISPERMANENT='N'
+        AND ISMANUAL='N'
+        AND COSTTYPE='AV'
+        AND ISPRODUCTION = 'N'
+        AND AD_CLIENT_ID = v_Client;
+      v_rowcount:=SQL%ROWCOUNT;
+      v_Deleted:=v_Deleted + v_rowcount;
+      v_rowcount:=SQL%ROWCOUNT;
+      DBMS_OUTPUT.PUT_LINE('  deleted=' || v_rowcount) ;
+    END IF;
+
+    --RECALCULATE THE DATETO OF ALREADY CALCULATED COSTS
     FOR Cur_ManualCost IN
       (SELECT M_COSTING_ID, M_PRODUCT_ID, DATEFROM
        FROM M_COSTING
@@ -441,6 +476,7 @@
       v_DateOld:=Cur_InOutLine.MOVEMENTDATE;
       v_ProductOld:=Cur_InOutLine.M_PRODUCT_ID;
     END LOOP;
+   END IF;  
     --<<FINISH_PROCESS>>
     --  Update AD_PInstance
     DBMS_OUTPUT.PUT_LINE('Updating PInstance - Finished ' || v_Message);
--- a/src-db/database/sourcedata/AD_ELEMENT.xml	Thu Feb 16 16:59:53 2012 +0100
+++ b/src-db/database/sourcedata/AD_ELEMENT.xml	Fri Jan 27 14:16:41 2012 +0100
@@ -21009,6 +21009,20 @@
 <!--2E25DF9EA05D415B851BDB301BA12D0A-->  <ISGLOSSARY><![CDATA[N]]></ISGLOSSARY>
 <!--2E25DF9EA05D415B851BDB301BA12D0A--></AD_ELEMENT>
 
+<!--2E2C9A366AD843778A3FE4A6360A16DC--><AD_ELEMENT>
+<!--2E2C9A366AD843778A3FE4A6360A16DC-->  <AD_ELEMENT_ID><![CDATA[2E2C9A366AD843778A3FE4A6360A16DC]]></AD_ELEMENT_ID>
+<!--2E2C9A366AD843778A3FE4A6360A16DC-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
+<!--2E2C9A366AD843778A3FE4A6360A16DC-->  <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID>
+<!--2E2C9A366AD843778A3FE4A6360A16DC-->  <ISACTIVE><![CDATA[Y]]></ISACTIVE>
+<!--2E2C9A366AD843778A3FE4A6360A16DC-->  <COLUMNNAME><![CDATA[IsIncremental]]></COLUMNNAME>
+<!--2E2C9A366AD843778A3FE4A6360A16DC-->  <NAME><![CDATA[Incremental]]></NAME>
+<!--2E2C9A366AD843778A3FE4A6360A16DC-->  <PRINTNAME><![CDATA[Incremental]]></PRINTNAME>
+<!--2E2C9A366AD843778A3FE4A6360A16DC-->  <DESCRIPTION><![CDATA[Calculate Average Costs incrementally]]></DESCRIPTION>
+<!--2E2C9A366AD843778A3FE4A6360A16DC-->  <HELP><![CDATA[When this value is not checked, already calculated costs are removed and recalculated again. If checked, existent costs are preserved and new ones are added.]]></HELP>
+<!--2E2C9A366AD843778A3FE4A6360A16DC-->  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
+<!--2E2C9A366AD843778A3FE4A6360A16DC-->  <ISGLOSSARY><![CDATA[N]]></ISGLOSSARY>
+<!--2E2C9A366AD843778A3FE4A6360A16DC--></AD_ELEMENT>
+
 <!--2EB6010EAA174169880C588220EFE7B8--><AD_ELEMENT>
 <!--2EB6010EAA174169880C588220EFE7B8-->  <AD_ELEMENT_ID><![CDATA[2EB6010EAA174169880C588220EFE7B8]]></AD_ELEMENT_ID>
 <!--2EB6010EAA174169880C588220EFE7B8-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
--- a/src-db/database/sourcedata/AD_MESSAGE.xml	Thu Feb 16 16:59:53 2012 +0100
+++ b/src-db/database/sourcedata/AD_MESSAGE.xml	Fri Jan 27 14:16:41 2012 +0100
@@ -16060,6 +16060,17 @@
 <!--45FB8C27317D441CAFD96F2EE0005EA1-->  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
 <!--45FB8C27317D441CAFD96F2EE0005EA1--></AD_MESSAGE>
 
+<!--466602FE48454BF588E9458DFB75CB16--><AD_MESSAGE>
+<!--466602FE48454BF588E9458DFB75CB16-->  <AD_MESSAGE_ID><![CDATA[466602FE48454BF588E9458DFB75CB16]]></AD_MESSAGE_ID>
+<!--466602FE48454BF588E9458DFB75CB16-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
+<!--466602FE48454BF588E9458DFB75CB16-->  <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID>
+<!--466602FE48454BF588E9458DFB75CB16-->  <ISACTIVE><![CDATA[Y]]></ISACTIVE>
+<!--466602FE48454BF588E9458DFB75CB16-->  <VALUE><![CDATA[NoNewCostingGenerationNeeded]]></VALUE>
+<!--466602FE48454BF588E9458DFB75CB16-->  <MSGTEXT><![CDATA[There is no new incremental costing to generate since last calculated costs.]]></MSGTEXT>
+<!--466602FE48454BF588E9458DFB75CB16-->  <MSGTYPE><![CDATA[I]]></MSGTYPE>
+<!--466602FE48454BF588E9458DFB75CB16-->  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
+<!--466602FE48454BF588E9458DFB75CB16--></AD_MESSAGE>
+
 <!--467F2D9DFA8E4254B205D872DC75193C--><AD_MESSAGE>
 <!--467F2D9DFA8E4254B205D872DC75193C-->  <AD_MESSAGE_ID><![CDATA[467F2D9DFA8E4254B205D872DC75193C]]></AD_MESSAGE_ID>
 <!--467F2D9DFA8E4254B205D872DC75193C-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
--- a/src-db/database/sourcedata/AD_PROCESS_PARA.xml	Thu Feb 16 16:59:53 2012 +0100
+++ b/src-db/database/sourcedata/AD_PROCESS_PARA.xml	Fri Jan 27 14:16:41 2012 +0100
@@ -3959,6 +3959,26 @@
 <!--0324F423A8F74D9A9B91F7FF0DBD25CF-->  <AD_ELEMENT_ID><![CDATA[1143]]></AD_ELEMENT_ID>
 <!--0324F423A8F74D9A9B91F7FF0DBD25CF--></AD_PROCESS_PARA>
 
+<!--04E33DC0F96740A799476B208528157F--><AD_PROCESS_PARA>
+<!--04E33DC0F96740A799476B208528157F-->  <AD_PROCESS_PARA_ID><![CDATA[04E33DC0F96740A799476B208528157F]]></AD_PROCESS_PARA_ID>
+<!--04E33DC0F96740A799476B208528157F-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
+<!--04E33DC0F96740A799476B208528157F-->  <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID>
+<!--04E33DC0F96740A799476B208528157F-->  <ISACTIVE><![CDATA[Y]]></ISACTIVE>
+<!--04E33DC0F96740A799476B208528157F-->  <NAME><![CDATA[Incremental]]></NAME>
+<!--04E33DC0F96740A799476B208528157F-->  <DESCRIPTION><![CDATA[Calculate Average Costs incrementally]]></DESCRIPTION>
+<!--04E33DC0F96740A799476B208528157F-->  <HELP><![CDATA[When this value is not checked, already calculated costs are removed and recalculated again. If checked, existent costs are preserved and new ones are added.]]></HELP>
+<!--04E33DC0F96740A799476B208528157F-->  <AD_PROCESS_ID><![CDATA[800085]]></AD_PROCESS_ID>
+<!--04E33DC0F96740A799476B208528157F-->  <SEQNO><![CDATA[10]]></SEQNO>
+<!--04E33DC0F96740A799476B208528157F-->  <AD_REFERENCE_ID><![CDATA[20]]></AD_REFERENCE_ID>
+<!--04E33DC0F96740A799476B208528157F-->  <COLUMNNAME><![CDATA[IsIncremental]]></COLUMNNAME>
+<!--04E33DC0F96740A799476B208528157F-->  <ISCENTRALLYMAINTAINED><![CDATA[Y]]></ISCENTRALLYMAINTAINED>
+<!--04E33DC0F96740A799476B208528157F-->  <FIELDLENGTH><![CDATA[10]]></FIELDLENGTH>
+<!--04E33DC0F96740A799476B208528157F-->  <ISMANDATORY><![CDATA[Y]]></ISMANDATORY>
+<!--04E33DC0F96740A799476B208528157F-->  <ISRANGE><![CDATA[N]]></ISRANGE>
+<!--04E33DC0F96740A799476B208528157F-->  <DEFAULTVALUE><![CDATA[N]]></DEFAULTVALUE>
+<!--04E33DC0F96740A799476B208528157F-->  <AD_ELEMENT_ID><![CDATA[2E2C9A366AD843778A3FE4A6360A16DC]]></AD_ELEMENT_ID>
+<!--04E33DC0F96740A799476B208528157F--></AD_PROCESS_PARA>
+
 <!--0A83F9207E2A49C99E275A6EE7340989--><AD_PROCESS_PARA>
 <!--0A83F9207E2A49C99E275A6EE7340989-->  <AD_PROCESS_PARA_ID><![CDATA[0A83F9207E2A49C99E275A6EE7340989]]></AD_PROCESS_PARA_ID>
 <!--0A83F9207E2A49C99E275A6EE7340989-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>