-- 
-- MosP - Mind Open Source Project    http://www.mosp.jp/
-- Copyright (C) MIND Co., Ltd.       http://www.e-mind.co.jp/
-- 
-- This program is free software; you can redistribute it and/or
-- modify it under the terms of the GNU General Public License
-- as published by the Free Software Foundation; either version 2
-- of the License, or (at your option) any later version.
-- 
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
-- GNU General Public License for more details.
-- 
-- You should have received a copy of the GNU General Public License
-- along with this program; if not, write to the Free Software
-- Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
-- 
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = public, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;

DROP TABLE ct_holidayrequest;
DROP TABLE pm_holidayrequest;

-- Table: cm_agreement_over_time

DROP TABLE cm_agreement_over_time;

CREATE TABLE cm_agreement_over_time
(
  over_time_id integer NOT NULL DEFAULT 0, -- 時間外協定id
  over_time_code character varying(10) NOT NULL DEFAULT ''::character varying, -- 時間外協定コード
  work_type_name character varying(50) NOT NULL DEFAULT ''::character varying, -- 業務の種類
  cause text NOT NULL, -- 具体的事由
  agree_type character varying(10) NOT NULL DEFAULT ''::character varying, -- 協定種類
  limit_code character varying(10) NOT NULL DEFAULT ''::character varying, -- 限度基準コード
  over_time_rate integer NOT NULL DEFAULT 0, -- 時間外割増賃金率
  caution_month integer NOT NULL DEFAULT 0, -- 月60時間時間外警告時間
  monthly_over_time_rate integer NOT NULL DEFAULT 0, -- 月60時間時間外割増賃金率
  sub_acquire_rate integer NOT NULL DEFAULT 0, -- 代替休暇取得割増賃金率
  sub_acquire_holiday_rate integer NOT NULL DEFAULT 0, -- 代替休暇取得休日割増賃金率
  sub_quit_rate integer NOT NULL DEFAULT 0, -- 代替休暇放棄割増賃金率
  start_date date NOT NULL, -- 開始年月日
  delete_flag integer NOT NULL DEFAULT 0, -- 削除フラグ
  insert_date timestamp without time zone NOT NULL, -- 作成日
  insert_user character varying(10) NOT NULL DEFAULT ''::character varying, -- 作成者
  update_date timestamp without time zone NOT NULL, -- 更新日
  update_user character varying(10) NOT NULL DEFAULT ''::character varying, -- 更新者
  CONSTRAINT cm_agreement_over_time_pkey PRIMARY KEY (over_time_id),
  CONSTRAINT cm_agreement_over_time_caution_month_check CHECK (caution_month >= 0),
  CONSTRAINT cm_agreement_over_time_delete_flag_check CHECK (delete_flag >= 0),
  CONSTRAINT cm_agreement_over_time_monthly_over_time_rate_check CHECK (monthly_over_time_rate >= 0),
  CONSTRAINT cm_agreement_over_time_over_time_id_check CHECK (over_time_id >= 0),
  CONSTRAINT cm_agreement_over_time_over_time_rate_check CHECK (over_time_rate >= 0),
  CONSTRAINT cm_agreement_over_time_sub_acquire_holiday_rate_check CHECK (sub_acquire_holiday_rate >= 0),
  CONSTRAINT cm_agreement_over_time_sub_acquire_rate_check CHECK (sub_acquire_rate >= 0),
  CONSTRAINT cm_agreement_over_time_sub_quit_rate_check CHECK (sub_quit_rate >= 0)
);

COMMENT ON TABLE cm_agreement_over_time IS '時間外協定マスタ';
COMMENT ON COLUMN cm_agreement_over_time.over_time_id IS '時間外協定id';
COMMENT ON COLUMN cm_agreement_over_time.over_time_code IS '時間外協定コード';
COMMENT ON COLUMN cm_agreement_over_time.work_type_name IS '業務の種類';
COMMENT ON COLUMN cm_agreement_over_time.cause IS '具体的事由';
COMMENT ON COLUMN cm_agreement_over_time.agree_type IS '協定種類';
COMMENT ON COLUMN cm_agreement_over_time.limit_code IS '限度基準コード';
COMMENT ON COLUMN cm_agreement_over_time.over_time_rate IS '時間外割増賃金率';
COMMENT ON COLUMN cm_agreement_over_time.caution_month IS '月60時間時間外警告時間';
COMMENT ON COLUMN cm_agreement_over_time.monthly_over_time_rate IS '月60時間時間外割増賃金率';
COMMENT ON COLUMN cm_agreement_over_time.sub_acquire_rate IS '代替休暇取得割増賃金率';
COMMENT ON COLUMN cm_agreement_over_time.sub_acquire_holiday_rate IS '代替休暇取得休日割増賃金率';
COMMENT ON COLUMN cm_agreement_over_time.sub_quit_rate IS '代替休暇放棄割増賃金率';
COMMENT ON COLUMN cm_agreement_over_time.start_date IS '開始年月日';
COMMENT ON COLUMN cm_agreement_over_time.delete_flag IS '削除フラグ';
COMMENT ON COLUMN cm_agreement_over_time.insert_date IS '作成日';
COMMENT ON COLUMN cm_agreement_over_time.insert_user IS '作成者';
COMMENT ON COLUMN cm_agreement_over_time.update_date IS '更新日';
COMMENT ON COLUMN cm_agreement_over_time.update_user IS '更新者';

