Search Results open_period

Overview

BIC_FCTV_SR_OPEN is a reporting view historically shipped with the BIC (Customer Intelligence) product family in Oracle E-Business Suite releases 12.1.1 and 12.2.2. The BIC module was an early analytical layer intended to feed customer-facing data marts and dashboards, and it has since been designated obsolete in the ETRM repository. The view presents a time-bucketed projection of service request (incident) activity, joining open service incidents to a time dimension and a customer dimension so that incidents can be reported by accounting or activity period rather than by raw transaction date.

The view is read-only (declared WITH READ ONLY) and is defined over the CS_INCIDENTS_ALL_B incident table plus several BIC and CRM lookup sources. Its purpose in reporting and integration is to expose one row per incident per intersecting reporting period, with pre-computed period attributes and an OPEN_PERIOD measure describing how many days of the incident's open life fall within that period. In practice this supports aging, backlog, and open-duration analytics for service requests. Note that many EBS instances report this object as "Not implemented in this database," meaning it exists in the ETRM catalog but the underlying BIC schema was not deployed.

Underlying Base Objects

The view text references the following base objects: CS_INCIDENTS_ALL_B (the incidents base table, aliased INC), CS_INCIDENT_STATUSES (STAT), CS_INCIDENT_SEVERITIES (ISEV), CS_INCIDENT_TYPES (ITYP), JTF_RS_RESOURCE_EXTNS (PF, outer-joined to resolve the incident owner as a resource), BIC_DIMV_TIME (DT, the BIC time dimension view), and BIC_DIMV_CUSTOMERS (DC, the BIC customer dimension view). The ETRM metadata records no formally documented base objects for this view, so the lineage below is derived from the view definition itself.

• The incident header supplies IDs, dates, owner, customer, and ORG_ID.
• CS_INCIDENT_STATUSES supplies the CLOSE_FLAG used to distinguish open from closed incidents.
• BIC_DIMV_TIME supplies the activity period attributes (period name, start/end dates, year, quarter, half-year).
• BIC_DIMV_CUSTOMERS supplies party, geography, customer category, and market segment attributes.

The join is a many-to-many expansion: an incident is matched to every time-dimension row whose period overlaps its open window, filtered by INCIDENT_DATE <= ACT_PERIOD_END_DATE and NVL(CLOSE_DATE, INCIDENT_DATE) >= START_DATE.

Key Columns

• INCIDENT_ID — primary identifier of the service incident.
• INCIDENT_OWNER_FK, INCIDENT_STATUS_FK, INCIDENT_SEVERITY_FK, INCIDENT_TYPE_FK — surrogate foreign keys to owner, status, severity, and type dimensions.
• INCIDENT_DATE, CLOSE_DATE — creation and closure timestamps of the incident.
• OPEN_PERIOD — computed days open within the intersecting period (LEAST of period end or close date, minus GREATEST of incident date or period start).
• CLOSE_FLAG — indicates whether the incident is closed.
• INCIDENT_SEVERITY / SEVERITY_DESC, INCIDENT_TYPE / INC_TYPE_DESC — descriptive severity and type labels.
• PARTY_ID, COUNTRY, CITY, STATE, CUSTOMER_CATEGORY_FK, MARKET_SEGMENT_FK — customer dimension attributes.
• ACT_PERIOD_NAME, START_DATE, ACT_PERIOD_START_DATE, ACT_PERIOD_END_DATE, ACT_YEAR, ACT_PERIOD_NUM, ACT_QUARTER, ACT_HALF_YEAR — time dimension attributes for period-based reporting.
• ORG_ID — operating unit context for multi-org security.

Common Use Cases and Queries

Typical usage covers open service request aging and backlog trend analysis by period, customer, or organization. Note that the customer-name columns LAST_NAME, FIRST_NAME, EMPLOYEE_NAME, and FULL_NAME are all defined as NULL literals, so customer identity must be resolved through PARTY_ID.

• Open service requests by period and severity: SELECT ACT_PERIOD_NAME, INCIDENT_SEVERITY, COUNT(*) FROM BIC_FCTV_SR_OPEN WHERE CLOSE_FLAG = 'N' GROUP BY ACT_PERIOD_NAME, INCIDENT_SEVERITY.
• Average open duration by customer market segment: SELECT MARKET_SEGMENT_FK, AVG(OPEN_PERIOD) FROM BIC_FCTV_SR_OPEN GROUP BY MARKET_SEGMENT_FK.
• Organization-scoped aging report: SELECT INCIDENT_ID, PARTY_ID, OPEN_PERIOD FROM BIC_FCTV_SR_OPEN WHERE ORG_ID = :p_org_id AND CLOSE_FLAG = 'N'.
• Type distribution by quarter: SELECT ACT_YEAR, ACT_QUARTER, INCIDENT_TYPE, COUNT(*) FROM BIC_FCTV_SR_OPEN GROUP BY ACT_YEAR, ACT_QUARTER, INCIDENT_TYPE.

Because BIC is flagged obsolete and the view is frequently unimplemented, these queries should be validated against the target instance before use; equivalent reporting is generally available through the standard Service (CS) tables.