Release 85

by

1. Core Detail Views

Updated views

  • busopp_core_details.sql
  • employee_core_details.sql
  • project_core_details.sql

Change

  • Updated base generation logic so that all LINK_KEYs in OBJECT_METADATA are explicitly CAST as TEXT.

Impact / Rationale

  • Ensures consistent typing of linkage keys across CDM views.
  • Reduces risk of mismatched joins and type conflicts where metadata is consumed downstream.

2. Project Meta Values Views

Updated views

  • project_meta_values_contingency.sql
  • project_meta_values_expenses.sql
  • project_meta_values_forecast.sql
  • project_meta_values_hours.sql
  • project_meta_values_revenue.sql
  • project_meta_values_time.sql

Changes / Notes

  • Legacy change where field COMPANY has been renamed to COMPNY_ID.
  • These are recognised as legacy CDM views, even though they were only created in Apr-25.
  • There is an open design decision on whether these views should:
    • Continue to exist as CDM meta value views; or
    • Be replaced by item_values_## views and assembled “in report”.

Risks / Considerations

  • Currency handling and conversions remain a known issue and will need to be addressed at some level (either in CDM or in reporting logic).
  • Decision required: where should the burden of value assembly and currency handling sit (warehouse vs. report layer)?

3. Meta Codes Views

Updated views

  • project_meta_codes.sql
  • busopp_meta_codes.sql
  • busopp_meta_codes_archive.sql
  • project_meta_codes_archive.sql

Changes

  • Rationalised multiple Programme/Program ID concepts into three distinct categories:
    • BUSINESS
    • REPORTING
    • DELIVERY
  • Confirmed direction that all code values should have a description for small lookup sets.

Impact / Rationale

  • Reduces ambiguity around programme identifiers and improves consistency of usage.
  • Enforces better usability of lookups in reports and downstream applications by ensuring descriptions are always available.

4. Feedback Meta Satisfaction View

View

  • feedback_meta_satisfaction.sql

Changes / Notes

  • This is a composite UNION view.
  • Historically saved as a CREATE VIEW statement, which conflicts with the current mvw materialised-view process.
  • Composite views remain a future pattern, but this one may need to be forked/isolated for now to avoid interference with the mvw process.

Action

  • Treat as an exception until a standard composite-view pattern is agreed.

5. Infrastructure OSH Records

View

  • infrastructure_oshrecords.sql

Changes / Notes

  • This view contains a very long comment field.
  • Currently, there is no robust solution for handling comments where JSON does not terminate cleanly.
  • For now, JSON warnings have been disabled for this view, and it should be treated as reference text only, not as structurally reliable JSON.

Impact

  • View is suitable for human reference but should not be used as a stable JSON source for automation or transformations.

6. Base Architecture / get.myview Adoption

Updated views

  • global_management_accs.sql
  • finance_codeb.sql
  • project_value.sql
  • employee_details.sql
  • employee_details_plus.sql
  • employee_details_plus_202409.sql
  • employee_headcount.sql
  • employee_hours.sql
  • employee_hours_202409.sql
  • project_customerinvoice.sql
  • project_customerorderline.sql
  • project_time_202407.sql
  • project_value_CAD.sql
  • project_purchaseorderline_202407.sql
  • project_roleassignment_202407.sql

Change

  • All listed views have been wrapped using the standard __myview CTE pattern and updated to use the modern get.myview structure (“top and tail” refactor).

Impact / Rationale

  • Aligns legacy/early views with the current CDM architectural standard.
  • Simplifies maintenance and makes behaviour more predictable across the view estate.

7. General Ledger Views – Pipeline Date Support

Updated views

  • generalledger_balance.sql
  • generalledger_balanceset.sql
  • generalledger_details.sql
  • generalledger_periodbudget.sql
  • generalledger_projectbalance.sql
  • generalledger_transaction.sql
  • ledger_item.sql

Change

  • Inserted pipeline date support (commented as -- pipeline dates) to enable date insertion during pipeline execution.

Impact / Rationale

  • Provides better traceability and control for incremental loads and pipeline runs.
  • Enables more robust auditing and potential backfill/rollback strategies based on pipeline dates.

Leave a Comment