Monthly Profit and Loss by Account (Pivot Table Friendly)

/*
 * INCOME STATEMENT (PROFIT & LOSS) - MONTHLY BY ACCOUNT (UNPIVOTED)
 * 
 * This query generates a monthly income statement with one row per account per month.
 * Output format is ideal for Excel pivot tables and charting.
 * 
 * Shows: Revenue, Cost of Goods Sold, Operating Expenses, Other Income, Other Expense
 * 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 income statement sections.
   * The numbered prefixes ensure proper sort order.
   */
  CASE 
    WHEN a.accttype = 'Income' THEN '1. Revenue'
    WHEN a.accttype IN ('COGS','Cost of Goods Sold') THEN '2. Cost of Goods Sold'
    WHEN a.accttype = 'Expense' THEN '3. Operating Expenses'
    WHEN a.accttype = 'OthIncome' THEN '4. Other Income'
    WHEN a.accttype = 'OthExpense' THEN '5. Other Expense'
  END AS section,
  
  -- Month column in YYYY-MM format for easy sorting and grouping
  TO_CHAR(ap.startdate,'YYYY-MM') AS month,
  
  SUM(cons_amt) AS amount
  
FROM (
  /*
   * SUBQUERY: Calculate consolidated amounts per transaction line
   * This inner query handles currency consolidation and sign conventions
   * before aggregation in the outer query.
   */
  SELECT
    tal.account,
    t.postingperiod,
    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'
        t.postingperiod,
        'DEFAULT'        -- Exchange rate type: 'DEFAULT' uses standard rates
      )
    )
    /*
     * SIGN CONVENTION
     * NetSuite stores Income/OthIncome as credits (negative).
     * Multiplying by -1 converts to positive for financial reporting.
     * Expense accounts are already stored as debits (positive).
     */
    * CASE WHEN a.accttype IN ('Income','OthIncome') THEN -1 ELSE 1 END AS cons_amt
    
  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 apf ON apf.id = t.postingperiod
    
  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')
    
    -- Exclude summary periods (year totals, quarter totals)
    AND apf.isyear = 'F' 
    AND apf.isquarter = 'F'
    
    /*
     * FISCAL YEAR FILTER
     * Change '2025' to your desired year.
     * For a custom date range, replace with:
     *     AND apf.startdate >= TO_DATE('2025-01-01', 'YYYY-MM-DD')
     *     AND apf.enddate <= TO_DATE('2025-12-31', 'YYYY-MM-DD')
     */
    AND TO_CHAR(apf.startdate,'YYYY') = '2025'
    
    /*
     * ELIMINATION ACCOUNT FILTER
     * Excludes intercompany elimination accounts.
     * These accounts are used for consolidated reporting adjustments
     * and should not appear on subsidiary-level statements.
     */
    AND COALESCE(a.eliminate,'F') = 'F'
    
    /*
     * ACCOUNT TYPE FILTER
     * Includes only income statement (P&L) account types.
     * Balance sheet accounts (Assets, Liabilities, Equity) are excluded.
     */
    AND a.accttype IN (
      'Income',            -- Sales, service revenue
      'COGS',              -- Cost of Goods Sold
      'Cost of Goods Sold',-- Alternative COGS designation (some accounts)
      'Expense',           -- Operating expenses
      'OthIncome',         -- Interest income, gains, etc.
      'OthExpense'         -- Interest expense, losses, etc.
    )
) x
-- Rejoin to accounting period for month extraction in outer query
JOIN accountingperiod ap ON ap.id = x.postingperiod
-- Rejoin to account for account details in output
JOIN account a          ON a.id = x.account
-- Parent account for hierarchy reporting (LEFT JOIN since top-level accounts have no parent)
LEFT JOIN account pa    ON pa.id = a.parent

GROUP BY 
  a.acctnumber, 
  a.accountsearchdisplaynamecopy, 
  pa.acctnumber, 
  a.accttype, 
  TO_CHAR(ap.startdate,'YYYY-MM')

/*
 * ZERO BALANCE FILTER
 * Excludes accounts with no activity in the period.
 * Remove this line to show all accounts regardless of balance.
 */
HAVING SUM(cons_amt) <> 0

ORDER BY section, a.acctnumber, month;