Search Results update_for_customized_plans

Overview

CSC_CUST_PLANS_PVT is a private PL/SQL package in the Oracle E-Business Suite Customer Care (CSC) application. It is defined with AUTHID CURRENT_USER and is responsible for performing inserts, updates, and deletes against the CSC_CUST_PLANS table, which stores customer-level service plan assignments. The package header dates to the original 11i code base (header revision 115.14, dated 2002/12/04) and remains in use through EBS 12.1.1 and 12.2.2.

Because the package is classified as PVT (private), it is not intended to be called directly by external or customer-developed code. Instead, it functions as the internal implementation layer behind the public APIs and forms that manage customer plans. Its primary business purpose is to enforce data integrity for customer plan records: it performs item-level validations when the validation level is set to 100 (FULL), and it writes an audit row to CSC_CUST_PLANS_AUDIT for every insert, update, or delete, tracking changes through a generated audit identifier (G_PLAN_AUDIT_ID). Historical comments also note that org_id validations were removed following the TCA decision to drop the ORG_ID column from HZ_CUST_ACCOUNTS.

Key Procedures and Functions

The package exposes sixteen documented procedures and functions. Grouped by responsibility they are:

• DML operations: CREATE_CUST_PLANS, UPDATE_CUST_PLANS, DELETE_CUST_PLANS, and UPDATE_FOR_CUSTOMIZED_PLANS. These perform the core insert, update, and delete logic against CSC_CUST_PLANS, including the customized-plan update path.
• State management: ENABLE_PLAN and DISABLE_PLAN, which toggle the active status of a customer plan record, typically by adjusting the plan status and active date range.
• Identifier retrieval: GET_CUST_PLAN_ID, which returns the internal customer plan identifier used to link related records.
• Validation routines: VALIDATE_CUST_PLAN_ID, VALIDATE_PLAN_ID, VALIDATE_PARTY_ID, VALIDATE_CUST_ACC_ORG_ID, VALIDATE_MANUAL_FLAG, VALIDATE_PLAN_STATUS_CODE, and VALIDATE_CSC_CUST_PLANS. These are the low-level referential and domain checks invoked before or during DML, ensuring that foreign keys, flags, and status codes conform to expected values.
• VALIDATE_PLAN_STATUS_CODE is the routine relevant to the searched term "validate_plan_status_code." It validates the PLAN_STATUS_CODE element of the CSC_CUST_PLANS record type, which is declared as VARCHAR2(30) in the record structure. This check ensures only permitted status values are persisted when a plan is created, updated, enabled, or disabled.

The package also declares the CSC_CUST_PLANS_Rec_Type record, which mirrors the CSC_CUST_PLANS columns (including CUST_PLAN_ID, PLAN_ID, PARTY_ID, CUST_ACCOUNT_ID, START_DATE_ACTIVE, END_DATE_ACTIVE, MANUAL_FLAG, PLAN_STATUS_CODE, REQUEST_ID, and PROGRAM_APPLICATION) and serves as the parameter carrier for the DML procedures.

Tables Accessed

Three tables are referenced through APPS synonyms:

• CSC_CUST_PLANS — the primary transactional table this package inserts, updates, and deletes; it holds customer plan assignments and their status, active dates, and manual flag.
• CSC_PLAN_HEADERS_B — the base table holding plan header definitions referenced by PLAN_ID; it is queried to validate that a supplied plan identifier exists and is valid.
• PLITBLM — the standard EBS PL/SQL table-of-rows structure used for bulk fetch processing. The package declares G_DEFAULT_NUM_REC_FETCH (30), indicating that DML and validation loops fetch records in batches of 30 to control memory use.
• CSC_CUST_PLANS_AUDIT — referenced in the header comments as the target of the generated audit identifier, capturing an audit trail for each DML action.

Usage Notes

As a private package, CSC_CUST_PLANS_PVT should not be invoked directly from custom code. It is called internally by other application modules — the metadata notes it is referenced by three other packages — and by the Customer Care forms and concurrent programs that maintain customer plan data. Typical invocation paths include the customer plan maintenance form, where user actions trigger create, update, enable, disable, and delete operations, and batch or concurrent processes that assign plans in bulk.

Developers who require plan maintenance logic should call the corresponding public API rather than this private package. When troubleshooting issues involving plan status codes, the VALIDATE_PLAN_STATUS_CODE routine is the appropriate starting point, and its behavior is governed by the allowable values defined for the PLAN_STATUS_CODE column. Because the DML procedures operate under AUTHID CURRENT_USER and rely on APPS synonyms, execute privileges must be granted through the owning schema, and callers must ensure the FND_API globals (G_MISS_NUM, G_MISS_CHAR) are honoured for the WHENEVER OSERROR EXIT FAILURE ROLLBACK convention introduced in the 2002 revision.