Customer.Invoice

Purpose

Customer Invoice, provides high-level invoice details primarily focused on essential customer and financial information. It is structured to include invoice metadata, billing details, amounts, and various identifiers. The view originates from multiple snapshots and iterations, with each change tracked meticulously to support functionality and flexibility for different reporting and integration requirements.

Use Case

Owner: Soon Tan
Typical Use Case: Ideal for applications that require an overview of invoices, facilitating queries on invoice issuance, payment tracking, and financial reporting.
Style: Single row per unique Customer ID & Invoice SEQ for detailed, high-granularity reporting.

DataMarts Integration

This view can be utilised across the following DataMarts:

• Project
• Company
• Customer
  • Order

Source & Aggregation

QFACT_BMT_DWH_INVOICE_TAB_IAS

Join Table: ifs.FACT_CUSTOMER_INVOICE_OL — to enrich data through invoice matching fields, enabling a comprehensive aggregation of invoice details.

Sample Exec

For sample data execution, use the code:

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

Response

Identifiers

• COMPANY: Identifier for the company placing the order.
• INVOICE_NO: Unique order number.
• CUSTOMER_ID (PAYER_IDENTITY): Identifier for the customer.
• PROJECT_ID: Project associated with the order.
• PRU_Code: Project-specific identifier (CODE_E)

Sequence

INVOICE_SEQ (JSON format): Structured sequence for Invoice tracking, including:

• COMPANY: Company identifier.
• INVOICE_NO: Order number.

ORDER_SEQ (JSON format): Structured sequence for order pipeline tracking:

Dates (JSON format)

• INVOICE_DATE: Issue date of the invoice
• DUE_DATE: Payment due date

Values (JSON format)

• NET_CURR_AMOUNT: Net amount in invoice currency
• VAT_CURR_AMOUNT: VAT applied to the invoice
• GROSS_CURR_AMOUNT: Total payable amount (net + VAT)
• CURRENCY: Invoice currency code

Codes (JSON format)

• INVOICE_NO: Invoice number
• INVOICE_VERSION: Version control for invoice updates
• INVOICE_TYPE: Type classification
• ROWSTATE: Row state indicator
• PAY_TERM_ID: Payment terms identifier
• SEND_STATUS: Current status of invoice dispatch
• SERIES_ID: Series identifier
• PROJECT_ACTIVITY_ID: Related project activity ID
• ACC_YEAR_INVOICE_DATE, ACC_PERIOD_INVOICE_DATE: Accounting period indicators

Notes

NOTES: Additional comments or notes tied to each invoice.

SQL Code

To access the full SQL structure, refer to…

BMT-DWH-DEV/Customer_Invoice.sql