10: v_number number := 0;
11: v_date date := add_months(sysdate, -480);
12:
13: BEGIN
14: select count(*) into v_number from jtf_diagnostic_prereq where
15: sourceappid = 'migrate_date_flag' and sourceid = 'migrate_date_flag'
16: and prereqid = 'migrate_date_flag';
17:
18: if v_number = 0 then
15: sourceappid = 'migrate_date_flag' and sourceid = 'migrate_date_flag'
16: and prereqid = 'migrate_date_flag';
17:
18: if v_number = 0 then
19: insert into jtf_diagnostic_prereq
20: (SEQUENCE, SOURCEID, PREREQID,
21: SOURCEAPPID, TYPE, OBJECT_VERSION_NUMBER,
22: CREATED_BY, LAST_UPDATE_DATE,
23: LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE)
21: SOURCEAPPID, TYPE, OBJECT_VERSION_NUMBER,
22: CREATED_BY, LAST_UPDATE_DATE,
23: LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE)
24: values
25: (JTF_DIAGNOSTIC_PREREQ_S.NEXTVAL, 'migrate_date_flag',
26: 'migrate_date_flag', 'migrate_date_flag', -1,
27: -1, -1,v_date , -1,
28: NULL, v_date);
29: end if;
37:
38: procedure UPDATE_MIGRATION_DATE is
39:
40: BEGIN
41: update jtf_diagnostic_prereq
42: set last_update_date = SYSDATE
43: where sourceid='migrate_date_flag' and
44: prereqid='migrate_date_flag' and
45: sourceappid='migrate_date_flag';
54: v_last_update_date date;
55:
56: BEGIN
57: select last_update_date into v_last_update_date
58: from jtf_diagnostic_prereq
59: where sourceid='migrate_date_flag' and
60: prereqid='migrate_date_flag' and
61: sourceappid='migrate_date_flag' for update of last_update_date;
62: END LOCK_MIGRATION_DATE;
93:
94: BEGIN
95:
96: select last_update_date into v_last_migrate_date from
97: jtf_diagnostic_prereq where sourceid = 'migrate_date_flag'
98: and sourceappid = 'migrate_date_flag'
99: and prereqid = 'migrate_date_flag';
100:
101: for x in applist
163: and a.perz_data_name = 'TESTHARNESS.' || P_ASN || '.PREREQ';
164:
165: BEGIN
166: select last_update_date into v_last_migrate_date from
167: jtf_diagnostic_prereq where sourceid = 'migrate_date_flag'
168: and sourceappid = 'migrate_date_flag'
169: and prereqid = 'migrate_date_flag';
170:
171: FOR X IN PREREQLIST LOOP
171: FOR X IN PREREQLIST LOOP
172:
173: -- check if this PREREQ already exists.
174: -- if not insert, else update
175: select count(*) into v_count from jtf_diagnostic_prereq
176: where sourceid = p_asn and sourceappid = p_asn
177: and prereqid = x.attribute_value;
178:
179: if v_last_migrate_date < x.last_update_date or v_count = 0 then
181: if v_count = 0 then
182:
183: -- insert the prereq
184:
185: insert into jtf_diagnostic_prereq(
186: SEQUENCE, SOURCEID, PREREQID,
187: SOURCEAPPID, TYPE, OBJECT_VERSION_NUMBER,
188: CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
189: LAST_UPDATE_LOGIN, CREATION_DATE)
187: SOURCEAPPID, TYPE, OBJECT_VERSION_NUMBER,
188: CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
189: LAST_UPDATE_LOGIN, CREATION_DATE)
190: values(
191: JTF_DIAGNOSTIC_PREREQ_S.nextval, p_asn, x.attribute_value,
192: p_asn, 1, x.object_version_number,
193: x.created_by, x.last_update_date, x.created_by,
194: NULL, x.LAST_UPDATE_DATE);
195:
195:
196: else
197: -- update the prereq
198:
199: update jtf_diagnostic_prereq set
200: last_update_date = x.last_update_date,
201: LAST_UPDATED_BY = x.LAST_UPDATED_BY,
202: OBJECT_VERSION_NUMBER = x.OBJECT_VERSION_NUMBER
203: where sourceappid = p_asn and
277:
278: IF P_GRPCOUNT > 0 THEN
279:
280: select last_update_date into v_last_migrate_date from
281: jtf_diagnostic_prereq where sourceid = 'migrate_date_flag'
282: and sourceappid = 'migrate_date_flag'
283: and prereqid = 'migrate_date_flag';
284:
285: FOR X IN grplist LOOP
401: and a.perz_data_name like 'TESTHARNESS.' || P_ASN || '.' || P_GRPNAME ||'.DEP';
402: BEGIN
403:
404: select last_update_date into v_last_migrate_date from
405: jtf_diagnostic_prereq where sourceid = 'migrate_date_flag'
406: and sourceappid = 'migrate_date_flag'
407: and prereqid = 'migrate_date_flag';
408:
409: FOR X IN prereqlist LOOP
409: FOR X IN prereqlist LOOP
410:
411: -- check if this already exists.
412: -- if not insert, else update
413: select count(*) into v_count from jtf_diagnostic_prereq
414: where sourceappid = P_ASN
415: and prereqid = x.attribute_value
416: and sourceid = p_grpname;
417:
419:
420: if v_count = 0 then
421: -- insert the group prereq
422:
423: insert into jtf_diagnostic_prereq(
424: SEQUENCE, SOURCEID, PREREQID,
425: SOURCEAPPID, TYPE, OBJECT_VERSION_NUMBER,
426: CREATED_BY, LAST_UPDATE_DATE,
427: LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE)
425: SOURCEAPPID, TYPE, OBJECT_VERSION_NUMBER,
426: CREATED_BY, LAST_UPDATE_DATE,
427: LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE)
428: values(
429: JTF_DIAGNOSTIC_PREREQ_S.NEXTVAL,
430: p_grpname, x.attribute_value, p_asn,
431: 2, x.object_version_number, x.created_by,
432: x.last_update_date, x.created_by,
433: NULL, x.last_update_date);
434:
435:
436: else
437: -- just update the group prereq
438: update jtf_diagnostic_prereq set
439: OBJECT_VERSION_NUMBER = x.OBJECT_VERSION_NUMBER,
440: LAST_UPDATE_DATE = x.LAST_UPDATE_DATE,
441: LAST_UPDATED_BY = x.LAST_UPDATED_BY;
442:
478:
479: BEGIN
480:
481: select last_update_date into v_last_migrate_date from
482: jtf_diagnostic_prereq where sourceid = 'migrate_date_flag'
483: and sourceappid = 'migrate_date_flag'
484: and prereqid = 'migrate_date_flag';
485:
486: FOR X IN testlist LOOP
761:
762: if v_count = v_argument_names.count then
763:
764: select last_update_date into v_last_migrate_date from
765: jtf_diagnostic_prereq where sourceid = 'migrate_date_flag'
766: and sourceappid = 'migrate_date_flag'
767: and prereqid = 'migrate_date_flag';
768:
769: for x in argvallist loop