create table Current_Work(

   ID int NOT NULL,

   Job_Type nvarchar2(255) NULL,

   Module_Type nvarchar2(10) NULL,

   Workstation_Name nvarchar2(80) NULL,

   Operator_Name nvarchar2(80) NULL,

   File_Name nvarchar2(255) NULL,

   File_Path nvarchar2(255) NULL,

   Date_Time date NULL,

   File_Creation_Date_Time date NULL,

   CONSTRAINT PK_current_work PRIMARY KEY

   (

        ID

   ),

   CONSTRAINT IX_current_work UNIQUE

   (

	ID,

	Job_Type,

	Module_Type,

	Workstation_Name,

	Operator_Name,

	Date_Time

   ) 

)





create table Deleted_Work(

   ID int NOT NULL,

   Job_Type nvarchar2(255) NULL,

   Module_Type nvarchar2(10) NULL,

   Workstation_Name nvarchar2(80) NULL,

   Operator_Name nvarchar2(80) NULL,

   File_Name nvarchar2(255) NULL,

   File_Path nvarchar2(255) NULL,

   Date_Time date NULL,

   File_Creation_Date_Time date NULL,

   CONSTRAINT PK_deleted_work PRIMARY KEY

   (

        ID

   ),

   CONSTRAINT IX_deleted_work UNIQUE

   (

	ID,

	Job_Type,

	Module_Type,

	Workstation_Name,

	Operator_Name,

	Date_Time

   ) 

)







create table Routed_Work(

   ID int NOT NULL,

   Job_Type nvarchar2(255) NULL,

   Module_Type nvarchar2(10) NULL,

   Workstation_Name nvarchar2(80) NULL,

   Operator_Name nvarchar2(80) NULL,

   File_Name nvarchar2(255) NULL,

   File_Path nvarchar2(255) NULL,

   Date_Time date NULL,

   File_Creation_Date_Time date NULL,

   Routed_To_Job_Type nvarchar2(255) NULL,

   Routed_To_File_Path nvarchar2(255) NULL,

   CONSTRAINT PK_routed_work PRIMARY KEY

   (

        ID

   ),

   CONSTRAINT IX_routed_work UNIQUE

   (

	ID,

	Job_Type,

	Module_Type,

	Workstation_Name,

	Operator_Name,

	Date_Time

   ) 

)



create table Completed_Work(

   ID int NOT NULL,

   Date_Time date NULL,

   Module_Type nvarchar2(10) NULL,

   Job_Type nvarchar2(255) NULL,

   Other_Destination nvarchar2(80) NULL,

   Input_Filename nvarchar2(255) NULL,

   Input_Directory nvarchar2(255) NULL,

   Output_Filename nvarchar2(255) NULL,

   Output_Directory nvarchar2(255) NULL,

   Output_Email_Address nvarchar2(255) NULL,

   Output_FtpServer_Address nvarchar2(255) NULL,

   Output_File_Format nvarchar2(5) NULL,

   Output_File_Size int NULL,

   Number_Of_Pages int NULL,

   Elapsed_Time int NULL,

   Routing_Rulename nvarchar2(255) NULL,

   Processed_By nvarchar2(80) NULL,

   KFI_Index_File nvarchar2(255) NULL,

   KFI_Index_File2 nvarchar2(255) NULL,

   Key_Strokes int NULL,

   Workstation_Name nvarchar2(80) NULL,

   Operator_Name nvarchar2(80) NULL,

   Zone_Errors int NULL,

   Scanner_Settings nvarchar2(255) NULL,

   Enhancement_Settings nvarchar2(255) NULL,

   File_Creation_Date_Time date NULL,

   CONSTRAINT PK_completed_work PRIMARY KEY 

   (

        ID

   ),

   CONSTRAINT IX_completed_work UNIQUE

   (

	ID,	

	Job_Type,

	Module_Type,

	Workstation_Name,

	Operator_Name,

	Date_Time

    )

)





CREATE SEQUENCE SEQ_CURRENT_ID

	MINVALUE 1

	MAXVALUE 999999999999999999

	START WITH 1

	INCREMENT BY 1

	NOCACHE

 



CREATE OR REPLACE TRIGGER TRG_CURRENT_WORK_INS  

     BEFORE INSERT ON CURRENT_WORK  

         FOR EACH ROW  

         BEGIN  

             IF :new.ID IS NULL THEN  

                 SELECT SEQ_CURRENT_ID.nextval INTO :new.ID FROM DUAL;  

             END IF;  

         END;  

   

COMMIT; 



CREATE SEQUENCE SEQ_DELETED_ID

	MINVALUE 1

	MAXVALUE 999999999999999999

	START WITH 1

	INCREMENT BY 1

	NOCACHE

 



CREATE OR REPLACE TRIGGER TRG_DELETED_WORK_INS  

     BEFORE INSERT ON DELETED_WORK  

         FOR EACH ROW  

         BEGIN  

             IF :new.ID IS NULL THEN  

                 SELECT SEQ_DELETED_ID.nextval INTO :new.ID FROM DUAL;  

             END IF;  

         END;  





CREATE SEQUENCE SEQ_ROUTED_ID

	MINVALUE 1

	MAXVALUE 999999999999999999

	START WITH 1

	INCREMENT BY 1

	NOCACHE

 



CREATE OR REPLACE TRIGGER TRG_ROUTED_WORK_INS  

     BEFORE INSERT ON ROUTED_WORK  

         FOR EACH ROW  

         BEGIN  

             IF :new.ID IS NULL THEN  

                 SELECT SEQ_ROUTED_ID.nextval INTO :new.ID FROM DUAL;  

             END IF;  

         END;  

   

COMMIT; 



CREATE SEQUENCE SEQ_COMPLETED_ID

	MINVALUE 1

	MAXVALUE 999999999999999999

	START WITH 1

	INCREMENT BY 1

	NOCACHE

 



CREATE OR REPLACE TRIGGER TRG_COMPLETED_WORK_INS  

     BEFORE INSERT ON COMPLETED_WORK  

         FOR EACH ROW  

         BEGIN  

             IF :new.ID IS NULL THEN  

                 SELECT SEQ_COMPLETED_ID.nextval INTO :new.ID FROM DUAL;  

             END IF;  

         END;  

   

COMMIT;