Search Results end_effective_date




Overview
Explore in AI Chat

Overview

QABV_COLLECTION_PLANS is a read-only Oracle EBS view owned by the APPS schema and defined in the Quality (QA) product. It presents collection plan header information from the QA_PLANS table in a denormalized, reporting-friendly form by resolving internal lookup codes into their user-facing meanings. The view is described in the ETRM metadata as "Retrofitted," indicating it was introduced or reconciled as part of multi-release support across Oracle EBS 12.1.1 and 12.2.2. Because it declares WITH READ ONLY, it cannot be used for DML; it is intended strictly for query, reporting, and integration consumption.

The view is particularly relevant to users searching for end_effective_date. That search term maps directly to the view's END_EFFECTIVE_DATE column, which exposes QA_PLANS.EFFECTIVE_TO under a more descriptive alias. This lets report authors and integrators reference effective-dating information consistently without needing to know the underlying column naming in QA_PLANS.

Underlying Base Objects

The view is defined over four documented base objects:

The joins are lookup-based rather than referential-key based, so a collection plan whose lookup values are missing or inactive may be filtered out of the result set. These are inner joins, which affects completeness of returned rows.

Key Columns

Note that PLAN_TYPE_CODE appears in the column listing but is not projected in the view text; only the decoded PLAN_TYPE meaning is exposed.

Common Use Cases and Queries

Typical uses include listing effective collection plans for an organization and identifying plans nearing or past expiration.

Active plans as of the current date:

  • SELECT PLAN_ID, COLLECTION_PLAN_NAME, START_EFFECTIVE_DATE, END_EFFECTIVE_DATE, PLAN_TYPE FROM QABV_COLLECTION_PLANS WHERE SYSDATE BETWEEN START_EFFECTIVE_DATE AND NVL(END_EFFECTIVE_DATE, SYSDATE);

Plans by organization and type:

  • SELECT PLAN_ID, COLLECTION_PLAN_NAME, ORGANIZATION_ID, SPECIFICATION_ASSIGNMENT_TYPE FROM QABV_COLLECTION_PLANS WHERE ORGANIZATION_ID = :org_id AND PLAN_TYPE = :plan_type;

Expiring within 90 days:

  • SELECT COLLECTION_PLAN_NAME, END_EFFECTIVE_DATE FROM QABV_COLLECTION_PLANS WHERE END_EFFECTIVE_DATE BETWEEN SYSDATE AND SYSDATE+90;

Because END_EFFECTIVE_DATE may be null for open-ended plans, apply NVL handling in date-range logic. All queries should account for organization security and lookup completeness.