[Home] [Help]
PACKAGE BODY: APPS.HXT_GEN_AAI
Source
1 PACKAGE BODY HXT_GEN_AAI AS
2 /* $Header: hxtgenai.pkb 120.3.12010000.4 2009/09/29 11:26:09 asrajago ship $ */
3
4 -------------------------------------------------------------------------------
5
6 g_debug boolean := hr_utility.debug_enabled;
7 PROCEDURE AAI_VALIDATIONS
8 (p_assignment_id NUMBER
9 ,p_earning_policy NUMBER
10 ,p_rotation_plan NUMBER
11 ,p_shift_differential_policy NUMBER
12 ,p_hour_deduction_policy NUMBER
13 ,p_autogen_hours_yn VARCHAR2
14 ,p_effective_start_date DATE
15 ) IS
16
17 asg_bus_grp_id NUMBER(15);
18
19 /* Cursor to check for the rotation plan */
20 CURSOR chk_rot_plan IS
21 SELECT 'compatible'
22 FROM hxt_rotation_plans rot
23 WHERE p_effective_start_date between rot.date_from
24 and nvl(rot.date_to, p_effective_start_date)
25 AND NOT EXISTS (SELECT '1'
26 FROM hxt_rotation_schedules hrs
27 ,hxt_weekly_work_schedules wws
28 WHERE hrs.rtp_id = rot.id
29 AND wws.id = hrs.tws_id
30 AND wws.business_group_id <> nvl(asg_bus_grp_id, -99))
31 AND rot.id = p_rotation_plan;
32
33 l_rtp_compatible varchar2(15) := NULL;
34
35 /* Cursor to check whether Earning Policy is compatible with the Assignment */
36 CURSOR chk_earning_policy IS
37 SELECT 'compatible'
38 FROM hxt_earning_policies erp
39 WHERE p_effective_start_date between erp.effective_start_date
40 and erp.effective_end_date
41 AND erp.business_group_id = nvl(asg_bus_grp_id, -99)
42 AND EXISTS
43 (select 1
44 from hxt_pay_element_types_f_ddf_v v
45 , pay_element_types_f t
46 , hxt_earning_rules r
47 where r.egp_id = erp.id
48 and p_effective_start_date between r.effective_start_date
52 and t.effective_end_date
49 and r.effective_end_date
50 and t.element_type_id = r.element_type_id
51 and p_effective_start_date between t.effective_start_date
53 and asg_bus_grp_id
54 = nvl(t.business_group_id,asg_bus_grp_id)
55 and v.element_type_id = t.element_type_id
56 and p_effective_start_date between v.effective_start_date
57 and v.effective_end_date
58 and v.hxt_earning_category in ('ABS', 'OVT', 'REG'))
59 AND NOT EXISTS
60 (select 1
61 from hxt_earning_rules er
62 where er.egp_id = erp.id
63 and p_effective_start_date between er.effective_start_date
64 and er.effective_end_date
65 and er.element_type_id NOT IN
66 (select ern.element_type_id
67 from pay_element_links_f ell
68 , per_assignments_f asm
69 , hxt_earning_rules ern
70 where ern.egp_id = erp.id
71 and p_effective_start_date between ern.effective_start_date
72 and ern.effective_end_date
73 and asm.assignment_id = p_assignment_id
74 and p_effective_start_date between asm.effective_start_date
75 and asm.effective_end_date
76 and ell.element_type_id = ern.element_type_id
77 and p_effective_start_date between ell.effective_start_date
78 and ell.effective_end_date
79 and nvl(ell.organization_id, nvl(asm.organization_id,-1))
80 = nvl(asm.organization_id,-1)
81 and (ell.people_group_id is null
82 or exists
83 (select 1
84 from pay_assignment_link_usages_f usage
85 where usage.assignment_id = asm.assignment_id
86 and usage.element_link_id = ell.element_link_id
87 and p_effective_start_date
88 between usage.effective_start_date
89 and usage.effective_end_date))
90 and nvl(ell.job_id,nvl(asm.job_id,-1))
91 = nvl(asm.job_id,-1)
92 and nvl(ell.position_id,nvl(asm.position_id,-1))
93 = nvl(asm.position_id,-1)
94 and nvl(ell.grade_id,nvl(asm.grade_id,-1))
95 = nvl(asm.grade_id,-1)
96 and nvl(ell.location_id,nvl(asm.location_id,-1))
97 = nvl(asm.location_id,-1)
98 and nvl(ell.payroll_id,nvl(asm.payroll_id,-1))
99 = nvl(asm.payroll_id,-1)
100 and nvl(ell.employment_category,nvl(asm.employment_category,-1)) = nvl(asm.employment_category,-1)
101 and nvl(ell.pay_basis_id,nvl(asm.pay_basis_id,-1)) = nvl(asm.pay_basis_id,-1)
102 and nvl(ell.business_group_id,nvl(asm.business_group_id,-1)) = nvl(asm.business_group_id,-1)))
103 AND erp.id = p_earning_policy;
104
105 l_egp_compatible VARCHAR2(15) := NULL;
106
107 /* Check whether the Shift Differentail Policy and Assignment are compatible */
108 CURSOR chk_shift_diff_policy IS
109 SELECT 'compatible'
110 FROM hxt_shift_diff_policies sdp
111 WHERE p_effective_start_date between sdp.date_from
112 and nvl(sdp.date_to
113 ,p_effective_start_date)
114 AND EXISTS
115 (select 1
116 from hxt_pay_element_types_f_ddf_v v
117 ,pay_element_types_f t
118 ,hxt_shift_diff_rules r
119 where r.sdp_id = sdp.id
120 and p_effective_start_date between r.effective_start_date
121 and r.effective_end_date
122 and t.element_type_id = r.element_type_id
123 and p_effective_start_date between t.effective_start_date
124 and t.effective_end_date
125 and asg_bus_grp_id = t.business_group_id
126 and v.element_type_id = t.element_type_id
127 and p_effective_start_date between v.effective_start_date
128 and v.effective_end_date
129 and v.hxt_earning_category = 'SDF')
130 AND NOT EXISTS
131 (select 1
132 from pay_element_types_f pet
133 , hxt_shift_diff_rules hsdr
134 where hsdr.sdp_id = sdp.id
135 and p_effective_start_date between hsdr.effective_start_date
136 and hsdr.effective_end_date
137 and pet.element_type_id = hsdr.element_type_id
138 and p_effective_start_date between pet.effective_start_date
139 and pet.effective_end_date
140 and pet.business_group_id <> nvl(asg_bus_grp_id, -99))
141 AND NOT EXISTS
142 (select 1
143 from hxt_shift_diff_rules dr
144 where dr.sdp_id = sdp.id
145 and p_effective_start_date between dr.effective_start_date
146 and dr.effective_end_date
147 and dr.element_type_id not in
148 (select sdr.element_type_id
149 from pay_element_links_f ell
150 ,per_assignments_f asm
154 and sdr.effective_end_date
151 ,hxt_shift_diff_rules sdr
152 where sdr.sdp_id = sdp.id
153 and p_effective_start_date between sdr.effective_start_date
155 and asm.assignment_id = p_assignment_id
156 and p_effective_start_date between asm.effective_start_date
157 and asm.effective_end_date
158 and ell.element_type_id = sdr.element_type_id
159 and p_effective_start_date between ell.effective_start_date
160 and ell.effective_end_date
161 and nvl(ell.organization_id, nvl(asm.organization_id,-1))
162 = nvl(asm.organization_id,-1)
163 and (ell.people_group_id is null
164 or exists
165 (select 1
166 from pay_assignment_link_usages_f usage
167 where usage.assignment_id = asm.assignment_id
168 and usage.element_link_id = ell.element_link_id
169 and p_effective_start_date
170 between usage.effective_start_date
171 and usage.effective_end_date))
172 and nvl(ell.job_id,nvl(asm.job_id,-1))
173 = nvl(asm.job_id,-1)
174 and nvl(ell.position_id,nvl(asm.position_id,-1))
175 = nvl(asm.position_id,-1)
176 and nvl(ell.grade_id,nvl(asm.grade_id,-1))
177 = nvl(asm.grade_id,-1)
178 and nvl(ell.location_id,nvl(asm.location_id,-1))
179 = nvl(asm.location_id,-1)
180 and nvl(ell.payroll_id,nvl(asm.payroll_id,-1))
181 = nvl(asm.payroll_id,-1)
182 and nvl(ell.employment_category,nvl(asm.employment_category,-1))
183 = nvl(asm.employment_category,-1)
184 and nvl(ell.pay_basis_id,nvl(asm.pay_basis_id,-1))
185 = nvl(asm.pay_basis_id,-1)
186 and nvl(ell.business_group_id,nvl(asm.business_group_id,-1))
187 = nvl(asm.business_group_id,-1)))
188 and sdp.id = p_shift_differential_policy;
189
190 l_sdp_compatible VARCHAR2(15) := NULL;
191
192 /* Cursor to check that Hour Deduction policy and Assignment are compatible */
193 CURSOR chk_hour_deduct_policy IS
194 SELECT 'compatible'
195 FROM hxt_hour_deduct_policies hdp
196 WHERE p_effective_start_date between hdp.date_from
197 and nvl(hdp.date_to,p_effective_start_date)
198 AND nvl(hdp.business_group_id, nvl(asg_bus_grp_id, -99))
199 = nvl(asg_bus_grp_id, -98)
200 AND hdp.id = p_hour_deduction_policy;
201
202 l_hdp_compatible VARCHAR2(15) := NULL;
203
204 CURSOR c_asg_bus_grp_id IS
205 SELECT business_group_id
206 FROM per_assignments_f
207 WHERE p_effective_start_date between effective_start_date
208 and effective_end_date
209 And assignment_type = 'E'
210 And assignment_id = p_assignment_id;
211
212 BEGIN
213
214 g_debug :=hr_utility.debug_enabled;
215 if g_debug then
216 hr_utility.set_location('HXT_GEN_AAI.AAI_VALIDATIONS',10);
217 end if;
218
219 /* Get the assignment business group id */
220 OPEN c_asg_bus_grp_id;
221 FETCH c_asg_bus_grp_id into asg_bus_grp_id;
222 CLOSE c_asg_bus_grp_id;
223
224 if g_debug then
225 hr_utility.trace('asg_bus_grp_id :'||asg_bus_grp_id);
226 end if;
227
228 /* IF AUTOGEN_HOURS_YN is 'Y', then ROTATION_PLAN_NAME is required */
229 IF p_autogen_hours_yn = 'Y' and p_rotation_plan IS NULL THEN
230 --
231 if g_debug then
232 hr_utility.set_location('HXT_GEN_AAI.AAI_VALIDATIONS',20);
233 end if;
234 -- FND_MESSAGE.SET_NAME('HXT','HXT_39496_ROT_PLAN_REQD');
235 -- FND_MESSAGE.ERROR;
236 hr_utility.set_message(809, 'HXT_39496_ROT_PLAN_REQD');
237 hr_utility.raise_error;
238 --
239 END IF;
240
241 /* Check for rotation plan */
242 IF p_rotation_plan is NOT NULL THEN
243 if g_debug then
244 hr_utility.set_location('HXT_GEN_AAI.AAI_VALIDATIONS',25);
245 end if;
246 OPEN chk_rot_plan;
247 FETCH chk_rot_plan INTO l_rtp_compatible;
248 CLOSE chk_rot_plan;
249 IF l_rtp_compatible IS NULL THEN
250 --
251 if g_debug then
252 hr_utility.set_location('HXT_GEN_AAI.AAI_VALIDATIONS',30);
253 end if;
254 -- FND_MESSAGE.SET_NAME('HXT','HXT_xxxxx_ROT_PLAN_ERR');
255 -- FND_MESSAGE.ERROR;
256 hr_utility.set_message(809, 'HXT_xxxxx_ROT_PLAN_ERR');
257 hr_utility.raise_error;
258 --
259 END IF;
260 END IF;
261
262 /* Check whether Earning policy and Assignment are compatible */
263 if g_debug then
264 hr_utility.trace('p_assignment_id is :'|| p_assignment_id);
265 hr_utility.trace('p_earning_policy is :'|| p_earning_policy);
266 end if;
267 OPEN chk_earning_policy;
268 FETCH chk_earning_policy into l_egp_compatible;
269 CLOSE chk_earning_policy;
270 IF l_egp_compatible IS NULL THEN
271 --
272 if g_debug then
273 hr_utility.set_location('HXT_GEN_AAI.AAI_VALIDATIONS',40);
274 end if;
278 hr_utility.raise_error;
275 -- FND_MESSAGE.SET_NAME('HXT','HXT_xxxxx_EARN_POL_ERR');
276 -- FND_MESSAGE.ERROR;
277 hr_utility.set_message(809,'HXT_xxxxx_EARN_POL_ERR');
279 --
280 END IF;
281
282 /* Check whether Shift Differential Policy and Assignment are compatible */
283 IF p_shift_differential_policy is NOT NULL THEN
284 if g_debug then
285 hr_utility.set_location('HXT_GEN_AAI.AAI_VALIDATIONS',45);
286 end if;
287 OPEN chk_shift_diff_policy;
288 FETCH chk_shift_diff_policy into l_sdp_compatible;
289 CLOSE chk_shift_diff_policy;
290 IF l_sdp_compatible IS NULL THEN
291 --
292 if g_debug then
293 hr_utility.set_location('HXT_GEN_AAI.AAI_VALIDATIONS',50);
294 end if;
295 -- FND_MESSAGE.SET_NAME('HXT','HXT_xxxxx_SDP_ERR');
296 -- FND_MESSAGE.ERROR;
297 hr_utility.set_message(809,'HXT_xxxxx_SDP_ERR');
298 hr_utility.raise_error;
299 --
300 END IF;
301 END IF;
302
303 /* Check whether Hour Deduction Policy and Assignment are compatible */
304 IF p_hour_deduction_policy is NOT NULL THEN
305 if g_debug then
306 hr_utility.set_location('HXT_GEN_AAI.AAI_VALIDATIONS',55);
307 end if;
308 OPEN chk_hour_deduct_policy;
309 FETCH chk_hour_deduct_policy into l_hdp_compatible;
310 CLOSE chk_hour_deduct_policy;
311 IF l_hdp_compatible IS NULL THEN
312 --
313 if g_debug then
314 hr_utility.set_location('HXT_GEN_AAI.AAI_VALIDATIONS',60);
315 end if;
316 -- FND_MESSAGE.SET_NAME('HXT','HXT_xxxxx_HDP_ERR');
317 -- FND_MESSAGE.ERROR;
318 hr_utility.set_message(809,'HXT_xxxxx_HDP_ERR');
319 hr_utility.raise_error;
320 --
321 END IF;
322 END IF;
323
324 END AAI_VALIDATIONS;
325
326 -------------------------------------------------------------------------------
327
328 PROCEDURE Create_Otlr_Add_Assign_Info (
329 p_id NUMBER
330 ,p_effective_start_date DATE
331 --,p_effective_end_date DATE
332 ,p_assignment_id NUMBER
333 ,p_autogen_hours_yn VARCHAR2
334 ,p_rotation_plan NUMBER DEFAULT NULL
335 ,p_earning_policy NUMBER
336 ,p_shift_differential_policy NUMBER DEFAULT NULL
337 ,p_hour_deduction_policy NUMBER DEFAULT NULL
338 ,p_created_by NUMBER
339 ,p_creation_date DATE
340 ,p_last_updated_by NUMBER
341 ,p_last_update_date DATE
342 ,p_last_update_login NUMBER
343 ,p_attribute_category VARCHAR2 DEFAULT NULL
344 ,p_attribute1 VARCHAR2 DEFAULT NULL
345 ,p_attribute2 VARCHAR2 DEFAULT NULL
346 ,p_attribute3 VARCHAR2 DEFAULT NULL
347 ,p_attribute4 VARCHAR2 DEFAULT NULL
348 ,p_attribute5 VARCHAR2 DEFAULT NULL
349 ,p_attribute6 VARCHAR2 DEFAULT NULL
350 ,p_attribute7 VARCHAR2 DEFAULT NULL
351 ,p_attribute8 VARCHAR2 DEFAULT NULL
352 ,p_attribute9 VARCHAR2 DEFAULT NULL
353 ,p_attribute10 VARCHAR2 DEFAULT NULL
354 ,p_attribute11 VARCHAR2 DEFAULT NULL
355 ,p_attribute12 VARCHAR2 DEFAULT NULL
356 ,p_attribute13 VARCHAR2 DEFAULT NULL
357 ,p_attribute14 VARCHAR2 DEFAULT NULL
358 ,p_attribute15 VARCHAR2 DEFAULT NULL
359 ,p_attribute16 VARCHAR2 DEFAULT NULL
360 ,p_attribute17 VARCHAR2 DEFAULT NULL
361 ,p_attribute18 VARCHAR2 DEFAULT NULL
362 ,p_attribute19 VARCHAR2 DEFAULT NULL
363 ,p_attribute20 VARCHAR2 DEFAULT NULL
364 ,p_attribute21 VARCHAR2 DEFAULT NULL
365 ,p_attribute22 VARCHAR2 DEFAULT NULL
366 ,p_attribute23 VARCHAR2 DEFAULT NULL
367 ,p_attribute24 VARCHAR2 DEFAULT NULL
368 ,p_attribute25 VARCHAR2 DEFAULT NULL
369 ,p_attribute26 VARCHAR2 DEFAULT NULL
370 ,p_attribute27 VARCHAR2 DEFAULT NULL
371 ,p_attribute28 VARCHAR2 DEFAULT NULL
372 ,p_attribute29 VARCHAR2 DEFAULT NULL
373 ,p_attribute30 VARCHAR2 DEFAULT NULL
374 ) IS
375
376 --The purpose of this procedure is to INSERT a row for the additional
377 --assignment information
378
379 --Arguments
380 -- Arguments
381 -- p_id -ID of the record.
382 -- p_effective_start_date -effective_start_date for the record to be
383 -- -inserted.
384 -- p_assignment_id -id of the assignment for which the information
385 -- -has to be added.
386 -- p_autogen_hours_yn -The value for this argument can be either
387 -- -'Y' or 'N',to specify whether to autogen
388 -- -the hours for this assignment
389 --p_rotation_plan -rotation_plan_id for this assignment
390 --p_earning_policy -earning_policy_id for this assignment
391 --p_shift_differential_policy -shift_differential_policy_id for this
392 -- -assignment
393 --p_hour_deduction_policy -hour_deduction_policy_id for this assignment
394 --p_attribute1 .. p_attribute30 -attribute values
395
396 asg_bus_grp_id NUMBER(15);
400 SELECT '1'
397
398 /* Cursor to Check for the Duplicate row */
399 CURSOR dup_aai IS
401 FROM hxt_add_assign_info_f aai
402 WHERE aai.assignment_id = p_assignment_id;
403 l_duplicate_row VARCHAR2(1);
404
405 /* Cursor to Create unique ID for AAI row */
406 CURSOR create_unique_id IS
407 SELECT hxt_seqno.nextval
408 FROM sys.dual;
409 l_id NUMBER(15);
410
411 BEGIN
412 g_debug :=hr_utility.debug_enabled;
413 if g_debug then
414 hr_utility.set_location('HXT_GEN_AAI.Create_Otlr_Add_Assign_Info ',10);
415 end if;
416
417 /* First, make sure AAI row doesn't already exist for this assignment */
418 OPEN dup_aai;
419 FETCH dup_aai into l_duplicate_row;
420 IF dup_aai%FOUND THEN
421 if g_debug then
422 hr_utility.set_location('HXT_GEN_AAI.Create_Otlr_Add_Assign_Info ',20);
423 end if;
424 -- FND_MESSAGE.SET_NAME('HXT','HXT_39481_INFO_EXST_FOR_ASSIGN');
425 -- FND_MESSAGE.Error;
426 hr_utility.set_message(809,'HXT_39481_INFO_EXST_FOR_ASSIGN');
427 hr_utility.raise_error;
428 CLOSE dup_aai;
429 END IF;
430 CLOSE dup_aai;
431
432 if g_debug then
433 hr_utility.set_location('HXT_GEN_AAI.Create_Otlr_Add_Assign_Info ',30);
434 end if;
435 HXT_GEN_AAI.AAI_VALIDATIONS
436 (p_assignment_id => p_assignment_id
437 ,p_earning_policy => p_earning_policy
438 ,p_rotation_plan => p_rotation_plan
439 ,p_shift_differential_policy => p_shift_differential_policy
440 ,p_hour_deduction_policy => p_hour_deduction_policy
441 ,p_autogen_hours_yn => p_autogen_hours_yn
442 ,p_effective_start_date => p_effective_start_date
443 );
444 if g_debug then
445 hr_utility.set_location('HXT_GEN_AAI.Create_Otlr_Add_Assign_Info ',40);
446 end if;
447
448 /* Create unique ID for AAI row */
449 IF (p_id is null) THEN
450 if g_debug then
451 hr_utility.set_location('HXT_GEN_AAI.Create_Otlr_Add_Assign_Info ',50);
452 end if;
453 OPEN create_unique_id;
454 FETCH create_unique_id into l_id;
455 if g_debug then
456 hr_utility.trace('l_id is :'|| l_id);
457 end if;
458 IF create_unique_id%NOTFOUND THEN
459 if g_debug then
460 hr_utility.set_location('HXT_GEN_AAI.Create_Otlr_Add_Assign_Info ',60);
461 end if;
462 -- fnd_message.set_name('HXT', 'HXT_39124_ROW_IN_DUAL_NF');
463 -- fnd_message.error;
464 hr_utility.set_message(809,'HXT_39124_ROW_IN_DUAL_NF');
465 hr_utility.raise_error;
466 END IF;
467 CLOSE create_unique_id;
468 END IF;
469
470 if g_debug then
471 hr_utility.set_location('HXT_GEN_AAI.Create_Otlr_Add_Assign_Info ',70);
472 end if;
473
474 INSERT into HXT_ADD_ASSIGN_INFO_F
475 (id
476 ,effective_start_date
477 ,effective_end_date
478 ,assignment_id
479 ,autogen_hours_yn
480 ,rotation_plan
481 ,earning_policy
482 ,shift_differential_policy
483 ,hour_deduction_policy
484 ,created_by
485 ,creation_date
486 ,last_updated_by
487 ,last_update_date
488 ,last_update_login
489 ,attribute_category
490 ,attribute1
491 ,attribute2
492 ,attribute3
493 ,attribute4
494 ,attribute5
495 ,attribute6
496 ,attribute7
497 ,attribute8
498 ,attribute9
499 ,attribute10
500 ,attribute11
501 ,attribute12
502 ,attribute13
503 ,attribute14
504 ,attribute15
505 ,attribute16
506 ,attribute17
507 ,attribute18
508 ,attribute19
509 ,attribute20
510 ,attribute21
511 ,attribute22
512 ,attribute23
513 ,attribute24
514 ,attribute25
515 ,attribute26
516 ,attribute27
517 ,attribute28
518 ,attribute29
519 ,attribute30)
520 VALUES( p_id
521 ,p_effective_start_date
522 ,hr_general.end_of_time --p_effective_end_date
523 ,p_assignment_id
524 ,p_autogen_hours_yn
525 ,p_rotation_plan
526 ,p_earning_policy
527 ,p_shift_differential_policy
528 ,p_hour_deduction_policy
529 ,p_created_by
530 ,p_creation_date
531 ,p_last_updated_by
532 ,p_last_update_date
533 ,p_last_update_login
534 ,p_attribute_category
535 ,p_attribute1
536 ,p_attribute2
537 ,p_attribute3
538 ,p_attribute4
539 ,p_attribute5
540 ,p_attribute6
541 ,p_attribute7
542 ,p_attribute8
543 ,p_attribute9
544 ,p_attribute10
545 ,p_attribute11
546 ,p_attribute12
547 ,p_attribute13
548 ,p_attribute14
549 ,p_attribute15
550 ,p_attribute16
551 ,p_attribute17
552 ,p_attribute18
553 ,p_attribute19
554 ,p_attribute20
555 ,p_attribute21
556 ,p_attribute22
557 ,p_attribute23
558 ,p_attribute24
559 ,p_attribute25
563 ,p_attribute29
560 ,p_attribute26
561 ,p_attribute27
562 ,p_attribute28
564 ,p_attribute30);
565
566 if g_debug then
567 hr_utility.set_location('HXT_GEN_AAI.Create_Otlr_Add_Assign_Info ',80);
568 end if;
569
570 END Create_Otlr_Add_Assign_Info ;
571
572 -------------------------------------------------------------------------------
573
574 PROCEDURE Update_Otlr_Add_Assign_Info (
575 p_id NUMBER
576 ,p_datetrack_mode VARCHAR2
577 ,p_effective_date DATE
578 ,p_effective_start_date DATE
579 -- ,p_effective_end_date DATE
580 ,p_assignment_id NUMBER
581 ,p_autogen_hours_yn VARCHAR2
582 ,p_rotation_plan NUMBER DEFAULT NULL
583 ,p_earning_policy NUMBER
584 ,p_shift_differential_policy NUMBER DEFAULT NULL
585 ,p_hour_deduction_policy NUMBER DEFAULT NULL
586 ,p_created_by NUMBER
587 ,p_creation_date DATE
588 ,p_last_updated_by NUMBER
589 ,p_last_update_date DATE
590 ,p_last_update_login NUMBER
591 ,p_attribute_category VARCHAR2 DEFAULT NULL
592 ,p_attribute1 VARCHAR2 DEFAULT NULL
593 ,p_attribute2 VARCHAR2 DEFAULT NULL
594 ,p_attribute3 VARCHAR2 DEFAULT NULL
595 ,p_attribute4 VARCHAR2 DEFAULT NULL
596 ,p_attribute5 VARCHAR2 DEFAULT NULL
597 ,p_attribute6 VARCHAR2 DEFAULT NULL
598 ,p_attribute7 VARCHAR2 DEFAULT NULL
599 ,p_attribute8 VARCHAR2 DEFAULT NULL
600 ,p_attribute9 VARCHAR2 DEFAULT NULL
601 ,p_attribute10 VARCHAR2 DEFAULT NULL
602 ,p_attribute11 VARCHAR2 DEFAULT NULL
603 ,p_attribute12 VARCHAR2 DEFAULT NULL
604 ,p_attribute13 VARCHAR2 DEFAULT NULL
605 ,p_attribute14 VARCHAR2 DEFAULT NULL
606 ,p_attribute15 VARCHAR2 DEFAULT NULL
607 ,p_attribute16 VARCHAR2 DEFAULT NULL
608 ,p_attribute17 VARCHAR2 DEFAULT NULL
609 ,p_attribute18 VARCHAR2 DEFAULT NULL
610 ,p_attribute19 VARCHAR2 DEFAULT NULL
611 ,p_attribute20 VARCHAR2 DEFAULT NULL
612 ,p_attribute21 VARCHAR2 DEFAULT NULL
613 ,p_attribute22 VARCHAR2 DEFAULT NULL
614 ,p_attribute23 VARCHAR2 DEFAULT NULL
615 ,p_attribute24 VARCHAR2 DEFAULT NULL
616 ,p_attribute25 VARCHAR2 DEFAULT NULL
617 ,p_attribute26 VARCHAR2 DEFAULT NULL
618 ,p_attribute27 VARCHAR2 DEFAULT NULL
619 ,p_attribute28 VARCHAR2 DEFAULT NULL
620 ,p_attribute29 VARCHAR2 DEFAULT NULL
621 ,p_attribute30 VARCHAR2 DEFAULT NULL
622 ) IS
623
624 -- The purpose of this procedure is to perform 'CORRECTION' and
625 -- 'UPDATE_OVERRIDE' for the additional assignment information.This procedure
626 -- when run in an 'UPDATE_OVERRIDE' mode would override the future changes
627
628 -- Arguments
629 -- p_id - ID of the record.
630 -- p_datetrack_mode -The mode in which the api has to be run.
631 -- -It can be run in 'CORRECTION' or
632 -- -'UPDATE_OVERRIDE' mode.
633 -- p_effective_date -The record will be update das of this date.
634 -- p_effective_start_date -effective_start_date of the record for this
635 -- -assignment that has effective_end_date as
636 -- -end_of_time.
637 -- p_assignment_id -assignment_id for which the update has to be
638 -- -done.
639 -- p_autogen_hours_yn -The value for this argument can be either
640 -- -'Y' or 'N',to specify whether to autogen
641 -- -the hours for this assignment.
642 --p_rotation_plan -rotation_plan_id for this assignment.
643 --p_earning_policy -earning_policy_id for this assignment.
644 --p_shift_differential_policy -shift_differential_policy_id for this
645 -- -assignment.
646 --p_hour_deduction_policy -hour_deduction_policy_id for this assignment.
647 --p_attribute1 .. p_attribute30 -attribute values.
648
649 CURSOR c2 is
650 SELECT rowid,effective_start_date,effective_end_date
651 FROM hxt_add_assign_info_f
652 WHERE id = p_id
653 AND effective_start_date = p_effective_start_date;
654
655 CURSOR c_delete_rec_eot is
656 SELECT rowid
657 FROM hxt_add_assign_info_f
658 WHERE id = p_id
659 AND effective_end_date = hr_general.end_of_time;
660
661
662 -- Bug 8327591
663 -- Added the below cursor to pick up the future records
664 -- ie. records with start date > p_effective_date
665
666 CURSOR get_future_records
667 IS SELECT ROWIDTOCHAR(rowid),
668 id
669 FROM hxt_add_assign_info_f
670 WHERE id = p_id
671 AND effective_start_date > p_effective_date;
672
673
674
675 /* Cursor to Create unique ID for AAI row */
676 /* CURSOR create_unique_id IS
677 SELECT hxt_seqno.nextval
678 FROM sys.dual;
679
680 l_id NUMBER(15);
681 */
682 l_effective_start_date DATE;
683 l_effective_end_date DATE;
684 p_rowid VARCHAR2(30);
685 p_rowid_eot VARCHAR2(30);
686 l_count NUMBER;
687
688 l_datetrack_mode VARCHAR2(50);
692 idtab NUMTABLE;
689 TYPE VARCHARTABLE IS TABLE OF VARCHAR2(30);
690 TYPE NUMTABLE IS TABLE OF NUMBER;
691
693 rowidtab VARCHARTABLE;
694
695
696
697 BEGIN
698 g_debug :=hr_utility.debug_enabled;
699 if g_debug then
700 hr_utility.set_location('HXT_GEN_AAI.Update_Otlr_Add_Assign_Info ',10);
701 hr_utility.set_location('HXT_GEN_AAI.Update_Otlr_Add_Assign_Info ',20);
702 end if;
703
704 -- Bug 8938280
705 -- Validation call here is wrong, commenting this out.
706 /*
707 HXT_GEN_AAI.AAI_VALIDATIONS
708 (p_assignment_id => p_assignment_id
709 ,p_earning_policy => p_earning_policy
710 ,p_rotation_plan => p_rotation_plan
711 ,p_shift_differential_policy => p_shift_differential_policy
712 ,p_hour_deduction_policy => p_hour_deduction_policy
713 ,p_autogen_hours_yn => p_autogen_hours_yn
714 ,p_effective_start_date => p_effective_start_date
715 );
716 */
717
718 if g_debug then
719 hr_utility.set_location('HXT_GEN_AAI.Update_Otlr_Add_Assign_Info ',30);
720 end if;
721
722 OPEN c2;
723 FETCH c2 into p_rowid,l_effective_start_date,l_effective_end_date;
724 CLOSE c2;
725
726 -- Bug 8327591
727 -- This is just a message for the users.
728 IF g_debug
729 THEN
730 IF p_effective_date > l_effective_end_date
731 OR p_effective_date < l_effective_start_date
732 THEN
733 hr_utility.trace(' p_effective_date :'||p_effective_date);
734 hr_utility.trace(' l_effective_start_date :'||l_effective_start_date);
735 hr_utility.trace(' l_effective_end_date :'||l_effective_end_date);
736 hr_utility.trace(' Effective Date falls outside the range '
737 ||' for the given record. Updates would be '
738 ||' done based on p_effective_date ');
739 END IF;
740 END IF;
741
742 -- Bug 8327591
743 -- If the effecctive_start_date coincides with the effective_date
744 -- the mode is switched to CORRECTION
745 IF p_effective_date = l_effective_start_date
746 THEN
747 l_datetrack_mode := 'CORRECTION';
748 ELSE
749 l_datetrack_mode := 'UPDATE_OVERRIDE';
750 END IF;
751
752
753 -- Bug 8327591
754 -- We are no longer using this to delete the reocrds.
755 /*
756 OPEN c_delete_rec_eot;
757 FETCH c_delete_rec_eot into p_rowid_eot;
758 CLOSE c_delete_rec_eot;
759 */
760
761 IF p_rowid is NOT NULL THEN
762 if g_debug then
763 hr_utility.trace('effective_start_date is :'|| l_effective_start_date);
764 hr_utility.trace('effective_end_date is :'|| l_effective_end_date);
765 hr_utility.trace('Rowid is :'|| p_rowid);
766 end if;
767 IF l_datetrack_mode = 'CORRECTION' THEN
768 if g_debug then
769 hr_utility.set_location('HXT_GEN_AAI.Update_Otlr_Add_Assign_Info ',40);
770 end if;
771
772 -- Bug 8938280
773 -- Added this validation call here. Parameters are same as
774 -- the commented one above.
775 HXT_GEN_AAI.AAI_VALIDATIONS
776 (p_assignment_id => p_assignment_id
777 ,p_earning_policy => p_earning_policy
778 ,p_rotation_plan => p_rotation_plan
779 ,p_shift_differential_policy => p_shift_differential_policy
780 ,p_hour_deduction_policy => p_hour_deduction_policy
781 ,p_autogen_hours_yn => p_autogen_hours_yn
782 ,p_effective_start_date => p_effective_start_date
783 );
784
785
786 UPDATE HXT_ADD_ASSIGN_INFO_F
787 SET--effective_start_date = p_effective_start_date
788 --,effective_end_date = p_effective_end_date
789 --,assignment_id = p_assignment_id
790 autogen_hours_yn = p_autogen_hours_yn
791 ,rotation_plan = p_rotation_plan
792 ,earning_policy = p_earning_policy
793 ,shift_differential_policy = p_shift_differential_policy
794 ,hour_deduction_policy = p_hour_deduction_policy
795 ,created_by = p_created_by
796 ,creation_date = p_creation_date
797 ,last_updated_by = p_last_updated_by
798 ,last_update_date = p_last_update_date
799 ,last_update_login = p_last_update_login
800 ,attribute_category = p_attribute_category
801 ,attribute1 = p_attribute1
802 ,attribute2 = p_attribute2
803 ,attribute3 = p_attribute3
804 ,attribute4 = p_attribute4
805 ,attribute5 = p_attribute5
806 ,attribute6 = p_attribute6
807 ,attribute7 = p_attribute7
808 ,attribute8 = p_attribute8
809 ,attribute9 = p_attribute9
810 ,attribute10 = p_attribute10
811 ,attribute11 = p_attribute11
812 ,attribute12 = p_attribute12
813 ,attribute13 = p_attribute13
814 ,attribute14 = p_attribute14
815 ,attribute15 = p_attribute15
816 ,attribute16 = p_attribute16
817 ,attribute17 = p_attribute17
818 ,attribute18 = p_attribute18
819 ,attribute19 = p_attribute19
820 ,attribute20 = p_attribute20
821 ,attribute21 = p_attribute21
825 ,attribute25 = p_attribute25
822 ,attribute22 = p_attribute22
823 ,attribute23 = p_attribute23
824 ,attribute24 = p_attribute24
826 ,attribute26 = p_attribute26
827 ,attribute27 = p_attribute27
828 ,attribute28 = p_attribute28
829 ,attribute29 = p_attribute29
830 ,attribute30 = p_attribute30
831 WHERE rowid = p_rowid;
832
833 ELSIF l_datetrack_mode = 'UPDATE_OVERRIDE' THEN
834 /* end date the existing record and then create a new record if an
835 existing record found */
836
837 if g_debug then
838 hr_utility.set_location('HXT_GEN_AAI.Update_Otlr_Add_Assign_Info ',50);
839 end if;
840
841 -- Bug 8938280
842 -- Added this validation call here. Parameter for effective
843 -- start date should be the passed p_effective_date.
844 HXT_GEN_AAI.AAI_VALIDATIONS
845 (p_assignment_id => p_assignment_id
846 ,p_earning_policy => p_earning_policy
847 ,p_rotation_plan => p_rotation_plan
848 ,p_shift_differential_policy => p_shift_differential_policy
849 ,p_hour_deduction_policy => p_hour_deduction_policy
850 ,p_autogen_hours_yn => p_autogen_hours_yn
851 ,p_effective_start_date => p_effective_date
852 );
853
854 /*UPDATE hxt_add_assign_info_f
855 SET effective_end_date = p_effective_date - 1
856 WHERE rowid = p_rowid;
857 */
858 SELECT count(*) into l_count
859 FROM hxt_add_assign_info_f
860 WHERE assignment_id = p_assignment_id;
861
862
863 -- Bug 8327591
864 -- No longer using this condition. Open the cursor below and follow
865 -- that cursor.
866
867 -- IF (l_count >1) AND (p_effective_date < p_effective_start_date) THEN
868 OPEN get_future_records;
869
870 FETCH get_future_records BULK COLLECT INTO rowidtab,
871 idtab;
872
873 CLOSE get_future_records;
874
875 IF (idtab.COUNT >0)
876 THEN
877 if g_debug then
878 hr_utility.set_location('HXT_GEN_AAI.Update_Otlr_Add_Assign_Info ',60);
879 end if;
880
881 -- Bug 8327591
882 -- Not required anymore.
883 -- We are doing it with the below FORALL statement.
884 /*
885 DELETE from hxt_add_assign_info_f
886 WHERE rowid = p_rowid_eot;
887 */
888
889 FORALL i IN rowidtab.FIRST..rowidtab.LAST
890 DELETE FROM hxt_add_assign_info_f
891 WHERE rowid = chartorowid(rowidtab(i));
892
893
894
895 if g_debug then
896 hr_utility.trace('p_effective_date is :'||p_effective_date);
897 end if;
898
899 UPDATE hxt_add_assign_info_f aai
900 SET aai.effective_end_date = p_effective_date - 1
901 WHERE aai.assignment_id = p_assignment_id
902 AND p_effective_date between aai.effective_start_date
903 and aai.effective_end_date;
904 ELSE
905 UPDATE hxt_add_assign_info_f
906 SET effective_end_date = p_effective_date - 1
907 WHERE rowid = p_rowid;
908 END IF;
909
910 if g_debug then
911 hr_utility.set_location('HXT_GEN_AAI.Update_Otlr_Add_Assign_Info ',70);
912 hr_utility.trace('p_id is :'||p_id);
913 end if;
914
915 INSERT into HXT_ADD_ASSIGN_INFO_F
916 (id
917 ,effective_start_date
918 ,effective_end_date
919 ,assignment_id
920 ,autogen_hours_yn
921 ,rotation_plan
922 ,earning_policy
923 ,shift_differential_policy
924 ,hour_deduction_policy
925 ,created_by
926 ,creation_date
927 ,last_updated_by
928 ,last_update_date
929 ,last_update_login
930 ,attribute_category
931 ,attribute1
932 ,attribute2
933 ,attribute3
934 ,attribute4
935 ,attribute5
936 ,attribute6
937 ,attribute7
938 ,attribute8
939 ,attribute9
940 ,attribute10
941 ,attribute11
942 ,attribute12
943 ,attribute13
944 ,attribute14
945 ,attribute15
946 ,attribute16
947 ,attribute17
948 ,attribute18
949 ,attribute19
950 ,attribute20
951 ,attribute21
952 ,attribute22
953 ,attribute23
954 ,attribute24
955 ,attribute25
956 ,attribute26
957 ,attribute27
958 ,attribute28
959 ,attribute29
960 ,attribute30)
961 VALUES
962 (p_id
963 ,p_effective_date
964 ,hr_general.end_of_time
965 ,p_assignment_id
966 ,p_autogen_hours_yn
967 ,p_rotation_plan
968 ,p_earning_policy
969 ,p_shift_differential_policy
970 ,p_hour_deduction_policy
971 ,p_created_by
972 ,p_creation_date
973 ,p_last_updated_by
974 ,p_last_update_date
975 ,p_last_update_login
976 ,p_attribute_category
977 ,p_attribute1
978 ,p_attribute2
979 ,p_attribute3
980 ,p_attribute4
981 ,p_attribute5
982 ,p_attribute6
983 ,p_attribute7
984 ,p_attribute8
985 ,p_attribute9
986 ,p_attribute10
987 ,p_attribute11
988 ,p_attribute12
989 ,p_attribute13
990 ,p_attribute14
991 ,p_attribute15
992 ,p_attribute16
993 ,p_attribute17
994 ,p_attribute18
995 ,p_attribute19
996 ,p_attribute20
997 ,p_attribute21
998 ,p_attribute22
999 ,p_attribute23
1000 ,p_attribute24
1001 ,p_attribute25
1002 ,p_attribute26
1003 ,p_attribute27
1004 ,p_attribute28
1005 ,p_attribute29
1006 ,p_attribute30);
1007
1008 if g_debug then
1009 hr_utility.set_location('HXT_GEN_AAI.Update_Otlr_Add_Assign_Info ',80);
1010 end if;
1011
1012 ELSE
1013 /* INVALID datetrack_mode */
1014 hr_utility.set_message(809,'HXT_xxxxx_INVALID_DTMODE');
1015 hr_utility.raise_error;
1016 END IF;
1017
1018 if g_debug then
1019 hr_utility.set_location('HXT_GEN_AAI.Update_Otlr_Add_Assign_Info ',90);
1020 end if;
1021 END IF;
1022
1023 END Update_Otlr_Add_Assign_Info ;
1024
1025 -------------------------------------------------------------------------------
1026 --begin
1027 --hr_utility.trace_on(null,'mhanda');
1028
1029 END HXT_GEN_AAI;