1 package body hr_offer_custom as
2 /* $Header: hrcustwf.pkb 120.3 2006/01/17 00:05:11 sturlapa noship $ */
3 --
4 g_package varchar2(31) := 'hr_offer_custom.';
5 --c_title hr_util_web.g_title%type;
6 --c_prompts hr_util_web.g_prompts%type;
7 --
8 -- ----------------------------------------------------------------------------
9 -- This is the generic product version
10 -- ----------------------------------------------------------------------------
11 --
12 -- ----------------------------------------------------------------------------
13 -- |------------------------< check_wf_question_status >----------------------|
14 -- ----------------------------------------------------------------------------
15 --
16 -- This function returns a boolean answer depending on whether an active
17 -- workflow exists for the question about to be changed.
18 --
19 function check_wf_question_status (p_proposal_question_name in varchar2)
20 return boolean is
21 --
22 l_count number;
23 --
24 Begin
25 --
26 Select count(*)
27 Into l_count
28 From per_assign_proposal_answers papa
29 Where papa.proposal_question_name = p_proposal_question_name
30 and exists
31 (Select *
32 From wf_item_activity_statuses wf
33 Where wf.item_key = papa.assignment_id
34 And wf.item_type = 'HR_OFFER'
35 And wf.activity_status = 'ACTIVE'
36 );
37 --
38 if l_count > 0 then -- the question exists in an active workflow
39 return (TRUE);
40 else
41 return (FALSE);
42 end if;
43 --
44 end check_wf_question_status;
45 --
46 -- ----------------------------------------------------------------------------
47 -- |------------------------------< get_hr_routing1 >-------------------------|
48 -- ----------------------------------------------------------------------------
49 --
50 -- This function returns the Id of an HR representative that handles the New
51 -- Hire Process and that maintains the organizational hierarchy.
52 --
53 -- This is used for routing in the Web Offers workflow, 'Offer Letter'.
54 --
55 function get_hr_routing1
56 (p_person_id in per_people_f.person_id%type)
57 return per_people_f.person_id%type is
58 begin
59 -- [CUSTOMIZE]
60 -- This function will need to be modified by the end customer.
61 -- We return the Hr Assistant id that handles the New Hire process
62 return(999999);
63 end get_hr_routing1;
64 -- ----------------------------------------------------------------------------
65 -- |------------------------------< get_hr_routing2 >-------------------------|
66 -- ----------------------------------------------------------------------------
67 --
68 -- This function returns the Id of an HR representative that would terminate
69 -- applicants and mail out offer letters.
70 --
71 -- This is used for routing in the Web Offers workflow, 'Offer Letter'.
72 --
73 function get_hr_routing2
74 (p_person_id in per_people_f.person_id%type)
75 return per_people_f.person_id%type is
76 begin
77 -- [CUSTOMIZE]
78 -- This function will need to be modified by the end customer.
79 -- We return a specific id that handles Mailing the letter,
80 -- waiting for the candidate's response, and terminating the applicant
81 -- if needed.
82 return(999998);
83 end get_hr_routing2;
84 -- ----------------------------------------------------------------------------
85 -- |------------------------------< get_hr_routing3 >-------------------------|
86 -- ----------------------------------------------------------------------------
87 --
88 -- This function returns the Id of a technical HR employee that could check the
89 -- approval chain for errors.
90 --
91 -- This is used for routing in the Web Offers workflow, 'Offer Letter'.
92 --
93 function get_hr_routing3
94 (p_person_id in per_people_f.person_id%type)
95 return per_people_f.person_id%type is
96 begin
97 -- [CUSTOMIZE]
98 -- This function will need to be modified by the end customer.
99 -- We return a specific id that handles database/system errors
100 return(999997);
101 end get_hr_routing3;
102 -- ----------------------------------------------------------------------------
103 -- |---------------------------< get_candidate_details >----------------------|
104 -- ----------------------------------------------------------------------------
105 --
106 -- This procedure retuns the Person Id, Applicant Number and Display Name
107 -- (as first_name||last_name) of the Candidate.
108 --
109 procedure get_candidate_details
110 (p_candidate_assignment_id in per_assignments_f.assignment_id%type
111 ,p_candidate_person_id out nocopy per_people_f.person_id%type
112 ,p_candidate_disp_name out nocopy varchar2
113 ,p_applicant_number out nocopy per_people_f.applicant_number%type) is
114 --
115 cursor csr_pp(l_effective_date in date) is
116 select ppf.person_id
117 ,ppf.first_name||' '||ppf.last_name
118 ,ppf.applicant_number
119 from per_all_people_f ppf
120 ,per_all_assignments_f paf
121 where paf.assignment_id = p_candidate_assignment_id
122 and l_effective_date
123 between paf.effective_start_date
124 and paf.effective_end_date
125 and ppf.person_id = paf.person_id
126 and l_effective_date
127 between ppf.effective_start_date
128 and ppf.effective_end_date;
129 --
130 begin
131 -- open the candidate select cursor
132 open csr_pp(trunc(sysdate));
133 -- fetch the candidate details
134 fetch csr_pp
135 into p_candidate_person_id
136 ,p_candidate_disp_name
137 ,p_applicant_number;
138 if csr_pp%notfound then
139 -- if the cursor does not return a row then we must set the out
140 -- parameters to null
141 p_candidate_person_id := null;
142 p_candidate_disp_name := null;
143 p_applicant_number := null;
144 end if;
145 -- close the cursor
146 close csr_pp;
147 --
148 end get_candidate_details;
149 -- ----------------------------------------------------------------------------
150 -- |-------------------------< get_hr_manager_details >-----------------------|
151 -- ----------------------------------------------------------------------------
152 --
153 -- This function returns the Id of an HR manager that the candidate may want to
154 -- contact for more information. This appears on the offer letter.
155 --
156 function get_hr_manager_details
157 (p_person_id in per_people_f.person_id%type)
158 return per_people_f.person_id%type is
159 begin
160 -- [CUSTOMIZE]
161 -- This function will need to be modified by the end customer.
162 -- Presently p_person_id is returned.
163 -- return(p_person_id);
164 return(999996);
165 end get_hr_manager_details;
166 -- ----------------------------------------------------------------------------
167 -- |---------------------< set_training_admin_person >-----------------------|
168 -- ----------------------------------------------------------------------------
169 --
170 -- This function returns the Id of a Training Manager.
171 --
172 function set_training_admin_person
173 return number is
174 l_training_admin_person number;
175 begin
176
177 -- Bug#1425440 hdshah Read default training administrator from profile instead of hard-coded value.
178 l_training_admin_person := fnd_profile.value('OTA_SS_DEFAULT_TRAINING_ADMINISTRATOR');
179 return l_training_admin_person;
180 -- return(9686);
181 end set_training_admin_person;
182 -- ----------------------------------------------------------------------------
183 -- |---------------------< set_supervisor_id >-----------------------|
184 -- ----------------------------------------------------------------------------
185 --
186 -- This function returns the Id of a supervisor.
187 --
188 function set_supervisor_id (p_person_id in per_all_people_f.person_id%type)
189 return per_all_people_f.person_id%type is
190 --
191 cursor csr_supervisor_id is
192 select a.supervisor_id
193 from per_all_assignments_f a
194 where a.person_id = p_person_id
195 and a.primary_flag = 'Y'
196 and a.assignment_type in ('E','C')
197 and trunc(sysdate)
198 between a.effective_start_date and effective_end_date;
199 -- Fix 2883914 - Filter benifits records from the cursor.
200 --
201 l_supervisor_id number;
202 --
203 BEGIN
204 OPEN csr_supervisor_id;
205 FETCH csr_supervisor_id into l_supervisor_id;
206 If csr_supervisor_id%notfound then
207 l_supervisor_id := null;
208 END IF;
209 CLOSE csr_supervisor_id;
210
211 return(l_supervisor_id);
212 END set_supervisor_id ;
213
214
215 -- ----------------------------------------------------------------------------
216 -- |-----------------------------< get_next_approver >------------------------|
217 -- ----------------------------------------------------------------------------
218 --
219 -- This function goes up the approval chain to determine who is the next
220 -- manager to approve the offer. This is used in:
221 -- Routing in the Web Offers workflow, 'Offer Letter'.
222 -- Security checks on the web pages (to determine who may see which offers),
223 -- (via check-if-in-approval-chain).
224 -- Get VP name to get the vice president's name to display on the Offer Page.
225 -- Get signatory details to decide who needs to sign the offer letter.
226 --
227 function get_next_approver
228 (p_person_id in per_people_f.person_id%type)
229 return per_people_f.person_id%type is
230 --
231 cursor csr_pa(l_effective_date in date
232 ,l_in_person_id in per_people_f.person_id%type) is
233 select ppf.person_id
234 from per_all_assignments_f paf
235 ,per_all_people_f ppf
236 where paf.person_id = l_in_person_id
237 and paf.primary_flag = 'Y'
238 and l_effective_date
239 between paf.effective_start_date
240 and paf.effective_end_date
241 and ppf.person_id = paf.supervisor_id
242 and ppf.current_employee_flag = 'Y'
243 and l_effective_date
244 between ppf.effective_start_date
245 and ppf.effective_end_date;
246 --
247 l_out_person_id per_people_f.person_id%type default null;
248 --
249 begin
250 -- [CUSTOMIZE]
251 -- open the candidate select cursor
252 open csr_pa(trunc(sysdate), p_person_id);
253 -- fetch the candidate details
254 fetch csr_pa into l_out_person_id;
255 if csr_pa%notfound then
256 -- if the cursor does not return a row then we must set the out
257 -- parameter to null
258 l_out_person_id := null;
259 end if;
260 --
261 -- close the cursor
262 close csr_pa;
263 return(l_out_person_id);
264 end get_next_approver;
265 -- ------------------------------------------------------------------------
266 -- |---------------------< get_url_string >----------------------------------|
267 -- ------------------------------------------------------------------------
268 --
269 -- Description:
270 -- This procedure returns the url string needed to build up urls when
271 -- running disconnected from the web server (such as from email for
272 -- workflow notifications).
273 -- ------------------------------------------------------------------------
274 function get_url_string
275 return varchar2 is
276 l_owa2 varchar2(2000);
277 begin
278 l_owa2 := fnd_profile.value('APPS_WEB_AGENT');
279 return l_owa2;
280 end get_url_string;
281 -- ------------------------------------------------------------------------
282 -- name:
283 -- get_vp_name
284 -- description:
285 -- This procedure obtains the name of the Vice President that the
286 -- candidate will report to. It goes up the approval chain for a
287 -- candidate and finds the first person with a job name like '%Vice%'
288 -- Obviously this won't work in the police field.
289 --
290 -- This name displays on the Update Offer Web Page.
291 -- requirement:
292 -- insert a row into fnd_sessions before calling
293 -- ------------------------------------------------------------------------
294 procedure get_vp_name
295 (p_assignment_id in number
296 ,p_vp_name out nocopy varchar2
297 ,p_job_name out nocopy varchar2) is
298 --
299 l_proc_name varchar2(200) default 'get_vp_name';
300 --
301 cursor csr_papa is
302 select distinct papa.person_id, pj.name
303 from per_jobs_tl pj
304 ,per_assignments pa
305 ,per_assign_proposal_answers papa
306 where pj.job_id = pa.job_id
307 and pj.language=userenv('LANG')
308 and pa.primary_flag = 'Y'
309 and pa.person_id = papa.person_id
310 and papa.assignment_id = p_assignment_id;
311 --
312 cursor csr_person (p_person_id in number) is
313 select distinct first_name||' '||last_name
314 from per_people
315 where person_id = p_person_id;
316 --
317 cursor csr_job (p_person_id in number) is
318 select distinct pj.name
319 from per_jobs_vl pj
320 ,per_assignments pa
321 where pj.job_id = pa.job_id
322 and pa.primary_flag = 'Y'
323 and pa.person_id = p_person_id;
324 --
325 l_in_chain boolean := false;
326 l_current_person_id per_people_f.person_id%type;
327 l_current_job per_jobs.name%type;
328 l_person_id per_people_f.person_id%type;
329 l_vp_name varchar(200);
330 l_found_it boolean default FALSE;
331 l_supervisors varchar2(32000);
332 l_dead_loop exception;
333 --
334 begin
335 -- determine the hiring manager for the candidate
336 open csr_papa;
337 fetch csr_papa into l_current_person_id, l_current_job;
338 close csr_papa;
339 --
340 --
341 while l_current_person_id is not null loop
342 if upper(l_current_job) like '%VICE%' then
343 p_job_name := l_current_job;
344 open csr_person(l_current_person_id);
345 fetch csr_person into l_vp_name;
346 close csr_person;
347 p_vp_name := l_vp_name;
348 l_found_it := TRUE;
349 exit;
350 else
351 l_supervisors := l_supervisors || ',' || to_char(l_current_person_id);
352 l_current_person_id := get_next_approver
353 (p_person_id => l_current_person_id);
354 if l_current_person_id is null then
355 exit;
356 end if;
357 --check approval chain dead loop
358 if instr(l_supervisors,to_char(l_current_person_id)) <> 0 then
359 raise l_dead_loop;
360 end if;
361 open csr_job(l_current_person_id);
362 fetch csr_job into l_current_job;
363 close csr_job;
364 end if;
365 end loop;
366 -- If nothing was found with 'VICE' in the loop, then the loop
367 -- will exit and we have found no Vice President in the approval chain.
368 --
369 if not l_found_it then
370 p_vp_name := null;
371 p_job_name := null;
372 end if;
373 --
374 exception
375 when l_dead_loop then
376 -- set OUT parameters.
377 p_vp_name := null;
378 p_job_name:= null;
379
380 fnd_message.set_name('PER','HR_WEB_APPROVAL_DEAD_LOOP');
381 --hr_java_script_web.alert(fnd_message.get);
382 raise hr_util_web.g_error_handled;
383 when others then
384 -- set OUT parameters.
385 p_vp_name := null;
386 p_job_name:= null;
387 --hr_java_script_web.alert(sqlerrm||' '||sqlcode);
388 raise hr_util_web.g_error_handled;
389 end get_vp_name;
390 -- ----------------------------------------------------------------------------
391 -- |--------------------------< check_final_approver >------------------------|
392 -- ----------------------------------------------------------------------------
393 --
394 -- This function determines if the current manager is the final approver for
395 -- an offer. It is used in the Web Offer workflow, 'Offer Letter' to determine
396 -- if we need to look for the next approver or if the offer is ready to be sent
397 -- to HR for printing/mailing.
398 --
399 function check_final_approver
400 (p_candidate_assignment_id in per_assignments_f.assignment_id%type
401 ,p_fwd_to_mgr_id in per_people_f.person_id%type
402 ,p_person_id in per_people_f.person_id%type)
403 return varchar2 is
404 --
405 cursor csr_pa(l_effective_date in date) is
406 select paf.person_id
407 from per_all_assignments_f paf
408 start with paf.person_id = p_person_id
409 and paf.primary_flag = 'Y'
410 and l_effective_date
411 between paf.effective_start_date
412 and paf.effective_end_date
413 connect by prior paf.supervisor_id = paf.person_id
414 and paf.primary_flag = 'Y'
415 and l_effective_date
416 between paf.effective_start_date
417 and paf.effective_end_date;
418 --
419 l_person_id per_people_f.person_id%type := null;
420 --
421 begin
422 --
423 --
424 -- loop through each row. the rows are returned in an order which makes
425 -- the last row selected the top most node of the chain.
426 for lcsr in csr_pa(trunc(sysdate)) loop
427 -- set the l_person_id variable to the row fetched
428 l_person_id := lcsr.person_id;
429 end loop;
430 if p_fwd_to_mgr_id = l_person_id then
431 return('Y');
432 else
433 return('N');
434 end if;
435 exception
436 when others then
437 return('E');
438 --
439 end check_final_approver;
440 -- ----------------------------------------------------------------------------
441 -- |-----------------------< check_if_in_approval_chain >---------------------|
442 -- ----------------------------------------------------------------------------
443 --
444 -- This function goes up the approval chain (via get_next_approver) to
445 -- determine if the person who is trying to look at a Candidate Offer can see
446 -- the offer information or not. It allows specific HR employee's to see the
447 -- data in update mode. It is used on the web pages.
448 --
449 function check_if_in_approval_chain
450 (p_person_id in per_people_f.person_id%type
451 ,p_candidate_assignment_id in per_assignments_f.assignment_id%type)
452 return boolean is
453 --
454 --1754123 begin
455 --the creator may not be the hiring manager if the
456 --profile option HR_USE_HIRE_MGR_APPR_CHAIN is set to 'Y'.
457 --So, we should get the hiring manager from per_all_assignments_f
458
459 --cursor csr_papa is
460 -- select distinct papa.person_id
461 -- from per_assign_proposal_answers papa
462 -- where papa.assignment_id = p_candidate_assignment_id;
463
464 cursor csr_supervisor is
465 select asg.supervisor_id
466 from per_all_assignments_f asg
467 where asg.assignment_id = p_candidate_assignment_id;
468 --1754123 end
469
470 --
471 l_in_chain boolean := false;
472 l_current_person_id per_people_f.person_id%type;
473 l_person_id per_people_f.person_id%type;
474 --
475 begin
476 -- Oracle internal only:
477 -- These are HR reps.
478 if p_person_id in (999999, 999998, 999997) then
479 l_in_chain := true;
480 return(l_in_chain);
481 end if;
482 -- determine the hiring manager for the candidate
483 --1754123 begin
484 --open csr_papa;
485 --fetch csr_papa into l_current_person_id;
486 --close csr_papa;
487
488 open csr_supervisor;
489 fetch csr_supervisor into l_current_person_id;
490 close csr_supervisor;
491 --1754123 end
492 --
493 while l_current_person_id is not null loop
494 if l_current_person_id = p_person_id then
495 l_in_chain := true;
496 exit;
497 else
498 l_current_person_id := get_next_approver
499 (p_person_id => l_current_person_id);
500 end if;
501 end loop;
502 return(l_in_chain);
503 end check_if_in_approval_chain;
504 -- ----------------------------------------------------------------------------
505 -- |--------------------------< get_signatories_details >---------------------|
506 -- ----------------------------------------------------------------------------
507 --
508 -- This function determines who should sign the offer letter. It's used when
509 -- generating the offer letter.
510 --
511 procedure get_signatories_details
512 (p_person_id in per_people_f.person_id%type
513 ,p_candidate_assignment_id in per_assignments_f.assignment_id%type
514 ,p_signatory_id1 out nocopy per_people_f.person_id%type
515 ,p_position_title1 out nocopy varchar2
516 ,p_signatory_id2 out nocopy per_people_f.person_id%type
517 ,p_position_title2 out nocopy varchar2
518 ,p_signatory_id3 out nocopy per_people_f.person_id%type
519 ,p_position_title3 out nocopy varchar2) is
520 --
521 l_fwd_to_mgr_id per_people_f.person_id%type := p_person_id;
522 --
523 begin
524 -- find the final approver
525 loop
526 l_fwd_to_mgr_id := get_next_approver
527 (p_person_id => l_fwd_to_mgr_id);
528 --
529 if l_fwd_to_mgr_id is not null then
530 -- check to see if final approver
531 if check_final_approver
532 (p_candidate_assignment_id => p_candidate_assignment_id
533 ,p_fwd_to_mgr_id => l_fwd_to_mgr_id
534 ,p_person_id => p_person_id)= 'Y' then
535 -- the final approver has been found
536 p_signatory_id1 := l_fwd_to_mgr_id;
537 exit;
538 end if;
539 else
540 -- a broken chain must exist therefore we cannot set the signatory
541 -- details
542 exit;
543 end if;
544 end loop;
545 --
546 -- Position title of approvers.
547 --
548 p_position_title1 := '(Position title1)';
549 p_position_title2 := '(Position title2)';
550 p_position_title3 := '(Position title3)';
551 --
552 exception
553 when others then
554 p_signatory_id1 := null;
555 p_position_title1 := null;
556 p_signatory_id2 := null;
557 p_position_title2 := null;
558 p_signatory_id3 := null;
559 p_position_title3 := null;
560 raise;
561
562 end get_signatories_details;
563 -- ----------------------------------------------------------------------------
564 -- |-----------------------------< set_apl_status >---------------------------|
565 -- ----------------------------------------------------------------------------
566 -- Description:
567 -- this procedure will correctly update the applicant assignment status type
568 -- to the specified type. because the assignment entity is datetracked this
569 -- change might have to be 'rippled' through onto future datetracked rows,
570 -- if the rows exist in the future. this procedure adhere's to the following
571 -- rules:
572 --
573 -- 1. performing an update on the day the applicant was created:
574 --
575 -- because an applicant assignment has to be an ACTIVE_APL for at least
576 -- 1 day, you cannot perform any datetrack update operations on this day.
577 -- to get around this problem, the effective date has to be increased by
578 -- a day (which equates to sysdate + 1). after the effective date has
579 -- been increased, the datetrack mode can be re-derived.
580 --
581 -- 2. performing an update after the day the applicant was created without
582 -- any future changes existing:
583 --
584 -- if the datetrack mode of UPDATE is allowed as of the session date,
585 -- then this mode will always succeed. because future changes do
586 -- not exist the process will finish.
587 --
588 -- 3. performing an update after the day the applicant was created but
589 -- with future changes existing:
590 --
591 -- if future changes exist as of the effective date then we need to
592 -- establish; a) the datetrack mode to be used for the initial API status
593 -- call, and b) if the future changes require further datetrack
594 -- CORRECTION updates to the future rows.
595 --
596 -- a) if the effective date of the API call falls on an effective start
597 -- date of the applicant assignment then only a CORRECTION is allowed
598 -- otherwise only a UPDATE_CHANGE_INSERT is allowed.
599 -- b) if future rows exist beyond the current row as of the effective
600 -- date then each subsequent row needs to be date effectively updated
601 -- by the API in CORRECTION mode to reflect the assignment status
602 -- change (referred to as rippling). if during execution of the API an
603 -- error is encountered, then the error is ignored and the processing
604 -- of future rows is stopped.
605 --
606 -- Pre Conditions:
607 -- The applicant must exist.
608 --
609 -- In Arguments:
610 -- p_candidate_assignment_id -> The assignment_id of the candidate
611 -- (applicant).
612 -- p_status_type_id -> The applicant assignment status type
613 -- to update the assignment with.
614 -- If not specified (i.e. null) then the
615 -- API will assume the default OFFER
616 -- status is required.
617 -- Post Success:
618 -- The API will update the applicant assignment row(s) to the specified or
619 -- defaulted status.
620 --
621 -- Post Failure:
622 -- An error will be raised.
623 --
624 -- Developer Implmentation Notes:
625 --
626 -- Access Status:
627 -- Private to this package.
628 -- ----------------------------------------------------------------------------
629 procedure set_apl_status
630 (p_candidate_assignment_id in
631 per_assignments_f.assignment_id%type
632 ,p_status_type_id in
633 per_assignments_f.assignment_status_type_id%type default null) is
634 --
635 -- define the pl/sql table types to be used
636 --
637 type l_ed_tab_type is table of date
638 index by binary_integer;
639 type l_ovn_tab_type is table of per_assignments_f.object_version_number%type
640 index by binary_integer;
641 -- define the local variables to be used
642 l_esd_tab l_ed_tab_type; -- effective start date table
643 l_eed_tab l_ed_tab_type; -- effective end date table
644 l_ovn_tab l_ovn_tab_type; -- object version number table
645 l_index binary_integer := 0;
646 l_ovn_index binary_integer;
647 l_correction_on_esd boolean;
648 l_initial_api_call boolean := true;
649 l_correction boolean;
650 l_update boolean;
651 l_update_override boolean;
652 l_update_change_insert boolean;
653 l_datetrack_update_mode varchar2(30);
654 l_effective_date date := trunc(sysdate);
655 l_object_version_number per_assignments_f.object_version_number%type;
656 l_effective_start_date per_assignments_f.effective_start_date%type;
657 l_effective_end_date per_assignments_f.effective_end_date%type;
658 --
659 cursor csr_paf is
660 select paf.effective_start_date
661 ,paf.effective_end_date
662 ,paf.object_version_number
663 from per_all_assignments_f paf -- 10/17/97 Changed
664 where paf.assignment_id = p_candidate_assignment_id
665 order by paf.effective_start_date;
666 --
667 begin
668 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
669 -- populate the pl/sql tables for the applicant assignment
670 for lcsr in csr_paf loop
671 l_index := l_index + 1;
672 l_esd_tab(l_index) := lcsr.effective_start_date;
673 l_eed_tab(l_index) := lcsr.effective_end_date;
674 l_ovn_tab(l_index) := lcsr.object_version_number;
675 end loop;
676 --
677 if l_index > 0 then
678 -- check to see if the minimum effective start date is the same as the
679 -- sysdate
680 if l_esd_tab(1) = l_effective_date then
681 -- the effective start date is the same as the sysdate therefore
682 -- we must move the date on because an applicant must have an 'ACTIVE_APL'
683 -- status for at least one day
684 l_effective_date := l_effective_date + 1;
685 end if;
686 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
687 -- the following section determines the datetrack mode to be used for
688 -- the initial API call
689 --
690 -- determine what datetrack modes are available as of the effective date
691 per_asg_shd.find_dt_upd_modes
692 (p_effective_date => l_effective_date
693 ,p_base_key_value => p_candidate_assignment_id
694 ,p_correction => l_correction
695 ,p_update => l_update
696 ,p_update_override => l_update_override
697 ,p_update_change_insert => l_update_change_insert);
698 --
699 if l_update then
700 -- as we can perform an UPDATE we must set the datetrack mode accordingly.
701 -- we can also assume that no future changes exist for this applicant
702 -- (otherwise we would not be able to perform an UPDATE)
703 l_datetrack_update_mode := 'UPDATE';
704 else
705 if l_correction then
706 l_correction_on_esd := false;
707 -- as CORRECTION is allowed therefore, determine if the effective
708 -- date is the same as an effective start date for the applicant
709 for i in 1..l_index loop
710 if l_esd_tab(i) = l_effective_date then
711 -- set the flag to indicate that an effective start date does exist
712 -- for the given effective date
713 l_correction_on_esd := true;
714 exit;
715 end if;
716 end loop;
717 --
718 if l_correction_on_esd then
719 -- perform a CORRECTION
720 l_datetrack_update_mode := 'CORRECTION';
721 else
722 -- perform a CHANGE_INSERT
723 l_datetrack_update_mode := 'UPDATE_CHANGE_INSERT';
724 end if;
725 end if;
726 end if;
727 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
728 -- process the initial API call and subsequent API calls (if required) to
729 -- ripple through any date effective CORRECTION's
730 --
731 loop
732 -- determine the object_version_number as of the effective date
733 for i in 1..l_index loop
734 if l_effective_date >= l_esd_tab(i) and
735 l_effective_date <= l_eed_tab(i) then
736 -- set the object version number
737 l_object_version_number := l_ovn_tab(i);
738 -- set the object version number index
739 l_ovn_index := i;
740 exit;
741 end if;
742 end loop;
743 --
744 begin
745 -- perform the status change
746 hr_assignment_api.offer_apl_asg
747 (p_effective_date => l_effective_date
748 ,p_datetrack_update_mode => l_datetrack_update_mode
749 ,p_assignment_id => p_candidate_assignment_id
750 ,p_object_version_number => l_object_version_number
751 ,p_assignment_status_type_id => p_status_type_id
752 ,p_effective_start_date => l_effective_start_date
753 ,p_effective_end_date => l_effective_end_date);
754 --
755 l_initial_api_call := false;
756 --
757 exception
758 when others then
759 -- the API call has failed. if the failure has ocurred on the initial
760 -- call we must let the workflow engine report the error.
761 -- if the API call has failed in the datetrack CORRECTION 'ripple'
762 -- mode we ignore the error and exit the loop.
763 if l_initial_api_call then
764 raise;
765 else
766 exit;
767 end if;
768 end;
769 if l_ovn_index < l_index then
770 -- future changes exist which we must ripple through as a date effective
771 -- CORRECTION
772 l_effective_date := l_esd_tab(l_ovn_index + 1);
773 l_datetrack_update_mode := 'CORRECTION';
774 else
775 exit;
776 end if;
777 end loop;
778 else
779 -- the assignment was not found as of the sysdate. this is a fatal error
780 -- which we must raise
781 fnd_message.set_name('PER', 'HR_ASG_DOES_NOT_EXIST');
782 hr_utility.raise_error;
783 end if;
784 -- commit any outstanding changes
785 -- commit;
786 --
787 end set_apl_status;
788 -- ----------------------------------------------------------------------------
789 -- |--------------------------< set_status_to_offer >-------------------------|
790 -- ----------------------------------------------------------------------------
791 --
792 -- This function just sets the applicant status to offer. It is called when
793 -- the offer is approved by the final approver. It is used in the Web Offer
794 -- workflow, 'Offer Letter'.
795 --
796 --
797 procedure set_status_to_offer
798 (p_candidate_assignment_id in per_assignments_f.assignment_id%type) is
799 begin
800 -- set applicant to default OFFER status
801 set_apl_status(p_candidate_assignment_id => p_candidate_assignment_id);
802 end set_status_to_offer;
803 -- ----------------------------------------------------------------------------
804 -- |--------------------------< set_status_to_sent >--------------------------|
805 -- ----------------------------------------------------------------------------
806 --
807 -- This function just sets the applicant status to sent. It is called when
808 -- the offer is mailed to the candidate. It is used in the Web Offer workflow,
809 -- 'Offer Letter'.
810 --
811 procedure set_status_to_sent
812 (p_candidate_assignment_id in per_assignments_f.assignment_id%type) is
813 begin
814 -- [CUSTOMIZE]
815 -- set applicant OFFER SENT status, this status must be set up in
816 -- per_assignment_status_types. As delivered, we have no 'sent'
817 -- status in core hrms, so we are not calling the procedure.
818 null;
819 -- set_apl_status(p_candidate_assignment_id => p_candidate_assignment_id
820 -- ,p_status_type_id => 6786);
821 end set_status_to_sent;
822 --
823 -- ----------------------------------------------------------------------------
824 -- |--------------------------< insert_attachment >----------------------------|
825 -- ----------------------------------------------------------------------------
826 --
827 procedure insert_attachment
828 (p_attachment_text in long default null
829 ,p_entity_name in varchar2 default null
830 ,p_pk1_value in varchar2 default null
831 ,p_attached_document_id out nocopy
832 fnd_attached_documents.attached_document_id%TYPE
833 ,p_document_id out nocopy
834 fnd_documents.document_id%TYPE
835 ,p_media_id out nocopy
836 fnd_documents_tl.media_id%TYPE
837 ,p_rowid out nocopy varchar2
838 ,p_login_person_id in number) is -- 10/14/97 Changed
839
840 -- [CUSTOMIZE]
841 -- Call fnd_attached_documents_pkg.insert_row api to insert into fnd_documents
842 -- table. If customer uses third party software to store the resume, modify
843 -- the code here.
844
845 l_rowid varchar2(50) default null;
846 l_media_id fnd_documents_tl.media_id%TYPE;
847 l_attached_document_id fnd_attached_documents.attached_document_id%TYPE
848 default null;
849 l_document_id fnd_documents.document_id%TYPE default null;
850 l_category_id fnd_document_categories.category_id%TYPE
851 default null;
852 l_datatype_id fnd_document_datatypes.datatype_id%TYPE default 2;
853 l_language varchar2(30) default 'AMERICAN';
854 l_seq_num fnd_attached_documents.seq_num%type;
855
856
857 cursor csr_get_seq_num is
858 select nvl(max(seq_num),0) + 10
859 from fnd_attached_documents
860 where entity_name = p_entity_name
861 and pk1_value = p_pk1_value
862 and pk2_value is null
863 and pk3_value is null
864 and pk4_value is null
865 and pk5_value is null;
866
867 cursor csr_get_category_id (csr_p_lang in varchar2) is
868 select category_id
869 from fnd_document_categories_tl
870 where language = csr_p_lang
871 and name = 'HR_RESUME'; -- updated for bug no:2533461
872 --
873
874 Begin
875 --
876 -- Get language
877 select userenv('LANG') into l_language from dual;
878 --
879 -- Get seq num
880 --
881 l_seq_num := 0;
882 open csr_get_seq_num;
883 fetch csr_get_seq_num into l_seq_num;
884 close csr_get_seq_num;
885 --
886 -- Get category ID
887 --
888 open csr_get_category_id (csr_p_lang => l_language);
889 fetch csr_get_category_id into l_category_id;
890 if csr_get_category_id%notfound then
891 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
892 hr_utility.raise_error;
893 end if;
894 close csr_get_category_id;
895 --
896 -- get sequence id for attached_document_id
897 select fnd_attached_documents_s.nextval
898 into l_attached_document_id
899 from sys.dual;
900
901 -- Insert document to fnd_documents_long_text
902 --
903 fnd_attached_documents_pkg.insert_row
904 (x_rowid => l_rowid
905 ,x_attached_document_id => l_attached_document_id
906 ,x_document_id => l_document_id
907 ,x_creation_date => trunc(sysdate)
908 ,x_created_by => p_login_person_id --10/14/97Chg
909 ,x_last_update_date => trunc(sysdate)
910 ,x_last_updated_by => p_login_person_id --10/14/97Chg
911 ,x_seq_num => l_seq_num
912 ,x_entity_name => p_entity_name
913 ,x_column1 => 'PERSON_ID'
914 ,x_pk1_value => p_pk1_value
915 ,x_pk2_value => null
916 ,x_pk3_value => null
917 ,x_pk4_value => null
918 ,x_pk5_value => null
919 ,x_automatically_added_flag => 'N'
920 ,x_datatype_id => l_datatype_id
921 ,x_category_id => l_category_id
922 ,x_security_type => 4
923 ,x_publish_flag =>'N'
924 ,x_usage_type =>'O'
925 ,x_language => l_language
926 ,x_media_id => l_media_id
927 ,x_doc_attribute_category => null
928 ,x_doc_attribute1 => null
929 ,x_doc_attribute2 => null
930 ,x_doc_attribute3 => null
931 ,x_doc_attribute4 => null
932 ,x_doc_attribute5 => null
933 ,x_doc_attribute6 => null
934 ,x_doc_attribute7 => null
935 ,x_doc_attribute8 => null
936 ,x_doc_attribute9 => null
937 ,x_doc_attribute10 => null
938 ,x_doc_attribute11 => null
939 ,x_doc_attribute12 => null
940 ,x_doc_attribute13 => null
941 ,x_doc_attribute14 => null
942 ,x_doc_attribute15 => null);
943 --
944
945 -- Now insert into fnd_documents_long_text using the media_id
946 -- generated from the above api call
947 --
948 insert into fnd_documents_long_text
949 (media_id
950 ,long_text)
951 values
952 (l_media_id
953 ,p_attachment_text);
954
955 p_attached_document_id := l_attached_document_id;
956 p_document_id := l_document_id;
957 p_media_id := l_media_id;
958 p_rowid := l_rowid;
959
960 EXCEPTION
961 When others then
962 p_attached_document_id := null;
963 p_document_id := null;
964 p_media_id := null;
965 p_rowid := null;
966 raise;
967 --
968 end insert_attachment;
969
970 -- ----------------------------------------------------------------------------
971 -- |--------------------------< update_attachment >----------------------------|
972 -- ----------------------------------------------------------------------------
973 --
974 procedure update_attachment
975 (p_attachment_text in long default null
976 ,p_entity_name in varchar2 default null
977 ,p_pk1_value in varchar2 default null
978 ,p_rowid in varchar2
979 ,p_login_person_id in number) is -- 10/14/97 Changed
980
981 -- [CUSTOMIZE]
982 -- Call fnd_attached_documents_pkg.update_row api to update fnd_documents
983 -- table. If customer uses third party software to store the resume, modify
984 -- the code here.
985
986 l_rowid varchar2(50);
987 l_language varchar2(30) default 'AMERICAN';
988
989 data_error exception;
990 --
991 -- -------------------------------------------------------------
992 -- Get the before update nullable fields so that we can
993 -- preserve the values entered in 10SC GUI after the update.
994 -- -------------------------------------------------------------
995 cursor csr_get_attached_doc is
996 select *
997 from fnd_attached_documents
998 where rowid = p_rowid;
999 --
1000 cursor csr_get_doc(csr_p_document_id in number) is
1001 select *
1002 from fnd_documents
1003 where document_id = csr_p_document_id;
1004 --
1005 cursor csr_get_doc_tl (csr_p_lang in varchar2
1006 ,csr_p_document_id in number) is
1007 select *
1008 from fnd_documents_tl
1009 where document_id = csr_p_document_id
1010 and language = csr_p_lang;
1011 --
1012 l_attached_doc_pre_upd csr_get_attached_doc%rowtype;
1013 l_doc_pre_upd csr_get_doc%rowtype;
1014 l_doc_tl_pre_upd csr_get_doc_tl%rowtype;
1015 --
1016 --
1017 Begin
1018 --
1019 -- Get language
1020 select userenv('LANG') into l_language from dual;
1021 --
1022 -- Get the before update nullable fields which are not used by the
1023 -- Web page to ensure the values are propagated.
1024 Open csr_get_attached_doc;
1025 fetch csr_get_attached_doc into l_attached_doc_pre_upd;
1026 IF csr_get_attached_doc%NOTFOUND THEN
1027 close csr_get_attached_doc;
1028 raise data_error;
1029 END IF;
1030
1031 Open csr_get_doc(l_attached_doc_pre_upd.document_id);
1032 fetch csr_get_doc into l_doc_pre_upd;
1033 IF csr_get_doc%NOTFOUND then
1034 close csr_get_doc;
1035 raise data_error;
1036 END IF;
1037
1038 Open csr_get_doc_tl (csr_p_lang => l_language
1039 ,csr_p_document_id => l_attached_doc_pre_upd.document_id);
1040 fetch csr_get_doc_tl into l_doc_tl_pre_upd;
1041 IF csr_get_doc_tl%NOTFOUND then
1042 close csr_get_doc_tl;
1043 raise data_error;
1044 END IF;
1045
1046 -- Now, lock the rows.
1047 fnd_attached_documents_pkg.lock_row
1048 (x_rowid => p_rowid
1049 ,x_attached_document_id =>
1050 l_attached_doc_pre_upd.attached_document_id
1051 ,x_document_id => l_doc_pre_upd.document_id
1052 ,x_seq_num => l_attached_doc_pre_upd.seq_num
1053 ,x_entity_name => l_attached_doc_pre_upd.entity_name
1054 ,x_column1 => l_attached_doc_pre_upd.column1
1055 ,x_pk1_value => l_attached_doc_pre_upd.pk1_value
1056 ,x_pk2_value => l_attached_doc_pre_upd.pk2_value
1057 ,x_pk3_value => l_attached_doc_pre_upd.pk3_value
1058 ,x_pk4_value => l_attached_doc_pre_upd.pk4_value
1059 ,x_pk5_value => l_attached_doc_pre_upd.pk5_value
1060 ,x_automatically_added_flag =>
1061 l_attached_doc_pre_upd.automatically_added_flag
1062 ,x_attribute_category =>
1063 l_attached_doc_pre_upd.attribute_category
1064 ,x_attribute1 => l_attached_doc_pre_upd.attribute1
1065 ,x_attribute2 => l_attached_doc_pre_upd.attribute2
1066 ,x_attribute3 => l_attached_doc_pre_upd.attribute3
1067 ,x_attribute4 => l_attached_doc_pre_upd.attribute4
1068 ,x_attribute5 => l_attached_doc_pre_upd.attribute5
1069 ,x_attribute6 => l_attached_doc_pre_upd.attribute6
1070 ,x_attribute7 => l_attached_doc_pre_upd.attribute7
1071 ,x_attribute8 => l_attached_doc_pre_upd.attribute8
1072 ,x_attribute9 => l_attached_doc_pre_upd.attribute9
1073 ,x_attribute10 => l_attached_doc_pre_upd.attribute10
1074 ,x_attribute11 => l_attached_doc_pre_upd.attribute11
1075 ,x_attribute12 => l_attached_doc_pre_upd.attribute12
1076 ,x_attribute13 => l_attached_doc_pre_upd.attribute13
1077 ,x_attribute14 => l_attached_doc_pre_upd.attribute14
1078 ,x_attribute15 => l_attached_doc_pre_upd.attribute15
1079 ,x_datatype_id => l_doc_pre_upd.datatype_id
1080 ,x_category_id => l_doc_pre_upd.category_id
1081 ,x_security_type => l_doc_pre_upd.security_type
1082 ,x_security_id => l_doc_pre_upd.security_id
1083 ,x_publish_flag => l_doc_pre_upd.publish_flag
1084 ,x_image_type => l_doc_pre_upd.image_type
1085 ,x_storage_type => l_doc_pre_upd.storage_type
1086 ,x_usage_type => l_doc_pre_upd.usage_type
1087 ,x_start_date_active => l_doc_pre_upd.start_date_active
1088 ,x_end_date_active => l_doc_pre_upd.end_date_active
1089 ,x_language => l_doc_tl_pre_upd.language
1090 ,x_description => l_doc_tl_pre_upd.description
1091 ,x_file_name => l_doc_tl_pre_upd.file_name
1092 ,x_media_id => l_doc_tl_pre_upd.media_id
1093 ,x_doc_attribute_category =>
1094 l_doc_tl_pre_upd.doc_attribute_category
1095 ,x_doc_attribute1 => l_doc_tl_pre_upd.doc_attribute1
1096 ,x_doc_attribute2 => l_doc_tl_pre_upd.doc_attribute2
1097 ,x_doc_attribute3 => l_doc_tl_pre_upd.doc_attribute3
1098 ,x_doc_attribute4 => l_doc_tl_pre_upd.doc_attribute4
1099 ,x_doc_attribute5 => l_doc_tl_pre_upd.doc_attribute5
1100 ,x_doc_attribute6 => l_doc_tl_pre_upd.doc_attribute6
1101 ,x_doc_attribute7 => l_doc_tl_pre_upd.doc_attribute7
1102 ,x_doc_attribute8 => l_doc_tl_pre_upd.doc_attribute8
1103 ,x_doc_attribute9 => l_doc_tl_pre_upd.doc_attribute9
1104 ,x_doc_attribute10 => l_doc_tl_pre_upd.doc_attribute10
1105 ,x_doc_attribute11 => l_doc_tl_pre_upd.doc_attribute11
1106 ,x_doc_attribute12 => l_doc_tl_pre_upd.doc_attribute12
1107 ,x_doc_attribute13 => l_doc_tl_pre_upd.doc_attribute13
1108 ,x_doc_attribute14 => l_doc_tl_pre_upd.doc_attribute14
1109 ,x_doc_attribute15 => l_doc_tl_pre_upd.doc_attribute15);
1110
1111
1112 -- Update document to fnd_attached_documents, fnd_documents,
1113 -- fnd_documents_tl and fnd_documents_long_text
1114 --
1115 fnd_attached_documents_pkg.update_row
1116 (x_rowid => p_rowid
1117 ,x_attached_document_id =>
1118 l_attached_doc_pre_upd.attached_document_id
1119 ,x_document_id => l_doc_pre_upd.document_id
1120 ,x_last_update_date => trunc(sysdate)
1121 ,x_last_updated_by => p_login_person_id --10/14/97chg
1122 ,x_seq_num => l_attached_doc_pre_upd.seq_num
1123 ,x_entity_name => p_entity_name
1124 ,x_column1 => 'PERSON_ID'
1125 ,x_pk1_value => p_pk1_value
1126 ,x_pk2_value => l_attached_doc_pre_upd.pk2_value
1127 ,x_pk3_value => l_attached_doc_pre_upd.pk3_value
1128 ,x_pk4_value => l_attached_doc_pre_upd.pk4_value
1129 ,x_pk5_value => l_attached_doc_pre_upd.pk5_value
1130 ,x_automatically_added_flag =>
1131 l_attached_doc_pre_upd.automatically_added_flag
1132 ,x_attribute_category =>
1133 l_attached_doc_pre_upd.attribute_category
1134 ,x_attribute1 => l_attached_doc_pre_upd.attribute1
1135 ,x_attribute2 => l_attached_doc_pre_upd.attribute2
1136 ,x_attribute3 => l_attached_doc_pre_upd.attribute3
1137 ,x_attribute4 => l_attached_doc_pre_upd.attribute4
1138 ,x_attribute5 => l_attached_doc_pre_upd.attribute5
1139 ,x_attribute6 => l_attached_doc_pre_upd.attribute6
1140 ,x_attribute7 => l_attached_doc_pre_upd.attribute7
1141 ,x_attribute8 => l_attached_doc_pre_upd.attribute8
1142 ,x_attribute9 => l_attached_doc_pre_upd.attribute9
1143 ,x_attribute10 => l_attached_doc_pre_upd.attribute10
1144 ,x_attribute11 => l_attached_doc_pre_upd.attribute11
1145 ,x_attribute12 => l_attached_doc_pre_upd.attribute12
1146 ,x_attribute13 => l_attached_doc_pre_upd.attribute13
1147 ,x_attribute14 => l_attached_doc_pre_upd.attribute14
1148 ,x_attribute15 => l_attached_doc_pre_upd.attribute15
1149 /* columns necessary for creating a document on the fly */
1150 ,x_datatype_id => l_doc_pre_upd.datatype_id
1151 ,x_category_id => l_doc_pre_upd.category_id
1152 ,x_security_type => l_doc_pre_upd.security_type
1153 ,x_security_id => l_doc_pre_upd.security_id
1154 ,x_publish_flag => l_doc_pre_upd.publish_flag
1155 ,x_image_type => l_doc_pre_upd.image_type
1156 ,x_storage_type => l_doc_pre_upd.storage_type
1157 ,x_usage_type => l_doc_pre_upd.usage_type
1158 ,x_start_date_active => trunc(sysdate)
1159 ,x_end_date_active => l_doc_pre_upd.end_date_active
1160 ,x_language => l_language
1161 ,x_description => l_doc_tl_pre_upd.description
1162 ,x_file_name => l_doc_tl_pre_upd.file_name
1163 ,x_media_id => l_doc_tl_pre_upd.media_id
1164 ,x_doc_attribute_category =>
1165 l_doc_tl_pre_upd.doc_attribute_category
1166 ,x_doc_attribute1 => l_doc_tl_pre_upd.doc_attribute1
1167 ,x_doc_attribute2 => l_doc_tl_pre_upd.doc_attribute2
1168 ,x_doc_attribute3 => l_doc_tl_pre_upd.doc_attribute3
1169 ,x_doc_attribute4 => l_doc_tl_pre_upd.doc_attribute4
1170 ,x_doc_attribute5 => l_doc_tl_pre_upd.doc_attribute5
1171 ,x_doc_attribute6 => l_doc_tl_pre_upd.doc_attribute6
1172 ,x_doc_attribute7 => l_doc_tl_pre_upd.doc_attribute7
1173 ,x_doc_attribute8 => l_doc_tl_pre_upd.doc_attribute8
1174 ,x_doc_attribute9 => l_doc_tl_pre_upd.doc_attribute9
1175 ,x_doc_attribute10 => l_doc_tl_pre_upd.doc_attribute10
1176 ,x_doc_attribute11 => l_doc_tl_pre_upd.doc_attribute11
1177 ,x_doc_attribute12 => l_doc_tl_pre_upd.doc_attribute12
1178 ,x_doc_attribute13 => l_doc_tl_pre_upd.doc_attribute13
1179 ,x_doc_attribute14 => l_doc_tl_pre_upd.doc_attribute14
1180 ,x_doc_attribute15 => l_doc_tl_pre_upd.doc_attribute15);
1181
1182 -- Now update the long text table
1183 update fnd_documents_long_text
1184 set long_text = p_attachment_text
1185 where media_id = l_doc_tl_pre_upd.media_id;
1186
1187 EXCEPTION
1188 when others then
1189 raise;
1190 --
1191 End update_attachment;
1192
1193 -- ----------------------------------------------------------------------------
1194 -- |--------------------------< get_attachment >------------------------------|
1195 -- ----------------------------------------------------------------------------
1196 --
1197 procedure get_attachment
1198 (p_attachment_text out nocopy long
1199 ,p_entity_name in varchar2 default null
1200 ,p_pk1_value in varchar2 default null
1201 ,p_effective_date in varchar2
1202 ,p_attached_document_id out nocopy
1203 fnd_attached_documents.attached_document_id%TYPE
1204 ,p_document_id out nocopy fnd_documents.document_id%TYPE
1205 ,p_media_id out nocopy fnd_documents_tl.media_id%TYPE
1206 ,p_rowid out nocopy varchar2
1207 ,p_category_id out nocopy fnd_documents.category_id%type
1208 ,p_seq_num out nocopy fnd_attached_documents.seq_num%type) is
1209
1210 -- [CUSTOMIZE]
1211 -- Call fnd_attached_documents, fnd_documents_tl and
1212 -- fnd_documents_long_text tables. If customer uses third party
1213 -- software to store the resumes, modify the code here.
1214
1215 l_attached_document_id fnd_attached_documents.attached_document_id%TYPE
1216 default null;
1217 l_document_id fnd_documents.document_id%TYPE default null;
1218 l_media_id fnd_documents_tl.media_id%TYPE default null;
1219 l_attachment_text long default null;
1220 l_rowid varchar2(50) default null;
1221 l_category_id fnd_documents.category_id%type default null;
1222 l_language varchar2(30) default 'AMERICAN';
1223 l_seq_num fnd_attached_documents.seq_num%type default null;
1224 l_update_date date default null;
1225
1226 cursor csr_get_category_id (csr_p_lang in varchar2) is
1227 select category_id
1228 from fnd_document_categories_tl
1229 where language = csr_p_lang
1230 and name = 'HR_RESUME'; -- updated for bug no:2533461
1231
1232 ------------------------------------------------------------------------------
1233 -- Jan. 11, 2001 Bug Fix 1576603:
1234 -- In closer look at the FND api, the fnd_attached_documents_pkg.insert_row
1235 -- (AFAKAADB.pls) always sets start_date_active and end_date_active to null in
1236 -- call to fnd_documents_pkg.insert_row (AFAKADCB.pls). Therefore, the
1237 -- comparision of p_effective_date to start_date_active and end_date_active in
1238 -- the cursor is futile. Therefore, remove the p_effective_date comparision
1239 -- in the cursor and sort the records by creation date, last_update_date so
1240 -- that the most current resume will be displayed.
1241 ------------------------------------------------------------------------------
1242 cursor csr_attached_documents (csr_p_cat_id in number) is
1243 select fatd.rowid, fatd.attached_document_id, fatd.document_id, fatd.seq_num
1244 ,fd.last_update_date
1245 from fnd_attached_documents fatd
1246 ,fnd_documents fd
1247 where fd.category_id = csr_p_cat_id
1248 and fatd.entity_name = p_entity_name
1249 and fatd.pk1_value = p_pk1_value
1250 and fatd.document_id = fd.document_id
1251 order by fd.creation_date desc
1252 ,fd.last_update_date desc; -- retrieve the one updated the last
1253
1254 cursor csr_documents_tl (csr_p_document_id in number) is
1255 select media_id
1256 from fnd_documents_tl
1257 where document_id = csr_p_document_id;
1258
1259 cursor csr_documents_long_text (csr_p_media_id in number) is
1260 select long_text
1261 from fnd_documents_long_text
1262 where media_id = csr_p_media_id;
1263
1264 Begin
1265 --
1266 -- Get language
1267 select userenv('LANG') into l_language from dual;
1268 --
1269 -- -------------------------------------------------------------------------
1270 -- Retrieving a resume requires 4 steps:
1271 -- 1) Get Category ID.
1272 -- 2) Get the attached_document_id, document_id and other fields from
1273 -- the table join of fnd_attached_documents and fnd_documents. The
1274 -- result set can have more than 1 row and is sorted by descending
1275 -- order of the last_update_date. So, if there are multipe resumes
1276 -- returned (which could be possible because a user in 10SC Person
1277 -- form can add an attachment with the category of 'Resume'. When
1278 -- that happens, we only want the one which is updated most recently.
1279 -- 3) Use the document_id obtained from the 1st record of step 2 to
1280 -- get the media_id from fnd_documents_tl.
1281 -- 4) Use the media_id from step 3 to obtain the resume text from
1282 -- fnd_documents_long_text.
1283 -- -------------------------------------------------------------------------
1284 --
1285 -- -------------------------------------------------------------------------
1286 -- 1) Get Category ID.
1287 -- -------------------------------------------------------------------------
1288 open csr_get_category_id (csr_p_lang => l_language);
1289 fetch csr_get_category_id into l_category_id;
1290 if csr_get_category_id%notfound then
1291 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
1292 hr_utility.raise_error;
1293 end if;
1294 close csr_get_category_id;
1295 --
1296 -- -------------------------------------------------------------------------
1297 -- 2) Get attached_document_id, document_id.
1298 -- -------------------------------------------------------------------------
1299 --
1300 Open csr_attached_documents (csr_p_cat_id => l_category_id);
1301 fetch csr_attached_documents into l_rowid, l_attached_document_id,
1302 l_document_id, l_seq_num, l_update_date;
1303
1304 IF csr_attached_documents%NOTFOUND THEN
1305 close csr_attached_documents;
1306 ELSE
1307 open csr_documents_tl(csr_p_document_id => l_document_id);
1308 fetch csr_documents_tl into l_media_id;
1309 IF csr_documents_tl%NOTFOUND THEN
1310 close csr_attached_documents;
1311 close csr_documents_tl;
1312 raise hr_utility.hr_error;
1313 ELSE
1314 open csr_documents_long_text(csr_p_media_id => l_media_id);
1315 fetch csr_documents_long_text into l_attachment_text;
1316 IF csr_documents_long_text%NOTFOUND THEN
1317 close csr_attached_documents;
1318 close csr_documents_tl;
1319 close csr_documents_long_text;
1320 raise hr_utility.hr_error;
1321 ELSE
1322 close csr_attached_documents;
1323 close csr_documents_tl;
1324 close csr_documents_long_text;
1325 END IF;
1326 END IF;
1327 END IF;
1328
1329 p_attachment_text := l_attachment_text;
1330 p_attached_document_id := l_attached_document_id;
1331 p_document_id := l_document_id;
1332 p_media_id := l_media_id;
1333 p_rowid := l_rowid;
1334 p_category_id := l_category_id;
1335 p_seq_num := l_seq_num;
1336
1337 exception
1338 when hr_utility.hr_error THEN
1339 p_attachment_text := null;
1340 p_attached_document_id := null;
1341 p_document_id := null;
1342 p_media_id := null;
1343 p_rowid := null;
1344 p_category_id := null;
1345 p_seq_num := null;
1346 raise;
1347
1348 when others then
1349 p_attachment_text := null;
1350 p_attached_document_id := null;
1351 p_document_id := null;
1352 p_media_id := null;
1353 p_rowid := null;
1354 p_category_id := null;
1355 p_seq_num := null;
1356 --hr_java_script_web.alert(sqlerrm||' '||sqlcode);
1357 --hr_util_web.standard_close;
1358 raise hr_util_web.g_error_handled;
1359
1360 end get_attachment;
1361
1362 -- ----------------------------------------------------------------------------
1363 -- |--------------------------< validate_phone_format >-----------------------|
1364 -- ----------------------------------------------------------------------------
1365 -- Name: validate_phone_format
1366 --
1367 -- Function: This procedure validates a phone number format by country code.
1368 -- This procedure is expected to be modified by customers to suit
1369 -- their needs in different ways of formatting a phone number.
1370 --
1371 -- Output: p_phone_num_out - Customers need to set this output parameter to
1372 -- a non-null value, formatted phone number which
1373 -- will be used to display in the offer letter.
1374 --
1375 -- p_phone_format_err - Customers need to set this parameter to 'Y' if
1376 -- there is an error. Otherwise, set it to 'N'
1377 -- if the phone passes their validation code.
1378 -- ----------------------------------------------------------------------------
1379 --
1380 procedure validate_phone_format(p_phone_num_in in varchar2
1381 ,p_country_code in varchar2 default 'US'
1382 ,p_phone_num_out out nocopy varchar2
1383 ,p_phone_format_err out nocopy varchar2) IS
1384
1385 Begin
1386 --
1387 -- Customers need to put in code for their own validations. If no
1388 -- code is put in here, the product validation code in
1389 -- hr_offer_util_web.validate_phone_format will be used.
1390 -- When validate, do:
1391 -- IF p_country_code = 'US' THEN
1392 -- . . . .
1393 -- ELSE
1394 -- . . . .
1395 -- END IF;
1396 --
1397 -- --------------------------------------------------------------------
1398 -- NOTE: For a list of country_code, please refer to
1399 -- fnd_territories_vl.territory_code.
1400 -- --------------------------------------------------------------------
1401 p_phone_num_out := null;
1402 p_phone_format_err := null;
1403 --
1404 END validate_phone_format;
1405
1406 end hr_offer_custom;
[Home] [Help]