Results for “as_collateral_req_items_v”

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

Overview

AS_COLLATERAL_REQ_ITEMS_V is a Sales Foundation (AS) reporting and inquiry view that exposes the line-level detail of collateral request items. In Oracle EBS terminology, "collateral" refers to promotional or marketing material — kits, point-of-purchase displays, signage, literature, and similar items — that a manufacturer distributes to customers or channel partners to support a promotion. When a user or a downstream process raises a collateral request, the individual inventory items and quantities being requested are recorded at the line level. This view consolidates those line records with descriptive promotion attributes and decoded status information so that the data can be consumed by concurrent programs, Oracle Reports, OA Framework pages, and integration extracts without requiring the caller to join multiple AS and INV objects manually.

In Oracle EBS 12.1.1 and 12.2.2, the view is shipped as part of the AS schema. In the specific environment referenced by the ETRM metadata, the view is documented as "Not implemented in this database," meaning the APPS-level synonym and underlying object may be absent or not deployed. Integrators should therefore verify existence via ALL_VIEWS or FND_VIEWS before relying on it in custom code. Its primary role is read-only reporting, not transactional processing; the base table AS_COLLATERAL_REQ_ITEMS remains the DML target.

Underlying Base Objects

The view text documents three referenced objects:

• AS_COLLATERAL_REQ_ITEMS ITM — the driving base table holding one row per collateral request line, including quantity, inventory item, organization, and the fifteen descriptive flexfield (DFF) attribute columns.
• AS_PROMOTIONS P — the promotion header, joined on ITM.COLLATERAL_ID = P.PROMOTION_ID(+). The outer join preserves request lines even when the referenced promotion is absent.
• AS_LOOKUPS ASLKP — the status lookup, joined on LOOKUP_TYPE = 'COLLATERAL_STATUS'. The lookup code is derived via DECODE on P.STATUS, mapping 'K' with PUBLIC_FLAG 'N' to PERSONAL_KIT and otherwise to PUBLIC_KIT.

No other base objects are documented. The view exposes no lookup for inventory item master or organization; those denormalized descriptions are typically resolved separately against MTL_SYSTEM_ITEMS_KFV and ORG_ORGANIZATION_DEFINITIONS.

Key Columns

• COLLATERAL_REQ_ITEM_ID — primary key of the request line; COLLATERAL_REQUEST_ID links to the parent request header.
• COLLATERAL_ID — the promotion/collateral identifier, foreign key to AS_PROMOTIONS.PROMOTION_ID.
• CODE, NAME, DESCRIPTION — the promotion code, name, and description for the requested collateral.
• STATUS — decoded from the lookup via the DECODE expression; returns the lookup MEANING (e.g., Active, Inactive, Personal Kit, Public Kit).
• KIT_FLAG — from P.COLLATERAL_KIT_FLAG; indicates whether the collateral is a kit. CHARGEBACK_AMT carries P.COLLATERAL_CHARGEBACK_AMT.
• INVENTORY_ITEM_ID, ORGANIZATION_ID, QUANTITY — the requested item, its fulfillment organization, and the requested quantity.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard WHO audit columns.
• ATTRIBUTE_CATEGORY, ATTRIBUTE1–ATTRIBUTE15 — the DFF columns for the request line, enabling site-specific reporting.

Common Use Cases and Queries

Typical uses include collateral request fulfillment reports, promotion-to-item consumption analysis, kit versus non-kit chargeback reconciliation, and extracts feeding order or shipment staging. A representative query listing open request lines with promotion details is:

• SELECT req_item_id, collateral_id, code, name, status, kit_flag, quantity FROM as_collateral_req_items_v WHERE status = 'Active' ORDER BY collateral_request_id, collateral_req_item_id; — filter active collateral lines.
• SELECT collateral_request_id, inventory_item_id, organization_id, SUM(quantity) FROM as_collateral_req_items_v GROUP BY collateral_request_id, inventory_item_id, organization_id; — aggregate requested quantities per request, item, and organization.
• SELECT code, kit_flag, chargeback_amt, COUNT(*) FROM as_collateral_req_items_v WHERE kit_flag = 'Y' GROUP BY code, kit_flag, chargeback_amt; — isolate kit collateral for chargeback review.

Because the view is documented as not implemented in the mapped environment, deployments should confirm availability and, where absent, reproduce the logic directly against AS_COLLATERAL_REQ_ITEMS, AS_PROMOTIONS, and AS_LOOKUPS.