--------------------------------------------------------
--  ファイルを作成しました - 土曜日-4月-07-2012   
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Sequence SEQ_REQUEST_SEQ_ID
--------------------------------------------------------

   CREATE SEQUENCE  "SEQ_REQUEST_SEQ_ID"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  ORDER  NOCYCLE ;

--------------------------------------------------------
--  ファイルを作成しました - 土曜日-4月-07-2012   
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Table REQUEST_DATA
--------------------------------------------------------

  CREATE TABLE "REQUEST_DATA" 
   (	"SEQ_ID" NUMBER, 
	"SEI_KANJI" VARCHAR2(50 BYTE), 
	"MEI_KANJI" VARCHAR2(50 BYTE), 
	"SEI_KANA" VARCHAR2(50 BYTE), 
	"MEI_KANA" VARCHAR2(50 BYTE), 
	"BIRTH_DATE" VARCHAR2(8 BYTE), 
	"GENDER" NUMBER, 
	"GENDER_TEXT" VARCHAR2(100 BYTE), 
	"ZIP1" VARCHAR2(10 BYTE), 
	"ZIP2" VARCHAR2(10 BYTE), 
	"ADDRESS1" VARCHAR2(60 BYTE), 
	"ADDRESS2" VARCHAR2(60 BYTE), 
	"ADDRESS3" VARCHAR2(80 BYTE), 
	"ADDRESS4" VARCHAR2(80 BYTE), 
	"TEL1" VARCHAR2(10 BYTE), 
	"TEL2" VARCHAR2(10 BYTE), 
	"TEL3" VARCHAR2(10 BYTE), 
	"M_TEL1" VARCHAR2(10 BYTE), 
	"M_TEL2" VARCHAR2(10 BYTE), 
	"M_TEL3" VARCHAR2(10 BYTE), 
	"E_MAIL" VARCHAR2(100 BYTE), 
	"OCCUPATION" NUMBER, 
	"OCCUPATION_TEXT" VARCHAR2(100 BYTE), 
	"REQUEST_DATE" DATE, 
	"IP_ADDRESS" VARCHAR2(100 BYTE), 
	"SESSION_ID" VARCHAR2(100 BYTE), 
	"STATUS" NUMBER, 
	"STATUS_TEXT" VARCHAR2(100 BYTE)
   );
--------------------------------------------------------
--  DDL for Index REQUEST_DATA_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "REQUEST_DATA_PK" ON "REQUEST_DATA" ("SEQ_ID");
--------------------------------------------------------
--  DDL for Index IX_REQUEST_DATA_SEI_KANJI
--------------------------------------------------------

  CREATE INDEX "IX_REQUEST_DATA_SEI_KANJI" ON "REQUEST_DATA" ("SEI_KANJI");
--------------------------------------------------------
--  DDL for Index IX_REQUEST_DATA_MEI_KANJI
--------------------------------------------------------

  CREATE INDEX "IX_REQUEST_DATA_MEI_KANJI" ON "REQUEST_DATA" ("MEI_KANJI");
--------------------------------------------------------
--  DDL for Index IX_REQUEST_DATA_SEI_KANA
--------------------------------------------------------

  CREATE INDEX "IX_REQUEST_DATA_SEI_KANA" ON "REQUEST_DATA" ("SEI_KANA");
--------------------------------------------------------
--  DDL for Index IX_REQUEST_DATA_MEI_KANA
--------------------------------------------------------

  CREATE INDEX "IX_REQUEST_DATA_MEI_KANA" ON "REQUEST_DATA" ("MEI_KANA");
--------------------------------------------------------
--  DDL for Index IX_REQUEST_DATA_ZIP1
--------------------------------------------------------

  CREATE INDEX "IX_REQUEST_DATA_ZIP1" ON "REQUEST_DATA" ("ZIP1");
--------------------------------------------------------
--  DDL for Index IX_REQUEST_DATA_ZIP2
--------------------------------------------------------

  CREATE INDEX "IX_REQUEST_DATA_ZIP2" ON "REQUEST_DATA" ("ZIP2");
