# Master-Data & Historical-Data Reconciliation Checklist

Part of task #18 (MIGRATION_PLAN.md) / #17 (task tracker). Covers the four
data domains the task names — employees, org/supervisor tree, leave
balances & accruals, historical attendance & leave — plus the row-count/
spot-total validation the task explicitly calls for. Each section states
what "reconciled" means, the exact check to run, and current status.

Sources: HRSystem (`192.168.60.211`, SQL Server, DB `HRSystem`, read via
`xeno_odoo_ro`) and XENHR's own `locations`/leave tables where HRSystem
doesn't hold the data. Target: Odoo `xeno.xeno` DB on `192.168.60.33`.

---

## 1. Employees

**Definition of reconciled**: every HRSystem `employees` row with a
plausible individual identity (excludes `EMP-000X` generic role accounts)
either (a) is linked to an Odoo `hr.employee` via `xeno_employee_code`, or
(b) is explicitly logged as intentionally excluded (terminated pre-cutover,
duplicate, or test data) with a reason.

**Check**:
```sql
-- HRSystem side: total candidate rows
SELECT COUNT(*) FROM employees WHERE employee_code NOT LIKE 'EMP-%';

-- Odoo side: how many have the bridge field set
-- (odoo shell) env['hr.employee'].search_count([('xeno_employee_code','!=',False)])
```
Row counts must match, or every gap must appear in the exclusion log below.

**Status: DONE, re-verified live 2026-07-16**. HRSystem now has 75
candidate rows (down from 78 on 2026-07-14 — some earlier candidates were
since excluded/archived on the HRSystem side). All 75 are linked in Odoo
via `xeno_employee_code`; zero unlinked. The one extra Odoo-linked code
beyond 75 is `100205`, a throwaway "Test ERP Employee" record, not a real
gap. The earlier "3 unaccounted for" no longer applies.

**SAP B1 (XENDEMO) side — separate from Odoo reconciliation**: 44 of 76
Odoo-linked employees now have `b1_employee_id` set (pushed successfully,
up from 6 on 2026-07-14); 32 remain pending retry once XENDEMO's Service
Layer stabilizes (see `project_odoo_employee_identity_bridge.md`). Not a
blocker for the Odoo-side count above, but track separately before
treating XENDEMO as reconciled.

---

## 2. Org / supervisor tree

**Definition of reconciled**: every Odoo `hr.employee.parent_id` (manager)
matches HRSystem's `employees.supervisor_id` chain, resolved through
`xeno_employee_code` rather than name.

**Check**:
```sql
SELECT employee_code, supervisor_id FROM employees WHERE supervisor_id IS NOT NULL;
```
then, in Odoo, for each row: resolve both `employee_code` and its
`supervisor_id` (itself an `employees.id`, not a code — needs a join back to
`employees.employee_code`) to `hr.employee` via `xeno_employee_code`, and
compare against the existing `parent_id`.

**Status**: **DONE (2026-07-14, Session A)**. Confirmed `supervisor_id` is
`employees.id` (self-join required, 49/49 rows matched by id, 0 by code —
caveat below resolved). Re-joined through `xeno_employee_code` instead of
name for all 49 org-tree rows: 34 already had the correct `parent_id`, 15
were corrected and committed (had to use `active_test=False` in the Odoo
search — an initial pass wrongly flagged 11 archived employees as
unmatched using the ORM's active-only default). Unblocks "My Team"
attendance/leave (#3/#4) with real data.

**Caveat (resolved 2026-07-14)**: `supervisor_id` in `employees` is
`employees.id`, confirmed via direct query — not `employee_code`.

---

## 3. Leave balances & accruals

**Definition of reconciled**: for each employee with `xeno_employee_code`,
the current leave balance per type in Odoo (`hr.leave.allocation`) matches
HRSystem's/XENHR's recorded balance as of the same cutoff date.

**Check**: pull XENHR's leave-balance table (name TBD — not yet inspected
this session), sum allocated minus taken per employee per leave type,
compare to Odoo's `hr.leave.allocation` (`number_of_days` minus
`leaves_taken`) for the 6 leave types now in Odoo (see
`project_odoo_missing_leave_types.md`).

