1 PACKAGE BODY hxc_timestore_deposit AS
2 /* $Header: hxctsdp.pkb 120.8.12020000.2 2012/11/08 09:45:56 asrajago ship $ */
3
4 -- Global package name
5 g_package CONSTANT VARCHAR2 (33) := 'hxc_timestore_deposit.';
6 g_debug BOOLEAN := hr_utility.debug_enabled;
7
8 PROCEDURE get_timecard_tables (
9 p_building_block_id IN hxc_time_building_blocks.time_building_block_id%TYPE,
10 p_deposit_process IN hxc_deposit_processes.NAME%TYPE,
11 p_clear_mapping_cache IN BOOLEAN DEFAULT FALSE,
12 p_app_blocks OUT NOCOPY hxc_block_table_type,
13 p_app_attributes OUT NOCOPY hxc_self_service_time_deposit.app_attributes_info
14 )
15 IS
16 BEGIN
17 hxc_timestore_deposit_util.get_timecard_tables (p_building_block_id,
18 p_deposit_process,
19 p_clear_mapping_cache,
20 p_app_blocks,
21 p_app_attributes
22 );
23 END get_timecard_tables;
24
25 PROCEDURE get_timecard_tables (
26 p_building_block_id IN hxc_time_building_blocks.time_building_block_id%TYPE,
27 p_deposit_process IN hxc_deposit_processes.NAME%TYPE,
28 p_clear_mapping_cache IN BOOLEAN DEFAULT FALSE,
29 p_app_blocks OUT NOCOPY hxc_self_service_time_deposit.timecard_info,
30 p_app_attributes OUT NOCOPY hxc_self_service_time_deposit.app_attributes_info
31 )
32 IS
33 BEGIN
34 hxc_timestore_deposit_util.get_timecard_tables (p_building_block_id,
35 p_deposit_process,
36 p_clear_mapping_cache,
37 p_app_blocks,
38 p_app_attributes
39 );
40 END get_timecard_tables;
41
42 -----------------------------------------------------------------------------
43 -- Type: Procedure
44 -- Scope: Public
45 -- Name: create_bb
46 -- IN Parameters: p_time building_block_id -> Dummy TBB Id for the TBB, this
47 -- is needed to link potential child
48 -- TBBs to this TBB.
49 -- p_type -> 'MEASURE' or 'RANGE'
50 -- p_measure -> The actual time recorded, e.g. 8 Only provide
51 -- when type is MEASURE
52 -- p_unit_of_measure -> Units of measure for previous parameter
53 -- defaults to 'HOURS', use default
54 -- p_start_time -> The IN time Only provide when type is RANGE
55 -- p_stop_time -> The OUT time Only provide when type is RANGE
56 -- p_parent_building_block_id -> Id of the TBB to which this TBB
57 -- needs to be attached
58 -- p_parent_is_new -> Set to 'Y', if parent does not exist yet
59 -- in the database, i.e. gets created together
60 -- with this TBB
61 -- p_scope -> 'TIMECARD', 'DAY' or 'DETAIL'
62 -- p_object_version_number -> ovn of the TBB you try to create
63 -- Defaults to 1, use detault
64 -- p_approval_status -> 'WORKING' or 'SUBMITTED'
65 -- p_resource_id -> Person Id to which the TBB needs to be attached
66 -- p_resource_type -> 'PERSON'
67 -- p_approval_style_id -> The Id of the approval style used
68 -- to approve the TBB
69 -- p_date_from -> date from which the TBB is valid. defaults
70 -- to SYSDATE, use default
71 -- p_date_to -> date till which the TBB is valid. defaults to
72 -- to hr_general.end_of_time, use default
73 -- p_comment_text -> comment to be saved with TBB
74 -- p_parent_building_block_ovn -> ovn of the parent TBB, should
75 -- always be the highest ovn
76 -- as we do not allow attaching
77 -- TBBs to old TBBs
78 -- p_new -> For new TBBs this needs to be Y, defaults to 'Y',
79 -- use default
80 -- p_changed -> For new TBBs this needs to be N, defaults to
81 -- 'N', use default
82 -- INOUT Parameters: p_app_blocks -> TBB Type Table to which the new TBB will be
83 -- added.
84 --
85 -- Description: This procedure will allow you to add a TBB of any type to
86 -- the TBB PL/SQL table (passed in). The BB will be added
87 -- to the same TC as all the other BB already in the table
88 -- If you want to add BBs to another table, please clear it
89 -- first and start afresh
90 --
91 -----------------------------------------------------------------------------
92 PROCEDURE create_bb (
93 p_time_building_block_id IN hxc_time_building_blocks.time_building_block_id%TYPE,
94 p_type IN hxc_time_building_blocks.TYPE%TYPE,
95 p_measure IN hxc_time_building_blocks.measure%TYPE,
96 p_unit_of_measure IN hxc_time_building_blocks.unit_of_measure%TYPE,
97 p_start_time IN hxc_time_building_blocks.start_time%TYPE,
98 p_stop_time IN hxc_time_building_blocks.stop_time%TYPE,
99 p_parent_building_block_id IN hxc_time_building_blocks.parent_building_block_id%TYPE,
100 p_parent_is_new IN VARCHAR2,
101 p_scope IN hxc_time_building_blocks.SCOPE%TYPE,
102 p_object_version_number IN hxc_time_building_blocks.object_version_number%TYPE,
103 p_approval_status IN hxc_time_building_blocks.approval_status%TYPE,
104 p_resource_id IN hxc_time_building_blocks.resource_id%TYPE,
105 p_resource_type IN hxc_time_building_blocks.resource_type%TYPE,
106 p_approval_style_id IN hxc_time_building_blocks.approval_style_id%TYPE,
107 p_date_from IN hxc_time_building_blocks.date_from%TYPE,
108 p_date_to IN hxc_time_building_blocks.date_to%TYPE,
109 p_comment_text IN hxc_time_building_blocks.comment_text%TYPE,
110 p_parent_building_block_ovn IN hxc_time_building_blocks.parent_building_block_ovn%TYPE,
111 p_new IN VARCHAR2,
112 p_changed IN VARCHAR2,
113 p_app_blocks IN OUT NOCOPY hxc_block_table_type
114 )
115 IS
116 l_proc VARCHAR2 (72);
117 l_count_building_block PLS_INTEGER;
118 l_parent_building_block_id hxc_time_building_blocks.time_building_block_id%TYPE;
119 l_parent_ovn hxc_time_building_blocks.parent_building_block_ovn%TYPE;
120 BEGIN
121 g_debug := hr_utility.debug_enabled;
122
123 IF g_debug
124 THEN
125 l_proc := g_package || 'create_bb';
126 hr_utility.set_location ('Entering:' || l_proc, 10);
127 END IF;
128
129 l_count_building_block := NVL (p_app_blocks.LAST, 0);
130 p_app_blocks.EXTEND;
131 p_app_blocks (p_app_blocks.LAST) :=
132 hxc_block_type (p_time_building_block_id,
133 p_type,
134 p_measure,
135 p_unit_of_measure,
136 fnd_date.date_to_canonical (p_start_time),
137 fnd_date.date_to_canonical (p_stop_time),
138 p_parent_building_block_id,
139 p_parent_is_new,
140 p_scope,
141 p_object_version_number,
142 p_approval_status,
143 p_resource_id,
144 p_resource_type,
145 p_approval_style_id,
146 fnd_date.date_to_canonical (p_date_from),
147 fnd_date.date_to_canonical (p_date_to),
148 p_comment_text,
149 p_parent_building_block_ovn,
150 p_new,
151 p_changed,
152 NULL, -- Process
153 NULL,
154 -- Will now get populated in execute_deposit_process
155 NULL
156 -- Unable to set the display here - could expose it? a row num for example?
157 );
158
159 IF g_debug
160 THEN
161 hr_utility.set_location ('Leaving:' || l_proc, 20);
162 END IF;
163 END create_bb;
164
165 -----------------------------------------------------------------------------
166 -- Type: Procedure
167 -- Scope: Public
168 -- Name: create_bb
169 -- IN Parameters: See overloaded Procedure
170 -- INOUT Parameters: p_app_blocks -> TBB Type to which the new TBB will be
171 -- added.
172 --
173 -- Description: Overloading the main procedure. This one will accept the old
174 -- TBB PL/SQL table (passed in), convert it to the new TYPE
175 -- call the create_bb procedure that accepts the new TYPE
176 -- and then convert it back to the old PL/SQL table.
177 -----------------------------------------------------------------------------
178 PROCEDURE create_bb (
179 p_time_building_block_id IN hxc_time_building_blocks.time_building_block_id%TYPE,
180 p_type IN hxc_time_building_blocks.TYPE%TYPE,
181 p_measure IN hxc_time_building_blocks.measure%TYPE,
182 p_unit_of_measure IN hxc_time_building_blocks.unit_of_measure%TYPE,
183 p_start_time IN hxc_time_building_blocks.start_time%TYPE,
184 p_stop_time IN hxc_time_building_blocks.stop_time%TYPE,
185 p_parent_building_block_id IN hxc_time_building_blocks.parent_building_block_id%TYPE,
186 p_parent_is_new IN VARCHAR2,
187 p_scope IN hxc_time_building_blocks.SCOPE%TYPE,
188 p_object_version_number IN hxc_time_building_blocks.object_version_number%TYPE,
189 p_approval_status IN hxc_time_building_blocks.approval_status%TYPE,
190 p_resource_id IN hxc_time_building_blocks.resource_id%TYPE,
191 p_resource_type IN hxc_time_building_blocks.resource_type%TYPE,
192 p_approval_style_id IN hxc_time_building_blocks.approval_style_id%TYPE,
193 p_date_from IN hxc_time_building_blocks.date_from%TYPE,
194 p_date_to IN hxc_time_building_blocks.date_to%TYPE,
195 p_comment_text IN hxc_time_building_blocks.comment_text%TYPE,
196 p_parent_building_block_ovn IN hxc_time_building_blocks.parent_building_block_ovn%TYPE,
197 p_new IN VARCHAR2,
198 p_changed IN VARCHAR2,
199 p_app_blocks IN OUT NOCOPY hxc_self_service_time_deposit.timecard_info
200 )
201 IS
202 l_proc VARCHAR2 (72);
203 l_blocks hxc_block_table_type;
204 BEGIN
205 g_debug := hr_utility.debug_enabled;
206
207 IF g_debug
208 THEN
209 l_proc := g_package || 'create_bb (Overload)';
210 hr_utility.set_location ('Entering:' || l_proc, 10);
211 END IF;
212
213 l_blocks :=
214 hxc_timestore_deposit_util.convert_tbb_to_type
215 (p_blocks => p_app_blocks);
216 create_bb (p_time_building_block_id => p_time_building_block_id,
217 p_type => p_type,
218 p_measure => p_measure,
219 p_unit_of_measure => p_unit_of_measure,
220 p_start_time => p_start_time,
221 p_stop_time => p_stop_time,
222 p_parent_building_block_id => p_parent_building_block_id,
223 p_parent_is_new => p_parent_is_new,
224 p_scope => p_scope,
225 p_object_version_number => p_object_version_number,
226 p_approval_status => p_approval_status,
227 p_resource_id => p_resource_id,
228 p_resource_type => p_resource_type,
229 p_approval_style_id => p_approval_style_id,
230 p_date_from => p_date_from,
231 p_date_to => p_date_to,
232 p_comment_text => p_comment_text,
233 p_parent_building_block_ovn => p_parent_building_block_ovn,
234 p_new => p_new,
235 p_changed => p_changed,
236 p_app_blocks => l_blocks
237 );
238 p_app_blocks :=
239 hxc_timecard_block_utils.convert_to_dpwr_blocks (p_blocks => l_blocks);
240
241 IF g_debug
242 THEN
243 hr_utility.set_location ('Leaving:' || l_proc, 20);
244 END IF;
245 END create_bb;
246
247 -----------------------------------------------------------------------------
248 -- Type: Procedure
249 -- Scope: Public
250 -- Name: create_timecard_bb
251 -- IN Parameters: p_start_time -> The first day of the TIMECARD
252 -- p_stop_time -> The last day of the TIMECARD
253 -- p_approval_status -> 'WORKING' or 'SUBMITTED', you can leave
254 -- this parameter blank and it will work it
255 -- out automatically, based on the mode used
256 -- when depositing the TC
257 -- p_resource_id -> Person Id to which the TBB needs to be attached
258 -- p_resource_type -> 'PERSON'
259 -- p_approval_style_id -> The Id of the approval style used
260 -- to approve the TBB
261 -- p_comment_text -> comment to be saved with TBB
262 -- INOUT Parameters: p_app_blocks -> TBB Type table to which the new TBB will be
263 -- added
264 -- OUT Parameters: p_time building_block_id -> TBB id of the just created TBB
265 --
266 -- Description: This procedure will allow you to add a TBB of type TIMECARD
267 -- the TBB Type table (passed in). The BB will be added
268 -- to the same TC as all the other BB already in the table
269 -- If you want to add BBs to another table, please clear it
270 -- first and start afresh
271 --
272 -----------------------------------------------------------------------------
273 PROCEDURE create_timecard_bb (
274 p_start_time IN hxc_time_building_blocks.start_time%TYPE,
275 p_stop_time IN hxc_time_building_blocks.stop_time%TYPE,
276 -- We will set this automatic, depending on the mode used when depositing the TC
277 -- p_approval_status IN hxc_time_building_blocks.approval_status%TYPE,
278 p_resource_id IN hxc_time_building_blocks.resource_id%TYPE,
279 -- default to person because there is no other resource type at the moment.
280 p_resource_type IN hxc_time_building_blocks.resource_type%TYPE,
281 -- We cannot use approval_style_name because that is not unique.
282 -- if NULL we will get it of the preferences
283 p_approval_style_id IN hxc_time_building_blocks.approval_style_id%TYPE,
284 p_comment_text IN hxc_time_building_blocks.comment_text%TYPE,
285 p_app_blocks IN OUT NOCOPY hxc_block_table_type,
286 p_time_building_block_id OUT NOCOPY hxc_time_building_blocks.time_building_block_id%TYPE
287 )
288 IS
289 l_proc VARCHAR2 (72);
290 l_count_building_block PLS_INTEGER;
291 -- l_tc_days PLS_INTEGER;
292 l_day_bb_id hxc_time_building_blocks.time_building_block_id%TYPE;
293 -- l_building_block_index PLS_INTEGER;
294 BEGIN
295 g_debug := hr_utility.debug_enabled;
296
297 IF g_debug
298 THEN
299 l_proc := g_package || 'create_timecard_bb';
300 hr_utility.set_location ('Entering:' || l_proc, 10);
301 END IF;
302
303 -- We are starting with a new timecard so clear the global tables
304 hxc_self_service_time_deposit.initialize_globals;
305 -- Also clear the local PL/SQL table
306 hxc_timestore_deposit_util.clear_building_block_table
307 (p_app_blocks => p_app_blocks);
308 l_count_building_block := p_app_blocks.LAST;
309
310 -- 'generate' a TBB ID
311 IF (l_count_building_block IS NULL)
312 THEN
313 p_time_building_block_id := -2;
314 -- never start at -1 because that has a special meaning in the deposit
315 ELSE
316 p_time_building_block_id := - (l_count_building_block) - 2;
317 END IF;
318
319 create_bb (p_time_building_block_id => p_time_building_block_id,
320 p_type => hxc_timecard.c_range_type,
321 -- p_measure => DEFAULTS TO NULL,
322 p_unit_of_measure => NULL,
323 p_start_time => p_start_time,
324 p_stop_time => p_stop_time,
325 p_parent_building_block_id => NULL,
326 -- Timecard does not have a parent
327 p_parent_is_new => NULL,
328 p_scope => hxc_timecard.c_timecard_scope,
329 -- p_object_version_number => DEFAULTS TO 1,
330 -- p_approval_status=> p_approval_status
331 p_resource_id => p_resource_id,
332 p_resource_type => p_resource_type,
333 p_approval_style_id => p_approval_style_id,
334 -- p_date_from => DEFAULTS TO SYSDATE,
335 -- p_date_to => DEFAULTS TO hr_general.end_of_time,
336 p_comment_text => p_comment_text,
337 -- p_parent_building_block_ovn => DEFAULTS TO NULL,
338 -- new => DEFAULTS TO 'Y',
339 -- changed => DEFAULTS TO 'N',
340 p_app_blocks => p_app_blocks
341 );
342
343 -- We can automatically insert the DAY TBBs if we want
344 -- but that would make it difficult for the user to add DETAIL TBBs
345 -- because they would not have a handle to the DAY TBBs, therefore this is
346 -- commented out for now until we find a mechanism to return the handles
347 -- in a userfriendly way
348 /*************************************************
349 -- calculate the number of days in the timecard
350 l_tc_days := TRUNC (p_stop_time)
351 - TRUNC (p_start_time);
352
353 FOR i IN 0 .. l_tc_days
354 LOOP
355 create_day_bb (
356 p_day => p_start_time
357 + i,
358 p_parent_building_block_id=> p_app_blocks (1).time_building_block_id,
359 -- p_comment_text => DEFAULTS TO NULL,
360 -- p_parent_building_block_ovn => DEFAULTS TO 1,
361 p_app_blocks=> p_app_blocks,
362 p_time_building_block_id=> l_day_bb_id
363 );
364 END LOOP;
365 *************************************************/
366 IF g_debug
367 THEN
368 hr_utility.set_location ( 'Leaving:'
369 || l_proc
370 || ' p_time_building_block_id = '
371 || p_time_building_block_id,
372 100
373 );
374 END IF;
375 END create_timecard_bb;
376
377 -----------------------------------------------------------------------------
378 -- Type: Procedure
379 -- Scope: Public
380 -- Name: create_timecard_bb
381 -- IN Parameters: See original procedure
382 -- INOUT Parameters: p_app_blocks -> TBB PL/SQL to which the new TBB will be
383 -- added
384 -- OUT Parameters: See original procedure
385 --
386 -- Description: Overloading the main procedure. This one will accept the old
387 -- TBB PL/SQL table (passed in), convert it to the new TYPE
388 -- call the overloaded create_timecard_bb procedure that accepts
389 -- the new TYPE and then convert it back to the old PL/SQL table.
390 --
391 -----------------------------------------------------------------------------
392 PROCEDURE create_timecard_bb (
393 p_start_time IN hxc_time_building_blocks.start_time%TYPE,
394 p_stop_time IN hxc_time_building_blocks.stop_time%TYPE,
395 -- We will set this automatic, depending on the mode used when depositing the TC
396 -- p_approval_status IN hxc_time_building_blocks.approval_status%TYPE,
397 p_resource_id IN hxc_time_building_blocks.resource_id%TYPE,
398 -- default to person because there is no other resource type at the moment.
399 p_resource_type IN hxc_time_building_blocks.resource_type%TYPE,
400 -- We cannot use approval_style_name because that is not unique.
401 -- if NULL we will get it of the preferences
402 p_approval_style_id IN hxc_time_building_blocks.approval_style_id%TYPE,
403 p_comment_text IN hxc_time_building_blocks.comment_text%TYPE,
404 p_app_blocks IN OUT NOCOPY hxc_self_service_time_deposit.timecard_info,
405 p_time_building_block_id OUT NOCOPY hxc_time_building_blocks.time_building_block_id%TYPE
406 )
407 IS
408 l_proc VARCHAR2 (72);
409 l_blocks hxc_block_table_type;
410 BEGIN
411 g_debug := hr_utility.debug_enabled;
412
413 IF g_debug
414 THEN
415 l_proc := g_package || 'create_timecard_bb (Overloaded)';
416 hr_utility.set_location ('Entering:' || l_proc, 10);
417 END IF;
418
419 l_blocks :=
420 hxc_timestore_deposit_util.convert_tbb_to_type
421 (p_blocks => p_app_blocks);
422 -- Call overloaded procedure
423 create_timecard_bb (p_start_time => p_start_time,
424 p_stop_time => p_stop_time,
425 p_resource_id => p_resource_id,
426 p_resource_type => p_resource_type,
427 p_approval_style_id => p_approval_style_id,
428 p_comment_text => p_comment_text,
429 p_app_blocks => l_blocks,
430 p_time_building_block_id => p_time_building_block_id
431 );
432 p_app_blocks :=
433 hxc_timecard_block_utils.convert_to_dpwr_blocks (p_blocks => l_blocks);
434
435 IF g_debug
436 THEN
437 hr_utility.set_location ( 'Leaving:'
438 || l_proc
439 || ' p_time_building_block_id = '
440 || p_time_building_block_id,
441 100
442 );
443 END IF;
444 END create_timecard_bb;
445
446 -----------------------------------------------------------------------------
447 -- Type: Procedure
448 -- Scope: Public
449 -- Name: create_day_bb
450 -- IN Parameters: p_day -> Day for which you want to create the DAY TBB
451 -- p_resource_id -> Person Id to which the TBB needs to be attached
452 -- p_resource_type -> 'PERSON'
453 -- p_comment_text -> comment to be saved with TBB
454 -- p_deposit_process -> Name of the deposit process which we will
455 -- use the mapping of
456 -- INOUT Parameters: p_app_blocks -> TBB PL/SQL to which the new TBB will be
457 -- added
458 -- OUT Parameters: p_time building_block_id -> TBB id of the just created TBB
459 --
460 -- Description: This procedure will allow you to add a TBB of type DAY
461 -- the TBB PL/SQL table (passed in). This instance of the procedure
462 -- does not need a parent (TIMECARD) TBB, the parent will be worked
463 -- out automatically based on the day and resource id.
464 -- The BB will be added to the same TC as all the other BB already
465 -- in the table. If you want to add BBs to another table, please
466 -- clear it first and start afresh.
467 --
468 -----------------------------------------------------------------------------
469 PROCEDURE create_day_bb (
470 p_day IN hxc_time_building_blocks.start_time%TYPE,
471 p_resource_id IN hxc_time_building_blocks.resource_id%TYPE,
472 p_resource_type IN hxc_time_building_blocks.resource_type%TYPE,
473 p_comment_text IN hxc_time_building_blocks.comment_text%TYPE,
474 p_deposit_process IN hxc_deposit_processes.NAME%TYPE,
475 p_app_blocks IN OUT NOCOPY hxc_block_table_type,
476 p_time_building_block_id OUT NOCOPY hxc_time_building_blocks.time_building_block_id%TYPE
477 )
478 IS
479 l_proc VARCHAR2 (72);
480 l_timecard_building_block_id hxc_time_building_blocks.parent_building_block_id%TYPE;
481 l_timecard_ovn hxc_time_building_blocks.parent_building_block_ovn%TYPE;
482 BEGIN
483 g_debug := hr_utility.debug_enabled;
484
485 IF g_debug
486 THEN
487 l_proc := g_package || 'create_day_bb (overload)';
488 hr_utility.set_location ('Entering:' || l_proc, 10);
489 END IF;
490
491 -- First find the TIMECARD TBB to which this DAY TBB belongs
492 hxc_timestore_deposit_util.find_parent_building_block
493 (p_start_time => p_day,
494 p_resource_id => p_resource_id,
495 p_resource_type => p_resource_type,
496 p_scope => hxc_timecard.c_timecard_scope,
497 p_app_blocks => p_app_blocks,
498 p_timecard_bb_id => l_timecard_building_block_id,
499 p_timecard_ovn => l_timecard_ovn
500 );
501 -- Now call the overloaded procedure
502 create_day_bb
503 (p_day => p_day,
504 p_parent_building_block_id => l_timecard_building_block_id,
505 p_comment_text => p_comment_text,
506 p_parent_building_block_ovn => l_timecard_ovn,
507 p_deposit_process => p_deposit_process,
508 p_app_blocks => p_app_blocks,
509 p_time_building_block_id => p_time_building_block_id
510 );
511
512 IF g_debug
513 THEN
514 hr_utility.set_location ( 'Leaving:'
515 || l_proc
516 || ' p_time_building_block_id = '
517 || p_time_building_block_id,
518 100
519 );
520 END IF;
521 END create_day_bb;
522
523 -----------------------------------------------------------------------------
524 -- Type: Procedure
525 -- Scope: Public
526 -- Name: create_day_bb
527 -- IN Parameters: See overloaded Procedure
528 -- INOUT Parameters: p_app_blocks -> TBB Type to which the new TBB will be
529 -- added
530 -- OUT Parameters: See overloaded Procedure
531 --
532 -- Description: Overloading the main procedure. This one will accept the old
533 -- TBB PL/SQL table (passed in), convert it to the new TYPE,
534 -- call the overloaded create_timecard_bb procedure that accepts
535 -- the new TYPE and then convert it back to the old PL/SQL table.
536 --
537 -----------------------------------------------------------------------------
538 PROCEDURE create_day_bb (
539 p_day IN hxc_time_building_blocks.start_time%TYPE,
540 p_resource_id IN hxc_time_building_blocks.resource_id%TYPE,
541 p_resource_type IN hxc_time_building_blocks.resource_type%TYPE,
542 p_comment_text IN hxc_time_building_blocks.comment_text%TYPE,
543 p_deposit_process IN hxc_deposit_processes.NAME%TYPE,
544 p_app_blocks IN OUT NOCOPY hxc_self_service_time_deposit.timecard_info,
545 p_time_building_block_id OUT NOCOPY hxc_time_building_blocks.time_building_block_id%TYPE
546 )
547 IS
548 l_proc VARCHAR2 (72);
549 l_blocks hxc_block_table_type;
550 BEGIN
551 g_debug := hr_utility.debug_enabled;
552
553 IF g_debug
554 THEN
555 l_proc := g_package || 'create_day_bb (overload)';
556 hr_utility.set_location ('Entering:' || l_proc, 10);
557 END IF;
558
559 l_blocks :=
560 hxc_timestore_deposit_util.convert_tbb_to_type
561 (p_blocks => p_app_blocks);
562 -- Call overloaded procedure
563 create_day_bb (p_day => p_day,
564 p_resource_id => p_resource_id,
565 p_resource_type => p_resource_type,
566 p_comment_text => p_comment_text,
567 p_deposit_process => p_deposit_process,
568 p_app_blocks => l_blocks,
569 p_time_building_block_id => p_time_building_block_id
570 );
571 p_app_blocks :=
572 hxc_timecard_block_utils.convert_to_dpwr_blocks (p_blocks => l_blocks);
573
574 IF g_debug
575 THEN
576 hr_utility.set_location ( 'Leaving:'
577 || l_proc
578 || ' p_time_building_block_id = '
579 || p_time_building_block_id,
580 100
581 );
582 END IF;
583 END create_day_bb;
584
585 -----------------------------------------------------------------------------
586 -- Type: Procedure
587 -- Scope: Public
588 -- Name: create_day_bb
589 -- IN Parameters: p_day -> Day for which you want to create the DAY TBB
590 -- p_parent_building_block_id -> Id of the TBB to which this TBB
591 -- needs to be attached, should be
592 -- set to a TIMECARD TBB id as a
593 -- DAY TBB needs to be attached to
594 -- TIMECARD TBB.
595 -- p_comment_text -> comment to be saved with TBB
596 -- p_parent_building_block_ovn -> ovn of the parent TBB, should
597 -- always be the highest ovn
598 -- as we do not allow attaching
599 -- TBBs to old TBBs
600 -- p_deposit_process -> Name of the deposit process which we will
601 -- use the mapping of
602 -- INOUT Parameters: p_app_blocks -> TBB PL/SQL to which the new TBB will be
603 -- added
604 -- OUT Parameters: p_time building_block_id -> TBB id of the just created TBB
605 --
606 -- Description: This procedure will allow you to add a TBB of type TIMECARD
607 -- the TBB PL/SQL table (passed in). This instance of the procedure
608 -- needs a parent (TIMECARD) TBB so you have to know what it is.
609 -- The BB will be added to the same TC as all the other BB already
610 -- in the table. If you want to add BBs to another table, please
611 -- clear it first and start afresh.
612 --
613 -----------------------------------------------------------------------------
614 PROCEDURE create_day_bb (
615 p_day IN hxc_time_building_blocks.start_time%TYPE,
616 p_parent_building_block_id IN hxc_time_building_blocks.parent_building_block_id%TYPE,
617 p_comment_text IN hxc_time_building_blocks.comment_text%TYPE,
618 p_parent_building_block_ovn IN hxc_time_building_blocks.parent_building_block_ovn%TYPE,
619 p_deposit_process IN hxc_deposit_processes.NAME%TYPE,
620 p_app_blocks IN OUT NOCOPY hxc_block_table_type,
621 p_time_building_block_id OUT NOCOPY hxc_time_building_blocks.time_building_block_id%TYPE
622 )
623 IS
624 l_proc VARCHAR2 (72);
625 l_count_building_block PLS_INTEGER;
626 -- l_building_block_index PLS_INTEGER;
627 l_parent_building_block_index PLS_INTEGER;
628 l_parent_is_new VARCHAR2 (1);
629 -- l_timecard_building_block_id hxc_time_building_blocks.parent_building_block_id%TYPE;
630 -- l_timecard_ovn hxc_time_building_blocks.parent_building_block_ovn%TYPE;
631 l_app_attributes hxc_self_service_time_deposit.app_attributes_info;
632 BEGIN
633 g_debug := hr_utility.debug_enabled;
634
635 IF g_debug
636 THEN
637 l_proc := g_package || 'create_day_bb';
638 hr_utility.set_location ('Entering:' || l_proc, 10);
639 END IF;
640
641 -- check if a timecard is already present in PL/SQL tables
642 IF (p_app_blocks.COUNT = 0)
643 THEN -- No Timecard is loaded yet, lets load it form the TimeStore
644 -- First clear the global tables so no garbage gets loaded with the
645 -- updated TC
646 hxc_self_service_time_deposit.initialize_globals;
647 -- then get the TC and attributes out of the DB into the PL/SQL Tables
648 hxc_timestore_deposit_util.get_timecard_tables
649 (p_building_block_id => p_parent_building_block_id,
650 p_deposit_process => p_deposit_process,
651 p_app_blocks => p_app_blocks,
652 p_app_attributes => l_app_attributes
653 -- not needed in this procedure
654 );
655 END IF;
656
657 l_count_building_block := p_app_blocks.LAST;
658
659 -- 'generate' a TBB ID
660 IF (l_count_building_block IS NULL)
661 THEN
662 p_time_building_block_id := -2;
663 -- never start at -1 because that has a special meaning in the deposit
664 ELSE
665 p_time_building_block_id := - (l_count_building_block) - 2;
666 END IF;
667
668 -- lets find the parent of this BB, we will use this to set some attributes = parents attributes
669 l_parent_building_block_index :=
670 hxc_timestore_deposit_util.get_index_in_bb_table
671 (p_bb_table => p_app_blocks,
672 p_bb_id_to_find => p_parent_building_block_id
673 );
674
675 -- set the parent_is_new flag for the new BB
676 -- if the parent BB id is negative (less than -1), it was just created and is therefore
677 -- not present in the db yet, so parent is new
678 IF (p_parent_building_block_id < -1)
679 THEN
680 l_parent_is_new := hxc_timecard.c_yes;
681 ELSIF (p_parent_building_block_id = -1)
682 THEN -- We did not find the parent present in the table, ERROR
683 NULL;
684 ELSE -- The parent BB was retrieved from the DB, so it is not new
685 l_parent_is_new := hxc_timecard.c_no;
686 END IF;
687
688 create_bb
689 (p_time_building_block_id => p_time_building_block_id,
690 p_type => hxc_timecard.c_range_type,
691 -- p_measure => DEFAULTS TO NULL,
692 p_unit_of_measure => NULL,
693 p_start_time => TO_DATE
694 ( TO_CHAR (p_day,
695 'DD-MON-YYYY'
696 )
697 || ' 00:00:00',
698 'DD-MON-YYYY HH24:MI:SS'
699 ),
700 p_stop_time => TO_DATE
701 ( TO_CHAR (p_day,
702 'DD-MON-YYYY'
703 )
704 || ' 23:59:59',
705 'DD-MON-YYYY HH24:MI:SS'
706 ),
707 p_parent_building_block_id => p_parent_building_block_id,
708 p_parent_is_new => l_parent_is_new,
709 p_scope => hxc_timecard.c_day_scope,
710 -- p_object_version_number => DEFAULTS TO 1,
711 -- set the next 4 fields to the parents equivalant, we do not want the users to set these manually for now.
712 -- This can be changed later. This is not a functional requirement, it just makes the API interface easier
713 p_approval_status => p_app_blocks
714 (l_parent_building_block_index
715 ).approval_status,
716 p_resource_id => p_app_blocks
717 (l_parent_building_block_index
718 ).resource_id,
719 p_resource_type => p_app_blocks
720 (l_parent_building_block_index
721 ).resource_type,
722 p_approval_style_id => p_app_blocks
723 (l_parent_building_block_index
724 ).approval_style_id,
725 -- p_date_from => DEFAULTS TO SYSDATE,
726 -- p_date_to => DEFAULTS TO hr_general.end_of_time,
727 p_comment_text => p_comment_text,
728 p_parent_building_block_ovn => p_parent_building_block_ovn,
729 -- new => DEFAULTS TO 'Y',
730 -- changed => DEFAULTS TO 'N',
731 p_app_blocks => p_app_blocks
732 );
733
734 IF g_debug
735 THEN
736 hr_utility.set_location ( 'Leaving:'
737 || l_proc
738 || ' p_time_building_block_id = '
739 || p_time_building_block_id,
740 100
741 );
742 END IF;
743 END create_day_bb;
744
745 -----------------------------------------------------------------------------
746 -- Type: Procedure
747 -- Scope: Public
748 -- Name: create_day_bb
749 -- IN Parameters: See overloaded Procedure
750 -- INOUT Parameters: p_app_blocks -> TBB Type to which the new TBB will be
751 -- added
752 -- OUT Parameters: See overloaded Procedure
753 --
754 -- Description: Overloading the main procedure. This one will accept the old
755 -- TBB PL/SQL table (passed in), convert it to the new TYPE,
756 -- call the overloaded create_timecard_bb procedure that accepts
757 -- the new TYPE and then convert it back to the old PL/SQL table.
758 --
759 -----------------------------------------------------------------------------
760 PROCEDURE create_day_bb (
761 p_day IN hxc_time_building_blocks.start_time%TYPE,
762 p_parent_building_block_id IN hxc_time_building_blocks.parent_building_block_id%TYPE,
763 p_comment_text IN hxc_time_building_blocks.comment_text%TYPE,
764 p_parent_building_block_ovn IN hxc_time_building_blocks.parent_building_block_ovn%TYPE,
765 p_deposit_process IN hxc_deposit_processes.NAME%TYPE,
766 p_app_blocks IN OUT NOCOPY hxc_self_service_time_deposit.timecard_info,
767 p_time_building_block_id OUT NOCOPY hxc_time_building_blocks.time_building_block_id%TYPE
768 )
769 IS
770 l_proc VARCHAR2 (72);
771 l_blocks hxc_block_table_type;
772 BEGIN
773 g_debug := hr_utility.debug_enabled;
774
775 IF g_debug
776 THEN
777 l_proc := g_package || 'create_day_bb (overload)';
778 hr_utility.set_location ('Entering:' || l_proc, 10);
779 END IF;
780
781 l_blocks :=
782 hxc_timestore_deposit_util.convert_tbb_to_type
783 (p_blocks => p_app_blocks);
784 -- Call overloaded procedure
785 create_day_bb
786 (p_day => p_day,
787 p_parent_building_block_id => p_parent_building_block_id,
788 p_comment_text => p_comment_text,
789 p_parent_building_block_ovn => p_parent_building_block_ovn,
790 p_deposit_process => p_deposit_process,
791 p_app_blocks => l_blocks,
792 p_time_building_block_id => p_time_building_block_id
793 );
794 p_app_blocks :=
795 hxc_timecard_block_utils.convert_to_dpwr_blocks (p_blocks => l_blocks);
796
797 IF g_debug
798 THEN
799 hr_utility.set_location ( 'Leaving:'
800 || l_proc
801 || ' p_time_building_block_id = '
802 || p_time_building_block_id,
803 100
804 );
805 END IF;
806 END create_day_bb;
807
808 -----------------------------------------------------------------------------
809 -- Type: Procedure
810 -- Scope: Private
811 -- Name: auto_create_timecard
812 -- IN Parameters: p_resource_id -> Person for which the timecard needs to be created
813 -- p_resource_type -> 'PERSON'
814 -- p_day -> Day for which you want to create the timecard
815 -- p_deposit_process -> Name of the deposit process which we will
816 -- use the mapping off.
817 -- INOUT Parameters: p_app_blocks -> TBB Type to which the new TBB will be
818 -- added (and contain complete timecard)
819 -- p_app_attributes -> Attribute PL/SQL to which the new attribure
820 -- will be added
821 -- OUT Parameters: p_time building_block_id -> TBB id of the DAY TBB (identified
822 -- by p_day) just created.
823 --
824 -- Description: This procedure can be used to create a timecard completely
825 -- automatically. Based on the day and resource information passed
826 -- in, it works out the preferences for this resource and creates
827 -- the timecard. The period for the timecard is read from the
828 -- preferences. Then a DAY TBB is created for every day in the
829 -- timecard period. The TBB id passed back, is the Id of the DAY
830 -- TBB, with a start date = the day passed in.
831 --
832 -----------------------------------------------------------------------------
833 PROCEDURE auto_create_timecard (
834 p_resource_id IN hxc_time_building_blocks.resource_id%TYPE,
835 p_resource_type IN hxc_time_building_blocks.resource_type%TYPE,
836 p_day IN hxc_time_building_blocks.start_time%TYPE,
837 p_deposit_process IN hxc_deposit_processes.NAME%TYPE,
838 p_app_blocks IN OUT NOCOPY hxc_block_table_type,
839 p_app_attributes IN OUT NOCOPY hxc_self_service_time_deposit.app_attributes_info,
840 p_time_building_block_id OUT NOCOPY hxc_time_building_blocks.time_building_block_id%TYPE
841 )
842 IS
843 l_proc VARCHAR2 (72);
844 l_timecard_building_block_id hxc_time_building_blocks.time_building_block_id%TYPE;
845 l_day_bb_id hxc_time_building_blocks.time_building_block_id%TYPE;
846 l_period_start DATE;
847 l_period_end DATE;
848 l_tc_days PLS_INTEGER;
849 l_start_time hxc_time_building_blocks.start_time%TYPE;
850 l_stop_time hxc_time_building_blocks.stop_time%TYPE;
851 BEGIN
852 IF g_debug
853 THEN
854 l_proc := g_package || 'auto_create_timecard';
855 hr_utility.set_location ('Entering:' || l_proc, 10);
856 END IF;
857
858 -- clear tables
859 hxc_timestore_deposit_util.clear_building_block_table
860 (p_app_blocks => p_app_blocks);
861 hxc_timestore_deposit_util.clear_attribute_table
862 (p_app_attributes => p_app_attributes);
863 hxc_timestore_deposit_util.find_current_period
864 (p_resource_id => p_resource_id,
865 p_resource_type => 'PERSON',
866 p_day => p_day,
867 p_start_time => l_start_time,
868 p_stop_time => l_stop_time
869 );
870 create_timecard_bb
871 (p_start_time => l_start_time,
872 p_stop_time => l_stop_time,
873 p_resource_id => p_resource_id,
874 p_resource_type => p_resource_type,
875 p_app_blocks => p_app_blocks,
876 p_time_building_block_id => l_timecard_building_block_id
877 );
878 -- And its associated DAY TBBs, one for every day of the TIMECARD period
879 -- calculate the number of days in the timecard
880 l_tc_days := TRUNC (l_stop_time) - TRUNC (l_start_time);
881
882 IF g_debug
883 THEN
884 hr_utility.set_location ( ' We will need to create '
885 || TO_CHAR (l_tc_days + 1)
886 || ' DAY TBBs',
887 30
888 );
889 END IF;
890
891 FOR i IN 0 .. l_tc_days
892 LOOP
893 create_day_bb
894 (p_day => l_start_time + i,
895 p_parent_building_block_id => l_timecard_building_block_id,
896 p_deposit_process => p_deposit_process,
897 p_app_blocks => p_app_blocks,
898 p_time_building_block_id => l_day_bb_id
899 );
900
901 -- Return the TBB_ID of the TBB to which we need to attache the DETAIL TBB
902 -- That initiated this procedure
903 IF ((TRUNC (l_start_time) + i) = TRUNC (p_day))
904 THEN
905 p_time_building_block_id := l_day_bb_id;
906 END IF;
907 END LOOP;
908
909 IF g_debug
910 THEN
911 hr_utility.set_location ( 'Leaving:'
912 || l_proc
913 || ' p_time_building_block_id = '
914 || p_time_building_block_id,
915 100
916 );
917 END IF;
918 END auto_create_timecard;
919
920 -----------------------------------------------------------------------------
921 -- Type: Procedure
922 -- Scope: Public
923 -- Name: create_detail_bb
924 -- IN Parameters: p_type -> 'MEASURE' or 'RANGE'
925 -- p_measure -> The actual time recorded, e.g. 8 Only provide
926 -- when type is MEASURE
927 -- p_start_time -> The IN time Only provide when type is RANGE
928 -- p_stop_time -> The OUT time Only provide when type is RANGE
929 -- p_parent_building_block_id -> Id of the TBB to which this TBB
930 -- needs to be attached, should be
931 -- set to a DAY TBB id as a DETAIL
932 -- TBB needs to be attached to
933 -- DAY TBB.
934 -- p_comment_text -> comment to be saved with TBB
935 -- p_parent_building_block_ovn -> ovn of the parent TBB, should
936 -- always be the highest ovn
937 -- as we do not allow attaching
938 -- TBBs to old TBBs
939 -- p_deposit_process -> Name of the deposit process which we will
940 -- use the mapping of
941 -- INOUT Parameters: p_app_blocks -> TBB PL/SQL to which the new TBB will be
942 -- added
943 -- p_app_attributes -> Attribute PL/SQL to which the new attribure
944 -- will be added
945 -- OUT Parameters: p_time building_block_id -> TBB id of the just created TBB
946 --
947 -- Description: This procedure will allow you to add a TBB of type DAY
948 -- the TBB PL/SQL table (passed in). This instance of the procedure
949 -- needs a parent (DAY) TBB so you have to know what it is.
950 -- The BB will be added to the same TC as all the other BB already
951 -- in the table. If you want to add BBs to another table, please
952 -- clear it first and start afresh.
953 --
954 -----------------------------------------------------------------------------
955 PROCEDURE create_detail_bb (
956 p_type IN hxc_time_building_blocks.TYPE%TYPE,
957 p_measure IN hxc_time_building_blocks.measure%TYPE,
958 p_start_time IN hxc_time_building_blocks.start_time%TYPE,
959 p_stop_time IN hxc_time_building_blocks.stop_time%TYPE,
960 p_parent_building_block_id IN hxc_time_building_blocks.parent_building_block_id%TYPE,
961 -- For now, these need to be the same as the parent BB (the TIMECARD BB). We set this in the code,
962 -- the user cannot manipulate this.
963 -- p_approval_status IN hxc_time_building_blocks.approval_status%TYPE,
964 -- p_resource_id IN hxc_time_building_blocks.resource_id%TYPE,
965 -- p_resource_type IN hxc_time_building_blocks.resource_type%TYPE,
966 -- p_approval_style_id IN hxc_time_building_blocks.approval_style_id%TYPE,
967 p_comment_text IN hxc_time_building_blocks.comment_text%TYPE,
968 p_parent_building_block_ovn IN hxc_time_building_blocks.parent_building_block_ovn%TYPE,
969 p_deposit_process IN hxc_deposit_processes.NAME%TYPE,
970 p_unit_of_measure IN hxc_time_building_blocks.unit_of_measure%TYPE,
971 p_app_blocks IN OUT NOCOPY hxc_block_table_type,
972 p_app_attributes IN OUT NOCOPY hxc_self_service_time_deposit.app_attributes_info,
973 p_time_building_block_id OUT NOCOPY hxc_time_building_blocks.time_building_block_id%TYPE
974 )
975 IS
976 l_proc VARCHAR2 (72);
977 l_count_building_block PLS_INTEGER;
978 l_parent_building_block_index PLS_INTEGER;
979 l_parent_is_new VARCHAR2 (1);
980 l_unit_of_measure hxc_time_building_blocks.unit_of_measure%TYPE;
981 BEGIN
982 g_debug := hr_utility.debug_enabled;
983
984 IF g_debug
985 THEN
986 l_proc := g_package || 'create_detail_bb';
987 hr_utility.set_location ('Entering:' || l_proc, 10);
988 END IF;
989
990 -- check if a timecard is already present in PL/SQL tables
991 IF (p_app_blocks.COUNT = 0)
992 THEN -- No Timecard is loaded yet, lets load it form the TimeStore
993 -- First clear the global tables so no garbage gets loaded with the
994 -- updated TC
995 hxc_self_service_time_deposit.initialize_globals;
996 -- then get the TC and attributes out of the DB into the PL/SQL Tables
997 hxc_timestore_deposit_util.get_timecard_tables
998 (p_building_block_id => p_parent_building_block_id,
999 p_deposit_process => p_deposit_process,
1000 p_app_blocks => p_app_blocks,
1001 p_app_attributes => p_app_attributes
1002 );
1003 END IF;
1004
1005 l_count_building_block := p_app_blocks.LAST;
1006
1007 -- 'generate' a TBB ID
1008 IF (l_count_building_block IS NULL)
1009 THEN
1010 p_time_building_block_id := -2;
1011 -- never start at -1 because that has a special meaning in the deposit
1012 ELSE
1013 p_time_building_block_id := - (l_count_building_block) - 2;
1014 END IF;
1015
1016 -- set the parent_is_new flag for the new BB
1017 -- if the parent BB id is negative, it was just created and is therefore not present in the db yet
1018 -- so parent is new
1019 IF (p_parent_building_block_id < 0)
1020 THEN
1021 l_parent_is_new := hxc_timecard.c_yes;
1022 ELSE -- The parent BB was retrieved from the DB, so it is not new
1023 l_parent_is_new := hxc_timecard.c_no;
1024 END IF;
1025
1026 -- If the user does not provide a UOM, we assume he wants to use HOURS
1027 -- This is a pretty save assumption as it is the only UOM we support at
1028 -- the moment. Should we start supporting more UOMs in the future, we need
1029 -- to rewrite this.
1030 IF (p_unit_of_measure IS NULL)
1031 THEN
1032 IF (p_measure IS NULL)
1033 THEN
1034 l_unit_of_measure := NULL;
1035 ELSE
1036 l_unit_of_measure := c_hours_uom;
1037 END IF;
1038 ELSE -- user knows best ...
1039 l_unit_of_measure := p_unit_of_measure;
1040 END IF;
1041
1042 -- lets find the parent of this BB, we will use this to set some attributes = parents attributes
1043 l_parent_building_block_index :=
1044 hxc_timestore_deposit_util.get_index_in_bb_table
1045 (p_bb_table => p_app_blocks,
1046 p_bb_id_to_find => p_parent_building_block_id
1047 );
1048 create_bb
1049 (p_time_building_block_id => p_time_building_block_id,
1050 p_type => p_type,
1051 p_measure => p_measure,
1052 p_unit_of_measure => l_unit_of_measure,
1053 p_start_time => p_start_time,
1054 p_stop_time => p_stop_time,
1055 p_parent_building_block_id => p_parent_building_block_id,
1056 p_parent_is_new => l_parent_is_new,
1057 p_scope => hxc_timecard.c_detail_scope,
1058 -- p_object_version_number => DEFAULTS TO 1,
1059 -- set the next 4 fields to the parents equivalant, we do not want the users to set these manually for now.
1060 -- This can be changed later. This is not a functional requirement, it just makes the API interface easier
1061 p_approval_status => p_app_blocks
1062 (l_parent_building_block_index
1063 ).approval_status,
1064 p_resource_id => p_app_blocks
1065 (l_parent_building_block_index
1066 ).resource_id,
1067 p_resource_type => p_app_blocks
1068 (l_parent_building_block_index
1069 ).resource_type,
1070 p_approval_style_id => p_app_blocks
1071 (l_parent_building_block_index
1072 ).approval_style_id,
1073 -- p_date_from => DEFAULTS TO SYSDATE,
1074 -- p_date_to => DEFAULTS TO hr_general.end_of_time,
1075 p_comment_text => p_comment_text,
1076 p_parent_building_block_ovn => p_parent_building_block_ovn,
1077 -- new => DEFAULTS TO 'Y',
1078 -- changed => DEFAULTS TO 'N',
1079 p_app_blocks => p_app_blocks
1080 );
1081
1082 IF g_debug
1083 THEN
1084 hr_utility.set_location ( 'Leaving:'
1085 || l_proc
1086 || ' p_time_building_block_id = '
1087 || p_time_building_block_id,
1088 100
1089 );
1090 END IF;
1091 END create_detail_bb;
1092
1093 -----------------------------------------------------------------------------
1094 -- Type: Procedure
1095 -- Scope: Public
1096 -- Name: create_detail_bb
1097 -- IN Parameters: See Overloaded procedure
1098 -- INOUT Parameters: p_app_blocks -> TBB Type to which the new TBB will be
1099 -- added
1100 -- p_app_attributes -> Attribute PL/SQL to which the new attribure
1101 -- will be added
1102 -- OUT Parameters: See Overloaded procedure
1103 --
1104 -- Description: Overloading the main procedure. This one will accept the old
1105 -- TBB PL/SQL table (passed in), convert it to the new TYPE,
1106 -- call the overloaded create_timecard_bb procedure that accepts
1107 -- the new TYPE and then convert it back to the old PL/SQL table.
1108 --
1109 -----------------------------------------------------------------------------
1110 PROCEDURE create_detail_bb (
1111 p_type IN hxc_time_building_blocks.TYPE%TYPE,
1112 p_measure IN hxc_time_building_blocks.measure%TYPE,
1113 p_start_time IN hxc_time_building_blocks.start_time%TYPE,
1114 p_stop_time IN hxc_time_building_blocks.stop_time%TYPE,
1115 p_parent_building_block_id IN hxc_time_building_blocks.parent_building_block_id%TYPE,
1116 -- For now, these need to be the same as the parent BB (the TIMECARD BB). We set this in the code,
1117 -- the user cannot manipulate this.
1118 -- p_approval_status IN hxc_time_building_blocks.approval_status%TYPE,
1119 -- p_resource_id IN hxc_time_building_blocks.resource_id%TYPE,
1120 -- p_resource_type IN hxc_time_building_blocks.resource_type%TYPE,
1121 -- p_approval_style_id IN hxc_time_building_blocks.approval_style_id%TYPE,
1122 p_comment_text IN hxc_time_building_blocks.comment_text%TYPE,
1123 p_parent_building_block_ovn IN hxc_time_building_blocks.parent_building_block_ovn%TYPE,
1124 p_deposit_process IN hxc_deposit_processes.NAME%TYPE,
1125 p_unit_of_measure IN hxc_time_building_blocks.unit_of_measure%TYPE,
1126 p_app_blocks IN OUT NOCOPY hxc_self_service_time_deposit.timecard_info,
1127 p_app_attributes IN OUT NOCOPY hxc_self_service_time_deposit.app_attributes_info,
1128 p_time_building_block_id OUT NOCOPY hxc_time_building_blocks.time_building_block_id%TYPE
1129 )
1130 IS
1131 l_proc VARCHAR2 (72);
1132 l_blocks hxc_block_table_type;
1133 BEGIN
1134 g_debug := hr_utility.debug_enabled;
1135
1136 IF g_debug
1137 THEN
1138 l_proc := g_package || 'create_detail_bb (Overloaded)';
1139 hr_utility.set_location ('Entering:' || l_proc, 10);
1140 END IF;
1141
1142 l_blocks :=
1143 hxc_timestore_deposit_util.convert_tbb_to_type
1144 (p_blocks => p_app_blocks);
1145 create_detail_bb
1146 (p_type => p_type,
1147 p_measure => p_measure,
1148 p_unit_of_measure => p_unit_of_measure,
1149 p_start_time => p_start_time,
1150 p_stop_time => p_stop_time,
1151 p_parent_building_block_id => p_parent_building_block_id,
1152 -- For now, these need to be the same as the parent BB (the TIMECARD BB). We set this in the code,
1153 -- the user cannot manipulate this.
1154 -- p_approval_status => p_approval_status,
1155 -- p_resource_id => p_resource_id,
1156 -- p_resource_type => p_resource_type,
1157 -- p_approval_style_id => p_approval_style_id,
1158 p_comment_text => p_comment_text,
1159 p_parent_building_block_ovn => p_parent_building_block_ovn,
1160 p_deposit_process => p_deposit_process,
1161 p_app_blocks => l_blocks,
1162 p_app_attributes => p_app_attributes,
1163 p_time_building_block_id => p_time_building_block_id
1164 );
1165 p_app_blocks :=
1166 hxc_timecard_block_utils.convert_to_dpwr_blocks (p_blocks => l_blocks);
1167
1168 IF g_debug
1169 THEN
1170 hr_utility.set_location ( 'Leaving:'
1171 || l_proc
1172 || ' p_time_building_block_id = '
1173 || p_time_building_block_id,
1174 100
1175 );
1176 END IF;
1177 END create_detail_bb;
1178
1179 -----------------------------------------------------------------------------
1180 -- Type: Procedure
1181 -- Scope: Public
1182 -- Name: create_time_entry
1183 -- IN Parameters: p_measure -> The actual time recorded, e.g. 8
1184 -- p_day -> Day to which you want to link the DETAIL TBB
1185 -- p_resource_id -> Person for which the timecard needs to be created
1186 -- p_resource_type -> 'PERSON'
1187 -- p_comment_text -> comment to be saved with TBB
1188 -- p_parent_building_block_ovn -> ovn of the parent TBB, should
1189 -- always be the highest ovn
1190 -- as we do not allow attaching
1191 -- TBBs to old TBBs
1192 -- p_deposit_process -> Name of the deposit process which we will
1193 -- use the mapping of
1194 -- INOUT Parameters: p_app_blocks -> TBB PL/SQL to which the new TBB will be
1195 -- added
1196 -- p_app_attributes -> Attribute PL/SQL to which the new attribure
1197 -- will be added
1198 -- OUT Parameters: p_time building_block_id -> TBB id of the just created TBB
1199 --
1200 -- Description: This procedure will allow you to add a TBB of type DAY
1201 -- the TBB PL/SQL table (passed in). This instance of the procedure
1202 -- can be used for creating DAY TBBs for which you have a measure.
1203 -- The BB will be added to the same TC as all the other BB already
1204 -- in the table. If you want to add BBs to another table, please
1205 -- clear it first and start afresh.
1206 --
1207 -----------------------------------------------------------------------------
1208 PROCEDURE create_time_entry (
1209 p_measure IN hxc_time_building_blocks.measure%TYPE,
1210 p_day IN hxc_time_building_blocks.start_time%TYPE,
1211 p_resource_id IN hxc_time_building_blocks.resource_id%TYPE,
1212 p_resource_type IN hxc_time_building_blocks.resource_type%TYPE,
1213 p_comment_text IN hxc_time_building_blocks.comment_text%TYPE,
1214 p_deposit_process IN hxc_deposit_processes.NAME%TYPE,
1215 p_app_blocks IN OUT NOCOPY hxc_block_table_type,
1216 p_app_attributes IN OUT NOCOPY hxc_self_service_time_deposit.app_attributes_info,
1217 p_time_building_block_id OUT NOCOPY hxc_time_building_blocks.time_building_block_id%TYPE
1218 )
1219 IS
1220 l_proc VARCHAR2 (72);
1221 l_time_building_block_id hxc_time_building_blocks.parent_building_block_id%TYPE;
1222 l_timecard_ovn hxc_time_building_blocks.parent_building_block_ovn%TYPE;
1223 BEGIN
1224 g_debug := hr_utility.debug_enabled;
1225
1226 IF g_debug
1227 THEN
1228 l_proc := g_package || 'create_time_entry';
1229 hr_utility.set_location ('Entering:' || l_proc, 10);
1230 END IF;
1231
1232 -- First find the DAY TBB to which this DETAIL TBB belongs
1233 hxc_timestore_deposit_util.find_parent_building_block
1234 (p_start_time => p_day,
1235 p_resource_id => p_resource_id,
1236 p_resource_type => p_resource_type,
1237 p_scope => hxc_timecard.c_day_scope,
1238 p_app_blocks => p_app_blocks,
1239 p_timecard_bb_id => l_time_building_block_id,
1240 p_timecard_ovn => l_timecard_ovn
1241 );
1242
1243 IF l_time_building_block_id IS NULL
1244 THEN -- We did not find the timecard so let's create one
1245 auto_create_timecard
1246 (p_resource_id => p_resource_id,
1247 p_resource_type => p_resource_type,
1248 p_day => p_day,
1249 p_deposit_process => p_deposit_process,
1250 p_time_building_block_id => l_time_building_block_id,
1251 p_app_blocks => p_app_blocks,
1252 p_app_attributes => p_app_attributes
1253 );
1254 l_timecard_ovn := 1;
1255 END IF;
1256
1257 -- Now call the actual create procedure
1258 create_detail_bb
1259 (p_type => hxc_timecard.c_measure_type,
1260 p_measure => p_measure,
1261 p_unit_of_measure => c_hours_uom,
1262 p_start_time => NULL,
1263 p_stop_time => NULL,
1264 p_parent_building_block_id => l_time_building_block_id,
1265 p_comment_text => p_comment_text,
1266 p_deposit_process => p_deposit_process,
1267 p_parent_building_block_ovn => l_timecard_ovn,
1268 p_app_blocks => p_app_blocks,
1269 p_app_attributes => p_app_attributes,
1270 p_time_building_block_id => p_time_building_block_id
1271 );
1272
1273 IF g_debug
1274 THEN
1275 hr_utility.set_location ( 'Leaving:'
1276 || l_proc
1277 || ' p_time_building_block_id = '
1278 || p_time_building_block_id,
1279 100
1280 );
1281 END IF;
1282 END create_time_entry;
1283
1284 -----------------------------------------------------------------------------
1285 -- Type: Procedure
1286 -- Scope: Public
1287 -- Name: create_time_entry
1288 -- IN Parameters: See Overloaded procedure
1289 -- INOUT Parameters: p_app_blocks -> TBB Type to which the new TBB will be
1290 -- added
1291 -- p_app_attributes -> Attribute PL/SQL to which the new attribure
1292 -- will be added
1293 -- OUT Parameters: p_time building_block_id -> TBB id of the just created TBB
1294 --
1295 -- Description: Overloading the main procedure. This one will accept the old
1296 -- TBB PL/SQL table (passed in), convert it to the new TYPE,
1297 -- call the overloaded create_timecard_bb procedure that accepts
1298 -- the new TYPE and then convert it back to the old PL/SQL table.
1299 --
1300 -----------------------------------------------------------------------------
1301 PROCEDURE create_time_entry (
1302 p_measure IN hxc_time_building_blocks.measure%TYPE,
1303 p_day IN hxc_time_building_blocks.start_time%TYPE,
1304 p_resource_id IN hxc_time_building_blocks.resource_id%TYPE,
1305 p_resource_type IN hxc_time_building_blocks.resource_type%TYPE,
1306 p_comment_text IN hxc_time_building_blocks.comment_text%TYPE,
1307 p_deposit_process IN hxc_deposit_processes.NAME%TYPE,
1308 p_app_blocks IN OUT NOCOPY hxc_self_service_time_deposit.timecard_info,
1309 p_app_attributes IN OUT NOCOPY hxc_self_service_time_deposit.app_attributes_info,
1310 p_time_building_block_id OUT NOCOPY hxc_time_building_blocks.time_building_block_id%TYPE
1311 )
1312 IS
1313 l_proc VARCHAR2 (72);
1314 l_blocks hxc_block_table_type;
1315 BEGIN
1316 g_debug := hr_utility.debug_enabled;
1317
1318 IF g_debug
1319 THEN
1320 l_proc := g_package || 'create_time_entry (Overloaded)';
1321 hr_utility.set_location ('Entering:' || l_proc, 10);
1322 END IF;
1323
1324 l_blocks :=
1325 hxc_timestore_deposit_util.convert_tbb_to_type
1326 (p_blocks => p_app_blocks);
1327 create_time_entry (p_measure => p_measure,
1328 p_day => p_day,
1329 p_resource_id => p_resource_id,
1330 p_resource_type => p_resource_type,
1331 p_comment_text => p_comment_text,
1332 p_deposit_process => p_deposit_process,
1333 p_app_blocks => l_blocks,
1334 p_app_attributes => p_app_attributes,
1335 p_time_building_block_id => p_time_building_block_id
1336 );
1337 p_app_blocks :=
1338 hxc_timecard_block_utils.convert_to_dpwr_blocks (p_blocks => l_blocks);
1339
1340 IF g_debug
1341 THEN
1342 hr_utility.set_location ( 'Leaving:'
1343 || l_proc
1344 || ' p_time_building_block_id = '
1345 || p_time_building_block_id,
1346 100
1347 );
1348 END IF;
1349 END create_time_entry;
1350
1351 -----------------------------------------------------------------------------
1352 -- Type: Procedure
1353 -- Scope: Public
1354 -- Name: create_time_entry (Overload)
1355 -- IN Parameters: p_start_time -> The IN time
1356 -- p_stop_time -> The OUT time
1357 -- p_resource_id -> Person for which the timecard needs to be created
1358 -- p_resource_type -> 'PERSON'
1359 -- p_comment_text -> comment to be saved with TBB
1360 -- p_parent_building_block_ovn -> ovn of the parent TBB, should
1361 -- always be the highest ovn
1362 -- as we do not allow attaching
1363 -- TBBs to old TBBs
1364 -- p_deposit_process -> Name of the deposit process which we will
1365 -- use the mapping of
1366 -- INOUT Parameters: p_app_blocks -> TBB PL/SQL to which the new TBB will be
1367 -- added
1368 -- p_app_attributes -> Attribute PL/SQL to which the new attribure
1369 -- will be added
1370 -- OUT Parameters: p_time building_block_id -> TBB id of the just created TBB
1371 --
1372 -- Description: This procedure will allow you to add a TBB of type DAY
1373 -- the TBB PL/SQL table (passed in). This instance of the procedure
1374 -- can be used for creating DAY TBBs for which you have a range,
1375 -- so you provide a start (IN) and stop (OUT) time.
1376 -- The BB will be added to the same TC as all the other BB already
1377 -- in the table. If you want to add BBs to another table, please
1378 -- clear it first and start afresh.
1379 --
1380 -----------------------------------------------------------------------------
1381 PROCEDURE create_time_entry (
1382 p_start_time IN hxc_time_building_blocks.start_time%TYPE,
1383 p_stop_time IN hxc_time_building_blocks.start_time%TYPE,
1384 p_resource_id IN hxc_time_building_blocks.resource_id%TYPE,
1385 p_resource_type IN hxc_time_building_blocks.resource_type%TYPE,
1386 p_comment_text IN hxc_time_building_blocks.comment_text%TYPE,
1387 p_deposit_process IN hxc_deposit_processes.NAME%TYPE,
1388 p_app_blocks IN OUT NOCOPY hxc_block_table_type,
1389 p_app_attributes IN OUT NOCOPY hxc_self_service_time_deposit.app_attributes_info,
1390 p_time_building_block_id OUT NOCOPY hxc_time_building_blocks.time_building_block_id%TYPE
1391 )
1392 IS
1393 l_proc VARCHAR2 (72);
1394 l_time_building_block_id hxc_time_building_blocks.parent_building_block_id%TYPE;
1395 l_timecard_ovn hxc_time_building_blocks.parent_building_block_ovn%TYPE;
1396 BEGIN
1397 g_debug := hr_utility.debug_enabled;
1398
1399 IF g_debug
1400 THEN
1401 l_proc := g_package || 'create_time_entry (overloaded range)';
1402 hr_utility.set_location ('Entering:' || l_proc, 10);
1403 END IF;
1404
1405 -- First find the DAY TBB to which this DETAIL TBB belongs
1406 hxc_timestore_deposit_util.find_parent_building_block
1407 (p_start_time => p_start_time,
1408 p_resource_id => p_resource_id,
1409 p_resource_type => p_resource_type,
1410 p_scope => hxc_timecard.c_day_scope,
1411 p_app_blocks => p_app_blocks,
1412 p_timecard_bb_id => l_time_building_block_id,
1413 p_timecard_ovn => l_timecard_ovn
1414 );
1415
1416 IF l_time_building_block_id IS NULL
1417 THEN -- We did not find the timecard so let's create one
1418 auto_create_timecard
1419 (p_resource_id => p_resource_id,
1420 p_resource_type => p_resource_type,
1421 p_day => p_start_time,
1422 p_deposit_process => p_deposit_process,
1423 p_time_building_block_id => l_time_building_block_id,
1424 p_app_blocks => p_app_blocks,
1425 p_app_attributes => p_app_attributes
1426 );
1427 l_timecard_ovn := 1;
1428 END IF;
1429
1430 -- Now call the actual create procedure
1431 create_detail_bb
1432 (p_type => hxc_timecard.c_range_type,
1433 p_measure => NULL,
1434 p_unit_of_measure => NULL,
1435 p_start_time => p_start_time,
1436 p_stop_time => p_stop_time,
1437 p_parent_building_block_id => l_time_building_block_id,
1438 p_comment_text => p_comment_text,
1439 p_deposit_process => p_deposit_process,
1440 p_parent_building_block_ovn => l_timecard_ovn,
1441 p_app_blocks => p_app_blocks,
1442 p_app_attributes => p_app_attributes,
1443 p_time_building_block_id => p_time_building_block_id
1444 );
1445
1446 IF g_debug
1447 THEN
1448 hr_utility.set_location ( 'Leaving:'
1449 || l_proc
1450 || ' p_time_building_block_id = '
1451 || p_time_building_block_id,
1452 100
1453 );
1454 END IF;
1455 END create_time_entry;
1456
1457 -----------------------------------------------------------------------------
1458 -- Type: Procedure
1459 -- Scope: Public
1460 -- Name: create_time_entry
1461 -- IN Parameters: See Overloaded procedure
1462 -- INOUT Parameters: p_app_blocks -> TBB Type to which the new TBB will be
1463 -- added
1464 -- p_app_attributes -> Attribute PL/SQL to which the new attribure
1465 -- will be added
1466 -- OUT Parameters: p_time building_block_id -> TBB id of the just created TBB
1467 --
1468 -- Description: Overloading the main procedure. This one will accept the old
1469 -- TBB PL/SQL table (passed in), convert it to the new TYPE,
1470 -- call the overloaded create_timecard_bb procedure that accepts
1471 -- the new TYPE and then convert it back to the old PL/SQL table.
1472 --
1473 -----------------------------------------------------------------------------
1474 PROCEDURE create_time_entry (
1475 p_start_time IN hxc_time_building_blocks.start_time%TYPE,
1476 p_stop_time IN hxc_time_building_blocks.start_time%TYPE,
1477 p_resource_id IN hxc_time_building_blocks.resource_id%TYPE,
1478 p_resource_type IN hxc_time_building_blocks.resource_type%TYPE,
1479 p_comment_text IN hxc_time_building_blocks.comment_text%TYPE,
1480 p_deposit_process IN hxc_deposit_processes.NAME%TYPE,
1481 p_app_blocks IN OUT NOCOPY hxc_self_service_time_deposit.timecard_info,
1482 p_app_attributes IN OUT NOCOPY hxc_self_service_time_deposit.app_attributes_info,
1483 p_time_building_block_id OUT NOCOPY hxc_time_building_blocks.time_building_block_id%TYPE
1484 )
1485 IS
1486 l_proc VARCHAR2 (72);
1487 l_blocks hxc_block_table_type;
1488 BEGIN
1489 g_debug := hr_utility.debug_enabled;
1490
1491 IF g_debug
1492 THEN
1493 l_proc := g_package || 'create_time_entry (Overloaded)';
1494 hr_utility.set_location ('Entering:' || l_proc, 10);
1495 END IF;
1496
1497 l_blocks :=
1498 hxc_timestore_deposit_util.convert_tbb_to_type
1499 (p_blocks => p_app_blocks);
1500 create_time_entry (p_start_time => p_start_time,
1501 p_stop_time => p_stop_time,
1502 p_resource_id => p_resource_id,
1503 p_resource_type => p_resource_type,
1504 p_comment_text => p_comment_text,
1505 p_deposit_process => p_deposit_process,
1506 p_app_blocks => l_blocks,
1507 p_app_attributes => p_app_attributes,
1508 p_time_building_block_id => p_time_building_block_id
1509 );
1510 p_app_blocks :=
1511 hxc_timecard_block_utils.convert_to_dpwr_blocks (p_blocks => l_blocks);
1512
1513 IF g_debug
1514 THEN
1515 hr_utility.set_location ( 'Leaving:'
1516 || l_proc
1517 || ' p_time_building_block_id = '
1518 || p_time_building_block_id,
1519 100
1520 );
1521 END IF;
1522 END create_time_entry;
1523
1524 -----------------------------------------------------------------------------
1525 -- Type: Procedure
1526 -- Scope: Public
1527 -- Name: create_attribute
1528 -- IN Parameters: p_building_block_id -> TBB Id to which you want to attach the
1529 -- attribute
1530 -- p_attribute_name -> Name of the attribute
1531 -- p_attribute_value -> Value you want to store in the attribute
1532 -- p_deposit_process -> Name of the deposit process which we will
1533 -- use the mapping of
1534 -- p_attribute_id -> Used to group attributes, use default (NULL)
1535 -- if you only have one type of attribute/TBB
1536 -- INOUT Parameters: p_app_attributes -> Attribute PL/SQL to which the new attribure
1537 -- will be added
1538 --
1539 -- Description: This procedure will allow you to add an attribute to the
1540 -- attributre PL/SQL table (passed in).
1541 -- The attribute will be added to the same TC as all the other
1542 -- attribute already in the table. If you want to add attributes
1543 -- to another timecard, please clear it first and start afresh.
1544 --
1545 -- In this procedure we now assume that only 1 attribute of the
1546 -- same building block info type can be attached to a TBB. E.g.
1547 -- you cannot add 2 PROJECT attributes to one and the same TBB
1548 -- We need to make this assumption so we can group the attributes
1549 -- by building block info type. If the user really wants to have
1550 -- multiple attributes of the same building block info type attached
1551 -- to one TBB, he will have to do the grouping manually by using
1552 -- the p_attribute_id parameter. For every attribute field that
1553 -- belongs to the same group, this needs to be the same id.
1554 --
1555 -----------------------------------------------------------------------------
1556 PROCEDURE create_attribute (
1557 p_building_block_id IN hxc_time_building_blocks.time_building_block_id%TYPE,
1558 p_attribute_name IN hxc_mapping_components.field_name%TYPE,
1559 p_attribute_value IN hxc_time_attributes.attribute1%TYPE,
1560 -- p_category IN hxc_bld_blk_info_type_usages.building_block_category%TYPE,
1561 p_deposit_process IN hxc_deposit_processes.NAME%TYPE,
1562 p_attribute_id IN hxc_time_attributes.time_attribute_id%TYPE,
1563 p_app_attributes IN OUT NOCOPY hxc_self_service_time_deposit.app_attributes_info
1564 )
1565 IS
1566 l_proc VARCHAR2 (72);
1567 l_attribute_count PLS_INTEGER;
1568 l_bld_blk_info_type hxc_bld_blk_info_types.bld_blk_info_type%TYPE;
1569 l_attribute_id hxc_time_attributes.time_attribute_id%TYPE
1570 := NULL;
1571 l_segment hxc_mapping_components.SEGMENT%TYPE;
1572 attr_index PLS_INTEGER;
1573 BEGIN
1574 g_debug := hr_utility.debug_enabled;
1575
1576 IF g_debug
1577 THEN
1578 l_proc := g_package || 'create_attribute';
1579 hr_utility.set_location ('Entering:' || l_proc, 10);
1580 END IF;
1581
1582 hxc_timestore_deposit_util.get_bld_blk_info_type
1583 (p_attribute_name => p_attribute_name,
1584 p_deposit_process => p_deposit_process,
1585 p_bld_blk_info_type => l_bld_blk_info_type,
1586 p_segment => l_segment
1587 );
1588
1589 -- We need to group the attributes per TBB and per bld_blk_info_type
1590 -- We use the attribute_id to do this, all attributes that belong to the
1591 -- same TBB and have the same bld_blk_info_type, will get the same
1592 -- attribute_id. There can be only 1 type/TBB for this to work. If the
1593 -- user wants more of the same type per TBB than he will have to group those
1594 -- attributes manually, this is done using the p_attribute_id parameter of
1595 -- this procedure. In that case we do not perform the automatic assignment
1596 IF (p_attribute_id IS NULL)
1597 THEN
1598 -- see if you can find an existing TBB/bld_blk_info_type combination
1599 -- already present in the p_app_attributes table.
1600 attr_index := p_app_attributes.FIRST;
1601
1602 <<find_attribute_id>>
1603 LOOP
1604 EXIT find_attribute_id WHEN ( NOT p_app_attributes.EXISTS
1605 (attr_index)
1606 OR l_attribute_id IS NOT NULL
1607 );
1608
1609 IF ( (p_app_attributes (attr_index).bld_blk_info_type =
1610 l_bld_blk_info_type
1611 )
1612 AND (p_app_attributes (attr_index).building_block_id =
1613 p_building_block_id
1614 )
1615 AND (p_app_attributes (attr_index).attribute_index IS NULL)
1616 )
1617 THEN -- Found existing bld_blk_info_type/TBB combination
1618 -- Use the same attribute_id from the existing attribute for
1619 -- our new attribute
1620 l_attribute_id :=
1621 p_app_attributes (attr_index).time_attribute_id;
1622 END IF;
1623
1624 attr_index := p_app_attributes.NEXT (attr_index);
1625 END LOOP find_attribute_id;
1626
1627 IF (l_attribute_id IS NULL)
1628 THEN
1629 -- We never found an existing combination, so this must be the first
1630 -- one, lets generate a new, unique id for it
1631 l_attribute_id := p_app_attributes.COUNT;
1632 END IF;
1633 ELSE -- p_attribute_id is not null
1634 -- user knows best!
1635 l_attribute_id := p_attribute_id;
1636 END IF;
1637
1638 l_attribute_count := NVL (p_app_attributes.LAST, 0) + 1;
1639 p_app_attributes (l_attribute_count).time_attribute_id := l_attribute_id;
1640 p_app_attributes (l_attribute_count).building_block_id :=
1641 p_building_block_id;
1642 p_app_attributes (l_attribute_count).attribute_name := p_attribute_name;
1643 p_app_attributes (l_attribute_count).attribute_value :=
1644 p_attribute_value;
1645 p_app_attributes (l_attribute_count).bld_blk_info_type :=
1646 l_bld_blk_info_type;
1647 p_app_attributes (l_attribute_count).CATEGORY := l_bld_blk_info_type;
1648 p_app_attributes (l_attribute_count).SEGMENT := l_segment;
1649 p_app_attributes (l_attribute_count).updated := hxc_timecard.c_no;
1650 p_app_attributes (l_attribute_count).changed := hxc_timecard.c_no;
1651
1652 IF g_debug
1653 THEN
1654 hr_utility.set_location ('Leaving:' || l_proc, 20);
1655 END IF;
1656 END create_attribute;
1657
1658 -----------------------------------------------------------------------------
1659 -- Type: Procedure
1660 -- Scope: Public
1661 -- Name: update_building_block
1662 -- IN Parameters: p_building_block_id -> Id of TBB you want to update
1663 -- p_measure -> New value for measure
1664 -- p_unit_of_measure -> Leave defaulted to HOURS
1665 -- p_start_time -> New value for start time
1666 -- p_stop_time -> New value for stop time
1667 -- p_comment_text -> New value for comment
1668 -- p_deposit_process -> Name of the deposit process which we will
1669 -- use the mapping of
1670 -- INOUT Parameters: p_app_blocks -> TBB PL/SQL table in which the update will
1671 -- take place.
1672 -- p_app_attributes -> Attribute PL/SQL table holding attributes
1673 -- returned from the database. Although not
1674 -- really needed here, it is necessary for
1675 -- re-depositing the timecard.
1676 --
1677 -- Description: This procedure will allow you to update the measure, start time
1678 -- stop time or comment of a TBB.
1679 -- The attribute will be updated in the same TC as all the other
1680 -- attribute already in the table. If you want to update attributes
1681 -- on another timecard, please clear it first and start afresh.
1682 --
1683 -----------------------------------------------------------------------------
1684 PROCEDURE update_building_block (
1685 p_building_block_id IN hxc_time_building_blocks.time_building_block_id%TYPE,
1686 p_measure IN hxc_time_building_blocks.measure%TYPE,
1687 p_unit_of_measure IN hxc_time_building_blocks.unit_of_measure%TYPE,
1688 p_start_time IN hxc_time_building_blocks.start_time%TYPE,
1689 p_stop_time IN hxc_time_building_blocks.stop_time%TYPE,
1690 p_comment_text IN hxc_time_building_blocks.comment_text%TYPE,
1691 -- p_time_recipient_id IN NUMBER,
1692 p_deposit_process IN hxc_deposit_processes.NAME%TYPE,
1693 p_app_blocks IN OUT NOCOPY hxc_block_table_type,
1694 p_app_attributes IN OUT NOCOPY hxc_self_service_time_deposit.app_attributes_info
1695 )
1696 IS
1697 l_proc VARCHAR2 (72);
1698 l_building_block_index PLS_INTEGER;
1699 l_load_new_timecard BOOLEAN;
1700 BEGIN
1701 g_debug := hr_utility.debug_enabled;
1702
1703 IF g_debug
1704 THEN
1705 l_proc := g_package || 'update_building_block';
1706 hr_utility.set_location ('Entering:' || l_proc, 10);
1707 END IF;
1708
1709 l_load_new_timecard := FALSE;
1710
1711 -- check if a timecard is loaded
1712 IF (p_app_blocks.COUNT <> 0)
1713 THEN
1714 -- check to see if you can find the BB in the loaded timecard
1715 l_building_block_index :=
1716 hxc_timestore_deposit_util.get_index_in_bb_table
1717 (p_bb_table => p_app_blocks,
1718 p_bb_id_to_find => p_building_block_id
1719 );
1720
1721 IF (l_building_block_index < 0)
1722 THEN -- not found
1723 -- User must be updating another timecard retrieve the new timecard
1724 -- from the database. Lets hope the user did a deposit of his data
1725 -- if not, he will have lost it now!!!
1726 l_load_new_timecard := TRUE;
1727 ELSE -- TBB found
1728 NULL;
1729 END IF; -- IF l_building_block_index < 0
1730 ELSE -- no timecard is loaded
1731 l_load_new_timecard := TRUE;
1732 END IF; -- IF p_app_blocks.COUNT <> 0
1733
1734 IF l_load_new_timecard
1735 THEN
1736 -- First clear the global tables so no garbage gets loaded with the
1737 -- updated TC
1738 hxc_self_service_time_deposit.initialize_globals;
1739 -- then get the TC and attributes out of the DB into the PL/SQL Tables
1740 hxc_timestore_deposit_util.get_timecard_tables
1741 (p_building_block_id => p_building_block_id,
1742 -- p_time_recipient_id=> p_time_recipient_id,
1743 p_deposit_process => p_deposit_process,
1744 p_app_blocks => p_app_blocks,
1745 p_app_attributes => p_app_attributes
1746 );
1747 -- now find where in the p_app_blocks table our TBB is situated
1748 l_building_block_index :=
1749 hxc_timestore_deposit_util.get_index_in_bb_table
1750 (p_bb_table => p_app_blocks,
1751 p_bb_id_to_find => p_building_block_id
1752 );
1753
1754 IF g_debug
1755 THEN
1756 hr_utility.set_location ( ' - l_building_block_index = '
1757 || l_building_block_index,
1758 20
1759 );
1760 END IF;
1761 END IF;
1762
1763 -- only change the values if something is passed in the procedure
1764 -- We need to do this workaround to avoid updating values to NULL
1765 -- If a system default is being used then we must not update the argument
1766 -- value.
1767 IF ((p_measure <> hr_api.g_number) OR (p_measure IS NULL))
1768 THEN
1769 p_app_blocks (l_building_block_index).measure := p_measure;
1770 END IF;
1771
1772 IF ( (p_unit_of_measure <> hr_api.g_varchar2)
1773 OR (p_unit_of_measure IS NULL)
1774 )
1775 THEN
1776 p_app_blocks (l_building_block_index).unit_of_measure :=
1777 p_unit_of_measure;
1778 END IF;
1779
1780 IF ((p_start_time <> hr_api.g_date) OR (p_start_time IS NULL))
1781 THEN
1782 -- Adding fix for Bug. no. 3327697
1783 p_app_blocks (l_building_block_index).start_time :=
1784 fnd_date.date_to_canonical (p_start_time);
1785 END IF;
1786
1787 IF ((p_stop_time <> hr_api.g_date) OR (p_stop_time IS NULL))
1788 THEN
1789 -- Adding fix for Bug. no. 3327697
1790 p_app_blocks (l_building_block_index).stop_time :=
1791 fnd_date.date_to_canonical (p_stop_time);
1792 END IF;
1793
1794 p_app_blocks (l_building_block_index).parent_is_new := hxc_timecard.c_no;
1795 -- Don't know yet if I need this here or not
1796 -- p_app_blocks ( l_building_block_index).object_version_number := 1;
1797 p_app_blocks (l_building_block_index).date_from :=
1798 fnd_date.date_to_canonical (SYSDATE);
1799 p_app_blocks (l_building_block_index).date_to :=
1800 fnd_date.date_to_canonical (hr_general.end_of_time);
1801
1802 IF ((p_comment_text <> hr_api.g_varchar2) OR (p_comment_text IS NULL))
1803 THEN
1804 p_app_blocks (l_building_block_index).comment_text := p_comment_text;
1805 END IF;
1806
1807 -- Indicate that this is an updated TBB, not a new one
1808 p_app_blocks (l_building_block_index).NEW := hxc_timecard.c_no;
1809 p_app_blocks (l_building_block_index).changed := hxc_timecard.c_yes;
1810
1811 IF g_debug
1812 THEN
1813 hr_utility.set_location ('Leaving:' || l_proc, 30);
1814 END IF;
1815 END update_building_block;
1816
1817 -----------------------------------------------------------------------------
1818 -- Type: Procedure
1819 -- Scope: Public
1820 -- Name: update_building_block
1821 -- IN Parameters: See Overloaded procedure
1822 -- INOUT Parameters: p_app_blocks -> TBB Type table in which the update will
1823 -- take place.
1824 -- p_app_attributes -> Attribute PL/SQL table holding attributes
1825 -- returned from the database. Although not
1826 -- really needed here, it is necessary for
1827 -- re-depositing the timecard.
1828 --
1829 -- Description: Overloading the main procedure. This one will accept the old
1830 -- TBB PL/SQL table (passed in), convert it to the new TYPE,
1831 -- call the overloaded create_timecard_bb procedure that accepts
1832 -- the new TYPE and then convert it back to the old PL/SQL table.
1833 --
1834 -----------------------------------------------------------------------------
1835 PROCEDURE update_building_block (
1836 p_building_block_id IN hxc_time_building_blocks.time_building_block_id%TYPE,
1837 p_measure IN hxc_time_building_blocks.measure%TYPE,
1838 p_unit_of_measure IN hxc_time_building_blocks.unit_of_measure%TYPE,
1839 p_start_time IN hxc_time_building_blocks.start_time%TYPE,
1840 p_stop_time IN hxc_time_building_blocks.stop_time%TYPE,
1841 p_comment_text IN hxc_time_building_blocks.comment_text%TYPE,
1842 -- p_time_recipient_id IN NUMBER,
1843 p_deposit_process IN hxc_deposit_processes.NAME%TYPE,
1844 p_app_blocks IN OUT NOCOPY hxc_self_service_time_deposit.timecard_info,
1845 p_app_attributes IN OUT NOCOPY hxc_self_service_time_deposit.app_attributes_info
1846 )
1847 IS
1848 l_proc VARCHAR2 (72);
1849 l_blocks hxc_block_table_type;
1850 BEGIN
1851 g_debug := hr_utility.debug_enabled;
1852
1853 IF g_debug
1854 THEN
1855 l_proc := g_package || 'update_building_block (Overloaded)';
1856 hr_utility.set_location ('Entering:' || l_proc, 10);
1857 END IF;
1858
1859 l_blocks :=
1860 hxc_timestore_deposit_util.convert_tbb_to_type
1861 (p_blocks => p_app_blocks);
1862 update_building_block (p_building_block_id => p_building_block_id,
1863 p_measure => p_measure,
1864 p_unit_of_measure => p_unit_of_measure,
1865 p_start_time => p_start_time,
1866 p_stop_time => p_stop_time,
1867 p_comment_text => p_comment_text,
1868 -- p_time_recipient_id => p_time_recipient_id,
1869 p_deposit_process => p_deposit_process,
1870 p_app_blocks => l_blocks,
1871 p_app_attributes => p_app_attributes
1872 );
1873 p_app_blocks :=
1874 hxc_timecard_block_utils.convert_to_dpwr_blocks (p_blocks => l_blocks);
1875
1876 IF g_debug
1877 THEN
1878 hr_utility.set_location ('Leaving:' || l_proc, 20);
1879 END IF;
1880 END update_building_block;
1881
1882 -----------------------------------------------------------------------------
1883 -- Type: Procedure
1884 -- Scope: Public
1885 -- Name: delete_detail_bb
1886 -- IN Parameters: p_building_block_id -> Id of TBB you want to delete, this
1887 -- needs to be a DETAIL TBB
1888 -- p_deposit_process -> Name of the deposit process which we will
1889 -- use the mapping of
1890 -- p_effective_date -> date as of which the TBB will be deleted
1891 -- defaults to sysdate, use default
1892 -- INOUT Parameters: p_app_blocks -> TBB Type table in which the update will
1893 -- take place.
1894 -- p_app_attributes -> Attribute PL/SQL table holding attributes
1895 -- returned from the database. Although not
1896 -- really needed here, it is necessary for
1897 -- re-depositing the timecard.
1898 --
1899 -- Description: This procedure will allow you to delete a DETAIL TBB. The
1900 -- delete is a soft delete, so the TBB is not actually purged
1901 -- from the database, it is just end dated. That is why you
1902 -- have to perform a deposit to get the delete through.
1903 --
1904 -----------------------------------------------------------------------------
1905 PROCEDURE delete_detail_bb (
1906 p_building_block_id IN hxc_time_building_blocks.time_building_block_id%TYPE,
1907 -- p_time_recipient_id IN NUMBER,
1908 p_deposit_process IN hxc_deposit_processes.NAME%TYPE,
1909 p_effective_date IN hxc_time_building_blocks.stop_time%TYPE,
1910 p_app_blocks IN OUT NOCOPY hxc_block_table_type,
1911 p_app_attributes IN OUT NOCOPY hxc_self_service_time_deposit.app_attributes_info
1912 )
1913 IS
1914 l_proc VARCHAR2 (72);
1915 l_building_block_index PLS_INTEGER;
1916 BEGIN
1917 g_debug := hr_utility.debug_enabled;
1918
1919 IF g_debug
1920 THEN
1921 l_proc := g_package || 'delete_detail_bb';
1922 hr_utility.set_location ('Entering:' || l_proc, 10);
1923 END IF;
1924
1925 -- First clear the global tables so no garbage gets loaded with the updated TC
1926 hxc_self_service_time_deposit.initialize_globals;
1927 -- then get the TC and attributes out of the DB into the PL/SQL Tables
1928 hxc_timestore_deposit_util.get_timecard_tables
1929 (p_building_block_id => p_building_block_id,
1930 -- p_time_recipient_id=> p_time_recipient_id,
1931 p_deposit_process => p_deposit_process,
1932 p_app_blocks => p_app_blocks,
1933 p_app_attributes => p_app_attributes
1934 );
1935 -- now find where in the p_app_blocks table our TBB is situated
1936 l_building_block_index :=
1937 hxc_timestore_deposit_util.get_index_in_bb_table
1938 (p_bb_table => p_app_blocks,
1939 p_bb_id_to_find => p_building_block_id
1940 );
1941 -- Set the date_to of the TBB to the sysdate, that will 'delete' the TBB
1942 -- This is, as you can see a soft delete, the TBB will not be visible for the
1943 -- user anymore, but it will stay in the DB (with a date_to of sysdate)
1944 p_app_blocks (l_building_block_index).date_to :=
1945 fnd_date.date_to_canonical (p_effective_date);
1946 -- indicate that this TBB has changed (delete)
1947 p_app_blocks (l_building_block_index).NEW := hxc_timecard.c_no;
1948 p_app_blocks (l_building_block_index).changed := hxc_timecard.c_yes;
1949
1950 IF g_debug
1951 THEN
1952 hr_utility.set_location ('Leaving:' || l_proc, 20);
1953 END IF;
1954 END delete_detail_bb;
1955
1956 -----------------------------------------------------------------------------
1957 -- Type: Procedure
1958 -- Scope: Public
1959 -- Name: delete_detail_bb
1960 -- IN Parameters: see overloaded procedure
1961 -- INOUT Parameters: p_app_blocks -> TBB PL/SQL table in which the update will
1962 -- take place.
1963 -- p_app_attributes -> Attribute PL/SQL table holding attributes
1964 -- returned from the database. Although not
1965 -- really needed here, it is necessary for
1966 -- re-depositing the timecard.
1967 --
1968 -- Description: Overloading the main procedure. This one will accept the old
1969 -- TBB PL/SQL table (passed in), convert it to the new TYPE,
1970 -- call the overloaded create_timecard_bb procedure that accepts
1971 -- the new TYPE and then convert it back to the old PL/SQL table.
1972 --
1973 -----------------------------------------------------------------------------
1974 PROCEDURE delete_detail_bb (
1975 p_building_block_id IN hxc_time_building_blocks.time_building_block_id%TYPE,
1976 -- p_time_recipient_id IN NUMBER,
1977 p_deposit_process IN hxc_deposit_processes.NAME%TYPE,
1978 p_effective_date IN hxc_time_building_blocks.stop_time%TYPE,
1979 p_app_blocks IN OUT NOCOPY hxc_self_service_time_deposit.timecard_info,
1980 p_app_attributes IN OUT NOCOPY hxc_self_service_time_deposit.app_attributes_info
1981 )
1982 IS
1983 l_proc VARCHAR2 (72);
1984 l_blocks hxc_block_table_type;
1985 BEGIN
1986 g_debug := hr_utility.debug_enabled;
1987
1988 IF g_debug
1989 THEN
1990 l_proc := g_package || 'delete_detail_bb (Overloaded)';
1991 hr_utility.set_location ('Entering:' || l_proc, 10);
1992 END IF;
1993
1994 l_blocks :=
1995 hxc_timestore_deposit_util.convert_tbb_to_type
1996 (p_blocks => p_app_blocks);
1997 delete_detail_bb (p_building_block_id => p_building_block_id,
1998 -- p_time_recipient_id => p_time_recipient_id,
1999 p_deposit_process => p_deposit_process,
2000 p_effective_date => p_effective_date,
2001 p_app_blocks => l_blocks,
2002 p_app_attributes => p_app_attributes
2003 );
2004 p_app_blocks :=
2005 hxc_timecard_block_utils.convert_to_dpwr_blocks (p_blocks => l_blocks);
2006
2007 IF g_debug
2008 THEN
2009 hr_utility.set_location ('Leaving:' || l_proc, 20);
2010 END IF;
2011 END delete_detail_bb;
2012
2013 -- Use to delete a complete timecard, TBB id passed in has to be a TIMECARD TBB
2014 -- This is just a wrapper for HXC_SELF_SERVICE_TIME_DEPOSIT.delete_timecard
2015 -----------------------------------------------------------------------------
2016 -- Type: Procedure
2017 -- Scope: Public
2018 -- Name: delete_timecard
2019 -- IN Parameters: p_building_block_id -> Id of TBB you want to delete, this
2020 -- needs to be a TIMECARD TBB
2021 -- p_mode -> Needs to be DELETE, use default
2022 -- p_deposit_process -> Name of the deposit process which we will
2023 -- use the mapping of
2024 -- p_effective_date -> date as of which the TBB will be deleted
2025 -- defaults to sysdate, use default
2026 -- INOUT Parameters: p_app_blocks -> TBB PL/SQL table in which the update will
2027 -- take place.
2028 --
2029 -- Description: This procedure will allow you to delete a complete timecard.
2030 -- The delete is a soft delete, so the TC is not actually purged
2031 -- from the database, it is just end dated. That is why you
2032 -- have to perform a deposit to get the delete through (this is
2033 -- done automatically by hxc_self_service_time_deposit.delete_timecard
2034 -- in this case).
2035 --
2036 -----------------------------------------------------------------------------
2037 PROCEDURE delete_timecard (
2038 p_building_block_id IN hxc_time_building_blocks.time_building_block_id%TYPE,
2039 p_mode IN VARCHAR2,
2040 p_deposit_process IN hxc_deposit_processes.NAME%TYPE,
2041 -- Not used anymore, left in for backwards compatibility
2042 p_retrieval_process IN VARCHAR2,
2043 -- Not used anymore, left in for backwards compatibility
2044 p_effective_date IN hxc_time_building_blocks.stop_time%TYPE,
2045 -- Not used anymore, left in for backwards compatibility
2046 p_template IN VARCHAR2
2047 )
2048 IS
2049 l_proc VARCHAR2 (72);
2050 l_dummy VARCHAR2 (10);
2051 BEGIN
2052 g_debug := hr_utility.debug_enabled;
2053 l_dummy := 'N';
2054
2055 IF g_debug
2056 THEN
2057 l_proc := g_package || 'delete_timecard';
2058 hr_utility.set_location ('Entering:' || l_proc, 10);
2059 END IF;
2060
2061 IF (NOT hxc_timestore_deposit_util.cla_enabled
2062 (p_building_block_id => p_building_block_id)
2063 )
2064 THEN
2065 hxc_timecard.delete_timecard (p_mode => p_mode,
2066 p_template => p_template,
2067 p_timecard_id => p_building_block_id,
2068 p_timecard_ok => l_dummy
2069 );
2070 -- Clear the PL/SQL tables as to similate a delete of the timecard.
2071 hxc_self_service_time_deposit.initialize_globals;
2072 END IF;
2073
2074 IF g_debug
2075 THEN
2076 hr_utility.set_location ('Leaving:' || l_proc, 20);
2077 END IF;
2078 END delete_timecard;
2079
2080 -----------------------------------------------------------------------------
2081 -- Type: Procedure
2082 -- Scope: Public
2083 -- Name: update_attribute
2084 -- IN Parameters: p_time_attribute_id -> Id of the attribute you want to update
2085 -- p_attribute_value -> New value you want to store in the attribute
2086 -- p_deposit_process -> Name of the deposit process which we will
2087 -- use the mapping of
2088 -- INOUT Parameters: p_app_attributes -> Attribute Type in which the update
2089 -- will take place.
2090 --
2091 -- Description: This procedure will allow you to update an attribute in
2092 -- attributre PL/SQL table (passed in). You can only update the
2093 -- value on the attribute record.
2094 -- The attribute will be updated on the same TC as all the other
2095 -- attribute already in the table. If you want to update attributes
2096 -- of another timecard, please clear it first and start afresh.
2097 --
2098 -----------------------------------------------------------------------------
2099 PROCEDURE update_attribute (
2100 p_time_attribute_id IN hxc_time_attributes.time_attribute_id%TYPE,
2101 -- p_building_block_id IN hxc_time_building_blocks.time_building_block_id%TYPE,
2102 p_attribute_name IN hxc_mapping_components.field_name%TYPE,
2103 p_attribute_value IN hxc_time_attributes.attribute1%TYPE,
2104 -- p_category IN hxc_bld_blk_info_type_usages.building_block_category%TYPE,
2105 -- p_time_recipient_id IN NUMBER,
2106 p_deposit_process IN hxc_deposit_processes.NAME%TYPE,
2107 p_app_blocks IN OUT NOCOPY hxc_block_table_type,
2108 p_app_attributes IN OUT NOCOPY hxc_self_service_time_deposit.app_attributes_info
2109 )
2110 IS
2111 l_proc VARCHAR2 (72);
2112 l_attribute_index PLS_INTEGER;
2113 -- l_app_blocks hxc_self_service_time_deposit.timecard_info;
2114 l_load_new_timecard BOOLEAN;
2115
2116 CURSOR csr_tbb_id (
2117 v_time_attribute_id hxc_time_attributes.time_attribute_id%TYPE
2118 )
2119 IS
2120 SELECT time_building_block_id
2121 FROM hxc_time_attribute_usages
2122 WHERE time_attribute_id = v_time_attribute_id;
2123
2124 l_tbb_id hxc_time_building_blocks.time_building_block_id%TYPE;
2125 --csr_tbb_id%ROWTYPE;
2126 BEGIN
2127 g_debug := hr_utility.debug_enabled;
2128
2129 IF g_debug
2130 THEN
2131 l_proc := g_package || 'update_attribute';
2132 hr_utility.set_location ('Entering:' || l_proc, 10);
2133 END IF;
2134
2135 l_load_new_timecard := FALSE;
2136
2137 -- check if a timecard is loaded
2138 IF (p_app_attributes.COUNT <> 0)
2139 THEN
2140 -- check to see if you can find the attribute in the loaded timecard
2141 l_attribute_index :=
2142 hxc_timestore_deposit_util.get_index_in_attr_table
2143 (p_attr_table => p_app_attributes,
2144 p_attr_id_to_find => p_time_attribute_id,
2145 p_attribute_name_to_find => p_attribute_name
2146 );
2147
2148 IF (l_attribute_index < 0)
2149 THEN -- not found
2150 -- User must be updating another timecard, retrieve the new timecard
2151 -- from the database. Lets hope the user did a deposit of his data
2152 -- if not, he will have lost it now!!!
2153 l_load_new_timecard := TRUE;
2154 ELSE -- TBB found
2155 NULL;
2156 END IF; -- IF l_attribute_index < 0
2157 ELSE -- no timecard is loaded
2158 l_load_new_timecard := TRUE;
2159 END IF; -- IF p_app_attributes.COUNT <> 0
2160
2161 IF l_load_new_timecard
2162 THEN
2163 -- First clear the global tables so no garbage gets loaded with the updated TC
2164 hxc_self_service_time_deposit.initialize_globals;
2165
2166 -- Find the TBB to which this attribute belongs
2167 OPEN csr_tbb_id (p_time_attribute_id);
2168
2169 FETCH csr_tbb_id
2170 INTO l_tbb_id;
2171
2172 CLOSE csr_tbb_id;
2173
2174 -- then get the TC and attributes out of the DB into the PL/SQL Tables
2175 -- ARR: Modified for 5096926; after we have constructed the blocks and
2176 -- attributes for the existing timecard, we must clear the mapping cache
2177 -- as only the mapping components for non-null attribute values are created
2178 -- and we may be creating a new attribute, or updating a null attribute
2179 -- in this procedure. Thus, we should regenerate the app attribute
2180 -- mapping cache for deposit and validation.
2181 hxc_timestore_deposit_util.get_timecard_tables
2182 (p_building_block_id => l_tbb_id,
2183 -- p_app_attributes (l_attribute_index).building_block_id,
2184 -- p_time_recipient_id=> p_time_recipient_id,
2185 p_deposit_process => p_deposit_process,
2186 p_clear_mapping_cache => TRUE,
2187 p_app_blocks => p_app_blocks,
2188 p_app_attributes => p_app_attributes
2189 );
2190 -- Now locate our attribute in the PL/SQL table
2191 l_attribute_index :=
2192 hxc_timestore_deposit_util.get_index_in_attr_table
2193 (p_attr_table => p_app_attributes,
2194 p_attr_id_to_find => p_time_attribute_id,
2195 p_attribute_name_to_find => p_attribute_name
2196 );
2197 END IF; -- IF l_load_new_timecard
2198
2199 IF (l_attribute_index <> -1)
2200 THEN
2201 -- ARR: Can only do this if we actually found the app attribute in the
2202 -- table.
2203 -- only change the values if something is passed in the procedure
2204 -- We need to do this workaround to avoid updating values to NULL
2205 -- If a system default is being used then we must not update the argument
2206 -- value.
2207 IF ( (p_attribute_value <> hr_api.g_varchar2)
2208 OR (p_attribute_value IS NULL)
2209 )
2210 THEN
2211 p_app_attributes (l_attribute_index).attribute_value :=
2212 p_attribute_value;
2213
2214 IF g_debug
2215 THEN
2216 hr_utility.set_location
2217 ( ' modifying '
2218 || p_app_attributes (l_attribute_index).attribute_value
2219 || ' to '
2220 || NVL (p_attribute_value, 'NULL'),
2221 20
2222 );
2223 END IF;
2224 END IF;
2225
2226 p_app_attributes (l_attribute_index).updated := hxc_timecard.c_no;
2227 p_app_attributes (l_attribute_index).changed := hxc_timecard.c_yes;
2228 ELSE
2229 -- ARR:
2230 -- We didn't find the app attribute, so we should create it.
2231 --
2232 IF (l_tbb_id IS NOT NULL)
2233 THEN
2234 create_attribute (p_building_block_id => l_tbb_id,
2235 p_attribute_name => p_attribute_name,
2236 p_attribute_value => p_attribute_value,
2237 p_deposit_process => p_deposit_process,
2238 p_attribute_id => p_time_attribute_id,
2239 p_app_attributes => p_app_attributes
2240 );
2241 END IF;
2242 END IF;
2243
2244 IF g_debug
2245 THEN
2246 hr_utility.set_location ('Leaving:' || l_proc, 100);
2247 END IF;
2248 END update_attribute;
2249
2250 -----------------------------------------------------------------------------
2251 -- Type: Procedure
2252 -- Scope: Public
2253 -- Name: update_attribute
2254 -- IN Parameters: See overloaded procedure
2255 -- INOUT Parameters: p_app_blocks -> TBB PL/SQL table in which the update will
2256 -- take place.
2257 -- p_app_attributes -> Attribute PL/SQL table holding attributes
2258 -- returned from the database. Although not
2259 -- really needed here, it is necessary for
2260 -- re-depositing the timecard.
2261 --
2262 -- Description: Overloading the main procedure. This one will accept the old
2263 -- TBB PL/SQL table (passed in), convert it to the new TYPE,
2264 -- call the overloaded create_timecard_bb procedure that accepts
2265 -- the new TYPE and then convert it back to the old PL/SQL table.
2266 --
2267 -----------------------------------------------------------------------------
2268 PROCEDURE update_attribute (
2269 p_time_attribute_id IN hxc_time_attributes.time_attribute_id%TYPE,
2270 -- p_building_block_id IN hxc_time_building_blocks.time_building_block_id%TYPE,
2271 p_attribute_name IN hxc_mapping_components.field_name%TYPE,
2272 p_attribute_value IN hxc_time_attributes.attribute1%TYPE,
2273 -- p_category IN hxc_bld_blk_info_type_usages.building_block_category%TYPE,
2274 -- p_time_recipient_id IN NUMBER,
2275 p_deposit_process IN hxc_deposit_processes.NAME%TYPE,
2276 p_app_blocks IN OUT NOCOPY hxc_self_service_time_deposit.timecard_info,
2277 p_app_attributes IN OUT NOCOPY hxc_self_service_time_deposit.app_attributes_info
2278 )
2279 IS
2280 l_proc VARCHAR2 (72);
2281 l_blocks hxc_block_table_type;
2282 BEGIN
2283 g_debug := hr_utility.debug_enabled;
2284
2285 IF g_debug
2286 THEN
2287 l_proc := g_package || 'update_attribute';
2288 hr_utility.set_location ('Entering:' || l_proc, 10);
2289 END IF;
2290
2291 l_blocks :=
2292 hxc_timestore_deposit_util.convert_tbb_to_type
2293 (p_blocks => p_app_blocks);
2294 update_attribute (p_time_attribute_id => p_time_attribute_id,
2295 -- p_building_block_id => p_building_block_id,
2296 p_attribute_name => p_attribute_name,
2297 p_attribute_value => p_attribute_value,
2298 -- p_category => p_category,
2299 -- p_time_recipient_id => p_time_recipient_id,
2300 p_deposit_process => p_deposit_process,
2301 p_app_blocks => l_blocks,
2302 p_app_attributes => p_app_attributes
2303 );
2304 p_app_blocks :=
2305 hxc_timecard_block_utils.convert_to_dpwr_blocks (p_blocks => l_blocks);
2306
2307 IF g_debug
2308 THEN
2309 hr_utility.set_location ('Leaving:' || l_proc, 20);
2310 END IF;
2311 END update_attribute;
2312
2313 -----------------------------------------------------------------------------
2314 -- Type: Procedure
2315 -- Scope: Public
2316 -- Name: execute_deposit_process
2317 --
2318 -- IN Parameters: p_validate-> if false, no commit will happen, only validation
2319 -- p_mode-> 'SUBMIT', 'SAVE', 'MIGRATION', 'FORCE_SAVE' or
2320 -- 'FORCE_SUBMIT'
2321 -- p_deposit_process-> process for deposit
2322 -- p_retrieval_process-> process for retrieval
2323 -- INOUT Parameters: p_app_blocks -> TBB PL/SQL table you want to deposit
2324 -- p_app_attributes-> Attr PL/SQL table you want to deposit
2325 -- OUT Parameters: p_messages-> TBB PL/SQL table containing msg from deposit process
2326 -- p_timecard_id-> new TC ID
2327 -- p_timecard_ovn-> new TC OVN
2328 --
2329 -- Description: Wrapper for the execute_deposit_process in old API
2330 --
2331 -----------------------------------------------------------------------------
2332 PROCEDURE execute_deposit_process (
2333 p_validate IN BOOLEAN,
2334 p_mode IN VARCHAR2,
2335 p_deposit_process IN VARCHAR2,
2336 p_retrieval_process IN VARCHAR2,
2337 -- p_add_security IN BOOLEAN DEFAULT TRUE,
2338 p_app_attributes IN OUT NOCOPY hxc_self_service_time_deposit.app_attributes_info,
2339 p_app_blocks IN OUT NOCOPY hxc_block_table_type,
2340 -- hxc_self_service_time_deposit.timecard_info,
2341 p_messages OUT NOCOPY hxc_message_table_type,
2342 -- hxc_self_service_time_deposit.message_table,
2343 p_timecard_id OUT NOCOPY hxc_time_building_blocks.time_building_block_id%TYPE,
2344 p_timecard_ovn OUT NOCOPY hxc_time_building_blocks.object_version_number%TYPE,
2345 p_template IN VARCHAR2,
2346 p_item_type IN wf_items.item_type%TYPE,
2347 p_approval_prc IN wf_process_activities.process_name%TYPE,
2348 p_process_terminated_employees IN BOOLEAN,
2349 p_approve_term_emps_on_submit IN BOOLEAN
2350 )
2351 IS
2352 l_proc VARCHAR2 (72);
2353 l_building_block_count PLS_INTEGER;
2354 l_attributes hxc_attribute_table_type;
2355 l_validate VARCHAR2 (1);
2356 i PLS_INTEGER;
2357 l_message fnd_new_messages.MESSAGE_TEXT%TYPE;
2358 l_approval_style_id NUMBER;
2359 l_locked_success BOOLEAN;
2360 l_released_success BOOLEAN;
2361 l_row_lock_id ROWID;
2362 l_appl_set_id NUMBER;
2363 BEGIN
2364 g_debug := hr_utility.debug_enabled;
2365 l_attributes := hxc_attribute_table_type ();
2366
2367 IF g_debug
2368 THEN
2369 l_proc := g_package || 'execute_deposit_process';
2370 hr_utility.set_location ('Entering:' || l_proc, 10);
2371 END IF;
2372
2373 -- issue savepoint in case running in validate mode
2374 SAVEPOINT validation_only;
2375 hxc_timecard_block_utils.initialize_timecard_index;
2376
2377 -- assign p_validate in global variable.
2378 g_validate := p_validate;
2379
2380 -- take out a lock on the period
2381 hxc_timestore_deposit_util.request_lock
2382 (p_app_blocks => p_app_blocks,
2383 p_messages => p_messages,
2384 p_locked_success => l_locked_success,
2385 p_row_lock_id => l_row_lock_id
2386 );
2387
2388 IF (l_locked_success)
2389 THEN
2390 -- before doing anything, we need to populate the approval_status of the
2391 -- TBBs. This used to be set manually but now we derive it from the mode
2392 -- passed in here. Since we did not have the mode handy when building up
2393 -- the TC, we left the approval_status empty. Now we will set it here.
2394 -- We set all approval_status of all TBBs of the timecard to the same
2395 -- value (as it should be).
2396 IF ((p_mode = c_migration) OR (p_process_terminated_employees))
2397 THEN
2398 hxc_preference_evaluation.set_migration_mode
2399 (p_migration_mode => TRUE);
2400 ELSE
2401 hxc_preference_evaluation.set_migration_mode
2402 (p_migration_mode => FALSE);
2403 END IF;
2404
2405 IF (p_mode = c_migration)
2406 THEN
2407 l_approval_style_id :=
2408 hxc_timestore_deposit_util.approval_style_id
2409 (p_approval_style_name => c_auto_approve_name);
2410 ELSE -- from the employee's preference
2411 IF ( (p_approve_term_emps_on_submit)
2412 AND (hxc_preference_evaluation.employment_ended
2413 (p_person_id => p_app_blocks (p_app_blocks.FIRST).resource_id
2414 )
2415 )
2416 AND ( (p_mode = hxc_timecard.c_submit)
2417 OR (p_mode = c_tk_submit)
2418 )
2419 )
2420 THEN
2421 l_approval_style_id :=
2422 hxc_timestore_deposit_util.approval_style_id
2423 (p_approval_style_name => c_approval_on_submit_name);
2424 ELSE
2425 l_approval_style_id :=
2426 hxc_preference_evaluation.resource_preferences
2427 (p_resource_id => p_app_blocks
2428 (p_app_blocks.FIRST).resource_id,
2429 p_pref_code => 'TS_PER_APPROVAL_STYLE',
2430 p_attribute_n => 1,
2431 p_evaluation_date => SYSDATE
2432 );
2433 END IF;
2434 END IF;
2435
2436 l_appl_set_id :=
2437 TO_NUMBER
2438 (hxc_preference_evaluation.resource_preferences
2439 (p_resource_id => p_app_blocks (p_app_blocks.FIRST).resource_id,
2440 p_pref_code => 'TS_PER_APPLICATION_SET',
2441 p_attribute_n => 1,
2442 p_evaluation_date => fnd_date.canonical_to_date
2443 (p_app_blocks
2444 (p_app_blocks.FIRST).start_time
2445 )
2446 )
2447 );
2448 l_building_block_count := p_app_blocks.FIRST;
2449
2450 LOOP
2451 EXIT WHEN (NOT p_app_blocks.EXISTS (l_building_block_count));
2452 p_app_blocks (l_building_block_count).approval_status :=
2453 hxc_timestore_deposit_util.get_approval_status (p_mode);
2454
2455 -- In case of MIGRATION we just override what that user passes in as
2456 -- approval style (we set it to Auto Approve regardless), in all other
2457 -- cases we never override, only if nothing is provided we put
2458 -- something
2459 IF ( (p_app_blocks (l_building_block_count).approval_style_id IS NULL
2460 )
2461 OR (p_mode = c_migration)
2462 )
2463 THEN
2464 p_app_blocks (l_building_block_count).approval_style_id :=
2465 l_approval_style_id;
2466 END IF;
2467
2468 p_app_blocks (l_building_block_count).application_set_id :=
2469 l_appl_set_id;
2470 l_building_block_count :=
2471 p_app_blocks.NEXT (l_building_block_count);
2472 END LOOP;
2473
2474 -- convert the app_attributes into attributes that the DPWR understands
2475 hxc_timestore_deposit_util.convert_app_attributes_to_type
2476 (p_attributes => l_attributes,
2477 p_app_attributes => p_app_attributes
2478 );
2479
2480 IF g_debug
2481 THEN
2482 hr_utility.TRACE ('l_attributes.count = ' || l_attributes.COUNT);
2483 hr_utility.TRACE ( 'p_app_attributes.count = '
2484 || p_app_attributes.COUNT
2485 );
2486 END IF;
2487
2488
2489 -- Bug 14828613
2490 -- Setting this global variable for Absences.
2491 hxc_retrieve_absences.g_lock_row_id := l_row_lock_id;
2492
2493
2494 -- perform the deposit using NEW DPWR
2495 IF (p_mode = hxc_timecard.c_save)
2496 THEN
2497 hxc_timestore_deposit_util.save_timecard
2498 (p_blocks => p_app_blocks,
2499 p_attributes => l_attributes,
2500 p_messages => p_messages,
2501 p_timecard_id => p_timecard_id,
2502 p_timecard_ovn => p_timecard_ovn
2503 );
2504 ELSIF (p_mode = hxc_timecard.c_submit)
2505 THEN
2506 hxc_timestore_deposit_util.submit_timecard
2507 (p_item_type => p_item_type,
2508 p_approval_prc => p_approval_prc,
2509 p_template => p_template,
2510 p_mode => p_mode,
2511 p_blocks => p_app_blocks,
2512 p_attributes => l_attributes,
2513 p_messages => p_messages,
2514 p_timecard_id => p_timecard_id,
2515 p_timecard_ovn => p_timecard_ovn
2516 );
2517 ELSIF (p_mode = c_tk_save)
2518 THEN
2519 hxc_timekeeper.save_timecard (p_blocks => p_app_blocks,
2520 p_attributes => l_attributes,
2521 p_messages => p_messages,
2522 p_timecard_id => p_timecard_id,
2523 p_timecard_ovn => p_timecard_ovn
2524 );
2525 ELSIF (p_mode = c_tk_submit)
2526 THEN
2527 hxc_timekeeper.submit_timecard (p_blocks => p_app_blocks,
2528 p_attributes => l_attributes,
2529 p_messages => p_messages,
2530 p_timecard_id => p_timecard_id,
2531 p_timecard_ovn => p_timecard_ovn
2532 );
2533 ELSIF (p_mode = c_migration)
2534 THEN
2535 IF (p_retrieval_process IS NOT NULL)
2536 THEN
2537 hxc_timestore_deposit_util.submit_timecard
2538 (p_item_type => 'HXCEMP',
2539 p_approval_prc => 'HXC_APPROVAL',
2540 p_template => p_template,
2541 p_mode => p_mode,
2542 p_retrieval_process => p_retrieval_process,
2543 p_blocks => p_app_blocks,
2544 p_attributes => l_attributes,
2545 p_messages => p_messages,
2546 p_timecard_id => p_timecard_id,
2547 p_timecard_ovn => p_timecard_ovn
2548 );
2549 ELSE
2550 fnd_message.set_name ('HXC', 'HXC_0075_HDP_DPROC_NAME_MAND');
2551 fnd_msg_pub.ADD;
2552 END IF;
2553 END IF;
2554
2555 hxc_timestore_deposit_util.release_lock
2556 (p_app_blocks => p_app_blocks,
2557 p_messages => p_messages,
2558 p_released_success => l_released_success,
2559 p_row_lock_id => l_row_lock_id
2560 );
2561
2562 -- The Projects Migration needs to have the content of these tables so
2563 -- we don't clear them for mode Migration. For all the other modes,
2564 -- we do clear the tables. This is done for useability so the users don't
2565 -- have to do this themselves every time. The clearing of the tables can
2566 -- always be removed if other modes also want to use the content of the
2567 -- tables but then the docs will have to be updated telling users to clear
2568 -- the table manually themselves (by calling these clear procedures in
2569 -- their wrappers as below.
2570 IF (p_mode <> c_migration)
2571 THEN
2572 -- Finished, clean up
2573 hxc_timestore_deposit_util.clear_building_block_table
2574 (p_app_blocks => p_app_blocks);
2575 hxc_timestore_deposit_util.clear_attribute_table
2576 (p_app_attributes => p_app_attributes);
2577 -- We don't really wanna clear this as the user might want to read them.
2578 -- clear_message_table (p_messages => p_messages);
2579 END IF;
2580 END IF; -- lock was unsuccessfull, nothing was done
2581
2582 IF (p_validate)
2583 THEN
2584 RAISE hr_api.validate_enabled;
2585 END IF;
2586
2587 IF g_debug
2588 THEN
2589 hr_utility.set_location ('Leaving:' || l_proc, 20);
2590 END IF;
2591 EXCEPTION
2592 --
2593 WHEN hr_api.validate_enabled
2594 THEN
2595 -- As the Validate_Enabled exception has been raised
2596 -- we must rollback to the savepoint
2597 ROLLBACK TO validation_only;
2598
2599 IF g_debug
2600 THEN
2601 hr_utility.set_location ('Leaving (validation mode):' || l_proc,
2602 30
2603 );
2604 END IF;
2605 WHEN OTHERS
2606 THEN
2607 -- make sure we release the lock
2608 hxc_timestore_deposit_util.release_lock
2609 (p_app_blocks => p_app_blocks,
2610 p_messages => p_messages,
2611 p_released_success => l_released_success,
2612 p_row_lock_id => l_row_lock_id
2613 );
2614 hr_utility.trace('Backtrace '||dbms_utility.format_error_backtrace);
2615 RAISE;
2616 END execute_deposit_process;
2617
2618 -----------------------------------------------------------------------------
2619 -- Type: Procedure
2620 -- Scope: Public
2621 -- Name: execute_deposit_process
2622 --
2623 -- IN Parameters: p_validate-> if false, no commit will happen, only validation
2624 -- p_mode-> 'SUBMIT', 'SAVE' or 'MIGRATION'
2625 -- p_deposit_process-> process for deposit
2626 -- p_retrieval_process-> process for retrieval
2627 -- INOUT Parameters: p_app_blocks -> TBB PL/SQL table you want to deposit
2628 -- p_app_attributes-> Attr PL/SQL table you want to deposit
2629 -- OUT Parameters: p_messages-> TBB PL/SQL table containing msg from deposit process
2630 -- p_timecard_id-> new TC ID
2631 -- p_timecard_ovn-> new TC OVN
2632 --
2633 -- Description: Wrapper for the execute_deposit_process in old API
2634 --
2635 -----------------------------------------------------------------------------
2636 PROCEDURE execute_deposit_process (
2637 p_validate IN BOOLEAN,
2638 p_mode IN VARCHAR2,
2639 p_deposit_process IN VARCHAR2,
2640 p_retrieval_process IN VARCHAR2,
2641 -- p_add_security IN BOOLEAN DEFAULT TRUE,
2642 p_app_attributes IN OUT NOCOPY hxc_self_service_time_deposit.app_attributes_info,
2643 p_app_blocks IN OUT NOCOPY hxc_self_service_time_deposit.timecard_info,
2644 p_messages OUT NOCOPY hxc_self_service_time_deposit.message_table,
2645 p_timecard_id OUT NOCOPY hxc_time_building_blocks.time_building_block_id%TYPE,
2646 p_timecard_ovn OUT NOCOPY hxc_time_building_blocks.object_version_number%TYPE,
2647 p_template IN VARCHAR2,
2648 p_item_type IN wf_items.item_type%TYPE,
2649 p_approval_prc IN wf_process_activities.process_name%TYPE,
2650 p_process_terminated_employees IN BOOLEAN,
2651 p_approve_term_emps_on_submit IN BOOLEAN
2652 )
2653 IS
2654 l_blocks hxc_block_table_type;
2655 l_messages hxc_message_table_type;
2656 l_proc VARCHAR2 (72);
2657 BEGIN
2658 g_debug := hr_utility.debug_enabled;
2659
2660 IF g_debug
2661 THEN
2662 l_proc := g_package || 'execute_deposit_process (Overloaded)';
2663 hr_utility.set_location ('Entering:' || l_proc, 10);
2664 END IF;
2665
2666 l_blocks :=
2667 hxc_timestore_deposit_util.convert_tbb_to_type
2668 (p_blocks => p_app_blocks);
2669 execute_deposit_process
2670 (p_validate => p_validate,
2671 p_mode => p_mode,
2672 p_deposit_process => p_deposit_process,
2673 p_retrieval_process => p_retrieval_process,
2674 -- p_add_security=>p_add_security,
2675 p_app_attributes => p_app_attributes,
2676 p_app_blocks => l_blocks,
2677 p_messages => l_messages,
2678 p_timecard_id => p_timecard_id,
2679 p_timecard_ovn => p_timecard_ovn,
2680 p_template => p_template,
2681 p_item_type => p_item_type,
2682 p_approval_prc => p_approval_prc,
2683 p_process_terminated_employees => p_process_terminated_employees,
2684 p_approve_term_emps_on_submit => p_approve_term_emps_on_submit
2685 );
2686 p_app_blocks :=
2687 hxc_timecard_block_utils.convert_to_dpwr_blocks (p_blocks => l_blocks);
2688 p_messages :=
2689 hxc_timestore_deposit_util.convert_to_dpwr_messages
2690 (p_messages => l_messages);
2691
2692 IF g_debug
2693 THEN
2694 hr_utility.set_location ('Leaving:' || l_proc, 20);
2695 END IF;
2696 END execute_deposit_process;
2697
2698 PROCEDURE clear_building_block_table (
2699 p_app_blocks IN OUT NOCOPY hxc_block_table_type
2700 )
2701 IS
2702 BEGIN
2703 hxc_timestore_deposit_util.clear_building_block_table (p_app_blocks);
2704 END clear_building_block_table;
2705
2706 PROCEDURE clear_attribute_table (
2707 p_app_attributes IN OUT NOCOPY hxc_self_service_time_deposit.app_attributes_info
2708 )
2709 IS
2710 BEGIN
2711 hxc_timestore_deposit_util.clear_attribute_table (p_app_attributes);
2712 END clear_attribute_table;
2713
2714 PROCEDURE clear_message_table (
2715 p_messages IN OUT NOCOPY hxc_message_table_type
2716 )
2717 IS
2718 BEGIN
2719 hxc_timestore_deposit_util.clear_message_table (p_messages);
2720 END clear_message_table;
2721
2722 PROCEDURE log_timecard (
2723 p_app_blocks IN hxc_block_table_type,
2724 p_app_attributes IN hxc_self_service_time_deposit.app_attributes_info
2725 )
2726 IS
2727 BEGIN
2728 hxc_timestore_deposit_util.log_timecard (p_app_blocks,
2729 p_app_attributes);
2730 END log_timecard;
2731
2732 PROCEDURE log_timecard (
2733 p_app_blocks IN hxc_self_service_time_deposit.timecard_info,
2734 p_app_attributes IN hxc_self_service_time_deposit.app_attributes_info
2735 )
2736 IS
2737 BEGIN
2738 hxc_timestore_deposit_util.log_timecard (p_app_blocks,
2739 p_app_attributes);
2740 END log_timecard;
2741
2742 PROCEDURE log_messages (p_messages IN hxc_message_table_type)
2743 IS
2744 BEGIN
2745 hxc_timestore_deposit_util.log_messages (p_messages);
2746 END log_messages;
2747
2748 PROCEDURE log_messages (
2749 p_messages IN hxc_self_service_time_deposit.message_table
2750 )
2751 IS
2752 BEGIN
2753 hxc_timestore_deposit_util.log_messages (p_messages);
2754 END log_messages;
2755 END hxc_timestore_deposit;
[Home] [Help]