16  Annexure A

Topics:

·        Entity Details

·        Adjustment Auto Approval Concept 

·        Load Run ID Implementation in Reconciliation Framework

·        Data Model and Metadata Extensions

·        Procedure to Add Optional Dimensions

·        Optimization of GL Reconciliation Processing Package

·        Map Level Recon Parent Node Selection

·        GL Nodes Configuration

·        Hierarchy Refresh

·        Creating Filter for Load Run ID

·        Duplicate Adjustments 

·        VPAT-keystrokes and access keys

Entity Details

The following provides a brief description of the various tables:

The requirement for Purging or Archiving FACT tables must be analyzed, developed, and executed as per the implementation site requirements; for better performance and space management.

Adjustment Auto Approval Concept

Load Run ID Implementation in Reconciliation Framework

This section provides information about Loading Multiple Load Runs in OFSAA in the Reconciliation Framework application and step-by-step instructions to use this section.

NOTE:   

Load Run ID population is a prerequisite for the Correction Entry. For more information on the Correction Entry refer to the

Before loading data into the staging table, generate a Load Run Identifier to stamp the records from the source. These records can be a complete snapshot or can be partial or incremental data too. The Load Run Identifier can be generated by calling the function in the OFSAA atomic schema named fn_register_load_run. The function expects some input parameters and returns a unique Load Run Identifier to the calling program.

Figure 74: Generate the Load Run Identifier

NOTE:   

Generate the Load Run Identifiers before the Reconciliation Rules are executed.

The following are the steps to generate the Load Run Identifiers:

1.     Call function fn_register_load_run with parameters mentioned in the document

Function - Register Load Run (fn_register_load_run)

Parameters - Batch ID, MIS-Date, Load Run Name, Load Run Purpose, Load Run Type

Example:

Declare

Result number;

Begin

