[Home] [Help]
PACKAGE BODY: APPS.IRC_PENDING_DATA_API
Source
1 Package Body IRC_PENDING_DATA_API as
2 /* $Header: iripdapi.pkb 120.15 2008/01/21 14:58:20 gaukumar noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := 'IRC_PENDING_DATA_API.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |-----------------------< CREATE_PENDING_DATA >-----------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure CREATE_PENDING_DATA
13 (p_validate in boolean default false
14 ,p_email_address in varchar2
15 ,p_last_name in varchar2
16 ,p_vacancy_id in number default null
17 ,p_first_name in varchar2 default null
18 ,p_user_password in varchar2 default null
19 ,p_resume_file_name in varchar2 default null
20 ,p_resume_description in varchar2 default null
21 ,p_resume_mime_type in varchar2 default null
22 ,p_source_type in varchar2 default null
23 ,p_job_post_source_name in varchar2 default null
24 ,p_posting_content_id in number default null
25 ,p_person_id in number default null
26 ,p_processed in varchar2 default null
27 ,p_sex in varchar2 default null
28 ,p_date_of_birth in date default null
29 ,p_per_information_category in varchar2 default null
30 ,p_per_information1 in varchar2 default null
31 ,p_per_information2 in varchar2 default null
32 ,p_per_information3 in varchar2 default null
33 ,p_per_information4 in varchar2 default null
34 ,p_per_information5 in varchar2 default null
35 ,p_per_information6 in varchar2 default null
36 ,p_per_information7 in varchar2 default null
37 ,p_per_information8 in varchar2 default null
38 ,p_per_information9 in varchar2 default null
39 ,p_per_information10 in varchar2 default null
40 ,p_per_information11 in varchar2 default null
41 ,p_per_information12 in varchar2 default null
42 ,p_per_information13 in varchar2 default null
43 ,p_per_information14 in varchar2 default null
44 ,p_per_information15 in varchar2 default null
45 ,p_per_information16 in varchar2 default null
46 ,p_per_information17 in varchar2 default null
47 ,p_per_information18 in varchar2 default null
48 ,p_per_information19 in varchar2 default null
49 ,p_per_information20 in varchar2 default null
50 ,p_per_information21 in varchar2 default null
51 ,p_per_information22 in varchar2 default null
52 ,p_per_information23 in varchar2 default null
53 ,p_per_information24 in varchar2 default null
54 ,p_per_information25 in varchar2 default null
55 ,p_per_information26 in varchar2 default null
56 ,p_per_information27 in varchar2 default null
57 ,p_per_information28 in varchar2 default null
58 ,p_per_information29 in varchar2 default null
59 ,p_per_information30 in varchar2 default null
60 ,p_error_message in varchar2 default null
61 ,p_creation_date in date
62 ,p_last_update_date in date
63 ,p_allow_access in varchar2 default null
64 ,p_user_guid in raw default null
65 ,p_visitor_resp_key in varchar2 default null
66 ,p_visitor_resp_appl_id in number default null
67 ,p_security_group_key in varchar2 default null
68 ,p_pending_data_id out nocopy number
69 ) is
70 --
71 -- Declare cursors and local variables
72 --
73 l_pending_data_id number(15);
74 l_num number;
75 l_user_id number;
76 l_date_of_birth date;
77 l_creation_date date;
78 l_last_update_date date;
79 l_enc_password fnd_user.encrypted_user_password%type;
80 l_user_guid fnd_user.user_guid%type;
81 l_proc varchar2(72) := g_package||'CREATE_PENDING_DATA';
82 --
83 -- cursor to get the encryptd password for new user.
84 cursor csr_fnd_user_details is
85 select fnd_user_pkg.GetReEncryptedPassword(user_name,'LOADER') as encrypted_user_password,
86 user_guid
87 from fnd_user
88 where user_name = upper(p_email_address);
89 --
90 begin
91 hr_utility.set_location('Entering:'|| l_proc, 10);
92 --
93 -- Issue a savepoint
94 --
95 savepoint CREATE_PENDING_DATA;
96 --
97 l_date_of_birth := trunc(p_date_of_birth);
98 l_last_update_date := trunc(p_last_update_date);
99 --
100 --Do not truncate the creation date.
101 --
102 l_creation_date := p_creation_date;
103 --
104 -- Call Before Process User Hook
105 --
106 begin
107 IRC_PENDING_DATA_BK1.CREATE_PENDING_DATA_b
108 (p_email_address => p_email_address
109 ,p_last_name => p_last_name
110 ,p_vacancy_id => p_vacancy_id
111 ,p_first_name => p_first_name
112 ,p_user_password => p_user_password
113 ,p_resume_file_name => p_resume_file_name
114 ,p_resume_description => p_resume_description
115 ,p_resume_mime_type => p_resume_mime_type
116 ,p_source_type => p_source_type
117 ,p_job_post_source_name => p_job_post_source_name
118 ,p_posting_content_id => p_posting_content_id
119 ,p_person_id => p_person_id
120 ,p_processed => p_processed
121 ,p_sex => p_sex
122 ,p_date_of_birth => l_date_of_birth
123 ,p_per_information_category => p_per_information_category
124 ,p_per_information1 => p_per_information1
125 ,p_per_information2 => p_per_information2
126 ,p_per_information3 => p_per_information3
127 ,p_per_information4 => p_per_information4
128 ,p_per_information5 => p_per_information5
129 ,p_per_information6 => p_per_information6
130 ,p_per_information7 => p_per_information7
131 ,p_per_information8 => p_per_information8
132 ,p_per_information9 => p_per_information9
133 ,p_per_information10 => p_per_information10
134 ,p_per_information11 => p_per_information11
135 ,p_per_information12 => p_per_information12
136 ,p_per_information13 => p_per_information13
137 ,p_per_information14 => p_per_information14
138 ,p_per_information15 => p_per_information15
139 ,p_per_information16 => p_per_information16
140 ,p_per_information17 => p_per_information17
141 ,p_per_information18 => p_per_information18
142 ,p_per_information19 => p_per_information19
143 ,p_per_information20 => p_per_information20
144 ,p_per_information21 => p_per_information21
145 ,p_per_information22 => p_per_information22
146 ,p_per_information23 => p_per_information23
147 ,p_per_information24 => p_per_information24
148 ,p_per_information25 => p_per_information25
149 ,p_per_information26 => p_per_information26
150 ,p_per_information27 => p_per_information27
151 ,p_per_information28 => p_per_information28
152 ,p_per_information29 => p_per_information29
153 ,p_per_information30 => p_per_information30
154 ,p_error_message => p_error_message
155 ,p_creation_date => l_creation_date
156 ,p_last_update_date => l_last_update_date
157 ,p_allow_access => p_allow_access
158 ,p_visitor_resp_key => p_visitor_resp_key
159 ,p_visitor_resp_appl_id => p_visitor_resp_appl_id
160 ,p_security_group_key => p_security_group_key
161 );
162 exception
163 when hr_api.cannot_find_prog_unit then
164 hr_api.cannot_find_prog_unit_error
165 (p_module_name => 'CREATE_PENDING_DATA'
166 ,p_hook_type => 'BP'
167 );
168 end;
169 --
170 -- Process Logic
171 --
172 if p_user_password is not null then
173 l_num := irc_party_api.testusername(p_user_name=>p_email_address);
174 hr_utility.set_location(l_proc,20);
175 if l_num = 0 then
176 --
177 --Create new fnd_user
178 --
179 l_user_id := fnd_user_pkg.createUserId
180 (x_user_name => p_email_address
181 ,x_owner => 'CUST'
182 ,x_unencrypted_password => p_user_password
183 ,x_email_address => p_email_address
184 ,x_password_date => sysdate
185 );
186 hr_utility.set_location(l_proc,30);
187 else
188 fnd_message.set_name('PER','IRC_EMAIL_ALREADY_REGISTERED');
189 fnd_message.set_token('USER_NAME',p_email_address);
190 fnd_message.raise_error;
191 end if;
192 end if;
193 -- get the user details
194 open csr_fnd_user_details;
195 fetch csr_fnd_user_details into l_enc_password, l_user_guid;
196 close csr_fnd_user_details;
197 --
198 irc_ipd_ins.ins
199 (p_email_address => p_email_address
200 ,p_last_name => p_last_name
201 ,p_vacancy_id => p_vacancy_id
202 ,p_first_name => p_first_name
203 ,p_user_password => l_enc_password
204 ,p_resume_file_name => p_resume_file_name
205 ,p_resume_description => p_resume_description
206 ,p_resume_mime_type => p_resume_mime_type
207 ,p_source_type => p_source_type
208 ,p_job_post_source_name => p_job_post_source_name
209 ,p_posting_content_id => p_posting_content_id
210 ,p_person_id => p_person_id
211 ,p_processed => p_processed
212 ,p_sex => p_sex
213 ,p_date_of_birth => l_date_of_birth
214 ,p_per_information_category => p_per_information_category
215 ,p_per_information1 => p_per_information1
216 ,p_per_information2 => p_per_information2
217 ,p_per_information3 => p_per_information3
218 ,p_per_information4 => p_per_information4
219 ,p_per_information5 => p_per_information5
220 ,p_per_information6 => p_per_information6
221 ,p_per_information7 => p_per_information7
222 ,p_per_information8 => p_per_information8
223 ,p_per_information9 => p_per_information9
224 ,p_per_information10 => p_per_information10
225 ,p_per_information11 => p_per_information11
226 ,p_per_information12 => p_per_information12
227 ,p_per_information13 => p_per_information13
228 ,p_per_information14 => p_per_information14
229 ,p_per_information15 => p_per_information15
230 ,p_per_information16 => p_per_information16
231 ,p_per_information17 => p_per_information17
232 ,p_per_information18 => p_per_information18
233 ,p_per_information19 => p_per_information19
234 ,p_per_information20 => p_per_information20
235 ,p_per_information21 => p_per_information21
236 ,p_per_information22 => p_per_information22
237 ,p_per_information23 => p_per_information23
238 ,p_per_information24 => p_per_information24
239 ,p_per_information25 => p_per_information25
240 ,p_per_information26 => p_per_information26
241 ,p_per_information27 => p_per_information27
242 ,p_per_information28 => p_per_information28
243 ,p_per_information29 => p_per_information29
244 ,p_per_information30 => p_per_information30
245 ,p_error_message => p_error_message
246 ,p_creation_date => l_creation_date
247 ,p_last_update_date => l_last_update_date
248 ,p_allow_access => p_allow_access
249 ,p_pending_data_id => l_pending_data_id
250 ,p_user_guid => l_user_guid
251 ,p_visitor_resp_key => p_visitor_resp_key
252 ,p_visitor_resp_appl_id => p_visitor_resp_appl_id
253 ,p_security_group_key => p_security_group_key
254 );
255 --
256 -- Call After Process User Hook
257 --
258 begin
259 IRC_PENDING_DATA_BK1.CREATE_PENDING_DATA_a
260 (p_email_address => p_email_address
261 ,p_last_name => p_last_name
262 ,p_vacancy_id => p_vacancy_id
263 ,p_first_name => p_first_name
264 ,p_user_password => p_user_password
265 ,p_resume_file_name => p_resume_file_name
266 ,p_resume_description => p_resume_description
267 ,p_resume_mime_type => p_resume_mime_type
268 ,p_source_type => p_source_type
269 ,p_job_post_source_name => p_job_post_source_name
270 ,p_posting_content_id => p_posting_content_id
271 ,p_person_id => p_person_id
272 ,p_processed => p_processed
273 ,p_sex => p_sex
274 ,p_date_of_birth => l_date_of_birth
275 ,p_per_information_category => p_per_information_category
276 ,p_per_information1 => p_per_information1
277 ,p_per_information2 => p_per_information2
278 ,p_per_information3 => p_per_information3
279 ,p_per_information4 => p_per_information4
280 ,p_per_information5 => p_per_information5
281 ,p_per_information6 => p_per_information6
282 ,p_per_information7 => p_per_information7
283 ,p_per_information8 => p_per_information8
284 ,p_per_information9 => p_per_information9
285 ,p_per_information10 => p_per_information10
286 ,p_per_information11 => p_per_information11
287 ,p_per_information12 => p_per_information12
288 ,p_per_information13 => p_per_information13
289 ,p_per_information14 => p_per_information14
290 ,p_per_information15 => p_per_information15
291 ,p_per_information16 => p_per_information16
292 ,p_per_information17 => p_per_information17
293 ,p_per_information18 => p_per_information18
294 ,p_per_information19 => p_per_information19
295 ,p_per_information20 => p_per_information20
296 ,p_per_information21 => p_per_information21
297 ,p_per_information22 => p_per_information22
298 ,p_per_information23 => p_per_information23
299 ,p_per_information24 => p_per_information24
300 ,p_per_information25 => p_per_information25
301 ,p_per_information26 => p_per_information26
302 ,p_per_information27 => p_per_information27
303 ,p_per_information28 => p_per_information28
304 ,p_per_information29 => p_per_information29
305 ,p_per_information30 => p_per_information30
306 ,p_error_message => p_error_message
307 ,p_creation_date => l_creation_date
308 ,p_last_update_date => l_last_update_date
309 ,p_allow_access => p_allow_access
310 ,p_visitor_resp_key => p_visitor_resp_key
311 ,p_visitor_resp_appl_id => p_visitor_resp_appl_id
312 ,p_security_group_key => p_security_group_key
313 );
314 exception
315 when hr_api.cannot_find_prog_unit then
316 hr_api.cannot_find_prog_unit_error
317 (p_module_name => 'CREATE_PENDING_DATA'
318 ,p_hook_type => 'AP'
319 );
320 end;
321 --
322 --
323 -- When in validation only mode raise the Validate_Enabled exception
324 --
325 if p_validate then
326 raise hr_api.validate_enabled;
327 end if;
328 --
329 -- Set all OUT parameters with out values
330 --
331 p_pending_data_id := l_pending_data_id;
332 --
333 hr_utility.set_location(' Leaving:'||l_proc, 70);
334 exception
335 when hr_api.validate_enabled then
336 --
337 -- As the Validate_Enabled exception has been raised
338 -- we must rollback to the savepoint
339 --
340 rollback to CREATE_PENDING_DATA;
341 --
342 -- Reset IN OUT parameters and set OUT parameters
343 -- (Any key or derived arguments must be set to null
344 -- when validation only mode is being used.)
345 --
346 p_pending_data_id := null;
347 hr_utility.set_location(' Leaving:'||l_proc, 80);
348 when others then
349 --
350 -- A validation or unexpected error has occured
351 --
352 rollback to CREATE_PENDING_DATA;
353 --
354 -- Reset IN OUT parameters and set all
355 -- OUT parameters, including warnings, to null
356 --
357 p_pending_data_id := null;
358 hr_utility.set_location(' Leaving:'||l_proc, 90);
359 raise;
360 end CREATE_PENDING_DATA;
361 --
362 -- ----------------------------------------------------------------------------
363 -- |-------------------------< UPDATE_PENDING_DATA >-------------------------|
364 -- ----------------------------------------------------------------------------
365 --
366 procedure UPDATE_PENDING_DATA
367 (p_validate in boolean default false
368 ,p_pending_data_id in number
369 ,p_email_address in varchar2 default hr_api.g_varchar2
370 ,p_last_name in varchar2 default hr_api.g_varchar2
371 ,p_vacancy_id in number default hr_api.g_number
372 ,p_first_name in varchar2 default hr_api.g_varchar2
373 ,p_user_password in varchar2 default hr_api.g_varchar2
374 ,p_resume_file_name in varchar2 default hr_api.g_varchar2
375 ,p_resume_description in varchar2 default hr_api.g_varchar2
376 ,p_resume_mime_type in varchar2 default hr_api.g_varchar2
377 ,p_source_type in varchar2 default hr_api.g_varchar2
378 ,p_job_post_source_name in varchar2 default hr_api.g_varchar2
379 ,p_posting_content_id in number default hr_api.g_number
380 ,p_person_id in number default hr_api.g_number
381 ,p_processed in varchar2 default hr_api.g_varchar2
382 ,p_sex in varchar2 default hr_api.g_varchar2
383 ,p_date_of_birth in date default hr_api.g_date
384 ,p_per_information_category in varchar2 default hr_api.g_varchar2
385 ,p_per_information1 in varchar2 default hr_api.g_varchar2
386 ,p_per_information2 in varchar2 default hr_api.g_varchar2
387 ,p_per_information3 in varchar2 default hr_api.g_varchar2
388 ,p_per_information4 in varchar2 default hr_api.g_varchar2
389 ,p_per_information5 in varchar2 default hr_api.g_varchar2
390 ,p_per_information6 in varchar2 default hr_api.g_varchar2
391 ,p_per_information7 in varchar2 default hr_api.g_varchar2
392 ,p_per_information8 in varchar2 default hr_api.g_varchar2
393 ,p_per_information9 in varchar2 default hr_api.g_varchar2
394 ,p_per_information10 in varchar2 default hr_api.g_varchar2
395 ,p_per_information11 in varchar2 default hr_api.g_varchar2
396 ,p_per_information12 in varchar2 default hr_api.g_varchar2
397 ,p_per_information13 in varchar2 default hr_api.g_varchar2
398 ,p_per_information14 in varchar2 default hr_api.g_varchar2
399 ,p_per_information15 in varchar2 default hr_api.g_varchar2
400 ,p_per_information16 in varchar2 default hr_api.g_varchar2
401 ,p_per_information17 in varchar2 default hr_api.g_varchar2
402 ,p_per_information18 in varchar2 default hr_api.g_varchar2
403 ,p_per_information19 in varchar2 default hr_api.g_varchar2
404 ,p_per_information20 in varchar2 default hr_api.g_varchar2
405 ,p_per_information21 in varchar2 default hr_api.g_varchar2
406 ,p_per_information22 in varchar2 default hr_api.g_varchar2
407 ,p_per_information23 in varchar2 default hr_api.g_varchar2
408 ,p_per_information24 in varchar2 default hr_api.g_varchar2
409 ,p_per_information25 in varchar2 default hr_api.g_varchar2
410 ,p_per_information26 in varchar2 default hr_api.g_varchar2
411 ,p_per_information27 in varchar2 default hr_api.g_varchar2
412 ,p_per_information28 in varchar2 default hr_api.g_varchar2
413 ,p_per_information29 in varchar2 default hr_api.g_varchar2
414 ,p_per_information30 in varchar2 default hr_api.g_varchar2
415 ,p_error_message in varchar2 default hr_api.g_varchar2
416 ,p_creation_date in date default hr_api.g_date
417 ,p_last_update_date in date default hr_api.g_date
418 ,p_allow_access in varchar2 default hr_api.g_varchar2
419 ,p_user_guid in raw default null
420 ,p_visitor_resp_key in varchar2 default hr_api.g_varchar2
421 ,p_visitor_resp_appl_id in number default hr_api.g_number
422 ,p_security_group_key in varchar2 default hr_api.g_varchar2
423 ) is
424 --
425 -- Declare cursors and local variables
426 --
427 l_date_of_birth date;
428 l_creation_date date;
429 l_last_update_date date;
430 l_proc varchar2(72) := g_package||'UPDATE_PENDING_DATA';
431 begin
432 hr_utility.set_location('Entering:'|| l_proc, 10);
433 --
434 -- Issue a savepoint
435 --
436 savepoint UPDATE_PENDING_DATA;
437 --
438 --
439 l_date_of_birth := trunc(p_date_of_birth);
440 l_last_update_date := trunc(p_last_update_date);
441 --
442 --Do not truncate the creation date.
443 --
444 l_creation_date := p_creation_date;
445 --
446 --
447 -- Call Before Process User Hook
448 --
449 begin
450 IRC_PENDING_DATA_BK2.UPDATE_PENDING_DATA_b
451 (p_email_address => p_email_address
452 ,p_last_name => p_last_name
453 ,p_vacancy_id => p_vacancy_id
454 ,p_first_name => p_first_name
455 ,p_user_password => p_user_password
456 ,p_resume_file_name => p_resume_file_name
457 ,p_resume_description => p_resume_description
458 ,p_resume_mime_type => p_resume_mime_type
459 ,p_source_type => p_source_type
460 ,p_job_post_source_name => p_job_post_source_name
461 ,p_posting_content_id => p_posting_content_id
462 ,p_person_id => p_person_id
463 ,p_processed => p_processed
464 ,p_sex => p_sex
465 ,p_date_of_birth => l_date_of_birth
466 ,p_per_information_category => p_per_information_category
467 ,p_per_information1 => p_per_information1
468 ,p_per_information2 => p_per_information2
469 ,p_per_information3 => p_per_information3
470 ,p_per_information4 => p_per_information4
471 ,p_per_information5 => p_per_information5
472 ,p_per_information6 => p_per_information6
473 ,p_per_information7 => p_per_information7
474 ,p_per_information8 => p_per_information8
475 ,p_per_information9 => p_per_information9
476 ,p_per_information10 => p_per_information10
477 ,p_per_information11 => p_per_information11
478 ,p_per_information12 => p_per_information12
479 ,p_per_information13 => p_per_information13
480 ,p_per_information14 => p_per_information14
481 ,p_per_information15 => p_per_information15
482 ,p_per_information16 => p_per_information16
483 ,p_per_information17 => p_per_information17
484 ,p_per_information18 => p_per_information18
485 ,p_per_information19 => p_per_information19
486 ,p_per_information20 => p_per_information20
487 ,p_per_information21 => p_per_information21
488 ,p_per_information22 => p_per_information22
489 ,p_per_information23 => p_per_information23
490 ,p_per_information24 => p_per_information24
491 ,p_per_information25 => p_per_information25
492 ,p_per_information26 => p_per_information26
493 ,p_per_information27 => p_per_information27
494 ,p_per_information28 => p_per_information28
495 ,p_per_information29 => p_per_information29
496 ,p_per_information30 => p_per_information30
497 ,p_error_message => p_error_message
498 ,p_creation_date => l_creation_date
499 ,p_last_update_date => l_last_update_date
500 ,p_allow_access => p_allow_access
501 ,p_visitor_resp_key => p_visitor_resp_key
502 ,p_visitor_resp_appl_id => p_visitor_resp_appl_id
503 ,p_security_group_key => p_security_group_key
504 );
505 exception
506 when hr_api.cannot_find_prog_unit then
507 hr_api.cannot_find_prog_unit_error
508 (p_module_name => 'UPDATE_PENDING_DATA'
509 ,p_hook_type => 'BP'
510 );
511 end;
512 --
513 -- Validation in addition to Row Handlers
514 --
515 --
516 -- Process Logic
517 --
518 irc_ipd_upd.upd
519 (p_pending_data_id => p_pending_data_id
520 ,p_email_address => p_email_address
521 ,p_last_name => p_last_name
522 ,p_first_name => p_first_name
523 ,p_user_password => p_user_password
524 ,p_resume_file_name => p_resume_file_name
525 ,p_resume_description => p_resume_description
526 ,p_resume_mime_type => p_resume_mime_type
527 ,p_source_type => p_source_type
528 ,p_job_post_source_name => p_job_post_source_name
529 ,p_posting_content_id => p_posting_content_id
530 ,p_person_id => p_person_id
531 ,p_processed => p_processed
532 ,p_sex => p_sex
533 ,p_date_of_birth => l_date_of_birth
534 ,p_per_information_category => p_per_information_category
535 ,p_per_information1 => p_per_information1
536 ,p_per_information2 => p_per_information2
537 ,p_per_information3 => p_per_information3
538 ,p_per_information4 => p_per_information4
539 ,p_per_information5 => p_per_information5
540 ,p_per_information6 => p_per_information6
541 ,p_per_information7 => p_per_information7
542 ,p_per_information8 => p_per_information8
543 ,p_per_information9 => p_per_information9
544 ,p_per_information10 => p_per_information10
545 ,p_per_information11 => p_per_information11
546 ,p_per_information12 => p_per_information12
547 ,p_per_information13 => p_per_information13
548 ,p_per_information14 => p_per_information14
549 ,p_per_information15 => p_per_information15
550 ,p_per_information16 => p_per_information16
551 ,p_per_information17 => p_per_information17
552 ,p_per_information18 => p_per_information18
553 ,p_per_information19 => p_per_information19
554 ,p_per_information20 => p_per_information20
555 ,p_per_information21 => p_per_information21
556 ,p_per_information22 => p_per_information22
557 ,p_per_information23 => p_per_information23
558 ,p_per_information24 => p_per_information24
559 ,p_per_information25 => p_per_information25
560 ,p_per_information26 => p_per_information26
561 ,p_per_information27 => p_per_information27
562 ,p_per_information28 => p_per_information28
563 ,p_per_information29 => p_per_information29
564 ,p_per_information30 => p_per_information30
565 ,p_error_message => p_error_message
566 ,p_last_update_date => l_last_update_date
567 ,p_allow_access => p_allow_access
568 ,p_user_guid => p_user_guid
569 ,p_visitor_resp_key => p_visitor_resp_key
570 ,p_visitor_resp_appl_id => p_visitor_resp_appl_id
571 ,p_security_group_key => p_security_group_key
572 );
573 -- Call After Process User Hook
574 --
575 begin
576 IRC_PENDING_DATA_BK2.UPDATE_PENDING_DATA_a
577 (p_email_address => p_email_address
578 ,p_last_name => p_last_name
579 ,p_vacancy_id => p_vacancy_id
580 ,p_first_name => p_first_name
581 ,p_user_password => p_user_password
582 ,p_resume_file_name => p_resume_file_name
583 ,p_resume_description => p_resume_description
584 ,p_resume_mime_type => p_resume_mime_type
585 ,p_source_type => p_source_type
586 ,p_job_post_source_name => p_job_post_source_name
587 ,p_posting_content_id => p_posting_content_id
588 ,p_person_id => p_person_id
589 ,p_processed => p_processed
590 ,p_sex => p_sex
591 ,p_date_of_birth => l_date_of_birth
592 ,p_per_information_category => p_per_information_category
593 ,p_per_information1 => p_per_information1
594 ,p_per_information2 => p_per_information2
595 ,p_per_information3 => p_per_information3
596 ,p_per_information4 => p_per_information4
597 ,p_per_information5 => p_per_information5
598 ,p_per_information6 => p_per_information6
599 ,p_per_information7 => p_per_information7
600 ,p_per_information8 => p_per_information8
601 ,p_per_information9 => p_per_information9
602 ,p_per_information10 => p_per_information10
603 ,p_per_information11 => p_per_information11
604 ,p_per_information12 => p_per_information12
605 ,p_per_information13 => p_per_information13
606 ,p_per_information14 => p_per_information14
607 ,p_per_information15 => p_per_information15
608 ,p_per_information16 => p_per_information16
609 ,p_per_information17 => p_per_information17
610 ,p_per_information18 => p_per_information18
611 ,p_per_information19 => p_per_information19
612 ,p_per_information20 => p_per_information20
613 ,p_per_information21 => p_per_information21
614 ,p_per_information22 => p_per_information22
615 ,p_per_information23 => p_per_information23
616 ,p_per_information24 => p_per_information24
617 ,p_per_information25 => p_per_information25
618 ,p_per_information26 => p_per_information26
619 ,p_per_information27 => p_per_information27
620 ,p_per_information28 => p_per_information28
621 ,p_per_information29 => p_per_information29
622 ,p_per_information30 => p_per_information30
623 ,p_error_message => p_error_message
624 ,p_creation_date => l_creation_date
625 ,p_last_update_date => l_last_update_date
626 ,p_allow_access => p_allow_access
627 ,p_visitor_resp_key => p_visitor_resp_key
628 ,p_visitor_resp_appl_id => p_visitor_resp_appl_id
629 ,p_security_group_key => p_security_group_key
630 );
631 exception
632 when hr_api.cannot_find_prog_unit then
633 hr_api.cannot_find_prog_unit_error
634 (p_module_name => 'UPDATE_PENDING_DATA'
635 ,p_hook_type => 'AP'
636 );
637 end;
638 --
639 --
640 -- When in validation only mode raise the Validate_Enabled exception
641 --
642 if p_validate then
643 raise hr_api.validate_enabled;
644 end if;
645 --
646 hr_utility.set_location(' Leaving:'||l_proc, 70);
647 exception
648 when hr_api.validate_enabled then
649 --
650 -- As the Validate_Enabled exception has been raised
651 -- we must rollback to the savepoint
652 --
653 rollback to UPDATE_PENDING_DATA;
654 --
655 hr_utility.set_location(' Leaving:'||l_proc, 80);
656 when others then
657 --
658 -- A validation or unexpected error has occured
659 --
660 rollback to UPDATE_PENDING_DATA;
661 --
662 -- Reset IN OUT parameters and set all
663 -- OUT parameters, including warnings, to null
664 --
665 hr_utility.set_location(' Leaving:'||l_proc, 90);
666 raise;
667 end UPDATE_PENDING_DATA;
668 --
669 -- ----------------------------------------------------------------------------
670 -- |-------------------------< DELETE_PENDING_DATA >-------------------------|
671 -- ----------------------------------------------------------------------------
672 --
673 procedure DELETE_PENDING_DATA
674 (p_validate in boolean default false
675 ,p_pending_data_id in number
676 ) is
677 --
678 -- Declare cursors and local variables
679 --
680 l_proc varchar2(72) := g_package||'DELETE_PENDING_DATA';
681 begin
682 hr_utility.set_location('Entering:'|| l_proc, 10);
683 --
684 -- Issue a savepoint
685 --
686 savepoint DELETE_PENDING_DATA;
687 --
688 -- Call Before Process User Hook
689 --
690 begin
691 IRC_PENDING_DATA_BK3.DELETE_PENDING_DATA_b
692 (p_pending_data_id => p_pending_data_id
693 );
694 exception
695 when hr_api.cannot_find_prog_unit then
696 hr_api.cannot_find_prog_unit_error
697 (p_module_name => 'DELETE_PENDING_DATA'
698 ,p_hook_type => 'BP'
699 );
700 end;
701 --
702 irc_ipd_del.del
703 (p_pending_data_id => p_pending_data_id
704 );
705 --
706 -- Call After Process User Hook
707 --
708 begin
709 IRC_PENDING_DATA_BK3.DELETE_PENDING_DATA_a
710 (p_pending_data_id => p_pending_data_id
711 );
712 exception
713 when hr_api.cannot_find_prog_unit then
714 hr_api.cannot_find_prog_unit_error
715 (p_module_name => 'DELETE_PENDING_DATA'
716 ,p_hook_type => 'AP'
717 );
718 end;
719 --
720 --
721 --
722 -- When in validation only mode raise the Validate_Enabled exception
723 --
724 if p_validate then
725 raise hr_api.validate_enabled;
726 end if;
727 --
728 hr_utility.set_location(' Leaving:'||l_proc, 70);
729 exception
730 when hr_api.validate_enabled then
731 --
732 -- As the Validate_Enabled exception has been raised
733 -- we must rollback to the savepoint
734 --
735 rollback to DELETE_PENDING_DATA;
736 --
737 hr_utility.set_location(' Leaving:'||l_proc, 80);
738 when others then
739 --
740 -- A validation or unexpected error has occured
741 --
742 rollback to DELETE_PENDING_DATA;
743 --
744 hr_utility.set_location(' Leaving:'||l_proc, 90);
745 raise;
746 end DELETE_PENDING_DATA;
747 --
748 -- ----------------------------------------------------------------------------
749 -- |-------------------------< process_applications >-------------------------|
750 -- ----------------------------------------------------------------------------
751 --
752 procedure process_applications
753 (p_server_name in fnd_nodes.node_name%type
754 )
755 is
756 --
757 -- Declare cursors and local variables
758 --
759 l_proc varchar2(72) := g_package||'process_applications';
760
761 l_person_id per_all_people_f.person_id%type;
762 l_resp_key fnd_responsibility.responsibility_key%type;
763 l_irc_resp_id fnd_responsibility.responsibility_id%type;
764 l_irc_resp_appl_id fnd_responsibility.application_id%type;
765 l_reg_bg_id number;
766
767 l_resume_file_name irc_pending_data.resume_file_name%type;
768 l_resume_description irc_pending_data.resume_description%type;
769 l_resume_mime_type irc_pending_data.resume_mime_type%type;
770 l_resume irc_pending_data.resume%type;
771 l_document_id irc_documents.document_id%type;
772 l_new_doc_id irc_documents.document_id%type;
773 l_doc_person_id irc_documents.person_id%type;
774 l_doc_party_id irc_documents.party_id%type;
775 l_end_date date := null;
776 l_assignment_id irc_documents.assignment_id%type;
777 l_doc_ovn number;
778 l_source_type irc_pending_data.source_type%type;
779 l_job_post_source_name irc_pending_data.job_post_source_name%type;
780
781 l_applicant_person_id per_all_people_f.person_id%type;
782 l_applicant_assg_id per_all_assignments_f.assignment_id%type;
783 l_appl_ovn per_all_people_f.object_version_number%type;
784 l_emp_number per_all_people_f.employee_number%type;
785
786 l_effective_start_date per_all_people_f.effective_start_date%type;
787 l_effective_end_date per_all_people_f.effective_end_date%type;
788 l_full_name per_all_people_f.full_name%type;
789 l_comment_id per_all_people_f.comment_id%type;
790 l_name_combination_warning boolean;
791 l_assign_payroll_warning boolean;
792 l_orig_hire_warning boolean;
793 l_per_ovn number;
794 l_asg_ovn number;
795 l_applicant_number per_all_people_f.applicant_number%type;
796 l_err_msg varchar2(4000);
797 l_msg varchar2(4000);
798 l_err_num number;
799 l_allow_access irc_pending_data.allow_access%type;
800 l_password irc_pending_data.user_password%type;
801 l_num number;
802 l_person_type varchar2(30);
803 l_irc_profile varchar2(30);
804 l_new_user_id number;
805 --
806 cursor csr_get_user_id (p_user_name varchar2) is
807 select user_id
808 from fnd_user
809 where user_name= upper(p_user_name);
810 l_user_id fnd_user.user_id%type;
811 --
812 cursor csr_get_employee_id (p_user_name varchar2) is
813 select employee_id
814 from fnd_user
815 where user_name= upper(p_user_name);
816 --
817 cursor csr_get_resp_id(p_resp_key varchar2) is
818 select responsibility_id,application_id
819 from fnd_responsibility
820 where responsibility_key = p_resp_key;
821 l_resp_id fnd_responsibility.responsibility_id%type;
822 l_appl_id fnd_responsibility.application_id%type;
823 --
824 cursor csr_get_sg_id(p_sec_group_key varchar2) is
825 select security_group_id
826 from fnd_security_groups
827 where security_group_key = p_sec_group_key;
828 l_sg_id fnd_security_groups.security_group_id%type;
829 --
830 cursor csr_get_server_id is
831 select node_id
832 from fnd_nodes
833 where lower(node_name)=lower(p_server_name);
834 l_server_id number;
835 --
836 cursor csr_get_resume(p_pending_data_id number) is
837 select ipd.resume_file_name
838 ,ipd.resume_description
839 ,ipd.resume_mime_type
840 ,ipd.resume
841 ,ido.document_id
842 ,ido.object_version_number
843 ,ido.person_id
844 ,ido.party_id
845 ,ido.assignment_id
846 from irc_pending_data ipd, irc_documents ido
847 where ipd.person_id = ido.person_id(+)
848 and ipd.resume_file_name = ido.file_name(+)
849 and ido.type(+) = 'RESUME'
850 and ido.end_date(+) is null
851 and ipd.resume_file_name is not null
852 and ipd.pending_data_id = p_pending_data_id;
853 --
854 cursor csr_get_person_id_in_vac_bg(p_person_id number, p_vacancy_id number) is
855 select ppf.person_id
856 ,ppf.object_version_number
857 ,ppf.employee_number
858 from per_all_people_f ppf
859 where trunc(sysdate) between
860 ppf.effective_start_date and ppf.effective_end_date
861 and ppf.party_id in (select party_id from per_all_people_f
862 where person_id=p_person_id
863 and trunc(sysdate) between
864 effective_start_date and effective_end_date)
865 and ppf.business_group_id in
866 (select business_group_id from per_all_vacancies
867 where vacancy_id=p_vacancy_id);
868 --
869 cursor csr_get_data is
870 select ipd.pending_data_id
871 ,ipd.email_address
872 ,ipd.vacancy_id
873 ,ipd.last_name
874 ,ipd.first_name
875 ,ipd.user_password
876 ,ipd.posting_content_id
877 ,ipd.sex
878 ,ipd.date_of_birth
879 ,ipd.per_information_category
880 ,ipd.per_information1
881 ,ipd.per_information2
882 ,ipd.per_information3
883 ,ipd.per_information4
884 ,ipd.per_information5
885 ,ipd.per_information6
886 ,ipd.per_information7
887 ,ipd.per_information8
888 ,ipd.per_information9
889 ,ipd.per_information10
890 ,ipd.per_information11
891 ,ipd.per_information12
892 ,ipd.per_information13
893 ,ipd.per_information14
894 ,ipd.per_information15
895 ,ipd.per_information16
896 ,ipd.per_information17
897 ,ipd.per_information18
898 ,ipd.per_information19
899 ,ipd.per_information20
900 ,ipd.per_information21
901 ,ipd.per_information22
902 ,ipd.per_information23
903 ,ipd.per_information24
904 ,ipd.per_information25
905 ,ipd.per_information26
906 ,ipd.per_information27
907 ,ipd.per_information28
908 ,ipd.per_information29
909 ,ipd.per_information30
910 ,ipd.creation_date
911 ,usr.user_id
912 ,usr.employee_id
913 ,ipd.allow_access
914 ,ipd.user_guid
915 ,ipd.visitor_resp_key
916 ,ipd.visitor_resp_appl_id
917 ,ipd.security_group_key
918 from irc_pending_data ipd, fnd_user usr
919 where upper(ipd.email_address) = usr.user_name(+)
920 and ipd.processed is null
921 order by ipd.creation_date asc;
922 --
923 --
924 -- Query built similar to JobsAppliedForVO query
925 --
926 cursor csr_job_applied_for(p_person_id in number, p_vacancy_id in number) is
927 select 1
928 from per_all_assignments_f asg,
929 per_assignment_status_types_v ast,
930 per_all_people_f ppf,
931 irc_assignment_statuses ias,
932 per_all_people_f linkppf,
933 per_assignment_status_types_v ast1
934 where asg.vacancy_id = p_vacancy_id
935 and asg.effective_start_date=(select max(effective_start_date)
936 from per_assignments_f asg2
937 where asg.assignment_id=asg2.assignment_id
938 and asg2.effective_start_date<=sysdate+1)
939 and asg.person_id = ppf.person_id
940 and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
941 and linkppf.person_id = p_person_id
942 and asg.assignment_id = ias.assignment_id
943 and asg.assignment_status_type_id = ast1.assignment_status_type_id
944 and (ias.assignment_status_id = (select max(inn.assignment_status_id)
945 from irc_assignment_statuses inn
946 where inn.assignment_id = asg.assignment_id)
947 or ias.assignment_status_id is null)
948 and ias.assignment_status_type_id = ast.assignment_status_type_id
949 and ppf.party_id = linkppf.party_id
950 and trunc(sysdate) between linkppf.effective_start_date and linkppf.effective_end_date;
951 --
952 -- Cursor to check if the Vacancy is an Internal vacancy
953 -- to prevent it from being applied by Employees
954 -- External Candidates won't be displayed Internal vacancies so we don't have
955 -- to check their applications
956 --
957 cursor csr_is_internal_vacancy(p_vacancy_id in number, p_application_date in date) is
958 select 1 from per_all_vacancies pav, per_recruitment_activities pra,
959 per_recruitment_activity_for prf, irc_all_recruiting_sites ias
960 where pav.vacancy_id = prf.vacancy_id AND
961 prf.recruitment_activity_id = pra.recruitment_activity_id AND
962 trunc(p_application_date) between PRA.date_start and nvl(PRA.date_end,trunc(p_application_date))
963 AND pra.recruiting_site_id = ias.recruiting_site_id and ias.internal='Y'
964 and pav.vacancy_id = p_vacancy_id;
965 --
966 begin
967 hr_utility.set_location('Entering:'|| l_proc, 10);
968 --
969 for l_data in csr_get_data loop
970 --
971 if l_data.user_id is null then
972 open csr_get_user_id(substr(FND_WEB_SEC.GET_GUEST_USERNAME_PWD()
973 ,0
974 ,instr(FND_WEB_SEC.GET_GUEST_USERNAME_PWD(),'/')-1));
975 fetch csr_get_user_id into l_user_id;
976 close csr_get_user_id;
977 else
978 l_user_id := l_data.user_id;
979 end if;
980 --
981 open csr_get_resp_id(l_data.visitor_resp_key);
982 fetch csr_get_resp_id into l_resp_id,l_appl_id;
983 close csr_get_resp_id;
984 --
985 open csr_get_sg_id(l_data.security_group_key);
986 fetch csr_get_sg_id into l_sg_id;
987 close csr_get_sg_id;
988 --
989 open csr_get_server_id;
990 fetch csr_get_server_id into l_server_id;
991 close csr_get_server_id;
992 --
993 hr_utility.set_location('Call to apps initialise'|| l_proc, 15);
994 if l_server_id is null then
995 fnd_global.apps_initialize
996 (user_id => l_user_id
997 ,resp_id => l_resp_id
998 ,resp_appl_id => l_data.visitor_resp_appl_id
999 ,security_group_id=> l_sg_id);
1000 else
1001 fnd_global.apps_initialize
1002 (user_id => l_user_id
1003 ,resp_id => l_resp_id
1004 ,resp_appl_id => l_data.visitor_resp_appl_id
1005 ,security_group_id=> l_sg_id
1006 ,server_id => l_server_id);
1007 end if;
1008 --
1009 --
1010 -- Fetch the responsibility set in IRC_REG_RESP profile
1011 --
1012 l_resp_key := fnd_profile.value_specific
1013 (name => 'IRC_REGISTRATION_RESP'
1014 ,responsibility_id => l_resp_id
1015 ,application_id => 800
1016 );
1017 --
1018 -- Get responsibility id and application id for the above profile
1019 --
1020 open csr_get_resp_id(l_resp_key);
1021 fetch csr_get_resp_id into l_irc_resp_id,l_irc_resp_appl_id;
1022 close csr_get_resp_id;
1023 --
1024 hr_utility.set_location(l_proc, 20);
1025 --
1026 -- Fetch the registration business group
1027 --
1028 l_reg_bg_id := fnd_profile.value('IRC_REGISTRATION_BG_ID');
1029
1030 --
1031 begin
1032 --
1033 l_person_id := null;
1034 --
1035 -- If user_id is NULL, create the FND user for this user
1036 --
1037 if l_data.user_id is null then
1038 irc_party_api.create_ha_processed_user(p_user_name => l_data.email_address
1039 ,p_password => l_data.user_password
1040 ,p_email => l_data.email_address
1041 ,p_start_date => trunc(l_data.creation_date)
1042 ,p_last_name => l_data.last_name
1043 ,p_first_name => l_data.first_name
1044 ,p_user_guid => l_data.user_guid
1045 ,p_reg_bg_id => l_reg_bg_id
1046 ,p_responsibility_id => l_irc_resp_id
1047 ,p_resp_appl_id => l_irc_resp_appl_id
1048 ,p_security_group_id => l_sg_id
1049 ,p_language => null
1050 ,p_allow_access => l_data.allow_access);
1051 --
1052 l_new_user_id := null;
1053 --
1054 select employee_id, user_id into l_person_id, l_new_user_id
1055 from fnd_user
1056 where user_name = upper(l_data.email_address);
1057 --
1058 irc_party_api.process_ha_resp_check(p_user_id => l_new_user_id,
1059 p_responsibility_id => l_irc_resp_id,
1060 p_resp_appl_id => l_irc_resp_appl_id,
1061 p_security_group_id => l_sg_id,
1062 p_start_date => trunc(l_data.creation_date),
1063 p_server_id=>l_server_id);
1064 --
1065 else
1066 irc_party_api.process_ha_resp_check(p_user_id => l_data.user_id,
1067 p_responsibility_id => l_irc_resp_id,
1068 p_resp_appl_id => l_irc_resp_appl_id,
1069 p_security_group_id => l_sg_id,
1070 p_start_date => trunc(l_data.creation_date),
1071 p_server_id=>l_server_id);
1072 end if;
1073
1074 l_irc_profile := irc_party_api.irec_profile_exists(p_user_name => l_data.email_address
1075 ,p_reg_bg_id => l_reg_bg_id
1076 ,p_responsibility_id => l_irc_resp_id
1077 ,p_resp_appl_id => l_irc_resp_appl_id
1078 ,p_security_group_id => l_sg_id);
1079 if l_irc_profile = 'NO_PROFILE' then
1080 irc_party_api.create_partial_user(p_user_name => l_data.email_address
1081 ,p_start_date => trunc(l_data.creation_date)
1082 ,p_last_name => l_data.last_name
1083 ,p_first_name => l_data.first_name
1084 ,p_reg_bg_id => l_reg_bg_id
1085 ,p_responsibility_id => l_irc_resp_id
1086 ,p_resp_appl_id => l_irc_resp_appl_id
1087 ,p_security_group_id => l_sg_id
1088 ,p_language => null
1089 ,p_allow_access => l_data.allow_access
1090 );
1091 end if;
1092 select employee_id into l_person_id
1093 from fnd_user
1094 where user_name = upper(l_data.email_address);
1095 --
1096 -- update pending data row to have the person_id
1097 --
1098 hr_utility.set_location(l_proc,40);
1099 update irc_pending_data
1100 set person_id = l_person_id
1101 ,last_update_date = trunc(sysdate)
1102 where pending_data_id = l_data.pending_data_id;
1103 commit;
1104 Fnd_file.put_line(FND_FILE.LOG,'Updated irc_pending_data with person_id:'||l_person_id);
1105 --
1106 -- create/update documents for users who have uploaded their resumes
1107 --
1108 hr_utility.set_location(l_proc, 45);
1109 open csr_get_resume(l_data.pending_data_id);
1110 fetch csr_get_resume into
1111 l_resume_file_name
1112 ,l_resume_description
1113 ,l_resume_mime_type
1114 ,l_resume
1115 ,l_document_id
1116 ,l_doc_ovn
1117 ,l_doc_person_id
1118 ,l_doc_party_id
1119 ,l_assignment_id;
1120 -- Fix for bug 4183508, bug 5948412
1121 -- check if above cursor fetched some value otherwise set the document
1122 -- variables to NULL
1123 if csr_get_resume%notfound then
1124 l_resume_file_name := null;
1125 l_document_id := null;
1126 l_doc_ovn := null;
1127 l_new_doc_id := null;
1128 l_assignment_id := null;
1129 l_doc_person_id := null;
1130 l_doc_party_id := null;
1131 end if;
1132 close csr_get_resume;
1133 --
1134 hr_utility.set_location(l_proc,50);
1135 --
1136 if l_resume_file_name is not null then
1137 if l_document_id is not null then
1138 hr_utility.set_location(l_proc, 55);
1139 Fnd_file.put_line(FND_FILE.LOG,'Updating resume document for the Candidate: '||l_resume_file_name||': document_id='||l_document_id);
1140 irc_document_api.update_document_track
1141 (p_effective_date => l_data.creation_date
1142 ,p_document_id => l_document_id
1143 ,p_mime_type => l_resume_mime_type
1144 ,p_type => 'RESUME'
1145 ,p_file_name => l_resume_file_name
1146 ,p_description => l_resume_description
1147 ,p_person_id => l_doc_person_id
1148 ,p_party_id => l_doc_party_id
1149 ,p_end_date => l_end_date
1150 ,p_assignment_id => l_assignment_id
1151 ,p_object_version_number => l_doc_ovn
1152 ,p_new_doc_id => l_new_doc_id
1153 );
1154 Fnd_file.put_line(FND_FILE.LOG,'Updated Resume document for the Candidate:'||l_document_id);
1155 If l_document_id <> l_new_doc_id then
1156 Fnd_file.put_line(FND_FILE.LOG,'Created Resume document for the Candidate:'||l_new_doc_id);
1157 end if;
1158 else
1159 hr_utility.set_location(l_proc, 60);
1160 Fnd_file.put_line(FND_FILE.LOG,'Creating resume document for the Candidate: '||l_resume_file_name);
1161 irc_document_api.create_document
1162 (p_effective_date => l_data.creation_date
1163 ,p_type => 'RESUME'
1164 ,p_person_id => l_person_id
1165 ,p_mime_type => l_resume_mime_type
1166 ,p_file_name => l_resume_file_name
1167 ,p_description => l_resume_description
1168 ,p_document_id => l_new_doc_id
1169 ,p_object_version_number => l_doc_ovn
1170 );
1171 Fnd_file.put_line(FND_FILE.LOG,'Created new Resume document for the Candidate:'||l_resume_file_name);
1172 end if;
1173 end if;
1174 hr_utility.set_location(l_proc, 65);
1175 if l_new_doc_id is not null then
1176 update irc_documents set binary_doc=l_resume
1177 where document_id = l_new_doc_id;
1178 irc_document_api.process_document(l_new_doc_id);
1179 end if;
1180 --
1181 hr_utility.set_location(l_proc, 70);
1182 if l_data.vacancy_id is not null then
1183 -- fix for bug 4046889
1184 -- check if this Person is an Employee and if he is applying for an
1185 -- External vacancy
1186 Fnd_file.put_line(FND_FILE.LOG,'Checking if this applicant is an employee and is applying for an internal vacancy only:');
1187 l_person_type := irc_utilities_pkg.get_emp_spt_for_person(p_person_id=>l_person_id, p_eff_date=>trunc(l_data.creation_date));
1188 if (l_person_type = 'EMP') then
1189 open csr_is_internal_vacancy(l_data.vacancy_id, trunc(l_data.creation_date));
1190 fetch csr_is_internal_vacancy into l_num;
1191 if csr_is_internal_vacancy%notfound then
1192 hr_utility.set_location(l_proc, 72);
1193 close csr_is_internal_vacancy;
1194 Fnd_file.put_line(FND_FILE.LOG,'Employee cannot apply for External Vacancy:'||l_data.vacancy_id);
1195 hr_utility.set_message(800,'IRC_412140_JOB_NOT_AVAILABLE');
1196 hr_utility.raise_error;
1197 else
1198 close csr_is_internal_vacancy;
1199 end if;
1200 end if;
1201 -- check if this person has already applied in the Vacancy BG
1202 Fnd_file.put_line(FND_FILE.LOG,'Checking if this person has already applied for a vacancy in this BG:');
1203 open csr_get_person_id_in_vac_bg(l_person_id,l_data.vacancy_id);
1204 fetch csr_get_person_id_in_vac_bg
1205 into l_applicant_person_id
1206 ,l_appl_ovn
1207 ,l_emp_number;
1208 if csr_get_person_id_in_vac_bg%notfound then
1209 select per_people_s.nextval into l_applicant_person_id from dual;
1210 -- fix for bug 4018218
1211 -- make this value as NULL because we are in a loop and if in the
1212 -- first loop we have a valid value and in second loop we have to
1213 -- clear it otherwise we send the value in first loop
1214 l_emp_number:=null;
1215 hr_utility.set_location(l_proc,75);
1216 end if;
1217
1218 /* process the vacancy application. We create a new user if needed in
1219 Vacancy BG and then update person record with EEO information
1220 after applying the processed flag is changed to 'A' so that if process
1221 run again 'A' rows won't be picked up.
1222 these API calls are present in PerAllPeopleFEOImpl.java */
1223
1224 select per_assignments_s.nextval into l_applicant_assg_id from dual;
1225
1226 hr_utility.set_location(l_proc, 80);
1227
1228 l_per_ovn := null;
1229 l_asg_ovn := null;
1230 l_applicant_number := null;
1231 --
1232 if csr_get_person_id_in_vac_bg%found then
1233 --
1234 -- Check if Job Already Applied For
1235 --
1236 open csr_job_applied_for(l_person_id, l_data.vacancy_id);
1237 fetch csr_job_applied_for into l_num;
1238 if csr_job_applied_for%found then
1239 hr_utility.set_location(l_proc, 82);
1240 close csr_job_applied_for;
1241 Fnd_file.put_line(FND_FILE.LOG,'Already applied for job:'||l_data.vacancy_id);
1242 hr_utility.set_message(800,'IRC_APL_ALREADY_APPLIED');
1243 hr_utility.raise_error;
1244 else
1245 close csr_job_applied_for;
1246 end if;
1247 end if;
1248 Fnd_file.put_line(FND_FILE.LOG,'Applying for Job Vacancy:'||l_data.vacancy_id);
1249 -- call the registered_user_application API to create the Job application
1250 irc_party_api.registered_user_application
1251 (p_effective_date => l_data.creation_date
1252 ,p_recruitment_person_id => l_person_id
1253 ,p_person_id => l_applicant_person_id
1254 ,p_assignment_id => l_applicant_assg_id
1255 ,p_application_received_date => l_data.creation_date
1256 ,p_vacancy_id => l_data.vacancy_id
1257 ,p_posting_content_id => l_data.posting_content_id
1258 ,p_per_object_version_number => l_per_ovn
1259 ,p_asg_object_version_number => l_asg_ovn
1260 ,p_applicant_number => l_applicant_number
1261 );
1262 Fnd_file.put_line(FND_FILE.LOG,'Applied successfully for vacancy:'||l_data.vacancy_id);
1263 --
1264 hr_utility.set_location(l_proc, 83);
1265 hr_person_api.update_person
1266 (p_effective_date => l_data.creation_date
1267 ,p_person_id => l_applicant_person_id
1268 ,p_datetrack_update_mode => 'CORRECTION'
1269 ,p_object_version_number => l_per_ovn
1270 ,p_last_name => l_data.last_name
1271 ,p_date_of_birth => nvl(l_data.date_of_birth,hr_api.g_date)
1272 ,p_email_address => l_data.email_address
1273 ,p_first_name => l_data.first_name
1274 ,p_sex => nvl(l_data.sex,hr_api.g_varchar2)
1275 ,p_employee_number => l_emp_number
1276 ,p_per_information_category => nvl(l_data.per_information_category,hr_api.g_varchar2)
1277 ,p_per_information1 => nvl(l_data.per_information1,hr_api.g_varchar2)
1278 ,p_per_information2 => nvl(l_data.per_information2,hr_api.g_varchar2)
1279 ,p_per_information3 => nvl(l_data.per_information3,hr_api.g_varchar2)
1280 ,p_per_information4 => nvl(l_data.per_information4,hr_api.g_varchar2)
1281 ,p_per_information5 => nvl(l_data.per_information5,hr_api.g_varchar2)
1282 ,p_per_information6 => nvl(l_data.per_information6,hr_api.g_varchar2)
1283 ,p_per_information7 => nvl(l_data.per_information7,hr_api.g_varchar2)
1284 ,p_per_information8 => nvl(l_data.per_information8,hr_api.g_varchar2)
1285 ,p_per_information9 => nvl(l_data.per_information9,hr_api.g_varchar2)
1286 ,p_per_information10 => nvl(l_data.per_information10,hr_api.g_varchar2)
1287 ,p_per_information11 => nvl(l_data.per_information11,hr_api.g_varchar2)
1288 ,p_per_information12 => nvl(l_data.per_information12,hr_api.g_varchar2)
1289 ,p_per_information13 => nvl(l_data.per_information13,hr_api.g_varchar2)
1290 ,p_per_information14 => nvl(l_data.per_information14,hr_api.g_varchar2)
1291 ,p_per_information15 => nvl(l_data.per_information15,hr_api.g_varchar2)
1292 ,p_per_information16 => nvl(l_data.per_information16,hr_api.g_varchar2)
1293 ,p_per_information17 => nvl(l_data.per_information17,hr_api.g_varchar2)
1294 ,p_per_information18 => nvl(l_data.per_information18,hr_api.g_varchar2)
1295 ,p_per_information19 => nvl(l_data.per_information19,hr_api.g_varchar2)
1296 ,p_per_information20 => nvl(l_data.per_information20,hr_api.g_varchar2)
1297 ,p_per_information21 => nvl(l_data.per_information21,hr_api.g_varchar2)
1298 ,p_per_information22 => nvl(l_data.per_information22,hr_api.g_varchar2)
1299 ,p_per_information23 => nvl(l_data.per_information23,hr_api.g_varchar2)
1300 ,p_per_information24 => nvl(l_data.per_information24,hr_api.g_varchar2)
1301 ,p_per_information25 => nvl(l_data.per_information25,hr_api.g_varchar2)
1302 ,p_per_information26 => nvl(l_data.per_information26,hr_api.g_varchar2)
1303 ,p_per_information27 => nvl(l_data.per_information27,hr_api.g_varchar2)
1304 ,p_per_information28 => nvl(l_data.per_information28,hr_api.g_varchar2)
1305 ,p_per_information29 => nvl(l_data.per_information29,hr_api.g_varchar2)
1306 ,p_per_information30 => nvl(l_data.per_information30,hr_api.g_varchar2)
1307 ,p_effective_start_date => l_effective_start_date
1308 ,p_effective_end_date => l_effective_end_date
1309 ,p_full_name => l_full_name
1310 ,p_comment_id => l_comment_id
1311 ,p_name_combination_warning => l_name_combination_warning
1312 ,p_assign_payroll_warning => l_assign_payroll_warning
1313 ,p_orig_hire_warning => l_orig_hire_warning
1314 );
1315 Fnd_file.put_line(FND_FILE.LOG,'Updated person record with EEO information');
1316 close csr_get_person_id_in_vac_bg;
1317 end if;
1318 hr_utility.set_location(l_proc, 90);
1319 --
1320 -- Update the processed status to 'A'
1321 --
1322 update irc_pending_data
1323 set processed='A'
1324 ,last_update_date = trunc(sysdate)
1325 where pending_data_id = l_data.pending_data_id;
1326 commit;
1327 --
1328 exception
1329 when others then
1330 rollback;
1331 if csr_get_person_id_in_vac_bg%isopen then
1332 close csr_get_person_id_in_vac_bg;
1333 end if;
1334 l_err_num := SQLCODE;
1335 l_err_msg := SUBSTR(SQLERRM, 1, 4000);
1336 l_msg := SUBSTR(l_err_msg,(INSTR(l_err_msg,':')+1),4000);
1337 update irc_pending_data ipd
1338 set ipd.processed='F'
1339 ,ipd.error_message=l_msg
1340 ,ipd.last_update_date=trunc(sysdate)
1341 where ipd.pending_data_id=l_data.pending_data_id;
1342 Fnd_file.put_line(FND_FILE.LOG,'An error occurred--'||l_err_msg);
1343 hr_utility.set_location('Leaving '||l_proc, 100);
1344 commit;
1345 end;
1346 end loop;
1347 end PROCESS_APPLICATIONS;
1348 --
1349 -- ----------------------------------------------------------------------------
1350 -- |-------------------------< SEND_NOTIFICATIONS >---------------------------|
1351 -- ----------------------------------------------------------------------------
1352 procedure send_notifications is
1353 cursor csr_get_applied_vacancies is
1354 select ipd.pending_data_id
1355 ,ipd.posting_content_id
1356 ,ipd.email_address
1357 ,ipd.processed
1358 ,ipd.error_message
1359 ,ipd.resume_file_name
1360 ,pav.name
1361 from irc_pending_data ipd
1362 ,per_all_vacancies pav
1363 where ipd.vacancy_id = pav.vacancy_id(+)
1364 and ipd.processed in ('A','R','E','F')
1365 order by ipd.email_address, ipd.creation_date desc;
1366 --
1367 cursor csr_is_new_user(p_email_address in varchar2) is
1368 select ipd.user_password
1369 from irc_pending_data ipd
1370 where ipd.email_address = p_email_address
1371 and ipd.user_password is not null;
1372 --
1373 cursor csr_posting_title(p_posting_content_id in number) is
1374 select ipc.job_title
1375 from irc_posting_contents_vl ipc
1376 where ipc.posting_content_id = p_posting_content_id;
1377 --
1378 type vacancies_table is
1379 table of per_all_vacancies.name%type
1380 index by binary_integer;
1381 --
1382 type comments_table is
1383 table of varchar2(1000)
1384 index by binary_integer;
1385 --
1386 type postings_table is
1387 table of irc_posting_contents_vl.job_title%type
1388 index by binary_integer;
1389 --
1390 l_resume varchar2(30);
1391 l_curr_email irc_pending_data.email_address%type;
1392 l_msg_html varchar2(32000);
1393 l_msg_text varchar2(32000);
1394 l_new_user_text varchar2(240);
1395 l_success_vacancies_list vacancies_table;
1396 l_success_vac_comments_list comments_table;
1397 l_failed_vacancies_list vacancies_table;
1398 l_failed_vac_comments_list comments_table;
1399 l_success_postings_list postings_table;
1400 l_failed_postings_list postings_table;
1401 l_notif_id number;
1402 l_password irc_pending_data.user_password%type;
1403 l_decrypted_password irc_pending_data.user_password%type;
1404 l_display_name varchar2(300); -- vacancy name plus postings title
1405 l_proc varchar2(72) := g_package||'SEND_NOTIFICATIONS';
1406
1407 vac_rows number;
1408 com_rows number;
1409 vac_rows1 number;
1410 com_rows1 number;
1411 curr_row number;
1412 --
1413 begin
1414 vac_rows := 1;
1415 com_rows := 1;
1416 vac_rows1 := 1;
1417 com_rows1 := 1;
1418 --
1419 hr_utility.set_location('Entering '||l_proc, 10);
1420 Fnd_file.put_line(FND_FILE.LOG,'Sending Notifications');
1421 --
1422 /* Process the pending data records having statuses 'A','R','E' or 'F'
1423 and having vacancy_id populated. For each of the records build the HTML
1424 test and send notifications to the user.
1425 Successful completion would set the processed status to 'S' */
1426 for l_applied_data in csr_get_applied_vacancies loop
1427 --
1428 Fnd_file.put_line(FND_FILE.LOG,'Checking the row with email_address: '||l_applied_data.email_address||' and pending_data_id '||l_applied_data.pending_data_id);
1429 hr_utility.set_location(l_proc, 20);
1430 --
1431 if l_applied_data.email_address <> l_curr_email
1432 and (l_success_vacancies_list.exists(1)
1433 or l_failed_vacancies_list.exists(1) ) then
1434 --
1435 hr_utility.set_location(l_proc, 25);
1436 Fnd_file.put_line(FND_FILE.LOG,'Building the notification text for '||l_curr_email);
1437 --
1438 -- Registration confirmation message to be shown to new users only
1439 --
1440 open csr_is_new_user(l_curr_email);
1441 fetch csr_is_new_user into l_password;
1442 if csr_is_new_user%found then
1443 fnd_message.set_name('PER', 'IRC_HA_NOTIF_NEW_USER_BODY');
1444 fnd_message.set_token('NAME', upper(l_curr_email));
1445 l_new_user_text := fnd_message.get;
1446 l_msg_html := l_msg_html|| '<BR>'||l_new_user_text || '<BR>';
1447 l_msg_text := l_msg_text|| '\n'||l_new_user_text || '\n';
1448 end if;
1449 --
1450 -- construct the HTML/text body containing the successful vacancies list
1451 -- append comments from each application
1452 --
1453 if l_success_vacancies_list.exists(1) then
1454 Fnd_file.put_line(FND_FILE.LOG,'Building list of successfull applications');
1455 --
1456 -- Construct html list of successful jobs applied for
1457 --
1458 l_msg_html := l_msg_html|| '<BR>'||fnd_message.get_string('PER','IRC_HA_NOTIF_VAC_APPLD')
1459 || '<BR>';
1460 l_msg_text := l_msg_text|| '\n' ||fnd_message.get_string('PER','IRC_HA_NOTIF_VAC_APPLD')
1461 || '\n';
1462 --
1463 curr_row := 1;
1464 while (curr_row <> vac_rows) loop
1465 fnd_message.set_name('PER', 'IRC_HA_NOTIF_VAC_NAME');
1466 fnd_message.set_token('VACNAME', l_success_vacancies_list(curr_row));
1467 fnd_message.set_token('POSTNAME',l_success_postings_list(curr_row));
1468 l_display_name := fnd_message.get;
1469 l_msg_html := l_msg_html||l_display_name|| '<BR>';
1470 curr_row := curr_row+1;
1471 end loop;
1472 --
1473 -- Construct text list of successful jobs applied for
1474 --
1475 curr_row := 1;
1476 while (curr_row <> vac_rows) loop
1477 fnd_message.set_name('PER', 'IRC_HA_NOTIF_VAC_NAME');
1478 fnd_message.set_token('VACNAME', l_success_vacancies_list(curr_row));
1479 fnd_message.set_token('POSTNAME',l_success_postings_list(curr_row));
1480 l_display_name := fnd_message.get;
1481 l_msg_text := l_msg_text||l_display_name|| '\n';
1482 curr_row := curr_row+1;
1483 end loop;
1484 vac_rows := 1;
1485 com_rows := 1;
1486 end if;
1487 --
1488 -- construct the HTML/text body containing the failed vacancies list
1489 -- append comments from each application
1490 --
1491 if l_failed_vacancies_list.exists(1) then
1492 Fnd_file.put_line(FND_FILE.LOG,'Building list of failed applications');
1493 --
1494 -- Construct html list of failed jobs applied for
1495 --
1496 l_msg_html := l_msg_html|| '<BR>'||fnd_message.get_string('PER','IRC_HA_NOTIF_VAC_FAILED')
1497 || '<BR>';
1498 l_msg_text := l_msg_text|| '\n' ||fnd_message.get_string('PER','IRC_HA_NOTIF_VAC_FAILED')
1499 || '\n';
1500 --
1501 curr_row := 1;
1502 while (curr_row <> vac_rows1) loop
1503 fnd_message.set_name('PER', 'IRC_HA_NOTIF_VAC_NAME');
1504 fnd_message.set_token('VACNAME', l_failed_vacancies_list(curr_row));
1505 fnd_message.set_token('POSTNAME',l_failed_postings_list(curr_row));
1506 l_display_name := fnd_message.get;
1507 l_msg_html := l_msg_html||l_display_name|| ' - ';
1508 l_msg_html := l_msg_html||l_failed_vac_comments_list(curr_row)|| '<BR>';
1509 curr_row := curr_row+1;
1510 end loop;
1511 --
1512 -- Construct text list of failed jobs applied for
1513 --
1514 curr_row := 1;
1515 while (curr_row <> vac_rows1) loop
1516 fnd_message.set_name('PER', 'IRC_HA_NOTIF_VAC_NAME');
1517 fnd_message.set_token('VACNAME', l_failed_vacancies_list(curr_row));
1518 fnd_message.set_token('POSTNAME',l_failed_postings_list(curr_row));
1519 l_display_name := fnd_message.get;
1520 l_msg_text := l_msg_text||l_display_name|| ' - ';
1521 l_msg_text := l_msg_text||l_failed_vac_comments_list(curr_row)|| '\n';
1522 curr_row := curr_row+1;
1523 end loop;
1524 vac_rows1 := 1;
1525 com_rows1 := 1;
1526 end if;
1527 --
1528 --
1529 if l_resume = 'true' then
1530 Fnd_file.put_line(FND_FILE.LOG,'Resume has been uploaded by the applicant and successfully parsed');
1531 --
1532 -- add additional message to notify successful resume parsing
1533 --
1534 l_msg_html := l_msg_html|| '<BR>'||fnd_message.get_string('PER','IRC_HA_NOTIF_RES_PASS')
1535 || '<BR>';
1536 l_msg_text := l_msg_text|| '\n'||fnd_message.get_string('PER','IRC_HA_NOTIF_RES_PASS')
1537 || '\n';
1538 --
1539 elsif l_resume = 'false' then
1540 Fnd_file.put_line(FND_FILE.LOG,'Resume has been uploaded by the applicant and parsing failed');
1541 --
1542 -- add additional message to indicate failed resume parsing
1543 --
1544 l_msg_html := l_msg_html|| '<BR>'||fnd_message.get_string('PER','IRC_HA_NOTIF_RES_FAILED')
1545 || '<BR>';
1546 l_msg_text := l_msg_text|| '\n'||fnd_message.get_string('PER','IRC_HA_NOTIF_RES_FAILED')
1547 || '\n';
1548 end if;
1549 --
1550 -- Append a generic message
1551 --
1552 l_msg_html := l_msg_html|| '<BR>'||fnd_message.get_string('PER','IRC_HA_NOTIF_MISC_TEXT')
1553 || '<BR>';
1554 l_msg_text := l_msg_text|| '\n'||fnd_message.get_string('PER','IRC_HA_NOTIF_MISC_TEXT')
1555 || '\n';
1556 --
1557 hr_utility.set_location(l_proc, 30);
1558 --
1559 -- Notify with different subjects for a new user and existing user
1560 --
1561 if csr_is_new_user%found then
1562 close csr_is_new_user;
1563 Fnd_file.put_line(FND_FILE.LOG,'Notifying the user with subject line about registration and job application');
1564 l_notif_id := irc_notification_helper_pkg.send_notification
1565 (p_user_name => l_curr_email
1566 ,p_subject => fnd_message.get_string('PER','IRC_HA_NOTIF_NEW_USER_SUBJ')
1567 ,p_html_body => l_msg_html
1568 ,p_text_body => l_msg_text
1569 ,p_from_role => 'SYSADMIN'
1570 );
1571 else
1572 close csr_is_new_user;
1573 Fnd_file.put_line(FND_FILE.LOG,'Notifying the user with subject line about job application');
1574 l_notif_id := irc_notification_helper_pkg.send_notification
1575 (p_user_name => l_curr_email
1576 ,p_subject => fnd_message.get_string('PER','IRC_APL_JOBAPPL_NOTIF_SUBJECT')
1577 ,p_html_body => l_msg_html
1578 ,p_text_body => l_msg_text
1579 ,p_from_role => 'SYSADMIN'
1580 );
1581 end if;
1582 commit;
1583 l_success_vacancies_list.delete;
1584 l_success_vac_comments_list.delete;
1585 l_failed_vacancies_list.delete;
1586 l_failed_vac_comments_list.delete;
1587 l_resume := null;
1588 l_msg_text := null;
1589 l_msg_html := null;
1590 end if;
1591 if l_applied_data.processed = 'F' then
1592 vac_rows1 := vac_rows1+1;
1593 com_rows1 := com_rows1+1;
1594 else
1595 vac_rows := vac_rows+1;
1596 com_rows := com_rows+1;
1597 end if;
1598 Fnd_file.put_line(FND_FILE.LOG,'Processing the row with status '||l_applied_data.processed);
1599 if l_applied_data.processed = 'R' then
1600 l_resume := 'true';
1601 l_success_vacancies_list(vac_rows-1) := l_applied_data.name;
1602 open csr_posting_title(l_applied_data.posting_content_id);
1603 fetch csr_posting_title into l_success_postings_list(vac_rows-1);
1604 close csr_posting_title;
1605 l_success_vac_comments_list(com_rows-1) := fnd_message.get_string('PER','IRC_JOB_APPLY_NOTIF_SUCCESS');
1606 elsif l_applied_data.processed = 'A' then
1607 l_success_vacancies_list(vac_rows-1) := l_applied_data.name;
1608 open csr_posting_title(l_applied_data.posting_content_id);
1609 fetch csr_posting_title into l_success_postings_list(vac_rows-1);
1610 close csr_posting_title;
1611 l_success_vac_comments_list(com_rows-1) := fnd_message.get_string('PER','IRC_JOB_APPLY_NOTIF_SUCCESS');
1612 elsif l_applied_data.processed = 'F' then
1613 l_failed_vacancies_list(vac_rows1-1) := l_applied_data.name;
1614 open csr_posting_title(l_applied_data.posting_content_id);
1615 fetch csr_posting_title into l_failed_postings_list(vac_rows1-1);
1616 close csr_posting_title;
1617 l_failed_vac_comments_list(com_rows1-1) := l_applied_data.error_message;
1618 elsif l_applied_data.processed = 'E' then
1619 l_resume := 'false';
1620 l_success_vacancies_list(vac_rows-1) := l_applied_data.name;
1621 open csr_posting_title(l_applied_data.posting_content_id);
1622 fetch csr_posting_title into l_success_postings_list(vac_rows-1);
1623 close csr_posting_title;
1624 l_success_vac_comments_list(com_rows-1) := fnd_message.get_string('PER','IRC_JOB_APPLY_NOTIF_SUCCESS');
1625 end if;
1626 l_curr_email := l_applied_data.email_address;
1627 --
1628 --update processed to S for the current row
1629 --
1630 update irc_pending_data
1631 set processed='S'
1632 ,last_update_date=trunc(sysdate)
1633 where pending_data_id=l_applied_data.pending_data_id;
1634 Fnd_file.put_line(FND_FILE.LOG,'Updated the processed flag to S');
1635 end loop;
1636 Fnd_file.put_line(FND_FILE.LOG,'Building the message for the last user');
1637 --
1638 -- Registration confirmation message to be shown to new users only
1639 --
1640 open csr_is_new_user(l_curr_email);
1641 fetch csr_is_new_user into l_password;
1642 if csr_is_new_user%found then
1643 fnd_message.set_name('PER', 'IRC_HA_NOTIF_NEW_USER_BODY');
1644 fnd_message.set_token('NAME', upper(l_curr_email));
1645 l_new_user_text := fnd_message.get;
1646 l_msg_html := l_msg_html|| '<BR>'||l_new_user_text || '<BR>';
1647 l_msg_text := l_msg_text|| '\n'||l_new_user_text || '\n';
1648 end if;
1649 --
1650 -- Build msg for the last user and send notification
1651 -- also check if there are any users to be notified
1652 --
1653 if vac_rows > 1 or vac_rows1 > 1 then
1654 --
1655 -- construct the HTML/text body containing the successful vacancies list
1656 -- append comments from each application
1657 --
1658 if vac_rows > 1 then
1659 Fnd_file.put_line(FND_FILE.LOG,'Building list of successfull applications for last user');
1660 --
1661 -- Construct html list of successful jobs applied for
1662 --
1663 l_msg_html := l_msg_html|| '<BR>'||fnd_message.get_string('PER','IRC_HA_NOTIF_VAC_APPLD')
1664 || '<BR>';
1665 l_msg_text := l_msg_text|| '\n' ||fnd_message.get_string('PER','IRC_HA_NOTIF_VAC_APPLD')
1666 || '\n';
1667 --
1668 curr_row := 1;
1669 while (curr_row <> vac_rows) loop
1670 fnd_message.set_name('PER', 'IRC_HA_NOTIF_VAC_NAME');
1671 fnd_message.set_token('VACNAME', l_success_vacancies_list(curr_row));
1672 fnd_message.set_token('POSTNAME',l_success_postings_list(curr_row));
1673 l_display_name := fnd_message.get;
1674 l_msg_html := l_msg_html||l_display_name|| '<BR>';
1675 curr_row := curr_row+1;
1676 end loop;
1677 --
1678 -- Construct text list of successful jobs applied for
1679 --
1680 curr_row := 1;
1681 while (curr_row <> vac_rows) loop
1682 fnd_message.set_name('PER', 'IRC_HA_NOTIF_VAC_NAME');
1683 fnd_message.set_token('VACNAME', l_success_vacancies_list(curr_row));
1684 fnd_message.set_token('POSTNAME',l_success_postings_list(curr_row));
1685 l_display_name := fnd_message.get;
1686 l_msg_text := l_msg_text||l_display_name|| '\n';
1687 curr_row := curr_row+1;
1688 end loop;
1689 end if;
1690 --
1691 if vac_rows1 > 1 then
1692 Fnd_file.put_line(FND_FILE.LOG,'Building list of failed applications for last user');
1693 --
1694 -- construct the HTML/text body containing the failed vacancies list
1695 -- append comments from each application
1696 --
1697 --
1698 -- Construct html list of failed jobs applied for
1699 --
1700 l_msg_html := l_msg_html|| '<BR>'||fnd_message.get_string('PER','IRC_HA_NOTIF_VAC_FAILED')
1701 || '<BR>';
1702 l_msg_text := l_msg_text|| '\n' ||fnd_message.get_string('PER','IRC_HA_NOTIF_VAC_FAILED')
1703 || '\n';
1704 --
1705 curr_row := 1;
1706 while (curr_row <> vac_rows1) loop
1707 fnd_message.set_name('PER', 'IRC_HA_NOTIF_VAC_NAME');
1708 fnd_message.set_token('VACNAME', l_failed_vacancies_list(curr_row));
1709 fnd_message.set_token('POSTNAME',l_failed_postings_list(curr_row));
1710 l_display_name := fnd_message.get;
1711 l_msg_html := l_msg_html||l_display_name|| ' - ';
1712 l_msg_html := l_msg_html||l_failed_vac_comments_list(curr_row)|| '<BR>';
1713 curr_row := curr_row+1;
1714 end loop;
1715 --
1716 -- Construct text list of failed jobs applied for
1717 --
1718 curr_row := 1;
1719 while (curr_row <> vac_rows1) loop
1720 fnd_message.set_name('PER', 'IRC_HA_NOTIF_VAC_NAME');
1721 fnd_message.set_token('VACNAME', l_failed_vacancies_list(curr_row));
1722 fnd_message.set_token('POSTNAME',l_failed_postings_list(curr_row));
1723 l_display_name := fnd_message.get;
1724 l_msg_text := l_msg_text||l_display_name|| ' - ';
1725 l_msg_text := l_msg_text||l_failed_vac_comments_list(curr_row)|| '\n';
1726 curr_row := curr_row+1;
1727 end loop;
1728 end if;
1729 --
1730 if l_resume = 'true' then
1731 Fnd_file.put_line(FND_FILE.LOG,'Last user had a successfull resume parse');
1732 --
1733 -- add additional message to notify successful resume parsing
1734 --
1735 l_msg_html := l_msg_html|| '<BR>'||fnd_message.get_string('PER','IRC_HA_NOTIF_RES_PASS')
1736 || '<BR>';
1737 l_msg_text := l_msg_text|| '\n'||fnd_message.get_string('PER','IRC_HA_NOTIF_RES_PASS')
1738 || '\n';
1739 --
1740 elsif l_resume = 'false' then
1741 Fnd_file.put_line(FND_FILE.LOG,'Last user had a unsuccessfull resume parse');
1742 --
1743 -- add additional message to indicate failed resume parsing
1744 --
1745 l_msg_html := l_msg_html|| '<BR>'||fnd_message.get_string('PER','IRC_HA_NOTIF_RES_FAILED')
1746 || '<BR>';
1747 l_msg_text := l_msg_text|| '\n'||fnd_message.get_string('PER','IRC_HA_NOTIF_RES_FAILED')
1748 || '\n';
1749 end if;
1750 --
1751 -- Append a generic message
1752 --
1753 l_msg_html := l_msg_html|| '<BR>'||fnd_message.get_string('PER','IRC_HA_NOTIF_MISC_TEXT')
1754 || '<BR>';
1755 l_msg_text := l_msg_text|| '\n'||fnd_message.get_string('PER','IRC_HA_NOTIF_MISC_TEXT')
1756 || '\n';
1757 --
1758 if l_success_vacancies_list.exists(1) or
1759 l_failed_vacancies_list.exists(1) then
1760 hr_utility.set_location(l_proc, 70);
1761 --
1762 -- Notify with different subjects for a new user and existing user
1763 --
1764 if csr_is_new_user%found then
1765 close csr_is_new_user;
1766 Fnd_file.put_line(FND_FILE.LOG,'Notifying last user with subject line about registration and job application');
1767 l_notif_id := irc_notification_helper_pkg.send_notification
1768 (p_user_name => l_curr_email
1769 ,p_subject => fnd_message.get_string('PER','IRC_HA_NOTIF_NEW_USER_SUBJ')
1770 ,p_html_body => l_msg_html
1771 ,p_text_body => l_msg_text
1772 ,p_from_role => 'SYSADMIN'
1773 );
1774 else
1775 close csr_is_new_user;
1776 Fnd_file.put_line(FND_FILE.LOG,'Notifying last user with subject line about job application');
1777 l_notif_id := irc_notification_helper_pkg.send_notification
1778 (p_user_name => l_curr_email
1779 ,p_subject => fnd_message.get_string('PER','IRC_APL_JOBAPPL_NOTIF_SUBJECT')
1780 ,p_html_body => l_msg_html
1781 ,p_text_body => l_msg_text
1782 ,p_from_role => 'SYSADMIN'
1783 );
1784 end if;
1785 commit;
1786 end if;
1787 end if;
1788 hr_utility.set_location('Leaving '||l_proc, 80);
1789 end send_notifications;
1790 --
1791 end IRC_PENDING_DATA_API;