Transaction

tenant_id

INTEGER

Identifier of the customer account (tenant) that owns the record.

id

UUID

Unique identifier of the transaction.

name

VARCHAR

Display name of the transaction, built from its type, vendor, and number, for example AP Invoice - Vendor - 001.

number

VARCHAR

Transaction number, such as the vendor invoice number on an AP invoice.

vendor_id

VARCHAR

Identifier of the vendor the transaction is for. Set on AP invoices, AP credit memos and AP payments, and on some bank expenses.

legal_entity_id

UUID

Identifier of the legal entity the transaction belongs to, derived from its location.

location_id

UUID

Identifier of the location the transaction is recorded against.

daily_sales_summary_id

VARCHAR

Identifier of the daily sales summary linked to the transaction. Populated only for journal entries created from daily sales summaries.

r365_daily_sales_summary_id

VARCHAR

Identifier of the R365 daily sales summary that generated the transaction. Populated for journal entries created from daily sales summaries.

date

DATE

Posting date of the transaction, the date it is recorded in the general ledger.

discount_date

DATE

Last date the early-payment discount can be taken on an AP invoice.

due_date

DATE

Date on which payment of the transaction is due.

to_location_id

VARCHAR

Identifier of the destination location on a transfer.

payment_terms_id

VARCHAR

Identifier of the payment terms applied to the transaction.

bank_reconciliation_id

VARCHAR

Identifier of the bank reconciliation that includes the transaction.

transfer_to_bank_reconciliation_id

VARCHAR

Identifier of the bank reconciliation that includes the receiving side of a bank transfer.

checking_account_id

VARCHAR

Identifier of the bank (checking) GL account used by the transaction.

first_account_id

VARCHAR

Identifier of the GL account on the first line item of the transaction.

offset_account_id

VARCHAR

Identifier of the GL account that offsets the transaction, such as the accounts payable account on an AP invoice.

tax_account_id

VARCHAR

The GL account that sales tax on the transaction is posted to. Used on AR invoices (for example catering invoices) and credit memos; blank otherwise.

transfer_to_id

VARCHAR

Identifier of the destination GL account of a bank transfer.

budget_master_id

VARCHAR

Currently not populated: this column is empty on every row.

budget_id

VARCHAR

Identifier of the budget that an older budget-period record belonged to. Budgets are now stored separately.

budget_group_id

VARCHAR

Not currently used. No value is available for this column.

deposit_id

VARCHAR

Not used on current data. On older budget records it held the identifier of the budget that a period record belonged to.

last_stock_count_id

VARCHAR

Identifier of the previous inventory count for the same count sheet. Set on inventory counts only.

template_transaction_id

VARCHAR

Identifier of the template transaction that this transaction was created from. On a voiding transaction, links back to the original transaction.

catering_event_id

VARCHAR

Identifier of the catering event the AR invoice or AR payment belongs to.

apply1_transaction_id

VARCHAR

On an AP invoice paid with Mark as Paid (auto-payment), the identifier of the payment created for it. Blank otherwise.

amount

NUMBER

Total amount of the transaction (the document amount).

amount_base

NUMBER

Transaction amount in the base currency. Rarely populated; use the amount column instead.

amount_remaining

NUMBER

Unpaid balance of the transaction. Does not always equal amount minus applied payments, for example with early-payment discounts, short payments, or write-offs.

amount_remaining_base

NUMBER

Amount still unpaid or unapplied, in the base currency. Rarely populated.

applied_amount

NUMBER

Amount of the transaction already applied to payments or credits.

applied_amount_base

NUMBER

Amount applied to the transaction (for example by payments or credits), in the base currency. Rarely populated.

apply1_amount

NUMBER

Not currently used. Empty on every row.

apply1_amount_base

NUMBER

Not currently used. Empty on every row.

credit_expected

NUMBER

Expected credit amount for short-pay transactions. The amount is not subtracted from the amount remaining. Apply it against the amount remaining as needed.

credit_expected_base

NUMBER

Expected vendor credit on an AP invoice, in the base currency. Only older data has values.

