[Home] [Help]
PACKAGE BODY: APPS.HR_API_WF_EVENTS
Source
1 Package Body hr_api_wf_events as
2 /* $Header: hrapiwfe.pkb 120.4.12010000.2 2008/09/29 12:54:07 srgnanas ship $ */
3 g_package varchar2(33) := ' hr_api_wf_events.';
4 --
5 function get_entity(p_package_name varchar2)
6 return varchar2 is
7 l_entity varchar2(80);
8 begin
9 l_entity:=lower(substrb(p_package_name,instrb(p_package_name,'_')+1
10 ,instrb(p_package_name,'_',-1)-instrb(p_package_name,'_')-1));
11 return l_entity;
12 end get_entity;
13 --
14 function get_event_name(p_package_name varchar2
15 ,p_procedure varchar2)
16 return varchar2 is
17
18 l_product varchar2(7);
19 l_3lc varchar2(3);
20 l_entity varchar2(80);
21 l_event varchar2(240);
22 begin
23 l_event:='oracle.apps.';
24 l_3lc:=lower(substrb(p_package_name,1,3));
25 if (l_3lc='hr_') then
26 l_product:='per';
27 elsif (l_3lc='irc') then
28 l_product:='per.irc';
29 else
30 l_product:=l_3lc;
31 end if;
32
33 l_event:=l_event||l_product||'.api.';
34 l_entity:=get_entity(p_package_name);
35 l_event:=l_event||l_entity||'.'||lower(substrb(p_procedure
36 ,1,instrb(p_procedure,'_',-1)-1));
37 return l_event;
38 end get_event_name;
39 --
40 function get_package_name(p_package_name varchar2)
41 return varchar2 is
42 l_package_name varchar2(80);
43 l_underscore_position number;
44 begin
45 l_underscore_position:=instrb(p_package_name,'_',-1);
46 l_package_name:=lower(substrb(p_package_name,1,l_underscore_position))||'be'
47 ||substrb(p_package_name,l_underscore_position+3);
48 return l_package_name;
49 end get_package_name;
50 --
51 procedure create_business_event_code(p_hook_package varchar2) is
52 l_header dbms_sql.varchar2s;
53 l_body dbms_sql.varchar2s;
54 i number:=0;
55 j number:=0;
56 l_overload dbms_describe.number_table;
57 l_position dbms_describe.number_table;
58 l_level dbms_describe.number_table;
59 l_argument_name dbms_describe.varchar2_table;
60 l_datatype dbms_describe.number_table;
61 l_default_value dbms_describe.number_table;
62 l_in_out dbms_describe.number_table;
63 l_length dbms_describe.number_table;
64 l_precision dbms_describe.number_table;
65 l_scale dbms_describe.number_table;
66 l_radix dbms_describe.number_table;
67 l_spare dbms_describe.number_table;
68 l_package_name varchar2(80);
69 l_proc_name varchar2(80);
70 l_entity varchar2(80);
71 l_dt_flag number :=0;
72 l_dt_argument_name varchar2(30);
73 k number;
74 --
75 -- Cursor to select all the procedure names in a given hook package
76 --
77 cursor csr_procs is
78 select ahk.hook_procedure
79 , ahk.api_hook_type
80 , ahk.api_hook_id
81 , ahk.legislation_package
82 , ahk.legislation_function
83 , amd.module_name
84 , amd.data_within_business_group
85 from hr_api_modules amd
86 , hr_api_hooks ahk
87 where amd.api_module_id = ahk.api_module_id
88 and ahk.hook_package = p_hook_package
89 and amd.api_module_type='BP'
90 and ahk.api_hook_type='AP';
91 --
92 l_proc varchar2(72) := g_package||'create_business_event_code';
93 l_datatype_str varchar2(80);
94 l_csr_sql integer;
95 l_rows number;
96 --
97 -- add body line adds a line to the body text with
98 -- a carriage return on the end
99 --
100 procedure add_body_line(text varchar2) is
101 begin
102 l_body(j):=text||'
103 ';
104 j:=j+1;
105 end;
106 --
107 -- add header line adds a line to the header text
108 -- with a carriage return on the end
109 --
110 procedure add_header_line(text varchar2) is
111 begin
112 l_header(i):=text||'
113 ';
114 i:=i+1;
115 end;
116 --
117 begin
118 -- get the new package name
119 l_package_name:=get_package_name(p_hook_package);
120 -- create the package header lines
121 add_header_line('create or replace package '||l_package_name||' as ');
122 add_body_line('create or replace package body '||l_package_name||' as ');
123 add_header_line('--Code generated on '||to_char(sysdate,'DD/MM/YYYY HH:MI:SS'));
124 add_header_line('/'||'* $Header: hrapiwfe.pkb 120.4.12010000.2 2008/09/29 12:54:07 srgnanas ship $*'||'/');
125 add_body_line('--Code generated on '||to_char(sysdate,'DD/MM/YYYY HH:MI:SS'));
126 add_body_line('/'||'* $Header: hrapiwfe.pkb 120.4.12010000.2 2008/09/29 12:54:07 srgnanas ship $*'||'/');
127 -- loop over all of the procedures in the package
128 for proc_rec in csr_procs loop
129 --
130 -- get a description of the procedure
131 --
132 l_dt_flag :=0;
133 l_dt_argument_name := NULL;
134 --
135 hr_general.describe_procedure
136 (object_name => p_hook_package||'.'||proc_rec.hook_procedure
137 ,reserved1 => ''
138 ,reserved2 => ''
139 ,overload => l_overload
140 ,position => l_position
141 ,level => l_level
142 ,argument_name=> l_argument_name
143 ,datatype => l_datatype
144 ,default_value=> l_default_value
145 ,in_out => l_in_out
146 ,length => l_length
147 ,precision => l_precision
148 ,scale => l_scale
149 ,radix => l_radix
150 ,spare => l_spare);
151 --
152 l_proc_name:=lower(proc_rec.hook_procedure);
153 -- add the procedure name line
154 add_header_line('procedure '||l_proc_name||' (');
155 add_body_line('procedure '||l_proc_name||' (');
156 -- loop over all of the parameters in the package, writing
157 -- them in to the procedure definition
158 for k in l_argument_name.first .. l_argument_name.last loop
159 --
160 if l_datatype(k) = 1 then
161 l_datatype_str := 'varchar2';
162 elsif l_datatype(k) = 2 then
163 l_datatype_str := 'number';
164 elsif l_datatype(k) = 12 then
165 l_datatype_str := 'date';
166 elsif l_datatype(k) = 252 then
167 l_datatype_str := 'boolean';
168 elsif l_datatype(k) = 8 then
169 l_datatype_str := 'long';
170 else
171 l_datatype_str := 'ERROR'||l_datatype_str;
172 end if;
173 --
174 if lower(l_argument_name(k)) like '%effective_start_date' then
175 l_dt_flag := 1;
176 l_dt_argument_name := lower(l_argument_name(k));
177 end if;
178 add_header_line(rpad(lower(l_argument_name(k)),30)||' '||l_datatype_str||',');
179 add_body_line(rpad(lower(l_argument_name(k)),30)||' '||l_datatype_str||',');
180 end loop;
181 -- remove the last comma and end the parameter list
182 l_header(i-1):=rtrim(l_header(i-1),',
183 ')||');
184 ';
185 l_body(j-1):=rtrim(l_body(j-1),',
186 ')||') is
187 ';
188 -- write out the variables
189 add_body_line(' l_event_key number;');
190 add_body_line(' l_event_data clob;');
191 add_body_line(' l_event_name varchar2(250);');
192 add_body_line(' l_text varchar2(2000);'); --2753722
193 add_body_line(' l_message varchar2(10);');
194 add_body_line(' --');
195 add_body_line(' cursor get_seq is');
196 add_body_line(' select per_wf_events_s.nextval from dual;');
197 add_body_line(' --');
198 add_body_line(' l_proc varchar2(72):='' '||l_package_name||'.'
199 ||l_proc_name||''';');
200 add_body_line('begin');
201 add_body_line(' hr_utility.set_location(''Entering: ''||l_proc,10);');
202 -- generate the event name
203 add_body_line(' -- check the status of the business event');
204 add_body_line(' l_event_name:='''||get_event_name(p_package_name=>l_package_name
205 ,p_procedure=>l_proc_name)||''';');
206 -- add the logic to call the business event
207 add_body_line(' l_message:=wf_event.test(l_event_name);');
208 add_body_line(' --');
209 add_body_line(' if (l_message=''MESSAGE'') then');
210 add_body_line(' hr_utility.set_location(l_proc,20);');
211 add_body_line(' --');
212 add_body_line(' -- get a key for the event');
213 add_body_line(' --');
214 add_body_line(' open get_seq;');
215 add_body_line(' fetch get_seq into l_event_key;');
216 add_body_line(' close get_seq;');
217 add_body_line(' --');
218 add_body_line(' -- build the xml data for the event');
219 add_body_line(' --');
220 -- build the XML to send with the message
221 add_body_line(' dbms_lob.createTemporary(l_event_data,false,dbms_lob.call);');
222 add_body_line(' l_text:=''<?xml version =''''1.0'''' encoding =''''ASCII''''?>'';');
223 add_body_line(' dbms_lob.writeAppend(l_event_data,length(l_text),l_text);');
224 l_entity:=get_entity(p_package_name=>l_package_name);
225 add_body_line(' l_text:=''<'||l_entity||'>'';');
226 add_body_line(' dbms_lob.writeAppend(l_event_data,length(l_text),l_text);');
227 add_body_line(' --');
228 -- loop over all of the parameters, building up the xml
229 for k in l_argument_name.first .. l_argument_name.last loop
230 --
231 add_body_line(' l_text:=''<'||lower(substrb(l_argument_name(k),3))||'>'';');
232 if l_datatype(k) = 2 then
233 add_body_line(' l_text:=l_text||fnd_number.number_to_canonical('||lower(l_argument_name(k))||');');
234 elsif l_datatype(k) = 12 then
235 add_body_line(' l_text:=l_text||fnd_date.date_to_canonical('||lower(l_argument_name(k))||');');
236 elsif l_datatype(k) = 252 then
237 add_body_line('if('||l_argument_name(k)||') then');
238 add_body_line('l_text:=l_text||''TRUE'';');
239 add_body_line('else');
240 add_body_line('l_text:=l_text||''FALSE'';');
241 add_body_line('end if;');
242 else
243 add_body_line(' l_text:=l_text||irc_utilities_pkg.removeTags('||lower(l_argument_name(k))||');');
244 end if;
245 add_body_line(' l_text:=l_text||''</'||lower(substrb(l_argument_name(k),3))||'>'';');
246 add_body_line(' dbms_lob.writeAppend(l_event_data,length(l_text),l_text);');
247 end loop;
248 add_body_line(' l_text:=''</'||l_entity||'>'';');
249 add_body_line(' --');
250 add_body_line(' dbms_lob.writeAppend(l_event_data,length(l_text),l_text);');
251 add_body_line(' --');
252 if (l_dt_flag = 1) then
253 add_body_line(' if ' || l_dt_argument_name || ' is not NULL and');
254 add_body_line(' ' || l_dt_argument_name || ' > trunc(SYSDATE) and');
255 add_body_line(' fnd_profile.value(''HR_DEFER_FD_BE_EVENTS'') = ''Y'' then ');
256 add_body_line(' -- raise the event with the event data, with send date set to effective date');
257 add_body_line(' wf_event.raise(p_event_name=>l_event_name');
258 add_body_line(' ,p_event_key=>l_event_key');
259 add_body_line(' ,p_event_data=>l_event_data');
260 add_body_line(' ,p_send_date => ' || l_dt_argument_name || ');');
261 add_body_line(' --');
262 add_body_line(' else ');
263 add_body_line(' -- raise the event with the event data');
264 add_body_line(' wf_event.raise(p_event_name=>l_event_name');
265 add_body_line(' ,p_event_key=>l_event_key');
266 add_body_line(' ,p_event_data=>l_event_data);');
267 add_body_line(' end if;');
268 else
269 add_body_line(' -- raise the event with the event data');
270 add_body_line(' wf_event.raise(p_event_name=>l_event_name');
271 add_body_line(' ,p_event_key=>l_event_key');
272 add_body_line(' ,p_event_data=>l_event_data);');
273 end if;
274
275 -- add the rest of the logic for the non MESSAGE events
276 add_body_line(' elsif (l_message=''KEY'') then');
277 add_body_line(' hr_utility.set_location(l_proc,30);');
278 add_body_line(' -- get a key for the event');
279 add_body_line(' open get_seq;');
280 add_body_line(' fetch get_seq into l_event_key;');
281 add_body_line(' close get_seq;');
282 if (l_dt_flag =1) then
283 add_body_line(' if ' || l_dt_argument_name || ' is not NULL and');
284 add_body_line(' ' || l_dt_argument_name || ' > trunc(SYSDATE) and');
285 add_body_line(' fnd_profile.value(''HR_DEFER_FD_BE_EVENTS'') = ''Y'' then ');
286 add_body_line(' -- this is a key event, so just raise the event');
287 add_body_line(' -- without the event data, with send date set to effective date');
288 add_body_line(' wf_event.raise(p_event_name=>l_event_name');
289 add_body_line(' ,p_event_key=>l_event_key');
290 add_body_line(' ,p_send_date => ' || l_dt_argument_name || ');');
291 add_body_line(' --');
292 add_body_line(' else');
293 add_body_line(' -- this is a key event, so just raise the event');
294 add_body_line(' -- without the event data');
295 add_body_line(' wf_event.raise(p_event_name=>l_event_name');
296 add_body_line(' ,p_event_key=>l_event_key);');
297
298 add_body_line(' end if;');
299 else
300 add_body_line(' -- this is a key event, so just raise the event');
301 add_body_line(' -- without the event data');
302 add_body_line(' wf_event.raise(p_event_name=>l_event_name');
303 add_body_line(' ,p_event_key=>l_event_key);');
304 end if;
305 add_body_line(' elsif (l_message=''NONE'') then');
306 add_body_line(' hr_utility.set_location(l_proc,40);');
307 add_body_line(' -- no event is required, so do nothing');
308 add_body_line(' null;');
309 add_body_line(' end if;');
310 add_body_line(' hr_utility.set_location(''Leaving: ''||l_proc,50);');
311 -- close off the procedure
312 add_body_line('end '||l_proc_name||';');
313 end loop;
314 -- close off the packages
315 add_body_line('end '||l_package_name||';');
316 add_header_line('end '||l_package_name||';');
317 -- build the neader
318 l_csr_sql := dbms_sql.open_cursor;
319 dbms_sql.parse( l_csr_sql, l_header,0,i-1,FALSE, dbms_sql.v7 );
320 l_rows := dbms_sql.execute( l_csr_sql );
321 dbms_sql.close_cursor( l_csr_sql );
322 -- for k in 0..j loop
323 -- dbms_output.put_line(l_body(k));
324 -- end loop;
325 -- build the body
326 l_csr_sql := dbms_sql.open_cursor;
327 dbms_sql.parse( l_csr_sql, l_body,0,j-1,FALSE, dbms_sql.v7 );
328 l_rows := dbms_sql.execute( l_csr_sql );
329 dbms_sql.close_cursor( l_csr_sql );
330 end create_business_event_code;
331 --
332 procedure subscribe_business_event_code(p_hook_package varchar2
333 ,p_regenerate boolean) is
334 --
335 cursor csr_api_hook is
336 select ahm.api_module_id
337 , ahk.api_hook_id
338 , ahk.hook_procedure
339 from hr_api_hooks ahk
340 , hr_api_modules ahm
341 where ahk.hook_package = p_hook_package
342 and ahk.api_module_id = ahm.api_module_id
343 and ahm.api_module_type='BP'
344 and ahk.api_hook_type='AP';
345 --
346 -- Declare local variables
347 --
348 l_object_version_number number;
349 l_api_hook_call_id number;
350 --
351 l_package_name varchar2(80);
352
353 begin
354 --
355 l_package_name:=get_package_name(p_package_name=>p_hook_package);
356 for hooks_rec in csr_api_hook loop
357 --
358 -- subscribe the procedure to the api hook
359 --
360 hr_app_api_hook_call_internal.create_app_api_hook_call
361 (p_validate => false
362 ,p_effective_date => to_date('2001/04/02', 'YYYY/MM/DD')
363 ,p_api_hook_id => hooks_rec.api_hook_id
364 ,p_api_hook_call_type => 'PP'
365 ,p_sequence => 1499
366 ,p_application_id => 800
367 ,p_app_install_status => 'I_OR_S'
368 ,p_enabled_flag => 'Y'
369 ,p_call_package => l_package_name
370 ,p_call_procedure => hooks_rec.hook_procedure
371 ,p_api_hook_call_id => l_api_hook_call_id
372 ,p_object_version_number => l_object_version_number
373 );
374 if p_regenerate then
375 --
376 -- Re-create the user hook package bodies for
377 -- the row handler API module
378 --
379 hr_api_user_hooks_utility.create_hooks_add_report(hooks_rec.api_module_id);
380 end if;
381 end loop;
382 end subscribe_business_event_code;
383 --
384 procedure register_business_event(p_hook_package varchar2) is
385 --
386 cursor csr_api_hook is
387 select ahk.hook_procedure
388 from hr_api_hooks ahk
389 where ahk.hook_package = p_hook_package
390 and ahk.api_hook_type='AP';
391 --
392 cursor event_exists(p_event_name varchar2) is
393 select GUID,STATUS
394 from wf_events
395 where name=p_event_name;
396 --
397 cursor appl_name(p_application_short_name varchar2) is
398 select application_name
399 from fnd_application_vl
400 where application_short_name=p_application_short_name;
401 --
402 -- Declare local variables
403 --
404 --
405 l_package_name varchar2(80);
406 l_event_name varchar2(240);
407 l_xml varchar2(32000);
408 l_guid varchar2(250);
409 l_name varchar2(250);
410 l_product_code varchar2(30);
411 l_application_name fnd_application_tl.application_name%type;
412 l_status wf_events.status%type;
413 --
414 begin
415 --
416 l_package_name:=get_package_name(p_package_name=>p_hook_package);
417 l_product_code:=upper(substr(p_hook_package,1,3));
418 if l_product_code='HR_' then
419 l_product_code:='PER';
420 end if;
421 --
422 open appl_name(l_product_code);
423 fetch appl_name into l_application_name;
424 close appl_name;
425 --
426 for hooks_rec in csr_api_hook loop
427 l_event_name:=get_event_name(p_package_name=>p_hook_package
428 ,p_procedure=>hooks_rec.hook_procedure);
429 l_name:=nls_initcap(
430 replace(
431 substrb(hooks_rec.hook_procedure,1
432 ,instrb(hooks_rec.hook_procedure,'_',-1)-1
433 )
434 ,'_',' ')
435 );
436 open event_exists(l_event_name);
437 fetch event_exists into l_guid,l_status;
438 if event_exists%found then
439 close event_exists;
440 l_xml:=
441 '<WF_TABLE_DATA>
442 <WF_EVENTS>
443 <VERSION>1.0</VERSION>
444 <GUID>'||l_guid||'</GUID>';
445 l_xml:=l_xml||'
446 <NAME>'||l_event_name||'</NAME>';
447 l_xml:=l_xml||'
448 <TYPE>EVENT</TYPE>
449 <STATUS>'||l_status||'</STATUS>
450 <GENERATE_FUNCTION/>
451 <OWNER_NAME>'||l_application_name||'</OWNER_NAME>
452 <OWNER_TAG>'||l_product_code||'</OWNER_TAG>';
453 l_xml:=l_xml||'
454 <DISPLAY_NAME>'||l_name
455 ||'</DISPLAY_NAME>';
456 l_xml:=l_xml||'
457 <DESCRIPTION>'||l_name
458 ||' API</DESCRIPTION>';
459 l_xml:=l_xml||'
460 </WF_EVENTS>
461 </WF_TABLE_DATA>';
462 else
463 close event_exists;
464 l_xml:=
465 '<WF_TABLE_DATA>
466 <WF_EVENTS>
467 <VERSION>1.0</VERSION>
468 <GUID>#NEW</GUID>';
469 l_xml:=l_xml||'
470 <NAME>'||l_event_name||'</NAME>';
471 l_xml:=l_xml||'
472 <TYPE>EVENT</TYPE>
473 <STATUS>DISABLED</STATUS>
474 <GENERATE_FUNCTION/>
475 <OWNER_NAME>'||l_application_name||'</OWNER_NAME>
476 <OWNER_TAG>'||l_product_code||'</OWNER_TAG>';
477 l_xml:=l_xml||'
478 <DISPLAY_NAME>'||l_name
479 ||'</DISPLAY_NAME>';
480 l_xml:=l_xml||'
481 <DESCRIPTION>'||l_name
482 ||' API</DESCRIPTION>';
483 l_xml:=l_xml||'
484 </WF_EVENTS>
485 </WF_TABLE_DATA>';
486 end if;
487 wf_events_pkg.receive(l_xml);
488 end loop;
489 end register_business_event;
490 --
491 procedure add_event(p_hook_package varchar2
492 ,p_regenerate boolean) is
493 begin
494 create_business_event_code(p_hook_package);
495 subscribe_business_event_code(p_hook_package,p_regenerate);
496 --
497 -- Business Event registrations are now delivered as wfx files
498 -- with irep annotations.
499 --
500 --register_business_event(p_hook_package);
501 --
502 end add_event;
503 --
504 procedure add_events_for_api(p_api_package varchar2
505 ,p_regenerate boolean) is
506 cursor get_hooks is
507 select distinct ah.hook_package
508 from hr_api_modules am
509 , hr_api_hooks ah
510 where am.api_module_id=ah.api_module_id
511 and am.module_package=p_api_package
512 and am.api_module_type='BP'
513 and ah.api_hook_type='AP';
514 begin
515 for hook_rec in get_hooks loop
516 add_event(p_hook_package=>hook_rec.hook_package
517 ,p_regenerate =>p_regenerate);
518 end loop;
519 end add_events_for_api;
520 --
521 function default_rule(p_subscription_guid in RAW,
522 p_event in out nocopy wf_event_t)
523 return varchar2
524 is
525 begin
526 select per_wf_events_s.nextval into p_event.Correlation_ID from dual;
527 return WF_RULE.DEFAULT_RULE(p_subscription_guid, p_event);
528 end;
529 --
530 end hr_api_wf_events;