[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_IMPORT_USER_PUB
Source
1 PACKAGE BODY jtf_rs_import_user_pub AS
2 /* $Header: jtfrsiub.pls 115.3 2002/12/20 07:39:58 smuniraj ship $ */
3
4 /*****************************************************************************************
5 ******************************************************************************************/
6
7 /* Package variables. */
8
9 G_PKG_NAME VARCHAR2(30) := 'JTF_RS_IMPORT_USER_PUB';
10
11 PROCEDURE crt_bulk_import (
12 ERRBUF OUT NOCOPY VARCHAR2,
13 RETCODE OUT NOCOPY VARCHAR2,
14 P_TRANSACTION_NO IN NUMBER
15 ) IS
16
17 l_api_name constant varchar2(30) := 'CRT_BULK_IMPORT';
18 l_return_status varchar2(100) := fnd_api.g_ret_sts_success;
19 l_msg_count number;
20 l_msg_data varchar2(2000);
21 l_msg_data1 varchar2(2000);
22 l_msg_index_out varchar2(2000);
23
24 l_first_name jtf_rs_resource_extns.source_first_name%type ;
25 l_last_name jtf_rs_resource_extns.source_last_name%type ;
26 l_user_name jtf_rs_resource_extns.user_name%type ;
27 l_email jtf_rs_resource_extns.source_email%type ;
28 l_mgr_user_name jtf_rs_resource_extns.user_name%type ;
29
30 l_nav_group1 jtf_iapp_families_vl.app_family_display_name%type;
31 l_resp_group1 fnd_responsibility_vl.responsibility_name%type;
32 l_res_role1 jtf_rs_roles_vl.role_name%type;
33
34 l_nav_group2 jtf_iapp_families_vl.app_family_display_name%type;
35 l_resp_group2 fnd_responsibility_vl.responsibility_name%type;
36 l_res_role2 jtf_rs_roles_vl.role_name%type;
37
38 l_employee_number per_all_people_f.employee_number%type ;
39 l_resource_group jtf_rs_groups_vl.group_name%type ;
40 l_default_resp fnd_responsibility_vl.responsibility_name%type ;
41
42 l_business_group_id per_all_people_f.business_group_id%type;
43 l_mgr_employee_id fnd_user.employee_id%type;
44
45 cursor upload_data is
46 select column1, column2, column3, column4, column5, column6, column7, column8,
47 column9, column10, column11, column12, column13, record_no
48 from jtf_rs_upload_data
49 where transaction_no = p_transaction_no;
50
51 cursor get_mgr_emp_id (p_mgr_user_name fnd_user.user_name%type) is
52 select employee_id
53 from fnd_user
54 where user_name = p_mgr_user_name;
55
56 cursor c_dup_user_name (l_user_name fnd_user.user_name%type) is
57 select 1 from fnd_user
58 where user_name = l_user_name;
59
60 cursor c_mgr_user_name (l_mgr_user_name fnd_user.user_name%type) is
61 select user_id from fnd_user
62 where user_name = l_mgr_user_name;
63
64 cursor c_mgr_resource_id (l_mgr_user_id jtf_rs_resource_extns.user_id%type) is
65 select resource_id
66 from jtf_rs_resource_extns
67 where user_id = l_mgr_user_id;
68
69 cursor c_nav_group (l_nav_group jtf_iapp_families_vl.app_family_display_name%type) is
70 select app_family_id
71 from jtf_iapp_families_vl
72 where app_family_display_name = l_nav_group;
73
74 cursor c_resp_group (l_resp_group fnd_responsibility_vl.responsibility_name%type) is
75 select responsibility_id
76 from fnd_responsibility_vl
77 where responsibility_name = l_resp_group;
78
79 cursor c_nav_resp_group (l_app_family_id jtf_iapp_families_b.app_family_id%type,
80 l_responsibility_id fnd_responsibility.responsibility_id%type) is
81 select 1 from jtf_iapp_families_b jif, fnd_responsibility fr,
82 jtf_iapp_family_app_map jpm
83 where jif.app_family_id = jpm.app_family_id
84 and jpm.application_id = fr.application_id
85 and fr.responsibility_id = l_responsibility_id
86 and jif.app_family_id = l_app_family_id;
87
88 cursor c_method_emp_gen (l_business_group_id per_all_people_f.business_group_id%type) is
89 select method_of_generation_emp_num
90 from per_business_groups
91 where business_group_id =l_business_group_id;
92
93 cursor c_res_group (l_resource_group jtf_rs_groups_vl.group_name%type) is
94 select group_id
95 from jtf_rs_groups_vl
96 where group_name = l_resource_group;
97
98 cursor c_res_role (l_role_name jtf_rs_roles_vl.role_name%type) is
99 select role_id, role_code
100 from jtf_rs_roles_vl
101 where role_name = l_role_name;
102
103 cursor c_get_user_id (l_user_name jtf_rs_resource_extns.user_name%type) is
104 select user_id
105 from fnd_user
106 where user_name = l_user_name;
107
108 l_resource_id number;
109 l_user_id number;
110 l_user_password fnd_user.encrypted_user_password%type := NULL;
111 l_group_member_id number;
112 l_role_relate_id number;
113
114 l_mgr_user_id jtf_rs_resource_extns.user_id%type;
115 l_mgr_resource_id jtf_rs_resource_extns.resource_id%type;
116 l_group_id jtf_rs_groups_b.group_id%type;
117 l_role_id1 jtf_rs_roles_b.role_id%type;
118 l_role_id2 jtf_rs_roles_b.role_id%type;
119 l_role_code1 jtf_rs_roles_b.role_code%type;
120 l_role_code2 jtf_rs_roles_b.role_code%type;
121
122 l_app_family_id1 jtf_iapp_families_b.app_family_id%type;
123 l_app_family_id2 jtf_iapp_families_b.app_family_id%type;
124 l_responsibility_id1 fnd_responsibility.responsibility_id%type;
125 l_responsibility_id2 fnd_responsibility.responsibility_id%type;
126
127 l_num number;
128 l_method_emp_gen varchar2(150);
129 l_assign_def_resp boolean;
130 l_error_flag varchar2(1) := 'N';
131 l_error_flag_resp varchar2(1) := 'N';
132 l_error_flag_role varchar2(1) := 'N';
133 l_error_flag_grp varchar2(1) := 'N';
134 l_record_no jtf_rs_upload_data.record_no%type;
135 l_error_text jtf_rs_upload_data.error_text%type := null;
136
137 BEGIN
138
139 for i_upload_data in upload_data loop
140 fnd_msg_pub.Initialize;
141
142 begin
143 savepoint jtf_rs_bulk_import;
144
145 l_first_name := i_upload_data.column1;
146 l_last_name := i_upload_data.column2;
147 l_user_name := upper(i_upload_data.column3);
148 l_employee_number := i_upload_data.column4;
149 l_email := i_upload_data.column5;
150 l_mgr_user_name := upper (i_upload_data.column6);
151 l_resource_group := i_upload_data.column7;
152 l_nav_group1 := i_upload_data.column8;
153 l_resp_group1 := i_upload_data.column9;
154 l_res_role1 := i_upload_data.column10;
155 l_nav_group2 := i_upload_data.column11;
156 l_resp_group2 := i_upload_data.column12;
157 l_res_role2 := i_upload_data.column13;
158 l_default_resp := l_resp_group1;
159 l_record_no := i_upload_data.record_no;
160
161 l_error_flag := 'N';
162 l_error_flag_resp := 'N';
163 l_error_flag_role := 'N';
164 l_error_flag_grp := 'N';
165
166 l_error_text := null;
167 l_msg_data := null;
168 l_msg_data1 := null;
169 l_msg_index_out := null;
170
171 --Employee Number Generation
172
173 fnd_profile.get('PER_BUSINESS_GROUP_ID',l_business_group_id);
174
175 open c_method_emp_gen (l_business_group_id);
176 fetch c_method_emp_gen into l_method_emp_gen;
177 close c_method_emp_gen;
178
179 if l_method_emp_gen = 'A' then
180 l_employee_number := null;
181 end if;
182
183 --Put all the Validations here
184
185 --Validate Last Name
186 if l_last_name is null then
187 fnd_message.set_name ('JTF','JTF_RS_LAST_NAME_NULL');
188 fnd_msg_pub.add;
189 l_error_flag := 'Y';
190 end if;
191
192 --Validate User Name
193 if l_user_name is null then
194 fnd_message.set_name ('JTF','JTF_RS_USER_NAME_NULL');
195 fnd_msg_pub.add;
196 l_error_flag := 'Y';
197 else
198 open c_dup_user_name (l_user_name);
199 fetch c_dup_user_name into l_num;
200 if c_dup_user_name%found then
201 fnd_message.set_name ('JTF','JTF_RS_USER_EXISTS');
202 fnd_message.set_token ('P_USER_NAME',l_user_name);
203 fnd_msg_pub.add;
204 l_error_flag := 'Y';
205 end if;
206 close c_dup_user_name;
207 end if;
208
209 --Validate Manager User
210 if l_mgr_user_name is not null then
211 open c_mgr_user_name (l_mgr_user_name);
212 fetch c_mgr_user_name into l_mgr_user_id;
213 if c_mgr_user_name%notfound then
214 fnd_message.set_name ('JTF','JTF_RS_INV_MGR_USER');
215 fnd_message.set_token ('P_MGR_USER_NAME',l_mgr_user_name);
216 fnd_msg_pub.add;
217 l_error_flag := 'Y';
218 else
219 open c_mgr_resource_id (l_mgr_user_id);
220 fetch c_mgr_resource_id into l_mgr_resource_id;
221 if c_mgr_resource_id%notfound then
222 fnd_message.set_name ('JTF','JTF_RS_MGR_INV_RESOURCE');
223 fnd_message.set_token ('P_MGR_USER_NAME',l_mgr_user_name);
224 fnd_msg_pub.add;
225 l_error_flag := 'Y';
226 end if;
227 close c_mgr_resource_id;
228 end if;
229 close c_mgr_user_name;
230 end if;
231
232 --Validate Nav Groups
233 if l_nav_group1 is null then
234 fnd_message.set_name ('JTF','JTF_RS_NAV_GRP_NULL');
235 fnd_msg_pub.add;
236 l_error_flag_resp := 'Y';
237 else
238 open c_nav_group (l_nav_group1);
239 fetch c_nav_group into l_app_family_id1;
240 if c_nav_group%notfound then
241 fnd_message.set_name ('JTF','JTF_RS_INV_NAV_GRP');
242 fnd_message.set_token ('P_NAV_GROUP',l_nav_group1);
243 fnd_msg_pub.add;
244 l_error_flag_resp := 'Y';
245 end if;
246 close c_nav_group;
247 end if;
248
249 if l_nav_group2 is not null then
250 open c_nav_group (l_nav_group2);
251 fetch c_nav_group into l_app_family_id2;
252 if c_nav_group%notfound then
253 fnd_message.set_name ('JTF','JTF_RS_INV_NAV_GRP');
254 fnd_message.set_token ('P_NAV_GROUP',l_nav_group2);
255 fnd_msg_pub.add;
256 l_error_flag_resp := 'Y';
257 end if;
258 close c_nav_group;
259 end if;
260
261 --Validate Responsibility
262 if l_resp_group1 is null then
263 fnd_message.set_name ('JTF','JTF_RS_RESP_NAME_NULL');
264 fnd_msg_pub.add;
265 l_error_flag_resp := 'Y';
266 else
267 open c_resp_group (l_resp_group1);
268 fetch c_resp_group into l_responsibility_id1;
269 if c_resp_group%notfound then
270 fnd_message.set_name ('JTF','JTF_RS_INV_RESP_NAME');
271 fnd_message.set_token ('P_RESP_NAME',l_resp_group1);
272 fnd_msg_pub.add;
273 l_error_flag_resp := 'Y';
274 end if;
275 close c_resp_group;
276 end if;
277
278 if l_resp_group2 is not null then
279 open c_resp_group (l_resp_group2);
280 fetch c_resp_group into l_responsibility_id2;
281 if c_resp_group%notfound then
282 fnd_message.set_name ('JTF','JTF_RS_INV_RESP_NAME');
283 fnd_message.set_token ('P_RESP_NAME',l_resp_group2);
284 fnd_msg_pub.add;
285 l_error_flag_resp := 'Y';
286 end if;
287 close c_resp_group;
288 end if;
289
290 --Validate Nav-Resp Groups
291 if (l_app_family_id1 is not null) AND (l_responsibility_id1 is not null) then
292 open c_nav_resp_group (l_app_family_id1, l_responsibility_id1);
293 fetch c_nav_resp_group into l_num;
294 if c_nav_resp_group%notfound then
295 fnd_message.set_name ('JTF','JTF_RS_INV_NAV_RESP_MAP');
296 fnd_message.set_token ('P_RESP_NAME',l_resp_group1);
297 fnd_message.set_token ('P_NAV_GROUP',l_nav_group1);
298 fnd_msg_pub.add;
299 l_error_flag_resp := 'Y';
300 end if;
301 close c_nav_resp_group;
302 end if;
303
304 if (l_app_family_id2 is not null) AND (l_responsibility_id2 is not null) then
305 open c_nav_resp_group (l_app_family_id2, l_responsibility_id2);
306 fetch c_nav_resp_group into l_num;
307 if c_nav_resp_group%notfound then
308 fnd_message.set_name ('JTF','JTF_RS_INV_NAV_RESP_MAP');
309 fnd_message.set_token ('P_RESP_NAME',l_resp_group2);
310 fnd_message.set_token ('P_NAV_GROUP',l_nav_group2);
311 fnd_msg_pub.add;
312 l_error_flag_resp := 'Y';
313 end if;
314 close c_nav_resp_group;
315 end if;
316
317 --Validate Resource Group
318 if l_resource_group is not null then
319 open c_res_group (l_resource_group);
320 fetch c_res_group into l_group_id;
321 if c_res_group%notfound then
322 fnd_message.set_name ('JTF','JTF_RS_INV_RES_GRP');
323 fnd_message.set_token ('P_RES_GRP',l_resource_group);
324 fnd_msg_pub.add;
325 l_error_flag_grp := 'Y';
326 end if;
327 close c_res_group;
328 end if;
329
330 --Validate Resource Roles
331 if l_res_role1 is not null then
332 open c_res_role (l_res_role1);
333 fetch c_res_role into l_role_id1, l_role_code1;
334 if c_res_role%notfound then
335 fnd_message.set_name ('JTF','JTF_RS_INV_ROLE_NAME');
336 fnd_message.set_token ('P_ROLE_NAME',l_res_role1);
337 fnd_msg_pub.add;
338 l_error_flag_role := 'Y';
339 end if;
340 close c_res_role;
341 end if;
342
343 if l_res_role2 is not null then
344 open c_res_role (l_res_role2);
345 fetch c_res_role into l_role_id2, l_role_code2;
346 if c_res_role%notfound then
347 fnd_message.set_name ('JTF','JTF_RS_INV_ROLE_NAME');
348 fnd_message.set_token ('P_ROLE_NAME',l_res_role2);
349 fnd_msg_pub.add;
350 l_error_flag_role := 'Y';
351 end if;
352 close c_res_role;
353 end if;
354
355 if l_error_flag <> 'Y' then
356 --Create the Employee Resource
357 jtf_rs_res_sswa_pub.create_emp_resource (
358 P_API_VERSION => 1.0,
359 P_INIT_MSG_LIST => 'F',
360 P_SOURCE_FIRST_NAME => l_first_name,
361 P_SOURCE_LAST_NAME => l_last_name,
362 P_EMPLOYEE_NUMBER => l_employee_number,
363 P_SOURCE_SEX => 'M',
364 P_SOURCE_EMAIL => l_email,
365 P_SOURCE_START_DATE => trunc(sysdate),
366 P_SOURCE_END_DATE => null,
367 P_USER_NAME => l_user_name,
368 P_SALESREP_NUMBER => NULL,
369 P_SALES_CREDIT_TYPE_ID => 1, /* This needs to be figured out */
370 P_SOURCE_MGR_ID => l_mgr_resource_id, /* Resource_id of the manager */
371 P_CALLED_FROM => 'CRT_BULK_IMPORT',
372 P_USER_PASSWORD => l_user_password,
373 X_RESOURCE_ID => l_resource_id,
374 X_RETURN_STATUS => l_return_status,
375 X_MSG_COUNT => l_msg_count,
376 X_MSG_DATA => l_msg_data
377 );
378 if not (l_return_status = fnd_api.g_ret_sts_success) THEN
379 l_error_flag := 'Y';
380 end if;
381 end if;
382
383 if (l_error_flag <> 'Y' AND l_error_flag_grp <> 'Y') then
384 -- Create Group Member
385 if l_group_id is not null then
386 jtf_rs_group_members_pub.create_resource_group_members (
387 p_api_version => 1.0,
388 p_init_msg_list => 'F',
389 p_group_id => l_group_id,
390 p_resource_id => l_resource_id,
391 p_group_number => null,
392 p_resource_number => null,
393 x_return_status => l_return_status,
394 x_msg_count => l_msg_count,
395 x_msg_data => l_msg_data,
396 x_group_member_id => l_group_member_id
397 );
398 if not (l_return_status = fnd_api.g_ret_sts_success) THEN
399 l_error_flag_grp := 'Y';
400 end if;
401 end if;
402 end if;
403
404 if (l_error_flag <> 'Y' AND l_error_flag_role <> 'Y') then
405 if (l_role_id1 is not null) then
406 -- Create Roles for the Resource created above
407 jtf_rs_role_relate_pub.create_resource_role_relate (
408 p_api_version => 1.0,
409 p_init_msg_list => 'F',
410 p_role_resource_type => 'RS_INDIVIDUAL',
411 p_role_resource_id => l_resource_id,
412 p_role_id => l_role_id1,
413 p_role_code => l_role_code1,
414 p_start_date_active => trunc(sysdate),
415 p_end_date_active => null,
416 x_return_status => l_return_status,
417 x_msg_count => l_msg_count,
418 x_msg_data => l_msg_data,
419 x_role_relate_id => l_role_relate_id
420 );
421 if not (l_return_status = fnd_api.g_ret_sts_success) THEN
422 l_error_flag_role := 'Y';
423 end if;
424 end if;
425
426 if (l_role_id2 is not null) then
427 jtf_rs_role_relate_pub.create_resource_role_relate (
428 p_api_version => 1.0,
429 p_init_msg_list => 'F',
430 p_role_resource_type => 'RS_INDIVIDUAL',
431 p_role_resource_id => l_resource_id,
432 p_role_id => l_role_id2,
433 p_role_code => l_role_code2,
434 p_start_date_active => trunc(sysdate),
435 p_end_date_active => null,
436 x_return_status => l_return_status,
437 x_msg_count => l_msg_count,
438 x_msg_data => l_msg_data,
439 x_role_relate_id => l_role_relate_id
440 );
441 if not (l_return_status = fnd_api.g_ret_sts_success) THEN
442 l_error_flag_role := 'Y';
443 end if;
444 end if;
445 end if;
446
447 if (l_error_flag <> 'Y' AND l_error_flag_role <> 'Y' AND l_error_flag_grp <> 'Y') then
448 if (l_role_id1 is not null AND l_group_id is not null) then
449 -- Create Group Member Roles
450 jtf_rs_role_relate_pub.create_resource_role_relate (
451 p_api_version => 1.0,
452 p_init_msg_list => 'F',
453 p_role_resource_type => 'RS_GROUP_MEMBER',
454 p_role_resource_id => l_group_member_id,
455 p_role_id => l_role_id1,
456 p_role_code => l_role_code1,
457 p_start_date_active => trunc(sysdate),
458 p_end_date_active => null,
459 x_return_status => l_return_status,
460 x_msg_count => l_msg_count,
461 x_msg_data => l_msg_data,
462 x_role_relate_id => l_role_relate_id
463 );
464 if not (l_return_status = fnd_api.g_ret_sts_success) THEN
465 l_error_flag_role := 'Y';
466 end if;
467 end if;
468
469 if (l_role_id2 is not null AND l_group_id is not null) then
470 jtf_rs_role_relate_pub.create_resource_role_relate (
471 p_api_version => 1.0,
472 p_init_msg_list => 'F',
473 p_role_resource_type => 'RS_GROUP_MEMBER',
474 p_role_resource_id => l_group_member_id,
475 p_role_id => l_role_id2,
476 p_role_code => l_role_code2,
477 p_start_date_active => trunc(sysdate),
478 p_end_date_active => null,
479 x_return_status => l_return_status,
480 x_msg_count => l_msg_count,
481 x_msg_data => l_msg_data,
482 x_role_relate_id => l_role_relate_id
483 );
484 if not (l_return_status = fnd_api.g_ret_sts_success) THEN
485 l_error_flag_role := 'Y';
486 end if;
487 end if;
488 end if;
489
490 if (l_error_flag <> 'Y' AND l_error_flag_resp <> 'Y') then
491
492 open c_get_user_id (l_user_name);
493 fetch c_get_user_id into l_user_id;
494 close c_get_user_id;
495
496 --Create Fnd User Responsibility Groups
497 Fnd_User_Resp_Groups_Api.Insert_Assignment(
498 l_user_id,
499 l_responsibility_id1,
500 690,
501 0,
502 trunc(sysdate),
503 null,
504 null
505 );
506
507 if l_responsibility_id2 is not null then
508
509 Fnd_User_Resp_Groups_Api.Insert_Assignment(
510 l_user_id,
511 l_responsibility_id2,
512 690,
513 0,
514 trunc(sysdate),
515 null,
516 null
517 );
518 end if;
519
520 --Create Default Responsibility
521 l_assign_def_resp := fnd_profile.save
522 (X_NAME => 'JTF_PROFILE_DEFAULT_RESPONSIBILITY',
523 X_VALUE => l_responsibility_id1,
524 X_LEVEL_NAME => 'USER',
525 X_LEVEL_VALUE => l_user_id);
526 end if;
527
528 if (l_error_flag = 'Y' OR l_error_flag_resp = 'Y' OR
529 l_error_flag_grp = 'Y' OR l_error_flag_role = 'Y') then
530 if (fnd_msg_pub.count_msg > 0) then
531 for i in 1..fnd_msg_pub.count_msg loop
532 fnd_msg_pub.get
533 (p_msg_index => i,
534 p_data => l_msg_data,
535 p_encoded => 'F',
536 p_msg_index_out => l_msg_index_out
537 );
538 l_msg_data1 := l_msg_data1||FND_GLOBAL.Local_Chr(10)||l_msg_data;
539 end loop;
540 fnd_message.set_encoded(l_msg_data1);
541 l_error_text := l_msg_data1;
542 end if;
543
544 fnd_message.set_name ('JTF','JTF_RS_USER_NOT_CREATED');
545 fnd_message.set_token ('P_USER_NAME',l_user_name);
546 l_error_text := fnd_message.get||l_error_text;
547
548 update_upload_data (
549 P_TRANSACTION_NO => p_transaction_no,
550 P_RECORD_NO => l_record_no,
551 P_PROCESS_STATUS => 'U',
552 P_ERROR_TEXT => l_error_text
553 );
554 rollback to jtf_rs_bulk_import;
555 else
556 -- initiate the workflow to send the password
557 jtf_um_password_pvt.send_password(
558 p_api_version_number => 1.0,
559 p_requester_user_name => l_user_name,
560 p_requester_password => l_user_password,
561 p_first_time_user => 'Y',
562 p_user_verified => 'Y',
563 x_return_status => l_return_status,
564 x_msg_count => l_msg_count,
565 x_msg_data => l_msg_data
566 );
567
568 if not (l_return_status = fnd_api.g_ret_sts_success) THEN
569 fnd_message.set_name ('JTF','JTF_RS_USER_CRT_WITH_WARNING');
570 fnd_message.set_token ('P_USER_NAME',l_user_name);
571 l_error_text := fnd_message.get;
572
573 fnd_message.set_name ('JTF','JTF_RS_UNABLE_SEND_PASSWD');
574 l_error_text := l_error_text||FND_GLOBAL.Local_Chr(10)||fnd_message.get;
575
576 update_upload_data (
577 P_TRANSACTION_NO => p_transaction_no,
578 P_RECORD_NO => l_record_no,
579 P_PROCESS_STATUS => 'W',
580 P_ERROR_TEXT => l_error_text
581 );
582 else
583 fnd_message.set_name ('JTF','JTF_RS_USER_CREATED');
584 fnd_message.set_token ('P_USER_NAME',l_user_name);
585 l_error_text := fnd_message.get;
586
587 fnd_message.set_name ('JTF','JTF_RS_USER_PASSWD');
588 fnd_message.set_token ('P_USER_PASSWD',l_user_password);
589 l_error_text := l_error_text||FND_GLOBAL.Local_Chr(10)||fnd_message.get;
590
591 update_upload_data (
592 P_TRANSACTION_NO => p_transaction_no,
593 P_RECORD_NO => l_record_no,
594 P_PROCESS_STATUS => 'S',
595 P_ERROR_TEXT => l_error_text
596 );
597 commit work;
598 end if;
599 end if;
600
601 exception
602 when others then
603 if (fnd_msg_pub.count_msg > 0) then
604 for i in 1..fnd_msg_pub.count_msg loop
605 fnd_msg_pub.get
606 (p_msg_index => i,
607 p_data => l_msg_data,
608 p_encoded => 'F',
609 p_msg_index_out => l_msg_index_out
610 );
611 l_msg_data1 := l_msg_data1||FND_GLOBAL.Local_Chr(10)||l_msg_data;
612 end loop;
613 fnd_message.set_encoded(l_msg_data1);
614 l_error_text := l_msg_data1;
615 end if;
616
617 l_error_text := l_error_text||FND_GLOBAL.Local_Chr(10)||sqlcode||' : '||sqlerrm;
618
619 update_upload_data (
620 P_TRANSACTION_NO => p_transaction_no,
621 P_RECORD_NO => l_record_no,
622 P_PROCESS_STATUS => 'U',
623 P_ERROR_TEXT => l_error_text
624 );
625 rollback to jtf_rs_bulk_import;
626 end;
627 end loop;
628
629 END crt_bulk_import;
630
631 PROCEDURE import_user
632 (P_API_VERSION IN NUMBER,
633 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
634 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
635 P_TRANSACTION_NO IN JTF_RS_UPLOAD_DATA.TRANSACTION_NO%TYPE,
636 P_REQUEST_NO OUT NOCOPY NUMBER,
637 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
638 X_MSG_COUNT OUT NOCOPY NUMBER,
639 X_MSG_DATA OUT NOCOPY VARCHAR2
640 )
641 IS
642
643 l_api_name CONSTANT VARCHAR2(30) := 'IMPORT_USER';
644 l_request NUMBER;
645
646 begin
647
648 x_return_status := fnd_api.g_ret_sts_success;
649
650 l_request := fnd_request.submit_request(application => 'JTF',
651 program => 'JTFRSIMPUSER',
652 argument1 => p_transaction_no);
653
654 p_request_no := l_request;
655
656 update jtf_rs_upload_data
657 set request_id = l_request
658 where transaction_no = p_transaction_no;
659
660 exception when others then
661 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
662 fnd_message.set_token('P_SQLCODE',SQLCODE);
663 fnd_message.set_token('P_SQLERRM',SQLERRM);
664 fnd_message.set_token('P_API_NAME', l_api_name);
665 FND_MSG_PUB.add;
666 x_return_status := fnd_api.g_ret_sts_unexp_error;
667 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
668 RAISE fnd_api.g_exc_unexpected_error;
669
670 END import_user;
671
672 procedure update_upload_data (
673 P_TRANSACTION_NO IN JTF_RS_UPLOAD_DATA.TRANSACTION_NO%TYPE,
674 P_RECORD_NO IN JTF_RS_UPLOAD_DATA.RECORD_NO%TYPE,
675 P_PROCESS_STATUS IN JTF_RS_UPLOAD_DATA.PROCESS_STATUS%TYPE,
676 P_ERROR_TEXT IN JTF_RS_UPLOAD_DATA.ERROR_TEXT%TYPE
677 ) IS
678
679 pragma autonomous_transaction;
680 begin
681
682 update jtf_rs_upload_data
683 set error_text = p_error_text,
684 process_status = p_process_status
685 where transaction_no = p_transaction_no
686 and record_no = p_record_no;
687
688 commit;
689 end;
690
691 END jtf_rs_import_user_pub;