Results for “as_collateral_cat”

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

Overview

AS_COLLATERAL_CAT is a multi-org secured view owned by the APPS schema in Oracle E-Business Suite, belonging to the AS – Sales Foundation product family. It exposes collateral category assignment data — the association between sales promotions and the categories of collateral material tied to them. The view is a reportable and queryable layer over the underlying multi-org table, automatically restricting returned rows to the operating unit that the current session is authorized to access. This makes it the standard integration point for concurrent programs, Oracle Reports, and custom SQL that must respect organizational data security without embedding the org-id predicate manually. In both 12.1.1 and 12.2.2, the object is documented as VALID with no structural differences, meaning queries written against either release remain portable.

Underlying Base Objects

The view is defined solely over AS_COLLATERAL_CAT_ALL, a synonym resolving to the physical multi-org table that stores rows for every operating unit. The view applies the standard EBS multi-org security predicate through USERENV('CLIENT_INFO'), extracting the first ten characters of the client information string to derive the current ORG_ID and comparing it against the ORG_ID column of the base table. Where ORG_ID is null, the view substitutes a sentinel value of -99, a convention used throughout Sales Foundation to represent shared or global data accessible from any operating unit. Because the view performs no joins, all columns map one-to-one to the base table, and no data transformation other than filtering occurs.

Key Columns

• PROMOTION_ID — Identifier of the promotion to which the collateral category record belongs; primary link to AS_PROMOTIONS.
• CATEGORY_ID — Identifier of the collateral category assigned to the promotion.
• ORG_ID — Operating unit that owns the row; null entries represent global records governed by the -99 sentinel.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — Concurrent program audit trail identifying the request that last touched the row.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard WHO columns providing creation and modification audit information.

Common Use Cases and Queries

The view is typically used to resolve which collateral categories a promotion references, to build validation lists in collateral setup forms, and to feed downstream reporting that must remain operating-unit aware. Because the org filter is applied automatically, users simply query the view rather than the base table when the intent is current-session data.

To list collateral categories for a specific promotion:

• SELECT promotion_id, category_id, org_id FROM as_collateral_cat WHERE promotion_id = :p_promotion_id;

To audit recent changes:

• SELECT promotion_id, category_id, last_updated_by, last_update_date FROM as_collateral_cat WHERE last_update_date > SYSDATE - 30;

To count assignments per category within the current operating unit:

• SELECT category_id, COUNT(*) FROM as_collateral_cat GROUP BY category_id;

Because the view depends on CLIENT_INFO for org context, sessions that invoke it must initialize the multi-org environment (for example, via FND_GLOBAL or a standard EBS logon) or the predicate resolves to -99 and returns only global rows.