COMMIT;
/* #########################################################################
   CREATE CATEGORY TABLE
   ######################################################################### */

CREATE TABLE CATEGORY(
  ID                 T_ID,
  CODE               T_CODE,
  DESCRIPTION        T_DESCRIPTION,
  SHW_DESCRIPTION    T_DESCRIPTION_NULL, /* Shadow Field on DESCRIPTION */
  SYS_ADD_DATE       T_DATE,
  SYS_ADD_USER       T_USER,
  SYS_CHG_DATE       T_DATE,
  SYS_CHG_USER       T_USER,
  CONSTRAINT         PK_CATEGORY PRIMARY KEY(ID)
);
CREATE UNIQUE INDEX UQ_CATEGORY_CODE ON CATEGORY(CODE);
CREATE UNIQUE INDEX UQ_CATEGORY_DESCRIPTION ON CATEGORY(SHW_DESCRIPTION);
GRANT ALL ON CATEGORY TO PUBLIC;
 

COMMIT;
/* #################
   INSERT TRIGGERS
   ################# */

SET TERM !! ;
CREATE TRIGGER TG_BI_PK_CATEGORY FOR CATEGORY BEFORE INSERT POSITION 0 AS
BEGIN
  IF ( ( new.ID IS NULL ) OR ( new.ID > GEN_ID(GEN_CATEGORY_ID, 0 ) ) )
    THEN new.ID = GEN_ID(GEN_CATEGORY_ID, 1 );
END !!
SET TERM ; !!

SET TERM !! ;
CREATE TRIGGER TG_BI_CALC_CATEGORY FOR CATEGORY BEFORE INSERT POSITION 10 AS
BEGIN
  new.CODE = UPPER(new.CODE);
  new.SHW_DESCRIPTION = UPPER(new.DESCRIPTION);
END !!
SET TERM ; !!

SET TERM !! ;
CREATE TRIGGER TG_BI_AUDIT_CATEGORY FOR CATEGORY BEFORE INSERT POSITION 100 AS
BEGIN
  new.SYS_CHG_DATE = 'NOW';
  new.SYS_ADD_DATE = new.SYS_CHG_DATE;
  new.SYS_ADD_USER = new.SYS_CHG_USER;
END !!
SET TERM ; !!
 

COMMIT;
/* #################
   UPDATE TRIGGERS
   ################# */

SET TERM !! ;
CREATE TRIGGER TG_BU_PK_CATEGORY FOR CATEGORY BEFORE UPDATE POSITION 0 AS
BEGIN
  new.ID = old.ID;
END !!
SET TERM ; !!

SET TERM !! ;
CREATE TRIGGER TG_BU_CALC_CATEGORY FOR CATEGORY BEFORE UPDATE POSITION 10 AS
BEGIN
  new.CODE = UPPER(new.CODE);
  new.SHW_DESCRIPTION = UPPER(new.DESCRIPTION);
END !!
SET TERM ; !!

SET TERM !! ;
CREATE TRIGGER TG_BU_AUDIT_CATEGORY FOR CATEGORY BEFORE UPDATE POSITION 100 AS
BEGIN
  new.SYS_ADD_DATE = old.SYS_ADD_DATE;
  new.SYS_ADD_USER = old.SYS_ADD_USER;
  IF ( ( old.CODE <> new.CODE ) OR
       ( old.SHW_DESCRIPTION <> new.SHW_DESCRIPTION ) )
    THEN new.SYS_CHG_DATE = 'NOW';
END !!
SET TERM ; !!
 

COMMIT;
/* #########################################################################
   CREATE ITEM TABLE
   ######################################################################### */

