General Issues and Workarounds

Review these general issues and workarounds for Oracle BI Applications.

RCU Fails to Import BIACOMP Data for Version 11.1.1.9.1

This issue applies to users who are on Oracle BI Applications version 11.1.1.9.1.

The RCU import of the obia_comp.dmp (BIACOMP schema) file fails to import successfully due to a lack of database privileges assigned to the BIACOMP user. The database import log contains errors similar to these:

ORA-39083: Object type TABLE:"BIA_BIACOMP"."FND_PROFILE_LEVELS" failed to create with error:
ORA-01950: no privileges on tablespace 'BIA_BIACOMP'
ORA-39112: Dependent object type INDEX:"BIA_BIACOMP"."FND_PROFILE_LEVELS_U3" skipped, base object type TABLE:"BIA_BIACOMP"."FND_PROFILE_LEVELS" creation failed

Workaround

  1. Open the file RCU_HOME/rcu/integration/biapps/sql/biapps_create_user.sql.

  2. Replace line CREATE USER &&1 identified by &&2 default tablespace &&3 temporary tablespace &&4; with CREATE USER &&1 identified by &&2 default tablespace &&3 temporary tablespace &&4 quota unlimited on &&3;

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

ETL Fails If lead_name Length Exceeds 100 Characters

This issue applies to ETL failure.

If any lead is created with longer than a 100-character lead name, then the ETL fails.

Workaround

  1. Connect to ODI Client.

  2. Navigate to BI Apps Project, Mappings > SDE_FUSION_V1_Adaptor > SDE_FUSION_MarketingLeadFact.

  3. Open SDE_FUSION_MarketingLeadFact.W_MKTG_LEAD_FS_SQ_JOINER.

    Select Interfaces, then Mapping tab.

  4. From Target Datastore - SQ_JOINER on right side window, highlight LeadName.

  5. From Property Inspector on bottom window, change Length to 250 under Target Column Properties.

    Description of the illustration prop_insp_leadname_length.gif follows
    Description of the illustration prop_insp_leadname_length.gif

  6. Save the change.

  7. Next, open SDE_FUSION_MarketingLeadFact.W_MKTG_LEAD_FS.

    Select Interfaces, then Mapping tab.

  8. From Target Datastore - W_MKTG_LEAD_FS on right side window, highlight LEAD_NAME.

  9. From Property Inspector on bottom window, change the Implementation value under Mapping Properties.

    The old value is SQ_LEADPVO.LeadName. Change to SUBSTR(SQ_LEADPVO.LeadName, 1, 100).

    Description of the illustration prop_insp_leadname_impl.gif follows
    Description of the illustration prop_insp_leadname_impl.gif

  10. Save the changes.

  11. Navigate to SDE_FUSION_MarketingLeadFact, Packages, SDE_FUSION_MarketingLeadFact, Scenarios, SDE_FUSION_V1_ADAPTOR_SDE_FUSION_MARKETINGLEADFACT Version 001, then right-click Regenerate.

  12. Click OK.

    Description of the illustration regen_scenario_dialog.gif follows
    Description of the illustration regen_scenario_dialog.gif

  13. In the Scenario Variables dialog, select the check marks for the BIAPPS.ETL Startup Parameters.

    Description of the illustration scenario_variables_db.gif follows
    Description of the illustration scenario_variables_db.gif

  14. Click OK.

Data Store Corruption Issue for On-premises Customers Extracting From Fusion Application Sources

There is a known data store ID corruption issue for on-premises customers who are on Release 8.1 and have run BI Extender, then apply a Patch Bundle.

This issue is happening because the patch bundle also has out of the box data stores and mappings applicable to Cloud, which are getting corrupted because of the data store ID mismatch. Additionally, there is another issue where the Patch Bundle is overwriting the LP Component containing the BI Extender related Mappings.

So, the workaround is to disable all the Cloud data source mappings/scenarios in the Load Plan component and then add/enable the BI Extender related steps that were removed/overwritten by the Patch bundle into the Load Plan component, respectively, before generating the load plan.

You need to perform the workaround if you see a similar error after applying the patch bundle:

ODI-ERROR: Failed while extracting source tables from interface:
SDE_FUSION_BalancingSegmentDimension_Tree

