[Home] [Help]
PACKAGE BODY: APPS.FND_APD
Source
1 package body fnd_apd as
2 /* $Header: AFRTPRFB.pls 120.1.12020000.2 2012/08/07 11:37:56 avmurthy ship $ */
3
4 /*
5 * Procedure: collect
6 *
7 * Purpose:
8 *
9 * Arguments:
10 *
11 */
12 PROCEDURE collect is
13 p_req_id NUMBER;
14 session NUMBER;
15 parentreqid NUMBER;
16 vdbname varchar2(16);
17 vversion varchar2(17);
18 vsql varchar2(256);
19 errmsg varchar2(80);
20 tracefile varchar2(30);
21 udump varchar2(64);
22 module_id varchar2(10);
23 flow_id varchar2(30);
24 vduration NUMBER;
25 sdate date;
26 cdate date;
27 status_code varchar2(1);
28 phase_code varchar2(1);
29 BEGIN
30 p_req_id := fnd_global.conc_request_id;
31
32 BEGIN
33 SELECT sid
34 INTO session
35 FROM v$session
36 WHERE audsid = userenv('SESSIONID');
37 EXCEPTION
38 when no_data_found then
39 fnd_file.put_line(fnd_file.log,'fnd_apd:Collect:Error while getting session Id ');
40 when others then
41 fnd_file.put_line(fnd_file.log,'fnd_apd:Collect:Error while getting session Id ');
42 errmsg := substr(sqlerrm,1,80);
43 fnd_file.put_line(fnd_file.log,errmsg);
44 END;
45
46 BEGIN
47 SELECT value
48 INTO udump
49 FROM v$parameter
50 WHERE name = 'user_dump_dest';
51 EXCEPTION
52 when no_data_found then
53 fnd_file.put_line(fnd_file.log,'fnd_apd:Collect:Error getting user dump destination ');
54 when others then
55 fnd_file.put_line(fnd_file.log,'fnd_apd:Collect:Error getting user dump destination ');
56 errmsg := substr(sqlerrm,1,80);
57 fnd_file.put_line(fnd_file.log,errmsg);
58 END;
59
60 BEGIN
61 select application_short_name , concurrent_program_name,
62 round((sysdate-fcr.actual_start_date)*24*60*60,2)
63 , fcr.phase_code , fcr.status_code , actual_start_date,
64 actual_completion_date,
65 lower(instance_name) || '_ora_' || fcr.oracle_process_id
66 || '.trc' , instance_name, parent_request_id,
67 version
68 into module_id, flow_id, vduration, phase_code , status_code,
69 sdate, cdate, tracefile , vdbname, parentreqid, vversion
70 from fnd_concurrent_requests fcr, fnd_application fa,
71 fnd_concurrent_programs fcp, v$instance
72 where fcr.request_id = p_req_id
73 and fcr.program_application_id = fa.application_id
74 and fcr.concurrent_program_id = fcp.concurrent_program_id
75 and fcr.program_application_id = fcp.application_id;
76 EXCEPTION
77 when no_data_found then
78 fnd_file.put_line(fnd_file.log,'fnd_apd:Collect:Error getting data from FND tables...');
79 when others then
80 fnd_file.put_line(fnd_file.log,'fnd_apd:Collect:Error getting data from FND tables...');
81 errmsg := substr(sqlerrm,1,80);
82 fnd_file.put_line(fnd_file.log,errmsg);
83 END;
84
85 BEGIN
86 vsql := 'begin gatherCPStat@rtperf(:1,:2,:3, ''C'',:4, :5, :6, :7, :8, :9 , :10); end; ';
87 execute immediate vsql using p_req_id,session,vdbname,parentreqid,vversion,tracefile,udump,module_id,flow_id,vduration;
88 EXCEPTION
89 when others then
90 errmsg := substr(sqlerrm,1,80);
91 fnd_file.put_line(fnd_file.log,'fnd_apd:Collect:Error during remote procedure call');
92 fnd_file.put_line(fnd_file.log,errmsg);
93 END;
94
95 END collect;
96
97 /*
98 * Procedure: store_initial
99 *
100 * Purpose:
101 *
102 * Arguments:
103 *
104 */
105 PROCEDURE store_initial is
106 p_req_id NUMBER;
107 session NUMBER;
108 parentreqid NUMBER;
109 vdbname varchar2(16);
110 vversion varchar2(17);
111 vsql varchar2(256);
112 errmsg varchar2(80);
113 tracefile varchar2(30);
114 udump varchar2(64);
115 module_id varchar2(10);
116 flow_id varchar2(30);
117 vduration NUMBER;
118 sdate date;
119 cdate date;
120 status_code varchar2(1);
121 phase_code varchar2(1);
122 BEGIN
123 p_req_id := fnd_global.conc_request_id;
124 BEGIN
125 vsql := 'alter session set tracefile_identifier = ' || p_req_id ;
126 execute immediate vsql ;
127 EXCEPTION
128 when others then
129 errmsg := substr(sqlerrm,1,80);
130 fnd_file.put_line(fnd_file.log,errmsg);
131 END;
132 BEGIN
133 SELECT sid
134 INTO session
135 FROM v$session
136 WHERE audsid = userenv('SESSIONID');
137 EXCEPTION
138 when no_data_found then
139 fnd_file.put_line(fnd_file.log,'fnd_apd:store_initial:Error while getting session Id ');
140 when others then
141 fnd_file.put_line(fnd_file.log,'fnd_apd:store_initial:Error while getting session Id ');
142 errmsg := substr(sqlerrm,1,80);
143 fnd_file.put_line(fnd_file.log,errmsg);
144 END;
145
146 BEGIN
147 SELECT value
148 INTO udump
149 FROM v$parameter
150 WHERE name = 'user_dump_dest';
151 EXCEPTION
152 when no_data_found then
153 fnd_file.put_line(fnd_file.log,'fnd_apd:store_initial:Error getting user dump destination ');
154 when others then
155 fnd_file.put_line(fnd_file.log,'fnd_apd:store_initial:Error getting user dump destination ');
156 errmsg := substr(sqlerrm,1,80);
157 fnd_file.put_line(fnd_file.log,errmsg);
158 END;
159
160 BEGIN
161 select application_short_name , concurrent_program_name,
162 lower(instance_name) || '_ora_' || fcr.oracle_process_id
163 || '_' || p_req_id || '.trc' , instance_name, parent_request_id,
164 version
165 into module_id, flow_id, tracefile , vdbname, parentreqid, vversion
166 from fnd_concurrent_requests fcr, fnd_application fa,
167 fnd_concurrent_programs fcp, v$instance
168 where fcr.request_id = p_req_id
169 and fcr.program_application_id = fa.application_id
170 and fcr.concurrent_program_id = fcp.concurrent_program_id
171 and fcr.program_application_id = fcp.application_id;
172 EXCEPTION
173 when no_data_found then
174 errmsg := substr(sqlerrm,1,80);
175 fnd_file.put_line(fnd_file.log,'fnd_apd:store_initial:Error getting data from FND tables...');
176 when others then
177 fnd_file.put_line(fnd_file.log,'fnd_apd:store_initial:Error getting data from FND tables...');
178 errmsg := substr(sqlerrm,1,80);
179 fnd_file.put_line(fnd_file.log,errmsg);
180 END;
181 BEGIN
182 vsql := 'begin gatherCPStat@rtperf(:1,:2,:3, ''I'',:4, :5, :6, :7, :8, :9 , :10); end; ';
183 execute immediate vsql using p_req_id,session,vdbname,parentreqid,vversion,tracefile,udump,module_id,flow_id,vduration;
184 EXCEPTION
185 when others then
186 errmsg := substr(sqlerrm,1,80);
187 fnd_file.put_line(fnd_file.log,'fnd_apd:store_initial:Error during remote procedure call');
188 fnd_file.put_line(fnd_file.log,errmsg);
189 END;
190 END store_initial;
191
192 /*
193 * Procedure: store_final
194 *
195 * Purpose:
196 *
197 * Arguments:
198 *
199 */
200 PROCEDURE store_final is
201 p_req_id NUMBER;
202 session NUMBER;
203 parentreqid NUMBER;
204 vdbname varchar2(16);
205 vversion varchar2(17);
206 vsql varchar2(256);
207 errmsg varchar2(80);
208 tracefile varchar2(30);
209 udump varchar2(64);
210 module_id varchar2(10);
211 flow_id varchar2(30);
212 vduration NUMBER;
213 sdate date;
214 cdate date;
215 status_code varchar2(1);
216 phase_code varchar2(1);
217 BEGIN
218 p_req_id := fnd_global.conc_request_id;
219 BEGIN
220 SELECT sid
221 INTO session
222 FROM v$session
223 WHERE audsid = userenv('SESSIONID');
224 EXCEPTION
225 when no_data_found then
226 fnd_file.put_line(fnd_file.log,'fnd_apd:store_final:Error while getting session Id ');
227 when others then
228 fnd_file.put_line(fnd_file.log,'fnd_apd:store_final:Error while getting session Id ');
229 errmsg := substr(sqlerrm,1,80);
230 fnd_file.put_line(fnd_file.log,errmsg);
231 END;
232
233 BEGIN
234 SELECT value
235 INTO udump
236 FROM v$parameter
237 WHERE name = 'user_dump_dest';
238 EXCEPTION
239 when no_data_found then
240 fnd_file.put_line(fnd_file.log,'fnd_apd:store_final:Error getting user dump destination ');
241 when others then
242 fnd_file.put_line(fnd_file.log,'fnd_apd:store_final:Error getting user dump destination ');
243 errmsg := substr(sqlerrm,1,80);
244 fnd_file.put_line(fnd_file.log,errmsg);
245 END;
246
247 BEGIN
248 select application_short_name , concurrent_program_name,
249 round((sysdate-fcr.actual_start_date)*24*60*60,2),
250 lower(instance_name) || '_ora_' || fcr.oracle_process_id
251 || '_' || p_req_id || '.trc' , instance_name, parent_request_id,
252 version
253 into module_id,flow_id,vduration,tracefile,vdbname,parentreqid,vversion
254 from fnd_concurrent_requests fcr, fnd_application fa,
255 fnd_concurrent_programs fcp, v$instance
256 where fcr.request_id = p_req_id
257 and fcr.program_application_id = fa.application_id
258 and fcr.concurrent_program_id = fcp.concurrent_program_id;
259 EXCEPTION
260 when no_data_found then
261 fnd_file.put_line(fnd_file.log,'fnd_apd:store_final:Error getting data from FND tables...');
262 when others then
263 fnd_file.put_line(fnd_file.log,'fnd_apd:store_final:Error getting data from FND tables...');
264 errmsg := substr(sqlerrm,1,80);
265 fnd_file.put_line(fnd_file.log,errmsg);
266 END;
267 BEGIN
268 vsql := 'begin gatherCPStat@rtperf(:1,:2,:3, ''F'',:4, :5, :6, :7, :8, :9 , :10); end; ';
269 execute immediate vsql using p_req_id,session,vdbname,parentreqid,vversion,tracefile,udump,module_id,flow_id,vduration;
270 EXCEPTION
271 when others then
272 fnd_file.put_line(fnd_file.log,'fnd_apd:store_final:Error during remote procedure call');
273 errmsg := substr(sqlerrm,1,80);
274 fnd_file.put_line(fnd_file.log,errmsg);
275 END;
276
277 END store_final;
278
279
280 PROCEDURE RTTrace is
281 icxid number ;
282 vspid number ;
283 vsql varchar2(1024) ;
284 vinstance varchar2(16) ;
285 vtechstack varchar2(16) ;
286 vrequest_id number;
287 errmsg varchar2(80);
288 FUNCTION rt_perf_stat_enabled RETURN BOOLEAN IS
289 rt_perf BOOLEAN := FALSE;
290 rt_perf_val VARCHAR2(1);
291 BEGIN
292 if( fnd_profile.defined('RT_PERF_STAT') ) then
293 fnd_profile.get('RT_PERF_STAT', rt_perf_val );
294 if ( rt_perf_val = 'Y' ) then
295 rt_perf := TRUE;
296 end if;
297 end if;
298 return rt_perf;
299 END;
300
301 BEGIN
302 -- Call performace package.
303 if ( rt_perf_stat_enabled ) then
304
305 BEGIN
306 --icxid := icx_sec.g_session_id;
307 icxid := fnd_global.session_id;
308 if (fnd_global.form_id > 0) then
309 vtechstack := 'F';
310 elsif (fnd_global.conc_request_id > 0) then
311 vrequest_id := fnd_global.conc_request_id ;
312 vtechstack := 'C';
313 else
314 vtechstack := 'J';
315 end if;
316 if ( icxid > 0 ) then
317 vsql := 'alter session set tracefile_identifier = ''' || icxid || '''' ;
318 else
319 vsql := 'alter session set tracefile_identifier = ''' || '''' ;
320 end if;
321 EXECUTE IMMEDIATE vsql ;
322 vsql := 'alter session set events ''10046 trace name context forever , level 8''';
323 EXECUTE IMMEDIATE vsql ;
324 END;
325 BEGIN
326 select spid into vspid
327 from v$process p,v$session s
328 where p.addr = s.paddr
329 and s.audsid = userenv('SESSIONID');
330 EXCEPTION
331 when no_data_found then
332 raise_application_error(-20100,'Error select SPID ...');
333 when others then
334 errmsg := substr(sqlerrm,1,80);
335 raise_application_error(-20100,errmsg);
336 END;
337 BEGIN
338 select instance_name
339 into vinstance
340 from v$instance i;
341 EXCEPTION
342 when no_data_found then
343 raise_application_error(-20100,'Error select Instance ...');
344 when others then
345 errmsg := substr(sqlerrm,1,80);
346 raise_application_error(-20100,errmsg);
347 END;
348 BEGIN
349 if ( icxid > 0 ) then
350 vsql := 'insert into rt_icx_data@rtperf
351 ( icxid , spid , instance , techstack , creation_date, request_id) select :b1 , :b2, upper(:b3), :b4, sysdate, :b5 from dual
352 where not exists ( select ''x'' from rt_icx_data@rtperf
353 where icxid = :b6 and spid = :b7 and instance = upper(:b8)
354 and techstack = upper(:b9)) ';
355 EXECUTE IMMEDIATE vsql using icxid , vspid , vinstance, vtechstack, vrequest_id, icxid, vspid, vinstance, vtechstack;
356 commit;
357 end if;
358 EXCEPTION
359 when others then
360 rollback;
361 errmsg := substr(sqlerrm,1,80);
362 raise_application_error(-20100,errmsg);
363 END;
364 end if;
365 END RTTrace;
366
367 end;