**Status**: source table located (2026-07-14, via GitLab) — dump still
blocked. `leave_balances` (Laravel migration
`2024_01_01_000004_create_leave_balances_table`, precision widened
`2026_06_23`): `employee_id` (FK→`employees`), `leave_type_id` (FK→
`leave_types`), `year`, `allocated_days`/`used_days`/`pending_days`/
`carried_days` (all `decimal(5,2)`), unique on `(employee_id, leave_type_id,
year)`. Confirmed via `xenhr_backend`'s `.env.example`/`config/database.php`
that this table lives in the **same** SQL Server DB as `employees` —
`DB_DATABASE=hr_system`, i.e. the already-known HRSystem instance
(`192.168.60.211`), not a separate XENHR-only database.

**DONE (2026-07-14, Session A, part 2)**: susu granted `SELECT` on
`leave_balances`/`department_approvers`/`employee_approvers` directly
(confirmed via `fn_my_permissions` before executing anything — not
assumed). Dumped 2026 data: 345 rows across 69 real employees (excl.
`EMP-000X`), 5 leave types (Annual/Sick/Personal/Unpaid/Maternity — no
Emergency Leave rows exist in HRSystem's 2026 data). Compared against
Odoo's `hr.leave.allocation`: **15 employees already had all 5 correct
(75 exact matches on `allocated_days` == `number_of_days`)**; **54
employees had zero allocation records at all** — the original leave-type
build ([[odoo-missing-leave-types]]) only created allocations for the
first ~15 real employees on hand at the time, not the 54 added later by
the identity-bridge work. **Imported the missing 270** (`54 × 5`):
`number_of_days` = `allocated_days + carried_days` (no distinct Odoo field
for carryover on a manual allocation), `xeno_used_days` = `used_days`
exactly, `date_from` = 2026-01-01, created via `Allocation.create()` +
`action_approve()` — all landed in `state='validate'`, `xeno_remaining_days`
recomputed consistently. **Known limitation, not imported**: HRSystem's
`pending_days` has no Odoo allocation-side equivalent (it reflects
in-flight leave *requests*, not a balance field) — would need the
individual `leave_applications` rows imported into `hr.leave` to
represent, which is domain 4's (historical attendance & leave) unresolved
scope decision, not this domain's gap.

---

## 4. Historical attendance & leave

**Definition of reconciled**: row counts and a handful of spot-check daily
totals (e.g. total hours worked in a sample month, total leave days taken
in a sample month) match between the source system and whatever Odoo ends
up storing/reporting for that history.

**Check**:
```sql
SELECT COUNT(*) FROM <attendance_source_table> WHERE scan_date BETWEEN ... ;
```
spot-compare against Odoo's equivalent for the same window, once historical
attendance is actually imported into Odoo-native models (currently the
`/odoo/attendances/bymonth` report reads HRSystem live — see task #10 note
— it does not import into `hr.attendance`).

**Status**: **RESOLVED 2026-07-16 — scope decision made, no import
needed**. susu confirmed the live read-through report is the accepted
permanent design: HRSystem's SQL Server (the biometric-scan source of
truth) is a separate system from XENHR the web app, and continues serving
as the live source even after XENHR itself is retired. No historical
attendance/leave migration into `hr.attendance`/`hr.leave` is planned.
This check (row-count/spot-total reconciliation) is therefore not
applicable — there is no parallel copy in Odoo to reconcile against.

---

## 5. Foreigner details (plan #37 — data half)

**Definition of reconciled**: every HRSystem `employee_foreigner_details`
row whose employee is linked via `xeno_employee_code` has its values on the
Odoo employee: passport number/expiry → `passport_id` /
`passport_expiration_date` (hr.version-backed), visa number/expiry →
`visa_no` / `visa_expire`, work permit number/expiry → `permit_no` /
`work_permit_expiration_date`, and the three metadata columns → the
`xeno_foreigner_details` module's `xeno_passport_issue_place` /
`xeno_passport_issue_date` / `xeno_visa_issue_date`.

