[Home] [Help]
13: WHEN no_data_found THEN
14: maxstartt := NULL;
15: END;
16:
17: -- insert login data from fnd_logins
18: INSERT INTO jtf_pf_ses_activity
19: (
20: seqid, day, timestamp, sessionid, userid,
21: appid, respid, langid, proxyid,
27: NVL(i.responsibility_application_id, -1), NVL(i.responsibility_id, -1), NVL(i.language_code, -1), NVL(i.proxy_user_id, -1),
28: (trunc( (f.start_time - to_date('1970/01/01', 'YYYY/MM/DD')) * 86400 * 1000) - timezone_offset) startt,
29: 'AUDIT', 'LOGIN', 200, 0, -1, 1, (f.end_time - f.start_time) * 86400 * 1000 thinkt,
30: fnd_global.user_id, sysdate, fnd_global.user_id, sysdate, fnd_global.conc_login_id, 'N/A', -1
31: FROM fnd_logins f, icx_sessions i
32: WHERE f.login_id = i.login_id
33: AND (maxstartt IS NULL
34: OR f.start_time > maxstartt
35: OR (f.start_time = maxstartt AND f.user_id NOT IN (SELECT userid FROM jtf_pf_ses_activity WHERE tech_stack='AUDIT' AND pagename='LOGIN' AND timestamp=maxstartt and day= maxstartt)))
43: WHEN no_data_found THEN
44: maxendt := NULL;
45: END;
46:
47: -- insert logout data from fnd_logins
48: INSERT INTO jtf_pf_ses_activity
49: (seqid, day, timestamp, sessionid, userid,
50: appid, respid, langid,proxyid,
51: startt,
56: NVL(i.responsibility_application_id, -1), NVL(i.responsibility_id, -1), NVL(i.language_code, -1), NVL(i.proxy_user_id, -1),
57: (trunc( (end_time - to_date('1970/01/01', 'YYYY/MM/DD')) * 86400 * 1000) - timezone_offset) startt,
58: 'AUDIT', 'LOGOUT', 200, 0, -1, 1, fnd_global.user_id,
59: sysdate, fnd_global.user_id, sysdate, fnd_global.conc_login_id
60: FROM fnd_logins f, icx_sessions i
61: WHERE f.login_id = i.login_id
62: AND f.end_time IS NOT NULL
63: AND (maxendt IS NULL
64: OR f.end_time > maxendt