Database Modifications

This Wiki is read-only for reference purposes to avoid broken links.

• Following are the new Tables which have been added in posterita as well as changes needed.
• The SQL is loaded to SourceForge Download. Apply it to your ADempiere DB.

//This Table is to store all the menus we have in posterita
1. CREATE TABLE U_MENU
 (
    U_Menu_ID      NUMBER(10,0)      NOT NULL  ,
    AD_Client_ID   NUMBER(10,0)      NOT NULL  ,
    AD_Org_ID      NUMBER(10,0)      NOT NULL  ,
    IsActive       CHAR(1)           DEFAULT 'Y' NOT NULL  ,
    Created        DATE              DEFAULT SYSDATE NOT NULL,
    CreatedBy      NUMBER(10,0)      NOT NULL ,
    Updated        DATE              DEFAULT SYSDATE NOT NULL ,
    UpdatedBy      NUMBER(10,0)      NOT NULL  ,
    Name           NVARCHAR2(120)        NOT NULL  ,
    MenuLink       NVARCHAR2(510)        NOT NULL  ,
    Module         NVARCHAR2(120)        NOT NULL  ,
    ParentMenu_ID  NUMBER(10,0)       ,
    HasSubMenu     CHAR(1)           DEFAULT 'N' NOT NULL,
    Description    NVARCHAR2(200)         ,
    ImageLink      NVARCHAR2(510)         ,
    Position       VARCHAR(10)        ,
    Help           NVARCHAR2(2000)    ,
    Category       NVARCHAR2(120),
    Sequence       NUMBER(10,0),
    primary key(U_Menu_ID),
    CHECK(IsActive IN ('Y', 'N')),
    CHECK(HasSubMenu IN ('Y', 'N'))
    
 )
 
 // This table stores the access for the roles only for the posterita menus.
 2. CREATE TABLE U_ROLE_MENU
 
 (
    U_RoleMenu_ID  NUMBER(10,0)   NOT NULL  ,
    AD_Client_ID   NUMBER(10,0)   NOT NULL  ,
    AD_Org_ID      NUMBER(10,0)   NOT NULL  ,
    IsActive       CHAR(1)        DEFAULT 'Y' NOT NULL,
    Created        DATE           DEFAULT SYSDATE NOT NULL,
    CreatedBy      INTEGER        NOT NULL,
    Updated        DATE           DEFAULT SYSDATE NOT NULL,
    UpdatedBy      INTEGER        NOT NULL,
    AD_Role_ID     NUMBER(10,0)   NOT NULL,
    U_Menu_ID      NUMBER(10,0)   NOT NULL,
    primary key(U_RoleMenu_ID),
    CHECK(IsActive IN ('Y', 'N'))
 
 )
 
 // Store some default values for the Factories defined in the code
 
 CREATE TABLE U_WEB_PROPERTIES
  (
    U_Web_Properties_ID  NUMBER(10,0)   NOT NULL  ,
    AD_Client_ID         NUMBER(10,0)   NOT NULL  ,
    AD_Org_ID            NUMBER(10,0)   NOT NULL  ,
    IsActive             CHAR(1)        DEFAULT 'Y' NOT NULL ,
    Created              DATE           DEFAULT SYSDATE NOT NULL ,
    CreatedBy            INTEGER        NOT NULL ,
    Updated              DATE           DEFAULT SYSDATE NOT NULL ,
    UpdatedBy            INTEGER        NOT NULL ,
    U_Key                NVARCHAR2(240)     NOT NULL ,
    U_Value              NVARCHAR2(240)     NOT NULL ,
    primary key(U_Web_Properties_ID),
    CHECK(IsActive IN ('Y', 'N'))
  )
  
  // This Table stores all the cheque numbers that have been blaklisted by the bank, 
  // a security feature demanded by on of the clients
  
  CREATE TABLE U_BlackListCheque
  (
    U_BlackListCheque_ID  NUMBER(10,0)     NOT NULL ,
    AD_Client_ID          NUMBER(10,0)     NOT NULL  ,
    AD_Org_ID             NUMBER(10,0)     NOT NULL  ,
    IsActive              CHAR(1)          DEFAULT 'Y' NOT NULL,
    Created               DATE             DEFAULT SYSDATE NOT NULL  ,
    CreatedBy             NUMBER(10,0)     NOT NULL  ,
    Updated               DATE             DEFAULT SYSDATE NOT NULL ,
    UpdatedBy             NUMBER(10,0)     NOT NULL  ,
    BankName              NVARCHAR2(120)   NOT NULL  ,
    ChequeNo              NVARCHAR2(120)   NOT NULL  ,
    primary key(U_BlackListCheque_ID),
    CHECK(IsActive IN ('Y', 'N'))
  )



  
  //The following tables have been modified to add the new fields.
 1. C_ORDER  
 
    a) OrderType NVARCHAR2(510); //the type of the order for the filters on the history etc
    b) POSID NUMBER(10,0);  //ID of the POS Terminal to link the order with the terminal,
       required to sort out the earnings for a perticular terminal.
    c) AmountTendered NUMBER(22,2); //to find out about the tendered amount for an order.
    d) AmountRefunded NUMBER(22,2); //to find out about the refunded amount for an order.
    

2. AD_USER

     a) UserDiscount NUMBER(22,2); 
       //security feature. User is only allowed to discount upton the limit specified in the field
     b)	UserPIN NVARCHAR2(20);
       //PIN for the pin login feature	

     
3. M_PRODUCT

	a) GROUP1 NVARCHAR2(255);
	b) GROUP2  NVARCHAR2(255); 
        //Both these fields have been created to falicitate the user to have its own grouping in the reports apart 
        //from the grouping already exists

	c) Keyword  NVARCHAR2(255); //All these fields are to group products according to some defined
	d) Keyword2 NVARCHAR2(255); //kewwords for the products, mainly required for the webstore.
	e) Keyword3 NVARCHAR2(255);
 	f) Keyword4 NVARCHAR2(255);



4. AD_PRINTFORMAT

(a) CLASSNAME NVARCHAR2(240) //It defines the classname for the print format for the slip printer
(b) ARGS      NVARCHAR2(480) //the arguments (fields) that needs to be printed on the slip, takes        default in absence of any.

Links

• Return to Posterita