In this article, we will look into the Offerings that client has opted for in cloud applications.
Here are the tables which can get this info:
ASM_FILTERS_VL – Contains feature data
ASM_FILTER_OPTIONS_VL – Contains feature options data
ASM_SVS_FILTER_OPTIONS – Contains user’s opt in data for features
ASM_FILTER_OPTIONS_FILTERS – Contains feature options filters
Table of Contents
SQL Query to get the Opt-In Features by Application:
SELECT FAV.APPLICATION_NAME, AFV.FILTER_NAME Feature, AFV.FILTER_DESC Feature_Descr,
CASE WHEN LENGTH(AFOV.FILTER_OPTION_NAME) <4 THEN
AFOV.FILTER_OPTION_NAME ELSE '' END Feature_Opted,
CASE WHEN LENGTH(AFOV.FILTER_OPTION_NAME) >3 THEN
AFOV.FILTER_OPTION_NAME ELSE '' END Feature_Opted_Choice
FROM
ASM_FILTERS_VL AFV,
ASM_FILTER_OPTIONS_VL AFOV,
ASM_SVS_FILTER_OPTIONS ASFO,
FND_APPLICATION_VL FAV
WHERE AFV.FILTER_ID = AFOV.FILTER_ID
AND AFV.APPLICATION_ID = FAV.APPLICATION_ID
AND ASFO.FILTER_OPTION_ID = AFOV.FILTER_OPTION_ID
order by FAV.APPLICATION_NAME, AFV.FILTER_NAMESample output will look like:
SQL Query to get all features irrespective of whether they are opted or not:
SELECT distinct FAV.APPLICATION_NAME, AFV.FILTER_NAME Feature, AFV.FILTER_DESC Feature_Descr,
CASE WHEN LENGTH(AFOV.FILTER_OPTION_NAME) <4 THEN
AFOV.FILTER_OPTION_NAME ELSE '' END Feature_Opted,
CASE WHEN LENGTH(AFOV.FILTER_OPTION_NAME) >3 THEN
AFOV.FILTER_OPTION_NAME ELSE '' END Feature_Opted_Choice
FROM
ASM_FILTERS_VL AFV,
ASM_FILTER_OPTIONS_VL AFOV,
ASM_SVS_FILTER_OPTIONS ASFO,
FND_APPLICATION_VL FAV
WHERE AFV.FILTER_ID = AFOV.FILTER_ID(+)
AND AFV.APPLICATION_ID = FAV.APPLICATION_ID
AND ASFO.FILTER_OPTION_ID = AFOV.FILTER_OPTION_ID(+)
order by FAV.APPLICATION_NAME, AFV.FILTER_NAME