credit_expected_header

NUMBER

Older field for an expected vendor credit entered on the invoice header. Not used now.

credit_expected_header_base

NUMBER

Base-currency copy of the older header-level expected credit. Not used now.

credit_total

NUMBER

Total of the credit amounts across the transaction's distribution lines.

credit_total_base

NUMBER

Total of the credit lines in the base currency. Only older and mass-entered transactions.

debit_total

NUMBER

Total of the debit amounts across the transaction's distribution lines.

debit_total_base

NUMBER

Total of the debit lines in the base currency.

details_total

NUMBER

Sum of the amounts on the detail lines of the transaction.

details_total_base

NUMBER

Sum of the detail line amounts in the base currency.

taxable_amount

NUMBER

The part of the transaction amount that tax applies to. Rarely populated.

taxable_amount_base

NUMBER

Currently not populated: this column is always 0.

taxable_subtotal

NUMBER

Total of the lines on the transaction that are taxable. Used when sales tax is calculated on AR billing, and on invoices imported from vendors by EDI.

taxable_subtotal_base

NUMBER

Currently not populated: this column is always 0.

tax_amount

NUMBER

Sales tax on the transaction. Used on AR invoices (for example catering) and some AP invoices; zero or blank otherwise.

tax_amount_base

NUMBER

Sales tax in the base currency. Only older transactions have values.

checkrun_amount

NUMBER

Currently not populated: this column is empty on every row.

checkrun_amount_base

NUMBER

Currently not populated: this column is empty on every row.

checkrun_discount_amount

NUMBER

Currently not populated: this column is empty on every row.

checkrun_discount_amount_base

NUMBER

Currently not populated: this column is empty on every row.

unassigned_amount

NUMBER

Part of the transaction amount not yet spread across its lines. Stored only on older transactions; current screens calculate it on the fly.

unassigned_amount_base

NUMBER

Base-currency copy of the unassigned amount. Only older transactions.

first_amount

NUMBER

Amount on the first line item of the transaction.

first_amount_base

NUMBER

Base-currency copy of the first line's amount. Only older and mass-entered transactions.

_1099_amount

NUMBER

Amount reportable on a 1099 form. It can differ from the transaction total.

_1099_amount_base

NUMBER

Amount of the transaction reported on a 1099 form, in the base currency. Only set on AP invoices and bank expenses for 1099 vendors.

overhead

NUMBER

Total overhead (for example freight or other extra charges) on an AP invoice, on top of the item cost. It must equal the sum of the overhead on the invoice lines.

discount_amount

NUMBER

Early-payment discount amount on an AP invoice. Zero or blank when no discount applies.

tax_percent

NUMBER

Tax rate, as a percent, applied to the AR invoice.

_1099_box

INTEGER

Number of the 1099 box under which the transaction is reported.

action

INTEGER

Not currently used. A small number of older transactions (2012 to 2015) have a value from 0 to 5; the meaning is not defined.

approval_status

INTEGER

Approval status of the transaction. 1 = Unapproved, 2 = Approved.

budget_year

INTEGER

Fiscal year of an older budget record. Not meaningful on current transactions; the values 0 and 2000 are defaults.

legal_entity_attribute

INTEGER

Not currently used. Always 0.

location_attribute

INTEGER

Not currently used. Always 0.

payment_method

INTEGER

Method used to pay the transaction. 0 = Unassigned, 1 = ACH, 2 = Check, 3 = Auto direct debit, 4 = Credit card, 5 = R365 Payments, 6 = VCard.

print_status

INTEGER

Check print status. 0 = Unknown, 1 = Can print, 2 = Printed.

save_type

INTEGER

Not currently used. Empty on every row.

source

INTEGER

Code that identifies where the transaction came from or its subtype. The meaning of each code depends on the transaction type.

transaction_type

INTEGER

