Projects:DEFINING SUB ACCT FOR BP PRODUCT/Technical Specifications

Contents 1 DEFINING A NEW ACCOUNT PER BUSINESS PARTNER & PRODUCT 1.1 Purpose of the Project 1.2 Introduction 1.3 Technical Implementation 1.4 Database Changes 1.5 Configuring the Application Dictionary 1.6 Open Discussion Items 1.7 Closed Discussion Items

DEFINING A NEW ACCOUNT PER BUSINESS PARTNER & PRODUCT

Technical Specification Document

Purpose of the Project

The purpose of this project is to enhance OB ERP application to setup new sub-accounts for each type of Business Partner(Customer/Vendor) and Product.

Introduction

This technical specifications document outlines the implementation of distinct new sub-accounts per each type of Business Partner(Customer or Vendor) and Product. The new requirement is to implement a new feature which will allow the user to configure the system to automatically create a separate account for every Business Partner (Customer / Vendor) and Product.To configure this feature, go to General Setup > Enterprise > Organization > Org Schema.

Technical Implementation

User Interface Implementation:

• The “Org Schema” tab of Organization window will be modified to have the check box called Create New Account for Business Partner and Create New Account for Product. Apart from the check-boxes above. The Org Scheme tab will also have the additional fields in UI for sequence selection, Length of the Account and Length of the Sub-account().

• When user chooses the desired checkbox for creating the separate for each business partner (customer or vendor) and product.If user checks one of the comboboxes, some new fields appear to configure this feature.
  • Sequence combobox.
  • Account Length.
  • Sub Account Length.

• Sequence numbering is a feature of Openbravo ERP , that provides a number generator in a really easy way.

Creating a numbering sequence

• From the Application menu, select Financial Management > Accounting > Setup > Document Sequence.
• Click New. A new document sequence record appears.
• From the Organization list, select the organization which will use this document sequence. Select * to apply the sequence across all organizations.
• In the Name field, give the sequence a descriptive name.
• In the Description field, type a brief description of the sequence if required.
• If you want to number documents automatically (for example to increase the document number by 1 every time a Business Partner or Product is created), select the Auto numbering options.
• Click Save.

Database Changes

According to the requirement the new table columns need to be added in ad_org_acctschema

1. IsBPNewAccount character(1)
2. IsProductNewAccount character(1)
3. Ad_Sequence_BP_Id character varying(32)
4. Ad_Sequence_PR_Id character varying(32)
5. Acct_length numeric(10)
6. Subacct_length numeric(10)

Configuring the Application Dictionary

• Login Openbravo ERP as System Administrator.
• Goto Application Dictionary || Tables and columns || Table then search "AD_Org_AcctSchema".
• Select the "AD_Org_AcctSchema" table (Double click it).
• Then click on the Button called "Create Columns from DB".
• You should get the message as below.
  • Process completed successfully. Created = 6.
• Then,Run the "Synchronize Terminology" process inside the "Application Dictionary".
• Application Dictionary || Windows, Tabs, and Fields || Window then search "Organization" tab "Org Schema".
• Application Dictionary || Windows, Tabs, and Fields || Window then create fields.
• Application Dictionary || Windows, Tabs, and Fields  ||  Tab  >>    >>  Field Sequence.
• Order the below fields in the Field Sequence Tab.
  • IsBPNewAccount
  • IsProductNewAccount
  • Ad_Sequence_BP_Id
  • Ad_Sequence_PR_Id
  • Acct_length
  • Subacct_length
  • Indicate "Display in the same line" to the sequence fileds.
• Application Dictionary || Windows, Tabs, and Fields  ||  Window  >>  Tab  >>  Field
  • Display logic for displaying the distinct new account for Product and Business Partner. The display logic can be configured on the system by modifying the table on the Application Dictionary as below.
• Table which Requires Modification :
  • AD_ORG_ACCTSCHEMA
• Field which Requires the Modification :
  • AD_SEQUENCE_BP_ID, AD_SEQUENCE_PR_ID, ACCT_LENGTH AND SUBACCT_LENGTH.
• If user checks one of the combobox of Create New Account for Business Partner, The below new fields appear to configure the new sub-account feature.
  • Sequence for Business Partner.
  • Account Length.
  • Sub Account Length.
• If user checks one of the combobox of Create New Account for Product ,The below new fields appear to configure the new sub-account feature.
  • Sequence for Product.
  • Account Length.
  • Sub Account Length.
