Stupid Planning queries #3 -- Accounts

Introduction

I said I would get all of Planning’s dimension queries out in a month, and I aim to meet that goal.

Without further ado, here’s the code to pull out the Account dimension from the sample Planning application in all of its glory.

Yes, I posted this on Network54, but here it is in my blog for completeness’ sake.

As always with these queries, they are 100% unsupported by Oracle and there’s a tremendous chance that I’ve gotten them wrong, so test, test, test and remember that you are hacking the tables and if anything blows up you are completely on your own.  Got it?  Good, let’s begin.

Code to query Accounts

--    Purpose:    Rewrite of Dave Farnsworth's sample Account query using
--                explicit INNER JOINS because I am a control freak.  Yes, it
--                really isn't any better, I just like it that way.
--    Modified:   30 June 2011, first write Cameron Lackpour, cameron@clsolve.com
--    Notes:      Really written by Dave Farnsworth --              
--                http://www.solutionsexperts.com
--                Go to http://www.odtug.com, then Technical Resources, then go
--                find his white paper on hacking Planning tables, then have
--                fun.  :)
SELECT
    O.OBJECT_ID,
    O.OBJECT_NAME AS 'Member name',
    -- Use SQL Subquery to get aliases.
    -- NB --     The overall SELECT from HSP_MEMBER ensures that members
    --            with and without an alias are selected.
    -- ISNULL puts in zero length string in place of NULL
    ISNULL((SELECT OA.OBJECT_NAME
    FROM HSP_ALIAS A
        INNER JOIN HSP_OBJECT OA
            ON A.MEMBER_ID = O.OBJECT_ID AND
            OA.OBJECT_ID = A.ALIAS_ID), '') AS 'Alias',
    PO.OBJECT_NAME AS 'Parent',
    --    There are up to five Plan Types and operators
    --    can vary across Plan Types, so you will need to repeat
    --    this block if > 1 Plan Type.
    CASE M.CONSOL_OP1
        WHEN 0 THEN '+'
        WHEN 1 THEN '-'
        WHEN 2 THEN '*'
        WHEN 3 THEN '\'
        WHEN 4 THEN '%'
        WHEN 5 THEN '~'
        WHEN 6 THEN '^'
    END AS 'Operator',
    CASE M.DATA_STORAGE
        WHEN 0 THEN 'Store Data'
        WHEN 1 THEN 'Never Share'
        WHEN 2 THEN 'Label Only'
        WHEN 3 THEN 'Shared Member'
        WHEN 4 THEN 'Dynamic Calc and Store'
        WHEN 5 THEN 'Dynamic'
    END AS 'Storage',
    CASE M.TWOPASS_CALC
        WHEN 0 THEN 'NO'
        WHEN 1 THEN 'YES'
    END AS 'Twopass',
  --    The parent object type is either 2, which is the Account

  --    dimension or 32, which is a Account member    

   --    Comment this out as it doesn't really add anything to the

  --    query.   

   --    CASE PO.OBJECT_TYPE,
    CASE AA.USE_445
        WHEN 0 THEN 'None'
        WHEN 1 THEN '445'
        WHEN 2 THEN '454'
        WHEN 3 THEN '544'
        ELSE ''
    END AS 'Spread Type',
    CASE AA.TIME_BALANCE
        WHEN 0 THEN 'None'
        WHEN 1 THEN 'First'
        WHEN 2 THEN 'Last'
        WHEN 3 THEN 'Average'
        ELSE ''
    END AS 'Time Balance',
    CASE AA.SKIP_VALUE
        WHEN 0 THEN 'None'
        WHEN 1 THEN 'Skip missing'
        WHEN 2 THEN 'Skip zeroes'
        WHEN 3 THEN 'skip missing and zeroes'
        ELSE ''
    END AS 'Skip Value',
    CASE AA.ACCOUNT_TYPE
        WHEN 1 THEN 'Expense'
        WHEN 2 THEN 'Revenue'
        WHEN 3 THEN 'Asset'
        WHEN 4 THEN 'Liability'
        WHEN 5 THEN 'Equity'
        WHEN 6 THEN 'Statistical'
        WHEN 7 THEN 'Saved Assumption'
        ELSE ''
    END AS 'Account Type',
    CASE AA.VARIANCE_REP
        WHEN 1 THEN 'Expense'
        WHEN 0 THEN 'Non Expense'
        ELSE ''
    END 'Variance Reporting',
    CASE AA.CURRENCY_RATE
        WHEN 0 THEN 'None'
        WHEN 1 THEN 'Average'
        WHEN 2 THEN 'Ending'
        WHEN 3 THEN 'Historical'
        ELSE ''
    END AS 'Currency Rate',
    --    Bitmask indicating the cubes that use the account
    --    1=revenue
    --    2=net income
    --    4=balance sheet
    CASE AA.USED_IN
        WHEN 1 THEN 'Revenue'
        WHEN 2 THEN 'Net Income'
        WHEN 3 THEN 'Balance Sheet'
    END AS 'Where used',
    CASE M.DATA_TYPE
        WHEN 1 THEN 'Currency'
        WHEN 2 THEN 'Non Currency'
        WHEN 3 THEN 'Percentage'
        ELSE ''
    END AS 'Data Type',
    CASE AA.SRC_PLAN_TYPE
        WHEN 0 THEN 'NA'
        WHEN 1 THEN 'Revenue'
        WHEN 2 THEN 'Net Income'
        WHEN 4 THEN 'Balance Sheet'
    End AS 'Plan Type'
FROM HSP_OBJECT O
INNER JOIN HSP_OBJECT PO ON
    PO.OBJECT_ID = O.PARENT_ID
INNER JOIN HSP_MEMBER M ON
    M.MEMBER_ID = O.OBJECT_ID
INNER JOIN HSP_ACCOUNT AA ON
    AA.ACCOUNT_ID = O.OBJECT_ID
WHERE O.OBJECT_TYPE = 32

Output

It’s too big to put in the blog, so download it here if you want or just run it against your own Planning application.  Happy 100% unsupported querying aka hacking!