In this topic, we shall be talking about Configuring Accounting Rules.
From the previous articles, we have understood how to get a data model by using the transaction object concept, and how to get the event model as part of the source system registration process. The next model that we need to think about is the accounting model. Here, we will talk about the concept of configuring the accounting rules.
An accounting method contains different components, and under one accounting method, you will have journal entry rule sets. Each journal entry rule set will be carrying journal line rules, account rules, description rules, and supporting references. And each one of these individual components may utilize conditions, custom formulas, and mapping sets.
Each Subledger application will have a unique Journal Entry Rule Set that groups all the rules together for creating journals. Journal entry rule set like a grouping component which groups all the other definitions that you might have defined under a subledger application.
The Journal Line Rules defines the debit and credit side of the journal entry. The journal line rules are also assigned to the journal entry rule set. There are multiple active rules that are governed by the journal line rules, such as identifying which line is a debit line, which line is a credit line for a particular transaction type.
And then we also have Account Rules which drives the GL account or segment for each debit and credit line of a journal. You may have multiple Journal Line Rules attached to the Journal Entry Rule Set and against each of those journal line rules, you will have to attach an accounting rule so that the system knows which account rule is applicable to enter the values for the respective segments.
Then accounting methods may also use some optional components such as Description Rules and Supporting References.
Description rules can be defined to dynamically extract the description for a particular subledger journal entry. This rule enables you to combine source system values and constant values to use as a description for the Journal entry.
For example, I want the loan officer name and Date of the Loan Origination in the description of the journal entry. I would need to define those as a source and concatenate them together with constant values and group them into a description rule. Using that description rule, whenever a journal entry is generated, the system will generate a dynamic description either at the header level or at the line level.
Another component that you can have is the Supporting Reference. These are basically used to store additional source information about transactions. They are also useful for reconciling the accounting with your source systems. They are also capable of storing the balances for a segment that might not be part of your chart of accounts.
For example, I want to identify the total loan that has been disbursed by a particular loan officer. So I can define loan officer as a supporting reference. Whenever a journal is posted, the system will keep on accumulating the amount by each of the loan officers. Then you can inquire on the balances using that supporting reference. When you click on the supporting reference name or when you search by the supporting reference, it will give you the consolidated amount, at the loan officer supporting reference level. Using supporting references, you can also drill down journal entries by each of the loan officers to see the amount that has been posted into their respective accounts.
Then we have another optional set-up called Mapping Sets. Mapping sets will have a direct relationship with account rules, and helps you map your source system journal entry rules to your Oracle chart of accounts.
Automatically, the system will create one header level object and one line level transaction object. Those transaction objects have been utilized to generate the sources. Then the sources are also automatically generated based on the transaction objects and are assigned to the corresponding accounting event classes. These sources will always be used as a value when you design your accounting rules.
What is stored at the header transaction object level? Header transaction objects are used to store one row with header source values for each transaction that comes from the source system. This header object will always get populated with the transaction information that is coming from the source system. So for every transaction that comes from the source system, we need one header level record.
Each transaction is identified by its unique transaction number. And each transaction is also automatically assigned with an event as well. The transaction details whose values do not vary by transaction line or distribution should normally be stored in the header transaction objects only. So if you do not have line level details for bifurcating that value that you're capturing at the other level, so only header level information is enough to create the accounting entry.
What is stored at the line transaction object level? If you have a requirement where one header may have multiple lines, then apart from populating the data into the header level object, we need to populate the data into the line level objects also. So line transaction objects will store the details of transactions that vary based on transaction attributes.
For example, a mortgage transaction for loan origination may have multiple amounts, each related to different components of the loan, such as a loan origination amount, closing cost amount, and escrow amount, and you want to charge them to separate accounts. So each of these amounts could be captured as separate lines, along with an indication of the amount type. One row per distribution is linked to the associated transaction by transaction number. So at the header level, you will have the transaction number. And same transaction number will be referenced at the line level also. Because against that header, we want to have three different lines.
Each row is also automatically assigned with a line number. Because for the line level transaction object, line number is a required field. Transaction details whose values will vary by transaction line or distribution are normally stored in the transaction object column, for example, the loan number for a loan payment.
If there is no breakdown for the amount, it's OK to capture that at the header level itself. Otherwise, then you must populate the values at the line level transaction object as well.
Sources
Each and every column that is part of your transaction object will be converted into a source. So you must assign an update value, certain look-up type assignments whenever appropriate sources can have lookup types or value sets assigned to them.
Assigning a value set or lookup type enables you to pre-define valid values for the sources that is used to create accounting rules. So basically, when the transaction object has been registered, you can look into the sources that system has generated against that transaction object. And against each of these columns that are part of the transaction object, you can associate them to a lookup code also so that whenever that will be used as a source while designing the accounting rules, you can pull the values from those lookup codes.
Accounting Attribute
Accounting attribute is a piece of journal entry. The mapping of the sources to accounting attribute specifies how the Create Accounting process will get its values for each of the piece within the journal entry. For example, the Entered Currency attribute is used to map the source values to the Entered Currency field for the subledger journal entry lines.
You have also got an option to add and update the source assignments to accounting attributes as per your requirement.
Go to the Functional Setup Manager (also known as the Setup and Maintenance work area)
Click on the Task Panel and choose Search
Search for a task called Create Subledger Application Setups in Spreadsheet
When you get to that task page, you will see a button on that page called "Download Setup Template" When you click on that button, a pop-up message will appear to open the spreadsheet template. Open it, and then you can start populating the data.
Once you're done populating the spreadsheet. Validate the information and correct any errors.
If there are no errors left, click on generate ZIP and it would generate a Zip file containing the spreadsheet information.
Go back to the task page and click on Upload setup data and choose the generated Zip file and click OK.
Once upload is complete, you may verify the created subledger application by going to the Manage Subledger Application task.
The first sheet on the template describes the instructions that you should follow to enter the data for registering your source system. The spreadsheet template will always have three worksheets:
Source system
Transaction Information
Line Information
Source system. This is where you will provide all the basic information, such as the name of the source system, event class name, event type information, and so on. This sheet has two sections: The Source System Transaction section and the Transaction Type Section:
In the source system transaction section and transaction type sections, you will provide the following:
Column Name
Max Length
Data Type
NAME
25
Character
SHORT_NAME
25
Character
The information that you provide in the source system transaction section is used for registering the source system name and the event class name, as well as to register the journal source name.
Meanwhile, the information that you provide in the transaction type section is used to represents an event in the lifecycle of a transaction that will always have accounting entries. The entries you've put in the transaction type section is grouped under the event class you've defined from the source system transaction section.
Transaction Information. After naming the source system, event class, and the event types, the next thing we need to figure out is what transaction attributes we are going to bring in from the source system.
The transaction information worksheet lists the sources that are used for setting up accounting rules. The system will create a transaction object using these details and each entry in that transaction object will be converted into a source, that will be used when you're designing the accounting model. You would have to define the below fields:
Column
Name
Max
Length
Remarks
NAME
80
SHORT_NAME
TYPE
Indicates
what Data Type the Source will have
JOURNAL
DISPLAY?
3
indicates
whether this source will be is shown on reports
and in inquiries along with the journal.
Values are Yes/No
The Transaction Information spreadsheet has three mandatory fields:
Transaction Date is, by default, used as the accounting date.
Transaction Number links the transaction information and the line information together. Use a unique transaction number so that the source system segregates each transaction differently.
Ledger Name is the reporting entity for which journal entries are booked in the GL application.
Apart from the three mandatory sources, you can add more sources as required. For Example, it may be relevant fields such as a loan schedule payment date, loan maturity date, loan type, loan amount, etc.
These additional fields are also known as User Transaction Identifiers. You can have up to 10 rows that can be used as user transaction identifiers. They constitute the user-oriented key of the underlying subledger transactions. These identifiers are primarily used in accounting events when you perform inquiries or you run accounting event-specific reports. The transaction data that identifies the transaction varies by subledger application. And accounting event reports and inquiries will display the transaction identifiers and their labels as appropriate for the corresponding event class.
Line Information. This sheet lists the type of transaction information that can have more than one value per transaction. There is a possibility that one transaction may have multiple lines. If that is the case, then you must provide those line level fields in this particular sheet.
The worksheet includes three predefined required sources:
Transaction number field links the transaction header with the line information. For example, if I have one transaction and it has got three lines, we need to ensure that the transaction number that we have given at the header level, the same transaction number should be given for all the lines so that system knows that all these three lines pertains to one header.
Default amount holds the transaction amount values that will be used when generating accounting entries
Default currency holds the entered currency value that you will be using in the accounting process.
Additional line sources can be added if multiple and different values are required for use in the same source attribute. The same details for the Transaction information spreadsheet applies to the Line Information spreadsheet. You must populate the Name, Short Name and Type fields.
The "Chart Of Account Value?" column indicates whether that source can be used to derive an account or not. There is a possibility that you might be using one of the fields to derive the chart of account values. For example, the salesperson name or maybe loan type. Acceptable values for this field is Yes or No and only text type sources can be used in segment rules.
An example of a source that would typically be used to derive general account could be Account, Cost Center, or Company. They could also be predefined values, so rather than using them as a derivation factor, you can directly pick up a field here called account value. So whenever the system will generate the accounting entry, if you have mapped it to this source, system will use that as a default value for the segment values under your chart of accounts.
Verifying and Uploading the Spreadsheet Data
Once the data has been populated into each of the three sheets, you will have to ensure that you have submitted the validation process. To verify whether the data that you have entered is accurate or not, you will need to click the Validation button in the Source System sheet.
If an error is generated, you will receive the error message and the system will also generate a validation report as a separate worksheet. You must open the validation report worksheet to review the errors. Then you must correct the error and validate the content again.
Once there are no more errors, then you can generate the zip file by clicking on the "Generate Zip" button in the Source System sheet and save the zip file. You will also receive a notification that the zip file was created and saved in the same local folder as the download spreadsheet template.
Once the file has been saved, the next step will be to upload the setup zip file. Go back to the application page and in the Create Subledger Application Setups in Spreadsheet task, there is another button available called "Upload Setup File". When you click on that, you will get an option to browse the file from your local machine. Choose the file that you generated and then choose "Select Import".
If errors exists during the upload process, you will be prompted with an error message. You must correct the error in the spreadsheet in the fields that have been notified in the error message box. If there are no errors, you will be able to successfully register the source system along with the underlying components like transaction objects, event models, and so on. The files uploaded will basically create the subledger setups in a single step. You don't have to do anything.
Editing the Uploaded Subledger Setups
Sometimes there is a possibility that you may be interested in overriding the uploaded source system information. In that case, you can re-upload the spreadsheet template for the same source system if you have not created any accounting rules under that source system.
You can download the template again, make changes, and load the data one more time. Once the data has been successfully uploaded, you can go to that particular source system from within the Setup and Maintenance work area and verify whether everything has been loaded successfully or not.
You can also update the uploaded setups from the user interface. For example, You can add new event types if you think that the event types that have been loaded are not enough for your requirement.
Also, if you want to make the changes to the source name the system has created to suit your business requirement, you can edit them so that they are easily understood when configuring accounting rules. It should be a user-friendly name, which every user who is involved in designing your accounting rules should be able to recognize them.
You can also add new sources from the user interface. The download of a new transaction data template will include this newly-added source as well. Then you can also complete the accounting attribute assignment, though when you upload the data, accounting attribute assignment happens automatically.
Post-Upload Process
Once the upload has successfully processed, Transaction objects (including Transaction Attributes) will get created automatically and can now be used to configure accounting rules through the Subledger Accounting user interface. Also, a Journal Source, Process Category and Event Class has also been automatically created using the name of the Source System.
Below is a quick step-by-step video demonstration of Creating Custom Subledger Application Setups in Spreadsheet:
