Search Results edr_psig_documents




Overview
Explore in AI Chat

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:

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.