[Home] [Help]
PACKAGE BODY: APPS.FND_OAM_METALINK_CREDS
Source
1 PACKAGE BODY fnd_oam_metalink_creds AS
2 /* $Header: AFOAMMCB.pls 120.2 2005/10/19 11:26:34 ilawler noship $ */
3
4 PROCEDURE dbg_print(p_msg varchar2)
5 AS
6
7 BEGIN
8 null;
9 --dbms_output.put_line(p_msg);
10 END dbg_print;
11
12
13 --
14 -- Name
15 -- put_credentials
16 --
17 -- Purpose
18 -- Stores the given metalink credentials for the given user in
19 -- fnd_oam_metalink_cred. If a row already exists for given username
20 -- it will update, otherwise it will insert a new row
21 --
22 -- Input Arguments
23 -- p_username - Applications username
24 -- p_metalink_userid - Metalink User id
25 -- p_metalink_password - Metalink password
26 -- p_email_address - Email address
27 -- Output Arguments
28 -- p_errmsg - Error message if any error occurs
29 -- p_retcode - Return code. 0 if success otherwise error.
30 -- Notes:
31 --
32 --
33 PROCEDURE put_credentials(
34 p_username varchar2,
35 p_metalink_userid varchar2,
36 p_metalink_password varchar2,
37 p_email_address varchar2,
38 p_errmsg OUT NOCOPY varchar2,
39 p_retcode OUT NOCOPY number)
40 AS
41 v_userid number;
42
43 v_key raw(24);
44 v_encr raw(2000);
45 v_dec raw(2000);
46 v_enc fnd_oam_metalink_cred.metalink_password%TYPE;
47
48 BEGIN
49 p_retcode := 0;
50 p_errmsg := '';
51
52 select user_id into v_userid
53 from fnd_user where upper(user_name) = upper(p_username);
54
55 v_key := fnd_crypto.randombytes(24);
56
57 --
58 -- Note: The call to utl_raw.cast_to_raw as coded below
59 -- may cause problems if database characterset changes.
60 -- Ideally it should be converted to UTF8 first.
61 --
62 -- Proper call. Works in 10g.
63 -- plaintext => utl_raw.cast_to_raw(
64 -- convert(p_metalink_password, 'AL32UTF8'));
65 --
66
67 v_encr := fnd_crypto.encrypt(
68 plaintext => utl_raw.cast_to_raw(p_metalink_password),
69 key => v_key);
70 v_dec := v_key || v_encr;
71 v_enc := fnd_crypto.encode(
72 source => v_dec,
73 fmt_type => fnd_crypto.ENCODE_B64);
74
75
76 begin
77 select user_id into v_userid
78 from fnd_oam_metalink_cred
79 where user_id = v_userid;
80
81 update fnd_oam_metalink_cred set
82 metalink_user_id = p_metalink_userid,
83 metalink_password = v_enc,
84 email_address = p_email_address,
85 last_updated_by = v_userid,
86 last_update_date = sysdate,
87 last_update_login = 0
88 where user_id = v_userid;
89 exception
90 when no_data_found then
91 insert into fnd_oam_metalink_cred (
92 user_id,
93 metalink_user_id,
94 metalink_password,
95 email_address,
96 created_by,
97 creation_date,
98 last_updated_by,
99 last_update_date,
100 last_update_login)
101 values (
102 v_userid,
103 p_metalink_userid,
104 v_enc,
105 p_email_address,
106 v_userid,
107 sysdate,
108 v_userid,
109 sysdate,
110 0);
111 end;
112 EXCEPTION
113 when others then
114 p_retcode := 1;
115 p_errmsg := SQLERRM;
116 END put_credentials;
117
118 --
119 -- Name
120 -- get_credentials
121 --
122 -- Purpose
123 -- Retrieves the given metalink credentials for the given user
124 --
125 -- Input Arguments
126 -- p_username - Applications username
127 --
128 -- Output Arguments
129 -- p_metalink_userid - Metalink User id
130 -- p_metalink_password - Metalink password
131 -- p_email_address - Email address
132 -- p_errmsg - Error message if any error occurs
133 -- p_retcode - Return code. 0 if success otherwise error.
134 -- Notes:
135 --
136 PROCEDURE get_credentials(
137 p_username varchar2,
138 p_metalink_userid OUT NOCOPY varchar2,
139 p_metalink_password OUT NOCOPY varchar2,
140 p_email_address OUT NOCOPY varchar2,
141 p_errmsg OUT NOCOPY varchar2,
142 p_retcode OUT NOCOPY number)
143 AS
144 v_key raw(24);
145 v_encr raw(2000);
146 v_dec raw(2000);
147 v_enc fnd_oam_metalink_cred.metalink_password%TYPE;
148 BEGIN
149 p_retcode := 0;
150 p_errmsg := '';
151 p_metalink_userid := '';
152 p_metalink_password := '';
153 p_email_address := '';
154
155 select fom.metalink_user_id,
156 fom.metalink_password,
157 fom.email_address
158 into p_metalink_userid,
159 v_enc,
160 p_email_address
161 from fnd_oam_metalink_cred fom,
162 fnd_user fu
163 where fu.user_id = fom.user_id
164 and upper(fu.user_name) = upper(p_username);
165
166 v_dec := fnd_crypto.decode(
167 source => v_enc,
168 fmt_type => fnd_crypto.ENCODE_B64);
169 v_key := utl_raw.substr(v_dec, 1, 24);
170 v_encr := utl_raw.substr(v_dec, 25);
171
172
173 --
174 -- Note: The call to utl_raw.cast_to_varchar2 as coded below
175 -- may cause problems if database characterset changes.
176 -- Ideally it should be converted to UTF8 first.
177 --
178 -- Proper call. Works in 10g.
179 -- p_metalink_password := utl_raw.cast_to_varchar2
180 -- (utl_raw.convert(src, userenv('language'),
181 -- 'AMERICAN_AMERICA.AL32UTF8'));
182 --
183
184 p_metalink_password := utl_raw.cast_to_varchar2(fnd_crypto.decrypt(
185 cryptext => v_encr,
186 key => v_key));
187
188 EXCEPTION
189 when no_data_found then
190 null;
191 when others then
192 p_retcode := 1;
193 p_errmsg := SQLERRM;
194 END get_credentials;
195
196 --
197 -- For testing only
198 --
199 PROCEDURE test
200 AS
201 v_errmsg varchar2(1000);
202 v_retcode binary_integer;
203 v_mlink_userid varchar2(100);
204 v_mlink_pw varchar2(240);
205 v_email varchar2(240);
206
207 v_username varchar2(30) := 'anonymous';
208
209 BEGIN
210 dbg_print('Adding credentials .. ');
211 fnd_oam_metalink_creds.put_credentials(
212 p_username => v_username,
213 p_metalink_userid => 'test_user',
214 p_metalink_password => 'welcome1',
215 p_email_address => '[email protected]',
216 p_errmsg => v_errmsg,
217 p_retcode => v_retcode);
218 dbg_print('Ret code: ' || to_char(v_retcode));
219 dbg_print('Err msg: ' || v_errmsg);
220
221 dbg_print('Getting credentials .. ');
222 fnd_oam_metalink_creds.get_credentials(
223 p_username => v_username,
224 p_metalink_userid => v_mlink_userid,
225 p_metalink_password => v_mlink_pw,
226 p_email_address => v_email,
227 p_errmsg => v_errmsg,
228 p_retcode => v_retcode);
229 dbg_print('Ret code: ' || to_char(v_retcode));
230 dbg_print('Err msg: ' || v_errmsg);
231 dbg_print('Mlink user: ' || v_mlink_userid);
232 dbg_print('Mlink pw: ' || v_mlink_pw);
233 dbg_print('Email: ' || v_email);
234
235 dbg_print('Updating credentials .. ');
236 fnd_oam_metalink_creds.put_credentials(
237 p_username => v_username,
238 p_metalink_userid => 'test_user2',
239 p_metalink_password => 'abcdefghijklmnopqrstuvwxyz0123456789',
240 p_email_address => '[email protected]',
241 p_errmsg => v_errmsg,
242 p_retcode => v_retcode);
243 dbg_print('Ret code: ' || to_char(v_retcode));
244 dbg_print('Err msg: ' || v_errmsg);
245
246 dbg_print('Getting credentials .. ');
247 fnd_oam_metalink_creds.get_credentials(
248 p_username => v_username,
249 p_metalink_userid => v_mlink_userid,
250 p_metalink_password => v_mlink_pw,
251 p_email_address => v_email,
252 p_errmsg => v_errmsg,
253 p_retcode => v_retcode);
254 dbg_print('Ret code: ' || to_char(v_retcode));
255 dbg_print('Err msg: ' || v_errmsg);
256 dbg_print('Mlink user: ' || v_mlink_userid);
257 dbg_print('Mlink pw: ' || v_mlink_pw);
258 dbg_print('Email: ' || v_email);
259
260 dbg_print('Rolling back test data .. ');
261 rollback;
262 END test;
263
264 END fnd_oam_metalink_creds;