1. Creating and Administering Analytics and Reports for SCM
  2. Use UOM Columns with ToUOM Session Variable in Dashboards, Analyses

Use UOM Columns with ToUOM Session Variable in Dashboards, Analyses

You can use predefined columns in some subject areas for Oracle Fusion Cloud Supply Chain Planning and the ToUOM session variable to author dashboards and analyses in which the primary or transaction unit of measure (UOM) for data is changed to a reporting UOM.

You can use these columns with the ToUOM session variable:

  • Reporting UOM Prompt: Use this column only to create prompts in dashboards and analyses. This column returns UOM values from INV_UNITS_OF_MEASURE_TL.UNIT_OF_MEASURE or UOM codes from INV_UNITS_OF_MEASURE_B.UOM_CODE.

  • UOM Conversion From Primary to Reporting: Use this column to get the conversion factor from the primary to the reporting UOM. Then, multiply the quantity in the primary UOM with the conversion factor to get the quantity in the reporting UOM.

  • UOM Conversion From Transaction to Reporting: Use this column to get the conversion factor from the transaction to the reporting UOM. Then, multiply the quantity in the transaction UOM with the conversion factor to get the quantity in the reporting UOM.

The predefined columns are available in these subject areas:

  • Costing - Work Order Costs Real Time

  • Inventory - Inventory Balance Real Time

  • Inventory - Inventory Transactions Real Time

  • Manufacturing - Actual Production Real Time

  • Order Management - Fulfillment Lines Real Time

  • Order Management - Order Lines Real Time

  • Shipping Real Time

You can use these columns and the ToUOM session variable in these ways:

  • Create a UOM prompt for a dashboard

  • Create a UOM prompt for an analysis

  • Set the ToUOM session variable to a UOM code in an analysis

Create a UOM Prompt for a Dashboard

To create a UOM prompt for a dashboard, follow these steps:

  1. Create an analysis for a subject area that has the predefined columns.

    To provide the user with the UOM conversion factor, include the UOM Conversion From Primary to Reporting or UOM Conversion From Transaction to Reporting column.

    1. Save the analysis.

  2. Create a column-based dashboard prompt for the subject area.

    1. Under Definition, click New, and select Column Prompt.

      The Select Column dialog box opens.

    2. Select the Reporting UOM Prompt column, and click OK.

      The New Prompt dialog box opens.

    3. Under Options, select Request Variable in the Set a variable list.

      A field appears under the list.

    4. Enter ToUOM in the field.

    5. Save the dashboard prompt.

  3. Create a dashboard.

    1. Add the dashboard prompt and analysis to the dashboard.

    2. Save the dashboard.

When you change the value in the Reporting UOM Prompt list of the dashboard, the UOM Conversion From Primary to Reporting or UOM Conversion From Transaction to Reporting column shows the conversion factor from the primary or transaction UOM to the reporting UOM.

Create a UOM Prompt for an Analysis

The procedure described in this section requires you to know the corresponding codes for UOM values.

To create a UOM prompt for an analysis, follow these steps:

  1. Create an analysis for a subject area that has the predefined columns.

    To provide the user with the UOM conversion factor, include the UOM Conversion From Primary to Reporting or UOM Conversion From Transaction to Reporting column.

  2. On the Prompts tab, click New, and select Variable Prompt.

    The New Prompt dialog box opens.

  3. In the Prompt for list, select Request Variable.

  4. In the field next to the Prompt for list, enter ToUOM.

  5. In the User Input list, select Choice List.

    The Choice List Values list appears.

  6. In the list, select SQL Results.

    A field for entering SQL code appears.

  7. Replace the SQL statement with another in the following format: SELECT DESCRIPTOR_IDOF("<subject area>"."Reporting UOM Prompt") FROM "<subject area>" FETCH FIRST 65001 ROWS ONLY

    Substitute values for the text within the angle brackets.

    For example, a complete SQL statement is as follows: SELECT DESCRIPTOR_IDOF("Inventory - Inventory Balance Real Time"."- Main"."Reporting UOM Prompt") FROM "Inventory - Inventory Balance Real Time" FETCH FIRST 65001 ROWS ONLY

  8. Click OK.

  9. Save the analysis.

When you run the analysis, you're first prompted to select a UOM code in a list. When you click OK, the analysis results are displayed, and the UOM Conversion From Primary to Reporting or UOM Conversion From Transaction to Reporting column shows the conversion factor from the primary or transaction UOM to the reporting UOM.

Set the ToUOM Session Variable to a UOM Code in an Analysis

The procedure described in this section requires you to know the corresponding codes for UOM values.

To set the ToUOM session variable to a UOM code in an analysis, follow these steps:

  1. Create an analysis for a subject area that has the predefined columns.

    To provide the user with the UOM conversion factor, include the UOM Conversion From Primary to Reporting or UOM Conversion From Transaction to Reporting column.

  2. On the Advanced tab, under Advanced SQL Clauses, in Prefix, enter an SQL statement in the following format: SET VARIABLE ToUOM='<UOM Code>';

    Substitute a UOM code for the text within the angle brackets.

    For example, a complete SQL statement is as follows: SET VARIABLE ToUOM='B050';

  3. Click Apply SQL.

  4. Save the analysis.

When you run the analysis, the UOM Conversion From Primary to Reporting or UOM Conversion From Transaction to Reporting column shows the conversion factor from the primary or transaction UOM to the reporting UOM. Moreover, you can't select any other code for the reporting UOM.

Related Topics