[Home] [Help]
PACKAGE BODY: APPS.PAY_HXC_DEPOSIT_INTERFACE
Source
1 PACKAGE BODY pay_hxc_deposit_interface AS
2 /* $Header: pyhxcdpi.pkb 120.5.12010000.2 2008/08/06 07:23:32 ubhat ship $ */
3
4 --
5 --
6 TYPE t_field_name IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
7 TYPE t_value IS TABLE OF VARCHAR2(150) INDEX BY BINARY_INTEGER;
8 TYPE t_attribute IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
9 --
10 --
11 --
12 TYPE t_segment IS TABLE OF varchar2(60) INDEX BY BINARY_INTEGER;
13
14 TYPE r_input_value IS RECORD (
15 name pay_input_values_f.name%TYPE
16 , id pay_input_values_f.input_value_id%TYPE
17 , value VARCHAR2(80));
18 TYPE t_input_value IS TABLE OF r_input_value INDEX BY BINARY_INTEGER;
19
20
21 TYPE pto_assignment_info_rec IS RECORD (
22 effective_start_date DATE
23 , effective_end_date DATE
24 , bg_id NUMBER(15) );
25
26 TYPE pto_assignment_info_tab IS TABLE OF pto_assignment_info_rec INDEX BY BINARY_INTEGER;
27
28 g_pto_assignment_info pto_assignment_info_tab;
29
30 TYPE pto_element_rec IS RECORD (
31 is_pto VARCHAR2(1),
32 att_num NUMBER(2),
33 iv_id NUMBER(15) );
34
35 TYPE pto_element_tab IS TABLE OF pto_element_rec INDEX BY BINARY_INTEGER;
36
37 g_pto_element pto_element_tab;
38
39 e_continue EXCEPTION;
40
41 -----------------------------------------------------------------------
42 PROCEDURE get_input_value_name (p_element_type_id IN number,
43 p_field_name IN VARCHAR2,
44 p_ipv_name OUT NOCOPY PAY_INPUT_VALUES_F.NAME%TYPE
45 )
46 IS
47 l_ivn_cached BOOLEAN := false;
48 l_iter BINARY_INTEGER;
49
50 cursor c_input_value_name
51 (p_ele_type_id in number
52 ,p_ipv_segment in VARCHAR2) is
53 select end_user_column_name
54 from fnd_descr_flex_column_usages c, hxc_mapping_components mpc
55 where c.application_id = 809
56 and c.descriptive_flexfield_name = 'OTC Information Types'
57 and c.descriptive_flex_context_code = 'ELEMENT - '||to_char(p_ele_type_id)
58 and c.application_column_name = mpc.segment
59 and upper(mpc.field_name) = p_ipv_segment;
60
61 BEGIN
62 l_iter := g_ivn_ct.first;
63 WHILE l_iter is not null LOOP
64 if (g_ivn_ct(l_iter).element_type_id = p_element_type_id) and
65 (g_ivn_ct(l_iter).field_name = p_field_name)
66 then
67 p_ipv_name := g_ivn_ct(l_iter).ipv_name;
68 l_ivn_cached := true;
69 exit;
70 end if;
71 l_iter := g_ivn_ct.next(l_iter);
72 END LOOP;
73
74 if (not l_ivn_cached) then
75
76 open c_input_value_name(p_element_type_id,upper(p_field_name));
77 fetch c_input_value_name into p_ipv_name;
78 close c_input_value_name;
79 --let us cache
80 l_iter := nvl(g_ivn_ct.last,0)+1;
81 g_ivn_ct(l_iter).element_type_id := p_element_type_id;
82 g_ivn_ct(l_iter).field_name := p_field_name;
83 g_ivn_ct(l_iter).ipv_name := p_ipv_name;
84 end if;
85
86 END get_input_value_name;
87
88 --
89 PROCEDURE populate_iv_map(p_element_type_id IN NUMBER,
90 p_effective_date IN DATE)
91 IS
92 l_cnt NUMBER(15);
93 l_input_value_id NUMBER(9);
94 l_name VARCHAR2(80);
95 l_seq NUMBER(5);
96 l_uom VARCHAR2(30);
97
98
99 -- Bug 6943339
100 -- Added one more column in the selected columns
101 -- list to pick up the UOM of the given input value
102 -- for format conversions later.
103
104 cursor csr_get_iv (p_element_type_id in number,
105 p_effective_date in date) IS
106 select piv.name, piv.input_value_id, piv.display_sequence,
107 piv.effective_start_date, piv.effective_end_date,
108 piv.uom
109 from pay_input_values_f piv
110 where piv.element_type_id = p_element_type_id
111 and p_effective_date between piv.effective_start_date
112 and piv.effective_end_date
113 order by piv.display_sequence
114 , piv.name;
115
116
117 BEGIN
118
119 if ((not g_iv_map_ct.exists(p_element_type_id)) or
120 (p_effective_date not between g_iv_map_ct(p_element_type_id).effective_start_date
121 and g_iv_map_ct(p_element_type_id).effective_end_date )
122 ) then
123 l_cnt := nvl(g_iv_mapping_ct.last,0)+1;
124 g_iv_map_ct(p_element_type_id).start_index := l_cnt;
125 open csr_get_iv(p_element_type_id, p_effective_date);
126 LOOP
127 -- Bug 6943339
128 -- Fetching UOM also after it was added in the selected columns.
129 -- UOM is assigned to iv_uom of the global table for IV mapping.
130 fetch csr_get_iv into l_name, l_input_value_id, l_seq,
131 g_iv_map_ct(p_element_type_id).effective_start_date,
132 g_iv_map_ct(p_element_type_id).effective_end_date,
133 l_uom ;
134 exit when csr_get_iv%notfound;
135 g_iv_mapping_ct(l_cnt).iv_name := l_name;
136 g_iv_mapping_ct(l_cnt).iv_id := l_input_value_id;
137 g_iv_mapping_ct(l_cnt).iv_seq := l_cnt;
138 g_iv_mapping_ct(l_cnt).iv_uom := l_uom;
139 --g_iv_mapping_ct(l_cnt).iv_seq := l_seq;
140 l_cnt := l_cnt + 1;
141 END LOOP;
142 close csr_get_iv;
143 g_iv_map_ct(p_element_type_id).stop_index := l_cnt-1;
144
145 end if;
146
147 END populate_iv_map;
148 --------------------------------------------------------------------------
149 PROCEDURE get_iv_map(p_ipv_name IN PAY_INPUT_VALUES_F.NAME%TYPE,
150 p_cnt IN NUMBER,
151 p_seq OUT NOCOPY NUMBER,
152 p_iv_id OUT NOCOPY pay_input_values_f.input_value_id%TYPE,
153 p_is_hour OUT NOCOPY BOOLEAN)
154 IS
155
156 l_iter BINARY_INTEGER;
157 l_iv_map_cached BOOLEAN := false;
158 lcode HR_LOOKUPS.lookup_code%TYPE;
159 BEGIN
160 p_is_hour := false;
161 l_iter := g_iv_lk_map_ct.first;
162 WHILE l_iter is not null LOOP
163 if (g_iv_lk_map_ct(l_iter).iv_name = p_ipv_name) then
164 -- cached value available
165 if (g_iv_lk_map_ct(l_iter).lcode = 'HOURS') then
166
167 p_is_hour := true;
168
169 IF p_cnt is not null then
170 p_seq := g_iv_mapping_ct(p_cnt).iv_seq;
171 p_iv_id := g_iv_mapping_ct(p_cnt).iv_id;
172 END IF;
173 end if;
174 l_iv_map_cached := true;
175 exit;
176 end if;
177 l_iter := g_iv_lk_map_ct.next(l_iter);
178 END LOOP;
179
180 if (not l_iv_map_cached) then
181
182 lcode := hxt_batch_process.get_lookup_code(p_ipv_name,sysdate);
183 --let us cache this data
184 l_iter := nvl(g_iv_lk_map_ct.last,0)+1;
185 g_iv_lk_map_ct(l_iter).iv_name := p_ipv_name;
186 g_iv_lk_map_ct(l_iter).lcode := lcode;
187
188 IF (lcode = 'HOURS' ) THEN
189 IF p_cnt is not null then
190 p_seq := g_iv_mapping_ct(p_cnt).iv_seq;
191 p_iv_id := g_iv_mapping_ct(p_cnt).iv_id;
192 END IF;
193
194 p_is_hour := true;
195 END IF;
196 end if;
197
198 END get_iv_map;
199
200 --------------------------- get_input_values -----------------------------
201 --
202 PROCEDURE get_input_values (p_element_name IN varchar2,
203 p_element_type_id IN number,
204 p_type IN varchar2,
205 p_measure IN number,
206 p_start_time IN date,
207 p_stop_time IN date,
208 p_effective_date IN date,
209 p_bb_id IN number,
210 p_bb_ovn IN number,
211 p_time_attribute_id IN number,
212 p_messages IN OUT NOCOPY hxc_self_service_time_deposit.message_table,
213 p_input_value OUT NOCOPY t_input_value,
214 p_field_name IN OUT NOCOPY t_field_name,
215 p_value IN OUT NOCOPY t_value,
216 p_segment OUT NOCOPY t_segment)
217 IS
218 --
219
220 l_ipv_name PAY_INPUT_VALUES_F.NAME%TYPE;
221 l_ipv_count NUMBER := 0;
222 --
223 --
224 l_seq NUMBER(5);
225 l_seq1 NUMBER(5);
226 l_iv NUMBER(5);
227 l_seg NUMBER(5);
228 l_cnt NUMBER(15);
229 l_input_value_id NUMBER(9);
230 l_name VARCHAR2(80);
231 l_iv_map_cached BOOLEAN;
232 l_iter BINARY_INTEGER;
233 l_index_input_value BINARY_INTEGER;
234 lcode HR_LOOKUPS.lookup_code%TYPE;
235 l_iv_id_1 pay_input_values_f.input_value_id%TYPE;
236
237
238 l_ivn_cached BOOLEAN;
239 l_is_hour BOOLEAN;
240 -- e_continue exception;
241
242
243 --
244 c_proc VARCHAR2(100) := 'pay_hxc_deposit_interface.get_input_values';
245
246 l_internal_value VARCHAR2(80);
247 l_display_value VARCHAR2(80);
248 --
249
250 BEGIN -- begin get_input_values
251
252 --
253 --FOR iv in 1 .. 15 LOOP
254 -- g_iv_mapping_ct(iv) := NULL;
255 --END LOOP;
256 --
257
258 --
259
260 populate_iv_map(p_element_type_id,p_effective_date);
261
262
263 --
264 -- Reset l_seq
265 l_seq := NULL;
266
267 IF g_iv_map_ct(p_element_type_id).start_index > g_iv_map_ct(p_element_type_id).stop_index
268 then
269 hxc_time_entry_rules_utils_pkg.add_error_to_table(
270 p_message_table => p_messages
271 ,p_message_name => 'HXC_HRPAY_RET_NO_IVS'
272 ,p_message_token => 'ELE_NAME&'||p_element_name
273 ,p_message_level => 'ERROR'
274 ,p_message_field => NULL
275 ,p_application_short_name => 'HXC'
276 ,p_timecard_bb_id => p_bb_id
277 ,p_time_attribute_id => p_time_attribute_id
278 ,p_timecard_bb_ovn => p_bb_ovn
279 ,p_time_attribute_ovn => NULL);
280 --
281 raise e_continue; --Bug#3004714
282
283 -- Start 2887210, i.e. Comment the raise error
284 -- Instead let the errors consolidate in the message table.
285 -- raise e_error;
286 -- End 2887210
287 --
288 END IF;
289 --
290 -- Initialize 15 input values to NULL
291 --
292 FOR iv in 1 .. 15 LOOP
293 p_input_value(iv) := NULL;
294 END LOOP;
295 --
296 -- Initialize 30 costing segments to NULL
297 --
298 FOR seg in 1 .. 30 LOOP
299 p_segment(seg) := NULL;
300 END LOOP;
301 --
302 -- Map Hours Input Value
303 --
304 --3675914
305 l_cnt := null;
306
307 FOR l_cnt in g_iv_map_ct(p_element_type_id).start_index .. g_iv_map_ct(p_element_type_id).stop_index LOOP
308
309 --let us check for cached value first. only if we dont find it in cached plsql table,
310 --are we going to check the db
311
312 get_iv_map(p_ipv_name => g_iv_mapping_ct(l_cnt).iv_name,
313 p_cnt => l_cnt,
314 p_seq => l_seq,
315 p_iv_id => l_iv_id_1,
316 p_is_hour => l_is_hour);
317
318 if l_is_hour then
319 -- 3675914
320 --p_input_value(l_seq).id := l_iv_id_1;
321 p_input_value(1).id := l_iv_id_1;
322 exit;
323 end if;
324
325 END LOOP;
326 --
327 IF l_seq IS NULL THEN
328 hxc_time_entry_rules_utils_pkg.add_error_to_table(
329 p_message_table => p_messages
330 ,p_message_name => 'HXC_HRPAY_RET_IV_NOT_FOUND'
331 ,p_message_token => 'ELE_NAME&'||p_element_name||'&IV_NAME&Hours'
332 ,p_message_level => 'ERROR'
333 ,p_message_field => NULL
334 ,p_application_short_name => 'HXC'
335 ,p_timecard_bb_id => p_bb_id
336 ,p_time_attribute_id => p_time_attribute_id
337 ,p_timecard_bb_ovn => p_bb_ovn
338 ,p_time_attribute_ovn => NULL);
339 --
340 raise e_continue; --Bug#3004714
341
342 -- Start 2887210, i.e. Comment the raise error
343 -- Instead let the errors consolidate in the message table.
344 -- raise e_error;
345 -- End 2887210
346 --
347 RETURN;
348 END IF;
349 --
350 hr_utility.set_location(c_proc, 10);
351 --
352 -- If the detail block is of type duration, then the number
353 -- of hours is in l_measure.
354 --
355 IF p_type = 'MEASURE' THEN
356
357 -- 3675914
358 --p_input_value(l_seq).value := to_char(p_measure);
359 p_input_value(1).value := to_char(p_measure);
360 --2223669
361 -- p_input_value(l_seq).name := 'Hours';
362 --p_input_value(l_seq).name := g_iv_mapping_ct(l_seq).iv_name;
363 p_input_value(1).name := g_iv_mapping_ct(l_seq).iv_name;
364 hr_utility.set_location(c_proc, 20);
365 --
366 END IF;
367 --
368 hr_utility.set_location(c_proc, 30);
369 --
370 -- If the detail block is of type range, then the number
371 -- of hours is derived from the difference between
372 -- p_start_time and p_stop_time.
373 --
374 IF p_type = 'RANGE' THEN
375
376 -- 3675914
377 --p_input_value(l_seq).value := (p_stop_time - p_start_time) * 24;
378 p_input_value(1).value := (p_stop_time - p_start_time) * 24;
379 -- 2223669
380 --p_input_value(l_seq).name := 'Hours';
381 --p_input_value(l_seq).name := g_iv_mapping_ct(l_seq).iv_name;
382 p_input_value(1).name := g_iv_mapping_ct(l_seq).iv_name;
383 --
384 hr_utility.set_location(c_proc, 40);
385 --
386 END IF;
387 --
388 -- Print out the Hours value
389 --
390 --hr_utility.trace('Input Value name is ' || p_input_value(l_seq).name);
391 --
392 --hr_utility.trace('Input Value value is ' || p_input_value(l_seq).value);
393 --
394 hr_utility.set_location(c_proc, 50);
395 --
396 hr_utility.trace('p_effective_date is ' ||
397 to_char(p_effective_date, 'DD-MON-YYYY'));
398 --
399 hr_utility.trace('p_start_time is ' || to_char(p_start_time, 'DD-MON-YYYY'));
400 --
401 -- Map all other input values
402 --
403 IF p_field_name.count <> 0 THEN
404 --
405 -- 3675914
406 l_index_input_value := 2;
407
408 FOR iv_cnt in p_field_name.first .. p_field_name.last LOOP
409
410 hr_utility.trace('p field name is '||p_field_name(iv_cnt));
411
412 IF upper(p_field_name(iv_cnt)) like 'INPUTVALUE%' THEN
413 --
414 hr_utility.set_location(c_proc, 60);
415 --
416 hr_utility.trace('---- In Input Value Loop ----');
417 --
418 -- Find IPV name corresponding to this mapping
419 -- component
420 --
421 --let us check if cached value exists
422
423 get_input_value_name(p_element_type_id, p_field_name(iv_cnt),l_ipv_name);
424
425 if (l_ipv_name is not null) then
426
427 --get the lookup code value
428 --let us look at the cached value.
429
430 get_iv_map(p_ipv_name => l_ipv_name,
431 p_cnt => null,
432 p_seq => l_seq1,
433 p_iv_id => l_iv_id_1,
434 p_is_hour => l_is_hour);
435
436 if (not l_is_hour) then
437
438 --
439 -- Next find the sequence for the input value
440 -- In this case, since there is no column on the
441 -- input values table, we have to count how many we get back
442 -- before we hit the input value name, and then set the
443 -- value as that value.
444
445 l_ipv_count := g_iv_map_ct(p_element_type_id).start_index;
446 LOOP
447 EXIT WHEN (NOT g_iv_mapping_ct.exists(l_ipv_count)); --OR
448 --l_ipv_count = g_iv_map_ct(p_element_type_id).stop_index);
449
450 hr_utility.trace('iv name is '||g_iv_mapping_ct(l_ipv_count).iv_name);
451 hr_utility.trace('iv id is '||to_char(g_iv_mapping_ct(l_ipv_count).iv_id));
452 hr_utility.trace('Field Name is '||p_field_name(iv_cnt));
453 hr_utility.trace('Value is '||p_value(iv_cnt));
454 hr_utility.trace('Value passed in is '||p_input_value(l_index_input_value).value);
455
456 if(g_iv_mapping_ct(l_ipv_count).iv_name = l_ipv_name) then
457 --
458 -- We can set the value since we have a match
459 --
460
461 -- WWB 4144047
462 -- Added check to see if the input value we are passing was set to a canonical date
463
464 -- Bug 6943339
465 -- Added extra OR condition to the IF below, and a new ELSIF construct.
466 -- The format change has to be done for not only the PTO entry effective dates
467 -- but also any input value which is captured and which has to be of date format.
468 -- The date value would be stored in attributes table in canonical format, and
469 -- has to be changed to a display format.
470 -- ELSIF added for a number datatype. Since there could be preference differences
471 -- b/w timekeeper and the HR user, the number format might be different. Eg. a
472 -- comma instead of a period is used for decimal by some customers.
473 -- OTL stores the NUMBER input values in attributes table after a conversion to
474 -- canonical format, hence while creation of BEE entries, there has to be a conversion
475 -- to number format.
476
477 IF ( pay_hxc_deposit_interface.g_canonical_iv_id_tab.EXISTS(g_iv_mapping_ct(l_ipv_count).iv_id)
478 OR g_iv_mapping_ct(l_ipv_count).iv_uom = 'D'
479 )
480 THEN
481
482 hr_utility.trace('setting date to display');
483
484 -- change the date from canonical to user
485
486 l_internal_value := p_value(iv_cnt);
487 l_display_value := NULL;
488
489 hr_utility.trace('internal format BEFORE is '||l_internal_value);
490 hr_utility.trace('display format BEFORE is '||l_display_value);
491
492 hr_chkfmt.changeformat (
493 l_internal_value, /* the value to be formatted (out - display) */
494 l_display_value, /* the formatted value on output (out - canonical) */
495 'D',
496 NULL );
497
498 hr_utility.trace('internal format BEFORE is '||l_internal_value);
499 hr_utility.trace('display format BEFORE is '||l_display_value);
500
501 p_input_value(l_index_input_value).value := l_display_value;
502
503 ELSIF g_iv_mapping_ct(l_ipv_count).iv_uom = 'N'
504 THEN
505 p_input_value(l_index_input_value).value := FND_NUMBER.CANONICAL_TO_NUMBER(p_value(iv_cnt));
506 ELSE
507
508 hr_utility.trace('not setting date to display');
509
510 p_input_value(l_index_input_value).value := p_value(iv_cnt);
511
512 END IF;
513
514
515 hr_utility.trace('iv name is '||g_iv_mapping_ct(l_ipv_count).iv_name);
516 hr_utility.trace('iv id is '||to_char(g_iv_mapping_ct(l_ipv_count).iv_id));
517 hr_utility.trace('Field Name is '||p_field_name(iv_cnt));
518 hr_utility.trace('Value is '||p_value(iv_cnt));
519 hr_utility.trace('Value passed in is '||p_input_value(l_index_input_value).value);
520
521 p_input_value(l_index_input_value).name := g_iv_mapping_ct(l_ipv_count).iv_name;
522 p_input_value(l_index_input_value).id := g_iv_mapping_ct(l_ipv_count).iv_id;
523 p_field_name(iv_cnt) := NULL;
524 p_value(iv_cnt) := NULL;
525
526 l_index_input_value := l_index_input_value + 1;
527
528 end if;
529
530 exit when (l_ipv_count = g_iv_map_ct(p_element_type_id).stop_index);
531
532 l_ipv_count := g_iv_mapping_ct.next(l_ipv_count);
533 END LOOP;
534
535 end if;
536
537 end if;
538
539 --
540 ELSIF upper(p_field_name(iv_cnt)) like 'COSTSEGMENT%' THEN
541 l_seg := to_number(replace(
542 upper(p_field_name(iv_cnt)), 'COSTSEGMENT'));
543 IF l_seg <= 30 THEN
544 p_segment(l_seg) := p_value(iv_cnt);
545 p_field_name(iv_cnt) := NULL;
546 p_value(iv_cnt) := NULL;
547 END IF;
548 END IF;
549 END LOOP;
550 END IF;
551
552 -- before we return the input value structure we must insure that
553 -- all ids are NULL where the corresponding value is also NULL
554 -- WWB 3403628
555
556 FOR iv in 1 .. 15
557 LOOP
558
559 IF ( p_input_value(iv).value IS NULL )
560 THEN
561
562 hr_utility.trace('setting NAME to null '||p_input_value(iv).name);
563 hr_utility.trace('id is '||p_input_value(iv).id);
564
565 p_input_value(iv).id := NULL;
566
567 END IF;
568
569 END LOOP;
570
571 hr_utility.set_location(c_proc, 90);
572 --
573 END get_input_values;
574
575 --------------------------- get_input_values over-------------------------
576
577 PROCEDURE get_full_name(p_person_id in number,
578 p_effective_date in date,
579 p_bb_id IN NUMBER,
580 p_bb_ovn IN NUMBER,
581 p_messages IN OUT NOCOPY hxc_self_service_time_deposit.message_table,
582 p_full_name OUT NOCOPY varchar2)
583 IS
584 l_cached boolean := false;
585 l_iter BINARY_INTEGER;
586 -- e_continue exception;
587
588 cursor c_get_full_name(p_person_id number,p_effective_date date) is
589 SELECT full_name,effective_start_date,effective_end_date
590 FROM per_people_f
591 WHERE person_id = p_person_id
592 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
593
594
595 BEGIN
596 l_iter := g_full_name_ct.first;
597 WHILE l_iter is not null LOOP
598 if ((g_full_name_ct(l_iter).person_id = p_person_id) and
599 (p_effective_date between g_full_name_ct(l_iter).effective_start_date
600 and g_full_name_ct(l_iter).effective_end_date )
601 )then
602 l_cached := true;
603 p_full_name := g_full_name_ct(l_iter).full_name;
604 exit;
605 end if;
606 l_iter := g_full_name_ct.next(l_iter);
607 END LOOP;
608
609
610 if (not l_cached)
611 then
612
613 l_iter := nvl(g_full_name_ct.last,0)+1;
614
615
616 OPEN c_get_full_name(p_person_id,p_effective_date);
617 FETCH c_get_full_name into g_full_name_ct(l_iter).full_name,
618 g_full_name_ct(l_iter).effective_start_date,
619 g_full_name_ct(l_iter).effective_end_date;
620
621 IF c_get_full_name%NOTFOUND then
622
623 hxc_time_entry_rules_utils_pkg.add_error_to_table(
624 p_message_table => p_messages
625 ,p_message_name => 'HR_52365_PTU_NO_PERSON_EXISTS'
626 ,p_message_token => NULL
627 ,p_message_level => 'ERROR'
628 ,p_message_field => NULL
629 ,p_application_short_name => 'PER'
630 ,p_timecard_bb_id => p_bb_id
631 ,p_time_attribute_id => NULL
632 ,p_timecard_bb_ovn => p_bb_ovn
633 ,p_time_attribute_ovn => NULL);
634
635 raise e_continue;
636 --
637 END IF;
638
639 g_full_name_ct(l_iter).person_id := p_person_id;
640 p_full_name := g_full_name_ct(l_iter).full_name;
641
642 CLOSE c_get_full_name;
643
644 end if;
645
646 END get_full_name;
647
648
649 ------------------------------------------------------
650 PROCEDURE get_assignment(p_person_id IN NUMBER,
651 p_effective_date IN DATE,
652 p_full_name IN VARCHAR2,
653 p_bb_id IN NUMBER,
654 p_bb_ovn IN NUMBER,
655 p_messages IN OUT NOCOPY hxc_self_service_time_deposit.message_table,
656 p_assignment_id OUT NOCOPY NUMBER,
657 p_business_group_id OUT NOCOPY NUMBER,
658 p_cost_allocation_structure OUT NOCOPY VARCHAR2)
659 IS
660
661 -- e_continue exception;
662
663 CURSOR c_assignment(p_person_id number,p_effective_date date) is
664 SELECT paf.assignment_id,
665 paf.business_group_id,
666 fnd_number.canonical_to_number (bsg.cost_allocation_structure),
667 paf.effective_start_date,
668 paf.effective_end_date
669 FROM per_all_assignments_f paf, per_business_groups bsg
670 WHERE paf.person_id = p_person_id
671 AND p_effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
672 AND paf.assignment_type = 'E'
673 AND paf.primary_flag = 'Y'
674 AND bsg.enabled_flag = 'Y'
675 AND paf.business_group_id = bsg.business_group_id;
676
677
678 BEGIN
679 --check the cached value
680 if ((not g_asg_ct.exists(p_person_id)) or
681 (p_effective_date not between g_asg_ct(p_person_id).effective_start_date
682 and g_asg_ct(p_person_id).effective_end_date)
683 )
684 then
685
686 --get the assignment id
687
688 OPEN c_assignment(p_person_id,p_effective_date);
689 FETCH c_assignment into p_assignment_id,p_business_group_id,p_cost_allocation_structure,
690 g_asg_ct(p_person_id).effective_start_date, g_asg_ct(p_person_id).effective_end_date;
691
692 IF c_assignment%NOTFOUND
693 THEN
694
695 hxc_time_entry_rules_utils_pkg.add_error_to_table(
696 p_message_table => p_messages
697 ,p_message_name => 'HXC_HRPAY_RET_NO_ASSIGN'
698 ,p_message_token => 'PERSON_NAME&'||p_full_name
699 ,p_message_level => 'ERROR'
700 ,p_message_field => NULL
701 ,p_application_short_name => 'HXC'
702 ,p_timecard_bb_id => p_bb_id
703 ,p_time_attribute_id => NULL
704 ,p_timecard_bb_ovn => p_bb_ovn
705 ,p_time_attribute_ovn => NULL);
706 --
707 raise e_continue;
708
709 END IF;
710
711 CLOSE c_assignment;
712
713 --let us get the data from g_full_name_ct table
714
715 g_asg_ct(p_person_id).assignment_id := p_assignment_id;
716 g_asg_ct(p_person_id).business_group_id := p_business_group_id;
717 g_asg_ct(p_person_id).cost_allocation_structure := p_cost_allocation_structure;
718
719 else
720
721 p_assignment_id := g_asg_ct(p_person_id).assignment_id ;
722 p_business_group_id:= g_asg_ct(p_person_id).business_group_id;
723 p_cost_allocation_structure := g_asg_ct(p_person_id).cost_allocation_structure;
724
725 end if;
726
727
728 END get_assignment;
729 ------------------------------------------------------
730 PROCEDURE get_element_name(p_element_type_id IN NUMBER,
731 p_effective_date IN DATE,
732 p_time_attribute_id IN NUMBER,
733 p_bb_id IN NUMBER,
734 p_bb_ovn IN NUMBER,
735 p_messages IN OUT NOCOPY hxc_self_service_time_deposit.message_table,
736 p_element_name OUT NOCOPY VARCHAR2)
737
738 IS
739
740 -- e_continue exception;
741
742 cursor c_get_element_name(p_element_type_id number,
743 p_user_language varchar2,
744 p_effective_date date) is
745 select petl.element_name,
746 pet.effective_start_date,
747 pet.effective_end_date
748 from pay_element_types_f pet,
749 pay_element_types_f_tl petl
750 where pet.element_type_id = p_element_type_id
751 and petl.element_type_id = pet.element_type_id
752 and p_user_language = petl.language
753 and p_effective_date between pet.effective_start_date
754 and pet.effective_end_date;
755
756 CURSOR c_chk_otc_information_type ( p_element_type_id NUMBER ) IS
757 select 'Y'
758 from fnd_descr_flex_column_usages c
759 where c.application_id = 809
760 and c.descriptive_flexfield_name = 'OTC Information Types'
761 and c.descriptive_flex_context_code = 'ELEMENT - '||p_element_type_id;
762
763 l_dummy VARCHAR2(1);
764
765
766 BEGIN
767
768 if ((not g_ele_type_ct.exists(p_element_type_id)) or
769 (p_effective_date not between g_ele_type_ct(p_element_type_id).effective_start_date and
770 g_ele_type_ct(p_element_type_id).effective_end_date)
771 ) then
772
773 OPEN c_get_element_name(p_element_type_id,user_language,p_effective_date);
774 FETCH c_get_element_name INTO p_element_name,
775 g_ele_type_ct(p_element_type_id).effective_start_date,
776 g_ele_type_ct(p_element_type_id).effective_end_date;
777
778 IF c_get_element_name%NOTFOUND then
779
780 hxc_time_entry_rules_utils_pkg.add_error_to_table(
781 p_message_table => p_messages
782 ,p_message_name => 'HXC_HRPAY_RET_NO_ELE_NAME'
783 ,p_message_token => 'ELE_TYPE_ID&'||to_char(p_element_type_id)
784 ,p_message_level => 'ERROR'
785 ,p_message_field => NULL
786 ,p_application_short_name => 'HXC'
787 ,p_timecard_bb_id => p_bb_id
788 ,p_time_attribute_id => p_time_attribute_id
789 ,p_timecard_bb_ovn => p_bb_ovn
790 ,p_time_attribute_ovn => NULL);
791 --
792 raise e_continue; --Bug#3004714
793 END IF;
794 g_ele_type_ct(p_element_type_id).element_name := p_element_name;
795 CLOSE c_get_element_name;
796
797 -- now check tha that generate flexfield and mapping process has been
798 -- run for this element
799
800 OPEN c_chk_otc_information_type ( p_element_type_id );
801 FETCH c_chk_otc_information_type INTO l_dummy;
802
803 IF c_chk_otc_information_type%FOUND
804 THEN
805
806 p_element_name := g_ele_type_ct(p_element_type_id).element_name;
807
808 ELSE
809
810 -- clear out global element table for this element since it failed
811 -- the validation
812
813 g_ele_type_ct.DELETE(p_element_type_id);
814
815 hxc_time_entry_rules_utils_pkg.add_error_to_table(
816 p_message_table => p_messages
817 ,p_message_name => 'HXC_GENERATE_FLEXFIELD_MAPPING'
818 ,p_message_token => 'ELEMENT_NAME&'||p_element_name
819 ,p_message_level => 'ERROR'
820 ,p_message_field => NULL
821 ,p_application_short_name => 'HXC'
822 ,p_timecard_bb_id => p_bb_id
823 ,p_time_attribute_id => p_time_attribute_id
824 ,p_timecard_bb_ovn => p_bb_ovn
825 ,p_time_attribute_ovn => NULL);
826
827 END IF;
828
829 CLOSE c_chk_otc_information_type;
830
831
832 else
833 p_element_name := g_ele_type_ct(p_element_type_id).element_name;
834
835 end if;
836
837
838
839 END get_element_name;
840
841 -------------------------------------------------------
842
843 PROCEDURE get_link(p_assignment_id in number ,
844 p_element_type_id in number,
845 p_effective_date in date,
846 p_element_link_id OUT NOCOPY number)
847
848 IS
849 l_cached BOOLEAN := false;
850 l_iter BINARY_INTEGER;
851 -- e_continue exception;
852 BEGIN
853
854 l_iter := g_link_ct.first;
855
856 -- get the start and end index and search through g_link_ct to find a matching record
857 WHILE l_iter is not null
858 LOOP
859 if ((p_assignment_id = g_link_ct(l_iter).assignment_id) and
860 (p_element_type_id = g_link_ct(l_iter).element_type_id) and
861 (p_effective_date = g_link_ct(l_iter).effective_date))
862 then
863 l_cached := true;
864 p_element_link_id := g_link_ct(l_iter).element_link_id;
865 exit;
866 end if;
867
868 l_iter := g_link_ct.next(l_iter);
869
870 END LOOP;
871
872
873 if (not l_cached) then
874 p_element_link_id := hr_entry_api.get_link(p_assignment_id,
875 p_element_type_id,
876 p_effective_date);
877
878 -- we have queried to get the link. let us cache it
879
880 l_iter := nvl(g_link_ct.last,0)+1;
881 g_link_ct(l_iter).assignment_id := p_assignment_id;
882 g_link_ct(l_iter).element_type_id := p_element_type_id;
883 g_link_ct(l_iter).effective_date := p_effective_date;
884 g_link_ct(l_iter).element_link_id := p_element_link_id;
885
886
887 end if;
888
889
890 END get_link;
891
892
893 --
894 --------------------------- pay_retrieval_process ---------------------------
895 --
896 FUNCTION pay_retrieval_process RETURN VARCHAR2 IS
897
898 l_retrieval_process HXC_TIME_RECIPIENTS.APPLICATION_RETRIEVAL_FUNCTION%TYPE;
899
900 BEGIN
901
902 l_retrieval_process := 'BEE Retrieval Process';
903
904 RETURN l_retrieval_process;
905
906 END pay_retrieval_process;
907
908 --
909 --------------------------- hr_retrieval_process ---------------------------
910 --
911 FUNCTION hr_retrieval_process RETURN VARCHAR2 IS
912
913 l_retrieval_process HXC_TIME_RECIPIENTS.APPLICATION_RETRIEVAL_FUNCTION%TYPE;
914
915 BEGIN
916
917 l_retrieval_process := 'HR Retrieval Process';
918
919 RETURN l_retrieval_process;
920
921 END hr_retrieval_process;
922
923
924 --
925 --------------------------- pay_update_process ----------------------------
926 --
927
928 PROCEDURE pay_update_process
929 (p_operation IN VARCHAR2) IS
930
931 l_blocks hxc_self_service_time_deposit.timecard_info;
932 l_attributes hxc_self_service_time_deposit.app_attributes_info;
933 l_messages hxc_self_service_time_deposit.message_table;
934 l_proc VARCHAR2(100) := 'pay_hxc_deposit_interface.PAY_UPDATE_PROCESS';
935
936 BEGIN
937 --
938 hr_utility.set_location(l_proc, 10);
939 --
940 hr_utility.set_location(l_proc, 20);
941 --
942 hxc_self_service_time_deposit.get_app_hook_params(
943 p_building_blocks => l_blocks,
944 p_app_attributes => l_attributes,
945 p_messages => l_messages);
946 --
947 hr_utility.set_location(l_proc, 40);
948 --
949
950 -- reset all the programatically updateble comps to null first
951
952 --hxc_layout_utils_pkg.reset_non_updatable_comps ( p_attributes => l_attributes );
953
954 pay_update_timecard
955 (p_attributes => l_attributes,
956 p_blocks => l_blocks );
957
958 hxc_self_service_time_deposit.set_app_hook_params(
959 p_building_blocks => l_blocks,
960 p_app_attributes => l_attributes,
961 p_messages => l_messages);
962
963 END pay_update_process;
964
965 --
966 --------------------------- pay_update_timecard ----------------------------
967 --
968 PROCEDURE pay_update_timecard
969 (p_attributes IN OUT NOCOPY hxc_self_service_time_deposit.app_attributes_info,
970 p_blocks in hxc_self_service_time_deposit.timecard_info
971 )
972 is
973
974 l_index NUMBER;
975 l_index_gaz NUMBER;
976
977 l_effective_date DATE;
978 l_day NUMBER;
979 l_parent_bb_id NUMBER;
980 l_resource_id NUMBER;
981 l_start_time DATE;
982 l_canonical_date VARCHAR2(25);
983
984 ----------------------------------------------------------------------
985 ---local procedure :set_pto_element_date
986 ---------------------------------------------------------------------
987 Procedure set_pto_element_date
988 (p_attributes in out NOCOPY hxc_self_service_time_deposit.app_attributes_info,
989 p_tbb_id in NUMBER,
990 p_resource_id in NUMBER,
991 p_effective_date in date,
992 p_canonical_date in varchar2
993 ) is
994
995 l_index NUMBER;
996 l_attribute HXC_ATTRIBUTE_TYPE;
997 l_old_attribute HXC_ATTRIBUTE_TYPE;
998
999
1000
1001 l_dd_mon_yyyy_date VARCHAR2(11);
1002
1003 l_test boolean;
1004
1005 l_att_num_changed BOOLEAN := FALSE;
1006 l_old_att_num NUMBER := -1;
1007 l_attribute_num NUMBER;
1008
1009 l_bg_id NUMBER;
1010 l_accrual_plan_id NUMBER;
1011 l_element_type_id NUMBER;
1012 l_plan_exists BOOLEAN;
1013 l_input_value_id NUMBER;
1014 l_iv_name VARCHAR2(240);
1015
1016 l_found BOOLEAN;
1017 L_ACCRUAL_PLAN_EXISTS VARCHAR2(3);
1018
1019 CURSOR c_assignments(p_resource_id In Number,
1020 p_evaluation_date In Date) IS
1021 SELECT pas.BUSINESS_GROUP_ID
1022 , pas.effective_start_date
1023 , pas.effective_end_date
1024 FROM PER_ALL_ASSIGNMENTS_F pas,
1025 per_assignment_status_types typ
1026 WHERE pas.PERSON_ID = p_resource_id
1027 AND pas.ASSIGNMENT_TYPE in ('E','C')
1028 AND pas.PRIMARY_FLAG = 'Y'
1029 AND pas.ASSIGNMENT_STATUS_TYPE_ID = typ.ASSIGNMENT_STATUS_TYPE_ID
1030 AND typ.PER_SYSTEM_STATUS IN ( 'ACTIVE_ASSIGN','ACTIVE_CWK')
1031 AND p_evaluation_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
1032
1033 cursor c_check_pto (p_bg_id NUMBER,p_element_type_id NUMBER,p_effective_date IN DATE)
1034 IS
1035 SELECT pnt.DATE_INPUT_VALUE_id DATE_INPUT_VALUE_id,
1036 pnt.accrual_plan_id accrual_plan_id
1037 FROM PAY_NET_CALCULATION_RULES pnt,PAY_INPUT_VALUES_F piv
1038 WHERE piv.business_group_id=p_bg_id
1039 and pnt.DATE_INPUT_VALUE_id=piv.INPUT_VALUE_ID
1040 and piv.element_type_ID=p_element_type_id
1041 AND p_effective_date between piv.effective_start_date and piv.effective_end_date
1042 and piv.business_group_id=pnt.business_group_id;
1043
1044 FUNCTION get_attribute_from_iv
1045 ( p_element_type_id NUMBER,
1046 p_effective_date DATE,
1047 p_iv_id NUMBER
1048 ) RETURN VARCHAR2 IS
1049
1050 cursor c_ipvs(p_element_type_id in number, p_effective_date in date) is
1051 select display_sequence, name, input_value_id, mandatory_flag
1052 from pay_input_values_f
1053 where element_type_id = p_element_type_id
1054 and p_effective_date between effective_start_date and effective_end_date
1055 order by display_sequence, name;
1056
1057 CURSOR c_get_atttribute_num( p_element_type_id NUMBER,p_iv_seq VARCHAR2)
1058 is
1059 select 'Y'
1060 from fnd_descr_flex_column_usages c, hxc_mapping_components mpc
1061 where c.application_id = 809
1062 and c.descriptive_flexfield_name = 'OTC Information Types'
1063 and c.descriptive_flex_context_code = 'ELEMENT - '||p_element_type_id
1064 and c.application_column_name = mpc.segment
1065 and mpc.field_name = 'InputValue'||p_iv_seq;
1066
1067 l_segment_count NUMBER:=0;
1068 l_segment_choice NUMBER:=0;
1069
1070 flex_entry_found VARCHAR2(3);
1071
1072 BEGIN
1073
1074 l_segment_count := 0;
1075
1076 FOR ipv_rec in c_ipvs(p_element_type_id, p_effective_date) LOOP
1077 l_segment_count := l_segment_count +1;
1078 if (ipv_rec.mandatory_flag <> 'X') then
1079 if((ipv_rec.display_sequence > 11) AND (ipv_rec.display_sequence < 16)) then
1080 l_segment_choice := ipv_rec.display_sequence;
1081 else
1082 l_segment_choice := l_segment_count;
1083 end if;
1084
1085 IF ipv_rec.input_value_id=p_iv_id THEN
1086
1087 -- confirm that the entry for this Input Vaue exists ion OTL Information DFF.
1088 --Andy We can remove this if you thik it is not required.
1089
1090 flex_entry_found :=NULL;
1091 OPEN c_get_atttribute_num(p_element_type_id,l_segment_choice);
1092 FETCH c_get_atttribute_num into flex_entry_found;
1093 CLOSE c_get_atttribute_num;
1094
1095 IF flex_entry_found ='Y' then
1096 return l_segment_choice;
1097 END IF;
1098
1099 END IF;
1100 END IF; -- is this a user enterable segment
1101 end LOOP; -- Input value loop
1102 return null;
1103 END;
1104
1105 FUNCTION get_assignment_info ( p_resource_id NUMBER
1106 , p_evaluation_date DATE ) RETURN NUMBER IS
1107
1108 BEGIN
1109
1110 hr_utility.trace('In get ass info : '||to_char(p_resource_id)||' : '||to_char(p_evaluation_date));
1111
1112 IF ( g_pto_assignment_info.EXISTS ( p_resource_id ) )
1113 THEN
1114
1115 IF ( ( p_evaluation_date <= g_pto_assignment_info( p_resource_id ).effective_end_date )
1116 AND ( p_evaluation_date >= g_pto_assignment_info( p_resource_id ).effective_start_date ) )
1117 THEN
1118
1119 hr_utility.trace('Using Cache : bg id is '||to_char(g_pto_assignment_info(p_Resource_id).bg_id));
1120
1121 RETURN g_pto_assignment_info(p_Resource_id).bg_id;
1122
1123 ELSE
1124
1125 hr_utility.trace('date changed - clearing cahce');
1126
1127 -- new assignment record bg id could potentially change invalidating
1128 -- assignment cache and pto element cache
1129
1130 g_pto_assignment_info.DELETE;
1131 g_pto_element.DELETE;
1132
1133 END IF;
1134
1135 END IF;
1136
1137 hr_utility.trace('not using cache');
1138
1139 open c_assignments(p_resource_id,p_evaluation_date);
1140 fetch c_assignments into g_pto_assignment_info(p_Resource_id).bg_id
1141 , g_pto_assignment_info(p_resource_id).effective_start_date
1142 , g_pto_assignment_info(p_resource_id).effective_end_date;
1143 close c_assignments;
1144 /*
1145 hr_utility.trace('cache is ');
1146 hr_utility.trace('bg id is '||to_char(g_pto_assignment_info(p_Resource_id).bg_id));
1147 hr_utility.trace('start date is '||to_char(g_pto_assignment_info(p_Resource_id).effective_start_Date));
1148 hr_utility.trace('end date is '||to_char(g_pto_assignment_info(p_Resource_id).effective_end_Date));
1149 */
1150
1151 RETURN g_pto_assignment_info(p_Resource_id).bg_id;
1152
1153
1154 EXCEPTION
1155 WHEN OTHERS THEN
1156
1157 return null;
1158
1159 END get_assignment_info;
1160
1161
1162 PROCEDURE create_pto_iv_row ( p_attributes in out NOCOPY hxc_self_service_time_deposit.app_attributes_info
1163 ,p_current_att_index NUMBER
1164 ,p_att_num NUMBER
1165 ,p_iv_id NUMBER ) IS
1166
1167 l_next_ind PLS_INTEGER := p_attributes.LAST+1;
1168 l_new_iv_row hxc_self_service_time_deposit.app_attributes;
1169
1170 BEGIN
1171
1172 hr_utility.trace('In create pto iv row');
1173 hr_utility.trace('next ind is '||to_char(l_next_ind));
1174
1175 -- use current Dummy Element Context row as starting point
1176
1177 l_new_iv_row := p_attributes(p_current_att_index);
1178
1179 l_new_iv_row.attribute_name := 'InputValue'||p_att_num;
1180 l_new_iv_row.segment := 'ATTRIBUTE' ||p_att_num;
1181 l_new_iv_row.attribute_value := p_canonical_date;
1182 l_new_iv_row.updated := 'N';
1183 l_new_iv_row.changed := 'N';
1184
1185 p_attributes(l_next_ind) := l_new_iv_row;
1186
1187 pay_hxc_deposit_interface.g_canonical_iv_id_tab(p_iv_id) := 'Y';
1188
1189 hr_utility.trace('att name is '||l_new_iv_row.attribute_name);
1190 hr_utility.trace('att value is '||l_new_iv_row.attribute_value);
1191 hr_utility.trace('iv id for global is '||to_char(p_iv_id));
1192
1193
1194 END create_pto_iv_row;
1195
1196 Begin -- set_pto_element_date
1197
1198 --get the assignment_id
1199
1200 l_bg_id := get_assignment_info ( p_resource_id, p_effective_date );
1201
1202 --Now loop through the attributes table and get the element type id from
1203 --ELEMENT -<element type id> record
1204
1205 l_index := p_attributes.first;
1206
1207 l_plan_exists :=FALSE;
1208 l_found :=FALSE;
1209
1210 LOOP
1211 EXIT WHEN NOT p_attributes.exists(l_index);
1212
1213 IF p_attributes(l_index).attribute_name ='Dummy Element Context' and
1214 substr(p_attributes(l_index).attribute_value, 1, 10) = 'ELEMENT - ' and --Bug 4560586
1215 p_attributes(l_index).building_block_id =p_tbb_id
1216 THEN
1217
1218 l_element_type_id := to_number(replace(p_attributes(l_index).attribute_value,'ELEMENT - '));
1219
1220 hr_utility.trace('l_element_type_id'||l_element_type_id);
1221
1222 IF ( g_pto_element.EXISTS( l_element_type_id ) )
1223 THEN
1224
1225 hr_utility.trace('Element exists in cache');
1226
1227 IF ( g_pto_element(l_element_type_id).is_pto = 'Y' )
1228 THEN
1229
1230 hr_utility.trace('Element is PTO');
1231
1232 l_attribute_num := g_pto_element(l_element_type_id).att_num;
1233 l_input_value_id := g_pto_element(l_element_type_id).iv_id;
1234
1235 create_pto_iv_row ( p_attributes, l_index, l_attribute_num,
1236 l_input_value_id );
1237
1238 END IF;
1239
1240 ELSE
1241
1242 hr_utility.trace('Element not in Cache');
1243
1244 l_input_value_id :=NULL;
1245 l_accrual_plan_id :=NULL;
1246
1247 -- get all the accrual plan which have this element in net calculation rule
1248
1249 -- ( pretty sure we do not need to loop here since we should never have
1250 -- the same element on different accrual plans with more than one
1251 -- date iv. If we do then we will simply not maintain the cache )
1252
1253 g_pto_element( l_element_type_id ).is_pto := 'N';
1254
1255 OPEN c_check_pto(l_bg_id,l_element_type_id,p_effective_date);
1256 LOOP
1257 FETCH c_check_pto into l_input_value_id,l_accrual_plan_id;
1258 EXIT WHEN c_check_pto%NOTFOUND;
1259
1260 hr_utility.trace('ELement is a PTO element');
1261
1262 g_pto_element( l_element_type_id ).is_pto := 'Y';
1263
1264 hr_utility.trace('l_input_value_id'||l_input_value_id);
1265
1266 -- if accrual plan is valid ..then set the value in correct attribute
1267
1268 l_attribute_num :=get_attribute_from_iv
1269 (p_element_type_id => l_element_type_id,
1270 p_effective_date => p_effective_date,
1271 p_iv_id => l_input_value_id);
1272
1273 IF ( ( l_old_att_num <> l_attribute_num )
1274 AND ( l_old_att_num <> -1 ) )
1275 THEN
1276 l_att_num_changed := TRUE;
1277 ELSE
1278 l_old_att_num := l_attribute_num;
1279 END IF;
1280
1281
1282 hr_utility.trace('attribute num is '||l_attribute_num);
1283 hr_utility.trace('p bb id is '||to_char(p_tbb_id));
1284
1285 create_pto_iv_row ( p_attributes, l_index, l_attribute_num,
1286 l_input_value_id );
1287
1288
1289 END LOOP;
1290 CLOSE c_check_pto;
1291
1292 IF ( NOT l_att_num_changed AND g_pto_element( l_element_type_id ).is_pto = 'Y')
1293 THEN
1294
1295 -- maintain cache
1296
1297 g_pto_element(l_element_type_id).is_pto := 'Y';
1298 g_pto_element(l_element_type_id).att_num := l_attribute_num;
1299 g_pto_element(l_element_type_id).iv_id := l_input_value_id;
1300
1301 ELSIF ( l_att_num_changed AND g_pto_element( l_element_type_id ).is_pto = 'Y' )
1302 THEN
1303
1304 g_pto_element.DELETE( l_element_type_id );
1305
1306
1307 END IF; -- NOT ll_att_num_changed
1308
1309 END IF; -- IF ( g_pto_element.EXISTS( l_element_type_id ) )
1310
1311 END IF; -- Dummy Element Context
1312
1313 l_index := p_attributes.next(l_index);
1314
1315 END LOOP;
1316
1317 End set_pto_element_date;
1318
1319
1320
1321 BEGIN --pay_update_timecard
1322
1323 l_index :=p_blocks.first;
1324 LOOP EXIT WHEN NOT p_blocks.exists(l_index);
1325
1326 -- NOTE: This only loops round non deleted blocks
1327 -- If at a later date more than setting the PTO date is done
1328 -- from within this loop the logic may have to change.
1329 -- Meanwhile, there is no need to set the PTO date for deleted
1330 -- entries since the date does not change.
1331
1332 IF ( ( p_blocks(l_index).SCOPE='DETAIL' ) AND
1333 ( p_blocks(l_index).date_to = hr_general.end_of_time ) )
1334 THEN
1335
1336 l_start_time := TRUNC(p_blocks(l_index).start_time);
1337 l_canonical_date := fnd_date.date_to_canonical(l_start_time);
1338 l_parent_bb_id := p_blocks(l_index).PARENT_BUILDING_BLOCK_ID;
1339 l_resource_id := p_blocks(l_index).resource_id;
1340
1341 IF p_blocks(l_index).TYPE = 'MEASURE' THEN
1342 l_day := p_blocks.first;
1343 LOOP EXIT WHEN (NOT p_blocks.exists(l_day));
1344 IF (p_blocks(l_day).TIME_BUILDING_BLOCK_ID = l_parent_bb_id) AND
1345 (p_blocks(l_day).SCOPE = 'DAY') THEN
1346 --
1347 l_start_time := p_blocks(l_day).start_time;
1348 l_canonical_date := fnd_date.date_to_canonical(p_blocks(l_day).start_time);
1349
1350 EXIT;
1351 END IF;
1352 l_day := p_blocks.next(l_day);
1353 END LOOP;
1354 END IF;
1355
1356 l_effective_date :=trunc(l_start_time);
1357
1358 if(l_canonical_date is not null) then
1359 set_pto_element_date
1360 (p_attributes => p_attributes,
1361 p_tbb_id => p_blocks(l_index).time_building_block_id,
1362 p_resource_id => l_resource_id,
1363 p_effective_date => l_effective_date,
1364 p_canonical_date => l_canonical_date
1365 );
1366 end if;
1367
1368 END IF;
1369
1370 l_index:=p_blocks.next(l_index);
1371
1372 END LOOP;
1373
1374 END pay_update_timecard;
1375 --
1376 --------------------------- pay_validate_process ----------------------------
1377 --
1378 PROCEDURE pay_validate_process
1379 (p_operation IN VARCHAR2) IS
1380
1381 l_blocks hxc_self_service_time_deposit.timecard_info;
1382 l_attributes hxc_self_service_time_deposit.app_attributes_info;
1383 l_messages hxc_self_service_time_deposit.message_table;
1384 l_proc VARCHAR2(100) := 'pay_hxc_deposit_interface.PAY_VALIDATE_PROCESS';
1385
1386 BEGIN
1387 --
1388 hr_utility.set_location(l_proc, 10);
1389 --
1390 hr_utility.set_location(l_proc, 20);
1391 --
1392 hxc_self_service_time_deposit.get_app_hook_params(
1393 p_building_blocks => l_blocks,
1394 p_app_attributes => l_attributes,
1395 p_messages => l_messages);
1396 --
1397 hr_utility.set_location(l_proc, 40);
1398 --
1399 pay_validate_timecard
1400 (p_operation => p_operation
1401 ,p_time_building_blocks => l_blocks
1402 ,p_time_attributes => l_attributes
1403 ,p_messages => l_messages
1404 );
1405
1406 hxc_self_service_time_deposit.set_app_hook_params(
1407 p_building_blocks => l_blocks,
1408 p_app_attributes => l_attributes,
1409 p_messages => l_messages);
1410
1411 END pay_validate_process;
1412 --
1413 ------------------------- pay_validate_timecard ------------------------------
1414 --
1415 PROCEDURE pay_validate_timecard
1416 (p_operation IN VARCHAR2
1417 ,p_time_building_blocks IN OUT NOCOPY hxc_self_service_time_deposit.timecard_info
1418 ,p_time_attributes IN OUT NOCOPY hxc_self_service_time_deposit.app_attributes_info
1419 ,p_messages IN OUT NOCOPY hxc_self_service_time_deposit.message_table) IS
1420 --
1421 cursor get_debug is
1422 SELECT 'X'
1423 FROM hxc_debug
1424 WHERE process = 'pay_validate_timecard'
1425 AND trunc(debug_date) <= sysdate;
1426
1427 --payroll perf fixes
1428
1429 l_full_name VARCHAR2(240);
1430 lcode HR_LOOKUPS.lookup_code%TYPE;
1431
1432 --local tables for caching
1433
1434 --
1435 -- local tables
1436 --
1437 l_field_name t_field_name;
1438 l_value t_value;
1439 l_segment t_segment;
1440 l_input_value t_input_value;
1441 l_attribute_ids t_attribute;
1442 l_time_attribute_id NUMBER(15);
1443 --
1444 l_bb_id NUMBER(15);
1445 l_bb_ovn NUMBER(15);
1446 l_type VARCHAR2(30);
1447
1448 --Changed by smummini for bug # 2791955
1449 --l_measure NUMBER(15);
1450 l_measure hxc_time_building_blocks.measure%TYPE;
1451
1452 l_start_time DATE;
1453 l_stop_time DATE;
1454 l_parent_bb_id NUMBER(15);
1455 l_scope VARCHAR2(30);
1456 l_resource_id NUMBER(15);
1457 l_resource_type VARCHAR2(30);
1458 l_comment_text VARCHAR2(2000);
1459 l_date_to DATE;
1460 --
1461 l_person_id NUMBER(9);
1462 l_effective_date DATE;
1463 l_assignment_id NUMBER(9);
1464 l_business_group_id NUMBER(9);
1465 l_element_type_id NUMBER(9);
1466 l_element_link_id NUMBER(9);
1467 l_element_name VARCHAR2(80);
1468 l_cost_allocation_structure VARCHAR2(150);
1469 l_cost_allocation_keyflex_id NUMBER(9);
1470 --
1471 l_effective_start_date DATE;
1472 l_effective_end_date DATE;
1473 l_element_entry_id NUMBER(9);
1474 l_object_version_number NUMBER(9);
1475 l_create_warning BOOLEAN;
1476 l_cnt NUMBER(15);
1477 l_day NUMBER(15);
1478 l_cnt_att NUMBER(15);
1479 --
1480 l_cost VARCHAR2(1) := 'N';
1481 l_valid VARCHAR2(1) := 'N';
1482 l_att NUMBER := 0;
1483 e_error exception;
1484 -- e_continue exception;
1485 l_debug VARCHAR2(1);
1486 --
1487 l_proc VARCHAR2(100):= 'pay_hxc_deposit_interface.PAY_VALIDATE_TIMECARD';
1488
1489 l_name_cached BOOLEAN;
1490 l_link_cached BOOLEAN;
1491 l_iter BINARY_INTEGER;
1492
1493 l_index_gaz pls_integer;
1494
1495 --
1496 -- MAIN --begin pay_validate_timecard
1497 --
1498 BEGIN
1499 --
1500 /*
1501
1502 Commented out for enabling validation on save
1503 preference - bug 3480070, no longer need
1504 to issue this return in this case, since
1505 we can remove the validation by setting
1506 the preference to no.
1507
1508 IF p_operation <> 'SUBMIT' THEN
1509 return;
1510 END IF;
1511
1512 */
1513 --
1514 open get_debug;
1515 fetch get_debug into l_debug;
1516 IF get_debug%FOUND THEN
1517 hr_utility.trace_on(null, 'PAYVAL');
1518 END IF;
1519 close get_debug;
1520
1521 --get user language
1522 user_language := userenv('LANG');
1523
1524
1525 --
1526 -- Loop through all the building blocks and validate the details.
1527 --
1528 l_cnt := p_time_building_blocks.first;
1529 LOOP
1530 EXIT WHEN
1531 (NOT p_time_building_blocks.exists(l_cnt));
1532
1533 --
1534 hr_utility.set_location(l_proc, 10);
1535 hr_utility.trace('*********** NEW TIME BUILDING BLOCK ************');
1536 --
1537 l_bb_id := p_time_building_blocks(l_cnt).TIME_BUILDING_BLOCK_ID;
1538 l_bb_ovn := p_time_building_blocks(l_cnt).OBJECT_VERSION_NUMBER;
1539 l_type := p_time_building_blocks(l_cnt).TYPE;
1540 l_measure := p_time_building_blocks(l_cnt).MEASURE;
1541 l_start_time := p_time_building_blocks(l_cnt).START_TIME;
1542 l_stop_time := p_time_building_blocks(l_cnt).STOP_TIME;
1543 l_parent_bb_id := p_time_building_blocks(l_cnt).PARENT_BUILDING_BLOCK_ID;
1544 l_scope := p_time_building_blocks(l_cnt).SCOPE;
1545 l_resource_id := p_time_building_blocks(l_cnt).RESOURCE_ID;
1546 l_resource_type := p_time_building_blocks(l_cnt).RESOURCE_TYPE;
1547 l_comment_text := p_time_building_blocks(l_cnt).COMMENT_TEXT;
1548 l_date_to := p_time_building_blocks(l_cnt).DATE_TO;
1549 --
1550 hr_utility.trace('Time BB ID is : ' || to_char(l_bb_id));
1551 hr_utility.trace('Type is : ' || l_type);
1552 hr_utility.trace('Measure is : ' || to_char(l_measure));
1553 hr_utility.trace('l_start_time is ' ||
1554 to_char(l_start_time, 'DD-MON-YYYY HH:MI:SS'));
1555 hr_utility.trace('l_stop_time is ' ||
1556 to_char(l_stop_time, 'DD-MON-YYYY HH:MI:SS'));
1557 hr_utility.trace('l_scope is '||l_scope);
1558 hr_utility.trace('l_resource_id is '||to_char(l_resource_id));
1559 hr_utility.trace('l_resource_type is '||l_resource_type);
1560 --
1561 hr_utility.trace('UOM is : ' ||
1562 p_time_building_blocks(l_cnt).UNIT_OF_MEASURE);
1563 hr_utility.trace('Parent BB ID is : ' ||
1564 to_char(p_time_building_blocks(l_cnt).PARENT_BUILDING_BLOCK_ID));
1565 hr_utility.trace('PARENT_IS_NEW is : ' ||
1566 p_time_building_blocks(l_cnt).PARENT_IS_NEW);
1567 -- hr_utility.trace('OVN is : ' ||
1568 -- to_char(p_time_building_blocks(l_cnt).OBJECT_VERSION_NUMBER));
1569 hr_utility.trace('APPROVAL_STATUS is : ' ||
1570 p_time_building_blocks(l_cnt).APPROVAL_STATUS);
1571 -- hr_utility.trace('APPROVAL_STYLE_ID is : ' ||
1572 -- to_char(p_time_building_blocks(l_cnt).APPROVAL_STYLE_ID));
1573 -- hr_utility.trace('DATE_FROM is : ' ||
1574 -- to_char(p_time_building_blocks(l_cnt).DATE_FROM, 'DD-MON-YYYY'));
1575 -- hr_utility.trace('DATE_TO is : ' ||
1576 -- to_char(p_time_building_blocks(l_cnt).DATE_TO, 'DD-MON-YYYY'));
1577 hr_utility.trace('COMMENT_TEXT is : ' ||
1578 p_time_building_blocks(l_cnt).COMMENT_TEXT);
1579 -- hr_utility.trace('Parent OVN is : ' ||
1580 -- to_char(p_time_building_blocks(l_cnt).PARENT_BUILDING_BLOCK_OVN));
1581 hr_utility.trace('NEW is : ' || p_time_building_blocks(l_cnt).NEW);
1582 --
1583 hr_utility.set_location(l_proc, 20);
1584 --
1585
1586 IF ( (l_type = 'MEASURE' AND l_measure IS NOT NULL) OR
1587 (l_type = 'RANGE' AND l_start_time IS NOT NULL AND
1588 l_stop_time IS NOT NULL)) AND
1589 (l_date_to = hr_general.end_of_time) THEN
1590 l_valid := 'Y';
1591 ELSE
1592 l_valid := 'N';
1593 END IF;
1594
1595 --
1596 -- Only care about valid DETAIL Blocks.
1597 --
1598 IF l_scope = 'DETAIL' AND l_valid = 'Y' THEN
1599 --
1600 -- Get the start and stop times from the parent DAY block if DETAIL is
1601 -- a measure.
1602 --
1603 IF l_type = 'MEASURE' THEN
1604 --
1605 l_day := p_time_building_blocks.first;
1606 LOOP
1607 EXIT WHEN
1608 (NOT p_time_building_blocks.exists(l_day));
1609 --
1610 hr_utility.set_location(l_proc, 30);
1611 --
1612 IF (p_time_building_blocks(l_day).TIME_BUILDING_BLOCK_ID =
1613 l_parent_bb_id) AND
1614 (p_time_building_blocks(l_day).SCOPE = 'DAY') THEN
1615 --
1616 l_start_time := p_time_building_blocks(l_day).START_TIME;
1617 l_stop_time := p_time_building_blocks(l_day).STOP_TIME;
1618 --
1619 hr_utility.trace('l_start_time is ' ||
1620 to_char(l_start_time, 'DD-MON-YYYY HH:MI:SS'));
1621 hr_utility.trace('l_stop_time is ' ||
1622 to_char(l_stop_time, 'DD-MON-YYYY HH:MI:SS'));
1623 --
1624 EXIT;
1625 --
1626 END IF;
1627 l_day := p_time_building_blocks.next(l_day);
1628
1629 END LOOP;
1630 END IF;
1631 --
1632
1633 BEGIN
1634
1635
1636 l_person_id := NULL;
1637 --
1638 IF l_resource_type = 'PERSON' THEN
1639 --
1640 l_person_id := l_resource_id;
1641 --
1642 hr_utility.trace('l_person_id is '||to_char(l_person_id));
1643 --
1644 END IF;
1645 --
1646 l_effective_date := trunc(l_start_time);
1647 hr_utility.trace('l_effective_date is :'||
1648 to_char(l_effective_date, 'DD-MON-YYYY'));
1649 --
1650 get_full_name( p_person_id => l_person_id,
1651 p_effective_date => l_effective_date,
1652 p_bb_id => l_bb_id,
1653 p_bb_ovn => l_bb_ovn,
1654 p_messages => p_messages,
1655 p_full_name => l_full_name);
1656
1657 --
1658 -- Get Assignment ID and Business Group ID.
1659 --
1660 l_business_group_id := NULL;
1661 l_assignment_id := NULL;
1662 --
1663
1664 get_assignment(p_person_id =>l_person_id,
1665 p_effective_date =>l_effective_date,
1666 p_full_name =>l_full_name,
1667 p_bb_id =>l_bb_id,
1668 p_bb_ovn =>l_bb_ovn,
1669 p_messages =>p_messages,
1670 p_assignment_id => l_assignment_id,
1671 p_business_group_id =>l_business_group_id,
1672 p_cost_allocation_structure =>l_cost_allocation_structure);
1673
1674 --
1675 l_field_name.delete;
1676 l_value.delete;
1677 l_attribute_ids.delete;
1678 --
1679 hr_utility.set_location(l_proc, 50);
1680 --
1681 -- Get the attributes for this detail building block.
1682 --
1683 IF p_time_attributes.count <> 0 THEN
1684 --
1685 l_att := 0;
1686 --
1687 l_cnt_att := p_time_attributes.first;
1688 LOOP
1689 EXIT WHEN
1690 (NOT p_time_attributes.exists(l_cnt_att));
1691
1692 -- FOR l_cnt_att in p_time_attributes.first .. p_time_attributes.last LOOP
1693 --
1694 IF l_bb_id = p_time_attributes(l_cnt_att).BUILDING_BLOCK_ID THEN
1695 --
1696 hr_utility.trace('------ In Attribute Loop ------');
1697 --
1698 l_attribute_ids(l_att) :=
1699 p_time_attributes(l_cnt_att).time_attribute_id;
1700 --
1701 l_field_name(l_att) :=
1702 p_time_attributes(l_cnt_att).attribute_name;
1703 --
1704 --hr_utility.trace('l_field_name(l_att) is '||l_field_name(l_att));
1705 --
1706 l_value(l_att) := p_time_attributes(l_cnt_att).attribute_value;
1707 --
1708 --hr_utility.trace('l_value(l_att) is '||l_value(l_att));
1709 --
1710 l_att := l_att + 1;
1711 --
1712 END IF;
1713 --
1714 l_cnt_att := p_time_attributes.next(l_cnt_att);
1715
1716 END LOOP;
1717 --
1718 ELSE
1719 --
1720 hr_utility.set_location(l_proc, 100);
1721 --
1722 hxc_time_entry_rules_utils_pkg.add_error_to_table(
1723 p_message_table => p_messages
1724 ,p_message_name => 'HXC_DEP_VAL_NO_ATTR'
1725 ,p_message_token => NULL
1726 ,p_message_level => 'ERROR'
1727 ,p_message_field => NULL
1728 ,p_application_short_name => 'HXC'
1729 ,p_timecard_bb_id => l_bb_id
1730 ,p_time_attribute_id => NULL
1731 ,p_timecard_bb_ovn => l_bb_ovn
1732 ,p_time_attribute_ovn => NULL);
1733 --
1734 raise e_continue; --Bug#3004714
1735
1736 -- Start 2887210, i.e. Comment the raise error
1737 -- Instead let the errors consolidate in the message table.
1738 -- raise e_error;
1739 -- End 2887210
1740 --
1741 END IF; -- p_time_attributes.count is greater than 0
1742 --
1743 hr_utility.trace('l_att is ' || to_char(l_att));
1744 --
1745 IF l_att > 0 THEN
1746 --
1747 hr_utility.set_location(l_proc, 52);
1748 --
1749 -- Get the Element Type ID
1750 --
1751 l_element_type_id := NULL;
1752 l_time_attribute_id := NULL;
1753 --
1754 IF l_field_name.count <> 0 THEN
1755 FOR fld_cnt in l_field_name.first .. l_field_name.last LOOP
1756 IF upper(l_field_name(fld_cnt)) = 'DUMMY ELEMENT CONTEXT' THEN
1757 BEGIN
1758 l_element_type_id := to_number(replace(
1759 upper(l_value(fld_cnt)), 'ELEMENT - '));
1760 l_time_attribute_id := l_attribute_ids(fld_cnt);
1761 EXCEPTION
1762 WHEN OTHERS THEN
1763 hxc_time_entry_rules_utils_pkg.add_error_to_table(
1764 p_message_table => p_messages
1765 ,p_message_name => 'HXC_DEP_VAL_NO_ATTR'
1766 ,p_message_token => NULL
1767 ,p_message_level => 'ERROR'
1768 ,p_message_field => NULL
1769 ,p_application_short_name => 'HXC'
1770 ,p_timecard_bb_id => l_bb_id
1771 ,p_time_attribute_id => NULL
1772 ,p_timecard_bb_ovn => l_bb_ovn
1773 ,p_time_attribute_ovn => NULL);
1774 --
1775 raise e_continue;
1776 END;
1777 EXIT;
1778 END IF;
1779 END LOOP;
1780 END IF;
1781 --
1782 hr_utility.trace('l_element_type_id is ' ||
1783 to_char(l_element_type_id));
1784 --
1785 hr_utility.set_location(l_proc, 53);
1786 --
1787 IF l_element_type_id IS NOT NULL THEN
1788 --
1789 get_element_name(l_element_type_id,
1790 l_effective_date ,
1791 l_time_attribute_id,
1792 l_bb_id,
1793 l_bb_ovn,
1794 p_messages,
1795 l_element_name );
1796
1797
1798 ELSE
1799 --
1800 hxc_time_entry_rules_utils_pkg.add_error_to_table(
1801 p_message_table => p_messages
1802 ,p_message_name => 'HXC_DEP_VAL_NO_ATTR'
1803 ,p_message_token => NULL
1804 ,p_message_level => 'ERROR'
1805 ,p_message_field => NULL
1806 ,p_application_short_name => 'HXC'
1807 ,p_timecard_bb_id => l_bb_id
1808 ,p_time_attribute_id => NULL
1809 ,p_timecard_bb_ovn => l_bb_ovn
1810 ,p_time_attribute_ovn => NULL);
1811 --
1812 raise e_continue;
1813
1814 --
1815 END IF;
1816 --
1817 hr_utility.trace('l_element_name is ' || l_element_name);
1818 --
1819 hr_utility.set_location(l_proc, 55);
1820 --
1821 l_input_value.delete;
1822 l_segment.delete;
1823 --
1824 get_input_values(p_element_name => l_element_name,
1825 p_element_type_id => l_element_type_id,
1826 p_type => l_type,
1827 p_measure => l_measure,
1828 p_start_time => l_start_time,
1829 p_stop_time => l_stop_time,
1830 p_effective_date => l_effective_date,
1831 p_bb_id => l_bb_id,
1832 p_bb_ovn => l_bb_ovn,
1833 p_time_attribute_id => l_time_attribute_id,
1834 p_messages => p_messages,
1835 p_input_value => l_input_value,
1836 p_field_name => l_field_name,
1837 p_value => l_value,
1838 p_segment => l_segment);
1839
1840
1841 hr_utility.set_location(l_proc, 60);
1842 --
1843 -- Get Element Link ID.
1844 --
1845
1846 -- we can cache this
1847 -- check if we have already retrieved this record.
1848
1849 get_link(p_assignment_id => l_assignment_id,
1850 p_element_type_id => l_element_type_id,
1851 p_effective_date => l_effective_date,
1852 p_element_link_id => l_element_link_id);
1853
1854 --
1855 hr_utility.trace('l_element_link_id is ' ||
1856 to_char(l_element_link_id));
1857 --
1858 hr_utility.set_location(l_proc, 65);
1859 --
1860 --
1861 hr_utility.set_location(l_proc, 70);
1862 --
1863 savepoint val_timecard;
1864 --
1865 l_cost := 'N';
1866 --
1867 FOR i in 1 .. 30 LOOP
1868 IF l_segment(i) IS NOT NULL THEN
1869 l_cost := 'Y';
1870 EXIT;
1871 END IF;
1872 END LOOP;
1873 --
1874 IF l_cost = 'Y' THEN
1875 BEGIN
1876 l_cost_allocation_keyflex_id := hr_entry.maintain_cost_keyflex(
1877 p_cost_keyflex_structure => l_cost_allocation_structure,
1878 p_cost_allocation_keyflex_id => -1,
1879 p_concatenated_segments => NULL,
1880 p_summary_flag => 'N',
1881 p_start_date_active => NULL,
1882 p_end_date_active => NULL,
1883 p_segment1 => l_segment(1),
1884 p_segment2 => l_segment(2),
1885 p_segment3 => l_segment(3),
1886 p_segment4 => l_segment(4),
1887 p_segment5 => l_segment(5),
1888 p_segment6 => l_segment(6),
1889 p_segment7 => l_segment(7),
1890 p_segment8 => l_segment(8),
1891 p_segment9 => l_segment(9),
1892 p_segment10 => l_segment(10),
1893 p_segment11 => l_segment(11),
1894 p_segment12 => l_segment(12),
1895 p_segment13 => l_segment(13),
1896 p_segment14 => l_segment(14),
1897 p_segment15 => l_segment(15),
1898 p_segment16 => l_segment(16),
1899 p_segment17 => l_segment(17),
1900 p_segment18 => l_segment(18),
1901 p_segment19 => l_segment(19),
1902 p_segment20 => l_segment(20),
1903 p_segment21 => l_segment(21),
1904 p_segment22 => l_segment(22),
1905 p_segment23 => l_segment(23),
1906 p_segment24 => l_segment(24),
1907 p_segment25 => l_segment(25),
1908 p_segment26 => l_segment(26),
1909 p_segment27 => l_segment(27),
1910 p_segment28 => l_segment(28),
1911 p_segment29 => l_segment(29),
1912 p_segment30 => l_segment(30));
1913 --
1914 hr_utility.set_location(l_proc, 80);
1915 --
1916 EXCEPTION
1917 WHEN OTHERS THEN
1918 --
1919 hr_message.provide_error;
1920 --
1921 hxc_time_entry_rules_utils_pkg.add_error_to_table(
1922 p_message_table => p_messages
1923 ,p_message_name => hr_message.last_message_name
1924 ,p_message_token => NULL
1925 ,p_message_level => 'ERROR'
1926 ,p_message_field => NULL
1927 ,p_application_short_name => hr_message.last_message_app
1928 ,p_timecard_bb_id => l_bb_id
1929 ,p_time_attribute_id => l_time_attribute_id
1930 ,p_timecard_bb_ovn => l_bb_ovn
1931 ,p_time_attribute_ovn => NULL);
1932 --
1933 -- p_messages(g_error_count).MESSAGE_NAME :=
1934 -- hr_message.last_message_name;
1935 -- p_messages(g_error_count).APPLICATION_SHORT_NAME :=
1936 -- hr_message.last_message_app;
1937 --
1938 --raise e_error; 115.27 Change
1939 raise e_continue;
1940 --
1941 END;
1942 --
1943 ELSE
1944 --
1945 l_cost_allocation_keyflex_id := NULL;
1946 --
1947 hr_utility.set_location(l_proc, 85);
1948 --
1949 END IF;
1950 --
1951 hr_utility.set_location(l_proc, 88);
1952
1953 hr_utility.trace('iv 2 is '||l_input_value(2).value);
1954 --
1955 BEGIN
1956 py_element_entry_api.create_element_entry
1957 (p_validate => true
1958 ,p_effective_date => l_effective_date
1959 ,p_business_group_id => l_business_group_id
1960 -- ,p_original_entry_id =>
1961 ,p_assignment_id => l_assignment_id
1962 ,p_element_link_id => l_element_link_id
1963 ,p_entry_type => 'E'
1964 ,p_cost_allocation_keyflex_id => l_cost_allocation_keyflex_id
1965 -- ,p_updating_action_id =>
1966 -- ,p_comment_id =>
1967 -- ,p_reason =>
1968 -- ,p_target_entry_id =>
1969 -- ,p_subpriority =>
1970 ,p_date_earned => l_effective_date
1971 -- ,p_personal_payment_method_id =>
1972 -- ,p_attribute_category =>
1973 -- ,p_attribute1 =>
1974 -- ,p_attribute2 =>
1975 -- ,p_attribute3 =>
1976 -- ,p_attribute4 =>
1977 -- ,p_attribute5 =>
1978 -- ,p_attribute6 =>
1979 -- ,p_attribute7 =>
1980 -- ,p_attribute8 =>
1981 -- ,p_attribute9 =>
1982 -- ,p_attribute10 =>
1983 -- ,p_attribute11 =>
1984 -- ,p_attribute12 =>
1985 -- ,p_attribute13 =>
1986 -- ,p_attribute14 =>
1987 -- ,p_attribute15 =>
1988 -- ,p_attribute16 =>
1989 -- ,p_attribute17 =>
1990 -- ,p_attribute18 =>
1991 -- ,p_attribute19 =>
1992 -- ,p_attribute20 =>
1993 ,p_input_value_id1 => l_input_value(1).id
1994 ,p_input_value_id2 => l_input_value(2).id
1995 ,p_input_value_id3 => l_input_value(3).id
1996 ,p_input_value_id4 => l_input_value(4).id
1997 ,p_input_value_id5 => l_input_value(5).id
1998 ,p_input_value_id6 => l_input_value(6).id
1999 ,p_input_value_id7 => l_input_value(7).id
2000 ,p_input_value_id8 => l_input_value(8).id
2001 ,p_input_value_id9 => l_input_value(9).id
2002 ,p_input_value_id10 => l_input_value(10).id
2003 ,p_input_value_id11 => l_input_value(11).id
2004 ,p_input_value_id12 => l_input_value(12).id
2005 ,p_input_value_id13 => l_input_value(13).id
2006 ,p_input_value_id14 => l_input_value(14).id
2007 ,p_input_value_id15 => l_input_value(15).id
2008 ,p_entry_value1 => l_input_value(1).value
2009 ,p_entry_value2 => l_input_value(2).value
2010 ,p_entry_value3 => l_input_value(3).value
2011 ,p_entry_value4 => l_input_value(4).value
2012 ,p_entry_value5 => l_input_value(5).value
2013 ,p_entry_value6 => l_input_value(6).value
2014 ,p_entry_value7 => l_input_value(7).value
2015 ,p_entry_value8 => l_input_value(8).value
2016 ,p_entry_value9 => l_input_value(9).value
2017 ,p_entry_value10 => l_input_value(10).value
2018 ,p_entry_value11 => l_input_value(11).value
2019 ,p_entry_value12 => l_input_value(12).value
2020 ,p_entry_value13 => l_input_value(13).value
2021 ,p_entry_value14 => l_input_value(14).value
2022 ,p_entry_value15 => l_input_value(15).value
2023 ,p_effective_start_date => l_effective_start_date
2024 ,p_effective_end_date => l_effective_end_date
2025 ,p_element_entry_id => l_element_entry_id
2026 ,p_object_version_number => l_object_version_number
2027 ,p_create_warning => l_create_warning
2028 );
2029 --
2030 hr_utility.set_location(l_proc, 90);
2031 --
2032 EXCEPTION
2033 WHEN OTHERS THEN
2034 --
2035 hr_message.provide_error;
2036 --
2037 IF hr_message.last_message_name = 'HR_ELE_ENTRY_FORMULA_HINT' THEN
2038 hxc_time_entry_rules_utils_pkg.add_error_to_table(
2039 p_message_table => p_messages
2040 ,p_message_name => hr_message.last_message_name
2041 ,p_message_token => 'FORMULA_TEXT&'||hr_message.get_message_text
2042 ,p_message_level => 'ERROR'
2043 ,p_message_field => NULL
2044 ,p_application_short_name => hr_message.last_message_app
2045 ,p_timecard_bb_id => l_bb_id
2046 ,p_time_attribute_id => l_time_attribute_id
2047 ,p_timecard_bb_ovn => l_bb_ovn
2048 ,p_time_attribute_ovn => NULL);
2049 ELSE
2050 hxc_time_entry_rules_utils_pkg.add_error_to_table(
2051 p_message_table => p_messages
2052 ,p_message_name => hr_message.last_message_name
2053 ,p_message_token => NULL
2054 ,p_message_level => 'ERROR'
2055 ,p_message_field => NULL
2056 ,p_application_short_name => hr_message.last_message_app
2057 ,p_timecard_bb_id => l_bb_id
2058 ,p_time_attribute_id => l_time_attribute_id
2059 ,p_timecard_bb_ovn => l_bb_ovn
2060 ,p_time_attribute_ovn => NULL);
2061
2062 END IF;
2063 --
2064 -- p_messages(g_error_count).MESSAGE_NAME :=
2065 -- hr_message.last_message_name;
2066 -- p_messages(g_error_count).APPLICATION_SHORT_NAME :=
2067 -- hr_message.last_message_app;
2068 --
2069 --raise e_error; 115.27 change
2070 raise e_continue;
2071 --
2072 END;
2073 --
2074 rollback to val_timecard;
2075 --
2076 ELSE
2077 --
2078 hr_utility.set_location(l_proc, 100);
2079 --
2080 hxc_time_entry_rules_utils_pkg.add_error_to_table(
2081 p_message_table => p_messages
2082 ,p_message_name => 'HXC_DEP_VAL_NO_ATTR'
2083 ,p_message_token => NULL
2084 ,p_message_level => 'ERROR'
2085 ,p_message_field => NULL
2086 ,p_application_short_name => 'HXC'
2087 ,p_timecard_bb_id => l_bb_id
2088 ,p_time_attribute_id => l_time_attribute_id
2089 ,p_timecard_bb_ovn => l_bb_ovn
2090 ,p_time_attribute_ovn => NULL);
2091 --
2092 raise e_continue; --Bug#3004714
2093
2094 -- Start 2887210, i.e. Comment the raise error
2095 -- Instead let the errors consolidate in the message table.
2096 -- raise e_error;
2097 -- End 2887210
2098 --
2099 END IF; -- l_att is greater than 0
2100 --
2101 EXCEPTION --Bug#3004714
2102 WHEN e_continue THEN
2103 null;
2104 END; --Bug#3004714
2105
2106
2107 END IF; -- l_valid = Y and l_scope = DETAIL
2108 --
2109 l_cnt := p_time_building_blocks.next(l_cnt);
2110
2111
2112 END LOOP;
2113 --
2114 hr_utility.set_location(l_proc, 110);
2115 --
2116 EXCEPTION
2117 WHEN e_error THEN
2118 RETURN;
2119 --
2120 -- hr_utility.trace_off;
2121 --
2122 END pay_validate_timecard;
2123 --
2124 --
2125 END pay_hxc_deposit_interface;