Workaround

  1. Open Load Plan FUSION_1_0 under BIAPPS Load Plan, Load Plan Dev Components, then SDE.

  2. Open LP Component step 3 SDE Dims COSTCTR_DIM FUSION_1_0 and disable 3 child steps under the parallel step SDE_FUSION_COSTCENTERDIMENSION, SDE_FUSION_COSTCENTERDIMENSION_TREE and SDE_FUSION_COSTCENTERDIMENSIONHIERARCHY, and then save the Load Plan Component.

  3. Check to see if parallel step(s) SDE_Dim_Cost_Center_W_COST_CENTER_D and SDE_Dim_Cost_Center_W_COST_CENTER_DH relating to BI Extender are available in LP Component step 3 SDE Dims COSTCTR_DIM FUSION_1_0.

    If not add and enable the same.

    Description of the illustration prop_insp_parallel_steps.gif follows
    Description of the illustration prop_insp_parallel_steps.gif

  4. Open LP Component step 3 SDE Dims BALSEG_DIM FUSION_1_0 and disable 3 child steps under the parallel step SDE_FUSION_BALANCINGSEGMENTDIMENSION, SDE_FUSION_BALANCINGSEGMENTDIMENSION_TREE and SDE_FUSION_BALANCINGSEGMENTDIMENSIONHIERARCHY and save Load Plan Component.

  5. Check to see if parallel steps SDE_Dim_Balancing_Segment_W_BALANCING_SEGMENT_D and SDE_Dim_Balancing_Segment_W_BALANCING_SEGMENT_DH relating to BI Extender are available in Load Plan Component step 3 SDE Dims BALSEG_DIM FUSION_1_0.

    If not add and enable them. See the previous figure.

  6. Open LP Component step 3 SDE Dims NAT_ACCT_DIM FUSION_1_0 and disable 3 child steps under the parallel step SDE_FUSION_NATURALACCOUNTDIMENSION, SDE_FUSION_NATURALACCOUNTDIMENSION_TREE and SDE_FUSION_NATURALACCOUNTDIMENSIONHIERARCHY and then save the Load Plan Component.

  7. Check to see if parallel step(s) SDE_Dim_Natural_Account_Segment_W_NATURAL_ACCOUNT_D and SDE_Dim_Natural_Account_Segment_W_NATURAL_ACCOUNT_DH relating to BI Extender are available in Load Plan Component step 3 SDE Dims NAT_ACCT_DIM FUSION_1_0.

    If not add and enable them. See the previous figure.

  8. Open LP Component step 3 SDE Dims GLSEG_DIM FUSION_1_0 and enable the parallel step (which starts with SDE_FUSION…) and disable all the child steps under the parallel step and save the Load Plan Component.

    Description of the illustration load_plan_before.gif follows
    Description of the illustration load_plan_before.gif
    Description of the illustration load_plan_after.gif follows
    Description of the illustration load_plan_after.gif

  9. Finally, add all the GL Segment related BI Extender mapping steps into 3 SDE Dims GLSEG_DIM FUSION_1_0, similar to what is being done in steps 3, 5, and 7.

    Note:

    Number of segments to Add depends on how many non-qualified segments you have enabled and extended in Fusion Financials.

    For example, if you have enabled GL Segment1 and GL Segment2, then you will need to add 4 new steps (2 for dimensions and 2 for Hierarchy) as shown below:

    SDE_Dim_GL_Segment1_W_GL_SEGMENT_D
SDE_Dim_GL_Segment1_W_GL_SEGMENT_DH
SDE_Dim_GL_Segment2_W_GL_SEGMENT_D
SDE_Dim_GL_Segment2_W_GL_SEGMENT_DH

  10. After applying the above changes to the Load Plan, regenerate the Load Plan before executing the same.

NLS Regional Settings Not Save After Logout/Login

This issue applies to customers who want to use Oracle BI Applications Configuration Manager and 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.

Ignore Non-Fusion Applications FSM Tasks

In FSM, the Configure Enterprise List task might be erroneously displayed in non-Fusion Applications Implementation Projects, and should be ignored in that context.

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 Oracle BI Applications 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.

Insufficient Memory During Load Plan Execution

This issue applies to load plan execution.

If you are generate a Load Plan with many fact groups and the Load Plan generation 'hangs' while reporting the status 'In Progress', then increase the memory.

Workaround

  1. Restart BI Server and try to generate the Load Plan again.

    If you get the same issue, performs Steps 2 and 3 below.

  2. Increase BI Server memory settings to 3GB(-Xmx3072m) in <MWHome>/user_projects/domains/bifoundation_domain/bin/setBIAppsDomainEnv.sh.
  3. Restart BI Server from Administration Console.

    For example:

    if [ "${SVR_GRP}" = "obi" ] ; then
   #  BIA Managed Server : set max heap to 2GB
  if [ "${JAVA_VENDOR}" = "Sun" ] ; then
  SERVER_MEM_ARGS="-Xms512m -Xmx3072m -XX:MaxPermSize=1024m"
  export SERVER_MEM_ARGS
  fi
  if [ "${JAVA_VENDOR}" = "Oracle" ] ; then
  SERVER_MEM_ARGS="-Xms512m -Xmx3072m -XX:MaxPermSize=1024m"
  export SERVER_MEM_ARGS
  fi
  4. Regenerate and re-execute the Load Plan.

