-- 
-- 部門付き担当者別中分類別年月別売上予実績リスト
-- 
DROP PROCEDURE IF EXISTS proc_sales_budget_and_actual_results_by_lv2_ym
;
CREATE PROCEDURE proc_sales_budget_and_actual_results_by_lv2_ym() 
BEGIN
    -- 同じ名前のテンポラリテーブルがあれば削除
    DROP TEMPORARY TABLE IF EXISTS temp_sales_budget_and_actual_results_by_lv2_ym;

    -- テンポラリテーブル作成
    CREATE TEMPORARY TABLE temp_sales_budget_and_actual_results_by_lv2_ym (
        category_lv2_id INT,                          -- 中分類ID
        category_lv2_name  VARCHAR(100),              -- 中分類名
        category_lv2_sort_order INT(5),               -- 中分類表示順
        target_year  VARCHAR(4),                      -- 対象年
        target_month VARCHAR(6),                      -- 対象年月
        sales_budget_amount DECIMAL(18, 4),           -- 売上目標
        sales_amount DECIMAL(18, 4),                  -- 総売上額
        return_amount DECIMAL(18, 4),                 -- 返品額
        normal_discount_amount DECIMAL(18, 4),        -- 通常値引額
        policy_discount_amount DECIMAL(18, 4),        -- 政策値引額
        discount_amount DECIMAL(18, 4),               -- 値引合計
        amount DECIMAL(18, 4),                        -- 純売上額
        achievement_rate DECIMAL(18, 4),              -- 売上達成率
        gross_profit_budget_amount DECIMAL(18, 4),    -- 粗利目標
        gross_profit_amount DECIMAL(18, 4),           -- 粗利額
        gross_profit_achievement_rate DECIMAL(18, 4)  -- 粗利達成率
    );

    -- 対象データを挿入する 
    INSERT INTO temp_sales_budget_and_actual_results_by_lv2_ym(
        category_lv2_id,
        category_lv2_name,
        category_lv2_sort_order,
        target_year,
        target_month,
        sales_budget_amount,
        sales_amount,
        return_amount,
        normal_discount_amount,
        policy_discount_amount,
        discount_amount,
        amount,
        achievement_rate,
        gross_profit_budget_amount,
        gross_profit_amount,
        gross_profit_achievement_rate
    ) 
    SELECT 
        category_lv2_id,
        category_lv2_name,
        category_lv2_sort_order,
        target_year,
        target_month,
        sales_budget_amount,
        sales_amount,
        return_amount,
        normal_discount_amount,
        policy_discount_amount,
        discount_amount,
        amount,
        achievement_rate,
        gross_profit_budget_amount,
        gross_profit_amount,
        gross_profit_achievement_rate 
    FROM v_sales_budget_and_actual_results_by_lv2_ym;
END
;