--------------------------------------------------------
--  DDL for Index IX_REQUEST_DATA_EMAIL
--------------------------------------------------------

  CREATE INDEX "IX_REQUEST_DATA_EMAIL" ON "REQUEST_DATA" ("E_MAIL");
  
--------------------------------------------------------
--  DDL for Index IX_REQUEST_DATA_REQUEST_DATE
--------------------------------------------------------

  CREATE INDEX "IX_REQUEST_DATA_REQUEST_DATE" ON "REQUEST_DATA" ("REQUEST_DATE");
--------------------------------------------------------
--  Constraints for Table REQUEST_DATA
--------------------------------------------------------

  ALTER TABLE "REQUEST_DATA" ADD CONSTRAINT "REQUEST_DATA_PK" PRIMARY KEY ("SEQ_ID");

  ALTER TABLE "REQUEST_DATA" MODIFY ("STATUS_TEXT" NOT NULL ENABLE);
  ALTER TABLE "REQUEST_DATA" MODIFY ("STATUS" NOT NULL ENABLE);
  ALTER TABLE "REQUEST_DATA" MODIFY ("SESSION_ID" NOT NULL ENABLE);
  ALTER TABLE "REQUEST_DATA" MODIFY ("IP_ADDRESS" NOT NULL ENABLE);
  ALTER TABLE "REQUEST_DATA" MODIFY ("REQUEST_DATE" NOT NULL ENABLE);
  ALTER TABLE "REQUEST_DATA" MODIFY ("OCCUPATION_TEXT" NOT NULL ENABLE);
  ALTER TABLE "REQUEST_DATA" MODIFY ("OCCUPATION" NOT NULL ENABLE);
  ALTER TABLE "REQUEST_DATA" MODIFY ("E_MAIL" NOT NULL ENABLE);
  ALTER TABLE "REQUEST_DATA" MODIFY ("ADDRESS3" NOT NULL ENABLE);
  ALTER TABLE "REQUEST_DATA" MODIFY ("ADDRESS2" NOT NULL ENABLE);
  ALTER TABLE "REQUEST_DATA" MODIFY ("ADDRESS1" NOT NULL ENABLE);
  ALTER TABLE "REQUEST_DATA" MODIFY ("ZIP2" NOT NULL ENABLE);
  ALTER TABLE "REQUEST_DATA" MODIFY ("ZIP1" NOT NULL ENABLE);
  ALTER TABLE "REQUEST_DATA" MODIFY ("GENDER_TEXT" NOT NULL ENABLE);
  ALTER TABLE "REQUEST_DATA" MODIFY ("GENDER" NOT NULL ENABLE);
  ALTER TABLE "REQUEST_DATA" MODIFY ("BIRTH_DATE" NOT NULL ENABLE);
  ALTER TABLE "REQUEST_DATA" MODIFY ("MEI_KANA" NOT NULL ENABLE);
  ALTER TABLE "REQUEST_DATA" MODIFY ("SEI_KANA" NOT NULL ENABLE);
  ALTER TABLE "REQUEST_DATA" MODIFY ("MEI_KANJI" NOT NULL ENABLE);
  ALTER TABLE "REQUEST_DATA" MODIFY ("SEI_KANJI" NOT NULL ENABLE);
  ALTER TABLE "REQUEST_DATA" MODIFY ("SEQ_ID" NOT NULL ENABLE);

--------------------------------------------------------
--  ファイルを作成しました - 土曜日-4月-07-2012   
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Table STATUS_HISTORY
--------------------------------------------------------

  CREATE TABLE "STATUS_HISTORY" 
   (	"SEQ_ID" NUMBER, 
	"OLD_STATUS" NUMBER, 
	"OLD_STATUS_TEXT" VARCHAR2(100 BYTE), 
	"NEW_STATUS" NUMBER, 
	"NEW_STATUS_TEXT" VARCHAR2(100 BYTE), 
	"CHANGE_COMMENT" VARCHAR2(1024 BYTE), 
	"CREATE_DATE" DATE, 
	"OPERATOR_ID" VARCHAR2(50 BYTE)
   );
