Search Results psb_ws_account_line_notes_u1

Overview

PSB.PSB_WS_ACCOUNT_LINE_NOTES is a transactional table in the Oracle E-Business Suite PSB (Public Sector / Service) schema that stores free-form textual notes attached to account lines within the workspace framework. It resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, marking it as an OLTP-oriented data segment rather than a reporting or interface staging object. The table is registered under FND Design Data as PSB.PSB_WS_ACCOUNT_LINE_NOTES and carries a VALID status in both EBS 12.1.1 and 12.2.2, meaning its structure and dependencies are consistent across the two release families.

Functionally, the object serves as a comment repository: each row represents one note associated with an account line processed through the PSB workspace flows. Because notes are discrete, independently addressable records, the table behaves as a detail entity keyed by a single surrogate identifier. Applying a heuristic Data Vault classification, this object is best modeled as a satellite, since it holds descriptive, non-key context (the NOTE text) plus standard audit attributes around a single identifier, with no foreign key relationships documented to parent hub or link structures.

Key Information Stored

The documented physical schema comprises seven columns. The most significant are:

• NOTE_ID (NUMBER, mandatory) — the notes unique identifier. This is the surrogate primary key, materialized through the constraint PSB_WS_ACCOUNT_LINE_NOTE_PK and enforced by the unique index PSB_WS_ACCOUNT_LINE_NOTES_U1, the object the user searched for. The index resides in APPS_TS_TX_IDX, separating index I/O from table I/O.
• NOTE (VARCHAR2, 4000) — the textual free-form note. This is the sole business-content column and the reason the row exists; the 4000-byte limit reflects the standard SQL VARCHAR2 ceiling and constrains each note to a single non-CLOB value.
• LAST_UPDATE_DATE (DATE) — timestamp of the most recent modification, used for change detection and audit.
• LAST_UPDATED_BY (NUMBER) — the FND_USER identifier of the last modifying user.
• LAST_UPDATE_LOGIN (NUMBER) — the login session identifier associated with the last modification.
• CREATED_BY (NUMBER) — the FND_USER identifier of the creating user.
• CREATION_DATE (DATE) — timestamp of initial row creation.

The final five columns are the standard Who columns applied uniformly across Oracle EBS transactional tables. Notably, PSB_WS_ACCOUNT_LINE_NOTES_U1 is a unique index on NOTE_ID alone; there is no separate documented business-key unique index, so NOTE_ID functions as both surrogate and sole uniqueness guarantee.

Common Use Cases and Queries

Typical use cases center on retrieving, auditing, and reporting note activity for account lines. A direct retrieval by identifier uses the unique index:

• SELECT note_id, note FROM psb.psb_ws_account_line_notes WHERE note_id = :p_note_id;
• SELECT note_id, note, created_by, creation_date FROM psb.psb_ws_account_line_notes ORDER BY creation_date DESC;
• SELECT COUNT(*) FROM psb.psb_ws_account_line_notes WHERE last_update_date >= :p_since; — incremental audit of note modifications.

Because the table is standalone with no documented foreign keys, joins to account-line or workspace parent tables must be inferred from application logic rather than enforced relationships. Reporting scenarios include note-volume trending, user contribution audits via CREATED_BY and LAST_UPDATED_BY, and extracting NOTE content for downstream document generation or case-management feeds. The 4000-byte limit should be accounted for when concatenating or parsing note text in reports.

Related Objects

The ETRM dependency metadata records that PSB_WS_ACCOUNT_LINE_NOTES does not reference any database object, and it is referenced only by the APPS synonym PSB_WS_ACCOUNT_LINE_NOTES. The most significant related objects are therefore:

• APPS.PSB_WS_ACCOUNT_LINE_NOTES — the synonym through which application code and concurrent programs access the PSB-owned table.
• PSB_WS_ACCOUNT_LINE_NOTES_U1 — the unique index on NOTE_ID in APPS_TS_TX_IDX supporting primary-key lookups.
• PSB_WS_ACCOUNT_LINE_NOTE_PK — the primary-key constraint enforcing NOTE_ID uniqueness.
• FND_USER — logically related through LAST_UPDATED_BY and CREATED_BY for user-name resolution.
• FND_LOGINS — logically related through LAST_UPDATE_LOGIN for session tracing.
• Presumed account-line and workspace interface tables in the PSB schema, related by application-level (not database-enforced) account-line identifiers.

Because the vault classification is standalone, no hub or link dependencies are documented, and any parent-child association must be validated against the PSB workspace API layer rather than the data dictionary.