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