1 PACKAGE BODY FND_LOG_UTIL AS
2 /* $Header: AFUTLBEB.pls 115.9 2004/04/05 23:06:31 rmohan noship $ */
3
4 C_SUCCESS CONSTANT NUMBER := 0;
5 C_WARNING CONSTANT NUMBER := 1;
6 C_ERROR CONSTANT NUMBER := 2;
7
8 COUNT_COMMIT CONSTANT NUMBER := 500;
9
10
11 --------------------------------------------------------------------------------
12 -------------DEBUG METHODS
13 --------------------------------------------------------------------------------
14 procedure fdebug(msg in varchar2)
15 IS
16 l_msg VARCHAR2(1);
17 BEGIN
18 --- l_msg := dbms_utility.get_time || ' ' || msg;
19 --- dbms_output.put_line(dbms_utility.get_time || ' ' || msg);
20 --- fnd_file.put_line( fnd_file.log, dbms_utility.get_time || ' ' || msg);
21 l_msg := 'm';
22 END fdebug;
23
24
25 --------------------------------------------------------------------------------
26
27
28 --------------------------------------------------------------------------------
29 function IS_TRANSACTION_COMPLETED(trId in NUMBER, trType in VARCHAR2
30 ,sessId in NUMBER, ignoreICX in BOOLEAN) return BOOLEAN
31 IS
32 l_retu BOOLEAN;
33 l_phase_code FND_CONCURRENT_REQUESTS.PHASE_CODE%TYPE;
34 l_process_status_code FND_CONCURRENT_PROCESSES.PROCESS_STATUS_CODE%TYPE;
35 l_audsid_count NUMBER;
36 l_last_connect ICX_TRANSACTIONS.LAST_CONNECT%TYPE;
37
38 BEGIN
39 fdebug('In:FND_BE_UTIL.IS_TRANSACTION_COMPLETED');
40 fdebug('trId:'||trId ||',trType:'||trType ||',sessId:'||sessId );
41 l_retu := false;
42 IF (trType = 'REQUEST') THEN
43 select PHASE_CODE into l_phase_code
44 from FND_CONCURRENT_REQUESTS
45 where REQUEST_ID=trId;
46
47 IF (l_phase_code='C') THEN
48 l_retu:= TRUE;
49 END IF;
50 ELSIF (trType = 'FORM') THEN
51 select count(*) into l_audsid_count
52 from GV$SESSION
53 where AUDSID=trId;
54
55 IF (l_audsid_count = 0) THEN
56 l_retu:= TRUE;
57 END IF;
58 ELSIF (trType = 'SERVICE') THEN
59 select PROCESS_STATUS_CODE into l_process_status_code
60 from FND_CONCURRENT_PROCESSES
61 where CONCURRENT_PROCESS_ID=trId;
62
63 IF ((l_process_status_code='S') OR (l_process_status_code='K')) THEN
64 l_retu:= TRUE;
65 END IF;
66 ELSIF (trType = 'ICX') THEN
67 IF (ignoreICX = TRUE) THEN
68 l_retu:= TRUE;
69 ELSE
70 IF (trID <> NULL) THEN
71 select LAST_CONNECT into l_last_connect
72 from ICX_TRANSACTIONS
73 where TRANSACTION_ID=sessId;
74 ELSE
75 select max(LAST_CONNECT) into l_last_connect
76 from ICX_TRANSACTIONS
77 where SESSION_ID=sessId;
78 END IF;
79
80 IF (SYSDATE > l_last_connect + 1) THEN
81 l_retu:= TRUE;
82 END IF;
83 END IF;
84
85 END IF;
86 fdebug('Out:FND_BE_UTIL.IS_TRANSACTION_COMPLETED');
87 RETURN l_retu;
88 EXCEPTION
89 WHEN no_data_found THEN
90 RETURN TRUE;
91 WHEN OTHERS THEN
92 raise;
93 END IS_TRANSACTION_COMPLETED;
94 --------------------------------------------------------------------------------
95
96 --------------------------------------------------------------------------------
97 procedure METRIC_EVENT_PNDG_MTRCS(trCtxId in number, trId in NUMBER
98 , sessId in NUMBER, trType in VARCHAR2, err OUT NOCOPY VARCHAR2)
99 IS
100 BEGIN
101 fdebug('In:FND_BE_UTIL.METRIC_EVENT_PNDG_MTRCS');
102 IF (IS_TRANSACTION_COMPLETED(trId, trType, sessId, true)= TRUE) THEN
103 FND_LOG.WORK_METRICS_EVENT(trCtxId);
104 END IF;
105 fdebug('Out:FND_BE_UTIL.METRIC_EVENT_PNDG_MTRCS');
106 EXCEPTION
107 WHEN OTHERS THEN
108 FND_MESSAGE.CLEAR;
109 FND_MESSAGE.SET_NAME(application=>'FND', name=>'AF_OAM_BE_ERR_MP');
110 FND_MESSAGE.SET_TOKEN(token=>'TRANSACTION_CONTEXT_ID', value=>trCtxId);
111 FND_MESSAGE.SET_TOKEN(token=>'ERR_NUM', value=>SQLCODE);
112 FND_MESSAGE.SET_TOKEN(token=>'ERR_MSG', value=>SQLERRM);
113 err := FND_MESSAGE.GET;
114 FND_MESSAGE.CLEAR;
115
116 fdebug('Error:FND_BE_UTIL.METRIC_EVENT_PNDG_MTRCS err msg='||err);
117 --Don't raise;
118
119 END METRIC_EVENT_PNDG_MTRCS;
120 --------------------------------------------------------------------------------
121 --------------------------------------------------------------------------------
122 --------------------------------------------------------------------------------
123 --Actual Package Implementation------------------------------------------------------------------------------
124
125
126
127
128 --------------------------------------------------------------------------------
129 procedure METRIC_EVENT_PENDING_METRICS(errbuf OUT NOCOPY VARCHAR2
130 , retcode OUT NOCOPY VARCHAR2)
131 IS
132 err_msg VARCHAR2(2000);
133
134 CURSOR l_cur is
135 select
136 distinct fltc.SESSION_ID, fltc.TRANSACTION_CONTEXT_ID, fltc.TRANSACTION_TYPE
137 , fltc.TRANSACTION_ID
138 from
139 FND_LOG_TRANSACTION_CONTEXT fltc,
140 FND_LOG_METRICS flm
141 where
142 fltc.TRANSACTION_CONTEXT_ID=flm.TRANSACTION_CONTEXT_ID
143 and flm.EVENT_KEY is null;
144
145
146 BEGIN
147 fdebug('In:FND_BE_UTIL.MTERIC_EVENT_PENDING_METRICS');
148 retcode := C_SUCCESS;
149 FOR l_rec in l_cur LOOP
150 err_msg := null;
151 METRIC_EVENT_PNDG_MTRCS(
152 l_rec.TRANSACTION_CONTEXT_ID
153 ,l_rec.TRANSACTION_ID
154 ,l_rec.SESSION_ID
155 ,l_rec.TRANSACTION_TYPE
156 ,err_msg);
157 IF (err_msg is not NULL) THEN
158 fnd_file.put_line( fnd_file.log, err_msg);
159 retcode := C_WARNING;
160 END IF;
161 END LOOP; --subs_cur
162
163 --- fdebug('errbuf:::'||errbuf);
164 IF (retcode = C_WARNING) THEN
165 FND_MESSAGE.CLEAR;
166 FND_MESSAGE.SET_NAME(application=>'FND', name=>'AF_OAM_BE_ERR_MPS');
167 errbuf:=FND_MESSAGE.GET;
168 FND_MESSAGE.CLEAR;
169 END IF;
170 fnd_file.put_line( fnd_file.log, 'Posted metrics for all completed transactions');
171 fdebug('Out:FND_BE_UTIL.MTERIC_EVENT_PENDING_METRICS');
172 EXCEPTION
173 WHEN OTHERS THEN
174 errbuf := SQLERRM;
175 retcode := C_ERROR;
176 fdebug('Error:FND_BE_UTIL.MTERIC_EVENT_PENDING_METRICS msg' ||errbuf);
177 END METRIC_EVENT_PENDING_METRICS;
178
179
180
181
182
183 ----Testers
184 procedure SYNC_EXP_DATA
185 IS
186 l_rows_start NUMBER;
187 l_rows_end NUMBER;
188 BEGIN
189 --Detelet Transaction Context infor if its relevent infor NA
190 delete from FND_LOG_TRANSACTION_CONTEXT where
191 TRANSACTION_TYPE = 'REQUEST' AND TRANSACTION_ID<>
192 ( SELECT REQUEST_ID from FND_CONCURRENT_REQUESTS where REQUEST_ID=TRANSACTION_ID);
193
194 --Deletes data for which no transaction context info is available.
195 ---dbms_output.put_line('IN:SYNC_EXP_DATA');
196 select count(*) into l_rows_start from FND_LOG_MESSAGES;
197 delete from FND_LOG_MESSAGES where
198 TRANSACTION_CONTEXT_ID not in
199 (select distinct TRANSACTION_CONTEXT_ID from
200 FND_LOG_TRANSACTION_CONTEXT)
201 ;
202 select count(*) into l_rows_end from FND_LOG_MESSAGES;
203 ---dbms_output.put_line('FND_LOG_MESSAGES:dlt rows out of sync'||(l_rows_end-l_rows_start));
204 commit;
205
206 select count(*) into l_rows_start from FND_LOG_METRICS;
207 delete from FND_LOG_METRICS where
208 TRANSACTION_CONTEXT_ID not in
209 (select distinct TRANSACTION_CONTEXT_ID from
210 FND_LOG_TRANSACTION_CONTEXT)
211 ;
212 select count(*) into l_rows_end from FND_LOG_METRICS;
213 ---dbms_output.put_line('FND_LOG_METRICS:dlt rows out of sync'||(l_rows_end-l_rows_start));
214 commit;
215
216 select count(*) into l_rows_start from FND_LOG_EXCEPTIONS;
217 delete from FND_LOG_EXCEPTIONS where LOG_SEQUENCE not in
218 (select distinct LOG_SEQUENCE from FND_LOG_MESSAGES );
219 select count(*) into l_rows_end from FND_LOG_EXCEPTIONS;
220 ---dbms_output.put_line('FND_LOG_EXCEPTIONS: dlt rows out of sync'||(l_rows_end-l_rows_start));
221 commit;
222
223
224 select count(*) into l_rows_start from FND_LOG_UNIQUE_EXCEPTIONS;
225 delete from FND_LOG_UNIQUE_EXCEPTIONS where UNIQUE_EXCEPTION_ID not in
226 (select distinct UNIQUE_EXCEPTION_ID from FND_LOG_EXCEPTIONS );
227 select count(*) into l_rows_end from FND_LOG_UNIQUE_EXCEPTIONS;
228 ---dbms_output.put_line('FND_LOG_UNIQUE_EXCEPTIONS: dlt rows out of sync'||(l_rows_end-l_rows_start));
229 commit;
230
231
232 select count(*) into l_rows_start from FND_EXCEPTION_NOTES;
233 delete from FND_EXCEPTION_NOTES where UNIQUE_EXCEPTION_ID not in
234 (select distinct UNIQUE_EXCEPTION_ID from FND_LOG_UNIQUE_EXCEPTIONS );
235 select count(*) into l_rows_end from FND_EXCEPTION_NOTES;
236 ---dbms_output.put_line('FND_EXCEPTION_NOTES: dlt rows out of sync'||(l_rows_end-l_rows_start));
237 commit;
238
239
240 select count(*) into l_rows_start from FND_OAM_BIZEX_SENT_NOTIF;
241 delete from FND_OAM_BIZEX_SENT_NOTIF where UNIQUE_EXCEPTION_ID not in
242 (select distinct UNIQUE_EXCEPTION_ID from FND_LOG_UNIQUE_EXCEPTIONS );
243 select count(*) into l_rows_end from FND_OAM_BIZEX_SENT_NOTIF;
244 ---dbms_output.put_line('FND_OAM_BIZEX_SENT_NOTIF: dlt rows out of sync'||(l_rows_end-l_rows_start));
245 commit;
246
247
248
249
250
251
252
253
254 ---dbms_output.put_line('OUT:SYNC_EXP_DATA');
255 EXCEPTION
256 WHEN OTHERS THEN
257 raise;
258 END SYNC_EXP_DATA;
259
260
261
262
263 END FND_LOG_UTIL;
[Home] [Help]