details: http://code.openbravo.com/erp/devel/pi/rev/3cb40e9246fc changeset: 4313:3cb40e9246fc user: Vinoth Babu <vinoth.babu <at> openbravo.com> date: Sat Jul 11 12:24:59 2009 +0530 summary: The new feature implementation \"Defining the new sub-account per Business Partner and Product\" has been done.
diffstat: src-db/database/model/functions/C_VALIDCOMBINATION_CLONE.xml | 195 ++++++++++++++++ src-db/database/model/tables/AD_ORG_ACCTSCHEMA.xml | 33 ++ src-db/database/model/tables/C_ELEMENTVALUE.xml | 4 - src-db/database/model/triggers/C_BPARTNER_TRG.xml | 186 +++++++++++---- src-db/database/model/triggers/C_VALIDCOMBINATION_TRG.xml | 30 +- src-db/database/model/triggers/M_PRODUCT_TRG.xml | 112 ++++++--- src-db/database/sourcedata/AD_COLUMN.xml | 188 +++++++++++++++ src-db/database/sourcedata/AD_ELEMENT.xml | 78 ++++++ src-db/database/sourcedata/AD_FIELD.xml | 142 +++++++++++ src-db/database/sourcedata/AD_TEXTINTERFACES.xml | 2 +- 10 files changed, 869 insertions(+), 101 deletions(-) diffs (truncated from 1210 to 300 lines): diff -r 786b89452eb1 -r 3cb40e9246fc src-db/database/model/functions/C_VALIDCOMBINATION_CLONE.xml --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/src-db/database/model/functions/C_VALIDCOMBINATION_CLONE.xml Sat Jul 11 12:24:59 2009 +0530 @@ -0,0 +1,195 @@ +<?xml version="1.0"?> + <database name="FUNCTION C_VALIDCOMBINATION_CLONE"> + <function name="C_VALIDCOMBINATION_CLONE" type="VARCHAR"> + <parameter name="p_orginal_validcombination" type="VARCHAR" mode="in"> + <default/> + </parameter> + <parameter name="p_sequenceno" type="VARCHAR" mode="in"> + <default/> + </parameter> + <parameter name="p_accountname" type="VARCHAR" mode="in"> + <default/> + </parameter> + <parameter name="p_acct_length" type="NUMERIC" mode="in"> + <default/> + </parameter> + <parameter name="p_subacct_length" type="NUMERIC" mode="in"> + <default/> + </parameter> + <body><![CDATA[/************************************************************************* +* The contents of this file are subject to the Openbravo Public License +* Version 1.0 (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 SL +* All portions are Copyright (C) 2001-2006 Openbravo SL +* All Rights Reserved. +* Contributor(s): ______________________________________. +************************************************************************/ + v_Node_ID VARCHAR(32) :=null; --OBTG:varchar2-- + v_AD_TREE_ID VARCHAR(32) :=null; --OBTG:varchar2-- + v_PARENT_ID VARCHAR(32) :=null; --OBTG:varchar2-- + v_SeqNo NUMBER(38); + v_Next_Sequence_No VARCHAR(32) :=null; --OBTG:varchar2-- + v_Account_No VARCHAR(32) :=null; --OBTG:varchar2-- + v_Acct_No_For_SubAccount VARCHAR(32) :=null; --OBTG:varchar2-- + v_Subacct_No VARCHAR(32) :=null; --OBTG:varchar2-- + v_New_SubAccount_No VARCHAR(32) :=null; --OBTG:varchar2-- + v_Default_Account_No VARCHAR(32) :=null; --OBTG:varchar2-- + v_Seq_No_For_Subaccount_No VARCHAR(32) :=null; --OBTG:varchar2-- + v_Default_Account_Name VARCHAR(60) :=null; --OBTG:varchar2-- + v_Default_Account_Desc CHARACTER VARYING(255); --OBTG:varchar2-- + v_Default_Account_Client_ID VARCHAR(32); --OBTG:varchar2-- + v_Org_ID VARCHAR(32); --OBTG:varchar2-- + v_Account_ID VARCHAR(32); --OBTG:varchar2-- + v_seq_name CHARACTER VARYING(255); --OBTG:varchar2-- + v_C_AcctSchema_ID VARCHAR(32) :=null; --OBTG:varchar2-- + C_Out_New_Acct_Number VARCHAR(32) :=null; --OBTG:varchar2-- + v_UpdateSequence CHAR; + tmp_Subaccout_no VARCHAR(32) :=null; --OBTG:varchar2-- + v_C_ElementValue_ID VARCHAR(32) :=null; --OBTG:varchar2-- + +BEGIN + IF (p_orginal_validcombination IS NULL) THEN + RETURN NULL; + END IF; + v_C_ElementValue_ID := GET_UUID(); + + /* + Querying the default Account NUMBER, Client ID, Account Schema ID for cloning. + */ + SELECT + C_ElementValue.Value, + C_ElementValue.Name, + C_ElementValue.Description, + C_ElementValue.AD_Client_ID, + C_ElementValue.C_ElementValue_ID, + C_ValidCombination.C_AcctSchema_ID, + C_ElementValue.AD_Org_ID + INTO + v_Default_Account_No, + v_Default_Account_Name, + v_Default_Account_Desc, + v_Default_Account_Client_ID, + v_Node_ID, + v_C_AcctSchema_ID, + v_Org_ID + FROM C_ValidCombination, C_ElementValue + WHERE C_ValidCombination.Account_ID = C_ElementValue.C_ElementValue_ID + AND C_ValidCombination_ID =p_Orginal_Validcombination; + + -- Logic for generating the New Sub account number from Original Accounts. + v_Acct_No_For_SubAccount:=SUBSTR(v_Default_Account_No,1,p_Acct_Length); + tmp_Subaccout_no := p_Subacct_Length - p_Acct_Length; + v_Seq_No_For_Subaccount_No:= lpad(p_SequenceNo,cast(tmp_Subaccout_no as integer),'0'); + v_New_Subaccount_No:= v_Acct_No_For_SubAccount || v_Seq_No_For_Subaccount_No; + /* + Inserting the new element value for new account for Business Partner Or Product. + */ + INSERT INTO C_ElementValue + ( + C_ElementValue_ID, + AD_Client_ID, + AD_Org_ID, + IsActive, + Created, + CreatedBy, + Updated, + UpdatedBy, + Value, + Name, + Description, + AccountType, + AccountSign, + IsDocControlled, + C_Element_ID, + IsSummary, + ValidFrom, + ValidTo, + PostActual, + PostBudget, + PostEncumbrance, + PostStatistical, + IsBankAccount, + C_BankAccount_ID, + IsForeignCurrency, + C_Currency_ID , + ShowElement , + ShowValueCond , + ElementLevel) + + SELECT + v_C_ElementValue_ID, + EV.AD_Client_ID, + EV.AD_Org_ID, + EV.IsActive, + now(), + EV.CreatedBy, + now(), + EV.UpdatedBy, + v_New_Subaccount_No, + case when (p_accountName is not null) then (EV.Name || ' - ' || p_accountName) else EV.Name end, + EV.Description, + EV.AccountType, + EV.AccountSign, + EV.IsDocControlled, + EV.C_Element_ID, + EV.IsSummary, + EV.ValidFrom, + EV.ValidTo, + EV.PostActual, + EV.PostBudget, + EV.PostEncumbrance, + EV.PostStatistical, + EV.IsBankAccount, + EV.C_BankAccount_ID, + EV.IsForeignCurrency, + EV.C_Currency_ID, + EV.ShowElement, + EV.ShowValueCond, + EV.ElementLevel + FROM C_ValidCombination VC, C_ElementValue EV + WHERE VC.Account_ID = EV.C_ElementValue_ID + AND VC.C_ValidCombination_ID = p_orginal_validcombination; + + SELECT AD_TREE_ID INTO v_AD_TREE_ID + FROM C_ACCTSCHEMA_ELEMENT, C_ELEMENT + WHERE C_ACCTSCHEMA_ELEMENT.C_ELEMENT_ID = C_ELEMENT.C_ELEMENT_ID + AND C_ACCTSCHEMA_ELEMENT.ELEMENTTYPE = 'AC' + AND C_ACCTSCHEMA_ELEMENT.C_ACCTSCHEMA_ID = v_C_AcctSchema_ID; + + + SELECT PARENT_ID INTO v_PARENT_ID + FROM ad_treenode + WHERE AD_TREE_ID = v_AD_TREE_ID + AND NODE_ID = v_Node_ID; + + + SELECT MAX(COALESCE(SEQNO,0)) + 10 INTO v_SEQNO + FROM ad_treenode + WHERE AD_TREE_ID = v_AD_TREE_ID + AND PARENT_ID = v_PARENT_ID; + + + UPDATE AD_TREENODE SET PARENT_ID = (SELECT PARENT_ID + FROM ad_treenode WHERE AD_TREE_ID = v_AD_TREE_ID + AND NODE_ID = v_Node_ID ), SEQNO = v_SEQNO + WHERE NODE_ID = V_C_ElementValue_ID; + + /* + Function call to Retrieve the new account NUMBER for Business Partner or Product. */ + C_ValidCombination_Insert(C_Out_New_Acct_Number, v_Default_Account_Client_ID , v_Org_ID , v_C_AcctSchema_ID, + v_C_ElementValue_ID,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, + NULL, NULL, NULL, NULL, NULL, NULL, NULL); + + return C_Out_New_Acct_Number; +END C_VALIDCOMBINATION_CLONE +]]></body> + </function> + </database> diff -r 786b89452eb1 -r 3cb40e9246fc src-db/database/model/tables/AD_ORG_ACCTSCHEMA.xml --- a/src-db/database/model/tables/AD_ORG_ACCTSCHEMA.xml Fri Jul 10 21:36:24 2009 +0200 +++ b/src-db/database/model/tables/AD_ORG_ACCTSCHEMA.xml Sat Jul 11 12:24:59 2009 +0530 @@ -37,14 +37,47 @@ <default/> <onCreateDefault/> </column> + <column name="AD_SEQUENCE_BP_ID" primaryKey="false" required="false" type="VARCHAR" size="32" autoIncrement="false"> + <default/> + <onCreateDefault/> + </column> + <column name="AD_SEQUENCE_PR_ID" primaryKey="false" required="false" type="VARCHAR" size="32" autoIncrement="false"> + <default/> + <onCreateDefault/> + </column> + <column name="ISBPNEWACCOUNT" primaryKey="false" required="true" type="CHAR" size="1" autoIncrement="false"> + <default><![CDATA[N]]></default> + <onCreateDefault/> + </column> + <column name="ISPRNEWACCOUNT" primaryKey="false" required="true" type="CHAR" size="1" autoIncrement="false"> + <default><![CDATA[N]]></default> + <onCreateDefault/> + </column> + <column name="ACCT_LENGTH" primaryKey="false" required="false" type="DECIMAL" size="10,0" autoIncrement="false"> + <default/> + <onCreateDefault/> + </column> + <column name="SUBACCT_LENGTH" primaryKey="false" required="false" type="DECIMAL" size="10,0" autoIncrement="false"> + <default/> + <onCreateDefault/> + </column> <foreign-key foreignTable="AD_CLIENT" name="ADCLIENT_ADORGACCTSCHEMA"> <reference local="AD_CLIENT_ID" foreign="AD_CLIENT_ID"/> </foreign-key> <foreign-key foreignTable="AD_ORG" name="ADORG_ADORGACCTSCHEMA"> <reference local="AD_ORG_ID" foreign="AD_ORG_ID"/> </foreign-key> + <foreign-key foreignTable="AD_SEQUENCE" name="ADSEQUENCEBP_ADORGACCTSCHEMA"> + <reference local="AD_SEQUENCE_BP_ID" foreign="AD_SEQUENCE_ID"/> + </foreign-key> + <foreign-key foreignTable="AD_SEQUENCE" name="ADSEQUENCEPR_ADORGACCTSCHEMA"> + <reference local="AD_SEQUENCE_PR_ID" foreign="AD_SEQUENCE_ID"/> + </foreign-key> <foreign-key foreignTable="C_ACCTSCHEMA" name="CACCTSCHEMA_ADORGACCTSCHEMA"> <reference local="C_ACCTSCHEMA_ID" foreign="C_ACCTSCHEMA_ID"/> </foreign-key> + <check name="ADORGACCTSCH_ISACTIVE_CHECK"><![CDATA[ISACTIVE IN ('Y', 'N')]]></check> + <check name="ADORGACCTSCH_ISBPNEWACCT_CHECK"><![CDATA[ISBPNEWACCOUNT IN ('Y', 'N')]]></check> + <check name="ADORGACCTSCH_ISPRNEWACCT_CHECK"><![CDATA[ISPRNEWACCOUNT IN ('Y', 'N')]]></check> </table> </database> diff -r 786b89452eb1 -r 3cb40e9246fc src-db/database/model/tables/C_ELEMENTVALUE.xml --- a/src-db/database/model/tables/C_ELEMENTVALUE.xml Fri Jul 10 21:36:24 2009 +0200 +++ b/src-db/database/model/tables/C_ELEMENTVALUE.xml Sat Jul 11 12:24:59 2009 +0530 @@ -135,10 +135,6 @@ <index name="C_ELEMENTVALUE_NAME" unique="false"> <index-column name="NAME"/> </index> - <unique name="C_ELEMENTVALUE_VALUE"> - <unique-column name="C_ELEMENT_ID"/> - <unique-column name="VALUE"/> - </unique> <check name="C_ELEMENTVALUE_ISACTIVE_CHECK"><![CDATA[ISACTIVE IN ('Y', 'N')]]></check> <check name="CELEMVAL_ISBANKACCOUNT_CHECK"><![CDATA[ISBANKACCOUNT IN ('Y', 'N')]]></check> <check name="CELEMVAL_ISDOCCONTROL_CHECK"><![CDATA[ISDOCCONTROLLED IN ('Y', 'N')]]></check> diff -r 786b89452eb1 -r 3cb40e9246fc src-db/database/model/triggers/C_BPARTNER_TRG.xml --- a/src-db/database/model/triggers/C_BPARTNER_TRG.xml Fri Jul 10 21:36:24 2009 +0200 +++ b/src-db/database/model/triggers/C_BPARTNER_TRG.xml Sat Jul 11 12:24:59 2009 +0530 @@ -34,11 +34,15 @@ Cur_Defaults RECORD; Cur_Defaults2 RECORD; - v_xTree_ID varchar2(32); - v_xParent_ID varchar2(32); - v_BP_Acct_ID varchar2(32); - - + v_xTree_ID varchar2(32); + v_xParent_ID varchar2(32); + v_BP_Acct_ID varchar2(32); + v_C_Receivable_Acct varchar2(32); + v_V_Liability_Acct varchar2(32); + v_seq_name varchar2(32); + v_Sequence_No varchar2(32); + v_C_AcctSchema_ID VARCHAR2(32); + BEGIN IF AD_isTriggerEnabled()='N' THEN RETURN; @@ -46,59 +50,139 @@ -- Default Accounts for all AcctSchema - IF (INSERTING) THEN - FOR Cur_Defaults IN ( - SELECT distinct d1.* - FROM C_BP_Group_Acct d1, ad_Org_AcctSchema a - WHERE d1.C_BP_Group_ID=:new.C_BP_Group_ID + IF (INSERTING) THEN + v_C_AcctSchema_ID:=null; + FOR Cur_Defaults IN ------------------------------------------------------------------------------ Enter the BlackBerry Developer Challenge This is your chance to win up to $100,000 in prizes! For a limited time, vendors submitting new applications to BlackBerry App World(TM) will have the opportunity to enter the BlackBerry Developer Challenge. See full prize details at: http://p.sf.net/sfu/Challenge _______________________________________________ Openbravo-commits mailing list Openbravo-commits@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/openbravo-commits
