Query for GL Journal Creation, Approval, and Posting by the Same User

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 SQLGuru.ai for more advanced Oracle SQL insights, audit-ready validation scripts, and data governance solutions tailored for enterprise environments.