Custom Account Generation Process

Hi,

I am going to explain you about the customization of Account Generation Processes/Account generator workflows in various modules in Oracle Applications.

For all modules the basic profile option to run account generator workflow is "Account Generator:Run in Debug Mode". So Set the profile "Account Generator:Run in Debug Mode" to "Yes"

Account Payables:

 Workflow Name: Project Supplier Invoice Account Generation Process

 Internal Name:     PAAPINVW

   If the new invoice is created in the AP module (Oracle Payables à Invoice à Entry à Invoices) with the Project details. OR Invoice has interfaced from the other module like i-expense with Project details, we should definitely customize the ‘Project Supplier Invoice Account Generation Process (PAAPINVW.wft)’ workflow to create account in distributions.

What will happen if the workflow is not customized?

       You will get the error saying “APP-SQLAP-97734: The system can not generate distribution lines…..”. Please find the screen shot of the error below.

 

Error comes whenever you click on distributions button.

  

Solution:

   Need to customize the workflow PAAPINVW, create a custom process where in remove all dummy default account generator functions and create/assign your accounting structure using custom functions OR we can use standard functions as well.

Find the below image for the Original Process “Generate default account (DEFAULT_ACCOUNT_GENERATION)”

Steps to customize the workflow:

1. Copy the process and create another as it is
2. Remove all Dummy default account generator functions
3. Say if you have the accounting structure i.e code combination with 5 segments.

You need to assign values 5 times i.e you need to create 5 functions to assign each value.

4. You can directly assign each value using “Assign Value to Segment” function in the “Standard Flexfield Workflow (FNDFFWK)”. You usually get the Standard Flexfield Workflow () along with “Standard (WFSTD)” workflow whenever you open the PAAPINVW workflow.
5. Assign values in Node attributes of the function “Assign Value to Segement”
6. OR if you have a lookup to get the values of segments, then use the function “Segment Lookup Set value” to get the lookup values and then assign the value to segment. Use Node attributes here as well.
7. Use the standard function “Validate Code Combination” after assigning all segment values
8. Look at the screenshot below for the better understanding

How to set values in ‘Node Attributes’

  Double click on each function OR right click – Properties

  Click on Node attributes tab and assign the values. If you are going to get values from lookups get lookup name and the source (Expenditure Operating Unit) is the source in the screen shot. Use this lookup value while assigning the value. 

Where Expenditure Operating Unit is the standard item attribute, we can use values of standard item attributes to link in lookup value sets.

Node Attributes:

Lookup:

 *** You can create your custom activities depends upon the customization

After customizing the workflow assign the custom process in accounting flexfiled processes.

1.    Go to General ledger responsibility à Ctrl+L à Account Generation Processes à Find your custom accounting flexfiled structure

     Change the Process name to your custom process where the item type is ‘Project Supplier Invoice Account Generation’

2.  Set the profile option “PA: Allow Override of PA Distributions in AP/PO” to ‘No’.

     To override using the custom account generation process which we have created

Even after all customizations if you still gets the token error, please refer the following notes in Oracle Metalink for support.

R12: APXINWKB: APP-SQLAP-97734 Error Message Displayed At Invoice Distributions Generation Time: Distributions Cannot Be Generated For Line Because &TOKEN2 (Doc ID 850743.1)

Projects Supplier Invoice Account Generation Error: APP-SQLAP-97734: The System Cannot Generate Distributions (Doc ID 823787.1)

R12: APXINWKB: APP-SQLAP-97734 WHEN ENTERING INVOICE DISTRIBUTION (Doc ID 1360580.1)

i-Expenses:

 Workflow Name: Project Expense Report Account Generator

 Internal Name:     PAAPWEBX

Follow the same steps as above for customizing the workflow. Change the process in the accounting flexfiled structure in GL.

This workflow is going to fire/trigger whenever the user select the project while raising the expense in i-expenses module. After click on next the workflow triggers and creates the new/custom code combination before submitting the expense report.

Note: After submitting the expense report ‘Expenses (APEXP)’ workflow is going to trigger.

Project, Expenditure Orgainzation and task are going to enable in JSP page whenever we set the OIE profile options.

   Ex: OIE:Enable Project Allocations

         OIE:Enable Project Expenditure Organization

Note: A profile option OIE: Enable Project Allocations if set to "Yes with account allocations", then the Division and Cost center are enabled in Account Allocations tab of Expense Allocations Page of iExpenses. But with this if we update the Division this is not getting updated in the Expense report tables.

How to Build Accouting structure or Code combination id for Non-Project Expense Reports ? 

If the user is not selected the project while raising the expense report, this workflow will not launch/trigger.

