Embedded Files
User Defined Table(UDT) is a table defined by the user in application to hold the data .Oracle HRMS user-defined tables are different from the database tables of the Oracle Relational Database Management System.While defining the table we need to define all the rows and the columns. With the help of Oracle Fast formula, we can validate the entry made in the table column.
Basic Requirement
Our basic requirement is to create a global UDT that will hold the employee data and can be accessible from across the business group.
This data will be maintained by business users across the globe in different business group. Each business group will able to see their own data.
Problem Background
The UDT can be defined from PUI (professional User Interface. Oracle provided us user interface through which we can define our UDT.
Navigation:- Global HRMS Manager >> Other Definitions >> Table Structure (for Table Row and Column Definition and Row Values)
Global HRMS Manager >> Other Definitions >> Table Values (for Table column values)
Now the problem is that when we use the PUI, it will be business group dependent. It means if we create any UDT with "US HRMS Manager" it will be only visible to "US business group". Similary the UDT created via "UK HRMS Manager" will be visible to "UK business group".
Even if we create this UDT using "Global HRMS Manager" it will be create in "Setup Business Group",i.e, the value that is set at site level for "HR:Business Group" security profile.
Now lets check the metadata of the UDT that is stored in Oracle databse.
First we will discuss about how can we create a UDT and then we will talk about the solution of the problem
1) Go to US HRMS Manager >> Other Definitions>>Table Structure and date track to suitable date
2) Enter the values for the field as shown below.
3) Now for Setting up the Columns please refer the screen shot below
Note:- If we are using formula validation for a column's entries, select the name of the validation formula for the column(not shown in the above picture)
4) To enter the table values,i.e, the column values for each rows go to
US HRMS Manager >> Other Definitions >> Table Values
5) Save your work.
Our user defined table is now ready to use/modify the data. But since we have created the table defintion from US Business group responsibility, so it will only be visible from US business group. If we try to query it from UK Business group then it won't be visible from there
Solution Approach
1) We reach our goal we are going to use the following
a) pay_user_table_api
2) pay_user_column_api
But we cannot use those api directly, as Oracle won't allow us to pass Null in both the parameter business group and legislation Code.
To make the Oracle works we have apply the following trick
We have to make Oracle understand that we are defining "System Data".
Thats all we need to do. Now if we query for the metadata in the oracle table, we will get the business group id and legislation code as null.
Now Lets consider the above example given in Business Requirement section, say Suman and Rakesh are from US Business group and Joy from UK Business group. Then
enter the values of the Row from corresponding business group responsibility also enter the column values.
Now if we look into the row values stored in the database, we will see that 'Suman' and 'Rakesh' will have same business group where as 'Joy' will have different business group
Disclaimer:- This is a knowledge sharing site. This topic talks about a custom solution. Oracle may not provide you a support for any data corruption or any other problem in your custom code/problem arises because of the custom code. The author is not responsible for any kind of system/data problem appears because of usages of this code.Reader/implementer must do it on his/her own risk/responsibility.
Page updated
Google Sites
Report abuse