[Home] [Help]
PACKAGE BODY: APPS.ISC_FS_EVENT_LOG_ETL_PKG
Source
1 package body isc_fs_event_log_etl_pkg
2 /* $Header: iscfsevntlogetlb.pls 120.1 2005/11/24 18:31:14 kreardon noship $ */
3 as
4
5 function check_dep_arr_task
6 ( p_task_id in number
7 , p_task_audit_id in number
8 )
9 return varchar2
10 is
11 cursor c_task is
12 select
13 'Y'
14 from jtf_tasks_b t
15 where
16 t.task_id = p_task_id
17 -- R12 dep/arr
18 and t.task_type_id = 20;
19
20 cursor c_task_audit is
21 select
22 'Y'
23 from jtf_task_audits_b t
24 where
25 t.task_audit_id = p_task_audit_id
26 -- R12 dep/arr
27 and ( ( t.old_source_object_type_code = 'TASK' and t.old_task_type_id = 20 ) or
28 ( t.new_source_object_type_code = 'TASK' and t.new_task_type_id = 20 )
29 );
30
31 l_interested varchar2(1);
32
33 begin
34
35 l_interested := 'N';
36
37 if p_task_audit_id is null then
38 open c_task;
39 fetch c_task into l_interested;
40 close c_task;
41 else
42 open c_task_audit;
43 fetch c_task_audit into l_interested;
44 close c_task_audit;
45 end if;
46
47 return l_interested;
48
49 end check_dep_arr_task;
50
51 -- -------------------------------------------------------------------
52 -- PUBLIC PROCEDURES
53 -- -------------------------------------------------------------------
54
55 function check_events_enabled
56 return varchar2
57 is
58
59 cursor c_check is
60 select enabled
61 from isc_fs_enable_events;
62
63 l_enabled varchar2(1);
64
65 begin
66 open c_check;
67 fetch c_check into l_enabled;
68 close c_check;
69 return nvl(l_enabled,'N');
70 end check_events_enabled;
71
72 function enable_events
73 ( x_error_message out nocopy varchar2 )
74 return number
75 is
76 begin
77
78 update isc_fs_enable_events
79 set enabled = 'Y'
80 , last_updated_by = fnd_global.user_id
81 , last_update_date = sysdate
82 , last_update_login = fnd_global.login_id
83 , program_id = fnd_global.conc_program_id
84 , program_login_id = fnd_global.conc_login_id
85 , program_application_id = fnd_global.prog_appl_id
86 , request_id = fnd_global.conc_request_id;
87
88 if sql%rowcount = 0 then
89
90 insert into
91 isc_fs_enable_events
92 ( enabled
93 , created_by
94 , creation_date
95 , last_updated_by
96 , last_update_date
97 , last_update_login
98 , program_id
99 , program_login_id
100 , program_application_id
101 , request_id
102 )
103 values
104 ( 'Y'
105 , fnd_global.user_id
106 , sysdate
107 , fnd_global.user_id
108 , sysdate
109 , fnd_global.login_id
110 , fnd_global.conc_program_id
111 , fnd_global.conc_login_id
112 , fnd_global.prog_appl_id
113 , fnd_global.conc_request_id
114 );
115
116 end if;
117
118 -- note: the procedure does not perform the commit
119
120 return 0;
121
122 exception
123 when others then
124 x_error_message := 'Error in function enable_events : ' || sqlerrm;
125 return -1;
126 end enable_events;
127
128 function log_task
129 ( p_subscription_guid in raw
130 , p_event in out nocopy wf_event_t
131 )
132 return varchar2
133 is
134 l_send_date constant date := p_event.send_date;
135 l_event_name constant varchar2(240) := p_event.event_name;
136 l_task_id constant number := p_event.GetValueForParameter('TASK_ID');
137 l_task_audit_id constant number := p_event.GetValueForParameter('TASK_AUDIT_ID');
138
139 l_source_object_type_code varchar2(60);
140 l_source_object_id number;
141 l_interested varchar2(1);
142
143 begin
144
145 savepoint log_task;
146
147 if check_events_enabled <> 'Y' then
148
149 wf_event.addparametertolist
150 ( p_name => 'X_RETURN_STATUS'
151 , p_value => 'SUCCESS'
152 , p_parameterlist => p_event.parameter_list
153 );
154
155 return 'SUCCESS';
156
157 end if;
158
159 l_source_object_type_code := p_event.GetValueForParameter('SOURCE_OBJECT_TYPE_CODE');
160 l_source_object_id := p_event.GetValueForParameter('SOURCE_OBJECT_ID');
161
162 if l_source_object_type_code = 'SR' or
163 l_source_object_type_code = 'TASK' then
164
165 if l_source_object_type_code = 'SR' then
166 l_interested := 'Y';
167 else
168 l_interested := check_dep_arr_task
169 ( l_task_id
170 , l_task_audit_id
171 );
172 end if;
173
174 if l_interested = 'Y' then
175 insert into isc_fs_events
176 ( send_date
177 , event_name
178 , source_object_type_code
179 , source_object_id
180 , task_id
181 , task_audit_id
182 , created_by
183 , creation_date
184 , last_updated_by
185 , last_update_date
186 , last_update_login
187 )
188 values
189 ( l_send_date
190 , l_event_name
191 , l_source_object_type_code
192 , l_source_object_id
193 , l_task_id
194 , l_task_audit_id
195 , fnd_global.user_id
196 , sysdate
197 , fnd_global.user_id
198 , sysdate
199 , fnd_global.login_id
200 );
201 end if;
202
203 end if;
204
205 wf_event.addparametertolist
206 ( p_name => 'X_RETURN_STATUS'
207 , p_value => 'SUCCESS'
208 , p_parameterlist => p_event.parameter_list
209 );
210
211 return 'SUCCESS';
212
213 exception
214 when others then
215 rollback to savepoint log_task;
216 fnd_message.set_name ('CS', 'CS_API_SR_UNKNOWN_ERROR');
217 fnd_message.set_token ('P_TEXT','isc_fs_event_log_pkg.log_task: '||SQLERRM );
218 fnd_msg_pub.ADD;
219 wf_event.addparametertolist
220 ( p_name => 'X_RETURN_STATUS'
221 , p_value => 'ERROR'
222 , p_parameterlist => p_event.parameter_list
223 );
224 return 'ERROR';
225
226 end log_task;
227
228 function log_task_assignment
229 ( p_subscription_guid in raw
230 , p_event in out nocopy wf_event_t
231 )
232 return varchar2
233 is
234 cursor c_task( b_task_id number ) is
235 select
236 source_object_type_code
237 , source_object_id
238 from
239 jtf_tasks_b
240 where task_id = b_task_id
241 and ( source_object_type_code = 'SR' or
242 ( source_object_type_code = 'TASK' and
243 -- R12 dep/arr
244 task_type_id = 20
245 )
246 );
247
248 l_send_date constant date := p_event.send_date;
249 l_event_name constant varchar2(240) := p_event.event_name;
250 l_task_id constant number := p_event.GetValueForParameter('TASK_ID');
251 l_task_assignment_id constant number := p_event.GetValueForParameter('TASK_ASSIGNMENT_ID');
252 l_assignee_role constant varchar2(30) := p_event.GetValueForParameter('ASSIGNEE_ROLE');
253
254 l_source_object_type_code varchar2(60);
255 l_source_object_id number;
256
257 begin
258
259 savepoint log_task_assignment;
260
261 if check_events_enabled <> 'Y' then
262
263 wf_event.addparametertolist
264 ( p_name => 'X_RETURN_STATUS'
265 , p_value => 'SUCCESS'
266 , p_parameterlist => p_event.parameter_list
267 );
268
269 return 'SUCCESS';
270
271 end if;
272
273 if l_assignee_role = 'ASSIGNEE' then
274
275 open c_task( l_task_id );
276 fetch c_task into l_source_object_type_code, l_source_object_id;
277 if c_task%found then
278
279 insert into isc_fs_events
280 ( send_date
281 , event_name
282 , source_object_type_code
283 , source_object_id
284 , task_id
285 , task_assignment_id
286 , created_by
287 , creation_date
288 , last_updated_by
289 , last_update_date
290 , last_update_login
291 )
292 values
293 ( l_send_date
294 , l_event_name
295 , l_source_object_type_code
296 , l_source_object_id
297 , l_task_id
298 , l_task_assignment_id
299 , fnd_global.user_id
300 , sysdate
301 , fnd_global.user_id
302 , sysdate
303 , fnd_global.login_id
304 );
305
306 end if;
307 close c_task;
308
309 end if;
310
311 wf_event.addparametertolist
312 ( p_name => 'X_RETURN_STATUS'
313 , p_value => 'SUCCESS'
314 , p_parameterlist => p_event.parameter_list
315 );
316
317 return 'SUCCESS';
318
319 exception
320 when others then
321 rollback to savepoint log_task_assignment;
322 if c_task%isopen then
323 close c_task;
324 end if;
325 fnd_message.set_name ('CS', 'CS_API_SR_UNKNOWN_ERROR');
326 fnd_message.set_token ('P_TEXT','isc_fs_event_log_pkg.log_task_assignment: '||SQLERRM );
327 fnd_msg_pub.ADD;
328 wf_event.addparametertolist
329 ( p_name => 'X_RETURN_STATUS'
330 , p_value => 'ERROR'
331 , p_parameterlist => p_event.parameter_list
332 );
333 return 'ERROR';
334
335 end log_task_assignment;
336
337 function log_sr
338 ( p_subscription_guid in raw
339 , p_event in out nocopy wf_event_t
340 )
341 return varchar2
342 is
343
344 cursor c_task( b_incident_number varchar2 ) is
345 select
346 i.incident_id
347 from
348 jtf_tasks_b t
349 , cs_incidents_all_b i
350 where
351 t.source_object_type_code = 'SR'
352 and t.source_object_id = i.incident_id
353 and i.incident_number = b_incident_number;
354
355 l_send_date constant date := p_event.send_date;
356 l_event_name constant varchar2(240) := p_event.event_name;
357 l_incident_number constant varchar2(80) := p_event.GetValueForParameter('REQUEST_NUMBER');
358 l_incident_id number;
359
360 begin
361
362 savepoint log_sr;
363
364 if check_events_enabled <> 'Y' then
365
366 wf_event.addparametertolist
367 ( p_name => 'X_RETURN_STATUS'
368 , p_value => 'SUCCESS'
369 , p_parameterlist => p_event.parameter_list
370 );
371
372 return 'SUCCESS';
373
374 end if;
375
376 open c_task( l_incident_number );
377 fetch c_task into l_incident_id;
378 if c_task%found is not null then
379
380 insert into isc_fs_events
381 ( send_date
382 , event_name
383 , source_object_type_code
384 , source_object_id
385 , task_id
386 , created_by
387 , creation_date
388 , last_updated_by
389 , last_update_date
390 , last_update_login
391 )
392 select
393 l_send_date
394 , l_event_name
395 , t.source_object_type_code
396 , t.source_object_id
397 , t.task_id
398 , fnd_global.user_id
399 , sysdate
400 , fnd_global.user_id
401 , sysdate
402 , fnd_global.login_id
403 from
404 cs_incidents_audit_b a
405 , jtf_tasks_b t
406 where
407 a.incident_id = l_incident_id
408 and a.creation_date >= l_send_date - (5/1440) -- include audits from 5 minutes before event sent date
409 and 'Y' in ( a.change_inventory_item_flag
410 , a.change_inv_organization_flag
411 )
412 and a.entity_activity_code = 'U'
413 and a.updated_entity_code = 'SR_HEADER'
414 and t.source_object_type_code = 'SR'
415 and t.source_object_id = a.incident_id;
416
417 end if;
418 close c_task;
419
420 wf_event.addparametertolist
421 ( p_name => 'X_RETURN_STATUS'
422 , p_value => 'SUCCESS'
423 , p_parameterlist => p_event.parameter_list
424 );
425
426 return 'SUCCESS';
427
428 exception
429 when others then
430 rollback to savepoint log_sr;
431 if c_task%isopen then
432 close c_task;
433 end if;
434 fnd_message.set_name ('CS', 'CS_API_SR_UNKNOWN_ERROR');
435 fnd_message.set_token ('P_TEXT','isc_fs_event_log_pkg.log_sr: '||SQLERRM );
436 fnd_msg_pub.ADD;
437 wf_event.addparametertolist
438 ( p_name => 'X_RETURN_STATUS'
439 , p_value => 'ERROR'
440 , p_parameterlist => p_event.parameter_list
441 );
442 return 'ERROR';
443
444 end log_sr;
445
446 end isc_fs_event_log_etl_pkg;