BusOpp.Dates

Purpose

The Business Opportunity Dates view is designed to consolidate and standardise key date attributes related to business opportunities within the organisation.

The Business Opportunity Dates view plays a critical role in tracking, analysing, and forecasting opportunity lifecycle events. By consolidating multiple date fields into a single structured dataset, it ensures better visibility, consistency, and accessibility of key business opportunity milestones, supporting strategic decision-making across the organisation.

Use Case

Owner: Soon Tan
Typical Use Case: Used in business opportunity analytics, sales forecasting, and pipeline reporting to segment and track business opportunities by various lifecycle milestones.
Style: Dimension table containing a single row per OPPORTUNITY_NO.

Source

FACT_BUSINESS_OPP_OL$

Integrations:

• ifs.FACT_BUSINESS_OPP_LINE_OL – Retrieves WANTED_DELIVERY_DATE based on the latest available value per opportunity.

DataMarts Integration

This view is integrated across the following DataMarts:

• Customer – Associates business opportunities with specific customer details.
• Sales & Opportunities – Supports tracking and forecasting of opportunities.
• Company – Links opportunity data with organisational context.

Sample Exec

EXEC get.myView @dataMart = 'BusOpp', @viewName = 'Dates', @token = '<myToken>', @version = '<202404>'

Response

Identifiers:

The table includes unique identifiers:

• COMPANY
• OPPORTUNITY_NO
• OPPORTUNITY_SEQ: A JSON-based sequence key combining COMPANY and OPPORTUNITY_NO for consistent tracking.

Date Attributes:

Tracks critical lifecycle dates for business opportunities:

{
  "START_DATE": "2024-01-15",
  "ESTIMATED_SIGN_DATE": "2024-02-20",
  "ACTUAL_SIGN_DATE": "2024-03-10",
  "EXPIRATION_DATE": "2024-12-31",
  "PRICE_EFFECTIVE_DATE": "2024-02-01",
  "WANTED_DELIVERY_DATE": "2024-05-15",
  "CLOSURE_DATE": "2024-06-30"
}

• CLOSURE_DATE – The date when the opportunity is finalised or closed.
• START_DATE – The initial date of the opportunity.
• ESTIMATED_SIGN_DATE – Projected contract signature date.
• ACTUAL_SIGN_DATE – The actual contract signature date.
• EXPIRATION_DATE – The date the opportunity expires or is no longer valid.
• PRICE_EFFECTIVE_DATE – The date pricing for the opportunity becomes effective.
• WANTED_DELIVERY_DATE – The latest wanted delivery date based on associated opportunity line data.

SQL

BMT-DWH-DEV/BusOpp_Dates.sql