-- Table: cm_agreement_work_on

DROP TABLE cm_agreement_work_on;

CREATE TABLE cm_agreement_work_on
(
  work_on_id integer NOT NULL DEFAULT 0, -- 休出協定id
  work_on_code character varying(10) NOT NULL DEFAULT ''::character varying, -- 休出協定コード
  work_type_name character varying(50) NOT NULL DEFAULT ''::character varying, -- 業務の種類
  cause text NOT NULL, -- 具体的事由
  standard_week_holiday character varying(14) NOT NULL DEFAULT ''::character varying, -- 所定休日
  official_holiday character varying(1) NOT NULL DEFAULT ''::character varying, -- 法定休日
  japanese_holiday_flag character varying(1) NOT NULL DEFAULT ''::character varying, -- 国民の祝日フラグ
  holiday_master_flag character varying(1) NOT NULL DEFAULT ''::character varying, -- 祝祭日マスタフラグ
  standard_holiday_rate integer NOT NULL DEFAULT 0, -- 所定休日割増賃金率
  official_holiday_rate integer NOT NULL DEFAULT 0, -- 法定休日割増賃金率
  number_of_work_on integer NOT NULL DEFAULT 0, -- 休出日数
  start_time integer NOT NULL DEFAULT 0, -- 開始時刻
  end_time integer NOT NULL DEFAULT 0, -- 終了時刻
  start_date date NOT NULL, -- 開始年月日
  delete_flag integer NOT NULL DEFAULT 0, -- 削除フラグ
  insert_date timestamp without time zone NOT NULL, -- 作成日
  insert_user character varying(10) NOT NULL DEFAULT ''::character varying, -- 作成者
  update_date timestamp without time zone NOT NULL, -- 更新日
  update_user character varying(10) NOT NULL DEFAULT ''::character varying, -- 更新者
  CONSTRAINT cm_agreement_work_on_pkey PRIMARY KEY (work_on_id),
  CONSTRAINT cm_agreement_work_on_delete_flag_check CHECK (delete_flag >= 0),
  CONSTRAINT cm_agreement_work_on_end_time_check CHECK (end_time >= 0),
  CONSTRAINT cm_agreement_work_on_number_of_work_on_check CHECK (number_of_work_on >= 0),
  CONSTRAINT cm_agreement_work_on_official_holiday_rate_check CHECK (official_holiday_rate >= 0),
  CONSTRAINT cm_agreement_work_on_standard_holiday_rate_check CHECK (standard_holiday_rate >= 0),
  CONSTRAINT cm_agreement_work_on_start_time_check CHECK (start_time >= 0),
  CONSTRAINT cm_agreement_work_on_work_on_id_check CHECK (work_on_id >= 0)
);

COMMENT ON TABLE cm_agreement_work_on IS '休出協定マスタ';
COMMENT ON COLUMN cm_agreement_work_on.work_on_id IS '休出協定id';
COMMENT ON COLUMN cm_agreement_work_on.work_on_code IS '休出協定コード';
COMMENT ON COLUMN cm_agreement_work_on.work_type_name IS '業務の種類';
COMMENT ON COLUMN cm_agreement_work_on.cause IS '具体的事由';
COMMENT ON COLUMN cm_agreement_work_on.standard_week_holiday IS '所定休日';
COMMENT ON COLUMN cm_agreement_work_on.official_holiday IS '法定休日';
COMMENT ON COLUMN cm_agreement_work_on.japanese_holiday_flag IS '国民の祝日フラグ';
COMMENT ON COLUMN cm_agreement_work_on.holiday_master_flag IS '祝祭日マスタフラグ';
COMMENT ON COLUMN cm_agreement_work_on.standard_holiday_rate IS '所定休日割増賃金率';
COMMENT ON COLUMN cm_agreement_work_on.official_holiday_rate IS '法定休日割増賃金率';
COMMENT ON COLUMN cm_agreement_work_on.number_of_work_on IS '休出日数';
COMMENT ON COLUMN cm_agreement_work_on.start_time IS '開始時刻';
COMMENT ON COLUMN cm_agreement_work_on.end_time IS '終了時刻';
COMMENT ON COLUMN cm_agreement_work_on.start_date IS '開始年月日';
COMMENT ON COLUMN cm_agreement_work_on.delete_flag IS '削除フラグ';
COMMENT ON COLUMN cm_agreement_work_on.insert_date IS '作成日';
COMMENT ON COLUMN cm_agreement_work_on.insert_user IS '作成者';
COMMENT ON COLUMN cm_agreement_work_on.update_date IS '更新日';
COMMENT ON COLUMN cm_agreement_work_on.update_user IS '更新者';