CREATE TABLE ITEM(
  ID                 T_ID,
  ID_CATEGORY        T_ID,
  DESCRIPTION        T_DESCRIPTION,
  BARCODE            T_CODE,
  COST_PRICE         T_CURRENCY,
  CURR_VALUE         T_CURRENCY,
  SHW_DESCRIPTION    T_DESCRIPTION_NULL, /* Shadow Field on DESCRIPTION */
  SYS_ADD_DATE       T_DATE,
  SYS_ADD_USER       T_USER,
  SYS_CHG_DATE       T_DATE,
  SYS_CHG_USER       T_USER,
  CONSTRAINT         PK_ITEM PRIMARY KEY(ID),
  CONSTRAINT         RI_ITEM_CATEGORY FOREIGN KEY(ID_CATEGORY) REFERENCES CATEGORY(ID)
);
CREATE INDEX         IX_ITEM_DESCRIPTION ON ITEM(SHW_DESCRIPTION);
CREATE UNIQUE INDEX  UQ_ITEM_BARCODE ON ITEM(BARCODE);
GRANT ALL ON ITEM TO PUBLIC;
 

COMMIT;
/* #################
   INSERT TRIGGERS
   ################# */

SET TERM !! ;
CREATE TRIGGER TG_BI_PK_ITEM FOR ITEM BEFORE INSERT POSITION 0 AS
BEGIN
  IF ( ( new.ID IS NULL ) OR ( new.ID > GEN_ID(GEN_ITEM_ID, 0 ) ) )
    THEN new.ID = GEN_ID(GEN_ITEM_ID, 1 );
END !!
SET TERM ; !!

SET TERM !! ;
CREATE TRIGGER TG_BI_CALC_ITEM FOR ITEM BEFORE INSERT POSITION 10 AS
BEGIN
  new.SHW_DESCRIPTION = UPPER(new.DESCRIPTION);
  IF ( new.COST_PRICE IS NULL ) THEN new.COST_PRICE = 0;
  IF ( new.CURR_VALUE IS NULL ) THEN new.CURR_VALUE = new.COST_PRICE;
END !!
SET TERM ; !!

SET TERM !! ;
CREATE TRIGGER TG_BI_AUDIT_ITEM FOR ITEM BEFORE INSERT POSITION 100 AS
BEGIN
  new.SYS_CHG_DATE = 'NOW';
  new.SYS_ADD_DATE = new.SYS_CHG_DATE;
  new.SYS_ADD_USER = new.SYS_CHG_USER;
END !!
SET TERM ; !!
 

COMMIT;
/* #################
   UPDATE TRIGGERS
   ################# */

SET TERM !! ;
CREATE TRIGGER TG_BU_PK_ITEM FOR ITEM BEFORE UPDATE POSITION 0 AS
BEGIN
  new.ID = old.ID;
END !!
SET TERM ; !!

SET TERM !! ;
CREATE TRIGGER TG_BU_CALC_ITEM FOR ITEM BEFORE UPDATE POSITION 10 AS
BEGIN
  new.SHW_DESCRIPTION = UPPER(new.DESCRIPTION);
  IF ( new.COST_PRICE IS NULL ) THEN new.COST_PRICE = 0;
  IF ( new.CURR_VALUE IS NULL ) THEN new.CURR_VALUE = new.COST_PRICE;
END !!
SET TERM ; !!

SET TERM !! ;
CREATE TRIGGER TG_BU_AUDIT_ITEM FOR ITEM BEFORE UPDATE POSITION 100 AS
BEGIN
  new.SYS_ADD_DATE = old.SYS_ADD_DATE;
  new.SYS_ADD_USER = old.SYS_ADD_USER;
  IF ( ( old.ID_CATEGORY <> new.ID_CATEGORY ) OR
       ( old.SHW_DESCRIPTION <> new.SHW_DESCRIPTION ) OR
       ( old.BARCODE <> new.BARCODE ) OR
       ( old.COST_PRICE <> new.COST_PRICE ) OR
       ( old.CURR_VALUE <> new.CURR_VALUE ) )
    THEN new.SYS_CHG_DATE = 'NOW';
END !!
SET TERM ; !!
 

