Oracle Transactional Business Intelligence

Products Banner

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

Received Response
559
Views
15
Comments

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:


Tagged:

Answers

  • Bhaskar Konar
    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,

  • 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
    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
    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,

  • 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
    Trent M ✭✭✭

    Hi Bhaskar,

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

    Thanks,

    Trent

  • Bhaskar Konar
    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
    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
    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
    Trent M ✭✭✭

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

    I really appreciate all your help with this!

    Thanks again,

    Trent

  • Bhaskar Konar
    Bhaskar Konar ✭✭✭✭✭

    Hi @Trent M,

    Glad you found it useful.

    Appreciate your time & patience.

    Cheers,

  • Nathan CCC
    Nathan CCC ✭✭✭✭✭

    FYI Field default expense account on a person assignment employment information

    IS available in subject area "Expenses - Expense Transactions Real Time"

    select all 0 s_0
    , "Employee"."Employee Number" as "Person Number"
    , "Employee"."Employee Default Expense Concatenated Segments" as "Default Expense Account Code"
    , "Employee"."Employee Default Expense Account" as "Default Expense Account Description"
    from "Expenses - Expense Transactions Real Time"
    order by 2 asc nulls last
    
  • Trent M
    Trent M ✭✭✭

    Hi Nathan,

    I am aware that exists. However, that only pulls the default expense account for employees who have submitted expense reports. Employees who have not submitted expense reports will not show up in that field. For this particular project, I needed the default expense account for all employees regardless if they have submitted an expense report.

    Thanks,

    Trent

  • Nathan CCC
    Nathan CCC ✭✭✭✭✭

    Hi, Why do you think it will only return for people who have submitted expenses? Is that an assumption? If you ran the OTBI analysis above I think you will find it will return default expense accounts for all persons whether or not they have done expenses items/reports in the expenses module. As you can see from the physical SQL in the session log, because we have selected attribute columns from the same dimension, and no measure columns, the logical data model has not joined to a fact, so you get all persons.

    WITH 
    SAWITH0 AS (select T4422506.C365682431 as c1,
       T4422506.C84210515 as c2,
       T4422506.C228592637 as c3
    from 
       (SELECT V486837407.PERSON_NUMBER AS C365682431
         ,     V371846109.CONCATENATED_SEG AS C84210515
         ,     V371846109.DESCRIPTION128 AS C228592637
         ,     V486837407.PERSON_ID415 AS PKA_PersonDetailsPEOPersonId0
         ,     V486837407.EFFECTIVE_START_DATE424 AS PKA_PersonDetailsPEOEffective0
         ,     V486837407.EFFECTIVE_END_DATE433 AS PKA_PersonDetailsPEOEffective1
         ,     V371846109.CODE_COMBINATION_ID AS BDep_CodeCombinationId0 
         FROM (SELECT /*+ NO_PUSH_PRED */ PersonPEO.PERSON_ID AS PERSON_ID271
         ,     PersonDetailsPEO.PERSON_ID AS PERSON_ID415
         ,     PersonDetailsPEO.EFFECTIVE_START_DATE AS EFFECTIVE_START_DATE424
         ,     PersonDetailsPEO.EFFECTIVE_END_DATE AS EFFECTIVE_END_DATE433
         ,     PersonDetailsPEO.PERSON_NUMBER
         ,     AssignmentPEO.ASSIGNMENT_ID AS ASSIGNMENT_ID776
         ,     AssignmentPEO.DEFAULT_CODE_COMB_ID 
         FROM PER_PERSONS PersonPEO
         , PER_ALL_PEOPLE_F PersonDetailsPEO
         , PER_ALL_ASSIGNMENTS_M AssignmentPEO 
         WHERE (PersonPEO.PERSON_ID = PersonDetailsPEO.PERSON_ID 
         AND PersonPEO.PERSON_ID = AssignmentPEO.PERSON_ID 
         AND ( DATE'2023-03-06' BETWEEN PersonDetailsPEO.EFFECTIVE_START_DATE AND PersonDetailsPEO.EFFECTIVE_END_DATE) 
         AND ( DATE'2023-03-06' BETWEEN AssignmentPEO.EFFECTIVE_START_DATE AND AssignmentPEO.EFFECTIVE_END_DATE)) 
         AND ( ( (AssignmentPEO.EFFECTIVE_LATEST_CHANGE = 'Y' ) ) AND (( ( (AssignmentPEO.ASSIGNMENT_TYPE = 'E' ) ) OR ( (AssignmentPEO.ASSIGNMENT_TYPE = 'C' ) ) OR ( (AssignmentPEO.ASSIGNMENT_TYPE = 'N' ) ) OR ( (AssignmentPEO.ASSIGNMENT_TYPE = 'P' ) ) )) )) V486837407
         , (SELECT /*+ qb_name(CodeCombinationPVO) */ CodeCombination.CHART_OF_ACCOUNTS_ID,     CodeCombination.CODE_COMBINATION_ID,     (FND_FLEX_XML_PUBLISHER_APIS.PROCESS_KFF_COMBINATION_1('FLEXFIELD','GL','GL#',CodeCombination.CHART_OF_ACCOUNTS_ID,NULL,CodeCombination.CODE_COMBINATION_ID,'ALL','Y','VALUE')) AS CONCATENATED_SEG,     (FND_FLEX_XML_PUBLISHER_APIS.PROCESS_KFF_COMBINATION_1('FLEXFIELD','GL','GL#',CodeCombination.CHART_OF_ACCOUNTS_ID,NULL,CodeCombination.CODE_COMBINATION_ID,'ALL','Y','FULL_DESCRIPTION')) AS DESCRIPTION128 FROM GL_CODE_COMBINATIONS CodeCombination) V371846109 WHERE V486837407.DEFAULT_CODE_COMB_ID = V371846109.CODE_COMBINATION_ID(+)) T4422506)
    select distinct 0 as c1,
       D1.c1 as c2,
       D1.c2 as c3,
       D1.c3 as c4
    from 
       SAWITH0 D1
    order by c2
    


  • Trent M
    Trent M ✭✭✭

    Hi Nathan,

    When I created a report within OTBI it would only pull default expense accounts for employees who have submitted expense reports. I now see you were referring to the query. Sorry about that. And thank you for the additional information!

    Thanks,

    Trent