[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure INSERT_PLACEHOLDER_DATE is
v_number number := 0;
select count(*) into v_number from jtf_diagnostic_prereq where
sourceappid = 'migrate_date_flag' and sourceid = 'migrate_date_flag'
and prereqid = 'migrate_date_flag';
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, 'migrate_date_flag',
'migrate_date_flag', 'migrate_date_flag', -1,
-1, -1,v_date , -1,
NULL, v_date);
END INSERT_PLACEHOLDER_DATE;
procedure UPDATE_MIGRATION_DATE is
BEGIN
update jtf_diagnostic_prereq
set last_update_date = SYSDATE
where sourceid='migrate_date_flag' and
prereqid='migrate_date_flag' and
sourceappid='migrate_date_flag';
END UPDATE_MIGRATION_DATE;
v_last_update_date date;
select last_update_date into v_last_update_date
from jtf_diagnostic_prereq
where sourceid='migrate_date_flag' and
prereqid='migrate_date_flag' and
sourceappid='migrate_date_flag' for update of last_update_date;
select distinct c.application_id, c.application_short_name,
a.created_by, a.last_updated_by, a.last_update_date,
a.object_version_number from jtf_perz_data a,
jtf_perz_profile b, fnd_application c where a.profile_id = b.profile_id
and b.profile_name = 'JTF_PROPERTY_MANAGER_DEFAULT_1'
and a.perz_data_type = 'JTF' and a.perz_data_name
like 'TESTHARNESS%GRPCOUNT' and a.application_id = c.application_id;
select last_update_date into v_last_migrate_date from
jtf_diagnostic_prereq where sourceid = 'migrate_date_flag'
and sourceappid = 'migrate_date_flag'
and prereqid = 'migrate_date_flag';
-- if not insert, else update
select count(*) into v_count from jtf_diagnostic_app
where appid = x.application_short_name;
if v_last_migrate_date < x.last_update_date or v_count = 0 then
if v_count = 0 then
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,
x.application_short_name, x.object_version_number,
x.CREATED_BY, x.LAST_UPDATE_DATE, x.created_by,
null, x.LAST_UPDATE_DATE);
update jtf_diagnostic_app set
last_update_date = x.last_update_date,
LAST_UPDATED_BY = x.LAST_UPDATED_BY,
OBJECT_VERSION_NUMBER = x.OBJECT_VERSION_NUMBER
where appid = x.application_short_name;
-- insert or update prereqs
MIGRATE_APP_PREREQS(x.application_short_name,
x.application_id);
-- Insert or update groups
MIGRATE_APP_GROUPS(x.application_short_name,
x.application_id);
select b.attribute_value, b.created_by,
b.last_updated_by, b.last_update_date, a.object_version_number
from jtf_perz_data a , jtf_perz_data_attrib b, jtf_perz_profile c
where a.perz_data_id = b.perz_data_id
and a.profile_id = c.profile_id
and c.profile_name = 'JTF_PROPERTY_MANAGER_DEFAULT_1'
and a.application_id= P_APP_ID
and a.perz_data_name = 'TESTHARNESS.' || P_ASN || '.PREREQ';
select last_update_date into v_last_migrate_date from
jtf_diagnostic_prereq where sourceid = 'migrate_date_flag'
and sourceappid = 'migrate_date_flag'
and prereqid = 'migrate_date_flag';
-- if not insert, else update
select count(*) into v_count from jtf_diagnostic_prereq
where sourceid = p_asn and sourceappid = p_asn
and prereqid = x.attribute_value;
if v_last_migrate_date < x.last_update_date or v_count = 0 then
if v_count = 0 then
-- insert the prereq
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_asn, x.attribute_value,
p_asn, 1, x.object_version_number,
x.created_by, x.last_update_date, x.created_by,
NULL, x.LAST_UPDATE_DATE);
-- update the prereq
update jtf_diagnostic_prereq set
last_update_date = x.last_update_date,
LAST_UPDATED_BY = x.LAST_UPDATED_BY,
OBJECT_VERSION_NUMBER = x.OBJECT_VERSION_NUMBER
where sourceappid = p_asn and
prereqid = x.attribute_value
and sourceid = p_asn;
select distinct to_number(b.attribute_value) into v_group_count
from jtf_perz_data a , jtf_perz_data_attrib b, jtf_perz_profile c
where a.perz_data_id = b.perz_data_id
and a.profile_id = c.profile_id
and c.profile_name = 'JTF_PROPERTY_MANAGER_DEFAULT_1'
and a.application_id= P_APP_ID
and a.perz_data_name = 'TESTHARNESS.' || P_ASN || '.GRPCOUNT';
select b.attribute_value, b.created_by,
b.last_updated_by, b.last_update_date, a.object_version_number
from jtf_perz_data a , jtf_perz_data_attrib b, jtf_perz_profile c
where a.perz_data_id = b.perz_data_id
and a.profile_id = c.profile_id
and c.profile_name = 'JTF_PROPERTY_MANAGER_DEFAULT_1'
and a.application_id = P_APP_ID
and a.perz_data_name like 'TESTHARNESS.' || P_ASN || '.GROUP.'|| p_grpcount ||'.NAME';
select last_update_date into v_last_migrate_date from
jtf_diagnostic_prereq where sourceid = 'migrate_date_flag'
and sourceappid = 'migrate_date_flag'
and prereqid = 'migrate_date_flag';
-- if not insert, else update
select count(*) into v_count from jtf_diagnostic_group
where appid = P_ASN
and groupname = x.attribute_value;
if v_last_migrate_date < x.last_update_date or v_count = 0 then
-- v_temp := grplist%ROWCOUNT;
select MAX(ordernumber) into v_temp
from jtf_diagnostic_group where appid = p_asn;
-- insert the record
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, x.attribute_value,
P_ASN, DECODE(v_temp,null,1,v_temp),
x.OBJECT_VERSION_NUMBER, x.CREATED_BY,
x.LAST_UPDATE_DATE, x.created_by, NULL,
x.LAST_UPDATE_DATE, 1);
-- update the record
update jtf_diagnostic_group set
OBJECT_VERSION_NUMBER = x.OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE = x.LAST_UPDATE_DATE,
LAST_UPDATED_BY = x.LAST_UPDATED_BY
where appid = P_ASN
and groupname = x.attribute_value;
--insert or update group prereqs
MIGRATE_GROUP_PREREQS(P_ASN,
x.attribute_value, P_APP_ID);
select count(*) into v_temp
from jtf_perz_data a , jtf_perz_data_attrib b, jtf_perz_profile c
where a.perz_data_id = b.perz_data_id
and a.profile_id = c.profile_id
and c.profile_name = 'JTF_PROPERTY_MANAGER_DEFAULT_1'
and a.application_id = P_APP_ID
and a.perz_data_name = 'TESTHARNESS.' || p_asn || '.'
|| x.attribute_value ||'.TCOUNT';
select distinct to_number(b.attribute_value) into v_temp
from jtf_perz_data a , jtf_perz_data_attrib b, jtf_perz_profile c
where a.perz_data_id = b.perz_data_id
and a.profile_id = c.profile_id
and c.profile_name = 'JTF_PROPERTY_MANAGER_DEFAULT_1'
and a.application_id = P_APP_ID
and a.perz_data_name = 'TESTHARNESS.' || p_asn || '.'
|| x.attribute_value ||'.TCOUNT';
--insert or update test information
MIGRATE_GROUP_TESTS(P_ASN, x.attribute_value,
P_APP_ID, v_count);
select b.attribute_value, b.created_by,
b.last_updated_by, b.last_update_date, a.object_version_number
from jtf_perz_data a , jtf_perz_data_attrib b, jtf_perz_profile c
where a.perz_data_id = b.perz_data_id
and a.profile_id = c.profile_id
and c.profile_name = 'JTF_PROPERTY_MANAGER_DEFAULT_1'
and a.application_id = P_APP_ID
and a.perz_data_name like 'TESTHARNESS.' || P_ASN || '.' || P_GRPNAME ||'.DEP';
select last_update_date into v_last_migrate_date from
jtf_diagnostic_prereq where sourceid = 'migrate_date_flag'
and sourceappid = 'migrate_date_flag'
and prereqid = 'migrate_date_flag';
-- if not insert, else update
select count(*) into v_count from jtf_diagnostic_prereq
where sourceappid = P_ASN
and prereqid = x.attribute_value
and sourceid = p_grpname;
if v_last_migrate_date < x.last_update_date or v_count = 0 then
if v_count = 0 then
-- insert the group prereq
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_grpname, x.attribute_value, p_asn,
2, x.object_version_number, x.created_by,
x.last_update_date, x.created_by,
NULL, x.last_update_date);
-- just update the group prereq
update jtf_diagnostic_prereq set
OBJECT_VERSION_NUMBER = x.OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE = x.LAST_UPDATE_DATE,
LAST_UPDATED_BY = x.LAST_UPDATED_BY;
select distinct a.perz_data_name, b.attribute_value, b.created_by,
b.last_updated_by, b.last_update_date, a.object_version_number
from jtf_perz_data a , jtf_perz_data_attrib b, jtf_perz_profile c
where a.perz_data_id = b.perz_data_id
and a.profile_id = c.profile_id
and c.profile_name = 'JTF_PROPERTY_MANAGER_DEFAULT_1'
and a.application_id = P_APP_ID
and a.perz_data_name = 'TESTHARNESS.' || P_ASN || '.' || P_GRPNAME ||'.TEST'
||'.' || p_testnum order by a.perz_data_name, b.attribute_value;
select last_update_date into v_last_migrate_date from
jtf_diagnostic_prereq where sourceid = 'migrate_date_flag'
and sourceappid = 'migrate_date_flag'
and prereqid = 'migrate_date_flag';
-- insertion happens for every 2nd row from the cursor
-- since the first one is the test type that we have to convert
-- to the desired format as required by daniel's new standard
-- after pl/sql enabling
if mod(testlist%rowcount, 2) = 0 and v_prev_data_name = x.perz_data_name then
-- check if this already exists.
-- if not insert, else update
select count(*) into v_count from jtf_diagnostic_test
where appid = P_ASN and groupname = P_GRPNAME and
testclassname = x.attribute_value;
if v_last_migrate_date < x.last_update_date or v_count = 0 then
if v_count = 0 then
-- insert the testcase after getting
-- all the right parameters
-- ordernumber (max there in the database + 1)
select MAX(ordernumber) into v_temp_ordnum
from jtf_diagnostic_test
where appid = p_asn
and groupname = p_grpname;
select count(*) into v_temp_argrows
from jtf_perz_data a , jtf_perz_data_attrib b,
jtf_perz_profile c
where a.perz_data_id = b.perz_data_id
and a.profile_id = c.profile_id
and c.profile_name = 'JTF_PROPERTY_MANAGER_DEFAULT_1'
and a.application_id = p_app_id
and a.perz_data_name = 'TESTHARNESS.' || p_asn || '.'
|| p_grpname ||'.'
|| x.attribute_value ||'.SETCOUNT';
select distinct TO_NUMBER(b.attribute_value)
into v_temp_argrows
from jtf_perz_data a , jtf_perz_data_attrib b,
jtf_perz_profile c
where a.perz_data_id = b.perz_data_id
and a.profile_id = c.profile_id
and c.profile_name = 'JTF_PROPERTY_MANAGER_DEFAULT_1'
and a.application_id = p_app_id
and a.perz_data_name = 'TESTHARNESS.' || p_asn || '.'
|| p_grpname ||'.'
|| x.attribute_value ||'.SETCOUNT';
insert into jtf_diagnostic_test(
SEQUENCE, GROUPNAME, APPID,
ORDERNUMBER, TESTTYPE, TESTCLASSNAME,
TOTALARGUMENTROWS, OBJECT_VERSION_NUMBER,
CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
LAST_UPDATE_LOGIN, CREATION_DATE)
values(
jtf_diagnostic_test_s.nextval, p_grpname, p_asn,
DECODE(v_temp_ordnum,null,1,v_temp_ordnum),
v_prev_attrib_value,
x.attribute_value,
v_temp_argrows, X.OBJECT_VERSION_NUMBER, X.created_by,
x.last_update_date, X.created_by,
NULL, x.last_update_date);
-- just update the testcase
update jtf_diagnostic_test set
OBJECT_VERSION_NUMBER = x.OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE = x.LAST_UPDATE_DATE,
LAST_UPDATED_BY = x.LAST_UPDATED_BY
where appid = p_asn and groupname = p_grpname
and testclassname = x.attribute_value;
select a.perz_data_name, b.attribute_value, b.created_by,
b.last_updated_by, b.last_update_date, a.object_version_number
from jtf_perz_data a , jtf_perz_data_attrib b, jtf_perz_profile c
where a.perz_data_id = b.perz_data_id and a.profile_id = c.profile_id
and c.profile_name = 'JTF_PROPERTY_MANAGER_DEFAULT_1'
and a.application_id = p_app_id and a.perz_data_name like
'TESTHARNESS.' || p_asn || '.' || p_grpname ||'.'
|| p_classname ||'.ArgName';
select count(*) into v_count
from jtf_perz_data a , jtf_perz_data_attrib b,
jtf_perz_profile c
where a.perz_data_id = b.perz_data_id
and a.profile_id = c.profile_id
and c.profile_name = 'JTF_PROPERTY_MANAGER_DEFAULT_1'
and a.application_id = p_app_id
and a.perz_data_name = 'TESTHARNESS.' || p_asn || '.'
|| p_grpname ||'.'
|| p_classname ||'.SETCOUNT';
select distinct TO_NUMBER(b.attribute_value)
into v_count
from jtf_perz_data a , jtf_perz_data_attrib b,
jtf_perz_profile c
where a.perz_data_id = b.perz_data_id
and a.profile_id = c.profile_id
and c.profile_name = 'JTF_PROPERTY_MANAGER_DEFAULT_1'
and a.application_id = p_app_id
and a.perz_data_name = 'TESTHARNESS.' || p_asn || '.'
|| p_grpname ||'.'
|| p_classname ||'.SETCOUNT';
select a.perz_data_name, b.attribute_value, b.created_by,
b.last_updated_by, b.last_update_date, a.object_version_number
from jtf_perz_data a , jtf_perz_data_attrib b, jtf_perz_profile c
where a.perz_data_id = b.perz_data_id and a.profile_id = c.profile_id
and c.profile_name = 'JTF_PROPERTY_MANAGER_DEFAULT_1'
and a.application_id = p_app_id
and a.perz_data_name like
'TESTHARNESS.' || p_asn || '.' || p_grpname ||'.'
|| p_classname ||'.SET.' || p_rownum;
select count(*) into v_count
from jtf_perz_data a , jtf_perz_data_attrib b, jtf_perz_profile c
where a.perz_data_id = b.perz_data_id and a.profile_id = c.profile_id
and c.profile_name = 'JTF_PROPERTY_MANAGER_DEFAULT_1'
and a.application_id = p_app_id
and a.perz_data_name like
'TESTHARNESS.' || p_asn || '.' || p_grpname ||'.'
|| p_classname ||'.SET.' || p_rownum;
select last_update_date into v_last_migrate_date from
jtf_diagnostic_prereq where sourceid = 'migrate_date_flag'
and sourceappid = 'migrate_date_flag'
and prereqid = 'migrate_date_flag';
-- if not we will insert it anyway
-- irrespective of last_migrate date
v_rowcounter := argvallist%rowcount;
select count(*) into v_count from jtf_diagnostic_arg
where testclassname = p_classname and
groupname = p_grpname and
appid = p_asn and
argname = v_argument_names(v_rowcounter) and
rownumber = p_rownum;
if v_last_migrate_date < x.last_update_date or v_count = 0 then
if v_count = 0 then
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_classname, p_grpname,
p_asn,
v_argument_names(v_rowcounter),
x.attribute_value, p_rownum, 1,
x.object_version_number, x.created_by,
x.last_update_date, x.created_by,
null, x.last_update_date);
update jtf_diagnostic_arg set
argvalue = x.attribute_value,
last_updated_by = x.last_updated_by,
object_version_number = x.object_version_number,
last_update_date = x.last_update_date
where testclassname = p_classname and
groupname = p_grpname and
appid = p_asn and
argname = v_argument_names(v_rowcounter) and
rownumber = p_rownum;