Cek Stock

WITH data_log AS(
        -- Ambil nilai log stock per periode bulan selanjutnya setelah tutup bulan
    SELECT tenant_id, warehouse_id, product_id, product_balance_id, SUM(qty) AS qty
    FROM in_log_product_balance_stock A
    WHERE tenant_id = 10
        AND SUBSTRING(doc_date, 1, 6) = '201711'
    GROUP BY tenant_id, warehouse_id, product_id, product_balance_id
    UNION ALL
    -- Ambil nilai saldo awal per nextYearMonthAfterClosing dan doc_type_id = -99
    SELECT tenant_id, warehouse_id, product_id, product_balance_id, SUM(qty) AS qty
    FROM in_summary_monthly_qty
    WHERE tenant_id = 10
        AND date_year_month = '201711'
        AND doc_type_id = -99
    GROUP BY tenant_id, warehouse_id, product_id, product_balance_id, product_status
    ORDER BY product_balance_id

), data_summary_log AS(
    -- Summary nilai saldo awal dan log stock
    SELECT tenant_id, warehouse_id, product_id, product_balance_id, SUM(qty) AS qty
    FROM data_log
    GROUP BY tenant_id, warehouse_id, product_id, product_balance_id
    ORDER BY product_balance_id, product_id

), data_current_stock AS (
    SELECT tenant_id, warehouse_id, product_id, product_balance_id, SUM(qty) AS qty
    FROM in_product_balance_stock
    GROUP BY tenant_id, warehouse_id, product_id, product_balance_id
    ORDER BY product_balance_id, product_id
)
    -- Bandingkan (log_stock + saldo_awal) dengan current stock
    SELECT A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id, A.qty, B.qty AS current_stock
    FROM data_summary_log A
    INNER JOIN data_current_stock B   ON A.tenant_id = B.tenant_id
                        AND A.product_id = B.product_id
                        AND A.product_balance_id = B.product_balance_id
                        AND A.warehouse_id = B.warehouse_id
    WHERE A.qty <> B.qty
    ORDER BY A.product_balance_id;