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