Payroll and Compensation Subject Areas

Harminder Banga-283414

Within OTBI Reporting, the Payroll and Compensation Subject Areas no longer show any data.

On 24C

Nathan CCC

Hi, Or perhaps the subject areas DO show data! But it no longer shows data for your user with your roles with your setup by security context in manage data access for users and/or you security profile in a data role. To view the data security go to page issue SQL then manage sessions to view log to read the where clause added for your user at runtime?

Harminder Banga-283414

@Nathan CCC - Had worked previously without any change needed to roles before 24C

So I don't feel that is the case - however can you provide navigation to the menu options you have highlighted in bold

Nathan CCC

Hi,

To know what job roles are in play for a subject area go to docs.oracle.com.

For example, https://docs.oracle.com/en/cloud/saas/human-resources/24c/faohb/Compensation--Workforce-Assignment-Salary-Real-Tim-SA-118.html#Compensation__Workforce_Assignment_Salary_Real_Tim_SA_118_d1e8523

shows us that for subject area

Compensation - Workforce Assignment Salary Real Time

the job roles are

• Compensation Analyst
• Compensation Manager
• Human Resource Analyst
• Line Manager

Next for the job role you are using as IT Security Manager in Tools go to work area Security Console to view the Data Security Policies. One or more of these may be getting used at run time by OTBI.

If the subject area is FSCM then in My enterprise go to work area Setup and Maintenance then search for task Manage Data Access for Users. This tells you what data security you have setup.

If the subject area is HCM then in My enterprise go to work area Setup and Maintenance then search for task Manage Data Role and Security Profiles. A data role will inherit a job role. The data role will have security policies for public person etc. This tells you what data security you have setup.

To debug…

Ask your BI Administrator to use page Manage Privileges /analytics/saw.dll?Admin to grant to your user or a role your user has granted direct or inherited the privileges Issue SQL Directly and Manage Sessions

Next you sign in to OTBI to go to page Issue SQL /saw.dll?issuerawsql

cut paste the logical SQL from the advanced tab of your analysis then select Issue SQL

At the end of the results select View Log to go to log in page Manage Sessions.

In here will be one or more physical SQL statements typically begin WITH … Cut paste these into your favourite colourful editor to read the SQL.

You are looking for the where clauses added at runtime to implement data security in this session.

For example, 1=2 is false so I get no data because I do not have the required roles and data security setup

… FROM CMP_ASG_SAL_REPORTING_HIST_V CmpAsgSalReportingPEO

WHERE (( DATE'2024-09-04'  BETWEEN CmpAsgSalReportingPEO.ANCHOR_START_DATE AND CmpAsgSalReportingPEO.ANCHOR_END_DATE)) 

AND ((1=2))) V361113754

But if I grant the user a data role with for example a security profile to view all persons which inherit the required job role then this time the where clause work to give me the total salary amount of money

…FROM CMP_ASG_SAL_REPORTING_HIST_V CmpAsgSalReportingPEO

WHERE (( DATE'2024-09-04'  BETWEEN CmpAsgSalReportingPEO.ANCHOR_START_DATE AND CmpAsgSalReportingPEO.ANCHOR_END_DATE))

AND ((((EXISTS(SELECT 1 FROM PER_ALL_ASSIGNMENTS_M ASG, PER_PERIODS_OF_SERVICE PS

WHERE ASG.ASSIGNMENT_TYPE IN('E','C','P','N')

AND ASG.EFFECTIVE_LATEST_CHANGE='Y'

AND TRUNC(SYSDATE) < ASG.EFFECTIVE_END_DATE

AND PS.PERIOD_OF_SERVICE_ID =ASG.PERIOD_OF_SERVICE_ID

AND (ASG.ASSIGNMENT_STATUS_TYPE IN ('ACTIVE','SUSPENDED') OR (ASG.ASSIGNMENT_STATUS_TYPE  IN ('INACTIVE')

AND NOT EXISTS (SELECT 1 FROM PER_ALL_ASSIGNMENTS_M EXASG WHERE EXASG.ASSIGNMENT_TYPE IN('E','C','N','P') AND EXASG.EFFECTIVE_LATEST_CHANGE = 'Y' AND EXASG.PERSON_ID = ASG.PERSON_ID AND SYSDATE BETWEEN LEAST(SYSDATE,EXASG.EFFECTIVE_START_DATE) AND EXASG.EFFECTIVE_END_DATE AND EXASG.ASSIGNMENT_STATUS_TYPE IN ('ACTIVE','SUSPENDED'))

AND PS.ACTUAL_TERMINATION_DATE = (SELECT MAX(ALLPS.ACTUAL_TERMINATION_DATE) FROM PER_PERIODS_OF_SERVICE ALLPS WHERE ALLPS.PERSON_ID = ASG.PERSON_ID AND ALLPS.PERIOD_TYPE IN('E','C','N','P') AND ALLPS.ACTUAL_TERMINATION_DATE IS NOT NULL)))

AND ASG.PERSON_ID=CmpAsgSalReportingPEO.PERSON_ID AND EXISTS(SELECT 1 FROM PER_DEPT_TREE_NODE_RF TRNF WHERE TO_NUMBER(TRNF.PK1_VALUE)=ASG.ORGANIZATION_ID AND TRNF.TREE_VERSION_ID IN (SELECT FTV.TREE_VERSION_ID FROM FND_TREE_VERSION FTV,PER_ASG_RESPONSIBILITIES RES WHERE FTV.TREE_CODE=RES.DEPARTMENT_TREE_CODE AND FTV.TREE_STRUCTURE_CODE='PER_DEPT_TREE_STRUCTURE' AND FTV.STATUS='ACTIVE' AND TRUNC(SYSDATE) BETWEEN FTV.EFFECTIVE_START_DATE AND FTV.EFFECTIVE_END_DATE AND RES.RESPONSIBILITY_TYPE='FD_BUS_MGR_SEC' AND RES.PERSON_ID=(SELECT NVL(HRC_SESSION_UTIL.GET_USER_PERSONID,-1) FROM DUAL) AND SYSDATE BETWEEN RES.START_DATE AND NVL(RES.END_DATE,SYSDATE) AND TO_NUMBER(TRNF.ANCESTOR_PK1_VALUE)=NVL(RES.TOP_DEPARTMENT_ID,TO_NUMBER(TRNF.ANCESTOR_PK1_VALUE)) AND TRNF.DISTANCE>DECODE(RES.INCLUDE_TOP_HIER_NODE,'N',0,(TRNF.DISTANCE-1)) AND TRNF.DISTANCE<NVL(RES.HIERARCHY_LEVELS,TRNF.DISTANCE+1)  ))   )

OR ((SELECT NVL(HRC_SESSION_UTIL.GET_USER_PERSONID,-1) FROM DUAL) = CmpAsgSalReportingPEO.PERSON_ID) ))))) V361113754