Product: | JTF - CRM Foundation |
---|---|
Description: | |
Implementation/DBA Data: | APPS.JTF_PF_SESSION_SUMMARY_VL |
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