Search Results bil_fctv_cs_incidents

Overview

BIL_FCTV_CS_INCIDENTS is a reporting view within the Oracle E-Business Suite Sales Intelligence (BIL) product family. It exposes quantitative measures associated with customer service requests (service incidents), providing a denormalized, read-only projection of incident data intended for use by Sales Intelligence analytics, star-schema fact loads, and downstream reporting. The view is defined over the Service (CS) transactional tables and surfaces both descriptive attributes and the columns required to build fact and dimension records for service-request analysis.

In the Oracle EBS 12.1.1 and 12.2.2 environments, this object functions as a controlled read interface rather than a base transactional object. The view text carries the WITH READ ONLY clause, which explicitly prevents DML through the view and reinforces its role as a query and extraction surface. As documented in the ETRM metadata, the view is not implemented in the supplied database, meaning it should be treated as a documented definition whose presence depends on the relevant BIL/CS installation and patching level.

Underlying Base Objects

The view is defined directly over CS_INCIDENTS_ALL, the multi-organization base table that stores service request header information in Oracle Service. The ETRM metadata documents no additional referenced base objects, so CS_INCIDENTS_ALL is the sole documented source. Because CS_INCIDENTS_ALL is an _ALL table, it is partitioned by ORG_ID, and the view inherits organizational partitioning by carrying ORG_ID as a projected column and applying an operational-unit filter in its WHERE clause.

The filter employs USERENV('CLIENT_INFO') to retrieve the current organization context set by the application, extracting an identifier via SUBSTRB and DECODE. When no valid client information is present, the filter resolves to the sentinel value -99, using nested NVL and TO_NUMBER logic. This design restricts rows to the caller's current operating unit and ensures organizational data isolation is preserved through the reporting layer.

Key Columns

• INCIDENT_ID — Primary identifier of the service incident; the natural key for fact-level joins.
• CREATION_DATE, INCIDENT_DATE, EXPECTED_RESOLUTION_DATE, ACTUAL_RESOLUTION_DATE, CLOSE_DATE — Date measures supporting cycle-time, aging, and resolution-performance analytics.
• INVENTORY_ITEM_ID, CUSTOMER_PRODUCT_ID, CURRENT_SERIAL_NUMBER — Product and installed-base context for the incident.
• INCIDENT_SEVERITY_ID, INCIDENT_STATUS_ID, INCIDENT_URGENCY_ID, INCIDENT_TYPE_ID — Classification dimensions used to categorize and group incidents.
• EMPLOYEE_ID, INCIDENT_OWNER_ID — Assignment and ownership references for workload and resource analysis.
• ACCOUNT_ID, CUSTOMER_ID, CUSTOMER_NUMBER, BILL_TO_SITE_USE_ID, BILL_TO_CONTACT_ID, TERRITORY_ID — Customer, site, contact, and territory context.
• ORG_ID — Operating unit identifier; drives the view's organization filter.
• RECORD_IS_VALID_FLAG — Flag indicating whether the underlying record is currently valid, typically used to exclude logically deleted or superseded records.
• SYSTEM_ID — Source system identifier for records originating from external or integrated systems.

Common Use Cases and Queries

Typical scenarios include feeding service-incident fact tables in the Sales Intelligence warehouse, measuring response and resolution performance, and reporting incident volumes by customer, product, territory, and severity. Analysts frequently filter on RECORD_IS_VALID_FLAG to restrict results to valid records, and on SYSTEM_ID to isolate records from specific source systems.

A representative query counts valid incidents per status for a given period:

• SELECT INCIDENT_STATUS_ID, COUNT(*) FROM BIL_FCTV_CS_INCIDENTS WHERE RECORD_IS_VALID_FLAG = 'Y' AND INCIDENT_DATE BETWEEN :start_date AND :end_date GROUP BY INCIDENT_STATUS_ID;

A resolution-performance query computes elapsed resolution time:

• SELECT INCIDENT_ID, (ACTUAL_RESOLUTION_DATE - INCIDENT_DATE) AS RESOLUTION_DAYS FROM BIL_FCTV_CS_INCIDENTS WHERE ACTUAL_RESOLUTION_DATE IS NOT NULL AND RECORD_IS_VALID_FLAG = 'Y';

Because the view is read-only and organization-filtered, callers must set the correct operating unit context so that USERENV('CLIENT_INFO') resolves properly and returns the intended set of incidents.