Search Results include_exclude

Overview

The HR_ASSIGNMENT_SET_AMENDMENTS_V view is a seeded, dictionary-managed database object owned by the APPS schema within the PER — Human Resources product module. Registered in Oracle E-Business Suite 12.1.1 and 12.2.2 with a status of VALID, its documented purpose is solely to support the Oracle HRMS user interface. It is not a certified public API or an interface table intended for direct external writes; it is a supporting query structure that shapes records for an HRMS form, region or self-service page concerned with the composition of assignment sets.

At a functional level the view resolves the membership of an assignment set into readable, presentation-ready rows. An assignment set in Oracle HRMS is a named grouping of assignments, typically used for payroll processing, reporting scope, or batch eligibility. Membership can be either inclusive (the listed assignments are the set) or exclusive (the listed assignments are excluded from the set). The view extracts those membership and exclusion records, resolves the numeric lookup code to its meaning, joins each row to the person and assignment it references, and returns additional contextual attributes describing the worker and the session under which the query executes. Selecting from the view therefore yields the same visual information the form displays, but in a queryable relational result set suitable for ad hoc reporting or troubleshooting.

Underlying Base Objects

The view text is a two-branch UNION ALL query that draws on the following documented base objects: FND_SESSIONS (SYNONYM), HR_ASSIGNMENT_SET_AMENDMENTS (SYNONYM), HR_LOOKUPS (VIEW), PER_ALL_ASSIGNMENTS_F (SYNONYM) and PER_ALL_PEOPLE_F (SYNONYM). The HR_API package is noted in the ETRM 12.2.2 metadata; FND_SESSIONS is not involved in calling it, but it is a documented referenced object of the view.

HR_ASSIGNMENT_SET_AMENDMENTS is the driving table and supplies the membership rows: the assignment ID, the assignment set ID, the include/exclude code, row ID, and the standard WHO audit columns. PER_ALL_ASSIGNMENTS_F and PER_ALL_PEOPLE_F are joined through their date-tracked effective date ranges, which is why the query compares FND_SESSIONS.EFFECTIVE_DATE against the assignment and person effective start and end dates rather than joining on assignment and person ID alone. Both PER_ALL_ASSIGNMENTS_F and PER_ALL_PEOPLE_F are members of the PER_ASSIGNMENTS_F and PER_PEOPLE_F family typically exposed through row-level security (security profiles), so results are normally constrained to the operating user's accessible HR organizations. Two aliases of HR_LOOKUPS translate stored codes into display meanings: HR1 against lookup type INCLUDE_EXCLUDE produces the include/exclude meaning, while HR against lookup type YES_NO_ALL provides the current employee switch meaning. The FND_SESSIONS table contributes the current effective date of the user's session.

Key Columns

• ROW_ID — the Oracle row identifier of the underlying HR_ASSIGNMENT_SET_AMENDMENTS row, used by the form to position on the correct record.
• ASSIGNMENT_ID — identifier of the assignment that is a member of (or excluded from) the set.
• ASSIGNMENT_SET_ID — identifier of the assignment set that the membership row belongs to.
• INC_EXC_CODE — the raw stored include/exclude value from HR_ASSIGNMENT_SET_AMENDMENTS.INCLUDE_OR_EXCLUDE. This is the column underlying the search term "inc_exc_code" and is the value passed to the lookup for translation.
• INCLUDE_OR_EXCLUDE — the uppercased display meaning derived from HR_LOOKUPS lookup type INCLUDE_EXCLUDE, e.g. INCLUDE or EXCLUDE. This is the human-readable counterpart to INC_EXC_CODE.
• ASSIGNMENT_NUMBER — the assignment's user-visible number from PER_ALL_ASSIGNMENTS_F.
• FULL_NAME — the person's formatted name from PER_ALL_PEOPLE_F.
• CURRENT_EMPLOYEE_FLAG — the person's current employee flag, defaulted to 'N' via NVL when null.
• CURRENT_EMP_SWITCH — the YES_NO_ALL lookup meaning for the current employee flag.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard WHO audit columns for the membership row.

Common Use Cases and Queries

Typical uses include reviewing the contents of an assignment set, auditing why a given assignment is included in or excluded from a set, verifying that lookup translation values are consistent, and reconciling the set membership a user sees on the form against stored table contents. Because the view is session-filtered through FND_SESSIONS and its query is driven by the effective date of the current session (via USERENV('SESSIONID')), it is best queried from within an EBS session context, such as through the concurrent program framework or a form-based query, rather than from an external client.

A query listing set members with readable codes:

SELECT assignment_set_id,
       assignment_id,
       full_name,
       assignment_number,
       inc_exc_code,
       include_or_exclude
FROM   apps.hr_assignment_set_amendments_v
ORDER  BY assignment_set_id, full_name;

A query isolating the raw include/exclude codes for a specific set:

SELECT assignment_set_id,
       assignment_id,
       inc_exc_code,
       include_or_exclude,
       current_emp_switch
FROM   apps.hr_assignment_set_amendments_v
WHERE  assignment_set_id = :p_assignment_set_id
AND    inc_exc_code IN ('I', 'E');

A query testing whether stored codes resolve cleanly to lookup meanings:

SELECT inc_exc_code,
       COUNT(*) total_rows,
       COUNT(include_or_exclude) translated_rows
FROM   apps.hr_assignment_set_amendments_v
GROUP  BY inc_exc_code;

Differences between total_rows and translated_rows indicate lookup entries that are missing or disabled for lookup type INCLUDE_EXCLUDE, a common cause of blank values on the HRMS form. For production reporting, consider extracting into a custom table or view if session-scoped filtering or the effective date comparison produces unexpected row counts across concurrent sessions.