Results for “psp_auto_lookups”

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

Overview

PSP_AUTO_LOOKUPS is a table in the PSP (Labor Distribution) product of Oracle E-Business Suite, present in both 12.1.1 and 12.2.2. Its documented description states that it stores lookup codes and their descriptions used in the auto-population module of Labor Distribution. The table therefore acts as a configuration repository for the values and rules that drive automatic population of Labor Distribution parameters other than the main transaction data itself.

Each row defines a lookup keyed by a surrogate identifier, together with the parameter class, parameter name, datatype, optional dynamic SQL statement, bind variables, validation configuration, and an optional descriptive flexfield context. The table is scoped by business group and, where applicable, by set of books, which allows separate lookup definitions across operating units and ledger configurations.

From a heuristic Data Vault perspective, the mined foreign key structure classifies PSP_AUTO_LOOKUPS as satellite leaning. It is a descriptive, dependent structure attached to a parent context (the set of books) rather than a pure hub of independent business keys. This classification is a modeling suggestion derived from the FK topology and should be validated against the intended analytical grain before being adopted.

Key Information Stored

• LOOKUP_ID — the surrogate primary key, also documented as the business key candidate through the unique index PSP_AUTO_LOOKUPS_U1 and the PK constraints LOOKUP_ID_PK and PSP_AUTO_LOOKUPS_PK1.
• PARAMETER_CLASS — identifies the class or grouping to which the auto-population parameter belongs.
• PARAMETER — the specific parameter name within that class.
• DATATYPE — the data type expected for the parameter value.
• DYN_SQL_STMT — an optional dynamic SQL statement used to derive or validate the value.
• BIND_VAR — bind variable definition used with the dynamic SQL statement.
• VALIDATION_TYPE — the validation mechanism applied to the parameter.
• PARAM_VALUE_SET — the value set used for list-of-values or validation purposes.
• SET_OF_BOOKS_ID — foreign key to GL_SETS_OF_BOOKS_11I, tying the lookup to a ledger context.
• BUSINESS_GROUP_ID — the business group owning the lookup definition.
• DESCRIPTIVE_FLEX_CONTEXT_CODE — the DFF context code associated with the lookup.
• Audit columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, and CREATION_DATE provide standard EBS WHO column auditing.

Common Use Cases and Queries

Implementation teams query this table to document and troubleshoot auto-population behavior, particularly to confirm which parameters exist for a given class and how they are validated. A typical query joins the lookup to its parameter assignments:

• SELECT l.lookup_id, l.parameter_class, l.parameter, l.datatype, l.validation_type FROM psp_auto_lookups l WHERE l.business_group_id = :bg_id;
• SELECT l.lookup_id, l.parameter, p.* FROM psp_auto_lookups l JOIN psp_auto_params p ON p.lookup_id = l.lookup_id WHERE l.set_of_books_id = :sob_id;

Reporting scenarios include an inventory of dynamic SQL statements configured for auto-population, auditing of value set usage by parameter, and impact analysis before changing a parameter class or value set. Because dynamic SQL is stored in DYN_SQL_STMT, DBAs also review this column when investigating performance or SQL injection exposure.

Related Objects

• PSP_AUTO_PARAMS — references PSP_AUTO_LOOKUPS via PSP_AUTO_PARAMS.LOOKUP_ID, the principal dependent table.
• GL_SETS_OF_BOOKS_11I / GL_SETS_OF_BOOKS — referenced by PSP_AUTO_LOOKUPS.SET_OF_BOOKS_ID, providing the ledger context.
• PSP_AUTO_LOOKUPS_U1 — unique index on LOOKUP_ID, the business key candidate.
• LOOKUP_ID_PK / PSP_AUTO_LOOKUPS_PK1 — primary key constraints on LOOKUP_ID.
• FND_FLEX_VALUES / FND_FLEX_VALUE_SETS — logically related through PARAM_VALUE_SET for value set resolution.
• FND_DESCR_FLEX_CONTEXTS — related through DESCRIPTIVE_FLEX_CONTEXT_CODE for DFF context metadata.