VIEW: APPS.JTF_PF_SESSION_SUMMARY_VL

Source

View Text - Preformatted

SELECT u.day day, u.seqid seqid, u.sessionid sessionid, fnd_user.user_name user_name, u.userid userid, sum_execT_jtf, sum_execT_oaf, sum_execT_form, sum_thinkT_jtf, sum_thinkT_oaf, sum_thinkT_form, cnt_pages_jtf, cnt_pages_oaf, cnt_pages_form, ucnt_pages, ucnt_flows, ucnt_users, ucnt_apps, ucnt_resps, ucnt_langs, u.startT startT, u.endT endT, u.cnt_fail cnt_fail FROM (select MIN(day) day, MIN(seqid) seqid, sessionid sessionid, MAX(userid) userid, sum(decode(TECH_STACK, 'JTF', execT, 0)) sum_execT_jtf, sum(decode(TECH_STACK, 'OAF', execT, 0)) sum_execT_oaf, sum(decode(TECH_STACK, 'FORM', execT, 0)) sum_execT_form, sum(decode(TECH_STACK, 'JTF', thinkT, 0)) sum_thinkT_jtf, sum(decode(TECH_STACK, 'OAF', thinkT, 0)) sum_thinkT_oaf, sum(decode(TECH_STACK, 'FORM', thinkT, 0)) sum_thinkT_form, sum(decode(TECH_STACK, 'JTF', 1, 0)) cnt_pages_jtf, sum(decode(TECH_STACK, 'OAF', 1, 0)) cnt_pages_oaf, sum(decode(TECH_STACK, 'FORM', 1, 0)) cnt_pages_form, COUNT(unique(pagename)) ucnt_pages, COUNT(unique(flowid)) ucnt_flows, COUNT(unique(userid)) ucnt_users, COUNT(unique(appid)) ucnt_apps, COUNT(unique(respid)) ucnt_resps, COUNT(unique(langid)) ucnt_langs, MIN(startT) startT, MAX(startT+execT) endT, sum(decode(statuscode,-100,1,0)) cnt_fail from JTF_PF_SES_ACTIVITY group by sessionid) u, fnd_user where u.userid = fnd_user.user_id(+) UNION ALL select day day, MIN(seqid) seqid, MIN(sessionid) sessionid, null user_name, MAX(userid) userid, sum(decode(TECH_STACK, 'JTF', execT, 0)) sum_execT_jtf, sum(decode(TECH_STACK, 'OAF', execT, 0)) sum_execT_oaf, sum(decode(TECH_STACK, 'FORM', execT, 0)) sum_execT_form, sum(decode(TECH_STACK, 'JTF', thinkT, 0)) sum_thinkT_jtf, sum(decode(TECH_STACK, 'OAF', thinkT, 0)) sum_thinkT_oaf, sum(decode(TECH_STACK, 'FORM', thinkT, 0)) sum_thinkT_form, sum(decode(TECH_STACK, 'JTF', 1, 0)) cnt_pages_jtf, sum(decode(TECH_STACK, 'OAF', 1, 0)) cnt_pages_oaf, sum(decode(TECH_STACK, 'FORM', 1, 0)) cnt_pages_form, COUNT(unique(pagename)) ucnt_pages, COUNT(unique(flowid)) ucnt_flows, COUNT(unique(userid)) ucnt_users, COUNT(unique(appid)) ucnt_apps, COUNT(unique(respid)) ucnt_resps, COUNT(unique(langid)) ucnt_langs, MIN(startT) startT, MAX(startT+execT) endT, sum(decode(statuscode,-100,1,0)) cnt_fail from JTF_PF_ANON_ACTIVITY group by day

View Text - HTML Formatted