Deploying Multiple Instances of the Same Source

Applies to Business Intelligence Applications Consumer, version 11.1.1.7.1 and later.

If you are implementing multiple instances of the same source, then you can find additional information about how to do this in 'Multi-Instance Source Support in BI Applications 11.1.1.x.1' (Support Note Doc ID 1633996.1) on My Oracle Support.

SILOS_SIL_NEGOTIATIONLINEFACT - Error Creating Indexes With Duplicate Keys

This issue applies to Procurement and Spend Analytics with Fusion Applications OLTP Source V11.1.9.2.

Issue: SILOS_SIL_NEGOTIATIONLINEFACT-Error creating indexes with duplicate keys found.

Workaround

  1. In ODI Studio, navigate to the SDE adaptor and open the folder SDE_FUSION_NegLineFact, and open the main interface SDE_FUSION_NegLineFact.W_NEG_LINES_FS.
  2. Navigate to Quick Edit, expand the Filters section, and add a new filter as the follows: 
    TO_CHAR(COALESCE(SQ_NEG_LINE.LineNumber,0)) <> '0'

    Mark the filter executed on Staging.

  3. Save the interface and regenerate the scenario.

    Note:

    If the EAT has already been executed, run the following command to remove the duplicate records:
    delete from w_neg_lines_f where neg_line_num = 0 and created_on_dt is null

Non-supported Attributes and Metrics

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

E-Business Suite:

SDE_ORA11510_Adaptor
SDE_ORAR12_Adaptor
SDE_ORAR1211_Adaptor
SDE_ORAR1212_Adaptor
SDE_ORAR1213_Adaptor
SDE_ORAR122_Adaptor

JDE:

SDE_JDEE1_90_Adaptor
SDE_JDEE1_91_Adaptor

Peoplesoft:

SDE_PSFT_90_Adaptor
SDE_PSFT_91_Adaptor
SDE_PSFT_92_Adaptor

Consequently, history on underlying DW tables is not available on these ETL Adapters:

W_CST_ONHAND_DAILY_F
W_CST_ONHAND_ACC_DAILY_F
W_CST_INTRANSIT_DAILY_F
W_CST_INTRAN_ACC_DAILY_F

Consequently, the following Presentation Metrics are not supported on above listed ETL Adapters:

"Costing - Inventory Valuation".."Fact - Costing - Inventory Valuation"."Accounted Onhand Quantity Year Ago"
"Costing - Inventory Valuation".."Fact - Costing - Inventory Valuation"."Accounted Onhand Amount Year Ago"
"Costing - Inventory Valuation".."Fact - Costing - Inventory Valuation"."Costed Onhand Quantity Year Ago"
"Costing - Inventory Valuation".."Fact - Costing - Inventory Valuation"."Costed Onhand Amount Year Ago"
"Costing - Inventory Valuation".."Fact - Costing - Inventory Valuation"."Accounted In transit Quantity Year Ago"
"Costing - Inventory Valuation".."Fact - Costing - Inventory Valuation"."Accounted In transit Amount Year Ago"
"Costing - Inventory Valuation".."Fact - Costing - Inventory Valuation"."Costed In transit Quantity Year Ago"
"Costing - Inventory Valuation".."Fact - Costing - Inventory Valuation"."Costed In transit Amount Year Ago"
"Costing - Inventory Valuation".."Fact - Costing - Inventory Valuation"."Total Inventory Value Costed Year Ago"
"Costing - Inventory Valuation".."Fact - Costing - Inventory Valuation"."Total Inventory Value Accounted Year Ago"
"Costing - Inventory Valuation".."Fact - Costing - Inventory Valuation"."Chg % Total Value Accounted Year Ago"
"Costing - Inventory Valuation".."Fact - Costing - Inventory Valuation"."Chg % Total Value Costed Year Ago"
"Costing - Inventory Valuation".."Fact - Costing - Inventory Valuation"."Chg Total Inventory Accounted Value Month Ago"
"Costing - Inventory Valuation".."Fact - Costing - Inventory Valuation"."Chg Total Inventory Costed Value Month Ago"
"Costing - Inventory Valuation".."Fact - Costing - Inventory Valuation"."Total Inventory Quantity Costed Year Ago"
"Costing - Inventory Valuation".."Fact - Costing - Inventory Valuation"."Total Inventory Quantity Accounted Year Ago"
"Costing - Inventory Valuation".."Fact - Costing - Inventory Valuation"."Chg % Total Quantity Costed Year Ago"
"Costing - Inventory Valuation".."Fact - Costing - Inventory Valuation"."Chg % Total Quantity Accounted Year Ago"

