Year-to-Date P&L

/*
 * INCOME STATEMENT (PROFIT & LOSS) - YEAR TO DATE
 * 
 * This query generates a YTD income statement by account, showing:
 * - 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 * FROM (
    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,
        
        /*
         * YTD AMOUNT CALCULATION
         * Consolidates transaction amounts to the target subsidiary's currency,
         * then applies sign convention: Revenue/Other Income show as positive,
         * Expenses/COGS show as positive (costs).
         */
        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'
                    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 ytd_amount
        
    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
        
    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 ap.isyear = 'F'
        AND ap.isquarter = 'F'
        
        /*
         * DATE RANGE FILTER
         * Change these dates to your desired fiscal year or reporting period.
         * Format: YYYY-MM-DD
         */
        AND ap.startdate >= TO_DATE('2025-01-01', 'YYYY-MM-DD')  -- Period start date
        AND ap.enddate <= TO_DATE('2025-12-31', 'YYYY-MM-DD')    -- Period end date
        
        /*
         * 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.
        )
        
    GROUP BY 
        a.acctnumber, 
        a.accountsearchdisplaynamecopy, 
        pa.acctnumber, 
        a.accttype
    ORDER BY section, a.acctnumber
)
/*
 * ZERO BALANCE FILTER
 * Excludes accounts with no activity in the period.
 * Remove this line to show all accounts regardless of balance.
 */
WHERE ytd_amount <> 0