Release 80

DataMart views changed since 01-Oct-2025.

Executive summary

• Introduced three new meta-code views and one person/skills view.
• Corrected customer PRU→Company mapping and widened a busopp value field.
• Standardised source table identification (removed trailing “$” in table names) and aliasing.
• Adopted a consistent CTE top-and-tail query pattern to support get.myview in on-demand mode.
• Multiple views updated for “de-dollar” and alias clean-ups.

---

New views

• project_meta_codes_pmn — Transforms Project Manager Narrative (PMN) records.
  File: project_meta_codes_pmn.sql — Initial DataMart view
• employee_meta_codes_skillsbase — Employee skillsbase meta-codes.
  File: employee_meta_codes_skillsbase.sql — Initial DataMart view
• person_meta_codes_skillsbase — Provides a view of skillsbase person records based on an API export.
  File: person_meta_codes_skillsbase.sql — Initial DataMart view
• dates_mydate — Converted from CREATE VIEW to a static query for portability.
  File: dates_mydate.sql — Parameters in header removed

Renamed / globalised

• company_item → global_company_item
  Files: company_item.sql, global_company_item.sql — Renamed with “global” suffix
• program_item → global_program_item
  File: program_item.sql — Renamed with “global” suffix

Downstream dependencies:

• Update any references to company_item and program_item to use global_company_item and global_program_item.

---

Corrections

• customer_details_APC — Reconciles Company ID using supplied PRU Code name:
  When PRU name is ‘Maritime Engineering (Infrastructure) – SI’ then Company = ‘3037’.
  File: customer_details_APC.sql — Message: “Maritime Engineering 3037”
• busopp_value — Increased field capacity by changing DECIMAL(15,3) representation to VARCHAR(16) to avoid overflow/truncation.
  File: busopp_value.sql — Message: “VARCHAR(16)”

---

Improvements to source identification & aliasing

Purpose: Ensure tables are identified using their non-dollar names for lineage and metadata consistency.

Change pattern:
'{"Source":"ifs.[QFACT_BMT_DWH_INVOICE_TAB_IAS$]"}' as SOURCE_SYSTEM
→
'{"Source":"ifs.[QFACT_BMT_DWH_INVOICE_TAB_IAS]"}' as SOURCE_SYSTEM

Views updated:

• global_program_item.sql — improve alias
• project_item_202404.sql — improve alias
• employee_core_details_archive.sql — de-dollar
• invoice_core_details.sql — de-dollar
• invoice_meta_dates.sql — de-dollar
• order_core_details.sql — de-dollar
• order_core_details_archive.sql — de-dollar
• order_item_values.sql — de-dollar
• order_meta_codes.sql — de-dollar
• order_meta_codes_archive.sql — de-dollar
• order_meta_dates.sql — de-dollar

---

Global query structure change (Beta & 202409 queries)

Adopted a consistent CTE wrapper to enable get.myview to interact with queries in on-demand mode and to improve efficiency/accuracy:

WITH __myview AS (
  SELECT
    -- ...
)
SELECT * FROM __myview;

---

File change log (complete list)

File	Message
project_meta_codes_pmn.sql	Initial DataMart view
employee_meta_codes_skillsbase.sql	Initial DataMart view
person_meta_codes_skillsbase.sql	Initial DataMart view
customer_details_APC.sql	Maritime Engineering 3037
global_program_item.sql	improve alias
project_item_202404.sql	improve alias
employee_core_details_archive.sql	de-dollar
invoice_core_details.sql	de-dollar
invoice_meta_dates.sql	de-dollar
order_core_details.sql	de-dollar
order_core_details_archive.sql	de-dollar
order_item_values.sql	de-dollar
order_meta_codes.sql	de-dollar
order_meta_codes_archive.sql	de-dollar
order_meta_dates.sql	de-dollar
dates_mydate.sql	parameters in header (removed)
company_item.sql	rename global
global_company_item.sql	rename global
program_item.sql	rename global
busopp_value.sql	VARCHAR(16)