22 Oct The One CONFIGURATION That Could Undermine Your Journal Entry Controls
The One Configuration that Could Undermine Your Journal Entry Controls
Background: In Oracle there are various seeded (i.e. provided upon installation of the applications) Sources of Journal Entries (JEs). Organizations implementing the application can also define more custom sources as part of their implementation. A custom source may be defined where a JE is being sent from another system.
Let’s take a look at the Journal Sources form:
There are various attributes that can be set for each Source. The only critical attribute we are going to look at in this section is the “Freeze Journals” configuration. When the “Freeze Journals” configuration is set to “Yes” the JE is frozen once it is imported into the General Ledger. Therefore, because it is frozen, it cannot be changed.
How could the improper setting of this “Freeze Journals” attribute undermine your entire Journal Entry controls…?
Typically, organizations have journal entry controls that require a manual journal entry to be approved, but the journal entries transferred from the various subledgers aren’t reviewed. Journal entries that are transferred from subledgers are typically not reviewed in the same way as manual journal entries because there are hundreds, thousands, or more transactions that make up the subledger activity. There is no way a user could properly review and approve a JE coming from the sales ledger (Receivables module) or the payables ledger (Payables module). Therefore, these JEs are typically ‘accepted as is’ when they are transferred from the various subledgers. The assumption is that there are proper controls within each of the subledgers that are used to review and approve the underlying transactions.
The risk for these ‘subledger’ activities is that the JE could be modified once it is transferred to the General Ledger. The modification of a subledger JE would be looked upon as a ‘manual’ adjustment to the JE and, therefore, should be subject to the same controls as are needed for a manual JE.
Auditors should run a query to identify how the Sources are configured as part of their testing of manual JE controls. There are two risks to consider in the context of the Journal Sources configuration:
- A Source has the setting “Freeze Journals” set to “No” as of the point in time the query is run
- A Source has had the setting changes to allow the JE to be modified at any point during the period under audit.
Putting my auditor hat on… I’d first need to understand the expectations of each source. For the ‘seeded’ sources related to manual JEs such as Manual and Spreadsheet, I’d expect the Freeze Journals to be ‘N’. For the seeded sources related to subledger JEs (Assets, Cash Management, Cost Management, Payables, Receivables, Inventory, Marketing, Payroll), I expect the Freeze Journals to be ‘Y’. Note that some sources (Recurring, Eliminations, and Allocations as examples) that are formula driven, the approval could be either at the point of the definition of the formula or the output of the generation process (i.e. the journal entry itself). The controls related to these Sources could vary from organization to organization.
For the custom sources the expectation could be either way. If the JE was to be reviewed as a manual JE, it should be set to ‘N’. If the JE was not supposed to be modified because there is reliance on the controls within the source system, it should be set to ‘Y’.
Use the query in Appendix A to identify which Sources have been used. For those used, ask the control owner related to the JE controls which sources are being reviewed as ‘manual’ JEs (and, therefore, the Freeze Journals setting could be ‘N’) and which sources should be frozen upon import (and therefore, the Freeze Journals setting must be ‘Y’).
For those sources that should NOT be modifiable once imported the Source must be set to ‘Y’ for the entire period under audit. From an audit perspective, you should review the setting as of the time the query is generated and also look at the created by and last updated by dates. If either field has a date within the audit period it means that one or more columns within that Source was changed or the row itself was added (or both). The ‘row who’ information (Created and Last Updated By fields) are changed when any of the columns within that row are changed. Therefore, the Last_Update_Date column could have been updated because of a change in the Description (benign), a change was made in the Journal Approval Required field (relevant if you are using Journal Approval Workflow), or a change was made in the “Freeze Journals” field.
By default, Oracle does not have the detailed history available to identify which of the columns was added / changed if the CREATION_DATE or LAST_UDPATE_DATE field changed. Absent this complete, detailed audit trail, you cannot get comfort as to whether the Freeze Journals configuration was changed if a Source was created or updated during the audit period. Therefore, any additional testing you choose to do cannot give you assurance as to whether or not that configuration was changed. Therefore, it is reasonable to reject reliance on that configuration and assume that the Freeze Journals configuration was changed during the audit period. This would likely invalidate the reliance on the subledger journal entries since the JE could have been manually manipulated once the JE was imported into the General Ledger.
The critical functions in question have a User Function Name of: Journal Sources (there are two Functions). The Function names are: GLXSTSRC and GLXSTSRC_G. Auditors looking to identify which Responsibilities and Users have this ability to make changes to Journal Sources can run the query in Appendix A. Only those employees that are supervised and trained in the change management process should have access to these functions in Production. Another best practice is that the development of any changes to this configuration should be separate from the migration of the changes to the Production environment.
The complete list of all Journal Sources can be queried using the query in Appendix B.
Appendix A – Query to pull the Sources that have been used–Distinct sources
ORDER BY 1;
Appendix B – Query to pull the Journal Sources configuration
SELECT a. JE_SOURCE_NAME JE Source Name
, a.DESCRIPTION Description
, a. USER_JE_SOURCE_NAME User JE Source Name
, a. JOURNAL_APPROVAL_FLAG Journal Approval Required Flag
, a. OVERRIDE_EDITS_FLAG Freeze Journals Override
, a.LAST_UPDATE_DATE Last Updated Date
, a.LAST_UPDATED_BY Last Updated By
, b.User_name User Name Last Updated
, a.CREATION_DATE Creation Date
, a.CREATED_BY Created By
, b2.user_Name User Name Created
From GL_JE_SOURCES_TL a
, fnd_user b
, fnd_user b2
Where a.created_by = b.user_id
AND a.last_updated_by = b2.user_id
NOTE: Query result for column “OVERRIDE_EDITS_FLAG” – ‘N’ means No (journals are NOT frozen), ‘Y’ means Yes (journals are frozen)
Appendix C: Query to identify which Users and Responsibilities have access to make changes to the Journal Sources configuration
Purpose: This query identifies the users and responsibilities that can access high risk single functions. The limitation of this query is that it does not take into account menu or function exclusions that may be applied at the Responsibility level.
select distinct fu.user_name user_name,fu.description user_description, fr.responsibility_name
resp_name,fff.function_name,fff.user_function_name, fff.description, ff.form_name, ff.user_form_name
from applsys.fnd_user fu,
and furg.responsibility_id = fr.responsibility_id
and furg.responsibility_application_id = fr.application_id
and fr.menu_id = fcmf.menu_id
and fcmf.grant_flag = ‘Y’
and fcmf.function_id = fff.function_id
and furg.user_id = fu.user_id
and sysdate between fu.start_date and nvl(fu.end_date, sysdate+1)
and sysdate between fr.start_date and nvl(fr.end_date, sysdate+1)
and fff.function_name in (
from apps.fnd_form_functions_vl fun, apps.fnd_form_vl form
where fff.function_name in (‘GLXSTSRC’, ‘GLXSTSRC_G’)
order by 1,2