[Home] [Help]
PACKAGE BODY: APPS.HR_ASSIGNMENT_ATT
Source
1 PACKAGE BODY HR_ASSIGNMENT_ATT as
2 /* $Header: peasgati.pkb 120.5 2011/12/01 10:00:45 chsharma ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' hr_assignment_att.';
7 --
8 --
9 -- ----------------------------------------------------------------------------
10 -- |-----------------------------< update_asg >-------------------------------|
11 -- ----------------------------------------------------------------------------
12 --
13 procedure update_asg
14 (p_validate in boolean default false
15 ,p_effective_date in date
16 ,p_attribute_update_mode in varchar2
17 ,p_assignment_id in number
18 ,p_object_version_number in out nocopy number
19 ,p_supervisor_id in number default hr_api.g_number
20 ,p_supervisor_assignment_id in number default hr_api.g_number
21 ,p_assignment_number in varchar2 default hr_api.g_varchar2
22 ,p_change_reason in varchar2 default hr_api.g_varchar2
23 ,p_comments in varchar2 default hr_api.g_varchar2
24 ,p_date_probation_end in date default hr_api.g_date
25 ,p_default_code_comb_id in number default hr_api.g_number
26 ,p_frequency in varchar2 default hr_api.g_varchar2
27 ,p_internal_address_line in varchar2 default hr_api.g_varchar2
28 ,p_manager_flag in varchar2 default hr_api.g_varchar2
29 ,p_normal_hours in number default hr_api.g_number
30 ,p_perf_review_period in number default hr_api.g_number
31 ,p_perf_review_period_frequency in varchar2 default hr_api.g_varchar2
32 ,p_probation_period in number default hr_api.g_number
33 ,p_probation_unit in varchar2 default hr_api.g_varchar2
34 ,p_sal_review_period in number default hr_api.g_number
35 ,p_sal_review_period_frequency in varchar2 default hr_api.g_varchar2
36 ,p_set_of_books_id in number default hr_api.g_number
37 ,p_source_type in varchar2 default hr_api.g_varchar2
38 ,p_time_normal_finish in varchar2 default hr_api.g_varchar2
39 ,p_time_normal_start in varchar2 default hr_api.g_varchar2
40 ,p_ass_attribute_category in varchar2 default hr_api.g_varchar2
41 ,p_ass_attribute1 in varchar2 default hr_api.g_varchar2
42 ,p_ass_attribute2 in varchar2 default hr_api.g_varchar2
43 ,p_ass_attribute3 in varchar2 default hr_api.g_varchar2
44 ,p_ass_attribute4 in varchar2 default hr_api.g_varchar2
45 ,p_ass_attribute5 in varchar2 default hr_api.g_varchar2
46 ,p_ass_attribute6 in varchar2 default hr_api.g_varchar2
47 ,p_ass_attribute7 in varchar2 default hr_api.g_varchar2
48 ,p_ass_attribute8 in varchar2 default hr_api.g_varchar2
49 ,p_ass_attribute9 in varchar2 default hr_api.g_varchar2
50 ,p_ass_attribute10 in varchar2 default hr_api.g_varchar2
51 ,p_ass_attribute11 in varchar2 default hr_api.g_varchar2
52 ,p_ass_attribute12 in varchar2 default hr_api.g_varchar2
53 ,p_ass_attribute13 in varchar2 default hr_api.g_varchar2
54 ,p_ass_attribute14 in varchar2 default hr_api.g_varchar2
55 ,p_ass_attribute15 in varchar2 default hr_api.g_varchar2
56 ,p_ass_attribute16 in varchar2 default hr_api.g_varchar2
57 ,p_ass_attribute17 in varchar2 default hr_api.g_varchar2
58 ,p_ass_attribute18 in varchar2 default hr_api.g_varchar2
59 ,p_ass_attribute19 in varchar2 default hr_api.g_varchar2
60 ,p_ass_attribute20 in varchar2 default hr_api.g_varchar2
61 ,p_ass_attribute21 in varchar2 default hr_api.g_varchar2
62 ,p_ass_attribute22 in varchar2 default hr_api.g_varchar2
63 ,p_ass_attribute23 in varchar2 default hr_api.g_varchar2
64 ,p_ass_attribute24 in varchar2 default hr_api.g_varchar2
65 ,p_ass_attribute25 in varchar2 default hr_api.g_varchar2
66 ,p_ass_attribute26 in varchar2 default hr_api.g_varchar2
67 ,p_ass_attribute27 in varchar2 default hr_api.g_varchar2
68 ,p_ass_attribute28 in varchar2 default hr_api.g_varchar2
69 ,p_ass_attribute29 in varchar2 default hr_api.g_varchar2
70 ,p_ass_attribute30 in varchar2 default hr_api.g_varchar2
71 ,p_title in varchar2 default hr_api.g_varchar2
72 ,p_project_title in varchar2 default hr_api.g_varchar2
73 ,p_vendor_assignment_number in varchar2 default hr_api.g_varchar2
74 ,p_vendor_employee_number in varchar2 default hr_api.g_varchar2
75 ,p_vendor_id in number default hr_api.g_number
76 ,p_assignment_type in varchar2
77 ,p_comment_id out nocopy number
78 ,p_effective_start_date out nocopy date
79 ,p_effective_end_date out nocopy date
80 ,p_no_managers_warning out nocopy boolean
81 ,p_other_manager_warning out nocopy boolean
82 ) is
83 l_proc varchar2(72) := g_package||'update_asg';
84 l_effective_date date := trunc(p_effective_date);
85 l_constant_effective_date constant date := l_effective_date;
86 l_effective_date_row boolean := true;
87 l_validation_start_date date;
88 l_validation_end_date date;
89 l_datetrack_update_mode varchar2(30);
90 l_correction boolean;
91 l_update boolean;
92 l_update_override boolean;
93 l_update_change_insert boolean;
94 l_lck_start_date date;
95 --
96 lv_object_version_number number := p_object_version_number ;
97 --
98 -- --------------------------------------------------------------------------
99 -- local cursor definitions
100 -- --------------------------------------------------------------------------
101 -- csr_asg_lck -> locks all the datetracked rows for the specified assignment
102 -- from the specified lock date. this enforces integrity.
103 -- if the datetrack operation is for an ATTRIBUTE_UPDATE
104 -- then only the current and future rows will be locked. if
105 -- the datetrack operation is a ATTRIBUTE_CORRECTION then
106 -- all assignment rows are locked as we cannot guarantee how
107 -- many rows will be changed.
108 -- csr_asg1 -> selects assignment details for the current and future rows
109 -- csr_asg2 -> selects assignment details in the past in a descending
110 -- order not including the current row as of the effective
111 -- date.
112 --
113 -- note: the cursors csr_asg1 and csr_asg2 are specifically not merged
114 -- because of the of the order by clause
115 --
116 -- cursor to lock all rows for which the datetrack operation could
117 -- operate over
118 cursor csr_asg_lck(c_lck_start_date date) is
119 select 1
120 from per_all_assignments_f asg
121 where asg.assignment_id = p_assignment_id
122 and asg.effective_end_date >= c_lck_start_date
123 for update nowait;
124 -- select current and future rows
125 cursor csr_asg1 is
126 select
127 asg.object_version_number
128 ,asg.supervisor_id
129 -- Assignment Security
130 ,asg.supervisor_assignment_id
131
132 ,asg.assignment_number
133 ,asg.change_reason
134 ,asg.date_probation_end
135 ,asg.default_code_comb_id
136 ,asg.frequency
137 ,asg.internal_address_line
138 ,asg.manager_flag
139 ,asg.normal_hours
140 ,asg.perf_review_period
141 ,asg.perf_review_period_frequency
142 ,asg.probation_period
143 ,asg.probation_unit
144 ,asg.sal_review_period
145 ,asg.sal_review_period_frequency
146 ,asg.set_of_books_id
147 ,asg.source_type
148 ,asg.time_normal_finish
149 ,asg.time_normal_start
150 ,asg.ass_attribute_category
151 ,asg.ass_attribute1
152 ,asg.ass_attribute2
153 ,asg.ass_attribute3
154 ,asg.ass_attribute4
155 ,asg.ass_attribute5
156 ,asg.ass_attribute6
157 ,asg.ass_attribute7
158 ,asg.ass_attribute8
159 ,asg.ass_attribute9
160 ,asg.ass_attribute10
161 ,asg.ass_attribute11
162 ,asg.ass_attribute12
163 ,asg.ass_attribute13
164 ,asg.ass_attribute14
165 ,asg.ass_attribute15
166 ,asg.ass_attribute16
167 ,asg.ass_attribute17
168 ,asg.ass_attribute18
169 ,asg.ass_attribute19
170 ,asg.ass_attribute20
171 ,asg.ass_attribute21
172 ,asg.ass_attribute22
173 ,asg.ass_attribute23
174 ,asg.ass_attribute24
175 ,asg.ass_attribute25
176 ,asg.ass_attribute26
177 ,asg.ass_attribute27
178 ,asg.ass_attribute28
179 ,asg.ass_attribute29
180 ,asg.ass_attribute30
181 ,asg.title
182 ,asg.effective_start_date
183 ,asg.effective_end_date
184 ,hc.comment_text
185 ,asg.project_title
186 ,asg.vendor_assignment_number
187 ,asg.vendor_employee_number
188 ,asg.vendor_id
189 ,asg.soft_coding_keyflex_id
190 from hr_comments hc
191 , per_all_assignments_f asg
192 where asg.assignment_id=p_assignment_id
193 and asg.effective_end_date >= l_constant_effective_date
194 and hc.comment_id(+) = asg.comment_id
195 order by asg.effective_end_date asc;
196 -- select past rows not including the current rows
197 cursor csr_asg2 is
198 select
199 asg.object_version_number
200 ,asg.supervisor_id
201 -- Assignment Security
202 ,asg.supervisor_assignment_id
203 ,asg.assignment_number
204 ,asg.change_reason
205 ,asg.date_probation_end
206 ,asg.default_code_comb_id
207 ,asg.frequency
208 ,asg.internal_address_line
209 ,asg.manager_flag
210 ,asg.normal_hours
211 ,asg.perf_review_period
212 ,asg.perf_review_period_frequency
213 ,asg.probation_period
214 ,asg.probation_unit
215 ,asg.sal_review_period
216 ,asg.sal_review_period_frequency
217 ,asg.set_of_books_id
218 ,asg.source_type
219 ,asg.time_normal_finish
220 ,asg.time_normal_start
221 ,asg.ass_attribute_category
222 ,asg.ass_attribute1
223 ,asg.ass_attribute2
224 ,asg.ass_attribute3
225 ,asg.ass_attribute4
226 ,asg.ass_attribute5
227 ,asg.ass_attribute6
228 ,asg.ass_attribute7
229 ,asg.ass_attribute8
230 ,asg.ass_attribute9
231 ,asg.ass_attribute10
232 ,asg.ass_attribute11
233 ,asg.ass_attribute12
234 ,asg.ass_attribute13
235 ,asg.ass_attribute14
236 ,asg.ass_attribute15
237 ,asg.ass_attribute16
238 ,asg.ass_attribute17
239 ,asg.ass_attribute18
240 ,asg.ass_attribute19
241 ,asg.ass_attribute20
242 ,asg.ass_attribute21
243 ,asg.ass_attribute22
244 ,asg.ass_attribute23
245 ,asg.ass_attribute24
246 ,asg.ass_attribute25
247 ,asg.ass_attribute26
248 ,asg.ass_attribute27
249 ,asg.ass_attribute28
250 ,asg.ass_attribute29
251 ,asg.ass_attribute30
252 ,asg.title
253 ,asg.effective_start_date
254 ,asg.effective_end_date
255 ,hc.comment_text
256 ,asg.project_title
257 ,asg.vendor_assignment_number
258 ,asg.vendor_employee_number
259 ,asg.vendor_id
260 ,asg.soft_coding_keyflex_id
261 from hr_comments hc
262 , per_all_assignments_f asg
263 where asg.assignment_id=p_assignment_id
264 and asg.effective_end_date < l_constant_effective_date
265 and hc.comment_id(+) = asg.comment_id
266 order by asg.effective_end_date desc;
267
268 cursor scf_id is
269 select soft_coding_keyflex_id from
270 per_all_assignments_f asg where asg.assignment_id=p_assignment_id
271 and asg.effective_end_date >= l_constant_effective_date
272 order by asg.effective_end_date asc;
273
274 -- IN parameters for API
275 l_asg_rec per_all_assignments_f%rowtype;
276 l_comments hr_comments.comment_text%TYPE;
277 -- OUT parameters for API
278 l_soft_coding_keyflex_id number;
279 l_comment_id number;
280 l_effective_start_date date;
281 l_effective_end_date date;
282 l_concatenated_segments hr_soft_coding_keyflex.concatenated_segments%TYPE;
283 l_no_managers_warning boolean;
284 l_other_manager_warning boolean;
285 l_api_no_managers_warning boolean := false;
286 l_org_now_no_manager_warning boolean;
287 l_hourly_salaried_warning boolean;
288 l_api_other_manager_warning boolean := false;
289 l_add_struct hr_kflex_utility.l_ignore_kfcode_varray := hr_kflex_utility.l_ignore_kfcode_varray();
290 --
291 -- --------------------------------------------------------------------------
292 -- |---------------------------< process_row >------------------------------|
293 -- --------------------------------------------------------------------------
294 -- {Start Of Comments}
295 --
296 -- Description:
297 -- This private function is used to determine the correct attribute values
298 -- to pass to the API.
299 --
300 -- 1. Determine the parameter value to be passed to the API
301 -- 2. If at least one parameter value is changing then call the API
302 -- else exit function
303 -- 3. Set any parameters which have been supplied by the resulting call
304 -- to the API
305 --
306 -- Pre Conditions:
307 -- A row must be active from the cursor csr_asg1 or csr_asg2
308 --
309 -- In Arguments:
310 -- All the IN arguments hold the current selected cursor row values.
311 --
312 -- Post Success:
313 -- Ths function will return either TRUE or FALSE.
314 -- If TRUE is returned, the row has been processed succesfully and
315 -- attributes could possibly still be processed.
316 -- If FALSE is returned, the row has been processed succesfully
317 -- and all the attributes have been updated as far as possible.
318 --
319 -- Post Failure:
320 -- Exceptions are not handled, just raised.
321 --
322 -- Developer Implementation Notes:
323 -- None
324 --
325 -- Access Status:
326 -- Internal to owning procedure.
327 --
328 -- {End Of Comments}
329 -- --------------------------------------------------------------------------
330 function process_row
331 (c_effective_start_date in date
332 ,c_object_version_number in number
333 ,c_supervisor_id in number default hr_api.g_number
334 ,c_supervisor_assignment_id in number default hr_api.g_number
335
336 ,c_assignment_number in varchar2 default hr_api.g_varchar2
337 ,c_change_reason in varchar2 default hr_api.g_varchar2
338 ,c_comments in varchar2 default hr_api.g_varchar2
339 ,c_date_probation_end in date default hr_api.g_date
340 ,c_default_code_comb_id in number default hr_api.g_number
341 ,c_frequency in varchar2 default hr_api.g_varchar2
342 ,c_internal_address_line in varchar2 default hr_api.g_varchar2
343 ,c_manager_flag in varchar2 default hr_api.g_varchar2
344 ,c_normal_hours in number default hr_api.g_number
345 ,c_perf_review_period in number default hr_api.g_number
346 ,c_perf_review_period_frequency in varchar2 default hr_api.g_varchar2
347 ,c_probation_period in number default hr_api.g_number
348 ,c_probation_unit in varchar2 default hr_api.g_varchar2
349 ,c_sal_review_period in number default hr_api.g_number
350 ,c_sal_review_period_frequency in varchar2 default hr_api.g_varchar2
351 ,c_set_of_books_id in number default hr_api.g_number
352 ,c_source_type in varchar2 default hr_api.g_varchar2
353 ,c_time_normal_finish in varchar2 default hr_api.g_varchar2
354 ,c_time_normal_start in varchar2 default hr_api.g_varchar2
355 ,c_ass_attribute_category in varchar2 default hr_api.g_varchar2
356 ,c_ass_attribute1 in varchar2 default hr_api.g_varchar2
357 ,c_ass_attribute2 in varchar2 default hr_api.g_varchar2
358 ,c_ass_attribute3 in varchar2 default hr_api.g_varchar2
359 ,c_ass_attribute4 in varchar2 default hr_api.g_varchar2
360 ,c_ass_attribute5 in varchar2 default hr_api.g_varchar2
361 ,c_ass_attribute6 in varchar2 default hr_api.g_varchar2
362 ,c_ass_attribute7 in varchar2 default hr_api.g_varchar2
363 ,c_ass_attribute8 in varchar2 default hr_api.g_varchar2
364 ,c_ass_attribute9 in varchar2 default hr_api.g_varchar2
365 ,c_ass_attribute10 in varchar2 default hr_api.g_varchar2
366 ,c_ass_attribute11 in varchar2 default hr_api.g_varchar2
367 ,c_ass_attribute12 in varchar2 default hr_api.g_varchar2
368 ,c_ass_attribute13 in varchar2 default hr_api.g_varchar2
369 ,c_ass_attribute14 in varchar2 default hr_api.g_varchar2
370 ,c_ass_attribute15 in varchar2 default hr_api.g_varchar2
371 ,c_ass_attribute16 in varchar2 default hr_api.g_varchar2
372 ,c_ass_attribute17 in varchar2 default hr_api.g_varchar2
373 ,c_ass_attribute18 in varchar2 default hr_api.g_varchar2
374 ,c_ass_attribute19 in varchar2 default hr_api.g_varchar2
375 ,c_ass_attribute20 in varchar2 default hr_api.g_varchar2
376 ,c_ass_attribute21 in varchar2 default hr_api.g_varchar2
377 ,c_ass_attribute22 in varchar2 default hr_api.g_varchar2
378 ,c_ass_attribute23 in varchar2 default hr_api.g_varchar2
379 ,c_ass_attribute24 in varchar2 default hr_api.g_varchar2
380 ,c_ass_attribute25 in varchar2 default hr_api.g_varchar2
381 ,c_ass_attribute26 in varchar2 default hr_api.g_varchar2
382 ,c_ass_attribute27 in varchar2 default hr_api.g_varchar2
383 ,c_ass_attribute28 in varchar2 default hr_api.g_varchar2
384 ,c_ass_attribute29 in varchar2 default hr_api.g_varchar2
385 ,c_ass_attribute30 in varchar2 default hr_api.g_varchar2
386 ,c_title in varchar2 default hr_api.g_varchar2
387 ,c_project_title in varchar2 default hr_api.g_varchar2
388 ,c_vendor_assignment_number in varchar2 default hr_api.g_varchar2
389 ,c_vendor_employee_number in varchar2 default hr_api.g_varchar2
390 ,c_vendor_id in number default hr_api.g_number
391 ,c_soft_coding_keyflex_id in number default hr_api.g_number
392 )
393 return boolean is
394 l_proc varchar2(72) := g_package||'process_row';
395 begin
396 hr_utility.set_location('Entering:'|| l_proc, 5);
397 -- get the parameter values to pass to the API
398
399 -- if (l_effective_date_row) then
400
401 l_asg_rec.supervisor_id:= hr_dt_attribute_support.get_parameter_number
402 (p_effective_date_row => l_effective_date_row
403 ,p_parameter_name => 'P_SUPERVISOR_ID'
404 ,p_new_value => p_supervisor_id
405 ,p_current_value => c_supervisor_id);
406
407 -- Assignment Security
408 l_asg_rec.supervisor_assignment_id:= hr_dt_attribute_support.get_parameter_number
409 (p_effective_date_row => l_effective_date_row
410 ,p_parameter_name => 'P_SUPERVISOR_ASSIGNMENT_ID'
411 ,p_new_value => p_supervisor_assignment_id
412 ,p_current_value => c_supervisor_assignment_id);
413
414
415 l_asg_rec.assignment_number:= hr_dt_attribute_support.get_parameter_char
416 (p_effective_date_row => l_effective_date_row
417 ,p_parameter_name => 'p_assignment_number'
418 ,p_new_value => p_assignment_number
419 ,p_current_value => c_assignment_number);
420
421 l_asg_rec.change_reason:= hr_dt_attribute_support.get_parameter_char
422 (p_effective_date_row => l_effective_date_row
423 ,p_parameter_name => 'p_change_reason'
424 ,p_new_value => p_change_reason
425 ,p_current_value => c_change_reason);
426
427 l_comments:= hr_dt_attribute_support.get_parameter_char
428 (p_effective_date_row => l_effective_date_row
429 ,p_parameter_name => 'p_comments'
430 ,p_new_value => p_comments
431 ,p_current_value => c_comments);
432
433 l_asg_rec.date_probation_end:= hr_dt_attribute_support.get_parameter_date
434 (p_effective_date_row => l_effective_date_row
435 ,p_parameter_name => 'p_date_probation_end'
436 ,p_new_value => p_date_probation_end
437 ,p_current_value => c_date_probation_end);
438
439
440 l_asg_rec.default_code_comb_id:= hr_dt_attribute_support.get_parameter_number
441 (p_effective_date_row => l_effective_date_row
442 ,p_parameter_name => 'p_default_code_comb_id'
443 ,p_new_value => p_default_code_comb_id
444 ,p_current_value => c_default_code_comb_id);
445
446 l_asg_rec.frequency:= hr_dt_attribute_support.get_parameter_char
447 (p_effective_date_row => l_effective_date_row
448 ,p_parameter_name => 'p_frequency'
449 ,p_new_value => p_frequency
450 ,p_current_value => c_frequency);
451
452 l_asg_rec.internal_address_line:= hr_dt_attribute_support.get_parameter_char
453 (p_effective_date_row => l_effective_date_row
454 ,p_parameter_name => 'p_internal_address_line'
455 ,p_new_value => p_internal_address_line
456 ,p_current_value => c_internal_address_line);
457
458 l_asg_rec.manager_flag:= hr_dt_attribute_support.get_parameter_char
459 (p_effective_date_row => l_effective_date_row
460 ,p_parameter_name => 'p_manager_flag'
461 ,p_new_value => p_manager_flag
462 ,p_current_value => c_manager_flag);
463
464 l_asg_rec.normal_hours:= hr_dt_attribute_support.get_parameter_number
465 (p_effective_date_row => l_effective_date_row
466 ,p_parameter_name => 'p_normal_hours'
467 ,p_new_value => p_normal_hours
468 ,p_current_value => c_normal_hours);
469
470 l_asg_rec.perf_review_period:= hr_dt_attribute_support.get_parameter_number
471 (p_effective_date_row => l_effective_date_row
472 ,p_parameter_name => 'p_perf_review_period'
473 ,p_new_value => p_perf_review_period
474 ,p_current_value => c_perf_review_period);
475
476 l_asg_rec.perf_review_period_frequency:= hr_dt_attribute_support.get_parameter_char
477 (p_effective_date_row => l_effective_date_row
478 ,p_parameter_name => 'p_perf_review_period_frequency'
479 ,p_new_value => p_perf_review_period_frequency
480 ,p_current_value => c_perf_review_period_frequency);
481
482 l_asg_rec.probation_period:= hr_dt_attribute_support.get_parameter_number
483 (p_effective_date_row => l_effective_date_row
484 ,p_parameter_name => 'p_probation_period'
485 ,p_new_value => p_probation_period
486 ,p_current_value => c_probation_period);
487
488 l_asg_rec.probation_unit:= hr_dt_attribute_support.get_parameter_char
489 (p_effective_date_row => l_effective_date_row
490 ,p_parameter_name => 'p_probation_unit'
491 ,p_new_value => p_probation_unit
492 ,p_current_value => c_probation_unit);
493
494 l_asg_rec.sal_review_period:= hr_dt_attribute_support.get_parameter_number
495 (p_effective_date_row => l_effective_date_row
496 ,p_parameter_name => 'p_sal_review_period'
497 ,p_new_value => p_sal_review_period
498 ,p_current_value => c_sal_review_period);
499
500 l_asg_rec.sal_review_period_frequency:= hr_dt_attribute_support.get_parameter_char
501 (p_effective_date_row => l_effective_date_row
502 ,p_parameter_name => 'p_sal_review_period_frequency'
503 ,p_new_value => p_sal_review_period_frequency
504 ,p_current_value => c_sal_review_period_frequency);
505
506 l_asg_rec.set_of_books_id:= hr_dt_attribute_support.get_parameter_number
507 (p_effective_date_row => l_effective_date_row
508 ,p_parameter_name => 'p_set_of_books_id'
509 ,p_new_value => p_set_of_books_id
510 ,p_current_value => c_set_of_books_id);
511
512 l_asg_rec.source_type:= hr_dt_attribute_support.get_parameter_char
513 (p_effective_date_row => l_effective_date_row
514 ,p_parameter_name => 'p_source_type'
515 ,p_new_value => p_source_type
516 ,p_current_value => c_source_type);
517
518 l_asg_rec.time_normal_finish:= hr_dt_attribute_support.get_parameter_char
519 (p_effective_date_row => l_effective_date_row
520 ,p_parameter_name => 'p_time_normal_finish'
521 ,p_new_value => p_time_normal_finish
522 ,p_current_value => c_time_normal_finish);
523
524 l_asg_rec.time_normal_start:= hr_dt_attribute_support.get_parameter_char
525 (p_effective_date_row => l_effective_date_row
526 ,p_parameter_name => 'p_time_normal_start'
527 ,p_new_value => p_time_normal_start
528 ,p_current_value => c_time_normal_start);
529
530 l_asg_rec.ass_attribute_category:= hr_dt_attribute_support.get_parameter_char
531 (p_effective_date_row => l_effective_date_row
532 ,p_parameter_name => 'p_ass_attribute_category'
533 ,p_new_value => p_ass_attribute_category
534 ,p_current_value => c_ass_attribute_category);
535
536 l_asg_rec.ass_attribute1:= hr_dt_attribute_support.get_parameter_char
537 (p_effective_date_row => l_effective_date_row
538 ,p_parameter_name => 'p_ass_attribute1'
539 ,p_new_value => p_ass_attribute1
540 ,p_current_value => c_ass_attribute1);
541
542 l_asg_rec.ass_attribute2:= hr_dt_attribute_support.get_parameter_char
543 (p_effective_date_row => l_effective_date_row
544 ,p_parameter_name => 'p_ass_attribute2'
545 ,p_new_value => p_ass_attribute2
546 ,p_current_value => c_ass_attribute2);
547
548 l_asg_rec.ass_attribute3:= hr_dt_attribute_support.get_parameter_char
549 (p_effective_date_row => l_effective_date_row
550 ,p_parameter_name => 'p_ass_attribute3'
551 ,p_new_value => p_ass_attribute3
552 ,p_current_value => c_ass_attribute3);
553
554 l_asg_rec.ass_attribute4:= hr_dt_attribute_support.get_parameter_char
555 (p_effective_date_row => l_effective_date_row
556 ,p_parameter_name => 'p_ass_attribute4'
557 ,p_new_value => p_ass_attribute4
558 ,p_current_value => c_ass_attribute4);
559
560 l_asg_rec.ass_attribute5:= hr_dt_attribute_support.get_parameter_char
561 (p_effective_date_row => l_effective_date_row
562 ,p_parameter_name => 'p_ass_attribute5'
563 ,p_new_value => p_ass_attribute5
564 ,p_current_value => c_ass_attribute5);
565
566 l_asg_rec.ass_attribute6:= hr_dt_attribute_support.get_parameter_char
567 (p_effective_date_row => l_effective_date_row
568 ,p_parameter_name => 'p_ass_attribute6'
569 ,p_new_value => p_ass_attribute6
570 ,p_current_value => c_ass_attribute6);
571
572 l_asg_rec.ass_attribute7:= hr_dt_attribute_support.get_parameter_char
573 (p_effective_date_row => l_effective_date_row
574 ,p_parameter_name => 'p_ass_attribute7'
575 ,p_new_value => p_ass_attribute7
576 ,p_current_value => c_ass_attribute7);
577
578 l_asg_rec.ass_attribute8:= hr_dt_attribute_support.get_parameter_char
579 (p_effective_date_row => l_effective_date_row
580 ,p_parameter_name => 'p_ass_attribute8'
581 ,p_new_value => p_ass_attribute8
582 ,p_current_value => c_ass_attribute8);
583
584 l_asg_rec.ass_attribute9:= hr_dt_attribute_support.get_parameter_char
585 (p_effective_date_row => l_effective_date_row
586 ,p_parameter_name => 'p_ass_attribute9'
587 ,p_new_value => p_ass_attribute9
588 ,p_current_value => c_ass_attribute9);
589
590 l_asg_rec.ass_attribute10:= hr_dt_attribute_support.get_parameter_char
591 (p_effective_date_row => l_effective_date_row
592 ,p_parameter_name => 'p_ass_attribute10'
593 ,p_new_value => p_ass_attribute10
594 ,p_current_value => c_ass_attribute10);
595
596
597 l_asg_rec.ass_attribute11:= hr_dt_attribute_support.get_parameter_char
598 (p_effective_date_row => l_effective_date_row
599 ,p_parameter_name => 'p_ass_attribute11'
600 ,p_new_value => p_ass_attribute11
601 ,p_current_value => c_ass_attribute11);
602
603 l_asg_rec.ass_attribute12:= hr_dt_attribute_support.get_parameter_char
604 (p_effective_date_row => l_effective_date_row
605 ,p_parameter_name => 'p_ass_attribute12'
606 ,p_new_value => p_ass_attribute12
607 ,p_current_value => c_ass_attribute12);
608
609 l_asg_rec.ass_attribute13:= hr_dt_attribute_support.get_parameter_char
610 (p_effective_date_row => l_effective_date_row
611 ,p_parameter_name => 'p_ass_attribute13'
612 ,p_new_value => p_ass_attribute13
613 ,p_current_value => c_ass_attribute13);
614
615 l_asg_rec.ass_attribute14:= hr_dt_attribute_support.get_parameter_char
616 (p_effective_date_row => l_effective_date_row
617 ,p_parameter_name => 'p_ass_attribute14'
618 ,p_new_value => p_ass_attribute14
619 ,p_current_value => c_ass_attribute14);
620
621 l_asg_rec.ass_attribute15:= hr_dt_attribute_support.get_parameter_char
622 (p_effective_date_row => l_effective_date_row
623 ,p_parameter_name => 'p_ass_attribute15'
624 ,p_new_value => p_ass_attribute15
625 ,p_current_value => c_ass_attribute15);
626
627 l_asg_rec.ass_attribute16:= hr_dt_attribute_support.get_parameter_char
628 (p_effective_date_row => l_effective_date_row
629 ,p_parameter_name => 'p_ass_attribute16'
630 ,p_new_value => p_ass_attribute16
631 ,p_current_value => c_ass_attribute16);
632
633 l_asg_rec.ass_attribute17:= hr_dt_attribute_support.get_parameter_char
634 (p_effective_date_row => l_effective_date_row
635 ,p_parameter_name => 'p_ass_attribute17'
636 ,p_new_value => p_ass_attribute17
637 ,p_current_value => c_ass_attribute17);
638
639 l_asg_rec.ass_attribute18:= hr_dt_attribute_support.get_parameter_char
640 (p_effective_date_row => l_effective_date_row
641 ,p_parameter_name => 'p_ass_attribute18'
642 ,p_new_value => p_ass_attribute18
643 ,p_current_value => c_ass_attribute18);
644
645 l_asg_rec.ass_attribute19:= hr_dt_attribute_support.get_parameter_char
646 (p_effective_date_row => l_effective_date_row
647 ,p_parameter_name => 'p_ass_attribute19'
648 ,p_new_value => p_ass_attribute19
649 ,p_current_value => c_ass_attribute19);
650
651 l_asg_rec.ass_attribute20:= hr_dt_attribute_support.get_parameter_char
652 (p_effective_date_row => l_effective_date_row
653 ,p_parameter_name => 'p_ass_attribute20'
654 ,p_new_value => p_ass_attribute20
655 ,p_current_value => c_ass_attribute20);
656
657 l_asg_rec.ass_attribute21:= hr_dt_attribute_support.get_parameter_char
658 (p_effective_date_row => l_effective_date_row
659 ,p_parameter_name => 'p_ass_attribute21'
660 ,p_new_value => p_ass_attribute21
661 ,p_current_value => c_ass_attribute21);
662
663 l_asg_rec.ass_attribute22:= hr_dt_attribute_support.get_parameter_char
664 (p_effective_date_row => l_effective_date_row
665 ,p_parameter_name => 'p_ass_attribute22'
666 ,p_new_value => p_ass_attribute22
667 ,p_current_value => c_ass_attribute22);
668
669 l_asg_rec.ass_attribute23:= hr_dt_attribute_support.get_parameter_char
670 (p_effective_date_row => l_effective_date_row
671 ,p_parameter_name => 'p_ass_attribute23'
672 ,p_new_value => p_ass_attribute23
673 ,p_current_value => c_ass_attribute23);
674
675 l_asg_rec.ass_attribute24:= hr_dt_attribute_support.get_parameter_char
676 (p_effective_date_row => l_effective_date_row
677 ,p_parameter_name => 'p_ass_attribute24'
678 ,p_new_value => p_ass_attribute24
679 ,p_current_value => c_ass_attribute24);
680
681 l_asg_rec.ass_attribute25:= hr_dt_attribute_support.get_parameter_char
682 (p_effective_date_row => l_effective_date_row
683 ,p_parameter_name => 'p_ass_attribute25'
684 ,p_new_value => p_ass_attribute25
685 ,p_current_value => c_ass_attribute25);
686
687 l_asg_rec.ass_attribute26:= hr_dt_attribute_support.get_parameter_char
688 (p_effective_date_row => l_effective_date_row
689 ,p_parameter_name => 'p_ass_attribute26'
690 ,p_new_value => p_ass_attribute26
691 ,p_current_value => c_ass_attribute26);
692
693 l_asg_rec.ass_attribute27:= hr_dt_attribute_support.get_parameter_char
694 (p_effective_date_row => l_effective_date_row
695 ,p_parameter_name => 'p_ass_attribute27'
696 ,p_new_value => p_ass_attribute27
697 ,p_current_value => c_ass_attribute27);
698
699 l_asg_rec.ass_attribute28:= hr_dt_attribute_support.get_parameter_char
700 (p_effective_date_row => l_effective_date_row
701 ,p_parameter_name => 'p_ass_attribute28'
702 ,p_new_value => p_ass_attribute28
703 ,p_current_value => c_ass_attribute28);
704
705 l_asg_rec.ass_attribute29:= hr_dt_attribute_support.get_parameter_char
706 (p_effective_date_row => l_effective_date_row
707 ,p_parameter_name => 'p_ass_attribute29'
708 ,p_new_value => p_ass_attribute29
709 ,p_current_value => c_ass_attribute29);
710
711 l_asg_rec.ass_attribute30:= hr_dt_attribute_support.get_parameter_char
712 (p_effective_date_row => l_effective_date_row
713 ,p_parameter_name => 'p_ass_attribute30'
714 ,p_new_value => p_ass_attribute30
715 ,p_current_value => c_ass_attribute30);
716
717 l_asg_rec.title:= hr_dt_attribute_support.get_parameter_char
718 (p_effective_date_row => l_effective_date_row
719 ,p_parameter_name => 'p_title'
720 ,p_new_value => p_title
721 ,p_current_value => c_title);
722
723 l_asg_rec.project_title:= hr_dt_attribute_support.get_parameter_char
724 (p_effective_date_row => l_effective_date_row
725 ,p_parameter_name => 'p_project_title'
726 ,p_new_value => p_project_title
727 ,p_current_value => c_project_title);
728
729 l_asg_rec.vendor_assignment_number:=
730 hr_dt_attribute_support.get_parameter_char
731 (p_effective_date_row => l_effective_date_row
732 ,p_parameter_name => 'p_vendor_assignment_number'
733 ,p_new_value => p_vendor_assignment_number
734 ,p_current_value => c_vendor_assignment_number);
735
736 l_asg_rec.vendor_employee_number:=
737 hr_dt_attribute_support.get_parameter_char
738 (p_effective_date_row => l_effective_date_row
739 ,p_parameter_name => 'p_vendor_employee_number'
740 ,p_new_value => p_vendor_employee_number
741 ,p_current_value => c_vendor_employee_number);
742
743 l_asg_rec.vendor_id:= hr_dt_attribute_support.get_parameter_number
744 (p_effective_date_row => l_effective_date_row
745 ,p_parameter_name => 'p_vendor_id'
746 ,p_new_value => p_vendor_id
747 ,p_current_value => c_vendor_id);
748
749 hr_utility.set_location(' c_soft_coding_keyflex_id '||c_soft_coding_keyflex_id, 10);
750 if ( c_soft_coding_keyflex_id is not null)
751 then
752 l_asg_rec.SOFT_CODING_KEYFLEX_ID := c_soft_coding_keyflex_id;
753 end if;
754 hr_utility.set_location(' l_asg_rec.SOFT_CODING_KEYFLEX_ID '||l_asg_rec.SOFT_CODING_KEYFLEX_ID, 10);
755 -- end if;
756
757 --
758 -- call the API if at least one attribute can be changed
759 if hr_dt_attribute_support.is_current_row_changing then
760 -- set the object version number and effective date
761 if l_effective_date_row then
762 -- as we are on the first row, the ovn and effective date should be
763 -- set to the parameter specified by the caller
764 l_asg_rec.object_version_number := p_object_version_number;
765 l_effective_date := l_constant_effective_date;
766 else
767 -- as we are not on the first row, set the ovn and effective date
768 -- to the ovn and effective date for the row
769 l_asg_rec.object_version_number := c_object_version_number;
770 l_effective_date := c_effective_start_date;
771 end if;
772
773 if (p_assignment_type = 'C') then
774 -- call cwk api
775 hr_assignment_api.update_cwk_asg
776 (p_effective_date => l_effective_date
777 ,p_datetrack_update_mode => l_datetrack_update_mode
778 ,p_assignment_id => p_assignment_id
779 ,p_object_version_number => l_asg_rec.object_version_number
780 -- ,p_assignment_category => l_asg_rec.assignment_category
781 ,p_assignment_number => l_asg_rec.assignment_number
782 ,p_change_reason => l_asg_rec.change_reason
783 ,p_comments => l_comments
784 ,p_default_code_comb_id => l_asg_rec.default_code_comb_id
785 ,p_frequency => l_asg_rec.frequency
786 ,p_internal_address_line => l_asg_rec.internal_address_line
787 ,p_manager_flag => l_asg_rec.manager_flag
788 ,p_normal_hours => l_asg_rec.normal_hours
789 ,p_set_of_books_id => l_asg_rec.set_of_books_id
790 ,p_source_type => l_asg_rec.source_type
791 ,p_supervisor_id => l_asg_rec.supervisor_id
792
793 ,p_time_normal_finish => l_asg_rec.time_normal_finish
794 ,p_time_normal_start => l_asg_rec.time_normal_start
795 ,p_title => l_asg_rec.title
796 ,p_project_title => l_asg_rec.project_title
797 ,p_vendor_assignment_number => l_asg_rec.vendor_assignment_number
798 ,p_vendor_employee_number => l_asg_rec.vendor_employee_number
799 ,p_vendor_id => l_asg_rec.vendor_id
800 --,p_assignment_status_type_id => l_asg_rec.assignment_status_type_id
801 ,p_attribute_category => l_asg_rec.ass_attribute_category
802 ,p_attribute1 => l_asg_rec.ass_attribute1
803 ,p_attribute2 => l_asg_rec.ass_attribute2
804 ,p_attribute3 => l_asg_rec.ass_attribute3
805 ,p_attribute4 => l_asg_rec.ass_attribute4
806 ,p_attribute5 => l_asg_rec.ass_attribute5
807 ,p_attribute6 => l_asg_rec.ass_attribute6
808 ,p_attribute7 => l_asg_rec.ass_attribute7
809 ,p_attribute8 => l_asg_rec.ass_attribute8
810 ,p_attribute9 => l_asg_rec.ass_attribute9
811 ,p_attribute10 => l_asg_rec.ass_attribute10
812 ,p_attribute11 => l_asg_rec.ass_attribute11
813 ,p_attribute12 => l_asg_rec.ass_attribute12
814 ,p_attribute13 => l_asg_rec.ass_attribute13
815 ,p_attribute14 => l_asg_rec.ass_attribute14
816 ,p_attribute15 => l_asg_rec.ass_attribute15
817 ,p_attribute16 => l_asg_rec.ass_attribute16
818 ,p_attribute17 => l_asg_rec.ass_attribute17
819 ,p_attribute18 => l_asg_rec.ass_attribute18
820 ,p_attribute19 => l_asg_rec.ass_attribute19
821 ,p_attribute20 => l_asg_rec.ass_attribute20
822 ,p_attribute21 => l_asg_rec.ass_attribute21
823 ,p_attribute22 => l_asg_rec.ass_attribute22
824 ,p_attribute23 => l_asg_rec.ass_attribute23
825 ,p_attribute24 => l_asg_rec.ass_attribute24
826 ,p_attribute25 => l_asg_rec.ass_attribute25
827 ,p_attribute26 => l_asg_rec.ass_attribute26
828 ,p_attribute27 => l_asg_rec.ass_attribute27
829 ,p_attribute28 => l_asg_rec.ass_attribute28
830 ,p_attribute29 => l_asg_rec.ass_attribute29
831 ,p_attribute30 => l_asg_rec.ass_attribute30
832
833 -- Assignment Security
834 ,p_supervisor_assignment_id => l_asg_rec.supervisor_assignment_id
835
836 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
837 ,p_effective_start_date => l_effective_start_date
838 ,p_effective_end_date => l_effective_end_date
839 ,p_comment_id => l_comment_id
840 ,p_no_managers_warning => l_no_managers_warning
841 ,p_other_manager_warning => l_other_manager_warning
842 ,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
843 ,p_concatenated_segments => l_concatenated_segments
844 ,p_hourly_salaried_warning => l_hourly_salaried_warning
845 );
846 else
847 -- call emp API
848 hr_assignment_api.update_emp_asg
849 (p_effective_date => l_effective_date
850 ,p_datetrack_update_mode => l_datetrack_update_mode
851 ,p_assignment_id => p_assignment_id
852 ,p_object_version_number => l_asg_rec.object_version_number
853 ,p_supervisor_id => l_asg_rec.supervisor_id
854 ,p_assignment_number => l_asg_rec.assignment_number
855 ,p_change_reason => l_asg_rec.change_reason
856 ,p_comments => l_comments
857 ,p_date_probation_end => l_asg_rec.date_probation_end
858 ,p_default_code_comb_id => l_asg_rec.default_code_comb_id
859 ,p_frequency => l_asg_rec.frequency
860 ,p_internal_address_line => l_asg_rec.internal_address_line
861 ,p_manager_flag => l_asg_rec.manager_flag
862 ,p_normal_hours => l_asg_rec.normal_hours
863 ,p_perf_review_period => l_asg_rec.perf_review_period
864 ,p_perf_review_period_frequency => l_asg_rec.perf_review_period_frequency
865 ,p_probation_period => l_asg_rec.probation_period
866 ,p_probation_unit => l_asg_rec.probation_unit
867 ,p_sal_review_period => l_asg_rec.sal_review_period
868 ,p_sal_review_period_frequency => l_asg_rec.sal_review_period_frequency
869 ,p_set_of_books_id => l_asg_rec.set_of_books_id
870 ,p_source_type => l_asg_rec.source_type
871 ,p_time_normal_finish => l_asg_rec.time_normal_finish
872 ,p_time_normal_start => l_asg_rec.time_normal_start
873 ,p_ass_attribute_category => l_asg_rec.ass_attribute_category
874 ,p_ass_attribute1 => l_asg_rec.ass_attribute1
875 ,p_ass_attribute2 => l_asg_rec.ass_attribute2
876 ,p_ass_attribute3 => l_asg_rec.ass_attribute3
877 ,p_ass_attribute4 => l_asg_rec.ass_attribute4
878 ,p_ass_attribute5 => l_asg_rec.ass_attribute5
879 ,p_ass_attribute6 => l_asg_rec.ass_attribute6
880 ,p_ass_attribute7 => l_asg_rec.ass_attribute7
881 ,p_ass_attribute8 => l_asg_rec.ass_attribute8
882 ,p_ass_attribute9 => l_asg_rec.ass_attribute9
883 ,p_ass_attribute10 => l_asg_rec.ass_attribute10
884 ,p_ass_attribute11 => l_asg_rec.ass_attribute11
885 ,p_ass_attribute12 => l_asg_rec.ass_attribute12
886 ,p_ass_attribute13 => l_asg_rec.ass_attribute13
887 ,p_ass_attribute14 => l_asg_rec.ass_attribute14
888 ,p_ass_attribute15 => l_asg_rec.ass_attribute15
889 ,p_ass_attribute16 => l_asg_rec.ass_attribute16
890 ,p_ass_attribute17 => l_asg_rec.ass_attribute17
891 ,p_ass_attribute18 => l_asg_rec.ass_attribute18
892 ,p_ass_attribute19 => l_asg_rec.ass_attribute19
893 ,p_ass_attribute20 => l_asg_rec.ass_attribute20
894 ,p_ass_attribute21 => l_asg_rec.ass_attribute21
895 ,p_ass_attribute22 => l_asg_rec.ass_attribute22
896 ,p_ass_attribute23 => l_asg_rec.ass_attribute23
897 ,p_ass_attribute24 => l_asg_rec.ass_attribute24
898 ,p_ass_attribute25 => l_asg_rec.ass_attribute25
899 ,p_ass_attribute26 => l_asg_rec.ass_attribute26
900 ,p_ass_attribute27 => l_asg_rec.ass_attribute27
901 ,p_ass_attribute28 => l_asg_rec.ass_attribute28
902 ,p_ass_attribute29 => l_asg_rec.ass_attribute29
903 ,p_ass_attribute30 => l_asg_rec.ass_attribute30
904 ,p_title => l_asg_rec.title
905 ,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
906 ,p_comment_id => l_comment_id
907 ,p_effective_start_date => l_effective_start_date
908 ,p_effective_end_date => l_effective_end_date
909 ,p_concatenated_segments => l_concatenated_segments
910 ,p_no_managers_warning => l_no_managers_warning
911 ,p_other_manager_warning => l_other_manager_warning
912 -- Assignment Security
913 ,p_supervisor_assignment_id => l_asg_rec.supervisor_assignment_id
914 );
915 end if;
916 --
917 if l_effective_date_row then
918 -- reset the first row flag
919 l_effective_date_row := false;
920 -- set all future row operations to a CORRECTION
921 l_datetrack_update_mode := hr_api.g_correction;
922 -- set the API out parameters for the first transaction
923 p_object_version_number := l_asg_rec.object_version_number;
924 p_comment_id := l_comment_id;
925 p_effective_start_date := l_effective_start_date;
926 p_effective_end_date := l_effective_end_date;
927 end if;
928 -- determine if the warnings have been set at all
929 if l_no_managers_warning and not l_api_no_managers_warning then
930 l_api_no_managers_warning := l_no_managers_warning;
931 end if;
932 if l_other_manager_warning and not l_api_other_manager_warning then
933 l_api_other_manager_warning := l_other_manager_warning;
934 end if;
935 hr_utility.set_location(' Leaving:'|| l_proc, 10);
936 -- we need to process the next row so return true
937 return(true);
938 else
939 hr_utility.set_location(' Leaving:'|| l_proc, 15);
940 -- processing has finished return false
941 return(false);
942 end if;
943 end process_row;
944 -------------------------------------begin---------------------------------------------
945 begin
946 hr_utility.set_location('Entering:'|| l_proc, 5);
947 --
948 -- issue a savepoint if operating in validation only mode.
949 --
950 if p_validate then
951 savepoint update_asg;
952 end if;
953 -- Added for turn off key flex field validation 12884425
954 l_add_struct.extend(1);
955 l_add_struct(l_add_struct.count) := 'GRP';
956 l_add_struct.extend(1);
957 l_add_struct(l_add_struct.count) := 'CAGR';
958 l_add_struct.extend(1);
959 l_add_struct(l_add_struct.count) := 'SCL';
960 --
961 hr_kflex_utility.create_ignore_kf_validation(p_rec => l_add_struct);
962
963
964 -- lock the current row for the following two reasons:
965 -- a) ensure that the current row exists for the person as of the
966 -- specified effective date. we only lock the current row so the
967 -- CORRECTION datetrack mode is used
968 -- b) to populate the l_validation_start_date which is used
969 -- in determining the correct datetrack mode on an update operation
970 per_asg_shd.lck
971 (p_effective_date => l_constant_effective_date
972 ,p_datetrack_mode => hr_api.g_correction
973 ,p_assignment_id => p_assignment_id
974 ,p_object_version_number => p_object_version_number
975 ,p_validation_start_date => l_validation_start_date
976 ,p_validation_end_date => l_validation_end_date);
977 -- determine the datetrack mode to use
978 if p_attribute_update_mode = 'ATTRIBUTE_UPDATE' then
979 -- ------------------------------------------------------------------------
980 -- step 1: as we are performing an ATTRIBUTE_UPDATE we must determine
981 -- the initial datetrack mode to use (UPDATE, CORRECTION or
982 -- UPDATE_CHANGE_INSERT)
983 --
984 -- 1.1 - call the assignment datetrack find_dt_upd_modes to determine
985 -- all possible allowed datetrack update modes
986 -- 1.2 - determine the actual datetrack mode to use
987 -- the logic is as follows;
988 -- if update allowed then select UPDATE as mode
989 -- if change insert allowed then select UPDATE_CHANGE_INSERT as
990 -- mode
991 -- otherwise, select CORRECTION as the mode
992 -- ------------------------------------------------------------------------
993 -- step 1.1
994 per_asg_shd.find_dt_upd_modes
995 (p_effective_date => l_constant_effective_date
996 ,p_base_key_value => p_assignment_id
997 ,p_correction => l_correction
998 ,p_update => l_update
999 ,p_update_override => l_update_override
1000 ,p_update_change_insert => l_update_change_insert);
1001 -- step 1.2
1002 if l_update then
1003 -- we can do an update
1004 l_datetrack_update_mode := hr_api.g_update;
1005 elsif l_update_change_insert then
1006 -- we can do an update change insert
1007 l_datetrack_update_mode := hr_api.g_update_change_insert;
1008 elsif (l_validation_start_date = l_constant_effective_date) and
1009 l_correction then
1010 -- we can only perform a correction
1011 l_datetrack_update_mode := hr_api.g_correction;
1012 else
1013 -- we cannot perform an update due to a restriction within the APIs
1014 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1015 hr_utility.set_message_token('PROCEDURE', l_proc);
1016 hr_utility.set_message_token('STEP','10');
1017 hr_utility.raise_error;
1018 end if;
1019 -- set lock start date to the effective date
1020 l_lck_start_date := l_constant_effective_date;
1021 elsif p_attribute_update_mode = 'ATTRIBUTE_CORRECTION' then
1022 -- set lock start date to start of time and the datetrack mode
1023 -- to CORRECTION
1024 l_lck_start_date := hr_api.g_sot;
1025 l_datetrack_update_mode := hr_api.g_correction;
1026 else
1027 -- the datetrack mode is not an ATTRIBUTE_UPDATE or ATTRIBUTE_CORRECTION
1028 -- so raise DT invalid mode error
1029 hr_utility.set_message(801, 'HR_7203_DT_UPD_MODE_INVALID');
1030 hr_utility.raise_error;
1031 end if;
1032 -- lock all assignment rows to ensure integrity. note: this will never fail.
1033 -- if the assignment doesn't exist (i.e. the assignment_id is invalid) then the
1034 -- business process will error with the correct error
1035 open csr_asg_lck(l_lck_start_date);
1036 close csr_asg_lck;
1037 -- ------------------------------------------------------------------------
1038 -- process the current and future row(s)
1039 -- ------------------------------------------------------------------------
1040 for I in csr_asg1 loop
1041 if not process_row
1042 (c_effective_start_date => I.effective_start_date
1043 ,c_object_version_number => I.object_version_number
1044 ,c_supervisor_id => I.supervisor_id
1045 -- Assignment Security
1046 ,c_supervisor_assignment_id => I.supervisor_assignment_id
1047
1048 ,c_assignment_number => I.assignment_number
1049 ,c_change_reason => I.change_reason
1050 ,c_comments => I.comment_text
1051 ,c_date_probation_end => I.date_probation_end
1052 ,c_default_code_comb_id => I.default_code_comb_id
1053 ,c_frequency => I.frequency
1054 ,c_internal_address_line => I.internal_address_line
1055 ,c_manager_flag => I.manager_flag
1056 ,c_normal_hours => I.normal_hours
1057 ,c_perf_review_period => I.perf_review_period
1058 ,c_perf_review_period_frequency => I.perf_review_period_frequency
1059 ,c_probation_period => I.probation_period
1060 ,c_probation_unit => I.probation_unit
1061 ,c_sal_review_period => I.sal_review_period
1062 ,c_sal_review_period_frequency => I.sal_review_period_frequency
1063 ,c_set_of_books_id => I.set_of_books_id
1064 ,c_source_type => I.source_type
1065 ,c_time_normal_finish => I.time_normal_finish
1066 ,c_time_normal_start => I.time_normal_start
1067 ,c_ass_attribute_category => I.ass_attribute_category
1068 ,c_ass_attribute1 => I.ass_attribute1
1069 ,c_ass_attribute2 => I.ass_attribute2
1070 ,c_ass_attribute3 => I.ass_attribute3
1071 ,c_ass_attribute4 => I.ass_attribute4
1072 ,c_ass_attribute5 => I.ass_attribute5
1073 ,c_ass_attribute6 => I.ass_attribute6
1074 ,c_ass_attribute7 => I.ass_attribute7
1075 ,c_ass_attribute8 => I.ass_attribute8
1076 ,c_ass_attribute9 => I.ass_attribute9
1077 ,c_ass_attribute10 => I.ass_attribute10
1078 ,c_ass_attribute11 => I.ass_attribute11
1079 ,c_ass_attribute12 => I.ass_attribute12
1080 ,c_ass_attribute13 => I.ass_attribute13
1081 ,c_ass_attribute14 => I.ass_attribute14
1082 ,c_ass_attribute15 => I.ass_attribute15
1083 ,c_ass_attribute16 => I.ass_attribute16
1084 ,c_ass_attribute17 => I.ass_attribute17
1085 ,c_ass_attribute18 => I.ass_attribute18
1086 ,c_ass_attribute19 => I.ass_attribute19
1087 ,c_ass_attribute20 => I.ass_attribute20
1088 ,c_ass_attribute21 => I.ass_attribute21
1089 ,c_ass_attribute22 => I.ass_attribute22
1090 ,c_ass_attribute23 => I.ass_attribute23
1091 ,c_ass_attribute24 => I.ass_attribute24
1092 ,c_ass_attribute25 => I.ass_attribute25
1093 ,c_ass_attribute26 => I.ass_attribute26
1094 ,c_ass_attribute27 => I.ass_attribute27
1095 ,c_ass_attribute28 => I.ass_attribute28
1096 ,c_ass_attribute29 => I.ass_attribute29
1097 ,c_ass_attribute30 => I.ass_attribute30
1098 ,c_title => I.title
1099 ,c_project_title => I.project_title
1100 ,c_vendor_assignment_number => I.vendor_assignment_number
1101 ,c_vendor_employee_number => I.vendor_employee_number
1102 ,c_vendor_id => I.vendor_id
1103 ,c_soft_coding_keyflex_id => I.soft_coding_keyflex_id
1104 ) then
1105 -- all the attributes have been processed, exit the loop
1106 hr_utility.trace(' all the attributes have been processed'||
1107 'before exit');
1108 exit;
1109 end if;
1110 end loop;
1111 -- ------------------------------------------------------------------------
1112 -- process any past row(s)
1113 hr_utility.trace(' before if condition'||
1114 'p_attribute_update_mode = ATTRIBUTE_CORRECTION');
1115 if p_attribute_update_mode = 'ATTRIBUTE_CORRECTION' then
1116 -- reset the parameter statuses
1117 hr_utility.trace(' after if condition'||
1118 'p_attribute_update_mode = ATTRIBUTE_CORRECTION');
1119 hr_dt_attribute_support.reset_parameter_statuses;
1120 for I in csr_asg2 loop
1121 if not process_row
1122 (c_effective_start_date => I.effective_start_date
1123 ,c_object_version_number => I.object_version_number
1124 ,c_supervisor_id => I.supervisor_id
1125 -- Assignment Security
1126 ,c_supervisor_assignment_id => I.supervisor_assignment_id
1127 ,c_assignment_number => I.assignment_number
1128 ,c_change_reason => I.change_reason
1129 ,c_comments => I.comment_text
1130 ,c_date_probation_end => I.date_probation_end
1131 ,c_default_code_comb_id => I.default_code_comb_id
1132 ,c_frequency => I.frequency
1133 ,c_internal_address_line => I.internal_address_line
1134 ,c_manager_flag => I.manager_flag
1135 ,c_normal_hours => I.normal_hours
1136 ,c_perf_review_period => I.perf_review_period
1137 ,c_perf_review_period_frequency => I.perf_review_period_frequency
1138 ,c_probation_period => I.probation_period
1139 ,c_probation_unit => I.probation_unit
1140 ,c_sal_review_period => I.sal_review_period
1141 ,c_sal_review_period_frequency => I.sal_review_period_frequency
1142 ,c_set_of_books_id => I.set_of_books_id
1143 ,c_source_type => I.source_type
1144 ,c_time_normal_finish => I.time_normal_finish
1145 ,c_time_normal_start => I.time_normal_start
1146 ,c_ass_attribute_category => I.ass_attribute_category
1147 ,c_ass_attribute1 => I.ass_attribute1
1148 ,c_ass_attribute2 => I.ass_attribute2
1149 ,c_ass_attribute3 => I.ass_attribute3
1150 ,c_ass_attribute4 => I.ass_attribute4
1151 ,c_ass_attribute5 => I.ass_attribute5
1152 ,c_ass_attribute6 => I.ass_attribute6
1153 ,c_ass_attribute7 => I.ass_attribute7
1154 ,c_ass_attribute8 => I.ass_attribute8
1155 ,c_ass_attribute9 => I.ass_attribute9
1156 ,c_ass_attribute10 => I.ass_attribute10
1157 ,c_ass_attribute11 => I.ass_attribute11
1158 ,c_ass_attribute12 => I.ass_attribute12
1159 ,c_ass_attribute13 => I.ass_attribute13
1160 ,c_ass_attribute14 => I.ass_attribute14
1161 ,c_ass_attribute15 => I.ass_attribute15
1162 ,c_ass_attribute16 => I.ass_attribute16
1163 ,c_ass_attribute17 => I.ass_attribute17
1164 ,c_ass_attribute18 => I.ass_attribute18
1165 ,c_ass_attribute19 => I.ass_attribute19
1166 ,c_ass_attribute20 => I.ass_attribute20
1167 ,c_ass_attribute21 => I.ass_attribute21
1168 ,c_ass_attribute22 => I.ass_attribute22
1169 ,c_ass_attribute23 => I.ass_attribute23
1170 ,c_ass_attribute24 => I.ass_attribute24
1171 ,c_ass_attribute25 => I.ass_attribute25
1172 ,c_ass_attribute26 => I.ass_attribute26
1173 ,c_ass_attribute27 => I.ass_attribute27
1174 ,c_ass_attribute28 => I.ass_attribute28
1175 ,c_ass_attribute29 => I.ass_attribute29
1176 ,c_ass_attribute30 => I.ass_attribute30
1177 ,c_title => I.title
1178 ,c_project_title => I.project_title
1179 ,c_vendor_assignment_number => I.vendor_assignment_number
1180 ,c_vendor_employee_number => I.vendor_employee_number
1181 ,c_vendor_id => I.vendor_id,
1182 c_soft_coding_keyflex_id => I.soft_coding_keyflex_id
1183 ) then
1184 -- all the attributes have been processed, exit the loop
1185 hr_utility.trace(' all the attributes have been processed'||
1186 ' if not process_row -csr_asg2- before exit');
1187 exit;
1188 end if;
1189 end loop;
1190 end if;
1191 --
1192 -- When in validation only mode raise the Validate_Enabled exception
1193 --
1194 if p_validate then
1195 raise hr_api.validate_enabled;
1196 end if;
1197 -- set the warning OUT parameters
1198 p_no_managers_warning := l_api_no_managers_warning;
1199 p_other_manager_warning := l_api_other_manager_warning;
1200 --
1201 hr_utility.set_location(' Leaving:'||l_proc, 10);
1202 exception
1203 when hr_api.validate_enabled then
1204 --
1205 -- As the Validate_Enabled exception has been raised
1206 -- we must rollback to the savepoint
1207 --
1208 rollback to update_asg;
1209 -- reset IN OUT parameters to original IN value
1210 p_object_version_number := p_object_version_number;
1211 -- reset non-warning OUT parameters to NULL
1212 p_effective_start_date := null;
1213 p_effective_end_date := null;
1214 p_comment_id := null;
1215 -- set warning OUT parameters to REAL value
1216 p_no_managers_warning := l_api_no_managers_warning;
1217 p_other_manager_warning := l_api_other_manager_warning;
1218
1219 when others then
1220 p_object_version_number := lv_object_version_number;
1221 -- reset OUT parameters to NULL
1222 p_effective_start_date := null;
1223 p_effective_end_date := null;
1224 p_comment_id := null;
1225 p_no_managers_warning := null;
1226 p_other_manager_warning := null;
1227
1228 RAISE;
1229 --
1230 end update_asg;
1231 --
1232 --
1233 -- ----------------------------------------------------------------------------
1234 -- |------------------------< update_asg_criteria >---------------------------|
1235 -- ----------------------------------------------------------------------------
1236 --
1237 procedure update_asg_criteria
1238 (p_validate in boolean default false
1239 ,p_effective_date in date
1240 ,p_attribute_update_mode in varchar2
1241 ,p_assignment_id in number
1242 ,p_object_version_number in out nocopy number
1243 ,p_grade_id in number default hr_api.g_number
1244 ,p_position_id in number default hr_api.g_number
1245 ,p_job_id in number default hr_api.g_number
1246 ,p_payroll_id in number default hr_api.g_number
1247 ,p_location_id in number default hr_api.g_number
1248 ,p_special_ceiling_step_id in out nocopy number
1249 ,p_organization_id in number default hr_api.g_number
1250 ,p_pay_basis_id in number default hr_api.g_number
1251 ,p_employment_category in varchar2 default hr_api.g_varchar2
1252 ,p_assignment_type in varchar2
1253 ,p_effective_start_date out nocopy date
1254 ,p_effective_end_date out nocopy date
1255 ,p_no_managers_warning out nocopy boolean
1256 ,p_other_manager_warning out nocopy boolean
1257 ,p_spp_delete_warning out nocopy boolean
1258 ,p_entries_changed_warning out nocopy varchar2
1259 ,p_tax_district_changed_warning out nocopy boolean
1260 ) is
1261 l_proc varchar2(72) := g_package||'update_asg_criteria';
1262 l_effective_date date := trunc(p_effective_date);
1263 l_constant_effective_date constant date := l_effective_date;
1264 l_effective_date_row boolean := true;
1265 l_validation_start_date date;
1266 l_validation_end_date date;
1267 l_datetrack_update_mode varchar2(30);
1268 l_correction boolean;
1269 l_update boolean;
1270 l_update_override boolean;
1271 l_update_change_insert boolean;
1272 l_lck_start_date date;
1273 --
1274 lv_object_version_number number := p_object_version_number;
1275 lv_special_ceiling_step_id number := p_special_ceiling_step_id ;
1276 --
1277 -- --------------------------------------------------------------------------
1278 -- local cursor definitions
1279 -- --------------------------------------------------------------------------
1280 -- csr_asg_lck -> locks all the datetracked rows for the specified assignment
1281 -- from the specified lock date. this enforces integrity.
1282 -- if the datetrack operation is for an ATTRIBUTE_UPDATE
1283 -- then only the current and future rows will be locked. if
1284 -- the datetrack operation is a ATTRIBUTE_CORRECTION then
1285 -- all assignment rows are locked as we cannot guarantee how
1286 -- many rows will be changed.
1287 -- csr_asg1 -> selects assignment details for the current and future rows
1288 -- csr_asg2 -> selects assignment details in the past in a descending
1289 -- order not including the current row as of the effective
1290 -- date.
1291 --
1292 -- note: the cursors csr_asg1 and csr_asg2 are specifically not merged
1293 -- because of the of the order by clause
1294 --
1295 -- cursor to lock all rows for which the datetrack operation could
1296 -- operate over
1297 cursor csr_asg_lck(c_lck_start_date date) is
1298 select 1
1299 from per_all_assignments_f asg
1300 where asg.assignment_id = p_assignment_id
1301 and asg.effective_end_date >= c_lck_start_date
1302 for update nowait;
1303 -- select current and future rows
1304 cursor csr_asg1 is
1305 select
1306 asg.object_version_number
1307 ,asg.grade_id
1308 ,asg.position_id
1309 ,asg.job_id
1310 ,asg.payroll_id
1311 ,asg.location_id
1312 ,asg.special_ceiling_step_id
1313 ,asg.organization_id
1314 ,asg.pay_basis_id
1315 ,asg.employment_category
1316 ,asg.effective_start_date
1317 ,asg.effective_end_date
1318 from per_all_assignments_f asg
1319 where asg.assignment_id=p_assignment_id
1320 and asg.effective_end_date >= l_constant_effective_date
1321 order by asg.effective_end_date asc;
1322 -- select past rows not including the current rows
1323 cursor csr_asg2 is
1324 select
1325 asg.object_version_number
1326 ,asg.grade_id
1327 ,asg.position_id
1328 ,asg.job_id
1329 ,asg.payroll_id
1330 ,asg.location_id
1331 ,asg.special_ceiling_step_id
1332 ,asg.organization_id
1333 ,asg.pay_basis_id
1334 ,asg.employment_category
1335 ,asg.effective_start_date
1336 ,asg.effective_end_date
1337 from per_all_assignments_f asg
1338 where asg.assignment_id=p_assignment_id
1339 and asg.effective_end_date < l_constant_effective_date
1340 order by asg.effective_end_date desc;
1341 -- IN parameters for API
1342 l_asg_rec per_all_assignments_f%rowtype;
1343 -- OUT parameters for API
1344 l_people_group_id number;
1345 l_group_name varchar2(240);
1346 l_effective_start_date date;
1347 l_effective_end_date date;
1348 l_concatenated_segments hr_soft_coding_keyflex.concatenated_segments%TYPE;
1349 l_no_managers_warning boolean;
1350 l_other_manager_warning boolean;
1351 l_spp_delete_warning boolean;
1352 l_entries_changed_warning varchar2(1);
1353 l_tax_district_changed_warning boolean;
1354 l_api_no_managers_warning boolean := false;
1355 l_api_other_manager_warning boolean := false;
1356 l_api_spp_delete_warning boolean := false;
1357 l_api_entries_changed_warning varchar2(1) := '';
1358 l_api_tax_district_changed boolean := false;
1359 --
1360 -- --------------------------------------------------------------------------
1361 -- |---------------------------< process_row >------------------------------|
1362 -- --------------------------------------------------------------------------
1363 -- {Start Of Comments}
1364 --
1365 -- Description:
1366 -- This private function is used to determine the correct attribute values
1367 -- to pass to the API.
1368 --
1369 -- 1. Determine the parameter value to be passed to the API
1370 -- 2. If at least one parameter value is changing then call the API
1371 -- else exit function
1372 -- 3. Set any parameters which have been supplied by the resulting call
1373 -- to the API
1374 --
1375 -- Pre Conditions:
1376 -- A row must be active from the cursor csr_asg1 or csr_asg2
1377 --
1378 -- In Arguments:
1379 -- All the IN arguments hold the current selected cursor row values.
1380 --
1381 -- Post Success:
1382 -- Ths function will return either TRUE or FALSE.
1383 -- If TRUE is returned, the row has been processed succesfully and
1384 -- attributes could possibly still be processed.
1385 -- If FALSE is returned, the row has been processed succesfully
1386 -- and all the attributes have been updated as far as possible.
1387 --
1388 -- Post Failure:
1389 -- Exceptions are not handled, just raised.
1390 --
1391 -- Developer Implementation Notes:
1392 -- None
1393 --
1394 -- Access Status:
1395 -- Internal to owning procedure.
1396 --
1397 -- {End Of Comments}
1398 -- --------------------------------------------------------------------------
1399 function process_row
1400 (c_effective_start_date in date
1401 ,c_object_version_number in number
1402 ,c_grade_id in number default hr_api.g_number
1403 ,c_position_id in number default hr_api.g_number
1404 ,c_job_id in number default hr_api.g_number
1405 ,c_payroll_id in number default hr_api.g_number
1406 ,c_location_id in number default hr_api.g_number
1407 ,c_special_ceiling_step_id in out nocopy number
1408 ,c_organization_id in number default hr_api.g_number
1409 ,c_pay_basis_id in number default hr_api.g_number
1410 ,c_employment_category in varchar2 default hr_api.g_varchar2
1411 )
1412 return boolean is
1413 l_proc varchar2(72) := g_package||'process_row';
1414 begin
1415 hr_utility.set_location('Entering:'|| l_proc, 5);
1416 -- get the parameter values to pass to the API
1417 l_asg_rec.grade_id:= hr_dt_attribute_support.get_parameter_number
1418 (p_effective_date_row => l_effective_date_row
1419 ,p_parameter_name => 'P_GRADE_ID'
1420 ,p_new_value => p_grade_id
1421 ,p_current_value => c_grade_id);
1422
1423 l_asg_rec.position_id:= hr_dt_attribute_support.get_parameter_number
1424 (p_effective_date_row => l_effective_date_row
1425 ,p_parameter_name => 'P_POSITION_ID'
1426 ,p_new_value => p_position_id
1427 ,p_current_value => c_position_id);
1428
1429 l_asg_rec.job_id:= hr_dt_attribute_support.get_parameter_number
1430 (p_effective_date_row => l_effective_date_row
1431 ,p_parameter_name => 'P_JOB_ID'
1432 ,p_new_value => p_job_id
1433 ,p_current_value => c_job_id);
1434
1435 l_asg_rec.payroll_id:= hr_dt_attribute_support.get_parameter_number
1436 (p_effective_date_row => l_effective_date_row
1437 ,p_parameter_name => 'P_PAYROLL_ID'
1438 ,p_new_value => p_payroll_id
1439 ,p_current_value => c_payroll_id);
1440
1441 l_asg_rec.location_id:= hr_dt_attribute_support.get_parameter_number
1442 (p_effective_date_row => l_effective_date_row
1443 ,p_parameter_name => 'P_LOCATION_ID'
1444 ,p_new_value => p_location_id
1445 ,p_current_value => c_location_id);
1446
1447 l_asg_rec.special_ceiling_step_id:= hr_dt_attribute_support.get_parameter_number
1448 (p_effective_date_row => l_effective_date_row
1449 ,p_parameter_name => 'P_SPECIAL_CEILING_STEP_ID'
1450 ,p_new_value => p_special_ceiling_step_id
1451 ,p_current_value => c_special_ceiling_step_id);
1452
1453 l_asg_rec.organization_id:= hr_dt_attribute_support.get_parameter_number
1454 (p_effective_date_row => l_effective_date_row
1455 ,p_parameter_name => 'P_ORGANIZATION_ID'
1456 ,p_new_value => p_organization_id
1457 ,p_current_value => c_organization_id);
1458
1459 l_asg_rec.pay_basis_id:= hr_dt_attribute_support.get_parameter_number
1460 (p_effective_date_row => l_effective_date_row
1461 ,p_parameter_name => 'P_PAY_BASIS_ID'
1462 ,p_new_value => p_pay_basis_id
1463 ,p_current_value => c_pay_basis_id);
1464
1465 l_asg_rec.employment_category:= hr_dt_attribute_support.get_parameter_char
1466 (p_effective_date_row => l_effective_date_row
1467 ,p_parameter_name => 'P_EMPLOYMENT_CATEGORY'
1468 ,p_new_value => p_employment_category
1469 ,p_current_value => c_employment_category);
1470
1471 --
1472 -- call the API if at least one attribute can be changed
1473 if hr_dt_attribute_support.is_current_row_changing then
1474 -- set the object version number and effective date
1475 if l_effective_date_row then
1476 -- as we are on the first row, the ovn and effective date should be
1477 -- set to the parameter specified by the caller
1478 l_asg_rec.object_version_number := p_object_version_number;
1479 l_effective_date := l_constant_effective_date;
1480 else
1481 -- as we are not on the first row, set the ovn and effective date
1482 -- to the ovn and effective date for the row
1483 l_asg_rec.object_version_number := c_object_version_number;
1484 l_effective_date := c_effective_start_date;
1485 end if;
1486 if p_assignment_type = 'C' then
1487 hr_assignment_api.update_cwk_asg_criteria
1488 (p_effective_date => l_effective_date
1489 ,p_datetrack_update_mode => l_datetrack_update_mode
1490 ,p_assignment_id => p_assignment_id
1491 ,p_object_version_number => l_asg_rec.object_version_number
1492 ,p_grade_id => l_asg_rec.grade_id
1493 ,p_position_id => l_asg_rec.position_id
1494 ,p_job_id => l_asg_rec.job_id
1495 ,p_location_id => l_asg_rec.location_id
1496 ,p_organization_id => l_asg_rec.organization_id
1497 ,p_pay_basis_id => l_asg_rec.pay_basis_id
1498 ,p_people_group_name => l_group_name
1499 ,p_effective_start_date => l_effective_start_date
1500 ,p_effective_end_date => l_effective_end_date
1501 ,p_people_group_id => l_people_group_id
1502 ,p_org_now_no_manager_warning => l_no_managers_warning
1503 ,p_other_manager_warning => l_other_manager_warning
1504 ,p_spp_delete_warning => l_spp_delete_warning
1505 ,p_entries_changed_warning => l_entries_changed_warning
1506 ,p_tax_district_changed_warning => l_tax_district_changed_warning
1507 );
1508 else
1509 -- call API
1510 hr_assignment_api.update_emp_asg_criteria
1511 (p_effective_date => l_effective_date
1512 ,p_datetrack_update_mode => l_datetrack_update_mode
1513 ,p_assignment_id => p_assignment_id
1514 ,p_object_version_number => l_asg_rec.object_version_number
1515 ,p_grade_id => l_asg_rec.grade_id
1516 ,p_position_id => l_asg_rec.position_id
1517 ,p_job_id => l_asg_rec.job_id
1518 ,p_payroll_id => l_asg_rec.payroll_id
1519 ,p_location_id => l_asg_rec.location_id
1520 ,p_special_ceiling_step_id => l_asg_rec.special_ceiling_step_id
1521 ,p_organization_id => l_asg_rec.organization_id
1522 ,p_pay_basis_id => l_asg_rec.pay_basis_id
1523 ,p_employment_category => l_asg_rec.employment_category
1524 ,p_effective_start_date => l_effective_start_date
1525 ,p_effective_end_date => l_effective_end_date
1526 ,p_people_group_id => l_people_group_id
1527 ,p_group_name => l_group_name
1528 ,p_org_now_no_manager_warning => l_no_managers_warning
1529 ,p_other_manager_warning => l_other_manager_warning
1530 ,p_spp_delete_warning => l_spp_delete_warning
1531 ,p_entries_changed_warning => l_entries_changed_warning
1532 ,p_tax_district_changed_warning => l_tax_district_changed_warning
1533 );
1534 end if;
1535 --
1536 if l_effective_date_row then
1537 -- reset the first row flag
1538 l_effective_date_row := false;
1539 -- set all future row operations to a CORRECTION
1540 l_datetrack_update_mode := hr_api.g_correction;
1541 -- set the API out parameters for the first transaction
1542 p_object_version_number := l_asg_rec.object_version_number;
1543 p_special_ceiling_step_id := l_asg_rec.special_ceiling_step_id;
1544 p_effective_start_date := l_effective_start_date;
1545 p_effective_end_date := l_effective_end_date;
1546 end if;
1547 -- determine if the warnings have been set at all
1548 if l_no_managers_warning and not l_api_no_managers_warning then
1549 l_api_no_managers_warning := l_no_managers_warning;
1550 end if;
1551 if l_other_manager_warning and not l_api_other_manager_warning then
1552 l_api_other_manager_warning := l_other_manager_warning;
1553 end if;
1554 if l_spp_delete_warning and not l_api_spp_delete_warning then
1555 l_api_spp_delete_warning:=l_spp_delete_warning;
1556 end if;
1557 if l_entries_changed_warning is not null
1558 and l_api_entries_changed_warning is null then
1559 l_api_entries_changed_warning:=l_entries_changed_warning;
1560 end if;
1561 if l_tax_district_changed_warning and not l_api_tax_district_changed then
1562 l_api_tax_district_changed:=l_tax_district_changed_warning;
1563 end if;
1564 hr_utility.set_location(' Leaving:'|| l_proc, 10);
1565 -- we need to process the next row so return true
1566 return(true);
1567 else
1568 hr_utility.set_location(' Leaving:'|| l_proc, 15);
1569 -- processing has finished return false
1570 return(false);
1571 end if;
1572 end process_row;
1573 -------------------------------------begin---------------------------------------------
1574 begin
1575 hr_utility.set_location('Entering:'|| l_proc, 5);
1576 --
1577 -- issue a savepoint if operating in validation only mode.
1578 --
1579 if p_validate then
1580 savepoint update_asg_criteria;
1581 end if;
1582 -- lock the current row for the following two reasons:
1583 -- a) ensure that the current row exists for the person as of the
1584 -- specified effective date. we only lock the current row so the
1585 -- CORRECTION datetrack mode is used
1586 -- b) to populate the l_validation_start_date which is used
1587 -- in determining the correct datetrack mode on an update operation
1588 per_asg_shd.lck
1589 (p_effective_date => l_constant_effective_date
1590 ,p_datetrack_mode => hr_api.g_correction
1591 ,p_assignment_id => p_assignment_id
1592 ,p_object_version_number => p_object_version_number
1593 ,p_validation_start_date => l_validation_start_date
1594 ,p_validation_end_date => l_validation_end_date);
1595 -- determine the datetrack mode to use
1596 if p_attribute_update_mode = 'ATTRIBUTE_UPDATE' then
1597 -- ------------------------------------------------------------------------
1598 -- step 1: as we are performing an ATTRIBUTE_UPDATE we must determine
1599 -- the initial datetrack mode to use (UPDATE, CORRECTION or
1600 -- UPDATE_CHANGE_INSERT)
1601 --
1602 -- 1.1 - call the assignment datetrack find_dt_upd_modes to determine
1603 -- all possible allowed datetrack update modes
1604 -- 1.2 - determine the actual datetrack mode to use
1605 -- the logic is as follows;
1606 -- if update allowed then select UPDATE as mode
1607 -- if change insert allowed then select UPDATE_CHANGE_INSERT as
1608 -- mode
1609 -- otherwise, select CORRECTION as the mode
1610 -- ------------------------------------------------------------------------
1611 -- step 1.1
1612 per_asg_shd.find_dt_upd_modes
1613 (p_effective_date => l_constant_effective_date
1614 ,p_base_key_value => p_assignment_id
1615 ,p_correction => l_correction
1616 ,p_update => l_update
1617 ,p_update_override => l_update_override
1618 ,p_update_change_insert => l_update_change_insert);
1619 -- step 1.2
1620 if l_update then
1621 -- we can do an update
1622 l_datetrack_update_mode := hr_api.g_update;
1623 elsif l_update_change_insert then
1624 -- we can do an update change insert
1625 l_datetrack_update_mode := hr_api.g_update_change_insert;
1626 elsif (l_validation_start_date = l_constant_effective_date) and
1627 l_correction then
1628 -- we can only perform a correction
1629 l_datetrack_update_mode := hr_api.g_correction;
1630 else
1631 -- we cannot perform an update due to a restriction within the APIs
1632 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1633 hr_utility.set_message_token('PROCEDURE', l_proc);
1634 hr_utility.set_message_token('STEP','10');
1635 hr_utility.raise_error;
1636 end if;
1637 -- set lock start date to the effective date
1638 l_lck_start_date := l_constant_effective_date;
1639 elsif p_attribute_update_mode = 'ATTRIBUTE_CORRECTION' then
1640 -- set lock start date to start of time and the datetrack mode
1641 -- to CORRECTION
1642 l_lck_start_date := hr_api.g_sot;
1643 l_datetrack_update_mode := hr_api.g_correction;
1644 else
1645 -- the datetrack mode is not an ATTRIBUTE_UPDATE or ATTRIBUTE_CORRECTION
1646 -- so raise DT invalid mode error
1647 hr_utility.set_message(801, 'HR_7203_DT_UPD_MODE_INVALID');
1648 hr_utility.raise_error;
1649 end if;
1650 -- lock all assignment rows to ensure integrity. note: this will never fail.
1651 -- if the assignment doesn't exist (i.e. the assignment_id is invalid) then the
1652 -- business process will error with the correct error
1653 open csr_asg_lck(l_lck_start_date);
1654 close csr_asg_lck;
1655 -- ------------------------------------------------------------------------
1656 -- process the current and future row(s)
1657 -- ------------------------------------------------------------------------
1658 for I in csr_asg1 loop
1659 if not process_row
1660 (c_effective_start_date => I.effective_start_date
1661 ,c_object_version_number => I.object_version_number
1662 ,c_grade_id => I.grade_id
1663 ,c_position_id => I.position_id
1664 ,c_job_id => I.job_id
1665 ,c_payroll_id => I.payroll_id
1666 ,c_location_id => I.location_id
1667 ,c_special_ceiling_step_id => I.special_ceiling_step_id
1668 ,c_organization_id => I.organization_id
1669 ,c_pay_basis_id => I.pay_basis_id
1670 ,c_employment_category => I.employment_category) then
1671 -- all the attributes have been processed, exit the loop
1672 exit;
1673 end if;
1674 end loop;
1675 -- ------------------------------------------------------------------------
1676 -- process any past row(s)
1677 if p_attribute_update_mode = 'ATTRIBUTE_CORRECTION' then
1678 -- reset the parameter statuses
1679 hr_dt_attribute_support.reset_parameter_statuses;
1680 for I in csr_asg2 loop
1681 if not process_row
1682 (c_effective_start_date => I.effective_start_date
1683 ,c_object_version_number => I.object_version_number
1684 ,c_grade_id => I.grade_id
1685 ,c_position_id => I.position_id
1686 ,c_job_id => I.job_id
1687 ,c_payroll_id => I.payroll_id
1688 ,c_location_id => I.location_id
1689 ,c_special_ceiling_step_id => I.special_ceiling_step_id
1690 ,c_organization_id => I.organization_id
1691 ,c_pay_basis_id => I.pay_basis_id
1692 ,c_employment_category => I.employment_category) then
1693 -- all the attributes have been processed, exit the loop
1694 exit;
1695 end if;
1696 end loop;
1697 end if;
1698 --
1699 -- When in validation only mode raise the Validate_Enabled exception
1700 --
1701 if p_validate then
1702 raise hr_api.validate_enabled;
1703 end if;
1704 -- set the warning OUT parameters
1705 p_no_managers_warning := l_api_no_managers_warning;
1706 p_other_manager_warning := l_api_other_manager_warning;
1707 p_spp_delete_warning := l_api_spp_delete_warning;
1708 p_entries_changed_warning := l_api_entries_changed_warning;
1709 p_tax_district_changed_warning := l_api_tax_district_changed;
1710 --
1711 hr_utility.set_location(' Leaving:'||l_proc, 10);
1712 exception
1713 when hr_api.validate_enabled then
1714 --
1715 -- As the Validate_Enabled exception has been raised
1716 -- we must rollback to the savepoint
1717 --
1718 rollback to update_asg_criteria;
1719 -- reset IN OUT parameters to original IN value
1720 p_object_version_number := p_object_version_number;
1721 p_special_ceiling_step_id := p_special_ceiling_step_id;
1722 -- reset non-warning OUT parameters to NULL
1723 p_effective_start_date := null;
1724 p_effective_end_date := null;
1725 -- set warning OUT parameters to REAL value
1726 p_no_managers_warning := l_api_no_managers_warning;
1727 p_other_manager_warning := l_api_other_manager_warning;
1728 p_spp_delete_warning := l_api_spp_delete_warning;
1729 p_entries_changed_warning := l_api_entries_changed_warning;
1730 p_tax_district_changed_warning := l_api_tax_district_changed;
1731
1732 when others then
1733 -- reset IN OUT parameters to original IN value
1734 p_object_version_number := lv_object_version_number;
1735 p_special_ceiling_step_id := lv_special_ceiling_step_id;
1736 -- reset OUT parameters to NULL
1737 p_effective_start_date := null;
1738 p_effective_end_date := null;
1739 p_no_managers_warning := null;
1740 p_other_manager_warning := null;
1741 p_spp_delete_warning := null;
1742 p_entries_changed_warning := null;
1743 p_tax_district_changed_warning := null;
1744 --
1745 end update_asg_criteria;
1746 --
1747 end hr_assignment_att;