SELECT U.DAY DAY
, U.SEQID SEQID
, U.SESSIONID SESSIONID
, FND_USER.USER_NAME USER_NAME
, U.USERID USERID
, SUM_EXECT_JTF
, SUM_EXECT_OAF
, SUM_EXECT_FORM
, SUM_THINKT_JTF
, SUM_THINKT_OAF
, SUM_THINKT_FORM
, CNT_PAGES_JTF
, CNT_PAGES_OAF
, CNT_PAGES_FORM
, UCNT_PAGES
, UCNT_FLOWS
, UCNT_USERS
, UCNT_APPS
, UCNT_RESPS
, UCNT_LANGS
, U.STARTT STARTT
, U.ENDT ENDT
, U.CNT_FAIL CNT_FAIL
FROM (SELECT MIN(DAY) DAY
, MIN(SEQID) SEQID
, SESSIONID SESSIONID
, MAX(USERID) USERID
, SUM(DECODE(TECH_STACK
, 'JTF'
, EXECT
, 0)) SUM_EXECT_JTF
, SUM(DECODE(TECH_STACK
, 'OAF'
, EXECT
, 0)) SUM_EXECT_OAF
, SUM(DECODE(TECH_STACK
, 'FORM'
, EXECT
, 0)) SUM_EXECT_FORM
, SUM(DECODE(TECH_STACK
, 'JTF'
, THINKT
, 0)) SUM_THINKT_JTF
, SUM(DECODE(TECH_STACK
, 'OAF'
, THINKT
, 0)) SUM_THINKT_OAF
, SUM(DECODE(TECH_STACK
, 'FORM'
, THINKT
, 0)) SUM_THINKT_FORM
, SUM(DECODE(TECH_STACK
, 'JTF'
, 1
, 0)) CNT_PAGES_JTF
, SUM(DECODE(TECH_STACK
, 'OAF'
, 1
, 0)) CNT_PAGES_OAF
, SUM(DECODE(TECH_STACK
, 'FORM'
, 1
, 0)) CNT_PAGES_FORM
, COUNT(UNIQUE(PAGENAME)) UCNT_PAGES
, COUNT(UNIQUE(FLOWID)) UCNT_FLOWS
, COUNT(UNIQUE(USERID)) UCNT_USERS
, COUNT(UNIQUE(APPID)) UCNT_APPS
, COUNT(UNIQUE(RESPID)) UCNT_RESPS
, COUNT(UNIQUE(LANGID)) UCNT_LANGS
, MIN(STARTT) STARTT
, MAX(STARTT+EXECT) ENDT
, SUM(DECODE(STATUSCODE
, -100
, 1
, 0)) CNT_FAIL
FROM JTF_PF_SES_ACTIVITY GROUP BY SESSIONID) U
, FND_USER
WHERE U.USERID = FND_USER.USER_ID(+) UNION ALL SELECT DAY DAY
, MIN(SEQID) SEQID
, MIN(SESSIONID) SESSIONID
, NULL USER_NAME
, MAX(USERID) USERID
, SUM(DECODE(TECH_STACK
, 'JTF'
, EXECT
, 0)) SUM_EXECT_JTF
, SUM(DECODE(TECH_STACK
, 'OAF'
, EXECT
, 0)) SUM_EXECT_OAF
, SUM(DECODE(TECH_STACK
, 'FORM'
, EXECT
, 0)) SUM_EXECT_FORM
, SUM(DECODE(TECH_STACK
, 'JTF'
, THINKT
, 0)) SUM_THINKT_JTF
, SUM(DECODE(TECH_STACK
, 'OAF'
, THINKT
, 0)) SUM_THINKT_OAF
, SUM(DECODE(TECH_STACK
, 'FORM'
, THINKT
, 0)) SUM_THINKT_FORM
, SUM(DECODE(TECH_STACK
, 'JTF'
, 1
, 0)) CNT_PAGES_JTF
, SUM(DECODE(TECH_STACK
, 'OAF'
, 1
, 0)) CNT_PAGES_OAF
, SUM(DECODE(TECH_STACK
, 'FORM'
, 1
, 0)) CNT_PAGES_FORM
, COUNT(UNIQUE(PAGENAME)) UCNT_PAGES
, COUNT(UNIQUE(FLOWID)) UCNT_FLOWS
, COUNT(UNIQUE(USERID)) UCNT_USERS
, COUNT(UNIQUE(APPID)) UCNT_APPS
, COUNT(UNIQUE(RESPID)) UCNT_RESPS
, COUNT(UNIQUE(LANGID)) UCNT_LANGS
, MIN(STARTT) STARTT
, MAX(STARTT+EXECT) ENDT
, SUM(DECODE(STATUSCODE
, -100
, 1
, 0)) CNT_FAIL
FROM JTF_PF_ANON_ACTIVITY GROUP BY DAY