Search Results pay_au_module_types




Overview
Explore in AI Chat

Overview

PAY_AU_MODULE_TYPES is a reference (lookup) table owned by the HR schema and used by the Oracle Payroll (PAY) product, specifically within the Australian localization component of Oracle E-Business Suite. Its documented description states that it "defines the module types used in the generic code caller." In practical terms, the table acts as a controlled registry of module type identifiers that the Australian payroll code framework references when invoking generic code-caller routines. By centralizing these type definitions, the application avoids hard-coding module classifications into PL/SQL packages and instead resolves them dynamically at runtime.

The table is classified as VALID in the ETRM metadata and is present in both 12.1.1 and 12.2.2 environments. Based on the foreign-key topology — where PAY_AU_MODULE_TYPES is referenced by PAY_AU_MODULES and WMS_API_HOOKED_ENTITIES, but itself has no outward foreign keys — the heuristic Data Vault classification is hub-leaning. This suggests that, in a dimensional or Data Vault model, the table would most naturally be represented as a hub entity keyed on its natural business key, with dependent descriptive attributes treated as satellite data.

Key Information Stored

The physical schema documented for 12.2.2 contains eleven columns. The most significant columns and their roles are:

  • MODULE_TYPE_ID — The surrogate primary key, enforced by the PAY_AU_MODULE_TYPES_PK constraint. This numeric identifier uniquely identifies each module type row.
  • NAME — The business-key candidate, enforced by the unique index PAY_AU_MODULE_TYPES_UK1. It carries the human-readable module type name used by the code caller to look up the correct module type by label.
  • ENABLED_FLAG — Controls whether a given module type is active and available to the generic code caller. Disabled rows are typically skipped during resolution.
  • DESCRIPTION — Free-text explanation of the module type's purpose, aiding configuration and troubleshooting.
  • ZD_EDITION_NAME — Part of the composite unique index PAY_AU_MODULE_TYPES_U1 (MODULE_TYPE_ID, ZD_EDITION_NAME), which supports the editioning/online-patching model introduced in EBS 12.2.
  • OBJECT_VERSION_NUMBER — Optimistic locking column used by the Oracle Applications framework to detect concurrent updates.
  • The remaining audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN — provide standard who-column auditing required by the EBS schema conventions.

The distinction between the surrogate PK (MODULE_TYPE_ID) and the business-key candidates (NAME, and the composite MODULE_TYPE_ID with ZD_EDITION_NAME) is important for integration and data migration design.

Common Use Cases and Queries

Typical usage includes resolving a module type name to its ID for downstream processing, validating that a module type is enabled before invoking the code caller, and generating configuration reports of active Australian payroll module types. A common query pattern is:

  • Lookup by business key: SELECT module_type_id, name, enabled_flag FROM hr.pay_au_module_types WHERE name = :p_name AND enabled_flag = 'Y';
  • List all active types: SELECT module_type_id, name, description FROM hr.pay_au_module_types WHERE enabled_flag = 'Y' ORDER BY name;
  • Join to dependent modules: SELECT m.module_type_id, t.name, COUNT(*) FROM hr.pay_au_modules m JOIN hr.pay_au_module_types t ON m.module_type_id = t.module_type_id GROUP BY m.module_type_id, t.name;

Because the table is a low-volume reference table, queries against it are inexpensive and can be safely embedded in PL/SQL lookup routines or BI Publisher reports supporting Australian payroll configuration audits.

Related Objects

The foreign-key metadata identifies the following dependencies and referencing objects:

  • PAY_AU_MODULES — References PAY_AU_MODULE_TYPES via the MODULE_TYPE_ID column. This is the primary dependent table, linking individual Australian payroll modules to their parent module type.
  • WMS_API_HOOKED_ENTITIES — Also references PAY_AU_MODULE_TYPES through MODULE_TYPE_ID, indicating that the Warehouse Management System's API hook framework shares this module type classification.
  • PAY_AU_MODULE_TYPES_PK — The primary key constraint on MODULE_TYPE_ID.
  • PAY_AU_MODULE_TYPES_UK1 — The unique index on NAME, defining the business key.
  • PAY_AU_MODULE_TYPES_U1 — The edition-aware unique index on (MODULE_TYPE_ID, ZD_EDITION_NAME).

These relationships confirm the table's role as a hub-leaning reference entity that anchors both payroll module definitions and API hook configurations within the Australian localization.