Oracle BI Applications (OBIA) 7.9.6.4 released

Good News Oracle Business Intelligence Application new version 7.9.6.4 is available to download.

Highlights of the Oracle BI Applications 7.9.6.4 Release:

  • New Application – Oracle Manufacturing Analytics with pre-built adapters for EBS Process Manufacturing R12.x and EBS Discrete Manufacturing R12.x and 11.5.10
  • New Application – Oracle Enterprise Asset Management Analytics with pre-built adapters for EBS R12.x, EBS 11.5.10 and IBM Maximo 7.5
  • Universal Adapter to extend the capability to other source systems
  • Certified for OBIEE 11g
  • Certified for Exalytics
  • Certified for DAC 11g including support for Exalytics / Times Ten, Patching Framework, Dual ETL Support and many other performance enhancements
  • Native support for mobile and tablet devices
  • Localized in 28 languages
  • Supported on Oracle, SQL Server, DB2 and Teradata DB
  • Rollup patch resolving 122 BI Applications bugs

Good thing so many bugs are fixed. To get solution / work around,  me and my team spent days on Severity 1 Service Requests with Oracle.

OBIA 7.9.6.4: Bugs Fixed in 7.9.6.4 [ID 1528774.1]

Link to Download Software and Documentations

https://blogs.oracle.com/proactivesupportEPM/entry/oracle_bi_applications_obia_7

TASK_GROUP_Load_PartyDimension “Duplicate Keys Found”

While Running Full load on Oracle Business Intelligence Applications 7.9.6.3, when using Source system R12 TASK_GROUP_Load_PartyDimension failed with error Log says the issue is because “duplicate keys found”

Extract from the Log

**********************************************************************************************************************

CREATE INDEX:W_PARTY_D:W_PARTY_D_U1 has started.

ANOMALY INFO::: Error while executing : CREATE INDEX:W_PARTY_D:W_PARTY_D_U1
MESSAGE:::java.lang.Exception: Error while execution : CREATE UNIQUE INDEX
W_PARTY_D_U1
ON
W_PARTY_D
(
INTEGRATION_ID Asc
,DATASOURCE_NUM_ID Asc
,EFFECTIVE_FROM_DT Asc
)
NOLOGGING
with error DataWarehouse:CREATE UNIQUE INDEX
W_PARTY_D_U1
ON
W_PARTY_D
(
INTEGRATION_ID Asc
,DATASOURCE_NUM_ID Asc
,EFFECTIVE_FROM_DT Asc
)
NOLOGGING
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
EXCEPTION CLASS::: java.lang.Exception

**********************************************************************************************************************

For Resolution:- Download and review the readme and pre-requisites for Patch.14085106.

Live Console in Oracle Sourcing (Explanation about Graphs)

Live Console allows user to graphically view and compare responses to a negotiation.

The Live Console gives user a single location from which he/she can generate many in-depth graphic displays. user can use these graphs to assist in analyzing  negotiation responses during and after a negotiation.

Live Console is not available for:

  1. RFIs
  2. Contract Purchase Agreement negotiations that do not have lines.
  3. Sealed negotiation (once the negotiation is unsealed, Live Console is available).
  4. Draft negotiations (since the negotiation has no responses).
  5. Additionally, line-level information and displays are not available for large or very large negotiations.
  6. Also, information is not available for Two-Stage RFQs until the technical evaluation is completed and the commercial stage is unlocked.

Graphs Area

The Graph Area can display four types of graphs.

Best Bids by Time (XY Chart)

  1. The graph shows bids for the top five suppliers. If the supplier has multiple bids, all bids for that supplier are shown.
  2. The Y axis shows the best bid offered for the negotiation. This is calculated as the sum of (price * quantity) for all lines on the negotiation. The X axis shows the time range over which the bids have been submitted.
  3. The vertical line indicates the close date

All Bids By Time (XY chart)

  1. This chart graphs all the bids for the negotiation.
  2. The Y axis shows the best bid offered for the negotiation. This is calculated as the sum of (price * quantity) for all lines on the negotiation. The X axis shows the time range over which the bids have been submitted.
  3. The vertical line indicates the close date.

Savings by Supplier (Bar chart)

  1. The graph shows bids for the top five suppliers. If the supplier has multiple bids, all bids for that supplier are shown.
  2. The Y axis shows the best bid offered for the negotiation. This is calculated as the sum of (quantity * (bid price – current price)) for all lines on the negotiation. The X axis shows the bid number.

