[Home] [Help]
PACKAGE BODY: APPS.FND_REGISTRATION_UTILS_PKG
Source
1 package body FND_REGISTRATION_UTILS_PKG as
2 /* $Header: AFREGUB.pls 115.8 2003/01/09 00:35:26 bfreeman noship $ */
3
4 function is_requested_username_unique(
5 p_username IN VARCHAR2)
6 return VARCHAR2
7 IS
8 l_duplicates NUMBER;
9 BEGIN
10 -- determine if a duplicate username exists
11 -- trying to do this as quickly as possible, but I find using
12 -- an explicit cursor to be difficult to read
13 SELECT count(*)
14 INTO l_duplicates
15 FROM fnd_user
16 WHERE user_name = UPPER(p_username)
17 AND rownum = 1;
18
19 IF (l_duplicates <> 0)
20 THEN RETURN 'N';
21 END IF;
22
23 -- determine if there is a non-rejected duplicate requested_user_name
24 SELECT count(*)
25 INTO l_duplicates
26 FROM fnd_registrations
27 WHERE requested_user_name = p_username
28 AND registration_status IN('REGISTERED')
29 AND rownum = 1;
30
31 IF (l_duplicates <> 0)
32 THEN RETURN 'N';
33 END IF;
34
35 RETURN 'Y';
36
37 END is_requested_username_unique;
38
39
40
41 function is_requested_username_unique(
42 p_registration_id IN NUMBER)
43 return VARCHAR2
44 IS
45 l_username VARCHAR2(100);
46 l_duplicates NUMBER;
47 BEGIN
48
49 -- lookup requested_user_name for given registration_id
50 SELECT requested_user_name
51 INTO l_username
52 FROM fnd_registrations
53 WHERE registration_id = p_registration_id;
54
55 -- determine if a duplicate username exists
56 -- trying to do this as quickly as possible, but I find using
57 -- an explicit cursor to be difficult to read
58 SELECT count(*)
59 INTO l_duplicates
60 FROM fnd_user
61 WHERE user_name = UPPER(l_username)
62 AND rownum = 1;
63
64 IF (l_duplicates <> 0)
65 THEN RETURN 'N';
66 END IF;
67
68 -- determine if there is a non-rejected duplicate requested_user_name
69 SELECT count(*)
70 INTO l_duplicates
71 FROM fnd_registrations
72 WHERE requested_user_name = l_username
73 AND registration_status <> 'REJECTED'
74 AND rownum = 1;
75
76 IF (l_duplicates <> 0)
77 THEN RETURN 'N';
78 END IF;
79
80 RETURN 'Y';
81
82 END is_requested_username_unique;
83
84 function is_requested_username_unique(
85 p_registration_id IN NUMBER,
86 p_username IN VARCHAR2)
87 return VARCHAR2
88 IS
89 l_duplicates NUMBER;
90 BEGIN
91 -- determine if a duplicate username exists
92 -- trying to do this as quickly as possible, but I find using
93 -- an explicit cursor to be difficult to read
94 SELECT count(*)
95 INTO l_duplicates
96 FROM fnd_user
97 WHERE user_name = UPPER(p_username)
98 AND rownum = 1;
99
100 IF (l_duplicates <> 0)
101 THEN RETURN 'N';
102 END IF;
103
104 -- determine if there is a non-rejected duplicate requested_user_name
105 SELECT count(*)
106 INTO l_duplicates
107 FROM fnd_registrations
108 WHERE requested_user_name = p_username
109 AND registration_status IN('REGISTERED')
110 AND registration_id <> p_registration_id
111 AND rownum = 1;
112
113 IF (l_duplicates <> 0)
114 THEN RETURN 'N';
115 END IF;
116
117 RETURN 'Y';
118
119 END is_requested_username_unique;
120
121
122 function publish_event(
123 p_registration_id IN NUMBER,
124 p_event_type IN VARCHAR2
125 )
126 return VARCHAR2
127 IS
128
129 l_registration_key VARCHAR2(255);
130 l_event_name VARCHAR2(255);
131 l_param_list WF_PARAMETER_LIST_T := wf_parameter_list_t();
132 lv_reg_type FND_REGISTRATIONS.REGISTRATION_TYPE%TYPE;
133 ln_application_id FND_REGISTRATIONS.APPLICATION_ID%TYPE;
134
135 l_error_message VARCHAR2(4000);
136
137 BEGIN
138
139 l_registration_key := get_reg_key_from_id(p_registration_id);
140
141 SELECT application_id, registration_type
142 INTO ln_application_id, lv_reg_type
143 FROM fnd_registrations
144 WHERE registration_id = p_registration_id;
145
146 -- populate parameter list for event
147 wf_event.AddParameterToList( p_name => 'REGISTRATION_KEY',
148 p_value => l_registration_key,
149 p_parameterlist => l_param_list);
150
151 wf_event.AddParameterToList( p_name => 'REGISTRATION_TYPE',
152 p_value => lv_reg_type,
153 p_parameterlist => l_param_list);
154
155 wf_event.AddParameterToList( p_name => 'APPLICATION_ID',
156 p_value => ln_application_id,
157 p_parameterlist => l_param_list);
158
159 -- event names:
160 -- oracle.apps.fnd.umf.reg.user_invited
161 -- oracle.apps.fnd.umf.reg.user_registered
162 -- oracle.apps.fnd.umf.reg.user_approved
163 -- oracle.apps.fnd.umf.reg.user_rejected
164
165 l_event_name := 'oracle.apps.fnd.umf.reg.user_' || p_event_type;
166 wf_event.raise( p_event_name => l_event_name,
167 p_event_key => l_registration_key || ':' || to_char(sysdate,'RRDDDSSSSS'),
168 p_parameters => l_param_list);
169
170 l_param_list.DELETE;
171
172 -- errors are returned here
173 l_error_message := fnd_message.get();
174 if (l_error_message is not null) then
175
176 if (l_error_message = FND_REGISTRATION_UTILS_PKG.EVENT_SUCCESS) then
177 return 'Y';
178 end if;
179 fnd_message.set_name(null, l_error_message);
180 return 'N';
181 else
182 RAISE NO_DATA_FOUND;
183 end if;
184
185 return 'N';
186
187 END publish_event;
188
189
190
191 function publish_invitation_event(
192 p_registration_id IN NUMBER)
193 return VARCHAR2
194 IS
195 BEGIN
196 return publish_event( p_registration_id, 'invited');
197
198 END publish_invitation_event;
199
200 function publish_registration_event(
201 p_registration_id IN NUMBER)
202 return VARCHAR2
203 IS
204 BEGIN
205 return publish_event( p_registration_id, 'registered');
206
207 END publish_registration_event;
208
209 function publish_approval_event(
210 p_registration_id IN NUMBER)
211 return VARCHAR2
212 IS
213 BEGIN
214 return publish_event( p_registration_id, 'approved');
215
216 END publish_approval_event;
217
218 function publish_rejection_event(
219 p_registration_id IN NUMBER)
220 return VARCHAR2
221 IS
222 BEGIN
223 return publish_event( p_registration_id, 'rejected');
224
225 END publish_rejection_event;
226
227 function get_reg_id_from_key(
228 p_registration_key IN VARCHAR2)
229 return NUMBER
230 IS
231 l_registration_id NUMBER;
232 BEGIN
233 SELECT registration_id
234 INTO l_registration_id
235 FROM fnd_registrations
236 WHERE registration_key = p_registration_key;
237
238 return l_registration_id;
239
240 END get_reg_id_from_key;
241
242 function get_reg_key_from_id(
243 p_registration_id IN NUMBER)
244 return VARCHAR2
245 IS
246 l_registration_key VARCHAR2(255);
247 BEGIN
248 SELECT registration_key
249 INTO l_registration_key
250 FROM fnd_registrations
251 WHERE registration_id = p_registration_id;
252 return l_registration_key;
253
254 END get_reg_key_from_id;
255
256 function invite(
257 p_application_id IN NUMBER,
258 p_party_id IN NUMBER,
259 p_registration_type IN VARCHAR2,
260 p_requested_user_name IN VARCHAR2,
261 p_assigned_user_name IN VARCHAR2,
262 p_exists_in_fnd_user_flag IN VARCHAR2,
263 p_user_title IN VARCHAR2,
264 p_first_name IN VARCHAR2,
265 p_middle_name IN VARCHAR2,
266 p_last_name IN VARCHAR2,
267 p_user_suffix IN VARCHAR2,
268 p_email_contact_point_id IN NUMBER,
269 p_email IN VARCHAR2,
270 p_phone_contact_point_id IN NUMBER,
271 p_phone_country_code IN VARCHAR2,
272 p_phone_area_code IN VARCHAR2,
273 p_phone IN VARCHAR2,
274 p_phone_extension IN VARCHAR2,
275 p_fax_contact_point_id IN NUMBER,
276 p_fax_country_code IN VARCHAR2,
277 p_fax_area_code IN VARCHAR2,
278 p_fax IN VARCHAR2,
279 p_fax_extension IN VARCHAR2,
280 p_language_code IN VARCHAR2,
281 p_time_zone IN VARCHAR2,
282 p_territory_code IN VARCHAR2,
283 p_location_id IN NUMBER,
284 p_address1 IN VARCHAR2,
285 p_address2 IN VARCHAR2,
286 p_city IN VARCHAR2,
287 p_state IN VARCHAR2,
288 p_province IN VARCHAR2,
289 p_zip IN VARCHAR2,
290 p_postal_code IN VARCHAR2,
291 p_country IN VARCHAR2,
292 p_reg_details IN fnd_registration_pkg.fnd_reg_details_ref_cursor,
293 x_event_result OUT NOCOPY VARCHAR2)
294 return NUMBER
295 IS
296 l_registration_id NUMBER;
297
298 BEGIN
299 l_registration_id := fnd_registration_pkg.insert_reg(
300 p_application_id,
301 p_party_id,
302 p_registration_type,
303 p_requested_user_name,
304 p_assigned_user_name,
305 'INVITED',
306 p_exists_in_fnd_user_flag,
307 p_reg_details,
308 p_user_title,
309 p_first_name,
310 p_middle_name,
311 p_last_name,
312 p_user_suffix,
313 p_email_contact_point_id,
314 p_email,
315 p_phone_contact_point_id,
316 p_phone_country_code,
317 p_phone_area_code,
318 p_phone,
319 p_phone_extension,
320 p_fax_contact_point_id,
321 p_fax_country_code,
322 p_fax_area_code,
323 p_fax,
324 p_fax_extension,
325 p_language_code,
326 p_time_zone,
327 p_territory_code,
328 p_location_id,
329 p_address1,
330 p_address2,
331 p_city,
332 p_state,
333 p_province,
334 p_zip,
335 p_postal_code,
336 p_country);
337 x_event_result := publish_invitation_event(l_registration_id);
338 return l_registration_id;
339
340 END invite;
341
342 function register(
343 p_registration_key IN VARCHAR2,
344 p_application_id IN NUMBER,
345 p_party_id IN NUMBER,
346 p_registration_type IN VARCHAR2,
347 p_requested_user_name IN VARCHAR2,
348 p_assigned_user_name IN VARCHAR2,
349 p_registration_status IN VARCHAR2,
350 p_exists_in_fnd_user_flag IN VARCHAR2,
351 p_user_title IN VARCHAR2,
352 p_first_name IN VARCHAR2,
353 p_middle_name IN VARCHAR2,
354 p_last_name IN VARCHAR2,
355 p_user_suffix IN VARCHAR2,
356 p_email_contact_point_id IN NUMBER,
357 p_email IN VARCHAR2,
358 p_phone_contact_point_id IN NUMBER,
359 p_phone_country_code IN VARCHAR2,
360 p_phone_area_code IN VARCHAR2,
361 p_phone IN VARCHAR2,
362 p_phone_extension IN VARCHAR2,
363 p_fax_contact_point_id IN NUMBER,
364 p_fax_country_code IN VARCHAR2,
365 p_fax_area_code IN VARCHAR2,
366 p_fax IN VARCHAR2,
367 p_fax_extension IN VARCHAR2,
368 p_language_code IN VARCHAR2,
369 p_time_zone IN VARCHAR2,
370 p_territory_code IN VARCHAR2,
371 p_location_id IN NUMBER,
372 p_address1 IN VARCHAR2,
373 p_address2 IN VARCHAR2,
374 p_city IN VARCHAR2,
375 p_state IN VARCHAR2,
376 p_province IN VARCHAR2,
377 p_zip IN VARCHAR2,
378 p_postal_code IN VARCHAR2,
379 p_country IN VARCHAR2,
380 p_reg_details IN fnd_registration_pkg.fnd_reg_details_ref_cursor,
381 x_event_result OUT NOCOPY VARCHAR2)
382 return NUMBER
383 IS
384 l_registration_id NUMBER;
385 my_val2 NUMBER;
386 my_val VARCHAR2(255);
387 BEGIN
388 IF (p_registration_key = null)
389 THEN
390 l_registration_id := fnd_registration_pkg.insert_reg(
391 p_application_id,
392 p_party_id,
393 p_registration_type,
394 p_requested_user_name,
395 p_assigned_user_name,
396 'REGISTERED',
397 p_exists_in_fnd_user_flag,
398 p_reg_details,
399 p_user_title,
400 p_first_name,
401 p_middle_name,
402 p_last_name,
403 p_user_suffix,
404 p_email_contact_point_id,
405 p_email,
406 p_phone_contact_point_id,
407 p_phone_country_code,
408 p_phone_area_code,
409 p_phone,
410 p_phone_extension,
411 p_fax_contact_point_id,
412 p_fax_country_code,
413 p_fax_area_code,
414 p_fax,
415 p_fax_extension,
416 p_language_code,
417 p_time_zone,
418 p_territory_code,
419 p_location_id,
420 p_address1,
421 p_address2,
422 p_city,
423 p_state,
424 p_province,
425 p_zip,
426 p_postal_code,
427 p_country);
428 my_val := publish_registration_event(l_registration_id);
429 ELSE
430 l_registration_id := get_reg_id_from_key(p_registration_key);
431 my_val2 := fnd_registration_pkg.insert_reg(
432 p_application_id,
433 p_party_id,
434 p_registration_type,
435 p_requested_user_name,
436 p_assigned_user_name, 'REGISTERED',
437 p_exists_in_fnd_user_flag,
438 p_reg_details,
439 p_user_title,
440 p_first_name,
441 p_middle_name,
442 p_last_name,
443 p_user_suffix,
444 p_email_contact_point_id,
445 p_email,
446 p_phone_contact_point_id,
447 p_phone_country_code,
448 p_phone_area_code,
449 p_phone,
450 p_phone_extension,
451 p_fax_contact_point_id,
452 p_fax_country_code,
453 p_fax_area_code,
454 p_fax,
455 p_fax_extension,
456 p_language_code, p_time_zone,
457 p_territory_code,
458 p_location_id,
459 p_address1,
460 p_address2,
461 p_city,
462 p_state,
463 p_province,
464 p_zip,
465 p_postal_code,
466 p_country);
467 x_event_result := publish_registration_event(l_registration_id);
468 END IF;
469 return l_registration_id;
470
471 END register;
472
473 procedure approve(
474 p_registration_id IN NUMBER,
475 x_event_result OUT NOCOPY VARCHAR2)
476 IS
477 BEGIN
478 fnd_registration_pkg.update_reg_status(p_registration_id, 'APPROVED');
479 x_event_result := publish_approval_event(p_registration_id);
480
481 END approve;
482
483 procedure reject(
484 p_registration_id IN NUMBER,
485 x_event_result OUT NOCOPY VARCHAR2)
486 IS
487 BEGIN
488 fnd_registration_pkg.update_reg_status(p_registration_id, 'REJECTED');
489 x_event_result := publish_rejection_event(p_registration_id);
490
491 END reject;
492
493 END FND_REGISTRATION_UTILS_PKG;
494