ARM_COMMENTS, where the words live

The report builder offers a comment count. The auditor wants the comments. This table holds them, keyed by OBJECT_ID, and one correlated subquery hands them over.

Note: ARCS, Oracle's account reconciliation module, keeps the commentary preparers and reviewers write on each reconciliation. Auditors ask for it; the report columns do not offer it plainly; this table is where it lives.

All ARCS table references hang off Tables & views.

RuleJoin comments by OBJECT_ID to the reconciliation, decide latest versus all up front, and export them with the status so the narrative travels with the number.

Neverpromise the auditor the commentary from the standard report picker. It counts; it does not quote.

◆ The pattern, the correlated subquery, and the two shaping decisions.

Auditors ask for the commentary; the query column list offers a count of comments, not the words. The working pattern, published by a practitioner and still the cleanest route: a correlated subquery against ARM_COMMENTS keyed on OBJECT_ID, returning COMMENT_TEXT beside each reconciliation row.

SELECT r.RECONCILIATION_ACCOUNT_ID,
       p.PERIOD_NAME,
       (SELECT c.COMMENT_TEXT
          FROM ARM_COMMENTS c
         WHERE c.OBJECT_ID = r.RECONCILIATION_ID) AS "COMMENT"
FROM ARM_RECONCILIATIONS r
JOIN ARM_PERIODS p ON p.PERIOD_ID = r.PERIOD_ID
WHERE r.PERIOD_ID = :period_id AND r.PERIOD_ID <> -2

Two practical notes. Multiple comments per reconciliation are normal; decide whether you want the latest, all of them concatenated, or one row per comment, and shape the subquery accordingly. And once the period is closed, comments cannot be deleted, which makes this table the audit narrative the report framework never quite hands over.

THE USE CASE, SIMPLIFIED

The problem: The audit commentary is scattered across the application at exactly the wrong moment.

What we build: Comments export nightly with the status, joined on the reconciliation.

What you get: The narrative for any period is one query, permanently.

Audit season and the commentary scattered?

We export status and commentary together, nightly, and the narrative for any period is one query away, forever.

Talk to us

Terms on this page

OBJECT_ID: The comment's parent. For reconciliation comments, the RECONCILIATION_ID.
COMMENT_TEXT: The words. What the report picker will not give you.
permanence: After Closed, comments cannot be deleted. By design.
shaping: Latest, concatenated, or one row per comment. Decide once.