VIEW: APPS.IBW_BI_MSITE_DIMN_V

Source

View Text - Preformatted

SELECT site.msite_id id ,site.msite_name value ,NVL(site.site_type, ibw_bi_utl_pvt.get_lookups_mng('IBW_SITE_TYPE','I')) site_type FROM ibe_bi_msiteorg_mv siteorg ,ibe_msites_vl site WHERE siteorg.msite_id = site.msite_id AND site.reporting_status = 'Y' AND NOT EXISTS ( SELECT siteorg.org_id FROM ibe_bi_msiteorg_mv siteorg1 WHERE siteorg1.msite_id = siteorg.msite_id AND NOT EXISTS ( SELECT 'x' FROM per_organization_list org WHERE org.organization_id = siteorg1.org_id and org.security_profile_id = fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL')) ) GROUP BY site.msite_id,site.msite_name,NVL(site.site_type, ibw_bi_utl_pvt.get_lookups_mng('IBW_SITE_TYPE','I')) UNION ALL SELECT -1 id , ibw_bi_utl_pvt.get_lookups_mng('IBW_GEN_LOOKUP','UNA') value ,'I' site_type FROM dual

View Text - HTML Formatted

SELECT SITE.MSITE_ID ID
, SITE.MSITE_NAME VALUE
, NVL(SITE.SITE_TYPE
, IBW_BI_UTL_PVT.GET_LOOKUPS_MNG('IBW_SITE_TYPE'
, 'I')) SITE_TYPE
FROM IBE_BI_MSITEORG_MV SITEORG
, IBE_MSITES_VL SITE
WHERE SITEORG.MSITE_ID = SITE.MSITE_ID
AND SITE.REPORTING_STATUS = 'Y'
AND NOT EXISTS ( SELECT SITEORG.ORG_ID
FROM IBE_BI_MSITEORG_MV SITEORG1
WHERE SITEORG1.MSITE_ID = SITEORG.MSITE_ID
AND NOT EXISTS ( SELECT 'X'
FROM PER_ORGANIZATION_LIST ORG
WHERE ORG.ORGANIZATION_ID = SITEORG1.ORG_ID
AND ORG.SECURITY_PROFILE_ID = FND_PROFILE.VALUE('XLA_MO_SECURITY_PROFILE_LEVEL')) ) GROUP BY SITE.MSITE_ID
, SITE.MSITE_NAME
, NVL(SITE.SITE_TYPE
, IBW_BI_UTL_PVT.GET_LOOKUPS_MNG('IBW_SITE_TYPE'
, 'I')) UNION ALL SELECT -1 ID
, IBW_BI_UTL_PVT.GET_LOOKUPS_MNG('IBW_GEN_LOOKUP'
, 'UNA') VALUE
, 'I' SITE_TYPE
FROM DUAL