• Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:March 17, 2022
  • Reading time:3 mins read
You are currently viewing How to get the Offerings and Opt-In features in Cloud applications?
How to get the Offerings and Opt-In features in Cloud applications?

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_NAME

Sample output will look like:

image 1 - How to get the Offerings and Opt-In features in Cloud applications?

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
If you like the content, please follow us on LinkedInFacebook, and Twitter to get updated with the latest content.