Results for “plan_status”

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

Overview

The CSC_PLAN_SEARCH_V view is a reporting and integration object owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It belongs to the CSC (Customer Care) product family and exists to support search operations against customer service plans. Rather than requiring external consumers to join plan, party, and account data manually, the view presents a single denormalized result set that combines plan attributes with the party and customer account context to which each plan applies. This makes it suitable for building search screens, list-of-values queries, extracts, and downstream integrations where a plan must be located by customer, account, or plan attributes.

The view carries no stored data of its own; it is a runtime construct whose results are always derived from the underlying plan and party tables at query execution time. Any change to a plan, party, or account record is therefore immediately reflected in the view's output.

Underlying Base Objects

According to the documented ETRM 12.2.2 metadata, CSC_PLAN_SEARCH_V is defined over the following referenced base objects:

• CSC_CUST_PLANS_V — the primary plan view, supplying plan identifiers, names, status, dates, and grouping attributes.
• HZ_CUST_ACCOUNTS — the customer account entity, supplying account number, account name, and account identifier.
• HZ_PARTIES — the party entity, supplying party name, party number, and party identifier.
• AR_LOOKUPS — the Oracle Receivables lookup view, which underpins the translation of coded values into their display meanings.

The view body is a UNION of two nearly identical SELECT statements. Both branches join HZ_PARTIES, HZ_CUST_ACCOUNTS, and CSC_CUST_PLANS_V, and both restrict the result to parties whose status is 'A' (active). The distinction between the branches lies in the join direction: the first branch drives from the customer account and outer-joins the plan (using the (+) outer-join operator), while the second drives from the party and outer-joins both the plan and the account. This UNION pattern ensures that plans are returned whether or not a matching account exists, and that accounts are returned whether or not a matching plan exists, giving the search view broad coverage of the customer-plan landscape.

Key Columns

The columns exposed by the view can be grouped functionally:

• Party columns — PARTY_ID, PARTY_NAME, PARTY_NUMBER, identifying the party associated with the plan or account.
• Account columns — CUST_ACCOUNT_ID, ACCOUNT_NUMBER, ACCOUNT_NAME, identifying the customer account.
• Plan identity columns — PLAN_ID, PLAN_NAME, and CUSTOMIZED_PLAN.
• Plan status columns — PLAN_STATUS_CODE and its translated counterpart PLAN_STATUS.
• Plan date columns — START_DATE_ACTIVE and END_DATE_ACTIVE.
• Plan grouping column — PLAN_GROUP_CODE, together with the associated GROUP_NAME. This is the column most directly relevant to searches on plan grouping.
• Usage and applicability columns — USE_FOR_CUST_ACCOUNT, END_USER_TYPE, and END_USER_TYPE_MEANING.

Common Use Cases and Queries

The view is typically queried to locate plans by group, status, party, or account. A representative search retrieves active plans for a given plan group code:

• SELECT plan_id, plan_name, plan_group_code, group_name, plan_status FROM csc_plan_search_v WHERE plan_group_code = :p_group_code AND plan_status_code = 'ACTIVE';
• SELECT party_number, party_name, account_number, plan_name FROM csc_plan_search_v WHERE party_id = :p_party_id;
• SELECT plan_id, plan_name, start_date_active, end_date_active FROM csc_plan_search_v WHERE cust_account_id = :p_cust_account_id ORDER BY start_date_active DESC;

Because the view filters on active parties and performs outer joins in both branches, callers should expect rows where plan columns are null (accounts without plans) or where account columns are null (plans without accounts). Queries that require a plan should therefore include a predicate such as PLAN_ID IS NOT NULL. Typical consumers include Customer Care plan search pages, service request entry, and ad hoc reporting extracts that need to resolve a plan in the context of the owning customer.