1 package fnd_user_resp_groups_api as
2 /* $Header: AFSCURGS.pls 120.6 2006/02/27 12:48:13 tmorrow ship $ */
3 /*#
4 * Table Handler to insert or update data in FND_USER_RESP_GROUPS table.
5 * @rep:scope public
6 * @rep:product FND
7 * @rep:displayname User Responsibility Group
8 * @rep:lifecycle active
9 * @rep:compatibility S
10 * @rep:category BUSINESS_ENTITY FND_USER
11 */
12
13 --
14 -- Assignment_Exists
15 -- Check if user/resp/group assignment exists. This API does not check
16 -- start or end dates on the user, repsonsibility, or responsibility
17 -- assignment.
18 -- IN
19 -- user_id - User to get assignment
20 -- responsibility_id - Responsibility to be assigned
21 -- responsibility_application_id - Resp Application to be assigned
22 -- security_group_id - Security Group to be assigned (default to current)
23 -- direct_flag- 'Y', 'N', or 'E' (default) determines whether this checks
24 -- indirect assignments from wf_role_hierarchy or just
25 -- direct assignments.
26 -- 'Y'= Direct only. Dates can be updated.
27 -- 'N'= Indirect only. Dates cannot be updated on these assignments.
28 -- 'E'= Either Direct or Indirect. (this is the default)
29 -- RETURNS
30 -- TRUE if assignment is found
31 --
32 function Assignment_Exists(
33 user_id in number,
34 responsibility_id in number,
35 responsibility_application_id in number,
36 security_group_id in number default null,
37 direct_flag in varchar2 default null /* null means 'E': Direct or Indirect*/
38 )
39 return boolean;
40
41 --
42 -- Validates the security context to determine if the given user has access
43 -- to the given responsibility. This API checks start and end dates on
44 -- the user, responsibility, and responsibility assignment.
45 -- IN
46 -- p_user_id - the user id
47 -- p_resp_appl_id - the application id of the responsibility
48 -- p_responsibility_id - the responsibility id
49 -- p_security_group_id - the security group id
50 -- OU
51 -- x_status:
52 -- 'N' if the security context is not valid
53 -- 'Y' if the security context is valid
54 --
55 procedure validate_security_context(
56 p_user_id in number,
57 p_resp_appl_id in number,
58 p_responsibility_id in number,
59 p_security_group_id in number,
60 x_status out nocopy varchar2);
61
62
63 --
64 -- Assignment_Check (INTERNAL routine only)
65 --
66 -- Check whether a particular assignment of a user to a role exists,
67 -- regardless of start/end date. This is different from
68 -- wf_directory.IsPerformer which only operates for current sysdate.
69 -- In: username- user name
70 -- In: rolename- role name
71 --
72 function Assignment_Check(username in varchar2,
73 rolename in varchar2,
74 direct_flag in varchar2 /* 'D', 'I', or 'E'*/)
75 return boolean;
76
77 --
78 -- Role_Name_from_Resp
79 --
80 -- Returns role name in the format FND_RESP|APPSNAME|RESPKEY|SECGRPKEY
81 -- from the security group and resp passed in. This is generally only used
82 -- by FND internal code upgrading old data.
83 --
84 function Role_Name_from_Resp(
85 x_resp_id in number,
86 x_resp_appl_id in number,
87 x_secgrp_id in number) return varchar2;
88
89 --
90 -- Role_Name_from_Resp_No_Exc
91 --
92 -- This is a version of role_name_from_resp which won't raise exceptions,
93 -- to be used when calling from somewhere that errors can't be trapped
94 -- like inline inside a SQL select statement. Again only used by internal
95 -- FND code.
96 function Role_Name_from_Resp_No_Exc(
97 x_resp_id in number,
98 x_resp_appl_id in number,
99 x_secgrp_id in number) return varchar2;
100
101 --
102 -- Lock_Assignment
103 -- Lock the row for an assignment (used by a UI)
104 -- IN
105 -- user_id - User
106 -- responsibility_id - Responsibility
107 -- responsibility_application_id - Resp Application
108 -- security_group_id - Security Group
109 -- start_date - Start date of assignment
110 -- end_date - End date of assignment
111 -- EXCEPTION
112 --
113 --
114 procedure Lock_Assignment(
115 x_user_id in number,
116 x_responsibility_id in number,
117 x_resp_application_id in number,
118 x_security_group_id in number,
119 x_start_date in date,
120 x_end_date in date,
121 x_description in varchar2);
122
123 --
124 -- Insert_Assignment
125 -- Insert a new user/resp/group assignment
126 -- IN
127 -- user_id - User to get assignment
128 -- responsibility_id - Responsibility to be assigned
129 -- responsibility_application_id - Resp Application to be assigned
130 -- security_group_id - Security Group to be assigned
131 -- start_date - Start date of assignment
132 -- end_date - End date of assignment
133 -- description - Optional comment
134 -- EXCEPTION
135 -- If user/resp/group assignment already exists
136 --
137 procedure Insert_Assignment(
138 user_id in number,
139 responsibility_id in number,
140 responsibility_application_id in number,
141 security_group_id in number default null,
142 start_date in date,
143 end_date in date,
144 description in varchar2);
145
146 --
147 -- Update_Assignment
148 -- Update an existing user/resp/group assignment
149 -- IN
150 -- KEY VALUES: These columns identify row to update
151 -- user_id - User to get assignment
152 -- responsibility_id - Responsibility to be assigned
153 -- responsibility_application_id - Resp Application to be assigned
154 -- security_group_id - Security Group to be assigned (default to current)
155 -- UPDATE VALUES: These columns identify values to update
156 -- start_date - Start date of assignment
157 -- end_date - End date of assignment
158 -- description - Optional comment
159 -- FLAGS
160 -- update_who_columns- pass 'Y' or 'N' ('Y' is default if not passed)
161 -- 'N' = leave old who vals. 'Y'= update who cols to current user/date
162 -- EXCEPTION
163 -- If user/resp/group assignment does not exist
164 --
165 procedure Update_Assignment(
166 user_id in number,
167 responsibility_id in number,
168 responsibility_application_id in number,
169 security_group_id in number default null,
170 start_date in date,
171 end_date in date,
172 description in varchar2,
173 update_who_columns in varchar2 default null
174 /* 'N' = leave old who vals. 'Y' (default) = update who to current*/);
175
176 --
177 -- Upload_Assignment
178 -- Update user/resp/group assignment if it exists,
179 -- otherwise insert new assignment.
180 -- IN
181 -- user_id - User to get assignment
182 -- responsibility_id - Responsibility to be assigned
183 -- responsibility_application_id - Resp Application to be assigned
184 -- security_group_id - Security Group to be assigned (default to current)
185 -- start_date - Start date of assignment
186 -- end_date - End date of assignment
187 -- description - Optional comment
188 -- update_who_columns in varchar2 default null
189 -- 'N' = leave old who vals. 'Y' (default) = update who to current
190 --
191 procedure Upload_Assignment(
192 user_id in number,
193 responsibility_id in number,
194 responsibility_application_id in number,
195 security_group_id in number default null,
196 start_date in date,
197 end_date in date,
198 description in varchar2,
199 update_who_columns in varchar2 default null
200 /* 'N' = leave old who vals. 'Y' (default) = update who to current*/);
201
202 --
203 -- FNDLOAD-friendly cover for Upload_Assignment above
204 --
205 /*#
206 * Creates or updates User-Responsibility Group information as appropriate.
207 * @param x_user_name User Name
208 * @param x_resp_key Responsibility Key
209 * @param x_app_short_name Application Short Name
210 * @param x_security_group Security Group Name
211 * @param x_owner Owner Name
212 * @param x_start_date Effective Start Date
213 * @param x_end_date Effective End Date
214 * @param x_description Description
215 * @param x_last_update_date Insert/Update Date
216 * @rep:scope public
217 * @rep:lifecycle active
218 * @rep:displayname Create/Update User Responsibility Group
219 * @rep:compatibility S
220 */
221 procedure LOAD_ROW (
222 X_USER_NAME in VARCHAR2,
223 X_RESP_KEY in VARCHAR2,
224 X_APP_SHORT_NAME in VARCHAR2,
225 X_SECURITY_GROUP in VARCHAR2,
226 X_OWNER in VARCHAR2,
227 X_START_DATE in VARCHAR2,
228 X_END_DATE in VARCHAR2,
229 X_DESCRIPTION in VARCHAR2,
230 X_LAST_UPDATE_DATE in DATE default sysdate);
231
232
233 --
234 -- Makes a role for this resp/secgrp.
235 --
236 procedure sync_roles_one_resp_secgrp(
237 respid in number,
238 appid in number,
239 respkey in varchar2,
240 secgrpid in number,
241 secgrpkey in varchar2,
242 startdate in date,
243 enddate in date);
244
245 --
246 -- Makes roles for all security groups for a particular resp.
247 -- This should be called when a resp is created/deleted.
248 --
249 procedure sync_roles_all_secgrps(
250 respid in number,
251 appid in number,
252 respkey in varchar2,
253 startdate in date,
254 enddate in date);
255
256 --
257 -- Makes roles for all resps for a particular security group.
258 -- This should be called when a security group is created/deleted.
259 --
260 procedure sync_roles_all_resps(
261 secgrpid in varchar2,
262 secgrpkey in varchar2);
263
264
265 --
266 -- Makes roles for all resps for all security groups.
267 -- This routine is generally called once by an upgrade script which
268 -- converts from the old resp roles to the new resp roles.
269 -- Bug4349774 added sync_all_flag to enable original functionality when
270 -- set to TRUE.
271
272 /*#
273 * Creates roles for all responsibility and security group combinations.
274 * @param sync_all_flag Insert/Update All
275 * @rep:scope public
276 * @rep:lifecycle active
277 * @rep:displayname Creates all responsibility/security group roles.
278 * @rep:compatibility S
279 */
280
281 procedure sync_roles_all_resp_secgrps(sync_all_flag in boolean default FALSE);
282
283 --
284 -- Moves old data from fnd_user_resp_groups table to new workflow tables.
285 -- This routine is no longer used because this is now done in the
286 -- bulk sync. But it remains exposed just in case in an emergency
287 -- it might be useful to resolve something.
288 procedure one_time_furg_to_wf_upgrade;
289
290 -- Converts role names from FND_RESPX:Y format to FND_RESP_SEC|A|B|C format
291 -- if necessary. Returns upgraded role name or original if it is in any
292 -- other format.
293 function upgrade_resp_role(respid in number,
294 appid in number) return varchar2;
295
296
297
298 -- sync_roles_all_secgrps_int
299 --
300 -- Bug4322412
301 -- For a given resp, sync roles for all security groups if the role
302 -- does not already exist.
303 --
304 -- NOTE:This routine does not update existing roles. To update existing roles
305 -- the routine sync_roles_all_secgrps should be used.
306 --
307 procedure sync_roles_all_secgrps_int(
308 respid in number,
309 appid in number,
310 respkey in varchar2,
311 startdate in date,
312 enddate in date);
313
314
315 end fnd_user_resp_groups_api;