In Oracle General Ledger (GL), maintaining financial control and transparency is essential for every organization. One key area of compliance involves ensuring that no single user performs all critical actions, such as creating, approving, and posting the same journal entry.
However, in some business cases or during audit investigations, it becomes important to validate whether such scenarios exist. The following SQL queries help identify journals where the same user has created, approved, and posted the transaction, an important indicator for Segregation of Duties (SoD) compliance and audit review.
Scenario
This validation focuses on identifying all GL Journals (Manual Source) that were:
- Created,
- Approved, and
- Posted
by the same user in Oracle E-Business Suite (EBS).
Such cases are potential control exceptions, requiring management attention or audit justification.
Query 1: GL Journal Created, Approved, and Posted by the Same User
SELECT
NAME AS “Ledger Name”,
SHORT_NAME AS “Ledger Short Name”,
LEDGER_CATEGORY_CODE,
APPROVER_EMPLOYEE_ID,
b.je_batch_id AS batch_id,
b.Name AS “Batch Name”,
b.Description AS “Batch Desc”,
h.je_header_id AS header_id,
h.Name AS “Journal Name”,
h.description AS “Journal Desc”,
je_source,
h.currency_code,
h.ledger_id,
(SELECT user_name FROM fnd_user WHERE user_id = h.created_by) AS Entered_By,
(SELECT user_name FROM fnd_user WHERE user_id = b.posted_by) AS Posted_By,
appr.user_name AS approver_name,
h.creation_date,
b.posted_date
FROM
gl_je_headers h,
gl_je_batches b,
gl_ledgers l,
fnd_user appr,
(SELECT item_key, ORIGINAL_RECIPIENT, MESSAGE_NAME, subject
FROM wf_notifications
WHERE message_type = ‘GLBATCH’
AND message_name IN (‘NOTIFY_NO_APPROVAL_REQUIRED’, ‘REQUEST_APPROVAL’)) a_his
WHERE
b.je_batch_id = h.je_batch_id
AND b.status = ‘P’
AND je_source = ‘Manual’
AND h.creation_date > TO_DATE(’01-JAN-2017′, ‘DD-MON-YYYY’)
AND (b.created_by = b.posted_by)
AND (b.created_by = appr.user_id)
AND ORIGINAL_RECIPIENT = appr.user_name
AND l.ledger_id = h.ledger_id
AND SUBSTR(a_his.item_key, 1, INSTR(a_his.item_key, ‘*’) – 1) = h.je_batch_id
ORDER BY h.je_header_id;
Purpose
This query checks for journals where the same user created, approved (via workflow notification), and posted the journal batch.
It cross-references WF_NOTIFICATIONS to confirm the approval originated from the journal’s creator.
Query 2: GL Journal Created, Approved, and Posted by the Same User (Using Item Attributes)
SELECT
NAME AS “Ledger Name”,
SHORT_NAME AS “Ledger Short Name”,
LEDGER_CATEGORY_CODE,
APPROVER_EMPLOYEE_ID,
b.je_batch_id AS batch_id,
b.Name AS “Batch Name”,
b.Description AS “Batch Desc”,
h.je_header_id AS header_id,
h.Name AS “Journal Name”,
h.description AS “Journal Desc”,
je_source,
h.currency_code,
h.ledger_id,
(SELECT user_name FROM fnd_user WHERE user_id = h.created_by) AS Entered_By,
(SELECT user_name FROM fnd_user WHERE user_id = b.posted_by) AS Posted_By,
appr.user_name AS approver_name,
h.creation_date,
b.posted_date
FROM
gl_je_headers h,
gl_je_batches b,
gl_ledgers l,
fnd_user appr,
(SELECT TEXT_VALUE, item_key
FROM wf_item_attribute_values
WHERE item_type = ‘GLBATCH’
AND name IN (‘APPROVER_NAME’, ‘MANAGER_NAME’)) a_his
WHERE
b.je_batch_id = h.je_batch_id
AND b.status = ‘P’
AND je_source = ‘Manual’
AND h.creation_date > TO_DATE(’01-JAN-2017′, ‘DD-MON-YYYY’)
AND (b.created_by = b.posted_by)
AND (b.created_by = appr.user_id)
AND TEXT_VALUE = appr.user_name
AND l.ledger_id = h.ledger_id
AND SUBSTR(a_his.item_key, 1, INSTR(a_his.item_key, ‘*’) – 1) = h.je_batch_id
ORDER BY h.je_header_id;
Purpose
This variation utilizes the WF_ITEM_ATTRIBUTE_VALUES table to identify approval actions tied to the same user who created and posted the journal.
It ensures an alternative validation path if workflow notifications are not available.
Query 3: GL Journal Created, Approved, and Posted by the Same User (Using Activity Status)
SELECT
NAME AS “Ledger Name”,
SHORT_NAME AS “Ledger Short Name”,
LEDGER_CATEGORY_CODE,
APPROVER_EMPLOYEE_ID,
b.je_batch_id AS batch_id,
b.Name AS “Batch Name”,
b.Description AS “Batch Desc”,
h.je_header_id AS header_id,
h.Name AS “Journal Name”,
h.description AS “Journal Desc”,
je_source,
h.currency_code,
h.ledger_id,
(SELECT user_name FROM fnd_user WHERE user_id = h.created_by) AS Entered_By,
(SELECT user_name FROM fnd_user WHERE user_id = b.posted_by) AS Posted_By,
appr.user_name AS approver_name,
h.creation_date,
b.posted_date
FROM
gl_je_headers h,
gl_je_batches b,
gl_ledgers l,
fnd_user appr,
(SELECT s.assigned_user, item_key
FROM wf_item_activity_statuses s,
wf_process_activities p
WHERE item_type = ‘GLBATCH’
AND p.instance_id = s.process_activity
AND notification_id IS NOT NULL) a_his
WHERE
b.je_batch_id = h.je_batch_id
AND b.status = ‘P’
AND je_source LIKE ‘Manual’
AND h.creation_date > TO_DATE(’01-JAN-2017′, ‘DD-MON-YYYY’)
AND (b.created_by = b.posted_by)
AND (b.created_by = appr.user_id)
AND assigned_user = appr.user_name
AND l.ledger_id = h.ledger_id
AND SUBSTR(a_his.item_key, 1, INSTR(a_his.item_key, ‘*’) – 1) = h.je_batch_id
ORDER BY h.je_header_id;
Purpose
This third version leverages WF_ITEM_ACTIVITY_STATUSES to track assigned users within workflow activities, confirming whether the journal’s creator, approver, and poster are the same individual.
How These Queries Help
These SQL queries empower internal audit teams, finance controllers, and system administrators to:
- Identify journals violating Segregation of Duties (SoD) principles.
- Ensure proper workflow approval hierarchies are followed.
- Strengthen financial governance and compliance reporting.
- Detect manual posting risks that bypass multi-user controls.
Best Practices
- Schedule these validations monthly or quarterly as part of audit routines.
- Integrate results into Oracle BI dashboards or GRC reports.
- Cross-reference with user role mappings in Oracle ERP to validate access rights.
- Apply filters for critical ledgers or cost centers for targeted reviews.
Key Takeaways
- Detect journals created, approved, and posted by the same user.
- Enhance audit transparency in GL workflows.
- Strengthen SoD compliance and financial integrity.
- Works seamlessly across Manual Journal Sources in Oracle E-Business Suite.
Conclusion
In today’s compliance-driven financial landscape, identifying single-user journal processing is vital for risk mitigation and regulatory assurance.
These three Oracle SQL queries provide auditors and administrators with actionable visibility into GL journals processed entirely by one user, enabling proactive control over potential SoD conflicts.Stay connected with SQL Guru AI for more advanced Oracle SQL insights, audit-ready validation scripts, and data governance solutions tailored for enterprise environments.