Results for “edr_psig_details”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The EDR_PSIG_DETAILS table, owned by the EDR schema within the Oracle E-Business Suite E-Records (EDR) module, stores the detailed transactional records of electronic signature events captured against documents in the system. Its documented description — "Electronic signature document details" — identifies it as the lowest-level repository of signature evidence, capturing each signature action, the responding user, the response given, and the timestamp and timezone in which the response occurred. In Oracle EBS 12.1.1 and 12.2.2, EDR is the foundation of the Electronic Records and Electronic Signatures (ERES) framework used for 21 CFR Part 11 compliance, and EDR_PSIG_DETAILS is the object most directly relied upon when regulators or auditors request proof of who signed what, when, and how.

Based on the foreign key structure and the presence of an ORIG_SYSTEM / ORIG_SYSTEM_ID pair, the table exhibits satellite-leaning characteristics in Data Vault terms: it holds descriptive, time-stamped attributes (the signature response details) that attach to a parent signature entity, rather than acting as a pure hub of unique business keys or a link resolving relationships between hubs. This is a heuristic modeling suggestion only; the physical table functions as a standard transactional detail table in the EBS relational schema.

Key Information Stored

Each row represents one signature response event. The most significant columns include:

• SIGNATURE_ID — Foreign key to CZ_SIGNATURES; ties the detail row to the parent signature definition and serves as the primary business relationship key.
• DOCUMENT_ID and EVIDENCE_STORE_ID — Identify the document being signed and the evidence store holding the signature manifestation, establishing the audit chain.
• USER_NAME — Foreign key to FND_USER; identifies the application user who responded.
• USER_DISPLAY_NAME — The readable name captured at signature time, preserving the identity as presented to the signer.
• USER_RESPONSE — The signer's actual response (for example, accept, reject, or a typed meaning), the core evidentiary value.
• SIGNATURE_TIMESTAMP and SIGNATURE_TIMEZONE — The precise moment of signing together with timezone context, essential for legal defensibility.
• SIGNATURE_STATUS and ADHOC_STATUS — Current disposition of the signature and any ad hoc routing state.
• SIGNATURE_SEQUENCE — The ordering of the signer within a multi-signature routing, supporting sequential approval requirements.
• ORIGINAL_RECIPIENT — The originally designated recipient, permitting detection of delegation.
• SIGNATURE_OVERRIDING_COMMENTS — Free-text justification captured when a signer overrides a constraint.
• ORIG_SYSTEM and ORIG_SYSTEM_ID — Source system identifiers; ORIG_SYSTEM_ID is a foreign key to HZ_ORIG_SYSTEMS_B.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard EBS audit columns maintained by the WHO columns convention.

The surrogate identity of the row is not separately documented; SIGNATURE_ID combined with the sequence or timestamp functions effectively as the business-key candidate in query patterns.

Common Use Cases and Queries

Typical reporting and compliance scenarios include signature audit trails per document, per user, and sequential signer reconstruction. A representative query joining to the parent signature and the user master is:

SELECT d.SIGNATURE_SEQUENCE, d.USER_DISPLAY_NAME, d.USER_RESPONSE,
  d.SIGNATURE_TIMESTAMP, d.SIGNATURE_TIMEZONE, d.SIGNATURE_STATUS, u.USER_NAME
FROM EDR.EDR_PSIG_DETAILS d, FND_USER u
WHERE d.USER_NAME = u.USER_NAME(+)
AND TRUNC(d.SIGNATURE_TIMESTAMP) BETWEEN :from_date AND :to_date
AND d.SIGNATURE_STATUS = :status
ORDER BY d.DOCUMENT_ID, d.SIGNATURE_SEQUENCE

Common uses include generating 21 CFR Part 11 audit reports, investigating rejected or overridden signatures using SIGNATURE_OVERRIDING_COMMENTS and ADHOC_STATUS, reconciling signature evidence against ORIG_SYSTEM_ID source records, and feeding downstream e-record archives. Timezone-aware filtering is essential because SIGNATURE_TIMESTAMP alone is ambiguous across geographically distributed signers.

Related Objects

• CZ_SIGNATURES — Parent signature definition; join on EDR_PSIG_DETAILS.SIGNATURE_ID = CZ_SIGNATURES.SIGNATURE_ID.
• FND_USER — Signer identity master; join on EDR_PSIG_DETAILS.USER_NAME = FND_USER.USER_NAME.
• HZ_ORIG_SYSTEMS_B — Source system registry; join on EDR_PSIG_DETAILS.ORIG_SYSTEM_ID = HZ_ORIG_SYSTEMS_B.ORIG_SYSTEM_ID.
• EDR_PSIG_SUMMARY or equivalent signature summary objects — aggregate signature state per document.
• Evidence store tables referenced via EVIDENCE_STORE_ID — hold the signature manifestation itself.
• Oracle ERES/EDR APIs and concurrent programs that generate signature requests and consume responses for document routing.