[Home] [Help]
Skip to content
PACKAGE BODY: APPS.HR_API_WF_EVENTS
Source
1 Package Body hr_api_wf_events as
2 /* $Header: hrapiwfe.pkb 120.11 2011/12/24 13:28:35 vboggava 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.11 2011/12/24 13:28:35 vboggava 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.11 2011/12/24 13:28:35 vboggava 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 elsif l_datatype(k) = 112 then
171 l_datatype_str := 'clob';
172 else
173 l_datatype_str := 'ERROR'||l_datatype_str;
174 end if;
175 --
176 if (lower(l_argument_name(k)) like '%effective_start_date' or lower(l_argument_name(k)) like '%actual_termination_date') then
177 l_dt_flag := 1;
178 l_dt_argument_name := lower(l_argument_name(k));
179 end if;
180 add_header_line(rpad(lower(l_argument_name(k)),30)||' '||l_datatype_str||',');
181 add_body_line(rpad(lower(l_argument_name(k)),30)||' '||l_datatype_str||',');
182 end loop;
183 -- remove the last comma and end the parameter list
184 l_header(i-1):=rtrim(l_header(i-1),',
185 ')||');
186 ';
187 l_body(j-1):=rtrim(l_body(j-1),',
188 ')||') is
189 ';
190 -- write out the variables
191 add_body_line(' l_event_key number;');
192 add_body_line(' l_event_data clob;');
193 add_body_line(' l_event_name varchar2(250);');
194 add_body_line(' l_text varchar2(2000);'); --2753722
195 add_body_line(' l_message varchar2(10);');
196 add_body_line(' --');
197 add_body_line(' cursor get_seq is');
198 add_body_line(' select per_wf_events_s.nextval from dual;');
199 add_body_line(' --');
200 add_body_line(' l_proc varchar2(72):='' '||l_package_name||'.'
201 ||l_proc_name||''';');
202 add_body_line('begin');
203 add_body_line(' hr_utility.set_location(''Entering: ''||l_proc,10);');
204 -- generate the event name
205 add_body_line(' -- check the status of the business event');
206 add_body_line(' l_event_name:='''||get_event_name(p_package_name=>l_package_name
207 ,p_procedure=>l_proc_name)||''';');
208 -- add the logic to call the business event
209 add_body_line(' l_message:=wf_event.test(l_event_name);');
210 add_body_line(' --');
211 add_body_line(' if (l_message=''MESSAGE'') then');
212 add_body_line(' hr_utility.set_location(l_proc,20);');
213 add_body_line(' --');
214 add_body_line(' -- get a key for the event');
215 add_body_line(' --');
216 add_body_line(' open get_seq;');
217 add_body_line(' fetch get_seq into l_event_key;');
218 add_body_line(' close get_seq;');
219 add_body_line(' --');
220 add_body_line(' -- build the xml data for the event');
221 add_body_line(' --');
222 -- build the XML to send with the message
223 add_body_line(' dbms_lob.createTemporary(l_event_data,false,dbms_lob.call);');
224 add_body_line(' l_text:=''<?xml version =''''1.0'''' encoding =''''ASCII''''?>'';');
225 add_body_line(' dbms_lob.writeAppend(l_event_data,length(l_text),l_text);');
226 l_entity:=get_entity(p_package_name=>l_package_name);
227 add_body_line(' l_text:=''<'||l_entity||'>'';');
228 add_body_line(' dbms_lob.writeAppend(l_event_data,length(l_text),l_text);');
229 add_body_line(' --');
230 -- loop over all of the parameters, building up the xml
231 for k in l_argument_name.first .. l_argument_name.last loop
232 --
233 add_body_line(' l_text:=''<'||lower(substrb(l_argument_name(k),3))||'>'';');
234 if l_datatype(k) = 2 then
235 add_body_line(' l_text:=l_text||fnd_number.number_to_canonical('||lower(l_argument_name(k))||');');
236 elsif l_datatype(k) = 12 then
237 add_body_line(' l_text:=l_text||fnd_date.date_to_canonical('||lower(l_argument_name(k))||');');
238 elsif l_datatype(k) = 252 then
239 add_body_line('if('||l_argument_name(k)||') then');
240 add_body_line('l_text:=l_text||''TRUE'';');
241 add_body_line('else');
242 add_body_line('l_text:=l_text||''FALSE'';');
243 add_body_line('end if;');
244 else
245 add_body_line(' l_text:=l_text||irc_utilities_pkg.removeTags('||lower(l_argument_name(k))||');');
246 end if;
247 add_body_line(' l_text:=l_text||''</'||lower(substrb(l_argument_name(k),3))||'>'';');
248 add_body_line(' dbms_lob.writeAppend(l_event_data,length(l_text),l_text);');
249 end loop;
250 add_body_line(' l_text:=''</'||l_entity||'>'';');
251 add_body_line(' --');
252 add_body_line(' dbms_lob.writeAppend(l_event_data,length(l_text),l_text);');
253 add_body_line(' --');
254 if (l_dt_flag = 1) then
255 add_body_line(' if ' || l_dt_argument_name || ' is not NULL and');
256 add_body_line(' ' || l_dt_argument_name || ' > trunc(SYSDATE) and');
257 add_body_line(' fnd_profile.value(''HR_DEFER_FD_BE_EVENTS'') = ''Y'' then ');
258 add_body_line(' -- raise the event with the event data, with send date set to effective date');
259 add_body_line(' wf_event.raise(p_event_name=>l_event_name');
260 add_body_line(' ,p_event_key=>l_event_key');
261 add_body_line(' ,p_event_data=>l_event_data');
262 add_body_line(' ,p_send_date => ' || l_dt_argument_name || ');');
263 add_body_line(' --');
264 add_body_line(' else ');
265 add_body_line(' -- raise the event with the event data');
266 add_body_line(' wf_event.raise(p_event_name=>l_event_name');
267 add_body_line(' ,p_event_key=>l_event_key');
268 add_body_line(' ,p_event_data=>l_event_data);');
269 add_body_line(' end if;');
270 else
271 add_body_line(' -- raise the event with the event data');
272 add_body_line(' wf_event.raise(p_event_name=>l_event_name');
273 add_body_line(' ,p_event_key=>l_event_key');
274 add_body_line(' ,p_event_data=>l_event_data);');
275 end if;
276
277 -- add the rest of the logic for the non MESSAGE events
278 add_body_line(' elsif (l_message=''KEY'') then');
279 add_body_line(' hr_utility.set_location(l_proc,30);');
280 add_body_line(' -- get a key for the event');
281 add_body_line(' open get_seq;');
282 add_body_line(' fetch get_seq into l_event_key;');
283 add_body_line(' close get_seq;');
284 if (l_dt_flag =1) then
285 add_body_line(' if ' || l_dt_argument_name || ' is not NULL and');
286 add_body_line(' ' || l_dt_argument_name || ' > trunc(SYSDATE) and');
287 add_body_line(' fnd_profile.value(''HR_DEFER_FD_BE_EVENTS'') = ''Y'' then ');
288 add_body_line(' -- this is a key event, so just raise the event');
289 add_body_line(' -- without the event data, with send date set to effective date');
290 add_body_line(' wf_event.raise(p_event_name=>l_event_name');
291 add_body_line(' ,p_event_key=>l_event_key');
292 add_body_line(' ,p_send_date => ' || l_dt_argument_name || ');');
293 add_body_line(' --');
294 add_body_line(' else');
295 add_body_line(' -- this is a key event, so just raise the event');
296 add_body_line(' -- without the event data');
297 add_body_line(' wf_event.raise(p_event_name=>l_event_name');
298 add_body_line(' ,p_event_key=>l_event_key);');
299
300 add_body_line(' end if;');
301 else
302 add_body_line(' -- this is a key event, so just raise the event');
303 add_body_line(' -- without the event data');
304 add_body_line(' wf_event.raise(p_event_name=>l_event_name');
305 add_body_line(' ,p_event_key=>l_event_key);');
306 end if;
307 add_body_line(' elsif (l_message=''NONE'') then');
308 add_body_line(' hr_utility.set_location(l_proc,40);');
309 add_body_line(' -- no event is required, so do nothing');
310 add_body_line(' null;');
311 add_body_line(' end if;');
312 add_body_line(' hr_utility.set_location(''Leaving: ''||l_proc,50);');
313 -- close off the procedure
314 add_body_line('end '||l_proc_name||';');
315 end loop;
316 -- close off the packages
317 add_body_line('end '||l_package_name||';');
318 add_header_line('end '||l_package_name||';');
319 -- build the neader
320 l_csr_sql := dbms_sql.open_cursor;
321 dbms_sql.parse( l_csr_sql, l_header,0,i-1,FALSE, dbms_sql.v7 );
322 l_rows := dbms_sql.execute( l_csr_sql );
323 dbms_sql.close_cursor( l_csr_sql );
324 -- for k in 0..j loop
325 -- dbms_output.put_line(l_body(k));
326 -- end loop;
327 -- build the body
328 l_csr_sql := dbms_sql.open_cursor;
329 dbms_sql.parse( l_csr_sql, l_body,0,j-1,FALSE, dbms_sql.v7 );
330 l_rows := dbms_sql.execute( l_csr_sql );
331 dbms_sql.close_cursor( l_csr_sql );
332 end create_business_event_code;
333 --
334 procedure subscribe_business_event_code(p_hook_package varchar2
335 ,p_regenerate boolean) is
336 --
337 cursor csr_api_hook is
338 select ahm.api_module_id
339 , ahk.api_hook_id
340 , ahk.hook_procedure
341 from hr_api_hooks ahk
342 , hr_api_modules ahm
343 where ahk.hook_package = p_hook_package
344 and ahk.api_module_id = ahm.api_module_id
345 and ahm.api_module_type='BP'
346 and ahk.api_hook_type='AP';
347 --
348 -- Declare local variables
349 --
350 l_object_version_number number;
351 l_api_hook_call_id number;
352 --
353 l_package_name varchar2(80);
354
355 begin
356 --
357 l_package_name:=get_package_name(p_package_name=>p_hook_package);
358 for hooks_rec in csr_api_hook loop
359 --
360 -- subscribe the procedure to the api hook
361 --
362 hr_app_api_hook_call_internal.create_app_api_hook_call
363 (p_validate => false
364 ,p_effective_date => to_date('2001/04/02', 'YYYY/MM/DD')
365 ,p_api_hook_id => hooks_rec.api_hook_id
366 ,p_api_hook_call_type => 'PP'
367 ,p_sequence => 1499
368 ,p_application_id => 800
369 ,p_app_install_status => 'I_OR_S'
370 ,p_enabled_flag => 'Y'
371 ,p_call_package => l_package_name
372 ,p_call_procedure => hooks_rec.hook_procedure
373 ,p_api_hook_call_id => l_api_hook_call_id
374 ,p_object_version_number => l_object_version_number
375 );
376 if p_regenerate then
377 --
378 -- Re-create the user hook package bodies for
379 -- the row handler API module
380 --
381 hr_api_user_hooks_utility.create_hooks_add_report(hooks_rec.api_module_id);
382 end if;
383 end loop;
384 end subscribe_business_event_code;
385 --
386 procedure register_business_event(p_hook_package varchar2) is
387 --
388 cursor csr_api_hook is
389 select ahk.hook_procedure
390 from hr_api_hooks ahk
391 where ahk.hook_package = p_hook_package
392 and ahk.api_hook_type='AP';
393 --
394 cursor event_exists(p_event_name varchar2) is
395 select GUID,STATUS
396 from wf_events
397 where name=p_event_name;
398 --
399 cursor appl_name(p_application_short_name varchar2) is
400 select application_name
401 from fnd_application_vl
402 where application_short_name=p_application_short_name;
403 --
404 -- Declare local variables
405 --
406 --
407 l_package_name varchar2(80);
408 l_event_name varchar2(240);
409 l_xml varchar2(32000);
410 l_guid varchar2(250);
411 l_name varchar2(250);
412 l_product_code varchar2(30);
413 l_application_name fnd_application_tl.application_name%type;
414 l_status wf_events.status%type;
415 --
416 begin
417 --
418 l_package_name:=get_package_name(p_package_name=>p_hook_package);
419 l_product_code:=upper(substr(p_hook_package,1,3));
420 if l_product_code='HR_' then
421 l_product_code:='PER';
422 end if;
423 --
424 open appl_name(l_product_code);
425 fetch appl_name into l_application_name;
426 close appl_name;
427 --
428 for hooks_rec in csr_api_hook loop
429 l_event_name:=get_event_name(p_package_name=>p_hook_package
430 ,p_procedure=>hooks_rec.hook_procedure);
431 l_name:=nls_initcap(
432 replace(
433 substrb(hooks_rec.hook_procedure,1
434 ,instrb(hooks_rec.hook_procedure,'_',-1)-1
435 )
436 ,'_',' ')
437 );
438 open event_exists(l_event_name);
439 fetch event_exists into l_guid,l_status;
440 if event_exists%found then
441 close event_exists;
442 l_xml:=
443 '<WF_TABLE_DATA>
444 <WF_EVENTS>
445 <VERSION>1.0</VERSION>
446 <GUID>'||l_guid||'</GUID>';
447 l_xml:=l_xml||'
448 <NAME>'||l_event_name||'</NAME>';
449 l_xml:=l_xml||'
450 <TYPE>EVENT</TYPE>
451 <STATUS>'||l_status||'</STATUS>
452 <GENERATE_FUNCTION/>
453 <OWNER_NAME>'||l_application_name||'</OWNER_NAME>
454 <OWNER_TAG>'||l_product_code||'</OWNER_TAG>';
455 l_xml:=l_xml||'
456 <DISPLAY_NAME>'||l_name
457 ||'</DISPLAY_NAME>';
458 l_xml:=l_xml||'
459 <DESCRIPTION>'||l_name
460 ||' API</DESCRIPTION>';
461 l_xml:=l_xml||'
462 </WF_EVENTS>
463 </WF_TABLE_DATA>';
464 else
465 close event_exists;
466 l_xml:=
467 '<WF_TABLE_DATA>
468 <WF_EVENTS>
469 <VERSION>1.0</VERSION>
470 <GUID>#NEW</GUID>';
471 l_xml:=l_xml||'
472 <NAME>'||l_event_name||'</NAME>';
473 l_xml:=l_xml||'
474 <TYPE>EVENT</TYPE>
475 <STATUS>DISABLED</STATUS>
476 <GENERATE_FUNCTION/>
477 <OWNER_NAME>'||l_application_name||'</OWNER_NAME>
478 <OWNER_TAG>'||l_product_code||'</OWNER_TAG>';
479 l_xml:=l_xml||'
480 <DISPLAY_NAME>'||l_name
481 ||'</DISPLAY_NAME>';
482 l_xml:=l_xml||'
483 <DESCRIPTION>'||l_name
484 ||' API</DESCRIPTION>';
485 l_xml:=l_xml||'
486 </WF_EVENTS>
487 </WF_TABLE_DATA>';
488 end if;
489 wf_events_pkg.receive(l_xml);
490 end loop;
491 end register_business_event;
492 --
493 procedure add_event(p_hook_package varchar2
494 ,p_regenerate boolean) is
495 begin
496 create_business_event_code(p_hook_package);
497 subscribe_business_event_code(p_hook_package,p_regenerate);
498 --
499 -- Business Event registrations are now delivered as wfx files
500 -- with irep annotations.
501 --
502 --register_business_event(p_hook_package);
503 --
504 end add_event;
505 --
506 procedure add_events_for_api(p_api_package varchar2
507 ,p_regenerate boolean) is
508 cursor get_hooks is
509 select distinct ah.hook_package
510 from hr_api_modules am
511 , hr_api_hooks ah
512 where am.api_module_id=ah.api_module_id
513 and am.module_package=p_api_package
514 and am.api_module_type='BP'
515 and ah.api_hook_type='AP';
516 begin
517 for hook_rec in get_hooks loop
518 add_event(p_hook_package=>hook_rec.hook_package
519 ,p_regenerate =>p_regenerate);
520 end loop;
521 end add_events_for_api;
522 --
523 function default_rule(p_subscription_guid in RAW,
524 p_event in out nocopy wf_event_t)
525 return varchar2
526 is
527 begin
528 select per_wf_events_s.nextval into p_event.Correlation_ID from dual;
529 return WF_RULE.DEFAULT_RULE(p_subscription_guid, p_event);
530 end;
531 --
532 end hr_api_wf_events;