Result: = fn_register_load_run ('OFSBFNDINFO_20150101_1','20150101','ADJUSTMENT_STG_CARDS_OFSBFNDINFO_20150101_1’,'DATA ADJUSTMENT','A');

End;

The function registers the request in the table name rev_load_run_master and marks load as "In progress". You can use columns LOAD_RUN_NAME and LOAD_PURPOSE as per the requirement.

NOTE:   

Multiple calls to the procedure can be made to the function for given FIC_MIS_DATE. Each call returns a number that is unique across the FIC_MIS_DATE or Extraction date. You can use this Load Identifier to load either one or more staging tables.

2.     Get the Load Run ID registered using the query below:

SELECT LOAD_RUN_ID FROM REV_LOAD_RUN_MASTER WHERE BATCH_ID = 'OFSBFNDINFO_20150101_1' AND LOAD_RUN_NAME = ‘ADJUSTMENT_STG_CARDS_OFSBFNDINFO_20150101_1’

3.     Use this Load Run ID for your stage table population.

NOTE:   

Column n_load_run_id should always be populated only by the value returned by fn_register_load_run.

4.     Call functions fn_register_load_details and fn_pop_load_run_map which populates the account load run map table. Use load type as I.

The following are the java code examples:

a.     Declare

Result number;

Begin

Result: = fn_register_load_details ('OFSBFNDINFO_20150101_1','20150101',‘STG_CARDS’,1,' ADJUSTMENT_STG_CARDS_OFSBFNDINFO_20150101_1','I');

End;

b.     Declare

Result number;

Begin

Result: = fn_pop_load_run_map ('OFSBFNDINFO_20150101_1','20150101', ‘STG_CARDS’,1,' ADJUSTMENT_STG_CARDS_OFSBFNDINFO_20150101_1');

End;

5.     Run SQL below after running functions above:

UPDATE REV_LOAD_RUN_MASTER SET LOAD_RUN_STATUS = 'Completed', END_DT_TIME = SYSTIMESTAMP WHERE MIS_DATE = TO_DATE(?, 'YYYYMMDD')  AND LOAD_RUN_NAME = ? AND LOAD_RUN_ID = ?

NOTE:   

The functions mentioned can be registered as a DT and plugged in while loading data into OFSAA staging.

For more information on the Load Run ID refer, to the Oracle Financial Services Data Foundation Application Pack User Guide.

Data Model and Metadata Extensions

Topics:

·        Steps to Configure an Additional Table in the Application

Steps to Configure an Additional Table in the Application

To add a Ledger entity, Product Processor entity, or Dimension table in OFS Analytical Application Reconciliation Framework, follow these steps.

For example, to add a GL Entity for General Ledger to General Ledger reconciliation, perform the following steps:

1.     Open the OFS_GLRECON.erwin file in Erwin Data Modeler.

2.     Change the view mode to Physical.

3.     If Target ledger Structure is different than STG_GL_DATA: 

a.     Place the cursor on the tables in OFSAA Tree View, right-click, and select New. This creates a new table. Rename it to the required table name.

b.     Expand the table and click the ‘+’ icon to view the columns mapped against the tables.

c.     Place the cursor on the columns mapped against the tables, right-click, and select New. It adds a new column to that table. Rename it to the required column name.

d.     Repeat the above-mentioned steps to add multiple tables and columns.

e.     Navigate to Logical View and provide the logical table name and logical column names to the added columns.

4.     If Target ledger Structure is the same as STG_GL_DATA: 

a.     In the OFSAA tree view, you can see Tables.

b.     Navigate to STG_GL_DATA in OFSAA tree view; right-click and select Go to Diagram.

c.     Press CTRL+C (to copy) and then CTRL+V (to paste), to create a new table with the same name as STG_GL_DATA. Rename the table name in both the Physical View and Logical View.

d.     Rename the constraints and index names on the newly added table.

5.     Save the newly added tables as a .xml file. Choose save as type to XML Types (*.xml).

6.     Select option AllFusion Repository Format in the dialog box. Click OK to generate a new .xml file.

7.     Copy that XML file to server for fast processing under ftpshare/<<infodom>>/erwin/erwinXML

8.     Navigate to the OFSAAI UI and log in. Select an appropriate infodom and navigate to Unified Analytical Metadata. To process further see the following steps:

9.     Click Import Model.

10.  Select Incremental Changes or Sliced Model in Choose Type to Upload.

11.  Select the file from the server. It will list out the files present in the path.

12.  Select your XML file and click upload to begin the process to upload the data model. On successful upload, a dialog box pops up.

13.  Similarly, follow the steps mentioned above to add a Stage General Ledger Master (STG_GL_ACCOUNT_MASTER ), General ledger account dimension (Example DIM_GL_ACCOUNT), or Product processor (Example STG_CARDS).

14.  Perform the following steps to continue further (these steps are not applicable for a Product Processor entity):

15.  Insert a new unique map_ref_num in two tables SYS_TBL_MASTER and SYS_STG_JOIN_MASTER for creating SCD for data movement from new Stage general ledger master to new General ledger account dimension.

16.  Example: SYS_TBL_MASTER – Table contains table level information Stage to Dimension. Table names must be changed according to the new stage general ledger master (STG_TBL_NM) to the new General ledger account dimension (TBL_NM).

17.  Example: SYS_STG_JOIN_MASTER – Column level mapping information from Stage to Dimension. Column names and data types must be modified according to the new Stage general ledger master (STG_COL_NM) to the new General ledger account dimension (COL_NM).

18.  Navigate to Operations and see the following steps:

a.     Click Batch Maintenance.

b.     Add a new batch, provide an appropriate name and description, and click Save.

Figure 126: Batch Maintenance Window

c.     Add a task to the newly created batch.

d.     Select Run Executable under Components.

e.     Select appropriate Datastore type, Datastore Name, IP Address.

f.       Update scd,<<MAP_REF_NUM>> in the executable with the unique number which is provided while inserting in SYS_TBL_MASTER and SYS_STG_JOIN_MASTER. Select Wait as ‘N’ and Batch Parameter as ‘Y’. Click Save as shown in the following figure:

Figure 127: Task Definition Window

Navigate to Unified Analytical Metadata and see the following steps to proceed further:

a.     Click Data Sets under Business Metadata Management.

b.     Add a new dataset and provide code, short description, and long description.

c.     Select the entities that are participating in the Dataset.

d.     Define Ansi join.

19.  Example: where STG_GL_DATA is your Ledger table and DIM_GL_ACCOUNT is your General ledger account dimension.

STG_GL_DATA 

INNER JOIN DIM_CURRENCY ON DIM_CURRENCY.V_ISO_CURRENCY_CD = STG_GL_DATA.V_CCY_CODE 

INNER JOIN DIM_ORG_STRUCTURE ON DIM_ORG_STRUCTURE.V_ENTITY_CODE = 

STG_GL_DATA.V_LV_CODE AND DIM_ORG_STRUCTURE.f_latest_record_indicator='Y' 

INNER JOIN DIM_DATES ON DIM_DATES.D_CALENDAR_DATE = STG_GL_DATA.FIC_MIS_DATE INNER JOIN DIM_GL_ACCOUNT ON DIM_GL_ACCOUNT.V_GL_ACCOUNT_CODE = 

STG_GL_DATA.V_GL_CODE AND DIM_GL_ACCOUNT.f_latest_record_indicator='Y' 

INNER JOIN DIM_GAAP ON DIM_GAAP.V_GAAP_CODE = STG_GL_DATA.V_GAAP_CODE AND 

DIM_GAAP.f_latest_record_indicator='Y' LEFT OUTER JOIN DIM_BUSINESS_UNIT ON DIM_BUSINESS_UNIT.V_BUSINESS_UNIT_CODE = STG_GL_DATA.V_BUSINESS_UNIT_CODE AND 

DIM_BUSINESS_UNIT.f_latest_record_indicator='Y' 

LEFT OUTER JOIN DIM_ORG_UNIT ON DIM_ORG_UNIT.V_ORG_UNIT_CODE = 

STG_GL_DATA.V_ORG_UNIT_CODE AND DIM_ORG_UNIT.f_latest_record_indicator='Y' 

LEFT OUTER JOIN DIM_GEOGRAPHY ON DIM_GEOGRAPHY.V_ACCT_BRANCH_CODE = 

STG_GL_DATA.V_BRANCH_CODE AND DIM_GEOGRAPHY.f_latest_record_indicator='Y' 

LEFT OUTER JOIN DIM_PRODUCT ON DIM_PRODUCT.V_PROD_CODE = STG_GL_DATA.V_PROD_CODE 

AND DIM_PRODUCT.f_latest_record_indicator='Y'

20.  Click Save.

21.  Navigate to Unified Analytical Metadata and click Business Hierarchy under Business Metadata Management.

22.  Add two new Hierarchies on new Ledger Account Dimension, for GL Code, and GL Type and shown in the following figures:

Figure 128: GL Code Example

23.  Perform an excel upload for table SETUP_GLSOURCE_HIERARCHY

§       V_GL_HCY_CODE – Hierarchy code which is created, as on GL code for new general ledger account dimension.

§       V_GL_HCY_DIM_TABLE_NAME –General ledger account dimension table name.

§       V_GL_HCY_INTRA_GROUP_COL_NAME – Intragroup Column name.

§       V_GL_HCY_GL_TYPE_COL_NAME – GL Type column name.

Procedure to Add Optional Dimensions

1.     Create a dimension table in the model if required.

2.     Add the table (where the dimension is defined) in the join condition of the GL dataset & Product Processor dataset.

3.     Create a Hierarchy with metadata authorization.

4.     Add a field from the respective dimension table which is used to join with stage product processor tables in the following tables:

a.     FCT_RECONCILIATION_DIFFERENCE

b.     FCT_GL_ADJUSTMENT_ENTRIES

c.     FCT_GL_CORRECTION_ENTRIES

d.     FSI_TMP_TARGET_BALANCE

e.     FSI_TMP_SOURCE_BALANCE

f.       FSI_GL_THRESHOLD_BREACHES

5.     Add a field from the respective dimension table which is used to join with stage product processor tables in the unique index of the following tables:

a.     FCT_RECONCILIATION_DIFFERENCE

b.     FCT_GL_ADJUSTMENT_ENTRIES

c.     FCT_GL_CORRECTION_ENTRIES

d.     FSI_TMP_TARGET_BALANCE

e.     FSI_TMP_SOURCE_BALANCE

6.     Upload the customized data model to see the changes reflected in the database.

Optimization of GL Reconciliation Processing Package

This section is only applicable to the RDBMS installation mode.

Parallel Hint for SELECT statements:

The use of a parallel hint is optional in the queries. Perform the following steps to use the parallel hint in the SELECT clause of the queries generated during execution:

Insert a record into the GL_SETUP_CONFIGURATION table and provide a parallel hint in the V_COMPONENT_VALUE column.

For example, insert into GL_SETUP_CONFIGURATION (V_COMPONENT_CODE, V_COMPONENT_DESC, V_COMPONENT_VALUE) values ('GLRECON_SEL_PARALLEL_HINT', 'To use Parallel hint in GL Recon Application.', '/*+ parallel */').

In case a parallel hint is not required then the previous step is not required. The usage of a parallel hint is optional for the GL Processing package.

Parallel Hint for CREATE TABLE statements:

The use of parallelism during intermediate table creation is optional. Perform the following steps to use parallelism to CREATE TABLE clause during execution:

Insert a record into the GL_SETUP_CONFIGURATION table and provide a parallel hint in the V_COMPONENT_VALUE column.

For example, insert into GL_SETUP_CONFIGURATION (V_COMPONENT_CODE, V_COMPONENT_DESC, V_COMPONENT_VALUE) values ('CREATE_TABLE_PARALLEL_HINT', 'To use parallelism while creating intermediate tables', 'PARALLEL').

NOTE:   

HINT can be used with a combination of - NOLOGGING and PARALLEL with different degrees of parallelism as follows:

1.     PARALLEL

2.     NOLOGGING PARALLEL

3.     PARALLEL (DEGREE i*)

4.     NOLOGGING PARALLEL (DEGREE i*)

*Any positive integer based on available system resources.

Map Level Recon Parent Node Selection

To select the Map Level Recon parent node, perform the following steps:

1.     Ensure that the General Ledger Product Processor (GL PP) Definition has the following settings:

a.     Reconciliation Definition Type as Manual

b.     Consolidation Type as Solo

c.     Reconciliation Definition as Map Level Recon.

2.     Create an entry in the SetUpMaster as ‘LAM’, for the corresponding Map ID and Version Number.

Figure 129: LAM entry in SetUpMaster

a.     The parent-child hierarchy for the legal entity selected in the definition is considered for filtration.

GL Nodes Configuration

In case the GL nodes exceed 2024 then use the following configuration to support the hierarchy browser of GL:

1.     Change <PARAMETER NAME=‘‘PC_NONBI_BI_SWITCH’’ VALUE=‘<PARAM>‘/> in $FIC_HOME/conf/DynamicServices.xml and deployed location <CONTEXT_PATH>/CONTEXT_NAME/conf/DynamicServices.xml

2.     <PARAM>: Parameter value to be chosen based on no total number of nodes in a hierarchy (OOB: 2048) For example, if hierarchy nodes are 9000, then VALUE must be 9500.

3.     After taking backup of these tables, delete the data from rev_bihier,rev_locale_hier for the respective hierarchy code.

4.     Restart the web and app servers by clearing the work/cache/tmp folder.

5.     Resave the respective hierarchy, and verify whether the data is populated into the rev_bihier table with version 0.

6.     Clear browser cache and create the definition from the start.

Hierarchy Refresh

Topics:

·        Problem

·        Expected Behavior

·        Solution

·        Batch to be Executed

Problem

For a GL Recon Rule running in production, one of the legal entities has a new parent node in the underlying data and the Legal Entity Hierarchy is refreshed with an automatic batch. Because of this change in the legal entity structure, the GL Recon Rule, where the hierarchy is used, is broken.

GL Recon Rule did not fail, it is executed successfully without returning any results. Hence, there is no way for any user to know that there is an issue. When the GL Recon Rule is opened in the front end, the legal entity field is displayed as blank.

Expected Behavior

Ideally, any changes to the underlying data of the hierarchy must be automatically reflected in the GL Recon Rule.

Solution

A batch has to be executed with the FIC_MIS_DATE that will refresh all hierarchies used in the existing rule. Before executing the batch, check the entries in the table:

FSI_GL_HIERARCHY_CONFIGURATION

Batch to be executed

The following batch has to be executed with proper FIC_MIS_DATE and the updated hierarchies will be saved in an Audit Table: FSI_GL_HIER_REFRESH_AUDIT_DDL for tracking purposes.

Batch: ##INFODOM##REFRESH_HIERARCHY

Creating Filter for Load Run ID

You can filter the load Run ID value and process reconciliation accordingly. One load Run ID in the product processor table can be filtered and reconciled with the respective ledger data. When load Run ID filtration is enabled, adjustments automatically get updated with the filtered load Run ID value, irrespective of the value given in default values.

The following is the procedure to filter load Run ID, reconcile, and default the same:

·        Add the following entry in the GL_SETUP_CONFIGURATION table:

To enable load Run ID filtration, change the flag to Y in the column V_COMPONENT_VALUE.

·        Add the following entry in the RUN_EXE_PARAMETERS table for the corresponding RunSkey

·        RunSkey: RunSkey value can be fetched from the table DIM_RUN.

·        The rule must be executed once, before updating the previous tables and V_COMPONENT_VALUE must be N. You must go to the DIM_RUN table and fetch the corresponding Skey for the definition you like to filter with load Run ID.

·        V_PARAM_ID: This must have a value GLPPLOADRUNIDFILTER.

·        V_SEGMENT_CODE: This value must be updated with the corresponding application segment.

·        V_PARAM_VALUE_CODE: This column must have the value of load Run ID based on which filtration happens in the target data for the selected RunSkey rule.

·        Once these tables and values are updated, you can run the batch from the Operations tab and check the results. Adjustments that are created with these changes display the load Run ID value specified in the V_PARAM_VALUE_CODE.

Duplicate Adjustments

There is an option to include the adjustments as any other normal account information into the reconciliation process.

The following are the steps you must follow to include the adjustments into the reconciliation process:

·        You must make an entry in the  GL_SETUP_CONFIGURATION table as follows.

·        By default V_COMPONENT_VALUE value is set to ‘Y’.

·        Once the Reconciliation Difference process is executed, the differences between source and target are identified and captured in the table - FCT_RECONCILIATION_DIFFERENCE.

Based on Adjustment entry floor, dummy adjustment entries are generated in table - FCT_GL_ADJUSTMENT_ENTRIES with dummy account number - GL_<MAP_ID>_<Sequence> (GL_45_1).

·        If V_COMPONENT_VALUE is set to ‘N’ then during the next execution of the same definition or any definition which results in the same granularity, the dummy account numbers are considered for calculation, and differences are adjusted accordingly.

NOTE:   

While executing multiple rules together in one run, definitions with different dimensional granularity or similar definitions with different filters have to be carefully executed in a single run as the system randomly allocates the map ID for the order of execution and not for the order of UI rule addition.

VPAT-keystrokes and access keys

Voluntary Product Accessibility Template (VPAT) is a document that mentions how to access the User Interface elements. Unique keystrokes and Access keys move the focus to a specific UI element (and trigger the command). They are usually a button, link, or control that triggers the command. Access keys relocate the cursor or selection focus to specific interface components. Every component on the page with definable focus is accessible by tab traversal (using Tab and Shift+Tab); however, access keys provide quick focus to frequently used components. Access keys must be unique within a page. Oracle Financial Services Reconciliation Framework requires to use of the additional below-mentioned keys.

GL Parameters

Target Parameters