Search Results parent_sales_group_id

Overview

BIL_BI_PIPE_G_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite. Although its product affiliation is registered under FND — Application Object Library, the object naming and column structure place it within the Oracle Sales (BIL) business intelligence layer, where it supports pipeline and forecast reporting against sales group hierarchies. The suffix "_G" denotes the grouped or aggregated variant of the underlying pipeline materialized view, indicating that this object is intended to expose rolled-up pipeline figures rather than transaction-level detail.

The view presents pipeline amounts and open (unforecasted or not-yet-booked) amounts across multiple time buckets, with each metric available in both an absolute and a secondary (suffixed "_S") form. All rows are sourced from the BIL_BI_PIPE_G_MV materialized view, and a UNION ALL consolidates two identical column projections from that same source. In Oracle EBS 12.1.1 and 12.2.2, this view functions as a reporting convenience layer: consumers can query a stable, named view rather than the underlying materialized view directly, insulating them from any future change in the materialized view's physical organization.

Underlying Base Objects

The ETRM metadata documents no base tables beyond the view text itself. The view is defined exclusively as a UNION ALL over BIL_BI_PIPE_G_MV, the grouped materialized view that stores pre-aggregated pipeline data. No joins, filters, or aggregation clauses are present in the view definition; each metric column is passed through unchanged from the materialized view.

Because the two branches of the UNION ALL select identical column lists from the same source, the view performs no row transformation and no deduplication. This pattern is typical of BI-layer views that are intended to present a flattened projection without altering the materialized view contents. Any dependency on dimension or fact tables such as sales group, sales representative, product category, or item resides beneath the materialized view and is not visible in this object's definition. Consequently, the view's freshness is governed entirely by the refresh schedule of BIL_BI_PIPE_G_MV.

Key Columns

The column set is organized around four axes: grouping, entity, time bucket, and measure. Grouping and entity columns identify the aggregation context: GRP_TOTAL_FLAG indicates whether the row represents a group total; PARENT_SALES_GROUP_ID and SALES_GROUP_ID locate the row within the sales group hierarchy; SALESREP_ID, PRODUCT_CATEGORY_ID, and ITEM_ID identify the associated sales representative, category, and item; and SNAP_DATE records the snapshot date for which the figures were captured.

Measure columns fall into three families. PIPELINE_AMT_* columns hold pipeline value by day, week, period, quarter, and year. WTD_PIPELINE_AMT_* columns hold week-to-date pipeline values across the same five buckets. OPEN_AMT_* columns hold open amounts across the same five buckets. Each of these fifteen base measures is repeated with an "_S" suffix (for example, PIPELINE_AMT_DAY_S and OPEN_AMT_PERIOD_S), yielding a parallel set of secondary values that typically represent a converted, secured, or alternative-currency view of the corresponding primary measure. Queries should confirm the semantic distinction between the base and "_S" measures against local configuration before reporting on them.

Common Use Cases and Queries

This view is most often used for sales pipeline dashboards, forecast roll-ups by sales group, and point-in-time snapshot comparisons. A typical selection retrieves the latest snapshot for a given sales representative:

• SELECT sales_group_id, salesrep_id, snap_date, pipeline_amt_period, open_amt_period FROM apps.bil_bi_pipe_g_v WHERE snap_date = (SELECT MAX(snap_date) FROM apps.bil_bi_pipe_g_v) AND salesrep_id = :p_salesrep_id;
• SELECT grp_total_flag, sales_group_id, SUM(pipeline_amt_quarter) FROM apps.bil_bi_pipe_g_v WHERE snap_date BETWEEN :p_from AND :p_to GROUP BY grp_total_flag, sales_group_id;
• SELECT item_id, product_category_id, wtd_pipeline_amt_week, wtd_pipeline_amt_week_s FROM apps.bil_bi_pipe_g_v WHERE snap_date = :p_snap_date ORDER BY wtd_pipeline_amt_week DESC;

Because the underlying materialized view must be refreshed before new data appears, reports that require current-day figures should check the materialized view's last refresh time. Queries against this view are read-only and inherit the APPS schema grants, so access should be provisioned through standard responsibility and menu configuration rather than direct table grants.