--------------------------------------------------------
--  Constraints for Table STATUS_HISTORY
--------------------------------------------------------

  ALTER TABLE "STATUS_HISTORY" MODIFY ("OPERATOR_ID" NOT NULL ENABLE);
  ALTER TABLE "STATUS_HISTORY" MODIFY ("CREATE_DATE" NOT NULL ENABLE);
  ALTER TABLE "STATUS_HISTORY" MODIFY ("NEW_STATUS_TEXT" NOT NULL ENABLE);
  ALTER TABLE "STATUS_HISTORY" MODIFY ("NEW_STATUS" NOT NULL ENABLE);
  ALTER TABLE "STATUS_HISTORY" MODIFY ("OLD_STATUS_TEXT" NOT NULL ENABLE);
  ALTER TABLE "STATUS_HISTORY" MODIFY ("OLD_STATUS" NOT NULL ENABLE);
  ALTER TABLE "STATUS_HISTORY" MODIFY ("SEQ_ID" NOT NULL ENABLE);

--------------------------------------------------------
--  ファイルを作成しました - 土曜日-4月-07-2012   
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Table MST_USER
--------------------------------------------------------

  CREATE TABLE "MST_USER" 
   (	"OPERATOR_ID" VARCHAR2(50 BYTE), 
	"PASSWORD" VARCHAR2(256 BYTE), 
	"OPERATOR_NAME" VARCHAR2(50 BYTE), 
	"ROLE" VARCHAR2(20 BYTE), 
	"ROLE_TEXT" VARCHAR2(256 BYTE), 
	"LAST_PASSWORD_CHANGE" DATE, 
	"INITIAL_PASSWORD" NUMBER, 
	"LOCK_FLAG" NUMBER, 
	"FAILED_COUNT" NUMBER, 
	"LAST_LOGIN_DATE" DATE, 
	"LAST_LOGIN_DATE2" DATE, 
	"DATE_CREATED" DATE, 
	"DISABLED" NUMBER
   );

Insert into MST_USER (OPERATOR_ID,PASSWORD,OPERATOR_NAME,ROLE,ROLE_TEXT,LAST_PASSWORD_CHANGE,INITIAL_PASSWORD,LOCK_FLAG,FAILED_COUNT,LAST_LOGIN_DATE,LAST_LOGIN_DATE2,DATE_CREATED,DISABLED) values ('999999','41c667051788249604fde272ce140ebc36f4df9285612a0a7f664de5534d9b22','Admin','1,2,3,4','Read,Write,CSV Download,User Manager',to_date('12-04-05','RR-MM-DD'),0,0,0,to_date('12-04-07','RR-MM-DD'),to_date('12-04-07','RR-MM-DD'),to_date('12-04-05','RR-MM-DD'),0);
--------------------------------------------------------
--  DDL for Index MST_USER_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "MST_USER_PK" ON "MST_USER" ("OPERATOR_ID");

--------------------------------------------------------
--  Constraints for Table MST_USER
--------------------------------------------------------

  ALTER TABLE "MST_USER" ADD CONSTRAINT "MST_USER_PK" PRIMARY KEY ("OPERATOR_ID");
  ALTER TABLE "MST_USER" MODIFY ("DATE_CREATED" NOT NULL ENABLE);
  ALTER TABLE "MST_USER" MODIFY ("ROLE_TEXT" NOT NULL ENABLE);
  ALTER TABLE "MST_USER" MODIFY ("ROLE" NOT NULL ENABLE);
  ALTER TABLE "MST_USER" MODIFY ("OPERATOR_NAME" NOT NULL ENABLE);
  ALTER TABLE "MST_USER" MODIFY ("PASSWORD" NOT NULL ENABLE);
  ALTER TABLE "MST_USER" MODIFY ("OPERATOR_ID" NOT NULL ENABLE);

