[Home] [Help]
PACKAGE BODY: APPS.AZW_UTIL
Source
1 PACKAGE BODY AZW_UTIL as
2 /* $Header: AZWUTILB.pls 115.12 2003/04/08 08:50:27 sbandi ship $: */
3
4 -- UpdateDocUrl
5 -- Called by AIWStart
6 -- Update the urls in the specific implementation workflow to reflect
7 -- site specific information.
8 -- Used by the new UI.
9 --
10 procedure UpdateDocUrl(
11 p_itemtype in varchar2,
12 p_workflow in varchar2) IS
13 proc_version wf_process_activities.process_version%TYPE;
14 act_itemtype wf_activities.item_type%TYPE;
15 act_name wf_activities.name%TYPE;
16 act_type wf_activities.type%TYPE;
17 mesg_name wf_activities.message%TYPE;
18 app_short_name varchar2(30);
19 target varchar2(255);
20 url varchar2(255);
21
22 CURSOR c_act_c IS
23 select activity_item_type, activity_name
24 from wf_process_activities
25 start with (process_item_type = p_itemtype
26 and process_version = proc_version
27 and process_name = p_workflow)
28 connect by (prior activity_name = process_name
29 and prior activity_item_type = process_item_type);
30
31 CURSOR c_apps_c IS
32 select text_default
33 from wf_message_attributes_vl
34 where message_type = act_itemtype
35 and message_name = mesg_name
36 and name = 'AZW_IA_APPSNAME';
37
38 CURSOR c_target_c IS
39 select text_default
40 from wf_message_attributes_vl
41 where message_type = act_itemtype
42 and message_name = mesg_name
43 and name = 'AZW_IA_TARGET';
44 BEGIN
45 BEGIN
46 select max(process_version) into proc_version
47 from wf_process_activities
48 where process_item_type = p_itemtype
49 and process_name = p_workflow;
50 EXCEPTION
51 WHEN app_exception.application_exception THEN
52 RAISE ;
53 WHEN OTHERS THEN
54 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
55 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
56 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
57 fnd_message.set_token('AZW_ERROR_PROC','azw_util.UpdateDocUrl');
58 fnd_message.set_token('AZW_ERROR_STMT','select process_version from wf_process_activities');
59 APP_EXCEPTION.RAISE_EXCEPTION;
60 END;
61
62 OPEN c_act_c;
63 FETCH c_act_c into act_itemtype, act_name;
64 while(c_act_c%FOUND) loop
65 BEGIN
66 select type into act_type
67 from wf_activities
68 where item_type = act_itemtype
69 and name = act_name
70 and end_date is NULL;
71 EXCEPTION
72 WHEN app_exception.application_exception THEN
73 RAISE ;
74 WHEN OTHERS THEN
75 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
76 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
77 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
78 fnd_message.set_token('AZW_ERROR_PROC','azw_util.UpdateDocUrl');
79 fnd_message.set_token('AZW_ERROR_STMT','select type from wf_activities');
80 APP_EXCEPTION.RAISE_EXCEPTION;
81 END;
82
83 if (act_type = 'NOTICE') then
84 BEGIN
85 select message into mesg_name
86 from wf_activities
87 where item_type = act_itemtype
88 and name = act_name
89 and end_date is NULL;
90 EXCEPTION
91 WHEN app_exception.application_exception THEN
92 RAISE ;
93 WHEN OTHERS THEN
94 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
95 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
96 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
97 fnd_message.set_token('AZW_ERROR_PROC','azw_util.UpdateDocUrl');
98 fnd_message.set_token('AZW_ERROR_STMT','select message from wf_activities');
99 APP_EXCEPTION.RAISE_EXCEPTION;
100 END;
101
102 BEGIN
103 OPEN c_apps_c;
104 FETCH c_apps_c into app_short_name;
105 CLOSE c_apps_c;
106 EXCEPTION
107 WHEN app_exception.application_exception THEN
108 RAISE ;
109 WHEN OTHERS THEN
110 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
111 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
112 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
113 fnd_message.set_token('AZW_ERROR_PROC','azw_util.UpdateDocUrl');
114 fnd_message.set_token('AZW_ERROR_STMT','cursor c_apps_c');
115 APP_EXCEPTION.RAISE_EXCEPTION;
116 END;
117
118 BEGIN
119 OPEN c_target_c;
120 FETCH c_target_c into target;
121 CLOSE c_target_c;
122 EXCEPTION
123 WHEN app_exception.application_exception THEN
124 RAISE ;
125 WHEN OTHERS THEN
126 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
127 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
128 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
129 fnd_message.set_token('AZW_ERROR_PROC','azw_util.UpdateDocUrl');
130 fnd_message.set_token('AZW_ERROR_STMT','cursor c_target_c');
131 APP_EXCEPTION.RAISE_EXCEPTION;
132 END;
133
134 url := fnd_help.get_url(app_short_name, target);
135
136 BEGIN
137 update wf_message_attributes
138 set text_default = url
139 where message_type = act_itemtype
140 and message_name = mesg_name
141 and name = 'AZW_IA_DOC';
142 EXCEPTION
143 WHEN app_exception.application_exception THEN
144 RAISE ;
145 WHEN OTHERS THEN
146 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
147 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
148 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
149 fnd_message.set_token('AZW_ERROR_PROC','azw_util.UpdateDocUrl');
150 fnd_message.set_token('AZW_ERROR_STMT','update wf_message_attribute');
151 APP_EXCEPTION.RAISE_EXCEPTION;
152 END;
153 end if;
154
155 FETCH c_act_c into act_itemtype, act_name;
156 end loop;
157 CLOSE c_act_c;
158
159 EXCEPTION
160 WHEN app_exception.application_exception THEN
161 RAISE ;
162 WHEN OTHERS THEN
163 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
164 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
165 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
166 fnd_message.set_token('AZW_ERROR_PROC','azw_util.UpdateDocUrl');
167 fnd_message.set_token('AZW_ERROR_STMT','cursor c_act_c');
168 APP_EXCEPTION.RAISE_EXCEPTION;
169 End UpdateDocUrl;
170
171 -- IsProductInstalled
172 -- Called by workflow engine in branching functions activities.
173 -- Check whether the product associated with the workflow is installed
174 -- or not.
175 --
176 procedure IsProductInstalled(
177 itemtype in varchar2,
178 itemkey in varchar2,
179 actid in number,
180 funcmode in varchar2,
181 result out NOCOPY varchar2 ) is
182
183 prod_name varchar2(2000);
184 prod_id number;
185 --c_char VARCHAR(1);
186 --c_count NUMBER(15);
187 --c_word VARCHAR(240);
188 yes_result VARCHAR2(240);
189 no_result VARCHAR2(240);
190 tmp_result VARCHAR2(240);
191 begin
192 yes_result := 'COMPLETE:Y';
193 no_result := 'COMPLETE:N';
194
195 prod_name := wf_engine.GetActivityAttrText(itemtype, itemkey,actid, 'AZW_IA_WFPROD');
196 tmp_result := CheckProduct(prod_name);
197 if (tmp_result = 'TRUE') then
198 result := yes_result;
199 else
200 result := no_result;
201 end if;
202 EXCEPTION
203 WHEN app_exception.application_exception THEN
204 RAISE ;
205 WHEN OTHERS THEN
206 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
207 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
208 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
209 fnd_message.set_token('AZW_ERROR_PROC','azw_util.IsProductInstalled');
210 fnd_message.set_token('AZW_ERROR_STMT','UNKNOWN');
211 APP_EXCEPTION.RAISE_EXCEPTION;
212 END IsProductInstalled;
213
214 -- CheckProduct
215 -- Called by IsProcessRunnable
216 -- Check whether a product is installed
217 --
218 function CheckProduct(
219 prod_name in varchar2)
220 return varchar2
221 IS
222 prod_id number;
223 is_number BOOLEAN;
224 l_status VARCHAR2(1);
225 l_industry VARCHAR2(1);
226 l_oracle_schema VARCHAR2(30);
227 l_appl_short_name fnd_application_vl.application_short_name%TYPE;
228 c_char VARCHAR(1);
229 c_count NUMBER(15);
230 c_word VARCHAR(240);
231 yes_result VARCHAR2(240);
232 no_result VARCHAR2(240);
233 result VARCHAR2(240);
234 v_count_installed NUMBER(15);
235
236 CURSOR c_prod_c IS
237 select application_id
238 from fnd_product_installations
239 where to_char(application_id) = LTRIM(RTRIM(c_word))
240 and (status = 'I' or status ='S');
241
242 BEGIN
243 yes_result := 'TRUE';
244 no_result := 'FALSE';
245
246 result := no_result;
247 if(prod_name is null) then
248 result := yes_result;
249 return result;
250 end if;
251
252 if(prod_name like '%ALL%'
253 OR prod_name like '%All%'
254 OR prod_name like '%all%') then
255 result := yes_result;
256 return result;
257 end if;
258
259 c_char := ' ';
260 c_count := 0;
261 while (NOT (c_char is null)) loop
262 c_char := ' ';
263 c_word := ' ';
264 while (not (c_char is null)) and nvl(c_char, 'x') <> ',' loop
265 c_word := c_word || c_char;
266 c_count := c_count +1;
267 c_char := substr(prod_name, c_count, 1);
268 end loop;
269
270 --check the product status
271 is_number := TRUE;
272 BEGIN
273 prod_id := to_number(c_word);
274 EXCEPTION
275 WHEN OTHERS THEN
276 is_number := FALSE;
277 END;
278 IF( is_number = TRUE) THEN
279 --BEGIN
280 SELECT COUNT(*)
281 INTO v_count_installed
282 FROM fnd_product_installations
283 WHERE application_id = prod_id
284 AND status = 'I' OR status = 'S';
285 IF (v_count_installed > 0 ) THEN
286 result := yes_result;
287 END IF;
288 END IF;
289 end loop;
290
291 --result := yes_result;
292 RETURN result;
293 END CheckProduct;
294
295
296 -- validate_opm_context
297 -- Private procedure. Called by CheckContext.
298 -- Executes dynamic sql and returns the name and id of opmcontexts
299
300 PROCEDURE validate_opm_context(ctx_type IN varchar2,
301 current_ctx_name OUT NOCOPY VARCHAR2,
302 current_ctx_id OUT NOCOPY NUMBER) IS
303
304 curs integer;
305 rows integer;
306 sqlstatement az_contexts_sql.SQL_STATEMENT%TYPE;
307 opm_id varchar2(30);
308
309 BEGIN
310
311 fnd_profile.get('GEMMS_DEFAULT_ORGN', opm_id);
312
313 --select organization_id, orgn_name
314 --from sy_orgn_mst
315 --where orgn_code = opm_id;
316
317 SELECT sql_statement
318 INTO sqlstatement
319 FROM az_contexts_sql
320 WHERE context = ctx_type
321 AND purpose = 'VALIDATE';
322
323 curs := DBMS_SQL.OPEN_CURSOR;
324 DBMS_SQL.PARSE(curs, sqlstatement, DBMS_SQL.NATIVE);
325
326 DBMS_SQL.DEFINE_COLUMN(curs, 1, current_ctx_id);
327 DBMS_SQL.DEFINE_COLUMN(curs, 2, current_ctx_name, 80);
328 DBMS_SQL.BIND_VARIABLE(curs, ':opm_id', opm_id);
329
330 rows := DBMS_SQL.EXECUTE(curs);
331 rows := DBMS_SQL.FETCH_ROWS(curs);
332
333 DBMS_SQL.COLUMN_VALUE(curs, 1, current_ctx_id);
334 DBMS_SQL.COLUMN_VALUE(curs, 2, current_ctx_name);
335 DBMS_SQL.CLOSE_CURSOR(curs);
336
337 EXCEPTION
338 WHEN OTHERS THEN
339 IF DBMS_SQL.IS_OPEN(curs) then
340 DBMS_SQL.CLOSE_CURSOR(curs);
341 END IF;
342
343 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
344 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
345 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
346 fnd_message.set_token('AZW_ERROR_PROC','azw_util.validate_opm_context');
347 fnd_message.set_token('AZW_ERROR_STMT','UNKNOWN');
348 APP_EXCEPTION.RAISE_EXCEPTION;
349
350 END validate_opm_context;
351
352
353
354 -- CheckContext
355 -- Called by Callback
356 -- Do the context checking
357 --
358 procedure CheckContext(
359 itemtype in varchar2,
360 itemkey in varchar2,
361 result out NOCOPY varchar2) IS
362 context_type varchar2(30);
363 context_name varchar2(2000);
364 context varchar2(2000);
365 task_ctxt_id number;
366 task_ctxt_id_txt varchar2(2000);
367 resp_ctxt_id number;
368
369 act_name WF_ITEMS.ROOT_ACTIVITY%TYPE;
370 bg_id per_business_groups.BUSINESS_GROUP_ID%TYPE;
371 ou_id HR_OPERATING_UNITS.ORGANIZATION_ID%TYPE;
372 io_id ORG_ORGANIZATION_DEFINITIONS.ORGANIZATION_ID%TYPE;
373 BEGIN
374 result := 'TRUE';
375
376 BEGIN
377 task_ctxt_id_txt := wf_engine.GetItemAttrText(itemtype, itemkey,
378 'AZW_IA_CTXT_ID');
379 task_ctxt_id := to_number(task_ctxt_id_txt);
380 EXCEPTION
381 WHEN OTHERS THEN
382 task_ctxt_id := null;
383 END;
384
385 --Get the context type and context name from workflow process
386 context_name := wf_engine.GetItemAttrText(itemtype, itemkey, 'AZW_IA_CTXTNAME');
387 --context_type := wf_engine.GetItemAttrText(itemtype, itemkey, 'AZW_IA_CTXTYP');
388
389 select root_activity into act_name
390 from wf_items
391 where item_type = itemtype
392 and item_key = itemkey;
393
394 select waav.TEXT_DEFAULT into context_type
395 from wf_activity_attributes_vl waav
396 where waav.activity_item_type = itemtype
397 AND act_name = waav.activity_name
398 AND waav.NAME = 'AZW_IA_CTXTYP'
399 AND waav.activity_version =
400 (select max(waav.activity_version)
401 from wf_activity_attributes_vl waav
402 where waav.activity_item_type = itemtype
403 AND act_name = waav.activity_name
404 AND waav.NAME = 'AZW_IA_CTXTYP'
405 );
406
407 --Get the current context value
408 if(context_type = 'BG') then
409 fnd_profile.get('PER_BUSINESS_GROUP_ID', context);
410 bg_id := to_number(context);
411 resp_ctxt_id := bg_id;
412 select name into context
413 from per_business_groups
414 where BUSINESS_GROUP_ID = bg_id;
415 elsif (context_type = 'OU') then
416 fnd_profile.get('ORG_ID', context);
417 ou_id := to_number(context);
418 resp_ctxt_id := ou_id;
419 select name into context
420 from HR_OPERATING_UNITS
421 where ORGANIZATION_ID = ou_id;
422 elsif (context_type = 'SOB') then
423 fnd_profile.get('GL_SET_OF_BKS_ID', context);
424 resp_ctxt_id := to_number(context);
425 fnd_profile.get('GL_SET_OF_BKS_NAME', context);
426
427 elsif (context_type = 'OPMCOM' OR context_type = 'OPMORG') then
428 validate_opm_context(context_type, context, resp_ctxt_id);
429
430 elsif (context_type = 'IO') then
431 --update wf_notification_attributes
432 --set text_value = 'INVIDITM'
433 --where notification_id = 822
434 --and name = 'AZW_IA_FORM';
435 --fnd_org.choose_org;
436 return;
437 else
438 return;
439 end if;
440
441 context_name := NVL(context_name, ' ');
442 context := NVL(context, ' ');
443
444 if(task_ctxt_id is not null) then
445 if(task_ctxt_id = resp_ctxt_id) then
446 result := 'TRUE';
447 else
448 result := 'FALSE';
449 end if;
450 else
451 if( context_name = context) then
452 result := 'TRUE';
453 else
454 result := 'FALSE';
455 end if;
456 end if;
457 if( result = 'TRUE') then
458 return;
459 end if;
460
461 if (context_type = 'SOB') then
462 fnd_message.set_name('AZ', 'AZW_INCORRECT_CONTEXT_SOB');
463 elsif (context_type = 'OU') then
464 fnd_message.set_name('AZ', 'AZW_INCORRECT_CONTEXT_OU');
465 elsif (context_type = 'BG') then
466 fnd_message.set_name('AZ', 'AZW_INCORRECT_CONTEXT_BG');
467 elsif (context_type = 'IO') then
468 fnd_message.set_name('AZ', 'AZW_INCORRECT_CONTEXT_INV');
469 elsif (context_type = 'OPMCOM') then
470 fnd_message.set_name('AZ', 'AZW_INCORRECT_CONTEXT_OPMCOM');
471 elsif (context_type = 'OPMORG') then
472 fnd_message.set_name('AZ', 'AZW_INCORRECT_CONTEXT_OPMORG');
473 end if;
474 fnd_message.set_token('AZWCURTYPE', context_type);
475 fnd_message.set_token('AZWCURCTXT', context);
476 fnd_message.set_token('AZWDESTYPE', context_type);
477 fnd_message.set_token('AZWDESCTXT', context_name);
478 APP_EXCEPTION.RAISE_EXCEPTION;
479
480 END CheckContext;
481
482 -- Callback
483 -- Called by notification form to do context checking
484 --
485 procedure Callback(
486 itemtype in varchar2,
487 itemkey in varchar2,
488 actid in number,
489 command in varchar2,
490 result in out NOCOPY varchar2 ) IS
491 task_ctxt_id number;
492 task_ctxt_id_txt varchar2(2000);
493
494 BEGIN
495 if (command = 'TEST_CTX') then
496 CheckContext(itemtype, itemkey, result);
497 end if;
498
499 EXCEPTION
500 WHEN app_exception.application_exception THEN
501 RAISE ;
502 WHEN OTHERS THEN
503 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
504 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
505 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
506 fnd_message.set_token('AZW_ERROR_PROC','azw_util.Callback');
507 fnd_message.set_token('AZW_ERROR_STMT','Call to CheckContext');
508 APP_EXCEPTION.RAISE_EXCEPTION;
509 END Callback;
510
511 -- PreviousStep
512 -- Called by notification form
513 -- Go back to the previous notification.
514 --
515 procedure PreviousStep(
516 itemtype in varchar2,
517 itemkey in varchar2,
518 result out NOCOPY varchar2) IS
519
520 CURSOR wf_act_c IS
521 select wpa.instance_label, wpa.process_name, wias.notification_id
522 from wf_item_activity_statuses wias,
523 wf_process_activities wpa
524 where wias.item_type = itemtype
525 and wias.item_key = itemkey
526 and wias.notification_id is not null
527 and wias.process_activity = wpa.instance_id
528 order by wias.begin_date desc;
529
530
531 pre_act_label wf_process_activities.instance_label%TYPE;
532 pre_process_name wf_process_activities.process_name%TYPE;
533 pre_notification_id wf_item_activity_statuses_v.notification_id%TYPE;
534
535 cur_act_label wf_process_activities.instance_label%TYPE;
536 cur_process_name wf_process_activities.process_name%TYPE;
537 cur_notification_id wf_item_activity_statuses_v.notification_id%TYPE;
538 comment wf_notifications.user_comment%TYPE;
539
540 BEGIN
541
542 result := 'FALSE';
543 open wf_act_c;
544 FETCH wf_act_c INTO cur_act_label, cur_process_name, cur_notification_id;
545 FETCH wf_act_c INTO pre_act_label, pre_process_name, pre_notification_id;
546 if wf_act_c%FOUND then
547 --Get the previous comment
548 select wn.user_comment into comment
549 from wf_notifications wn
550 where wn.notification_id = pre_notification_id;
551
552 --Cancel the current message
553 --Deliver the previous message
554 wf_engine.handleerror(itemtype, itemkey,pre_process_name||':'|| pre_act_label, 'RETRY');
555
556 --Store the comment into the new notification
557 close wf_act_c;
558 open wf_act_c;
559 FETCH wf_act_c INTO cur_act_label, cur_process_name, cur_notification_id;
560 update wf_notifications
561 set user_comment = comment
562 where notification_id = cur_notification_id;
563
564 result := 'TRUE';
565 end if;
566 close wf_act_c;
567 commit;
568
569 EXCEPTION
570 WHEN OTHERS THEN
571 result := SQLERRM;
572
573 END PreviousStep;
574
575 end AZW_UTIL;
576