[Home] [Help]
PACKAGE BODY: APPS.JTF_PF_SOA_MIGRATE_PKG
Source
1 PACKAGE BODY JTF_PF_SOA_MIGRATE_PKG AS
2 /* $Header: jtfpfsoamgrtpkgb.pls 120.5 2006/08/18 08:53:32 rjaiswal noship $ */
3 PROCEDURE MIGRATE_LOGINS_DATA(timezone_offset IN NUMBER) IS
4 maxstartt DATE;
5 maxendt DATE;
6
7 BEGIN
8 BEGIN
9 SELECT max(timestamp) INTO maxstartt
10 FROM jtf_pf_ses_activity
11 WHERE tech_stack='AUDIT' AND pagename='LOGIN';
12 EXCEPTION
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,
22 startt,
23 tech_stack, pagename, statuscode, exect, po, object_version_number, thinkt,
24 created_by, creation_date, last_updated_by, last_update_date, last_update_login, servername, serverport
25 )
26 (SELECT /*+ parallel(i) use_nl(f) */ JTF_PF_SOA_SEQ.NEXTVAL + 0.1, trunc(f.start_time), f.start_time, i.session_id, f.user_id,
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)))
36 );
37
38 BEGIN
39 SELECT max(timestamp) INTO maxendt
40 FROM jtf_pf_ses_activity
41 WHERE tech_stack='AUDIT' AND pagename='LOGOUT';
42 EXCEPTION
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,
52 tech_stack, pagename, statuscode, exect, po, object_version_number, created_by,
53 creation_date, last_updated_by, last_update_date, last_update_login
54 )
55 (SELECT /*+ parallel(i) use_nl(f) */ JTF_PF_SOA_SEQ.NEXTVAL + 0.1, trunc(f.end_time), f.end_time, i.session_id, f.user_id,
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
65 OR (f.end_time = maxendt AND f.user_id NOT IN (SELECT userid FROM jtf_pf_ses_activity WHERE tech_stack='AUDIT' AND pagename='LOGOUT' AND timestamp=maxendt AND day=maxendt)))
66 );
67
68 END MIGRATE_LOGINS_DATA;
69
70 PROCEDURE MIGRATE_RESP_DATA(timezone_offset IN NUMBER) IS
71 maxtimestamp DATE;
72 BEGIN
73 BEGIN
74 SELECT max(timestamp) INTO maxtimestamp
75 FROM jtf_pf_ses_activity
76 WHERE tech_stack = 'AUDIT' AND pagename = 'RESP_CHANGE';
77 EXCEPTION
78 WHEN no_data_found THEN
79 maxtimestamp := NULL;
80 END;
81
82 IF(maxtimestamp IS NOT NULL) THEN
83 DELETE FROM jtf_pf_ses_activity
84 WHERE tech_stack = 'AUDIT' AND pagename = 'RESP_CHANGE' AND timestamp = maxtimestamp;
85 END IF;
86
87 INSERT INTO jtf_pf_ses_activity
88 (seqid, day, timestamp, sessionid, userid, appid,
89 respid, langid, proxyid,
90 startt,
91 tech_stack, pagename, statuscode, exect, thinkt,
92 po, object_version_number, created_by, creation_date, last_updated_by,
93 last_update_date, last_update_login
94 )
95 (SELECT /*+ parallel(i) use_nl(r) */ JTF_PF_SOA_SEQ.NEXTVAL + 0.1, trunc(r.start_time), r.start_time, i.session_id, i.user_id, r.resp_appl_id,
96 r.responsibility_id, NVL(i.language_code, -1), NVL(i.proxy_user_id, -1),
97 (trunc( (r.start_time - to_date('1970/01/01', 'YYYY/MM/DD')) * 86400 * 1000) - timezone_offset) startt,
98 'AUDIT', 'RESP_CHANGE', 200, 0, (r.end_time - r.start_time) * 86400 * 1000 thinkt,
99 -1, 1, fnd_global.user_id, sysdate, fnd_global.user_id,
100 sysdate, fnd_global.conc_login_id
101 FROM fnd_login_responsibilities r, icx_sessions i
102 WHERE r.login_id = i.login_id
103 AND (maxtimestamp IS NULL
104 OR r.start_time > maxtimestamp
105 OR (r.start_time = maxtimestamp AND r.responsibility_id NOT IN
106 (SELECT respid FROM jtf_pf_ses_activity WHERE tech_stack='AUDIT' AND pagename = 'RESP_CHANGE' AND timestamp = maxtimestamp AND day=maxtimestamp))));
107
108 END MIGRATE_RESP_DATA;
109
110 PROCEDURE MIGRATE_FORMS_DATA(timezone_offset IN NUMBER) IS
111 maxtimestamp DATE;
112 BEGIN
113 BEGIN
114 SELECT max(timestamp) INTO maxtimestamp FROM jtf_pf_ses_activity WHERE tech_stack='FORM';
115 EXCEPTION
116 WHEN no_data_found THEN
117 maxtimestamp := NULL;
118 END;
119
120 IF(maxtimestamp IS NOT NULL) THEN
121 DELETE FROM jtf_pf_ses_activity
122 WHERE tech_stack = 'FORM' AND timestamp = maxtimestamp;
123 END IF;
124
125 INSERT INTO jtf_pf_ses_activity
126 ( seqid, day, timestamp, sessionid, userid, appid,
127 respid, langid,proxyid,startt, tech_stack, pagename,
128 statuscode, exect, thinkt, po, object_version_number,
129 created_by, creation_date, last_updated_by, last_update_date,
130 last_update_login
131 )
132 ( SELECT /*+ parallel(i) use_nl(f) */ JTF_PF_SOA_SEQ.NEXTVAL + 0.1, trunc(f.start_time), f.start_time,
133 i.session_id, i.user_id, f.form_appl_id, r.responsibility_id, NVL(i.language_code, -1),NVL(i.proxy_user_id, -1),
134 (trunc( (f.start_time - to_date('1970/01/01', 'YYYY/MM/DD')) * 86400 * 1000) - timezone_offset) startt,
135 'FORM', to_char(f.form_id), 200, 0, (f.end_time - f.start_time) * 86400 * 1000 thinkt, -1, 1,
136 fnd_global.user_id, sysdate, fnd_global.user_id, sysdate, fnd_global.conc_login_id
137 FROM fnd_login_resp_forms f, icx_sessions i, fnd_login_responsibilities r
138 WHERE f.login_id = i.login_id
139 AND r.login_resp_id = f.login_resp_id
140 AND (maxtimestamp IS NULL
141 OR f.start_time > maxtimestamp
142 OR (f.start_time = maxtimestamp AND to_char(f.form_id) NOT IN (SELECT pagename FROM jtf_pf_ses_activity WHERE tech_stack = 'FORM' AND timestamp = maxtimestamp and day = maxtimestamp AND to_char(f.form_id) = pagename)))
143 );
144 END MIGRATE_FORMS_DATA;
145
146 END JTF_PF_SOA_MIGRATE_PKG;