[Home] [Help]
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
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:
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;
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
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:
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:
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;
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:
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
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
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;
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;
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;
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;
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;
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:
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;
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
440: select testclassname from jtf_diagnostic_test
441: where appid = P_APP_NAME
436: -- SKHEMANI Use the cursor to cleanup the
437: -- entry in the JTF_DIAGNOSTIC_KB table
438:
439: CURSOR TSTLIST IS
440: select testclassname from jtf_diagnostic_test
441: where appid = P_APP_NAME
442: and groupname = P_GROUP_NAME;
443:
444: CURSOR GRPLIST (c_ordernumber number) IS
442: and groupname = P_GROUP_NAME;
443:
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:
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
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
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;
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
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
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
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;
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;
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;
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;
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
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;
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:
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
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
591: and testclassname = P_TEST_CLASS_NAME
592: and rownum <= 1;
592: and rownum <= 1;
593:
594:
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
594:
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:
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
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
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:
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
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:
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
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:
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;
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;
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),
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
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
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');
747:
748: LOOP
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:
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
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
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;
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
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
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,
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
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,
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);
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
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:
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:
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
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,
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);
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:
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:
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
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:
1233: if sql%notfound or v_ordernumber = 0 then
1234: v_ordernumber := 1;
1235: else v_ordernumber := v_ordernumber + 1;
1236:
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,
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);
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:
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:
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:
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;
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,
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
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:
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:
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;
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;
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;
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,
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);
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
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
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');
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
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:
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)
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:
1679:
1680: BEGIN
1681:
1682: select count(*) into v_data_found
1683: from jtf_diagnostic_group
1684: where appid = p_appid
1685: and groupname = p_groupname;
1686:
1687: if v_data_found = 0 or sql%notfound then
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
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
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,
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
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
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
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
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
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
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
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:
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)
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,
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,
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
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,
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,
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,
1973: last_updated_by,
1974: creation_date,
1975: security_group_id)
1976: values(
1977: jtf_diagnostic_group_s.nextval,
1978: p_groupname,
1979: p_appid,
1980: v_sensitivity,
1981: v_num,
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,
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:
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,
2096: where appid = p_appid and groupname = p_groupname
2097: and testclassname = p_testclassname;
2098: else
2099:
2100: update jtf_diagnostic_test
2101: set last_updated_by = f_luby,
2102: last_update_date = sysdate,--f_ludate,
2103: object_version_number = object_version_number + 1,
2104: TOTALARGUMENTROWS = p_TOTALARGUMENTROWS,
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;
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,
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,
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,
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,
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
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,
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,
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,
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;
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)
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,
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,
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;
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),
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:
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,
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
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),
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,
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;
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),
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,
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,
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;
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:
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,
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,
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:
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
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,
3176: P_LAST_UPDATED_DATE IN DATE) IS
3177:
3178: BEGIN
3179:
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
3339: return custom_apps_array;
3340:
3341: END GET_CUSTOM_APPS_ARRAY;
3342:
3343: END JTF_DIAGNOSTIC;