CREATE TABLE CMPROCESSING (PROCESS_ID VARCHAR2(30) NOT NULL, COOP_CONTROL VARCHAR2(10), COOP_ID VARCHAR2(10), ENTRY_ID VARCHAR2(30) NOT NULL, ENTRY_DATE DATE, START_PROCESS NUMBER(4,0) DEFAULT 0 NOT NULL, END_PROCESS NUMBER(4,0) DEFAULT 0 NOT NULL, READ_DATE DATE, FINISH_DATE DATE, RUNTIME_STATUS NUMBER(2,0), SHOW_FLAG NUMBER(1,0) DEFAULT 1 NOT NULL, RUNTIME_MESSAGE VARCHAR2(500), OBJECT_NAME VARCHAR2(150), CMD VARCHAR2(500), LABEL_NAME VARCHAR2(50), CRITERIA_XML CLOB, CRITERIA_XML_1 CLOB, CRITERIA_XML_2 CLOB, APPLICATION VARCHAR2(20), REPORT_GROUP_ID VARCHAR2(40), REPORT_ID VARCHAR2(40), REPORT_PATH VARCHAR2(255), PRINTER VARCHAR2(150), WORKDATE DATE) ;
ALTER TABLE CMPROCESSING ADD ( CONSTRAINT CMP_PK PRIMARY KEY ( PROCESS_ID, ENTRY_ID )) ;

CREATE TABLE "CMPROCESSINGLOG" ("PROCESS_ID" VARCHAR2(30) NOT NULL, seq_no number(30,0), "COOP_CONTROL" VARCHAR2(10), "COOP_ID" VARCHAR2(10), "ENTRY_ID" VARCHAR2(30) NOT NULL, "ENTRY_DATE" DATE, "START_PROCESS" NUMBER(4,0) DEFAULT 0 NOT NULL, "END_PROCESS" NUMBER(4,0) DEFAULT 0 NOT NULL, "READ_DATE" DATE, "FINISH_DATE" DATE, "RUNTIME_STATUS" NUMBER(2,0), "SHOW_FLAG" NUMBER(1,0) DEFAULT 1 NOT NULL, "RUNTIME_MESSAGE" VARCHAR2(500), "OBJECT_NAME" VARCHAR2(150), "CMD" VARCHAR2(500), "LABEL_NAME" VARCHAR2(50), "CRITERIA_XML" CLOB, "CRITERIA_XML_1" CLOB, "CRITERIA_XML_2" CLOB, "APPLICATION" VARCHAR2(20), "REPORT_GROUP_ID" VARCHAR2(40), "REPORT_ID" VARCHAR2(40), "REPORT_PATH" VARCHAR2(255), "PRINTER" VARCHAR2(150), "WORKDATE" DATE) ;

CREATE SEQUENCE CMPROCESSINGLOG_SEQ INCREMENT BY 1 MAXVALUE 9999999999999999999999999999 MINVALUE 1 CYCLE CACHE 30 ORDER;

create or replace procedure reset_seq( p_seq_name in varchar2 )
is
    l_val number;
begin
    execute immediate
    'select ' || p_seq_name || '.nextval from dual' INTO l_val;

    execute immediate
    'alter sequence ' || p_seq_name || ' increment by -' || l_val ||' minvalue 0';

    execute immediate
    'select ' || p_seq_name || '.nextval from dual' INTO l_val;

    execute immediate
    'alter sequence ' || p_seq_name || ' increment by 1 minvalue 0';
end;
/
ALTER procedure reset_seq COMPILE ;
begin reset_seq('CMPROCESSINGLOG_SEQ'); end;
/


create or replace trigger tg_cmprocessign_log 
AFTER insert or update  ON cmprocessing 
referencing old as o new as n 
FOR EACH ROW 
DECLARE SEQ_NO  NUMBER(20,0);
begin
  select CMPROCESSINGLOG_SEQ.nextval into SEQ_NO from dual ;
  insert into cmprocessinglog (SEQ_NO,PROCESS_ID,COOP_CONTROL ,COOP_ID ,ENTRY_ID ,ENTRY_DATE,START_PROCESS ,END_PROCESS ,READ_DATE ,FINISH_DATE ,RUNTIME_STATUS ,SHOW_FLAG ,RUNTIME_MESSAGE ,OBJECT_NAME ,CMD ,LABEL_NAME ,CRITERIA_XML ,CRITERIA_XML_1 ,CRITERIA_XML_2 ,APPLICATION ,REPORT_GROUP_ID ,REPORT_ID ,REPORT_PATH,PRINTER ,WORKDATE ) 
  values (SEQ_NO,:n.PROCESS_ID,:n.COOP_CONTROL ,:n.COOP_ID ,:n.ENTRY_ID ,:n.ENTRY_DATE,:n.START_PROCESS ,:n.END_PROCESS ,:n.READ_DATE ,:n.FINISH_DATE ,:n.RUNTIME_STATUS ,:n.SHOW_FLAG ,:n.RUNTIME_MESSAGE ,:n.OBJECT_NAME ,:n.CMD ,:n.LABEL_NAME ,:n.CRITERIA_XML ,:n.CRITERIA_XML_1 ,:n.CRITERIA_XML_2 ,:n.APPLICATION ,:n.REPORT_GROUP_ID ,:n.REPORT_ID ,:n.REPORT_PATH,:n.PRINTER ,:n.WORKDATE );
end;
/
ALTER TRIGGER tg_cmprocessign_log disable;