10.2.2 ALMBI Transformation Package Update

The ALMBI Transformation Package should also be updated in ALMBI for a User-defined Product Dimension.

Perform the following actions to update the ALMBI Transformation Package (PKG_ALM_BI_TRANSFORMATIONS) to handle a User-defined Dimension.

Note:

The examples used here use the following: DIM_ALM_COA table with dimension member column as ALM_COA_ID You should substitute this with the correct user-defined dimension table and column name as required.

1. Inside the PKG Declaration section, include the new user-defined dimension reference in the variable 'v_basic_select'.
   For example, existing variable declaration is:
   For a new user v_basic_select VARCHAR2(500) := 'SELECT PROCESS_ID,PRODUCT, CURRENCY,START_DATE_INDX,SCENARIO,RESULT_TYPE,ORG_UNIT,COMMON_COA,BUCKET
   defined dimension reference ('ALM_COA_ID'), make the following:
   update:
   v_basic_select VARCHAR2(500) := ' SELECT PROCESS_ID,PRODUCT, CURRENCY,START_DATE_INDX,SCENARIO,RESULT_TYPE,ORG_UNIT,COMMON_COA,BUCKET
   S,LEGT
   YPE, ALM_COA_ID,';
2. Inside the PKG Body section, extend the existing IF condition with a new ELSEIF.
   ELSIF V_BI_SETUP_TABLE.MEMBER_COL_NAME = 'ALM_COA_ID' THEN
   V_JOIN_CLAUSE := V_BISETUP_JOIN_COLUMN ||' = CONS_RES_DTL.ALM_COA_ID AND ' ||
   V_BI_SETUP_TABLE.DIM_TABLE_NAME ||'.F_LATEST_RECORD_INDICATOR = ''Y'' ';
3. Once the above changes are complete, recompile, and execute the package.