How To Build a Custom Report to Display Data Security Condition and Value Associated With DB Resource Conditions? (Doc ID 2809777.1)

Last updated on OCTOBER 03, 2021

Applies to:

Goal

How to build a custom report to display Data Security Condition and Value associated with DB Resource Conditions?

The current Segment Value Security Settings has about 500 filters and condition, the current query does work for less than 260 filters, when going over than the query times out.

Sample of the query used:

SELECT tab.ANo,tab.ATitle,tab.type,tab.val,tab.description
FROM
(WITH A AS (SELECT sets.value_set_id, value.value AS ANo, Value.DESCRIPTION,sets.DATA_SECURITY_OBJECT_NAME
FROM FND_ID_FLEX_SEGMENTS segvalueset,
FND_ID_FLEX_STRUCTURES_VL structure,
FND_VS_VALUES_VL value,
FND_VS_VALUE_SETS sets
WHERE 'N' =
( SELECT fnd_flex_xml_publisher_apis.Process_kff_combination_1( 'FLEX_SECURE', 'GL', 'GL#', structure.id_flex_num, NULL, code.code_combination_id, 'ALL', 'Y', 'SECURITY' )
FROM GL_CODE_COMBINATIONS code
WHERE code.chart_of_accounts_id = structure.id_flex_num AND
ROWNUM = 1 AND
code.segment2 = value.value AND
code.enabled_flag='Y' AND
code.segment1 <> '000000' AND
TRUNC(CAST(FROM_TZ(CAST(SYSDATE AS TIMESTAMP), 'UTC') at time zone 'US/Eastern' AS Date)) BETWEEN NVL(Code.START_DATE_ACTIVE, TRUNC(SYSDATE - 1)) AND NVL(Code.END_DATE_ACTIVE, TRUNC(SYSDATE + 1))
) AND
sets.value_set_id = value.value_set_id AND
sets.value_set_code = 'A Value Set' AND
sets.enterprise_id = '1' AND
sets.sandbox_id = '1' AND
segvalueset.id_flex_code = 'GL#' AND
structure.id_flex_code = 'GL#' AND
structure.id_flex_structure_code = 'COA STRUCT CODE' AND
segvalueset.id_flex_num = structure.id_flex_num AND
segvalueset.application_column_name = 'SEGMENT2' AND
value.value_set_id = segvalueset.flex_value_set_id AND
segvalueset.application_id = '101' AND
( value.value IN ( :P_A ) OR
Least( :P_A ) IS NULL )
) ,
Security AS
(SELECT *
FROM (select substr(predicate.instance_set_name, instr(predicate.instance_set_name,'_',1,4)+1,4) agency
,obj_tl.display_name
,extractValue(predicate.filter,'FndFilter/GenericFilter/filterCriteriaRow/filterCriteriaItem[1]/value') Filter1
,extractValue(predicate.filter,'FndFilter/GenericFilter/filterCriteriaRow/filterCriteriaItem[2]/value') Filter2
....
,extractValue(predicate.filter,'FndFilter/GenericFilter/filterCriteriaRow/filterCriteriaItem[259]/value') Filter259
,extractValue(predicate.filter,'FndFilter/GenericFilter/filterCriteriaRow/filterCriteriaItem[260]/value') Filter260
from fusion.fnd_object_instance_sets predicate,
fusion.fnd_objects_tl obj_tl
where predicate.instance_set_name like 'ABCDE%'
and predicate.object_id = obj_tl.object_id
and obj_tl.display_name like 'XX%'
)
UNPIVOT (Val FOR ROLE IN (filter1,filter2,filter3,filter4,filter5,filter6,filter7,filter8,filter9,filter10,filter11,filter12,filter13,filter14,filter15,filter16,filter17,filter18,filter19,
filter20,filter21,filter22,filter23,filter24,filter25,filter26,filter27,filter28,filter29,filter30,filter31,filter32,filter33,filter34,filter35,filter36,filter37,filter38,filter39,
...
filter230,filter231,filter232,filter233,filter234,filter235,filter236,filter237,filter238,filter239,filter240,filter241,filter242,filter243,filter244,filter245,filter246,filter247,filter248,filter249,
filter250,filter251,filter252,filter253,filter254,filter255,filter256,filter257,filter258,filter259,filter260)))
SELECT ANo,Description ATitle,
DECODE (security.display_name, 'XX_ABC_VALUE_SECURITY','DEP - CASH & CHECK',
'XX_ABC_VALUE_VALUE_SECURITY','DEP - CREDIT CARD',
....) Type,
Security.val,
(SELECT ffvt.description
FROM
fnd_vs_value_sets fvvs,
fnd_flex_value_sets ffvs ,
fnd_flex_values ffv,
fnd_flex_values_tl ffvt
Where 1=1
AND fvvs.DATA_SECURITY_OBJECT_NAME = security.display_name
AND ffvs.flex_value_set_id = fvvs.value_set_id
AND ffvs.flex_value_set_id = ffv.flex_value_set_id
and ffv.flex_value_id = ffvt.flex_value_id
AND ffvt.language = USERENV('LANG')
AND flex_value = TRIM(Security.val)) as description
FROM Security, A
WHERE A.ANo = TRIM(Security.A)) tab
Order by ANo,Type,Val

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.