-- Table: kd_paid_holiday

DROP TABLE kd_paid_holiday;

CREATE TABLE kd_paid_holiday
(
  paid_id bigint NOT NULL DEFAULT 0, -- 有休id
  k_code character varying(10) NOT NULL DEFAULT ''::character varying, -- 社員コード
  process_type character varying(10) NOT NULL DEFAULT ''::character varying, -- 処理コード
  process_date date NOT NULL, -- 支給日
  last_remain_days double precision NOT NULL DEFAULT 0, -- 前年度残日数
  last_remain_hour integer NOT NULL DEFAULT 0, -- 前年度残時間
  remain_days double precision NOT NULL DEFAULT 0, -- 今年度残日数
  remain_hour integer NOT NULL DEFAULT 0, -- 今年度残時間
  possible integer NOT NULL DEFAULT 0, -- 時間単位取得可能日数
  delete_flag integer NOT NULL DEFAULT 0, -- 削除フラグ
  insert_date timestamp without time zone NOT NULL, -- 作成日
  insert_user character varying(10) NOT NULL DEFAULT ''::character varying, -- 作成者
  update_date timestamp without time zone NOT NULL, -- 更新日
  update_user character varying(10) NOT NULL DEFAULT ''::character varying, -- 更新者
  CONSTRAINT kd_paid_holiday_pkey PRIMARY KEY (paid_id, k_code),
  CONSTRAINT kd_paid_holiday_delete_flag_check CHECK (delete_flag >= 0),
  CONSTRAINT kd_paid_holiday_last_remain_days_check CHECK (last_remain_days >= 0::double precision),
  CONSTRAINT kd_paid_holiday_last_remain_hour_check CHECK (last_remain_hour >= 0),
  CONSTRAINT kd_paid_holiday_paid_id_check CHECK (paid_id >= 0),
  CONSTRAINT kd_paid_holiday_possible_check CHECK (possible >= 0),
  CONSTRAINT kd_paid_holiday_remain_days_check CHECK (remain_days >= 0::double precision),
  CONSTRAINT kd_paid_holiday_remain_hour_check CHECK (remain_hour >= 0)
);

COMMENT ON TABLE kd_paid_holiday IS '有休管理テーブル';
COMMENT ON COLUMN kd_paid_holiday.paid_id IS '有休id';
COMMENT ON COLUMN kd_paid_holiday.k_code IS '社員コード';
COMMENT ON COLUMN kd_paid_holiday.process_type IS '処理コード';
COMMENT ON COLUMN kd_paid_holiday.process_date IS '支給日';
COMMENT ON COLUMN kd_paid_holiday.last_remain_days IS '前年度残日数';
COMMENT ON COLUMN kd_paid_holiday.last_remain_hour IS '前年度残時間';
COMMENT ON COLUMN kd_paid_holiday.remain_days IS '今年度残日数';
COMMENT ON COLUMN kd_paid_holiday.remain_hour IS '今年度残時間';
COMMENT ON COLUMN kd_paid_holiday.possible IS '時間単位取得可能日数';
COMMENT ON COLUMN kd_paid_holiday.delete_flag IS '削除フラグ';
COMMENT ON COLUMN kd_paid_holiday.insert_date IS '作成日';
COMMENT ON COLUMN kd_paid_holiday.insert_user IS '作成者';
COMMENT ON COLUMN kd_paid_holiday.update_date IS '更新日';
COMMENT ON COLUMN kd_paid_holiday.update_user IS '更新者';

-- Table: kd_substitute_holiday

DROP TABLE kd_substitute_holiday;

