General Issues and Workarounds

These topics involve general issues and workarounds for Oracle BI Applications.

Absence Fact Has Zero Rows

This issue applies to People Soft Absence Adaptor.

Assignment Integration ID in Absence Event dimension is populated without space where as Workforce fact table does populate with space, hence SIL Absence fact interface fails over the following join condition and no data is loaded to the Absence fact table:

W_WRKFC_EVT_F.ASSIGNMENT_ID = W_ABSENCE_EVENT_D.ASSIGNMENT_INTEGRATION_ID

Workaround

  1. Log in to ODI repository using ODI Studio.
  2. Navigate to the SDE_PSFT_xx_Adaptor mapping folder and open the SDE_PSFT_AbsenceEventDimension_Preload.W_ABSENCE_EVENT_TMP interface.
  3. Navigate to Quick Edit, then Mappings tab, then ASSIGNMENT_INTEGRATION_ID Target Column Name.
  4. Open Mapping Expression and change expression as follows: 
    from 
TRIM(SQ_ABSENCE_HIST.EMPLID)||'~'||TO_CHAR(SQ_ABSENCE_HIST.EMPL_RCD) 
to
SQ_ABSENCE_HIST.EMPLID||'~'||TO_CHAR(SQ_ABSENCE_HIST.EMPL_RCD
  5. Save the interface.
  6. Regenerate the scenario of this interface.
  7. Regenerate the load plan.

ActivityResourcesFact and ActivityFact Failure

This issue applies to the Fusion adaptor.

ETL fails due to SDE_FUSION_ActivityFact and SDE_FUSION_ActivityResourcesFact failure.
  1. Log into the Oracle Business Intelligence Administration Tool.
  2. In the Physical Layer, navigate to the Import Metadata option for CRManalytics.
  3. Select Activity, Activity Assignee, and ActivityContact view objects and click Import Selected.
  4. After importing, click Finish.

TABS in Sourcing Dashboard Do Not Show Report or Graph

Workaround

  1. Login to the environment with Admininstrator user.

  2. Browse the catalog to the Sourcing folder:

    /Shared/Procurement/Analytic Library/

  3. Find the Sourcing folder and click the More hyperlink below the Sourcing Folder and select Permissions.

  4. Select the two check boxes Apply permissions to Sub-folders and Apply permissions to item within folder below the Permissions dialog box and click OK.

    The logged in user should have at least one of these assigned roles to be able to access the content of the sourcing dashboard and reports in Release 9:

    • BI Administrator Role

    • Procurement Executive Analysis Duty

    • Procurement Managerial Analysis Duty

    • Procurement and Spend Executive for EBS

    • Procurement and Spend Executive for PSFT

    • Purchasing Buyer for EBS

    • Purchasing Buyer for PSFT

NLS Regional Settings Not Save After Logout/Login

This issue applies to customers who want to use Configuration Manager and Functional Setup Manager (FSM) in non-English languages.

The changes in task Preferences > Regional or Preferences > Language are not saved properly after you log out the application and log in again.

Workaround

After logging in, if the preference settings (for example, UI language, number format, date format, time format, time-zone) are not set to the values as required, then users can go to task Preferences > Regional or Preferences > Language, change the preferences as required and save, and then continue to use other tasks without logging out.

Market Basket Analysis Facts and Dimensions Not Supported

Due to performance issues, Market Basket analysis related facts and dimensions are not supported.

List of logical facts not supported:

  • - Fact - CRM - Next Order Same Account

  • - Fact - CRM - Next Order Same Contact

  • - Fact - CRM - Order Item Same

  • - Fact - CRM - Product Affinity

List of logical dimensions not supported:

  • - Dim - Market Basket Product

  • - Dim - Next Product Purchased

Workaround

There is no workaround for this issue.

Error While Running SDE_FUSION_9_ADAPTOR_SDE_FUSION_PROJECTTASKDIMENSION Session

This issue applies to Fusion Direct incremental load plan.

SDE_FUSION_9_ADAPTOR_SDE_FUSION_PROJECTTASKDIMENSION session runs into an error while calling remote service ADFService11G.

Workaround

  1. Log in to ODI Designer and select the SDE Fusion 9 folder.
  2. Expand SDE_FUSION_ProjectTaskDimension.
  3. Open the main interface SDE_FUSION_ProjectTaskDimension.W_TASK_DS.
  4. Navigate to the Quick Edit tab, open the Mappings section, and remove the expression from AUX1_CHANGED_ON_DT.
  5. Open the temp interface SDE_FUSION_ProjectTaskDimension.W_TASK_DS_SQ, navigate to the Quick Edit tab, open the Mappings section, and delete the column ProjectProgressPEOLastUpdateDate.
  6. Save.
  7. Right click on Packages-Scenarios and regenerate the SDE_FUSION_9_ADAPTOR_SDE_FUSION_PROJECTTASKDIMENSION scenario

Fusion Direct Full Load ETL Results in SDE_FUSION_HRPERSONDIMENSION Failure

This issue applies to Fusion adaptor.

Fusion direct full load ETL results in SDE_FUSION_HRPERSONDIMENSION failure.

Workaround

Note:

Ensure that you perform these steps before generating a new load plan.
  1. Log in to ODI Studio and navigate to the Designer tab.
  2. Navigate to SDE_FUSION_HRPersonDimension.W_HR_PERSON_DS_SQ_PERSONDFF interface.
  3. Set column s_k_5000 as CAST(PERSONDFF.s_k_5000 AS VARCHAR(18)).
  4. Set column PERSONDFF_SRC_LAST_UPDATE_DATE as RUN_REPLICATED_TRANSACTIONAL("#IS_SDS_DEPLOYED", PERSONDFF.LastUpdateDate,          PERSONDFF.CDC$_SRC_LAST_UPDATE_DATE).
  5. Regenerate the scenario.

Human Resources E-Business Suite Payroll Patches for Payroll Analytics

This issue applies to Human Resources Analytics Payroll Subject Area for E-Business Suite.

If you are implementing Human Resources Analytics Payroll Subject Area for E-Business Suite, then it is mandatory to follow the E-Business Suite Payroll patching policy mentioned in My Oracle Support Note 295406.1 Mandatory Family Pack / Rollup Patch (RUP) levels for Oracle Payroll.

Load Plan Schedules Missing After Regeneration

This issue applies to load plan regeneration.

After Load Plan is regenerated in Configuration Manager, the Load Plan schedule is missing.

Workaround

Before regenerating a Load Plan, remove all existing schedules and recreate them after regeneration is complete.

Non-supported Attributes and Metrics

Full DW Refresh is enforced on the Valuation Fact-Group (VALUATION_FG) on these ETL Adapters.

Peoplesoft:

On Peoplesoft datasource, the following Dimension Attributes are not supported:

"Lot"."Best By Date"
"Lot"."color"
"Lot"."Length"
"Lot"."Length UOM"
"Lot"."Thickness"
"Lot"."Thickness UOM"
"Lot"."Volume"
"Lot"."colume UOM"
"Lot"."Width"
"Lot"."Width UOM"
"Movement Type"."Transaction Action Code"
"Movement Type"."Transaction Action Description"

On Peoplesoft datasources, the following Metrics are not supported:

BMM:  "Core"."Fact - Supply Chain - Inventory Transaction"."Material Quantity"
BMM:  "Core"."Fact - Supply Chain - Inventory Transaction"."Material Amount"
BMM:  "Core"."Fact - Supply Chain - Inventory Transaction"."Total Material Amount Last 365 Days"
Presentation: "Inventory - Transactions".."Fact - Inventory Transactions"."Material Quantity"
Presentation: "Costing - Item Cost".."Fact - Costing - Item Cost"."Profit In Inventory"
Presentation: "Costing - Item Cost".."Fact - Costing - Item Cost"."Percentage of Profit In Inventory"
BMM: “Core”.”Fact – Supply Chain - Inventory Balance”.”In Transit Quantity”
BMM: “Core”.”Fact – Supply Chain - Inventory Balance”.”Receiving Quantity”
BMM: “Core”.”Fact – Supply Chain - Inventory Balance”.”Allocated Quantity”
BMM: “Core”.”Fact – Supply Chain - Inventory Balance”.”Un Allocated Quantity”
BMM: “Core”.”Fact – Supply Chain - Inventory Balance”.” # of Products Requiring Reorder”
BMM: “Core”.”Fact – Supply Chain - Inventory Balance”.”Value Only Amount”
BMM: “Core”.”Fact – Supply Chain - Inventory Balance”.”Value Transfer Amount”
BMM: “Core”.”Fact – Supply Chain - Inventory Balance”.”Returned Quantity”
BMM: “Core”.”Fact – Supply Chain - Inventory Balance”.”Replenishment Quantity”
BMM: “Core”.”Fact – Supply Chain - Inventory Balance”.” Inspection Consignment Quantity”
BMM: “Core”.”Fact – Supply Chain - Inventory Balance”.”Reorder Point”
BMM: “Core”.”Fact – Supply Chain - Inventory Balance”.” Inspection Amount”
BMM: “Core”.”Fact – Supply Chain - Inventory Balance”.”Returned Amount”
BMM: “Core”.”Fact – Supply Chain - Inventory Balance”.”Replenishment Amount”
BMM: “Core”.”Fact – Supply Chain - Inventory Balance”.”Inspection Consignment Amount”
BMM: “Core”.”Fact – Supply Chain - Inventory Balance”.”WIP Amount”
BMM: “Core”.”Fact – Supply Chain - Inventory Balance”.” Returned Quantity Year Ago”
BMM: “Core”.”Fact – Supply Chain - Inventory Balance”.” Chg Returned Quantity Year Ago”
BMM: “Core”.”Fact – Supply Chain - Inventory Balance”.” Chg Returned Quantity % Year Ago”
Presentation: “Facts - Inventory Balance”.”In Transit Quantity”
Presentation: “Facts - Inventory Balance”.”Receiving Quantity”
Presentation: “Facts - Inventory Balance”.”Allocated Quantity”
Presentation: “Facts - Inventory Balance”.”Un Allocated Quantity”
Presentation: “Facts - Inventory Balance”.”Replenishment Quantity”
Presentation: “Facts - Inventory Balance”.”Inspection Consignment Quantity”
Presentation: “Facts - Inventory Balance”.”# of Products Requiring Reorder”

On Peoplesoft datasource, the following Presentation Subject Area is not supported:

Inventory - Bill of Materials

On Peoplesoft datasources, there is changed behavior on the following Attributes:

"Inventory - Transactions".."Inventory Details"."Debit Credit Flag"
"Inventory - Customer and Supplier Returns".."Inventory Details"."Debit Credit Flag"

An additional value B represents Bin-to-Bin transfers. It will show up as neither Debits or Credits in the reports.

On peoplesoft datasource, the following Logical Tables and corresponding Dimensions are not supported:

Dim - Balancing Segment
Dim - Product Transaction Type (Stays deprecated)
Dim - Cost Valuation Unit

On Peoplesoft datasources, CST_COST_ELEMENT and CST_COST_ELEMENT_TYPES source domains have an additional grain of SETID. Hence, you must set these in Configuration Manager with a pattern of  COST_ELEMENT~SETID.

Notes on behavior of specific Attributes and Metrics on Peoplesoft datasource:

  • BMM: "Core"."Fact - Supply Chain - Inventory Aging"."Days In Inventory" (affects of Date Received)

    The “Days In Inventory” metric is defined as the number of days between the Date Received and the date of data extraction. The Date Received, for PeopleSoft Inventory, is retrieved from the latest Receipt Header record which matches a particular Physical Inventory record. If no Receipt Header can be found, then the Staged Date of the Physical Inventory is used. If there is no associated Receipt Header and no Staged Date for a Physical Inventory record, then Days In Inventory will not be populated for that record.

  • BMM: "Core"."Fact - Supply Chain - Inventory Aging"."Days Left Expiry" and “Days Since Expired” (affects of Date Received)

    The “Days Left Expiry” and “Days Since Expired” metrics are defined as the days between the Expiration Date for a Physical Inventory item and the date of data extraction. For lot controlled items, the Expiration Date is derived from the lot control record. For other items, the Expiration Date is derived by adding the item’s Shelf Life to the Date Received.

  • Plant Location

    In the PeopleSoft adaptor, Plant Locations are retrieved for every Business Unit which is marked with a Business Unit Type of “PLNT”. Only “PLNT” type Business Units are represented in the Plant Location Dimension.

Correction to Column Precision Value W_PURCH_CHANGE_ORDER_F_DEL.DATASOURCE_NUM_ID

This issue applies to correcting the column precision value.

Precision value for number data type Column DATASOURCE_NUM_ID is wrongly set in the W_PURCH_CHANGE_ORDER_F_DEL table.

Workaround

  1. In ODI Studio, navigate to Designer > Models > Oracle BI Applications folder.
  2. Right click and open Oracle BI Applications > Delete folder > Open the W_PURCH_CHANGE_ORDER_F_DEL table.
  3. Edit the table. Go to Columns section and change the DATASOURCE_NUM_ID column logical length to 10 and change the scale to 0.
  4. Save the model and close.

Data Extraction Error 'XXXDFFBIVO of type View Definition is not found'

This issue applies to Fusion Applications on-premises data sources.

If during load plan execution you see the error message XXXDFFBIVO of type View Definition is not found(or similar), then you must check your Flexfield deployment. You might have an incomplete flexfield setup.

Workaround

Make sure that your flexfield setup is complete.

BI Metadata Repository Issue in Fusion Applications for Security View

Fusion Applications supports 18 levels of Territory Hierarchy, but the opaque security viewDim_Security_PrimaryTerritoryHierarchy_TerritoryResourceQuota' in the repository uses only 12 levels.

This results in a data security issue, where users are not able to view all records.

Workaround

  1. In Oracle BI Administration Tool, edit the repository.
  2. Click Physical, click Oracle Data Warehouse and select Dim_Security_PrimaryTerritoryHierarchy_TerritoryResourceQuota.
  3. Change the Default Initialization String to the following SQL statement:

    Note:

    In the SQL statement, for each of the Territory Hierarchy levels 0 to 17, repeat the 'OR' clause with a CASE statement 'case VALUEOF(n, NQ_SESSION.TERR_HIER_LEVEL_LIST)...'.
    select T.TERR_WID as TERR_WID,
'Y' as PRI_TERR_HIER_SEC_FLG
from W_TERR_DH T
where T.CURRENT_FLG = 'Y'
and
(
(
case VALUEOF(0, NQ_SESSION.TERR_HIER_LEVEL_LIST)
  when '0' then T.BASE_TERR_ID
  when '1' then T.LVL1_TERR_ID
  when '2' then T.LVL2_TERR_ID
  when '3' then T.LVL3_TERR_ID
  when '4' then T.LVL4_TERR_ID
  when '5' then T.LVL5_TERR_ID
  when '6' then T.LVL6_TERR_ID
  when '7' then T.LVL7_TERR_ID
  when '8' then T.LVL8_TERR_ID
  when '9' then T.LVL9_TERR_ID
  when '10' then T.LVL10_TERR_ID
  when '11' then T.LVL11_TERR_ID
  when '12' then T.LVL12_TERR_ID
  when '13' then T.LVL13_TERR_ID
  when '14' then T.LVL14_TERR_ID
  when '15' then T.LVL15_TERR_ID
  when '16' then T.LVL16_TERR_ID
  when '17' then T.TOP_LVL_TERR_ID
else 'NO_VALUE'
end
) in (VALUELISTOF(NQ_SESSION.SUPER_TERR_LIST))
or
(
case VALUEOF(1, NQ_SESSION.TERR_HIER_LEVEL_LIST)
  when '0' then T.BASE_TERR_ID
  when '1' then T.LVL1_TERR_ID
  when '2' then T.LVL2_TERR_ID
  when '3' then T.LVL3_TERR_ID
  when '4' then T.LVL4_TERR_ID
  when '5' then T.LVL5_TERR_ID
  when '6' then T.LVL6_TERR_ID
  when '7' then T.LVL7_TERR_ID
  when '8' then T.LVL8_TERR_ID
  when '9' then T.LVL9_TERR_ID
  when '10' then T.LVL10_TERR_ID
  when '11' then T.LVL11_TERR_ID
  when '12' then T.LVL12_TERR_ID
  when '13' then T.LVL13_TERR_ID
  when '14' then T.LVL14_TERR_ID
  when '15' then T.LVL15_TERR_ID
  when '16' then T.LVL16_TERR_ID
  when '17' then T.TOP_LVL_TERR_ID
else 'NO_VALUE'
end
) in (VALUELISTOF(NQ_SESSION.SUPER_TERR_LIST)) 
...
...and so on.
...
or
(
case VALUEOF(17, NQ_SESSION.TERR_HIER_LEVEL_LIST)
  when '0' then T.BASE_TERR_ID
  when '1' then T.LVL1_TERR_ID
  when '2' then T.LVL2_TERR_ID
  when '3' then T.LVL3_TERR_ID
  when '4' then T.LVL4_TERR_ID
  when '5' then T.LVL5_TERR_ID
  when '6' then T.LVL6_TERR_ID
  when '7' then T.LVL7_TERR_ID
  when '8' then T.LVL8_TERR_ID
  when '9' then T.LVL9_TERR_ID
  when '10' then T.LVL10_TERR_ID
  when '11' then T.LVL11_TERR_ID
  when '12' then T.LVL12_TERR_ID
  when '13' then T.LVL13_TERR_ID
  when '14' then T.LVL14_TERR_ID
  when '15' then T.LVL15_TERR_ID
  when '16' then T.LVL16_TERR_ID
  when '17' then T.TOP_LVL_TERR_ID
else 'NO_VALUE'
end
) in (VALUELISTOF(NQ_SESSION.SUPER_TERR_LIST))
)

Error During ATG Schema Upgrade

This issue applies to Fusion Application environments during the upgrade of the ATG schema, which results in a database error.

Note:

This issue appears when you upgrade from 11.1.1.9.2 to 11.1.10.1. However, this issue does not occur when you perform a fresh 11.1.1.10.1 install.
UPGAST-00176: Response file specifies an unknown upgrade step ATG.ATGLITE. The command failed to complete successfully.

Workaround

  1. Login into the database using sysdbacredentials.

  2. Execute the following query:

    update schema_version_registry set COMP_ID='ATGLITE' where COMP_ID='ATG' and OWNER='<BIACOMP_SCHEMA_OWNER>'

Error While Running ETL in Windows 2008

This issue applies to running ETL in Windows 2008.

To resolve the error that you may encounter while running ETL in Windows 2008, use the following workaround.

Workaround

While running ETL in Windows, ensure that the file path in Configuration Manager to register src uses forward slashes as follows:

C:/work/biappsdw10.1dw/biappsmw3/Oracle_BI1/biapps/etl/data_files/src_files/PSFT_9_0

Inactive Indexes for Qualified Segments

Unique indexes defined on W_COST_CENTER_DS, W_NATURAL_ACCOUNT_DS and W_BALANCING_SEGMENT_DS tables are made inactive in this release. Hence, drop these indexes from the Oracle Business Analytics Warehouse.

Workaround

Run the following drop index scripts on the Oracle Business Analytics Warehouse:

  • DROP INDEX W_COST_CENTER_DS_U1

  • DROP INDEX W_NATURAL_ACCOUNT_DS_U1

  • DROP INDEX W_BALANCING_SEGMENT_DS_U1

Note:

Ignore, if you get an error message saying that the specified index doesn't exist.

Limitation to Use One Subject Area Column for Fusion Cloud Adaptor

This issue applies to Fusion Cloud Adaptor.

If you are using the Fusion Cloud adaptor, then in subject area Procurement and Spend - Change Orders, do not use the Bill To Business Unit.Bill To Business Unit Default Set Name column for reporting.

Workaround

There is no workaround.

No External Data Support for UOM

For External data support, Unit Of Measure (UOM) maps need to call PL/SQL procedures in E-Business Suite to get the UOM conversions.

Workaround

Follow these instructions to manually generate the UOM Data from the E-Business Suite (EBS) source:

  1. Use the four SQL code examples to manually generate the UOM Data from the E-Business Suite (EBS) source.

    1. Replace the #BIAPPS.LANGUAGE_BASE with the correct values for your implementation.

    2. For an Incremental Load, replace 1=1 with the following filter value at places highlighted in the queries:

      CONV.LAST_UPDATE_DATE>TO_DATE(SUBSTR('#BIAPPS.LAST_EXTRACT_DATE',0,19),'YYYY-MM-DD HH24:MI:SS')
Example with values - CONV.LAST_UPDATE_DATE>TO_DATE(SUBSTR('2015-07-19 06:32:34',0,19),'YYYY-MM-DD HH24:MI:SS')

  2. Run each of these SQL code examples and create the csv file. The Name of the csv file should be in the following format:

    file_W_SDS_UOM_CONVERSION_....<SYSTIME>

  3. Copy this csv file in the Replicator directory where other csv files are stored.

  4. Ensure that the IS_SDS_DEPLOYED parameter is set to Y at the global level and at the Dimension Group Level for UOM_DIM. Use the Manage Dataload Parameters dialog in Configuration Manager to set the parameter.

  5. Continue with the normal load.

SQL Code Examples

Use the following SQL code examples:

Interclass

SELECT TO_CHAR(SQ_MTL_UOM_CONV_INTERCLASS.INVENTORY_ITEM_ID) PRODUCT_ID,
  TO_CHAR(SQ_MTL_UOM_CONV_INTERCLASS.ConversionRate) CONVERSION_RATE,
  TO_CHAR(SQ_MTL_UOM_CONV_INTERCLASS.CREATION_DATE, 'YYYY-MM-DD') CREATED_ON_DT,
TO_CHAR(NVL(SQ_MTL_UOM_CONV_INTERCLASS.LAST_UPDATE_DATE, SYSDATE), 'YYYY-MM-DD') CHANGED_ON_DT,
  TO_CHAR(SQ_MTL_UOM_CONV_INTERCLASS.CREATED_BY) CREATED_BY_ID,
  TO_CHAR(SQ_MTL_UOM_CONV_INTERCLASS.LAST_UPDATED_BY) CHANGED_BY_ID,
  SQ_MTL_UOM_CONV_INTERCLASS.X_CUSTOM X_CUSTOM,
  SQ_MTL_UOM_CONV_INTERCLASS.FROM_UOM_CODE FROM_UOM_CODE,
  SQ_MTL_UOM_CONV_INTERCLASS.TO_UOM_CODE TO_UOM_CODE,
  TO_CHAR(SQ_MTL_UOM_CONV_INTERCLASS.INVENTORY_ITEM_ID) INVENTORY_ITEM_ID
FROM
  (
  /* Subselect from SDE_ORA_UOMConversionGeneral_InterClass.W_UOM_CONVERSION_GS_SQ_MTL_UOM_CONV_INTERCLASS
  */
  SELECT UOM_CLASS_CONVERSIONS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID,
    UOM_CLASS_CONVERSIONS.FROM_UOM_CODE FROM_UOM_CODE,
    UOM_CLASS_CONVERSIONS.TO_UOM_CODE TO_UOM_CODE,
    UOM_CLASS_CONVERSIONS.LAST_UPDATE_DATE LAST_UPDATE_DATE,
    UOM_CLASS_CONVERSIONS.LAST_UPDATED_BY LAST_UPDATED_BY,
    UOM_CLASS_CONVERSIONS.CREATION_DATE CREATION_DATE,
    UOM_CLASS_CONVERSIONS.CREATED_BY CREATED_BY,
    UOM_CLASS_CONVERSIONS.X_CUSTOM X_CUSTOM,
    INV_CONVERT.INV_UM_CONVERT_NEW( UOM_CLASS_CONVERSIONS.INVENTORY_ITEM_ID, 10, NULL, UOM_CLASS_CONVERSIONS.FROM_UOM_CODE, UOM_CLASS_CONVERSIONS.TO_UOM_CODE, NULL, NULL, 'U' ) ConversionRate
  FROM
    (
    /* Subselect from SDE_ORA_UOMConversionGeneral_InterClass.W_UOM_CONVERSION_GS_INTERCLASS_CLASS_CONVERSIONS
    */
    SELECT CONV.INVENTORY_ITEM_ID INVENTORY_ITEM_ID,
      UOM_FROM.UOM_CODE FROM_UOM_CODE,
      UOM_TO.UOM_CODE TO_UOM_CODE,
      NULL LAST_UPDATE_DATE,
      '0' LAST_UPDATED_BY,
      NULL CREATION_DATE,
      '0' CREATED_BY,
      '0' X_CUSTOM
    FROM APPS.MTL_UNITS_OF_MEASURE UOM_FROM,
      APPS.MTL_UNITS_OF_MEASURE UOM_TO,
      APPS.MTL_UOM_CLASS_CONVERSIONS CONV,
      APPS.MTL_SYSTEM_ITEMS_B ITEM
    WHERE (1                                =1)
    AND (UOM_TO.UOM_CODE                    =ITEM.PRIMARY_UOM_CODE
    AND CONV.INVENTORY_ITEM_ID              =ITEM.INVENTORY_ITEM_ID)
    AND (UOM_FROM.UOM_CLASS                 =CONV.FROM_UOM_CLASS)
    AND (UOM_TO.UOM_CLASS                   =CONV.TO_UOM_CLASS)
    AND (UOM_TO.LANGUAGE                    ='#BIAPPS.LANGUAGE_BASE')
    AND (UOM_FROM.LANGUAGE                  ='#BIAPPS.LANGUAGE_BASE')
    AND (NVL(CONV.DISABLE_DATE,SYSDATE)    >=SYSDATE)
    AND (NVL(UOM_FROM.DISABLE_DATE,SYSDATE)>=SYSDATE)
    AND (NVL(UOM_TO.DISABLE_DATE,SYSDATE)  >=SYSDATE)
    AND ( 1                                 =1 )
    UNION
    SELECT CONV.INVENTORY_ITEM_ID INVENTORY_ITEM_ID,
      UOM_TO.UOM_CODE FROM_UOM_CODE,
      UOM_FROM.UOM_CODE TO_UOM_CODE,
      NULL LAST_UPDATE_DATE,
      '0' LAST_UPDATED_BY,
      NULL CREATION_DATE,
      '0' CREATED_BY,
      '0' X_CUSTOM
    FROM APPS.MTL_UNITS_OF_MEASURE UOM_FROM,
      APPS.MTL_UNITS_OF_MEASURE UOM_TO,
      APPS.MTL_UOM_CLASS_CONVERSIONS CONV,
      APPS.MTL_SYSTEM_ITEMS_B ITEM
    WHERE (1                                =1)
    AND (CONV.INVENTORY_ITEM_ID             = ITEM.INVENTORY_ITEM_ID
    AND UOM_FROM.UOM_CODE                   = ITEM.PRIMARY_UOM_CODE)
    AND (UOM_FROM.UOM_CLASS                 =CONV.FROM_UOM_CLASS)
    AND (UOM_TO.UOM_CLASS                   =CONV.TO_UOM_CLASS)
    AND (UOM_FROM.LANGUAGE                  ='#BIAPPS.LANGUAGE_BASE')
    AND (UOM_TO.LANGUAGE                    ='#BIAPPS.LANGUAGE_BASE')
    AND (NVL(CONV.DISABLE_DATE,SYSDATE)    >=SYSDATE)
    AND (NVL(UOM_FROM.DISABLE_DATE,SYSDATE)>=SYSDATE)
    AND (NVL(UOM_TO.DISABLE_DATE,SYSDATE)  >=SYSDATE)
    AND ( 1                                 =1 )
    ) UOM_CLASS_CONVERSIONS
  WHERE (1=1)
  MINUS
  SELECT UOM_NON_CLASS_CONVERSIONS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID,
    UOM_NON_CLASS_CONVERSIONS.FROM_UOM_CODE FROM_UOM_CODE,
    UOM_NON_CLASS_CONVERSIONS.TO_UOM_CODE TO_UOM_CODE,
    UOM_NON_CLASS_CONVERSIONS.LAST_UPDATE_DATE LAST_UPDATE_DATE,
    UOM_NON_CLASS_CONVERSIONS.LAST_UPDATED_BY LAST_UPDATED_BY,
    UOM_NON_CLASS_CONVERSIONS.CREATION_DATE CREATION_DATE,
    UOM_NON_CLASS_CONVERSIONS.CREATED_BY CREATED_BY,
    UOM_NON_CLASS_CONVERSIONS.X_CUSTOM X_CUSTOM,
    INV_CONVERT.INV_UM_CONVERT_NEW( UOM_NON_CLASS_CONVERSIONS.INVENTORY_ITEM_ID, 10, NULL, UOM_NON_CLASS_CONVERSIONS.FROM_UOM_CODE, UOM_NON_CLASS_CONVERSIONS.TO_UOM_CODE, NULL, NULL, 'U' ) ConversionRate
  FROM
    (
    /* Subselect from SDE_ORA_UOMConversionGeneral_InterClass.W_UOM_CONVERSION_GS_INTERCLASS_NON_CLASS_CONVERSIONS
    */
    SELECT CONV.INVENTORY_ITEM_ID INVENTORY_ITEM_ID,
      FROM_UOM.UOM_CODE FROM_UOM_CODE,
      TO_UOM.UOM_CODE TO_UOM_CODE,
      NULL LAST_UPDATE_DATE,
      '0' LAST_UPDATED_BY,
      NULL CREATION_DATE,
      '0' CREATED_BY,
      '0' X_CUSTOM
    FROM APPS.MTL_UOM_CONVERSIONS CONV,
      APPS.MTL_UNITS_OF_MEASURE FROM_UOM,
      APPS.MTL_UNITS_OF_MEASURE TO_UOM,
      APPS.MTL_SYSTEM_ITEMS_B ITEM
    WHERE (1                              =1)
    AND (CONV.INVENTORY_ITEM_ID           =ITEM.INVENTORY_ITEM_ID
    AND TO_UOM.UOM_CODE                   =ITEM.PRIMARY_UOM_CODE)
    AND (CONV.UOM_CLASS                   =TO_UOM.UOM_CLASS)
    AND (CONV.UOM_CLASS                   =FROM_UOM.UOM_CLASS)
    AND (FROM_UOM.UNIT_OF_MEASURE         =CONV.UNIT_OF_MEASURE)
    AND (FROM_UOM.LANGUAGE                ='#BIAPPS.LANGUAGE_BASE')
    AND (TO_UOM.LANGUAGE                  ='#BIAPPS.LANGUAGE_BASE')
    AND (NVL(TO_UOM.DISABLE_DATE,SYSDATE)>=SYSDATE)
    AND (NVL(CONV.DISABLE_DATE,SYSDATE)  >=SYSDATE)
        AND ( 1                               =1 )
    UNION
    SELECT CONV.INVENTORY_ITEM_ID INVENTORY_ITEM_ID,
      FROM_UOM.UOM_CODE FROM_UOM_CODE,
      TO_UOM.UOM_CODE TO_UOM_CODE,
      NULL LAST_UPDATE_DATE,
      '0' LAST_UPDATED_BY,
      NULL CREATION_DATE,
      '0' CREATED_BY,
      '0' X_CUSTOM
    FROM APPS.MTL_UOM_CONVERSIONS CONV,
      APPS.MTL_UNITS_OF_MEASURE FROM_UOM,
      APPS.MTL_UNITS_OF_MEASURE TO_UOM,
      APPS.MTL_SYSTEM_ITEMS_B ITEM
    WHERE (1                                =1)
    AND (CONV.INVENTORY_ITEM_ID             =ITEM.INVENTORY_ITEM_ID
    AND TO_UOM.UOM_CODE                     =ITEM.PRIMARY_UOM_CODE)
    AND (CONV.UOM_CLASS                     =FROM_UOM.UOM_CLASS)
    AND (CONV.UOM_CLASS                     =TO_UOM.UOM_CLASS)
    AND (TO_UOM.UNIT_OF_MEASURE             =CONV.UNIT_OF_MEASURE)
    AND (FROM_UOM.LANGUAGE                  ='#BIAPPS.LANGUAGE_BASE')
    AND (TO_UOM.LANGUAGE                    ='#BIAPPS.LANGUAGE_BASE')
    AND (NVL(FROM_UOM.DISABLE_DATE,SYSDATE)>=SYSDATE)
    AND (NVL(CONV.DISABLE_DATE,SYSDATE)    >=SYSDATE)
    AND ( 1                                 =1 )
    ) UOM_NON_CLASS_CONVERSIONS
  WHERE (1=1)
  ) SQ_MTL_UOM_CONV_INTERCLASS
WHERE (1=1)

Interclass_Resource

SELECT TO_CHAR(SQ_MTL_UOM_CONV_INTERCLASS.INVENTORY_ITEM_ID) PRODUCT_ID,
  TO_CHAR(SQ_MTL_UOM_CONV_INTERCLASS.ConversionRate) CONVERSION_RATE,
  TO_CHAR(SQ_MTL_UOM_CONV_INTERCLASS.CREATION_DATE, 'YYYY-MM-DD') CREATED_ON_DT,
TO_CHAR(NVL(SQ_MTL_UOM_CONV_INTERCLASS.LAST_UPDATE_DATE, SYSDATE), 'YYYY-MM-DD') CHANGED_ON_DT,
  TO_CHAR(SQ_MTL_UOM_CONV_INTERCLASS.CREATED_BY) CREATED_BY_ID,
  TO_CHAR(SQ_MTL_UOM_CONV_INTERCLASS.LAST_UPDATED_BY) CHANGED_BY_ID,
  SQ_MTL_UOM_CONV_INTERCLASS.X_CUSTOM X_CUSTOM,
  SQ_MTL_UOM_CONV_INTERCLASS.FROM_UOM_CODE FROM_UOM_CODE,
  SQ_MTL_UOM_CONV_INTERCLASS.TO_UOM_CODE TO_UOM_CODE,
  TO_CHAR(SQ_MTL_UOM_CONV_INTERCLASS.INVENTORY_ITEM_ID) INVENTORY_ITEM_ID
FROM
  (
  /* Subselect from SDE_ORA_UOMConversionGeneral_InterClass_Resource.W_UOM_CONVERSION_GS_SQ_MTL_UOM_CONV_INTERCLASS
  */
  SELECT UOM_CLASS_CONVERSIONS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID,
    UOM_CLASS_CONVERSIONS.FROM_UOM_CODE FROM_UOM_CODE,
    UOM_CLASS_CONVERSIONS.TO_UOM_CODE TO_UOM_CODE,
    UOM_CLASS_CONVERSIONS.LAST_UPDATE_DATE LAST_UPDATE_DATE,
    UOM_CLASS_CONVERSIONS.LAST_UPDATED_BY LAST_UPDATED_BY,
    UOM_CLASS_CONVERSIONS.CREATION_DATE CREATION_DATE,
    UOM_CLASS_CONVERSIONS.CREATED_BY CREATED_BY,
    UOM_CLASS_CONVERSIONS.X_CUSTOM X_CUSTOM,
    INV_CONVERT.INV_UM_CONVERT_NEW( UOM_CLASS_CONVERSIONS.INVENTORY_ITEM_ID, 10, NULL, UOM_CLASS_CONVERSIONS.FROM_UOM_CODE, UOM_CLASS_CONVERSIONS.TO_UOM_CODE, NULL, NULL, 'U' ) ConversionRate
  FROM
    (
    /* Subselect from SDE_ORA_UOMConversionGeneral_InterClass_Resource.W_UOM_CONVERSION_GS_INTERCLASS_CLASS_CONVERSIONS
    */
    SELECT CONV.INVENTORY_ITEM_ID INVENTORY_ITEM_ID,
      UOM_FROM.UOM_CODE FROM_UOM_CODE,
      UOM_TO.UOM_CODE TO_UOM_CODE,
      NULL LAST_UPDATE_DATE,
      '0' LAST_UPDATED_BY,
      NULL CREATION_DATE,
      '0' CREATED_BY,
      '0' X_CUSTOM
    FROM APPS.MTL_UNITS_OF_MEASURE UOM_FROM,
      APPS.MTL_UNITS_OF_MEASURE UOM_TO,
      APPS.MTL_UOM_CLASS_CONVERSIONS CONV
    WHERE (1                                =1)
    AND (UOM_TO.UOM_CLASS                   =CONV.TO_UOM_CLASS)
    AND (UOM_FROM.UOM_CLASS                 =CONV.FROM_UOM_CLASS)
    AND ( 1                                 =1 )
    AND (NVL(UOM_FROM.DISABLE_DATE,SYSDATE)>=SYSDATE)
    AND (NVL(UOM_TO.DISABLE_DATE,SYSDATE)  >=SYSDATE)
    AND (NVL(CONV.DISABLE_DATE,SYSDATE)    >=SYSDATE)
    AND (UOM_TO.LANGUAGE                    ='#BIAPPS.LANGUAGE_BASE')
    AND (UOM_FROM.LANGUAGE                  ='#BIAPPS.LANGUAGE_BASE')
    AND (CONV.INVENTORY_ITEM_ID             =0)
    UNION
    SELECT CONV.INVENTORY_ITEM_ID INVENTORY_ITEM_ID,
      UOM_TO.UOM_CODE FROM_UOM_CODE,
      UOM_FROM.UOM_CODE TO_UOM_CODE,
      NULL LAST_UPDATE_DATE,
      '0' LAST_UPDATED_BY,
      NULL CREATION_DATE,
      '0' CREATED_BY,
      '0' X_CUSTOM
    FROM APPS.MTL_UNITS_OF_MEASURE UOM_FROM,
      APPS.MTL_UNITS_OF_MEASURE UOM_TO,
      APPS.MTL_UOM_CLASS_CONVERSIONS CONV
    WHERE (1                                =1)
    AND (UOM_FROM.UOM_CLASS                 =CONV.FROM_UOM_CLASS)
    AND (UOM_TO.UOM_CLASS                   =CONV.TO_UOM_CLASS)
    AND ( 1                                 =1 )
    AND (NVL(UOM_FROM.DISABLE_DATE,SYSDATE)>=SYSDATE)
    AND (NVL(UOM_TO.DISABLE_DATE,SYSDATE)  >=SYSDATE)
    AND (NVL(CONV.DISABLE_DATE,SYSDATE)    >=SYSDATE)
    AND (UOM_TO.LANGUAGE                    ='#BIAPPS.LANGUAGE_BASE')
    AND (UOM_FROM.LANGUAGE                  ='#BIAPPS.LANGUAGE_BASE')
    AND (CONV.INVENTORY_ITEM_ID             =0)
    ) UOM_CLASS_CONVERSIONS
  WHERE (1=1)
  MINUS
  SELECT UOM_NON_CLASS_CONVERSIONS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID,
    UOM_NON_CLASS_CONVERSIONS.FROM_UOM_CODE FROM_UOM_CODE,
    UOM_NON_CLASS_CONVERSIONS.TO_UOM_CODE TO_UOM_CODE,
    UOM_NON_CLASS_CONVERSIONS.LAST_UPDATE_DATE LAST_UPDATE_DATE,
    UOM_NON_CLASS_CONVERSIONS.LAST_UPDATED_BY LAST_UPDATED_BY,
    UOM_NON_CLASS_CONVERSIONS.CREATION_DATE CREATION_DATE,
    UOM_NON_CLASS_CONVERSIONS.CREATED_BY CREATED_BY,
    UOM_NON_CLASS_CONVERSIONS.X_CUSTOM X_CUSTOM,
    INV_CONVERT.INV_UM_CONVERT_NEW( UOM_NON_CLASS_CONVERSIONS.INVENTORY_ITEM_ID, 10, NULL, UOM_NON_CLASS_CONVERSIONS.FROM_UOM_CODE, UOM_NON_CLASS_CONVERSIONS.TO_UOM_CODE, NULL, NULL, 'U' ) ConversionRate
  FROM
    (
    /* Subselect from SDE_ORA_UOMConversionGeneral_InterClass_Resource.W_UOM_CONVERSION_GS_INTERCLASS_NON_CLASS_CONVERSIONS
    */
    SELECT CONV.INVENTORY_ITEM_ID INVENTORY_ITEM_ID,
      FROM_UOM.UOM_CODE FROM_UOM_CODE,
      TO_UOM.UOM_CODE TO_UOM_CODE,
      NULL LAST_UPDATE_DATE,
      '0' LAST_UPDATED_BY,
      NULL CREATION_DATE,
      '0' CREATED_BY,
      '0' X_CUSTOM
    FROM APPS.MTL_UOM_CONVERSIONS CONV,
      APPS.MTL_UNITS_OF_MEASURE FROM_UOM,
      APPS.MTL_UNITS_OF_MEASURE TO_UOM
    WHERE (1                              =1)
    AND (CONV.UOM_CLASS                   =FROM_UOM.UOM_CLASS)
    AND (FROM_UOM.UNIT_OF_MEASURE         =CONV.UNIT_OF_MEASURE)
    AND (CONV.UOM_CLASS                   =TO_UOM.UOM_CLASS)
    AND (NVL(CONV.DISABLE_DATE,SYSDATE)  >=SYSDATE)
    AND (NVL(TO_UOM.DISABLE_DATE,SYSDATE)>=SYSDATE)
    AND ( 1                               =1 )
    AND (TO_UOM.LANGUAGE                  ='#BIAPPS.LANGUAGE_BASE')
    AND (FROM_UOM.LANGUAGE                ='#BIAPPS.LANGUAGE_BASE')
    AND (CONV.INVENTORY_ITEM_ID           =0)
    UNION
    SELECT CONV.INVENTORY_ITEM_ID INVENTORY_ITEM_ID,
      FROM_UOM.UOM_CODE FROM_UOM_CODE,
      TO_UOM.UOM_CODE TO_UOM_CODE,
      NULL LAST_UPDATE_DATE,
      '0' LAST_UPDATED_BY,
      NULL CREATION_DATE,
      '0' CREATED_BY,
      '0' X_CUSTOM
    FROM APPS.MTL_UOM_CONVERSIONS CONV,
      APPS.MTL_UNITS_OF_MEASURE FROM_UOM,
      APPS.MTL_UNITS_OF_MEASURE TO_UOM
    WHERE (1                                =1)
    AND (TO_UOM.UNIT_OF_MEASURE             =CONV.UNIT_OF_MEASURE)
    AND (CONV.UOM_CLASS                     =TO_UOM.UOM_CLASS)
    AND (CONV.UOM_CLASS                     =FROM_UOM.UOM_CLASS)
    AND ( 1                                 =1 )
    AND (NVL(FROM_UOM.DISABLE_DATE,SYSDATE)>=SYSDATE)
    AND (NVL(CONV.DISABLE_DATE,SYSDATE)    >=SYSDATE)
    AND (FROM_UOM.LANGUAGE                  ='#BIAPPS.LANGUAGE_BASE')
    AND (TO_UOM.LANGUAGE                    ='#BIAPPS.LANGUAGE_BASE')
    AND (CONV.INVENTORY_ITEM_ID             =0)
    ) UOM_NON_CLASS_CONVERSIONS
  WHERE (1=1)
  ) SQ_MTL_UOM_CONV_INTERCLASS
WHERE (1=1)

Intraclass

SELECT TO_CHAR(SQ_MTL_UOM_CONV_INTRACLASS.INVENTORY_ITEM_ID) PRODUCT_ID,
  TO_CHAR(SQ_MTL_UOM_CONV_INTRACLASS.ConversionRate) CONVERSION_RATE,
  TO_CHAR(SQ_MTL_UOM_CONV_INTRACLASS.CREATION_DATE, 'YYYY-MM-DD') CREATED_ON_DT,
TO_CHAR(NVL(SQ_MTL_UOM_CONV_INTRACLASS.LAST_UPDATE_DATE, SYSDATE), 'YYYY-MM-DD') CHANGED_ON_DT,
  TO_CHAR(SQ_MTL_UOM_CONV_INTRACLASS.CREATED_BY) CREATED_BY_ID,
  TO_CHAR(SQ_MTL_UOM_CONV_INTRACLASS.LAST_UPDATED_BY) CHANGED_BY_ID,
  SQ_MTL_UOM_CONV_INTRACLASS.X_CUSTOM X_CUSTOM,
  SQ_MTL_UOM_CONV_INTRACLASS.FROM_UOM_CODE FROM_UOM_CODE,
  SQ_MTL_UOM_CONV_INTRACLASS.TO_UOM_CODE TO_UOM_CODE,
  TO_CHAR(SQ_MTL_UOM_CONV_INTRACLASS.INVENTORY_ITEM_ID) INVENTORY_ITEM_ID
FROM
  (
  /* Subselect from SDE_ORA_UOMConversionGeneral_IntraClass.W_UOM_CONVERSION_GS_SQ_MTL_UOM_CONV_INTRACLASS
  */
  SELECT CONV.INVENTORY_ITEM_ID INVENTORY_ITEM_ID,
    FROM_UOM.UOM_CODE FROM_UOM_CODE,
    TO_UOM.UOM_CODE TO_UOM_CODE,
    NULL LAST_UPDATE_DATE,
    '0' LAST_UPDATED_BY,
    NULL CREATION_DATE,
    '0' CREATED_BY,
    '0' X_CUSTOM,
    INV_CONVERT.INV_UM_CONVERT_NEW( CONV.INVENTORY_ITEM_ID, 10, NULL, FROM_UOM.UOM_CODE, TO_UOM.UOM_CODE, NULL, NULL, 'U' ) ConversionRate
  FROM APPS.MTL_UOM_CONVERSIONS CONV,
    APPS.MTL_UNITS_OF_MEASURE FROM_UOM,
    APPS.MTL_UNITS_OF_MEASURE TO_UOM,
    APPS.MTL_SYSTEM_ITEMS_B ITEM
  WHERE (1                              =1)
  AND (TO_UOM.UOM_CODE                  = ITEM.PRIMARY_UOM_CODE
  AND CONV.INVENTORY_ITEM_ID            = ITEM.INVENTORY_ITEM_ID)
  AND (CONV.UOM_CLASS                   =TO_UOM.UOM_CLASS)
  AND (CONV.UOM_CLASS                   =FROM_UOM.UOM_CLASS)
  AND (FROM_UOM.UNIT_OF_MEASURE         =CONV.UNIT_OF_MEASURE)
  AND (FROM_UOM.LANGUAGE                ='#BIAPPS.LANGUAGE_BASE')
  AND (TO_UOM.LANGUAGE                  ='#BIAPPS.LANGUAGE_BASE')
  AND (NVL(TO_UOM.DISABLE_DATE,SYSDATE)>=SYSDATE)
  AND (NVL(CONV.DISABLE_DATE,SYSDATE)  >=SYSDATE)
  AND ( 1                               =1 )
  UNION
  SELECT CONV.INVENTORY_ITEM_ID INVENTORY_ITEM_ID,
    FROM_UOM.UOM_CODE FROM_UOM_CODE,
    TO_UOM.UOM_CODE TO_UOM_CODE,
    NULL LAST_UPDATE_DATE,
    '0' LAST_UPDATED_BY,
    NULL CREATION_DATE,
    '0' CREATED_BY,
    '0' X_CUSTOM,
    INV_CONVERT.INV_UM_CONVERT_NEW( CONV.INVENTORY_ITEM_ID, 10, NULL, FROM_UOM.UOM_CODE, TO_UOM.UOM_CODE, NULL, NULL, 'U' ) ConversionRate
  FROM APPS.MTL_UOM_CONVERSIONS CONV,
    APPS.MTL_UNITS_OF_MEASURE FROM_UOM,
    APPS.MTL_UNITS_OF_MEASURE TO_UOM,
    APPS.MTL_SYSTEM_ITEMS_B ITEM
  WHERE (1                                =1)
  AND (TO_UOM.UOM_CODE                    = ITEM.PRIMARY_UOM_CODE
  AND CONV.INVENTORY_ITEM_ID              =ITEM.INVENTORY_ITEM_ID)
  AND (CONV.UOM_CLASS                     =FROM_UOM.UOM_CLASS)
  AND (CONV.UOM_CLASS                     =TO_UOM.UOM_CLASS)
  AND (TO_UOM.UNIT_OF_MEASURE             =CONV.UNIT_OF_MEASURE)
  AND (FROM_UOM.LANGUAGE                  ='#BIAPPS.LANGUAGE_BASE')
  AND (TO_UOM.LANGUAGE                    ='#BIAPPS.LANGUAGE_BASE')
  AND (NVL(FROM_UOM.DISABLE_DATE,SYSDATE)>=SYSDATE)
  AND (NVL(CONV.DISABLE_DATE,SYSDATE)    >=SYSDATE)
  AND ( 1                                 =1 )
  ) SQ_MTL_UOM_CONV_INTRACLASS
WHERE (1=1)

Intraclass_Resource

SELECT TO_CHAR(SQ_MTL_UOM_CONV_INTRACLASS.INVENTORY_ITEM_ID) PRODUCT_ID,
  TO_CHAR(SQ_MTL_UOM_CONV_INTRACLASS.ConversionRate) CONVERSION_RATE,
  TO_CHAR(SQ_MTL_UOM_CONV_INTRACLASS.CREATION_DATE, 'YYYY-MM-DD') CREATED_ON_DT,
TO_CHAR(NVL(SQ_MTL_UOM_CONV_INTRACLASS.LAST_UPDATE_DATE, SYSDATE), 'YYYY-MM-DD') CHANGED_ON_DT,
  TO_CHAR(SQ_MTL_UOM_CONV_INTRACLASS.CREATED_BY) CREATED_BY_ID,
  TO_CHAR(SQ_MTL_UOM_CONV_INTRACLASS.LAST_UPDATED_BY) CHANGED_BY_ID,
  SQ_MTL_UOM_CONV_INTRACLASS.X_CUSTOM X_CUSTOM,
  SQ_MTL_UOM_CONV_INTRACLASS.FROM_UOM_CODE FROM_UOM_CODE,
  SQ_MTL_UOM_CONV_INTRACLASS.TO_UOM_CODE TO_UOM_CODE,
  TO_CHAR(SQ_MTL_UOM_CONV_INTRACLASS.INVENTORY_ITEM_ID) INVENTORY_ITEM_ID
FROM
  (
  /* Subselect from SDE_ORA_UOMConversionGeneral_IntraClass_Resource.W_UOM_CONVERSION_GS_SQ_MTL_UOM_CONV_INTRACLASS
  */
  SELECT CONV.INVENTORY_ITEM_ID INVENTORY_ITEM_ID,
    FROM_UOM.UOM_CODE FROM_UOM_CODE,
    TO_UOM.UOM_CODE TO_UOM_CODE,
    NULL LAST_UPDATE_DATE,
    '0' LAST_UPDATED_BY,
    NULL CREATION_DATE,
    '0' CREATED_BY,
    '0' X_CUSTOM,
    INV_CONVERT.INV_UM_CONVERT_NEW( CONV.INVENTORY_ITEM_ID, 10, NULL, FROM_UOM.UOM_CODE, TO_UOM.UOM_CODE, NULL, NULL, 'U' ) ConversionRate
  FROM APPS.MTL_UOM_CONVERSIONS CONV,
    APPS.MTL_UNITS_OF_MEASURE FROM_UOM,
    APPS.MTL_UNITS_OF_MEASURE TO_UOM
  WHERE (1                              =1)
  AND (CONV.UOM_CLASS                   =FROM_UOM.UOM_CLASS)
  AND (CONV.UOM_CLASS                   =TO_UOM.UOM_CLASS)
  AND (FROM_UOM.UNIT_OF_MEASURE         =CONV.UNIT_OF_MEASURE)
  AND (NVL(TO_UOM.DISABLE_DATE,SYSDATE)>=SYSDATE)
  AND (NVL(CONV.DISABLE_DATE,SYSDATE)  >=SYSDATE)
  AND ( 1                               =1 )
  AND (TO_UOM.LANGUAGE                  ='#BIAPPS.LANGUAGE_BASE')
  AND (FROM_UOM.LANGUAGE                ='#BIAPPS.LANGUAGE_BASE')
  AND (CONV.INVENTORY_ITEM_ID           =0)
  UNION
  SELECT CONV.INVENTORY_ITEM_ID INVENTORY_ITEM_ID,
    FROM_UOM.UOM_CODE FROM_UOM_CODE,
    TO_UOM.UOM_CODE TO_UOM_CODE,
    NULL LAST_UPDATE_DATE,
    '0' LAST_UPDATED_BY,
    NULL CREATION_DATE,
    '0' CREATED_BY,
    '0' X_CUSTOM,
    INV_CONVERT.INV_UM_CONVERT_NEW( CONV.INVENTORY_ITEM_ID, 10, NULL, FROM_UOM.UOM_CODE, TO_UOM.UOM_CODE, NULL, NULL, 'U' ) ConversionRate
  FROM APPS.MTL_UOM_CONVERSIONS CONV,
    APPS.MTL_UNITS_OF_MEASURE FROM_UOM,
    APPS.MTL_UNITS_OF_MEASURE TO_UOM
  WHERE (1                                =1)
  AND (CONV.UOM_CLASS                     =FROM_UOM.UOM_CLASS)
  AND (CONV.UOM_CLASS                     =TO_UOM.UOM_CLASS)
  AND (TO_UOM.UNIT_OF_MEASURE             =CONV.UNIT_OF_MEASURE)
  AND ( 1                                 =1 )
  AND (NVL(FROM_UOM.DISABLE_DATE,SYSDATE)>=SYSDATE)
  AND (NVL(CONV.DISABLE_DATE,SYSDATE)    >=SYSDATE)
  AND (FROM_UOM.LANGUAGE                  ='#BIAPPS.LANGUAGE_BASE')
  AND (TO_UOM.LANGUAGE                    ='#BIAPPS.LANGUAGE_BASE')
  AND (CONV.INVENTORY_ITEM_ID             =0)
  ) SQ_MTL_UOM_CONV_INTRACLASS
WHERE (1=1)

Metric Name Change for Average Resolution Rate in Service Analytics

This issue applies to metric name changes in Service Analytics.

In Service Analytics, the metric Average Resolution Rate has been renamed to Average Close Rate in the following subject areas:

  • Service - CRM Activities

  • Service - CRM Agreements

  • Service - CRM Customer Satisfaction

  • Service - CRM Service Requests

If this metric is being used in any custom reports, then those reports will no longer work. You need to modify each custom report to replace the old metric name with the new metric name.

Missing Predefined Object Duty Roles

This issue applies to the missing predefined object duty roles.

Oracle BI Applications Release 11.1.1.10.1 delivered the following new HR Subject Area's:

  • Human Resources - Workforce Compensation

  • Human Resources - Succession Planning

These subject areas have Oracle BI Enterprise Edition Permissions against the corresponding Application Roles:

  • OBIA_AU_HCM_WRKFC_COMP_DUTY

  • OBIA_AU_HCM_SUCC_PLNING_DUTY

However, these application roles are missing in the bi stripe/jazn of the Oracle BI Applications Release 11.1.1.10.1 installation files.

Workaround

If you need non-Administrator role access to these subject areas, then you need to add the Application Roles manually using Oracle Enterprise Manager Fusion Middleware Control. See: Managing Duty Roles in Oracle BI Applications, Oracle Business Intelligence Applications Security Guide.

Entered Currency Supporting Reference Balances Not Supported for Fusion Applications Release 9

Oracle BI Applications release 11.1.1.10.1 supports entered currency supporting reference balances only for Fusion Applications release 10. 

If you are using Fusion Applications release 9 as your OLTP source, then this feature is not available in Oracle BI Applications release 11.1.1.10.1.

Issues with UNIVERSAL Load Plan

This issue applies to registration of the Universal source in Configuration Manager.

The Universal source is registered in Configuration Manager with Load technology as None, so SDS_LOAD_TECHNO is null, but the generated load plan includes the step EXTERNAL_STORAGE_INTEGRATION_DOWNLOAD procedure to make calls to download files from storage service. This should not be the case for on-premise customers.

Workaround

  1. Log in to ODI repository using ODI client, navigate to load plan and scenario’s section, and go to BIAPPS Load Plan > Load Plan Dev Components > SDE LP folder > UNIVERSAL LP folder.
    Description of GUID-1357A271-7C35-4450-A7AC-282470449F44-default.gif follows
    Description of the illustration GUID-1357A271-7C35-4450-A7AC-282470449F44-default.gif
  2. Expand the UNIVERSAL LP folder, search for 3 SDE General Post Load UNIVERSAL load plan and open it.
    Description of GUID-FBC43CE5-A917-4F85-A76A-D80F191EFC28-default.gif follows
    Description of the illustration GUID-FBC43CE5-A917-4F85-A76A-D80F191EFC28-default.gif
  3. Disable the following steps:

    • Mark Manifest file as Completed

    • Clear Universal file Directory (Post Load)

    Description of GUID-F112542C-8C0A-4C56-BA35-20FF2D8AD9BF-default.gif follows
    Description of the illustration GUID-F112542C-8C0A-4C56-BA35-20FF2D8AD9BF-default.gif
  4. Search for 3 SDE General Pre Load UNIVERSAL load plan and open it.
    Description of GUID-DA73570F-601B-419D-82D1-7ED924D21F80-default.gif follows
    Description of the illustration GUID-DA73570F-601B-419D-82D1-7ED924D21F80-default.gif
  5. Disable the following steps:

    • External Storage Integration Download

    • Merge Universal Files

    Description of GUID-F4CC1517-177F-41AE-8897-A8837BE73E7C-default.gif follows
    Description of the illustration GUID-F4CC1517-177F-41AE-8897-A8837BE73E7C-default.gif

SDE_FUSION_PARTYORGANIZATIONDIMENSION Populate W/Data Has Gaps

This issue applies to SDE_FUSION_PartyOrganizationDimension.

Workaround

  1. Log in to ODI Studio and navigate to the Designer tab.
  2. Open BI Apps Project > Mappings > SDE_FUSION_x_Adaptor (where x can be 9, 10 or 11).
  3. Expand SDE_FUSION_PartyOrganizationDimension.
  4. Open MAIN interface (blue) SDE_FUSION_PartyOrganizationDimension.W_PARTY_ORG_DS.
  5. Go to column PRIMARY_PHONE_AREA_CODE and replace the existing expression with SQ_W_FSN_PARTY_ORG_PS.PRIMARY_PHONE_AREA_CODE.

Award_WID Not Populating in Existing Project Facts

This issue applies to Award_WID not getting populated in existing Project Budget and Cost facts.

Workaround

Use the CA_DETAIL_PROJ table to get the link between contract number, projects, and activities for the following existing facts:

  • W_PROJ_COST_LINE_FS

  • W_PROJ_INVOICE_LINE_FS

  • W_PROJ_REVENUE_LINE_FS

  • W_PROJ_BUDGET_FS

  • W_PROJ_FORECAST_FS

  • W_PROJ_COMMITMENT_FS

Note:

Please contact Oracle Support for more information regarding the workaround.

Load Plan Fails Due to Malformed PARTITION BY Clause in Auto Correction SQL

This issue applies to load plan runs.

If an interface task meeting the following conditions fails, then diagnostic auto correction is not able to handle it:

  • Task uses Incremental Update or Fact Incremental Update IKM with KM options - INSERT=false and UPDATE=true.

  • Alternate keys defined in model for corresponding target data store are not mapped in interface.

The interface task fails with the Missing Expression error due to malformed PARITION BY clause in Diagnostics - Find Dupes,Cnstrnt Vltn rows on I$ KM step.

Workaround

To avoid this failure in future load plan runs, you must fix the data or system issue that led to failure of the task. Then you must disable diagnostics auto correction for that task in the load plan components. Lastly, generate and start the new load plan.

To disable diagnostics for the applicable scenario:

  1. Open ODI Studio.
  2. Open Designer Navigator’s Load Plans and Scenarios accordian, click BIAPPS Load Plans, and navigate to Load Plan Dev Components.
  3. Locate the scenario step by traversing the hierarchy.
  4. Select scenario step, set DIAG_ERR_LOG_SUPPORTED variable value in property inspector to N. Ensure that the Overwrite check box is checked and Refresh check box is unchecked.
  5. Save the load plan component.
  6. Generate and rerun the load plan.

Fusion VO - Legal Entities are Being Dropped

This issue is applicable only for Fusion Application as a Source.

There is an issue with the Fusion VO wherein the legal entities are being dropped and hence are not being loaded in the warehouse.

Workaround

Legal Entity dimension is not completely supported for now. Only those Legal Entities are loaded in the warehouse, which have a primary ledger defined. Legal Entity Dimension attributes in reports might not result in any data. Legal Entity Dimension will be supported in the upcoming future patches.

SDE_FUSION_HRASSIGNMENTDIMENSION Failure

This issue applies to Fusion Workforce adaptor.

Fusion direct full load ETL runs in to an error with SDE_FUSION_HRASSIGNMENTDIMENSION failure.

Workaround

Note:

Ensure that you perform the following steps before generating a new load plan.
  1. Log in to ODI Studio and navigate to the Designer tab.
  2. Navigate to the SDE_FUSION_HRAssignmentDimension.W_HR_ASSIGNMENT_DS_SQ_ASGDFF interface.
  3. Set the column ASGDFF_SRC_LAST_UPDATE_DATE in SDE_FUSION_HRAssignmentDimension.W_HR_ASSIGNMENT_DS_SQ_ASGDFF interface as RUN_REPLICATED_TRANSACTIONAL("#IS_SDS_DEPLOYED", ASGDFF.LastUpdateDate,      ASGDFF.CDC$_SRC_LAST_UPDATE_DATE ).
  4. Regenerate the scenario.

SDE_FUSION_HRPersonLegislation_VisaPermit Fails

This issue applies to the Fusion adaptor (Workforce).

Fusion direct full load ETL runs in to an error when SDE_FUSION_HRPersonLegislation_VisaPermit fails.

Workaround

Note:

Ensure that you perform these steps before generating a new load plan.
  1. Log in to ODI Studio, navigate to the Designer tab, then BI Apps Project, then Mappings, and the SDE_FUSION_10_Adaptor folder.
  2. Locate the SDE_FUSION_HRPersonLegislation_VisaPermit task and open the SDE_FUSION_HRPersonLegislation_VisaPermit.W_FSN_PER_VISAS_PERMITS_F_TMP_SQ_VISA interface.
  3. In the Quick-Edit tab, find the filter beginning RUN_REPLICATED_TRANSACTIONAL...and remove VISAPERMITPVO alias and AS keyword. It should look as follows: 
    RUN_REPLICATED_TRANSACTIONAL('#IS_SDS_DEPLOYED', 
VisaPermitId  in ( Select_physical MAX(CHK.VisaPermitId) from 
"oracle.apps.hcm.model.analytics.applicationModule.HcmTopModelAnalyticsGlobalAM_HcmTopModelGlobalAMLocal"..."HcmTopModelAnalyticsGlobalAM.PersonAM.VisaPermitPVO" CHK
Where CHK.VisaPermitPEOPersonId=VisaPermitPEOPersonId), 
VISAPERMITPVO.VisaPermitId=(Select MAX(CHK.VisaPermitId) from 
QUALIFY(HCM_PE_VISAPERMITPVO764912) CHK 
Where CHK.VisaPermitPEOPersonId=VISAPERMITPVO.VisaPermitPEOPersonId))
  4. Save the interface and regenerate the scenario.

SDE_FUSION_StatusDimension_EnrollmentStatus Failure

This issue applies to the Fusion Adaptor Direct.

Fusion direct full load ETL runs in to an error due to SDE_FUSION_StatusDimension_EnrollmentStatus failure.

Workaround

Note:

Ensure that you perform these steps before generating a new load plan.
  1. Log in to the ODI Studio and navigate to the Designer tab.
  2. Navigate to the SDE_FUSION_9_Adaptor adaptor folder and then to the    SDE_FUSION_StatusDimension_EnrollmentStatus.W_STATUS_DS interface.
  3. Change the Execute On property for column DELETE_FLG from Staging to Source.
  4. Regenerate the scenario.

SDE_PSFT_DomainGeneral_PayrollPeriodType Fails

This issue applies to the PeopleSoft Payroll Adaptor.

SDE_PSFT_DomainGeneral_PayrollPeriodType fails with the following error: 
ORA-00904: "SQ_FREQ_TBL"."LANGUAGE_CODE": invalid identifier
.

Workaround

Note:

Ensure that you perform these steps before generating a new load plan.
  1. Log in to ODI Studio and navigate to the Designer tab.
  2. Navigate to the SDE_PSFT_DomainGeneral_PayrollPeriodType.W_DOMAIN_MEMBER_GS interface.
  3. Change the current mapping for SRC_LANGUAGE_CODE from DOMAIN_MEMBER_MAP('LANGUAGE',SQ_FREQ_TBL.LANGUAGE_CODE, #DATASOURCE_NUM_ID,'W_LANGUAGE') to DOMAIN_MEMBER_MAP('LANGUAGE',SQ_FREQ_TBL.LANGUAGE_CODE,#DATASOURCE_NUM_ID,'W_LANGUAGE')
  4. Regenerate the scenario.

SDE_Universal_PayrollBalanceDimension_Translate Fails

This issue applies to the Universal adaptor.

SDE_Universal_PayrollBalanceDimension_Translate fails as the balance name column has more than 80 characters.

Workaround

Ensure that the balance names in the source data are 80 characters or less.

Mapping SDE_FUSION_PARTYPERSONDIMENSION Fails on the Health Diagnostics Step

This issue applies to mapping failures on the health diagnostics step.

Mapping the SDE_FUSION_PARTYPERSONDIMENSION fails on the health diagnostics step with Value too large issue. The mapping expression of the PRIMARY_PHONE_AREA_CODE column uses the DOMAIN_DEFAULT_UNASSIGNED user defined function (UDF). In cases where there is no data from the source system, __UNASIGNED__ gets evaluated as a result of this UDF which is of length 14. However, the data warehouse column is of length 10, hence mapping fails with the Value too large issue.

Workaround

  1. In ODI client, navigate to the Designer and open BI Apps Project, then Mappings, and select SDE_FUSION_x_Adaptor (where x can be 9,10, or 11).
  2. Expand SDE_FUSION_PartyPersonDimension.
  3. Open MAIN interface (blue) SDE_FUSION_PartyPersonDimension.W_PARTY_PER_DS, go to column PRIMARY_PHONE_AREA_CODE, and replace the existing expression with the following: 
    . 
IIF( 
  SQ_W_PARTY_PER_DS.CON_PHN_CONTACT_FLG = 'Y', 
  SQ_W_PARTY_PER_DS.CON_PHONE_AREA_CODE, 
  SQ_W_PARTY_PER_DS.PRIMARY_PHONE_AREA_CODE 
) 
.
  4. Save the interface and regenerate the scenario.

Email Address Missing While Extracting Data from GlobalPerson

This issue applies to the missing privileges for the provisioned extractor user in the Fusion Applications release 11 POD environments.

While extracting data from the GlobalPersonForETLPVO Fusion view object (VO), using the FUSION_APPS_OBIA_BIEE_APPID user , some of the persons' email addresses are missing. This issue is due to the missing privileges for this user in the Fusion Applications release 11 POD environments. This user is the provisioned extractor user that the Oracle BI Applications ETL process uses to extract data from Fusion Applications. In order to extract secured data such as PII information (for example, person's home email, home phone, and national identifiers) , you must ensure that this user is granted the data security policies that allow it to read the underlying tables.

Workaround

  1. Log in to the Fusion Applications, with a user that has the IT Security Manager role.
  2. Navigate to Setup and Maintenance, search and launch the Manage Duties task.
    This opens the Oracle Entitlements Server tab, commonly known as the Authorization Policy Manager (APM).
  3. Use the search box to search for the FUSION_APPS_OBIA_BIEE_APPID user.

    Note:

    There is also a role called FUSION_APPS_OBIA_BIEE_APPID. Do not search for and create policies for the role. You must ensure to search for and create policies for the FUSION_APPS_OBIA_BIEE_APPID user.
    The search result displays the Business Intelligence Applications Extract Transform and Load Application Identity user name.
  4. Select this user name in the search result area and click on the Open (folder) icon.
    This opens a new tab displaying details such as the role assignments of this user.
  5. Click on the Find Global Policies to open the Search Policies tab.
    In the Data Security Policies for: region within the Search Policies tab are listed all of the currently granted data security policies to this user.
  6. Click New to open the New Policy tab with four sub-tabs and enter the following information in each sub-tab:
    • General Information tab:

      • Name: Custom Grant for Person Email to OBIA

      • DB Resource: Person Email

      • Module: Global Human Resources

      • Start Date: 1/1/01

    • Roles tab: There is no information to enter on this tab. It must, however, display a single entry for FUSION_APPS_OBIA_BIEE_APPID user name.

    • Rule tab:

      • Row Set: All Values

    • Action tab: Select Report Person Email and shuttle to the selected actions list.

    • Click Save on the New Policy tab. This saves and returns you to the Search Policies tab with a confirmation message. Press OK to confirm.

  7. Repeat Step 6 for the following DB Resource and Actions:

    • DB Resource: Person Phone; Action: Report Person Phone

    • DB Resource: Human Resources Address; Action: Report Person Address

  8. On the Search Policies tab, sort Data Security Policies by Action (descending), scroll, and verify that the policies for Report Person Email , Report Person Phone, and Report Person Address have been created successfully.
  9. If the BI reports additionally need to extract other secured details such as National Identifiers, Visa , then create data security policies for the following:

    • DB Resource: Person National Identifier; Action: Report Person National Identifier

    • DB Resource: Person Visa Action; Action: Report Person Visa

    • DB Resource: Person Passport; Action: Report Person Passport

    • DB Resource: Person Driver License; Action: Report Driver License

    Note:

    If you are uncertain whether the Oracle Business Intelligence ETL process needs to extract data from these objects, then do not create the data security policies for that object. Only do so, when you are absolutely certain.

Some Fixed Asset Fact Folders Not Opening in BI Answers for E-Business Suite and PeopleSoft

Fact folders in some Fixed Asset subject areas in the Oracle Business Intelligence Answers page are not opening. This issue may occur in E-Business Suite or PeopleSoft security-enabled environments when logged into Oracle BI Answers using a secured user.

  1. Use the Oracle BI Administration Tool to open the RPD file, then select Manage, then Variables.
  2. Double-click the session variable ASSET_BOOK_CODE___FUSION to open the session variable edit window.
  3. Change the Default Initalizer value from -1 to '-1', which changes it from integer type to string type.
  4. Click OK to save the changes.

    Make sure these three variables have a Default Initializer value of ‘-1’.

    • ASSET_BOOK_CODE____EBS

    • ASSET_BOOK_CODE____FUSION

    • ASSET_BOOK_CODE____PSFT

  5. Select Manage, then Variables.
  6. In the Initialization Blocks, make sure you ONLY enable the initialization block based on your setup, for example, enable "Fixed Asset Book EBS" if you are implementing E-Business Suite as the source (or Fixed Asset Book PSFT), then disable the other two.
  7. Save the RPD and restart the BI server.