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

    -- 例外処理
    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,
                                    'hco_to_mdb_launderingでエラーが発生', @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,
                            '【洗替】４：メルク施設コードの洗替_A① 開始');

    TRUNCATE TABLE internal05.hco_cnv_mdb_t;

    CALL medaca_common.put_info_log(schema_name, procedure_name, procedure_args,
                            '【洗替】４：メルク施設コードの洗替_A① 終了');

    CALL medaca_common.put_info_log(schema_name, procedure_name, procedure_args,
                            '【洗替】４：メルク施設コードの洗替_A② 開始');

    INSERT INTO 
        internal05.hco_cnv_mdb_t (
            hco_vid_v,
            mdb_cd,
            form_inst_name_kana,
            form_inst_name_kanji,
            inst_addr,
            prefc_cd,
            delete_flg,
            abolish_ymd,
            start_date
        )
    SELECT
        mcmv.hco_vid_v,
        mcmv.mdb_cd,
        ci.form_inst_name_kana,
        ci.form_inst_name_kanji,
        ci.inst_addr,
        ci.prefc_cd,
        ci.delete_flg,
        ci.abolish_ymd,
        mcmv.start_date
    FROM
        src05.mdb_cnv_mst_v AS mcmv
    INNER JOIN (
            SELECT
                hco_vid_v,MAX(sub_num) AS sno
            FROM
                src05.mdb_cnv_mst_v
            WHERE
                    rec_sts_kbn != '9'
                AND src05.get_syor_date() >= START_DATE
            GROUP BY hco_vid_v
        ) AS mcmv2
        ON  mcmv.hco_vid_v = mcmv2.hco_vid_v
        AND mcmv.sub_num = mcmv2.sno
    LEFT OUTER JOIN src05.com_inst AS ci
        ON  mcmv.mdb_cd = ci.dcf_dsf_inst_cd
        AND ci.delete_flg = '0'
    ;

    CALL medaca_common.put_info_log(schema_name, procedure_name, procedure_args,
                            '【洗替】４：メルク施設コードの洗替_A② 終了');

    CALL medaca_common.put_info_log(schema_name, procedure_name, procedure_args,
                            '【洗替】４：メルク施設コードの洗替_A③ 開始');

    SET @update_institution = "
        UPDATE $$target_table$$ AS tt, internal05.hco_cnv_mdb_t AS hcmt
        SET
            tt.inst_cd = hcmt.mdb_cd,
            tt.inst_name_kana = hcmt.form_inst_name_kana,
            tt.inst_name = hcmt.form_inst_name_kanji,
            tt.address = hcmt.inst_addr,
            tt.pref_cd = hcmt.prefc_cd
        WHERE
                tt.v_inst_cd = hcmt.hco_vid_v
            AND tt.inst_clas_cd = '1'
    ";
    SET @update_institution = REPLACE(@update_institution, "$$target_table$$", target_table);
    PREPARE update_institution_stmt from @update_institution;
    EXECUTE update_institution_stmt;

    CALL medaca_common.put_info_log(schema_name, procedure_name, procedure_args,
                            '【洗替】４：メルク施設コードの洗替_A③ 終了');

END