COMMIT;
/* #########################################################################
   POPULATE CATEGORY TABLE
   ######################################################################### */
 
SET GENERATOR GEN_CATEGORY_ID TO 3;

INSERT INTO CATEGORY(ID, CODE, DESCRIPTION) VALUES(1, 'MV', 'Motor Vehicle');
INSERT INTO CATEGORY(ID, CODE, DESCRIPTION) VALUES(2, 'CE', 'Computer Equipment');
INSERT INTO CATEGORY(ID, CODE, DESCRIPTION) VALUES(3, 'FF', 'Furniture and Fittings');
 

COMMIT;
/* #########################################################################
   POPULATE ITEM TABLE
   ######################################################################### */

SET TERM !! ;
CREATE PROCEDURE SP_GEN_DUMMY_ITEM(P_CATEGORY_ID INTEGER, P_DESCRIPTION VARCHAR(50), P_BAR_PREFIX VARCHAR(5), P_COUNT INTEGER) AS
  DECLARE VARIABLE i INTEGER;
BEGIN
  i = 1;
  WHILE ( i <= :P_COUNT ) DO BEGIN
    INSERT INTO ITEM(ID_CATEGORY, DESCRIPTION, BARCODE, COST_PRICE, CURR_VALUE)
      VALUES( :P_CATEGORY_ID,
              ( :P_DESCRIPTION || ' ' || :i ),
              ( :P_BAR_PREFIX || ( :P_CATEGORY_ID * ( 100000 + :i ) + :i * 10000 + :i * 10 + :P_CATEGORY_ID ) ),
              ( :i * 121 + :P_CATEGORY_ID * 10 + :i ),
              ( :i * 59 ) );
    i = i + 1;
  END /* WHILE */
END !!
SET TERM ; !!
GRANT EXECUTE ON PROCEDURE SP_GEN_DUMMY_ITEM TO PUBLIC;
 

COMMIT;
 

EXECUTE PROCEDURE SP_GEN_DUMMY_ITEM(1, 'Bmw 325i', 'BMW', 10);
EXECUTE PROCEDURE SP_GEN_DUMMY_ITEM(1, 'Tipper-Truck', 'TIPTR', 10);
EXECUTE PROCEDURE SP_GEN_DUMMY_ITEM(1, 'Kawasaki KLE500', 'KLE', 10);
EXECUTE PROCEDURE SP_GEN_DUMMY_ITEM(1, 'Porche 911', 'PORCH', 10);
EXECUTE PROCEDURE SP_GEN_DUMMY_ITEM(1, 'Yellow Submarine', 'SUB', 10);

EXECUTE PROCEDURE SP_GEN_DUMMY_ITEM(2, 'P166 MMX', '166MX',10);
EXECUTE PROCEDURE SP_GEN_DUMMY_ITEM(2, 'Printer', 'PRINT', 10);
EXECUTE PROCEDURE SP_GEN_DUMMY_ITEM(2, '386 DX-40', '386DX', 10);
EXECUTE PROCEDURE SP_GEN_DUMMY_ITEM(2, 'MainFrame', 'MF', 10);
EXECUTE PROCEDURE SP_GEN_DUMMY_ITEM(2, 'Monitor', 'MON', 10);

EXECUTE PROCEDURE SP_GEN_DUMMY_ITEM(3, 'Chair- Wooden', 'WDCHR', 10);
EXECUTE PROCEDURE SP_GEN_DUMMY_ITEM(3, 'Chair - Swivel', 'SWCHR', 10);
EXECUTE PROCEDURE SP_GEN_DUMMY_ITEM(3, 'Plant', 'PLANT', 10);
EXECUTE PROCEDURE SP_GEN_DUMMY_ITEM(3, 'Picture', 'PICTR', 10);
EXECUTE PROCEDURE SP_GEN_DUMMY_ITEM(3, 'Table', 'TABLE', 10);
 

COMMIT;