Search Results geography_fk

Overview

BIC_PMF_SUMMARY_V is a reporting view associated with the Oracle E-Business Suite Customer Intelligence module (product code BIC). The BIC product family is documented as obsolete in Oracle EBS 12.1.1 and 12.2.2, and this view is not implemented in the current database. Its purpose, as reflected in the view definition, is to expose customer summary metrics joined to customer account, party, market segmentation, and GL period attributes in a single flattened row set suitable for reporting, analytics, and downstream integration.

The view consolidates measures stored in BIC_CUSTOMER_SUMMARY_ALL with descriptive context from HZ_CUST_ACCOUNTS, HZ_PARTIES, AMS_PARTY_MARKET_SEGMENTS, and GL_PERIODS. The result is a denormalized structure in which each row represents a measure value for a customer for a given period, enriched with account, party, demographic, and calendar attributes. Because the view is defined WITH READ ONLY, it is strictly a query object and cannot be used as a DML target.

Underlying Base Objects

The documented base objects referenced by the view are:

• BIC_CUSTOMER_SUMMARY_ALL — the fact-style table supplying S.PERIOD_START_DATE, S.VALUE, S.CUSTOMER_ID, S.BUCKET_ID, S.MEASURE_ID, S.MEASURE_CODE, and S.ORG_ID.
• HZ_CUST_ACCOUNTS — supplies account-level attributes including ACCOUNT_NUMBER, CUST_ACCOUNT_ID, PARTY_ID, ACCOUNT_NAME, and ACCOUNT_ESTABLISHED_DATE.
• HZ_PARTIES — supplies party-level location and demographic attributes: COUNTRY, STATE, and CITY.
• AMS_PARTY_MARKET_SEGMENTS — supplies MARKET_SEGMENT_ID and is joined with an outer join (A.PARTY_ID (+)) filtered on MARKET_SEGMENT_FLAG = 'Y'.
• GL_PERIODS — supplies calendar attributes including PERIOD_SET_NAME, PERIOD_NAME, START_DATE, END_DATE, PERIOD_YEAR, PERIOD_NUM, QUARTER_NUM, YEAR_START_DATE, and QUARTER_START_DATE.

Joins are established on PARTY_ID between HZ_CUST_ACCOUNTS, HZ_PARTIES, and AMS_PARTY_MARKET_SEGMENTS, on CUST_ACCOUNT_ID between HZ_CUST_ACCOUNTS and BIC_CUSTOMER_SUMMARY_ALL, and via a date range condition placing S.PERIOD_START_DATE between GL_PERIODS START_DATE and END_DATE.

Key Columns

• PERIOD_START_DATE, VALUE, BUCKET_ID, MEASURE_ID, MEASURE_CODE — the core measure facts and their temporal and dimensional identifiers.
• CUSTOMER_FK, CUSTOMER_ID, PARTY_ID, ACCOUNT_NUMBER, ACCOUNT_NAME — customer and account identity columns. ACQUIRED_DATE is mapped from ACCOUNT_ESTABLISHED_DATE.
• ORG_FK, ORG_ID, ORG_NAME, CUST_ORG_ID — operating unit context; ORG_NAME, CUST_ORG_ID are exposed as NULL placeholders.
• MEASURE_NAME, MEASURE_DESCRIPTION, MEASURE_WEIGHT, MEASURE_TIME_CODE — placeholders returned as NULL, intended for measure metadata enrichment.
• CUSTOMER_CATEGORY_FK, MARKET_SEGMENT_FK — category and market segmentation identifiers.
• GEOGRAPHY_FK — exposed as a NULL placeholder in this view. It is not populated from a physical geography dimension; geographic detail is instead provided via COUNTRY, STATE, and CITY sourced from HZ_PARTIES.
• PERIOD_SET_NAME, PERIOD_NAME, START_DATE, END_DATE, PERIOD_YEAR, PERIOD_NUM, QUARTER_NUM, YEAR_START_DATE, QUARTER_START_DATE — calendar dimensions from GL_PERIODS for time-based reporting.

Common Use Cases and Queries

Because GEOGRAPHY_FK is a NULL placeholder, queries seeking geographic grouping must rely on COUNTRY, STATE, and CITY. Since the view is not implemented in current 12.1.1/12.2.2 databases and BIC is obsolete, it survives principally as a legacy reporting artifact. Representative query:

SELECT customer_id, account_number, country, state, city, measure_code, period_name, value FROM bic_pmf_summary_v WHERE period_year = 2010 AND org_id = :org_id ORDER BY account_number, period_name;

Aggregations by market segment or calendar quarter similarly use MARKET_SEGMENT_FK and PERIOD_YEAR/QUARTER_NUM. Any consumer must treat NULL placeholder columns (GEOGRAPHY_FK, ORG_NAME, MEASURE_NAME, and related) as unavailable and source that context elsewhere.