Then how will the accounting structure/code combination creates in non project expense reports? With our triggering the accounting generator workflow?

   This is different concept altogether. In non project expense reports the accounting structure/code combination is going to build using the standard package (AP_WEB_CUS_ACCTG_PKG) with our customizations.

   This package has two main procedure for this customization. Those are

      ap_web_cus_acctg_pkg.getiscustombuildonly
          If this function returns 1, then your custom code in PL/SQL    ap_web_cus_acctg_pkgbuildaccount will be called.

    ap_web_cus_acctg_pkg.buildaccount
        This is where you actually build the default account for non-project expense line.

getiscustombuildonly API this must return 1 if you want your custom logic to kick-off.
If this API does not return 1, then iExpenses will use the person level default accounts from HRMS.

Sample code you can use is (from apps2fusion). You can find the real time code below.

  FUNCTION buildaccount(p_report_header_id      IN NUMBER
                       ,p_report_line_id        IN NUMBER
                       ,p_employee_id           IN NUMBER
                       ,p_cost_center           IN VARCHAR2
                       ,p_exp_type_parameter_id IN NUMBER
                       ,p_segments              IN ap_oie_kff_segments_t
                       ,p_ccid                  IN NUMBER
                       ,p_build_mode            IN VARCHAR2
                       ,p_new_segments          OUT NOCOPY ap_oie_kff_segments_t
                       ,p_new_ccid              OUT NOCOPY NUMBER
                       ,p_return_error_message  OUT NOCOPY VARCHAR2)
    RETURN BOOLEAN IS
    l_account_array fnd_flex_ext.segmentarray;
  BEGIN
    p_new_ccid := 240694; --do not hard code in realiy, this is just sample
    l_account_array(1) := 'FOCUST';
    l_account_array(2) := 'CONSULTANCY';
    l_account_array(3) := 'R12';
    l_account_array(4) := '165122';
    l_account_array(5) := '0';
    l_account_array(6) := '0';
    l_account_array(7) := '0';
    p_new_segments := ap_oie_kff_segments_t('');
    p_new_segments.EXTEND(l_account_array.COUNT);
    FOR n_ctr IN 1 .. l_account_array.COUNT LOOP
      p_new_segments(n_ctr) := l_account_array(n_ctr);
    END LOOP;
    RETURN TRUE;
  END buildaccount;

How to Configure GL Account and GL Segments for Oracle E-Business Suite

B.2.20 How to Configure GL Account and GL Segments for Oracle E-Business Suite

This section explains how to configure General Ledger Account and General Ledger Segments for Oracle E-Business Suite, and contains the following topics:

• Section B.2.20.1, "Overview"
• Section B.2.20.2, "Example of Data Configuration for a Chart of Accounts"
• Section B.2.20.3, "How to Set Up the GL Segment Configuration File"
• Section B.2.20.4, "How to Configure GL Segments and Hierarchies Using Value Set Definitions"

B.2.20.1 Overview

If you are deploying Oracle Financial Analytics, Oracle Procurement and Spend Analytics, or Oracle Supply Chain and Order Management Analytics, then you must configure GL account hierarchies as described in this topic.

Thirty segments are supported in which you can store accounting flexfields. Flexfields are flexible enough to support complex data configurations. For example:

• You can store data in any segment.
• You can use more or fewer segments per chart of accounts, as required.
• You can specify multiple segments for the same chart of accounts.

B.2.20.2 Example of Data Configuration for a Chart of Accounts

A single company might have a US chart of accounts and an APAC chart of accounts, with the following data configuration:

Table B-7 Example Chart of Accounts

Segment Type	US Chart of Account (4256) value	APAC Chart of Account (4257) value
Company	Stores in segment 3	Stores in segment 1
Natural Account	Stores in segment 4	Stores in segment 3
Cost Center	Stores in segment 5	Stores in segment 2
Geography	Stores in segment 2	Stores in segment 5
Line of Business (LOB)	Stores in segment 1	Stores in segment 4

This example shows that in US Chart of Account , 'Company' is stored in the segment 3 column in the Oracle E-Business Suite table GL_CODE_COMBINATIONS. In APAC Chart of Account, 'Company' is stored in the segment 1 column in GL_CODE_COMBINATIONS table. The objective of this configuration file is to ensure that when segment information is extracted into the Oracle Business Analytics Warehouse table W_GL_ACCOUNT_D, segments with the same nature from different chart of accounts are stored in the same column in W_GL_ACCOUNT_D.

For example, we can store 'Company' segments from US COA and APAC COA in the segment 1 column in W_GL_ACCOUNT_D; and Cost Center segments from US COA and APAC COA in the segment 2 column in W_GL_ACCOUNT_D, and so on.

