Application Architecture

This section will show the server architecture for the various financial applications.

The server architecture can be found for the production environment in RDIMS 13614189.

The server architecture can be found for the software development environment in RDIMS 13614183.

This section will show the application architecture for the various financial applications.

Oracle Applications originally installed in 1995 provides Transport Canada with a robust Financial and Materiel Management System. It is a Computer Off The Shelf (COTS) package developed by Oracle Corporation. Although Oracle has a multitude of subsystems within this product, Transport Canada extensively utilizes Oracle Purchasing, Oracle Payables, Oracle General Ledger, Oracle Receivables, and Oracle Fixed Assets.

Oracle Purchasing provides the ability for Responsibility Managers to requisition (soft commit) to purchase goods and services. Approvals are managed through Oracle Workflow, Transport Canada Buyers (Contracting Officers) will autocreate approved requisitions into purchase orders or contracts once a supplier / vendor is determined. The autocreation process will turned planned expenditures (soft commitments) into hard commitments. Oracle Purchasing’s receiving functionality is not utilized.

Oracle Payables is used to manage and pay Transport Canada invoices. All invoices are paid through Public Works and Government Services Canada (PWGSC) currently Public Services and Procurement Canada (PSPC). For this Transport Canada (and a couple of other departments) have customized a PWGSC Interface to pass from Transport Canada to PWGSC unpaid invoices and receive back payment reconciliation information.

Oracle General Ledger provides Transport Canada the ability to record all financial transactions and maintain account balances. There is a customized interface to consolidate financial transactions and balances with the Receiver General General Ledger. Oracle General Ledger’s budgeting functionality is not utilized. Transport Canada uses Hyperion to manage it’s budgeting requirements.

Oracle has several interfaces with other government departments such as the PWGSC interface, Receiver General General Ledger interface, and Standard Payment System (Phoenix) to name a few external entities / stakeholders and their subsequent interfaces with Transport Canada’s financial systems.

These interfaces are documented in RDIMS 7946007 eBusiness Suite Interfacing Systems Diagram and RDIMS 4058962 BIRM Interfacing Systems and Description of Data Transferred Between Systems.

Open

Open

Salary Management System

The Salary Management System (SMS) is used to prepare Transport Canada’s salary forecasts and contain their actual salary expenditures including regular pay and overtime. Actual pay is provided by Phoenix through a custom interface. SMS was implemented in 2001 in order to replace the Salary Information Management System (SIMS).

SMS is a COTS package developed by Freebalance using the Forte programming language. The implementation is a client server implementation with three tiers including a database server, application server, and application client installed on the desktop. Access is also delivered through Citrix.

The interfacing between SMS, Oracle Applications, Hyperion, Phoenix are extensive and I hesitate to use words to describe. Please see RDIMS 11977842 The Financial System Management Model as this will provide you with a good visual understanding of how the systems interface. RDIMS 15615802 Financial Management Model Data Map will provide some more detailed information on the data passed between interfaces.

System Administration

RDIMS 12294083 Salary Management System All in One Support Document an excellent view into the necessary knowledge to maintain the system.

User Guide

http://mytc.tc.gc.ca/tp-11906-oracle-erp-user-manual-system-and-business-processes-2225.html

Hardware Configuration

RDIMS 7734201 provides a diagram of servers. This was last updated in 2015.

Scheduled Tasks

RDIMS 556430 SMS Production Scheduled Tasks Summary lists the daily jobs run through various schedulers. It will be out of date following the implementation of SMS version 7 later in 2020.

Disaster Recovery

To be Determined

Rererence Material

To be Determined

Transport Canada Budgeting was managed within the Oracle General Ledger’s product when Oracle Applications was implemented in 1995. In 2004, Transport Canada moved to using Oracle Financial Analyzer (OFA) to manage budgets. Hyperion was implemented in 2009 as an upgrade to OFA.

Hyperion is the financial management application that meets TC budgeting, forecasting, and planning needs including single year operational budgets, multi year capital budgets, environmental projects, and grants and contributions.

System Administration

RDIMS 8000903 Hyperion Technical Administrator Manual is intended to provide the Hyperion technical team the reference manual they need to support the product.

User Guide

http://mytc.tc.gc.ca/tp-11906-oracle-erp-user-manual-system-and-business-processes-2225.html

Hardware Configuration

RDIMS 7837187 Provides a good description of the Hyperion Server configuration from 2015.

Scheduled Tasks

RDIMS 7685067 Hyperion Job Schedule provides a list of the daily Hyperion jobs and their scheduled time.

RDIMS 7569086 Year Over Year Instructions Procedure documents the procedure to change years within Hyperion.

Disaster Recovery

RDIMS 7199561 Application Recovery - Hyperion provides detailed server information and information on how data flows between systems. There is no information on back up and restoration. It has not been updated since 2019.

Rererence Material

To be Determined

Data Warehouse and Business Objects Reporting System

The data warehouse and Business Objects Reporting System was originally installed in 2004 for a couple of reasons:

The first to join together data from separate systems such as Oracle Applications, SMS, Hyperion, and LEX so that reports could be run not only on those systems but where those systems intersect.

The second reason was to improve reporting efficiency. Data stored in the source systems is denormalized to remove repeating groups to improve efficiency in maintaining the data. Data stored in the data warehouse is normalized to reduce the number of tables accessed to improve reporting efficiency in retrieving the data.

The data warehouse extract was a customized extract built inhouse at Transport Canada as was the Business Objects reports. The development of the reports was implemented with the approach that they were going to implement the exact same functionality provided in the Glue reports that were being replaced.

Star schemas were designed included but were not limited to Requisitions, purchase orders, invoices, payments, GL Balances, Financial Transactions, etc. Star schemas are intended to optimize and provide versatility to the way the data is viewed. The center of the star schema is the fact which holds numerical and additive information such as sales amounts. The dimensional data is stored in separate tables that allows you to view the factual data by attributes such as time, organization (responsibility centre), account (line object), project, customer, sales person, etc.

System Administration

RDIMS 4693661 BIRM ETL Guide is intended to provide the Data Warehouse technical team the reference manual they need to support the product.

User Guide

http://mytc.tc.gc.ca/tp-11906-oracle-erp-user-manual-system-and-business-processes-2225.html

Hardware Configuration

to be determined

Scheduled Tasks

The datawarehouse extract takes place as part of the Daily Batch Schedule on a daily basis. Please see Daily Batch Schedule section of Finance Web site. The BIRM ETL Guide shown above also provides a great deal of information on how the daily extract works.

Disaster Recovery

RDIMS 7191685 TC Financial Data Warehouse Application Recovery.

Rererence Material

To be Determined

Portal

The Portal is an Oracle application that acts as an access point to various applications, of which Oracle Applications, the Salary Management System, Hyperion, the Business Objects reporting tool would be a few.

For Start and Stop Procedures for the BIRM Portal see RDIMS 16588952.

Cluster Group