On Peoplesoft data source 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 data sources, 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 data sources, the following Presentation Subject Areas are not supported:

Inventory - Cycle Count
Inventory - Bill of Materials

On Peoplesoft data sources there is changed behavior on the following Attribute:

"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 data sources, the following Logical Tables and corresponding Dimensions are not supported:

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

In addition, note the following:

  • On Peoplesoft data sources, CST_COST_ELEMENT and CST_COST_ELEMENT_TYPES source domains have an additional grain of SETID. Hence, these are to be setup in CM with a pattern of COST_ELEMENT~SETID

  • Notes on behavior of specific Attributes/Metrics on Peoplesoft data sources:

    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 that is marked with a Business Unit Type of "PLNT". Only "PLNT" type Business Units are represented in the Plant Location Dimension.

Cloud Data Source System: W_PURCH_VARIANCE_XACT_F Is Not Loaded

This issue applies to the Cloud data source system.

Purchase Price Variance for Cloud Data Source systems are not supported. Therefore, W_PURCH_VARIANCE_XACT_F is not loaded in Cloud Data source systems for any Purchase Price Variance related data.

Note:

Purchase Price Variance is available for the other adapters including E-Business Suite, People Soft FT, and Fusion.

Integrating Financial Analytics with Oracle Essbase for E-Business Suite

For information about integrating Financial Analytics with Oracle Essbass, refer to document Integrating OBIA Financial Analytics with Oracle Essbase for Oracle EBS (Doc ID 1954381.1.) on My Oracle Support (support.oracle.com).

Configuring UCM Download for Cloud Data Source Systems

Use these instructions to configure UCM download for Cloud data source systems.

Before you start the UCM download (as described in the Task Register file system location for download and staging the files from FA UCM server in ODI studio, see Register the Fusion Applications Source in Oracle Business Intelligence Applications Installation Guide), ensure that you complete the steps mentioned in the Workaround.

Workaround

  1. Edit the BIAPPSConfig.properties file in the following location:
    <middleware_home>/user_projects/domains/bifoundation_domain/config/fmwconfig/biinstances/coreapplication/
  2. Add the configuration tag EXTERNAL_STORAGE_TYPE=UCM.

Soft Deletes Not Working With PeopleSoft PSFT91

This issue applies to PeopleSoft PSFT91.

The Soft Delete feature is not working for Amt Based Revenue, for example, expenditure types of BILL%, CNTR%.

Workaround

There is no workaround for this issue.

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.

ODI Map Trimming out Trailing Space from Column Data

When moving data from flat file (delimited type) to a table in Oracle Business Analytics Warehouse using Oracle Data Integrator, if the map run trims out the trailing space from the column data, then apply the workaround.

Workaround

  1. Download ODI patch file 18408469 from the Oracle Automated Release Updates (ARU) website.
  2. Close all opened ODI Clients and any running ODI Agents.
  3. To preserve trailing spaces in column data, after the successful patch application, connect to the ODI repository using ODI Client and change the File Data server JDBC URL to the URL below: 
    jdbc:snps:dbfile?ENCODING=UTF-8&NO_RTRIM_DEL_STRING=TRUE
  4. Save the changes and re-run the ODI task to verify the fix.

Task 'SDE_PSFT_90_ADAPTOR_SDE_PSFT_PERSISTEDSTAGE_TALENTMGMT_PROFILEITEMS' Fails During ETL

During ETL, the task 'SDE_PSFT_90_ADAPTOR_SDE_PSFT_PERSISTEDSTAGE_TALENTMGMT_PROFILEITEMS' fails with these errors.

ODI-1217: Session SDE_PSFT_90_ADAPTOR_SDE_PSFT_PERSISTEDSTAGE_TALENTMGMT_PROFILEITEMS (587500) fails with return code 904.
ODI-1226: Step SDE_PSFT_PersistedStage_TalentMgmt_ProfileItems.W_PSFT_PRFL_ITEM_PS fails after 1 attempt(s).
ODI-1240: Flow SDE_PSFT_PersistedStage_TalentMgmt_ProfileItems.W_PSFT_PRFL_ITEM_PS fails while performing a Loading operation. This flow loads target table W_PSFT_PRFL_ITEM_PS.
ODI-1227: Task SrcSet0 (Loading) fails on the source ORACLE connection HCM_SOURCE. 
Caused By: java.sql.SQLSyntaxErrorException: ORA-00904: "W_PSFT_PRFL_ITEM_PS_TMP"."MINOR_DESCR": invalid identifier

