[Home] [Help]
PACKAGE BODY: APPS.HXC_SETUP_VALIDATION_PKG
Source
1 Package Body hxc_setup_validation_pkg as
2 /* $Header: hxcotcvld.pkb 120.15 2006/10/06 16:44:09 arundell noship $ */
3
4 g_debug boolean := hr_utility.debug_enabled;
5
6 --
7 --
8 -- ----------------------------------------------------------------------------
9 -- |------------------------< execute_otc_validation >----------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 -- Description:
13 --
14 -- This procedure is used to check that certain areas of OTC are configured
15 -- correctly at time entry. This is to avoid usability issues later on in the
16 -- system.
17 --
18 -- Prerequisites:
19 --
20 -- None
21 --
22 -- In Parameters:
23 -- Name Reqd Type Description
24 --
25 -- Access Status:
26 -- Public.
27 --
28
29 PROCEDURE execute_otc_validation (
30 p_operation VARCHAR2
31 , p_resource_id NUMBER
32 , p_timecard_bb_id NUMBER
33 , p_timecard_bb_ovn NUMBER
34 , p_start_date DATE
35 , p_end_date DATE
36 , p_master_pref_table IN OUT NOCOPY hxc_preference_evaluation.t_pref_table
37 , p_messages IN OUT NOCOPY hxc_message_table_type ) IS
38
39 CURSOR csr_get_retrieval_rules ( p_rtr_grp_id NUMBER ) IS
40 SELECT
41 DISTINCT( rrc.time_recipient_id )
42 FROM
43 hxc_retrieval_rule_comps rrc
44 , hxc_retrieval_rules rr
45 , hxc_retrieval_rule_grp_comps_v rrgc
46 , hxc_retrieval_rule_groups_v rrg
47 WHERE
48 rrg.retrieval_rule_group_id = p_rtr_grp_id
49 AND
50 rrgc.retrieval_rule_group_id = rrg.retrieval_rule_group_id
51 AND
52 rr.retrieval_rule_id = rrgc.retrieval_rule_id
53 AND
54 rrc.retrieval_rule_id = rr.retrieval_rule_id AND
55 rrc.status <> 'WORKING';
56
57
58 -- retrieves list of time recipients in retrieval rule group
59
60 CURSOR csr_get_rtr ( p_rtr_id NUMBER ) IS
61 SELECT
62 DISTINCT( rrc.time_recipient_id )
63 FROM
64 hxc_retrieval_rule_comps rrc
65 , hxc_retrieval_rules rr
66 WHERE
67 rr.retrieval_rule_id = p_rtr_id
68 AND
69 rrc.retrieval_rule_id = rr.retrieval_rule_id AND
70 rrc.status <> 'WORKING';
71
72
73 -- retrieves list of time recipients in application set
74
75 CURSOR csr_get_app_sets ( p_app_set_id NUMBER ) IS
76 SELECT apsc.time_recipient_id
77 FROM hxc_application_set_comps_v apsc
78 , hxc_application_sets_v aps
79 WHERE aps.application_set_id = p_app_set_id
80 AND
81 apsc.application_set_id = aps.application_set_id;
82
83
84 -- tests that the time recipient from the application set
85 -- has a corresponding row in the approval period set
86
87 CURSOR csr_get_app_rec_period(p_time_recipient_id number,
88 p_app_periods number) is
89 SELECT 'Y'
90 FROM hxc_approval_period_comps hapc,
91 hxc_approval_period_sets haps
92 WHERE haps.approval_period_set_id = p_app_periods
93 AND
94 hapc.approval_period_set_id = haps.approval_period_set_id AND
95 hapc.time_recipient_id = p_time_recipient_id;
96
97
98 -- tests that the time recipients in the application set have
99 -- corresponding entries in the approval style components
100
101
102 CURSOR csr_get_approval_style_comps ( p_time_recipient_id number,
103 p_approval_style_id number ) IS
104 SELECT 'Y'
105 FROM hxc_approval_comps hac,
106 hxc_approval_styles has
107 WHERE hac.approval_style_id = p_approval_style_id
108 AND hac.time_recipient_id = p_time_recipient_id
109 OR (has.approval_style_id = p_approval_style_id
110 AND has.name = 'Approval on Submit');
111
112
113 CURSOR csr_get_elp_terg_comps(p_elp_terg_id number,
114 p_time_recipient_id number) IS
115 SELECT 'Y'
116 FROM hxc_entity_groups heg,
117 hxc_entity_group_comps hec,
118 hxc_time_entry_rules hte
119 WHERE heg.entity_type = 'TIME_ENTRY_RULES' and
120 heg.entity_group_id = p_elp_terg_id and
121 hec.ENTITY_GROUP_ID =heg.entity_group_id and
122 hec.entity_id = hte.TIME_ENTRY_RULE_ID and
123 hte.attribute1 = p_time_recipient_id;
124 --
125 -- Added for 115.26, gets timecard information for self
126 -- service, since we can not know it in the properties
127 -- package
128 cursor c_timecard_info
129 (p_timecard_bb_id in NUMBER,
130 p_timecard_bb_ovn in NUMBER) is
131 select approval_status
132 from hxc_timecard_summary
133 where timecard_id = p_timecard_bb_id
134 and timecard_ovn =p_timecard_bb_ovn;
135
136 l_tc_approval_status hxc_timecard_summary.approval_status%type;
137 l_test_edit boolean;
138
139 l_proc VARCHAR2(72);
140
141 l_index BINARY_INTEGER;
142 l_pref_table hxc_preference_evaluation.t_pref_table;
143
144 l_id NUMBER(15);
145 l_pref_code VARCHAR2(30);
146
147 l_rtr_grp_id NUMBER(15);
148 l_app_set_id NUMBER(15);
149 l_approval_period_set_id NUMBER(15);
150 l_approval_style_id NUMBER(15);
151 l_override_approval_style_id hxc_approval_styles.approval_style_id%type;
152 l_elp_terg_id NUMBER(15);
153 l_cla_terg_id NUMBER(15);
154 l_cla_prefs_ok BOOLEAN := FALSE;
155 l_dummy VARCHAR2(1);
156
157 -- Default override approver validation
158 l_default_override_approver_id number(15);
159 l_can_enter_override_approver hxc_pref_hierarchies.attribute1%type;
160
161 l_past_limit_date VARCHAR2(80);
162 l_futur_limit_date VARCHAR2(80);
163
164
165 l_otm_explosion VARCHAR2(1);
166 l_otm_rtr_id hxc_retrieval_rules.retrieval_rule_id%TYPE;
167
168 l_status_allowing_edits hxc_pref_hierarchies.attribute1%TYPE;
169 l_edit_allowed VARCHAR2(5);
170
171 l_rtr_tr_id hxc_time_recipients.time_recipient_id%TYPE;
172 l_aps_tr_id hxc_time_recipients.time_recipient_id%TYPE;
173
174 l_timecard_info_rec hxc_time_entry_rules_utils_pkg.r_timecard_info;
175
176 TYPE r_time_recipient IS RECORD ( DUMMY VARCHAR2(1) );
177
178 TYPE t_time_recipient IS TABLE OF r_time_recipient INDEX BY BINARY_INTEGER;
179
180 -- table for application set and retrieval rule time recipients
181 -- note we are going to use the table index to store the time
182 -- recipient id
183
184 t_aps_tr t_time_recipient;
185 t_rtr_tr t_time_recipient;
186
187 e_no_resource_id_error exception;
188 e_no_rtr_id exception;
189
190 FUNCTION test_aps_vs_rtr (
191 p_rtr_tr t_time_recipient
192 , p_aps_tr t_time_recipient ) RETURN BOOLEAN IS
193
194 l_rtr_index BINARY_INTEGER;
195 l_return BOOLEAN := FALSE;
196
197 BEGIN
198
199 l_rtr_index := t_rtr_tr.FIRST;
200
201 WHILE ( l_rtr_index IS NOT NULL )
202 LOOP
203
204 IF NOT t_aps_tr.EXISTS(l_rtr_index)
205 THEN
206
207 l_return := TRUE;
208 EXIT;
209
210 END IF;
211
212 l_rtr_index := t_rtr_tr.NEXT(l_rtr_index);
213
214 END LOOP;
215
216 RETURN l_return;
217
218 END test_aps_vs_rtr;
219
220 BEGIN -- execute_otc_Validation
221
222 g_debug := hr_utility.debug_enabled;
223
224 if g_debug then
225 l_proc := g_package||'execute_otc_validation';
226 hr_utility.set_location('Processing '||l_proc, 10);
227 end if;
228
229 l_timecard_info_rec.resource_id := p_resource_id;
230 l_timecard_info_rec.timecard_bb_id := p_timecard_bb_id;
231 l_timecard_info_rec.timecard_ovn := p_timecard_bb_ovn;
232 l_timecard_info_rec.start_date := p_start_date;
233 l_timecard_info_rec.end_date := p_end_date;
234
235 -- now get the retrieval rule grp id and application set id
236 -- based on the resource id
237
238 hxc_preference_evaluation.resource_preferences(p_resource_id => l_timecard_info_rec.resource_id,
239 p_preference_code => 'TS_PER_APPLICATION_SET',
240 p_start_evaluation_date => l_timecard_info_rec.start_date,
241 p_end_evaluation_date => l_timecard_info_rec.end_date,
242 p_sorted_pref_table => l_pref_table,
243 p_master_pref_table => p_master_pref_table );
244
245 l_app_set_id := TO_NUMBER(l_pref_table(1).attribute1);
246 if g_debug then
247 hr_utility.trace('gaz - app set id is '||to_char(l_app_set_id));
248 end if;
249
250 hxc_preference_evaluation.resource_preferences(p_resource_id => l_timecard_info_rec.resource_id,
251 p_preference_code => 'TS_PER_RETRIEVAL_RULES',
252 p_start_evaluation_date => l_timecard_info_rec.start_date,
253 p_end_evaluation_date => l_timecard_info_rec.end_date,
254 p_sorted_pref_table => l_pref_table,
255 p_master_pref_table => p_master_pref_table );
256
257 l_rtr_grp_id := TO_NUMBER(l_pref_table(1).attribute1);
258 if g_debug then
259 hr_utility.trace('gaz - rtr grp id is '||to_char(l_rtr_grp_id));
260 end if;
261
262 hxc_preference_evaluation.resource_preferences(p_resource_id => l_timecard_info_rec.resource_id,
263 p_preference_code => 'TC_W_RULES_EVALUATION',
264 p_start_evaluation_date => l_timecard_info_rec.start_date,
265 p_end_evaluation_date => l_timecard_info_rec.end_date,
266 p_sorted_pref_table => l_pref_table,
267 p_master_pref_table => p_master_pref_table );
268
269 l_otm_explosion := l_pref_table(1).attribute1;
270 l_otm_rtr_id := TO_NUMBER(l_pref_table(1).attribute2);
271 if g_debug then
272 hr_utility.trace('gaz - otm explosion is '||l_otm_explosion);
273 end if;
274
275 hxc_preference_evaluation.resource_preferences(p_resource_id => l_timecard_info_rec.resource_id,
276 p_preference_code => 'TS_PER_APPROVAL_PERIODS',
277 p_start_evaluation_date => l_timecard_info_rec.start_date,
278 p_end_evaluation_date => l_timecard_info_rec.end_date,
279 p_sorted_pref_table => l_pref_table,
280 p_master_pref_table => p_master_pref_table );
281
282 l_approval_period_set_id := TO_NUMBER(l_pref_table(1).attribute1);
283 if g_debug then
284 hr_utility.trace('gaz - approval period set id is '||to_char(l_approval_period_set_id));
285 end if;
286
287 hxc_preference_evaluation.resource_preferences(p_resource_id => l_timecard_info_rec.resource_id,
288 p_preference_code => 'TS_PER_APPROVAL_STYLE',
289 p_start_evaluation_date => l_timecard_info_rec.start_date,
290 p_end_evaluation_date => l_timecard_info_rec.end_date,
291 p_sorted_pref_table => l_pref_table,
292 p_master_pref_table => p_master_pref_table );
293
294 l_approval_style_id := TO_NUMBER(l_pref_table(1).attribute1);
295 l_override_approval_style_id := TO_NUMBER(l_pref_table(1).attribute2);
296 if g_debug then
297 hr_utility.trace('gaz - approval style id is '||to_char(l_approval_style_id));
298 hr_utility.trace('gaz - override approval style id is '||to_char(l_override_approval_style_id));
299 end if;
300
301 hxc_preference_evaluation.resource_preferences(p_resource_id => l_timecard_info_rec.resource_id,
302 p_preference_code => 'TC_W_TCRD_ST_ALW_EDITS',
303 p_start_evaluation_date => l_timecard_info_rec.start_date,
304 p_end_evaluation_date => l_timecard_info_rec.end_date,
305 p_sorted_pref_table => l_pref_table,
306 p_master_pref_table => p_master_pref_table );
307
308 l_status_allowing_edits := l_pref_table(1).attribute1;
309 l_past_limit_date := l_pref_table(1).attribute6;
310 l_futur_limit_date := l_pref_table(1).attribute11;
311 if g_debug then
312 hr_utility.trace('gaz - status allowing edits is '||l_status_allowing_edits);
313 hr_utility.trace('gaz - past limit date is '||l_past_limit_date);
314 hr_utility.trace('gaz - futur limit date is '||l_futur_limit_date);
315 end if;
316
317 --ELP Validation
318
319 hxc_preference_evaluation.resource_preferences(p_resource_id => l_timecard_info_rec.resource_id,
320 p_preference_code => 'TS_PER_ELP_RULES',
321 p_start_evaluation_date => l_timecard_info_rec.start_date,
322 p_end_evaluation_date => l_timecard_info_rec.end_date,
323 p_sorted_pref_table => l_pref_table,
324 p_master_pref_table => p_master_pref_table );
325
326 l_elp_terg_id := TO_NUMBER(l_pref_table(1).attribute1);
327 if g_debug then
328 hr_utility.trace('gaz- ELP TERG is ' || to_char(l_elp_terg_id));
329 end if;
330
331 -- Override Approval Validation
332
333 hxc_preference_evaluation.resource_preferences(p_resource_id => l_timecard_info_rec.resource_id,
334 p_preference_code => 'TC_W_APRVR_DFLT_OVRD',
335 p_start_evaluation_date => l_timecard_info_rec.start_date,
336 p_end_evaluation_date => l_timecard_info_rec.end_date,
337 p_sorted_pref_table => l_pref_table,
338 p_master_pref_table => p_master_pref_table );
339
340 l_default_override_approver_id := TO_NUMBER(l_pref_table(1).attribute1);
341
342 hxc_preference_evaluation.resource_preferences(p_resource_id => l_timecard_info_rec.resource_id,
343 p_preference_code => 'TC_W_APRVR_ENBLE_OVRD',
344 p_start_evaluation_date => l_timecard_info_rec.start_date,
345 p_end_evaluation_date => l_timecard_info_rec.end_date,
346 p_sorted_pref_table => l_pref_table,
347 p_master_pref_table => p_master_pref_table );
348
349 l_can_enter_override_approver := l_pref_table(1).attribute1;
350
351 hxc_preference_evaluation.clear_sort_pref_table_cache;
352
353
354 IF l_past_limit_date is null THEN
355 l_past_limit_date := '0001/01/01';
356 ELSE
357 l_past_limit_date := to_char((sysdate - to_number(l_past_limit_date)),'YYYY/MM/DD');
358 END IF;
359
360 IF l_futur_limit_date is null THEN
361 l_futur_limit_date := '4712/12/31';
362 ELSE
363 l_futur_limit_date := to_char((sysdate + to_number(l_futur_limit_date)),'YYYY/MM/DD');
364 END IF;
365
366 if g_debug then
367 hr_utility.trace('gaz - past limit date is '||l_past_limit_date);
368 hr_utility.trace('gaz - futur limit date is '||l_futur_limit_date);
369 end if;
370
371 if g_debug then
372 hr_utility.set_location('Processing '||l_proc, 20);
373 end if;
374
375 -- now get the application set time recipients and retrieval rule group time recipients
376 -- and make sure they match.
377
378 OPEN csr_get_retrieval_rules ( l_rtr_grp_id );
379 FETCH csr_get_retrieval_rules INTO l_rtr_tr_id;
380
381 WHILE csr_get_retrieval_rules%FOUND
382 LOOP
383 if g_debug then
384 hr_utility.set_location('Processing '||l_proc, 25);
385 end if;
386
387 t_rtr_tr(l_rtr_tr_id).dummy := 'N';
388
389 FETCH csr_get_retrieval_rules INTO l_rtr_tr_id;
390
391 END LOOP;
392
393 CLOSE csr_get_retrieval_rules;
394
395 if g_debug then
396 hr_utility.set_location('Processing '||l_proc, 30);
397 end if;
398
399 OPEN csr_get_app_sets ( l_app_set_id );
400 FETCH csr_get_app_sets INTO l_aps_tr_id;
401
402 WHILE csr_get_app_sets%FOUND
403 LOOP
404 if g_debug then
405 hr_utility.set_location('Processing '||l_proc, 35);
406 end if;
407
408 t_aps_tr(l_aps_tr_id).dummy := 'N';
409
410 FETCH csr_get_app_sets INTO l_aps_tr_id;
411
412 END LOOP;
413
414 CLOSE csr_get_app_sets;
415
416 if g_debug then
417 hr_utility.set_location('Processing '||l_proc, 40);
418 end if;
419
423 IF ( test_aps_vs_rtr ( p_rtr_tr => t_rtr_tr
420 -- now test to see if the retrieval rule group time recipients
421 -- is at least a subset of the application set time recipients
422
424 , p_aps_tr => t_aps_tr ) )
425 THEN
426
427 hxc_timecard_message_helper.adderrortocollection (
428 p_messages => p_messages
429 , p_message_name => 'HXC_VLD_APS_VS_RTR_GRP'
430 , p_message_level => 'ERROR'
431 , p_message_field => NULL
432 , p_message_tokens => NULL
433 , p_application_short_name => 'HXC'
434 , p_time_building_block_id => l_timecard_info_rec.timecard_bb_id
435 , p_time_building_block_ovn => l_timecard_info_rec.timecard_ovn
436 , p_time_attribute_id => NULL
437 , p_time_attribute_ovn => NULL );
438
439 END IF;
440
441 if g_debug then
442 hr_utility.set_location('Processing '||l_proc, 50);
443 end if;
444
445 -- now get the time recipients associated with the
446 -- OTM Evaluation rtr if the person has their
447 -- apply schedule rule prefernce set
448
449 IF ( l_otm_explosion = 'Y' )
450 THEN
451 if g_debug then
452 hr_utility.set_location('Processing '||l_proc, 55);
453 end if;
454
455 IF ( l_otm_rtr_id IS NULL )
456 THEN
457 if g_debug then
458 hr_utility.set_location('Processing '||l_proc, 60);
459 end if;
460
461 raise e_no_rtr_id;
462
463 END IF;
464
465 if g_debug then
466 hr_utility.set_location('Processing '||l_proc, 70);
467 end if;
468
469 t_rtr_tr.DELETE;
470
471 OPEN csr_get_rtr ( l_otm_rtr_id );
472 FETCH csr_get_rtr INTO l_rtr_tr_id;
473
474 WHILE csr_get_rtr%FOUND
475 LOOP
476 if g_debug then
477 hr_utility.set_location('Processing '||l_proc, 80);
478 end if;
479
480 t_rtr_tr(l_rtr_tr_id).dummy := 'N';
481
482 FETCH csr_get_rtr INTO l_rtr_tr_id;
483
484 END LOOP;
485
486 CLOSE csr_get_rtr;
487
488 if g_debug then
489 hr_utility.set_location('Processing '||l_proc, 90);
490 end if;
491
492 -- now test to see if the retrieval rule group time recipients
493 -- is at least a subset of the application set time recipients
494
495 IF ( test_aps_vs_rtr ( p_rtr_tr => t_rtr_tr
496 , p_aps_tr => t_aps_tr ) )
497 THEN
498
499 hxc_timecard_message_helper.adderrortocollection (
500 p_messages => p_messages
501 , p_message_name => 'HXC_VLD_APS_VS_RTR_GRP'
502 , p_message_level => 'ERROR'
503 , p_message_field => NULL
504 , p_message_tokens => NULL
505 , p_application_short_name => 'HXC'
506 , p_time_building_block_id => l_timecard_info_rec.timecard_bb_id
507 , p_time_building_block_ovn => l_timecard_info_rec.timecard_ovn
508 , p_time_attribute_id => NULL
509 , p_time_attribute_ovn => NULL );
510
511 END IF;
512
513
514 if g_debug then
515 hr_utility.set_location('Processing '||l_proc, 100);
516 end if;
517
518 END IF; -- IF ( l_otm_explosion = 'Y' ) GPM v115.19
519
520 if g_debug then
521 hr_utility.set_location('Processing '||l_proc, 130);
522 end if;
523
524 -- test that the applications in the application set have a corresponding
525 -- period in the Approval Periods Group
526
527 l_index := t_aps_tr.FIRST;
528
529 WHILE ( l_index IS NOT NULL )
530 LOOP
531
532 if g_debug then
533 hr_utility.trace('Here is the time recipient '||to_char(l_index));
534
535 hr_utility.set_location('Processing '||l_proc, 140);
536 end if;
537
538 OPEN csr_get_app_rec_period(l_index, l_approval_period_set_id);
539 FETCH csr_get_app_rec_period into l_dummy;
540
541 IF csr_get_app_rec_period%NOTFOUND
542 THEN
543 if g_debug then
544 hr_utility.set_location('Processing '||l_proc, 150);
545 end if;
546
547 hxc_timecard_message_helper.adderrortocollection (
548 p_messages => p_messages
549 , p_message_name => 'HXC_APR_NO_REC_PERIOD'
550 , p_message_level => 'ERROR'
551 , p_message_field => NULL
552 , p_message_tokens => NULL
553 , p_application_short_name => 'HXC'
554 , p_time_building_block_id => l_timecard_info_rec.timecard_bb_id
555 , p_time_building_block_ovn => l_timecard_info_rec.timecard_ovn
556 , p_time_attribute_id => NULL
557 , p_time_attribute_ovn => NULL );
558
559 CLOSE csr_get_app_rec_period;
560
561 EXIT;
562
563 END IF;
564
565 CLOSE csr_get_app_rec_period;
566
567 if g_debug then
568 hr_utility.set_location('Processing '||l_proc, 160);
569 end if;
570
571 l_index := t_aps_tr.NEXT(l_index);
572
573 END LOOP; -- t_aps_tr
574
575 if g_debug then
576 hr_utility.set_location('Processing '||l_proc, 170);
577 end if;
578
579
583 l_index := t_aps_tr.FIRST;
580 -- tests that the time recipients in the application set have
581 -- corresponding entries in the approval style components
582
584
585 WHILE ( l_index IS NOT NULL )
586 LOOP
587
588 if g_debug then
589 hr_utility.set_location('Processing '||l_proc, 180);
590 end if;
591
592 OPEN csr_get_approval_style_comps(l_index, l_approval_style_id);
593 FETCH csr_get_approval_style_comps into l_dummy;
594
595 IF csr_get_approval_style_comps%NOTFOUND
596 THEN
597 if g_debug then
598 hr_utility.set_location('Processing '||l_proc, 190);
599 end if;
600
601 hxc_timecard_message_helper.adderrortocollection (
602 p_messages => p_messages
603 , p_message_name => 'HXC_APR_NO_APP_STYLE_COMP'
604 , p_message_level => 'ERROR'
605 , p_message_field => NULL
606 , p_message_tokens => NULL
607 , p_application_short_name => 'HXC'
608 , p_time_building_block_id => l_timecard_info_rec.timecard_bb_id
609 , p_time_building_block_ovn => l_timecard_info_rec.timecard_ovn
610 , p_time_attribute_id => NULL
611 , p_time_attribute_ovn => NULL );
612
613 CLOSE csr_get_approval_style_comps;
614
615 EXIT;
616
617 END IF;
618
619 CLOSE csr_get_approval_style_comps;
620
621 if g_debug then
622 hr_utility.set_location('Processing '||l_proc, 200);
623 end if;
624
625 l_index := t_aps_tr.NEXT(l_index);
626
627 END LOOP; -- t_aps_tr
628 --
629 -- tests that the time recipients in the application set have
630 -- corresponding entries in the override approval style components,
631 -- if it is set
632 --
633 if(l_override_approval_style_id is not null)then
634 l_index := t_aps_tr.FIRST;
635 WHILE ( l_index IS NOT NULL ) LOOP
636
637 if g_debug then
638 hr_utility.set_location('Processing '||l_proc, 201);
639 end if;
640 OPEN csr_get_approval_style_comps(l_index, l_override_approval_style_id);
641 FETCH csr_get_approval_style_comps into l_dummy;
642
643 IF csr_get_approval_style_comps%NOTFOUND THEN
644 if g_debug then
645 hr_utility.set_location('Processing '||l_proc, 202);
646 end if;
647
648 hxc_timecard_message_helper.adderrortocollection
649 (p_messages => p_messages,
650 p_message_name => 'HXC_APR_NO_APP_STYLE_COMP',
651 p_message_level => 'ERROR' ,
652 p_message_field => NULL,
653 p_message_tokens => NULL,
654 p_application_short_name => 'HXC',
655 p_time_building_block_id => l_timecard_info_rec.timecard_bb_id,
656 p_time_building_block_ovn => l_timecard_info_rec.timecard_ovn,
657 p_time_attribute_id => NULL,
658 p_time_attribute_ovn => NULL
659 );
660
661 CLOSE csr_get_approval_style_comps;
662
663 EXIT;
664
665 END IF;
666
667 CLOSE csr_get_approval_style_comps;
668
669 if g_debug then
670 hr_utility.set_location('Processing '||l_proc, 203);
671 end if;
672
673 l_index := t_aps_tr.NEXT(l_index);
674
675 END LOOP; -- t_aps_tr
676 end if; -- Is the Override Style set?
677
678 if g_debug then
679 hr_utility.set_location('Processing '||l_proc, 210);
680 end if;
681
682 --ELP Validation
683 -- tests that the time recipients in the application set have
684 -- corresponding entries in the ELP TERG components
685
686 if (l_elp_terg_id is not null) then
687 l_index := t_aps_tr.FIRST;
688
689 WHILE ( l_index IS NOT NULL )
690 LOOP
691
692 if g_debug then
693 hr_utility.set_location('Processing '||l_proc, 211);
694 end if;
695
696 OPEN csr_get_elp_terg_comps(l_elp_terg_id,l_index);
697 FETCH csr_get_elp_terg_comps into l_dummy;
698
699 IF csr_get_elp_terg_comps%NOTFOUND
700 THEN
701 if g_debug then
702 hr_utility.set_location('Processing '||l_proc, 212);
703 end if;
704
705 hxc_timecard_message_helper.adderrortocollection (
706 p_messages => p_messages
707 , p_message_name => 'HXC_VLD_ELP_VIOLATION'
708 , p_message_level => 'ERROR'
709 , p_message_field => NULL
710 , p_message_tokens => NULL
711 , p_application_short_name => 'HXC'
712 , p_time_building_block_id => l_timecard_info_rec.timecard_bb_id
713 , p_time_building_block_ovn => l_timecard_info_rec.timecard_ovn
714 , p_time_attribute_id => NULL
715 , p_time_attribute_ovn => NULL );
716
717 CLOSE csr_get_elp_terg_comps;
718
719 EXIT;
720
721 END IF;
722
723 CLOSE csr_get_elp_terg_comps;
724
725 if g_debug then
726 hr_utility.set_location('Processing '||l_proc, 213);
727 end if;
728
729 l_index := t_aps_tr.NEXT(l_index);
730
731 END LOOP; -- t_aps_tr
732
733 END IF;
734 if g_debug then
735 hr_utility.set_location('Processing '||l_proc, 214);
736 end if;
737
738 -- Now let's check to see if the user is still allowed to update the timecard
739 -- WWB 2290884
740 -- GPM v115.11
741 -- ARR v115.26 - 4561454
742 --Bug 4733480
743 if(l_timecard_info_rec.timecard_bb_id is null) then
744 l_test_edit := false;
745 else
746 open c_timecard_info(l_timecard_info_rec.timecard_bb_id,l_timecard_info_rec.timecard_ovn);
747 fetch c_timecard_info into l_tc_approval_status;
748 if(c_timecard_info%found) then -- condition added to allow edit check only for timecard and not for templates
749 l_test_edit := true;
750 else
751 l_test_edit := false;
752 l_tc_approval_status := null;
753 end if;
754 close c_timecard_info;
755 end if;
756
757 if l_test_edit then
758
759 l_edit_allowed := NULL;
760
761 IF (to_char(l_timecard_info_rec.start_date,'YYYY/MM/DD') <= l_futur_limit_date and
762 to_char(l_timecard_info_rec.end_date,'YYYY/MM/DD') >= l_past_limit_date )
763 THEN
764 hxc_time_entry_rules_utils_pkg.tc_edit_allowed
765 (p_timecard_id => l_timecard_info_rec.timecard_bb_id
766 ,p_timecard_ovn => l_timecard_info_rec.timecard_ovn
767 ,p_timecard_status => l_tc_approval_status
768 ,p_edit_allowed_preference => l_status_allowing_edits
769 ,p_edit_allowed => l_edit_allowed
770 );
771 ELSE
772 l_edit_allowed := 'FALSE';
773 END IF;
774
775 IF ( l_edit_allowed = 'FALSE' )
776 THEN
777 --
778 -- Make sure this is raised at page level, i.e.
779 -- pass null for the timecard id and ovn.
780 --
781 hxc_timecard_message_helper.adderrortocollection (
782 p_messages => p_messages
783 , p_message_name => 'HXC_VLD_TC_STATUS_CHANGED'
784 , p_message_level => 'ERROR'
785 , p_message_field => NULL
786 , p_message_tokens => NULL
787 , p_application_short_name => 'HXC'
788 , p_time_building_block_id => NULL
789 , p_time_building_block_ovn => NULL
790 , p_time_attribute_id => NULL
791 , p_time_attribute_ovn => NULL );
792
793 END IF;
794
795 END IF; -- Test edit
796 -- +---------------------------------------------------------------------------+
797 -- | |
798 -- | Change Late Audit Set Up Validation |
799 -- | |
800 -- +---------------------------------------------------------------------------+
801
802 l_cla_prefs_ok := FALSE;
803 hxc_preference_evaluation.resource_preferences
804 (p_resource_id => l_timecard_info_rec.resource_id,
805 p_preference_code => 'TC_W_FLOW_PROCESS_NAME',
806 p_start_evaluation_date => l_timecard_info_rec.start_date,
807 p_end_evaluation_date => l_timecard_info_rec.end_date,
808 p_sorted_pref_table => l_pref_table,
809 p_master_pref_table => p_master_pref_table);
810
811 if ( l_pref_table(1).attribute1 = 'AUDIT' ) then
812 -- For AUDIT Flow:
813 -- Audit Layout Must be set
814 -- Delete is *NOT* allowed
815 -- CLA TERG must be specified
816 hxc_preference_evaluation.resource_preferences
817 (p_resource_id => l_timecard_info_rec.resource_id,
818 p_preference_code => 'TC_W_TCRD_LAYOUT',
819 p_start_evaluation_date => l_timecard_info_rec.start_date,
820 p_end_evaluation_date => l_timecard_info_rec.end_date,
821 p_sorted_pref_table => l_pref_table,
822 p_master_pref_table => p_master_pref_table );
823
824 if ( l_pref_table(1).attribute6 IS NOT NULL ) then
825 --Audit Layout is Not Null
826 hxc_preference_evaluation.resource_preferences
827 (p_resource_id => l_timecard_info_rec.resource_id,
828 p_preference_code => 'TC_W_DELETE_ALLOW',
829 p_start_evaluation_date => l_timecard_info_rec.start_date,
830 p_end_evaluation_date => l_timecard_info_rec.end_date,
831 p_sorted_pref_table => l_pref_table,
832 p_master_pref_table => p_master_pref_table );
833
834 if(l_pref_table(1).attribute1 = 'N' )then
835 hxc_preference_evaluation.resource_preferences
836 (p_resource_id => l_timecard_info_rec.resource_id,
837 p_preference_code => 'TS_PER_AUDIT_REQUIREMENTS',
838 p_start_evaluation_date => l_timecard_info_rec.start_date,
839 p_end_evaluation_date => l_timecard_info_rec.end_date,
840 p_sorted_pref_table => l_pref_table,
841 p_master_pref_table => p_master_pref_table );
842
843 l_cla_terg_id := to_number(l_pref_table(1).attribute1);
844 if ( l_cla_terg_id is not null )then
845 l_cla_prefs_ok := TRUE;
846 end if; -- Null CLA TERG ?
847 end if; -- Delete Allowed ?
848 end if; -- Null Audit Layout ?
849
850 if not l_cla_prefs_ok then
851 -- Failed CLA set up validation for AUDIT flow
852 hxc_timecard_message_helper.adderrortocollection
853 (p_messages => p_messages,
854 p_message_name => 'HXC_VLD_CLA_PREF_DFN',
855 p_message_level => 'ERROR',
856 p_message_field => NULL,
857 p_message_tokens => NULL,
858 p_application_short_name => 'HXC',
862 p_time_attribute_ovn => NULL );
859 p_time_building_block_id => l_timecard_info_rec.timecard_bb_id,
860 p_time_building_block_ovn => l_timecard_info_rec.timecard_ovn,
861 p_time_attribute_id => NULL,
863
864 end if;
865 else if ( nvl(l_pref_table(1).attribute1,'STANDARD') = 'STANDARD' ) then
866 --
867 -- Non Audit flow:
868 -- Audit layout must be null
869 -- CLA TERG must be null
870 hxc_preference_evaluation.resource_preferences
871 (p_resource_id => l_timecard_info_rec.resource_id,
872 p_preference_code => 'TC_W_TCRD_LAYOUT',
873 p_start_evaluation_date => l_timecard_info_rec.start_date,
874 p_end_evaluation_date => l_timecard_info_rec.end_date,
875 p_sorted_pref_table => l_pref_table,
876 p_master_pref_table => p_master_pref_table );
877
878 if ( l_pref_table(1).attribute6 is null ) then
879 --Audit Layout is null
880 hxc_preference_evaluation.resource_preferences
881 (p_resource_id => l_timecard_info_rec.resource_id,
882 p_preference_code => 'TS_PER_AUDIT_REQUIREMENTS',
883 p_start_evaluation_date => l_timecard_info_rec.start_date,
884 p_end_evaluation_date => l_timecard_info_rec.end_date,
885 p_sorted_pref_table => l_pref_table,
886 p_master_pref_table => p_master_pref_table );
887
888 l_cla_terg_id := to_number(l_pref_table(1).attribute1);
889 if ( l_cla_terg_id is null )then
890 l_cla_prefs_ok := TRUE;
891 end if; -- Null CLA TERG ?
892 end if; -- Null Audit Layout ?
893
894 if not l_cla_prefs_ok then
895 -- Failed CLA Setup validation for STANDARD flow.
896 hxc_timecard_message_helper.adderrortocollection
897 (p_messages => p_messages,
898 p_message_name => 'HXC_VLD_NON_CLA_PREF_DFN',
899 p_message_level => 'ERROR',
900 p_message_field => NULL,
901 p_message_tokens => NULL,
902 p_application_short_name => 'HXC',
903 p_time_building_block_id => l_timecard_info_rec.timecard_bb_id,
904 p_time_building_block_ovn => l_timecard_info_rec.timecard_ovn,
905 p_time_attribute_id => NULL,
906 p_time_attribute_ovn => NULL );
907 end if;
908 end if; -- Flow Style
909 end if; -- What is this?
910
911 -- Override Approval Validation
912
913 If (l_can_enter_override_approver = 'Y') OR (l_default_override_approver_id is not null) then
914
915 If(l_override_approval_style_id is null) then
916
917 hxc_timecard_message_helper.adderrortocollection (
918 p_messages => p_messages
919 , p_message_name => 'HXC_VLD_OAPPROVER_INCOMPLETE'
920 , p_message_level => 'ERROR'
921 , p_message_field => NULL
922 , p_message_tokens => NULL
923 , p_application_short_name => 'HXC'
924 , p_time_building_block_id => l_timecard_info_rec.timecard_bb_id
925 , p_time_building_block_ovn => l_timecard_info_rec.timecard_ovn
926 , p_time_attribute_id => NULL
927 , p_time_attribute_ovn => NULL );
928
929 End if;
930
931 End if;
932
933
934
935 EXCEPTION WHEN e_no_resource_id_error
936 THEN
937
938 hxc_timecard_message_helper.adderrortocollection (
939 p_messages => p_messages
940 , p_message_name => 'HR_6153_ALL_PROCEDURE_FAIL'
941 , p_message_level => 'ERROR'
942 , p_message_field => NULL
943 , p_message_tokens => 'PROCEDURE&'||l_proc||'&STEP&1'
944 , p_application_short_name => 'PAY'
945 , p_time_building_block_id => l_timecard_info_rec.timecard_bb_id
946 , p_time_building_block_ovn => l_timecard_info_rec.timecard_ovn
947 , p_time_attribute_id => NULL
948 , p_time_attribute_ovn => NULL );
949
950 WHEN e_no_rtr_id
951 THEN
952
953 hxc_timecard_message_helper.adderrortocollection (
954 p_messages => p_messages
955 , p_message_name => 'HR_6153_ALL_PROCEDURE_FAIL'
956 , p_message_level => 'ERROR'
957 , p_message_field => NULL
958 , p_message_tokens => 'PROCEDURE&no rtr id for rules evaluation&STEP&2'
959 , p_application_short_name => 'PAY'
960 , p_time_building_block_id => l_timecard_info_rec.timecard_bb_id
961 , p_time_building_block_ovn => l_timecard_info_rec.timecard_ovn
962 , p_time_attribute_id => NULL
963 , p_time_attribute_ovn => NULL );
964
965 WHEN OTHERS
966 THEN
967
968 -- v115.9 - SUBSTR syntax
969
970 hxc_timecard_message_helper.adderrortocollection (
971 p_messages => p_messages
972 , p_message_name => 'EXCEPTION'
973 , p_message_level => 'ERROR'
974 , p_message_field => NULL
975 , p_message_tokens => NULL
976 , p_application_short_name => 'HXC'
977 , p_time_building_block_id => l_timecard_info_rec.timecard_bb_id
978 , p_time_building_block_ovn => l_timecard_info_rec.timecard_ovn
979 , p_time_attribute_id => NULL
980 , p_time_attribute_ovn => NULL );
981
982 END execute_otc_validation;
983
984
985 end hxc_setup_validation_pkg;