PL/SQL function in universe

[Former Member]

Hi Experts,

I am creating a universe on top of Oracle EBS. There is a PL/SQL function in the resultant Query(which we got from oracle source system side). We have been able to create data foundation based on the tables and their relationships. Unfortunately, we are NOT been able to map that PL/SQL function in our Universe.

How can we achieve this?

Regards

Ayaz

[Henry_Banks · Accepted solution]

Hi,

There's a Note 1600940 - Unable to create a universe based on stored
procedure with OUT parameters in Universe Designer

which may give you some ideas.

Would be interested to know if you get any errors ?

regards,

H

[Former Member · Accepted solution]

Hi Henry,

Thanks a lot for your kind response . Henry, let me describe scenario in detail.

User has given the requirements in form of reports which are being used currently (which were developed in "Oracle Report Builder 6i", source system is Oracle EBS). Now client gave us those reports and asked us to come up with some analytic based on those reports. Now to achieve this,first we  need to create universe which can produce the same results as produced by Oracle Reports. Those reports contains some tables (which we have picked in our universe (using IDT 4.0)) and a PL/SQL function written on the REPORT LEVEL (inside the Oracle Report Builder 6i). This function is NOT stored inside database, It's just embedded at report level. I am attaching 2 Files.

1. is the PL/SQL Function file having complete function details.
2. is the Query which generates the report in Oracle Builder 6i.

In the "2" you will find this line of code: xxraya_hrms.get_contact_name(db.customer_id,db.delegate_contact_id) emp_name,

This line is calling the function of File "1".

We have implemented rest of the functionality of File"2" in the IDT, BUT we are NOT BEEN able to implement this Function. How can we achieve this? Kindly also tell us that do we need to do something at Webi Level to implement this function, may be using some tricks in Query Panel (Its just a thought, i am not sure about it).

We are hoping to build a dashboard on this query. I hope i have made the scenario clear. Kindly let me know if you need any further details.

Thanks & Regards

Ayaz

1.txt.zip

2.txt.zip

[marc_daniau · Accepted solution]

Have you tried in the business layer to define the following dimension object?

Name: Employee name

SQL: xxraya_hrms.get_contact_name(db.customer_id, db.delegate_contact_id)

[Former Member · Accepted solution]

What to write in Where clause? I pasted it in Select but its asking for WHERE Clause.

[marc_daniau · Accepted solution]

The WHERE clause is optional; therefore you can leave it empty.

[Former Member · Accepted solution]

. It's asking for a reference missing in WHERE clause. This error has shown by IDT.

[marc_daniau · Accepted solution]

Have you tried the object in a query in the Information Desing tool and check the underlying SQL script to compare it with what you expect to be sent to the Oracle database?

[Former Member · Accepted solution]

How can i send values to the Oracle Database through this? This is the issue that how to implement the function.

[Henry_Banks · Accepted solution]

Marc was suggesting you look at an object syntax for guidelines.

[Former Member · Accepted solution]

Thanks Marc. It Worked

[Former Member]

If anyone else gets stuck..

we had to use this syntax instead:

wrong: xxraya_hrms.get_contact_name(db.customer_id, db.delegate_contact_id)

correct: apps.xxraya_hrms.get_contact_name(db.customer_id, db.delegate_contact_id)

Had to add the user at the begining

[Former Member]

OK solved in my case.

There is sort option in query panel of Dashboard.

It gets the data sorted by the field selected.