B.2.20.3 How to Set Up the GL Segment Configuration File

Before you run the ETL process for GL accounts, you must specify the segments that you want to analyze. To specify the segments, you use the ETL configuration file named file_glacct_segment_config_<source_system>.csv.

Note:

The configuration file or files for this task are provided on installation of Oracle BI Applications at one of the following locations:

Source-independent files: <Oracle Home for BI>\biapps\etl\data_files\src_files\.

Source-specific files: <Oracle Home for BI>\biapps\etl\data_files\src_files\<source adaptor>.

Your system administrator will have copied these files to another location and configured ODI connections to read from this location. Work with your system administrator to obtain the files. When configuration is complete, your system administrator will need to copy the configured files to the location from which ODI reads these files.

Figure B-2 Example values in file_glacct_segment_config_ora.csv

In file_glacct_segment_config_ora.csv, you must specify the segments of the same type in the same column. For example, you might store all Cost Center segments from all charts of accounts in one column, and all Company segments from all charts of accounts in a separate column.

File file_glacct_segment_config_ora.csv contains a pair of columns for each accounting segment to be configured in the warehouse. In the 1st column, give the actual segment column name in Oracle E-Business Suite where this particular entity is stored. This column takes values such as SEGMENT1, SEGMENT2....SEGMENT30 (this is case sensitive). In the second column give the corresponding VALUESETID used for this COA and segment in Oracle E-Business Suite.

For example, you might want to do the following:

• Analyze GL account hierarchies using only Company, Cost Center, Natural Account, and LOB.
  You are not interested in using Geography for hierarchy analysis.
• Store all Company segments from all COAs in ACCOUNT_SEG1_CODE column in W_GL_ACCOUNT_D.
• Store all Cost Center segments from all COAs in ACCOUNT_SEG2_CODE column in W_GL_ACCOUNT_D.
• Store all Natural Account segments from all COAs in ACCOUNT_SEG3_CODE column in W_GL_ACCOUNT_D.
• Store all LOB segments from all COAs in ACCOUNT_SEG4_CODE column in W_GL_ACCOUNT_D.
  Note: Although the examples above are mapping Natural Account, Balancing Segment and Cost Center segments to one of the segment columns in the file, it is not required that you map these three segments in the file. This is because we have dedicated dimensions to populate these three segments and they will be populated automatically by default whether or not you map these three segments in this file. It is preferred that these three segments are not mapped in this file so as to avoid redundant segment dimensions giving the same information.

GL Segment Configuration for Budgetary Control

For Budgetary Control, the first two segments are reserved for Project and Program segments respectively. Therefore, to use one or both of these, configure file_glacct_segment_config_ora.csv in this particular order:

1. Put your Project segment column name in the 'SEG_PROJECT' column in the CSV file.

2. Put your Program segment column name in the 'SEG_PROGRAM' column in the CSV file.

If you do not have any one of these reserved segments in your source system, leave that particular segment empty in the CSV file.

B.2.20.4 How to Configure GL Segments and Hierarchies Using Value Set Definitions

Note:

The configuration file or files for this task are provided on installation of Oracle BI Applications at one of the following locations:

Source-independent files: <Oracle Home for BI>\biapps\etl\data_files\src_files\.

Source-specific files: <Oracle Home for BI>\biapps\etl\data_files\src_files\<source adaptor>.

Your system administrator will have copied these files to another location and configured ODI connections to read from this location. Work with your system administrator to obtain the files. When configuration is complete, your system administrator will need to copy the configured files to the location from which ODI reads these files.

1. Configure file_glacct_segment_config_ora.csv, as follows:
   1. Edit the file file_glacct_segment_config_ora.csv.
      For example, you might edit the file located in \src_files\EBS11510.
   2. Follow the steps in Section B.2.20.3, "How to Set Up the GL Segment Configuration File" to configure the file.
2. Edit the BI metadata repository (that is, the RPD file) for GL Segments and Hierarchies Using Value Set Definitions.
   The metadata contains multiple logical tables that represent each GL Segment, such as Dim_W_GL_SEGMENT_D_ProgramSegment, Dim_W_GL_SEGMENT_D_ProjectSegment, Dim_W_GL_SEGMENT_D_Segment1 and so on. Because all these logical tables are mapped to the same physical table, W_GL_SEGMENT_D, a filter should be specified in the logical table source of these logical tables in order to restrain the output of the logical table to get values pertaining to that particular segment. You must set the filter on the physical column SEGMENT_LOV_ID to the Value Set IDs that are applicable for that particular segment. The list of the Value Set IDs would be the same as the Value Set IDs you configured in the CSV file mentioned above.
   Specify a filter in the Business Model and Mapping layer of the Oracle BI Repository, as follows.
   1. In Oracle BI EE Administration Tool, edit the BI metadata repository (for example, OracleBIAnalyticsApps.rpd).
      The OracleBIAnalyticsApps.rpd file is located in ORACLE_INSTANCE\bifoundation\OracleBIServerComponent\coreapplication_obis<n>\repository.
   2. Expand each logical table, for example, Dim - GL Segment1, and open the logical table source under it. Display the Content tab. In the 'Use this WHERE clause…' box, apply a filter on the corresponding physical table alias of W_GL_SEGMENT_D.
      For example: "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_GL_SEGMENT_D_Segment1"."SEGMENT_LOV_ID" IN (comma separated values IDs).
   3. Enter all Value Set IDs, separated by commas that correspond to this segment.
