How to restrict the PPM contract data based on the roles of the user logged in in OTBI Dashboard

Abhilash K - Deloitte USI

Summary:

We are trying to restrict the data based on the roles available for the user logged in specifically Principal Investigator role (PI role).

Content (required):

Version (include the version you are using, if applicable):

Oracle Cloud Application 22A (11.13.22.01.0)

Code Snippet (add any code snippets that support your topic, if applicable):

SET VARIABLE PREFERRED_CURRENCY='User Preferred Currency 1';SELECT

  0 s_0,

  "Projects - Cross Subject Area Analysis - Real Time"."- Project Type Attributes"."Project Type Name" s_1,

  "Projects - Cross Subject Area Analysis - Real Time"."Award Funding Source"."Funding Source Name" s_2,

  "Projects - Cross Subject Area Analysis - Real Time"."Award"."Award End Date" s_3,

  "Projects - Cross Subject Area Analysis - Real Time"."Award"."Award Name" s_4,

  "Projects - Cross Subject Area Analysis - Real Time"."Award"."Award Number" s_5,

  "Projects - Cross Subject Area Analysis - Real Time"."Award"."Award Start Date" s_6,

  "Projects - Cross Subject Area Analysis - Real Time"."Award"."Principal Investigator" s_7,

  "Projects - Cross Subject Area Analysis - Real Time"."Project"."Current Project Manager" s_8,

  "Projects - Cross Subject Area Analysis - Real Time"."Project"."Project Name" s_9,

  "Projects - Cross Subject Area Analysis - Real Time"."Project"."Project Number" s_10,

  "Projects - Cross Subject Area Analysis - Real Time"."Resource"."Expenditure Organization Name" s_11,

  "Projects - Cross Subject Area Analysis - Real Time"."Resource"."Project Owning Organization Name" s_12,

  "Projects - Cross Subject Area Analysis - Real Time"."Task"."Task Name" s_13,

  "Projects - Cross Subject Area Analysis - Real Time"."Task"."Task Number" s_14,

  'Dummy' s_15,

  DESCRIPTOR_IDOF("Projects - Cross Subject Area Analysis - Real Time"."- Project Type Attributes"."Project Type Name") s_16,

  DESCRIPTOR_IDOF("Projects - Cross Subject Area Analysis - Real Time"."Resource"."Expenditure Organization Name") s_17,

  DESCRIPTOR_IDOF("Projects - Cross Subject Area Analysis - Real Time"."Resource"."Project Owning Organization Name") s_18,

  DESCRIPTOR_IDOF("Projects - Cross Subject Area Analysis - Real Time"."Task"."Task Number") s_19,

  ifnull("Projects - Cross Subject Area Analysis - Real Time"."- Budget Cost Measures"."Current Budget Cost",0) s_20,

  ifnull("Projects - Cross Subject Area Analysis - Real Time"."- Budget Cost Measures"."Current Budget Cost",0)-ifnull("Projects - Cross Subject Area Analysis - Real Time"."- Commitment Cost Measures"."Total Committed Cost",0)-ifnull("Projects - Cross Subject Area Analysis - Real Time"."Cost Transaction Measures"."Raw Cost",0) s_21,

  ifnull("Projects - Cross Subject Area Analysis - Real Time"."- Commitment Cost Measures"."Total Committed Cost",0) s_22,

  ifnull("Projects - Cross Subject Area Analysis - Real Time"."Cost Transaction Measures"."Raw Cost",0) s_23,

  REPORT_AGGREGATE(ifnull("Projects - Cross Subject Area Analysis - Real Time"."- Budget Cost Measures"."Current Budget Cost",0) BY DESCRIPTOR_IDOF("Projects - Cross Subject Area Analysis - Real Time"."Resource"."Project Owning Organization Name"),"Projects - Cross Subject Area Analysis - Real Time"."Award"."Award Number","Projects - Cross Subject Area Analysis - Real Time"."Award"."Award Name","Projects - Cross Subject Area Analysis - Real Time"."Award"."Award Start Date","Projects - Cross Subject Area Analysis - Real Time"."Award"."Award End Date","Projects - Cross Subject Area Analysis - Real Time"."Award"."Principal Investigator","Projects - Cross Subject Area Analysis - Real Time"."Project"."Project Name","Projects - Cross Subject Area Analysis - Real Time"."Project"."Project Number","Projects - Cross Subject Area Analysis - Real Time"."Project"."Current Project Manager",DESCRIPTOR_IDOF("Projects - Cross Subject Area Analysis - Real Time"."- Project Type Attributes"."Project Type Name"),DESCRIPTOR_IDOF("Projects - Cross Subject Area Analysis - Real Time"."Task"."Task Name"),"Projects - Cross Subject Area Analysis - Real Time"."Award Funding Source"."Funding Source Name") s_24,

  REPORT_AGGREGATE(ifnull("Projects - Cross Subject Area Analysis - Real Time"."- Budget Cost Measures"."Current Budget Cost",0)-ifnull("Projects - Cross Subject Area Analysis - Real Time"."- Commitment Cost Measures"."Total Committed Cost",0)-ifnull("Projects - Cross Subject Area Analysis - Real Time"."Cost Transaction Measures"."Raw Cost",0) BY DESCRIPTOR_IDOF("Projects - Cross Subject Area Analysis - Real Time"."Resource"."Project Owning Organization Name"),"Projects - Cross Subject Area Analysis - Real Time"."Award"."Award Number","Projects - Cross Subject Area Analysis - Real Time"."Award"."Award Name","Projects - Cross Subject Area Analysis - Real Time"."Award"."Award Start Date","Projects - Cross Subject Area Analysis - Real Time"."Award"."Award End Date","Projects - Cross Subject Area Analysis - Real Time"."Award"."Principal Investigator","Projects - Cross Subject Area Analysis - Real Time"."Project"."Project Name","Projects - Cross Subject Area Analysis - Real Time"."Project"."Project Number","Projects - Cross Subject Area Analysis - Real Time"."Project"."Current Project Manager",DESCRIPTOR_IDOF("Projects - Cross Subject Area Analysis - Real Time"."- Project Type Attributes"."Project Type Name"),DESCRIPTOR_IDOF("Projects - Cross Subject Area Analysis - Real Time"."Task"."Task Name"),"Projects - Cross Subject Area Analysis - Real Time"."Award Funding Source"."Funding Source Name") s_25,

  REPORT_AGGREGATE(ifnull("Projects - Cross Subject Area Analysis - Real Time"."- Commitment Cost Measures"."Total Committed Cost",0) BY DESCRIPTOR_IDOF("Projects - Cross Subject Area Analysis - Real Time"."Resource"."Project Owning Organization Name"),"Projects - Cross Subject Area Analysis - Real Time"."Award"."Award Number","Projects - Cross Subject Area Analysis - Real Time"."Award"."Award Name","Projects - Cross Subject Area Analysis - Real Time"."Award"."Award Start Date","Projects - Cross Subject Area Analysis - Real Time"."Award"."Award End Date","Projects - Cross Subject Area Analysis - Real Time"."Award"."Principal Investigator","Projects - Cross Subject Area Analysis - Real Time"."Project"."Project Name","Projects - Cross Subject Area Analysis - Real Time"."Project"."Project Number","Projects - Cross Subject Area Analysis - Real Time"."Project"."Current Project Manager",DESCRIPTOR_IDOF("Projects - Cross Subject Area Analysis - Real Time"."- Project Type Attributes"."Project Type Name"),DESCRIPTOR_IDOF("Projects - Cross Subject Area Analysis - Real Time"."Task"."Task Name"),"Projects - Cross Subject Area Analysis - Real Time"."Award Funding Source"."Funding Source Name") s_26,

  REPORT_AGGREGATE(ifnull("Projects - Cross Subject Area Analysis - Real Time"."Cost Transaction Measures"."Raw Cost",0) BY DESCRIPTOR_IDOF("Projects - Cross Subject Area Analysis - Real Time"."Resource"."Project Owning Organization Name"),"Projects - Cross Subject Area Analysis - Real Time"."Award"."Award Number","Projects - Cross Subject Area Analysis - Real Time"."Award"."Award Name","Projects - Cross Subject Area Analysis - Real Time"."Award"."Award Start Date","Projects - Cross Subject Area Analysis - Real Time"."Award"."Award End Date","Projects - Cross Subject Area Analysis - Real Time"."Award"."Principal Investigator","Projects - Cross Subject Area Analysis - Real Time"."Project"."Project Name","Projects - Cross Subject Area Analysis - Real Time"."Project"."Project Number","Projects - Cross Subject Area Analysis - Real Time"."Project"."Current Project Manager",DESCRIPTOR_IDOF("Projects - Cross Subject Area Analysis - Real Time"."- Project Type Attributes"."Project Type Name"),DESCRIPTOR_IDOF("Projects - Cross Subject Area Analysis - Real Time"."Task"."Task Name"),"Projects - Cross Subject Area Analysis - Real Time"."Award Funding Source"."Funding Source Name") s_27

