General ledger

1-- core.netsuite.general_ledger
2-- Every posting NetSuite made to the general ledger, one row per accounting line,
3-- with the account, its type and the line's subsidiary and dimensions.
4--
5-- Grain: transaction x transaction line x accounting book. Keep
6-- accounting_book_id in every GROUP BY: an account with Multi-Book Accounting
7-- posts the same transaction once per book, and summing across books counts it
8-- twice. Without Multi-Book there is only the primary book.
9--
10-- Currency: the base currency of the line's subsidiary, in that book. Every
11-- aggregate downstream keeps subsidiary_id in its grain for that reason.
12--
13-- Included: posting lines of posting transactions that have not been deleted.
14-- Statistical and non-posting account types carry no money and are left out, and
15-- so are revenue arrangements: they are revenue management bookkeeping, and the
16-- revenue they schedule reaches the ledger through the revenue recognition
17-- journals that are already counted.
18--
19-- Consolidation: is_elimination flags intercompany lines marked for elimination.
20-- NetSuite offsets them with journals posted to an elimination subsidiary, which
21-- appear here under that subsidiary's id. A group view therefore includes the
22-- elimination subsidiary rather than dropping the flagged lines; use the flag to
23-- review intercompany balances that have not been eliminated yet.
24--
25-- Signs:
26--   debit, credit     both positive, as NetSuite stores them
27--   net_debit         debit - credit; sums to zero per transaction, book and
28--                     subsidiary
29--   amount_natural    signed by the account type's normal balance: debit - credit
30--                     for debit-normal types (assets, expenses), credit - debit
31--                     for the rest. Positive means the account grew.
32
33WITH live_transactions AS (
34    SELECT t.*
35    FROM {{staging.netsuite.transaction}} t
36    LEFT JOIN {{staging.netsuite.deleted_record}} d
37        ON  d.netsuite_account = t.netsuite_account
38        AND d.record_type      = t.record_type
39        AND d.record_id        = t.transaction_id
40    WHERE d.record_id IS NULL