Search Results total_compile_value

Overview

APPS.MTL_ABC_ASSIGNMENT_GROUPS_V is a reporting and integration view in Oracle E-Business Suite (12.1.1 and 12.2.2) over the ABC assignment group model in Oracle Inventory. An ABC assignment group defines the scope of items to be classified using ABC analysis and the method by which cumulative value or quantity is compiled. The view joins the assignment group header to its associated compile header and to the lookup that describes the item scope, producing a single denormalized row per assignment group with human-readable meaning text for the item scope. This makes it the preferred access point for reports, concurrent programs, OAF pages, and interfaces that need assignment group attributes without reimplementing the DECODE and lookup joins themselves.

Underlying Base Objects

The view is defined in the APPS schema over three documented objects: MTL_ABC_ASSIGNMENT_GROUPS, MTL_ABC_COMPILE_HEADERS, and MFG_LOOKUPS. In the ETRM metadata these are registered as a SYNONYM, a SYNONYM, and a VIEW respectively, with APPS as the owner.

• MTL_ABC_ASSIGNMENT_GROUPS A — the driving table, holding assignment group identity, organization, scope type, classification method, secondary inventory, descriptive flexfield attributes, and the standard WHO/request audit columns.
• MTL_ABC_COMPILE_HEADERS B — supplies compile-level attributes (COMPILE_NAME, ITEM_SCOPE_CODE, DESCRIPTION, CUMULATIVE_VALUE, COMPILE_ITEMS). The join is on COMPILE_ID and ORGANIZATION_ID, with an OR branch that allows rows where COMPILE_ID is NULL to still match headers for the same organization.
• MFG_LOOKUPS C — restricted to LOOKUP_TYPE = 'MTL_ABC_ITEM_SCOPE'. The lookup code is derived with DECODE(A.COMPILE_ID, NULL, 1, B.ITEM_SCOPE_CODE), meaning rows with no compile default to the seeded "1" scope meaning, while compiled rows inherit the scope from the compile header. C.MEANING is projected outward as the user-facing scope description.

Every compile-derived column is wrapped in DECODE(A.COMPILE_ID, NULL, NULL, …) so that assignment groups without a compile return NULLs rather than unintentionally matching unrelated header data.

Key Columns

• ASSIGNMENT_GROUP_ID / ASSIGNMENT_GROUP_NAME — primary key and user-facing identifier of the assignment group.
• ORGANIZATION_ID — inventory organization owning the group; essential for multi-org filtering.
• COMPILE_ID — compile run linked to the group; NULL indicates an uncompiled group.
• ITEM_SCOPE_TYPE — the scope definition as stored on the group.
• ITEM_SCOPE_CODE / MEANING — scope code from the compile header (or the defaulted "1") and its MTL_ABC_ITEM_SCOPE lookup meaning.
• CLASSIFICATION_METHOD_TYPE — method used to rank items into ABC classes.
• SECONDARY_INVENTORY — secondary inventory restriction applied to the scope.
• COMPILE_NAME, DESCRIPTION, CUMULATIVE_VALUE, COMPILE_ITEMS — compile header detail, NULL when no compile exists.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–15 — descriptive flexfield context and segments.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — concurrent request audit context.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY — standard WHO columns.

Common Use Cases and Queries

Typical uses include LOV and validation queries for assignment groups, reporting on which scope and classification method each group uses, diagnosing groups that have never been compiled, and integration extracts feeding ABC analysis dashboards. The view is commonly filtered by organization and by ITEM_SCOPE_CODE or its MEANING to isolate groups of a particular scope.

List assignment groups with their scope meaning for one organization:

• SELECT assignment_group_id, assignment_group_name, item_scope_code, meaning, classification_method_type, compile_id FROM apps.mtl_abc_assignment_groups_v WHERE organization_id = :org_id ORDER BY assignment_group_name;

Identify groups that have no compile and therefore default to the seeded scope:

• SELECT assignment_group_id, assignment_group_name, item_scope_type, meaning FROM apps.mtl_abc_assignment_groups_v WHERE organization_id = :org_id AND compile_id IS NULL;

Report scope distribution across groups:

• SELECT item_scope_code, meaning, COUNT(*) group_count FROM apps.mtl_abc_assignment_groups_v WHERE organization_id = :org_id GROUP BY item_scope_code, meaning;

Because COMPILE_ID and the compile-derived columns can be NULL, queries should use outer-join-safe predicates and avoid equating NULL compile values with specific scope codes.