/*****************************************************************************
*
* Filename         - $Workfile:   messages_trace.sql  $
*
*                     Name              Date      Version     CR Number
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - TAGTRAK(Oracle Database)
*
* Source Code
* Identifier       - 0/3/300/8010/112
*
* Design Spec
* Section          -
*
* Contents         - Live Schema: messages_trace table and associated items
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2006
*
*****************************************************************************/

PROMPT Creating table MESSAGES_TRACE

CREATE TABLE messages_trace (
   message_id                    NUMBER(10) NOT NULL,
   message_part_id               NUMBER(10),
   creator_id                    NUMBER(10) NOT NULL,
   creator_location_code         CHAR(6),
   creator_agent_code            CHAR(5) ,
   method                        CHAR(1)    NOT NULL,
   trace_action                  VARCHAR(20),
   message_destination           CHAR(8),
   message_route_id              NUMBER(10),
   action_timestamp              DATE       DEFAULT SYSDATE NOT NULL,
   row_create_time               date,
   row_date_time                 date,
   cuser                         varchar2(30),
   muser                         varchar2(30),
   mterm                         varchar2(64),
   source                        varchar2(8),
   to_be_archived                varchar2(1),
   appn_row_id                   number(18) not null,
   system_id                     number(4) default 0 not null
);

alter table messages_trace add ( constraint messages_trace_pk primary key (appn_row_id) using index tablespace &1._indexes);

ALTER TABLE messages_trace ADD ( CONSTRAINT messages_trace_fk_bmm_routes FOREIGN KEY ( message_route_id ) REFERENCES bmm_routes( route_id ));

ALTER TABLE messages_trace ADD (   CONSTRAINT messages_trace_fk_users FOREIGN KEY ( creator_id ) REFERENCES tagtrak_users( user_id ));

ALTER TABLE messages_trace ADD (   CONSTRAINT messages_trace_fk_locations FOREIGN KEY ( creator_location_code ) REFERENCES location( location_code ));

ALTER TABLE messages_trace ADD (   CONSTRAINT messages_trace_fk_agents FOREIGN KEY ( creator_agent_code ) REFERENCES agents( agent_code ));

ALTER TABLE messages_trace ADD (   CONSTRAINT messages_trace_method_ch CHECK (method IN ('M','A')));

CREATE INDEX messages_trace_sk1 ON messages_trace(creator_agent_code) TABLESPACE &1._indexes;

CREATE INDEX messages_trace_sk2 ON messages_trace(creator_id) TABLESPACE &1._indexes;

CREATE INDEX messages_trace_sk3 ON messages_trace(creator_location_code) TABLESPACE &1._indexes;

CREATE INDEX messages_trace_sk4 ON messages_trace(message_route_id) TABLESPACE &1._indexes;

COMMENT ON TABLE MESSAGES_TRACE IS 'This table contains a log of actions performed on messages';

COMMENT ON COLUMN MESSAGES_TRACE.ACTION_TIMESTAMP IS  'The time the action was performed';
COMMENT ON COLUMN MESSAGES_TRACE.CREATOR_AGENT_CODE IS  'Agent code';
COMMENT ON COLUMN MESSAGES_TRACE.CREATOR_ID IS  'Creator identifier';
COMMENT ON COLUMN MESSAGES_TRACE.CREATOR_LOCATION_CODE IS  'Location code';
COMMENT ON COLUMN MESSAGES_TRACE.MESSAGE_DESTINATION IS  'Destination of the message';
COMMENT ON COLUMN MESSAGES_TRACE.MESSAGE_ID IS  'Message identifier';
COMMENT ON COLUMN MESSAGES_TRACE.MESSAGE_PART_ID IS  'Message part identifier';
COMMENT ON COLUMN MESSAGES_TRACE.MESSAGE_ROUTE_ID IS  'Route identifier';
COMMENT ON COLUMN MESSAGES_TRACE.METHOD IS  'Method';
COMMENT ON COLUMN MESSAGES_TRACE.TRACE_ACTION IS  'Action being traced';
COMMENT ON COLUMN MESSAGES_TRACE.APPN_ROW_ID IS 'Unique sequence number for this row';
COMMENT ON COLUMN MESSAGES_TRACE.ROW_CREATE_TIME IS 'The time the row was created';
COMMENT ON COLUMN MESSAGES_TRACE.ROW_DATE_TIME IS 'The time the row was last modified';
COMMENT ON COLUMN MESSAGES_TRACE.CUSER IS 'The user who created the row';
COMMENT ON COLUMN MESSAGES_TRACE.MUSER IS 'The user who last modified the row';
COMMENT ON COLUMN MESSAGES_TRACE.MTERM IS 'The terminal from which the last modifcation to this row was made';
COMMENT ON COLUMN MESSAGES_TRACE.SYSTEM_ID IS 'The system identifier';
COMMENT ON COLUMN MESSAGES_TRACE.SOURCE IS 'The process that last amended this row ';
COMMENT ON COLUMN MESSAGES_TRACE.TO_BE_ARCHIVED IS 'Indicates that this row is to be archived or has been archived';
