1 PACKAGE BODY JTF_DIAGNOSTIC_MIGRATE AS
2 /* $Header: jtfdiagmigrate_b.pls 115.1 2003/01/04 00:54:27 skhemani noship $ */
3
4 ------------------------------------------------------------
5 -- Begin procedure INSERT_PLACEHOLDER_DATE
6 ------------------------------------------------------------
7
8 procedure INSERT_PLACEHOLDER_DATE is
9
10 v_number number := 0;
11 v_date date := add_months(sysdate, -480);
12
13 BEGIN
14 select count(*) into v_number from jtf_diagnostic_prereq where
15 sourceappid = 'migrate_date_flag' and sourceid = 'migrate_date_flag'
16 and prereqid = 'migrate_date_flag';
17
18 if v_number = 0 then
19 insert into jtf_diagnostic_prereq
20 (SEQUENCE, SOURCEID, PREREQID,
21 SOURCEAPPID, TYPE, OBJECT_VERSION_NUMBER,
22 CREATED_BY, LAST_UPDATE_DATE,
23 LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE)
24 values
25 (JTF_DIAGNOSTIC_PREREQ_S.NEXTVAL, 'migrate_date_flag',
26 'migrate_date_flag', 'migrate_date_flag', -1,
27 -1, -1,v_date , -1,
28 NULL, v_date);
29 end if;
30
31
32 END INSERT_PLACEHOLDER_DATE;
33
34 ------------------------------------------------------------
35 -- Begin procedure UPDATE_MIGRATION_DATE
36 ------------------------------------------------------------
37
38 procedure UPDATE_MIGRATION_DATE is
39
40 BEGIN
41 update jtf_diagnostic_prereq
42 set last_update_date = SYSDATE
43 where sourceid='migrate_date_flag' and
44 prereqid='migrate_date_flag' and
45 sourceappid='migrate_date_flag';
46 END UPDATE_MIGRATION_DATE;
47
48 ------------------------------------------------------------
49 -- Begin procedure LOCK_MIGRATION_DATE
50 ------------------------------------------------------------
51
52 procedure LOCK_MIGRATION_DATE is
53
54 v_last_update_date date;
55
56 BEGIN
57 select last_update_date into v_last_update_date
58 from jtf_diagnostic_prereq
59 where sourceid='migrate_date_flag' and
60 prereqid='migrate_date_flag' and
61 sourceappid='migrate_date_flag' for update of last_update_date;
62 END LOCK_MIGRATION_DATE;
63
64
65 ------------------------------------------------------------
66 -- Begin procedure MIGRATE_DB_DIAGNOSTIC_DATA
67 ------------------------------------------------------------
68
69 PROCEDURE MIGRATE_DB_DIAGNOSTIC_DATA IS
70
71 BEGIN
72 MIGRATE_APPS;
73 END MIGRATE_DB_DIAGNOSTIC_DATA;
74
75
76 ------------------------------------------------------------
77 -- Begin procedure MIGRATE_APPS
78 ------------------------------------------------------------
79
80 PROCEDURE MIGRATE_APPS IS
81
82 v_last_migrate_date date := SYSDATE;
83 v_count number := 0;
84
85 CURSOR applist IS
86 select distinct c.application_id, c.application_short_name,
87 a.created_by, a.last_updated_by, a.last_update_date,
88 a.object_version_number from jtf_perz_data a,
89 jtf_perz_profile b, fnd_application c where a.profile_id = b.profile_id
90 and b.profile_name = 'JTF_PROPERTY_MANAGER_DEFAULT_1'
91 and a.perz_data_type = 'JTF' and a.perz_data_name
92 like 'TESTHARNESS%GRPCOUNT' and a.application_id = c.application_id;
93
94 BEGIN
95
96 select last_update_date into v_last_migrate_date from
97 jtf_diagnostic_prereq where sourceid = 'migrate_date_flag'
98 and sourceappid = 'migrate_date_flag'
99 and prereqid = 'migrate_date_flag';
100
101 for x in applist
102 loop
103 -- check if this already exists.
104 -- if not insert, else update
105 select count(*) into v_count from jtf_diagnostic_app
106 where appid = x.application_short_name;
107
108 if v_last_migrate_date < x.last_update_date or v_count = 0 then
109
110 if v_count = 0 then
111
112 insert into jtf_diagnostic_app(
113 sequence, appID, OBJECT_VERSION_NUMBER, CREATED_BY,
114 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
115 CREATION_DATE) values(
116 JTF_DIAGNOSTIC_APP_S.nextval,
117 x.application_short_name, x.object_version_number,
118 x.CREATED_BY, x.LAST_UPDATE_DATE, x.created_by,
119 null, x.LAST_UPDATE_DATE);
120
121 else
122 update jtf_diagnostic_app set
123 last_update_date = x.last_update_date,
124 LAST_UPDATED_BY = x.LAST_UPDATED_BY,
125 OBJECT_VERSION_NUMBER = x.OBJECT_VERSION_NUMBER
126 where appid = x.application_short_name;
127
128 end if;
129
130 end if;
131
132
133 -- insert or update prereqs
134 MIGRATE_APP_PREREQS(x.application_short_name,
135 x.application_id);
136
137 -- Insert or update groups
138 MIGRATE_APP_GROUPS(x.application_short_name,
139 x.application_id);
140
141
142 end loop;
143 END MIGRATE_APPS;
144
145
146 ------------------------------------------------------------
147 -- Begin procedure MIGRATE_APP_PREREQS
148 ------------------------------------------------------------
149
150 PROCEDURE MIGRATE_APP_PREREQS(P_ASN IN VARCHAR2, P_APP_ID IN NUMBER) IS
151
152 v_last_migrate_date date := SYSDATE;
153 v_count NUMBER := 0;
154
155 CURSOR PREREQLIST IS
156 select b.attribute_value, b.created_by,
157 b.last_updated_by, b.last_update_date, a.object_version_number
158 from jtf_perz_data a , jtf_perz_data_attrib b, jtf_perz_profile c
159 where a.perz_data_id = b.perz_data_id
160 and a.profile_id = c.profile_id
161 and c.profile_name = 'JTF_PROPERTY_MANAGER_DEFAULT_1'
162 and a.application_id= P_APP_ID
163 and a.perz_data_name = 'TESTHARNESS.' || P_ASN || '.PREREQ';
164
165 BEGIN
166 select last_update_date into v_last_migrate_date from
167 jtf_diagnostic_prereq where sourceid = 'migrate_date_flag'
168 and sourceappid = 'migrate_date_flag'
169 and prereqid = 'migrate_date_flag';
170
171 FOR X IN PREREQLIST LOOP
172
173 -- check if this PREREQ already exists.
174 -- if not insert, else update
175 select count(*) into v_count from jtf_diagnostic_prereq
176 where sourceid = p_asn and sourceappid = p_asn
177 and prereqid = x.attribute_value;
178
179 if v_last_migrate_date < x.last_update_date or v_count = 0 then
180
181 if v_count = 0 then
182
183 -- insert the prereq
184
185 insert into jtf_diagnostic_prereq(
186 SEQUENCE, SOURCEID, PREREQID,
187 SOURCEAPPID, TYPE, OBJECT_VERSION_NUMBER,
188 CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
189 LAST_UPDATE_LOGIN, CREATION_DATE)
190 values(
191 JTF_DIAGNOSTIC_PREREQ_S.nextval, p_asn, x.attribute_value,
192 p_asn, 1, x.object_version_number,
193 x.created_by, x.last_update_date, x.created_by,
194 NULL, x.LAST_UPDATE_DATE);
195
196 else
197 -- update the prereq
198
199 update jtf_diagnostic_prereq set
200 last_update_date = x.last_update_date,
201 LAST_UPDATED_BY = x.LAST_UPDATED_BY,
202 OBJECT_VERSION_NUMBER = x.OBJECT_VERSION_NUMBER
203 where sourceappid = p_asn and
204 prereqid = x.attribute_value
205 and sourceid = p_asn;
206
207 end if;
208
209 end if;
210
211 END LOOP;
212
213 END MIGRATE_APP_PREREQS;
214
215
216 ------------------------------------------------------------
217 -- Begin procedure MIGRATE_APP_GROUPS
218 ------------------------------------------------------------
219
220 PROCEDURE MIGRATE_APP_GROUPS(P_ASN IN VARCHAR2, P_APP_ID IN NUMBER) IS
221
222 v_count number := 0;
223 v_group_count number := 0;
224
225 BEGIN
226
227 select distinct to_number(b.attribute_value) into v_group_count
228 from jtf_perz_data a , jtf_perz_data_attrib b, jtf_perz_profile c
229 where a.perz_data_id = b.perz_data_id
230 and a.profile_id = c.profile_id
231 and c.profile_name = 'JTF_PROPERTY_MANAGER_DEFAULT_1'
232 and a.application_id= P_APP_ID
233 and a.perz_data_name = 'TESTHARNESS.' || P_ASN || '.GRPCOUNT';
234
235 if v_group_count > 0 then
236 for v_count in 1..v_group_count loop
237 MIGRATE_APP_GROUPS(P_ASN, P_APP_ID, v_count);
238 end loop;
239 end if;
240
241 EXCEPTION
242 -- basically do nothing when no data found or any
243 -- other error for getting group count for an application
244
245 WHEN NO_DATA_FOUND or TOO_MANY_ROWS THEN
246 v_group_count := 0;
247 WHEN OTHERS THEN
248 v_group_count := 0;
249
250 END MIGRATE_APP_GROUPS;
251
252
253 ------------------------------------------------------------
254 -- Begin procedure MIGRATE_APP_GROUPS
255 ------------------------------------------------------------
256
257 PROCEDURE MIGRATE_APP_GROUPS(
258 P_ASN IN VARCHAR2,
259 P_APP_ID IN NUMBER,
260 P_GRPCOUNT IN NUMBER) IS
261
262 v_last_migrate_date date := SYSDATE;
263 v_count NUMBER := 0;
264 v_temp NUMBER := 0;
265
266
267 cursor grplist is
268 select b.attribute_value, b.created_by,
269 b.last_updated_by, b.last_update_date, a.object_version_number
270 from jtf_perz_data a , jtf_perz_data_attrib b, jtf_perz_profile c
271 where a.perz_data_id = b.perz_data_id
272 and a.profile_id = c.profile_id
273 and c.profile_name = 'JTF_PROPERTY_MANAGER_DEFAULT_1'
274 and a.application_id = P_APP_ID
275 and a.perz_data_name like 'TESTHARNESS.' || P_ASN || '.GROUP.'|| p_grpcount ||'.NAME';
276 BEGIN
277
278 IF P_GRPCOUNT > 0 THEN
279
280 select last_update_date into v_last_migrate_date from
281 jtf_diagnostic_prereq where sourceid = 'migrate_date_flag'
282 and sourceappid = 'migrate_date_flag'
283 and prereqid = 'migrate_date_flag';
284
285 FOR X IN grplist LOOP
286
287 -- check if this already exists.
288 -- if not insert, else update
289 select count(*) into v_count from jtf_diagnostic_group
290 where appid = P_ASN
291 and groupname = x.attribute_value;
292
293 if v_last_migrate_date < x.last_update_date or v_count = 0 then
294
295 -- v_temp := grplist%ROWCOUNT;
296
297 select MAX(ordernumber) into v_temp
298 from jtf_diagnostic_group where appid = p_asn;
299
300 if sql%notfound or v_temp = 0 then
301 v_temp := 1;
302 else v_temp := v_temp + 1;
303 end if;
304
305 if v_count = 0 then
306
307 -- making sure that the rowcount is not
308 -- already in use in which case use the max
309 -- number there
310
311 -- insert the record
312
313 insert into jtf_diagnostic_group(SEQUENCE, GROUPNAME,
314 APPID, ORDERNUMBER, OBJECT_VERSION_NUMBER,
315 CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
316 LAST_UPDATE_LOGIN, CREATION_DATE, SENSITIVITY) values (
317 JTF_DIAGNOSTIC_GROUP_S.NEXTVAL, x.attribute_value,
318 P_ASN, DECODE(v_temp,null,1,v_temp),
319 x.OBJECT_VERSION_NUMBER, x.CREATED_BY,
320 x.LAST_UPDATE_DATE, x.created_by, NULL,
321 x.LAST_UPDATE_DATE, 1);
322
323 else
324 -- update the record
325
326 update jtf_diagnostic_group set
327 OBJECT_VERSION_NUMBER = x.OBJECT_VERSION_NUMBER,
328 LAST_UPDATE_DATE = x.LAST_UPDATE_DATE,
329 LAST_UPDATED_BY = x.LAST_UPDATED_BY
330 where appid = P_ASN
331 and groupname = x.attribute_value;
332
333 end if;
334
335 end if;
336
337 --insert or update group prereqs
338 MIGRATE_GROUP_PREREQS(P_ASN,
339 x.attribute_value, P_APP_ID);
340
341
342 v_temp := 0;
343 select count(*) into v_temp
344 from jtf_perz_data a , jtf_perz_data_attrib b, jtf_perz_profile c
345 where a.perz_data_id = b.perz_data_id
346 and a.profile_id = c.profile_id
347 and c.profile_name = 'JTF_PROPERTY_MANAGER_DEFAULT_1'
348 and a.application_id = P_APP_ID
349 and a.perz_data_name = 'TESTHARNESS.' || p_asn || '.'
350 || x.attribute_value ||'.TCOUNT';
351
352
353 if v_temp = 1 then
354
355 select distinct to_number(b.attribute_value) into v_temp
356 from jtf_perz_data a , jtf_perz_data_attrib b, jtf_perz_profile c
357 where a.perz_data_id = b.perz_data_id
358 and a.profile_id = c.profile_id
359 and c.profile_name = 'JTF_PROPERTY_MANAGER_DEFAULT_1'
360 and a.application_id = P_APP_ID
361 and a.perz_data_name = 'TESTHARNESS.' || p_asn || '.'
362 || x.attribute_value ||'.TCOUNT';
363
364
365 for v_count in 1..v_temp loop
366 --insert or update test information
367 MIGRATE_GROUP_TESTS(P_ASN, x.attribute_value,
368 P_APP_ID, v_count);
369 end loop;
370
371 end if;
372
373 END LOOP;
374
375 END IF;
376
377 END MIGRATE_APP_GROUPS;
378
379 ------------------------------------------------------------
380 -- Begin procedure MIGRATE_GROUP_PREREQS
381 ------------------------------------------------------------
382
383 PROCEDURE MIGRATE_GROUP_PREREQS(
384 P_ASN IN VARCHAR2,
385 P_GRPNAME IN VARCHAR2,
386 P_APP_ID IN NUMBER) IS
387
388 v_last_migrate_date date := SYSDATE;
389 v_count NUMBER := 0;
390 v_temp NUMBER := 0;
391
392
393 cursor prereqlist is
394 select b.attribute_value, b.created_by,
395 b.last_updated_by, b.last_update_date, a.object_version_number
396 from jtf_perz_data a , jtf_perz_data_attrib b, jtf_perz_profile c
397 where a.perz_data_id = b.perz_data_id
398 and a.profile_id = c.profile_id
399 and c.profile_name = 'JTF_PROPERTY_MANAGER_DEFAULT_1'
400 and a.application_id = P_APP_ID
401 and a.perz_data_name like 'TESTHARNESS.' || P_ASN || '.' || P_GRPNAME ||'.DEP';
402 BEGIN
403
404 select last_update_date into v_last_migrate_date from
405 jtf_diagnostic_prereq where sourceid = 'migrate_date_flag'
406 and sourceappid = 'migrate_date_flag'
407 and prereqid = 'migrate_date_flag';
408
409 FOR X IN prereqlist LOOP
410
411 -- check if this already exists.
412 -- if not insert, else update
413 select count(*) into v_count from jtf_diagnostic_prereq
414 where sourceappid = P_ASN
415 and prereqid = x.attribute_value
416 and sourceid = p_grpname;
417
418 if v_last_migrate_date < x.last_update_date or v_count = 0 then
419
420 if v_count = 0 then
421 -- insert the group prereq
422
423 insert into jtf_diagnostic_prereq(
424 SEQUENCE, SOURCEID, PREREQID,
425 SOURCEAPPID, TYPE, OBJECT_VERSION_NUMBER,
426 CREATED_BY, LAST_UPDATE_DATE,
427 LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE)
428 values(
429 JTF_DIAGNOSTIC_PREREQ_S.NEXTVAL,
430 p_grpname, x.attribute_value, p_asn,
431 2, x.object_version_number, x.created_by,
432 x.last_update_date, x.created_by,
433 NULL, x.last_update_date);
434
435
436 else
437 -- just update the group prereq
438 update jtf_diagnostic_prereq set
439 OBJECT_VERSION_NUMBER = x.OBJECT_VERSION_NUMBER,
440 LAST_UPDATE_DATE = x.LAST_UPDATE_DATE,
441 LAST_UPDATED_BY = x.LAST_UPDATED_BY;
442
443 end if;
444
445 end if;
446
447 END LOOP;
448
449 END MIGRATE_GROUP_PREREQS;
450
451 ------------------------------------------------------------
452 -- Begin procedure MIGRATE_GROUP_TESTS
453 ------------------------------------------------------------
454
455 PROCEDURE MIGRATE_GROUP_TESTS(
456 P_ASN IN VARCHAR2,
457 P_GRPNAME IN VARCHAR2,
458 P_APP_ID IN NUMBER,
459 p_testnum in number) IS
460
461 v_last_migrate_date date := SYSDATE;
462 v_count NUMBER := 0;
463 v_temp_ordnum number := 0;
464 v_temp_argrows number := 0;
465 v_prev_data_name jtf_perz_data.perz_data_name%type := 'blank';
466 v_prev_attrib_value jtf_perz_data_attrib.attribute_value%type := 'blank';
467
468 cursor testlist is
469 select distinct a.perz_data_name, b.attribute_value, b.created_by,
470 b.last_updated_by, b.last_update_date, a.object_version_number
471 from jtf_perz_data a , jtf_perz_data_attrib b, jtf_perz_profile c
472 where a.perz_data_id = b.perz_data_id
473 and a.profile_id = c.profile_id
474 and c.profile_name = 'JTF_PROPERTY_MANAGER_DEFAULT_1'
475 and a.application_id = P_APP_ID
476 and a.perz_data_name = 'TESTHARNESS.' || P_ASN || '.' || P_GRPNAME ||'.TEST'
477 ||'.' || p_testnum order by a.perz_data_name, b.attribute_value;
478
479 BEGIN
480
481 select last_update_date into v_last_migrate_date from
482 jtf_diagnostic_prereq where sourceid = 'migrate_date_flag'
483 and sourceappid = 'migrate_date_flag'
484 and prereqid = 'migrate_date_flag';
485
486 FOR X IN testlist LOOP
487
488 -- insertion happens for every 2nd row from the cursor
489 -- since the first one is the test type that we have to convert
490 -- to the desired format as required by daniel's new standard
491 -- after pl/sql enabling
492
493 if mod(testlist%rowcount, 2) = 0 and v_prev_data_name = x.perz_data_name then
494
495
496 -- check if this already exists.
497 -- if not insert, else update
498
499 select count(*) into v_count from jtf_diagnostic_test
500 where appid = P_ASN and groupname = P_GRPNAME and
501 testclassname = x.attribute_value;
502
503 if v_last_migrate_date < x.last_update_date or v_count = 0 then
504
505 if v_count = 0 then
506
507 -- insert the testcase after getting
508 -- all the right parameters
509
510
511
512 -- ordernumber (max there in the database + 1)
513
514 select MAX(ordernumber) into v_temp_ordnum
515 from jtf_diagnostic_test
516 where appid = p_asn
517 and groupname = p_grpname;
518 -- and testclassname = x.attribute_value;
519
520 if sql%notfound or v_temp_ordnum = 0 then
521 v_temp_ordnum := 1;
522 else v_temp_ordnum := v_temp_ordnum + 1;
523 end if;
524
525
526
527
528 -- testtype should be properly formatted
529 -- according to the new standard
530
531 if v_prev_attrib_value = '1' then
532 v_prev_attrib_value := '{1,5}';
533 elsif v_prev_attrib_value = '2' then
534 v_prev_attrib_value := '{2,5}';
535 elsif v_prev_attrib_value = '3' then
536 v_prev_attrib_value := '{1,2,5}';
537 else v_prev_attrib_value := '{1,5}';
538 end if;
539
540
541
542 -- total argument rows (key is okay)
543 -- v_temp_argrows
544
545 select count(*) into v_temp_argrows
546 from jtf_perz_data a , jtf_perz_data_attrib b,
547 jtf_perz_profile c
548 where a.perz_data_id = b.perz_data_id
549 and a.profile_id = c.profile_id
550 and c.profile_name = 'JTF_PROPERTY_MANAGER_DEFAULT_1'
551 and a.application_id = p_app_id
552 and a.perz_data_name = 'TESTHARNESS.' || p_asn || '.'
553 || p_grpname ||'.'
554 || x.attribute_value ||'.SETCOUNT';
555
556
557 if v_temp_argrows = 1 then
558
559 select distinct TO_NUMBER(b.attribute_value)
560 into v_temp_argrows
561 from jtf_perz_data a , jtf_perz_data_attrib b,
562 jtf_perz_profile c
563 where a.perz_data_id = b.perz_data_id
564 and a.profile_id = c.profile_id
565 and c.profile_name = 'JTF_PROPERTY_MANAGER_DEFAULT_1'
566 and a.application_id = p_app_id
567 and a.perz_data_name = 'TESTHARNESS.' || p_asn || '.'
568 || p_grpname ||'.'
569 || x.attribute_value ||'.SETCOUNT';
570
571 end if;
572
573
574 insert into jtf_diagnostic_test(
575 SEQUENCE, GROUPNAME, APPID,
576 ORDERNUMBER, TESTTYPE, TESTCLASSNAME,
577 TOTALARGUMENTROWS, OBJECT_VERSION_NUMBER,
578 CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
579 LAST_UPDATE_LOGIN, CREATION_DATE)
580 values(
581 jtf_diagnostic_test_s.nextval, p_grpname, p_asn,
582 DECODE(v_temp_ordnum,null,1,v_temp_ordnum),
583 v_prev_attrib_value,
584 x.attribute_value,
585 v_temp_argrows, X.OBJECT_VERSION_NUMBER, X.created_by,
586 x.last_update_date, X.created_by,
587 NULL, x.last_update_date);
588
589 else
590
591 -- just update the testcase
592 update jtf_diagnostic_test set
593 OBJECT_VERSION_NUMBER = x.OBJECT_VERSION_NUMBER,
594 LAST_UPDATE_DATE = x.LAST_UPDATE_DATE,
595 LAST_UPDATED_BY = x.LAST_UPDATED_BY
596 where appid = p_asn and groupname = p_grpname
597 and testclassname = x.attribute_value;
598
599 end if;
600 end if;
601
602
603 -- Migrate test arguments here
604 -- figure out what the bare min parameters required
605 -- to get arguments from the property manager tables
606
607 migrate_test_arguments(p_asn, p_grpname, p_app_id, x.attribute_value);
608
609 end if;
610
611 v_prev_data_name := x.perz_data_name;
612 v_prev_attrib_value := x.attribute_value;
613
614 END LOOP;
615
616 END MIGRATE_GROUP_TESTS;
617
618 ------------------------------------------------------------
619 -- Begin procedure migrate_test_arguments
620 ------------------------------------------------------------
621
622 procedure migrate_test_arguments(
623 p_asn in varchar2,
624 p_grpname in varchar2,
625 p_app_id in number,
626 p_classname in varchar2) is
627
628 v_count number := 0;
629 v_counter binary_integer := 1;
630 v_argument_names JTF_VARCHAR2_TABLE_4000;
631
632 cursor argnamelist is
633
634 select a.perz_data_name, b.attribute_value, b.created_by,
635 b.last_updated_by, b.last_update_date, a.object_version_number
636 from jtf_perz_data a , jtf_perz_data_attrib b, jtf_perz_profile c
637 where a.perz_data_id = b.perz_data_id and a.profile_id = c.profile_id
638 and c.profile_name = 'JTF_PROPERTY_MANAGER_DEFAULT_1'
639 and a.application_id = p_app_id and a.perz_data_name like
640 'TESTHARNESS.' || p_asn || '.' || p_grpname ||'.'
641 || p_classname ||'.ArgName';
642
643 begin
644
645 v_argument_names := JTF_VARCHAR2_TABLE_4000();
646
647 -- total argument rows (key is okay)
648 -- v_temp_argrows
649
650 select count(*) into v_count
651 from jtf_perz_data a , jtf_perz_data_attrib b,
652 jtf_perz_profile c
653 where a.perz_data_id = b.perz_data_id
654 and a.profile_id = c.profile_id
655 and c.profile_name = 'JTF_PROPERTY_MANAGER_DEFAULT_1'
656 and a.application_id = p_app_id
657 and a.perz_data_name = 'TESTHARNESS.' || p_asn || '.'
658 || p_grpname ||'.'
659 || p_classname ||'.SETCOUNT';
660
661
662 if v_count = 1 then
663
664 -- this means that one row was returned
665 -- and there is a char (numeric) value in the result
666 -- set that we should convert to a number
667 -- and search for arguments
668
669 select distinct TO_NUMBER(b.attribute_value)
670 into v_count
671 from jtf_perz_data a , jtf_perz_data_attrib b,
672 jtf_perz_profile c
673 where a.perz_data_id = b.perz_data_id
674 and a.profile_id = c.profile_id
675 and c.profile_name = 'JTF_PROPERTY_MANAGER_DEFAULT_1'
676 and a.application_id = p_app_id
677 and a.perz_data_name = 'TESTHARNESS.' || p_asn || '.'
678 || p_grpname ||'.'
679 || p_classname ||'.SETCOUNT';
680
681
682 -- only if there are more than 0
683 -- 0 argument rows in the perz data
684 -- tables should we even bother to
685 -- carry out the following process
686 -- for performance purposes
687
688 if v_count > 0 then
689
690 -- now get the argument names in the array
691 -- jtf_varchar_table_4000
692
693 for x in argnamelist loop
694 v_argument_names.extend;
695 v_argument_names(argnamelist%rowcount)
696 := x.attribute_value;
697 end loop;
698
699 -- send all this information to the next plsql procedure
700 -- that loops thru and makes the argument row migration to the
701 -- database table jtf_diagnostic_arg
702
703 for v_counter in 1..v_count loop
704 migrate_test_arg_row(v_argument_names,
705 p_asn, p_grpname,
706 p_classname, p_app_id,
707 to_char(v_counter));
708 end loop;
709
710 end if;
711
712 end if;
713
714 end migrate_test_arguments;
715
716
717 -------------------------------------------------
718 -- Begin procedure migrate_test_arg_row
719 -------------------------------------------------
720
721 procedure migrate_test_arg_row(
722 v_argument_names IN JTF_VARCHAR2_TABLE_4000,
723 p_asn in varchar2,
724 p_grpname in varchar2,
725 p_classname in varchar2,
726 p_app_id in number,
727 p_rownum in varchar2) is
728
729 v_count number := 0;
730 v_rowcounter number;
731 v_last_migrate_date date := SYSDATE;
732 v_arg_values jtf_varchar2_table_4000;
733
734 cursor argvallist is
735
736 select a.perz_data_name, b.attribute_value, b.created_by,
737 b.last_updated_by, b.last_update_date, a.object_version_number
738 from jtf_perz_data a , jtf_perz_data_attrib b, jtf_perz_profile c
739 where a.perz_data_id = b.perz_data_id and a.profile_id = c.profile_id
740 and c.profile_name = 'JTF_PROPERTY_MANAGER_DEFAULT_1'
741 and a.application_id = p_app_id
742 and a.perz_data_name like
743 'TESTHARNESS.' || p_asn || '.' || p_grpname ||'.'
744 || p_classname ||'.SET.' || p_rownum;
745
746 begin
747
748 -- make sure that the number of rows received from the
749 -- query is equal to the number of arguments in the array
750 -- if not dont do anything, potentiall dangerous situation
751
752 select count(*) into v_count
753 from jtf_perz_data a , jtf_perz_data_attrib b, jtf_perz_profile c
754 where a.perz_data_id = b.perz_data_id and a.profile_id = c.profile_id
755 and c.profile_name = 'JTF_PROPERTY_MANAGER_DEFAULT_1'
756 and a.application_id = p_app_id
757 and a.perz_data_name like
758 'TESTHARNESS.' || p_asn || '.' || p_grpname ||'.'
759 || p_classname ||'.SET.' || p_rownum;
760
761
762 if v_count = v_argument_names.count then
763
764 select last_update_date into v_last_migrate_date from
765 jtf_diagnostic_prereq where sourceid = 'migrate_date_flag'
766 and sourceappid = 'migrate_date_flag'
767 and prereqid = 'migrate_date_flag';
768
769 for x in argvallist loop
770
771 -- check if this exists or not.
772 -- if not we will insert it anyway
773 -- irrespective of last_migrate date
774
775 v_rowcounter := argvallist%rowcount;
776
777 select count(*) into v_count from jtf_diagnostic_arg
778 where testclassname = p_classname and
779 groupname = p_grpname and
780 appid = p_asn and
781 argname = v_argument_names(v_rowcounter) and
782 rownumber = p_rownum;
783
784 if v_last_migrate_date < x.last_update_date or v_count = 0 then
785
786 if v_count = 0 then
787
788 insert into jtf_diagnostic_arg(
789 SEQUENCE, TESTCLASSNAME, GROUPNAME,
790 APPID, ARGNAME, ARGVALUE,
791 ROWNUMBER, VALUESETNUMBER,
792 OBJECT_VERSION_NUMBER, CREATED_BY,
793 LAST_UPDATE_DATE, LAST_UPDATED_BY,
794 LAST_UPDATE_LOGIN, CREATION_DATE)
795 values(
796 jtf_diagnostic_arg_s.nextval,
797 p_classname, p_grpname,
798 p_asn,
799 v_argument_names(v_rowcounter),
800 x.attribute_value, p_rownum, 1,
801 x.object_version_number, x.created_by,
802 x.last_update_date, x.created_by,
803 null, x.last_update_date);
804
805 else
806 update jtf_diagnostic_arg set
807 argvalue = x.attribute_value,
808 last_updated_by = x.last_updated_by,
809 object_version_number = x.object_version_number,
810 last_update_date = x.last_update_date
811 where testclassname = p_classname and
812 groupname = p_grpname and
813 appid = p_asn and
814 argname = v_argument_names(v_rowcounter) and
815 rownumber = p_rownum;
816
817 end if;
818
819 end if;
820
821 end loop;
822
823 end if;
824
825 end migrate_test_arg_row;
826
827
828 END JTF_DIAGNOSTIC_MIGRATE;
829
[Home] [Help]