Project.CustomerInvoice

by

Purpose

The Project Customer Invoice (value) Fact Table
Designed to display quantitative data related to invoices 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_INVOICE_OL

Integrates with

  • FACT_CUSTOMER_INVOICE_OL: Contains detailed data related to customer invoices, including amounts, dates, and statuses.
  • QFACT_CUST_ORDER_INV_H_CFV_OL: Provides additional invoice information linked by INVOICE_ID, including invoice status and readiness.
WHERE
src.PROJECT_ID is not null
and
src.COMPANY_KEY not in ('1062')

DataMarts

Company
Customer
Project

Sample Exec

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

Response

src.COMPANY_KEY AS COMPANY
src.DIM_CUSTOMER_ID AS CUSTOMER_ID
src.PROJECT_ID

SEQ

INVOICE_ID_KEY,
INVOICE_VERSION,

Dates

try_parse(src.INVOICE_DATE AS DATE) AS INVOICE_DATE
try_parse(DIM_DUE_DATE_ID AS DATE) AS DUE_DATE
try_parse(src.PAY_TERM_BASE_DATE AS DATE) AS PAY_TERM_BASE_DATE
try_parse(VOUCHER_DATE AS DATE) AS VOUCHER_DATE
try_parse(ACC_PERIOD_FROM AS DATE) AS ACC_PERIOD_FROM

Measures

cast(src.GROSS_AMOUNT AS decimal(10, 3)) AS GROSS_AMOUNT
cast(src.OPEN_AMOUNT AS decimal(10, 3)) AS OPEN_AMOUNT
cast(src.NET_AMOUNT AS decimal(10, 3)) AS NET_AMOUNT
cast(src.VAT_AMOUNT AS decimal(10, 3)) AS VAT_AMOUNT
cast(src.GROSS_AMOUNT_DOM AS decimal(10, 3)) AS GROSS_AMOUNT_DOM
cast(src.OPEN_AMOUNT_DOM AS decimal(10, 3)) AS OPEN_AMOUNT_DOM
cast(src.NET_AMOUNT_DOM AS decimal(10, 3)) AS NET_AMOUNT_DOM,

Codes

src.INVOICE_NO
src.INVOICE_TYPE
src.STATE
src.SEND_STATUS
src.PAY_TERM_ID
src.CURRENCY AS CURRENCY_CODE
cfv.CF$_INVOICE_SENT AS INVOICE_SENT
cfv.CF$_READY_TO_SEND AS READY_TO_SEND
src.NOTES

SQL

BMT-DWH-DEV/Project_CustomerInvoice.sql

2 thoughts on “Project.CustomerInvoice”

  1. Checked 19/06/2024
    Only Companies 1072 & 1098 invoices are checked that are linked to a Project ID.
    Customer (CUSTDEB/CUSTCRE) and Project (PRJINV / PRJCRE) are checked, Instant invoices (INSTINV) are not, as the data is not present in a list form in G-MIS
    Instant invoices are primarily for inter-company invoices or have been used to resolve G-MIS issues, therefore their exclusion is acceptable.

    2 invoices (0.1%) of 2073 of invoices had errors.
    These are timing issues, and not errors.

Leave a Comment