[Home] [Help]
PACKAGE BODY: APPS.HXC_TIMESTORE_DEPOSIT_UTIL
Source
1 PACKAGE BODY hxc_timestore_deposit_util AS
2 /* $Header: hxctsdputil.pkb 120.32.12020000.5 2012/11/08 10:29:38 asrajago ship $ */
3
4 -- Global package name
5 g_package CONSTANT VARCHAR2 (33)
6 := ' hxc_timestore_deposit_util.';
7 g_oit_migration CONSTANT VARCHAR2 (30) := 'OIT_MIGRATION';
8 g_debug BOOLEAN := hr_utility.debug_enabled;
9
10 FUNCTION perform_audit (
11 p_props IN hxc_timecard_prop_table_type,
12 p_blocks IN hxc_block_table_type
13 )
14 RETURN BOOLEAN
15 AS
16 l_cla_terg_id NUMBER := NULL;
17 l_audit BOOLEAN;
18 BEGIN
19 l_cla_terg_id :=
20 TO_NUMBER
21 (hxc_timecard_properties.find_property_value
22 (p_props,
23 'TsPerAuditRequirementsAuditRequirements',
24 NULL,
25 NULL,
26 p_start_date => fnd_date.canonical_to_date
27 (p_blocks
28 (hxc_timecard_block_utils.find_active_timecard_index
29 (p_blocks)
30 ).start_time
31 ),
32 p_stop_date => fnd_date.canonical_to_date
33 (p_blocks
34 (hxc_timecard_block_utils.find_active_timecard_index
35 (p_blocks)
36 ).stop_time
37 )
38 )
39 );
40
41 IF (l_cla_terg_id IS NOT NULL)
42 THEN
43 l_audit := TRUE;
44 ELSE
45 l_audit := FALSE;
46 END IF;
47
48 RETURN l_audit;
49 END perform_audit;
50
51 -----------------------------------------------------------------------------
52 -- Type: Function
53 -- Scope: Public
54 -- Name: get_retrieval_process_id
55 -- Returns: NUMBER
56 -- IN Parameters: p_retrieval_process_name -> Name of the retrieval process for
57 -- which you want to get the ID
58 --
59 -- Description: This function will return the Id of the retrieval process
60 -- passed in.
61 --
62 -----------------------------------------------------------------------------
63 FUNCTION get_retrieval_process_id (
64 p_retrieval_process_name IN hxc_retrieval_processes.NAME%TYPE
65 )
66 RETURN hxc_retrieval_processes.retrieval_process_id%TYPE
67 IS
68 l_proc VARCHAR2 (72);
69
70 CURSOR csr_retrieval_process_id (v_retrieval_process_name IN VARCHAR2)
71 IS
72 SELECT retrieval_process_id
73 FROM hxc_retrieval_processes
74 WHERE NAME = v_retrieval_process_name;
75
76 l_retrieval_process_id hxc_retrieval_processes.retrieval_process_id%TYPE;
77 BEGIN
78 g_debug := hr_utility.debug_enabled;
79
80 IF g_debug
81 THEN
82 l_proc := g_package || 'get_retrieval_process_id';
83 hr_utility.set_location ('Entering:' || l_proc, 10);
84 END IF;
85
86 OPEN csr_retrieval_process_id (p_retrieval_process_name);
87
88 FETCH csr_retrieval_process_id
89 INTO l_retrieval_process_id;
90
91 IF csr_retrieval_process_id%NOTFOUND
92 THEN
93 fnd_message.set_name ('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
94 fnd_message.set_token ('PROCEDURE', l_proc);
95 fnd_message.set_token ('STEP', '1');
96 fnd_msg_pub.ADD;
97 END IF;
98
99 CLOSE csr_retrieval_process_id;
100
101 IF g_debug
102 THEN
103 hr_utility.set_location ( ' returning retrieval process id = '
104 || l_retrieval_process_id,
105 20
106 );
107 hr_utility.set_location ('Leaving:' || l_proc, 30);
108 END IF;
109
110 RETURN l_retrieval_process_id;
111 END get_retrieval_process_id;
112
113 -----------------------------------------------------------------------------
114 -- Type: Function
115 -- Scope: Public
116 -- Name: approval_style_id
117 -- Returns: hxc_approval_styles.approval_style_id
118 -- IN Parameters: p_approval_style_name -> Name of the approval style for which
119 -- you want to find the ID
120 --
121 -- Description: Private Function that return the ID of the 'OTL Auto Approve'
122 -- approval.
123 --
124 -----------------------------------------------------------------------------
125 FUNCTION approval_style_id (
126 p_approval_style_name hxc_approval_styles.NAME%TYPE
127 )
128 RETURN hxc_approval_styles.approval_style_id%TYPE
129 IS
130 l_proc VARCHAR2 (72);
131 l_approval_style_id hxc_approval_styles.approval_style_id%TYPE;
132
133 CURSOR csr_approval_style_id (p_name hxc_approval_styles.NAME%TYPE)
134 IS
135 SELECT has.approval_style_id
136 FROM hxc_approval_styles has
137 WHERE has.NAME = p_name;
138 BEGIN
139 g_debug := hr_utility.debug_enabled;
140
141 IF g_debug
142 THEN
143 l_proc := g_package || 'approval_style_id';
144 hr_utility.set_location ('Entering:' || l_proc, 10);
145 END IF;
146
147 OPEN csr_approval_style_id (p_approval_style_name);
148
149 FETCH csr_approval_style_id
150 INTO l_approval_style_id;
151
152 CLOSE csr_approval_style_id;
153
154 IF g_debug
155 THEN
156 hr_utility.set_location ( ' returning approval_style_id = '
157 || l_approval_style_id,
158 20
159 );
160 hr_utility.set_location ('Leaving:' || l_proc, 30);
161 END IF;
162
163 RETURN l_approval_style_id;
164 END approval_style_id;
165
166 -----------------------------------------------------------------------------
167 -- Type: Procedure
168 -- Scope: Public
169 -- Name: begin_approval
170 -- IN Parameters: p_blocks
171 --
172 -- Description: Public Procedure that can be used to start the approval
173 -- process.
174 --
175 -----------------------------------------------------------------------------
176 PROCEDURE begin_approval (
177 p_timecard_id IN hxc_time_building_blocks.time_building_block_id%TYPE,
178 p_blocks IN hxc_block_table_type
179 )
180 IS
181 l_proc VARCHAR2 (72);
182 l_resubmit VARCHAR2 (10) := hxc_timecard.c_no;
183 l_item_key wf_items.item_key%TYPE := NULL;
184 l_messages hxc_message_table_type := hxc_message_table_type();
185 l_timecard_props hxc_timecard_prop_table_type := hxc_timecard_prop_table_type();
186 l_timecard_index number;
187 l_message fnd_new_messages.message_text%type;
188 TC_APPROVAL_EXCEPTION EXCEPTION;
189 BEGIN
190 g_debug := hr_utility.debug_enabled;
191
192 IF g_debug
193 THEN
194 l_proc := g_package || 'begin_approval';
195 hr_utility.set_location ('Entering:' || l_proc, 10);
196 END IF;
197
198 -- set savepoint
199 savepoint TC_APPROVAL_SAVEPOINT;
200
201 l_timecard_index := hxc_timecard_block_utils.find_active_timecard_index(p_blocks);
202
203 hxc_timecard_properties.get_preference_properties
204 (p_validate => hxc_timecard.c_no,
205 p_resource_id => p_blocks(l_timecard_index).resource_id,
206 p_timecard_start_time => hxc_timecard_block_utils.date_value(p_blocks(l_timecard_index).start_time),
207 p_timecard_stop_time => hxc_timecard_block_utils.date_value(p_blocks(l_timecard_index).stop_time),
208 p_for_timecard => false,
209 p_timecard_bb_id => p_timecard_id,
210 p_timecard_bb_ovn => p_blocks(l_timecard_index).object_version_number,
211 p_messages => l_messages,
212 p_property_table => l_timecard_props
213 );
214
215 hxc_timecard_message_helper.processerrors
216 (p_messages => l_messages);
217
218 -- Determine if this is a resubmitted timecard
219 l_timecard_index :=
220 hxc_timecard_block_utils.find_active_timecard_index (p_blocks);
221
222 IF (hxc_timecard_block_utils.date_value
223 (p_blocks (l_timecard_index).date_to
224 ) = hr_general.end_of_time
225 )
226 THEN
227 l_resubmit :=
228 hxc_timecard_approval.is_timecard_resubmitted
229 (p_blocks (l_timecard_index).time_building_block_id,
230 p_blocks (l_timecard_index).object_version_number,
231 p_blocks (l_timecard_index).resource_id,
232 fnd_date.canonical_to_date
233 (p_blocks (l_timecard_index).start_time
234 ),
235 fnd_date.canonical_to_date
236 (p_blocks (l_timecard_index).stop_time
237 )
238 );
239 ELSE
240 l_resubmit := hxc_timecard.c_delete;
241 END IF;
242
243 l_item_key :=
244 hxc_timecard_approval.begin_approval
245 (p_blocks => p_blocks,
246 p_item_type => 'HXCEMP',
247 p_process_name => 'HXC_APPROVAL',
248 p_resubmitted => l_resubmit,
249 p_timecard_props => l_timecard_props,
250 p_messages => l_messages
251 );
252
253 -- Absences start
254
255 IF (nvl(fnd_profile.value('HR_ABS_OTL_INTEGRATION'), 'N') = 'Y') THEN
256 hr_utility.trace('ABS:Checking status of BEGIN_APPROVAL');
257 hr_utility.trace('ABS:l_messages.COUNT = '||l_messages.COUNT);
258
259 if l_messages.COUNT > 0
260 then
261 hr_utility.trace('ABS:Error in POST_ABSENCES - Rollback changes');
262
263 l_message := fnd_message.get_string (
264 appin => l_messages(l_messages.last).application_short_name
265 , namein => l_messages(l_messages.last).message_name
266 );
267
268 --dbms_output.put_line('online retrieval error : '||substr(l_message,1,255));
269
270 rollback to TC_APPROVAL_SAVEPOINT;
271
272 hxc_timecard_message_helper.processerrors
273 (p_messages => l_messages);
274 end if;
275 END IF;
276
277 -- Absences end
278
279 hxc_timecard_message_helper.processerrors
280 (p_messages => l_messages);
281
282 hxc_timecard_summary_pkg.update_summary_row
283 (p_timecard_id => p_timecard_id,
284 p_approval_item_type => 'HXCEMP',
285 p_approval_process_name => 'HXC_APPROVAL',
286 p_approval_item_key => l_item_key
287 );
288
289 IF g_debug
290 THEN
291 hr_utility.set_location ('Leaving:' || l_proc, 100);
292 END IF;
293 END begin_approval;
294
295 -----------------------------------------------------------------------------
296 -- Type: Procedure
297 -- Scope: Public
298 -- Name: save_timecard
299 -- IN OUT Parameters: p_blocks -> The Timecard structure you want Save
300 -- p_attributes -> The Timecard's attributes you want to Save
301 -- p_messages -> The messages returned from the Save process
302 -- OUT Parameters: p_timecard_id -> The timecard_id of the saved Timecard
303 -- p_timecard_ovn -> the OVN of the saved Timecard
304 --
305 -- Description: Private Procedure that can will be used to Save the timecard
306 -- (as oppose to Submit it). This will store the Timecard in the
307 -- DB with a status of WORKING. This whole procedure is mimicking
308 -- the Save as it happens in the deposit wrapper HXC_TIMECARD.
309 -----------------------------------------------------------------------------
310 PROCEDURE save_timecard (
311 p_blocks IN OUT NOCOPY hxc_block_table_type,
312 p_attributes IN OUT NOCOPY hxc_attribute_table_type,
313 p_messages IN OUT NOCOPY hxc_message_table_type,
314 p_timecard_id OUT NOCOPY hxc_time_building_blocks.time_building_block_id%TYPE,
315 p_timecard_ovn OUT NOCOPY hxc_time_building_blocks.object_version_number%TYPE
316 )
317 IS
318 l_timecard_blocks hxc_timecard.block_list;
319 l_day_blocks hxc_timecard.block_list;
320 l_detail_blocks hxc_timecard.block_list;
321 l_transaction_info hxc_timecard.transaction_info;
322 l_timecard_props hxc_timecard_prop_table_type;
323 l_proc VARCHAR2 (50) := g_package || 'save_timecard';
324 l_can_deposit BOOLEAN := TRUE;
325 l_timecard_index NUMBER;
326 l_old_style_blks hxc_self_service_time_deposit.timecard_info;
327 l_old_style_attrs hxc_self_service_time_deposit.building_block_attribute_info;
328 l_old_messages hxc_self_service_time_deposit.message_table;
329 l_message fnd_new_messages.message_text%type;
330
331 l_resource_id number;
332 l_start_date date;
333 l_stop_date date;
334 l_tc_status varchar2(20);
335
336
337 TC_SAVE_EXCEPTION EXCEPTION;
338
339 l_timecard_start_time DATE;
340 l_timecard_stop_time DATE;
341
342 l_index NUMBER;
343 BEGIN
344 fnd_msg_pub.initialize;
345 hxc_timecard_message_helper.initializeerrors;
346
347 --This is done in the call to hxc_timecard_validation.deposit_validation
348 --hxc_time_category_utils_pkg.push_timecard (p_blocks, p_attributes);
349
350 p_messages := hxc_message_table_type ();
351 hxc_timecard_block_utils.initialize_timecard_index;
352
353 -- set savepoint
354 savepoint TC_SAVE_SAVEPOINT;
355
356 -- Check input parameters
357 hxc_deposit_checks.check_inputs (p_blocks => p_blocks,
358 p_attributes => p_attributes,
359 p_deposit_mode => hxc_timecard.c_save,
360 p_template => hxc_timecard.c_no,
361 p_messages => p_messages
362 );
363 hxc_timecard_message_helper.processerrors (p_messages => p_messages);
364 -- First we are getting the preferences
365 l_timecard_index :=
366 hxc_timecard_block_utils.find_active_timecard_index (p_blocks);
367
368 l_timecard_start_time :=
369 fnd_date.canonical_to_date(p_blocks (l_timecard_index).start_time) ;
370
371 l_timecard_stop_time :=
372 fnd_date.canonical_to_date(p_blocks (l_timecard_index).stop_time) ;
373
374 -- Bug 14828613
375 -- Added these initializations to take care of correct absence summary processing.
376
377 IF g_debug
378 THEN
379 hr_utility.trace('ABS: Initializing Absence global variables ');
380 END IF;
381
382 hxc_retrieve_absences.g_person_id := p_blocks(l_timecard_index).resource_id;
383 hxc_retrieve_absences.g_start_time := l_timecard_start_time;
384 hxc_retrieve_absences.g_stop_time := l_timecard_stop_time;
385
386
387 IF g_debug THEN
388 hr_utility.trace('ABS> In hxc_timecard.create_timeecard');
389 hr_utility.trace('ABS> call hxc_retrieve_absences.insert_absence_summary_row');
390 hr_utility.trace('ABS: hxc_retrieve_absences.g_person_id '||hxc_retrieve_absences.g_person_id);
391 hr_utility.trace('ABS: hxc_retrieve_absences.g_start_time '||hxc_retrieve_absences.g_start_time);
392 hr_utility.trace('ABS: hxc_retrieve_absences.g_stop_time '||hxc_retrieve_absences.g_stop_time);
393
394 END IF;
395
396 IF (NVL(fnd_profile.value('HR_ABS_OTL_INTEGRATION'), 'N') = 'Y') THEN
397 hxc_retrieve_absences.insert_absence_summary_row;
398 END IF;
399
400
401 hxc_timecard_properties.get_preference_properties
402 (p_validate => hxc_timecard.c_yes,
403 p_resource_id => p_blocks (l_timecard_index).resource_id,
404 p_timecard_start_time => l_timecard_start_time,
405 p_timecard_stop_time => l_timecard_stop_time,
406 p_for_timecard => FALSE,
407 p_messages => p_messages,
408 p_property_table => l_timecard_props
409 );
410 hxc_timecard_message_helper.processerrors (p_messages => p_messages);
411
412 -- Bug 9023209
413 -- API absences validation
414 hxc_retrieve_absences.verify_view_only_absences
415 (p_blocks => p_blocks,
416 p_attributes => p_attributes,
417 p_lock_rowid => hxc_retrieve_absences.g_lock_row_id,
418 p_messages => p_messages);
419
420 -- Added for Bug 10326049
421 -- OTL - Projects Payroll Integration
422
423 IF g_debug THEN
424 hr_utility.trace('PA-PAY> In hxc_timestore_deposit_util.save_timecard');
425 hr_utility.trace('PA-PAY> calling hxc_proj_pay_dep_validation.verify_proj_pay_setup');
426 END IF;
427
428 hxc_proj_pay_dep_validation.verify_proj_pay_setup
429 (p_blocks => p_blocks,
430 p_attributes => p_attributes,
431 p_messages => p_messages);
432
433 IF g_debug THEN
434 hr_utility.trace('PA-PAY> out of hxc_proj_pay_dep_validation.verify_proj_pay_setup');
435 IF (p_messages.count>0) THEN
436
437
438 hr_utility.trace(' P_MESSAGES TABLE START ');
439 hr_utility.trace(' *****************');
440
441 l_index := p_messages.FIRST;
442
443 LOOP
444 EXIT WHEN NOT p_messages.EXISTS (l_index);
445
446
447 hr_utility.trace(' MESSAGE_NAME = '|| p_messages(l_index).MESSAGE_NAME );
448 hr_utility.trace(' MESSAGE_LEVEL = '|| p_messages(l_index).MESSAGE_LEVEL ) ;
449 hr_utility.trace(' MESSAGE_FIELD = '|| p_messages(l_index).MESSAGE_FIELD) ;
450 hr_utility.trace(' MESSAGE_TOKENS = '|| p_messages(l_index).MESSAGE_TOKENS ) ;
451 hr_utility.trace(' APPLICATION_SHORT_NAME = '|| p_messages(l_index).APPLICATION_SHORT_NAME ) ;
452 hr_utility.trace(' TIME_BUILDING_BLOCK_ID = '|| p_messages(l_index).TIME_BUILDING_BLOCK_ID ) ;
453 hr_utility.trace(' TIME_BUILDING_BLOCK_OVN = '|| p_messages(l_index).TIME_BUILDING_BLOCK_OVN ) ;
454 hr_utility.trace(' TIME_ATTRIBUTE_ID = '|| p_messages(l_index).TIME_ATTRIBUTE_ID ) ;
455 hr_utility.trace(' TIME_ATTRIBUTE_OVN = '|| p_messages(l_index).TIME_ATTRIBUTE_OVN ) ;
456 hr_utility.trace(' MESSAGE_EXTENT = '|| p_messages(l_index).MESSAGE_EXTENT ) ;
457
458 l_index := p_messages.NEXT (l_index);
459
460 END LOOP;
461
462 hr_utility.trace(' p_messages TABLE END ');
463 hr_utility.trace(' *****************');
464
465 END IF;
466 END IF;
467
468
469 -- p_messages.DELETE;
470 -- Sort blocks
471 hxc_timecard_block_utils.sort_blocks
472 (p_blocks => p_blocks,
473 p_timecard_blocks => l_timecard_blocks,
474 p_day_blocks => l_day_blocks,
475 p_detail_blocks => l_detail_blocks
476 );
477 -- Perform basic checks
478 hxc_deposit_checks.perform_checks (p_blocks => p_blocks,
479 p_attributes => p_attributes,
480 p_timecard_props => l_timecard_props,
481 p_days => l_day_blocks,
482 p_details => l_detail_blocks,
483 p_messages => p_messages
484 );
485 hxc_timecard_message_helper.processerrors (p_messages => p_messages);
486 -- Add the security attributes
487 -- ARR: 115.26 Add message structure
488 hxc_security.add_security_attribute
489 (p_blocks => p_blocks,
490 p_attributes => p_attributes,
491 p_timecard_props => l_timecard_props,
492 p_messages => p_messages
493 );
494 hxc_timecard_message_helper.processerrors (p_messages => p_messages);
495 -- Translate any aliases
496 hxc_timecard_deposit_common.alias_translation
497 (p_blocks => p_blocks,
498 p_attributes => p_attributes,
499 p_messages => p_messages
500 );
501 hxc_timecard_message_helper.processerrors (p_messages => p_messages);
502
503 -- Set the block and attribute update process flags Based on the data sent
504 -- and in the db
505 hxc_block_attribute_update.set_process_flags
506 (p_blocks => p_blocks,
507 p_attributes => p_attributes
508 );
509 hxc_timecard_attribute_utils.remove_deleted_attributes
510 (p_attributes => p_attributes);
511 /*
512 Validate the set up for the user
513
514 validate_setup
515 (p_deposit_mode => hxc_timecard.c_save
516 ,p_blocks => p_blocks
517 ,p_attributes => p_attributes
518 ,p_messages => p_messages
519 );
520
521 hxc_timecard_message_helper.processerrors
522 (p_messages => p_messages);
523 */ -- Call time entry rules for save
524
525
526 /*
527 l_old_style_blks :=
528 hxc_timecard_block_utils.convert_to_dpwr_blocks (p_blocks);
529 l_old_style_attrs :=
530 hxc_timecard_attribute_utils.convert_to_dpwr_attributes (p_attributes);
531
532 hxc_time_entry_rules_utils_pkg.execute_time_entry_rules
533 (p_operation => hxc_timecard.c_save,
534 p_time_building_blocks => l_old_style_blks,
535 p_time_attributes => l_old_style_attrs,
536 p_messages => l_old_messages,
537 p_resubmit => hxc_timecard.c_no,
538 p_blocks => p_blocks,
539 p_attributes => p_attributes
540 );
541 hxc_timecard_message_utils.append_old_messages
542 (p_messages => p_messages,
543 p_old_messages => l_old_messages,
544 p_retrieval_process_id => NULL
545 );
546 hxc_timecard_message_helper.processerrors (p_messages => p_messages);
547 */
548
549 -- Added the following validation code for bug 8900783
550 -- This validation is to check if the timecard start and stop times falls within the
551 -- PAST and FUTURE number of days for which timecard create/edit is allowed
552 IF g_debug THEN
553 hr_utility.trace('l_timecard_start_time = '||l_timecard_start_time);
554 hr_utility.trace('l_timecard_stop_time = '||l_timecard_stop_time);
555 END IF;
556
557 -- populate the global variables which hold the PAST and FUTURE date limits
558 hxc_timestore_deposit_util.get_past_future_limits
559 (p_resource_id => p_blocks (l_timecard_index).resource_id,
560 p_timecard_start_time => trunc(l_timecard_start_time),
561 p_timecard_stop_time => trunc(l_timecard_stop_time),
562 p_messages => p_messages
563 );
564
565 hxc_timecard_message_helper.processerrors (p_messages => p_messages);
566
567
568
569 -- Call hxc_timecard_validation.deposit_validation to do this along with
570 -- recipient application validation
571
572 -- Validate blocks, attributes
573 hxc_timecard_validation.deposit_validation
574 (p_blocks => p_blocks,
575 p_attributes => p_attributes,
576 p_messages => p_messages,
577 p_props => l_timecard_props,
578 p_deposit_mode => hxc_timecard.c_save,
579 p_template => hxc_timecard.c_no,
580 p_resubmit => hxc_timecard.c_no,
581 p_can_deposit => l_can_deposit
582 );
583
584
585 hxc_timecard_message_helper.processerrors (p_messages => p_messages);
586
587 --svg start Bug 9181950
588 if NOT hxc_timecard_message_helper.noErrors then
589 --HXC_SVG_LOG.record_log('Reached here:svg');
590 if g_debug then
591 hr_utility.trace('SVG: Rollingback to savepoint TC_SAVE_SAVEPOINT for any Projects related updates');
592 end if;
593 rollback to TC_SAVE_SAVEPOINT;
594 end if;
595 -- svg end
596
597
598
599 -- Store blocks and attributes
600 IF hxc_timecard_message_helper.noerrors
601 THEN
602 hxc_timecard_deposit.EXECUTE
603 (p_blocks => p_blocks,
604 p_attributes => p_attributes,
605 p_timecard_blocks => l_timecard_blocks,
606 p_day_blocks => l_day_blocks,
607 p_detail_blocks => l_detail_blocks,
608 p_messages => p_messages,
609 p_transaction_info => l_transaction_info
610 );
611 hxc_timecard_message_helper.processerrors (p_messages => p_messages);
612 --
613 -- Maintain summary table
614 --
615 hxc_timecard_summary_api.timecard_deposit
616 (p_blocks => p_blocks,
617 p_approval_item_type => NULL,
618 p_approval_process_name => NULL,
619 p_approval_item_key => NULL,
620 p_tk_audit_item_type => NULL,
621 p_tk_audit_process_name => NULL,
622 p_tk_audit_item_key => NULL
623 );
624 hxc_timecard_audit.maintain_latest_details (p_blocks => p_blocks);
625
626 /* Bug 8888904 */
627 hxc_timecard_audit.maintain_rdb_snapshot
628 (p_blocks => p_blocks,
629 p_attributes => p_attributes);
630
631
632 hxc_timecard_message_helper.processerrors (p_messages => p_messages);
633 END IF;
634
635 -- get all the errors
636 p_messages := hxc_timecard_message_helper.getmessages;
637 p_timecard_id :=
638 p_blocks
639 (hxc_timecard_block_utils.find_active_timecard_index (p_blocks)
640 ).time_building_block_id;
641 p_timecard_ovn :=
642 p_blocks
643 (hxc_timecard_block_utils.find_active_timecard_index (p_blocks)
644 ).object_version_number;
645
646
647 -- OTL-Absences Integration (Bug 8779478)
648 IF (nvl(fnd_profile.value('HR_ABS_OTL_INTEGRATION'), 'N') = 'Y') THEN
649 IF (p_timecard_id > 0 and hxc_timecard_message_helper.noerrors
650 and p_blocks(hxc_timecard_block_utils.find_active_timecard_index(p_blocks)).SCOPE <> hxc_timecard.c_template_scope) THEN
651
652 IF g_debug THEN
653 hr_utility.trace('ABS:Initiated Online Retrieval from HXC_TIMESTORE_DEPOSIT_UTIL.SAVE_TIMECARD');
654 END IF;
655
656 l_resource_id := p_blocks (l_timecard_index).resource_id;
657 l_start_date := fnd_date.canonical_to_date(p_blocks (l_timecard_index).start_time);
658 l_stop_date := fnd_date.canonical_to_date(p_blocks (l_timecard_index).stop_time);
659 l_tc_status := p_blocks (l_timecard_index).approval_status;
660
661 HXC_ABS_RETRIEVAL_PKG.POST_ABSENCES(l_resource_id,
662 l_start_date,
663 l_stop_date,
664 l_tc_status,
665 p_messages);
666
667 IF g_debug THEN
668 hr_utility.trace('ABS:p_messages.COUNT = '||p_messages.COUNT);
669 END IF;
670
671 IF p_messages.COUNT > 0 THEN
672 IF g_debug THEN
673 hr_utility.trace('ABS:Error in POST_ABSENCES - Rollback changes');
674 END IF;
675
676 l_message := fnd_message.get_string (
677 appin => p_messages(p_messages.last).application_short_name
678 , namein => p_messages(p_messages.last).message_name
679 );
680
681 --dbms_output.put_line('online retrieval error : '||substr(l_message,1,255));
682
683 -- Bug 14593332
684 -- Rollback only if there is at least one ERROR level message.
685 FOR i IN p_messages.FIRST..p_messages.LAST
686 LOOP
687 IF p_messages(i).message_level = 'ERROR'
688 THEN
689 hr_utility.trace('ABS: Error is '||p_messages(i).message_name);
690 ROLLBACK TO TC_SAVE_SAVEPOINT;
691 EXIT;
692 END IF;
693 END LOOP;
694
695 hxc_timecard_message_helper.processerrors
696 (p_messages => p_messages);
697
698 END IF;
699
700 END IF;
701 END IF;
702
703 -- Absences end
704
705
706 IF g_debug THEN
707 hr_utility.trace('Leaving SAVE_TIMECARD');
708 END IF;
709
710 END save_timecard;
711
712 -----------------------------------------------------------------------------
713 -- Type: Procedure
714 -- Scope: Public
715 -- Name: submit_timecard
716 -- IN OUT Parameters: p_item_type -> Item Type to be used by approval process
717 -- p_approval_prc -> Approval Process used for approval
718 -- p_template -> Is this TC a template, Y(es) or N(o)
719 -- p_mode -> 'SUBMIT' or 'AUDIT'
720 -- p_blocks -> The Timecard structure you want Submit
721 -- p_attributes -> The Timecard's attributes you want to
722 -- Submit
723 -- p_messages -> The messages returned from the Submit
724 -- process
725 -- OUT Parameters: p_timecard_id -> The timecard_id of the submitted Timecard
726 -- p_timecard_ovn -> the OVN of the submitted Timecard
727 --
728 -- Description: Private Procedure that can will be used to Submit the timecard
729 -- This will store the Timecard in the DB with a status of
730 -- SUBMITTED. This whole procedure is mimicking
731 -- the Submit as it happens in the deposit wrapper HXC_TIMECARD.
732 -----------------------------------------------------------------------------
733 PROCEDURE submit_timecard (
734 p_item_type IN wf_items.item_type%TYPE,
735 p_approval_prc IN wf_process_activities.process_name%TYPE,
736 p_template IN VARCHAR2,
737 p_mode IN VARCHAR2,
738 p_retrieval_process IN hxc_retrieval_processes.NAME%TYPE,
739 p_blocks IN OUT NOCOPY hxc_block_table_type,
740 p_attributes IN OUT NOCOPY hxc_attribute_table_type,
741 p_messages IN OUT NOCOPY hxc_message_table_type,
742 p_timecard_id OUT NOCOPY hxc_time_building_blocks.time_building_block_id%TYPE,
743 p_timecard_ovn OUT NOCOPY hxc_time_building_blocks.object_version_number%TYPE
744 )
745 IS
746 l_timecard_blocks hxc_timecard.block_list;
747 l_day_blocks hxc_timecard.block_list;
748 l_detail_blocks hxc_timecard.block_list;
749 l_transaction_info hxc_timecard.transaction_info;
750 l_old_transaction_info hxc_deposit_wrapper_utilities.t_transaction;
751 l_timecard_props hxc_timecard_prop_table_type;
752 l_proc VARCHAR2 (50)
753 := g_package || 'submit_timecard';
754 l_can_deposit BOOLEAN := TRUE;
755 l_resubmit VARCHAR2 (10) := hxc_timecard.c_no;
756 l_timecard_index NUMBER;
757 l_rollback BOOLEAN := FALSE;
758 l_item_key wf_items.item_key%TYPE := NULL;
759 l_mode VARCHAR2 (30);
760 l_message fnd_new_messages.message_text%type;
761
762 TC_SUB_EXCEPTION EXCEPTION;
763
764 l_resource_id number;
765 l_start_date date;
766 l_stop_date date;
767 l_tc_status varchar2(20);
768
769 l_timecard_start_time DATE;
770 l_timecard_stop_time DATE;
771
772 l_index NUMBER;
773 BEGIN
774 fnd_msg_pub.initialize;
775 hxc_timecard_message_helper.initializeerrors;
776
777 p_messages := hxc_message_table_type ();
778 hxc_timecard_block_utils.initialize_timecard_index;
779
780 -- set savepoint
781 savepoint TC_SUB_SAVEPOINT ;
782
783 -- Check input parameters
784 hxc_deposit_checks.check_inputs
785 (p_blocks => p_blocks,
786 p_attributes => p_attributes,
787 p_deposit_mode => hxc_timecard.c_submit,
788 p_template => hxc_timecard.c_no,
789 p_messages => p_messages
790 );
791 hxc_timecard_message_helper.processerrors (p_messages => p_messages);
792 -- Determine if this is a resubmitted timecard
793 l_timecard_index :=
794 hxc_timecard_block_utils.find_active_timecard_index (p_blocks);
795
796 IF (hxc_timecard_block_utils.date_value
797 (p_blocks (l_timecard_index).date_to
798 ) = hr_general.end_of_time
799 )
800 THEN
801 l_resubmit :=
802 hxc_timecard_approval.is_timecard_resubmitted
803 (p_blocks (l_timecard_index).time_building_block_id,
804 p_blocks (l_timecard_index).object_version_number,
805 p_blocks (l_timecard_index).resource_id,
806 fnd_date.canonical_to_date
807 (p_blocks (l_timecard_index).start_time
808 ),
809 fnd_date.canonical_to_date
810 (p_blocks (l_timecard_index).stop_time
811 )
812 );
813 ELSE
814 l_resubmit := hxc_timecard.c_delete;
815 END IF;
816
817
818 l_timecard_start_time :=
819 fnd_date.canonical_to_date(p_blocks (l_timecard_index).start_time) ;
820
821 l_timecard_stop_time :=
822 fnd_date.canonical_to_date(p_blocks (l_timecard_index).stop_time) ;
823
824
825 -- Bug 14828613
826 -- Added these initializations to take care of correct absence summary processing.
827
828 IF g_debug
829 THEN
830 hr_utility.trace('ABS: Initializing Absence global variables ');
831 END IF;
832
833 hxc_retrieve_absences.g_person_id := p_blocks(l_timecard_index).resource_id;
834 hxc_retrieve_absences.g_start_time := l_timecard_start_time;
835 hxc_retrieve_absences.g_stop_time := l_timecard_stop_time;
836
837
838 IF g_debug THEN
839 hr_utility.trace('ABS> In hxc_timecard.create_timeecard');
840 hr_utility.trace('ABS> call hxc_retrieve_absences.insert_absence_summary_row');
841 hr_utility.trace('ABS: hxc_retrieve_absences.g_person_id '||hxc_retrieve_absences.g_person_id);
842 hr_utility.trace('ABS: hxc_retrieve_absences.g_start_time '||hxc_retrieve_absences.g_start_time);
843 hr_utility.trace('ABS: hxc_retrieve_absences.g_stop_time '||hxc_retrieve_absences.g_stop_time);
844
845 END IF;
846
847 IF (NVL(fnd_profile.value('HR_ABS_OTL_INTEGRATION'), 'N') = 'Y') THEN
848 hxc_retrieve_absences.insert_absence_summary_row;
849 END IF;
850
851
852 -- Obtain the timecard properties
853 hxc_timecard_properties.get_preference_properties
854 (p_validate => hxc_timecard.c_yes,
855 p_resource_id => p_blocks (l_timecard_index).resource_id,
856 p_timecard_start_time => l_timecard_start_time,
857 p_timecard_stop_time => l_timecard_stop_time,
858 p_for_timecard => FALSE,
859 p_messages => p_messages,
860 p_property_table => l_timecard_props
861 );
862 hxc_timecard_message_helper.processerrors (p_messages => p_messages);
863 -- Sort the blocks - needed for deposit and all sorts of short cuts!
864
865 -- Bug 9023209
866 -- API absences validation
867 hxc_retrieve_absences.verify_view_only_absences
868 (p_blocks => p_blocks,
869 p_attributes => p_attributes,
870 p_lock_rowid => hxc_retrieve_absences.g_lock_row_id,
871 p_messages => p_messages);
872
873 -- Added for Bug 10326049
874 -- OTL - Projects Payroll Integration
875
876 IF g_debug THEN
877 hr_utility.trace('PA-PAY> In hxc_timestore_deposit_util.submit_timecard');
878 hr_utility.trace('PA-PAY> calling hxc_proj_pay_dep_validation.verify_proj_pay_setup');
879 END IF;
880
881 hxc_proj_pay_dep_validation.verify_proj_pay_setup
882 (p_blocks => p_blocks,
883 p_attributes => p_attributes,
884 p_messages => p_messages);
885
886 IF g_debug THEN
887 hr_utility.trace('PA-PAY> out of hxc_proj_pay_dep_validation.verify_proj_pay_setup');
888 IF (p_messages.count>0) THEN
889
890
891 hr_utility.trace(' P_MESSAGES TABLE START ');
892 hr_utility.trace(' *****************');
893
894 l_index := p_messages.FIRST;
895
896 LOOP
897 EXIT WHEN NOT p_messages.EXISTS (l_index);
898
899
900 hr_utility.trace(' MESSAGE_NAME = '|| p_messages(l_index).MESSAGE_NAME );
901 hr_utility.trace(' MESSAGE_LEVEL = '|| p_messages(l_index).MESSAGE_LEVEL ) ;
902 hr_utility.trace(' MESSAGE_FIELD = '|| p_messages(l_index).MESSAGE_FIELD) ;
903 hr_utility.trace(' MESSAGE_TOKENS = '|| p_messages(l_index).MESSAGE_TOKENS ) ;
904 hr_utility.trace(' APPLICATION_SHORT_NAME = '|| p_messages(l_index).APPLICATION_SHORT_NAME ) ;
905 hr_utility.trace(' TIME_BUILDING_BLOCK_ID = '|| p_messages(l_index).TIME_BUILDING_BLOCK_ID ) ;
906 hr_utility.trace(' TIME_BUILDING_BLOCK_OVN = '|| p_messages(l_index).TIME_BUILDING_BLOCK_OVN ) ;
907 hr_utility.trace(' TIME_ATTRIBUTE_ID = '|| p_messages(l_index).TIME_ATTRIBUTE_ID ) ;
908 hr_utility.trace(' TIME_ATTRIBUTE_OVN = '|| p_messages(l_index).TIME_ATTRIBUTE_OVN ) ;
909 hr_utility.trace(' MESSAGE_EXTENT = '|| p_messages(l_index).MESSAGE_EXTENT ) ;
910
911 l_index := p_messages.NEXT (l_index);
912
913 END LOOP;
914
915 hr_utility.trace(' p_messages TABLE END ');
916 hr_utility.trace(' *****************');
917
918 END IF;
919 END IF;
920 hxc_timecard_block_utils.sort_blocks
921 (p_blocks => p_blocks,
922 p_timecard_blocks => l_timecard_blocks,
923 p_day_blocks => l_day_blocks,
924 p_detail_blocks => l_detail_blocks
925 );
926 --
927 -- Main deposit controls
928 -- ^^^^^^^^^^^^^^^^^^^^^
929 -- Reform time data, if required
930 -- e.g Denormalize time data
931 --
932 hxc_block_attribute_update.denormalize_time (p_blocks => p_blocks,
933 p_mode => 'ADD'
934 );
935
936 --
937 -- Perform basic checks, e.g.
938 -- Are there any other timecards for this period?
939 --
940 IF (p_template = hxc_timecard.c_no)
941 THEN
942 hxc_deposit_checks.perform_checks
943 (p_blocks => p_blocks,
944 p_attributes => p_attributes,
945 p_timecard_props => l_timecard_props,
946 p_days => l_day_blocks,
947 p_details => l_detail_blocks,
948 p_messages => p_messages
949 );
950 hxc_timecard_message_helper.processerrors (p_messages => p_messages);
951 END IF;
952
953 -- Add the security attributes
954 -- ARR: 115.26 Add message structure
955 hxc_security.add_security_attribute
956 (p_blocks => p_blocks,
957 p_attributes => p_attributes,
958 p_timecard_props => l_timecard_props,
959 p_messages => p_messages
960 );
961 hxc_timecard_message_helper.processerrors (p_messages => p_messages);
962 -- Translate any aliases
963 hxc_timecard_deposit_common.alias_translation
964 (p_blocks => p_blocks,
965 p_attributes => p_attributes,
966 p_messages => p_messages
967 );
968 hxc_timecard_message_helper.processerrors (p_messages => p_messages);
969
970 -- Set the block and attribute update process flags based on the data sent
971 -- and in the db
972 hxc_block_attribute_update.set_process_flags
973 (p_blocks => p_blocks,
974 p_attributes => p_attributes
975 );
976 hxc_timecard_attribute_utils.remove_deleted_attributes
977 (p_attributes => p_attributes);
978 -- Perform process checks
979 hxc_deposit_checks.perform_process_checks
980 (p_blocks => p_blocks,
981 p_attributes => p_attributes,
982 p_timecard_props => l_timecard_props,
983 p_days => l_day_blocks,
984 p_details => l_detail_blocks,
985 p_template => hxc_timecard.c_no,
986 p_deposit_mode => hxc_timecard.c_submit,
987 p_messages => p_messages
988 );
989 hxc_timecard_message_helper.processerrors (p_messages => p_messages);
990
991
992 -- Added the following validation code for bug 8900783
993 -- This validation is to check if the timecard start and stop times falls within the
994 -- PAST and FUTURE number of days for which timecard create/edit is allowed
995 IF g_debug THEN
996 hr_utility.trace('l_timecard_start_time = '||l_timecard_start_time);
997 hr_utility.trace('l_timecard_stop_time = '||l_timecard_stop_time);
998 END IF;
999
1000 -- populate the global variables which hold the PAST and FUTURE date limits
1001 hxc_timestore_deposit_util.get_past_future_limits
1002 (p_resource_id => p_blocks (l_timecard_index).resource_id,
1003 p_timecard_start_time => trunc(l_timecard_start_time),
1004 p_timecard_stop_time => trunc(l_timecard_stop_time),
1005 p_messages => p_messages
1006 );
1007
1008 hxc_timecard_message_helper.processerrors (p_messages => p_messages);
1009
1010 -- Validate blocks, attributes
1011 IF (p_mode <> hxc_timestore_deposit.c_migration
1012 --AND hxc_timestore_deposit.g_validate <> TRUE) -- Commented this condition for the bug 14494474
1013 )
1014 THEN
1015 hxc_timecard_validation.deposit_validation
1016 (p_blocks => p_blocks,
1017 p_attributes => p_attributes,
1018 p_messages => p_messages,
1019 p_props => l_timecard_props,
1020 p_deposit_mode => p_mode,
1021 p_template => hxc_timecard.c_no,
1022 p_resubmit => l_resubmit,
1023 p_can_deposit => l_can_deposit
1024 );
1025 ELSE -- minimal validation for migration
1026 hxc_timecard_validation.data_set_validation
1027 (p_blocks => p_blocks,
1028 p_messages => p_messages
1029 );
1030 END IF;
1031
1032 hxc_timecard_message_helper.processerrors (p_messages => p_messages);
1033 -- Validate the set up for the user Do this only for timecards, and not
1034 -- for templates.
1035 /*
1036 hxc_timecard_deposit_common.validate_setup
1037 (p_deposit_mode => hxc_timecard.c_submit
1038 ,p_blocks => p_blocks
1039 ,p_attributes => p_attributes
1040 ,p_messages => p_messages
1041 );
1042
1043 hxc_timecard_message_helper.processerrors
1044 (p_messages => p_messages);
1045 */
1046 -- Reform time data, if required e.g Denormalize time data
1047 hxc_block_attribute_update.denormalize_time (p_blocks => p_blocks,
1048 p_mode => 'REMOVE'
1049 );
1050
1051 IF perform_audit (p_props => l_timecard_props, p_blocks => p_blocks)
1052 THEN
1053 -- Get the messages to perform the audit check
1054 p_messages := hxc_timecard_message_helper.getmessages;
1055 -- Perform Audit Checks
1056 -- Mental Note on how this works:)
1057 -- hxc_timecard_validation.deposit_validation called above, raises
1058 -- proper errors if something is really wrong, e.g. during the
1059 -- project or payroll validation. However for Change and Late Audit
1060 -- (CLA), it stores special messages in message table p_messages.
1061 -- These messages have a message_level of REASON, are not considered
1062 -- errors and will not be recognized by
1063 -- hxc_timecard_message_helper.noerrors as errors.
1064 -- hxc_deposit_checks.audit_checks transforms these REASON errors
1065 -- into proper errors that will be caught by
1066 -- hxc_timecard_message_helper.noerrors.
1067 -- So effectively what we are doing here is converting the audit
1068 -- message into errors when the user calls the API and AUDIT is
1069 -- switched on in the preferences of the employee/resource.
1070 -- If the user calls the API and AUDIT is not set, we completely
1071 -- ignore the auditmessages and continue with the submit.
1072 hxc_deposit_checks.audit_checks (p_blocks => p_blocks,
1073 p_attributes => p_attributes,
1074 p_messages => p_messages
1075 );
1076 hxc_timecard_message_helper.processerrors (p_messages => p_messages);
1077 END IF;
1078
1079 --svg start Bug 9181950
1080 if NOT hxc_timecard_message_helper.noErrors then
1081 --HXC_SVG_LOG.record_log('Reached here:svg');
1082 if g_debug then
1083 hr_utility.trace('SVG: Rollingback to savepoint TC_SUB_SAVEPOINT for any Projects related updates');
1084 end if;
1085 rollback to TC_SUB_SAVEPOINT;
1086 end if;
1087 -- svg end
1088
1089
1090
1091 -- Store blocks and attributes
1092 IF hxc_timecard_message_helper.noerrors
1093 THEN
1094 hxc_timecard_deposit.EXECUTE
1095 (p_blocks => p_blocks,
1096 p_attributes => p_attributes,
1097 p_timecard_blocks => l_timecard_blocks,
1098 p_day_blocks => l_day_blocks,
1099 p_detail_blocks => l_detail_blocks,
1100 p_messages => p_messages,
1101 p_transaction_info => l_transaction_info
1102 );
1103
1104
1105
1106
1107 hxc_timecard_message_helper.processerrors (p_messages => p_messages);
1108 -- set the out parameters
1109 p_timecard_id :=
1110 p_blocks
1111 (hxc_timecard_block_utils.find_active_timecard_index (p_blocks)
1112 ).time_building_block_id;
1113 p_timecard_ovn :=
1114 p_blocks
1115 (hxc_timecard_block_utils.find_active_timecard_index (p_blocks)
1116 ).object_version_number;
1117
1118 IF ( (p_template <> hxc_timecard.c_yes)
1119 AND (hxc_timecard_message_helper.noerrors)
1120 )
1121 THEN
1122 --
1123 -- Maintain summary table
1124 --
1125 IF (p_mode = hxc_timestore_deposit.c_migration)
1126 THEN
1127 l_mode := g_oit_migration;
1128 ELSE
1129 l_mode := hxc_timecard_summary_pkg.c_normal_mode;
1130 END IF;
1131
1132 hxc_timecard_summary_api.timecard_deposit
1133 (p_blocks => p_blocks,
1134 p_mode => l_mode,
1135 p_approval_item_type => NULL,
1136 p_approval_process_name => NULL,
1137 p_approval_item_key => NULL,
1138 p_tk_audit_item_type => NULL,
1139 p_tk_audit_process_name => NULL,
1140 p_tk_audit_item_key => NULL
1141 );
1142 hxc_timecard_audit.maintain_latest_details (p_blocks => p_blocks);
1143
1144 /* Bug 8888904 */
1145 hxc_timecard_audit.maintain_rdb_snapshot
1146 (p_blocks => p_blocks,
1147 p_attributes => p_attributes);
1148
1149 hxc_timecard_message_helper.processerrors
1150 (p_messages => p_messages);
1151
1152 -- OTL-Absences Integration (Bug 8779478)
1153 IF (nvl(fnd_profile.value('HR_ABS_OTL_INTEGRATION'), 'N') = 'Y')
1154 THEN
1155 IF (p_timecard_id > 0 and hxc_timecard_message_helper.noerrors
1156 and p_blocks(hxc_timecard_block_utils.find_active_timecard_index(p_blocks)).SCOPE <> hxc_timecard.c_template_scope)
1157 THEN
1158 IF g_debug THEN
1159 hr_utility.trace('ABS:Initiated Online Retrieval from HXC_TIMESTORE_DEPOSIT_UTIL.SUBMIT_TIMECARD');
1160 END IF;
1161
1162 l_resource_id := p_blocks (l_timecard_index).resource_id;
1163 l_start_date := fnd_date.canonical_to_date(p_blocks (l_timecard_index).start_time);
1164 l_stop_date := fnd_date.canonical_to_date(p_blocks (l_timecard_index).stop_time);
1165 l_tc_status := p_blocks (l_timecard_index).approval_status;
1166
1167 HXC_ABS_RETRIEVAL_PKG.POST_ABSENCES(l_resource_id,
1168 l_start_date,
1169 l_stop_date,
1170 l_tc_status,
1171 p_messages);
1172
1173 IF g_debug THEN
1174 hr_utility.trace('ABS:p_messages.COUNT = '||p_messages.COUNT);
1175 END IF;
1176
1177 if p_messages.COUNT > 0
1178 then
1179 IF g_debug THEN
1180 hr_utility.trace('ABS:Error in POST_ABSENCES - Rollback changes');
1181 END IF;
1182
1183 l_message := fnd_message.get_string (
1184 appin => p_messages(p_messages.last).application_short_name
1185 , namein => p_messages(p_messages.last).message_name
1186 );
1187
1188 --dbms_output.put_line('online retrieval error : '||substr(l_message,1,255));
1189
1190 -- Bug 14593332
1191 -- Rollback only if there is at least one Error message.
1192 FOR i IN p_messages.FIRST..p_messages.LAST
1193 LOOP
1194 IF p_messages(i).message_level = 'ERROR'
1195 THEN
1196 hr_utility.trace('ABS: Error is '||p_messages(i).message_name);
1197 ROLLBACK TO TC_SUB_SAVEPOINT;
1198 EXIT;
1199 END IF;
1200 END LOOP;
1201
1202 hxc_timecard_message_helper.processerrors
1203 (p_messages => p_messages);
1204
1205 end if;
1206
1207 END IF;
1208 END IF;
1209
1210 -- Absences end
1211
1212 -- Modified for Bug 13401578
1213 IF (p_mode <> hxc_timestore_deposit.c_migration
1214 AND hxc_timestore_deposit.g_validate <> TRUE)
1215 THEN
1216 l_item_key :=
1217 hxc_timecard_approval.begin_approval
1218 (p_blocks => p_blocks,
1219 p_item_type => p_item_type,
1220 p_process_name => p_approval_prc,
1221 p_resubmitted => l_resubmit,
1222 p_timecard_props => l_timecard_props,
1223 p_messages => p_messages
1224 );
1225
1226 hxc_timecard_message_helper.processerrors
1227 (p_messages => p_messages);
1228
1229 -- Absences start
1230
1231 IF (nvl(fnd_profile.value('HR_ABS_OTL_INTEGRATION'), 'N') = 'Y') THEN
1232 hr_utility.trace('ABS:Checking status of BEGIN_APPROVAL');
1233 hr_utility.trace('ABS:p_messages.COUNT = '||p_messages.COUNT);
1234
1235 if p_messages.COUNT > 0 then
1236 hr_utility.trace('ABS:Error in POST_ABSENCES - Rollback changes');
1237
1238 l_message := fnd_message.get_string (
1239 appin => p_messages(p_messages.last).application_short_name
1240 , namein => p_messages(p_messages.last).message_name
1241 );
1242
1243 --dbms_output.put_line('online retrieval error : '||substr(l_message,1,255));
1244
1245 rollback to TC_SUB_SAVEPOINT;
1246
1247 hxc_timecard_message_helper.processerrors
1248 (p_messages => p_messages);
1249 end if;
1250 END IF;
1251
1252 -- Absences end
1253
1254 hxc_timecard_summary_pkg.update_summary_row
1255 (p_timecard_id => p_timecard_id,
1256 p_approval_item_type => p_item_type,
1257 p_approval_process_name => p_approval_prc,
1258 p_approval_item_key => l_item_key
1259 );
1260 END IF;
1261 ELSIF ( (p_template = hxc_timecard.c_yes)
1262 AND (hxc_timecard_message_helper.noerrors)
1263 )
1264 THEN
1265 hxc_template_summary_api.template_deposit
1266 (p_blocks => p_blocks,
1267 p_attributes => p_attributes,
1268 p_template_id => p_timecard_id
1269 );
1270 END IF;
1271 END IF;
1272
1273 -- Audit this transaction
1274 hxc_timecard_audit.audit_deposit
1275 (p_transaction_info => l_transaction_info,
1276 p_messages => p_messages
1277 );
1278 hxc_timecard_message_helper.processerrors (p_messages => p_messages);
1279
1280
1281
1282
1283 IF ( (p_mode = hxc_timestore_deposit.c_migration)
1284 AND (hxc_timecard_message_helper.noerrors)
1285 )
1286 THEN
1287 l_old_transaction_info :=
1288 convert_new_trans_info_to_old
1289 (p_transaction_info => l_transaction_info);
1290 hxc_deposit_wrapper_utilities.audit_transaction
1291 (p_effective_date => SYSDATE,
1292 p_transaction_type => 'RETRIEVAL',
1293 p_transaction_process_id => get_retrieval_process_id
1294 (p_retrieval_process_name => p_retrieval_process
1295 ),
1296 p_overall_status => 'SUCCESS',
1297 p_transaction_tab => l_old_transaction_info
1298 );
1299 hxc_timecard_message_helper.processerrors (p_messages => p_messages);
1300 END IF;
1301
1302 -- get all the errors
1303 p_messages := hxc_timecard_message_helper.getmessages;
1304
1305 IF g_debug THEN
1306 hr_utility.trace('Leaving SUBMIT_TIMECARD');
1307 END IF;
1308
1309 END submit_timecard;
1310
1311 -----------------------------------------------------------------------------
1312 -- Type: Function
1313 -- Scope: Public
1314 -- Name: convert_tbb_to_type
1315 -- Returns: hxc_block_table_type
1316 -- IN Parameters: p_blocks -> The PL/SQL Timecard structure you want to convert
1317 --
1318 -- Description: Private Function that will convert the old PL/SQL Timecard
1319 -- structure to the new TYPE that is then returned
1320 -- FYI: This is the reverse function of convert_to_dpwr_blocks
1321 -- that can be found in hxc_timecard_block_utils. It is not
1322 -- defined in that procedure as it is only needed for this API
1323 -- hence we define it here as a provate function
1324 -----------------------------------------------------------------------------
1325 FUNCTION convert_tbb_to_type (
1326 p_blocks IN hxc_self_service_time_deposit.timecard_info
1327 )
1328 RETURN hxc_block_table_type
1329 IS
1330 l_proc VARCHAR2 (72);
1331 l_blocks hxc_block_table_type;
1332 l_index PLS_INTEGER;
1333 BEGIN
1334 g_debug := hr_utility.debug_enabled;
1335
1336 IF g_debug
1337 THEN
1338 l_proc := g_package || 'convert_tbb_to_type';
1339 hr_utility.set_location ('Entering:' || l_proc, 10);
1340 END IF;
1341
1342 -- Initialize the collection
1343 l_blocks := hxc_block_table_type ();
1344 l_index := p_blocks.FIRST;
1345
1346 LOOP
1347 EXIT WHEN NOT p_blocks.EXISTS (l_index);
1348 l_blocks.EXTEND;
1349 l_blocks (l_blocks.LAST) :=
1350 hxc_block_type
1351 (p_blocks (l_index).time_building_block_id,
1352 p_blocks (l_index).TYPE,
1353 p_blocks (l_index).measure,
1354 p_blocks (l_index).unit_of_measure,
1355 fnd_date.date_to_canonical (p_blocks (l_index).start_time),
1356 fnd_date.date_to_canonical (p_blocks (l_index).stop_time),
1357 p_blocks (l_index).parent_building_block_id,
1358 p_blocks (l_index).parent_is_new,
1359 p_blocks (l_index).SCOPE,
1360 p_blocks (l_index).object_version_number,
1361 p_blocks (l_index).approval_status,
1362 p_blocks (l_index).resource_id,
1363 p_blocks (l_index).resource_type,
1364 p_blocks (l_index).approval_style_id,
1365 fnd_date.date_to_canonical (p_blocks (l_index).date_from),
1366 fnd_date.date_to_canonical (p_blocks (l_index).date_to),
1367 p_blocks (l_index).comment_text,
1368 p_blocks (l_index).parent_building_block_ovn,
1369 p_blocks (l_index).NEW,
1370 p_blocks (l_index).changed,
1371 NULL, -- Process
1372 p_blocks (l_index).application_set_id,
1373 NULL -- Can not set this from old structure at the moment
1374 );
1375 l_index := p_blocks.NEXT (l_index);
1376 END LOOP;
1377
1378 IF g_debug
1379 THEN
1380 hr_utility.set_location ( ' returning l_blocks.count = '
1381 || l_blocks.COUNT,
1382 20
1383 );
1384 hr_utility.set_location ('Leaving: ' || l_proc, 30);
1385 END IF;
1386
1387 RETURN l_blocks;
1388 END convert_tbb_to_type;
1389
1390 -----------------------------------------------------------------------------
1391 -- Type: Function
1392 -- Scope: Public
1393 -- Name: convert_new_trans_info_to_old
1394 -- Returns: hxc_deposit_wrapper_utilities.t_transaction
1395 -- IN Parameters: p_trans_info -> The PL/SQL Transaction info table you want to
1396 -- convert
1397 -- Description: Public Function that will convert the new PL/SQL Transaction
1398 -- PL/SQL table to the old PL/SQL Table that is then returned
1399 -----------------------------------------------------------------------------
1400 FUNCTION convert_new_trans_info_to_old (
1401 p_transaction_info IN hxc_timecard.transaction_info
1402 )
1403 RETURN hxc_deposit_wrapper_utilities.t_transaction
1404 IS
1405 l_proc VARCHAR2 (72);
1406 l_transaction_info hxc_deposit_wrapper_utilities.t_transaction;
1407 l_index PLS_INTEGER;
1408 BEGIN
1409 g_debug := hr_utility.debug_enabled;
1410
1411 IF g_debug
1412 THEN
1413 l_proc := g_package || 'convert_new_trans_info_to_old';
1414 hr_utility.set_location ('Entering:' || l_proc, 10);
1415 END IF;
1416
1417 l_index := p_transaction_info.FIRST;
1418
1419 LOOP
1420 EXIT WHEN NOT p_transaction_info.EXISTS (l_index);
1421 l_transaction_info (l_index).txd_id :=
1422 p_transaction_info (l_index).transaction_detail_id;
1423 l_transaction_info (l_index).tbb_id :=
1424 p_transaction_info (l_index).time_building_block_id;
1425 l_transaction_info (l_index).tbb_ovn :=
1426 p_transaction_info (l_index).object_version_number;
1427 l_transaction_info (l_index).status :=
1428 p_transaction_info (l_index).status;
1429 l_transaction_info (l_index).exception_desc :=
1430 p_transaction_info (l_index).exception_desc;
1431 l_index := p_transaction_info.NEXT (l_index);
1432 END LOOP;
1433
1434 IF g_debug
1435 THEN
1436 hr_utility.set_location
1437 ( ' returning l_transaction_info.count = '
1438 || l_transaction_info.COUNT,
1439 20
1440 );
1441 hr_utility.set_location ('Leaving: ' || l_proc, 30);
1442 END IF;
1443
1444 RETURN l_transaction_info;
1445 END convert_new_trans_info_to_old;
1446
1447 PROCEDURE update_value (
1448 p_attributes IN OUT NOCOPY hxc_attribute_table_type,
1449 p_index IN NUMBER,
1450 p_segment IN hxc_mapping_components.SEGMENT%TYPE,
1451 p_value IN hxc_time_attributes.attribute1%TYPE
1452 )
1453 IS
1454 BEGIN
1455 IF (p_segment = 'ATTRIBUTE1')
1456 THEN
1457 p_attributes (p_index).attribute1 := p_value;
1458 ELSIF (p_segment = 'ATTRIBUTE2')
1459 THEN
1460 p_attributes (p_index).attribute2 := p_value;
1461 ELSIF (p_segment = 'ATTRIBUTE3')
1462 THEN
1463 p_attributes (p_index).attribute3 := p_value;
1464 ELSIF (p_segment = 'ATTRIBUTE4')
1465 THEN
1466 p_attributes (p_index).attribute4 := p_value;
1467 ELSIF (p_segment = 'ATTRIBUTE5')
1468 THEN
1469 p_attributes (p_index).attribute5 := p_value;
1470 ELSIF (p_segment = 'ATTRIBUTE6')
1471 THEN
1472 p_attributes (p_index).attribute6 := p_value;
1473 ELSIF (p_segment = 'ATTRIBUTE7')
1474 THEN
1475 p_attributes (p_index).attribute7 := p_value;
1476 ELSIF (p_segment = 'ATTRIBUTE8')
1477 THEN
1478 p_attributes (p_index).attribute8 := p_value;
1479 ELSIF (p_segment = 'ATTRIBUTE9')
1480 THEN
1481 p_attributes (p_index).attribute9 := p_value;
1482 ELSIF (p_segment = 'ATTRIBUTE10')
1483 THEN
1484 p_attributes (p_index).attribute10 := p_value;
1485 ELSIF (p_segment = 'ATTRIBUTE11')
1486 THEN
1487 p_attributes (p_index).attribute11 := p_value;
1488 ELSIF (p_segment = 'ATTRIBUTE12')
1489 THEN
1490 p_attributes (p_index).attribute12 := p_value;
1491 ELSIF (p_segment = 'ATTRIBUTE13')
1492 THEN
1493 p_attributes (p_index).attribute13 := p_value;
1494 ELSIF (p_segment = 'ATTRIBUTE14')
1495 THEN
1496 p_attributes (p_index).attribute14 := p_value;
1497 ELSIF (p_segment = 'ATTRIBUTE15')
1498 THEN
1499 p_attributes (p_index).attribute15 := p_value;
1500 ELSIF (p_segment = 'ATTRIBUTE16')
1501 THEN
1502 p_attributes (p_index).attribute16 := p_value;
1503 ELSIF (p_segment = 'ATTRIBUTE17')
1504 THEN
1505 p_attributes (p_index).attribute17 := p_value;
1506 ELSIF (p_segment = 'ATTRIBUTE18')
1507 THEN
1508 p_attributes (p_index).attribute18 := p_value;
1509 ELSIF (p_segment = 'ATTRIBUTE19')
1510 THEN
1511 p_attributes (p_index).attribute19 := p_value;
1512 ELSIF (p_segment = 'ATTRIBUTE20')
1513 THEN
1514 p_attributes (p_index).attribute20 := p_value;
1515 ELSIF (p_segment = 'ATTRIBUTE21')
1516 THEN
1517 p_attributes (p_index).attribute21 := p_value;
1518 ELSIF (p_segment = 'ATTRIBUTE22')
1519 THEN
1520 p_attributes (p_index).attribute22 := p_value;
1521 ELSIF (p_segment = 'ATTRIBUTE23')
1522 THEN
1523 p_attributes (p_index).attribute23 := p_value;
1524 ELSIF (p_segment = 'ATTRIBUTE24')
1525 THEN
1526 p_attributes (p_index).attribute24 := p_value;
1527 ELSIF (p_segment = 'ATTRIBUTE25')
1528 THEN
1529 p_attributes (p_index).attribute25 := p_value;
1530 ELSIF (p_segment = 'ATTRIBUTE26')
1531 THEN
1532 p_attributes (p_index).attribute26 := p_value;
1533 ELSIF (p_segment = 'ATTRIBUTE27')
1534 THEN
1535 p_attributes (p_index).attribute27 := p_value;
1536 ELSIF (p_segment = 'ATTRIBUTE28')
1537 THEN
1538 p_attributes (p_index).attribute28 := p_value;
1539 ELSIF (p_segment = 'ATTRIBUTE29')
1540 THEN
1541 p_attributes (p_index).attribute29 := p_value;
1542 ELSIF (p_segment = 'ATTRIBUTE30')
1543 THEN
1544 p_attributes (p_index).attribute30 := p_value;
1545 ELSIF (p_segment = 'ATTRIBUTE_CATEGORY')
1546 THEN
1547 p_attributes (p_index).attribute_category := p_value;
1548 END IF;
1549 END update_value;
1550
1551 PROCEDURE set_new_attribute_value (
1552 p_attribute IN OUT NOCOPY hxc_attribute_type,
1553 p_segment IN hxc_mapping_components.SEGMENT%TYPE,
1554 p_value IN hxc_time_attributes.attribute1%TYPE,
1555 p_changed IN hxc_time_attributes.attribute1%TYPE
1556 )
1557 IS
1558 BEGIN
1559 IF (p_segment = 'ATTRIBUTE1')
1560 THEN
1561 p_attribute.attribute1 := p_value;
1562 ELSIF (p_segment = 'ATTRIBUTE2')
1563 THEN
1564 p_attribute.attribute2 := p_value;
1565 ELSIF (p_segment = 'ATTRIBUTE3')
1566 THEN
1567 p_attribute.attribute3 := p_value;
1568 ELSIF (p_segment = 'ATTRIBUTE4')
1569 THEN
1570 p_attribute.attribute4 := p_value;
1571 ELSIF (p_segment = 'ATTRIBUTE5')
1572 THEN
1573 p_attribute.attribute5 := p_value;
1574 ELSIF (p_segment = 'ATTRIBUTE6')
1575 THEN
1576 p_attribute.attribute6 := p_value;
1577 ELSIF (p_segment = 'ATTRIBUTE7')
1578 THEN
1579 p_attribute.attribute7 := p_value;
1580 ELSIF (p_segment = 'ATTRIBUTE8')
1581 THEN
1582 p_attribute.attribute8 := p_value;
1583 ELSIF (p_segment = 'ATTRIBUTE9')
1584 THEN
1585 p_attribute.attribute9 := p_value;
1586 ELSIF (p_segment = 'ATTRIBUTE10')
1587 THEN
1588 p_attribute.attribute10 := p_value;
1589 ELSIF (p_segment = 'ATTRIBUTE11')
1590 THEN
1591 p_attribute.attribute11 := p_value;
1592 ELSIF (p_segment = 'ATTRIBUTE12')
1593 THEN
1594 p_attribute.attribute12 := p_value;
1595 ELSIF (p_segment = 'ATTRIBUTE13')
1596 THEN
1597 p_attribute.attribute13 := p_value;
1598 ELSIF (p_segment = 'ATTRIBUTE14')
1599 THEN
1600 p_attribute.attribute14 := p_value;
1601 ELSIF (p_segment = 'ATTRIBUTE15')
1602 THEN
1603 p_attribute.attribute15 := p_value;
1604 ELSIF (p_segment = 'ATTRIBUTE16')
1605 THEN
1606 p_attribute.attribute16 := p_value;
1607 ELSIF (p_segment = 'ATTRIBUTE17')
1608 THEN
1609 p_attribute.attribute17 := p_value;
1610 ELSIF (p_segment = 'ATTRIBUTE18')
1611 THEN
1612 p_attribute.attribute18 := p_value;
1613 ELSIF (p_segment = 'ATTRIBUTE19')
1614 THEN
1615 p_attribute.attribute19 := p_value;
1616 ELSIF (p_segment = 'ATTRIBUTE20')
1617 THEN
1618 p_attribute.attribute20 := p_value;
1619 ELSIF (p_segment = 'ATTRIBUTE21')
1620 THEN
1621 p_attribute.attribute21 := p_value;
1622 ELSIF (p_segment = 'ATTRIBUTE22')
1623 THEN
1624 p_attribute.attribute22 := p_value;
1625 ELSIF (p_segment = 'ATTRIBUTE23')
1626 THEN
1627 p_attribute.attribute23 := p_value;
1628 ELSIF (p_segment = 'ATTRIBUTE24')
1629 THEN
1630 p_attribute.attribute24 := p_value;
1631 ELSIF (p_segment = 'ATTRIBUTE25')
1632 THEN
1633 p_attribute.attribute25 := p_value;
1634 ELSIF (p_segment = 'ATTRIBUTE26')
1635 THEN
1636 p_attribute.attribute26 := p_value;
1637 ELSIF (p_segment = 'ATTRIBUTE27')
1638 THEN
1639 p_attribute.attribute27 := p_value;
1640 ELSIF (p_segment = 'ATTRIBUTE28')
1641 THEN
1642 p_attribute.attribute28 := p_value;
1643 ELSIF (p_segment = 'ATTRIBUTE29')
1644 THEN
1645 p_attribute.attribute29 := p_value;
1646 ELSIF (p_segment = 'ATTRIBUTE30')
1647 THEN
1648 p_attribute.attribute30 := p_value;
1649 ELSIF (p_segment = 'ATTRIBUTE_CATEGORY')
1650 THEN
1651 p_attribute.attribute_category := p_value;
1652 END IF;
1653
1654 IF (p_changed = hxc_timecard.c_yes)
1655 THEN
1656 p_attribute.changed := hxc_timecard.c_yes;
1657 p_attribute.process := hxc_timecard.c_yes;
1658 END IF;
1659 END set_new_attribute_value;
1660 -----------------------------------------------------------------------------
1661 -- Bug 11896973
1662 -- Type: Function
1663 -- Scope: Private
1664 -- Name: check_global_context
1665 -- INOUT Parameters: p_context_prefix -> VARCHAR2
1666 --
1667 -- Description: This function will verify whether 'PAEXPITDFF - GLOBAL'
1668 -- exists as a context or not.
1669 -------------------------------------------------------------------------------
1670 FUNCTION check_global_context(
1671 p_context_prefix in VARCHAR2) return boolean is
1672 l_dummy VARCHAR2(10);
1673
1674 BEGIN
1675 select 'Y' into l_dummy
1676 from fnd_descr_flex_contexts
1677 where application_id = 809
1678 and descriptive_flexfield_name = 'OTC Information Types'
1679 and enabled_flag = 'Y'
1680 and descriptive_flex_context_code like p_context_prefix||'%GLOBAL%';
1681 return true;
1682
1683 EXCEPTION
1684 WHEN no_data_found then
1685 return false;
1686
1687 END;
1688
1689 -----------------------------------------------------------------------------
1690 -- Bug 11896973
1691 -- Type: Procedure
1692 -- Scope: Private
1693 -- Name: get_dff_attribute_category
1694 -- INOUT Parameters: p_app_attributes -> TBB Table Type
1695 --
1696 -- Description: This procedure will retrieve attribute category for
1697 -- additional detail DFF.
1698 --
1699 -----------------------------------------------------------------------------
1700
1701 PROCEDURE get_dff_attribute_category (
1702 p_app_attributes IN OUT NOCOPY hxc_self_service_time_deposit.app_attributes_info,
1703 p_attribute_category IN OUT NOCOPY hxc_bld_blk_info_types.bld_blk_info_type%TYPE)
1704 IS
1705 CURSOR c_reference_field IS
1706 SELECT d.default_context_field_name
1707 FROM fnd_descriptive_flexs d, fnd_application a, fnd_product_installations z
1708 WHERE d.application_id = a.application_id
1709 AND z.application_id = a.application_id
1710 AND a.application_short_name = 'PA'
1711 AND z.status = 'I'
1712 AND d.descriptive_flexfield_name = 'PA_EXPENDITURE_ITEMS_DESC_FLEX';
1713
1714 l_system_linkage varchar2(240) := '';
1715 l_expenditure_type varchar2(240) := '';
1716 l_index number;
1717
1718 BEGIN
1719 l_index := p_app_attributes.FIRST;
1720 LOOP
1721 EXIT WHEN NOT p_app_attributes.EXISTS (l_index);
1722 IF (p_app_attributes (l_index).CATEGORY = 'PROJECTS')
1723 THEN
1724 IF p_app_attributes (l_index).attribute_name = 'SYSTEM_LINKAGE_FUNCTION'
1725 THEN
1726 l_system_linkage := p_app_attributes (l_index).attribute_value;
1727 END IF;
1728 IF p_app_attributes (l_index).attribute_name = 'Expenditure_Type'
1729 THEN
1730 l_expenditure_type := p_app_attributes (l_index).attribute_value;
1731 END IF;
1732 END IF;
1733 l_index := p_app_attributes.NEXT(l_index);
1734 END LOOP;
1735
1736
1737 IF (g_reference_field = '') OR (g_reference_field is NULL)
1738 THEN
1739 open c_reference_field;
1740 fetch c_reference_field into g_reference_field;
1741 close c_reference_field;
1742 END IF;
1743
1744 IF (g_reference_field = 'SYSTEM_LINKAGE_FUNCTION')
1745 THEN
1746 IF (l_system_linkage <> 'NO_PROJECTS')
1747 THEN
1748 IF g_dupdff_code.EXISTS(l_system_linkage)
1749 THEN
1750 p_attribute_category := g_dupdff_code(l_system_linkage);
1751
1752 ELSE
1753 p_attribute_category := HXC_DEPOSIT_WRAPPER_UTILITIES.get_dupdff_code('PAEXPITDFF - ' || l_system_linkage);
1754 g_dupdff_code(l_system_linkage) := p_attribute_category;
1755
1756 END IF;
1757 END IF;
1758
1759 ELSIF (g_reference_field = 'EXPENDITURE_TYPE')
1760 THEN
1761 IF (l_expenditure_type <> 'NO_PROJECTS')
1762 THEN
1763 IF g_dupdff_code.EXISTS(l_expenditure_type)
1764 THEN
1765 p_attribute_category := g_dupdff_code(l_expenditure_type);
1766
1767 ELSE
1768 p_attribute_category := HXC_DEPOSIT_WRAPPER_UTILITIES.get_dupdff_code('PAEXPITDFF - ' || l_expenditure_type);
1769 g_dupdff_code(l_expenditure_type) := p_attribute_category;
1770
1771 END IF;
1772 END IF;
1773
1774 ELSIF ((g_reference_field = '') OR (g_reference_field is NULL))
1775 THEN
1776 IF (check_global_context('PAEXPITDFF'))
1777 THEN
1778 p_attribute_category := 'PAEXPITDFF - GLOBAL';
1779 END IF;
1780 END IF;
1781
1782 END get_dff_attribute_category;
1783
1784
1785 PROCEDURE create_new_attribute (
1786 p_attributes IN OUT NOCOPY hxc_attribute_table_type,
1787 p_app_attributes IN OUT NOCOPY hxc_self_service_time_deposit.app_attributes_info,
1788 p_app_index IN NUMBER
1789 )
1790 IS
1791 l_new_time_attribute_id NUMBER;
1792 l_new_attribute hxc_attribute_type;
1793 l_index NUMBER;
1794 l_attribute_category hxc_bld_blk_info_types.bld_blk_info_type%TYPE;
1795 l_new VARCHAR2 (1);
1796 BEGIN
1797 IF (INSTR (p_app_attributes (p_app_index).bld_blk_info_type, 'Dummy') <
1798 1
1799 )
1800 THEN
1801 l_attribute_category :=
1802 SUBSTR (p_app_attributes (p_app_index).bld_blk_info_type, 1, 30);
1803 ELSE
1804 -- Bug 11896973 -- Added new method to handle addtional dff attributes
1805 IF (p_app_attributes (p_app_index).bld_blk_info_type = 'Dummy Paexpitdff Context')
1806 THEN
1807 get_dff_attribute_category(p_app_attributes,l_attribute_category);
1808
1809 ELSE
1810 l_attribute_category := NULL;
1811 END IF;
1812 END IF;
1813 IF (p_app_attributes (p_app_index).attribute_index IS NULL)
1814 THEN
1815 l_new := hxc_timecard.c_yes;
1816 ELSE
1817 l_new := hxc_timecard.c_no;
1818 END IF;
1819
1820 l_new_attribute :=
1821 hxc_attribute_type
1822 (p_app_attributes (p_app_index).time_attribute_id,
1823 p_app_attributes (p_app_index).building_block_id,
1824 l_attribute_category,
1825 NULL,
1826 NULL,
1827 NULL,
1828 NULL,
1829 NULL,
1830 NULL,
1831 NULL,
1832 NULL,
1833 NULL,
1834 NULL,
1835 NULL,
1836 NULL,
1837 NULL,
1838 NULL,
1839 NULL,
1840 NULL,
1841 NULL,
1842 NULL,
1843 NULL,
1844 NULL,
1845 NULL,
1846 NULL,
1847 NULL,
1848 NULL,
1849 NULL,
1850 NULL,
1851 NULL,
1852 NULL,
1853 NULL,
1854 NULL,
1855 hxc_timecard_attribute_utils.get_bld_blk_info_type_id
1856 (p_app_attributes (p_app_index).bld_blk_info_type
1857 ),
1858 1,
1859 l_new,
1860 hxc_timecard.c_no,
1861 -- Changed: We will decide later if this is a changed Attribute
1862 p_app_attributes (p_app_index).bld_blk_info_type,
1863 hxc_timecard.c_no,
1864 -- Process: We will decide later if this Attribute needs to get processed
1865 NULL
1866 );
1867 l_index := p_app_attributes.FIRST;
1868
1869 LOOP
1870 EXIT WHEN NOT p_app_attributes.EXISTS (l_index);
1871
1872 IF ( (p_app_attributes (l_index).bld_blk_info_type =
1873 p_app_attributes (p_app_index).bld_blk_info_type
1874 )
1875 AND (p_app_attributes (l_index).time_attribute_id =
1876 p_app_attributes (p_app_index).time_attribute_id
1877 )
1878 )
1879 THEN
1880 set_new_attribute_value
1881 (l_new_attribute,
1882 p_app_attributes (l_index).SEGMENT,
1883 p_app_attributes (l_index).attribute_value,
1884 p_app_attributes (l_index).changed
1885 );
1886 p_app_attributes (l_index).updated := 'Y';
1887 END IF;
1888
1889 l_index := p_app_attributes.NEXT (l_index);
1890 END LOOP;
1891
1892 p_attributes.EXTEND ();
1893 p_attributes (p_attributes.LAST) := l_new_attribute;
1894 END create_new_attribute;
1895
1896 PROCEDURE convert_app_attributes_to_type (
1897 p_attributes IN OUT NOCOPY hxc_attribute_table_type,
1898 p_app_attributes IN OUT NOCOPY hxc_self_service_time_deposit.app_attributes_info
1899 )
1900 IS
1901 l_index NUMBER;
1902 BEGIN
1903 l_index := p_app_attributes.FIRST;
1904
1905 LOOP
1906 EXIT WHEN NOT p_app_attributes.EXISTS (l_index);
1907
1908 IF (NVL (p_app_attributes (l_index).updated, 'N') = 'N')
1909 THEN
1910 create_new_attribute (p_attributes => p_attributes,
1911 p_app_attributes => p_app_attributes,
1912 p_app_index => l_index
1913 );
1914 p_app_attributes (l_index).updated := 'Y';
1915 END IF;
1916
1917 l_index := p_app_attributes.NEXT (l_index);
1918 END LOOP;
1919 END convert_app_attributes_to_type;
1920
1921 -----------------------------------------------------------------------------
1922 -- Type: Function
1923 -- Scope: Public
1924 -- Name: convert_to_dpwr_messages
1925 -- Returns: hxc_self_service_time_deposit.message_table
1926 -- IN Parameters: p_messages -> The PL/SQL table structure you want to convert
1927 --
1928 -- Description: Private Function that will convert the new TYPE to the old
1929 -- PL/SQL message structure that is then returned.
1930 --
1931 -----------------------------------------------------------------------------
1932 FUNCTION convert_to_dpwr_messages (p_messages IN hxc_message_table_type)
1933 RETURN hxc_self_service_time_deposit.message_table
1934 IS
1935 l_proc VARCHAR2 (72);
1936 l_messages hxc_self_service_time_deposit.message_table;
1937 l_index NUMBER;
1938 BEGIN
1939 g_debug := hr_utility.debug_enabled;
1940
1941 IF g_debug
1942 THEN
1943 l_proc := g_package || 'convert_to_dpwr_messages';
1944 hr_utility.set_location ('Entering:' || l_proc, 10);
1945 END IF;
1946
1947 l_index := p_messages.FIRST;
1948
1949 LOOP
1950 EXIT WHEN NOT p_messages.EXISTS (l_index);
1951 l_messages (l_index).message_name :=
1952 p_messages (l_index).message_name;
1953 l_messages (l_index).message_level :=
1954 p_messages (l_index).message_level;
1955 l_messages (l_index).message_field :=
1956 p_messages (l_index).message_field;
1957 l_messages (l_index).message_tokens :=
1958 p_messages (l_index).message_tokens;
1959 l_messages (l_index).application_short_name :=
1960 p_messages (l_index).application_short_name;
1961 l_messages (l_index).time_building_block_id :=
1962 p_messages (l_index).time_building_block_id;
1963 l_messages (l_index).time_building_block_ovn :=
1964 p_messages (l_index).time_building_block_ovn;
1965 l_messages (l_index).time_attribute_id :=
1966 p_messages (l_index).time_attribute_id;
1967 l_messages (l_index).time_attribute_ovn :=
1968 p_messages (l_index).time_attribute_ovn;
1969 l_index := p_messages.NEXT (l_index);
1970 END LOOP;
1971
1972 IF g_debug
1973 THEN
1974 hr_utility.set_location ( ' returning l_messages.count = '
1975 || l_messages.COUNT,
1976 20
1977 );
1978 hr_utility.set_location ('Leaving:' || l_proc, 30);
1979 END IF;
1980
1981 RETURN l_messages;
1982 END convert_to_dpwr_messages;
1983
1984 -----------------------------------------------------------------------------
1985 -- Type: Function
1986 -- Scope: Public
1987 -- Name: convert_msg_to_type
1988 -- Returns: hxc_message_table_type
1989 -- IN Parameters: p_messages -> The PL/SQL msg structure you want to convert
1990 --
1991 -- Description: Private Function that will convert the old PL/SQL message
1992 -- structure to the new TYPE that is then returned.
1993 --
1994 -----------------------------------------------------------------------------
1995 FUNCTION convert_msg_to_type (
1996 p_messages IN hxc_self_service_time_deposit.message_table
1997 )
1998 RETURN hxc_message_table_type
1999 IS
2000 l_proc VARCHAR2 (72);
2001 l_messages hxc_message_table_type;
2002 l_index PLS_INTEGER;
2003 BEGIN
2004 IF g_debug
2005 THEN
2006 l_proc := g_package || 'convert_msg_to_type';
2007 hr_utility.set_location ('Entering:' || l_proc, 10);
2008 END IF;
2009
2010 -- Initialize the collection
2011 l_messages := hxc_message_table_type ();
2012 l_index := p_messages.FIRST;
2013
2014 LOOP
2015 EXIT WHEN NOT p_messages.EXISTS (l_index);
2016 l_messages.EXTEND;
2017 l_messages (l_messages.LAST) :=
2018 hxc_message_type (p_messages (l_index).message_name,
2019 p_messages (l_index).message_level,
2020 p_messages (l_index).message_field,
2021 p_messages (l_index).message_tokens,
2022 p_messages (l_index).application_short_name,
2023 p_messages (l_index).time_building_block_id,
2024 p_messages (l_index).time_building_block_ovn,
2025 p_messages (l_index).time_attribute_id,
2026 p_messages (l_index).time_attribute_ovn,
2027 p_messages (l_index).message_extent
2028 );
2029 l_index := p_messages.NEXT (l_index);
2030 END LOOP;
2031
2032 IF g_debug
2033 THEN
2034 hr_utility.set_location ( ' returning l_messages.count = '
2035 || l_messages.COUNT,
2036 20
2037 );
2038 hr_utility.set_location ('Leaving: ' || l_proc, 30);
2039 END IF;
2040
2041 RETURN l_messages;
2042 END convert_msg_to_type;
2043
2044 -----------------------------------------------------------------------------
2045 -- Type: Function
2046 -- Scope: Public
2047 -- Name: get_approval_status
2048 -- Returns: VARCHAR2
2049 -- IN Parameters: p_mode -> The mode you want to find the approval status for
2050 --
2051 -- Description: Private Function that will return the approval status that
2052 -- matches a mode
2053 -- The approval status is linked (hard coded here) to the mode
2054 -- For P_MODE 'SAVE' APPROVAL_STATUS = 'WORKING'
2055 -- For P_MODE 'SUBMIT' APPROVAL_STATUS = 'SUBMITTED'
2056 -- For P_MODE 'FORCE_SAVE' APPROVAL_STATUS = 'WORKING'
2057 -- For P_MODE 'FORCE_SUBMIT' APPROVAL_STATUS = 'SUBMITTED'
2058 -- For P_MODE 'AUDIT' APPROVAL_STATUS = 'SUBMITTED'
2059 -- For P_MODE 'MIGRATION' APPROVAL_STATUS = 'SUBMITTED'
2060 -----------------------------------------------------------------------------
2061 FUNCTION get_approval_status (p_mode IN VARCHAR2)
2062 RETURN VARCHAR2
2063 IS
2064 l_proc VARCHAR2 (72);
2065 l_appr_stat VARCHAR2 (30);
2066 BEGIN
2067 g_debug := hr_utility.debug_enabled;
2068
2069 IF g_debug
2070 THEN
2071 l_proc := g_package || 'get_approval_status';
2072 hr_utility.set_location ('Entering:' || l_proc, 10);
2073 END IF;
2074
2075 IF p_mode = hxc_timecard.c_save
2076 THEN
2077 l_appr_stat := hxc_timecard.c_working_status;
2078 ELSIF p_mode = hxc_timecard.c_submit
2079 THEN
2080 l_appr_stat := hxc_timecard.c_submitted_status;
2081 ELSIF p_mode = hxc_timestore_deposit.c_tk_save
2082 THEN
2083 l_appr_stat := hxc_timecard.c_working_status;
2084 ELSIF p_mode = hxc_timestore_deposit.c_tk_submit
2085 THEN
2086 l_appr_stat := hxc_timecard.c_submitted_status;
2087 ELSIF p_mode = hxc_timecard.c_audit
2088 THEN
2089 l_appr_stat := hxc_timecard.c_submitted_status;
2090 ELSIF p_mode = hxc_timestore_deposit.c_migration
2091 THEN
2092 l_appr_stat := hxc_timecard.c_submitted_status;
2093 ELSE -- if no mode was supplied (or the wrong one), try working
2094 l_appr_stat := hxc_timecard.c_working_status;
2095 END IF;
2096
2097 IF g_debug
2098 THEN
2099 hr_utility.set_location ( ' returning approval_status = '
2100 || l_appr_stat,
2101 20
2102 );
2103 hr_utility.set_location ('Leaving:' || l_proc, 30);
2104 END IF;
2105
2106 RETURN l_appr_stat;
2107 END get_approval_status;
2108
2109 -----------------------------------------------------------------------------
2110 -- Type: Procedure
2111 -- Scope: Public
2112 -- Name: find_parent_building_block
2113 -- IN Parameters: p_start_time -> Start Time of the TBB
2114 -- p_resource_id -> Resource ID (Person ID)
2115 -- p_resource_type -> Defaults to 'PERSON', use the default
2116 -- p_scope -> 'DAY' or 'DETAIL' (TIMECARD TBBs do not
2117 -- have parents)
2118 -- p_app_blocks -> The table type already holding a timecard
2119 -- This might be empty
2120 -- OUT Parameters: p_timecard_bb_id -> id of the parent TBB id
2121 -- p_timecard_ovn -> ovn of the parent TBB id
2122 --
2123 -- Description: By passing in the details of a TBB (p_start_time,
2124 -- p_resource_id), this procedure will
2125 -- work out what the parent for that TBB should be. We can do
2126 -- this, because for a certain time and resource id, there
2127 -- can only be one parent TBB to which this TBB can be added.
2128 --
2129 -- If a DAY TBB is passed in, this procedure will work out
2130 -- to which TIMECARD TBB this DAY TBB needs to be attached,
2131 -- if a DETAIL TBB is passed in, this procedure will work out
2132 -- to which DAY TBB this DETAIL TBB needs to be attached.
2133 --
2134 -- The procedure will first scan the PL/SQL timecard table to
2135 -- see if it can find the TBB in there. If this table is empty
2136 -- it will look in the DB.
2137 --
2138 -- Exceptions: No Timecard found
2139 -- Wrong Timecard in PL/SQL Table
2140 -----------------------------------------------------------------------------
2141 PROCEDURE find_parent_building_block (
2142 p_start_time IN hxc_time_building_blocks.start_time%TYPE,
2143 p_resource_id IN hxc_time_building_blocks.resource_id%TYPE,
2144 p_resource_type IN hxc_time_building_blocks.resource_type%TYPE,
2145 p_scope IN hxc_time_building_blocks.SCOPE%TYPE,
2146 p_app_blocks IN hxc_block_table_type,
2147 p_timecard_bb_id OUT NOCOPY hxc_time_building_blocks.time_building_block_id%TYPE,
2148 p_timecard_ovn OUT NOCOPY hxc_time_building_blocks.object_version_number%TYPE
2149 )
2150 IS
2151 l_proc VARCHAR2 (72);
2152
2153 -- This cursor will return the BB_ID and OVN of the current TBB.
2154 CURSOR csr_get_timecard_id (
2155 v_start_time hxc_time_building_blocks.start_time%TYPE,
2156 v_resource_id hxc_time_building_blocks.resource_id%TYPE,
2157 v_resource_type hxc_time_building_blocks.resource_type%TYPE,
2158 v_scope hxc_time_building_blocks.SCOPE%TYPE
2159 )
2160 IS
2161 SELECT tbb.time_building_block_id,
2162 MAX (tbb.object_version_number)
2163 FROM hxc_time_building_blocks tbb, hxc_time_building_blocks parent_tbb
2164 WHERE tbb.SCOPE = v_scope
2165 AND tbb.resource_type = v_resource_type
2166 AND tbb.resource_id = v_resource_id
2167 AND v_start_time BETWEEN tbb.start_time AND tbb.stop_time
2168 AND tbb.date_to = hr_general.end_of_time
2169 AND parent_tbb.time_building_block_id = tbb.parent_building_block_id
2170 AND parent_tbb.object_version_number = tbb.parent_building_block_ovn
2171 AND parent_tbb.date_to = hr_general.end_of_time
2172 AND parent_tbb.SCOPE <> hxc_timecard.c_template_scope
2173 GROUP BY tbb.time_building_block_id;
2174
2175 l_building_block_count PLS_INTEGER;
2176 l_index PLS_INTEGER;
2177 BEGIN
2178 g_debug := hr_utility.debug_enabled;
2179
2180 IF g_debug
2181 THEN
2182 l_proc := g_package || 'find_parent_building_block';
2183 hr_utility.set_location ('Entering:' || l_proc, 10);
2184 END IF;
2185
2186 -- If the PL/SQL structure is not empty, we assume the user wants to add
2187 -- this DAY TBB to the TIMECARD present in the PL/SQL table so first look
2188 -- there
2189 IF (p_app_blocks.COUNT <> 0)
2190 THEN -- Look for TIMECARD in PL/SQL structure
2191 p_timecard_bb_id := NULL; -- also used to exit loop
2192 l_building_block_count := p_app_blocks.FIRST;
2193
2194 -- loop over all TBB in the PL/SQL table and see if we find a match
2195 LOOP
2196 EXIT WHEN (NOT p_app_blocks.EXISTS (l_building_block_count))
2197 OR (p_timecard_bb_id IS NOT NULL);
2198
2199 IF (p_start_time
2200 BETWEEN fnd_date.canonical_to_date
2201 (p_app_blocks (l_building_block_count).start_time
2202 )
2203 AND fnd_date.canonical_to_date
2204 (p_app_blocks (l_building_block_count).stop_time
2205 )
2206 )
2207 AND (p_resource_type =
2208 p_app_blocks (l_building_block_count).resource_type
2209 )
2210 AND (p_resource_id =
2211 p_app_blocks (l_building_block_count).resource_id
2212 )
2213 AND (p_app_blocks (l_building_block_count).SCOPE = p_scope)
2214 THEN
2215 IF g_debug
2216 THEN
2217 hr_utility.set_location
2218 ( ' Found TBB in PL/SQL Table, ID = '
2219 || p_app_blocks (l_building_block_count).time_building_block_id,
2220 20
2221 );
2222 END IF;
2223
2224 -- found BB, set the ID and OVN
2225 p_timecard_bb_id :=
2226 p_app_blocks (l_building_block_count).time_building_block_id;
2227 p_timecard_ovn :=
2228 p_app_blocks (l_building_block_count).object_version_number;
2229 END IF;
2230
2231 -- Next
2232 l_building_block_count :=
2233 p_app_blocks.NEXT (l_building_block_count);
2234 END LOOP;
2235
2236 IF l_index IS NULL
2237 THEN -- we never found the BB : ERROR
2238 NULL;
2239 END IF;
2240 ELSE -- Look for TIMECARD in DB
2241 OPEN csr_get_timecard_id (p_start_time,
2242 p_resource_id,
2243 p_resource_type,
2244 p_scope
2245 );
2246
2247 FETCH csr_get_timecard_id
2248 INTO p_timecard_bb_id, p_timecard_ovn;
2249
2250 IF g_debug
2251 THEN
2252 hr_utility.set_location ( ' Found TBB in DB, ID = '
2253 || p_timecard_bb_id,
2254 30
2255 );
2256 END IF;
2257
2258 CLOSE csr_get_timecard_id;
2259 END IF; -- (p_app_blocks.COUNT <> 0)
2260
2261 IF g_debug
2262 THEN
2263 hr_utility.set_location ('Leaving:' || l_proc, 40);
2264 END IF;
2265 END find_parent_building_block;
2266
2267 -----------------------------------------------------------------------------
2268 -- Type: Procedure
2269 -- Scope: Public
2270 -- Name: set_new_change_flags
2271 -- IN OUT Parameters: p_attributes -> The PL/SQL attribute table type you want
2272 -- to update the changed and new flags for.
2273 --
2274 -- Description: Public Procedure that will set the changed and new flags for
2275 -- the attribute structure passed in. Unfortunately, when we
2276 -- retrieve the attributes from the database using the attribute
2277 -- utilities, these flags are not set correctly so we need to
2278 -- addjust them, we can do that with this function.
2279 -----------------------------------------------------------------------------
2280 PROCEDURE set_new_change_flags (
2281 p_attributes IN OUT NOCOPY hxc_attribute_table_type
2282 )
2283 IS
2284 l_proc VARCHAR2 (72);
2285 l_index PLS_INTEGER;
2286
2287 CURSOR find_attribute (
2288 p_attr_id hxc_time_attributes.time_attribute_id%TYPE
2289 )
2290 IS
2291 SELECT time_attribute_id, attribute_category, attribute1,
2292 attribute2, attribute3, attribute4, attribute5, attribute6,
2293 attribute7, attribute8, attribute9, attribute10,
2294 attribute11, attribute12, attribute13, attribute14,
2295 attribute15, attribute16, attribute17, attribute18,
2296 attribute19, attribute20, attribute21, attribute22,
2297 attribute23, attribute24, attribute25, attribute26,
2298 attribute27, attribute28, attribute29, attribute30,
2299 MAX (object_version_number)
2300 FROM hxc_time_attributes
2301 WHERE time_attribute_id = p_attr_id
2302 GROUP BY time_attribute_id,
2303 attribute_category,
2304 attribute1,
2305 attribute2,
2306 attribute3,
2307 attribute4,
2308 attribute5,
2309 attribute6,
2310 attribute7,
2311 attribute8,
2312 attribute9,
2313 attribute10,
2314 attribute11,
2315 attribute12,
2316 attribute13,
2317 attribute14,
2318 attribute15,
2319 attribute16,
2320 attribute17,
2321 attribute18,
2322 attribute19,
2323 attribute20,
2324 attribute21,
2325 attribute22,
2326 attribute23,
2327 attribute24,
2328 attribute25,
2329 attribute26,
2330 attribute27,
2331 attribute28,
2332 attribute29,
2333 attribute30;
2334
2335 l_attribute_rec find_attribute%ROWTYPE;
2336 c_null CONSTANT VARCHAR2 (5) := '@@@@@';
2337 BEGIN
2338 g_debug := hr_utility.debug_enabled;
2339
2340 IF g_debug
2341 THEN
2342 l_proc := g_package || 'set_new_change_flags';
2343 hr_utility.set_location ('Entering:' || l_proc, 10);
2344 END IF;
2345
2346 l_index := p_attributes.FIRST;
2347
2348 LOOP
2349 EXIT WHEN NOT p_attributes.EXISTS (l_index);
2350
2351 OPEN find_attribute (p_attributes (l_index).time_attribute_id);
2352
2353 FETCH find_attribute
2354 INTO l_attribute_rec;
2355
2356 IF find_attribute%FOUND
2357 THEN
2358 IF g_debug
2359 THEN
2360 hr_utility.set_location
2361 ( ' - Attribute '
2362 || p_attributes (l_index).time_attribute_id
2363 || ' Found',
2364 20
2365 );
2366 END IF;
2367
2368 p_attributes (l_index).NEW := 'N';
2369
2370 IF ( NVL (l_attribute_rec.attribute1, c_null) =
2371 NVL (p_attributes (l_index).attribute1, c_null)
2372 AND NVL (l_attribute_rec.attribute2, c_null) =
2373 NVL (p_attributes (l_index).attribute2, c_null)
2374 AND NVL (l_attribute_rec.attribute3, c_null) =
2375 NVL (p_attributes (l_index).attribute3, c_null)
2376 AND NVL (l_attribute_rec.attribute4, c_null) =
2377 NVL (p_attributes (l_index).attribute4, c_null)
2378 AND NVL (l_attribute_rec.attribute5, c_null) =
2379 NVL (p_attributes (l_index).attribute5, c_null)
2380 AND NVL (l_attribute_rec.attribute6, c_null) =
2381 NVL (p_attributes (l_index).attribute6, c_null)
2382 AND NVL (l_attribute_rec.attribute7, c_null) =
2383 NVL (p_attributes (l_index).attribute7, c_null)
2384 AND NVL (l_attribute_rec.attribute8, c_null) =
2385 NVL (p_attributes (l_index).attribute8, c_null)
2386 AND NVL (l_attribute_rec.attribute9, c_null) =
2387 NVL (p_attributes (l_index).attribute9, c_null)
2388 AND NVL (l_attribute_rec.attribute10, c_null) =
2389 NVL (p_attributes (l_index).attribute10, c_null)
2390 AND NVL (l_attribute_rec.attribute11, c_null) =
2391 NVL (p_attributes (l_index).attribute11, c_null)
2392 AND NVL (l_attribute_rec.attribute12, c_null) =
2393 NVL (p_attributes (l_index).attribute12, c_null)
2394 AND NVL (l_attribute_rec.attribute13, c_null) =
2395 NVL (p_attributes (l_index).attribute13, c_null)
2396 AND NVL (l_attribute_rec.attribute14, c_null) =
2397 NVL (p_attributes (l_index).attribute14, c_null)
2398 AND NVL (l_attribute_rec.attribute15, c_null) =
2399 NVL (p_attributes (l_index).attribute15, c_null)
2400 AND NVL (l_attribute_rec.attribute16, c_null) =
2401 NVL (p_attributes (l_index).attribute16, c_null)
2402 AND NVL (l_attribute_rec.attribute17, c_null) =
2403 NVL (p_attributes (l_index).attribute17, c_null)
2404 AND NVL (l_attribute_rec.attribute18, c_null) =
2405 NVL (p_attributes (l_index).attribute18, c_null)
2406 AND NVL (l_attribute_rec.attribute19, c_null) =
2407 NVL (p_attributes (l_index).attribute19, c_null)
2408 AND NVL (l_attribute_rec.attribute20, c_null) =
2409 NVL (p_attributes (l_index).attribute20, c_null)
2410 AND NVL (l_attribute_rec.attribute21, c_null) =
2411 NVL (p_attributes (l_index).attribute21, c_null)
2412 AND NVL (l_attribute_rec.attribute22, c_null) =
2413 NVL (p_attributes (l_index).attribute22, c_null)
2414 AND NVL (l_attribute_rec.attribute23, c_null) =
2415 NVL (p_attributes (l_index).attribute23, c_null)
2416 AND NVL (l_attribute_rec.attribute24, c_null) =
2417 NVL (p_attributes (l_index).attribute24, c_null)
2418 AND NVL (l_attribute_rec.attribute25, c_null) =
2419 NVL (p_attributes (l_index).attribute25, c_null)
2420 AND NVL (l_attribute_rec.attribute26, c_null) =
2421 NVL (p_attributes (l_index).attribute26, c_null)
2422 AND NVL (l_attribute_rec.attribute27, c_null) =
2423 NVL (p_attributes (l_index).attribute27, c_null)
2424 AND NVL (l_attribute_rec.attribute28, c_null) =
2425 NVL (p_attributes (l_index).attribute28, c_null)
2426 AND NVL (l_attribute_rec.attribute29, c_null) =
2427 NVL (p_attributes (l_index).attribute29, c_null)
2428 AND NVL (l_attribute_rec.attribute30, c_null) =
2429 NVL (p_attributes (l_index).attribute30, c_null)
2430 )
2431 THEN
2432 IF g_debug
2433 THEN
2434 hr_utility.set_location
2435 ( ' - Attribute '
2436 || p_attributes (l_index).time_attribute_id
2437 || ' Not Changed',
2438 40
2439 );
2440 END IF;
2441
2442 p_attributes (l_index).changed := 'N';
2443 p_attributes (l_index).process := 'N';
2444 ELSE
2445 IF g_debug
2446 THEN
2447 hr_utility.set_location
2448 ( ' - Attribute '
2449 || p_attributes (l_index).time_attribute_id
2450 || ' Changed',
2451 50
2452 );
2453 END IF;
2454
2455 p_attributes (l_index).changed := 'Y';
2456 p_attributes (l_index).process := 'Y';
2457 END IF;
2458 ELSE
2459 IF g_debug
2460 THEN
2461 hr_utility.set_location
2462 ( ' - Attribute '
2463 || p_attributes (l_index).time_attribute_id
2464 || ' Not Found',
2465 60
2466 );
2467 END IF;
2468
2469 p_attributes (l_index).NEW := 'Y';
2470 p_attributes (l_index).changed := 'N';
2471 p_attributes (l_index).process := 'Y';
2472 END IF;
2473
2474 CLOSE find_attribute;
2475
2476 l_index := p_attributes.NEXT (l_index);
2477 END LOOP;
2478
2479 IF g_debug
2480 THEN
2481 hr_utility.set_location ('Leaving: ' || l_proc, 70);
2482 END IF;
2483 END set_new_change_flags;
2484
2485 -----------------------------------------------------------------------------
2486 -- Type: Procedure
2487 -- Scope: Public
2488 -- Name: find_parent_building_block
2489 -- IN Parameters: p_start_time -> Start Time of the TBB
2490 -- p_resource_id -> Resource ID (Person ID)
2491 -- p_resource_type -> Defaults to 'PERSON', use the default
2492 -- p_scope -> 'DAY' or 'DETAIL' (TIMECARD TBBs do not
2493 -- have parents)
2494 -- p_app_blocks -> The PL/SQL table already holding a timecard
2495 -- This might be empty
2496 -- OUT Parameters: p_timecard_bb_id -> id of the parent TBB id
2497 -- p_timecard_ovn -> ovn of the parent TBB id
2498 --
2499 -- Description: By passing in the details of a TBB (p_start_time,
2500 -- p_resource_id), this procedure will
2501 -- work out what the parent for that TBB should be. We can do
2502 -- this, because for a certain time and resource id, there
2503 -- can only be one parent TBB to which this TBB can be added.
2504 --
2505 -- If a DAY TBB is passed in, this procedure will work out
2506 -- to which TIMECARD TBB this DAY TBB needs to be attached,
2507 -- if a DETAIL TBB is passed in, this procedure will work out
2508 -- to which DAY TBB this DETAIL TBB needs to be attached.
2509 --
2510 -- The procedure will first scan the PL/SQL timecard table to
2511 -- see if it can find the TBB in there. If this table is empty
2512 -- it will look in the DB.
2513 --
2514 -- Exceptions: No Timecard found
2515 -- Wrong Timecard in PL/SQL Table
2516 -----------------------------------------------------------------------------
2517 PROCEDURE find_parent_building_block (
2518 p_start_time IN hxc_time_building_blocks.start_time%TYPE,
2519 p_resource_id IN hxc_time_building_blocks.resource_id%TYPE,
2520 p_resource_type IN hxc_time_building_blocks.resource_type%TYPE,
2521 p_scope IN hxc_time_building_blocks.SCOPE%TYPE,
2522 p_app_blocks IN hxc_self_service_time_deposit.timecard_info,
2523 p_timecard_bb_id OUT NOCOPY hxc_time_building_blocks.time_building_block_id%TYPE,
2524 p_timecard_ovn OUT NOCOPY hxc_time_building_blocks.object_version_number%TYPE
2525 )
2526 IS
2527 l_proc VARCHAR2 (72);
2528 l_blocks hxc_block_table_type;
2529 BEGIN
2530 g_debug := hr_utility.debug_enabled;
2531
2532 IF g_debug
2533 THEN
2534 l_proc := g_package || 'find_parent_building_block (Overload)';
2535 hr_utility.set_location ('Entering:' || l_proc, 10);
2536 END IF;
2537
2538 l_blocks := convert_tbb_to_type (p_blocks => p_app_blocks);
2539 find_parent_building_block (p_start_time => p_start_time,
2540 p_resource_id => p_resource_id,
2541 p_resource_type => p_resource_type,
2542 p_scope => p_scope,
2543 p_app_blocks => l_blocks,
2544 p_timecard_bb_id => p_timecard_bb_id,
2545 p_timecard_ovn => p_timecard_ovn
2546 );
2547
2548 /* hxc_timecard.convert_to_type
2549 (p_attributes => in HXC_SELF_SERVICE_TIME_DEPOSIT.building_block_attribute_info)
2550 return HXC_ATTRIBUTE_TABLE_TYPE is
2551
2552 l_attributes HXC_ATTRIBUTE_TABLE_TYPE;
2553 l_index NUMBER;
2554
2555 */
2556 IF g_debug
2557 THEN
2558 hr_utility.set_location ('Leaving:' || l_proc, 40);
2559 END IF;
2560 END find_parent_building_block;
2561
2562 -----------------------------------------------------------------------------
2563 -- Type: Procedure
2564 -- Scope: Public
2565 -- Name: get_timecard_bb_id
2566 -- IN Parameters: p_bb_id -> Start Time of the TBB
2567 -- OUT Parameters: p_timecard_bb_id -> id of the parent TBB id
2568 -- p_timecard_ovn -> ovn of the parent TBB id
2569 --
2570 -- Description: By passing in the id of a TBB, this procedure will work out
2571 -- what the TIMECARD TBB is, so it finds the highest TBB in the
2572 -- hierarchy of a timecard. You can pass in the id of a DAY or
2573 -- DETAIL (even TIMECARD although that wouldn't make a lot of
2574 -- sence) TBB and it will return the ID and OVN of the TIMECARD
2575 -- TBB this TBB belongs to. The procedure only looks in the DB
2576 -- so it is only usefull for timecards stored in the TimeStore.
2577 --
2578 -- Exceptions: No Timecard found
2579 -----------------------------------------------------------------------------
2580 PROCEDURE get_timecard_bb_id (
2581 p_bb_id IN hxc_time_building_blocks.time_building_block_id%TYPE,
2582 p_timecard_bb_id OUT NOCOPY hxc_time_building_blocks.time_building_block_id%TYPE,
2583 p_timecard_ovn OUT NOCOPY hxc_time_building_blocks.object_version_number%TYPE
2584 )
2585 IS
2586 l_proc VARCHAR2 (72);
2587
2588 -- This cursor will return the BB_ID and OVN of the current TIMECARD BB of a BB.
2589 CURSOR csr_get_timecard_bb_id (
2590 v_tc_id hxc_time_building_blocks.time_building_block_id%TYPE
2591 )
2592 IS
2593 SELECT time_building_block_id, MAX (object_version_number)
2594 FROM hxc_time_building_blocks
2595 WHERE SCOPE = hxc_timecard.c_timecard_scope
2596 AND date_to = hr_general.end_of_time
2597 CONNECT BY PRIOR parent_building_block_id = time_building_block_id
2598 AND PRIOR parent_building_block_ovn = object_version_number
2599 START WITH time_building_block_id = v_tc_id
2600 GROUP BY time_building_block_id
2601 ORDER BY time_building_block_id;
2602 BEGIN
2603 g_debug := hr_utility.debug_enabled;
2604
2605 IF g_debug
2606 THEN
2607 l_proc := g_package || 'get_timecard_bb_id';
2608 hr_utility.set_location ('Entering:' || l_proc, 10);
2609 END IF;
2610
2611 OPEN csr_get_timecard_bb_id (p_bb_id);
2612
2613 FETCH csr_get_timecard_bb_id
2614 INTO p_timecard_bb_id, p_timecard_ovn;
2615
2616 CLOSE csr_get_timecard_bb_id;
2617
2618 IF g_debug
2619 THEN
2620 hr_utility.set_location ('Leaving:' || l_proc, 20);
2621 END IF;
2622 END get_timecard_bb_id;
2623
2624 -----------------------------------------------------------------------------
2625 -- Type: Function
2626 -- Scope: Public
2627 -- Name: get_index_in_bb_table
2628 -- Returns: PLS_INTEGER
2629 -- IN Parameters: p_bb_table -> TBB Table Type that needs to be scanned
2630 -- p_bb_id_to_find -> id of the TBB that is being looked for
2631 --
2632 -- Description: This function will return the index of the row in the PL/SQL
2633 -- table (passed in) that holds the id (also passed in) of the
2634 -- TBB that is being looked for. In case the TBB is not found
2635 -- in the PL/SQL Table, a negative index is being returned.
2636 --
2637 -----------------------------------------------------------------------------
2638 FUNCTION get_index_in_bb_table (
2639 p_bb_table IN hxc_block_table_type,
2640 p_bb_id_to_find IN hxc_time_building_blocks.time_building_block_id%TYPE
2641 )
2642 RETURN PLS_INTEGER
2643 IS
2644 l_proc VARCHAR2 (72);
2645 l_building_block_count PLS_INTEGER;
2646 l_index PLS_INTEGER;
2647 l_current_highest_ovn PLS_INTEGER;
2648 BEGIN
2649 g_debug := hr_utility.debug_enabled;
2650
2651 IF g_debug
2652 THEN
2653 l_proc := g_package || 'get_index_in_bb_table';
2654 hr_utility.set_location ('Entering:' || l_proc, 10);
2655 END IF;
2656
2657 l_building_block_count := p_bb_table.FIRST;
2658 -- Initialize to zero as lowest possible ovn is 1
2659 l_current_highest_ovn := 0;
2660
2661 LOOP
2662 EXIT WHEN (NOT p_bb_table.EXISTS (l_building_block_count));
2663
2664 IF p_bb_table (l_building_block_count).time_building_block_id =
2665 p_bb_id_to_find
2666 THEN -- found BB
2667 IF (l_current_highest_ovn <
2668 p_bb_table (l_building_block_count).object_version_number
2669 )
2670 THEN
2671 -- only set the index if this is a newer TBB
2672 l_index := l_building_block_count;
2673 -- set this now as the highest ovn
2674 l_current_highest_ovn :=
2675 p_bb_table (l_building_block_count).object_version_number;
2676 END IF;
2677 END IF;
2678
2679 l_building_block_count := p_bb_table.NEXT (l_building_block_count);
2680 END LOOP;
2681
2682 IF l_index IS NULL
2683 THEN -- we never found the BB, return a negative number
2684 l_index := -1;
2685 END IF;
2686
2687 IF g_debug
2688 THEN
2689 hr_utility.set_location (' returning index = ' || l_index, 20);
2690 hr_utility.set_location ('Leaving:' || l_proc, 30);
2691 END IF;
2692
2693 RETURN l_index;
2694 END get_index_in_bb_table;
2695
2696 -----------------------------------------------------------------------------
2697 -- Type: Function
2698 -- Scope: Public
2699 -- Name: get_deposit_process_id
2700 -- Returns: NUMBER
2701 -- IN Parameters: p_deposit_process_name -> Name of the deposit process for
2702 -- which you want to get the ID
2703 --
2704 -- Description: This function will return the Id of the deposit process passed
2705 -- in.
2706 --
2707 -----------------------------------------------------------------------------
2708 FUNCTION get_deposit_process_id (
2709 p_deposit_process_name IN hxc_deposit_processes.NAME%TYPE
2710 )
2711 RETURN hxc_deposit_processes.deposit_process_id%TYPE
2712 IS
2713 l_proc VARCHAR2 (72);
2714
2715 CURSOR csr_deposit_process_id (v_deposit_process_name IN VARCHAR2)
2716 IS
2717 SELECT deposit_process_id
2718 FROM hxc_deposit_processes
2719 WHERE NAME = v_deposit_process_name;
2720
2721 l_deposit_process_id hxc_deposit_processes.deposit_process_id%TYPE;
2722 BEGIN
2723 g_debug := hr_utility.debug_enabled;
2724
2725 IF g_debug
2726 THEN
2727 l_proc := g_package || 'get_deposit_process_id';
2728 hr_utility.set_location ('Entering:' || l_proc, 10);
2729 END IF;
2730
2731 OPEN csr_deposit_process_id (p_deposit_process_name);
2732
2733 FETCH csr_deposit_process_id
2734 INTO l_deposit_process_id;
2735
2736 IF csr_deposit_process_id%NOTFOUND
2737 THEN
2738 CLOSE csr_deposit_process_id;
2739
2740 fnd_message.set_name ('HXC', 'HXC_NO_RETRIEVAL_PROCESS');
2741 fnd_message.set_token ('PROCESS_NAME', p_deposit_process_name);
2742 fnd_message.raise_error;
2743 END IF;
2744
2745 CLOSE csr_deposit_process_id;
2746
2747 IF g_debug
2748 THEN
2749 hr_utility.set_location ( ' returning deposit process id = '
2750 || l_deposit_process_id,
2751 20
2752 );
2753 hr_utility.set_location ('Leaving:' || l_proc, 30);
2754 END IF;
2755
2756 RETURN l_deposit_process_id;
2757 END get_deposit_process_id;
2758
2759 -----------------------------------------------------------------------------
2760 -- Type: Function
2761 -- Scope: Public
2762 -- Name: get_index_in_attr_table
2763 -- Returns: PLS_INTEGER
2764 -- IN Parameters: p_attr_table -> Attribute PL/SQL table that needs to be scanned
2765 -- p_attr_id_to_find -> id of the attr that is being looked for
2766 --
2767 -- Description: This function will return the index of the row in the PL/SQL
2768 -- table (passed in) that holds the id (also passed in) of the
2769 -- attr that is being looked for. In case the TBattrB is not
2770 -- found in the PL/SQL Table, a negative index is being returned.
2771 --
2772 -----------------------------------------------------------------------------
2773 FUNCTION get_index_in_attr_table (
2774 p_attr_table IN hxc_self_service_time_deposit.app_attributes_info,
2775 p_attr_id_to_find IN hxc_time_attributes.time_attribute_id%TYPE,
2776 p_attribute_name_to_find IN hxc_mapping_components.field_name%TYPE
2777 )
2778 RETURN PLS_INTEGER
2779 IS
2780 l_proc VARCHAR2 (72);
2781 l_attribute_count PLS_INTEGER;
2782 l_index PLS_INTEGER;
2783 BEGIN
2784 g_debug := hr_utility.debug_enabled;
2785
2786 IF g_debug
2787 THEN
2788 l_proc := g_package || 'get_index_in_attr_table';
2789 hr_utility.set_location ('Entering:' || l_proc, 10);
2790 END IF;
2791
2792 l_attribute_count := p_attr_table.FIRST;
2793
2794 LOOP
2795 EXIT WHEN (NOT p_attr_table.EXISTS (l_attribute_count))
2796 OR (l_index IS NOT NULL);
2797
2798 IF (p_attr_table (l_attribute_count).time_attribute_id =
2799 p_attr_id_to_find
2800 )
2801 AND (p_attr_table (l_attribute_count).attribute_name =
2802 p_attribute_name_to_find
2803 )
2804 THEN
2805 -- found BB, set the index
2806 l_index := l_attribute_count;
2807 END IF;
2808
2809 l_attribute_count := p_attr_table.NEXT (l_attribute_count);
2810 END LOOP;
2811
2812 IF l_index IS NULL
2813 THEN -- we never found the BB return a negative number
2814 l_index := -1;
2815 END IF;
2816
2817 IF g_debug
2818 THEN
2819 hr_utility.set_location (' returning index = ' || l_index, 20);
2820 hr_utility.set_location ('Leaving:' || l_proc, 30);
2821 END IF;
2822
2823 RETURN l_index;
2824 END get_index_in_attr_table;
2825
2826 -- Use this to find the parent BB if a range is given, this can not work for a measure
2827 -- It needs the start and stop time to find the parent
2828 -- this saves the user from having to find the ID of the parent block themselves
2829 -- This only works because there is no overlap in BB start and stop times
2830 -- Should this ever change than this procedure will cease to work as it might return multiple BB rows.
2831 /* PROCEDURE find_parent_building_block (
2832 p_building_block_id OUT NOCOPY hxc_time_building_blocks.time_building_block_id%TYPE,
2833 p_object_version_number OUT NOCOPY hxc_time_building_blocks.object_version_number%TYPE,
2834 p_scope IN hxc_time_building_blocks.SCOPE%TYPE,
2835 p_start_time IN hxc_time_building_blocks.start_time%TYPE,
2836 p_stop_time IN hxc_time_building_blocks.stop_time%TYPE,
2837 p_resource_id IN hxc_time_building_blocks.resource_id%TYPE,
2838 p_resource_type IN hxc_time_building_blocks.resource_type%TYPE
2839 DEFAULT 'PERSON'
2840 )
2841 IS
2842 l_proc VARCHAR2 (72)
2843 := g_package
2844 || 'find_parent_building_block';
2845
2846 -- This cursor will return the BB_ID and OVN of the parent TIMECARD BB.
2847 -- It should always return only one row!
2848 -- if it does not, there is overlap in the start and stop times which should not happen!
2849 CURSOR csr_get_parent (
2850
2851 -- v_tc_id hxc_time_building_blocks.time_building_block_id%TYPE,
2852 v_scope hxc_time_building_blocks.SCOPE%TYPE,
2853 v_start_time hxc_time_building_blocks.start_time%TYPE,
2854 v_stop_time hxc_time_building_blocks.stop_time%TYPE,
2855 v_resource_id hxc_time_building_blocks.resource_id%TYPE,
2856 v_resource_type hxc_time_building_blocks.resource_type%TYPE
2857 )
2858 IS
2859 SELECT time_building_block_id, MAX (object_version_number)
2860 FROM hxc_time_building_blocks
2861 WHERE SCOPE = DECODE (v_scope, 'DAY', 'TIMECARD', 'DETAIL', 'DAY')
2862 AND v_start_time >= start_time
2863 AND v_stop_time <= stop_time
2864 AND v_resource_id = resource_id
2865 AND v_resource_type = resource_type
2866 AND date_to = hr_general.end_of_time
2867 GROUP BY time_building_block_id;
2868 BEGIN
2869 g_debug :=hr_utility.debug_enabled;
2870 if g_debug then
2871 hr_utility.set_location ( 'Entering:'
2872 || l_proc, 10);
2873 end if;
2874 OPEN csr_get_parent (
2875 p_scope,
2876 p_start_time,
2877 p_stop_time,
2878 p_resource_id,
2879 p_resource_type
2880 );
2881 FETCH csr_get_parent INTO p_building_block_id, p_object_version_number;
2882 CLOSE csr_get_parent;
2883 if g_debug then
2884 hr_utility.set_location ( 'Leaving:'
2885 || l_proc, 20);
2886 end if;
2887 END find_parent_building_block;
2888 */
2889
2890 -----------------------------------------------------------------------------
2891 -- Type: Procedure
2892 -- Scope: Public
2893 -- Name: get_timecard_tables
2894 -- IN Parameters: p_building_block_id -> TBB Id for which a timecard table
2895 -- needs to be retrieved
2896 -- p_deposit_process -> Deposit process of which the mapping
2897 -- will be used to retrieve the attrs
2898 -- OUT Parameters: p_app_blocks -> TBB Type table that that will hold all
2899 -- all the TBBs found
2900 -- p_app_attributes -> Attribute PL/SQL table that will hold
2901 -- all the attrs found
2902 -- Description: This function will return the index of the row in the PL/SQL
2903 -- table (passed in) that holds the id (also passed in) of the
2904 -- attr that is being looked for. In case the TBattrB is not
2905 -- found in the PL/SQL Table, a negative index is being returned.
2906 --
2907 -----------------------------------------------------------------------------
2908 PROCEDURE get_timecard_tables
2909 (p_building_block_id IN hxc_time_building_blocks.time_building_block_id%TYPE,
2910 -- p_time_recipient_id IN NUMBER,
2911 p_deposit_process IN hxc_deposit_processes.NAME%TYPE,
2912 p_clear_mapping_cache IN BOOLEAN default false,
2913 p_app_blocks OUT NOCOPY hxc_block_table_type,
2914 p_app_attributes OUT NOCOPY hxc_self_service_time_deposit.app_attributes_info
2915 )
2916 IS
2917 l_proc VARCHAR2 (72);
2918 l_timecard_bb_id hxc_time_building_blocks.time_building_block_id%TYPE;
2919 l_object_version_number hxc_time_building_blocks.object_version_number%TYPE;
2920 l_counter PLS_INTEGER;
2921 l_attributes hxc_attribute_table_type;
2922 BEGIN
2923 g_debug := hr_utility.debug_enabled;
2924
2925 IF g_debug
2926 THEN
2927 l_proc := g_package || 'get_timecard_tables';
2928 hr_utility.set_location ('Entering:' || l_proc, 10);
2929 END IF;
2930
2931 -- find out what the ID of the TIMECARD BB for this BB is
2932 get_timecard_bb_id (p_bb_id => p_building_block_id,
2933 p_timecard_bb_id => l_timecard_bb_id,
2934 p_timecard_ovn => l_object_version_number
2935 );
2936
2937 IF g_debug
2938 THEN
2939 hr_utility.set_location ('Timecard BB_ID = ' || l_timecard_bb_id,
2940 15);
2941 END IF;
2942
2943 -- Then get the complete TC
2944 -- First get all the TBBs ...
2945 p_app_blocks :=
2946 hxc_timecard.load_blocks (p_timecard_id => l_timecard_bb_id,
2947 p_timecard_ovn => l_object_version_number
2948 );
2949 -- ... then all the attributes and store them in the Type structures
2950 l_attributes := hxc_timecard.load_attributes (p_blocks => p_app_blocks);
2951 -- Finally convert the attributes to app_attributes.
2952 p_app_attributes :=
2953 hxc_app_attribute_utils.create_app_attributes
2954 (p_attributes => l_attributes,
2955 p_retrieval_process_id => NULL,
2956 p_deposit_process_id => get_deposit_process_id(p_deposit_process)
2957 );
2958
2959 if(p_clear_mapping_cache) then
2960 hxc_app_attribute_utils.clear_mapping_cache;
2961 end if;
2962
2963 IF g_debug
2964 THEN
2965 hr_utility.set_location ('Leaving:' || l_proc, 20);
2966 END IF;
2967 END get_timecard_tables;
2968
2969 -----------------------------------------------------------------------------
2970 -- Type: Procedure
2971 -- Scope: Public
2972 -- Name: get_timecard_tables
2973 -- IN Parameters: p_building_block_id -> TBB Id for which a timecard table
2974 -- needs to be retrieved
2975 -- p_deposit_process -> Deposit process of which the mapping
2976 -- will be used to retrieve the attrs
2977 -- OUT Parameters: p_app_blocks -> TBB PL/SQL table that that will hold all
2978 -- all the TBBs found
2979 -- p_app_attributes -> Attribute PL/SQL table that will hold
2980 -- all the attrs found
2981 -- Description: Overloaded procedure, using old PL/SQL Table for TBBs. See
2982 -- main get_timecard_tables for more information
2983 -----------------------------------------------------------------------------
2984 PROCEDURE get_timecard_tables
2985 (p_building_block_id IN hxc_time_building_blocks.time_building_block_id%TYPE,
2986 -- p_time_recipient_id IN NUMBER,
2987 p_deposit_process IN hxc_deposit_processes.NAME%TYPE,
2988 p_clear_mapping_cache IN BOOLEAN default false,
2989 p_app_blocks OUT NOCOPY hxc_self_service_time_deposit.timecard_info,
2990 p_app_attributes OUT NOCOPY hxc_self_service_time_deposit.app_attributes_info
2991 )
2992 IS
2993 l_proc VARCHAR2 (72);
2994 l_blocks hxc_block_table_type;
2995 BEGIN
2996 g_debug := hr_utility.debug_enabled;
2997
2998 IF g_debug
2999 THEN
3000 l_proc := g_package || 'get_timecard_tables (Overloaded)';
3001 hr_utility.set_location ('Entering:' || l_proc, 10);
3002 END IF;
3003
3004 get_timecard_tables (p_building_block_id => p_building_block_id,
3005 p_deposit_process => p_deposit_process,
3006 p_clear_mapping_cache => p_clear_mapping_cache,
3007 p_app_blocks => l_blocks,
3008 p_app_attributes => p_app_attributes
3009 );
3010 p_app_blocks :=
3011 hxc_timecard_block_utils.convert_to_dpwr_blocks (p_blocks => l_blocks);
3012
3013 IF g_debug
3014 THEN
3015 hr_utility.set_location ('Leaving:' || l_proc, 20);
3016 END IF;
3017 END get_timecard_tables;
3018
3019 -----------------------------------------------------------------------------
3020 -- Type: Procedure
3021 -- Scope: Public
3022 -- Name: get_bld_blk_info_type
3023 -- Returns: hxc_bld_blk_info_types.bld_blk_info_type%TYPE
3024 -- IN Parameters: p_attribute_name -> Name of the attribute
3025 -- p_deposit_process -> Name of the deposit process which we will
3026 -- use the mapping of
3027 -- OUT Parameters: p_bld_blk_info_type -> BB Info Type for the attribute name
3028 -- p_segment -> Segment in which the attribute value needs to
3029 -- get stored.
3030 --
3031 -- Description: Private Procedure that will find the Building Block Info Type
3032 -- of an attribute and the segment in which the attribute needs
3033 -- to get stored. It uses the mapping related to the deposit
3034 -- process (passed in) to work out what the Building Block Info
3035 -- Type is. The approval status is linked (hard coded here) to
3036 -- the mode.
3037 --
3038 -----------------------------------------------------------------------------
3039 PROCEDURE get_bld_blk_info_type (
3040 p_attribute_name IN hxc_mapping_components.field_name%TYPE,
3041 p_deposit_process IN hxc_deposit_processes.NAME%TYPE,
3042 p_bld_blk_info_type OUT NOCOPY hxc_bld_blk_info_types.bld_blk_info_type%TYPE,
3043 p_segment OUT NOCOPY hxc_mapping_components.SEGMENT%TYPE
3044 )
3045 IS
3046 l_proc VARCHAR2 (72);
3047
3048 CURSOR csr_bld_blk_info_type (
3049 v_field_name hxc_mapping_components.field_name%TYPE,
3050 v_deposit_process_id hxc_deposit_processes.deposit_process_id%TYPE
3051 )
3052 IS
3053 SELECT bbit.bld_blk_info_type, mc.SEGMENT
3054 FROM hxc_mapping_components mc,
3055 hxc_mapping_comp_usages mcu,
3056 hxc_mappings m,
3057 hxc_deposit_processes dp,
3058 hxc_bld_blk_info_types bbit,
3059 hxc_bld_blk_info_type_usages bbui
3060 WHERE dp.mapping_id = m.mapping_id
3061 AND m.mapping_id = mcu.mapping_id
3062 AND mcu.mapping_component_id = mc.mapping_component_id
3063 AND mc.bld_blk_info_type_id = bbit.bld_blk_info_type_id
3064 AND bbit.bld_blk_info_type_id = bbui.bld_blk_info_type_id
3065 AND mc.field_name = v_field_name
3066 AND dp.deposit_process_id = v_deposit_process_id;
3067 BEGIN
3068 g_debug := hr_utility.debug_enabled;
3069
3070 IF g_debug
3071 THEN
3072 l_proc := g_package || 'get_bld_blk_info_type';
3073 hr_utility.set_location ('Entering:' || l_proc, 10);
3074 END IF;
3075
3076 OPEN csr_bld_blk_info_type (p_attribute_name,
3077 get_deposit_process_id (p_deposit_process)
3078 );
3079
3080 FETCH csr_bld_blk_info_type
3081 INTO p_bld_blk_info_type, p_segment;
3082
3083 CLOSE csr_bld_blk_info_type;
3084
3085 IF g_debug
3086 THEN
3087 hr_utility.set_location ( ' Found Building Block Info Type = '
3088 || p_bld_blk_info_type,
3089 20
3090 );
3091 hr_utility.set_location (' Found Segment = ' || p_segment, 30);
3092 hr_utility.set_location ('Leaving:' || l_proc, 40);
3093 END IF;
3094 END get_bld_blk_info_type;
3095
3096 -----------------------------------------------------------------------------
3097 -- Type: Procedure
3098 -- Scope: Public
3099 -- Name: clear_building_block_table
3100 -- INOUT Parameters: p_app_blocks -> TBB Table Type you want to empty
3101 --
3102 -- Description: This procedure will allow you to clear the TBB PL/SQL Table
3103 --
3104 -----------------------------------------------------------------------------
3105 PROCEDURE clear_building_block_table (
3106 p_app_blocks IN OUT NOCOPY hxc_block_table_type
3107 )
3108 IS
3109 l_proc VARCHAR2 (72);
3110 BEGIN
3111 g_debug := hr_utility.debug_enabled;
3112
3113 IF g_debug
3114 THEN
3115 l_proc := g_package || 'clear_building_block_table';
3116 hr_utility.set_location ('Entering:' || l_proc, 10);
3117 END IF;
3118
3119 IF p_app_blocks.COUNT > 0
3120 THEN
3121 p_app_blocks.DELETE;
3122 END IF;
3123
3124 IF g_debug
3125 THEN
3126 hr_utility.set_location ('Leaving:' || l_proc, 20);
3127 END IF;
3128 END clear_building_block_table;
3129
3130 -----------------------------------------------------------------------------
3131 -- Type: Procedure
3132 -- Scope: Public
3133 -- Name: clear_attribute_table
3134 -- INOUT Parameters: p_app_attributes -> Attr PL/SQL table you want to empty
3135 --
3136 -- Description: This procedure will allow you to clear the Attr PL/SQL Table
3137 --
3138 -----------------------------------------------------------------------------
3139 PROCEDURE clear_attribute_table (
3140 p_app_attributes IN OUT NOCOPY hxc_self_service_time_deposit.app_attributes_info
3141 )
3142 IS
3143 l_proc VARCHAR2 (72);
3144 BEGIN
3145 g_debug := hr_utility.debug_enabled;
3146
3147 IF g_debug
3148 THEN
3149 l_proc := g_package || 'clear_attribute_table';
3150 hr_utility.set_location ('Entering:' || l_proc, 10);
3151 END IF;
3152
3153 IF p_app_attributes.COUNT > 0
3154 THEN
3155 p_app_attributes.DELETE;
3156 END IF;
3157
3158 IF g_debug
3159 THEN
3160 hr_utility.set_location ('Leaving:' || l_proc, 20);
3161 END IF;
3162 END clear_attribute_table;
3163
3164 -----------------------------------------------------------------------------
3165 -- Type: Procedure
3166 -- Scope: Public
3167 -- Name: clear_message_table
3168 -- INOUT Parameters: p_messages -> Message PL/SQL table you want to empty
3169 --
3170 -- Description: This procedure will allow you to clear the Message PL/SQL Table
3171 --
3172 -----------------------------------------------------------------------------
3173 PROCEDURE clear_message_table (
3174 p_messages IN OUT NOCOPY hxc_message_table_type
3175 )
3176 IS
3177 l_proc VARCHAR2 (72);
3178 BEGIN
3179 g_debug := hr_utility.debug_enabled;
3180
3181 IF g_debug
3182 THEN
3183 l_proc := g_package || 'clear_message_table';
3184 hr_utility.set_location ('Entering:' || l_proc, 10);
3185 END IF;
3186
3187 IF p_messages.COUNT > 0
3188 THEN
3189 p_messages.DELETE;
3190 END IF;
3191
3192 IF g_debug
3193 THEN
3194 hr_utility.set_location ('Leaving:' || l_proc, 20);
3195 END IF;
3196 END clear_message_table;
3197
3198 -----------------------------------------------------------------------------
3199 -- Type: Procedure
3200 -- Scope: Public
3201 -- Name: request_lock
3202 -- IN Parameters: p_app_blocks -> TBB PL/SQL table you want to lock
3203 -- IN OUT Parameters: p_messages -> Message PL/SQL table
3204 -- OUT Parameters: p_locked_success -> Lock was successful or not
3205 -- p_row_lock_id -> lock id of the lock
3206 -- Description: This procedure will allow you to lock the period for which you
3207 -- are about to create or update a TC.
3208 --
3209 -----------------------------------------------------------------------------
3210 PROCEDURE request_lock (
3211 p_app_blocks IN hxc_block_table_type,
3212 p_messages IN OUT NOCOPY hxc_message_table_type,
3213 p_locked_success OUT NOCOPY BOOLEAN,
3214 p_row_lock_id OUT NOCOPY ROWID
3215 )
3216 IS
3217 l_proc VARCHAR2 (72);
3218 l_tc_index PLS_INTEGER;
3219 BEGIN
3220 g_debug := hr_utility.debug_enabled;
3221
3222 IF g_debug
3223 THEN
3224 l_proc := g_package || 'request_lock';
3225 hr_utility.set_location ('Entering:' || l_proc, 10);
3226 END IF;
3227
3228 l_tc_index :=
3229 hxc_timecard_block_utils.find_active_timecard_index (p_app_blocks);
3230
3231 IF (hxc_timecard_block_utils.is_new_block (p_app_blocks (l_tc_index)))
3232 THEN
3233 hxc_lock_api.request_lock
3234 (p_process_locker_type => hxc_lock_util.c_plsql_deposit_action,
3235 p_resource_id => p_app_blocks (l_tc_index).resource_id,
3236 p_start_time => fnd_date.canonical_to_date
3237 (p_app_blocks (l_tc_index).start_time
3238 ),
3239 p_stop_time => fnd_date.canonical_to_date
3240 (p_app_blocks (l_tc_index).stop_time
3241 ),
3242 p_time_building_block_id => NULL,
3243 p_time_building_block_ovn => NULL,
3244 p_row_lock_id => p_row_lock_id,
3245 p_messages => p_messages,
3246 p_locked_success => p_locked_success
3247 );
3248 ELSE
3249 hxc_lock_api.request_lock
3250 (p_process_locker_type => hxc_lock_util.c_plsql_deposit_action,
3251 p_resource_id => NULL,
3252 p_start_time => NULL,
3253 p_stop_time => NULL,
3254 p_time_building_block_id => p_app_blocks (l_tc_index).time_building_block_id,
3255 p_time_building_block_ovn => p_app_blocks (l_tc_index).object_version_number,
3256 p_row_lock_id => p_row_lock_id,
3257 p_messages => p_messages,
3258 p_locked_success => p_locked_success
3259 );
3260 END IF;
3261
3262 IF g_debug
3263 THEN
3264 hr_utility.set_location ('Leaving:' || l_proc, 20);
3265 END IF;
3266 END request_lock;
3267
3268 -----------------------------------------------------------------------------
3269 -- Type: Procedure
3270 -- Scope: Public
3271 -- Name: release_lock
3272 -- IN Parameters: p_app_blocks -> TBB PL/SQL table you want to release the lock
3273 -- for
3274 -- IN OUT Parameters: p_messages -> Message PL/SQL table
3275 -- OUT Parameters: p_released_success -> Lock was released successful or not
3276 -- p_row_lock_id -> lock id of the lock you want to release
3277 -- Description: This procedure will allow you to release the lock on the
3278 -- period that you grabbed with request_lock.
3279 --
3280 -----------------------------------------------------------------------------
3281 PROCEDURE release_lock (
3282 p_app_blocks IN hxc_block_table_type,
3283 p_messages IN OUT NOCOPY hxc_message_table_type,
3284 p_released_success OUT NOCOPY BOOLEAN,
3285 p_row_lock_id IN OUT NOCOPY ROWID
3286 )
3287 IS
3288 l_proc VARCHAR2 (72);
3289 l_tc_index PLS_INTEGER;
3290 BEGIN
3291 g_debug := hr_utility.debug_enabled;
3292
3293 IF g_debug
3294 THEN
3295 l_proc := g_package || 'release_lock';
3296 hr_utility.set_location ('Entering:' || l_proc, 10);
3297 END IF;
3298
3299 l_tc_index :=
3300 hxc_timecard_block_utils.find_active_timecard_index (p_app_blocks);
3301
3302 IF (hxc_timecard_block_utils.is_new_block (p_app_blocks (l_tc_index)))
3303 THEN
3304 hxc_lock_api.release_lock
3305 (p_row_lock_id => p_row_lock_id,
3306 p_process_locker_type => hxc_lock_util.c_plsql_deposit_action,
3307 p_resource_id => p_app_blocks (l_tc_index).resource_id,
3308 p_start_time => fnd_date.canonical_to_date
3309 (p_app_blocks (l_tc_index).start_time
3310 ),
3311 p_stop_time => fnd_date.canonical_to_date
3312 (p_app_blocks (l_tc_index).stop_time
3313 ),
3314 p_time_building_block_id => NULL,
3315 p_time_building_block_ovn => NULL,
3316 p_messages => p_messages,
3317 p_released_success => p_released_success
3318 );
3319 ELSE
3320 hxc_lock_api.release_lock
3321 (p_row_lock_id => p_row_lock_id,
3322 p_process_locker_type => hxc_lock_util.c_plsql_deposit_action,
3323 p_resource_id => NULL,
3324 p_start_time => NULL,
3325 p_stop_time => NULL,
3326 p_time_building_block_id => p_app_blocks (l_tc_index).time_building_block_id,
3327 p_time_building_block_ovn => p_app_blocks (l_tc_index).object_version_number,
3328 p_messages => p_messages,
3329 p_released_success => p_released_success
3330 );
3331 END IF;
3332
3333 IF g_debug
3334 THEN
3335 hr_utility.set_location ('Leaving:' || l_proc, 20);
3336 END IF;
3337 END release_lock;
3338
3339 -----------------------------------------------------------------------------
3340 -- Type: Procedure
3341 -- Scope: Public
3342 -- Name: log_timecard
3343 -- IN Parameters: p_app_blocks -> TBB type you want to log
3344 -- p_app_attributes -> Attr PL/SQL table you want to log
3345 -- Description: This procedure will allow you log the complete timecard and
3346 -- all it's associated attributes in fnd_log_messages. For this
3347 -- to work you will have to set the the profile option
3348 -- FND: Debug Log Enabled = Yes and FND: Debug Log Level =
3349 -- Statement. Also your code must contain a line in the beginning
3350 -- that looks like this:
3351 -- FND_GLOBAL.APPS_INITIALIZE
3352 -- ( user_id => <user id you are loggin msg under>,
3353 -- resp_id => <responsibility id of the user>
3354 -- resp_appl_id => 809 )
3355 -- ;
3356 --
3357 -- This procedure is provided for debugging purposes.
3358 --
3359 -----------------------------------------------------------------------------
3360 PROCEDURE log_timecard (
3361 p_app_blocks IN hxc_block_table_type,
3362 p_app_attributes IN hxc_self_service_time_deposit.app_attributes_info
3363 )
3364 IS
3365 l_proc VARCHAR2 (72);
3366 i PLS_INTEGER;
3367 j PLS_INTEGER;
3368 BEGIN
3369 g_debug := hr_utility.debug_enabled;
3370
3371 IF g_debug
3372 THEN
3373 l_proc := g_package || 'log_timecard';
3374 hr_utility.set_location ('Entering:' || l_proc, 10);
3375 hr_utility.set_location (' Number of BBs:' || p_app_blocks.COUNT,
3376 15
3377 );
3378 hr_utility.set_location ( ' Number of Attrs:'
3379 || p_app_attributes.COUNT,
3380 16
3381 );
3382 END IF;
3383
3384 i := p_app_blocks.FIRST;
3385
3386 <<print_time_building_blocks>>
3387 LOOP
3388 EXIT print_time_building_blocks WHEN (NOT p_app_blocks.EXISTS (i));
3389
3390 IF g_debug
3391 THEN
3392 hr_utility.TRACE
3393 ( LPAD
3394 (NVL (TO_CHAR (p_app_blocks (i).time_building_block_id),
3395 ' '
3396 ),
3397 9
3398 )
3399 || ' '
3400 || RPAD (NVL (p_app_blocks (i).TYPE, ' '), 7)
3401 || ' '
3402 || LPAD (NVL (TO_CHAR (p_app_blocks (i).measure), ' '), 7)
3403 || ' '
3404 || RPAD (NVL (p_app_blocks (i).unit_of_measure, ' '), 5)
3405 || ' '
3406 || RPAD (NVL (p_app_blocks (i).start_time, ' '), 20)
3407 || ' '
3408 || RPAD (NVL (p_app_blocks (i).stop_time, ' '), 20)
3409 || ' '
3410 || LPAD
3411 (NVL (TO_CHAR (p_app_blocks (i).parent_building_block_id),
3412 ' '
3413 ),
3414 9
3415 )
3416 || ' '
3417 || RPAD (NVL (p_app_blocks (i).parent_is_new, ' '), 2)
3418 || ' '
3419 || RPAD (NVL (p_app_blocks (i).SCOPE, ' '), 8)
3420 || ' '
3421 || LPAD (NVL (TO_CHAR (p_app_blocks (i).object_version_number),
3422 ' '
3423 ),
3424 5
3425 )
3426 || ' '
3427 || RPAD (NVL (p_app_blocks (i).approval_status, ' '), 10)
3428 || ' '
3429 || LPAD (NVL (TO_CHAR (p_app_blocks (i).resource_id), ' '),
3430 10)
3431 || ' '
3432 || RPAD (NVL (p_app_blocks (i).resource_type, ' '), 10)
3433 || ' '
3434 || LPAD (NVL (TO_CHAR (p_app_blocks (i).approval_style_id),
3435 ' '
3436 ),
3437 5
3438 )
3439 || ' '
3440 || RPAD (p_app_blocks (i).date_from, 11)
3441 || ' '
3442 || RPAD (p_app_blocks (i).date_to, 11)
3443 || ' '
3444 || RPAD (NVL (p_app_blocks (i).comment_text, ' '), 30)
3445 || ' '
3446 || RPAD
3447 (NVL
3448 (TO_CHAR (p_app_blocks (i).parent_building_block_ovn),
3449 ' '
3450 ),
3451 5
3452 )
3453 || ' '
3454 || RPAD (NVL (p_app_blocks (i).NEW, ' '), 2)
3455 || ' '
3456 || RPAD (NVL (p_app_blocks (i).changed, ' '), 2)
3457 || ' '
3458 );
3459 END IF;
3460
3461 -- Find out if this BB has any attributes
3462 -- if so, print them with this BB
3463 j := p_app_attributes.FIRST;
3464
3465 <<print_attributes>>
3466 LOOP
3467 EXIT print_attributes WHEN (NOT p_app_attributes.EXISTS (j));
3468
3469 IF p_app_blocks (i).time_building_block_id =
3470 p_app_attributes (j).building_block_id
3471 THEN
3472 IF g_debug
3473 THEN
3474 hr_utility.TRACE
3475 ( ' '
3476 || --indent to indicate attribute
3477 LPAD (p_app_attributes (j).time_attribute_id,
3478 9
3479 )
3480 || ' '
3481 || LPAD (p_app_attributes (j).building_block_id,
3482 9
3483 )
3484 || ' '
3485 || RPAD (p_app_attributes (j).attribute_name,
3486 20
3487 )
3488 || ' '
3489 || RPAD (p_app_attributes (j).attribute_value,
3490 20
3491 )
3492 || ' '
3493 || RPAD (p_app_attributes (j).bld_blk_info_type,
3494 20
3495 )
3496 || ' '
3497 || RPAD (p_app_attributes (j).CATEGORY, 20)
3498 || ' '
3499 || RPAD (p_app_attributes (j).updated, 2)
3500 || ' '
3501 || RPAD (p_app_attributes (j).changed, 2)
3502 || ' '
3503 );
3504 END IF;
3505 END IF;
3506
3507 j := p_app_attributes.NEXT (j);
3508 END LOOP print_attributes;
3509
3510 i := p_app_blocks.NEXT (i);
3511 END LOOP print_time_building_blocks;
3512
3513 IF g_debug
3514 THEN
3515 hr_utility.set_location ('Leaving:' || l_proc, 20);
3516 END IF;
3517 END log_timecard;
3518
3519 -----------------------------------------------------------------------------
3520 -- Type: Procedure
3521 -- Scope: Public
3522 -- Name: log_timecard
3523 -- IN Parameters: p_app_blocks -> TBB PL/SQL table you want to log
3524 -- p_app_attributes -> Attr PL/SQL table you want to log
3525 -- Description: This procedure will allow you log the complete timecard and
3526 -- all it's associated attributes in fnd_log_messages. For this
3527 -- to work you will have to set the the profile option
3528 -- FND: Debug Log Enabled = Yes and FND: Debug Log Level =
3529 -- Statement. Also your code must contain a line in the beginning
3530 -- that looks like this:
3531 -- FND_GLOBAL.APPS_INITIALIZE
3532 -- ( user_id => <user id you are loggin msg under>,
3533 -- resp_id => <responsibility id of the user>
3534 -- resp_appl_id => 809 )
3535 -- ;
3536 --
3537 -- This procedure is provided for debugging purposes.
3538 --
3539 -----------------------------------------------------------------------------
3540 PROCEDURE log_timecard (
3541 p_app_blocks IN hxc_self_service_time_deposit.timecard_info,
3542 p_app_attributes IN hxc_self_service_time_deposit.app_attributes_info
3543 )
3544 IS
3545 l_proc VARCHAR2 (72);
3546 l_blocks hxc_block_table_type;
3547 BEGIN
3548 g_debug := hr_utility.debug_enabled;
3549
3550 IF g_debug
3551 THEN
3552 l_proc := g_package || 'log_timecard (Overloaded)';
3553 hr_utility.set_location ('Entering:' || l_proc, 10);
3554 END IF;
3555
3556 l_blocks := convert_tbb_to_type (p_blocks => p_app_blocks);
3557 log_timecard (p_app_blocks => l_blocks,
3558 p_app_attributes => p_app_attributes
3559 );
3560
3561 IF g_debug
3562 THEN
3563 hr_utility.set_location ('Leaving:' || l_proc, 20);
3564 END IF;
3565 END log_timecard;
3566
3567 -----------------------------------------------------------------------------
3568 -- Type: Procedure
3569 -- Scope: Public
3570 -- Name: log_messages
3571 -- IN Parameters: p_messages -> Message type you want to log
3572 -- Description: This procedure will allow you log the messages returned by the
3573 -- deposit process.
3574 -- For this to work you will have to set the the profile option
3575 -- FND: Debug Log Enabled = Yes and FND: Debug Log Level =
3576 -- Statement. Also your code must contain a line in the beginning
3577 -- that looks like this:
3578 -- FND_GLOBAL.APPS_INITIALIZE
3579 -- ( user_id => <user id you are loggin msg under>,
3580 -- resp_id => <responsibility id of the user>
3581 -- resp_appl_id => 809 )
3582 -- ;
3583 --
3584 -- This procedure is provided for debugging purposes.
3585 --
3586 -----------------------------------------------------------------------------
3587 PROCEDURE log_messages (p_messages IN hxc_message_table_type)
3588 IS
3589 l_proc VARCHAR2 (72);
3590 l_message fnd_new_messages.MESSAGE_TEXT%TYPE;
3591 i PLS_INTEGER;
3592 BEGIN
3593 g_debug := hr_utility.debug_enabled;
3594
3595 IF g_debug
3596 THEN
3597 l_proc := g_package || 'log_messages';
3598 hr_utility.set_location ('Entering:' || l_proc, 10);
3599 END IF;
3600
3601 IF (p_messages IS NOT NULL)
3602 THEN -- messages have been returned
3603 i := p_messages.FIRST;
3604
3605 LOOP
3606 EXIT WHEN (NOT p_messages.EXISTS (i));
3607 -- First translate the message as the messagetable returned does not give the actual
3608 -- message, only the message_name which doesn't mean anything to the user.
3609 l_message :=
3610 fnd_message.get_string
3611 (appin => p_messages (i).application_short_name,
3612 namein => p_messages (i).message_name
3613 );
3614
3615 IF g_debug
3616 THEN
3617 hr_utility.TRACE
3618 ( RPAD (p_messages (i).application_short_name, 10)
3619 || ' '
3620 || LPAD (p_messages (i).time_building_block_id, 7)
3621 || ' '
3622 || LPAD
3623 (NVL (TO_CHAR (p_messages (i).time_building_block_ovn),
3624 ' '
3625 ),
3626 5
3627 )
3628 || ' '
3629 || LPAD (NVL (TO_CHAR (p_messages (i).time_attribute_id),
3630 ' '
3631 ),
3632 7
3633 )
3634 || ' '
3635 || LPAD (p_messages (i).time_attribute_ovn, 5)
3636 || ' '
3637 || RPAD (p_messages (i).message_name, 30)
3638 || ' '
3639 || RPAD (l_message, 80)
3640 || ' '
3641 || RPAD (p_messages (i).message_level, 10)
3642 || ' '
3643 || RPAD (p_messages (i).message_field, 30)
3644 || ' '
3645 || RPAD (p_messages (i).message_tokens, 30)
3646 || ' '
3647 -- rpad(p_messages(i).on_oa_msg_stack,30)||' '
3648 );
3649 END IF;
3650
3651 i := p_messages.NEXT (i);
3652 END LOOP;
3653 ELSE
3654 IF g_debug
3655 THEN
3656 hr_utility.TRACE
3657 (' --- No Errors Found, Timecard Deposit Successfull! ---');
3658 END IF;
3659 END IF;
3660
3661 IF g_debug
3662 THEN
3663 hr_utility.set_location ('Leaving:' || l_proc, 20);
3664 END IF;
3665 END log_messages;
3666
3667 -----------------------------------------------------------------------------
3668 -- Type: Procedure
3669 -- Scope: Public
3670 -- Name: log_messages
3671 -- IN Parameters: p_messages -> Message PL/SQL table you want to log
3672 -- p_retrieval_process_id -> process id for retrieval
3673 -- Description: This procedure will allow you log the messages returned by the
3674 -- deposit process.
3675 -- For this to work you will have to set the the profile option
3676 -- FND: Debug Log Enabled = Yes and FND: Debug Log Level =
3677 -- Statement. Also your code must contain a line in the beginning
3678 -- that looks like this:
3679 -- FND_GLOBAL.APPS_INITIALIZE
3680 -- ( user_id => <user id you are loggin msg under>,
3681 -- resp_id => <responsibility id of the user>
3682 -- resp_appl_id => 809 )
3683 -- ;
3684 --
3685 -- This procedure is provided for debugging purposes.
3686 --
3687 -----------------------------------------------------------------------------
3688 PROCEDURE log_messages (
3689 p_messages IN hxc_self_service_time_deposit.message_table
3690 )
3691 IS
3692 l_proc VARCHAR2 (72);
3693 l_messages hxc_message_table_type;
3694 BEGIN
3695 g_debug := hr_utility.debug_enabled;
3696
3697 IF g_debug
3698 THEN
3699 l_proc := g_package || 'log_messages (Overloaded)';
3700 hr_utility.set_location ('Entering:' || l_proc, 10);
3701 END IF;
3702
3703 hxc_timecard_message_utils.append_old_messages
3704 (p_messages => l_messages,
3705 p_old_messages => p_messages,
3706 p_retrieval_process_id => NULL
3707 );
3708 log_messages (p_messages => l_messages);
3709
3710 IF g_debug
3711 THEN
3712 hr_utility.set_location ('Leaving:' || l_proc, 20);
3713 END IF;
3714 END log_messages;
3715
3716 -----------------------------------------------------------------------------
3717 -- Type: Function
3718 -- Scope: Public
3719 -- Name: translate_message_table
3720 -- Returns: hxc_self_service_time_deposit.message_table
3721 -- IN Parameters: p_messages -> Message PL/SQL table you want to use for
3722 -- replacing tokens.
3723 -- Description: This procedure will take the message table from the deposit
3724 -- wrapper and construct a new table that will have the proper
3725 -- message in it with all tokens properly filled in.
3726 -- This procedure is provided for debugging purposes.
3727 --
3728 -----------------------------------------------------------------------------
3729 FUNCTION translate_message_table (
3730 p_messages IN hxc_self_service_time_deposit.message_table
3731 )
3732 RETURN translated_message_table
3733 IS
3734 l_proc VARCHAR2 (72);
3735 l_messages translated_message_table;
3736 l_token_table hxc_deposit_wrapper_utilities.t_simple_table;
3737 l_message_idx PLS_INTEGER := p_messages.FIRST;
3738 BEGIN
3739 g_debug := hr_utility.debug_enabled;
3740
3741 IF g_debug
3742 THEN
3743 l_proc := g_package || 'translate_message_table';
3744 hr_utility.set_location ('Entering:' || l_proc, 10);
3745 END IF;
3746
3747 <<process_all_messages>>
3748 LOOP
3749 EXIT process_all_messages WHEN NOT p_messages.EXISTS (l_message_idx);
3750 -- set message on stack so we can work with it
3751 fnd_message.set_name
3752 (p_messages (l_message_idx).application_short_name,
3753 p_messages (l_message_idx).message_name
3754 );
3755
3756 IF (p_messages (l_message_idx).message_tokens IS NOT NULL)
3757 THEN
3758 hxc_deposit_wrapper_utilities.string_to_table
3759 ('&',
3760 '&'
3761 || p_messages (l_message_idx).message_tokens,
3762 l_token_table
3763 );
3764
3765 FOR l_token IN 0 .. (l_token_table.COUNT / 2) - 1
3766 LOOP
3767 -- replace all tokens in the message on the stack
3768 fnd_message.set_token (token => l_token_table (2 * l_token),
3769 VALUE => l_token_table ( 2
3770 * l_token
3771 + 1
3772 )
3773 );
3774 END LOOP;
3775 END IF;
3776
3777 -- get the message back from the stack and put in the message table...
3778 l_messages (l_message_idx).message_name :=
3779 p_messages (l_message_idx).message_name;
3780 -- ... and just copy the other fields for convinience.
3781 l_messages (l_message_idx).MESSAGE_TEXT := fnd_message.get;
3782 l_messages (l_message_idx).time_building_block_id :=
3783 p_messages (l_message_idx).time_building_block_id;
3784 l_messages (l_message_idx).time_building_block_ovn :=
3785 p_messages (l_message_idx).time_building_block_ovn;
3786 l_messages (l_message_idx).time_attribute_id :=
3787 p_messages (l_message_idx).time_attribute_id;
3788 l_messages (l_message_idx).time_attribute_ovn :=
3789 p_messages (l_message_idx).time_attribute_ovn;
3790 l_message_idx := p_messages.NEXT (l_message_idx);
3791 END LOOP process_all_messages;
3792
3793 IF g_debug
3794 THEN
3795 hr_utility.set_location ('Leaving:' || l_proc, 100);
3796 END IF;
3797
3798 RETURN l_messages;
3799 END translate_message_table;
3800
3801 -----------------------------------------------------------------------------
3802 -- Type: Procedure
3803 -- Scope: Public
3804 -- Name: find_current_period
3805 -- IN Parameters: p_resource_id ->
3806 -- p_resource_type ->
3807 -- p_day ->
3808 -- OUT Parameters: p_start_time ->
3809 -- p_stop_time ->
3810 -- Description: This procedure will try to work out the start and end date for
3811 -- the timecard to be created. This can easily be worked out
3812 -- from the TC recuring period, but we also need to take into
3813 -- consideration the effective dates of the active assignments
3814 -- for the resource in question, i.e. we should not let the TC
3815 -- start before an assignment exists for the resouce.
3816 --
3817 -----------------------------------------------------------------------------
3818 PROCEDURE find_current_period (
3819 p_resource_id IN hxc_time_building_blocks.resource_id%TYPE,
3820 p_resource_type IN hxc_time_building_blocks.resource_type%TYPE,
3821 p_day IN hxc_time_building_blocks.start_time%TYPE,
3822 p_start_time OUT NOCOPY DATE,
3823 p_stop_time OUT NOCOPY DATE
3824 )
3825 IS
3826 l_proc VARCHAR2 (72);
3827 l_periods hxc_timecard_utilities.periods;
3828 l_period_idx PLS_INTEGER;
3829 l_period_found BOOLEAN DEFAULT FALSE;
3830 BEGIN
3831 IF g_debug
3832 THEN
3833 l_proc := g_package || 'find_current_period';
3834 hr_utility.set_location ('Entering:' || l_proc, 10);
3835 END IF;
3836
3837 hxc_timecard_utilities.init_globals (p_resource_id => p_resource_id);
3838 l_periods :=
3839 hxc_timecard_utilities.get_periods
3840 (p_resource_id => p_resource_id,
3841 p_resource_type => p_resource_type,
3842 p_current_date => p_day,
3843 p_show_existing_timecard => 'Y'
3844 );
3845 l_period_idx := l_periods.FIRST;
3846
3847 <<find_period>>
3848 LOOP
3849 EXIT find_period WHEN NOT l_periods.EXISTS (l_period_idx)
3850 OR l_period_found;
3851
3852 IF (TRUNC (p_day) BETWEEN TRUNC(l_periods (l_period_idx).start_date)
3853 AND TRUNC(l_periods (l_period_idx).end_date)
3854 )
3855 THEN
3856 p_start_time :=
3857 TO_DATE ( TO_CHAR (l_periods (l_period_idx).start_date,
3858 'DD-MM-YYYY'
3859 )
3860 || ' 00:00:00',
3861 'DD-MM-YYYY HH24:MI:SS'
3862 );
3863 p_stop_time :=
3864 TO_DATE ( TO_CHAR (l_periods (l_period_idx).end_date,
3865 'DD-MM-YYYY'
3866 )
3867 || ' 23:59:59',
3868 'DD-MM-YYYY HH24:MI:SS'
3869 );
3870 l_period_found := TRUE;
3871 END IF;
3872
3873 l_period_idx := l_periods.NEXT (l_period_idx);
3874 END LOOP find_period;
3875 END find_current_period;
3876
3877 FUNCTION cla_enabled (
3878 p_building_block_id IN hxc_time_building_blocks.time_building_block_id%TYPE
3879 )
3880 RETURN BOOLEAN
3881 AS
3882 l_cla_enabled BOOLEAN;
3883 l_cla_terg_id hxc_pref_hierarchies.attribute1%TYPE;
3884
3885 FUNCTION resource_id (
3886 p_building_block_id IN hxc_time_building_blocks.time_building_block_id%TYPE
3887 )
3888 RETURN hxc_time_building_blocks.resource_id%TYPE
3889 AS
3890 l_resource_id hxc_time_building_blocks.resource_id%TYPE;
3891 BEGIN
3892 SELECT MAX (htbb.resource_id)
3893 INTO l_resource_id
3894 FROM hxc_time_building_blocks htbb
3895 WHERE htbb.time_building_block_id = p_building_block_id
3896 AND resource_type = 'PERSON';
3897
3898 RETURN l_resource_id;
3899 END resource_id;
3900 /*
3901 || MAIN
3902 */
3903 BEGIN
3904
3905 <<get_cla_id>>
3906 BEGIN
3907 l_cla_terg_id :=
3908 hxc_preference_evaluation.resource_preferences
3909 (p_resource_id => resource_id
3910 (p_building_block_id),
3911 p_pref_code => 'TS_PER_AUDIT_REQUIREMENTS',
3912 p_attribute_n => 1,
3913 p_evaluation_date => SYSDATE
3914 );
3915 EXCEPTION
3916 WHEN OTHERS
3917 THEN
3918 -- Since we want to be as restrictive as possible, and CLA ON is more
3919 -- restrictive than CLA OFF, we just emulate CLA ON when no CLA
3920 -- preference has been found by setting the id to -1.
3921 l_cla_terg_id := -1;
3922 END get_cla_id;
3923
3924 IF (l_cla_terg_id IS NOT NULL)
3925 THEN
3926 l_cla_enabled := TRUE;
3927 ELSE
3928 l_cla_enabled := FALSE;
3929 END IF;
3930
3931 RETURN l_cla_enabled;
3932 END cla_enabled;
3933
3934
3935 -----------------------------------------------------------------------------
3936 -- Type: Procedure
3937 -- Scope: Public
3938 -- Name: get_past_future_limits
3939 -- IN Parameters: p_resource_id, p_timecard_start_time, p_timecard_stop_time
3940 -- IN OUT Param : p_messages
3941 --
3942 -- Description: Public Procedure that can be used to get the past and future
3943 -- dates till which a timecard can be created/updated through API
3944 --
3945 -- Procedure added for bug 8900783
3946 -----------------------------------------------------------------------------
3947 PROCEDURE get_past_future_limits (p_resource_id IN hxc_time_building_blocks.resource_id%TYPE,
3948 p_timecard_start_time IN date,
3949 p_timecard_stop_time IN date,
3950 p_messages IN OUT NOCOPY hxc_message_table_type)
3951 IS
3952 l_index BINARY_INTEGER;
3953 l_pref_table hxc_preference_evaluation.t_pref_table;
3954 l_num_past_days NUMBER;
3955 l_num_future_days NUMBER;
3956 l_past_date_limit DATE;
3957 l_future_date_limit DATE;
3958
3959 BEGIN
3960
3961 hxc_preference_evaluation.resource_preferences(
3962 p_resource_id => p_resource_id
3963 ,p_pref_code_list => 'TC_W_TCRD_ST_ALW_EDITS'
3964 ,p_pref_table => l_pref_table );
3965
3966 l_index := l_pref_table.FIRST;
3967
3968 WHILE ( l_index IS NOT NULL )
3969 LOOP
3970
3971 IF ( l_pref_table(l_index).preference_code = 'TC_W_TCRD_ST_ALW_EDITS' )
3972 THEN
3973 l_num_future_days := l_pref_table(l_index).attribute11 ;
3974 l_num_past_days := l_pref_table(l_index).attribute6 ;
3975
3976 END IF;
3977 l_index := l_pref_table.NEXT(l_index);
3978
3979 END LOOP;
3980
3981 IF g_debug THEN
3982 hr_utility.trace('l_num_past_days=' || l_num_past_days);
3983 hr_utility.trace('l_num_future_days=' || l_num_future_days);
3984 END IF;
3985
3986 IF l_num_past_days IS NOT NULL
3987 THEN
3988 l_past_date_limit := SYSDATE - TO_NUMBER(l_num_past_days);
3989 ELSE
3990 l_past_date_limit := hr_general.START_OF_TIME;
3991 END IF;
3992
3993 IF l_num_future_days IS NOT NULL
3994 THEN
3995 l_future_date_limit := SYSDATE + TO_NUMBER(l_num_future_days);
3996 ELSE
3997 l_future_date_limit := hr_general.END_OF_TIME;
3998 END IF;
3999
4000 l_past_date_limit := TRUNC(l_past_date_limit);
4001 l_future_date_limit := TRUNC(l_future_date_limit);
4002
4003 IF (p_timecard_start_time < l_past_date_limit
4004 OR
4005 p_timecard_stop_time > l_future_date_limit ) THEN
4006
4007 hr_utility.trace('Timecard not within the allowed past and future days limit');
4008
4009 hxc_timecard_message_helper.addErrorToCollection
4010 (p_messages,
4011 'HXC_NO_PERIODS_TO_CREATE',
4012 hxc_timecard.c_error,
4013 null,
4014 null,
4015 hxc_timecard.c_hxc,
4016 null,
4017 null,
4018 null,
4019 null
4020 );
4021
4022 END IF;
4023
4024 IF g_debug THEN
4025 hr_utility.trace(' l_past_date_limit =' || to_char(l_past_date_limit, 'DD-MON-YYYY'));
4026 hr_utility.trace(' l_future_date_limit=' ||to_char(l_future_date_limit, 'DD-MON-YYYY') );
4027 END IF;
4028
4029 END get_past_future_limits;
4030
4031 END hxc_timestore_deposit_util;