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