Search Results edr_psig_documents_p

Overview

EDR_PSIG_DOCUMENTS is a table in the EDR (E-Records) product module of Oracle E-Business Suite, documented as the Electronic Signature Document store. It resides in the EDR schema and holds the persistent representation of electronically signed documents generated or captured by the Oracle E-Records / Electronic Signature (PSIG) framework, which underpins regulatory compliance features such as 21 CFR Part 11 signature capture and document archival. The object carries a VALID status in the documented 12.2.2 physical schema and exposes 18 columns. Its Data Vault classification is heuristic and mined from foreign-key structure; because no inbound or outbound foreign keys were identified, the object is treated as standalone. In Data Vault terms, this suggests modeling the table as a self-contained hub or as a record-keeping satellite without external link dependencies, rather than as a link table joining two business entities.

Key Information Stored

The table is anchored by the surrogate primary key DOCUMENT_ID, enforced through the EDR_PSIG_DOCUMENTS_P unique index. The remaining columns fall into three functional groups:

• Signature payload: PSIG_XML, PSIG_DOCUMENT, and PSIG_DOCUMENTFORMAT carry the signed document body and its serialized XML representation, together with the encoding or format indicator.
• Signature context: PSIG_TIMESTAMP and PSIG_TIMEZONE record when and in which time zone the signature was applied; PSIG_STATUS and PSIG_SOURCE describe the lifecycle state and the originating channel of the signature.
• Event linkage and attribution: DOCUMENT_REQUESTER, DOC_REQ_DISP_NAME, EVENT_NAME, and EVENT_KEY tie the document to the requesting user and to the business event that triggered the signature.
• Audit and lifecycle: CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN provide standard EBS "WHO" auditing, while PRINT_COUNT tracks reproduction of the signed artifact.

The documented unique indexes are the primary key EDR_PSIG_DOCUMENTS_P (DOCUMENT_ID) and two system-managed LOB indexes, SYS_IL0000240378C00002$$ and SYS_IL0000240378C00003$$, which support the large object columns PSIG_XML and PSIG_DOCUMENT. No business-key candidate columns beyond DOCUMENT_ID are exposed, so the surrogate key is the sole documented identifier.

Common Use Cases and Queries

Typical usage centres on compliance reporting and audit retrieval. A signature audit extract joins the document store to the requesting user and event to reconstruct who signed what and when:

• Retrieve signatures applied within a period: filter on PSIG_TIMESTAMP and PSIG_TIMEZONE.
• Reconstruct a signed artifact by selecting PSIG_DOCUMENT and PSIG_DOCUMENTFORMAT for a given DOCUMENT_ID.
• Audit reprints by aggregating PRINT_COUNT by DOCUMENT_REQUESTER.
• Trace the source event by filtering EVENT_NAME and EVENT_KEY against the originating business flow.

Sample pattern:

SELECT d.DOCUMENT_ID, d.EVENT_NAME, d.EVENT_KEY, d.DOCUMENT_REQUESTER, d.PSIG_STATUS, d.PSIG_TIMESTAMP, d.PSIG_TIMEZONE, d.PRINT_COUNT, d.CREATION_DATE FROM EDR.EDR_PSIG_DOCUMENTS d WHERE d.PSIG_TIMESTAMP >= :from_ts AND d.PSIG_TIMESTAMP < :to_ts ORDER BY d.PSIG_TIMESTAMP;

Because LOB columns are large, reporting queries should project PSIG_DOCUMENT and PSIG_XML only when the document body is actually required.

Related Objects

The table was documented as standalone, with no inbound or outbound foreign keys, so relationships to other E-Records and Electronic Signature objects are logical rather than enforced. The most significant associated objects are:

• EDR_PSIG_DOCUMENTS_P — the primary key index on DOCUMENT_ID that governs uniqueness and lookup.
• SYS_IL0000240378C00002$$ and SYS_IL0000240378C00003$$ — system LOB indexes backing the PSIG_XML and PSIG_DOCUMENT columns.
• Electronic Signature request and signature tables in the EDR/PSIG family, which are correlated through EVENT_NAME and EVENT_KEY.
• E-Records archive and retention objects that consume PSIG documents for long-term storage.
• Standard EBS audit and user tables referenced indirectly via CREATED_BY, LAST_UPDATED_BY, and DOCUMENT_REQUESTER.

Because no FK constraints are documented, join conditions against these objects should be validated at the business-key and event-key level rather than assumed from referential metadata.