VIEW: APPS.IBW_SITE_CURRENCY_V

Source

View Text - Preformatted

SELECT TO_CHAR(a.msite_id) msiteid, a.currency_code currency_code from ibe_bi_currency_v a where not exists (select b.org_id from ibe_bi_msiteorg_mv b where b.msite_id = a.msite_id AND NOT EXISTS ( SELECT 'x' FROM per_organization_list a WHERE a.organization_id = b.org_id AND a.security_profile_id = fnd_profile.VALUE ('XLA_MO_SECURITY_PROFILE_LEVEL'))) union all select null, v.id from fii_currencies_v v, (select bis_common_parameters.get_currency_code p_code, bis_common_parameters.get_rate_type p_rate, bis_common_parameters.get_secondary_currency_code s_code, bis_common_parameters.get_secondary_rate_type s_rate from dual) s where v.id = 'FII_GLOBAL1' OR ( v.id = 'FII_GLOBAL2' AND s.s_code is not null AND s.s_rate is not null AND NOT (s.p_code = s.s_code AND s.p_rate = s.s_rate)) union all select 'ALL', s.currency_code FROM (SELECT DECODE(COUNT(distinct f_curr.currency_code ) OVER (),1,f_curr.currency_code,null) CURRENCY_CODE FROM ibe_bi_currency_v f_curr WHERE not exists (select b.org_id from ibe_bi_msiteorg_mv b where b.msite_id = f_Curr.msite_id AND NOT EXISTS ( SELECT 'x' FROM per_organization_list a WHERE a.organization_id = b.org_id AND a.security_profile_id = fnd_profile.VALUE ('XLA_MO_SECURITY_PROFILE_LEVEL')))) S WHERE ROWNUM = 1

View Text - HTML Formatted

SELECT TO_CHAR(A.MSITE_ID) MSITEID
, A.CURRENCY_CODE CURRENCY_CODE
FROM IBE_BI_CURRENCY_V A
WHERE NOT EXISTS (SELECT B.ORG_ID
FROM IBE_BI_MSITEORG_MV B
WHERE B.MSITE_ID = A.MSITE_ID
AND NOT EXISTS ( SELECT 'X'
FROM PER_ORGANIZATION_LIST A
WHERE A.ORGANIZATION_ID = B.ORG_ID
AND A.SECURITY_PROFILE_ID = FND_PROFILE.VALUE ('XLA_MO_SECURITY_PROFILE_LEVEL'))) UNION ALL SELECT NULL
, V.ID
FROM FII_CURRENCIES_V V
, (SELECT BIS_COMMON_PARAMETERS.GET_CURRENCY_CODE P_CODE
, BIS_COMMON_PARAMETERS.GET_RATE_TYPE P_RATE
, BIS_COMMON_PARAMETERS.GET_SECONDARY_CURRENCY_CODE S_CODE
, BIS_COMMON_PARAMETERS.GET_SECONDARY_RATE_TYPE S_RATE
FROM DUAL) S
WHERE V.ID = 'FII_GLOBAL1' OR ( V.ID = 'FII_GLOBAL2'
AND S.S_CODE IS NOT NULL
AND S.S_RATE IS NOT NULL
AND NOT (S.P_CODE = S.S_CODE
AND S.P_RATE = S.S_RATE)) UNION ALL SELECT 'ALL'
, S.CURRENCY_CODE
FROM (SELECT DECODE(COUNT(DISTINCT F_CURR.CURRENCY_CODE ) OVER ()
, 1
, F_CURR.CURRENCY_CODE
, NULL) CURRENCY_CODE
FROM IBE_BI_CURRENCY_V F_CURR
WHERE NOT EXISTS (SELECT B.ORG_ID
FROM IBE_BI_MSITEORG_MV B
WHERE B.MSITE_ID = F_CURR.MSITE_ID
AND NOT EXISTS ( SELECT 'X'
FROM PER_ORGANIZATION_LIST A
WHERE A.ORGANIZATION_ID = B.ORG_ID
AND A.SECURITY_PROFILE_ID = FND_PROFILE.VALUE ('XLA_MO_SECURITY_PROFILE_LEVEL')))) S
WHERE ROWNUM = 1