1 PACKAGE BODY JTF_DIAGNOSTIC AS
2 /* $Header: jtfdiagnostic_b.pls 120.33 2011/04/06 21:38:13 tshort ship $ */
3
4 ------------------------------
5 -- Begin procedure GET APPS
6 ------------------------------
7
8
9 procedure GET_APPS(
10 P_APPS OUT NOCOPY JTF_VARCHAR2_TABLE_4000,
11 P_APPNAMES OUT NOCOPY JTF_VARCHAR2_TABLE_4000,
12 P_SIZE OUT NOCOPY NUMBER) is
13
14
15 CURSOR APPLIST IS
16 -- select distinct a.appid, b.APPLICATION_NAME
17 -- from jtf_diagnostic_app a, fnd_application_tl b, fnd_application c
18 -- where a.appid = c.application_short_name
19 -- and c.application_id = b.application_id
20 -- and b.language = userenv('LANG');
21
22 select distinct appid from jtf_diagnostic_app;
23
24 BEGIN
25
26 P_SIZE := 0;
27 P_APPS := JTF_VARCHAR2_TABLE_4000();
28 P_APPNAMES := JTF_VARCHAR2_TABLE_4000();
29
30 -- Add the first application that does not show up
31 -- in the database fnd tables, 'HTML Platform'
32
33 -- P_SIZE := P_SIZE + 1;
34 -- P_APPS.EXTEND;
35 -- P_APPNAMES.EXTEND;
36 -- P_APPS(P_SIZE) := 'SYSTEM_TESTS';
37 -- P_APPNAMES(P_SIZE) := 'HTML Platform';
38 -- P_APPNAMES(P_SIZE) := '';
39
40 -- Now get stuff from the database and populate the
41 -- rest of the array
42
43 FOR x in APPLIST
44 LOOP
45 P_SIZE := P_SIZE + 1;
46 P_APPS.EXTEND;
47 P_APPNAMES.EXTEND;
48 P_APPS(P_SIZE) := x.APPID;
49 -- P_APPNAMES(P_SIZE) := x.APPLICATION_NAME;
50 P_APPNAMES(P_SIZE) := '';
51 END LOOP;
52
53 END GET_APPS;
54
55
56 ---------------------------------------------------
57 -- Begin procedure to GET GROUPS FOR AN APPLICATION
58 ---------------------------------------------------
59
60
61 procedure GET_GROUPS(
62 P_APPNAME in VARCHAR2,
63 P_GROUPNAMES OUT NOCOPY JTF_VARCHAR2_TABLE_4000,
64 P_GRP_SENSITIVITY OUT NOCOPY JTF_NUMBER_TABLE,
65 P_GRP_LAST_UPDATED_BY OUT NOCOPY JTF_NUMBER_TABLE) is
66
67 V_SIZE NUMBER;
68
69 /* 5953806 - changed select to get last_updated_by instead of created_by */
70 cursor grouplist is
71 select groupName, sensitivity, last_updated_by
72 from jtf_diagnostic_group
73 where appID like P_APPNAME
74 order by orderNumber;
75
76 BEGIN
77
78 P_GROUPNAMES := JTF_VARCHAR2_TABLE_4000();
79 P_GRP_SENSITIVITY := JTF_NUMBER_TABLE();
80 P_GRP_LAST_UPDATED_BY := JTF_NUMBER_TABLE();
81
82 V_SIZE := 0;
83
84 FOR x in grouplist
85 LOOP
86
87 V_SIZE := V_SIZE + 1;
88 P_GROUPNAMES.extend;
89 P_GROUPNAMES(V_SIZE) := x.groupName;
90
91 P_GRP_SENSITIVITY.extend;
92 P_GRP_SENSITIVITY(V_SIZE) := x.sensitivity;
93
94 P_GRP_LAST_UPDATED_BY.extend;
95 P_GRP_LAST_UPDATED_BY(V_SIZE) := x.last_updated_by;
96
97 END LOOP;
98
99 END GET_GROUPS;
100
101
102 ---------------------------------------------------
103 -- Begin procedure to GET TESTS FOR AN APPLICATION
104 ---------------------------------------------------
105
106
107 procedure GET_TESTS(
108 P_APPNAME IN VARCHAR2,
109 P_GROUPNAME IN VARCHAR2,
110 P_TESTCLASSNAMES OUT NOCOPY JTF_VARCHAR2_TABLE_4000,
111 P_TESTTYPES OUT NOCOPY JTF_VARCHAR2_TABLE_4000,
112 P_TOTALARGROWS OUT NOCOPY JTF_NUMBER_TABLE,
113 P_TST_SENSITIVITY OUT NOCOPY JTF_NUMBER_TABLE,
114 P_TEST_LAST_UPDATED_BY OUT NOCOPY JTF_NUMBER_TABLE
115 ) IS
116
117
118 V_SIZE number;
119
120 /* 5953806 - changed select to get last_updated_by instead of created_by */
121 cursor testlist is
122 select testClassName, testtype, totalargumentrows, sensitivity, last_updated_by
123 from jtf_diagnostic_test
124 where appID like P_APPNAME
125 and groupname like P_GROUPNAME
126 order by orderNumber;
127
128 BEGIN
129
130 P_TESTCLASSNAMES := JTF_VARCHAR2_TABLE_4000();
131 P_TESTTYPES := JTF_VARCHAR2_TABLE_4000();
132 P_TOTALARGROWS := JTF_NUMBER_TABLE();
133 P_TST_SENSITIVITY := JTF_NUMBER_TABLE();
134 P_TEST_LAST_UPDATED_BY := JTF_NUMBER_TABLE();
135
136 V_SIZE := 0;
137
138 FOR x in TESTLIST
139 LOOP
140 V_SIZE := V_SIZE + 1;
141 P_TESTCLASSNAMES.extend;
142 P_TESTTYPES.EXTEND;
143 P_TOTALARGROWS.EXTEND;
144 P_TEST_LAST_UPDATED_BY.EXTEND;
145 P_TESTCLASSNAMES(V_SIZE) := x.TESTCLASSNAME;
146 P_TESTTYPES(V_SIZE) := X.TESTTYPE;
147 P_TOTALARGROWS(V_SIZE) := X.TOTALARGUMENTROWS;
148 P_TST_SENSITIVITY(V_SIZE) := X.SENSITIVITY;
149 P_TEST_LAST_UPDATED_BY(V_SIZE) := X.LAST_UPDATED_BY;
150 END LOOP;
151
152 END GET_TESTS;
153
154 -- deprecated don't use if you have test level sensitivity
155 procedure GET_TESTS(
156 P_APPNAME IN VARCHAR2,
157 P_GROUPNAME IN VARCHAR2,
158 P_TESTCLASSNAMES OUT NOCOPY JTF_VARCHAR2_TABLE_4000,
159 P_TESTTYPES OUT NOCOPY JTF_VARCHAR2_TABLE_4000,
160 P_TOTALARGROWS OUT NOCOPY JTF_NUMBER_TABLE,
161 P_TEST_LAST_UPDATED_BY OUT NOCOPY JTF_NUMBER_TABLE
162 ) IS
163
164
165 V_SIZE number;
166
167 /* 5953806 - changed select to get last_updated_by instead of created_by */
168 cursor testlist is
169 select testClassName, testtype, totalargumentrows, last_updated_by
170 from jtf_diagnostic_test
171 where appID like P_APPNAME
172 and groupname like P_GROUPNAME
173 order by orderNumber;
174
175 BEGIN
176
177 P_TESTCLASSNAMES := JTF_VARCHAR2_TABLE_4000();
178 P_TESTTYPES := JTF_VARCHAR2_TABLE_4000();
179 P_TOTALARGROWS := JTF_NUMBER_TABLE();
180 P_TEST_LAST_UPDATED_BY := JTF_NUMBER_TABLE();
181
182 V_SIZE := 0;
183
184 FOR x in TESTLIST
185 LOOP
186 V_SIZE := V_SIZE + 1;
187 P_TESTCLASSNAMES.extend;
188 P_TESTTYPES.EXTEND;
189 P_TOTALARGROWS.EXTEND;
190 P_TEST_LAST_UPDATED_BY.EXTEND;
191 P_TESTCLASSNAMES(V_SIZE) := x.TESTCLASSNAME;
192 P_TESTTYPES(V_SIZE) := X.TESTTYPE;
193 P_TOTALARGROWS(V_SIZE) := X.TOTALARGUMENTROWS;
194 P_TEST_LAST_UPDATED_BY(V_SIZE) := X.LAST_UPDATED_BY;
195 END LOOP;
196
197 END GET_TESTS;
198
199 ----------------------------------------------------------
200 -- Begin procedure to GET ARGS FOR A TEST, GROUP, APP
201 ----------------------------------------------------------
202
203
204 procedure GET_ARGS(
205 P_APPID IN VARCHAR2,
206 P_GROUPNAME IN VARCHAR2,
207 P_TESTCLASSNAME IN VARCHAR2,
208 P_ARGNAMES OUT NOCOPY JTF_VARCHAR2_TABLE_4000,
209 P_ARGVALUES OUT NOCOPY JTF_VARCHAR2_TABLE_4000,
210 P_ROWNUMBERS OUT NOCOPY JTF_NUMBER_TABLE,
211 P_VALUESETNUM OUT NOCOPY JTF_NUMBER_TABLE) is
212
213 V_SIZE number;
214
215 cursor arglist is
216 select argname, argvalue, rownumber, valuesetnumber
217 from jtf_diagnostic_arg
218 where TestClassName = P_TESTCLASSNAME
219 and groupname = P_GROUPNAME
220 and appid = P_APPID
221 order by rownumber;
222
223 BEGIN
224
225 P_ARGNAMES := jtf_varchar2_table_4000();
226 P_ARGVALUES := jtf_varchar2_table_4000();
227 P_ROWNUMBERS := JTF_NUMBER_TABLE();
228 P_VALUESETNUM := JTF_NUMBER_TABLE();
229
230 V_SIZE := 0;
231
232 FOR x in arglist
233 LOOP
234 V_SIZE := V_SIZE + 1;
235
236 P_ARGNAMES.extend;
237 P_ARGVALUES.extend;
238 P_ROWNUMBERS.extend;
239 P_VALUESETNUM.extend;
240
241 P_ARGNAMES(V_SIZE) := x.ArgName;
242 P_argvalues(V_SIZE) := x.argvalue;
243 P_ROWNUMBERS(V_SIZE) := x.RowNumber;
244 P_VALUESETNUM(V_SIZE) := x.valuesetnumber;
245 END LOOP;
246
247 END GET_ARGS;
248
249
250 ----------------------------------------------------------
251 -- Begin procedure to GET PREREQS FOR AN APP OR A GROUP
252 -- Also gets the full name of the application based on
253 -- whether it is an application or a group
254 ----------------------------------------------------------
255
256
257 procedure GET_PREREQS(
258 P_APP_OR_GROUP_NAME IN VARCHAR2,
259 P_APPNAME IN VARCHAR2,
260 P_PREREQ_IDS OUT NOCOPY JTF_VARCHAR2_TABLE_4000,
261 P_PREREQ_NAMES OUT NOCOPY JTF_VARCHAR2_TABLE_4000
262 ) IS
263
264 V_SIZE NUMBER;
265 V_TEMP_NAME VARCHAR2(256);
266 V_TEMP_TYPE NUMBER;
267
268 CURSOR prereqlist is
269 select prereqid, type
270 from jtf_diagnostic_prereq
271 where sourceid = P_APP_OR_GROUP_NAME
272 and sourceappid = P_APPNAME;
273
274
275 BEGIN
276 V_SIZE := 0;
277 P_PREREQ_IDS := JTF_VARCHAR2_TABLE_4000();
278 P_PREREQ_NAMES := JTF_VARCHAR2_TABLE_4000();
279 V_TEMP_NAME := '';
280 V_TEMP_TYPE := 0;
281
282 FOR x in prereqlist
283 LOOP
284
285 V_SIZE := V_SIZE + 1;
286 P_PREREQ_IDS.extend;
287 P_PREREQ_NAMES.extend;
288 P_PREREQ_IDS(V_SIZE) := x.PREREQID;
289 V_TEMP_TYPE := x.TYPE;
290 V_TEMP_NAME := x.PREREQID;
291
292 IF V_TEMP_TYPE = 1 AND V_TEMP_NAME <> 'SYSTEM_TESTS' THEN
293
294 -- select a.application_name into V_TEMP_NAME
295 -- from fnd_application_tl a, fnd_application b
296 -- where b.APPLICATION_SHORT_NAME = x.PREREQID
297 -- and b.APPLICATION_ID = a.APPLICATION_ID
298 -- and a.language = userenv('LANG');
299 V_TEMP_NAME := '';
300
301 ELSIF V_TEMP_TYPE = 1 AND V_TEMP_NAME = 'SYSTEM_TESTS' THEN
302 -- V_TEMP_NAME := 'HTML Platform';
303 V_TEMP_NAME := '';
304
305 END IF;
306
307 P_PREREQ_NAMES(V_SIZE) := V_TEMP_NAME;
308
309 END LOOP;
310
311 END GET_PREREQS;
312
313
314
315 -- ----------------------------------------------------------------------
316 -- Updates a groups sensitivity in the database
317 -- ----------------------------------------------------------------------
318
319 procedure UPDATE_GROUP_SENSITIVITY(
320 P_APP_NAME IN VARCHAR2,
321 P_GROUP_NAME IN VARCHAR2,
322 P_GRP_SENSITIVITY IN NUMBER,
323 P_LUBID IN NUMBER
324 ) IS
325
326 BEGIN
327
328 UPDATE jtf_diagnostic_group
329 SET sensitivity = P_GRP_SENSITIVITY,
330 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
331 LAST_UPDATE_DATE = SYSDATE,
332 LAST_UPDATED_BY = P_LUBID
333 WHERE appid = P_APP_NAME AND
334 groupname = P_GROUP_NAME;
335
336 IF SQL%NOTFOUND THEN
337 RAISE_APPLICATION_ERROR(-20000,'Cant Update, Record Not Found');
338 END IF;
339
340
341 END UPDATE_GROUP_SENSITIVITY;
342
343
344 -- This is deprecated, please use the one above
345 procedure UPDATE_GROUP_SENSITIVITY(
346 P_APP_NAME IN VARCHAR2,
347 P_GROUP_NAME IN VARCHAR2,
348 P_GRP_SENSITIVITY IN NUMBER
349 ) IS
350 BEGIN
351
352 UPDATE_GROUP_SENSITIVITY(P_APP_NAME,
353 P_GROUP_NAME,
354 P_GRP_SENSITIVITY,
355 UID);
356
357 END UPDATE_GROUP_SENSITIVITY;
358
359
360 -----------------------------------------------------------
361 -- Updates a tests sensitivity in the database
362 ----------------------------------------------------------
363
364 procedure UPDATE_TEST_SENSITIVITY(
365 P_APP_NAME IN VARCHAR2,
366 P_GROUP_NAME IN VARCHAR2,
367 P_TEST_NAME IN VARCHAR2,
368 P_TST_SENSITIVITY IN NUMBER,
369 P_LUBID IN NUMBER
370 ) IS
371 BEGIN
372
373 UPDATE jtf_diagnostic_test
374 SET sensitivity = P_TST_SENSITIVITY,
375 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
376 LAST_UPDATE_DATE = SYSDATE,
377 LAST_UPDATED_BY = P_LUBID
378 WHERE appid = P_APP_NAME AND
379 groupname = P_GROUP_NAME AND
380 testclassname = P_TEST_NAME;
381
382 IF SQL%NOTFOUND THEN
383 RAISE_APPLICATION_ERROR(-20000,'Cant Update, Record Not
384 Found');
385 END IF;
386
387
388 END UPDATE_TEST_SENSITIVITY;
389
390 ----------------------------------------------------------
391 -- procedure DELETE AN APPLICATION
392 ----------------------------------------------------------
393
394 procedure DELETE_APP(
395 P_APP_NAME IN VARCHAR2
396 ) IS
397
398 BEGIN
399
400 delete from jtf_diagnostic_app
401 where appid = P_APP_NAME;
402
403 delete from jtf_diagnostic_group
404 where appid = P_APP_NAME;
405
406 delete from jtf_diagnostic_test
407 where appid = P_APP_NAME;
408
409 delete from jtf_diagnostic_arg
410 where appid = P_APP_NAME;
411
412 delete from jtf_diagnostic_prereq
413 where sourceappid = P_APP_NAME;
414
415 delete from jtf_diagnostic_prereq
416 where prereqid = P_APP_NAME;
417
418 END DELETE_APP;
419
420 ----------------------------------------------------------
421 -- procedure DELETE a GROUP FOR AN APPLICATION
422 ----------------------------------------------------------
423
424
425 procedure DELETE_GROUP(
426 P_APP_NAME IN VARCHAR2,
427 P_GROUP_NAME IN VARCHAR2
428 ) IS
429
430 V_ORDERNUM jtf_diagnostic_group.ordernumber%TYPE;
431
432 l_groupname varchar2(500);
433 l_ordernumber number;
434 l_object_version_number number;
435
436 check_extraneous_only boolean := false;
437
438 -- SKHEMANI Use the cursor to cleanup the
439 -- entry in the JTF_DIAGNOSTIC_KB table
440
441 -- there were extraneous decl_test_steps left
442 CURSOR TSTSTEPLIST IS
443 select testclassname, teststepname from jtf_diagnostic_decl_test_steps
444 where appid = P_APP_NAME
445 and groupname = P_GROUP_NAME;
446
447 CURSOR TSTLIST IS
448 select testclassname from jtf_diagnostic_test
449 where appid = P_APP_NAME
450 and groupname = P_GROUP_NAME;
451
452 CURSOR GRPLIST (c_ordernumber number) IS
453 select groupname, ordernumber, object_version_number
454 from jtf_diagnostic_group
455 where appid = P_APP_NAME
456 and ordernumber > c_ordernumber
457 order by ordernumber;
458
459 BEGIN
460
461 -- populate the variable v_ordernum
462 -- so that we can use this for resequencing
463
464 select distinct count(*) into V_ORDERNUM
465 from jtf_diagnostic_group
466 where APPID = P_APP_NAME and groupname = P_GROUP_NAME;
467
468 -- if the ordernumber not found then no point continuing with
469 -- the rest, just raise an exception
470
471 IF v_ordernum = 0 THEN
472 check_extraneous_only := true;
473 END IF;
474
475 IF (check_extraneous_only = FALSE) THEN
476 -- if application error not raised then
477 -- get the right order number into the variable
478 -- for further processing
479
480 select distinct ordernumber into V_ORDERNUM
481 from jtf_diagnostic_group
482 where APPID = P_APP_NAME and groupname = P_GROUP_NAME;
483
484 -- if flow of control reaches here,
485 -- cleanup all information about this group
486 -- from the jtf_diagnostic_group table
487
488 delete from jtf_diagnostic_group
489 where groupname = P_GROUP_NAME
490 and appid = P_APP_NAME;
491
492 -- Resequence the groups to make sure there are no holes in the
493 -- groups of this application
494
495 open GRPLIST(V_ORDERNUM);
496 loop
497 fetch GRPLIST into l_groupname, l_ordernumber, l_object_version_number;
498 exit when (GRPLIST%notfound);
499 update jtf_diagnostic_group
500 set ordernumber = (l_ordernumber - 1),
501 OBJECT_VERSION_NUMBER = l_OBJECT_VERSION_NUMBER + 1,
502 LAST_UPDATE_DATE = SYSDATE
503 where groupname = l_groupname
504 and appid = P_APP_NAME;
505 end loop;
506 close GRPLIST;
507
508 -- SKHEMANI Use the cursor to cleanup the
509 -- entries of all tests in the JTF_DIAGNOSTIC_KB table
510 -- pertaining to this group, if any
511
512 FOR x in TSTLIST
513 LOOP
514 delete_test(P_APP_NAME, P_GROUP_NAME, x.testclassname);
515 END LOOP;
516
517 -- called separately from delete_test to get rid of extraneous data
518
519 -- fix for bug 4606418, we were not cleaning
520 -- up the prereqs of a group at the time of deleting a
521 -- group
522
523 delete from jtf_diagnostic_prereq
524 where sourceid = P_GROUP_NAME
525 and sourceappid = P_APP_NAME;
526
527 ELSE
528
529 FOR x in TSTSTEPLIST
530 LOOP
531 delete_decl_test_step(P_APP_NAME, P_GROUP_NAME, x.testclassname, x.teststepname);
532 END LOOP;
533
534 END IF;
535
536 END DELETE_GROUP;
537
538
539 ----------------------------------------------------------
540 -- procedure delete a test for a group and app
541 ----------------------------------------------------------
542
543
544 procedure DELETE_TEST(
545 P_APP_NAME IN VARCHAR2,
546 P_GROUP_NAME IN VARCHAR2,
547 P_TEST_CLASS_NAME IN VARCHAR2
548 ) IS
549
550 V_ORDERNUM jtf_diagnostic_test.ordernumber%TYPE;
551 V_SEQUENCE jtf_diagnostic_test.sequence%TYPE;
552
553 l_testclassname varchar2(1500);
554 l_ordernumber number;
555 l_object_version_number number;
556
557 check_extraneous_only boolean := false;
558
559 CURSOR TESTLIST (c_ordernumber number) IS
560 select testclassname, ordernumber, object_version_number
561 from jtf_diagnostic_test
562 where appid = P_APP_NAME
563 and groupname = P_GROUP_NAME
564 and ordernumber > c_ordernumber
565 order by ordernumber;
566
567 BEGIN
568
569 select count(*) into V_ORDERNUM
570 from jtf_diagnostic_test
571 where APPID = P_APP_NAME
572 and groupname = P_GROUP_NAME
573 and testclassname = P_TEST_CLASS_NAME
574 and rownum <= 1;
575
576 -- if the ordernumber not found then no point continuing with
577 -- the rest, just raise an exception
578
579 IF v_ordernum = 0 THEN
580 check_extraneous_only := true;
581 END IF;
582
583 IF (check_extraneous_only = FALSE) THEN
584 -- SKHEMANI if flow of control reaches here, then the test has been found
585 -- great... we will use this sequence number to cleanup the
586 -- entry in the JTF_DIAGNOSTIC_KB table
587
588 select sequence into V_SEQUENCE
589 from jtf_diagnostic_test
590 where APPID = P_APP_NAME
591 and groupname = P_GROUP_NAME
592 and testclassname = P_TEST_CLASS_NAME
593 and rownum <= 1;
594
595 -- SKHEMANI Use the stored sequence number to cleanup the
596 -- entry in the JTF_DIAGNOSTIC_KB table
597
598 delete from jtf_diagnostic_kb where
599 sequence = V_SEQUENCE;
600
601
602 -- populate the variable v_ordernum
603 -- so that we can use this for resequencing
604 -- incase there are more than one testcases with the same classname
605 -- they should all be deleted since a group should have the same testclassname
606 -- appearing once in it
607
608 select ordernumber into V_ORDERNUM
609 from jtf_diagnostic_test
610 where APPID = P_APP_NAME
611 and groupname = P_GROUP_NAME
612 and testclassname = P_TEST_CLASS_NAME
613 and rownum <= 1;
614
615
616 -- cleanup all information about this test
617 -- from the jtf_diagnostic_test table
618
619 delete from jtf_diagnostic_test
620 where groupname = P_GROUP_NAME
621 and appid = P_APP_NAME
622 and testclassname = P_TEST_CLASS_NAME;
623
624 -- Resequence the testcases to make sure there are no holes in the
625 -- testcases of this group and application
626
627 open TESTLIST(V_ORDERNUM);
628 loop
629 fetch TESTLIST into l_testclassname, l_ordernumber,
630 l_object_version_number;
631 exit when (TESTLIST%notfound);
632 update jtf_diagnostic_test
633 set ordernumber = (l_ordernumber - 1),
634 OBJECT_VERSION_NUMBER = l_OBJECT_VERSION_NUMBER + 1,
635 LAST_UPDATE_DATE = SYSDATE
636 where testclassname = l_testclassname
637 and appid = P_APP_NAME
638 and groupname = P_GROUP_NAME;
639 end loop;
640 close TESTLIST;
641
642 -- cleanup all information about this testcase (testcase arguments)
643 -- from the jtf_diagnostic_arg table
644
645 delete from jtf_diagnostic_arg
646 where groupname = P_GROUP_NAME
647 and appid = P_APP_NAME
648 and testclassname = P_TEST_CLASS_NAME;
649
650 END IF;
651
652 -- cleanup all information about all teststeps of this testcase
653 -- from the jtf_diagnostic_decl_test_steps table
654
655 delete from jtf_diagnostic_decl_test_steps
656 where groupname = P_GROUP_NAME
657 and appid = P_APP_NAME
658 and testclassname = P_TEST_CLASS_NAME;
659
660 -- cleanup all information about all teststeps of this testcase
661 -- from the jtf_diagnostic_decl_step_cols table
662
663 delete from jtf_diagnostic_decl_step_cols
664 where groupname = P_GROUP_NAME
665 and appid = P_APP_NAME
666 and testclassname = P_TEST_CLASS_NAME;
667
668 -- commit;
669
670 END DELETE_TEST;
671
672
673
674
675 procedure DELETE_ALL_ARGS_FOR_TEST(
676 P_APP_NAME IN VARCHAR2,
677 P_GROUP_NAME IN VARCHAR2,
678 P_TEST_CLASS_NAME IN VARCHAR2
679 ) IS
680
681 BEGIN
682 delete from jtf_diagnostic_arg
683 where testclassname = P_TEST_CLASS_NAME
684 and groupname = P_GROUP_NAME
685 and appid = P_APP_NAME;
686 END DELETE_ALL_ARGS_FOR_TEST;
687
688
689
690 ----------------------------------------------------------
691 -- procedure delete arguments for a testclassname, given
692 -- a row number, application id and group name
693 ----------------------------------------------------------
694
695
696 procedure DELETE_ARG_SET(
697 P_APP_NAME IN VARCHAR2,
698 P_GROUP_NAME IN VARCHAR2,
699 P_TEST_CLASS_NAME IN VARCHAR2,
700 P_ARG_ROW_NUM IN NUMBER
701 ) IS
702
703 BEGIN
704
705 -- remove the argument combination corresponding to the
706 -- testcase where we get the rownumber from the UI / Java layer
707 -- where each rownumber corresponds to one combination of
708 -- arguments which we will just delete
709
710 delete from jtf_diagnostic_arg
711 where testclassname = P_TEST_CLASS_NAME
712 and groupname = P_GROUP_NAME
713 and appid = P_APP_NAME
714 and rownumber = P_ARG_ROW_NUM;
715
716 -- If a row was deleted, then bump down the
717 -- number of argument rows for jtf_diagnostic_test
718
719 IF NOT SQL%NOTFOUND THEN
720 update jtf_diagnostic_test
721 set totalargumentrows = (totalargumentrows - 1),
722 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
723 LAST_UPDATE_DATE = SYSDATE
724 where testclassname = P_TEST_CLASS_NAME
725 and groupname = P_GROUP_NAME
726 and appid = P_APP_NAME;
727
728 update jtf_diagnostic_arg
729 set rownumber = (rownumber - 1),
730 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
731 LAST_UPDATE_DATE = SYSDATE
732 where testclassname = P_TEST_CLASS_NAME
733 and groupname = P_GROUP_NAME
734 and appid = P_APP_NAME
735 and rownumber > P_ARG_ROW_NUM;
736 END IF;
737
738 -- commit;
739
740 END DELETE_ARG_SET;
741
742 ------------------------------
743 -- Begin procedure DELETE_DECL_TEST_STEP
744 ------------------------------
745
746 procedure DELETE_DECL_TEST_STEP(
747 P_APPID IN VARCHAR2,
748 P_GROUPNAME IN VARCHAR2,
749 P_TESTCLASSNAME IN VARCHAR2,
750 P_TESTSTEPNAME IN VARCHAR2) is
751
752 V_ORDERNUM number;
753
754 l_teststepname varchar2(200);
755 l_ordernumber number;
756
757 v_reorder boolean;
758
759 CURSOR DECLTESTLIST (c_ordernumber number) IS
760 select teststepname, execution_sequence
761 from jtf_diagnostic_decl_test_steps
762 where appid = P_APPID
763 and groupname = P_GROUPNAME
764 and testclassname = P_TESTCLASSNAME
765 and execution_sequence > c_ordernumber
766 order by execution_sequence;
767
768 BEGIN
769
770 select execution_sequence into v_ordernum
771 from jtf_diagnostic_decl_test_steps
772 where appid = P_APPID and groupname = P_GROUPNAME
773 and testclassname = P_TESTCLASSNAME and teststepname = P_TESTSTEPNAME;
774
775 IF v_ordernum is null then
776 v_reorder := false;
777 ELSE
778 v_reorder := true;
779 END IF;
780
781 delete from jtf_diagnostic_decl_test_steps
782 where appid = P_APPID
783 and groupname = P_GROUPNAME
784 and testclassname = P_TESTCLASSNAME
785 and teststepname = P_TESTSTEPNAME;
786
787 IF v_reorder THEN
788 open DECLTESTLIST(V_ORDERNUM);
789 loop
790 fetch DECLTESTLIST into l_teststepname, l_ordernumber;
791 exit when (DECLTESTLIST%notfound);
792 update jtf_diagnostic_decl_test_steps
793 set execution_sequence = (l_ordernumber - 1),
794 LAST_UPDATE_DATE = SYSDATE
795 where teststepname = l_teststepname
796 and appid = P_APPID
797 and groupname = P_GROUPNAME
798 and testclassname = P_TESTCLASSNAME;
799 end loop;
800 close DECLTESTLIST;
801 END IF;
802
803 delete from jtf_diagnostic_decl_test_steps
804 where appid = P_APPID
805 and groupname = P_GROUPNAME
806 and testclassname = P_TESTCLASSNAME
807 and teststepname = P_TESTSTEPNAME;
808
809 delete from jtf_diagnostic_decl_step_cols
810 where groupname = P_GROUPNAME
811 and appid = P_APPID
812 and testclassname = P_TESTCLASSNAME;
813
814 END DELETE_DECL_TEST_STEP;
815
816 ------------------------------
817 -- Begin procedure UPDATE_GROUP_SEQ
818 ------------------------------
819
820
821 procedure UPDATE_GROUP_SEQ(
822 P_APPID IN VARCHAR2,
823 P_GROUPNAMES IN JTF_VARCHAR2_TABLE_4000,
824 P_LUBID IN NUMBER) is
825
826 v_numofrows NUMBER;
827 v_index BINARY_INTEGER := 1;
828
829
830 BEGIN
831
832 SELECT COUNT(*)
833 INTO v_numofrows
834 FROM jtf_diagnostic_group
835 WHERE appid = P_APPID;
836
837 IF P_GROUPNAMES.COUNT <> v_numofrows THEN
838 --RAISE_APPLICATION_ERROR(-20000, 'Cant Update - Mismatch');
839 RAISE_APPLICATION_ERROR(-20000, 'UPDATE_GROUP_SEQ(): Cannot Update -
840 Mismatch. P_APPID=' || P_APPID|| ' ; v_numofrows='||v_numofrows ||' ;
841 P_GROUPNAMES.COUNT='||P_GROUPNAMES.COUNT);
842 END IF;
843
844 LOOP
845 IF P_GROUPNAMES.EXISTS(v_index) THEN
846
847 UPDATE jtf_diagnostic_group
848 SET ordernumber = v_index * -1
849 WHERE appid = P_APPID AND
850 ordernumber = v_index;
851
852 UPDATE jtf_diagnostic_group
853 SET ordernumber = v_index,
854 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
855 LAST_UPDATE_DATE = SYSDATE
856 --LAST_UPDATED_BY = P_LUBID
857 WHERE appid = P_APPID AND
858 groupname = P_GROUPNAMES(v_index);
859
860 IF SQL%NOTFOUND THEN
861 RAISE_APPLICATION_ERROR(-20000,'Cant Update, Record Not Found');
862 END IF;
863 v_index := v_index + 1;
864 ELSE
865 EXIT;
866 END IF;
867 END LOOP;
868
869 -- commit;
870
871 END UPDATE_GROUP_SEQ;
872
873
874 -- deprecated, please use the one above
875
876 procedure UPDATE_GROUP_SEQ(
877 P_APPID IN VARCHAR2,
878 P_GROUPNAMES IN JTF_VARCHAR2_TABLE_4000
879 ) IS
880
881 BEGIN
882
883 UPDATE_GROUP_SEQ(P_APPID,
884 P_GROUPNAMES,
885 UID);
886
887
888 END UPDATE_GROUP_SEQ;
889
890 ----------------------------------
891 -- Begin procedure UPDATE_TEST_SEQ
892 ----------------------------------
893
894
895 procedure UPDATE_TEST_SEQ(
896 P_APPID IN VARCHAR2,
897 P_GROUPNAME IN VARCHAR2,
898 P_TESTCLASSNAMES IN JTF_VARCHAR2_TABLE_4000,
899 P_LUBID IN NUMBER) is
900
901 v_numofrows NUMBER;
902 v_index BINARY_INTEGER := 1;
903
904 BEGIN
905
906 SELECT COUNT(*)
907 INTO v_numofrows
908 FROM jtf_diagnostic_test
909 WHERE appid = P_APPID AND
910 groupname = P_GROUPNAME;
911
912 IF P_TESTCLASSNAMES.COUNT <> v_numofrows THEN
913 --RAISE_APPLICATION_ERROR(-20000, 'Cant Update - Mismatch');
914 RAISE_APPLICATION_ERROR(-20000, 'UPDATE_TEST_SEQ(): Cannot Update -
915 Mismatch. P_APPID=' || P_APPID|| ' ; P_GROUPNAME='|| P_GROUPNAME || ' ;v_numofrows='||v_numofrows ||' ;
916 P_TESTCLASSNAMES.COUNT='||P_TESTCLASSNAMES.COUNT);
917 END IF;
918
919 LOOP
920 IF P_TESTCLASSNAMES.EXISTS(v_index) THEN
921
922 UPDATE jtf_diagnostic_test
923 SET ordernumber = v_index * -1
924 WHERE appid = P_APPID AND
925 groupname = p_groupname and
926 ordernumber = v_index;
927
928
929 UPDATE jtf_diagnostic_test
930 SET OrderNumber = v_index,
931 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
932 LAST_UPDATE_DATE = SYSDATE
933 --LAST_UPDATED_BY = P_LUBID
934 WHERE appid = P_APPID AND
935 groupname = P_GROUPNAME AND
936 testclassname = P_TESTCLASSNAMES(v_index);
937
938 IF SQL%NOTFOUND THEN
939 RAISE_APPLICATION_ERROR(-20000,'Cant Update, Record Not Found');
940 END IF;
941 v_index := v_index + 1;
942 ELSE
943 EXIT;
944 END IF;
945 END LOOP;
946
947 -- commit;
948
949 END UPDATE_TEST_SEQ;
950
951
952 procedure UPDATE_TEST_SEQ(
953 P_APPID IN VARCHAR2,
954 P_GROUPNAME IN VARCHAR2,
955 P_TESTCLASSNAMES IN JTF_VARCHAR2_TABLE_4000
956 ) IS
957
958 BEGIN
959
960 UPDATE_TEST_SEQ(P_APPID,
961 P_GROUPNAME,
962 P_TESTCLASSNAMES,
963 UID);
964
965 END UPDATE_TEST_SEQ;
966
967 ---------------------------------
968 -- Begin procedure UPDATE_PREREQS
969 ---------------------------------
970
971
972 procedure UPDATE_PREREQS(
973 P_SOURCEID IN VARCHAR2,
974 P_SOURCEAPPID IN VARCHAR2,
975 P_PREREQID IN JTF_VARCHAR2_TABLE_4000,
976 P_SOURCETYPE IN NUMBER,
977 P_LUBID IN NUMBER) IS
978
979 v_index BINARY_INTEGER := 1;
980 v_data_found BINARY_INTEGER := 0;
981
982
983
984 BEGIN
985
986 CHECK_APP_OR_GROUP_VALIDITY(P_SOURCEID,P_SOURCEAPPID,P_SOURCETYPE);
987
988 -- if flow of control has reached thus far, remove all records
989 -- for the sourceid supplied to the pl/sql layer
990
991 delete from jtf_diagnostic_prereq
992 where sourceid = p_sourceid
993 and sourceappid = p_sourceappid;
994
995 IF P_PREREQID IS NOT NULL AND P_PREREQID.COUNT > 0 THEN
996 PREREQ_INSERTION(P_SOURCEID,P_SOURCEAPPID,P_PREREQID,P_SOURCETYPE,P_LUBID);
997 END IF;
998
999 -- commit;
1000 END UPDATE_PREREQS;
1001
1002 -- deprecated, please use procedure above
1003 procedure UPDATE_PREREQS(
1004 P_SOURCEID IN VARCHAR2,
1005 P_SOURCEAPPID IN VARCHAR2,
1006 P_PREREQID IN JTF_VARCHAR2_TABLE_4000,
1007 P_SOURCETYPE IN NUMBER) IS
1008
1009 BEGIN
1010
1011 UPDATE_PREREQS(P_SOURCEID,
1012 P_SOURCEAPPID,
1013 P_PREREQID,
1014 P_SOURCETYPE,
1015 UID);
1016
1017 END UPDATE_PREREQS;
1018
1019 ------------------------------
1020 -- Begin procedure UPDATE_ARG_VALUES
1021 ------------------------------
1022 --mls
1023
1024 procedure UPDATE_ARG_VALUES(
1025 P_TESTCLASSNAME IN VARCHAR2,
1026 P_GROUPNAME IN VARCHAR2,
1027 P_APPID IN VARCHAR2,
1028 P_ARGNAMES IN JTF_VARCHAR2_TABLE_4000,
1029 P_ARGVALUES IN JTF_VARCHAR2_TABLE_4000,
1030 p_arg_dates in jtf_date_table,
1031 p_arg_numbers in jtf_number_table,
1032 P_ROWNUMBER IN NUMBER,
1033 P_LUBID IN NUMBER) is
1034
1035 v_index BINARY_INTEGER := 1;
1036
1037 BEGIN
1038
1039 loop
1040 if p_argnames.EXISTS(v_index) AND p_argvalues.exists(v_index) then
1041
1042 update jtf_diagnostic_arg set
1043 argvalue = p_argvalues(v_index),
1044 arg_date_value = p_arg_dates(v_index),
1045 arg_number_value = p_arg_numbers(v_index),
1046 object_version_number = object_version_number + 1,
1047 -- last_updated_by = UID,
1048 last_updated_by = P_LUBID,
1049 last_update_date = sysdate
1050 where argname = p_argnames(v_index)
1051 and rownumber = p_rownumber
1052 and testclassname = p_testclassname
1053 and groupname = p_groupname
1054 and appid = p_appid;
1055
1056
1057 if sql%notfound then
1058 raise_application_error(-20000,
1059 'Invalid data received -- no record found to update');
1060 end if;
1061
1062 -- increment the counter
1063 v_index := v_index + 1;
1064 else
1065 exit;
1066 end if;
1067
1068
1069
1070 end loop;
1071 -- commit;
1072 END UPDATE_ARG_VALUES;
1073 --mls
1074
1075 procedure UPDATE_ARG_VALUES(
1076 P_TESTCLASSNAME IN VARCHAR2,
1077 P_GROUPNAME IN VARCHAR2,
1078 P_APPID IN VARCHAR2,
1079 P_ARGNAMES IN JTF_VARCHAR2_TABLE_4000,
1080 P_ARGVALUES IN JTF_VARCHAR2_TABLE_4000,
1081 P_ROWNUMBER IN NUMBER,
1082 P_LUBID IN NUMBER) is
1083
1084 v_index BINARY_INTEGER := 1;
1085
1086 BEGIN
1087
1088 loop
1089 if p_argnames.EXISTS(v_index) AND p_argvalues.exists(v_index) then
1090
1091 update jtf_diagnostic_arg set
1092 argvalue = p_argvalues(v_index),
1093 object_version_number = object_version_number + 1,
1094 -- last_updated_by = UID,
1095 last_updated_by = P_LUBID,
1096 last_update_date = sysdate
1097 where argname = p_argnames(v_index)
1098 and rownumber = p_rownumber
1099 and testclassname = p_testclassname
1100 and groupname = p_groupname
1101 and appid = p_appid;
1102
1103
1104 if sql%notfound then
1105 raise_application_error(-20000,
1106 'Invalid data received -- no record found to update');
1107 end if;
1108
1109 -- increment the counter
1110 v_index := v_index + 1;
1111 else
1112 exit;
1113 end if;
1114
1115
1116
1117 end loop;
1118 -- commit;
1119 END UPDATE_ARG_VALUES;
1120
1121 -- deprecated, please use procedure above
1122 procedure UPDATE_ARG_VALUES(
1123 P_TESTCLASSNAME IN VARCHAR2,
1124 P_GROUPNAME IN VARCHAR2,
1125 P_APPID IN VARCHAR2,
1126 P_ARGNAMES IN JTF_VARCHAR2_TABLE_4000,
1127 P_ARGVALUES IN JTF_VARCHAR2_TABLE_4000,
1128 P_ROWNUMBER IN NUMBER
1129 ) IS
1130
1131 BEGIN
1132
1133 UPDATE_ARG_VALUES(P_TESTCLASSNAME,
1134 P_GROUPNAME,
1135 P_APPID,
1136 P_ARGNAMES,
1137 P_ARGVALUES,
1138 P_ROWNUMBER,
1139 UID);
1140
1141 END UPDATE_ARG_VALUES;
1142
1143 -- ----------------------------------------------------------------------
1144 -- Insert an app into the framework with or without prereqs
1145 -- ----------------------------------------------------------------------
1146
1147 procedure INSERT_APP(
1148 P_APPID IN VARCHAR2,
1149 P_PREREQID IN JTF_VARCHAR2_TABLE_4000,
1150 P_LUBID IN NUMBER) IS
1151
1152 -- v_asn fnd_application.application_short_name%TYPE;
1153 V_SOURCETYPE BINARY_INTEGER := 1;
1154
1155 BEGIN
1156
1157 -- check if the application value entered is
1158 -- a valid application in the fnd_application table
1159 -- and if yes it should not already be there in the
1160 -- jtf_diagnostic_app table
1161
1162 -- select distinct application_short_name into v_asn
1163 -- from fnd_application
1164 -- where application_short_name = P_APPID
1165 -- and rownum <= 1;
1166
1167 -- if sql%notfound then
1168 -- raise_application_error(-20000,
1169 -- 'Invalid application short name');
1170 -- else
1171 select count(*) into v_sourcetype
1172 from jtf_diagnostic_app
1173 where appid = P_APPID;
1174
1175 if v_sourcetype <> 0 then
1176 raise_application_error(-20000,
1177 'Application already registered');
1178 end if;
1179 -- end if;
1180
1181 v_sourcetype := 1;
1182
1183 -- Else create a new record
1184 -- since the new value seems fine
1185
1186 insert into jtf_diagnostic_app(
1187 SEQUENCE, APPID, OBJECT_VERSION_NUMBER, CREATED_BY,
1188 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE)
1189 values (JTF_DIAGNOSTIC_APP_S.NEXTVAL, P_APPID, 1, P_LUBID,
1190 SYSDATE, P_LUBID, NULL, SYSDATE);
1191
1192
1193 -- Now check if the object received as the pre-req array
1194 -- is not null in which case call the insertion routine
1195
1196 IF NOT P_PREREQID IS NULL then
1197 V_SOURCETYPE := 1;
1198 PREREQ_INSERTION(P_APPID, P_APPID, P_PREREQID, V_SOURCETYPE, P_LUBID);
1199 end if;
1200
1201 -- commit;
1202
1203 END INSERT_APP;
1204
1205 -- deprecated, please use procedure above
1206 procedure INSERT_APP(
1207 P_APPID IN VARCHAR2,
1208 P_PREREQID IN JTF_VARCHAR2_TABLE_4000
1209 ) IS
1210
1211 BEGIN
1212
1213 INSERT_APP(P_APPID,
1214 P_PREREQID,
1215 UID);
1216
1217 END INSERT_APP;
1218
1219
1220
1221 -- ----------------------------------------------------------------------
1222 -- Insert Group with or without prereqs with out SENSITIVITY
1223 -- ----------------------------------------------------------------------
1224
1225 procedure INSERT_GRP(
1226 P_NEW_GROUP IN VARCHAR2,
1227 P_APP IN VARCHAR2,
1228 P_PREREQID IN JTF_VARCHAR2_TABLE_4000,
1229 P_LUBID IN NUMBER) IS
1230
1231 v_groupname jtf_diagnostic_group.groupname%TYPE;
1232 V_SOURCETYPE BINARY_INTEGER := 2;
1233 v_ordernumber jtf_diagnostic_group.ordernumber%TYPE;
1234
1235
1236 BEGIN
1237
1238 -- Check if application is valid
1239 V_SOURCETYPE := 1;
1240 CHECK_APP_OR_GROUP_VALIDITY(P_APP, P_APP, V_SOURCETYPE);
1241
1242 -- if flow of control reached here, implies that the
1243 -- application is a valid application in the diagnostic framework
1244
1245
1246 -- now check if the group value entered is
1247 -- not already there in the tables for the application
1248 -- and if yes it should not be reentered
1249
1250 select count(*) into v_sourcetype
1251 from jtf_diagnostic_group
1252 where appid = P_APP and groupname = p_new_group
1253 and rownum <= 1;
1254
1255 -- if anything found then raise an application error since the
1256 -- same group cannot be added multiple times
1257
1258 if v_sourcetype <> 0 then
1259 raise_application_error(-20000,
1260 'Group already exist. Cannot reenter');
1261 end if;
1262
1263 -- reset the old v_sourcetype value
1264 v_sourcetype := 1;
1265
1266
1267 -- Else create a new record
1268 -- since the new value seems fine
1269 -- but first find out the highest number of order of groups
1270 -- and add this to the end. If there are no groups
1271 -- then make sure that this gets the first order number
1272
1273 select MAX(ordernumber) into v_ordernumber
1274 from jtf_diagnostic_group where appid = p_app;
1275
1276 if sql%notfound or v_ordernumber = 0 then
1277 v_ordernumber := 1;
1278 else v_ordernumber := v_ordernumber + 1;
1279
1280 end if;
1281
1282
1283 insert into jtf_diagnostic_group(
1284 SEQUENCE, GROUPNAME,
1285 APPID, ORDERNUMBER,
1286 OBJECT_VERSION_NUMBER, CREATED_BY,
1287 LAST_UPDATE_DATE, LAST_UPDATED_BY,
1288 LAST_UPDATE_LOGIN, CREATION_DATE)
1289 values(
1290 JTF_DIAGNOSTIC_GROUP_S.NEXTVAL, P_NEW_GROUP,
1291 P_APP, DECODE(v_ordernumber,null,1,v_ordernumber),
1292 1, P_LUBID,
1293 SYSDATE, P_LUBID,
1294 NULL, SYSDATE);
1295
1296
1297 -- Now check if the object received as the pre-req array
1298 -- is not null in which case call the insertion routine
1299
1300 IF NOT P_PREREQID IS NULL then
1301 V_SOURCETYPE := 2;
1302 PREREQ_INSERTION(P_NEW_GROUP, P_APP, P_PREREQID, V_SOURCETYPE);
1303 end if;
1304
1305 -- commit;
1306
1307 END INSERT_GRP;
1308
1309 procedure INSERT_GRP(
1310 P_NEW_GROUP IN VARCHAR2,
1311 P_APP IN VARCHAR2,
1312 P_PREREQID IN JTF_VARCHAR2_TABLE_4000
1313 ) IS
1314
1315 BEGIN
1316
1317 INSERT_GRP(P_NEW_GROUP,
1318 P_APP,
1319 P_PREREQID,
1320 UID);
1321
1322 END INSERT_GRP;
1323
1324 -- ----------------------------------------------------------------------
1325 -- Insert Group with or without prereqs -DEPRECATED
1326 -- ----------------------------------------------------------------------
1327
1328 procedure INSERT_GROUP(
1329 P_NEW_GROUP IN VARCHAR2,
1330 P_APP IN VARCHAR2,
1331 P_PREREQID IN JTF_VARCHAR2_TABLE_4000,
1332 P_SENSITIVITY IN NUMBER,
1333 P_LUBID IN NUMBER) IS
1334
1335 v_groupname jtf_diagnostic_group.groupname%TYPE;
1336 V_SOURCETYPE BINARY_INTEGER := 2;
1337 v_ordernumber jtf_diagnostic_group.ordernumber%TYPE;
1338
1339
1340 BEGIN
1341
1342 -- Check if application is valid
1343 V_SOURCETYPE := 1;
1344 CHECK_APP_OR_GROUP_VALIDITY(P_APP, P_APP, V_SOURCETYPE);
1345
1346 -- if flow of control reached here, implies that the
1347 -- application is a valid application in the diagnostic framework
1348
1349
1350 -- now check if the group value entered is
1351 -- not already there in the tables for the application
1352 -- and if yes it should not be reentered
1353
1354 select count(*) into v_sourcetype
1355 from jtf_diagnostic_group
1356 where appid = P_APP and groupname = p_new_group
1357 and rownum <= 1;
1358
1359 -- if anything found then raise an application error since the
1360 -- same group cannot be added multiple times
1361
1362 if v_sourcetype <> 0 then
1363 raise_application_error(-20000,
1364 'Group already exist. Cannot reenter');
1365 end if;
1366
1367 -- reset the old v_sourcetype value
1368 v_sourcetype := 1;
1369
1370
1371 -- Else create a new record
1372 -- since the new value seems fine
1373 -- but first find out the highest number of order of groups
1374 -- and add this to the end. If there are no groups
1375 -- then make sure that this gets the first order number
1376
1377 select MAX(ordernumber) into v_ordernumber
1378 from jtf_diagnostic_group where appid = p_app;
1379
1380 if sql%notfound or v_ordernumber = 0 then
1381 v_ordernumber := 1;
1382 else v_ordernumber := v_ordernumber + 1;
1383
1384 end if;
1385
1386
1387 insert into jtf_diagnostic_group(
1388 SEQUENCE, GROUPNAME,
1389 APPID, ORDERNUMBER,
1390 OBJECT_VERSION_NUMBER, CREATED_BY,
1391 LAST_UPDATE_DATE, LAST_UPDATED_BY,
1392 LAST_UPDATE_LOGIN, CREATION_DATE, SENSITIVITY)
1393 values(
1394 JTF_DIAGNOSTIC_GROUP_S.NEXTVAL, P_NEW_GROUP,
1395 P_APP, DECODE(v_ordernumber,null,1,v_ordernumber),
1396 1, P_LUBID,
1397 SYSDATE, P_LUBID,
1398 NULL, SYSDATE, P_SENSITIVITY);
1399
1400
1401 -- Now check if the object received as the pre-req array
1402 -- is not null in which case call the insertion routine
1403
1404 IF NOT P_PREREQID IS NULL then
1405 V_SOURCETYPE := 2;
1406 PREREQ_INSERTION(P_NEW_GROUP, P_APP, P_PREREQID, V_SOURCETYPE);
1407 end if;
1408
1409 -- commit;
1410
1411 END INSERT_GROUP;
1412
1413 procedure INSERT_GROUP(
1414 P_NEW_GROUP IN VARCHAR2,
1415 P_APP IN VARCHAR2,
1416 P_PREREQID IN JTF_VARCHAR2_TABLE_4000,
1417 P_SENSITIVITY IN NUMBER
1418 ) IS
1419
1420 BEGIN
1421
1422 INSERT_GROUP(P_NEW_GROUP,
1423 P_APP,
1424 P_PREREQID,
1425 P_SENSITIVITY,
1426 UID);
1427
1428 END INSERT_GROUP;
1429
1430 -- ----------------------------------------------------------------------
1431 -- Insert testcase to a group of an application
1432 -- ----------------------------------------------------------------------
1433
1434 procedure GET_GROUP_SENSITIVITY(p_appid in varchar2,
1435 p_group_name in varchar2,
1436 p_sensitivity out NOCOPY number) IS
1437
1438 begin
1439 select sensitivity into p_sensitivity
1440 from jtf_diagnostic_group
1441 where appid = p_appid and
1442 groupname = p_group_name;
1443 end GET_GROUP_SENSITIVITY;
1444
1445
1446 procedure INSERT_TESTCASE(p_testclassname in varchar2,
1447 p_group_name in varchar2,
1448 p_appid in varchar2,
1449 p_test_type in varchar2,
1450 p_sensitivity in number,
1451 p_valid_apps_xml in varchar2,
1452 p_end_date in date default null,
1453 p_meta_data in varchar2,
1454 p_lubid in number) IS
1455
1456 V_SOURCETYPE BINARY_INTEGER := 2;
1457 v_ordernumber jtf_diagnostic_test.ordernumber%TYPE;
1458 l_sensitivity number;
1459 f_end_date date;
1460 f_meta_data xmltype;
1461
1462 BEGIN
1463
1464 -- Check for groupname validity
1465 v_sourcetype := 2;
1466 CHECK_APP_OR_GROUP_VALIDITY(P_group_name, P_APPID, V_SOURCETYPE);
1467
1468 -- Check for application validity
1469 v_sourcetype := 1;
1470 CHECK_APP_OR_GROUP_VALIDITY(P_APPID, P_APPID, V_SOURCETYPE);
1471
1472 -- Now make sure that this testcase does not already exist in
1473 -- the table. The same testcase should not exist 2 times in the
1474 -- group
1475
1476 select count(*) into V_SOURCETYPE
1477 from jtf_diagnostic_test
1478 where appid = p_appid and groupname = p_group_name
1479 and testclassname = p_testclassname
1480 and rownum <= 1;
1481
1482 if v_sourcetype > 0 then
1483 raise_application_error(-20000,
1484 'Testclassname already found in group and application');
1485 end if;
1486
1487
1488 -- bring the v_sourcetype back to original value
1489 v_sourcetype := 1;
1490
1491 -- default test sensitivity to group if not there
1492 if (P_SENSITIVITY is null) then
1493 GET_GROUP_SENSITIVITY(P_APPID,P_GROUP_NAME,L_SENSITIVITY);
1494 else
1495 l_sensitivity := P_SENSITIVITY;
1496 end if;
1497
1498 IF P_END_DATE IS NOT NULL THEN
1499 --F_END_DATE := to_date(P_END_DATE, JTF_DIAGNOSTIC_ADAPTUTIL.GET_SITE_DATE_FORMAT());
1500 F_END_DATE := P_END_DATE;
1501 END IF;
1502
1503 IF P_META_DATA is not null then
1504 F_META_DATA := XMLTYPE(P_META_DATA);
1505 END IF;
1506
1507 -- if flow of control reaches here, insert the testcase
1508 -- to the group
1509 -- but first find out the highest number of order of tests
1510 -- in the group and add this to the end. If there are no tests
1511 -- then make sure that this gets the first order number
1512
1513 select MAX(ordernumber) into v_ordernumber
1514 from jtf_diagnostic_test where appid = p_appid
1515 and groupname = p_group_name;
1516
1517 if sql%notfound or v_ordernumber = 0 then
1518 v_ordernumber := 1;
1519 else v_ordernumber := v_ordernumber + 1;
1520 end if;
1521
1522 insert into jtf_diagnostic_test(
1523 SEQUENCE, GROUPNAME, APPID,
1524 ORDERNUMBER, TESTTYPE, TESTCLASSNAME,
1525 TOTALARGUMENTROWS,
1526 SENSITIVITY,
1527 OBJECT_VERSION_NUMBER, CREATED_BY,
1528 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
1529 CREATION_DATE,VALID_APPLICATIONS,END_DATE,TEST_METADATA)
1530 values(
1531 JTF_DIAGNOSTIC_TEST_S.NEXTVAL, p_group_name, p_appid,
1532 decode(v_ordernumber, null, 1, v_ordernumber),
1533 p_test_type, p_testclassname,
1534 0, l_sensitivity, 1, p_lubid,
1535 SYSDATE, p_lubid, NULL,
1536 SYSDATE,xmltype(p_valid_apps_xml),F_END_DATE,F_META_DATA);
1537
1538 -- commit;
1539
1540 END INSERT_TESTCASE;
1541
1542 -- deprecated, please use procedure above
1543 procedure INSERT_TESTCASE(p_testclassname in varchar2,
1544 p_group_name in varchar2,
1545 p_appid in varchar2,
1546 p_test_type in varchar2,
1547 p_lubid in number) IS
1548
1549 BEGIN
1550
1551 INSERT_TESTCASE(p_testclassname,
1552 p_group_name,
1553 p_appid,
1554 p_test_type,
1555 null,null,null,null,
1556 p_lubid);
1557
1558 END INSERT_TESTCASE;
1559
1560 -- deprecated, please use procedure above
1561 procedure INSERT_TESTCASE(p_testclassname in varchar2,
1562 p_group_name in varchar2,
1563 p_appid in varchar2,
1564 p_test_type in varchar2) IS
1565
1566 BEGIN
1567
1568 INSERT_TESTCASE(p_testclassname,
1569 p_group_name,
1570 p_appid,
1571 p_test_type,
1572 UID);
1573
1574 END INSERT_TESTCASE;
1575
1576 -- ----------------------------------------------------------------------
1577 -- Insert argument values for a testcase but one row only
1578 -- ----------------------------------------------------------------------
1579
1580
1581 procedure INSERT_ARGVALUE_ROW(p_appid in varchar2,
1582 p_group_name in varchar2,
1583 p_test_class_name in varchar2,
1584 p_arg_names in jtf_varchar2_table_4000,
1585 p_arg_values in jtf_varchar2_table_4000,
1586 p_lubid in number) IS
1587
1588 V_SOURCETYPE BINARY_INTEGER := 1;
1589 v_rownumber jtf_diagnostic_arg.rownumber%TYPE;
1590 v_valsetnumber jtf_diagnostic_arg.valuesetnumber%TYPE;
1591
1592 BEGIN
1593 -- first check if the application is valid
1594
1595 v_sourcetype := 1;
1596 CHECK_APP_OR_GROUP_VALIDITY(P_APPID, P_APPID, V_SOURCETYPE);
1597
1598 -- check for groupname validity
1599
1600 v_sourcetype := 2;
1601 CHECK_APP_OR_GROUP_VALIDITY(P_group_name, P_APPID, V_SOURCETYPE);
1602
1603 -- then check if the testclassname is valid
1604
1605 select count(*) into v_sourcetype
1606 from jtf_diagnostic_test where appid = p_appid
1607 and groupname = p_group_name
1608 and testclassname = p_test_class_name
1609 and rownum <= 1;
1610
1611 -- making sure that the error only gets thrown incase
1612 -- its not a valid test name and also not a declarative
1613 -- test that contains a step that is a diagnostic test
1614
1615 if v_sourcetype <> 1 and instr(p_test_class_name, '{-STEP/CLASS-}') <= 0 then
1616 raise_application_error(-20000, 'Invalid testclassname received:' || p_test_class_name);
1617 end if;
1618
1619 -- then get the max row number and increment it by 1
1620 -- max row number is for this testclassname only
1621
1622 select max(rownumber) into v_rownumber
1623 from jtf_diagnostic_arg where testclassname = p_test_class_name
1624 and groupname = p_group_name and appid = p_appid;
1625
1626 if sql%notfound or v_rownumber = 0 then
1627 v_rownumber := 1;
1628 else v_rownumber := v_rownumber + 1;
1629 end if;
1630
1631 -- now get the max valuesetnumber and increment it by 1
1632
1633 select JTF_DIAGNOSTIC_ARG_VAL_SET_S.nextval
1634 into v_valsetnumber from dual;
1635
1636 -- select max(valuesetnumber) into v_valsetnumber
1637 -- from jtf_diagnostic_arg;
1638
1639 -- if sql%notfound or v_valsetnumber = 0 then
1640 -- v_valsetnumber := 1;
1641 -- else v_valsetnumber := v_valsetnumber + 1;
1642 -- end if;
1643
1644 -- insert the name-value pair one by one
1645 V_SOURCETYPE := 1;
1646 loop
1647 if p_arg_names.exists(v_sourcetype) then
1648
1649 insert into jtf_diagnostic_arg(
1650 SEQUENCE, TESTCLASSNAME, GROUPNAME,
1651 APPID, ARGNAME, ARGVALUE,
1652 ROWNUMBER, VALUESETNUMBER, OBJECT_VERSION_NUMBER,
1653 CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
1654 LAST_UPDATE_LOGIN, CREATION_DATE)
1655 values(
1656 JTF_DIAGNOSTIC_ARG_S.NEXTVAL, p_test_class_name, p_group_name,
1657 p_appid, p_arg_names(v_sourcetype), p_arg_values(v_sourcetype),
1658 decode(v_rownumber, null, 1, v_rownumber), v_valsetnumber, 1,
1659 p_lubid, SYSDATE, p_lubid,
1660 NULL, SYSDATE);
1661
1662 v_sourcetype := v_sourcetype + 1;
1663 else
1664 exit;
1665 end if;
1666 end loop;
1667
1668 -- commit;
1669
1670 END INSERT_ARGVALUE_ROW;
1671
1672 procedure INSERT_ARGVALUE_ROW(p_appid in varchar2,
1673 p_group_name in varchar2,
1674 p_test_class_name in varchar2,
1675 p_arg_names in jtf_varchar2_table_4000,
1676 p_arg_values in jtf_varchar2_table_4000
1677 ) IS
1678
1679 BEGIN
1680
1681 INSERT_ARGVALUE_ROW(P_APPID,
1682 P_GROUP_NAME,
1683 P_TEST_CLASS_NAME,
1684 P_ARG_NAMES,
1685 P_ARG_VALUES,
1686 UID);
1687
1688 END INSERT_ARGVALUE_ROW;
1689
1690 --mls
1691 procedure INSERT_ARGVALUE_ROW(p_appid in varchar2,
1692 p_group_name in varchar2,
1693 p_test_class_name in varchar2,
1694 p_arg_names in jtf_varchar2_table_4000,
1695 p_arg_values in jtf_varchar2_table_4000,
1696 p_arg_dates in jtf_date_table,
1697 p_arg_numbers in jtf_number_table,
1698 p_lubid in number) IS
1699
1700 V_SOURCETYPE BINARY_INTEGER := 1;
1701 v_rownumber jtf_diagnostic_arg.rownumber%TYPE;
1702 v_valsetnumber jtf_diagnostic_arg.valuesetnumber%TYPE;
1703
1704 BEGIN
1705 -- first check if the application is valid
1706
1707 v_sourcetype := 1;
1708 CHECK_APP_OR_GROUP_VALIDITY(P_APPID, P_APPID, V_SOURCETYPE);
1709
1710 -- check for groupname validity
1711
1712 v_sourcetype := 2;
1713 CHECK_APP_OR_GROUP_VALIDITY(P_group_name, P_APPID, V_SOURCETYPE);
1714
1715 -- then check if the testclassname is valid
1716
1717 select count(*) into v_sourcetype
1718 from jtf_diagnostic_test where appid = p_appid
1719 and groupname = p_group_name
1720 and testclassname = p_test_class_name
1721 and rownum <= 1;
1722
1723 -- making sure that the error only gets thrown incase
1724 -- its not a valid test name and also not a declarative
1725 -- test that contains a step that is a diagnostic test
1726
1727 if v_sourcetype <> 1 and instr(p_test_class_name, '{-STEP/CLASS-}') <= 0 then
1728 raise_application_error(-20000, 'Invalid testclassname received:' || p_test_class_name);
1729 end if;
1730
1731 -- then get the max row number and increment it by 1
1732 -- max row number is for this testclassname only
1733
1734 select max(rownumber) into v_rownumber
1735 from jtf_diagnostic_arg where testclassname = p_test_class_name
1736 and groupname = p_group_name and appid = p_appid;
1737
1738 if sql%notfound or v_rownumber = 0 then
1739 v_rownumber := 1;
1740 else v_rownumber := v_rownumber + 1;
1741 end if;
1742
1743 -- now get the max valuesetnumber and increment it by 1
1744
1745 select JTF_DIAGNOSTIC_ARG_VAL_SET_S.nextval
1746 into v_valsetnumber from dual;
1747
1748 -- select max(valuesetnumber) into v_valsetnumber
1749 -- from jtf_diagnostic_arg;
1750
1751 -- if sql%notfound or v_valsetnumber = 0 then
1752 -- v_valsetnumber := 1;
1753 -- else v_valsetnumber := v_valsetnumber + 1;
1754 -- end if;
1755
1756 -- insert the name-value pair one by one
1757 V_SOURCETYPE := 1;
1758 loop
1759 if p_arg_names.exists(v_sourcetype) then
1760
1761 insert into jtf_diagnostic_arg(
1762 SEQUENCE, TESTCLASSNAME, GROUPNAME,
1763 APPID, ARGNAME, ARGVALUE,
1764 ARG_DATE_VALUE, ARG_NUMBER_VALUE,
1765 ROWNUMBER, VALUESETNUMBER, OBJECT_VERSION_NUMBER,
1766 CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
1767 LAST_UPDATE_LOGIN, CREATION_DATE)
1768 values(
1769 JTF_DIAGNOSTIC_ARG_S.NEXTVAL, p_test_class_name, p_group_name,
1770 p_appid, p_arg_names(v_sourcetype), p_arg_values(v_sourcetype),
1771 p_arg_dates(v_sourcetype), p_arg_numbers(v_sourcetype),
1772 decode(v_rownumber, null, 1, v_rownumber), v_valsetnumber, 1,
1773 p_lubid, SYSDATE, p_lubid,
1774 NULL, SYSDATE);
1775
1776 v_sourcetype := v_sourcetype + 1;
1777 else
1778 exit;
1779 end if;
1780 end loop;
1781
1782 -- commit;
1783
1784 END INSERT_ARGVALUE_ROW;
1785
1786 --mls
1787
1788 ---------------------------------------------------------------------------
1789 -- Checks if a group or application is valid. If application, it should
1790 -- be registered with the diagnostic framework. If group then it should be
1791 -- registered within the application
1792 ---------------------------------------------------------------------------
1793
1794 procedure CHECK_APP_OR_GROUP_VALIDITY(
1795 P_SOURCEID IN VARCHAR2,
1796 P_SOURCEAPPID IN VARCHAR2,
1797 P_SOURCETYPE IN NUMBER) IS
1798
1799 v_data_found BINARY_INTEGER := 0;
1800
1801 BEGIN
1802
1803 -- if application, then is the application
1804 -- registered in the jtf_diagnostic_app table
1805 -- if group, then is the group part of the application
1806
1807 if p_sourcetype = 1 then
1808 select count(*) into v_data_found from jtf_diagnostic_app
1809 where appid = p_sourceid;
1810 elsif p_sourcetype = 2 then
1811 select count(*) into v_data_found from jtf_diagnostic_group
1812 where appid = p_sourceappid and
1813 groupname = p_sourceid;
1814 else
1815 raise_application_error(-20000, 'Invalid data type received');
1816 end if;
1817
1818 if v_data_found = 0 then
1819 raise_application_error(-20000,
1820 'Could not find the group or application as registered');
1821 end if;
1822
1823 END CHECK_APP_OR_GROUP_VALIDITY;
1824
1825
1826 ---------------------------------------------------------------------------
1827 -- Inserts array of applications or groups into the database but makes
1828 -- sure that the application or group does not prereq itself and is
1829 -- registered (application with the framework and group with the application)
1830 ---------------------------------------------------------------------------
1831
1832 procedure PREREQ_INSERTION(
1833 P_SOURCEID IN VARCHAR2,
1834 P_SOURCEAPPID IN VARCHAR2,
1835 P_PREREQID IN JTF_VARCHAR2_TABLE_4000,
1836 P_SOURCETYPE IN NUMBER,
1837 P_LUBID IN NUMBER) IS
1838
1839 v_index BINARY_INTEGER := 1;
1840 v_data_found BINARY_INTEGER := 1;
1841
1842 BEGIN
1843
1844 if P_SOURCEID = 'SYSTEM_TESTS' then
1845 raise_application_error(-20000, 'HTML Platform cannot have any prereqs');
1846 end if;
1847
1848 LOOP
1849 IF P_PREREQID.EXISTS(v_index) THEN
1850
1851 -- a group or application cannot prereq itself
1852 -- the following checks for that
1853
1854 if P_SOURCEID = P_PREREQID(v_index) then
1855 raise_application_error(-20000, 'Entity Cant prereq itself');
1856 end if;
1857
1858 -- the following checks if the data to be inserted
1859 -- is a valid group in the same application or
1860 -- a valid application registered in the diagnostic
1861 -- framework
1862
1863 if P_SOURCETYPE = 1 then
1864 select sequence into v_data_found from jtf_diagnostic_app
1865 where appid = P_PREREQID(v_index)
1866 and rownum <= 1;
1867 elsif p_sourcetype = 2 then
1868 select sequence into v_data_found from jtf_diagnostic_group
1869 where groupname = P_PREREQID(v_index)
1870 and appid = p_sourceappid
1871 and rownum <= 1;
1872
1873 end if;
1874
1875 IF SQL%NOTFOUND THEN
1876 RAISE_APPLICATION_ERROR(-20000,
1877 'Group / Application supplied as prereq is not valid');
1878 END IF;
1879
1880
1881 -- if reached this far, great. the record is valid and
1882 -- we can insert the record in the table
1883 -- need to complete the insert statement
1884
1885 insert into jtf_diagnostic_prereq
1886 (SEQUENCE, SOURCEID, PREREQID,
1887 SOURCEAPPID, TYPE, OBJECT_VERSION_NUMBER,
1888 CREATED_BY, LAST_UPDATE_DATE,
1889 LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE)
1890 values
1891 (JTF_DIAGNOSTIC_PREREQ_S.NEXTVAL, P_SOURCEID,
1892 P_PREREQID(v_index), P_SOURCEAPPID, P_SOURCETYPE,
1893 1, P_LUBID, SYSDATE, P_LUBID,
1894 P_LUBID, SYSDATE);
1895
1896 --increment the counter
1897
1898 v_index := v_index + 1;
1899 ELSE
1900 EXIT;
1901 END IF;
1902 END LOOP;
1903
1904 -- commit;
1905
1906 END PREREQ_INSERTION;
1907
1908 -- deprecated, please use procedure above
1909 procedure PREREQ_INSERTION(
1910 P_SOURCEID IN VARCHAR2,
1911 P_SOURCEAPPID IN VARCHAR2,
1912 P_PREREQID IN JTF_VARCHAR2_TABLE_4000,
1913 P_SOURCETYPE IN NUMBER) IS
1914
1915 BEGIN
1916
1917 PREREQ_INSERTION(P_SOURCEID,
1918 P_SOURCEAPPID,
1919 P_PREREQID,
1920 P_SOURCETYPE,
1921 UID);
1922
1923 END PREREQ_INSERTION;
1924
1925 ---------------------------------------------------------------------------
1926 -- Rename a group within an application. This procedure makes sure that the
1927 -- new group name does not clash with another name in the same application
1928 ---------------------------------------------------------------------------
1929
1930 procedure RENAME_GROUP(
1931 P_APPID IN VARCHAR2,
1932 P_GROUPNAME IN VARCHAR2,
1933 P_NEWGROUPNAME IN VARCHAR2,
1934 P_LUBID IN NUMBER) IS
1935
1936 v_data_found BINARY_INTEGER := 0;
1937
1938 BEGIN
1939
1940 select count(*) into v_data_found
1941 from jtf_diagnostic_group
1942 where appid = p_appid
1943 and groupname = p_groupname;
1944
1945 if v_data_found = 0 or sql%notfound then
1946 raise_application_error(-20000, 'Invalid current group name provided');
1947 end if;
1948
1949 -- proceed only if the old and the new
1950 -- group names are not the same
1951
1952 if p_groupname <> p_newgroupname then
1953
1954 -- if flow of control reaches here there is a valid
1955 -- group. Now make sure there already isnt a group existing in the
1956 -- application. Should not be.
1957
1958 -- put an invalid value into
1959 -- v_data_found
1960
1961 v_data_found := 1;
1962
1963 select count(*) into v_data_found
1964 from jtf_diagnostic_group
1965 where appid = p_appid
1966 and groupname = p_newgroupname;
1967
1968 if v_data_found > 0 then
1969 raise_application_error(-20000,
1970 'New group name invalid -- name already taken');
1971 end if;
1972
1973
1974 -- if flow of control reaches here, it is alright to
1975 -- rename the group across jtf_diagnostic_group,
1976 -- jtf_diagnostic_test, jtf_diagnostic_arg, jtf_diagnostic_prereq
1977
1978
1979 update jtf_diagnostic_group
1980 set groupname = p_newgroupname,
1981 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
1982 LAST_UPDATE_DATE = SYSDATE,
1983 LAST_UPDATED_BY = P_LUBID
1984 where groupname = p_groupname
1985 and appid = p_appid;
1986
1987 update jtf_diagnostic_test
1988 set groupname = p_newgroupname,
1989 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
1990 LAST_UPDATE_DATE = SYSDATE,
1991 LAST_UPDATED_BY = P_LUBID
1992 where groupname = p_groupname
1993 and appid = p_appid;
1994
1995 update jtf_diagnostic_arg
1996 set groupname = p_newgroupname,
1997 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
1998 LAST_UPDATE_DATE = SYSDATE,
1999 LAST_UPDATED_BY = P_LUBID
2000 where groupname = p_groupname
2001 and appid = p_appid;
2002
2003 -- rename the sourceid and
2004 -- the prereqid
2005
2006 update jtf_diagnostic_prereq
2007 set sourceid = p_newgroupname,
2008 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
2009 LAST_UPDATE_DATE = SYSDATE,
2010 LAST_UPDATED_BY = P_LUBID
2011 where sourceid = p_groupname
2012 and sourceappid = p_appid;
2013
2014 update jtf_diagnostic_prereq
2015 set prereqid = p_newgroupname,
2016 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
2017 LAST_UPDATE_DATE = SYSDATE,
2018 LAST_UPDATED_BY = P_LUBID
2019 where prereqid = p_groupname
2020 and sourceappid = p_appid;
2021
2022 update jtf_diagnostic_decl_test_steps
2023 set groupname = p_newgroupname,
2024 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
2025 LAST_UPDATE_DATE = SYSDATE,
2026 LAST_UPDATED_BY = P_LUBID
2027 where groupname = p_groupname
2028 and appid = p_appid;
2029
2030 update jtf_diagnostic_decl_step_cols
2031 set groupname = p_newgroupname,
2032 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
2033 LAST_UPDATE_DATE = SYSDATE,
2034 LAST_UPDATED_BY = P_LUBID
2035 where groupname = p_groupname
2036 and appid = p_appid;
2037
2038 end if;
2039
2040
2041 END RENAME_GROUP;
2042
2043 -- deprecated, please use procedure above
2044 procedure RENAME_GROUP(
2045 P_APPID IN VARCHAR2,
2046 P_GROUPNAME IN VARCHAR2,
2047 P_NEWGROUPNAME IN VARCHAR2) IS
2048
2049 BEGIN
2050
2051 RENAME_GROUP(P_APPID,
2052 P_GROUPNAME,
2053 P_NEWGROUPNAME,
2054 UID);
2055
2056 END RENAME_GROUP;
2057
2058 ---------------------------------------------------------------------------
2059 -- Upload an application row from the ldt file
2060 ---------------------------------------------------------------------------
2061
2062 PROCEDURE LOAD_ROW_APP(
2063 P_APPID IN VARCHAR2,
2064 P_LUDATE IN VARCHAR2,
2065 P_SEC_GRP_ID IN VARCHAR2,
2066 P_CUST_MODE IN VARCHAR2,
2067 P_OWNER IN VARCHAR2) IS
2068
2069 f_luby number; -- entity owner in file
2070 f_ludate date; -- entity update date in file
2071 db_luby number; -- entity owner in db
2072 db_ludate date; -- entity update date in db
2073
2074 BEGIN
2075
2076 -- Translate owner to file_last_updated_by
2077 -- 5953806 - replaced to follow FNDLOAD standards
2078 /*if (P_OWNER = 'SEED') then
2079 f_luby := 1;
2080 else
2081 f_luby := 0;
2082 end if;*/
2083
2084 f_luby := fnd_load_util.owner_id(P_OWNER);
2085
2086 -- Translate char last_update_date to date
2087 f_ludate := nvl(to_date(p_ludate, 'YYYY/MM/DD'), sysdate);
2088
2089 begin
2090 select LAST_UPDATED_BY, LAST_UPDATE_DATE
2091 into db_luby, db_ludate
2092 from jtf_diagnostic_app
2093 where appid = p_appid;
2094
2095 -- Update record only as per standard
2096
2097 -- 5953806 - replaced to if statement to follow FNDLOAD standards
2098 /*if ((p_cust_mode = 'FORCE') or
2099 ((f_luby = 0) and (db_luby = 1)) or
2100 ((f_luby = db_luby) and (f_ludate > db_ludate)))*/
2101 /* if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,db_ludate,
2102 p_cust_mode))
2103 then*/
2104 -- seed data must not be changed by customers.Hence overwriting data always
2105 -- so that it covers up any changes by mistake
2106 update jtf_diagnostic_app
2107 set last_updated_by = f_luby,
2108 last_update_date = f_ludate,
2109 object_version_number = object_version_number + 1,
2110 security_group_id = to_number(P_SEC_GRP_ID)
2111 where appid = p_appid;
2112
2113 --end if;
2114
2115 exception
2116
2117 when no_data_found then
2118 -- Record doesn't exist - insert in all cases
2119
2120 insert into jtf_diagnostic_app(
2121 sequence,
2122 appid,
2123 object_version_number,
2124 created_by,
2125 last_update_date,
2126 last_updated_by,
2127 last_update_login,
2128 creation_date,
2129 security_group_id)
2130 values(
2131 jtf_diagnostic_app_s.nextval,
2132 p_appid,
2133 1,
2134 f_luby,
2135 f_ludate,
2136 f_luby,
2137 null,
2138 f_ludate,
2139 to_number(P_SEC_GRP_ID));
2140
2141 end;
2142
2143 END LOAD_ROW_APP;
2144
2145
2146 ---------------------------------------------------------------------------
2147 -- Upload an application group row from the ldt file
2148 ---------------------------------------------------------------------------
2149
2150 PROCEDURE LOAD_ROW_GROUP(
2151 P_APPID IN VARCHAR2,
2152 P_GROUPNAME IN VARCHAR2,
2153 P_SENSITIVITY IN VARCHAR2,
2154 P_LUDATE IN VARCHAR2,
2155 P_SEC_GRP_ID IN VARCHAR2,
2156 P_CUST_MODE IN VARCHAR2,
2157 P_OWNER IN VARCHAR2) IS
2158
2159 f_luby number; -- entity owner in file
2160 f_ludate date; -- entity update date in file
2161 db_luby number; -- entity owner in db
2162 db_ludate date; -- entity update date in db
2163 v_num number; -- temporary variable
2164 v_sensitivity number; -- temp variable for sensitivity
2165
2166 BEGIN
2167
2168 v_sensitivity := to_number(nvl(P_SENSITIVITY, '1'));
2169
2170 -- Translate owner to file_last_updated_by
2171
2172 -- 5953806 - replaced to follow FNDLOAD standards
2173 /*if (P_OWNER = 'SEED') then
2174 f_luby := 1;
2175 else
2176 f_luby := 0;
2177 end if;*/
2178
2179 f_luby := fnd_load_util.owner_id(P_OWNER);
2180
2181 -- Translate char last_update_date to date
2182 f_ludate := nvl(to_date(p_ludate, 'YYYY/MM/DD'), sysdate);
2183
2184 begin
2185 select LAST_UPDATED_BY, LAST_UPDATE_DATE
2186 into db_luby, db_ludate
2187 from jtf_diagnostic_group
2188 where appid = p_appid and
2189 groupname = p_groupname;
2190
2191 -- Update record only as per standard
2192
2193 -- 5953806 - replaced to if statement to follow FNDLOAD standards
2194 /*if ((p_cust_mode = 'FORCE') or
2195 ((f_luby = 0) and (db_luby = 1)) or
2196 ((f_luby = db_luby) and (f_ludate > db_ludate)))*/
2197 /*if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,db_ludate,
2198 p_cust_mode))
2199 then*/
2200 -- seed data must not be changed by customers.Hence overwriting data always
2201 -- so that it covers up any changes by mistake
2202 update jtf_diagnostic_group
2203 set sensitivity = v_sensitivity,
2204 last_updated_by = f_luby,
2205 last_update_date = f_ludate,
2206 object_version_number = object_version_number + 1,
2207 security_group_id = to_number(P_SEC_GRP_ID)
2208 where appid = p_appid and groupname = p_groupname;
2209
2210 --end if;
2211
2212 exception
2213
2214 when no_data_found then
2215 -- Record doesn't exist - insert in all cases
2216
2217 select nvl(max(ordernumber)+1,1) into v_num
2218 from jtf_diagnostic_group where
2219 appid = p_appid;
2220
2221 insert into jtf_diagnostic_group(
2222 sequence,
2223 groupname,
2224 appid,
2225 sensitivity,
2226 ordernumber,
2227 object_version_number,
2228 created_by,
2229 last_update_date,
2230 last_update_login,
2231 last_updated_by,
2232 creation_date,
2233 security_group_id)
2234 values(
2235 jtf_diagnostic_group_s.nextval,
2236 p_groupname,
2237 p_appid,
2238 v_sensitivity,
2239 v_num,
2240 1,
2241 f_luby,
2242 f_ludate,
2243 null,
2244 f_luby,
2245 f_ludate,
2246 to_number(P_SEC_GRP_ID));
2247
2248 end;
2249
2250 END LOAD_ROW_GROUP;
2251
2252
2253 ---------------------------------------------------------------------------
2254 -- Upload an application group test row from the ldt file
2255 ---------------------------------------------------------------------------
2256
2257 PROCEDURE LOAD_ROW_TEST(
2258 P_APPID IN VARCHAR2,
2259 P_GROUPNAME IN VARCHAR2,
2260 P_TESTCLASSNAME IN VARCHAR2,
2261 P_TESTTYPE IN VARCHAR2,
2262 P_TOTALARGUMENTROWS IN VARCHAR2,
2263 P_SENSITIVITY IN VARCHAR2,
2264 P_LUDATE IN VARCHAR2,
2265 P_SEC_GRP_ID IN VARCHAR2,
2266 P_CUST_MODE IN VARCHAR2,
2267 P_VALID_APPLICATIONS IN CLOB,
2268 P_END_DATE IN VARCHAR2,
2269 P_META_DATA IN VARCHAR2,
2270 P_OWNER IN VARCHAR2) IS
2271
2272 f_luby number; -- entity owner in file
2273 f_ludate date; -- entity update date in file
2274 db_luby number; -- entity owner in db
2275 db_ludate date; -- entity update date in db
2276 v_num number; -- temporary variable
2277 v_product varchar2(50);
2278 v_filename varchar2(500);
2279 c_product varchar2(50);
2280 c_testclassname varchar2(1500);
2281 DOINSERT boolean;
2282 l_sensitivity number;
2283 f_end_date date;
2284 f_meta_data xmltype;
2285
2286 cursor C_GET_PROD_NAMES (l_appid varchar2,
2287 l_groupname varchar2,
2288 l_filename varchar2,
2289 l_testclassname varchar2) is
2290 select substr(TESTCLASSNAME,
2291 instr(TESTCLASSNAME,'.',1,2)+1,
2292 instr(TESTCLASSNAME,'.',1,3) -
2293 instr(TESTCLASSNAME,'.',1,2) - 1),
2294 testclassname
2295 from JTF_DIAGNOSTIC_TEST
2296 where GROUPNAME = l_groupname
2297 and APPID = l_appid
2298 and testclassname <> l_testclassname
2299 and substr(TESTCLASSNAME,
2300 instr(TESTCLASSNAME,'.',-1,1)+1)
2301 = l_filename;
2302
2303 BEGIN
2304
2305 -- Translate owner to file_last_updated_by
2306
2307 -- 5953806 - replaced to follow FNDLOAD standards
2308 /*if (P_OWNER = 'SEED') then
2309 f_luby := 1;
2310 else
2311 f_luby := 0;
2312 end if;*/
2313
2314 f_luby := fnd_load_util.owner_id(P_OWNER);
2315
2316 -- Translate char last_update_date to date
2317 f_ludate := nvl(to_date(p_ludate, 'YYYY/MM/DD'), sysdate);
2318
2319 if p_end_date is not null then
2320 f_end_date := to_date(p_end_date, 'YYYY/MM/DD');
2321 end if;
2322 IF P_META_DATA is not null then
2323 F_META_DATA := XMLTYPE(P_META_DATA);
2324 END IF;
2325
2326 if (p_sensitivity is null) then
2327 GET_GROUP_SENSITIVITY(p_appid,p_groupname,
2328 l_sensitivity);
2329 else
2330 l_sensitivity := p_sensitivity;
2331 end if;
2332
2333 begin
2334 select LAST_UPDATED_BY, LAST_UPDATE_DATE
2335 into db_luby, db_ludate
2336 from jtf_diagnostic_test
2337 where appid = p_appid and
2338 groupname = p_groupname
2339 and testclassname = p_testclassname;
2340
2341 -- Update record only as per standard
2342
2343 -- 5953806 - replaced to if statement to follow FNDLOAD standards
2344 /*if ((p_cust_mode = 'FORCE') or
2345 ((f_luby = 0) and (db_luby = 1)) or
2346 ((f_luby = db_luby) and (f_ludate > db_ludate)))*/
2347 /*if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,db_ludate,
2348 p_cust_mode))
2349 then*/
2350 -- seed data must not be changed by customers.Hence overwriting data always
2351 -- so that it covers up any changes by mistake
2352
2353 -- if valid_applications is not null, insert valid_applications in to table
2354 -- else ignore the valid_applications while inserting
2355 if P_VALID_APPLICATIONS is not null and P_VALID_APPLICATIONS <> empty_clob() then
2356 update jtf_diagnostic_test
2357 set last_updated_by = f_luby,
2358 last_update_date = sysdate,--f_ludate,
2359 object_version_number = object_version_number + 1,
2360 TOTALARGUMENTROWS = p_TOTALARGUMENTROWS,
2361 TESTTYPE = p_TESTTYPE,
2362 sensitivity = l_sensitivity,
2363 security_group_id = to_number(P_SEC_GRP_ID),
2364 valid_applications = xmltype(P_VALID_APPLICATIONS),
2365 end_date = f_end_date,
2366 test_metadata = f_meta_data
2367 where appid = p_appid and groupname = p_groupname
2368 and testclassname = p_testclassname;
2369 else
2370
2371 update jtf_diagnostic_test
2372 set last_updated_by = f_luby,
2373 last_update_date = sysdate,--f_ludate,
2374 object_version_number = object_version_number + 1,
2375 TOTALARGUMENTROWS = p_TOTALARGUMENTROWS,
2376 TESTTYPE = p_TESTTYPE,
2377 sensitivity = l_sensitivity,
2378 security_group_id = to_number(P_SEC_GRP_ID),
2379 end_date = f_end_date,
2380 test_metadata = f_meta_data
2381 where appid = p_appid and groupname = p_groupname
2382 and testclassname = p_testclassname;
2383
2384 end if;
2385 -- end if
2386
2387 exception
2388
2389 when no_data_found then
2390 -- Record doesn't exist
2391
2392 DOINSERT := TRUE;
2393
2394 -- For java test make sure izu test doesn't also
2395 -- exist and if it does delete it, if we are
2396 -- uploading izu java test and test exists in
2397 -- another product don't upload.
2398 if (instr(P_TESTTYPE,5) <> 0) then
2399 PARSE_TESTCLASSNAME(P_TESTCLASSNAME,
2400 V_PRODUCT,
2401 V_FILENAME);
2402 open C_GET_PROD_NAMES(p_appid,
2403 p_groupname,
2404 V_FILENAME,
2405 P_TESTCLASSNAME);
2406 loop
2407 fetch C_GET_PROD_NAMES into c_product,c_testclassname;
2408 exit when C_GET_PROD_NAMES%notfound;
2409 if c_product = 'izu' then
2410 DELETE_TEST(p_appid,
2411 p_groupname,
2412 c_testclassname);
2413 elsif V_PRODUCT = 'izu' and
2414 c_product <> 'izu' then
2415 DOINSERT := FALSE;
2416 end if;
2417 end loop;
2418 close C_GET_PROD_NAMES;
2419 end if;
2420
2421 if DOINSERT then
2422 --6599133
2423 select MAX(ordernumber) into v_num
2424 from jtf_diagnostic_test where appid = p_appid
2425 and groupname = p_groupname;
2426
2427 if sql%notfound or v_num = 0 or v_num is null then
2428 v_num := 1;
2429 else v_num := v_num + 1;
2430 end if;
2431
2432 -- if valid_applications is not null, insert valid_applications in to table
2433 -- else ignore the valid_applications while inserting
2434 if P_VALID_APPLICATIONS is not null and P_VALID_APPLICATIONS <> empty_clob() then
2435 insert into jtf_diagnostic_test(
2436 SEQUENCE,
2437 GROUPNAME,
2438 APPID,
2439 ORDERNUMBER,
2440 TESTTYPE,
2441 TESTCLASSNAME,
2442 TOTALARGUMENTROWS,
2443 SENSITIVITY,
2444 OBJECT_VERSION_NUMBER,
2445 CREATED_BY,
2446 LAST_UPDATE_DATE,
2447 LAST_UPDATED_BY,
2448 LAST_UPDATE_LOGIN,
2449 CREATION_DATE,
2450 security_group_id,
2451 valid_applications,
2452 end_date,
2453 test_metadata)
2454 values(
2455 jtf_diagnostic_test_s.nextval,
2456 p_groupname,
2457 p_appid,
2458 v_num,
2459 p_testtype,
2460 p_testclassname,
2461 p_totalargumentrows,
2462 l_sensitivity,
2463 1,
2464 f_luby,
2465 f_ludate,
2466 f_luby,
2467 null,
2468 f_ludate,
2469 to_number(P_SEC_GRP_ID),
2470 xmltype(P_VALID_APPLICATIONS),
2471 f_end_date,
2472 f_meta_data);
2473 else
2474 insert into jtf_diagnostic_test(
2475 SEQUENCE,
2476 GROUPNAME,
2477 APPID,
2478 ORDERNUMBER,
2479 TESTTYPE,
2480 TESTCLASSNAME,
2481 TOTALARGUMENTROWS,
2482 SENSITIVITY,
2483 OBJECT_VERSION_NUMBER,
2484 CREATED_BY,
2485 LAST_UPDATE_DATE,
2486 LAST_UPDATED_BY,
2487 LAST_UPDATE_LOGIN,
2488 CREATION_DATE,
2489 security_group_id,
2490 end_date,
2491 test_metadata)
2492 values(
2493 jtf_diagnostic_test_s.nextval,
2494 p_groupname,
2495 p_appid,
2496 v_num,
2497 p_testtype,
2498 p_testclassname,
2499 p_totalargumentrows,
2500 l_sensitivity,
2501 1,
2502 f_luby,
2503 f_ludate,
2504 f_luby,
2505 null,
2506 f_ludate,
2507 to_number(P_SEC_GRP_ID),
2508 f_end_date,
2509 f_meta_data);
2510 end if;
2511 end if;
2512 end;
2513
2514 END LOAD_ROW_TEST;
2515
2516
2517 ---------------------------------------------------------------------------
2518 -- Upload arguments of a testcase from the ldt file
2519 ---------------------------------------------------------------------------
2520
2521 PROCEDURE LOAD_ROW_ARG(
2522 P_APPID IN VARCHAR2,
2523 P_GROUPNAME IN VARCHAR2,
2524 P_TESTCLASSNAME IN VARCHAR2,
2525 P_ARGNAME IN VARCHAR2,
2526 P_ROWNUMBER IN VARCHAR2,
2527 P_ARGVALUE IN VARCHAR2,
2528 P_VALUESETNUMBER IN VARCHAR2,
2529 P_LUDATE IN VARCHAR2,
2530 P_SEC_GRP_ID IN VARCHAR2,
2531 P_CUST_MODE IN VARCHAR2,
2532 P_OWNER IN VARCHAR2) IS
2533
2534 f_luby number; -- entity owner in file
2535 f_ludate date; -- entity update date in file
2536 db_luby number; -- entity owner in db
2537 db_ludate date; -- entity update date in db
2538 v_num number; -- temporary variable
2539
2540 BEGIN
2541
2542 -- Translate owner to file_last_updated_by
2543
2544 -- 5953806 - replaced to follow FNDLOAD standards
2545 /*if (P_OWNER = 'SEED') then
2546 f_luby := 1;
2547 else
2548 f_luby := 0;
2549 end if;*/
2550
2551 f_luby := fnd_load_util.owner_id(P_OWNER);
2552
2553 -- Translate char last_update_date to date
2554 f_ludate := nvl(to_date(p_ludate, 'YYYY/MM/DD'), sysdate);
2555
2556 begin
2557 select LAST_UPDATED_BY, LAST_UPDATE_DATE
2558 into db_luby, db_ludate
2559 from jtf_diagnostic_arg
2560 where appid = p_appid
2561 and groupname = p_groupname
2562 and testclassname = p_testclassname
2563 and argname = p_argname
2564 and rownumber = p_rownumber;
2565
2566 -- Update record only as per standard
2567
2568 -- 5953806 - replaced to if statement to follow FNDLOAD standards
2569 /*if ((p_cust_mode = 'FORCE') or
2570 ((f_luby = 0) and (db_luby = 1)) or
2571 ((f_luby = db_luby) and (f_ludate > db_ludate)))*/
2572 /*if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,db_ludate,
2573 p_cust_mode))
2574 then*/
2575 -- seed data must not be changed by customers.Hence overwriting data always
2576 -- so that it covers up any changes by mistake
2577 update jtf_diagnostic_arg
2578 set last_updated_by = f_luby,
2579 argvalue = p_argvalue,
2580 VALUESETNUMBER = p_VALUESETNUMBER,
2581 last_update_date = f_ludate,
2582 object_version_number = object_version_number + 1,
2583 security_group_id = to_number(P_SEC_GRP_ID)
2584 where appid = p_appid
2585 and groupname = p_groupname
2586 and testclassname = p_testclassname
2587 and argname = p_argname
2588 and rownumber = p_rownumber;
2589 --end if;
2590
2591 exception
2592
2593 when no_data_found then
2594 -- Record doesn't exist - insert in all cases
2595
2596 insert into jtf_diagnostic_arg(
2597 SEQUENCE,
2598 TESTCLASSNAME,
2599 GROUPNAME,
2600 APPID,
2601 ARGNAME,
2602 ARGVALUE,
2603 ROWNUMBER,
2604 VALUESETNUMBER,
2605 OBJECT_VERSION_NUMBER,
2606 CREATED_BY,
2607 LAST_UPDATE_DATE,
2608 LAST_UPDATED_BY,
2609 LAST_UPDATE_LOGIN,
2610 CREATION_DATE,
2611 SECURITY_GROUP_ID)
2612 values(
2613 jtf_diagnostic_arg_s.nextval,
2614 p_testclassname,
2615 p_groupname,
2616 p_appid,
2617 p_argname,
2618 p_argvalue,
2619 p_rownumber,
2620 p_valuesetnumber,
2621 1,
2622 f_luby,
2623 f_ludate,
2624 f_luby,
2625 null,
2626 f_ludate,
2627 to_number(P_SEC_GRP_ID));
2628
2629
2630 end;
2631
2632 END LOAD_ROW_ARG;
2633
2634
2635 ---------------------------------------------------------------------------
2636 -- Upload application or group prerequisites from the ldt file
2637 ---------------------------------------------------------------------------
2638
2639 PROCEDURE LOAD_ROW_PREREQ(
2640 P_SOURCEID IN VARCHAR2,
2641 P_PREREQID IN VARCHAR2,
2642 P_SOURCEAPPID IN VARCHAR2,
2643 P_TYPE IN VARCHAR2,
2644 P_LUDATE IN VARCHAR2,
2645 P_SEC_GRP_ID IN VARCHAR2,
2646 P_CUST_MODE IN VARCHAR2,
2647 P_OWNER IN VARCHAR2) IS
2648
2649 f_luby number; -- entity owner in file
2650 f_ludate date; -- entity update date in file
2651 db_luby number; -- entity owner in db
2652 db_ludate date; -- entity update date in db
2653 v_num number; -- temporary variable
2654
2655 BEGIN
2656
2657 -- Translate owner to file_last_updated_by
2658 -- 5953806 - replaced to follow FNDLOAD standards
2659 /*if (P_OWNER = 'SEED') then
2660 f_luby := 1;
2661 else
2662 f_luby := 0;
2663 end if;*/
2664
2665 f_luby := fnd_load_util.owner_id(P_OWNER);
2666
2667 -- Translate char last_update_date to date
2668 f_ludate := nvl(to_date(p_ludate, 'YYYY/MM/DD'), sysdate);
2669
2670 begin
2671 select LAST_UPDATED_BY, LAST_UPDATE_DATE
2672 into db_luby, db_ludate
2673 from jtf_diagnostic_prereq
2674 where sourceid = p_sourceid
2675 and prereqid = p_prereqid
2676 and SOURCEAPPID = p_SOURCEAPPID
2677 and type = p_type;
2678
2679 -- Update record only as per standard
2680
2681 -- 5953806 - replaced to if statement to follow FNDLOAD standards
2682 /*if ((p_cust_mode = 'FORCE') or
2683 ((f_luby = 0) and (db_luby = 1)) or
2684 ((f_luby = db_luby) and (f_ludate > db_ludate)))*/
2685 /*if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,db_ludate,
2686 p_cust_mode))
2687 then*/
2688 -- seed data must not be changed by customers.Hence overwriting data always
2689 -- so that it covers up any changes by mistake
2690 update jtf_diagnostic_prereq
2691 set last_updated_by = f_luby,
2692 last_update_date = f_ludate,
2693 object_version_number = object_version_number + 1,
2694 security_group_id = to_number(P_SEC_GRP_ID)
2695 where sourceid = p_sourceid
2696 and prereqid = p_prereqid
2697 and SOURCEAPPID = p_SOURCEAPPID
2698 and type = p_type;
2699
2700 --end if;
2701
2702 exception
2703
2704 when no_data_found then
2705 -- Record doesn't exist - insert in all cases
2706
2707 insert into jtf_diagnostic_prereq(
2708 SEQUENCE,
2709 SOURCEID,
2710 PREREQID,
2711 SOURCEAPPID,
2712 TYPE,
2713 OBJECT_VERSION_NUMBER,
2714 CREATED_BY,
2715 LAST_UPDATE_DATE,
2716 LAST_UPDATED_BY,
2717 LAST_UPDATE_LOGIN,
2718 CREATION_DATE,
2719 SECURITY_GROUP_ID)
2720 values(
2721 jtf_diagnostic_prereq_s.nextval,
2722 p_sourceid,
2723 p_prereqid,
2724 p_sourceappid,
2725 p_type,
2726 1,
2727 f_luby,
2728 f_ludate,
2729 f_luby,
2730 null,
2731 f_ludate,
2732 to_number(P_SEC_GRP_ID));
2733
2734 end;
2735
2736 END LOAD_ROW_PREREQ;
2737
2738
2739 PROCEDURE LOAD_ROW_TEST_STEPS(
2740 P_APPID IN VARCHAR2,
2741 P_GROUPNAME IN VARCHAR2,
2742 P_TESTCLASSNAME IN VARCHAR2,
2743 P_TESTSTEPNAME IN VARCHAR2,
2744 P_EXECUTION_SEQUENCE IN VARCHAR2,
2745 P_STEP_TYPE IN VARCHAR2,
2746 P_STEP_DESCRIPTION IN VARCHAR2,
2747 P_ERROR_TYPE IN VARCHAR2,
2748 P_ERROR_MESSAGE IN VARCHAR2,
2749 P_FIX_INFO IN VARCHAR2,
2750 P_MULTI_ORG IN VARCHAR2,
2751 P_TABLE_VIEW_NAME IN VARCHAR2,
2752 P_WHERE_CLAUSE_OR_SQL IN VARCHAR2,
2753 P_PROFILE_NAME IN VARCHAR2,
2754 P_PROFILE_VALUE IN VARCHAR2,
2755 P_LOGICAL_OPERATOR IN VARCHAR2,
2756 P_FUNCTION_NAME IN VARCHAR2,
2757 P_VALIDATION_VAL1 IN VARCHAR2,
2758 P_VALIDATION_VAL2 IN VARCHAR2,
2759 P_LAST_UPDATE_DATE IN VARCHAR2,
2760 P_SECURITY_GROUP_ID IN VARCHAR2,
2761 P_CUST_MODE IN VARCHAR2,
2762 P_OWNER IN VARCHAR2) IS
2763
2764 f_luby number; -- entity owner in file
2765 f_ludate date; -- entity update date in file
2766 db_luby number; -- entity owner in db
2767 db_ludate date; -- entity update date in db
2768 v_num number; -- temporary variable
2769 v_EXECUTION_SEQUENCE number; -- temporary variable
2770
2771 BEGIN
2772
2773 -- Translate owner to file_last_updated_by
2774
2775 -- 5953806 - replaced to follow FNDLOAD standards
2776 /*if (P_OWNER = 'SEED') then
2777 f_luby := 1;
2778 else
2779 f_luby := 0;
2780 end if;*/
2781
2782 f_luby := fnd_load_util.owner_id(P_OWNER);
2783
2784 -- Translate char last_update_date to date
2785 f_ludate := nvl(to_date(P_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
2786
2787 begin
2788 select LAST_UPDATED_BY, LAST_UPDATE_DATE
2789 into db_luby, db_ludate
2790 from JTF_DIAGNOSTIC_DECL_TEST_STEPS
2791 where APPID = P_APPID
2792 and GROUPNAME = P_GROUPNAME
2793 and TESTCLASSNAME = P_TESTCLASSNAME
2794 and TESTSTEPNAME = P_TESTSTEPNAME;
2795
2796 -- Update record only as per standard
2797
2798 -- 5953806 - replaced to if statement to follow FNDLOAD standards
2799 /*if ((p_cust_mode = 'FORCE') or
2800 ((f_luby = 0) and (db_luby = 1)) or
2801 ((f_luby = db_luby) and (f_ludate > db_ludate)))*/
2802 /*if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,db_ludate,
2803 p_cust_mode))
2804 then*/
2805 -- seed data must not be changed by customers.Hence overwriting data always
2806 -- so that it covers up any changes by mistake
2807 update JTF_DIAGNOSTIC_DECL_TEST_STEPS
2808 set last_updated_by = f_luby,
2809 last_update_date = f_ludate,
2810 object_version_number = object_version_number + 1,
2811 security_group_id = to_number(P_SECURITY_GROUP_ID),
2812 STEP_TYPE = P_STEP_TYPE,
2813 STEP_DESCRIPTION = P_STEP_DESCRIPTION,
2814 ERROR_TYPE = P_ERROR_TYPE,
2815 ERROR_MESSAGE = P_ERROR_MESSAGE,
2816 FIX_INFO = P_FIX_INFO,
2817 MULTI_ORG = P_MULTI_ORG,
2818 TABLE_VIEW_NAME = P_TABLE_VIEW_NAME,
2819 WHERE_CLAUSE_OR_SQL = P_WHERE_CLAUSE_OR_SQL,
2820 PROFILE_NAME = P_PROFILE_NAME,
2821 PROFILE_VALUE = P_PROFILE_VALUE,
2822 LOGICAL_OPERATOR = P_LOGICAL_OPERATOR,
2823 FUNCTION_NAME = P_FUNCTION_NAME,
2824 VALIDATION_VAL1 = P_VALIDATION_VAL1,
2825 VALIDATION_VAL2 = P_VALIDATION_VAL2
2826 where APPID = P_APPID
2827 and GROUPNAME = P_GROUPNAME
2828 and TESTCLASSNAME = P_TESTCLASSNAME
2829 and TESTSTEPNAME = P_TESTSTEPNAME;
2830
2831 --end if;
2832
2833 exception
2834
2835 when no_data_found then
2836 -- Record doesn't exist - insert in all cases
2837
2838 select MAX(EXECUTION_SEQUENCE) into v_EXECUTION_SEQUENCE
2839 from JTF_DIAGNOSTIC_DECL_TEST_STEPS
2840 where APPID = P_APPID
2841 and GROUPNAME = P_GROUPNAME
2842 and TESTCLASSNAME = P_TESTCLASSNAME;
2843
2844 if sql%notfound or v_EXECUTION_SEQUENCE = 0 then
2845 v_EXECUTION_SEQUENCE := 1;
2846 else v_EXECUTION_SEQUENCE := v_EXECUTION_SEQUENCE + 1;
2847 end if;
2848
2849 insert into JTF_DIAGNOSTIC_DECL_TEST_STEPS(
2850 APPID,
2851 GROUPNAME,
2852 TESTCLASSNAME,
2853 TESTSTEPNAME,
2854 EXECUTION_SEQUENCE,
2855 STEP_TYPE,
2856 STEP_DESCRIPTION,
2857 ERROR_TYPE,
2858 ERROR_MESSAGE,
2859 FIX_INFO,
2860 MULTI_ORG,
2861 TABLE_VIEW_NAME,
2862 WHERE_CLAUSE_OR_SQL,
2863 PROFILE_NAME,
2864 PROFILE_VALUE,
2865 LOGICAL_OPERATOR,
2866 FUNCTION_NAME,
2867 VALIDATION_VAL1,
2868 VALIDATION_VAL2,
2869 OBJECT_VERSION_NUMBER,
2870 CREATED_BY,
2871 LAST_UPDATE_DATE,
2872 LAST_UPDATED_BY,
2873 LAST_UPDATE_LOGIN,
2874 CREATION_DATE,
2875 SECURITY_GROUP_ID)
2876 values(
2877 P_APPID,
2878 P_GROUPNAME,
2879 P_TESTCLASSNAME,
2880 P_TESTSTEPNAME,
2881 v_EXECUTION_SEQUENCE,
2882 P_STEP_TYPE,
2883 P_STEP_DESCRIPTION,
2884 P_ERROR_TYPE,
2885 P_ERROR_MESSAGE,
2886 P_FIX_INFO,
2887 P_MULTI_ORG,
2888 P_TABLE_VIEW_NAME,
2889 P_WHERE_CLAUSE_OR_SQL,
2890 P_PROFILE_NAME,
2891 P_PROFILE_VALUE,
2892 P_LOGICAL_OPERATOR,
2893 P_FUNCTION_NAME,
2894 P_VALIDATION_VAL1,
2895 P_VALIDATION_VAL2,
2896 1,
2897 f_luby,
2898 f_ludate,
2899 f_luby,
2900 null,
2901 f_ludate,
2902 to_number(P_SECURITY_GROUP_ID));
2903
2904 end;
2905 END LOAD_ROW_TEST_STEPS;
2906
2907
2908
2909 PROCEDURE LOAD_ROW_STEP_COLS(
2910 P_APPID IN VARCHAR2,
2911 P_GROUPNAME IN VARCHAR2,
2912 P_TESTCLASSNAME IN VARCHAR2,
2913 P_TESTSTEPNAME IN VARCHAR2,
2914 P_COLUMN_NAME IN VARCHAR2,
2915 P_LOGICAL_OPERATOR IN VARCHAR2,
2916 P_VALIDATION_VAL1 IN VARCHAR2,
2917 P_VALIDATION_VAL2 IN VARCHAR2,
2918 P_LAST_UPDATE_DATE IN VARCHAR2,
2919 P_SECURITY_GROUP_ID IN VARCHAR2,
2920 P_CUST_MODE IN VARCHAR2,
2921 P_OWNER IN VARCHAR2) IS
2922
2923 f_luby number; -- entity owner in file
2924 f_ludate date; -- entity update date in file
2925 db_luby number; -- entity owner in db
2926 db_ludate date; -- entity update date in db
2927 v_num number; -- temporary variable
2928
2929 BEGIN
2930
2931 -- Translate owner to file_last_updated_by
2932
2933 -- 5953806 - replaced to follow FNDLOAD standards
2934 /*if (P_OWNER = 'SEED') then
2935 f_luby := 1;
2936 else
2937 f_luby := 0;
2938 end if;*/
2939
2940 f_luby := fnd_load_util.owner_id(P_OWNER);
2941
2942 -- Translate char last_update_date to date
2943 f_ludate := nvl(to_date(P_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
2944
2945 begin
2946 select LAST_UPDATED_BY, LAST_UPDATE_DATE
2947 into db_luby, db_ludate
2948 from jtf_diagnostic_decl_step_cols
2949 where APPID = P_APPID
2950 and GROUPNAME = P_GROUPNAME
2951 and TESTCLASSNAME = P_TESTCLASSNAME
2952 and TESTSTEPNAME = P_TESTSTEPNAME
2953 and COLUMN_NAME = P_COLUMN_NAME;
2954
2955 -- Update record only as per standard
2956
2957 -- 5953806 - replaced to if statement to follow FNDLOAD standards
2958 /*if ((p_cust_mode = 'FORCE') or
2959 ((f_luby = 0) and (db_luby = 1)) or
2960 ((f_luby = db_luby) and (f_ludate > db_ludate)))*/
2961 /*if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,db_ludate,
2962 p_cust_mode))
2963 then*/
2964 -- seed data must not be changed by customers.Hence overwriting data always
2965 -- so that it covers up any changes by mistake
2966 update jtf_diagnostic_decl_step_cols
2967 set last_updated_by = f_luby,
2968 last_update_date = f_ludate,
2969 object_version_number = object_version_number + 1,
2970 security_group_id = to_number(P_SECURITY_GROUP_ID),
2971 LOGICAL_OPERATOR = P_LOGICAL_OPERATOR,
2972 VALIDATION_VAL1 = P_VALIDATION_VAL1,
2973 VALIDATION_VAL2 = P_VALIDATION_VAL2
2974 where APPID = P_APPID
2975 and GROUPNAME = P_GROUPNAME
2976 and TESTCLASSNAME = P_TESTCLASSNAME
2977 and TESTSTEPNAME = P_TESTSTEPNAME
2978 and COLUMN_NAME = P_COLUMN_NAME;
2979
2980 --end if;
2981
2982 exception
2983
2984 when no_data_found then
2985 -- Record doesn't exist - insert in all cases
2986
2987 insert into jtf_diagnostic_decl_step_cols(
2988 APPID,
2989 GROUPNAME,
2990 TESTCLASSNAME,
2991 TESTSTEPNAME,
2992 COLUMN_NAME,
2993 LOGICAL_OPERATOR,
2994 VALIDATION_VAL1,
2995 VALIDATION_VAL2,
2996 OBJECT_VERSION_NUMBER,
2997 CREATED_BY,
2998 LAST_UPDATE_DATE,
2999 LAST_UPDATED_BY,
3000 LAST_UPDATE_LOGIN,
3001 CREATION_DATE,
3002 SECURITY_GROUP_ID)
3003 values(
3004 P_APPID,
3005 P_GROUPNAME,
3006 P_TESTCLASSNAME,
3007 P_TESTSTEPNAME,
3008 P_COLUMN_NAME,
3009 P_LOGICAL_OPERATOR,
3010 P_VALIDATION_VAL1,
3011 P_VALIDATION_VAL2,
3012 1,
3013 f_luby,
3014 f_ludate,
3015 f_luby,
3016 null,
3017 f_ludate,
3018 to_number(P_SECURITY_GROUP_ID));
3019
3020 end;
3021
3022 END LOAD_ROW_STEP_COLS;
3023
3024
3025 ---------------------------------------------------------------------------
3026 -- Upload a test alert information row from the ldt file
3027 ---------------------------------------------------------------------------
3028
3029 PROCEDURE LOAD_ROW_ALERT(
3030 P_APPID IN VARCHAR2,
3031 P_GROUPNAME IN VARCHAR2,
3032 P_TESTCLASSNAME IN VARCHAR2,
3033 P_TYPE IN VARCHAR2,
3034 P_LEVEL_VALUE IN VARCHAR2,
3035 P_LUDATE IN VARCHAR2,
3036 P_SEC_GRP_ID IN VARCHAR2,
3037 P_CUST_MODE IN VARCHAR2,
3038 P_OWNER IN VARCHAR2) IS
3039
3040 f_luby number; -- entity owner in file
3041 f_ludate date; -- entity update date in file
3042 db_luby number; -- entity owner in db
3043 db_ludate date; -- entity update date in db
3044 v_num number; -- temporary variable
3045
3046 BEGIN
3047
3048 -- Translate owner to file_last_updated_by
3049
3050 -- 5953806 - replaced to follow FNDLOAD standards
3051 /*if (P_OWNER = 'SEED') then
3052 f_luby := 1;
3053 else
3054 f_luby := 0;
3055 end if;*/
3056
3057 f_luby := fnd_load_util.owner_id(P_OWNER);
3058
3059 -- Translate char last_update_date to date
3060 f_ludate := nvl(to_date(p_ludate, 'YYYY/MM/DD'), sysdate);
3061
3062 begin
3063 select LAST_UPDATED_BY, LAST_UPDATE_DATE
3064 into db_luby, db_ludate
3065 from jtf_diagnostic_alert
3066 where appid = p_appid and
3067 groupname = p_groupname
3068 and testclassname = p_testclassname
3069 and type = p_type;
3070
3071 -- Update record only as per standard
3072
3073 -- 5953806 - replaced to if statement to follow FNDLOAD standards
3074 /*if ((p_cust_mode = 'FORCE') or
3075 ((f_luby = 0) and (db_luby = 1)) or
3076 ((f_luby = db_luby) and (f_ludate > db_ludate)))*/
3077 /*if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,db_ludate,
3078 p_cust_mode))
3079 then*/
3080 -- seed data must not be changed by customers.Hence overwriting data always
3081 -- so that it covers up any changes by mistake
3082 update jtf_diagnostic_alert
3083 set last_updated_by = f_luby,
3084 last_update_date = f_ludate,
3085 object_version_number = object_version_number + 1,
3086 LEVEL_VALUE = to_number(p_LEVEL_VALUE),
3087 security_group_id = to_number(P_SEC_GRP_ID)
3088 where appid = p_appid and groupname = p_groupname
3089 and testclassname = p_testclassname
3090 and type = p_type;
3091
3092 --end if;
3093
3094 exception
3095
3096 when no_data_found then
3097 -- Record doesn't exist - insert in all cases
3098
3099 insert into jtf_diagnostic_alert(
3100 SEQUENCE,
3101 GROUPNAME,
3102 APPID,
3103 TYPE,
3104 TESTCLASSNAME,
3105 LEVEL_VALUE,
3106 OBJECT_VERSION_NUMBER,
3107 CREATED_BY,
3108 LAST_UPDATE_DATE,
3109 LAST_UPDATED_BY,
3110 LAST_UPDATE_LOGIN,
3111 CREATION_DATE,
3112 security_group_id)
3113 values(
3114 jtf_diagnostic_alert_s.nextval,
3115 p_groupname,
3116 p_appid,
3117 p_type,
3118 p_testclassname,
3119 to_number(p_level_value),
3120 1,
3121 f_luby,
3122 f_ludate,
3123 f_luby,
3124 null,
3125 f_ludate,
3126 to_number(P_SEC_GRP_ID));
3127 end;
3128
3129 END LOAD_ROW_ALERT;
3130
3131
3132 ---------------------------------------------------------------------------
3133 -- Upload a knowledge base information row from the ldt file
3134 ---------------------------------------------------------------------------
3135
3136 PROCEDURE LOAD_ROW_KB(
3137 P_APPID IN VARCHAR2,
3138 P_GROUPNAME IN VARCHAR2,
3139 P_TESTCLASSNAME IN VARCHAR2,
3140 P_USER_TEST_NAME IN VARCHAR2,
3141 P_METALINK_NOTE IN VARCHAR2,
3142 P_COMPETENCY IN VARCHAR2,
3143 P_SUBCOMPETENCY IN VARCHAR2,
3144 P_PRODUCTS IN VARCHAR2,
3145 P_TEST_TYPE IN VARCHAR2,
3146 P_ANALYSIS_SCOPE IN VARCHAR2,
3147 P_DESCRIPTION IN VARCHAR2,
3148 P_SHORT_DESCR IN VARCHAR2,
3149 P_USAGE_DESCR IN VARCHAR2,
3150 P_KEYWORDS IN VARCHAR2,
3151 P_COMPONENT IN VARCHAR2,
3152 P_SUBCOMPONENT IN VARCHAR2,
3153 P_HIGH_PRODUCT_VERSION IN VARCHAR2,
3154 P_LOW_PRODUCT_VERSION IN VARCHAR2,
3155 P_HIGH_PATCHSET IN VARCHAR2,
3156 P_LOW_PATCHSET IN VARCHAR2,
3157 P_LUDATE IN VARCHAR2,
3158 P_SEC_GRP_ID IN VARCHAR2,
3159 P_CUST_MODE IN VARCHAR2,
3160 P_OWNER IN VARCHAR2) IS
3161
3162 f_luby number; -- entity owner in file
3163 f_ludate date; -- entity update date in file
3164 db_luby number; -- entity owner in db
3165 db_ludate date; -- entity update date in db
3166 v_num number; -- temporary variable
3167 seq number; -- varaible for SEQUENCE in db
3168
3169 BEGIN
3170 -- Get the sequence number from test table
3171 -- as test table is updated before kb table, this should work
3172 begin
3173 select SEQUENCE
3174 into seq
3175 from jtf_diagnostic_test
3176 where appid = p_appid and
3177 groupname = p_groupname and
3178 testclassname = p_testclassname;
3179 end;
3180
3181 -- Translate owner to file_last_updated_by
3182
3183 -- 5953806 - replaced to follow FNDLOAD standards
3184 /*if (P_OWNER = 'SEED') then
3185 f_luby := 1;
3186 else
3187 f_luby := 0;
3188 end if;*/
3189
3190 f_luby := fnd_load_util.owner_id(P_OWNER);
3191
3192 -- Translate char last_update_date to date
3193 f_ludate := nvl(to_date(p_ludate, 'YYYY/MM/DD'), sysdate);
3194
3195 begin
3196 select LAST_UPDATED_BY, LAST_UPDATE_DATE
3197 into db_luby, db_ludate
3198 from jtf_diagnostic_kb
3199 where sequence = seq;
3200
3201 -- Update record only as per standard
3202
3203 -- 5953806 - replaced to if statement to follow FNDLOAD standards
3204 /*if ((p_cust_mode = 'FORCE') or
3205 ((f_luby = 0) and (db_luby = 1)) or
3206 ((f_luby = db_luby) and (f_ludate > db_ludate)))*/
3207 /*if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,db_ludate,
3208 p_cust_mode))
3209 then*/
3210 -- seed data must not be changed by customers.Hence overwriting data always
3211 -- so that it covers up any changes by mistake
3212 update jtf_diagnostic_kb
3213 set last_updated_by = f_luby,
3214 last_update_date = f_ludate,
3215 object_version_number = object_version_number + 1,
3216 USER_TEST_NAME = P_USER_TEST_NAME,
3217 METALINK_NOTE = P_METALINK_NOTE,
3218 COMPETENCY = P_COMPETENCY,
3219 SUBCOMPETENCY = P_SUBCOMPETENCY,
3220 PRODUCTS = P_PRODUCTS,
3221 TEST_TYPE = P_TEST_TYPE,
3222 ANALYSIS_SCOPE = P_ANALYSIS_SCOPE,
3223 DESCRIPTION = P_DESCRIPTION,
3224 SHORT_DESCR = P_SHORT_DESCR,
3225 USAGE_DESCR = P_USAGE_DESCR,
3226 KEYWORDS = P_KEYWORDS,
3227 COMPONENT = P_COMPONENT,
3228 SUBCOMPONENT = P_SUBCOMPONENT,
3229 HIGH_PRODUCT_VERSION = P_HIGH_PRODUCT_VERSION,
3230 LOW_PRODUCT_VERSION = P_LOW_PRODUCT_VERSION,
3231 HIGH_PATCHSET = P_HIGH_PATCHSET,
3232 LOW_PATCHSET = P_LOW_PATCHSET,
3233 security_group_id = to_number(P_SEC_GRP_ID)
3234 where sequence = seq;
3235
3236 --end if;
3237
3238 exception
3239
3240 when no_data_found then
3241 -- Record doesn't exist - insert in all cases
3242
3243 insert into jtf_diagnostic_kb(
3244 SEQUENCE,
3245 USER_TEST_NAME,
3246 METALINK_NOTE,
3247 COMPETENCY,
3248 SUBCOMPETENCY,
3249 PRODUCTS,
3250 TEST_TYPE,
3251 ANALYSIS_SCOPE,
3252 DESCRIPTION,
3253 SHORT_DESCR,
3254 USAGE_DESCR,
3255 KEYWORDS,
3256 COMPONENT,
3257 SUBCOMPONENT,
3258 HIGH_PRODUCT_VERSION,
3259 LOW_PRODUCT_VERSION,
3260 HIGH_PATCHSET,
3261 LOW_PATCHSET,
3262 OBJECT_VERSION_NUMBER,
3263 CREATED_BY,
3264 LAST_UPDATE_DATE,
3265 LAST_UPDATED_BY,
3266 LAST_UPDATE_LOGIN,
3267 CREATION_DATE,
3268 security_group_id)
3269 values(
3270 seq,
3271 P_USER_TEST_NAME,
3272 P_METALINK_NOTE,
3273 P_COMPETENCY,
3274 P_SUBCOMPETENCY,
3275 P_PRODUCTS,
3276 P_TEST_TYPE,
3277 P_ANALYSIS_SCOPE,
3278 P_DESCRIPTION,
3279 P_SHORT_DESCR,
3280 P_USAGE_DESCR,
3281 P_KEYWORDS,
3282 P_COMPONENT,
3283 P_SUBCOMPONENT,
3284 P_HIGH_PRODUCT_VERSION,
3285 P_LOW_PRODUCT_VERSION,
3286 P_HIGH_PATCHSET,
3287 P_LOW_PATCHSET,
3288 1,
3289 f_luby,
3290 f_ludate,
3291 f_luby,
3292 null,
3293 f_ludate,
3294 to_number(P_SEC_GRP_ID));
3295 end;
3296
3297 END LOAD_ROW_KB;
3298
3299 ------------------------------------------------------------
3300 -- procedure PARSE TEST CLASS NAME
3301 ------------------------------------------------------------
3302
3303 procedure PARSE_TESTCLASSNAME(
3304 P_TESTCLASSNAME IN VARCHAR2,
3305 V_PRODUCT OUT NOCOPY VARCHAR2,
3306 V_FILENAME OUT NOCOPY VARCHAR2) IS
3307
3308 BEGIN
3309
3310 select substr(P_TESTCLASSNAME,
3311 instr(P_TESTCLASSNAME,'.',1,2)+1,
3312 instr(P_TESTCLASSNAME,'.',1,3) -
3313 instr(P_TESTCLASSNAME,'.',1,2) - 1)
3314 into V_PRODUCT from dual;
3315
3316 select substr(P_TESTCLASSNAME,
3317 instr(P_TESTCLASSNAME,'.',-1,1)+1)
3318 into V_FILENAME from dual;
3319
3320 END PARSE_TESTCLASSNAME;
3321
3322 PROCEDURE SEED_TESTSET(
3323 P_NAME IN VARCHAR2,
3324 P_DESCRIPTION IN VARCHAR2,
3325 P_XML IN CLOB,
3326 P_LUDATE IN VARCHAR2,
3327 P_CUST_MODE IN VARCHAR2,
3328 P_OWNER IN VARCHAR2) IS
3329
3330 f_luby number; -- entity owner in file
3331 f_ludate date; -- entity update date in file
3332 db_luby number; -- entity owner in db
3333 db_ludate date; -- entity update date in db
3334
3335 BEGIN
3336
3337 -- Translate owner to file_last_updated_by
3338 f_luby := fnd_load_util.owner_id(P_OWNER);
3339
3340 -- Translate char last_update_date to date
3341 f_ludate := nvl(to_date(p_ludate, 'YYYY/MM/DD'), sysdate);
3342
3343 begin
3344 select LAST_UPDATED_BY, LAST_UPDATE_DATE
3345 into db_luby, db_ludate
3346 from jtf_diagnostic_testset
3347 where name = p_name;
3348
3349 -- Update record only as per standard
3350
3351 /*if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,db_ludate,
3352 p_cust_mode))
3353 then*/
3354 -- seed data must not be changed by customers.Hence overwriting data always
3355 -- so that it covers up any changes by mistake
3356 update_testset(p_name,p_description,p_xml,f_luby,f_ludate);
3357
3358 --end if;
3359
3360 exception
3361
3362 when no_data_found then
3363 -- Record doesn't exist - insert in all cases
3364 insert_testset(p_name,p_description,p_xml,f_luby,f_ludate,null,f_luby,f_ludate);
3365
3366 end;
3367
3368 END SEED_TESTSET;
3369
3370
3371 PROCEDURE UPDATE_TESTSET(
3372 P_NAME IN VARCHAR2,
3373 P_DESCRIPTION IN VARCHAR2,
3374 P_XML IN CLOB) IS
3375 f_luby number;
3376 f_ludate date;
3377 BEGIN
3378 f_luby := FND_GLOBAL.user_id;
3379 select sysdate into f_ludate from dual;
3380 UPDATE_TESTSET(P_NAME, P_DESCRIPTION, P_XML, f_luby, f_ludate);
3381 END UPDATE_TESTSET;
3382
3383 PROCEDURE UPDATE_TESTSET(
3384 P_NAME IN VARCHAR2,
3385 P_DESCRIPTION IN VARCHAR2,
3386 P_XML IN CLOB,
3387 P_LAST_UPDATED_BY IN NUMBER,
3388 P_LAST_UPDATED_DATE IN DATE) IS
3389
3390 BEGIN
3391 update jtf_diagnostic_testset
3392 set description = P_DESCRIPTION,
3393 xml = XMLTYPE(P_XML),
3394 last_updated_by = P_LAST_UPDATED_BY,
3395 last_update_date = P_LAST_UPDATED_DATE
3396 where name = P_NAME;
3397 END UPDATE_TESTSET;
3398
3399 PROCEDURE INSERT_TESTSET(
3400 P_NAME IN VARCHAR2,
3401 P_DESCRIPTION IN VARCHAR2,
3402 P_XML IN CLOB) IS
3403 f_luby number;
3404 f_ludate date;
3405 BEGIN
3406 f_luby := FND_GLOBAL.user_id;
3407 select sysdate into f_ludate from dual;
3408 INSERT_TESTSET(P_NAME, P_DESCRIPTION, P_XML, f_luby, f_ludate, null, f_luby, f_ludate);
3409 END INSERT_TESTSET;
3410
3411 PROCEDURE INSERT_TESTSET(
3412 P_NAME IN VARCHAR2,
3413 P_DESCRIPTION IN VARCHAR2,
3414 P_XML IN CLOB,
3415 P_CREATED_BY IN NUMBER,
3416 P_CREATION_DATE IN DATE,
3417 P_LAST_UPDATE_LOGIN IN NUMBER,
3418 P_LAST_UPDATED_BY IN NUMBER,
3419 P_LAST_UPDATED_DATE IN DATE) IS
3420
3421 BEGIN
3422 insert into jtf_diagnostic_testset
3423 (NAME, DESCRIPTION, XML, CREATED_BY, CREATION_DATE,
3424 LAST_UPDATE_LOGIN, LAST_UPDATED_BY, LAST_UPDATE_DATE)
3425 values
3426 ( P_NAME , P_DESCRIPTION, XMLType(P_XML), P_CREATED_BY, P_CREATION_DATE,
3427 P_LAST_UPDATE_LOGIN, P_LAST_UPDATED_BY, P_LAST_UPDATED_DATE);
3428 END INSERT_TESTSET;
3429
3430 -- ---------------------------------------------------------------------------------------
3431 -- Procedure to update valid applications for the test. The last updated date would be the
3432 -- system date and the user info will be taken from FND_GLOBAL.user_id
3433 -- ---------------------------------------------------------------------------------------
3434 PROCEDURE UPDATE_VALID_APPS(
3435 P_APPSHORTNAME IN VARCHAR2,
3436 P_GROUPNAME IN VARCHAR2,
3437 P_TESTCLASSNAME IN VARCHAR2,
3438 P_VALIDAPPS IN VARCHAR2) IS
3439 F_LUBY NUMBER;
3440 F_LUDATE DATE;
3441 BEGIN
3442 F_LUBY := FND_GLOBAL.user_id;
3443 SELECT SYSDATE INTO F_LUDATE FROM DUAL;
3444 UPDATE_VALID_APPS(P_APPSHORTNAME, P_GROUPNAME, P_TESTCLASSNAME,P_VALIDAPPS, F_LUBY, F_LUDATE);
3445 END UPDATE_VALID_APPS;
3446
3447 -- ------------------------------------------------------------------------------------------
3448 -- Procedure to update valid applications for the test providing the last updated information
3449 -- ------------------------------------------------------------------------------------------
3450 PROCEDURE UPDATE_VALID_APPS(
3451 P_APPSHORTNAME IN VARCHAR2,
3452 P_GROUPNAME IN VARCHAR2,
3453 P_TESTCLASSNAME IN VARCHAR2,
3454 P_VALIDAPPS IN VARCHAR2,
3455 P_LAST_UPDATED_BY IN NUMBER,
3456 P_LAST_UPDATED_DATE IN DATE) IS
3457
3458 BEGIN
3459
3460 UPDATE JTF_DIAGNOSTIC_TEST
3461 SET VALID_APPLICATIONS = XMLTYPE(P_VALIDAPPS),
3462 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
3463 LAST_UPDATE_DATE = P_LAST_UPDATED_DATE
3464 WHERE APPID = P_APPSHORTNAME
3465 AND GROUPNAME = P_GROUPNAME
3466 AND TESTCLASSNAME = P_TESTCLASSNAME;
3467
3468 END UPDATE_VALID_APPS;
3469
3470
3471 -- ------------------------------------------------------------------------------------------
3472 -- Function used to validate whether the user is having the privilege to execute the test
3473 -- or not. This function takes sensitivity & valid applications at test level as parameters
3474 -- and checks if user is having the privilege to execute the test
3475 -- ------------------------------------------------------------------------------------------
3476 FUNCTION VALIDATE_APPLICATIONS(
3477 P_SENSITIVITY NUMBER,
3478 P_VALID_APPS_XML XMLTYPE) RETURN NUMBER IS
3479
3480 -- a cursor pointing to list of applications which are valid for
3481 -- user obtained using USER_NAME
3482 cursor valid_user_apps_cursor is
3483 select distinct owner_tag from wf_roles where name in
3484 ( select role_name from wf_user_roles where user_name=sys_context('FND','USER_NAME')
3485 and role_name not in ( 'FND_RESP|FND|APPLICATION_DIAGNOSTICS|STANDARD','UMX|ODF_APPLICATION_END_USER_ROLE',
3486 'UMX|ODF_APPLICATION_SUPER_USER_ROLE','UMX|ODF_DIAGNOSTICS_SUPER_USER_ROLE')
3487 and sysdate >=start_date and start_date <nvl(expiration_date,sysdate+1)
3488 and nvl2(expiration_date,expiration_date,sysdate+1) >= sysdate
3489 )
3490 and sysdate >=start_date and start_date <nvl(expiration_date,sysdate+1)
3491 and owner_tag is not null;
3492
3493 -- a cursor pointing to valid apps for test using valid_applications column in test table
3494 cursor valid_seeded_apps_cursor is
3495 select extractvalue(value(tbl),'/value') apps from
3496 table(xmlsequence(extract(P_VALID_APPS_XML,'/list/value'))) tbl;
3497
3498 user_apps jtf_diag_arraylist; -- List of apps obtained using USER_NAME
3499 seeded_apps jtf_diag_arraylist; -- List of apps marked as valid @ test level
3500 custom_apps jtf_diag_arraylist; -- custom applications w.r.t seeded apps
3501 valid_apps jtf_diag_arraylist; -- valid seeded & custom apps for user
3502
3503
3504 BEGIN
3505
3506 if P_SENSITIVITY = 1 then
3507 return 1;
3508 end if;
3509
3510 -- List of apps obtained using USER_NAME
3511 user_apps := jtf_diag_arraylist();
3512
3513 -- List of apps marked as valid @ test level
3514 seeded_apps := jtf_diag_arraylist();
3515
3516
3517 -- get applications using USER_NAME
3518 for x in valid_user_apps_cursor loop
3519 user_apps.addtolist(x.owner_tag);
3520 end loop;
3521
3522 -- get valid_apps_for_test using valid_applications column in test table
3523 for x in valid_seeded_apps_cursor loop
3524 seeded_apps.addtolist(x.apps);
3525 end loop;
3526
3527
3528 for i in 1 .. seeded_apps.getsize() loop
3529
3530 -- if user_apps (obtained using USER_NAME) contains any of applications
3531 -- marked as valid at test level, then add those to valid_apps list
3532 if user_apps.contains(seeded_apps.get(i)) then
3533 return 1;
3534 end if;
3535
3536 -- custom apps w.r.t seeded apps
3537 custom_apps := get_custom_apps(seeded_apps.get(i));
3538
3539 -- if custom_apps contains any of applications on which user is having
3540 -- custom responsibility, then add respective seeded application to valid_apps list
3541 for j in 1 .. custom_apps.getsize() loop
3542 if user_apps.contains(custom_apps.get(j)) then
3543 return 1;
3544 end if;
3545 end loop;
3546 end loop;
3547
3548 return 0;
3549 -- end if;
3550 END VALIDATE_APPLICATIONS;
3551
3552 -- ------------------------------------------------------------------------------------------
3553 -- Function to return an arraylist of custom applications w.r.t seed application
3554 -- ------------------------------------------------------------------------------------------
3555 FUNCTION GET_CUSTOM_APPS(seeded_app VARCHAR2)
3556 RETURN JTF_DIAG_ARRAYLIST IS
3557
3558 p_object_id integer;
3559 p_permission_set_id integer;
3560 p_custom_role varchar2(100);
3561 custom_apps_list jtf_diag_arraylist;
3562
3563 cursor custom_apps_cursor is select instance_pk1_value from fnd_grants
3564 where grantee_key = p_custom_role and object_id = p_object_id
3565 and menu_id = p_permission_set_id;
3566
3567 BEGIN
3568 -- retrieve the OBJECT_ID of ODF_CUSTOMIZATION_OBJ object
3569 select object_id into p_object_id from fnd_objects
3570 where obj_name = 'ODF_CUSTOMIZATION_OBJ';
3571
3572 -- retrieve the PERMISSION_SET_ID of ODF_EXECUTION_PS
3573 select menu_id into p_permission_set_id from fnd_menus
3574 where menu_name = 'ODF_EXECUTION_PS';
3575
3576 --Custom role attached to seed application
3577 p_custom_role := 'UMX|ODF_CUSTOM_'||seeded_app||'_ROLE';
3578
3579 --instantiate array list
3580 custom_apps_list := jtf_diag_arraylist();
3581
3582 for x in custom_apps_cursor loop
3583 custom_apps_list.addtolist(x.instance_pk1_value);
3584 end loop;
3585
3586 return custom_apps_list;
3587 END GET_CUSTOM_APPS;
3588
3589
3590 -- ------------------------------------------------------------------------------------------
3591 -- Function to return an app id from app short name
3592 -- ------------------------------------------------------------------------------------------
3593 FUNCTION GET_APP_ID(APP_SHORT_NAME VARCHAR2)
3594 RETURN INTEGER IS
3595 p_appid integer;
3596 BEGIN
3597 select application_id into p_appid from fnd_application where application_short_name = APP_SHORT_NAME;
3598 return p_appid;
3599 END GET_APP_ID;
3600
3601 -- ------------------------------------------------------------------------------------------
3602 -- Function to return an array of spp short names from app short name
3603 -- ------------------------------------------------------------------------------------------
3604 FUNCTION GET_CUSTOM_APPS_ARRAY(APP_SHORT_NAME VARCHAR2)
3605 RETURN jtf_varchar2_table_100 IS
3606
3607 custom_apps_array jtf_varchar2_table_100;
3608 custom_apps_list jtf_diag_arraylist;
3609 --asize integer:=0;
3610 BEGIN
3611 custom_apps_list:= GET_CUSTOM_APPS(APP_SHORT_NAME);
3612 custom_apps_array := jtf_varchar2_table_100();
3613 for i in 1 .. custom_apps_list.getsize() loop
3614 -- asize := asize+1;
3615 custom_apps_array.extend;
3616 custom_apps_array(i):= custom_apps_list.get(i);
3617 end loop;
3618
3619 return custom_apps_array;
3620
3621 END GET_CUSTOM_APPS_ARRAY;
3622
3623 procedure UPDATE_TEST_END_DATE(
3624 P_APP_NAME IN VARCHAR2,
3625 P_GROUP_NAME IN VARCHAR2,
3626 P_TEST_NAME IN VARCHAR2,
3627 P_END_DATE IN DATE default null,
3628 P_LUBID IN NUMBER
3629 ) IS
3630 F_END_DATE date;
3631 BEGIN
3632 IF P_END_DATE IS NOT NULL THEN
3633 --F_END_DATE := to_date(P_END_DATE, JTF_DIAGNOSTIC_ADAPTUTIL.GET_SITE_DATE_FORMAT());
3634 F_END_DATE := P_END_DATE;
3635 END IF;
3636 UPDATE jtf_diagnostic_test
3637 SET end_date = F_END_DATE,
3638 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
3639 LAST_UPDATE_DATE = SYSDATE,
3640 LAST_UPDATED_BY = P_LUBID
3641 WHERE appid = P_APP_NAME AND
3642 groupname = P_GROUP_NAME AND
3643 testclassname = P_TEST_NAME;
3644
3645 IF SQL%NOTFOUND THEN
3646 RAISE_APPLICATION_ERROR(-20000,'Cant Update, Record Not
3647 Found');
3648 END IF;
3649
3650
3651 END UPDATE_TEST_END_DATE;
3652 END JTF_DIAGNOSTIC;
[Home] [Help]