VIEW: APPS.IBU_OSS_RPT_DATA_OUT

Source

View Text - Preformatted

SELECT trunc(first_connect) as Action_Date,'Unique Visitors Today' as Metric, count(distinct ICX_SESSIONS.user_id) as total_count FROM ICX_SESSIONS WHERE responsibility_application_id = 672 AND TRUNC(FIRST_CONNECT)>=TRUNC(SYSDATE)-30 GROUP BY trunc(first_connect) UNION SELECT trunc(first_connect) as Action_date,'Total Logons Today' as Metric, count(session_id) as total_count from ICX_SESSIONS where responsibility_application_id = 672 AND TRUNC(FIRST_CONNECT)>=TRUNC(SYSDATE)-30 group by trunc(first_connect) UNION select trunc(fnd_user_resp_groups.creation_date) as Action_date, 'Newly Registered Users Today' as Metric, count(distinct fnd_user.user_id) as total_count from fnd_user, FND_USER_RESP_GROUPS, fnd_responsibility where fnd_user.user_id = FND_USER_RESP_GROUPS.user_id and FND_USER_RESP_GROUPS.responsibility_id = fnd_responsibility.responsibility_id and responsibility_application_id=672 and FND_USER_RESP_GROUPS.created_by <> 1 AND trunc(fnd_user_resp_groups.creation_date) >=TRUNC(SYSDATE)-30 group by trunc(fnd_user_resp_groups.creation_date) union select trunc(sysdate),'Unique Visitors To-date' as prompt1, count(distinct ICX_SESSIONS.user_id) as total_count from ICX_SESSIONS where responsibility_application_id = 672 union select trunc(sysdate), 'Total Registered Users' as prompt1, count(distinct fnd_user.user_id) as total_count from fnd_user, FND_USER_RESP_GROUPS, fnd_responsibility where fnd_user.user_id = FND_USER_RESP_GROUPS.user_id and FND_USER_RESP_GROUPS.responsibility_id = fnd_responsibility.responsibility_id and responsibility_application_id=672

View Text - HTML Formatted

SELECT TRUNC(FIRST_CONNECT) AS ACTION_DATE
, 'UNIQUE VISITORS TODAY' AS METRIC
, COUNT(DISTINCT ICX_SESSIONS.USER_ID) AS TOTAL_COUNT
FROM ICX_SESSIONS
WHERE RESPONSIBILITY_APPLICATION_ID = 672
AND TRUNC(FIRST_CONNECT)>=TRUNC(SYSDATE)-30 GROUP BY TRUNC(FIRST_CONNECT) UNION SELECT TRUNC(FIRST_CONNECT) AS ACTION_DATE
, 'TOTAL LOGONS TODAY' AS METRIC
, COUNT(SESSION_ID) AS TOTAL_COUNT
FROM ICX_SESSIONS
WHERE RESPONSIBILITY_APPLICATION_ID = 672
AND TRUNC(FIRST_CONNECT)>=TRUNC(SYSDATE)-30 GROUP BY TRUNC(FIRST_CONNECT) UNION SELECT TRUNC(FND_USER_RESP_GROUPS.CREATION_DATE) AS ACTION_DATE
, 'NEWLY REGISTERED USERS TODAY' AS METRIC
, COUNT(DISTINCT FND_USER.USER_ID) AS TOTAL_COUNT
FROM FND_USER
, FND_USER_RESP_GROUPS
, FND_RESPONSIBILITY
WHERE FND_USER.USER_ID = FND_USER_RESP_GROUPS.USER_ID
AND FND_USER_RESP_GROUPS.RESPONSIBILITY_ID = FND_RESPONSIBILITY.RESPONSIBILITY_ID
AND RESPONSIBILITY_APPLICATION_ID=672
AND FND_USER_RESP_GROUPS.CREATED_BY <> 1
AND TRUNC(FND_USER_RESP_GROUPS.CREATION_DATE) >=TRUNC(SYSDATE)-30 GROUP BY TRUNC(FND_USER_RESP_GROUPS.CREATION_DATE) UNION SELECT TRUNC(SYSDATE)
, 'UNIQUE VISITORS TO-DATE' AS PROMPT1
, COUNT(DISTINCT ICX_SESSIONS.USER_ID) AS TOTAL_COUNT
FROM ICX_SESSIONS
WHERE RESPONSIBILITY_APPLICATION_ID = 672 UNION SELECT TRUNC(SYSDATE)
, 'TOTAL REGISTERED USERS' AS PROMPT1
, COUNT(DISTINCT FND_USER.USER_ID) AS TOTAL_COUNT
FROM FND_USER
, FND_USER_RESP_GROUPS
, FND_RESPONSIBILITY
WHERE FND_USER.USER_ID = FND_USER_RESP_GROUPS.USER_ID
AND FND_USER_RESP_GROUPS.RESPONSIBILITY_ID = FND_RESPONSIBILITY.RESPONSIBILITY_ID
AND RESPONSIBILITY_APPLICATION_ID=672