Results for “cln_procat_catalog_v”

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

Overview

CLN_PROCAT_CATALOG_V is a reporting and integration view owned by the APPS schema in Oracle E-Business Suite. It belongs to the CLN product family, Supply Chain Trading Connector for RosettaNet, and is documented for both 12.1.1 and 12.2.2. Its purpose is to expose price list header information required to build the Sync Catalog XML message exchanged in RosettaNet Partner Interface Process (PIP) transactions. In a typical B2B catalog synchronization flow, a trading partner publishes a catalog built from a price list, and the connector must emit a payload that identifies the price list, its validity window, its currency context, and its status. CLN_PROCAT_CATALOG_V supplies that header-level data in a normalized format, decoupling the messaging layer from the underlying Advanced Pricing schema. The view is read-only by nature and is intended for query-based consumption rather than transactional maintenance.

Underlying Base Objects

The view is defined over three documented base objects, all referenced through APPS synonyms: QP_LIST_HEADERS_B, QP_LIST_HEADERS_TL, and QP_CURRENCY_DETAILS. QP_LIST_HEADERS_B holds the price list header row, including dates, active flag, list type, and the currency header reference. QP_LIST_HEADERS_TL provides the translated list name, filtered by USERENV('LANG') so that only the session-language name is returned. QP_CURRENCY_DETAILS holds the multi-currency detail rows linked through CURRENCY_HEADER_ID. The view is a UNION of two branches. The first branch selects headers that have no currency detail rows and synthesizes a single row with CURRENCY_DETAIL_ID hard-coded to 0, TO_CURRENCY_CODE derived from LH.CURRENCY_CODE, and FIXED_VALUE set to 1. The second branch joins QP_CURRENCY_DETAILS to return each real currency detail row with its own CURRENCY_DETAIL_ID, TO_CURRENCY_CODE, and FIXED_VALUE. Both branches restrict to LIST_TYPE_CODE of 'PRL' (price list) and ACTIVE_FLAG of 'Y'.

Key Columns

• LIST_HEADER_ID — primary identifier of the price list header, used as the join key back to QP_LIST_HEADERS_B.
• NAME — the translated price list name from QP_LIST_HEADERS_TL.
• START_DATE_ACTIVE / END_DATE_ACTIVE — the effective date range of the price list.
• ACTIVE_FLAG — always 'Y' in the view output because of the filter predicate.
• LIST_TYPE_CODE — always 'PRL' in the view output for the same reason.
• CURRENCY_DETAIL_ID — 0 when the header has no currency details; otherwise the QP_CURRENCY_DETAILS identifier.
• TO_CURRENCY_CODE — the target currency of the price list. This is the column most relevant to searches on "to_currency_code".
• FIXED_VALUE — indicates whether the currency conversion is fixed (1) or derived from currency details.
• COMMENTS — free-form header comments carried through from QP_LIST_HEADERS_B.

Common Use Cases and Queries

The principal use case is generating Sync Catalog payloads, where each active price list and its associated target currency must be reported. A query filtered on the currency column is common when validating that a catalog is published in an expected currency:

SELECT list_header_id, name, to_currency_code, fixed_value, start_date_active, end_date_active FROM cln_procat_catalog_v WHERE to_currency_code = 'USD' ORDER BY name;

The view is also used to enumerate all active price lists and their multi-currency expansions:

SELECT list_header_id, name, currency_detail_id, to_currency_code FROM cln_procat_catalog_v WHERE currency_detail_id > 0;

Because the view already restricts to active price lists, it is well suited for reconciliation reports that compare EBS price list configuration against what was transmitted to a trading partner. Note that CURRENCY_DETAIL_ID of 0 rows represent headers without currency details, while non-zero rows represent the expanded multi-currency set.