Back to Spreadsheet Uploads

Introduction

The Business Associates (BA) upload spreadsheet is designed to assist users in uploading new Business Associates into the Qbyte Financial application. The current version of the spreadsheet can insert and update records in the database tables. The spreadsheet file consists of the following three worksheets:

1. Business Associates Masters Worksheet

2. BA Addresses Worksheet

3. BA Comments Worksheet

Roles

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

• PWSQL_01_BA_MAINTENANCE

AND/OR

• PWSQL_50_SUPER_USER

Feature Highlights

Column Select Functionality

The Business Associates 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 > Business Associates > Show Available Columns

Open

Open

Duplicate Name Check

The business associates master worksheet will give a warning message on validation if the BA name for the new BA is the same as the BA Name for an existing BA in the application. This warning message is there to inform the user: users will be allowed to upload BAs with the same name as existing BAs in the application.

Open

User Defined Fields (UDFs)

The business associates master worksheet allows users to input information into thirty 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 Codes.

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

• BA_UDF_X_CODE (e.g. UDF # 1)

OR

• BA_UDF_XX_CODE (e.g. UDF # 10)

Process Workflow

Uploading a New Business Associate

1. Open spreadsheet file

2. Enable Macros Content on worksheet if required

3. Select the Business Associates Worksheet

Open

4. Connect to Database

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

Open

2. Input BA information. Ensure that required fields are populated. 

Required General BA Information:

• Business Associate ID (auto generated if not specified)

• Business Associate Name

• BA Type

• Effective Date (Defaults to the present date if not specified)

• Non Resident Flag (Defaults to ‘N’ if not specified)

• BA Short Name (Defaults to BA Name if not specified)

• Legal Name

• FM BA Flag (Defaults to ‘Y’ if not specified)

• LM, PM, and EXT BA Flags (Default to ‘N’ if not specified)

• External Application Code

  • Will default to the value found in SUBSYSTEMS #1 QBYTE-CORE --> #1 CORE --> EXT_APPLICATION_CODE

• BA Address Information (Warning message will be generated if no address information is inputted)

3. Validate Data

• Ribbon > Add-Ins > Qbyte Connect > Business Associates > Validate Records

Open

4. 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.

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

6. Insert Records

• Ribbon > Add-Ins > Qbyte Connect > Business Associates > Insert Records

Open

• 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 Address Information to an Existing Business Associate

1. Open spreadsheet file.

2. Enable Macros Content on worksheet if required

3. Select BA Addresses Worksheet

Open

4. Connect to Database

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

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

• BA ID

• FM, LM, PM, and EXT BA Flags (Default to ‘N’ if not specified)

• External Application Code

  • Will default to the value found in SUBSYSTEMS #1 QBYTE-CORE --> #1 CORE --> EXT_APPLICATION_CODE

• Payment Code

• Default Address Flag (Will Default to “N” if not specified)

• Address Name

• Address Type

• Effective Date (Will default to the present date if not specified)

• Address Line 1

• Alternate BA Name 1

6. Validate Data

• Ribbon > Add-Ins > Qbyte Connect > BA Addresses > 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 > BA Addresses > 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 BA Address ID column will populate with the ID number for the record in the BA_ADDRESSES table.

Uploading Comment Information to an Existing Business Associate

1. Open spreadsheet file

2. Enable Macros Content on worksheet if required

3. Select Comments Worksheet

Open

4. Connect to Database

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

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

• BA ID

• Comment Type Code

• Comment Text

• 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 > BA 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: Comment ID column will populate with the ID number for the record in the COMMENTS table.

Tip and Tricks

Inactivating a Business Associate

The FM BA, LM BA, PM BA and Ext PA columns (columns R-U) relate to the Financial Management tab, Land Management tab, Production Management tab and External tab respectively in the Business Associates screen.  Therefore, if you want to update fields on the Financial Management tab you need to set the FM BA column to "Y" (yes). 

For example, to inactivate a Business Associate and it's address in it's entirety from a Financial Management perspective, you need to:

• Enter the BA ID in the Business Associate ID column (column A)

• Enter the Business Associate Inactive Date in the Inactive Date column (column I)

• Set the FM BA column to Y (column R)

• Enter the Business Associate Address Inactive Date in the Address Inactive Date column (column AB)

After choosing Ribbon > Add-Ins > Qbyte Connect > Business Associates > Update Records and choosing Save, your Business Associate masterfile will be updated in Qbyte Financial:

Open

Open

Open

Technical Notes

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 install

• The oracle OLEDB provider

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

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

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

4. A Qbyte Connect license is required