Customer.OrderLine

Purpose

The Customer Order Line view provides essential details for individual order lines within customer orders. It serves as a granular component, detailing each line item’s specifics, such as quantity, pricing, revenue, dates, and relevant codes. Designed to align closely with the Customer Order view, this view enables precise tracking of each line item in a customer’s order, supporting comprehensive reporting and integration.

Use Case

Owner: Soon Tan
Typical Use Case: Suitable for applications requiring detailed insights into order lines, facilitating analysis on sales quantities, pricing, and market segmentation at a line-item level.
Style: Each row represents a unique Order Line with associated customer, project, and market attributes.

You could create a dashboard that summarises the key performance indicators (KPIs) at the order level:

• Total number of orders placed in a given time frame.
• Percentage of orders that are delayed.
• Total revenue generated from orders.
• Average order value.
• Percentage of cancelled orders.
• Breakdown of orders by region or customer segment.

Source

FACT_CUSTOMER_ORDER_LINE_OL$

Integration with Dimension Tables:

Joins:

• ifs.DIM_ORDER_CANCEL_REASON_OL: Provides descriptions for cancellation codes.
• ifs.QFACT_CUSTOMER_ORDER_CFV_OL$: Links project and opportunity information.
• ifs.FACT_CUSTOMER_OL_BMT_OL: Includes additional notes and descriptions.
• ifs.DIM_SALES_REGION_OL, DIM_SALES_DISTRICT_OL, DIM_SALES_MARKET_OL: Provides regional, district, and market descriptions.
• ifs.FACT_BUSINESS_OPP_OL$: Connects opportunity and strategic pillar data.
• ifs.DIM_PROJECT_OL$: Enriches project-related information.

Although not explicitly joined in the provided query, this table typically integrates with dimension tables for:

Company Details: To ensure company-specific data is included for analysis.
Customer Details: To enrich the data with additional customer information.
Project Details: To provide context on which project the purchase order line pertains to.
Business Opportunity Details: Links the purchase order line to its corresponding business opportunity.

Sample Exec

For sample data execution, use the code:

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

Response

Identifiers

• COMPANY: Company associated with the order.
• CUSTOMER_ID (CUSTOMER): Identifier for the customer.
• PROJECT_ID: Project identifier, based on CF$_PROJECT_ID or DIM_PROJECT_ID.
• OPPORTUNITY_NO: Associated business opportunity number.
• PRU_Code: Strategic PRU code, derived from various project sources.

Order Line Sequence (JSON Format)

• ORDER_NO_KEY: Key order number.
• LINE_NO_KEY, REL_NO_KEY, LINE_ITEM_NO_KEY: Line item identifiers for precise tracking of each part of the order.

Dates

• DATE_ENTERED: Date the order line was created.
• PROMISED_DELIVERY_DATE: Expected delivery date for the line item.
• ACTUAL_DELIVERY_DATE: Date the item was delivered, if available.

Financial Values

• BASE_SALE_UNIT_PRICE: Unit price of the item.
• SALES_QTY: Quantity of items sold.
• NET_AMOUNT_BASE: Net amount in base currency.
• REVENUE: Revenue generated from the order line, calculated based on SALES_QTY, PRICE_CONV_FACTOR, and BASE_SALE_UNIT_PRICE. If cancelled, the revenue is set to zero.
• CURRENCY: Currency code for the transaction.

Codes (JSON Format)

• ORDER_NO: Order number.
• CUST_ORDER_TYPE: Type of customer order.
• INVOICE_BLOCKED: Indicator of invoice status.
• AUTHORIZE_CODE: Code for authorisation if applicable.

Status (JSON Format)

• STATE: Code and description for the order line state.
• ORDER_CANCEL_REASON: Code and description for cancellation reasons.
• WORKING_AT_RISK: Indicates if the line item is at risk.

Notes

• LINE_DESCRIPTION: Brief description of the line item.
• LINE_NOTES: Additional notes from the order line.

Market (JSON Format)

• STRATEGIC_PILLAR: Strategic pillar associated with the line item.
• SALES_REGION, SALES_DISTRICT, SALES_MARKET: Codes and descriptions for sales segmentation.
• MAIN_CONTACT_ID: Primary contact identifier for the order.
• MAIN_REPRESENTATIVE_ID: Main representative ID for the sales effort.

SQL Code

To access the full SQL structure, refer to…

BMT-DWH-DEV/Customer_OrderLine.sql