Search Results pn_pay_group_rules_u1

Overview

PN.PN_PAY_GROUP_RULES is a seed-data table within the Oracle E-Business Suite Payables and Payments (PN) schema. It stores the definitions of payment grouping rules — named configurations that determine how payment processing batches, groups, and sequences payment instructions generated from invoices, payment batches, and payment process requests. In Oracle EBS 12.1.1 and 12.2.2, these rules are referenced by payment process templates and payment process profiles, which the Payments workbench and the Oracle Payments engine consult during payment instruction creation.

The table is partitioned by organization, a design choice that aligns grouping rule definitions with the Multi-Org access control model used throughout the PN schema. It resides in the APPS_TS_SEED tablespace, consistent with its role as reference and setup data rather than high-volume transactional data. The documented heuristic Data Vault classification is standalone. Under a Data Vault modeling suggestion, this object is best treated as a descriptive reference or lookup construct rather than as a hub, link, or satellite, since it holds no foreign-key relationships to other tables and carries no history or transactional grain beyond standard WHO auditing columns. Its primary key, PN_PAY_GROUP_RULES_PK, is defined on GROUPING_RULE_ID.

Key Information Stored

The table comprises 24 documented columns. The most significant are:

• GROUPING_RULE_ID — NUMBER(15), mandatory. The surrogate primary key that uniquely identifies each grouping rule. It is also the column behind the unique index PN_PAY_GROUP_RULES_U1.
• NAME — VARCHAR2(30), mandatory. The business identifier for the grouping rule. It is the second documented business-key candidate, enforced by the unique index PN_PAY_GROUP_RULES_U2.
• DESCRIPTION — VARCHAR2(240). A free-text explanation of the rule's purpose.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — the five standard WHO audit columns. They record insert and update provenance, which is essential for seed-data auditing and for diagnosing configuration drift across environments.
• ATTRIBUTE_CATEGORY — VARCHAR2(30). The context or structure identifier for the descriptive flexfield (DFF).
• ATTRIBUTE1 through ATTRIBUTE15 — fifteen VARCHAR2(150) flexfield segments. Although these are absent from the excerpted query text, the documented column list confirms all fifteen are present. They provide extensibility for customer-specific grouping attributes without schema modification.

The distinction between the surrogate key (GROUPING_RULE_ID) and the business-key candidates (GROUPING_RULE_ID via U1, and NAME via U2) is important: NAME is the human-recognizable identifier used in setup forms, while GROUPING_RULE_ID is the internal foreign key referenced by dependent configuration and runtime tables.

Common Use Cases and Queries

Typical uses include auditing payment grouping rules after a clone or upgrade, validating that rule names are unique across the enterprise, and identifying customer-defined flexfield attribute usage.

A basic lookup of a rule by name:

SELECT grouping_rule_id, name, description
FROM   pn_pay_group_rules
WHERE  name = :rule_name;

Checking for duplicates against the U2 business key:

SELECT name, COUNT(*)
FROM   pn_pay_group_rules
GROUP  BY name
HAVING COUNT(*) > 1;

Auditing recently modified setup data:

SELECT name, last_update_date, last_updated_by
FROM   pn_pay_group_rules
ORDER  BY last_update_date DESC;

Reporting on DFF usage, for example where a particular attribute is populated:

SELECT grouping_rule_id, name, attribute_category, attribute1
FROM   pn_pay_group_rules
WHERE  attribute1 IS NOT NULL;

These queries support configuration reviews, migration validation, and reconciliation between a source and target environment.

Related Objects

The documented relationship data classifies PN_PAY_GROUP_RULES as standalone, meaning no foreign keys were mined from the physical schema. Dependents therefore reference it logically rather than through declared FK constraints. The most significant associated objects are:

• PN_PAY_GROUP_RULES_PK — the primary key constraint on GROUPING_RULE_ID, the canonical join key.
• PN_PAY_GROUP_RULES_U1 — the unique index on GROUPING_RULE_ID, providing the surrogate-key path.
• PN_PAY_GROUP_RULES_U2 — the unique index on NAME, the business-key lookup path.
• Payment process profile and payment process template entities in the PN schema, which resolve a grouping rule through its GROUPING_RULE_ID or NAME to determine instruction grouping behavior.
• Setup and lookup query components (for example value-set backed LOVs in the Payments setup UI) that validate a grouping rule by NAME against PN_PAY_GROUP_RULES_U2.
• Reporting and diagnostic queries that join grouping rule definitions to payment process profile configuration and to transactions processed by those profiles.

Because no FK relationships are documented, any join to downstream payment or profile tables should rely on GROUPING_RULE_ID as the logical foreign key, and referential integrity should be validated through application logic and setup review rather than database constraints.