Results for “csc_plan_headers_v”

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

Overview

CSC_PLAN_HEADERS_V is an APPS-owned view in the Oracle E-Business Suite Customer Care (CSC) module. It presents a reporting-oriented projection of plan headers used by customer care plans, combining the base header data stored in CSC_PLAN_HEADERS_B with the translatable name and description attributes held in CSC_PLAN_HEADERS_TL. The view is documented in ETRM for both EBS 12.1.1 and 12.2.2, and its status is VALID.

The view exposes the plan identifier, plan name, description, associated party, plan group meaning, customization flag, effective date range, and the profile check and criteria values that determine when a plan applies. Because it joins transactional header data with lookup meanings and translated text, it functions as a single-point source for reports, concurrent programs, and integration interfaces that need human-readable plan definitions rather than raw foreign keys. The searched column criteria_value_low is exposed directly from the base table and, together with RELATIONAL_OPERATOR and CRITERIA_VALUE_HIGH, defines the range-based match criteria for a plan.

Underlying Base Objects

The ETRM metadata documents the following referenced objects: CSC_PLAN_HEADERS_B, CSC_PLAN_HEADERS_TL, CSC_CUSTOMIZED_PLANS, CSC_PROF_CHECKS_VL, and CSC_LOOKUPS, with the first three referenced as synonyms and the latter two as views. The view's SQL joins these sources as follows:

• CSC_PLAN_HEADERS_B (alias PNHB) supplies the core plan attributes, including PLAN_ID, PLAN_GROUP_CODE, ORIGINAL_PLAN_ID, USE_FOR_CUST_ACCOUNT, END_USER_TYPE, CUSTOMIZED_PLAN, the active date range, PROFILE_CHECK_ID, RELATIONAL_OPERATOR, CRITERIA_VALUE_LOW, CRITERIA_VALUE_HIGH, audit columns, OBJECT_VERSION_NUMBER, and the 15 descriptive flexfield attribute columns plus ATTRIBUTE_CATEGORY.
• CSC_PLAN_HEADERS_TL (alias PNHTL) supplies the translated NAME and DESCRIPTION, joined on PLAN_ID and restricted to USERENV('LANG') so the caller sees only their session language.
• CSC_CUSTOMIZED_PLANS (alias CCPN) provides PARTY_ID through an outer join on PLAN_ID, so a plan without a customized counterpart still returns a row.
• CSC_LOOKUPS (alias CLGR) is joined with the lookup type CSC_PLAN_GROUP to translate PLAN_GROUP_CODE into GROUP_NAME.
• CSC_PROF_CHECKS_VL (alias CPC) supplies CHECK_NAME for the profile check identified by PROFILE_CHECK_ID.

Key Columns

• ROW_ID — the ROWID of the base header row, useful for row-level addressing.
• PLAN_ID — primary identifier of the plan, also the join key across the underlying objects.
• NAME, DESCRIPTION — translated plan text from the _TL table.
• PARTY_ID — the party associated with the plan via CSC_CUSTOMIZED_PLANS.
• PLAN_GROUP_CODE, GROUP_NAME — plan group code and its decoded lookup meaning.
• CUSTOMIZED_PLAN — indicates whether the plan is customized.
• START_DATE_ACTIVE, END_DATE_ACTIVE — validity window of the plan.
• PROFILE_CHECK_ID, PROFILE_CHECK_NAME — the profile check that the plan evaluates.
• RELATIONAL_OPERATOR — the operator applied to the criteria range, for example equality, less-than, or between.
• CRITERIA_VALUE_LOW, CRITERIA_VALUE_HIGH — the lower and upper bounds of the match criteria; only the low value is populated for single-value operators.
• USE_FOR_CUST_ACCOUNT, END_USER_TYPE, END_USER_TYPE_MEANING — plan applicability flags and the decoded end-user type.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — descriptive flexfield context and segments.
• CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER — standard audit and concurrency columns.

Common Use Cases and Queries

Typical usage includes reporting active plans and their criteria, diagnosing why a customer matched or failed to match a plan, and populating integration extracts. Because the view already resolves language and lookup meanings, it is preferred over querying the base tables directly.

Listing criteria-bearing plans for a profile check:

SELECT plan_id, name, profile_check_name, relational_operator,
       criteria_value_low, criteria_value_high
  FROM apps.csc_plan_headers_v
 WHERE profile_check_id = :p_check_id
   AND TRUNC(SYSDATE) BETWEEN NVL(start_date_active, TRUNC(SYSDATE))
                         AND NVL(end_date_active, TRUNC(SYSDATE));

Retrieving customized plans for a party with decoded group names:

SELECT h.plan_id, h.name, h.group_name, h.customized_plan
  FROM apps.csc_plan_headers_v h
 WHERE h.party_id = :p_party_id
   AND h.customized_plan = 'Y';

Filtering specifically on the low criteria bound, as implied by the user search, is supported by a predicate such as WHERE criteria_value_low = :p_value, optionally paired with a range test using the high value. Note the outer joins to CSC_CUSTOMIZED_PLANS and CSC_LOOKUPS require care: PARTY_ID and GROUP_NAME may be null, so NVL handling is advisable when those columns are consumed by reporting logic.