DROP SEQUENCE ussi_jobid
/

CREATE SEQUENCE ussi_jobid
  INCREMENT BY 1
  START WITH 1000
  MINVALUE 1
  MAXVALUE 999999999999999999999999999
  NOCYCLE
  NOORDER
  CACHE 20
/

DROP TABLE ussi_jobs CASCADE CONSTRAINTS
/

CREATE TABLE ussi_jobs
    (ujb_jobid                      NUMBER NOT NULL,
    ujb_randomid                   VARCHAR2(80) NOT NULL,
    ujb_email_address              VARCHAR2(100),
    ujb_certificate                VARCHAR2(4000),
    ujb_signatureblock             VARCHAR2(100),
    ujb_signer_comment             VARCHAR2(4000),
    ujb_inputfile                  VARCHAR2(4000),
    ujb_outputfile                 VARCHAR2(4000),
    ujb_jobstatus                  VARCHAR2(20) NOT NULL,
    ujb_jobstatusdetail            VARCHAR2(80) NOT NULL,
    ujb_applogo                    VARCHAR2(4000),
    ujb_requestor                  VARCHAR2(100),
    ujb_requestor_comment          VARCHAR2(4000),
    ujb_ecopy                      VARCHAR2(5),
    ujb_ecopyoutputfile            VARCHAR2(4000),
    ujb_target_location            VARCHAR2(4000),
    ujb_newwindow_yn               VARCHAR2(1) DEFAULT 'Y'  NOT NULL,
    ujb_createdate                 DATE,
    ujb_modifydate                 DATE,
    ujb_joburl                     VARCHAR2(100),
    ujb_function                   VARCHAR2(10) )
/

CREATE UNIQUE INDEX ussi_jobs_unique ON ussi_jobs
  (
    ujb_jobid                       ASC
  )
/

ALTER TABLE ussi_jobs
ADD UNIQUE (ujb_jobid)
/

DROP TABLE ussi_jobs_sign_reasons CASCADE CONSTRAINTS
/

CREATE TABLE ussi_jobs_sign_reasons
    (ujr_jobid                      NUMBER NOT NULL,
    ujr_reason                     VARCHAR2(4000) NOT NULL)
/

DROP TABLE ussi_sign_reasons CASCADE CONSTRAINTS
/

CREATE TABLE ussi_sign_reasons
    (usr_code                       NUMBER,
	 usr_reason                     VARCHAR2(4000) NOT NULL)
/

INSERT INTO ussi_sign_reasons
VALUES ('1','I am the author of this document.')
/
INSERT INTO ussi_sign_reasons
VALUES ('2','I am approving this document.')
/
INSERT INTO ussi_sign_reasons
VALUES ('3','I have reviewed this document.')
/
INSERT INTO ussi_sign_reasons
VALUES ('4','I attest to the accuracy and integrity of this document.')
/
INSERT INTO ussi_sign_reasons
VALUES ('5','I agree to the terms defined by the placement of my signature on this document.')
/
INSERT INTO ussi_sign_reasons
VALUES ('6','I agree to specified portions of this document.')
/
