/
Cost Centres Spreadsheet Upload

Cost Centres Spreadsheet Upload

Owned by IFS Energy and Resources (Deactivated)
Last updated: May 29, 2024 by IFS Energy & Resources
8 min read

Search the Qbyte Financial Wiki
 

Back to Spreadsheet Uploads

Introduction

The Cost Centre (CC) upload spreadsheet is designed to assist users to upload new cost centres into the Qbyte Financial application. The current version of the spreadsheet will only insert records into the database tables and cannot update existing records. Changes or updates can be performed through the Qbyte Financial cost centre maintenance screen (MRSS011). The spreadsheet file consists of the following three work sheets:

1. Masters Worksheet

2. Cost Centre Legal Locations Worksheet

3. CC Comments Worksheet

Roles

Users must be granted one of the following roles before they can use the upload spreadsheet:

  • PWSQL_03_CC_MASTER_MAINTENANCE

OR:

  • PWSQL_50_SUPER_USER

 

Feature Highlights and System Configuration

Column Select Functionality

The CC Master worksheet allows users to “hide” selected columns (provided that they are not mandatory fields) that may not be needed or applicable for the user. The “Column Select” window can be accessed by going to:

  • Ribbon > Add-Ins > Qbyte Connect > Cost Centres > Show Available Columns

Cost Centre Auto Numbering

The new upload spreadsheet will allow users to utilize existing numbering schemes setup in the application. Users will not be allowed to input cost centre numbers if the system is setup to auto number newly created cost centres. Auto numbering can be setup by:

1. Setting up the cost centre numbering scheme for a specific Org ID and Cost Centre Type

  • This is done through the Cost Centre Next Available Numbers Screen: MRSS126

  • This screen can be accessed through the menu by navigating to:

    • Configuration  > Cost Centres > Cost Centre Numbering

2. Once the numbering scheme has been defined, the system default to auto number cost centres must be set to Y. To do this:

  • Navigate to the menu and select Administration > System Defaults and Controls

  • In the Sub-Systems tab, select 2 - QBYTE FM

  • In the Sub-Sys Comp tab, select 16 - CORE

  • Navigate to the system default AUTO_NUMBER_CC

  • Update the Value field to Y.

  • Select Ctrl + S, or the Save icon, to save changes.

3. If this flag is set to ‘N’, the spreadsheet will require the user to input a cost centre number.

Legal Locations Setup

The upload spreadsheet allows users to attach legal location information to a cost centre. The user must input:

1. A Valid Survey System Code

  • Can be added through the Codes screen (ACTS017).

Customization can include:

  • Whether or not a specific location element is to be used for the survey system

  • Whether or not a specific location element is mandatory

  • Date format and field length for each location element

  • Maximum and minimum values for each location element

System Defaults

The following fields (if not specified by the user) will auto populate with default values defined at the system or database level:

1. Use OM Flag

  • System Default: #2 Qbyte FM > #16 Core > DFLT_COST_CENTRE_OM_FLAG

2. Alt GL Code Flag

  • Database Default is set to to ‘N’

3. Capital/Expense Flag

  • System Default: #2 Qbyte FM > #7 Work In Progress Transfer > CIP_RESULTS_PROJ_MTNCE

4. Billing Code Flag

  • System Default: #2 Qbyte FM > #16 Core  BILLING_CODE

5. OC Cost Centre Flag

  • Database Default is set to ‘N’

6. Active Date

  • If not provided will default to today’s date.

User Defined Fields (UDFs)

The cost centres master worksheet allows users to input information into twenty different UDF fields. These UDF fields can be customized within the application according to the client’s business needs and processes. Customization can include:

  • Specifying a custom name for each UDF 

  • Specifying the data type (numeric, date, alphanumeric) and maximum string length for each UDF.

  • Specifying which UDFs are mandatory

  • Specifying valid values for each UDF

For more information on customizing User Defined Fields in Qbyte Financial, please refer to the wiki page on User Defined Fields.

