Monthly Journal Line View


The monthly journal line view consolidates all journal entries across all financial records and aggregates those journal entries by calendar month. Each line of this view represents a monthly journal line.

The monthly JL view provides additional aggregation on top of the daily JE view. When generating monthly or annual JE reports, using the monthly JL view yeilds better query performance.

Query daily JL view

Monthly journal lines are stored in VW_AG_JOURNAL_ENTRIES_MONTHLY table. It can be accessed via Snowflake Data Sharing and Access.

LEAPFIN_IDunique identifier created by Leapfin
LEAPFIN_DATA_TYPEtype of financial record, i.e. "LeapfinLineItem", "LeapfinInvoice", etc. (see full list under "Financial Data Records" section)
DECODED_LEAPFIN_ID_VTdecoded 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 financial record, i.e. "LeapfinLineItem", "LeapfinInvoice", etc
- leapfin_suffix: additional string to distinguish different financial 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_IDunique identifier of the account. This is an internal id created by Leapfin.
DATA_VTall data fields of this financial record in json format.
MONTH_IDthe year-month of the journal entry. Value format "202201".
DOCUMENT_DATE_DTthe date when this financial record is created in external systems outside Leapfin. Value format "2022-01-31"
PRODUCT_NAMEthe name of the product associated with the financial record.
CURRENCY_CODE3-digit ISO code of AMOUNT field, e.g. “USD”, "GBP", etc. This is the primary currency of this record.
CUSTOM_FIELD_VTadditional fields (i.e. key-value pairs) about the financial record in JSON format
ACCOUNTthe 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_SIDEthe booking direction of the journal line. Acceptable values are "dr" and "cr".
JE_PAIR_VTthe journal entry that contains this journal lines. (refer "Journal entry" section for exact data structure)
AMOUNTthe 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_DRthe debit amount of the journal line.
AMOUNT_CRthe credit amount of the journal line.
CREATED_AT_DTthe date-time when the financial record is created by Leapfin. Value format: "2022-07-14 23:01:26.400"
UPDATED_AT_DTthe date-time when the financial record is last updated by Leapfin. Value format: "2022-09-01 06:09:57.379"