Workaround

  1. In ODI Studio, expand the SDE_PSFT_90_Adaptor folder.
  2. Open the interface SDE_PSFT_PersistedStage_TalentMgmt_ProfileItems.W_PSFT_PRFL_ITEM_PS_SQ_PS_JPM_JP_ITEMS.
  3. Using the Quick Edit tab, map the MAJOR_DESCR and MINOR_DESCR columns as follows: 
    MAJOR_DESCR = JPM_JP_ITEMS.MAJOR_DESCR
MINOR_DESCR = JPM_JP_ITEMS.MINOR_DESCR
  4. Open the interface SDE_PSFT_PersistedStage_TalentMgmt_ProfileItems.W_PSFT_PRFL_ITEM_PS.
  5. Using the Quick Edit tab, map the MAJOR_DESCR and MINOR_DESCR columns as follows: 
    MAJOR_DESCR = W_PSFT_PRFL_ITEM_PS_TMP.MAJOR_DESCR
MINOR_DESCR = W_PSFT_PRFL_ITEM_PS_TMP.MINOR_DESCR
  6. Save both interfaces and regenerate the scenario.

Deployment of the ODI Repository on Non-Oracle Databases is Not Supported

This issue applies to deployment of the ODI repository on non-Oracle databases.

This release does not support the deployment of the ODI Repository on non-Oracle source databases as documented in Chapter 6 'Deploying the ODI Repository for Non-Oracle Source Databases' of Oracle Business Intelligence Applications Installation Guide. You should ignore Chapter 6 'Deploying the ODI Repository for Non-Oracle Source Databases'.

Workaround

There is no workaround for this issue.

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.

Invalid Filter in SDE_ORA_EmployeeExpenseCreditCardFact_Primary mapping for E-Business Suite Source Systems

This issue applies to E-Business Suite source systems.

The SDE_ORA_EmployeeExpenseCreditCardFact_Primary mapping contains an invalid filter for incremental loads. Primary mappings should extract all records without filtering any records, even in incremental load. If you enable the Soft Delete feature by setting SOFT_DELETE_FEATURE_ENABLED=Y and SOFT_DELETE_PREPROCESS=Y, then it will mark DELETE_FLG='Y' for all records that were loaded in the previous load, but not updated in the current load. If the Soft Delete feature is not enabled, then there is no impact on data.

Workaround

  1. In ODI Studio, navigate to the Designer tab.

  2. Expand your SDE ORA source adaptor, navigate to the SDE_ORA(your EBS version number)_Adaptor > SDE_ORA_EmployeeExpenseCreditCardFact_Primary > Interfaces folder, and locate the following interface:

    SDE_ORA_EmployeeExpenseCreditCardFact_Primary.W_EXPENSE_CC_F_PE_SQ

  3. Open the interface, display the Quick Edit tab, and expand the Mappings section.

  4. Replace the filter starting with RUN_FULL_INCREMENTAL with the following expression:

    AP_CREDIT_CARD_TRXNS_ALL.CREATION_DATE >TO_DATE_VAR('#INITIAL_EXTRACT_DATE')

  5. Save the interface and close it.

  6. To make the changes that you made take effect, locate the scenario and regenerate the scenario.

    For example, if your source adaptor is EBSR1213, then the scenario is SDE_ORAR1213_ADAPTOR_SDE_ORA_EMPLOYEEEXPENSECREDITCARDFACT_PRIMARY.

    The scenario should be located at:

    SDE_ORA(your E-Business Suite version number)_Adaptor
- SDE_ORA_EmployeeExpenseCreditCardFact_Primary
- Packages
- SDE_ORA_EmployeeExpenseCreditCardFact_Primary
- Scenarios

Existing data fix

If Oracle Business Analytics Warehouse has already been built before applying the fix above, then you must correct the existing data in Oracle Business Analytics Warehouse.

  1. Run the following UPDATE SQL in Oracle Business Analytics Warehouse first, and then run your next incremental load as usual. This will populate only the W_EXPENSE_CC_F and W_EXPENSE_CC_F_PE tables in full load mode, while keeping other tables' records.

    update W_ETL_LOAD_DATES set committed=0
where TARGET_TABLE_NAME IN ('W_EXPENSE_CC_FS',
'W_EXPENSE_CC_F','W_EXPENSE_CC_F_PE'); COMMIT;

Enable Data Security for Peoplesoft Data Source

This issue applies to Peoplesoft data source.