**Prerequisites (Session A)**: install `xeno_foreigner_details` (built
2026-07-14, in repo) before importing, so its windowed alert override is
active when the dates land — core's cron alone would silently skip any
expiry already inside the 60-day notice window at import time. After the
import, set **HR Responsible** on foreign employees, otherwise expiry
activities fall back to the cron user (OdooBot).

**Check**:
```sql
SELECT COUNT(*) FROM employee_foreigner_details;
-- (odoo shell) env['hr.employee'].search_count([('visa_expire','!=',False)])
-- plus spot-check 3 employees' expiry dates field-by-field
```

**Status**: **DONE (2026-07-14, Session A cont'd)**. HRSystem's
`employee_foreigner_details` has 11 rows total but only **2 with
`is_foreigner=1`** (the other 9 are placeholder rows with every detail
column null) — `employee_code` 100202 (Nyein Su Su) and 200002 (Wa Wa
Lwin), both resolved via `xeno_employee_code`. Needed a fresh SQL grant
(`xeno_odoo_ro` had no access to this table either; same self-grant-is-a-
silent-no-op pattern as `leave_balances`/`departments` — susu ran the
`GRANT`, verified via `fn_my_permissions` before importing). Mapped:
`passport_number`→`passport_id`, `passport_expire_date`→
`passport_expiration_date`, `visa_number`→`visa_no`,
`visa_expire_date`→`visa_expire`, `work_permit`→`permit_no`,
`work_permit_expire_date`→`work_permit_expiration_date`, plus the module's
three metadata fields (`xeno_passport_issue_place`,
`xeno_passport_issue_date`, `xeno_visa_issue_date`). Set **HR Responsible**
on both to the real `hr.manager@xenoptics.com` role account (found: 105 of
107 employees with any HR-responsible value default to OdooBot — not a
real convention to match, just the unset fallback — so picked the one
actual HR-manager user account instead of Susu or OdooBot). **Known gap,
not fixed**: HRSystem's `visa_issue_place` column has no Odoo-side field —
the module only tracks 3 of the 4 metadata columns; low-impact today since
only 2 employees are affected, flagged here rather than silently dropped.
Verified: both employees' alert-latch flags (`xeno_visa_alerted`/
`xeno_passport_alerted`) correctly reset to `False` on write (module's
`write()` override), so the expiry cron will evaluate them fresh.

---

## How to re-run these checks

All the HRSystem-side queries need the `xeno_odoo_ro` credentials, which
live in Odoo's own `ir.config_parameter` (`hrsys.*` keys) — so re-running
any of the above requires `odoo shell` access on `192.168.60.33`, same as
prior sessions. There is no standalone credential store outside Odoo.

## Summary

**All 5 domains closed as of 2026-07-16.** Task #18 / #17 is done.

| Domain | Status |
|---|---|
| Employees | **DONE** — 75/75 real HRSystem employees linked in Odoo (re-verified live 2026-07-16); XENDEMO push 44/76 (32 blocked on XENDEMO Service Layer instability, not code — tracked separately, not an Odoo-reconciliation blocker) |
| Org/supervisor tree | **DONE (2026-07-14)** — all 49 rows resolved via employee_code, 15 `parent_id` corrections applied |
| Leave balances/accruals | **DONE (2026-07-14)** — 270-row import landed on top of the existing baseline (505 allocation records across 103 employees, re-verified live 2026-07-16) |
| Historical attendance & leave | **RESOLVED 2026-07-16** — scope decision: no import, live read-through from HRSystem stays the permanent design |
| Foreigner details | **DONE (2026-07-14)** — module deployed, 2 real foreigners (100202, 200002) imported, HR Responsible set; `visa_issue_place` field gap flagged (not fixed) |