Search Results pspfv_auto_segments

Overview

PSPFV_AUTO_SEGMENTS is a read-only Oracle EBS view belonging to the PSP (Labor Distribution) product family. It presents the configuration of automatic segment generation rules that govern how Oracle Labor Distribution derives accounting flexfield segment values when it builds distribution lines for labor costs. The view exposes the mapping between a ledger (set of books), the business group that owns the rule, and the segment number that the rule targets.

The view is explicitly defined WITH READ ONLY, confirming that it is intended strictly for query and reporting purposes. It provides a denormalized, human-readable projection of the sparse PSP_AUTO_SEGMENTS table: rather than requiring the caller to resolve numeric identifiers, the view joins to GL_SETS_OF_BOOKS and HR_ALL_ORGANIZATION_UNITS so that the set of books name and business group name are returned directly. This makes it suitable for diagnostic reports, data extracts, and integration queries that need to reconcile Labor Distribution segment rules against the General Ledger and HR organization model.

In the ETRM 12.2.2 metadata, the object is recorded with the note "Not implemented in this database." This indicates that the view definition exists in the reference documentation but was not instantiated in the source environment from which the metadata was extracted; the view text below is therefore the authoritative definition of its structure. Note also that the referenced base objects list is empty in the metadata, meaning the dependencies must be inferred from the view text.

Underlying Base Objects

The view is defined over three objects:

• PSP_AUTO_SEGMENTS A — The primary PSP base table holding the automatic segment rules keyed by set of books and business group.
• GL_SETS_OF_BOOKS GL — The General Ledger ledger (set of books) definition, joined on A.SET_OF_BOOKS_ID = GL.SET_OF_BOOKS_ID to supply the ledger name.
• HR_ALL_ORGANIZATION_UNITS HOU — The HR organization units table, joined on A.BUSINESS_GROUP_ID = HOU.ORGANIZATION_ID to supply the business group name.

The join model is an inner join across all three sources; consequently, a rule row is only returned when both the ledger and the organization unit resolve. Because HR_ALL_ORGANIZATION_UNITS is the standard HR business group source, BUSINESS_GROUP_ID is effectively an ORGANIZATION_ID pointing at a business group–classification organization unit.

Key Columns

• SET_OF_BOOKS_ID — Identifier of the ledger for which the automatic segment rule is defined.
• SET_OF_BOOKS_NAME — Ledger name sourced from GL_SETS_OF_BOOKS.NAME; the primary human-readable ledger label.
• BUSINESS_GROUP_ID — Identifier of the business group owning the rule.
• BUSINESS_GRP_NAME — Business group name sourced from HR_ALL_ORGANIZATION_UNITS.NAME. This is the column matching the search term "business_grp_name" and is the standard label for the owning HR business group.
• SEGMENT_NUMBER — The ordinal position of the accounting flexfield segment to which the automatic segment rule applies.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE — Standard Oracle WHO audit columns describing rule creation and last modification.

Common Use Cases and Queries

Typical uses include verifying which ledgers and business groups have automatic segment rules configured, producing audit extracts of rule ownership and change history, and supporting troubleshooting when Labor Distribution generates unexpected segment values. The following query lists all rules with resolved names:

SELECT set_of_books_name, business_grp_name, segment_number, last_update_date
FROM pspfv_auto_segments
ORDER BY set_of_books_name, business_grp_name, segment_number;

To isolate rules for a specific ledger or business group, filter on the resolved names:

SELECT set_of_books_name, business_grp_name, segment_number
FROM pspfv_auto_segments
WHERE business_grp_name = :p_business_group_name;

Because the view is read-only and denormalized, it is safe to embed in BI Publisher datasets, FSG-style extracts, and custom concurrent programs without risk of accidental DML. Where a rule exists in PSP_AUTO_SEGMENTS but its ledger or business group is not resolvable, it will be silently excluded by the inner joins; in such cases query the base table directly to identify orphaned configuration rows.