[Home] [Help]
PACKAGE BODY: APPS.JTF_PF_CONV_PKG
Source
1 PACKAGE BODY JTF_PF_CONV_PKG AS
2 /* $Header: jtfpfconvpkgb.pls 120.15.12020000.6 2012/10/12 17:44:00 pdeluna 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(
33 objTab JTF_PF_PAGE_OBJECT_TABLE,
34 write_to_jtf INTEGER,
35 tech_stack_tbl JTF_PF_TECHSTACK_TABLE,
36 track_prps_tbl JTF_PF_TRACKPURPOSE_TABLE,
37 dbdate OUT NOCOPY DATE)
38 IS
39 PRAGMA AUTONOMOUS_TRANSACTION;
40 enqueue_options dbms_aq.enqueue_options_t;
41 message_properties dbms_aq.message_properties_t;
42 message_handle RAW(16);
43 objTabLocal JTF_PF_PAGE_OBJECT_TABLE;
44 obj JTF_PF_PAGE_OBJECT;
45 dummyPageObj JTF_PF_PAGE_OBJECT;
46 seq_num INTEGER;
47 enable_site varchar2(10) := NULL;
48 enable_app varchar2(10) := NULL;
49 enable_resp varchar2(10) := NULL;
50 enable_user varchar2(10) := NULL;
51 BEGIN
52 objTabLocal := JTF_PF_PAGE_OBJECT_TABLE();
53 FOR i IN 1..objTab.COUNT
54 LOOP
55 obj := objTab(i);
56 objTabLocal.EXTEND;
57 SELECT JTF_PF_SEQ.NEXTVAL INTO seq_num FROM dual;
58 objTabLocal(i) := JTF_PF_PAGE_OBJECT(
59 JTF_PF_INFO_OBJECT(seq_num,obj.info.Day,obj.info.Timestamp),
60 obj.dpf, obj.ses, obj.jsp, obj.loc, obj.lang, obj.client,
61 obj.params, obj.cookies, obj.headers, obj.prodinfo);
62 END LOOP;
63
64 SELECT sysdate INTO dbdate FROM dual;
65
66 IF (write_to_jtf = 1) THEN
67 FORALL indx IN objTabLocal.FIRST .. objTabLocal.LAST
68 INSERT INTO jtf_pf_repository(
69 pageobject,
70 object_version_number,
71 created_by,
72 creation_date,
73 last_updated_by,
74 last_update_date,
75 tech_stack,
76 track_purpose)
77 VALUES (
78 objTabLocal(indx),
79 0,
80 690,
81 sysdate,
82 690,
83 sysdate,
84 tech_stack_tbl(indx),
85 track_prps_tbl(indx));
86 ELSE
87 message_properties.correlation := seq_num;
88 message_properties.expiration := 24*60*60; -- messages expire in one day
89 -- after first dequeue
90 dbms_aq.enqueue(queue_name => qname,
91 enqueue_options => enqueue_options,
92 message_properties => message_properties,
93 payload => dummyPageObj,
94 msgid => message_handle);
95 END IF;
96 COMMIT;
97 END writePFObjectTable;
98
99 /*===========================================================================
100 | Writes THE Page flow data coming IN FROM THE JAVA layer INTO Advanced
101 | Queue INTO JTF_PF_PARAMS_TABLE
102 | This IS an autonomous TRANSACTION i.e. It does NOT matter whether THE
103 | parent TRANSACTION commited OR rolled back , this TRANSACTION will
104 | COMMIT IF ALL goes well
105 ===========================================================================*/
106 PROCEDURE writePFObject(
107 obj JTF_PF_PAGE_OBJECT,
108 write_to_jtf INTEGER,
109 tech_stack VARCHAR2,
110 track_purpose VARCHAR2,
111 dbdate OUT NOCOPY DATE)
112 IS
113 PRAGMA AUTONOMOUS_TRANSACTION;
114 pObject JTF_PF_PAGE_OBJECT;
115 enqueue_options dbms_aq.enqueue_options_t;
116 message_properties dbms_aq.message_properties_t;
117 message_handle RAW(16);
118 dummyPageObj JTF_PF_PAGE_OBJECT;
119 seq_num INTEGER;
120 enable_site varchar2(10) := NULL;
121 enable_app varchar2(10) := NULL;
122 enable_resp varchar2(10) := NULL;
123 enable_user varchar2(10) := NULL;
124 BEGIN
125 SELECT JTF_PF_SEQ.NEXTVAL INTO seq_num FROM dual;
126 SELECT sysdate INTO dbdate FROM dual;
127 dummyPageObj := JTF_PF_PAGE_OBJECT(
128 JTF_PF_INFO_OBJECT(seq_num,obj.info.Day,obj.info.Timestamp), obj.dpf,
129 obj.ses, obj.jsp, obj.loc, obj.lang, obj.client, obj.params,
130 obj.cookies, obj.headers, obj.prodinfo);
131
132 IF (write_to_jtf = 1) THEN
133 INSERT INTO jtf_pf_repository(
134 pageobject,
135 object_version_number,
136 created_by,
137 creation_date,
138 last_updated_by,
139 last_update_date,
140 tech_stack,
141 track_purpose)
142 VALUES (
143 dummyPageObj,
144 0,
145 690,
146 obj.info.Day,
147 690,
148 obj.info.Timestamp,
149 tech_stack,
150 track_purpose);
151 ELSE
152 message_properties.correlation := seq_num;
153 message_properties.expiration := 24*60*60; -- messages expire in one day
154 -- after first dequeue
155 dbms_aq.enqueue(queue_name => qname,
156 enqueue_options => enqueue_options,
157 message_properties => message_properties,
158 payload => dummyPageObj,
159 msgid => message_handle);
160 END IF;
161
162 COMMIT;
163 END writePFObject;
164
165 /* ==========================================================================
166 | Converts THE param names AND param VALUES, coming IN AS 2 separate arrays,
167 | FROM THE JAVA layer, INTO JTF_PF_PARAMS_TABLE |
168 | This IS used TO construct THE JTF_PF_PAGE_OBJECT |
169 ============================================================================*/
170 FUNCTION GetParams(
171 paramNames JTF_VARCHAR2_TABLE_300,
172 paramValues JTF_VARCHAR2_TABLE_4000,
173 paramSz INTEGER)
174 RETURN JTF_PF_PARAMS_TABLE
175 IS
176 params JTF_PF_PARAMS_TABLE := JTF_PF_PARAMS_TABLE();
177 BEGIN
178 FOR i IN 1..paramSz LOOP
179 params.EXTEND;
180 params(i) := JTF_PF_NVPAIR(paramNames(i),paramValues(i));
181 END LOOP;
182 RETURN params;
183 END;
184 /* ==========================================================================
185 | Converts THE param names AND param VALUES, coming IN AS 2 seperate arrays,|
186 | FROM THE JAVA layer, INTO JTF_PF_PRODPARAMS_TABLE |
187 | This IS used TO construct THE JTF_PF_PRODINFO_OBJECT |
188 ============================================================================*/
189 FUNCTION GetProdParams (
190 paramNames JTF_VARCHAR2_TABLE_300,
191 paramValues JTF_VARCHAR2_TABLE_4000,
192 paramSz INTEGER)
193 RETURN JTF_PF_PRODPARAMS_TABLE
194 IS
195 params JTF_PF_PRODPARAMS_TABLE := JTF_PF_PRODPARAMS_TABLE();
196 BEGIN
197 FOR i IN 1..paramSz LOOP
198 params.EXTEND;
199 params(i) := JTF_PF_NVPAIR(paramNames(i),paramValues(i));
200 END LOOP;
201 RETURN params;
202 END;
203 /* ==========================================================================
204 | Converts THE param names AND param VALUES, |
205 | coming IN AS an ARRAY OF NVPAIRS INTO a CLOB FOR clickstream support |
206 ============================================================================*/
207 FUNCTION GetParamString(params JTF_PF_PARAMS_TABLE)
208 RETURN CLOB IS
209 paramCLOB CLOB;
210 paramV VARCHAR(32767);
211 nv JTF_PF_NVPAIR;
212 paramSz NUMBER;
213 psize NUMBER;
214 offset NUMBER;
215 i NUMBER;
216 BEGIN
217 dbms_lob.createtemporary(paramCLOB,TRUE,DBMS_LOB.CALL);
218 psize := 0;
219 IF(params IS NULL) THEN
220 RETURN paramCLOB;
221 END IF;
222 paramSz := params.COUNT;
223
224 FOR i IN 1..paramSz LOOP
225 nv := params(i);
226 IF i <> 1 THEN
227 paramV := '&' || nv.NAME || '=' || nv.value;
228 ELSE
229 paramV := nv.NAME || '=' || nv.value;
230 END IF;
231 IF paramV IS NOT NULL THEN
232 psize := length(paramV);
233 offset := DBMS_LOB.GETLENGTH(paramClob) + 1;
234 DBMS_LOB.WRITE(paramCLOB,psize,offset,paramV);
235 END IF;
236 END LOOP;
237 RETURN paramCLOB;
238 END;
239 /* ========================================================================
240 | Converts THE param names AND param VALUES, |
241 | coming IN AS an ARRAY OF NVPAIRS INTO a CLOB FOR clickstream support |
242 ==========================================================================*/
243 FUNCTION GetCookieString(params JTF_PF_COOKIES_TABLE)
244 RETURN CLOB IS
245 paramCLOB CLOB;
246 paramV VARCHAR(32767);
247 nvs JTF_PF_NVSTRIPLET;
248 paramSz NUMBER;
249 psize NUMBER;
250 offset NUMBER;
251 i NUMBER;
252 BEGIN
253 dbms_lob.createtemporary(paramCLOB,TRUE,DBMS_LOB.CALL);
254 psize := 0;
255 IF(params IS NULL) THEN
256 RETURN paramCLOB;
257 END IF;
258 paramSz := params.COUNT;
259
260 FOR i IN 1..paramSz LOOP
261 nvs := params(i);
262 IF i <> 1 THEN
263 paramV := ' ' || nvs.NAME || '=' || nvl(nvs.value, 'JTF_PF_SZ:'
264 || nvs.length) || ';';
265 ELSE
266 paramV := nvs.NAME || '=' ||nvl(nvs.value, 'JTF_PF_SZ:'|| nvs.length)
267 || ';';
268 END IF;
269 IF paramV IS NOT NULL THEN
270 psize := length(paramV);
271 offset := DBMS_LOB.GETLENGTH(paramClob) + 1;
272 DBMS_LOB.WRITE(paramCLOB,psize,offset,paramV);
273 END IF;
274 END LOOP;
275 RETURN paramCLOB;
276 END;
277 /* ==========================================================================
278 | Converts THE cookie names, cookie sizes AND cokie VALUES, coming IN AS
279 | 3 separate arrays,FROM THE JAVA layer, INTO JTF_PF_COOKIES_TABLE
280 | This IS used TO construct THE JTF_PF_PAGE_OBJECT
281 ============================================================================*/
282 FUNCTION GetCookies(
283 cookieNames JTF_VARCHAR2_TABLE_300,
284 cookieValues JTF_VARCHAR2_TABLE_4000,
285 cookieSizes JTF_NUMBER_TABLE,
286 cookieSz INTEGER)
287 RETURN JTF_PF_COOKIES_TABLE
288 IS
289 cookie JTF_PF_COOKIES_TABLE := JTF_PF_COOKIES_TABLE();
290 BEGIN
291 cookie.EXTEND(cookieSz);
292 FOR i IN 1..cookieSz LOOP
293 cookie(i) := JTF_PF_NVSTRIPLET(cookieNames(i), cookieValues(i),
294 cookieSizes(i));
295 END LOOP;
296 RETURN cookie;
297 END;
298 /*===========================================================================
299 | Upload THE data IN AQ periodically TO THE JTF Repository |
300 ============================================================================*/
301 PROCEDURE uploadAllNewPfObjects --ang This proc is currently not being invoked
302 IS --anywhere
303 pgObjects JTF_PF_PAGE_OBJECT ;
304 new_messages BOOLEAN :=TRUE;
305 dopt dbms_aq.dequeue_options_t;
306 mprop dbms_aq.message_properties_t;
307 deq_msgid RAW(16);
308 no_messages EXCEPTION;
309 last_msg_in_jtf_rep INTEGER;
310 last_msg_to_upload INTEGER;
311 current_msg INTEGER;
312 QUERY VARCHAR2(2000);
313 PRAGMA EXCEPTION_INIT(NO_MESSAGES,-25228);
314 PRAGMA AUTONOMOUS_TRANSACTION;
315 enable_site varchar2(10) := NULL;
316 enable_app varchar2(10) := NULL;
317 enable_resp varchar2(10) := NULL;
318 enable_user varchar2(10) := NULL;
319 BEGIN
320 SELECT max(a.pageobject.info.recid)
321 INTO last_msg_in_jtf_rep
322 FROM jtf_pf_repository a;
323 -- select max(a.corrid) into last_msg_to_upload from JTF_PF_LOGGING_TABLE a;
324
325 IF last_msg_in_jtf_rep IS NOT NULL THEN
326 current_msg := last_msg_in_jtf_rep;
327 END IF;
328
329 dopt.navigation := dbms_aq.first_message;
330 dopt.dequeue_mode := DBMS_AQ.BROWSE;
331 dopt.wait := 0;
332
333 WHILE (new_messages) LOOP
334 BEGIN
335 IF(current_msg IS NOT NULL) THEN
336 current_msg := current_msg + 1;
337 IF (current_msg > last_msg_to_upload) THEN
338 new_messages := FALSE;
339 END IF;
340 dopt.correlation := current_msg;
341 END IF;
342
343 dbms_aq.dequeue(queue_name => qname,
344 dequeue_options => dopt,
345 message_properties => mprop,
346 payload => pgObjects,
347 msgid => deq_msgid);
348
349 current_msg := mprop.correlation;
350 dopt.navigation := dbms_aq.first_message;
351 dopt.wait := 0;
352
353 INSERT INTO jtf_pf_repository( --ang no need to update this insert
354 pageobject, --as the proc is not in use
355 object_version_number,
356 created_by,
357 creation_date,
358 last_updated_by,
359 last_update_date)
360 VALUES(
361 pgObjects,
362 0,
363 690,
364 pgObjects.info.Day,
365 690,
366 pgObjects.info.Timestamp);
367 -- dbms_output.put_line('inserted ' || mprop.correlation);
368 EXCEPTION
369 WHEN NO_MESSAGES THEN
370 IF(current_msg IS NOT NULL) THEN
371 current_msg := current_msg + 1;
372 IF(current_msg > last_msg_to_upload) THEN
373 -- dbms_output.put_line('No more messages to dequeue');
374 new_messages := FALSE;
375 COMMIT;
376 END IF;
377 dopt.correlation := current_msg;
378 END IF;
379 WHEN OTHERS THEN
380 new_messages :=FALSE;
381 -- raise_application_error(-20101, SQLERRM);
382 -- dbms_output.put_line(SQLERRM);
383 END;
384 END LOOP;
385 END;
386 /*===========================================================================
387 | This procedure is for migrating data from the JTF_PF_REPOSITORY (OLTP Stage
388 | Area) to the OLAP Tables (JTF_PF_ANON_ACTIVITY, JTF_PF_SES_ACTIVITY).
389 ============================================================================*/
390 PROCEDURE MIGRATE_DATA(timezone_offset IN NUMBER)
391 IS
392 today DATE;
393 BEGIN
394 SELECT trunc(sysdate)
395 INTO today
396 FROM dual;
397
398 JTF_PF_SOA_MIGRATE_PKG.MIGRATE_LOGINS_DATA(timezone_offset);
399 JTF_PF_SOA_MIGRATE_PKG.MIGRATE_RESP_DATA(timezone_offset);
400 JTF_PF_SOA_MIGRATE_PKG.MIGRATE_FORMS_DATA(timezone_offset);
401 migrate_data_raw;
402 migrate_data_stats(today);
403 migrated_data(1,sysdate);
404 COMMIT;
405 END MIGRATE_DATA;
406 /*===========================================================================
407 | This procedure is for migrating data stats
408 |
409 ============================================================================*/
410 PROCEDURE MIGRATE_DATA_STATS(today DATE)
411 IS
412 BEGIN
413 IF (last_migrate_day IS NULL) THEN
414 SELECT min(x.day)
415 INTO last_migrate_day
416 FROM (SELECT max(day) AS day
417 FROM JTF_PF_APP_SUMM
418 UNION ALL
419 SELECT max(day) AS day
420 FROM JTF_PF_HOST_SUMM
421 UNION ALL
422 SELECT max(day) AS day
423 FROM JTF_PF_PAGE_SUMM
424 UNION ALL
425 SELECT max(day) AS day
426 FROM JTF_PF_SESSION_SUMM
427 UNION ALL
428 SELECT max(day) AS day
429 FROM JTF_PF_USER_SUMM) x;
430 END IF;
431
432 IF (last_migrate_day IS NOT NULL) THEN
433 --DELETE FROM JTF_PF_ANON_ACTIVITY WHERE day < last_migrate_day;
434 --DELETE FROM JTF_PF_SES_ACTIVITY WHERE day < last_migrate_day;
435 DELETE FROM JTF_PF_APP_SUMM
436 WHERE day >= last_migrate_day;
437
438 DELETE FROM JTF_PF_HOST_SUMM
439 WHERE day >= last_migrate_day;
440
441 DELETE FROM JTF_PF_PAGE_SUMM
442 WHERE day >= last_migrate_day;
443
444 DELETE FROM JTF_PF_SESSION_SUMM
445 WHERE (sessionid IS NULL and day >= last_migrate_day)
446 OR (sessionid IS NOT NULL
447 and sessionid IN
448 (select distinct sessionid
449 from JTF_PF_SES_ACTIVITY
450 where day >= last_migrate_day
451 OR last_migrate_day IS NULL));
452
453 DELETE FROM JTF_PF_USER_SUMM
454 WHERE day >= last_migrate_day;
455 END IF;
456
457 -- ADDED to fix bug no-5036611 (START HERE)
458 IF (last_migrate_day is null) THEN
459
460 INSERT INTO JTF_PF_APP_SUMM(day, cnt_pages_jtf, cnt_pages_oaf,
461 cnt_pages_form, cnt_pages_all, cnt_flows, cnt_users, cnt_apps,
462 cnt_resps, cnt_sessions, cnt_langs)
463 (SELECT day, cnt_pages_jtf, cnt_pages_oaf, cnt_pages_form,
464 cnt_pages_jtf+cnt_pages_oaf+cnt_pages_form, cnt_flows, cnt_users,
465 cnt_apps, cnt_resps, cnt_sessions, cnt_langs
466 FROM JTF_PF_APP_SUMMARY_VL);
467
468 INSERT INTO JTF_PF_HOST_SUMM (day, servername, serverport, jservs,
469 sum_exect_jtf, sum_exect_oaf, pagehits_jtf, pagehits_oaf, pagehits_all,
470 fails, pages, badpages)
471 (SELECT day, NVL(servername, 'N/A'), NVL(serverport, -1), jservs,
472 sum_exect_jtf, sum_exect_oaf, pagehits_jtf, pagehits_oaf,
473 pagehits_jtf+pagehits_oaf, fails, pages, badpages
474 FROM JTF_PF_HOST_SUMMARY_VL);
475
476 INSERT INTO JTF_PF_PAGE_SUMM (day, pagename, tech_stack, cnt_pages,
477 cnt_ses, ucnt_ses, ucnt_users, ucnt_apps, ucnt_resps, ucnt_langs,
478 sum_exect, sum_thinkt, cnt_thinkt, startt, endt, cnt_fail, cnt_forward)
479 (SELECT day, pagename, tech_stack, cnt_pages, cnt_ses, ucnt_ses,
480 ucnt_users, ucnt_apps, ucnt_resps, ucnt_langs, sum_exect, sum_thinkt,
481 cnt_thinkt, startt, endt, cnt_fail, cnt_forward
482 FROM JTF_PF_PAGE_SUMMARY_VL);
483
484 INSERT INTO JTF_PF_SESSION_SUMM (day, seqid, sessionid, user_name, userid,
485 sum_exect_jtf, sum_exect_oaf, sum_exect_form, sum_thinkt_jtf,
486 sum_thinkt_oaf, sum_thinkt_form, cnt_pages_jtf, cnt_pages_oaf,
487 cnt_pages_form, cnt_pages_all, ucnt_pages, ucnt_flows, ucnt_users,
488 ucnt_apps, ucnt_resps, ucnt_langs, startt, endt, cnt_fail)
489 (SELECT day, seqid, sessionid, user_name, userid, sum_exect_jtf,
490 sum_exect_oaf, sum_exect_form, sum_thinkt_jtf, sum_thinkt_oaf,
491 sum_thinkt_form, cnt_pages_jtf, cnt_pages_oaf, cnt_pages_form,
492 cnt_pages_jtf+cnt_pages_oaf+cnt_pages_form, ucnt_pages, ucnt_flows,
493 ucnt_users, ucnt_apps, ucnt_resps, ucnt_langs, startt, endt, cnt_fail
494 FROM JTF_PF_SESSION_SUMMARY_VL
495 WHERE (sessionid IS NULL )
496 or (sessionid IS NOT NULL
497 and sessionid IN
498 (select distinct sessionid
499 from JTF_PF_SES_ACTIVITY)));
500
501 INSERT INTO JTF_PF_USER_SUMM (day, user_name, userid, sum_exect_jtf,
502 sum_exect_oaf, sum_exect_form, sum_thinkt_jtf, sum_thinkt_oaf,
503 sum_thinkt_form, cnt_pages_jtf, cnt_pages_oaf, cnt_pages_form,
504 cnt_pages_all, ucnt_ses, ucnt_pages, ucnt_apps, ucnt_resps, ucnt_langs,
505 startt, endt, cnt_fail)
506 (SELECT day, user_name, userid, sum_exect_jtf, sum_exect_oaf,
507 sum_exect_form, sum_thinkt_jtf, sum_thinkt_oaf, sum_thinkt_form,
508 cnt_pages_jtf, cnt_pages_oaf, cnt_pages_form,
509 cnt_pages_jtf+cnt_pages_oaf+cnt_pages_form, ucnt_ses, ucnt_pages,
510 ucnt_apps, ucnt_resps, ucnt_langs, startt, endt, cnt_fail
511 FROM JTF_PF_USER_SUMMARY_VL);
512
513 ELSE
514
515 INSERT INTO JTF_PF_APP_SUMM (day, cnt_pages_jtf, cnt_pages_oaf,
516 cnt_pages_form, cnt_pages_all, cnt_flows, cnt_users, cnt_apps,
517 cnt_resps, cnt_sessions, cnt_langs)
518 (SELECT day, cnt_pages_jtf, cnt_pages_oaf, cnt_pages_form,
519 cnt_pages_jtf+cnt_pages_oaf+cnt_pages_form, cnt_flows, cnt_users,
520 cnt_apps, cnt_resps, cnt_sessions, cnt_langs
521 FROM JTF_PF_APP_SUMMARY_VL
522 WHERE day >= last_migrate_day);
523
524 INSERT INTO JTF_PF_HOST_SUMM (day, servername, serverport, jservs,
525 sum_exect_jtf, sum_exect_oaf, pagehits_jtf, pagehits_oaf, pagehits_all,
526 fails, pages, badpages)
527 (SELECT day, NVL(servername, 'N/A'), NVL(serverport, -1), jservs,
528 sum_exect_jtf, sum_exect_oaf, pagehits_jtf, pagehits_oaf,
529 pagehits_jtf+pagehits_oaf, fails, pages, badpages
530 FROM JTF_PF_HOST_SUMMARY_VL
531 WHERE day >= last_migrate_day);
532
533 INSERT INTO JTF_PF_PAGE_SUMM (day, pagename, tech_stack, cnt_pages,
534 cnt_ses, ucnt_ses, ucnt_users, ucnt_apps, ucnt_resps, ucnt_langs,
535 sum_exect, sum_thinkt, cnt_thinkt, startt, endt, cnt_fail, cnt_forward)
536 (SELECT day, pagename, tech_stack, cnt_pages, cnt_ses, ucnt_ses,
537 ucnt_users, ucnt_apps, ucnt_resps, ucnt_langs, sum_exect, sum_thinkt,
538 cnt_thinkt, startt, endt, cnt_fail, cnt_forward
539 FROM JTF_PF_PAGE_SUMMARY_VL
540 WHERE day >= last_migrate_day);
541
542 INSERT INTO JTF_PF_SESSION_SUMM (day, seqid, sessionid, user_name, userid,
543 sum_exect_jtf, sum_exect_oaf, sum_exect_form, sum_thinkt_jtf,
544 sum_thinkt_oaf, sum_thinkt_form, cnt_pages_jtf, cnt_pages_oaf,
545 cnt_pages_form, cnt_pages_all, ucnt_pages, ucnt_flows, ucnt_users,
546 ucnt_apps, ucnt_resps, ucnt_langs, startt, endt, cnt_fail)
547 (SELECT day, seqid, sessionid, user_name, userid, sum_exect_jtf,
548 sum_exect_oaf, sum_exect_form, sum_thinkt_jtf, sum_thinkt_oaf,
549 sum_thinkt_form, cnt_pages_jtf, cnt_pages_oaf, cnt_pages_form,
550 cnt_pages_jtf+cnt_pages_oaf+cnt_pages_form, ucnt_pages, ucnt_flows,
551 ucnt_users, ucnt_apps, ucnt_resps, ucnt_langs, startt, endt, cnt_fail
552 FROM JTF_PF_SESSION_SUMMARY_VL
553 WHERE (sessionid IS NULL and day >= last_migrate_day)
554 or (sessionid IS NOT NULL
555 and sessionid IN
556 (select distinct sessionid
557 from JTF_PF_SES_ACTIVITY
558 where day >= last_migrate_day )));
559
560 INSERT INTO JTF_PF_USER_SUMM (day, user_name, userid, sum_exect_jtf,
561 sum_exect_oaf, sum_exect_form, sum_thinkt_jtf, sum_thinkt_oaf,
562 sum_thinkt_form, cnt_pages_jtf, cnt_pages_oaf, cnt_pages_form,
563 cnt_pages_all, ucnt_ses, ucnt_pages, ucnt_apps, ucnt_resps, ucnt_langs,
564 startt, endt, cnt_fail)
565 (SELECT day, user_name, userid, sum_exect_jtf, sum_exect_oaf,
566 sum_exect_form, sum_thinkt_jtf, sum_thinkt_oaf, sum_thinkt_form,
567 cnt_pages_jtf, cnt_pages_oaf, cnt_pages_form,
568 cnt_pages_jtf+cnt_pages_oaf+cnt_pages_form, ucnt_ses, ucnt_pages,
569 ucnt_apps, ucnt_resps, ucnt_langs, startt, endt, cnt_fail
570 FROM JTF_PF_USER_SUMMARY_VL
571 WHERE day >= last_migrate_day);
572
573 END IF;
574 -- ADDED To fix bug no-5036611 (END HERE)
575
576 last_migrate_day := today;
577 END MIGRATE_DATA_STATS;
578 /*===========================================================================
579 | This procedure is for migrating data stats
580 |
581 ============================================================================*/
582 PROCEDURE MIGRATE_DATA_RAW
583 IS
584 v_SQLString VARCHAR2(300);
585
586 CURSOR po_Cursor(starting_po IN INTEGER) IS
587 SELECT x.pageobject, x.tech_stack
588 FROM JTF_PF_REPOSITORY x
589 WHERE x.pageobject.info.RecId > starting_po and
590 x.track_purpose not in (2, 4)
591 ORDER BY x.pageobject.ses.SessionId, x.pageobject.jsp.StartTime;
592
593 CURSOR cur_val IS
594 SELECT nvl(max_po,-100000000000), nvl(ses_seqid,0), nvl(anon_seqid,0)
595 FROM JTF_PF_SEQ_VL;
596
597 nextpo JTF_PF_PAGE_OBJECT;
598 currpo JTF_PF_PAGE_OBJECT;
599 thinkT INTEGER;
600 seqcnt1 INTEGER;
601 seqcnt2 INTEGER;
602 maxpo INTEGER;
603 currtechstack varchar2(20);
604 nexttechstack VARCHAR2(20);
605 BEGIN
606 OPEN cur_val;
607 FETCH cur_val INTO maxpo, seqcnt2, seqcnt1;
608 CLOSE cur_val;
609
610 currpo := NULL;
611
612 OPEN po_Cursor(maxpo);
613 LOOP
614 FETCH po_Cursor INTO nextpo, nexttechstack;
615 -- if record exists in the cursor (previous iteration)
616 IF currpo IS NOT NULL THEN
617 -- for anonymous activity
618 IF currpo.ses IS NULL THEN
619 IF currpo.jsp IS NOT NULL
620 AND currpo.loc IS NOT NULL
621 AND currpo.info IS NOT NULL THEN
622 seqcnt1 := seqcnt1 - 1;
623 INSERT INTO JTF_PF_ANON_ACTIVITY(
624 SEQID,
625 DAY,
626 TECH_STACK,
627 TIMESTAMP,
628 SERVERNAME,
629 SERVERPORT,
630 JSERVPORT,
631 FLOWID,
632 SESSIONID,
633 USERID,
634 PROXYID,
635 APPID,
636 RESPID,
637 LANGID,
638 STARTT,
639 PAGENAME,
640 STATUSCODE,
641 EXECT,
642 THINKT,
643 PO,
644 OBJECT_VERSION_NUMBER,
645 CREATED_BY,
646 CREATION_DATE,
647 LAST_UPDATED_BY,
648 LAST_UPDATE_DATE)
649 VALUES(
650 seqcnt1,
651 currpo.info.day,
652 currtechstack,
653 currpo.info.timestamp,
654 currpo.loc.servername,
655 currpo.loc.serverport,
656 currpo.loc.jservport,
657 null,
658 null,
659 null,
660 null,
661 null,
662 null,
663 null,
664 NVL(currpo.jsp.starttime, -1), -- ANG - Remove NVL condition,
665 -- still what to do when starttime
666 -- is null
667 NVL(currpo.jsp.NAME, 'N/A'), -- ANG - Remove NVL condition, again
668 -- there are records in repository
669 -- table where the value for name
670 -- coln is null. How should this be
671 -- handled?
672 NVL(currpo.jsp.statusCode, -1), -- ANG - need to get rid of NVL
673 -- condition
674 NVL(currpo.jsp.executionTime, -1), -- ANG - need to get rid of NVL
675 -- condition
676 thinkT,
677 currpo.info.Recid,
678 0,
679 690,
680 currpo.info.day,
681 690,
682 currpo.info.timestamp);
683 END IF;
684 ELSE
685 -- for session activity
686 thinkT := NULL;
687 IF NOT(currpo.jsp.statusCode = -200)
688 AND currpo.ses.SessionId = nextpo.ses.SessionId
689 AND NOT(po_Cursor%NOTFOUND) THEN
690 thinkT := (nextpo.jsp.StartTime - (currpo.jsp.StartTime + currpo.jsp.executionTime));
691 IF thinkT IS NOT NULL AND thinkT <= 0 THEN
692 thinkT := NULL;
693 END IF;
694 END IF;
695
696 IF currpo.jsp IS NOT NULL
697 AND currpo.loc IS NOT NULL
698 AND currpo.info IS NOT NULL
699 AND currtechstack IS NOT NULL
700 AND currpo.info.day IS NOT NULL
701 AND currpo.info.timestamp IS NOT NULL
702 AND currpo.info.Recid IS NOT NULL
703 AND currpo.ses.sessionid IS NOT NULL
704 AND currpo.ses.userid IS NOT NULL
705 AND currpo.ses.appid IS NOT NULL
706 AND currpo.ses.respid IS NOT NULL
707 AND currpo.ses.langid IS NOT NULL
708 AND currpo.jsp.starttime IS NOT NULL
709 AND currpo.jsp.NAME IS NOT NULL
710 AND currpo.jsp.statusCode IS NOT NULL
711 AND currpo.jsp.executionTime IS NOT NULL THEN
712 seqcnt2 := seqcnt2 + 1;
713 INSERT INTO JTF_PF_SES_ACTIVITY(
714 SEQID,
715 DAY,
716 TECH_STACK,
717 TIMESTAMP,
718 SERVERNAME,
719 SERVERPORT,
720 JSERVPORT,
721 FLOWID,
722 SESSIONID,
723 USERID,
724 PROXYID,
725 APPID,
726 RESPID,
727 LANGID,
728 STARTT,
729 PAGENAME,
730 STATUSCODE,
731 EXECT,
732 THINKT,
733 PO,
734 OBJECT_VERSION_NUMBER,
735 CREATED_BY,
736 CREATION_DATE,
737 LAST_UPDATED_BY,
738 LAST_UPDATE_DATE)
739 VALUES(
740 seqcnt2,
741 currpo.info.day,
742 currtechstack,
743 currpo.info.timestamp,
744 currpo.loc.servername,
745 currpo.loc.serverport,
746 currpo.loc.jservport,
747 null,
748 currpo.ses.sessionid,
749 currpo.ses.userid,
750 currpo.ses.proxyid,
751 currpo.ses.appid,
752 currpo.ses.respid,
753 currpo.ses.langid,
754 currpo.jsp.starttime,
755 currpo.jsp.NAME,
756 currpo.jsp.statusCode,
757 currpo.jsp.executionTime,
758 thinkT,
759 currpo.info.Recid,
760 0,
761 690,
762 currpo.info.day,
763 690,
764 currpo.info.timestamp);
765 END IF;
766 END IF;
767 END IF;
768 EXIT WHEN po_Cursor%NOTFOUND;
769 currpo := nextpo;
770 currtechstack := nexttechstack;
771 END LOOP;
772 CLOSE po_Cursor;
773 END MIGRATE_DATA_RAW;
774
778 PROCEDURE synchronize_pageflow_data(
775 /*===========================================================================
776 | Wrapper FOR migrate_data TO be called BY Concurrent Manager
777 ============================================================================*/
779 ERRBUF OUT NOCOPY VARCHAR2,
780 RETCODE OUT NOCOPY NUMBER)
781 IS
782 BEGIN
783 --call migrate api
784 JTF_PF_CONV_PKG.migrate_data(0);
785 EXCEPTION
786 WHEN OTHERS THEN
787 RETCODE := 2;
788 ERRBUF := sqlcode||':'||sqlerrm;
789 fnd_file.put_line(fnd_file.log,
790 'JTF_PF_CONV_PKG.synchronize_pageflow_data failed ' || sqlcode||':'
791 ||sqlerrm);
792 END synchronize_pageflow_data;
793 /*===========================================================================
794 |
795 ============================================================================*/
796 FUNCTION GetParamNVs (po_id INTEGER)
797 RETURN JTF_VARCHAR2_TABLE_4000
798 IS
799 params JTF_PF_PARAMS_TABLE;
800 paramSz NUMBER;
801 nv JTF_PF_NVPAIR;
802 pname JTF_VARCHAR2_TABLE_4000 := JTF_VARCHAR2_TABLE_4000();
803
804 CURSOR cur(poid IN INTEGER) IS
805 SELECT x.pageobject.params
806 FROM JTF_PF_REPOSITORY x
807 WHERE x.pageobject.info.RecId = poid;
808 BEGIN
809 OPEN cur(po_id);
810 FETCH cur INTO params;
811 IF cur%NOTFOUND THEN
812 CLOSE cur;
813 RETURN NULL;
814 END IF;
815 CLOSE cur;
816
817 IF params IS NULL THEN
818 pname.EXTEND(1);
819 pname(1) := '';
820 RETURN pname;
821 END IF;
822
823 paramSz := params.COUNT;
824 pname.EXTEND(paramSz * 2);
825
826 FOR i IN 1..paramSz LOOP
827 nv := params(i);
828 pname(2 * i - 1) := nv.NAME;
829 pname(2 * i) := nv.value;
830 END LOOP;
831 RETURN pname;
832 END;
833 /*===========================================================================
834 |
835 ============================================================================*/
836 FUNCTION GetParamNames (params JTF_PF_PARAMS_TABLE, paramSz INTEGER)
837 RETURN JTF_VARCHAR2_TABLE_300
838 IS
839 nv JTF_PF_NVPAIR;
840 pname JTF_VARCHAR2_TABLE_300 := JTF_VARCHAR2_TABLE_300();
841 BEGIN
842 pname.EXTEND(paramSz);
843 FOR i IN 1..paramSz LOOP
844 nv := params(i);
845 pname(i) := nv.NAME;
846 END LOOP;
847 RETURN pname;
848 END;
849 /*===========================================================================
850 |
851 ============================================================================*/
852 FUNCTION GetParamValues (params JTF_PF_PARAMS_TABLE, paramSz INTEGER)
853 RETURN JTF_VARCHAR2_TABLE_4000 IS
854 nv JTF_PF_NVPAIR;
855 pval JTF_VARCHAR2_TABLE_4000 := JTF_VARCHAR2_TABLE_4000();
856 BEGIN
857 pval.EXTEND(paramSz);
858 FOR i IN 1..paramSz LOOP
859 nv := params(i);
860 pval(i) := nv.value;
861 END LOOP;
862 END;
863 /*===========================================================================
864 | This procedure cleans data from JTF_PF_REPOSITORY based on when the data
865 | was last migrated into the Page Access Tracking tables. It can also clean
866 | all data from JTF_PF_REPOSITORY by force, if preferred.
867 ============================================================================*/
868 PROCEDURE CLEAN_DATA(START_DATE DATE, force_clean boolean default FALSE)
869 IS
870 -- PRAGMA AUTONOMOUS_TRANSACTION;
871 TYPE RECTABTYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
872 RECTABTYPE1 RECTABTYPE;
873
874 CURSOR EBIZ_DATA
875 IS
876 SELECT X.PAGEOBJECT.INFO.RECID
877 FROM JTF_PF_REPOSITORY X
878 WHERE X.TRACK_PURPOSE IN (0,1)
879 AND X.LAST_UPDATE_DATE <= LEAST(START_DATE,
880 (SELECT LAST_RECORD_MIGRATED_TIME
881 FROM JTF_PF_PURGEABLE
882 WHERE TRACK_PURPOSE IN (0,1)
883 ));
884
885 CURSOR NON_EBIZ_DATA
886 IS
887 SELECT X.PAGEOBJECT.INFO.RECID
888 FROM JTF_PF_REPOSITORY X
889 WHERE X.TRACK_PURPOSE = 4
890 AND X.LAST_UPDATE_DATE <= LEAST(START_DATE,
891 (SELECT LAST_RECORD_MIGRATED_TIME
892 FROM JTF_PF_PURGEABLE
893 WHERE TRACK_PURPOSE = 4
894 ));
895
896 CURSOR WA_EBIZ_DATA
897 IS
898 SELECT X.PAGEOBJECT.INFO.RECID
899 FROM JTF_PF_REPOSITORY X
900 WHERE X.TRACK_PURPOSE = 2
901 AND X.LAST_UPDATE_DATE <= LEAST( START_DATE,
902 (SELECT LAST_RECORD_MIGRATED_TIME
903 FROM JTF_PF_PURGEABLE
904 WHERE TRACK_PURPOSE = 2
905 ));
906
907 CURSOR COMMON_DATA
908 IS
909 SELECT X.PAGEOBJECT.INFO.RECID
910 FROM JTF_PF_REPOSITORY X
911 WHERE X.TRACK_PURPOSE = 3
912 AND X.LAST_UPDATE_DATE <= LEAST (START_DATE ,
913 (SELECT MIN(LAST_RECORD_MIGRATED_TIME)
914 FROM JTF_PF_PURGEABLE
915 WHERE TRACK_PURPOSE IN(0,1,2)
916 ));
917
918 JTF_OWNER varchar2(10);
919 BEGIN
920 -- If data is to be cleaned by force
921 IF force_clean THEN
922 -- Get owner of JTF_PF_REPOSITORY, cannot hardcode 'JTF' per GSCC
923 -- standards File.Sql.6
924 SELECT FOU.ORACLE_USERNAME INTO JTF_OWNER
925 FROM FND_ORACLE_USERID FOU,
926 FND_PRODUCT_INSTALLATIONS FPI,
927 FND_APPLICATION A
928 WHERE FOU.ORACLE_ID = FPI.ORACLE_ID
929 AND FPI.APPLICATION_ID = A.APPLICATION_ID
933 -- TRUNCATE TABLE cannot be called directly from PL/SQL, so use
930 AND A.APPLICATION_SHORT_NAME = 'JTF';
931
932 -- truncate all rows from the table JTF_PF_REPOSITORY;
934 -- dynamic PL/SQL call. Once table is truncated, it cannot be rolled back.
935 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || JTF_OWNER ||'.JTF_PF_REPOSITORY';
936 ELSE
937 -- Use cursors that filter on when data was last migrated
938 OPEN EBIZ_DATA;
939 LOOP
940 FETCH EBIZ_DATA BULK COLLECT INTO RECTABTYPE1 LIMIT 1000;
941 IF RECTABTYPE1.COUNT > 0 THEN
942 FORALL I IN RECTABTYPE1.FIRST .. RECTABTYPE1.LAST
943 DELETE
944 FROM JTF_PF_REPOSITORY X
945 WHERE X.PAGEOBJECT.INFO.RECID = RECTABTYPE1(I);
946 END IF;
947 EXIT
948 WHEN EBIZ_DATA%NOTFOUND;
949 END LOOP;
950 RECTABTYPE1.DELETE;
951 CLOSE EBIZ_DATA;
952
953 OPEN NON_EBIZ_DATA;
954 LOOP
955 FETCH NON_EBIZ_DATA BULK COLLECT INTO RECTABTYPE1 LIMIT 1000;
956 IF RECTABTYPE1.COUNT > 0 THEN
957 FORALL I IN RECTABTYPE1.FIRST .. RECTABTYPE1.LAST
958 DELETE
959 FROM JTF_PF_REPOSITORY X
960 WHERE X.PAGEOBJECT.INFO.RECID = RECTABTYPE1(I);
961 END IF;
962 EXIT
963 WHEN NON_EBIZ_DATA%NOTFOUND;
964 END LOOP;
965 RECTABTYPE1.DELETE;
966 CLOSE NON_EBIZ_DATA;
967
968 OPEN WA_EBIZ_DATA;
969 LOOP
970 FETCH WA_EBIZ_DATA BULK COLLECT INTO RECTABTYPE1 LIMIT 1000;
971 IF RECTABTYPE1.COUNT > 0 THEN
972 FORALL I IN RECTABTYPE1.FIRST .. RECTABTYPE1.LAST
973 DELETE
974 FROM JTF_PF_REPOSITORY X
975 WHERE X.PAGEOBJECT.INFO.RECID = RECTABTYPE1(I);
976 END IF;
977 EXIT
978 WHEN WA_EBIZ_DATA%NOTFOUND;
979 END LOOP;
980 RECTABTYPE1.DELETE;
981 CLOSE WA_EBIZ_DATA;
982
983 OPEN COMMON_DATA;
984 LOOP
985 FETCH COMMON_DATA BULK COLLECT INTO RECTABTYPE1 LIMIT 1000;
986 IF RECTABTYPE1.COUNT > 0 THEN
987 FORALL I IN RECTABTYPE1.FIRST .. RECTABTYPE1.LAST
988 DELETE
989 FROM JTF_PF_REPOSITORY X
990 WHERE X.PAGEOBJECT.INFO.RECID = RECTABTYPE1(I);
991 END IF;
992 EXIT
993 WHEN COMMON_DATA%NOTFOUND;
994 END LOOP;
995 RECTABTYPE1.DELETE;
996 CLOSE COMMON_DATA;
997 END IF;
998 COMMIT;
999 END;
1000 /*===========================================================================
1001 |
1002 ============================================================================*/
1003 PROCEDURE PURGE_DATA(
1004 start_date IN DATE,
1005 force_purge IN VARCHAR2 default 'FALSE') -- Bug 9431056
1006 IS
1007 PRAGMA AUTONOMOUS_TRANSACTION;
1008 TEMP_DATE DATE;
1009 BEGIN
1010 --dbms_output.put_line('purge data ');
1011 TEMP_DATE:=(START_DATE-1);
1012 -- Bug 9431056
1013 if (force_purge = upper('TRUE')) then
1014 CLEAN_DATA(TEMP_DATE, TRUE);
1015 else
1016 CLEAN_DATA(TEMP_DATE, FALSE);
1017 end if;
1018
1019 DELETE FROM JTF_PF_SES_ACTIVITY WHERE DAY <= START_DATE;
1020 DELETE FROM JTF_PF_ANON_ACTIVITY WHERE DAY <= START_DATE;
1021 DELETE FROM JTF_PF_APP_SUMM WHERE DAY <= START_DATE;
1022 DELETE FROM JTF_PF_HOST_SUMM WHERE DAY <= START_DATE;
1023 DELETE FROM JTF_PF_PAGE_SUMM WHERE DAY <= START_DATE;
1024 DELETE FROM JTF_PF_SESSION_SUMM WHERE DAY <= START_DATE;
1025 DELETE FROM JTF_PF_USER_SUMM WHERE DAY <= START_DATE;
1026 COMMIT;
1027 END;
1028 /*===========================================================================
1029 |
1030 ============================================================================*/
1031 --6991900, changed date parameter to varchar2
1032 PROCEDURE PURGE_DATA(
1033 ERRBUF OUT NOCOPY VARCHAR2,
1034 RETCODE OUT NOCOPY NUMBER,
1035 start_date_v IN VARCHAR2,
1036 force_purge IN VARCHAR2 default 'FALSE') -- Bug 9431056
1037 IS
1038 PRAGMA AUTONOMOUS_TRANSACTION;
1039 start_date DATE;
1040 BEGIN
1041 start_date := FND_CONC_DATE.STRING_TO_DATE(start_date_v);
1042 PURGE_DATA(START_DATE, force_purge); -- Bug 9431056
1043 EXCEPTION
1044 WHEN OTHERS THEN
1045 RETCODE := 2;
1046 ERRBUF := SQLCODE||':'||sqlerrm;
1047 fnd_file.put_line(fnd_file.log, 'JTF_PF_CONV_PKG.PURGE_DATA failed ' ||
1048 SQLCODE||':'||sqlerrm);
1049 END;
1050 /*===========================================================================
1051 |
1052 ============================================================================*/
1053 PROCEDURE MULTIPLY_DATA(days NUMBER)
1054 IS
1055 PRAGMA AUTONOMOUS_TRANSACTION;
1056
1057 CURSOR cur
1058 IS
1059 SELECT MAX(x.pageobject.ses.sessionid) + 3
1060 FROM JTF_PF_REPOSITORY_TMP x;
1061
1062 max_sessionid INTEGER;
1063 BEGIN
1064 DELETE FROM JTF_PF_REPOSITORY_TMP;
1065 DELETE FROM JTF_PF_REPOSITORY x WHERE x.pageobject.info.day > sysdate;
1066 COMMIT;
1067
1068 INSERT INTO JTF_PF_REPOSITORY_TMP(
1069 pageobject,
1070 object_version_number,
1071 created_by,
1072 creation_date,
1073 last_updated_by,
1074 last_update_date,
1075 last_update_login,
1076 security_group_id,
1077 tech_stack)
1078 (SELECT
1079 pageobject,
1080 object_version_number,
1081 created_by,
1082 creation_date,
1083 last_updated_by,
1084 last_update_date,
1085 last_update_login,
1086 security_group_id,
1087 tech_stack
1088 FROM JTF_PF_REPOSITORY x
1089 WHERE x.pageobject.info.day > sysdate - 1
1090 AND x.pageobject.info.day < sysdate);
1091 COMMIT;
1092
1093 OPEN cur;
1094 FETCH cur INTO max_sessionid;
1095 IF(cur%NOTFOUND OR max_sessionid IS NULL) THEN
1096 max_sessionid := 100000;
1097 END IF;
1098 CLOSE cur;
1099
1100 FOR i IN 1..days
1101 LOOP
1102 UPDATE JTF_PF_REPOSITORY_TMP x
1103 SET x.pageobject.info.day = x.pageobject.info.day + 1,
1104 x.pageobject.info.timestamp = x.pageobject.info.timestamp + 1,
1105 x.pageobject.jsp.starttime =
1106 x.pageobject.jsp.starttime + 1000 * 60 * 60 * 24;
1107 COMMIT;
1108
1109 UPDATE JTF_PF_REPOSITORY_TMP x
1110 SET x.pageobject.ses.sessionid = DECODE(x.pageobject.ses.sessionid, NULL,
1111 x.pageobject.ses.sessionid, -1,x.pageobject.ses.sessionid,
1112 x.pageobject.ses.sessionid - max_sessionid)
1113 WHERE x.pageobject.ses IS NOT NULL;
1114 COMMIT;
1115
1116 INSERT INTO JTF_PF_REPOSITORY(
1117 pageobject,
1118 object_version_number,
1119 created_by,
1120 creation_date,
1121 last_updated_by,
1122 last_update_date,
1123 last_update_login,
1124 security_group_id,
1125 tech_stack)
1126 (SELECT
1127 pageobject,
1128 object_version_number,
1129 created_by,
1130 creation_date,
1131 last_updated_by,
1132 last_update_date,
1133 last_update_login,
1134 security_group_id,
1135 tech_stack
1136 FROM JTF_PF_REPOSITORY_TMP);
1137 COMMIT;
1138
1139 END LOOP;
1140 END;
1141 /*===========================================================================
1142 |
1143 ============================================================================*/
1144 FUNCTION GROUP_CONCAT(
1145 list IN JTF_PF_TABLETYPE,
1146 separator VARCHAR2)
1147 RETURN VARCHAR2
1148 IS
1149 ret VARCHAR2(4000) :='';
1150 BEGIN
1151 IF (list.COUNT > 0) THEN
1152 FOR j IN list.FIRST..list.LAST
1153 LOOP
1154 IF j = 1 THEN
1155 ret := list(j);
1156 ELSE
1157 --9365233, increased variable to 4000 and protected against an overrun
1158 IF lengthb(ret || separator || list(j)) < 4000 THEN
1159 ret := ret || separator || list(j);
1160 END IF;
1161 END IF;
1162 END LOOP;
1163 RETURN ret;
1164 ELSE
1165 RETURN ret;
1166 END IF;
1167 END;
1168 /*===========================================================================
1169 |
1170 ============================================================================*/
1171 PROCEDURE MIGRATED_DATA(
1172 TRK_PRPS NUMBER,
1173 LST_RCD_MGRTD_TM DATE)
1174 IS
1175 TP NUMBER := -1;
1176 BEGIN
1177 SELECT COUNT(TRACK_PURPOSE)
1178 INTO TP
1179 FROM JTF_PF_PURGEABLE
1180 WHERE TRACK_PURPOSE = TRK_PRPS;
1181 IF (TP > 0) THEN
1182 UPDATE JTF_PF_PURGEABLE
1183 SET LAST_RECORD_MIGRATED_TIME = LST_RCD_MGRTD_TM
1184 WHERE TRACK_PURPOSE = TRK_PRPS;
1185 ELSE
1186 INSERT INTO JTF_PF_PURGEABLE(
1187 TRACK_PURPOSE,
1188 LAST_RECORD_MIGRATED_TIME)
1189 VALUES(
1190 TRK_PRPS,
1191 LST_RCD_MGRTD_TM);
1192 END IF;
1193 END MIGRATED_DATA;
1194 END;