Referral status transition timestamps in Clarity — is there a better path than parsing REFERRAL_HIST?
I'm a Clarity report writer building a referral lifecycle report and I'm hitting a wall. I want to track a referral from creation through closure and capture when each key status transition occurred , not just current state, but the full audit trail with timestamps so I can calculate duration between milestones: created → authorized → Ready to Schedule → first appointment scheduled → closed.
Here's where I've landed after digging through the schema. V_REFERRAL_CYCLE_TIME gets me most of the way there (AUTHORIZED_DT, FIRST_APPT_ASSIGNED_DT, LAST_SCHED_STATUS_CHG_DT), but it doesn't break out when the scheduling sub-status changed to a specific value. The bigger discovery was that referrals have two separate status dimensions — RFL_STATUS_C (Authorized, Closed, etc.) joining to ZC_RFL_STATUS, and SCHED_STATUS_C (Scheduling Approval, Ready to Schedule, Called 1x, etc.) joining to a completely different table, ZC_SCHED_STATUS. Easy to miss. For the actual history, REFERRAL_HIST.AUTH_HX_ITEM_VALUE stores transitions as free text like "From Scheduling Approval to Ready to Schedule" with a CHANGE_DATE, which works but requires parsing. I've also spotted REFERRAL_HISTORY and RFL_HX_ITEM_CHANGE as a possible cleaner structured alternative, and RFL_WQ for workqueue dwell time, but haven't fully validated either yet.
My questions: is there a more structured way to pull scheduling sub-status transition timestamps than parsing AUTH_HX_ITEM_VALUE free text? Has anyone used RFL_HX_ITEM_CHANGE to reconstruct a status change timeline? And is RFL_WQ reliably populated in most environments, or is it hit or miss? We're a Community Connect site if that affects anything. Happy to share what I've built so far.