Search Results edr_psig_documents_n1

Overview

The EDR.EDR_PSIG_DOCUMENTS table is the electronic signature (e-signature) document store within the Oracle E-Business Suite Electronic Document Repository (EDR) module. Its role is to persist the electronic records that are captured when a user applies an electronic signature to an Oracle EBS transaction or event. Each row holds both the XML representation of the signature record and the user-visible rendering of the electronic record, together with contextual metadata such as the requester, the initiating event, and the status of the document. In EBS 12.1.1 and 12.2.2, this table underpins the EDR "PSIG" (Personal Signature) feature set, which is used where regulatory or internal controls require a verifiable, timestamped record of who signed what, and when.

The table resides in the EDR schema, is VALID, and is physically stored in the APPS_TS_TX_DATA tablespace with PCTFREE 10. It contains 18 documented columns. Based on the Data Vault heuristic derived from its foreign key structure, the object is classified as standalone; from a modelling perspective this suggests it behaves as an independent hub-style entity keyed by DOCUMENT_ID, rather than participating as a link or satellite in a star schema.

Key Information Stored

The surrogate primary key is DOCUMENT_ID, which uniquely identifies each electronic signature document and is enforced through the unique index EDR_PSIG_DOCUMENTS_P. The table does not carry a separate business-key unique index beyond this primary key; the two remaining unique indexes documented (SYS_IL0000240378C00002$$ and SYS_IL0000240378C00003$$) are LOB indexes for the CLOB columns, not alternate business keys. The most significant columns are:

• DOCUMENT_ID — Unique document identifier and the primary key.
• PSIG_XML (CLOB) — The XML document representing the signature record.
• PSIG_DOCUMENT (CLOB) — The electronic record as seen by the user.
• PSIG_DOCUMENTFORMAT — Format of the stored document.
• PSIG_TIMESTAMP — Timestamp when the document was requested; indexed via EDR_PSIG_DOCUMENTS_N2 for time-based retrieval.
• PSIG_TIMEZONE — Time zone in which the document was initiated.
• DOCUMENT_REQUESTER — The requester of the document.
• DOC_REQ_DISP_NAME — The requester's display name as held in the WF directory at creation time, preserving the value as it appeared historically.
• PSIG_STATUS — Status of the document, with documented values ERROR, PENDING, or COMPLETE.
• PSIG_SOURCE — Source of the document.
• EVENT_NAME and EVENT_KEY — The associated event name and the identifier of the specific event instance; these are indexed together via EDR_PSIG_DOCUMENTS_N1.
• PRINT_COUNT — Number of times the document has been requested for printing.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard WHO audit columns.

Common Use Cases and Queries

Typical scenarios include auditing who signed a given EBS event, retrieving the rendered electronic record for compliance or legal review, and monitoring the volume of documents in PENDING or ERROR state. The EVENT_NAME/EVENT_KEY pair is the most practical entry point, since it links a signature document back to the initiating business event instance.

Retrieve all documents for a known event instance:

SELECT DOCUMENT_ID, PSIG_STATUS, PSIG_TIMESTAMP, DOC_REQ_DISP_NAME, PRINT_COUNT
FROM EDR.EDR_PSIG_DOCUMENTS
WHERE EVENT_NAME = :p_event_name
AND EVENT_KEY = :p_event_key;

Monitor document pipeline health by status:

SELECT PSIG_STATUS, COUNT(*)
FROM EDR.EDR_PSIG_DOCUMENTS
GROUP BY PSIG_STATUS;

Because PSIG_XML and PSIG_DOCUMENT are CLOB columns, reports should project them selectively or convert them (for example with DBMS_LOB.SUBSTR or a CLOB-aware reporting tool) rather than selecting them in bulk. The PSIG_TIMESTAMP index supports time-bounded extracts over high-volume signature periods.

Related Objects

The documented relationship model classifies EDR_PSIG_DOCUMENTS as standalone, meaning no foreign key dependencies were mined from its structure. The following are the most significant related or dependent objects to consider when working with this table:

• EDR_PSIG_DOCUMENTS_P — The unique primary key index on DOCUMENT_ID; drives single-row lookups.
• EDR_PSIG_DOCUMENTS_N1 — Non-unique composite index on EVENT_NAME, EVENT_KEY; supports event-based joins to the application that raised the signature event.
• EDR_PSIG_DOCUMENTS_N2 — Non-unique index on PSIG_TIMESTAMP; supports temporal reporting.
• SYS_IL0000240378C00002$$ and SYS_IL0000240378C00003$$ — LOB indexes associated with the PSIG_XML and PSIG_DOCUMENT CLOB segments.
• Oracle Workflow directory objects — Referenced at insert time to populate DOC_REQ_DISP_NAME with the requester's display name.
• EDR module signature/registration objects — The broader EDR PSIG component set that consumes document records for signature verification and printing.

When integrating externally, join to the originating transactional table using EVENT_NAME and EVENT_KEY rather than expecting a physical foreign key from this standalone store.