[Home] [Help]
PACKAGE BODY: APPS.JTF_DIAGNOSTICLOG
Source
1 PACKAGE BODY JTF_DIAGNOSTICLOG AS
2 /* $Header: jtfdiaglog_b.pls 120.2 2005/08/13 01:56:40 minxu noship $ */
3 ----------------------------------------------------------
4 -- Insert a new stat or update an existing stat
5 ----------------------------------------------------------
6
7 procedure INSERT_LOG_STATS(
8 P_SESSIONID IN VARCHAR2,
9 P_MIDTIERNODE IN VARCHAR2,
10 P_APPNAME IN VARCHAR2,
11 P_GROUPNAME IN VARCHAR2,
12 P_TESTCLASSNAME IN VARCHAR2,
13 P_TIME IN DATE,
14 P_STATUS IN NUMBER,
15 P_MILLISCONSUMED IN NUMBER,
16 P_MODE IN NUMBER,
17 P_INDEX IN NUMBER,
18 P_INSTALLVERSION IN VARCHAR2,
19 P_TOOLVERSION IN VARCHAR2,
20 P_TESTVERSION IN VARCHAR2,
21 P_INPUTS IN VARCHAR2,
22 P_ERROR IN VARCHAR2,
23 P_FIXINFO IN VARCHAR2,
24 P_REPORT IN CLOB,
25 P_VERSIONS IN VARCHAR2,
26 P_DEPENDENCIES IN VARCHAR2,
27 P_LUBID IN NUMBER,
28 P_SEQUENCE OUT NOCOPY NUMBER
29 ) IS
30
31 V_SEQUENCE NUMBER;
32
33 BEGIN
34
35 INSERT_LOG(P_SESSIONID, P_MIDTIERNODE, P_APPNAME, P_GROUPNAME,
36 P_TESTCLASSNAME, P_TIME, P_STATUS, P_MILLISCONSUMED,
37 P_MODE, P_INDEX, P_INSTALLVERSION, P_TOOLVERSION, P_TESTVERSION,
38 P_INPUTS, P_ERROR, P_FIXINFO, P_REPORT, P_VERSIONS,
39 P_DEPENDENCIES, P_LUBID, V_SEQUENCE);
40
41 P_SEQUENCE := V_SEQUENCE;
42
43 INSERT_OR_UPDATE_STATS(P_APPNAME, P_GROUPNAME, P_TESTCLASSNAME,
44 P_TIME, P_STATUS, V_SEQUENCE, P_LUBID);
45
46
47 END INSERT_LOG_STATS;
48
49
50 ----------------------------------------------------------
51 -- Insert a new stat or update an existing stat
52 ----------------------------------------------------------
53
54 procedure INSERT_OR_UPDATE_STATS(
55 P_APPNAME IN VARCHAR2,
56 P_GROUPNAME IN VARCHAR2,
57 P_TESTCLASSNAME IN VARCHAR2,
58 P_TIME IN DATE,
59 P_STATUS IN NUMBER,
60 P_SEQUENCE IN NUMBER,
61 P_LUBID IN NUMBER
62 ) IS
63 V_SEQUENCE NUMBER;
64 V_FAIL NUMBER := 0;
65 V_FAIL_TIME DATE;
66 V_FAIL_SEQ NUMBER;
67 V_RECORD_EXIST NUMBER := 0;
68
69 BEGIN
70
71 if P_STATUS <> 0 then
72 V_FAIL := 1;
73 V_FAIL_TIME := P_TIME;
74 V_FAIL_SEQ := P_SEQUENCE;
75 end if;
76
77 select count(*) into V_RECORD_EXIST
78 from JTF_DIAGNOSTIC_STATS where
79 appName = P_APPNAME
80 and groupName = P_GROUPNAME
81 and testClassName = P_TESTCLASSNAME;
82
83 if V_RECORD_EXIST = 0 then
84
85 select JTF_DIAGNOSTIC_LOG_S.nextval
86 into V_SEQUENCE from DUAL;
87
88 insert into JTF_DIAGNOSTIC_STATS(SEQUENCE, APPNAME, GROUPNAME, TESTCLASSNAME,
89 TOTALRUN, TOTALFAIL, LASTEXECUTIONTIME, LASTSTATUS,
90 LASTREPORTSEQUENCEID, LASTFAILURETIME, LASTFAILURESEQUENCEID,
91 OBJECT_VERSION_NUMBER, CREATED_BY,
92 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE)
93 values(V_SEQUENCE, P_APPNAME, P_GROUPNAME, P_TESTCLASSNAME, 1, V_FAIL, P_TIME, P_STATUS,
94 P_SEQUENCE, V_FAIL_TIME, V_FAIL_SEQ,
95 1, P_LUBID, SYSDATE, P_LUBID,
96 NULL, SYSDATE);
97
98 else
99
100 if (P_STATUS = 1 OR P_STATUS = 2) then
101
102 update JTF_DIAGNOSTIC_STATS
103 set totalRun = totalRun + 1,
104 totalFail = totalFail + 1,
105 lastExecutionTime = P_TIME,
106 lastStatus = P_STATUS,
107 lastReportSequenceID = P_SEQUENCE,
108 lastFailureTime = P_TIME,
109 lastFailureSequenceID = P_SEQUENCE,
110 last_updated_by = P_LUBID
111 where appName = P_APPNAME
112 and groupName = P_GROUPNAME
113 and testClassName = P_TESTCLASSNAME;
114
115 else
116
117 update JTF_DIAGNOSTIC_STATS
118 set totalRun = totalRun + 1,
119 lastExecutionTime = P_TIME,
120 lastStatus = P_STATUS,
121 lastReportSequenceID = P_SEQUENCE,
122 last_updated_by = P_LUBID
123 where appName = P_APPNAME
124 and groupName = P_GROUPNAME
125 and testClassName = P_TESTCLASSNAME;
126
127 end if;
128
129 end if;
130
131 END INSERT_OR_UPDATE_STATS;
132
133
134 ----------------------------------------------------------
135 -- Insert a new QALog entry in the DB
136 ----------------------------------------------------------
137
138 procedure INSERT_LOG(
139 P_SESSIONID IN VARCHAR2,
140 P_MIDTIERNODE IN VARCHAR2,
141 P_APPNAME IN VARCHAR2,
142 P_GROUPNAME IN VARCHAR2,
143 P_TESTCLASSNAME IN VARCHAR2,
144 P_TIME IN DATE,
145 P_STATUS IN NUMBER,
146 P_MILLISCONSUMED IN NUMBER,
147 P_MODE IN NUMBER,
148 P_INDEX IN NUMBER,
149 P_INSTALLVERSION IN VARCHAR2,
150 P_TOOLVERSION IN VARCHAR2,
151 P_TESTVERSION IN VARCHAR2,
152 P_INPUTS IN VARCHAR2,
153 P_ERROR IN VARCHAR2,
154 P_FIXINFO IN VARCHAR2,
155 P_REPORT IN CLOB,
156 P_VERSIONS IN VARCHAR2,
157 P_DEPENDENCIES IN VARCHAR2,
158 P_LUBID IN NUMBER,
159 P_SEQUENCE OUT NOCOPY NUMBER
160 ) IS
161 V_SEQUENCE NUMBER;
162
163 BEGIN
164 select JTF_DIAGNOSTIC_LOG_S.nextval
165 into V_SEQUENCE from DUAL;
166
167 insert into jtf_diagnostic_log( SEQUENCE, SESSIONID, MIDTIERNODE, APPNAME, GROUPNAME, TESTCLASSNAME, TIME, STATUS, MILLISCONSUMED, TESTMODE, TESTINDEX, INSTALLVERSION, TOOLVERSION, TESTVERSION, INPUTS, ERROR,
168 FIXINFO, REPORT, VERSIONS, DEPENDENCIES, OBJECT_VERSION_NUMBER,
169 CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
170 CREATION_DATE) values ( V_SEQUENCE, P_SESSIONID,
171 P_MIDTIERNODE, P_APPNAME, P_GROUPNAME, P_TESTCLASSNAME, P_TIME, P_STATUS,
172 P_MILLISCONSUMED, P_MODE, P_INDEX, P_INSTALLVERSION, P_TOOLVERSION,
173 P_TESTVERSION, P_INPUTS, P_ERROR, P_FIXINFO, P_REPORT, P_VERSIONS,
174 P_DEPENDENCIES, 1, P_LUBID, SYSDATE, P_LUBID,
175 NULL, SYSDATE);
176
177 P_SEQUENCE := V_SEQUENCE;
178
179 END INSERT_LOG;
180
181 procedure GET_REPORT_CLOB(
182 P_SEQUENCE IN NUMBER,
183 P_REPORT OUT NOCOPY CLOB
184 ) IS
185
186 BEGIN
187
188 select REPORT into P_REPORT
189 from JTF_DIAGNOSTIC_LOG
190 where sequence = P_SEQUENCE
191 for update;
192
193 IF SQL%NOTFOUND THEN
194 RAISE_APPLICATION_ERROR(-20000, 'Report CLOB not found');
195 END IF;
196
197 END GET_REPORT_CLOB;
198
199
200 procedure DELETE_EXPIRED_LOGS(
201 P_EXPIRATION IN DATE
202 ) IS
203
204 BEGIN
205
206 delete from jtf_diagnostic_log
207 where time <= P_EXPIRATION;
208
209 END DELETE_EXPIRED_LOGS;
210
211
212 END JTF_DIAGNOSTICLOG;