Results for “as_lists_u1”

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

Overview

OSM.AS_LISTS_ALL is the foundational table in the Oracle E-Business Suite (EBS) Territory Manager / Sales Resource Manager data model, owned by the OSM schema and registered in FND Design Data as AS.AS_LISTS_ALL. It stores the master definitions of lists used to group, qualify, and route trading partners, resources, and assignments across Oracle Sales, Oracle Receivables, and related order-to-cash modules. Each row represents a named list governed by an assignment rule, priority, ownership, and enablement state, and the table is deployed on the APPS_TS_ARCHIVE tablespace with a PCT_FREE of 10, consistent with a low-volatility, reference-style archive-oriented structure.

The metadata classifies AS_LISTS_ALL as standalone under the heuristic Data Vault model derived from its foreign-key structure. This classification is a modeling suggestion rather than a physical constraint: the table behaves as a hub-like entity whose stable business identifier is the numeric LIST_ID, with descriptive and administrative state attributes forming an effectively embedded satellite. Because it is documented as standalone, no upstream parent tables are enforced through declarative foreign keys, and dependency direction must be inferred from the application logic and the derived columns present on the row.

Key Information Stored

The table contains 37 documented columns. The most significant are:

• LIST_ID — the surrogate primary key, enforced by AS_LISTS_PK and by the unique index AS_LISTS_U1.
• NAME — the human-readable list name, VARCHAR2(240).
• OWNER_PERSON_ID — the person responsible for the list.
• ORG_ID — the operating unit / organization identifier, relevant to multi-org access control.
• TYPE — the list category used to drive assignment behavior.
• ENABLED_FLAG, PUBLIC_FLAG, LIST_ONLINE_FLAG — status and visibility indicators controlling whether and how the list participates in runtime assignment.
• LIST_RULE_ID and LIST_PRIORITY_ID — references to the qualification rule and ranking priority applied to list members.
• ASSIGN_PERSON_ID — the resource to which qualifying entities are assigned.
• LIST_SOURCE — the origin of the list definition.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the standard EBS DFF descriptive flexfield block.
• WHO columns including LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, plus concurrent-program columns REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE.

The business-key candidates are documented through two unique indexes. AS_LISTS_U1 is a single-column unique index on LIST_ID, while AS_LISTS_U2 enforces uniqueness across the composite of NAME, OWNER_PERSON_ID, and ORG_ID — meaning the same list name cannot be duplicated for the same owner within the same operating unit. Two non-unique indexes, AS_LISTS_N1 on OWNER_PERSON_ID and AS_LISTS_N2 on TYPE, support the common filtering patterns described below.

Common Use Cases and Queries

The table is queried to resolve list identity to assignment behavior, to audit which lists are active for an operating unit, and to identify lists owned by or assigned to specific resources. A representative lookup by business key uses AS_LISTS_U2:

• SELECT list_id, name, type, enabled_flag, list_rule_id, assign_person_id FROM osm.as_lists_all WHERE name = :p_name AND owner_person_id = :p_owner AND org_id = :p_org;

Operational reporting commonly filters on enablement and type using AS_LISTS_N2:

• SELECT name, owner_person_id, public_flag FROM osm.as_lists_all WHERE type = :p_type AND enabled_flag = 'Y';

Ownership audits leverage AS_LISTS_N1. Because LIST_ID is the sole primary key and no FK constraints are declared, joins to rule, priority, or member tables must be built explicitly on the ID columns, and multi-org queries should always constrain ORG_ID. DFF attributes should be joined through FND descriptive flexfield views rather than read directly when labels are required.

Related Objects

• AS_LISTS_U1 / AS_LISTS_U2 / AS_LISTS_N1 / AS_LISTS_N2 — the unique and non-unique indexes that enforce and accelerate access to LIST_ID, NAME+OWNER_PERSON_ID+ORG_ID, OWNER_PERSON_ID, and TYPE.
• AS_LISTS_PK — the primary key constraint on LIST_ID.
• OSM.AS_LIST_RULES and OSM.AS_LIST_PRIORITIES — joined via LIST_RULE_ID and LIST_PRIORITY_ID to resolve qualification and ranking behavior.
• OSM.AS_LIST_MEMBERS / list entry tables — joined on LIST_ID to retrieve the members grouped by each list.
• PER_ALL_PEOPLE_F — joined on OWNER_PERSON_ID and ASSIGN_PERSON_ID to resolve person names.
• HR_OPERATING_UNITS / ORG_ORGANIZATION_DEFINITIONS — joined on ORG_ID for multi-org context.
• FND_DESCR_FLEX_COLUMN_USAGES / DFF views — joined for ATTRIBUTE_CATEGORY and ATTRIBUTE1–15 context labels.