CREATE TABLE kd_substitute_holiday
(
  substitute_id bigint NOT NULL DEFAULT 0, -- 管理id
  k_code character varying(10) NOT NULL DEFAULT ''::character varying, -- 社員コード
  occur_year integer NOT NULL DEFAULT 0, -- 発生年
  occur_month integer NOT NULL DEFAULT 0, -- 発生月
  close_date date NOT NULL, -- 締切日
  limit_start_date date NOT NULL, -- 期限開始日
  limit_end_date date NOT NULL, -- 期限終了日
  attitude_status character varying(10) NOT NULL DEFAULT ''::character varying, -- 意向状況
  payment_acquire_time integer NOT NULL DEFAULT 0, -- 代替休暇取得時間外
  payment_acquire_holiday integer NOT NULL DEFAULT 0, -- 代替休暇取得休日時間外
  payment_quit_time integer NOT NULL DEFAULT 0, -- 代替休暇放棄時間外
  use_holiday_time integer NOT NULL DEFAULT 0, -- 利用時間
  acquisition_status character varying(10) NOT NULL DEFAULT ''::character varying, -- 取得状況
  liquidation_over_time integer NOT NULL DEFAULT 0, -- 代休決済による時間外清算時間
  liquidation_week_holiday integer NOT NULL DEFAULT 0, -- 代休決済による休日時間外清算時間
  decision_status character varying(10) NOT NULL DEFAULT ''::character varying, -- 決済状況
  delete_flag integer NOT NULL DEFAULT 0, -- 削除フラグ
  insert_date timestamp without time zone NOT NULL, -- 作成日
  insert_user character varying(10) NOT NULL DEFAULT ''::character varying, -- 作成者
  update_date timestamp without time zone NOT NULL, -- 更新日
  update_user character varying(10) NOT NULL DEFAULT ''::character varying, -- 更新者
  CONSTRAINT kd_substitute_holiday_pkey PRIMARY KEY (substitute_id),
  CONSTRAINT kd_substitute_holiday_use_holiday_time_check CHECK (use_holiday_time >= 0),
  CONSTRAINT kd_substitute_holiday_delete_flag_check CHECK (delete_flag >= 0),
  CONSTRAINT kd_substitute_holiday_occur_month_check CHECK (occur_month >= 0),
  CONSTRAINT kd_substitute_holiday_occur_year_check CHECK (occur_year >= 0),
  CONSTRAINT kd_substitute_holiday_payment_acquire_holiday_check CHECK (payment_acquire_holiday >= 0),
  CONSTRAINT kd_substitute_holiday_payment_acquire_time_check CHECK (payment_acquire_time >= 0),
  CONSTRAINT kd_substitute_holiday_payment_quit_time_check CHECK (payment_quit_time >= 0)
);

COMMENT ON TABLE kd_substitute_holiday IS '代替休暇管理テーブル';
COMMENT ON COLUMN kd_substitute_holiday.substitute_id IS '管理id';
COMMENT ON COLUMN kd_substitute_holiday.k_code IS '社員コード';
COMMENT ON COLUMN kd_substitute_holiday.occur_year IS '発生年';
COMMENT ON COLUMN kd_substitute_holiday.occur_month IS '発生月';
COMMENT ON COLUMN kd_substitute_holiday.close_date IS '締切日';
COMMENT ON COLUMN kd_substitute_holiday.limit_start_date IS '期限開始日';
COMMENT ON COLUMN kd_substitute_holiday.limit_end_date IS '期限終了日';
COMMENT ON COLUMN kd_substitute_holiday.attitude_status IS '意向状況';
COMMENT ON COLUMN kd_substitute_holiday.payment_acquire_time IS '代替休暇取得時間外';
COMMENT ON COLUMN kd_substitute_holiday.payment_acquire_holiday IS '代替休暇取得休日時間外';
COMMENT ON COLUMN kd_substitute_holiday.payment_quit_time IS '代替休暇放棄時間外';
COMMENT ON COLUMN kd_substitute_holiday.use_holiday_time IS '利用時間';
COMMENT ON COLUMN kd_substitute_holiday.acquisition_status IS '取得状況';
COMMENT ON COLUMN kd_substitute_holiday.liquidation_over_time IS '代休決済による時間外清算時間';
COMMENT ON COLUMN kd_substitute_holiday.liquidation_week_holiday IS '代休決済による休日時間外清算時間';
COMMENT ON COLUMN kd_substitute_holiday.decision_status IS '決済状況';
COMMENT ON COLUMN kd_substitute_holiday.delete_flag IS '削除フラグ';
COMMENT ON COLUMN kd_substitute_holiday.insert_date IS '作成日';
COMMENT ON COLUMN kd_substitute_holiday.insert_user IS '作成者';
COMMENT ON COLUMN kd_substitute_holiday.update_date IS '更新日';
COMMENT ON COLUMN kd_substitute_holiday.update_user IS '更新者';

-- Table: kd_work

DROP TABLE kd_work;

