1 PACKAGE BODY asg_helper AS
2 /*$Header: asghlpb.pls 120.7 2008/05/16 09:54:37 saradhak ship $*/
3
4 -- DESCRIPTION
5 -- This package is used for miscellaneous chores
6 --
7 -- HISTORY
8 -- 29-sep-2004 ssabesan Removed method truncate_sdq
9 -- 08-sep-2004 ssabesan remove code in recreate_synonyms() from 115.43
10 -- 24-jun-2004 rsripada Fix bug 3720692
11 -- 23-jun-2004 ssabesan Fix bug 3713556
12 -- 01-jun-2004 ssabesan Change literals to bind variables.
13 -- 14-may-2004 ssabesan Fix GSCC warning - Standard File.Sql.6
14 -- 16-apr-2004 rsripada Provide additional privs to inq table
15 -- 05-apr-2004 rsripada Add enable_olite_privs
16 -- 01-apr-2004 ssabesan Added en/decrypt, set_profile_to_null routines
17 -- 13-jan-2004 ssabesan Added method recreate_synonyms() for use during
18 -- user creation.
19 -- 30-dec-2003 rsripada Added procedures for creating/dropping olite
20 -- synonyms
21 -- 11-nov-2003 ssabesan modified set_synch_errmsg to write into
22 -- asg_user_pub_resps.synch_date
23 -- 22-oct-2003 ssabesan Merge 115.23.1158.15 into mainline
24 -- 01-oct-2003 ssabesan Purge SDQ changes (bug 3170790)
25 -- 12-jun-2003 rsripada Added proc to determine last synch device type
26 -- 10-apr-2003 ssabesan for logging user_setup and synch errors use
27 -- last_wireless_contact_date column.
28 -- 26-mar-2003 rsripada Removed default values in log procedure
29 -- 25-mar-2003 ssabesan Added API for updating user_sertup_errors and
30 -- synch_errors column in asg_user
31 -- 12-feb-2003 ssabesan Added API for updating hwm_tranid in asg_user
32 -- 10-jan-2003 ssabesan Added a wrapper around check_is_log_enabled()
33 -- for use from java program.
34 -- 06-jan-2003 ssabesan PL/SQL API changes. Added method for checking
35 -- whether logging is enabled.
36 -- 02-jan-2003 rsripada Bug fix 2731476
37 -- 12-dec-2002 rsripada Do not populate access records for custom pub
38 -- 19-nov-2002 rsripada Added routines for disabling user synch
39 -- 10-nov-2002 ssabesan added routine for specifying a pub-item
40 -- to be completely refreshed
41 -- 09-sep-2002 rsripada Added routines to enable/disable synch
42 -- 15-aug-2002 rsripada Fixed 2504496
43 -- 17-jul-2002 rsripada Catch exception in callouts and added logging
44 -- 18-jun-2002 rsripada Modified log to allow easier debug
45 -- 04-jun-2002 rsripada Initialized logging
46 -- 28-may-2002 rsripada Created
47
48 g_initialize_log BOOLEAN := FALSE;
49 g_stmt_level NUMBER := FND_LOG.LEVEL_STATEMENT;
50 g_err_level NUMBER := FND_LOG.LEVEL_ERROR;
51 g_svc VARCHAR2(30) := 'ASG_OLITE';
52
53
54 FUNCTION check_is_log_enabled(log_level IN NUMBER)
55 RETURN BOOLEAN
56 IS
57 l_userid NUMBER;
58 l_respid NUMBER;
59 l_appid NUMBER;
60 BEGIN
61 IF(g_initialize_log = TRUE )
62 THEN
63 IF(log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
64 THEN
65 RETURN true;
66 ELSE
67 RETURN false;
68 END IF;
69 ELSE
70 l_userid := fnd_global.user_id();
71 l_respid := fnd_global.resp_id();
72 l_appid := fnd_global.resp_appl_id();
73 IF l_userid IS NULL or l_userid = -1 THEN
74 l_userid := 5;
75 END IF;
76 IF l_respid IS NULL or l_respid = -1 THEN
77 l_respid := 20420;
78 END IF;
79 IF l_appid IS NULL or l_appid = -1 THEN
80 l_appid := 1;
81 END IF;
82 fnd_global.apps_initialize(l_userid,
83 l_respid,
84 l_appid);
85 fnd_log_repository.init();
86 g_initialize_log := TRUE;
87 IF(log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
88 THEN
89 RETURN true;
90 ELSE
91 RETURN false;
92 END IF;
93 END IF;
94 END check_is_log_enabled;
95
96 --wrapper around check_is_log_enabled(log_level IN NUMBER)
97 --for use from java programs.
98 FUNCTION check_log_enabled(log_level IN NUMBER)
99 RETURN VARCHAR2
100 IS
101 l_retval varchar2(1);
102 BEGIN
103 IF(check_is_log_enabled(log_level))
104 THEN
105 l_retval := 'Y'; -- log is enabled
106 ELSE
107 l_retval := 'N'; -- log is disabled
108 END IF;
109 RETURN l_retval;
110 END check_log_enabled;
111
112 -- Invokes the callback to populate all the user's acc tables
113 PROCEDURE populate_access(p_user_name IN VARCHAR2,
114 p_pub_name IN VARCHAR2)
115 IS
116 CURSOR c_wrapper_name(p_pub_name VARCHAR2) IS
117 SELECT wrapper_name
118 FROM asg_pub
119 WHERE name = p_pub_name;
120 CURSOR c_custom_pub(p_pub_name VARCHAR2) IS
121 SELECT nvl(custom, 'N')
122 FROM asg_pub
123 WHERE name = p_pub_name;
124 l_custom_pub VARCHAR2(1);
125 l_callback_string VARCHAR2(512);
126 l_user_id NUMBER;
127 l_wrapper_name asg_pub.wrapper_name%type;
128 BEGIN
129
130 IF(p_pub_name = 'ALL') THEN
131 return;
132 END IF;
133
134 -- Check if the publication is custom and return if it is
135 OPEN c_custom_pub(p_pub_name);
136 FETCH c_custom_pub into l_custom_pub;
137 CLOSE c_custom_pub ;
138 IF (l_custom_pub = 'Y') THEN
139 return;
140 END IF;
141 IF(check_is_log_enabled(g_stmt_level))
142 THEN
143 log('Calling populate_access_records for user: ' || p_user_name ||
144 ' and publication: ' || p_pub_name,'asg_helper',g_stmt_level);
145 END IF;
146
147 OPEN c_wrapper_name(p_pub_name);
148 FETCH c_wrapper_name into l_wrapper_name;
149 CLOSE c_wrapper_name;
150 IF l_wrapper_name IS NULL THEN
151 IF(check_is_log_enabled(g_err_level))
152 THEN
153 log('Wrapper for publication: ' || p_pub_name || ' is null',
154 'asg_helper',g_err_level);
155 END IF;
156 RAISE_APPLICATION_ERROR(-20989, 'Callback package missing for ' ||
157 'publication: ' || p_pub_name);
158 END IF;
159 l_user_id := asg_base.get_user_id(p_user_name);
160
161 BEGIN
162 l_callback_string := 'BEGIN ' || l_wrapper_name ||
163 '.populate_access_records( :1 ); END;';
164 IF(check_is_log_enabled(g_stmt_level))
165 THEN
166 log('Callback SQLCommand: ' || l_callback_string,
167 'asg_helper',g_stmt_level);
168 END IF;
169 EXECUTE IMMEDIATE l_callback_string
170 USING l_user_id;
171 EXCEPTION
172 WHEN OTHERS THEN
173 IF(check_is_log_enabled(g_err_level))
174 THEN
175 log('Exception in call to populate access records: ' ||
176 SQLERRM, 'asg_helper',g_err_level);
177 END IF;
178 RAISE;
179 END;
180
181 END populate_access;
182
183 -- Invokes the callback to remove all acc table records
184 PROCEDURE delete_access(p_user_name IN VARCHAR2,
185 p_pub_name IN VARCHAR2)
186 IS
187 CURSOR c_wrapper_name(p_pub_name VARCHAR2) IS
188 SELECT wrapper_name
189 FROM asg_pub
190 WHERE name = p_pub_name;
191 CURSOR c_custom_pub(p_pub_name VARCHAR2) IS
192 SELECT nvl(custom, 'N')
193 FROM asg_pub
194 WHERE name = p_pub_name;
195 l_custom_pub VARCHAR2(1);
196 l_callback_string VARCHAR2(512);
197 l_user_id NUMBER;
198 l_wrapper_name asg_pub.wrapper_name%type;
199 BEGIN
200
201 IF(p_pub_name = 'ALL') THEN
202 return;
203 END IF;
204
205 -- Check if the publication is custom and return if it is
206 OPEN c_custom_pub(p_pub_name);
207 FETCH c_custom_pub into l_custom_pub;
208 CLOSE c_custom_pub ;
209 IF (l_custom_pub = 'Y') THEN
210 return;
211 END IF;
212 IF(check_is_log_enabled(g_stmt_level))
213 THEN
214 log('Calling delete_access_records for user: ' || p_user_name ||
215 ' and publication: ' || p_pub_name,'asg_helper',g_stmt_level);
216 END IF;
217 OPEN c_wrapper_name(p_pub_name);
218 FETCH c_wrapper_name into l_wrapper_name;
219 CLOSE c_wrapper_name;
220 IF l_wrapper_name IS NULL THEN
221 IF(check_is_log_enabled(g_err_level))
222 THEN
223 log('Wrapper for publication: ' || p_pub_name || ' is null',
224 'asg_helper',g_err_level);
225 END IF;
226 RAISE_APPLICATION_ERROR(-20989, 'Callback package missing for ' ||
227 'publication: ' || p_pub_name);
228 END IF;
229 l_user_id := asg_base.get_user_id(p_user_name);
230
231 BEGIN
232 l_callback_string := 'BEGIN ' || l_wrapper_name ||
233 '.delete_access_records(:2); END;';
234 IF(check_is_log_enabled(g_stmt_level))
235 THEN
236 log('Callback SQLCommand: ' || l_callback_string,
237 'asg_helper',g_stmt_level);
238 END IF;
239 EXECUTE IMMEDIATE l_callback_string
240 USING l_user_id;
241 EXCEPTION
242 WHEN OTHERS THEN
243 IF(check_is_log_enabled(g_err_level))
244 THEN
245 log('Exception in call to delete access records: ' ||
246 SQLERRM, 'asg_helper',g_err_level);
247 END IF;
248 RAISE;
249 END;
250
251 END delete_access;
252
253 -- Creates a sequence partitions
254 PROCEDURE create_seq_partition(p_user_name IN VARCHAR2,
255 p_seq_name IN VARCHAR2,
256 p_start_value IN VARCHAR2,
257 p_next_value IN VARCHAR2)
258 IS
259 BEGIN
260 -- Delete the row before inserting
261 -- #$% Can remove delete after debug/qa
262 DELETE FROM asg_sequence_partitions
263 WHERE clientid = p_user_name AND name = p_seq_name;
264
265 INSERT INTO asg_sequence_partitions (
266 CLIENTID,
267 NAME,
268 CURR_VAL,
269 INCR,
270 LAST_UPDATE_DATE,
271 LAST_UPDATED_BY,
272 CREATION_DATE,
273 CREATED_BY)
274 values
275 (p_user_name,
276 p_seq_name,
277 to_number(p_start_value),
278 to_number(p_next_value),
279 sysdate,
280 1,
281 sysdate,
282 1);
283 COMMIT;
284 IF(check_is_log_enabled(g_stmt_level))
285 THEN
286 log('Created Sequence Partition for user: ' || p_user_name ||
287 ' and sequence: ' || p_seq_name || ' with start value: ' ||
288 p_start_value || ' and next value: ' || p_next_value,
289 'asg_helper',g_stmt_level);
290 END IF;
291
292 END create_seq_partition;
293
294 -- Drop the sequence partition
295 PROCEDURE drop_seq_partition(p_user_name IN VARCHAR2,
296 p_seq_name IN VARCHAR2)
297 IS
298 BEGIN
299
300 DELETE FROM asg_sequence_partitions
301 WHERE clientid = p_user_name AND name = p_seq_name;
302
303 COMMIT;
304 IF(check_is_log_enabled(g_stmt_level))
305 THEN
306 log('Dropped Sequence Partition for user: ' || p_user_name ||
307 ' and sequence: ' || p_seq_name,'asg_helper',g_stmt_level);
308 END IF;
309 END drop_seq_partition;
310
311 -- insert pub responsibilities
312 PROCEDURE insert_user_pub_resp(p_user_name IN VARCHAR2,
313 p_pub_name IN VARCHAR2,
314 p_resp_id IN NUMBER,
315 p_app_id IN NUMBER)
316 IS
317 BEGIN
318 DELETE FROM asg_user_pub_resps
319 WHERE user_name = p_user_name AND
320 pub_name = p_pub_name AND
321 responsibility_id = p_resp_id AND
322 app_id = p_app_id;
323
324 INSERT INTO asg_user_pub_resps (
325 USER_NAME,
326 PUB_NAME,
327 SYNCH_DISABLED,
328 RESPONSIBILITY_ID,
329 APP_ID,
330 LAST_UPDATE_DATE,
331 LAST_UPDATED_BY,
332 CREATION_DATE,
333 CREATED_BY)
334 VALUES
335 (p_user_name,
336 p_pub_name,
337 'N',
338 p_resp_id,
339 p_app_id,
340 sysdate,
341 1,
342 sysdate,
343 1);
344 COMMIT;
345 IF(check_is_log_enabled(g_stmt_level))
346 THEN
347 log('Created user pub responsibility record for user: ' || p_user_name ||
348 ' and publication: ' || p_pub_name || ' and responsibility id: ' ||
349 p_resp_id || ' and app id: ' || p_app_id,'asg_helper',g_stmt_level);
350 END IF;
351
352 END insert_user_pub_resp;
353
354 --delete user-pub
355 PROCEDURE delete_user_pub(p_user_name IN VARCHAR2,
356 p_pub_name IN VARCHAR2)
357 IS
358 BEGIN
359
360 DELETE FROM asg_user_pub_resps
361 WHERE user_name = p_user_name AND
362 pub_name = p_pub_name;
363
364 DELETE FROM asg_purge_sdq
365 WHERE user_name = p_user_name AND
366 pub_name = p_pub_name;
367
368 DELETE FROM asg_complete_refresh
369 WHERE user_name = p_user_name AND
370 publication_item IN
371 ( SELECT item_id FROM asg_pub_item
372 WHERE pub_name = p_pub_name);
373
374 COMMIT;
375 IF(check_is_log_enabled(g_stmt_level))
376 THEN
377 log('Deleted user pub record for user: ' || p_user_name ||
378 ' and publication: ' || p_pub_name,'asg_helper',g_stmt_level);
379 END IF;
380 END delete_user_pub;
381
382
383 -- delete pub responsibilites
384 PROCEDURE delete_user_pub_resp(p_user_name IN VARCHAR2,
385 p_pub_name IN VARCHAR2,
386 p_resp_id IN NUMBER)
387 IS
388 BEGIN
389
390 DELETE FROM asg_user_pub_resps
391 WHERE user_name = p_user_name AND
392 pub_name = p_pub_name AND
393 responsibility_id = p_resp_id;
394
395 COMMIT;
396 IF(check_is_log_enabled(g_stmt_level))
397 THEN
398 log('Deleted user pub responsibility record for user: ' || p_user_name ||
399 ' and publication: ' || p_pub_name || ' and responsibility id: ' ||
400 p_resp_id,'asg_helper',g_stmt_level);
401 END IF;
402 END delete_user_pub_resp;
403
404 -- wrapper on fnd_log
405 PROCEDURE log(message IN VARCHAR2,
406 module IN VARCHAR2,
407 log_level IN NUMBER)
408 IS
409 l_userid NUMBER;
410 l_respid NUMBER;
411 l_appid NUMBER;
412 l_message VARCHAR2(4000);
413 l_start_string VARCHAR2(64);
414 BEGIN
415 l_start_string := asg_base.get_user_name() ||
416 ',' || asg_base.get_current_tranid() || ': ';
417 IF asg_base.get_user_name() IS NULL THEN
418 l_message := message;
419 ELSE
420 l_message := l_start_string || message;
421 END IF;
422 IF (log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
423 FND_LOG.STRING(log_level, module, l_message);
424 ELSE
425 l_userid := fnd_global.user_id();
426 l_respid := fnd_global.resp_id();
427 l_appid := fnd_global.resp_appl_id();
428 IF l_userid IS NULL or l_userid = -1 THEN
429 l_userid := 5;
430 END IF;
431 IF l_respid IS NULL or l_respid = -1 THEN
432 l_respid := 20420;
433 END IF;
434 IF l_appid IS NULL or l_appid = -1 THEN
435 l_appid := 1;
436 END IF;
437 fnd_global.apps_initialize(l_userid,
438 l_respid,
439 l_appid);
440 fnd_log_repository.init();
441 g_initialize_log := TRUE;
442 IF (log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
443 fnd_log.string(log_level, module, l_message);
444 end if;
445 END IF;
446 END log;
447
448
449 -- Used to clean up metadata associated with an user
450 PROCEDURE drop_user(p_user_name IN VARCHAR2)
451 IS
452 l_user_name VARCHAR2(30);
453 l_bool_ret BOOLEAN;
454 CURSOR c_user_pubs(p_user_name VARCHAR2) IS
455 SELECT pub_name
456 FROM asg_user_pub_resps
457 WHERE user_name = p_user_name;
458 BEGIN
459 l_user_name := upper(p_user_name);
460
461 -- Call delete access for all the publications the user
462 -- is subscribed to
463 FOR cups in c_user_pubs(l_user_name) LOOP
464 delete_access(l_user_name, cups.pub_name);
465 END LOOP;
466
467 DELETE FROM asg_user_pub_resps
468 WHERE user_name = p_user_name;
469
470 DELETE FROM ASG_USERS_INQARCHIVE
471 WHERE device_user_name = l_user_name;
472
473 -- Before drop is called we check to see if there are any
474 -- unprocessed inq transactions. So, at this point all of them
475 -- should have been processed.
476 DELETE FROM ASG_DEFERRED_TRANINFO
477 WHERE device_user_name = l_user_name;
478
479 DELETE FROM ASG_USERS_INQINFO
480 WHERE device_user_name = l_user_name;
481
482 --12.1
483 DELETE FROM asg_auto_sync_tranids
484 WHERE user_name = l_user_name;
485
486 BEGIN
487 l_bool_ret := asg_download.purgesdq(l_user_name);
488 EXCEPTION
489 WHEN OTHERS THEN
490 IF(check_is_log_enabled(g_err_level))
491 THEN
492 log('Exception in purgesdq during drop_user: ' || l_user_name,
493 'asg_helper',g_err_level);
494 END IF;
495 END;
496
497 --delete from asg_purge_sdq and asg_complete_refresh
498
499 DELETE FROM asg_purge_sdq
500 WHERE user_name = l_user_name;
501
502 DELETE FROM asg_complete_refresh
503 WHERE user_name = l_user_name;
504
505 DELETE FROM asg_sequence_partitions
506 WHERE clientid = p_user_name;
507
508 DELETE FROM ASG_USER
509 WHERE user_name = l_user_name;
510
511 COMMIT;
512 IF(check_is_log_enabled(g_stmt_level))
513 THEN
514 log('Done cleaning up user meta data during drop user: ' || p_user_name,
515 'asg_helper',g_stmt_level);
516 END IF;
517 EXCEPTION
518 WHEN OTHERS THEN
519 IF(check_is_log_enabled(g_err_level))
520 THEN
521 log('Exception in drop_user ' || SQLERRM,
522 'asg_helper',g_err_level);
523 END IF;
524 RAISE;
525 END drop_user;
526
527 -- Used to update a parameter in asg_config
528 PROCEDURE set_config_param(p_param_name IN VARCHAR2,
529 p_param_value IN VARCHAR2,
530 p_param_description IN VARCHAR2 := NULL)
531 IS
532 BEGIN
533
534 asg_config_pkg.load_row(p_param_name, p_param_value,
535 p_param_description,
536 sysdate, 1,
537 sysdate, 1,
538 FND_API.G_MISS_CHAR);
539 COMMIT;
540
541 END set_config_param;
542
543 -- Returns the value column in asg_config table based on the
544 -- specified parameter name
545 FUNCTION get_param_value(p_param_name IN VARCHAR2)
546 return VARCHAR2 IS
547 CURSOR C_PARAM_VALUE(p_param_name varchar2) IS
548 SELECT value
549 FROM asg_config
550 WHERE name = p_param_name;
551 l_param_value VARCHAR2(2000);
552 BEGIN
553
554 OPEN C_PARAM_VALUE(p_param_name);
555 FETCH C_PARAM_VALUE into l_param_value;
556 CLOSE C_PARAM_VALUE;
557 return l_param_value;
558
559 END get_param_value;
560
561 -- Used to enable synch for all publications
562 PROCEDURE enable_synch
563 IS
564 BEGIN
565
566 UPDATE asg_pub
567 SET enable_synch = 'Y';
568 COMMIT;
569
570 END enable_synch;
571
572
573 -- Used to enable synch for the specified publication
574 PROCEDURE enable_pub_synch(p_pub_name IN VARCHAR2)
575 IS
576 BEGIN
577
578 UPDATE asg_pub
579 SET enable_synch = 'Y'
580 WHERE name = upper(p_pub_name);
581 COMMIT;
582
583 END enable_pub_synch;
584
585 -- Used to disable synch for all publications
586 PROCEDURE disable_synch
587 IS
588 BEGIN
589
590 UPDATE asg_pub
591 SET enable_synch = 'N';
592 COMMIT;
593
594 END disable_synch;
595
596 -- Used to disable synch for the specified publication
597 PROCEDURE disable_pub_synch(p_pub_name IN VARCHAR2)
598 IS
599 BEGIN
600
601 UPDATE asg_pub
602 SET enable_synch = 'N'
603 WHERE name = upper(p_pub_name);
604 COMMIT;
605
606 END disable_pub_synch;
607
608 -- Returns FND_API.G_TRUE if the user synch is enabled
609 FUNCTION is_user_synch_enabled(p_user_name IN VARCHAR2,
610 p_disabled_synch_message OUT NOCOPY VARCHAR2)
611 return VARCHAR2 IS
612 l_synch_enabled VARCHAR2(1) := FND_API.G_TRUE;
613 l_disabled_user PLS_INTEGER;
614 l_disabled_pubs PLS_INTEGER;
615 l_query_string VARCHAR2(2000);
616 BEGIN
617
618 -- First, check if the user was created properly
619 SELECT count(*) into l_disabled_user
620 FROM asg_user
621 WHERE user_name = p_user_name AND
622 enabled = 'N';
623
624 IF l_disabled_user > 0 THEN
625 l_synch_enabled := FND_API.G_FALSE;
626 p_disabled_synch_message := asg_helper.get_param_value(
627 'DISABLED_SYNCH_MESSAGE_UC');
628 return l_synch_enabled;
629 END IF;
630
631 -- Check if access table population has been completed for this user
632 l_query_string := 'SELECT count(*) ' ||
633 'FROM asg_user_pub_resps aup, asg_pub ap ' ||
634 'WHERE aup.user_name = :1 AND ' ||
635 ' aup.pub_name = ap.name AND ' ||
636 ' aup.synch_disabled = ''Y'' AND ' ||
637 ' ap.name in ' ||
638 ' (SELECT distinct pub_name ' ||
639 ' FROM asg_pub_item api, ' ||
640 asg_base.G_OLITE_SCHEMA || '.c$pub_list_q cpq ' ||
641 ' where api.name = cpq.name)';
642
643 EXECUTE IMMEDIATE l_query_string
644 INTO l_disabled_pubs
645 USING p_user_name;
646
647 IF (l_disabled_pubs >0) THEN
648 l_synch_enabled := FND_API.G_FALSE;
649 p_disabled_synch_message := asg_helper.get_param_value(
650 'DISABLED_SYNCH_MESSAGE_ACC');
651 return l_synch_enabled;
652 END IF;
653
654 -- Check if synchronization is disabled for any of the
655 -- publications the user is subscribed to
656 -- and downloading as part of current synch due to patching
657 l_query_string := 'SELECT count(*) ' ||
658 'FROM asg_user_pub_resps aup, asg_pub ap ' ||
659 'WHERE aup.user_name = :1 AND ' ||
660 ' aup.pub_name = ap.name AND ' ||
661 ' ap.enable_synch = ''N'' AND ' ||
662 ' ap.name in ' ||
663 ' (SELECT distinct pub_name ' ||
664 ' FROM asg_pub_item api, ' ||
665 asg_base.G_OLITE_SCHEMA || '.c$pub_list_q cpq ' ||
666 ' where api.name = cpq.name)';
667
668 EXECUTE IMMEDIATE l_query_string
669 INTO l_disabled_pubs
670 USING p_user_name;
671
672 IF (l_disabled_pubs >0) THEN
673 l_synch_enabled := FND_API.G_FALSE;
674 p_disabled_synch_message := asg_helper.get_param_value(
675 'DISABLED_SYNCH_MESSAGE_PATCH');
676 END IF;
677
678 return l_synch_enabled;
679
680 END is_user_synch_enabled;
681
682 --routine for setting complete_refresh for a pub-item for all users
683 --subscribed to that Pub-Item.
684 PROCEDURE set_complete_refresh(p_pub_item VARCHAR2)
685 IS
686 CURSOR c_all_users(pi_name VARCHAR2) IS
687 SELECT user_name
688 FROM asg_user_pub_resps aup, asg_pub_item api
689 WHERE api.name = upper(pi_name) AND
690 api.pub_name = aup.pub_name;
691 CURSOR c_row_exists(pi_name VARCHAR2) IS
692 SELECT count(*)
693 FROM asg_complete_refresh
694 WHERE publication_item=pi_name;
695 CURSOR c_new_users(pi_name VARCHAR2) IS
696 SELECT user_name
697 FROM asg_user_pub_resps aup, asg_pub_item api
698 WHERE api.name = upper(pi_name) AND
699 aup.pub_name = api.pub_name AND
700 user_name NOT IN
701 ( SELECT user_name
702 FROM asg_complete_refresh
703 WHERE publication_item = pi_name );
704 l_pub_item VARCHAR2(30);
705 l_cnt NUMBER :=0;
706 l_uname c_all_users%ROWTYPE;
707 l_recf1 c_new_users%ROWTYPE;
708 BEGIN
709
710 IF ((p_pub_item = FND_API.G_MISS_CHAR) OR (p_pub_item IS NULL)) THEN
711 return;
712 END IF;
713
714 l_pub_item := upper(p_pub_item);
715
716 -- Check if there already some records for this publication item
717 OPEN c_row_exists(l_pub_item);
718 FETCH c_row_exists INTO l_cnt;
719 -- Records exist
720 IF(l_cnt > 0) THEN
721 -- Reset all the records so that they will be completely refreshed
722 UPDATE asg_complete_refresh
723 SET last_update_date=sysdate,synch_completed='N'
724 WHERE publication_item=l_pub_item;
725
726 OPEN c_new_users(l_pub_item);
727 LOOP
728 FETCH c_new_users INTO l_recf1;
729 EXIT WHEN c_new_users%NOTFOUND;
730 INSERT INTO asg_complete_refresh(
731 USER_NAME,
732 PUBLICATION_ITEM,
733 SYNCH_COMPLETED,
734 CREATION_DATE,
735 CREATED_BY,
736 LAST_UPDATE_DATE,
737 LAST_UPDATED_BY)
738 VALUES(
739 l_recf1.user_name,
740 l_pub_item,
741 'N',
742 sysdate,
743 1,
744 sysdate,
745 1);
746 END LOOP;
747 CLOSE c_new_users;
748 CLOSE c_row_exists;
749 COMMIT;
750 RETURN;
751 END IF;
752 CLOSE c_row_exists;
753
754 OPEN c_all_users(l_pub_item);
755 LOOP
756 FETCH c_all_users INTO l_uname;
757 EXIT WHEN c_all_users%NOTFOUND;
758 INSERT INTO asg_complete_refresh(
759 USER_NAME,
760 PUBLICATION_ITEM,
761 SYNCH_COMPLETED,
762 CREATION_DATE,
763 CREATED_BY,
764 LAST_UPDATE_DATE,
765 LAST_UPDATED_BY)
766 VALUES(
767 l_uname.user_name,
768 l_pub_item,
769 'N',
770 sysdate,
771 1,
772 sysdate,
773 1);
774 END LOOP;
775 CLOSE c_all_users;
776 COMMIT;
777 END set_complete_refresh;
778
779 -- Disables synch for specified user/publication
780 PROCEDURE disable_user_pub_synch(p_user_id IN NUMBER,
781 p_pub_name IN VARCHAR2)
782 IS
783 CURSOR C_USER_NAME(p_user_id NUMBER) IS
784 SELECT user_name
785 FROM asg_user
786 WHERE user_id = p_user_id;
787 l_user_name VARCHAR2(30);
788 PRAGMA AUTONOMOUS_TRANSACTION;
789 BEGIN
790 IF ((p_user_id = FND_API.G_MISS_NUM) OR (p_user_id IS NULL)) OR
791 ((p_pub_name = FND_API.G_MISS_CHAR) OR (p_pub_name IS NULL)) THEN
792 return;
793 END IF;
794
795 OPEN C_USER_NAME(p_user_id);
796 FETCH C_USER_NAME into l_user_name;
797 CLOSE C_USER_NAME;
798
799 UPDATE asg_user_pub_resps
800 SET SYNCH_DISABLED = 'Y'
801 WHERE user_name = l_user_name AND
802 pub_name = p_pub_name;
803 COMMIT;
804 EXCEPTION
805 WHEN OTHERS THEN
806 ROLLBACK;
807 END disable_user_pub_synch;
808
809 -- Enables synch for specified user/publication
810 PROCEDURE enable_user_pub_synch(p_user_id IN NUMBER,
811 p_pub_name IN VARCHAR2)
812 IS
813 CURSOR C_USER_NAME(p_user_id NUMBER) IS
814 SELECT user_name
815 FROM asg_user
816 WHERE user_id = p_user_id;
817 l_user_name VARCHAR2(30);
818 PRAGMA AUTONOMOUS_TRANSACTION;
819 BEGIN
820 IF ((p_user_id = FND_API.G_MISS_NUM) OR (p_user_id IS NULL)) OR
821 ((p_pub_name = FND_API.G_MISS_CHAR) OR (p_pub_name IS NULL)) THEN
822 return;
823 END IF;
824
825 OPEN C_USER_NAME(p_user_id);
826 FETCH C_USER_NAME into l_user_name;
827 CLOSE C_USER_NAME;
828
829 UPDATE asg_user_pub_resps
830 SET SYNCH_DISABLED = 'N'
831 WHERE user_name = l_user_name AND
832 pub_name = p_pub_name;
833 COMMIT;
834 EXCEPTION
835 WHEN OTHERS THEN
836 ROLLBACK;
837 END enable_user_pub_synch;
838
839 -- Enables all users access to public group
840 PROCEDURE set_group_access(p_group_name IN VARCHAR2)
841 IS
842 l_grp_name VARCHAR2(256);
843 l_grp_id NUMBER;
844 l_query_string VARCHAR2(2000);
845 BEGIN
846
847 IF (p_group_name IS NULL) OR (p_group_name = FND_API.G_MISS_CHAR) THEN
848 return;
849 END IF;
850 l_grp_name := upper(p_group_name);
851
852 -- Get the group id first
853 l_query_string := 'SELECT id ' ||
854 'FROM ' || asg_base.G_OLITE_SCHEMA || '.groups grp ' ||
855 'WHERE grp.name = :group_name';
856
857 EXECUTE IMMEDIATE l_query_string
858 INTO l_grp_id
859 USING l_grp_name;
860
861 IF (l_grp_id IS NULL) THEN
862 return;
863 END IF;
864
865 -- Insert into usr_grp all users who do not have access to
866 -- this group
867 l_query_string :=
868 'INSERT INTO ' ||
869 asg_base.G_OLITE_SCHEMA || '.usr_grp ' ||
870 '(entity_id, entity_type, grp_id) ' ||
871 'SELECT usr.id, 0, :group_id ' ||
872 'FROM ' || asg_base.G_OLITE_SCHEMA || '.users usr ' ||
873 'WHERE usr.id not in ' ||
874 ' (SELECT usr2.id ' ||
875 ' FROM ' || asg_base.G_OLITE_SCHEMA || '.users usr2,' ||
876 asg_base.G_OLITE_SCHEMA || '.usr_grp ugrp ' ||
877 ' WHERE ugrp.grp_id = :group_id AND ' ||
878 ' usr2.id = ugrp.entity_id) AND ' ||
879 ' usr.name in ' ||
880 ' (SELECT user_name ' ||
881 ' FROM asg_user)';
882
883 EXECUTE IMMEDIATE l_query_string
884 USING l_grp_id,l_grp_id;
885
886 END set_group_access;
887
888 --API for updating hwm_tranid column in asg_user table
889 PROCEDURE update_hwm_tranid(p_user_name IN VARCHAR2,p_tranid IN NUMBER)
890 IS
891 BEGIN
892 UPDATE asg_user
893 SET hwm_tranid=p_tranid
894 WHERE user_name=UPPER(p_user_name);
895 END update_hwm_tranid;
896
897 --API for autonomous update of USER_SETUP_ERRORS column in asg_user table
898 PROCEDURE update_user_setup_errors(p_user_name IN VARCHAR2,p_mesg IN VARCHAR2)
899 IS
900 PRAGMA AUTONOMOUS_TRANSACTION;
901 BEGIN
902 UPDATE asg_user
903 SET user_setup_errors = p_mesg
904 WHERE user_name = p_user_name;
905 COMMIT;
906 EXCEPTION
907 WHEN OTHERS THEN
908 ROLLBACK;
909 END update_user_setup_errors;
910
911 --API for synching info between asg_user_pub_resps and asg_user tables
912 --after adding/dropping subscription
913 PROCEDURE update_user_resps(p_user_name IN VARCHAR2)
914 IS
915 l_resp_id NUMBER;
916 l_app_id NUMBER;
917 BEGIN
918
919 BEGIN
920 SELECT responsibility_id, app_id INTO l_resp_id, l_app_id
921 FROM asg_user_pub_resps
922 WHERE user_name = p_user_name AND
923 pub_name = 'SERVICEP';
924 EXCEPTION
925 WHEN OTHERS THEN
926 NULL;
927 END;
928
929 /* Check SERVICEL if no item exists for SERVICEP */
930 IF (l_resp_id IS NULL) or (l_app_id IS NULL) THEN
931 BEGIN
932 SELECT responsibility_id, app_id INTO l_resp_id, l_app_id
933 FROM asg_user_pub_resps
934 WHERE user_name = p_user_name AND
935 pub_name = 'SERVICEL';
936 EXCEPTION
937 WHEN OTHERS THEN
938 NULL;
939 END;
940 END IF;
941
942 IF (l_resp_id IS NOT NULL) AND (l_app_id IS NOT NULL) THEN
943 UPDATE asg_user
944 SET responsibility_id = l_resp_id, app_id = l_app_id
945 WHERE user_name = p_user_name AND
946 responsibility_id <> l_resp_id AND
947 app_id <> l_app_id;
948 COMMIT;
949 END IF;
950 END update_user_resps;
951
952 --API for updating SYNCH_ERRORS column in asg_user table
953 --not used currently
954 PROCEDURE update_synch_errors(p_user_name IN VARCHAR2,p_mesg IN VARCHAR2)
955 IS
956 BEGIN
957 UPDATE asg_user
958 SET synch_errors = p_mesg,
959 last_wireless_contact_date = sysdate
960 WHERE user_name = p_user_name;
961 END update_synch_errors;
962
963 --API for autonomous update of hwm_tranid and synch_errors.
964 --if p_tranid is null the hwm_tranid col is not changed.
965 PROCEDURE set_synch_errmsg(p_user_name IN VARCHAR2, p_tranid IN NUMBER,
966 p_device_type IN VARCHAR2, p_mesg IN VARCHAR2)
967 IS
968 l_curid NUMBER;
969 l_dml VARCHAR2(2000);
970 l_ret NUMBER;
971 l_pub_name varchar2(30);
972 CURSOR get_pub(l_device_type varchar2) IS
973 SELECT NAME FROM asg_pub WHERE device_type=l_device_type;
974
975 PRAGMA AUTONOMOUS_TRANSACTION;
976 BEGIN
977 UPDATE asg_user
978 SET synch_errors = p_mesg,
979 cookie = nvl(p_device_type,cookie),
980 hwm_tranid = nvl(p_tranid,hwm_tranid),
981 last_wireless_contact_date = sysdate
982 WHERE user_name = p_user_name;
983 -- update asg_user_pub_resp.synch_date
984 OPEN get_pub(p_device_type);
985 LOOP
986 FETCH get_pub INTO l_pub_name;
987 EXIT WHEN get_pub%NOTFOUND;
988 log('Setting synch time for pub_name: '||l_pub_name,'asg_helper',g_stmt_level);
989 UPDATE asg_user_pub_resps
990 SET synch_date = sysdate
991 WHERE user_name = p_user_name
992 AND pub_name = l_pub_name;
993 END LOOP;
994 CLOSE get_pub;
995 COMMIT;
996 EXCEPTION
997 WHEN OTHERS THEN
998 ROLLBACK;
999 END set_synch_errmsg;
1000
1001 -- Procedure to set the last synch device type
1002 PROCEDURE set_last_synch_device_type
1003 IS
1004 CURSOR c_users IS
1005 SELECT user_name
1006 FROM asg_user
1007 WHERE cookie is null;
1008 CURSOR c_user_devices (p_user_name VARCHAR2) IS
1009 SELECT distinct ap.device_type
1010 FROM asg_user_pub_resps aupr, asg_pub ap
1011 WHERE aupr.pub_name = ap.name and
1012 aupr.user_name = p_user_name and
1013 ap.device_type is not null;
1014 l_current_user VARCHAR2(30);
1015 l_counter NUMBER;
1016 l_user_synched NUMBER;
1017 l_device_type VARCHAR2(30);
1018 l_found_device_type BOOLEAN;
1019 l_device_type_stored NUMBER;
1020 l_sql_string VARCHAR2(512);
1021 BEGIN
1022
1023 -- Update users whose last synch device type is not yet set.
1024 -- Find the device type from asg_user_pub_resps and asg_pub
1025 -- For users with multiple devices query Oracle Lite tables
1026 -- If device type for last synch is not knowable, do not update asg_user
1027 FOR cu in c_users LOOP
1028 l_current_user := cu.user_name;
1029 l_counter := 0;
1030 l_found_device_type := FALSE;
1031 OPEN c_user_devices (l_current_user);
1032 LOOP
1033 FETCH c_user_devices INTO l_device_type;
1034 EXIT WHEN c_user_devices%NOTFOUND;
1035 l_counter := l_counter +1 ;
1036 END LOOP;
1037 CLOSE c_user_devices;
1038
1039 -- If only one device type is found for this user
1040 -- Update the device type to the one we found
1041 IF (l_counter = 1) THEN
1042 UPDATE asg_user
1043 SET cookie = l_device_type
1044 WHERE user_name = l_current_user;
1045 l_found_device_type := TRUE;
1046 END IF;
1047
1048 -- ok, multiple devices assigned to this user
1049 IF l_found_device_type = FALSE THEN
1050 l_sql_string := 'SELECT count(ws.os_name) ' ||
1051 'FROM ' ||
1052 asg_base.G_OLITE_SCHEMA || '.wtg_sites ws, ' ||
1053 asg_base.G_OLITE_SCHEMA || '.users usr, ' ||
1054 asg_base.G_OLITE_SCHEMA || '.c$all_clients cac ' ||
1055 'where cac.synctime_start is not null ' ||
1056 'and cac.clientid = usr.name and usr.id = ws.usr_id ' ||
1057 'and abs(cac.synctime_start-ws.last_sync) <= 1/24 ' ||
1058 'and ws.os_name is not null ' ||
1059 'and usr.name = :1';
1060
1061 EXECUTE IMMEDIATE l_sql_string
1062 INTO l_device_type_stored
1063 USING l_current_user;
1064
1065 -- Users who synched from web-to-go (laptop) are remembered in wtg_sites
1066 -- So, if a record exists in this table, it means user synched from wtg
1067 IF l_device_type_stored > 0 THEN
1068 UPDATE asg_user
1069 SET cookie = 'LAPTOP'
1070 WHERE user_name = l_current_user;
1071 ELSE
1072 -- No device type found from last synch.
1073 -- Find out if the user ever synched
1074 l_sql_string := 'SELECT COUNT(*) ' ||
1075 'FROM ' ||
1076 asg_base.G_OLITE_SCHEMA || '.c$all_clients ' ||
1077 'WHERE synctime_start is not null and ' ||
1078 'clientid = :1';
1079 EXECUTE IMMEDIATE l_sql_string
1080 INTO l_user_synched
1081 USING l_current_user;
1082 IF l_user_synched = 1 THEN
1083 UPDATE asg_user
1084 SET cookie = 'PALM'
1085 WHERE user_name = l_current_user;
1086 END IF;
1087 END IF;
1088 END IF;
1089 END LOOP;
1090 EXCEPTION
1091 WHEN OTHERS THEN
1092 ROLLBACK;
1093 END set_last_synch_device_type;
1094
1095
1096 --Routine that sets up a user for complete refresh for a publication
1097 PROCEDURE set_first_synch(p_clientid in varchar2,p_pub in varchar2)
1098 is
1099 CURSOR c_custom_pub(l_pub_name VARCHAR2) IS
1100 SELECT nvl(custom, 'N')
1101 FROM asg_pub
1102 WHERE name = l_pub_name;
1103 l_custom_pub varchar2(2);
1104 PRAGMA autonomous_transaction;
1105 BEGIN
1106 IF(p_pub = 'ALL')
1107 THEN
1108 RETURN;
1109 END IF;
1110
1111 OPEN c_custom_pub(p_pub);
1112 FETCH c_custom_pub INTO l_custom_pub;
1113 CLOSE c_custom_pub;
1114 if(l_custom_pub = 'Y')
1115 THEN
1116 RETURN;
1117 END IF;
1118
1119 -- neither ALL nor custom publication..
1120 INSERT INTO
1121 asg_purge_sdq(user_name,pub_name,transaction_id,CREATION_DATE,CREATED_BY,
1122 LAST_UPDATE_DATE,LAST_UPDATED_BY)
1123 values(p_clientid,p_pub,null,sysdate,1,sysdate,1);
1124 commit;
1125
1126 EXCEPTION
1127 WHEN DUP_VAL_ON_INDEX then
1128 UPDATE asg_purge_sdq
1129 SET transaction_id = null,last_update_date = SYSDATE
1130 WHERE user_name = p_clientid AND pub_name = p_pub;
1131 commit;
1132 END set_first_synch;
1133
1134
1135 PROCEDURE set_sso_profile(p_userId in VARCHAR2)
1136 IS
1137 l_ret boolean;
1138 BEGIN
1139 l_ret:=fnd_profile.save('APPS_SSO_LOCAL_LOGIN','BOTH','USER',p_userId);
1140 commit;
1141 EXCEPTION
1142 WHEN OTHERS then
1143 rollback;
1144 END set_sso_profile;
1145
1146 -- Routine for creating public synonyms
1147 PROCEDURE create_olite_synonyms
1148 IS
1149 CURSOR c_olite_objects IS
1150 SELECT object_name
1151 FROM dba_objects
1152 WHERE owner = 'MOBILEADMIN' AND
1153 object_type in ('TABLE', 'VIEW') AND
1154 object_name not like 'C__$%'
1155 UNION
1156 SELECT object_name
1157 FROM dba_objects
1158 WHERE owner = 'MOBILEADMIN' AND
1159 object_type in ('SEQUENCE') AND
1160 object_name not like 'M$%';
1161 l_sql_string VARCHAR2(512);
1162 BEGIN
1163 FOR cob IN c_olite_objects LOOP
1164 BEGIN
1165 l_sql_string := 'CREATE SYNONYM ' || cob.object_name ||
1166 ' FOR MOBILEADMIN.' || cob.object_name;
1167 EXECUTE IMMEDIATE l_sql_string;
1168 EXCEPTION
1169 WHEN OTHERS THEN
1170 NULL; -- Ignore
1171 END;
1172 END LOOP;
1173
1174 END create_olite_synonyms;
1175
1176 -- Routine for dropping public synonyms
1177 PROCEDURE drop_olite_synonyms
1178 IS
1179 CURSOR c_olite_objects IS
1180 SELECT object_name
1181 FROM dba_objects
1182 WHERE owner = 'MOBILEADMIN' AND
1183 object_type in ('TABLE', 'VIEW') AND
1184 object_name not like 'C__$%'
1185 UNION
1186 SELECT object_name
1187 FROM dba_objects
1188 WHERE owner = 'MOBILEADMIN' AND
1189 object_type in ('SEQUENCE') AND
1190 object_name not like 'M$%';
1191 l_sql_string VARCHAR2(512);
1192 BEGIN
1193 FOR cob IN c_olite_objects LOOP
1194 BEGIN
1195 l_sql_string := 'DROP SYNONYM ' || cob.object_name;
1196 EXECUTE IMMEDIATE l_sql_string;
1197 EXCEPTION
1198 WHEN OTHERS THEN
1199 NULL; -- Ignore
1200 END;
1201 END LOOP;
1202
1203 END drop_olite_synonyms;
1204
1205 PROCEDURE recreate_synonyms(p_dt IN DATE)
1206 is
1207 BEGIN
1208 null;
1209 END recreate_synonyms;
1210
1211 --This function taken a input String and a 8 byte key and encrypts the input
1212 --If the key is less than 8 bytes, then the error
1213 --"ORA-28234: key length too short" is thrown
1214 function encrypt(p_input_string varchar2,p_key varchar2)
1215 return varchar2
1216 is
1217 l_encrypted_string varchar2(1024);
1218 l_in_str varchar2(1024);
1219 l_pad_len NUMBER;
1220 begin
1221 l_in_str := p_input_string;
1222 -- If the string length is not a mutliple of 8
1223 if(mod(lengthb(p_input_string),8) <> 0) then
1224 -- Find the num of bytes to add to make it a multiple of 8
1225 l_pad_len := 8 - mod(lengthb(p_input_string),8);
1226 l_in_str := l_in_str || rpad(' ', l_pad_len, ' ');
1227 end if;
1228
1229 DBMS_OBFUSCATION_toolkit.DES3Encrypt(input_string => l_in_str,
1230 key_string => p_key,encrypted_string => l_encrypted_string,
1231 which => 0);
1232 return l_encrypted_string;
1233 end encrypt;
1234
1235
1236 --This procedure encrypts p_input_string using the key p_key.
1237 --It then updates the asg_config param p_param_name
1238 procedure encrypt_and_copy(p_param_name varchar2,p_input_string varchar2,
1239 p_key varchar2,p_param_desc varchar2)
1240 is
1241 begin
1242 fnd_vault.put(g_svc, p_param_name, p_input_string);
1243 end encrypt_and_copy;
1244
1245 procedure encrypt_old(p_param_name varchar2,p_input_string varchar2,
1246 p_key varchar2,p_param_desc varchar2)
1247 is
1248 l_encrypted_string varchar2(1024);
1249 l_in_str varchar2(1024);
1250 l_pad_len NUMBER;
1251 begin
1252 l_in_str := p_input_string;
1253 -- If the string length is not a mutliple of 8
1254 if(mod(lengthb(p_input_string),8) <> 0) then
1255 -- Find the num of bytes to add to make it a multiple of 8
1256 l_pad_len := 8 - mod(lengthb(p_input_string),8);
1257 l_in_str := l_in_str || rpad(' ', l_pad_len, ' ');
1258 end if;
1259
1260 DBMS_OBFUSCATION_toolkit.DES3Encrypt(input_string => l_in_str,
1261 key_string => p_key,encrypted_string => l_encrypted_string,
1262 which => 0);
1263 set_config_param(p_param_name,l_encrypted_string,p_param_desc);
1264 end encrypt_old;
1265
1266 --This function taken a input String and a 8 byte key and decrypts the input
1267 --If the key is less than 8 bytes, then the error
1268 --"ORA-28234: key length too short" is thrown
1269 function decrypt(p_input_string varchar2,p_key varchar2)
1270 return varchar2
1271 is
1272 l_decrypted_string varchar2(1024);
1273 begin
1274 DBMS_OBFUSCATION_toolkit.DES3Decrypt(input_string => p_input_string,
1275 key_string => p_key,decrypted_string => l_decrypted_string,
1276 which => 0);
1277 return l_decrypted_string;
1278 end decrypt;
1279
1280 --This function reads the value of the asg_config param p_param_name
1281 --The value is decrypted using p_key and the decrypted string is returned.
1282 function decrypt_and_return(p_param_name varchar2,p_key varchar2)
1283 return varchar2
1284 is
1285 l_decrypted_string varchar2(1024);
1286 begin
1287 l_decrypted_string := fnd_vault.get(g_svc, p_param_name);
1288 return l_decrypted_string;
1289 end decrypt_and_return;
1290
1291 function decrypt_old(p_param_name varchar2,p_key varchar2)
1292 return varchar2
1293 is
1294 l_decrypted_string varchar2(1024):= null;
1295 l_dec_str varchar2(1024);
1296 begin
1297 select value into l_dec_str from asg_config
1298 where name = p_param_name;
1299
1300 DBMS_OBFUSCATION_toolkit.DES3Decrypt(input_string => l_dec_str,
1301 key_string => p_key,decrypted_string => l_decrypted_string,
1302 which => 0);
1303 return l_decrypted_string;
1304 exception
1305 when others then
1306 return l_decrypted_string;
1307 end decrypt_old;
1308
1309 function get_key
1310 return varchar2
1311 is
1312 l_key varchar2(128);
1313 l_schema_name varchar2(128);
1314 l_ASG_APP_ID number := 689;
1315 begin
1316 select oracle_username into l_schema_name
1317 from fnd_oracle_userid
1318 where oracle_id = l_ASG_APP_ID;
1319
1320 l_key := l_schema_name;
1321 l_key := rpad(l_key, 16, l_schema_name);
1322 return l_key;
1323
1324 end get_key;
1325
1326
1327 --Sets a given profile value to null at all levels.
1328 procedure set_profile_to_null(p_profile_name varchar2)
1329 is
1330 cursor c_get_profile_option_id(l_profile_name varchar2)
1331 is
1332 select profile_option_id, application_id
1333 from fnd_profile_options
1334 where ( END_dATE_ACTIVE IS NULL OR END_dATE_ACTIVE > SYSDATE )
1335 AND profile_option_name = l_profile_name;
1336
1337 cursor c_profile_reset(l_app_id number, l_profile_id number,l_level_id number)
1338 is
1339 select profile_option_value,level_value,level_id
1340 from fnd_profile_option_values
1341 where application_id = l_app_id and
1342 profile_option_id = l_profile_id and
1343 level_id = l_level_id ;
1344
1345 l_profile_var c_profile_reset%rowtype;
1346 l_ret boolean;
1347 l_prof_id number;
1348 l_app_id number;
1349 begin
1350 --Get the profile option ID for the given profile option
1351 open c_get_profile_option_id(p_profile_name);
1352 fetch c_get_profile_option_id into l_prof_id, l_app_id;
1353 close c_get_profile_option_id;
1354
1355 if(l_prof_id is not null)
1356 then
1357 --set to null at site level : 10001
1358 open c_profile_reset(l_app_id, l_prof_id,10001);
1359 loop
1360 fetch c_profile_reset into l_profile_var;
1361 exit when c_profile_reset%NOTFOUND;
1362 l_ret := fnd_profile.save(p_profile_name,null,'SITE');
1363 end loop;
1364 close c_profile_reset;
1365
1366 --set to null at application level : 10002
1367 open c_profile_reset(l_app_id, l_prof_id,10002);
1368 loop
1369 fetch c_profile_reset into l_profile_var;
1370 exit when c_profile_reset%NOTFOUND;
1371 l_ret :=fnd_profile.save(p_profile_name,null,'APPL',
1372 l_profile_var.level_value);
1373 end loop;
1374 close c_profile_reset;
1375
1376 --set to null at resp level : 10003
1377 open c_profile_reset(l_app_id,l_prof_id,10003);
1378 loop
1379 fetch c_profile_reset into l_profile_var;
1380 exit when c_profile_reset%NOTFOUND;
1381 l_ret := fnd_profile.save(p_profile_name,null,'RESP',
1382 l_profile_var.level_value,l_app_id);
1383 end loop;
1384 close c_profile_reset;
1385
1386 --set to null at user level : 10004
1387 open c_profile_reset(l_app_id, l_prof_id,10004);
1388 loop
1389 fetch c_profile_reset into l_profile_var;
1390 exit when c_profile_reset%NOTFOUND;
1391 l_ret := fnd_profile.save(p_profile_name,null,'USER',
1392 l_profile_var.level_value);
1393 end loop;
1394 close c_profile_reset;
1395 end if;
1396 commit;
1397 end set_profile_to_null ;
1398
1399 -- Just execute the grant string and dont raise any exceptions
1400 PROCEDURE grant_db_privilege (p_grant_string IN VARCHAR2)
1401 IS
1402 BEGIN
1403 EXECUTE IMMEDIATE p_grant_string;
1404 EXCEPTION
1405 WHEN OTHERS THEN
1406 NULL;
1407 END grant_db_privilege;
1408
1409 -- Grants necessary select, insert privileges to mobile table/views to
1410 -- Olite db schema
1411 PROCEDURE enable_olite_privs
1412 IS
1413 CURSOR c_inq_outq_objects
1414 IS
1415 SELECT base_object_name, inq_name
1416 FROM asg_pub_item
1417 WHERE enabled = 'Y';
1418 l_olite_schema VARCHAR2(30);
1419 l_sql_string VARCHAR2(4096);
1420 BEGIN
1421 l_olite_schema := asg_base.G_OLITE_SCHEMA;
1422
1423 l_sql_string := 'GRANT SELECT ON ASG_SYSTEM_DIRTY_QUEUE TO '||
1424 l_olite_schema;
1425 grant_db_privilege(l_sql_string);
1426
1427 l_sql_string := 'GRANT SELECT ON ASG_DELETE_QUEUE TO '||
1428 l_olite_schema;
1429 grant_db_privilege(l_sql_string);
1430
1431 l_sql_string := 'GRANT SELECT ON ASG_SEQUENCE_PARTITIONS_V TO '||
1432 l_olite_schema;
1433 grant_db_privilege(l_sql_string);
1434
1435 l_sql_string := 'GRANT SELECT ON ASG_TEMP_LOB TO '||
1436 l_olite_schema;
1437 grant_db_privilege(l_sql_string);
1438
1439
1440 /* Loop through all the pub-items and grant appropriate privileges */
1441 FOR c_ioq IN c_inq_outq_objects LOOP
1442 l_sql_string := 'GRANT SELECT ON '|| c_ioq.base_object_name ||
1443 ' TO ' || l_olite_schema;
1444 grant_db_privilege(l_sql_string);
1445
1446 l_sql_string := 'GRANT SELECT ON ' || c_ioq.inq_name ||
1447 ' TO ' || l_olite_schema;
1448 grant_db_privilege(l_sql_string);
1449 l_sql_string := 'GRANT INSERT ON ' || c_ioq.inq_name ||
1450 ' TO ' || l_olite_schema;
1451 grant_db_privilege(l_sql_string);
1452 l_sql_string := 'GRANT UPDATE ON ' || c_ioq.inq_name ||
1453 ' TO ' || l_olite_schema;
1454 grant_db_privilege(l_sql_string);
1455 l_sql_string := 'GRANT DELETE ON ' || c_ioq.inq_name ||
1456 ' TO ' || l_olite_schema;
1457 grant_db_privilege(l_sql_string);
1458 END LOOP;
1459
1460 END enable_olite_privs;
1461
1462
1463
1464 END asg_helper;
[Home] [Help]