3. Oracle Financial Analytics supports up to 30 segments in the GL Account dimension, and by default delivers ten GL Segment dimensions in the RPD. If you need more than ten GL Segments, perform the following steps to add new segments:
   In the Physical Layer:
   1. Create two new physical alias of W_GL_SEGMENT_D as "Dim_W_GL_SEGMENT_D_SegmentXX" and Dim_W_GL_SEGMENT_D_SegmentXX_GLAccount.
      To do this, right-click the physical table W_GL_SEGMENT_D and select New Object and then Alias.Name the new alias as "Dim_W_GL_SEGMENT_D_SegmentXX" and "Dim_W_GL_SEGMENT_D_SegmentXX_GLAccount".
   2. Create 4 new alias of W_GL_SEGMENT_DH as:
      - "Dim_W_GL_SEGMENT_DH_SegmentXX"
      - "Dim_W_GL_SEGMENT_DH_Security_SegmentXX"
      - "Dim_W_GL_SEGMENT_DH_SegmentXX_GLAccount"
      - "Dim_W_GL_SEGMENT_DH_Security_SegmentXX_GLAccount"
   3. Create a Foreign Key from "Dim_W_GL_SEGMENT_D_SegmentXX" to "Dim_W_GL_SEGMENT_DH_SegmentXX" and "Dim_W_GL_SEGMENT_DH_Security_SegmentXX".
      The foreign key is similar to the one from "Dim_W_GL_SEGMENT_D_Segment1" to "Dim_W_GL_SEGMENT_DH_Segment1" and "Dim_W_GL_SEGMENT_DH_Security_Segment1".
      The direction of the foreign key should be from W_GL_SEGMENT_DH to W_GL_SEGMENT_D; for example, on a '0/1': N cardinality join, W_GL_SEGMENT_DH will be on the '0/1' side and W_GL_SEGMENT_D will be on the 'N' side. See Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition for more information about how to create physical foreign key joins.
   4. Create a similar physical foreign key from "Dim_W_GL_SEGMENT_D_SegmentXX_GLAccount" to "Dim_W_GL_SEGMENT_DH_SegmentXX_GLAccount' and "Dim_W_GL_SEGMENT_DH_Security_SegmentXX_GLAccount".
   5. Similarly, create physical foreign key join between Dim_W_GL_SEGMENT_D_SegmentXX and Dim_W_GL_ACCOUNT_D, with W_GL_SEGMENT_D on the '1' side and W_GL_ACCOUNT_D on the 'N' side.
   6. Save your changes.