CREATE TABLE kd_work
(
  k_code character varying(10) NOT NULL DEFAULT ''::character varying, -- 社員コード
  sikyu_ym character varying(8) NOT NULL DEFAULT ''::character varying, -- 支給年月
  s_nen integer NOT NULL DEFAULT 0, -- 支給年
  s_tuki integer NOT NULL DEFAULT 0, -- 支給月
  s_hi integer NOT NULL DEFAULT 0, -- 支給日
  s_nissu double precision NOT NULL DEFAULT 0, -- 出勤日数
  s_kaisu integer NOT NULL DEFAULT 0, -- 出勤回数
  kinmu_ji integer NOT NULL DEFAULT 0, -- 勤務時間
  kyukei_ji integer NOT NULL DEFAULT 0, -- 休憩時間
  yukyu double precision NOT NULL DEFAULT 0, -- 有給
  tokukyu double precision NOT NULL DEFAULT 0, -- 特別休暇
  kekkin double precision NOT NULL DEFAULT 0, -- 一般欠勤日数
  s_kekkin double precision NOT NULL DEFAULT 0, -- 傷病欠勤日数
  daikyu double precision NOT NULL DEFAULT 0, -- 代替休暇
  furikae double precision NOT NULL DEFAULT 0, -- 振替休暇
  kk_riyuu_6 double precision NOT NULL DEFAULT 0, -- 休暇理由6
  kk_riyuu_7 double precision NOT NULL DEFAULT 0, -- 休暇理由7
  kk_riyuu_8 double precision NOT NULL DEFAULT 0, -- 休暇理由8
  kk_riyuu_9 double precision NOT NULL DEFAULT 0, -- 休暇理由9
  sonota double precision NOT NULL DEFAULT 0, -- その他休暇
  ti_so_kai integer NOT NULL DEFAULT 0, -- 遅刻早退回数
  ti_so_jikan integer NOT NULL DEFAULT 0, -- 遅刻早退時間
  za_jikan integer NOT NULL DEFAULT 0, -- 普通残業時間
  ks_jikan integer NOT NULL DEFAULT 0, -- 休日出勤時間
  sn_jikan integer NOT NULL DEFAULT 0, -- 深夜勤務時間
  kaikin integer NOT NULL DEFAULT 0, -- 精皆勤手当支給
  syokuji_kaisu integer NOT NULL DEFAULT 0, -- 食事手当支給回数
  cyoku_kaisu_1 integer NOT NULL DEFAULT 0, -- 1直手当支給回数
  cyoku_kaisu_2 integer NOT NULL DEFAULT 0, -- 2直手当支給回数
  cyoku_kaisu_3 integer NOT NULL DEFAULT 0, -- 3直手当支給回数
  hoka_kaisu_1 integer NOT NULL DEFAULT 0, -- その他支給回数1
  hoka_kaisu_2 integer NOT NULL DEFAULT 0, -- その他支給回数2
  hoka_kaisu_3 integer NOT NULL DEFAULT 0, -- その他支給回数3
  hoka_kaisu_4 integer NOT NULL DEFAULT 0, -- その他支給回数4
  hoka_kaisu_5 integer NOT NULL DEFAULT 0, -- その他支給回数5
  sinsei_gaku integer NOT NULL DEFAULT 0, -- 支払申請額
  warimasi_1 integer NOT NULL DEFAULT 0, -- 1.00割増
  week_day_over_time integer NOT NULL DEFAULT 0, -- 平日時間外
  week_holiday_over_time integer NOT NULL DEFAULT 0, -- 所定休日時間外
  monthly_over_time integer NOT NULL DEFAULT 0, -- 月60時間超過平日時間外
  monthly_week_holiday integer NOT NULL DEFAULT 0, -- 月60時間超過所定休日時間外
  hour_holiday integer NOT NULL DEFAULT 0, -- 時間単位年休取得時間
  substitute_holiday double precision NOT NULL DEFAULT 0, -- 時間外時間代替休暇取得日数
  amend_flag integer NOT NULL DEFAULT 0, -- 修正フラグ
  insert_date timestamp without time zone NOT NULL, -- 作成日
  insert_user character varying(10) NOT NULL DEFAULT ''::character varying, -- 作成者
  update_date timestamp without time zone NOT NULL, -- 更新日
  update_user character varying(10) NOT NULL DEFAULT ''::character varying, -- 更新者
  CONSTRAINT kd_work_pkey PRIMARY KEY (k_code, sikyu_ym)
);

