BusOpp.Details_Plus

by

Purpose

The Common View for Employee Details. It provides dimension attributes essential for data segmentation of Employee data. It is accessible to multiple departments and systems across the organisation.

An extension of BusOpp.Details

Use Case

Owner: Martyn Cole
Typical Use Case: It provides dimension attributes essential for data segmentation
Style: Dimension, meaning a single row per Business Opportunity No.

Source

FACT_BUSINESS_OPP_OL

Parameters

Company
Business Opportunity
Customer
Project

Sample Exec

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

Response

	src.COMPANY,
	src.OPPORTUNITY_NO,
	src.CUSTOMER_ID,
	clv.PROJECT_ID,

Entity

	CONVERT(NVARCHAR(32),HashBytes('MD5', CONCAT_WS('|',src.COMPANY,src.OPPORTUNITY_NO)),2) as busopp_item_id,
	iif(len(src.CUSTOMER_ID)>0,CONVERT(NVARCHAR(32),HashBytes('MD5', CONCAT_WS('|',src.COMPANY,src.CUSTOMER_ID)),2),NULL) as customer_item_id,
	iif(len(clv.PROJECT_ID)>0,CONVERT(NVARCHAR(32),HashBytes('MD5', CONCAT_WS('|',src.COMPANY,clv.PROJECT_ID)),2),NULL) as project_item_id,
    src.DESCRIPTION as BusOpp_Desc,
    left(CONCAT_WS(' - ', src.OPPORTUNITY_NO, src.DESCRIPTION), 40)
        + IIF(LEN(CONCAT_WS(' - ', src.OPPORTUNITY_NO, src.DESCRIPTION)) > 40, '...', '') AS [BusOpp_Title],

Dates

	try_parse(DATE_ENTERED as date) as DATE_ENTERED,
	try_parse(START_DATE as date) as START_DATE,
	try_parse(ESTIMATED_SIGN_DATE as date) as ESTIMATED_SIGN_DATE,
	try_parse(WantedDate.WANTED_DELIVERY_DATE as date) as WANTED_DELIVERY_DATE,
	try_parse(CLOSURE_DATE as date) as CLOSURE_DATE,

Measures

Value

	try_parse(BASE_EST_OPPORTUNITY_VALUE AS DECIMAL(15, 3)) AS BASE_EST_OPPORTUNITY_VALUE,
	try_parse(EST_OPPORTUNITY_VALUE AS DECIMAL(15, 3)) AS EST_OPPORTUNITY_VALUE,
	try_parse(TOTAL_BASE_OPP_VALUE AS DECIMAL(15, 3)) AS TOTAL_BASE_OPP_VALUE,
	try_parse(TOTAL_CURR_OPP_VALUE AS DECIMAL(15, 3)) AS TOTAL_CURR_OPP_VALUE,
	try_parse(TOTAL_COST_VALUE AS DECIMAL(15, 3)) AS TOTAL_COST_VALUE,

Codes

	src.CURRENCY_CODE,
	src.SOURCE_ID,

Probability

	src.OPPORTUNITY_TYPE,
	src.PROBABILITY,
	clv.RISK_LEVEL,

Status

	src.STAGE_ID,
	src.STATE,
	src.WON_LOST_REASON_ID,
	src.CLOSED_STATUS,
	src.LOST_TO,
	src.CANCELLATION_REASON,

Market

	src.BUSINESS_TYPE,
	src.REGION_CODE,
	src.DISTRICT_CODE,
	src.MARKET_CODE,
	src.MAIN_CONTACT_ID,
	src.MAIN_REPRESENTATIVE_ID

Other Codes

src.[CURRENCY_CODE],

SQL

BMT-DWH-DEV/BusOpp_Details_Plus.sql

5 thoughts on “BusOpp.Details_Plus”

  1. Array
(
    [Current stable version] => 202409
    [exec] => @version = '202409'
    [Development] => @version = 'beta'
    [Change] => Include [CF$_PRU_VALUE] as PRU | Remove Dates and Values to Busopp.Value (Headline Values and Dates remain) | Calculation of BaseOppValue | Add Market Codes
)

  4. LEFT JOIN
    (
    SELECT
    MAX(CF$_BO_NO) AS OPPORTUNITY_NO,
    CF$_PROJECT_ID AS PROJECT_ID
    FROM
    ifs.QFACT_OPP_PROJECT_CLV_OL$
    JOIN
    ifs.FACT_BUSINESS_OPP_OL AS status
    ON CF$_BO_NO = OPPORTUNITY_NO
    AND status.STATE NOT IN
    (
    ‘CANCELLED’,
    ‘UNCONFIRMED’
    )
    WHERE
    LEFT(CF$_PROJECT_ID, ‘1’) = ‘D’
    GROUP BY
    CF$_PROJECT_ID
    ) as busopp ON Busopp.PROJECT_ID = src.PROJECT_ID

Leave a Comment