Search Results paybv_consolidation_set




Overview
Explore in AI Chat

Overview

The PAYBV_CONSOLIDATION_SET view is a business-group-secured reporting layer over the PAY_CONSOLIDATION_SETS table in the Oracle E-Business Suite Payroll (PAY) module. It is owned by the APPS schema and holds VALID status in both 12.1.1 and 12.2.2. The view presents consolidation set definitions — named groupings that determine how multiple payroll runs and assignment sets are processed and reported together — while appending a runtime security indicator, BG_SECURITY_FLAG_CODE, that reflects the current session's business group context.

The object follows the "BV" naming convention used by Oracle Payroll for business-group-secured views. Its primary role is to expose consolidation set data to forms, concurrent processes, and integration components while transparently signalling whether each row is visible under the session's security profile. Because the security flag is computed at query time rather than stored, the view centralises business group filtering logic that would otherwise be duplicated across numerous call sites.

Underlying Base Objects

The view is defined over two documented dependencies. The first is the PAY_CONSOLIDATION_SETS synonym, which resolves to the underlying consolidation set entity table holding the authoritative definition of each set, including its name, descriptive comments, owning business group, and standard WHO audit columns. The second is the HR_BIS package, referenced solely to obtain the session's secured business group identifier through the GET_SEC_PROFILE_BG_ID function.

The view is created WITH READ ONLY, so it supports query access only; all maintenance of consolidation sets must be performed against the base objects themselves. The join between the two dependencies is not a conventional equijoin but a scalar comparison: the business group on the row is tested against the value returned by HR_BIS.GET_SEC_PROFILE_BG_ID, with NVL ensuring that rows are not filtered out when the security function returns null.

Key Columns

  • CONSOLIDATION_SET_ID — Primary identifier for the consolidation set, used as the foreign key from payroll action and process parameter records.
  • CONSOLIDATION_SET_NAME — User-defined name of the set, as displayed in Payroll setup forms and process parameters.
  • BUSINESS_GROUP_ID — Identifier of the business group that owns the consolidation set; this is the column against which the security comparison is evaluated.
  • COMMENTS — Free-text description recorded by the administrator when the set was defined.
  • BG_SECURITY_FLAG_CODE — Derived column returned by the CASE expression. It evaluates to 'Y' when the row's BUSINESS_GROUP_ID matches the session's secured business group (or when the secured value is null), and 'N' otherwise. This is the column referenced by the user's search term.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard WHO audit columns inherited from the base table, supporting change tracking and diagnostic reporting.

Common Use Cases and Queries

The view is typically queried when building lists of consolidation sets for a process parameter, validating access before submission of a payroll process, or auditing which sets belong to which business group. A straightforward lookup of all visible sets is shown below.

  • SELECT consolidation_set_id, consolidation_set_name, business_group_id, bg_security_flag_code FROM apps.paybv_consolidation_set ORDER BY consolidation_set_name; — returns every set with its security indicator.
  • SELECT consolidation_set_id, consolidation_set_name FROM apps.paybv_consolidation_set WHERE bg_security_flag_code = 'Y'; — restricts output to sets owned by the session's secured business group, the standard pattern for LOV-style queries.
  • SELECT cs.consolidation_set_name, cs.comments, cs.last_update_date FROM apps.paybv_consolidation_set cs WHERE cs.consolidation_set_id = :p_set_id; — retrieves descriptive detail for a specific set identified by parameter.
  • SELECT business_group_id, COUNT(*) FROM apps.paybv_consolidation_set GROUP BY business_group_id; — summarises set distribution across business groups for administrative review.

Because BG_SECURITY_FLAG_CODE is computed dynamically, filtering on it reflects the querying session's security profile rather than a fixed attribute of the row. Reports and integrations that must respect business group isolation should therefore filter on this column rather than on BUSINESS_GROUP_ID directly.