Search Results bic_party_summ_u1

Overview

BIC.BIC_PARTY_SUMM is a summary table residing in the BIC (Business Intelligence Customer) schema within Oracle E-Business Suite, deployed across both 12.1.1 and 12.2.2 environments. It functions as a flattened, transposed reporting structure that reorganizes rows originating from a Customer Summary source table into columns, producing a matrix-style layout for organization-striped measures. The table is stored in the APPS_TS_SUMMARY tablespace, which is typical of pre-aggregated reporting structures designed to reduce query-time aggregation cost. The object carries FND design data registered under BIC.BIC_PARTY_SUMM, confirming it as a recognized EBS-delivered summary object with a VALID status.

Based on the foreign key structure and the presence of descriptive period-keyed measures, a Data Vault modeling heuristic suggests classifying this table as satellite-leaning. It behaves as a periodic snapshot satellite keyed by party, period, and operating unit, carrying measured facts rather than dependable hub or link service. Modelers treating this as a satellite should anchor it to a party hub (HZ_PARTIES) and an operating-unit dimension.

Key Information Stored

The table contains 90 documented columns spanning order, service, contract, satisfaction, and loyalty domains. The three-part unique index BIC_PARTY_SUMM_U1 covers PERIOD_START_DATE, PARTY_ID, and ORG_ID, establishing these as the composite business-key candidate for the summary grain. The most significant columns include:

• PARTY_ID — Foreign key to HZ_PARTIES, identifying the customer party being measured.
• PERIOD_START_DATE — The date anchor beginning the measurement period, forming the temporal component of the unique key.
• ORG_ID — Operating unit identifier that stripes measures across organizations.
• LOYALTY — Aggregated loyalty measure for the party.
• SATISFACTION — Overall satisfaction score, decomposed into sub-measures SF_BILLING, SF_CONTRACT, SF_QUALITY, SF_SERVICE, and SF_SHIPMENT.
• ORDER_AMT, ORDER_QTY, and ORDER_NUM — Core order value, volume, and count metrics.
• ORDER_QTY_CUML — Cumulative order quantity, supporting trend reporting.
• ORDER_RECENCY — Measures how recent the party's ordering activity is, useful for churn and recency scoring.
• PAYMENTS and ONTIME_PAYMENTS — Payment totals and timeliness.
• RETURNS, RETURN_QTY, and RETURN_BY_VALUE — Return volume and value trending.
• PROFITABILITY — Derived profitability measure for the party.
• MARKET_SEGMENT_ID — Segmentation key for grouping customers.

Standard Who columns (LAST_UPDATE_DATE, CREATION_DATE, LAST_UPDATED_BY, CREATED_BY) and concurrency columns (REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, LAST_UPDATE_LOGIN) are also present, along with SECURITY_GROUP_ID as a foreign key to FND_SECURITY_GROUPS for multi-tenant security filtering.

Common Use Cases and Queries

The primary use case is customer-centric performance reporting and dashboards for the BIC product suite. Reporting queries typically filter by PERIOD_START_DATE and ORG_ID, then aggregate across PARTY_ID. A representative pattern:

• SELECT party_id, period_start_date, org_id, order_amt, loyalty, satisfaction FROM bic.bic_party_summ WHERE org_id = :org_id AND period_start_date BETWEEN :start AND :end AND party_id = :party_id;
• Trend analysis joining consecutive periods on PARTY_ID and ORG_ID to compute measures such as ORDER_QTY_CUML deltas or satisfaction movement.
• Scorecard queries combining LOYALTY, SATISFACTION, ORDER_RECENCY, and PROFITABILITY to rank customers within a market segment.

Because the table pre-flattens data, its use avoids runtime pivoting that would otherwise be required against the source Customer Summary rows. It is well suited to concurrent-mancurrent report consumption where response time is critical.

Related Objects

The documented foreign keys and the shared party domain indicate strong dependencies on the following objects:

• HZ_PARTIES — Joined via BIC_PARTY_SUMM.PARTY_ID; the primary source of party identity and classification.
• FND_SECURITY_GROUPS — Joined via SECURITY_GROUP_ID for row-level security in a multi-tenant deployment.
• BIC Customer Summary source table — The upstream structure from which rows are transposed, feeding this table via the BIC collection programs.
• FND_USER — Referenced by LAST_UPDATED_BY and CREATED_BY for audit trail resolution.
• HZ_CUST_ACCOUNTS — Commonly joined through party-customer account relationships for account-level rollups.
• BIC_PARTY_SUMMARY and related BIC aggregate tables — Sibling structures that share the PERIOD_START_DATE, PARTY_ID, ORG_ID grain.