Search Results validity_rule_status

Overview

The PMITS_RECIPE_VALIDITY_RULE_V view is an APPS-owned database object in Oracle EBS 12.1.1 and 12.2.2, documented under the PMI – Process Manufacturing Intelligence product/module. Its documented purpose is to present Recipe Validity Rules — the rules that govern the conditions under which a specific recipe may be selected for a given item, organization, and use. The _V suffix indicates it is a view rather than a base table, and the PMITS_ prefix associates it with Process Manufacturing Intelligence reporting.

For users and integrators who search on validity_rule_status, this view is significant because it exposes VALIDITY_RULE_STATUS as a first-class column, alongside a decoded, human-readable STATUS value. This allows downstream reporting, OBIEE/BI Publisher extracts, and custom SQL to evaluate rule state without re-implementing the status decode logic. The view also enforces organization-level security: the row filter applies PMI_SECURITY_PKG.SHOW_RECORD(RVR.ORGN_CODE) so that only records whose organization is visible to the connecting user are returned. Records where ORGN_CODE IS NULL are always included.

The view is defined with STATUS = 'VALID' in the ETRM metadata, confirming it is a supported, compiled object within the APPS schema.

Underlying Base Objects

The view joins several Process Manufacturing and Inventory base objects. The documented referenced objects are:

• GMD_RECIPE_VALIDITY_RULES (SYNONYM) — the primary driving table, aliased RVR, holding the validity rule records.
• GMD_RECIPES_VL (VIEW) — aliased RC, supplying recipe number and version.
• GMD_STATUS (SYNONYM) — aliased GS, providing the decoded status meaning.
• GEM_LOOKUPS (VIEW) — aliased RCPU_LKP, resolving the recipe-use lookup (GMD_FORMULA_USE).
• FM_FORM_MST (SYNONYM) — aliased FM, the formula master, providing formula number and version.
• FM_ROUT_HDR (SYNONYM) — aliased RT, the routing header, outer-joined for routing number and version.
• IC_ITEM_MST (SYNONYM) — aliased IM, the item master, supplying item number and description.
• PMI_SECURITY_PKG (PACKAGE) — invoked in the WHERE clause to enforce organization visibility.

Rows are joined on RECIPE_ID (RVR to RC), FORMULA_ID (RC to FM), ROUTING_ID (RC to RT, outer join), and ITEM_ID (RVR to IM). The status and lookup joins constrain VALIDITY_RULE_STATUS = GS.STATUS_CODE and LOOKUP_TYPE = 'GMD_FORMULA_USE'.

Key Columns

• RECIPE_VALIDITY_RULE_ID — primary identifier for the validity rule.
• RECIPE_ID / RECIPE_NO / RECIPE_VERSION — the parent recipe and its number/version.
• ORGN_CODE — organization; may be NULL, in which case the rule is not organization-restricted.
• ITEM_ID / ITEM_NO / ITEM_DESC1 — the item to which the rule applies.
• RECIPE_USE — decoded recipe-use meaning from GEM_LOOKUPS.
• PREFERENCE — priority of the rule relative to competing rules.
• START_DATE / END_DATE — the effective date range of the rule.
• MIN_QTY / MAX_QTY / STD_QTY / ITEM_UM — quantity thresholds and unit of measure expressed in item UOM.
• INV_MIN_QTY / INV_MAX_QTY — quantity thresholds expressed in inventory UOM.
• VALIDITY_RULE_STATUS — the raw status code, central to searches on validity_rule_status.
• STATUS — the decoded status meaning from GMD_STATUS.
• LAB_TYPE / TEXT_CODE / DELETE_MARK — supporting attributes; DELETE_MARK flags logically deleted rows.
• FORMULA_NO / FORMULA_VERS / ROUTING_NO / ROUTING_VERS — associated formula and routing identifiers.

Common Use Cases and Queries

Typical uses include listing active validity rules for an item/organization, filtering rules by status, and reconciling recipe availability in reporting extracts. Because the view already decodes status, reporting tools can select STATUS directly.

Example — list validity rules for a given status:

• SELECT RECIPE_NO, RECIPE_VERSION, ITEM_NO, ORGN_CODE, RECIPE_USE, PREFERENCE, START_DATE, END_DATE, VALIDITY_RULE_STATUS, STATUS FROM PMITS_RECIPE_VALIDITY_RULE_V WHERE VALIDITY_RULE_STATUS = :status AND NVL(DELETE_MARK,0) = 0;

Example — active rules for an item and organization:

• SELECT RECIPE_NO, ITEM_NO, MIN_QTY, MAX_QTY, STD_QTY, ITEM_UM FROM PMITS_RECIPE_VALIDITY_RULE_V WHERE ITEM_NO = :item AND ORGN_CODE = :org AND TRUNC(SYSDATE) BETWEEN START_DATE AND END_DATE;

Example — count of rules by decoded status:

• SELECT STATUS, COUNT(*) FROM PMITS_RECIPE_VALIDITY_RULE_V GROUP BY STATUS;

Because organization security is embedded in the view via PMI_SECURITY_PKG.SHOW_RECORD, queries automatically respect the connecting user's organization access. Rows with a NULL ORGN_CODE are global and always returned. This makes the view safe for self-service reporting without additional organization predicates, though explicit ORGN_CODE filters remain advisable for performance and clarity.