1 PACKAGE asg_helper AUTHID DEFINER AS
2 /*$Header: asghlps.pls 120.3 2006/09/20 17:04:31 rsripada noship $*/
3
4 -- DESCRIPTION
5 -- This package is used for miscellaneous chores
6 --
7 -- HISTORY
8 -- 29-sep-2004 ssabesan Removed method truncate_sdq
9 -- 23-jun-2004 ssabesan Fix bug 3713556
10 -- 05-apr-2004 rsripada Add enable_olite_privs
11 -- 01-apr-2004 ssabesan Added encrypt, decrypt, set_profile_to_null routines
12 -- 13-jan-2004 ssabesan Added method recreate_synonyms() for use during
13 -- user creation.
14 -- 30-dec-2003 rsripada Added procedures for creating/dropping olite
15 -- synonyms
16 -- 22-oct-2003 ssabesan Merge 115.7.1158.8 into mainline
17 -- 01-oct-2003 ssabesan Purge SDQ changes (bug 3170790)
18 -- 12-jun-2003 rsripada Added procedure to determine last synch device type
19 -- 25-mar-2003 ssabesan Added API for updating user_sertup_errors and
20 -- synch_errors column in asg_user
21 -- 12-feb-2003 ssabesan Added API for updating hwm_tranid column in asg_user
22 -- 10-jan-2003 ssabesan Added a wrapper around check_is_log_enabled()
23 -- for use from java program.
24 -- 06-jan-2003 ssabesan PL/SQL API changes. Added method for checking
25 -- whether logging is enabled.
26 -- 02-jan-2003 rsripada Bug fix 2731476
27 -- 19-nov-2002 rsripada Added routines for disabling user synch
28 -- 10-nov-2002 ssabesan added routine for specifying a pub-item
29 -- to be completely refreshed
30 -- 09-sep-2002 rsripada Added routines to enable/disable synch
31 -- 28-may-2002 rsripada Created
32
33 -- Invokes the callback to populate all the user's acc tables
34 PROCEDURE populate_access(p_user_name IN VARCHAR2,
35 p_pub_name IN VARCHAR2);
36
37 -- Invokes the callback to remove all acc table records
38 PROCEDURE delete_access(p_user_name IN VARCHAR2,
39 p_pub_name IN VARCHAR2);
40
41 -- Creates a sequence partitions
42 PROCEDURE create_seq_partition(p_user_name IN VARCHAR2,
43 p_seq_name IN VARCHAR2,
44 p_start_value IN VARCHAR2,
45 p_next_value IN VARCHAR2);
46
47 -- Drop the sequence partition
48 PROCEDURE drop_seq_partition(p_user_name IN VARCHAR2,
49 p_seq_name IN VARCHAR2);
50
51 -- insert pub responsibilities
52 PROCEDURE insert_user_pub_resp(p_user_name IN VARCHAR2,
53 p_pub_name IN VARCHAR2,
54 p_resp_id IN NUMBER,
55 p_app_id IN NUMBER);
56
57 -- delete user-pub
58 PROCEDURE delete_user_pub(p_user_name IN VARCHAR2,
59 p_pub_name IN VARCHAR2);
60
61 -- delete pub responsibilites
62 PROCEDURE delete_user_pub_resp(p_user_name IN VARCHAR2,
63 p_pub_name IN VARCHAR2,
64 p_resp_id IN NUMBER);
65
66 -- wrapper on fnd_log
67 PROCEDURE log(message IN VARCHAR2,
68 module IN VARCHAR2 ,
69 log_level IN NUMBER);
70
71
72 -- Used to clean up metadata associated with an user
73 PROCEDURE drop_user(p_user_name IN VARCHAR2);
74
75 -- Used to update a parameter in asg_config
76 PROCEDURE set_config_param(p_param_name IN VARCHAR2,
77 p_param_value IN VARCHAR2,
78 p_param_description IN VARCHAR2 := NULL);
79
80 -- Returns the value column in asg_config table based on the
81 -- specified parameter name
82 FUNCTION get_param_value(p_param_name IN VARCHAR2)
83 return VARCHAR2;
84
85 -- Used to enable synch for all publications
86 PROCEDURE enable_synch;
87
88 -- Used to enable synch for the specified publication
89 PROCEDURE enable_pub_synch(p_pub_name IN VARCHAR2);
90
91 -- Used to disable synch for all publications
92 PROCEDURE disable_synch;
93
94 -- Used to disable synch for the specified publication
95 PROCEDURE disable_pub_synch(p_pub_name IN VARCHAR2);
96
97 -- Returns FND_API.G_TRUE if the user synch is enabled
98 FUNCTION is_user_synch_enabled(p_user_name IN VARCHAR2,
99 p_disabled_synch_message OUT NOCOPY VARCHAR2)
100 return VARCHAR2;
101
102 --routine for setting complete_refresh for a pub-item for all users
103 --subscribed to that Pub-Item.
104 PROCEDURE set_complete_refresh(p_pub_item VARCHAR2);
105
106 -- Disables synch for specified user/publication
107 PROCEDURE disable_user_pub_synch(p_user_id IN NUMBER,
108 p_pub_name IN VARCHAR2);
109
110 -- Enables synch for specified user/publication
111 PROCEDURE enable_user_pub_synch(p_user_id IN NUMBER,
112 p_pub_name IN VARCHAR2);
113
114
115 -- Enables all users access to public group
116 PROCEDURE set_group_access(p_group_name IN VARCHAR2);
117
118 -- Check whether is logging is enabled
119 FUNCTION check_is_log_enabled (log_level IN NUMBER) RETURN BOOLEAN;
120
121 --wrapper around check_is_log_enabled(log_level IN NUMBER) method
122 FUNCTION check_log_enabled(log_level IN NUMBER) RETURN VARCHAR2;
123
124 --API for updating hwm_tranid column in asg_user table
125 PROCEDURE update_hwm_tranid(p_user_name IN VARCHAR2,p_tranid IN NUMBER);
126
127 --API for autonomous update of USER_SETUP_ERRORS column in asg_user table
128 PROCEDURE update_user_setup_errors(p_user_name IN VARCHAR2,
129 p_mesg IN VARCHAR2);
130
131 --API for synching info between asg_user_pub_resps and asg_user tables
132 --after adding/dropping subscription
133 PROCEDURE update_user_resps(p_user_name IN VARCHAR2);
134
135 --API for updating SYNCH_ERRORS column in asg_user table
136 PROCEDURE update_synch_errors(p_user_name IN VARCHAR2,
137 p_mesg IN VARCHAR2);
138
139 --API for autonomous update of hwm_tranid and synch_errors.
140 PROCEDURE set_synch_errmsg(p_user_name IN VARCHAR2, p_tranid IN NUMBER,
141 p_device_type IN VARCHAR2, p_mesg IN VARCHAR2);
142
143 --Routine that sets the device type of last synch
144 PROCEDURE set_last_synch_device_type;
145
146 --Routine that sets up a user for complete refresh for a publication
147 PROCEDURE set_first_synch(p_clientid in varchar2,p_pub in varchar2);
148
149 -- Routine for setting the SSO login profile for each user
150 PROCEDURE set_sso_profile(p_userId in VARCHAR2);
151
152 -- Routine for creating olite synonyms
153 PROCEDURE create_olite_synonyms;
154
155 -- Routine for dropping olite synonyms
156 PROCEDURE drop_olite_synonyms;
157
158 PROCEDURE recreate_synonyms(p_dt IN DATE);
159
160 function encrypt(p_input_string varchar2,p_key varchar2) return varchar2;
161
162 function decrypt(p_input_string varchar2,p_key varchar2) return varchar2;
163
164 --Sets a given profile value to null at all levels.
165 procedure set_profile_to_null(p_profile_name varchar2);
166
167 -- Grants necessary select, insert privileges to mobile table/views to
168 -- Olite db schema
169 PROCEDURE enable_olite_privs;
170
171 --This procedure encrypts p_input_string using the key p_key.
172 --It then updates the asg_config param p_param_name
173 procedure encrypt_and_copy(p_param_name varchar2,p_input_string varchar2,
174 p_key varchar2,p_param_desc varchar2);
175
176 --This function reads the value of the asg_config param p_param_name
177 --The value is decrypted using p_key and the decrypted string is returned.
178 function decrypt_and_return(p_param_name varchar2,p_key varchar2)
179 return varchar2;
180
181 --Earlier versions that used asg_config
182 procedure encrypt_old(p_param_name varchar2,p_input_string varchar2,
183 p_key varchar2,p_param_desc varchar2);
184
185 function decrypt_old(p_param_name varchar2,p_key varchar2)
186 return varchar2;
187
188 function get_key
189 return varchar2;
190
191 END asg_helper;