1 Package body pqh_rank_utility as
2 /* $Header: pqrnkutl.pkb 120.7 2012/01/19 07:25:43 rpahune ship $ */
3
4 g_package constant Varchar2(30) := 'pqh_rank_utility.';
5 g_debug constant boolean := hr_utility.debug_enabled;
6
7
8 -- ---------------------------------------------------------------------------
9 -- ------------- <is_ranking_enabled_for_txn> --------------------------------
10 -- ---------------------------------------------------------------------------
11 function is_ranking_enabled_for_txn(
12 p_copy_entity_txn_id in number )
13 return varchar2 is
14 l_proc constant varchar2(72) := g_package||'.is_ranking_enabled_for_txn';
15 l_use_rank varchar2(1);
16 begin
17 hr_utility.set_location('Entering:'|| l_proc, 10);
18 select information12
19 into l_use_rank
20 from ben_copy_entity_results
21 where copy_entity_txn_id = p_copy_entity_txn_id
22 and table_alias = 'PGI' ;
23 return l_use_rank ;
24 exception
25 when others then
26 raise ;
27 end ;
28
29
30 -- ---------------------------------------------------------------------------
31 -- ---------------- <get_ben_action_id> --------------------------------------
32 -- ---------------------------------------------------------------------------
33 function get_ben_action_id(
34 p_per_in_ler_id in number
35 ,p_pgm_id in number
36 ,p_pl_id in number)
37 return number is
38 l_proc constant varchar2(72) := g_package||'.get_ben_action_id';
39 l_ben_action_id number ;
40 l_gl_option varchar2(80) ;
41 cursor csr_for_group_gl is
42 select rp.benefit_action_id
43 from pqh_rank_processes rp
44 where rp.pgm_id = p_pgm_id
45 and rp.per_in_ler_id = p_per_in_ler_id;
46
47 cursor csr_for_group_grade is
48 select rp.benefit_action_id
49 from pqh_rank_processes rp
50 where rp.pgm_id = p_pgm_id
51 and rp.per_in_ler_id = p_per_in_ler_id
52 and rp.pl_id = p_pl_id ;
53 begin
54 hr_utility.set_location('Entering:'|| l_proc, 10);
55
56 -- test for ranking enabled for this gl
57 if ( get_grade_ladder_option (p_pgm_id , 'RANK_ENABLED')= 'N' ) then
58 l_ben_action_id := -1 ;
59 return l_ben_action_id ;
60 end if ;
61
62 l_gl_option := get_grade_ladder_option (p_pgm_id , 'RANK_GROUP_CRITERIA');
63 if (l_gl_option = 'GRADE' )
64 then
65 open csr_for_group_grade;
66 fetch csr_for_group_grade into l_ben_action_id ;
67 if csr_for_group_grade%notfound then
68 l_ben_action_id := -1 ;
69 End If;
70 Close csr_for_group_grade;
71 else
72 open csr_for_group_gl;
73 fetch csr_for_group_gl into l_ben_action_id ;
74 if csr_for_group_gl%notfound then
75 l_ben_action_id := -1 ;
76 End If;
77 Close csr_for_group_gl;
78 end if;
79 return l_ben_action_id ;
80 exception
81 when others then
82 hr_utility.set_location('Problem in determining Total Score',10);
83 raise;
84 end get_ben_action_id ;
85 -- ---------------------------------------------------------------------------
86 -- ------------- <update_proposed_rank> --------------------------------------
87 -- ---------------------------------------------------------------------------
88 procedure update_proposed_rank ( p_proposed_rank in number
89 ,p_per_in_ler_id in number
90 ,p_pgm_id in number
91 ,p_pl_id in number )
92 is
93 l_proc constant varchar2(72) := g_package||'.update_proposed_rank';
94 l_gl_option varchar2(80) ;
95 l_rank_process_approval_id number;
96 l_rank_process_id number;
97 l_date date ;
98 l_object_version_number number ;
99
100 cursor csr_for_group_gl is
101 select rpa.rank_process_approval_id
102 ,rpa.rank_process_id
103 ,rpa.object_version_number
104 from pqh_rank_processes rp
105 ,pqh_rank_process_approvals rpa
106 where rp.pgm_id = p_pgm_id
107 and rp.per_in_ler_id = p_per_in_ler_id
108 and rp.rank_process_id = rpa.rank_process_id;
109
110 cursor csr_for_group_grade is
111 select rpa.rank_process_approval_id
112 ,rpa.rank_process_id
113 ,rpa.object_version_number
114 from pqh_rank_processes rp
115 ,pqh_rank_process_approvals rpa
116 where rp.pgm_id = p_pgm_id
117 and rp.per_in_ler_id = p_per_in_ler_id
118 and rp.rank_process_id = rpa.rank_process_id
119 and rp.pl_id = p_pl_id ;
120 begin
121 hr_utility.set_location('Entering:'|| l_proc, 10);
122 l_date := sysdate ;
123 -- Dont update If rank is not enabled for this grade ladder
124 if ( get_grade_ladder_option (p_pgm_id , 'RANK_ENABLED')= 'N' ) then
125 return ;
126 end if ;
127
128 l_gl_option := get_grade_ladder_option (p_pgm_id , 'RANK_GROUP_CRITERIA');
129 if (l_gl_option = 'GRADE')
130 then
131 open csr_for_group_grade;
132 fetch csr_for_group_grade
133 into l_rank_process_approval_id,l_rank_process_id, l_object_version_number ;
134 Close csr_for_group_grade;
135 else
136 open csr_for_group_gl;
137 fetch csr_for_group_gl
138 into l_rank_process_approval_id,l_rank_process_id, l_object_version_number ;
139 Close csr_for_group_gl;
140 end if;
141
142 --NS 04/24/05: Update to be called only if record exits
143 IF (l_rank_process_id is not null) Then
144 PQH_RANK_PROCESS_APPROVAL_API.update_rank_process_approval
145 (p_effective_date =>l_date
146 ,p_rank_process_approval_id =>l_rank_process_approval_id
147 ,p_rank_process_id =>l_rank_process_id
148 ,p_approval_date =>l_date
149 ,p_proposed_rank =>p_proposed_rank
150 ,p_object_version_number =>l_object_version_number
151 );
152 End If;
153
154 exception
155 when others then
156 hr_utility.set_location('Problem in updating proposed Rank',10);
157 raise;
158 end update_proposed_rank ;
159
160 -- ---------------------------------------------------------------------------
161 -- ------------- <update_proposed_rank> --------------------------------------
162 -- -------- <OBSOLETE USE THE OVERLOADED METHOD WITH PER_IN_LER_ID > ---------
163 -- ---------------------------------------------------------------------------
164 procedure update_proposed_rank ( p_proposed_rank in number
165 ,p_assignment_id in number
166 ,p_life_event_dt in date
167 ,p_pgm_id in number
168 ,p_pl_id in number )
169 is
170 l_proc constant varchar2(72) := g_package||'.update_proposed_rank';
171 l_gl_option varchar2(80) ;
172 l_rank_process_approval_id number;
173 l_rank_process_id number;
174 l_date date ;
175 l_object_version_number number ;
176
177 cursor csr_for_group_gl is
178 select rpa.rank_process_approval_id
179 ,rpa.rank_process_id
180 ,rpa.object_version_number
181 from pqh_rank_processes rp
182 ,pqh_rank_process_approvals rpa
183 where rp.pgm_id = p_pgm_id
184 and rp.assignment_id = p_assignment_id
185 and rp.PROCESS_DATE = p_life_event_dt
186 and rp.rank_process_id = rpa.rank_process_id;
187
188 cursor csr_for_group_grade is
189 select rpa.rank_process_approval_id
190 ,rpa.rank_process_id
191 ,rpa.object_version_number
192 from pqh_rank_processes rp
193 ,pqh_rank_process_approvals rpa
194 where rp.pgm_id = p_pgm_id
195 and rp.assignment_id = p_assignment_id
196 and rp.PROCESS_DATE = p_life_event_dt
197 and rp.rank_process_id = rpa.rank_process_id
198 and rp.pl_id = p_pl_id ;
199 begin
200 hr_utility.set_location('Entering:'|| l_proc, 10);
201 l_date := sysdate ;
202 -- Dont update If rank is not enabled for this grade ladder
203 if ( get_grade_ladder_option (p_pgm_id , 'RANK_ENABLED')= 'N' ) then
204 return ;
205 end if ;
206
207 l_gl_option := get_grade_ladder_option (p_pgm_id , 'RANK_GROUP_CRITERIA');
208 if (l_gl_option = 'GRADE')
209 then
210 open csr_for_group_grade;
211 fetch csr_for_group_grade
212 into l_rank_process_approval_id,l_rank_process_id, l_object_version_number ;
213 Close csr_for_group_grade;
214 else
215 open csr_for_group_gl;
216 fetch csr_for_group_gl
217 into l_rank_process_approval_id,l_rank_process_id, l_object_version_number ;
218 Close csr_for_group_gl;
219 end if;
220
221 --NS 04/24/05: Update to be called only if record exits
222 IF (l_rank_process_id is not null) Then
223 PQH_RANK_PROCESS_APPROVAL_API.update_rank_process_approval
224 (p_effective_date =>l_date
225 ,p_rank_process_approval_id =>l_rank_process_approval_id
226 ,p_rank_process_id =>l_rank_process_id
227 ,p_approval_date =>l_date
228 ,p_proposed_rank =>p_proposed_rank
229 ,p_object_version_number =>l_object_version_number
230 );
231 End If;
232
233 exception
234 when others then
235 hr_utility.set_location('Problem in updating proposed Rank',10);
236 raise;
237 end update_proposed_rank ;
238
239 -- ---------------------------------------------------------------------------
240 -- ------------------------- <get_rank> --------------------------------------
241 -- ---------------------------------------------------------------------------
242 function get_rank (
243 p_rank_type in varchar2
244 ,p_per_in_ler_id in number
245 ,p_pgm_id in number
246 ,p_pl_id in number )
247 return number is
248 l_proc constant varchar2(72) := g_package||'.get_rank';
249 l_proposed_rank number ;
250 l_rank number ;
251 l_gl_option varchar2(80) ;
252
253 cursor csr_for_grp_gl_sys_rank is
254 select rpa.system_rank
255 from pqh_rank_processes rp
256 ,pqh_rank_process_approvals rpa
257 where rp.pgm_id = p_pgm_id
258 and rp.per_in_ler_id = p_per_in_ler_id
259 and rp.rank_process_id = rpa.rank_process_id;
260
261 cursor csr_for_grp_grade_sys_rank is
262 select rpa.system_rank
263 from pqh_rank_processes rp
264 ,pqh_rank_process_approvals rpa
265 where rp.pgm_id = p_pgm_id
266 and rp.per_in_ler_id = p_per_in_ler_id
267 and rp.rank_process_id = rpa.rank_process_id
268 and rp.pl_id = p_pl_id ;
269
270 cursor csr_for_grp_gl_pro_rank is
271 select rpa.proposed_rank
272 from pqh_rank_processes rp
273 ,pqh_rank_process_approvals rpa
274 where rp.pgm_id = p_pgm_id
275 and rp.per_in_ler_id = p_per_in_ler_id
276 and rp.rank_process_id = rpa.rank_process_id;
277
278 cursor csr_for_grp_grade_pro_rank is
279 select rpa.proposed_rank
280 from pqh_rank_processes rp
281 ,pqh_rank_process_approvals rpa
282 where rp.pgm_id = p_pgm_id
283 and rp.per_in_ler_id = p_per_in_ler_id
284 and rp.rank_process_id = rpa.rank_process_id
285 and rp.pl_id = p_pl_id ;
286 begin
287 hr_utility.set_location('Entering:'|| l_proc, 10);
288 -- Test for ranking enabled for this gl
289 if ( get_grade_ladder_option (p_pgm_id , 'RANK_ENABLED')= 'N' ) then
290 return 0 ;
291 end if ;
292
293 l_gl_option := get_grade_ladder_option (p_pgm_id , 'RANK_GROUP_CRITERIA');
294 if (l_gl_option = 'GRADE')
295 then
296 if (p_rank_type = 'S') -- Grouping criteria Grade, System Rank
297 then
298 open csr_for_grp_grade_sys_rank;
299 fetch csr_for_grp_grade_sys_rank into l_rank;
300 if csr_for_grp_grade_sys_rank%notfound then
301 l_rank := 0 ;
302 End If;
303 Close csr_for_grp_grade_sys_rank;
304 else -- Grouping criteria Grade, Proposed Rank
305 open csr_for_grp_grade_pro_rank;
306 fetch csr_for_grp_grade_pro_rank into l_rank;
307 if csr_for_grp_grade_pro_rank%notfound then
308 l_rank := 0 ;
309 End If;
310 Close csr_for_grp_grade_pro_rank;
311 end if ;
312 else
313 if (p_rank_type = 'S') -- Grouping criteria GL, System Rank
314 then
315 open csr_for_grp_gl_sys_rank;
316 fetch csr_for_grp_gl_sys_rank into l_rank;
317 if csr_for_grp_gl_sys_rank%notfound then
318 l_rank := 0 ;
319 End If;
320 Close csr_for_grp_gl_sys_rank;
321 else -- Grouping criteria GL, Proposed Rank
322 open csr_for_grp_gl_pro_rank;
323 fetch csr_for_grp_gl_pro_rank into l_rank;
324 if csr_for_grp_gl_pro_rank%notfound then
325 l_rank := 0 ;
326 End If;
327 Close csr_for_grp_gl_pro_rank;
328 end if ;
329 end if;
330 return l_rank ;
331 exception
332 when others then
333 hr_utility.set_location('Problem in determining Rank',10);
334 raise;
335 end get_rank ;
336
337 -- ---------------------------------------------------------------------------
338 -- ------------------ <get_total_score> --------------------------------------
339 -- ---------------------------------------------------------------------------
340 function get_total_score(
341 p_per_in_ler_id in number
342 ,p_pgm_id in number
343 ,p_pl_id in number )
344 return number is
345 l_proc constant varchar2(72):= g_package||'.get_total_score';
346 l_total_score number ;
347 l_gl_option varchar2(80) ;
348 cursor csr_for_group_gl is
349 select rp.total_score
350 from pqh_rank_processes rp
351 where rp.pgm_id = p_pgm_id
352 and rp.per_in_ler_id = p_per_in_ler_id;
353
354 cursor csr_for_group_grade is
355 select rp.total_score
356 from pqh_rank_processes rp
357 where rp.pgm_id = p_pgm_id
358 and rp.per_in_ler_id = p_per_in_ler_id
359 and rp.pl_id = p_pl_id ;
360 begin
361 hr_utility.set_location('Entering:'|| l_proc, 10);
362 -- may be test for ranking enabled for this gl be doen
363 if ( get_grade_ladder_option (p_pgm_id , 'RANK_ENABLED')= 'N' ) then
364 l_total_score := 0 ;
365 return l_total_score ;
366 end if ;
367
368 l_gl_option := get_grade_ladder_option (p_pgm_id , 'RANK_GROUP_CRITERIA');
369 if (l_gl_option = 'GRADE' )
370 then
371 open csr_for_group_grade;
372 fetch csr_for_group_grade into l_total_score;
373 if csr_for_group_grade%notfound then
374 l_total_score := 0 ;
375 End If;
376 Close csr_for_group_grade;
377 else
378 open csr_for_group_gl;
379 fetch csr_for_group_gl into l_total_score;
380 if csr_for_group_gl%notfound then
381 l_total_score := 0 ;
382 End If;
383 Close csr_for_group_gl;
384 end if;
385 return l_total_score ;
386 exception
387 when others then
388 hr_utility.set_location('Problem in determining Total Score',10);
389 raise;
390 end get_total_score ;
391
392
393 -- ---------------------------------------------------------------------------
394 -- ------------- <is_ranking_enabled_for_bg> ---------------------------------
395 -- ---------------------------------------------------------------------------
396 function is_ranking_enabled_for_bg (p_business_group_id in Number)
397 return varchar2 is
398 l_proc constant varchar2(72) := g_package||'is_ranking_enabled_for_bg';
399 l_present_flag varchar2(1) ;
400 begin
401 hr_utility.set_location('Entering:'|| l_proc, 10);
402 begin
403 select 'Y'
404 into l_present_flag
405 from ben_pgm_f pgm
406 ,ben_pgm_extra_info pgmei
407 where pgm.business_group_id = p_business_group_id
408 and pgm.pgm_id = pgmei.pgm_id
409 and pgmei.information_type = 'PQH_GSP_EXTRA_INFO'
410 and pgmei.pgi_information2 = 'Y' -- Verify and confirm
411 and rownum < 2 ;
412 exception
413 when no_data_found then
414 l_present_flag := 'N' ;
415 when others then
416 hr_utility.set_location('Problem in determining Ranking Enabled for BG ',10);
417 raise;
418 end ;
419 return l_present_flag ;
420 end is_ranking_enabled_for_bg;
421
422 -- ---------------------------------------------------------------------------
423 -- ------------- <get_grade_ladder_option> -----------------------------------
424 -- ---------------------------------------------------------------------------
425 function get_grade_ladder_option (
426 p_pgm_id in number
427 ,p_option in varchar2)
428 return Varchar2 is
429 l_proc constant varchar2(72) := g_package||'.get_grade_ladder_option';
430 l_option_value varchar2(100);
431 l_present_flag varchar2(10);
432 begin
433 hr_utility.set_location('Entering:'|| l_proc, 10);
434 l_present_flag := 'N';
435 if (p_option = 'WORKFLOW_STATUS') then
436 begin
437 --NS 4/23/05: If not set, treat it as not found
438 select NVL(pgi_information1,'NOT_FOUND') -- Verify and confirm
439 into l_option_value
440 from ben_pgm_extra_info pgmei
441 where pgmei.pgm_id = p_pgm_id
442 and pgmei.information_type = 'PQH_GSP_EXTRA_INFO'
443 and rownum < 2 ;
444 exception
445 when no_data_found then
446 l_option_value := 'NOT_FOUND' ;
447 end ;
448
449 elsif (p_option = 'RANK_ENABLED') then
450 begin
451 --NS 4/23/05: If not set, treat it as N
452 select NVL(pgi_information2,'N') -- Verify and confirm
453 into l_option_value
454 from ben_pgm_extra_info pgmei
455 where pgmei.pgm_id = p_pgm_id
456 and pgmei.information_type = 'PQH_GSP_EXTRA_INFO'
457 and rownum < 2 ;
458 exception
459 when no_data_found then
460 l_option_value := 'N' ;
461 end ;
462 elsif (p_option = 'RANK_GROUP_CRITERIA') then
463 begin
464 --NS 4/23/05: If not set, treat it as not found
465 select NVL(pgi_information3,'NOT_FOUND') -- Verify and confirm
466 into l_option_value
467 from ben_pgm_extra_info pgmei
468 where pgmei.pgm_id = p_pgm_id
469 and pgmei.information_type = 'PQH_GSP_EXTRA_INFO'
470 and rownum < 2 ;
471 exception
472 when no_data_found then
473 l_option_value := 'NOT_FOUND' ;
474 end ;
475
476 end if ;
477 return l_option_value ;
478 exception
479 when others then
480 hr_utility.set_location('Problem in determining Ranking Enabled for BG ',10);
481 raise;
482 end get_grade_ladder_option;
483
484 -- ---------------------------------------------------------------
485 -- ---- Added by Nischal to Initate workflow from approval UI ----
486 -- ---------- <on_approval_init_workflow > -----------------------
487 -- ---------------------------------------------------------------
488 /* This procedure will check whether the workflow is enabled for the program.
489 * If it is enabled, a workflow process will be initiated. If not,
490 * the electable choice record will be marked approved as usual.
491 */
492 procedure on_approval_init_workflow(
493 p_elctbl_chc_id in number
494 ,p_pgm_id in number
495 ,p_person_id in number
496 ,p_person_name in varchar2
497 ,p_prog_dt in date
498 ,p_sal_chg_dt in date
499 ,p_comments in varchar2
500 ,p_ameTranType in varchar2
501 ,p_ameAppId in varchar2
502 ,p_itemType in varchar2
503 ,p_processName in varchar2
504 ,p_functionName in varchar2
505 ,p_currentUser in varchar2
506 ,p_supervisorId in number ) IS
507 -- Local Variables
508 l_workflow_enabled varchar2(10);
509 l_transaction_id number(18);
510 l_itemKey varchar2(240);
511 l_function_id number(18);
512
513 -- Cursor to get function details.
514 cursor csr_fun is
515 select fff.function_id
516 from fnd_form_functions_vl fff
517 where fff.function_name = p_functionName;
518 --
519 l_ovn number(15);
520 l_processDisplayName varchar2(240);
521
522 cursor csr_prc is
523 SELECT wrpv.display_name displayName
524 FROM wf_runnable_processes_v wrpv
525 WHERE wrpv.item_type = p_itemType
526 AND wrpv.process_name = p_processName;
527
528 procedure approve_now is
529 begin
530 -- Call the procedure to mark the record as approved.
531 Pqh_gsp_post_process.Approve_Reject_AUI (
532 P_Elig_per_Elctbl_Chc_id => p_elctbl_chc_id
533 ,P_Prog_Dt => p_prog_dt
534 ,P_Sal_Chg_Dt => p_sal_chg_dt
535 ,P_Comments => p_comments
536 ,P_Approve_Rej => 'PQH_GSP_A' );
537 end approve_now;
538 --
539 begin
540 -- If person has no supervisor , then approve immediately
541 -- no need for further processing
542 if ( p_supervisorId is null) then
543 approve_now;
544 return;
545 end if;
546
547 SAVEPOINT on_approval_workflow_process;
548 -- Check if workflow is enabled.
549 l_workflow_enabled := pqh_rank_utility.get_grade_ladder_option (
550 p_pgm_id => p_pgm_id
551 ,p_option => 'WORKFLOW_STATUS');
552
553 -- Workflow not enabled
554 If (l_workflow_enabled <> 'Y') then
555 approve_now;
556 Else
557 -- Update the electable choice record, mark it as pending workflow approval
558 Select object_version_number
559 into l_ovn
560 From ben_elig_per_elctbl_chc
561 Where elig_per_elctbl_chc_id = p_elctbl_chc_id;
562
563 ben_elig_per_elc_chc_api.update_elig_per_elc_chc (
564 p_elig_per_elctbl_chc_id => p_elctbl_chc_id
565 ,p_in_pndg_wkflow_flag => 'Y'
566 ,p_object_version_number => l_ovn
567 ,p_effective_date => sysdate);
568
569 -- create a transaction that AME will use to fetch appropriate info.
570 -- Workflow is enabled, so initiate the workflow process,
571 -- Set workflow attributes that will be used later on.
572 -- -----------------------------------------------------
573 -- Error Handling needed here for mandatory arguments
574 -- p_itemType - Must check the function parameter
575 -- FND User for person_id must be defined
576 -- FND Users must be defined All managers
577 -- These validations may be performed while initializing AME
578 -- -----------------------------------------------------
579 -- Get the itemKey from sequence.
580 -- NOTE: This is a new item for the ODF and needs to be generated.
581 --
582 select pqh_workflow_item_key_s.nextval into l_itemKey from dual;
583 --
584 l_itemKey := 'GSP'||l_itemKey;
585 Open csr_fun;
586 Fetch csr_fun into l_function_id;
587 Close csr_fun;
588 --
589 hr_transaction_api.create_transaction(
590 p_validate => false
591 ,p_creator_person_id => p_person_id
592 ,p_transaction_privilege => 'PRIVATE'
593 ,p_transaction_id => l_transaction_id
594 ,p_product_code => 'PQH'
595 ,p_status => 'N' -- new txn
596 ,p_function_id => l_function_id
597 ,p_selected_person_id => p_person_id
598 ,p_item_type => p_itemtype
599 ,p_item_key => l_itemkey
600 ,p_process_name => p_processName );
601
602
603 -- Kick off the workflow process.
604 wf_engine.CreateProcess (p_itemtype,l_itemkey,p_processName);
605
606
607 if p_currentUser is not null then
608 wf_engine.SetItemOwner(p_itemtype,l_itemkey,p_currentUser);
609 end if;
610
611 -- Get Process Display Name
612 Open csr_prc;
613 Fetch csr_prc into l_processDisplayName;
614 Close csr_prc;
615
616 wf_engine.SetItemAttrText (
617 itemtype => p_itemType
618 ,itemkey => l_ItemKey
619 ,aname => 'TRANSACTION_NAME'
620 ,avalue => l_processDisplayName);
621
622 -- Set the route by user (appears in from on worklist)
623 wf_engine.SetItemAttrText (
624 itemtype => p_itemType
625 ,itemkey => l_ItemKey
626 ,aname => 'ROUTED_BY_USER'
627 ,avalue => p_currentUser);
628
629 -- Set the electable choice id
630 wf_engine.SetItemAttrText (
631 itemtype => p_itemType
632 ,itemkey => l_ItemKey
633 ,aname => 'PARAMETER1_VALUE'
634 ,avalue => p_elctbl_chc_id);
635
636 -- set the comments entered -- 9654222
637
638 wf_engine.SetItemAttrText (
639 itemtype => p_itemType
640 ,itemkey => l_ItemKey
641 ,aname => 'APPROVAL_COMMENT_COPY'
642 ,avalue => p_comments);
643
644 -- Set the person Id
645 wf_engine.SetItemAttrText (
646 itemtype => p_itemType
647 ,itemkey => l_ItemKey
648 ,aname => 'PERSON_ID'
649 ,avalue => p_person_id );
650
651 -- Set the person name
652 wf_engine.SetItemAttrText (
653 itemtype => p_itemType
654 ,itemkey => l_ItemKey
655 ,aname => 'PSV_PERSON_NAME'
656 ,avalue => p_person_name );
657
658 -- Set the AME Transaction Type
659 wf_engine.SetItemAttrText (
660 itemtype => p_itemType
661 ,itemkey => l_ItemKey
662 ,aname => 'PARAMETER2_VALUE'
663 ,avalue => p_AMETranType );
664
665 -- Set the AME Application Id
666 wf_engine.SetItemAttrText (
667 itemtype => p_itemType
668 ,itemkey => l_ItemKey
669 ,aname => 'PARAMETER3_VALUE'
670 ,avalue => p_AMEAppId );
671
672 wf_engine.StartProcess (p_itemtype,l_itemkey);
673
674 end if;
675 Exception
676 When Others Then
677 ROLLBACK to on_approval_workflow_process;
678 raise;
679 end on_approval_init_workflow;
680
681
682 -- ---------------------------------------------------------------------------
683 -- ------------- <is_ranking_enabled_for_person> ---------------------------------
684 -- ---------------------------------------------------------------------------
685 /* This function will get the program id for a person and
686 * If Ranking is enabled for the grade ladder it will return RankEnabled
687 * else it will return RankDisabled
688 */
689 function is_ranking_enabled_for_person(p_person_id in number
690 ,p_business_group_id in number
691 ,p_effective_date in date)
692 return varchar2 is
693 l_proc constant varchar2(72) := g_package||'is_ranking_enabled_for_person';
694 l_present_flag varchar2(1) ;
695 l_persons_pgm_id number ;
696 l_persons_plip_id number ;
697 l_prog_style varchar2(30) ;
698 l_rank_enabled varchar2(10) ;
699 l_return_value varchar2(30) ;
700 begin
701 hr_utility.set_location('Entering:'|| l_proc, 10);
702 --
703 -- fetch the grade ladder of the person
704 --
705 pqh_gsp_post_process.get_persons_gl_and_grade(p_person_id => p_person_id
706 ,p_business_group_id => p_business_group_id
707 ,p_effective_date => p_effective_date
708 ,p_persons_pgm_id => l_persons_pgm_id
709 ,p_persons_plip_id => l_persons_plip_id
710 ,p_prog_style => l_prog_style
711 );
712 --
713 -- get rank status for the grade ladder
714 --
715 l_rank_enabled := get_grade_ladder_option (l_persons_pgm_id , 'RANK_ENABLED') ;
716
717 if ( l_rank_enabled = 'Y') then
718 l_return_value:='RankEnabled';
719 else
720 l_return_value:='RankDisabled';
721 end if ;
722
723 return l_return_value ;
724
725 end is_ranking_enabled_for_person;
726
727 End pqh_rank_utility;