Results for “opp_open_status_flag”
50+ results
AI-generated from documented ETRM metadata — verify critical details on the linked pages.
Overview
PV_AMS_BIN_MV is a materialized-view-backed data object owned by APPS within the Oracle E-Business Suite Partner Management (PV) module. It is not implemented as a physical table in the standard ETRM 12.1.1 environment, a status consistent with its designation as an Oracle proprietary, confidential structure used internally by the application rather than as a documented transactional or interface entity.
Functionally, PV_AMS_BIN_MV aggregates promotion, lead, and opportunity metrics into summarized "bins." Its column set suggests it is used to store rolled-up counts and monetary totals segmented by period, currency, opportunity status, and win/loss outcome, most likely to support channel or campaign performance reporting within Partner Management and Marketing.
Under the heuristic Data Vault classification mined from its foreign key structure, PV_AMS_BIN_MV is identified as standalone. In Data Vault modeling terms, this suggests it would typically be treated as a standalone satellite or aggregate (fact-style) structure rather than a strict hub or link, since it carries descriptive measures and an outbound reference to a lead entity but does not form part of a broader normalized hub-and-link chain. This classification is a modeling suggestion only; the object does not form part of a documented Data Vault implementation.
Key Information Stored
The documented physical schema under ETRM 12.1.1 comprises ten columns. The most significant are summarized below.
- LEAD_ID — The only documented foreign key, referencing AS_LEADS_ALL. This is the principal business-key candidate that ties each aggregated row back to an individual lead.
- PARENT_RESOURCE_ID — Identifies the parent resource (typically a partner, channel, or organizational resource) associated with the aggregated bin.
- SOURCE_PROMOTION_ID — Identifies the source promotion driving the underlying activity, enabling promotion-level attribution.
- CNT — The count of underlying records rolled into the bin.
- TOTAL_AMOUNT — The summed monetary value for the bin, most meaningful when read alongside CURRENCY_CODE.
- WIN_LOSS_INDICATOR — Classifies the opportunity outcome as won or lost, supporting conversion analysis.
- OPP_OPEN_STATUS_FLAG — Indicates whether the related opportunities remain open, allowing open-versus-closed segmentation.
- CURRENCY_CODE — The currency in which TOTAL_AMOUNT is expressed; no code-to-code conversion is implied.
- PERIOD_TYPE and PERIOD_NAME — Define the accounting or reporting period granularity and the specific period label for the bin.
No separate surrogate primary key column is documented in the ETRM metadata. Where a unique index exists, it is most plausibly a composite of the dimensional keys (for example LEAD_ID, PARENT_RESOURCE_ID, SOURCE_PROMOTION_ID, PERIOD_NAME), which behave as the effective business keys. Because this is a materialized view, row identity is derived from the aggregation grain rather than from a single sequence-generated key.
Common Use Cases and Queries
The most common scenario is promotion and channel performance reporting. A typical pattern aggregates counts and amounts by promotion and period:
- SELECT SOURCE_PROMOTION_ID, PERIOD_NAME, SUM(CNT), SUM(TOTAL_AMOUNT) FROM PV_AMS_BIN_MV GROUP BY SOURCE_PROMOTION_ID, PERIOD_NAME;
- Filtering WIN_LOSS_INDICATOR to derive conversion rates by parent resource: SELECT PARENT_RESOURCE_ID, SUM(CASE WHEN WIN_LOSS_INDICATOR='W' THEN CNT ELSE 0 END) / SUM(CNT) FROM PV_AMS_BIN_MV GROUP BY PARENT_RESOURCE_ID;
- Segmenting by OPP_OPEN_STATUS_FLAG to separate pipeline from closed business.
- Joining to AS_LEADS_ALL on LEAD_ID to enrich aggregated figures with lead-level attributes.
Because the object is backed by a materialized view, queries reflect a snapshot that depends on the refresh schedule configured for the view. Any reporting built on top of it should account for that latency. Absence from the standard implementation also means custom BI solutions must verify refresh behavior and object existence before querying.
Related Objects
The following objects are most significant in relation to PV_AMS_BIN_MV.
- AS_LEADS_ALL — The only documented foreign key target, joined via PV_AMS_BIN_MV.LEAD_ID = AS_LEADS_ALL.LEAD_ID. This is the primary integration point for lead-level enrichment.
- PV_AMS_BIN_MV master or base objects — The underlying base tables from which the materialized view is refreshed; these supply the aggregated rows.
- Promotion and campaign objects — Referenced through SOURCE_PROMOTION_ID, providing promotion attribution.
- Resource and partner objects — Referenced through PARENT_RESOURCE_ID, supplying channel or partner context.
- Period and currency setup objects — Supporting PERIOD_TYPE, PERIOD_NAME, and CURRENCY_CODE interpretation.
- Opportunity-related objects — Supporting WIN_LOSS_INDICATOR and OPP_OPEN_STATUS_FLAG semantics.
Given the limited documented schema and the object's non-implemented status, dependency analysis should be confirmed against the actual partner management data model in the target release before relying on any join path other than the documented LEAD_ID relationship.
-
View: BIL_DIMV_OPTY_STATUSES 12.1.1
Opportunity Statuses view
APPS.BIL_DIMV_OPTY_STATUSES·↳ AS_STATUSES_B·↳ AS_STATUSES_TL·↳ FND_GLOBAL·Explore BIL module →
-
Table: PV_CHN_BIN_SUM_MV 12.1.1
-
Table: ASF_OPP_CMP_MV 12.1.1
-
Table: PV_SLBINAMT_SUM_MV 12.1.1
-
Table: PV_PRD_BIN_SUM_MV 12.1.1
-
Staging table for sales open and pipeline amounts
-
View: AS_STATUSES_VL 12.1.1
MLS view to join as_statuses_b and as_statuses_tl
APPS.AS_STATUSES_VL·↳ AS_STATUSES_B·↳ AS_STATUSES_TL·Explore AS module →
-
View: AS_TAP_OPPORTUNITIES_V 12.2.2
For TAP use
APPS.AS_TAP_OPPORTUNITIES_V·↳ AS_LEADS_ALL·↳ AS_STATUSES_B·↳ JTF_TAE_1001_ACCOUNT_TRANS·Explore AS module →
-
Hold three types of status, opportunity, lead and quote
-
Hold three types of status, opportunity, lead and quote
-
View: AS_TAP_LEADS_V 12.1.1
For TAP use
APPS.AS_TAP_LEADS_V·↳ AS_SALES_LEADS·↳ AS_STATUSES_B·↳ JTF_TAE_1001_ACCOUNT_TRANS·Explore AS module →
-
View: AS_TAP_LEADS_V 12.2.2
For TAP use
APPS.AS_TAP_LEADS_V·↳ AS_SALES_LEADS·↳ AS_STATUSES_B·↳ JTF_TAE_1001_ACCOUNT_TRANS·Explore AS module →
-
Table: PV_AMS_BIN_SUM_MV 12.1.1
-
Table: ASF_OPP_CHN_MV 12.1.1
-
Staging table used to populate fact table bil_bi_opdtl_f
-
View: ASF_FRCST_SUM_REPRT_V 12.2.2
APPS.ASF_FRCST_SUM_REPRT_V·↳ AS_MC_SALES_CREDITS_DEN·↳ AS_OPP_WORKSHEET_LINES·↳ AS_SALES_CREDITS_DENORM·Explore ASF module →
-
View: ASF_FRCST_SUM_REPRT_V 12.1.1
APPS.ASF_FRCST_SUM_REPRT_V·↳ AS_MC_SALES_CREDITS_DEN·↳ AS_OPP_WORKSHEET_LINES·↳ AS_SALES_CREDITS_DENORM·Explore ASF module →
-
APPS.AST_SALES_LEADS_REL_BALI_V·↳ AS_LOOKUPS·↳ AS_SALES_LEADS·↳ AS_SALES_LEAD_CONTACTS·Explore AST module →
-
APPS.AST_SALES_LEADS_REL_BALI_V·↳ AS_LOOKUPS·↳ AS_SALES_LEADS·↳ AS_SALES_LEAD_CONTACTS·Explore AST module →
-
View: AS_TAP_OPPORTUNITIES_V 12.1.1
For TAP use
APPS.AS_TAP_OPPORTUNITIES_V·↳ AS_LEADS_ALL·↳ AS_STATUSES_B·↳ JTF_TAE_1001_ACCOUNT_TRANS·Explore AS module →
-
View: AS_STATUSES_VL 12.2.2
MLS view to join as_statuses_b and as_statuses_tl
APPS.AS_STATUSES_VL·↳ AS_STATUSES_B·↳ AS_STATUSES_TL·Explore AS module →
-
View: AST_SALES_LEADS_V 12.2.2
APPS.AST_SALES_LEADS_V·↳ AMS_SOURCE_CODES·↳ AS_LOOKUPS·↳ AS_PARTY_ADDRESSES_V·Explore AST module →
-
Denormalized table to store opportunity data denormalized from AS_LEADS_ALL, AS_LEAD_LINES_ALL, AS_SALES_CREDITS, AS_SALES_STAGE_ALL, AS_INTEREST_TYPES_ALL, AS_INTEREST_CODES_ALL, HZ_PARTIES, AS_SALES_GROUPS, PER_ALL_PEOPLR_F
-
View: AST_SALES_LEADS_BALI_V 12.2.2
APPS.AST_SALES_LEADS_BALI_V·↳ AS_LOOKUPS·↳ AS_SALES_LEADS·↳ AS_SALES_LEAD_RANKS_TL·Explore AST module →
-
View: AST_SALES_LEADS_BALI_V 12.1.1
APPS.AST_SALES_LEADS_BALI_V·↳ AS_LOOKUPS·↳ AS_SALES_LEADS·↳ AS_SALES_LEAD_RANKS_TL·Explore AST module →
-
View: AST_SALES_LEADS_EBC_V 12.1.1
APPS.AST_SALES_LEADS_EBC_V·↳ AMS_SOURCE_CODES·↳ AS_LOOKUPS·↳ AS_SALES_LEADS·Explore AST module →
-
View: AST_SALES_LEADS_EBC_V 12.2.2
APPS.AST_SALES_LEADS_EBC_V·↳ AMS_SOURCE_CODES·↳ AS_LOOKUPS·↳ AS_SALES_LEADS·Explore AST module →
-
View: AST_SALES_LEADS_V 12.1.1
APPS.AST_SALES_LEADS_V·↳ AMS_SOURCE_CODES·↳ AS_LOOKUPS·↳ AS_PARTY_ADDRESSES_V·Explore AST module →
-
Denormalized table to store opportunity data denormalized from AS_LEADS_ALL, AS_LEAD_LINES_ALL, AS_SALES_CREDITS, AS_SALES_STAGE_ALL, AS_INTEREST_TYPES_ALL, AS_INTEREST_CODES_ALL, HZ_PARTIES, AS_SALES_GROUPS, PER_ALL_PEOPLR_F
-
VIEW: OSM.AS_STATUSES_B# 12.2.2
-
TABLE: APPS.PV_SLBIN_SUM_MV 12.1.1
-
TABLE: APPS.ASF_OPP_CHN_MV 12.1.1