Inventory Subledger to GL Reconciliation SQL Generator

-- Inventory subledger vs GL by account for period 202609
-- Table and column names are generic: map them to your ERP.
WITH sub AS (
  SELECT inventory_account AS gl_account,
         SUM(extended_value) AS sub_value
  FROM inventory_valuation
  WHERE period = '202609'
    AND inventory_account IN ('1300','1310','1320')
  GROUP BY inventory_account
),
gl AS (
  SELECT gl_account,
         SUM(ending_balance) AS gl_value
  FROM gl_period_balance
  WHERE period = '202609'
    AND gl_account IN ('1300','1310','1320')
  GROUP BY gl_account
)
SELECT COALESCE(sub.gl_account, gl.gl_account) AS gl_account,
       COALESCE(sub.sub_value, 0) AS subledger_value,
       COALESCE(gl.gl_value, 0) AS gl_value,
       COALESCE(sub.sub_value, 0) - COALESCE(gl.gl_value, 0) AS difference,
       CASE WHEN ABS(COALESCE(sub.sub_value, 0) - COALESCE(gl.gl_value, 0)) <= 1.00
            THEN 'MATCHED' ELSE 'INVESTIGATE' END AS status
FROM sub
FULL OUTER JOIN gl ON gl.gl_account = sub.gl_account
ORDER BY ABS(COALESCE(sub.sub_value, 0) - COALESCE(gl.gl_value, 0)) DESC;

-- Largest item-level values in the subledger for one account
SELECT item_number, warehouse, quantity_on_hand, unit_cost, extended_value
FROM inventory_valuation
WHERE period = '202609'
  AND inventory_account = '1300'
ORDER BY ABS(extended_value) DESC
FETCH FIRST 50 ROWS ONLY;