[Home] [Help]
PACKAGE BODY: APPS.HXC_TIMESTORE_DEPOSIT_UTIL
Source
1 PACKAGE BODY hxc_timestore_deposit_util AS
2 /* $Header: hxctsdputil.pkb 120.17 2007/01/12 09:14:57 mbhammar noship $ */
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 BEGIN
188 g_debug := hr_utility.debug_enabled;
189
190 IF g_debug
191 THEN
192 l_proc := g_package || 'begin_approval';
193 hr_utility.set_location ('Entering:' || l_proc, 10);
194 END IF;
195
196 l_timecard_index := hxc_timecard_block_utils.find_active_timecard_index(p_blocks);
197
198 hxc_timecard_properties.get_preference_properties
199 (p_validate => hxc_timecard.c_no,
200 p_resource_id => p_blocks(l_timecard_index).resource_id,
201 p_timecard_start_time => hxc_timecard_block_utils.date_value(p_blocks(l_timecard_index).start_time),
202 p_timecard_stop_time => hxc_timecard_block_utils.date_value(p_blocks(l_timecard_index).stop_time),
203 p_for_timecard => false,
204 p_timecard_bb_id => p_timecard_id,
205 p_timecard_bb_ovn => p_blocks(l_timecard_index).object_version_number,
206 p_messages => l_messages,
207 p_property_table => l_timecard_props
208 );
209
210 hxc_timecard_message_helper.processerrors
211 (p_messages => l_messages);
212
213 -- Determine if this is a resubmitted timecard
214 l_timecard_index :=
215 hxc_timecard_block_utils.find_active_timecard_index (p_blocks);
216
217 IF (hxc_timecard_block_utils.date_value
218 (p_blocks (l_timecard_index).date_to
219 ) = hr_general.end_of_time
220 )
221 THEN
222 l_resubmit :=
223 hxc_timecard_approval.is_timecard_resubmitted
224 (p_blocks (l_timecard_index).time_building_block_id,
225 p_blocks (l_timecard_index).object_version_number,
226 p_blocks (l_timecard_index).resource_id,
227 fnd_date.canonical_to_date
228 (p_blocks (l_timecard_index).start_time
229 ),
230 fnd_date.canonical_to_date
231 (p_blocks (l_timecard_index).stop_time
232 )
233 );
234 ELSE
235 l_resubmit := hxc_timecard.c_delete;
236 END IF;
237
238 l_item_key :=
239 hxc_timecard_approval.begin_approval
240 (p_blocks => p_blocks,
241 p_item_type => 'HXCEMP',
242 p_process_name => 'HXC_APPROVAL',
243 p_resubmitted => l_resubmit,
244 p_timecard_props => l_timecard_props,
245 p_messages => l_messages
246 );
247
248 hxc_timecard_message_helper.processerrors
249 (p_messages => l_messages);
250
251 hxc_timecard_summary_pkg.update_summary_row
252 (p_timecard_id => p_timecard_id,
253 p_approval_item_type => 'HXCEMP',
254 p_approval_process_name => 'HXC_APPROVAL',
255 p_approval_item_key => l_item_key
256 );
257
258 IF g_debug
259 THEN
260 hr_utility.set_location ('Leaving:' || l_proc, 100);
261 END IF;
262 END begin_approval;
263
264 -----------------------------------------------------------------------------
265 -- Type: Procedure
266 -- Scope: Public
267 -- Name: save_timecard
268 -- IN OUT Parameters: p_blocks -> The Timecard structure you want Save
269 -- p_attributes -> The Timecard's attributes you want to Save
270 -- p_messages -> The messages returned from the Save process
271 -- OUT Parameters: p_timecard_id -> The timecard_id of the saved Timecard
272 -- p_timecard_ovn -> the OVN of the saved Timecard
273 --
274 -- Description: Private Procedure that can will be used to Save the timecard
275 -- (as oppose to Submit it). This will store the Timecard in the
276 -- DB with a status of WORKING. This whole procedure is mimicking
277 -- the Save as it happens in the deposit wrapper HXC_TIMECARD.
278 -----------------------------------------------------------------------------
279 PROCEDURE save_timecard (
280 p_blocks IN OUT NOCOPY hxc_block_table_type,
281 p_attributes IN OUT NOCOPY hxc_attribute_table_type,
282 p_messages IN OUT NOCOPY hxc_message_table_type,
283 p_timecard_id OUT NOCOPY hxc_time_building_blocks.time_building_block_id%TYPE,
284 p_timecard_ovn OUT NOCOPY hxc_time_building_blocks.object_version_number%TYPE
285 )
286 IS
287 l_timecard_blocks hxc_timecard.block_list;
288 l_day_blocks hxc_timecard.block_list;
289 l_detail_blocks hxc_timecard.block_list;
290 l_transaction_info hxc_timecard.transaction_info;
291 l_timecard_props hxc_timecard_prop_table_type;
292 l_proc VARCHAR2 (50) := g_package || 'save_timecard';
293 l_timecard_index NUMBER;
294 l_old_style_blks hxc_self_service_time_deposit.timecard_info;
295 l_old_style_attrs hxc_self_service_time_deposit.building_block_attribute_info;
296 l_old_messages hxc_self_service_time_deposit.message_table;
297 BEGIN
298 fnd_msg_pub.initialize;
299 hxc_timecard_message_helper.initializeerrors;
300 hxc_time_category_utils_pkg.push_timecard (p_blocks, p_attributes);
301 p_messages := hxc_message_table_type ();
302 hxc_timecard_block_utils.initialize_timecard_index;
303 -- Check input parameters
304 hxc_deposit_checks.check_inputs (p_blocks => p_blocks,
305 p_attributes => p_attributes,
306 p_deposit_mode => hxc_timecard.c_save,
307 p_template => hxc_timecard.c_no,
308 p_messages => p_messages
309 );
310 hxc_timecard_message_helper.processerrors (p_messages => p_messages);
311 -- First we are getting the preferences
312 l_timecard_index :=
313 hxc_timecard_block_utils.find_active_timecard_index (p_blocks);
314 hxc_timecard_properties.get_preference_properties
315 (p_validate => hxc_timecard.c_yes,
316 p_resource_id => p_blocks (l_timecard_index).resource_id,
317 p_timecard_start_time => fnd_date.canonical_to_date
318 (p_blocks (l_timecard_index).start_time
319 ),
320 p_timecard_stop_time => fnd_date.canonical_to_date
321 (p_blocks (l_timecard_index).stop_time
322 ),
323 p_for_timecard => FALSE,
324 p_messages => p_messages,
325 p_property_table => l_timecard_props
326 );
327 hxc_timecard_message_helper.processerrors (p_messages => p_messages);
328 -- p_messages.DELETE;
329 -- Sort blocks
330 hxc_timecard_block_utils.sort_blocks
331 (p_blocks => p_blocks,
332 p_timecard_blocks => l_timecard_blocks,
333 p_day_blocks => l_day_blocks,
334 p_detail_blocks => l_detail_blocks
335 );
336 -- Perform basic checks
337 hxc_deposit_checks.perform_checks (p_blocks => p_blocks,
338 p_attributes => p_attributes,
339 p_timecard_props => l_timecard_props,
340 p_days => l_day_blocks,
341 p_details => l_detail_blocks,
342 p_messages => p_messages
343 );
344 hxc_timecard_message_helper.processerrors (p_messages => p_messages);
345 -- Add the security attributes
346 -- ARR: 115.26 Add message structure
347 hxc_security.add_security_attribute
348 (p_blocks => p_blocks,
349 p_attributes => p_attributes,
350 p_timecard_props => l_timecard_props,
351 p_messages => p_messages
352 );
353 hxc_timecard_message_helper.processerrors (p_messages => p_messages);
354 -- Translate any aliases
355 hxc_timecard_deposit_common.alias_translation
356 (p_blocks => p_blocks,
357 p_attributes => p_attributes,
358 p_messages => p_messages
359 );
360 hxc_timecard_message_helper.processerrors (p_messages => p_messages);
361 -- Set the block and attribute update process flags Based on the data sent
362 -- and in the db
363 hxc_block_attribute_update.set_process_flags
364 (p_blocks => p_blocks,
365 p_attributes => p_attributes
366 );
367 hxc_timecard_attribute_utils.remove_deleted_attributes
368 (p_attributes => p_attributes);
369 /*
370 Validate the set up for the user
371
372 validate_setup
373 (p_deposit_mode => hxc_timecard.c_save
374 ,p_blocks => p_blocks
375 ,p_attributes => p_attributes
376 ,p_messages => p_messages
377 );
378
379 hxc_timecard_message_helper.processerrors
380 (p_messages => p_messages);
381 */ -- Call time entry rules for save
382
383 l_old_style_blks :=
384 hxc_timecard_block_utils.convert_to_dpwr_blocks (p_blocks);
385 l_old_style_attrs :=
386 hxc_timecard_attribute_utils.convert_to_dpwr_attributes (p_attributes);
387
388 hxc_time_entry_rules_utils_pkg.execute_time_entry_rules
389 (p_operation => hxc_timecard.c_save,
390 p_time_building_blocks => l_old_style_blks,
391 p_time_attributes => l_old_style_attrs,
392 p_messages => l_old_messages,
393 p_resubmit => hxc_timecard.c_no,
394 p_blocks => p_blocks,
395 p_attributes => p_attributes
396 );
397 hxc_timecard_message_utils.append_old_messages
398 (p_messages => p_messages,
399 p_old_messages => l_old_messages,
400 p_retrieval_process_id => NULL
401 );
402 hxc_timecard_message_helper.processerrors (p_messages => p_messages);
403
404 -- Store blocks and attributes
405 IF hxc_timecard_message_helper.noerrors
406 THEN
407 hxc_timecard_deposit.EXECUTE
408 (p_blocks => p_blocks,
409 p_attributes => p_attributes,
410 p_timecard_blocks => l_timecard_blocks,
411 p_day_blocks => l_day_blocks,
412 p_detail_blocks => l_detail_blocks,
413 p_messages => p_messages,
414 p_transaction_info => l_transaction_info
415 );
416 hxc_timecard_message_helper.processerrors (p_messages => p_messages);
417 --
418 -- Maintain summary table
419 --
420 hxc_timecard_summary_api.timecard_deposit
421 (p_blocks => p_blocks,
422 p_approval_item_type => NULL,
423 p_approval_process_name => NULL,
424 p_approval_item_key => NULL,
425 p_tk_audit_item_type => NULL,
426 p_tk_audit_process_name => NULL,
427 p_tk_audit_item_key => NULL
428 );
429 hxc_timecard_audit.maintain_latest_details (p_blocks => p_blocks);
430 END IF;
431
432 -- get all the errors
433 p_messages := hxc_timecard_message_helper.getmessages;
434 p_timecard_id :=
435 p_blocks
436 (hxc_timecard_block_utils.find_active_timecard_index (p_blocks)
437 ).time_building_block_id;
438 p_timecard_ovn :=
439 p_blocks
440 (hxc_timecard_block_utils.find_active_timecard_index (p_blocks)
441 ).object_version_number;
442 END save_timecard;
443
444 -----------------------------------------------------------------------------
445 -- Type: Procedure
446 -- Scope: Public
447 -- Name: submit_timecard
448 -- IN OUT Parameters: p_item_type -> Item Type to be used by approval process
449 -- p_approval_prc -> Approval Process used for approval
450 -- p_template -> Is this TC a template, Y(es) or N(o)
451 -- p_mode -> 'SUBMIT' or 'AUDIT'
452 -- p_blocks -> The Timecard structure you want Submit
453 -- p_attributes -> The Timecard's attributes you want to
454 -- Submit
455 -- p_messages -> The messages returned from the Submit
456 -- process
457 -- OUT Parameters: p_timecard_id -> The timecard_id of the submitted Timecard
458 -- p_timecard_ovn -> the OVN of the submitted Timecard
459 --
460 -- Description: Private Procedure that can will be used to Submit the timecard
461 -- This will store the Timecard in the DB with a status of
462 -- SUBMITTED. This whole procedure is mimicking
463 -- the Submit as it happens in the deposit wrapper HXC_TIMECARD.
464 -----------------------------------------------------------------------------
465 PROCEDURE submit_timecard (
466 p_item_type IN wf_items.item_type%TYPE,
467 p_approval_prc IN wf_process_activities.process_name%TYPE,
468 p_template IN VARCHAR2,
469 p_mode IN VARCHAR2,
470 p_retrieval_process IN hxc_retrieval_processes.NAME%TYPE,
471 p_blocks IN OUT NOCOPY hxc_block_table_type,
472 p_attributes IN OUT NOCOPY hxc_attribute_table_type,
473 p_messages IN OUT NOCOPY hxc_message_table_type,
474 p_timecard_id OUT NOCOPY hxc_time_building_blocks.time_building_block_id%TYPE,
475 p_timecard_ovn OUT NOCOPY hxc_time_building_blocks.object_version_number%TYPE
476 )
477 IS
478 l_timecard_blocks hxc_timecard.block_list;
479 l_day_blocks hxc_timecard.block_list;
480 l_detail_blocks hxc_timecard.block_list;
481 l_transaction_info hxc_timecard.transaction_info;
482 l_old_transaction_info hxc_deposit_wrapper_utilities.t_transaction;
483 l_timecard_props hxc_timecard_prop_table_type;
484 l_proc VARCHAR2 (50)
485 := g_package || 'submit_timecard';
486 l_can_deposit BOOLEAN := TRUE;
487 l_resubmit VARCHAR2 (10) := hxc_timecard.c_no;
488 l_timecard_index NUMBER;
489 l_rollback BOOLEAN := FALSE;
490 l_item_key wf_items.item_key%TYPE := NULL;
491 l_mode VARCHAR2 (30);
492 BEGIN
493 fnd_msg_pub.initialize;
494 hxc_timecard_message_helper.initializeerrors;
495 p_messages := hxc_message_table_type ();
496 hxc_timecard_block_utils.initialize_timecard_index;
497 -- Check input parameters
498 hxc_deposit_checks.check_inputs
499 (p_blocks => p_blocks,
500 p_attributes => p_attributes,
501 p_deposit_mode => hxc_timecard.c_submit,
502 p_template => hxc_timecard.c_no,
503 p_messages => p_messages
504 );
505 hxc_timecard_message_helper.processerrors (p_messages => p_messages);
506 -- Determine if this is a resubmitted timecard
507 l_timecard_index :=
508 hxc_timecard_block_utils.find_active_timecard_index (p_blocks);
509
510 IF (hxc_timecard_block_utils.date_value
511 (p_blocks (l_timecard_index).date_to
512 ) = hr_general.end_of_time
513 )
514 THEN
515 l_resubmit :=
516 hxc_timecard_approval.is_timecard_resubmitted
517 (p_blocks (l_timecard_index).time_building_block_id,
518 p_blocks (l_timecard_index).object_version_number,
519 p_blocks (l_timecard_index).resource_id,
520 fnd_date.canonical_to_date
521 (p_blocks (l_timecard_index).start_time
522 ),
523 fnd_date.canonical_to_date
524 (p_blocks (l_timecard_index).stop_time
525 )
526 );
527 ELSE
528 l_resubmit := hxc_timecard.c_delete;
529 END IF;
530
531 -- Obtain the timecard properties
532 hxc_timecard_properties.get_preference_properties
533 (p_validate => hxc_timecard.c_yes,
534 p_resource_id => p_blocks (l_timecard_index).resource_id,
535 p_timecard_start_time => fnd_date.canonical_to_date
536 (p_blocks (l_timecard_index).start_time
537 ),
538 p_timecard_stop_time => fnd_date.canonical_to_date
539 (p_blocks (l_timecard_index).stop_time
540 ),
541 p_for_timecard => FALSE,
542 p_messages => p_messages,
543 p_property_table => l_timecard_props
544 );
545 hxc_timecard_message_helper.processerrors (p_messages => p_messages);
546 -- Sort the blocks - needed for deposit and all sorts of short cuts!
547 hxc_timecard_block_utils.sort_blocks
548 (p_blocks => p_blocks,
549 p_timecard_blocks => l_timecard_blocks,
550 p_day_blocks => l_day_blocks,
551 p_detail_blocks => l_detail_blocks
552 );
553 --
554 -- Main deposit controls
555 -- ^^^^^^^^^^^^^^^^^^^^^
556 -- Reform time data, if required
557 -- e.g Denormalize time data
558 --
559 hxc_block_attribute_update.denormalize_time (p_blocks => p_blocks,
560 p_mode => 'ADD'
561 );
562
563 --
564 -- Perform basic checks, e.g.
565 -- Are there any other timecards for this period?
566 --
567 IF (p_template = hxc_timecard.c_no)
568 THEN
569 hxc_deposit_checks.perform_checks
570 (p_blocks => p_blocks,
571 p_attributes => p_attributes,
572 p_timecard_props => l_timecard_props,
573 p_days => l_day_blocks,
574 p_details => l_detail_blocks,
575 p_messages => p_messages
576 );
577 hxc_timecard_message_helper.processerrors (p_messages => p_messages);
578 END IF;
579
580 -- Add the security attributes
581 -- ARR: 115.26 Add message structure
582 hxc_security.add_security_attribute
583 (p_blocks => p_blocks,
584 p_attributes => p_attributes,
585 p_timecard_props => l_timecard_props,
586 p_messages => p_messages
587 );
588 hxc_timecard_message_helper.processerrors (p_messages => p_messages);
589 -- Translate any aliases
590 hxc_timecard_deposit_common.alias_translation
591 (p_blocks => p_blocks,
592 p_attributes => p_attributes,
593 p_messages => p_messages
594 );
595 hxc_timecard_message_helper.processerrors (p_messages => p_messages);
596 -- Set the block and attribute update process flags based on the data sent
597 -- and in the db
598 hxc_block_attribute_update.set_process_flags
599 (p_blocks => p_blocks,
600 p_attributes => p_attributes
601 );
602 hxc_timecard_attribute_utils.remove_deleted_attributes
603 (p_attributes => p_attributes);
604 -- Perform process checks
605 hxc_deposit_checks.perform_process_checks
606 (p_blocks => p_blocks,
607 p_attributes => p_attributes,
608 p_timecard_props => l_timecard_props,
609 p_days => l_day_blocks,
610 p_details => l_detail_blocks,
611 p_template => hxc_timecard.c_no,
612 p_deposit_mode => hxc_timecard.c_submit,
613 p_messages => p_messages
614 );
615 hxc_timecard_message_helper.processerrors (p_messages => p_messages);
616
617 -- Validate blocks, attributes
618 IF (p_mode <> hxc_timestore_deposit.c_migration)
619 THEN
620 hxc_timecard_validation.deposit_validation
621 (p_blocks => p_blocks,
622 p_attributes => p_attributes,
623 p_messages => p_messages,
624 p_props => l_timecard_props,
625 p_deposit_mode => p_mode,
626 p_template => hxc_timecard.c_no,
627 p_resubmit => l_resubmit,
628 p_can_deposit => l_can_deposit
629 );
630 ELSE -- minimal validation for migration
631 hxc_timecard_validation.data_set_validation
632 (p_blocks => p_blocks,
633 p_messages => p_messages
634 );
635 END IF;
636
637 hxc_timecard_message_helper.processerrors (p_messages => p_messages);
638 -- Validate the set up for the user Do this only for timecards, and not
639 -- for templates.
640 /*
641 hxc_timecard_deposit_common.validate_setup
642 (p_deposit_mode => hxc_timecard.c_submit
643 ,p_blocks => p_blocks
644 ,p_attributes => p_attributes
645 ,p_messages => p_messages
646 );
647
648 hxc_timecard_message_helper.processerrors
649 (p_messages => p_messages);
650 */
651 -- Reform time data, if required e.g Denormalize time data
652 hxc_block_attribute_update.denormalize_time (p_blocks => p_blocks,
653 p_mode => 'REMOVE'
654 );
655
656 IF perform_audit (p_props => l_timecard_props, p_blocks => p_blocks)
657 THEN
658 -- Get the messages to perform the audit check
659 p_messages := hxc_timecard_message_helper.getmessages;
660 -- Perform Audit Checks
661 -- Mental Note on how this works:)
662 -- hxc_timecard_validation.deposit_validation called above, raises
663 -- proper errors if something is really wrong, e.g. during the
664 -- project or payroll validation. However for Change and Late Audit
665 -- (CLA), it stores special messages in message table p_messages.
666 -- These messages have a message_level of REASON, are not considered
667 -- errors and will not be recognized by
668 -- hxc_timecard_message_helper.noerrors as errors.
669 -- hxc_deposit_checks.audit_checks transforms these REASON errors
670 -- into proper errors that will be caught by
671 -- hxc_timecard_message_helper.noerrors.
672 -- So effectively what we are doing here is converting the audit
673 -- message into errors when the user calls the API and AUDIT is
674 -- switched on in the preferences of the employee/resource.
675 -- If the user calls the API and AUDIT is not set, we completely
676 -- ignore the auditmessages and continue with the submit.
677 hxc_deposit_checks.audit_checks (p_blocks => p_blocks,
678 p_attributes => p_attributes,
679 p_messages => p_messages
680 );
681 hxc_timecard_message_helper.processerrors (p_messages => p_messages);
682 END IF;
683
684 -- Store blocks and attributes
685 IF hxc_timecard_message_helper.noerrors
686 THEN
687 hxc_timecard_deposit.EXECUTE
688 (p_blocks => p_blocks,
689 p_attributes => p_attributes,
690 p_timecard_blocks => l_timecard_blocks,
691 p_day_blocks => l_day_blocks,
692 p_detail_blocks => l_detail_blocks,
693 p_messages => p_messages,
694 p_transaction_info => l_transaction_info
695 );
696 hxc_timecard_message_helper.processerrors (p_messages => p_messages);
697 -- set the out parameters
698 p_timecard_id :=
699 p_blocks
700 (hxc_timecard_block_utils.find_active_timecard_index (p_blocks)
701 ).time_building_block_id;
702 p_timecard_ovn :=
703 p_blocks
704 (hxc_timecard_block_utils.find_active_timecard_index (p_blocks)
705 ).object_version_number;
706
707 IF ( (p_template <> hxc_timecard.c_yes)
708 AND (hxc_timecard_message_helper.noerrors)
709 )
710 THEN
711 --
712 -- Maintain summary table
713 --
714 IF (p_mode = hxc_timestore_deposit.c_migration)
715 THEN
716 l_mode := g_oit_migration;
717 ELSE
718 l_mode := hxc_timecard_summary_pkg.c_normal_mode;
719 END IF;
720
721 hxc_timecard_summary_api.timecard_deposit
722 (p_blocks => p_blocks,
723 p_mode => l_mode,
724 p_approval_item_type => NULL,
725 p_approval_process_name => NULL,
726 p_approval_item_key => NULL,
727 p_tk_audit_item_type => NULL,
728 p_tk_audit_process_name => NULL,
729 p_tk_audit_item_key => NULL
730 );
731 hxc_timecard_audit.maintain_latest_details (p_blocks => p_blocks);
732 hxc_timecard_message_helper.processerrors
733 (p_messages => p_messages);
734
735 IF (p_mode <> hxc_timestore_deposit.c_migration)
736 THEN
737 l_item_key :=
738 hxc_timecard_approval.begin_approval
739 (p_blocks => p_blocks,
740 p_item_type => p_item_type,
741 p_process_name => p_approval_prc,
742 p_resubmitted => l_resubmit,
743 p_timecard_props => l_timecard_props,
744 p_messages => p_messages
745 );
746
747 hxc_timecard_message_helper.processerrors
748 (p_messages => p_messages);
749
750 hxc_timecard_summary_pkg.update_summary_row
751 (p_timecard_id => p_timecard_id,
752 p_approval_item_type => p_item_type,
753 p_approval_process_name => p_approval_prc,
754 p_approval_item_key => l_item_key
755 );
756 END IF;
757 ELSIF ( (p_template = hxc_timecard.c_yes)
758 AND (hxc_timecard_message_helper.noerrors)
759 )
760 THEN
761 hxc_template_summary_api.template_deposit
762 (p_blocks => p_blocks,
763 p_attributes => p_attributes,
764 p_template_id => p_timecard_id
765 );
766 END IF;
767 END IF;
768
769 -- Audit this transaction
770 hxc_timecard_audit.audit_deposit
771 (p_transaction_info => l_transaction_info,
772 p_messages => p_messages
773 );
774 hxc_timecard_message_helper.processerrors (p_messages => p_messages);
775
776 IF ( (p_mode = hxc_timestore_deposit.c_migration)
777 AND (hxc_timecard_message_helper.noerrors)
778 )
779 THEN
780 l_old_transaction_info :=
781 convert_new_trans_info_to_old
782 (p_transaction_info => l_transaction_info);
783 hxc_deposit_wrapper_utilities.audit_transaction
784 (p_effective_date => SYSDATE,
785 p_transaction_type => 'RETRIEVAL',
786 p_transaction_process_id => get_retrieval_process_id
787 (p_retrieval_process_name => p_retrieval_process
788 ),
789 p_overall_status => 'SUCCESS',
790 p_transaction_tab => l_old_transaction_info
791 );
792 hxc_timecard_message_helper.processerrors (p_messages => p_messages);
793 END IF;
794
795 -- get all the errors
796 p_messages := hxc_timecard_message_helper.getmessages;
797 END submit_timecard;
798
799 -----------------------------------------------------------------------------
800 -- Type: Function
801 -- Scope: Public
802 -- Name: convert_tbb_to_type
803 -- Returns: hxc_block_table_type
804 -- IN Parameters: p_blocks -> The PL/SQL Timecard structure you want to convert
805 --
806 -- Description: Private Function that will convert the old PL/SQL Timecard
807 -- structure to the new TYPE that is then returned
808 -- FYI: This is the reverse function of convert_to_dpwr_blocks
809 -- that can be found in hxc_timecard_block_utils. It is not
810 -- defined in that procedure as it is only needed for this API
811 -- hence we define it here as a provate function
812 -----------------------------------------------------------------------------
813 FUNCTION convert_tbb_to_type (
814 p_blocks IN hxc_self_service_time_deposit.timecard_info
815 )
816 RETURN hxc_block_table_type
817 IS
818 l_proc VARCHAR2 (72);
819 l_blocks hxc_block_table_type;
820 l_index PLS_INTEGER;
821 BEGIN
822 g_debug := hr_utility.debug_enabled;
823
824 IF g_debug
825 THEN
826 l_proc := g_package || 'convert_tbb_to_type';
827 hr_utility.set_location ('Entering:' || l_proc, 10);
828 END IF;
829
830 -- Initialize the collection
831 l_blocks := hxc_block_table_type ();
832 l_index := p_blocks.FIRST;
833
834 LOOP
835 EXIT WHEN NOT p_blocks.EXISTS (l_index);
836 l_blocks.EXTEND;
837 l_blocks (l_blocks.LAST) :=
838 hxc_block_type
839 (p_blocks (l_index).time_building_block_id,
840 p_blocks (l_index).TYPE,
841 p_blocks (l_index).measure,
842 p_blocks (l_index).unit_of_measure,
843 fnd_date.date_to_canonical (p_blocks (l_index).start_time),
844 fnd_date.date_to_canonical (p_blocks (l_index).stop_time),
845 p_blocks (l_index).parent_building_block_id,
846 p_blocks (l_index).parent_is_new,
847 p_blocks (l_index).SCOPE,
848 p_blocks (l_index).object_version_number,
849 p_blocks (l_index).approval_status,
850 p_blocks (l_index).resource_id,
851 p_blocks (l_index).resource_type,
852 p_blocks (l_index).approval_style_id,
853 fnd_date.date_to_canonical (p_blocks (l_index).date_from),
854 fnd_date.date_to_canonical (p_blocks (l_index).date_to),
855 p_blocks (l_index).comment_text,
856 p_blocks (l_index).parent_building_block_ovn,
857 p_blocks (l_index).NEW,
858 p_blocks (l_index).changed,
859 NULL, -- Process
860 p_blocks (l_index).application_set_id,
861 NULL -- Can not set this from old structure at the moment
862 );
863 l_index := p_blocks.NEXT (l_index);
864 END LOOP;
865
866 IF g_debug
867 THEN
868 hr_utility.set_location ( ' returning l_blocks.count = '
869 || l_blocks.COUNT,
870 20
871 );
872 hr_utility.set_location ('Leaving: ' || l_proc, 30);
873 END IF;
874
875 RETURN l_blocks;
876 END convert_tbb_to_type;
877
878 -----------------------------------------------------------------------------
879 -- Type: Function
880 -- Scope: Public
881 -- Name: convert_new_trans_info_to_old
882 -- Returns: hxc_deposit_wrapper_utilities.t_transaction
883 -- IN Parameters: p_trans_info -> The PL/SQL Transaction info table you want to
884 -- convert
885 -- Description: Public Function that will convert the new PL/SQL Transaction
886 -- PL/SQL table to the old PL/SQL Table that is then returned
887 -----------------------------------------------------------------------------
888 FUNCTION convert_new_trans_info_to_old (
889 p_transaction_info IN hxc_timecard.transaction_info
890 )
891 RETURN hxc_deposit_wrapper_utilities.t_transaction
892 IS
893 l_proc VARCHAR2 (72);
894 l_transaction_info hxc_deposit_wrapper_utilities.t_transaction;
895 l_index PLS_INTEGER;
896 BEGIN
897 g_debug := hr_utility.debug_enabled;
898
899 IF g_debug
900 THEN
901 l_proc := g_package || 'convert_new_trans_info_to_old';
902 hr_utility.set_location ('Entering:' || l_proc, 10);
903 END IF;
904
905 l_index := p_transaction_info.FIRST;
906
907 LOOP
908 EXIT WHEN NOT p_transaction_info.EXISTS (l_index);
909 l_transaction_info (l_index).txd_id :=
910 p_transaction_info (l_index).transaction_detail_id;
911 l_transaction_info (l_index).tbb_id :=
912 p_transaction_info (l_index).time_building_block_id;
913 l_transaction_info (l_index).tbb_ovn :=
914 p_transaction_info (l_index).object_version_number;
915 l_transaction_info (l_index).status :=
916 p_transaction_info (l_index).status;
917 l_transaction_info (l_index).exception_desc :=
918 p_transaction_info (l_index).exception_desc;
919 l_index := p_transaction_info.NEXT (l_index);
920 END LOOP;
921
922 IF g_debug
923 THEN
924 hr_utility.set_location
925 ( ' returning l_transaction_info.count = '
926 || l_transaction_info.COUNT,
927 20
928 );
929 hr_utility.set_location ('Leaving: ' || l_proc, 30);
930 END IF;
931
932 RETURN l_transaction_info;
933 END convert_new_trans_info_to_old;
934
935 PROCEDURE update_value (
936 p_attributes IN OUT NOCOPY hxc_attribute_table_type,
937 p_index IN NUMBER,
938 p_segment IN hxc_mapping_components.SEGMENT%TYPE,
939 p_value IN hxc_time_attributes.attribute1%TYPE
940 )
941 IS
942 BEGIN
943 IF (p_segment = 'ATTRIBUTE1')
944 THEN
945 p_attributes (p_index).attribute1 := p_value;
946 ELSIF (p_segment = 'ATTRIBUTE2')
947 THEN
948 p_attributes (p_index).attribute2 := p_value;
949 ELSIF (p_segment = 'ATTRIBUTE3')
950 THEN
951 p_attributes (p_index).attribute3 := p_value;
952 ELSIF (p_segment = 'ATTRIBUTE4')
953 THEN
954 p_attributes (p_index).attribute4 := p_value;
955 ELSIF (p_segment = 'ATTRIBUTE5')
956 THEN
957 p_attributes (p_index).attribute5 := p_value;
958 ELSIF (p_segment = 'ATTRIBUTE6')
959 THEN
960 p_attributes (p_index).attribute6 := p_value;
961 ELSIF (p_segment = 'ATTRIBUTE7')
962 THEN
963 p_attributes (p_index).attribute7 := p_value;
964 ELSIF (p_segment = 'ATTRIBUTE8')
965 THEN
966 p_attributes (p_index).attribute8 := p_value;
967 ELSIF (p_segment = 'ATTRIBUTE9')
968 THEN
969 p_attributes (p_index).attribute9 := p_value;
970 ELSIF (p_segment = 'ATTRIBUTE10')
971 THEN
972 p_attributes (p_index).attribute10 := p_value;
973 ELSIF (p_segment = 'ATTRIBUTE11')
974 THEN
975 p_attributes (p_index).attribute11 := p_value;
976 ELSIF (p_segment = 'ATTRIBUTE12')
977 THEN
978 p_attributes (p_index).attribute12 := p_value;
979 ELSIF (p_segment = 'ATTRIBUTE13')
980 THEN
981 p_attributes (p_index).attribute13 := p_value;
982 ELSIF (p_segment = 'ATTRIBUTE14')
983 THEN
984 p_attributes (p_index).attribute14 := p_value;
985 ELSIF (p_segment = 'ATTRIBUTE15')
986 THEN
987 p_attributes (p_index).attribute15 := p_value;
988 ELSIF (p_segment = 'ATTRIBUTE16')
989 THEN
990 p_attributes (p_index).attribute16 := p_value;
991 ELSIF (p_segment = 'ATTRIBUTE17')
992 THEN
993 p_attributes (p_index).attribute17 := p_value;
994 ELSIF (p_segment = 'ATTRIBUTE18')
995 THEN
996 p_attributes (p_index).attribute18 := p_value;
997 ELSIF (p_segment = 'ATTRIBUTE19')
998 THEN
999 p_attributes (p_index).attribute19 := p_value;
1000 ELSIF (p_segment = 'ATTRIBUTE20')
1001 THEN
1002 p_attributes (p_index).attribute20 := p_value;
1003 ELSIF (p_segment = 'ATTRIBUTE21')
1004 THEN
1005 p_attributes (p_index).attribute21 := p_value;
1006 ELSIF (p_segment = 'ATTRIBUTE22')
1007 THEN
1008 p_attributes (p_index).attribute22 := p_value;
1009 ELSIF (p_segment = 'ATTRIBUTE23')
1010 THEN
1011 p_attributes (p_index).attribute23 := p_value;
1012 ELSIF (p_segment = 'ATTRIBUTE24')
1013 THEN
1014 p_attributes (p_index).attribute24 := p_value;
1015 ELSIF (p_segment = 'ATTRIBUTE25')
1016 THEN
1017 p_attributes (p_index).attribute25 := p_value;
1018 ELSIF (p_segment = 'ATTRIBUTE26')
1019 THEN
1020 p_attributes (p_index).attribute26 := p_value;
1021 ELSIF (p_segment = 'ATTRIBUTE27')
1022 THEN
1023 p_attributes (p_index).attribute27 := p_value;
1024 ELSIF (p_segment = 'ATTRIBUTE28')
1025 THEN
1026 p_attributes (p_index).attribute28 := p_value;
1027 ELSIF (p_segment = 'ATTRIBUTE29')
1028 THEN
1029 p_attributes (p_index).attribute29 := p_value;
1030 ELSIF (p_segment = 'ATTRIBUTE30')
1031 THEN
1032 p_attributes (p_index).attribute30 := p_value;
1033 ELSIF (p_segment = 'ATTRIBUTE_CATEGORY')
1034 THEN
1035 p_attributes (p_index).attribute_category := p_value;
1036 END IF;
1037 END update_value;
1038
1039 PROCEDURE set_new_attribute_value (
1040 p_attribute IN OUT NOCOPY hxc_attribute_type,
1041 p_segment IN hxc_mapping_components.SEGMENT%TYPE,
1042 p_value IN hxc_time_attributes.attribute1%TYPE,
1043 p_changed IN hxc_time_attributes.attribute1%TYPE
1044 )
1045 IS
1046 BEGIN
1047 IF (p_segment = 'ATTRIBUTE1')
1048 THEN
1049 p_attribute.attribute1 := p_value;
1050 ELSIF (p_segment = 'ATTRIBUTE2')
1051 THEN
1052 p_attribute.attribute2 := p_value;
1053 ELSIF (p_segment = 'ATTRIBUTE3')
1054 THEN
1055 p_attribute.attribute3 := p_value;
1056 ELSIF (p_segment = 'ATTRIBUTE4')
1057 THEN
1058 p_attribute.attribute4 := p_value;
1059 ELSIF (p_segment = 'ATTRIBUTE5')
1060 THEN
1061 p_attribute.attribute5 := p_value;
1062 ELSIF (p_segment = 'ATTRIBUTE6')
1063 THEN
1064 p_attribute.attribute6 := p_value;
1065 ELSIF (p_segment = 'ATTRIBUTE7')
1066 THEN
1067 p_attribute.attribute7 := p_value;
1068 ELSIF (p_segment = 'ATTRIBUTE8')
1069 THEN
1070 p_attribute.attribute8 := p_value;
1071 ELSIF (p_segment = 'ATTRIBUTE9')
1072 THEN
1073 p_attribute.attribute9 := p_value;
1074 ELSIF (p_segment = 'ATTRIBUTE10')
1075 THEN
1076 p_attribute.attribute10 := p_value;
1077 ELSIF (p_segment = 'ATTRIBUTE11')
1078 THEN
1079 p_attribute.attribute11 := p_value;
1080 ELSIF (p_segment = 'ATTRIBUTE12')
1081 THEN
1082 p_attribute.attribute12 := p_value;
1083 ELSIF (p_segment = 'ATTRIBUTE13')
1084 THEN
1085 p_attribute.attribute13 := p_value;
1086 ELSIF (p_segment = 'ATTRIBUTE14')
1087 THEN
1088 p_attribute.attribute14 := p_value;
1089 ELSIF (p_segment = 'ATTRIBUTE15')
1090 THEN
1091 p_attribute.attribute15 := p_value;
1092 ELSIF (p_segment = 'ATTRIBUTE16')
1093 THEN
1094 p_attribute.attribute16 := p_value;
1095 ELSIF (p_segment = 'ATTRIBUTE17')
1096 THEN
1097 p_attribute.attribute17 := p_value;
1098 ELSIF (p_segment = 'ATTRIBUTE18')
1099 THEN
1100 p_attribute.attribute18 := p_value;
1101 ELSIF (p_segment = 'ATTRIBUTE19')
1102 THEN
1103 p_attribute.attribute19 := p_value;
1104 ELSIF (p_segment = 'ATTRIBUTE20')
1105 THEN
1106 p_attribute.attribute20 := p_value;
1107 ELSIF (p_segment = 'ATTRIBUTE21')
1108 THEN
1109 p_attribute.attribute21 := p_value;
1110 ELSIF (p_segment = 'ATTRIBUTE22')
1111 THEN
1112 p_attribute.attribute22 := p_value;
1113 ELSIF (p_segment = 'ATTRIBUTE23')
1114 THEN
1115 p_attribute.attribute23 := p_value;
1116 ELSIF (p_segment = 'ATTRIBUTE24')
1117 THEN
1118 p_attribute.attribute24 := p_value;
1119 ELSIF (p_segment = 'ATTRIBUTE25')
1120 THEN
1121 p_attribute.attribute25 := p_value;
1122 ELSIF (p_segment = 'ATTRIBUTE26')
1123 THEN
1124 p_attribute.attribute26 := p_value;
1125 ELSIF (p_segment = 'ATTRIBUTE27')
1126 THEN
1127 p_attribute.attribute27 := p_value;
1128 ELSIF (p_segment = 'ATTRIBUTE28')
1129 THEN
1130 p_attribute.attribute28 := p_value;
1131 ELSIF (p_segment = 'ATTRIBUTE29')
1132 THEN
1133 p_attribute.attribute29 := p_value;
1134 ELSIF (p_segment = 'ATTRIBUTE30')
1135 THEN
1136 p_attribute.attribute30 := p_value;
1137 ELSIF (p_segment = 'ATTRIBUTE_CATEGORY')
1138 THEN
1139 p_attribute.attribute_category := p_value;
1140 END IF;
1141
1142 IF (p_changed = hxc_timecard.c_yes)
1143 THEN
1144 p_attribute.changed := hxc_timecard.c_yes;
1145 p_attribute.process := hxc_timecard.c_yes;
1146 END IF;
1147 END set_new_attribute_value;
1148
1149 PROCEDURE create_new_attribute (
1150 p_attributes IN OUT NOCOPY hxc_attribute_table_type,
1151 p_app_attributes IN OUT NOCOPY hxc_self_service_time_deposit.app_attributes_info,
1152 p_app_index IN NUMBER
1153 )
1154 IS
1155 l_new_time_attribute_id NUMBER;
1156 l_new_attribute hxc_attribute_type;
1157 l_index NUMBER;
1158 l_attribute_category hxc_bld_blk_info_types.bld_blk_info_type%TYPE;
1159 l_new VARCHAR2 (1);
1160 BEGIN
1161 IF (INSTR (p_app_attributes (p_app_index).bld_blk_info_type, 'Dummy') <
1162 1
1163 )
1164 THEN
1165 l_attribute_category :=
1166 SUBSTR (p_app_attributes (p_app_index).bld_blk_info_type, 1, 30);
1167 ELSE
1168 l_attribute_category := NULL;
1169 END IF;
1170
1171 IF (p_app_attributes (p_app_index).attribute_index IS NULL)
1172 THEN
1173 l_new := hxc_timecard.c_yes;
1174 ELSE
1175 l_new := hxc_timecard.c_no;
1176 END IF;
1177
1178 l_new_attribute :=
1179 hxc_attribute_type
1180 (p_app_attributes (p_app_index).time_attribute_id,
1181 p_app_attributes (p_app_index).building_block_id,
1182 l_attribute_category,
1183 NULL,
1184 NULL,
1185 NULL,
1186 NULL,
1187 NULL,
1188 NULL,
1189 NULL,
1190 NULL,
1191 NULL,
1192 NULL,
1193 NULL,
1194 NULL,
1195 NULL,
1196 NULL,
1197 NULL,
1198 NULL,
1199 NULL,
1200 NULL,
1201 NULL,
1202 NULL,
1203 NULL,
1204 NULL,
1205 NULL,
1206 NULL,
1207 NULL,
1208 NULL,
1209 NULL,
1210 NULL,
1211 NULL,
1212 NULL,
1213 hxc_timecard_attribute_utils.get_bld_blk_info_type_id
1214 (p_app_attributes (p_app_index).bld_blk_info_type
1215 ),
1216 1,
1217 l_new,
1218 hxc_timecard.c_no,
1219 -- Changed: We will decide later if this is a changed Attribute
1220 p_app_attributes (p_app_index).bld_blk_info_type,
1221 hxc_timecard.c_no,
1222 -- Process: We will decide later if this Attribute needs to get processed
1223 NULL
1224 );
1225 l_index := p_app_attributes.FIRST;
1226
1227 LOOP
1228 EXIT WHEN NOT p_app_attributes.EXISTS (l_index);
1229
1230 IF ( (p_app_attributes (l_index).bld_blk_info_type =
1231 p_app_attributes (p_app_index).bld_blk_info_type
1232 )
1233 AND (p_app_attributes (l_index).time_attribute_id =
1234 p_app_attributes (p_app_index).time_attribute_id
1235 )
1236 )
1237 THEN
1238 set_new_attribute_value
1239 (l_new_attribute,
1240 p_app_attributes (l_index).SEGMENT,
1241 p_app_attributes (l_index).attribute_value,
1242 p_app_attributes (l_index).changed
1243 );
1244 p_app_attributes (l_index).updated := 'Y';
1245 END IF;
1246
1247 l_index := p_app_attributes.NEXT (l_index);
1248 END LOOP;
1249
1250 p_attributes.EXTEND ();
1251 p_attributes (p_attributes.LAST) := l_new_attribute;
1252 END create_new_attribute;
1253
1254 PROCEDURE convert_app_attributes_to_type (
1255 p_attributes IN OUT NOCOPY hxc_attribute_table_type,
1256 p_app_attributes IN OUT NOCOPY hxc_self_service_time_deposit.app_attributes_info
1257 )
1258 IS
1259 l_index NUMBER;
1260 BEGIN
1261 l_index := p_app_attributes.FIRST;
1262
1263 LOOP
1264 EXIT WHEN NOT p_app_attributes.EXISTS (l_index);
1265
1266 IF (NVL (p_app_attributes (l_index).updated, 'N') = 'N')
1267 THEN
1268 create_new_attribute (p_attributes => p_attributes,
1269 p_app_attributes => p_app_attributes,
1270 p_app_index => l_index
1271 );
1272 p_app_attributes (l_index).updated := 'Y';
1273 END IF;
1274
1275 l_index := p_app_attributes.NEXT (l_index);
1276 END LOOP;
1277 END convert_app_attributes_to_type;
1278
1279 -----------------------------------------------------------------------------
1280 -- Type: Function
1281 -- Scope: Public
1282 -- Name: convert_to_dpwr_messages
1283 -- Returns: hxc_self_service_time_deposit.message_table
1284 -- IN Parameters: p_messages -> The PL/SQL table structure you want to convert
1285 --
1286 -- Description: Private Function that will convert the new TYPE to the old
1287 -- PL/SQL message structure that is then returned.
1288 --
1289 -----------------------------------------------------------------------------
1290 FUNCTION convert_to_dpwr_messages (p_messages IN hxc_message_table_type)
1291 RETURN hxc_self_service_time_deposit.message_table
1292 IS
1293 l_proc VARCHAR2 (72);
1294 l_messages hxc_self_service_time_deposit.message_table;
1295 l_index NUMBER;
1296 BEGIN
1297 g_debug := hr_utility.debug_enabled;
1298
1299 IF g_debug
1300 THEN
1301 l_proc := g_package || 'convert_to_dpwr_messages';
1302 hr_utility.set_location ('Entering:' || l_proc, 10);
1303 END IF;
1304
1305 l_index := p_messages.FIRST;
1306
1307 LOOP
1308 EXIT WHEN NOT p_messages.EXISTS (l_index);
1309 l_messages (l_index).message_name :=
1310 p_messages (l_index).message_name;
1311 l_messages (l_index).message_level :=
1312 p_messages (l_index).message_level;
1313 l_messages (l_index).message_field :=
1314 p_messages (l_index).message_field;
1315 l_messages (l_index).message_tokens :=
1316 p_messages (l_index).message_tokens;
1317 l_messages (l_index).application_short_name :=
1318 p_messages (l_index).application_short_name;
1319 l_messages (l_index).time_building_block_id :=
1320 p_messages (l_index).time_building_block_id;
1321 l_messages (l_index).time_building_block_ovn :=
1322 p_messages (l_index).time_building_block_ovn;
1323 l_messages (l_index).time_attribute_id :=
1324 p_messages (l_index).time_attribute_id;
1325 l_messages (l_index).time_attribute_ovn :=
1326 p_messages (l_index).time_attribute_ovn;
1327 l_index := p_messages.NEXT (l_index);
1328 END LOOP;
1329
1330 IF g_debug
1331 THEN
1332 hr_utility.set_location ( ' returning l_messages.count = '
1333 || l_messages.COUNT,
1334 20
1335 );
1336 hr_utility.set_location ('Leaving:' || l_proc, 30);
1337 END IF;
1338
1339 RETURN l_messages;
1340 END convert_to_dpwr_messages;
1341
1342 -----------------------------------------------------------------------------
1343 -- Type: Function
1344 -- Scope: Public
1345 -- Name: convert_msg_to_type
1346 -- Returns: hxc_message_table_type
1347 -- IN Parameters: p_messages -> The PL/SQL msg structure you want to convert
1348 --
1349 -- Description: Private Function that will convert the old PL/SQL message
1350 -- structure to the new TYPE that is then returned.
1351 --
1352 -----------------------------------------------------------------------------
1353 FUNCTION convert_msg_to_type (
1354 p_messages IN hxc_self_service_time_deposit.message_table
1355 )
1356 RETURN hxc_message_table_type
1357 IS
1358 l_proc VARCHAR2 (72);
1359 l_messages hxc_message_table_type;
1360 l_index PLS_INTEGER;
1361 BEGIN
1362 IF g_debug
1363 THEN
1364 l_proc := g_package || 'convert_msg_to_type';
1365 hr_utility.set_location ('Entering:' || l_proc, 10);
1366 END IF;
1367
1368 -- Initialize the collection
1369 l_messages := hxc_message_table_type ();
1370 l_index := p_messages.FIRST;
1371
1372 LOOP
1373 EXIT WHEN NOT p_messages.EXISTS (l_index);
1374 l_messages.EXTEND;
1375 l_messages (l_messages.LAST) :=
1376 hxc_message_type (p_messages (l_index).message_name,
1377 p_messages (l_index).message_level,
1378 p_messages (l_index).message_field,
1379 p_messages (l_index).message_tokens,
1380 p_messages (l_index).application_short_name,
1381 p_messages (l_index).time_building_block_id,
1382 p_messages (l_index).time_building_block_ovn,
1383 p_messages (l_index).time_attribute_id,
1384 p_messages (l_index).time_attribute_ovn,
1385 p_messages (l_index).message_extent
1386 );
1387 l_index := p_messages.NEXT (l_index);
1388 END LOOP;
1389
1390 IF g_debug
1391 THEN
1392 hr_utility.set_location ( ' returning l_messages.count = '
1393 || l_messages.COUNT,
1394 20
1395 );
1396 hr_utility.set_location ('Leaving: ' || l_proc, 30);
1397 END IF;
1398
1399 RETURN l_messages;
1400 END convert_msg_to_type;
1401
1402 -----------------------------------------------------------------------------
1403 -- Type: Function
1404 -- Scope: Public
1405 -- Name: get_approval_status
1406 -- Returns: VARCHAR2
1407 -- IN Parameters: p_mode -> The mode you want to find the approval status for
1408 --
1409 -- Description: Private Function that will return the approval status that
1410 -- matches a mode
1411 -- The approval status is linked (hard coded here) to the mode
1412 -- For P_MODE 'SAVE' APPROVAL_STATUS = 'WORKING'
1413 -- For P_MODE 'SUBMIT' APPROVAL_STATUS = 'SUBMITTED'
1414 -- For P_MODE 'FORCE_SAVE' APPROVAL_STATUS = 'WORKING'
1415 -- For P_MODE 'FORCE_SUBMIT' APPROVAL_STATUS = 'SUBMITTED'
1416 -- For P_MODE 'AUDIT' APPROVAL_STATUS = 'SUBMITTED'
1417 -- For P_MODE 'MIGRATION' APPROVAL_STATUS = 'SUBMITTED'
1418 -----------------------------------------------------------------------------
1419 FUNCTION get_approval_status (p_mode IN VARCHAR2)
1420 RETURN VARCHAR2
1421 IS
1422 l_proc VARCHAR2 (72);
1423 l_appr_stat VARCHAR2 (30);
1424 BEGIN
1425 g_debug := hr_utility.debug_enabled;
1426
1427 IF g_debug
1428 THEN
1429 l_proc := g_package || 'get_approval_status';
1430 hr_utility.set_location ('Entering:' || l_proc, 10);
1431 END IF;
1432
1433 IF p_mode = hxc_timecard.c_save
1434 THEN
1435 l_appr_stat := hxc_timecard.c_working_status;
1436 ELSIF p_mode = hxc_timecard.c_submit
1437 THEN
1438 l_appr_stat := hxc_timecard.c_submitted_status;
1439 ELSIF p_mode = hxc_timestore_deposit.c_tk_save
1440 THEN
1441 l_appr_stat := hxc_timecard.c_working_status;
1442 ELSIF p_mode = hxc_timestore_deposit.c_tk_submit
1443 THEN
1444 l_appr_stat := hxc_timecard.c_submitted_status;
1445 ELSIF p_mode = hxc_timecard.c_audit
1446 THEN
1447 l_appr_stat := hxc_timecard.c_submitted_status;
1448 ELSIF p_mode = hxc_timestore_deposit.c_migration
1449 THEN
1450 l_appr_stat := hxc_timecard.c_submitted_status;
1451 ELSE -- if no mode was supplied (or the wrong one), try working
1452 l_appr_stat := hxc_timecard.c_working_status;
1453 END IF;
1454
1455 IF g_debug
1456 THEN
1457 hr_utility.set_location ( ' returning approval_status = '
1458 || l_appr_stat,
1459 20
1460 );
1461 hr_utility.set_location ('Leaving:' || l_proc, 30);
1462 END IF;
1463
1464 RETURN l_appr_stat;
1465 END get_approval_status;
1466
1467 -----------------------------------------------------------------------------
1468 -- Type: Procedure
1469 -- Scope: Public
1470 -- Name: find_parent_building_block
1471 -- IN Parameters: p_start_time -> Start Time of the TBB
1472 -- p_resource_id -> Resource ID (Person ID)
1473 -- p_resource_type -> Defaults to 'PERSON', use the default
1474 -- p_scope -> 'DAY' or 'DETAIL' (TIMECARD TBBs do not
1475 -- have parents)
1476 -- p_app_blocks -> The table type already holding a timecard
1477 -- This might be empty
1478 -- OUT Parameters: p_timecard_bb_id -> id of the parent TBB id
1479 -- p_timecard_ovn -> ovn of the parent TBB id
1480 --
1481 -- Description: By passing in the details of a TBB (p_start_time,
1482 -- p_resource_id), this procedure will
1483 -- work out what the parent for that TBB should be. We can do
1484 -- this, because for a certain time and resource id, there
1485 -- can only be one parent TBB to which this TBB can be added.
1486 --
1487 -- If a DAY TBB is passed in, this procedure will work out
1488 -- to which TIMECARD TBB this DAY TBB needs to be attached,
1489 -- if a DETAIL TBB is passed in, this procedure will work out
1490 -- to which DAY TBB this DETAIL TBB needs to be attached.
1491 --
1492 -- The procedure will first scan the PL/SQL timecard table to
1493 -- see if it can find the TBB in there. If this table is empty
1494 -- it will look in the DB.
1495 --
1496 -- Exceptions: No Timecard found
1497 -- Wrong Timecard in PL/SQL Table
1498 -----------------------------------------------------------------------------
1499 PROCEDURE find_parent_building_block (
1500 p_start_time IN hxc_time_building_blocks.start_time%TYPE,
1501 p_resource_id IN hxc_time_building_blocks.resource_id%TYPE,
1502 p_resource_type IN hxc_time_building_blocks.resource_type%TYPE,
1503 p_scope IN hxc_time_building_blocks.SCOPE%TYPE,
1504 p_app_blocks IN hxc_block_table_type,
1505 p_timecard_bb_id OUT NOCOPY hxc_time_building_blocks.time_building_block_id%TYPE,
1506 p_timecard_ovn OUT NOCOPY hxc_time_building_blocks.object_version_number%TYPE
1507 )
1508 IS
1509 l_proc VARCHAR2 (72);
1510
1511 -- This cursor will return the BB_ID and OVN of the current TBB.
1512 CURSOR csr_get_timecard_id (
1513 v_start_time hxc_time_building_blocks.start_time%TYPE,
1514 v_resource_id hxc_time_building_blocks.resource_id%TYPE,
1515 v_resource_type hxc_time_building_blocks.resource_type%TYPE,
1516 v_scope hxc_time_building_blocks.SCOPE%TYPE
1517 )
1518 IS
1519 SELECT tbb.time_building_block_id,
1520 MAX (tbb.object_version_number)
1521 FROM hxc_time_building_blocks tbb, hxc_time_building_blocks parent_tbb
1522 WHERE tbb.SCOPE = v_scope
1523 AND tbb.resource_type = v_resource_type
1524 AND tbb.resource_id = v_resource_id
1525 AND v_start_time BETWEEN tbb.start_time AND tbb.stop_time
1526 AND tbb.date_to = hr_general.end_of_time
1527 AND parent_tbb.time_building_block_id = tbb.parent_building_block_id
1528 AND parent_tbb.object_version_number = tbb.parent_building_block_ovn
1529 AND parent_tbb.date_to = hr_general.end_of_time
1530 AND parent_tbb.SCOPE <> hxc_timecard.c_template_scope
1531 GROUP BY tbb.time_building_block_id;
1532
1533 l_building_block_count PLS_INTEGER;
1534 l_index PLS_INTEGER;
1535 BEGIN
1536 g_debug := hr_utility.debug_enabled;
1537
1538 IF g_debug
1539 THEN
1540 l_proc := g_package || 'find_parent_building_block';
1541 hr_utility.set_location ('Entering:' || l_proc, 10);
1542 END IF;
1543
1544 -- If the PL/SQL structure is not empty, we assume the user wants to add
1545 -- this DAY TBB to the TIMECARD present in the PL/SQL table so first look
1546 -- there
1547 IF (p_app_blocks.COUNT <> 0)
1548 THEN -- Look for TIMECARD in PL/SQL structure
1549 p_timecard_bb_id := NULL; -- also used to exit loop
1550 l_building_block_count := p_app_blocks.FIRST;
1551
1552 -- loop over all TBB in the PL/SQL table and see if we find a match
1553 LOOP
1554 EXIT WHEN (NOT p_app_blocks.EXISTS (l_building_block_count))
1555 OR (p_timecard_bb_id IS NOT NULL);
1556
1557 IF (p_start_time
1558 BETWEEN fnd_date.canonical_to_date
1559 (p_app_blocks (l_building_block_count).start_time
1560 )
1561 AND fnd_date.canonical_to_date
1562 (p_app_blocks (l_building_block_count).stop_time
1563 )
1564 )
1565 AND (p_resource_type =
1566 p_app_blocks (l_building_block_count).resource_type
1567 )
1568 AND (p_resource_id =
1569 p_app_blocks (l_building_block_count).resource_id
1570 )
1571 AND (p_app_blocks (l_building_block_count).SCOPE = p_scope)
1572 THEN
1573 IF g_debug
1574 THEN
1575 hr_utility.set_location
1576 ( ' Found TBB in PL/SQL Table, ID = '
1577 || p_app_blocks (l_building_block_count).time_building_block_id,
1578 20
1579 );
1580 END IF;
1581
1582 -- found BB, set the ID and OVN
1583 p_timecard_bb_id :=
1584 p_app_blocks (l_building_block_count).time_building_block_id;
1585 p_timecard_ovn :=
1586 p_app_blocks (l_building_block_count).object_version_number;
1587 END IF;
1588
1589 -- Next
1590 l_building_block_count :=
1591 p_app_blocks.NEXT (l_building_block_count);
1592 END LOOP;
1593
1594 IF l_index IS NULL
1595 THEN -- we never found the BB : ERROR
1596 NULL;
1597 END IF;
1598 ELSE -- Look for TIMECARD in DB
1599 OPEN csr_get_timecard_id (p_start_time,
1600 p_resource_id,
1601 p_resource_type,
1602 p_scope
1603 );
1604
1605 FETCH csr_get_timecard_id
1606 INTO p_timecard_bb_id, p_timecard_ovn;
1607
1608 IF g_debug
1609 THEN
1610 hr_utility.set_location ( ' Found TBB in DB, ID = '
1611 || p_timecard_bb_id,
1612 30
1613 );
1614 END IF;
1615
1616 CLOSE csr_get_timecard_id;
1617 END IF; -- (p_app_blocks.COUNT <> 0)
1618
1619 IF g_debug
1620 THEN
1621 hr_utility.set_location ('Leaving:' || l_proc, 40);
1622 END IF;
1623 END find_parent_building_block;
1624
1625 -----------------------------------------------------------------------------
1626 -- Type: Procedure
1627 -- Scope: Public
1628 -- Name: set_new_change_flags
1629 -- IN OUT Parameters: p_attributes -> The PL/SQL attribute table type you want
1630 -- to update the changed and new flags for.
1631 --
1632 -- Description: Public Procedure that will set the changed and new flags for
1633 -- the attribute structure passed in. Unfortunately, when we
1634 -- retrieve the attributes from the database using the attribute
1635 -- utilities, these flags are not set correctly so we need to
1636 -- addjust them, we can do that with this function.
1637 -----------------------------------------------------------------------------
1638 PROCEDURE set_new_change_flags (
1639 p_attributes IN OUT NOCOPY hxc_attribute_table_type
1640 )
1641 IS
1642 l_proc VARCHAR2 (72);
1643 l_index PLS_INTEGER;
1644
1645 CURSOR find_attribute (
1646 p_attr_id hxc_time_attributes.time_attribute_id%TYPE
1647 )
1648 IS
1649 SELECT time_attribute_id, attribute_category, attribute1,
1650 attribute2, attribute3, attribute4, attribute5, attribute6,
1651 attribute7, attribute8, attribute9, attribute10,
1652 attribute11, attribute12, attribute13, attribute14,
1653 attribute15, attribute16, attribute17, attribute18,
1654 attribute19, attribute20, attribute21, attribute22,
1655 attribute23, attribute24, attribute25, attribute26,
1656 attribute27, attribute28, attribute29, attribute30,
1657 MAX (object_version_number)
1658 FROM hxc_time_attributes
1659 WHERE time_attribute_id = p_attr_id
1660 GROUP BY time_attribute_id,
1661 attribute_category,
1662 attribute1,
1663 attribute2,
1664 attribute3,
1665 attribute4,
1666 attribute5,
1667 attribute6,
1668 attribute7,
1669 attribute8,
1670 attribute9,
1671 attribute10,
1672 attribute11,
1673 attribute12,
1674 attribute13,
1675 attribute14,
1676 attribute15,
1677 attribute16,
1678 attribute17,
1679 attribute18,
1680 attribute19,
1681 attribute20,
1682 attribute21,
1683 attribute22,
1684 attribute23,
1685 attribute24,
1686 attribute25,
1687 attribute26,
1688 attribute27,
1689 attribute28,
1690 attribute29,
1691 attribute30;
1692
1693 l_attribute_rec find_attribute%ROWTYPE;
1694 c_null CONSTANT VARCHAR2 (5) := '@@@@@';
1695 BEGIN
1696 g_debug := hr_utility.debug_enabled;
1697
1698 IF g_debug
1699 THEN
1700 l_proc := g_package || 'set_new_change_flags';
1701 hr_utility.set_location ('Entering:' || l_proc, 10);
1702 END IF;
1703
1704 l_index := p_attributes.FIRST;
1705
1706 LOOP
1707 EXIT WHEN NOT p_attributes.EXISTS (l_index);
1708
1709 OPEN find_attribute (p_attributes (l_index).time_attribute_id);
1710
1711 FETCH find_attribute
1712 INTO l_attribute_rec;
1713
1714 IF find_attribute%FOUND
1715 THEN
1716 IF g_debug
1717 THEN
1718 hr_utility.set_location
1719 ( ' - Attribute '
1720 || p_attributes (l_index).time_attribute_id
1721 || ' Found',
1722 20
1723 );
1724 END IF;
1725
1726 p_attributes (l_index).NEW := 'N';
1727
1728 IF ( NVL (l_attribute_rec.attribute1, c_null) =
1729 NVL (p_attributes (l_index).attribute1, c_null)
1730 AND NVL (l_attribute_rec.attribute2, c_null) =
1731 NVL (p_attributes (l_index).attribute2, c_null)
1732 AND NVL (l_attribute_rec.attribute3, c_null) =
1733 NVL (p_attributes (l_index).attribute3, c_null)
1734 AND NVL (l_attribute_rec.attribute4, c_null) =
1735 NVL (p_attributes (l_index).attribute4, c_null)
1736 AND NVL (l_attribute_rec.attribute5, c_null) =
1737 NVL (p_attributes (l_index).attribute5, c_null)
1738 AND NVL (l_attribute_rec.attribute6, c_null) =
1739 NVL (p_attributes (l_index).attribute6, c_null)
1740 AND NVL (l_attribute_rec.attribute7, c_null) =
1741 NVL (p_attributes (l_index).attribute7, c_null)
1742 AND NVL (l_attribute_rec.attribute8, c_null) =
1743 NVL (p_attributes (l_index).attribute8, c_null)
1744 AND NVL (l_attribute_rec.attribute9, c_null) =
1745 NVL (p_attributes (l_index).attribute9, c_null)
1746 AND NVL (l_attribute_rec.attribute10, c_null) =
1747 NVL (p_attributes (l_index).attribute10, c_null)
1748 AND NVL (l_attribute_rec.attribute11, c_null) =
1749 NVL (p_attributes (l_index).attribute11, c_null)
1750 AND NVL (l_attribute_rec.attribute12, c_null) =
1751 NVL (p_attributes (l_index).attribute12, c_null)
1752 AND NVL (l_attribute_rec.attribute13, c_null) =
1753 NVL (p_attributes (l_index).attribute13, c_null)
1754 AND NVL (l_attribute_rec.attribute14, c_null) =
1755 NVL (p_attributes (l_index).attribute14, c_null)
1756 AND NVL (l_attribute_rec.attribute15, c_null) =
1757 NVL (p_attributes (l_index).attribute15, c_null)
1758 AND NVL (l_attribute_rec.attribute16, c_null) =
1759 NVL (p_attributes (l_index).attribute16, c_null)
1760 AND NVL (l_attribute_rec.attribute17, c_null) =
1761 NVL (p_attributes (l_index).attribute17, c_null)
1762 AND NVL (l_attribute_rec.attribute18, c_null) =
1763 NVL (p_attributes (l_index).attribute18, c_null)
1764 AND NVL (l_attribute_rec.attribute19, c_null) =
1765 NVL (p_attributes (l_index).attribute19, c_null)
1766 AND NVL (l_attribute_rec.attribute20, c_null) =
1767 NVL (p_attributes (l_index).attribute20, c_null)
1768 AND NVL (l_attribute_rec.attribute21, c_null) =
1769 NVL (p_attributes (l_index).attribute21, c_null)
1770 AND NVL (l_attribute_rec.attribute22, c_null) =
1771 NVL (p_attributes (l_index).attribute22, c_null)
1772 AND NVL (l_attribute_rec.attribute23, c_null) =
1773 NVL (p_attributes (l_index).attribute23, c_null)
1774 AND NVL (l_attribute_rec.attribute24, c_null) =
1775 NVL (p_attributes (l_index).attribute24, c_null)
1776 AND NVL (l_attribute_rec.attribute25, c_null) =
1777 NVL (p_attributes (l_index).attribute25, c_null)
1778 AND NVL (l_attribute_rec.attribute26, c_null) =
1779 NVL (p_attributes (l_index).attribute26, c_null)
1780 AND NVL (l_attribute_rec.attribute27, c_null) =
1781 NVL (p_attributes (l_index).attribute27, c_null)
1782 AND NVL (l_attribute_rec.attribute28, c_null) =
1783 NVL (p_attributes (l_index).attribute28, c_null)
1784 AND NVL (l_attribute_rec.attribute29, c_null) =
1785 NVL (p_attributes (l_index).attribute29, c_null)
1786 AND NVL (l_attribute_rec.attribute30, c_null) =
1787 NVL (p_attributes (l_index).attribute30, c_null)
1788 )
1789 THEN
1790 IF g_debug
1791 THEN
1792 hr_utility.set_location
1793 ( ' - Attribute '
1794 || p_attributes (l_index).time_attribute_id
1795 || ' Not Changed',
1796 40
1797 );
1798 END IF;
1799
1800 p_attributes (l_index).changed := 'N';
1801 p_attributes (l_index).process := 'N';
1802 ELSE
1803 IF g_debug
1804 THEN
1805 hr_utility.set_location
1806 ( ' - Attribute '
1807 || p_attributes (l_index).time_attribute_id
1808 || ' Changed',
1809 50
1810 );
1811 END IF;
1812
1813 p_attributes (l_index).changed := 'Y';
1814 p_attributes (l_index).process := 'Y';
1815 END IF;
1816 ELSE
1817 IF g_debug
1818 THEN
1819 hr_utility.set_location
1820 ( ' - Attribute '
1821 || p_attributes (l_index).time_attribute_id
1822 || ' Not Found',
1823 60
1824 );
1825 END IF;
1826
1827 p_attributes (l_index).NEW := 'Y';
1828 p_attributes (l_index).changed := 'N';
1829 p_attributes (l_index).process := 'Y';
1830 END IF;
1831
1832 CLOSE find_attribute;
1833
1834 l_index := p_attributes.NEXT (l_index);
1835 END LOOP;
1836
1837 IF g_debug
1838 THEN
1839 hr_utility.set_location ('Leaving: ' || l_proc, 70);
1840 END IF;
1841 END set_new_change_flags;
1842
1843 -----------------------------------------------------------------------------
1844 -- Type: Procedure
1845 -- Scope: Public
1846 -- Name: find_parent_building_block
1847 -- IN Parameters: p_start_time -> Start Time of the TBB
1848 -- p_resource_id -> Resource ID (Person ID)
1849 -- p_resource_type -> Defaults to 'PERSON', use the default
1850 -- p_scope -> 'DAY' or 'DETAIL' (TIMECARD TBBs do not
1851 -- have parents)
1852 -- p_app_blocks -> The PL/SQL table already holding a timecard
1853 -- This might be empty
1854 -- OUT Parameters: p_timecard_bb_id -> id of the parent TBB id
1855 -- p_timecard_ovn -> ovn of the parent TBB id
1856 --
1857 -- Description: By passing in the details of a TBB (p_start_time,
1858 -- p_resource_id), this procedure will
1859 -- work out what the parent for that TBB should be. We can do
1860 -- this, because for a certain time and resource id, there
1861 -- can only be one parent TBB to which this TBB can be added.
1862 --
1863 -- If a DAY TBB is passed in, this procedure will work out
1864 -- to which TIMECARD TBB this DAY TBB needs to be attached,
1865 -- if a DETAIL TBB is passed in, this procedure will work out
1866 -- to which DAY TBB this DETAIL TBB needs to be attached.
1867 --
1868 -- The procedure will first scan the PL/SQL timecard table to
1869 -- see if it can find the TBB in there. If this table is empty
1870 -- it will look in the DB.
1871 --
1872 -- Exceptions: No Timecard found
1873 -- Wrong Timecard in PL/SQL Table
1874 -----------------------------------------------------------------------------
1875 PROCEDURE find_parent_building_block (
1876 p_start_time IN hxc_time_building_blocks.start_time%TYPE,
1877 p_resource_id IN hxc_time_building_blocks.resource_id%TYPE,
1878 p_resource_type IN hxc_time_building_blocks.resource_type%TYPE,
1879 p_scope IN hxc_time_building_blocks.SCOPE%TYPE,
1880 p_app_blocks IN hxc_self_service_time_deposit.timecard_info,
1881 p_timecard_bb_id OUT NOCOPY hxc_time_building_blocks.time_building_block_id%TYPE,
1882 p_timecard_ovn OUT NOCOPY hxc_time_building_blocks.object_version_number%TYPE
1883 )
1884 IS
1885 l_proc VARCHAR2 (72);
1886 l_blocks hxc_block_table_type;
1887 BEGIN
1888 g_debug := hr_utility.debug_enabled;
1889
1890 IF g_debug
1891 THEN
1892 l_proc := g_package || 'find_parent_building_block (Overload)';
1893 hr_utility.set_location ('Entering:' || l_proc, 10);
1894 END IF;
1895
1896 l_blocks := convert_tbb_to_type (p_blocks => p_app_blocks);
1897 find_parent_building_block (p_start_time => p_start_time,
1898 p_resource_id => p_resource_id,
1899 p_resource_type => p_resource_type,
1900 p_scope => p_scope,
1901 p_app_blocks => l_blocks,
1902 p_timecard_bb_id => p_timecard_bb_id,
1903 p_timecard_ovn => p_timecard_ovn
1904 );
1905
1906 /* hxc_timecard.convert_to_type
1907 (p_attributes => in HXC_SELF_SERVICE_TIME_DEPOSIT.building_block_attribute_info)
1908 return HXC_ATTRIBUTE_TABLE_TYPE is
1909
1910 l_attributes HXC_ATTRIBUTE_TABLE_TYPE;
1911 l_index NUMBER;
1912
1913 */
1914 IF g_debug
1915 THEN
1916 hr_utility.set_location ('Leaving:' || l_proc, 40);
1917 END IF;
1918 END find_parent_building_block;
1919
1920 -----------------------------------------------------------------------------
1921 -- Type: Procedure
1922 -- Scope: Public
1923 -- Name: get_timecard_bb_id
1924 -- IN Parameters: p_bb_id -> Start Time of the TBB
1925 -- OUT Parameters: p_timecard_bb_id -> id of the parent TBB id
1926 -- p_timecard_ovn -> ovn of the parent TBB id
1927 --
1928 -- Description: By passing in the id of a TBB, this procedure will work out
1929 -- what the TIMECARD TBB is, so it finds the highest TBB in the
1930 -- hierarchy of a timecard. You can pass in the id of a DAY or
1931 -- DETAIL (even TIMECARD although that wouldn't make a lot of
1932 -- sence) TBB and it will return the ID and OVN of the TIMECARD
1933 -- TBB this TBB belongs to. The procedure only looks in the DB
1934 -- so it is only usefull for timecards stored in the TimeStore.
1935 --
1936 -- Exceptions: No Timecard found
1937 -----------------------------------------------------------------------------
1938 PROCEDURE get_timecard_bb_id (
1939 p_bb_id IN hxc_time_building_blocks.time_building_block_id%TYPE,
1940 p_timecard_bb_id OUT NOCOPY hxc_time_building_blocks.time_building_block_id%TYPE,
1941 p_timecard_ovn OUT NOCOPY hxc_time_building_blocks.object_version_number%TYPE
1942 )
1943 IS
1944 l_proc VARCHAR2 (72);
1945
1946 -- This cursor will return the BB_ID and OVN of the current TIMECARD BB of a BB.
1947 CURSOR csr_get_timecard_bb_id (
1948 v_tc_id hxc_time_building_blocks.time_building_block_id%TYPE
1949 )
1950 IS
1951 SELECT time_building_block_id, MAX (object_version_number)
1952 FROM hxc_time_building_blocks
1953 WHERE SCOPE = hxc_timecard.c_timecard_scope
1954 AND date_to = hr_general.end_of_time
1955 CONNECT BY PRIOR parent_building_block_id = time_building_block_id
1956 AND PRIOR parent_building_block_ovn = object_version_number
1957 START WITH time_building_block_id = v_tc_id
1958 GROUP BY time_building_block_id
1959 ORDER BY time_building_block_id;
1960 BEGIN
1961 g_debug := hr_utility.debug_enabled;
1962
1963 IF g_debug
1964 THEN
1965 l_proc := g_package || 'get_timecard_bb_id';
1966 hr_utility.set_location ('Entering:' || l_proc, 10);
1967 END IF;
1968
1969 OPEN csr_get_timecard_bb_id (p_bb_id);
1970
1971 FETCH csr_get_timecard_bb_id
1972 INTO p_timecard_bb_id, p_timecard_ovn;
1973
1974 CLOSE csr_get_timecard_bb_id;
1975
1976 IF g_debug
1977 THEN
1978 hr_utility.set_location ('Leaving:' || l_proc, 20);
1979 END IF;
1980 END get_timecard_bb_id;
1981
1982 -----------------------------------------------------------------------------
1983 -- Type: Function
1984 -- Scope: Public
1985 -- Name: get_index_in_bb_table
1986 -- Returns: PLS_INTEGER
1987 -- IN Parameters: p_bb_table -> TBB Table Type that needs to be scanned
1988 -- p_bb_id_to_find -> id of the TBB that is being looked for
1989 --
1990 -- Description: This function will return the index of the row in the PL/SQL
1991 -- table (passed in) that holds the id (also passed in) of the
1992 -- TBB that is being looked for. In case the TBB is not found
1993 -- in the PL/SQL Table, a negative index is being returned.
1994 --
1995 -----------------------------------------------------------------------------
1996 FUNCTION get_index_in_bb_table (
1997 p_bb_table IN hxc_block_table_type,
1998 p_bb_id_to_find IN hxc_time_building_blocks.time_building_block_id%TYPE
1999 )
2000 RETURN PLS_INTEGER
2001 IS
2002 l_proc VARCHAR2 (72);
2003 l_building_block_count PLS_INTEGER;
2004 l_index PLS_INTEGER;
2005 l_current_highest_ovn PLS_INTEGER;
2006 BEGIN
2007 g_debug := hr_utility.debug_enabled;
2008
2009 IF g_debug
2010 THEN
2011 l_proc := g_package || 'get_index_in_bb_table';
2012 hr_utility.set_location ('Entering:' || l_proc, 10);
2013 END IF;
2014
2015 l_building_block_count := p_bb_table.FIRST;
2016 -- Initialize to zero as lowest possible ovn is 1
2017 l_current_highest_ovn := 0;
2018
2019 LOOP
2020 EXIT WHEN (NOT p_bb_table.EXISTS (l_building_block_count));
2021
2022 IF p_bb_table (l_building_block_count).time_building_block_id =
2023 p_bb_id_to_find
2024 THEN -- found BB
2025 IF (l_current_highest_ovn <
2026 p_bb_table (l_building_block_count).object_version_number
2027 )
2028 THEN
2029 -- only set the index if this is a newer TBB
2030 l_index := l_building_block_count;
2031 -- set this now as the highest ovn
2032 l_current_highest_ovn :=
2033 p_bb_table (l_building_block_count).object_version_number;
2034 END IF;
2035 END IF;
2036
2037 l_building_block_count := p_bb_table.NEXT (l_building_block_count);
2038 END LOOP;
2039
2040 IF l_index IS NULL
2041 THEN -- we never found the BB, return a negative number
2042 l_index := -1;
2043 END IF;
2044
2045 IF g_debug
2046 THEN
2047 hr_utility.set_location (' returning index = ' || l_index, 20);
2048 hr_utility.set_location ('Leaving:' || l_proc, 30);
2049 END IF;
2050
2051 RETURN l_index;
2052 END get_index_in_bb_table;
2053
2054 -----------------------------------------------------------------------------
2055 -- Type: Function
2056 -- Scope: Public
2057 -- Name: get_deposit_process_id
2058 -- Returns: NUMBER
2059 -- IN Parameters: p_deposit_process_name -> Name of the deposit process for
2060 -- which you want to get the ID
2061 --
2062 -- Description: This function will return the Id of the deposit process passed
2063 -- in.
2064 --
2065 -----------------------------------------------------------------------------
2066 FUNCTION get_deposit_process_id (
2067 p_deposit_process_name IN hxc_deposit_processes.NAME%TYPE
2068 )
2069 RETURN hxc_deposit_processes.deposit_process_id%TYPE
2070 IS
2071 l_proc VARCHAR2 (72);
2072
2073 CURSOR csr_deposit_process_id (v_deposit_process_name IN VARCHAR2)
2074 IS
2075 SELECT deposit_process_id
2076 FROM hxc_deposit_processes
2077 WHERE NAME = v_deposit_process_name;
2078
2079 l_deposit_process_id hxc_deposit_processes.deposit_process_id%TYPE;
2080 BEGIN
2081 g_debug := hr_utility.debug_enabled;
2082
2083 IF g_debug
2084 THEN
2085 l_proc := g_package || 'get_deposit_process_id';
2086 hr_utility.set_location ('Entering:' || l_proc, 10);
2087 END IF;
2088
2089 OPEN csr_deposit_process_id (p_deposit_process_name);
2090
2091 FETCH csr_deposit_process_id
2092 INTO l_deposit_process_id;
2093
2094 IF csr_deposit_process_id%NOTFOUND
2095 THEN
2096 CLOSE csr_deposit_process_id;
2097
2098 fnd_message.set_name ('HXC', 'HXC_NO_RETRIEVAL_PROCESS');
2099 fnd_message.set_token ('PROCESS_NAME', p_deposit_process_name);
2100 fnd_message.raise_error;
2101 END IF;
2102
2103 CLOSE csr_deposit_process_id;
2104
2105 IF g_debug
2106 THEN
2107 hr_utility.set_location ( ' returning deposit process id = '
2108 || l_deposit_process_id,
2109 20
2110 );
2111 hr_utility.set_location ('Leaving:' || l_proc, 30);
2112 END IF;
2113
2114 RETURN l_deposit_process_id;
2115 END get_deposit_process_id;
2116
2117 -----------------------------------------------------------------------------
2118 -- Type: Function
2119 -- Scope: Public
2120 -- Name: get_index_in_attr_table
2121 -- Returns: PLS_INTEGER
2122 -- IN Parameters: p_attr_table -> Attribute PL/SQL table that needs to be scanned
2123 -- p_attr_id_to_find -> id of the attr that is being looked for
2124 --
2125 -- Description: This function will return the index of the row in the PL/SQL
2126 -- table (passed in) that holds the id (also passed in) of the
2127 -- attr that is being looked for. In case the TBattrB is not
2128 -- found in the PL/SQL Table, a negative index is being returned.
2129 --
2130 -----------------------------------------------------------------------------
2131 FUNCTION get_index_in_attr_table (
2132 p_attr_table IN hxc_self_service_time_deposit.app_attributes_info,
2133 p_attr_id_to_find IN hxc_time_attributes.time_attribute_id%TYPE,
2134 p_attribute_name_to_find IN hxc_mapping_components.field_name%TYPE
2135 )
2136 RETURN PLS_INTEGER
2137 IS
2138 l_proc VARCHAR2 (72);
2139 l_attribute_count PLS_INTEGER;
2140 l_index PLS_INTEGER;
2141 BEGIN
2142 g_debug := hr_utility.debug_enabled;
2143
2144 IF g_debug
2145 THEN
2146 l_proc := g_package || 'get_index_in_attr_table';
2147 hr_utility.set_location ('Entering:' || l_proc, 10);
2148 END IF;
2149
2150 l_attribute_count := p_attr_table.FIRST;
2151
2152 LOOP
2153 EXIT WHEN (NOT p_attr_table.EXISTS (l_attribute_count))
2154 OR (l_index IS NOT NULL);
2155
2156 IF (p_attr_table (l_attribute_count).time_attribute_id =
2157 p_attr_id_to_find
2158 )
2159 AND (p_attr_table (l_attribute_count).attribute_name =
2160 p_attribute_name_to_find
2161 )
2162 THEN
2163 -- found BB, set the index
2164 l_index := l_attribute_count;
2165 END IF;
2166
2167 l_attribute_count := p_attr_table.NEXT (l_attribute_count);
2168 END LOOP;
2169
2170 IF l_index IS NULL
2171 THEN -- we never found the BB return a negative number
2172 l_index := -1;
2173 END IF;
2174
2175 IF g_debug
2176 THEN
2177 hr_utility.set_location (' returning index = ' || l_index, 20);
2178 hr_utility.set_location ('Leaving:' || l_proc, 30);
2179 END IF;
2180
2181 RETURN l_index;
2182 END get_index_in_attr_table;
2183
2184 -- Use this to find the parent BB if a range is given, this can not work for a measure
2185 -- It needs the start and stop time to find the parent
2186 -- this saves the user from having to find the ID of the parent block themselves
2187 -- This only works because there is no overlap in BB start and stop times
2188 -- Should this ever change than this procedure will cease to work as it might return multiple BB rows.
2189 /* PROCEDURE find_parent_building_block (
2190 p_building_block_id OUT NOCOPY hxc_time_building_blocks.time_building_block_id%TYPE,
2191 p_object_version_number OUT NOCOPY hxc_time_building_blocks.object_version_number%TYPE,
2192 p_scope IN hxc_time_building_blocks.SCOPE%TYPE,
2193 p_start_time IN hxc_time_building_blocks.start_time%TYPE,
2194 p_stop_time IN hxc_time_building_blocks.stop_time%TYPE,
2195 p_resource_id IN hxc_time_building_blocks.resource_id%TYPE,
2196 p_resource_type IN hxc_time_building_blocks.resource_type%TYPE
2197 DEFAULT 'PERSON'
2198 )
2199 IS
2200 l_proc VARCHAR2 (72)
2201 := g_package
2202 || 'find_parent_building_block';
2203
2204 -- This cursor will return the BB_ID and OVN of the parent TIMECARD BB.
2205 -- It should always return only one row!
2206 -- if it does not, there is overlap in the start and stop times which should not happen!
2207 CURSOR csr_get_parent (
2208
2209 -- v_tc_id hxc_time_building_blocks.time_building_block_id%TYPE,
2210 v_scope hxc_time_building_blocks.SCOPE%TYPE,
2211 v_start_time hxc_time_building_blocks.start_time%TYPE,
2212 v_stop_time hxc_time_building_blocks.stop_time%TYPE,
2213 v_resource_id hxc_time_building_blocks.resource_id%TYPE,
2214 v_resource_type hxc_time_building_blocks.resource_type%TYPE
2215 )
2216 IS
2217 SELECT time_building_block_id, MAX (object_version_number)
2218 FROM hxc_time_building_blocks
2219 WHERE SCOPE = DECODE (v_scope, 'DAY', 'TIMECARD', 'DETAIL', 'DAY')
2220 AND v_start_time >= start_time
2221 AND v_stop_time <= stop_time
2222 AND v_resource_id = resource_id
2223 AND v_resource_type = resource_type
2224 AND date_to = hr_general.end_of_time
2225 GROUP BY time_building_block_id;
2226 BEGIN
2227 g_debug :=hr_utility.debug_enabled;
2228 if g_debug then
2229 hr_utility.set_location ( 'Entering:'
2230 || l_proc, 10);
2231 end if;
2232 OPEN csr_get_parent (
2233 p_scope,
2234 p_start_time,
2235 p_stop_time,
2236 p_resource_id,
2237 p_resource_type
2238 );
2239 FETCH csr_get_parent INTO p_building_block_id, p_object_version_number;
2240 CLOSE csr_get_parent;
2241 if g_debug then
2242 hr_utility.set_location ( 'Leaving:'
2243 || l_proc, 20);
2244 end if;
2245 END find_parent_building_block;
2246 */
2247
2248 -----------------------------------------------------------------------------
2249 -- Type: Procedure
2250 -- Scope: Public
2251 -- Name: get_timecard_tables
2252 -- IN Parameters: p_building_block_id -> TBB Id for which a timecard table
2253 -- needs to be retrieved
2254 -- p_deposit_process -> Deposit process of which the mapping
2255 -- will be used to retrieve the attrs
2256 -- OUT Parameters: p_app_blocks -> TBB Type table that that will hold all
2257 -- all the TBBs found
2258 -- p_app_attributes -> Attribute PL/SQL table that will hold
2259 -- all the attrs found
2260 -- Description: This function will return the index of the row in the PL/SQL
2261 -- table (passed in) that holds the id (also passed in) of the
2262 -- attr that is being looked for. In case the TBattrB is not
2263 -- found in the PL/SQL Table, a negative index is being returned.
2264 --
2265 -----------------------------------------------------------------------------
2266 PROCEDURE get_timecard_tables
2267 (p_building_block_id IN hxc_time_building_blocks.time_building_block_id%TYPE,
2268 -- p_time_recipient_id IN NUMBER,
2269 p_deposit_process IN hxc_deposit_processes.NAME%TYPE,
2270 p_clear_mapping_cache IN BOOLEAN default false,
2271 p_app_blocks OUT NOCOPY hxc_block_table_type,
2272 p_app_attributes OUT NOCOPY hxc_self_service_time_deposit.app_attributes_info
2273 )
2274 IS
2275 l_proc VARCHAR2 (72);
2276 l_timecard_bb_id hxc_time_building_blocks.time_building_block_id%TYPE;
2277 l_object_version_number hxc_time_building_blocks.object_version_number%TYPE;
2278 l_counter PLS_INTEGER;
2279 l_attributes hxc_attribute_table_type;
2280 BEGIN
2281 g_debug := hr_utility.debug_enabled;
2282
2283 IF g_debug
2284 THEN
2285 l_proc := g_package || 'get_timecard_tables';
2286 hr_utility.set_location ('Entering:' || l_proc, 10);
2287 END IF;
2288
2289 -- find out what the ID of the TIMECARD BB for this BB is
2290 get_timecard_bb_id (p_bb_id => p_building_block_id,
2291 p_timecard_bb_id => l_timecard_bb_id,
2292 p_timecard_ovn => l_object_version_number
2293 );
2294
2295 IF g_debug
2296 THEN
2297 hr_utility.set_location ('Timecard BB_ID = ' || l_timecard_bb_id,
2298 15);
2299 END IF;
2300
2301 -- Then get the complete TC
2302 -- First get all the TBBs ...
2303 p_app_blocks :=
2304 hxc_timecard.load_blocks (p_timecard_id => l_timecard_bb_id,
2305 p_timecard_ovn => l_object_version_number
2306 );
2307 -- ... then all the attributes and store them in the Type structures
2308 l_attributes := hxc_timecard.load_attributes (p_blocks => p_app_blocks);
2309 -- Finally convert the attributes to app_attributes.
2310 p_app_attributes :=
2311 hxc_app_attribute_utils.create_app_attributes
2312 (p_attributes => l_attributes,
2313 p_retrieval_process_id => NULL,
2314 p_deposit_process_id => get_deposit_process_id(p_deposit_process)
2315 );
2316
2317 if(p_clear_mapping_cache) then
2318 hxc_app_attribute_utils.clear_mapping_cache;
2319 end if;
2320
2321 IF g_debug
2322 THEN
2323 hr_utility.set_location ('Leaving:' || l_proc, 20);
2324 END IF;
2325 END get_timecard_tables;
2326
2327 -----------------------------------------------------------------------------
2328 -- Type: Procedure
2329 -- Scope: Public
2330 -- Name: get_timecard_tables
2331 -- IN Parameters: p_building_block_id -> TBB Id for which a timecard table
2332 -- needs to be retrieved
2333 -- p_deposit_process -> Deposit process of which the mapping
2334 -- will be used to retrieve the attrs
2335 -- OUT Parameters: p_app_blocks -> TBB PL/SQL table that that will hold all
2336 -- all the TBBs found
2337 -- p_app_attributes -> Attribute PL/SQL table that will hold
2338 -- all the attrs found
2339 -- Description: Overloaded procedure, using old PL/SQL Table for TBBs. See
2340 -- main get_timecard_tables for more information
2341 -----------------------------------------------------------------------------
2342 PROCEDURE get_timecard_tables
2343 (p_building_block_id IN hxc_time_building_blocks.time_building_block_id%TYPE,
2344 -- p_time_recipient_id IN NUMBER,
2345 p_deposit_process IN hxc_deposit_processes.NAME%TYPE,
2346 p_clear_mapping_cache IN BOOLEAN default false,
2347 p_app_blocks OUT NOCOPY hxc_self_service_time_deposit.timecard_info,
2348 p_app_attributes OUT NOCOPY hxc_self_service_time_deposit.app_attributes_info
2349 )
2350 IS
2351 l_proc VARCHAR2 (72);
2352 l_blocks hxc_block_table_type;
2353 BEGIN
2354 g_debug := hr_utility.debug_enabled;
2355
2356 IF g_debug
2357 THEN
2358 l_proc := g_package || 'get_timecard_tables (Overloaded)';
2359 hr_utility.set_location ('Entering:' || l_proc, 10);
2360 END IF;
2361
2362 get_timecard_tables (p_building_block_id => p_building_block_id,
2363 p_deposit_process => p_deposit_process,
2364 p_clear_mapping_cache => p_clear_mapping_cache,
2365 p_app_blocks => l_blocks,
2366 p_app_attributes => p_app_attributes
2367 );
2368 p_app_blocks :=
2369 hxc_timecard_block_utils.convert_to_dpwr_blocks (p_blocks => l_blocks);
2370
2371 IF g_debug
2372 THEN
2373 hr_utility.set_location ('Leaving:' || l_proc, 20);
2374 END IF;
2375 END get_timecard_tables;
2376
2377 -----------------------------------------------------------------------------
2378 -- Type: Procedure
2379 -- Scope: Public
2380 -- Name: get_bld_blk_info_type
2381 -- Returns: hxc_bld_blk_info_types.bld_blk_info_type%TYPE
2382 -- IN Parameters: p_attribute_name -> Name of the attribute
2383 -- p_deposit_process -> Name of the deposit process which we will
2384 -- use the mapping of
2385 -- OUT Parameters: p_bld_blk_info_type -> BB Info Type for the attribute name
2386 -- p_segment -> Segment in which the attribute value needs to
2387 -- get stored.
2388 --
2389 -- Description: Private Procedure that will find the Building Block Info Type
2390 -- of an attribute and the segment in which the attribute needs
2391 -- to get stored. It uses the mapping related to the deposit
2392 -- process (passed in) to work out what the Building Block Info
2393 -- Type is. The approval status is linked (hard coded here) to
2394 -- the mode.
2395 --
2396 -----------------------------------------------------------------------------
2397 PROCEDURE get_bld_blk_info_type (
2398 p_attribute_name IN hxc_mapping_components.field_name%TYPE,
2399 p_deposit_process IN hxc_deposit_processes.NAME%TYPE,
2400 p_bld_blk_info_type OUT NOCOPY hxc_bld_blk_info_types.bld_blk_info_type%TYPE,
2401 p_segment OUT NOCOPY hxc_mapping_components.SEGMENT%TYPE
2402 )
2403 IS
2404 l_proc VARCHAR2 (72);
2405
2406 CURSOR csr_bld_blk_info_type (
2407 v_field_name hxc_mapping_components.field_name%TYPE,
2408 v_deposit_process_id hxc_deposit_processes.deposit_process_id%TYPE
2409 )
2410 IS
2411 SELECT bbit.bld_blk_info_type, mc.SEGMENT
2412 FROM hxc_mapping_components mc,
2413 hxc_mapping_comp_usages mcu,
2414 hxc_mappings m,
2415 hxc_deposit_processes dp,
2416 hxc_bld_blk_info_types bbit,
2417 hxc_bld_blk_info_type_usages bbui
2418 WHERE dp.mapping_id = m.mapping_id
2419 AND m.mapping_id = mcu.mapping_id
2420 AND mcu.mapping_component_id = mc.mapping_component_id
2421 AND mc.bld_blk_info_type_id = bbit.bld_blk_info_type_id
2422 AND bbit.bld_blk_info_type_id = bbui.bld_blk_info_type_id
2423 AND mc.field_name = v_field_name
2424 AND dp.deposit_process_id = v_deposit_process_id;
2425 BEGIN
2426 g_debug := hr_utility.debug_enabled;
2427
2428 IF g_debug
2429 THEN
2430 l_proc := g_package || 'get_bld_blk_info_type';
2431 hr_utility.set_location ('Entering:' || l_proc, 10);
2432 END IF;
2433
2434 OPEN csr_bld_blk_info_type (p_attribute_name,
2435 get_deposit_process_id (p_deposit_process)
2436 );
2437
2438 FETCH csr_bld_blk_info_type
2439 INTO p_bld_blk_info_type, p_segment;
2440
2441 CLOSE csr_bld_blk_info_type;
2442
2443 IF g_debug
2444 THEN
2445 hr_utility.set_location ( ' Found Building Block Info Type = '
2446 || p_bld_blk_info_type,
2447 20
2448 );
2449 hr_utility.set_location (' Found Segment = ' || p_segment, 30);
2450 hr_utility.set_location ('Leaving:' || l_proc, 40);
2451 END IF;
2452 END get_bld_blk_info_type;
2453
2454 -----------------------------------------------------------------------------
2455 -- Type: Procedure
2456 -- Scope: Public
2457 -- Name: clear_building_block_table
2458 -- INOUT Parameters: p_app_blocks -> TBB Table Type you want to empty
2459 --
2460 -- Description: This procedure will allow you to clear the TBB PL/SQL Table
2461 --
2462 -----------------------------------------------------------------------------
2463 PROCEDURE clear_building_block_table (
2464 p_app_blocks IN OUT NOCOPY hxc_block_table_type
2465 )
2466 IS
2467 l_proc VARCHAR2 (72);
2468 BEGIN
2469 g_debug := hr_utility.debug_enabled;
2470
2471 IF g_debug
2472 THEN
2473 l_proc := g_package || 'clear_building_block_table';
2474 hr_utility.set_location ('Entering:' || l_proc, 10);
2475 END IF;
2476
2477 IF p_app_blocks.COUNT > 0
2478 THEN
2479 p_app_blocks.DELETE;
2480 END IF;
2481
2482 IF g_debug
2483 THEN
2484 hr_utility.set_location ('Leaving:' || l_proc, 20);
2485 END IF;
2486 END clear_building_block_table;
2487
2488 -----------------------------------------------------------------------------
2489 -- Type: Procedure
2490 -- Scope: Public
2491 -- Name: clear_attribute_table
2492 -- INOUT Parameters: p_app_attributes -> Attr PL/SQL table you want to empty
2493 --
2494 -- Description: This procedure will allow you to clear the Attr PL/SQL Table
2495 --
2496 -----------------------------------------------------------------------------
2497 PROCEDURE clear_attribute_table (
2498 p_app_attributes IN OUT NOCOPY hxc_self_service_time_deposit.app_attributes_info
2499 )
2500 IS
2501 l_proc VARCHAR2 (72);
2502 BEGIN
2503 g_debug := hr_utility.debug_enabled;
2504
2505 IF g_debug
2506 THEN
2507 l_proc := g_package || 'clear_attribute_table';
2508 hr_utility.set_location ('Entering:' || l_proc, 10);
2509 END IF;
2510
2511 IF p_app_attributes.COUNT > 0
2512 THEN
2513 p_app_attributes.DELETE;
2514 END IF;
2515
2516 IF g_debug
2517 THEN
2518 hr_utility.set_location ('Leaving:' || l_proc, 20);
2519 END IF;
2520 END clear_attribute_table;
2521
2522 -----------------------------------------------------------------------------
2523 -- Type: Procedure
2524 -- Scope: Public
2525 -- Name: clear_message_table
2526 -- INOUT Parameters: p_messages -> Message PL/SQL table you want to empty
2527 --
2528 -- Description: This procedure will allow you to clear the Message PL/SQL Table
2529 --
2530 -----------------------------------------------------------------------------
2531 PROCEDURE clear_message_table (
2532 p_messages IN OUT NOCOPY hxc_message_table_type
2533 )
2534 IS
2535 l_proc VARCHAR2 (72);
2536 BEGIN
2537 g_debug := hr_utility.debug_enabled;
2538
2539 IF g_debug
2540 THEN
2541 l_proc := g_package || 'clear_message_table';
2542 hr_utility.set_location ('Entering:' || l_proc, 10);
2543 END IF;
2544
2545 IF p_messages.COUNT > 0
2546 THEN
2547 p_messages.DELETE;
2548 END IF;
2549
2550 IF g_debug
2551 THEN
2552 hr_utility.set_location ('Leaving:' || l_proc, 20);
2553 END IF;
2554 END clear_message_table;
2555
2556 -----------------------------------------------------------------------------
2557 -- Type: Procedure
2558 -- Scope: Public
2559 -- Name: request_lock
2560 -- IN Parameters: p_app_blocks -> TBB PL/SQL table you want to lock
2561 -- IN OUT Parameters: p_messages -> Message PL/SQL table
2562 -- OUT Parameters: p_locked_success -> Lock was successful or not
2563 -- p_row_lock_id -> lock id of the lock
2564 -- Description: This procedure will allow you to lock the period for which you
2565 -- are about to create or update a TC.
2566 --
2567 -----------------------------------------------------------------------------
2568 PROCEDURE request_lock (
2569 p_app_blocks IN hxc_block_table_type,
2570 p_messages IN OUT NOCOPY hxc_message_table_type,
2571 p_locked_success OUT NOCOPY BOOLEAN,
2572 p_row_lock_id OUT NOCOPY ROWID
2573 )
2574 IS
2575 l_proc VARCHAR2 (72);
2576 l_tc_index PLS_INTEGER;
2577 BEGIN
2578 g_debug := hr_utility.debug_enabled;
2579
2580 IF g_debug
2581 THEN
2582 l_proc := g_package || 'request_lock';
2583 hr_utility.set_location ('Entering:' || l_proc, 10);
2584 END IF;
2585
2586 l_tc_index :=
2587 hxc_timecard_block_utils.find_active_timecard_index (p_app_blocks);
2588
2589 IF (hxc_timecard_block_utils.is_new_block (p_app_blocks (l_tc_index)))
2590 THEN
2591 hxc_lock_api.request_lock
2592 (p_process_locker_type => hxc_lock_util.c_plsql_deposit_action,
2593 p_resource_id => p_app_blocks (l_tc_index).resource_id,
2594 p_start_time => fnd_date.canonical_to_date
2595 (p_app_blocks (l_tc_index).start_time
2596 ),
2597 p_stop_time => fnd_date.canonical_to_date
2598 (p_app_blocks (l_tc_index).stop_time
2599 ),
2600 p_time_building_block_id => NULL,
2601 p_time_building_block_ovn => NULL,
2602 p_row_lock_id => p_row_lock_id,
2603 p_messages => p_messages,
2604 p_locked_success => p_locked_success
2605 );
2606 ELSE
2607 hxc_lock_api.request_lock
2608 (p_process_locker_type => hxc_lock_util.c_plsql_deposit_action,
2609 p_resource_id => NULL,
2610 p_start_time => NULL,
2611 p_stop_time => NULL,
2612 p_time_building_block_id => p_app_blocks (l_tc_index).time_building_block_id,
2613 p_time_building_block_ovn => p_app_blocks (l_tc_index).object_version_number,
2614 p_row_lock_id => p_row_lock_id,
2615 p_messages => p_messages,
2616 p_locked_success => p_locked_success
2617 );
2618 END IF;
2619
2620 IF g_debug
2621 THEN
2622 hr_utility.set_location ('Leaving:' || l_proc, 20);
2623 END IF;
2624 END request_lock;
2625
2626 -----------------------------------------------------------------------------
2627 -- Type: Procedure
2628 -- Scope: Public
2629 -- Name: release_lock
2630 -- IN Parameters: p_app_blocks -> TBB PL/SQL table you want to release the lock
2631 -- for
2632 -- IN OUT Parameters: p_messages -> Message PL/SQL table
2633 -- OUT Parameters: p_released_success -> Lock was released successful or not
2634 -- p_row_lock_id -> lock id of the lock you want to release
2635 -- Description: This procedure will allow you to release the lock on the
2636 -- period that you grabbed with request_lock.
2637 --
2638 -----------------------------------------------------------------------------
2639 PROCEDURE release_lock (
2640 p_app_blocks IN hxc_block_table_type,
2641 p_messages IN OUT NOCOPY hxc_message_table_type,
2642 p_released_success OUT NOCOPY BOOLEAN,
2643 p_row_lock_id IN OUT NOCOPY ROWID
2644 )
2645 IS
2646 l_proc VARCHAR2 (72);
2647 l_tc_index PLS_INTEGER;
2648 BEGIN
2649 g_debug := hr_utility.debug_enabled;
2650
2651 IF g_debug
2652 THEN
2653 l_proc := g_package || 'release_lock';
2654 hr_utility.set_location ('Entering:' || l_proc, 10);
2655 END IF;
2656
2657 l_tc_index :=
2658 hxc_timecard_block_utils.find_active_timecard_index (p_app_blocks);
2659
2660 IF (hxc_timecard_block_utils.is_new_block (p_app_blocks (l_tc_index)))
2661 THEN
2662 hxc_lock_api.release_lock
2663 (p_row_lock_id => p_row_lock_id,
2664 p_process_locker_type => hxc_lock_util.c_plsql_deposit_action,
2665 p_resource_id => p_app_blocks (l_tc_index).resource_id,
2666 p_start_time => fnd_date.canonical_to_date
2667 (p_app_blocks (l_tc_index).start_time
2668 ),
2669 p_stop_time => fnd_date.canonical_to_date
2670 (p_app_blocks (l_tc_index).stop_time
2671 ),
2672 p_time_building_block_id => NULL,
2673 p_time_building_block_ovn => NULL,
2674 p_messages => p_messages,
2675 p_released_success => p_released_success
2676 );
2677 ELSE
2678 hxc_lock_api.release_lock
2679 (p_row_lock_id => p_row_lock_id,
2680 p_process_locker_type => hxc_lock_util.c_plsql_deposit_action,
2681 p_resource_id => NULL,
2682 p_start_time => NULL,
2683 p_stop_time => NULL,
2684 p_time_building_block_id => p_app_blocks (l_tc_index).time_building_block_id,
2685 p_time_building_block_ovn => p_app_blocks (l_tc_index).object_version_number,
2686 p_messages => p_messages,
2687 p_released_success => p_released_success
2688 );
2689 END IF;
2690
2691 IF g_debug
2692 THEN
2693 hr_utility.set_location ('Leaving:' || l_proc, 20);
2694 END IF;
2695 END release_lock;
2696
2697 -----------------------------------------------------------------------------
2698 -- Type: Procedure
2699 -- Scope: Public
2700 -- Name: log_timecard
2701 -- IN Parameters: p_app_blocks -> TBB type you want to log
2702 -- p_app_attributes -> Attr PL/SQL table you want to log
2703 -- Description: This procedure will allow you log the complete timecard and
2704 -- all it's associated attributes in fnd_log_messages. For this
2705 -- to work you will have to set the the profile option
2706 -- FND: Debug Log Enabled = Yes and FND: Debug Log Level =
2707 -- Statement. Also your code must contain a line in the beginning
2708 -- that looks like this:
2709 -- FND_GLOBAL.APPS_INITIALIZE
2710 -- ( user_id => <user id you are loggin msg under>,
2711 -- resp_id => <responsibility id of the user>
2712 -- resp_appl_id => 809 )
2713 -- ;
2714 --
2715 -- This procedure is provided for debugging purposes.
2716 --
2717 -----------------------------------------------------------------------------
2718 PROCEDURE log_timecard (
2719 p_app_blocks IN hxc_block_table_type,
2720 p_app_attributes IN hxc_self_service_time_deposit.app_attributes_info
2721 )
2722 IS
2723 l_proc VARCHAR2 (72);
2724 i PLS_INTEGER;
2725 j PLS_INTEGER;
2726 BEGIN
2727 g_debug := hr_utility.debug_enabled;
2728
2729 IF g_debug
2730 THEN
2731 l_proc := g_package || 'log_timecard';
2732 hr_utility.set_location ('Entering:' || l_proc, 10);
2733 hr_utility.set_location (' Number of BBs:' || p_app_blocks.COUNT,
2734 15
2735 );
2736 hr_utility.set_location ( ' Number of Attrs:'
2737 || p_app_attributes.COUNT,
2738 16
2739 );
2740 END IF;
2741
2742 i := p_app_blocks.FIRST;
2743
2744 <<print_time_building_blocks>>
2745 LOOP
2746 EXIT print_time_building_blocks WHEN (NOT p_app_blocks.EXISTS (i));
2747
2748 IF g_debug
2749 THEN
2750 hr_utility.TRACE
2751 ( LPAD
2752 (NVL (TO_CHAR (p_app_blocks (i).time_building_block_id),
2753 ' '
2754 ),
2755 9
2756 )
2757 || ' '
2758 || RPAD (NVL (p_app_blocks (i).TYPE, ' '), 7)
2759 || ' '
2760 || LPAD (NVL (TO_CHAR (p_app_blocks (i).measure), ' '), 7)
2761 || ' '
2762 || RPAD (NVL (p_app_blocks (i).unit_of_measure, ' '), 5)
2763 || ' '
2764 || RPAD (NVL (p_app_blocks (i).start_time, ' '), 20)
2765 || ' '
2766 || RPAD (NVL (p_app_blocks (i).stop_time, ' '), 20)
2767 || ' '
2768 || LPAD
2769 (NVL (TO_CHAR (p_app_blocks (i).parent_building_block_id),
2770 ' '
2771 ),
2772 9
2773 )
2774 || ' '
2775 || RPAD (NVL (p_app_blocks (i).parent_is_new, ' '), 2)
2776 || ' '
2777 || RPAD (NVL (p_app_blocks (i).SCOPE, ' '), 8)
2778 || ' '
2779 || LPAD (NVL (TO_CHAR (p_app_blocks (i).object_version_number),
2780 ' '
2781 ),
2782 5
2783 )
2784 || ' '
2785 || RPAD (NVL (p_app_blocks (i).approval_status, ' '), 10)
2786 || ' '
2787 || LPAD (NVL (TO_CHAR (p_app_blocks (i).resource_id), ' '),
2788 10)
2789 || ' '
2790 || RPAD (NVL (p_app_blocks (i).resource_type, ' '), 10)
2791 || ' '
2792 || LPAD (NVL (TO_CHAR (p_app_blocks (i).approval_style_id),
2793 ' '
2794 ),
2795 5
2796 )
2797 || ' '
2798 || RPAD (p_app_blocks (i).date_from, 11)
2799 || ' '
2800 || RPAD (p_app_blocks (i).date_to, 11)
2801 || ' '
2802 || RPAD (NVL (p_app_blocks (i).comment_text, ' '), 30)
2803 || ' '
2804 || RPAD
2805 (NVL
2806 (TO_CHAR (p_app_blocks (i).parent_building_block_ovn),
2807 ' '
2808 ),
2809 5
2810 )
2811 || ' '
2812 || RPAD (NVL (p_app_blocks (i).NEW, ' '), 2)
2813 || ' '
2814 || RPAD (NVL (p_app_blocks (i).changed, ' '), 2)
2815 || ' '
2816 );
2817 END IF;
2818
2819 -- Find out if this BB has any attributes
2820 -- if so, print them with this BB
2821 j := p_app_attributes.FIRST;
2822
2823 <<print_attributes>>
2824 LOOP
2825 EXIT print_attributes WHEN (NOT p_app_attributes.EXISTS (j));
2826
2827 IF p_app_blocks (i).time_building_block_id =
2828 p_app_attributes (j).building_block_id
2829 THEN
2830 IF g_debug
2831 THEN
2832 hr_utility.TRACE
2833 ( ' '
2834 || --indent to indicate attribute
2835 LPAD (p_app_attributes (j).time_attribute_id,
2836 9
2837 )
2838 || ' '
2839 || LPAD (p_app_attributes (j).building_block_id,
2840 9
2841 )
2842 || ' '
2843 || RPAD (p_app_attributes (j).attribute_name,
2844 20
2845 )
2846 || ' '
2847 || RPAD (p_app_attributes (j).attribute_value,
2848 20
2849 )
2850 || ' '
2851 || RPAD (p_app_attributes (j).bld_blk_info_type,
2852 20
2853 )
2854 || ' '
2855 || RPAD (p_app_attributes (j).CATEGORY, 20)
2856 || ' '
2857 || RPAD (p_app_attributes (j).updated, 2)
2858 || ' '
2859 || RPAD (p_app_attributes (j).changed, 2)
2860 || ' '
2861 );
2862 END IF;
2863 END IF;
2864
2865 j := p_app_attributes.NEXT (j);
2866 END LOOP print_attributes;
2867
2868 i := p_app_blocks.NEXT (i);
2869 END LOOP print_time_building_blocks;
2870
2871 IF g_debug
2872 THEN
2873 hr_utility.set_location ('Leaving:' || l_proc, 20);
2874 END IF;
2875 END log_timecard;
2876
2877 -----------------------------------------------------------------------------
2878 -- Type: Procedure
2879 -- Scope: Public
2880 -- Name: log_timecard
2881 -- IN Parameters: p_app_blocks -> TBB PL/SQL table you want to log
2882 -- p_app_attributes -> Attr PL/SQL table you want to log
2883 -- Description: This procedure will allow you log the complete timecard and
2884 -- all it's associated attributes in fnd_log_messages. For this
2885 -- to work you will have to set the the profile option
2886 -- FND: Debug Log Enabled = Yes and FND: Debug Log Level =
2887 -- Statement. Also your code must contain a line in the beginning
2888 -- that looks like this:
2889 -- FND_GLOBAL.APPS_INITIALIZE
2890 -- ( user_id => <user id you are loggin msg under>,
2891 -- resp_id => <responsibility id of the user>
2892 -- resp_appl_id => 809 )
2893 -- ;
2894 --
2895 -- This procedure is provided for debugging purposes.
2896 --
2897 -----------------------------------------------------------------------------
2898 PROCEDURE log_timecard (
2899 p_app_blocks IN hxc_self_service_time_deposit.timecard_info,
2900 p_app_attributes IN hxc_self_service_time_deposit.app_attributes_info
2901 )
2902 IS
2903 l_proc VARCHAR2 (72);
2904 l_blocks hxc_block_table_type;
2905 BEGIN
2906 g_debug := hr_utility.debug_enabled;
2907
2908 IF g_debug
2909 THEN
2910 l_proc := g_package || 'log_timecard (Overloaded)';
2911 hr_utility.set_location ('Entering:' || l_proc, 10);
2912 END IF;
2913
2914 l_blocks := convert_tbb_to_type (p_blocks => p_app_blocks);
2915 log_timecard (p_app_blocks => l_blocks,
2916 p_app_attributes => p_app_attributes
2917 );
2918
2919 IF g_debug
2920 THEN
2921 hr_utility.set_location ('Leaving:' || l_proc, 20);
2922 END IF;
2923 END log_timecard;
2924
2925 -----------------------------------------------------------------------------
2926 -- Type: Procedure
2927 -- Scope: Public
2928 -- Name: log_messages
2929 -- IN Parameters: p_messages -> Message type you want to log
2930 -- Description: This procedure will allow you log the messages returned by the
2931 -- deposit process.
2932 -- For this to work you will have to set the the profile option
2933 -- FND: Debug Log Enabled = Yes and FND: Debug Log Level =
2934 -- Statement. Also your code must contain a line in the beginning
2935 -- that looks like this:
2936 -- FND_GLOBAL.APPS_INITIALIZE
2937 -- ( user_id => <user id you are loggin msg under>,
2938 -- resp_id => <responsibility id of the user>
2939 -- resp_appl_id => 809 )
2940 -- ;
2941 --
2942 -- This procedure is provided for debugging purposes.
2943 --
2944 -----------------------------------------------------------------------------
2945 PROCEDURE log_messages (p_messages IN hxc_message_table_type)
2946 IS
2947 l_proc VARCHAR2 (72);
2948 l_message fnd_new_messages.MESSAGE_TEXT%TYPE;
2949 i PLS_INTEGER;
2950 BEGIN
2951 g_debug := hr_utility.debug_enabled;
2952
2953 IF g_debug
2954 THEN
2955 l_proc := g_package || 'log_messages';
2956 hr_utility.set_location ('Entering:' || l_proc, 10);
2957 END IF;
2958
2959 IF (p_messages IS NOT NULL)
2960 THEN -- messages have been returned
2961 i := p_messages.FIRST;
2962
2963 LOOP
2964 EXIT WHEN (NOT p_messages.EXISTS (i));
2965 -- First translate the message as the messagetable returned does not give the actual
2966 -- message, only the message_name which doesn't mean anything to the user.
2967 l_message :=
2968 fnd_message.get_string
2969 (appin => p_messages (i).application_short_name,
2970 namein => p_messages (i).message_name
2971 );
2972
2973 IF g_debug
2974 THEN
2975 hr_utility.TRACE
2976 ( RPAD (p_messages (i).application_short_name, 10)
2977 || ' '
2978 || LPAD (p_messages (i).time_building_block_id, 7)
2979 || ' '
2980 || LPAD
2981 (NVL (TO_CHAR (p_messages (i).time_building_block_ovn),
2982 ' '
2983 ),
2984 5
2985 )
2986 || ' '
2987 || LPAD (NVL (TO_CHAR (p_messages (i).time_attribute_id),
2988 ' '
2989 ),
2990 7
2991 )
2992 || ' '
2993 || LPAD (p_messages (i).time_attribute_ovn, 5)
2994 || ' '
2995 || RPAD (p_messages (i).message_name, 30)
2996 || ' '
2997 || RPAD (l_message, 80)
2998 || ' '
2999 || RPAD (p_messages (i).message_level, 10)
3000 || ' '
3001 || RPAD (p_messages (i).message_field, 30)
3002 || ' '
3003 || RPAD (p_messages (i).message_tokens, 30)
3004 || ' '
3005 -- rpad(p_messages(i).on_oa_msg_stack,30)||' '
3006 );
3007 END IF;
3008
3009 i := p_messages.NEXT (i);
3010 END LOOP;
3011 ELSE
3012 IF g_debug
3013 THEN
3014 hr_utility.TRACE
3015 (' --- No Errors Found, Timecard Deposit Successfull! ---');
3016 END IF;
3017 END IF;
3018
3019 IF g_debug
3020 THEN
3021 hr_utility.set_location ('Leaving:' || l_proc, 20);
3022 END IF;
3023 END log_messages;
3024
3025 -----------------------------------------------------------------------------
3026 -- Type: Procedure
3027 -- Scope: Public
3028 -- Name: log_messages
3029 -- IN Parameters: p_messages -> Message PL/SQL table you want to log
3030 -- p_retrieval_process_id -> process id for retrieval
3031 -- Description: This procedure will allow you log the messages returned by the
3032 -- deposit process.
3033 -- For this to work you will have to set the the profile option
3034 -- FND: Debug Log Enabled = Yes and FND: Debug Log Level =
3035 -- Statement. Also your code must contain a line in the beginning
3036 -- that looks like this:
3037 -- FND_GLOBAL.APPS_INITIALIZE
3038 -- ( user_id => <user id you are loggin msg under>,
3039 -- resp_id => <responsibility id of the user>
3040 -- resp_appl_id => 809 )
3041 -- ;
3042 --
3043 -- This procedure is provided for debugging purposes.
3044 --
3045 -----------------------------------------------------------------------------
3046 PROCEDURE log_messages (
3047 p_messages IN hxc_self_service_time_deposit.message_table
3048 )
3049 IS
3050 l_proc VARCHAR2 (72);
3051 l_messages hxc_message_table_type;
3052 BEGIN
3053 g_debug := hr_utility.debug_enabled;
3054
3055 IF g_debug
3056 THEN
3057 l_proc := g_package || 'log_messages (Overloaded)';
3058 hr_utility.set_location ('Entering:' || l_proc, 10);
3059 END IF;
3060
3061 hxc_timecard_message_utils.append_old_messages
3062 (p_messages => l_messages,
3063 p_old_messages => p_messages,
3064 p_retrieval_process_id => NULL
3065 );
3066 log_messages (p_messages => l_messages);
3067
3068 IF g_debug
3069 THEN
3070 hr_utility.set_location ('Leaving:' || l_proc, 20);
3071 END IF;
3072 END log_messages;
3073
3074 -----------------------------------------------------------------------------
3075 -- Type: Function
3076 -- Scope: Public
3077 -- Name: translate_message_table
3078 -- Returns: hxc_self_service_time_deposit.message_table
3079 -- IN Parameters: p_messages -> Message PL/SQL table you want to use for
3080 -- replacing tokens.
3081 -- Description: This procedure will take the message table from the deposit
3082 -- wrapper and construct a new table that will have the proper
3083 -- message in it with all tokens properly filled in.
3084 -- This procedure is provided for debugging purposes.
3085 --
3086 -----------------------------------------------------------------------------
3087 FUNCTION translate_message_table (
3088 p_messages IN hxc_self_service_time_deposit.message_table
3089 )
3090 RETURN translated_message_table
3091 IS
3092 l_proc VARCHAR2 (72);
3093 l_messages translated_message_table;
3094 l_token_table hxc_deposit_wrapper_utilities.t_simple_table;
3095 l_message_idx PLS_INTEGER := p_messages.FIRST;
3096 BEGIN
3097 g_debug := hr_utility.debug_enabled;
3098
3099 IF g_debug
3100 THEN
3101 l_proc := g_package || 'translate_message_table';
3102 hr_utility.set_location ('Entering:' || l_proc, 10);
3103 END IF;
3104
3105 <<process_all_messages>>
3106 LOOP
3107 EXIT process_all_messages WHEN NOT p_messages.EXISTS (l_message_idx);
3108 -- set message on stack so we can work with it
3109 fnd_message.set_name
3110 (p_messages (l_message_idx).application_short_name,
3111 p_messages (l_message_idx).message_name
3112 );
3113
3114 IF (p_messages (l_message_idx).message_tokens IS NOT NULL)
3115 THEN
3116 hxc_deposit_wrapper_utilities.string_to_table
3117 ('&',
3118 '&'
3119 || p_messages (l_message_idx).message_tokens,
3120 l_token_table
3121 );
3122
3123 FOR l_token IN 0 .. (l_token_table.COUNT / 2) - 1
3124 LOOP
3125 -- replace all tokens in the message on the stack
3126 fnd_message.set_token (token => l_token_table (2 * l_token),
3127 VALUE => l_token_table ( 2
3128 * l_token
3129 + 1
3130 )
3131 );
3132 END LOOP;
3133 END IF;
3134
3135 -- get the message back from the stack and put in the message table...
3136 l_messages (l_message_idx).message_name :=
3137 p_messages (l_message_idx).message_name;
3138 -- ... and just copy the other fields for convinience.
3139 l_messages (l_message_idx).MESSAGE_TEXT := fnd_message.get;
3140 l_messages (l_message_idx).time_building_block_id :=
3141 p_messages (l_message_idx).time_building_block_id;
3142 l_messages (l_message_idx).time_building_block_ovn :=
3143 p_messages (l_message_idx).time_building_block_ovn;
3144 l_messages (l_message_idx).time_attribute_id :=
3145 p_messages (l_message_idx).time_attribute_id;
3146 l_messages (l_message_idx).time_attribute_ovn :=
3147 p_messages (l_message_idx).time_attribute_ovn;
3148 l_message_idx := p_messages.NEXT (l_message_idx);
3149 END LOOP process_all_messages;
3150
3151 IF g_debug
3152 THEN
3153 hr_utility.set_location ('Leaving:' || l_proc, 100);
3154 END IF;
3155
3156 RETURN l_messages;
3157 END translate_message_table;
3158
3159 -----------------------------------------------------------------------------
3160 -- Type: Procedure
3161 -- Scope: Public
3162 -- Name: find_current_period
3163 -- IN Parameters: p_resource_id ->
3164 -- p_resource_type ->
3165 -- p_day ->
3166 -- OUT Parameters: p_start_time ->
3167 -- p_stop_time ->
3168 -- Description: This procedure will try to work out the start and end date for
3169 -- the timecard to be created. This can easily be worked out
3170 -- from the TC recuring period, but we also need to take into
3171 -- consideration the effective dates of the active assignments
3172 -- for the resource in question, i.e. we should not let the TC
3173 -- start before an assignment exists for the resouce.
3174 --
3175 -----------------------------------------------------------------------------
3176 PROCEDURE find_current_period (
3177 p_resource_id IN hxc_time_building_blocks.resource_id%TYPE,
3178 p_resource_type IN hxc_time_building_blocks.resource_type%TYPE,
3179 p_day IN hxc_time_building_blocks.start_time%TYPE,
3180 p_start_time OUT NOCOPY DATE,
3181 p_stop_time OUT NOCOPY DATE
3182 )
3183 IS
3184 l_proc VARCHAR2 (72);
3185 l_periods hxc_timecard_utilities.periods;
3186 l_period_idx PLS_INTEGER;
3187 l_period_found BOOLEAN DEFAULT FALSE;
3188 BEGIN
3189 IF g_debug
3190 THEN
3191 l_proc := g_package || 'find_current_period';
3192 hr_utility.set_location ('Entering:' || l_proc, 10);
3193 END IF;
3194
3195 hxc_timecard_utilities.init_globals (p_resource_id => p_resource_id);
3196 l_periods :=
3197 hxc_timecard_utilities.get_periods
3198 (p_resource_id => p_resource_id,
3199 p_resource_type => p_resource_type,
3200 p_current_date => p_day,
3201 p_show_existing_timecard => 'Y'
3202 );
3203 l_period_idx := l_periods.FIRST;
3204
3205 <<find_period>>
3206 LOOP
3207 EXIT find_period WHEN NOT l_periods.EXISTS (l_period_idx)
3208 OR l_period_found;
3209
3210 IF (TRUNC (p_day) BETWEEN TRUNC(l_periods (l_period_idx).start_date)
3211 AND TRUNC(l_periods (l_period_idx).end_date)
3212 )
3213 THEN
3214 p_start_time :=
3215 TO_DATE ( TO_CHAR (l_periods (l_period_idx).start_date,
3216 'DD-MM-YYYY'
3217 )
3218 || ' 00:00:00',
3219 'DD-MM-YYYY HH24:MI:SS'
3220 );
3221 p_stop_time :=
3222 TO_DATE ( TO_CHAR (l_periods (l_period_idx).end_date,
3223 'DD-MM-YYYY'
3224 )
3225 || ' 23:59:59',
3226 'DD-MM-YYYY HH24:MI:SS'
3227 );
3228 l_period_found := TRUE;
3229 END IF;
3230
3231 l_period_idx := l_periods.NEXT (l_period_idx);
3232 END LOOP find_period;
3233 END find_current_period;
3234
3235 FUNCTION cla_enabled (
3236 p_building_block_id IN hxc_time_building_blocks.time_building_block_id%TYPE
3237 )
3238 RETURN BOOLEAN
3239 AS
3240 l_cla_enabled BOOLEAN;
3241 l_cla_terg_id hxc_pref_hierarchies.attribute1%TYPE;
3242
3243 FUNCTION resource_id (
3244 p_building_block_id IN hxc_time_building_blocks.time_building_block_id%TYPE
3245 )
3246 RETURN hxc_time_building_blocks.resource_id%TYPE
3247 AS
3248 l_resource_id hxc_time_building_blocks.resource_id%TYPE;
3249 BEGIN
3250 SELECT MAX (htbb.resource_id)
3251 INTO l_resource_id
3252 FROM hxc_time_building_blocks htbb
3253 WHERE htbb.time_building_block_id = p_building_block_id
3254 AND resource_type = 'PERSON';
3255
3256 RETURN l_resource_id;
3257 END resource_id;
3258 /*
3259 || MAIN
3260 */
3261 BEGIN
3262
3263 <<get_cla_id>>
3264 BEGIN
3265 l_cla_terg_id :=
3266 hxc_preference_evaluation.resource_preferences
3267 (p_resource_id => resource_id
3268 (p_building_block_id),
3269 p_pref_code => 'TS_PER_AUDIT_REQUIREMENTS',
3270 p_attribute_n => 1,
3271 p_evaluation_date => SYSDATE
3272 );
3273 EXCEPTION
3274 WHEN OTHERS
3275 THEN
3276 -- Since we want to be as restrictive as possible, and CLA ON is more
3277 -- restrictive than CLA OFF, we just emulate CLA ON when no CLA
3278 -- preference has been found by setting the id to -1.
3279 l_cla_terg_id := -1;
3280 END get_cla_id;
3281
3282 IF (l_cla_terg_id IS NOT NULL)
3283 THEN
3284 l_cla_enabled := TRUE;
3285 ELSE
3286 l_cla_enabled := FALSE;
3287 END IF;
3288
3289 RETURN l_cla_enabled;
3290 END cla_enabled;
3291 END hxc_timestore_deposit_util;