You're almost there! Please answer a few more questions for access to the Applications content. Complete registration
Interested in joining? Complete your registration by providing Areas of Interest here. Register

Custom Criteria Person Security Profile

in Reporting and Analytics for HCM

Summary:

Hi all

I've created a custom role and created a Person Security Profile which is secured by Area Of Responsibility, and the scope of responsibility is Organization Hierarchy for Department. However it seems that only active employees are visable in my OTBI Reports.

Here is my Profile Predicates:

Person Access:
(EXISTS(SELECT 1 FROM PER_ALL_ASSIGNMENTS_M ASG WHERE ASG.ASSIGNMENT_TYPE IN('E','C','P','N') AND ASG.EFFECTIVE_LATEST_CHANGE='Y' AND TRUNC(SYSDATE) < ASG.EFFECTIVE_END_DATE AND ASG.PERSON_ID=&TABLE_ALIAS.PERSON_ID AND EXISTS(SELECT 1 FROM PER_ORG_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.ORGANIZATION_TREE_CODE AND FTV.TREE_STRUCTURE_CODE='PER_ORG_TREE_STRUCTURE' AND FTV.STATUS='ACTIVE' AND TRUNC(SYSDATE) BETWEEN FTV.EFFECTIVE_START_DATE AND FTV.EFFECTIVE_END_DATE AND RES.RESPONSIBILITY_TYPE='SHR_FTE' 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_ORGANIZATION_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) = &TABLE_ALIAS.PERSON_ID) )

Tagged:

Howdy, Stranger!

Log In

To view full details, sign in.

Register

Don't have an account? Click here to get started!