4. In the Business Model and Mapping Layer, do the following:
   1. Create a new logical table "Dim - GL SegmentXX" similar to "Dim – GL Segment1".
      This logical table should have a logical table source that is mapped to the physical tables created above (for example, it will have both Dim_W_GL_SEGMENT_DH_SegmentXX and Dim_W_GL_SEGMENT_DH_SegmentXX_GLAccount).
      This logical table should also have all attributes similar to "Dim – GL Segment1" properly mapped to the respective physical tables, Dim_W_GL_SEGMENT_DH_SegmentXX and Dim_W_GL_SEGMENT_DH_SegmentXX_GLAccount.
   2. In the Business Model Diagram, create a logical join from "Dim – GL SegmentXX" to all the relevant logical fact tables similar to "Dim – GL Segment1", with the GL Segment Dimension Logical table on the '0/1' side and the logical fact table on the 'N' side.
      To see all the relevant logical fact tables, first include Dim – GL Segment1 on the Business Model Diagram, and then right-click that table and select Add Direct Joins.
   3. Add the content filter in the logical table source of "Dim – GL SegmentXX" as described in the previous step.
   4. Create a dimension by right-clicking "Dim – GL SegmentXX", and select Create Dimension. Rename this to "GL SegmentXX". Make sure the drill-down structure is similar to "GL Segment1".
      If you are not sure how to do this, follow these steps: By default, the dimension will have two levels: the Grand Total Level and the Detail Level. Rename these levels to "All" and "Detail – GL Segment" respectively.
      Right-click the "All" level and select "New Object" and then "Child Level". Name this level as Tree Code And Version. Create a level under Tree Code And Version and name it as Level31. Similarly create a level under Level31 as Level30. Repeat this process until you have Level1 under Level2.
   5. Drag the "Detail – GL Segment" level under "Level1" so that it is the penultimate level of the hierarchy. Create another child level under "Detail – GL Segment" and name it as "Detail – GL Account".
   6. From the new logical table Dim - GL SegmentXX, drag the Segment Code, Segment Name, Segment Description, Segment Code Id and Segment Value Set Code attributes to the "Detail – GL Segment" level of the hierarchy. Similarly pull in the columns mentioned below for the remaining levels.
      Detail – GL Account – Segment Code – GL Account
      Levelxx – Levelxx Code, Levelxx Name, Levelxx Description and Levelxx Code Id
      Tree Code And Version – Tree Filter, Tree Version ID, Tree Version Name and Tree Code
   7. Navigate to the properties of each Level and from the Keys tab, create the appropriate keys for each level as mentioned below. Select the primary key and "Use for Display option" for each level as mentioned in the matrix below.
      Table B-8 Configuration values for GL Segments and Hierarchies Using Value Set Definitions

      Level	Key Name	Columns	Primary Key of that Level	Use for Display?
      Tree Code And Version	Tree Filter	Tree Filter	Y	Y
      Levelxx	Levelxx Code	Levelxx Code	Y	Y
      Levelxx	Levelxx ID	Levelxx Code Id	<empty>	<empty>
      Detail - GL Segment	Segment ID	Segment Code Id	Y	<empty>
      Detail - GL Segment	Segment Code	Segment Value Set Code and Segment Code	<empty>	Y
      Detail - GL Account	Segment Code - GL Account	Segment Code - GL Account	Y	Y

   8. Once you have created these new levels, you will have to set the aggregation content for all the Logical Table Sources of the newly created logical table created Dim - GL SegmentXX. Set the Aggregation Content in the Content tab for each LTS as mentioned below:
      Dim_W_GL_SEGMENT_DH_SegmentXX – Set the content level to "Detail – GL Segment".
      Dim _W_GL_SEGMENT_DH_SegmentXX_GLAccount – Set it to "Detail – GL Account".
   9. Set the aggregation content to all relevant fact logical table sources. Open all Logical Table Sources of all the logical fact tables that are relevant to the new logical table one at a time. Display the Content tab. If the LTS is applicable for that newly created segment, then set the aggregation content to "Detail – GL Account". If not, skip that logical table source and go to the next one.
   10. Drag your new "Dim - GL Segment XX" dimensions into the appropriate subject areas in the Presentation layer. Typically, you can expose these GL Segment dimensions in all subject areas where the GL Account dimension is exposed. You can also find all appropriate subject areas by right-clicking Dim – GL Segment1 and select Query Related Objects, then selecting Presentation, and then selecting Subject Area.
   11. Save your changes and check global consistency.
5. Each GL Segment denotes a certain meaningful ValueSet(s) in your OLTP. To clearly identify each segment in the report, you can rename the presentation table "GL SegmentX", logical dimension "GL SegmentX", and logical table "Dim - GL SegmentX" according to its own meaning.
   For example, if you populate Product segment into Segment1, you can rename logical table "Dim - GL Segment1" as "Dim – GL Segment Product" or any other appropriate name and then rename the tables in the Presentation layer accordingly.

B.2.79 How to Configure GL Account Hierarchies using FSG definitions for E-Business Suite

You must configure GL account hierarchies if you are deploying Oracle Financial Analytics, Oracle Procurement and Spend Analytics, and Oracle Supply Chain and Order Management Analytics.

For information on how to configure Hierarchies using GL Accounting flexfields value sets definitions, see Section B.2.20, "How to Configure GL Account and GL Segments for Oracle E-Business Suite".

If you need to define GL account hierarchies based on multiple segments within a chart of accounts, then you can use the Oracle FSG report definition in E-Business Suite to define them.

You should first use the Oracle FSG form to define a row set or a column set, then Oracle BI Applications will extract the row set or column set definition and convert them into hierarchies.

Oracle FSG hierarchies are extracted from following E-Business Suite source tables:

• RG_REPORT_AXIS_CONTENTS
  This table defines the relationship between the FSG report axis and GL code combinations. The GL code combinations with segment values within the value range defined for that axis are categorized as children of that axis.
