Results for “per_letter_gen_statuses”

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

Overview

PER_LETTER_GEN_STATUSES is a Human Resources (PER) module table in Oracle EBS 12.1.1 and 12.2.2 that stores the associations between letter types and the assignment status type changes that a generated letter is permitted to drive. It functions as an action-oriented mapping table within the Oracle Advanced Benefits / letter generation framework, where letters such as offer letters, promotion letters, or termination notifications can be configured to transition an employee assignment from one status to another (for example, from Active to Terminated) when the letter is produced.

The table resides in the HR schema and is documented by ETRM as containing 14 columns with a single-column primary key, PER_LETTER_GEN_STATUSES_PK, defined on LETTER_GEN_STATUS_ID. Under the heuristic Data Vault classification derived from its foreign key structure, this object is best modeled as a link table. It resolves a many-to-many relationship among letter types, assignment status types, and the business group that owns the configuration, rather than carrying the descriptive attributes typical of a satellite.

Key Information Stored

• LETTER_GEN_STATUS_ID — surrogate primary key (PER_LETTER_GEN_STATUSES_PK); uniquely identifies each letter-type-to-status mapping row.
• BUSINESS_GROUP_ID — foreign key to HR_ALL_ORGANIZATION_UNITS; scopes the configuration to a specific business group, enabling multi-organization partitioning of letter rules.
• LETTER_TYPE_ID — foreign key to PER_LETTER_TYPES; identifies the letter type whose status transition behavior is being defined.
• ASSIGNMENT_STATUS_TYPE_ID — foreign key to PER_ASSIGNMENT_STATUS_TYPES; identifies the assignment status the letter is authorized to apply.
• ENABLED_FLAG — indicates whether the association is currently active and available to the letter generation process.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID — concurrent program context columns that record which program created or maintained the row.
• PROGRAM_UPDATE_DATE — date the row was last touched by the concurrent program.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard WHO audit columns tracking the last modification and its user/session.
• CREATED_BY, CREATION_DATE — standard WHO columns capturing row creation provenance.

The business-key candidate is represented by the primary key index itself; no additional unique index is documented in the ETRM metadata, so uniqueness of the (BUSINESS_GROUP_ID, LETTER_TYPE_ID, ASSIGNMENT_STATUS_TYPE_ID) combination is an implementation-level assumption rather than a documented constraint.

Common Use Cases and Queries

Typical reporting scenarios include auditing which letters can change employee status, validating configuration before a letter generation run, and troubleshooting why a generated letter failed to update an assignment status. A representative query joins the mapping to its parents:

• List all enabled status transitions for a letter type: SELECT lgt.LETTER_TYPE_ID, ast.USER_STATUS, lgs.ENABLED_FLAG FROM PER_LETTER_GEN_STATUSES lgs, PER_ASSIGNMENT_STATUS_TYPES ast WHERE lgs.LETTER_TYPE_ID = :letter_type_id AND lgs.ASSIGNMENT_STATUS_TYPE_ID = ast.ASSIGNMENT_STATUS_TYPE_ID AND lgs.ENABLED_FLAG = 'Y';
• Audit business-group scope: filter on BUSINESS_GROUP_ID to confirm configuration isolation across legal entities.
• Trace creation provenance by inspecting REQUEST_ID and PROGRAM_ID to identify the concurrent process that loaded the mapping.

Related Objects

• PER_LETTER_TYPES — joined on LETTER_TYPE_ID; the parent defining the letter itself.
• PER_ASSIGNMENT_STATUS_TYPES — joined on ASSIGNMENT_STATUS_TYPE_ID; supplies valid target statuses.
• HR_ALL_ORGANIZATION_UNITS — joined on BUSINESS_GROUP_ID; the business group owning the configuration.
• PER_LETTER_GEN_STATUSES_PK — the unique index enforcing the primary key.
• PER_LETTERS / letter generation concurrent programs — consume these mappings at runtime to determine permissible status transitions.