[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select distinct appid from jtf_diagnostic_app;
P_GRP_LAST_UPDATED_BY OUT NOCOPY JTF_NUMBER_TABLE) is
V_SIZE NUMBER;
/* 5953806 - changed select to get last_updated_by instead of created_by */
cursor grouplist is
select groupName, sensitivity, last_updated_by
from jtf_diagnostic_group
where appID like P_APPNAME
order by orderNumber;
P_GRP_LAST_UPDATED_BY := JTF_NUMBER_TABLE();
P_GRP_LAST_UPDATED_BY.extend;
P_GRP_LAST_UPDATED_BY(V_SIZE) := x.last_updated_by;
P_TEST_LAST_UPDATED_BY OUT NOCOPY JTF_NUMBER_TABLE
) IS
V_SIZE number;
/* 5953806 - changed select to get last_updated_by instead of created_by */
cursor testlist is
select testClassName, testtype, totalargumentrows, sensitivity, last_updated_by
from jtf_diagnostic_test
where appID like P_APPNAME
and groupname like P_GROUPNAME
order by orderNumber;
P_TEST_LAST_UPDATED_BY := JTF_NUMBER_TABLE();
P_TEST_LAST_UPDATED_BY.EXTEND;
P_TEST_LAST_UPDATED_BY(V_SIZE) := X.LAST_UPDATED_BY;
P_TEST_LAST_UPDATED_BY OUT NOCOPY JTF_NUMBER_TABLE
) IS
V_SIZE number;
/* 5953806 - changed select to get last_updated_by instead of created_by */
cursor testlist is
select testClassName, testtype, totalargumentrows, last_updated_by
from jtf_diagnostic_test
where appID like P_APPNAME
and groupname like P_GROUPNAME
order by orderNumber;
P_TEST_LAST_UPDATED_BY := JTF_NUMBER_TABLE();
P_TEST_LAST_UPDATED_BY.EXTEND;
P_TEST_LAST_UPDATED_BY(V_SIZE) := X.LAST_UPDATED_BY;
select argname, argvalue, rownumber, valuesetnumber
from jtf_diagnostic_arg
where TestClassName = P_TESTCLASSNAME
and groupname = P_GROUPNAME
and appid = P_APPID
order by rownumber;
select prereqid, type
from jtf_diagnostic_prereq
where sourceid = P_APP_OR_GROUP_NAME
and sourceappid = P_APPNAME;
-- select a.application_name into V_TEMP_NAME
-- from fnd_application_tl a, fnd_application b
-- where b.APPLICATION_SHORT_NAME = x.PREREQID
-- and b.APPLICATION_ID = a.APPLICATION_ID
-- and a.language = userenv('LANG');
procedure UPDATE_GROUP_SENSITIVITY(
P_APP_NAME IN VARCHAR2,
P_GROUP_NAME IN VARCHAR2,
P_GRP_SENSITIVITY IN NUMBER,
P_LUBID IN NUMBER
) IS
BEGIN
UPDATE jtf_diagnostic_group
SET sensitivity = P_GRP_SENSITIVITY,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = P_LUBID
WHERE appid = P_APP_NAME AND
groupname = P_GROUP_NAME;
RAISE_APPLICATION_ERROR(-20000,'Cant Update, Record Not Found');
END UPDATE_GROUP_SENSITIVITY;
procedure UPDATE_GROUP_SENSITIVITY(
P_APP_NAME IN VARCHAR2,
P_GROUP_NAME IN VARCHAR2,
P_GRP_SENSITIVITY IN NUMBER
) IS
BEGIN
UPDATE_GROUP_SENSITIVITY(P_APP_NAME,
P_GROUP_NAME,
P_GRP_SENSITIVITY,
UID);
END UPDATE_GROUP_SENSITIVITY;
procedure UPDATE_TEST_SENSITIVITY(
P_APP_NAME IN VARCHAR2,
P_GROUP_NAME IN VARCHAR2,
P_TEST_NAME IN VARCHAR2,
P_TST_SENSITIVITY IN NUMBER,
P_LUBID IN NUMBER
) IS
BEGIN
UPDATE jtf_diagnostic_test
SET sensitivity = P_TST_SENSITIVITY,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = P_LUBID
WHERE appid = P_APP_NAME AND
groupname = P_GROUP_NAME AND
testclassname = P_TEST_NAME;
RAISE_APPLICATION_ERROR(-20000,'Cant Update, Record Not
Found');
END UPDATE_TEST_SENSITIVITY;
procedure DELETE_APP(
P_APP_NAME IN VARCHAR2
) IS
BEGIN
delete from jtf_diagnostic_app
where appid = P_APP_NAME;
delete from jtf_diagnostic_group
where appid = P_APP_NAME;
delete from jtf_diagnostic_test
where appid = P_APP_NAME;
delete from jtf_diagnostic_arg
where appid = P_APP_NAME;
delete from jtf_diagnostic_prereq
where sourceappid = P_APP_NAME;
delete from jtf_diagnostic_prereq
where prereqid = P_APP_NAME;
END DELETE_APP;
procedure DELETE_GROUP(
P_APP_NAME IN VARCHAR2,
P_GROUP_NAME IN VARCHAR2
) IS
V_ORDERNUM jtf_diagnostic_group.ordernumber%TYPE;
select testclassname, teststepname from jtf_diagnostic_decl_test_steps
where appid = P_APP_NAME
and groupname = P_GROUP_NAME;
select testclassname from jtf_diagnostic_test
where appid = P_APP_NAME
and groupname = P_GROUP_NAME;
select groupname, ordernumber, object_version_number
from jtf_diagnostic_group
where appid = P_APP_NAME
and ordernumber > c_ordernumber
order by ordernumber;
select distinct count(*) into V_ORDERNUM
from jtf_diagnostic_group
where APPID = P_APP_NAME and groupname = P_GROUP_NAME;
select distinct ordernumber into V_ORDERNUM
from jtf_diagnostic_group
where APPID = P_APP_NAME and groupname = P_GROUP_NAME;
delete from jtf_diagnostic_group
where groupname = P_GROUP_NAME
and appid = P_APP_NAME;
update jtf_diagnostic_group
set ordernumber = (l_ordernumber - 1),
OBJECT_VERSION_NUMBER = l_OBJECT_VERSION_NUMBER + 1,
LAST_UPDATE_DATE = SYSDATE
where groupname = l_groupname
and appid = P_APP_NAME;
delete_test(P_APP_NAME, P_GROUP_NAME, x.testclassname);
delete from jtf_diagnostic_prereq
where sourceid = P_GROUP_NAME
and sourceappid = P_APP_NAME;
delete_decl_test_step(P_APP_NAME, P_GROUP_NAME, x.testclassname, x.teststepname);
END DELETE_GROUP;
procedure DELETE_TEST(
P_APP_NAME IN VARCHAR2,
P_GROUP_NAME IN VARCHAR2,
P_TEST_CLASS_NAME IN VARCHAR2
) IS
V_ORDERNUM jtf_diagnostic_test.ordernumber%TYPE;
select testclassname, ordernumber, object_version_number
from jtf_diagnostic_test
where appid = P_APP_NAME
and groupname = P_GROUP_NAME
and ordernumber > c_ordernumber
order by ordernumber;
select count(*) into V_ORDERNUM
from jtf_diagnostic_test
where APPID = P_APP_NAME
and groupname = P_GROUP_NAME
and testclassname = P_TEST_CLASS_NAME
and rownum <= 1;
select sequence into V_SEQUENCE
from jtf_diagnostic_test
where APPID = P_APP_NAME
and groupname = P_GROUP_NAME
and testclassname = P_TEST_CLASS_NAME
and rownum <= 1;
delete from jtf_diagnostic_kb where
sequence = V_SEQUENCE;
-- they should all be deleted since a group should have the same testclassname
-- appearing once in it
select ordernumber into V_ORDERNUM
from jtf_diagnostic_test
where APPID = P_APP_NAME
and groupname = P_GROUP_NAME
and testclassname = P_TEST_CLASS_NAME
and rownum <= 1;
delete from jtf_diagnostic_test
where groupname = P_GROUP_NAME
and appid = P_APP_NAME
and testclassname = P_TEST_CLASS_NAME;
update jtf_diagnostic_test
set ordernumber = (l_ordernumber - 1),
OBJECT_VERSION_NUMBER = l_OBJECT_VERSION_NUMBER + 1,
LAST_UPDATE_DATE = SYSDATE
where testclassname = l_testclassname
and appid = P_APP_NAME
and groupname = P_GROUP_NAME;
delete from jtf_diagnostic_arg
where groupname = P_GROUP_NAME
and appid = P_APP_NAME
and testclassname = P_TEST_CLASS_NAME;
delete from jtf_diagnostic_decl_test_steps
where groupname = P_GROUP_NAME
and appid = P_APP_NAME
and testclassname = P_TEST_CLASS_NAME;
delete from jtf_diagnostic_decl_step_cols
where groupname = P_GROUP_NAME
and appid = P_APP_NAME
and testclassname = P_TEST_CLASS_NAME;
END DELETE_TEST;
procedure DELETE_ALL_ARGS_FOR_TEST(
P_APP_NAME IN VARCHAR2,
P_GROUP_NAME IN VARCHAR2,
P_TEST_CLASS_NAME IN VARCHAR2
) IS
BEGIN
delete from jtf_diagnostic_arg
where testclassname = P_TEST_CLASS_NAME
and groupname = P_GROUP_NAME
and appid = P_APP_NAME;
END DELETE_ALL_ARGS_FOR_TEST;
procedure DELETE_ARG_SET(
P_APP_NAME IN VARCHAR2,
P_GROUP_NAME IN VARCHAR2,
P_TEST_CLASS_NAME IN VARCHAR2,
P_ARG_ROW_NUM IN NUMBER
) IS
BEGIN
-- remove the argument combination corresponding to the
-- testcase where we get the rownumber from the UI / Java layer
-- where each rownumber corresponds to one combination of
-- arguments which we will just delete
delete from jtf_diagnostic_arg
where testclassname = P_TEST_CLASS_NAME
and groupname = P_GROUP_NAME
and appid = P_APP_NAME
and rownumber = P_ARG_ROW_NUM;
-- If a row was deleted, then bump down the
-- number of argument rows for jtf_diagnostic_test
IF NOT SQL%NOTFOUND THEN
update jtf_diagnostic_test
set totalargumentrows = (totalargumentrows - 1),
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
LAST_UPDATE_DATE = SYSDATE
where testclassname = P_TEST_CLASS_NAME
and groupname = P_GROUP_NAME
and appid = P_APP_NAME;
update jtf_diagnostic_arg
set rownumber = (rownumber - 1),
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
LAST_UPDATE_DATE = SYSDATE
where testclassname = P_TEST_CLASS_NAME
and groupname = P_GROUP_NAME
and appid = P_APP_NAME
and rownumber > P_ARG_ROW_NUM;
END DELETE_ARG_SET;
procedure DELETE_DECL_TEST_STEP(
P_APPID IN VARCHAR2,
P_GROUPNAME IN VARCHAR2,
P_TESTCLASSNAME IN VARCHAR2,
P_TESTSTEPNAME IN VARCHAR2) is
V_ORDERNUM number;
select teststepname, execution_sequence
from jtf_diagnostic_decl_test_steps
where appid = P_APPID
and groupname = P_GROUPNAME
and testclassname = P_TESTCLASSNAME
and execution_sequence > c_ordernumber
order by execution_sequence;
select execution_sequence into v_ordernum
from jtf_diagnostic_decl_test_steps
where appid = P_APPID and groupname = P_GROUPNAME
and testclassname = P_TESTCLASSNAME and teststepname = P_TESTSTEPNAME;
delete from jtf_diagnostic_decl_test_steps
where appid = P_APPID
and groupname = P_GROUPNAME
and testclassname = P_TESTCLASSNAME
and teststepname = P_TESTSTEPNAME;
update jtf_diagnostic_decl_test_steps
set execution_sequence = (l_ordernumber - 1),
LAST_UPDATE_DATE = SYSDATE
where teststepname = l_teststepname
and appid = P_APPID
and groupname = P_GROUPNAME
and testclassname = P_TESTCLASSNAME;
delete from jtf_diagnostic_decl_test_steps
where appid = P_APPID
and groupname = P_GROUPNAME
and testclassname = P_TESTCLASSNAME
and teststepname = P_TESTSTEPNAME;
delete from jtf_diagnostic_decl_step_cols
where groupname = P_GROUPNAME
and appid = P_APPID
and testclassname = P_TESTCLASSNAME;
END DELETE_DECL_TEST_STEP;
procedure UPDATE_GROUP_SEQ(
P_APPID IN VARCHAR2,
P_GROUPNAMES IN JTF_VARCHAR2_TABLE_4000,
P_LUBID IN NUMBER) is
v_numofrows NUMBER;
SELECT COUNT(*)
INTO v_numofrows
FROM jtf_diagnostic_group
WHERE appid = P_APPID;
--RAISE_APPLICATION_ERROR(-20000, 'Cant Update - Mismatch');
RAISE_APPLICATION_ERROR(-20000, 'UPDATE_GROUP_SEQ(): Cannot Update -
Mismatch. P_APPID=' || P_APPID|| ' ; v_numofrows='||v_numofrows ||' ;
UPDATE jtf_diagnostic_group
SET ordernumber = v_index * -1
WHERE appid = P_APPID AND
ordernumber = v_index;
UPDATE jtf_diagnostic_group
SET ordernumber = v_index,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
LAST_UPDATE_DATE = SYSDATE
--LAST_UPDATED_BY = P_LUBID
WHERE appid = P_APPID AND
groupname = P_GROUPNAMES(v_index);
RAISE_APPLICATION_ERROR(-20000,'Cant Update, Record Not Found');
END UPDATE_GROUP_SEQ;
procedure UPDATE_GROUP_SEQ(
P_APPID IN VARCHAR2,
P_GROUPNAMES IN JTF_VARCHAR2_TABLE_4000
) IS
BEGIN
UPDATE_GROUP_SEQ(P_APPID,
P_GROUPNAMES,
UID);
END UPDATE_GROUP_SEQ;
procedure UPDATE_TEST_SEQ(
P_APPID IN VARCHAR2,
P_GROUPNAME IN VARCHAR2,
P_TESTCLASSNAMES IN JTF_VARCHAR2_TABLE_4000,
P_LUBID IN NUMBER) is
v_numofrows NUMBER;
SELECT COUNT(*)
INTO v_numofrows
FROM jtf_diagnostic_test
WHERE appid = P_APPID AND
groupname = P_GROUPNAME;
--RAISE_APPLICATION_ERROR(-20000, 'Cant Update - Mismatch');
RAISE_APPLICATION_ERROR(-20000, 'UPDATE_TEST_SEQ(): Cannot Update -
Mismatch. P_APPID=' || P_APPID|| ' ; P_GROUPNAME='|| P_GROUPNAME || ' ;v_numofrows='||v_numofrows ||' ;
UPDATE jtf_diagnostic_test
SET ordernumber = v_index * -1
WHERE appid = P_APPID AND
groupname = p_groupname and
ordernumber = v_index;
UPDATE jtf_diagnostic_test
SET OrderNumber = v_index,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
LAST_UPDATE_DATE = SYSDATE
--LAST_UPDATED_BY = P_LUBID
WHERE appid = P_APPID AND
groupname = P_GROUPNAME AND
testclassname = P_TESTCLASSNAMES(v_index);
RAISE_APPLICATION_ERROR(-20000,'Cant Update, Record Not Found');
END UPDATE_TEST_SEQ;
procedure UPDATE_TEST_SEQ(
P_APPID IN VARCHAR2,
P_GROUPNAME IN VARCHAR2,
P_TESTCLASSNAMES IN JTF_VARCHAR2_TABLE_4000
) IS
BEGIN
UPDATE_TEST_SEQ(P_APPID,
P_GROUPNAME,
P_TESTCLASSNAMES,
UID);
END UPDATE_TEST_SEQ;
procedure UPDATE_PREREQS(
P_SOURCEID IN VARCHAR2,
P_SOURCEAPPID IN VARCHAR2,
P_PREREQID IN JTF_VARCHAR2_TABLE_4000,
P_SOURCETYPE IN NUMBER,
P_LUBID IN NUMBER) IS
v_index BINARY_INTEGER := 1;
delete from jtf_diagnostic_prereq
where sourceid = p_sourceid
and sourceappid = p_sourceappid;
PREREQ_INSERTION(P_SOURCEID,P_SOURCEAPPID,P_PREREQID,P_SOURCETYPE,P_LUBID);
END UPDATE_PREREQS;
procedure UPDATE_PREREQS(
P_SOURCEID IN VARCHAR2,
P_SOURCEAPPID IN VARCHAR2,
P_PREREQID IN JTF_VARCHAR2_TABLE_4000,
P_SOURCETYPE IN NUMBER) IS
BEGIN
UPDATE_PREREQS(P_SOURCEID,
P_SOURCEAPPID,
P_PREREQID,
P_SOURCETYPE,
UID);
END UPDATE_PREREQS;
procedure UPDATE_ARG_VALUES(
P_TESTCLASSNAME IN VARCHAR2,
P_GROUPNAME IN VARCHAR2,
P_APPID IN VARCHAR2,
P_ARGNAMES IN JTF_VARCHAR2_TABLE_4000,
P_ARGVALUES IN JTF_VARCHAR2_TABLE_4000,
p_arg_dates in jtf_date_table,
p_arg_numbers in jtf_number_table,
P_ROWNUMBER IN NUMBER,
P_LUBID IN NUMBER) is
v_index BINARY_INTEGER := 1;
update jtf_diagnostic_arg set
argvalue = p_argvalues(v_index),
arg_date_value = p_arg_dates(v_index),
arg_number_value = p_arg_numbers(v_index),
object_version_number = object_version_number + 1,
-- last_updated_by = UID,
last_updated_by = P_LUBID,
last_update_date = sysdate
where argname = p_argnames(v_index)
and rownumber = p_rownumber
and testclassname = p_testclassname
and groupname = p_groupname
and appid = p_appid;
'Invalid data received -- no record found to update');
END UPDATE_ARG_VALUES;
procedure UPDATE_ARG_VALUES(
P_TESTCLASSNAME IN VARCHAR2,
P_GROUPNAME IN VARCHAR2,
P_APPID IN VARCHAR2,
P_ARGNAMES IN JTF_VARCHAR2_TABLE_4000,
P_ARGVALUES IN JTF_VARCHAR2_TABLE_4000,
P_ROWNUMBER IN NUMBER,
P_LUBID IN NUMBER) is
v_index BINARY_INTEGER := 1;
update jtf_diagnostic_arg set
argvalue = p_argvalues(v_index),
object_version_number = object_version_number + 1,
-- last_updated_by = UID,
last_updated_by = P_LUBID,
last_update_date = sysdate
where argname = p_argnames(v_index)
and rownumber = p_rownumber
and testclassname = p_testclassname
and groupname = p_groupname
and appid = p_appid;
'Invalid data received -- no record found to update');
END UPDATE_ARG_VALUES;
procedure UPDATE_ARG_VALUES(
P_TESTCLASSNAME IN VARCHAR2,
P_GROUPNAME IN VARCHAR2,
P_APPID IN VARCHAR2,
P_ARGNAMES IN JTF_VARCHAR2_TABLE_4000,
P_ARGVALUES IN JTF_VARCHAR2_TABLE_4000,
P_ROWNUMBER IN NUMBER
) IS
BEGIN
UPDATE_ARG_VALUES(P_TESTCLASSNAME,
P_GROUPNAME,
P_APPID,
P_ARGNAMES,
P_ARGVALUES,
P_ROWNUMBER,
UID);
END UPDATE_ARG_VALUES;
procedure INSERT_APP(
P_APPID IN VARCHAR2,
P_PREREQID IN JTF_VARCHAR2_TABLE_4000,
P_LUBID IN NUMBER) IS
-- v_asn fnd_application.application_short_name%TYPE;
-- select distinct application_short_name into v_asn
-- from fnd_application
-- where application_short_name = P_APPID
-- and rownum <= 1;
select count(*) into v_sourcetype
from jtf_diagnostic_app
where appid = P_APPID;
insert into jtf_diagnostic_app(
SEQUENCE, APPID, OBJECT_VERSION_NUMBER, CREATED_BY,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE)
values (JTF_DIAGNOSTIC_APP_S.NEXTVAL, P_APPID, 1, P_LUBID,
SYSDATE, P_LUBID, NULL, SYSDATE);
-- is not null in which case call the insertion routine
IF NOT P_PREREQID IS NULL then
V_SOURCETYPE := 1;
PREREQ_INSERTION(P_APPID, P_APPID, P_PREREQID, V_SOURCETYPE, P_LUBID);
END INSERT_APP;
procedure INSERT_APP(
P_APPID IN VARCHAR2,
P_PREREQID IN JTF_VARCHAR2_TABLE_4000
) IS
BEGIN
INSERT_APP(P_APPID,
P_PREREQID,
UID);
END INSERT_APP;
procedure INSERT_GRP(
P_NEW_GROUP IN VARCHAR2,
P_APP IN VARCHAR2,
P_PREREQID IN JTF_VARCHAR2_TABLE_4000,
P_LUBID IN NUMBER) IS
v_groupname jtf_diagnostic_group.groupname%TYPE;
select count(*) into v_sourcetype
from jtf_diagnostic_group
where appid = P_APP and groupname = p_new_group
and rownum <= 1;
select MAX(ordernumber) into v_ordernumber
from jtf_diagnostic_group where appid = p_app;
insert into jtf_diagnostic_group(
SEQUENCE, GROUPNAME,
APPID, ORDERNUMBER,
OBJECT_VERSION_NUMBER, CREATED_BY,
LAST_UPDATE_DATE, LAST_UPDATED_BY,
LAST_UPDATE_LOGIN, CREATION_DATE)
values(
JTF_DIAGNOSTIC_GROUP_S.NEXTVAL, P_NEW_GROUP,
P_APP, DECODE(v_ordernumber,null,1,v_ordernumber),
1, P_LUBID,
SYSDATE, P_LUBID,
NULL, SYSDATE);
-- is not null in which case call the insertion routine
IF NOT P_PREREQID IS NULL then
V_SOURCETYPE := 2;
PREREQ_INSERTION(P_NEW_GROUP, P_APP, P_PREREQID, V_SOURCETYPE);
END INSERT_GRP;
procedure INSERT_GRP(
P_NEW_GROUP IN VARCHAR2,
P_APP IN VARCHAR2,
P_PREREQID IN JTF_VARCHAR2_TABLE_4000
) IS
BEGIN
INSERT_GRP(P_NEW_GROUP,
P_APP,
P_PREREQID,
UID);
END INSERT_GRP;
procedure INSERT_GROUP(
P_NEW_GROUP IN VARCHAR2,
P_APP IN VARCHAR2,
P_PREREQID IN JTF_VARCHAR2_TABLE_4000,
P_SENSITIVITY IN NUMBER,
P_LUBID IN NUMBER) IS
v_groupname jtf_diagnostic_group.groupname%TYPE;
select count(*) into v_sourcetype
from jtf_diagnostic_group
where appid = P_APP and groupname = p_new_group
and rownum <= 1;
select MAX(ordernumber) into v_ordernumber
from jtf_diagnostic_group where appid = p_app;
insert into jtf_diagnostic_group(
SEQUENCE, GROUPNAME,
APPID, ORDERNUMBER,
OBJECT_VERSION_NUMBER, CREATED_BY,
LAST_UPDATE_DATE, LAST_UPDATED_BY,
LAST_UPDATE_LOGIN, CREATION_DATE, SENSITIVITY)
values(
JTF_DIAGNOSTIC_GROUP_S.NEXTVAL, P_NEW_GROUP,
P_APP, DECODE(v_ordernumber,null,1,v_ordernumber),
1, P_LUBID,
SYSDATE, P_LUBID,
NULL, SYSDATE, P_SENSITIVITY);
-- is not null in which case call the insertion routine
IF NOT P_PREREQID IS NULL then
V_SOURCETYPE := 2;
PREREQ_INSERTION(P_NEW_GROUP, P_APP, P_PREREQID, V_SOURCETYPE);
END INSERT_GROUP;
procedure INSERT_GROUP(
P_NEW_GROUP IN VARCHAR2,
P_APP IN VARCHAR2,
P_PREREQID IN JTF_VARCHAR2_TABLE_4000,
P_SENSITIVITY IN NUMBER
) IS
BEGIN
INSERT_GROUP(P_NEW_GROUP,
P_APP,
P_PREREQID,
P_SENSITIVITY,
UID);
END INSERT_GROUP;
select sensitivity into p_sensitivity
from jtf_diagnostic_group
where appid = p_appid and
groupname = p_group_name;
procedure INSERT_TESTCASE(p_testclassname in varchar2,
p_group_name in varchar2,
p_appid in varchar2,
p_test_type in varchar2,
p_sensitivity in number,
p_valid_apps_xml in varchar2,
p_end_date in date default null,
p_meta_data in varchar2,
p_lubid in number) IS
V_SOURCETYPE BINARY_INTEGER := 2;
select count(*) into V_SOURCETYPE
from jtf_diagnostic_test
where appid = p_appid and groupname = p_group_name
and testclassname = p_testclassname
and rownum <= 1;
-- if flow of control reaches here, insert the testcase
-- to the group
-- but first find out the highest number of order of tests
-- in the group and add this to the end. If there are no tests
-- then make sure that this gets the first order number
select MAX(ordernumber) into v_ordernumber
from jtf_diagnostic_test where appid = p_appid
and groupname = p_group_name;
insert into jtf_diagnostic_test(
SEQUENCE, GROUPNAME, APPID,
ORDERNUMBER, TESTTYPE, TESTCLASSNAME,
TOTALARGUMENTROWS,
SENSITIVITY,
OBJECT_VERSION_NUMBER, CREATED_BY,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
CREATION_DATE,VALID_APPLICATIONS,END_DATE,TEST_METADATA)
values(
JTF_DIAGNOSTIC_TEST_S.NEXTVAL, p_group_name, p_appid,
decode(v_ordernumber, null, 1, v_ordernumber),
p_test_type, p_testclassname,
0, l_sensitivity, 1, p_lubid,
SYSDATE, p_lubid, NULL,
SYSDATE,xmltype(p_valid_apps_xml),F_END_DATE,F_META_DATA);
END INSERT_TESTCASE;
procedure INSERT_TESTCASE(p_testclassname in varchar2,
p_group_name in varchar2,
p_appid in varchar2,
p_test_type in varchar2,
p_lubid in number) IS
BEGIN
INSERT_TESTCASE(p_testclassname,
p_group_name,
p_appid,
p_test_type,
null,null,null,null,
p_lubid);
END INSERT_TESTCASE;
procedure INSERT_TESTCASE(p_testclassname in varchar2,
p_group_name in varchar2,
p_appid in varchar2,
p_test_type in varchar2) IS
BEGIN
INSERT_TESTCASE(p_testclassname,
p_group_name,
p_appid,
p_test_type,
UID);
END INSERT_TESTCASE;
procedure INSERT_ARGVALUE_ROW(p_appid in varchar2,
p_group_name in varchar2,
p_test_class_name in varchar2,
p_arg_names in jtf_varchar2_table_4000,
p_arg_values in jtf_varchar2_table_4000,
p_lubid in number) IS
V_SOURCETYPE BINARY_INTEGER := 1;
select count(*) into v_sourcetype
from jtf_diagnostic_test where appid = p_appid
and groupname = p_group_name
and testclassname = p_test_class_name
and rownum <= 1;
select max(rownumber) into v_rownumber
from jtf_diagnostic_arg where testclassname = p_test_class_name
and groupname = p_group_name and appid = p_appid;
select JTF_DIAGNOSTIC_ARG_VAL_SET_S.nextval
into v_valsetnumber from dual;
-- select max(valuesetnumber) into v_valsetnumber
-- from jtf_diagnostic_arg;
-- insert the name-value pair one by one
V_SOURCETYPE := 1;
insert into jtf_diagnostic_arg(
SEQUENCE, TESTCLASSNAME, GROUPNAME,
APPID, ARGNAME, ARGVALUE,
ROWNUMBER, VALUESETNUMBER, OBJECT_VERSION_NUMBER,
CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
LAST_UPDATE_LOGIN, CREATION_DATE)
values(
JTF_DIAGNOSTIC_ARG_S.NEXTVAL, p_test_class_name, p_group_name,
p_appid, p_arg_names(v_sourcetype), p_arg_values(v_sourcetype),
decode(v_rownumber, null, 1, v_rownumber), v_valsetnumber, 1,
p_lubid, SYSDATE, p_lubid,
NULL, SYSDATE);
END INSERT_ARGVALUE_ROW;
procedure INSERT_ARGVALUE_ROW(p_appid in varchar2,
p_group_name in varchar2,
p_test_class_name in varchar2,
p_arg_names in jtf_varchar2_table_4000,
p_arg_values in jtf_varchar2_table_4000
) IS
BEGIN
INSERT_ARGVALUE_ROW(P_APPID,
P_GROUP_NAME,
P_TEST_CLASS_NAME,
P_ARG_NAMES,
P_ARG_VALUES,
UID);
END INSERT_ARGVALUE_ROW;
procedure INSERT_ARGVALUE_ROW(p_appid in varchar2,
p_group_name in varchar2,
p_test_class_name in varchar2,
p_arg_names in jtf_varchar2_table_4000,
p_arg_values in jtf_varchar2_table_4000,
p_arg_dates in jtf_date_table,
p_arg_numbers in jtf_number_table,
p_lubid in number) IS
V_SOURCETYPE BINARY_INTEGER := 1;
select count(*) into v_sourcetype
from jtf_diagnostic_test where appid = p_appid
and groupname = p_group_name
and testclassname = p_test_class_name
and rownum <= 1;
select max(rownumber) into v_rownumber
from jtf_diagnostic_arg where testclassname = p_test_class_name
and groupname = p_group_name and appid = p_appid;
select JTF_DIAGNOSTIC_ARG_VAL_SET_S.nextval
into v_valsetnumber from dual;
-- select max(valuesetnumber) into v_valsetnumber
-- from jtf_diagnostic_arg;
-- insert the name-value pair one by one
V_SOURCETYPE := 1;
insert into jtf_diagnostic_arg(
SEQUENCE, TESTCLASSNAME, GROUPNAME,
APPID, ARGNAME, ARGVALUE,
ARG_DATE_VALUE, ARG_NUMBER_VALUE,
ROWNUMBER, VALUESETNUMBER, OBJECT_VERSION_NUMBER,
CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
LAST_UPDATE_LOGIN, CREATION_DATE)
values(
JTF_DIAGNOSTIC_ARG_S.NEXTVAL, p_test_class_name, p_group_name,
p_appid, p_arg_names(v_sourcetype), p_arg_values(v_sourcetype),
p_arg_dates(v_sourcetype), p_arg_numbers(v_sourcetype),
decode(v_rownumber, null, 1, v_rownumber), v_valsetnumber, 1,
p_lubid, SYSDATE, p_lubid,
NULL, SYSDATE);
END INSERT_ARGVALUE_ROW;
select count(*) into v_data_found from jtf_diagnostic_app
where appid = p_sourceid;
select count(*) into v_data_found from jtf_diagnostic_group
where appid = p_sourceappid and
groupname = p_sourceid;
procedure PREREQ_INSERTION(
P_SOURCEID IN VARCHAR2,
P_SOURCEAPPID IN VARCHAR2,
P_PREREQID IN JTF_VARCHAR2_TABLE_4000,
P_SOURCETYPE IN NUMBER,
P_LUBID IN NUMBER) IS
v_index BINARY_INTEGER := 1;
-- the following checks if the data to be inserted
-- is a valid group in the same application or
-- a valid application registered in the diagnostic
-- framework
if P_SOURCETYPE = 1 then
select sequence into v_data_found from jtf_diagnostic_app
where appid = P_PREREQID(v_index)
and rownum <= 1;
select sequence into v_data_found from jtf_diagnostic_group
where groupname = P_PREREQID(v_index)
and appid = p_sourceappid
and rownum <= 1;
-- we can insert the record in the table
-- need to complete the insert statement
insert into jtf_diagnostic_prereq
(SEQUENCE, SOURCEID, PREREQID,
SOURCEAPPID, TYPE, OBJECT_VERSION_NUMBER,
CREATED_BY, LAST_UPDATE_DATE,
LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE)
values
(JTF_DIAGNOSTIC_PREREQ_S.NEXTVAL, P_SOURCEID,
P_PREREQID(v_index), P_SOURCEAPPID, P_SOURCETYPE,
1, P_LUBID, SYSDATE, P_LUBID,
P_LUBID, SYSDATE);
END PREREQ_INSERTION;
procedure PREREQ_INSERTION(
P_SOURCEID IN VARCHAR2,
P_SOURCEAPPID IN VARCHAR2,
P_PREREQID IN JTF_VARCHAR2_TABLE_4000,
P_SOURCETYPE IN NUMBER) IS
BEGIN
PREREQ_INSERTION(P_SOURCEID,
P_SOURCEAPPID,
P_PREREQID,
P_SOURCETYPE,
UID);
END PREREQ_INSERTION;
select count(*) into v_data_found
from jtf_diagnostic_group
where appid = p_appid
and groupname = p_groupname;
select count(*) into v_data_found
from jtf_diagnostic_group
where appid = p_appid
and groupname = p_newgroupname;
update jtf_diagnostic_group
set groupname = p_newgroupname,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = P_LUBID
where groupname = p_groupname
and appid = p_appid;
update jtf_diagnostic_test
set groupname = p_newgroupname,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = P_LUBID
where groupname = p_groupname
and appid = p_appid;
update jtf_diagnostic_arg
set groupname = p_newgroupname,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = P_LUBID
where groupname = p_groupname
and appid = p_appid;
update jtf_diagnostic_prereq
set sourceid = p_newgroupname,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = P_LUBID
where sourceid = p_groupname
and sourceappid = p_appid;
update jtf_diagnostic_prereq
set prereqid = p_newgroupname,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = P_LUBID
where prereqid = p_groupname
and sourceappid = p_appid;
update jtf_diagnostic_decl_test_steps
set groupname = p_newgroupname,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = P_LUBID
where groupname = p_groupname
and appid = p_appid;
update jtf_diagnostic_decl_step_cols
set groupname = p_newgroupname,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = P_LUBID
where groupname = p_groupname
and appid = p_appid;
f_ludate date; -- entity update date in file
db_ludate date; -- entity update date in db
select LAST_UPDATED_BY, LAST_UPDATE_DATE
into db_luby, db_ludate
from jtf_diagnostic_app
where appid = p_appid;
-- Update record only as per standard
-- 5953806 - replaced to if statement to follow FNDLOAD standards
/*if ((p_cust_mode = 'FORCE') or
((f_luby = 0) and (db_luby = 1)) or
((f_luby = db_luby) and (f_ludate > db_ludate)))*/
/* if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,db_ludate,
p_cust_mode))
then*/
-- seed data must not be changed by customers.Hence overwriting data always
-- so that it covers up any changes by mistake
update jtf_diagnostic_app
set last_updated_by = f_luby,
last_update_date = f_ludate,
object_version_number = object_version_number + 1,
security_group_id = to_number(P_SEC_GRP_ID)
where appid = p_appid;
-- Record doesn't exist - insert in all cases
insert into jtf_diagnostic_app(
sequence,
appid,
object_version_number,
created_by,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
security_group_id)
values(
jtf_diagnostic_app_s.nextval,
p_appid,
1,
f_luby,
f_ludate,
f_luby,
null,
f_ludate,
to_number(P_SEC_GRP_ID));
f_ludate date; -- entity update date in file
db_ludate date; -- entity update date in db
select LAST_UPDATED_BY, LAST_UPDATE_DATE
into db_luby, db_ludate
from jtf_diagnostic_group
where appid = p_appid and
groupname = p_groupname;
-- Update record only as per standard
-- 5953806 - replaced to if statement to follow FNDLOAD standards
/*if ((p_cust_mode = 'FORCE') or
((f_luby = 0) and (db_luby = 1)) or
((f_luby = db_luby) and (f_ludate > db_ludate)))*/
/*if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,db_ludate,
p_cust_mode))
then*/
-- seed data must not be changed by customers.Hence overwriting data always
-- so that it covers up any changes by mistake
update jtf_diagnostic_group
set sensitivity = v_sensitivity,
last_updated_by = f_luby,
last_update_date = f_ludate,
object_version_number = object_version_number + 1,
security_group_id = to_number(P_SEC_GRP_ID)
where appid = p_appid and groupname = p_groupname;
-- Record doesn't exist - insert in all cases
select nvl(max(ordernumber)+1,1) into v_num
from jtf_diagnostic_group where
appid = p_appid;
insert into jtf_diagnostic_group(
sequence,
groupname,
appid,
sensitivity,
ordernumber,
object_version_number,
created_by,
last_update_date,
last_update_login,
last_updated_by,
creation_date,
security_group_id)
values(
jtf_diagnostic_group_s.nextval,
p_groupname,
p_appid,
v_sensitivity,
v_num,
1,
f_luby,
f_ludate,
null,
f_luby,
f_ludate,
to_number(P_SEC_GRP_ID));
f_ludate date; -- entity update date in file
db_ludate date; -- entity update date in db
DOINSERT boolean;
select substr(TESTCLASSNAME,
instr(TESTCLASSNAME,'.',1,2)+1,
instr(TESTCLASSNAME,'.',1,3) -
instr(TESTCLASSNAME,'.',1,2) - 1),
testclassname
from JTF_DIAGNOSTIC_TEST
where GROUPNAME = l_groupname
and APPID = l_appid
and testclassname <> l_testclassname
and substr(TESTCLASSNAME,
instr(TESTCLASSNAME,'.',-1,1)+1)
= l_filename;
select LAST_UPDATED_BY, LAST_UPDATE_DATE
into db_luby, db_ludate
from jtf_diagnostic_test
where appid = p_appid and
groupname = p_groupname
and testclassname = p_testclassname;
-- Update record only as per standard
-- 5953806 - replaced to if statement to follow FNDLOAD standards
/*if ((p_cust_mode = 'FORCE') or
((f_luby = 0) and (db_luby = 1)) or
((f_luby = db_luby) and (f_ludate > db_ludate)))*/
/*if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,db_ludate,
p_cust_mode))
then*/
-- seed data must not be changed by customers.Hence overwriting data always
-- so that it covers up any changes by mistake
-- if valid_applications is not null, insert valid_applications in to table
-- else ignore the valid_applications while inserting
if P_VALID_APPLICATIONS is not null and P_VALID_APPLICATIONS <> empty_clob() then
update jtf_diagnostic_test
set last_updated_by = f_luby,
last_update_date = sysdate,--f_ludate,
object_version_number = object_version_number + 1,
TOTALARGUMENTROWS = p_TOTALARGUMENTROWS,
TESTTYPE = p_TESTTYPE,
sensitivity = l_sensitivity,
security_group_id = to_number(P_SEC_GRP_ID),
valid_applications = xmltype(P_VALID_APPLICATIONS),
end_date = f_end_date,
test_metadata = f_meta_data
where appid = p_appid and groupname = p_groupname
and testclassname = p_testclassname;
update jtf_diagnostic_test
set last_updated_by = f_luby,
last_update_date = sysdate,--f_ludate,
object_version_number = object_version_number + 1,
TOTALARGUMENTROWS = p_TOTALARGUMENTROWS,
TESTTYPE = p_TESTTYPE,
sensitivity = l_sensitivity,
security_group_id = to_number(P_SEC_GRP_ID),
end_date = f_end_date,
test_metadata = f_meta_data
where appid = p_appid and groupname = p_groupname
and testclassname = p_testclassname;
DOINSERT := TRUE;
-- exist and if it does delete it, if we are
-- uploading izu java test and test exists in
-- another product don't upload.
if (instr(P_TESTTYPE,5) <> 0) then
PARSE_TESTCLASSNAME(P_TESTCLASSNAME,
V_PRODUCT,
V_FILENAME);
DELETE_TEST(p_appid,
p_groupname,
c_testclassname);
DOINSERT := FALSE;
if DOINSERT then
--6599133
select MAX(ordernumber) into v_num
from jtf_diagnostic_test where appid = p_appid
and groupname = p_groupname;
insert into jtf_diagnostic_test(
SEQUENCE,
GROUPNAME,
APPID,
ORDERNUMBER,
TESTTYPE,
TESTCLASSNAME,
TOTALARGUMENTROWS,
SENSITIVITY,
OBJECT_VERSION_NUMBER,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
security_group_id,
valid_applications,
end_date,
test_metadata)
values(
jtf_diagnostic_test_s.nextval,
p_groupname,
p_appid,
v_num,
p_testtype,
p_testclassname,
p_totalargumentrows,
l_sensitivity,
1,
f_luby,
f_ludate,
f_luby,
null,
f_ludate,
to_number(P_SEC_GRP_ID),
xmltype(P_VALID_APPLICATIONS),
f_end_date,
f_meta_data);
insert into jtf_diagnostic_test(
SEQUENCE,
GROUPNAME,
APPID,
ORDERNUMBER,
TESTTYPE,
TESTCLASSNAME,
TOTALARGUMENTROWS,
SENSITIVITY,
OBJECT_VERSION_NUMBER,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
security_group_id,
end_date,
test_metadata)
values(
jtf_diagnostic_test_s.nextval,
p_groupname,
p_appid,
v_num,
p_testtype,
p_testclassname,
p_totalargumentrows,
l_sensitivity,
1,
f_luby,
f_ludate,
f_luby,
null,
f_ludate,
to_number(P_SEC_GRP_ID),
f_end_date,
f_meta_data);
f_ludate date; -- entity update date in file
db_ludate date; -- entity update date in db
select LAST_UPDATED_BY, LAST_UPDATE_DATE
into db_luby, db_ludate
from jtf_diagnostic_arg
where appid = p_appid
and groupname = p_groupname
and testclassname = p_testclassname
and argname = p_argname
and rownumber = p_rownumber;
-- Update record only as per standard
-- 5953806 - replaced to if statement to follow FNDLOAD standards
/*if ((p_cust_mode = 'FORCE') or
((f_luby = 0) and (db_luby = 1)) or
((f_luby = db_luby) and (f_ludate > db_ludate)))*/
/*if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,db_ludate,
p_cust_mode))
then*/
-- seed data must not be changed by customers.Hence overwriting data always
-- so that it covers up any changes by mistake
update jtf_diagnostic_arg
set last_updated_by = f_luby,
argvalue = p_argvalue,
VALUESETNUMBER = p_VALUESETNUMBER,
last_update_date = f_ludate,
object_version_number = object_version_number + 1,
security_group_id = to_number(P_SEC_GRP_ID)
where appid = p_appid
and groupname = p_groupname
and testclassname = p_testclassname
and argname = p_argname
and rownumber = p_rownumber;
-- Record doesn't exist - insert in all cases
insert into jtf_diagnostic_arg(
SEQUENCE,
TESTCLASSNAME,
GROUPNAME,
APPID,
ARGNAME,
ARGVALUE,
ROWNUMBER,
VALUESETNUMBER,
OBJECT_VERSION_NUMBER,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
SECURITY_GROUP_ID)
values(
jtf_diagnostic_arg_s.nextval,
p_testclassname,
p_groupname,
p_appid,
p_argname,
p_argvalue,
p_rownumber,
p_valuesetnumber,
1,
f_luby,
f_ludate,
f_luby,
null,
f_ludate,
to_number(P_SEC_GRP_ID));
f_ludate date; -- entity update date in file
db_ludate date; -- entity update date in db
select LAST_UPDATED_BY, LAST_UPDATE_DATE
into db_luby, db_ludate
from jtf_diagnostic_prereq
where sourceid = p_sourceid
and prereqid = p_prereqid
and SOURCEAPPID = p_SOURCEAPPID
and type = p_type;
-- Update record only as per standard
-- 5953806 - replaced to if statement to follow FNDLOAD standards
/*if ((p_cust_mode = 'FORCE') or
((f_luby = 0) and (db_luby = 1)) or
((f_luby = db_luby) and (f_ludate > db_ludate)))*/
/*if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,db_ludate,
p_cust_mode))
then*/
-- seed data must not be changed by customers.Hence overwriting data always
-- so that it covers up any changes by mistake
update jtf_diagnostic_prereq
set last_updated_by = f_luby,
last_update_date = f_ludate,
object_version_number = object_version_number + 1,
security_group_id = to_number(P_SEC_GRP_ID)
where sourceid = p_sourceid
and prereqid = p_prereqid
and SOURCEAPPID = p_SOURCEAPPID
and type = p_type;
-- Record doesn't exist - insert in all cases
insert into jtf_diagnostic_prereq(
SEQUENCE,
SOURCEID,
PREREQID,
SOURCEAPPID,
TYPE,
OBJECT_VERSION_NUMBER,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
SECURITY_GROUP_ID)
values(
jtf_diagnostic_prereq_s.nextval,
p_sourceid,
p_prereqid,
p_sourceappid,
p_type,
1,
f_luby,
f_ludate,
f_luby,
null,
f_ludate,
to_number(P_SEC_GRP_ID));
P_LAST_UPDATE_DATE IN VARCHAR2,
P_SECURITY_GROUP_ID IN VARCHAR2,
P_CUST_MODE IN VARCHAR2,
P_OWNER IN VARCHAR2) IS
f_luby number; -- entity owner in file
f_ludate date; -- entity update date in file
db_ludate date; -- entity update date in db
f_ludate := nvl(to_date(P_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
select LAST_UPDATED_BY, LAST_UPDATE_DATE
into db_luby, db_ludate
from JTF_DIAGNOSTIC_DECL_TEST_STEPS
where APPID = P_APPID
and GROUPNAME = P_GROUPNAME
and TESTCLASSNAME = P_TESTCLASSNAME
and TESTSTEPNAME = P_TESTSTEPNAME;
-- Update record only as per standard
-- 5953806 - replaced to if statement to follow FNDLOAD standards
/*if ((p_cust_mode = 'FORCE') or
((f_luby = 0) and (db_luby = 1)) or
((f_luby = db_luby) and (f_ludate > db_ludate)))*/
/*if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,db_ludate,
p_cust_mode))
then*/
-- seed data must not be changed by customers.Hence overwriting data always
-- so that it covers up any changes by mistake
update JTF_DIAGNOSTIC_DECL_TEST_STEPS
set last_updated_by = f_luby,
last_update_date = f_ludate,
object_version_number = object_version_number + 1,
security_group_id = to_number(P_SECURITY_GROUP_ID),
STEP_TYPE = P_STEP_TYPE,
STEP_DESCRIPTION = P_STEP_DESCRIPTION,
ERROR_TYPE = P_ERROR_TYPE,
ERROR_MESSAGE = P_ERROR_MESSAGE,
FIX_INFO = P_FIX_INFO,
MULTI_ORG = P_MULTI_ORG,
TABLE_VIEW_NAME = P_TABLE_VIEW_NAME,
WHERE_CLAUSE_OR_SQL = P_WHERE_CLAUSE_OR_SQL,
PROFILE_NAME = P_PROFILE_NAME,
PROFILE_VALUE = P_PROFILE_VALUE,
LOGICAL_OPERATOR = P_LOGICAL_OPERATOR,
FUNCTION_NAME = P_FUNCTION_NAME,
VALIDATION_VAL1 = P_VALIDATION_VAL1,
VALIDATION_VAL2 = P_VALIDATION_VAL2
where APPID = P_APPID
and GROUPNAME = P_GROUPNAME
and TESTCLASSNAME = P_TESTCLASSNAME
and TESTSTEPNAME = P_TESTSTEPNAME;
-- Record doesn't exist - insert in all cases
select MAX(EXECUTION_SEQUENCE) into v_EXECUTION_SEQUENCE
from JTF_DIAGNOSTIC_DECL_TEST_STEPS
where APPID = P_APPID
and GROUPNAME = P_GROUPNAME
and TESTCLASSNAME = P_TESTCLASSNAME;
insert into JTF_DIAGNOSTIC_DECL_TEST_STEPS(
APPID,
GROUPNAME,
TESTCLASSNAME,
TESTSTEPNAME,
EXECUTION_SEQUENCE,
STEP_TYPE,
STEP_DESCRIPTION,
ERROR_TYPE,
ERROR_MESSAGE,
FIX_INFO,
MULTI_ORG,
TABLE_VIEW_NAME,
WHERE_CLAUSE_OR_SQL,
PROFILE_NAME,
PROFILE_VALUE,
LOGICAL_OPERATOR,
FUNCTION_NAME,
VALIDATION_VAL1,
VALIDATION_VAL2,
OBJECT_VERSION_NUMBER,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
SECURITY_GROUP_ID)
values(
P_APPID,
P_GROUPNAME,
P_TESTCLASSNAME,
P_TESTSTEPNAME,
v_EXECUTION_SEQUENCE,
P_STEP_TYPE,
P_STEP_DESCRIPTION,
P_ERROR_TYPE,
P_ERROR_MESSAGE,
P_FIX_INFO,
P_MULTI_ORG,
P_TABLE_VIEW_NAME,
P_WHERE_CLAUSE_OR_SQL,
P_PROFILE_NAME,
P_PROFILE_VALUE,
P_LOGICAL_OPERATOR,
P_FUNCTION_NAME,
P_VALIDATION_VAL1,
P_VALIDATION_VAL2,
1,
f_luby,
f_ludate,
f_luby,
null,
f_ludate,
to_number(P_SECURITY_GROUP_ID));
P_LAST_UPDATE_DATE IN VARCHAR2,
P_SECURITY_GROUP_ID IN VARCHAR2,
P_CUST_MODE IN VARCHAR2,
P_OWNER IN VARCHAR2) IS
f_luby number; -- entity owner in file
f_ludate date; -- entity update date in file
db_ludate date; -- entity update date in db
f_ludate := nvl(to_date(P_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
select LAST_UPDATED_BY, LAST_UPDATE_DATE
into db_luby, db_ludate
from jtf_diagnostic_decl_step_cols
where APPID = P_APPID
and GROUPNAME = P_GROUPNAME
and TESTCLASSNAME = P_TESTCLASSNAME
and TESTSTEPNAME = P_TESTSTEPNAME
and COLUMN_NAME = P_COLUMN_NAME;
-- Update record only as per standard
-- 5953806 - replaced to if statement to follow FNDLOAD standards
/*if ((p_cust_mode = 'FORCE') or
((f_luby = 0) and (db_luby = 1)) or
((f_luby = db_luby) and (f_ludate > db_ludate)))*/
/*if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,db_ludate,
p_cust_mode))
then*/
-- seed data must not be changed by customers.Hence overwriting data always
-- so that it covers up any changes by mistake
update jtf_diagnostic_decl_step_cols
set last_updated_by = f_luby,
last_update_date = f_ludate,
object_version_number = object_version_number + 1,
security_group_id = to_number(P_SECURITY_GROUP_ID),
LOGICAL_OPERATOR = P_LOGICAL_OPERATOR,
VALIDATION_VAL1 = P_VALIDATION_VAL1,
VALIDATION_VAL2 = P_VALIDATION_VAL2
where APPID = P_APPID
and GROUPNAME = P_GROUPNAME
and TESTCLASSNAME = P_TESTCLASSNAME
and TESTSTEPNAME = P_TESTSTEPNAME
and COLUMN_NAME = P_COLUMN_NAME;
-- Record doesn't exist - insert in all cases
insert into jtf_diagnostic_decl_step_cols(
APPID,
GROUPNAME,
TESTCLASSNAME,
TESTSTEPNAME,
COLUMN_NAME,
LOGICAL_OPERATOR,
VALIDATION_VAL1,
VALIDATION_VAL2,
OBJECT_VERSION_NUMBER,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
SECURITY_GROUP_ID)
values(
P_APPID,
P_GROUPNAME,
P_TESTCLASSNAME,
P_TESTSTEPNAME,
P_COLUMN_NAME,
P_LOGICAL_OPERATOR,
P_VALIDATION_VAL1,
P_VALIDATION_VAL2,
1,
f_luby,
f_ludate,
f_luby,
null,
f_ludate,
to_number(P_SECURITY_GROUP_ID));
f_ludate date; -- entity update date in file
db_ludate date; -- entity update date in db
select LAST_UPDATED_BY, LAST_UPDATE_DATE
into db_luby, db_ludate
from jtf_diagnostic_alert
where appid = p_appid and
groupname = p_groupname
and testclassname = p_testclassname
and type = p_type;
-- Update record only as per standard
-- 5953806 - replaced to if statement to follow FNDLOAD standards
/*if ((p_cust_mode = 'FORCE') or
((f_luby = 0) and (db_luby = 1)) or
((f_luby = db_luby) and (f_ludate > db_ludate)))*/
/*if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,db_ludate,
p_cust_mode))
then*/
-- seed data must not be changed by customers.Hence overwriting data always
-- so that it covers up any changes by mistake
update jtf_diagnostic_alert
set last_updated_by = f_luby,
last_update_date = f_ludate,
object_version_number = object_version_number + 1,
LEVEL_VALUE = to_number(p_LEVEL_VALUE),
security_group_id = to_number(P_SEC_GRP_ID)
where appid = p_appid and groupname = p_groupname
and testclassname = p_testclassname
and type = p_type;
-- Record doesn't exist - insert in all cases
insert into jtf_diagnostic_alert(
SEQUENCE,
GROUPNAME,
APPID,
TYPE,
TESTCLASSNAME,
LEVEL_VALUE,
OBJECT_VERSION_NUMBER,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
security_group_id)
values(
jtf_diagnostic_alert_s.nextval,
p_groupname,
p_appid,
p_type,
p_testclassname,
to_number(p_level_value),
1,
f_luby,
f_ludate,
f_luby,
null,
f_ludate,
to_number(P_SEC_GRP_ID));
f_ludate date; -- entity update date in file
db_ludate date; -- entity update date in db
-- as test table is updated before kb table, this should work
begin
select SEQUENCE
into seq
from jtf_diagnostic_test
where appid = p_appid and
groupname = p_groupname and
testclassname = p_testclassname;
select LAST_UPDATED_BY, LAST_UPDATE_DATE
into db_luby, db_ludate
from jtf_diagnostic_kb
where sequence = seq;
-- Update record only as per standard
-- 5953806 - replaced to if statement to follow FNDLOAD standards
/*if ((p_cust_mode = 'FORCE') or
((f_luby = 0) and (db_luby = 1)) or
((f_luby = db_luby) and (f_ludate > db_ludate)))*/
/*if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,db_ludate,
p_cust_mode))
then*/
-- seed data must not be changed by customers.Hence overwriting data always
-- so that it covers up any changes by mistake
update jtf_diagnostic_kb
set last_updated_by = f_luby,
last_update_date = f_ludate,
object_version_number = object_version_number + 1,
USER_TEST_NAME = P_USER_TEST_NAME,
METALINK_NOTE = P_METALINK_NOTE,
COMPETENCY = P_COMPETENCY,
SUBCOMPETENCY = P_SUBCOMPETENCY,
PRODUCTS = P_PRODUCTS,
TEST_TYPE = P_TEST_TYPE,
ANALYSIS_SCOPE = P_ANALYSIS_SCOPE,
DESCRIPTION = P_DESCRIPTION,
SHORT_DESCR = P_SHORT_DESCR,
USAGE_DESCR = P_USAGE_DESCR,
KEYWORDS = P_KEYWORDS,
COMPONENT = P_COMPONENT,
SUBCOMPONENT = P_SUBCOMPONENT,
HIGH_PRODUCT_VERSION = P_HIGH_PRODUCT_VERSION,
LOW_PRODUCT_VERSION = P_LOW_PRODUCT_VERSION,
HIGH_PATCHSET = P_HIGH_PATCHSET,
LOW_PATCHSET = P_LOW_PATCHSET,
security_group_id = to_number(P_SEC_GRP_ID)
where sequence = seq;
-- Record doesn't exist - insert in all cases
insert into jtf_diagnostic_kb(
SEQUENCE,
USER_TEST_NAME,
METALINK_NOTE,
COMPETENCY,
SUBCOMPETENCY,
PRODUCTS,
TEST_TYPE,
ANALYSIS_SCOPE,
DESCRIPTION,
SHORT_DESCR,
USAGE_DESCR,
KEYWORDS,
COMPONENT,
SUBCOMPONENT,
HIGH_PRODUCT_VERSION,
LOW_PRODUCT_VERSION,
HIGH_PATCHSET,
LOW_PATCHSET,
OBJECT_VERSION_NUMBER,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
security_group_id)
values(
seq,
P_USER_TEST_NAME,
P_METALINK_NOTE,
P_COMPETENCY,
P_SUBCOMPETENCY,
P_PRODUCTS,
P_TEST_TYPE,
P_ANALYSIS_SCOPE,
P_DESCRIPTION,
P_SHORT_DESCR,
P_USAGE_DESCR,
P_KEYWORDS,
P_COMPONENT,
P_SUBCOMPONENT,
P_HIGH_PRODUCT_VERSION,
P_LOW_PRODUCT_VERSION,
P_HIGH_PATCHSET,
P_LOW_PATCHSET,
1,
f_luby,
f_ludate,
f_luby,
null,
f_ludate,
to_number(P_SEC_GRP_ID));
select substr(P_TESTCLASSNAME,
instr(P_TESTCLASSNAME,'.',1,2)+1,
instr(P_TESTCLASSNAME,'.',1,3) -
instr(P_TESTCLASSNAME,'.',1,2) - 1)
into V_PRODUCT from dual;
select substr(P_TESTCLASSNAME,
instr(P_TESTCLASSNAME,'.',-1,1)+1)
into V_FILENAME from dual;
f_ludate date; -- entity update date in file
db_ludate date; -- entity update date in db
select LAST_UPDATED_BY, LAST_UPDATE_DATE
into db_luby, db_ludate
from jtf_diagnostic_testset
where name = p_name;
-- Update record only as per standard
/*if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,db_ludate,
p_cust_mode))
then*/
-- seed data must not be changed by customers.Hence overwriting data always
-- so that it covers up any changes by mistake
update_testset(p_name,p_description,p_xml,f_luby,f_ludate);
-- Record doesn't exist - insert in all cases
insert_testset(p_name,p_description,p_xml,f_luby,f_ludate,null,f_luby,f_ludate);
PROCEDURE UPDATE_TESTSET(
P_NAME IN VARCHAR2,
P_DESCRIPTION IN VARCHAR2,
P_XML IN CLOB) IS
f_luby number;
select sysdate into f_ludate from dual;
UPDATE_TESTSET(P_NAME, P_DESCRIPTION, P_XML, f_luby, f_ludate);
END UPDATE_TESTSET;
PROCEDURE UPDATE_TESTSET(
P_NAME IN VARCHAR2,
P_DESCRIPTION IN VARCHAR2,
P_XML IN CLOB,
P_LAST_UPDATED_BY IN NUMBER,
P_LAST_UPDATED_DATE IN DATE) IS
BEGIN
update jtf_diagnostic_testset
set description = P_DESCRIPTION,
xml = XMLTYPE(P_XML),
last_updated_by = P_LAST_UPDATED_BY,
last_update_date = P_LAST_UPDATED_DATE
where name = P_NAME;
END UPDATE_TESTSET;
PROCEDURE INSERT_TESTSET(
P_NAME IN VARCHAR2,
P_DESCRIPTION IN VARCHAR2,
P_XML IN CLOB) IS
f_luby number;
select sysdate into f_ludate from dual;
INSERT_TESTSET(P_NAME, P_DESCRIPTION, P_XML, f_luby, f_ludate, null, f_luby, f_ludate);
END INSERT_TESTSET;
PROCEDURE INSERT_TESTSET(
P_NAME IN VARCHAR2,
P_DESCRIPTION IN VARCHAR2,
P_XML IN CLOB,
P_CREATED_BY IN NUMBER,
P_CREATION_DATE IN DATE,
P_LAST_UPDATE_LOGIN IN NUMBER,
P_LAST_UPDATED_BY IN NUMBER,
P_LAST_UPDATED_DATE IN DATE) IS
BEGIN
insert into jtf_diagnostic_testset
(NAME, DESCRIPTION, XML, CREATED_BY, CREATION_DATE,
LAST_UPDATE_LOGIN, LAST_UPDATED_BY, LAST_UPDATE_DATE)
values
( P_NAME , P_DESCRIPTION, XMLType(P_XML), P_CREATED_BY, P_CREATION_DATE,
P_LAST_UPDATE_LOGIN, P_LAST_UPDATED_BY, P_LAST_UPDATED_DATE);
END INSERT_TESTSET;
PROCEDURE UPDATE_VALID_APPS(
P_APPSHORTNAME IN VARCHAR2,
P_GROUPNAME IN VARCHAR2,
P_TESTCLASSNAME IN VARCHAR2,
P_VALIDAPPS IN VARCHAR2) IS
F_LUBY NUMBER;
SELECT SYSDATE INTO F_LUDATE FROM DUAL;
UPDATE_VALID_APPS(P_APPSHORTNAME, P_GROUPNAME, P_TESTCLASSNAME,P_VALIDAPPS, F_LUBY, F_LUDATE);
END UPDATE_VALID_APPS;
PROCEDURE UPDATE_VALID_APPS(
P_APPSHORTNAME IN VARCHAR2,
P_GROUPNAME IN VARCHAR2,
P_TESTCLASSNAME IN VARCHAR2,
P_VALIDAPPS IN VARCHAR2,
P_LAST_UPDATED_BY IN NUMBER,
P_LAST_UPDATED_DATE IN DATE) IS
BEGIN
UPDATE JTF_DIAGNOSTIC_TEST
SET VALID_APPLICATIONS = XMLTYPE(P_VALIDAPPS),
LAST_UPDATED_BY = P_LAST_UPDATED_BY,
LAST_UPDATE_DATE = P_LAST_UPDATED_DATE
WHERE APPID = P_APPSHORTNAME
AND GROUPNAME = P_GROUPNAME
AND TESTCLASSNAME = P_TESTCLASSNAME;
END UPDATE_VALID_APPS;
select distinct owner_tag from wf_roles where name in
( select role_name from wf_user_roles where user_name=sys_context('FND','USER_NAME')
and role_name not in ( 'FND_RESP|FND|APPLICATION_DIAGNOSTICS|STANDARD','UMX|ODF_APPLICATION_END_USER_ROLE',
'UMX|ODF_APPLICATION_SUPER_USER_ROLE','UMX|ODF_DIAGNOSTICS_SUPER_USER_ROLE')
and sysdate >=start_date and start_date = sysdate
)
and sysdate >=start_date and start_date
select extractvalue(value(tbl),'/value') apps from
table(xmlsequence(extract(P_VALID_APPS_XML,'/list/value'))) tbl;
cursor custom_apps_cursor is select instance_pk1_value from fnd_grants
where grantee_key = p_custom_role and object_id = p_object_id
and menu_id = p_permission_set_id;
select object_id into p_object_id from fnd_objects
where obj_name = 'ODF_CUSTOMIZATION_OBJ';
select menu_id into p_permission_set_id from fnd_menus
where menu_name = 'ODF_EXECUTION_PS';
select application_id into p_appid from fnd_application where application_short_name = APP_SHORT_NAME;
procedure UPDATE_TEST_END_DATE(
P_APP_NAME IN VARCHAR2,
P_GROUP_NAME IN VARCHAR2,
P_TEST_NAME IN VARCHAR2,
P_END_DATE IN DATE default null,
P_LUBID IN NUMBER
) IS
F_END_DATE date;
UPDATE jtf_diagnostic_test
SET end_date = F_END_DATE,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = P_LUBID
WHERE appid = P_APP_NAME AND
groupname = P_GROUP_NAME AND
testclassname = P_TEST_NAME;
RAISE_APPLICATION_ERROR(-20000,'Cant Update, Record Not
Found');
END UPDATE_TEST_END_DATE;