Search Results cache_rules

Overview

PV_OPP_MATCH_PUB is a public PL/SQL API within the Oracle EBS Trade Management (formerly Released Value Chain / ETRM) product family. Its business purpose is to evaluate opportunity selection rules — configurable criteria that determine which opportunities, leads, or partners qualify for a given trade process or promotion. The package allows callers to submit an entity (such as a lead or party), run it against active process rules, and return the matching selection criteria outcome. Internally the package caches the rule-attribute-value combinations across all opportunity selection rules so that matching can be performed efficiently within a session.

The package is declared with AUTHID CURRENT_USER and follows the standard EBS PL/SQL API conventions, accepting API version, initialization message list, commit, and validation level parameters. It is classified as a PUB (public) API, meaning it is supported for direct invocation by customer and partner extensions.

Key Procedures and Functions

Five documented procedures/functions comprise the API surface:

• OPPORTUNITY_SELECTION — The core entry point. It accepts an entity identifier and evaluates the applicable opportunity selection rules, returning whether the entity matches. Its signature carries the standard EBS API parameters (p_api_version, p_init_msg_list, p_commit, p_validation_level, p_entity_id). Internally it relies on the g_opp_selection_tab cache of t_opp_selection_rec records, each holding rank, process rule id, attribute id, currency, operator, selection criteria id, attribute value, last-attribute flag, and an AND/OR count.
• CACHE_RULES — Populates the session-level cache of rule-attribute-value information for all opportunity selection rules, so repeated selections avoid repeated queries against the rule tables.
• CLEAR_RULES_CACHE — Invalidates or empties the cached rule data, ensuring subsequent selections reflect updated rule definitions.
• PARTNER_SELECTION — Provides parallel selection logic for partners (as opposed to opportunities/leads), enabling partner qualification against the configured rules.
• The package also exposes the global type declarations t_opp_selection_rec and t_opp_selection_tab, which support the caching mechanism described above.

Tables Accessed

The package reads from and writes to several application tables via APPS synonyms:

• PV_PROCESS_RULES_B — Stores the process rule definitions evaluated during selection.
• PV_ENTY_SELECT_CRITERIA — Holds the selection criteria associated with each rule.
• PV_SEARCH_ATTR_VALUES and PV_SELECTED_ATTR_VALUES — Provide attribute-level search and selected-value data used to match entity attributes against rule criteria.
• PV_ENTITY_ATTR_MAPPINGS and PV_ENTITY_ROUTINGS — Define how entity attributes map to selection attributes and how entities route through processes.
• AS_LEADS_ALL — The leads/opportunity base table, queried to resolve the entity under evaluation.
• HZ_LOCATIONS, HZ_PARTY_SITES, and JTF_RS_RESOURCE_EXTNS — Provide party, site, location, and resource attribute data used in matching.
• FND_USER and DUAL — Support user context and simple lookups.
• DBMS_SQL, DBMS_UTILITY, and PLITBLM — Utility packages used for dynamic SQL construction, error handling, and PL/SQL table manipulation.

Usage Notes

PV_OPP_MATCH_PUB is typically invoked from Oracle Forms within the Trade Management module whenever an opportunity, lead, or partner must be evaluated against selection rules, and from concurrent programs or custom PL/SQL that need to apply rule-based qualification. The recommended calling pattern is to call CACHE_RULES to load rule data, invoke OPPORTUNITY_SELECTION or PARTNER_SELECTION for the entity in question, and call CLEAR_RULES_CACHE when rules are modified or the session ends. Because the package maintains session-level cached state in g_opp_selection_tab, callers should be aware that rule changes are not reflected until the cache is cleared.