Results for “process_status_code”

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

Overview

The AML_LEAD_PROCESS_REPT_V view, owned by the APPS schema in Oracle E-Business Suite (applicable to 12.1.1 and 12.2.2), is a reporting object within the AMS - Marketing product family. Its documented purpose is to return leads together with the processing rules applied to them. Structurally, it is a denormalized join that flattens the relationship between a sales lead, the entity rules applied against that lead by the Process Validation (PV) engine, the parent and child process rules, lead status, lead rank, and the territory/address attributes of the lead.

Because it consolidates lead header data, rule evaluation results, and lookup-driven code meanings into a single flat row, the view acts as a reporting and integration surface rather than a transactional one. It is not a base table and should not be used for DML. Instead, it supports ad hoc queries, custom reports, and extracts that need to answer questions such as which processing rule won for a given lead, what the evaluation outcome was, and how the lead was staged or ranked. The search term "process_status_code" maps directly to the view column PROCESS_STATUS_CODE, an alias of APP.PROCESS_STATUS drawn from PV_ENTITY_RULES_APPLIED.

Underlying Base Objects

The view is defined over a mix of base tables exposed through synonyms and other views. Documented referenced objects include:

• PV_ENTITY_RULES_APPLIED (synonym) — the central fact source, aliased APP. It holds the rule application records keyed by ENTITY and ENTITY_ID, and supplies PROCESS_STATUS, PROCESS_RULE_ID, PARENT_PROCESS_RULE_ID, ACTION_VALUE, ENTITY_DETAIL, WINNING_RULE_FLAG, LATEST_FLAG, and CREATION_DATE.
• PV_PROCESS_RULES_VL (view) — joined twice as PARENT and CHILD to resolve rule names, and PARENT.RANK supplying the precedence/ordering value.
• AS_SALES_LEADS (synonym) — aliased LEAD, providing lead identity, number, source promotion, rank, currency, channel, and description.
• AS_STATUSES_VL (view) — aliased STATUS, supplying the lead status code and its meaning.
• FND_LOOKUP_VALUES_VL (view) — joined twice as LK1 (lookup type PV_PROCESS_TYPE) and LK2 (lookup type PV_RULES_APPLIED_STATUS) to translate codes into user-facing meanings.
• AML_LEAD_PROCESSING_STAGES_V (view) — instantiated twice as STAGES and RANK to derive the stage/result and lead rank meanings.
• HZ_PARTY_SITES, HZ_LOCATIONS, and FND_TERRITORIES_VL — composed in an inline subquery aliased ADDRESS to resolve country and territory short name.
• FND_GLOBAL (package) — referenced for session/context values used in the view definition.

The join predicates are largely outer joins (the parent and child rule and address relationships use the (+) operator), which preserves rule-application rows even when a matching rule definition or address record is absent.

Key Columns

• SALES_LEAD_ID, LEAD_NUMBER — lead identity and human-readable number.
• PROCESS_STATUS_CODE — the raw process status code from PV_ENTITY_RULES_APPLIED (APP.PROCESS_STATUS).
• PROCESS_STATUS — the decoded meaning of that code via lookup type PV_RULES_APPLIED_STATUS.
• EVAL_CODE — the action value (ACTION_VALUE) representing the evaluation outcome.
• RULE_ID, PROCESS_RULE_ID, DOMAIN, SUB_RULE, PRECEDENCE — the parent rule, child rule, their names, and the precedence rank.
• STAGE, RULE_CODE, RESULT, LEAD_RANK — stage and rank meanings, with RULE_CODE mapping to the lookup code.
• STATUS, STATUS_CODE — lead status and its meaning.
• COUNTRY, COUNTRY_CODE — territory short name and country code from the address subquery.
• WINNING_RULE_FLAG, LATEST_FLAG, ACCEPT_FLAG — flags indicating rule election, recency, and lead acceptance.
• EVALUATION_DATE, CREATION_DATE — rule evaluation and lead creation timestamps.
• CURRENCY_CODE, CHANNEL_CODE, SOURCE_PROMOTION_ID, ENTITY_DETAIL, LEAD_NAME — supporting lead attributes.

Common Use Cases and Queries

A frequent requirement is to report the current processing status of leads by joining on the decoded status rather than the raw code. The following sample retrieves leads with their winning rule and status meaning:

• SELECT LEAD_NUMBER, PROCESS_STATUS_CODE, PROCESS_STATUS, DOMAIN, SUB_RULE, PRECEDENCE, EVAL_CODE FROM AML_LEAD_PROCESS_REPT_V WHERE WINNING_RULE_FLAG = 'Y' ORDER BY LEAD_NUMBER, PRECEDENCE;
• SELECT PROCESS_STATUS_CODE, PROCESS_STATUS, COUNT(*) FROM AML_LEAD_PROCESS_REPT_V GROUP BY PROCESS_STATUS_CODE, PROCESS_STATUS;
• SELECT LEAD_NUMBER, STAGE, LEAD_RANK, COUNTRY, EVALUATION_DATE FROM AML_LEAD_PROCESS_REPT_V WHERE PROCESS_STATUS_CODE = :p_status AND LATEST_FLAG = 'Y';

Because PROCESS_STATUS_CODE is the raw code and PROCESS_STATUS is its lookup meaning, reporting against the code is appropriate when filtering programmatically, while the meaning column is preferable for user-facing output. Filtering on LATEST_FLAG or WINNING_RULE_FLAG limits results to the most recent or elected rule application, preventing duplicate lead rows produced by the one-to-many rule relationship.