• When user chooses the desired checkbox, The flag 'Y' is stored on the column of IsBPNewAccount and IsProductNewAccount in the table of C_ORG_ACCTSCHEMA.
  • Ad_Sequence_BP_Id: @IsBPNewAccount@='Y'
  • Ad_Sequence_PR_Id: @IsProductNewAccount@='Y'
  • Acct_length: @IsBPNewAccount@='Y' || @IsProductNewAccount@='Y'
  • Subacct_length: @IsBPNewAccount@='Y' || @IsProductNewAccount@='Y'

* C_BPARTNER_TRG:

---

In the existing system when create a Business Partner(customer or Vendor),The trigger C_BPARTNER_TRG will be raised to set the default accounts for a newly created Business Partner(customer or Vendor). According to the new requirement when create a Business Partner(customer or Vendor),The trigger C_BPARTNER_TRG will be raised to set the sub accounts for a newly created Business Partner(customer or Vendor).

Trigger changes:

• The below steps are explains the changes in the trigger.
  • In the C_BPARTNER_TRG will have the Customer creation ,Vendor creation and Employee creation. In this case we have two cursor , One is for Customer and Vendor creation. Another cursor is for Employee creation.The appropriate changes will be made in the above mentioned cursor for creating new sub accounts.
• We need to have below values in the above mentioned cursor from the table name of ad_Org_AcctSchema.
  • AD_SEQUENCE_BP_ID
  • ISBPNEWACCOUNT
  • ACCT_LENGTH
  • SUBACCT_LENGTH

• Once we got the above field values in the cursor, We need to check the condition whether it is going to create a Default account or New sub-account based on the checkbox selection.

• The flag 'Y' is stored on the column of "IsBPNewAccount" in the table of C_ORG_ACCTSCHEMA when user chooses "Create Business partner checkbox".

• If user chooses the Business Partner checkbox , The below condition will execute for creating the new sub-account, otherwise Else part will execute to set the default accounts.
  • IF (Cur_Defaults.IsBPNewAccount='Y') THEN

• • Cur_Defaults is the Cursor name which is having the value of the Column "IsBPNewAccount"

• Inside of the condition, We need to get the Sequence field name in the below steps for creating the sub account.
  • login as system administrator
  • Application Dictionary || Windows, Tabs, and Fields || Window then search "Document sequence"
  • When click on tab , there we find the table of AD_SEQUENCE for document sequence window.
  • Click on Field tab , Then find the field name of 'Next Assigned Number'
  • When click on name of filed Next Assigned Number , we will get the DB column name of CurrentNext.
  • The DB Column name of CurrentNext will be used for generating the sequence number.
• We have to get the sequence name which is having the value of the Column "Ad_Sequence_BP_Id" with using the below SQL Query.
  • SELECT NAME INTO v_seq_name FROM AD_SEQUENCE WHERE AD_SEQUENCE.AD_SEQUENCE_ID=Cur_Defaults.Ad_Sequence_BP_Id;
• Once we got the sequence name , we have to pass the parameter to the Procedure of AD_Sequence_Doc. The below is query to get the Sequence number.
  • SELECT * INTO v_Sequence_No FROM AD_Sequence_Doc(v_seq_name,Cur_Defaults.AD_Client_ID, 'Y');

• After getting the sequence number, we have to call and pass the required parameter's to the newly created function of " c_validcombination_clone" . The required parameter as follow's
  • C_Receivable_Acct
  • v_Sequence_No
  • acct_length
  • subacct_length
• The below all functionalities are defined in the Functions called " c_validcombination_clone".
  • Copying(Clone) the accounts details from the default account.
  • After creating the new element value for the account, Fold it and  Place it properly in tree (using the same parent_ID than the account of the category has).
  • Create the valid combination for the newly created account, so that it can be displayed under Business Partner (Customer or Vendor).
  • Assign the Valid combination to business partner.
  • Clone the accounts details from the C_VALIDCOMBINATION & C_ELEMENTVALUE

• Using the below function for trimming the newly created sub account number.
  • LPAD(<ad_sequence_bp_id>, <acct_length - subacct_length>,'0').
  • LPAD Usage:
    • Remaining charactor will be fill up by the 0 in the left side if the sequence number is less than sub account length
    • Ex.If sub_account_Length is 5. But Sequence is 1, In this case the result will be 00001.

• The function of c_validcombination_clone will return the account number after passing the required parameter's
• The below is the newly created sub account number for Customer with calling function of c_validcombination_clone and Inserted into the table name of C_BP_Customer_Acct.
  • Receivable Account,
  • Prepayment Account.