• RG_REPORT_AXIS_SETS
  This table stores the information for each of the row set or column set you defined. There is one record in this table for each row or column set you defined. Each row includes an axis set identifier, a row or column set name, and a structure identifier to assign a specific chart of accounts to the row set or column set.
• RG_REPORT_CALCULATIONS
  This table stores formulas for calculating each row or column in the row or column set. An example of a row calculation might be to sum up the amount from the previous five rows. An example of a columns calculation might be to calculate column five by subtracting column four from column three.

For example, in Income Statement, 'Net Income' is the calculation result of 'Gross Profit from Revenue' minus 'Total Expense'. When converting to hierarchy, Net Income becomes the parent of 'Gross Profit from Revenue' and 'Total Expense'. Therefore, hierarchy can be derived based on the information in RG_REPORT_CALCULATION.

The following diagram shows an example hierarchy, with the top level Net Income node having two child nodes, Total Expense, and Gross Profit from Revn, and the Total Expense node having two child nodes, Operating Expense, and Depreciation Expense.

The following diagram shows how an income state is derived from a hierarchy:

This hierarchy would be converted into a flattened hierarchy and stored in W_HIERARCHY_D in the following format:

Table B-88 Example of Flattened Hierarchy Stored in W_HIERARCHY_D

HIER Name	HIER1	HIER2	HIER3	HIER4	HIER20
Income Statement	Net Income	Gross Profit...	Gross Profit...	Gross Profit...	Gross Profit...
Income Statement	Net Income	Total Expenses	Operating Expenses	Operating Expenses	Operating Expenses
Income Statement	Net Income	Total Expenses	Depreciation Expense	Depreciation Expense	Depreciation Expense

Fact tables join to the W_HIERARCHY_D table through the GL Account dimension table (W_GL_ACCOUNT_D).

The W_GL_ACCOUNT_D table contains six fields (HIER1_WID, HIER2_WID, HIER3_WID, ...., HIER6_WID), which are foreign keys to the W_HIERARCHY_D.row_wid. Therefore, each General Ledger Code combination can participate in up to six different hierarchies. You can decide which of the six hierarchies to drill on based on the column you use to join to W_HIERARCHY_D. For example, if you want to drill using the third hierarchy, you use W_GL_ACCOUNT_D.hier3_wid = W_HIERARCHY_D.row_wid.

Note:

Mathematical operators, such as '+', '-', '*', '/' (addition, subtraction, multiplication, division, and so on) are not extracted from the FSG definitions. For example, both A + B = C and A - B = C would give the same hierarchy, with a node C having two child nodes A and B, as shown in the following diagram:

About the ETL Process for Oracle FSG Report

Before you run the ETL process for GL accounts, you must specify the hierarchies that you want to reference. To specify the FSG hierarchies that you want to reference, use the file file_gl_hierarchy_assignment_ora.csv.

Note:

The configuration file or files for this task are provided on installation of Oracle BI Applications at one of the following locations:

Source-independent files: <Oracle Home for BI>\biapps\etl\data_files\src_files\.

Source-specific files: <Oracle Home for BI>\biapps\etl\data_files\src_files\<source adaptor>.

Your system administrator will have copied these files to another location and configured ODI connections to read from this location. Work with your system administrator to obtain the files. When configuration is complete, your system administrator will need to copy the configured files to the location from which ODI reads these files.

Figure B-4 Example values in file_gl_hierarchy_assignment_ora.csv

In this file, for each chart of accounts, you can specify six FSG hierarchies, using axis_set_id, which is a column from the RG_REPORT_AXIS_SETS table. It is the unique ID of a row set or column set you want to store in the GL account dimension table for the code combinations that belong to that chart of accounts.

The DATASOURCE_NUM_ID field specifies the data source to which the configurations apply. If you have multiple source systems, there might be a chart of accounts across the multiple source systems with the same ID. Therefore, you must use the DATASOURCE_NUM_ID value to distinguish between them.

For example, suppose you have an income statement FSG report and a balance sheet FSG report and you want to input both of their hierarchy structures into the data warehouse. Oracle BI Applications assumes that both reports are derived from the same set of GL accounts with CHART_OF_ACCOUNTS=101. The axis_set_id of the income statement is 1001, and for the balance sheet, it is 1003. The DATASOURCE_NUM_ID for this application is 2.

In addition, for those GL accounts that belong to the two reports, assume you want to associate their HIER1 column (in GL_ACCOUNT_D) with the income statement hierarchy structure and HIER3 column with balance sheet hierarchy structure.

In this case, you would add one row into file_gl_hierarchy_assignment_ora.csv with fields set as follows:

CHART OF ACCOUNTS - 101

HIER1_AXIS_SET_ID - 1001

