[Home] [Help]
1: package body FND_USER_PKG as
2: /* $Header: AFSCUSRB.pls 120.48.12010000.3 2009/01/16 22:51:42 fskinner ship $ */
3:
4: /* START PARTY */
5: C_PKG_NAME CONSTANT VARCHAR2(30) := 'FND_USER_PKG';
1: package body FND_USER_PKG as
2: /* $Header: AFSCUSRB.pls 120.48.12010000.3 2009/01/16 22:51:42 fskinner ship $ */
3:
4: /* START PARTY */
5: C_PKG_NAME CONSTANT VARCHAR2(30) := 'FND_USER_PKG';
6: C_LOG_HEAD CONSTANT VARCHAR2(30) := 'fnd.plsql.FND_USER_PKG.';
7:
8: /* One level cache for derive_person_party_id */
9: Z_EMPLOYEE_ID NUMBER := NULL;
2: /* $Header: AFSCUSRB.pls 120.48.12010000.3 2009/01/16 22:51:42 fskinner ship $ */
3:
4: /* START PARTY */
5: C_PKG_NAME CONSTANT VARCHAR2(30) := 'FND_USER_PKG';
6: C_LOG_HEAD CONSTANT VARCHAR2(30) := 'fnd.plsql.FND_USER_PKG.';
7:
8: /* One level cache for derive_person_party_id */
9: Z_EMPLOYEE_ID NUMBER := NULL;
10: Z_CUSTOMER_ID NUMBER := NULL;
149: l_old_user_guid RAW(16);
150:
151: cursor usercur is
152: select customer_id, employee_id, person_party_id, user_guid
153: from fnd_user
154: where user_name = upper(x_user_name);
155:
156: -- Bug 5235329
157: l_expire_pwd pls_integer;
178: -- Bug4680643 get values for start_date and end_date from the database.
179:
180: select start_date, end_date
181: into l_start_date, l_end_date
182: from fnd_user
183: where user_name = upper(x_user_name);
184:
185: -- Bug4680643 Determine and set the value of start_date and end_date
186: -- outside of the decode statement which was truncating the actual
185: -- Bug4680643 Determine and set the value of start_date and end_date
186: -- outside of the decode statement which was truncating the actual
187: -- value when it is already set.
188:
189: if (x_start_date = fnd_user_pkg.null_date) then
190: l_start_date := null;
191: elsif (x_start_date is not null) then
192: l_start_date := x_start_date;
193: end if;
193: end if;
194: -- Bug 4901996. Truncate the time stamp
195: l_start_date := trunc(l_start_date);
196:
197: if (x_end_date = fnd_user_pkg.null_date) then
198: l_end_date := null;
199: elsif (x_end_date is not null) then
200: l_end_date := x_end_date;
201: end if;
204:
205: -- Translate *NULL* parameter values into real nulls,
206: -- treat null values as no-change.
207: begin
208: select decode(x_session_number, fnd_user_pkg.null_number, null,
209: null, u.session_number,
210: x_session_number),
211: -- bug 6608790 this preserves timestamp for iRecruitement
212: decode(x_last_logon_date, fnd_user_pkg.null_date, to_date(null),
208: select decode(x_session_number, fnd_user_pkg.null_number, null,
209: null, u.session_number,
210: x_session_number),
211: -- bug 6608790 this preserves timestamp for iRecruitement
212: decode(x_last_logon_date, fnd_user_pkg.null_date, to_date(null),
213: to_date(null), u.last_logon_date,
214: x_last_logon_date),
215: decode(x_description, fnd_user_pkg.null_char, null,
216: null, u.description,
211: -- bug 6608790 this preserves timestamp for iRecruitement
212: decode(x_last_logon_date, fnd_user_pkg.null_date, to_date(null),
213: to_date(null), u.last_logon_date,
214: x_last_logon_date),
215: decode(x_description, fnd_user_pkg.null_char, null,
216: null, u.description,
217: x_description),
218: decode(x_password_date, fnd_user_pkg.null_date, null,
219: null, u.password_date,
214: x_last_logon_date),
215: decode(x_description, fnd_user_pkg.null_char, null,
216: null, u.description,
217: x_description),
218: decode(x_password_date, fnd_user_pkg.null_date, null,
219: null, u.password_date,
220: x_password_date),
221: decode(x_password_accesses_left, fnd_user_pkg.null_number, null,
222: null, u.password_accesses_left,
217: x_description),
218: decode(x_password_date, fnd_user_pkg.null_date, null,
219: null, u.password_date,
220: x_password_date),
221: decode(x_password_accesses_left, fnd_user_pkg.null_number, null,
222: null, u.password_accesses_left,
223: x_password_accesses_left),
224: decode(x_password_lifespan_accesses, fnd_user_pkg.null_number, null,
225: null, u.password_lifespan_accesses,
220: x_password_date),
221: decode(x_password_accesses_left, fnd_user_pkg.null_number, null,
222: null, u.password_accesses_left,
223: x_password_accesses_left),
224: decode(x_password_lifespan_accesses, fnd_user_pkg.null_number, null,
225: null, u.password_lifespan_accesses,
226: x_password_lifespan_accesses),
227: decode(x_password_lifespan_days, fnd_user_pkg.null_number, null,
228: null, u.password_lifespan_days,
223: x_password_accesses_left),
224: decode(x_password_lifespan_accesses, fnd_user_pkg.null_number, null,
225: null, u.password_lifespan_accesses,
226: x_password_lifespan_accesses),
227: decode(x_password_lifespan_days, fnd_user_pkg.null_number, null,
228: null, u.password_lifespan_days,
229: x_password_lifespan_days),
230: decode(x_employee_id, fnd_user_pkg.null_number, null,
231: null, u.employee_id,
226: x_password_lifespan_accesses),
227: decode(x_password_lifespan_days, fnd_user_pkg.null_number, null,
228: null, u.password_lifespan_days,
229: x_password_lifespan_days),
230: decode(x_employee_id, fnd_user_pkg.null_number, null,
231: null, u.employee_id,
232: x_employee_id),
233: decode(x_email_address, fnd_user_pkg.null_char, null,
234: null, u.email_address,
229: x_password_lifespan_days),
230: decode(x_employee_id, fnd_user_pkg.null_number, null,
231: null, u.employee_id,
232: x_employee_id),
233: decode(x_email_address, fnd_user_pkg.null_char, null,
234: null, u.email_address,
235: x_email_address),
236: decode(x_fax, fnd_user_pkg.null_char, null,
237: null, u.fax,
232: x_employee_id),
233: decode(x_email_address, fnd_user_pkg.null_char, null,
234: null, u.email_address,
235: x_email_address),
236: decode(x_fax, fnd_user_pkg.null_char, null,
237: null, u.fax,
238: x_fax),
239: decode(x_customer_id, fnd_user_pkg.null_number, null,
240: null, u.customer_id,
235: x_email_address),
236: decode(x_fax, fnd_user_pkg.null_char, null,
237: null, u.fax,
238: x_fax),
239: decode(x_customer_id, fnd_user_pkg.null_number, null,
240: null, u.customer_id,
241: x_customer_id),
242: decode(x_supplier_id, fnd_user_pkg.null_number, null,
243: null, u.supplier_id,
238: x_fax),
239: decode(x_customer_id, fnd_user_pkg.null_number, null,
240: null, u.customer_id,
241: x_customer_id),
242: decode(x_supplier_id, fnd_user_pkg.null_number, null,
243: null, u.supplier_id,
244: x_supplier_id),
245: decode(x_person_party_id, fnd_user_pkg.null_number, null,
246: null, u.person_party_id,
241: x_customer_id),
242: decode(x_supplier_id, fnd_user_pkg.null_number, null,
243: null, u.supplier_id,
244: x_supplier_id),
245: decode(x_person_party_id, fnd_user_pkg.null_number, null,
246: null, u.person_party_id,
247: x_person_party_id),
248: decode(x_user_guid, fnd_user_pkg.null_raw, null,
249: null, u.user_guid,
244: x_supplier_id),
245: decode(x_person_party_id, fnd_user_pkg.null_number, null,
246: null, u.person_party_id,
247: x_person_party_id),
248: decode(x_user_guid, fnd_user_pkg.null_raw, null,
249: null, u.user_guid,
250: x_user_guid )
251: into l_session_number,
252: l_last_logon_date, l_description, l_password_date,
253: l_password_accesses_left, l_password_lifespan_accesses,
254: l_password_lifespan_days, l_employee_id,
255: l_email_address, l_fax, l_customer_id, l_supplier_id,
256: l_person_party_id , l_user_guid
257: from fnd_user u
258: where u.user_name = upper(x_user_name);
259: exception
260: when others then
261: fnd_message.set_name('FND', 'FND_INVALID_USER');
268: -- Called from UpdateUserParty
269: -- Derive customer/employee_ids from party_id
270: -- This was added to fix Bug#3663908
271: if (nvl(l_old_person_party_id, 0) <> nvl(l_person_party_id, 0)) then
272: fnd_user_pkg.derive_customer_employee_id(x_user_name, l_person_party_id,
273: l_customer_id, l_employee_id);
274: end if;
275: else
276: -- Called from UpdateUser
276: -- Called from UpdateUser
277: -- Derive the party_id from the customer/employee_ids.
278: -- This was added to fix Bug#3663908
279: if (nvl(l_old_customer_id, 0) <> nvl(l_customer_id, 0)) or (nvl(l_old_employee_id, 0) <> nvl(l_employee_id, 0)) then
280: l_person_party_id := fnd_user_pkg.derive_person_party_id(
281: x_user_name, l_customer_id, l_employee_id);
282: end if;
283: end if;
284:
329: 'UpdateUser(): before updating user');
330: end if;
331:
332: -- Update all of the non-password columns
333: update fnd_user set
334: last_update_date = x_last_update_date,
335: last_updated_by = owner_id,
336: last_update_login = owner_id,
337: session_number = l_session_number,
358: app_exception.raise_exception;
359: else
360: -- Added for Function Security Cache Invalidation
361: select user_id into l_user_id
362: from fnd_user
363: where user_name = upper(x_user_name);
364:
365: fnd_function_security_cache.update_user(l_user_id);
366: end if;
374: end if;
375:
376: -- Enhancement 5027812
377: if (x_change_source is null or
378: x_change_source <> fnd_user_pkg.change_source_oid ) then
379: begin
380: -- 5235329, 5375111 Let ldap/oid know whether to expire password
381: -- But only if there is a new password.
382: if (x_unencrypted_password is not null and
383: x_unencrypted_password <> FND_WEB_SEC.EXTERNAL_PWD) then
384: begin
385: select fnd_ldap_wrapper.G_TRUE
386: into l_expire_pwd
387: from fnd_user
388: where user_name = upper(x_user_name)
389: and (PASSWORD_DATE is NULL or
390: (PASSWORD_LIFESPAN_ACCESSES is not NULL and
391: nvl(PASSWORD_ACCESSES_LEFT, 0) < 1) or
417: 'After calling ldap_wrapper_update_user');
418: end if;
419:
420: -- Sync user change to LDAP
421: fnd_user_pkg.user_synch(x_user_name);
422:
423: end UpdateUserInternal;
424:
425: ----------------------------------------------------------------------
444: X_EMAIL_ADDRESS in VARCHAR2,
445: X_FAX in VARCHAR2 ) is
446:
447: begin
448: fnd_user_pkg.LOAD_ROW (
449: X_USER_NAME=>X_USER_NAME,
450: X_OWNER=> X_OWNER,
451: X_ENCRYPTED_USER_PASSWORD=>X_ENCRYPTED_USER_PASSWORD,
452: X_SESSION_NUMBER=> X_SESSION_NUMBER,
520:
521: -- bug 4047740 - as a byproduct of FND_WEB_SEC.set_reencrypted_password
522: -- NOT calling change_password we need this check to complete the checks
523: -- below as in FND_WEB_SEC - also added update to both encrypted rows for
524: -- the fnd_user update below for completeness.
525: if(false = fnd_sso_manager.isPasswordChangeable(puser_name)) then
526: encPwd := 'EXTERNAL';
527: else
528: encPwd := pcrypt_pass;
530:
531: if (encPwd in ('EXTERNAL', 'INVALID')) then
532: -- The password was 'EXTERNAL' or 'INVALID', just set it directly
533: -- without trying to re-encrypt
534: update fnd_user
535: set encrypted_foundation_password = encPwd,
536: encrypted_user_password = encPwd
537: where user_name = puser_name;
538:
540: if (encPwd = 'INVALID') then
541: fnd_file.put_line(fnd_file.Log,'Invalid password for user ' ||puser_name );
542: end if;
543: else
544: ret := fnd_user_pkg.SetReEncryptedPassword(puser_name, encPwd, 'LOADER');
545: end if;
546: END;
547:
548: begin
555: /*Bug2896887 - Modified code to analyze and set up the correct value
556: for the nullable parameters to be passed to the
557: UpdateUser and CreateUser procedures. */
558:
559: if (X_END_DATE = fnd_user_pkg.null_char) then
560: l_end_date := fnd_user_pkg.null_date;
561: else
562: l_end_date := to_date(X_END_DATE, 'YYYY/MM/DD');
563: end if;
556: for the nullable parameters to be passed to the
557: UpdateUser and CreateUser procedures. */
558:
559: if (X_END_DATE = fnd_user_pkg.null_char) then
560: l_end_date := fnd_user_pkg.null_date;
561: else
562: l_end_date := to_date(X_END_DATE, 'YYYY/MM/DD');
563: end if;
564:
561: else
562: l_end_date := to_date(X_END_DATE, 'YYYY/MM/DD');
563: end if;
564:
565: if (X_PASSWORD_DATE = fnd_user_pkg.null_char) then
566: l_password_date := fnd_user_pkg.null_date;
567: else
568: l_password_date := to_date(X_PASSWORD_DATE, 'YYYY/MM/DD');
569: end if;
562: l_end_date := to_date(X_END_DATE, 'YYYY/MM/DD');
563: end if;
564:
565: if (X_PASSWORD_DATE = fnd_user_pkg.null_char) then
566: l_password_date := fnd_user_pkg.null_date;
567: else
568: l_password_date := to_date(X_PASSWORD_DATE, 'YYYY/MM/DD');
569: end if;
570:
567: else
568: l_password_date := to_date(X_PASSWORD_DATE, 'YYYY/MM/DD');
569: end if;
570:
571: if (X_LAST_LOGON_DATE = fnd_user_pkg.null_char) then
572: l_last_logon_date := fnd_user_pkg.null_date;
573: else
574: l_last_logon_date := to_date(X_LAST_LOGON_DATE, 'YYYY/MM/DD');
575: end if;
568: l_password_date := to_date(X_PASSWORD_DATE, 'YYYY/MM/DD');
569: end if;
570:
571: if (X_LAST_LOGON_DATE = fnd_user_pkg.null_char) then
572: l_last_logon_date := fnd_user_pkg.null_date;
573: else
574: l_last_logon_date := to_date(X_LAST_LOGON_DATE, 'YYYY/MM/DD');
575: end if;
576:
573: else
574: l_last_logon_date := to_date(X_LAST_LOGON_DATE, 'YYYY/MM/DD');
575: end if;
576:
577: if (X_PASSWORD_ACCESSES_LEFT = fnd_user_pkg.null_char) then
578: l_password_accesses_left := fnd_user_pkg.null_number;
579: else
580: l_password_accesses_left := to_number(X_PASSWORD_ACCESSES_LEFT);
581: end if;
574: l_last_logon_date := to_date(X_LAST_LOGON_DATE, 'YYYY/MM/DD');
575: end if;
576:
577: if (X_PASSWORD_ACCESSES_LEFT = fnd_user_pkg.null_char) then
578: l_password_accesses_left := fnd_user_pkg.null_number;
579: else
580: l_password_accesses_left := to_number(X_PASSWORD_ACCESSES_LEFT);
581: end if;
582:
579: else
580: l_password_accesses_left := to_number(X_PASSWORD_ACCESSES_LEFT);
581: end if;
582:
583: if (X_PASSWORD_LIFESPAN_ACCESSES = fnd_user_pkg.null_char) then
584: l_password_lifespan_accesses := fnd_user_pkg.null_number;
585: else
586: l_password_lifespan_accesses := to_number(X_PASSWORD_LIFESPAN_ACCESSES);
587: end if;
580: l_password_accesses_left := to_number(X_PASSWORD_ACCESSES_LEFT);
581: end if;
582:
583: if (X_PASSWORD_LIFESPAN_ACCESSES = fnd_user_pkg.null_char) then
584: l_password_lifespan_accesses := fnd_user_pkg.null_number;
585: else
586: l_password_lifespan_accesses := to_number(X_PASSWORD_LIFESPAN_ACCESSES);
587: end if;
588:
585: else
586: l_password_lifespan_accesses := to_number(X_PASSWORD_LIFESPAN_ACCESSES);
587: end if;
588:
589: if (X_PASSWORD_LIFESPAN_DAYS = fnd_user_pkg.null_char) then
590: l_password_lifespan_days := fnd_user_pkg.null_number;
591: else
592: l_password_lifespan_days := to_number(X_PASSWORD_LIFESPAN_DAYS);
593: end if;
586: l_password_lifespan_accesses := to_number(X_PASSWORD_LIFESPAN_ACCESSES);
587: end if;
588:
589: if (X_PASSWORD_LIFESPAN_DAYS = fnd_user_pkg.null_char) then
590: l_password_lifespan_days := fnd_user_pkg.null_number;
591: else
592: l_password_lifespan_days := to_number(X_PASSWORD_LIFESPAN_DAYS);
593: end if;
594:
594:
595: begin
596: select LAST_UPDATED_BY, LAST_UPDATE_DATE
597: into db_luby, db_ludate
598: from FND_USER
599: where USER_NAME = X_USER_NAME;
600:
601: /* PARTY */
602: l_person_party_id := null;
640: exception
641: when no_data_found then
642: -- bug 4047740 changed dummy password from 'welcome' to new FND_WEB_SEC
643: -- constant, requires AFSCJAVS.pls 115.27 and AFSCJAVB.pls 115.63
644: fnd_user_pkg.createuser(
645: X_USER_NAME,
646: X_OWNER, -- bug 7687370
647: FND_WEB_SEC.INVALID_PWD,
648: to_number(X_SESSION_NUMBER),
729:
730: if (ret = 'Y') then
731: -- Enhancement 5027812
732: if ((x_change_source is null or
733: x_change_source <> fnd_user_pkg.CHANGE_SOURCE_OID) and
734: pwd <> fnd_web_sec.external_pwd) then
735: -- We need to translate the external constant to null. Otherwise
736: -- fnd_ldap_wrapper.create_user will use that constant as real password.
737: if (pwd = fnd_web_sec.external_pwd) then
789: end if;
790:
791: -- the createuser java code uses the user_id as created_by which
792: -- is not correct. We must correct here.
793: update fnd_user
794: set created_by = owner_id
795: where user_name = upper(x_user_name);
796:
797: -- update the rest of the data except password
797: -- update the rest of the data except password
798: if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
799: fnd_log.string(FND_LOG.LEVEL_STATEMENT,
800: c_log_head || l_api_name || '.call_upduser',
801: 'CreateUser(): calling fnd_user_pkg.UpdateUser');
802: end if;
803:
804: -- NOTE: This bit is using UpdateUser (rather than direct sql update)
805: -- so that the user synch code in UpdateUser is triggered.
805: -- so that the user synch code in UpdateUser is triggered.
806: -- Don't change this without taking that into account.
807: --
808: if (x_mode = 'PARTY') then
809: -- fnd_user_pkg.UpdateUserParty(
810: -- Begin bug 4318754, 4424225
811: -- In order to handle user_guid, calls UpdateUserInternal directly
812: -- instead of UpdateUserParty which does not take user_guid as argument.
813: fnd_user_pkg.UpdateUserInternal(
809: -- fnd_user_pkg.UpdateUserParty(
810: -- Begin bug 4318754, 4424225
811: -- In order to handle user_guid, calls UpdateUserInternal directly
812: -- instead of UpdateUserParty which does not take user_guid as argument.
813: fnd_user_pkg.UpdateUserInternal(
814: -- End bug 4318754, 4424225
815: x_user_name => x_user_name,
816: x_owner => x_owner,
817: x_unencrypted_password => l_oid_pwd,
832: x_person_party_id => x_person_party_id,
833: x_old_password => null,
834: x_mode => 'PARTY',
835: x_user_guid => l_user_guid,
836: x_change_source => fnd_user_pkg.change_source_oid);
837: else
838: fnd_user_pkg.UpdateUser(
839: x_user_name => x_user_name,
840: x_owner => x_owner,
834: x_mode => 'PARTY',
835: x_user_guid => l_user_guid,
836: x_change_source => fnd_user_pkg.change_source_oid);
837: else
838: fnd_user_pkg.UpdateUser(
839: x_user_name => x_user_name,
840: x_owner => x_owner,
841: x_unencrypted_password => l_oid_pwd,
842: x_session_number => x_session_number,
854: x_customer_id => x_customer_id,
855: x_supplier_id => x_supplier_id,
856: x_old_password => null,
857: x_user_guid => l_user_guid,
858: x_change_source => fnd_user_pkg.change_source_oid);
859: end if;
860:
861: else -- fnd_web_sec.create_user must have failed.
862: -- The java layer puts message onto the message stack.
873:
874: ----------------------------------------------------------------------
875: --
876: -- CreateUserId (PUBLIC)
877: -- Insert new user record into FND_USER table.
878: -- If that user exists already, exception raised with the error message.
879: -- There are three input arguments must be provided. All the other columns
880: -- in FND_USER table can take the default value.
881: --
876: -- CreateUserId (PUBLIC)
877: -- Insert new user record into FND_USER table.
878: -- If that user exists already, exception raised with the error message.
879: -- There are three input arguments must be provided. All the other columns
880: -- in FND_USER table can take the default value.
881: --
882: -- *** NOTE: This version accepts the old customer_id/employee_id
883: -- keys foreign keys to the "person". Use CreateUserIdParty to create
884: -- a user with the new person_party_id key.
934:
935: ----------------------------------------------------------------------
936: --
937: -- CreateUserIdParty (PUBLIC)
938: -- Insert new user record into FND_USER table.
939: -- If that user exists already, exception raised with the error message.
940: -- There are three input arguments must be provided. All the other columns
941: -- in FND_USER table can take the default value.
942: --
937: -- CreateUserIdParty (PUBLIC)
938: -- Insert new user record into FND_USER table.
939: -- If that user exists already, exception raised with the error message.
940: -- There are three input arguments must be provided. All the other columns
941: -- in FND_USER table can take the default value.
942: --
943: -- *** NOTE: This version accepts the new person_party_id foreign key
944: -- to the "person". Use CreateUserId to create a user with the old
945: -- customer_id/employee_id keys.
993:
994: ----------------------------------------------------------------------
995: --
996: -- CreateUser (PUBLIC)
997: -- Insert new user record into FND_USER table.
998: -- If that user exists already, exception raised with the error message.
999: -- There are three input arguments must be provided. All the other columns
1000: -- in FND_USER table can take the default value.
1001: --
996: -- CreateUser (PUBLIC)
997: -- Insert new user record into FND_USER table.
998: -- If that user exists already, exception raised with the error message.
999: -- There are three input arguments must be provided. All the other columns
1000: -- in FND_USER table can take the default value.
1001: --
1002: -- *** NOTE: This version accepts the old customer_id/employee_id
1003: -- keys foreign keys to the "person". Use CreateUserParty to create
1004: -- a user with the new person_party_id key.
1028: x_supplier_id in number default null)
1029: is
1030: dummy number;
1031: begin
1032: dummy := fnd_user_pkg.CreateUserId(
1033: x_user_name,
1034: x_owner,
1035: x_unencrypted_password,
1036: x_session_number,
1051:
1052: ----------------------------------------------------------------------
1053: --
1054: -- CreateUserParty (PUBLIC)
1055: -- Insert new user record into FND_USER table.
1056: -- If that user exists already, exception raised with the error message.
1057: -- There are three input arguments must be provided. All the other columns
1058: -- in FND_USER table can take the default value.
1059: --
1054: -- CreateUserParty (PUBLIC)
1055: -- Insert new user record into FND_USER table.
1056: -- If that user exists already, exception raised with the error message.
1057: -- There are three input arguments must be provided. All the other columns
1058: -- in FND_USER table can take the default value.
1059: --
1060: -- *** NOTE: This version accepts the new person_party_id foreign key
1061: -- to the "person". Use CreateUser to create a user with the old
1062: -- customer_id/employee_id keys.
1084: x_person_party_id in number default null)
1085: is
1086: dummy number;
1087: begin
1088: dummy := fnd_user_pkg.CreateUserIdParty(
1089: x_user_name,
1090: x_owner,
1091: x_unencrypted_password,
1092: x_session_number,
1114: -- keys foreign keys to the "person". Use UpdateUserParty to update
1115: -- a user with the new person_party_id key.
1116: --
1117: -- Usage Example in pl/sql
1118: -- begin fnd_user_pkg.updateuser('SCOTT', 'SEED', 'DRAGON'); end;
1119: --
1120: -- Mandatory Input Arguments
1121: -- x_user_name: An existing user name
1122: -- x_owner: 'SEED' or 'CUST'(customer)
1176: -- to the "person". Use UpdateUser to update a user with the old
1177: -- customer_id/employee_id keys.
1178: --
1179: -- Usage Example in pl/sql
1180: -- begin fnd_user_pkg.updateuser('SCOTT', 'SEED', 'DRAGON'); end;
1181: --
1182: -- Mandatory Input Arguments
1183: -- x_user_name: An existing user name
1184: -- x_owner: 'SEED' or 'CUST'(customer)
1265: end if;
1266: begin
1267: select 'Y'
1268: into exists_flag
1269: from fnd_user u
1270: where u.user_name = x_user_name;
1271: exception
1272: when no_data_found then
1273: exists_flag := 'N';
1273: exists_flag := 'N';
1274: end;
1275:
1276: if (exists_flag = 'Y') then
1277: fnd_user_pkg.UpdateUser(x_user_name,
1278: x_owner,
1279: x_unencrypted_password,
1280: x_session_number,
1281: x_start_date,
1300: fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1301: c_log_head || l_api_name || '.call_creatuser',
1302: 'LoadUser(): calling CreateUser');
1303: end if;
1304: fnd_user_pkg.createuser(x_user_name,
1305: x_owner,
1306: x_unencrypted_password,
1307: x_session_number,
1308: x_start_date,
1320: x_supplier_id);
1321: else
1322: fnd_message.set_name('FND', 'FND_NO_PASSWORD_PROVIDED');
1323: fnd_message.set_token('USER_NAME', X_USER_NAME);
1324: fnd_message.set_token('ROUTINE', 'FND_USER_PKG.LOADUSER');
1325: app_exception.raise_exception;
1326: end if;
1327: end if;
1328: end LoadUser;
1365: end if;
1366: begin
1367: select 'Y'
1368: into exists_flag
1369: from fnd_user u
1370: where u.user_name = x_user_name;
1371: exception
1372: when no_data_found then
1373: exists_flag := 'N';
1373: exists_flag := 'N';
1374: end;
1375:
1376: if (exists_flag = 'Y') then
1377: fnd_user_pkg.UpdateUserParty(x_user_name,
1378: x_owner,
1379: x_unencrypted_password,
1380: x_session_number,
1381: x_start_date,
1398: fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1399: c_log_head || l_api_name || '.call_creatuser',
1400: 'LoadUser(): calling CreateUser');
1401: end if;
1402: fnd_user_pkg.createuserparty(x_user_name,
1403: x_owner,
1404: x_unencrypted_password,
1405: x_session_number,
1406: x_start_date,
1416: x_person_party_id);
1417: else
1418: fnd_message.set_name('FND', 'FND_NO_PASSWORD_PROVIDED');
1419: fnd_message.set_token('USER_NAME', X_USER_NAME);
1420: fnd_message.set_token('ROUTINE', 'FND_USER_PKG.LOADUSER');
1421: app_exception.raise_exception;
1422: end if;
1423: end if;
1424: end LoadUserParty;
1430: -- You longer can log in as this user anymore. If username is not valid,
1431: -- exception raised with error message.
1432: --
1433: -- Usage example in pl/sql
1434: -- begin fnd_user_pkg.disableuser('SCOTT'); end;
1435: --
1436: -- Input (Mandatory)
1437: -- username: User Name
1438: --
1442: -- here we do not disable Guest account
1443: if( upper(username) = 'GUEST' ) then
1444: return;
1445: end if;
1446: fnd_user_pkg.UpdateUser(x_user_name => DisableUser.username,
1447: x_owner => null,
1448: x_end_date => sysdate);
1449: end DisableUser;
1450: ----------------------------------------------------------------------------
1452: -- ValidateLogin (PUBLIC)
1453: -- Test if password is good for this given user.
1454: --
1455: -- Usage example in pl/sql
1456: -- begin fnd_user_pkg.validatelogin('SCOTT', 'TIGER'); end;
1457: --
1458: -- Input (Mandatory)
1459: -- username: User Name
1460: -- password: User Password
1470: -- Set new password for a given user without having to provide
1471: -- the old password.
1472: --
1473: -- Usage example in pl/sql
1474: -- begin fnd_user_pkg.changepassword('SCOTT', 'WELCOME'); end;
1475: --
1476: -- Input (Mandatory)
1477: -- username: User Name
1478: -- newpassword New Password
1488: -- Set new password for a given user if the existing password needed to be
1489: -- validated before changing to the new password.
1490: --
1491: -- Usage example in pl/sql
1492: -- begin fnd_user_pkg.changepassword('SCOTT', 'TIGER', 'WELCOME'); end;
1493: --
1494: -- Input (Mandatory)
1495: -- username: User Name
1496: -- oldpassword Old Password
1507: ----------------------------------------------------------------------------
1508: --
1509: -- GetReEncryptedPassword (PUBLIC)
1510: -- Return user password encrypted with new key. This just returns the
1511: -- newly encrypted password. It does not set the password in FND_USER table.
1512: --
1513: -- Usage example in pl/sql
1514: -- declare
1515: -- newpass varchar2(100);
1513: -- Usage example in pl/sql
1514: -- declare
1515: -- newpass varchar2(100);
1516: -- begin
1517: -- newpass := fnd_user_pkg.getreencryptedpassword('SCOTT', 'NEWKEY'); end;
1518: -- end;
1519: --
1520: -- Input (Mandatory)
1521: -- username: User Name
1529:
1530: ----------------------------------------------------------------------------
1531: -- SetReEncryptedPassword (PUBLIC)
1532: -- Set user password from value returned from GetReEncryptedPassword.
1533: -- This is to update column ENCRYPTED_USER_PASSWORD in table FND_USER
1534: --
1535: -- Usage example in pl/sql
1536: -- declare
1537: -- newpass varchar2(100);
1535: -- Usage example in pl/sql
1536: -- declare
1537: -- newpass varchar2(100);
1538: -- begin
1539: -- newpass := fnd_user_pkg.getreencryptedpassword('SCOTT', 'NEWKEY'); end;
1540: -- fnd_user_pkg.setreencryptedpassword('SCOTT', newpass, 'NEWKEY'); end;
1541: -- end;
1542: --
1543: -- Input (Mandatory)
1536: -- declare
1537: -- newpass varchar2(100);
1538: -- begin
1539: -- newpass := fnd_user_pkg.getreencryptedpassword('SCOTT', 'NEWKEY'); end;
1540: -- fnd_user_pkg.setreencryptedpassword('SCOTT', newpass, 'NEWKEY'); end;
1541: -- end;
1542: --
1543: -- Input (Mandatory)
1544: -- username: User Name
1553: end;
1554: ----------------------------------------------------------------------------
1555: -- MergeCustomer (PUBLIC)
1556: -- This is the procedure being called during the Party Merge.
1557: -- FND_USER.MergeCustomer() has been registered in Party Merge Data Dict.
1558: -- The input/output arguments format matches the document PartyMergeDD.doc.
1559: -- The goal is to fix the customer id in fnd_user table to point to the
1560: -- same party when two similar parties are begin merged.
1561: --
1555: -- MergeCustomer (PUBLIC)
1556: -- This is the procedure being called during the Party Merge.
1557: -- FND_USER.MergeCustomer() has been registered in Party Merge Data Dict.
1558: -- The input/output arguments format matches the document PartyMergeDD.doc.
1559: -- The goal is to fix the customer id in fnd_user table to point to the
1560: -- same party when two similar parties are begin merged.
1561: --
1562: -- Usage example in pl/sql
1563: -- This procedure should only be called from the PartyMerge utility.
1575: p_return_status := FND_API.G_RET_STS_SUCCESS;
1576:
1577: if (p_from_fk_id <> p_to_fk_id) then
1578:
1579: update fnd_user
1580: set customer_id = p_to_fk_id
1581: where customer_id = p_from_fk_id;
1582:
1583: -- Added for Function Security Cache Invalidation
1591: --
1592: -- user_change - The rule function for FND's subscription on the
1593: -- oracle.apps.wf.entmgr.user.change event. This function
1594: -- retrieves the user's information and updates the
1595: -- corresponding fnd_user as needed, if the user exists.
1596: --
1597: FUNCTION user_change(p_subscription_guid in raw,
1598: p_event in out nocopy wf_event_t)
1599: return varchar2 is
1625: nvl(email_address, '*NULL*'),
1626: nvl(fax, '*NULL*'),
1627: user_name,
1628: user_id
1629: from fnd_user
1630: where user_guid = userGuid;
1631:
1632: begin
1633:
1661: end if;
1662:
1663: if (my_mode = 'DELETE') then
1664: begin
1665: fnd_user_pkg.disableUser(my_username);
1666: exception
1667: when others then null;
1668: end;
1669:
1679: nvl(email_address, '*NULL*'),
1680: nvl(fax, '*NULL*'),
1681: user_guid
1682: into old_desc, old_email, old_fax, old_guid
1683: from fnd_user
1684: where user_name = my_username;
1685: exception
1686: when others then return 'SUCCESS';
1687: end;
1739: end if;
1740: --End bug 3147423
1741: -- end of conversions --
1742:
1743: fnd_user_pkg.UpdateUser(
1744: x_user_name => my_username,
1745: x_owner => 'CUST',
1746: x_description => new_desc2,
1747: x_email_address => new_email2,
1748: x_fax => new_fax2,
1749: -- This api is called by LDAP so pass the change source so that we don't
1750: -- start the synch loop.
1751: x_user_guid => old_guid,
1752: x_change_source => fnd_user_pkg.change_source_oid);
1753:
1754: if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
1755: fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
1756: 'LDAP - finished fnd_user_pkg.UpdateUser('||my_username||')');
1752: x_change_source => fnd_user_pkg.change_source_oid);
1753:
1754: if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
1755: fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
1756: 'LDAP - finished fnd_user_pkg.UpdateUser('||my_username||')');
1757: end if;
1758: end if;
1759: end if;
1760:
1795: if ( (l_allow_sync = 'Y') and (l_local_login <> 'LOCAL') ) then
1796:
1797: if (p_event.GetValueForParameter('CHANGE_TYPE') = 'DELETE') then
1798: begin
1799: fnd_user_pkg.disableUser(my_user);
1800: exception
1801: when others then null;
1802: end;
1803:
1808: --
1809: -- NOTE: While we have the ability to distinguish between null
1810: -- and "known to be null", the standard apis do not.
1811: -- For now, we're must pass null regardless which is
1812: -- treated as a "don't change". fnd_user_pkg apis do
1813: -- not yet support the ability to "null out" an
1814: -- attribute value. Weak.
1815: --
1816: new_desc := wf_entity_mgr.get_attribute_value(my_ent_type, my_cachekey,
1838: if (new_email in ('*NULL*', '*UNKNOWN*')) then
1839: new_email := null_char;
1840: end if;
1841:
1842: fnd_user_pkg.UpdateUser(
1843: x_user_name => my_user,
1844: x_owner => 'CUST',
1845: x_description => new_desc,
1846: x_email_address => new_email,
1847: x_fax => new_fax);
1848:
1849: if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
1850: fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
1851: 'OID - finished fnd_user_pkg.UpdateUser('||my_user||')');
1852: end if;
1853: end if;
1854: end if;
1855: else
1870: --
1871: -- user_create_rf - The rule function for FND's 2nd subscription on the
1872: -- oracle.apps.wf.entmgr.user.change event. This function
1873: -- retrieves the user's information and creates the
1874: -- corresponding fnd_user if the user does not already exist.
1875: --
1876: FUNCTION user_create_rf(p_subscription_guid in raw,
1877: p_event in out nocopy wf_event_t)
1878: return varchar2
1917: end if;
1918:
1919: begin
1920: select 'Y' into existing_user
1921: from fnd_user
1922: where user_name = my_username;
1923:
1924: return 'SUCCESS';
1925: exception
1969: --End Bug 3147423
1970:
1971: -- end of conversions --
1972:
1973: fnd_user_pkg.CreateUser(
1974: x_user_name => my_username,
1975: x_owner => 'CUST',
1976: x_unencrypted_password => null,
1977: x_description => new_desc2,
1979: x_fax => new_fax2);
1980:
1981: if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
1982: fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
1983: 'LDAP - finished fnd_user_pkg.CreateUser('||my_username||')');
1984: end if;
1985: end;
1986:
1987: -- If CHANGE_SOURCE is OID, it means it's raised from two-way sync code.
2000: 'OID - guid ='||new_guid);
2001: end if;
2002: begin
2003: select 'Y' into existing_user
2004: from fnd_user
2005: where user_name = my_cachekey;
2006:
2007: return 'SUCCESS';
2008:
2026: if (new_email in ('*NULL*', '*UNKNOWN*')) then
2027: new_email := null;
2028: end if;
2029:
2030: fnd_user_pkg.CreateUser(
2031: x_user_name => my_cachekey,
2032: x_owner => 'CUST',
2033: x_unencrypted_password => null,
2034: x_description => new_desc,
2036: x_fax => new_fax);
2037:
2038: if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
2039: fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
2040: 'OID - finished fnd_user_pkg.CreateUser('||my_cachekey||')');
2041: end if;
2042:
2043: --
2044: -- guid will eventually be managed centrally so didn't want to add it
2041: end if;
2042:
2043: --
2044: -- guid will eventually be managed centrally so didn't want to add it
2045: -- to the fnd_user_pkg apis...hence the separate update here
2046: --
2047: update fnd_user set user_guid = new_guid
2048: where user_name = my_cachekey;
2049:
2043: --
2044: -- guid will eventually be managed centrally so didn't want to add it
2045: -- to the fnd_user_pkg apis...hence the separate update here
2046: --
2047: update fnd_user set user_guid = new_guid
2048: where user_name = my_cachekey;
2049:
2050: end;
2051: end if;
2083: if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
2084: fnd_log.string(FND_LOG.LEVEL_STATEMENT, C_LOG_HEAD || l_api_name || '.',
2085: tmpbuf);
2086: end if;
2087: ret := fnd_dictionary_pkg.updatepkcolumns('FND', 'FND_USER', colnames, colold,
2088: colnew);
2089: tmpbuf := 'Finished fnd_dictionary_pkg.updatepkcolumns';
2090: if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
2091: fnd_log.string(FND_LOG.LEVEL_STATEMENT, C_LOG_HEAD || l_api_name || '.',
2149: to_char(start_date, 'YYYYMMDDHH24MISS'),
2150: to_char(end_date, 'YYYYMMDDHH24MISS')
2151: into my_userid, my_email, my_desc, my_fax, my_empid, my_partyid,
2152: my_start, my_exp, my_guid, my_pwd, ch_start, ch_exp
2153: from fnd_user
2154: where user_name = upper(p_user_name);
2155:
2156: -- construct attribute list for wf synch --
2157: -- unstubbed the code
2197:
2198: -- begin bug 2504562
2199: wf_event.AddParameterToList('OLD_USER_NAME', g_old_user_name, mylist);
2200: /* set g_old_user_name to null here to cover calls to user_synch initiated
2201: from Forms or from the fnd_user_pkg.change_user_name PL/SQL api */
2202: --Comment out this following call because I need it to do the pk children
2203: --later. After pk children update, then I will reset g_old_user_name.
2204: --dummy := fnd_user_pkg.set_old_user_name(NULL);
2205: -- end bug 2504562
2200: /* set g_old_user_name to null here to cover calls to user_synch initiated
2201: from Forms or from the fnd_user_pkg.change_user_name PL/SQL api */
2202: --Comment out this following call because I need it to do the pk children
2203: --later. After pk children update, then I will reset g_old_user_name.
2204: --dummy := fnd_user_pkg.set_old_user_name(NULL);
2205: -- end bug 2504562
2206:
2207: --
2208: -- Added calls for the lang/territory and notification preference.
2295: if (g_old_user_name is not null) then
2296: UpdateUsernameChildren(g_old_user_name, p_user_name);
2297: end if;
2298: -- set g_old_user_name to null here to cover calls to user_synch initiated
2299: -- from Forms or from the fnd_user_pkg.change_user_name PL/SQL api
2300: dummy := fnd_user_pkg.set_old_user_name(NULL);
2301: dummy := fnd_user_pkg.set_old_person_party_id(NULL);
2302: dummy := fnd_user_pkg.set_old_user_guid(NULL);
2303:
2296: UpdateUsernameChildren(g_old_user_name, p_user_name);
2297: end if;
2298: -- set g_old_user_name to null here to cover calls to user_synch initiated
2299: -- from Forms or from the fnd_user_pkg.change_user_name PL/SQL api
2300: dummy := fnd_user_pkg.set_old_user_name(NULL);
2301: dummy := fnd_user_pkg.set_old_person_party_id(NULL);
2302: dummy := fnd_user_pkg.set_old_user_guid(NULL);
2303:
2304: if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
2297: end if;
2298: -- set g_old_user_name to null here to cover calls to user_synch initiated
2299: -- from Forms or from the fnd_user_pkg.change_user_name PL/SQL api
2300: dummy := fnd_user_pkg.set_old_user_name(NULL);
2301: dummy := fnd_user_pkg.set_old_person_party_id(NULL);
2302: dummy := fnd_user_pkg.set_old_user_guid(NULL);
2303:
2304: if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
2305: fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
2298: -- set g_old_user_name to null here to cover calls to user_synch initiated
2299: -- from Forms or from the fnd_user_pkg.change_user_name PL/SQL api
2300: dummy := fnd_user_pkg.set_old_user_name(NULL);
2301: dummy := fnd_user_pkg.set_old_person_party_id(NULL);
2302: dummy := fnd_user_pkg.set_old_user_guid(NULL);
2303:
2304: if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
2305: fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
2306: 'End user_synch');
2306: 'End user_synch');
2307: end if;
2308: exception
2309: -- Bug 3617474: This NO_DATA_FOUND exception handler was placed in the event
2310: -- that fnd_user_pkg.user_synch() was passed an invalid or null user_name,
2311: -- i.e a user_name that does not exist in fnd_user.
2312: when NO_DATA_FOUND then
2313: null;
2314:
2307: end if;
2308: exception
2309: -- Bug 3617474: This NO_DATA_FOUND exception handler was placed in the event
2310: -- that fnd_user_pkg.user_synch() was passed an invalid or null user_name,
2311: -- i.e a user_name that does not exist in fnd_user.
2312: when NO_DATA_FOUND then
2313: null;
2314:
2315: end user_synch;
2321: -- If any of the username or application short name or responsibility key or
2322: -- security group is not valid, exception raised with error message.
2323: --
2324: -- Usage example in pl/sql
2325: -- begin fnd_user_pkg.delresp('SCOTT', 'FND', 'APPLICATION_DEVELOPER',
2326: -- 'STANDARD'); end;
2327: -- Input (Mandatory)
2328: -- username: User Name
2329: -- resp_app: Application Short Name
2341: startdate date;
2342: begin
2343: begin
2344: select user_id into userid
2345: from fnd_user
2346: where user_name = DelResp.username;
2347:
2348: select application_id into appid
2349: from fnd_application
2349: from fnd_application
2350: where application_short_name = DelResp.Resp_app;
2351:
2352: /* Bug4600645 - Modified to get actual start_date instead of using */
2353: /* sysdate in call to fnd_user_resp_groups_api.update_assignment */
2354:
2355: select responsibility_id
2356: into respid
2357: from fnd_responsibility
2364:
2365: /*Bug4600645 - Get actual start_date value*/
2366:
2367: select start_date into startdate
2368: from fnd_user_resp_groups_all
2369: where user_id = userid
2370: and responsibility_id = respid
2371: and responsibility_application_id = appid;
2372:
2379: fnd_message.set_token('SECURITY_GROUP', security_group);
2380: app_exception.raise_exception;
2381: end;
2382:
2383: if (fnd_user_resp_groups_api.assignment_exists(
2384: userid, respid, appid, secid)) then
2385: fnd_user_resp_groups_api.update_assignment(
2386: user_id => userid,
2387: responsibility_id => respid,
2381: end;
2382:
2383: if (fnd_user_resp_groups_api.assignment_exists(
2384: userid, respid, appid, secid)) then
2385: fnd_user_resp_groups_api.update_assignment(
2386: user_id => userid,
2387: responsibility_id => respid,
2388: responsibility_application_id => appid,
2389: security_group_id => secid,
2400: -- If user name or application short name or responsbility key name
2401: -- or security group key is not valid, exception raised with error message.
2402: --
2403: -- Usage example in pl/sql
2404: -- begin fnd_user_pkg.addresp('SCOTT', 'FND', 'APPLICATION_DEVELOPER',
2405: -- 'STANDARD', 'DESCRIPTION', sysdate, null); end;
2406: -- Input (Mandatory)
2407: -- username: User Name
2408: -- resp_app: Application Short Name
2426: begin
2427:
2428: begin
2429: select user_id into userid
2430: from fnd_user
2431: where user_name = AddResp.username;
2432:
2433: select application_id into appid
2434: from fnd_application
2452: fnd_message.set_token('SECURITY_GROUP', security_group);
2453: app_exception.raise_exception;
2454: end;
2455:
2456: if (fnd_user_resp_groups_api.assignment_exists(
2457: userid, respid, appid, secid)) then
2458: fnd_user_resp_groups_api.update_assignment(
2459: user_id => userid,
2460: responsibility_id => respid,
2454: end;
2455:
2456: if (fnd_user_resp_groups_api.assignment_exists(
2457: userid, respid, appid, secid)) then
2458: fnd_user_resp_groups_api.update_assignment(
2459: user_id => userid,
2460: responsibility_id => respid,
2461: responsibility_application_id => appid,
2462: security_group_id => secid,
2463: start_date => AddResp.start_date,
2464: end_date => AddResp.end_date,
2465: description => AddResp.description);
2466: else
2467: fnd_user_resp_groups_api.insert_assignment(
2468: user_id => userid,
2469: responsibility_id => respid,
2470: responsibility_application_id => appid,
2471: security_group_id => secid,
2489: end isPasswordChangeable;
2490:
2491: -------------------------------------------------------------------
2492: -- Name: UpdatePassword_WF
2493: -- Description: Calls FND_USER_PKG.UpdateUser
2494: -------------------------------------------------------------------
2495: Procedure UpdatePassword_WF(itemtype in varchar2,
2496: itemkey in varchar2,
2497: actid in number,
2500:
2501: begin
2502:
2503: if (funcmode = 'RUN') then
2504: FND_USER_PKG.UpdateUser(
2505: x_user_name=>
2506: WF_ENGINE.GetActivityAttrText(itemtype, itemkey, actid,
2507: 'X_USER_NAME'),
2508: x_owner=>'CUST',
2883: -- You can log in as this user from now.
2884: -- If username is not valid, exception raised with error message.
2885: --
2886: -- Usage example in pl/sql
2887: -- begin fnd_user_pkg.enableuser('SCOTT'); end;
2888: -- begin fnd_user_pkg.enableuser('SCOTT', sysdate+1, sysdate+30); end;
2889: --
2890: -- Input (Mandatory)
2891: -- username: User Name
2884: -- If username is not valid, exception raised with error message.
2885: --
2886: -- Usage example in pl/sql
2887: -- begin fnd_user_pkg.enableuser('SCOTT'); end;
2888: -- begin fnd_user_pkg.enableuser('SCOTT', sysdate+1, sysdate+30); end;
2889: --
2890: -- Input (Mandatory)
2891: -- username: User Name
2892: -- Input (Non-Mandatory)
2894: -- end_date: End Date
2895: --
2896: procedure EnableUser(username varchar2,
2897: start_date date default sysdate,
2898: end_date date default fnd_user_pkg.null_date) is
2899: begin
2900: fnd_user_pkg.UpdateUser(x_user_name => EnableUser.username,
2901: x_owner => NULL,
2902: x_start_date => EnableUser.start_date,
2896: procedure EnableUser(username varchar2,
2897: start_date date default sysdate,
2898: end_date date default fnd_user_pkg.null_date) is
2899: begin
2900: fnd_user_pkg.UpdateUser(x_user_name => EnableUser.username,
2901: x_owner => NULL,
2902: x_start_date => EnableUser.start_date,
2903: x_end_date => EnableUser.end_date);
2904: end EnableUser;
2913: -- UMX with an aproval process.
2914: --
2915: --
2916: -- Usage example in pl/sql
2917: -- begin fnd_user_pkg.creatependinguser('SCOTT', 'SEED', 'welcome'); end;
2918: -- begin fnd_user_pkg.creatependinguser('SCOTT', 'SEED'); end;
2919: --
2920: -- Input (Mandatory)
2921: -- x_user_name: User Name
2914: --
2915: --
2916: -- Usage example in pl/sql
2917: -- begin fnd_user_pkg.creatependinguser('SCOTT', 'SEED', 'welcome'); end;
2918: -- begin fnd_user_pkg.creatependinguser('SCOTT', 'SEED'); end;
2919: --
2920: -- Input (Mandatory)
2921: -- x_user_name: User Name
2922: -- x_owner: 'SEED' or 'CUST'(customer)
2936: x_person_party_id in number default null) return number
2937: is
2938: uid number;
2939: begin
2940: uid := fnd_user_pkg.CreateUserIdParty(
2941: x_user_name,
2942: x_owner,
2943: x_unencrypted_password,
2944: x_session_number,
2961:
2962: ----------------------------------------------------------------------------
2963: --
2964: -- RemovePendingUser (PUBLIC)
2965: -- Delete this user from fnd_user table only if this is a pending user.
2966: -- If this is not a valid username or is not a pending user, raise error.
2967: -- Pending user is created when a user registers a user account through
2968: -- UMX with an aproval process.
2969: --
2967: -- Pending user is created when a user registers a user account through
2968: -- UMX with an aproval process.
2969: --
2970: -- Usage example in pl/sql
2971: -- begin fnd_user_pkg.removependinguser('SCOTT'); end;
2972: --
2973: -- Input (Mandatory)
2974: -- username: User Name
2975: --
2983: -- RSHEH: Need to have exception trapping for no_data_found exception
2984: begin
2985: select user_id, user_guid
2986: into l_user_id, l_user_guid
2987: from fnd_user
2988: where user_name = upper(username)
2989: and to_char(start_date) = to_char(FND_API.G_MISS_DATE)
2990: and to_char(end_date) = to_char(FND_API.G_MISS_DATE);
2991:
3009: end;
3010: end if;
3011:
3012: -- Only allow to delete a PENDING user
3013: delete from fnd_user
3014: where user_name = upper(username)
3015: and to_char(start_date) = to_char(FND_API.G_MISS_DATE)
3016: and to_char(end_date) = to_char(FND_API.G_MISS_DATE);
3017:
3031: -- AssignPartyToUser (PUBLIC)
3032: -- Assign a TCA party to a given user
3033: --
3034: -- Usage example in pl/sql
3035: -- begin fnd_user_pkg.assignpartytouser('SCOTT', 1001); end;
3036: --
3037: -- Input (Mandatory)
3038: -- x_user_name: User Name
3039: -- x_party_id: Party Name Id
3059: fnd_message.set_token('PARTY_NAME', x_party);
3060: app_exception.raise_exception;
3061: end;
3062:
3063: fnd_user_pkg.UpdateUserParty(x_user_name => x_user_name,
3064: x_owner => 'SEED',
3065: x_person_party_id => pid);
3066: */
3067:
3064: x_owner => 'SEED',
3065: x_person_party_id => pid);
3066: */
3067:
3068: fnd_user_pkg.UpdateUserParty(x_user_name => x_user_name,
3069: x_owner => 'SEED',
3070: x_person_party_id => x_party_id);
3071:
3072: end AssignPartyToUser;
3106: -- This api changes username, deals with encryption changes and
3107: -- update foreign keys that were using the old username.
3108: --
3109: -- Usage example in pl/sql
3110: -- begin fnd_user_pkg.change_user_name('SOCTT', 'SCOTT'); end;
3111: --
3112: -- Input (Mandantory)
3113: -- x_old_user_name: Old User Name
3114: -- x_new_user_name: New User Name
3125: tmpbuf varchar2(240);
3126: reason varchar2(240);
3127: encpwd varchar2(100);
3128: begin
3129: -- ensure x_old_user_name exists in fnd_user before we proceed
3130: select USER_GUID, encrypted_user_password
3131: into l_user_guid, encpwd
3132: from fnd_user
3133: where user_name = upper(x_old_user_name);
3128: begin
3129: -- ensure x_old_user_name exists in fnd_user before we proceed
3130: select USER_GUID, encrypted_user_password
3131: into l_user_guid, encpwd
3132: from fnd_user
3133: where user_name = upper(x_old_user_name);
3134:
3135: begin
3136: -- ensure x_new_user_name doesn't already exist in fnd_user
3132: from fnd_user
3133: where user_name = upper(x_old_user_name);
3134:
3135: begin
3136: -- ensure x_new_user_name doesn't already exist in fnd_user
3137: select null into dummy from fnd_user
3138: where user_name = upper(x_new_user_name);
3139: fnd_message.set_name('FND', 'SECURITY-DUPLICATE USER');
3140: app_exception.raise_exception;
3133: where user_name = upper(x_old_user_name);
3134:
3135: begin
3136: -- ensure x_new_user_name doesn't already exist in fnd_user
3137: select null into dummy from fnd_user
3138: where user_name = upper(x_new_user_name);
3139: fnd_message.set_name('FND', 'SECURITY-DUPLICATE USER');
3140: app_exception.raise_exception;
3141: exception
3142: when no_data_found then
3143: -- Start bug 5866089 just adding the following if check
3144: if (x_change_source is null) then
3145: -- Start bug 4625235
3146: -- Move ldap_wrapper synch call to here before we do FND user update
3147: begin
3148: tmpbuf := 'Calling ldap_wrapper_change_user_name to change '||
3149: x_old_user_name|| ' to '|| x_new_user_name;
3150: if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
3167: -- capture x_old_user_name in package variable g_old_user_name
3168: g_old_user_name := upper(x_old_user_name);
3169:
3170: -- change old username to new username
3171: update fnd_user set user_name = upper(x_new_user_name)
3172: where user_name = upper(x_old_user_name);
3173:
3174: -- This code was moved before updating FND_USER due to a change in
3175: -- FND_WEB_SEC.change_password having an autonomous transaction pragma.
3170: -- change old username to new username
3171: update fnd_user set user_name = upper(x_new_user_name)
3172: where user_name = upper(x_old_user_name);
3173:
3174: -- This code was moved before updating FND_USER due to a change in
3175: -- FND_WEB_SEC.change_password having an autonomous transaction pragma.
3176: -- Password changes failed due to said change. See bug 2426407.
3177: -- handle password encryption with new username
3178: tmpbuf := 'Recrypting '||x_new_user_name|| ' password';
3182: end if;
3183:
3184: -- Only have to do password reencryption if is not EXTERNAL, INVALID or Hash mode
3185: if ( (encpwd not in ('EXTERNAL', 'INVALID')) and (substr(encpwd, 1, 1) <> 'X') ) then
3186: newpass := fnd_user_pkg.GetReEncryptedPassword(x_new_user_name, 'NEWKEY');
3187: ret := fnd_user_pkg.SetReEncryptedPassword(x_new_user_name,newpass,'NEWKEY');
3188: end if;
3189:
3190: tmpbuf := 'updating fnd_user for new user_name '||x_new_user_name;
3183:
3184: -- Only have to do password reencryption if is not EXTERNAL, INVALID or Hash mode
3185: if ( (encpwd not in ('EXTERNAL', 'INVALID')) and (substr(encpwd, 1, 1) <> 'X') ) then
3186: newpass := fnd_user_pkg.GetReEncryptedPassword(x_new_user_name, 'NEWKEY');
3187: ret := fnd_user_pkg.SetReEncryptedPassword(x_new_user_name,newpass,'NEWKEY');
3188: end if;
3189:
3190: tmpbuf := 'updating fnd_user for new user_name '||x_new_user_name;
3191: if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
3186: newpass := fnd_user_pkg.GetReEncryptedPassword(x_new_user_name, 'NEWKEY');
3187: ret := fnd_user_pkg.SetReEncryptedPassword(x_new_user_name,newpass,'NEWKEY');
3188: end if;
3189:
3190: tmpbuf := 'updating fnd_user for new user_name '||x_new_user_name;
3191: if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
3192: fnd_log.string(FND_LOG.LEVEL_STATEMENT, C_LOG_HEAD || l_api_name || '.',
3193: tmpbuf);
3194: end if;
3199: -- is stored) exception and returns FND_CHANGE_USER_FAILED error.
3200: -- Added for Function Security Cache Invalidation
3201: select user_id
3202: into l_user_id
3203: from fnd_user
3204: where user_name = upper(x_new_user_name);
3205:
3206: fnd_function_security_cache.update_user(l_user_id);
3207:
3205:
3206: fnd_function_security_cache.update_user(l_user_id);
3207:
3208: -- propagate username change to WF and entity mgr
3209: tmpbuf := 'Start calling fnd_user_pkg.user_synch('||x_new_user_name||')';
3210: if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
3211: fnd_log.string(FND_LOG.LEVEL_STATEMENT, C_LOG_HEAD || l_api_name || '.',
3212: tmpbuf);
3213: end if;
3211: fnd_log.string(FND_LOG.LEVEL_STATEMENT, C_LOG_HEAD || l_api_name || '.',
3212: tmpbuf);
3213: end if;
3214: begin
3215: fnd_user_pkg.user_synch(upper(x_new_user_name));
3216: exception
3217: when others then
3218: reason := fnd_message.get;
3219: fnd_message.set_name('FND', 'FND_FAILED_WF_USER_SYNCH');
3231: set grantee_key = x_new_user_name
3232: where grantee_key = x_old_user_name
3233: and grantee_type = 'USER';
3234:
3235: tmpbuf := 'Finished fnd_user_pkg.user_synch';
3236: if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
3237: fnd_log.string(FND_LOG.LEVEL_STATEMENT, C_LOG_HEAD || l_api_name || '.',
3238: tmpbuf);
3239: end if;
3241: end;
3242:
3243: exception
3244: when no_data_found then
3245: -- old username does not exist in fnd_user
3246: fnd_message.set_name('FND', 'FND_CHANGE_USER_FAILED');
3247: fnd_message.set_token('USER_NAME', x_old_user_name);
3248: app_exception.raise_exception;
3249:
3260: --
3261: -- Usage example in pl/sql
3262: -- declare
3263: -- retval number := null;
3264: -- begin retval := fnd_user_pkg.set_old_user_name('SOCTT'); end;
3265: --
3266: -- Input (Mandantory)
3267: -- x_old_user_name: Old User Name
3268: --
3282:
3283: ----------------------------------------------------------------------------
3284: -- MergePartyId (PUBLIC)
3285: -- This is the procedure being called during the Party Merge.
3286: -- FND_USER.MergePartyId() has been registered in Party Merge Data Dict.
3287: -- The input/output arguments format matches the document PartyMergeDD.doc.
3288: -- The goal is to fix the person_party_id in fnd_user table to point to the
3289: -- same party when two similar parties are begin merged.
3290: --
3284: -- MergePartyId (PUBLIC)
3285: -- This is the procedure being called during the Party Merge.
3286: -- FND_USER.MergePartyId() has been registered in Party Merge Data Dict.
3287: -- The input/output arguments format matches the document PartyMergeDD.doc.
3288: -- The goal is to fix the person_party_id in fnd_user table to point to the
3289: -- same party when two similar parties are begin merged.
3290: --
3291: -- Usage example in pl/sql
3292: -- This procedure should only be called from the PartyMerge utility.
3304: p_return_status := FND_API.G_RET_STS_SUCCESS;
3305:
3306: if (p_from_fk_id <> p_to_fk_id) then
3307:
3308: update fnd_user
3309: set person_party_id = p_to_fk_id
3310: where person_party_id = p_from_fk_id;
3311:
3312: -- Added for Function Security Cache Invalidation
3354: else
3355: -- we pass the generic validation, it is time to call any
3356: -- customized user name validation if there is any.
3357: begin
3358: wf_event.raise(p_event_name => 'oracle.apps.fnd.user.name.validate',
3359: p_event_key => x_user_name,
3360: p_event_data => NULL,
3361: p_parameters => NULL,
3362: p_send_date => Sysdate);
3377:
3378: --
3379: -- CreateUser (PUBLIC)
3380: --
3381: -- Bug#3904339 - SSO: Add user_guid parameter in fnd_user_pkg apis
3382: -- Overloaded procedure to create user
3383: -- Accepts User GUID as a parameter in addition to the other parameters
3384: --
3385: --
3405: x_change_source in number default null)
3406: is
3407: l_result number;
3408: begin
3409: l_result := fnd_user_pkg.CreateUserId(
3410: x_user_name,
3411: x_owner,
3412: x_unencrypted_password,
3413: x_session_number,
3431: ----------------------------------------------------------------------
3432: --
3433: -- CreateUserId (PUBLIC)
3434: --
3435: -- Bug#3904339 - SSO: Add user_guid parameter in fnd_user_pkg apis
3436: -- Overloaded procedure to create user
3437: -- Accepts User GUID as a parameter in addition to the other parameters
3438: --
3439: -- Returns
3489: ----------------------------------------------------------------------
3490: --
3491: -- UpdateUser (Public)
3492: --
3493: -- Bug#3904339 - SSO: Add user_guid parameter in fnd_user_pkg apis
3494: -- Overloaded procedure to update user
3495: -- Accepts User GUID as a parameter in addition to the other parameters
3496: --
3497: procedure UpdateUser (
3551:
3552: function userExists(x_user_name in varchar2) return boolean is
3553: dummy number;
3554: begin
3555: select 1 into dummy from fnd_user
3556: where user_name = upper(x_user_name);
3557: return TRUE;
3558: exception
3559: when no_data_found then
3567: -- This api test whether a username exists in FND and/or in OID.
3568: --
3569: -- Usage example in pl/sql
3570: -- declare ret number;
3571: -- begin ret := fnd_user_pkg.testusername('SOCTT'); end;
3572: --
3573: -- Input (Mandantory)
3574: -- x_user_name: User Name that you want to test
3575: --
3588:
3589: pf := 'N';
3590:
3591: begin
3592: fnd_user_pkg.validate_user_name(x_user_name);
3593: exception
3594: when others then
3595: -- error message is already on the stack from validate_user_name()
3596: -- Either a generic validation error message or specific message from
3593: exception
3594: when others then
3595: -- error message is already on the stack from validate_user_name()
3596: -- Either a generic validation error message or specific message from
3597: -- the subscriber of "fnd.user.name.validate"
3598: return(USER_INVALID_NAME);
3599: end;
3600:
3601: if (fnd_user_pkg.userExists(x_user_name)) then
3597: -- the subscriber of "fnd.user.name.validate"
3598: return(USER_INVALID_NAME);
3599: end;
3600:
3601: if (fnd_user_pkg.userExists(x_user_name)) then
3602: fnd_message.set_name('FND', 'FND_USER_EXISTS_IN_FND');
3603: return(USER_EXISTS_IN_FND);
3604: else
3605: begin
3598: return(USER_INVALID_NAME);
3599: end;
3600:
3601: if (fnd_user_pkg.userExists(x_user_name)) then
3602: fnd_message.set_name('FND', 'FND_USER_EXISTS_IN_FND');
3603: return(USER_EXISTS_IN_FND);
3604: else
3605: begin
3606: retval := fnd_ldap_wrapper.user_exists(x_user_name);
3613: -- The above check return that user exists in oid.
3614: fnd_profile.get('APPS_SSO_LINK_SAME_NAMES', pf);
3615:
3616: if(pf = 'Y') then
3617: fnd_message.set_name('FND', 'FND_USER_SYNCHED');
3618: fnd_message.set_token('USER_NAME', x_user_name);
3619: return(USER_SYNCHED);
3620: -- next time when this user gets created in fnd, it will be
3621: -- linked to each other.
3619: return(USER_SYNCHED);
3620: -- next time when this user gets created in fnd, it will be
3621: -- linked to each other.
3622: else
3623: fnd_message.set_name('FND', 'FND_USER_EXISTS_NO_LINK');
3624: return(USER_EXISTS_NO_LINK_ALLOWED);
3625: end if;
3626: else
3627: return(USER_OK_CREATE);
3641: --
3642: -- Usage example in pl/sql
3643: -- declare
3644: -- retval number := null;
3645: -- begin retval := fnd_user_pkg.set_old_person_party_id(12345); end;
3646: --
3647: -- Input (Mandantory)
3648: -- x_old_person_party_id: Old Person Party Id
3649: --
3674: -- retval number := null;
3675: -- guid raw(16);
3676: -- begin
3677: -- guid := 'F9374D4B80AB1A86E034080020B2612C';
3678: -- retval := fnd_user_pkg.set_old_user_guid(guid); end;
3679: --
3680: -- Input (Mandantory)
3681: -- x_old_user_guid: Old USER GUID
3682: --
3719: userid := null;
3720:
3721: -- bug 5162136 Obtain the user_id for later use
3722: select user_guid, user_id into l_user_guid, userid
3723: from fnd_user
3724: where user_name = x_user_name;
3725:
3726: if (l_user_guid is null) then
3727: return null;
3787:
3788: ----------------------------------------------------------------------------
3789: --
3790: -- ldap_wrapper_update_user (PUBLIC)
3791: -- This is called by the fnd_user_pkg and fnd_web_sec
3792: -- It serves as a helper routine to call fnd_ldap_wrapper.update_user
3793: -- when we need to synch the user update to OID.
3794: -- Note
3795: -- Please note that even this is public procedure, it does not mean for
3814: if (l_pwd_ret is not null) then
3815: -- If the return password from ldap is not null, that means ldap
3816: -- is informing us that this user is externally managed so we need
3817: -- to update the password to EXTERNAL.
3818: update fnd_user
3819: set encrypted_foundation_password = l_pwd_ret,
3820: encrypted_user_password = l_pwd_ret
3821: where user_name = upper(x_user_name);
3822: end if;
3825:
3826:
3827: --
3828: -- ldap_wrapper_create_user (PUBLIC)
3829: -- This is called by user form and the fnd_user_pkg.
3830: -- It serves as a helper routine to call fnd_ldap_wrapper.create_user
3831: -- when we need to synch that new FND user to OID.
3832: -- It also takes care of updating fnd_user with the user_guid and oid_pwd
3833: -- coming back from ldap_wrapper layer.
3827: --
3828: -- ldap_wrapper_create_user (PUBLIC)
3829: -- This is called by user form and the fnd_user_pkg.
3830: -- It serves as a helper routine to call fnd_ldap_wrapper.create_user
3831: -- when we need to synch that new FND user to OID.
3832: -- It also takes care of updating fnd_user with the user_guid and oid_pwd
3833: -- coming back from ldap_wrapper layer.
3834: -- Note
3835: -- Please note that even this is public procedure, it does not mean for
3828: -- ldap_wrapper_create_user (PUBLIC)
3829: -- This is called by user form and the fnd_user_pkg.
3830: -- It serves as a helper routine to call fnd_ldap_wrapper.create_user
3831: -- when we need to synch that new FND user to OID.
3832: -- It also takes care of updating fnd_user with the user_guid and oid_pwd
3833: -- coming back from ldap_wrapper layer.
3834: -- Note
3835: -- Please note that even this is public procedure, it does not mean for
3836: -- other public usage. This is mainly created as a helper routine to
3857: x_start_date, x_end_date,
3858: x_description,x_email_address,x_fax,x_expire_pwd,
3859: l_user_guid, l_oid_pwd);
3860: if (l_user_guid is not null) then
3861: update fnd_user
3862: set user_guid = l_user_guid
3863: where user_name = x_user_name;
3864: end if;
3865:
3901: l_user_guid := null;
3902:
3903: select user_guid
3904: into l_user_guid
3905: from fnd_user
3906: where user_name = x_old_user_name;
3907:
3908: if (l_user_guid is not null) then
3909: ldap_wrapper_change_user_name(l_user_guid, x_old_user_name, x_new_user_name);
3942: -- Bug 5161134
3943: -- If is from Form, we can not use our rule about null means no change.
3944: -- If form passs in null value, that means change it to null.
3945: if (x_end_date is null) then
3946: l_end_date := fnd_user_pkg.null_date;
3947: else
3948: l_end_date := x_end_date;
3949: end if;
3950:
3948: l_end_date := x_end_date;
3949: end if;
3950:
3951: if (x_description is null) then
3952: l_description := fnd_user_pkg.null_char;
3953: else
3954: l_description := x_description;
3955: end if;
3956:
3954: l_description := x_description;
3955: end if;
3956:
3957: if (x_email_address is null) then
3958: l_email_address := fnd_user_pkg.null_char;
3959: else
3960: l_email_address := x_email_address;
3961: end if;
3962:
3960: l_email_address := x_email_address;
3961: end if;
3962:
3963: if (x_fax is null) then
3964: l_fax := fnd_user_pkg.null_char;
3965: else
3966: l_fax := x_fax;
3967: end if;
3968:
3984: ----------------------------------------------------------------------------
3985: -- This routine is for AOL INTERNAL USE ONLY !!!!!!!
3986: --
3987: -- ldap_wrp_update_user_loader
3988: -- This is called by the fnd_user_pkg and fnd_web_sec.
3989: -- It serves as a helper routine to call fnd_ldap_wrapper.update_user
3990: -- when we need to synch the user update to OID.
3991: procedure ldap_wrp_update_user_loader(x_user_name in varchar2,
3992: x_hashed_password in varchar2,
4009: l_pwd_ret := null;
4010: userid := null;
4011:
4012: select user_guid, user_id into l_user_guid, userid
4013: from fnd_user
4014: where user_name = x_user_name;
4015:
4016: if (l_user_guid is null) then
4017: return;
4029: app_exception.raise_exception;
4030: end if;
4031:
4032: if (l_pwd_ret = fnd_web_sec.external_pwd) then
4033: update fnd_user
4034: set encrypted_foundation_password = 'EXTERNAL',
4035: encrypted_user_password = 'EXTERNAL'
4036: where user_name = upper(x_user_name);
4037: end if;
4044: app_exception.raise_exception;
4045:
4046: end;
4047:
4048: end FND_USER_PKG;