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
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
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
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.
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
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.
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)
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.
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.
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.
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.
Do not select the Validate Records option. If selecting the Validate Records option, fields which are not populated will err out.) |
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.
The following technical requirements must be met before using the upload spreadsheet:
The following components from the Oracle Client package must be installed:
The standard Oracle Client 11g install package
The Oracle OLEDB provider
A copy of the correct tnsnames.ora file must be placed in:
C:\[Oracle Client Install Directory]\product\[ClientVersionNumber]\client_[X]\network\admin
The user account must have sufficient privileges to enable Macros “Protected Content” on the spreadsheet.