[Home] [Help]
1: PACKAGE BODY JTF_DIAGNOSTIC AS
2: /* $Header: jtfdiagnostic_b.pls 120.33 2011/04/06 21:38:13 tshort ship $ */
3:
4: ------------------------------
5: -- Begin procedure GET APPS
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;
435:
436: check_extraneous_only boolean := false;
437:
438: -- SKHEMANI Use the cursor to cleanup the
439: -- entry in the JTF_DIAGNOSTIC_KB table
440:
441: -- there were extraneous decl_test_steps left
442: CURSOR TSTSTEPLIST IS
443: select testclassname, teststepname from jtf_diagnostic_decl_test_steps
439: -- entry in the JTF_DIAGNOSTIC_KB table
440:
441: -- there were extraneous decl_test_steps left
442: CURSOR TSTSTEPLIST IS
443: select testclassname, teststepname from jtf_diagnostic_decl_test_steps
444: where appid = P_APP_NAME
445: and groupname = P_GROUP_NAME;
446:
447: CURSOR TSTLIST IS
444: where appid = P_APP_NAME
445: and groupname = P_GROUP_NAME;
446:
447: CURSOR TSTLIST IS
448: select testclassname from jtf_diagnostic_test
449: where appid = P_APP_NAME
450: and groupname = P_GROUP_NAME;
451:
452: CURSOR GRPLIST (c_ordernumber number) IS
450: and groupname = P_GROUP_NAME;
451:
452: CURSOR GRPLIST (c_ordernumber number) IS
453: select groupname, ordernumber, object_version_number
454: from jtf_diagnostic_group
455: where appid = P_APP_NAME
456: and ordernumber > c_ordernumber
457: order by ordernumber;
458:
461: -- populate the variable v_ordernum
462: -- so that we can use this for resequencing
463:
464: select distinct count(*) into V_ORDERNUM
465: from jtf_diagnostic_group
466: where APPID = P_APP_NAME and groupname = P_GROUP_NAME;
467:
468: -- if the ordernumber not found then no point continuing with
469: -- the rest, just raise an exception
477: -- get the right order number into the variable
478: -- for further processing
479:
480: select distinct ordernumber into V_ORDERNUM
481: from jtf_diagnostic_group
482: where APPID = P_APP_NAME and groupname = P_GROUP_NAME;
483:
484: -- if flow of control reaches here,
485: -- cleanup all information about this group
482: where APPID = P_APP_NAME and groupname = P_GROUP_NAME;
483:
484: -- if flow of control reaches here,
485: -- cleanup all information about this group
486: -- from the jtf_diagnostic_group table
487:
488: delete from jtf_diagnostic_group
489: where groupname = P_GROUP_NAME
490: and appid = P_APP_NAME;
484: -- if flow of control reaches here,
485: -- cleanup all information about this group
486: -- from the jtf_diagnostic_group table
487:
488: delete from jtf_diagnostic_group
489: where groupname = P_GROUP_NAME
490: and appid = P_APP_NAME;
491:
492: -- Resequence the groups to make sure there are no holes in the
495: open GRPLIST(V_ORDERNUM);
496: loop
497: fetch GRPLIST into l_groupname, l_ordernumber, l_object_version_number;
498: exit when (GRPLIST%notfound);
499: update jtf_diagnostic_group
500: set ordernumber = (l_ordernumber - 1),
501: OBJECT_VERSION_NUMBER = l_OBJECT_VERSION_NUMBER + 1,
502: LAST_UPDATE_DATE = SYSDATE
503: where groupname = l_groupname
505: end loop;
506: close GRPLIST;
507:
508: -- SKHEMANI Use the cursor to cleanup the
509: -- entries of all tests in the JTF_DIAGNOSTIC_KB table
510: -- pertaining to this group, if any
511:
512: FOR x in TSTLIST
513: LOOP
519: -- fix for bug 4606418, we were not cleaning
520: -- up the prereqs of a group at the time of deleting a
521: -- group
522:
523: delete from jtf_diagnostic_prereq
524: where sourceid = P_GROUP_NAME
525: and sourceappid = P_APP_NAME;
526:
527: ELSE
546: P_GROUP_NAME IN VARCHAR2,
547: P_TEST_CLASS_NAME IN VARCHAR2
548: ) IS
549:
550: V_ORDERNUM jtf_diagnostic_test.ordernumber%TYPE;
551: V_SEQUENCE jtf_diagnostic_test.sequence%TYPE;
552:
553: l_testclassname varchar2(1500);
554: l_ordernumber number;
547: P_TEST_CLASS_NAME IN VARCHAR2
548: ) IS
549:
550: V_ORDERNUM jtf_diagnostic_test.ordernumber%TYPE;
551: V_SEQUENCE jtf_diagnostic_test.sequence%TYPE;
552:
553: l_testclassname varchar2(1500);
554: l_ordernumber number;
555: l_object_version_number number;
557: check_extraneous_only boolean := false;
558:
559: CURSOR TESTLIST (c_ordernumber number) IS
560: select testclassname, ordernumber, object_version_number
561: from jtf_diagnostic_test
562: where appid = P_APP_NAME
563: and groupname = P_GROUP_NAME
564: and ordernumber > c_ordernumber
565: order by ordernumber;
566:
567: BEGIN
568:
569: select count(*) into V_ORDERNUM
570: from jtf_diagnostic_test
571: where APPID = P_APP_NAME
572: and groupname = P_GROUP_NAME
573: and testclassname = P_TEST_CLASS_NAME
574: and rownum <= 1;
582:
583: IF (check_extraneous_only = FALSE) THEN
584: -- SKHEMANI if flow of control reaches here, then the test has been found
585: -- great... we will use this sequence number to cleanup the
586: -- entry in the JTF_DIAGNOSTIC_KB table
587:
588: select sequence into V_SEQUENCE
589: from jtf_diagnostic_test
590: where APPID = P_APP_NAME
585: -- great... we will use this sequence number to cleanup the
586: -- entry in the JTF_DIAGNOSTIC_KB table
587:
588: select sequence into V_SEQUENCE
589: from jtf_diagnostic_test
590: where APPID = P_APP_NAME
591: and groupname = P_GROUP_NAME
592: and testclassname = P_TEST_CLASS_NAME
593: and rownum <= 1;
592: and testclassname = P_TEST_CLASS_NAME
593: and rownum <= 1;
594:
595: -- SKHEMANI Use the stored sequence number to cleanup the
596: -- entry in the JTF_DIAGNOSTIC_KB table
597:
598: delete from jtf_diagnostic_kb where
599: sequence = V_SEQUENCE;
600:
594:
595: -- SKHEMANI Use the stored sequence number to cleanup the
596: -- entry in the JTF_DIAGNOSTIC_KB table
597:
598: delete from jtf_diagnostic_kb where
599: sequence = V_SEQUENCE;
600:
601:
602: -- populate the variable v_ordernum
605: -- they should all be deleted since a group should have the same testclassname
606: -- appearing once in it
607:
608: select ordernumber into V_ORDERNUM
609: from jtf_diagnostic_test
610: where APPID = P_APP_NAME
611: and groupname = P_GROUP_NAME
612: and testclassname = P_TEST_CLASS_NAME
613: and rownum <= 1;
613: and rownum <= 1;
614:
615:
616: -- cleanup all information about this test
617: -- from the jtf_diagnostic_test table
618:
619: delete from jtf_diagnostic_test
620: where groupname = P_GROUP_NAME
621: and appid = P_APP_NAME
615:
616: -- cleanup all information about this test
617: -- from the jtf_diagnostic_test table
618:
619: delete from jtf_diagnostic_test
620: where groupname = P_GROUP_NAME
621: and appid = P_APP_NAME
622: and testclassname = P_TEST_CLASS_NAME;
623:
628: loop
629: fetch TESTLIST into l_testclassname, l_ordernumber,
630: l_object_version_number;
631: exit when (TESTLIST%notfound);
632: update jtf_diagnostic_test
633: set ordernumber = (l_ordernumber - 1),
634: OBJECT_VERSION_NUMBER = l_OBJECT_VERSION_NUMBER + 1,
635: LAST_UPDATE_DATE = SYSDATE
636: where testclassname = l_testclassname
639: end loop;
640: close TESTLIST;
641:
642: -- cleanup all information about this testcase (testcase arguments)
643: -- from the jtf_diagnostic_arg table
644:
645: delete from jtf_diagnostic_arg
646: where groupname = P_GROUP_NAME
647: and appid = P_APP_NAME
641:
642: -- cleanup all information about this testcase (testcase arguments)
643: -- from the jtf_diagnostic_arg table
644:
645: delete from jtf_diagnostic_arg
646: where groupname = P_GROUP_NAME
647: and appid = P_APP_NAME
648: and testclassname = P_TEST_CLASS_NAME;
649:
649:
650: END IF;
651:
652: -- cleanup all information about all teststeps of this testcase
653: -- from the jtf_diagnostic_decl_test_steps table
654:
655: delete from jtf_diagnostic_decl_test_steps
656: where groupname = P_GROUP_NAME
657: and appid = P_APP_NAME
651:
652: -- cleanup all information about all teststeps of this testcase
653: -- from the jtf_diagnostic_decl_test_steps table
654:
655: delete from jtf_diagnostic_decl_test_steps
656: where groupname = P_GROUP_NAME
657: and appid = P_APP_NAME
658: and testclassname = P_TEST_CLASS_NAME;
659:
657: and appid = P_APP_NAME
658: and testclassname = P_TEST_CLASS_NAME;
659:
660: -- cleanup all information about all teststeps of this testcase
661: -- from the jtf_diagnostic_decl_step_cols table
662:
663: delete from jtf_diagnostic_decl_step_cols
664: where groupname = P_GROUP_NAME
665: and appid = P_APP_NAME
659:
660: -- cleanup all information about all teststeps of this testcase
661: -- from the jtf_diagnostic_decl_step_cols table
662:
663: delete from jtf_diagnostic_decl_step_cols
664: where groupname = P_GROUP_NAME
665: and appid = P_APP_NAME
666: and testclassname = P_TEST_CLASS_NAME;
667:
678: P_TEST_CLASS_NAME IN VARCHAR2
679: ) IS
680:
681: BEGIN
682: delete from jtf_diagnostic_arg
683: where testclassname = P_TEST_CLASS_NAME
684: and groupname = P_GROUP_NAME
685: and appid = P_APP_NAME;
686: END DELETE_ALL_ARGS_FOR_TEST;
706: -- testcase where we get the rownumber from the UI / Java layer
707: -- where each rownumber corresponds to one combination of
708: -- arguments which we will just delete
709:
710: delete from jtf_diagnostic_arg
711: where testclassname = P_TEST_CLASS_NAME
712: and groupname = P_GROUP_NAME
713: and appid = P_APP_NAME
714: and rownumber = P_ARG_ROW_NUM;
713: and appid = P_APP_NAME
714: and rownumber = P_ARG_ROW_NUM;
715:
716: -- If a row was deleted, then bump down the
717: -- number of argument rows for jtf_diagnostic_test
718:
719: IF NOT SQL%NOTFOUND THEN
720: update jtf_diagnostic_test
721: set totalargumentrows = (totalargumentrows - 1),
716: -- If a row was deleted, then bump down the
717: -- number of argument rows for jtf_diagnostic_test
718:
719: IF NOT SQL%NOTFOUND THEN
720: update jtf_diagnostic_test
721: set totalargumentrows = (totalargumentrows - 1),
722: OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
723: LAST_UPDATE_DATE = SYSDATE
724: where testclassname = P_TEST_CLASS_NAME
724: where testclassname = P_TEST_CLASS_NAME
725: and groupname = P_GROUP_NAME
726: and appid = P_APP_NAME;
727:
728: update jtf_diagnostic_arg
729: set rownumber = (rownumber - 1),
730: OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
731: LAST_UPDATE_DATE = SYSDATE
732: where testclassname = P_TEST_CLASS_NAME
757: v_reorder boolean;
758:
759: CURSOR DECLTESTLIST (c_ordernumber number) IS
760: select teststepname, execution_sequence
761: from jtf_diagnostic_decl_test_steps
762: where appid = P_APPID
763: and groupname = P_GROUPNAME
764: and testclassname = P_TESTCLASSNAME
765: and execution_sequence > c_ordernumber
767:
768: BEGIN
769:
770: select execution_sequence into v_ordernum
771: from jtf_diagnostic_decl_test_steps
772: where appid = P_APPID and groupname = P_GROUPNAME
773: and testclassname = P_TESTCLASSNAME and teststepname = P_TESTSTEPNAME;
774:
775: IF v_ordernum is null then
777: ELSE
778: v_reorder := true;
779: END IF;
780:
781: delete from jtf_diagnostic_decl_test_steps
782: where appid = P_APPID
783: and groupname = P_GROUPNAME
784: and testclassname = P_TESTCLASSNAME
785: and teststepname = P_TESTSTEPNAME;
788: open DECLTESTLIST(V_ORDERNUM);
789: loop
790: fetch DECLTESTLIST into l_teststepname, l_ordernumber;
791: exit when (DECLTESTLIST%notfound);
792: update jtf_diagnostic_decl_test_steps
793: set execution_sequence = (l_ordernumber - 1),
794: LAST_UPDATE_DATE = SYSDATE
795: where teststepname = l_teststepname
796: and appid = P_APPID
799: end loop;
800: close DECLTESTLIST;
801: END IF;
802:
803: delete from jtf_diagnostic_decl_test_steps
804: where appid = P_APPID
805: and groupname = P_GROUPNAME
806: and testclassname = P_TESTCLASSNAME
807: and teststepname = P_TESTSTEPNAME;
805: and groupname = P_GROUPNAME
806: and testclassname = P_TESTCLASSNAME
807: and teststepname = P_TESTSTEPNAME;
808:
809: delete from jtf_diagnostic_decl_step_cols
810: where groupname = P_GROUPNAME
811: and appid = P_APPID
812: and testclassname = P_TESTCLASSNAME;
813:
830: BEGIN
831:
832: SELECT COUNT(*)
833: INTO v_numofrows
834: FROM jtf_diagnostic_group
835: WHERE appid = P_APPID;
836:
837: IF P_GROUPNAMES.COUNT <> v_numofrows THEN
838: --RAISE_APPLICATION_ERROR(-20000, 'Cant Update - Mismatch');
843:
844: LOOP
845: IF P_GROUPNAMES.EXISTS(v_index) THEN
846:
847: UPDATE jtf_diagnostic_group
848: SET ordernumber = v_index * -1
849: WHERE appid = P_APPID AND
850: ordernumber = v_index;
851:
848: SET ordernumber = v_index * -1
849: WHERE appid = P_APPID AND
850: ordernumber = v_index;
851:
852: UPDATE jtf_diagnostic_group
853: SET ordernumber = v_index,
854: OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
855: LAST_UPDATE_DATE = SYSDATE
856: --LAST_UPDATED_BY = P_LUBID
904: BEGIN
905:
906: SELECT COUNT(*)
907: INTO v_numofrows
908: FROM jtf_diagnostic_test
909: WHERE appid = P_APPID AND
910: groupname = P_GROUPNAME;
911:
912: IF P_TESTCLASSNAMES.COUNT <> v_numofrows THEN
918:
919: LOOP
920: IF P_TESTCLASSNAMES.EXISTS(v_index) THEN
921:
922: UPDATE jtf_diagnostic_test
923: SET ordernumber = v_index * -1
924: WHERE appid = P_APPID AND
925: groupname = p_groupname and
926: ordernumber = v_index;
925: groupname = p_groupname and
926: ordernumber = v_index;
927:
928:
929: UPDATE jtf_diagnostic_test
930: SET OrderNumber = v_index,
931: OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
932: LAST_UPDATE_DATE = SYSDATE
933: --LAST_UPDATED_BY = P_LUBID
987:
988: -- if flow of control has reached thus far, remove all records
989: -- for the sourceid supplied to the pl/sql layer
990:
991: delete from jtf_diagnostic_prereq
992: where sourceid = p_sourceid
993: and sourceappid = p_sourceappid;
994:
995: IF P_PREREQID IS NOT NULL AND P_PREREQID.COUNT > 0 THEN
1038:
1039: loop
1040: if p_argnames.EXISTS(v_index) AND p_argvalues.exists(v_index) then
1041:
1042: update jtf_diagnostic_arg set
1043: argvalue = p_argvalues(v_index),
1044: arg_date_value = p_arg_dates(v_index),
1045: arg_number_value = p_arg_numbers(v_index),
1046: object_version_number = object_version_number + 1,
1087:
1088: loop
1089: if p_argnames.EXISTS(v_index) AND p_argvalues.exists(v_index) then
1090:
1091: update jtf_diagnostic_arg set
1092: argvalue = p_argvalues(v_index),
1093: object_version_number = object_version_number + 1,
1094: -- last_updated_by = UID,
1095: last_updated_by = P_LUBID,
1156:
1157: -- check if the application value entered is
1158: -- a valid application in the fnd_application table
1159: -- and if yes it should not already be there in the
1160: -- jtf_diagnostic_app table
1161:
1162: -- select distinct application_short_name into v_asn
1163: -- from fnd_application
1164: -- where application_short_name = P_APPID
1168: -- raise_application_error(-20000,
1169: -- 'Invalid application short name');
1170: -- else
1171: select count(*) into v_sourcetype
1172: from jtf_diagnostic_app
1173: where appid = P_APPID;
1174:
1175: if v_sourcetype <> 0 then
1176: raise_application_error(-20000,
1182:
1183: -- Else create a new record
1184: -- since the new value seems fine
1185:
1186: insert into jtf_diagnostic_app(
1187: SEQUENCE, APPID, OBJECT_VERSION_NUMBER, CREATED_BY,
1188: LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE)
1189: values (JTF_DIAGNOSTIC_APP_S.NEXTVAL, P_APPID, 1, P_LUBID,
1190: SYSDATE, P_LUBID, NULL, SYSDATE);
1185:
1186: insert into jtf_diagnostic_app(
1187: SEQUENCE, APPID, OBJECT_VERSION_NUMBER, CREATED_BY,
1188: LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE)
1189: values (JTF_DIAGNOSTIC_APP_S.NEXTVAL, P_APPID, 1, P_LUBID,
1190: SYSDATE, P_LUBID, NULL, SYSDATE);
1191:
1192:
1193: -- Now check if the object received as the pre-req array
1227: P_APP IN VARCHAR2,
1228: P_PREREQID IN JTF_VARCHAR2_TABLE_4000,
1229: P_LUBID IN NUMBER) IS
1230:
1231: v_groupname jtf_diagnostic_group.groupname%TYPE;
1232: V_SOURCETYPE BINARY_INTEGER := 2;
1233: v_ordernumber jtf_diagnostic_group.ordernumber%TYPE;
1234:
1235:
1229: P_LUBID IN NUMBER) IS
1230:
1231: v_groupname jtf_diagnostic_group.groupname%TYPE;
1232: V_SOURCETYPE BINARY_INTEGER := 2;
1233: v_ordernumber jtf_diagnostic_group.ordernumber%TYPE;
1234:
1235:
1236: BEGIN
1237:
1247: -- not already there in the tables for the application
1248: -- and if yes it should not be reentered
1249:
1250: select count(*) into v_sourcetype
1251: from jtf_diagnostic_group
1252: where appid = P_APP and groupname = p_new_group
1253: and rownum <= 1;
1254:
1255: -- if anything found then raise an application error since the
1270: -- and add this to the end. If there are no groups
1271: -- then make sure that this gets the first order number
1272:
1273: select MAX(ordernumber) into v_ordernumber
1274: from jtf_diagnostic_group where appid = p_app;
1275:
1276: if sql%notfound or v_ordernumber = 0 then
1277: v_ordernumber := 1;
1278: else v_ordernumber := v_ordernumber + 1;
1279:
1280: end if;
1281:
1282:
1283: insert into jtf_diagnostic_group(
1284: SEQUENCE, GROUPNAME,
1285: APPID, ORDERNUMBER,
1286: OBJECT_VERSION_NUMBER, CREATED_BY,
1287: LAST_UPDATE_DATE, LAST_UPDATED_BY,
1286: OBJECT_VERSION_NUMBER, CREATED_BY,
1287: LAST_UPDATE_DATE, LAST_UPDATED_BY,
1288: LAST_UPDATE_LOGIN, CREATION_DATE)
1289: values(
1290: JTF_DIAGNOSTIC_GROUP_S.NEXTVAL, P_NEW_GROUP,
1291: P_APP, DECODE(v_ordernumber,null,1,v_ordernumber),
1292: 1, P_LUBID,
1293: SYSDATE, P_LUBID,
1294: NULL, SYSDATE);
1331: P_PREREQID IN JTF_VARCHAR2_TABLE_4000,
1332: P_SENSITIVITY IN NUMBER,
1333: P_LUBID IN NUMBER) IS
1334:
1335: v_groupname jtf_diagnostic_group.groupname%TYPE;
1336: V_SOURCETYPE BINARY_INTEGER := 2;
1337: v_ordernumber jtf_diagnostic_group.ordernumber%TYPE;
1338:
1339:
1333: P_LUBID IN NUMBER) IS
1334:
1335: v_groupname jtf_diagnostic_group.groupname%TYPE;
1336: V_SOURCETYPE BINARY_INTEGER := 2;
1337: v_ordernumber jtf_diagnostic_group.ordernumber%TYPE;
1338:
1339:
1340: BEGIN
1341:
1351: -- not already there in the tables for the application
1352: -- and if yes it should not be reentered
1353:
1354: select count(*) into v_sourcetype
1355: from jtf_diagnostic_group
1356: where appid = P_APP and groupname = p_new_group
1357: and rownum <= 1;
1358:
1359: -- if anything found then raise an application error since the
1374: -- and add this to the end. If there are no groups
1375: -- then make sure that this gets the first order number
1376:
1377: select MAX(ordernumber) into v_ordernumber
1378: from jtf_diagnostic_group where appid = p_app;
1379:
1380: if sql%notfound or v_ordernumber = 0 then
1381: v_ordernumber := 1;
1382: else v_ordernumber := v_ordernumber + 1;
1383:
1384: end if;
1385:
1386:
1387: insert into jtf_diagnostic_group(
1388: SEQUENCE, GROUPNAME,
1389: APPID, ORDERNUMBER,
1390: OBJECT_VERSION_NUMBER, CREATED_BY,
1391: LAST_UPDATE_DATE, LAST_UPDATED_BY,
1390: OBJECT_VERSION_NUMBER, CREATED_BY,
1391: LAST_UPDATE_DATE, LAST_UPDATED_BY,
1392: LAST_UPDATE_LOGIN, CREATION_DATE, SENSITIVITY)
1393: values(
1394: JTF_DIAGNOSTIC_GROUP_S.NEXTVAL, P_NEW_GROUP,
1395: P_APP, DECODE(v_ordernumber,null,1,v_ordernumber),
1396: 1, P_LUBID,
1397: SYSDATE, P_LUBID,
1398: NULL, SYSDATE, P_SENSITIVITY);
1436: p_sensitivity out NOCOPY number) IS
1437:
1438: begin
1439: select sensitivity into p_sensitivity
1440: from jtf_diagnostic_group
1441: where appid = p_appid and
1442: groupname = p_group_name;
1443: end GET_GROUP_SENSITIVITY;
1444:
1453: p_meta_data in varchar2,
1454: p_lubid in number) IS
1455:
1456: V_SOURCETYPE BINARY_INTEGER := 2;
1457: v_ordernumber jtf_diagnostic_test.ordernumber%TYPE;
1458: l_sensitivity number;
1459: f_end_date date;
1460: f_meta_data xmltype;
1461:
1473: -- the table. The same testcase should not exist 2 times in the
1474: -- group
1475:
1476: select count(*) into V_SOURCETYPE
1477: from jtf_diagnostic_test
1478: where appid = p_appid and groupname = p_group_name
1479: and testclassname = p_testclassname
1480: and rownum <= 1;
1481:
1495: l_sensitivity := P_SENSITIVITY;
1496: end if;
1497:
1498: IF P_END_DATE IS NOT NULL THEN
1499: --F_END_DATE := to_date(P_END_DATE, JTF_DIAGNOSTIC_ADAPTUTIL.GET_SITE_DATE_FORMAT());
1500: F_END_DATE := P_END_DATE;
1501: END IF;
1502:
1503: IF P_META_DATA is not null then
1510: -- in the group and add this to the end. If there are no tests
1511: -- then make sure that this gets the first order number
1512:
1513: select MAX(ordernumber) into v_ordernumber
1514: from jtf_diagnostic_test where appid = p_appid
1515: and groupname = p_group_name;
1516:
1517: if sql%notfound or v_ordernumber = 0 then
1518: v_ordernumber := 1;
1518: v_ordernumber := 1;
1519: else v_ordernumber := v_ordernumber + 1;
1520: end if;
1521:
1522: insert into jtf_diagnostic_test(
1523: SEQUENCE, GROUPNAME, APPID,
1524: ORDERNUMBER, TESTTYPE, TESTCLASSNAME,
1525: TOTALARGUMENTROWS,
1526: SENSITIVITY,
1527: OBJECT_VERSION_NUMBER, CREATED_BY,
1528: LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
1529: CREATION_DATE,VALID_APPLICATIONS,END_DATE,TEST_METADATA)
1530: values(
1531: JTF_DIAGNOSTIC_TEST_S.NEXTVAL, p_group_name, p_appid,
1532: decode(v_ordernumber, null, 1, v_ordernumber),
1533: p_test_type, p_testclassname,
1534: 0, l_sensitivity, 1, p_lubid,
1535: SYSDATE, p_lubid, NULL,
1585: p_arg_values in jtf_varchar2_table_4000,
1586: p_lubid in number) IS
1587:
1588: V_SOURCETYPE BINARY_INTEGER := 1;
1589: v_rownumber jtf_diagnostic_arg.rownumber%TYPE;
1590: v_valsetnumber jtf_diagnostic_arg.valuesetnumber%TYPE;
1591:
1592: BEGIN
1593: -- first check if the application is valid
1586: p_lubid in number) IS
1587:
1588: V_SOURCETYPE BINARY_INTEGER := 1;
1589: v_rownumber jtf_diagnostic_arg.rownumber%TYPE;
1590: v_valsetnumber jtf_diagnostic_arg.valuesetnumber%TYPE;
1591:
1592: BEGIN
1593: -- first check if the application is valid
1594:
1602:
1603: -- then check if the testclassname is valid
1604:
1605: select count(*) into v_sourcetype
1606: from jtf_diagnostic_test where appid = p_appid
1607: and groupname = p_group_name
1608: and testclassname = p_test_class_name
1609: and rownum <= 1;
1610:
1619: -- then get the max row number and increment it by 1
1620: -- max row number is for this testclassname only
1621:
1622: select max(rownumber) into v_rownumber
1623: from jtf_diagnostic_arg where testclassname = p_test_class_name
1624: and groupname = p_group_name and appid = p_appid;
1625:
1626: if sql%notfound or v_rownumber = 0 then
1627: v_rownumber := 1;
1629: end if;
1630:
1631: -- now get the max valuesetnumber and increment it by 1
1632:
1633: select JTF_DIAGNOSTIC_ARG_VAL_SET_S.nextval
1634: into v_valsetnumber from dual;
1635:
1636: -- select max(valuesetnumber) into v_valsetnumber
1637: -- from jtf_diagnostic_arg;
1633: select JTF_DIAGNOSTIC_ARG_VAL_SET_S.nextval
1634: into v_valsetnumber from dual;
1635:
1636: -- select max(valuesetnumber) into v_valsetnumber
1637: -- from jtf_diagnostic_arg;
1638:
1639: -- if sql%notfound or v_valsetnumber = 0 then
1640: -- v_valsetnumber := 1;
1641: -- else v_valsetnumber := v_valsetnumber + 1;
1645: V_SOURCETYPE := 1;
1646: loop
1647: if p_arg_names.exists(v_sourcetype) then
1648:
1649: insert into jtf_diagnostic_arg(
1650: SEQUENCE, TESTCLASSNAME, GROUPNAME,
1651: APPID, ARGNAME, ARGVALUE,
1652: ROWNUMBER, VALUESETNUMBER, OBJECT_VERSION_NUMBER,
1653: CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
1652: ROWNUMBER, VALUESETNUMBER, OBJECT_VERSION_NUMBER,
1653: CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
1654: LAST_UPDATE_LOGIN, CREATION_DATE)
1655: values(
1656: JTF_DIAGNOSTIC_ARG_S.NEXTVAL, p_test_class_name, p_group_name,
1657: p_appid, p_arg_names(v_sourcetype), p_arg_values(v_sourcetype),
1658: decode(v_rownumber, null, 1, v_rownumber), v_valsetnumber, 1,
1659: p_lubid, SYSDATE, p_lubid,
1660: NULL, SYSDATE);
1697: p_arg_numbers in jtf_number_table,
1698: p_lubid in number) IS
1699:
1700: V_SOURCETYPE BINARY_INTEGER := 1;
1701: v_rownumber jtf_diagnostic_arg.rownumber%TYPE;
1702: v_valsetnumber jtf_diagnostic_arg.valuesetnumber%TYPE;
1703:
1704: BEGIN
1705: -- first check if the application is valid
1698: p_lubid in number) IS
1699:
1700: V_SOURCETYPE BINARY_INTEGER := 1;
1701: v_rownumber jtf_diagnostic_arg.rownumber%TYPE;
1702: v_valsetnumber jtf_diagnostic_arg.valuesetnumber%TYPE;
1703:
1704: BEGIN
1705: -- first check if the application is valid
1706:
1714:
1715: -- then check if the testclassname is valid
1716:
1717: select count(*) into v_sourcetype
1718: from jtf_diagnostic_test where appid = p_appid
1719: and groupname = p_group_name
1720: and testclassname = p_test_class_name
1721: and rownum <= 1;
1722:
1731: -- then get the max row number and increment it by 1
1732: -- max row number is for this testclassname only
1733:
1734: select max(rownumber) into v_rownumber
1735: from jtf_diagnostic_arg where testclassname = p_test_class_name
1736: and groupname = p_group_name and appid = p_appid;
1737:
1738: if sql%notfound or v_rownumber = 0 then
1739: v_rownumber := 1;
1741: end if;
1742:
1743: -- now get the max valuesetnumber and increment it by 1
1744:
1745: select JTF_DIAGNOSTIC_ARG_VAL_SET_S.nextval
1746: into v_valsetnumber from dual;
1747:
1748: -- select max(valuesetnumber) into v_valsetnumber
1749: -- from jtf_diagnostic_arg;
1745: select JTF_DIAGNOSTIC_ARG_VAL_SET_S.nextval
1746: into v_valsetnumber from dual;
1747:
1748: -- select max(valuesetnumber) into v_valsetnumber
1749: -- from jtf_diagnostic_arg;
1750:
1751: -- if sql%notfound or v_valsetnumber = 0 then
1752: -- v_valsetnumber := 1;
1753: -- else v_valsetnumber := v_valsetnumber + 1;
1757: V_SOURCETYPE := 1;
1758: loop
1759: if p_arg_names.exists(v_sourcetype) then
1760:
1761: insert into jtf_diagnostic_arg(
1762: SEQUENCE, TESTCLASSNAME, GROUPNAME,
1763: APPID, ARGNAME, ARGVALUE,
1764: ARG_DATE_VALUE, ARG_NUMBER_VALUE,
1765: ROWNUMBER, VALUESETNUMBER, OBJECT_VERSION_NUMBER,
1765: ROWNUMBER, VALUESETNUMBER, OBJECT_VERSION_NUMBER,
1766: CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
1767: LAST_UPDATE_LOGIN, CREATION_DATE)
1768: values(
1769: JTF_DIAGNOSTIC_ARG_S.NEXTVAL, p_test_class_name, p_group_name,
1770: p_appid, p_arg_names(v_sourcetype), p_arg_values(v_sourcetype),
1771: p_arg_dates(v_sourcetype), p_arg_numbers(v_sourcetype),
1772: decode(v_rownumber, null, 1, v_rownumber), v_valsetnumber, 1,
1773: p_lubid, SYSDATE, p_lubid,
1800:
1801: BEGIN
1802:
1803: -- if application, then is the application
1804: -- registered in the jtf_diagnostic_app table
1805: -- if group, then is the group part of the application
1806:
1807: if p_sourcetype = 1 then
1808: select count(*) into v_data_found from jtf_diagnostic_app
1804: -- registered in the jtf_diagnostic_app table
1805: -- if group, then is the group part of the application
1806:
1807: if p_sourcetype = 1 then
1808: select count(*) into v_data_found from jtf_diagnostic_app
1809: where appid = p_sourceid;
1810: elsif p_sourcetype = 2 then
1811: select count(*) into v_data_found from jtf_diagnostic_group
1812: where appid = p_sourceappid and
1807: if p_sourcetype = 1 then
1808: select count(*) into v_data_found from jtf_diagnostic_app
1809: where appid = p_sourceid;
1810: elsif p_sourcetype = 2 then
1811: select count(*) into v_data_found from jtf_diagnostic_group
1812: where appid = p_sourceappid and
1813: groupname = p_sourceid;
1814: else
1815: raise_application_error(-20000, 'Invalid data type received');
1860: -- a valid application registered in the diagnostic
1861: -- framework
1862:
1863: if P_SOURCETYPE = 1 then
1864: select sequence into v_data_found from jtf_diagnostic_app
1865: where appid = P_PREREQID(v_index)
1866: and rownum <= 1;
1867: elsif p_sourcetype = 2 then
1868: select sequence into v_data_found from jtf_diagnostic_group
1864: select sequence into v_data_found from jtf_diagnostic_app
1865: where appid = P_PREREQID(v_index)
1866: and rownum <= 1;
1867: elsif p_sourcetype = 2 then
1868: select sequence into v_data_found from jtf_diagnostic_group
1869: where groupname = P_PREREQID(v_index)
1870: and appid = p_sourceappid
1871: and rownum <= 1;
1872:
1881: -- if reached this far, great. the record is valid and
1882: -- we can insert the record in the table
1883: -- need to complete the insert statement
1884:
1885: insert into jtf_diagnostic_prereq
1886: (SEQUENCE, SOURCEID, PREREQID,
1887: SOURCEAPPID, TYPE, OBJECT_VERSION_NUMBER,
1888: CREATED_BY, LAST_UPDATE_DATE,
1889: LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE)
1887: SOURCEAPPID, TYPE, OBJECT_VERSION_NUMBER,
1888: CREATED_BY, LAST_UPDATE_DATE,
1889: LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE)
1890: values
1891: (JTF_DIAGNOSTIC_PREREQ_S.NEXTVAL, P_SOURCEID,
1892: P_PREREQID(v_index), P_SOURCEAPPID, P_SOURCETYPE,
1893: 1, P_LUBID, SYSDATE, P_LUBID,
1894: P_LUBID, SYSDATE);
1895:
1937:
1938: BEGIN
1939:
1940: select count(*) into v_data_found
1941: from jtf_diagnostic_group
1942: where appid = p_appid
1943: and groupname = p_groupname;
1944:
1945: if v_data_found = 0 or sql%notfound then
1960:
1961: v_data_found := 1;
1962:
1963: select count(*) into v_data_found
1964: from jtf_diagnostic_group
1965: where appid = p_appid
1966: and groupname = p_newgroupname;
1967:
1968: if v_data_found > 0 then
1971: end if;
1972:
1973:
1974: -- if flow of control reaches here, it is alright to
1975: -- rename the group across jtf_diagnostic_group,
1976: -- jtf_diagnostic_test, jtf_diagnostic_arg, jtf_diagnostic_prereq
1977:
1978:
1979: update jtf_diagnostic_group
1972:
1973:
1974: -- if flow of control reaches here, it is alright to
1975: -- rename the group across jtf_diagnostic_group,
1976: -- jtf_diagnostic_test, jtf_diagnostic_arg, jtf_diagnostic_prereq
1977:
1978:
1979: update jtf_diagnostic_group
1980: set groupname = p_newgroupname,
1975: -- rename the group across jtf_diagnostic_group,
1976: -- jtf_diagnostic_test, jtf_diagnostic_arg, jtf_diagnostic_prereq
1977:
1978:
1979: update jtf_diagnostic_group
1980: set groupname = p_newgroupname,
1981: OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
1982: LAST_UPDATE_DATE = SYSDATE,
1983: LAST_UPDATED_BY = P_LUBID
1983: LAST_UPDATED_BY = P_LUBID
1984: where groupname = p_groupname
1985: and appid = p_appid;
1986:
1987: update jtf_diagnostic_test
1988: set groupname = p_newgroupname,
1989: OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
1990: LAST_UPDATE_DATE = SYSDATE,
1991: LAST_UPDATED_BY = P_LUBID
1991: LAST_UPDATED_BY = P_LUBID
1992: where groupname = p_groupname
1993: and appid = p_appid;
1994:
1995: update jtf_diagnostic_arg
1996: set groupname = p_newgroupname,
1997: OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
1998: LAST_UPDATE_DATE = SYSDATE,
1999: LAST_UPDATED_BY = P_LUBID
2002:
2003: -- rename the sourceid and
2004: -- the prereqid
2005:
2006: update jtf_diagnostic_prereq
2007: set sourceid = p_newgroupname,
2008: OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
2009: LAST_UPDATE_DATE = SYSDATE,
2010: LAST_UPDATED_BY = P_LUBID
2010: LAST_UPDATED_BY = P_LUBID
2011: where sourceid = p_groupname
2012: and sourceappid = p_appid;
2013:
2014: update jtf_diagnostic_prereq
2015: set prereqid = p_newgroupname,
2016: OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
2017: LAST_UPDATE_DATE = SYSDATE,
2018: LAST_UPDATED_BY = P_LUBID
2018: LAST_UPDATED_BY = P_LUBID
2019: where prereqid = p_groupname
2020: and sourceappid = p_appid;
2021:
2022: update jtf_diagnostic_decl_test_steps
2023: set groupname = p_newgroupname,
2024: OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
2025: LAST_UPDATE_DATE = SYSDATE,
2026: LAST_UPDATED_BY = P_LUBID
2026: LAST_UPDATED_BY = P_LUBID
2027: where groupname = p_groupname
2028: and appid = p_appid;
2029:
2030: update jtf_diagnostic_decl_step_cols
2031: set groupname = p_newgroupname,
2032: OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
2033: LAST_UPDATE_DATE = SYSDATE,
2034: LAST_UPDATED_BY = P_LUBID
2088:
2089: begin
2090: select LAST_UPDATED_BY, LAST_UPDATE_DATE
2091: into db_luby, db_ludate
2092: from jtf_diagnostic_app
2093: where appid = p_appid;
2094:
2095: -- Update record only as per standard
2096:
2102: p_cust_mode))
2103: then*/
2104: -- seed data must not be changed by customers.Hence overwriting data always
2105: -- so that it covers up any changes by mistake
2106: update jtf_diagnostic_app
2107: set last_updated_by = f_luby,
2108: last_update_date = f_ludate,
2109: object_version_number = object_version_number + 1,
2110: security_group_id = to_number(P_SEC_GRP_ID)
2116:
2117: when no_data_found then
2118: -- Record doesn't exist - insert in all cases
2119:
2120: insert into jtf_diagnostic_app(
2121: sequence,
2122: appid,
2123: object_version_number,
2124: created_by,
2127: last_update_login,
2128: creation_date,
2129: security_group_id)
2130: values(
2131: jtf_diagnostic_app_s.nextval,
2132: p_appid,
2133: 1,
2134: f_luby,
2135: f_ludate,
2183:
2184: begin
2185: select LAST_UPDATED_BY, LAST_UPDATE_DATE
2186: into db_luby, db_ludate
2187: from jtf_diagnostic_group
2188: where appid = p_appid and
2189: groupname = p_groupname;
2190:
2191: -- Update record only as per standard
2198: p_cust_mode))
2199: then*/
2200: -- seed data must not be changed by customers.Hence overwriting data always
2201: -- so that it covers up any changes by mistake
2202: update jtf_diagnostic_group
2203: set sensitivity = v_sensitivity,
2204: last_updated_by = f_luby,
2205: last_update_date = f_ludate,
2206: object_version_number = object_version_number + 1,
2214: when no_data_found then
2215: -- Record doesn't exist - insert in all cases
2216:
2217: select nvl(max(ordernumber)+1,1) into v_num
2218: from jtf_diagnostic_group where
2219: appid = p_appid;
2220:
2221: insert into jtf_diagnostic_group(
2222: sequence,
2217: select nvl(max(ordernumber)+1,1) into v_num
2218: from jtf_diagnostic_group where
2219: appid = p_appid;
2220:
2221: insert into jtf_diagnostic_group(
2222: sequence,
2223: groupname,
2224: appid,
2225: sensitivity,
2231: last_updated_by,
2232: creation_date,
2233: security_group_id)
2234: values(
2235: jtf_diagnostic_group_s.nextval,
2236: p_groupname,
2237: p_appid,
2238: v_sensitivity,
2239: v_num,
2291: instr(TESTCLASSNAME,'.',1,2)+1,
2292: instr(TESTCLASSNAME,'.',1,3) -
2293: instr(TESTCLASSNAME,'.',1,2) - 1),
2294: testclassname
2295: from JTF_DIAGNOSTIC_TEST
2296: where GROUPNAME = l_groupname
2297: and APPID = l_appid
2298: and testclassname <> l_testclassname
2299: and substr(TESTCLASSNAME,
2332:
2333: begin
2334: select LAST_UPDATED_BY, LAST_UPDATE_DATE
2335: into db_luby, db_ludate
2336: from jtf_diagnostic_test
2337: where appid = p_appid and
2338: groupname = p_groupname
2339: and testclassname = p_testclassname;
2340:
2352:
2353: -- if valid_applications is not null, insert valid_applications in to table
2354: -- else ignore the valid_applications while inserting
2355: if P_VALID_APPLICATIONS is not null and P_VALID_APPLICATIONS <> empty_clob() then
2356: update jtf_diagnostic_test
2357: set last_updated_by = f_luby,
2358: last_update_date = sysdate,--f_ludate,
2359: object_version_number = object_version_number + 1,
2360: TOTALARGUMENTROWS = p_TOTALARGUMENTROWS,
2367: where appid = p_appid and groupname = p_groupname
2368: and testclassname = p_testclassname;
2369: else
2370:
2371: update jtf_diagnostic_test
2372: set last_updated_by = f_luby,
2373: last_update_date = sysdate,--f_ludate,
2374: object_version_number = object_version_number + 1,
2375: TOTALARGUMENTROWS = p_TOTALARGUMENTROWS,
2420:
2421: if DOINSERT then
2422: --6599133
2423: select MAX(ordernumber) into v_num
2424: from jtf_diagnostic_test where appid = p_appid
2425: and groupname = p_groupname;
2426:
2427: if sql%notfound or v_num = 0 or v_num is null then
2428: v_num := 1;
2431:
2432: -- if valid_applications is not null, insert valid_applications in to table
2433: -- else ignore the valid_applications while inserting
2434: if P_VALID_APPLICATIONS is not null and P_VALID_APPLICATIONS <> empty_clob() then
2435: insert into jtf_diagnostic_test(
2436: SEQUENCE,
2437: GROUPNAME,
2438: APPID,
2439: ORDERNUMBER,
2451: valid_applications,
2452: end_date,
2453: test_metadata)
2454: values(
2455: jtf_diagnostic_test_s.nextval,
2456: p_groupname,
2457: p_appid,
2458: v_num,
2459: p_testtype,
2470: xmltype(P_VALID_APPLICATIONS),
2471: f_end_date,
2472: f_meta_data);
2473: else
2474: insert into jtf_diagnostic_test(
2475: SEQUENCE,
2476: GROUPNAME,
2477: APPID,
2478: ORDERNUMBER,
2489: security_group_id,
2490: end_date,
2491: test_metadata)
2492: values(
2493: jtf_diagnostic_test_s.nextval,
2494: p_groupname,
2495: p_appid,
2496: v_num,
2497: p_testtype,
2555:
2556: begin
2557: select LAST_UPDATED_BY, LAST_UPDATE_DATE
2558: into db_luby, db_ludate
2559: from jtf_diagnostic_arg
2560: where appid = p_appid
2561: and groupname = p_groupname
2562: and testclassname = p_testclassname
2563: and argname = p_argname
2573: p_cust_mode))
2574: then*/
2575: -- seed data must not be changed by customers.Hence overwriting data always
2576: -- so that it covers up any changes by mistake
2577: update jtf_diagnostic_arg
2578: set last_updated_by = f_luby,
2579: argvalue = p_argvalue,
2580: VALUESETNUMBER = p_VALUESETNUMBER,
2581: last_update_date = f_ludate,
2592:
2593: when no_data_found then
2594: -- Record doesn't exist - insert in all cases
2595:
2596: insert into jtf_diagnostic_arg(
2597: SEQUENCE,
2598: TESTCLASSNAME,
2599: GROUPNAME,
2600: APPID,
2609: LAST_UPDATE_LOGIN,
2610: CREATION_DATE,
2611: SECURITY_GROUP_ID)
2612: values(
2613: jtf_diagnostic_arg_s.nextval,
2614: p_testclassname,
2615: p_groupname,
2616: p_appid,
2617: p_argname,
2669:
2670: begin
2671: select LAST_UPDATED_BY, LAST_UPDATE_DATE
2672: into db_luby, db_ludate
2673: from jtf_diagnostic_prereq
2674: where sourceid = p_sourceid
2675: and prereqid = p_prereqid
2676: and SOURCEAPPID = p_SOURCEAPPID
2677: and type = p_type;
2686: p_cust_mode))
2687: then*/
2688: -- seed data must not be changed by customers.Hence overwriting data always
2689: -- so that it covers up any changes by mistake
2690: update jtf_diagnostic_prereq
2691: set last_updated_by = f_luby,
2692: last_update_date = f_ludate,
2693: object_version_number = object_version_number + 1,
2694: security_group_id = to_number(P_SEC_GRP_ID)
2703:
2704: when no_data_found then
2705: -- Record doesn't exist - insert in all cases
2706:
2707: insert into jtf_diagnostic_prereq(
2708: SEQUENCE,
2709: SOURCEID,
2710: PREREQID,
2711: SOURCEAPPID,
2717: LAST_UPDATE_LOGIN,
2718: CREATION_DATE,
2719: SECURITY_GROUP_ID)
2720: values(
2721: jtf_diagnostic_prereq_s.nextval,
2722: p_sourceid,
2723: p_prereqid,
2724: p_sourceappid,
2725: p_type,
2786:
2787: begin
2788: select LAST_UPDATED_BY, LAST_UPDATE_DATE
2789: into db_luby, db_ludate
2790: from JTF_DIAGNOSTIC_DECL_TEST_STEPS
2791: where APPID = P_APPID
2792: and GROUPNAME = P_GROUPNAME
2793: and TESTCLASSNAME = P_TESTCLASSNAME
2794: and TESTSTEPNAME = P_TESTSTEPNAME;
2803: p_cust_mode))
2804: then*/
2805: -- seed data must not be changed by customers.Hence overwriting data always
2806: -- so that it covers up any changes by mistake
2807: update JTF_DIAGNOSTIC_DECL_TEST_STEPS
2808: set last_updated_by = f_luby,
2809: last_update_date = f_ludate,
2810: object_version_number = object_version_number + 1,
2811: security_group_id = to_number(P_SECURITY_GROUP_ID),
2835: when no_data_found then
2836: -- Record doesn't exist - insert in all cases
2837:
2838: select MAX(EXECUTION_SEQUENCE) into v_EXECUTION_SEQUENCE
2839: from JTF_DIAGNOSTIC_DECL_TEST_STEPS
2840: where APPID = P_APPID
2841: and GROUPNAME = P_GROUPNAME
2842: and TESTCLASSNAME = P_TESTCLASSNAME;
2843:
2845: v_EXECUTION_SEQUENCE := 1;
2846: else v_EXECUTION_SEQUENCE := v_EXECUTION_SEQUENCE + 1;
2847: end if;
2848:
2849: insert into JTF_DIAGNOSTIC_DECL_TEST_STEPS(
2850: APPID,
2851: GROUPNAME,
2852: TESTCLASSNAME,
2853: TESTSTEPNAME,
2944:
2945: begin
2946: select LAST_UPDATED_BY, LAST_UPDATE_DATE
2947: into db_luby, db_ludate
2948: from jtf_diagnostic_decl_step_cols
2949: where APPID = P_APPID
2950: and GROUPNAME = P_GROUPNAME
2951: and TESTCLASSNAME = P_TESTCLASSNAME
2952: and TESTSTEPNAME = P_TESTSTEPNAME
2962: p_cust_mode))
2963: then*/
2964: -- seed data must not be changed by customers.Hence overwriting data always
2965: -- so that it covers up any changes by mistake
2966: update jtf_diagnostic_decl_step_cols
2967: set last_updated_by = f_luby,
2968: last_update_date = f_ludate,
2969: object_version_number = object_version_number + 1,
2970: security_group_id = to_number(P_SECURITY_GROUP_ID),
2983:
2984: when no_data_found then
2985: -- Record doesn't exist - insert in all cases
2986:
2987: insert into jtf_diagnostic_decl_step_cols(
2988: APPID,
2989: GROUPNAME,
2990: TESTCLASSNAME,
2991: TESTSTEPNAME,
3061:
3062: begin
3063: select LAST_UPDATED_BY, LAST_UPDATE_DATE
3064: into db_luby, db_ludate
3065: from jtf_diagnostic_alert
3066: where appid = p_appid and
3067: groupname = p_groupname
3068: and testclassname = p_testclassname
3069: and type = p_type;
3078: p_cust_mode))
3079: then*/
3080: -- seed data must not be changed by customers.Hence overwriting data always
3081: -- so that it covers up any changes by mistake
3082: update jtf_diagnostic_alert
3083: set last_updated_by = f_luby,
3084: last_update_date = f_ludate,
3085: object_version_number = object_version_number + 1,
3086: LEVEL_VALUE = to_number(p_LEVEL_VALUE),
3095:
3096: when no_data_found then
3097: -- Record doesn't exist - insert in all cases
3098:
3099: insert into jtf_diagnostic_alert(
3100: SEQUENCE,
3101: GROUPNAME,
3102: APPID,
3103: TYPE,
3110: LAST_UPDATE_LOGIN,
3111: CREATION_DATE,
3112: security_group_id)
3113: values(
3114: jtf_diagnostic_alert_s.nextval,
3115: p_groupname,
3116: p_appid,
3117: p_type,
3118: p_testclassname,
3171: -- as test table is updated before kb table, this should work
3172: begin
3173: select SEQUENCE
3174: into seq
3175: from jtf_diagnostic_test
3176: where appid = p_appid and
3177: groupname = p_groupname and
3178: testclassname = p_testclassname;
3179: end;
3194:
3195: begin
3196: select LAST_UPDATED_BY, LAST_UPDATE_DATE
3197: into db_luby, db_ludate
3198: from jtf_diagnostic_kb
3199: where sequence = seq;
3200:
3201: -- Update record only as per standard
3202:
3208: p_cust_mode))
3209: then*/
3210: -- seed data must not be changed by customers.Hence overwriting data always
3211: -- so that it covers up any changes by mistake
3212: update jtf_diagnostic_kb
3213: set last_updated_by = f_luby,
3214: last_update_date = f_ludate,
3215: object_version_number = object_version_number + 1,
3216: USER_TEST_NAME = P_USER_TEST_NAME,
3239:
3240: when no_data_found then
3241: -- Record doesn't exist - insert in all cases
3242:
3243: insert into jtf_diagnostic_kb(
3244: SEQUENCE,
3245: USER_TEST_NAME,
3246: METALINK_NOTE,
3247: COMPETENCY,
3342:
3343: begin
3344: select LAST_UPDATED_BY, LAST_UPDATE_DATE
3345: into db_luby, db_ludate
3346: from jtf_diagnostic_testset
3347: where name = p_name;
3348:
3349: -- Update record only as per standard
3350:
3387: P_LAST_UPDATED_BY IN NUMBER,
3388: P_LAST_UPDATED_DATE IN DATE) IS
3389:
3390: BEGIN
3391: update jtf_diagnostic_testset
3392: set description = P_DESCRIPTION,
3393: xml = XMLTYPE(P_XML),
3394: last_updated_by = P_LAST_UPDATED_BY,
3395: last_update_date = P_LAST_UPDATED_DATE
3418: P_LAST_UPDATED_BY IN NUMBER,
3419: P_LAST_UPDATED_DATE IN DATE) IS
3420:
3421: BEGIN
3422: insert into jtf_diagnostic_testset
3423: (NAME, DESCRIPTION, XML, CREATED_BY, CREATION_DATE,
3424: LAST_UPDATE_LOGIN, LAST_UPDATED_BY, LAST_UPDATE_DATE)
3425: values
3426: ( P_NAME , P_DESCRIPTION, XMLType(P_XML), P_CREATED_BY, P_CREATION_DATE,
3456: P_LAST_UPDATED_DATE IN DATE) IS
3457:
3458: BEGIN
3459:
3460: UPDATE JTF_DIAGNOSTIC_TEST
3461: SET VALID_APPLICATIONS = XMLTYPE(P_VALIDAPPS),
3462: LAST_UPDATED_BY = P_LAST_UPDATED_BY,
3463: LAST_UPDATE_DATE = P_LAST_UPDATED_DATE
3464: WHERE APPID = P_APPSHORTNAME
3629: ) IS
3630: F_END_DATE date;
3631: BEGIN
3632: IF P_END_DATE IS NOT NULL THEN
3633: --F_END_DATE := to_date(P_END_DATE, JTF_DIAGNOSTIC_ADAPTUTIL.GET_SITE_DATE_FORMAT());
3634: F_END_DATE := P_END_DATE;
3635: END IF;
3636: UPDATE jtf_diagnostic_test
3637: SET end_date = F_END_DATE,
3632: IF P_END_DATE IS NOT NULL THEN
3633: --F_END_DATE := to_date(P_END_DATE, JTF_DIAGNOSTIC_ADAPTUTIL.GET_SITE_DATE_FORMAT());
3634: F_END_DATE := P_END_DATE;
3635: END IF;
3636: UPDATE jtf_diagnostic_test
3637: SET end_date = F_END_DATE,
3638: OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
3639: LAST_UPDATE_DATE = SYSDATE,
3640: LAST_UPDATED_BY = P_LUBID
3648: END IF;
3649:
3650:
3651: END UPDATE_TEST_END_DATE;
3652: END JTF_DIAGNOSTIC;