Search Results bic_dimv_cust_categories

Overview

BIC_DIMV_CUST_CATEGORIES is a dimensional view belonging to the BIC (Customer Intelligence) product family, historically shipped as part of Oracle's Customer Intelligence / Data Warehouse reporting stack. It exposes a consolidated list of customer category codes and their descriptive attributes, intended to act as a dimension source for analytical reporting and ETL processes that classify customers by category. The view is documented as obsolete in ETRM 12.2.2 and is not implemented in the database referenced by the documentation; the DBA Data section indicates "Not implemented in this database." Its presence in ETRM is therefore informational, preserving the historical definition of the view text so that legacy integrations and migration efforts can understand what the object once provided. In practice, the object surfaces the AR_LOOKUPS values for LOOKUP_TYPE = 'CUSTOMER_CATEGORY' plus a synthetic "ALL" row for reporting completeness.

Underlying Base Objects

The view text is defined over two base sources, neither of which is listed under "Referenced base objects" in the documented metadata, but both are identifiable from the SQL:

• AR_LOOKUPS — the Receivables lookup table. The first branch selects LOOKUP_CODE, MEANING, and DESCRIPTION where LOOKUP_TYPE = 'CUSTOMER_CATEGORY' and ENABLED_FLAG = 'Y'. This is the authoritative source of active customer category codes in Oracle Receivables.
• FND_LOOKUPS (aliased FL) — the Oracle Application Object Library lookup table. The second branch selects from this table where LOOKUP_TYPE = 'BIC_VALUE_TYPE' and LOOKUP_CODE = '-999'.
• The two branches are combined with UNION ALL, and the whole statement is declared WITH READ ONLY, indicating the view is intended purely for query access, not DML.

Key Columns

• ALL_FK — a hard-coded literal 'ALL', used as a foreign-key style constant so that the dimension can be joined to an "all values" aggregate row without a NULL key.
• CUSTOMER_CATEGORY_CODE — the category identifier. In the first branch this is AR_LOOKUPS.LOOKUP_CODE; in the second branch it is the MEANING value of the special FND_LOOKUPS row. This is the column most often targeted by the search term "customer_category_code."
• CUSTOMER_CATEGORY_NAME — the display name or meaning of the category (AR_LOOKUPS.MEANING in branch one).
• CUSTOMER_CATEGORY_DESC — the longer description text for the category, taken from DESCRIPTION in both branches.

Common Use Cases and Queries

A typical consumer query retrieves the available customer categories for a reporting filter or LOV:

• SELECT CUSTOMER_CATEGORY_CODE, CUSTOMER_CATEGORY_NAME FROM BIC_DIMV_CUST_CATEGORIES;
• Joining the "ALL" row to a fact table to produce a rolled-up total: SELECT ... FROM fact f JOIN BIC_DIMV_CUST_CATEGORIES d ON f.category_fk = d.CUSTOMER_CATEGORY_CODE OR d.ALL_FK = 'ALL';
• Since the view is read-only, it should never be the target of INSERT/UPDATE/DELETE. Because the object is obsolete and unimplemented in current databases, the equivalent logic should be sourced directly from AR_LOOKUPS where LOOKUP_TYPE = 'CUSTOMER_CATEGORY' and ENABLED_FLAG = 'Y'. Note the intrinsic safety of the read-only clause for any residual legacy reporting code.