[Home] [Help]
PACKAGE BODY: APPS.HXC_SETUP_VALIDATION_PKG
Source
1 Package Body hxc_setup_validation_pkg as
2 /* $Header: hxcotcvld.pkb 120.22.12020000.2 2012/07/20 06:59:58 pnshukla ship $ */
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 /*+ ORDERED
116 INDEX(hte hxc_time_entry_rules_pk)*/
117 'Y'
118 FROM hxc_entity_groups heg,
119 hxc_entity_group_comps hec,
120 hxc_time_entry_rules hte
121 WHERE heg.entity_type = 'TIME_ENTRY_RULES' and
122 heg.entity_group_id = p_elp_terg_id and
123 hec.ENTITY_GROUP_ID =heg.entity_group_id and
124 hec.entity_id = hte.TIME_ENTRY_RULE_ID and
125 hte.attribute1 = p_time_recipient_id;
126 --
127 -- Added for 115.26, gets timecard information for self
128 -- service, since we can not know it in the properties
129 -- package
130 cursor c_timecard_info
131 (p_timecard_bb_id in NUMBER,
132 p_timecard_bb_ovn in NUMBER) is
133 select approval_status
134 from hxc_timecard_summary
135 where timecard_id = p_timecard_bb_id
136 and timecard_ovn =p_timecard_bb_ovn;
137
138 l_tc_approval_status hxc_timecard_summary.approval_status%type;
139 l_test_edit boolean;
140
141 l_proc VARCHAR2(72);
142
143 l_index BINARY_INTEGER;
144 l_pref_table hxc_preference_evaluation.t_pref_table;
145
146 l_id NUMBER(15);
147 l_pref_code VARCHAR2(30);
148
149 l_rtr_grp_id NUMBER(15);
150 l_app_set_id NUMBER(15);
151 l_approval_period_set_id NUMBER(15);
152 l_approval_style_id NUMBER(15);
153 l_override_approval_style_id hxc_approval_styles.approval_style_id%type;
154 l_elp_terg_id NUMBER(15);
155 l_cla_terg_id NUMBER(15);
156 l_cla_prefs_ok BOOLEAN := FALSE;
157 l_dummy VARCHAR2(1);
158
159 -- Default override approver validation
160 l_default_override_approver_id number(15);
161 l_can_enter_override_approver hxc_pref_hierarchies.attribute1%type;
162
163 l_past_limit_date VARCHAR2(80);
164 l_futur_limit_date VARCHAR2(80);
165
166
167 l_otm_explosion VARCHAR2(1);
168 l_otm_rtr_id hxc_retrieval_rules.retrieval_rule_id%TYPE;
169
170 l_status_allowing_edits hxc_pref_hierarchies.attribute1%TYPE;
171 l_edit_allowed VARCHAR2(5);
172
173 l_rtr_tr_id hxc_time_recipients.time_recipient_id%TYPE;
174 l_aps_tr_id hxc_time_recipients.time_recipient_id%TYPE;
175
176 l_timecard_info_rec hxc_time_entry_rules_utils_pkg.r_timecard_info;
177
178 -- OTL - ABS Integration
179 l_absences_integration VARCHAR2(5);
180 l_mismatch_abs BOOLEAN := FALSE;
181 l_ind BINARY_INTEGER;
182
183 -- Added for Bug 10326049
184 -- OTL - Projects Payroll Integration
185 l_proj_pay_integration VARCHAR2(5);
186 l_mismatch_proj_pay BOOLEAN := FALSE;
187 l_proj_pay_ind BINARY_INTEGER;
188 l_pay_tr_exists NUMBER := 0;
189
190 TYPE r_time_recipient IS RECORD ( DUMMY VARCHAR2(1) );
191
192 TYPE t_time_recipient IS TABLE OF r_time_recipient INDEX BY BINARY_INTEGER;
193
194 -- table for application set and retrieval rule time recipients
195 -- note we are going to use the table index to store the time
196 -- recipient id
197
198 t_aps_tr t_time_recipient;
199 t_rtr_tr t_time_recipient;
200
201 e_no_resource_id_error exception;
202 e_no_rtr_id exception;
203
204 FUNCTION test_aps_vs_rtr (
205 p_rtr_tr t_time_recipient
206 , p_aps_tr t_time_recipient ) RETURN BOOLEAN IS
207
208 l_rtr_index BINARY_INTEGER;
209 l_return BOOLEAN := FALSE;
210
211 BEGIN
212
213 l_rtr_index := t_rtr_tr.FIRST;
214
215 WHILE ( l_rtr_index IS NOT NULL )
216 LOOP
217
218 IF NOT t_aps_tr.EXISTS(l_rtr_index)
219 THEN
220
221 l_return := TRUE;
222 EXIT;
223
224 END IF;
225
226 l_rtr_index := t_rtr_tr.NEXT(l_rtr_index);
227
228 END LOOP;
229
230 RETURN l_return;
231
232 END test_aps_vs_rtr;
233
234 BEGIN -- execute_otc_Validation
235
236 g_debug := hr_utility.debug_enabled;
237
238 if g_debug then
239 l_proc := g_package||'execute_otc_validation';
240 hr_utility.set_location('Processing '||l_proc, 10);
241 end if;
242
243 l_timecard_info_rec.resource_id := p_resource_id;
244 l_timecard_info_rec.timecard_bb_id := p_timecard_bb_id;
245 l_timecard_info_rec.timecard_ovn := p_timecard_bb_ovn;
246 l_timecard_info_rec.start_date := p_start_date;
247 l_timecard_info_rec.end_date := p_end_date;
248
249 -- now get the retrieval rule grp id and application set id
250 -- based on the resource id
251
252 hxc_preference_evaluation.resource_preferences(p_resource_id => l_timecard_info_rec.resource_id,
253 p_preference_code => 'TS_PER_APPLICATION_SET',
254 p_start_evaluation_date => l_timecard_info_rec.start_date,
255 p_end_evaluation_date => l_timecard_info_rec.end_date,
256 p_sorted_pref_table => l_pref_table,
257 p_master_pref_table => p_master_pref_table );
258
259 l_app_set_id := TO_NUMBER(l_pref_table(1).attribute1);
260 if g_debug then
261 hr_utility.trace('OTL Setup - app set id is '||to_char(l_app_set_id));
262 end if;
263
264 hxc_preference_evaluation.resource_preferences(p_resource_id => l_timecard_info_rec.resource_id,
265 p_preference_code => 'TS_PER_RETRIEVAL_RULES',
266 p_start_evaluation_date => l_timecard_info_rec.start_date,
267 p_end_evaluation_date => l_timecard_info_rec.end_date,
268 p_sorted_pref_table => l_pref_table,
269 p_master_pref_table => p_master_pref_table );
270
271 l_rtr_grp_id := TO_NUMBER(l_pref_table(1).attribute1);
272 if g_debug then
273 hr_utility.trace('OTL Setup - rtr grp id is '||to_char(l_rtr_grp_id));
274 end if;
275
276 -- OTL - ABS Integration
277
278 IF NVL(FND_PROFILE.VALUE('HR_ABS_OTL_INTEGRATION'),'N') = 'Y'
279 THEN
280 hxc_preference_evaluation.resource_preferences(p_resource_id => l_timecard_info_rec.resource_id,
281 p_preference_code => 'TS_ABS_PREFERENCES',
282 p_start_evaluation_date => l_timecard_info_rec.start_date,
283 p_end_evaluation_date => l_timecard_info_rec.end_date,
284 p_sorted_pref_table => l_pref_table,
285 p_master_pref_table => p_master_pref_table );
286
287 l_absences_integration := l_pref_table(1).attribute1;
288 if g_debug then
289 hr_utility.trace('ABS: OTL Setup - Abs Integration is '||l_absences_integration);
290 end if;
291 -- Bug 8855103
292 -- Mid period pref changes should be disabled.
293 IF l_pref_table.COUNT > 0
294 THEN
295 l_ind := l_pref_table.FIRST;
296 LOOP
297 IF l_pref_table(l_ind).attribute1 <> l_absences_integration
298 THEN
299 l_mismatch_abs := TRUE;
300 EXIT;
301 END IF;
302 l_ind := l_pref_table.NEXT(l_ind);
303 EXIT WHEN NOT l_pref_table.EXISTS(l_ind);
304 END LOOP;
305 END IF;
306 END IF;
307
308 --- Added for Bug 10326049
309 --- OTL - Projects Payroll Integration
310 IF g_debug THEN
311 hr_utility.trace('PA-PAY> l_timecard_info_rec.resource_i ::'||l_timecard_info_rec.resource_id);
312 hr_utility.trace('PA-PAY> l_timecard_info_rec.start_date ::'||l_timecard_info_rec.start_date);
313 hr_utility.trace('PA-PAY> l_timecard_info_rec.end_date ::'||l_timecard_info_rec.end_date);
314 END IF;
315
316 hxc_preference_evaluation.resource_preferences(p_resource_id => l_timecard_info_rec.resource_id,
317 p_preference_code => 'TS_PA_PAY_INTG',
318 p_start_evaluation_date => l_timecard_info_rec.start_date,
319 p_end_evaluation_date => l_timecard_info_rec.end_date,
320 p_sorted_pref_table => l_pref_table,
321 p_master_pref_table => p_master_pref_table );
322
323 l_proj_pay_integration := l_pref_table(1).attribute1;
324 if g_debug then
325 hr_utility.trace('PA-PAY> OTL Setup - Projects Payroll Integration is '||l_proj_pay_integration);
326 hr_utility.trace('PA-PAY> l_pref_table.COUNT ::'||l_pref_table.COUNT);
327 end if;
328
329 IF l_pref_table.COUNT > 0
330 THEN
331 l_proj_pay_ind := l_pref_table.FIRST;
332 LOOP
333 IF l_pref_table(l_proj_pay_ind).attribute1 <> l_proj_pay_integration
334 THEN
335 l_mismatch_proj_pay := TRUE;
336 EXIT;
337 END IF;
338 l_proj_pay_ind := l_pref_table.NEXT(l_proj_pay_ind);
339 EXIT WHEN NOT l_pref_table.EXISTS(l_proj_pay_ind);
340 END LOOP;
341 END IF;
342
343 if l_mismatch_proj_pay then
344 hr_utility.trace('PA-PAY> l_mismatch_proj_pay is true');
345 else
346 hr_utility.trace('PA-PAY> l_mismatch_proj_pay is false');
347 end if;
348
349
350 hxc_preference_evaluation.resource_preferences(p_resource_id => l_timecard_info_rec.resource_id,
351 p_preference_code => 'TC_W_RULES_EVALUATION',
352 p_start_evaluation_date => l_timecard_info_rec.start_date,
353 p_end_evaluation_date => l_timecard_info_rec.end_date,
354 p_sorted_pref_table => l_pref_table,
355 p_master_pref_table => p_master_pref_table );
356
357 l_otm_explosion := l_pref_table(1).attribute1;
358 l_otm_rtr_id := TO_NUMBER(l_pref_table(1).attribute2);
359 if g_debug then
360 hr_utility.trace('OTL Setup - otm explosion is '||l_otm_explosion);
361 end if;
362
363 hxc_preference_evaluation.resource_preferences(p_resource_id => l_timecard_info_rec.resource_id,
364 p_preference_code => 'TS_PER_APPROVAL_PERIODS',
365 p_start_evaluation_date => l_timecard_info_rec.start_date,
366 p_end_evaluation_date => l_timecard_info_rec.end_date,
367 p_sorted_pref_table => l_pref_table,
368 p_master_pref_table => p_master_pref_table );
369
370 l_approval_period_set_id := TO_NUMBER(l_pref_table(1).attribute1);
371 if g_debug then
372 hr_utility.trace('OTL Setup - approval period set id is '||to_char(l_approval_period_set_id));
373 end if;
374
375 hxc_preference_evaluation.resource_preferences(p_resource_id => l_timecard_info_rec.resource_id,
376 p_preference_code => 'TS_PER_APPROVAL_STYLE',
377 p_start_evaluation_date => l_timecard_info_rec.start_date,
378 p_end_evaluation_date => l_timecard_info_rec.end_date,
379 p_sorted_pref_table => l_pref_table,
380 p_master_pref_table => p_master_pref_table );
381
382 l_approval_style_id := TO_NUMBER(l_pref_table(1).attribute1);
383 l_override_approval_style_id := TO_NUMBER(l_pref_table(1).attribute2);
384 if g_debug then
385 hr_utility.trace('OTL Setup - approval style id is '||to_char(l_approval_style_id));
386 hr_utility.trace('OTL Setup - override approval style id is '||to_char(l_override_approval_style_id));
387 end if;
388
389 hxc_preference_evaluation.resource_preferences(p_resource_id => l_timecard_info_rec.resource_id,
390 p_preference_code => 'TC_W_TCRD_ST_ALW_EDITS',
391 p_start_evaluation_date => l_timecard_info_rec.start_date,
392 p_end_evaluation_date => l_timecard_info_rec.end_date,
393 p_sorted_pref_table => l_pref_table,
394 p_master_pref_table => p_master_pref_table );
395
396 l_status_allowing_edits := l_pref_table(1).attribute1;
397 l_past_limit_date := l_pref_table(1).attribute6;
398 l_futur_limit_date := l_pref_table(1).attribute11;
399 if g_debug then
400 hr_utility.trace('OTL Setup - status allowing edits is '||l_status_allowing_edits);
401 hr_utility.trace('OTL Setup - past limit date is '||l_past_limit_date);
402 hr_utility.trace('OTL Setup - futur limit date is '||l_futur_limit_date);
403 end if;
404
405 --ELP Validation
406
407 hxc_preference_evaluation.resource_preferences(p_resource_id => l_timecard_info_rec.resource_id,
408 p_preference_code => 'TS_PER_ELP_RULES',
409 p_start_evaluation_date => l_timecard_info_rec.start_date,
410 p_end_evaluation_date => l_timecard_info_rec.end_date,
411 p_sorted_pref_table => l_pref_table,
412 p_master_pref_table => p_master_pref_table );
413
414 l_elp_terg_id := TO_NUMBER(l_pref_table(1).attribute1);
415 if g_debug then
416 hr_utility.trace('OTL Setup- ELP TERG is ' || to_char(l_elp_terg_id));
417 end if;
418
419 -- Override Approval Validation
420
421 hxc_preference_evaluation.resource_preferences(p_resource_id => l_timecard_info_rec.resource_id,
422 p_preference_code => 'TC_W_APRVR_DFLT_OVRD',
423 p_start_evaluation_date => l_timecard_info_rec.start_date,
424 p_end_evaluation_date => l_timecard_info_rec.end_date,
425 p_sorted_pref_table => l_pref_table,
426 p_master_pref_table => p_master_pref_table );
427
428 l_default_override_approver_id := TO_NUMBER(l_pref_table(1).attribute1);
429
430 hxc_preference_evaluation.resource_preferences(p_resource_id => l_timecard_info_rec.resource_id,
431 p_preference_code => 'TC_W_APRVR_ENBLE_OVRD',
432 p_start_evaluation_date => l_timecard_info_rec.start_date,
433 p_end_evaluation_date => l_timecard_info_rec.end_date,
434 p_sorted_pref_table => l_pref_table,
435 p_master_pref_table => p_master_pref_table );
436
437 l_can_enter_override_approver := l_pref_table(1).attribute1;
438
439 hxc_preference_evaluation.clear_sort_pref_table_cache;
440
441
442 IF l_past_limit_date is null THEN
443 l_past_limit_date := '0001/01/01';
444 ELSE
445 l_past_limit_date := to_char((sysdate - to_number(l_past_limit_date)),'YYYY/MM/DD');
446 END IF;
447
448 IF l_futur_limit_date is null THEN
449 l_futur_limit_date := '4712/12/31';
450 ELSE
451 l_futur_limit_date := to_char((sysdate + to_number(l_futur_limit_date)),'YYYY/MM/DD');
452 END IF;
453
454 if g_debug then
455 hr_utility.trace('OTL Setup - past limit date is '||l_past_limit_date);
456 hr_utility.trace('OTL Setup - futur limit date is '||l_futur_limit_date);
457 end if;
458
459 if g_debug then
460 hr_utility.set_location('Processing '||l_proc, 20);
461 end if;
462
463 -- Bug 12310566
464 -- OTL - Projects Payroll Integration
465
466 IF (NVL(l_proj_pay_integration,'N') = 'Y') THEN
467
468 OPEN csr_get_app_sets ( l_app_set_id );
469 LOOP
470 FETCH csr_get_app_sets INTO l_aps_tr_id;
471 EXIT WHEN csr_get_app_sets%NOTFOUND OR l_pay_tr_exists > 0;
472
473 SELECT COUNT(1)
474 INTO l_pay_tr_exists
475 FROM hxc_time_recipients
476 WHERE time_recipient_id = l_aps_tr_id
477 AND application_id = 801; -- Payroll
478
479 END LOOP;
480 CLOSE csr_get_app_sets;
481
482 -- Raise an error when application set does not contain Payroll application.
483 IF l_pay_tr_exists = 0 THEN
484
485 hxc_timecard_message_helper.adderrortocollection (
486 p_messages => p_messages
487 , p_message_name => 'HXC_PROJ_PAY_VLD_APS'
488 , p_message_level => 'ERROR'
489 , p_message_field => NULL
490 , p_message_tokens => NULL
491 , p_application_short_name => 'HXC'
492 , p_time_building_block_id => l_timecard_info_rec.timecard_bb_id
493 , p_time_building_block_ovn => l_timecard_info_rec.timecard_ovn
494 , p_time_attribute_id => NULL
495 , p_time_attribute_ovn => NULL );
496
497 END IF;
498
499 -- Raise an error when absence integration is enabled.
500 IF (NVL(FND_PROFILE.VALUE('HR_ABS_OTL_INTEGRATION'),'N') = 'Y') AND
501 (NVL(l_absences_integration,'N') = 'Y') THEN
502
503 hxc_timecard_message_helper.adderrortocollection (
504 p_messages => p_messages
505 , p_message_name => 'HXC_PROJ_PAY_NO_ABS_INTG'
506 , p_message_level => 'ERROR'
507 , p_message_field => NULL
508 , p_message_tokens => NULL
509 , p_application_short_name => 'HXC'
510 , p_time_building_block_id => l_timecard_info_rec.timecard_bb_id
511 , p_time_building_block_ovn => l_timecard_info_rec.timecard_ovn
512 , p_time_attribute_id => NULL
513 , p_time_attribute_ovn => NULL );
514
515 END IF;
516 -- Bug 11940252
517 -- Raise an error when ELP is enabled.
518 IF l_elp_terg_id is not null THEN
519
520 hxc_timecard_message_helper.adderrortocollection (
521 p_messages => p_messages
522 , p_message_name => 'HXC_PROJ_PAY_NO_ELP'
523 , p_message_level => 'ERROR'
524 , p_message_field => NULL
525 , p_message_tokens => NULL
526 , p_application_short_name => 'HXC'
527 , p_time_building_block_id => l_timecard_info_rec.timecard_bb_id
528 , p_time_building_block_ovn => l_timecard_info_rec.timecard_ovn
529 , p_time_attribute_id => NULL
530 , p_time_attribute_ovn => NULL );
531
532
533 END IF;
534
535 END IF;
536
537
538
539 --
540
541 -- now get the application set time recipients and retrieval rule group time recipients
542 -- and make sure they match.
543
544 OPEN csr_get_retrieval_rules ( l_rtr_grp_id );
545 FETCH csr_get_retrieval_rules INTO l_rtr_tr_id;
546
547 WHILE csr_get_retrieval_rules%FOUND
548 LOOP
549 if g_debug then
550 hr_utility.set_location('Processing '||l_proc, 25);
551 end if;
552
553 t_rtr_tr(l_rtr_tr_id).dummy := 'N';
554
555 FETCH csr_get_retrieval_rules INTO l_rtr_tr_id;
556
557 END LOOP;
558
559 CLOSE csr_get_retrieval_rules;
560
561 if g_debug then
562 hr_utility.set_location('Processing '||l_proc, 30);
563 end if;
564
565 OPEN csr_get_app_sets ( l_app_set_id );
566 FETCH csr_get_app_sets INTO l_aps_tr_id;
567
568 WHILE csr_get_app_sets%FOUND
569 LOOP
570 if g_debug then
571 hr_utility.set_location('Processing '||l_proc, 35);
572 end if;
573
574 t_aps_tr(l_aps_tr_id).dummy := 'N';
575
576 FETCH csr_get_app_sets INTO l_aps_tr_id;
577
578 END LOOP;
579
580 CLOSE csr_get_app_sets;
581
582 if g_debug then
583 hr_utility.set_location('Processing '||l_proc, 40);
584 end if;
585
586 -- now test to see if the retrieval rule group time recipients
587 -- is at least a subset of the application set time recipients
588
589 IF ( test_aps_vs_rtr ( p_rtr_tr => t_rtr_tr
590 , p_aps_tr => t_aps_tr ) )
591 THEN
592
593 hxc_timecard_message_helper.adderrortocollection (
594 p_messages => p_messages
595 , p_message_name => 'HXC_VLD_APS_VS_RTR_GRP'
596 , p_message_level => 'ERROR'
597 , p_message_field => NULL
598 , p_message_tokens => NULL
599 , p_application_short_name => 'HXC'
600 , p_time_building_block_id => l_timecard_info_rec.timecard_bb_id
601 , p_time_building_block_ovn => l_timecard_info_rec.timecard_ovn
602 , p_time_attribute_id => NULL
603 , p_time_attribute_ovn => NULL );
604
605 END IF;
606
607 if g_debug then
608 hr_utility.set_location('Processing '||l_proc, 50);
609 end if;
610
611 -- now get the time recipients associated with the
612 -- OTM Evaluation rtr if the person has their
613 -- apply schedule rule prefernce set
614
615 IF ( l_otm_explosion = 'Y' )
616 THEN
617 if g_debug then
618 hr_utility.set_location('Processing '||l_proc, 55);
619 end if;
620
621 IF ( l_otm_rtr_id IS NULL )
622 THEN
623 if g_debug then
624 hr_utility.set_location('Processing '||l_proc, 60);
625 end if;
626
627 raise e_no_rtr_id;
628
629 END IF;
630
631 if g_debug then
632 hr_utility.set_location('Processing '||l_proc, 70);
633 end if;
634
635 t_rtr_tr.DELETE;
636
637 OPEN csr_get_rtr ( l_otm_rtr_id );
638 FETCH csr_get_rtr INTO l_rtr_tr_id;
639
640 WHILE csr_get_rtr%FOUND
641 LOOP
642 if g_debug then
643 hr_utility.set_location('Processing '||l_proc, 80);
644 end if;
645
646 t_rtr_tr(l_rtr_tr_id).dummy := 'N';
647
648 FETCH csr_get_rtr INTO l_rtr_tr_id;
649
650 END LOOP;
651
652 CLOSE csr_get_rtr;
653
654 if g_debug then
655 hr_utility.set_location('Processing '||l_proc, 90);
656 end if;
657
658 -- now test to see if the retrieval rule group time recipients
659 -- is at least a subset of the application set time recipients
660
661 IF ( test_aps_vs_rtr ( p_rtr_tr => t_rtr_tr
662 , p_aps_tr => t_aps_tr ) )
663 THEN
664
665 hxc_timecard_message_helper.adderrortocollection (
666 p_messages => p_messages
667 , p_message_name => 'HXC_VLD_APS_VS_RTR_GRP'
668 , p_message_level => 'ERROR'
669 , p_message_field => NULL
670 , p_message_tokens => NULL
671 , p_application_short_name => 'HXC'
672 , p_time_building_block_id => l_timecard_info_rec.timecard_bb_id
673 , p_time_building_block_ovn => l_timecard_info_rec.timecard_ovn
674 , p_time_attribute_id => NULL
675 , p_time_attribute_ovn => NULL );
676
677 END IF;
678
679
680 if g_debug then
681 hr_utility.set_location('Processing '||l_proc, 100);
682 end if;
683
684 END IF; -- IF ( l_otm_explosion = 'Y' ) GPM v115.19
685
686 if g_debug then
687 hr_utility.set_location('Processing '||l_proc, 130);
688 end if;
689
690 -- test that the applications in the application set have a corresponding
691 -- period in the Approval Periods Group
692
693 l_index := t_aps_tr.FIRST;
694
695 WHILE ( l_index IS NOT NULL )
696 LOOP
697
698 if g_debug then
699 hr_utility.trace('Here is the time recipient '||to_char(l_index));
700
701 hr_utility.set_location('Processing '||l_proc, 140);
702 end if;
703
704 OPEN csr_get_app_rec_period(l_index, l_approval_period_set_id);
705 FETCH csr_get_app_rec_period into l_dummy;
706
707 IF csr_get_app_rec_period%NOTFOUND
708 THEN
709 if g_debug then
710 hr_utility.set_location('Processing '||l_proc, 150);
711 end if;
712
713 hxc_timecard_message_helper.adderrortocollection (
714 p_messages => p_messages
715 , p_message_name => 'HXC_APR_NO_REC_PERIOD'
716 , p_message_level => 'ERROR'
717 , p_message_field => NULL
718 , p_message_tokens => NULL
719 , p_application_short_name => 'HXC'
720 , p_time_building_block_id => l_timecard_info_rec.timecard_bb_id
721 , p_time_building_block_ovn => l_timecard_info_rec.timecard_ovn
722 , p_time_attribute_id => NULL
723 , p_time_attribute_ovn => NULL );
724
725 CLOSE csr_get_app_rec_period;
726
727 EXIT;
728
729 END IF;
730
731 CLOSE csr_get_app_rec_period;
732
733 if g_debug then
734 hr_utility.set_location('Processing '||l_proc, 160);
735 end if;
736
737 l_index := t_aps_tr.NEXT(l_index);
738
739 END LOOP; -- t_aps_tr
740
741 if g_debug then
742 hr_utility.set_location('Processing '||l_proc, 170);
743 end if;
744
745
746 -- tests that the time recipients in the application set have
747 -- corresponding entries in the approval style components
748
749 l_index := t_aps_tr.FIRST;
750
751 WHILE ( l_index IS NOT NULL )
752 LOOP
753
754 if g_debug then
755 hr_utility.set_location('Processing '||l_proc, 180);
756 end if;
757
758 OPEN csr_get_approval_style_comps(l_index, l_approval_style_id);
759 FETCH csr_get_approval_style_comps into l_dummy;
760
761 IF csr_get_approval_style_comps%NOTFOUND
762 THEN
763 if g_debug then
764 hr_utility.set_location('Processing '||l_proc, 190);
765 end if;
766
767 hxc_timecard_message_helper.adderrortocollection (
768 p_messages => p_messages
769 , p_message_name => 'HXC_APR_NO_APP_STYLE_COMP'
770 , p_message_level => 'ERROR'
771 , p_message_field => NULL
772 , p_message_tokens => NULL
773 , p_application_short_name => 'HXC'
774 , p_time_building_block_id => l_timecard_info_rec.timecard_bb_id
775 , p_time_building_block_ovn => l_timecard_info_rec.timecard_ovn
776 , p_time_attribute_id => NULL
777 , p_time_attribute_ovn => NULL );
778
779 CLOSE csr_get_approval_style_comps;
780
781 EXIT;
782
783 END IF;
784
785 CLOSE csr_get_approval_style_comps;
786
787 if g_debug then
788 hr_utility.set_location('Processing '||l_proc, 200);
789 end if;
790
791 l_index := t_aps_tr.NEXT(l_index);
792
793 END LOOP; -- t_aps_tr
794 --
795 -- tests that the time recipients in the application set have
796 -- corresponding entries in the override approval style components,
797 -- if it is set
798 --
799 if(l_override_approval_style_id is not null)then
800 l_index := t_aps_tr.FIRST;
801 WHILE ( l_index IS NOT NULL ) LOOP
802
803 if g_debug then
804 hr_utility.set_location('Processing '||l_proc, 201);
805 end if;
806 OPEN csr_get_approval_style_comps(l_index, l_override_approval_style_id);
807 FETCH csr_get_approval_style_comps into l_dummy;
808
809 IF csr_get_approval_style_comps%NOTFOUND THEN
810 if g_debug then
811 hr_utility.set_location('Processing '||l_proc, 202);
812 end if;
813
814 hxc_timecard_message_helper.adderrortocollection
815 (p_messages => p_messages,
816 p_message_name => 'HXC_APR_NO_APP_STYLE_COMP',
817 p_message_level => 'ERROR' ,
818 p_message_field => NULL,
819 p_message_tokens => NULL,
820 p_application_short_name => 'HXC',
821 p_time_building_block_id => l_timecard_info_rec.timecard_bb_id,
822 p_time_building_block_ovn => l_timecard_info_rec.timecard_ovn,
823 p_time_attribute_id => NULL,
824 p_time_attribute_ovn => NULL
825 );
826
827 CLOSE csr_get_approval_style_comps;
828
829 EXIT;
830
831 END IF;
832
833 CLOSE csr_get_approval_style_comps;
834
835 if g_debug then
836 hr_utility.set_location('Processing '||l_proc, 203);
837 end if;
838
839 l_index := t_aps_tr.NEXT(l_index);
840
841 END LOOP; -- t_aps_tr
842 end if; -- Is the Override Style set?
843
844 if g_debug then
845 hr_utility.set_location('Processing '||l_proc, 210);
846 end if;
847
848 --ELP Validation
849 -- tests that the time recipients in the application set have
850 -- corresponding entries in the ELP TERG components
851
852 if (l_elp_terg_id is not null) then
853 l_index := t_aps_tr.FIRST;
854
855 WHILE ( l_index IS NOT NULL )
856 LOOP
857
858 if g_debug then
859 hr_utility.set_location('Processing '||l_proc, 211);
860 end if;
861
862 OPEN csr_get_elp_terg_comps(l_elp_terg_id,l_index);
863 FETCH csr_get_elp_terg_comps into l_dummy;
864
865 IF csr_get_elp_terg_comps%NOTFOUND
866 THEN
867 if g_debug then
868 hr_utility.set_location('Processing '||l_proc, 212);
869 end if;
870
871 hxc_timecard_message_helper.adderrortocollection (
872 p_messages => p_messages
873 , p_message_name => 'HXC_VLD_ELP_VIOLATION'
874 , p_message_level => 'ERROR'
875 , p_message_field => NULL
876 , p_message_tokens => NULL
877 , p_application_short_name => 'HXC'
878 , p_time_building_block_id => l_timecard_info_rec.timecard_bb_id
879 , p_time_building_block_ovn => l_timecard_info_rec.timecard_ovn
880 , p_time_attribute_id => NULL
881 , p_time_attribute_ovn => NULL );
882
883 CLOSE csr_get_elp_terg_comps;
884
885 EXIT;
886
887 END IF;
888
889 CLOSE csr_get_elp_terg_comps;
890
891 if g_debug then
892 hr_utility.set_location('Processing '||l_proc, 213);
893 end if;
894
895 l_index := t_aps_tr.NEXT(l_index);
896
897 END LOOP; -- t_aps_tr
898
899 END IF;
900 if g_debug then
901 hr_utility.set_location('Processing '||l_proc, 214);
902 end if;
903
904 -- Now let's check to see if the user is still allowed to update the timecard
905 -- WWB 2290884
906 -- GPM v115.11
907 -- ARR v115.26 - 4561454
908 --Bug 4733480
909 if(l_timecard_info_rec.timecard_bb_id is null) then
910 l_test_edit := false;
911 else
912 open c_timecard_info(l_timecard_info_rec.timecard_bb_id,l_timecard_info_rec.timecard_ovn);
913 fetch c_timecard_info into l_tc_approval_status;
914 if(c_timecard_info%found) then -- condition added to allow edit check only for timecard and not for templates
915 l_test_edit := true;
916 else
917 l_test_edit := false;
918 l_tc_approval_status := null;
919 end if;
920 close c_timecard_info;
921 end if;
922
923 if l_test_edit then
924
925 l_edit_allowed := NULL;
926
927 IF (to_char(l_timecard_info_rec.start_date,'YYYY/MM/DD') <= l_futur_limit_date and
928 to_char(l_timecard_info_rec.end_date,'YYYY/MM/DD') >= l_past_limit_date )
929 THEN
930 hxc_time_entry_rules_utils_pkg.tc_edit_allowed
931 (p_timecard_id => l_timecard_info_rec.timecard_bb_id
932 ,p_timecard_ovn => l_timecard_info_rec.timecard_ovn
933 ,p_timecard_status => l_tc_approval_status
934 ,p_edit_allowed_preference => l_status_allowing_edits
935 ,p_edit_allowed => l_edit_allowed
936 );
937 ELSE
938 l_edit_allowed := 'FALSE';
939 END IF;
940
941 IF ( l_edit_allowed = 'FALSE' )
942 THEN
943 --
944 -- Make sure this is raised at page level, i.e.
945 -- pass null for the timecard id and ovn.
946 --
947 hxc_timecard_message_helper.adderrortocollection (
948 p_messages => p_messages
949 , p_message_name => 'HXC_VLD_TC_STATUS_CHANGED'
950 , p_message_level => 'ERROR'
951 , p_message_field => NULL
952 , p_message_tokens => NULL
953 , p_application_short_name => 'HXC'
954 , p_time_building_block_id => NULL
955 , p_time_building_block_ovn => NULL
956 , p_time_attribute_id => NULL
957 , p_time_attribute_ovn => NULL );
958
959 END IF;
960
961 END IF; -- Test edit
962 -- +---------------------------------------------------------------------------+
963 -- | |
964 -- | Change Late Audit Set Up Validation |
965 -- | |
966 -- +---------------------------------------------------------------------------+
967
968 l_cla_prefs_ok := FALSE;
969 hxc_preference_evaluation.resource_preferences
970 (p_resource_id => l_timecard_info_rec.resource_id,
971 p_preference_code => 'TC_W_FLOW_PROCESS_NAME',
972 p_start_evaluation_date => l_timecard_info_rec.start_date,
973 p_end_evaluation_date => l_timecard_info_rec.end_date,
974 p_sorted_pref_table => l_pref_table,
975 p_master_pref_table => p_master_pref_table);
976
977 if ( l_pref_table(1).attribute1 = 'AUDIT' ) then
978 -- For AUDIT Flow:
979 -- Audit Layout Must be set
980 -- Delete is *NOT* allowed
981 -- CLA TERG must be specified
982 hxc_preference_evaluation.resource_preferences
983 (p_resource_id => l_timecard_info_rec.resource_id,
984 p_preference_code => 'TC_W_TCRD_LAYOUT',
985 p_start_evaluation_date => l_timecard_info_rec.start_date,
986 p_end_evaluation_date => l_timecard_info_rec.end_date,
987 p_sorted_pref_table => l_pref_table,
988 p_master_pref_table => p_master_pref_table );
989
990 if ( l_pref_table(1).attribute6 IS NOT NULL ) then
991 --Audit Layout is Not Null
992 hxc_preference_evaluation.resource_preferences
993 (p_resource_id => l_timecard_info_rec.resource_id,
994 p_preference_code => 'TC_W_DELETE_ALLOW',
995 p_start_evaluation_date => l_timecard_info_rec.start_date,
996 p_end_evaluation_date => l_timecard_info_rec.end_date,
997 p_sorted_pref_table => l_pref_table,
998 p_master_pref_table => p_master_pref_table );
999
1000 if(l_pref_table(1).attribute1 = 'N' )then
1001 hxc_preference_evaluation.resource_preferences
1002 (p_resource_id => l_timecard_info_rec.resource_id,
1003 p_preference_code => 'TS_PER_AUDIT_REQUIREMENTS',
1004 p_start_evaluation_date => l_timecard_info_rec.start_date,
1005 p_end_evaluation_date => l_timecard_info_rec.end_date,
1006 p_sorted_pref_table => l_pref_table,
1007 p_master_pref_table => p_master_pref_table );
1008
1009 l_cla_terg_id := to_number(l_pref_table(1).attribute1);
1010 if ( l_cla_terg_id is not null )then
1011 l_cla_prefs_ok := TRUE;
1012 end if; -- Null CLA TERG ?
1013 end if; -- Delete Allowed ?
1014 end if; -- Null Audit Layout ?
1015
1016 if not l_cla_prefs_ok then
1017 -- Failed CLA set up validation for AUDIT flow
1018 hxc_timecard_message_helper.adderrortocollection
1019 (p_messages => p_messages,
1020 p_message_name => 'HXC_VLD_CLA_PREF_DFN',
1021 p_message_level => 'ERROR',
1022 p_message_field => NULL,
1023 p_message_tokens => NULL,
1024 p_application_short_name => 'HXC',
1025 p_time_building_block_id => l_timecard_info_rec.timecard_bb_id,
1026 p_time_building_block_ovn => l_timecard_info_rec.timecard_ovn,
1027 p_time_attribute_id => NULL,
1028 p_time_attribute_ovn => NULL );
1029
1030 end if;
1031 else if ( nvl(l_pref_table(1).attribute1,'STANDARD') = 'STANDARD' ) then
1032 --
1033 -- Non Audit flow:
1034 -- Audit layout must be null
1035 -- CLA TERG must be null
1036 hxc_preference_evaluation.resource_preferences
1037 (p_resource_id => l_timecard_info_rec.resource_id,
1038 p_preference_code => 'TC_W_TCRD_LAYOUT',
1039 p_start_evaluation_date => l_timecard_info_rec.start_date,
1040 p_end_evaluation_date => l_timecard_info_rec.end_date,
1041 p_sorted_pref_table => l_pref_table,
1042 p_master_pref_table => p_master_pref_table );
1043
1044 if ( l_pref_table(1).attribute6 is null ) then
1045 --Audit Layout is null
1046 hxc_preference_evaluation.resource_preferences
1047 (p_resource_id => l_timecard_info_rec.resource_id,
1048 p_preference_code => 'TS_PER_AUDIT_REQUIREMENTS',
1049 p_start_evaluation_date => l_timecard_info_rec.start_date,
1050 p_end_evaluation_date => l_timecard_info_rec.end_date,
1051 p_sorted_pref_table => l_pref_table,
1052 p_master_pref_table => p_master_pref_table );
1053
1054 l_cla_terg_id := to_number(l_pref_table(1).attribute1);
1055 if ( l_cla_terg_id is null )then
1056 l_cla_prefs_ok := TRUE;
1057 end if; -- Null CLA TERG ?
1058 end if; -- Null Audit Layout ?
1059
1060 if not l_cla_prefs_ok then
1061 -- Failed CLA Setup validation for STANDARD flow.
1062 hxc_timecard_message_helper.adderrortocollection
1063 (p_messages => p_messages,
1064 p_message_name => 'HXC_VLD_NON_CLA_PREF_DFN',
1065 p_message_level => 'ERROR',
1066 p_message_field => NULL,
1067 p_message_tokens => NULL,
1068 p_application_short_name => 'HXC',
1069 p_time_building_block_id => l_timecard_info_rec.timecard_bb_id,
1070 p_time_building_block_ovn => l_timecard_info_rec.timecard_ovn,
1071 p_time_attribute_id => NULL,
1072 p_time_attribute_ovn => NULL );
1073 end if;
1074 end if; -- Flow Style
1075 end if; -- What is this?
1076
1077 -- Override Approval Validation
1078
1079 If (l_can_enter_override_approver = 'Y') OR (l_default_override_approver_id is not null) then
1080
1081 If(l_override_approval_style_id is null) then
1082
1083 hxc_timecard_message_helper.adderrortocollection (
1084 p_messages => p_messages
1085 , p_message_name => 'HXC_VLD_OAPPROVER_INCOMPLETE'
1086 , p_message_level => 'ERROR'
1087 , p_message_field => NULL
1088 , p_message_tokens => NULL
1089 , p_application_short_name => 'HXC'
1090 , p_time_building_block_id => l_timecard_info_rec.timecard_bb_id
1091 , p_time_building_block_ovn => l_timecard_info_rec.timecard_ovn
1092 , p_time_attribute_id => NULL
1093 , p_time_attribute_ovn => NULL );
1094
1095 End if;
1096
1097 End if;
1098
1099 -- OTL - ABS Integration
1100 If (NVL(l_absences_integration,'N') = 'Y') AND
1101 (l_otm_explosion = 'Y') then
1102
1103 hxc_timecard_message_helper.adderrortocollection (
1104 p_messages => p_messages
1105 , p_message_name => 'HXC_ABS_NO_OTLR'
1106 , p_message_level => 'ERROR'
1107 , p_message_field => NULL
1108 , p_message_tokens => NULL
1109 , p_application_short_name => 'HXC'
1110 , p_time_building_block_id => l_timecard_info_rec.timecard_bb_id
1111 , p_time_building_block_ovn => l_timecard_info_rec.timecard_ovn
1112 , p_time_attribute_id => NULL
1113 , p_time_attribute_ovn => NULL );
1114
1115
1116 End if;
1117
1118 -- Bug 8855103
1119 -- Added the below error message for mid period pref changes for ABS.
1120 IF l_mismatch_abs -- Bug 12429117
1121 THEN
1122 hxc_timecard_message_helper.adderrortocollection (
1123 p_messages => p_messages
1124 , p_message_name => 'HXC_ABS_MID_PERIOD_PREF'
1125 , p_message_level => 'ERROR'
1126 , p_message_field => NULL
1127 , p_message_tokens => NULL
1128 , p_application_short_name => 'HXC'
1129 , p_time_building_block_id => l_timecard_info_rec.timecard_bb_id
1130 , p_time_building_block_ovn => l_timecard_info_rec.timecard_ovn
1131 , p_time_attribute_id => NULL
1132 , p_time_attribute_ovn => NULL );
1133 END IF;
1134
1135 -- Added for Bug 10326049
1136 -- OTL - Projects Payroll Integration
1137
1138 If (NVL(l_proj_pay_integration,'N') = 'Y') AND
1139 (l_otm_explosion = 'Y') then
1140
1141 hxc_timecard_message_helper.adderrortocollection (
1142 p_messages => p_messages
1143 , p_message_name => 'HXC_PROJ_PAY_NO_OTLR'
1144 , p_message_level => 'ERROR'
1145 , p_message_field => NULL
1146 , p_message_tokens => NULL
1147 , p_application_short_name => 'HXC'
1148 , p_time_building_block_id => l_timecard_info_rec.timecard_bb_id
1149 , p_time_building_block_ovn => l_timecard_info_rec.timecard_ovn
1150 , p_time_attribute_id => NULL
1151 , p_time_attribute_ovn => NULL );
1152
1153
1154 End if;
1155
1156 --- Added for Bug 10326049
1157 --- OTL - Projects Payroll Integration
1158 IF l_mismatch_proj_pay -- Bug 12429117
1159 THEN
1160 hxc_timecard_message_helper.adderrortocollection (
1161 p_messages => p_messages
1162 , p_message_name => 'HXC_PROJ_PAY_MID_PERIOD_PREF'
1163 , p_message_level => 'ERROR'
1164 , p_message_field => NULL
1165 , p_message_tokens => NULL
1166 , p_application_short_name => 'HXC'
1167 , p_time_building_block_id => l_timecard_info_rec.timecard_bb_id
1168 , p_time_building_block_ovn => l_timecard_info_rec.timecard_ovn
1169 , p_time_attribute_id => NULL
1170 , p_time_attribute_ovn => NULL );
1171 END IF;
1172
1173
1174
1175
1176 EXCEPTION WHEN e_no_resource_id_error
1177 THEN
1178
1179 hxc_timecard_message_helper.adderrortocollection (
1180 p_messages => p_messages
1181 , p_message_name => 'HR_6153_ALL_PROCEDURE_FAIL'
1182 , p_message_level => 'ERROR'
1183 , p_message_field => NULL
1184 , p_message_tokens => 'PROCEDURE&'||l_proc||'&STEP&1'
1185 , p_application_short_name => 'PAY'
1186 , p_time_building_block_id => l_timecard_info_rec.timecard_bb_id
1187 , p_time_building_block_ovn => l_timecard_info_rec.timecard_ovn
1188 , p_time_attribute_id => NULL
1189 , p_time_attribute_ovn => NULL );
1190
1191 WHEN e_no_rtr_id
1192 THEN
1193
1194 hxc_timecard_message_helper.adderrortocollection (
1195 p_messages => p_messages
1196 , p_message_name => 'HR_6153_ALL_PROCEDURE_FAIL'
1197 , p_message_level => 'ERROR'
1198 , p_message_field => NULL
1199 , p_message_tokens => 'PROCEDURE&no rtr id for rules evaluation&STEP&2'
1200 , p_application_short_name => 'PAY'
1201 , p_time_building_block_id => l_timecard_info_rec.timecard_bb_id
1202 , p_time_building_block_ovn => l_timecard_info_rec.timecard_ovn
1203 , p_time_attribute_id => NULL
1204 , p_time_attribute_ovn => NULL );
1205
1206 WHEN OTHERS
1207 THEN
1208
1209 -- v115.9 - SUBSTR syntax
1210
1211 hxc_timecard_message_helper.adderrortocollection (
1212 p_messages => p_messages
1213 , p_message_name => 'EXCEPTION'
1214 , p_message_level => 'ERROR'
1215 , p_message_field => NULL
1216 , p_message_tokens => NULL
1217 , p_application_short_name => 'HXC'
1218 , p_time_building_block_id => l_timecard_info_rec.timecard_bb_id
1219 , p_time_building_block_ovn => l_timecard_info_rec.timecard_ovn
1220 , p_time_attribute_id => NULL
1221 , p_time_attribute_ovn => NULL );
1222
1223
1224 END execute_otc_validation;
1225
1226
1227 end hxc_setup_validation_pkg;