Hi folks,
When you want to create something new on Openbravo, first thing to do on the construction process is to create his skeleton on the database. The ERP tool needs at least eight obrigatory fields to run a simple window and they are:
1. ID
This column will serve to identify the registry on the table. Openbravo uses UUID (more info here or simple one here) for recognition, so you gonna need a varchar2(32) in case of a oracle database or character varying(32) in case of postgreSQL;
2. Client ID (AD_ORG_ID)
To identify on with client this registry is, openbravo need this column on the record. As the ID of this table, the Client ID uses UUID also (and have a foreign key rule), so varchar2(32) on oracle or character varying(32) on postegreSQL;
3. Organization ID (AD_CLIENT_ID)
Same as the Client ID but, for the organization;
4. Is Active
This column determines if the record is active or not. This fella is used in lot of process, such as selects on database and other things, so be aware sometimes when you are trying to check a registry but he is not appearing on the window, etc. Different from the other cited above, this column is a char(1) on oracle or postegreSQL (both cases);
5. Created
This column shows the time that the record has been created. In the case of oracle this column will have a DATE property, but on postgreSQL will be a time stamp without timezone;
6. Createdby
Created is a column that will determine the user who created this record. As a identified column, the User ID gonna be inserted on him, so this column is a varchar2(32) in case of a oracle database or character varying(32) in case of postgreSQL. This column doesn't have a foreign key rule, but you can make a join to the user table, to gather more information on the user who created this record;
7. Updated
Finally, this shows the time that the record was updated;
8. Updatedby
This guy is just like the 'Created' but he will be filled when the user updates the record;
----
So, to make a table on oracle you gonna need at least:
ORACLE:
CREATE TABLE "TABLENAME"
(
"TABLENAME_ID" VARCHAR2(32) NOT NULL ENABLE,
"AD_CLIENT_ID" VARCHAR2(32) NOT NULL ENABLE,
"AD_ORG_ID" VARCHAR2(32) NOT NULL ENABLE,
"ISACTIVE" CHAR(1) DEFAULT 'Y' NOT NULL ENABLE,
"CREATED" DATE DEFAULT SYSDATE NOT NULL ENABLE,
"CREATEDBY" VARCHAR2(32) NOT NULL ENABLE,
"UPDATED" DATE DEFAULT SYSDATE NOT NULL ENABLE,
"UPDATEDBY" VARCHAR2(32) NOT NULL ENABLE,
-- insert extra columns here
CONSTRAINT "TABLENAME_ISACTIVE_CHECK" CHECK (ISACTIVE IN ('Y', 'N')) ENABLE,
CONSTRAINT "TABLENAME_KEY" PRIMARY KEY ("TABLENAME_ID") ENABLE,
CONSTRAINT "TABLENAME_AD_CLIENT" FOREIGN KEY ("AD_CLIENT_ID")
REFERENCES "AD_CLIENT" ("AD_CLIENT_ID") ENABLE,
CONSTRAINT "TABLENAME_AD_ORG" FOREIGN KEY ("AD_ORG_ID")
REFERENCES "AD_ORG" ("AD_ORG_ID") ENABLE
)
POSTGRESQL:
CREATE TABLE TABLENAME
(
TABLENAME_ID CHARACTER VARYING(32) NOT NULL,
AD_CLIENT_ID CHARACTER VARYING(32) NOT NULL,
AD_ORG_ID CHARACTER VARYING(32) NOT NULL,
ISACTIVE CHARACTER(1) NOT NULL DEFAULT 'Y',
CREATED TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT now(),
CREATEDBY CHARACTER VARYING(32) NOT NULL,
UPDATED TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT now(),
UPDATEDBY CHARACTER VARYING(32) NOT NULL,
-- insert extra columns here
CONSTRAINT HRSALARY_ISACTIVE_CHECK CHECK (isactive = ANY (ARRAY['Y'::bpchar, 'N'::bpchar])),
CONSTRAINT TABLENAME_KEY PRIMARY KEY (TABLENAME_ID),
CONSTRAINT AD_ORG_CUS_PROJECT FOREIGN KEY (AD_ORG_ID)
REFERENCES AD_ORG (AD_ORG_ID),
CONSTRAINT AD_CLIENT_CUS_PROJECT FOREIGN KEY (AD_CLIENT_ID)
REFERENCES AD_CLIENT (AD_CLIENT_ID),
)
---
If you wanna a deep info, check here,
See ya ;)