Type of the transaction. 1 = AP invoice, 2 = AP credit memo, 3 = AP payment, 4 = AR invoice, 5 = AR credit memo, 6 = AR payment, 7 = Journal entry, 8 = Bank expense, 9 = Budget detail, 10 = Bank transfer, 11 = Bank deposit, 12 = Budget, 13 = Inventory count, 14 = Item transfer, 15 = Waste log, 16 = Commissary fulfillment, 17 = Commissary order, 18 = Donation, 23 = Cost transfer, 58 = Daily sales summary, 65 = Prep log.

auto_recurrence

INTEGER

How often a recurring (memorized) or spread transaction repeats. 0 = Does not repeat, 1 = Monthly on the last day, 2 = Monthly on the same day, 3 = Every period on the last day, 4 = Weekly, 5 = Every 2 weeks, 6 = Monthly on the first day, 7 = Every period on the first day, 8 = Daily. Blank on ordinary transactions.

days_in_advance

INTEGER

For a recurring or spread transaction, how many days before its date the next transaction is created.

status

INTEGER

Operational status of inventory, commissary, and prep transactions. 1 = In progress, 2 = Completed, 3 = Approved.

cutoff_reminder_sent

INTEGER

Currently not populated: this column is empty on every row.

spread_type

INTEGER

Type of spread applied when the transaction spans multiple periods. 0 = None, 1 = Spread, 2 = Spread reversal, 3 = Memorized spread, 4 = Recurring spread.

times_to_repeat

INTEGER

How many times a recurring or spread transaction will be created. Blank or 0 means no limit was set.

times_repeated

INTEGER

How many times a recurring or spread transaction has been created so far.

schedule_status

INTEGER

Progress of a recurring or spread transaction. 0 = Not scheduled, 1 = Scheduled, 2 = Completed (it has repeated all its times).

mobile_qty

INTEGER

For stock counts only: the Mobile Qty Entry setting, which controls whether a quantity entered on a mobile device replaces the current counted quantity (1) or is added to it (2). It has no meaning on other transaction types.

workflow_status

INTEGER

Approval workflow state of the transaction. 1 = Non-workflow, 2 = Work needed, 3 = Pending approval, 4 = Approved, 5 = Researching, 6 = Imported. Used for AP invoices and journal entries only.

priority

INTEGER

Payment priority of the invoice. 10 = Critical, 20 = High, 30 = Standard, 40 = Low.

entry_type

INTEGER

Entry type of the transaction, used mainly for journal entries. 1 = Standard, 2 = Amortization, 3 = Adjustment, 4 = Beginning balance, 5 = Payroll.

origin_data_source

INTEGER

How the transaction was created. 0 = Unknown, 1 = Manual entry, 2 = EDI or file import, 3 = OCR, 4 = Import.

is_1099_vendor

BOOLEAN

Whether the vendor on the transaction was set up as a 1099 vendor when the transaction was saved. Blank on most current transactions.

is_beginning_balance

BOOLEAN

Indicates whether the transaction is a beginning balance journal entry.

is_brand_new

BOOLEAN

Not currently used. True only on a small number of older transactions (2013 to 2015).

is_count_complete

BOOLEAN

True when the transaction has reached the completed state. For inventory counts this shows as Count Complete. Not limited to inventory counts.

is_default_budget

BOOLEAN

True when the budget is the default budget. Only meaningful on budget records; populated only for older data.

is_deposit_now

BOOLEAN

On an accounts receivable payment, shows the Deposit Now box was ticked, meaning the payment was deposited to a bank (checking) account when it was entered. It has no reliable meaning on other transaction types.

is_excluded_from_1099

BOOLEAN

True when the transaction is left out of 1099 reporting. Voiding a transaction sets it to true.

is_franchising

BOOLEAN

True when the transaction is flagged for franchise billing, so its lines are picked up when franchisee billing runs.

is_individual_deposit

BOOLEAN

Not currently used. True only on a small number of older transactions (1997 to 2015).

is_in_use

BOOLEAN

For stock count templates, true when the template is in use (In Use checkbox). For system labor journal entries, true when payroll clearing has used the entry.

is_most_recent

BOOLEAN

Not meaningful for current data. Flag for the most recently paid invoice; only older rows (2013) have it.

is_only_one_detail

BOOLEAN

True when the transaction has only one detail (expense) line.

