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 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 Name | SQL Server Type | Oracle Type | Description |
|---|---|---|---|
| ACCOUNT_ID | int | NUMBER(38,0) | The OBJECT_ID of the member. |
| USE_445 | smallint | NUMBER(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_BALANCE | smallint | NUMBER(38,0) | Determines the Essbase time balance attribute. 0=none 1=first 2=last 3=average |
| SKIP_VALUE | smallint | NUMBER(38,0) | Determines the Essbase skip attribute. 0=none 1=skip missing values 2=skip zero values 3=skip missing and zero values |
| ACCOUNT_TYPE | smallint | NUMBER(38,0) | Determines the account type. 1=expense 2=revenue 3=asset 4=liability 5=equity 6=statistical 7=saved assumption |
| VARIANCE_REP | smallint | NUMBER(38,0) | Determines the Essbase variance reporting attribute. 1=expense 2=non-expense |
| CURRENCY_RATE | smallint | NUMBER(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_METHOD | smallint | NUMBER(38,0) | No idea on this one. |
| USED_IN | smallint | NUMBER(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_TYPE | smallint | NUMBER(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_TYPE | int | NUMBER(38,0) | "Indicates the source of data value for account." - Direct from Oracle. |
| SUB_ACCOUNT_TYPE | int | NUMBER(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:
Oracle:
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!
Comments