--------------------------------------------------------
--  ファイルを作成しました - 土曜日-4月-07-2012   
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Table LOGIN_LOG
--------------------------------------------------------

  CREATE TABLE "LOGIN_LOG" 
   (	"OPERATOR_ID" VARCHAR2(50 BYTE), 
	"LOGIN_SUCCESSFUL" NUMBER, 
	"LOGIN_DATE" DATE, 
	"IP_ADDRESS" VARCHAR2(256 BYTE), 
	"SESSION_ID" VARCHAR2(256 BYTE)
   );
--------------------------------------------------------
--  DDL for Index IX_LOGIN_LOG_ID
--------------------------------------------------------

  CREATE INDEX "IX_LOGIN_LOG_ID" ON "LOGIN_LOG" ("OPERATOR_ID");

--------------------------------------------------------
--  DDL for Index IX_LOGIN_LOG_DATE
--------------------------------------------------------

  CREATE INDEX "IX_LOGIN_LOG_DATE" ON "LOGIN_LOG" ("LOGIN_DATE");


--------------------------------------------------------
--  ファイルを作成しました - 土曜日-4月-07-2012   
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Table DOWNLOAD_LOG
--------------------------------------------------------

  CREATE TABLE "DOWNLOAD_LOG" 
   (	"OPERATOR_ID" VARCHAR2(50 BYTE), 
	"DOWNLOAD_FILENAME" VARCHAR2(256 BYTE), 
	"DOWNLOAD_COUNT" NUMBER, 
	"DOWNLOAD_DATE" DATE, 
	"IP_ADDRESS" VARCHAR2(256 BYTE), 
	"SESSION_ID" VARCHAR2(256 BYTE)
   );
--------------------------------------------------------
--  DDL for Index IX_DOWNLOAD_LOG_ID
--------------------------------------------------------

  CREATE INDEX "IX_DOWNLOAD_LOG_ID" ON "DOWNLOAD_LOG" ("OPERATOR_ID");
--------------------------------------------------------
--  DDL for Index IX_DOWNLOAD_LOG_DATE
--------------------------------------------------------

  CREATE INDEX "IX_DOWNLOAD_LOG_DATE" ON "DOWNLOAD_LOG" ("DOWNLOAD_DATE");


--------------------------------------------------------
--  ファイルを作成しました - 土曜日-4月-07-2012   
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Procedure EXECUTE_LOGIN
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "EXECUTE_LOGIN" 
(
  P_OPERATOR_ID IN VARCHAR2
, P_SUCCESSFUL IN NUMBER
, P_FAILED_COUNT IN NUMBER
, P_LOCK_FLAG IN NUMBER
, P_IP_ADDRESS IN VARCHAR2
, P_SESSION_ID IN VARCHAR2
)
AS 
BEGIN
  IF P_SUCCESSFUL = 1 Then
    UPDATE MST_USER SET
      FAILED_COUNT = 0,
      LAST_LOGIN_DATE2 = LAST_LOGIN_DATE,
      LAST_LOGIN_DATE = sysdate()
    WHERE OPERATOR_ID = P_OPERATOR_ID;
    
    INSERT INTO LOGIN_LOG (
        OPERATOR_ID,
        LOGIN_SUCCESSFUL,
        LOGIN_DATE,
        IP_ADDRESS,
        SESSION_ID) 
      VALUES (
        P_OPERATOR_ID,
        1,
        sysdate(),
        P_IP_ADDRESS,
        P_SESSION_ID
     );
    
  ELSE
    UPDATE MST_USER SET
      FAILED_COUNT = P_FAILED_COUNT,
      LOCK_FLAG = P_LOCK_FLAG
    WHERE OPERATOR_ID = P_OPERATOR_ID;
    
    INSERT INTO LOGIN_LOG (
        OPERATOR_ID,
        LOGIN_SUCCESSFUL,
        LOGIN_DATE,
        IP_ADDRESS,
        SESSION_ID) 
      VALUES (
        P_OPERATOR_ID,
        0,
        sysdate(),
        P_IP_ADDRESS,
        P_SESSION_ID
     );
  END IF;
END EXECUTE_LOGIN;

