🔍 Find open RITMS with completed/deleted flow contexts

[Uncle Rob]

SCENARIO
A customer needed to "fix the catalog".  A broad complaint was "My requests never close".  Customers were angry and C suite was getting involved.  Inspection revealed HUNDREDS of open RITMS (far beyond expectation for the size of customer).  Avg age was insane.  Closer inspection:  many RITMS are open but have  no sc_tasks. 
Lets talk about 🤬BAD RITMS🤬.

🤬WHAT IS A BAD RITM🤬
A BAD RITM is one that should be closed, but isn't.  "HOW!?" you say:
- Flow or Legacy Workflow are poorly defined and don't adequately close the RITM
- Modifications made to RITM or sc_task business rules, interfering with proper RITM closure.
- Exotic circumstances nuked the flow or workflow contexts of the RITM.

🥴 FAILED SOLUTIONS 🥴

(SKIP TO SOLUTION IF YOU DON'T WANT THE HISTORY)
- Tried list report with related list conditions where sum of sc_tasks where active = true is 0.  This didn't work because RITMs can have approvals before sc_task generation.  Could also have automations, integrations, or wait conditions where no active sc_tasks are present.
- Tried above + filtering out RITM stages such as "approval, waiting for approval, approving, etc".  This won't work at scale since stages can be defined at the item level.  Too hard to keep track.  Still doesn't factor for integrations & wait conditions.

💪 ROBUST SOLUTION 💪

Since reporting on stage and or sc_task presence isn't reliable enough, but we know flow & legacy workflow both have context records, we can make a database view to join RITM, Flow Context, and Workflow Context

🔗BUILDING A DATABASE VIEW🔗
Plenty of good explainers if you've never built a Database View.  We'll just cover the pertinent points.

Mine is called u_no_flow_ritm.
- First sc_req_item (RITM) at order 100.  It is imperative we query that first.  We want all RITMS.
- Second is wf_context for RITMs governed by legacy workflow.  Order 110 because it comes after the RITM table.  We want this "where the ritm's sys_id = the workflow context's id"
- Third table we want is sys_flow_context for RITMs governed by flow designer.  Order 110 because it comes after the RITM table but can be at the same time as the wf_context.  We want this "where RITM's sys_id = Flow Context's source_record .
- Fourth table isn't strictly necessary, I add it because Flow Context only stores the sys_id of a flow, not a real reference, so this will allow me to get Flow properties like name.  This must come after the sys_flow_context, so order 200.  We want this "Where Flow's sys_id = Flow Context's flow"

THE POWER OF THE LEFT JOIN!

Please note the wf_context, sys_flow_context, and sys_hub_flow tables are using Left Join.
Briefly, if you have a regular join on tables A and B you get all records in A & B.
We only want wf_context and sys_flow_context records that CAN join to sc_req_item

TRYING IT OUT

When you first run the database view you're going to get a bunch of fields you likely don't need.  Adjust the list view as follows.   Be careful here, because width database views you'll get all fields from all tables involved.  You'll notice state is on my screen tree times.  See below the list view for the actual definition.  Notice the prefixes!

We now have a database view that can tell us for each ritm if there's an associated flow OR workflow, and what state they're in.  It will also show if ritms have neither.

📊 REPORTING SOLUTION 📊

So now that I'm collecting the right data, I can build a report.  Something with these conditions delivers the result we want:

- Find me all RITMS where active is true, and Flow is empty or workflow is empty.  This are BadRITMs who's contexts have already completed (gracefully or not) and been flushed.
- OR Find me all RITMS where active is true and Workflow is not empty and Workflow Context state is terminal (Finished, Error, Cancelled).  These are BadRITMS who have a workflow context that is complete.
- OR Find me all RITMS where active is true and Flow is not empty and Flow Context state is terminal (complete, error, cancelled).  These are BadRITM's who have a Flow context that is complete.  You'll want to look at these flows for bad definition around closure.

Run this on your PDI and nearly all the demo data will come back.
Add a couple custom catalog items (one that runs workflow, and one that runs flow) and order a few of each.

Very interested to hear....
🤔WHAT ARE YOUR FINDINGS?🤔