1 Package Body pqh_corps_utility as
2 /* $Header: pqcpdutl.pkb 120.1 2006/03/17 07:04:02 ggnanagu noship $ */
3
4 FUNCTION get_step_name(p_step_id Number, p_effective_date Date) RETURN varchar2 IS
5
6 Cursor csr_step_dtls (p_step_id number, p_eff_date Date) IS
7 SELECT --steps.sequence||'('|| point.spinal_point||')' "SEQUENCE",
8 point.spinal_point,
9 steps.spinal_point_id,
10 steps.grade_spine_id
11 FROM per_spinal_point_steps_f steps,
12 per_spinal_points point
13 WHERE steps.step_id = p_step_id
14 AND steps.spinal_point_id = point.spinal_point_id
15 AND p_eff_date BETWEEN steps.effective_start_date AND steps.effective_end_date;
16
17 l_step_name Varchar2(240);
18 l_spinal_point varchar2(240);
19 l_spinal_point_id number;
20 l_grade_spine_id number;
21 l_seq number;
22 BEGIN
23 OPEN csr_step_dtls(p_step_id,p_effective_date);
24 FETCH csr_step_dtls INTO l_spinal_point, l_spinal_point_id, l_grade_spine_id;
25 CLOSE csr_step_dtls;
26 PER_SPINAL_POINT_STEPS_PKG.pop_flds(l_seq,p_effective_date,l_spinal_point_id,l_grade_spine_id);
27 IF l_seq IS NULL AND l_spinal_point IS NULL THEN
28 l_step_name := NULL;
29 ELSE
30 l_step_name := l_seq||'('||l_spinal_point||')';
31 END IF;
32 RETURN l_step_name;
33 END get_step_name;
34
35 Function get_increased_index(p_gross_index IN NUMBER, p_effective_date IN date) Return Number IS
36
37 CURSOR csr_increased_index IS
38 SELECT increased_index
39 FROM pqh_fr_global_indices_f
40 WHERE gross_index = p_gross_index
41 AND type_of_record = 'IND' -- for indices
42 AND p_effective_date BETWEEN effective_start_date and effective_end_date;
43
44 l_increased_index pqh_fr_global_indices_f.increased_index%TYPE;
45 BEGIN
46 OPEN csr_increased_index;
47 FETCH csr_increased_index INTO l_increased_index;
48 CLOSE csr_increased_index;
49
50 RETURN l_increased_index;
51
52 END get_increased_index;
53
54 Function get_salary_rate(p_gross_index IN NUMBER,
55 p_effective_date IN DATE,
56 p_copy_entity_txn_id IN NUMBER default null,
57 p_currency_code IN VARCHAR2 DEFAULT NULL) RETURN NUMBER IS
58
59 Cursor csr_sal_rate IS
60 SELECT NVL(basic_salary_rate,0), currency_code
61 FROM pqh_fr_global_indices_f
62 WHERE type_of_record = 'INM'
63 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
64
65 CURSOR csr_gl_currency (p_cet_id IN NUMBER) IS
66 SELECT information50
67 FROM ben_copy_entity_results
68 WHERE copy_entity_txn_id = p_cet_id
69 AND table_alias = 'PGM';
70
71 CURSOR csr_get_precision
72 IS
73 select Precision
74 from Fnd_Currencies
75 where currency_code =p_currency_code;
76
77 l_gl_currency pqh_fr_global_indices_f.currency_code%TYPE;
78 l_barame_currency pqh_fr_global_indices_f.currency_code%TYPE;
79 l_increased_index pqh_fr_global_indices_f.increased_index%TYPE;
80 l_basic_sal_rate pqh_fr_global_indices_f.basic_salary_rate%TYPE;
81 l_salary_value NUMBER := 0;
82 l_conv_factor NUMBER := 1;
83 l_precision NUMBER;
84
85 BEGIN
86
87 IF p_currency_code IS NULL THEN
88 l_precision :=2;
89 ELSE
90 OPEN csr_get_precision;
91 FETCH csr_get_precision INTO l_precision;
92 if l_precision IS NULL THEN
93 l_precision := 2;
94 END IF;
95 CLOSE csr_get_precision;
96 END IF;
97
98 l_increased_index := get_increased_index(p_gross_index => p_gross_index,p_effective_date => p_effective_date);
99 IF l_increased_index IS NOT NULL THEN
100 OPEN csr_sal_rate;
101 FETCH csr_sal_rate INTO l_basic_sal_rate,l_barame_currency;
102 CLOSE csr_sal_rate;
103 l_salary_value := l_increased_index * l_basic_sal_rate;
104 END IF;
105 IF p_currency_code IS NOT NULL THEN
106 l_gl_currency := p_currency_code;
107 ELSIF p_copy_entity_txn_id IS NOT NULL THEN
108 OPEN csr_gl_currency(p_copy_entity_txn_id);
109 FETCH csr_gl_currency INTO l_gl_currency;
110 CLOSE csr_gl_currency;
111 END IF;
112 IF l_gl_currency IS NOT NULL AND l_barame_currency IS NOT NULL AND l_gl_currency <> l_barame_currency THEN
113 BEGIN
114 l_conv_factor := hr_currency_pkg.get_rate(p_from_currency => l_barame_currency,
115 p_to_currency => l_gl_currency,
116 p_conversion_date => p_effective_date,
117 p_rate_type => 'Corporate');
118 Exception
119 When Others Then
120 l_conv_factor := 1;
121 END;
122 END IF;
123 l_salary_value := l_salary_value*NVL(l_conv_factor,1);
124
125 l_salary_value := round(l_salary_value,l_precision);
126
127 RETURN l_salary_value;
128
129 END get_salary_rate;
130
131 Function get_increased_index(p_gross_index IN NUMBER, p_copy_entity_txn_id IN NUMBER) RETURN NUMBER IS
132
133 CURSOR csr_eff_date IS
134 SELECT action_date
135 FROM pqh_copy_entity_txns
136 WHERE copy_entity_txn_id = p_copy_entity_txn_id;
137 /* For Bug Fix 3532356: Retrieving the Effective Date value from pqh_copy_entity_txns.action_date
138 instead from ben_copy_entity_results.
139 */
140
141 l_effective_date DATE;
142 l_increased_index NUMBER;
143 BEGIN
144 OPEN csr_eff_date;
145 FETCH csr_eff_date INTO l_effective_date;
146 CLOSE csr_eff_date;
147 l_increased_index := get_increased_index(p_gross_index => p_gross_index,p_effective_date => l_effective_date);
148
149 RETURN l_increased_index;
150
151
152
153 END get_increased_index;
154
155 Procedure review_submit_valid_corps(p_copy_entity_txn_id IN NUMBER,
156 p_effective_date IN DATE,
157 p_business_group_id IN NUMBER,
158 p_status OUT NOCOPY Varchar2) IS
159
160 l_status varchar2(1) := 'S';
161
162 CURSOR csr_corps_dflt_plcmt IS
163 SELECT information162, information169
164 FROM ben_copy_entity_results
165 WHERE copy_entity_txn_id = p_copy_entity_txn_id
166 AND information4 = p_business_group_id
167 AND table_alias = 'CPD';
168 l_plip_cer_id number(15);
169 l_oipl_cer_id number(15);
170
171 CURSOR csr_dflt_grd_in_grdldr(p_plip_cer_id Number) IS
172 SELECT 'Y'
173 FROM ben_copy_entity_results
174 WHERE copy_entity_txn_id = p_copy_entity_txn_id
175 AND table_alias = 'CPP'
176 AND copy_entity_result_id = p_plip_cer_id
177 AND result_type_cd = 'DISPLAY'
178 AND nvl(information104,'LINK') <> 'UNLINK';
179 l_grd_exists Varchar2(1);
180
181 CURSOR csr_dflt_step_in_grdldr(p_oipl_cer_id Number, p_plip_cer_id Number) IS
182 SELECT 'Y'
183 FROM ben_copy_entity_results
184 WHERE copy_entity_txn_id = p_copy_entity_txn_id
185 AND table_alias = 'COP'
186 AND copy_entity_result_id = p_oipl_cer_id
187 AND gs_parent_entity_result_id = p_plip_cer_id
188 AND result_type_cd = 'DISPLAY'
189 AND nvl(information104,'LINK') <> 'UNLINK';
190 l_step_exists Varchar2(1);
191
192 BEGIN
193 IF nvl(get_cet_business_area(p_copy_entity_txn_id),'PQH_GSP_TASK_LIST') = 'PQH_CORPS_TASK_LIST' THEN
194 OPEN csr_corps_dflt_plcmt;
195 FETCH csr_corps_dflt_plcmt INTO l_plip_cer_id, l_oipl_cer_id;
196 CLOSE csr_corps_dflt_plcmt;
197 IF l_plip_cer_id IS NOT NULL THEN
198 OPEN csr_dflt_grd_in_grdldr(l_plip_cer_id);
199 FETCH csr_dflt_grd_in_grdldr INTO l_grd_exists;
200 IF csr_dflt_grd_in_grdldr%NOTFOUND THEN
201 fnd_message.set_name('PQH','PQH_CORPS_DFLT_GRD_NOTLINKED');
202 hr_multi_message.add;
203 l_status := 'E';
204 ELSE
205 IF l_oipl_cer_id IS NOT NULL THEN
206 OPEN csr_dflt_step_in_grdldr(l_oipl_cer_id,l_plip_cer_id);
207 FETCH csr_dflt_step_in_grdldr INTO l_step_exists;
208 IF csr_dflt_step_in_grdldr%NOTFOUND THEN
209 fnd_message.set_name('PQH','PQH_CORPS_DFLT_STEP_NOTLINKED');
210 hr_multi_message.add;
211 l_status := 'E';
212 END IF;
213 CLOSE csr_dflt_step_in_grdldr;
214 END IF;
215 END IF;
216 Close csr_dflt_grd_in_grdldr;
217 END IF;
218 p_status := l_status;
219 END IF;
220 END review_submit_valid_corps;
221
222 Function get_global_basic_sal_rate(p_effective_date in DATE) RETURN NUMBER IS
223 CURSOR csr_sal_rate IS
224 SELECT basic_salary_rate
225 FROM pqh_fr_global_indices_f
226 WHERE type_of_record = 'INM'
227 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
228
229 l_sal_rate NUMBER := 0;
230 BEGIN
231
232 OPEN csr_sal_rate;
233 FETCH csr_sal_rate INTO l_sal_rate;
234 CLOSE csr_sal_rate;
235
236 RETURN l_sal_rate;
237
238 END get_global_basic_sal_rate;
239
240 Function get_cet_business_area(p_copy_entity_txn_id IN Number) return Varchar2 IS
241 CURSOR csr_bus_area IS
242 SELECT NVL(cea.information9,'PQH_GSP_TASK_LIST')
243 FROM pqh_copy_entity_attribs cea
244 WHERE cea.copy_entity_txn_id = p_copy_entity_txn_id;
245 l_bus_area varchar2(30);
246 BEGIN
247 OPEN csr_bus_area;
248 FETCH csr_bus_area INTO l_bus_area;
249 CLOSE Csr_bus_area;
250 RETURN l_bus_area;
251 END get_cet_business_area;
252
253 Function get_step_name_for_hgrid(p_step_id IN Number,
254 p_effective_date IN DATE) RETURN VARCHAR2 IS
255
256 Cursor csr_stp_name IS
257 SELECT steps.sequence,
258 point.spinal_point,
259 steps.spinal_point_id,
260 steps.grade_spine_id,
261 point.information1 "IB",
262 get_increased_index(point.information1,p_effective_date) "INM"
263 FROM per_spinal_point_steps_f steps,
264 per_spinal_points point
265 WHERE steps.step_id = p_step_id
266 AND steps.spinal_point_id = point.spinal_point_id
267 AND p_effective_date BETWEEN steps.effective_start_date AND steps.effective_end_date;
268
269 l_name varchar2(240);
270 l_stp_name_rec csr_stp_name%ROWTYPE;
271 l_seq number;
272 BEGIN
273 OPEN csr_stp_name;
274 FETCH csr_stp_name INTO l_stp_name_rec.sequence,
275 l_stp_name_rec.spinal_point,
276 l_stp_name_rec.spinal_point_id,
277 l_stp_name_rec.grade_spine_id,
278 l_stp_name_rec.ib,
279 l_stp_name_rec.inm;
280 CLOSE csr_stp_name;
281 PER_SPINAL_POINT_STEPS_PKG.pop_flds(l_seq,p_effective_date,l_stp_name_rec.spinal_point_id,l_stp_name_rec.grade_spine_id);
282 l_name := l_seq||'('||l_stp_name_rec.spinal_point;
283 IF l_stp_name_rec.ib IS NOT NULL AND l_stp_name_rec.INM IS NOT NULL THEN
284 l_name := l_name||'| IB: '||l_stp_name_rec.ib||'| INM: '||l_stp_name_rec.inm;
285 END IF;
286 l_name := l_name||')';
287
288 RETURN l_name;
289 END get_step_name_for_hgrid;
290
291 Function get_bg_type_of_ps(p_business_group_id IN NUMBER) RETURN VARCHAR2
292
293 IS
294 CURSOR csr_bg_type_of_ps IS
295 SELECT org_information1
296 FROM hr_organization_information
297 WHERE organization_id = p_business_group_id
298 AND org_information_context = 'FR_PQH_GROUPING_UNIT_INFO';
299 l_type_of_ps varchar2(30);
300 BEGIN
301 OPEN csr_bg_type_of_ps;
302 FETCH csr_bg_type_of_ps INTO l_type_of_ps;
303 CLOSE csr_bg_type_of_ps;
304 RETURN l_type_of_ps;
305 END get_bg_type_of_ps;
306
307 Function get_cpd_status(p_node_number IN varchar2,
308 p_copy_entity_txn_id IN NUMBER) RETURN VARCHAR2 IS
309
310 st_icon Varchar2(10) := 'Y';
311
312 CURSOR csr_cpd_control_rec IS
313 SELECT bcer.information100,
314 bcer.information101,
315 bcer.information102,
316 bcer.information103,
317 bcer.information104,
318 bcer.information105,
319 bcer.information106,
320 bcer.information107,
321 bcer.information108
322 FROM ben_copy_entity_results bcer
323 WHERE bcer.copy_entity_txn_id = p_copy_entity_txn_id
324 AND bcer.table_alias = 'PQH_CORPS_TASK_LIST';
325 l_cpd_control_rec csr_cpd_control_rec%ROWTYPE;
326 BEGIN
327 OPEN csr_cpd_control_rec;
328 FETCH csr_cpd_control_rec INTO l_cpd_control_rec.information100,
329 l_cpd_control_rec.information101,
330 l_cpd_control_rec.information102,
331 l_cpd_control_rec.information103,
332 l_cpd_control_rec.information104,
333 l_cpd_control_rec.information105,
334 l_cpd_control_rec.information106,
335 l_cpd_control_rec.information107,
336 l_cpd_control_rec.information108;
337 CLOSE csr_cpd_control_rec;
338 if p_node_number = '1' then
339 --Status for Grade Ladder
340 st_icon := l_cpd_control_rec.information100;
341 elsif p_node_number = '2' then
342 --Status for Corps
343 st_icon := l_cpd_control_rec.information107;
344 elsif p_node_number = '3' then
345 --Status for Sal. Info.
346 st_icon := l_cpd_control_rec.information101;
347 elsif p_node_number = '4'then
348 --Status for Grades
349 st_icon := l_cpd_control_rec.information102;
350 elsif p_node_number = '5' then
351 --Status for Steps
352 st_icon := l_cpd_control_rec.information103;
353 elsif p_node_number = '6' then
354 --Status for Rates
355 st_icon := l_cpd_control_rec.information104;
356 elsif p_node_number = '7' then
357 --Status for Progression Rules
358 st_icon := l_cpd_control_rec.information105;
359 elsif p_node_number = '8' then
360 --Status for Corps Documents
361 st_icon := l_cpd_control_rec.information108;
362 elsif p_node_number = '9' then
363 --Status for Review and Submit
364 st_icon := l_cpd_control_rec.information106;
365 else
366 st_icon := 'N';
367 end if;
368 RETURN st_icon;
369 EXCEPTION
370 WHEN others THEN
371 return 'N';
372 END get_cpd_status;
373
374 Function chk_steps_exist_for_index(p_gross_index IN NUMBER) RETURN VARCHAR2 IS
375
376 CURSOR csr_steps_exist_for_index IS
377 SELECT 'Y'
378 FROM dual
379 WHERE EXISTS (SELECT '1'
380 FROM per_parent_spines pps,
381 per_spinal_points psp
382 WHERE psp.information1 = p_gross_index
383 AND psp.information_category = 'FR_PQH'
384 AND psp.parent_spine_id = pps.parent_spine_id
385 AND pps.information_category = 'FR_PQH'
386 AND pps.information1 = 'L');
387
388 l_status VARCHAR2(30) := 'N';
389 BEGIN
390 OPEN csr_steps_exist_for_index;
391 FETCH csr_steps_exist_for_index INTO l_status;
392 CLOSE csr_steps_exist_for_index;
393
394 RETURN l_status;
395
396 END chk_steps_exist_for_index;
397 FUNCTION bus_area_pgm_entity_exist(p_bus_area_cd IN Varchar2,
398 P_pgm_id IN NUMBER)
399 RETURN varchar2
400 IS
401
402 Cursor csr_corps_pgm_exists IS
403 SELECT 'Y'
404 FROM pqh_corps_definitions
405 WHERE ben_pgm_id = p_pgm_id;
406
407
408 l_exist Varchar2(1) := 'N';
409 BEGIN
410
411 IF p_bus_area_cd = 'PQH_GSP_TASK_LIST' THEN
412 l_exist := 'Y';
413 ELSIF p_bus_area_cd = 'PQH_CORPS_TASK_LIST' THEN
414 OPEN csr_corps_pgm_exists;
415 FETCH csr_corps_pgm_exists INTO l_exist;
416 CLOSE csr_corps_pgm_exists;
417 END IF;
418 RETURN l_exist;
419 END bus_area_pgm_entity_exist;
420
421 FUNCTION chk_primary_prof_field(p_corps_definition_id IN NUMBER
422 ,p_field_of_prof_activity_id IN NUMBER)
423 RETURN VARCHAR2 IS
424 CURSOR csr_if_prim_prof_field IS
425 SELECT 'Y'
426 FROM dual
427 WHERE EXISTS (SELECT 1
428 FROM pqh_corps_definitions
429 WHERE corps_definition_id = p_corps_definition_id
430 AND nvl(primary_prof_field_id,-1) = p_field_of_prof_activity_id);
431 l_primary varchar2(10) := 'N';
432 BEGIN
433 OPEN csr_if_prim_prof_field;
434 FETCH csr_if_prim_prof_field INTO l_primary;
435 CLOSE csr_if_prim_prof_field;
436 RETURN l_primary;
437 END chk_primary_prof_field;
438
439 FUNCTION chk_corps_info_exists(p_corps_definition_id IN NUMBER
440 ,p_information_type IN VARCHAR2)
441 RETURN VARCHAR2 IS
442 CURSOR csr_corps_info_exists(p_corps_definition_id IN NUMBER, p_information_type IN varchar2) IS
443 SELECT 'Y'
444 FROM dual
445 WHERE EXISTS (SELECT 1
446 FROM pqh_corps_extra_info
447 WHERE corps_definition_id = p_corps_definition_id
448 AND information_type = p_information_type);
449 l_info_type varchar2(30);
450 l_info_exist VARCHAR2(10) := 'N';
451 BEGIN
452 IF p_information_type = 'PQH_CORPS_ADDL_ADMIN_OVERVIEW' THEN
453 l_info_type := 'ORGANIZATION';
454 ELSIF p_information_type = 'PQH_CORPS_ADDL_EXAM_OVERVIEW' THEN
455 l_info_type := 'EXAM';
456 ELSIF p_information_type = 'PQH_CORPS_ADDL_FOP_OVERVIEW' THEN
457 l_info_type := 'FILERE';
458 ELSIF p_information_type = 'PQH_CORPS_ADDL_TRNG_OVERVIEW' THEN
459 l_info_type := 'TRAINING';
460 END IF;
461 OPEN csr_corps_info_exists(p_corps_definition_id,l_info_type);
462 FETCH csr_corps_info_exists INTO l_info_exist;
463 CLOSE csr_corps_info_exists;
464 RETURN l_info_exist;
465 END chk_corps_info_exists;
466
467 ------------------------- get_corps_name ------------------------------
468 /*
469 Author : mvankada
470 Purpose : Returns Corps Name for a given Corps Definition Id.
471 */
472
473 FUNCTION get_corps_name(p_corps_definition_id IN VARCHAR2) Return Varchar2
474 IS
475
476 Cursor csr_corps_name
477 IS
478 Select Name
479 From pqh_corps_definitions
480 Where Corps_Definition_Id = p_corps_definition_id ;
481 l_corps_name Varchar2(240) := null;
482
483 BEGIN
484 If p_corps_definition_id IS NOT NULL Then
485 Open csr_corps_name;
486 Fetch csr_corps_name into l_corps_name;
487 Close csr_corps_name;
488 End If;
489 return l_corps_name;
490 END get_corps_name;
491
492 ------------------------- los_in_months ------------------------------
493 /*
494 Author : mvankada
495 Purpose : Returns Lenth of Service (LOS) in months.
496 */
497
498
499 FUNCTION los_in_months(p_los_years IN Number,
500 p_los_months IN Number,
501 p_los_days IN Number) Return Number IS
502 l_los_in_months Number := 0;
503 BEGIN
504
505 l_los_in_months := Nvl(p_los_years,0)*12 + Nvl(p_los_months,0) + Nvl(p_los_days,0)/(365/12);
506 l_los_in_months := round(nvl(l_los_in_months,0),2);
507 return l_los_in_months;
508
509 END los_in_months;
510 ------------------------ get_from_step_name------------------------------
511 /*
512 Author : mvankada
513 Purpose : Returns Step Name for a Step Cer Id.
514 */
515
516
517 FUNCTION get_from_step_name( p_step_cer_id IN Number,
518 p_copy_entity_txn_id IN Number) Return Varchar2 IS
519 Cursor csr_seq_no
520 IS
521 Select Information263 -- Sequence Number
522 From Ben_Copy_Entity_Results
523 Where Copy_Entity_Result_Id = p_step_cer_id
524 And Table_Alias = 'COP'
525 And Copy_Entity_Txn_Id = p_copy_entity_txn_id;
526
527 Cursor csr_point_name
528 IS
529 Select Information98 -- Point Name
530 From Ben_Copy_Entity_Results
531 Where Copy_Entity_Txn_Id = p_copy_entity_txn_id
532 And Table_Alias = 'OPT'
533 And Copy_Entity_Result_id = ( select Information262 -- POINT Cer Id
534 From Ben_Copy_Entity_results
535 Where Copy_Entity_Result_id = p_step_cer_id);
536
537
538 l_point_name Varchar2(2000);
539 l_step_name Varchar2(2000) := null;
540 l_seq_no Number;
541
542 BEGIN
543 Open csr_seq_no;
544 Fetch csr_seq_no into l_seq_no;
545 Close csr_seq_no;
546
547 Open csr_point_name;
548 Fetch csr_point_name into l_point_name;
549 Close csr_point_name;
550
551 if l_point_name IS NOT NULL Then
552 l_step_name := l_seq_no || '(' || l_point_name || ')';
553 end if;
554 return l_step_name;
555 END get_from_step_name;
556
557 ------------------------ update_or_delete_crpath ------------------------------
558 /*
559 Author : mvankada
560 Purpose : Purge CRPATH Rec if Record is in Staging Area else UNLINK Record.
561 */
562
563
564 Procedure update_or_delete_crpath ( p_crpath_cer_id IN Number,
565 p_effective_date IN Date,
566 p_dml_operation IN Varchar2) IS
567
568 Cursor Csr_Ovn IS
569 Select Object_Version_Number
570 From Ben_Copy_Entity_Results
571 Where Copy_Entity_Result_Id = p_crpath_cer_id;
572 l_ovn Number;
573
574 Begin
575 Open Csr_Ovn;
576 Fetch Csr_Ovn into l_ovn;
577 Close Csr_Ovn;
578
579 If p_dml_operation = 'INSERT' Then
580 -- Purge record
581 ben_copy_entity_results_api.delete_copy_entity_results
582 ( p_copy_entity_result_id => p_crpath_cer_id,
583 p_effective_date => p_effective_date,
584 p_object_version_number => l_ovn);
585
586
587 Else
588 -- UNLINK record
589 ben_copy_entity_results_api.update_copy_entity_results
590 ( p_copy_entity_result_id => p_crpath_cer_id,
591 p_effective_date => p_effective_date,
592 p_information104 => 'UNLINK',
593 p_object_version_number => l_ovn,
594 p_information323 => null);
595
596 End If;
597 END update_or_delete_crpath;
598
599 Function decode_stage_entity(p_copy_entity_txn_id IN NUMBER,
600 p_table_alias IN VARCHAR2,
601 p_copy_entity_result_id IN NUMBER) RETURN VARCHAR2 IS
602 Cursor csr_decode_stage_entity(p_cet_id IN NUMBER,
603 p_table_alias IN varchar2,
604 p_cer_id IN NUMBER) IS
605 SELECT information5
606 FROM ben_copy_entity_results
607 WHERE copy_entity_txn_id = p_cet_id
608 AND table_alias = p_table_alias
609 AND copy_entity_result_id = p_cer_id;
610 l_name varchar2(240);
611 BEGIN
612 OPEN csr_decode_stage_entity(p_copy_entity_txn_id,p_table_alias,p_copy_entity_result_id);
613 FETCH csr_decode_stage_entity INTO l_name;
614 CLOSE csr_decode_stage_entity;
615 RETURN l_name;
616 END decode_stage_entity;
617 ----------
618 --ggnanagu
619 FUNCTION get_pgm_id (p_corps_definition_id IN NUMBER)
620 RETURN NUMBER
621 IS
622 /*
623 * Returs the Pgm Id for the corps_definition_id
624 */
625 l_pgm_id NUMBER;
626
627 BEGIN
628 SELECT ben_pgm_id
629 INTO l_pgm_id
630 FROM pqh_corps_definitions
631 WHERE corps_definition_id = p_corps_definition_id;
632 Return l_pgm_id;
633 END;
634
635 ---
636 FUNCTION is_career_def_exist(p_Copy_Entity_txn_Id IN NUMBER,
637 p_mirror_src_entity_rslt_id IN Number,
638 p_from_step_id IN NUMBER,
639 p_to_corps_id IN Number,
640 p_to_grade_id In Number,
641 p_to_step_id In Number ,
642 p_copy_entity_result_id In Number) RETURN VARCHAR2
643 IS
644 Cursor csr_career_info IS
645 Select 'Y'
646 FROM BEN_COPY_ENTITY_RESULTS
647 Where Copy_Entity_txn_Id = p_Copy_Entity_txn_Id
648 AND Gs_Mirror_Src_Entity_Result_Id = p_mirror_src_entity_rslt_id
649 AND Information234 = p_from_step_id -- p_from_step_id
650 AND Information227 = p_to_corps_id -- to_corps_id
651 AND Information228 = p_to_grade_id -- to_grade_id
652 AND nvl(Information229,1) = nvl(p_to_step_id,1)
653 AND copy_entity_result_id <> p_copy_entity_result_id; -- to_step_id
654
655 l_result varchar2(10) := 'N';
656 BEGIN
657 Open csr_career_info;
658 Fetch csr_career_info into l_result;
659 Close csr_career_info;
660
661 return l_result;
662
663 END is_career_def_exist;
664 ---
665 function get_date_of_placement(p_career_level in varchar2, p_assignment_id in number,
666 p_career_level_id in number)
667 return date
668 IS
669 Cursor csr_corps_date IS
670 Select min(asg.effective_start_date)
671 from per_all_assignments_f asg
672 where asg.grade_ladder_pgm_id = p_career_level_id
673 and assignment_id = p_assignment_id;
674
675
676 Cursor csr_grade_date IS
677 Select min(asg.effective_start_date)
678 from per_all_assignments_f asg
679 where asg.grade_id = p_career_level_id
680 and assignment_id = p_assignment_id;
681
682 Cursor csr_step_date is
683 Select min(spp.effective_start_date)
684 from per_spinal_point_placements_f spp
685 where assignment_id =p_assignment_id
686 and step_id = p_career_level_id;
687
688 --
689 l_date date;
690
691 begin
692
693 l_date := null;
694
695 If (p_career_level ='CORPS') then
696 --
697 Open csr_corps_date;
698 Fetch csr_corps_date into l_date;
699 Close csr_corps_date;
700
701 ElsIf (p_career_level = 'GRADE') then
702 --
703 Open csr_grade_date;
704 Fetch csr_grade_date into l_date;
705 Close csr_grade_date;
706 --
707 ElsIf (p_career_level ='STEP') THEN
708 --
709 Open Csr_step_date;
710 Fetch csr_step_date into L_date;
711 Close csr_step_date;
712 End if;
713
714 return l_date;
715
716 end get_date_of_placement;
717 --
718 FUNCTION get_gross_index (p_step_id IN NUMBER, p_effective_date IN DATE)
719 RETURN NUMBER
720 IS
721 CURSOR csr_gross_index
722 IS
723 SELECT psp.information1
724 FROM per_spinal_point_placements_f spp,
725 per_spinal_point_steps_f sps,
726 per_spinal_points psp
727 WHERE spp.step_id = p_step_id
728 AND p_effective_date BETWEEN spp.effective_start_date
729 AND spp.effective_end_date
730 AND spp.step_id = sps.step_id
731 AND p_effective_date BETWEEN sps.effective_start_date
732 AND sps.effective_end_date
733 AND sps.spinal_point_id = psp.spinal_point_id;
734
735 l_gross_index VARCHAR2 (240);
736 BEGIN
737 OPEN csr_gross_index;
738
739 FETCH csr_gross_index
740 INTO l_gross_index;
741
742 CLOSE csr_gross_index;
743
744 return fnd_number.canonical_to_number (l_gross_index);
745
746 END get_gross_index;
747 --
748 Function get_postStyle_of_grdldr(p_txn_id in varchar2) return varchar2 is
749
750 l_postStyle varchar2(2);
751
752 Cursor csr_postStyle is
753 Select information52 post_style
754 from ben_copy_entity_results
755 where table_alias = 'PGM'
756 and copy_entity_txn_id = to_number(p_txn_id);
757
758 Begin
759
760 open csr_postStyle;
761 fetch csr_postStyle into l_postStyle;
762 close csr_postStyle;
763 return l_poststyle;
764
765 END get_postStyle_of_grdldr;
766
767 END pqh_corps_utility;
[Home] [Help]