HIER3_AXIS_SET_ID - 1003

DATASOURCE_NUM_ID - 2

(Leave the other row values blank.)

This row indicates that for all of the GL accounts with CHART_OF_ACCOUNTS=101 and DATASOURCE_NUM_ID=2, assigning hierarchies with axis_set_id=1001, null, 1003, null, null, null to HIER1~HIER6 columns respectively. Therefore, after extraction and loading, for those affected GL account rows, HIER1 column will be the foreign key to the income statement hierarchy row ID in W_HIERARCHY_D, and HIER3 column will be the foreign key to the balance sheet hierarchy row ID in W_HIERARCHY_D.

Note: Axis_set_id must be specified in file_gl_hierarchy_assignment_ora.csv for Financial Analytics to load the hierarchies.

To set up hierarchies with FSG Report Definition:

1. Configure the file_gl_hierarchy_assignment_ora.csv file to specify the hierarchies you want to reference for each CHART_OF_ACCOUNTS, as follows:
   1. Edit the file file_gl_hierarchy_assignment_ora.csv.
      Note:

      The configuration file or files for this task are provided on installation of Oracle BI Applications at one of the following locations:

      Source-independent files: <Oracle Home for BI>\biapps\etl\data_files\src_files\.

      Source-specific files: <Oracle Home for BI>\biapps\etl\data_files\src_files\<source adaptor>.

      Your system administrator will have copied these files to another location and configured ODI connections to read from this location. Work with your system administrator to obtain the files. When configuration is complete, your system administrator will need to copy the configured files to the location from which ODI reads these files.
   2. Specify the segments that you want to analyze.
   3. Save and close the file.
2. Configuration provided by default in the BI metadata repository relating to GL Account Hierarchy using FSG:
   1. Physical table aliases for GL Account Hierarchy using FSG (6) are created and joins created to GL Account Dimension table (Dim_W_GL_ACCOUNT_D).
      
   2. Logical tables for the above six dimension hierarchy physical tables are created along with the BMM joins to relevant logical facts.
   3. Appropriate logical Levels and content filters have been set for the 6 FSG Logical dimensions that are provided by default.
      
   4. All relevant Logical Table sources of the Logical Fact tables have been set with necessary Aggregation content for the six Logical dimensions that are provided by default.
      
3. The following additional configuration might be needed by the users to expose the necessary attributes relating to FSG.
   1. Using Oracle BI EE Administration Tool, in the Presentation layer of the Oracle BI Repository, drag the new hierarchies from appropriate Logical dimensions into the Presentation folder.
   2. If required, then rename the hierarchies in the presentation layers.

How to Set Up Accounts Payable Security for Oracle E-Business Suite

B.2.8 How to Set Up Accounts Payable Security for Oracle E-Business Suite

Financial Analytics supports security over Payables Invoicing Business Unit in Accounts Payable subject areas. This Business Unit is the same as Operating Unit Organizations in E-Business Suite, and the list of Operating Unit Organizations that a user has access to is determined by the grants in E-Business Suite.

Configuring Accounts Payable Security

In order for data security filters to be applied, appropriate initialization blocks need to be enabled depending on the deployed source system. To enable Accounts Payable security for E-Business Suite, enable Oracle E-Business Suite initialization block and make sure the initialization blocks of all other source systems are disabled. The initialization block names relevant to various source systems are given below. If more than one source system is deployed, then you must also enable the initialization blocks of those source systems. For example:

• Oracle Fusion Applications: Payables Business Unit
• Oracle E-Business Suite: Operating Unit Organizations EBS
• Oracle PeopleSoft: Payables Organizations

To enable initialization blocks, follow the steps below:

1. In Oracle BI EE Administration Tool, edit the BI metadata repository (RPD file).
2. Choose Manage, then Variables to display the Variables dialog.
3. Under Session – Initialization Blocks, open the initialization block that you need to enable.
4. Clear the Disabled check box.
5. Save the RPD file.

Configuring BI Duty Roles

The following BI Duty Roles are applicable to the Accounts Payable subject area.

• AP Analyst
• AP Manager

These duty roles control which subject areas and dashboard content the user get access to. These duty roles also ensure the data security filters are applied to all the queries. For more information about how to define new groups and mappings for Users and BI Roles, see Section B.2.44, "How to Define New Groups and Mappings for Users and BI Roles".

http://docs.oracle.com/cd/E38317_01/doc.11117/e36171/app_func_tasks.htm#sthref784

Calculated Percentage Column always showing result as 0% Zero Percentage in OBIEE 11g

