Table Prefix

Table Prefix

When you open the iDempiere's database you can see many tables prefixes. What is each one?

 AD_  : Application Dictionary       (i.e.: AD_Element)
 A_   : Assets Management            (i.e.: A_Asset_Group)
 ASP_ : Application Service Provider (i.e.: ASP_Module)
 B_   : Marketplace                  (i.e.: B_Buyer)
 C_   : Common or Core Functionality (i.e.: C_AcctSchema)
 CM_  : Collaboration Management     (i.e.: CM_WebProject)
 FACT_: Multi-Dimensional Cube       (i.e.: Fact_Acct)
 GL_  : General Ledger               (i.e.: GL_Journal)
 HR_  : Human Resource               (i.e.: HR_Payroll) (Integration to Libero)
 I_   : Import                       (i.e.: I_BPartner)
 K_   : Knowledge Management         (i.e.: K_Category)
 M_   : Material Management          (i.e.: M_Cost)
 MFA_ : Multi Factor Authentication  (i.e.: MFA_Method)
 PA_  : Performance Analysis         (i.e.: PA_Report)
 PP_  : Production Planning          (i.e.: PP_Order)  (Integration to Libero)
 R_   : Requests                     (i.e.: R_Request)
 RV_  : Report View                  (i.e.: RV_BPartner)
 S_   : Service                      (i.e.: S_Resource)
 T_   : Temporary Tables             (i.e.: T_Report)
 W_   : Web                          (i.e.: W_Basket)
 WS_  : Web Service                  (i.e.: WS_WebService)

Prefixes used or reserved by add-on projects

 LCO_  : Localization Colombia - Carlos Ruiz
 JP_  : Localization Japan - Hideaki Hagiwara
 LCL_  : Localization Chile - Marcos Medina
 LFR_  : Localization France - Nicolas Micoud
 LIT_  : Localization Italy - Marco Longo

Prefixes used or reserved by implementors contributing public plugins

 BXS   : BX Services GmbH
 SBxx  : Martin Schönbeck Beratungen GmbH (xx plugin dependent)

Special Column Suffix & Prefix

• _ID
  • Data Type: Numeric(10), in oracle Number(10)
  • Primary key or foreign key column. Where possible, should use the pattern TableName_ID. For e.g, AD_User_ID is the primary key column for the AD_User table.
• Is
  • Data Type: Char(1) Value: Y/N
  • Use for yes/no flag, for e.g - IsApproved, IsActive, IsVendor, etc.

Some Tips about Custom Tables

• Naming:
  • Use Prefix - EXT_, CUST_ or System Custom Prefix
    • It's recommended to use custom prefixes with three or more letters. The model classes must be looked with prefix. I.e. if you have a table called XX_Invoice the model class must be looked as MInvoice and can conflict with the product model class, but if you have a table called CUS_Invoice, then the model class looked will be MCUSInvoice.
  • Try to be descriptive.
  • Use case conventions consistently.
• Required Columns:
  • AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy
• Suggested Columns:
  • Value (the Key), Name, Description, Help
• Very Important: The Column Names are case sensitive.

About References (Data Types)

• TableDir
  • Column Name without _ID is the Table Name
    • If Column: C_BPartner_ID ....Then Table is: C_BPartner
    • Don't forget: Case sensitive
• Search
  • Same as TableDir: displayed in Search and Info Window rather then ComboBox
• Table
  • Define Display Column and Key Column
  • Column CreatedBy = Table: C_BPartner - Display: Name - Key: C_BPartner_ID
• Dynamic Validation Rules
  • The content is revaluated when change the Variable.
    • AD_Column.AD_Table_ID= @AD_Table_ID@

Some Special Columns

• Processed
  • Data Type: CHAR(1) - Values: Y/N
    • Used for all Transaction Tables.
    • If Y then your record is Read-Only.
    • Fields marked as Always Updateable aren't read only
    • When the table has this column the History button is enabled on the toolbar

• Posted
  • Data Type: CHAR(1) - Values: Y/N
    • If Y then is displayed as Posting Button.

• Processing
  • Data Type: CHAR(1) - Values: Y/N
    • Lock for Batch processing.
    • Second use for starting any Process.

• Record_ID
  • Data Type: Numeric(10), in oracle Number(10)
    • With AD_Table_ID, system reference (Zoom)

Standard Mandatory Columns

• AD_Client_ID
  • Data Type: Numeric(10) NOT NULL, in oracle Number(10) NOT NULL
• AD_Org_ID
  • Data Type: Numeric(10) NOT NULL, in oracle Number(10) NOT NULL
• IsActive
  • Data Type: Char(1) - Values: Y/N
• Created
  • Data Type: Timestamp NOT NULL, in oracle Date NOT NULL
• CreatedBy
  • Data Type: Numeric(10) NOT NULL, in oracle Number(10) NOT NULL
• Updated
  • Data Type: Timestamp NOT NULL, in oracle Date NOT NULL
• UpdatedBy
  • Data Type: Numeric(10) NOT NULL, in oracle Number(10) NOT NULL
• [Table]_UU
  • Data Type: Varchar(36) NOT NULL, in oracle Varchar2(36 char) NOT NULL
• [Table]_ID (optional but suggested to have)
  • Data Type: Numeric(10) NOT NULL, in oracle Number(10) NOT NULL

Common column use in master

• Value
  • Data Type: varchar(40), in oracle varchar2(40 char)
    • Search Key or Code
• Name
  • Data Type: varchar(60), in oracle varchar2(60 char)
    • Display name
• Description
  • Data Type: varchar(255), in oracle varchar2(255 char)
    • Long description
• Help
  • Data Type: varchar(2000), in oracle varchar2(2000 char)
    • Help or comments