Search Results collection_plan




Overview
Explore in AI Chat

Overview

APPS.QABV_COLLECTION_PLANS is a read-only reporting view in Oracle E-Business Suite (documented under ETRM 12.2.2, with the same definition applicable in 12.1.x) that presents Oracle Quality collection plan definitions in a denormalized, user-friendly form. A collection plan in Oracle Quality (QA_PLANS) defines what data to collect, when to collect it, and which specifications or specification sets apply to a given item, organization, or event. Because the base QA_PLANS table stores lookup codes rather than human-readable meanings, this view joins to the lookup views to expose decoded PLAN_TYPE and SPECIFICATION_ASSIGNMENT_TYPE values, along with effective dating and audit columns. The view is owned by APPS and is defined WITH READ ONLY, meaning it cannot be used for inserts or updates and is intended strictly for query, reporting, and integration access. This makes it the recommended access point for external systems, custom reports, and extracts that need collection plan metadata without navigating the underlying code-based columns.

Underlying Base Objects

The view is defined over a single base table joined to two lookup views. The documented referenced objects are:

  • QA_PLANS (accessed via a SYNONYM), aliased as COLLECTION_PLAN — the primary source of collection plan records, supplying plan identity, name, description, effective dates, organization, and audit columns.
  • FND_COMMON_LOOKUPS (a VIEW), aliased as FN — resolves PLAN_TYPE_CODE into a meaningful PLAN_TYPE using lookup type COLLECTION_PLAN_TYPE, constrained to APPLICATION_ID = 250 (Oracle Quality).
  • MFG_LOOKUPS (a VIEW), aliased as MF — resolves SPEC_ASSIGNMENT_TYPE into a meaningful SPECIFICATION_ASSIGNMENT_TYPE using lookup type QA_PLAN_SPEC_TYPE.
  • FND_GLOBAL (a PACKAGE) — referenced as part of the APPS view construction (for example, for org/security context resolution at definition time).

Because the joins rely on lookup codes, any plan whose type or specification assignment code is absent from the relevant lookup view will not be returned, which is an important consideration when reconciling row counts against QA_PLANS.

Key Columns

  • PLAN_ID — Primary identifier of the collection plan, mapped from QA_PLANS.PLAN_ID.
  • COLLECTION_PLAN_NAME — The descriptive plan name (QA_PLANS.NAME).
  • DESCRIPTION — Free-text description of the plan.
  • START_EFFECTIVE_DATE / END_EFFECTIVE_DATE — The plan's effective date range (EFFECTIVE_FROM / EFFECTIVE_TO).
  • PLAN_TYPE — Decoded meaning of the collection plan type from FND_COMMON_LOOKUPS (lookup type COLLECTION_PLAN_TYPE).
  • SPECIFICATION_ASSIGNMENT_TYPE — Decoded meaning describing how specifications are assigned to the plan, sourced from MFG_LOOKUPS (lookup type QA_PLAN_SPEC_TYPE).
  • ORGANIZATION_ID — The organization to which the collection plan belongs.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY — Standard EBS audit columns supporting change tracking and data lineage.

Common Use Cases and Queries

Typical scenarios include listing all active collection plans for an organization, extracting plan metadata for integration, and filtering plans by type or specification assignment method. The following examples illustrate common access patterns.

List all collection plans with decoded attributes for a given organization:

SELECT plan_id,
       collection_plan_name,
       plan_type,
       specification_assignment_type,
       start_effective_date,
       end_effective_date
FROM   apps.qabv_collection_plans
WHERE  organization_id = :p_org_id
ORDER  BY collection_plan_name;

Identify plans currently in effect:

SELECT plan_id, collection_plan_name
FROM   apps.qabv_collection_plans
WHERE  TRUNC(SYSDATE) BETWEEN start_effective_date AND NVL(end_effective_date, TRUNC(SYSDATE));

Filter by plan type or specification assignment method for reporting breakdowns:

SELECT plan_type, COUNT(*)
FROM   apps.qabv_collection_plans
GROUP  BY plan_type;

Because the view is WITH READ ONLY, it serves reliably as a stable reporting contract: consumers avoid lookup-decoding logic and benefit from a consistent column set. For write operations or access to underlying code columns, developers must instead use the base QA_PLANS table.