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

Bhaskar Konar · Mar 2, 2023 5:49PM

Glad you found it useful.

Appreciate your time & patience.

Cheers,

Nathan CCC · Mar 6, 2023 9:33AM

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 · Mar 6, 2023 2:32PM

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 · Mar 6, 2023 5:41PM

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 · Mar 6, 2023 8:00PM

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

Karen Wilson · Apr 16, 2024 3:00PM

Is it possible to add the assignment number to the above report so we can see all the default expense codes for all current assignments, whether expenses have been claimed or not?

Bhaskar Konar · Apr 17, 2024 6:20AM

Hi @Karen Wilson,

Please find below SQL with Assignment Number:

SELECT ppf.person_number,

ppnf.display_name,

pf.assignment_number,

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,

Karen Wilson · Apr 17, 2024 9:48AM

Thanks Bhaskar Konar, that worked perfectly

Emily Cikovsky-Oracle · Apr 17, 2024 6:59PM

Glad it worked! @Bhaskar Konar, thank you for the info!

@Karen Wilson, would you be willing to accept the answer? Just click Yes to "Did this answer the question?", above. That will raise its status in the community and make it easier for people to find :). Thanks so much!

MFA for Oracle Community

Oracle Transactional Business Intelligence

Categories

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

Answers