Support-Item & Service Mapping – Data-Model Working Session

Context & Overall Purpose

• Meeting revolves around generating monthly/periodic statements for clients (NDIS/Support-at-Home context).
• Participants: Edwin (primary developer handling FetchXML/Power Platform build), Abhi & Saurabh (solution/design), Ravi (screen-share), plus other team members.
• Core goal: decide how to pull data from Microsoft Dynamics CRM (package account, product, support item tables) and map to the three statement sections:
  1. Support-at-Home (SH) – Services & Other Expenses
  2. Assistive Technology (AT) – Services & Other Expenses
  3. Home Modification (HM) – Services & Other Expenses
• Need tight alignment between: (a) top-level “summary” totals, (b) mid-level category totals, (c) line-level transaction details. Totals must reconcile exactly across all three levels.

Major Data Entities & Relationships

• Package Account
  • Represents funding "bucket" for a participant; holds the quantity and amount actually used.
  • Links 1-to-many with Product.
  • Key fields to surface:
  • quantity → will be summed
  • amount → will be summed, then displayed as negative in statements.
• Product
  • Can be Support Item (NDIS line items) or other product types.
  • Holds two monetary-related option-set fields:
  • monetarytype (expected value = Expense)
  • submonetarytype (values = Service OR Other Expense)
  • supportitemid (lookup) → pulls full Support Item entity.
  • displayname – chosen interim label for statements (original plan was reportingid; data is incomplete).
• Support Item
  • Contains canonical NDIS item codes & names.
  • Fields used: name (label), maybe category for higher-level grouping.

Current Data Problems

• “Service” flag does not exist at Support Item level; it’s inferred via submonetarytype in Product.
• reportingid almost empty in DEV → fallback to displayname until cleansed.
• Possible master-data errors: 20k+ records could require downstream patching if wrong flags are created.
• Network lag & screen-share glitches complicate walkthrough.

Filtering & FetchXML Logic (Agreed)

• One single FetchXML per statement section.
• Base filter (identical for SH, AT, HM):
  • $packageaccount.product.monetarytype = \text{Expense}$
  • AND $packageaccount.product.submonetarytype \in {\text{Service}, \text{Other Expense}}$
• Fields returned:
  • packageaccount.quantity
  • packageaccount.amount
  • packageaccount.product.supportitem.name (→ used as category/label)
  • packageaccount.product.displayname (→ used as line description)
• Aggregations inside FetchXML:
  • Sum of Units per Support Item:
    $\text{TotalUnits}<em>i = \sum</em>{r \in i} quantity_r$
  • Sum of Amounts per Support Item:
    $\text{TotalAmt}<em>i = \sum</em>{r \in i} amount_r$
  • After retrieval, negate amounts for statement visual:
    $\text{DisplayAmt}<em>i = -\,\text{TotalAmt}</em>i$
• Grouping: group by support item ID to guarantee one row per label despite many transactions ("nursing care" may appear 100 times – need one aggregate line).
• Hierarchy: same dataset feeds three presentation tiers:
  1. Grand Total – sum across all support items (one row per section).
  2. Category Total – e.g.
  • Personal Care
  • Domestic Assistance
  • AT Equipment etc. (Support Item → parent category if available).
  1. Line Detail – individual support item label with units & amount.

Section-Specific Details & Concerns

Section 3 (AT & HM Summary up top)

• Displays total spend for AT + HM in a consolidated header.
• Amounts must match the later Section 8 breakout.

Section 8 (Assistive Technology & Home Modification Breakdown)

• Lists expense/service lines such as:
  • "Administration Cost (Assistive Technology)"
  • "Repair & Maintenance (Assistive Technology)"
  • "Wrap-Around Service (Assistive Technology)"
• These may be a mix of true services and pure expenses; treated uniformly via the same filter.
• Pending mapping:
  • Clarify which support-item codes correspond to AT-specific terms.
  • Possibly introduce another flag or use parent category to split AT vs HM vs SH.

Section 8-HM Equivalent (Home Modification)

• Same structure as AT section; again reuse the identical FetchXML but with parent category filter = "Home Modification" once categories finalised.

Field Mapping Decisions

• Label Column (mid & line level)
  • Interim source: product.displayname (falls back to supportitem.name when data cleansed).
• Transaction Description Column
  • Populate with the bill line name when available; else re-use display name.
• Units Column
  • Pull packageaccount.quantity; aggregate by support item.
• Amount Column
  • Pull packageaccount.amount; aggregate & negate.

Functional Examples / Scenarios

• Example 1 – Nursing Care appears 20 times:
  • Fetch returns 20 packageaccount rows; grouped yields $Units=77$, $Amount=-5000$.
• Example 2 – Short-Term Accommodation checked; found to be not a service; submonetarytype likely "Other Expense"; still included by filter.
• Example 3 – Administration Fee for AT: no obvious support item; needs categorisation decision; may rely on new flag or master-data clean-up.

Practical / Implementation Guidance

• Build & test statement rendering with provisional mappings first; treat field swaps (displayname → reportingid) as low-risk post-UAT bug.
• Keep one consolidated query per section to avoid duplicate data pulls and ensure totals tie.
• Use Dynamics linked-entity syntax: packageaccount → productid → supportitemid.
• Performance: 20k+ products flagged incorrectly could force a data patch; avoid ad-hoc "service" flag on support-item to sidestep future bulk updates.

Outstanding Questions & Action Items

• Clarify with Shivan (or business owner):
  1. Should reportingid ultimately replace displayname?
  2. Confirm authoritative list of AT/HM support items vs SH.
  3. Decide if new attribute is needed ("Is Service?" vs relying on submonetarytype).
• Edwin to schedule 30-minute design walkthrough with Sandhya (team) to confirm filter consistency across all three statement fragments.
• Abhi to verify Section 8 numbers reconcile with Section 3 summary in sample statement.
• Saurabh to check OneNote/Confluence for any prior mapping rule documentation; circulate screenshot if available.

Ethical & Business Impact Notes

• Correct categorisation affects financial transparency to vulnerable participants; mis-labelling could cause under/over-utilisation and compliance issues with Services Australia.
• Master data governance (properly setting submonetarytype) avoids downstream bulk patches and preserves audit integrity.

Numerical/Formula Recap

• Expense filter:
  $monetarytype = Expense$
• Service/Other Expense sub-filter:
  $submonetarytype \in {Service,\ Other\ Expense}$
• Aggregations per Support Item:
  $\text{Units} = \sum quantity$
  $\text{Amount} = -\sum amount$ (negative for statement display)