Introduction
Oracle Fusion Cloud Application doesn’t have an inbuilt validation where it restricts assigning a National ID which has already been allocated to another person. As such there could be multiple individuals ( some dummy users, test users .. etc.) which might have the same national identifier values attached with their records.
Business would like to know how many such National Identifiers exist in the system and what the person records who are having such ambiguity.
While the solution would also have been achieved by writing a SQL query, but for this example we would use OTBI to achieve the same.
In a broad level we would need to perform the following steps:
-
Create another OTBI Analysis which will have Person Details like Person Number, Display Name and National ID. (Lets say we call this as PersonNationalIDListAnalysis)
-
Create a OTBI Analysis which would list all the National Identifiers that have more than one occurrence. ( Lets say we call this as DuplicateNationalIDListAnalysis)
-
Use the OTBI Drill Down Feature where in PersonNationalIDListAnalysis will have NationalID as a prompted field and would be called as an Action when user clicks on NationalId field of DuplicateNationalIDListAnalysis. Sounds complicated ?. It isn’t will get clarity when we go through creation of same.
So let us get started.
Creating PersonNationalIDListAnalysis
We would be using three columns namely Person Number, Display Name and National ID for this Analysis. Details about the same are as below:
PersonNationalIDListAnalysis |
|
Subject Area Used: Workforce Management Person Real Time |
|
Column Name |
Column Value |
Person Number |
"Person Details"."Person Number" |
Display Name |
"Person Names"."Display Name" |
National ID |
"Person National Identifiers"."National Identifier Number" |
Filter Criteria: National ID is prompted |
And the results would appear as:
That’s it for the first analysis. Now let us move to the second analysis
Creating DuplicateNationalIDListAnalysis
We will have 5 different columns in this analysis namely National Identifier Type, National ID, National Identifier Type-National ID(custom heading),Is Duplicate (Yes/No). Details about the same are below:
DuplicateNationalIDListAnalysis |
|
Subject Area Used: Workforce Management Person Real Time |
|
Column Name |
Column Value |
National Identifier Type |
"Person National Identifiers"."National Identifier Type" |
National ID |
"Person National Identifiers"."National Identifier Number" |
National Identifier Type-National ID |
CONCAT(CONCAT("Person National Identifiers"."National Identifier Type",'-'),"Person National Identifiers"."National Identifier Number") |
Count(National ID) |
COUNT(CONCAT(CONCAT("Person National Identifiers"."National Identifier Type",'-'),"Person National Identifiers"."National Identifier Number")) |
Is Duplicate (Yes/No) |
CASE WHEN COUNT(CONCAT(CONCAT("Person National Identifiers"."National Identifier Type",'-'),"Person National Identifiers"."National Identifier Number")) = 1 THEN 'No' ELSE 'Yes' END |
Filter Criteria: COUNT(CONCAT(CONCAT("Person National Identifiers"."National Identifier Type",'-'),"Person National Identifiers"."National Identifier Number")) is greater than 1 |
And the results would appear as:
We would now move to the third and most important step.
Using OTBI Drill Down Feature
We need to select the ‘National ID’ column of ‘DuplicateNationalIDListAnalysis’ and use following navigation.
National ID -> Column Properties -> Interaction and fill following details
Attribute |
Value |
Column Heading -> Primary Interaction |
Default (Drill) |
Value -> Primary Interaction |
Action Links |
Action Links -> Link Text |
Navigate to PersonNationalIDListAnalysis |
Action Links -> Action |
PersonNationalIDListAnalysis |
Action Links -> Show Link |
Always Enabled |
Once we click on OK and go to results tab we could see that the National ID column is highlighted and when we click on any of the values it shows ‘Navigate to PersonNationalIDListAnalysis’
We could also see that the record we have selected says that it is associated with 3 different person numbers. So when we would navigate to the Analysis (PersonNationalIDListAnalysis) it should display 3 person numbers.
Which is what the analysis says too.
So thus we have accomplished what we were looking for by making use of 2 OTBI Analysis and Drill Down feature.
Comments
Just wanted to say I love reading your blog and
look forward to all your posts! Keep up actors in the movie casino - Brady: https://caut.co.uk/user/profile/SenaidaGorh - excellent work!
RSS feed for comments to this post