[Home] [Help]
PACKAGE BODY: APPS.FA_ASSET_TRACE_PUB
Source
1 PACKAGE BODY FA_ASSET_TRACE_PUB AS
2 /* $Header: faxtrcpb.pls 120.0.12010000.3 2009/07/19 08:21:10 glchen noship $
3 * */
4
5 PROCEDURE hook_back;
6
7 g_options_tbl t_options_tbl;
8 g_schema VARCHAR2(50);
9 --
10 --
11 PROCEDURE run_trace (p_opt_tbl IN t_options_tbl,
12 p_exc_tbl IN t_excl_tbl,
13 p_tdyn_head IN VARCHAR2,
14 p_stmt IN VARCHAR2,
15 p_sys_opt_tbl IN VARCHAR2 DEFAULT NULL,
16 p_use_utl_file IN VARCHAR2 DEFAULT 'N',
17 p_debug_flag IN BOOLEAN,
18 p_calling_prog IN VARCHAR2,
19 p_retcode OUT NOCOPY NUMBER,
20 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
21
22 l_calling_fn varchar2(40) := 'fa_asset_trace_pub.run_trace';
23
24 error_found1 EXCEPTION;
25 error_found2 EXCEPTION;
26
27 BEGIN
28
29 FA_ASSET_TRACE_PVT.initialize_globals (p_opt_tbl => p_opt_tbl,
30 p_exc_tbl => p_exc_tbl,
31 p_schema => g_schema,
32 p_debug_flag => p_debug_flag);
33 if (p_stmt is not null) then
34 do_top_section (p_tdyn_head => p_tdyn_head,
35 p_stmt => p_stmt);
36 end if;
37
38 FA_ASSET_TRACE_PVT.do_primary;
39 hook_back;
40
41 if (p_sys_opt_tbl is not null) then
42 FA_ASSET_TRACE_PVT.get_system_options (p_sys_opt_tbl);
43 end if;
44
45 FA_ASSET_TRACE_PVT.save_output (p_calling_prog, p_use_utl_file);
46
47 p_retcode := 0;
48
49 EXCEPTION
50 WHEN ERROR_FOUND1 THEN
51 LOG(l_calling_fn, 'ERROR_FOUND1 Exception');
52 p_retcode :=2;
53 WHEN OTHERS THEN
54 LOG(l_calling_fn, 'OTHERS Exception');
55 p_retcode :=2;
56 raise;
57
58 END run_trace;
59 --
60 PROCEDURE wait_for_req is
61
62 l_phase varchar2(2000);
63 l_status varchar2(2000);
64 l_dev_phase varchar2(2000);
65 l_dev_status varchar2(2000);
66 l_message varchar2(2000);
67 l_request_id number := 0;
68 l_req_status boolean;
69
70 l_calling_fn varchar2(40) := 'fa_asset_trace_pub.wait_for_req';
71
72 BEGIN
73 if g_req_tbl.count > 1 then
74 FOR i IN g_req_tbl.first .. g_req_tbl.last LOOP
75 l_request_id := g_req_tbl(i);
76 if l_request_id not in (0,-1) then
77 log(l_calling_fn,'Waiting for request '||to_char(l_request_id));
78 while (FND_CONCURRENT.CHILDREN_DONE(Parent_Request_ID => l_request_id,
79 Interval => 20,
80 Max_Wait => 120) = FALSE) loop
81 LOG(l_calling_fn, 'Waiting for all sub-requests to complete.');
82 end loop; --children_done
83
84 LOOP
85 l_req_status := FND_CONCURRENT.wait_for_request(
86 request_id => l_request_id, interval => 60,
87 max_wait => 60, phase => l_phase,
88 status => l_status, dev_phase => l_dev_phase,
89 dev_status => l_dev_status, message => l_message);
90 IF (l_dev_phase = 'COMPLETE') THEN
91 EXIT;
92 END IF;
93 END LOOP;
94 log(l_calling_fn,'Done waiting. finished with status '||l_status);
95 else
96 dbms_lock.sleep(90);
97 end if; --l_request_id
98 g_req_tbl.delete(i);
99 END LOOP; --FOR i IN g_req_tbl.first...
100 else
101 g_req_tbl.delete(1);
102 log(l_calling_fn, 'Not waiting.');
103 end if;
104
105 EXCEPTION
106 WHEN OTHERS THEN
107 LOG(l_calling_fn, 'Error');
108 raise;
109
110 END wait_for_req;
111 --
112 --Set banner area content
113 --
114 PROCEDURE set_temp_head (p_temp_head IN VARCHAR2,
115 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
116
117 l_calling_fn varchar2(40) := 'fa_asset_trace_pub.set_temp_head';
118
119 BEGIN
120
121 FA_ASSET_TRACE_PVT.g_temp_head := p_temp_head;
122
123 EXCEPTION
124 WHEN OTHERS THEN
125 LOG(l_calling_fn, 'Error');
126 raise;
127
128 END set_temp_head;
129 --
130 --Used to call any special case code from calling apps.
131 --
132 PROCEDURE hook_back IS
133
134 l_calling_fn varchar2(40) := 'fa_asset_trace_pub.hook_back';
135
136 BEGIN
137
138 --Add any hooks here
139 null;
140
141 EXCEPTION
142 WHEN OTHERS THEN
143 LOG(l_calling_fn, 'Error');
144 raise;
145
146 END hook_back;
147 --
148 PROCEDURE do_top_section (p_tdyn_head IN VARCHAR2,
149 p_stmt IN VARCHAR2,
150 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
151
152 l_calling_fn varchar2(40) := 'fa_asset_trace_pub.do_top_section';
153 BEGIN
154
155 FA_ASSET_TRACE_PVT.g_dyn_head := p_tdyn_head;
156
157 FA_ASSET_TRACE_PVT.exec_sql (p_table => 'NO_ANCHOR',
158 p_sel_clause => NULL,
159 p_stmt => p_stmt,
160 p_schema => 'NONE');
161 EXCEPTION
162 WHEN OTHERS THEN
163 LOG(l_calling_fn, 'Error');
164 raise;
165
166 END do_top_section;
167 --
168 FUNCTION start_queue (p_qtable IN varchar2,
169 p_qpayload_type IN varchar2,
170 p_qname IN varchar2) RETURN VARCHAR2 IS
171
172 l_ret_val varchar2(3);
173
174 qtblexists exception;
175 pragma exception_init(qtblexists, -24001);
176 qexists exception;
177 pragma exception_init(qexists, -24006);
178
179 BEGIN
180
181 BEGIN
182 DBMS_AQADM.CREATE_QUEUE_TABLE (queue_table => p_qtable,
183 multiple_consumers => TRUE,
184 queue_payload_type => p_qpayload_type,
185 compatible => '8.1');
186 DBMS_AQADM.CREATE_QUEUE (queue_name => p_qname,
187 queue_table => p_qtable,
188 max_retries => 2);
189 DBMS_AQADM.START_QUEUE (queue_name => p_qname);
190 EXCEPTION
191 WHEN qtblexists THEN
192 l_ret_val := 'TE'; --Table Exists
193 WHEN qexists THEN
194 l_ret_val := 'QE'; --Queue Exists
195 WHEN others THEN
196 l_ret_val := 'F'; --Some other failure
197 END; --Annonymous
198
199 IF l_ret_val IS NULL THEN
200 l_ret_val := 'S'; --Success
201 END IF;
202 RETURN l_ret_val;
203
204 EXCEPTION
205 WHEN OTHERS THEN
206 log('start_queue','Unspecified Error');
207
208 END start_queue;
209 --
210 FUNCTION add_subscriber (p_qname IN varchar2,
211 p_subscriber IN varchar2,
212 p_sub_rule IN varchar2) RETURN BOOLEAN IS
213
214 l_subscriber sys.aq$_agent;
215 sub_exists exception;
216 pragma exception_init(sub_exists, -24034);
217
218 BEGIN
219 l_subscriber := sys.aq$_agent(p_subscriber, p_qname, null);
220 dbms_aqadm.add_subscriber (queue_name => p_qname,
221 subscriber => l_subscriber,
222 rule => p_sub_rule);
223 RETURN TRUE;
224
225 EXCEPTION
226 WHEN sub_exists THEN
227 RETURN TRUE;
228 WHEN OTHERS THEN
229 log('add_subscriber','Unspecified Error');
230 raise;
231
232 END add_subscriber;
233 --
234 FUNCTION drop_queue (p_qtable IN varchar2) RETURN BOOLEAN IS
235
236 qnotthere exception;
237 pragma exception_init(qnotthere, -24010);
238
239 BEGIN
240 DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => p_qtable, force => TRUE);
241
242 EXCEPTION
243 WHEN qnotthere THEN
244 RETURN TRUE;
245 WHEN OTHERS THEN
246 log('drop_queue','Unspecified Error');
247 END drop_queue;
248 --
249 --Write to log
250 --
251 PROCEDURE log(p_calling_fn IN VARCHAR2,
252 p_msg IN VARCHAR2 default null,
253 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
254
255 BEGIN
256
257 FA_ASSET_TRACE_PVT.LOG(p_calling_fn,p_msg);
258
259 EXCEPTION
260 When Others Then Raise;
261
262 END log;
263
264 --
265 END FA_ASSET_TRACE_PUB;