Table Prefix

From iDempiere en

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
Cookies help us deliver our services. By using our services, you agree to our use of cookies.