The CC UDF fields in the application are in the following format:

  • CC_UDF_X_CODE (e.g. UDF # 1)

OR

  • CC_UDF_XX_CODE (e.g. UDF # 10)

Process Workflow

Uploading a New Cost Centre

1. Open the spreadsheet file.

2. Enable Macros Content on the worksheet if required.

3. Select the Cost Centres Worksheet.

4. Connect to the Database: 

  • Ribbon > Add-Ins > Qbyte Connect > Database logon

5. Input CC information. Ensure that required fields are populated. Required General CC Information:

  • Cost Centre Name

  • Cost Centre Number (if auto numbering is deactivated in the application)

  • Cost Centre Type Code

  • Managing Organization ID

  • Ownership Organization ID

6. Validate Data:

  • Ribbon > Add-Ins > Qbyte Connect > Cost Centres > Validate Records

7. One of the following will occur:

  • Validation Successful: The Return Message column will display a “Successful” message.

  • Warning: The row will be highlighted Yellow. The Return Message column will display a specific warning message. These records can still be uploaded without fixing the problem specified.

  • Error: The row will be highlighted Red. The Return Message column will display a specific error message. The spreadsheet will not allow these rows to be uploaded without the specified problem being corrected.

8. Correct any warning/error messages and validate again.

9. Insert Records:

  • Ribbon > Add-Ins > Qbyte Connect > Cost Centres > Insert Records

  • Spreadsheet will automatically validate data before actual upload.

  • Message box will pop up indicating that the process has completed.

  • Message box will display the number of valid and erroneous records.

  • User can now select to:

    • Save: Completing the process. All valid records will be updated in the database.

    • Cancel: All changes will be reversed and no data will be inserted.

Uploading Legal Location Information to an Existing Cost Centre

1. Open the spreadsheet file.

2. Enable Macros Content on the worksheet if required.

3. Select the CC Legals Worksheet.

4. Connect to the Database: 

  • Ribbon > Add Ins > Qbyte Connect > Database Logon

5. Input information. Ensure that the following required fields are populated:

  • Cost Centre Number

  • Primary Flag

  • Survey System Code

  • Location Elements 1-8 (dependent on survey system code settings defined in the application).

6. Validate Data:

  • Ribbon > Add-Ins > Qbyte Connect > CC Legals > Validate Records

7. One of the following will occur:

  • Validation Successful: The Return Message column will display a “Successful” message.

  • Error: The row will be highlighted Red. The Return Message column will display a specific error message. The spreadsheet will not allow these rows to be uploaded without the specified problem being corrected.

8. Correct any error messages and validate again.

9. Insert Records;

  • Ribbon > Add-Ins > Qbyte Connect > CC Legals > Insert Records

  • Spreadsheet will automatically validate data before actual upload.

  • Message box will pop up indicating that the process has completed.

  • Message box will display the number of valid and erroneous records.

  • User can now select to:

    • Save: Completing the process. All valid records will be updated in the database.

    • Cancel: All changes will be reversed and no data will be inserted.

10. Upon successful insert: the CC Legal ID column will populate with the ID number of the record in the COST_CENTRE_LEGALS table.

Uploading Comment Information to an Existing Cost Centre

1. Open the spreadsheet file.

2. Enable Macros Content on the worksheet if required.

3. Select the Comments Worksheet.

4. Connect to the Database:

  • Ribbon > Add-Ins > Qbyte Connect > Database logon

5. Input information. Ensure that the following required fields are populated:

  • Cost Centre Number

  • Comment Type Code

  • Comment Description

  • Comment Date

6. Validate Data

  • Ribbon > Add-Ins > Qbyte Connect > BA Comments > Validate Records

7. One of the following will occur:

  • Validation Successful: The Return Message column will display a “Successful” message.

  • Error: The row will be highlighted Red. The Return Message column will display a specific error message. The spreadsheet will not allow these rows to be uploaded without the specified problem being corrected.

8. Correct error messages and validate again.

9. Insert Records

  • Ribbon > Add-Ins > Qbyte Connect > Comments > Insert Records

  • Spreadsheet will automatically validate data before actual upload.

  • Message box will pop up indicating that the process has completed.

  • Message box will display the number of valid and erroneous records.

10. User can now select to:

  • Save: Completing the process. All valid records will be updated in the database.

  • Cancel: All changes will be reversed and no data will be inserted.

11. Upon successful insert: the Comment ID column will populate with the ID number of the record in the COMMENTS table.

Updating Cost Centre Information

To update existing Cost Centre records, use the following steps:

  • Enable Macros

  • Log on to the database by navigating to Add-Ins> Qbyte Connect > Database Logon

    • Enter user ID, password and connect string.

  • Enter Cost Centres that require updates. Fill in only the fields that require updating

    • If updating CC Legals or Comments, fill in information in those respective tabs

  • Upload the data by navigating to Add-Ins > Qbyte Connect> Cost Centres / CC Legals / Comments > Update Records. 

  • Validation will run on the Cost Centres prior to being updated. Correct any issues that are identified by the validation process.

  • When updating the Cost Centres is complete, users will see the following message. Press Save to commit the changes, or Cancel to roll back the changes.

 

Technical Requirements

The following technical requirements must be met before using the upload spreadsheet:

  1. The following components from the Oracle Client package must be installed:

  • The standard Oracle Client 11g install package

  • The Oracle OLEDB provider

  1. A copy of the correct tnsnames.ora file must be placed in:

C:\[Oracle Client Install Directory]\product\[ClientVersionNumber]\client_[X]\network\admin

  1. The user account must have sufficient privileges to enable Macros “Protected Content” on the spreadsheet.

Copyright© 2024 IFS AB. Copying prohibited. All rights reserved.