Customer.OrderlineValue

Purpose

The Customer Order Line Value view provides a detailed financial overview of customer order lines, including key pricing and cost information, quantities, dates, and currency-based amounts. This view is specifically designed to facilitate reporting and analysis on the financial performance of individual order lines. It includes fields that capture gross and net amounts, tax-inclusive prices, and multi-currency support, which is crucial for businesses operating in international markets.

This view supports detailed financial tracking, profitability analysis, and operational reporting across various dimensions such as order dates, shipping schedules, and quantities. By centralising this detailed information, it allows for comprehensive evaluations of customer orders at the line-item level.

Use Case

Owner: Soon Tan
Typical Use Case: 

• Revenue Reporting: Generate reports to track total net and gross amounts for all order lines over a given period, helping to analyse sales performance.
• Profitability Analysis: Evaluate the profitability of individual order lines or product categories by comparing revenue and cost data.
• International Reporting: Analyse financial performance in different currencies to support multi-country business operations.
• Order Lifecycle Tracking: Track the planned and actual delivery timelines to monitor operational performance and customer fulfilment.

Style: Fact, Each row represents a unique Order Line with comprehensive financial details and associated dates.

Source

FACT_CUSTOMER_ORDER_LINE_OL$

Sample Exec

For sample data execution, use the code:

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

Response

Identifiers

• COMPANY: Company associated with the order.
• ORDER_NO_KEY: Key identifier for the order.
• ORDER_LINE_SEQ (JSON Format): Structured order line sequence, including:
  • ORDER_NO_KEY: Key order number.
  • LINE_NO_KEY, REL_NO_KEY, LINE_ITEM_NO_KEY: Sequential identifiers for the line item.

Dates

• PLANNED_DELIVERY_DATE: Expected delivery date.
• WANTED_DELIVERY_DATE: Date requested by the customer.
• PLANNED_SHIP_DATE: Scheduled shipping date.
• FIRST_ACTUAL_SHIP_DATE and LAST_ACTUAL_SHIP_DATE: Actual shipment dates.
• TARGET_DATE: Target completion or delivery date.
• REPORTING_DATE: Date relevant for reporting.
• PRICE_EFFECTIVITY_DATE: Date the price became effective.

Quantities

• SALES_QTY: Quantity of items sold.

Prices and Amounts

• BASE_SALE_UNIT_PRICE: Base unit sale price of the item.
• BASE_UNIT_PRICE_INCL_TAX: Unit price inclusive of tax.
• NET_AMOUNT_BASE: Net amount in base currency (reflects order intake).
• GROSS_AMOUNT_BASE: Gross amount in base currency.
• NET_AMOUNT_CURR: Net amount in transaction currency.
• GROSS_AMOUNT_CURR: Gross amount in transaction currency.

Cost and Profit

• COST_PER_UNIT: Cost per unit of the item.
• TOTAL_COST: Total cost for the order line.
• PROFIT: Profit from the order line.

Codes (JSON Format)

• CURRENCY: Currency code for the transaction.
• TAX_CODE_DESC: Description of the tax code applied.

SQL Code

To access the full SQL structure, refer to…

BMT-DWH-DEV/Customer_OrderlineValue.sql