1 PACKAGE BODY JTF_PF_CONV_PKG AS
2 /* $Header: jtfpfconvpkgb.pls 120.14 2008/04/28 15:32:10 rlandows ship $ */
3 /*===========================================================================+
4 | Copyright (c) 2002 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | ALL rights reserved. |
7 +===========================================================================+
8 | FILE NAME |
9 | jtfpfconvpkgb.pls |
10 | |
11 | Description |
12 | |
13 | This FILE contains THE PACKAGE BODY FOR THE |
14 | JTF_PF_CONV_PKG , which IS THE PLSQL INTERFACE FOR THE |
15 | jsp (activity) LOGGING project |
16 | |
17 | Modification History: |
18 | 05-Feb-2004 Modified navkumar |
19 | fixed midnight-straddle bug going in %_SUMM tables |
20 | split migrate_data to _stats and _raw for |
21 | troubleshooting |
22 | 08-Jan-2004 Modified angunda |
23 | modified migrate_data to write to JTF_PF_%_SUMM |
24 | Added TECH_STACK logic |
25 | 14-Apr-2002 Modified navkumar |
26 | added a suite OF PL/SQL FUNCTIONS and procedures |
27 | 11-Apr-2002 Created bsanghav |
28 |___________________________________________________________________________|*/
29
30 last_migrate_day DATE;
31
32 PROCEDURE writePFObjectTable(objTab JTF_PF_PAGE_OBJECT_TABLE, write_to_jtf INTEGER, tech_stack_tbl JTF_PF_TECHSTACK_TABLE, track_prps_tbl JTF_PF_TRACKPURPOSE_TABLE, dbdate OUT NOCOPY DATE) IS
33 PRAGMA AUTONOMOUS_TRANSACTION;
34 enqueue_options dbms_aq.enqueue_options_t;
35 message_properties dbms_aq.message_properties_t;
36 message_handle RAW(16);
37 objTabLocal JTF_PF_PAGE_OBJECT_TABLE;
38 obj JTF_PF_PAGE_OBJECT;
39 dummyPageObj JTF_PF_PAGE_OBJECT;
40 seq_num INTEGER;
41
42 BEGIN
43 objTabLocal := JTF_PF_PAGE_OBJECT_TABLE();
44 FOR i IN 1..objTab.COUNT
45 LOOP
46 obj := objTab(i);
47 objTabLocal.EXTEND;
48 SELECT JTF_PF_SEQ.NEXTVAL INTO seq_num FROM dual;
49 objTabLocal(i) := JTF_PF_PAGE_OBJECT(JTF_PF_INFO_OBJECT(seq_num,obj.info.Day,obj.info.Timestamp),obj.dpf,obj.ses,obj.jsp,
50 obj.loc,obj.lang,obj.client,obj.params,
51 obj.cookies,obj.headers, obj.prodinfo);
52 END LOOP;
53 SELECT sysdate INTO dbdate FROM dual;
54 IF (write_to_jtf = 1) THEN
55 FORALL indx IN objTabLocal.FIRST .. objTabLocal.LAST
56 INSERT INTO jtf_pf_repository(
57 pageobject, object_version_number,
58 created_by, creation_date,
59 last_updated_by, last_update_date, tech_stack, track_purpose)
60 VALUES (objTabLocal(indx), 0, 690, sysdate, 690, sysdate, tech_stack_tbl(indx), track_prps_tbl(indx));
61
62 ELSE
63 message_properties.correlation := seq_num;
64 message_properties.expiration := 24*60*60; -- messages expire in one day after first dequeue
65 dbms_aq.enqueue(queue_name => qname,
66 enqueue_options => enqueue_options,
67 message_properties => message_properties,
68 payload => dummyPageObj,
69 msgid => message_handle);
70 END IF;
71 COMMIT;
72 END writePFObjectTable;
73
74 /*============================================================================================================
75 | Writes THE Page flow data coming IN FROM THE JAVA layer INTO Advanced Queue
76 | INTO JTF_PF_PARAMS_TABLE
77 | This IS an autonomous TRANSACTION i.e. It does NOT matter whether THE parent TRANSACTION
78 | commited OR rolled back , this TRANSACTION will COMMIT IF ALL goes well
79 ============================================================================================================*/
80 PROCEDURE writePFObject(obj JTF_PF_PAGE_OBJECT,write_to_jtf INTEGER,tech_stack VARCHAR2, track_purpose VARCHAR2, dbdate OUT NOCOPY DATE) IS
81 PRAGMA AUTONOMOUS_TRANSACTION;
82 pObject JTF_PF_PAGE_OBJECT;
83 enqueue_options dbms_aq.enqueue_options_t;
84 message_properties dbms_aq.message_properties_t;
85 message_handle RAW(16);
86 dummyPageObj JTF_PF_PAGE_OBJECT;
87 seq_num INTEGER;
88 BEGIN
89 SELECT JTF_PF_SEQ.NEXTVAL INTO seq_num FROM dual;
90 SELECT sysdate INTO dbdate FROM dual;
91 dummyPageObj := JTF_PF_PAGE_OBJECT(JTF_PF_INFO_OBJECT(seq_num,obj.info.Day,obj.info.Timestamp),obj.dpf,obj.ses,obj.jsp,
92 obj.loc,obj.lang,obj.client,obj.params,
93 obj.cookies,obj.headers,obj.prodinfo);
94 IF (write_to_jtf = 1) THEN
95 INSERT INTO jtf_pf_repository(
96 pageobject, object_version_number,
97 created_by, creation_date,
98 last_updated_by, last_update_date, tech_stack, track_purpose)
99 VALUES (dummyPageObj, 0, 690, obj.info.Day, 690, obj.info.Timestamp, tech_stack, track_purpose);
100 ELSE
101 message_properties.correlation := seq_num;
102 message_properties.expiration := 24*60*60; -- messages expire in one day after first dequeue
103 dbms_aq.enqueue(queue_name => qname,
104 enqueue_options => enqueue_options,
105 message_properties => message_properties,
106 payload => dummyPageObj,
107 msgid => message_handle);
108 END IF;
109 COMMIT;
110 END writePFObject;
111
112 /* ============================================================================================================
113 | Converts THE param names AND param VALUES, coming IN AS 2 seperate arrays, FROM THE JAVA layer, |
114 | INTO JTF_PF_PARAMS_TABLE |
115 | This IS used TO construct THE JTF_PF_PAGE_OBJECT |
116 ===============================================================================================================*/
117 FUNCTION GetParams (paramNames JTF_VARCHAR2_TABLE_300, paramValues JTF_VARCHAR2_TABLE_4000, paramSz INTEGER)
118 RETURN JTF_PF_PARAMS_TABLE IS
119 params JTF_PF_PARAMS_TABLE := JTF_PF_PARAMS_TABLE();
120 BEGIN
121 FOR i IN 1..paramSz LOOP
122 params.EXTEND;
123 params(i) := JTF_PF_NVPAIR(paramNames(i),paramValues(i));
124 END LOOP;
125 RETURN params;
126 END;
127 /* ============================================================================================================
128 | Converts THE param names AND param VALUES, coming IN AS 2 seperate arrays, FROM THE JAVA layer, |
129 | INTO JTF_PF_PRODPARAMS_TABLE |
130 | This IS used TO construct THE JTF_PF_PRODINFO_OBJECT |
131 ===============================================================================================================*/
132 FUNCTION GetProdParams (paramNames JTF_VARCHAR2_TABLE_300, paramValues JTF_VARCHAR2_TABLE_4000, paramSz INTEGER)
133 RETURN JTF_PF_PRODPARAMS_TABLE IS
134 params JTF_PF_PRODPARAMS_TABLE := JTF_PF_PRODPARAMS_TABLE();
135 BEGIN
136 FOR i IN 1..paramSz LOOP
137 params.EXTEND;
138 params(i) := JTF_PF_NVPAIR(paramNames(i),paramValues(i));
139 END LOOP;
140 RETURN params;
141 END;
142
143 /* ========================================================================
144 | Converts THE param names AND param VALUES, |
145 | coming IN AS an ARRAY OF NVPAIRS INTO a CLOB FOR clickstream support |
146 ===========================================================================*/
147 FUNCTION GetParamString(params JTF_PF_PARAMS_TABLE)
148 RETURN CLOB IS
149 paramCLOB CLOB;
150 paramV VARCHAR(32767);
151 nv JTF_PF_NVPAIR;
152 paramSz NUMBER;
153 psize NUMBER;
154 offset NUMBER;
155 i NUMBER;
156 BEGIN
157 dbms_lob.createtemporary(paramCLOB,TRUE,DBMS_LOB.CALL);
158 psize := 0;
159 IF(params IS NULL) THEN
160 RETURN paramCLOB;
161 END IF;
162 paramSz := params.COUNT;
163
164 FOR i IN 1..paramSz LOOP
165 nv := params(i);
166 IF i <> 1 THEN
167 paramV := '&' || nv.NAME || '=' || nv.value;
168 ELSE
169 paramV := nv.NAME || '=' || nv.value;
170 END IF;
171 IF paramV IS NOT NULL THEN
172 psize := length(paramV);
173 offset := DBMS_LOB.GETLENGTH(paramClob) + 1;
174 DBMS_LOB.WRITE(paramCLOB,psize,offset,paramV);
175 END IF;
176 END LOOP;
177 RETURN paramCLOB;
178 END;
179
180 /* ========================================================================
181 | Converts THE param names AND param VALUES, |
182 | coming IN AS an ARRAY OF NVPAIRS INTO a CLOB FOR clickstream support |
183 ==========================================================================*/
184 FUNCTION GetCookieString(params JTF_PF_COOKIES_TABLE)
185 RETURN CLOB IS
186 paramCLOB CLOB;
187 paramV VARCHAR(32767);
188 nvs JTF_PF_NVSTRIPLET;
189 paramSz NUMBER;
190 psize NUMBER;
191 offset NUMBER;
192 i NUMBER;
193 BEGIN
194 dbms_lob.createtemporary(paramCLOB,TRUE,DBMS_LOB.CALL);
195 psize := 0;
196 IF(params IS NULL) THEN
197 RETURN paramCLOB;
198 END IF;
199 paramSz := params.COUNT;
200
201 FOR i IN 1..paramSz LOOP
202 nvs := params(i);
203 IF i <> 1 THEN
204 paramV := ' ' || nvs.NAME || '=' || nvl(nvs.value, 'JTF_PF_SZ:'||nvs.length) || ';';
205 ELSE
206 paramV := nvs.NAME || '=' || nvl(nvs.value, 'JTF_PF_SZ:'||nvs.length) || ';';
207 END IF;
208 IF paramV IS NOT NULL THEN
209 psize := length(paramV);
210 offset := DBMS_LOB.GETLENGTH(paramClob) + 1;
211 DBMS_LOB.WRITE(paramCLOB,psize,offset,paramV);
212 END IF;
213 END LOOP;
214 RETURN paramCLOB;
215 END;
216
217 /* ============================================================================================================
218 | Converts THE cookie names, cookie sizes AND cokie VALUES, coming IN AS 3 seperate arrays, |
219 | FROM THE JAVA layer, INTO JTF_PF_COOKIES_TABLE |
220 | This IS used TO construct THE JTF_PF_PAGE_OBJECT |
221 ===============================================================================================================*/
222 FUNCTION GetCookies (cookieNames JTF_VARCHAR2_TABLE_300, cookieValues JTF_VARCHAR2_TABLE_4000,
223 cookieSizes JTF_NUMBER_TABLE, cookieSz INTEGER)
224 RETURN JTF_PF_COOKIES_TABLE IS
225 cookie JTF_PF_COOKIES_TABLE := JTF_PF_COOKIES_TABLE();
226 BEGIN
227 cookie.EXTEND(cookieSz);
228 FOR i IN 1..cookieSz LOOP
229 cookie(i) := JTF_PF_NVSTRIPLET(cookieNames(i),cookieValues(i),cookieSizes(i));
230 END LOOP;
231 RETURN cookie;
232 END;
233
234
235 /*============================================================================================================
236 | Upload THE data IN AQ periodically TO THE JTF Repository |
237 =============================================================================================================*/
238
239 PROCEDURE uploadAllNewPfObjects IS --ang This proc is currently not being invoked anywhere
240 pgObjects JTF_PF_PAGE_OBJECT ;
241 new_messages BOOLEAN :=TRUE;
242 dopt dbms_aq.dequeue_options_t;
243 mprop dbms_aq.message_properties_t;
244 deq_msgid RAW(16);
245 no_messages EXCEPTION;
246 last_msg_in_jtf_rep INTEGER;
247 last_msg_to_upload INTEGER;
248 current_msg INTEGER;
249 QUERY VARCHAR2(2000);
250 PRAGMA EXCEPTION_INIT(NO_MESSAGES,-25228);
251 PRAGMA AUTONOMOUS_TRANSACTION;
252 BEGIN
253 SELECT max(a.pageobject.info.recid) INTO last_msg_in_jtf_rep FROM jtf_pf_repository a;
254 -- select max(a.corrid) into last_msg_to_upload from JTF_PF_LOGGING_TABLE a;
255
256 IF last_msg_in_jtf_rep IS NOT NULL THEN current_msg := last_msg_in_jtf_rep; END IF;
257
258 dopt.navigation:=dbms_aq.first_message;
259 dopt.dequeue_mode := DBMS_AQ.BROWSE;
260 dopt.wait := 0;
261 WHILE (new_messages) LOOP
262 BEGIN
263 IF(current_msg IS NOT NULL) THEN
264 current_msg := current_msg + 1;
265 IF (current_msg > last_msg_to_upload) THEN new_messages := FALSE; END IF;
266 dopt.correlation := current_msg;
267 END IF;
268
269 dbms_aq.dequeue(queue_name=>qname,
270 dequeue_options=>dopt,
271 message_properties=> mprop,
272 payload => pgObjects,
273 msgid => deq_msgid);
274
275 current_msg := mprop.correlation;
276 dopt.navigation := dbms_aq.first_message;
277 dopt.wait := 0;
278 INSERT INTO jtf_pf_repository( --ang no need to update this insert as the proc is not in use
279 pageobject, object_version_number,
280 created_by, creation_date,
281 last_updated_by, last_update_date)
282 VALUES (pgObjects, 0, 690, pgObjects.info.Day, 690, pgObjects.info.Timestamp);
283
284 -- dbms_output.put_line('inserted ' || mprop.correlation);
285 EXCEPTION
286 WHEN NO_MESSAGES THEN
287 IF(current_msg IS NOT NULL) THEN
288 current_msg := current_msg + 1;
289 IF(current_msg > last_msg_to_upload) THEN
290 -- dbms_output.put_line('No more messages to dequeue');
291 new_messages := FALSE;
292 COMMIT;
293 END IF;
294 dopt.correlation := current_msg;
295 END IF;
296 WHEN OTHERS THEN
297 new_messages :=FALSE;
298 -- raise_application_error(-20101, SQLERRM);
299 -- dbms_output.put_line(SQLERRM);
300 END;
301 END LOOP;
302 END ;
303
304 -- This procedure is for migrating data from the JTF_PF_REPOSITORY (OLTP Stage Area)
305 -- to the OLAP Tables (JTF_PF_ANON_ACTIVITY, JTF_PF_SES_ACTIVITY).
306 PROCEDURE MIGRATE_DATA(timezone_offset IN NUMBER) IS
307 today DATE;
308 BEGIN
309 SELECT trunc(sysdate) INTO today FROM dual;
310 JTF_PF_SOA_MIGRATE_PKG.MIGRATE_LOGINS_DATA(timezone_offset);
311 JTF_PF_SOA_MIGRATE_PKG.MIGRATE_RESP_DATA(timezone_offset);
312 JTF_PF_SOA_MIGRATE_PKG.MIGRATE_FORMS_DATA(timezone_offset);
313 migrate_data_raw;
314 migrate_data_stats(today);
315 migrated_data(1,sysdate);
316 COMMIT;
317 END MIGRATE_DATA;
318
319 PROCEDURE MIGRATE_DATA_STATS(today DATE) IS
320 BEGIN
321 IF (last_migrate_day IS NULL) THEN
322 SELECT min(x.day) INTO last_migrate_day FROM
323 (SELECT max(day) AS day FROM JTF_PF_APP_SUMM
324 UNION ALL
325 SELECT max(day) AS day FROM JTF_PF_HOST_SUMM
326 UNION ALL
327 SELECT max(day) AS day FROM JTF_PF_PAGE_SUMM
328 UNION ALL
329 SELECT max(day) AS day FROM JTF_PF_SESSION_SUMM
330 UNION ALL
331 SELECT max(day) AS day FROM JTF_PF_USER_SUMM
332 ) x;
333 END IF;
334 IF (last_migrate_day IS NOT NULL) THEN
335 --DELETE FROM JTF_PF_ANON_ACTIVITY WHERE day < last_migrate_day;
336 --DELETE FROM JTF_PF_SES_ACTIVITY WHERE day < last_migrate_day;
337 DELETE FROM JTF_PF_APP_SUMM WHERE day >= last_migrate_day;
338 DELETE FROM JTF_PF_HOST_SUMM WHERE day >= last_migrate_day;
339 DELETE FROM JTF_PF_PAGE_SUMM WHERE day >= last_migrate_day;
340 DELETE FROM JTF_PF_SESSION_SUMM WHERE (sessionid IS NULL and day >= last_migrate_day)
341 or (sessionid IS NOT NULL and sessionid IN
342 (select distinct sessionid from JTF_PF_SES_ACTIVITY where day >= last_migrate_day OR last_migrate_day IS NULL));
343 DELETE FROM JTF_PF_USER_SUMM WHERE day >= last_migrate_day;
344 END IF;
345 -- ADDED to fix bug no-5036611 (START HERE)
346 IF (last_migrate_day is null) THEN
347
348 INSERT INTO JTF_PF_APP_SUMM (day, cnt_pages_jtf, cnt_pages_oaf, cnt_pages_form, cnt_pages_all, cnt_flows, cnt_users, cnt_apps, cnt_resps, cnt_sessions, cnt_langs)
349 (SELECT day, cnt_pages_jtf, cnt_pages_oaf, cnt_pages_form, cnt_pages_jtf+cnt_pages_oaf+cnt_pages_form, cnt_flows, cnt_users, cnt_apps, cnt_resps, cnt_sessions, cnt_langs
350 FROM JTF_PF_APP_SUMMARY_VL
351 );
352 INSERT INTO JTF_PF_HOST_SUMM (day, servername, serverport, jservs, sum_exect_jtf, sum_exect_oaf, pagehits_jtf, pagehits_oaf, pagehits_all, fails, pages, badpages)
353 (SELECT day, NVL(servername, 'N/A'), NVL(serverport, -1), jservs, sum_exect_jtf, sum_exect_oaf, pagehits_jtf, pagehits_oaf, pagehits_jtf+pagehits_oaf, fails, pages, badpages
354 FROM JTF_PF_HOST_SUMMARY_VL
355 );
356 INSERT INTO JTF_PF_PAGE_SUMM (day, pagename, tech_stack, cnt_pages, cnt_ses, ucnt_ses, ucnt_users, ucnt_apps, ucnt_resps, ucnt_langs,
357 sum_exect, sum_thinkt, cnt_thinkt, startt, endt, cnt_fail, cnt_forward)
358 (SELECT day, pagename, tech_stack, cnt_pages, cnt_ses, ucnt_ses, ucnt_users, ucnt_apps, ucnt_resps, ucnt_langs,
359 sum_exect, sum_thinkt, cnt_thinkt, startt, endt, cnt_fail, cnt_forward
360 FROM JTF_PF_PAGE_SUMMARY_VL
361 );
362 INSERT INTO JTF_PF_SESSION_SUMM (day, seqid, sessionid, user_name, userid, sum_exect_jtf, sum_exect_oaf, sum_exect_form, sum_thinkt_jtf, sum_thinkt_oaf, sum_thinkt_form,
363 cnt_pages_jtf, cnt_pages_oaf, cnt_pages_form, cnt_pages_all, ucnt_pages, ucnt_flows, ucnt_users, ucnt_apps, ucnt_resps, ucnt_langs, startt, endt, cnt_fail)
364 (SELECT day, seqid, sessionid, user_name, userid, sum_exect_jtf, sum_exect_oaf, sum_exect_form, sum_thinkt_jtf, sum_thinkt_oaf, sum_thinkt_form,
365 cnt_pages_jtf, cnt_pages_oaf, cnt_pages_form, cnt_pages_jtf+cnt_pages_oaf+cnt_pages_form, ucnt_pages, ucnt_flows, ucnt_users, ucnt_apps, ucnt_resps, ucnt_langs, startt, endt, cnt_fail
366 FROM JTF_PF_SESSION_SUMMARY_VL
367 WHERE (sessionid IS NULL )
368 or (sessionid IS NOT NULL and sessionid IN
369 (select distinct sessionid from JTF_PF_SES_ACTIVITY))
370 );
371 INSERT INTO JTF_PF_USER_SUMM (day, user_name, userid, sum_exect_jtf, sum_exect_oaf, sum_exect_form, sum_thinkt_jtf, sum_thinkt_oaf, sum_thinkt_form,
372 cnt_pages_jtf, cnt_pages_oaf, cnt_pages_form, cnt_pages_all, ucnt_ses, ucnt_pages, ucnt_apps, ucnt_resps, ucnt_langs, startt, endt, cnt_fail)
373 (SELECT day, user_name, userid, sum_exect_jtf, sum_exect_oaf, sum_exect_form, sum_thinkt_jtf, sum_thinkt_oaf, sum_thinkt_form,
374 cnt_pages_jtf, cnt_pages_oaf, cnt_pages_form, cnt_pages_jtf+cnt_pages_oaf+cnt_pages_form, ucnt_ses, ucnt_pages, ucnt_apps, ucnt_resps, ucnt_langs, startt, endt, cnt_fail
375 FROM JTF_PF_USER_SUMMARY_VL
376 );
377
378 ELSE
379
380 INSERT INTO JTF_PF_APP_SUMM (day, cnt_pages_jtf, cnt_pages_oaf, cnt_pages_form, cnt_pages_all, cnt_flows, cnt_users, cnt_apps, cnt_resps, cnt_sessions, cnt_langs)
381 (SELECT day, cnt_pages_jtf, cnt_pages_oaf, cnt_pages_form, cnt_pages_jtf+cnt_pages_oaf+cnt_pages_form, cnt_flows, cnt_users, cnt_apps, cnt_resps, cnt_sessions, cnt_langs
382 FROM JTF_PF_APP_SUMMARY_VL
383 WHERE day >= last_migrate_day
384 );
385 INSERT INTO JTF_PF_HOST_SUMM (day, servername, serverport, jservs, sum_exect_jtf, sum_exect_oaf, pagehits_jtf, pagehits_oaf, pagehits_all, fails, pages, badpages)
386 (SELECT day, NVL(servername, 'N/A'), NVL(serverport, -1), jservs, sum_exect_jtf, sum_exect_oaf, pagehits_jtf, pagehits_oaf, pagehits_jtf+pagehits_oaf, fails, pages, badpages
387 FROM JTF_PF_HOST_SUMMARY_VL
388 WHERE day >= last_migrate_day
389 );
390 INSERT INTO JTF_PF_PAGE_SUMM (day, pagename, tech_stack, cnt_pages, cnt_ses, ucnt_ses, ucnt_users, ucnt_apps, ucnt_resps, ucnt_langs,
391 sum_exect, sum_thinkt, cnt_thinkt, startt, endt, cnt_fail, cnt_forward)
392 (SELECT day, pagename, tech_stack, cnt_pages, cnt_ses, ucnt_ses, ucnt_users, ucnt_apps, ucnt_resps, ucnt_langs,
393 sum_exect, sum_thinkt, cnt_thinkt, startt, endt, cnt_fail, cnt_forward
394 FROM JTF_PF_PAGE_SUMMARY_VL
395 WHERE day >= last_migrate_day
396 );
397 INSERT INTO JTF_PF_SESSION_SUMM (day, seqid, sessionid, user_name, userid, sum_exect_jtf, sum_exect_oaf, sum_exect_form, sum_thinkt_jtf, sum_thinkt_oaf, sum_thinkt_form,
398 cnt_pages_jtf, cnt_pages_oaf, cnt_pages_form, cnt_pages_all, ucnt_pages, ucnt_flows, ucnt_users, ucnt_apps, ucnt_resps, ucnt_langs, startt, endt, cnt_fail)
399 (SELECT day, seqid, sessionid, user_name, userid, sum_exect_jtf, sum_exect_oaf, sum_exect_form, sum_thinkt_jtf, sum_thinkt_oaf, sum_thinkt_form,
400 cnt_pages_jtf, cnt_pages_oaf, cnt_pages_form, cnt_pages_jtf+cnt_pages_oaf+cnt_pages_form, ucnt_pages, ucnt_flows, ucnt_users, ucnt_apps, ucnt_resps, ucnt_langs, startt, endt, cnt_fail
401 FROM JTF_PF_SESSION_SUMMARY_VL
402 WHERE (sessionid IS NULL and day >= last_migrate_day)
403 or (sessionid IS NOT NULL and sessionid IN
404 (select distinct sessionid from JTF_PF_SES_ACTIVITY where day >= last_migrate_day ))
405 );
406 INSERT INTO JTF_PF_USER_SUMM (day, user_name, userid, sum_exect_jtf, sum_exect_oaf, sum_exect_form, sum_thinkt_jtf, sum_thinkt_oaf, sum_thinkt_form,
407 cnt_pages_jtf, cnt_pages_oaf, cnt_pages_form, cnt_pages_all, ucnt_ses, ucnt_pages, ucnt_apps, ucnt_resps, ucnt_langs, startt, endt, cnt_fail)
408 (SELECT day, user_name, userid, sum_exect_jtf, sum_exect_oaf, sum_exect_form, sum_thinkt_jtf, sum_thinkt_oaf, sum_thinkt_form,
409 cnt_pages_jtf, cnt_pages_oaf, cnt_pages_form, cnt_pages_jtf+cnt_pages_oaf+cnt_pages_form, ucnt_ses, ucnt_pages, ucnt_apps, ucnt_resps, ucnt_langs, startt, endt, cnt_fail
410 FROM JTF_PF_USER_SUMMARY_VL
411 WHERE day >= last_migrate_day
412 );
413
414 END IF;
415 -- ADDED To fix bug no-5036611 (END HERE)
416
417 last_migrate_day := today;
418 END MIGRATE_DATA_STATS;
419
420 PROCEDURE MIGRATE_DATA_RAW IS
421 v_SQLString VARCHAR2(300);
422
423 CURSOR po_Cursor(starting_po IN INTEGER) IS
424 SELECT x.pageobject, x.tech_stack
425 FROM JTF_PF_REPOSITORY x
426 WHERE x.pageobject.info.RecId > starting_po and
427 x.track_purpose not in (2, 4)
428 ORDER BY x.pageobject.ses.SessionId, x.pageobject.jsp.StartTime;
429
430 CURSOR cur_val IS SELECT nvl(max_po,-100000000000), nvl(ses_seqid,0), nvl(anon_seqid,0) FROM JTF_PF_SEQ_VL;
431
432 nextpo JTF_PF_PAGE_OBJECT;
433 currpo JTF_PF_PAGE_OBJECT;
434 thinkT INTEGER;
435 seqcnt1 INTEGER;
436 seqcnt2 INTEGER;
437 maxpo INTEGER;
438 currtechstack varchar2(20);
439 nexttechstack VARCHAR2(20);
440
441 BEGIN
442 OPEN cur_val;
446 currpo := NULL;
443 FETCH cur_val INTO maxpo, seqcnt2, seqcnt1;
444 CLOSE cur_val;
445
447
448 OPEN po_Cursor(maxpo);
449 LOOP
450 FETCH po_Cursor INTO nextpo, nexttechstack;
451 -- if record exists in the cursor (previous iteration)
452 IF currpo IS NOT NULL THEN
453 -- for anonymous activity
454 IF currpo.ses IS NULL THEN
455 IF currpo.jsp IS NOT NULL AND currpo.loc IS NOT NULL AND currpo.info IS NOT NULL THEN
456 seqcnt1 := seqcnt1 - 1;
457 INSERT INTO JTF_PF_ANON_ACTIVITY(
458 SEQID,
459 DAY,
460 TECH_STACK,
461 TIMESTAMP,
462 SERVERNAME,
463 SERVERPORT,
464 JSERVPORT,
465 FLOWID,
466 SESSIONID,
467 USERID,
468 PROXYID,
469 APPID,
470 RESPID,
471 LANGID,
472 STARTT,
473 PAGENAME,
474 STATUSCODE,
475 EXECT,
476 THINKT,
477 PO,
478 OBJECT_VERSION_NUMBER,
479 CREATED_BY,
480 CREATION_DATE,
481 LAST_UPDATED_BY,
482 LAST_UPDATE_DATE
483 ) VALUES(
484 seqcnt1,
485 currpo.info.day,
486 currtechstack,
487 currpo.info.timestamp,
488 currpo.loc.servername,
489 currpo.loc.serverport,
490 currpo.loc.jservport,
491 null,
492 null,
493 null,
494 null,
495 null,
496 null,
497 null,
498 NVL(currpo.jsp.starttime, -1), -- ANG - Remove NVL condition, still what to do when starttime is null
499 NVL(currpo.jsp.NAME, 'N/A'), -- ANG - Remove NVL condition, again there are records in repository table where the value for name coln is null. How should this be handled?
500 NVL(currpo.jsp.statusCode, -1), -- ANG - need to get rid of NVL condition
501 NVL(currpo.jsp.executionTime, -1), -- ANG - need to get rid of NVL condition
502 thinkT,
503 currpo.info.Recid,
504 0,690,currpo.info.day,690,currpo.info.timestamp);
505 END IF;
506 ELSE
507 -- for session activity
508 thinkT := NULL;
509 IF NOT(currpo.jsp.statusCode = -200) AND currpo.ses.SessionId = nextpo.ses.SessionId AND NOT(po_Cursor%NOTFOUND) THEN
510 thinkT := (nextpo.jsp.StartTime - (currpo.jsp.StartTime + currpo.jsp.executionTime));
511 IF thinkT IS NOT NULL AND thinkT <= 0 THEN
512 thinkT := NULL;
513 END IF;
514 END IF;
515 IF currpo.jsp IS NOT NULL
516 AND currpo.loc IS NOT NULL
517 AND currpo.info IS NOT NULL
518 AND currtechstack IS NOT NULL
519 AND currpo.info.day IS NOT NULL
520 AND currpo.info.timestamp IS NOT NULL
521 AND currpo.info.Recid IS NOT NULL
522 AND currpo.ses.sessionid IS NOT NULL
523 AND currpo.ses.userid IS NOT NULL
524 AND currpo.ses.appid IS NOT NULL
525 AND currpo.ses.respid IS NOT NULL
526 AND currpo.ses.langid IS NOT NULL
527 AND currpo.jsp.starttime IS NOT NULL
528 AND currpo.jsp.NAME IS NOT NULL
529 AND currpo.jsp.statusCode IS NOT NULL
530 AND currpo.jsp.executionTime IS NOT NULL
531 THEN
532 seqcnt2 := seqcnt2 + 1;
533 INSERT INTO JTF_PF_SES_ACTIVITY(
534 SEQID,
535 DAY,
536 TECH_STACK,
537 TIMESTAMP,
538 SERVERNAME,
539 SERVERPORT,
540 JSERVPORT,
541 FLOWID,
542 SESSIONID,
543 USERID,
544 PROXYID,
545 APPID,
546 RESPID,
547 LANGID,
548 STARTT,
549 PAGENAME,
550 STATUSCODE,
551 EXECT,
552 THINKT,
553 PO,
554 OBJECT_VERSION_NUMBER,
555 CREATED_BY,
556 CREATION_DATE,
557 LAST_UPDATED_BY,
558 LAST_UPDATE_DATE
559 ) VALUES(
560 seqcnt2,
561 currpo.info.day,
562 currtechstack,
563 currpo.info.timestamp,
564 currpo.loc.servername,
565 currpo.loc.serverport,
566 currpo.loc.jservport,
567 null,
568 currpo.ses.sessionid,
572 currpo.ses.respid,
569 currpo.ses.userid,
570 currpo.ses.proxyid,
571 currpo.ses.appid,
573 currpo.ses.langid,
574 currpo.jsp.starttime,
575 currpo.jsp.NAME,
576 currpo.jsp.statusCode,
577 currpo.jsp.executionTime,
578 thinkT,
579 currpo.info.Recid,
580 0,690,currpo.info.day,690,currpo.info.timestamp);
581 END IF;
582 END IF;
583 END IF;
584 EXIT WHEN po_Cursor%NOTFOUND;
585 currpo := nextpo;
586 currtechstack := nexttechstack;
587 END LOOP;
588 CLOSE po_Cursor;
589 END MIGRATE_DATA_RAW;
590
591 /*============================================================================================================
592 | Wrapper FOR migrate_data TO be called BY Concurrent Manager
593 ============================================================================================================*/
594 PROCEDURE synchronize_pageflow_data
595 (ERRBUF OUT NOCOPY VARCHAR2,
596 RETCODE OUT NOCOPY NUMBER
597 )
598 IS
599 BEGIN
600
601 --call migrate api
602 JTF_PF_CONV_PKG.migrate_data(0);
603
604 EXCEPTION
605 WHEN OTHERS
606 THEN
607 RETCODE := 2;
608 ERRBUF := sqlcode||':'||sqlerrm;
609 fnd_file.put_line(fnd_file.log, 'JTF_PF_CONV_PKG.synchronize_pageflow_data failed ' || sqlcode||':'||sqlerrm);
610 END synchronize_pageflow_data;
611
612 FUNCTION GetParamNVs (po_id INTEGER)
613 RETURN JTF_VARCHAR2_TABLE_4000 IS
614 params JTF_PF_PARAMS_TABLE;
615 paramSz NUMBER;
616 nv JTF_PF_NVPAIR;
617 pname JTF_VARCHAR2_TABLE_4000 := JTF_VARCHAR2_TABLE_4000();
618 CURSOR cur(poid IN INTEGER) IS
619 SELECT x.pageobject.params
620 FROM JTF_PF_REPOSITORY x
621 WHERE x.pageobject.info.RecId = poid;
622 BEGIN
623 OPEN cur(po_id);
624 FETCH cur INTO params;
625 IF cur%NOTFOUND THEN
626 CLOSE cur;
627 RETURN NULL;
628 END IF;
629 CLOSE cur;
630
631 IF params IS NULL THEN
632 pname.EXTEND(1);
633 pname(1) := '';
634 RETURN pname;
635 END IF;
636
637 paramSz := params.COUNT;
638
639 pname.EXTEND(paramSz * 2);
640 FOR i IN 1..paramSz LOOP
641 nv := params(i);
642 pname(2 * i - 1) := nv.NAME;
643 pname(2 * i) := nv.value;
644 END LOOP;
645 RETURN pname;
646 END;
647
648 FUNCTION GetParamNames (params JTF_PF_PARAMS_TABLE, paramSz INTEGER)
649 RETURN JTF_VARCHAR2_TABLE_300 IS
650 nv JTF_PF_NVPAIR;
651 pname JTF_VARCHAR2_TABLE_300 := JTF_VARCHAR2_TABLE_300();
652 BEGIN
653 pname.EXTEND(paramSz);
654 FOR i IN 1..paramSz LOOP
655 nv := params(i);
656 pname(i) := nv.NAME;
657 END LOOP;
658 RETURN pname;
659 END;
660
661 FUNCTION GetParamValues (params JTF_PF_PARAMS_TABLE, paramSz INTEGER)
662 RETURN JTF_VARCHAR2_TABLE_4000 IS
663 nv JTF_PF_NVPAIR;
664 pval JTF_VARCHAR2_TABLE_4000 := JTF_VARCHAR2_TABLE_4000();
665 BEGIN
666 pval.EXTEND(paramSz);
667 FOR i IN 1..paramSz LOOP
668 nv := params(i);
669 pval(i) := nv.value;
670 END LOOP;
671 END;
672
673 PROCEDURE CLEAN_DATA(START_DATE DATE) IS
674 -- PRAGMA AUTONOMOUS_TRANSACTION;
675 TYPE RECTABTYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
676 RECTABTYPE1 RECTABTYPE;
677
678 CURSOR EBIZ_DATA IS
679 SELECT X.PAGEOBJECT.INFO.RECID FROM JTF_PF_REPOSITORY X WHERE
680 X.TRACK_PURPOSE IN (0,1) AND
681 X.LAST_UPDATE_DATE <= LEAST(START_DATE ,(SELECT LAST_RECORD_MIGRATED_TIME FROM JTF_PF_PURGEABLE WHERE TRACK_PURPOSE IN (0,1)));
682
683 CURSOR NON_EBIZ_DATA IS
684 SELECT X.PAGEOBJECT.INFO.RECID FROM JTF_PF_REPOSITORY X WHERE
685 X.TRACK_PURPOSE = 4 AND
686 X.LAST_UPDATE_DATE <= LEAST(START_DATE , (SELECT LAST_RECORD_MIGRATED_TIME FROM JTF_PF_PURGEABLE WHERE TRACK_PURPOSE = 4));
687
688 CURSOR WA_EBIZ_DATA IS
689 SELECT X.PAGEOBJECT.INFO.RECID FROM JTF_PF_REPOSITORY X WHERE
690 X.TRACK_PURPOSE = 2 AND
691 X.LAST_UPDATE_DATE <= LEAST( START_DATE, (SELECT LAST_RECORD_MIGRATED_TIME FROM JTF_PF_PURGEABLE WHERE TRACK_PURPOSE = 2));
692
693 CURSOR COMMON_DATA IS
694 SELECT X.PAGEOBJECT.INFO.RECID FROM JTF_PF_REPOSITORY X WHERE
695 X.TRACK_PURPOSE = 3 AND
696 X.LAST_UPDATE_DATE <= LEAST (START_DATE ,(SELECT MIN(LAST_RECORD_MIGRATED_TIME) FROM JTF_PF_PURGEABLE WHERE TRACK_PURPOSE IN(0,1,2)));
697
698 BEGIN
699 OPEN EBIZ_DATA;
700 LOOP
701 FETCH EBIZ_DATA BULK COLLECT INTO RECTABTYPE1 LIMIT 1000;
702 IF RECTABTYPE1.COUNT > 0 THEN
703 FORALL I IN RECTABTYPE1.FIRST .. RECTABTYPE1.LAST
704 DELETE FROM JTF_PF_REPOSITORY X WHERE X.PAGEOBJECT.INFO.RECID = RECTABTYPE1(I);
705 END IF;
706
707 EXIT WHEN EBIZ_DATA%NOTFOUND;
708 END LOOP;
709 RECTABTYPE1.DELETE;
710 CLOSE EBIZ_DATA;
711
712 OPEN NON_EBIZ_DATA;
713 LOOP
714 FETCH NON_EBIZ_DATA BULK COLLECT INTO RECTABTYPE1 LIMIT 1000;
715 IF RECTABTYPE1.COUNT > 0 THEN
716 FORALL I IN RECTABTYPE1.FIRST .. RECTABTYPE1.LAST
720 EXIT WHEN NON_EBIZ_DATA%NOTFOUND;
717 DELETE FROM JTF_PF_REPOSITORY X WHERE X.PAGEOBJECT.INFO.RECID = RECTABTYPE1(I);
718 END IF;
719
721 END LOOP;
722 RECTABTYPE1.DELETE;
723 CLOSE NON_EBIZ_DATA;
724
725 OPEN WA_EBIZ_DATA;
726 LOOP
727 FETCH WA_EBIZ_DATA BULK COLLECT INTO RECTABTYPE1 LIMIT 1000;
728 IF RECTABTYPE1.COUNT > 0 THEN
729 FORALL I IN RECTABTYPE1.FIRST .. RECTABTYPE1.LAST
730 DELETE FROM JTF_PF_REPOSITORY X WHERE X.PAGEOBJECT.INFO.RECID = RECTABTYPE1(I);
731 END IF;
732
733 EXIT WHEN WA_EBIZ_DATA%NOTFOUND;
734 END LOOP;
735 RECTABTYPE1.DELETE;
736 CLOSE WA_EBIZ_DATA;
737
738
739 OPEN COMMON_DATA;
740 LOOP
741 FETCH COMMON_DATA BULK COLLECT INTO RECTABTYPE1 LIMIT 1000;
742 IF RECTABTYPE1.COUNT > 0 THEN
743 FORALL I IN RECTABTYPE1.FIRST .. RECTABTYPE1.LAST
744 DELETE FROM JTF_PF_REPOSITORY X WHERE X.PAGEOBJECT.INFO.RECID = RECTABTYPE1(I);
745 END IF;
746 EXIT WHEN COMMON_DATA%NOTFOUND;
747 END LOOP;
748
749
750 RECTABTYPE1.DELETE;
751 CLOSE COMMON_DATA;
752 COMMIT;
753 END;
754
755 PROCEDURE PURGE_DATA(start_date IN DATE) IS
756 PRAGMA AUTONOMOUS_TRANSACTION;
757 TEMP_DATE DATE;
758 BEGIN
759 --dbms_output.put_line('purge data ');
760 TEMP_DATE:=(START_DATE-1);
761 CLEAN_DATA(TEMP_DATE);
762 DELETE FROM JTF_PF_SES_ACTIVITY WHERE DAY <= START_DATE;
763 DELETE FROM JTF_PF_ANON_ACTIVITY WHERE DAY <= START_DATE;
764 DELETE FROM JTF_PF_APP_SUMM WHERE DAY <= START_DATE;
765 DELETE FROM JTF_PF_HOST_SUMM WHERE DAY <= START_DATE;
766 DELETE FROM JTF_PF_PAGE_SUMM WHERE DAY <= START_DATE;
767 DELETE FROM JTF_PF_SESSION_SUMM WHERE DAY <= START_DATE;
768 DELETE FROM JTF_PF_USER_SUMM WHERE DAY <= START_DATE;
769 COMMIT;
770 END;
771
772 --6991900, changed date parameter to varchar2
773 PROCEDURE PURGE_DATA(ERRBUF OUT NOCOPY VARCHAR2, RETCODE OUT NOCOPY NUMBER, start_date_v IN varchar2) IS
774 PRAGMA AUTONOMOUS_TRANSACTION;
775 start_date date;
776 BEGIN
777 start_date := FND_CONC_DATE.STRING_TO_DATE(start_date_v);
778 PURGE_DATA(START_DATE);
779 EXCEPTION
780 WHEN OTHERS
781 THEN
782 RETCODE := 2;
783 ERRBUF := sqlcode||':'||sqlerrm;
784 fnd_file.put_line(fnd_file.log, 'JTF_PF_CONV_PKG.PURGE_DATA failed ' || sqlcode||':'||sqlerrm);
785 END;
786
787
788 PROCEDURE MULTIPLY_DATA(days NUMBER) IS
789 PRAGMA AUTONOMOUS_TRANSACTION;
790 CURSOR cur IS
791 SELECT MAX(x.pageobject.ses.sessionid) + 3
792 FROM JTF_PF_REPOSITORY_TMP x;
793 max_sessionid INTEGER;
794 BEGIN
795 DELETE FROM JTF_PF_REPOSITORY_TMP;
796 DELETE FROM JTF_PF_REPOSITORY x WHERE x.pageobject.info.day > sysdate;
797 COMMIT;
798
799 INSERT INTO JTF_PF_REPOSITORY_TMP(pageobject, object_version_number, created_by, creation_date, last_updated_by, last_update_date, last_update_login, security_group_id, tech_stack)
800 (SELECT pageobject, object_version_number, created_by, creation_date, last_updated_by, last_update_date, last_update_login, security_group_id, tech_stack FROM JTF_PF_REPOSITORY x
801 WHERE
802 x.pageobject.info.day > sysdate - 1
803 AND x.pageobject.info.day < sysdate);
804 COMMIT;
805
806 OPEN cur;
807 FETCH cur INTO max_sessionid;
808 IF(cur%NOTFOUND OR max_sessionid IS NULL) THEN
809 max_sessionid := 100000;
810 END IF;
811 CLOSE cur;
812
813 FOR i IN 1..days LOOP
814 UPDATE JTF_PF_REPOSITORY_TMP x
815 SET
816 x.pageobject.info.day = x.pageobject.info.day + 1,
817 x.pageobject.info.timestamp = x.pageobject.info.timestamp + 1,
818 x.pageobject.jsp.starttime = x.pageobject.jsp.starttime + 1000 * 60 * 60 * 24;
819 COMMIT;
820
821 UPDATE JTF_PF_REPOSITORY_TMP x
822 SET
823 x.pageobject.ses.sessionid = decode(x.pageobject.ses.sessionid,
824 NULL,x.pageobject.ses.sessionid,
825 -1,x.pageobject.ses.sessionid,
826 x.pageobject.ses.sessionid - max_sessionid)
827 WHERE
828 x.pageobject.ses IS NOT NULL;
829 COMMIT;
830
831 INSERT INTO JTF_PF_REPOSITORY (pageobject, object_version_number, created_by, creation_date, last_updated_by, last_update_date, last_update_login, security_group_id, tech_stack)
832 (SELECT pageobject, object_version_number, created_by, creation_date, last_updated_by, last_update_date, last_update_login, security_group_id, tech_stack FROM JTF_PF_REPOSITORY_TMP);
833 COMMIT;
834 END LOOP;
835 END;
836 Function GROUP_CONCAT ( list IN JTF_PF_TABLETYPE, separator VARCHAR2)
837 RETURN VARCHAR2 IS
838 ret VARCHAR2(1000) :='';
839 BEGIN
840 IF (list.COUNT > 0) THEN
841 FOR j IN list.FIRST..list.LAST LOOP
842 IF j = 1 THEN
843 ret := list(j);
844 ELSE
845 ret := ret || separator || list(j);
846 END IF;
847 END LOOP;
848 RETURN ret;
849 ELSE
850 RETURN ret;
851 END IF;
852 END;
853 PROCEDURE MIGRATED_DATA(TRK_PRPS NUMBER, LST_RCD_MGRTD_TM DATE) IS
854 TP NUMBER := -1;
855 BEGIN
856 SELECT COUNT(TRACK_PURPOSE) INTO TP FROM JTF_PF_PURGEABLE WHERE TRACK_PURPOSE = TRK_PRPS;
857 IF (TP > 0) THEN
861 END IF;
858 UPDATE JTF_PF_PURGEABLE SET LAST_RECORD_MIGRATED_TIME = LST_RCD_MGRTD_TM WHERE TRACK_PURPOSE = TRK_PRPS;
859 ELSE
860 INSERT INTO JTF_PF_PURGEABLE(TRACK_PURPOSE, LAST_RECORD_MIGRATED_TIME) VALUES(TRK_PRPS, LST_RCD_MGRTD_TM);
862 END MIGRATED_DATA;
863 END;
[Home] [Help]