Results for “ar_ta_prom_prods”

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

Overview

AR_TA_PROM_PRODS is a Receivables (AR) reporting view delivered within the Oracle E-Business Suite Release 12.1.1 and 12.2.2 technology stack. Its documented description identifies it explicitly as a "SINGLE_ORG view," a designation that carries significant functional meaning in the EBS multi-org architecture. Unlike a _ALL table or a secure view that spans all operating units, a SINGLE_ORG view is intended to expose only the rows belonging to the operating unit currently established in the session context. Access is controlled through the USERENV('CLIENT_INFO') mechanism, which the view decodes to determine the active ORG_ID at runtime.

The view belongs to the Oracle Trade Management and Advanced Pricing promotion data model inside Receivables. It presents the association between promotions or promotion product groups and the individual inventory products they cover. The acronym "TA" refers to the Trade Management schema elements, while "PROM_PRODS" denotes promotion products. In practice, the object functions as a reporting and integration surface that allows application modules, concurrent programs, and custom SQL to retrieve promotion-to-product relationships with operating-unit security already applied by the database layer rather than by application code.

Underlying Base Objects

The view is defined over a single documented base object, AR_TA_PROM_PRODS_ALL. This table stores promotion product definitions across all operating units and is the multi-org _ALL counterpart from which the secure, single-org view is derived. Although the ETRM metadata records "Referenced base objects: none documented" for the view itself, the view text explicitly selects from AR_TA_PROM_PRODS_ALL, making that table the authoritative source of rows and columns.

The defining filter is:

NVL(ORG_ID, NVL(TO_NUMBER(DECODE(SUBSTR(USERENV('CLIENT_INFO'), 1, 1), ' ', NULL, SUBSTR(USERENV('CLIENT_INFO'), 1, 10))), -99)) = NVL(TO_NUMBER(DECODE(SUBSTR(USERENV('CLIENT_INFO'), 1, 1), ' ', NULL, SUBSTR(USERENV('CLIENT_INFO'), 1, 10))), -99)

This predicate matches the ORG_ID column against the operating unit value embedded in the client information string, defaulting unmatched values to -99. The same construction appears throughout EBS multi-org secured views and ensures that a query executed in one operating unit does not return promotion product data owned by another. Note that the ETRM record lists the implementation status as "Not implemented in this database," meaning the view may be absent from a given instance unless the Trade Management or Advanced Pricing promotion functionality has been installed and the corresponding product tables have been created.

Key Columns

• ROW_ID — The physical ROWID of the underlying row, exposed for row-level identification and update operations.
• PROMOTION_ID — Foreign key identifying the parent promotion to which the product association belongs.
• PROMOTION_PRODUCT_GROUP_ID — Identifier of the product group within the promotion, allowing products to be organized into logical groupings.
• PRODUCT_ID — Identifier of the inventory product or item linked to the promotion.
• PRODUCT_NAME — The descriptive name of the product. This is the column most frequently referenced by users searching for "product_name" and is the primary human-readable attribute returned by the view.
• ID_TYPE — Indicates the type of identifier stored, distinguishing products from other identification categories.
• ORG_ID — The operating unit that owns the row and drives the single-org security filter.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard EBS audit columns recording creation and modification history.
• ATTRIBUTE_CATEGORY through ATTRIBUTE15 — The standard flexible descriptive flexfield segment columns available for customer-specific extension data.

Common Use Cases and Queries

Typical uses include reporting on which products are attached to a promotion, validating promotion configuration before pricing or accrual processing, and feeding integration extracts that require product-level promotion detail. Because the view already enforces operating unit security, custom reports can query it directly without additional org predicates.

A representative query listing products for a given promotion within the current operating unit:

SELECT promotion_id, promotion_product_group_id, product_id, product_name, id_type
FROM ar_ta_prom_prods
WHERE promotion_id = :p_promotion_id;

To locate a promotion by product name:

SELECT promotion_id, product_id, product_name
FROM ar_ta_prom_prods
WHERE product_name LIKE :p_name_pattern;

Because the security predicate depends on USERENV('CLIENT_INFO'), these queries return only rows for the operating unit initialized in the session. When the view is not implemented in a given database, administrators should confirm that the Trade Management promotion tables and the AR_TA_PROM_PRODS_ALL base table have been created during product installation.