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