Best Bids by Supplier (Bar chart)

  1. The graph shows bids for the top five suppliers. If the supplier has multiple bids, all bids for that supplier are shown.

Graphs are not available for

  1. Contract Purchase Agreement negotiations that have no lines.
  2. Blanket Purchase Agreements or Contract Purchase Agreement negotiations that have lines but no Estimated Quantity value.
  3. Negotiations for which there are no responses (for example, a negotiation that is in Preview status.
  4. Quotes that are not shortlisted from the technical evaluation stage of a Two-Stage RFQ.

 

Disable JRE Auto-Update for All E-Business Suite End-Users

All desktop administrators must IMMEDIATELY disable the Java Runtime Environment (JRE) Auto-Update option for all Windows end-user desktops connecting to Oracle E-Business Suite Release 11i, 12.0, and 12.1

Read Detail

Supplier Search results in ‘No results found’

While Searching Supplier using Payable Manager or any other responsibility in R12.1.X results into ‘No Results Found’ though supplier is already defined in system.

This is because POS: SM: Enable Data Security for Supplier” profile option value. Value should be NO at site level. In case if  you wish to use this feature the refer to Oracle Supplier Management Implementation and Administration Guide

Oracle Sourcing Configuration and Functionality

This document contains complete steps to setup Oracle Sourcing along with major functionality This document can be used to setup oracle sourcing where purchasing setup is already in place
Download

How to Create Code Combination ID by API

I was on an integration assignment between Home Grown Project System and Oracle Payable. A big gap which I faced there was how to maintain single source of Account Code Combinations. We had following options to manage this issue.
Built a View of GL_CODE_COMBINATIONS table and pass Combination and ID from Oracle to Custom System
Use API and create Code Combination
I used second way to handle this. Here is script which i used in that Assignment ( it works for me but you need to be careful as it’s not documented any where 🙂
—–Create Function
CREATE OR REPLACE FUNCTION APPS.XXX_CREATE_CCID
( P_CONCAT_SEGS IN VARCHAR2
) RETURN VARCHAR2
IS
L_STATUS BOOLEAN;
L_COA_ID NUMBER;
BEGIN
SELECT CHART_OF_ACCOUNTS_ID
INTO L_COA_ID
FROM GL_SETS_OF_BOOKS
WHERE SET_OF_BOOKS_ID = 2021; –UPDATE THIS WITH SET OF BOOKS ID

L_STATUS := FND_FLEX_KEYVAL.VALIDATE_SEGS(
‘CREATE_COMBINATION’,
‘SQLGL’,
‘GL#’,
L_COA_ID,
P_CONCAT_SEGS,
‘V’,
SYSDATE,
‘ALL’, NULL, NULL, NULL, NULL,
FALSE,FALSE, NULL, NULL, NULL);
IF L_STATUS THEN
RETURN ‘S’;
ELSE
RETURN ‘F’;
END IF;
END ;
/

—–EXECUTE FUNCTION
DECLARE
RETVAL VARCHAR2(200);
P_CONCAT_SEGS VARCHAR2(200); /* ‘10.2001.2211101.987872.001.0000’ THIS COMBINATION I WANT TO CREATE */
BEGIN
RETVAL := APPS.XXX_CREATE_CCID ( P_CONCAT_SEGS );
COMMIT;
END;

How to Change Company Logo on Standard Purchase Order Template

Few days back one of my friend was asking me that how can She add corporate logo on standard purchase order template . here is solution with screen shots. same solution is also there on METALINK but missing a major step 🙂

How to Change Company Logo on Standard Purchase Order Template

No approver found for Purchase Requisition XX

I was working on one client site for AME setup with Purchase Requisition. I finished setup for AME transaction Type “Purchase Requisition Approval”. When requester was submitting requisition system initially shows that it’s in process but after one minute requester was getting notification “No approver found for Purchase Requisition”.
I checked from AME Test Workbench functionality by using transaction ID system was generating approval list as per setup. In other words everything was setup properly but still after submission user was getting error while submitting from Core Purchasing Form.

If user was completing this purchase requisition by using iProcurement Checkout functionality request was going for approval to supervisor as per setup.

After 2 days of working, found out one silly problem .guess what????????

Download Solution

Oracle Procurement Contract (a step by step user guide)

Download Word File

SQL Query to Get Diffrence in Values for Profile Options assigned on USER Level

I got this Query from Gareth Roberts BLOG …Very Very Useful during implementation specially if there are some custom profile options .

I used to work for a home appliances Manufacturing Organization . we used some custom profile options to setup level of security for Sales Manage, Salesman and Warehouse User. Some Time we faced issues and the and discovered that it’s wrong profile option assignment.

/* Formatted on 2009/08/19 08:41 (Formatter Plus v4.8.8) */
SELECT *
FROM (WITH prof_di AS
(SELECT ‘USER’ level_name, fu.user_name level_value,
fpo.profile_option_id, fpot.user_profile_option_name,
fpo.profile_option_name, fpov.profile_option_value
FROM fnd_user fu,
fnd_profile_options fpo,
fnd_profile_option_values fpov,
fnd_profile_options_tl fpot
WHERE fu.user_id = fpov.level_value
AND fpo.profile_option_id = fpov.profile_option_id
AND fpo.profile_option_name = fpot.profile_option_name
AND fpot.LANGUAGE = ‘US’
AND fpov.level_id = 10004
AND fu.user_name = ‘SYSADMIN’), /*User Name Working Fine*/
prof_gr AS
(SELECT ‘USER’ level_name, fu.user_name level_value,
fpo.profile_option_id, fpot.user_profile_option_name,
fpo.profile_option_name, fpov.profile_option_value
FROM fnd_user fu,
fnd_profile_options fpo,
fnd_profile_option_values fpov,
fnd_profile_options_tl fpot
WHERE fu.user_id = fpov.level_value
AND fpo.profile_option_id = fpov.profile_option_id
AND fpo.profile_option_name = fpot.profile_option_name
AND fpot.LANGUAGE = ‘US’
AND fpov.level_id = 10004
AND fu.user_name = ‘AHMADBILAL’) /*User Name Where we are facing some issue*/
SELECT pd.profile_option_id, pd.user_profile_option_name,
pd.profile_option_name, pd.profile_option_value d_value,
pg.profile_option_value g_value,
DECODE (pd.profile_option_value,
pg.profile_option_value, ‘EQUAL’,
‘DIFF’
) status
FROM prof_di pd, prof_gr pg
WHERE pd.profile_option_name = pg.profile_option_name(+)
UNION
SELECT pg.profile_option_id, pg.user_profile_option_name,
pg.profile_option_name, pd.profile_option_value d_value,
pg.profile_option_value g_value,
DECODE (pg.profile_option_value,
pd.profile_option_value, ‘EQUAL’,
‘DIFF’
) status
FROM prof_di pd, prof_gr pg
WHERE pg.profile_option_name = pd.profile_option_name(+))
WHERE status != ‘EQUAL’;

How to Migrate Forms Personalization from One Instance to Other Instance.

It’s very normal during implementation that we need to move all personalization from TEST / DEVELOPMENT to PRODUCTION there are two way (best of my Knowledge)

Method 1
Open TEST instance navigate to form where you did personalization, simultaneously open PRODUCTION instance start copy pasting….. Sounds pathetic (at least to me)
Method 2
Use FNDLOAD (purely a DBA task)
Download for a specific form
FNDLOAD / 0 Y DOWNLOAD
$FND_TOP/patch/115/import/affrmcus.lct FND_FORM_CUSTOM_RULES form_name=

Download all personalization
FNDLOAD / 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct FND_FORM_CUSTOM_RULES

Upload
FNDLOAD / 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct

For more information you can visit this LINK
http://oracle.anilpassi.com/oracle-fndload-script-examples.html

How to Get Approval Group assigned to specific Job and Position in Oracle Purchasing

How to Get Approval Group assigned to specific Job and Position in Oracle Purchasing

Remittance Advice by Email to Supplier by Using UTL_SMTP

Click on Link to Read Article

Strength and Weakness of Approval Hierarchies available in Oracle Purchasing

Strengths and Weaknesses of Approvals Using the Employee/Supervisor Relationship

Strengths

Allows flexibility of establishing approval groups around either jobs or positions.

Assuming Oracle Human Resources (HR) is installed and the data is being maintained, Purchasing uses the supervisor name on the employee record which should be entered and maintained for HR purposes already.

Weaknesses

Documents may not travel by the employee/supervisor relationship for approval purposes, but take a different path in part or entirely.

Documents can get hung up more frequently due to the volume of personnel changes that typically occur in companies.

Higher volumes of documents need to be re-routed using the Forward-To settings. Recall that the first name alphabetically appears as the default.

Strengths and Weaknesses of Approvals Using Position Approval Hierarchies

Strengths

Allows approval paths to be set up that follow the actual flow of documents for approval

Assuming Human Resources is installed and the data is being maintained, Purchasing can make an employee representation of the position approval hierarchy by simply running the Fill Employee Hierarchy process. This will allow changes occurring in personnel assignments to be updated in the position approval hierarchy.

Documents can get hung up less frequently due to the lower volume of structural hierarchy changes compared to the higher volume of personnel changes that typically occur in companies.

Lower volumes of documents need to be re-routed using the Forward To settings, especially when there are multiple holders in a particular job or position. Recall that the first name alphabetically appears as the default when there are multiple holders.

Weaknesses

Cannot be used with jobs, but must be used with positions.

Some Doubts………..In Oracle Purchasing

What if there is more than one holder in a job or position?

Multiple holders in the same job or position can only occur if Oracle Human Resources is installed. If only Oracle Purchasing is installed, you can assign only one employee to each job or position. If multiple holders exist when using position approval hierarchies, Oracle Workflow will route the document to the holder based on alphabetical order. You can see who the system will route the document to by clicking the Forward radio button, overriding the defaulted name if needed.

What if there are changes to the position approval hierarchy?

Personnel changes are updated in the position approval hierarchy by running the Fill Employee Hierarchy process which reviews the employee record to determine the current position assignment. You will probably want to schedule this process to run on a frequent basis to ensure smooth processing of all approval requests. Structural changes to the position approval hierarchy require the hierarchy to be rebuilt from the point where the change occurs to the bottom of that branch. Documents are routed according to the hierarchy in effect at the time they are submitted for approval.

What if Human Resources were already implemented with jobs set up, but no positions?

Unless the decision to set up jobs only and no positions can be revisited, you will have to route all documents by the employee/supervisor relationship and maintain this data on the employee records.

What if the approver is unavailable for an extended period?

Oracle Purchasing has the ability to automatically forward documents when users do not respond to notifications. This tool should be used when possible to prevent documents from holding up business productivity. Oracle Workflow manages this functionality, typically set up to send a first and second reminder after pre-determined time periods before forwarding the notification to the next approver.

What if the document has a status of Pre-Approved?

The status of Pre-Approved is the outcome of a person forwarding a document for approval even though the forwarding person has the necessary authority to approve it. The document may have been forwarded by mistake or for business reasons. Once the person it was forwarded to approves the document, the status will be changed to Approved and subsequent actions such as receiving and invoicing can be completed.

What if there is no account range on an approval group assigned to a job or position?

By default, all accounts are excluded from an approval group and therefore documents governed by this approval group cannot be approved. You avoid this situation by ensuring that there is at least one Account Range Include rule on every Approval Group.

What if Workflow can’t find a supervisor to approve the document?

If this occurs, the person submitting the request for approval must forward the document to a different person in the list of values for Forward To. If this isn’t done, the document will be returned to an Incomplete status and a notification will be sent stating No Approver Found – Please Select a Forward To Employee. If using employee/supervisor relationship to determine approval paths, the list of values will include all active employees. If using position approval hierarchies to determine approval paths, the list of values will include all employees in the hierarchy selected.

What if a job or position has different authority levels for different document types?

You may have jobs or positions that can approve one document up to a specified dollar amount, while they can approve another document at a lower dollar amount. If this occurs, simply set up multiple approval groups with the rules properly defined for the differences and ensure that the right approval group is assigned to the correct document type.

What if documents flow employee/supervisor, but approval rules are needed?

Approval Groups can be used whether approval paths are determined by employee/supervisor relationships or by position approval hierarchies.

Oppps Employee as Supplier in Oracle Payables R12. How Crazy….

How to enable creation of employee as supplier in oracle payable supplier entry form

How to Turn On “About This Page” in Oracle R12 Web Forms

Set Following Profile Options as Required at User or Site

Personalize Self-Service Defn = YES

FND: Personalization Region Link Enabled = YES

Disable Self-Service Personal = NO (Only at Site)

FND: Diagnostics = Yes

Bounce the Apache Server

Login to Application you can see the About This Page Link on all web pages

By Using About This Page link you can have almost all information of technology Stack