[Home] [Help]
PACKAGE BODY: APPS.FND_ORACLE_USER_PKG
Source
1 package body FND_ORACLE_USER_PKG as
2 /* $Header: AFSCOUSB.pls 120.1 2005/07/02 03:08:50 appldev ship $ */
3
4 ----------------------------------------------------------------------------
5 /* PRIVATE */
6 function boolRet(ret varchar2) return boolean is
7 begin
8 if (ret = 'Y') then
9 return TRUE;
10 end if;
11 return FALSE;
12 end;
13
14 /* PRIVATE */
15 -- Called by LOAD_ROW() with a reencrypted_password
16 procedure CreateOracleUser (
17 x_oracle_username in varchar2,
18 x_owner in varchar2,
19 x_reencrypted_oracle_password in varchar2,
20 x_description in varchar2 default null,
21 x_enabled_flag in varchar2 default null,
22 x_read_only_flag in varchar2 default null) is
23
24 owner_id number := 0;
25 ouser_id number;
26 ret varchar2(1) := 'N';
27 reason varchar2(32000);
28
29 begin
30 if (x_owner = 'SEED') then
31 owner_id := 1;
32 elsif (x_owner = 'CUST') then
33 owner_id := 0;
34 end if;
35
36 /* Java layer takes care of decrypting and encrypting password and */
37 /* store in table */
38 ret :=fnd_web_sec.create_oracle_user(x_oracle_username,
39 x_reencrypted_oracle_password,
40 'LOADER',
41 ouser_id);
42 if (ret = 'Y') then
43
44 -- update the rest of the data except password
45 update fnd_oracle_userid set
46 last_update_date = sysdate,
47 last_updated_by = owner_id,
48 last_update_login = 0,
49 description = nvl(x_description, description),
50 enabled_flag = x_enabled_flag,
51 read_only_flag = x_read_only_flag
52 where oracle_username = upper(x_oracle_username);
53
54 else
55 -- The java layer puts message onto the message stack.
56 -- WHAT TO DO WITH THE REAL MESSAGE????
57 reason := fnd_message.get();
58 fnd_message.set_name('FND', 'FND_CREATE_ORACLE_USER_FAILED');
59 fnd_message.set_token('ORACLE_USER_NAME', x_oracle_username);
60 fnd_message.set_token('REASON', reason);
61 app_exception.raise_exception;
62 end if;
63
64 end CreateOracleUser;
65
66 ----------------------------------------------------------------------------
67 --
68 -- LOAD_ROW (PUBLIC): used by the FNDLOAD not meant for public use
69 --
70 --
71 procedure LOAD_ROW (
72 x_oracle_username in VARCHAR2,
73 x_owner in VARCHAR2,
74 x_encrypted_oracle_password in VARCHAR2,
75 x_description in VARCHAR2,
76 x_enabled_flag in VARCHAR2,
77 x_read_only_flag in VARCHAR2) IS
78 begin
79
80 fnd_oracle_user_pkg.load_row(
81 x_oracle_username => x_oracle_username,
82 x_owner => x_owner,
83 x_encrypted_oracle_password => x_encrypted_oracle_password,
84 x_description => x_description,
85 x_enabled_flag => x_enabled_flag,
86 x_read_only_flag => x_read_only_flag,
87 x_custom_mode => null,
88 x_last_update_date => null);
89 end LOAD_ROW;
90
91 -- Overloaded !!
92
93 procedure LOAD_ROW (
94 x_oracle_username in VARCHAR2,
95 x_owner in VARCHAR2,
96 x_encrypted_oracle_password in VARCHAR2,
97 x_description in VARCHAR2,
98 x_enabled_flag in VARCHAR2,
99 x_read_only_flag in VARCHAR2,
100 x_custom_mode in VARCHAR2,
101 x_last_update_date in VARCHAR2) IS
102
103 owner_id number := 0;
104 ret boolean;
105 f_luby number; -- entity owner in file
106 f_ludate date; -- entity update date in file
107 db_luby number; -- entity owner in db
108 db_ludate date; -- entity update date in db
109 enc_pwd varchar2(100); -- encrypted password to go in database
110
111 begin
112 -- Translate owner to file_last_updated_by
113 f_luby := fnd_load_util.owner_id(x_owner);
114
115 -- Translate char last_update_date to date
116 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
117
118 begin
119 select LAST_UPDATED_BY, LAST_UPDATE_DATE
120 into db_luby, db_ludate
121 from fnd_oracle_userid
122 where oracle_username = x_oracle_username;
123
124 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
125 db_ludate, X_CUSTOM_MODE)) then
126
127 /* Convert the password from being encrypted with the loader key to */
128 /* being encrypted with right key for storing it in the database */
129 /* (apps pwd) */
130 enc_pwd := x_encrypted_oracle_password;
131 if ((enc_pwd <> 'EXTERNAL') AND (enc_pwd <> 'INVALID')) then
132 enc_pwd := fnd_web_sec.cvt_reencrypted_oracle_pwd(
133 x_encrypted_oracle_password, 'LOADER');
134 end if;
135 if(enc_pwd is NULL) then
136 enc_pwd := 'INVALID';
137 end if;
138
139 update fnd_oracle_userid set
140 last_update_date = f_ludate,
141 last_updated_by = f_luby,
142 last_update_login = 0,
143 description = nvl(x_description, description),
144 enabled_flag = x_enabled_flag,
145 read_only_flag = x_read_only_flag,
146 encrypted_oracle_password = enc_pwd
147 where oracle_username = x_oracle_username;
148 end if;
149 exception
150 when no_data_found then
151
152 fnd_oracle_user_pkg.createoracleuser(
153 x_oracle_username,
154 x_owner,
155 x_encrypted_oracle_password,
156 x_description,
157 x_enabled_flag,
158 x_read_only_flag);
159
160 end;
161 end LOAD_ROW;
162
163 --
164 -- GetReEncryptedPassword (PUBLIC)
165 -- Return user password encrypted with new key. This just returns the
166 -- newly encrypted password. It does not set the password in
167 -- FND_ORACLE_USERID table.
168 --
169 -- Usage example in pl/sql
170 -- declare
171 -- newpass varchar2(100);
172 -- begin
173 -- newpass := fnd_oracle_user_pkg.getreencryptedpassword('SCOTT','NEWKEY');
174 -- end;
175 --
176 -- Input (Mandatory)
177 -- username: User Name
178 -- newkey New Key
179 --
180 function GetReEncryptedPassword(username varchar2,
181 newkey varchar2) return varchar2 is
182 begin
183 return (fnd_web_sec.get_reencrypted_oracle_pwd(username, newkey));
184 end;
185
186 end FND_ORACLE_USER_PKG;