Search Results csc_cust_plans_audit




Overview
Explore in AI Chat

Overview

The CSC_CUST_PLANS_AUDIT table is a core audit and history tracking object within the Oracle E-Business Suite Customer Care (CSC) module. Its primary function is to maintain a complete historical record of all changes to customer and plan associations. In the context of service and support operations, a "plan" often represents a service contract, support agreement, or subscription. This table ensures a non-repudiable audit trail of when a specific customer (or customer account) was associated with or disassociated from a particular plan, which is critical for compliance, dispute resolution, and analyzing customer service history across versions 12.1.1 and 12.2.2.

Key Information Stored

The table's structure is designed to capture the essential elements of an audit record. Its primary key, PLAN_AUDIT_ID, uniquely identifies each historical event. The most critical foreign key columns define the entities involved in the association: PLAN_ID references the specific plan from CSC_PLAN_HEADERS_B, PARTY_ID identifies the customer party from the Trading Community Architecture (HZ_PARTIES), and CUST_ACCOUNT_ID points to the specific customer account (HZ_CUST_ACCOUNTS). While the provided metadata does not list all columns, a table of this nature typically includes audit-specific fields such as CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and columns indicating the action performed (e.g., INSERT, UPDATE, DELETE) or the effective start and end dates of the association.

Common Use Cases and Queries

This table is central to historical reporting and forensic analysis in Customer Care. Common use cases include generating a customer's complete plan history for audit purposes, determining which plan a customer was subscribed to on a specific historical date for billing or support entitlement validation, and analyzing plan adoption or churn trends over time. A typical query would join this audit table to the plan and customer tables to produce a readable history report.

  • Sample Query Pattern: SELECT cpa.*, ph.PLAN_NAME, p.PARTY_NAME, ca.ACCOUNT_NUMBER FROM CSC_CUST_PLANS_AUDIT cpa, CSC_PLAN_HEADERS_B ph, HZ_PARTIES p, HZ_CUST_ACCOUNTS ca WHERE cpa.PLAN_ID = ph.PLAN_ID AND cpa.PARTY_ID = p.PARTY_ID AND cpa.CUST_ACCOUNT_ID = ca.CUST_ACCOUNT_ID AND cpa.PARTY_ID = :p_customer_id ORDER BY cpa.CREATION_DATE DESC;

Related Objects

The CSC_CUST_PLANS_AUDIT table sits at the intersection of Customer Care data and the core Trading Community Architecture. Its documented foreign key relationships are fundamental to its purpose:

  • CSC_PLAN_HEADERS_B: Joined via CSC_CUST_PLANS_AUDIT.PLAN_ID. This provides descriptive information about the service plan or contract involved in the audit record.
  • HZ_PARTIES: Joined via CSC_CUST_PLANS_AUDIT.PARTY_ID. This links the audit entry to the master customer (party) entity in the system.
  • HZ_CUST_ACCOUNTS: Joined via CSC_CUST_PLANS_AUDIT.CUST_ACCOUNT_ID. This links the audit entry to the specific customer account related to the plan association.

These relationships ensure data integrity and enable comprehensive joins for reporting. The table is likely referenced by standard Customer Care reports and may be populated by triggers or APIs managing the main customer-plan association table.