1 package FND_USER_PKG as
2 /* $Header: AFSCUSRS.pls 120.16.12010000.1 2008/07/25 14:22:04 appldev ship $ */
3 /*#
4 * Table Handler to insert or update data in FND_USER table.
5 * @rep:scope public
6 * @rep:product FND
7 * @rep:displayname User
8 * @rep:lifecycle active
9 * @rep:compatibility S
10 * @rep:category BUSINESS_ENTITY FND_USER
11 * @rep:ihelp FND/@o_funcsec#o_funcsec See the related online help
12 */
13
14
15
16 null_char varchar2(8) := '*NULL*';
17 null_date date := to_date('2', 'J');
18 null_number number := -999;
19 null_raw raw(16) := '9999999999999999';
20
21 /* bug 4318754, 4352995 SSO related changes */
22 USER_OK_CREATE constant pls_integer := 0;
23 USER_INVALID_NAME constant pls_integer := 1;
24 USER_EXISTS_IN_FND constant pls_integer := 2;
25 USER_SYNCHED constant pls_integer := 3;
26 USER_EXISTS_NO_LINK_ALLOWED constant pls_integer := 4;
27
28 /* bug 5027812 */
29 CHANGE_SOURCE_OID constant pls_integer := 1;
30 --
31 -- LOAD_ROW (PRIVATE)
32 -- Overloaded version for backward compatibility only.
33 -- Use version below.
34 --
35 procedure LOAD_ROW (
36 X_USER_NAME in VARCHAR2,
37 X_OWNER in VARCHAR2,
38 X_ENCRYPTED_USER_PASSWORD in VARCHAR2,
39 X_SESSION_NUMBER in VARCHAR2,
40 X_START_DATE in VARCHAR2,
41 X_END_DATE in VARCHAR2,
42 X_LAST_LOGON_DATE in VARCHAR2,
43 X_DESCRIPTION in VARCHAR2,
44 X_PASSWORD_DATE in VARCHAR2,
45 X_PASSWORD_ACCESSES_LEFT in VARCHAR2,
46 X_PASSWORD_LIFESPAN_ACCESSES in VARCHAR2,
47 X_PASSWORD_LIFESPAN_DAYS in VARCHAR2,
48 X_EMAIL_ADDRESS in VARCHAR2,
49 X_FAX in VARCHAR2 );
50
51 ----------------------------------------------------------------------
52 --
53 -- LOAD_ROW (PRIVATE)
54 -- Insert/update a new row of data.
55 -- Only for use by FNDLOAD, other apis should use LoadUser below.
56 --
57 /*#
58 * Creates or updates Application's User data as appropriate.
59 * @param x_user_name User Name
60 * @param x_owner Owner Name
61 * @param x_encrypted_user_password Encrypted Password for Authentication
62 * @param x_session_number Session ID
63 * @param x_start_date User Effective Start Date
64 * @param x_end_date User Effective End Date
65 * @param x_last_logon_date User Last Login Date
66 * @param x_description User Description
67 * @param x_password_date Password Creation Date
68 * @param x_password_accesses_left Number of Login Accesses left (From Current Day) for Password Expiry
69 * @param x_password_lifespan_accesses Number of Login Accesses (From Password Creation Day) after which Password Expires
70 * @param x_password_lifespan_days Number of days after which Password Expires
71 * @param x_email_address User Email Address
72 * @param x_fax Fax Number
73 * @param x_custom_mode Custom Mode
74 * @param x_last_update_date Insert/Update Date
75 * @param x_person_party_name Person Party Name
76 * @rep:scope public
77 * @rep:lifecycle active
78 * @rep:displayname Create/Update User
79 * @rep:compatibility S
80 * @rep:ihelp FND/@dev_p_funcworks#dev_p_funcworks See the related online help
81 */
82 procedure LOAD_ROW (
83 X_USER_NAME in VARCHAR2,
84 X_OWNER in VARCHAR2,
85 X_ENCRYPTED_USER_PASSWORD in VARCHAR2,
86 X_SESSION_NUMBER in VARCHAR2,
87 X_START_DATE in VARCHAR2,
88 X_END_DATE in VARCHAR2,
89 X_LAST_LOGON_DATE in VARCHAR2,
90 X_DESCRIPTION in VARCHAR2,
91 X_PASSWORD_DATE in VARCHAR2,
92 X_PASSWORD_ACCESSES_LEFT in VARCHAR2,
93 X_PASSWORD_LIFESPAN_ACCESSES in VARCHAR2,
94 X_PASSWORD_LIFESPAN_DAYS in VARCHAR2,
95 X_EMAIL_ADDRESS in VARCHAR2,
96 X_FAX in VARCHAR2,
97 X_CUSTOM_MODE in VARCHAR2,
98 X_LAST_UPDATE_DATE in VARCHAR2,
99 X_PERSON_PARTY_NAME in VARCHAR2 default NULL);
100
101 ----------------------------------------------------------------------
102 --
103 -- CreateUserId (PUBLIC)
104 -- Insert new user record into FND_USER table.
105 -- If that user exists already, exception raised with the error message.
106 -- There are three input arguments must be provided. All the other columns
107 -- in FND_USER table can take the default value.
108 --
109 -- *** NOTE: This version accepts the old customer_id/employee_id
110 -- keys foreign keys to the "person". Use CreateUserIdParty to create
111 -- a user with the new person_party_id key.
112 --
113 -- Input (Mandatory)
114 -- x_user_name: The name of the new user
115 -- x_owner: 'SEED' or 'CUST'(customer)
116 -- x_unencrypted_password: The password for this new user
117 -- Returns
118 -- User_id of created user
119 --
120 function CreateUserId (
121 x_user_name in varchar2,
122 x_owner in varchar2,
123 x_unencrypted_password in varchar2 default null,
124 x_session_number in number default 0,
125 x_start_date in date default sysdate,
126 x_end_date in date default null,
127 x_last_logon_date in date default null,
128 x_description in varchar2 default null,
129 x_password_date in date default null,
130 x_password_accesses_left in number default null,
131 x_password_lifespan_accesses in number default null,
132 x_password_lifespan_days in number default null,
133 x_employee_id in number default null,
134 x_email_address in varchar2 default null,
135 x_fax in varchar2 default null,
136 x_customer_id in number default null,
137 x_supplier_id in number default null)
138 return number;
139
140 ----------------------------------------------------------------------
141 --
142 -- CreateUserIdParty (PUBLIC)
143 -- Insert new user record into FND_USER table.
144 -- If that user exists already, exception raised with the error message.
145 -- There are three input arguments must be provided. All the other columns
146 -- in FND_USER table can take the default value.
147 --
148 -- *** NOTE: This version accepts the new person_party_id foreign key
149 -- to the "person". Use CreateUserId to create a user with the old
150 -- customer_id/employee_id keys.
151 --
152 -- Input (Mandatory)
153 -- x_user_name: The name of the new user
154 -- x_owner: 'SEED' or 'CUST'(customer)
155 -- x_unencrypted_password: The password for this new user
156 -- Returns
157 -- User_id of created user
158 --
159 function CreateUserIdParty (
160 x_user_name in varchar2,
161 x_owner in varchar2,
162 x_unencrypted_password in varchar2 default null,
163 x_session_number in number default 0,
164 x_start_date in date default sysdate,
165 x_end_date in date default null,
166 x_last_logon_date in date default null,
167 x_description in varchar2 default null,
168 x_password_date in date default null,
169 x_password_accesses_left in number default null,
170 x_password_lifespan_accesses in number default null,
171 x_password_lifespan_days in number default null,
172 x_email_address in varchar2 default null,
173 x_fax in varchar2 default null,
174 x_person_party_id in number default null)
175 return number;
176
177 ----------------------------------------------------------------------
178 --
179 -- CreateUser (PUBLIC)
180 -- Insert new user record into FND_USER table.
181 -- If that user exists already, exception raised with the error message.
182 -- There are three input arguments must be provided. All the other columns
183 -- in FND_USER table can take the default value.
184 --
185 -- *** NOTE: This version accepts the old customer_id/employee_id
186 -- keys foreign keys to the "person". Use CreateUserParty to create
187 -- a user with the new person_party_id key.
188 --
189 -- Input (Mandatory)
190 -- x_user_name: The name of the new user
191 -- x_owner: 'SEED' or 'CUST'(customer)
192 -- x_unencrypted_password: The password for this new user
193 --
194 procedure CreateUser (
195 x_user_name in varchar2,
196 x_owner in varchar2,
197 x_unencrypted_password in varchar2 default null,
198 x_session_number in number default 0,
199 x_start_date in date default sysdate,
200 x_end_date in date default null,
201 x_last_logon_date in date default null,
202 x_description in varchar2 default null,
203 x_password_date in date default null,
204 x_password_accesses_left in number default null,
205 x_password_lifespan_accesses in number default null,
206 x_password_lifespan_days in number default null,
207 x_employee_id in number default null,
208 x_email_address in varchar2 default null,
209 x_fax in varchar2 default null,
210 x_customer_id in number default null,
211 x_supplier_id in number default null);
212
213 ----------------------------------------------------------------------
214 --
215 -- CreateUserParty (PUBLIC)
216 -- Insert new user record into FND_USER table.
217 -- If that user exists already, exception raised with the error message.
218 -- There are three input arguments must be provided. All the other columns
219 -- in FND_USER table can take the default value.
220 --
221 -- *** NOTE: This version accepts the new person_party_id foreign key
222 -- to the "person". Use CreateUser to create a user with the old
223 -- customer_id/employee_id keys.
224 --
225 -- Input (Mandatory)
226 -- x_user_name: The name of the new user
227 -- x_owner: 'SEED' or 'CUST'(customer)
228 -- x_unencrypted_password: The password for this new user
229 --
230 procedure CreateUserParty (
231 x_user_name in varchar2,
232 x_owner in varchar2,
233 x_unencrypted_password in varchar2 default null,
234 x_session_number in number default 0,
235 x_start_date in date default sysdate,
236 x_end_date in date default null,
237 x_last_logon_date in date default null,
238 x_description in varchar2 default null,
239 x_password_date in date default null,
240 x_password_accesses_left in number default null,
241 x_password_lifespan_accesses in number default null,
242 x_password_lifespan_days in number default null,
243 x_email_address in varchar2 default null,
244 x_fax in varchar2 default null,
245 x_person_party_id in number default null);
246
247 ----------------------------------------------------------------------
248 --
249 -- UpdateUser (Public)
250 -- Update any column for a particular user record. If that user does
251 -- not exist, exception raised with error message.
252 -- You can use this procedure to update a user's password for example.
253 --
254 -- *** NOTE: This version accepts the old customer_id/employee_id
255 -- keys foreign keys to the "person". Use UpdateUserParty to update
256 -- a user with the new person_party_id key.
257 --
258 -- Usage Example in pl/sql
259 -- begin fnd_user_pkg.updateuser('SCOTT', 'SEED', 'DRAGON'); end;
260 --
261 -- Mandatory Input Arguments
262 -- x_user_name: An existing user name
263 -- x_owner: 'SEED' or 'CUST'(customer)
264 --
265 procedure UpdateUser (
266 x_user_name in varchar2,
267 x_owner in varchar2,
268 x_unencrypted_password in varchar2 default null,
269 x_session_number in number default null,
270 x_start_date in date default null,
271 x_end_date in date default null,
272 x_last_logon_date in date default null,
273 x_description in varchar2 default null,
274 x_password_date in date default null,
275 x_password_accesses_left in number default null,
276 x_password_lifespan_accesses in number default null,
277 x_password_lifespan_days in number default null,
278 x_employee_id in number default null,
279 x_email_address in varchar2 default null,
280 x_fax in varchar2 default null,
281 x_customer_id in number default null,
282 x_supplier_id in number default null,
283 x_old_password in varchar2 default null);
284
285 ----------------------------------------------------------------------
286 --
287 -- UpdateUserParty (Public)
288 -- Update any column for a particular user record. If that user does
289 -- not exist, exception raised with error message.
290 -- You can use this procedure to update a user's password for example.
291 --
292 -- *** NOTE: This version accepts the new person_party_id foreign key
293 -- to the "person". Use UpdateUser to update a user with the old
294 -- customer_id/employee_id keys.
295 --
296 -- Usage Example in pl/sql
297 -- begin fnd_user_pkg.updateuser('SCOTT', 'SEED', 'DRAGON'); end;
298 --
299 -- Mandatory Input Arguments
300 -- x_user_name: An existing user name
301 -- x_owner: 'SEED' or 'CUST'(customer)
302 --
303 procedure UpdateUserParty (
304 x_user_name in varchar2,
305 x_owner in varchar2,
306 x_unencrypted_password in varchar2 default null,
307 x_session_number in number default null,
308 x_start_date in date default null,
309 x_end_date in date default null,
310 x_last_logon_date in date default null,
311 x_description in varchar2 default null,
312 x_password_date in date default null,
313 x_password_accesses_left in number default null,
314 x_password_lifespan_accesses in number default null,
315 x_password_lifespan_days in number default null,
316 x_email_address in varchar2 default null,
317 x_fax in varchar2 default null,
318 x_person_party_id in number default null,
319 x_old_password in varchar2 default null);
320
321 ----------------------------------------------------------------------------
322 --
323 -- LoadUser (Public)
324 -- Create or Update user, as appropriate.
325 --
326 -- *** NOTE: This version accepts the old customer_id/employee_id
327 -- keys foreign keys to the "person". Use LoadUserParty to load
328 -- a user with the new person_party_id key.
329 --
330 procedure LoadUser(
331 x_user_name in varchar2,
332 x_owner in varchar2,
333 x_unencrypted_password in varchar2 default null,
334 x_session_number in number default null,
335 x_start_date in date default null,
336 x_end_date in date default null,
337 x_last_logon_date in date default null,
338 x_description in varchar2 default null,
339 x_password_date in date default null,
340 x_password_accesses_left in number default null,
341 x_password_lifespan_accesses in number default null,
342 x_password_lifespan_days in number default null,
343 x_employee_id in number default null,
344 x_email_address in varchar2 default null,
345 x_fax in varchar2 default null,
346 x_customer_id in number default null,
347 x_supplier_id in number default null);
348
349 ----------------------------------------------------------------------------
350 --
351 -- LoadUserParty (Public)
352 -- Create or Update user, as appropriate.
353 --
354 -- *** NOTE: This version accepts the new person_party_id foreign key
355 -- to the "person". Use LoadUser to load a user with the old
356 -- customer_id/employee_id keys.
357 --
358 procedure LoadUserParty(
359 x_user_name in varchar2,
360 x_owner in varchar2,
361 x_unencrypted_password in varchar2 default null,
362 x_session_number in number default null,
363 x_start_date in date default null,
364 x_end_date in date default null,
365 x_last_logon_date in date default null,
366 x_description in varchar2 default null,
367 x_password_date in date default null,
368 x_password_accesses_left in number default null,
369 x_password_lifespan_accesses in number default null,
370 x_password_lifespan_days in number default null,
371 x_email_address in varchar2 default null,
372 x_fax in varchar2 default null,
373 x_person_party_id in number default null);
374
375 ----------------------------------------------------------------------------
376 --
377 -- DisableUser (PUBLIC)
378 -- Sets end_date to sysdate for a given user. This is to terminate that user.
379 -- You longer can log in as this user anymore. If username is not valid,
380 -- exception raised with error message.
381 --
382 -- Usage example in pl/sql
383 -- begin fnd_user_pkg.disableuser('SCOTT'); end;
384 --
385 -- Input (Mandatory)
386 -- username: User Name
387 --
388 procedure DisableUser(username varchar2);
389 ----------------------------------------------------------------------------
390 --
391 -- ValidateLogin (PUBLIC)
392 -- Test if password is good for this given user.
393 --
394 -- Usage example in pl/sql
395 -- begin fnd_user_pkg.validatelogin('SCOTT', 'TIGER'); end;
396 --
397 -- Input (Mandatory)
398 -- username: User Name
399 -- password: User Password
400 --
401 function ValidateLogin(username varchar2,
402 password varchar2) return boolean;
403 ----------------------------------------------------------------------------
404 --
405 -- ChangePassword (PUBLIC)
406 -- Set new password for a given user without having to provide
407 -- the old password.
408 --
409 -- Usage example in pl/sql
410 -- begin fnd_user_pkg.changepassword('SCOTT', 'WELCOME'); end;
411 --
412 -- Input (Mandatory)
413 -- username: User Name
414 -- newpassword New Password
415 --
416 function ChangePassword(username varchar2,
417 newpassword varchar2) return boolean;
418 ----------------------------------------------------------------------------
419 --
420 -- ChangePassword (PUBLIC)
421 -- Set new password for a given user if the existing password needed to be
422 -- validated before changing to the new password.
423 --
424 -- Usage example in pl/sql
425 -- begin fnd_user_pkg.changepassword('SCOTT', 'TIGER', 'WELCOME'); end;
426 --
427 -- Input (Mandatory)
428 -- username: User Name
429 -- oldpassword Old Password
430 -- newpassword New Password
431 --
432 function ChangePassword(username varchar2,
433 oldpassword varchar2,
434 newpassword varchar2) return boolean;
435 ----------------------------------------------------------------------------
436 --
437 -- GetReEncryptedPassword (PUBLIC)
438 -- Return user password encrypted with new key. This just returns the
439 -- newly encrypted password. It does not set the password in FND_USER table.
443 -- newpass varchar2(100);
440 --
441 -- Usage example in pl/sql
442 -- declare
444 -- begin
445 -- newpass := fnd_user_pkg.getreencryptedpassword('SCOTT', 'NEWKEY'); end;
446 -- end;
447 --
448 -- Input (Mandatory)
449 -- username: User Name
450 -- newkey New Key
451 --
452 function GetReEncryptedPassword(username varchar2,
453 newkey varchar2) return varchar2;
454
455 ----------------------------------------------------------------------------
456 -- SetReEncryptedPassword (PUBLIC)
457 -- Set user password from value returned from GetReEncryptedPassword.
458 -- This is to update column ENCRYPTED_USER_PASSWORD in table FND_USER
459 --
460 -- Usage example in pl/sql
461 -- declare
462 -- newpass varchar2(100);
463 -- begin
464 -- newpass := fnd_user_pkg.getreencryptedpassword('SCOTT', 'NEWKEY'); end;
465 -- fnd_user_pkg.setreencryptedpassword('SCOTT', newpass, 'NEWKEY'); end;
466 -- end;
467 --
468 -- Input (Mandatory)
469 -- username: User Name
470 -- reencpwd: Reencrypted Password
471 -- newkey New Key
472 --
473 function SetReEncryptedPassword(username varchar2,
474 reencpwd varchar2,
475 newkey varchar2) return boolean;
476 ----------------------------------------------------------------------------
477 -- MergeCustomer (PUBLIC)
478 -- This is the procedure being called during the Party Merge.
479 -- FND_USER.MergeCustomer() has been registered in Party Merge Data Dict.
480 -- The input/output arguments format matches the document PartyMergeDD.doc.
481 -- The goal is to fix the customer id in fnd_user table to point to the
482 -- same party when two similar parties are begin merged.
483 --
484 -- Usage example in pl/sql
485 -- This procedure should only be called from the PartyMerge utility.
486 --
487 procedure MergeCustomer(p_entity_name in varchar2,
488 p_from_id in number,
489 p_to_id in out nocopy number,
490 p_from_fk_id in number,
491 p_to_fk_id in number,
492 p_parent_entity_name in varchar2,
493 p_batch_id in number,
494 p_batch_party_id in number,
495 p_return_status in out nocopy varchar2);
496 --------------------------------------------------------------------------
497 /*
498 ** user_change - The rule function for FND's subscription on the
499 ** oracle.apps.wf.entmgr.user.change event. This function
500 ** retrieves the user's information and updates the
501 ** corresponding fnd_user as needed, if the user exists.
502 */
503 FUNCTION user_change(p_subscription_guid in raw,
504 p_event in out nocopy wf_event_t)
505 return varchar2;
506 --------------------------------------------------------------------------
507 /*
508 ** user_create_rf - The rule function for FND's 2nd subscription on the
509 ** oracle.apps.wf.entmgr.user.change event. This function
510 ** retrieves the user's information and creates the
511 ** corresponding fnd_user if the user does not already exist.
512 */
513 FUNCTION user_create_rf(p_subscription_guid in raw,
514 p_event in out nocopy wf_event_t)
515 return varchar2;
516 --------------------------------------------------------------------------
517 /*
518 ** user_synch - The centralized routine for communicating user changes
519 ** with wf and entity mgr.
520 */
521 PROCEDURE user_synch(p_user_name in varchar2);
522
523 --------------------------------------------------------------------------
524 --
525 -- DelResp (PUBLIC)
526 -- Detach a responsibility which is currently attached to this given user.
527 -- If any of the username or application short name or responsibility key or
528 -- security group is not valid, exception raised with error message.
529 --
530 -- Usage example in pl/sql
531 -- begin fnd_user_pkg.delresp('SCOTT', 'FND', 'APPLICATION_DEVELOPER',
532 -- 'STANDARD'); end;
533 -- Input (Mandatory)
534 -- username: User Name
535 -- resp_app: Application Short Name
536 -- resp_key: Responsibility Key
537 -- security_group: Security Group Key
538 --
539 procedure DelResp(username varchar2,
540 resp_app varchar2,
541 resp_key varchar2,
542 security_group varchar2);
543 --------------------------------------------------------------------------
544 --
545 -- AddResp (PUBLIC)
546 -- For a given user, attach a valid responsibility.
547 -- If user name or application short name or responsbility key name
548 -- or security group key is not valid, exception raised with error message.
549 --
550 -- Usage example in pl/sql
551 -- begin fnd_user_pkg.addresp('SCOTT', 'FND', 'APPLICATION_DEVELOPER',
552 -- 'STANDARD', 'DESCRIPTION', sysdate, null); end;
553 -- Input (Mandatory)
554 -- username: User Name
555 -- resp_app: Application Short Name
556 -- resp_key: Responsibility Key
557 -- security_group: Security Group Key
561 --
558 -- description: Description
559 -- start_date: Start Date
560 -- end_date: End Date
562 procedure AddResp(username varchar2,
563 resp_app varchar2,
564 resp_key varchar2,
565 security_group varchar2,
566 description varchar2,
567 start_date date,
568 end_date date);
569
570 -------------------------------------------------------------------
571 -- Name: isPasswordChangeable
572 -- Description: Checks if user us externally authenticatied
573 ----------------------------------------------------------------------
574 Function isPasswordChangeable (p_user_name in varchar2)
575 return boolean;
576
577 -------------------------------------------------------------------
578 -- Name: UpdatePassword_WF
579 -- Description: Calls FND_USER_PKG.UpdateUser
580 -------------------------------------------------------------------
581 Procedure UpdatePassword_WF(itemtype in varchar2,
582 itemkey in varchar2,
583 actid in number,
584 funcmode in varchar2,
585 resultout in out nocopy varchar2);
586 ----------------------------------------------------------------------------
587
588 --
589 -- DERIVE_PERSON_PARTY_ID
590 -- Derive the person_party_id, given a customer_id and employee_id
591 -- IN
592 -- customer_id
593 -- employee_id
594 -- RETURNS
595 -- person_party_id
596 --
597 /*#
598 * This is called by user form and other packages. This function
599 * validates the person party and returns the person party id
600 * attached to the user.
601 * @param user_name in varchar2 Username
602 * @param customer_id in number Customer Id
603 * @param employee_id in number Employee Id
604 * @param log_exception in varchar2 Parameter which controls whether exception needs to be raised or not. When passed 'Y', the function exits by raising an exception. When passed 'N', the function exits quitely returning null.
605 * @return person party id
606 * @rep:scope public
607 * @rep:lifecycle active
608 * @rep:displayname Derive Person Party Id
609 * @rep:compatibility S
610 */
611 Function DERIVE_PERSON_PARTY_ID(
612 user_name in varchar2,
613 customer_id in number,
614 employee_id in number,
615 log_exception in varchar2 default 'Y')
616 return number;
617
618 --
619 -- DERIVE_CUSTOMER_EMPLOYEE_ID
620 -- Update customer and employee ids if person_party_id is changed
621 -- IN
622 -- person_party_id
623 -- OUT
624 -- customer_id
625 -- employee_id
626 --
627 Procedure DERIVE_CUSTOMER_EMPLOYEE_ID(
628 user_name in varchar2,
629 person_party_id in number,
630 customer_id out nocopy number,
631 employee_id out nocopy number);
632
633 ----------------------------------------------------------------------------
634 --
635 -- EnableUser (PUBLIC)
636 -- Sets the start_date and end_date as requested. By default, the
637 -- start_date will be set to sysdate and end_date to null.
638 -- This is to enable that user.
639 -- You can log in as this user from now.
640 -- If username is not valid, exception raised with error message.
641 --
642 -- Usage example in pl/sql
643 -- begin fnd_user_pkg.enableuser('SCOTT'); end;
644 -- begin fnd_user_pkg.enableuser('SCOTT', sysdate+1, sysdate+30); end;
645 --
646 -- Input (Mandatory)
647 -- username: User Name
648 -- Input (Non-Mandatory)
649 -- start_date: Start Date
650 -- end_date: End Date
651 --
652 procedure EnableUser(username varchar2,
653 start_date date default sysdate,
654 end_date date default fnd_user_pkg.null_date);
655
656 ----------------------------------------------------------------------------
657 --
658 -- CreatePendingUser (PUBLIC)
659 -- Create a user whose start_date and end_date = FND_API.G_MISS_DATE as
660 -- a pending user.
661 -- Pending user is created when a user registers a user account through
662 -- UMX with an aproval process.
663 -- USER_ID is returned.
664 --
665 --
666 -- Usage example in pl/sql
667 -- begin uid := fnd_user_pkg.creatependinguser('SCOTT', 'SEED', 'welcome');
668 -- end;
669 -- begin uid := fnd_user_pkg.creatependinguser('SCOTT', 'SEED'); end;
670 --
671 -- Input (Mandatory)
672 -- x_user_name: User Name
673 -- x_owner: 'SEED' or 'CUST'(customer)
674 -- Output
675 -- user_id
676 --
677 function CreatePendingUser(
678 x_user_name in varchar2,
679 x_owner in varchar2,
680 x_unencrypted_password in varchar2 default null,
681 x_session_number in number default 0,
682 x_description in varchar2 default null,
683 x_password_date in date default null,
684 x_password_accesses_left in number default null,
685 x_password_lifespan_accesses in number default null,
686 x_password_lifespan_days in number default null,
687 x_email_address in varchar2 default null,
688 x_fax in varchar2 default null,
692 --
689 x_person_party_id in number default null) return number;
690
691 ----------------------------------------------------------------------------
693 -- RemovePendingUser (PUBLIC)
694 -- Delete this user from fnd_user table only if this is a pending user.
695 -- If this is not a valid username or is not a pending user, raise error.
696 -- Pending user is created when a user registers a user account through
697 -- UMX with an aproval process.
698 --
699 -- Usage example in pl/sql
700 -- begin fnd_user_pkg.removependinguser('SCOTT'); end;
701 --
702 -- Input (Mandatory)
703 -- username: User Name
704 --
705 procedure RemovePendingUser(username varchar2);
706
707 ----------------------------------------------------------------------------
708 --
709 -- AssignPartyToUser (PUBLIC)
710 -- Assign a TCA party to a given user
711 --
712 -- Usage example in pl/sql
713 -- begin fnd_user_pkg.assignpartytouser('SCOTT', 1001); end;
714 --
715 -- Input (Mandatory)
716 -- x_user_name: User Name
717 -- x_party_id: Party Name Id
718 --
719 procedure AssignPartyToUser(
720 x_user_name in varchar2,
721 x_party_id in number);
722
723 -- begin bug 2504562
724
725 ----------------------------------------------------------------------------
726 --
727 -- Usage example in pl/sql
728 -- begin fnd_user_pkg.change_user_name('SOCTT', 'SCOTT'); end;
729 --
730 -- Input (Mandantory)
731 -- x_old_user_name: Old User Name
732 -- x_new_user_name: New User Name
733 -- x_change_source: Change Source
734 -- null means we need to synch change to oid
735 -- CHANGE_SOURCE_OID means we don't need to synch back to oid anymore
736 -- X_CHANGE_SOURCE is private argument only used by ldap routine.
737 --
738 /*#
739 * This api changes username, deals with encryption changes and
740 * update foreign keysthat were using the old username.
741 * PLEASE NOTE THAT x_change_source IS PRIVATE ARGUMENT ONLY USED BY SSO!!!
742 * @param x_old_user_name in varchar2 The Old user name
743 * @param x_new_user_name in varchar2 The New user name
744 * @param x_change_source in number (default null).
745 * @rep:scope public
746 * @rep:lifecycle active
747 * @rep:displayname Change User Name
748 * @rep:compatibility S
749 * @rep:ihelp FND/@mesgdict#mesgdict See the related online help
750 */
751 procedure change_user_name(
752 x_old_user_name in varchar2,
753 x_new_user_name in varchar2,
754 x_change_source in number default null);
755
756
757 ----------------------------------------------------------------------------
758 --
759 -- set_old_user_name (PUBLIC)
760 -- This function is called from Forms to set the global variable,
761 -- fnd_user_pkg/g_old_user_name since this cannot be set directly from Forms.
762 -- This function returns a number which can be used to check for success
763 -- from Forms.
764 --
765 -- Usage example in pl/sql
766 -- declare
767 -- retval number := null;
768 -- begin retval := fnd_user_pkg.set_old_user_name('SOCTT'); end;
769 --
770 -- Input (Mandantory)
771 -- x_old_user_name: Old User Name
772 --
773 function set_old_user_name(x_old_user_name in varchar2) return number;
774
775 -- end bug 2504562
776
777 ----------------------------------------------------------------------------
778 -- MergePartyId (PUBLIC)
779 -- This is the procedure being called during the Party Merge.
780 -- FND_USER.MergePartyId() has been registered in Party Merge Data Dict.
781 -- The input/output arguments format matches the document PartyMergeDD.doc.
782 -- The goal is to fix the party id in fnd_user table to point to the
783 -- same party when two similar parties are begin merged.
784 --
785 -- Usage example in pl/sql
786 -- This procedure should only be called from the PartyMerge utility.
787 --
788 procedure MergePartyId(p_entity_name in varchar2,
789 p_from_id in number,
790 p_to_id in out nocopy number,
791 p_from_fk_id in number,
792 p_to_fk_id in number,
793 p_parent_entity_name in varchar2,
794 p_batch_id in number,
795 p_batch_party_id in number,
796 p_return_status in out nocopy varchar2);
797
798 -- Validate_User_Name (PUBLIC)
799 -- Make sure that input argument x_user_name doesn't contain invalid character.
800 -- For now: We only care about '/' and ':' because they are known problem.
801 -- 01/19/05: we now have more invalid characters info from bug 4116239, so
802 -- I am adding more characters.
803 -- Rewrite later: checking for any non-printable character.
804 -- make sure multibyte character is ok.
805 /*#
806 * This is called by user form and the fnd_user_pkg. In both places
807 * we need to validate whether an username is in a valid format.
808 * @param x_user_name in varchar2 Username
809 * @rep:scope public
810 * @rep:lifecycle active
811 * @rep:displayname Validate User Name
812 * @rep:compatibility S
813 * @rep:ihelp FND/@mesgdict#mesgdict See the related online help
814 */
815 procedure validate_user_name(x_user_name in varchar2);
816
820 --
817 ----------------------------------------------------------------------
818 --
819 -- CreateUser (PUBLIC)
821 -- Bug#3904339 - SSO: Add user_guid parameter in fnd_user_pkg apis
822 -- Overloaded procedure to create user
823 -- Accepts User GUID as a parameter in addition to the other parameters
824 --
825 procedure CreateUser (
826 x_user_name in varchar2,
827 x_owner in varchar2,
828 x_unencrypted_password in varchar2 default null,
829 x_session_number in number default 0,
830 x_start_date in date default sysdate,
831 x_end_date in date default null,
832 x_last_logon_date in date default null,
833 x_description in varchar2 default null,
834 x_password_date in date default null,
835 x_password_accesses_left in number default null,
836 x_password_lifespan_accesses in number default null,
837 x_password_lifespan_days in number default null,
838 x_employee_id in number default null,
839 x_email_address in varchar2 default null,
840 x_fax in varchar2 default null,
841 x_customer_id in number default null,
842 x_supplier_id in number default null,
843 x_user_guid in raw,
844 x_change_source in number default null);
845
846 ----------------------------------------------------------------------
847 --
848 -- CreateUserId (PUBLIC)
849 --
850 -- Bug#3904339 - SSO: Add user_guid parameter in fnd_user_pkg apis
851 -- Overloaded function to create user
852 -- Accepts User GUID as a parameter in addition to the other parameters
853 -- Returns
854 -- User_id of created user
855 --
856 function CreateUserId (
857 x_user_name in varchar2,
858 x_owner in varchar2,
859 x_unencrypted_password in varchar2 default null,
860 x_session_number in number default 0,
861 x_start_date in date default sysdate,
862 x_end_date in date default null,
863 x_last_logon_date in date default null,
864 x_description in varchar2 default null,
865 x_password_date in date default null,
866 x_password_accesses_left in number default null,
867 x_password_lifespan_accesses in number default null,
868 x_password_lifespan_days in number default null,
869 x_employee_id in number default null,
870 x_email_address in varchar2 default null,
871 x_fax in varchar2 default null,
872 x_customer_id in number default null,
873 x_supplier_id in number default null,
874 x_user_guid in raw,
875 x_change_source in number default null)
876 return number;
877
878 ----------------------------------------------------------------------
879 --
880 -- UpdateUser (Public)
881 --
882 -- Bug#3904339 - SSO: Add user_guid parameter in fnd_user_pkg apis
883 -- Overloaded procedure to update user
884 -- Accepts User GUID in addition to the other parameters
885 --
886
887 procedure UpdateUser (
888 x_user_name in varchar2,
889 x_owner in varchar2,
890 x_unencrypted_password in varchar2 default null,
891 x_session_number in number default null,
892 x_start_date in date default null,
893 x_end_date in date default null,
894 x_last_logon_date in date default null,
895 x_description in varchar2 default null,
896 x_password_date in date default null,
897 x_password_accesses_left in number default null,
898 x_password_lifespan_accesses in number default null,
899 x_password_lifespan_days in number default null,
900 x_employee_id in number default null,
901 x_email_address in varchar2 default null,
902 x_fax in varchar2 default null,
903 x_customer_id in number default null,
904 x_supplier_id in number default null,
905 x_old_password in varchar2 default null,
906 x_user_guid in raw,
907 x_change_source in number default null);
908
909 ----------------------------------------------------------------------
910 -- userExists (Public)
911 --
912 -- This function checks if the user exists and returns 'True' or 'False'
913 -- Input (Mandatory)
914 -- username: User Name
915
916 function userExists (x_user_name in varchar2) return boolean;
917
918 ----------------------------------------------------------------------------
919 --
920 -- TestUserName (PUBLIC)
921 -- This api test whether a username exists in FND and/or in OID.
922 --
923 -- Usage example in pl/sql
924 -- declare ret number;
925 -- begin ret := fnd_user_pkg.testusername('SOCTT'); end;
926 --
927 -- Input (Mandantory)
928 -- x_user_name: User Name that you want to test
929 --
930 -- Output
931 -- USER_OK_CREATE : User does not exist in either FND or OID
932 -- USER_INVALID_NAME : User name is not valid
933 -- USER_EXISTS_IN_FND : User exists in FND
934 -- USER_SYNCHED : User exists in OID and next time when this user gets
935 -- created in FND, the two will be synched together.
939 * Check a user name exists in FND and (or) in OID.
936 -- USER_EXISTS_NO_LINK_ALLOWED: User exists in OID and no synching allowed.
937 --
938 /*#
940 * @param x_user_name The username to be tested
941 * @return The User Existence Status Code
942 * @rep:scope public
943 * @rep:lifecycle active
944 * @rep:displayname Test User Name
945 * @rep:compatibility S
946 * @rep:ihelp FND/@mesgdict#mesgdict See the related online help
947 */
948 function TestUserName(x_user_name in varchar2) return pls_integer;
949
950 ----------------------------------------------------------------------------
951 --
952 -- set_old_person_party_id (PUBLIC)
953 -- This function is called from Forms to set the global variable,
954 -- g_old_person_party_id since this cannot be set directly from Forms.
955 -- This function returns a number which can be used to check for success
956 -- from Forms.
957 --
958 -- Usage example in pl/sql
959 -- declare
960 -- retval number := null;
961 -- begin retval := fnd_user_pkg.set_old_person_party_id(12345); end;
962 --
963 -- Input (Mandantory)
964 -- x_old_person_party_id: Old Person Party Id
965 --
966 /*#
967 * This function is called from Forms and other serverside code to set
968 * the global variable g_old_person_party_id.
969 * Even this is a public function but is for INTERNAL usage.
970 * @param x_old_person_party_id The old person party id for a FND user
971 * @return 1: Success or 0: Failure
972 * @rep:scope public
973 * @rep:lifecycle active
974 * @rep:displayname Set Old Person Party Id
975 * @rep:compatibility S
976 * @rep:ihelp FND/@mesgdict#mesgdict See the related online help
977 */
978 function set_old_person_party_id(x_old_person_party_id in varchar2)
979 return number;
980
981 ----------------------------------------------------------------------------
982 --
983 -- set_old_user_guid (PUBLIC)
984 -- This function sets the global variable g_old_user_guid.
985 -- This function returns a number which can be used to check for success
986 -- from Forms.
987 --
988 -- Usage example in pl/sql
989 -- declare
990 -- retval number := null;
991 -- guid raw(16);
992 -- begin
993 -- guid := 'F9374D4B80AB1A86E034080020B2612C';
994 -- retval := fnd_user_pkg.set_old_user_guid(guid); end;
995 --
996 -- Input (Mandantory)
997 -- x_old_user_guid: Old USER GUID
998 --
999 /*#
1000 * This function sets the global variable g_old_user_guid.
1001 * Even this is a public function but is for INTERNAL usage.
1002 * @param x_old_user_guid The old user guid for a FND user
1003 * @return 1: Success or 0: Failure
1004 * @rep:scope public
1005 * @rep:lifecycle active
1006 * @rep:displayname Set Old User GUID
1007 * @rep:compatibility S
1008 * @rep:ihelp FND/@mesgdict#mesgdict See the related online help
1009 */
1010 function set_old_user_guid(x_old_user_guid in raw) return number;
1011
1012
1016 -- This is called by the fnd_user_pkg and fnd_web_sec.
1013 ----------------------------------------------------------------------------
1014 --
1015 -- ldap_wrapper_update_user (PUBLIC)
1017 -- It serves as a helper routine to call fnd_ldap_wrapper.update_user
1018 -- when we need to synch the user update to OID.
1019 -- Note
1020 -- Please note that even this is public procedure, it does not mean for
1021 -- other public usage. This is mainly created as a helper routine to
1022 -- service the user form and the user package.
1023 /*#
1024 * This is called by the fnd_user_pkg and fnd_web_sec.
1025 * It serves as a helper routine to call fnd_ldap_wrapper.update_user
1026 * when we need to synch the user update to OID.
1027 * Please note that even this is public procedure, it does not mean for
1028 * other public usage. This is mainly created as a helper routine to
1029 * service the user form and the user package.
1030 * @param x_user_name in varchar2 The user name
1031 * @param x_unencrypted_password in varchar2 The unencrypted user password
1032 * @param x_start_date in date Start date
1033 * @param x_end_date in date End date
1034 * @param x_description in varchar2 Description
1035 * @param x_email_address in varchar2 Email address
1036 * @param x_fax in varchar2 Fax
1037 * @param x_expire_pwd in boolean Inform LDAP/OID whether to expire the pwd
1038 * @rep:scope public
1039 * @rep:lifecycle active
1040 * @rep:displayname LDAP Wrapper Update User
1041 * @rep:compatibility S
1042 * @rep:ihelp FND/@mesgdict#mesgdict See the related online help
1043 */
1044 procedure ldap_wrapper_update_user(x_user_name in varchar2,
1045 x_unencrypted_password in varchar2,
1046 x_start_date in date,
1047 x_end_date in date,
1048 x_description in varchar2,
1049 x_email_address in varchar2,
1050 x_fax in varchar2,
1051 x_expire_pwd in pls_integer default 0);
1052
1053 ----------------------------------------------------------------------------
1054 --
1055 -- ldap_wrapper_create_user (PUBLIC)
1056 -- This is called by user form and the fnd_user_pkg.
1057 -- It serves as a helper routine to call fnd_ldap_wrapper.create_user
1058 -- when we need to synch that new FND user to OID.
1059 -- It also takes care of updating fnd_user with the user_guid and oid_pwd
1060 -- coming back from ldap_wrapper layer.
1061 -- Note
1062 -- Please note that even this is public procedure, it does not mean for
1063 -- other public usage. This is mainly created as a helper routine to
1064 -- service the user form and the user package.
1065 /*#
1066 * This is called by user form and the fnd_user_pkg. It serves as helper
1067 * routine to call fnd_ldap_wrapper.create_user when we need to synch that
1068 * new FND user to OID.
1069 * Please note that even this is public procedure, it does not mean for
1070 * other public usage. This is mainly created as a helper routine to
1071 * service the user form and the user package.
1072 * @param x_user_name in varchar2 The user name
1073 * @param x_unencrypted_password in varchar2 The unencrypted user password
1074 * @param x_start_date in date Start date
1075 * @param x_end_date in date End date
1076 * @param x_description in varchar2 Description
1077 * @param x_email_address in varchar2 Email address
1078 * @param x_fax in varchar2 Fax
1079 * @param x_expire_pwd in pls_integer Whether to expire user password
1080 * @rep:scope public
1081 * @rep:lifecycle active
1082 * @rep:displayname LDAP Wrapper Create User
1083 * @rep:compatibility S
1084 * @rep:ihelp FND/@mesgdict#mesgdict See the related online help
1085 */
1086 -- ldap_wrapper_create_user
1087 procedure ldap_wrapper_create_user(x_user_name in varchar2,
1088 x_unencrypted_password in varchar2,
1089 x_start_date in date,
1090 x_end_date in date,
1091 x_description in varchar2,
1092 x_email_address in varchar2,
1093 x_fax in varchar2,
1097 ----------------------------------------------------------------------------
1094 x_expire_pwd in pls_integer default 0);
1095
1096 -- end bug 4318754
1098 --
1099 -- ldap_wrapper_change_user_name (PUBLIC)
1100 -- This is called by user form. When there is user name changed inside
1101 -- User form, we need to synch with ldap.
1102 --
1103 -- Note
1104 -- Please note that even this is public procedure, it does not mean for
1105 -- other public usage. This is mainly created as a helper routine to
1106 -- service the user form and the user package.
1107 /*#
1108 * This is called by user form when a user name changed, we need to
1109 * synch up with ldap with the new name.
1110 * Please note that even this is public procedure, it does not mean for
1111 * other public usage. This is mainly created as a helper routine to
1112 * service the user form and the user package.
1113 * @param x_old_user_name The Old User Name
1114 * @param x_new_user_name The New User Name
1115 * @rep:scope public
1116 * @rep:lifecycle active
1117 * @rep:displayname LDAP Wrapper Change User Name
1118 * @rep:compatibility S
1119 * @rep:ihelp FND/@mesgdict#mesgdict See the related online help
1120 */
1121 -- ldap_wrapper_create_user
1122 procedure ldap_wrapper_change_user_name(x_old_user_name in varchar2,
1123 x_new_user_name in varchar2);
1124
1125 ----------------------------------------------------------------------------
1126 --
1127 -- form_ldap_wrapper_update_user (PUBLIC)
1128 -- This is called by user form.
1129 -- It serves as a helper routine to call fnd_ldap_wrapper.update_user
1130 -- when we need to synch the user update to OID.
1131 -- Note
1132 -- Please note that even this is public procedure, it does not mean for
1133 -- other public usage. This is mainly created as a helper routine to
1134 -- service the user form and the user package.
1135 /*#
1136 * This is called by user form.
1137 * It serves as a helper routine to call fnd_ldap_wrapper.update_user
1138 * when we need to synch the user update to OID.
1139 * Please note that even this is public procedure, it does not mean for
1140 * other public usage. This is mainly created as a helper routine to
1141 * service the user form and the user package.
1142 * @param x_user_name in varchar2 The user name
1143 * @param x_unencrypted_password in varchar2 The unencrypted user password
1144 * @param x_start_date in date Start date
1145 * @param x_end_date in date End date
1146 * @param x_description in varchar2 Description
1147 * @param x_email_address in varchar2 Email address
1148 * @param x_fax in varchar2 Fax
1149 * @param x_out_pwd in out varchar2 output password from ldap/oid
1150 * @rep:scope public
1151 * @rep:lifecycle active
1152 * @rep:displayname User Form LDAP Wrapper Update User
1153 * @rep:compatibility S
1154 * @rep:ihelp FND/@mesgdict#mesgdict See the related online help
1155 */
1156 procedure form_ldap_wrapper_update_user(x_user_name in varchar2,
1157 x_unencrypted_password in varchar2,
1158 x_start_date in date,
1159 x_end_date in date,
1160 x_description in varchar2,
1161 x_email_address in varchar2,
1162 x_fax in varchar2,
1163 x_out_pwd in out nocopy varchar2);
1164
1165
1166 ----------------------------------------------------------------------------
1167 --
1168 -- ldap_wrp_update_user_loader (PUBLIC)
1169 -- This is called by the fnd_user_pkg and fnd_web_sec.
1170 -- It serves as a helper routine to call fnd_ldap_wrapper.update_user
1171 -- when we need to synch the user update to OID.
1172 -- Note
1173 -- Please note that even this is public procedure, it does not mean for
1174 -- other public usage. This is mainly created as a helper routine to
1175 -- service the user form and the user package.
1176 /*#
1177 * This is called by the fnd_user_pkg and fnd_web_sec.
1178 * It serves as a helper routine to call fnd_ldap_wrapper.update_user
1179 * when we need to synch the user update to OID.
1180 * Please note that even this is public procedure, it does not mean for
1181 * other public usage. This is mainly created as a helper routine to
1182 * service the user form and the user package.
1183 * @param x_user_name in varchar2 The user name
1184 * @param x_hashed_password in varchar2 The hashed user password
1185 * @param x_start_date in date Start date
1186 * @param x_end_date in date End date
1187 * @param x_description in varchar2 Description
1188 * @param x_email_address in varchar2 Email address
1189 * @param x_fax in varchar2 Fax
1190 * @param x_expire_pwd in boolean Inform LDAP/OID whether to expire the pwd
1191 * @rep:scope public
1192 * @rep:lifecycle active
1193 * @rep:displayname LDAP Wrapper Update User
1194 * @rep:compatibility S
1195 * @rep:ihelp FND/@mesgdict#mesgdict See the related online help
1196 */
1197 procedure ldap_wrp_update_user_loader(x_user_name in varchar2,
1198 x_hashed_password in varchar2,
1199 x_start_date in date,
1200 x_end_date in date,
1201 x_description in varchar2,
1202 x_email_address in varchar2,
1203 x_fax in varchar2,
1204 x_expire_pwd in pls_integer default 1);
1205
1206 end FND_USER_PKG;