1 package FND_USER_PKG AUTHID CURRENT_USER as
2 /* $Header: AFSCUSRS.pls 120.16.12020000.2 2012/07/18 09:07:37 vijnagar 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,
376 --
373 x_person_party_id in number default null);
374
375 ----------------------------------------------------------------------------
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 -- ValidateSSOLogin (RESTRICTED)
406 -- Validates the given username/password against SSO
407 -- This routine is restricted and can ONLY be used with the explicit permission
408 -- from ATG/AOL management.
409 -- Any other use will NOT be supported by AOLJ/SSO development team!!!!!!!
410 --
411 -- This api is introduced to enable the clients which can NOT perform HTTP
412 -- authentication against SSO (NON-UI business logic).
413
414 -- Use the api for SSO user authentication. It is assumed that EBS is already
415 -- configured with SSO.
416 -- In all other cases use FND_USER_PKG.ValidateLogin.
417 -- Product teams who are currently using this api:
418 -- Oracle Mobile Field Service (FND_APPLICATION.APPLICATION_SHORT_NAME=CSM)
419 -- (bug#14057306)
420 function ValidateSSOLogin(username varchar2,
421 password varchar2) return boolean;
422 ----------------------------------------------------------------------------
423 --
424 -- ChangePassword (PUBLIC)
425 -- Set new password for a given user without having to provide
426 -- the old password.
427 --
428 -- Usage example in pl/sql
429 -- begin fnd_user_pkg.changepassword('SCOTT', 'WELCOME'); end;
430 --
431 -- Input (Mandatory)
432 -- username: User Name
433 -- newpassword New Password
434 --
435 function ChangePassword(username varchar2,
436 newpassword varchar2) return boolean;
437 ----------------------------------------------------------------------------
438 --
439 -- ChangePassword (PUBLIC)
440 -- Set new password for a given user if the existing password needed to be
441 -- validated before changing to the new password.
442 --
443 -- Usage example in pl/sql
444 -- begin fnd_user_pkg.changepassword('SCOTT', 'TIGER', 'WELCOME'); end;
445 --
446 -- Input (Mandatory)
447 -- username: User Name
448 -- oldpassword Old Password
449 -- newpassword New Password
450 --
451 function ChangePassword(username varchar2,
452 oldpassword varchar2,
453 newpassword varchar2) return boolean;
454 ----------------------------------------------------------------------------
455 --
456 -- GetReEncryptedPassword (PUBLIC)
457 -- Return user password encrypted with new key. This just returns the
458 -- newly encrypted password. It does not set the password in FND_USER table.
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 -- end;
466 --
467 -- Input (Mandatory)
468 -- username: User Name
469 -- newkey New Key
470 --
471 function GetReEncryptedPassword(username varchar2,
472 newkey varchar2) return varchar2;
473
474 ----------------------------------------------------------------------------
475 -- SetReEncryptedPassword (PUBLIC)
476 -- Set user password from value returned from GetReEncryptedPassword.
477 -- This is to update column ENCRYPTED_USER_PASSWORD in table FND_USER
478 --
479 -- Usage example in pl/sql
480 -- declare
481 -- newpass varchar2(100);
482 -- begin
483 -- newpass := fnd_user_pkg.getreencryptedpassword('SCOTT', 'NEWKEY'); end;
484 -- fnd_user_pkg.setreencryptedpassword('SCOTT', newpass, 'NEWKEY'); end;
485 -- end;
486 --
487 -- Input (Mandatory)
488 -- username: User Name
489 -- reencpwd: Reencrypted Password
490 -- newkey New Key
491 --
492 function SetReEncryptedPassword(username varchar2,
493 reencpwd varchar2,
494 newkey varchar2) return boolean;
495 ----------------------------------------------------------------------------
496 -- MergeCustomer (PUBLIC)
497 -- This is the procedure being called during the Party Merge.
498 -- FND_USER.MergeCustomer() has been registered in Party Merge Data Dict.
499 -- The input/output arguments format matches the document PartyMergeDD.doc.
500 -- The goal is to fix the customer id in fnd_user table to point to the
501 -- same party when two similar parties are begin merged.
502 --
503 -- Usage example in pl/sql
504 -- This procedure should only be called from the PartyMerge utility.
505 --
506 procedure MergeCustomer(p_entity_name in varchar2,
507 p_from_id in number,
508 p_to_id in out nocopy number,
509 p_from_fk_id in number,
513 p_batch_party_id in number,
510 p_to_fk_id in number,
511 p_parent_entity_name in varchar2,
512 p_batch_id in number,
514 p_return_status in out nocopy varchar2);
515 --------------------------------------------------------------------------
516 /*
517 ** user_change - The rule function for FND's subscription on the
518 ** oracle.apps.wf.entmgr.user.change event. This function
519 ** retrieves the user's information and updates the
520 ** corresponding fnd_user as needed, if the user exists.
521 */
522 FUNCTION user_change(p_subscription_guid in raw,
523 p_event in out nocopy wf_event_t)
524 return varchar2;
525 --------------------------------------------------------------------------
526 /*
527 ** user_create_rf - The rule function for FND's 2nd subscription on the
528 ** oracle.apps.wf.entmgr.user.change event. This function
529 ** retrieves the user's information and creates the
530 ** corresponding fnd_user if the user does not already exist.
531 */
532 FUNCTION user_create_rf(p_subscription_guid in raw,
533 p_event in out nocopy wf_event_t)
534 return varchar2;
535 --------------------------------------------------------------------------
536 /*
537 ** user_synch - The centralized routine for communicating user changes
538 ** with wf and entity mgr.
539 */
540 PROCEDURE user_synch(p_user_name in varchar2);
541
542 --------------------------------------------------------------------------
543 --
544 -- DelResp (PUBLIC)
545 -- Detach a responsibility which is currently attached to this given user.
546 -- If any of the username or application short name or responsibility key or
547 -- security group is not valid, exception raised with error message.
548 --
549 -- Usage example in pl/sql
550 -- begin fnd_user_pkg.delresp('SCOTT', 'FND', 'APPLICATION_DEVELOPER',
551 -- 'STANDARD'); end;
552 -- Input (Mandatory)
553 -- username: User Name
554 -- resp_app: Application Short Name
555 -- resp_key: Responsibility Key
556 -- security_group: Security Group Key
557 --
558 procedure DelResp(username varchar2,
559 resp_app varchar2,
560 resp_key varchar2,
561 security_group varchar2);
562 --------------------------------------------------------------------------
563 --
564 -- AddResp (PUBLIC)
565 -- For a given user, attach a valid responsibility.
566 -- If user name or application short name or responsbility key name
567 -- or security group key is not valid, exception raised with error message.
568 --
569 -- Usage example in pl/sql
570 -- begin fnd_user_pkg.addresp('SCOTT', 'FND', 'APPLICATION_DEVELOPER',
571 -- 'STANDARD', 'DESCRIPTION', sysdate, null); end;
572 -- Input (Mandatory)
573 -- username: User Name
574 -- resp_app: Application Short Name
575 -- resp_key: Responsibility Key
576 -- security_group: Security Group Key
577 -- description: Description
578 -- start_date: Start Date
579 -- end_date: End Date
580 --
581 procedure AddResp(username varchar2,
582 resp_app varchar2,
583 resp_key varchar2,
584 security_group varchar2,
585 description varchar2,
586 start_date date,
587 end_date date);
588
589 -------------------------------------------------------------------
590 -- Name: isPasswordChangeable
591 -- Description: Checks if user us externally authenticatied
592 ----------------------------------------------------------------------
593 Function isPasswordChangeable (p_user_name in varchar2)
594 return boolean;
595
596 -------------------------------------------------------------------
597 -- Name: UpdatePassword_WF
598 -- Description: Calls FND_USER_PKG.UpdateUser
599 -------------------------------------------------------------------
600 Procedure UpdatePassword_WF(itemtype in varchar2,
601 itemkey in varchar2,
602 actid in number,
603 funcmode in varchar2,
604 resultout in out nocopy varchar2);
605 ----------------------------------------------------------------------------
606
607 --
608 -- DERIVE_PERSON_PARTY_ID
609 -- Derive the person_party_id, given a customer_id and employee_id
610 -- IN
611 -- customer_id
612 -- employee_id
613 -- RETURNS
614 -- person_party_id
615 --
616 /*#
617 * This is called by user form and other packages. This function
618 * validates the person party and returns the person party id
619 * attached to the user.
620 * @param user_name in varchar2 Username
621 * @param customer_id in number Customer Id
622 * @param employee_id in number Employee Id
623 * @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.
624 * @return person party id
625 * @rep:scope public
626 * @rep:lifecycle active
627 * @rep:displayname Derive Person Party Id
628 * @rep:compatibility S
629 */
630 Function DERIVE_PERSON_PARTY_ID(
631 user_name in varchar2,
632 customer_id in number,
633 employee_id in number,
634 log_exception in varchar2 default 'Y')
635 return number;
636
637 --
638 -- DERIVE_CUSTOMER_EMPLOYEE_ID
642 -- OUT
639 -- Update customer and employee ids if person_party_id is changed
640 -- IN
641 -- person_party_id
643 -- customer_id
644 -- employee_id
645 --
646 Procedure DERIVE_CUSTOMER_EMPLOYEE_ID(
647 user_name in varchar2,
648 person_party_id in number,
649 customer_id out nocopy number,
650 employee_id out nocopy number);
651
652 ----------------------------------------------------------------------------
653 --
654 -- EnableUser (PUBLIC)
655 -- Sets the start_date and end_date as requested. By default, the
656 -- start_date will be set to sysdate and end_date to null.
657 -- This is to enable that user.
658 -- You can log in as this user from now.
659 -- If username is not valid, exception raised with error message.
660 --
661 -- Usage example in pl/sql
662 -- begin fnd_user_pkg.enableuser('SCOTT'); end;
663 -- begin fnd_user_pkg.enableuser('SCOTT', sysdate+1, sysdate+30); end;
664 --
665 -- Input (Mandatory)
666 -- username: User Name
667 -- Input (Non-Mandatory)
668 -- start_date: Start Date
669 -- end_date: End Date
670 --
671 procedure EnableUser(username varchar2,
672 start_date date default sysdate,
673 end_date date default fnd_user_pkg.null_date);
674
675 ----------------------------------------------------------------------------
676 --
677 -- CreatePendingUser (PUBLIC)
678 -- Create a user whose start_date and end_date = FND_API.G_MISS_DATE as
679 -- a pending user.
680 -- Pending user is created when a user registers a user account through
681 -- UMX with an aproval process.
682 -- USER_ID is returned.
683 --
684 --
685 -- Usage example in pl/sql
686 -- begin uid := fnd_user_pkg.creatependinguser('SCOTT', 'SEED', 'welcome');
687 -- end;
688 -- begin uid := fnd_user_pkg.creatependinguser('SCOTT', 'SEED'); end;
689 --
690 -- Input (Mandatory)
691 -- x_user_name: User Name
692 -- x_owner: 'SEED' or 'CUST'(customer)
693 -- Output
694 -- user_id
695 --
696 function CreatePendingUser(
697 x_user_name in varchar2,
698 x_owner in varchar2,
699 x_unencrypted_password in varchar2 default null,
700 x_session_number in number default 0,
701 x_description in varchar2 default null,
702 x_password_date in date default null,
703 x_password_accesses_left in number default null,
704 x_password_lifespan_accesses in number default null,
705 x_password_lifespan_days in number default null,
706 x_email_address in varchar2 default null,
707 x_fax in varchar2 default null,
708 x_person_party_id in number default null) return number;
709
710 ----------------------------------------------------------------------------
711 --
712 -- RemovePendingUser (PUBLIC)
713 -- Delete this user from fnd_user table only if this is a pending user.
714 -- If this is not a valid username or is not a pending user, raise error.
715 -- Pending user is created when a user registers a user account through
716 -- UMX with an aproval process.
717 --
718 -- Usage example in pl/sql
719 -- begin fnd_user_pkg.removependinguser('SCOTT'); end;
720 --
721 -- Input (Mandatory)
722 -- username: User Name
723 --
724 procedure RemovePendingUser(username varchar2);
725
726 ----------------------------------------------------------------------------
727 --
728 -- AssignPartyToUser (PUBLIC)
729 -- Assign a TCA party to a given user
730 --
731 -- Usage example in pl/sql
732 -- begin fnd_user_pkg.assignpartytouser('SCOTT', 1001); end;
733 --
734 -- Input (Mandatory)
735 -- x_user_name: User Name
736 -- x_party_id: Party Name Id
737 --
738 procedure AssignPartyToUser(
739 x_user_name in varchar2,
740 x_party_id in number);
741
742 -- begin bug 2504562
743
744 ----------------------------------------------------------------------------
745 --
746 -- Usage example in pl/sql
747 -- begin fnd_user_pkg.change_user_name('SOCTT', 'SCOTT'); end;
748 --
749 -- Input (Mandantory)
750 -- x_old_user_name: Old User Name
751 -- x_new_user_name: New User Name
752 -- x_change_source: Change Source
753 -- null means we need to synch change to oid
754 -- CHANGE_SOURCE_OID means we don't need to synch back to oid anymore
755 -- X_CHANGE_SOURCE is private argument only used by ldap routine.
756 --
757 /*#
758 * This api changes username, deals with encryption changes and
759 * update foreign keysthat were using the old username.
760 * PLEASE NOTE THAT x_change_source IS PRIVATE ARGUMENT ONLY USED BY SSO!!!
761 * @param x_old_user_name in varchar2 The Old user name
762 * @param x_new_user_name in varchar2 The New user name
763 * @param x_change_source in number (default null).
764 * @rep:scope public
765 * @rep:lifecycle active
766 * @rep:displayname Change User Name
767 * @rep:compatibility S
768 * @rep:ihelp FND/@mesgdict#mesgdict See the related online help
769 */
770 procedure change_user_name(
771 x_old_user_name in varchar2,
772 x_new_user_name in varchar2,
773 x_change_source in number default null);
774
775
776 ----------------------------------------------------------------------------
777 --
778 -- set_old_user_name (PUBLIC)
779 -- This function is called from Forms to set the global variable,
780 -- fnd_user_pkg/g_old_user_name since this cannot be set directly from Forms.
781 -- This function returns a number which can be used to check for success
782 -- from Forms.
786 -- retval number := null;
783 --
784 -- Usage example in pl/sql
785 -- declare
787 -- begin retval := fnd_user_pkg.set_old_user_name('SOCTT'); end;
788 --
789 -- Input (Mandantory)
790 -- x_old_user_name: Old User Name
791 --
792 function set_old_user_name(x_old_user_name in varchar2) return number;
793
794 -- end bug 2504562
795
796 ----------------------------------------------------------------------------
797 -- MergePartyId (PUBLIC)
798 -- This is the procedure being called during the Party Merge.
799 -- FND_USER.MergePartyId() has been registered in Party Merge Data Dict.
800 -- The input/output arguments format matches the document PartyMergeDD.doc.
801 -- The goal is to fix the party id in fnd_user table to point to the
802 -- same party when two similar parties are begin merged.
803 --
804 -- Usage example in pl/sql
805 -- This procedure should only be called from the PartyMerge utility.
806 --
807 procedure MergePartyId(p_entity_name in varchar2,
808 p_from_id in number,
809 p_to_id in out nocopy number,
810 p_from_fk_id in number,
811 p_to_fk_id in number,
812 p_parent_entity_name in varchar2,
813 p_batch_id in number,
814 p_batch_party_id in number,
815 p_return_status in out nocopy varchar2);
816
817 -- Validate_User_Name (PUBLIC)
818 -- Make sure that input argument x_user_name doesn't contain invalid character.
819 -- For now: We only care about '/' and ':' because they are known problem.
820 -- 01/19/05: we now have more invalid characters info from bug 4116239, so
821 -- I am adding more characters.
822 -- Rewrite later: checking for any non-printable character.
823 -- make sure multibyte character is ok.
824 /*#
825 * This is called by user form and the fnd_user_pkg. In both places
826 * we need to validate whether an username is in a valid format.
827 * @param x_user_name in varchar2 Username
828 * @rep:scope public
829 * @rep:lifecycle active
830 * @rep:displayname Validate User Name
831 * @rep:compatibility S
832 * @rep:ihelp FND/@mesgdict#mesgdict See the related online help
833 */
834 procedure validate_user_name(x_user_name in varchar2);
835
836 ----------------------------------------------------------------------
837 --
838 -- CreateUser (PUBLIC)
839 --
840 -- Bug#3904339 - SSO: Add user_guid parameter in fnd_user_pkg apis
841 -- Overloaded procedure to create user
842 -- Accepts User GUID as a parameter in addition to the other parameters
843 --
844 procedure CreateUser (
845 x_user_name in varchar2,
846 x_owner in varchar2,
847 x_unencrypted_password in varchar2 default null,
848 x_session_number in number default 0,
849 x_start_date in date default sysdate,
850 x_end_date in date default null,
851 x_last_logon_date in date default null,
852 x_description in varchar2 default null,
853 x_password_date in date default null,
854 x_password_accesses_left in number default null,
855 x_password_lifespan_accesses in number default null,
856 x_password_lifespan_days in number default null,
857 x_employee_id in number default null,
858 x_email_address in varchar2 default null,
859 x_fax in varchar2 default null,
860 x_customer_id in number default null,
861 x_supplier_id in number default null,
862 x_user_guid in raw,
863 x_change_source in number default null);
864
865 ----------------------------------------------------------------------
866 --
867 -- CreateUserId (PUBLIC)
868 --
869 -- Bug#3904339 - SSO: Add user_guid parameter in fnd_user_pkg apis
870 -- Overloaded function to create user
871 -- Accepts User GUID as a parameter in addition to the other parameters
872 -- Returns
873 -- User_id of created user
874 --
875 function CreateUserId (
876 x_user_name in varchar2,
877 x_owner in varchar2,
878 x_unencrypted_password in varchar2 default null,
879 x_session_number in number default 0,
880 x_start_date in date default sysdate,
881 x_end_date in date default null,
882 x_last_logon_date in date default null,
883 x_description in varchar2 default null,
884 x_password_date in date default null,
885 x_password_accesses_left in number default null,
886 x_password_lifespan_accesses in number default null,
887 x_password_lifespan_days in number default null,
888 x_employee_id in number default null,
889 x_email_address in varchar2 default null,
890 x_fax in varchar2 default null,
891 x_customer_id in number default null,
892 x_supplier_id in number default null,
893 x_user_guid in raw,
894 x_change_source in number default null)
895 return number;
896
897 ----------------------------------------------------------------------
898 --
899 -- UpdateUser (Public)
900 --
901 -- Bug#3904339 - SSO: Add user_guid parameter in fnd_user_pkg apis
902 -- Overloaded procedure to update user
903 -- Accepts User GUID in addition to the other parameters
904 --
905
906 procedure UpdateUser (
907 x_user_name in varchar2,
908 x_owner in varchar2,
909 x_unencrypted_password in varchar2 default null,
910 x_session_number in number default null,
914 x_description in varchar2 default null,
911 x_start_date in date default null,
912 x_end_date in date default null,
913 x_last_logon_date in date default null,
915 x_password_date in date default null,
916 x_password_accesses_left in number default null,
917 x_password_lifespan_accesses in number default null,
918 x_password_lifespan_days in number default null,
919 x_employee_id in number default null,
920 x_email_address in varchar2 default null,
921 x_fax in varchar2 default null,
922 x_customer_id in number default null,
923 x_supplier_id in number default null,
924 x_old_password in varchar2 default null,
925 x_user_guid in raw,
926 x_change_source in number default null);
927
928 ----------------------------------------------------------------------
929 -- userExists (Public)
930 --
931 -- This function checks if the user exists and returns 'True' or 'False'
932 -- Input (Mandatory)
933 -- username: User Name
934
935 function userExists (x_user_name in varchar2) return boolean;
936
937 ----------------------------------------------------------------------------
938 --
939 -- TestUserName (PUBLIC)
940 -- This api test whether a username exists in FND and/or in OID.
941 --
942 -- Usage example in pl/sql
943 -- declare ret number;
944 -- begin ret := fnd_user_pkg.testusername('SOCTT'); end;
945 --
946 -- Input (Mandantory)
947 -- x_user_name: User Name that you want to test
948 --
949 -- Output
950 -- USER_OK_CREATE : User does not exist in either FND or OID
951 -- USER_INVALID_NAME : User name is not valid
952 -- USER_EXISTS_IN_FND : User exists in FND
953 -- USER_SYNCHED : User exists in OID and next time when this user gets
954 -- created in FND, the two will be synched together.
955 -- USER_EXISTS_NO_LINK_ALLOWED: User exists in OID and no synching allowed.
956 --
957 /*#
958 * Check a user name exists in FND and (or) in OID.
959 * @param x_user_name The username to be tested
960 * @return The User Existence Status Code
961 * @rep:scope public
962 * @rep:lifecycle active
963 * @rep:displayname Test User Name
964 * @rep:compatibility S
965 * @rep:ihelp FND/@mesgdict#mesgdict See the related online help
966 */
967 function TestUserName(x_user_name in varchar2) return pls_integer;
968
969 ----------------------------------------------------------------------------
970 --
971 -- set_old_person_party_id (PUBLIC)
972 -- This function is called from Forms to set the global variable,
973 -- g_old_person_party_id since this cannot be set directly from Forms.
974 -- This function returns a number which can be used to check for success
975 -- from Forms.
976 --
977 -- Usage example in pl/sql
978 -- declare
979 -- retval number := null;
980 -- begin retval := fnd_user_pkg.set_old_person_party_id(12345); end;
981 --
982 -- Input (Mandantory)
983 -- x_old_person_party_id: Old Person Party Id
984 --
985 /*#
986 * This function is called from Forms and other serverside code to set
987 * the global variable g_old_person_party_id.
988 * Even this is a public function but is for INTERNAL usage.
989 * @param x_old_person_party_id The old person party id for a FND user
990 * @return 1: Success or 0: Failure
991 * @rep:scope public
992 * @rep:lifecycle active
993 * @rep:displayname Set Old Person Party Id
994 * @rep:compatibility S
995 * @rep:ihelp FND/@mesgdict#mesgdict See the related online help
996 */
997 function set_old_person_party_id(x_old_person_party_id in varchar2)
998 return number;
999
1000 ----------------------------------------------------------------------------
1001 --
1002 -- set_old_user_guid (PUBLIC)
1003 -- This function sets the global variable g_old_user_guid.
1004 -- This function returns a number which can be used to check for success
1005 -- from Forms.
1006 --
1007 -- Usage example in pl/sql
1008 -- declare
1009 -- retval number := null;
1010 -- guid raw(16);
1011 -- begin
1012 -- guid := 'F9374D4B80AB1A86E034080020B2612C';
1013 -- retval := fnd_user_pkg.set_old_user_guid(guid); end;
1014 --
1015 -- Input (Mandantory)
1016 -- x_old_user_guid: Old USER GUID
1017 --
1018 /*#
1019 * This function sets the global variable g_old_user_guid.
1020 * Even this is a public function but is for INTERNAL usage.
1021 * @param x_old_user_guid The old user guid for a FND user
1022 * @return 1: Success or 0: Failure
1023 * @rep:scope public
1024 * @rep:lifecycle active
1025 * @rep:displayname Set Old User GUID
1026 * @rep:compatibility S
1027 * @rep:ihelp FND/@mesgdict#mesgdict See the related online help
1028 */
1029 function set_old_user_guid(x_old_user_guid in raw) return number;
1030
1031
1032 ----------------------------------------------------------------------------
1033 --
1034 -- ldap_wrapper_update_user (PUBLIC)
1035 -- This is called by the fnd_user_pkg and fnd_web_sec.
1036 -- It serves as a helper routine to call fnd_ldap_wrapper.update_user
1037 -- when we need to synch the user update to OID.
1038 -- Note
1039 -- Please note that even this is public procedure, it does not mean for
1040 -- other public usage. This is mainly created as a helper routine to
1041 -- service the user form and the user package.
1042 /*#
1043 * This is called by the fnd_user_pkg and fnd_web_sec.
1044 * It serves as a helper routine to call fnd_ldap_wrapper.update_user
1045 * when we need to synch the user update to OID.
1046 * Please note that even this is public procedure, it does not mean for
1050 * @param x_unencrypted_password in varchar2 The unencrypted user password
1047 * other public usage. This is mainly created as a helper routine to
1048 * service the user form and the user package.
1049 * @param x_user_name in varchar2 The user name
1051 * @param x_start_date in date Start date
1052 * @param x_end_date in date End date
1053 * @param x_description in varchar2 Description
1054 * @param x_email_address in varchar2 Email address
1055 * @param x_fax in varchar2 Fax
1056 * @param x_expire_pwd in boolean Inform LDAP/OID whether to expire the pwd
1057 * @rep:scope public
1058 * @rep:lifecycle active
1059 * @rep:displayname LDAP Wrapper Update User
1060 * @rep:compatibility S
1061 * @rep:ihelp FND/@mesgdict#mesgdict See the related online help
1062 */
1063 procedure ldap_wrapper_update_user(x_user_name in varchar2,
1064 x_unencrypted_password in varchar2,
1065 x_start_date in date,
1066 x_end_date in date,
1067 x_description in varchar2,
1068 x_email_address in varchar2,
1069 x_fax in varchar2,
1070 x_expire_pwd in pls_integer default 0);
1071
1072 ----------------------------------------------------------------------------
1073 --
1074 -- ldap_wrapper_create_user (PUBLIC)
1075 -- This is called by user form and the fnd_user_pkg.
1076 -- It serves as a helper routine to call fnd_ldap_wrapper.create_user
1077 -- when we need to synch that new FND user to OID.
1078 -- It also takes care of updating fnd_user with the user_guid and oid_pwd
1079 -- coming back from ldap_wrapper layer.
1080 -- Note
1081 -- Please note that even this is public procedure, it does not mean for
1082 -- other public usage. This is mainly created as a helper routine to
1083 -- service the user form and the user package.
1084 /*#
1085 * This is called by user form and the fnd_user_pkg. It serves as helper
1086 * routine to call fnd_ldap_wrapper.create_user when we need to synch that
1087 * new FND user to OID.
1088 * Please note that even this is public procedure, it does not mean for
1089 * other public usage. This is mainly created as a helper routine to
1090 * service the user form and the user package.
1091 * @param x_user_name in varchar2 The user name
1092 * @param x_unencrypted_password in varchar2 The unencrypted user password
1093 * @param x_start_date in date Start date
1094 * @param x_end_date in date End date
1095 * @param x_description in varchar2 Description
1096 * @param x_email_address in varchar2 Email address
1097 * @param x_fax in varchar2 Fax
1098 * @param x_expire_pwd in pls_integer Whether to expire user password
1099 * @rep:scope public
1100 * @rep:lifecycle active
1101 * @rep:displayname LDAP Wrapper Create User
1102 * @rep:compatibility S
1103 * @rep:ihelp FND/@mesgdict#mesgdict See the related online help
1104 */
1105 -- ldap_wrapper_create_user
1106 procedure ldap_wrapper_create_user(x_user_name in varchar2,
1107 x_unencrypted_password in varchar2,
1108 x_start_date in date,
1109 x_end_date in date,
1110 x_description in varchar2,
1111 x_email_address in varchar2,
1112 x_fax in varchar2,
1113 x_expire_pwd in pls_integer default 0);
1114
1115 -- end bug 4318754
1116 ----------------------------------------------------------------------------
1117 --
1118 -- ldap_wrapper_change_user_name (PUBLIC)
1119 -- This is called by user form. When there is user name changed inside
1120 -- User form, we need to synch with ldap.
1121 --
1122 -- Note
1123 -- Please note that even this is public procedure, it does not mean for
1124 -- other public usage. This is mainly created as a helper routine to
1125 -- service the user form and the user package.
1126 /*#
1127 * This is called by user form when a user name changed, we need to
1128 * synch up with ldap with the new name.
1129 * Please note that even this is public procedure, it does not mean for
1130 * other public usage. This is mainly created as a helper routine to
1131 * service the user form and the user package.
1132 * @param x_old_user_name The Old User Name
1133 * @param x_new_user_name The New User Name
1134 * @rep:scope public
1135 * @rep:lifecycle active
1136 * @rep:displayname LDAP Wrapper Change User Name
1137 * @rep:compatibility S
1138 * @rep:ihelp FND/@mesgdict#mesgdict See the related online help
1139 */
1140 -- ldap_wrapper_create_user
1141 procedure ldap_wrapper_change_user_name(x_old_user_name in varchar2,
1142 x_new_user_name in varchar2);
1143
1144 ----------------------------------------------------------------------------
1145 --
1146 -- form_ldap_wrapper_update_user (PUBLIC)
1147 -- This is called by user form.
1148 -- It serves as a helper routine to call fnd_ldap_wrapper.update_user
1149 -- when we need to synch the user update to OID.
1150 -- Note
1151 -- Please note that even this is public procedure, it does not mean for
1152 -- other public usage. This is mainly created as a helper routine to
1153 -- service the user form and the user package.
1154 /*#
1155 * This is called by user form.
1156 * It serves as a helper routine to call fnd_ldap_wrapper.update_user
1157 * when we need to synch the user update to OID.
1158 * Please note that even this is public procedure, it does not mean for
1159 * other public usage. This is mainly created as a helper routine to
1160 * service the user form and the user package.
1161 * @param x_user_name in varchar2 The user name
1162 * @param x_unencrypted_password in varchar2 The unencrypted user password
1163 * @param x_start_date in date Start date
1164 * @param x_end_date in date End date
1165 * @param x_description in varchar2 Description
1166 * @param x_email_address in varchar2 Email address
1167 * @param x_fax in varchar2 Fax
1168 * @param x_out_pwd in out varchar2 output password from ldap/oid
1169 * @rep:scope public
1170 * @rep:lifecycle active
1171 * @rep:displayname User Form LDAP Wrapper Update User
1172 * @rep:compatibility S
1173 * @rep:ihelp FND/@mesgdict#mesgdict See the related online help
1174 */
1175 procedure form_ldap_wrapper_update_user(x_user_name in varchar2,
1176 x_unencrypted_password in varchar2,
1177 x_start_date in date,
1178 x_end_date in date,
1179 x_description in varchar2,
1180 x_email_address in varchar2,
1181 x_fax in varchar2,
1182 x_out_pwd in out nocopy varchar2);
1183
1184
1185 ----------------------------------------------------------------------------
1186 --
1187 -- ldap_wrp_update_user_loader (PUBLIC)
1188 -- This is called by the fnd_user_pkg and fnd_web_sec.
1189 -- It serves as a helper routine to call fnd_ldap_wrapper.update_user
1190 -- when we need to synch the user update to OID.
1191 -- Note
1192 -- Please note that even this is public procedure, it does not mean for
1193 -- other public usage. This is mainly created as a helper routine to
1194 -- service the user form and the user package.
1195 /*#
1196 * This is called by the fnd_user_pkg and fnd_web_sec.
1197 * It serves as a helper routine to call fnd_ldap_wrapper.update_user
1198 * when we need to synch the user update to OID.
1199 * Please note that even this is public procedure, it does not mean for
1200 * other public usage. This is mainly created as a helper routine to
1201 * service the user form and the user package.
1202 * @param x_user_name in varchar2 The user name
1203 * @param x_hashed_password in varchar2 The hashed user password
1204 * @param x_start_date in date Start date
1205 * @param x_end_date in date End date
1206 * @param x_description in varchar2 Description
1207 * @param x_email_address in varchar2 Email address
1208 * @param x_fax in varchar2 Fax
1209 * @param x_expire_pwd in boolean Inform LDAP/OID whether to expire the pwd
1210 * @rep:scope public
1211 * @rep:lifecycle active
1212 * @rep:displayname LDAP Wrapper Update User
1213 * @rep:compatibility S
1214 * @rep:ihelp FND/@mesgdict#mesgdict See the related online help
1215 */
1216 procedure ldap_wrp_update_user_loader(x_user_name in varchar2,
1217 x_hashed_password in varchar2,
1218 x_start_date in date,
1219 x_end_date in date,
1220 x_description in varchar2,
1221 x_email_address in varchar2,
1222 x_fax in varchar2,
1223 x_expire_pwd in pls_integer default 1);
1224
1225 end FND_USER_PKG;
[Home] [Help]