Difference between revisions of "Database Modifications"

Revision as of 12:44, 19 February 2007

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);