is_rapid

BOOLEAN

Not currently used. False on every row.

is_reconciled

BOOLEAN

Indicates whether the transaction has been reconciled with a bank statement.

is_short_pay

BOOLEAN

True when the transaction is flagged for short-pay.

is_template

BOOLEAN

Indicates whether the transaction is a template (memorized transaction) used to generate other transactions.

is_voided

BOOLEAN

Indicates whether the transaction has been voided.

is_voiding

BOOLEAN

Indicates whether the transaction is the reversal that voids another transaction.

is_locked

BOOLEAN

On commissary orders, true when the order has been locked so the ordering location can no longer change it.

is_related_to_asset

BOOLEAN

Indicates whether the transaction is linked to a fixed asset.

is_auto_create_payment

BOOLEAN

On an AP invoice, true when it is marked as paid so a payment is created automatically (Mark as Paid).

is_missing_items

BOOLEAN

Indicates whether the invoice has line items that are not yet received or matched.

is_on_payment_hold

BOOLEAN

Indicates whether the invoice is on payment hold and excluded from check runs.

is_has_issues

BOOLEAN

Indicates whether the transaction has validation issues or exceptions.

apply1_date

DATE

Not currently used. The only value is the placeholder date 1753-01-01.

approved_by

VARCHAR

User who approved the transaction.

approved_on

TIMESTAMP_NTZ

Date and time the transaction was approved.

count_time

TIMESTAMP_NTZ

Time of the inventory count (stock count Count Time). Often shows midnight when no time was set.

last_count_date

TIMESTAMP_NTZ

On a stock count template, the date of the previous count. 1900-01-01 means no earlier count.

payroll_start_date

DATE

Start date of the payroll period covered by a payroll journal entry.

payroll_end_date

DATE

End date of the payroll period covered by a payroll journal entry.

transfer_to_reconcile_date

DATE

Not currently used. A small number of older transactions have a date, mostly the placeholder 1900-01-01.

first_spread_date

DATE

First date of the period range a spread transaction covers.

last_spread_date

DATE

Last date of the period range a spread transaction covers.

doc_date

DATE

Date shown on the source document, such as the vendor invoice date.

completed_on

TIMESTAMP_NTZ

Date and time the transaction was completed.

accrual_start_date

DATE

Start date of the accrual period.

accrual_end_date

DATE

End date of the accrual period.

submit_time

TIMESTAMP_NTZ

When a commissary order was submitted by the ordering location. 1900-01-01 means it was not submitted.

address1

VARCHAR

First address line recorded on the transaction, copied from the vendor record and editable.

address2

VARCHAR

Second address line recorded on the transaction.

city

VARCHAR

City of the address recorded on the transaction.

state

VARCHAR

State of the address recorded on the transaction.

zip

VARCHAR

ZIP or postal code of the address recorded on the transaction.

country

VARCHAR

Country of the address recorded on the transaction.

comment

VARCHAR

Free-text comment or memo entered on the transaction.

credit_comment

VARCHAR

Comment that explains the expected credit, for example items missing from the delivery.

company_text

VARCHAR

Vendor name stored as text on the transaction, a snapshot used before or alongside the vendor record.

frequency

VARCHAR

How often the inventory count is performed, for example Weekly or Per Period.

commissary_location

VARCHAR

For a commissary order, the commissary location that supplies it. Blank on other transactions.

company_payment_name

VARCHAR

Name of the company the payment is made to. For AP invoices and payments this is the vendor name.

payment_expected

VARCHAR

On a payment, the expected catering payment (such as a deposit or installment due for a catering event) it relates to.

spread_account

VARCHAR

The general ledger account chosen when the transaction is spread over several periods. Blank when not spread.

spread_link

VARCHAR

Identifier that links a spread journal entry to its related spread transactions.

created_on

TIMESTAMP_NTZ

Date and time the transaction was created.

created_by

VARCHAR

User who created the transaction.

modified_on

TIMESTAMP_NTZ

Date and time the transaction was last modified.

modified_by

VARCHAR

User who last modified the transaction.