-- A5M2で実行時に[SQL] - [スラッシュ(/)のみの行でSQLを区切る]に変えてから実行する
CREATE PROCEDURE src05.whs_org_laundering(target_table VARCHAR(255))
SQL SECURITY INVOKER
BEGIN
    -- スキーマ名
    DECLARE schema_name VARCHAR(50) DEFAULT (SELECT DATABASE());
    -- プロシージャ名
    DECLARE procedure_name VARCHAR(100) DEFAULT 'whs_org_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,
                                    'whs_org_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,
                            '【洗替】１．卸組織洗替① 開始' 
    );

    TRUNCATE TABLE internal05.whs_customer_org_t;

    CALL medaca_common.put_info_log(schema_name, procedure_name, procedure_args,
                            '【洗替】１．卸組織洗替① 終了'
    );

    CALL medaca_common.put_info_log(schema_name, procedure_name, procedure_args,
                            '【洗替】１．卸組織洗替② 開始'
    );

    INSERT INTO
        internal05.whs_customer_org_t (
            whs_cd,
            whs_sub_cd,
            customer_cd,
            whs_org_cd,
            v_org_cd,
            name_2
        ) 
    SELECT
        wcmv.whs_cd,
        wcmv.whs_sub_cd,
        wcmv.customer_cd,
        wcmv.whs_org_cd,
        ocmv.v_org_cd,
        mohv2.name_2
    FROM
        src05.whs_customer_mst_v AS wcmv
    LEFT OUTER JOIN src05.org_cnv_mst_v AS ocmv
        ON wcmv.whs_cd = ocmv.whs_cd
        AND wcmv.whs_sub_cd = ocmv.whs_sub_cd
        AND wcmv.whs_org_cd = ocmv.org_cd
        AND src05.get_syor_date() BETWEEN ocmv.start_date AND ocmv.end_date
        AND ocmv.rec_sts_kbn != '9'
    LEFT OUTER JOIN (
            SELECT
                mohv.v_cd_2,
                mohv.name_2
            FROM src05.mkr_org_horizon_v AS mohv
            INNER JOIN (
                SELECT
                    v_cd_2,
                    MAX(dwh_upd_dt) AS dwh_upd_dt_latest
                FROM
                    src05.mkr_org_horizon_v
                WHERE
                        rec_sts_kbn != '9'
                    AND src05.get_syor_date() BETWEEN start_date AND end_date
                GROUP BY
                    v_cd_2
                ORDER BY
                    MAX(start_date) DESC
            ) AS m_latest
            ON mohv.v_cd_2 = m_latest.v_cd_2
            AND mohv.dwh_upd_dt = m_latest.dwh_upd_dt_latest
            WHERE
                    mohv.rec_sts_kbn != '9'
                AND src05.get_syor_date() BETWEEN mohv.start_date AND mohv.end_date
        ) AS mohv2
        ON ocmv.v_org_cd = mohv2.v_cd_2
    WHERE
            wcmv.rec_sts_kbn != '9'
        AND src05.get_syor_date() BETWEEN wcmv.start_date AND wcmv.end_date
    ;

    CALL medaca_common.put_info_log(schema_name, procedure_name, procedure_args,
                            '【洗替】１．卸組織洗替② 終了'
    );

    CALL medaca_common.put_info_log(schema_name, procedure_name, procedure_args,
                            '【洗替】１．卸組織洗替③ 開始'
    );

    SET @update_organization = "
        UPDATE
            $$target_table$$ AS tt, internal05.whs_customer_org_t AS wcot
        SET
            tt.whs_org_cd = wcot.whs_org_cd,
            tt.v_whsorg_cd = wcot.v_org_cd,
            tt.whs_org_name = wcot.name_2
        WHERE
                wcot.whs_cd = tt.rec_whs_cd
            AND wcot.whs_sub_cd = tt.rec_whs_sub_cd
            AND wcot.customer_cd = tt.rec_cust_cd
    ";
    SET @update_organization = REPLACE(@update_organization, "$$target_table$$", target_table);
    PREPARE update_organization_stmt from @update_organization;
    EXECUTE update_organization_stmt;

    CALL medaca_common.put_info_log(schema_name, procedure_name, procedure_args,
                            '【洗替】１．卸組織洗替③ 終了'
    );

END