Release 64

by

Summary of Changes by View

1. project_details_plus_USD & project_details_plus_CAD

Status Logic Updated

  • Improved derivation of Project_Status to align with IFS workflow: 
CASE
    WHEN src.STATUS = 'Active' AND 'war' = 'Yes' THEN 'WAR'
    WHEN src.STATUS = 'Active' AND 'war' = 'No' THEN 'Started'
    WHEN src.STATUS = 'Active' THEN 'Active'
    WHEN src.STATUS = 'Inactive' THEN 'Completed'
    WHEN src.STATUS = 'Dormant' THEN 'Closed'
    ELSE src.STATUS
END AS Project_Status

Project Manager Email Added

  • Joined to pmo.Project_Managers for better accuracy, includes fallback logic: 
COALESCE(pm.Email_Address_, 'mday-nasr@ca.bmt.org', 'project.manager@ca.bmt.org') AS Project_Manager_Email

2. project_details_plus_CAD & project_details_USD

Column Name Adjustment

  • Temporary rename of source column:
    • src.Unnamed__0 → PROJECT_DESCRIPTION (→ src.Estimate_Overhead)
  • Note:
    • This rename was reversed in project_details_USD and remains src.Unnamed__0.
    • Both src.Estimate_Overhead and src.Unnamed__0 are ambiguous column names for project description.
    • A longer-term fix may be needed.

3. project_details_plus_APC, customer_details_APC, project_details_APC

PRU Code Mapping Enhancement

  • Extended mapping logic in the Company_Helper CTE:
    • Added new lines (e.g., Defence Ship Design – DEF AUS ‘2014003‘, Maritime Engineering (Infrastructure) – SI, ‘3403007’)
  • Aligns with current cost centre and reporting conventions.

4. global_company & global_company_202409

Expanded Source

  • Now fully sourced from MIS80
  • Includes all company codes and uses standardised regional names

5. global_organisation & global_organisation_202409

Same as Above

  • Fully sourced from MIS80
  • Aligned to the new regional name standards

6. employee_item_codes_absence, employee_item_dates_absence, employee_item_values_absence

Minor Logic Refinements

  • Adjustments to ensure consistency across employee absence item views.
  • No structural changes, but internal logic improved for accuracy and reliability.

7. dates_DIM_DATE_202409

Performance Optimisation

  • Efficiency improvements made to resolve rendering delays.
  • No schema changes; resolves known timeout issue during query generation.

Notes

🔍 Found 15 unique file changes for datamart view in the given period:
1. ⚠️ DIFFERENT: project_details_plus_USD.sql
2. ⚠️ DIFFERENT: project_details_plus_CAD.sql
3. ⚠️ DIFFERENT: project_details_USD.sql
4. ⚠️ DIFFERENT: project_details_CAD.sql
5. ⚠️ DIFFERENT: customer_details_APC.sql
6. ⚠️ DIFFERENT: project_details_APC.sql
7. ⚠️ DIFFERENT: project_details_plus_APC.sql
8. ⚠️ DIFFERENT: global_company.sql
9. ⚠️ DIFFERENT: global_organisation.sql
10. ⚠️ DIFFERENT: global_company_202409.sql
11. ⚠️ DIFFERENT: global_organisation_202409.sql
12. ⚠️ DIFFERENT: employee_item_codes_absence.sql
13. ⚠️ DIFFERENT: employee_item_dates_absence.sql
14. ⚠️ DIFFERENT: employee_item_values_absence.sql
15. ⚠️ DIFFERENT: dates_DIM_DATE_202409.sql

Leave a Comment