Search Results sys_il0000241120c00008

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

Overview

APPLSYS.WF_DIG_SIGS is the Oracle E-Business Suite digital signatures table, registered under FND design data as FND.WF_DIG_SIGS. It stores signature records generated by the Oracle Workflow digital signature framework and is deployed in the APPS_TS_TX_DATA tablespace with a PCTFREE of 10. The object is documented as VALID in both Oracle EBS 12.1.1 and 12.2.2. Its functional purpose is to capture the cryptographic signature payload, the associated certificate, the signer identity, the signed object reference, and the lifecycle timestamps that record when a signature was created, signed, verified, and fully validated.

The table is the persistent store for e-signature activity rather than a configuration or transaction table. Rows are created when a signature request is raised against a business object, updated as signing and verification progress, and retained as an audit record of who signed what and when. A heuristic Data Vault classification mined from the foreign key structure places this object as a standalone table; modeling guidance would suggest treating it as a hub for the SIG_ID business key, with descriptive attributes such as SIG_POLICY, SIG_FLAVOR, STATUS, and the date columns forming a satellite. The CERT_ID and SECURITY_GROUP_ID references point outward to certificate and security group hubs.

Key Information Stored

The surrogate primary key is SIG_ID, enforced by the constraint WF_DIG_SIGS_PK. The documented unique indexes are the two LOB indexes SYS_IL0000241120C00007$$ and SYS_IL0000241120C00008$$, which support the two large-object columns rather than representing business keys. The most significant columns are:

• SIG_ID — numeric signature identifier and primary key.
• CERT_ID — foreign key to WF_DIG_CERTS, identifying the certificate used for the signature.
• SIG_POLICY and SIG_FLAVOR — the signature policy and flavor applied to the request.
• SIG_OBJ_TYPE and SIG_OBJ_ID — the object type and identifier of the business object being signed.
• PLAINTEXT — CLOB holding the text to be signed.
• SIGNATURE — CLOB holding the resulting signature data.
• REQUESTED_SIGNER_TYPE and REQUESTED_SIGNER_ID — the type and identifier of the requested signer.
• STATUS — numeric state of the signature record.
• CREATION_DATE, SIGNED_DATE, VERIFIED_DATE, LAST_VALIDATION_ATTEMPT, VALIDATED_COMPLETE_DATE — the lifecycle timestamps for creation, signing, verification, validation attempts, and completed validation.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, reserved for the hosting environment.

ERRBUF and ERRSTACK capture error information when a signing or validation step fails. Together these columns provide both the forensic payload and the audit trail of the signature.

Common Use Cases and Queries

Typical operational scenarios include auditing which documents were signed, identifying signatures still awaiting verification, and diagnosing signing failures through ERRBUF and ERRSTACK. The most frequent access paths follow the documented indexes. WF_DIG_SIGS_N4 supports retrieval by the signed object:

• SELECT SIG_ID, STATUS, SIGNED_DATE, VERIFIED_DATE FROM WF_DIG_SIGS WHERE SIG_OBJ_TYPE = :type AND SIG_OBJ_ID = :id;
• SELECT SIG_ID, CERT_ID, STATUS FROM WF_DIG_SIGS WHERE REQUESTED_SIGNER_ID = :signer AND REQUESTED_SIGNER_TYPE = :type;
• SELECT SIG_ID, ERRBUF, ERRSTACK FROM WF_DIG_SIGS WHERE STATUS = :pending AND CREATION_DATE > SYSDATE - 7;

Reporting use cases include signature volume by policy and flavor, average elapsed time between CREATION_DATE and SIGNED_DATE, and exception reporting for records where LAST_VALIDATION_ATTEMPT is populated but VALIDATED_COMPLETE_DATE is null. Because SIGNATURE and PLAINTEXT are CLOB columns, reporting queries should avoid selecting them unless the payload is required, since they inflate result sets and I/O.

Related Objects

Documented relationships connect this table to the following significant objects:

• APPLSYS.WF_DIG_CERTS — joined on CERT_ID; holds the certificate definition used by each signature.
• APPLSYS.FND_SECURITY_GROUPS — joined on SECURITY_GROUP_ID; provides the security group context for the signature record.
• Constraint WF_DIG_SIGS_PK — the primary key on SIG_ID that enforces uniqueness of the signature identifier.
• Indexes WF_DIG_SIGS_N1 through WF_DIG_SIGS_N9 — the supporting access paths on STATUS, SIG_ID, CERT_ID, SIG_OBJ_ID with SIG_OBJ_TYPE, REQUESTED_SIGNER_ID with REQUESTED_SIGNER_TYPE, and the date columns.
• Workflow digital signature APIs — the Oracle Workflow signing and verification routines that insert and update rows in this table as part of approval processing.