BusOpp.OrderLine

Purpose

for Live order intake and Pipeline Review

Business Opportunity Line Details

Use Case

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

Based on mvw.busopp_details_plus

DataMarts

Company
Customer
Business Opportunity

Source

FACT_BUSINESS_OPP_LINE_OL

From

	svw.busopp_value 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 svw.busopp_details as details_busopp
        ON details_busopp.COMPANY = src.COMPANY and details_busopp.PROJECT_ID = src.PROJECT_ID
	LEFT JOIN
		ifs.DIM_PROC_TEMPLATE_STAGE_OL$ AS dim_stage 
		ON details_busopp.[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] = details_busopp.[MARKET_CODE] 
	LEFT JOIN
		[ifs].[DIM_SALES_DISTRICT_OL] AS dim_district 
		ON dim_district.[CODE] = details_busopp.[DISTRICT_CODE] 
	LEFT JOIN
		[ifs].[DIM_SALES_REGION_OL] AS dim_region 
		ON dim_region.[CODE] = details_busopp.[REGION_CODE] 
    LEFT JOIN mvw.dates_DIM_DATE as entered_date
        on entered_date.Date_Key = coalesce(details_busopp.[ESTIMATED_SIGN_DATE],details_busopp.[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

	details_busopp.[STATE] NOT IN 
	(
		'Closed',
		'Cancelled' 
	)

Sample Exec

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

Response

	src.opportunity_no AS REFERENCE_ID,
	details_busopp.DESCRIPTION as [DESCRIPTION],
	[FYYY-FP] AS [PERIOD_SUBMITTED],
	'<blank>' AS FILLER1,
	--details_busopp.[STATE] AS [STATUS],
	'<blank>' AS FILLER2,
	details_project.WORK_AT_RISK AS WORKING_AT_RISK,
	'?field?' AS STATUS_DATE,
	'<blank>' AS BAFLITE_ID,
	'<blank>' AS DAY_BID_SUBMITTED,
	details_busopp.[DISTRICT_CODE], dim_district.[DESCRIPTION] AS [DISTRICT],
	details_busopp.[MARKET_CODE], dim_market.[DESCRIPTION] AS [MARKET],
	'<blank>' AS CAMPAIGN,
	details_busopp.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,
	'?field?' AS NET_PROFIT_INC_OVERHEAD_RECOV,
    details_busopp.ESTIMATED_SIGN_DATE as  PROBABLE_DATE_OF_ORDER,
    dates_project.[Start_Date] as  PROJECT_START_DATE,    
    dates_project.[End_Date] as  PROJECT_END_DATE,
	'?field?' AS EVERYTHING_BEFORE_THIS,
	'<blank>' AS FILLER3,
	'<blank>' AS COMMENTS,
	details_busopp.[WON_LOST_REASON_ID],
	'?field?' AS TARGET_CUSTOMER,
	'<blank>' AS CUST_ORDER_PLANNED_VALUE,
	'?field?' AS STRATEGIC_PILLAR,
	'?field?' AS ORDER_NO,
	LINE_NO_KEY AS LINE_NO,
	'?field?' AS REL_NO,
	'?field?' AS LINE_ITEM_NO,
	REVISION_NO_KEY,
	details_busopp.[STATE],
	'?field?' AS STATE_CHANGE,
	'?field?' AS CURRENT_LINE_STATE,
	'?field?' AS REVENUE,
	src.[CURRENCY_CODE],
	details_busopp.[CANCELLATION_REASON],
	'?field?' AS CANCEL_REASON_DESC,
	details_busopp.[STATE] AS BO_STATE,
	details_busopp.[CLOSED_STATUS] AS BO_CLOSED_STATUS,
	details_busopp.[DESCRIPTION] AS BO_DESCRIPTION,
	[BASE_SALE_UNIT_PRICE],
	[SALE_UNIT_PRICE],
	'?field?' AS BO_ESTIMATED_BASE_PROFIT,
	'?field?' AS [ACTIVITY_NO],
	'?field?' AS [PROJECT_ESTIMATED_NFI],
	'?field?' AS [PROJECT_PLANNED_NFI],
	details_project.CUSTOMER_PROJECT_ID AS [CO_PROJECT_ID],
	'?field?' AS AUTHORIZE_CODE,
	'?field?' AS BASE_REVENUE,
 	details_project.PROGRAM_ID as  PROGRAM_ID,
--BusOpp
	BusOpp_item.*,
--Project
	project_item.*,
--Customer
	customer_item.*,
--Log
	src.pipeline_log,
	src.snapshot_date

SQL

BMT-DWH-DEV/BusOpp_OrderLine.sql