The stage dimension loader procedure does not insert or update the
<Dimension>_code column in the
Dim_<Dimension>_B table. This is an alternate method for
updating the < Dimension>_Code column in the Dim_<
Dimension>_B table, retained to accommodate implementations prior to the
enhancement where we enable loading the code directly to the dimension table instead of
from the attribute table. It is not recommended for new installations. This section
explains how the <Dimension>_code can be updated.
- A new attribute should be created in the
REV_DIM_ATTRIBUTES_B /
TL table.
Note:
You should use the existing CODE attribute for the seeded dimensions.
PRODUCT CODE, COMMON COA CODE, and so on.
- The fsi_dim_attribute_map table should be populated with values.
The following columns must be populated:
N_DIMENSION_ID (Dimension id)
V_ATTRIBUTE_NAME (The attribute name)
V_UPDATE_B_CODE_FLAG (This flag should be
'Y').
Any given dimension can have only one attribute with
V_UPDATE_B_CODE_FLAG as 'Y'. This should only be specified
for the CODE attribute for that dimension.
Example:
N_DIMENSION_ID 4V_ATTRIBUTE_NAME 'PRODUCT_CODE'
V_UPDATE_B_CODE_FLAG 'Y' V_STG_TABLE_NAME 'stg_product_master'
V_STG_COLUMN_NAME 'v_prod_code'
Note:
The values in
V_STG_TABLE_NAME and
V_STG_COLUMN_NAME
are not used by the fn_updateDimensionCode procedure, however these fields
are set to NOT NULL and should be populated.
- Load
STG_<DIMENSION>_ATTR_INTF table with data for the
new ATTRIBUTE created. The attribute values must first be loaded using the stage dimension loader
procedure, fn_drmDataLoader, before running this procedure. This procedure will
pull values from the DIM_<DIMENSION>_ATTR table. If these
rows do not exist for these members prior to running this procedure, the
DIM_<DIMENSION>_B.<DIMENSION>_CODE field will
not be updated.
Execute the fn_updateDimensionCode function. The function
updates the code column with values from the
DIM_<DIMENSION>_ATTR table.
- You can execute this procedure either from SQL*Plus or from within a PL/SQL
block or from the Batch Maintenance window within OFSAAI framework.
To run the procedure from SQL*Plus, login to SQL*Plus as the Atomic Schema
Owner. The function requires 3 parameters: Batch Run Identifier , As of Date,
Dimension Identifier.
The syntax for calling the procedure is:
function fn_updateDimensionCode (batch_run_id varchar2, as_of_date
varchar2, pDimensionId varchar2)
where
BATCH_RUN_ID is any string to identify the executed batch.
AS_OF_DATE in the format YYYYMMDD.
pDIMENSIONID dimension id
For Example
Declare num number;Begin num := fn_updateDimensionCode
('INFODOM_20100405','20100405',1 );End;
You need to populate a row in
FSI_DIM_LOADER_SETUP_DETAILS.
For example, for FINANCIAL ELEM CODE, to
insert a row into FSI_DIM_LOADER_SETUP_DETAILS, following is
the syntax:
INSERT INTO FSI_DIM_LOADER_SETUP_DETAILS (N_DIMENSION_ID) VALUES
('0'); COMMIT;
- To execute the procedure from OFSAAI Batch Maintenance, create a new Batch with
the Task as TRANSFORM DATA and specify the following parameters for the
task:
Datastore Type: Select appropriate
datastore from list
Datastore Name: Select appropriate name
from the list
IP address: Select the IP address from
the list
Rule Name:
Update_Dimension_Code
Parameter List: Dimension ID