[Home] [Help]
PACKAGE BODY: APPS.PQP_PRORATION_WRAPPER
Source
1 PACKAGE BODY pqp_proration_wrapper AS
2 /* $Header: pqprowiz.pkb 115.15 2002/03/17 10:51:15 pkm ship $ */
3
4 CURSOR c_biz_group(p_business_group_name IN VARCHAR2) IS
5 SELECT business_group_id
6 FROM per_business_groups
7 WHERE UPPER(name) = UPPER(p_business_group_name);
8
9 CURSOR c_dated_pay_table(p_table_name IN VARCHAR2) IS
10 SELECT dated_table_id
11 FROM pay_dated_tables
12 WHERE UPPER(table_name) = UPPER(p_table_name);
13
14 CURSOR c_primary_classification(p_primary_class IN VARCHAR2) IS
15 SELECT default_priority
16 FROM pay_element_classifications
17 WHERE NVL(legislation_code, 'GB') = 'GB'
18 AND UPPER(classification_name) = UPPER(p_primary_class);
19
20 CURSOR c_element_id(p_ele_name IN VARCHAR2) IS
21 SELECT element_type_id
22 FROM pay_element_types_f
23 WHERE UPPER(element_name) = UPPER(LTRIM(RTRIM(p_ele_name)));
24
25 CURSOR c_input_value(p_element_type_id IN NUMBER ,
26 p_input_value_name IN VARCHAR2 ) IS
27 SELECT input_value_id
28 FROM pay_input_values_f
29 WHERE element_type_id = p_element_type_id
30 AND name = LTRIM(RTRIM(p_input_value_name));
31
32 CURSOR c_formula_id(p_formula_name IN VARCHAR2) IS
33 SELECT formula_id
34 FROM ff_formulas_f
35 WHERE formula_name = UPPER(p_formula_name);
36
37 CURSOR c_element_extra_info_cnt(p_ele_id IN NUMBER) IS
38 SELECT COUNT(*) count
39 FROM pay_element_type_extra_info
40 WHERE element_type_id = p_ele_id
41 AND information_type = 'PQP_UK_ELEMENT_ATTRIBUTION'
42 AND eei_information_category = 'PQP_UK_ELEMENT_ATTRIBUTION';
43
44 CURSOR c_element_extra_info_id(p_ele_id IN NUMBER) IS
45 SELECT element_type_extra_info_id,
46 object_version_number
47 FROM pay_element_type_extra_info
48 WHERE element_type_id = p_ele_id
49 AND information_type = 'PQP_UK_ELEMENT_ATTRIBUTION'
50 AND eei_information_category = 'PQP_UK_ELEMENT_ATTRIBUTION';
51
52 CURSOR c_event_group_id(p_pg_name IN VARCHAR) IS
53 SELECT event_group_id
54 FROM pay_event_groups
55 WHERE UPPER(event_group_name) = UPPER(p_pg_name);
56
57 CURSOR c_formula_text(p_formula_name IN VARCHAR) IS
58 SELECT formula_text
59 FROM ff_formulas_f ff
60 WHERE ff.formula_name = p_formula_name
61 AND ff.legislation_code = 'GB'
62 AND ff.business_group_id IS NULL;
63
64 CURSOR c_fast_formula_id(p_formula_name IN VARCHAR,
65 p_business_group_id IN NUMBER) IS
66 SELECT formula_id
67 FROM ff_formulas_f ff
68 WHERE RTRIM(LTRIM(UPPER(ff.formula_name)))
69 = RTRIM(LTRIM(UPPER(p_formula_name)))
70 AND ff.legislation_code IS NULL
71 AND ff.business_group_id = p_business_group_id;
72
73 gv_package varchar2(100) := 'pqp_proration_wrapper';
74 --*************************************************************************
75 -- Procedure : Valid business group
76 --*************************************************************************
77
78 PROCEDURE valid_business_group(p_business_group_name IN per_business_groups.name%TYPE)
79 AS
80 l_exists varchar2(2) := 'N';
81 lv_procedure_name VArchar2(80) := '.valid_business_group';
82 BEGIN
83 hr_utility.set_location('Entering {' || gv_package || lv_procedure_name, 10);
84 begin
85 SELECT 'Y'
86 INTO l_exists
87 FROM dual
88 WHERE EXISTS
89 (SELECT null
90 FROM PER_BUSINESS_GROUPS
91 WHERE name = p_business_group_name
92 );
93 EXCEPTION
94 WHEN NO_DATA_FOUND THEN
95 hr_utility.set_message(800, 'HR_7208_API_BUS_GRP_INVALID');
96 hr_utility.raise_error;
97 END;
98 hr_utility.set_location('Exiting }' || gv_package ||
99 lv_procedure_name, 50);
100 --
101 --
102 END;
103 --*************************************************************************
104 -- Procedure : Enable_Dynamic_Triggers
105 --*************************************************************************
106
107 PROCEDURE enable_dynamic_triggers
108 (
109 p_business_group_id IN NUMBER ,
110 p_salary_flag IN VARCHAR2 ,
111 p_grade_flag IN VARCHAR2 ,
112 p_spinal_flag IN VARCHAR2 ,
113 p_address_flag IN VARCHAR2 ,
114 p_location_flag IN VARCHAR2
115 )
116 AS
117 l_area_id NUMBER ;
118 l_business_group_id NUMBER ;
119 l_count NUMBER ;
120 l_row_id ROWID ;
121 l_usage_id NUMBER ;
122 l_select_statement VARCHAR2(1000) ;
123 l_select_flag VARCHAR2(6) ;
124 l_salary_flag VARCHAR2(6) := 'FALSE' ;
125 l_grade_flag VARCHAR2(6) := 'FALSE' ;
126 l_spinal_flag VARCHAR2(6) := 'FALSE' ;
127 l_address_flag VARCHAR2(6) := 'FALSE' ;
128 l_location_flag VARCHAR2(6) := 'FALSE' ;
129 l_short_name VARCHAR2(80) ;
130 lv_procedure_name VARCHAR2(50) := '.enable_dynamic_triggers';
131 l_event_id NUMBER ;
132 l_dummy NUMBER ;
133
134 l_cursor_num NUMBER ;
135
136 TYPE c_dt_cursor IS REF CURSOR ;
137
138 c_dtc c_dt_cursor;
139
140 CURSOR c_functional_areas IS
141 SELECT area_id
142 FROM pay_functional_areas
143 WHERE short_name = 'INCIDENT REGISTER';
144
145 CURSOR c_functional_usages(p_area_id IN NUMBER ,
146 p_business_group_Id IN NUMBER ) IS
147 SELECT COUNT(*) count
148 FROM pay_functional_usages
149 WHERE area_id = p_area_id
150 AND business_group_id = p_business_group_id;
151 BEGIN
152 hr_utility.set_location('Entering {' || gv_package ||
153 lv_procedure_name, 10);
154
155 l_business_group_id := p_business_group_id;
156 l_salary_flag := p_salary_flag ;
157 l_grade_flag := p_grade_flag ;
158 l_spinal_flag := p_spinal_flag ;
159 l_address_flag := p_address_flag ;
160 l_location_flag := p_location_flag ;
161
162 l_select_statement := 'SELECT pte.short_name,
163 pte.event_id
164 FROM pay_functional_areas pfa,
165 pay_functional_triggers pft,
166 pay_trigger_events pte
167 WHERE pte.event_id = pft.event_id
168 AND pft.area_id = pfa.area_id
169 AND pfa.short_name = ''INCIDENT REGISTER''
170 AND pte.short_name IN (';
171
172 l_select_flag := 'FALSE';
173
174 IF (l_salary_flag = 'TRUE') THEN
175
176 l_select_flag := 'TRUE';
177
178 l_select_statement := l_select_statement ||
179 '''PAY_ELEMENT_ENTRIES_F_ARD'',
180 ''PAY_ELEMENT_ENTRIES_F_ARI'',
181 ''PAY_ELEMENT_ENTRIES_F_ARU'',
182 ''PAY_ELEMENT_ENTRY_VALUES_F_ARU''';
183 END IF;
184
185 IF (l_grade_flag = 'TRUE') THEN
186
187
188 IF (l_select_flag = 'TRUE') THEN
189 l_select_statement := l_select_statement ||
190 ', ''PAY_GRADE_RULES_F_ARU'',
191 ''PER_ALL_ASSIGNMENTS_F_ARU''';
192 ELSE
193 l_select_statement := l_select_statement ||
194 '''PAY_GRADE_RULES_F_ARU'',
195 ''PER_ALL_ASSIGNMENTS_F_ARU''';
196 END IF;
197 l_select_flag := 'TRUE';
198 END IF;
199
200 IF (l_spinal_flag = 'TRUE') THEN
201
202
203 IF (l_select_flag = 'TRUE') THEN
204 l_select_statement := l_select_statement ||
205 ',''PER_SPINAL_POINT_PLACEMENTS_F_ARU'',
206 ''PER_ALL_ASSIGNMENTS_F_ARU''';
207 ELSE
208 l_select_statement := l_select_statement ||
209 '''PER_SPINAL_POINT_PLACEMENTS_F_ARU'',
210 ''PER_ALL_ASSIGNMENTS_F_ARU''';
211 END IF;
212 l_select_flag := 'TRUE';
213 END IF;
214
215 IF (l_address_flag = 'TRUE') THEN
216
217 IF (l_select_flag = 'TRUE') THEN
218 l_select_statement := l_select_statement ||
219 ',''PER_ADDRESSES_ARU''';
220 ELSE
221 l_select_statement := l_select_statement ||
222 '''PER_ADDRESSES_ARU''';
223 END IF;
224 l_select_flag := 'TRUE';
225 END IF;
226
227 IF (l_location_flag = 'TRUE') THEN
228
229 IF (l_select_flag = 'TRUE') THEN
230 l_select_statement := l_select_statement ||
231 ',''PER_ALL_ASSIGNMENTS_F_ARU''';
232 ELSE
233 l_select_statement := l_select_statement ||
234 '''PER_ALL_ASSIGNMENTS_F_ARU''';
235 END IF;
236 l_select_flag := 'TRUE';
237 END IF;
238
239 IF (l_select_flag = 'FALSE') THEN
240 l_select_statement := l_select_statement || 'NULL)';
241 ELSE
242 l_select_statement := l_select_statement || ')';
243
244 l_cursor_num := DBMS_SQL.OPEN_CURSOR;
245
246 DBMS_SQL.PARSE(l_cursor_num, l_select_statement, DBMS_SQL.V7);
247 DBMS_SQL.DEFINE_COLUMN(l_cursor_num, 1, l_short_name, 80);
248 DBMS_SQL.DEFINE_COLUMN(l_cursor_num, 2, l_event_id);
249
250 l_dummy := DBMS_SQL.EXECUTE(l_cursor_num);
251
252 END IF;
253
254 LOOP
255 IF DBMS_SQL.FETCH_ROWS(l_cursor_num) = 0 THEN
256 EXIT;
257 END IF;
258
259 DBMS_SQL.COLUMN_VALUE(l_cursor_num, 1, l_short_name );
260 DBMS_SQL.COLUMN_VALUE(l_cursor_num, 2, l_event_id );
261
262 UPDATE pay_trigger_components
263 SET enabled_flag = 'Y'
264 WHERE event_id = l_event_id;
265
266 UPDATE pay_trigger_events
267 SET generated_flag = 'Y',
268 enabled_flag = 'Y'
269 WHERE event_id = l_event_id;
270
271 pay_dyn_triggers.generate_trigger_event(l_short_name);
272 hr_utility.trace('Generate Trigger Event');
273
274 END LOOP;
275
276 FOR c1 IN c_functional_areas
277 LOOP
278 l_area_id := c1.area_id;
279 END LOOP;
280
281 FOR c2 IN c_functional_usages (l_area_id,
282 l_business_Group_id) LOOP
283 l_count := c2.count;
284 END LOOP;
285
286 --************************************************************************
287 -- The following code inserts a row in pay_functional_usages table for the
288 -- respective Business Group.
289 --************************************************************************
290 IF (l_count = 0) THEN
291 hr_utility.trace('The count is ' || TO_CHAR(l_count));
292 pay_functional_usages_pkg.insert_row(
293 p_row_id => l_row_id ,
294 p_usage_id => l_usage_id ,
295 p_area_id => l_area_id ,
296 p_legislation_code => NULL ,
297 p_business_group_id => l_business_group_id ,
298 p_payroll_id => NULL );
299 END IF;
300 hr_utility.set_location('Leaving }' || gv_package || lv_procedure_name, 20);
301 END enable_dynamic_triggers;
302
303 --*************************************************************************
304 -- Procedure : Standard Procedure
305 --*************************************************************************
306
307 PROCEDURE standard_proc
308 (
309 business_group IN VARCHAR2 DEFAULT NULL ,
310 pay_mode_grade IN VARCHAR2 DEFAULT NULL ,
311 pay_mode_scale IN VARCHAR2 DEFAULT NULL ,
312 pay_mode_salary IN VARCHAR2 DEFAULT NULL ,
313 teacher_england IN VARCHAR2 DEFAULT NULL ,
314 teacher_scotland IN VARCHAR2 DEFAULT NULL ,
315 startdate IN VARCHAR2 DEFAULT NULL ,
316 basename IN VARCHAR2 DEFAULT NULL ,
317 sal_rep_name IN VARCHAR2 DEFAULT NULL ,
318 grade_rep_name IN VARCHAR2 DEFAULT NULL ,
319 ps_rep_name IN VARCHAR2 DEFAULT NULL ,
320 p_ele_gr_name IN VARCHAR2 DEFAULT NULL ,
321 p_ele_psr_name IN VARCHAR2 DEFAULT NULL
322 )
323 AS
324 l_ele_id NUMBER ;
325 l_formula_id NUMBER ;
326 l_status_proc_rule_id NUMBER ;
327 l_event_group_id NUMBER ;
328 l_ovn NUMBER ;
329 l_dt_event_id NUMBER ;
330 l_ipv_pv NUMBER ;
331 l_ipv_as NUMBER ;
332 l_for_res_id NUMBER ;
333 l_formula_type_id NUMBER ;
334 l_formula_text LONG ;
335 l_business_group_name VARCHAR2(50) ;
336 l_business VARCHAR2(50) ;
337 l_formula_name VARCHAR2(50) ;
338 l_description VARCHAR2(50) ;
339 l_date VARCHAR2(50) ;
340 l_base VARCHAR2(50) ;
341 l_upper_base VARCHAR2(50) ;
342 l_modified_base_pg VARCHAR2(50) ;
343 l_modified_base_ele VARCHAR2(50) ;
344 l_modified_base_formula VARCHAR2(50) ;
345 l_salary_flag VARCHAR2(6) := 'FALSE' ;
346 l_grade_flag VARCHAR2(6) := 'FALSE' ;
347 l_pscale_flag VARCHAR2(6) := 'FALSE' ;
348 l_string VARCHAR2(500) ;
349 lv_procedure_name VARCHAR2(50) := '.standard_proc' ;
350 l_england_flag BOOLEAN := FALSE ;
351 l_scotland_flag BOOLEAN := FALSE ;
352 l_count NUMBER ;
353 l_business_group_id NUMBER ;
354 l_dated_table_id NUMBER ;
355 l_ele_id_scot NUMBER ;
356 l_ipv_pv_scot NUMBER ;
357 l_req_id NUMBER ;
358 l_etei_ovn NUMBER ;
359 l_etei_id NUMBER ;
360 BEGIN
361 hr_utility.set_location('Entering {'|| gv_package || lv_procedure_name, 10);
362 l_business_group_name := UPPER(business_group) ;
363 l_business := business_group ;
364 l_upper_base := UPPER(basename) ;
365 l_base := basename ;
366 l_modified_base_pg := l_base ;
367 l_modified_base_ele := l_base ;
368 l_modified_base_formula := REPLACE(l_upper_base, ' ', '_');
369 l_date := startdate ;
370
371 valid_business_group(p_business_group_name => business_group);
372 FOR c1 IN c_biz_group (l_business_group_name)
373 LOOP
374 l_business_group_id := c1.business_group_id;
375 END LOOP;
376
377 IF (pay_mode_grade = 'YES') THEN
378 l_grade_flag := 'TRUE';
379 END IF;
380 IF (pay_mode_salary = 'YES') THEN
381 l_salary_flag := 'TRUE';
382 END IF;
383 IF (pay_mode_scale = 'YES') THEN
384 l_pscale_flag := 'TRUE';
385 END IF;
386
387 --*********** SALARY ********************
388
389 IF (l_salary_flag = 'TRUE') THEN
390 hr_utility.trace('Salary Flag is true');
391
392 -- We should basically
393 -- a) Create a Pro ration Group with the events enabled for SALARY.
394 -- b) Create an Element.
395 -- c) Link the formula to the element.
396 -- d) Enable the dynamic triggers and Functional Specifications.
397
398 -- a) Step a
399
400 -- PAY_DATETRACKED_EVENTS_API. CREATE_DATETRACKED_EVENT
401 -- PAY_EVENT_GROUPS_API.CREATE_EVENT_GROUP
402
403 l_count := 0;
404
405 SELECT COUNT(*)
406 INTO l_count
407 FROM pay_event_groups
408 WHERE event_group_name = UPPER(l_modified_base_pg || ' sal pg');
409
410 hr_utility.trace('The count 15 is ' || TO_CHAR(l_count));
411
412 IF (l_count = 0) THEN
413 hr_utility.trace('If condition ... Creating Even Group');
414
415 pay_event_groups_api.create_event_group
416 (
417 p_effective_date => TO_DATE(l_date,'dd/mm/yyyy')
418 ,p_event_group_name => UPPER(l_modified_base_pg || ' sal pg')
419 ,p_event_group_type => 'P'
420 ,p_proration_type => 'P'
421 ,p_business_group_id => l_business_group_id
422 ,p_legislation_code => NULL
423 ,p_event_group_id => l_event_group_id
424 ,p_object_version_number => l_ovn
425 );
426 ELSE
427 hr_utility.trace('Else condition ');
428 l_event_group_id := NULL;
429
430 -- SELECT event_group_id
431 -- INTO l_event_group_id
432 -- FROM pay_event_groups
433 -- WHERE event_group_name = UPPER(l_modified_base_pg || ' sal pg');
434
435 FOR cegi IN c_event_group_id(l_modified_base_pg || ' sal pg')
436 LOOP
437 l_event_group_id := cegi.event_group_id;
438 END LOOP;
439 END IF;
440
441 FOR c2 IN c_dated_pay_table ('PAY_ELEMENT_ENTRIES_F')
442 LOOP
443 l_dated_table_id := c2.dated_table_id;
444 END LOOP;
445
446 l_count := 0;
447
448 SELECT COUNT(*)
449 INTO l_count
450 FROM pay_datetracked_events
451 WHERE column_name = 'EFFECTIVE_START_DATE'
452 AND event_group_id = l_event_group_id
453 AND dated_table_id = l_dated_table_id ;
454
455 hr_utility.trace('The count 20 is ' || TO_CHAR(l_count));
456
457 IF (l_count = 0) THEN
458 hr_utility.trace('If condition ... Creating Date Tracked Events');
459 pay_datetracked_events_api.create_datetracked_event
460 (
461 p_validate => FALSE
462 ,p_effective_date => TO_DATE(l_date,'dd/mm/yyyy')
463 ,p_event_group_id => l_event_group_id
464 ,p_dated_table_id => l_dated_table_id
465 /* of pay_element_entries_f */
466 ,p_update_type => 'U'
467 ,p_column_name => 'EFFECTIVE_START_DATE'
468 ,p_business_group_id => l_business_group_id
469 ,p_legislation_code => NULL
470 ,p_datetracked_event_id => l_dt_event_id
471 ,p_object_version_number => l_ovn
472 ) ;
473 END IF;
474
475 l_count := 0;
476
477 SELECT COUNT(*)
478 INTO l_count
479 FROM pay_datetracked_events
480 WHERE column_name = 'EFFECTIVE_END_DATE'
481 AND event_group_id = l_event_group_id
482 AND dated_table_id = l_dated_table_id ;
483
484 hr_utility.trace('The count 25 is ' || TO_CHAR(l_count));
485
486 IF (l_count = 0) THEN
487 hr_utility.trace('If condition ... Creating Date Tracked Events');
488 pay_datetracked_events_api.create_datetracked_event
489 (
490 p_validate => FALSE
491 ,p_effective_date => TO_DATE(l_date,'dd/mm/yyyy')
492 ,p_event_group_id => l_event_group_id
493 ,p_dated_table_id => l_dated_table_id
494 /* of pay_element_entries_f */
495 ,p_update_type => 'U'
496 ,p_column_name => 'EFFECTIVE_END_DATE'
497 ,p_business_group_id => l_business_group_id
498 ,p_legislation_code => NULL
499 ,p_datetracked_event_id => l_dt_event_id
500 ,p_object_version_number => l_ovn );
501 END IF;
502 --b) Step b
503
504 IF (teacher_england = 'YES') THEN
505 hr_utility.trace('If condition for English Teachers');
506
507 l_count := 0;
508
509 SELECT COUNT(*)
510 INTO l_count
511 FROM pay_element_types_f
512 WHERE UPPER(element_name) =
513 UPPER(l_modified_base_ele || ' sal ele');
514
515 IF (l_count = 0) THEN
516 hr_utility.trace('If condition ... Creating Element');
517
518 l_ele_id := pay_db_pay_setup.create_element(
519 p_element_name => l_modified_base_ele || ' sal ele'
520 ,p_description => 'Element to prorate the salary'
521 ,p_reporting_name => SUBSTR(sal_rep_name, 1, 80)
522 ,p_classification_name => 'Earnings'
523 ,p_post_termination_rule => 'Actual Termination'
524 ,p_processing_type => 'R'
525 ,p_processing_priority => 2500
526 ,p_standard_link_flag => 'N'
527 ,p_business_group_name => l_business
528 ,p_legislation_code => NULL
529 ,p_effective_start_date => TO_DATE(l_date,'dd/mm/yyyy')
530 ,p_effective_end_date => TO_DATE('31/12/4712','dd/mm/yyyy')
531 ,p_proration_group_id => l_event_group_id);
532 --
533 -- create input values
534 --
535 l_ipv_pv := NULL;
536
537 FOR c_iv IN c_input_value(l_ele_id, 'Pay Value')
538 LOOP
539 l_ipv_pv := c_iv.input_value_id;
540 END LOOP;
541
542 l_ipv_as := NULL;
543
544 FOR c_iv IN c_input_value(l_ele_id, 'Amount')
545 LOOP
546 l_ipv_as := c_iv.input_value_id;
547 END LOOP;
548
549 IF (l_ipv_as IS NULL) THEN
550 hr_utility.trace('If condition ... Creating Input Value');
551 l_ipv_as := pay_db_pay_setup.create_input_value(
552 p_element_name => l_modified_base_ele || ' sal ele'
553 ,p_name => 'Amount'
554 ,p_uom_code => 'M'
555 ,p_mandatory_flag => 'X'
556 ,p_display_sequence => 2
557 ,p_business_group_name => l_business
558 ,p_effective_start_date => TO_DATE(l_date,'dd/mm/yyyy')
559 ,p_effective_end_date => TO_DATE('31/12/4712','DD/MM/YYYY')
560 ,p_legislation_code => NULL);
561 END IF;
562 --
563 ELSE
564 hr_utility.trace(l_modified_base_ele ||
565 '_sal_ele already exists.');
566 END IF;
567 END IF;
568 IF (teacher_scotland = 'YES' OR
569 (teacher_england = 'NO' AND teacher_scotland = 'NO')) THEN
570 hr_utility.trace('If condition ... Teacher Scotland ');
571 l_count := 0;
572
573 SELECT COUNT(*)
574 INTO l_count
575 FROM pay_element_types_f
576 WHERE UPPER(element_name) =
577 UPPER(l_modified_base_ele || ' sal ele1');
578
579 IF (l_count = 0) THEN
580 hr_utility.trace('If condition ... create element');
581
582 l_ele_id_scot := pay_db_pay_setup.create_element(
583 p_element_name => l_modified_base_ele || ' sal ele1'
584 ,p_description => 'Element to prorate the salary'
585 ,p_reporting_name => SUBSTR(sal_rep_name, 1, 80)
586 ,p_classification_name => 'Earnings'
587 ,p_post_termination_rule => 'Actual Termination'
588 ,p_processing_type => 'R'
589 ,p_processing_priority => 2500
590 ,p_standard_link_flag => 'N'
591 ,p_business_group_name => l_business
592 ,p_legislation_code => NULL
593 ,p_effective_start_date => TO_DATE(l_date,'dd/mm/yyyy')
594 ,p_effective_end_date => TO_DATE('31/12/4712','dd/mm/yyyy')
595 ,p_proration_group_id => l_event_group_id);
596 --
597 -- create input values
598 --
599 FOR c_iv1 IN c_input_value(l_ele_id_scot, 'Pay Value')
600 LOOP
601 l_ipv_pv_scot := c_iv1.input_value_id;
602 END LOOP;
603
604 l_ipv_as := NULL;
605
606 FOR c_iv IN c_input_value(l_ele_id_scot, 'Amount')
607 LOOP
608 l_ipv_as := c_iv.input_value_id;
609 END LOOP;
610
611 IF (l_ipv_as IS NULL) THEN
612 hr_utility.trace('If condition ... create input value');
613 l_ipv_as := pay_db_pay_setup.create_input_value(
614 p_element_name => l_modified_base_ele || ' sal ele1'
615 ,p_name => 'Amount'
616 ,p_uom_code => 'M'
617 ,p_mandatory_flag => 'X'
618 ,p_display_sequence => 2
619 ,p_business_group_name => l_business
620 ,p_effective_start_date => TO_DATE(l_date,'dd/mm/yyyy')
621 ,p_effective_end_date => TO_DATE('31/12/4712','DD/MM/YYYY')
622 ,p_legislation_code => NULL);
623 END IF;
624 ELSE
625 hr_utility.trace(l_modified_base_ele ||
626 ' sal ele1 already exists.');
627 END IF;
628 END IF;
629
630 -- c) Step c)
631
632 IF (teacher_england = 'YES') THEN
633 hr_utility.trace('If condition ... teacher england');
634 l_count := 0;
635
636 SELECT COUNT(*)
637 INTO l_count
638 FROM ff_formulas_f
639 WHERE formula_name = UPPER(l_modified_base_formula || '_sal_ff');
640
641 IF (l_count = 0) THEN
642 hr_utility.trace('If condition ... count = 0');
643
644 SELECT formula_type_id
645 INTO l_formula_type_id
646 FROM ff_formula_types
647 WHERE formula_type_name = 'Oracle Payroll';
648
649 -- SELECT formula_text
650 -- INTO l_formula_text
651 -- FROM ff_formulas_f ff
652 -- WHERE ff.formula_name = 'UK_PRORATION_SAL_MANAGEMENT'
653 -- AND ff.legislation_code = 'GB'
654 -- AND ff.business_group_id IS NULL;
655
656 FOR cft IN c_formula_text('UK_PRORATION_SAL_MANAGEMENT')
657 LOOP
658 l_formula_text := cft.formula_text;
659 END LOOP;
660
661 l_formula_name := UPPER(l_modified_base_formula || '_sal_ff');
662 l_formula_text :=
663 REPLACE(l_formula_text, 'annual_salary', 'Amount');
664 l_formula_text :=
665 REPLACE(l_formula_text,
666 'UK_PRORATION_SAL_MANAGEMENT',
667 l_formula_name);
668 l_description := 'Formula for Salary Management';
669
670 INSERT INTO ff_formulas_f
671 (formula_id ,
672 effective_start_date ,
673 effective_end_date ,
674 business_group_id ,
675 legislation_code ,
676 formula_type_id ,
677 formula_name ,
678 description ,
679 formula_text ,
680 last_update_date ,
681 last_updated_by ,
682 last_update_login ,
683 created_by ,
684 creation_date )
685 VALUES
686 (ff_formulas_s.NEXTVAL , -- formula_id
687 TO_DATE(l_date,'dd/mm/yyyy') , -- effective_start_date
688 TO_DATE('31/12/4712', 'DD/MM/YYYY') , -- effective_end_date
689 l_business_group_id , -- business_group_id
690 NULL , -- legislation_code
691 l_formula_type_id , -- formula_type_id
692 l_formula_name , -- formula_name
693 l_description , -- description
694 l_formula_text , -- formula_text
695 SYSDATE , -- last_update_date
696 -1 , -- last_updated_by
697 -1 , -- last_update_login
698 -1 , -- created_by
699 SYSDATE ); -- creation_date
700
701 -- SELECT formula_id
702 -- INTO l_formula_id
703 -- FROM ff_formulas_f ff
704 -- WHERE ff.formula_name = l_formula_name
705 -- AND ff.legislation_code IS NULL
706 -- AND ff.business_group_id = l_business_group_id;
707
708 FOR cffi IN c_fast_formula_id(l_formula_name ,
709 l_business_group_id )
710 LOOP
711 l_formula_id := cffi.formula_id;
712 END LOOP;
713
714 l_req_id := fnd_request.submit_request(
715 application => 'FF' ,
716 program => 'BULKCOMPILE' ,
717 argument1 => 'Oracle Payroll' ,
718 argument2 => l_formula_name );
719
720 l_status_proc_rule_id := pay_formula_results.ins_stat_proc_rule
721 (
722 p_business_group_id =>l_business_group_id
723 ,p_legislation_code => NULL
724 ,p_effective_start_date => TO_DATE(l_date,'dd/mm/yyyy')
725 ,p_element_type_id => l_ele_id
726 ,p_formula_id => l_formula_id
727 ,p_processing_rule => 'P'
728 );
729
730 l_for_res_id := pay_formula_results.ins_form_res_rule
731 (
732 p_business_group_id => l_business_group_id
733 ,p_legislation_code => NULL
734 ,p_status_processing_rule_id => l_status_proc_rule_id
735 ,p_result_name => 'RESULT1'
736 ,p_element_type_id => l_ele_id
737 ,p_result_rule_type => 'D'
738 ,p_effective_start_date => TO_DATE(l_date,'dd/mm/yyyy')
739 );
740 ELSE
741 hr_utility.trace(l_modified_base_formula ||
742 '_sal_ff already exists.');
743 END IF;
744 END IF;
745
746 IF (teacher_scotland = 'YES' OR
747 (teacher_england = 'NO' AND teacher_scotland = 'NO')) THEN
748 hr_utility.trace('If condition ... teacher scotland');
749 l_count := 0;
750
751 SELECT COUNT(*)
752 INTO l_count
753 FROM ff_formulas_f
754 WHERE formula_name = UPPER(l_modified_base_formula || '_sal_ff1');
755
756 IF (l_count = 0) THEN
757 hr_utility.trace('If condition ... count = 0');
758
759 SELECT formula_type_id
760 INTO l_formula_type_id
761 FROM ff_formula_types
762 WHERE formula_type_name = 'Oracle Payroll';
763
764 -- SELECT formula_text
765 -- INTO l_formula_text
766 -- FROM ff_formulas_f ff
767 -- WHERE ff.formula_name = 'UK_PRORATION_ALLOWANCE'
768 -- AND ff.legislation_code = 'GB'
769 -- AND ff.business_group_id IS NULL;
770
771 FOR cft1 IN c_formula_text('UK_PRORATION_ALLOWANCE')
772 LOOP
773 l_formula_text := cft1.formula_text;
774 END LOOP;
775
776 l_formula_name := UPPER(l_modified_base_formula || '_sal_ff1');
777
778 l_formula_text :=
779 REPLACE(l_formula_text, 'annual_allowance', 'Amount');
780 l_formula_text :=
781 REPLACE(l_formula_text,
782 'UK_PRORATION_ALLOWANCE',
783 l_formula_name);
784 l_description := 'Formula for Salary Management';
785
786 INSERT INTO ff_formulas_f
787 (formula_id ,
788 effective_start_date ,
789 effective_end_date ,
790 business_group_id ,
791 legislation_code ,
792 formula_type_id ,
793 formula_name ,
794 description ,
795 formula_text ,
796 last_update_date ,
797 last_updated_by ,
798 last_update_login ,
799 created_by ,
800 creation_date )
801 VALUES
802 (ff_formulas_s.NEXTVAL , -- formula_id
803 TO_DATE(l_date,'dd/mm/yyyy') , -- effective_start_date
804 TO_DATE('31/12/4712', 'DD/MM/YYYY') , -- effective_end_date
805 l_business_group_id , -- business_group_id
806 NULL , -- legislation_code
807 l_formula_type_id , -- formula_type_id
808 l_formula_name , -- formula_name
809 l_description , -- description
810 l_formula_text , -- formula_text
811 SYSDATE , -- last_update_date
812 -1 , -- last_updated_by
813 -1 , -- last_update_login
814 -1 , -- created_by
815 SYSDATE ); -- creation_date
816
817 -- SELECT formula_id
818 -- INTO l_formula_id
819 -- FROM ff_formulas_f ff
820 -- WHERE ff.formula_name = l_formula_name
821 -- AND ff.legislation_code IS NULL
822 -- AND ff.business_group_id = l_business_group_id;
823
824 FOR cffi1 IN c_fast_formula_id(l_formula_name ,
825 l_business_group_id )
826 LOOP
827 l_formula_id := cffi1.formula_id;
828 END LOOP;
829 l_req_id := fnd_request.submit_request(
830 application => 'FF' ,
831 program => 'BULKCOMPILE' ,
832 argument1 => 'Oracle Payroll' ,
833 argument2 => l_formula_name );
834
835 l_status_proc_rule_id := pay_formula_results.ins_stat_proc_rule
836 (
837 p_business_group_id =>l_business_group_id
838 ,p_legislation_code => NULL
839 ,p_effective_start_date => TO_DATE(l_date,'dd/mm/yyyy')
840 ,p_element_type_id => l_ele_id_scot
841 ,p_formula_id => l_formula_id
842 ,p_processing_rule => 'P'
843 );
844
845 l_for_res_id := pay_formula_results.ins_form_res_rule
846 (
847 p_business_group_id => l_business_group_id
848 ,p_legislation_code => NULL
849 ,p_status_processing_rule_id => l_status_proc_rule_id
850 ,p_result_name => 'L_AMOUNT'
851 ,p_element_type_id => l_ele_id_scot
852 ,p_result_rule_type => 'D'
853 ,p_effective_start_date => TO_DATE(l_date,'dd/mm/yyyy')
854 );
855 ELSE
856 hr_utility.trace(l_modified_base_formula ||
857 '_sal_ff1 already exists.');
858 END IF;
859 END IF;
860 END IF;
861
862 --**************** GRADES ******************************
863
864 IF (l_grade_flag = 'TRUE') THEN
865 hr_utility.trace('If condition ... grade flag is true');
866
867 -- We should basically
868 -- a) Create a Pro ration Group with the events enabled for SALARY.
869 -- b) Create an Element.
870 -- c) Link the formula to the element.
871 -- d) Enable the dynamic triggers and Functional Specifications.
872
873 -- a) Step a
874
875 -- PAY_DATETRACKED_EVENTS_API. CREATE_DATETRACKED_EVENT
876 -- PAY_EVENT_GROUPS_API.CREATE_EVENT_GROUP
877
878 l_count := 0;
879
880 SELECT COUNT(*)
881 INTO l_count
882 FROM pay_event_groups
883 WHERE event_group_name = UPPER(l_modified_base_pg || ' GRADE pg');
884
885 IF (l_count = 0 ) THEN
886 hr_utility.trace('If condition ... creating event group');
887 pay_event_groups_api.create_event_group
888 (
889 p_effective_date => TO_DATE(l_date,'dd/mm/yyyy')
890 ,p_event_group_name => UPPER(l_modified_base_pg || ' grade pg')
891 ,p_event_group_type => 'P'
892 ,p_proration_type => 'P'
893 ,p_business_group_id => l_business_group_id
894 ,p_legislation_code => NULL
895 ,p_event_group_id => l_event_group_id
896 ,p_object_version_number => l_ovn
897 );
898 ELSE
899 hr_utility.trace('Else condition ...');
900 l_event_group_id := NULL;
901
902 -- SELECT event_group_id
903 -- INTO l_event_group_id
904 -- FROM pay_event_groups
905 -- WHERE event_group_name = UPPER(l_modified_base_pg || ' GRADE pg');
906 FOR cegi1 IN c_event_group_id(l_modified_base_pg || ' GRADE pg')
907 LOOP
908 l_event_group_id := cegi1.event_group_id;
909 END LOOP;
910 END IF;
911
912 FOR c2 IN c_dated_pay_table ('PAY_GRADE_RULES_F')
913 LOOP
914 l_dated_table_id := c2.dated_table_id;
915 END LOOP;
916
917 l_count := 0;
918
919 SELECT COUNT(*)
920 INTO l_count
921 FROM pay_datetracked_events
922 WHERE column_name = 'VALUE'
923 AND event_group_id = l_event_group_id
924 AND dated_table_id = l_dated_table_id;
925
926 IF (l_count = 0) THEN
927 hr_utility.trace('If condition ... creating datetracked events');
928
929 pay_datetracked_events_api.create_datetracked_event
930 (
931 p_validate => FALSE
932 ,p_effective_date => TO_DATE(l_date,'dd/mm/yyyy')
933 ,p_event_group_id => l_event_group_id
934 ,p_dated_table_id => l_dated_table_id
935 -- of pay_grade_rules_f
936 ,p_update_type => 'U'
937 ,p_column_name => 'VALUE'
938 ,p_business_group_id => l_business_group_id
939 ,p_legislation_code => NULL
940 ,p_datetracked_event_id => l_dt_event_id
941 ,p_object_version_number => l_ovn
942 ) ;
943 END IF;
944
945 FOR c2 IN c_dated_pay_table ('PER_ALL_ASSIGNMENTS_F')
946 LOOP
947 l_dated_table_id := c2.dated_table_id;
948 END LOOP;
949
950 l_count := 0;
951
952 SELECT COUNT(*)
953 INTO l_count
954 FROM pay_datetracked_events
955 WHERE column_name = 'GRADE_ID'
956 AND event_group_id = l_event_group_id
957 AND dated_table_id = l_dated_table_id ;
958
959 IF (l_count = 0) THEN
960 hr_utility.trace('If condition ... creating datetracked events');
961
962 pay_datetracked_events_api.create_datetracked_event
963 (
964 p_validate => FALSE
965 ,p_effective_date => TO_DATE(l_date,'dd/mm/yyyy')
966 ,p_event_group_id => l_event_group_id
967 ,p_dated_table_id => l_dated_table_id
968 -- of per_all_assignments_f
969 ,p_update_type => 'U'
970 ,p_column_name => 'GRADE_ID'
971 ,p_business_group_id => l_business_group_id
972 ,p_legislation_code => NULL
973 ,p_datetracked_event_id => l_dt_event_id
974 ,p_object_version_number => l_ovn );
975
976 END IF;
977
978 --b) Step b
979
980 l_count := 0;
981
982 SELECT COUNT(*)
983 INTO l_count
984 FROM pay_element_types_f
985 WHERE UPPER(element_name) = UPPER(l_modified_base_ele || ' grade ele');
986
987 IF (l_count = 0) THEN
988 hr_utility.trace('If condition ... creating elements');
989
990 l_ele_id := pay_db_pay_setup.create_element(
991 p_element_name => l_modified_base_ele || ' grade ele'
992 ,p_description => 'Element to prorate the Grades'
993 ,p_reporting_name => grade_rep_name
994 ,p_classification_name => 'Earnings'
995 ,p_post_termination_rule => 'Actual Termination'
996 ,p_processing_type => 'R'
997 ,p_processing_priority => 2500
998 ,p_standard_link_flag => 'N'
999 ,p_business_group_name => l_business
1000 ,p_legislation_code => NULL
1001 ,p_effective_start_date => to_date(l_date,'dd/mm/yyyy')
1002 ,p_effective_end_date => to_date('31/12/4712','dd/mm/yyyy')
1003 ,p_proration_group_id => l_event_group_id);
1004
1005 --
1006 -- create input values
1007 --
1008 -- SELECT input_value_id
1009 -- INTO l_ipv_pv
1010 -- FROM pay_input_values_f
1011 -- WHERE element_type_id = l_ele_id
1012 -- AND name = 'Pay Value'
1013 -- AND rownum < 2;
1014
1015 l_ipv_pv := NULL;
1016
1017 FOR c_iv IN c_input_value(l_ele_id, 'Pay Value')
1018 LOOP
1019 l_ipv_pv := c_iv.input_value_id;
1020 END LOOP;
1021 --
1022 ELSE
1023 hr_utility.trace(l_modified_base_ele || '_grade_ele already exists.');
1024 END IF;
1025
1026 IF (p_ele_gr_name IS NOT NULL) THEN
1027 IF (l_count <> 0) THEN
1028 FOR ceti IN c_element_id(l_modified_base_ele || ' grade ele')
1029 LOOP
1030 l_ele_id := ceti.element_type_id;
1031 END LOOP;
1032 END IF;
1033
1034 l_count := 0;
1035
1036 FOR cetei IN c_element_extra_info_cnt(l_ele_id)
1037 LOOP
1038 l_count := cetei.count;
1039 END LOOP;
1040 IF (l_count = 0 ) THEN
1041 hr_utility.trace('If condition ...creating element extra info');
1042 pay_db_pay_setup.set_session_date(trunc(sysdate));
1043 l_etei_id := NULL;
1044 l_etei_ovn := NULL;
1045 pay_element_extra_info_api.create_element_extra_info
1046 ( p_element_type_id => l_ele_id
1047 ,p_information_type => 'PQP_UK_ELEMENT_ATTRIBUTION'
1048 ,p_eei_information_category => 'PQP_UK_ELEMENT_ATTRIBUTION'
1049 ,p_eei_information1 => 'H'
1050 -- For Hourly Time Dimension
1051 ,p_eei_information2 => 'GR'
1052 -- Spinal Points Pay Source Value
1053 ,p_eei_information3 => p_ele_gr_name
1054 ,p_eei_information4 => 'N'
1055 -- No FTE
1056 ,p_eei_information5 => 'N'
1057 ,p_element_type_extra_info_id => l_etei_id
1058 ,p_object_version_number => l_etei_ovn
1059 -- 'No' Service History
1060 );
1061 ELSE
1062 hr_utility.trace('Else condition..updating element extra info');
1063 pay_db_pay_setup.set_session_date(trunc(sysdate));
1064 l_etei_id := NULL;
1065 l_etei_ovn := NULL;
1066 FOR cetei1 IN c_element_extra_info_id(l_ele_id)
1067 LOOP
1068 l_etei_id := cetei1.element_type_extra_info_id;
1069 l_etei_ovn := cetei1.object_version_number;
1070 END LOOP;
1071
1072 pay_element_extra_info_api.update_element_extra_info
1073 (p_element_type_extra_info_id => l_etei_id
1074 ,p_object_version_number => l_etei_ovn
1075 ,p_eei_information_category => 'PQP_UK_ELEMENT_ATTRIBUTION'
1076 ,p_eei_information1 => 'H'
1077 -- For Hourly Time Dimension
1078 ,p_eei_information2 => 'GR'
1079 -- Spinal Points Pay Source Value
1080 ,p_eei_information3 => p_ele_gr_name
1081 ,p_eei_information4 => 'N'
1082 -- No FTE
1083 ,p_eei_information5 => 'N'
1084 -- 'No' Service History
1085 );
1086 END IF;
1087 END IF;
1088
1089 -- c) Step c)
1090
1091 l_count := 0;
1092
1093 SELECT COUNT(*)
1094 INTO l_count
1095 FROM ff_formulas_f
1096 WHERE formula_name = UPPER(l_modified_base_formula || '_grade_ff');
1097
1098 IF (l_count = 0) THEN
1099 hr_utility.trace('If condition ...creating formula');
1100 SELECT formula_type_id
1101 INTO l_formula_type_id
1102 FROM ff_formula_types
1103 WHERE formula_type_name = 'Oracle Payroll';
1104
1105 -- SELECT formula_text
1106 -- INTO l_formula_text
1107 -- FROM ff_formulas_f ff
1108 -- WHERE ff.formula_name = 'UK_PRORATION_GRADE_RATE'
1109 -- AND ff.legislation_code = 'GB'
1110 -- AND ff.business_group_id IS NULL;
1111
1112 FOR cft2 IN c_formula_text('UK_PRORATION_GRADE_RATE')
1113 LOOP
1114 l_formula_text := cft2.formula_text;
1115 END LOOP;
1116
1117 l_formula_name := UPPER(l_modified_base_formula || '_grade_ff');
1118 l_description := 'Formula for Grades Proration';
1119
1120 l_formula_text := REPLACE(l_formula_text, 'UK_PRORATION_GRADE_RATE',
1121 l_formula_name);
1122
1123 l_formula_text := REPLACE(l_formula_text, 'UK Grade Rate',
1124 l_modified_base_ele || ' grade ele' );
1125
1126 INSERT INTO ff_formulas_f
1127 (formula_id ,
1128 effective_start_date ,
1129 effective_end_date ,
1130 business_group_id ,
1131 legislation_code ,
1132 formula_type_id ,
1133 formula_name ,
1134 description ,
1135 formula_text ,
1136 last_update_date ,
1137 last_updated_by ,
1138 last_update_login ,
1139 created_by ,
1140 creation_date )
1141 VALUES
1142 (ff_formulas_s.NEXTVAL , -- formula_id
1143 TO_DATE(l_date,'dd/mm/yyyy') , -- effective_start_date
1144 TO_DATE('31/12/4712', 'DD/MM/YYYY') , -- effective_end_date
1145 l_business_group_id , -- business_group_id
1146 NULL , -- legislation_code
1147 l_formula_type_id , -- formula_type_id
1148 l_formula_name , -- formula_name
1149 l_description , -- description
1150 l_formula_text , -- formula_text
1151 SYSDATE , -- last_update_date
1152 -1 , -- last_updated_by
1153 -1 , -- last_update_login
1154 -1 , -- created_by
1155 SYSDATE ); -- creation_date
1156
1157 -- SELECT formula_id
1158 -- INTO l_formula_id
1159 -- FROM ff_formulas_f ff
1160 -- WHERE ff.formula_name = l_formula_name
1161 -- AND ff.legislation_code IS NULL
1162 -- AND ff.business_group_id = l_business_group_id;
1163
1164 FOR cffi2 IN c_fast_formula_id(l_formula_name,
1165 l_business_group_id)
1166 LOOP
1167 l_formula_id := cffi2.formula_id;
1168 END LOOP;
1169
1170 l_req_id := fnd_request.submit_request(
1171 application => 'FF' ,
1172 program => 'BULKCOMPILE' ,
1173 argument1 => 'Oracle Payroll' ,
1174 argument2 => l_formula_name );
1175
1176 l_status_proc_rule_id := pay_formula_results.ins_stat_proc_rule
1177 (
1178 p_business_group_id =>l_business_group_id
1179 ,p_legislation_code => NULL
1180 ,p_effective_start_date => TO_DATE(l_date,'dd/mm/yyyy')
1181 ,p_element_type_id => l_ele_id
1182 ,p_formula_id => l_formula_id
1183 ,p_processing_rule => 'P'
1184 );
1185
1186 l_for_res_id := pay_formula_results.ins_form_res_rule
1187 (
1188 p_business_group_id => l_business_group_id
1189 ,p_legislation_code => NULL
1190 ,p_status_processing_rule_id => l_status_proc_rule_id
1191 ,p_input_value_id => l_ipv_pv
1192 ,p_result_name => 'L_AMOUNT'
1193 ,p_element_type_id => l_ele_id
1194 ,p_result_rule_type => 'D'
1195 ,p_effective_start_date => TO_DATE(l_date,'dd/mm/yyyy')
1196 );
1197 ELSE
1198 hr_utility.trace(l_modified_base_formula || '_grade_ff already exists.');
1199 END IF;
1200 END IF;
1201 --*************** PROGRESSION POINTS (Pay Scale) **************************
1202
1203 IF (l_pscale_flag = 'TRUE') THEN
1204 hr_utility.trace('If condition ...pay scale flag is true');
1205 -- We should basically
1206 -- a) Create a Pro ration Group with the events enabled for SALARY.
1207 -- b) Create an Element.
1208 -- c) Link the formula to the element.
1209 -- d) Enable the dynamic triggers and Functional Specifications.
1210
1211 l_grade_flag := 'TRUE';
1212 -- a) Step a
1213
1214 -- PAY_DATETRACKED_EVENTS_API. CREATE_DATETRACKED_EVENT
1215 -- PAY_EVENT_GROUPS_API.CREATE_EVENT_GROUP
1216
1217 l_count := 0;
1218
1219 SELECT COUNT(*)
1220 INTO l_count
1221 FROM pay_event_groups
1222 WHERE event_group_name = UPPER(l_modified_base_pg || ' PAYSCALE pg');
1223
1224 IF (l_count = 0) THEN
1225 hr_utility.trace('If condition ...creating event group');
1226 pay_event_groups_api.create_event_group
1227 (
1228 p_effective_date => TO_DATE(l_date,'dd/mm/yyyy')
1229 ,p_event_group_name => UPPER(l_modified_base_pg || ' payscale pg' )
1230 ,p_event_group_type => 'P'
1231 ,p_proration_type => 'P'
1232 ,p_business_group_id => l_business_group_id
1233 ,p_legislation_code => NULL
1234 ,p_event_group_id => l_event_group_id
1235 ,p_object_version_number => l_ovn
1236 );
1237 ELSE
1238 hr_utility.trace('else condition ...selecting from event group');
1239 l_event_group_id := NULL;
1240
1241 -- SELECT event_group_id
1242 -- INTO l_event_group_id
1243 -- FROM pay_event_groups
1244 -- WHERE event_group_name = UPPER(l_modified_base_pg ||
1245 -- ' payscale pg');
1246 FOR cegi3 IN c_event_group_id(UPPER(l_modified_base_pg || ' payscale pg'))
1247 LOOP
1248 l_event_group_id := cegi3.event_group_id;
1249 END LOOP;
1250 END IF;
1251
1252 FOR c2 IN c_dated_pay_table ('PER_SPINAL_POINT_PLACEMENTS_F')
1253 LOOP
1254 l_dated_table_id := c2.dated_table_id;
1255 END LOOP;
1256
1257 l_count := 0;
1258
1259 SELECT COUNT(*)
1260 INTO l_count
1261 FROM pay_datetracked_events
1262 WHERE column_name = 'STEP_ID'
1263 AND event_group_id = l_event_group_id
1264 AND dated_table_id = l_dated_table_id ;
1265
1266 IF (l_count = 0) THEN
1267 hr_utility.trace('If condition ...creating datetracked events');
1268 pay_datetracked_events_api.create_datetracked_event
1269 (
1270 p_validate => FALSE
1271 ,p_effective_date => TO_DATE(l_date,'dd/mm/yyyy')
1272 ,p_event_group_id => l_event_group_id
1273 ,p_dated_table_id => l_dated_table_id
1274 -- PER_SPINAL_POINT_PLACEMENTS_F
1275 ,p_update_type => 'U'
1276 ,p_column_name => 'STEP_ID'
1277 ,p_business_group_id => l_business_group_id
1278 ,p_legislation_code => NULL
1279 ,p_datetracked_event_id => l_dt_event_id
1280 ,p_object_version_number => l_ovn
1281 ) ;
1282 END IF;
1283
1284 FOR c2 IN c_dated_pay_table ('PAY_GRADE_RULES_F')
1285 LOOP
1286 l_dated_table_id := c2.dated_table_id;
1287 END LOOP;
1288 l_count := 0;
1289 SELECT COUNT(*)
1290 INTO l_count
1291 FROM pay_datetracked_events
1292 WHERE column_name = 'VALUE'
1293 AND event_group_id = l_event_group_id
1294 AND dated_table_id = l_dated_table_id;
1295
1296 IF (l_count = 0) THEN
1297 hr_utility.trace('If condition ...creating datetracked events');
1298
1299 pay_datetracked_events_api.create_datetracked_event
1300 (
1301 p_validate => FALSE
1302 ,p_effective_date => TO_DATE(l_date,'dd/mm/yyyy')
1303 ,p_event_group_id => l_event_group_id
1304 ,p_dated_table_id => l_dated_table_id
1305 -- of pay_grade_rules_f
1306 ,p_update_type => 'U'
1307 ,p_column_name => 'VALUE'
1308 ,p_business_group_id => l_business_group_id
1309 ,p_legislation_code => NULL
1310 ,p_datetracked_event_id => l_dt_event_id
1311 ,p_object_version_number => l_ovn
1312 ) ;
1313 END IF;
1314
1315 FOR c2 IN c_dated_pay_table ('PER_ALL_ASSIGNMENTS_F')
1316 LOOP
1317 l_dated_table_id := c2.dated_table_id;
1318 END LOOP;
1319
1320 l_count := 0;
1321
1322 SELECT COUNT(*)
1323 INTO l_count
1324 FROM pay_datetracked_events
1325 WHERE column_name = 'GRADE_ID'
1326 AND event_group_id = l_event_group_id
1327 AND dated_table_id = l_dated_table_id ;
1328
1329 IF (l_count = 0) THEN
1330 hr_utility.trace('If condition ...creating datetracked events');
1331
1332 pay_datetracked_events_api.create_datetracked_event
1333 (
1334 p_validate => FALSE
1335 ,p_effective_date => TO_DATE(l_date,'dd/mm/yyyy')
1336 ,p_event_group_id => l_event_group_id
1337 ,p_dated_table_id => l_dated_table_id
1338 -- of per_all_assignments_f
1339 ,p_update_type => 'U'
1340 ,p_column_name => 'GRADE_ID'
1341 ,p_business_group_id => l_business_group_id
1342 ,p_legislation_code => NULL
1343 ,p_datetracked_event_id => l_dt_event_id
1344 ,p_object_version_number => l_ovn );
1345
1346 END IF;
1347
1348 --b) Step b
1349
1350 l_count := 0;
1351
1352 SELECT COUNT(*)
1353 INTO l_count
1354 FROM pay_element_types_f
1355 WHERE UPPER(element_name) =
1356 UPPER(l_modified_base_ele || ' payscale ele');
1357 hr_utility.trace('The count is ' || TO_CHAR(l_count));
1358
1359 IF (l_count = 0) THEN
1360 hr_utility.trace('If condition ...creating element');
1361
1362 l_ele_id := pay_db_pay_setup.create_element(
1363 p_element_name => l_modified_base_ele || ' payscale ele'
1364 ,p_description => 'Element to prorate the Pay Scale'
1365 ,p_reporting_name => ps_rep_name
1366 ,p_classification_name => 'Earnings'
1367 ,p_post_termination_rule => 'Actual Termination'
1368 ,p_processing_type => 'R'
1369 ,p_processing_priority => 2500
1370 ,p_standard_link_flag => 'N'
1371 ,p_business_group_name => l_business
1372 ,p_legislation_code => NULL
1373 ,p_effective_start_date => to_date(l_date,'dd/mm/yyyy')
1374 ,p_effective_end_date => to_date('31/12/4712','dd/mm/yyyy')
1375 ,p_proration_group_id => l_event_group_id);
1376
1377 hr_utility.trace('The element type id is ' || TO_CHAR(l_ele_id));
1378
1379 --
1380 -- create input values
1381 --
1382 -- SELECT input_value_id
1383 -- INTO l_ipv_pv
1384 -- FROM pay_input_values_f
1385 -- WHERE element_type_id = l_ele_id
1386 -- AND name = 'Pay Value'
1387 -- AND rownum < 2 ;
1388
1389 l_ipv_pv := NULL;
1390
1391 FOR c_iv IN c_input_value(l_ele_id, 'Pay Value')
1392 LOOP
1393 l_ipv_pv := c_iv.input_value_id;
1394 END LOOP;
1395 ELSE
1396 hr_utility.trace(l_modified_base_ele ||
1397 ' payscale ele already exists.');
1398 END IF;
1399 IF (p_ele_psr_name IS NOT NULL) THEN
1400 hr_utility.trace('Pay Scale qualifier is not null');
1401 IF (l_count <> 0) THEN
1402 hr_utility.trace('Second iteration');
1403 FOR ceti IN c_element_id(l_modified_base_ele || ' payscale ele')
1404 LOOP
1405 l_ele_id := ceti.element_type_id;
1406 END LOOP;
1407 hr_utility.trace('The element id is ' || TO_CHAR(l_ele_id));
1408 END IF;
1409
1410 l_count := 0;
1411
1412 FOR cetei IN c_element_extra_info_cnt(l_ele_id)
1413 LOOP
1414 l_count := cetei.count;
1415 END LOOP;
1416 hr_utility.trace('The count is ' || TO_CHAR(l_count));
1417
1418 IF (l_count = 0) THEN
1419 hr_utility.trace('If condition ...creating element extra info');
1420 l_etei_id := NULL;
1421 l_etei_ovn := NULL;
1422 pay_db_pay_setup.set_session_date(trunc(sysdate));
1423 pay_element_extra_info_api.create_element_extra_info
1424 ( p_element_type_id => l_ele_id
1425 ,p_information_type => 'PQP_UK_ELEMENT_ATTRIBUTION'
1426 ,p_eei_information_category => 'PQP_UK_ELEMENT_ATTRIBUTION'
1427 ,p_eei_information1 => 'H'
1428 -- For Hourly Time Dimension
1429 ,p_eei_information2 => 'SP'
1430 -- Spinal Points Pay Source Value
1431 ,p_eei_information3 => p_ele_psr_name
1432 ,p_eei_information4 => 'N'
1433 -- No FTE
1434 ,p_eei_information5 => 'N'
1435 ,p_element_type_extra_info_id => l_etei_id
1436 ,p_object_version_number => l_etei_ovn
1437 -- 'No' Service History
1438 );
1439 hr_utility.trace('The extra info id is '|| TO_CHAR(l_etei_id));
1440 ELSE
1441 hr_utility.trace('Else condition..updating element extra info');
1442 pay_db_pay_setup.set_session_date(trunc(sysdate));
1443 l_etei_id := NULL;
1444 l_etei_ovn := NULL;
1445 FOR cetei1 IN c_element_extra_info_id(l_ele_id)
1446 LOOP
1447 l_etei_id := cetei1.element_type_extra_info_id;
1448 l_etei_ovn := cetei1.object_version_number;
1449 END LOOP;
1450
1451 pay_element_extra_info_api.update_element_extra_info
1452 (p_element_type_extra_info_id => l_etei_id
1453 ,p_object_version_number => l_etei_ovn
1454 ,p_eei_information_category => 'PQP_UK_ELEMENT_ATTRIBUTION'
1455 ,p_eei_information1 => 'H'
1456 -- For Hourly Time Dimension
1457 ,p_eei_information2 => 'SP'
1458 -- Spinal Points Pay Source Value
1459 ,p_eei_information3 => p_ele_psr_name
1460 ,p_eei_information4 => 'N'
1461 -- No FTE
1462 ,p_eei_information5 => 'N'
1463 -- 'No' Service History
1464 );
1465 END IF;
1466 END IF;
1467
1468 -- c) Step c)
1469
1470 l_count := 0;
1471
1472 SELECT COUNT(*)
1473 INTO l_count
1474 FROM ff_formulas_f
1475 WHERE formula_name = UPPER(l_modified_base_ele || '_payscale_ff');
1476
1477 IF (l_count = 0) THEN
1478 SELECT formula_type_id
1479 INTO l_formula_type_id
1480 FROM ff_formula_types
1481 WHERE formula_type_name = 'Oracle Payroll';
1482
1483 -- SELECT formula_text
1484 -- INTO l_formula_text
1485 -- FROM ff_formulas_f ff
1486 -- WHERE ff.formula_name = 'UK_PRORATION_SPINAL_POINT'
1487 -- AND ff.legislation_code = 'GB'
1488 -- AND ff.business_group_id IS NULL;
1489
1490 FOR cft4 IN c_formula_text('UK_PRORATION_SPINAL_POINT')
1491 LOOP
1492 l_formula_text := cft4.formula_text;
1493 END LOOP;
1494
1495 l_formula_name := UPPER(l_modified_base_formula || '_payscale_ff');
1496 l_description := 'Formula for Progression Point Proration';
1497
1498 l_formula_text := REPLACE(l_formula_text,
1499 'UK_PRORATION_SPINAL_POINT', l_formula_name);
1500
1501 l_formula_text := REPLACE(l_formula_text, 'UK Spinal Point',
1502 l_modified_base_ele || ' payscale ele');
1503
1504 INSERT INTO ff_formulas_f
1505 ( formula_id ,
1506 effective_start_date ,
1507 effective_end_date ,
1508 business_group_id ,
1509 legislation_code ,
1510 formula_type_id ,
1511 formula_name ,
1512 description ,
1513 formula_text ,
1514 last_update_date ,
1515 last_updated_by ,
1516 last_update_login ,
1517 created_by ,
1518 creation_date )
1519 VALUES
1520 ( ff_formulas_s.NEXTVAL , -- formula_id
1521 TO_DATE(l_date,'dd/mm/yyyy') , -- effective_start_date
1522 TO_DATE('31/12/4712', 'DD/MM/YYYY') , -- effective_end_date
1523 l_business_group_id , -- business_group_id
1524 NULL , -- legislation_code
1525 l_formula_type_id , -- formula_type_id
1526 l_formula_name , -- formula_name
1527 l_description , -- description
1528 l_formula_text , -- formula_text
1529 SYSDATE , -- last_update_date
1530 -1 , -- last_updated_by
1531 -1 , -- last_update_login
1532 -1 , -- created_by
1533 SYSDATE ); -- creation_date
1534
1535 -- SELECT formula_id
1536 -- INTO l_formula_id
1537 -- FROM ff_formulas_f ff
1538 -- WHERE ff.formula_name = l_formula_name
1539 -- AND ff.legislation_code IS NULL
1540 -- AND ff.business_group_id = l_business_group_id;
1541
1542 FOR cffi4 IN c_fast_formula_id (l_formula_name,
1543 l_business_group_id)
1544 LOOP
1545 l_formula_id := cffi4.formula_id;
1546 END LOOP;
1547
1548 l_req_id := fnd_request.submit_request(
1549 application => 'FF' ,
1550 program => 'BULKCOMPILE' ,
1551 argument1 => 'Oracle Payroll' ,
1552 argument2 => l_formula_name );
1553 --
1554 l_status_proc_rule_id := pay_formula_results.ins_stat_proc_rule
1555 (
1556 p_business_group_id => l_business_group_id
1557 ,p_legislation_code => NULL
1558 ,p_effective_start_date => TO_DATE(l_date,'dd/mm/yyyy')
1559 ,p_element_type_id => l_ele_id
1560 ,p_formula_id => l_formula_id
1561 ,p_processing_rule => 'P'
1562 );
1563
1564 l_for_res_id := pay_formula_results.ins_form_res_rule
1565 (
1566 p_business_group_id => l_business_group_id
1567 ,p_legislation_code => NULL
1568 ,p_status_processing_rule_id => l_status_proc_rule_id
1569 ,p_input_value_id => l_ipv_pv
1570 ,p_result_name => 'L_AMOUNT'
1571 ,p_element_type_id => l_ele_id
1572 ,p_result_rule_type => 'D'
1573 ,p_effective_start_date => TO_DATE(l_date,'dd/mm/yyyy')
1574 );
1575 ELSE
1576 hr_utility.trace(l_modified_base_formula ||
1577 '_payscale_ff already exists.');
1578 END IF;
1579 END IF;
1580
1581 hr_utility.trace('Enabling dynamic trigger ');
1582 enable_dynamic_triggers
1583 (
1584 p_business_group_id => l_business_group_id ,
1585 p_salary_flag => l_salary_flag ,
1586 p_grade_flag => l_grade_flag ,
1587 p_spinal_flag => l_pscale_flag ,
1588 p_address_flag => 'FALSE' ,
1589 p_location_flag => 'FALSE'
1590 );
1591 hr_utility.set_location('Leaving }'|| gv_package || lv_procedure_name, 250);
1592
1593 END standard_proc;
1594 -- ***************************************************************************
1595 -- proration_group_proc
1596 -- ***************************************************************************
1597 PROCEDURE proration_group_proc
1598 (
1599 p_pgname IN VARCHAR2 DEFAULT NULL ,
1600 p_pg_startdate IN VARCHAR2 DEFAULT NULL ,
1601 p_pggrd IN VARCHAR2 DEFAULT NULL ,
1602 p_pggrdrt IN VARCHAR2 DEFAULT NULL ,
1603 p_pgchgpysc IN VARCHAR2 DEFAULT NULL ,
1604 p_pgchrtpysc IN VARCHAR2 DEFAULT NULL ,
1605 p_pgchgsal IN VARCHAR2 DEFAULT NULL ,
1606 p_pgtermemp IN VARCHAR2 DEFAULT NULL ,
1607 p_pgnewhre IN VARCHAR2 DEFAULT NULL ,
1608 p_pgstchenea IN VARCHAR2 DEFAULT NULL ,
1609 p_pgstchended IN VARCHAR2 DEFAULT NULL ,
1610 p_pgchgloc IN VARCHAR2 DEFAULT NULL ,
1611 p_business_group_pg IN VARCHAR2 DEFAULT NULL
1612 )
1613 AS
1614 l_business_group_id NUMBER ;
1615 l_count NUMBER ;
1616 l_event_group_id NUMBER ;
1617 l_dt_event_id NUMBER ;
1618 l_dated_table_id NUMBER ;
1619 l_ovn NUMBER ;
1620 l_pg_name VARCHAR2(40);
1621 l_business_group_name VARCHAR2(80);
1622 lv_procedure_name VARCHAR2(80) := '.proration_group_proc' ;
1623
1624 l_salary_flag VARCHAR2(40) := 'FALSE';
1625 l_grade_flag VARCHAR2(40) := 'FALSE';
1626 l_payscale_flag VARCHAR2(40) := 'FALSE';
1627 l_address_flag VARCHAR2(40) := 'FALSE';
1628 l_location_flag VARCHAR2(40) := 'FALSE';
1629 BEGIN
1630 hr_utility.set_location('Entering {'|| gv_package || lv_procedure_name, 250);
1631 valid_business_group(p_business_group_name => p_business_group_pg);
1632 l_business_group_name := p_business_group_pg;
1633 FOR c1 IN c_biz_group (l_business_group_name)
1634 LOOP
1635 l_business_group_id := c1.business_group_id;
1636 END LOOP;
1637
1638 l_pg_name := UPPER(SUBSTR(REPLACE(p_pgname, ' ',' '), 1, 40));
1639
1640 -- a) Step a
1641
1642 -- PAY_DATETRACKED_EVENTS_API. CREATE_DATETRACKED_EVENT
1643 -- PAY_EVENT_GROUPS_API.CREATE_EVENT_GROUP
1644
1645 l_count := 0;
1646
1647 SELECT COUNT(*)
1648 INTO l_count
1649 FROM pay_event_groups
1650 WHERE event_group_name = l_pg_name;
1651
1652 IF (l_count = 0) THEN
1653 hr_utility.trace('If condition ... Creating event groups');
1654 pay_event_groups_api.create_event_group
1655 (
1656 p_effective_date => TO_DATE(p_pg_startdate,'dd/mm/yyyy')
1657 ,p_event_group_name => l_pg_name
1658 ,p_event_group_type => 'P'
1659 ,p_proration_type => 'P'
1660 ,p_business_group_id => l_business_group_id
1661 ,p_legislation_code => NULL
1662 ,p_event_group_id => l_event_group_id
1663 ,p_object_version_number => l_ovn
1664 );
1665 ELSE
1666 hr_utility.trace('Else condition ...');
1667 l_event_group_id := NULL;
1668
1669 -- SELECT event_group_id
1670 -- INTO l_event_group_id
1671 -- FROM pay_event_groups
1672 -- WHERE event_group_name = l_pg_name;
1673
1674 FOR cegid IN c_event_group_id (l_pg_name)
1675 LOOP
1676 l_event_group_id := cegid.event_group_id;
1677 END LOOP;
1678 END IF;
1679
1680 -- Change in Grade
1681
1682 IF (UPPER(p_pggrd) = 'YES') THEN
1683 hr_utility.trace('If condition ... p_pggrd');
1684 FOR c2 IN c_dated_pay_table ('PER_ALL_ASSIGNMENTS_F')
1685 LOOP
1686 l_dated_table_id := c2.dated_table_id;
1687 END LOOP;
1688 l_count := 0;
1689
1690 SELECT COUNT(*)
1691 INTO l_count
1692 FROM pay_datetracked_events
1693 WHERE column_name = 'GRADE_ID'
1694 AND event_group_id = l_event_group_id
1695 AND dated_table_id = l_dated_table_id ;
1696
1697 IF (l_count = 0) THEN
1698 hr_utility.trace('If condition ... creating datetracked event');
1699
1700 pay_datetracked_events_api.create_datetracked_event
1701 (
1702 p_validate => FALSE
1703 ,p_effective_date => TO_DATE(p_pg_startdate,'dd/mm/yyyy')
1704 ,p_event_group_id => l_event_group_id
1705 ,p_dated_table_id => l_dated_table_id
1706 -- of per_all_assignments_f
1707 ,p_update_type => 'U'
1708 ,p_column_name => 'GRADE_ID'
1709 ,p_business_group_id => l_business_group_id
1710 ,p_legislation_code => NULL
1711 ,p_datetracked_event_id => l_dt_event_id
1712 ,p_object_version_number => l_ovn );
1713 END IF;
1714 END IF;
1715
1716 -- Change in Pay Scale
1717
1718 IF (UPPER(p_pgchgpysc) = 'YES') THEN
1719 hr_utility.trace('If condition ... p_pgchgpysc');
1720
1721 l_payscale_flag := 'TRUE';
1722
1723 FOR c2 IN c_dated_pay_table ('PER_SPINAL_POINT_PLACEMENTS_F')
1724 LOOP
1725 l_dated_table_id := c2.dated_table_id;
1726 END LOOP;
1727 l_count := 0;
1728
1729 SELECT COUNT(*)
1730 INTO l_count
1731 FROM pay_datetracked_events
1732 WHERE column_name = 'STEP_ID'
1733 AND event_group_id = l_event_group_id
1734 AND dated_table_id = l_dated_table_id ;
1735
1736 IF (l_count = 0) THEN
1737 hr_utility.trace('If condition ... creating datetracked event');
1738
1739 pay_datetracked_events_api.create_datetracked_event
1740 (
1741 p_validate => FALSE
1742 ,p_effective_date => TO_DATE(p_pg_startdate,'dd/mm/yyyy')
1743 ,p_event_group_id => l_event_group_id
1744 ,p_dated_table_id => l_dated_table_id
1745 -- PER_SPINAL_POINT_PLACEMENTS_F
1746 ,p_update_type => 'U'
1747 ,p_column_name => 'STEP_ID'
1748 ,p_business_group_id => l_business_group_id
1749 ,p_legislation_code => NULL
1750 ,p_datetracked_event_id => l_dt_event_id
1751 ,p_object_version_number => l_ovn
1752 ) ;
1753 END IF;
1754 END IF;
1755
1756 -- Change in Grade Rate or Change in Rate associated with Payscale
1757
1758 IF (UPPER(p_pggrdrt) = 'YES' OR UPPER(p_pgchrtpysc) = 'YES') THEN
1759 hr_utility.trace('If condition ... p_pggrdrt p_pgchrtpysc');
1760
1761 l_grade_flag := 'TRUE';
1762
1763 FOR c2 IN c_dated_pay_table ('PAY_GRADE_RULES_F')
1764 LOOP
1765 l_dated_table_id := c2.dated_table_id;
1766 END LOOP;
1767 l_count := 0;
1768
1769 SELECT COUNT(*)
1770 INTO l_count
1771 FROM pay_datetracked_events
1772 WHERE column_name = 'VALUE'
1773 AND event_group_id = l_event_group_id
1774 AND dated_table_id = l_dated_table_id ;
1775
1776 IF (l_count = 0) THEN
1777 hr_utility.trace('If condition ... creating datetracked event');
1778
1779 pay_datetracked_events_api.create_datetracked_event
1780 (
1781 p_validate => FALSE
1782 ,p_effective_date => TO_DATE(p_pg_startdate,'dd/mm/yyyy')
1783 ,p_event_group_id => l_event_group_id
1784 ,p_dated_table_id => l_dated_table_id
1785 ,p_update_type => 'U'
1786 ,p_column_name => 'VALUE'
1787 ,p_business_group_id => l_business_group_id
1788 ,p_legislation_code => NULL
1789 ,p_datetracked_event_id => l_dt_event_id
1790 ,p_object_version_number => l_ovn
1791 ) ;
1792 END IF;
1793 END IF;
1794 --*********************************************************
1795 -- Change in Salary
1796 -- Termination of an employee
1797 -- New Hire
1798 -- Start/Change/End of earning
1799 -- Start/Change/End of deduction
1800 --********************************************************
1801
1802 IF (UPPER(p_pgchgsal ) = 'YES' OR
1803 UPPER(p_pgtermemp) = 'YES' OR
1804 UPPER(p_pgnewhre ) = 'YES' OR
1805 UPPER(p_pgstchenea) = 'YES' OR
1806 UPPER(p_pgstchended) = 'YES' ) THEN
1807 hr_utility.trace('If condition ... p_pgchgsal p_pgtermemp p_pgnewhre p_pgstchenea p_pgstchended');
1808
1809 l_salary_flag := 'TRUE';
1810
1811 FOR c2 IN c_dated_pay_table ('PAY_ELEMENT_ENTRIES_F')
1812 LOOP
1813 l_dated_table_id := c2.dated_table_id;
1814 END LOOP;
1815
1816 l_count := 0;
1817
1818 SELECT COUNT(*)
1819 INTO l_count
1820 FROM pay_datetracked_events
1821 WHERE column_name = 'EFFECTIVE_START_DATE'
1822 AND event_group_id = l_event_group_id
1823 AND dated_table_id = l_dated_table_id ;
1824
1825 IF (l_count = 0) THEN
1826 hr_utility.trace('If condition ... creating datetracked event');
1827 pay_datetracked_events_api.create_datetracked_event
1828 (
1829 p_validate => FALSE
1830 ,p_effective_date => TO_DATE(p_pg_startdate,'dd/mm/yyyy')
1831 ,p_event_group_id => l_event_group_id
1832 ,p_dated_table_id => l_dated_table_id
1833 -- of pay_element_entries_f
1834 ,p_update_type => 'U'
1835 ,p_column_name => 'EFFECTIVE_START_DATE'
1836 ,p_business_group_id => l_business_group_id
1837 ,p_legislation_code => NULL
1838 ,p_datetracked_event_id => l_dt_event_id
1839 ,p_object_version_number => l_ovn
1840 ) ;
1841 END IF;
1842
1843 l_count := 0;
1844
1845 SELECT COUNT(*)
1846 INTO l_count
1847 FROM pay_datetracked_events
1848 WHERE column_name = 'EFFECTIVE_END_DATE'
1849 AND event_group_id = l_event_group_id
1850 AND dated_table_id = l_dated_table_id;
1851
1852 IF (l_count = 0) THEN
1853 hr_utility.trace('If condition ... creating datetracked event');
1854 pay_datetracked_events_api.create_datetracked_event
1855 (
1856 p_validate => FALSE
1857 ,p_effective_date =>
1858 TO_DATE(p_pg_startdate,'dd/mm/yyyy')
1859 ,p_event_group_id => l_event_group_id
1860 ,p_dated_table_id => l_dated_table_id
1861 -- of pay_element_entries_f
1862 ,p_update_type => 'U'
1863 ,p_column_name => 'EFFECTIVE_END_DATE'
1864 ,p_business_group_id => l_business_group_id
1865 ,p_legislation_code => NULL
1866 ,p_datetracked_event_id => l_dt_event_id
1867 ,p_object_version_number => l_ovn );
1868 END IF;
1869 END IF;
1870
1871 -- Change of location
1872
1873 IF ( UPPER(p_pgchgloc) = 'YES' ) THEN
1874 hr_utility.trace('If condition ... p_pgchgloc');
1875 l_location_flag := 'TRUE';
1876
1877 FOR c2 IN c_dated_pay_table ('PER_ALL_ASSIGNMENTS_F')
1878 LOOP
1879 l_dated_table_id := c2.dated_table_id;
1880 END LOOP;
1881 l_count := 0;
1882
1883 SELECT COUNT(*)
1884 INTO l_count
1885 FROM pay_datetracked_events
1886 WHERE column_name = 'LOCATION_ID'
1887 AND event_group_id = l_event_group_id
1888 AND dated_table_id = l_dated_table_id ;
1889
1890 IF (l_count = 0) THEN
1891 hr_utility.trace('If condition ... creating datetracked event');
1892
1893 pay_datetracked_events_api.create_datetracked_event
1894 (
1895 p_validate => FALSE
1896 ,p_effective_date => TO_DATE(p_pg_startdate,'dd/mm/yyyy')
1897 ,p_event_group_id => l_event_group_id
1898 ,p_dated_table_id => l_dated_table_id
1899 -- PER_ALL_ASSIGNMENTS_F
1900 ,p_update_type => 'U'
1901 ,p_column_name => 'LOCATION_ID'
1902 ,p_business_group_id => l_business_group_id
1903 ,p_legislation_code => NULL
1904 ,p_datetracked_event_id => l_dt_event_id
1905 ,p_object_version_number => l_ovn
1906 ) ;
1907 END IF;
1908 END IF;
1909 hr_utility.trace('Enable Dynamic Trigger');
1910
1911 enable_dynamic_triggers
1912 (
1913 p_business_group_id => l_business_group_id ,
1914 p_salary_flag => l_salary_flag ,
1915 p_grade_flag => l_grade_flag ,
1916 p_spinal_flag => l_payscale_flag ,
1917 p_address_flag => l_address_flag ,
1918 p_location_flag => l_location_flag
1919 );
1920 hr_utility.set_location('Leaving }'|| gv_package || lv_procedure_name, 250);
1921 END proration_group_proc;
1922 --*************************************************************************
1923 --Procedure : Element_proc
1924 --*************************************************************************
1925 PROCEDURE element_proc
1926 (
1927 p_ele_startdate IN VARCHAR2 DEFAULT NULL,
1928 p_business_group IN VARCHAR2 DEFAULT NULL,
1929 p_ele_name IN VARCHAR2 DEFAULT NULL,
1930 p_ele_desc IN VARCHAR2 DEFAULT NULL,
1931 p_ele_terminate IN VARCHAR2 DEFAULT NULL,
1932 p_ele_uenterable IN VARCHAR2 DEFAULT NULL,
1933 p_ele_addentry IN VARCHAR2 DEFAULT NULL,
1934 p_ele_payment IN VARCHAR2 DEFAULT NULL,
1935 p_ele_recur IN VARCHAR2 DEFAULT NULL,
1936 p_ele_priclass IN VARCHAR2 DEFAULT NULL,
1937 p_ele_multientry IN VARCHAR2 DEFAULT NULL,
1938 p_ele_repname IN VARCHAR2 DEFAULT NULL,
1939 p_ele_pg IN VARCHAR2 DEFAULT NULL,
1940 p_ele_teach_eng IN VARCHAR2 DEFAULT NULL,
1941 p_ele_teach_scot IN VARCHAR2 DEFAULT NULL,
1942 p_ele_extra_td IN VARCHAR2 DEFAULT NULL,
1943 p_ele_extra_psv IN VARCHAR2 DEFAULT NULL,
1944 p_ele_extra_qualifier IN VARCHAR2 DEFAULT NULL,
1945 p_ele_extra_fte IN VARCHAR2 DEFAULT NULL,
1946 p_ele_extra_sh IN VARCHAR2 DEFAULT NULL
1947 )
1948 AS
1949 l_count NUMBER;
1950 l_pg_id NUMBER;
1951 l_ele_id NUMBER;
1952 l_ipv_as NUMBER;
1953 l_primary_class_name pay_element_classifications.classification_name%TYPE;
1954 l_default_priority NUMBER;
1955 l_etei_id NUMBER;
1956 l_etei_ovn NUMBER;
1957
1958 l_pg_name VARCHAR2(40);
1959 l_business_group_name VARCHAR2(80);
1960 lv_procedure_name VARCHAR2(250) := '.element_proc';
1961 BEGIN
1962 hr_utility.set_location('Entering {'|| gv_package || lv_procedure_name, 10);
1963 l_count := 0;
1964
1965 l_business_group_name := p_business_group;
1966
1967 SELECT COUNT(*)
1968 INTO l_count
1969 FROM pay_element_types_f
1970 WHERE UPPER(element_name) = UPPER(LTRIM(RTRIM(p_ele_name)));
1971
1972 hr_utility.trace('The count is ' || TO_CHAR(l_count) );
1973
1974 IF (l_count = 0) THEN
1975 hr_utility.trace('If condition ' );
1976
1977 l_pg_name := UPPER(SUBSTR(REPLACE(p_ele_pg, ' ',' '), 1, 40));
1978
1979 -- SELECT event_group_id
1980 -- INTO l_pg_id
1981 -- FROM pay_event_groups
1982 -- WHERE UPPER(event_group_name) = l_pg_name;
1983
1984 FOR cegid IN c_event_group_id (l_pg_name)
1985 LOOP
1986 l_pg_id := cegid.event_group_id;
1987 END LOOP;
1988 l_primary_class_name := p_ele_priclass;
1989
1990 IF (l_primary_class_name = 'Pre-tax Deductions') THEN
1991 l_primary_class_name := 'Pre Tax Deductions' ;
1992 END IF;
1993 FOR c_pc IN c_primary_classification(l_primary_class_name)
1994 LOOP
1995 l_default_priority := c_pc.default_priority;
1996 END LOOP;
1997
1998 hr_utility.trace('Creating element ' );
1999
2000 l_ele_id := pay_db_pay_setup.create_element(
2001 p_element_name => LTRIM(RTRIM(p_ele_name))
2002 ,p_description => p_ele_desc
2003 ,p_reporting_name => p_ele_repname
2004 ,p_classification_name => l_primary_class_name
2005 ,p_post_termination_rule => p_ele_terminate
2006 ,p_processing_type => p_ele_recur
2007 ,p_processing_priority => l_default_priority
2008 ,p_standard_link_flag => 'N'
2009 ,p_business_group_name => p_business_group
2010 ,p_legislation_code => NULL
2011 ,p_effective_start_date =>
2012 TO_DATE(p_ele_startdate,'dd/mm/yyyy')
2013 ,p_effective_end_date => TO_DATE('31/12/4712','dd/mm/yyyy')
2014 ,p_mult_entries_allowed => p_ele_multientry
2015 ,p_add_entry_allowed_flag => p_ele_addentry
2016 ,p_proration_group_id => l_pg_id);
2017
2018
2019 IF (p_ele_teach_eng = 'YES' AND p_ele_payment = 'S') THEN
2020 l_ipv_as := NULL;
2021
2022 hr_utility.trace('If condition ... before creating input value ' );
2023
2024 FOR c_iv IN c_input_value(l_ele_id, 'Amount')
2025 LOOP
2026 l_ipv_as := c_iv.input_value_id;
2027 END LOOP;
2028
2029 IF (l_ipv_as IS NULL) THEN
2030
2031 hr_utility.trace('If condition ... Creating input value ');
2032
2033 l_ipv_as := pay_db_pay_setup.create_input_value(
2034 p_element_name => p_ele_name
2035 ,p_name => 'Amount'
2036 ,p_uom_code => 'M'
2037 ,p_mandatory_flag => 'X'
2038 ,p_display_sequence => 2
2039 ,p_business_group_name => l_business_group_name
2040 ,p_effective_start_date =>
2041 TO_DATE(p_ele_startdate,'dd/mm/yyyy')
2042 ,p_effective_end_date => TO_DATE('31/12/4712','DD/MM/YYYY')
2043 ,p_legislation_code => NULL);
2044 END IF;
2045 END IF;
2046 ELSE
2047 hr_utility.trace('Element ' || p_ele_name || ' already exists.');
2048 END IF;
2049
2050 IF (p_ele_extra_qualifier IS NOT NULL) THEN
2051 IF (l_count <> 0) THEN
2052 FOR ceti IN c_element_id(p_ele_name)
2053 LOOP
2054 l_ele_id := ceti.element_type_id;
2055 END LOOP;
2056 END IF;
2057 l_count := 0;
2058
2059 FOR cetei IN c_element_extra_info_cnt(l_ele_id)
2060 LOOP
2061 l_count := cetei.count;
2062 END LOOP;
2063 IF (l_count = 0) THEN
2064
2065 hr_utility.trace('If condition ... Creating element extra info' );
2066
2067 pay_db_pay_setup.set_session_date(trunc(sysdate));
2068 pay_element_extra_info_api.create_element_extra_info
2069 ( p_element_type_id => l_ele_id
2070 ,p_information_type => 'PQP_UK_ELEMENT_ATTRIBUTION'
2071 ,p_eei_information_category => 'PQP_UK_ELEMENT_ATTRIBUTION'
2072 ,p_eei_information1 => NVL(p_ele_extra_td, 'H')
2073 -- For Hourly Time Dimension
2074 ,p_eei_information2 => p_ele_extra_psv
2075 -- Spinal Points Pay Source Value
2076 ,p_eei_information3 => p_ele_extra_qualifier
2077 ,p_eei_information4 => NVL(p_ele_extra_fte, 'N')
2078 -- No FTE
2079 ,p_eei_information5 => NVL(p_ele_extra_sh, 'N')
2080 ,p_element_type_extra_info_id => l_etei_id
2081 ,p_object_version_number => l_etei_ovn
2082 -- 'No' Service History */
2083 );
2084 ELSE
2085 hr_utility.trace('Else condition..updating element extra info');
2086 pay_db_pay_setup.set_session_date(trunc(sysdate));
2087 l_etei_id := NULL;
2088 l_etei_ovn := NULL;
2089 FOR cetei1 IN c_element_extra_info_id(l_ele_id)
2090 LOOP
2091 l_etei_id := cetei1.element_type_extra_info_id;
2092 l_etei_ovn := cetei1.object_version_number;
2093 END LOOP;
2094
2095 pay_element_extra_info_api.update_element_extra_info
2096 (p_element_type_extra_info_id => l_etei_id
2097 ,p_object_version_number => l_etei_ovn
2098 ,p_eei_information_category => 'PQP_UK_ELEMENT_ATTRIBUTION'
2099 ,p_eei_information1 => NVL(p_ele_extra_td, 'H')
2100 -- For Hourly Time Dimension
2101 ,p_eei_information2 => p_ele_extra_psv
2102 -- Spinal Points Pay Source Value
2103 ,p_eei_information3 => p_ele_extra_qualifier
2104 ,p_eei_information4 => NVL(p_ele_extra_fte, 'N')
2105 -- No FTE
2106 ,p_eei_information5 => NVL(p_ele_extra_sh, 'N')
2107 -- 'No' Service History
2108 );
2109 END IF;
2110 END IF;
2111 hr_utility.set_location('Leaving }'|| gv_package || lv_procedure_name, 200);
2112 END element_proc;
2113 -- *************************************************************************
2114 -- Procedure : Input_Value_proc
2115 -- ***************************************************************************
2116 PROCEDURE input_value_proc
2117 (
2118 p_ipvalue_name IN VARCHAR2 DEFAULT NULL,
2119 p_ipvalue_uom IN VARCHAR2 DEFAULT NULL,
2120 p_ipvalue_required IN VARCHAR2 DEFAULT NULL,
2121 p_ipvalue_uenterble IN VARCHAR2 DEFAULT NULL,
2122 p_ipvalue_dfltval IN VARCHAR2 DEFAULT NULL,
2123 p_ipvalue_lkpval IN VARCHAR2 DEFAULT NULL,
2124 p_ipvalue_hotdflt IN VARCHAR2 DEFAULT NULL,
2125 p_ipvalue_formula IN VARCHAR2 DEFAULT NULL,
2126 p_ipvalue_minimum IN VARCHAR2 DEFAULT NULL,
2127 p_ipvalue_maximum IN VARCHAR2 DEFAULT NULL,
2128 p_ipvalue_error IN VARCHAR2 DEFAULT NULL,
2129 p_ipvalue_dispseq IN VARCHAR2 DEFAULT NULL,
2130 p_ipvalue_dbitem IN VARCHAR2 DEFAULT NULL,
2131 p_business_group_ipv IN VARCHAR2 DEFAULT NULL,
2132 p_ipvalue_startdate IN VARCHAR2 DEFAULT NULL,
2133 p_ele_name_ipv IN VARCHAR2 DEFAULT NULL
2134 )
2135 IS
2136 l_ele_id NUMBER;
2137 l_ipv_as NUMBER;
2138 l_formula_id NUMBER;
2139 l_business_group_name VARCHAR2(80);
2140 lv_procedure_name VARCHAR2(80) := '.input_value_proc';
2141 BEGIN
2142 hr_utility.set_location('Entering {'|| gv_package || lv_procedure_name, 10);
2143 FOR ceti IN c_element_id(p_ele_name_ipv)
2144 LOOP
2145 l_ele_id := ceti.element_type_id;
2146 END LOOP;
2147
2148 l_business_group_name := p_business_group_ipv;
2149
2150 FOR civ1 IN c_input_value(l_ele_id ,
2151 p_ipvalue_name)
2152 LOOP
2153 l_ipv_as := civ1.input_value_id;
2154 END LOOP;
2155
2156 IF (l_ipv_as IS NULL) THEN
2157 hr_utility.trace('If condition...');
2158 FOR cfi IN c_formula_id (p_ipvalue_formula)
2159 LOOP
2160 l_formula_id := cfi.formula_id;
2161 END LOOP;
2162 hr_utility.trace('Creating input value');
2163 l_ipv_as := pay_db_pay_setup.create_input_value(
2164 p_element_name => LTRIM(RTRIM(p_ele_name_ipv))
2165 ,p_name => SUBSTR(LTRIM(RTRIM(p_ipvalue_name)),
2166 1, 80)
2167 ,p_uom_code => p_ipvalue_uom
2168 ,p_mandatory_flag => p_ipvalue_required
2169 ,p_display_sequence => p_ipvalue_dispseq
2170 ,p_business_group_name => l_business_group_name
2171 ,p_effective_start_date =>
2172 TO_DATE(p_ipvalue_startdate,'dd/mm/yyyy')
2173 ,p_effective_end_date => TO_DATE('31/12/4712','DD/MM/YYYY')
2174 ,p_legislation_code => NULL
2175 ,p_min_value => p_ipvalue_minimum
2176 ,p_max_value => p_ipvalue_maximum
2177 ,p_default_value => p_ipvalue_dfltval
2178 ,p_lookup_type => p_ipvalue_lkpval
2179 ,p_formula_id => l_formula_id
2180 ,p_hot_default_flag => p_ipvalue_hotdflt
2181 ,p_generate_db_item_flag => p_ipvalue_dbitem );
2182 ELSE
2183 hr_utility.trace('Input Value ' || p_ele_name_ipv ||' already exists.');
2184 END IF;
2185 hr_utility.set_location('Leaving }'|| gv_package || lv_procedure_name, 10);
2186 END input_value_proc;
2187 --*************************************************************************
2188 --Procedure Formula_proc
2189 --****************************************************************************/
2190 PROCEDURE formula_proc
2191 (
2192 p_business_group_fr IN VARCHAR2 DEFAULT NULL,
2193 p_ele_name_fr IN VARCHAR2 DEFAULT NULL,
2194 p_ele_payment_fr IN VARCHAR2 DEFAULT NULL,
2195 p_ele_startdate_fr IN VARCHAR2 DEFAULT NULL,
2196 p_ele_teach_eng_fr IN VARCHAR2 DEFAULT NULL,
2197 p_ele_teach_scot_fr IN VARCHAR2 DEFAULT NULL,
2198 p_ele_priclass_fr IN VARCHAR2 DEFAULT NULL
2199 )
2200 AS
2201 l_count NUMBER ;
2202 l_formula_name VARCHAR2(80) ;
2203 l_new_formula_name VARCHAR2(80) ;
2204 l_formula_type_id NUMBER ;
2205 l_business_group_id NUMBER ;
2206 l_ele_id NUMBER ;
2207 l_status_proc_rule_id NUMBER ;
2208 l_for_res_id NUMBER ;
2209 l_formula_id NUMBER ;
2210 l_req_id NUMBER ;
2211 l_formula_text LONG ;
2212 l_description VARCHAR2(50) ;
2213 l_business_group_name VARCHAR2(80) ;
2214 l_result VARCHAR2(80) ;
2215 lv_procedure_name VARCHAR2(80) ;
2216 BEGIN
2217 hr_utility.set_location('Entering {'|| gv_package || lv_procedure_name, 10);
2218 l_count := 0;
2219 l_business_group_name := p_business_group_fr;
2220
2221 IF (UPPER(p_ele_priclass_fr) LIKE '%DEDUCTION%') THEN
2222 l_formula_name := 'UK_PRORATION_DEDUCTION';
2223 l_result := 'L_AMOUNT';
2224 ELSIF (p_ele_payment_fr = 'P') THEN
2225 l_formula_name := 'UK_PRORATION_SPINAL_POINT';
2226 l_result := 'L_AMOUNT';
2227 ELSIF (p_ele_payment_fr = 'G') THEN
2228 l_formula_name := 'UK_PRORATION_GRADE_RATE';
2229 l_result := 'L_AMOUNT';
2230 ELSIF (p_ele_teach_eng_fr = 'YES') THEN
2231 l_formula_name := 'UK_PRORATION_SAL_MANAGEMENT';
2232 l_result := 'RESULT1';
2233 ELSIF (p_ele_payment_fr = 'S' OR p_ele_teach_scot_fr = 'YES') THEN
2234 l_formula_name := 'UK_PRORATION_ALLOWANCE';
2235 l_result := 'L_AMOUNT';
2236 ELSE
2237 RETURN;
2238 END IF;
2239
2240 l_new_formula_name :=
2241 SUBSTR(UPPER(REPLACE(p_ele_name_fr, ' ', '_') || '_FF'), 1, 80);
2242
2243 SELECT COUNT(*)
2244 INTO l_count
2245 FROM ff_formulas_f
2246 WHERE formula_name = l_new_formula_name ;
2247
2248 IF (l_count = 0) THEN
2249
2250 SELECT formula_type_id
2251 INTO l_formula_type_id
2252 FROM ff_formula_types
2253 WHERE formula_type_name = 'Oracle Payroll';
2254
2255 -- SELECT formula_text
2256 -- INTO l_formula_text
2257 -- FROM ff_formulas_f ff
2258 -- WHERE ff.formula_name = l_formula_name
2259 -- AND ff.legislation_code = 'GB'
2260 -- AND ff.business_group_id IS NULL;
2261
2262 FOR cft IN c_formula_text (l_formula_name)
2263 LOOP
2264 l_formula_text := cft.formula_text;
2265 END LOOP;
2266
2267 IF (l_formula_name = 'UK_PRORATION_SAL_MANAGEMENT') THEN
2268 l_formula_text :=REPLACE(l_formula_text, 'annual_salary', 'Amount');
2269 l_formula_text :=
2270 REPLACE(l_formula_text, 'UK_PRORATION_SAL_MANAGEMENT',
2271 l_new_formula_name);
2272 END IF;
2273 IF (l_formula_name = 'UK_PRORATION_ALLOWANCE') THEN
2274 l_formula_text :=
2275 REPLACE(l_formula_text, 'annual_allowance', 'Amount');
2276 l_formula_text :=
2277 REPLACE(l_formula_text ,
2278 'UK_PRORATION_ALLOWANCE' ,
2279 l_new_formula_name );
2280 END IF;
2281 IF (l_formula_name = 'UK_PRORATION_DEDUCTION') THEN
2282 l_formula_text :=
2283 REPLACE(l_formula_text ,
2284 'UK_PRORATION_DEDUCTION' ,
2285 l_new_formula_name );
2286 END IF;
2287 IF (l_formula_name = 'UK_PRORATION_SPINAL_POINT') THEN
2288 l_formula_text :=
2289 REPLACE(l_formula_text ,
2290 'UK_PRORATION_SPINAL_POINT' ,
2291 l_new_formula_name );
2292 l_formula_text := REPLACE(l_formula_text, 'UK Spinal Point',
2293 p_ele_name_fr );
2294 END IF;
2295 IF (l_formula_name = 'UK_PRORATION_GRADE_RATE') THEN
2296 l_formula_text :=
2297 REPLACE(l_formula_text ,
2298 'UK_PRORATION_GRADE_RATE' ,
2299 l_new_formula_name );
2300 l_formula_text := REPLACE(l_formula_text, 'UK Grade Rate',
2301 p_ele_name_fr );
2302 END IF;
2303
2304 FOR c1 IN c_biz_group (l_business_group_name)
2305 LOOP
2306 l_business_group_id := c1.business_group_id;
2307 END LOOP;
2308
2309 l_description := 'Formula created for ' || p_ele_name_fr;
2310
2311 INSERT INTO ff_formulas_f
2312 (formula_id ,
2313 effective_start_date ,
2314 effective_end_date ,
2315 business_group_id ,
2316 legislation_code ,
2317 formula_type_id ,
2318 formula_name ,
2319 description ,
2320 formula_text ,
2321 last_update_date ,
2322 last_updated_by ,
2323 last_update_login ,
2324 created_by ,
2325 creation_date )
2326 VALUES
2327 (ff_formulas_s.NEXTVAL , -- formula_id
2328 TO_DATE(p_ele_startdate_fr,'dd/mm/yyyy') ,
2329 -- effective_start_date
2330 TO_DATE('31/12/4712', 'DD/MM/YYYY') , -- effective_end_date
2331 l_business_group_id , -- business_group_id
2332 NULL , -- legislation_code
2333 l_formula_type_id , -- formula_type_id
2334 l_new_formula_name , -- formula_name
2335 l_description , -- description
2336 l_formula_text , -- formula_text
2337 SYSDATE , -- last_update_date
2338 -1 , -- last_updated_by
2339 -1 , -- last_update_login
2340 -1 , -- created_by
2341 SYSDATE ); -- creation_date
2342
2343 -- SELECT formula_id
2344 -- INTO l_formula_id
2345 -- FROM ff_formulas_f ff
2346 -- WHERE ff.formula_name = l_new_formula_name
2347 -- AND ff.legislation_code IS NULL
2348 -- AND ff.business_group_id = l_business_group_id;
2349 FOR cffi IN c_fast_formula_id(l_new_formula_name,
2350 l_business_group_id)
2351 LOOP
2352 l_formula_id := cffi.formula_id;
2353 END LOOP;
2354
2355 l_req_id := fnd_request.submit_request(
2356 application => 'FF' ,
2357 program => 'BULKCOMPILE' ,
2358 argument1 => 'Oracle Payroll' ,
2359 argument2 => l_new_formula_name );
2360
2361 -- SELECT element_type_id
2362 -- INTO l_ele_id
2363 -- FROM pay_element_types_f
2364 -- WHERE UPPER(element_name) = UPPER(LTRIM(RTRIM(p_ele_name_fr)));
2365
2366 FOR ceti IN c_element_id(p_ele_name_fr)
2367 LOOP
2368 l_ele_id := ceti.element_type_id;
2369 END LOOP;
2370
2371 l_status_proc_rule_id := pay_formula_results.ins_stat_proc_rule
2372 (
2373 p_business_group_id => l_business_group_id
2374 ,p_legislation_code => NULL
2375 ,p_effective_start_date => TO_DATE(p_ele_startdate_fr,'dd/mm/yyyy')
2376 ,p_element_type_id => l_ele_id
2377 ,p_formula_id => l_formula_id
2378 ,p_processing_rule => 'P'
2379 );
2380
2381 l_for_res_id := pay_formula_results.ins_form_res_rule
2382 (
2383 p_business_group_id => l_business_group_id
2384 ,p_legislation_code => NULL
2385 ,p_status_processing_rule_id => l_status_proc_rule_id
2386 ,p_result_name => l_result
2387 ,p_element_type_id => l_ele_id
2388 ,p_result_rule_type => 'D'
2389 ,p_effective_start_date =>
2390 TO_DATE(p_ele_startdate_fr,'dd/mm/yyyy')
2391 );
2392 ELSE
2393 hr_utility.trace('Formula ' || l_new_formula_name ||' already exists.');
2394 END IF;
2395 hr_utility.set_location('Leaving }'|| gv_package || lv_procedure_name, 200);
2396 END formula_proc;
2397 --************************************************************************
2398 --**************************************************************************
2399 FUNCTION get_contract_type(p_assignment_id IN NUMBER ,
2400 p_effective_date IN DATE ) RETURN VARCHAR
2401 AS
2402 CURSOR c_assignment_details(p_assignment_id IN NUMBER ,
2403 p_effective_date IN DATE ) IS
2404 SELECT aat.contract_type
2405 FROM pqp_assignment_attributes_f aat
2406 WHERE aat.assignment_id = p_assignment_id
2407 AND p_effective_date between aat.effective_start_date
2408 AND aat.effective_end_date;
2409 l_contract_type VARCHAR2(100);
2410 BEGIN
2411 FOR cad IN c_assignment_details(p_assignment_id,
2412 p_effective_date)
2413 LOOP
2414 l_contract_type := cad.contract_type;
2415 END LOOP;
2416 RETURN l_contract_type;
2417 END;
2418 END pqp_proration_wrapper;