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

//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; b) GROUP2; //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);