Monthly Journal Line View
Overview
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.
| Name | Description |
|---|---|
| LEAPFIN_ID | unique identifier created by Leapfin |
| LEAPFIN_DATA_TYPE | type of financial record, i.e. "LeapfinLineItem", "LeapfinInvoice", etc. (see full list under "Financial Data Records" section) |
| 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 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_ID | unique identifier of the account. This is an internal id created by Leapfin. |
| DATA_VT | all data fields of this financial record in json format. |
| MONTH_ID | the year-month of the journal entry. Value format "202201". |
| DOCUMENT_DATE_DT | the date when this financial record is created in external systems outside Leapfin. Value format "2022-01-31" |
| PRODUCT_NAME | the name of the product associated with the financial record. |
| CURRENCY_CODE | 3-digit ISO code of AMOUNT field, e.g. “USD”, "GBP", etc. This is the primary currency of this record. |
| CUSTOM_FIELD_VT | additional fields (i.e. key-value pairs) about the financial 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. (refer "Journal entry" section for exact data structure) |
| 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 financial record is created by Leapfin. Value format: "2022-07-14 23:01:26.400" |
| UPDATED_AT_DT | the date-time when the financial record is last updated by Leapfin. Value format: "2022-09-01 06:09:57.379" |
Updated about 1 year ago