Issue with Multiple Custom Person Security Profiles in FDI Semantic Model

Swati Sontakke

I'm implementing custom person security in Fusion HCM using the guidance from this blog: https://blogs.oracle.com/analytics/post/fdi-data-security-implementation-of-fusion-hcm-person-security-profile-custom-criteria-in-fdi .

In my use case, I need to implement two separate custom security views for two different application roles. I followed the exact same steps as outlined in the blog, and each custom security configuration works perfectly fine when deployed individually (i.e., when only one application role is merged in the main sandbox).

However, the issue arises when I include both application roles and merge the changes in the main sandbox, only one of the custom security filters works as intended — or in some cases, neither seems to be applied correctly.

I've confirmed that the roles function properly when applied separately, so the issue seems to be linked to having both roles active at the same time in the model.

Below are the two custom views I'm using for the person security profile. As you’ll see, the views are almost identical, with only minor differences in the filtering logic:

View 1: X_HCM_VIEW_ALL_EXCLUDING_HR_SECURITY_VW

SELECT

   asg.person_id,

   asg.worker_type,

   asg.department_id,

   dept_dff.org_hrdept_

   FROM   

   oax$oac.dw_wrkfrc_asg_f              asg,   

   oax$oac.dw_department_d              dept,   

   oax$oac.dw_per_organization_unit_dff dept_dffWHERE   

   asg.department_id = dept.department_id   

   AND

   dept.department_id = dept_dff.s_k_5000   

   AND asg.worker_type IN ( 'E', 'C', 'N', 'P' )   

   AND asg.effective_latest_change = 'Y'   

   AND trunc(sysdate) BETWEEN asg.effective_start_date AND asg.effective_end_date   

   AND trunc(sysdate) BETWEEN dept.effective_start_date AND dept.effective_end_date   

   AND nvl(dept_dff.org_hrdept_, 'N') != 'Y';

View 2: X_HCM_VIEW_ALL_EXCLUDING_HR_AND_EGM_SL_SECURITY_VW

SELECT   

asg.person_id,   

asg.worker_type,   

asg.department_id,   

dept_dff.org_hrdept_,   

pg.grade_id,   

pg.grade_code

FROM   

oax$oac.dw_wrkfrc_asg_f              asg,   

oax$oac.dw_department_d              dept,   

oax$oac.dw_per_organization_unit_dff dept_dff,   

oax$oac.dw_pay_grade_d               pg

WHERE   

asg.department_id = dept.department_id   

AND dept.department_id = dept_dff.s_k_5000   

AND asg.grade_id = pg.grade_id   

AND asg.worker_type IN ( 'E', 'C', 'N', 'P' )   

AND asg.effective_latest_change = 'Y'   

AND trunc(sysdate) BETWEEN asg.effective_start_date AND asg.effective_end_date   

AND trunc(sysdate) BETWEEN dept.effective_start_date AND dept.effective_end_date   

AND trunc(sysdate) BETWEEN pg.effective_start_date AND pg.effective_end_date   

AND nvl(dept_dff.org_hrdept_, 'N') != 'Y'   

AND pg.grade_code NOT IN ( 'L4', 'L5', 'L6', 'ZZ_L4', 'ZZ_L5', 'ZZ_L6' );

Key Differences Between the Views:

1. Additional Join: The second view includes an additional join to the oax$oac.dw_pay_grade_d table via asg.grade_id = pg.grade_id.
2. Additional Filters:
   • It filters out certain pay grades using pg.grade_code NOT IN (...).
   • It includes the pay grade effective date condition: trunc(sysdate) BETWEEN pg.effective_start_date AND pg.effective_end_date.

These additions are specific to exclude certain leadership roles based on grade codes, while both views share the same core structure, especially around filtering HR departments using the DFF column org_hrdept_.

Both application roles (with their respective custom views) are configured in the security filters and assigned to different users for testing. As long as only one role is merged in the main sandbox, the filtering works. But once both are present, the security behaviour breaks down.

Has anyone encountered a similar issue with multiple custom person security profiles in FDI? Any guidance or best practices to correctly implement multiple custom security views within the same semantic model would be greatly appreciated.

Thanks,
Swati