BusOpp.Codes

by

📝 Description

This view provides a structured metadata representation of Business Opportunities (BusOpp), transforming source data into a JSON-like attribute-value format. It supports consistent reporting and simplifies analytics by organising attributes under clear META_TYPE categories like OPPORTUNITY, STATUS, and SALES.

🎯 Purpose

  • To standardise metadata across all Business Opportunity reporting use cases.
  • To offer a code-value-description model that is both user-readable and machine-readable.
  • To align attribute sourcing across multiple IFS tables under a unified format.
  • To serve as the primary metadata layer for busopp.core, busopp.details, and busopp.values.

✅ Use Cases

  • To enrich busopp.details with decoupled code metadata.
  • To enable drill-through or filter panels in Power BI with properly labelled values.
  • To ensure audit trails and clear lineage of classification fields.
  • To support flexible value reporting, including grouping, filtering, or classification.

🧱 Structure

Each row in the view corresponds to a single metadata entry, uniquely identified by:

  • OBJECT_SEQ – combination of COMPANY and OPPORTUNITY_NO
  • META_TYPE – high-level classification (e.g. OPPORTUNITY, STATUS, SALES)
  • ATTRIBUTE – the specific metadata label (e.g. RISK_LEVEL, STAGE, MARKET)
  • CODE_VALUE – the raw or hashed code
  • VALUE_METADATA – JSON block containing CODE, DESC, and any additional context

🔄 Sourcing Logic

The view is built using a sequence of CTEs and CROSS APPLY transformations:

  • OBJECT_SOURCE: Generates the unique identifier (OBJECT_SEQ) for each opportunity.
  • META_OPPORTUNITY: Extracts attributes like Strategic Pillar, PRU Code, Programme ID, and Risk Level from opportunity tables.
  • META_STATUS: Captures status-related metadata like Stage, State, Win/Loss Reason with embedded NOTE blocks.
  • META_SALES: Extracts sales hierarchy data including Region, Market, District, Target Customer, Contact, and Representative.

Each value is tagged with:

  • A source system identifier (e.g., FACT_BUSINESS_OPP_OL$)
  • A JSON-formatted description
  • A checksum for auditing changes

🧾 Output Columns

ColumnDescription
OBJECT_SEQUnique identifier { "COMPANY": "X", "OPPORTUNITY_NO": "Y" }
META_TYPEOne of: OPPORTUNITY, STATUS, SALES
ATTRIBUTEAttribute name (e.g. PRU_CODE, STATE, REGION)
CODE_VALUERaw or hashed code used for classification
VALUE_METADATAJSON describing the code, label, and (where relevant) note
SOURCE_SYSTEMThe table where the data came from
CREATE_DATEDate the row was created (default: current date)
EXPIRY_DATEExpiry of the record (default: 2099-12-31)
ACTIVE_FLAGBoolean flag (1 = active)
CHECKSUMMD5 hash of core fields to detect changes

🔍 Key Notes

  • Working at Risk (WAR): Is embedded in the STAGE attribute if applicable.
  • Lost Reasons: May include optional JSON NOTE entries with REASON, CANCELLATION, LOST_TO.
  • TARGET_CUSTOMER: Extracted from compound fields (e.g., “12345 – Acme Ltd”).

Leave a Comment