Balance Sheet by Account (As of a Specific Period)

/*
 * BALANCE SHEET - AS OF DATE
 * 
 * This query generates a balance sheet showing account balances as of a specific period.
 * Balance sheet accounts accumulate all transactions from inception through the target date.
 * 
 * Shows: Assets, Liabilities, and Equity
 * Amounts are consolidated to the target subsidiary's currency.
 * Only accounts with non-zero balances are included.
 * 
 * Compatible with both OneWorld (multi-subsidiary) and standard NetSuite accounts.
 */

SELECT 
  a.acctnumber AS account_number,
  a.accountsearchdisplaynamecopy AS account_name,
  -- Alternative: For full hierarchy path (e.g., "Parent : Child : Account"), use:
  -- a.fullname AS account_name,
  pa.acctnumber AS parent_account_number,
  a.accttype AS account_type,
  
  /*
   * FINANCIAL STATEMENT SECTIONS
   * Maps NetSuite account types to standard balance sheet sections.
   * The numbered prefixes ensure proper sort order (Assets, Liabilities, Equity).
   */
  CASE 
    WHEN a.accttype IN ('Bank', 'AcctRec', 'OthCurrAsset', 'FixedAsset', 'OthAsset', 'DeferExpense', 'Unbilled') 
      THEN '1. Assets'
    WHEN a.accttype IN ('AcctPay', 'CreditCard', 'OthCurrLiab', 'LongTermLiab', 'DeferRevenue') 
      THEN '2. Liabilities'
    WHEN a.accttype IN ('Equity') 
      THEN '3. Equity'
    ELSE '4. Other Balance Sheet'
  END AS section,
  
  /*
   * BALANCE CALCULATION
   * Sums all transactions from inception through the target period end date.
   * Unlike the income statement, balance sheet accounts do not reset each year.
   */
  SUM(
    TO_NUMBER(
      BUILTIN.CONSOLIDATE(
        tal.amount,
        'LEDGER',        -- Consolidation type: Uses ledger amounts
        'DEFAULT',       -- Accounting book: 'DEFAULT' uses primary book
        'DEFAULT',       -- Currency: 'DEFAULT' uses subsidiary's base currency
        1,               -- Subsidiary ID: Change to run for a specific subsidiary.
                         -- Default is 1, which is typically the parent/consolidated subsidiary.
                         -- For non-OneWorld accounts, this value is ignored.
                         -- To find your subsidiary IDs, run:
                         -- SELECT id, name FROM subsidiary WHERE isinactive = 'F'
        target_period.id,
        'DEFAULT'        -- Exchange rate type: 'DEFAULT' uses standard rates
      )
    )
  ) AS balance
  
FROM transactionaccountingline tal
  -- Link to transaction header for posting status and period
  JOIN transaction t ON t.id = tal.transaction
  -- Link to account for account details and type
  JOIN account a ON a.id = tal.account
  -- Link to accounting period for date filtering
  JOIN accountingperiod ap ON ap.id = t.postingperiod
  -- Parent account for hierarchy reporting (LEFT JOIN since top-level accounts have no parent)
  LEFT JOIN account pa ON pa.id = a.parent
  
  /*
   * TARGET PERIOD FOR BALANCE SHEET DATE
   * The balance sheet shows balances "as of" the end of this period.
   * Change '2025-12' to your desired YYYY-MM (only need to change it here).
   * 
   * Example: '2025-12' shows balances as of December 31, 2025
   */
  CROSS JOIN (
    SELECT id, enddate
    FROM accountingperiod 
    WHERE TO_CHAR(startdate, 'YYYY-MM') = '2025-12'  -- Balance sheet date: Change to desired period
      AND isquarter = 'F' 
      AND isyear = 'F'
    FETCH FIRST 1 ROWS ONLY
  ) target_period
  
WHERE 
  -- Posted transactions only (excludes pending, voided, etc.)
  t.posting = 'T'
  
  /*
   * ACCOUNTING BOOK FILTER
   * Default: Dynamically selects the primary accounting book.
   * 
   * To use a specific book, replace the subquery with the book ID:
   *     AND tal.accountingbook = 3
   * 
   * To find your accounting book IDs, run:
   *     SELECT id, name, isprimary FROM accountingbook
   */
  AND tal.accountingbook = (SELECT id FROM accountingbook WHERE isprimary = 'T')
  
  /*
   * ACCOUNT TYPE FILTER
   * Excludes income statement (P&L) accounts to include only balance sheet accounts.
   * Balance sheet accounts: Assets, Liabilities, Equity
   */
  AND a.accttype NOT IN (
    'Income',            -- Sales, service revenue (P&L)
    'COGS',              -- Cost of Goods Sold (P&L)
    'Cost of Goods Sold',-- Alternative COGS designation (P&L)
    'Expense',           -- Operating expenses (P&L)
    'OthIncome',         -- Other income (P&L)
    'OthExpense'         -- Other expense (P&L)
  )
  
  /*
   * DATE FILTER - CUMULATIVE THROUGH TARGET PERIOD
   * Balance sheet includes all transactions from the beginning of time
   * through the end of the target period.
   * Uses enddate from the CROSS JOIN above (single date entry point).
   */
  AND ap.startdate <= target_period.enddate
  
GROUP BY 
  a.acctnumber, 
  a.accountsearchdisplaynamecopy,
  pa.acctnumber,
  a.accttype

/*
 * ZERO BALANCE FILTER
 * Excludes accounts with no balance as of the target date.
 * Remove this clause to show all balance sheet accounts regardless of balance.
 */
HAVING 
  SUM(
    TO_NUMBER(
      BUILTIN.CONSOLIDATE(
        tal.amount,
        'LEDGER',
        'DEFAULT',
        'DEFAULT',
        1,               -- Subsidiary ID: Must match value above
        target_period.id,
        'DEFAULT'
      )
    )
  ) <> 0
  
ORDER BY 
  section, 
  a.acctnumber;