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