Search Results common_spec_id




Overview
Explore in AI Chat

Overview

APPS.QA_SPEC_ORG_ASSIGNMENTS_V is a reporting and integration view in Oracle E-Business Suite (EBS) Release 12.1.1 and 12.2.2 that exposes the relationship between a parent specification and the specifications assigned to child organizations. In the Quality Management (QA) module, specifications are defined at an inventory organization level, and a common specification defined in a master organization may be propagated to subordinate organizations. This view surfaces that propagation logic by joining the organization-level specification view with the specification master records to distinguish between specifications that merely inherit a common definition and those that have their own locally defined child specification.

The view is principally used where a query must determine, for a given specification and organization, whether a corresponding child specification exists and to retrieve its identifier and name. This is relevant to specification assignment reporting, qualification and collection plan setup, and to any downstream interface that must resolve the effective specification for a receiving organization.

Underlying Base Objects

The ETRM metadata documents four referenced base objects, all accessed through APPS synonyms:

  • QA_SPEC_ORGS_V (VIEW) — aliased as QS1; supplies the parent specification, its name, the owning organization, the child organization identifier/code/name, and the item identifier.
  • QA_SPECS (SYNONYM) — aliased as QS2; supplies the child specification row, matched on COMMON_SPEC_ID and ORGANIZATION_ID.
  • MTL_PARAMETERS (SYNONYM) — aliased as MP; supplies the ORGANIZATION_CODE for the parent organization. Joined with an outer join (+).
  • HR_ALL_ORGANIZATION_UNITS_TL (SYNONYM) — aliased as HRORG; supplies the translated organization NAME, restricted to the session language via USERENV('LANG'). Joined with an outer join (+).

The joins are outer on the MTL_PARAMETERS, HR_ALL_ORGANIZATION_UNITS_TL and QA_SPECS sources, so a row is returned for every parent-to-child organization combination even when no child specification or descriptive organization data exists. The final predicate (QS2.SPEC_ID IS NULL OR QS2.SPEC_ID <> QS2.COMMON_SPEC_ID) excludes child specification rows that are themselves the common specification, preventing self-match.

Key Columns

  • ROW_ID — the ROWID of the matched child specification in QA_SPECS; null when no child specification exists.
  • SPEC_ID / SPEC_NAME — identifier and name of the parent specification from QA_SPEC_ORGS_V; SPEC_NAME is the column matched when users search on "spec_name".
  • CHILD_SPEC_ID — identifier of the locally defined child specification, or null if none exists.
  • CHILD_SPEC_NAME — the child specification name; when null, defaults to a concatenation of the first 26 characters of the parent name and the child organization code.
  • CHILD_SPEC_NAME_OLD — the raw QA_SPECS.SPEC_NAME value before the NVL substitution, useful for auditing.
  • ORGANIZATION_ID — owning (parent) organization identifier.
  • ORGANIZATION_CODE — inventory organization code from MTL_PARAMETERS.
  • NAME — translated organization name from HR_ALL_ORGANIZATION_UNITS_TL.
  • ASSIGN_FLAG — DECODE flag set to 1 when a child specification exists, and 2 when none exists.
  • ITEM_ID — item associated with the specification assignment, where applicable.
  • CHILD_ORGANIZATION_ID / CHILD_ORGANIZATION_CODE / CHILD_ORGANIZATION_NAME — the receiving organization to which the specification is assigned.

Common Use Cases and Queries

Typical usage includes determining assignment coverage of specifications across organizations, identifying organizations that still lack a local child specification, and resolving the effective specification name for display or interface output.

To locate all organization assignments for a specification by name:

  • SELECT spec_id, spec_name, child_spec_id, child_spec_name, organization_code, child_organization_code, assign_flag FROM apps.qa_spec_org_assignments_v WHERE spec_name LIKE :spec_name;

To find organizations with no child specification (unassigned):

  • SELECT spec_id, spec_name, child_organization_id, child_organization_code FROM apps.qa_spec_org_assignments_v WHERE spec_id = :spec_id AND assign_flag = 2;

Because the view depends on USERENV('LANG') for the translated organization name, results for the NAME column are session-language dependent. Queries should filter on SPEC_ID or ORGANIZATION_ID where deterministic results are required, and the view is best treated as read-only for reporting rather than as a target for data manipulation.