FROM "Projects - Performance Reporting Real Time"

WHERE

(("Projects - Cross Subject Area Analysis - Real Time"."Time"."Date" BETWEEN date '1900-02-02' AND date '2022-05-05') AND (("Projects - Cross Subject Area Analysis - Real Time"."Award"."Principal Investigator" IN (VALUEOF(NQ_SESSION."DISPLAYNAME"))) OR ("Projects - Cross Subject Area Analysis - Real Time"."Project"."Current Project Manager" IN (VALUEOF(NQ_SESSION."DISPLAYNAME")))))

ORDER BY 12 ASC NULLS LAST, 18 ASC NULLS LAST, 13 ASC NULLS LAST, 19 ASC NULLS LAST, 6 ASC NULLS LAST, 5 ASC NULLS LAST, 7 ASC NULLS LAST, 4 ASC NULLS LAST, 8 ASC NULLS LAST, 10 ASC NULLS LAST, 11 ASC NULLS LAST, 9 ASC NULLS LAST, 16 ASC NULLS LAST, 2 ASC NULLS LAST, 17 ASC NULLS LAST, 14 ASC NULLS LAST, 20 ASC NULLS LAST, 15 ASC NULLS LAST, 3 ASC NULLS LAST

FETCH FIRST 75001 ROWS ONLY

Gaurav Bharadwaj-Oracle

Hi, OTBI follows the data security which is set for the user in the Fusion Application. There is no Security Configuration needed/available in OTBI to secure the Data.

Vijay Ramani VJ

As Gaurav mentioned, please make sure the role user has the appropriate data access sets assigned and that should take care!

Shashank Verma-Oracle

As Mentioned above. OTBI will retrieve the output only if the User have the required data access.

For Example If a User have Excess to One Business unit, He will be able to see only that particular Business Units data in OTBI output.

Abhilash K - Deloitte USI

We were able to use a custom sql analysis to meet the business need, thanks for all the inputs,

Regards

Abhilash

Naresh313

Hi Abhilash,

Is it possible to share the SQL used to achieve this Scenario?