The Planning Repository: HSP_ACCOUNT
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 188.8.131.52.500 and 184.108.40.206.
- 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:
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:
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!