Release 60

by

Additions

202409

⚠️ Created:global_company_202409.sql
⚠️ Created:global_organisation_202409.sql
⚠️ Created:customer_details_202409.sql

Datamart views have been created to ensure a seamless transition for clients migrating from 202404 to 202409, providing a structured and usable view for ongoing data integration. These views have been explicitly versioned with the 202409 signature to:

  • Represent the edition status of the views.
  • Ensure compatibility with complementary versions, facilitating smoother integration and version control.

Common Data Standard

⚠️ Created:project_core.sql
⚠️ Created:project_codes.sql
⚠️ Created:employee_item_codes_expense.sql
⚠️ Created:employee_item_values_expense.sql

New views introduced within the future Common Data Standard. These views follow a newly adopted table template that:

  • Utilises a consistent JSON-based METADATA structure for a unified Code/Value representation.
  • Incorporates a created/expiry date alongside an Active Flag to track record validity over time.
  1. Employee Item Value Expense – This view provides detailed information on individual expense items attributed to employees, including expense type, gross pay amount, and financial metadata. It supports financial reporting, auditing, and expense analysis by aligning employee and company structures.
  2. Employee Item Codes Expense – This view captures the classification codes associated with employee expenses, including cost centres, projects, PRU codes, and asset codes. It facilitates expense categorisation, compliance reporting, and financial alignment with business dimensions.

Alterations

General Ledger

Additional codes added to GeneralLedger Transaction

  • Voucher Type
  • Identity (Customer Ref)
  • Reference Series
  • Reference Number

APC PRU Codes

Correction to Company PRU Codes

⚠️ Modified: customer_details_APC.sql
⚠️ Modified: project_details_APC.sql

Current State:

This table provides an updated mapping of PRU Codes for the respective Company Units.

WITH Company_Helper AS (
    SELECT * FROM (VALUES
        ('Marine Surveys - SI', '3037', '1013037'),
        ('People - COMM AUS', '4009', '9304009'),
        ('Maritime Engineering (Infrastructure) - SI', '3037', '3003037'),
        ('Maritime Engineering (Infrastructure) - Comm Aus', '4009', '3404009'),
        ('Flood Risk Software - COMM AUS', '4009', '5024009'),
        ('Defence Engineering Services (Embedded Work) - DEF AUS', '4003', '3904003'),
        ('Flood Risk Management - COMM AUS', '4009', '5014009'),
        ('Environment and Climate Consultancy - COMM AUS', '4009', '5034009'),
        ('PMO - Singapore', '3037', '8103037'),
        ('Facilities HSEQ - DEF AUS', '4003', '9504003'),
        ('Ship Support & Delivery - DEF AUS', '4003', '3104003'),
        ('Other', '4009', '9704009'),
        ('Delivery Management - DEF AUS', '4003', '8204003'),
        ('Defence Ship Design - DEF AUS', '4003', '2094003'),
        ('Future Business - DEF AUS', '4003', '9004003'),
        ('Coastal Management - COMM AUS', '4009', '5044009')
    ) AS Lookup(PRU, COMPANY, PRU_Code)
)

Notes

🔍 Found 10 unique file changes in the given period:
1. ⚠️ Created: global_company_202409.sql
2. ⚠️ Created: global_organisation_202409.sql
3. ⚠️ Created: project_core.sql
4. ⚠️ Created: project_codes.sql
5. ⚠️ Created: employee_item_codes_expense.sql
6. ⚠️ Created: employee_item_values_expense.sql
7. ⚠️ Created: customer_details_202409.sql
8. ⚠️ Modified: generalledger_transaction.sql
9. ⚠️ Modified: customer_details_APC.sql
10. ⚠️ Modified: project_details_APC.sql

Leave a Comment