Script to Compare JE Valuation of Interim Stock / Inventory to Stock Picking Valuation Layer Value

WITH pos_journal AS (
    SELECT 
        am.ref AS session_ref,
        am.id AS move_id,
        am.name AS move_number,
        am.date AS accounting_date,
        SUM(aml.credit - aml.debit) AS interim_credit
    FROM account_move am
    JOIN account_move_line aml ON aml.move_id = am.id
    JOIN account_account aa ON aa.id = aml.account_id
    WHERE am.state = 'posted'
      AND (
          aa.code_store::text LIKE '%2.500%' 
          OR aa.name::text ILIKE '%INTERIM%'
      )
    GROUP BY am.ref, am.id, am.name, am.date
),
pos_svl AS (
    SELECT 
        COALESCE(sp.origin, sm.origin) AS session_ref,
        ABS(SUM(svl.value)) AS total_svl_value,
        SUM(svl.quantity) AS total_qty
    FROM stock_valuation_layer svl
    JOIN stock_move sm ON sm.id = svl.stock_move_id
    JOIN stock_picking sp ON sp.id = sm.picking_id
    WHERE sp.state = 'done'
    GROUP BY COALESCE(sp.origin, sm.origin)
)
SELECT 
    ps.name AS pos_session,
    pj.move_number AS journal_entry,
    pj.accounting_date,
    ROUND(COALESCE(pj.interim_credit, 0), 2) AS journal_interim_value,
    ROUND(COALESCE(psvl.total_svl_value, 0), 2) AS stock_valuation_value,
    ROUND(COALESCE(pj.interim_credit, 0) - COALESCE(psvl.total_svl_value, 0), 2) AS difference,
    CASE 
        WHEN ABS(COALESCE(pj.interim_credit, 0) - COALESCE(psvl.total_svl_value, 0)) < 0.01 THEN 'BALANCED'
        ELSE 'MISMATCH'
    END AS status
FROM pos_session ps
LEFT JOIN pos_journal pj ON pj.session_ref = ps.name
LEFT JOIN pos_svl psvl ON psvl.session_ref = ps.name
WHERE ps.state = 'closed'
  -- Filter by Accounting Date range:
  AND pj.accounting_date BETWEEN '2026-07-01' AND '2026-07-31'
ORDER BY pj.accounting_date DESC, ps.id DESC;

WITH pos_journal AS (
    SELECT 
        am.ref AS session_ref,
        am.id AS move_id,
        am.name AS move_number,
        am.date AS accounting_date,
        SUM(aml.credit - aml.debit) AS interim_credit
    FROM account_move am
    JOIN account_move_line aml ON aml.move_id = am.id
    JOIN account_account aa ON aa.id = aml.account_id
    WHERE am.state = 'posted'
      AND (
          aa.code_store::text LIKE '%2.500%' 
          OR aa.name::text ILIKE '%INTERIM%'
      )
    GROUP BY am.ref, am.id, am.name, am.date
),
pos_svl AS (
    SELECT 
        COALESCE(sp.origin, sm.origin) AS session_ref,
        ABS(SUM(svl.value)) AS total_svl_value,
        SUM(svl.quantity) AS total_qty
    FROM stock_valuation_layer svl
    JOIN stock_move sm ON sm.id = svl.stock_move_id
    JOIN stock_picking sp ON sp.id = sm.picking_id
    WHERE sp.state = 'done'
    GROUP BY COALESCE(sp.origin, sm.origin)
)
SELECT 
    ps.name AS pos_session,
    pj.move_number AS journal_entry,
    pj.accounting_date,
    ROUND(COALESCE(pj.interim_credit, 0), 2) AS journal_interim_value,
    ROUND(COALESCE(psvl.total_svl_value, 0), 2) AS stock_valuation_value,
    ROUND(COALESCE(pj.interim_credit, 0) - COALESCE(psvl.total_svl_value, 0), 2) AS difference,
    CASE 
        WHEN ABS(COALESCE(pj.interim_credit, 0) - COALESCE(psvl.total_svl_value, 0)) < 0.01 THEN 'BALANCED'
        ELSE 'MISMATCH'
    END AS status
FROM pos_session ps
LEFT JOIN pos_journal pj ON pj.session_ref = ps.name
LEFT JOIN pos_svl psvl ON psvl.session_ref = ps.name
WHERE ps.state = 'closed'
  -- Filter by Accounting Date range:
  AND pj.accounting_date BETWEEN '2026-07-01' AND '2026-07-31'
ORDER BY pj.accounting_date DESC, ps.id DESC;