Search Results as_collateral_kits_pk

Overview

AS_COLLATERAL_KITS_ALL is a transactional and setup table owned by the OSM schema within the AS (Sales Foundation) product family of Oracle E-Business Suite, and it is validated in both 12.1.1 and 12.2.2. The table defines collateral kit compositions: each row links a parent collateral (a promotion record) to a child collateral, together with the quantity of the child that belongs in the kit. Every parent-child pairing carries an ORG_ID, making the structure multi-org aware and enabling kit definitions to differ by operating unit.

Because the object resolves parent and child identifiers into independent foreign keys against a shared reference table, the heuristic Data Vault classification supplied in the metadata is link. This is a modeling suggestion rather than an enforced ETRM designation: the table naturally resolves the many-to-many association between parent and child collateral promotions and carries the descriptive QUANTITY attribute on the relationship itself.

Key Information Stored

• PARENT_COLLATERAL_ID — identifier of the parent collateral promotion. Forms the first component of the primary key and of the unique business index.
• CHILD_COLLATERAL_ID — identifier of the child collateral contained within the kit. Forms the second component of the primary key.
• ORG_ID — operating unit discriminator that scopes the kit definition. Included in the unique index, so the same parent/child pairing may be defined separately per organization.
• QUANTITY — the count of the child collateral included in each parent kit; the principal non-key business attribute.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard WHO audit columns recording creation and modification metadata.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — the DFF/Flexfield descriptive columns reserved for customer-specific extensions.

The primary key is AS_COLLATERAL_KITS_PK, defined on (PARENT_COLLATERAL_ID, CHILD_COLLATERAL_ID). The unique index AS_COLLATERAL_KITS_U1 on (PARENT_COLLATERAL_ID, CHILD_COLLATERAL_ID, ORG_ID) represents the leading business-key candidate, enforcing one kit line per parent-child combination per operating unit. Two foreign keys point to AS_PROMOTIONS_ALL, via PARENT_COLLATERAL_ID and CHILD_COLLATERAL_ID. Twenty-five columns are documented in the 12.2.2 physical schema.

Common Use Cases and Queries

Typical applications include kit definition maintenance, collateral availability checks, and reporting on collateral bundling. A common query retrieves the children of a parent kit:

SELECT k.PARENT_COLLATERAL_ID,
       k.CHILD_COLLATERAL_ID,
       k.QUANTITY,
       k.ORG_ID
FROM   AS_COLLATERAL_KITS_ALL k
WHERE  k.PARENT_COLLATERAL_ID = :parent_id
AND    k.ORG_ID               = :org_id;

Because both foreign keys resolve to AS_PROMOTIONS_ALL, a self-join or dual alias on that table resolves parent and child descriptions simultaneously:

SELECT pk.COLLATERAL_ID  parent_collateral,
       ck.COLLATERAL_ID  child_collateral,
       k.QUANTITY
FROM   AS_COLLATERAL_KITS_ALL k,
       AS_PROMOTIONS_ALL      pk,
       AS_PROMOTIONS_ALL      ck
WHERE  pk.COLLATERAL_ID = k.PARENT_COLLATERAL_ID
AND    ck.COLLATERAL_ID = k.CHILD_COLLATERAL_ID
AND    k.ORG_ID         = :org_id;

Reporting patterns frequently aggregate QUANTITY by parent to determine total collateral counts, or count distinct children to measure kit size. Data integrity checks should confirm that no parent and child identifiers are identical and that no duplicate combinations exist beyond the ORG_ID dimension. Multi-org reports must always filter on ORG_ID to avoid cross-operating-unit duplication.

Related Objects

• AS_PROMOTIONS_ALL — the reference table for both foreign keys; PARENT_COLLATERAL_ID and CHILD_COLLATERAL_ID join to its collateral identifier, making it the primary parent object.
• AS_COLLATERAL_KITS_PK — the primary key constraint defining row uniqueness on parent and child identifiers.
• AS_COLLATERAL_KITS_U1 — the unique index extending the key with ORG_ID; used by the optimizer for kit lookups by organization.
• AS_PROMOTIONS_ALL (aliased) — used again as the child reference in self-join reporting queries.
• Sales Foundation promotion APIs and concurrent programs that create and maintain collateral kits, which insert and update rows in this table.
• Descriptive flexfield definitions registered against ATTRIBUTE_CATEGORY and ATTRIBUTE1–15 for extended collateral kit attributes.