The Presentation Table 'Product' in the 'Inventory – Aging' Subject Area is not visible, even though the User is provisioned with the required Application Duty Roles. In addition, the Supply Chain Management and Fusion Costing Catalog is not visible to a User having been granted Peoplesoft specific Duty Roles.

To correct these two issues, apply the workaround.

Workaround

Pre-requisites:

  • You have must have installed or upgraded to Oracle BI Applications Release 11.1.1.9.1.

  • You must have access to the installed default RPD through Oracle BI EE Administration Tool.

  • You must have Administrator's privilege over the BI Presentation Services Catalog (also known as webcat).

  1. In Oracle BI EE Administration Tool, edit the BI metadata repository (RPD), and expand Presentation Subject Area Inventory – Aging..

  2. Right-click on the Presentation Table Productand select Properties...

  3. Click Permissions…

  4. Select the Read' radio button against Authenticated User.

  5. Click OK, then click Save and exit.

  6. Deploy the RPD if in offline mode or restart the BI Server if in online mode.

  7. Login to the Analytics page on your browser with a user having Administrator privileges, (for example, at: <Server>:9704/analytics).

  8. Click the Catalog link on the top menu bar.

  9. Make sure that User View is selected on the view drop down, and click the Shared Folders link on the left Navigation Panel.

  10. Scroll down on the right panel to the Supply Chain Management section, then click the More link, then click Permissions.

  11. Add the following Application Duty Roles with the Permissions listed below:

    Accounts Permissions
    BI Administrator Full Control
    Inventory Analyst PSFT Open
    Inventory Manager PSFT Open

    Click OK.

  12. On the Catalog page, change the view to Admin Viewand repeat steps 9 to 11.

  13. Log out.

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 BI Metadata Repository (RPD) 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 EE Administration Tool, edit the BI Metadata Repository (that is, the RPD file).
  2. Navigate to Physical > Oracle Data Warehouse > 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.

Using response file /slot/ems6472/appmgr/R92_DWH/upgrade_atg.rsp for input 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>'

Missing Soft Delete on Workforce Object

This issue applies to soft delete and the resultant duplicate index failure.

When a Soft Delete (setting DELETE_FLG = 'Y') occurs on the Workforce object(s) leverage by Time and Labor ETL, a duplicate index failure can occur because of a missing filter for the applicable cases.

Workaround

Add an additional join predicate to filter out DELETE_FLG <> 'Y'.

ODI fix summary: 
 SDE_PSFT_TimeReported_Fact.W_TLB_RPTD_FS_SQ_W_PSFT_TLB_RPTD_TIME_PS 

 Join: 
 RPTD.EMPLID=PSFT_WEVT_PS.EMPLID AND 
 RPTD.EMPL_RCD=PSFT_WEVT_PS.EMPL_RCD AND 
 RPTD.DUR >= PSFT_WEVT_PS.EFFECTIVE_FROM_DT AND 
 RPTD.DUR < PSFT_WEVT_PS.EFFECTIVE_TO_DT AND 
 PSFT_WEVT_PS.MAX_EFFSEQ_IND=1 AND 
 PSFT_WEVT_PS.DELETE_FLG <> 'Y' AND 
 RPTD.DATASOURCE_NUM_ID=PSFT_WEVT_PS.DATASOURCE_NUM_ID 

 SDE_PSFT_TimeProcessed_Fact.W_TLB_PRCSD_FS_SQ_W_PSFT_TL_PAYABLE_TIME_PS 
 Join: 
 PAYABLE.EMPLID=PS_JOB.EMPLID AND 
 PAYABLE.EMPL_RCD=PS_JOB.EMPL_RCD AND 
 PAYABLE.DUR >= PS_JOB.EFFECTIVE_FROM_DT AND 
 PAYABLE.DUR < PS_JOB.EFFECTIVE_TO_DT AND 
 PS_JOB.MAX_EFFSEQ_IND = 1 AND 
 PS_JOB.DELETE_FLG <> 'Y' AND 
 PAYABLE.DATASOURCE_NUM_ID = PS_JOB.DATASOURCE_NUM_ID

Duplicate Record for Balancing Segments when Parent and Child are the Same in PSTREELEAF

This issue applies to duplicates in the extract.

If a PSFT tree for Fund Code (Balancing Segment) is a summer tree and if the detail table and the corresponding node table are different but the Node ID has the same value as one of the detail nodes, then the current extract causes duplicates.

