Results for “jtf_imp_psnew_vl”

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

Overview

JTF_IMP_PSNEW_VL is a CRM Foundation (JTF) view used during Oracle E-Business Suite patch and snapshot management. Its name encodes its purpose: "PS" refers to patch files, "NEW" indicates it exposes patch files that are new relative to the contents of a stored snapshot, and the "_VL" suffix denotes a view with a translated (language) component in the standard EBS naming convention. In practical terms, the view answers the question: "Which patch files exist in the patch file repository that have not yet been recorded within a given snapshot?"

The view is a comparison and reconciliation mechanism rather than a transactional data source. It is consumed by Oracle Applications Manager (OAM) and the AD (Application DBA) patch/snapshot infrastructure to determine deltas between the current file system state and a previously captured snapshot. Although the ETRM metadata states "Not implemented in this database" for the documented environment, the view definition is shipped as part of the CRM Foundation schema and can be present in environments where the snapshot and patch-management features are active.

The user search term snapshot_id is directly relevant, because SNAPSHOT_ID is one of the view's seven projected columns and the key correlation value that drives every row returned.

Underlying Base Objects

The documented view text references two source objects, both aliased in the query:

• JTF_IMP_PFILEINFO2 (alias PATCH_FILES) — supplies patch file metadata: BUG_NO, APP_SHORT_NAME, DIRECTORY, FILENAME, and VERSION.
• JTF_IMP_SFILEINFO_VL (alias SNAP_FILES) — supplies snapshot file metadata, filtered by SNAPSHOT_ID.
• AD_SNAPSHOTS — used in an inline view with SELECT DISTINCT SNAPSHOT_ID to enumerate available snapshots; this is an AD (Application DBA) product table.

The join logic is a correlated NOT IN subquery. The outer query cross-joins JTF_IMP_PFILEINFO2 against the distinct snapshot list, then excludes any patch file whose (APP_SHORT_NAME, DIRECTORY, FILENAME) tuple already appears in JTF_IMP_SFILEINFO_VL for that same snapshot. ETRM records these referenced base objects as "none documented" in the standard metadata extract, but the view text itself names them explicitly, and they should be treated as the authoritative dependency list.

Key Columns

• BUG_NO — the bug or patch number associated with the patch file, derived from JTF_IMP_PFILEINFO2.
• SNAPSHOT_ID — the snapshot identifier from AD_SNAPSHOTS; the central correlation key and the value most commonly used for filtering.
• APP_SHORT_NAME — the application short name (for example, JTF, FND, INV) to which the file belongs.
• DIRECTORY — the directory path of the patch file.
• FILENAME — the file name within the directory.
• NEW_VERSION — the patch file version, projected from PATCH_FILES.VERSION under the alias NEW_VERSION to distinguish it from any snapshot version.

Together, APP_SHORT_NAME, DIRECTORY, and FILENAME form the composite key used in both the outer projection and the NOT IN comparison.

Common Use Cases and Queries

The primary use case is identifying newly applied or available patch files not yet captured in a snapshot, typically ahead of snapshot refresh or comparison reporting.

List all new patch files for a specific snapshot:

SELECT BUG_NO, SNAPSHOT_ID, APP_SHORT_NAME, DIRECTORY, FILENAME, NEW_VERSION FROM JTF_IMP_PSNEW_VL WHERE SNAPSHOT_ID = :snapshot_id ORDER BY APP_SHORT_NAME, DIRECTORY, FILENAME;

Group by application to gauge impact per module:

SELECT APP_SHORT_NAME, COUNT(*) file_count FROM JTF_IMP_PSNEW_VL WHERE SNAPSHOT_ID = :snapshot_id GROUP BY APP_SHORT_NAME ORDER BY file_count DESC;

Because the view already restricts to patch files absent from the snapshot, no additional anti-join is required in consumer queries. Note that the NOT IN predicate can behave unexpectedly if any correlated value is null, so analysts should confirm the key columns are populated before relying on results for reconciliation.