/
Understanding your Leapfin data schema
  • Verified

    • Understanding your Leapfin data schema

    Owned by Ian Fondrk (Unlicensed)
    Last updated: Jun 24, 2024 by Mark McGuire (Unlicensed)
    29 min read

    Table of contents

    Overview

    The data tables within your Leapfin data warehouse provide a reliable and efficient way to store, organize, and retrieve your Leapfin accounting data, supporting accurate and streamlined accounting processes. Data tables organize your accounting data in a structured format, with each table corresponding to a specific data point, such as accounting records, ledger accounts, and journal entries. Snowflake uses a relational database structure, allowing tables to be linked to run queries based on specific relationships. For example, you can link an invoice table with a journal entry table to associate specific invoices with their journal entries.

    This guide details each data table contained within your data warehouse and how they relate to one another.

    Data table relationships

    When querying data, it is important to understand how each table relates to one another to identify specific data in various tables. For example, you may need to identify specific dispute records when querying the journal entry table. As shown in the diagram below, for most tables (not all), the primary identifier is the LEAPFIN_ID column, which is the unique ID Leapfin assigns to each accounting record. Using the previous example, you could identify each specific dispute record based on its LEAPFIN_ID when querying the journal entry table.

    The chart below displays how each table relates to one another and identifies the common data columns between each.

    image-20240514-135236.png

    Journal entry data tables

    Journal entry tables aggregate journal entry data across all accounting records.

    VW_AG_JOURNAL_ENTRIES

    The VW_AG_JOURNAL_ENTRIES table aggregates all journal entries across all accounting records at the daily level. Each line of this table represents a daily journal line. This table is commonly used as the base for aggregated journal entry reports, such as when loading journal entry data to Netsuite. You can use this table to group journal entries across all accounting records.

    Name

    Description

    Name

    Description

    LEAPFIN_ID

    unique identifier created by Leapfin

    LEAPFIN_DATA_TYPE

    type of accounting record, i.e. "LeapfinLineItem", "LeapfinInvoice", etc.

    DECODED_LEAPFIN_ID_VT

    decoded version of LEAPFIN_ID in json format. It contains the following values:

    • external_code: the unique identifier of this record. This is external id received by Leapfin.

    • integration_id: the unique identifier of the data source. This is an internal id created by Leapfin.

    • leapfin_data_type: type of accounting record, i.e. "LeapfinLineItem", "LeapfinInvoice", etc

    • leapfin_suffix: additional string to distinguish different accounting records when the same external_code is received from the data source.

    • raw_data_type: type of raw data record received from the data source.

    ORGANIZATION_ID

    unique identifier of the account. This is an internal id created by Leapfin.

    DATA_VT

    all data fields of this accounting record in json format.

    DATE_DT

    the book date of the journal entry. Value format "2022-01-31"

    DOCUMENT_DATE_DT

    the date when this accounting record is created in external systems outside Leapfin. Value format "2022-01-31"

    PRODUCT_NAME

    the name of the product associated with the accounting record.

    CURRENCY_CODE

    3-digit ISO code of AMOUNT field, e.g. “USD”, "GBP", etc. This is the primary currency of this record.

    CUSTOM_FIELDS_VT

    additional fields (i.e. key-value pairs) about the accounting record in JSON format

    ACCOUNT

    the unique identifier of ledger account impacted by this journal line. To get the account name and other details, this ACCOUNT field can be joined with VW_LK_ACCOUNT table (see Chart of Accounts for details).

    ACCOUNTING_SIDE

    the booking direction of the journal line. Acceptable values are "dr" and "cr".

    JE_PAIR_VT

    the journal entry that contains this journal lines.

    JE_METADATA_VT

    Contains rule ID and description

    JE_MEMO_VT

    Contains journal entry tags

    AMOUNT

    the amount of the journal line. A positive number means that the booking direction of the journal line (e.g. "dr") matches with the increasing side of the ledger account (e.g. "dr"). A negative number means the booking direction (e.g. "cr") differs from the account side (e.g. "dr")

    AMOUNT_DR

    the debit amount of the journal line.

    AMOUNT_CR

    the credit amount of the journal line.

    CREATED_AT_DT

    the date-time when the accounting record is created by Leapfin. Value format: "2022-07-14 23:01:26.400"

    UPDATED_AT_DT

    the date-time when the accounting record is last updated by Leapfin. Value format: "2022-09-01 06:09:57.379"

    VW_AG_JOURNAL_ENTRIES_MONTHLY

    The VW_AG_JOURNAL_ENTRIES_MONTHLY table consolidates all journal entries across all accounting records and aggregates those journal entries by calendar month. Each line of this table represents a monthly journal entry line. This table provides additional aggregation on top of the VW_AG_JOURNAL_ENTRIES table. When generating monthly or annual journal entry reports, the VW_AG_JOURNAL_ENTRIES_MONTHLY table yields better query performance.

    Name

    Description

    Name

    Description

    LEAPFIN_ID

    unique identifier created by Leapfin

    LEAPFIN_DATA_TYPE

    type of accounting record, i.e. "LeapfinLineItem", "LeapfinInvoice", etc.

    DECODED_LEAPFIN_ID_VT

    decoded version of LEAPFIN_ID in json format. It contains the following values:

    •  external_code: the unique identifier of this record. This is external id received by Leapfin.

    •  integration_id: the unique identifier of the data source. This is an internal id created by Leapfin.

    •  leapfin_data_type: type of accounting record, i.e. "LeapfinLineItem", "LeapfinInvoice", etc

    •  leapfin_suffix: additional string to distinguish different accounting records when the same external_code is received from the data source.

    •  raw_data_type: type of raw data record received from the data source.

    ORGANIZATION_ID

    unique identifier of the account. This is an internal id created by Leapfin.

    DATA_VT

    all data fields of this accounting record in json format.

    MONTH_ID

    the year-month of the journal entry. Value format "202201".

    DOCUMENT_DATE_DT

    the date when this accounting record is created in external systems outside Leapfin. Value format "2022-01-31"

    PRODUCT_NAME

    the name of the product associated with the accounting record.

    CURRENCY_CODE

    3-digit ISO code of AMOUNT field, e.g. “USD”, "GBP", etc. This is the primary currency of this record.

    CUSTOM_FIELDS_VT

    additional fields (i.e. key-value pairs) about the accounting record in JSON format

    ACCOUNT

    the unique identifier of ledger account impacted by this journal line. To get the account name and other details. This ACCOUNT field can be joined with VW_LK_ACCOUNT table (see Chart of Accounts for details).

    ACCOUNTING_SIDE

    the booking direction of the journal line. Acceptable values are "dr" and "cr".

    JE_PAIR_VT

    the journal entry that contains this journal lines.

    JE_METADATA_VT

    Contains rule ID and description

    JE_MEMO_VT

    Contains journal entry tags

    AMOUNT

    the amount of the journal line. A positive number means that the booking direction of the journal line (e.g. "dr") matches with the increasing side of the ledger account (e.g. "dr"). A negative number means the booking direction (e.g. "cr") differs from the account side (e.g. "dr")

    AMOUNT_DR

    the debit amount of the journal line.

    AMOUNT_CR

    the credit amount of the journal line.

    CREATED_AT_DT

    the date-time when the accounting record is created by Leapfin. Value format: "2022-07-14 23:01:26.400"

    UPDATED_AT_DT

    the date-time when the accounting record is last updated by Leapfin. Value format: "2022-09-01 06:09:57.379"

    Accounting record data tables

    VW_FT_INVOICE

    This table aggregates data for all of your invoice accounting records in Leapfin.

    Column

    Description

    Column

    Description

    LEAPFIN_ID

    unique identifier created by Leapfin.

    DECODED_LEAPFIN_ID_VT

    decoded version of LEAPFIN_ID in json format. It contains following values:

    • external_code: unique identifier of this record. This is external id received by Leapfin.

    • integration_id: unique identifier of the data source. This is internal id created by Leapfin.

    • leapfin_data_type: type of accounting record, i.e. "LeapfinInvoice"

    • leapfin_suffix: additional string to distinguish different accounting records when the same external_code is received from the data source.

    • raw_data_type: type of raw data record received from the data source.

    ORGANIZATION_ID

    unique identifier of the account. This is internal id created by Leapfin.

    DATE_DT

    the date of when this Event happened.

    STATUS

    current status of this invoice. Acceptable values are:

    • "drafted": the invoice is created but has not been sent to customers

    • "open": the invoice is issued to customers for payment

    • "paid": the invoice is paid by customers

    • "uncollectible": the invoice is issued but never paid successfully. Payment is not longer expected to be received.

    • "voided": the invoice is canceled after issued to customers

    DUE_DATE_DT

    the date when the payment for this invoice is due.

    ISSUED_DATE_DT

    the date when this invoice is issued. If not issued yet, the value is null. In most cases this will be the same as date. However, if invoices can be in a 'drafted' state, then this date can be different.

    UNCOLLECTIBLE_DATE_DT

    the date when the payment for this invoice becomes uncollectible. If invoice has not become uncollectible or has been paid, then the value is null. Typically used for dunning/bad debt rule scenarios.

    CURRENCY_CODE

    3-digit ISO code, e.g. “USD”, "GBP", etc. This is the primary currency of this record.

    EXCHANGE_RATES_VT

    a list of exchange rates used to calculate currency conversions. Each rate contains following values:

    • currencyCode: 3-digit ISO code, e.g. “USD”, "GBP", etc. This is the target currency of this record.

    • rate: conversion rate from primary currency to target currency. i.e. amount in primary currency * rate = converted amount in target currency.

    PAID_DATE_DT

    the date when this invoice is paid. Typically used for invoice to payment matching.

    CUSTOM_FIELDS_VT

    additional fields (i.e. key-value pairs) about the accounting record in json format

    ACCOUNTING_TRANSACTIONS_VT

    a list of journal entries associated with this accounting record. Each item contains following values:

    • date: the date of the journal entry

    • entries: a list of journal lines within the journal entry. Each line contains following values:

      • account: unique identifier of the accounting account. This is an internal id created by Leapfin

      • amount: amount of money booked towards the account.

      • accountingSide: an indicator of whether the amount is a debt ("dr") or credit ("cr") to the account.

    • comments: additional fields (i.e. key-value pairs) on the journal entry in json format.

    TOTAL

    total amount of invoice including tax and discount. This amount is derived by Leapfin based on the Line Item, Tax, and Discount associated with the Invoice.

    SUBTOTAL

    total amount of invoice excluding tax and discount. This amount is derived by Leapfin based on the Line Item associated with the Invoice.

    EXTERNAL_TOTAL

    total amount of invoice including tax and discount. This amount is received from external source.

    EXTERNAL_SUBTOTAL

    total amount of invoice excluding tax and discount. This amount is received from external source.

    CREATED_AT_DT

    the date-time when the accounting record is created by Leapfin. Value format: "2022-07-14 23:01:26.400"

    UPDATED_AT_DT

    the date-time when the accounting record is last updated by Leapfin. Value format: "2022-09-01 06:09:57.379"

    VW_FT_LINE_ITEM

    This table aggregates data for all of your line item accounting records in Leapfin.

    Name

    Description

    Name

    Description

    LEAPFIN_ID

    unique identifier created by Leapfin

    DECODED_LEAPFIN_ID_VT

    decoded version of LEAPFIN_ID in json format. It contains the following values:

    • external_code: the unique identifier of this record. This is external id received by Leapfin.

    • integration_id: the unique identifier of the data source. This is an internal id created by Leapfin.

    • leapfin_data_type: type of accounting record, i.e. "LeapfinLineItem"

    • leapfin_suffix: additional string to distinguish different accounting records when the same external_code is received from the data source.

    • raw_data_type: type of raw data record received from the data source.

    ORGANIZATION_ID

    unique identifier of the account. This is an internal id created by Leapfin.

    DATE_DT

    the date when this line-item is created in billing system.

    START_DATE_DT

    for subscription item, this is the start date of the subscription.

    END_DATE_DT

    for subscription item, this is the end date of the subscription.

    DESCRIPTION

    general description of the line-item

    CURRENCY_CODE

    3-digit ISO code of AMOUNT field, e.g. “USD”, "GBP", etc. This is the primary currency of this record.

    DISCOUNT_AMOUNT

    amount of the discount associated with the line-item. The value should be non-negative.

    EXCHANGE_RATES_VT

    a list of exchange rates used to calculate currency conversions. Each rate contains the following values:

    • currencyCode: 3-digit ISO code, e.g. “USD”, "GBP", etc. This is the target currency of this record.

    • rate: conversion rate from primary currency to target currency. i.e. amount in primary currency * rate = converted amount in target currency.

    CUSTOM_FIELDS_VT

    additional fields (i.e. key-value pairs) about the accounting record in JSON format

    ACCOUNTING_TRANSACTIONS_VT

    a list of journal entries associated with this accounting record. Each item contains the following values:

    • date: the date of the journal entry

    • entries: a list of journal lines within the journal entry. Each line contains the following values:

      • account: unique identifier of the accounting account. This is an internal id created by Leapfin (refer to VW_LK_ACCOUNT).

      • amount: amount of money booked towards the account.

      • accountingSide: an indicator of whether the amount is a debt ("dr") or credit ("cr") to the acount.

    • comments: additional fields (i.e. key-value pairs) on the journal entry in JSON format.

    QUANTITY

    the number of units purchased.

    AMOUNT

    the amount of the purchased goods or services excluding tax and discount. The value should be non-negative.

    CREATED_AT_DT

    the date-time when the accounting record is created by Leapfin. Value format: "2022-07-14 23:01:26.400"

    UPDATED_AT_DT

    the date-time when the accounting record is last updated by Leapfin. Value format: "2022-09-01 06:09:57.379"

    VW_FT_DISCOUNT

    This table aggregates data for all of your discount accounting records in Leapfin.

    Name

    Description

    Name

    Description

    LEAPFIN_ID

    unique identifier created by Leapfin

    DECODED_LEAPFIN_ID_VT

    decoded version of LEAPFIN_ID in json format. It contains the following values:

    • external_code: the unique identifier of this record. This is external id received by Leapfin.

    • integration_id: the unique identifier of the data source. This is an internal id created by Leapfin.

    • leapfin_data_type: type of accounting record, i.e. "LeapfinDiscount"

    • leapfin_suffix: additional string to distinguish different accounting records when the same external_code is received from the data source.

    • raw_data_type: type of raw data record received from the data source.

    ORGANIZATION_ID

    unique identifier of the account. This is an internal id created by Leapfin.

    DATE_DT

    the date of when this discount is created in billing system.

    CURRENCY_CODE

    3-digit ISO code of amount field, e.g. “USD”, "GBP", etc. This is the primary currency of this record.

    DESCRIPTION

    A description of the Discount record.

    START_DATE_DT

    For Discounts that are subscription in nature (i.e. time-series based), this is the start date of the period.

    END_DATE_DT

    For Discounts that are subscription in nature (i.e. time-series based), this is the end date of the period.

    AMOUNT

    amount of money impacted by this Discount. The value should be non-negative. This is the primary amount of this record.

    EXCHANGE_RATES_VT

    a list of exchange rates used to calculate currency conversions. Each rate contains the following values:

    • currencyCode: 3-digit ISO code, e.g. “USD”, "GBP", etc. This is the target currency of this record.

    • rate: conversion rate from primary currency to target currency. i.e. amount in primary currency * rate = converted amount in target currency.

    CUSTOM_FIELDS_VT

    additional fields (i.e. key-value pairs) about the accounting record in JSON format

    ACCOUNTING_TRANSACTIONS_VT

    a list of journal entries associated with this accounting record. Each item contains the following values:

    • date: the date of the journal entry

    • entries: a list of journal lines within the journal entry. Each line contains the following values:

      • account: unique identifier of the accounting account. This is an internal id created by Leapfin (refer to VW_LK_ACCOUNT).

      • amount: amount of money booked towards the account.

      • accountingSide: an indicator of whether the amount is a debt ("dr") or credit ("cr") to the acount.

    • comments: additional fields (i.e. key-value pairs) on the journal entry in JSON format.

    CREATED_AT_DT

    the date-time when the accounting record is created by Leapfin. Value format: "2022-07-14 23:01:26.400"

    UPDATED_AT_DT

    the date-time when the accounting record is last updated by Leapfin. Value format: "2022-09-01 06:09:57.379"

    VW_FT_CREDIT

    This table aggregates data for all of your credit accounting records in Leapfin.

    Column

    Description

    Column

    Description

    LEAPFIN_ID

    unique identifier created by Leapfin.

    DECODED_LEAPFIN_ID_VT

    decoded version of LEAPFIN_ID in JSON format. It contains the following values:

    • external_code: unique identifier of this record. This is an external id received by Leapfin.

    • integration_id: unique identifier of the data source. This is an internal id created by Leapfin.

    • leapfin_data_type: type of accounting record, i.e. "LeapfinCredit"

    • leapfin_suffix: additional string to distinguish different accounting records when the same external_code is received from the data source.

    • raw_data_type: type of raw data record received from the data source.

    ORGANIZATION_ID

    Unique identifier for your account

    DATE_DT

    the date-time of when this credit was issued or applied.

    DESCRIPTION

    description of the Credit

    CURRENCY_CODE

    3-digit ISO code of AMOUNT field, e.g. “USD”, "GBP", etc. This is the primary currency of this record.

    AMOUNT

    amount of the credit. The amount should be non-negative. The direction of the credit (i.e. the accounting impact of the credit amount) is derived from the TYPE field.

    TYPE

    The type of credit. Acceptable values are:

    • "issuance": this is representing the credit is being issued.

    • "application": represents this credit record is being used.

    • "adjustment" represents this credit record is an adjustment of credits.

    START_DATE_DT

    the date when the credit becomes available.

    END_DATE_DT

    the date when the credit expires.

    EXCHANGE_RATES_VT

    a list of exchange rates used to calculate currency conversions. Each rate contains the following values:

    • currencyCode: 3-digit ISO code, e.g. “USD”, "GBP", etc. This is the target currency of this record.

    • rate: conversion rate from primary currency to target currency. i.e. amount in primary currency * rate = converted amount in target currency.

    CUSTOM_FIELDS_VT

    additional fields (i.e. key-value pairs) about the accounting record in json format

    ACCOUNTING_TRANSACTIONS_VT

    a list of journal entries associated with this accounting record. Each item contains the following values:

    • date: the date of the journal entry

    • entries: a list of journal lines within the journal entry. Each line contains the following values:

      • account: unique identifier of the accounting account. This is an internal id created by Leapfin (refer to VW_LK_ACCOUNT).

      • amount: amount of money booked towards the account.

      • accountingSide: an indicator of whether the amount is a debt ("dr") or credit ("cr") to the acount.

    • comments: additional fields (i.e. key-value pairs) on the journal entry in JSON format.

    CREATED_AT_DT

    the date-time when the accounting record is created by Leapfin. Value format: "2022-07-14 23:01:26.400"

    UPDATED_AT_DT

    the date-time when the accounting record is last updated by Leapfin. Value format: "2022-09-01 06:09:57.379"

    VW_FT_TAX

    This table aggregates data for all of your tax accounting records in Leapfin.

    Column

    Description

    Column

    Description

    LEAPFIN_ID

    unique identifier created by Leapfin

    DECODED_LEAPFIN_ID_VT

    decoded version of LEAPFIN_ID in JSON format. It contains the following values:

    • external_code: the unique identifier of this record. This is an external id received by Leapfin.

    • integration_id: the unique identifier of the data source. This is an internal id created by Leapfin.

    • leapfin_data_type: type of accounting record, i.e. "LeapfinTax"

    • leapfin_suffix: additional string to distinguish different accounting records when the same external_code is received from the data source.

    • raw_data_type: type of raw data record received from the data source.

    ORGANIZATION_ID

    unique identifier of the account. This is an internal id created by Leapfin.

    DATE_DT

    the date of when this tax is created in billing system.

    DESCRIPTION

    general description of the tax.

    CURRENCY_CODE

    3-digit ISO code of AMOUNT field, e.g. “USD”, "GBP", etc. This is the primary currency of this record

    AMOUNT

    the amount of the tax. The amount should be non-negative

    EXCHANGE_RATES_VT

    a list of exchange rates used to calculate currency conversions. Each rate contains the following values:

    • currencyCode: 3-digit ISO code, e.g. “USD”, "GBP", etc. This is the target currency of this record.

    • rate: conversion rate from primary currency to target currency. i.e. amount in primary currency * rate = converted amount in target currency.

    CUSTOM_FIELDS_VT

    additional fields (i.e. key-value pairs) about the accounting record in JSON format

    ACCOUNTING_TRANSACTIONS_VT

    a list of journal entries associated with this accounting record. Each item contains the following values:

    • date: the date of the journal entry

    • entries: a list of journal lines within the journal entry. Each line contains the following values:

      • account: the unique identifier of the accounting account. This is an internal id created by Leapfin (refer to VW_LK_ACCOUNT).

      • amount: the amount of money booked towards the account.

      • accountingSide: an indicator of whether the amount is a debt ("dr") or credit ("cr") to the account.

    • comments: additional fields (i.e. key-value pairs) on the journal entry in JSON format.

    CREATED_AT_DT

    the date-time when the accounting record is created by Leapfin. Value format: "2022-07-14 23:01:26.400"

    UPDATED_AT_DT

    the date-time when the accounting record is last updated by Leapfin. Value format: "2022-09-01 06:09:57.379"

    VW_FT_PAYMENT

    This table aggregates data for all of your payment accounting records in Leapfin.

    Column

    Description

    Column

    Description

    LEAPFIN_ID

    unique identifier created by Leapfin

    DECODED_LEAPFIN_ID_VT

    decoded version of LEAPFIN_ID in JSON format. It contains the following values:

    • external_code: the unique identifier of this record. This is an external id received by Leapfin.

    • integration_id: the unique identifier of the data source. This is an internal id created by Leapfin.

    • leapfin_data_type: type of accounting record, i.e. "LeapfinPayment"

    • leapfin_suffix: additional string to distinguish different accounting records when the same external_code is received from the data source.

    • raw_data_type: type of raw data record received from the data source.

    ORGANIZATION_ID

    unique identifier of the account. This is an internal id created by Leapfin.

    DATE_DT

    the date of when this payment is created by payment processor.

    STATUS

    current status of the payment. Acceptable values are:

    • "succeeded": the payment is successfully processed / settled.

    • "failed": the payment failed during processing.

    • "pending": the payment has been initiated but still being processed.

    DESCRIPTION

    general description of the payment .

    CURRENCY_CODE

    3-digit ISO code of AMOUNT field, e.g. “USD”, "GBP", etc. This is the primary currency of this record

    SUCCEEDED_DATE_DT

    the date when the payment is successfully processed / settled.

    EXCHANGE_RATES_VT

    a list of exchange rates used to calculate currency conversions. Each rate contains the following values:

    • currencyCode: 3-digit ISO code, e.g. “USD”, "GBP", etc. This is the target currency of this record.

    • rate: conversion rate from primary currency to target currency. i.e. amount in primary currency * rate = converted amount in target currency.

    CUSTOM_FIELDS_VT

    additional fields (i.e. key-value pairs) about the accounting record in JSON format

    ACCOUNTING_TRANSACTIONS_VT

    a list of journal entries associated with this accounting record. Each item contains the following values:

    • date: the date of the journal entry

    • entries: a list of journal lines within the journal entry. Each line contains the following values:

      • account: the unique identifier of the accounting account. This is an internal id created by Leapfin (refer to VW_LK_ACCOUNT).

      • amount: the amount of money booked towards the account.

      • accountingSide: an indicator of whether the amount is a debt ("dr") or credit ("cr") to the acount.

    • comments: additional fields (i.e. key-value pairs) on the journal entry in JSON format.

    AMOUNT

    the amount of the payment. The amount should be non-negative

    CREATED_AT_DT

    the date-time when the accounting record is created by Leapfin. Value format: "2022-07-14 23:01:26.400"

    UPDATED_AT_DT

    the date-time when the accounting record is last updated by Leapfin. Value format: "2022-09-01 06:09:57.379"

    VW_FT_REFUND

    This table aggregates data for all of your refund accounting records in Leapfin.

    Column

    Description

    Column

    Description

    LEAPFIN_ID

    unique identifier created by Leapfin

    DECODED_LEAPFIN_ID_VT

    decoded version of LEAPFIN_ID in JSON format. It contains the following values:

    • external_code: the unique identifier of this record. This is an external id received by Leapfin.

    • integration_id: the unique identifier of the data source. This is an internal id created by Leapfin.

    • leapfin_data_type: type of accounting record, i.e. "LeapfinRefund"

    • leapfin_suffix: additional string to distinguish different accounting records when the same external_code is received from the data source.

    • raw_data_type: type of raw data record received from the data source.

    ORGANIZATION_ID

    unique identifier of the account. This is an internal id created by Leapfin.

    DATE_DT

    the date of when this refund is created by payment processor.

    CURRENCY_CODE

    3-digit ISO code of AMOUNT field, e.g. “USD”, "GBP", etc. This is the primary currency of this record.

    AMOUNT

    amount of the refund. The amount should be non-negative.

    STATUS

    current status of the payment. Acceptable values are:

    • "succeeded": the refund is successfully processed.

    • "failed": the refund failed during processing.

    • "pending": the refund has been initiated but still being processed.

    EXCHANGE_RATES_VT

    a list of exchange rates used to calculate currency conversions. Each rate contains the following values:

    • currencyCode: 3-digit ISO code, e.g. “USD”, "GBP", etc. This is the target currency of this record.

    • rate: conversion rate from primary currency to target currency. i.e. amount in primary currency * rate = converted amount in target currency.

    CUSTOM_FIELDS_VT

    additional fields (i.e. key-value pairs) about the accounting record in JSON format

    ACCOUNTING_TRANSACTIONS_VT

    a list of journal entries associated with this accounting record. Each item contains the following values:

    • date: the date of the journal entry

    • entries: a list of journal lines within the journal entry. Each line contains the following values:

      • account: the unique identifier of the accounting account. This is an internal id created by Leapfin (refer to VW_LK_ACCOUNT).

      • amount: the amount of money booked towards the account.

      • accountingSide: an indicator of whether the amount is a debt ("dr") or credit ("cr") to the acount.

    • comments: additional fields (i.e. key-value pairs) on the journal entry in JSON format.

    CREATED_AT_DT

    the date-time when the accounting record is created by Leapfin. Value format: "2022-07-14 23:01:26.400"

    UPDATED_AT_DT

    the date-time when the accounting record is last updated by Leapfin. Value format: "2022-09-01 06:09:57.379"

    VW_FT_DISPUTE

    This table aggregates data for all of your dispute accounting records in Leapfin.

    Column

    Description

    Column

    Description

    LEAPFIN_ID

    unique identifier created by Leapfin.

    DECODED_LEAPFIN_ID_VT

    decoded version of LEAPFIN_ID in json format. It contains following values:

    • external_code: unique identifier of this record. This is external id received by Leapfin.

    • integration_id: unique identifier of the data source. This is internal id created by Leapfin.

    • leapfin_data_type: type of accounting record, i.e. "LeapfinDispute"

    • leapfin_suffix: additional string to distinguish different accounting records when the same external_code is received from the data source.

    • raw_data_type: type of raw data record received from the data source.

    ORGANIZATION_ID

    unique identifier of the account. This is internal id created by Leapfin.

    DATE_DT

    the date of when this dispute is created by payment processor.

    DESCRIPTION

    description of the dispute. Use this field to explain the reason of dispute.

    CURRENCY_CODE

    3-digit ISO code of amount field, e.g. “USD”, "GBP", etc. This is the primary currency of this record.

    AMOUNT

    amount of money impacted by this record. The value should be non-negative. This is the primary amount of this record.

    INITIATED_DATE_DT

    the date when this dispute is formally initiated (excluding the informational inquiry stage). This is also the date when funds are withdrawn by payment processors from merchant's account.

    RESOLVED_DATE_DT

    the date when this dispute is closed. If the dispute decision is in favor of the merchant, this is also the date when funds are returned to merchant's account.

    EXCHANGE_RATES_VT

    a list of exchange rates used to calculate currency conversions. Each rate contains following values:

    • currencyCode: 3-digit ISO code, e.g. “USD”, "GBP", etc. This is the target currency of this record.

    • rate: conversion rate from primary currency to target currency. i.e. amount in primary currency * rate = converted amount in target currency.

    STATUS

    the status of the dispute. Acceptable values are: "pending" - the dispute has been initiated and under investigation. "lost" - the dispute is closed in favor of the customer or issuer. "won" - the dispute is closed in favor of the merchant.

    CUSTOM_FIELDS_VT

    additional fields (i.e. key-value pairs) about the accounting record in json format

    ACCOUNTING_TRANSACTIONS_VT

    a list of journal entries associated with this accounting record. Each item contains following values:

    • date: the date of the journal entry

    • entries: a list of journal lines within the journal entry. Each line contains following values:

      • account: unique identifier of the accounting account. This is an internal id created by Leapfin (refer to VW_LK_ACCOUNT).

      • amount: amount of money booked towards the account.

      • accountingSide: an indicator of whether the amount is a debt ("dr") or credit ("cr") to the acount.

    • comments: additional fields (i.e. key-value pairs) on the journal entry in json format.

    CREATED_AT_DT

    the date-time when the accounting record is created by Leapfin. Value format: "2022-07-14 23:01:26.400"

    UPDATED_AT_DT

    the date-time when the accounting record is last updated by Leapfin. Value format: "2022-09-01 06:09:57.379"

    VW_FT_SETTLEMENT

    This table aggregates data for all of your payout/settlement/disbursement accounting records in Leapfin.

    Column

    Description

    Column

    Description

    LEAPFIN_ID

    A unique identifier created by Leapfin.

    DECODED_LEAPFIN_ID_VT

    The decoded version of LEAPFIN_ID in JSON format, which contains the following values:

    • external_code: The unique identifier for the record sent to Leapfin by the payment processor.

    • integration_id: The unique identifier of the data source created internally by Leapfin.

    • leapfin_data_type: A type of accounting record, such as LeapfinSettlement.

    • leapfin_suffix: An additional string that helps differentiate accounting records that have the same external_code.

    • raw_data_type: A type of raw data record received from the data source.

    ORGANIZATION_ID

    A unique internal identifier of the account created by Leapfin.

    DATE_DT

    The date the payment processor created the payout/settlement/disbursement.

    DESCRIPTION

    The description of the payout/settlement/disbursement.

    CURRENCY_CODE

    The 3-digit ISO code of the amount field, such as USD or GBP. This code indicates the primary currency of the record.

    AMOUNT

    The primary monetary amount impacted by the record.

    • A positive amount indicates a transfer of funds from the payment processor’s wallet to the merchant's bank account.

    • A negative amount indicates the opposite flow of funds.

    STATUS

    The status of the payout/settlement/disbursement. Acceptable values include:

    • Paid: The payout/settlement/disbursement was successfully processed

    • Failed: The payout/settlement/disbursement failed during processing

    • Pending: The payout/settlement/disbursement was initiated but is still being processed

    EXCHANGE_RATES_VT

    A list of exchange rates used to calculate currency conversions. Each rate contains the following values:

    • currencyCode: The 3-digit ISO code of the amount field, such as USD or GBP. This code indicates the primary currency of the record.

    • rate: The conversion rate from primary currency to target currency (amount in primary currency * rate = converted amount in target currency).

    CUSTOM_FIELDS_VT

    Additional fields associated with the accounting record in JSON format, such as key-value pairs.

    ACCOUNTING_TRANSACTIONS_VT

    A list of journal entries associated with the accounting record. Each item contains the following values:

    • date: The date of the journal entry

    • entries: A list of journal lines within the journal entry. Each line contains the following values:

      • account: A unique identifier of the accounting account. This is an internal id created by Leapfin.

      • amount: The amount of money booked towards the account.

      • accountingSide: An indicator of whether the amount is a debt (dr) or credit (cr) to the account.

    • comments: Additional fields, such as key-value pairs, on the journal entry in JSON format.

    CREATED_AT_DT

    The date and time the accounting record was created by Leapfin. Value Format example: 2022-07-14 23:01:26.400

    UPDATED_AT_DT

    The date and time the accounting record was last updated by Leapfin. Format example: 2022-09-01 06:09:57.379

    VW_FT_FEE

    This table aggregates data for all of your fee accounting records in Leapfin.

    Column

    Description

    Column

    Description

    LEAPFIN_ID

    unique identifier created by Leapfin.

    DECODED_LEAPFIN_ID_VT

    decoded version of LEAPFIN_ID in json format. It contains following values:

    • external_code: unique identifier of this record. This is external id received by Leapfin.

    • integration_id: unique identifier of the data source. This is internal id created by Leapfin.

    • leapfin_data_type: type of accounting record, i.e. "LeapfinFee"

    • leapfin_suffix: additional string to distinguish different accounting records when the same external_code is received from the data source.

    • raw_data_type: type of raw data record received from the data source.

    ORGANIZATION_ID

    unique identifier of the account. This is internal id created by Leapfin.

    DATE_DT

    the date of when this fee is created by payment processor.

    DESCRIPTION

    description of the fee. Use this field to explain the reason of fee.

    CURRENCY_CODE

    3-digit ISO code of amount field, e.g. “USD”, "GBP", etc. This is the primary currency of this record.

    AMOUNT

    amount of money impacted by this record. The value should be non-negative. This is the primary amount of this record.

    EXCHANGE_RATES_VT

    a list of exchange rates used to calculate currency conversions. Each rate contains following values:

    • currencyCode: 3-digit ISO code, e.g. “USD”, "GBP", etc. This is the target currency of this record.

    • rate: conversion rate from primary currency to target currency. i.e. amount in primary currency * rate = converted amount in target currency.

    CUSTOM_FIELDS_VT

    additional fields (i.e. key-value pairs) about the accounting record in json format

    ACCOUNTING_TRANSACTIONS_VT

    a list of journal entries associated with this accounting record. Each item contains following values:

    • date: the date of the journal entry

    • entries: a list of journal lines within the journal entry. Each line contains following values:

      • account: unique identifier of the accounting account. This is an internal id created by Leapfin (refer to VW_LK_ACCOUNT).

      • amount: amount of money booked towards the account.

      • accountingSide: an indicator of whether the amount is a debt ("dr") or credit ("cr") to the acount.

    • comments: additional fields (i.e. key-value pairs) on the journal entry in json format.

    CREATED_AT_DT

    the date-time when the accounting record is created by Leapfin. Value format: "2022-07-14 23:01:26.400"

    UPDATED_AT_DT

    the date-time when the accounting record is last updated by Leapfin. Value format: "2022-09-01 06:09:57.379"

    VW_FT_RECOGNITION_EVENT

    This table aggregates data for all of your event accounting records in Leapfin.

    olumn

    Description

    olumn

    Description

    LEAPFIN_ID

    unique identifier created by Leapfin.

    DECODED_LEAPFIN_ID_VT

    decoded version of LEAPFIN_ID in JSON format. It contains the following values:

    • external_code: unique identifier of this record. This is an external id received by Leapfin.

    • integration_id: unique identifier of the data source. This is an internal id created by Leapfin.

    • leapfin_data_type: type of accounting record, i.e. "LeapfinRecognitionEvent"

    • leapfin_suffix: additional string to distinguish different accounting records when the same external_code is received from the data source.

    • raw_data_type: type of raw data record received from the data source.

    ORGANIZATION_ID

    unique identifier of the account. This is an internal id created by Leapfin.

    DATE_DT

    the date of when this Event happened.

    CURRENCY_CODE

    3-digit ISO code of amount field, e.g. “USD”, "GBP", etc. This is the primary currency of this record.

    AMOUNT

    amount of money impacted by this Event. The value should be non-negative. This is the primary amount of this record.

    EXCHANGE_RATES_VT

    a list of exchange rates used to calculate currency conversions. Each rate contains the following values:

    • currencyCode: 3-digit ISO code, e.g. “USD”, "GBP", etc. This is the target currency of this record.

    • rate: conversion rate from primary currency to target currency. i.e. amount in primary currency * rate = converted amount in target currency.

    CUSTOM_FIELDS_VT

    additional fields (i.e. key-value pairs) about the accounting record in json format

    ACCOUNTING_TRANSACTIONS_VT

    a list of journal entries associated with this accounting record. Each item contains the following values:

    • date: the date of the journal entry

    • entries: a list of journal lines within the journal entry. Each line contains the following values:

      • account: unique identifier of the accounting account. This is an internal id created by Leapfin (refer to VW_LK_ACCOUNT).

      • amount: amount of money booked towards the account.

      • accountingSide: an indicator of whether the amount is a debt ("dr") or credit ("cr") to the acount.

    • comments: additional fields (i.e. key-value pairs) on the journal entry in JSON format.

    CREATED_AT_DT

    the date-time when the accounting record is created by Leapfin. Value format: "2022-07-14 23:01:26.400"

    UPDATED_AT_DT

    the date-time when the accounting record is last updated by Leapfin. Value format: "2022-09-01 06:09:57.379"

    VW_FT_CREDIT_NOTE

    This table aggregates data for all of your credit note accounting records in Leapfin.

    Column

    Description

    Column

    Description

    LEAPFIN_ID

    unique identifier created by Leapfin.

    DECODED_LEAPFIN_ID_VT

    decoded version of LEAPFIN_ID in json format. It contains following values:

    • external_code: unique identifier of this record. This is external id received by Leapfin.

    • integration_id: unique identifier of the data source. This is internal id created by Leapfin.

    • leapfin_data_type: type of accounting record, i.e. "LeapfinCreditNote"

    • leapfin_suffix: additional string to distinguish different accounting records when the same external_code is received from the data source.

    • raw_data_type: type of raw data record received from the data source.

    ORGANIZATION_ID

    unique identifier of the account. This is internal id created by Leapfin.

    DATE_DT

    the date of when this credit note is created by payment processor.

    ISSUED_DATE_DT

    the date when this credit note is issued. If not issued yet, the value is null.

    CURRENCY_CODE

    3-digit ISO code, e.g. “USD”, "GBP", etc. This is the primary currency of this record.

    DESCRIPTION

    general description of the credit note.

    STATUS

    current status of this credit note. Acceptable values are:

    • "issued": the credit note is issued to customers.

    • "voided": the credit note is canceled after issued to customers

    EXTERNAL_TOTAL

    total amount of invoice including tax and discount. This amount is received from external source.

    EXTERNAL_SUBTOTAL

    total amount of invoice excluding tax and discount. This amount is received from external source.

    EXCHANGE_RATES_VT

    a list of exchange rates used to calculate currency conversions. Each rate contains following values:

    • currencyCode: 3-digit ISO code, e.g. “USD”, "GBP", etc. This is the target currency of this record.

    • rate: conversion rate from primary currency to target currency. i.e. amount in primary currency * rate = converted amount in target currency.

    CUSTOM_FIELDS_VT

    additional fields (i.e. key-value pairs) about the accounting record in json format

    ACCOUNTING_TRANSACTIONS_VT

    a list of journal entries associated with this accounting record. Each item contains following values:

    • date: the date of the journal entry

    • entries: a list of journal lines within the journal entry. Each line contains following values:

      • account: unique identifier of the accounting account. This is an internal id created by Leapfin (refer to VW_LK_ACCOUNT).

      • amount: amount of money booked towards the account.

      • accountingSide: an indicator of whether the amount is a debt ("dr") or credit ("cr") to the acount.

    • comments: additional fields (i.e. key-value pairs) on the journal entry in json format.

    CREATED_AT_DT

    the date-time when the accounting record is created by Leapfin. Value format: "2022-07-14 23:01:26.400"

    UPDATED_AT_DT

    the date-time when the accounting record is last updated by Leapfin. Value format: "2022-09-01 06:09:57.379"

    VW_FT_CONTRACT

    This table aggregates data for all of your contract accounting records in Leapfin.

    Name

    Description

    Name

    Description

    LEAPFIN_ID

    unique identifier created by Leapfin

    DECODED_LEAPFIN_ID_VT

    decoded version of LEAPFIN_ID in json format. It contains the following values:

    • external_code: the unique identifier of this record. This is external id received by Leapfin.

    • integration_id: the unique identifier of the data source. This is an internal id created by Leapfin.

    • leapfin_data_type: type of accounting record, i.e. "LeapfinContract"

    • leapfin_suffix: additional string to distinguish different accounting records when the same external_code is received from the data source.

    • raw_data_type: type of raw data record received from the data source.

    ORGANIZATION_ID

    unique identifier of the account. This is an internal id created by Leapfin.

    DATE_DT

    the date-time of when this contract is created in order system.

    AMOUNT

    total amount of the contract.. The value should be non-negative.

    CURRENCY_CODE

    3-digit ISO code of AMOUNT field, e.g. “USD”, "GBP", etc. This is the primary currency of this record.

    STATUS

    current status of the contract. Acceptable values are:

    • "Draft" - the contract is either being created or waiting to be signed by the customer.

    • "Signed" - the contract is approved/signed by all parties.

    • "Terminated" - the contract is cancelled either before or after the customer has signed it.

    DESCRIPTION

    a general description about the contract

    START_DATE_DT

    the start date-time of the effective period of the contract. Start date is inclusive.

    END_DATE_DT

    the end date-time of the effective period of the contract. Start date is inclusive.

    SIGNED_DATE_DT

    the date-time when this contract is signed / approved by all parties.

    TERMINATED_DATE_DT

    the date-time when this contract is terminated or cancelled

    EXCHANGE_RATES_VT

    a list of exchange rates used to calculate currency conversions. Each rate contains the following values:

    • currencyCode: 3-digit ISO code, e.g. “USD”, "GBP", etc. This is the target currency of this record.

    • rate: conversion rate from primary currency to target currency. i.e. amount in primary currency * rate = converted amount in target currency.

    CUSTOM_FIELDS_VT

    additional fields (i.e. key-value pairs) about the accounting record in JSON format

    ACCOUNTING_TRANSACTIONS_VT

    a list of journal entries associated with this accounting record. Each item contains the following values:

    • date: the date of the journal entry

    • entries: a list of journal lines within the journal entry. Each line contains the following values:

      • account: unique identifier of the accounting account. This is an internal id created by Leapfin (refer to VW_LK_ACCOUNT).

      • amount: amount of money booked towards the account.

      • accountingSide: an indicator of whether the amount is a debt ("dr") or credit ("cr") to the acount.

    • comments: additional fields (i.e. key-value pairs) on the journal entry in JSON format.

    CREATED_AT_DT

    the date-time when the accounting record is created by Leapfin. Value format: "2022-07-14 23:01:26.400"

    UPDATED_AT_DT

    the date-time when the accounting record is last updated by Leapfin. Value format: "2022-09-01 06:09:57.379"

    Account tables

    Your chart of account information is stored in the VW_LK_ACCOUNT and VW_LK_ACCOUNT_GROUP tables.

    VW_LK_ACCOUNT

    The VW_LK_ACCOUNT table contains details of each individual ledger account. The accounts that populate this table are also displayed on the Revenue tab in the Leapfin app as follows:

    image-20240109-200108.png

    Column Name

    Description

    Column Name

    Description

    ACCOUNT_ID

    the serial number of ledger account.

    ACCOUNT

    the unique identifier of ledger account

    ORGANIZATION_ID

    the unique identifier of the account. This is an internal id created by Leapfin.

    DISPLAY_NAME

    the display name of the ledger account

    INCREASE_SIDE

    the increasing side of the ledger account. Acceptable values are "dr" and "cr". For example, if increasing side is "dr", the all debts will be displayed as positive numbers and all credits will be negative.

    ACCOUNT_NUMBER

    the numeric value presenting the ledger account

    ACCOUNT_GROUP_ID

    the serial number of the account group. To get account group details, join with VW_LK_ACCOUNT_GROUP table.

    ACCOUNT_RANK

    the numeric order of the ledger account, indicating the sequence display on Leapfin's revenue page and report. Account with smaller value will be display on top within the account group.

    CREATED_AT_DT

    the date-time when the ledger account is created by Leapfin. Value format: "2022-07-14 23:01:26.400"

    UPDATED_AT_DT

    the date-time when the ledger account is last updated by Leapfin. Value format: "2022-07-14 23:01:26.400"

    VW_LK_ACCOUNT_GROUP

    The VW_LK_ACCOUNT_GROUP table contains details of account groups within your chart of accounts. The account groups that populate this table are also displayed on the Revenue tab in the Leapfin app as follows:

    image-20240109-195923.png

    Column Name

    Description

    Column Name

    Description

    ACCOUNT_GROUP_ID

    the serial number of the account group. This is also the unique identifier created by Leapfin

    ORGANIZATION_ID

    the unique identifier of the account. This is an internal id created by Leapfin.

    ACCOUNT_GROUP_DESC

    the display name of the account group

    RANK

    the numeric order of the account group, indicating the sequence display on Leapfin's revenue page and report. Account group with smaller value will be display on top.

    CREATED_AT_DT

    the date-time when the account group is created by Leapfin. Value format: "2022-07-14 23:01:26.400"

    UPDATED_AT_DT

    the date-time when the account group is last updated by Leapfin. Value format: "2022-07-14 23:01:26.400"

    Linking tables

    VW_RL_LINK

    Accounting records are not independent and can form complex relationships with other accounting records. Within Leapfin, the relationships among accounting records are called links. Knowing these links is critical to correctly derive the financial impacts of the transaction. For example, an invoice record contains multiple line-item records. Each line-item record may be associated with a tax record. When the invoice is paid, a payment record is created for that particular invoice record. The payment record may be later refunded as a refund or credit record.

    The VW_RL_LINK table aggregates data related to how each accounting record links to one another. Details regarding the linked records are represented in fields with the term “LINKED_”. Within this table, each link is represented as a row. Rows with the same LEAPFIN_ID and different LINKED_LEAPFIN_IDs, represent a record with multiple links.

    Column Name

    Description

    Column Name

    Description

    CREATED_AT_DT

    The timestamp of when the link is created

    DECODED_LEAPFIN_ID_VT

    decoded version of LEAPFIN_ID in JSON format. It contains the following values:

    • external_code: the unique identifier of this record. This is an external id received by Leapfin.

    • integration_id: the unique identifier of the data source. This is an internal id created by Leapfin.

    • leapfin_data_type: type of accounting record, i.e. "LeapfinRecognitionEvent"

    • leapfin_suffix: additional string to distinguish different accounting records when the same external_code is received from the data source.

    • raw_data_type: type of raw data record received from the data source.

    DECODED_LINKED_LEAPFIN_ID_VT

    decoded version of the Linked LEAPFIN_ID in JSON format. The values resemble what is shown above.

    LEAPFIN_DATA_TYPE

    Type of Leapfin record

    LEAPFIN_ID

    unique identifier created by Leapfin.

    LINKED_LEAPFIN_DATA_TYPE

    Type of Linked Leapfin record

    LINKED_LEAPFIN_ID

    Unique identifier of the linked Leapfin record

    ORGANIZATION_ID

    The unique identifier of the organization

    UPDATED_AT_DT

    The Timestamp of when the Record Link was last updated

     

    Related content