Transaction details described

When importing general ledger transactions from a CSV or Excel file, you must map fields, so the transactions data displays properly in the engagement. In addition, certain fields are consistent across lines within an entry.

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 analytic tests to function properly. Refer to the descriptions below.

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 Gap detection analytic 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 analytic tests:

    • Duplicate detection

    • Recurring numeric pattern

    • High amounts

    • Rounded values

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 analytic tests:

  • Missing information

  • 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 analytic tests:

    • Duplicates

    • Date entries

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 analytic tests.

To learn more, see View analytic test results for checklists.

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 items analytic 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