Results for “plan_group_code”

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

Overview

CSC_CUST_PLANS_V is a denormalized reporting and integration view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the CSC – Customer Care (TeleService / Customer Care) product. According to ETRM documentation, the view is based on the CSC_CUST_PLANS table and is specifically used in the lock row event of server-side APIs. In Oracle EBS, the lock row event is a standard Oracle Forms and OAF mechanism in which a query against a view is used to acquire a row-level lock on the underlying base table prior to an update. The view therefore serves two purposes: it presents a flattened, human-readable projection of customer plan assignments for reporting, and it provides the row identifier and key columns needed by the Customer Care application programmatic interfaces to serialize concurrent modifications.

The view is valid in both EBS 12.1.1 and 12.2.2 and follows the EBS 12.2 online patching editioning model, where the APPS synonym layer abstracts the underlying editioned objects.

Underlying Base Objects

The documented definition of CSC_CUST_PLANS_V joins the following objects:

• CSC_CUST_PLANS (shown as a synonym in 12.2.2) – the transaction table holding customer plan assignments; alias CPN supplies the ROWID as ROW_ID along with audit columns, attributes, and the object version number.
• CSC_PLAN_HEADERS_VL – the multi-language (TL) view of plan headers; supplies PLAN_NAME, PLAN_GROUP_CODE, CUSTOMIZED_PLAN, USE_FOR_CUST_ACCOUNT, and END_USER_TYPE.
• CSC_LOOKUPS – referenced three times (aliases CL1, CL2, CL3) to translate lookup codes into meanings for group name, plan status, and end user type. Oracle-style outer joins (+) are applied to these lookup joins so that missing lookup values do not suppress plan rows.
• HZ_PARTIES – the TCA party registry; provides PARTY_NUMBER, PARTY_NAME, and PARTY_TYPE.
• HZ_CUST_ACCOUNTS – the TCA customer account table; provides ACCOUNT_NUMBER and ACCOUNT_NAME.
• FND_USER – resolves CREATED_BY / LAST_UPDATED_BY into USER_NAME.
• AR_LOOKUPS – documented as a referenced object via the CSC_LOOKUPS view lineage, since CSC_LOOKUPS is itself commonly based on the Receivables lookup infrastructure.

Key Columns

• ROW_ID – the ROWID of the CSC_CUST_PLANS row; this is the column that enables the lock row event to select atomic row identity and apply FOR UPDATE semantics on the base table.
• PLAN_ID, CUST_PLAN_ID, PARTY_ID, CUST_ACCOUNT_ID – primary and foreign key identifiers linking the assignment to its plan header, customer party, and customer account.
• PLAN_NAME, PLAN_GROUP_CODE, GROUP_NAME – descriptive attributes of the subscribed plan and its lookup-derived group.
• PARTY_NUMBER, PARTY_NAME, PARTY_TYPE, ACCOUNT_NUMBER, ACCOUNT_NAME – TCA-derived customer identity for reporting.
• START_DATE_ACTIVE, END_DATE_ACTIVE – effective dating of the customer plan assignment.
• PLAN_STATUS_CODE, PLAN_STATUS_MEANING – status of the assignment and its decoded meaning.
• CUSTOMIZED_PLAN, USE_FOR_CUST_ACCOUNT, END_USER_TYPE, END_USER_TYPE_MEANING – plan configuration and end-user classification.
• MANUAL_FLAG – indicates whether the assignment was created manually rather than by an automated process.
• ATTRIBUTE_CATEGORY, ATTRIBUTE1–ATTRIBUTE15 – DFF-enabled descriptive flexfield columns from the base table.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID – concurrent program tracing columns inherited from the base table.
• CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY, USER_NAME, OBJECT_VERSION_NUMBER – standard audit and optimistic locking columns; OBJECT_VERSION_NUMBER supports OAF optimistic locking in the Customer Care UI and APIs.

Common Use Cases and Queries

Typical uses include customer service representative lookups of a party’s active plans, plan coverage reports, and integration extracts that require the TCA party and account descriptors in a single query. Because the view resolves lookups to meanings and joins directly to HZ_PARTIES and HZ_CUST_ACCOUNTS, it avoids repetitive join logic in custom reports.

Retrieve all plans for a given party:

• SELECT plan_id, cust_plan_id, plan_name, plan_status_meaning, start_date_active, end_date_active FROM csc_cust_plans_v WHERE party_id = :p_party_id ORDER BY start_date_active DESC;

Find active assignments by account number:

• SELECT account_number, party_name, plan_name, plan_status_meaning FROM csc_cust_plans_v WHERE account_number = :p_account AND SYSDATE BETWEEN start_date_active AND NVL(end_date_active, SYSDATE+1);

Reproduce the lock row pattern used by server-side APIs (selecting ROW_ID so the base row can be locked):

• SELECT row_id FROM csc_cust_plans_v WHERE cust_plan_id = :p_cust_plan_id FOR UPDATE OF cust_plan_id;

Because ROW_ID is a ROWID from CSC_CUST_PLANS and not a surrogate key, custom code should treat it as transient identifier data and should not persist or cache it across sessions. Queries should also account for the outer-joined lookup joins, which may return NULL meanings when a lookup code is not yet defined in CSC_LOOKUPS.