Results for “csr_rules_exploded_v”

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

Overview

CSR_RULES_EXPLODED_V is an APPS-owned database view within the CSR (Scheduler) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. The view presents scheduler rule definitions in a flattened, query-friendly form by combining the language-independent rule header stored in CSR_RULES_B with its translatable attributes from CSR_RULES_TL, while simultaneously decomposing the XML rule document (RULE_DOC) held on the base table into discrete, addressable columns.

In its native form, a scheduler rule is a single configurable entity whose behavior is governed by a serialized XMLTYPE document. That structure is efficient for the application layer but inconvenient for ad-hoc reporting, SQL joins, and external integration. CSR_RULES_EXPLODED_V solves this by applying EXTRACTVALUE against XPath expressions over the rule document, surfacing each scheduler parameter as a conventional scalar column. The view therefore serves as the primary read-only reporting surface for scheduler rule configuration in ETRM, and is commonly used by implementation teams to audit which planning constraints, resource selection flags, and time parameters are active for each rule.

The object holds a VALID status in the data dictionary and is treated as a system-level view; it should be queried rather than modified.

Underlying Base Objects

The documented referenced objects for this view are:

• CSR_RULES_B (SYNONYM) — the base table holding rule identity, ownership, and the RULE_DOC XMLTYPE payload.
• CSR_RULES_TL (SYNONYM) — the translation table supplying RULE_NAME and DESCRIPTION for the current language.
• WINDOW1532_COLL (TYPE) — a collection type referenced in the view's definition.
• STANDARD (PACKAGE) — the standard EBS package typically used for common view logic such as language resolution.
• XMLTYPE (TYPE) — the datatype of the source RULE_DOC column, against which the EXTRACTVALUE calls execute.

Functionally, the view is a join-driven projection: the _B table supplies the structural and XML-derived columns, while the _TL table supplies the user-visible name and description. Because RULE_DOC is stored as XMLTYPE, all extracted parameter columns are returned as text and typically require explicit numeric conversion before arithmetic use.

Key Columns

• RULE_ID, BASE_RULE_ID — unique identifier for the rule and the identifier of the base rule from which it was derived.
• OBJECT_VERSION_NUMBER — optimistic locking version, useful for detecting concurrent modification.
• RULE_NAME, DESCRIPTION — translated rule identity from CSR_RULES_TL.
• APPL_ID, RESP_ID, USER_ID — the application, responsibility, and user context in which the rule applies.
• TERR_ID, RESOURCE_TYPE, RESOURCE_ID — territory and resource scoping, defining which resources the rule governs.
• ENABLED_FLAG, RULE_RANK — whether the rule is active and the precedence used when multiple rules compete.
• SPPLANSCOPE, SPMAXPLANOPTIONS, SPMAXRESOURCES, SPMAXCALCTIME, SPMAXOVERTIME — planning scope and solver effort limits extracted from the rule document.
• SPWTPTHRESHOLD, SPENFORCEPLANWINDOW, SPCONSIDERSTANDBYSHIFTS — willingness-to-pay, plan window, and standby shift handling flags.
• SPSPARESMANDATORY, SPSPARESSOURCE — spare-parts enforcement and sourcing behavior.
• SPMINTASKLENGTH, SPDEFAULTSHIFTDURATION, SPDISTLASTCHILDEFFORT — task duration and effort distribution parameters.
• SPPICKCONTRACTRESOURCES, SPPICKIBRESOURCES, SPPICKTERRITORYRESOURCES, SPPICKSKILLEDRESOURCES — boolean resource-selection policies.
• SPAUTOSCHDEFAULTQUERY — the default auto-scheduling query assigned to the rule.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard EBS audit columns.

Common Use Cases and Queries

The view supports rule auditing, migration validation, and integration extracts. A typical audit lists enabled rules in rank order, exposing the effective solver limits and resource-picking policies as a single row per rule.

• Rule configuration audit: retrieve rule name, scope, and solver limits for enabled rules.
• Resource selection review: determine which rules permit contract, IB, territory, or skilled resources.
• Migration comparison: compare rule parameters between source and target instances.
• Integration extract: feed external scheduling engines with flat rule data.

Sample SQL — list enabled scheduler rules with their key planning parameters:

• SELECT rule_id, rule_name, rule_rank, enabled_flag, spplanscope, spmaxplanoptions, spmaxresources, spmaxcalctime, sppickterritoryresources FROM csr_rules_exploded_v WHERE enabled_flag = 'Y' ORDER BY rule_rank;

Sample SQL — identify rules with spare-part constraints active:

• SELECT rule_id, rule_name, spsparesmandatory, spsparessource FROM csr_rules_exploded_v WHERE spsparesmandatory = 'true';

To aggregate extraction, wrap the parameter columns in TO_NUMBER, for example TO_NUMBER(spmaxcalctime). Because the view joins translated data, include an NLS-aware predicate (for example, source_lang = USERENV('LANG')) when joining CSR_RULES_TL independently to avoid duplicate translation rows. All access should remain read-only, as the view is a reporting construct over the CSR scheduler rule repository.