1 PACKAGE BODY asg_base AS
2 /*$Header: asgbaseb.pls 120.5 2006/05/09 13:52:01 rsripada noship $*/
3
4 -- DESCRIPTION
5 -- Contains functions to retrieve information during a synch session.
6 --
7 --
8 -- HISTORY
9 -- 02-sep-2005 rsripada Multiple Responsibility Support
10 -- 23-dec-2004 rsripada Fix Bug 4086602
11 -- 12-aug-2004 ssabesan Added device switch changes ( bug 3824280 )
12 -- 02-jun-2004 rsripada Add function to download attachments
13 -- 12-may-2004 ssabesan Fix GSCC warning - Standard File.Sql.6
14 -- 17-mar-2004 rsripada Modified reset_all_globals
15 -- 27-may-2003 ssabesan Merged the branch line with main line
16 -- 09-may-2003 rsripada Added get_last_synch_date(p_user_id) API
17 -- 31-mar-2003 rsripada Modify init method to pass last_synch_date
18 -- 28-mar-2003 rsripada Get last synch date from asg_user
19 -- 11-feb-2003 rsripada Added get_upload_tranid, set_upload_tranid
20 -- 09-feb-2003 rsripada Modify find_pub_item to always return a value
21 -- 06-jan-2003 ssabesan checking whether logging is enabled before invoking
22 -- the logging procedure.
23 -- 12-sep-2002 vekrishn Fix for bug 2565884
24 -- 10-jul-2002 vekrishn Commented out logging in get_current_tran_id and
25 -- get_last_synch_date
26 -- 28-jun-2002 vekrishn Added logging for GET_CURRENT_TRANID and
27 -- get_last_synch_date
28 -- 25-jun-2002 rsripada Remove dependencies on Olite schema
29 -- 29-may-2002 rsripada Added logging support
30 -- 15-may-2002 rsripada Added procedures to set pub items
31 -- 25-apr-2002 rsripada Added functions for debug logging
32 -- 18-apr-2002 rsripada Added functions for online queries etc.
33 -- 29-mar-2002 rsripada Created
34
35
36 /* Global Variables
37 glsd - get_last_synch_date
38 ifs - is_first_synch
39 gdt - get_dml_type
40 */
41
42 g_last_pub_item_name_glsd VARCHAR2(30);
43 g_pub_item_last_synch_date DATE;
44 g_last_pub_item_name_ifs VARCHAR2(30);
45 g_comp_ref_ifs CHAR(1);
46 g_last_pub_item_name_gdt VARCHAR2(30);
47 g_dml_type_gdt VARCHAR2(1);
48
49 g_stmt_level NUMBER := FND_LOG.LEVEL_STATEMENT;
50 g_err_level NUMBER := FND_LOG.LEVEL_ERROR;
51
52
53 g_max_allowed_size NUMBER := NULL;
54 g_current_size NUMBER := 0;
55 g_atmt_size_exceeded BOOLEAN := FALSE;
56
57 g_olite_version VARCHAR2(30) := NULL;
58
59 FUNCTION find_pub_item(p_pub_item_name IN VARCHAR2)
60 return PLS_INTEGER IS
61 l_item_index PLS_INTEGER := -1;
62 BEGIN
63 FOR curr_index in 1..g_pub_item_tbl.count LOOP
64 IF(g_pub_item_tbl(curr_index).name = p_pub_item_name) THEN
65 return curr_index;
66 END IF;
67 END LOOP;
68 return l_item_index;
69 END find_pub_item;
70
71
72 /* Useful for debugging */
73 /* Logs all session information */
74 PROCEDURE print_all_globals
75 IS
76 TYPE vc256_tbl_type is table of varchar2(256) index by binary_integer;
77 sql_string_tbl vc256_tbl_type;
78 BEGIN
79 sql_string_tbl(1) := '**** Session Variables for this '
80 || 'Synch '||
81 ' ****';
82 sql_string_tbl(2) := 'Current User is : ' || get_user_name;
83 sql_string_tbl(3) := ' Resource ID : ' || get_resource_id;
84 sql_string_tbl(4) := ' User ID : ' || get_user_id;
85 sql_string_tbl(5) := ' Language : ' || get_language;
86 sql_string_tbl(6) := ' Current Tran id : ' || get_current_tranid;
87 sql_string_tbl(7) := ' Last Tran id : ' || get_last_tranid;
88 sql_string_tbl(8) := ' Last Synch Date : ' ||
89 to_char(get_last_synch_date, 'DD-MON-YYYY HH24:MI:SS');
90
91 FOR curr_index in 1..g_pub_item_tbl.count LOOP
92 IF (curr_index = 1) THEN
93 sql_string_tbl(9) := 'Publication Items that will be downloaded:';
94 END IF;
95 sql_string_tbl(9+curr_index) :=
96 ' Publication Item: ' || g_pub_item_tbl(curr_index).name ||
97 ' Complete Refresh: ' || g_pub_item_tbl(curr_index).comp_ref;
98 END LOOP;
99
100 BEGIN
101 FOR curr_index in 1..sql_string_tbl.count LOOP
102 IF(asg_helper.check_is_log_enabled(g_stmt_level))
103 THEN
104 asg_helper.log(sql_string_tbl(curr_index),
105 'asg_base',g_stmt_level);
106 END IF;
107 END LOOP;
108 EXCEPTION
109 WHEN OTHERS THEN
110 NULL;
111 END;
112
113 END print_all_globals;
114
115 /* get user name for the specified resource_id */
116 FUNCTION get_user_name(p_resource_id IN NUMBER)
117 return VARCHAR2 IS
118 CURSOR C_USER_NAME(p_resource_id NUMBER) IS
119 SELECT user_name
120 FROM asg_user
121 WHERE resource_id = p_resource_id;
122 l_user_name asg_user.user_name%type;
123 BEGIN
124
125 OPEN C_USER_NAME(p_resource_id);
126 FETCH C_USER_NAME into l_user_name;
127 CLOSE C_USER_NAME;
128 return l_user_name;
129
130 END get_user_name;
131
132 /* get resource_id for user_name */
133 FUNCTION get_resource_id(p_user_name VARCHAR2)
134 return NUMBER IS
135 CURSOR C_RESOURCE(p_user_name varchar2) IS
136 SELECT resource_id
137 FROM asg_user
138 WHERE user_name = p_user_name;
139 l_resource_id number;
140 BEGIN
141
142 OPEN C_RESOURCE(p_user_name);
143 FETCH C_RESOURCE into l_resource_id;
144 CLOSE C_RESOURCE;
145 return l_resource_id;
146
147 END get_resource_id;
148
149 /* get user_id for user_name */
150 FUNCTION get_user_id(p_user_name VARCHAR2)
151 return NUMBER IS
152 CURSOR C_USER(p_user_name varchar2) IS
153 SELECT user_id
154 FROM asg_user
155 WHERE user_name = p_user_name;
156 l_user_id number;
157 BEGIN
158
159 OPEN C_USER(p_user_name);
160 FETCH C_USER into l_user_id;
161 CLOSE C_USER;
162 return l_user_id;
163
164 END get_user_id;
165
166 /* get mobile responsibility for user_name */
167 FUNCTION get_resp_id(p_user_name VARCHAR2)
168 return NUMBER IS
169 CURSOR C_RESP(p_user_name varchar2) IS
170 SELECT responsibility_id
171 FROM asg_user
172 WHERE user_name = p_user_name;
173 l_resp_id number;
174 BEGIN
175
176 OPEN C_RESP(p_user_name);
177 FETCH C_RESP into l_resp_id;
178 CLOSE C_RESP;
179 return l_resp_id;
180
181 END get_resp_id;
182
183 /* get language for user */
184 FUNCTION get_language(p_user_name VARCHAR2)
185 return VARCHAR2 IS
186 CURSOR C_LANGUAGE(p_user_name varchar2) IS
187 SELECT language
188 FROM asg_user
189 WHERE user_name = p_user_name;
190 l_language VARCHAR2(4);
191 BEGIN
192
193 OPEN C_LANGUAGE(p_user_name);
194 FETCH C_LANGUAGE into l_language;
195 CLOSE C_LANGUAGE;
196 return l_language;
197
198 END get_language;
199
200 /* get application_id for user */
201 FUNCTION get_application_id(p_user_name VARCHAR2)
202 return NUMBER IS
203 CURSOR C_APP(p_user_name varchar2) IS
204 SELECT app_id
205 FROM asg_user
206 WHERE user_name = p_user_name;
207 l_app_id number;
208 BEGIN
209 OPEN C_APP(p_user_name);
210 FETCH C_APP INTO l_app_id;
211 CLOSE C_APP;
212 return l_app_id;
213 END get_application_id;
214
215 /* get last successful synch date */
216 /* Internal function for use only by this package */
217 FUNCTION get_last_synch_date_internal(p_user_name VARCHAR2)
218 return DATE IS
219 l_last_synch_date DATE;
220 BEGIN
221
222 SELECT last_synch_date_end into l_last_synch_date
223 FROM asg_user
224 WHERE user_name = p_user_name;
225
226 return l_last_synch_date;
227
228 END get_last_synch_date_internal;
229
230 /* get resource_id */
231 FUNCTION get_resource_id
232 return NUMBER IS
233 BEGIN
234 return g_resource_id;
235 END get_resource_id;
236
237 /* get user_id */
238 FUNCTION get_user_id
239 return NUMBER IS
240 BEGIN
241 return g_user_id;
242 END get_user_id;
243
244 /* get responsibility_id */
245 FUNCTION get_resp_id
246 return NUMBER IS
247 BEGIN
248 return g_resp_id;
249 END get_resp_id;
250
251 /* get language */
252 FUNCTION get_language
253 return VARCHAR2 IS
254 BEGIN
255 return g_language;
256 END get_language;
257
258 /* get application_id */
259 FUNCTION get_application_id
260 return NUMBER IS
261 BEGIN
262 return g_application_id;
263 END get_application_id;
264
265 /* get user name */
266 FUNCTION get_user_name
267 return VARCHAR2 IS
268 BEGIN
269 return g_user_name;
270 END get_user_name;
271
272 /* get last successful synch date */
273 FUNCTION get_last_synch_date
274 return DATE IS
275 BEGIN
276 return g_last_synch_date;
277 END get_last_synch_date;
278
279 /* Checks if the passed in publication item is going to be completely refreshed */
280 /* ands returns G_OLD_DATE. Otherwise, gets last successful synch date */
281 FUNCTION get_last_synch_date(p_pub_item_name IN VARCHAR2)
282 return DATE IS
283 BEGIN
284
285 IF (p_pub_item_name = g_last_pub_item_name_glsd) THEN
286 RETURN g_pub_item_last_synch_date;
287 ELSE
288 g_last_pub_item_name_glsd := p_pub_item_name;
289 IF(is_first_synch(p_pub_item_name) = G_YES) THEN
290 g_pub_item_last_synch_date := G_OLD_DATE;
291 ELSE
292 g_pub_item_last_synch_date := g_last_synch_date;
293 END IF;
294 RETURN g_pub_item_last_synch_date;
295 END IF;
296
297 END get_last_synch_date;
298
299 /* Get current download tran id */
300 FUNCTION get_current_tranid
301 return NUMBER IS
302 BEGIN
303 return g_download_tranid;
304 END get_current_tranid;
305
306 /* Get current download tran id */
307 FUNCTION get_current_tranid (p_pub_item_name IN VARCHAR2)
308 return NUMBER IS
309 BEGIN
310 return g_download_tranid;
311 END get_current_tranid;
312
313 /* Get last download tran id */
314 FUNCTION get_last_tranid
315 return NUMBER IS
316 BEGIN
317 return g_last_tranid;
318 END get_last_tranid;
319
320 /* Sets the upload tranid */
321 FUNCTION get_upload_tranid
322 return NUMBER IS
323 BEGIN
324 return g_upload_tranid;
325 END get_upload_tranid;
326
327
328 /* get dml type based on creation_date, update_date and */
329 /* last_synch_date. Will return either G_INS or G_UPD */
330 FUNCTION get_dml_type(p_creation_date IN DATE)
331 return VARCHAR2 IS
332 BEGIN
333 -- g_last_synch_date IS NULL means no synch completed successfully
334 -- or the user has never synched.
335 IF ((g_last_synch_date IS NULL) OR
336 (p_creation_date > g_last_synch_date)) THEN
337 return G_INS;
338 ELSE
339 return G_UPD;
340 END IF;
341 END get_dml_type;
342
343 /* get dml type based on update date and publication name */
344 /* For publications that will be completely refreshed the */
345 /* DML type will be insert (G_INS) */
346 FUNCTION get_dml_type(p_pub_item_name IN VARCHAR2,
347 p_creation_date IN DATE)
348 return VARCHAR2 IS
349 BEGIN
350 -- g_last_synch_date IS NULL means no synch completed successfully
351 -- or the user has never synched.
352 -- Skip check for complete_refresh if p_creation_date is
353 -- greater than last_synch_date
354
355 IF (p_pub_item_name = g_last_pub_item_name_gdt) THEN
356 RETURN g_dml_type_gdt;
357 ELSE
358 g_last_pub_item_name_gdt := p_pub_item_name;
359 IF ((g_last_synch_date IS NULL) OR
360 (p_creation_date > g_last_synch_date) OR
361 (is_first_synch(p_pub_item_name) = G_YES)) THEN
362 g_dml_type_gdt := G_INS;
363 ELSE
364 g_dml_type_gdt := G_UPD;
365 END IF;
366 RETURN g_dml_type_gdt;
367 END IF;
368 END get_dml_type;
369
370 /* returns 'Y' if the publication item will be completely */
371 /* refreshed */
372 FUNCTION is_first_synch(p_pub_item_name IN VARCHAR2)
373 return VARCHAR2 IS
374 l_pub_index PLS_INTEGER := 0;
375 BEGIN
376
377 IF (p_pub_item_name = g_last_pub_item_name_ifs) THEN
378 RETURN g_comp_ref_ifs;
379 ELSE
380 g_last_pub_item_name_ifs := p_pub_item_name;
381 l_pub_index := find_pub_item(p_pub_item_name);
382 -- If publication item is not found return complete refresh
383 IF (l_pub_index <> -1) THEN
384 g_comp_ref_ifs := g_pub_item_tbl(l_pub_index).comp_ref;
385 ELSE
386 g_comp_ref_ifs := G_YES;
387 END IF;
388 RETURN g_comp_ref_ifs;
389 END IF;
390
391 END is_first_synch;
392
393 /* Initializes the global variables during synch session */
394 PROCEDURE init(p_user_name IN VARCHAR2, p_last_tranid IN NUMBER,
395 p_curr_tranid IN NUMBER,
396 p_last_synch_date IN DATE,
397 p_pub_items pub_item_tbl_type)
398 IS
399 BEGIN
400 g_user_name := p_user_name;
401 g_last_tranid := p_last_tranid;
402 g_download_tranid := p_curr_tranid;
403 g_language := get_language(p_user_name);
404 g_resource_id := get_resource_id(p_user_name);
405 g_user_id := get_user_id(p_user_name);
406 g_resp_id := get_resp_id(p_user_name);
407 g_application_id := get_application_id(p_user_name);
408 if(p_last_synch_date IS NOT NULL) THEN
409 g_last_synch_date := p_last_synch_date;
410 ELSE
411 g_last_synch_date := get_last_synch_date_internal(p_user_name);
412 END IF;
413
414 IF (p_pub_items IS NOT NULL) AND (p_pub_items.count > 0) THEN
415 g_pub_item_tbl := g_empty_pub_item_tbl;
416 FOR curr_index in 1..p_pub_items.count LOOP
417 g_pub_item_tbl(curr_index).name := p_pub_items(curr_index).name;
418 g_pub_item_tbl(curr_index).comp_ref := p_pub_items(curr_index).comp_ref;
419 END LOOP;
420 END IF;
421
422 END init;
423
424
425 /* Initializes the global pubitem table with specified items */
426 PROCEDURE set_pub_items(p_pub_items pub_item_tbl_type)
427 IS
428 BEGIN
429 IF (p_pub_items IS NOT NULL) THEN
430 g_pub_item_tbl := g_empty_pub_item_tbl;
431 FOR curr_index in 1..p_pub_items.count LOOP
432 g_pub_item_tbl(curr_index).name :=p_pub_items(curr_index).name;
433 g_pub_item_tbl(curr_index).comp_ref :=p_pub_items(curr_index).comp_ref;
434 END LOOP;
435 ELSE
436 -- Set the pub item to empty
437 g_pub_item_tbl := g_empty_pub_item_tbl;
438 END IF;
439
440 END set_pub_items;
441
442 /* Sets the specified pub item for complete refresh */
443 PROCEDURE set_complete_refresh(p_pub_item_name VARCHAR2)
444 IS
445 l_item_index PLS_INTEGER;
446 BEGIN
447 -- Return if the pub item table is not setup
448 IF (g_pub_item_tbl IS NULL) OR
449 (g_pub_item_tbl.count = 0) THEN
450 return;
451 END IF;
452 l_item_index := find_pub_item(p_pub_item_name);
453
454 -- If the item was found
455 IF (l_item_index <> -1) THEN
456 g_pub_item_tbl(l_item_index).comp_ref := G_YES;
457 END IF;
458
459 END set_complete_refresh;
460
461 /* Sets the upload tranid */
462 PROCEDURE set_upload_tranid(p_upload_tranid IN NUMBER)
463 IS
464 BEGIN
465 g_upload_tranid := p_upload_tranid;
466 END set_upload_tranid;
467
468 /* Initializes the global variables with specified values.
469 Use for debug only */
470 PROCEDURE init_debug(p_user_name IN VARCHAR2, p_language IN VARCHAR2,
471 p_resource_id IN NUMBER, p_user_id IN NUMBER,
472 p_resp_id IN NUMBER,
473 p_application_id IN NUMBER, p_last_synch_date IN DATE)
474 IS
475 BEGIN
476
477 g_user_name := p_user_name;
478 g_language := p_language;
479 g_resource_id := p_resource_id;
480 g_user_id := p_user_id;
481 g_resp_id := p_resp_id;
482 g_application_id := p_application_id;
483 g_last_synch_date := p_last_synch_date;
484
485 END init_debug;
486
487 /* Resets all global variables to null */
488 PROCEDURE reset_all_globals
489 IS
490 BEGIN
491 g_user_name := null;
492 g_language := null;
493 g_resource_id := null;
494 g_user_id := null;
495 g_resp_id := null;
496 g_application_id := null;
497 g_last_synch_date := null;
498 g_download_tranid := null;
499 g_upload_tranid := null;
500 g_last_tranid := null;
501 g_pub_item_tbl := g_empty_pub_item_tbl;
502
503 g_last_pub_item_name_glsd := null;
504 g_pub_item_last_synch_date := null;
505 g_last_pub_item_name_ifs := null;
506 g_comp_ref_ifs := null;
507 g_last_pub_item_name_gdt := null;
508 g_dml_type_gdt := null;
509
510 END reset_all_globals;
511
512 /*get the last synch date of a user*/
513 FUNCTION get_last_synch_date(p_user_id IN NUMBER)
514 RETURN DATE IS
515 l_date DATE;
516 BEGIN
517 SELECT last_synch_date_end INTO l_date
518 FROM asg_user WHERE user_id = p_user_id;
519 RETURN l_date;
520 END get_last_synch_date;
521
522 /* Allow download of attachment based on size */
523 FUNCTION allow_att_download(p_row_num IN NUMBER,
524 p_blob IN BLOB)
525 RETURN VARCHAR2 IS
526 l_max_size_str VARCHAR2(100);
527 l_csm_app_id NUMBER := 883;
528 l_ret_value VARCHAR2(1) := 'N';
529 BEGIN
530
531 /* Very first row, find profile option value once per synch. */
532 IF (p_row_num = 1) THEN
533 g_max_allowed_size := NULL;
534 g_atmt_size_exceeded := FALSE;
535 g_current_size := 0;
536 l_max_size_str := fnd_profile.VALUE_SPECIFIC(
537 name => 'CSM_MAX_ATTACHMENT_SIZE',
538 user_id => g_user_id,
539 responsibility_id => null,
540 application_id => l_csm_app_id);
541
542 IF (l_max_size_str IS NOT NULL) THEN
543 BEGIN
544 g_max_allowed_size := to_number(l_max_size_str);
545 /* Convert MB to bytes */
546 g_max_allowed_size := g_max_allowed_size*1024*1024;
547 EXCEPTION
548 WHEN OTHERS THEN
552 END;
549 /* Implies the profile value is not a purely numeric value */
550 g_max_allowed_size := NULL;
551 return l_ret_value;
553 END IF;
554 END IF;
555
556 IF (g_max_allowed_size IS NOT NULL AND
557 g_atmt_size_exceeded = FALSE) THEN
558 /* Length is returned in bytes */
559 g_current_size := g_current_size + dbms_lob.getlength(p_blob);
560 IF (g_current_size <= g_max_allowed_size) THEN
561 l_ret_value := 'Y';
562 ELSE
563 g_atmt_size_exceeded := TRUE;
564 END IF;
565 END IF;
566
567 return l_ret_value;
568
569 END allow_att_download;
570
571 /* Allow download of attachment based on size */
572 FUNCTION allow_attachment_download(p_row_num IN NUMBER,
573 p_blob IN BLOB)
574 RETURN VARCHAR2 IS
575 BEGIN
576 return 'Y';
577 END allow_attachment_download;
578
579 --Function to return the current device type as a number.
580 --returns 200 for laptop synch, 100 for PPC and 0 for others
581 FUNCTION get_device_type
582 RETURN NUMBER is
583 l_dev_name varchar2(30);
584 l_str varchar2(1024);
585 BEGIN
586 l_str := 'select '||G_OLITE_SCHEMA||'.CONS_EXT.GET_CURR_DEVICE from dual';
587 execute immediate l_str into l_dev_name;
588 if l_dev_name = 'WTG' THEN
589 return 200;
590 elsif l_dev_name = 'WCE' THEN
591 return 100;
592 else
593 RETURN 0;
594 end if;
595 END get_device_type;
596
597 --Function to return the current device type.
598 FUNCTION get_device_type_name
599 RETURN varchar2 is
600 l_dev_name varchar2(30);
601 l_str varchar2(1024);
602 BEGIN
603 l_str := 'select '||G_OLITE_SCHEMA||'.CONS_EXT.GET_CURR_DEVICE from dual';
604 execute immediate l_str into l_dev_name;
605 if l_dev_name = 'WTG' THEN
606 return 'LAPTOP';
607 elsif l_dev_name = 'WCE' THEN
608 return 'WINCE';
609 else
610 RETURN 'UNKNOWNDEVICE';
611 end if;
612 END get_device_type_name;
613
614 procedure detect_device_switch(p_user_name IN varchar2,
615 p_device_type OUT NOCOPY varchar2)
616 is
617 l_curr_dev_type varchar2(30);
618 l_prev_dev_type varchar2(30);
619 l_qry_string varchar2(512);
620 cursor c_get_dev_type(l_user_name varchar2) is
621 select current_device from asg_user where user_name = l_user_name;
622 l_version NUMBER;
623 l_param VARCHAR2(30) := 'VERSION';
624 begin
625 IF (g_olite_version IS NULL) THEN
626 l_qry_string := 'SELECT value ' ||
627 'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$all_config ' ||
628 'WHERE param = :1';
629 EXECUTE IMMEDIATE l_qry_string
630 INTO g_olite_version
631 USING l_param;
632 END IF;
633 l_version := to_number(substr(g_olite_version,1,instr(g_olite_version,'.')-1));
634 asg_helper.log('Olite version : '||g_olite_version,'asg_base',g_stmt_level);
635 if(l_version < 10) then
636 asg_helper.log('Not checking for device switch since Olite version is '||
637 'less than 10.0.0.0.0','asg_base',g_stmt_level);
638 else
639 asg_helper.log('Checking for device switch','asg_base',g_stmt_level);
640 open c_get_dev_type(p_user_name);
641 fetch c_get_dev_type into l_prev_dev_type;
642 close c_get_dev_type;
643
644 l_curr_dev_type := get_device_type_name;
645 p_device_type := l_curr_dev_type;
646 asg_helper.log('Previous device : '||l_prev_dev_type,'asg_base',g_stmt_level);
647 asg_helper.log('Current device : '||l_curr_dev_type,'asg_base',g_stmt_level);
648
649 if (l_prev_dev_type is null) then
650 /*First time synch*/
651 update asg_user set current_device = l_curr_dev_type
652 where user_name = p_user_name;
653 elsif(l_prev_dev_type <> l_curr_dev_type ) then
654 /*device switch detected .Set to complete refresh*/
655 l_qry_string := 'update '||asg_base.G_OLITE_SCHEMA||'.c$pub_list_q '
656 ||'set comp_ref = ''Y'' '||' where name IN '
657 ||' ( select item_id from asg_pub_item)' ;
658 execute immediate l_qry_string;
659 update asg_user set current_device = l_curr_dev_type
660 where user_name = p_user_name;
661 asg_helper.log('Device switch detected. Doing complete refresh',
662 'asg_base',g_stmt_level);
663 else
664 /*Device type is same.Do nothing*/
665 null;
666 end if;
667 end if;
668 exception
669 when others then
670 asg_helper.log('Exception in detect_device_switch',
671 'asg_base',g_stmt_level);
672 end detect_device_switch;
673
674 -- Returns G_YES if the user is a valid MFS user
675 FUNCTION is_mobile_user(p_user_id IN NUMBER)
676 RETURN VARCHAR2 IS
677 l_ret_value VARCHAR2(1) := G_NO;
678 l_cnt NUMBER := 0;
679 BEGIN
680 SELECT count(*) into l_cnt
681 FROM asg_user
682 WHERE user_id = p_user_id AND enabled = 'Y';
683 IF (l_cnt = 1) THEN
684 l_ret_value := G_YES;
685 END IF;
686 return l_ret_value;
687 END is_mobile_user;
688
689 -- Returns a list of all valid mobile users
690 FUNCTION get_mobile_users(p_device_type IN VARCHAR2)
691 RETURN mobile_user_list_type IS
692 CURSOR c_user_list
693 IS
694 SELECT user_id
695 FROM asg_user
696 WHERE enabled = 'Y';
697 l_user_list mobile_user_list_type;
698 BEGIN
699 IF (p_device_type <> G_ALL_DEVICES AND
700 p_device_type <> G_POCKETPC AND
701 p_device_type <> G_LAPTOP ) THEN
702 -- return empty user_list
703 return l_user_list;
704 END IF;
705 IF (p_device_type = G_ALL_DEVICES) THEN
706 OPEN c_user_list;
707 FETCH c_user_list BULK COLLECT INTO l_user_list;
708 CLOSE c_user_list;
709 return l_user_list;
710 END IF;
711
712 END get_mobile_users;
713
714 -- Returns the appid/respid used when creating this user
715 PROCEDURE get_user_app_responsibility(p_user_id IN NUMBER,
716 p_app_id OUT NOCOPY NUMBER,
717 p_resp_id OUT NOCOPY NUMBER)
718 IS
719 BEGIN
720 p_app_id := NULL;
721 p_resp_id := NULL;
722
723 SELECT app_id, responsibility_id into p_app_id, p_resp_id
724 FROM asg_user
725 WHERE user_id = p_user_id and enabled = 'Y';
726
727 END get_user_app_responsibility;
728
729 END asg_base;
[Home] [Help]