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:

Field NameSQL Server TypeOracle TypeDescription
ACCOUNT_IDintNUMBER(38,0)The OBJECT_ID of the member.
USE_445smallintNUMBER(38,0)If you application is set to use weekly distribution, this field will be populated when the Distribution checkbox is checked.
0=don't adjust values
1=445 spread
2=454 spread
3=544 spread
TIME_BALANCEsmallintNUMBER(38,0)Determines the Essbase time balance attribute.
0=none
1=first
2=last
3=average
SKIP_VALUEsmallintNUMBER(38,0)Determines the Essbase skip attribute.
0=none
1=skip missing values
2=skip zero values
3=skip missing and zero values
ACCOUNT_TYPEsmallintNUMBER(38,0)Determines the account type.
1=expense
2=revenue
3=asset
4=liability
5=equity
6=statistical
7=saved assumption
VARIANCE_REPsmallintNUMBER(38,0)Determines the Essbase variance reporting attribute.
1=expense
2=non-expense
CURRENCY_RATEsmallintNUMBER(38,0)If your application is set to use the built-in currency functionality, this determines which rate to use for conversion.
0=none
1=average
2=ending
3=historical
PLANNING_METHODsmallintNUMBER(38,0)No idea on this one.
USED_INsmallintNUMBER(38,0)The plan type usage for the member. This is also a bitmask that tells us which plan types are used in a single integer. This field also appears in the HSP_MEMBER table and appears to stay in sync.
DATA_TYPEsmallintNUMBER(38,0)This field is also found in the HSP_MEMBER table. It is populated in HSP_MEMBER but is always NULL in this table.
SRC_PLAN_TYPEintNUMBER(38,0)"Indicates the source of data value for account." - Direct from Oracle.
SUB_ACCOUNT_TYPEintNUMBER(38,0)"Identifies the type of account and how its useds" - Direct from Oracle.

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>