---

WITH scheme_ids AS (
    SELECT DISTINCT v.scheme_id
    FROM back_office.eps_transaction et
    JOIN ourpension.eps e ON e.id = et.eps_id
    CROSS JOIN LATERAL (
        VALUES (et.pens_schema_dt), (et.pens_schema_ct)
    ) v(scheme_id)
    WHERE e.service_type = 'NPO'
      AND et.oper_date >= DATE '2026-08-01'
      AND et.oper_date <  DATE '2026-09-01'
      AND v.scheme_id IS NOT NULL
)
SELECT
    s.scheme_id AS eps_scheme_id,
    ps.id AS found_scheme_id,
    ps.name AS pension_scheme,
    ps.index_scheme,
    ps.uid_1c,
    EXISTS (
        SELECT 1
        FROM back_office.report_conformity_pu_bu_detail rd
        WHERE rd.report_conformity_pu_bu_id = 39
          AND LOWER(BTRIM(rd.uidpensionscheme))
              = LOWER(BTRIM(ps.uid_1c))
          AND NULLIF(BTRIM(ps.uid_1c), '') IS NOT NULL
    ) AS uid_found_in_report
FROM scheme_ids s
LEFT JOIN prod_line.pens_scheme ps ON ps.id = s.scheme_id
ORDER BY s.scheme_id;