-- 
-- 引当管理表
-- 
CREATE OR REPLACE VIEW v_allocation_management
AS 
SELECT
    source_type,
    sales_rep_id,
    sales_rep_code,
    sales_rep_name,
    customer_id,
    customer_code,
    customer_name,
    order_slip_no,
    DATE_FORMAT(x.order_date, '%Y/%m/%d') AS order_date,
    DATE_FORMAT(x.shipment_date, '%Y/%m/%d') AS shipment_date,
    category_lv2_name,
    product_id,
    product_code,
    product_name,
    product_character,
    allocated_qty,
    unit_price,
    amount,
    order_number,
    delivery_name
FROM (
    /* =========================================================
       1) 受注ロット引当（メイン：order_lot_allocations）
       ========================================================= */
    SELECT
        'ORDER'                                                AS source_type,
        ocsr.sales_rep_id                                      AS sales_rep_id,
        u.user_code                                            AS sales_rep_code,
        CONCAT_WS('', u.family_name, u.given_name)             AS sales_rep_name,
        oh.customer_id                                         AS customer_id,
        c.customer_code                                        AS customer_code,
        oh.customer_name                                       AS customer_name,
        oh.slip_number                                         AS order_slip_no,
        oh.order_date                                          AS order_date,
        oh.shipment_date                                       AS shipment_date,
        cat.category_name                                      AS category_lv2_name,
        od.product_id                                          AS product_id,
        od.product_code                                        AS product_code,
        od.product_name                                        AS product_name,
        p.product_character                                    AS product_character,
        ola.allocated_qty                                      AS allocated_qty,
        od.unit_price                                          AS unit_price,
        od.amount                                              AS amount,
        od.order_number                                        AS order_number,
        oh.delivery_name                                       AS delivery_name
    FROM order_lot_allocations ola
        INNER JOIN order_details od
            ON od.id = ola.order_detail_id
        INNER JOIN order_headers oh
            ON oh.id = od.order_id
        INNER JOIN customers c
            ON c.id = oh.customer_id
        INNER JOIN products p
            ON p.id = od.product_id
        LEFT JOIN categories cat
            ON cat.id = p.category_lv2_id
        LEFT JOIN order_category_sales_reps ocsr
            ON ocsr.order_id = oh.id
           AND ocsr.category_id = p.category_lv1_id
        LEFT JOIN users u
            ON u.id = ocsr.sales_rep_id
    WHERE
        ola.status = 1
        AND (
            od.product_name IS NULL
            OR od.product_name NOT IN ('ｳﾝﾁﾝ', 'ｿﾉﾀ ｿﾉﾀ')
        )

    UNION ALL

    /* =========================================================
       2) EDI受注ロット引当（メイン：edi_order_lot_allocations）
       ========================================================= */
    SELECT
        'EDI'                                                  AS source_type,
        ccsr.sales_rep_id                                      AS sales_rep_id,
        u.user_code                                            AS sales_rep_code,
        CONCAT_WS('', u.family_name, u.given_name)             AS sales_rep_name,
        eoh.customer_id                                        AS customer_id,
        c.customer_code                                        AS customer_code,
        c.customer_name                                        AS customer_name,
        eoh.edi_order_number                                   AS order_slip_no,
        eoh.order_date                                         AS order_date,
        eod.shipment_date                                      AS shipment_date,
        cat.category_name                                      AS category_lv2_name,
        eod.product_id                                         AS product_id,
        eod.product_code                                       AS product_code,
        eod.product_name                                       AS product_name,
        p.product_character                                    AS product_character,
        eola.allocated_qty                                     AS allocated_qty,
        eod.unit_price                                         AS unit_price,
        eod.amount                                             AS amount,
        ''                                                     AS order_number, -- EDIは注文Noなし
        CONCAT_WS('', eod.delivery_name1, eod.delivery_name2)  AS delivery_name
    FROM edi_order_lot_allocations eola
        INNER JOIN edi_order_details eod
            ON eod.id = eola.edi_order_detail_id
        INNER JOIN edi_order_headers eoh
            ON eoh.id = eod.edi_order_header_id
        INNER JOIN customers c
            ON c.id = eoh.customer_id
        INNER JOIN products p
            ON p.id = eod.product_id
        LEFT JOIN categories cat
            ON cat.id = p.category_lv2_id
        LEFT JOIN customer_category_sales_reps ccsr
            ON ccsr.customer_id = eoh.customer_id
           AND ccsr.category_id = p.category_lv1_id
        LEFT JOIN users u
            ON u.id = ccsr.sales_rep_id
    WHERE
        eola.status = 1
        AND (
            eod.product_name IS NULL
            OR eod.product_name NOT IN ('ｳﾝﾁﾝ', 'ｿﾉﾀ ｿﾉﾀ')
        )
) x;