[Home] [Help]
PACKAGE BODY: APPS.IRC_DOCUMENT_API
Source
1 Package Body IRC_DOCUMENT_API as
2 /* $Header: iridoapi.pkb 120.7 2011/05/17 12:04:29 nchinnam noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := 'IRC_DOCUMENT_API.';
7 g_full_mode varchar2(30) := 'FULL';
8 g_online_mode varchar2(30) := 'ONLINE';
9 g_none_mode varchar(30) :='NONE';
10 --
11 -- ----------------------------------------------------------------------------
12 -- |--------------------------< synchronize_index >-----------------------
13 -- ----------------------------------------------------------------------------
14 Procedure synchronize_index(p_mode in varchar2)
15 is
16 l_proc varchar2(72) := g_package||'synchronize_index';
17 l_hr_username fnd_oracle_userid.oracle_username%TYPE :=null ;
18 cursor csr_user is
19 select oracle_username
20 from fnd_oracle_userid
21 where oracle_id = 800;
22 begin
23 hr_utility.set_location('Entering:'|| l_proc, 10);
24 open csr_user;
25 fetch csr_user into l_hr_username;
26 close csr_user;
27 If l_hr_username is not null
28 then
29 if p_mode = g_full_mode
30 then
31 hr_utility.set_location(l_proc, 20);
32 ad_ctx_ddl.optimize_index
33 (idx_name=>l_hr_username||'.IRC_DOCUMENTS_CTX1'
34 ,optlevel=>'FULL'
35 ,maxtime=>null
36 ,token=>null);
37 elsif p_mode = g_online_mode
38 then
39 hr_utility.set_location(l_proc, 30);
40 ad_ctx_ddl.sync_index
41 (idx_name=>l_hr_username||'.IRC_DOCUMENTS_CTX1');
42 elsif p_mode = g_none_mode
43 then
44 hr_utility.set_location(l_proc, 35);
45 end if;
46 end if;
47 hr_utility.set_location('Leaving:'|| l_proc, 40);
48 exception
49 when others then
50 If csr_user%isopen
51 then
52 close csr_user;
53 End if;
54 raise;
55 end synchronize_index;
56 -- ----------------------------------------------------------------------------
57 -- |----------------------------< CREATE_DOCUMENT >---------------------------|
58 -- ----------------------------------------------------------------------------
59 --
60 procedure CREATE_DOCUMENT
61 (p_validate in boolean default false
62 ,p_effective_date in date
63 ,p_type in varchar2
64 ,p_person_id in number
65 ,p_mime_type in varchar2
66 ,p_assignment_id in number default null
67 ,p_file_name in varchar2 default null
68 ,p_description in varchar2 default null
69 ,p_end_date in date default null
70 ,p_document_id out nocopy number
71 ,p_object_version_number out nocopy number
72 ) is
73 --
74 -- Declare cursors and local variables
75 --
76 l_proc varchar2(72) := g_package||'CREATE_DOCUMENT';
77 l_effective_date date;
78 l_document_id number(15);
79 l_object_version_number number(9);
80 begin
81 hr_utility.set_location('Entering:'|| l_proc, 10);
82 --
83 -- Issue a savepoint
84 --
85 savepoint CREATE_DOCUMENT;
86 --
87 -- Truncate the time portion from all IN date parameters
88 --
89 l_effective_date := TRUNC(p_effective_date);
90
91 --
92 -- Call Before Process User Hook
93 --
94 begin
95 IRC_DOCUMENT_BK1.CREATE_DOCUMENT_b
96 (p_effective_date => l_effective_date
97 ,p_type => p_type
98 ,p_mime_type => p_mime_type
99 ,p_person_id => p_person_id
100 ,p_assignment_id => p_assignment_id
101 ,p_file_name => p_file_name
102 ,p_description => p_description
103 ,p_end_date => p_end_date
104 );
105
106 exception
107 when hr_api.cannot_find_prog_unit then
108 hr_api.cannot_find_prog_unit_error
109 (p_module_name => 'CREATE_DOCUMENT'
110 ,p_hook_type => 'BP'
111 );
112 end;
113 --
114 -- Process Logic
115 --
116 irc_ido_ins.ins
117 (p_effective_date => l_effective_date
118 ,p_person_id => p_person_id
119 ,p_mime_type => p_mime_type
120 ,p_type => p_type
121 ,p_assignment_id => p_assignment_id
122 ,p_character_doc => empty_clob()
123 ,p_file_name => p_file_name
124 ,p_description => p_description
125 ,p_document_id => l_document_id
126 ,p_parsed_xml => empty_clob()
127 ,p_object_version_number => l_object_version_number
128 ,p_end_date => p_end_date
129 );
130 --
131 -- Call After Process User Hook
132 --
133 begin
134 IRC_DOCUMENT_BK1.CREATE_DOCUMENT_a
135 (p_effective_date => l_effective_date
136 ,p_type => p_type
137 ,p_mime_type => p_mime_type
138 ,p_person_id => p_person_id
139 ,p_assignment_id => p_assignment_id
140 ,p_file_name => p_file_name
141 ,p_description => p_description
142 ,p_document_id => l_document_id
143 ,p_object_version_number => l_object_version_number
144 ,p_end_date => p_end_date
145 );
146
147 exception
148 when hr_api.cannot_find_prog_unit then
149 hr_api.cannot_find_prog_unit_error
150 (p_module_name => 'CREATE_DOCUMENT'
151 ,p_hook_type => 'AP'
152 );
153 end;
154 --
155 -- When in validation only mode raise the Validate_Enabled exception
156 --
157 if p_validate then
158 raise hr_api.validate_enabled;
159 end if;
160 --
161 -- Set all output arguments
162 --
163 p_document_id := l_document_id;
164 p_object_version_number := l_object_version_number;
165 hr_utility.set_location(' Leaving:'||l_proc, 70);
166 exception
167 when hr_api.validate_enabled then
168 --
169 -- As the Validate_Enabled exception has been raised
170 -- we must rollback to the savepoint
171 --
172 rollback to CREATE_DOCUMENT;
173 --
174 -- Only set output warning arguments
175 -- (Any key or derived arguments must be set to null
176 -- when validation only mode is being used.)
177 --
178 p_document_id := null;
179 p_object_version_number := null;
180 hr_utility.set_location(' Leaving:'||l_proc, 80);
181 when others then
182 --
183 -- A validation or unexpected error has occured
184 --
185 rollback to CREATE_DOCUMENT;
186 -- Reset IN OUT Parameters and set OUT parameters
187 --
188 p_document_id := null;
189 p_object_version_number := null;
190 --
191 hr_utility.set_location(' Leaving:'||l_proc, 90);
192 raise;
193 end CREATE_DOCUMENT;
194 --
195 --
196 -- ----------------------------------------------------------------------------
197 -- |----------------------------< UPDATE_DOCUMENT >---------------------------|
198 -- ----------------------------------------------------------------------------
199 --
200 procedure UPDATE_DOCUMENT
201 (p_validate in boolean default false
202 ,p_effective_date in date
203 ,p_document_id in number
204 ,p_mime_type in varchar2 default HR_API.G_VARCHAR2
205 ,p_type in varchar2 default HR_API.G_VARCHAR2
206 ,p_file_name in varchar2 default HR_API.G_VARCHAR2
207 ,p_description in varchar2 default HR_API.G_VARCHAR2
208 ,p_object_version_number in out nocopy number
209 ) is
210 --
211 -- Declare cursors and local variables
212 --
213 l_proc varchar2(72) := g_package||'CREATE_DOCUMENT';
214 l_effective_date date;
215 l_object_version_number number(9);
216 l_new_doc_id irc_documents.document_id%type;
217 l_party_id irc_documents.party_id%type;
218 l_assignment_id irc_documents.assignment_id%type;
219 l_person_id irc_documents.person_id%type;
220 l_end_date date;
221
222
223 --
224 -- Define cursor to fetch the document record
225 --
226 cursor csr_document_record Is
227 select
228 party_id,
229 assignment_id,
230 person_id
231 from irc_documents
232 where document_id = p_document_id;
233
234 l_doc_record csr_document_record%ROWTYPE;
235
236 begin
237 hr_utility.set_location('Entering:'|| l_proc, 10);
238 --
239 -- Issue a savepoint
240 --
241 savepoint UPDATE_DOCUMENT;
242 --
243 -- Truncate the time portion from all IN date parameters
244 --
245 l_effective_date := TRUNC(p_effective_date);
246
247 --open the cursor and fetch the document record
248 open csr_document_record;
249 fetch csr_document_record into l_doc_record;
250 close csr_document_record;
251 --
252 --
253 l_party_id := l_doc_record.party_id;
254 l_assignment_id := l_doc_record.assignment_id;
255 l_person_id := l_doc_record.person_id;
256 --
257
258 --
259 -- Call Before Process User Hook
260 --
261 begin
262 IRC_DOCUMENT_BK2.UPDATE_DOCUMENT_b
263 (p_effective_date => l_effective_date
264 ,p_document_id => p_document_id
265 ,p_type => p_type
266 ,p_mime_type => p_mime_type
267 ,p_file_name => p_file_name
268 ,p_description => p_description
269 ,p_object_version_number => p_object_version_number
270 );
271
272 exception
273 when hr_api.cannot_find_prog_unit then
274 hr_api.cannot_find_prog_unit_error
275 (p_module_name => 'UPDATE_DOCUMENT'
276 ,p_hook_type => 'BP'
277 );
278 end;
279 --
280 -- Process Logic
281 --
282 l_object_version_number := p_object_version_number;
283 update_document_track
284 ( p_validate => p_validate
285 ,p_effective_date => l_effective_date
286 ,p_document_id => p_document_id
287 ,p_mime_type => p_mime_type
288 ,p_type => p_type
289 ,p_file_name => p_file_name
290 ,p_description => p_description
291 ,p_person_id => l_person_id
292 ,p_party_id => l_party_id
293 ,p_end_date => l_end_date
294 ,p_assignment_id => l_assignment_id
295 ,p_object_version_number => l_object_version_number
296 ,p_new_doc_id => l_new_doc_id
297 );
298
299 --
300 -- Call After Process User Hook
301 --
302 begin
303 IRC_DOCUMENT_BK2.UPDATE_DOCUMENT_a
304 (p_effective_date => l_effective_date
305 ,p_document_id => p_document_id
306 ,p_type => p_type
307 ,p_mime_type => p_mime_type
308 ,p_file_name => p_file_name
309 ,p_description => p_description
310 ,p_object_version_number => p_object_version_number
311 );
312
313 exception
314 when hr_api.cannot_find_prog_unit then
315 hr_api.cannot_find_prog_unit_error
316 (p_module_name => 'UPDATE_DOCUMENT'
317 ,p_hook_type => 'AP'
318 );
319 end;
320 --
321 -- When in validation only mode raise the Validate_Enabled exception
322 --
323 if p_validate then
324 raise hr_api.validate_enabled;
325 end if;
326 --
327 -- Set all output arguments
328 --
329 p_object_version_number := l_object_version_number;
330 hr_utility.set_location(' Leaving:'||l_proc, 70);
331 exception
332 when hr_api.validate_enabled then
333 --
334 -- As the Validate_Enabled exception has been raised
335 -- we must rollback to the savepoint
336 --
337 rollback to UPDATE_DOCUMENT;
338 --
339 -- Only set output warning arguments
340 -- (Any key or derived arguments must be set to null
341 -- when validation only mode is being used.)
342 --
343 p_object_version_number := l_object_version_number;
344 hr_utility.set_location(' Leaving:'||l_proc, 80);
345 when others then
346 --
347 -- A validation or unexpected error has occured
348 --
349 rollback to UPDATE_DOCUMENT;
350 --
351 p_object_version_number := l_object_version_number;
352 hr_utility.set_location(' Leaving:'||l_proc, 90);
353 raise;
354 end UPDATE_DOCUMENT;
355 --
356 --
357 -- ----------------------------------------------------------------------------
358 -- |----------------------------< UPDATE_DOCUMENT_TRACK >---------------------|
359 -- ----------------------------------------------------------------------------
360 --
361 procedure UPDATE_DOCUMENT_TRACK
362 (p_validate in boolean default false
363 ,p_effective_date in date
364 ,p_document_id in number
365 ,p_mime_type in varchar2 default HR_API.G_VARCHAR2
366 ,p_type in varchar2 default HR_API.G_VARCHAR2
367 ,p_file_name in varchar2 default HR_API.G_VARCHAR2
368 ,p_description in varchar2 default HR_API.G_VARCHAR2
369 ,p_person_id in number default HR_API.G_NUMBER
370 ,p_party_id in number default HR_API.G_NUMBER
371 ,p_end_date in date default HR_API.G_DATE
372 ,p_assignment_id in number default HR_API.G_NUMBER
373 ,p_object_version_number in out nocopy number
374 ,p_new_doc_id out nocopy number
375 ) is
376 --
377 -- Declare cursors and local variables
378 --
379 l_proc varchar2(72) := g_package||'UPDATE_DOCUMENT';
380 l_effective_date date;
381 l_object_version_number number(9);
382 --
383 l_job_appln number := 0;
384 --
385 l_end_date date; --to populate end_date in irc_documents table for internet applicants
386 --
387 l_mime_type irc_documents.mime_type%type := HR_API.G_VARCHAR2;
388 l_type irc_documents.type%type := HR_API.G_VARCHAR2;
389 l_file_name irc_documents.file_name%type := HR_API.G_VARCHAR2;
390 l_description irc_documents.description%type := HR_API.G_VARCHAR2;
391 l_new_doc_id irc_documents.document_id%type;
392 l_doc_id irc_documents.document_id%type;
393 l_obj_version_number number(9);
394
395
396 begin
397 hr_utility.set_location('Entering:'|| l_proc, 10);
398 --
399 hr_utility.set_location('call function is_internet_applicant: '|| p_party_id || ' '|| l_proc, 115);
400 --
401 is_internet_applicant( p_document_id => p_document_id,
402 p_person_id => p_person_id ,
403 p_party_id => p_party_id ,
404 p_num_job_applications => l_job_appln);
405
406 --
407 hr_utility.set_location('leave function is_internet_applicant: job applications: '|| to_char(l_job_appln), 116);
408 --
409 If ((l_job_appln > 0) and (p_type In ('RESUME','AUTO_RESUME')) ) Then
410 begin --begin For update when it Is an Internet Applicant
411
412 --The Applicant Is an Internet Applicant, so first update his existing Document record with an End
413 --Date And Then create a new Document record with the new Resume he has uploaded
414 begin --begin For updating End Date And creating new Document record
415 --
416 -- Issue a savepoint
417 --
418 savepoint UPDATE_DOCUMENT_TRACK;
419 --
420 -- Truncate the time portion from all IN date parameters
421 --
422 l_effective_date := TRUNC(p_effective_date);
423 --
424 l_end_date := TRUNC(sysdate);
425
426
427 --
428 -- Call Before Process User Hook
429 --
430 begin
431 IRC_DOCUMENT_BK4.UPDATE_DOCUMENT_TRACK_b
432 (p_effective_date => l_effective_date
433 ,p_document_id => p_document_id
434 ,p_type => p_type
435 ,p_mime_type => p_mime_type
436 ,p_file_name => p_file_name
437 ,p_description => p_description
438 ,p_person_id => p_person_id
439 ,p_party_id => p_party_id
440 ,p_assignment_id => p_assignment_id
441 ,p_object_version_number => p_object_version_number
442 ,p_end_date => l_end_date
443 );
444
445 exception
446 when hr_api.cannot_find_prog_unit then
447 hr_api.cannot_find_prog_unit_error
448 (p_module_name => 'UPDATE_DOCUMENT_TRACK'
449 ,p_hook_type => 'BP'
450 );
451 end;
452 --
453 -- Process Logic
454 --
455 l_object_version_number := p_object_version_number;
456 irc_ido_upd.upd
457 (p_effective_date => l_effective_date
458 ,p_mime_type => l_mime_type
459 ,p_type => l_type
460 ,p_file_name => l_file_name
461 ,p_description => l_description
462 ,p_document_id => p_document_id
463 ,p_object_version_number => l_object_version_number
464 ,p_end_date => l_end_date
465 );
466 --
467 -- Call After Process User Hook
468 --
469 begin
470 IRC_DOCUMENT_BK4.UPDATE_DOCUMENT_TRACK_a
471 (p_effective_date => l_effective_date
472 ,p_document_id => p_document_id
473 ,p_type => p_type
474 ,p_mime_type => p_mime_type
475 ,p_file_name => p_file_name
476 ,p_description => p_description
477 ,p_person_id => p_person_id
478 ,p_party_id => p_party_id
479 ,p_assignment_id => p_assignment_id
480 ,p_object_version_number => p_object_version_number
481 ,p_end_date => l_end_date
482 );
483
484 exception
485 when hr_api.cannot_find_prog_unit then
486 hr_api.cannot_find_prog_unit_error
487 (p_module_name => 'UPDATE_DOCUMENT_TRACK'
488 ,p_hook_type => 'AP'
489 );
490 end;
491 --
492
493 --Select the Document id from the db sequence
494 --
495 begin
496 select irc_documents_s.nextval
497 into l_new_doc_id
498 from dual;
499 end;
500 --
501 --Call the setbase key value procedure In the row handler
502 --
503 irc_ido_ins.set_base_key_value
504 (p_document_id => l_new_doc_id
505 );
506 --
507
508 hr_utility.set_location('call create_document to create new record'||l_proc,117);
509 --
510 create_document
511 (p_validate => p_validate
512 ,p_effective_date => p_effective_date
513 ,p_type => p_type
514 ,p_person_id => p_person_id
515 ,p_mime_type => p_mime_type
516 ,p_assignment_id => p_assignment_id
517 ,p_file_name => p_file_name
518 ,p_description => p_description
519 ,p_end_date => p_end_date
520 ,p_document_id => l_doc_id
521 ,p_object_version_number => l_obj_version_number );
522 --
523 hr_utility.set_location('end call create_document to create new record'||l_proc,118);
524 --
525 -- When in validation only mode raise the Validate_Enabled exception
526 --
527 if p_validate then
528 raise hr_api.validate_enabled;
529 end if;
530 --
531 -- Set all output arguments
532 --
533 p_object_version_number := l_obj_version_number;
534 p_new_doc_id := l_new_doc_id;
535 hr_utility.set_location(' Leaving:'||l_proc, 70);
536 --
537 exception
538 when hr_api.validate_enabled then
539 --
540 -- As the Validate_Enabled exception has been raised
541 -- we must rollback to the savepoint
542 --
543 rollback to UPDATE_DOCUMENT_TRACK;
544 --
545 -- Only set output warning arguments
546 -- (Any key or derived arguments must be set to null
547 -- when validation only mode is being used.)
548 --
549 p_object_version_number := l_obj_version_number;
550 p_new_doc_id := l_new_doc_id;
551 hr_utility.set_location(' Leaving:'||l_proc, 80);
552 when others then
553 --
554 -- A validation or unexpected error has occured
555 --
556 rollback to UPDATE_DOCUMENT_TRACK;
557 --
558 p_object_version_number := l_obj_version_number;
559 p_new_doc_id := l_new_doc_id;
560 hr_utility.set_location(' Leaving:'||l_proc, 90);
561 raise;
562 end; --End For updating End Date And creating new Document record
563 --
564
565 End;--End For when it Is an Internet Applicant
566 Else
567 begin --begin when the applicant Is Not an Internet Applicant
568 --
569 begin --begin 1
570 --
571 -- Issue a savepoint
572 --
573 savepoint UPDATE_DOCUMENT_TRACK;
574 --
575 -- Truncate the time portion from all IN date parameters
576 --
577 l_effective_date := TRUNC(p_effective_date);
578
579 --
580 -- Call Before Process User Hook
581 --
582 begin
583 IRC_DOCUMENT_BK4.UPDATE_DOCUMENT_TRACK_b
584 (p_effective_date => l_effective_date
585 ,p_document_id => p_document_id
586 ,p_type => p_type
587 ,p_mime_type => p_mime_type
588 ,p_file_name => p_file_name
589 ,p_description => p_description
590 ,p_person_id => p_person_id
591 ,p_party_id => p_party_id
592 ,p_assignment_id => p_assignment_id
593 ,p_object_version_number => p_object_version_number
594 ,p_end_date => p_end_date
595 );
596
597 exception
598 when hr_api.cannot_find_prog_unit then
599 hr_api.cannot_find_prog_unit_error
600 (p_module_name => 'UPDATE_DOCUMENT_TRACK'
601 ,p_hook_type => 'BP'
602 );
603 end;
604 --
605 -- Process Logic
606 --
607 l_object_version_number := p_object_version_number;
608 irc_ido_upd.upd
609 (p_effective_date => l_effective_date
610 ,p_mime_type => p_mime_type
611 ,p_type => p_type
612 ,p_file_name => p_file_name
613 ,p_description => p_description
614 ,p_document_id => p_document_id
615 ,p_object_version_number => l_object_version_number
616 ,p_end_date => p_end_date
617 );
618 --
619 -- Call After Process User Hook
620 --
621 begin
622 IRC_DOCUMENT_BK4.UPDATE_DOCUMENT_TRACK_a
623 (p_effective_date => l_effective_date
624 ,p_document_id => p_document_id
625 ,p_type => p_type
626 ,p_mime_type => p_mime_type
627 ,p_file_name => p_file_name
628 ,p_description => p_description
629 ,p_person_id => p_person_id
630 ,p_party_id => p_party_id
631 ,p_assignment_id => p_assignment_id
632 ,p_object_version_number => p_object_version_number
633 ,p_end_date => p_end_date
634 );
635
636 exception
637 when hr_api.cannot_find_prog_unit then
638 hr_api.cannot_find_prog_unit_error
639 (p_module_name => 'UPDATE_DOCUMENT_TRACK'
640 ,p_hook_type => 'AP'
641 );
642 end;
643 --
644 -- When in validation only mode raise the Validate_Enabled exception
645 --
646 if p_validate then
647 raise hr_api.validate_enabled;
648 end if;
649 --
650 -- Set all output arguments
651 --
652 p_object_version_number := l_object_version_number;
653 p_new_doc_id := p_document_id;
654 hr_utility.set_location(' Leaving:'||l_proc, 70);
655 exception
656 when hr_api.validate_enabled then
657 --
658 -- As the Validate_Enabled exception has been raised
659 -- we must rollback to the savepoint
660 --
661 rollback to UPDATE_DOCUMENT_TRACK;
662 --
663 -- Only set output warning arguments
664 -- (Any key or derived arguments must be set to null
665 -- when validation only mode is being used.)
666 --
667 p_object_version_number := l_object_version_number;
668 p_new_doc_id := p_document_id;
669 hr_utility.set_location(' Leaving:'||l_proc, 80);
670 when others then
671 --
672 -- A validation or unexpected error has occured
673 --
674 rollback to UPDATE_DOCUMENT_TRACK;
675 --
676 p_object_version_number := l_object_version_number;
677 p_new_doc_id := p_document_id;
678 hr_utility.set_location(' Leaving:'||l_proc, 90);
679 raise;
680 end; --End 1
681 --
682
683 end; --End For when the Applicant Is Not an Internet Applicant
684 End if;
685 --
686 end UPDATE_DOCUMENT_TRACK;
687 --
688 -- ----------------------------------------------------------------------------
689 -- |----------------------------< APPLICATION_COUNT >-------------------------|
690 -- ----------------------------------------------------------------------------
691 --
692 procedure application_count
693 ( p_party_id in number,
694 p_num_job_applications out nocopy number)
695 Is
696 --
697 l_num_job_applications number := 0;
698 --
699 cursor csr_application_count
700 Is
701 select count(pasf.person_id)
702 from per_all_people_f paf,
703 per_all_assignments_f pasf
704 where
705 paf.party_id = p_party_id
706 and pasf.person_id = paf.person_id
707 and pasf.assignment_type = 'A'
708 and trunc(sysdate) between paf.effective_start_date and paf.effective_end_date;
709 begin
710
711 open csr_application_count;
712 fetch csr_application_count into l_num_job_applications;
713 close csr_application_count;
714 p_num_job_applications := l_num_job_applications;
715
716
717 End application_count;
718 --
719 -- ----------------------------------------------------------------------------
720 -- |----------------------------< DELETE_DOCUMENT >---------------------------|
721 -- ----------------------------------------------------------------------------
722 --
723 procedure DELETE_DOCUMENT
724 (p_validate in boolean default false
725 ,p_effective_date in date
726 ,p_document_id in number
727 ,p_object_version_number in number
728 ,p_person_id in number
729 ,p_party_id in number
730 ,p_end_date in date
731 ,p_type in varchar2
732 ,p_purge in varchar2 default 'N'
733 ) is
734 --
735 -- Declare cursors and local variables
736 --
737 l_effective_date date;
738 l_proc varchar2(72) := g_package||'DELETE_DOCUMENT';
739
740 --
741 l_end_date date;
742 --
743 l_job_applications number := 0;
744 --
745 l_object_version_number number;
746 --
747 l_doc_id number;
748 --
749 begin
750 hr_utility.set_location('Entering:'|| l_proc, 10);
751 --
752 --
753 -- Issue a savepoint
754 --
755 savepoint DELETE_DOCUMENT;
756 --
757 --
758 -- Truncate the time portion from all IN date parameters
759 --
760 l_effective_date := TRUNC(p_effective_date);
761 --
762 --
763 l_end_date := TRUNC(sysdate);
764 --
765 l_object_version_number := p_object_version_number;
766 --
767 hr_utility.set_location('call function is_internet_applicant: '|| p_party_id || ' '|| l_proc, 111);
768 --
769 application_count ( p_party_id =>p_party_id ,
770 p_num_job_applications=> l_job_applications );
771 --
772 hr_utility.set_location('leave function is_internet_applicant: job applications: '|| to_char(l_job_applications), 112);
773 --
774
775 --
776 --
777 -- Call Before Process User Hook
778 --
779 begin
780 IRC_DOCUMENT_BK3.DELETE_DOCUMENT_b
781 (p_document_id => p_document_id
782 ,p_effective_date => l_effective_date
783 ,p_object_version_number => p_object_version_number
784 ,p_person_id => p_person_id
785 ,p_party_id => p_party_id
786 ,p_end_date => p_end_date
787 ,p_type => p_type
788 ,p_purge => p_purge
789 );
790
791 exception
792 when hr_api.cannot_find_prog_unit then
793 hr_api.cannot_find_prog_unit_error
794 (p_module_name => 'DELETE_DOCUMENT'
795 ,p_hook_type => 'BP'
796 );
797 end;
798 --
799 -- If we are not purging and if candidate has applied to any job,
800 -- then documents will not be deleted. They will only be end-dated.
801 -- This is required for application-time documents functionality.
802 If ((p_purge = 'N') and (l_job_applications > 0)) Then
803 begin
804 hr_utility.set_location('call update document: '||l_proc,113);
805 --call the update document rowhandler to update the end_date for the document
806 irc_ido_upd.upd
807 (p_effective_date => l_effective_date
808 ,p_type => p_type
809 ,p_document_id => p_document_id
810 ,p_object_version_number => l_object_version_number
811 ,p_end_date => l_end_date
812 );
813
814 end;
815 else
816 begin
817 -- Process Logic
818 --
819 irc_ido_del.del
820 (p_document_id => p_document_id
821 ,p_object_version_number => p_object_version_number
822 );
823 --
824 end;
825 end if;
826
827
828 -- Call After Process User Hook
829 --
830 begin
831 IRC_DOCUMENT_BK3.DELETE_DOCUMENT_a
832 (p_document_id => p_document_id
833 ,p_effective_date => l_effective_date
834 ,p_object_version_number => p_object_version_number
835 ,p_person_id => p_person_id
836 ,p_party_id => p_party_id
837 ,p_end_date => p_end_date
838 ,p_type => p_type
839 ,p_purge => p_purge
840 );
841
842 exception
843 when hr_api.cannot_find_prog_unit then
844 hr_api.cannot_find_prog_unit_error
845 (p_module_name => 'DELETE_DOCUMENT'
846 ,p_hook_type => 'AP'
847 );
848
849 end;
850
851 if p_validate then
852 raise hr_api.validate_enabled;
853 end if;
854
855 hr_utility.set_location(' Leaving:'||l_proc, 70);
856
857 exception
858 when hr_api.validate_enabled then
859 --
860 -- As the Validate_Enabled exception has been raised
861 -- we must rollback to the savepoint
862 --
863 rollback to DELETE_DOCUMENT;
864 hr_utility.set_location(' Leaving:'||l_proc, 80);
865 when others then
866 --
867 -- A validation or unexpected error has occured
868 --
869 rollback to DELETE_DOCUMENT;
870 hr_utility.set_location(' Leaving:'||l_proc, 90);
871 raise;
872
873
874 end DELETE_DOCUMENT;
875 --
876 --
877 -- ----------------------------------------------------------------------------
878 -- |---------------------------< PROCESS_DOCUMENT >---------------------------|
879 -- ----------------------------------------------------------------------------
880 --
881 procedure PROCESS_DOCUMENT
882 (p_document_id in number
883 ) IS
884 --
885 l_proc varchar2(72) := g_package||'PROCESS_DOCUMENT';
886 --
887 -- Cursor to ensure row exists in table.
888 cursor csr_doc_exists(p_document_id number) is
889 select rowid
890 from irc_documents
891 where document_id = p_document_id;
892 --
893 l_hr_username fnd_oracle_userid.oracle_username%TYPE :=null;
894 cursor csr_user is
895 select oracle_username
896 from fnd_oracle_userid
897 where oracle_id = 800;
898 --
899 l_rowid rowid;
900 --
901 clob_doc clob;
902 --
903 l_ret varchar2(30);
904 begin
905 --
906 hr_utility.set_location(' Entering:'||l_proc, 10);
907 --
908 open csr_user;
909 fetch csr_user into l_hr_username;
910 close csr_user;
911 --
912 If l_hr_username is not null -- enter if product is found
913 then
914 -- Ensure doc exists, if not raise error.
915 open csr_doc_exists(p_document_id);
916 fetch csr_doc_exists into l_rowid;
917 If csr_doc_exists%found then
918 close csr_doc_exists;
919 --
920 -- Convert document
921 ctx_doc.filter(index_name => l_hr_username || '.IRC_DOCUMENTS_CTX',
922 textkey => l_rowid,
923 restab => clob_doc,
924 plaintext => true);
925 --
926 irc_ido_shd.g_api_dml := true; -- Set the api dml status
927 --
928 update irc_documents
929 set character_doc = clob_doc
930 where document_id = p_document_id;
931 --
932 irc_ido_shd.g_api_dml := false; -- Unset the api dml status
933 --
934 hr_utility.set_location(l_proc||' - BLOB Conversion Complete', 20);
935 --
936 -- Synchronize interMedia index
937 hr_utility.set_location(l_proc||' - Synchronization Complete', 30);
938 --
939 else
940 close csr_doc_exists;
941 hr_utility.set_message(800, 'IRC_412046_IDO_INV_DOC_ID');
942 hr_utility.raise_error;
943 end if;
944 end if; -- End if for l_hr_username is not null
945 hr_utility.set_location(' Leaving:'||l_proc, 40);
946 --
947 exception
948 when others then
949 If csr_user%isopen
950 then
951 close csr_user;
952 End if;
953 If csr_doc_exists%isopen
954 then
955 close csr_doc_exists;
956 End if;
957 if(fnd_log.test(fnd_log.level_error,'per.irc_document_api.process_document')) then
958 hr_utility.log_at_error_level('per'
959 ,'irc_document_api.process_document'
960 ,'unable to process'
961 ,dbms_utility.format_error_stack);
962 end if;
963 fnd_message.set_name('PER', 'IRC_UNABLE_TO_INDEX_DOC');
964 fnd_msg_pub.add_detail
965 (p_message_type => 'I'
966 );
967 end process_document;
968 --
969 function get_html_preview
970 (p_document_id in number,p_highlight_string in varchar2 default null) return clob is
971 cursor get_schema_name is
972 select oracle_username
973 from fnd_oracle_userid
974 where oracle_id = 800;
975
976 cursor csr_doc_exists is
977 select rowid
978 from irc_documents
979 where document_id = p_document_id;
980 cursor csr_doc_type is
981 select file_name
982 from irc_documents
983 where document_id = p_document_id;
984
985 l_rowid rowid;
986 l_schema_name varchar2(20) := null;
987 l_output_clob clob := null;
988 l_file_name IRC_DOCUMENTS.FILE_NAME%type;
989 l_type varchar2(10);
990 begin
991 --
992 fnd_msg_pub.delete_msg;
993 --
994 open csr_doc_exists;
995 fetch csr_doc_exists into l_rowid;
996 if (csr_doc_exists%found) then
997 close csr_doc_exists;
998
999 open get_schema_name;
1000 fetch get_schema_name into l_schema_name;
1001 if (get_schema_name%found) then
1002 close get_schema_name;
1003 -- Highlight the keywords if highlight string is null
1004 if (trim(p_highlight_string) is not null) then
1005 CTX_DOC.MARKUP (index_name => l_schema_name||'.IRC_DOCUMENTS_CTX' ,
1006 textkey => l_rowid,
1007 text_query => p_highlight_string,
1008 restab => l_output_clob,
1009 plaintext => FALSE,
1010 tagset => 'HTML_DEFAULT',
1011 starttag => IRC_MARKUP_STARTTAG,
1012 endtag => IRC_MARKUP_ENDTAG);
1013
1014 -- IF keyword is null then generate html version without markup
1015 else
1016 ctx_doc.filter(index_name => l_schema_name||'.IRC_DOCUMENTS_CTX',
1017 textkey => l_rowid,
1018 restab => l_output_clob,
1019 plaintext => false);
1020 end if;
1021 open csr_doc_type;
1022 fetch csr_doc_type into l_file_name;
1023 if(csr_doc_type%found) then
1024 l_type := lower(substr(l_file_name,- 4));
1025 if(l_type = '.pdf') then
1026 ame_util.runtimeexception('IRC_DOCUMENT_API','get_html_preview',-9999,'Adding the customizations');
1027 l_output_clob := '<br id="pdfBr"/><div style="position:relative" id="pdfHtmlDoc">' || l_output_clob;
1028 l_output_clob := l_output_clob || '</div><script>
1029 function getRealTop(mChildIn,maxTop)
1030 {
1031 var childMarker=mChildIn.childCount
1032 if(childMarker!=undefined)
1033 {
1034 for (m=0;m<childMarker ;m++ )
1035 {
1036 var m_ChildIn=mChildIn.childNodes[m];
1037 maxTop=getRealTop(m_ChildIn);
1038 }
1039 }
1040 var m_Top=mChildIn.offsetTop
1041 if(m_Top !=undefined)
1042 {
1043 var m_Height=mChildIn.offsetHeight;
1044 if(m_Height!=undefined)
1045 {
1046 m_Top=m_Top+m_Height;
1047 }
1048 if(m_Top>maxTop)
1049 {
1050 maxTop=m_Top;
1051 }
1052 }
1053 return maxTop;
1054 }
1055 var divElement=document.getElementById("pdfHtmlDoc")
1056 var brTop=document.getElementById("pdfBr").offsetTop
1057 var brHeight=document.getElementById("pdfBr").offsetHeight
1058 var maxTop=0
1059 for (i=0;i<divElement.childNodes.length;i++)
1060 {
1061 var mchild=divElement.childNodes[i]
1062 maxTop=getRealTop(mchild,maxTop);
1063 }
1064 if(maxTop>brTop)
1065 {
1066 var mLength=Math.ceil((maxTop)/(brHeight-1));
1067 for (j=0;j<=mLength ;j++ )
1068 {
1069 document.writeln("<br/>")
1070 }
1071 document.writeln("<br/>")
1072 }</script>';
1073 ame_util.runtimeexception('IRC_DOCUMENT_API','get_html_preview',-9999,'Added the customizations');
1074 end if;
1075 end if;
1076 close csr_doc_type;
1077 else
1078 close get_schema_name;
1079 end if;
1080 else
1081 close csr_doc_exists;
1082 hr_utility.set_message(800, 'IRC_412046_IDO_INV_DOC_ID');
1083 hr_utility.raise_error;
1084 end if;
1085
1086 return l_output_clob;
1087 exception
1088 when others then
1089 if(fnd_log.test(fnd_log.level_error,'per.irc_document_api.get_html_preview')) then
1090 hr_utility.log_at_error_level('per'
1091 ,'irc_document_api.get_html_preview'
1092 ,'unable to preview'
1093 ,dbms_utility.format_error_stack);
1094 end if;
1095 hr_utility.log_at_error_level('per','get_html_preview','unable to preview'
1096 ,dbms_utility.format_error_stack);
1097 fnd_message.set_name('PER', 'IRC_UNABLE_TO_PREVIEW_DOC');
1098 fnd_msg_pub.add_detail
1099 (p_message_type => 'W'
1100 );
1101 return l_output_clob;
1102
1103 end get_html_preview;
1104
1105 procedure is_internet_applicant
1106 ( p_document_id in number,
1107 p_person_id in number,
1108 p_party_id in number,
1109 p_num_job_applications out nocopy number)
1110 Is
1111
1112 --
1113 l_num_job_applications number := 0;
1114 --
1115
1116 cursor csr_internet_applicant
1117 Is
1118 select count(pasf.person_id)
1119 from per_all_people_f paf,
1120 per_all_assignments_f pasf
1121 where
1122 paf.party_id = p_party_id
1123 and trunc(sysdate) between paf.effective_start_date and paf.effective_end_date
1124 and trunc(sysdate) between pasf.effective_start_date and pasf.effective_end_date
1125 and pasf.person_id = paf.person_id
1126 and pasf.business_group_id in ( select bginfo.organization_id from hr_organization_information bginfo
1127 where bginfo.ORG_INFORMATION_CONTEXT = 'BG Recruitment'
1128 and bginfo.org_information11 = 'Y'
1129 );
1130 begin
1131
1132 open csr_internet_applicant;
1133 fetch csr_internet_applicant into l_num_job_applications;
1134
1135 close csr_internet_applicant;
1136
1137
1138 p_num_job_applications := l_num_job_applications;
1139
1140
1141 End is_internet_applicant;
1142
1143
1144 end IRC_DOCUMENT_API;