Project.CustomerOrderLine

Purpose

The Project Customer Orderline (value) Fact Table
Designed to display quantitative data related to orders generated for projects. This table integrates with dimension tables, such as client_details and project_details, to provide detailed insights into the financial aspects of projects, including invoicing, revenue, and billing trends. The primary purpose is to support forecasting, budgeting, and proactive project management across the organisation.

Use Case

Owner: Martyn Cole
Typical Use Case: For the purpose of Order Tracking within Project Reporting Portal
Style: Fact, meaning it consists of the measurements, metrics or facts of a business process

Source

Table

FACT_CUSTOMER_ORDER_LINE_OL$

Integrated Tables:

• DIM_ORDER_CANCEL_REASON_OL: Provides descriptions for cancellation reasons, linked by ORDER_CANCEL_REASON.
• QFACT_CUSTOMER_ORDER_CFV_OL$: Contains additional information about customer orders, such as project IDs and order states, joined via ORDER_NO_KEY and CUSTOMER_NO.
• QFACT_PRE_ACCOUNTING_OL$: Although not selected in this query, it joins with cfv based on PRE_ACCOUNTING_ID, which may provide additional accounting details in other contexts.
• FACT_CUSTOMER_OL_BMT_OL: Supplies extra notes related to the order line, joined by CUSTOMER, ORDER_NO_KEY, LINE_NO_KEY, REL_NO_KEY, and LINE_ITEM_NO_KEY.

Filtering Criteria:

• cfv.CF$_PROJECT_ID, CODENO_D, and src.DIM_PROJECT_ID must be not null and not equal to ‘#’.
• src.DIM_COMPANY_ID must not be ‘1062’.

DataMarts

1. Company
2. Customer
3. Project

Sample Exec

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

Response

	src.DIM_COMPANY_ID AS COMPANY,
	src.CUSTOMER AS CUSTOMER_ID,
	coalesce(cfv.CF$_PROJECT_ID,CODENO_D,src.DIM_PROJECT_ID) as PROJECT_ID,

SEQ

ORDER_NO_KEY,
LINE_NO_KEY,
REL_NO_KEY,
LINE_ITEM_NO_KEY,

Dates

	try_parse(src.[DATE_ENTERED_TIME] as date) as DATE_ENTERED,
	try_parse(src.[PLANNED_DELIVERY_DATE] as date) as PLANNED_DELIVERY_DATE,
	try_parse(src.[WANTED_DELIVERY_DATE] as date) as WANTED_DELIVERY_DATE,
	try_parse(src.[PROMISED_DELIVERY_DATE] as date) as PROMISED_DELIVERY_DATE,
	try_parse(src.[ACTUAL_DELIVERY_DATE] as date) as ACTUAL_DELIVERY_DATE,

Measures

Value

	cast(src.BASE_SALE_UNIT_PRICE as decimal (10,3)) as BASE_SALE_UNIT_PRICE,
	cast(src.SALE_UNIT_PRICE as decimal (10,3)) as SALE_UNIT_PRICE,
	cast(src.NET_AMOUNT_CURR as decimal (10,3)) as NET_AMOUNT_CURR,

Quantity

    cast([SALES_QTY] as decimal (10,3)) as [SALES_QTY],

Other Codes

	src.ORDER_NO,
	src.CUST_ORDER_TYPE,
	src.INVOICE_BLOCKED,
	src.STATE AS [Line_State],src.STATE_DESC AS [Line_State_Desc], -- Renamed 2024-07-16
	cfv.[STATE] as [Customer_Order_State], -- 2024-07-16
	src.ORDER_CANCEL_REASON,code_cancel.REASON_DESCRIPTION AS CANCEL_REASON_DESC,
	src.LINE_DESCRIPTION,
	ol_bmt.NOTES as LINE_NOTES

SQL

BMT-DWH-DEV/Project_CustomerOrderLine.sql