Search Results option_sql_text




Overview
Explore in AI Chat

Overview

APPS.PER_PROPOSAL_QUESTION_TYPES_V is a reporting and integration view in the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 releases, owned by the APPS schema. It exposes the configuration metadata that drives the dynamic question architecture of Oracle iRecruitment and Oracle Advanced Benefits (OAB) self-service flows, where "proposal questions" govern how questionnaire and proposal pages are rendered to end users. Rather than storing transactional responses, the view surfaces the definitional attributes of each question type — including presentation settings (HTML_TYPE, WIDTH_SIZE, HEIGHT_SIZE, ALIGN, FULL_TEXT), instructional text (QUESTION_HEADER, HELP_TEXT, NOTE_TEXT), value handling (DEFAULT_VALUE_TYPE, DEFAULT_VALUE, LOOKUP_TYPE), and lookup-driven prompt behavior (OPTION_SQL_TEXT, PROVIDE_FIND, FIND_FILTER_COLUMN, FIND_SELECT_SQL). Because the view is a thin projection over a single synonym, it provides a stable read-only interface for developers, report writers, and integrators who need to introspect the question catalog without touching the base table directly. It is commonly consumed by concurrent programs, Oracle Reports, BI Publisher data models, and OAF-based extension code that programmatically reconstructs question rendering logic.

Underlying Base Objects

The ETRM 12.2.2 metadata documents a single referenced base object: the synonym PER_PROPOSAL_QUESTION_TYPES. This synonym resolves at runtime to the underlying table PER_PROPOSAL_QUESTION_TYPES in the APPLSYS or PER schema, depending on patch level and environment. The view is defined as a direct SELECT of all defined columns from that synonym, with no joins, unions, or WHERE clauses, and therefore inherits the table's row set exactly — one row per proposal question type. Because there is no filtering or aggregation in the view definition, grants and synonym security on the base table flow through directly. Any DML against the view is not supported by convention; it is intended strictly as a query surface. Performance characteristics equal those of a direct table access, and no materialization or query-rewrite benefit applies.

Key Columns

  • PROPOSAL_QUESTION_NAME — Primary identifier of the question type; joins to proposal/question instance tables elsewhere in the data model.
  • FULL_TEXT — The complete display text rendered for the question.
  • ALIGN — Horizontal alignment setting for the rendered prompt.
  • QUESTION_HEADER — Header or label shown above the response field.
  • HELP_TEXT and NOTE_TEXT — Supplementary guidance and note content attached to the question.
  • HTML_TYPE — The rendering control type (for example text field, list, or radio group).
  • WIDTH_SIZE, HEIGHT_SIZE, MAXLENGTH — Layout and input-length constraints.
  • DEFAULT_VALUE_TYPE and DEFAULT_VALUE — Mechanism and literal used to pre-populate a response.
  • LOOKUP_TYPE — Flexfield lookup type supplying valid values for list-style questions.
  • OPTION_SQL_TEXT — SQL used to generate dynamic option lists.
  • PROVIDE_FIND — Flag indicating whether a Find (search) facility is offered on the prompt.
  • TYPE — Classification of the question type.
  • FIND_FILTER_COLUMN — Column against which the Find filter is applied.
  • FIND_SELECT_SQL — SQL statement executed to populate the Find/LOV result set.

Common Use Cases and Queries

The view is most frequently queried to audit question configuration, to troubleshoot rendering anomalies, or to reproduce the prompt logic in custom extensions. A typical diagnostic query retrieving the Find-related configuration — the pattern implied by the search term "find_select_sql" — is:

  • SELECT proposal_question_name, html_type, provide_find, find_filter_column, find_select_sql FROM apps.per_proposal_question_types_v WHERE provide_find = 'Y';
  • SELECT proposal_question_name, lookup_type, option_sql_text FROM apps.per_proposal_question_types_v WHERE lookup_type IS NOT NULL;
  • SELECT proposal_question_name, default_value_type, default_value FROM apps.per_proposal_question_types_v ORDER BY proposal_question_name;

These queries support impact analysis before patching, verification of seeded versus custom question types, and validation that dynamic SQL in FIND_SELECT_SQL or OPTION_SQL_TEXT references valid objects. Because the view carries no business logic, all interpretation of HTML_TYPE and PROVIDE_FIND must be applied by the consuming form, report, or integration layer.