Search Results ess_snapshot_dt_time

Overview

IGS_ST_GVT_SPSHT_CTL is an Oracle E-Business Suite view owned by the APPS schema, delivered as part of the IGS (Student System) product family. In Oracle EBS 12.1.1 and 12.2.2 the IGS module supports the management of student records, admissions, and related government reporting requirements. This particular view functions as a security and reporting layer over a government snapshot control table, exposing submission-level control data while enforcing organizational (multi-org) access rules.

The view presents one row per government snapshot submission, keyed by submission year and submission number. It is primarily used in reporting and integration contexts where downstream processes need to identify when a snapshot was taken and, critically, when the submission was completed. Because the view follows the standard EBS multi-org security pattern, rows are filtered automatically based on the operating unit context derived from the session's CLIENT_INFO setting, ensuring that users only see data belonging to their authorized organization. The view is marked VALID in the ETRM metadata, confirming that all referenced objects resolve correctly at the documented release levels.

Underlying Base Objects

The view is defined over a single documented base object: IGS_ST_GVT_SPSHT_CTL_ALL. The _ALL suffix is significant in EBS conventions, indicating that the underlying table stores records across all operating units rather than being partitioned per organization in the database. The view is the mechanism through which the multi-org restriction is applied at query time, rather than through a physical _ALL versus non-_ALL table split.

The WHERE clause compares the row's ORG_ID against the organization identifier parsed from USERENV('CLIENT_INFO'). When CLIENT_INFO is blank, the expression resolves to NULL and is defaulted to -99 through a nested NVL, meaning that sessions without an established org context can match only rows whose ORG_ID is likewise -99 or NULL. No additional joins, views, or synonyms are documented in the ETRM metadata; the view is a direct projection of the base table with a ROWID pseudo-column exposed as ROW_ID.

Key Columns

The column set is narrow and control-oriented, consistent with the purpose of tracking snapshot submission state:

• ROW_ID — The base table ROWID, exposed for row-level addressing and update-through-view scenarios.
• SUBMISSION_YR / SUBMISSION_NUMBER — The composite business key identifying the government snapshot submission cycle and sequence.
• ESS_SNAPSHOT_DT_TIME — The date and time at which the Enterprise Scheduler Service snapshot was captured.
• COMPLETION_DT — The date on which the submission was completed. This is the column most relevant to the user's search term, and it is the primary indicator of submission finalization; a NULL COMPLETION_DT generally signifies an in-progress or failed submission.
• ORG_ID — The operating unit owning the submission record, used by the multi-org filter.
• Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN follow standard EBS audit conventions.
• Concurrent program columns — REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE link the row to the concurrent request that created or last modified it.

Common Use Cases and Queries

Typical usage revolves around monitoring submission status, auditing snapshot completion, and feeding downstream government reporting extracts. Because COMPLETION_DT is the operational signal for a finished submission, many queries filter on its null state.

• Listing completed submissions for a given year, ordered by completion time.
• Identifying submissions that have not yet completed, for retry or escalation.
• Correlating submissions with their originating concurrent request for diagnostic purposes.

A representative query is:

SELECT submission_yr, submission_number, ess_snapshot_dt_time, completion_dt
FROM apps.igs_st_gvt_spsht_ctl
WHERE completion_dt IS NOT NULL
ORDER BY completion_dt DESC;

A second common pattern surfaces outstanding work for the current operating unit:

SELECT submission_yr, submission_number, ess_snapshot_dt_time
FROM apps.igs_st_gvt_spsht_ctl
WHERE completion_dt IS NULL;

Both queries execute against the view rather than the base table so that the multi-org security predicate is enforced automatically, which is the principal reason the view exists alongside IGS_ST_GVT_SPSHT_CTL_ALL.