[Home] [Help]
PACKAGE BODY: APPS.FND_REGISTRATION_PKG
Source
1 package body FND_REGISTRATION_PKG as
2 /* $Header: AFREGB.pls 120.2 2005/11/04 12:40:39 rsheh noship $ */
3
4 function insert_fnd_reg(
5 p_application_id IN NUMBER,
6 p_party_id IN NUMBER,
7 p_registration_type IN VARCHAR2,
8 p_requested_user_name IN VARCHAR2,
9 p_assigned_user_name IN VARCHAR2,
10 p_registration_status IN VARCHAR2,
11 p_exists_in_fnd_user_flag IN VARCHAR2,
12 p_user_title IN VARCHAR2 default null,
13 p_first_name IN VARCHAR2 default null,
14 p_middle_name IN VARCHAR2 default null,
15 p_last_name IN VARCHAR2 default null,
16 p_user_suffix IN VARCHAR2 default null,
17 p_email_contact_point_id IN NUMBER default null,
18 p_email IN VARCHAR2 default null,
19 p_phone_contact_point_id IN NUMBER default null,
20 p_phone_country_code IN VARCHAR2 default null,
21 p_phone_area_code IN VARCHAR2 default null,
22 p_phone IN VARCHAR2 default null,
23 p_phone_extension IN VARCHAR2 default null,
24 p_fax_contact_point_id IN NUMBER default null,
25 p_fax_country_code IN VARCHAR2 default null,
26 p_fax_area_code IN VARCHAR2 default null,
27 p_fax IN VARCHAR2 default null,
28 p_fax_extension IN VARCHAR2 default null,
29 p_language_code IN VARCHAR2 default null,
30 p_time_zone IN VARCHAR2 default null,
31 p_territory_code IN VARCHAR2 default null,
32 p_location_id IN NUMBER default null,
33 p_address1 IN VARCHAR2 default null,
34 p_address2 IN VARCHAR2 default null,
35 p_city IN VARCHAR2 default null,
36 p_state IN VARCHAR2 default null,
37 p_province IN VARCHAR2 default null,
38 p_zip IN VARCHAR2 default null,
39 p_postal_code IN VARCHAR2 default null,
40 p_country IN VARCHAR2 default null)
41 return NUMBER
42 IS
43 l_registration_id NUMBER;
44
45 BEGIN
46
47 select fnd_registrations_s.nextval
48 into l_registration_id
49 from dual;
50
51 -- Note that this does not set the registration_key field.
52 -- For now this can only be set in the middle tier.
53
54 INSERT INTO fnd_registrations(
55 registration_id,
56 registration_key,
57 application_id,
58 party_id,
59 registration_type,
60 requested_user_name,
61 assigned_user_name,
62 registration_status,
63 exists_in_fnd_user_flag,
64 user_title,
65 first_name,
66 middle_name,
67 last_name,
68 user_suffix,
69 email_contact_point_id,
70 email,
71 phone_contact_point_id,
72 phone_country_code,
73 phone_area_code,
74 phone,
75 phone_extension,
76 fax_contact_point_id,
77 fax_country_code,
78 fax_area_code,
79 fax,
80 fax_extension,
81 language_code,
82 time_zone,
83 territory_code,
84 location_id,
85 address1,
86 address2,
87 city,
88 state,
89 province,
90 zip,
91 postal_code,
92 country,
93 date_requested,
94 last_update_date,
95 last_updated_by,
96 created_by,
97 creation_date,
98 last_update_login
99 )VALUES(
100 l_registration_id,
101 l_registration_id,
102 p_application_id,
103 p_party_id,
104 p_registration_type,
105 p_requested_user_name,
106 p_assigned_user_name,
107 p_registration_status,
108 p_exists_in_fnd_user_flag,
109 p_user_title,
110 p_first_name,
111 p_middle_name,
112 p_last_name,
113 p_user_suffix,
114 p_email_contact_point_id,
115 p_email,
116 p_phone_contact_point_id,
117 p_phone_country_code,
118 p_phone_area_code,
119 p_phone,
120 p_phone_extension,
121 p_fax_contact_point_id,
122 p_fax_country_code,
123 p_fax_area_code,
124 p_fax,
125 p_fax_extension,
126 p_language_code,
127 p_time_zone,
128 p_territory_code,
129 p_location_id,
130 p_address1,
131 p_address2,
132 p_city,
133 p_state,
134 p_province,
135 p_zip,
136 p_postal_code,
137 p_country,
138 sysdate,
139 sysdate,
140 fnd_global.user_id,
141 fnd_global.user_id,
142 sysdate,
143 fnd_global.login_id
144 );
145 return l_registration_id;
146
147 END insert_fnd_reg;
148
149
150
151 procedure update_fnd_reg(
152 p_registration_id IN NUMBER,
153 p_application_id IN NUMBER,
154 p_party_id IN NUMBER,
155 p_registration_type IN VARCHAR2,
156 p_requested_user_name IN VARCHAR2,
157 p_assigned_user_name IN VARCHAR2,
158 p_registration_status IN VARCHAR2,
159 p_exists_in_fnd_user_flag IN VARCHAR2,
160 p_user_title IN VARCHAR2 default null,
161 p_first_name IN VARCHAR2 default null,
162 p_middle_name IN VARCHAR2 default null,
163 p_last_name IN VARCHAR2 default null,
164 p_user_suffix IN VARCHAR2 default null,
165 p_email_contact_point_id IN NUMBER default null,
166 p_email IN VARCHAR2 default null,
167 p_phone_contact_point_id IN NUMBER default null,
168 p_phone_country_code IN VARCHAR2 default null,
169 p_phone_area_code IN VARCHAR2 default null,
170 p_phone IN VARCHAR2 default null,
171 p_phone_extension IN VARCHAR2 default null,
172 p_fax_contact_point_id IN NUMBER default null,
173 p_fax_country_code IN VARCHAR2 default null,
174 p_fax_area_code IN VARCHAR2 default null,
175 p_fax IN VARCHAR2 default null,
176 p_fax_extension IN VARCHAR2 default null,
177 p_language_code IN VARCHAR2 default null,
178 p_time_zone IN VARCHAR2 default null,
179 p_territory_code IN VARCHAR2 default null,
180 p_location_id IN NUMBER default null,
181 p_address1 IN VARCHAR2 default null,
182 p_address2 IN VARCHAR2 default null,
183 p_city IN VARCHAR2 default null,
184 p_state IN VARCHAR2 default null,
185 p_province IN VARCHAR2 default null,
186 p_zip IN VARCHAR2 default null,
187 p_postal_code IN VARCHAR2 default null,
188 p_country IN VARCHAR2 default null)
189 IS
190 BEGIN
191 UPDATE fnd_registrations
192 SET application_id = p_application_id,
193 party_id = p_party_id,
194 registration_type = p_registration_type,
195 requested_user_name = p_requested_user_name,
196 assigned_user_name = p_assigned_user_name,
197 registration_status = p_registration_status,
198 exists_in_fnd_user_flag = p_exists_in_fnd_user_flag,
199 user_title = p_user_title,
200 first_name = p_first_name,
201 middle_name = p_middle_name,
202 last_name = p_last_name,
203 user_suffix = p_user_suffix,
204 email_contact_point_id = p_email_contact_point_id,
205 email = p_email,
206 phone_contact_point_id = p_phone_contact_point_id,
207 phone_country_code = p_phone_country_code,
208 phone_area_code = p_phone_area_code,
209 phone = p_phone,
210 phone_extension = p_phone_extension,
211 fax_contact_point_id = p_fax_contact_point_id,
212 fax_country_code = p_fax_country_code,
213 fax_area_code = p_fax_area_code,
214 fax = p_fax,
215 fax_extension = p_fax_extension,
216 language_code = p_language_code,
217 time_zone = p_time_zone,
218 territory_code = p_territory_code,
219 location_id = p_location_id,
220 address1 = p_address1,
221 address2 = p_address2,
222 city = p_city,
223 state = p_state,
224 province = p_province,
225 zip = p_zip,
226 postal_code = p_postal_code,
227 country = p_country,
228 last_update_date = sysdate,
229 last_updated_by = fnd_global.user_id
230 WHERE
231 registration_id = p_registration_id;
232
233 END update_fnd_reg;
234
235
236 procedure delete_fnd_reg(
237 p_registration_id IN VARCHAR2)
238 IS
239 BEGIN
240 DELETE from fnd_registrations
241 WHERE registration_id = p_registration_id;
242
243 END delete_fnd_reg;
244
245 procedure insert_fnd_reg_details(
246 p_registration_id IN NUMBER,
247 p_application_id IN NUMBER,
248 p_registration_type IN VARCHAR2,
249 p_field_name IN VARCHAR2,
250 p_field_type IN VARCHAR2,
251 p_field_format IN VARCHAR2 default null,
252 p_field_value_string IN VARCHAR2 default null,
253 p_field_value_number IN NUMBER default null,
254 p_field_value_date IN DATE default null)
255 IS
256 BEGIN
257 INSERT INTO fnd_registration_details(
258 registration_id,
259 application_id,
260 registration_type,
261 field_name,
262 field_type,
263 field_format,
264 field_value_string,
265 field_value_number,
266 field_value_date,
267 last_update_date,
268 last_updated_by,
269 created_by,
270 creation_date,
271 last_update_login
272 )VALUES(
273 p_registration_id,
274 p_application_id,
275 p_registration_type,
276 p_field_name,
277 p_field_type,
278 p_field_format,
279 p_field_value_string,
280 p_field_value_number,
281 p_field_value_date,
282 sysdate,
283 fnd_global.user_id,
284 fnd_global.user_id,
285 sysdate,
286 fnd_global.login_id
287 );
288 END insert_fnd_reg_details;
289
290 procedure update_fnd_reg_details(
291 p_registration_id IN NUMBER,
292 p_field_name IN VARCHAR2,
293 p_field_type IN VARCHAR2,
294 p_field_format IN VARCHAR2 default null,
295 p_field_value_string IN VARCHAR2 default null,
296 p_field_value_number IN NUMBER default null,
297 p_field_value_date IN DATE default null)
298 IS
299 BEGIN
300 UPDATE fnd_registration_details
301 SET field_type = p_field_type,
302 field_format = p_field_format,
303 field_value_string = p_field_value_string,
304 field_value_number = p_field_value_number,
305 field_value_date = p_field_value_date,
306 last_update_date = sysdate,
307 last_updated_by = fnd_global.user_id
308 WHERE registration_id = p_registration_id
309 AND field_name = p_field_name;
310
311 END update_fnd_reg_details;
312
313 procedure delete_fnd_reg_details(
314 p_registration_id IN NUMBER,
315 p_field_name IN VARCHAR2)
316 IS
317 BEGIN
318 DELETE FROM fnd_registration_details
319 WHERE registration_id = p_registration_id
320 AND field_name = p_field_name;
321
322 END delete_fnd_reg_details;
323
324
325 procedure update_reg_status(
326 p_registration_id IN NUMBER,
327 p_new_status IN VARCHAR2)
328 IS
329 BEGIN
330 UPDATE fnd_registrations
331 SET registration_status = p_new_status,
332 last_update_date = sysdate,
333 last_updated_by = fnd_global.user_id
334 WHERE registration_id = p_registration_id;
335
336 END update_reg_status;
337
338 procedure update_reg_status_by_key(
339 p_registration_key IN VARCHAR2,
340 p_new_status IN VARCHAR2)
341 IS
342 l_registration_id NUMBER;
343 BEGIN
344 l_registration_id := get_registration_id_from_key(p_registration_key);
345 update_reg_status(l_registration_id, p_new_status);
346
347 END update_reg_status_by_key;
348
349 procedure update_fnd_reg_by_key(
350 p_registration_key IN VARCHAR2,
351 p_application_id IN NUMBER,
352 p_party_id IN NUMBER,
353 p_registration_type IN VARCHAR2,
354 p_requested_user_name IN VARCHAR2,
355 p_assigned_user_name IN VARCHAR2,
356 p_registration_status IN VARCHAR2,
357 p_exists_in_fnd_user_flag IN VARCHAR2,
358 p_user_title IN VARCHAR2 default null,
359 p_first_name IN VARCHAR2 default null,
360 p_middle_name IN VARCHAR2 default null,
361 p_last_name IN VARCHAR2 default null,
362 p_user_suffix IN VARCHAR2 default null,
363 p_email_contact_point_id IN NUMBER default null,
364 p_email IN VARCHAR2 default null,
365 p_phone_contact_point_id IN NUMBER default null,
366 p_phone_country_code IN VARCHAR2 default null,
367 p_phone_area_code IN VARCHAR2 default null,
368 p_phone IN VARCHAR2 default null,
369 p_phone_extension IN VARCHAR2 default null,
370 p_fax_contact_point_id IN NUMBER default null,
371 p_fax_country_code IN VARCHAR2 default null,
372 p_fax_area_code IN VARCHAR2 default null,
373 p_fax IN VARCHAR2 default null,
374 p_fax_extension IN VARCHAR2 default null,
375 p_language_code IN VARCHAR2 default null,
376 p_time_zone IN VARCHAR2 default null,
377 p_territory_code IN VARCHAR2 default null,
378 p_location_id IN NUMBER default null,
379 p_address1 IN VARCHAR2 default null,
380 p_address2 IN VARCHAR2 default null,
381 p_city IN VARCHAR2 default null,
382 p_state IN VARCHAR2 default null,
383 p_province IN VARCHAR2 default null,
384 p_zip IN VARCHAR2 default null,
385 p_postal_code IN VARCHAR2 default null,
386 p_country IN VARCHAR2 default null)
387 IS
388 l_registration_id NUMBER;
389 BEGIN
390 l_registration_id := get_registration_id_from_key(p_registration_key);
391 update_fnd_reg(
392 l_registration_id,
393 p_application_id,
394 p_party_id,
395 p_registration_type,
396 p_requested_user_name,
397 p_assigned_user_name,
398 p_registration_status,
402 p_middle_name,
399 p_exists_in_fnd_user_flag,
400 p_user_title,
401 p_first_name,
403 p_last_name,
404 p_user_suffix,
405 p_email_contact_point_id,
406 p_email,
407 p_phone_contact_point_id,
408 p_phone_country_code,
409 p_phone_area_code,
410 p_phone,
411 p_phone_extension,
412 p_fax_contact_point_id,
413 p_fax_country_code,
414 p_fax_area_code,
415 p_fax,
416 p_fax_extension,
417 p_language_code,
418 p_time_zone,
419 p_territory_code,
420 p_location_id,
421 p_address1,
422 p_address2,
423 p_city,
424 p_state,
425 p_province,
426 p_zip,
427 p_postal_code,
428 p_country);
429 END update_fnd_reg_by_key;
430
431
432
433 procedure delete_fnd_reg_by_key(
434 p_registration_key IN VARCHAR2)
435 IS
436 l_registration_id NUMBER;
437 BEGIN
438 l_registration_id := get_registration_id_from_key(p_registration_key);
439 delete_fnd_reg(l_registration_id);
440
441 END delete_fnd_reg_by_key;
442
443
444 function insert_reg(
445 p_application_id IN NUMBER,
446 p_party_id IN NUMBER,
447 p_registration_type IN VARCHAR2,
448 p_requested_user_name IN VARCHAR2,
449 p_assigned_user_name IN VARCHAR2,
450 p_registration_status IN VARCHAR2,
451 p_exists_in_fnd_user_flag IN VARCHAR2,
452 p_reg_details IN fnd_reg_details_ref_cursor,
453 p_user_title IN VARCHAR2 default null,
454 p_first_name IN VARCHAR2 default null,
455 p_middle_name IN VARCHAR2 default null,
456 p_last_name IN VARCHAR2 default null,
457 p_user_suffix IN VARCHAR2 default null,
458 p_email_contact_point_id IN NUMBER default null,
459 p_email IN VARCHAR2 default null,
460 p_phone_contact_point_id IN NUMBER default null,
461 p_phone_country_code IN VARCHAR2 default null,
462 p_phone_area_code IN VARCHAR2 default null,
463 p_phone IN VARCHAR2 default null,
464 p_phone_extension IN VARCHAR2 default null,
465 p_fax_contact_point_id IN NUMBER default null,
466 p_fax_country_code IN VARCHAR2 default null,
467 p_fax_area_code IN VARCHAR2 default null,
468 p_fax IN VARCHAR2 default null,
469 p_fax_extension IN VARCHAR2 default null,
470 p_language_code IN VARCHAR2 default null,
471 p_time_zone IN VARCHAR2 default null,
472 p_territory_code IN VARCHAR2 default null,
473 p_location_id IN NUMBER default null,
474 p_address1 IN VARCHAR2 default null,
475 p_address2 IN VARCHAR2 default null,
476 p_city IN VARCHAR2 default null,
477 p_state IN VARCHAR2 default null,
478 p_province IN VARCHAR2 default null,
479 p_zip IN VARCHAR2 default null,
480 p_postal_code IN VARCHAR2 default null,
481 p_country IN VARCHAR2 default null)
482 return NUMBER
483 IS
484 l_registration_id NUMBER;
485
486 l_ri_cur NUMBER;
487 l_ai_cur NUMBER;
488 l_rt_cur VARCHAR2(255);
489 l_lud_cur DATE;
490 l_lub_cur NUMBER;
491 l_cb_cur NUMBER;
492 l_cd_cur DATE;
493 l_lul_cur NUMBER;
494
495 l_field_name VARCHAR2(255);
496 l_field_type VARCHAR2(255);
497 l_field_format VARCHAR2(255);
498 l_field_value_string VARCHAR2(4000);
499 l_field_value_number NUMBER;
500 l_field_value_date DATE;
501 BEGIN
502
503 -- For fnd_registrations
504 -- Remember registration_key is not set this way
505
506 l_registration_id := insert_fnd_reg(
507 p_application_id,
508 p_party_id,
509 p_registration_type,
510 p_requested_user_name,
511 p_assigned_user_name,
512 p_registration_status,
513 p_exists_in_fnd_user_flag,
514 p_user_title,
515 p_first_name,
516 p_middle_name,
517 p_last_name,
518 p_user_suffix,
519 p_email_contact_point_id,
520 p_email,
521 p_phone_contact_point_id,
522 p_phone_country_code,
523 p_phone_area_code,
524 p_phone,
525 p_phone_extension,
526 p_fax_contact_point_id,
527 p_fax_country_code,
528 p_fax_area_code,
529 p_fax,
530 p_fax_extension,
531 p_language_code,
532 p_time_zone,
533 p_territory_code,
534 p_location_id,
535 p_address1,
536 p_address2,
537 p_city,
538 p_state,
539 p_province,
540 p_zip,
541 p_postal_code,
542 p_country);
543
544 -- for fnd_registration_details
545 -- loops through the ref_cursor
549 --OPEN p_reg_details;
546 -- Note this is primarily a REF Cursor because
547 -- Java cannot support records and tables
548
550 LOOP
551 FETCH p_reg_details
552 INTO l_ri_cur,
553 l_ai_cur,
554 l_rt_cur,
555 l_field_name,
556 l_field_type,
557 l_field_format,
558 l_field_value_string,
559 l_field_value_number,
560 l_field_value_date,
561 l_lud_cur,
562 l_lub_cur,
563 l_cb_cur,
564 l_cd_cur,
565 l_lul_cur;
566 insert_fnd_reg_details(
567 l_registration_id,
568 p_application_id,
569 p_registration_type,
570 l_field_name,
571 l_field_type,
572 l_field_format,
573 l_field_value_string,
574 l_field_value_number,
575 l_field_value_date
576 );
577 EXIT WHEN p_reg_details%NOTFOUND;
578 END LOOP;
579 --CLOSE p_reg_details;
580
581 END insert_reg;
582
583 procedure update_reg(
584 p_registration_id IN VARCHAR2,
585 p_application_id IN NUMBER,
586 p_party_id IN NUMBER,
587 p_registration_type IN VARCHAR2,
588 p_requested_user_name IN VARCHAR2,
589 p_assigned_user_name IN VARCHAR2,
590 p_registration_status IN VARCHAR2,
591 p_exists_in_fnd_user_flag IN VARCHAR2,
592 p_reg_details IN fnd_reg_details_ref_cursor,
593 p_user_title IN VARCHAR2 default null,
594 p_first_name IN VARCHAR2 default null,
595 p_middle_name IN VARCHAR2 default null,
596 p_last_name IN VARCHAR2 default null,
597 p_user_suffix IN VARCHAR2 default null,
598 p_email_contact_point_id IN NUMBER default null,
599 p_email IN VARCHAR2 default null,
600 p_phone_contact_point_id IN NUMBER default null,
601 p_phone_country_code IN VARCHAR2 default null,
602 p_phone_area_code IN VARCHAR2 default null,
603 p_phone IN VARCHAR2 default null,
604 p_phone_extension IN VARCHAR2 default null,
605 p_fax_contact_point_id IN NUMBER default null,
606 p_fax_country_code IN VARCHAR2 default null,
607 p_fax_area_code IN VARCHAR2 default null,
608 p_fax IN VARCHAR2 default null,
609 p_fax_extension IN VARCHAR2 default null,
610 p_language_code IN VARCHAR2 default null,
611 p_time_zone IN VARCHAR2 default null,
612 p_territory_code IN VARCHAR2 default null,
613 p_location_id IN NUMBER default null,
614 p_address1 IN VARCHAR2 default null,
615 p_address2 IN VARCHAR2 default null,
616 p_city IN VARCHAR2 default null,
617 p_state IN VARCHAR2 default null,
618 p_province IN VARCHAR2 default null,
619 p_zip IN VARCHAR2 default null,
620 p_postal_code IN VARCHAR2 default null,
621 p_country IN VARCHAR2 default null)
622 IS
623 l_registration_id NUMBER;
624
625 l_ri_cur NUMBER;
626 l_ai_cur NUMBER;
627 l_rt_cur VARCHAR2(255);
628 l_lud_cur DATE;
629 l_lub_cur NUMBER;
630 l_cb_cur NUMBER;
631 l_cd_cur DATE;
632 l_lul_cur NUMBER;
633
634 l_field_name VARCHAR2(255);
635 l_field_type VARCHAR2(255);
636 l_field_format VARCHAR2(255);
637 l_field_value_string VARCHAR2(4000);
638 l_field_value_number NUMBER;
639 l_field_value_date DATE;
640 BEGIN
641
642 -- Do update_fnd_registrations first
643
644 update_fnd_reg(
645 p_registration_id,
646 p_application_id,
647 p_party_id,
648 p_registration_type,
649 p_requested_user_name,
650 p_assigned_user_name,
651 p_registration_status,
652 p_exists_in_fnd_user_flag,
653 p_user_title,
654 p_first_name,
655 p_middle_name,
656 p_last_name,
657 p_user_suffix,
658 p_email_contact_point_id,
659 p_email,
660 p_phone_contact_point_id,
661 p_phone_country_code,
662 p_phone_area_code,
663 p_phone,
664 p_phone_extension,
665 p_fax_contact_point_id,
666 p_fax_country_code,
667 p_fax_area_code,
668 p_fax,
669 p_fax_extension,
670 p_language_code,
671 p_time_zone,
672 p_territory_code,
673 p_location_id,
674 p_address1,
675 p_address2,
676 p_city,
677 p_state,
678 p_province,
679 p_zip,
680 p_postal_code,
681 p_country);
682
683 -- Now do update fnd_registration_details
684
685 LOOP
686 FETCH p_reg_details
687 INTO l_ri_cur,
688 l_ai_cur,
689 l_rt_cur,
690 l_field_name,
694 l_field_value_number,
691 l_field_type,
692 l_field_format,
693 l_field_value_string,
695 l_field_value_date,
696 l_lud_cur,
697 l_lub_cur,
698 l_cb_cur,
699 l_cd_cur,
700 l_lul_cur;
701 insert_fnd_reg_details(
702 l_registration_id,
703 p_application_id,
704 p_registration_type,
705 l_field_name,
706 l_field_type,
707 l_field_format,
708 l_field_value_string,
709 l_field_value_number,
710 l_field_value_date
711 );
712 EXIT WHEN p_reg_details%NOTFOUND;
713 END LOOP;
714
715 END update_reg;
716
717 procedure update_reg_by_key(
718 p_registration_key IN VARCHAR2,
719 p_application_id IN NUMBER,
720 p_party_id IN NUMBER,
721 p_registration_type IN VARCHAR2,
722 p_requested_user_name IN VARCHAR2,
723 p_assigned_user_name IN VARCHAR2,
724 p_registration_status IN VARCHAR2,
725 p_exists_in_fnd_user_flag IN VARCHAR2,
726 p_reg_details IN fnd_reg_details_ref_cursor,
727 p_user_title IN VARCHAR2 default null,
728 p_first_name IN VARCHAR2 default null,
729 p_middle_name IN VARCHAR2 default null,
730 p_last_name IN VARCHAR2 default null,
731 p_user_suffix IN VARCHAR2 default null,
732 p_email_contact_point_id IN NUMBER default null,
733 p_email IN VARCHAR2 default null,
734 p_phone_contact_point_id IN NUMBER default null,
735 p_phone_country_code IN VARCHAR2 default null,
736 p_phone_area_code IN VARCHAR2 default null,
737 p_phone IN VARCHAR2 default null,
738 p_phone_extension IN VARCHAR2 default null,
739 p_fax_contact_point_id IN NUMBER default null,
740 p_fax_country_code IN VARCHAR2 default null,
741 p_fax_area_code IN VARCHAR2 default null,
742 p_fax IN VARCHAR2 default null,
743 p_fax_extension IN VARCHAR2 default null,
744 p_language_code IN VARCHAR2 default null,
745 p_time_zone IN VARCHAR2 default null,
746 p_territory_code IN VARCHAR2 default null,
747 p_location_id IN NUMBER default null,
748 p_address1 IN VARCHAR2 default null,
749 p_address2 IN VARCHAR2 default null,
750 p_city IN VARCHAR2 default null,
751 p_state IN VARCHAR2 default null,
752 p_province IN VARCHAR2 default null,
753 p_zip IN VARCHAR2 default null,
754 p_postal_code IN VARCHAR2 default null,
755 p_country IN VARCHAR2 default null)
756 IS
757 l_registration_id NUMBER;
758 BEGIN
759 l_registration_id := get_registration_id_from_key(p_registration_key);
760
761 update_reg(
762 l_registration_id,
763 p_application_id,
764 p_party_id,
765 p_registration_type,
766 p_requested_user_name,
767 p_assigned_user_name,
768 p_registration_status,
769 p_exists_in_fnd_user_flag,
770 p_reg_details,
771 p_user_title,
772 p_first_name,
773 p_middle_name,
774 p_last_name,
775 p_user_suffix,
776 p_email_contact_point_id,
777 p_email,
778 p_phone_contact_point_id,
779 p_phone_country_code,
780 p_phone_area_code,
781 p_phone,
782 p_phone_extension,
783 p_fax_contact_point_id,
784 p_fax_country_code,
785 p_fax_area_code,
786 p_fax,
787 p_fax_extension,
788 p_language_code,
789 p_time_zone,
790 p_territory_code,
791 p_location_id,
792 p_address1,
793 p_address2,
794 p_city,
795 p_state,
796 p_province,
797 p_zip,
798 p_postal_code,
799 p_country);
800
801 END update_reg_by_key;
802
803
804 procedure delete_reg(
805 p_registration_id IN NUMBER)
806 IS
807 BEGIN
808 DELETE FROM fnd_registration_details
809 WHERE registration_id = p_registration_id;
810
811 DELETE FROM fnd_registrations
812 WHERE registration_id = p_registration_id;
813 END;
814
815 procedure delete_reg_by_key(
816 p_registration_key IN VARCHAR2)
817 IS
818 l_registration_id NUMBER;
819 BEGIN
820 l_registration_id := get_registration_id_from_key(p_registration_key);
821 delete_reg(l_registration_id);
822 END delete_reg_by_key;
823
824 procedure retrieve_fnd_reg(
825 p_registration_id IN NUMBER,
826 x_application_id OUT nocopy NUMBER,
827 x_party_id OUT nocopy NUMBER,
828 x_registration_type OUT nocopy VARCHAR2,
829 x_requested_user_name OUT nocopy VARCHAR2,
830 x_assigned_user_name OUT nocopy VARCHAR2,
834 x_first_name OUT nocopy VARCHAR2,
831 x_registration_status OUT nocopy VARCHAR2,
832 x_exists_in_fnd_user_flag OUT nocopy VARCHAR2,
833 x_user_title OUT nocopy VARCHAR2,
835 x_middle_name OUT nocopy VARCHAR2,
836 x_last_name OUT nocopy VARCHAR2,
837 x_user_suffix OUT nocopy VARCHAR2,
838 x_email_contact_point_id OUT nocopy NUMBER,
839 x_email OUT nocopy VARCHAR2,
840 x_phone_contact_point_id OUT nocopy NUMBER,
841 x_phone_country_code OUT nocopy VARCHAR2,
842 x_phone_area_code OUT nocopy VARCHAR2,
843 x_phone OUT nocopy VARCHAR2,
844 x_phone_extension OUT nocopy VARCHAR2,
845 x_fax_contact_point_id OUT nocopy NUMBER,
846 x_fax_country_code OUT nocopy VARCHAR2,
847 x_fax_area_code OUT nocopy VARCHAR2,
848 x_fax OUT nocopy VARCHAR2,
849 x_fax_extension OUT nocopy VARCHAR2,
850 x_language_code OUT nocopy VARCHAR2,
851 x_time_zone OUT nocopy VARCHAR2,
852 x_territory_code OUT nocopy VARCHAR2,
853 x_location_id OUT nocopy NUMBER,
854 x_address1 OUT nocopy VARCHAR2,
855 x_address2 OUT nocopy VARCHAR2,
856 x_city OUT nocopy VARCHAR2,
857 x_state OUT nocopy VARCHAR2,
858 x_province OUT nocopy VARCHAR2,
859 x_zip OUT nocopy VARCHAR2,
860 x_postal_code OUT nocopy VARCHAR2,
861 x_country OUT nocopy VARCHAR2)
862 IS
863 BEGIN
864 SELECT application_id,
865 party_id,
866 registration_type,
867 requested_user_name,
868 assigned_user_name,
869 registration_status,
870 exists_in_fnd_user_flag,
871 user_title,
872 first_name,
873 middle_name,
874 last_name,
875 user_suffix,
876 email_contact_point_id,
877 email,
878 phone_contact_point_id,
879 phone_country_code,
880 phone_area_code,
881 phone,
882 phone_extension,
883 fax_contact_point_id,
884 fax_country_code,
885 fax_area_code,
886 fax,
887 fax_extension,
888 language_code,
889 time_zone,
890 territory_code,
891 location_id,
892 address1,
893 address2,
894 city,
895 state,
896 province,
897 zip,
898 postal_code,
899 country
900 INTO x_application_id,
901 x_party_id,
902 x_registration_type,
903 x_requested_user_name,
904 x_assigned_user_name,
905 x_registration_status,
906 x_exists_in_fnd_user_flag,
907 x_user_title,
908 x_first_name,
909 x_middle_name,
910 x_last_name,
911 x_user_suffix,
912 x_email_contact_point_id,
913 x_email,
914 x_phone_contact_point_id,
915 x_phone_country_code,
916 x_phone_area_code,
917 x_phone,
918 x_phone_extension,
919 x_fax_contact_point_id,
920 x_fax_country_code,
921 x_fax_area_code,
922 x_fax,
923 x_fax_extension,
924 x_language_code,
925 x_time_zone,
926 x_territory_code,
927 x_location_id,
928 x_address1,
929 x_address2,
930 x_city,
931 x_state,
932 x_province,
933 x_zip,
934 x_postal_code,
935 x_country
936 FROM fnd_registrations
937 WHERE registration_id = p_registration_id;
938
939 END retrieve_fnd_reg;
940
941 procedure retrieve_fnd_reg_details(
942 p_registration_id IN NUMBER,
943 p_field_name IN VARCHAR2,
944 x_field_type OUT nocopy VARCHAR2,
945 x_field_format OUT nocopy VARCHAR2,
946 x_field_value_string OUT nocopy VARCHAR2,
947 x_field_value_number OUT nocopy NUMBER,
948 x_field_value_date OUT nocopy DATE)
949 IS
950 BEGIN
951 SELECT field_type,
952 field_format,
953 field_value_string,
954 field_value_number,
955 field_value_date
956 INTO x_field_type,
957 x_field_format,
958 x_field_value_string,
959 x_field_value_number,
960 x_field_value_date
961 FROM fnd_registration_details
962 WHERE registration_id = p_registration_id
963 AND field_name = p_field_name;
964 END retrieve_fnd_reg_details;
965
966 procedure retrieve_reg(
967 p_registration_id IN NUMBER,
968 x_application_id OUT nocopy NUMBER,
969 x_party_id OUT nocopy NUMBER,
970 x_registration_type OUT nocopy VARCHAR2,
971 x_requested_user_name OUT nocopy VARCHAR2,
972 x_assigned_user_name OUT nocopy VARCHAR2,
976 x_first_name OUT nocopy VARCHAR2,
973 x_registration_status OUT nocopy VARCHAR2,
974 x_exists_in_fnd_user_flag OUT nocopy VARCHAR2,
975 x_user_title OUT nocopy VARCHAR2,
977 x_middle_name OUT nocopy VARCHAR2,
978 x_last_name OUT nocopy VARCHAR2,
979 x_user_suffix OUT nocopy VARCHAR2,
980 x_email_contact_point_id OUT nocopy NUMBER,
981 x_email OUT nocopy VARCHAR2,
982 x_phone_contact_point_id OUT nocopy NUMBER,
983 x_phone_country_code OUT nocopy VARCHAR2,
984 x_phone_area_code OUT nocopy VARCHAR2,
985 x_phone OUT nocopy VARCHAR2,
986 x_phone_extension OUT nocopy VARCHAR2,
987 x_fax_contact_point_id OUT nocopy NUMBER,
988 x_fax_country_code OUT nocopy VARCHAR2,
989 x_fax_area_code OUT nocopy VARCHAR2,
990 x_fax OUT nocopy VARCHAR2,
991 x_fax_extension OUT nocopy VARCHAR2,
992 x_language_code OUT nocopy VARCHAR2,
993 x_time_zone OUT nocopy VARCHAR2,
994 x_territory_code OUT nocopy VARCHAR2,
995 x_location_id OUT nocopy NUMBER,
996 x_address1 OUT nocopy VARCHAR2,
997 x_address2 OUT nocopy VARCHAR2,
998 x_city OUT nocopy VARCHAR2,
999 x_state OUT nocopy VARCHAR2,
1000 x_province OUT nocopy VARCHAR2,
1001 x_zip OUT nocopy VARCHAR2,
1002 x_postal_code OUT nocopy VARCHAR2,
1003 x_country OUT nocopy VARCHAR2,
1004 x_reg_details OUT nocopy fnd_reg_details_ref_cursor)
1005 IS
1006 BEGIN
1007
1008 -- populate from fnd_registrations first
1009
1010 retrieve_fnd_reg(
1011 p_registration_id,
1012 x_application_id,
1013 x_party_id,
1014 x_registration_type,
1015 x_requested_user_name,
1016 x_assigned_user_name,
1017 x_registration_status,
1018 x_exists_in_fnd_user_flag,
1019 x_user_title,
1020 x_first_name,
1021 x_middle_name,
1022 x_last_name,
1023 x_user_suffix,
1024 x_email_contact_point_id,
1025 x_email,
1026 x_phone_contact_point_id,
1027 x_phone_country_code,
1028 x_phone_area_code,
1029 x_phone,
1030 x_phone_extension,
1031 x_fax_contact_point_id,
1032 x_fax_country_code,
1033 x_fax_area_code,
1034 x_fax,
1035 x_fax_extension,
1036 x_language_code,
1037 x_time_zone,
1038 x_territory_code,
1039 x_location_id,
1040 x_address1,
1041 x_address2,
1042 x_city,
1043 x_state,
1044 x_province,
1045 x_zip,
1046 x_postal_code,
1047 x_country);
1048
1049 -- Now do registration details.
1050
1051 OPEN x_reg_details FOR
1052 SELECT registration_id,
1053 application_id,
1054 registration_type,
1055 field_name,
1056 field_type,
1057 field_format,
1058 field_value_string,
1059 field_value_number,
1060 field_value_date,
1061 last_update_date,
1062 last_updated_by,
1063 created_by,
1064 creation_date,
1065 last_update_login
1066 FROM fnd_registration_details
1067 WHERE registration_id = p_registration_id;
1068
1069 END retrieve_reg;
1070
1071 procedure retrieve_invited_reg(
1072 p_registration_key IN VARCHAR2,
1073 x_application_id OUT nocopy NUMBER,
1074 x_party_id OUT nocopy NUMBER,
1075 x_registration_type OUT nocopy VARCHAR2,
1076 x_requested_user_name OUT nocopy VARCHAR2,
1077 x_assigned_user_name OUT nocopy VARCHAR2,
1078 x_registration_status OUT nocopy VARCHAR2,
1079 x_exists_in_fnd_user_flag OUT nocopy VARCHAR2,
1080 x_user_title OUT nocopy VARCHAR2,
1081 x_first_name OUT nocopy VARCHAR2,
1082 x_middle_name OUT nocopy VARCHAR2,
1083 x_last_name OUT nocopy VARCHAR2,
1084 x_user_suffix OUT nocopy VARCHAR2,
1085 x_email_contact_point_id OUT nocopy NUMBER,
1086 x_email OUT nocopy VARCHAR2,
1087 x_phone_contact_point_id OUT nocopy NUMBER,
1088 x_phone_country_code OUT nocopy VARCHAR2,
1089 x_phone_area_code OUT nocopy VARCHAR2,
1090 x_phone OUT nocopy VARCHAR2,
1091 x_phone_extension OUT nocopy VARCHAR2,
1092 x_fax_contact_point_id OUT nocopy NUMBER,
1093 x_fax_country_code OUT nocopy VARCHAR2,
1094 x_fax_area_code OUT nocopy VARCHAR2,
1095 x_fax OUT nocopy VARCHAR2,
1096 x_fax_extension OUT nocopy VARCHAR2,
1097 x_language_code OUT nocopy VARCHAR2,
1098 x_time_zone OUT nocopy VARCHAR2,
1099 x_territory_code OUT nocopy VARCHAR2,
1103 x_city OUT nocopy VARCHAR2,
1100 x_location_id OUT nocopy NUMBER,
1101 x_address1 OUT nocopy VARCHAR2,
1102 x_address2 OUT nocopy VARCHAR2,
1104 x_state OUT nocopy VARCHAR2,
1105 x_province OUT nocopy VARCHAR2,
1106 x_zip OUT nocopy VARCHAR2,
1107 x_postal_code OUT nocopy VARCHAR2,
1108 x_country OUT nocopy VARCHAR2,
1109 x_reg_details OUT nocopy fnd_reg_details_ref_cursor)
1110 IS
1111 l_registration_id NUMBER;
1112 BEGIN
1113 l_registration_id := get_registration_id_from_key(p_registration_key);
1114 retrieve_reg(
1115 l_registration_id,
1116 x_application_id,
1117 x_party_id,
1118 x_registration_type,
1119 x_requested_user_name,
1120 x_assigned_user_name,
1121 x_registration_status,
1122 x_exists_in_fnd_user_flag,
1123 x_user_title,
1124 x_first_name,
1125 x_middle_name,
1126 x_last_name,
1127 x_user_suffix,
1128 x_email_contact_point_id,
1129 x_email,
1130 x_phone_contact_point_id,
1131 x_phone_country_code,
1132 x_phone_area_code,
1133 x_phone,
1134 x_phone_extension,
1135 x_fax_contact_point_id,
1136 x_fax_country_code,
1137 x_fax_area_code,
1138 x_fax,
1139 x_fax_extension,
1140 x_language_code,
1141 x_time_zone,
1142 x_territory_code,
1143 x_location_id,
1144 x_address1,
1145 x_address2,
1146 x_city,
1147 x_state,
1148 x_province,
1149 x_zip,
1150 x_postal_code,
1151 x_country,
1152 x_reg_details);
1153
1154 END retrieve_invited_reg;
1155
1156 procedure retrieve_reg_by_username(
1157 p_assigned_user_name IN VARCHAR2,
1158 p_application_id IN NUMBER,
1159 p_registration_type IN VARCHAR2,
1160 x_party_id OUT nocopy NUMBER,
1161 x_registration_status OUT nocopy VARCHAR2,
1162 x_exists_in_fnd_user_flag OUT nocopy VARCHAR2,
1163 x_user_title OUT nocopy VARCHAR2,
1164 x_first_name OUT nocopy VARCHAR2,
1165 x_middle_name OUT nocopy VARCHAR2,
1166 x_last_name OUT nocopy VARCHAR2,
1167 x_user_suffix OUT nocopy VARCHAR2,
1168 x_email_contact_point_id OUT nocopy NUMBER,
1169 x_email OUT nocopy VARCHAR2,
1170 x_phone_contact_point_id OUT nocopy NUMBER,
1171 x_phone_country_code OUT nocopy VARCHAR2,
1172 x_phone_area_code OUT nocopy VARCHAR2,
1173 x_phone OUT nocopy VARCHAR2,
1174 x_phone_extension OUT nocopy VARCHAR2,
1175 x_fax_contact_point_id OUT nocopy NUMBER,
1176 x_fax_country_code OUT nocopy VARCHAR2,
1177 x_fax_area_code OUT nocopy VARCHAR2,
1178 x_fax OUT nocopy VARCHAR2,
1179 x_fax_extension OUT nocopy VARCHAR2,
1180 x_language_code OUT nocopy VARCHAR2,
1181 x_time_zone OUT nocopy VARCHAR2,
1182 x_territory_code OUT nocopy VARCHAR2,
1183 x_location_id OUT nocopy NUMBER,
1184 x_address1 OUT nocopy VARCHAR2,
1185 x_address2 OUT nocopy VARCHAR2,
1186 x_city OUT nocopy VARCHAR2,
1187 x_state OUT nocopy VARCHAR2,
1188 x_province OUT nocopy VARCHAR2,
1189 x_zip OUT nocopy VARCHAR2,
1190 x_postal_code OUT nocopy VARCHAR2,
1191 x_country OUT nocopy VARCHAR2,
1192 x_reg_details OUT nocopy fnd_reg_details_ref_cursor)
1193 IS
1194 l_registration_id NUMBER;
1195 l_application_id NUMBER;
1196 l_registration_type VARCHAR2(255);
1197 l_requested_user_name VARCHAR2(100);
1198 l_assigned_user_name VARCHAR2(100);
1199 BEGIN
1200 SELECT registration_id
1201 INTO l_registration_id
1202 FROM fnd_registrations
1203 WHERE assigned_user_name = p_assigned_user_name
1204 AND application_id = p_application_id
1205 AND registration_type = p_registration_type;
1206
1207 retrieve_reg(
1208 l_registration_id,
1209 l_application_id,
1210 x_party_id,
1211 l_registration_type,
1212 l_requested_user_name,
1213 l_assigned_user_name,
1214 x_registration_status,
1215 x_exists_in_fnd_user_flag,
1216 x_user_title,
1217 x_first_name,
1218 x_middle_name,
1219 x_last_name,
1220 x_user_suffix,
1221 x_email_contact_point_id,
1222 x_email,
1223 x_phone_contact_point_id,
1224 x_phone_country_code,
1225 x_phone_area_code,
1226 x_phone,
1227 x_phone_extension,
1228 x_fax_contact_point_id,
1229 x_fax_country_code,
1230 x_fax_area_code,
1231 x_fax,
1232 x_fax_extension,
1233 x_language_code,
1234 x_time_zone,
1235 x_territory_code,
1236 x_location_id,
1237 x_address1,
1241 x_province,
1238 x_address2,
1239 x_city,
1240 x_state,
1242 x_zip,
1243 x_postal_code,
1244 x_country,
1245 x_reg_details);
1246
1247 END retrieve_reg_by_username;
1248
1249
1250 procedure retrieve_reg_by_party_id(
1251 p_party_id IN NUMBER,
1252 p_application_id IN NUMBER,
1253 p_registration_type IN VARCHAR2,
1254 x_requested_user_name OUT nocopy VARCHAR2,
1255 x_assigned_user_name OUT nocopy VARCHAR2,
1256 x_registration_status OUT nocopy VARCHAR2,
1257 x_exists_in_fnd_user_flag OUT nocopy VARCHAR2,
1258 x_user_title OUT nocopy VARCHAR2,
1259 x_first_name OUT nocopy VARCHAR2,
1260 x_middle_name OUT nocopy VARCHAR2,
1261 x_last_name OUT nocopy VARCHAR2,
1262 x_user_suffix OUT nocopy VARCHAR2,
1263 x_email_contact_point_id OUT nocopy NUMBER,
1264 x_email OUT nocopy VARCHAR2,
1265 x_phone_contact_point_id OUT nocopy NUMBER,
1266 x_phone_country_code OUT nocopy VARCHAR2,
1267 x_phone_area_code OUT nocopy VARCHAR2,
1268 x_phone OUT nocopy VARCHAR2,
1269 x_phone_extension OUT nocopy VARCHAR2,
1270 x_fax_contact_point_id OUT nocopy NUMBER,
1271 x_fax_country_code OUT nocopy VARCHAR2,
1272 x_fax_area_code OUT nocopy VARCHAR2,
1273 x_fax OUT nocopy VARCHAR2,
1274 x_fax_extension OUT nocopy VARCHAR2,
1275 x_language_code OUT nocopy VARCHAR2,
1276 x_time_zone OUT nocopy VARCHAR2,
1277 x_territory_code OUT nocopy VARCHAR2,
1278 x_location_id OUT nocopy NUMBER,
1279 x_address1 OUT nocopy VARCHAR2,
1280 x_address2 OUT nocopy VARCHAR2,
1281 x_city OUT nocopy VARCHAR2,
1282 x_state OUT nocopy VARCHAR2,
1283 x_province OUT nocopy VARCHAR2,
1284 x_zip OUT nocopy VARCHAR2,
1285 x_postal_code OUT nocopy VARCHAR2,
1286 x_country OUT nocopy VARCHAR2,
1287 x_reg_details OUT nocopy fnd_reg_details_ref_cursor)
1288 IS
1289 l_registration_id NUMBER;
1290 l_application_id NUMBER;
1291 l_party_id NUMBER;
1292 l_registration_type VARCHAR2(255);
1293 BEGIN
1294 SELECT registration_id
1295 INTO l_registration_id
1296 FROM fnd_registrations
1297 WHERE registration_type = p_registration_type
1298 AND application_id = p_application_id
1299 AND party_id = p_party_id;
1300
1301 retrieve_reg(
1302 l_registration_id,
1303 l_application_id,
1304 l_party_id,
1305 l_registration_type,
1306 x_requested_user_name,
1307 x_assigned_user_name,
1308 x_registration_status,
1309 x_exists_in_fnd_user_flag,
1310 x_user_title,
1311 x_first_name,
1312 x_middle_name,
1313 x_last_name,
1314 x_user_suffix,
1315 x_email_contact_point_id,
1316 x_email,
1317 x_phone_contact_point_id,
1318 x_phone_country_code,
1319 x_phone_area_code,
1320 x_phone,
1321 x_phone_extension,
1322 x_fax_contact_point_id,
1323 x_fax_country_code,
1324 x_fax_area_code,
1325 x_fax,
1326 x_fax_extension,
1327 x_language_code,
1328 x_time_zone,
1329 x_territory_code,
1330 x_location_id,
1331 x_address1,
1332 x_address2,
1333 x_city,
1334 x_state,
1335 x_province,
1336 x_zip,
1337 x_postal_code,
1338 x_country,
1339 x_reg_details);
1340
1341 END retrieve_reg_by_party_id;
1342
1343 function get_registration_id_from_key(
1344 p_registration_key IN VARCHAR2)
1345 return NUMBER
1346 IS
1347 l_registration_id NUMBER;
1348 BEGIN
1349 SELECT registration_id
1350 INTO l_registration_id
1351 FROM fnd_registrations
1352 WHERE registration_key = p_registration_key;
1353 return l_registration_id;
1354
1355 END get_registration_id_from_key;
1356
1357 function get_registration_key_from_id(
1358 p_registration_id IN NUMBER)
1359 return VARCHAR2
1360 IS
1361 l_registration_key VARCHAR2(255);
1362 BEGIN
1363
1364 SELECT registration_key
1365 INTO l_registration_key
1366 FROM fnd_registrations
1367 WHERE registration_id = p_registration_id;
1368
1369 return l_registration_key;
1370
1371 END get_registration_key_from_id;
1372
1373
1374 end FND_REGISTRATION_PKG;
1375