1 package body hr_approval_custom as
2 /* $Header: hrapcuwf.pkb 120.9 2008/04/15 05:51:32 rachakra ship $ */
3 -- ---------------------------------------------------------------------------
4 -- private package global declarations
5 -- ---------------------------------------------------------------------------
6 g_package constant varchar2(31) := 'hr_approval_custom.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |------------------------------< get_routing_details1 >--------------------|
10 -- ----------------------------------------------------------------------------
11 function get_routing_details1
12 (p_person_id in per_people_f.person_id%type)
13 return per_people_f.person_id%type is
14 begin
15 -- [CUSTOMIZE]
16 -- This function will need to be modified by the end customer.
17 return(null);
18 end get_routing_details1;
19 --
20 -- ----------------------------------------------------------------------------
21 -- |------------------------------< get_routing_details2 >--------------------|
22 -- ----------------------------------------------------------------------------
23 function get_routing_details2
24 (p_person_id in per_people_f.person_id%type)
25 return per_people_f.person_id%type is
26 begin
27 -- [CUSTOMIZE]
28 -- This function will need to be modified by the end customer.
29 return(null);
30 end get_routing_details2;
31 --
32 -- ----------------------------------------------------------------------------
33 -- |------------------------------< get_routing_details3 >--------------------|
34 -- ----------------------------------------------------------------------------
35 function get_routing_details3
36 (p_person_id in per_people_f.person_id%type)
37 return per_people_f.person_id%type is
38 begin
39 -- [CUSTOMIZE]
40 -- This function will need to be modified by the end customer.
41 return(null);
42 end get_routing_details3;
43 -- ----------------------------------------------------------------------------
47 (p_person_id in per_people_f.person_id%type)
44 -- |------------------------------< get_routing_details4 >--------------------|
45 -- ----------------------------------------------------------------------------
46 function get_routing_details4
48 return per_people_f.person_id%type is
49 begin
50 -- [CUSTOMIZE]
51 -- This function will need to be modified by the end customer.
52 return(null);
53 end get_routing_details4;
54 --
55 -- ----------------------------------------------------------------------------
56 -- |------------------------------< get_routing_details5 >--------------------|
57 -- ----------------------------------------------------------------------------
58 function get_routing_details5
59 (p_person_id in per_people_f.person_id%type)
60 return per_people_f.person_id%type is
61 begin
62 -- [CUSTOMIZE]
63 -- This function will need to be modified by the end customer.
64 return(null);
65 end get_routing_details5;
66 -- ----------------------------------------------------------------------------
67 -- |--------------------------< check_final_approver >------------------------|
68 -- ----------------------------------------------------------------------------
69 function check_final_approver
70 (p_forward_to_person_id in per_people_f.person_id%type
71 ,p_person_id in per_people_f.person_id%type)
72 return varchar2 is
73 --
74 cursor csr_pa(l_effective_date in date) is
75 SELECT paf.person_id
76 FROM per_all_assignments_f paf START WITH paf.person_id = p_person_id
77 AND paf.primary_flag = 'Y'
78 AND paf.assignment_type IN('E', 'C')
79 AND l_effective_date BETWEEN paf.effective_start_date
80 AND paf.effective_end_date
81 CONNECT BY PRIOR paf.supervisor_id = paf.person_id
82 AND paf.primary_flag = 'Y'
83 AND paf.assignment_type IN('E', 'C')
84 AND l_effective_date BETWEEN paf.effective_start_date
85 AND paf.effective_end_date;
86
87
88 --
89 l_person_id per_people_f.person_id%type := null;
90 l_employee per_people_f.person_id%type := null;
91 --
92 begin
93 -- check if the start of the chain is a valid employee
94 begin
95 select ppf.person_id
96 into l_employee
97 from per_all_people_f ppf
98 where ppf.person_id = p_person_id
99 and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
100 and (ppf.current_employee_flag = 'Y' Or ppf.current_npw_flag = 'Y') ;
101 exception
102 when no_data_found then
103 hr_utility.set_message(800,'HR_INVALID_PERSON_ID');
104 hr_utility.set_message_token('PERSON_ID', p_person_id);
105 hr_utility.raise_error;
106 end;
107
108 -- loop through each row. the rows are returned in an order which makes
109 -- the last row selected the top most node of the chain.
110 for lcsr in csr_pa(trunc(sysdate)) loop
111 -- set the l_person_id variable to the row fetched
112 l_person_id := lcsr.person_id;
113 -- check if the l_person_id is valid employee
114 begin
115 select ppf.person_id
116 into l_employee
117 from per_all_people_f ppf
118 where ppf.person_id = l_person_id
119 and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
120 and (ppf.current_employee_flag = 'Y' Or ppf.current_npw_flag = 'Y');
121 exception
122 when no_data_found then
123 hr_utility.set_message(800,'HR_INVALID_PERSON_ID');
124 hr_utility.set_message_token('PERSON_ID', l_person_id);
125 hr_utility.raise_error;
126 end;
127 end loop;
128 if p_forward_to_person_id = l_person_id then
129 ------------add extra check to block auto approval---------------
130 declare
131 ed_date per_all_assignments_f.effective_end_date%type;
132 l_disp_person_id per_all_assignments_f.person_id%type;
133 begin
134
135 select nvl(max(ppf.EFFECTIVE_END_DATE),sysdate+10)
136 into ed_date
137 from per_all_people_f ppf where ppf.person_id in (
138 select paf1.supervisor_id
139 from per_all_assignments_f paf1
140 where paf1.primary_flag = 'Y'
141 and paf1.assignment_type in ('E','C')
142 and paf1.person_id = l_person_id
143 and paf1.supervisor_id is not null
144 and paf1.EFFECTIVE_END_DATE = ( select max(paf.EFFECTIVE_END_DATE)
145 from per_all_assignments_f paf
146 where paf.primary_flag = 'Y'
147 and paf.assignment_type in ('E','C')
148 and paf.person_id = l_person_id
149 )
150 )
151 and (ppf.current_employee_flag = 'Y' Or ppf.current_npw_flag = 'Y');
152
153
154 if ed_date < trunc(sysdate) then
155 select distinct paf1.supervisor_id
156 into l_disp_person_id
157 from per_all_assignments_f paf1
158 where paf1.primary_flag = 'Y'
159 and paf1.assignment_type in ('E','C')
160 and paf1.person_id = l_person_id
161 and paf1.supervisor_id is not null
162 and paf1.EFFECTIVE_END_DATE = ( select max(paf.EFFECTIVE_END_DATE)
163 from per_all_assignments_f paf
164 where paf.primary_flag = 'Y'
165 and paf.assignment_type in ('E','C')
166 and paf.person_id = l_person_id
167 );
168
169 hr_utility.set_message(800,'HR_INVALID_PERSON_ID');
170 hr_utility.set_message_token('PERSON_ID', l_disp_person_id);
174 when no_data_found then
171 hr_utility.raise_error;
172 end if;
173 exception
175 raise;
176 when others then
177 raise;
178 end;
179 ------------add extra check to block auto approval---------------
180 return('Y');
181 else
182 return('N');
183 end if;
184 exception
185 when others then
186 raise;
187 --
188 end check_final_approver;
189 -- ----------------------------------------------------------------------------
190 -- |--------------------------< check_final_payroll_notifier >----------------|
191 -- ----------------------------------------------------------------------------
192 function check_final_payroll_notifier
193 (p_forward_to_person_id in per_people_f.person_id%type
194 ,p_person_id in per_people_f.person_id%type)
195 return varchar2 is
196 begin
197 -- [CUSTOMIZE]
198 return('Y');
199 end check_final_payroll_notifier;
200 -- ----------------------------------------------------------------------------
201 -- |-----------------------------< get_next_approver >------------------------|
202 -- ----------------------------------------------------------------------------
203 function get_next_approver
204 (p_person_id in per_people_f.person_id%type)
205 return per_people_f.person_id%type is
206 --
207 cursor csr_pa(l_effective_date in date
208 ,l_in_person_id in per_people_f.person_id%type) is
209 /*
210 -- Modified the cursor to support Contingent Worker
211 -- Fix for bug#2949844
212 select ppf.person_id
213 from per_all_assignments_f paf
214 -- fix for bug # 1677216
215 --,per_people_f ppf
216 ,per_all_people_f ppf
217 where paf.person_id = l_in_person_id
218 and paf.primary_flag = 'Y'
219 and l_effective_date
220 between paf.effective_start_date
221 and paf.effective_end_date
222 and ppf.person_id = paf.supervisor_id
223 and ppf.current_employee_flag = 'Y'
224 and l_effective_date
225 between ppf.effective_start_date
226 and ppf.effective_end_date;
227 */
228 -- modified cursor to handle the issues reported
229 -- in bug#3007859
230 select ppf.person_id
231 from per_all_assignments_f paf
232 ,per_all_people_f ppf
233 where paf.person_id = l_in_person_id
234 and paf.primary_flag = 'Y'
235 and trunc(sysdate)
236 between paf.effective_start_date
237 and paf.effective_end_date
238 and paf.assignment_type in ('E','C')
239 and paf.assignment_status_type_id not in
240 (select assignment_status_type_id
241 from per_assignment_status_types
242 where per_system_status = 'TERM_ASSIGN' and business_group_id=paf.business_group_id)
243 and ppf.person_id = paf.supervisor_id
244 and (ppf.current_employee_flag = 'Y' Or ppf.current_npw_flag = 'Y')
245 and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date;
246 --
247
248 --
249 l_out_person_id per_people_f.person_id%type default null;
250 --
251 begin
252 -- [CUSTOMIZE]
253 -- open the candidate select cursor
254 open csr_pa(trunc(sysdate), p_person_id);
255 -- fetch the candidate details
256 fetch csr_pa into l_out_person_id;
257 if csr_pa%notfound then
258 -- if the cursor does not return a row then we must set the out
259 -- parameter to null
260 l_out_person_id := null;
261 end if;
262 -- close the cursor
263 close csr_pa;
264 return(l_out_person_id);
265 end get_next_approver;
266
267 -- ----------------------------------------------------------------------------
268 -- |-----------------------------< get_next_payroll_notifier >----------------|
269 -- ----------------------------------------------------------------------------
270 function get_next_payroll_notifier
271 (p_person_id in per_people_f.person_id%type)
272 return per_people_f.person_id%type is
273 begin
274 -- [CUSTOMIZE]
275 return null;
276 end get_next_payroll_notifier;
277 -- ------------------------------------------------------------------------
278 -- |------------------------------< get_URL1 >-----------------------------|
279 -- ------------------------------------------------------------------------
280 function get_URL1 return varchar2 is
281 --
282 -- Declare and intialise the URL to display the review approval page
283 l_url varchar2(2000) := NULL;
284 begin
285 -- [CUSTOMIZE]
286 -- This function will need to be modified by the end customer.
287 return(l_url);
288 end get_URL1;
289 -- ------------------------------------------------------------------------
290 -- |------------------------------< get_URL2 >-----------------------------|
291 -- ------------------------------------------------------------------------
292 function get_URL2 return varchar2 is
293 --
294 -- Declare and intialise the URL to display the view and resubmit URL
295 --
296 l_url varchar2(2000) := NULL;
297 begin
298 -- [CUSTOMIZE]
299 -- This function will need to be modified by the end customer.
300 return(l_url);
301 end get_URL2;
302 -- ------------------------------------------------------------------------
306 --
303 -- |------------------------------< get_URL3 >-----------------------------|
304 -- ------------------------------------------------------------------------
305 function get_URL3 return varchar2 is
307 -- Declare and intialise the URL
308 l_url varchar2(2000) := NULL;
309 begin
310 -- [CUSTOMIZE]
311 -- This function will need to be modified by the end customer.
312 return(l_url);
313 end get_URL3;
314 -- ------------------------------------------------------------------------
315 -- |------------------------------< get_URL4 >-----------------------------|
316 -- ------------------------------------------------------------------------
317 function get_URL4 return varchar2 is
318 --
319 -- Declare and intialise the URL
320 l_url varchar2(2000) := NULL;
321 begin
322 -- [CUSTOMIZE]
323 -- This function will need to be modified by the end customer.
324 return(l_url);
325 end get_URL4;
326 -- ------------------------------------------------------------------------
327 -- |------------------------------< get_URL5 >-----------------------------|
328 -- ------------------------------------------------------------------------
329 function get_URL5 return varchar2 is
330 --
331 -- Declare and intialise the URL
332 l_url varchar2(2000) := NULL;
333 begin
334 -- [CUSTOMIZE]
335 -- This function will need to be modified by the end customer.
336 return(l_url);
337 end get_URL5;
338 -- ------------------------------------------------------------------------
339 -- |------------------------------< get_URL6 >-----------------------------|
340 -- ------------------------------------------------------------------------
341 function get_URL6 return varchar2 is
342 --
343 -- Declare and intialise the URL
344 l_url varchar2(2000) := NULL;
345 begin
346 -- [CUSTOMIZE]
347 -- This function will need to be modified by the end customer.
348 return(l_url);
349 end get_URL6;
350 -- ------------------------------------------------------------------------
351 -- |------------------------------< get_URL7 >-----------------------------|
352 -- ------------------------------------------------------------------------
353 function get_URL7 return varchar2 is
354 --
355 -- Declare and intialise the URL
356 l_url varchar2(2000) := NULL;
357 begin
358 -- [CUSTOMIZE]
359 -- This function will need to be modified by the end customer.
360 return(l_url);
361 end get_URL7;
362 -- ------------------------------------------------------------------------
363 -- |------------------------------< get_URL8 >-----------------------------|
364 -- ------------------------------------------------------------------------
365 function get_URL8 return varchar2 is
366 --
367 -- Declare and intialise the URL
368 l_url varchar2(2000) := NULL;
369 begin
370 -- [CUSTOMIZE]
371 -- This function will need to be modified by the end customer.
372 return(l_url);
373 end get_URL8;
374 -- ------------------------------------------------------------------------
375 -- |------------------------------< get_URL9 >-----------------------------|
376 -- ------------------------------------------------------------------------
377 function get_URL9 return varchar2 is
378 --
379 -- Declare and intialise the URL
380 l_url varchar2(2000) := NULL;
381 begin
382 -- [CUSTOMIZE]
383 -- This function will need to be modified by the end customer.
384 return(l_url);
385 end get_URL9;
386 -- ------------------------------------------------------------------------
387 -- |------------------------------< get_URL10 >-----------------------------|
388 -- ------------------------------------------------------------------------
389 function get_URL10 return varchar2 is
390 --
391 -- Declare and intialise the URL
392 l_url varchar2(2000) := NULL;
393 begin
394 -- [CUSTOMIZE]
395 -- This function will need to be modified by the end customer.
396 return(l_url);
397 end get_URL10;
398 -- ------------------------------------------------------------------------
399 -- |------------------------------< get_URL11 >-----------------------------|
400 -- ------------------------------------------------------------------------
401 function get_URL11 return varchar2 is
402 --
403 -- Declare and intialise the URL
404 l_url varchar2(2000) := NULL;
405 begin
406 -- [CUSTOMIZE]
407 -- This function will need to be modified by the end customer.
408 return(l_url);
409 end get_URL11;
410 -- ------------------------------------------------------------------------
411 -- |------------------------------< get_URL12 >-----------------------------|
412 -- ------------------------------------------------------------------------
413 function get_URL12 return varchar2 is
414 --
415 -- Declare and intialise the URL
416 l_url varchar2(2000) := NULL;
417 begin
418 -- [CUSTOMIZE]
419 -- This function will need to be modified by the end customer.
420 return(l_url);
421 end get_URL12;
422 -- ------------------------------------------------------------------------
423 -- |------------------------------< get_URL13 >-----------------------------|
424 -- ------------------------------------------------------------------------
425 function get_URL13 return varchar2 is
426 --
427 -- Declare and intialise the URL
428 l_url varchar2(2000) := NULL;
429 begin
430 -- [CUSTOMIZE]
431 -- This function will need to be modified by the end customer.
432 return(l_url);
433 end get_URL13;
434 -- ------------------------------------------------------------------------
435 -- |------------------------------< get_URL14 >-----------------------------|
436 -- ------------------------------------------------------------------------
437 function get_URL14 return varchar2 is
438 --
439 -- Declare and intialise the URL
440 l_url varchar2(2000) := NULL;
441 begin
442 -- [CUSTOMIZE]
443 -- This function will need to be modified by the end customer.
444 return(l_url);
445 end get_URL14;
446 -- ------------------------------------------------------------------------
447 -- |------------------------------< get_URL15 >-----------------------------|
448 -- ------------------------------------------------------------------------
449 function get_URL15 return varchar2 is
450 --
451 -- Declare and intialise the URL
452 l_url varchar2(2000) := NULL;
453 begin
454 -- [CUSTOMIZE]
455 -- This function will need to be modified by the end customer.
456 return(l_url);
457 end get_URL15;
458 -- ------------------------------------------------------------------------
459 -- |------------------------------< get_URL16 >-----------------------------|
460 -- ------------------------------------------------------------------------
461 function get_URL16 return varchar2 is
462 --
463 -- Declare and intialise the URL
464 l_url varchar2(2000) := NULL;
465 begin
466 -- [CUSTOMIZE]
467 -- This function will need to be modified by the end customer.
468 return(l_url);
469 end get_URL16;
470 -- ------------------------------------------------------------------------
471 -- |------------------------------< get_URL17 >-----------------------------|
472 -- ------------------------------------------------------------------------
473 function get_URL17 return varchar2 is
474 --
475 -- Declare and intialise the URL
476 l_url varchar2(2000) := NULL;
477 begin
478 -- [CUSTOMIZE]
479 -- This function will need to be modified by the end customer.
480 return(l_url);
481 end get_URL17;
482 -- ------------------------------------------------------------------------
483 -- |------------------------------< get_URL18 >-----------------------------|
484 -- ------------------------------------------------------------------------
485 function get_URL18 return varchar2 is
486 --
487 -- Declare and intialise the URL
488 l_url varchar2(2000) := NULL;
489 begin
490 -- [CUSTOMIZE]
491 -- This function will need to be modified by the end customer.
492 return(l_url);
493 end get_URL18;
494 -- ------------------------------------------------------------------------
495 -- |------------------------------< get_URL19 >-----------------------------|
496 -- ------------------------------------------------------------------------
497 function get_URL19 return varchar2 is
498 --
499 -- Declare and intialise the URL
500 l_url varchar2(2000) := NULL;
501 begin
502 -- [CUSTOMIZE]
503 -- This function will need to be modified by the end customer.
504 return(l_url);
505 end get_URL19;
506 -- ------------------------------------------------------------------------
507 -- |------------------------------< get_URL20 >-----------------------------|
508 -- ------------------------------------------------------------------------
509 function get_URL20 return varchar2 is
510 --
511 -- Declare and intialise the URL
512 l_url varchar2(2000) := NULL;
513 begin
514 -- [CUSTOMIZE]
515 -- This function will need to be modified by the end customer.
516 return(l_url);
517 end get_URL20;
518 -- ----------------------------------------------------------------------------
519 -- |-----------------------< check_if_in_approval_chain >---------------------|
520 -- ----------------------------------------------------------------------------
521 function check_if_in_approval_chain
522 (p_forward_to_person_id in per_people_f.person_id%type
523 ,p_person_id in per_people_f.person_id%type)
524 return boolean is
525 --
526 l_in_chain boolean := false;
527 l_current_person_id per_people_f.person_id%type := p_person_id;
528 l_person_id per_people_f.person_id%type;
529 --
530 begin
531 --
532 while l_current_person_id is not null loop
533 if l_current_person_id = p_forward_to_person_id then
534 l_in_chain := true;
535 exit;
536 else
537 l_current_person_id := get_next_approver
538 (p_person_id => l_current_person_id);
539 end if;
540 end loop;
541 return(l_in_chain);
542 end check_if_in_approval_chain;
543 --
544 end hr_approval_custom;
[Home] [Help]