BusOpp.Pipeline

Purpose

for Live order intake and Pipeline Review

Single row (Summary Header) per Business Opportunity

Use Case

Owner: Soon Tan
Typical Use Case: Live order intake and Pipeline Review
Style: Tailored Fact

Based on mvw.busopp_details_plus

Source

FACT_BUSINESS_OPP_OL

FROM

svw.busopp_details AS src 

    LEFT JOIN dvw.busopp_item as busopp_item on
    CONVERT(NVARCHAR(32),HashBytes('MD5', CONCAT_WS('|',src.COMPANY,src.OPPORTUNITY_NO)),2) = busopp_item_id
    LEFT JOIN dvw.customer_item as customer_item on
    CONVERT(NVARCHAR(32),HashBytes('MD5', CONCAT_WS('|',src.COMPANY,src.CUSTOMER_ID)),2) = customer_item_id
	LEFT JOIN dvw.project_item as project_item on
    CONVERT(NVARCHAR(32),HashBytes('MD5', CONCAT_WS('|',src.COMPANY,src.PROJECT_ID)),2) = project_item_id
        LEFT JOIN svw.project_details as details_project
        ON details_project.COMPANY = src.COMPANY and details_project.PROJECT_ID = src.PROJECT_ID
	LEFT JOIN
		ifs.DIM_PROC_TEMPLATE_STAGE_OL$ AS dim_stage 
		ON src.[STAGE_ID] = dim_stage.[STAGE_ID] 
		AND dim_stage.[PROCESS_ID] = 10 
	LEFT JOIN
		[ifs].[DIM_SALES_MARKET_OL] AS dim_market 
		ON dim_market.[CODE] = [MARKET_CODE] 
	LEFT JOIN
		[ifs].[DIM_SALES_DISTRICT_OL] AS dim_district 
		ON dim_district.[CODE] = [DISTRICT_CODE] 
	LEFT JOIN
		[ifs].[DIM_SALES_REGION_OL] AS dim_region 
		ON dim_region.[CODE] = src.[REGION_CODE] 
    LEFT JOIN mvw.dates_DIM_DATE as entered_date
        on entered_date.Date_Key = coalesce(src.[ESTIMATED_SIGN_DATE],src.[DATE_ENTERED])
    LEFT JOIN svw.project_dates as dates_project
        ON dates_project.COMPANY = src.COMPANY and dates_project.PROJECT_ID = src.PROJECT_ID

WHERE

src.[STATE] not in (‘Closed’,’Cancelled’)

Parameters

Company
Customer
Business Opportunity

Sample Exec

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

Response

    src.opportunity_no as REFERENCE_ID,
    busopp_item.BusOpp_Desc  as [DESCRIPTION],
    [FYYY-FP] as [PERIOD_SUBMITTED],
    '<blank>' as FILLER1,
    '<blank>' as FILLER2,
    '<blank>' as FILLER3,
    '<blank>' as FILLER4,
    src.[STAGE_ID],       dim_stage.[DESCRIPTION] as [STAGE_DESCRIPTION],
    '<blank>' as BAFLITE_ID,
    '<blank>' as DAY_BIG_SUBMITTED,   
    src.[DISTRICT_CODE],       dim_district.[DESCRIPTION] as [DISTRICT],
    src.[MARKET_CODE],       dim_market.[DESCRIPTION] as [MARKET],
    '<blank>' as CAMPAIGN,
    src.BUSINESS_TYPE as BUSINESS_TYPE,
    '<blank>' as INTERNAL_BMT_CUSTOMER,
    '<blank>' as INTERNAL_SALE_ONLY,
    src.CUSTOMER_ID,
    customer_item.[Customer_Name] as CUSTOMER,
    '<blank>' as KEY_CUSTOMER_ACC_NAME,
    src.[TOTAL_CURR_OPP_VALUE] as ORDER_INTAKE,
    src.[TOTAL_BASE_OPP_VALUE] as NET_FEE_INCOME_OF_OI,
    '<blank>' as FILLER,
    src.ESTIMATED_SIGN_DATE as  PROBABLE_DATE_OF_ORDER,
    dates_project.[Start_Date] as  PROJECT_START_DATE,    
    dates_project.[End_Date] as  PROJECT_END_DATE,
    src.[PROBABILITY] as WIN_PROBABILITY,
    '?field?' as COMMENTS,
    '?field?' as TARGET_CUSTOMER,
    '?field?' as STRATEGIC_PILLAR,
    details_project.PROGRAM_ID as  PROGRAM_ID,
    src.[PROJECT_ID] as [DEL_PROJECT_ID],

--BusOpp
	BusOpp_item.*,
--Project
	project_item.*,
--Customer
	customer_item.*,
--Log
	src.pipeline_log,
	src.snapshot_date

SQL

BMT-DWH-DEV/BusOpp_Pipeline.sql