Search Results qafv_collection_plans




Overview
Explore in AI Chat

Overview

QAFV_COLLECTION_PLANS is an APPS-owned, VALID database view in the Oracle E-Business Suite Quality (QA) module. It exposes collection plan header information stored in the QA_PLANS table, presenting each plan together with decoded lookup meanings and organization context. The view is described in the ETRM metadata as "Retrofitted," indicating it was introduced or re-pointed to support Oracle EBS 12.1.1 and 12.2.2 reporting against the QA collection plan schema.

The view exists primarily for reporting and integration. Rather than requiring developers to join QA_PLANS to multiple lookup and organization tables, QAFV_COLLECTION_PLANS delivers a denormalized, read-only result set. It defines a COLLECTION_PLAN_NAME column (mapped from QA_PLANS.NAME), which is the field most commonly searched by users looking up a collection plan by name. Because the view is declared WITH READ ONLY, it cannot be used for DML and is intended strictly for query access.

Underlying Base Objects

Per the documented metadata, the view is defined over the following base objects:

The lookup and organization joins are outer joins for the organization tables ((+) syntax), ensuring a plan row is returned even when organization or lookup decoding is absent.

Key Columns

  • PLAN_ID — Primary identifier of the collection plan from QA_PLANS.
  • COLLECTION_PLAN_NAME — The plan name (QA_PLANS.NAME); the primary search term for locating a plan.
  • DESCRIPTION — Free-text description of the plan.
  • START_EFFECTIVE_DATE / END_EFFECTIVE_DATE — Effective-from and effective-to dates controlling plan validity.
  • PLAN_TYPE_CODE / PLAN_TYPE — Raw code and decoded meaning (COLLECTION_PLAN_TYPE lookup) describing the plan's type.
  • SPECIFICATION_ASSIGNMENT_TYPE — Decoded meaning of the QA_PLAN_SPEC_TYPE lookup for specification assignment.
  • ORGANIZATION_ID, ORGANIZATION_CODE, ORGANIZATION_NAME — Organization context resolved via MTL_PARAMETERS and HR_ALL_ORGANIZATION_UNITS.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY — Standard audit columns.

Common Use Cases and Queries

Typical scenarios include quality reporting on active collection plans, lookups by plan name, and integration extracts keyed on the organization. A common query retrieves plans for a given organization:

  • Find a plan by name: SELECT PLAN_ID, COLLECTION_PLAN_NAME, PLAN_TYPE FROM QAFV_COLLECTION_PLANS WHERE COLLECTION_PLAN_NAME LIKE :name;
  • List effective plans: SELECT COLLECTION_PLAN_NAME, START_EFFECTIVE_DATE, END_EFFECTIVE_DATE FROM QAFV_COLLECTION_PLANS WHERE TRUNC(SYSDATE) BETWEEN START_EFFECTIVE_DATE AND NVL(END_EFFECTIVE_DATE, SYSDATE + 1);
  • Organization-scoped report: SELECT COLLECTION_PLAN_NAME, ORGANIZATION_CODE, SPECIFICATION_ASSIGNMENT_TYPE FROM QAFV_COLLECTION_PLANS WHERE ORGANIZATION_ID = :org_id;

Because the view already decodes lookup values and resolves organization codes, it reduces join complexity for Quality reporting and downstream integration extracts.