[Home] [Help]
PACKAGE BODY: APPS.HXT_GEN_AAI
Source
1 PACKAGE BODY HXT_GEN_AAI AS
2 /* $Header: hxtgenai.pkb 120.3.12010000.2 2008/08/05 09:47:31 ubhat 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
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
52 and t.effective_end_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
151 ,hxt_shift_diff_rules sdr
152 where sdr.sdp_id = sdp.id
153 and p_effective_start_date between sdr.effective_start_date
154 and sdr.effective_end_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;
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');
278 hr_utility.raise_error;
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;
314 hr_utility.set_location('HXT_GEN_AAI.AAI_VALIDATIONS',60);
311 IF l_hdp_compatible IS NULL THEN
312 --
313 if g_debug then
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);
397
398 /* Cursor to Check for the Duplicate row */
399 CURSOR dup_aai IS
400 SELECT '1'
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;
427 hr_utility.raise_error;
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');
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
560 ,p_attribute26
561 ,p_attribute27
562 ,p_attribute28
563 ,p_attribute29
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
580 ,p_assignment_id NUMBER
577 ,p_effective_date DATE
578 ,p_effective_start_date DATE
579 -- ,p_effective_end_date DATE
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 /* Cursor to Create unique ID for AAI row */
662 /* CURSOR create_unique_id IS
663 SELECT hxt_seqno.nextval
664 FROM sys.dual;
665
666 l_id NUMBER(15);
667 */
668 l_effective_start_date DATE;
669 l_effective_end_date DATE;
670 p_rowid VARCHAR2(30);
671 p_rowid_eot VARCHAR2(30);
672 l_count NUMBER;
673
674 BEGIN
675 g_debug :=hr_utility.debug_enabled;
676 if g_debug then
677 hr_utility.set_location('HXT_GEN_AAI.Update_Otlr_Add_Assign_Info ',10);
678 hr_utility.set_location('HXT_GEN_AAI.Update_Otlr_Add_Assign_Info ',20);
679 end if;
680 HXT_GEN_AAI.AAI_VALIDATIONS
681 (p_assignment_id => p_assignment_id
682 ,p_earning_policy => p_earning_policy
683 ,p_rotation_plan => p_rotation_plan
684 ,p_shift_differential_policy => p_shift_differential_policy
688 );
685 ,p_hour_deduction_policy => p_hour_deduction_policy
686 ,p_autogen_hours_yn => p_autogen_hours_yn
687 ,p_effective_start_date => p_effective_start_date
689 if g_debug then
690 hr_utility.set_location('HXT_GEN_AAI.Update_Otlr_Add_Assign_Info ',30);
691 end if;
692
693 OPEN c2;
694 FETCH c2 into p_rowid,l_effective_start_date,l_effective_end_date;
695 CLOSE c2;
696
697 OPEN c_delete_rec_eot;
698 FETCH c_delete_rec_eot into p_rowid_eot;
699 CLOSE c_delete_rec_eot;
700
701 IF p_rowid is NOT NULL THEN
702 if g_debug then
703 hr_utility.trace('effective_start_date is :'|| l_effective_start_date);
704 hr_utility.trace('effective_end_date is :'|| l_effective_end_date);
705 hr_utility.trace('Rowid is :'|| p_rowid);
706 end if;
707 IF p_datetrack_mode = 'CORRECTION' THEN
708 if g_debug then
709 hr_utility.set_location('HXT_GEN_AAI.Update_Otlr_Add_Assign_Info ',40);
710 end if;
711
712 UPDATE HXT_ADD_ASSIGN_INFO_F
713 SET--effective_start_date = p_effective_start_date
714 --,effective_end_date = p_effective_end_date
715 --,assignment_id = p_assignment_id
716 autogen_hours_yn = p_autogen_hours_yn
717 ,rotation_plan = p_rotation_plan
718 ,earning_policy = p_earning_policy
719 ,shift_differential_policy = p_shift_differential_policy
720 ,hour_deduction_policy = p_hour_deduction_policy
721 ,created_by = p_created_by
722 ,creation_date = p_creation_date
723 ,last_updated_by = p_last_updated_by
724 ,last_update_date = p_last_update_date
725 ,last_update_login = p_last_update_login
726 ,attribute_category = p_attribute_category
727 ,attribute1 = p_attribute1
728 ,attribute2 = p_attribute2
729 ,attribute3 = p_attribute3
730 ,attribute4 = p_attribute4
731 ,attribute5 = p_attribute5
732 ,attribute6 = p_attribute6
733 ,attribute7 = p_attribute7
734 ,attribute8 = p_attribute8
735 ,attribute9 = p_attribute9
736 ,attribute10 = p_attribute10
737 ,attribute11 = p_attribute11
738 ,attribute12 = p_attribute12
739 ,attribute13 = p_attribute13
740 ,attribute14 = p_attribute14
741 ,attribute15 = p_attribute15
742 ,attribute16 = p_attribute16
743 ,attribute17 = p_attribute17
744 ,attribute18 = p_attribute18
745 ,attribute19 = p_attribute19
746 ,attribute20 = p_attribute20
747 ,attribute21 = p_attribute21
748 ,attribute22 = p_attribute22
749 ,attribute23 = p_attribute23
750 ,attribute24 = p_attribute24
751 ,attribute25 = p_attribute25
752 ,attribute26 = p_attribute26
753 ,attribute27 = p_attribute27
754 ,attribute28 = p_attribute28
755 ,attribute29 = p_attribute29
756 ,attribute30 = p_attribute30
757 WHERE rowid = p_rowid;
758
759 ELSIF p_datetrack_mode = 'UPDATE_OVERRIDE' THEN
760 /* end date the existing record and then create a new record if an
761 existing record found */
762
763 if g_debug then
764 hr_utility.set_location('HXT_GEN_AAI.Update_Otlr_Add_Assign_Info ',50);
765 end if;
766
767 /*UPDATE hxt_add_assign_info_f
768 SET effective_end_date = p_effective_date - 1
769 WHERE rowid = p_rowid;
770 */
771 SELECT count(*) into l_count
772 FROM hxt_add_assign_info_f
773 WHERE assignment_id = p_assignment_id;
774
775 IF (l_count >1) AND (p_effective_date < p_effective_start_date) THEN
776 if g_debug then
777 hr_utility.set_location('HXT_GEN_AAI.Update_Otlr_Add_Assign_Info ',60);
778 end if;
779 DELETE from hxt_add_assign_info_f
780 WHERE rowid = p_rowid_eot;
781
782 if g_debug then
783 hr_utility.trace('p_effective_date is :'||p_effective_date);
784 end if;
785
786 UPDATE hxt_add_assign_info_f aai
787 SET aai.effective_end_date = p_effective_date - 1
788 WHERE aai.assignment_id = p_assignment_id
789 AND p_effective_date between aai.effective_start_date
790 and aai.effective_end_date;
791 ELSE
792 UPDATE hxt_add_assign_info_f
793 SET effective_end_date = p_effective_date - 1
794 WHERE rowid = p_rowid;
795 END IF;
796
797 if g_debug then
798 hr_utility.set_location('HXT_GEN_AAI.Update_Otlr_Add_Assign_Info ',70);
799 hr_utility.trace('p_id is :'||p_id);
800 end if;
801
802 INSERT into HXT_ADD_ASSIGN_INFO_F
803 (id
804 ,effective_start_date
808 ,rotation_plan
805 ,effective_end_date
806 ,assignment_id
807 ,autogen_hours_yn
809 ,earning_policy
810 ,shift_differential_policy
811 ,hour_deduction_policy
812 ,created_by
813 ,creation_date
814 ,last_updated_by
815 ,last_update_date
816 ,last_update_login
817 ,attribute_category
818 ,attribute1
819 ,attribute2
820 ,attribute3
821 ,attribute4
822 ,attribute5
823 ,attribute6
824 ,attribute7
825 ,attribute8
826 ,attribute9
827 ,attribute10
828 ,attribute11
829 ,attribute12
830 ,attribute13
831 ,attribute14
832 ,attribute15
833 ,attribute16
834 ,attribute17
835 ,attribute18
836 ,attribute19
837 ,attribute20
838 ,attribute21
839 ,attribute22
840 ,attribute23
841 ,attribute24
842 ,attribute25
843 ,attribute26
844 ,attribute27
845 ,attribute28
846 ,attribute29
847 ,attribute30)
848 VALUES
849 (p_id
850 ,p_effective_date
851 ,hr_general.end_of_time
852 ,p_assignment_id
853 ,p_autogen_hours_yn
854 ,p_rotation_plan
855 ,p_earning_policy
856 ,p_shift_differential_policy
857 ,p_hour_deduction_policy
858 ,p_created_by
859 ,p_creation_date
860 ,p_last_updated_by
861 ,p_last_update_date
862 ,p_last_update_login
863 ,p_attribute_category
864 ,p_attribute1
865 ,p_attribute2
866 ,p_attribute3
867 ,p_attribute4
868 ,p_attribute5
869 ,p_attribute6
870 ,p_attribute7
871 ,p_attribute8
872 ,p_attribute9
873 ,p_attribute10
874 ,p_attribute11
875 ,p_attribute12
876 ,p_attribute13
877 ,p_attribute14
878 ,p_attribute15
879 ,p_attribute16
880 ,p_attribute17
881 ,p_attribute18
882 ,p_attribute19
883 ,p_attribute20
884 ,p_attribute21
885 ,p_attribute22
886 ,p_attribute23
887 ,p_attribute24
888 ,p_attribute25
889 ,p_attribute26
890 ,p_attribute27
891 ,p_attribute28
892 ,p_attribute29
893 ,p_attribute30);
894
895 if g_debug then
896 hr_utility.set_location('HXT_GEN_AAI.Update_Otlr_Add_Assign_Info ',80);
897 end if;
898
899 ELSE
900 /* INVALID datetrack_mode */
901 hr_utility.set_message(809,'HXT_xxxxx_INVALID_DTMODE');
902 hr_utility.raise_error;
903 END IF;
904
905 if g_debug then
906 hr_utility.set_location('HXT_GEN_AAI.Update_Otlr_Add_Assign_Info ',90);
907 end if;
908 END IF;
909
910 END Update_Otlr_Add_Assign_Info ;
911
912 -------------------------------------------------------------------------------
913 --begin
914 --hr_utility.trace_on(null,'mhanda');
915
916 END HXT_GEN_AAI;