1 PACKAGE BODY PER_GB_PENSRV_SVPN AS
2 /* $Header: pegbasgp.pkb 120.2.12010000.3 2008/11/07 11:31:08 npannamp ship $ */
3
4 --Globals
5 gv_package_name VARCHAR2(100);
6 g_svpn_flag VARCHAR2(4);
7
8 -- This procedure is used to calculate service period numbers.
9 -- ----------------------------------------------------------------------------
10 -- |-------------------------< derive_svpn >--------------------------|
11 -- ----------------------------------------------------------------------------
12
13 PROCEDURE create_gb_spn(p_assignment_id IN NUMBER,
14 p_effective_date IN DATE)
15
16 IS
17 -- local variables
18
19 l_procedure_name VARCHAR2(100);
20 l_ass_attribute_category VARCHAR2(300);
21 l_column_name VARCHAR2(30);
22 l_pvc_information2 VARCHAR2(30);
23 l_bg_id NUMBER;
24 l_query VARCHAR2(1000);
25 l_value VARCHAR2(30);
26 l_spn NUMBER;
27 l_assignment_extra_info_id NUMBER;
28 l_object_version_number NUMBER;
29 g_assignment_id NUMBER;
30 g_effective_date DATE;
31 l_ni_number VARCHAR2(30);
32 l_pri_flag VARCHAR2(4);
33
34
35 TYPE base_table_ref_csr_typ IS REF CURSOR;
36 c_base_table base_table_ref_csr_typ;
37
38 -- Cursor to fetch the business_group_id
39
40 CURSOR get_bg_id(c_assignment_id NUMBER,
41 c_effective_date DATE)
42 IS
43 SELECT business_group_id
44 FROM per_all_assignments_f
45 WHERE assignment_id = c_assignment_id
46 AND c_effective_date BETWEEN effective_start_date AND effective_end_date;
47
48
49 -- Cursor to fetch the columns name agaist which the Pension scheme is configured
50
51 CURSOR get_penserv_config(c_business_group_id NUMBER)
52 IS
53 SELECT pcv_information1,pcv_information2
54 FROM pqp_configuration_values pcv,
55 pqp_configuration_types pct
56 WHERE pcv.pcv_information_category = pct.configuration_type
57 AND pct.configuration_type = 'PQP_GB_PENSERVER_ELIGBLTY_CONF'
58 AND pcv.business_group_id = c_business_group_id;
59
60 -- Cursor to check if emp is elegible for spn calculation
61 -- if SPN already exist, no calculation required*/
62
63 CURSOR cur_get_eit_info(c_assignment_id NUMBER)
64 IS
65 SELECT aei_information1
66 FROM per_assignment_Extra_info
67 WHERE assignment_id = c_assignment_id
68 AND aei_information_category = 'PQP_GB_PENSERV_SVPN';
69
70 -- Cursor to fetch maximum service period number
71
72 CURSOR cur_get_svpn(c_ni_number VARCHAR2)
73 IS
74 SELECT max(to_number(paei.aei_information1))+1
75 FROM per_all_people_f papf,
76 per_all_assignments_f paaf,
77 per_assignment_extra_info paei
78 WHERE papf.NATIONAL_IDENTIFIER = c_ni_number
79 AND papf.person_id = paaf.person_id
80 AND paei.assignment_id = paaf.assignment_id
81 AND paei.aei_information_category = 'PQP_GB_PENSERV_SVPN';
82
83 -- Cursor to fetch NI number for the give assignment
84 CURSOR cur_get_NI_number(c_assignment_id NUMBER,
85 c_effective_date DATE)
86 IS
87 SELECT papf1.NATIONAL_IDENTIFIER
88 FROM per_all_people_f papf1,
89 per_all_assignments_f paaf1
90 WHERE paaf1.assignment_id = c_assignment_id
91 AND c_effective_date BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
92 AND papf1.person_id = paaf1.person_id
93 AND c_effective_date BETWEEN papf1.effective_start_date AND papf1.effective_end_date;
94
95 -- Cursor to fetch Matain service period number flag
96
97 CURSOR csr_svpn_maintain_flag(c_business_group_id NUMBER)
98 IS
99 SELECT pcv_information4
100 FROM pqp_configuration_values pcv,
101 pqp_configuration_types pct
102 WHERE pcv.pcv_information_category = pct.configuration_type
103 AND pct.configuration_type = 'PQP_GB_PENSERVER_PAYPOINT_INFO'
104 AND pcv.business_group_id = c_business_group_id;
105
106 -- Cursor to fetch assignment details (Primary or not)
107 CURSOR csr_get_asg_detials(c_assignment_id NUMBER,
108 c_effective_date DATE)
109 IS
110 SELECT paaf.primary_flag
111 FROM per_all_assignments_f paaf
112 WHERE paaf.assignment_id = c_assignment_id
113 AND c_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date;
114
115 BEGIN
116
117 l_procedure_name := '.create_gb_spn';
118 g_assignment_id := p_assignment_id;
119 g_effective_date := p_effective_date;
120
121 hr_utility.set_location('attrbute_category is :'||l_ass_attribute_category, 10);
122 hr_utility.set_location('g_assignment_id is :'||g_assignment_id, 20);
123 hr_utility.set_location('g_effective_date is :'||g_effective_date, 30);
124
125 -- fetching primary assignment details
126 OPEN csr_get_asg_detials(g_assignment_id,g_effective_date);
127 FETCH csr_get_asg_detials INTO l_pri_flag;
128 CLOSE csr_get_asg_detials;
129
130
131
132 -- if the assignment is primary assignment then proceed
133 IF l_pri_flag = 'Y' THEN
134 -- fetching business groupid
135 OPEN get_bg_id(g_assignment_id,g_effective_date);
136 FETCH get_bg_id INTO l_bg_id;
137 CLOSE get_bg_id;
138
139 /* Start Bug 7132727 */
140 OPEN get_penserv_config(l_bg_id);
141 FETCH get_penserv_config INTO l_ass_attribute_category,
142 l_column_name;
143 CLOSE get_penserv_config;
144
145 IF l_ass_attribute_category is not null
146 then
147
148 /* End Bug 7132727 */
149
150 -- fetching Maintaion service period flag
151 OPEN csr_svpn_maintain_flag(l_bg_id);
152 FETCH csr_svpn_maintain_flag INTO g_svpn_flag;
153 CLOSE csr_svpn_maintain_flag;
154
155 hr_utility.set_location(' l_bg_id :'||l_bg_id,40);
156 hr_utility.set_location('g_svpn_flag'||g_svpn_flag,45);
157
158 IF g_svpn_flag = 'Yes' THEN
159
160
161 -- Query to fetch configured value for the assignment.
162 l_query := 'select '||l_column_name||' '||
163 'from per_all_assignments_f'||' '||
164 'where business_group_id = '||l_bg_id||' '||
165 'and assignment_id = '||g_assignment_id||' ';
166 IF l_ass_attribute_category <> 'Global Data Elements' THEN
167 l_query := l_query||
168 'and ASS_ATTRIBUTE_CATEGORY = '''||l_ass_attribute_category||''''||' ';
169 END IF;
170
171 l_query := l_query||
172 'and to_date('''||TO_CHAR(g_effective_date,'dd/mm/yyyy')||''',''dd/mm/yyyy'') between effective_start_date'||' '||
173 'and effective_end_date';
174
175 hr_utility.set_location('l_query: '||l_query,50);
176
177 -- fetch the value of from the column name
178 OPEN c_base_table FOR l_query;
179 FETCH c_base_table INTO l_value;
180 CLOSE c_base_table;
181
182 IF l_value IS NULL THEN
183 hr_utility.set_location('value is NULL :', 60);
184 hr_utility.set_location(' l_value'||l_value,70);
185 NULL;
186 ELSE
187 hr_utility.set_location('attribute value is not NULL :', 80);
188 hr_utility.set_location(' l_value'||l_value,90);
189
190 -- fetch EIT information
191 OPEN cur_get_eit_info(c_assignment_id => g_assignment_id);
192 FETCH cur_get_eit_info INTO l_spn;
193 CLOSE cur_get_eit_info;
194
195 hr_utility.set_location(' spn value :'||l_spn, 100);
196
197 -- Check whether service period number is null or not
198
199 IF l_spn IS NULL THEN
200 OPEN cur_get_NI_number(g_assignment_id,g_effective_date);
201 FETCH cur_get_NI_number INTO l_ni_number;
202 CLOSE cur_get_NI_number;
203
204 -- Checking whether NI number is null or not
205 IF l_ni_number IS NOT NULL
206 AND UPPER(SUBSTR(l_ni_number,1,2)) <> 'TN' THEN
207
208 OPEN cur_get_svpn(l_ni_number);
209 FETCH cur_get_svpn INTO l_spn;
210 CLOSE cur_get_svpn;
211 hr_utility.set_location(' l_spn value :'||l_spn, 105);
212 IF l_spn IS null THEN
213 l_spn := 1;
214 END IF;
215 -- Inserting the service period number
216 HR_ASSIGNMENT_EXTRA_INFO_API.create_assignment_extra_info(p_assignment_id => g_assignment_id
217 ,p_information_type => 'PQP_GB_PENSERV_SVPN'
218 ,p_aei_information_category => 'PQP_GB_PENSERV_SVPN'
219 ,p_aei_information1 => lpad(l_spn,2,'0')
220 ,p_assignment_extra_info_id => l_assignment_extra_info_id
221 ,p_object_version_number => l_object_version_number);
222 hr_utility.set_location(' inserted successfully :'||l_spn, 100);
223 END IF;
224 ELSE
225 hr_utility.set_location(' Spn calculation not required :',110);
226 END IF;
227 END IF;
228 ELSE
229 fnd_file.put_line(fnd_file.log,'Maitain Service Period No. is not set to "Yes" for the configuration PAYPOINT and CUTOVER INFORMATION
230 and hence service period number will not be populated for this assignment');
231 END IF;
232 /* Start Bug 7132727 */
233 else
234 NULL;
235 end if;
236 /* End Bug 7132727 */
237 END IF;
238 fnd_file.put_line(fnd_file.log,'Leaving ' || gv_package_name || l_procedure_name);
239
240 END create_gb_spn;
241
242
243 -- ----------------------------------------------------------------------------
244 -- Bug #7307266. FTE Calculation logic is available in PUI through PQH_GEN.pld
245 -- The same code is moved here so that SSHR can also access this.
246 -- This procedure is used to calculate FTE.
247 -- |-------------------------< auto calc FTE >--------------------------|
248 -- ----------------------------------------------------------------------------
249
250 procedure AUTO_CALC_FTE (p_assignment_id in Number,
251 P_EFFECTIVE_START_DATE in Date)
252 IS
253 l_business_group_id pqp_configuration_values.business_group_id%TYPE;
254 l_legislation_code pqp_configuration_values.legislation_code%TYPE;
255 l_abv_uom pqp_configuration_values.pcv_information1%TYPE;
256 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
257 l_effective_start_date per_all_assignments_f.effective_start_date%TYPE;
258 l_assignment_type varchar2(30);
259
260 -- added by akarmaka for the BUG:5047827
261 l_contract_type_exist NUMBER;
262 l_fte_exist NUMBER;
263
264 l_error_message fnd_new_messages.message_text%TYPE;
265 g_debug boolean := true;
266
267 -- Pick up only those UOMS which are enabled and have some
268 -- process defnition. There is no validation as yet on the configuration
269 -- which prevents the user from storing just one, however subsequent
270 -- processing cannot take place without both.
271 CURSOR csr_abvm_uoms_to_process
272 (p_business_group_id IN NUMBER
273 ,p_legislation_code IN VARCHAR2
274 ) IS
275 SELECT mtn.configuration_value_id
276 ,mtn.configuration_name
277 ,mtn.business_group_id
278 ,mtn.legislation_code
279 ,mtn.pcv_information1 uom
280 ,mtn.pcv_information2 is_enabled
281 ,def.configuration_value_id defn_config_value_id
282 ,def.configuration_name defn_config_name
283 ,def.business_group_id defn_bg_id
284 ,def.legislation_code defn_leg_code
285 ,def.pcv_information4 defn_custom_function
286 FROM pqp_configuration_values mtn
287 ,pqp_configuration_values def
288 WHERE mtn.pcv_information_category = 'PQP_ABVM_MAINTENANCE'
289 AND def.pcv_information_category = 'PQP_ABVM_DEFINITION'
290 AND def.pcv_information1 = mtn.pcv_information1
291 AND mtn.pcv_information2 = 'Y'
292 AND ( mtn.business_group_id = p_business_group_id
293 OR
294 ( ( mtn.business_group_id IS NULL AND mtn.legislation_code = p_legislation_code )
295 AND -- there does not exist a config for this UOM at bg level
296 NOT EXISTS
297 (SELECT 1
298 FROM pqp_configuration_values bgmtn
299 WHERE bgmtn.pcv_information_category = 'PQP_ABVM_MAINTENANCE'
300 AND bgmtn.pcv_information1 = mtn.pcv_information1
301 AND bgmtn.business_group_id = p_business_group_id
302 ) -- NOT EXISTS
303 ) -- OR
304 OR
305 ( ( mtn.business_group_id IS NULL AND mtn.legislation_code IS NULL )
306 AND -- there does not exist a config for this UOM at a higher level
307 NOT EXISTS
308 (SELECT 1
309 FROM pqp_configuration_values hlmtn
310 WHERE hlmtn.pcv_information_category = 'PQP_ABVM_MAINTENANCE'
311 AND hlmtn.pcv_information1 = mtn.pcv_information1
312 AND ( hlmtn.business_group_id = p_business_group_id
313 OR
314 hlmtn.legislation_code = p_legislation_code
315 )
316 ) -- NOT EXISTS
317 ) -- OR
318 ) -- AND
319 AND ( def.business_group_id = p_business_group_id
320 OR
321 def.business_group_id IS NULL AND def.legislation_code = p_legislation_code
322 OR
323 def.business_group_id IS NULL AND def.legislation_code IS NULL
324 );
325
326 -- check to find if the contract type is ever attached to this person's assignment
327 CURSOR csr_chk_asg_contract_exist( p_assignment_id IN NUMBER)
328 IS
329 SELECT 1
330 FROM pqp_assignment_attributes_f
331 WHERE assignment_id = p_assignment_id
332 AND contract_type is NOT NULL
333 AND rownum < 2;
334
335 -- chk to find if an FTE row exist for the Assignment
336 CURSOR csr_chk_asg_abv_exist(p_assignment_id IN NUMBER
337 ,p_uom IN VARCHAR2)
338 IS
339 SELECT 1
340 FROM per_assignment_budget_values_f
341 WHERE ASSIGNMENT_ID = p_assignment_id
342 and unit = p_uom ;
343
344 -- fetch assignment_type and business_group_id. Added newly for 7307266.
345 CURSOR get_ass_typ_bus_grp_id(p_assignment_id IN NUMBER,
346 p_effective_date in Date)
347 IS
348 select assignment_type, business_group_id
349 from per_all_assignments_f
350 where assignment_id = p_assignment_id
351 and p_effective_date between effective_start_date and effective_end_date ;
352
353 BEGIN --Begin Procedure
354
355 -- Added for BUG: 5287675
356 IF g_debug is null THEN
357 g_debug := hr_utility.debug_enabled;
358 END IF;
359 -- Addition ends for BUG: 5287675
360 -- hr_utility.trace_on(null,'FTE');
361 hr_utility.trace('Entering '||gv_package_name||'.AUTO_CALC_FTE');
362 hr_utility.trace('p_assignment_id :'||p_assignment_id);
363 hr_utility.trace('P_EFFECTIVE_START_DATE :'||P_EFFECTIVE_START_DATE);
364
365 BEGIN
366 l_assignment_id := p_assignment_id;
367 l_effective_start_date := P_EFFECTIVE_START_DATE;
368
369 l_legislation_code := PER_ASG_BUS1.return_legislation_code(P_ASSIGNMENT_ID => P_ASSIGNMENT_ID);
370
371 --fetch bus_grp_id and asgn_type
372 open get_ass_typ_bus_grp_id(p_assignment_id => l_assignment_id, p_effective_date => l_effective_start_date);
373 fetch get_ass_typ_bus_grp_id into l_assignment_type, l_business_group_id;
374 close get_ass_typ_bus_grp_id;
375
376 hr_utility.trace('l_business_group_id :'||l_business_group_id);
377 hr_utility.trace('l_assignment_type :'||l_assignment_type);
378 hr_utility.trace('l_legislation_code :'||l_legislation_code);
379
380
381 IF (l_assignment_id IS NOT NULL AND l_assignment_type = 'E')
382 THEN
383
384 --
385 -- This code will attempt to update the FTE value for
386 -- an assignment. It is within its own block because we don't
387 -- want to rollback the update to the assignment in the event that
388 -- the FTE processing fails for some reason.
389 hr_utility.trace('Inside '||gv_package_name||'.AUTO_CALC_FTE'||
390 l_assignment_id||' on '||
391 fnd_date.date_to_canonical(l_effective_start_date)
392 );
393 FOR this_abvm IN csr_abvm_uoms_to_process
394 (p_business_group_id => l_business_group_id
395 ,p_legislation_code => l_legislation_code
396 )
397 LOOP
398
399 IF g_debug THEN -- BUG: 5287675
400 hr_utility.trace('AUTO_CALC_FTE this_abvm.configuration_value_id:'||this_abvm.configuration_value_id);
401 hr_utility.trace('AUTO_CALC_FTE this_abvm.configuration_name:'||this_abvm.configuration_name);
402 hr_utility.trace('AUTO_CALC_FTE this_abvm.business_group_id:'||this_abvm.business_group_id);
403 hr_utility.trace('AUTO_CALC_FTE this_abvm.legislation_code:'||this_abvm.legislation_code);
404 hr_utility.trace('AUTO_CALC_FTE this_abvm.uom:'||this_abvm.uom);
405 hr_utility.trace('AUTO_CALC_FTE this_abvm.is_enabled:'||this_abvm.is_enabled);
406 hr_utility.trace('AUTO_CALC_FTE this_abvm.defn_config_value_id:'||this_abvm.defn_config_value_id);
407 hr_utility.trace('AUTO_CALC_FTE this_abvm.defn_config_name:'||this_abvm.defn_config_name);
408 hr_utility.trace('AUTO_CALC_FTE this_abvm.defn_bg_id:'||this_abvm.defn_bg_id);
409 hr_utility.trace('AUTO_CALC_FTE this_abvm.defn_leg_code:'||this_abvm.defn_leg_code);
410 hr_utility.trace('AUTO_CALC_FTE this_abvm.defn_custom_function:'||this_abvm.defn_custom_function);
411 END IF;
412
413
414 l_abv_uom := this_abvm.uom;
415
416 -- the condition below also checks for this_abvm.defn_custom_function IS NOT NULL implicitly
417 IF ( this_abvm.defn_custom_function = 'pqp_budget_maintenance.get_FTE_event_dates' )
418 THEN
419
420
421 OPEN csr_chk_asg_contract_exist (p_assignment_id => l_assignment_id );
422 FETCH csr_chk_asg_contract_exist INTO l_contract_type_exist;
423 CLOSE csr_chk_asg_contract_exist ;
424
425
426 OPEN csr_chk_asg_abv_exist (p_assignment_id => l_assignment_id
427 ,p_uom => l_abv_uom);
428 FETCH csr_chk_asg_abv_exist INTO l_fte_exist ;
429 CLOSE csr_chk_asg_abv_exist ;
430
431 IF g_debug THEN -- BUG: 5287675
432 IF l_contract_type_exist IS NOT NULL THEN
433 hr_utility.trace('l_contract_type_exist: TRUE');
434 ELSE
435 hr_utility.trace('l_contract_type_exist: FALSE');
436 END IF;
437 IF l_fte_exist IS NOT NULL THEN
438 hr_utility.trace('l_fte_exist: TRUE');
439 ELSE
440 hr_utility.trace('l_fte_exist: FALSE');
441 END IF;
442 END IF;
443
444 IF (l_contract_type_exist IS NOT NULL OR l_fte_exist IS NOT NULL )
445 THEN
446
447 pqp_budget_maintenance.maintain_abv_for_assignment
448 (p_uom => this_abvm.uom
449 ,p_assignment_id => l_assignment_id
450 ,p_business_group_id => l_business_group_id
451 ,p_effective_date => l_effective_start_date
452 ,p_action => 'Normal'
453 );
454 END IF;
455
456 ELSE -- IF this_abvm.defn_custom_function != 'pqp_budget_maintenance.get_FTE_event_dates'
457
458
459 pqp_budget_maintenance.maintain_abv_for_assignment
460 (p_uom => this_abvm.uom
461 ,p_assignment_id => l_assignment_id
462 ,p_business_group_id => l_business_group_id
463 ,p_effective_date => l_effective_start_date
464 ,p_action => 'Normal'
465 );
466
467 END IF; -- IF ( this_abvm.business_group_id IS NULL AND this_abvm.legislation_code = 'GB' )
468
469 END LOOP; -- FOR this_abvm IN csr_abvm_uoms_to_process
470
471 IF g_debug THEN -- BUG: 5287675
472 hr_utility.trace('Leaving '||gv_package_name||'.AUTO_CALC_FTE'||' for '||
473 l_assignment_id||' on '||
474 fnd_date.date_to_canonical(l_effective_start_date)
475 );
476 END IF;
477 END IF; -- IF (l_assignment_id IS NOT NULL AND l_assignment_type = 'E')
478
479 END;
480 EXCEPTION
481 WHEN OTHERS THEN
482 hr_utility.trace('Error in '||gv_package_name||'.AUTO_CALC_FTE'||' for '||
483 l_assignment_id||' on '||
484 fnd_date.date_to_canonical(l_effective_start_date)
485 );
486 --fnd_message.RETRIEVE;
487 l_error_message := fnd_message.get;
488 l_error_message := NVL(RTRIM(LTRIM(l_error_message)),'SQLERRM:'||SQLERRM);
489 IF g_debug THEN -- BUG: 5287675
490 hr_utility.trace(gv_package_name||'.AUTO_CALC_FTE : Error msg '||l_error_message);
491 END IF;
492 hr_utility.set_message(8303,'PQP_230514_FTE_FAILURE');
493 fnd_message.set_token('ABVUOM',l_abv_uom);
494 fnd_message.set_token('ERRORMSG',l_error_message);
495 hr_utility.set_warning;
496
497
498 END AUTO_CALC_FTE;
499
500 BEGIN
501 gv_package_name := 'per_gb_pensrv_svpn';
502 END per_gb_pensrv_svpn;
503
[Home] [Help]