Results for “psp_element_types”

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

Overview

PSP_ELEMENT_TYPES is a reference and staging table owned by the PSP schema within the Oracle E-Business Suite Labor Distribution module (PSP). Its documented purpose is to store element types imported from Oracle HRMS. In the Oracle HRMS Payroll model, an element type represents a component of employee compensation or a pay-related definition, such as a salary element, an earnings element, or a deduction. Labor Distribution consumes these definitions to allocate and distribute payroll costs across projects, cost centers, and general ledger accounts. The table therefore acts as the bridge between the HRMS payroll definition layer and the Labor Distribution cost allocation and accounting layer.

Within the PSP schema, PSP_ELEMENT_TYPES functions as a central reference point for element type identity, validity, and accounting linkage. The documented physical schema lists 28 columns in ETRM 12.2.2, most of which are descriptive or audit-related. Based on the mined foreign key structure and the presence of a composite primary key, the heuristic Data Vault classification for this table is hub-leaning. This should be treated as a modeling suggestion rather than a definitive architectural statement: the table holds a natural business key (an element type identifier combined with an effective start date) around which dependent attributes and relationships are organized.

Key Information Stored

The primary key of PSP_ELEMENT_TYPES is PSP_ELEMENT_TYPES_PK, defined on the composite of ELEMENT_TYPE_ID and START_DATE_ACTIVE. ELEMENT_TYPE_ID is the surrogate identifier that links the row back to its source element type in Oracle HRMS, while START_DATE_ACTIVE establishes the date from which the element type definition becomes effective. A second documented unique index, PSP_ELEMENT_TYPES_U1, spans ELEMENT_TYPE_ID, START_DATE_ACTIVE, BUSINESS_GROUP_ID, and SET_OF_BOOKS_ID. This composite represents the strongest business-key candidate, indicating that a unique element type definition is scoped by operating unit (business group) and by the ledger (set of books) under which it is accounted. END_DATE_ACTIVE marks the date on which a given element type definition ceases to be effective, enabling date-effective, history-aware querying.

Business and accounting scope are captured by BUSINESS_GROUP_ID and SET_OF_BOOKS_ID. The ADJUST column supports adjustment-related processing within Labor Distribution, while COMMENTS provides free-text annotation. Standard audit columns are present: CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN. A set of 15 descriptive flexfield columns (ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15) allows customers to extend the table with installation-specific information without modifying its base structure.

Common Use Cases and Queries

Typical usage centers on retrieving currently active element types, joining element types to their accounting definitions, and verifying that imported HRMS elements are correctly scoped by business group and ledger. A frequently used pattern restricts the result set to rows where the effective date range includes the reporting date:

• Active element types query: SELECT element_type_id, start_date_active, end_date_active, business_group_id, set_of_books_id FROM psp_element_types WHERE TRUNC(SYSDATE) BETWEEN start_date_active AND NVL(end_date_active, TRUNC(SYSDATE));
• Joining to accounting setup: SELECT pet.element_type_id, peta.* FROM psp_element_types pet JOIN psp_element_type_accounts peta ON peta.element_type_id = pet.element_type_id AND peta.start_date_active = pet.start_date_active;
• Group membership reporting: SELECT pgel.* FROM psp_group_element_list pgel JOIN psp_element_types pet ON pgel.element_type_id = pet.element_type_id AND pgel.start_date_active = pet.start_date_active;

These patterns support reconciliation of imported HRMS elements, audit of accounting assignments, and reporting on which element types belong to which distribution groups.

Related Objects

• PSP_ELEMENT_TYPE_ACCOUNTS — references PSP_ELEMENT_TYPES via ELEMENT_TYPE_ID and START_DATE_ACTIVE; stores accounting assignments for each element type.
• PSP_GROUP_ELEMENT_LIST — references PSP_ELEMENT_TYPES via ELEMENT_TYPE_ID and START_DATE_ACTIVE; maps element types into distribution groups.
• Oracle HRMS element type definitions — the source from which records are imported.
• PSP distribution and allocation processes — consume the element type and accounting data defined here.