COMMENT ON TABLE kd_work IS '勤怠集計';
COMMENT ON COLUMN kd_work.k_code IS '社員コード';
COMMENT ON COLUMN kd_work.sikyu_ym IS '支給年月';
COMMENT ON COLUMN kd_work.s_nen IS '支給年';
COMMENT ON COLUMN kd_work.s_tuki IS '支給月';
COMMENT ON COLUMN kd_work.s_hi IS '支給日';
COMMENT ON COLUMN kd_work.s_nissu IS '出勤日数';
COMMENT ON COLUMN kd_work.s_kaisu IS '出勤回数';
COMMENT ON COLUMN kd_work.kinmu_ji IS '勤務時間';
COMMENT ON COLUMN kd_work.kyukei_ji IS '休憩時間';
COMMENT ON COLUMN kd_work.yukyu IS '有給';
COMMENT ON COLUMN kd_work.tokukyu IS '特別休暇';
COMMENT ON COLUMN kd_work.kekkin IS '一般欠勤日数';
COMMENT ON COLUMN kd_work.s_kekkin IS '傷病欠勤日数';
COMMENT ON COLUMN kd_work.daikyu IS '代替休暇';
COMMENT ON COLUMN kd_work.furikae IS '振替休暇';
COMMENT ON COLUMN kd_work.kk_riyuu_6 IS '休暇理由6';
COMMENT ON COLUMN kd_work.kk_riyuu_7 IS '休暇理由7';
COMMENT ON COLUMN kd_work.kk_riyuu_8 IS '休暇理由8';
COMMENT ON COLUMN kd_work.kk_riyuu_9 IS '休暇理由9';
COMMENT ON COLUMN kd_work.sonota IS 'その他休暇';
COMMENT ON COLUMN kd_work.ti_so_kai IS '遅刻早退回数';
COMMENT ON COLUMN kd_work.ti_so_jikan IS '遅刻早退時間';
COMMENT ON COLUMN kd_work.za_jikan IS '普通残業時間';
COMMENT ON COLUMN kd_work.ks_jikan IS '休日出勤時間';
COMMENT ON COLUMN kd_work.sn_jikan IS '深夜勤務時間';
COMMENT ON COLUMN kd_work.kaikin IS '精皆勤手当支給';
COMMENT ON COLUMN kd_work.syokuji_kaisu IS '食事手当支給回数';
COMMENT ON COLUMN kd_work.cyoku_kaisu_1 IS '1直手当支給回数';
COMMENT ON COLUMN kd_work.cyoku_kaisu_2 IS '2直手当支給回数';
COMMENT ON COLUMN kd_work.cyoku_kaisu_3 IS '3直手当支給回数';
COMMENT ON COLUMN kd_work.hoka_kaisu_1 IS 'その他支給回数1';
COMMENT ON COLUMN kd_work.hoka_kaisu_2 IS 'その他支給回数2';
COMMENT ON COLUMN kd_work.hoka_kaisu_3 IS 'その他支給回数3';
COMMENT ON COLUMN kd_work.hoka_kaisu_4 IS 'その他支給回数4';
COMMENT ON COLUMN kd_work.hoka_kaisu_5 IS 'その他支給回数5';
COMMENT ON COLUMN kd_work.sinsei_gaku IS '支払申請額';
COMMENT ON COLUMN kd_work.warimasi_1 IS '1.00割増';
COMMENT ON COLUMN kd_work.week_day_over_time IS '平日時間外';
COMMENT ON COLUMN kd_work.week_holiday_over_time IS '所定休日時間外';
COMMENT ON COLUMN kd_work.monthly_over_time IS '月60時間超過平日時間外';
COMMENT ON COLUMN kd_work.monthly_week_holiday IS '月60時間超過所定休日時間外';
COMMENT ON COLUMN kd_work.hour_holiday IS '時間単位年休取得時間';
COMMENT ON COLUMN kd_work.substitute_holiday IS '時間外時間代替休暇取得日数';
COMMENT ON COLUMN kd_work.amend_flag IS '修正フラグ';
COMMENT ON COLUMN kd_work.insert_date IS '作成日';
COMMENT ON COLUMN kd_work.insert_user IS '作成者';
COMMENT ON COLUMN kd_work.update_date IS '更新日';
COMMENT ON COLUMN kd_work.update_user IS '更新者';

-- Table: kt_holiday_request

DROP TABLE kt_holiday_request;

CREATE TABLE kt_holiday_request
(
  voucher integer NOT NULL DEFAULT 0, -- 休暇申請伝票番号
  workflow bigint NOT NULL DEFAULT (0)::bigint, -- ワークフロー番号
  k_code character varying(10) NOT NULL, -- 社員コード
  holiday_date date NOT NULL, -- 休暇日
  start_time integer NOT NULL DEFAULT 0, -- 休暇開始時間
  end_time integer NOT NULL DEFAULT 0, -- 休暇終了時間
  reason character varying(10) NOT NULL, -- 休暇理由
  holiday_type character varying(10) NOT NULL, -- 有給区分
  occur_date_1 character varying(10) NOT NULL, -- 発生年月日1
  use_time_1 integer NOT NULL, -- 利用時間1
  occur_date_2 character varying(10) NOT NULL, -- 発生年月日2
  use_time_2 integer NOT NULL, -- 利用時間2
  substitute_date character varying(10) NOT NULL, -- 充当日
  cause_comment character varying(10) NOT NULL, -- 事由
  delete_flag integer NOT NULL DEFAULT 0, -- 削除フラグ
  insert_date timestamp without time zone NOT NULL, -- 作成日
  insert_user character varying(10) NOT NULL DEFAULT ''::character varying, -- 作成者
  update_date timestamp without time zone NOT NULL, -- 更新日
  update_user character varying(10) NOT NULL DEFAULT ''::character varying, -- 更新者
  CONSTRAINT kt_holiday_request_pkey PRIMARY KEY (voucher),
  CONSTRAINT kt_holiday_request_voucher_check CHECK (voucher >= 0),
  CONSTRAINT kt_holiday_request_workflow_check CHECK (workflow >= 0)
);

