[Home] [Help]
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
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;
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,
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
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:
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:
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;
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 ' ||
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 ' ||
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
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;
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;
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;
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
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
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;
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);
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;