-- 
-- 発送内容照会
-- 
CREATE OR REPLACE VIEW v_shipping_inquiry AS
SELECT
    -- 伝票・日付情報
    sh.slip_number AS slip_number,
    DATE_FORMAT(sh.shipment_actual_date, '%Y/%m/%d') AS shipment_actual_date,
    DATE_FORMAT(sh.arrival_date, '%Y/%m/%d') AS arrival_date,

    -- 得意先・納品先
    sh.customer_id AS customer_id,
    c.customer_code AS customer_code,
    sh.customer_name AS customer_name,

    -- EC入力（input_type = '3EC'）の場合は納品先名を非表示
    CASE WHEN sh.input_type = '3EC' THEN ''
        ELSE sh.delivery_name
    END AS delivery_name,

    -- 配送情報
    sh.carrier_id AS carrier_id,
    cr.carrier_name AS carrier_name,
    sh.tracking_number AS tracking_number,
    sh.package_count AS package_count,

    -- 受注・商品情報
    sd.product_id AS product_id,
    sd.product_code AS product_code,
    sd.product_name AS product_name,
    sd.shipment_qty AS shipment_qty,
    sd.order_number AS order_number,

    -- メモ類
    sh.logistics_note AS logistics_note,
    sd.note AS detail_note,
    '総計' AS total_lavel

FROM sales_headers sh
    -- 売上明細
    INNER JOIN sales_details sd
        ON sd.sales_id = sh.id

    -- マスタ（表示用
    LEFT JOIN customers c
        ON c.id = sh.customer_id
    LEFT JOIN carriers cr
        ON cr.id = sh.carrier_id

    -- shipment 表示なし結合のみ
    LEFT JOIN shipment_headers sht
        ON sht.id = sh.shipment_id
    LEFT JOIN shipment_details sdt
        ON sdt.id = sd.shipment_detail_id
       AND sdt.shipment_id = sht.id
