Register a Source System Using a Spreadsheet
After completing the source system transaction flow analysis, you can use a spreadsheet to register your source systems in Accounting Hub. The spreadsheet registration accelerates the time from implementation and testing to production.
Complete the following tasks to register your source system.
1. Download the Spreadsheet Template
Download the spreadsheet template using the Create Subledger Application Setups in Spreadsheet task.
Navigate: Setup and Maintenance work area > Define Accounting Entry Configuration for Rapid Implementation > Create Subledger Application Setups in Spreadsheet
2. Populate the Spreadsheet Template
Populate source system and transaction information in the spreadsheet template.
The spreadsheet template has three worksheets:
-
Source System
-
Transaction Information
-
Line Information
Source System
On the Source System worksheet, enter the name of the source system and the transaction types.
In the Source System Transactions region, enter the source system name and a short name:
This table contains definitions of the Name and Short Name fields used in the spreadsheet.
Name |
Short Name |
---|---|
Name of the source system.
Maximum length 25 characters. |
Used as the column name in database. |
This source system name is used as the registered subledger, event class, and journal source names in Accounting Hub.
-
Event class represents the transaction and is used to group event types.
-
Event types represent an event in the life cycle of a transaction that has accounting impact.
In the Transaction Types region, enter the transaction types.
This table contains definitions of the Name and Short Name fields used in the spreadsheet.
Name |
Short Name |
---|---|
Transaction type name. Maximum length 30 characters. |
Used as the column name in database. |
Transaction Information
The Transaction Information worksheet lists transaction information that can be used for accounting. These are header-level information sources.
The worksheet includes three predefined required sources.
-
By default, the Transaction Date provides the accounting date for booking the journal.
-
You can add additional source attributes for the transaction date if there are multiple date options that are used as accounting date.
-
-
Transaction Number links the transaction and line information.
-
Ledger Name is the reporting entity for which journal entries are booked in the general ledger.
In the Transaction Information: List of Sources region enter the sources.
This table contains definitions of the Name, Short Name, Type and Journal Display fields used in the spreadsheet.
Name |
Short Name |
Type |
Journal Display? |
---|---|---|---|
Name of sources that appears in a drop-down list for accounting rules. Maximum length 80 characters. |
Used as the column header in the transaction data template. This template is used to upload source system transactions. |
Used to describe the type of values from the source system. Available values are:
Up to 50 text, 10 number, 10 dates, and 5 long text sources can be defined. This includes the three predefined required sources. |
Indicates whether this attribute is shown on report and inquiries, along with the journal. Acceptable values are Yes or No. In the user interface, these are known as user transaction identifiers. Up to ten rows can be used. |
Line Information
The Line Information worksheet lists types of transaction information that can have more than one value per transaction. For example, an order or invoice line item amount.
The worksheet includes three predefined required sources.
-
Transaction Number links transaction and line information.
-
Default Amount holds transaction amount values for use in accounting.
-
Default Currency holds entered currency values for use in accounting.
Additional sources can be added if there are multiple different values that use the same source attribute. In that case, you must manually map the related accounting attributes with these additional sources from the user interface.
In the Transaction Lines Information: List of Sources region enter the line sources.
This table contains definitions of the Name, Short Name, Type and Chart of Accounts Value fields used in the spreadsheet.
Name |
Short Name |
Type |
Chart of Accounts Value? |
---|---|---|---|
Name of sources that appears in a drop-down list for accounting rules. Do not add Line Number source name, as this name is reserved for automatically created internal source. Maximum length 80 characters. |
Used as the column header in the transaction data template. This template is used to upload source system transactions. |
Used to describe the type of values from the source system. Available values are:
Up to 100 text, 30 number, 10 dates, and 5 long text sources can be defined. This includes the three predefined required sources. |
Indicates that the source can be used to derive an account. Acceptable values are Yes or No. Only text type sources can be used in segment rules. Examples of sources that are typically used to derive the general ledger account:
|
3. Validate
To verify the accuracy of the content in the spreadsheet template, use the Validate icon on the Source System worksheet.
-
If there is an error, you receive an error message.
-
A Validation Report worksheet is created.
-
Open the Validation Report worksheet to review the error.
-
Correct the error and validate the content again.
4. Generate .zip File
Generate the .zip file by using the Generate .zip icon on the Source System worksheet.
You receive a notification that the .zip file was created and saved in the same local folder as the downloaded spreadsheet template.
5. Upload .zip File
Use the Create Subledger Application Setups in Spreadsheet task to upload the .zip file.
-
Use the Upload Setup File button.
-
Browse for your .zip file.
-
Select Import.
If there is any error during the upload process, you're prompted with an error message. Correct the error in the spreadsheet in the fields noted in the error message.
If there is no error:
-
The source system is now registered in Accounting Hub.
-
One transaction object at header and line levels is created.
6. Verify Source System
Verify the uploaded source system and transaction information from the user interface.
You can update the uploaded objects from the user interface. For example:
-
Accounting Event Types:
-
Add new event types.
-
-
Sources:
-
Assign and update value set and lookup type assignments. Whenever appropriate, sources can have lookup types or value sets assigned. Assigning a value set or lookup type enables you to predefine valid values for the source that's used to create accounting rules.
-
You can revise the sources. These revisions are:
-
Source names can be revised to be more business user-friendly so they're easily understood when configuring accounting rules.
-
Sources that correspond to accounting flexfield identifiers can be marked as such.
-
-
You can add sources from the user interface. The download of a new transaction data template includes this newly added source.
-
-
Accounting Attribute Assignments:
An accounting attribute is a piece of the journal entry. The mapping of sources to accounting attributes specifies how the Create Accounting process gets the value for each piece of the journal entry. For example, the Entered Currency attribute is used to map source values to the entered currency field for subledger journal entry lines.
-
Add and update source assignments to accounting attribute.
-
You can add and make updates to the source assignment to the accounting attribute if you have other sources available. For example, when you have more than one transaction date source value.
-
-
7. Configure Accounting Rules
Next, configure the accounting rules using the sources from the transaction information.
Use the streamlined navigation across rule components to configure the accounting rules.
-
Start by using the Configure Accounting Rules task from the Setup and Maintenance work area. This task is also embedded in the Define Accounting Entry Configuration for Rapid Implementation task list.
-
Highlight the subledger application that you would like to use to configure accounting rules.
-
On the Configure Accounting Rules user interface, navigate across the rule components that you would like to create or edit.
Define Accounting Methods
Define accounting methods to group subledger journal entry rule sets. This determines how the source system transactions are accounted for a given ledger.
Assign journal entry rule sets to event class and event type combinations in an accounting method. This determines how the subledger journal entries for that class or type are created.
The following are the components of a journal entry rule set:
This table contains the accounting rule names and descriptions.
Rule |
Description |
---|---|
Journal Line Rule |
Determine basic information about a subledger journal entry line. Such information includes whether the line is a debit or credit, the accounting class, the currency, the amounts, and conversion rates. |
Description Rule |
Determine the descriptions that are included on subledger journal entry headers and lines. Include constant values, transaction attribute, and transaction information in descriptions. |
Account Rule |
Determine which account is used for a subledger journal entry line. |
Supporting Reference |
Optionally used to store additional source information about transactions. Are useful for reconciliation of accounting to source systems, as well as reporting. |
Conditions
You can attach conditions to journal line rules, description rules, and account rules components. A condition combines constants, source values, and operands to indicate when a particular journal line rule, description, or account rule is used. For example, for mortgage loans, you can elect to use a specific loan receivable account based on the loan type.
- To obtain a listing of accounting rule setup by ledger, submit the Subledger Accounting Method Setups Report.
-
The maximum size allowed for a numeric source in the description rule is 24 digits to the left of the decimal and 38 digits to the right of the decimal point.