-- A5M2で実行時に[SQL] - [スラッシュ(/)のみの行でSQLを区切る]に変えてから実行する
CREATE PROCEDURE src05.v_inst_merge_t_create()
SQL SECURITY INVOKER
BEGIN
    -- スキーマ名
    DECLARE schema_name VARCHAR(50) DEFAULT (SELECT DATABASE());
    -- プロシージャ名
    DECLARE procedure_name VARCHAR(100) DEFAULT 'v_inst_merge_t_create';
    -- プロシージャの引数
    DECLARE procedure_args JSON DEFAULT JSON_OBJECT();	

    -- 例外処理
    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
        BEGIN
            GET DIAGNOSTICS CONDITION 1
                @error_state = RETURNED_SQLSTATE, @error_msg = MESSAGE_TEXT;
            CALL medaca_common.put_error_log(schema_name, procedure_name, procedure_args,
                                    'v_inst_merge_t_createでエラーが発生', @error_state, @error_msg);
            SET @error_msg = (
                CASE
                WHEN LENGTH(@error_msg) > 128 THEN CONCAT(SUBSTRING(@error_msg, 1, 125), '...')
                ELSE @error_msg
                END
            );
            SIGNAL SQLSTATE '45000'
                SET MYSQL_ERRNO = @error_state, MESSAGE_TEXT = @error_msg;
        END;

    SET @error_state = NULL, @error_msg = NULL;

    CALL medaca_common.put_info_log(schema_name, procedure_name, procedure_args,
                            'V施設統合マスタ（洗替処理一時テーブル）作成① 開始');

    TRUNCATE TABLE internal05.v_inst_merge_t;

    CALL medaca_common.put_info_log(schema_name, procedure_name, procedure_args,
                            'V施設統合マスタ（洗替処理一時テーブル）作成① 終了');

    CALL medaca_common.put_info_log(schema_name, procedure_name, procedure_args,
                            'V施設統合マスタ（洗替処理一時テーブル）作成② 開始');

    INSERT INTO
        internal05.v_inst_merge_t (
            v_inst_cd,
            v_inst_cd_merge,
            fcl_name,
            fcl_kn_name,
            fmt_addr,
            prft_cd,
            fcl_type
        )
    SELECT
        vhmv.v_inst_cd,
        vhmv.v_inst_cd_merg,
        fmv.fcl_name,
        fmv.fcl_kn_name,
        fmv.fmt_addr,
        fmv.prft_cd,
        fmv.fcl_type
    FROM
        src05.vop_hco_merge_v AS vhmv,
        src05.fcl_mst_v AS fmv
    INNER JOIN (
        SELECT
            v_inst_cd,
            MAX(sub_num) AS sno
        FROM
            src05.fcl_mst_v
        GROUP BY
            v_inst_cd
        ) AS max_sno_fmv
        ON fmv.v_inst_cd = max_sno_fmv.v_inst_cd
        AND fmv.sub_num = max_sno_fmv.sno
    WHERE
            vhmv.v_inst_cd_merg = fmv.v_inst_cd
        AND STR_TO_DATE(vhmv.apply_dt, '%Y-%m-%d') <= src05.get_syor_date()
        AND fmv.rec_sts_kbn != '9';

    CALL medaca_common.put_info_log(schema_name, procedure_name, procedure_args,
                            'V施設統合マスタ（洗替処理一時テーブル）作成② 終了' );

END
