Categories
Employee Name and Person Number in Project Billing - Invoices Real Time Subject area are incorrect
I tried to report on details of an invoice generated by Fusion using OTBI. When I choose the 'Project Billing - Invoices Real Time' subject area, it has the details, the invoice number, the invoice date, revenue amount, the transaction number. However, it does not have the expenditure item date and the exact quantity of hours. I had to join this with 'Project Costing - Actual Costs Real Time' subject area to get the quantity and the expenditure item date.
When I now try to report the employee related to the transaction, the report brings up the name of the person who processed the invoice batch and not the employee related to the transaction number. When I look at the 'Project Costing - Actual Costs Real Time' subject area alone, it has the correct employee name and the person number associated with the transaction number. However, due to using both the subject areas, it forces the 'batch processor's name in the place of the employee and this results in an incorrect report.
Have you faced this problem? What is the workaround for this issue?
Answers
-
Hi @ravinat,
One work around would be using the Logical SQL.
Create the first data set using 'Project Billing - Invoices Real Time' and create the second dataset using 'Project Costing - Actual Costs Real Time'. Take the required fields and then join both the dataset on a common key like 'Invoice Number' or 'invoice ID etc.
Now you can get the all the columns required.
Hope this help.
Cheers,
0 -
Logical SQL does work. It comes with maintenance headaches. Thanks for your suggestion Bhaskar.
Oracle has to fix this glaring error, though.0 -
Agree with you.
Probably in this case combining both SAs from Answers might not work due to the difference in Data Granularity.
Cheers,
0 -
Hi @ravinat,
Please create an Idea in the idea lab with your business case / requirement and if it is getting enough votes, Oracle will pick this up for future release.
Also would you mind selecting the answer that provide you the solution / work around so that it'll be helpful for fellow community members in future.
Thank You!
Cheers,
0 -
I wrote a LSQL based query where I pick the Employee name from the 'Project Costing - Actual Costs Real Time' and the Employee number from the 'Project Billing - Invoices Real Time' subject area and I force the Project BU, Project number in the join criteria. This works but with limitations. You cannot pass the Project BU and the Project Number from the dashboard and have a query where these variables are prompted. You need to create presentation variable to subset it. When user wants to add a new field to the report, you have to begin from scratch as LSQL is inflexible in this area.
0 -
Yes, your observations are correct for any modification for LSQL is time consuming activity.
0 -
Try below options:
- Use a Separate Query for Employee Data
Instead of directly joining both subject areas, create a subquery or a separate analysis that pulls employee details from Project Costing - Actual Costs Real Time.
Then, use Union or Filters to merge the correct employee data.2. Modify the Join Conditions
Ensure that the join condition is based on Transaction Number rather than Invoice Batch details.
If possible, use Person Number from Project Costing instead of relying on the default employee field in Project Billing.3. Use OTBI Filters to Prioritize Employee Data
Apply a filter to exclude batch processor names and prioritize employee records from Project Costing.
Example: Filter by Employee Type or Transaction Source.Check the below:
OTBI Report with two subject areas that are not connected — Oracle Analytics
Incorrect Costs Displayed Using "Project Costing-Actual Cost Real time" OTBI Analysis (Doc ID 2711557.1)
0