You may also face similar issue while upgrading OBIEE to a newer version or if you upgrade some of your dashboards from OBIEE 10.1.3.4 to 11.1.1.5. The issue is that, some of the calculations under certain columns in the reports were showing zeros instead of the calculated values.  The requirement was to calculate a match rate % for some data based on another field that showed 'MATCH' or 'NO MATCH'. 

 The real-time scenario was

(SUM(CASE Table1.Column1 WHEN 'MATCH' THEN 1 ELSE 0 END)/COUNT(Table1.Column1))*100

However, this was returning 0% as the result. With the help of some blogs and questions raised on OBIEE threads I came to know that this happens when you have an integer in a calculation. The solution which I used is very simple and was successful in achieving the requirement. The very easy fix to this issue is to multiply the calculation by 1.0.  The updated calculation which works is shown below and provides a meaningful result.

(don't use 1, as this will not work.  Use 1.0)

((1.0*SUM(CASE Table1.Column1 WHEN 'MATCH' THEN 1 ELSE 0 END))/(1.0*COUNT(Table1.Column1)))*100

Types of Views in OBIEE

Types of Views in OBIEE

View Name	Description
Compound Layout	Use the compound layout view to assemble different views for display on a dashboard. On the Criteria tab, you can click the following button to access the compound layout view.
Title	Use the title view to add a title, a subtitle, a logo, a link to a custom online help page, and timestamps to the results.
Table	Use the table view to show results in a standard table. Users can navigate and drill down in the results. You can add totals, customize headings, and change the formula or aggregation rule for a column. You can also control the appearance of a column and its contents, and specify formatting to apply only if the contents of the column meet certain conditions. On the Criteria tab, you can click the following button to access the table view.
Chart	Use the chart view to drag and drop columns to a layout chart. You can customize the title, legend location, axis titles, and data labels. You can customize the size and scale of the chart, and control colors using a style sheet. Oracle BI Answers supports a variety of standard chart types, including bar charts, column charts, line charts, area charts, pie charts, and scatter charts. Custom chart subtypes include two-and-three-dimensional, absolute, clustered, stacked, combination, and custom. On the Criteria tab, you can click the following button to access the chart view.
Pivot Table	Use the pivot table view to take row, column, and section headings and swap them around to obtain different perspectives. You can drag and drop headings to pivot results, preview them, and apply the settings. Users can navigate through pivot tables and drill down into information. Users can create complex pivot tables that show aggregate and nonrelated totals next to the pivoted data, allowing for flexible analysis. For an interactive result set, elements can be placed in pages, allowing users to choose elements. On the Criteria tab, you can click the following button to access the pivot table view.
Gauge	Use the gauge view to show results as gauges, such as dial, bar, and bulb-style gauges.
Filters	Use the filters view to show the filters in effect for a request. Filters allow you to constrain a request to obtain results that answer a particular question.
Column Selector	Use the column selector view to permit users to dynamically change which columns appear in results. This allows users to analyze data along several dimensions. By changing the facts, users can dynamically alter the content of the results.
View Selector	Use the View Selector view to select a specific view of the results from among the saved views. When placed on a dashboard, the view selector appears as a drop-down list from which users can make a selection.
Legend	Use the Legend view to document the meaning of special formatting used in results, such as the meaning of custom colors applied to gauges.
Funnel Chart	Use the funnel chart view to show a three-dimensional chart that represents target and actual values using volume, level, and color. It is useful for depicting target values that decline over time, such as a sales pipeline.
Narrative	Use the narrative view to show the results as one or more paragraphs of text. You can type in a sentence with placeholders for each column in the results, and specify how rows should be separated.
Ticker	Use the ticker view to show the results of the request as a ticker or marquee, similar in style to the stock tickers that run across many financial and news sites on the Internet. You can control what information is presented and how it scrolls across the page.
Static Text	Use the static text view to include static text in the results. You can use HTML to add banners, tickers, ActiveX objects, Java applets, links, instructions, descriptions, graphics, and so on, in the results.
No Results	The no results view allows you to specify explanatory text to appear if the request does not return any results.
Logical SQL	Use the logical SQL view to show the SQL generated for the request. This view is useful for trainers and Oracle BI administrators, and is usually not included in results for typical users. You cannot modify this view, except to delete it.
Create Segment	The create segment view is for users of the Oracle's Siebel Marketing Version 7.7 (or higher) operational application. Use it to display a Create Segment link in the results. Users can click this link to create a segment in their Oracle Siebel Marketing operational application, based on the results data.
Create Target List	The create target list view is for users of Oracle's Siebel Life Sciences operational application integrated with Oracle's Siebel Life Sciences Analytics applications. Use it to create a Create Target List link in the results. Users can click this link to create a target list, based on the results data, in their Oracle Siebel operational application.