1 PACKAGE JTF_UM_UTIL_PVT AUTHID CURRENT_USER as
2 /* $Header: JTFVUUTS.pls 120.8 2006/02/14 00:15:13 snellepa ship $ */
3
4 /**
5 * Procedure : get_wf_user
6 * Type : Private
7 * Pre_reqs :
8 * Description : this procedure returns the user name, email and wf_user_name of a given user.
9 * If a email address is passed as an input parameter it
10 * checks to see if the email address is a valid one.
11 * If x_requester_user_name or x_requester_email is NULL then a valid email /user could
12 * not be found
13 * If x_wf_user is NULL, and x_requester_user_name and x_requester_email are not NULL then
14 * the user /email combination is valid but does not have a valid
15 * user in wf_user.
16 * Parameters : None
17 * input parameters
18 * param x_requester_user_name (*) - user name of the requester
19 * param x_requester_email - email address the requester would like to use.
20 * (*) required fields
21 * output parameters
22 * param x_requester_user_name
23 * param x_requester_email
24 * param x_wf_user_name
25 * param x_return_status
26 * Errors : Expected Errors
27 * x_requester_user_name and x_requester_email is null
28 * x_requester_user_name is not a valid user
29 * x_requester_email does not correspond to a valid user
30 * Other Comments :
31 * DEFAULTING LOGIC
32 * If only the user name is passed then the email is defaulted using the following logic
33 * 1. Email address from fnd_users where user_name = x_requester_user_name
34 * 2. Email from per_all_people_F where person_id = employee_id (retrieved from fnd_users using the user_name)
35 * 3. Email from hz_contact_points where owner_type_id = party_id and owner_table = 'HZ_PARTIES' and contact_point_type = 'EMAIL' and primary_flag = 'Y'.
36 * Party_id is determined using the following :
37 * (a)from hz_parties where party_id = customer_id (retrieved from
38 * fnd_users using the user_name) and party_type = 'PERSON' or 'ORGANIZATION'.
39 * (b)from hz_parties where party_id = customer_id (retrieved from fnd_users using the user_name) and party_type = 'PARTY_RELATIONSHIP'.
40 * Use this party_id to determine the subject_id from the hz_party_relationships
41 * table. The subject_id would be used for the querying hz_contact_points.
42 * In all the above cases the user, employee, party etc. have to be valid.
43 *
44 * The same logic is used to validate the requester_email.
45 */
46 procedure get_wf_user(p_api_version_number in number,
47 p_init_msg_list in varchar2 := FND_API.G_FALSE,
48 p_commit in varchar2 := FND_API.G_FALSE,
49 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
50 x_requester_user_name in out NOCOPY varchar2,
51 x_requester_email in out NOCOPY varchar2,
52 x_wf_user out NOCOPY varchar2,
53 x_return_status out NOCOPY varchar2,
54 x_msg_count out NOCOPY number,
55 x_msg_data out NOCOPY varchar2
56 );
57
58
59 /**
60 * Procedure : GetAdHocUser
61 * Type : Public
62 * Pre_reqs : WF_DIRECTORY.CreateAdHocUser and
63 * WF_DIRECTORY.SetAdHocUserAttr
64 * Description : This API tries to create an adhoc user with the provided
65 * username. If the username is already being used in the
66 * database, just update input attributes.
67 * Parameters :
68 * input parameters
69 * @param
70 * p_username
71 * description: The adhoc username.
72 * required : Y
73 * p_display_name
74 * description: The adhoc display name.
75 * required : N
76 * default : null
77 * p_language
78 * description: The value of the database NLS_LANGUAGE initialization
79 * parameter that specifies the default language-dependent
80 * behavior of the user's notification session. If null,
81 * the procedure resolves this to the language setting of
82 * your current session.
83 * required : N
84 * default : null
85 * p_territory
86 * description: The value of the database NLS_TERRITORY initialization
87 * parameter that specifies the default territory-dependant
88 * date and numeric formatting used in the user's
89 * notification session. If null, the procedure resolves
90 * this to the territory setting of your current session.
91 * required : N
92 * default : null
93 * p_description
94 * description: Description for the user.
95 * required : N
96 * default : null
97 * p_notification_preference
98 * description: Indicate how this user prefers to receive notifications:
99 * 'MAILTEXT', 'MAILHTML', 'MAILATTH', 'QUERY' or 'SUMMARY'.
100 * If null, the procedure sets the notification preference
101 * to 'MAILHTML'.
102 * required : N
103 * default : 'MAILTEXT'
104 * p_email_address
105 * description: Electronic mail address for this user.
106 * required : Y
107 * p_fax
108 * description: Fax number for the user
109 * required : N
110 * default : null
111 * p_status
112 * description: The availability of the user to participate in a
113 * workflow process. The possible statuses are 'ACTIVE',
114 * 'EXTLEAVE', 'INACTIVE', and 'TMPLEAVE'. If null, the
115 * procedure sets the status to 'ACTIVE'.
116 * required : N
117 * default : 'ACTIVE'
118 * p_expiration_date
119 * description: The date at which the user is no longer valid in the
120 * directory service. If null, the procedure defaults the
121 * expiration date to sysdate.
122 * required : N
123 * default : sysdate
124 * output parameters
125 * @return
126 * Errors : possible errors raised by this API
127 * Other Comments :
128 */
129 PROCEDURE GetAdHocUser (p_api_version_number in number,
130 p_init_msg_list in varchar2 default FND_API.G_FALSE,
131 p_commit in varchar2 default FND_API.G_FALSE,
132 p_validation_level in number default FND_API.G_VALID_LEVEL_FULL,
133 p_username in varchar2,
134 p_display_name in varchar2 default null,
135 p_language in varchar2 default null,
136 p_territory in varchar2 default null,
137 p_description in varchar2 default null,
138 p_notification_preference in varchar2 default 'MAILTEXT',
139 p_email_address in varchar2,
140 p_fax in varchar2 default null,
141 p_status in varchar2 default 'ACTIVE',
142 p_expiration_date in date default sysdate,
143 x_return_status out NOCOPY varchar2,
144 x_msg_data out NOCOPY varchar2,
145 x_msg_count out NOCOPY varchar2);
146
147 /**
148 * Procedure : EMAIL_NOTIFICATION
149 * Type : Public
150 * Pre_reqs : WF_NOTIFICATION.Send, WF_ENGINE.SetItemAttrText
151 * Description : Send email notification to fnd user.
152 * Parameters :
153 * input parameters
154 * @param
155 * p_username
156 * description: FND user's username. The recep of the notification.
157 * required : Y
158 * validation : Must be a valid FND User.
159 * p_subject
160 * description: The subject of the notification.
161 * required : Y
162 * p_text_body
163 * description: Text version of the notification body.
164 * required : Y
165 * p_HTML_body
166 * description: HTML version of the notification body.
167 * required : N
168 * default : null
169 * p_email_address
170 * description: Send to this email and overwrite the email address
171 * in the FND_USER table.
172 * required : N
173 * default : null
174 * output parameters
175 * @return
176 * Errors : possible errors raised by this API
177 * Other Comments :
178 */
179 PROCEDURE EMAIL_NOTIFICATION (p_api_version_number in number,
180 p_init_msg_list in varchar2 default FND_API.G_FALSE,
181 p_commit in varchar2 default FND_API.G_FALSE,
182 p_validation_level in number default FND_API.G_VALID_LEVEL_FULL,
183 p_username in varchar2 default null,
184 p_email_address in varchar2 default null,
185 p_subject in varchar2,
186 p_text_body in varchar2,
187 p_HTML_body in varchar2 default null,
188 x_return_status out NOCOPY varchar2,
189 x_msg_data out NOCOPY varchar2,
190 x_msg_count out NOCOPY varchar2);
191
192
193 /*
194 ** VALUE_SPECIFIC - Get profile value for a specific user/resp/appl
195 **
196 ** Unlike fnd_profile.value_specific this procedure retrieves the
197 ** profile value only at the level(s) that is not null.
198 ** For retrieving profiles at site level site id should be set to true.
199 ** For retrieving profiles at responsibility level pass both responsibility_id and resp_appl_id
200 ** To retrieve values at any level pass all the parameters.
201 ** This procedure does not get the resp and app id from login.
202 */
203 function VALUE_SPECIFIC(NAME in varchar2,
204 USER_ID in number default null,
205 RESPONSIBILITY_ID in number default null,
206 RESP_APPL_ID in number default null,
207 APPLICATION_ID in number default null,
208 SITE_LEVEL in boolean default false) return varchar2;
209
210
211 /**
212 * This procedure gets the default appl and resp id using the following logic
213 *
214 * If appl id and resp id are null - get the user value of the profiles
215 * JTF_PROFILE_DEFAULT_RESPONSIBILITY, JTF_PROFILE_DEFAULT_APPLICATION. These
216 * values can be set to 'Pending appr' if user requires approval.
217 * In this case we use the resp of the usertype the user has registered to.
218 * these values could still be null if the user was registered from fnd.
219 *
220 */
221
222 procedure getDefaultAppRespId (P_USERNAME IN VARCHAR2,
223 P_RESP_ID IN NUMBER := null,
224 P_APPL_ID IN NUMBER := null,
225 X_RESP_ID out NOCOPY NUMBER,
226 X_APPL_ID out NOCOPY NUMBER);
227
228 /*
229 * Name : check_role
230 * Pre_reqs : None
231 * Description : Will determine if a user has a specific role or not
232 * Parameters :
233 * input parameters
234 * @param p_user_id
235 * description: The user_id of a user
236 * required : Y
237 * validation : Must be a valid user_id
238 * @param p_auth_principal_id
239 * description: The jtf_auth_principal_id of a role
240 * required : Y
241 * validation : Must be a valid jtf_auth_principal_id
242 *
243 * Note:
244 *
245 * This API will raise an exception if a user name or a jtf_auth_principal_id
246 * is invalid
247 */
248
249 function check_role(
250 p_user_id in number,
251 p_auth_principal_id in number
252 ) return boolean;
253
254 /*
255 * Name : check_role
256 * Pre_reqs : None
257 * Description : Will determine if a user has a specific role or not
258 * Parameters :
259 * input parameters
260 * @param p_user_id
261 * description: The user_id of a user
262 * required : Y
263 * validation : Must be a valid user_id
264 * @param p_principal_name
265 * description: The principal_name of a role
266 * required : Y
267 * validation : Must be a valid principal_name
268 *
269 * Note:
270 *
271 * This API will raise an exception if a user name or a principal_name
272 * is invalid
273 */
274
275 function check_role(
276 p_user_id in number,
277 p_principal_name in varchar2
278 ) return boolean;
279
280
281
282 /*
283 * Name : VALIDATE_USER_ID
284 * Pre_reqs : None
285 * Description : Will validate the user_id
286 * Parameters :
287 * input parameters
288 * @param
289 * p_user_id:
290 * description: The user_id of a user
291 * required : Y
292 *
293 * output parameters
294 * None
295 *
296 * Notes:
297 *
298 * This is a package private helper function.
302
299 */
300
301 function VALIDATE_USER_ID(p_user_id number) return boolean;
303
304 /*
305 * Name : VALIDATE_SUBSCRIPTION_ID
306 * Pre_reqs : None
307 * Description : Will validate the subscription_id
308 * Parameters :
309 * input parameters
310 * @param
311 * p_subscription_id:
312 * description: The subscription_id of the subscription
313 * required : Y
314 *
315 * output parameters
316 * None
317 *
318 * Notes:
319 *
320 * This is a package private helper function.
321 */
322
323 function VALIDATE_SUBSCRIPTION_ID(p_subscription_id number) return boolean;
324
325 /**
326 * Procedure : grant_roles
327 * Type : Private
328 * Pre_reqs : None
329 * Description : Will grant roles to users
330 * Parameters :
331 * input parameters
332 * p_user_name:
333 * description: The user_name of the user
334 * required : Y
335 * validation : Must be a valid user_name
336 * p_role_id
337 * description: The value of the JTF_AUTH_PRINCIPAL_ID
338 * required : Y
339 * validation : Must exist as a JTF_AUTH_PRONCIPAL_ID
340 * in the table JTF_AUTH_PRINCIPALS_B
341 * p_source_name
342 * description: The value of the name of the source
343 * required : Y
344 * validation : Must be "USERTYPE" or "ENROLLMENT"
345 * p_source_id
346 * description: The value of the id associated with the source
347 * required : Y
348 * validation : Must be a usertype_id or a subscription_id
349 * output parameters
350 * None
351 */
352 procedure grant_roles (
353 p_user_name in varchar2,
354 p_role_id in number,
355 p_source_name in varchar2,
356 p_source_id in varchar2
357 );
358
359 /*
360 ** GET_SPECIFIC - Get a profile value for a specific user/resp/appl.
361 ** Does not go up the hierarchy to retrieve the profile
362 ** values if input values are null.
363 */
364 procedure GET_SPECIFIC(name_z in varchar2,
365 user_id_z in number default null,
366 responsibility_id_z in number default null,
367 resp_appl_id_z in number default null,
368 application_id_z in number default null,
369 site_id_z in boolean default false,
370 val_z out NOCOPY varchar2,
371 defined_z out NOCOPY boolean);
372
373 /*
374 * Name : GET_USER_ID
375 * Pre_reqs : None
376 * Description : Will get user id from username
377 * Parameters :
378 * input parameters
379 * @param
380 * p_user_id:
381 * description: The user_name of a user
382 * required : Y
383 *
384 * output parameters
385 * None
386 *
387 * Notes:
388 * This function will return null, if it can not find username
389 *
390 */
391
392 function GET_USER_ID(p_user_name varchar2) return NUMBER;
393
394 /*
395 * Name : GET_USERTYPE_ID
396 * Pre_reqs : None
397 * Description : Will get user type id for a user
398 * Parameters :
399 * input parameters
400 * @param
401 * p_user_id:
402 * description: The user_id of a user
403 * required : Y
404 *
405 * output parameters
406 * None
407 *
408 * Notes:
409 * This function will return null, if it can not find username
410 *
411 */
412
413 function GET_USERTYPE_ID(p_user_id NUMBER) return NUMBER;
414
415 /*
416 *@Name: Check_Party_type
417 *@Param: Party_id
418 *@Description: function which returns the party_type of a party in hz_parties given
419 * given party_id
420 *@Output: party_type varchar2
421 */
422
423 function CHECK_PARTY_TYPE(p_party_id NUMBER) return VARCHAR2;
424
425
426 /*
427 *@Name: validate_user_name
428 *@Param: username
429 *@Description: checks is a user name is valid
430 *@Output: number ( 1 True , 0 False)
431 Code Changes for 5033237/5033238, the errMsg from FND_MESSAGE Stack is being re-used.
432
433 */
434 function validate_user_name(username varchar2, errMsg out NOCOPY varchar2) return number;
435
436
437 /*
438 *@Name: validate_user_name_in_use
439 *@Param: username
440 *@Description: checks is a user name is in use
441 *@Output: number ( 1 True , 0 False)
442 */
443 function validate_user_name_in_use(username varchar2) return number;
444
445 /* function to get the constant FND_API.G_MISS_DATE and use it in sql*/
446 FUNCTION GET_G_MISS_DATE return DATE;
447
448 /*
449 bug 4903775 - for name formatting based on region territory
450 */
451 function format_user_name(fname varchar2, lname varchar2) return varchar;
452
453
454 end JTF_UM_UTIL_PVT;
[Home] [Help]