Skip to content

Self Serve Repairs — Workflow & Bug Fixes

The Self Serve repairs feature shows tenants their active repairs and repair history in the MyLHP app and portal. The data is sourced from QL (hpmordhd, hpmaptmt, hpmordnt, etc.) via two Versaa workflows.

Workflows

Workflow Purpose
SS App Get Active Repairs Returns active/in-progress repairs for the tenant's property — main card data, appointment date, and "View Progress" step detail
SS Repair History GetRepairs Returns completed/historical repairs for the repair history tab

May 2026 Bug Fixes

Four bugs were diagnosed and fixed in May 2026 (ticket: tenants confused by wrong/unlabelled dates on repairs cards).

Fix 1 — App Main Card: Wrong Date Shown (Target Completion Instead of Appointment)

File: SS_App_Get_Active_Repairs_1.workflow

Root cause: RepairCardTitle was set as FORMAT(targ_comp_dt, 'dd MMMM') in the initial SELECT. The ApptStartDate was fetched later via a separate UPDATE step — but RepairCardTitle was never recalculated after the appointment was retrieved.

Fix: Added a 4th UPDATE after the ApptStartDate fetch: 

UPDATE #orders 
SET RepairCardTitle = ISNULL(FORMAT(ApptStartDate, 'dd MMMM'), FORMAT(targ_comp_dt, 'dd MMMM'))

Fallback behaviour: If no appointment is booked, shows target completion date rather than blank.

Fix 2 — View Progress: No "Next Appointment" Label

File: SS_App_Get_Active_Repairs_1.workflow

Root cause: The "Append Steps" query (used to build the progress timeline) had no JOIN to hpmaptmt. It only showed the order creation date, with no appointment information.

Fix: Added a deduplicating LEFT JOIN subquery to hpmaptmt (aliased apt — the main alias a was already used for cmpadd address table): 

LEFT JOIN (
  SELECT order_no, comp_id, MAX(start_time) AS start_time
  FROM hpmaptmt
  GROUP BY order_no, comp_id
) apt ON apt.order_no = o.order_no AND apt.comp_id = o.comp_id

Updated StepSubtitle to: 

ISNULL(
  'Next appointment: ' + FORMAT(apt.start_time, 'dd MMM yyyy'),
  'This repair was opened on the: ' + CONVERT(varchar, ord_crea_on, 106)
)

Fix 3 — Repair History: Duplicate Rows

File: SS_Repair_History_GetRepairs_1.workflow

Root cause 1: LEFT OUTER JOIN dbo.hpmaptmt with no deduplication — one row returned per appointment per order (orders with multiple appointments produced multiple rows).

Fix 1: Replaced with a deduplicating subquery: 

LEFT OUTER JOIN (
  SELECT order_no, comp_id, MAX(appoint_date) AS appoint_date
  FROM dbo.hpmaptmt
  GROUP BY order_no, comp_id
) apt ON apt.order_no = o.order_no AND apt.comp_id = o.comp_id

Root cause 2: LEFT OUTER JOIN dbo.hpmordnt also produced duplicates when an order had multiple notes.

Fix 2: Replaced with: 

LEFT OUTER JOIN (
  SELECT order_no, comp_id, MIN(note_no) AS note_no
  FROM dbo.hpmordnt
  GROUP BY order_no, comp_id
) nt ON nt.order_no = o.order_no AND nt.comp_id = o.comp_id

Fix 4 — ApptStartDate Exposed as DataItem (Portal Prep)

File: SS_App_Get_Active_Repairs_1.workflow

Added ApptStartDate to the output DataItems: 

<DataItem sourceColumn="ApptStartDate" dataItemName="ApptStartDate" dataTypeName="dateTime" />

This enables Aareon to wire up the appointment date on the portal main card (currently shows "Your order was requested on" — Aareon-side change required to consume this field).

Out of Scope (Aareon-Side)

These issues exist but are controlled by Aareon, not LHP:

  • Portal main card still shows "Your order was requested on" with no date — portal card rendering is Aareon-controlled. Raise as bug referencing ApptStartDate now available in workflow output
  • Portal "Repair Details" popup shows "Appointment Date: No Appointment Date" — also Aareon-side

Key QL Tables

Table Key Columns Purpose
hpmordhd targ_comp_dt, ord_status, client_no, message1, ord_crea_on Order header
hpmohsup orig_date, completed_dt, completed_yn Order supplement
hpmaptmt start_time, end_time, appoint_date, order_no, comp_id Appointments — always deduplicate before joining
hpmordnt order_no, comp_id, note_no Order notes link table — always deduplicate before joining
cmpnote / ocmpnote — Note text (segmented into 255-char chunks)
hratency tency_st_dt, tency_end_dt, tency_seq_no Tenancy

Warning: Both hpmaptmt and hpmordnt can have multiple rows per order. Always use a GROUP BY subquery before joining, not a direct join. Failing to do so silently produces duplicate rows.

QL Database Names

Environment Database
SS Test (portal testing) t_qlfdat
Dev / Acceptance a_qlfdat
Live QLFDAT

Test Client / Order

Test client used during May 2026 development: - Client: 2322454 - Order: 373982 - Property: 13 Park Avenue, Sutterton - targ_comp_dt = 23 Dec 2025 (target completion — was incorrectly showing before fix) - appointment_date = 05 May 2026 (correct date — now shown after fix)

Useful Test Queries

-- Find tenant with a future appointment (for testing SS active repairs)
SELECT TOP 10 
  o.order_no, o.client_no, o.prty_id, o.targ_comp_dt, a.start_time
FROM hpmordhd o
INNER JOIN hpmaptmt a ON a.order_no = o.order_no
INNER JOIN hratency t ON t.prty_ref = o.prty_id AND t.comp_id = o.comp_id
WHERE o.ord_status = 'ISS'
  AND a.start_time > GETDATE()
  AND o.targ_comp_dt != CAST(a.start_time AS DATE)
  AND (t.tency_end_dt IS NULL OR t.tency_end_dt > GETDATE())
ORDER BY a.start_time ASC

-- Find order details by order number
SELECT o.order_no, o.client_no, o.prty_id, o.targ_comp_dt, o.ord_status, a.start_time
FROM hpmordhd o
LEFT JOIN hpmaptmt a ON a.order_no = o.order_no
WHERE o.order_no = ''