Search Results cs_tp_lookups




Overview
Explore in AI Chat

Overview

CS_TP_LOOKUPS is a Service (CS) module reference table that stores the lookup values governing answer types used within Oracle E-Business Suite telephony and service request processing. In EBS 12.1.1 and 12.2.2 the table resides in the CS schema and carries a VALID status in the data dictionary. Its documented purpose is narrow and well defined: it is the lookup table for answer types, meaning that individual rows define the permissible responses or answer categories associated with service-related questions and scripts.

The object is registered in ETRM with 27 documented columns and a single unique index, CS_TP_LOOKUP_U1, defined on LOOKUP_ID. From a Data Vault modeling perspective, the heuristic classification derived from the mined foreign-key structure identifies CS_TP_LOOKUPS as a standalone object. In practical terms this suggests treating the table as an independent reference or lookup hub, with its descriptive attributes carried either alongside the hub or in a dependent satellite rather than through explicit link tables, since the only documented outbound reference is a security-group association.

Key Information Stored

The most significant columns in CS_TP_LOOKUPS fall into three groups: identity, business definition, and standard EBS bookkeeping.

  • LOOKUP_ID — the surrogate primary key and the column underpinning the unique index CS_TP_LOOKUP_U1. It uniquely identifies each lookup row.
  • LOOKUP_TYPE — the business-key candidate that classifies the lookup into a category. In practice this column drives the filtering that separates one answer-type set from another.
  • DEFAULT_VALUE — the default answer value applied when no explicit selection is made.
  • START_DATE_ACTIVE and END_DATE_ACTIVE — date-effective range columns that control when a lookup is valid. Rows outside the active window should be excluded from operational queries.
  • SECURITY_GROUP_ID — the foreign key to FND_SECURITY_GROUPS, providing row-level security scoping.
  • CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — the standard EBS audit trail for insert and update tracking.
  • ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the descriptive flexfield (DFF) columns used for customer-specific extensions.

The surrogate key LOOKUP_ID is thus distinct from the business-key candidate LOOKUP_ID documented via the unique index; both identify the row, while LOOKUP_TYPE and DEFAULT_VALUE carry the semantics consumed by application logic.

Common Use Cases and Queries

Typical usage involves resolving the answer types available for a service script or questionnaire, validating a submitted response, or reporting on configured answer categories.

  • Retrieve active lookups for a given type: 
    SELECT lookup_id, lookup_type, default_value
FROM   cs.cs_tp_lookups
WHERE  lookup_type = :p_type
AND    TRUNC(SYSDATE) BETWEEN start_date_active AND NVL(end_date_active, SYSDATE);
  • Find the default value for a specific answer type: 
    SELECT default_value
FROM   cs.cs_tp_lookups
WHERE  lookup_id = :p_id;
  • Security-scoped reporting joined to the security group: 
    SELECT l.lookup_id, l.lookup_type, s.security_group_name
FROM   cs.cs_tp_lookups l, fnd_security_groups s
WHERE  l.security_group_id = s.security_group_id;

Reporting is commonly performed to audit which answer types remain active, which defaults are configured, and whether DFF attributes carry locally defined values.

Related Objects

The documented relationships and the surrounding CS data model identify the following as the most significant related objects:

  • FND_SECURITY_GROUPS — referenced by CS_TP_LOOKUPS.SECURITY_GROUP_ID; the primary documented foreign-key relationship.
  • FND_LOOKUP_VALUES / FND_LOOKUPS — the standard EBS lookup framework, typically referenced by LOOKUP_TYPE to supply the base lookup codes.
  • CS_TP_LOOKUP_VALUES — commonly paired lookup-value detail table for answer-type entries.
  • CS_INCIDENTS / CS_SR_* service request tables — consumer tables that store responses referencing lookup IDs.
  • CS_TP_* telephony tables — sibling tables in the Service telephony (TP) family that share the same lookup conventions.