Transaction

Prev Next

Financial transactions including AP invoices, AP payments, AR invoices, and journal entries, with one row per transaction.

Before querying this table, complete Snowflake Data Share setup. See Snowflake Data Share Setup for step-by-step instructions.


Column reference

Column

Type

Description

tenant_id

NUMBER(38,0)

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

id

VARCHAR

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

VARCHAR

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

location_id

VARCHAR

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(38,2)

Total amount of the transaction (the document amount).

amount_base

NUMBER(38,2)

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

amount_remaining

NUMBER(38,2)

Unpaid balance of the transaction, as recorded in the source system. It does not always equal the amount less applied payments and credits, for example with early-payment discounts, short payments, or write-offs.

amount_remaining_base

NUMBER(38,2)

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

applied_amount

NUMBER(38,2)

Amount of the transaction already applied to payments or credits.

applied_amount_base

NUMBER(38,2)

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

apply1_amount

NUMBER(38,4)

Not currently used. Empty on every row.

apply1_amount_base

NUMBER(38,4)

Not currently used. Empty on every row.

credit_expected

NUMBER(38,2)

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(38,2)

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

credit_expected_header

NUMBER(38,2)

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

credit_expected_header_base

NUMBER(38,2)

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

credit_total

NUMBER(38,2)

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

credit_total_base

NUMBER(38,2)

Total of the credit lines in the base currency; mirrors the credit total on older and mass-entered transactions.

debit_total

NUMBER(38,2)

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

debit_total_base

NUMBER(38,2)

Total of the debit lines in the base currency; mirrors the debit total.

details_total

NUMBER(38,2)

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

details_total_base

NUMBER(38,2)

Sum of the detail line amounts in the base currency.

taxable_amount

NUMBER(38,2)

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

taxable_amount_base

NUMBER(38,2)

Currently not populated: this column is always 0.

taxable_subtotal

NUMBER(38,2)

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(38,2)

Currently not populated: this column is always 0.

tax_amount

NUMBER(38,2)

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

tax_amount_base

NUMBER(38,2)

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

checkrun_amount

NUMBER(38,4)

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

checkrun_amount_base

NUMBER(38,4)

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

checkrun_discount_amount

NUMBER(38,4)

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

checkrun_discount_amount_base

NUMBER(38,4)

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

unassigned_amount

NUMBER(38,2)

Part of the transaction amount not yet spread across its lines (header amount minus line total). Stored only on older transactions; current screens calculate it on the fly.

unassigned_amount_base

NUMBER(38,2)

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

first_amount

NUMBER(38,2)

Amount on the first line item of the transaction.

first_amount_base

NUMBER(38,2)

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

_1099_amount

NUMBER(38,2)

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

_1099_amount_base

NUMBER(38,2)

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

overhead

NUMBER(38,2)

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(38,2)

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

tax_percent

NUMBER(38,10)

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

_1099_box

NUMBER(38,0)

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

action

NUMBER(38,0)

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

NUMBER(38,0)

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

budget_year

NUMBER(38,0)

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

legal_entity_attribute

NUMBER(38,0)

Not currently used. Always 0.

location_attribute

NUMBER(38,0)

Not currently used. Always 0.

payment_method

NUMBER(38,0)

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

NUMBER(38,0)

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

save_type

NUMBER(38,0)

Not currently used. Empty on every row.

source

NUMBER(38,0)

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

transaction_type

NUMBER(38,0)

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

NUMBER(38,0)

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

NUMBER(38,0)

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

status

NUMBER(38,0)

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

cutoff_reminder_sent

NUMBER(38,0)

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

spread_type

NUMBER(38,0)

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

NUMBER(38,0)

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

times_repeated

NUMBER(38,0)

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

schedule_status

NUMBER(38,0)

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

mobile_qty

NUMBER(38,0)

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

NUMBER(38,0)

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

NUMBER(38,0)

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

entry_type

NUMBER(38,0)

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

origin_data_source

NUMBER(38,0)

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.