Workaround

  1. In ODI Studio, navigate to BI Apps Project.
  2. Under the Mappings folder, create a new patch SDE adaptor folder based on the appropriate PSFT release version in your environment, as follows:
    1. Right-click the Mappings folder and select New Sub-Folder.
    2. Specify the Name as PATCH_<Original Folder Name>.

      For example, if this is for PSFT 9.0, then create patch folder PATCH_SDE_PSFT_90_Adaptor. This represents the patch SDE folder for the original SDE_PSFT_90_Adaptor folder.

    3. In the Designer tab, click the Connect Navigator button.
    4. Select Edit Release Tags.
    5. Select the release tag that corresponds to your source.

      For example, PSFT_9_0.

    6. Select the patch SDE folder you created and add it to the release tag.
    7. Click Next. then click Finish.
  3. Enable versioning for the preconfigured Task Folder, SDE_PSFT_Stage_BalancingSegmentDimensionHierarchy and SDE_PSFT_BalancingSegmentDimensionHierarchy to be modified.

    The version comment should indicate that this is the base version of the task. Subsequent patches applied to this task in the future require increasing the version in the comment so that it can be compared to the original task to identify any changes.

    1. Navigate to the appropriate PSFT SDE folder (for example, SDE_PSFT_90_Adaptor), right-click the task folder SDE_PSFT_Stage_BalancingSegmentDimensionHierarchy and select Version > Create Version.
    2. Accept the default version number, 1.0.0.0.
    3. Add a description indicating that this is the original version of this task.
    4. Repeat steps a to c for SDE_PSFT_BalancingSegmentDimensionHierarchy.
  4. Duplicate the Task folder to be modified (SDE_PSFT_Stage_BalancingSegmentDimensionHierarchy and SDE_PSFT_BalancingSegmentDimensionHierarchy) by copying it.

    Cut and paste the copied task folder to the patch adaptor folder that you have created (example, PATCH_SDE_PSFT_90_Adaptor), and rename it to remove the 'Copy of…' prefix.

  5. Using the same method as in step 3, enable versioning of copied Task folders.

    The version comment should indicate this is the original version. This versioning enables comparison of the modified task to a copy of the original version to determine all changes that have been introduced.

  6. Create another version of the copied task SDE_PSFT_Stage_BalancingSegmentDimensionHierarchy and SDE_PSFT_BalancingSegmentDimensionHierarchy.

    The version comment should indicate this is the modified version. Use the same steps as above.

  7. Make the changes which are mentioned below for the main interface SDE_PSFT_Stage_BalancingSegmentDimensionHierarchy.W_PSFT_BALANCING_SEGMENT_DH_PS in the folder SDE_PSFT_Stage_BalancingSegmentDimensionHierarchy under the PATCH_<Adaptor> folder.
    1. Modify the expressions of some of the columns as shown below.
      Column New Expression
      BALANCING_SEGMENT_NAME 
      IIF(SQ_PSTREE.NODE_RECNAME='TREE_NODE_TBL',
IIF(ISNULL(SQ_PSTREE.TREE_NODE_NUM),
SPACES2NULL("Y",SQ_PSTREE.TREE_NODE),
SPACES2NULL("Y",SQ_PSTREE.TREE_NODE)||'~'||'NODE'),
SPACES2NULL("Y",SQ_PSTREE.TREE_NODE))
      PARENT_BALANCING_SEGMENT_NAME 
      IIF(SQ_PSTREE.PARENT_NODE_NUM = 0,NULL,
IIF(SQ_PSTREE.NODE_RECNAME='TREE_NODE_TBL',
SPACES2NULL("Y",SQ_PSTREE.PARENT_NODE_NAME)||'~NODE',
SPACES2NULL("Y",SQ_PSTREE.PARENT_NODE_NAME)))
      PAR_INTEGRATION_ID 
      'Fund Code~' || SPACES2NULL("N",SQ_PSTREE.SETID) || '~' || 
SPACES2NULL("N",SQ_PSTREE.TREE_NAME) || '~' ||
IIF(SQ_PSTREE.PARENT_NODE_NUM = 0,NULL,
IIF(SQ_PSTREE.NODE_RECNAME='TREE_NODE_TBL',
SPACES2NULL("Y",SQ_PSTREE.PARENT_NODE_NAME)||'~NODE',
SPACES2NULL("Y",SQ_PSTREE.PARENT_NODE_NAME)))
      INTEGRATION_ID 
      'Fund Code~' || SPACES2NULL("N",SQ_PSTREE.SETID) || '~' || 