/
--------------------------------------------------------
--  DDL for Procedure INSERT_DOWNLOAD_LOG
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "INSERT_DOWNLOAD_LOG" 
(
  P_OPERATOR_ID IN VARCHAR2
, P_DOWNLOAD_FILENAME IN VARCHAR2
, P_DOWNLOAD_COUNT IN NUMBER
, P_IP_ADDRESS IN VARCHAR2
, P_SESSION_ID IN VARCHAR2
)
AS 
BEGIN
  INSERT INTO DOWNLOAD_LOG(
    OPERATOR_ID,
    DOWNLOAD_FILENAME,
    DOWNLOAD_COUNT,
    DOWNLOAD_DATE,
    IP_ADDRESS,
    SESSION_ID
  ) VALUES (
    P_OPERATOR_ID,
    P_DOWNLOAD_FILENAME,
    P_DOWNLOAD_COUNT,
    sysdate(),
    P_IP_ADDRESS,
    P_SESSION_ID
  );
END INSERT_DOWNLOAD_LOG;

/
--------------------------------------------------------
--  DDL for Procedure INSERT_MST_USER
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "INSERT_MST_USER" 
(
  P_OPERATOR_ID IN VARCHAR2
, P_PASSWORD IN VARCHAR2  
, P_OPERATOR_NAME IN VARCHAR2
, P_ROLE IN VARCHAR2
, P_ROLE_TEXT IN VARCHAR2
)
AS 
BEGIN
  INSERT INTO MST_USER(
    OPERATOR_ID,
 		PASSWORD,
		OPERATOR_NAME,
    ROLE,
    ROLE_TEXT,
		LAST_PASSWORD_CHANGE,
		INITIAL_PASSWORD,
		LOCK_FLAG,
		FAILED_COUNT,
		DATE_CREATED,
		DISABLED
  ) VALUES (
    P_OPERATOR_ID,
    P_PASSWORD,
		P_OPERATOR_NAME,
    P_ROLE,
    P_ROLE_TEXT,
		sysdate(),
		1,
		0,
		0,
		sysdate(),
		0
  );
END INSERT_MST_USER;

/
--------------------------------------------------------
--  DDL for Procedure INSERT_REQUEST
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "INSERT_REQUEST" 
(
  P_SEQ_ID OUT NUMBER
, P_SEI_KANJI IN VARCHAR2  
, P_MEI_KANJI IN VARCHAR2
, P_SEI_KANA IN VARCHAR2
, P_MEI_KANA IN VARCHAR2
, P_BIRTH_DATE IN VARCHAR2
, P_GENDER IN NUMBER
, P_GENDER_TEXT IN VARCHAR2
, P_ZIP1 IN VARCHAR2
, P_ZIP2 IN VARCHAR2
, P_ADDRESS1 IN VARCHAR2
, P_ADDRESS2 IN VARCHAR2
, P_ADDRESS3 IN VARCHAR2
, P_ADDRESS4 IN VARCHAR2
, P_TEL1 IN VARCHAR2
, P_TEL2 IN VARCHAR2
, P_TEL3 IN VARCHAR2
, P_M_TEL1 IN VARCHAR2
, P_M_TEL2 IN VARCHAR2
, P_M_TEL3 IN VARCHAR2
, P_E_MAIL IN VARCHAR2
, P_OCCUPATION IN NUMBER
, P_OCCUPATION_TEXT IN VARCHAR2
, P_IP_ADDRESS IN VARCHAR2
, P_SESSION_ID IN VARCHAR2
) AS 
BEGIN
  INSERT INTO REQUEST_DATA (
    SEQ_ID,
    SEI_KANJI,
    MEI_KANJI,
    SEI_KANA,
    MEI_KANA,
    BIRTH_DATE,
    GENDER,
    GENDER_TEXT,
    ZIP1,
    ZIP2,
    ADDRESS1,
    ADDRESS2,
    ADDRESS3,
    ADDRESS4,
    TEL1,
    TEL2,
    TEL3,
    M_TEL1,
    M_TEL2,
    M_TEL3,
    E_MAIL,
    OCCUPATION,
    OCCUPATION_TEXT,
    REQUEST_DATE,
    IP_ADDRESS,
    SESSION_ID,
    STATUS,
    STATUS_TEXT
  ) VALUES (
    SEQ_REQUEST_SEQ_ID.NEXTVAL,
    P_SEI_KANJI,
    P_MEI_KANJI,
    P_SEI_KANA,
    P_MEI_KANA,
    P_BIRTH_DATE,
    P_GENDER,
    P_GENDER_TEXT,
    P_ZIP1,
    P_ZIP2,
    P_ADDRESS1,
    P_ADDRESS2,
    P_ADDRESS3,
    P_ADDRESS4,
    P_TEL1,
    P_TEL2,
    P_TEL3,
    P_M_TEL1,
    P_M_TEL2,
    P_M_TEL3,
    P_E_MAIL,
    P_OCCUPATION,
    P_OCCUPATION_TEXT,
    sysdate(),
    P_IP_ADDRESS,
    P_SESSION_ID,
    0,
    '正常'   
  );
  
  P_SEQ_ID := SEQ_REQUEST_SEQ_ID.CURRVAL;
  
