Results for “per_proposal_category_members”

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

Overview

PER_PROPOSAL_CATEGORY_MEMBERS is an Oracle E-Business Suite Human Resources (PER) table that supports the Web Offers / iRecruitment recruitment functionality. It defines the profile categories of questions presented to candidates during an offer or proposal process. Each row links a specific proposal template to a proposal category type and optionally to a parent category type, thereby establishing the hierarchical grouping of questions that make up an offer questionnaire. In Oracle EBS 12.1.1 and 12.2.2 the table resides in the HR schema and is documented in the ETRM as a VALID object owned by the PER product family.

From a Data Vault modeling perspective, the mined FK structure classifies this table as satellite-leaning. It hangs off the PER_PROPOSAL_CATEGORY_TYPES and PER_PROPOSAL_TEMPLATES hubs and carries descriptive attributes such as display order flags, header text, and type indicators, which is characteristic of a satellite rather than a pure hub or link.

Key Information Stored

The table contains 13 documented columns. The most significant are:

• PROPOSAL_CATEGORY_MEMBER_ID — surrogate primary key, enforced by the unique index PER_PROPOSAL_CATEGORY_MEMBE_PK. This is the only documented unique index and therefore the sole business-key candidate; no natural alternate key is defined in the metadata.
• PROPOSAL_CATEGORY_TYPE_ID — foreign key to PER_PROPOSAL_CATEGORY_TYPES, identifying which category type the member belongs to.
• PROPOSAL_TEMPLATE_ID — foreign key to PER_PROPOSAL_TEMPLATES, associating the category member with the specific proposal template it belongs to.
• PARENT_CATEGORY_TYPE_ID — self-referencing foreign key to PER_PROPOSAL_CATEGORY_MEMBERS, enabling recursive category hierarchies.
• CATEGORY_DISPLAY_ORDER and PARENT_DISPLAY_ORDER — numeric ordering columns controlling the sequence in which categories and their parents are rendered to the candidate.
• DISPLAY_CATEGORY_HEADER — the label or heading text shown above the category in the Web Offers UI.
• TYPE — discriminator describing the nature of the category member record.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY — standard WHO audit columns populated by the EBS framework.

Common Use Cases and Queries

Typical uses include retrieving the complete category tree for a proposal template, ordering questions for a candidate offer, and auditing which categories were configured for a given template.

Sample: fetch all categories for a template in display order:

• SELECT pcm.proposal_category_member_id, pcm.proposal_category_type_id, pcm.parent_category_type_id, pcm.category_display_order, pcm.display_category_header FROM per_proposal_category_members pcm WHERE pcm.proposal_template_id = :template_id ORDER BY pcm.parent_display_order, pcm.category_display_order;

Sample: resolve the parent category header via the self-join:

• SELECT c.proposal_category_member_id, c.display_category_header, p.display_category_header AS parent_header FROM per_proposal_category_members c LEFT JOIN per_proposal_category_members p ON c.parent_category_type_id = p.proposal_category_member_id;

Sample: count questions per category by joining to PER_PROPOSAL_QUESTION_MEMBERS:

• SELECT pcm.proposal_category_member_id, COUNT(qm.proposal_question_member_id) FROM per_proposal_category_members pcm JOIN per_proposal_question_members qm ON qm.proposal_category_member_id = pcm.proposal_category_member_id GROUP BY pcm.proposal_category_member_id;

Related Objects

The following objects are the most significant dependents and referenced parents:

• PER_PROPOSAL_CATEGORY_TYPES — referenced via PROPOSAL_CATEGORY_TYPE_ID; defines the underlying category type.
• PER_PROPOSAL_TEMPLATES — referenced via PROPOSAL_TEMPLATE_ID; the parent proposal template.
• PER_PROPOSAL_CATEGORY_MEMBERS (self) — PARENT_CATEGORY_TYPE_ID creates the recursive hierarchy.
• PER_PROPOSAL_QUESTION_MEMBERS — child table; its PROPOSAL_CATEGORY_MEMBER_ID column references this table's primary key, tying questions to categories.