-- 
-- 在庫表
-- 
CREATE OR REPLACE VIEW v_product_inventory_list
AS 
SELECT
-- 倉庫ID
warehouses.id AS warehouse_id
-- 倉庫コード
,warehouses.warehouse_code
-- 倉庫名
,warehouses.warehouse_name
-- 商品ID
,products.id AS product_id
-- 商品コード
,products.product_code
-- 商品名
,products.product_name
-- 商品性格
,products.product_character
-- 在庫表出力区分
,products.stock_output_type
-- 後継品
,products_successor.product_name successor_product_name
-- 賞味期限
,DATE_FORMAT(inventory_lots.expiration_date, '%Y/%m/%d') AS expiration_date
-- 在庫数
,inventory_lots.quantity AS inventory_quantity
-- 出荷可能数
,COALESCE(inventory_lots.quantity, 0)
-COALESCE((
SELECT SUM(COALESCE(allocated_qty, 0))
FROM order_lot_allocations
WHERE status='1'
AND order_lot_allocations.inventory_lot_id=inventory_lots.id
), 0)
-COALESCE((
SELECT SUM(COALESCE(allocated_qty, 0))
FROM edi_order_lot_allocations
WHERE status='1'
AND edi_order_lot_allocations.inventory_lot_id=inventory_lots.id
), 0)
AS available_qty
-- 入荷予定数
,(
SELECT SUM(receipt_details.receipt_qty)
FROM receipt_headers
JOIN receipt_details on receipt_details.receipt_id=receipt_headers.id
WHERE receipt_headers.status in (1, 2) 
AND receipt_headers.receipt_date>CURDATE()
AND receipt_details.product_id=products.id
) AS receipt_qty
FROM products
JOIN inventory_lots ON inventory_lots.product_id=products.id
LEFT JOIN products AS products_successor ON products_successor.id=products.successor_product_id
LEFT JOIN warehouses ON warehouses.id=inventory_lots.warehouse_id
WHERE (products.product_type=2 AND inventory_lots.quantity>0 OR products.product_type!=2)
