-- 
-- 受注残管理表（商品別）
-- 
DROP PROCEDURE IF EXISTS proc_generate_backorder_report_by_product
;
CREATE PROCEDURE proc_generate_backorder_report_by_product() 
BEGIN
    DROP TEMPORARY TABLE IF EXISTS tmp_backlog_management_by_product;

    CALL sp_generate_backorder_report(null, null, null);


    CREATE TEMPORARY TABLE tmp_backlog_management_by_product AS
    SELECT
        order_detail_id,
        order_id,
        slip_number,
        line_no,
        product_id,
        product_code,
        product_name,
        product_character,
        category_name,
        DATE_FORMAT(order_date, '%Y/%m/%d') AS order_date,
        DATE_FORMAT(shipment_date, '%Y/%m/%d') AS shipment_date,
        order_number,
        allocation_policy,
        order_qty,
        backorder_qty,
        unit_price,
        backorder_amount,
        priority_allocation_flag,
        customer_code,
        customer_name,
        sales_rep_code,
        sales_rep_name,
        order_accumulated,
        order_pre_allocated,
        purchase_order_detail_id,
        purchase_order_number,
        DATE_FORMAT(arrival_date, '%Y/%m/%d') AS arrival_date,
        arrival_qty,
        job_number,
        purchase_accumulated,
        stock_control_type,
        available_qty,
        allocation_pattern,
        1 AS total_flg,
        CONCAT(product_code, product_name) AS total_key
    FROM tmp_allocations
    UNION ALL
    SELECT
        NULL AS order_detail_id,
        NULL AS order_id,
        NULL AS slip_number,
        NULL AS line_no,
        NULL AS product_id,
        product_code,
        '【商品計】' AS product_name,
        NULL AS product_character,
        NULL AS category_name,
        NULL AS order_date,
        NULL AS shipment_date,
        NULL AS order_number,
        NULL AS allocation_policy,
        SUM(order_qty) AS order_qty,
        SUM(backorder_qty) AS backorder_qty,
        NULL AS unit_price,
        SUM(backorder_amount) AS backorder_amount,
        NULL AS priority_allocation_flag,
        NULL AS customer_code,
        NULL AS customer_name,
        NULL AS sales_rep_code,
        NULL AS sales_rep_name,
        NULL AS order_accumulated,
        NULL AS order_pre_allocated,
        NULL AS purchase_order_detail_id,
        NULL AS purchase_order_number,
        NULL AS arrival_date,
        SUM(arrival_qty) AS arrival_qty,
        NULL AS job_number,
        SUM(purchase_accumulated) AS purchase_accumulated,
        NULL AS stock_control_type,
        SUM(available_qty) AS available_qty,
        NULL AS allocation_pattern,
        9 AS total_flg,
        CONCAT(product_code, product_name) AS total_key
    FROM tmp_allocations
    GROUP BY product_code, product_name;
END
;