[Home] [Help]
PACKAGE BODY: APPS.PAY_FR_RUN_TYPES
Source
1 Package Body pay_fr_run_types As
2 /* $Header: pyfrrunt.pkb 115.4 2004/04/15 02:21:24 autiwari noship $ */
3 g_package Constant varchar2(30):= 'pay_fr_run_types';
4 g_called_from Varchar2(18) := Null;
5 l_index Number := 0;
6 l_prod_flag Boolean := True;
7 l_user_flag Boolean := True;
8 --
9 Procedure element_run_types(p_element_type_id In Number
10 )
11 Is
12 l_proc varchar2(60) := g_package||'.element_run_types';
13 l_user_table_name varchar2(30) := 'FR_RUN_TYPE_RULES';
14 l_rule_type Varchar2(60) := Null;
15 l_rule Varchar2(10) := Null;
16 l_run_type Varchar2(20) := Null;
17 l_run_type_id Number;
18 l_element_type_usage_id Number;
19 l_object_version_number Number;
20 l_effective_start_date Date;
21 l_effective_end_date Date;
22 l_print_element_info Varchar2(500);
23
24
25
26 Cursor csr_element_info(c_element_type_id Number) Is
27 Select pet.element_type_id,
28 pet.element_name,
29 pec.classification_name,
30 pet.business_group_id business_group_id,
31 pet.legislation_code,
32 pet.indirect_only_flag,
33 min(pet.effective_start_date) effective_date
34 From
35 pay_element_types_f pet,
36 pay_element_classifications pec,
37 per_business_groups pbg
38 Where
39 pet.element_type_id = c_element_type_id
40 and pet.classification_id = pec.classification_id
41 and nvl(pet.business_group_id,pbg.business_group_id) = pbg.business_group_id
42 and nvl(pet.legislation_code,pbg.legislation_code) = 'FR'
43 Group By
44 pet.element_type_id,
45 pet.element_name,
46 pec.classification_name,
47 pet.business_group_id,
48 pet.legislation_code,
49 pet.indirect_only_flag;
50
51
52 Cursor csr_row_id(c_element_name Varchar2,c_classification_name Varchar2,csr_effective_date date) Is
53 select pur.row_low_range_or_name row_id,puci.value
54 from pay_user_tables put
55 , pay_user_rows_f pur
56 , pay_user_columns puc
57 , pay_user_column_instances_f puci
58 , pay_user_columns puc_rule_type
59 , pay_user_column_instances_f puci_rule_type
60 where put.user_table_name = 'FR_RUN_TYPE_RULES'
61 and put.legislation_code = 'FR'
62 and put.business_group_id is null
63 and puc.user_table_id = put.user_table_id
64 and puc.user_column_name = 'ID'
65 and pur.user_table_id = put.user_table_id
66 and csr_effective_date
67 between pur.effective_start_date and pur.effective_end_date
68 and puci.user_column_id = puc.user_column_id
69 and puci.user_row_id = pur.user_row_id
70 and puci.value in (c_element_name , c_classification_name)
71 and csr_effective_date
72 between puci.effective_start_date and puci.effective_end_date
73 and puc_rule_type.user_table_id = put.user_table_id
74 and puc_rule_type.user_column_name = 'Rule Type'
75 and puci_rule_type.user_column_id = puc_rule_type.user_column_id
76 and puci_rule_type.user_row_id = pur.user_row_id
77 and csr_effective_date
78 between puci_rule_type.effective_start_date and puci_rule_type.effective_end_date
79 order by puci_rule_type.value;
80 --
81 Cursor get_usage_info(c_run_type Varchar2) Is
82 Select etu.element_type_usage_id
83 ,etu.effective_start_date effective_date
84 ,etu.object_version_number
85 ,etu.business_group_id
86 ,etu.legislation_code
87 From pay_element_type_usages_f etu
88 ,pay_run_types_f rt
89 Where rt.legislation_code = 'FR'
90 And rt.shortname = c_run_type
91 And etu.run_type_id = rt.run_type_id
92 And etu.element_type_id = p_element_type_id;
93
94
95 l_element_info csr_element_info%ROWTYPE;
96 l_row_id csr_row_id%ROWTYPE;
97 l_run_type_usage_rec get_usage_info%ROWTYPE;
98 --
99
100 Function get_run_type_id(p_run_type varchar2,p_legislation_code varchar2)
101 Return Number
102 Is
103
104 Cursor csr_run_type_id Is
105 Select run_type_id
106 From pay_run_types_f
107 Where shortname = p_run_type
108 and legislation_code = p_legislation_code;
109
110 l_run_type_id pay_run_types.run_type_id%TYPE;
111
112 Begin
113
114 Open csr_run_type_id ;
115 Fetch csr_run_type_id into l_run_type_id;
116 Close csr_run_type_id;
117
118 Return l_run_type_id;
119
120 End get_run_type_id;
121
122 --
123
124
125 Function get_table_value (p_bus_group_id in number,
126 p_table_name in varchar2,
127 p_col_name in varchar2,
128 p_row_value in varchar2,
129 p_effective_date in date )
130 Return varchar2 is
131
132 l_value pay_user_column_instances_f.value%TYPE;
133
134 Cursor csr_get_table_value Is
135 Select puci.value
136 From pay_user_tables put
137 ,pay_user_rows_f pur
138 ,pay_user_columns puc
139 ,pay_user_column_instances_f puci
140 Where put.user_table_name = p_table_name
141 and put.legislation_code = 'FR'
142 and put.business_group_id Is Null
143 and put.user_table_id = pur.user_table_id
144 and pur.row_low_range_or_name = p_row_value
145 and ((pur.legislation_code = 'FR'and pur.business_group_id Is Null)
146 or
147 (pur.legislation_code Is Null and pur.business_group_id =p_bus_group_id))
148 and p_effective_date Between pur.effective_start_date And pur.effective_end_date
149 and puc.user_table_id = put.user_table_id
150 and puc.user_column_name = p_col_name
151 and puc.legislation_code = 'FR'
152 and puc.business_group_id Is Null
153 and puci.user_row_id = pur.user_row_id
154 and puci.user_column_id = puc.user_column_id
155 and p_effective_date Between puci.effective_start_date And puci.effective_end_date;
156
157
158
159 Begin
160
161 Open csr_get_table_value;
162 Fetch csr_get_table_value Into l_value;
163 Close csr_get_table_value;
164
165 Return l_value;
166
167 End get_table_value;
168
169
170
171
172
173 Begin
174 hr_utility.set_location('Entering ' || l_proc,10);
175 Open csr_element_info(p_element_type_id);
176 Fetch csr_element_info into l_element_info;
177 Close csr_element_info;
178
179 --Check For indirect only flag
180 --If 'Y' don't Maintain usages
181 If l_element_info.indirect_only_flag = 'N' Then
182 --
183
184 IF l_element_info.legislation_code IS NOT NULL THEN
185 hr_startup_data_api_support.enable_startup_mode('STARTUP');
186
187 If l_prod_flag Then
188 l_prod_flag := False;
189 hr_startup_data_api_support.create_owner_definition('PER');
190 hr_startup_data_api_support.create_owner_definition('PAY');
191 End If;
192
193 ELSE
194 hr_startup_data_api_support.enable_startup_mode('USER');
195 END IF;
196
197
198 --
199 For l_row_id In csr_row_id(l_element_info.element_name,l_element_info.classification_name,l_element_info.effective_date) Loop
200
201
202 Begin
203 l_rule_type := get_table_value( p_bus_group_id => l_element_info.business_group_id
204 ,p_table_name =>l_user_table_name
205 ,p_col_name =>'Rule Type'
206 ,p_row_value =>l_row_id.row_id
207 ,p_effective_date =>l_element_info.effective_date
208 );
209 Exception
210 When Others Then
211 Null;
212 End;
213
214 If l_rule_type = 'ELEMENT' Then
215 l_rule :=get_table_value( p_bus_group_id =>l_element_info.business_group_id
216 ,p_table_name =>l_user_table_name
217 ,p_col_name =>'Rule'
218 ,p_row_value =>l_row_id.row_id
219 ,p_effective_date =>l_element_info.effective_date
220 );
221
222 If l_rule = 'EXCLUDE' Then
223
224 l_run_type := get_table_value( p_bus_group_id =>l_element_info.business_group_id
225 ,p_table_name =>l_user_table_name
226 ,p_col_name =>'Run Type'
227 ,p_row_value =>l_row_id.row_id
228 ,p_effective_date =>l_element_info.effective_date
229 );
230
231 l_run_type_id := get_run_type_id(l_run_type,'FR');
232
233 hr_utility.set_location('Creating Run Type Usage for Element'||l_element_info.element_name||' '||l_proc,20);
234
235 Begin
236
237 pay_element_type_usage_api.create_element_type_usage(
238 p_effective_date => l_element_info.effective_date
239 ,p_run_type_id => l_run_type_id
240 ,p_element_type_id => p_element_type_id
241 ,p_business_group_id => l_element_info.business_group_id
242 ,p_legislation_code => l_element_info.legislation_code
243 ,p_element_type_usage_id => l_element_type_usage_id
244 ,p_object_version_number => l_object_version_number
245 ,p_effective_start_date => l_effective_start_date
246 ,p_effective_end_date => l_effective_end_date
247 );
248 If g_called_from = 'Concurrent_Program' Then
249 l_index := l_index + 1;
250
251 l_print_element_info:= rpad(nvl(to_char(l_index),' '),3)||' '||
252 rpad(nvl(l_element_info.element_name,' '),45)||' '||
253 rpad(nvl(l_element_info.classification_name,' '),20)||' '||
254 rpad(nvl(l_run_type,' '),10)||' '||
255 rpad(nvl(to_char(l_element_info.business_group_id),' ') ,17)||' '||
256 rpad(nvl(l_element_info.legislation_code,' '),16)|| ' '||
257 rpad('Exclude',7);
258
259 Fnd_file.put_line(FND_FILE.OUTPUT,l_print_element_info);
260 End If;
261 Exception
262 When Others then
263 If g_called_from = 'Concurrent_Program' Then
264 l_index := l_index + 1;
265 l_print_element_info:= rpad(nvl(to_char(l_index),' '),3)||' '||
266 rpad(nvl(l_element_info.element_name,' '),45)||' '||
267 rpad(nvl(l_element_info.classification_name,' '),20)||' '||
268 rpad(nvl(l_run_type,' '),10)||' '||
269 rpad(nvl(to_char(l_element_info.business_group_id),' ') ,17)||' '||
270 rpad(nvl(l_element_info.legislation_code,' '),16)|| ' '||
271 rpad('Invalid',7);
272 Fnd_file.put_line(FND_FILE.OUTPUT,l_print_element_info);
273 End If;
274 End;
275
276
277 Elsif l_rule = 'INCLUDE' Then
278 l_run_type := get_table_value( p_bus_group_id =>l_element_info.business_group_id
279 ,p_table_name =>l_user_table_name
280 ,p_col_name =>'Run Type'
281 ,p_row_value =>l_row_id.row_id
282 ,p_effective_date =>l_element_info.effective_date
283 );
284
285 Open get_usage_info(l_run_type);
286 Fetch get_usage_info Into l_run_type_usage_rec;
287
288 If get_usage_info%FOUND Then
289 pay_element_type_usage_api.delete_element_type_usage( p_validate =>null
290 ,p_effective_date =>l_run_type_usage_rec.effective_date
291 ,p_datetrack_delete_mode =>'ZAP'
292 ,p_element_type_usage_id =>l_run_type_usage_rec.element_type_usage_id
293 ,p_object_version_number =>l_run_type_usage_rec.object_version_number
294 ,p_business_group_id =>l_run_type_usage_rec.business_group_id
295 ,p_legislation_code =>l_run_type_usage_rec.legislation_code
296 ,p_effective_start_date =>l_effective_start_date
297 ,p_effective_end_date =>l_effective_end_date
298 );
299 End If;
300 Close get_usage_info;
301
302 If g_called_from = 'Concurrent_Program' Then
303 l_index := l_index + 1;
304
305 l_print_element_info:= rpad(nvl(to_char(l_index),' '),3)||' '||
306 rpad(nvl(l_element_info.element_name,' '),45)||' '||
307 rpad(nvl(l_element_info.classification_name,' '),20)||' '||
308 rpad(nvl(l_run_type,' '),10)||' '||
309 rpad(nvl(to_char(l_element_info.business_group_id),' ') ,17)||' '||
310 rpad(nvl(l_element_info.legislation_code,' '),16)|| ' '||
311 rpad('Include',7);
312
313 Fnd_file.put_line(FND_FILE.OUTPUT,l_print_element_info);
314
315 End If;
316
317 End If; -- l_rule (INCLUDE/EXCLUDE)
318
319
320 Else -- l_rule_type IS NOT ELEMENT
321 Begin
322 l_rule_type :=get_table_value( p_bus_group_id =>l_element_info.business_group_id
323 ,p_table_name =>l_user_table_name
324 ,p_col_name =>'Rule Type'
325 ,p_row_value =>l_row_id.row_id
326 ,p_effective_date =>l_element_info.effective_date
327 );
328 Exception
329 When Others Then
330 Null;
331 End;
332
333 IF l_rule_type = 'CLASSIFICATION' Then
334 l_rule :=get_table_value( p_bus_group_id =>l_element_info.business_group_id
335 ,p_table_name =>l_user_table_name
336 ,p_col_name =>'Rule'
337 ,p_row_value =>l_row_id.row_id
338 ,p_effective_date =>l_element_info.effective_date
339 );
340
341 If l_rule = 'EXCLUDE' Then
342
343 l_run_type := get_table_value( p_bus_group_id =>l_element_info.business_group_id
344 ,p_table_name =>l_user_table_name
345 ,p_col_name =>'Run Type'
346 ,p_row_value =>l_row_id.row_id
347 ,p_effective_date =>l_element_info.effective_date
348 );
349
350 l_run_type_id := get_run_type_id(l_run_type,'FR');
351
352 hr_utility.set_location('Creating Usage for Element '||l_element_info.element_name||' '||l_proc,30);
353
354 Begin
355
356 pay_element_type_usage_api.create_element_type_usage(
357 p_effective_date => l_element_info.effective_date
358 ,p_run_type_id => l_run_type_id
359 ,p_element_type_id => p_element_type_id
360 ,p_business_group_id => l_element_info.business_group_id
361 ,p_legislation_code => l_element_info.legislation_code
362 ,p_element_type_usage_id => l_element_type_usage_id
363 ,p_object_version_number => l_object_version_number
364 ,p_effective_start_date => l_effective_start_date
365 ,p_effective_end_date => l_effective_end_date
366 );
367
368 If g_called_from = 'Concurrent_Program' Then
369 l_index := l_index + 1;
370
371 l_print_element_info:= rpad(nvl(to_char(l_index),' '),3)||' '||
372 rpad(nvl(l_element_info.element_name,' '),45)||' '||
373 rpad(nvl(l_element_info.classification_name,' '),20)||' '||
374 rpad(nvl(l_run_type,' '),10)||' '||
378
375 rpad(nvl(to_char(l_element_info.business_group_id),' '),17)||' '||
376 rpad(nvl(l_element_info.legislation_code,' '),16)|| ' '||
377 rpad('Exclude',7);
379 Fnd_file.put_line(FND_FILE.OUTPUT,l_print_element_info);
380 End If;
381
382
383
384 Exception
385 When Others Then
386
387 If g_called_from = 'Concurrent_Program' Then
388 l_index := l_index + 1;
389
390 l_print_element_info:= rpad(nvl(to_char(l_index),' '),3)||' '||
391 rpad(nvl(l_element_info.element_name,' '),45)||' '||
392 rpad(nvl(l_element_info.classification_name,' '),20)||' '||
393 rpad(nvl(l_run_type,' '),10)||' '||
394 rpad(nvl(to_char(l_element_info.business_group_id),' ') ,17)||' '||
395 rpad(nvl(l_element_info.legislation_code,' '),16)|| ' '||
396 rpad('Invalid',7);
397 Fnd_file.put_line(FND_FILE.OUTPUT,l_print_element_info);
398 End If;
399 End;
400 End IF;--If l_rule = 'EXCLUDE'
401 End If; --IF l_rule_type = 'CLASSIFICATION'
402
403
404 End If; -- IF Direct Elements only
405 End Loop;
406
407
408 End If;
409
410 End element_run_types;
411
412
413 --
414 Procedure rebuild_run_types(errbuf out nocopy Varchar2,
415 retcode out nocopy Varchar2
416 )
417 Is
418
419 l_proc varchar2(60) := g_package||'.rebuild_run_types';
420
421 Cursor csr_run_type_usage_all Is
422 Select etu.element_type_usage_id
423 ,etu.effective_start_date effective_date
424 ,etu.object_version_number
425 ,etu.business_group_id
426 ,etu.legislation_code
427 From pay_element_type_usages_f etu
428 ,pay_run_types_f rt
429 Where rt.legislation_code = 'FR'
430 And rt.shortname In ('STANDARD', 'NET', 'SICKNESS')
431 And etu.run_type_id = rt.run_type_id;
432
433 Cursor csr_element_all Is
434 Select pet.element_type_id
435 ,pet.element_name
436 ,pet.business_group_id
437 ,pet.legislation_code
438 ,Min(pet.effective_start_date) effective_date
439 From pay_element_types_f pet
440 ,per_business_groups pbg
441 Where Nvl(pet.indirect_only_flag, 'N') = 'N'
442 And pbg.business_group_id = pet.business_group_id
443 And pbg.legislation_code = 'FR'
444 And pet.legislation_code Is Null
445 Group By pet.element_type_id,
446 pet.element_name,
447 pet.business_group_id,
448 pet.legislation_code
449 union all
450 Select pet.element_type_id
451 ,pet.element_name
452 ,pet.business_group_id
453 ,pet.legislation_code
454 ,Min(pet.effective_start_date) effective_date
455 From pay_element_types_f pet
456 Where Nvl(pet.indirect_only_flag, 'N') = 'N'
457 And pet.legislation_code = 'FR'
458 And pet.business_group_id Is Null
459 Group By pet.element_type_id,
460 pet.element_name,
461 pet.business_group_id,
462 pet.legislation_code ;
463
464
465 l_run_type_usage_rec csr_run_type_usage_all%ROWTYPE;
466 l_element_rec csr_element_all%ROWTYPE;
467 l_effective_start_date Date;
468 l_effective_end_date Date;
469 l_header Varchar2(500);
470 l_underline Varchar2(500);
471
472
473 Begin
474 --hr_utility.trace_on(null ,'PAY_FR_RUN_TYPES');
475
476 If g_called_from IS Null Then
477
478 g_called_from := 'Concurrent_Program';
479
480 End If;
481 If g_called_from = 'Concurrent_Program' Then
482
483 l_header := rpad('No',3)||' '||
484 rpad('Element',45)||' '||
485 rpad('Classification',20)||' '||
486 rpad('Run Type',10)||' '||
487 rpad('Business Group Id',17)||' '||
488 rpad('Legislation Code',16)|| ' '||
489 rpad('Status',7);
490
491 l_underline :=rpad('-',03,'-')||' '||
492 rpad('-',45,'-')||' '||
493 rpad('-',20,'-')||' '||
494 rpad('-',10,'-')||' '||
495 rpad('-',17,'-')||' '||
496 rpad('-',16,'-')||' '||
497 rpad('-',07,'-');
498
499 Fnd_File.New_Line(FND_FILE.OUTPUT,1);
500 Fnd_file.put_line(FND_FILE.OUTPUT,'Rebuilt Element Exclusions (France)');
501 Fnd_File.New_Line(FND_FILE.OUTPUT,1);
502 Fnd_file.put_line(FND_FILE.OUTPUT,l_header);
503 Fnd_file.put_line(FND_FILE.OUTPUT,l_underline);
504
505 End If;
506
507 Fnd_file.put_line(FND_FILE.OUTPUT,'Deleting Usages');
508 --1.Delete all existing usages
509
510 For l_run_type_usage_rec In csr_run_type_usage_all Loop
511
512 hr_utility.set_location('Deleting run type usage '||l_proc,10);
513
514 If l_run_type_usage_rec.legislation_code Is Null
515 Then
516 hr_startup_data_api_support.enable_startup_mode('USER');
517 ElsIF l_run_type_usage_rec.business_group_id Is Null Then
518 hr_startup_data_api_support.enable_startup_mode('STARTUP');
519 End If;
520
521 pay_element_type_usage_api.delete_element_type_usage( p_validate =>null
522 ,p_effective_date =>l_run_type_usage_rec.effective_date
523 ,p_datetrack_delete_mode =>'ZAP'
524 ,p_element_type_usage_id =>l_run_type_usage_rec.element_type_usage_id
525 ,p_object_version_number =>l_run_type_usage_rec.object_version_number
526 ,p_business_group_id =>l_run_type_usage_rec.business_group_id
527 ,p_legislation_code =>l_run_type_usage_rec.legislation_code
528 ,p_effective_start_date =>l_effective_start_date
529 ,p_effective_end_date =>l_effective_end_date
530 );
531
532 End Loop;
533
534
535 --2.Create fresh run type usages
536
537 For l_element_rec In csr_element_all Loop
538 hr_utility.set_location('Creating run type usage For Element '||l_element_rec.element_name||l_proc,20);
539
540 element_run_types( p_element_type_id => l_element_rec.element_type_id
541 );
542
543 End Loop;
544
545
546
547 End rebuild_run_types;
548
549 Procedure rebuild_run_types IS
550
551 l_errbuf varchar2(1000);
552 l_retcode varchar2(500);
553
554 Begin
555 g_called_from := 'Hrglobal';
556 rebuild_run_types( errbuf =>l_errbuf
557 ,retcode =>l_retcode
558 );
559
560 End rebuild_run_types;
561
562 --
563 End pay_fr_run_types;
564