SPACES2NULL("N",SQ_PSTREE.TREE_NAME) || '~' || 
IIF(SQ_PSTREE.NODE_RECNAME='TREE_NODE_TBL',
IIF(ISNULL(SQ_PSTREE.TREE_NODE_NUM),
SPACES2NULL("Y",SQ_PSTREE.TREE_NODE),
SPACES2NULL("Y",SQ_PSTREE.TREE_NODE)||'~'||'NODE'),
SPACES2NULL("Y",SQ_PSTREE.TREE_NODE))
    2. Validate and save the changes to the interface.
    3. Before regenerating the scenario, go to the original task folder SDE_PSFT_Stage_BalancingSegmentDimensionHierarchy in the default installed mapping folder (for example, SDE_PSFT_90_Adaptor), and delete the existing default installed scenario (for example, SDE_PSFT_90_ADAPTOR_SDE_PSFT_STAGE_BALANCINGSEGMENTDIMENSIONHIERARCHY).
    4. Go back to the modified SDE_PSFT_Stage_BalancingSegmentDimensionHierarchy in the patch folder, navigate to Packages, and generate the scenario using the option to generate the scenario as if all underlying objects are materialized.
    5. Rename the scenario name to use the original default installed scenario name. For example, SDE_PSFT_90_ADAPTOR_SDE_PSFT_STAGE_BALANCINGSEGMENTDIMENSIONHIERARCHY for PSFT 9.0 or SDE_PSFT_91_ADAPTOR_SDE_PSFT_STAGE_BALANCINGSEGMENTDIMENSIONHIERARCHY for PSFT 9.1..
  8. Make the changes which are mentioned below for the main interface SDE_PSFT_BalancingSegmentDimensionHierarchy.W_BALANCING_SEGMENT_DHS in the folder SDE_PSFT_BalancingSegmentDimensionHierarchy under the PATCH_<Adaptor> folder.
    1. Modify the expressions (use BALANCING_SEGMENT_NAME instead of BALANCING_SEGMENT_ID) for some of the columns as shown below:
      Column New Expression
      LEVEL0_INTEGRATION_ID 
      'Fund Code'||'~'||SQ_HIERARCHY.SETID ||'~'|| SQ_HIERARCHY.BALANCING_SEGMENT_NAME
      All other LEVELx_INTEGRATION_ID 
      'Fund Code' || '~' ||  SQ_HIERARCHY.SETID || '~' || HIERARCHY_FLATTEN(SQ_HIERARCHY.BALANCING_SEGMENT_NAME,<level*>)
      INTEGRATION_ID 
      'Fund Code' || '~' || SQ_HIERARCHY.SETID || '~' || SQ_HIERARCHY.TREE_NAME ||'~'|| SQ_HIERARCHY.BALANCING_SEGMENT_NAME

      Note:

      Please note the <level> is not the same as the column Level x but it needs to be 31 for Level1, 30 for Level 2....... 1 for Level30.
    2. Navigate to the Flow tab and to the KM properties. Under the KM properties, navigate to a property called OBI_PARENT_CHILD_JOIN and modify the join condition there to the condition shown below: 
      PARENT.INTEGRATION_ID = CHILD.PAR_INTEGRATION_ID 
AND PARENT.DATASOURCE_NUM_ID = CHILD.DATASOURCE_NUM_ID 
AND PARENT.EFFDT=CHILD.EFFDT
    3. Validate and save the changes to the interface.
    4. Before regenerating the scenario, go to the original task folder SDE_PSFT_BalancingSegmentDimensionHierarchy in the default installed mapping folder (for example, SDE_PSFT_90_Adaptor), and delete the existing default installed scenario (for example, SDE_PSFT_90_ADAPTOR_SDE_PSFT_BALANCINGSEGMENTDIMENSIONHIERARCHY).
    5. Go back to the modified SDE_PSFT_BalancingSegmentDimensionHierarchy in the patch folder, navigate to Packages, and generate the scenario using the option to generate the scenario as if all underlying objects are materialized.
    6. Rename the scenario name to use the original default installed scenario name. For example, SDE_PSFT_90_ADAPTOR_SDE_PSFT_BALANCINGSEGMENTDIMENSIONHIERARCHY for PSFT 9.0 or SDE_PSFT_91_ADAPTOR_SDE_PSFT_BALANCINGSEGMENTDIMENSIONHIERARCHY. for PSFT 9.1.

Headcount Movement Detailed Report Displays Error

This issue applies to the Headcount Movement detailed report.

When navigating to the Headcount Movement detailed report from report Workforce Deployment/Staffing level, the detailed report returns the following error:

nQSError: 43119] Query Failed: [nQSError: 17001] Oracle Error code: 600, 
 message: ORA-00600: internal error code, arguments: [qesdpSigError], 
 [adaptive join resolution failed], [205], [], [], [], [], [], [], [], [], [] 
 at OCI call OCIStmtFetch. [nQSError: 17012] Bulk fetch failed. (HY000)

Workaround

As SYSDBA execute the following database level setting change:

alter system set "_optimizer_adaptive_plans"=false;