Employee.Item_Codes_Expense

by

Purpose

This view is part of the Common Data Standard, following an item and codes style table structure, ensuring consistency in data modelling across financial and operational datasets.
The Employee Item Codes Expense view provides structured details on the various codes associated with employee expense items. It captures key attributes such as expense type, cost centre, project, programme, and other financial classification codes, ensuring precise tracking, classification, and reporting. This view enhances financial analysis and compliance by aligning expenses with business dimensions.

Use Case

Owner: Taariq Fry
Typical Use Case: Suitable for applications requiring detailed breakdowns of expense categorisation, supporting financial reporting, compliance audits, and cost allocation.
Style: Each row represents a unique employee expense record with associated classification codes.

You could create a dashboard that summarises key expense classification insights:

  • Total expenses classified by cost centre, project, and programme.
  • Breakdown of expenses by financial codes (e.g., PRU code, asset code).
  • Analysis of expense types and their distribution.
  • Identification of frequently used codes for expense tracking.

Source

IFS.QFACT_DETAIL_MILAGE_CFV_OL$

Integration with Dimension Tables:

Joins:

  • ifs.QDIM_EXPENSE_HEADER_CFV_OL$: Provides employee and company-related details for each expense item.
  • ifs.DIM_ACCOUNT_OL$: Links expense items with financial account codes.
  • ifs.DIM_CODE_B_OL$: Associates expenses with cost centres.
  • ifs.DIM_CODE_C_OL$: Provides programme-related information.
  • ifs.DIM_PROJECT_OL$: Enriches the data with project details.
  • ifs.DIM_CODE_E_OL$: Maps PRU codes to expenses.
  • ifs.DIM_CODE_F_OL$: Associates assets with expense entries.

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

  • Finance & Accounting Tables: Ensuring expense codes align with financial records.
  • Organisational Hierarchies: Supporting detailed financial and operational analysis.

Sample Exec

For sample data execution, use the code:

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

Response

Identifiers

  • COMPANY: Company associated with the expense.
  • EMP_NO (EMPLOYEE_ID): Identifier for the employee incurring the expense.

Expense Sequence (JSON Format)

  • EXPENSE_ID: Unique identifier for the expense entry.
  • SEQ_NO: Sequence number for the expense item.
  • ITEM_TYPE: Defines the item category (e.g., “Expense”).

Expense Classification Details

  • ATTRIBUTE: Describes the classification type (e.g., cost centre, project, PRU code).
  • ITEM_CODE: The corresponding code for the attribute.
  • ITEM_METADATA: Additional metadata, such as descriptions or related information.

Financial Metadata (JSON Format)

  • CURRENCY_CODE: Currency associated with the expense.
  • STATUS: Status of the expense entry.
  • SHEET_TYPE: Type of expense sheet.
  • AUTHORISER: Authoriser details if applicable.

Status (JSON Format)

  • ACTIVE_FLAG: Indicates whether the expense entry is active (1 = Active).
  • EXPIRY_DATE: Default expiry date for record tracking.

Audit & Tracking

  • CREATE_DATE: Timestamp of the record creation.
  • SOURCE_SYSTEM: Table name from which the data originates.

SQL Code

To access the full SQL structure, refer to…

BMT-DWH-DEV/Employee_Item_Codes_Expense.sql

Leave a Comment