Project.PurchaseOrderline

by

Purpose

The Supplier Purchase Order Line Fact Table
Designed to display detailed quantitative data related to purchase orders from suppliers. This table integrates with various dimension tables to provide comprehensive insights into purchase order lines, including project associations, supplier details, and order statuses. The primary purpose is to support procurement tracking, cost analysis, and supplier management within the organisation.

Use Case

Owner: Martyn Cole
Typical Use Case: For the purpose of Procurement Tracking within the Project Reporting Portal
Style: Fact, focusing on the measurements, metrics, or facts associated with purchase order lines.

Source

Table

FACT_PURCHASE_ORDER_LINE_OL

Contains core data about purchase order lines from suppliers, including pricing, quantities, and order-specific dates.

Integration with Dimension Tables:

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

  • Supplier Details: To enrich the data with additional supplier information.
  • Project Details: To provide context on which project the purchase order line pertains to.
  • Company Details: To ensure company-specific data is included for analysis.

Filtering Criteria:

  • The PROJECT field must be not null.
  • src.DIM_COMPANY_ID must not be ‘1062’.

Sample Exec

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

Response

src.DIM_COMPANY_ID AS COMPANY: 
src.SUPPLIER AS SUPPLIER_ID: 
src.PROJECT AS PROJECT_ID:

SEQ

src.ORDER_NO_KEY:
src.LINE_NO_KEY: 
src.RELEASE_NO_KEY:

Dates

try_parse(src.[PO_DATE_ENTERED] AS date) AS DATE_ENTERED: 
try_parse(src.[POL_PLANNED_DEL_DATE] AS date) AS PLANNED_DELIVERY_DATE: 
try_parse(src.[POL_WANTED_DEL_DATE] AS date) AS WANTED_DELIVERY_DATE: 
try_parse(src.[POL_PROMISED_DEL_DATE] AS date) AS PROMISED_DELIVERY_DATE:

Measures

Value

cast(src.BUY_UNIT_PRICE AS decimal(10, 3)) AS BUY_UNIT_PRICE: 
cast(src.NET_LINE_PRICE_BASE AS decimal(10, 3)) AS NET_LINE_PRICE_BASE: 
cast(src.GROSS_LINE_PRICE_BASE AS decimal(10, 3)) AS GROSS_LINE_PRICE_BASE:

Quantity

cast(src.PURCHASE_QTY AS decimal(10, 3)) AS PURCHASE_QTY:

Codes

src.STATE AS [Line_State]: 
src.NO_PART_DESCRIPTION AS LINE_DESCRIPTION:

SQL

BMT-DWH-DEV/Project_PurchaseOrderline.sql

Leave a Comment