Search Results bic_fctv_sr_escalated

Overview

BIC_FCTV_SR_ESCALATED is a reporting view defined within the BIC – Customer Intelligence product family in Oracle EBS. Customer Intelligence was Oracle's analytical layer built on top of the Service (CS) and CRM foundation schemas to deliver dimensional, pre-joined data sets for business intelligence reporting on service requests, customers, and time. This particular view isolates escalated service requests, presenting them in a star-schema-friendly shape with foreign-key columns already renamed (suffixed _FK) for consumption by BIC fact and dimension consumers.

The ETRM metadata for this object explicitly flags the product as obsolete and records "Not implemented in this database." This is a critical qualifier: BIC_FCTV_SR_ESCALATED shipped as part of the Customer Intelligence seed/definition set in earlier 11i/12.1.1 releases, but in a 12.1.1 or 12.2.2 instance it will only exist if Customer Intelligence was installed and its views were deployed. In most modern EBS environments the view is absent, and the underlying BIC dimension views it depends on (BIC_DIMV_TIME, BIC_DIMV_CUSTOMERS) may themselves be missing. It is therefore best understood as a historical artifact of the Customer Intelligence data model rather than a supported integration object.

Underlying Base Objects

The view is defined over the following objects, all joined in a single SELECT with WITH READ ONLY:

• CS_INCIDENTS_ALL_B (INC) – the core service request / incident table; supplies identity, owner, status, severity, type, dates, and ORG_ID.
• CS_INCIDENT_STATUSES (STAT) – resolves status flags, notably CLOSE_FLAG.
• CS_INCIDENT_SEVERITIES (ISEV) – provides severity name and description.
• CS_INCIDENT_TYPES (ITYP) – provides incident type name and description.
• JTF_RS_RESOURCE_EXTNS (PF) – owner/resource details, outer-joined ((+)) on RESOURCE_ID, yielding employee start date.
• JTF_TASK_REFERENCES_B (JTR) – links the incident to a task reference with OBJECT_TYPE_CODE = 'SR' and REFERENCE_CODE = 'ESC', which is the escalation marker.
• JTF_TASKS_B (JTB) – the escalated task, filtered to TASK_TYPE_ID = 22.
• BIC_DIMV_TIME (DT) – the BIC time dimension; the task's ACTUAL_START_DATE is constrained to fall between the period start and end dates.
• BIC_DIMV_CUSTOMERS (DC) – the BIC customer dimension, joined to the incident on CUSTOMER_ID = PARTY_ID.

The escalation filter is the defining characteristic: an incident appears here only when it has a JTF task reference flagged ESC of task type 22.

Key Columns

Among the columns most relevant to the search term incident_type_fk:

• INCIDENT_TYPE_FK – sourced from INC.INCIDENT_TYPE_ID; the foreign-key surrogate into CS_INCIDENT_TYPES. The view also exposes the descriptive INCIDENT_TYPE (ITYP.NAME) and INC_TYPE_DESC (ITYP.DESCRIPTION), so consumers can use either the key or the label.
• INCIDENT_ID – primary identifier of the service request.
• INCIDENT_OWNER_FK – owner resource ID; LAST_NAME, FIRST_NAME, EMPLOYEE_NAME are returned as NULL literals, while EMPLOYEE_START_DATE comes from the resource extension.
• INCIDENT_STATUS_FK / INCIDENT_SEVERITY_FK – surrogate keys to status and severity, alongside CLOSE_FLAG, INCIDENT_SEVERITY, and SEVERITY_DESC.
• PARTY_ID, COUNTRY, CITY, STATE – customer-dimension attributes.
• CUSTOMER_CATEGORY_FK, MARKET_SEGMENT_FK – BIC customer classification keys.
• Time attributes – ACT_PERIOD_NAME, ACT_PERIOD_START_DATE, ACT_PERIOD_END_DATE, ACT_YEAR, ACT_PERIOD_NUM, ACT_QUARTER, ACT_HALF_YEAR and related start dates, enabling period-based aggregation.

Common Use Cases and Queries

Typical usage counts escalated service requests by incident type per accounting period for customer-intelligence dashboards. Because the view already renames keys, BI tools and fact-table loads consume it directly.

Sample listing of escalated SRs by type:

SELECT incident_type, COUNT(*) escalated_cnt
FROM bic_fctv_sr_escalated
WHERE act_period_name = :period
GROUP BY incident_type;

Joining the foreign key to its base type table:

SELECT v.incident_type_fk, t.name, v.act_quarter, COUNT(*)
FROM bic_fctv_sr_escalated v, cs_incident_types t
WHERE v.incident_type_fk = t.incident_type_id
GROUP BY v.incident_type_fk, t.name, v.act_quarter;

Before relying on this view, confirm existence via ALL_VIEWS; if absent, equivalent logic must be reconstructed from CS_INCIDENTS_ALL_B, JTF_TASK_REFERENCES_B, and JTF_TASKS_B with the ESC reference filter.