COMMENT ON TABLE kt_holiday_request IS '休暇申請テーブル';
COMMENT ON COLUMN kt_holiday_request.voucher IS '休暇申請伝票番号';
COMMENT ON COLUMN kt_holiday_request.workflow IS 'ワークフロー番号';
COMMENT ON COLUMN kt_holiday_request.k_code IS '社員コード';
COMMENT ON COLUMN kt_holiday_request.holiday_date IS '休暇日';
COMMENT ON COLUMN kt_holiday_request.start_time IS '休暇開始時間';
COMMENT ON COLUMN kt_holiday_request.end_time IS '休暇終了時間';
COMMENT ON COLUMN kt_holiday_request.reason IS '休暇理由';
COMMENT ON COLUMN kt_holiday_request.holiday_type IS '有給区分';
COMMENT ON COLUMN kt_holiday_request.occur_date_1 IS '発生年月日1';
COMMENT ON COLUMN kt_holiday_request.use_time_1 IS '利用時間1';
COMMENT ON COLUMN kt_holiday_request.occur_date_2 IS '発生年月日2';
COMMENT ON COLUMN kt_holiday_request.use_time_2 IS '利用時間2';
COMMENT ON COLUMN kt_holiday_request.substitute_date IS '充当日';
COMMENT ON COLUMN kt_holiday_request.cause_comment IS '事由';
COMMENT ON COLUMN kt_holiday_request.delete_flag IS '削除フラグ';
COMMENT ON COLUMN kt_holiday_request.insert_date IS '作成日';
COMMENT ON COLUMN kt_holiday_request.insert_user IS '作成者';
COMMENT ON COLUMN kt_holiday_request.update_date IS '更新日';
COMMENT ON COLUMN kt_holiday_request.update_user IS '更新者';

-- Table: cm_labor_agreement

DROP TABLE cm_labor_agreement;

CREATE TABLE cm_labor_agreement
(
  agree_code character varying(10) NOT NULL DEFAULT ''::character varying, -- 協定コード
  agree_type character varying(15) NOT NULL DEFAULT ''::character varying, -- 協定種類
  over_time_code character varying(10) NOT NULL DEFAULT ''::character varying, -- 時間外協定コード
  work_on_code character varying(10) NOT NULL DEFAULT ''::character varying, -- 休出協定コード
  delete_flag integer NOT NULL DEFAULT 0, -- 削除フラグ
  insert_date timestamp without time zone NOT NULL, -- 作成日
  insert_user character varying(10) NOT NULL DEFAULT ''::character varying, -- 作成者
  update_date timestamp without time zone NOT NULL, -- 更新日
  update_user character varying(10) NOT NULL DEFAULT ''::character varying, -- 更新者
  CONSTRAINT cm_labor_agreement_pkey PRIMARY KEY (agree_code)
);

COMMENT ON TABLE cm_labor_agreement IS '労使協定マスタ';
COMMENT ON COLUMN cm_labor_agreement.agree_code IS '協定コード';
COMMENT ON COLUMN cm_labor_agreement.agree_type IS '協定種類';
COMMENT ON COLUMN cm_labor_agreement.over_time_code IS '時間外協定コード';
COMMENT ON COLUMN cm_labor_agreement.work_on_code IS '休出協定コード';
COMMENT ON COLUMN cm_labor_agreement.delete_flag IS '削除フラグ';
COMMENT ON COLUMN cm_labor_agreement.insert_date IS '作成日';
COMMENT ON COLUMN cm_labor_agreement.insert_user IS '作成者';
COMMENT ON COLUMN cm_labor_agreement.update_date IS '更新日';
COMMENT ON COLUMN cm_labor_agreement.update_user IS '更新者';

-- Table: cm_limit_standard

DROP TABLE cm_limit_standard;

CREATE TABLE cm_limit_standard
(
  limit_code character varying(10) NOT NULL DEFAULT ''::character varying, -- 限度基準コード
  limit_name character varying(50) NOT NULL DEFAULT ''::character varying, -- 限度基準名称
  term_type character varying(10) NOT NULL DEFAULT ''::character varying, -- 期間の種類
  prescribed_time integer NOT NULL DEFAULT 0, -- 所定労働時間
  caution_day integer NOT NULL DEFAULT 0, -- 1日の警告時間
  caution_term integer NOT NULL DEFAULT 0, -- 指定期間の警告時間
  caution_year integer NOT NULL DEFAULT 0, -- 1年の警告時間
  limit_day integer NOT NULL DEFAULT 0, -- 一日の限度時間
  limit_term integer NOT NULL DEFAULT 0, -- 指定期間の限度時間
  limit_year integer NOT NULL DEFAULT 0, -- 1年の限度時間
  rate_term integer NOT NULL DEFAULT 0, -- 指定期間の割増率
  rate_year integer NOT NULL DEFAULT 0, -- 1年の割増率
  delete_flag integer NOT NULL DEFAULT 0, -- 削除フラグ
  insert_date timestamp without time zone NOT NULL, -- 作成日
  insert_user character varying(10) NOT NULL DEFAULT ''::character varying, -- 作成者
  update_date timestamp without time zone NOT NULL, -- 更新日
  update_user character varying(10) NOT NULL DEFAULT ''::character varying, -- 更新者
  CONSTRAINT cm_limit_standard_pkey PRIMARY KEY (limit_code),
  CONSTRAINT cm_limit_standard_caution_day_check CHECK (caution_day >= 0),
  CONSTRAINT cm_limit_standard_caution_term_check CHECK (caution_term >= 0),
  CONSTRAINT cm_limit_standard_caution_year_check CHECK (caution_year >= 0),
  CONSTRAINT cm_limit_standard_limit_day_check CHECK (limit_day >= 0),
  CONSTRAINT cm_limit_standard_limit_term_check CHECK (limit_term >= 0),
  CONSTRAINT cm_limit_standard_limit_year_check CHECK (limit_year >= 0),
  CONSTRAINT cm_limit_standard_prescribed_time_check CHECK (prescribed_time >= 0),
  CONSTRAINT cm_limit_standard_rate_term_check CHECK (rate_term >= 0),
  CONSTRAINT cm_limit_standard_rate_year_check CHECK (rate_year >= 0)
);

