Results for “per_cagr_entitlement_items_v”

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

Overview

The APPS.PER_CAGR_ENTITLEMENT_ITEMS_V view is a Human Resources (PER) product database object within Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented purpose is to support the Collective Agreements Entitlement Item user interface, presenting a denormalized, presentation-ready projection of collective agreement entitlement item definitions. Rather than exposing the raw transactional columns of the underlying entitlement item table, the view resolves surrogate identifiers into human-readable names and concatenates lookup-meaning descriptions, making it suitable for both UI rendering and downstream reporting or integration extraction. The view joins localized name data, API definition data, element type information, input value information, and flexfield value set data into a single coherent result set. Any application or report that needs to display entitlement item configuration—including the UOM, its default UOM source, and the associated parameter lookup context—can query this view instead of assembling the joins manually.

Underlying Base Objects

The view is defined over several base objects. The primary driving table is PER_CAGR_ENTITLEMENT_ITEMS (aliased CEI), which holds the stored entitlement item rows. It is left-outer-joined to PER_CAGR_ENTITLEMENT_ITEMS_TL (CEITL) on the entitlement item identifier and on USERENV('LANG'), providing translated item names. The API definition table PER_CAGR_APIS (PCA) supplies the API name, while PER_CAGR_API_PARAMETERS (CAP) supplies the display name, default UOM, UOM parameter, and UOM lookup type. Payroll element and input value definitions are drawn from the views PAY_ELEMENT_TYPES_V (PET) and PAY_INPUT_VALUES_V (PIV). Flexfield value set names are resolved from FND_FLEX_VALUE_SETS, which is referenced twice—once as F for the value set and once as B for the beneficial rule value set. Lookup decoding is performed through the HR_GENERAL package, specifically the DECODE_LOOKUP function, and the view also references HR_API, HR_CHKFMT, and PAY_INPUT_VALUES_PKG. All non-mandatory joins are outer joins, preserving entitlement item rows even when related API, parameter, element, input value, or value set data is absent.

Key Columns

The view exposes the surrogate key CAGR_ENTITLEMENT_ITEM_ID and the owning BUSINESS_GROUP_ID. ITEM_NAME is resolved via NVL(CEITL.ITEM_NAME, CEI.ITEM_NAME), preferring the translated value. ELEMENT_TYPE_ID and ELEMENT_NAME link the item to a payroll element, while INPUT_VALUE_ID and INPUT_VALUE_NAME link it to an input value. BENEFICIAL_RULE is presented alongside BENEFICIAL_RULE_DESC, decoded from the CAGR_BENEFICIAL_RULE lookup. API configuration columns include CAGR_API_ID, API_NAME, CAGR_API_PARAM_ID, and DISPLAY_NAME. Parameter typing is conveyed through COLUMN_TYPE and COLUMN_TYPE_DESC (lookup CAGR_PARAM_TYPES), COLUMN_SIZE, CATEGORY_NAME, and CATEGORY_NAME_DESC (lookup CAGR_CATEGORIES). Unit-of-measure handling is central: UOM holds the stored UOM, UOM_DESC is decoded using NVL(CAP.UOM_LOOKUP, 'UNITS'), and DEFAULT_UOM originates from the API parameter definition. Flexfield references are exposed as FLEX_VALUE_SET_ID/FLEX_VALUE_SET_NAME and BENEFICIAL_RULE_VALUE_SET_ID/BENEFICIAL_RULE_VALUE_SET. Audit columns include OBJECT_VERSION_NUMBER, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, and CREATION_DATE.

Common Use Cases and Queries

A frequent requirement is to determine, for each entitlement item, whether a UOM default is supplied by the API parameter definition. The DEFAULT_UOM column surfaces this directly:

• SELECT cagr_entitlement_item_id, item_name, uom, default_uom, uom_desc, uom_parameter, parameter_lookup_type FROM apps.per_cagr_entitlement_items_v WHERE default_uom IS NOT NULL;
• SELECT cagr_entitlement_item_id, item_name, api_name, display_name, column_type_desc, category_name_desc FROM apps.per_cagr_entitlement_items_v WHERE business_group_id = :p_bg_id ORDER BY item_name;
• SELECT item_name, beneficial_rule_desc, beneficial_rule_value_set, multiple_entries_allowed_flag FROM apps.per_cagr_entitlement_items_v WHERE legislation_code = :p_legislation;

Because the view resolves all lookup meanings and localized names, it is well suited to concurrent program extracts, BI Publisher reports, and integration staging tables. When the user search term is default_uom, the relevant pattern is to compare the stored UOM against DEFAULT_UOM and UOM_DESC to validate that configured entitlements honor the API parameter's unit-of-measure default. Filtering or joining on CAGR_API_PARAM_ID allows the same analysis at the parameter level.