How to build an Analysis on OTBI to show every colleague's default expense account?

Trent M

Summary:

Need report to show all colleague's default expense accounts.

Content (required):

I need to create a report to show every colleague's default expense account so we can review and update colleague's who have moved to different departments within the company. Currently I cannot find a way to build a report like this. The expense report templates only pull actual expense reports that were submitted and the user system usage template does not let me pull their expense account.

Version (include the version you are using, if applicable):

Code Snippet (add any code snippets that support your topic, if applicable):

This is the data that I need to be able to see for every employee:

Bhaskar Konar

Hi @Trent M,

It seems default expense account is not available within OTBI Subject Areas. Here's a not from Oracle.

Default Expense Account In OTBI Report (Doc ID 2492612.1): Document 2492612.1 (oracle.com)

Alternatively you have to build a BI Publisher Report. Following query might be useful for you.

This is stored in DEFAULT_CODE_COMB_ID column in PER_ALL_ASSIGNMENTS_M table. 

DEFAULT_CODE_COMB_ID is a foreign key to CODE_COMBINATION_ID in GL_CODE_COMBINATIONS table.

You could use the following query to get details from GL_CODE_COMBINATIONS for an employee:

select * from GL_CODE_COMBINATIONS where code_combination_id in (select
distinct default_Code_comb_id from per_all_assignments_m where person_id in
(select person_id from per_all_people_f where person_number like 'PERSON_NUMBER'))

Hope this help.

Cheers,

Trent M

Hi Bhaskar,

It appears I would need to run this query with a specific person number. I need the default expense account for multiple users so running this by each person number would be very time-consuming. Is there a way we can run a query by a specific company number (segment 1)?

Thanks,

Trent

Bhaskar Konar

Hi Trent,

Yes that can be done. I was just giving you a sample query. If we remove the filter on Person Number it'll bring everybody in one go.

select * from GL_CODE_COMBINATIONS where code_combination_id in (select
distinct default_Code_comb_id from per_all_assignments_m where person_id in
(select person_id from per_all_people_f))

I'll check if I'm having any query with SEG1, shall keep you posted.

Cheers,

Bhaskar Konar

Hi Trent,

Here's query where you can specify the Segment1

select * from GL_CODE_COMBINATIONS 
where 
code_combination_id in (select
distinct default_Code_comb_id from per_all_assignments_m where person_id in
(select person_id from per_all_people_f))
AND SEGMENT1 = 'Specify SEGMENT1_NAME/NUMBER'

Hope this help.

Cheers,

Trent M

Hi Bhaskar,

We are getting closer to what I need. The one issue I'm noticing is that the query pulls in a code combination ID, which I assume refers to a specific user. Is there something that will pull in the user name instead? And then also within this query, it is only pulling in distinct default expense accounts which means it is leaving out any duplicates. There are many users with the same default expense accounts. I need to see all users even if they have the same expense accounts and then their names as well. If there is a way I can map the code combination IDs to a user name then I'd be willing to try that as well.

Thanks,

Trent

Trent M

Hi Bhaskar,

Curious as to if a query like the one I described above exists?

Thanks,

Trent

Bhaskar Konar

Hi @Trent M,

Apologies for late response.

Here is the query, please have a look

SELECT ppf.person_number,
  ppnf.display_name,
  pf.default_code_comb_id,
  glcc.segment1,
  glcc.segment2,
  glcc.segment3,
  glcc.segment4,
  glcc.segment5,
  glcc.segment6
FROM per_all_people_f ppf,
  per_all_assignments_m pf,
  gl_code_combinations glcc,
  per_person_names_f ppnf
WHERE ppf.person_id        =pf.person_id
AND pf.default_code_comb_id=glcc.code_combination_id
AND PPF.person_id = PPNF.person_id
AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN pf.effective_start_date AND pf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
AND PPNF.name_type = 'GLOBAL'

Hope this help.

Cheers,

Trent M

Hi Bhaskar,

Thanks, this was a huge help! Just one more question. Is there a parameter we can input in the query above so it only pulls active employees? I noticed this particular query is pulling our inactive employees and I would like those excluded.

Thanks!

Trent

Bhaskar Konar

Hi @Trent M,

Please find below query where I have included Assignment Status Type. Either you can download the full result set and then exclude those that you don't need using spreadsheet filter or just do the filtering in the SQL for the status that is required.

SELECT ppf.person_number,
  ppnf.display_name,
  pf.assignment_status_type,
  pf.default_code_comb_id,
  glcc.segment1,
  glcc.segment2,
  glcc.segment3,
  glcc.segment4,
  glcc.segment5,
  glcc.segment6
FROM per_all_people_f ppf,
  per_all_assignments_m pf,
  gl_code_combinations glcc,
  per_person_names_f ppnf
WHERE ppf.person_id        =pf.person_id
AND pf.default_code_comb_id=glcc.code_combination_id
AND PPF.person_id = PPNF.person_id
AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN pf.effective_start_date AND pf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
AND PPNF.name_type = 'GLOBAL'

Hope this help.

Cheers,

Trent M

Thank you Bhaskar! That's exactly what I needed.

I really appreciate all your help with this!

Thanks again,

Trent