0014035: fact_acct_reset pl-function is too slow

comparing with https://huehner@code.openbravo.com/erp/devel/pi
searching for changes
changeset:   7907:3439f3e9fe2d
tag:         tip
user:        Stefan Hühner <stefan.huehner@openbravo.com>
date:        Thu Jul 22 16:05:00 2010 +0200
files:       src-db/database/model/functions/FACT_ACCT_RESET.xml src-db/database/model/tables/C_PERIODCONTROL.xml
description:
Fixed 14035: fact_acct_reset is too slow
Speed up this pl-function by:
- add missing index to c_periodcontrol table
- move one function call out of a complex query as it does not depend on any
  data from the query, instead execute the function onces before the bigger
  query.


diff --git a/src-db/database/model/functions/FACT_ACCT_RESET.xml b/src-db/database/model/functions/FACT_ACCT_RESET.xml
--- a/src-db/database/model/functions/FACT_ACCT_RESET.xml
+++ b/src-db/database/model/functions/FACT_ACCT_RESET.xml
@@ -41,6 +41,7 @@
   v_DateColumn VARCHAR2(2000):='';
   v_AD_Org_ID VARCHAR2(32);
   v_TableName VARCHAR2(48):='';
+  v_AD_Org_CalOwner VARCHAR(32);
   -- Parameter
   TYPE RECORD IS REF CURSOR;
     Cur_Parameter RECORD;
@@ -207,6 +208,7 @@
           -- Update Table
           v_ResultStr:='ResetTable:' || Cur_Tables.TableName;
            IF(v_DeletePosting='Y') THEN
+            v_AD_Org_CalOwner := AD_ORG_GETCALENDAROWNER(v_AD_Org_ID);
             FOR Cur_Fact_Acct IN (
               SELECT DISTINCT Record_ID
               FROM FACT_ACCT
@@ -226,7 +228,7 @@
                       AND fa_aux.AD_TABLE_ID = FACT_ACCT.AD_TABLE_ID
                       AND fa_aux.RECORD_ID = FACT_ACCT.RECORD_ID
                       AND C_PeriodControl.AD_Org_ID=AD_Org.AD_Org_ID
-                      AND C_PeriodControl.AD_Org_ID=AD_ORG_GETCALENDAROWNER(v_AD_Org_ID)
+                      AND C_PeriodControl.AD_Org_ID=v_AD_Org_CalOwner
                     )
                 AND Record_ID=(
                       CASE COALESCE(v_Record_ID, '0') WHEN '0' THEN Record_ID ELSE v_Record_ID
diff --git a/src-db/database/model/tables/C_PERIODCONTROL.xml b/src-db/database/model/tables/C_PERIODCONTROL.xml
--- a/src-db/database/model/tables/C_PERIODCONTROL.xml
+++ b/src-db/database/model/tables/C_PERIODCONTROL.xml
@@ -62,6 +62,9 @@
       <foreign-key foreignTable="AD_ORG" name="C_PERIODCONTROL_AD_ORG">
         <reference local="AD_ORG_ID" foreign="AD_ORG_ID"/>
       </foreign-key>
+      <index name="C_PERIODCONTROL_PERIOD" unique="false">
+        <index-column name="C_PERIOD_ID"/>
+      </index>
       <check name="C_PERIODCONTROL_ISACTIVE_CHECK"><![CDATA[ISACTIVE IN ('Y', 'N')]]></check>
     </table>
   </database>