END;

/
--------------------------------------------------------
--  DDL for Procedure INSERT_STATUS_HISTORY
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "INSERT_STATUS_HISTORY" 
(
  P_SEQ_ID IN NUMBER
, P_NEW_STATUS IN NUMBER  
, P_NEW_STATUS_TEXT IN VARCHAR2
, P_OLD_STATUS IN NUMBER
, P_OLD_STATUS_TEXT IN VARCHAR2
, P_COMMENT IN VARCHAR2
, P_OPERATOR_ID IN VARCHAR2
)
AS
BEGIN

  UPDATE REQUEST_DATA
    SET STATUS = P_NEW_STATUS,
    STATUS_TEXT = P_NEW_STATUS_TEXT
    WHERE SEQ_ID = P_SEQ_ID;

  INSERT INTO STATUS_HISTORY(
    SEQ_ID,
    OLD_STATUS,
    OLD_STATUS_TEXT,
    NEW_STATUS,
    NEW_STATUS_TEXT,
    CHANGE_COMMENT,
    CREATE_DATE,
    OPERATOR_ID
  ) VALUES (
    P_SEQ_ID,
    P_OLD_STATUS,
    P_OLD_STATUS_TEXT,
    P_NEW_STATUS,
    P_NEW_STATUS_TEXT,
    P_COMMENT,
    sysdate(),
    P_OPERATOR_ID
  );
  
END INSERT_STATUS_HISTORY;

/
--------------------------------------------------------
--  DDL for Procedure UPDATE_MST_USER
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "UPDATE_MST_USER" 
(
  P_OPERATOR_ID IN VARCHAR2
, P_OPERATOR_NAME IN VARCHAR2
, P_ROLE IN VARCHAR2
, P_ROLE_TEXT IN VARCHAR2
, P_LOCK_FLAG IN NUMBER
, P_DISABLED IN NUMBER
)
AS 
BEGIN
  UPDATE MST_USER
    SET
    OPERATOR_NAME = P_OPERATOR_NAME,
    ROLE = P_ROLE,
    ROLE_TEXT = P_ROLE_TEXT,
    LOCK_FLAG = P_LOCK_FLAG,
    DISABLED = P_DISABLED
  WHERE OPERATOR_ID = P_OPERATOR_ID;
END UPDATE_MST_USER;

/
--------------------------------------------------------
--  DDL for Procedure UPDATE_PASSWORD
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "UPDATE_PASSWORD" 
(
  P_OPERATOR_ID IN VARCHAR2
, P_INITIAL_PASSWORD IN NUMBER
, P_PASSWORD IN VARCHAR2
)
AS 
BEGIN
  UPDATE MST_USER SET
    INITIAL_PASSWORD = P_INITIAL_PASSWORD,
    PASSWORD = P_PASSWORD
  WHERE OPERATOR_ID = P_OPERATOR_ID;
END UPDATE_PASSWORD;

/

commit;
