Transaction details described

When importing general ledger transactions from a CSV or Excel file, you must map fields, so the transactions data display properly in the engagement. In addition, certain fields are consistent across lines within an entry. During an import, these fields will be stored separately, being populated with the values from the first entry line within the entry. See How general ledger data is stored upon import for more information.

This topic describes the fields within the TRANSACTION DETAILS category that appear in the General ledger dataset.

Note that some fields are mandatory for specific data analytics tests to function properly. To learn more see, Mandatory fields for data analytics.

Entry Number (entry_number)

Sequential identifier that is unique to each journal entry.

Usage

  • Groups transaction lines into transactions (if Entry ID isn't assigned).
  • Visual identifier for transactions.
  • If Entry Number and Entry ID are not assigned, each transaction is assigned a random unique identifier.
  • In the Sequence Gaps AnalyticsAI test.

Requirements

  • If Entry ID is not assigned, there must be no blank Entry Number cells.

  • If Entry ID is assigned, only the first entry number value for each unique Entry ID will be imported.

Amount (amount)

Amount of the transaction line. This can be a positive value for a debit and negative for a credit.

Usage

  • Data reconciliation/completeness.
  • In these AnalyticsAI tests:
    • Duplicate Entries
    • Ends in 999
    • High Amounts
    • Rounded Amounts
    • 2-Digit Benford's
    • Zero Amounts

Requirements

  • Positive and negative values can be included in either of:
    • One column with negative values formatted in a standard negative formatting. Examples include:
      • -4000.00
      • (4000.00)
      • 4000.00-
    • Separate columns tagged "debit" and "credit".
  • Commas or periods are interpreted as decimal separators.

Detail Comment (detail_comment)

Description of the line entry.

Usage

In these AnalyticsAI tests:

  • Missing Descriptions
  • Specific Keywords

Posting Date (posting_date)

Effective date - data entry was posted (validated) to the general ledger, regardless of the date created.

Usage

  • Associates transactions with a date and fiscal period.
  • In these AnalyticsAI tests:
    • Duplicate Entries
    • Unusual Days

Requirements

To be viewed in the transactions list, transactions must be within fiscal-year range as set within the engagement.

Date format must be properly set:

Day

D: One digit for days below 10 (for example, 2 for the 2nd day of the month)

DD: Two digits (for example, 02 for the 2nd day of the month)

DDD: Day of the year (for example, 365 for December 31st)

DDDD: Three-digit day of the year (for example, 002 for January 2nd)

Month

M: One digit for months below 10 (for example, 4 for April)

MM: Two digits (for example, 04 for April)

MMM: Three-letter abbreviation (for example, Apr for April)

MMMM: Full month name (for example, April)

Year

YY: Two digits (for example, 19 for 2019)

YYYY: Four digits (for example, 2019)

Time

H HH: Hours in 24-hour time (for example, 0 for midnight, 23 for 11 PM)

h hh: Hours in 12-hour time used with AM/PM (for example, 1 for 1 AM, 12 for noon)

k kk: Hours in 24-hour time from 1 to 24 (for example, 1 for 1 AM, 24 for midnight)

a A: Post or ante meridiem (for example, am for morning, pm for afternoon)

m mm: Minutes (for example, 0 for the top of the hour, 59 for the last minute of the hour)

s ss: Seconds (for example, 0 for the start of the minute, 59 for the last second of the minute)

S SS SSS ... SSSSSSSSS: Fractional seconds (for example, 0 for no fractional second, 999999999 for the last fractional second)

Z ZZ: Offset from UTC as +-HH:mm, +-HHmm, or Z (for example, +12:00 for 12 hours ahead of UTC)

Example Transaction

Let's say we have a transaction that occurred on April 2, 2019, at 1:30:45 PM with 123 milliseconds, and the UTC offset is +02:00. Here’s how it would look in different formats:

  • Date: 2/4/19 or 02/04/2019

  • Time: 13:30:45.123 +02:00

Posting Status (posting_status)

Indicates whether the entry has been posted or not posted.

Usage

  • Transactions whose Posting Status is set to non-posted are not used for calculating the sum of transactions for data completeness checks.
  • Transactions whose Posting Status is set to posted are used in AnalyticsAI tests.

To learn more, see AnalyticsAI test results.

Requirements

  • Less than 2,500 unique values. These values must be tagged to one of:

    • Posted
    • Non-posted

Document Type (document_type)

Enumerated field describing the original source document, with check, debit-memo, credit-memo, finance-charge, invoice, order-customer, order-vendor, payment-other, reminder, tegata, voucher, shipment, receipt, manual-adjustment, other.

Usage

  • Filtering transactions based on journal type.
  • In the Unusual Users AnalyticsAI test.

Requirements

  • Less than 2,500 unique values.
  • The data in the applicable column in the transaction import file (the CSV file) must be assigned to the document_type values below:
    • GJ - General Ledger Entries
    • CD - Cash Disbursement
    • CR - Cash Receipts
    • SJ - Sales Invoices
    • PJ - Purchases Invoices
    • PL - Payroll Ledger Entries
    • AR - Accounts Receivable Adjustments (credits, returns, other adjustments)
    • AP - Accounts Payable Adjustments (credits, returns, other adjustments)
    • FA - Fixed Assets Adjustments
    • IN - Inventory Adjustments
    • SC - Sales Contracts
    • SO - Sales Orders
    • PC - Purchases Contracts
    • PR - Purchase Requisitions
    • PO - Purchase Orders
    • GS - Goods Shipped
    • GR - Goods Received
    • OT - Other