PROMPT Creating table EMAIL_TRACE

CREATE TABLE email_trace ( 
   report_id                     NUMBER(10,0)  NOT NULL,
   sender_user_id                NUMBER(10,0)  NOT NULL,
   agent_code                    CHAR(5)       NOT NULL,
   recipient_email_address       VARCHAR2(512) NOT NULL,
   transmission_date             DATE          NOT NULL,
   report_format                 VARCHAR2(5)   NOT NULL,
   location_code                 CHAR(6)       NOT NULL,
   flight_id                     NUMBER(10,0),
   flight_no                     CHAR(8),
   flight_date                   CHAR(5),
   transmission_failure          VARCHAR2(1)   DEFAULT 'N',
   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 email_trace add ( constraint email_trace_pk primary key (appn_row_id) using index tablespace &1._indexes);

ALTER TABLE email_trace ADD CONSTRAINT email_trace_fk_agents FOREIGN KEY (agent_code) REFERENCES agents (agent_code);

ALTER TABLE email_trace ADD CONSTRAINT email_trace_fk_reports FOREIGN KEY (report_id) REFERENCES reports (report_id);

ALTER TABLE email_trace ADD CONSTRAINT email_trace_fk_location FOREIGN KEY (location_code) REFERENCES location (location_code);

ALTER TABLE email_trace ADD CONSTRAINT email_trace_chk1 CHECK (transmission_failure IN ('Y','N'));

CREATE INDEX email_trace_ind1 ON email_trace(agent_code) TABLESPACE &1._indexes; 
  
CREATE INDEX email_trace_ind2 ON email_trace(sender_user_id) TABLESPACE &1._indexes; 
  
CREATE INDEX email_trace_ind3 ON email_trace(transmission_date) TABLESPACE &1._indexes;
  
CREATE INDEX email_trace_ind4 ON email_trace(report_id) TABLESPACE &1._indexes;
  
CREATE INDEX email_trace_ind5 ON email_trace(location_code) TABLESPACE &1._indexes;

COMMENT ON TABLE EMAIL_TRACE IS 'This table trace information for emails sent by the system';

COMMENT ON COLUMN EMAIL_TRACE.AGENT_CODE IS  'Agent code';
COMMENT ON COLUMN EMAIL_TRACE.FLIGHT_DATE IS  'Flight date';
COMMENT ON COLUMN EMAIL_TRACE.FLIGHT_ID IS  'Flight identifier';
COMMENT ON COLUMN EMAIL_TRACE.FLIGHT_NO IS  'Flight number';
COMMENT ON COLUMN EMAIL_TRACE.LOCATION_CODE IS  'Location code';
COMMENT ON COLUMN EMAIL_TRACE.RECIPIENT_EMAIL_ADDRESS IS  'Recipent email address';
COMMENT ON COLUMN EMAIL_TRACE.REPORT_FORMAT IS  'Format of the record';
COMMENT ON COLUMN EMAIL_TRACE.REPORT_ID IS  'Report identifier';
COMMENT ON COLUMN EMAIL_TRACE.SENDER_USER_ID IS  'User indentifier for the sender of the email';
COMMENT ON COLUMN EMAIL_TRACE.TRANSMISSION_DATE IS  'Transmission date of the email';
COMMENT ON COLUMN EMAIL_TRACE.TRANSMISSION_FAILURE IS  'Flag to indicate whether transmission failed';
COMMENT ON COLUMN EMAIL_TRACE.APPN_ROW_ID IS 'Unique sequence number for this row';
COMMENT ON COLUMN EMAIL_TRACE.ROW_CREATE_TIME IS 'The time the row was created';
COMMENT ON COLUMN EMAIL_TRACE.ROW_DATE_TIME IS 'The time the row was last modified';
COMMENT ON COLUMN EMAIL_TRACE.CUSER IS 'The user who created the row';
COMMENT ON COLUMN EMAIL_TRACE.MUSER IS 'The user who last modified the row';
COMMENT ON COLUMN EMAIL_TRACE.MTERM IS 'The terminal from which the last modifcation to this row was made';
COMMENT ON COLUMN EMAIL_TRACE.SYSTEM_ID IS 'The system identifier';
COMMENT ON COLUMN EMAIL_TRACE.SOURCE IS 'The process that last amended this row ';
COMMENT ON COLUMN EMAIL_TRACE.TO_BE_ARCHIVED IS 'Indicates that this row is to be archived or has been archived';
