Background and Issue Description
• Related Bug Number: 9202898
• Product: Oracle BI Applications, Financial Analytics
• Issue: The current architecture of Group Account Numbers supports mapping only the Natural
Account segment values to a Group Account Number. It doesn’t support mapping multiple charts
of accounts (COA) segments to Group Account Number.
Code Fix:
In order to achieve this, the following changes have to be made to the code. For a single chart of
accounts configuration, refer to the steps given below. In case you have a multiple chart of accounts
configuration, in addition to these refer to the extra steps given in section 2.
1. Single Chart of Accounts Configuration
Source File Changes:
a. Open the file file_group_account_codes_ora.csv file under the folder
$INFORMATICA\server\infa_shared\SrcFiles to make the necessary changes.
b. Add range columns for each additional segment used. You can name the columns like FROM
SEG<x> and TO SEG<x>. Suppose you have 2 segments (Cost Center and Department) in
addition to Natural Account then you will have 3 pairs of range columns totally and you will need
to assign one range to one segment as shown below.
c. For example FROM ACCT & TO ACCT will be for natural account, FROM SEG1 & TO SEG1 is
for cost center and FROM SEG2 & TO SEG2 is for department. Assign the Group Account
Numbers accordingly.
d. Save the file. The file after your changes should look like the one given below.
Informatica Changes:
Source and target table changes:
1. Since you are adding new columns in the file, the corresponding columns have to be added to the
Informatica source and target definitions. Add the necessary columns in Informatica for the
source FILE_GROUP_ACCT_NUM and the target W_ORA_GROUP_ACCOUNT_NUM_D_TMP
definitions.
Mapping changes:
2. Make the changes in the mapping SDE_ORA_GroupAccountNumberDimension to map the newly
added columns from the source till the target.
3. As the ranges can now repeat, the Integration ID has to be changed to include the starting range
of each segment. So change the Integration ID derivation to concatenate the FROM_SEG<x>
columns in addition to the CHART_OF_ACCOUNTS_ID and FROM_ACCT_NUM_IN columns.
You would have to do the changes as per the guidelines below for the reusable lookup transformation
LKP_GROUP_ACCOUNT_NUM.
4. Add input ports for each additional segment used for mapping Group Account Number. You can
name the ports as SEG<x>_CODE_IN. Add lookup ports corresponding to the new columns
added to the target mentioned above.
5. You can rearrange the input ports in the lookup transformation to have it in the same order as in
the csv file to be consistent with the file i.e. CHART_OF_ACCOUNTS_IN, ACCT_CODE_IN
followed by the segment codes. The modified transformation should look like
6. Modify the lookup condition to include comparisons on the newly added segment ranges. The
modified condition looks like
You would have to do changes as per the guidelines given below in the
mplt_SA_ORA_GLAccountDimension mapplet used in the SDE_ORA_GLAccountDimension mapping.
7. First look at the code in the expression transformations EXP_GL_ACCOUNTS and
EXP_ARR_SEGMENTS. Notice that, in the EXP_GL_ACCOUNTS, the group account num is
being derived by calling the lookup LKP_GROUP_ACCOUNT_NUM. And using this group
account num, other attributes such as FIN_STMT_ITEM_CODE and GL_ACCT_CAT_CODE are
being derived. Also notice that, in the EXP_ARR_SEGMENTS, the segment equalization is being
done. The goal is to remove the group account num derivation and related attributes from
EXP_GL_ACCOUNTS and do it after the equalization of segments is done.
8. First, you need to remove the logic to call the lookups for GROUP_ACCT_NUM,
FIN_STMT_ITEM_CODE and GL_ACCT_CAT_CODE in EXP_GL_ACCOUNTS. For example,
remove the ports EXT_GL_ACCT_CAT_CODE, EXT_GL_ACCT_CAT_NAME,
EXT_FIN_STMT_ITEM_CODE, EXT_FIN_STMT_ITEM_NAME, EXT_GROUP_ACCT_NUM, and
EXT_GROUP_ACCT_NAME. You can also remove the variable ports such as
VAR_GRP_ACCT_NUM, VAR_FIN_STMT_ITEM_CODE, and VAL_GL_ACCT_CAT_CODE that
do the lookup to get these values.
9. Add another expression transformation which would come after EXP_ARR_SEGMENTS. This
would have input ports for chart of accounts, GL Account Num and the additional segments used.
10. In this transformation, take the INP_CHART_OF_ACCOUNTS, INP_GL_ACCOUNT_NUM from
EXP_GL_ACCOUNTS. For the segment ports, you can take the inputs from
EXP_GL_ACCOUNTS if you have used qualified segments (Cost Center or Profit Center) or in
case if other segments have been used, you can use the equalized segment ports
EXT_ACCT_SEG<x>_CODE from EXP_ARR_SEGMENTS as inputs. You need only the
segments that you have used in addition to natural account when you created the csv file in the
first step. You don’t need the other segments in this transformation.
11. Add a variable port, say VAR_GRP_ACCT_NUM, where you would call the lookup to derive the
group account num based on the chart of accounts, GL Account port and the additional segment
ports created above. Remember to use the ports in the same order as defined in the lookup (Step
5). For example, the expression would look like
:LKP.LKP_GROUP_ACCOUNT_NUM (INP_CHART_OF_ACCOUNTS, INP_GL_ACCOUNT_NUM,
INP_SEG1, INP_SEG2)
12. Add 2 more variable ports one each for Fin Stmt Item Code and GL Account Cat Code and use
the appropriate lookups to get the values similar to how it was originally done in
EXP_GL_ACCOUNTS.
13. Create output ports for each of these variables and set the value to the variable port, similar to
how it was originally done in EXP_GL_ACCOUNTS.
14. Map the output ports from the transformation to the corresponding ports in the mapplet output.
The newly added expression should look like the one given below.
Note: With this change, the mapping which is used for Group Account Clean Up is also impacted. Earlier
only GL Account Num was used for calculating the changed Group Account Numbers. Now the additional
segments mentioned above also have to be used in conjunction with GL Account.
15. Edit the mapping SDE_ORA_Stage_GLAccountDimension_GroupChange to modify the filter
condition to include the range joins on the new segments along with the range join on GL Account.
16. Replace the where clause in the SQ transformation to use the new filter which will be similar to
the one given below.
W_GL_ACCOUNT_D.CHART_OF_ACCOUNTS = W_ORA_GROUP_ACCOUNT_NUM_D_TMP.CHART_OF_ACCOUNTS_ID
AND W_GL_ACCOUNT_D.GL_ACCOUNT_NUM BETWEEN W_ORA_GROUP_ACCOUNT_NUM_D_TMP.FROM_ACCT_NUM
AND W_ORA_GROUP_ACCOUNT_NUM_D_TMP.TO_ACCT_NUM AND
W_GL_ACCOUNT_D.ACCOUNT_SEG<X>_CODE BETWEEN W_ORA_GROUP_ACCOUNT_NUM_D_TMP.FROM_SEG1 AND
W_ORA_GROUP_ACCOUNT_NUM_D_TMP.TO_SEG1 AND
W_GL_ACCOUNT_D.ACCOUNT_SEG<X>_CODE BETWEEN W_ORA_GROUP_ACCOUNT_NUM_D_TMP.FROM_SEG2 AND
W_ORA_GROUP_ACCOUNT_NUM_D_TMP.TO_SEG2 AND
W_GL_ACCOUNT_D.GROUP_ACCOUNT_NUM <> W_ORA_GROUP_ACCOUNT_NUM_D_TMP.GROUP_ACCT_NUM AND
W_GL_ACCOUNT_D.DATASOURCE_NUM_ID = W_ORA_GROUP_ACCOUNT_NUM_D_TMP.DATASOURCE_NUM_ID AND
W_ORA_GROUP_ACCOUNT_NUM_D_TMP.GROUP_ACCT_NUM = W_GROUP_ACCT_FIN_STMT_D_TMP
.GROUP_ACCT_NUM
DAC Changes:
• Open the appropriate container and query for the table
W_ORA_GROUP_ACCOUNT_NUM_D_TMP. Add the new segment range columns as done in
Step 1 and save the changes.
Data Model Changes:
• The new segment columns need to be added to the table. Run ALTER TABLE commands on
your DW schema to add the columns to W_ORA_GROUP_ACCOUNT_NUM_D_TMP table.
2. Additional Steps for Multiple Chart of Accounts Configuration.
This section specifies the additional steps that you may have to do in case you have a multiple
Chart of Accounts configuration. This would be required when there is different Group Account
Number mapping mechanisms for different COA’s. For example, for COA1, your group Account
Number is based on Natural Account + 2 segments, for COA2, it is Natural Account + 1 segment
and for COA3 it is just Natural Account. In such cases you would have to make the additional
changes mentioned below.
Source File Changes:
• Let’s take an example where you have 3 COAs. For COA 101, the group account number is
determined using natural account, cost center and department segments. For COA 102, it’s
based on natural account and cost center. For COA 103, it’s based on natural account only. That
means, you have 3 distinct segments (natural account, cost center, department) among all the 3
COAs that are used for group account number derivation. So, have 3 pairs of range columns in
the file, as shown below. Designate one pair of range to one segment - for example, FROM
ACCT & TO ACCT is for natural account, FROM SEG1 & TO SEG1 is for cost center, and FROM
SEG2 & TO SEG2 is for department.
• In step c, when you are defining the ranges, fill the ranges that are applicable for the COA and
use a default value (such as NA) for the range which is not relevant for a particular COA. In this
example, you will populate all 3 ranges for COA 101, FROM ACCT, TO ACCT & FROM SEG1,
TO SEG1 for COA 102 and just FROM ACCT, TO ACCT for COA 103 and populate the default
value for the remaining ranges, as illustrated in the screenshot below.
• Note: Use a default value that wouldn’t be a valid segment value. For example, if you happen to
have a segment which has NA as a valid value, then you cannot use NA but pick something else.
You will be using the same default value you pick here in one of the Informatica mapping
changes explained in later sections.
Informatica Changes:
• In step 10, The INP_SEG<x> ports can’t be directly used as inputs to the lookup because the
group account number mapping mechanisms can be different for different chart of accounts.
Certain segments may not be applicable for some COA’s as explained above.
• In order to accommodate this, in the same expression transformation after the segment input
ports, add variable ports for each of the segments used. You can name the ports as
VAR_SEG<x>.
• The expression for each variable port has to check for every chart of account and set the value
accordingly depending on whether that segment is applicable for that COA or not. If that segment
is not applicable for that COA, then the default value NA has to be set as done in the file.
Remember to use the same default value as in the file.
• Taking the example of the one shown in the screenshot, the expression for VAR_SEG1 would be
IIF (INP_CHART_OF_ACCOUNTS='103','NA', INP_SEG1) and the expression for VAR_SEG2
would be IIF (INP_CHART_OF_ACCOUNTS='102','NA', IIF (INP_CHART_OF_ACCOUNTS=
'103','NA', INP_SEG2)).
• The expression for VAR_GRP_ACCT_NUM which calls the lookup function should use these
variable ports now. The expression in this case would look like.
:LKP.LKP_GROUP_ACCOUNT_NUM (INP_CHART_OF_ACCOUNTS, INP_GL_ACCOUNT_NUM,
VAR_SEG1, VAR_SEG2)
• The other changes remain the same.
Note: The Group Account Number Clean Up mapping mentioned in step 15 and step 16 does a range
join on all the segments used. So if a segment has been defaulted to NA value for some COA’s, then the
range joins should also use the default value. For this you will have to define case statements to check on
every COA and set it to a default value if required.
So the where clause mentioned in step 16 will change to something similar to the one given below
W_GL_ACCOUNT_D.CHART_OF_ACCOUNTS = W_ORA_GROUP_ACCOUNT_NUM_D_TMP.CHART_OF_ACCOUNTS_ID
AND W_GL_ACCOUNT_D.GL_ACCOUNT_NUM BETWEEN W_ORA_GROUP_ACCOUNT_NUM_D_TMP.FROM_ACCT_NUM
AND W_ORA_GROUP_ACCOUNT_NUM_D_TMP.TO_ACCT_NUM AND
(CASE WHEN W_GL_ACCOUNT_D.CHART_OF_ACCOUNTS IN ('103') THEN 'NA' ELSE
W_GL_ACCOUNT_D.ACCOUNT_SEG<X>_CODE END) BETWEEN
W_ORA_GROUP_ACCOUNT_NUM_D_TMP.FROM_SEG1 AND W_ORA_GROUP_ACCOUNT_NUM_D_TMP.TO_SEG1 AND
(CASE WHEN W_GL_ACCOUNT_D.CHART_OF_ACCOUNTS IN ('102','103') THEN 'NA' ELSE
W_GL_ACCOUNT_D.ACCOUNT_SEG<X>_CODE END) BETWEEN
W_ORA_GROUP_ACCOUNT_NUM_D_TMP.FROM_SEG2 AND W_ORA_GROUP_ACCOUNT_NUM_D_TMP.TO_SEG2 AND
W_GL_ACCOUNT_D.GROUP_ACCOUNT_NUM <> W_ORA_GROUP_ACCOUNT_NUM_D_TMP.GROUP_ACCT_NUM AND
W_GL_ACCOUNT_D.DATASOURCE_NUM_ID = W_ORA_GROUP_ACCOUNT_NUM_D_TMP.DATASOURCE_NUM_ID AND
W_ORA_GROUP_ACCOUNT_NUM_D_TMP.GROUP_ACCT_NUM = W_GROUP_ACCT_FIN_STMT_D_TMP
.GROUP_ACCT_NUM