COMMENT ON TABLE cm_limit_standard IS '限度基準マスタ';
COMMENT ON COLUMN cm_limit_standard.limit_code IS '限度基準コード';
COMMENT ON COLUMN cm_limit_standard.limit_name IS '限度基準名称';
COMMENT ON COLUMN cm_limit_standard.term_type IS '期間の種類';
COMMENT ON COLUMN cm_limit_standard.prescribed_time IS '所定労働時間';
COMMENT ON COLUMN cm_limit_standard.caution_day IS '1日の警告時間';
COMMENT ON COLUMN cm_limit_standard.caution_term IS '指定期間の警告時間';
COMMENT ON COLUMN cm_limit_standard.caution_year IS '1年の警告時間';
COMMENT ON COLUMN cm_limit_standard.limit_day IS '一日の限度時間';
COMMENT ON COLUMN cm_limit_standard.limit_term IS '指定期間の限度時間';
COMMENT ON COLUMN cm_limit_standard.limit_year IS '1年の限度時間';
COMMENT ON COLUMN cm_limit_standard.rate_term IS '指定期間の割増率';
COMMENT ON COLUMN cm_limit_standard.rate_year IS '1年の割増率';
COMMENT ON COLUMN cm_limit_standard.delete_flag IS '削除フラグ';
COMMENT ON COLUMN cm_limit_standard.insert_date IS '作成日';
COMMENT ON COLUMN cm_limit_standard.insert_user IS '作成者';
COMMENT ON COLUMN cm_limit_standard.update_date IS '更新日';
COMMENT ON COLUMN cm_limit_standard.update_user IS '更新者';

DELETE FROM m_code WHERE 

TID ='給与支給名' OR 
TID ='給与控除名' OR 
TID ='賞与支給名' OR 
TID ='賞与控除名'
;

INSERT INTO m_code VALUES
('限度基準期間', '0', '1週間', '1,week', NOW(), 'admin', NOW(), 'admin'),
('限度基準期間', '1', '2週間', '2,week', NOW(), 'admin', NOW(), 'admin'),
('限度基準期間', '2', '4週間', '4,week', NOW(), 'admin', NOW(), 'admin'),
('限度基準期間', '3', '1ヶ月', '1,month', NOW(), 'admin', NOW(), 'admin'),
('限度基準期間', '4', '2ヶ月', '2,month', NOW(), 'admin', NOW(), 'admin'),
('限度基準期間', '5', '3ヶ月', '3,month', NOW(), 'admin', NOW(), 'admin'),

('協定種類', '0', '１年単位の変形労働時間制に該当しない労働者', '', NOW(), 'admin', NOW(), 'admin'),
('協定種類', '1', '１年単位の変形労働時間制により労働する労働者', '', NOW(), 'admin', NOW(), 'admin'),
('協定種類', '2', '育児・介護休業法上の時間外労働の制限を請求した労働者', '', NOW(), 'admin', NOW(), 'admin'),

('勤怠管理', '丸め処理', '', '0', NOW(), 'admin', NOW(), 'admin'),
('勤怠管理', '計算処理', '', '0', NOW(), 'admin', NOW(), 'admin'),
('勤怠管理', '月60時間時間外機能', '', '1', NOW(), 'admin', NOW(), 'admin'),
('勤怠管理', '代休情報修正', '', '1', NOW(), 'admin', NOW(), 'admin'),
('勤怠管理', '代替休暇機能', '', '1', NOW(), 'admin', NOW(), 'admin'),

('有給休暇', '時間単位年休', '', '1', NOW(), 'admin', NOW(), 'admin'),
('有給休暇', '年休単位時間', '', '', NOW(), 'admin', NOW(), 'admin'),

('承認理由', '5', '下書き', '', NOW(), 'admin', NOW(), 'admin')

;