• The below is the newly created sub account number for Vendor with calling function of c_validcombination_clone and Inserted into the table name of C_BP_Vendor_Acct.
  • Liability account,
  • liability services account,
  • Prepayment account,
• The below is the newly created sub account number for Employee with calling function of c_validcombination_clone and Inserted into the table name of C_BP_Employee_Acct.
  • Expense account,
  • Prepayment account.

*M_PRODUCT_TRG:

---

In the existing system when create a Product,The trigger M_PRODUCT_TRG will be raised to set the default accounts for a newly created Product. According to the new requirement when create a Product,The trigger M_PRODUCT_TRG will be raised to set the sub accounts for a newly created Product.

Trigger changes:

• The below steps are explains the changes in the trigger.
  • In the M_PRODUCT_TRG will have the Product creation, In this case we have cursor called "Cur_Defaults",The appropriate changes will be made in the above mentioned cursor for creating new sub accounts.

• We need to have below values in the above mentioned cursor from the table name of ad_Org_AcctSchema.
  • AD_SEQUENCE_PR_ID
  • ISBPNEWACCOUNT
  • ACCT_LENGTH
  • SUBACCT_LENGTH

• Once we got the above field values in the cursor, We need to check the condition whether it is going to create a Default account or New sub-account based on the checkbox selection.

• The flag 'Y' is stored on the column of "IsPRNewAccount" in the table of C_ORG_ACCTSCHEMA when user chooses "Create a Product checkbox".

• If user chooses the Product checkbox , The below condition will execute for creating the new sub-account, otherwise Else part will execute to set the default accounts.
  • IF (Cur_Defaults.IsPRNewAccount='Y') THEN

• • Cur_Defaults is the Cursor name which is having the value of the Column "IsPRNewAccount"

• Inside of the condition, We need to get the Sequence field name in the below steps for creating the sub account.
  • login as system administrator
  • Application Dictionary || Windows, Tabs, and Fields || Window then search "Document sequence"
  • When click on tab , there we find the table of AD_SEQUENCE for document sequence window.
  • Click on Field tab , Then find the field name of 'Next Assigned Number'
  • When click on name of filed Next Assigned Number , we will get the DB column name of CurrentNext.
  • The DB Column name of CurrentNext will be used for generating the sequence number.

• We have to get the sequence name which is having the value of the Column "Ad_Sequence_PR_Id" with using the below SQL Query.
  • SELECT NAME INTO v_seq_name FROM AD_SEQUENCE WHERE AD_SEQUENCE.AD_SEQUENCE_ID=Cur_Defaults.Ad_Sequence_PR_Id;
• Once we got the sequence name , we have to pass the parameter to the Procedure of AD_Sequence_Doc. The below is query to get the Sequence number.
  • SELECT * INTO v_Sequence_No FROM AD_Sequence_Doc(v_seq_name,Cur_Defaults.AD_Client_ID, 'Y');

• After getting the sequence number, we have to call and pass the required parameter's to the newly created function of " c_validcombination_clone" . The required parameter as follow's
  • P_Revenue_Acct
  • v_Sequence_No
  • acct_length
  • subacct_length
• The below all functionalities are defined in the Functions called " c_validcombination_clone".
  • Copying(Clone) the accounts details from the default account.
  • After creating the new element value for the account, Fold it and Place it properly in tree (using the same parent_ID than the account of the category has).
  • Create the valid combination for the newly created account, so that it can be displayed under Product accounting section.
  • Assign the Valid combination to business partner.

• Using the below function for trimming the newly created sub account number.
  • LPAD(<ad_sequence_bp_id>, <acct_length - subacct_length>,'0').
  • LPAD Usage:
    • Remaining charactor will be fill up by the 0 in the left side if the sequence number is less than sub account length
    • Ex.If sub_account_Length is 5. But Sequence is 1, In this case the result will be 00001.

• The function of c_validcombination_clone will return the account number after passing the required parameter's.

• The below is the newly created sub account number with calling function of c_validcombination_clone and Inserted into the table name of M_Product_Acct.
  • Revenue_Account,
  • Expense_Account,
  • Asset_Account,
  • COGS_Account,
  • PurchasePriceVariance_Account,
  • InvoicePriceVariance_Account,
  • TradeDiscountRec_Account,
  • TradeDiscountGrant_Account.

Open Discussion Items

Closed Discussion Items

Category: Projects ERP