Results for “date_of_last_service_call”

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

Overview

ICX_PANEL_SUMMARY is a denormalized summary table in the ICX schema (Oracle iProcurement) that stores pre-aggregated customer activity metrics for display on iProcurement and Oracle E-Business Suite procurement dashboards and customer panels. Rather than executing real-time queries against transactional tables such as SO_HEADERS_ALL, RA_CUSTOMERS, and service request tables, the application reads consolidated figures from this table, which is refreshed periodically by concurrent programs. This design improves panel rendering performance in high-volume environments.

In Oracle EBS 12.1.1 and 12.2.2, the table is fully validated and documented, with 55 columns. Each row represents a snapshot of a single customer's commercial relationship within a specific operating unit, keyed by ORG_ID, CUSTOMER_ID, and DATA_AS_OF_DATE. The heuristic Data Vault classification for this object is a link, reflecting its role as a relationship record connecting a customer (party/site dimension) to an operational context and order history rather than serving as a pure reference hub or an insert-only satellite. This classification is a modeling suggestion derived from the foreign-key structure.

Key Information Stored

The primary key ICX_PANEL_SUMMARY_PK comprises ORG_ID, CUSTOMER_ID, and DATA_AS_OF_DATE, and a functionally equivalent unique index ICX_PANEL_SUMMARY_U1 exists on the same three columns, making them the business-key candidates. The most operationally significant columns include:

• ORG_ID, CUSTOMER_ID, DATA_AS_OF_DATE — the composite business key identifying the operating unit, the customer, and the snapshot date.
• LAST_ORDER_HEADER_ID — foreign key to SO_HEADERS_ALL, pointing to the customer's most recent order header.
• ORDERS_LAST_WEEK, ORDERS_LAST_MONTH, ORDERS_LAST_3_MONTHS, ORDERS_LAST_6_MONTHS, ORDERS_LAST_12_MONTHS — order counts across trailing windows.
• Orders_AMOUNT variants (same five windows) — corresponding monetary totals.
• CANCELLED_ORDERS_3_MONTHS / 6_MONTHS and CANCELLED_AMOUNT_3_MONTHS / 6_MONTHS — cancellation frequency and value.
• RMA_3_MONTHS / 6_MONTHS and RMA_AMOUNT_3_MONTHS / 6_MONTHS — return volumes and amounts.
• SHIPMENTS_OPEN, SHIPMENTS_LATE — fulfillment pipeline indicators.
• CREDIT_RATING, RISK_CODE, LATE_PAYMENTS_3_MONTHS, LATE_PAYMENTS_6_MONTHS, AMOUNT_DUE — credit and receivables exposure metrics.
• SERVICE_CALLS_3_MONTHS, DATE_OF_LAST_SERVICE_CALL, SITE_ID_LAST_SERVICE_CALL, SERVICE_AMOUNT_6_MONTHS / 12_MONTHS, SERVICE_ORDERS_6_MONTHS / 12_MONTHS — service activity summary.
• CURRENCY_CODE and SAME_SUPPLY_CHAIN_FLAG — context flags for interpretation of amounts.

Columns ATTRIBUTE1 through ATTRIBUTE12 provide extensibility for customer-specific or localized reporting requirements.

Common Use Cases and Queries

Typical usage centers on customer profile panels and account-management reporting. A representative query retrieves the latest snapshot per customer:

• Fetch the most recent metrics for a customer in an operating unit: filter on ORG_ID and CUSTOMER_ID and order by DATA_AS_OF_DATE descending, selecting ORDERS_LAST_12_MONTHS, AMOUNT_DUE, and CREDIT_RATING.
• Build a credit-risk watchlist by filtering AMOUNT_DUE above a threshold and LATE_PAYMENTS_6_MONTHS greater than zero.
• Identify at-risk accounts by joining CUSTOMER_ID to RA_CUSTOMERS and comparing ORDERS_LAST_3_MONTHS against ORDERS_LAST_12_MONTHS.
• Trend analysis per ORG_ID by grouping DATA_AS_OF_DATE and summing orders and RMA values.
• Drill-down from a panel: use LAST_ORDER_HEADER_ID to join SO_HEADERS_ALL and retrieve order detail for the summarized customer.
• Service exposure reporting using SERVICE_ORDERS_6_MONTHS and SERVICE_AMOUNT_6_MONTHS.

Because the data is a periodic snapshot, reporting queries should always constrain DATA_AS_OF_DATE to the relevant as-of date to avoid double counting.

Related Objects

• RA_CUSTOMERS — joined via CUSTOMER_ID; the master customer record supplying name and party context.
• SO_HEADERS_ALL — joined via LAST_ORDER_HEADER_ID; provides order header detail for the most recent order.
• ICX_PANEL_SUMMARY_PK — the primary-key constraint enforcing uniqueness of ORG_ID, CUSTOMER_ID, and DATA_AS_OF_DATE.
• ICX_PANEL_SUMMARY_U1 — the unique index supporting the business-key columns and query performance.
• Order, return, and service-request source tables that feed the concurrent refresh process referenced through the summary metrics.

The table therefore functions as a performance-oriented cache linking customer, order, and service information into a single reviewable panel record.