Cek current stock

-- Cek current stock

WITH beg_log_os_qty AS (
    -- Ambil saldo awal stock per product
    SELECT product_id, SUM(qty) AS qty
    FROM in_summary_monthly_qty
    WHERE date_year_month = '201610'
    AND doc_type_id = -99
    AND product_status = 'GOOD'
    GROUP BY product_id
    UNION ALL
    -- totalkan qty per product dari data log stock
    SELECT A.product_id, SUM(A.qty) AS qty
    FROM in_log_product_balance_stock A
    WHERE A.product_status = 'GOOD'
    GROUP BY A.product_id
    UNION ALL
    -- totalkan qty barang keluar (adj stock) yg masih outstanding
    SELECT B.product_id, SUM(B.qty_realization) AS qty
    FROM in_inventory A
    INNER JOIN in_inventory_item B ON A.inventory_id = B.inventory_id
    WHERE A.doc_type_id IN (521, 522)
    AND A.status_doc <> 'R'
    AND B.qty_realization < 0
    AND B.product_status = 'GOOD'
    GROUP BY B.product_id
), summary_stock AS (
    SELECT product_id, SUM(qty) AS qty_check_stock
    FROM beg_log_os_qty
    GROUP BY product_id
)
SELECT A.product_id, B.product_code, B.product_name,
    COALESCE(A.qty_current_stock, 0) AS qty_current_stock,
    COALESCE(C.qty_check_stock, 0) AS qty_check_stock,
    (COALESCE(A.qty_current_stock, 0) - COALESCE(C.qty_check_stock, 0)) AS selisih_stock
FROM (
    SELECT X.product_id, SUM(X.qty) AS qty_current_stock
    FROM in_product_balance_stock X
    WHERE X.product_status = 'GOOD'
    GROUP BY X.product_id
) A
INNER JOIN m_product B ON A.product_id = B.product_id
FULL OUTER JOIN summary_stock C ON A.product_id = C.product_id
WHERE (COALESCE(A.qty_current_stock, 0) - COALESCE(C.qty_check_stock, 0)) <> 0
ORDER BY B.product_name;