AFE Spreadsheet Upload
- 1 Introduction
- 2 Roles
- 3 Feature Highlights and System Configuration
- 4 AFE Auto Numbering
- 5 Legal Locations Setup
- 6 System Defaults
- 7 User Defined Fields (UDFs)
- 8 Process Workflow
- 9 Uploading a New AFE
- 10 Uploading Legal Location Information to an Existing AFE
- 11 Uploading Comment Information to an Existing AFE
Introduction
The Authorizations for Expenditures (AFE) upload spreadsheet is designed to assist users in uploading new AFEs into the Qbyte Financial Management application. The current version of the spreadsheet can insert and update records in the database tables. The spreadsheet file consists of the following work sheets:
Masters Worksheet
Descriptions Worksheet
Operators Worksheet
Comments Worksheet
Cost Centres Worksheet
Legal Locations Worksheet
Cost Estimate Items Worksheet
Approvals Worksheet
Project Justifications Worksheet
Supplements Worksheet
Revisions Worksheet
Roles
Users must be granted one of the following roles before they can use the upload spreadsheet:
PWSQL_04_AFE_MAINT_AND_PROCESS
OR:
PWSQL_50_SUPER_USER
Feature Highlights and System Configuration
Column Select Functionality
The AFE 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 navigating to:
Ribbon > Add-Ins > Qbyte Connect > AFE Master > Show Available Columns
AFE Auto Numbering
The new upload spreadsheet allows you to utilize existing numbering schemes set up in the application.
Legal Locations Setup
The upload spreadsheet allows you to attach legal location information to an AFE. You must input:
1. A Valid Survey System Code.
This 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 you have not specified them) will auto-populate with default values defined at the system or database level:
1. Use OM Flag
Database Default is set to N.
2. Alt GL Code Flag
Database Default is set to N.
3. Capital/Expense Flag
System Default: #2 Qbyte FM > #7 Work In Progress Transfer > CIP_RESULTS_PROJ_MTNCE.
User Defined Fields (UDFs)
The AFE Master worksheet allows you to input information into twenty different User Defined Fields (UDF) fields. These UDF fields can be customized within the application according to your 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 AFE UDF fields in the application are in the following format:
AFE_UDF_X_CODE (e.g. UDF # 1)
OR:
AFE_UDF_XX_CODE (e.g. UDF # 10)
Process Workflow
General Workflow (applicable to all worksheets)
1. Open the spreadsheet file.
2. Enable Macros Content on the worksheet if required.
3. Select any Worksheet.
4. Connect to the Database:
Ribbon > Add-Ins > Qbyte Connect > Database logon
5. Input information. Ensure that required fields are populated (column headers will be highlighted in blue).
6. Validate Data:
Ribbon > Add-Ins > Qbyte Connect > "Worksheet" > 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 > "Worksheet" > Insert Records
The spreadsheet will automatically validate data before actual upload.
The message box will pop up indicating that the process has completed.
The message box will display the number of valid and erroneous records.
You 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 a New AFE
1. Open the spreadsheet file.
2. Enable Macros Content on the worksheet if required.
3. Select the Master Worksheet.
4. Connect to the Database:
Ribbon > Add-Ins > Qbyte Connect > Database logon.
5. Input information. Ensure that the following required fields are populated:
AFE Name
AFE Number - Required if auto-numbering is deactivated in the application.
AFE Type Code
Managing Organization ID
Ownership Organization ID - Will default to value in Managing Org ID if not specified.
AFE Amount
AFE Status Code - Will default to 'U'napproved if not specified.
Use OM Flag - Autopopulates.
System Default:
#2 Qbyte FM
#16 Core
DFLT_COST_CENTRE_OM_FLAG
WIP Transfer Flag - Will default to N if not specified.
Accrual Flag - Will default to N if not specified.
Percent Interest
Translation Rate - If operating and reporting currencies for the Managing Org are different.
6. Validate Data:
Correct any error messages and validate again.
7. Insert Records.
Uploading Legal Location Information to an Existing AFE
1. Open the spreadsheet file.
2. Enable Macros Content on the worksheet if required.
3. Select the AFE 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:
AFE Number
Primary Flag
Survey System Code
Location Elements 1-8 - Dependent on survey system code settings defined in the application.
6. Validate Data:
Correct any error messages and validate again.
7. Insert Records.
8. Upon successful insert: the AFE Legal ID column will populate with the ID number of the record in the COST_CENTRE_LEGALS table.
Uploading Comment Information to an Existing AFE
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:
AFE Number
Comment Type Code
Comment Text
Comment Date
6. Validate Data:
Correct error messages and validate again.
7. Insert Records.
8. Upon successful insert: the Comment ID column will populate with the ID number of the record in the COMMENTS table.
Copyright© 2024 IFS AB. Copying prohibited. All rights reserved.