Results for “csp_not_tystat_nav”

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

Overview

The CSP_NOT_TYSTAT_NAV view is a reporting and navigation-support object within the Oracle E-Business Suite Spares Management (CSP) module. It is owned by the APPS schema and holds a VALID status across EBS 12.1.1 and 12.2.2. As its ETRM description states, the view is defined for the "Notification Types to Status flow" — it flattens the hierarchical relationship between a notification type (e.g., a field service or depot repair notification) and the possible notification statuses into a single, query-friendly relational shape.

The CSP module uses a lightweight navigation framework driven by the CSP_NAV_NODE_TYPES table and the CSP_PARTS_LOOPS_B loop definitions to present tree-structured choices in the Spares Management UI. CSP_NOT_TYSTAT_NAV exposes that same tree as rows, so that forms, concurrent programs, and custom integrations do not have to traverse the hierarchy programmatically. In practice it functions as a denormalized lookup: one row per (notification status × notification type × parts loop) combination, pre-composed with concatenated navigation keys and labels. This makes it suitable for both user-facing List of Values (LOV) construction and for direct SQL used in reporting, validation, or integration mappings.

Underlying Base Objects

The documented base objects referenced by this view are:

• CSP_NAV_NODE_TYPES (VIEW) — the navigation node type definitions, aliased NT. The view filters to ROOT_KEY = 'CSP_NOTIF' and NODE_TYPE = 'NOTIF_STATUS', isolating the notification-status branch of the navigation tree.
• FND_LOOKUPS (VIEW) — joined twice. Alias FL1 is restricted to LOOKUP_TYPE = 'CSP_NOTIFICATION_STATUS', and alias FL2 to LOOKUP_TYPE = 'CSP_NOTIFICATION_TYPES'. These supply the human-readable MEANING and LOOKUP_CODE values.
• CSP_PARTS_LOOPS_B (SYNONYM) — aliased PL, supplying PARTS_LOOP_ID so that each status/type pairing can be scoped to a parts loop.
• FND_GLOBAL (PACKAGE) — referenced in the documented metadata as a base object, typically used to resolve environment context (e.g., FND_GLOBAL.USER_ID, ORG_ID) at query time.

Because CSP_NAV_NODE_TYPES and FND_LOOKUPS are themselves views, the object graph is layered: CSP_NOT_TYSTAT_NAV → navigation node types → underlying CSP tables, and → FND lookups → FND_LOOKUPS_TL / FND_LOOKUPS core. This layering means the returned content automatically honors whatever is configured in the notification lookup types.

Key Columns

• LABEL — the MEANING from FL1 (the notification status), i.e., the display text for the status.
• NODE_TYPE — the navigation node type, filtered to 'NOTIF_STATUS'.
• NODE_KEY — a composite, quoted, comma-delimited key built as "STATUS_CODE", "TYPE_CODE", "PARTS_LOOP_ID" via LISTAGG-style string concatenation. This uniquely identifies the navigation node.
• ICON_NAME — the icon associated with the node type, used by the UI tree.
• LEAF_FLAG — literal 'N', indicating the node is not a leaf in the navigation tree.
• PARENT_NODE_TYPE — literal 'NOTIF_TYPES', the parent branch these status nodes hang under.
• PARENT_NODE_KEY — the parent composite key built as "TYPE_CODE", "PARTS_LOOP_ID", linking each status row to its notification-type parent.

Common Use Cases and Queries

Typical uses include populating LOVs that drive notification status selection, validating status/type combinations during data entry, building navigation trees in custom HTML/Forms-based UIs, and generating status-flow reports for service notifications. A representative query is:

SELECT label,
       node_type,
       node_key,
       icon_name,
       leaf_flag,
       parent_node_type,
       parent_node_key
  FROM apps.csp_not_tystat_nav
 ORDER BY label;

To list only the statuses available for a specific notification type and parts loop, filter on PARENT_NODE_KEY:

SELECT label, node_key
  FROM apps.csp_not_tystat_nav
 WHERE parent_node_key = '"REPAIR", "123"';

Because the composite keys embed lookup codes and the parts loop ID, callers must construct and match those quoted, comma-delimited strings exactly as the view emits them. Joining the view back to CSP_NOTIFICATION_STATUS or FND_LOOKUPS for additional descriptive attributes is also common, though the view already denormalizes the most frequently needed labels and keys. In all cases, queries should be run against the APPS synonym with the standard EBS responsibility context so that any FND_GLOBAL-based predicates resolve correctly.