The Planning Repository: HSP_ACCOUNT

Brian Marshall

Not to sound like a broken record, but…a few disclaimers:

  • This is the fun stuff…that voids your warranty.  Not that you have to tell Oracle that you poke around here, but if you do, they will blame anything and everything that’s wrong on you for being in there.
  • The content here has been tested and validated against 11.1.2.3.500 and 11.1.2.4.
  • The content here has been tested and validated against Microsoft SQL Server and Oracle 11g.
  • The content here is based on the Vision sample application.
  • The content here is totally unsupported by Oracle (in case you missed the first bullet).

So it’s been a while since we took at look at the Planning Repository.  Today we’ll move past the HSP_MEMBER table (and associated tables) to look at the first of our built-in Planning dimensions:  HSP_ACCOUNT.  As the name indicates, the HSP_ACCOUNT table contains properties related to the account dimension in Planning.  Let’s take a look at the table structure:

Attention: The internal data of table “38” is corrupted!

Overall, this table is pretty simple.  We could probably stop there, but hey, how about a quick query:

SELECT
	 po.OBJECT_NAME AS Parent
	,o.OBJECT_NAME AS Account
	,CASE a.USE_445
		WHEN 0 THEN 'None'
		WHEN 1 THEN '445'
		WHEN 2 THEN '454'
		WHEN 3 THEN '544'
		ELSE ''
	 END AS WeeklySpread
	,CASE a.TIME_BALANCE
		WHEN 0 THEN 'None'
		WHEN 1 THEN 'First'
		WHEN 2 THEN 'Last'
		WHEN 3 THEN 'Average'
		ELSE ''
	 END AS TimeBalance
	,CASE a.SKIP_VALUE
		WHEN 0 THEN 'None'
		WHEN 1 THEN 'Skip Missing'
		WHEN 2 THEN 'Skip Zeros'
		WHEN 3 THEN 'Skip Missing and Zeros'
		ELSE ''
	 END AS SkipValue
	,CASE a.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 AccountType
	,CASE a.VARIANCE_REP
		WHEN 1 THEN 'Expense'
		WHEN 2 THEN 'Non-expense'
		ELSE ''
	 END AS VarianceReporting
	,CASE a.CURRENCY_RATE
		WHEN 0 THEN 'None'
		WHEN 1 THEN 'Average'
		WHEN 2 THEN 'Ending'
		WHEN 3 THEN 'Historical'
		ELSE ''
	 END AS CurrencyRate
FROM 
	HSP_ACCOUNT a
INNER JOIN
	HSP_OBJECT o ON a.ACCOUNT_ID = o.OBJECT_ID
INNER JOIN
	HSP_OBJECT po ON o.PARENT_ID = po.OBJECT_ID
ORDER BY 
	o.POSITION

This is basically a decode of all of the different fields.  Here are the results:

SQL Server:

HSP_ACCOUNT_MOracle:

HSP_ACCOUNT_O

We’ll be keeping it simple for the next few posts, focusing on the built-in dimensions that we have left.  That’s all for now!

Author:

One Comment

  1. April 23, 2016 at 2:39 am

    […] the Planning Repository!  I started down the path of the built-in dimensions.  First up was the HSP_ACCOUNT […]

Leave a Reply

Name*
Email*
Url
Your message*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.