[Home] [Help]
PACKAGE BODY: APPS.PER_PYP_UPD
Source
1 Package Body per_pyp_upd as
2 /* $Header: pepyprhi.pkb 120.24 2011/09/28 10:47:13 akaranam ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_pyp_upd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------------< update_dml >------------------------------|
12 -- ----------------------------------------------------------------------------
13 -- {Start Of Comments}
14 --
15 -- Description:
16 -- This procedure controls the actual dml update logic. The processing of
17 -- this procedure is:
18 -- 1) Increment the object_version_number by 1 if the object_version_number
19 -- is defined as an attribute for this entity.
20 -- 2) To set and unset the g_api_dml status as required (as we are about to
21 -- perform dml).
22 -- 3) To update the specified row in the schema using the primary key in
23 -- the predicates.
24 -- 4) To trap any constraint violations that may have occurred.
25 -- 5) To raise any other errors.
26 --
27 -- Pre Conditions:
28 -- This is an internal private procedure which must be called from the upd
29 -- procedure.
30 --
31 -- In Parameters:
32 -- A Pl/Sql record structre.
33 --
34 -- Post Success:
35 -- The specified row will be updated in the schema.
36 --
37 -- Post Failure:
38 -- On the update dml failure it is important to note that we always reset the
39 -- g_api_dml status to false.
40 -- If a check, unique or parent integrity constraint violation is raised the
41 -- constraint_error procedure will be called.
42 -- If any other error is reported, the error will be raised after the
43 -- g_api_dml status is reset.
44 --
45 -- Developer Implementation Notes:
46 -- The update 'set' attribute list should be modified if any of your
47 -- attributes are not updateable.
48 --
49 -- Access Status:
50 -- Internal Table Handler Use Only.
51 --
52 -- {End Of Comments}
53 -- ----------------------------------------------------------------------------
54 Procedure update_dml(p_rec in out nocopy per_pyp_shd.g_rec_type) is
55 --
56 l_proc varchar2(72) := g_package||'update_dml';
57 --
58 Begin
59 hr_utility.set_location('Entering:'||l_proc, 5);
60 --
61 -- Increment the object version
62 --
63 p_rec.object_version_number := p_rec.object_version_number + 1;
64 --
65 per_pyp_shd.g_api_dml := true; -- Set the api dml status
66 --
67 -- Update the per_pay_proposals Row
68 --
69 update per_pay_proposals
70 set
71 pay_proposal_id = p_rec.pay_proposal_id,
72 event_id = null,
73 change_date = p_rec.change_date,
74 comments = p_rec.comments,
75 next_perf_review_date = null,
76 next_sal_review_date = p_rec.next_sal_review_date,
77 performance_rating = null,
78 proposal_reason = p_rec.proposal_reason,
79 proposed_salary_n = p_rec.proposed_salary_n,
80 review_date = null,
81 forced_ranking = p_rec.forced_ranking,
82 date_to = p_rec.date_to,
83 performance_review_id = p_rec.performance_review_id,
84 attribute_category = p_rec.attribute_category,
85 attribute1 = p_rec.attribute1,
86 attribute2 = p_rec.attribute2,
87 attribute3 = p_rec.attribute3,
88 attribute4 = p_rec.attribute4,
89 attribute5 = p_rec.attribute5,
90 attribute6 = p_rec.attribute6,
91 attribute7 = p_rec.attribute7,
92 attribute8 = p_rec.attribute8,
93 attribute9 = p_rec.attribute9,
94 attribute10 = p_rec.attribute10,
95 attribute11 = p_rec.attribute11,
96 attribute12 = p_rec.attribute12,
97 attribute13 = p_rec.attribute13,
98 attribute14 = p_rec.attribute14,
99 attribute15 = p_rec.attribute15,
100 attribute16 = p_rec.attribute16,
101 attribute17 = p_rec.attribute17,
102 attribute18 = p_rec.attribute18,
103 attribute19 = p_rec.attribute19,
104 attribute20 = p_rec.attribute20,
105 object_version_number = p_rec.object_version_number,
106 multiple_components = p_rec.multiple_components,
107 approved = p_rec.approved
108 where pay_proposal_id = p_rec.pay_proposal_id;
109 --
110 per_pyp_shd.g_api_dml := false; -- Unset the api dml status
111 --
112 hr_utility.set_location(' Leaving:'||l_proc, 10);
113 --
114 Exception
115 When hr_api.check_integrity_violated Then
116 -- A check constraint has been violated
117 per_pyp_shd.g_api_dml := false; -- Unset the api dml status
118 per_pyp_shd.constraint_error
119 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
120 When hr_api.parent_integrity_violated Then
121 -- Parent integrity has been violated
122 per_pyp_shd.g_api_dml := false; -- Unset the api dml status
123 per_pyp_shd.constraint_error
124 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
125 When hr_api.unique_integrity_violated Then
126 -- Unique integrity has been violated
127 per_pyp_shd.g_api_dml := false; -- Unset the api dml status
128 per_pyp_shd.constraint_error
129 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
130 When Others Then
131 per_pyp_shd.g_api_dml := false; -- Unset the api dml status
132 Raise;
133 End update_dml;
134 --
135 -- ----------------------------------------------------------------------------
136 -- |------------------------------< pre_update >------------------------------|
137 -- ----------------------------------------------------------------------------
138 -- {Start Of Comments}
139 --
140 -- Description:
141 -- This private procedure contains any processing which is required before
142 -- the update dml.
143 --
144 -- Pre Conditions:
145 -- This is an internal procedure which is called from the upd procedure.
146 --
147 -- In Parameters:
148 -- A Pl/Sql record structre.
149 --
150 -- Post Success:
151 -- Processing continues.
152 --
153 -- Post Failure:
154 -- If an error has occurred, an error message and exception will be raised
155 -- but not handled.
156 --
157 -- Developer Implementation Notes:
158 -- Any pre-processing required before the update dml is issued should be
159 -- coded within this procedure. It is important to note that any 3rd party
160 -- maintenance should be reviewed before placing in this procedure.
161 --
162 -- Access Status:
163 -- Internal Table Handler Use Only.
164 --
165 -- {End Of Comments}
166 -- ----------------------------------------------------------------------------
167 Procedure pre_update(p_rec in per_pyp_shd.g_rec_type) is
168 --
169 l_proc varchar2(72) := g_package||'pre_update';
170 --
171 Begin
172 hr_utility.set_location('Entering:'||l_proc, 5);
173 --
174 hr_utility.set_location(' Leaving:'||l_proc, 10);
175 End pre_update;
176 --
177 -- ----------------------------------------------------------------------------
178 -- |-----------------------------< post_update >------------------------------|
179 -- ----------------------------------------------------------------------------
180 -- {Start Of Comments}
181 --
182 -- Description:
183 -- This private procedure contains any processing which is required after the
184 -- update dml.
185 --
186 -- Pre Conditions:
187 -- This is an internal procedure which is called from the upd procedure.
188 --
189 -- In Parameters:
190 -- A Pl/Sql record structre.
191 --
192 -- Post Success:
193 -- Processing continues.
194 --
195 -- Post Failure:
196 -- If an error has occurred, an error message and exception will be raised
197 -- but not handled.
198 --
199 -- Developer Implementation Notes:
200 -- Any post-processing required after the update dml is issued should be
201 -- coded within this procedure. It is important to note that any 3rd party
202 -- maintenance should be reviewed before placing in this procedure.
203 --
204 -- Access Status:
205 -- Internal Table Handler Use Only.
206 --
207 -- {End Of Comments}
208 -- ----------------------------------------------------------------------------
209 Procedure post_update
210 (p_rec in per_pyp_shd.g_rec_type
211 ,p_inv_next_sal_date_warning in boolean
212 ,p_proposed_salary_warning in boolean
213 ,p_approved_warning in boolean
214 ,p_payroll_warning in boolean
215 ) is
216 --
217 l_proc varchar2(72) := g_package||'post_update';
218 l_old_rec ben_pro_ler.g_pro_ler_rec ;
219 l_new_rec ben_pro_ler.g_pro_ler_rec ;
220 --
221 -- extract change events
222 l_ext_old_rec ben_ext_chlg.g_per_pay_rec_type;
223 l_ext_new_rec ben_ext_chlg.g_per_pay_rec_type;
224 l_event varchar2(20) ;
225 --vkodedal 6785012 EXTRACT BUG -old salary needs to be retrieved when proposal is approved
226 l_old_salary number;
227 --
228
229 Begin
230 hr_utility.set_location('Entering:'||l_proc, 5);
231 --
232 -- Call to user hook for post_update.
233 --
234 --
235 -- OAB Bug 1863413 : Detect potential life events when salary
236 -- information changes
237 --
238 l_old_rec.BUSINESS_GROUP_ID := per_pyp_shd.g_old_rec.BUSINESS_GROUP_ID;
239 l_old_rec.PAY_PROPOSAL_ID := per_pyp_shd.g_old_rec.PAY_PROPOSAL_ID;
240 l_old_rec.ASSIGNMENT_ID := per_pyp_shd.g_old_rec.ASSIGNMENT_ID;
241 l_old_rec.EVENT_ID := null; -- 9999 per_pyp_shd.g_old_rec.EVENT_ID;
242 l_old_rec.CHANGE_DATE := per_pyp_shd.g_old_rec.CHANGE_DATE;
243 l_old_rec.LAST_CHANGE_DATE := per_pyp_shd.g_old_rec.LAST_CHANGE_DATE;
244 l_old_rec.NEXT_PERF_REVIEW_DATE := null; -- 9999 per_pyp_shd.g_old_rec.NEXT_PERF_REVIEW_DATE;
245 l_old_rec.NEXT_SAL_REVIEW_DATE := per_pyp_shd.g_old_rec.NEXT_SAL_REVIEW_DATE;
246 l_old_rec.PERFORMANCE_RATING := null; -- 9999 per_pyp_shd.g_old_rec.PERFORMANCE_RATING;
247 l_old_rec.PROPOSAL_REASON := per_pyp_shd.g_old_rec.PROPOSAL_REASON;
248 l_old_rec.PROPOSED_SALARY_N := per_pyp_shd.g_old_rec.PROPOSED_SALARY_N;
249 l_old_rec.REVIEW_DATE := null; -- 9999 per_pyp_shd.g_old_rec.REVIEW_DATE;
250 l_old_rec.APPROVED := per_pyp_shd.g_old_rec.APPROVED;
251 l_old_rec.MULTIPLE_COMPONENTS := per_pyp_shd.g_old_rec.MULTIPLE_COMPONENTS;
252 l_old_rec.FORCED_RANKING := per_pyp_shd.g_old_rec.FORCED_RANKING;
253 l_old_rec.DATE_TO := per_pyp_shd.g_old_rec.DATE_TO;
254 l_old_rec.PERFORMANCE_REVIEW_ID := per_pyp_shd.g_old_rec.PERFORMANCE_REVIEW_ID;
255 l_old_rec.ATTRIBUTE1 := per_pyp_shd.g_old_rec.ATTRIBUTE1;
256 l_old_rec.ATTRIBUTE2 := per_pyp_shd.g_old_rec.ATTRIBUTE2;
257 l_old_rec.ATTRIBUTE3 := per_pyp_shd.g_old_rec.ATTRIBUTE3;
258 l_old_rec.ATTRIBUTE4 := per_pyp_shd.g_old_rec.ATTRIBUTE4;
259 l_old_rec.ATTRIBUTE5 := per_pyp_shd.g_old_rec.ATTRIBUTE5;
260 l_old_rec.ATTRIBUTE6 := per_pyp_shd.g_old_rec.ATTRIBUTE6;
261 l_old_rec.ATTRIBUTE7 := per_pyp_shd.g_old_rec.ATTRIBUTE7;
262 l_old_rec.ATTRIBUTE8 := per_pyp_shd.g_old_rec.ATTRIBUTE8;
263 l_old_rec.ATTRIBUTE9 := per_pyp_shd.g_old_rec.ATTRIBUTE9;
264 l_old_rec.ATTRIBUTE10 := per_pyp_shd.g_old_rec.ATTRIBUTE10;
265 l_old_rec.ATTRIBUTE11 := per_pyp_shd.g_old_rec.ATTRIBUTE11;
266 l_old_rec.ATTRIBUTE12 := per_pyp_shd.g_old_rec.ATTRIBUTE12;
267 l_old_rec.ATTRIBUTE13 := per_pyp_shd.g_old_rec.ATTRIBUTE13;
268 l_old_rec.ATTRIBUTE14 := per_pyp_shd.g_old_rec.ATTRIBUTE14;
269 l_old_rec.ATTRIBUTE15 := per_pyp_shd.g_old_rec.ATTRIBUTE15;
270 l_old_rec.ATTRIBUTE16 := per_pyp_shd.g_old_rec.ATTRIBUTE16;
271 l_old_rec.ATTRIBUTE17 := per_pyp_shd.g_old_rec.ATTRIBUTE17;
272 l_old_rec.ATTRIBUTE18 := per_pyp_shd.g_old_rec.ATTRIBUTE18;
273 l_old_rec.ATTRIBUTE19 := per_pyp_shd.g_old_rec.ATTRIBUTE19;
274 l_old_rec.ATTRIBUTE20 := per_pyp_shd.g_old_rec.ATTRIBUTE20;
275 l_old_rec.PROPOSED_SALARY := null; -- 9999 ben_pro_shd.g_old_rec.PROPOSED_SALARY;
276 --
277 l_new_rec.BUSINESS_GROUP_ID := p_rec.BUSINESS_GROUP_ID;
278 l_new_rec.PAY_PROPOSAL_ID := p_rec.PAY_PROPOSAL_ID;
279 l_new_rec.ASSIGNMENT_ID := p_rec.ASSIGNMENT_ID;
280 l_new_rec.EVENT_ID := null; -- 9999 p_rec.EVENT_ID;
281 l_new_rec.CHANGE_DATE := p_rec.CHANGE_DATE;
282 l_new_rec.LAST_CHANGE_DATE := p_rec.LAST_CHANGE_DATE;
283 l_new_rec.NEXT_PERF_REVIEW_DATE := null; -- 9999 p_rec.NEXT_PERF_REVIEW_DATE;
284 l_new_rec.NEXT_SAL_REVIEW_DATE := p_rec.NEXT_SAL_REVIEW_DATE;
285 l_new_rec.PERFORMANCE_RATING := null; -- 9999 p_rec.PERFORMANCE_RATING;
286 l_new_rec.PROPOSAL_REASON := p_rec.PROPOSAL_REASON;
287 l_new_rec.PROPOSED_SALARY_N := p_rec.PROPOSED_SALARY_N;
288 l_new_rec.REVIEW_DATE := null; -- 9999 p_rec.REVIEW_DATE;
289 l_new_rec.APPROVED := p_rec.APPROVED;
290 l_new_rec.MULTIPLE_COMPONENTS := p_rec.MULTIPLE_COMPONENTS;
291 l_new_rec.FORCED_RANKING := p_rec.FORCED_RANKING;
292 l_new_rec.DATE_TO := p_rec.DATE_TO;
293 l_new_rec.PERFORMANCE_REVIEW_ID := p_rec.PERFORMANCE_REVIEW_ID;
294 l_new_rec.ATTRIBUTE1 := p_rec.ATTRIBUTE1;
295 l_new_rec.ATTRIBUTE2 := p_rec.ATTRIBUTE2;
296 l_new_rec.ATTRIBUTE3 := p_rec.ATTRIBUTE3;
297 l_new_rec.ATTRIBUTE4 := p_rec.ATTRIBUTE4;
298 l_new_rec.ATTRIBUTE5 := p_rec.ATTRIBUTE5;
299 l_new_rec.ATTRIBUTE6 := p_rec.ATTRIBUTE6;
300 l_new_rec.ATTRIBUTE7 := p_rec.ATTRIBUTE7;
301 l_new_rec.ATTRIBUTE8 := p_rec.ATTRIBUTE8;
302 l_new_rec.ATTRIBUTE9 := p_rec.ATTRIBUTE9;
303 l_new_rec.ATTRIBUTE10 := p_rec.ATTRIBUTE10;
304 l_new_rec.ATTRIBUTE11 := p_rec.ATTRIBUTE11;
305 l_new_rec.ATTRIBUTE12 := p_rec.ATTRIBUTE12;
306 l_new_rec.ATTRIBUTE13 := p_rec.ATTRIBUTE13;
307 l_new_rec.ATTRIBUTE14 := p_rec.ATTRIBUTE14;
308 l_new_rec.ATTRIBUTE15 := p_rec.ATTRIBUTE15;
309 l_new_rec.ATTRIBUTE16 := p_rec.ATTRIBUTE16;
310 l_new_rec.ATTRIBUTE17 := p_rec.ATTRIBUTE17;
311 l_new_rec.ATTRIBUTE18 := p_rec.ATTRIBUTE18;
312 l_new_rec.ATTRIBUTE19 := p_rec.ATTRIBUTE19;
313 l_new_rec.ATTRIBUTE20 := p_rec.ATTRIBUTE20;
314 l_new_rec.PROPOSED_SALARY := null; -- 9999 p_rec.PROPOSED_SALARY;
315 --
316 -- extract change events
317 l_ext_old_rec.BUSINESS_GROUP_ID := per_pyp_shd.g_old_rec.BUSINESS_GROUP_ID;
318 l_ext_old_rec.PAY_PROPOSAL_ID := per_pyp_shd.g_old_rec.PAY_PROPOSAL_ID;
319 l_ext_old_rec.ASSIGNMENT_ID := per_pyp_shd.g_old_rec.ASSIGNMENT_ID;
320 --l_ext_old_rec.EVENT_ID := per_pyp_shd.g_old_rec.EVENT_ID;
321 l_ext_old_rec.CHANGE_DATE := per_pyp_shd.g_old_rec.CHANGE_DATE;
322 l_ext_old_rec.LAST_CHANGE_DATE := per_pyp_shd.g_old_rec.LAST_CHANGE_DATE;
323 --l_ext_old_rec.NEXT_PERF_REVIEW_DATE := per_pyp_shd.g_old_rec.NEXT_PERF_REVIEW_DATE;
324 l_ext_old_rec.NEXT_SAL_REVIEW_DATE := per_pyp_shd.g_old_rec.NEXT_SAL_REVIEW_DATE;
325 --l_ext_old_rec.PERFORMANCE_RATING := per_pyp_shd.g_old_rec.PERFORMANCE_RATING;
326 l_ext_old_rec.PROPOSAL_REASON := per_pyp_shd.g_old_rec.PROPOSAL_REASON;
327
328 --vkodedal 6785012 EXTRACT BUG -old salary needs to be retrieved when proposal is approved
329 if(l_old_rec.proposed_salary_n = p_rec.proposed_salary_n and
330 l_old_rec.approved = 'N' and p_rec.approved = 'Y' and
331 l_old_rec.change_date = p_rec.change_date)
332 then
333 begin
334 select proposed_salary_n into l_old_salary from per_pay_proposals
335 where assignment_id = p_rec.ASSIGNMENT_ID and
336 change_date = (select max(change_date) from per_pay_proposals where
337 change_date < p_rec.change_date and assignment_id = p_rec.ASSIGNMENT_ID );
338 l_ext_old_rec.PROPOSED_SALARY_N := l_old_salary;
339 exception
340 when no_data_found
341 then
342 l_ext_old_rec.PROPOSED_SALARY_N :=null;
343 end;
344 else
345 l_ext_old_rec.PROPOSED_SALARY_N := per_pyp_shd.g_old_rec.PROPOSED_SALARY_N;
346 end if;
347
348 --l_ext_old_rec.REVIEW_DATE := per_pyp_shd.g_old_rec.REVIEW_DATE;
349 l_ext_old_rec.APPROVED := per_pyp_shd.g_old_rec.APPROVED;
350 l_ext_old_rec.MULTIPLE_COMPONENTS := per_pyp_shd.g_old_rec.MULTIPLE_COMPONENTS;
351 l_ext_old_rec.FORCED_RANKING := per_pyp_shd.g_old_rec.FORCED_RANKING;
352 l_ext_old_rec.DATE_TO := per_pyp_shd.g_old_rec.DATE_TO;
353 l_ext_old_rec.PERFORMANCE_REVIEW_ID := per_pyp_shd.g_old_rec.PERFORMANCE_REVIEW_ID;
354 l_ext_old_rec.ATTRIBUTE1 := per_pyp_shd.g_old_rec.ATTRIBUTE1;
355 l_ext_old_rec.ATTRIBUTE2 := per_pyp_shd.g_old_rec.ATTRIBUTE2;
356 l_ext_old_rec.ATTRIBUTE3 := per_pyp_shd.g_old_rec.ATTRIBUTE3;
357 l_ext_old_rec.ATTRIBUTE4 := per_pyp_shd.g_old_rec.ATTRIBUTE4;
358 l_ext_old_rec.ATTRIBUTE5 := per_pyp_shd.g_old_rec.ATTRIBUTE5;
359 l_ext_old_rec.ATTRIBUTE6 := per_pyp_shd.g_old_rec.ATTRIBUTE6;
360 l_ext_old_rec.ATTRIBUTE7 := per_pyp_shd.g_old_rec.ATTRIBUTE7;
361 l_ext_old_rec.ATTRIBUTE8 := per_pyp_shd.g_old_rec.ATTRIBUTE8;
362 l_ext_old_rec.ATTRIBUTE9 := per_pyp_shd.g_old_rec.ATTRIBUTE9;
363 l_ext_old_rec.ATTRIBUTE10 := per_pyp_shd.g_old_rec.ATTRIBUTE10;
364 l_ext_old_rec.ATTRIBUTE11 := per_pyp_shd.g_old_rec.ATTRIBUTE11;
365 l_ext_old_rec.ATTRIBUTE12 := per_pyp_shd.g_old_rec.ATTRIBUTE12;
366 l_ext_old_rec.ATTRIBUTE13 := per_pyp_shd.g_old_rec.ATTRIBUTE13;
367 l_ext_old_rec.ATTRIBUTE14 := per_pyp_shd.g_old_rec.ATTRIBUTE14;
368 l_ext_old_rec.ATTRIBUTE15 := per_pyp_shd.g_old_rec.ATTRIBUTE15;
369 l_ext_old_rec.ATTRIBUTE16 := per_pyp_shd.g_old_rec.ATTRIBUTE16;
370 l_ext_old_rec.ATTRIBUTE17 := per_pyp_shd.g_old_rec.ATTRIBUTE17;
371 l_ext_old_rec.ATTRIBUTE18 := per_pyp_shd.g_old_rec.ATTRIBUTE18;
372 l_ext_old_rec.ATTRIBUTE19 := per_pyp_shd.g_old_rec.ATTRIBUTE19;
373 l_ext_old_rec.ATTRIBUTE20 := per_pyp_shd.g_old_rec.ATTRIBUTE20;
374 --l_ext_old_rec.PROPOSED_SALARY := per_pro_shd.g_old_rec.PROPOSED_SALARY;
375
376 l_ext_new_rec.BUSINESS_GROUP_ID := p_rec.BUSINESS_GROUP_ID;
377 l_ext_new_rec.PAY_PROPOSAL_ID := p_rec.PAY_PROPOSAL_ID;
378 l_ext_new_rec.ASSIGNMENT_ID := p_rec.ASSIGNMENT_ID;
379 --l_ext_new_rec.EVENT_ID := p_rec.EVENT_ID;
380 l_ext_new_rec.CHANGE_DATE := p_rec.CHANGE_DATE;
381 l_ext_new_rec.LAST_CHANGE_DATE := p_rec.LAST_CHANGE_DATE;
382 -- l_ext_new_rec.NEXT_PERF_REVIEW_DATE := p_rec.NEXT_PERF_REVIEW_DATE;
383 l_ext_new_rec.NEXT_SAL_REVIEW_DATE := p_rec.NEXT_SAL_REVIEW_DATE;
384 -- l_ext_new_rec.PERFORMANCE_RATING := p_rec.PERFORMANCE_RATING;
385 l_ext_new_rec.PROPOSAL_REASON := p_rec.PROPOSAL_REASON;
386 l_ext_new_rec.PROPOSED_SALARY_N := p_rec.PROPOSED_SALARY_N;
387 --l_ext_new_rec.REVIEW_DATE := p_rec.REVIEW_DATE;
388 l_ext_new_rec.APPROVED := p_rec.APPROVED;
389 l_ext_new_rec.MULTIPLE_COMPONENTS := p_rec.MULTIPLE_COMPONENTS;
390 l_ext_new_rec.FORCED_RANKING := p_rec.FORCED_RANKING;
391 l_ext_new_rec.PERFORMANCE_REVIEW_ID := p_rec.PERFORMANCE_REVIEW_ID;
392 l_ext_new_rec.ATTRIBUTE1 := p_rec.ATTRIBUTE1;
393 l_ext_new_rec.ATTRIBUTE2 := p_rec.ATTRIBUTE2;
394 l_ext_new_rec.ATTRIBUTE3 := p_rec.ATTRIBUTE3;
395 l_ext_new_rec.ATTRIBUTE4 := p_rec.ATTRIBUTE4;
396 l_ext_new_rec.ATTRIBUTE5 := p_rec.ATTRIBUTE5;
397 l_ext_new_rec.ATTRIBUTE6 := p_rec.ATTRIBUTE6;
398 l_ext_new_rec.ATTRIBUTE7 := p_rec.ATTRIBUTE7;
399 l_ext_new_rec.ATTRIBUTE8 := p_rec.ATTRIBUTE8;
400 l_ext_new_rec.ATTRIBUTE9 := p_rec.ATTRIBUTE9;
401 l_ext_new_rec.ATTRIBUTE10 := p_rec.ATTRIBUTE10;
402 l_ext_new_rec.ATTRIBUTE11 := p_rec.ATTRIBUTE11;
403 l_ext_new_rec.ATTRIBUTE12 := p_rec.ATTRIBUTE12;
404 l_ext_new_rec.ATTRIBUTE13 := p_rec.ATTRIBUTE13;
405 l_ext_new_rec.ATTRIBUTE14 := p_rec.ATTRIBUTE14;
406 l_ext_new_rec.ATTRIBUTE15 := p_rec.ATTRIBUTE15;
407 l_ext_new_rec.ATTRIBUTE16 := p_rec.ATTRIBUTE16;
408 l_ext_new_rec.ATTRIBUTE17 := p_rec.ATTRIBUTE17;
409 l_ext_new_rec.ATTRIBUTE18 := p_rec.ATTRIBUTE18;
410 l_ext_new_rec.ATTRIBUTE19 := p_rec.ATTRIBUTE19;
411 l_ext_new_rec.ATTRIBUTE20 := p_rec.ATTRIBUTE20;
412 -- l_ext_new_rec.PROPOSED_SALARY := p_rec.PROPOSED_SALARY;
413 l_ext_new_rec.date_to := p_rec.date_to;
414 l_event := 'UPDATE' ;
415 if l_ext_new_rec.last_change_date = l_ext_old_rec.last_change_date then
416 l_ext_new_rec.update_mode := 'CORRECTION';
417 l_ext_old_rec.update_mode := 'CORRECTION';
418 else
419 l_ext_new_rec.update_mode := 'UPDATE';
420 l_ext_old_rec.update_mode := 'UPDATE';
421 end if;
422
423 begin
424 --
425 per_pyp_rku.after_update
426 (
427 p_pay_proposal_id => p_rec.pay_proposal_id,
428 p_change_date => p_rec.change_date,
429 p_comments => p_rec.comments,
430 p_next_sal_review_date => p_rec.next_sal_review_date,
431 p_proposal_reason => p_rec.proposal_reason,
432 p_proposed_salary_n => p_rec.proposed_salary_n,
433 p_forced_ranking => p_rec.forced_ranking,
434 p_date_to => p_rec.date_to,
435 p_performance_review_id => p_rec.performance_review_id,
436 p_attribute_category => p_rec.attribute_category,
437 p_attribute1 => p_rec.attribute1,
438 p_attribute2 => p_rec.attribute2,
439 p_attribute3 => p_rec.attribute3,
440 p_attribute4 => p_rec.attribute4,
441 p_attribute5 => p_rec.attribute5,
442 p_attribute6 => p_rec.attribute6,
443 p_attribute7 => p_rec.attribute7,
444 p_attribute8 => p_rec.attribute8,
445 p_attribute9 => p_rec.attribute9,
446 p_attribute10 => p_rec.attribute10,
447 p_attribute11 => p_rec.attribute11,
448 p_attribute12 => p_rec.attribute12,
449 p_attribute13 => p_rec.attribute13,
450 p_attribute14 => p_rec.attribute14,
451 p_attribute15 => p_rec.attribute15,
452 p_attribute16 => p_rec.attribute16,
453 p_attribute17 => p_rec.attribute17,
454 p_attribute18 => p_rec.attribute18,
455 p_attribute19 => p_rec.attribute19,
456 p_attribute20 => p_rec.attribute20,
457 p_object_version_number => p_rec.object_version_number,
458 p_multiple_components => p_rec.multiple_components,
459 p_approved => p_rec.approved,
460 p_inv_next_sal_date_warning => p_inv_next_sal_date_warning,
461 p_proposed_salary_warning => p_proposed_salary_warning,
462 p_approved_warning => p_approved_warning,
463 p_payroll_warning => p_payroll_warning,
464 p_assignment_id_o => per_pyp_shd.g_old_rec.assignment_id,
465 p_business_group_id_o => per_pyp_shd.g_old_rec.business_group_id,
466 p_change_date_o => per_pyp_shd.g_old_rec.change_date,
467 p_comments_o => per_pyp_shd.g_old_rec.comments,
468 p_next_sal_review_date_o => per_pyp_shd.g_old_rec.next_sal_review_date,
469 p_proposal_reason_o => per_pyp_shd.g_old_rec.proposal_reason,
470 p_proposed_salary_n_o => per_pyp_shd.g_old_rec.proposed_salary_n,
471 p_forced_ranking_o => per_pyp_shd.g_old_rec.forced_ranking,
472 p_date_to_o => per_pyp_shd.g_old_rec.date_to,
473 p_performance_review_id_o => per_pyp_shd.g_old_rec.performance_review_id,
474 p_attribute_category_o => per_pyp_shd.g_old_rec.attribute_category,
475 p_attribute1_o => per_pyp_shd.g_old_rec.attribute1,
476 p_attribute2_o => per_pyp_shd.g_old_rec.attribute2,
477 p_attribute3_o => per_pyp_shd.g_old_rec.attribute3,
478 p_attribute4_o => per_pyp_shd.g_old_rec.attribute4,
479 p_attribute5_o => per_pyp_shd.g_old_rec.attribute5,
480 p_attribute6_o => per_pyp_shd.g_old_rec.attribute6,
481 p_attribute7_o => per_pyp_shd.g_old_rec.attribute7,
482 p_attribute8_o => per_pyp_shd.g_old_rec.attribute8,
483 p_attribute9_o => per_pyp_shd.g_old_rec.attribute9,
484 p_attribute10_o => per_pyp_shd.g_old_rec.attribute10,
485 p_attribute11_o => per_pyp_shd.g_old_rec.attribute11,
486 p_attribute12_o => per_pyp_shd.g_old_rec.attribute12,
487 p_attribute13_o => per_pyp_shd.g_old_rec.attribute13,
488 p_attribute14_o => per_pyp_shd.g_old_rec.attribute14,
489 p_attribute15_o => per_pyp_shd.g_old_rec.attribute15,
490 p_attribute16_o => per_pyp_shd.g_old_rec.attribute16,
491 p_attribute17_o => per_pyp_shd.g_old_rec.attribute17,
492 p_attribute18_o => per_pyp_shd.g_old_rec.attribute18,
493 p_attribute19_o => per_pyp_shd.g_old_rec.attribute19,
494 p_attribute20_o => per_pyp_shd.g_old_rec.attribute20,
495 p_object_version_number_o => per_pyp_shd.g_old_rec.object_version_number,
496 p_multiple_components_o => per_pyp_shd.g_old_rec.multiple_components,
497 p_approved_o => per_pyp_shd.g_old_rec.approved
498 );
499 --
500 hr_utility.set_location('DM Mode pro ' ||hr_general.g_data_migrator_mode ,379);
501 --
502 -- OAB Bug 1863413 : Detect potential life events when salary
503 -- information changes
504 --
505 if hr_general.g_data_migrator_mode not in ( 'Y','P') then
506 ben_pro_ler.ler_chk(p_old => l_old_rec
507 ,p_new => l_new_rec
508 ,p_effective_date => p_rec.change_date );
509 end if ;
510
511 -- extract change event log call
512 if hr_general.g_data_migrator_mode <> 'Y' then
513
514 hr_utility.set_location('Extract event logged called for pay proposal',99) ;
515 ben_ext_chlg.log_per_pay_chg
516 (p_event => l_event
517 ,p_old_rec => l_ext_old_rec
518 ,p_new_rec => l_ext_new_rec
519 );
520 hr_utility.set_location('Extract event logged out',99) ;
521 end if ;
522
523
524 exception
525 when hr_api.cannot_find_prog_unit then
526 hr_api.cannot_find_prog_unit_error
527 (p_module_name => 'PER_PAY_PROPOSALS'
528 ,p_hook_type => 'AU'
529 );
530 end;
531 -- End of API User Hook for post_update.
532 hr_utility.set_location(' Leaving:'||l_proc, 10);
533 End post_update;
534 --
535 -- ----------------------------------------------------------------------------
536 -- |-----------------------------< convert_defs >-----------------------------|
537 -- ----------------------------------------------------------------------------
538 -- {Start Of Comments}
539 --
540 -- Description:
541 -- The Convert_Defs procedure has one very important function:
542 -- It must return the record structure for the row with all system defaulted
543 -- values converted into its corresponding parameter value for update. When
544 -- we attempt to update a row through the Upd process , certain
545 -- parameters can be defaulted which enables flexibility in the calling of
546 -- the upd process (e.g. only attributes which need to be updated need to be
547 -- specified). For the upd process to determine which attributes
548 -- have NOT been specified we need to check if the parameter has a reserved
549 -- system default value. Therefore, for all parameters which have a
550 -- corresponding reserved system default mechanism specified we need to
551 -- check if a system default is being used. If a system default is being
552 -- used then we convert the defaulted value into its corresponding attribute
553 -- value held in the g_old_rec data structure.
554 --
555 -- Pre Conditions:
556 -- This private function can only be called from the upd process.
557 --
558 -- In Parameters:
559 -- A Pl/Sql record structre.
560 --
561 -- Post Success:
562 -- The record structure will be returned with all system defaulted parameter
563 -- values converted into its current row attribute value.
564 --
565 -- Post Failure:
566 -- No direct error handling is required within this function. Any possible
567 -- errors within this procedure will be a PL/SQL value error due to conversion
568
569 -- of datatypes or data lengths.
570 --
571 -- Developer Implementation Notes:
572 -- None.
573 --
574 -- Access Status:
575 -- Internal Table Handler Use Only.
576 --
577 -- {End Of Comments}
578 -- ----------------------------------------------------------------------------
579 Procedure convert_defs(p_rec in out nocopy per_pyp_shd.g_rec_type) is
580 --
581 l_proc varchar2(72) := g_package||'convert_defs';
582 --
583 Begin
584 --
585 hr_utility.set_location('Entering:'||l_proc, 5);
586 --
587 -- We must now examine each argument value in the
588 -- p_rec plsql record structure
589 -- to see if a system default is being used. If a system default
590 -- is being used then we must set to the 'current' argument value.
591 --
592 If (p_rec.assignment_id = hr_api.g_number) then
593 p_rec.assignment_id :=
594 per_pyp_shd.g_old_rec.assignment_id;
595 End If;
596 If (p_rec.business_group_id = hr_api.g_number) then
597 p_rec.business_group_id :=
598 per_pyp_shd.g_old_rec.business_group_id;
599 End If;
600 If (p_rec.change_date = hr_api.g_date) then
601 p_rec.change_date :=
602 per_pyp_shd.g_old_rec.change_date;
603 End If;
604 If (p_rec.comments = hr_api.g_varchar2) then
605 p_rec.comments :=
606 per_pyp_shd.g_old_rec.comments;
607 End If;
608 If (p_rec.last_change_date = hr_api.g_date) then
609 p_rec.last_change_date :=
610 per_pyp_shd.g_old_rec.last_change_date;
611 End If;
612 If (p_rec.next_sal_review_date = hr_api.g_date) then
613 p_rec.next_sal_review_date :=
614 per_pyp_shd.g_old_rec.next_sal_review_date;
615 End If;
616 If (p_rec.proposal_reason = hr_api.g_varchar2) then
617 p_rec.proposal_reason :=
618 per_pyp_shd.g_old_rec.proposal_reason;
619 End If;
620 If (p_rec.proposed_salary_n = hr_api.g_number) then
621 p_rec.proposed_salary_n :=
622 per_pyp_shd.g_old_rec.proposed_salary_n;
623 End If;
624 If (p_rec.forced_ranking = hr_api.g_number) then
625 p_rec.forced_ranking :=
626 per_pyp_shd.g_old_rec.forced_ranking;
627 End If;
628 If (p_rec.date_to = hr_api.g_date) then
629 p_rec.date_to :=
630 per_pyp_shd.g_old_rec.date_to;
631 End If;
632 If (p_rec.performance_review_id = hr_api.g_number) then
633 p_rec.performance_review_id :=
634 per_pyp_shd.g_old_rec.performance_review_id;
635 End If;
636 If (p_rec.attribute_category = hr_api.g_varchar2) then
637 p_rec.attribute_category :=
638 per_pyp_shd.g_old_rec.attribute_category;
639 End If;
640 If (p_rec.attribute1 = hr_api.g_varchar2) then
641 p_rec.attribute1 :=
642 per_pyp_shd.g_old_rec.attribute1;
643 End If;
644 If (p_rec.attribute2 = hr_api.g_varchar2) then
645 p_rec.attribute2 :=
646 per_pyp_shd.g_old_rec.attribute2;
647 End If;
648 If (p_rec.attribute3 = hr_api.g_varchar2) then
649 p_rec.attribute3 :=
650 per_pyp_shd.g_old_rec.attribute3;
651 End If;
652 If (p_rec.attribute4 = hr_api.g_varchar2) then
653 p_rec.attribute4 :=
654 per_pyp_shd.g_old_rec.attribute4;
655 End If;
656 If (p_rec.attribute5 = hr_api.g_varchar2) then
657 p_rec.attribute5 :=
658 per_pyp_shd.g_old_rec.attribute5;
659 End If;
660 If (p_rec.attribute6 = hr_api.g_varchar2) then
661 p_rec.attribute6 :=
662 per_pyp_shd.g_old_rec.attribute6;
663 End If;
664 If (p_rec.attribute7 = hr_api.g_varchar2) then
665 p_rec.attribute7 :=
666 per_pyp_shd.g_old_rec.attribute7;
667 End If;
668 If (p_rec.attribute8 = hr_api.g_varchar2) then
669 p_rec.attribute8 :=
670 per_pyp_shd.g_old_rec.attribute8;
671 End If;
672 If (p_rec.attribute9 = hr_api.g_varchar2) then
673 p_rec.attribute9 :=
674 per_pyp_shd.g_old_rec.attribute9;
675 End If;
676 If (p_rec.attribute10 = hr_api.g_varchar2) then
677 p_rec.attribute10 :=
678 per_pyp_shd.g_old_rec.attribute10;
679 End If;
680 If (p_rec.attribute11 = hr_api.g_varchar2) then
681 p_rec.attribute11 :=
682 per_pyp_shd.g_old_rec.attribute11;
683 eND iF;
684 If (p_rec.attribute12 = hr_api.g_varchar2) then
685 p_rec.attribute12 :=
686 per_pyp_shd.g_old_rec.attribute12;
687 End If;
688 If (p_rec.attribute13 = hr_api.g_varchar2) then
689 p_rec.attribute13 :=
690 per_pyp_shd.g_old_rec.attribute13;
691 End If;
692 If (p_rec.attribute14 = hr_api.g_varchar2) then
693 p_rec.attribute14 :=
694 per_pyp_shd.g_old_rec.attribute14;
695 End If;
696 If (p_rec.attribute15 = hr_api.g_varchar2) then
697 p_rec.attribute15 :=
698 per_pyp_shd.g_old_rec.attribute15;
699 End If;
700 If (p_rec.attribute16 = hr_api.g_varchar2) then
701 p_rec.attribute16 :=
702 per_pyp_shd.g_old_rec.attribute16;
703 End If;
704 If (p_rec.attribute17 = hr_api.g_varchar2) then
705 p_rec.attribute17 :=
706 per_pyp_shd.g_old_rec.attribute17;
707 End If;
708 If (p_rec.attribute18 = hr_api.g_varchar2) then
709 p_rec.attribute18 :=
710 per_pyp_shd.g_old_rec.attribute18;
711 End If;
712 If (p_rec.attribute19 = hr_api.g_varchar2) then
713 p_rec.attribute19 :=
714 per_pyp_shd.g_old_rec.attribute19;
715 End If;
716 If (p_rec.attribute20 = hr_api.g_varchar2) then
717 p_rec.attribute20 :=
718 per_pyp_shd.g_old_rec.attribute20;
719 End If;
720 If (p_rec.multiple_components = hr_api.g_varchar2) then
721 p_rec.multiple_components :=
722 per_pyp_shd.g_old_rec.multiple_components;
723 End If;
724 If (p_rec.approved = hr_api.g_varchar2) then
725 p_rec.approved :=
726 per_pyp_shd.g_old_rec.approved;
727 End If;
728 --
729 hr_utility.set_location(' Leaving:'||l_proc, 10);
730 --
731 End convert_defs;
732 --
733 -- ----------------------------------------------------------------------------
734 -- |---------------------------------< upd >----------------------------------|
735 -- ----------------------------------------------------------------------------
736 Procedure upd
737 (
738 p_rec in out nocopy per_pyp_shd.g_rec_type,
739 p_validate in boolean ,
740 p_inv_next_sal_date_warning out nocopy boolean ,
741 p_proposed_salary_warning out nocopy boolean ,
742 p_approved_warning out nocopy boolean ,
743 p_payroll_warning out nocopy boolean
744 ) is
745 --
746 l_proc varchar2(72) := g_package||'upd';
747 l_inv_next_sal_date_warning boolean;
748 l_proposed_salary_warning boolean;
749 l_approved_warning boolean;
750 l_payroll_warning boolean;
751
752 --
753 Begin
754 hr_utility.set_location('Entering:'||l_proc, 5);
755 --
756 -- Determine if the business process is to be validated.
757 --
758 If p_validate then
759 --
760 -- Issue the savepoint.
761 --
762 SAVEPOINT upd_per_pyp;
763 End If;
764 --
765 -- We must lock the row which we need to update.
766 --
767 per_pyp_shd.lck
768 (
769 p_rec.pay_proposal_id,
770 p_rec.object_version_number
771 );
772 --
773 -- 1. During an update system defaults are used to determine if
774 -- arguments have been defaulted or not. We must therefore
775 -- derive the full record structure values to be updated.
776 --
777 -- 2. Call the supporting update validate operations.
778 --
779 convert_defs(p_rec);
780 per_pyp_bus.update_validate
781 (p_rec => p_rec
782 ,p_inv_next_sal_date_warning => l_inv_next_sal_date_warning
783 ,p_proposed_salary_warning => l_proposed_salary_warning
784 ,p_approved_warning => l_approved_warning
785 ,p_payroll_warning => l_payroll_warning
786 );
787 --
788 -- Call the supporting pre-update operation
789 --
790 pre_update(p_rec);
791 --
792 -- Update the row.
793 --
794 update_dml(p_rec);
795 --
796 -- Call the supporting post-update operation
797 --
798 post_update(p_rec => p_rec
799 ,p_inv_next_sal_date_warning => l_inv_next_sal_date_warning
800 ,p_proposed_salary_warning => l_proposed_salary_warning
801 ,p_approved_warning => l_approved_warning
802 ,p_payroll_warning => l_payroll_warning
803 );
804
805 p_inv_next_sal_date_warning := l_inv_next_sal_date_warning;
806 p_proposed_salary_warning := l_proposed_salary_warning;
807 p_approved_warning := l_approved_warning;
808 p_payroll_warning := l_payroll_warning;
809
810 --
811 -- If we are validating then raise the Validate_Enabled exception
812 --
813 If p_validate then
814 Raise HR_Api.Validate_Enabled;
815 End If;
816 --
817 hr_utility.set_location(' Leaving:'||l_proc, 10);
818 Exception
819 When HR_Api.Validate_Enabled Then
820 --
821 -- As the Validate_Enabled exception has been raised
822 -- we must rollback to the savepoint
823 --
824 ROLLBACK TO upd_per_pyp;
825 End upd;
826 --
827 -- ----------------------------------------------------------------------------
828 -- |---------------------------------< upd >----------------------------------|
829 -- ----------------------------------------------------------------------------
830 Procedure upd
831 (
832 p_pay_proposal_id in number,
833 p_change_date in date,
834 p_comments in varchar2,
835 p_next_sal_review_date in date,
836 p_proposal_reason in varchar2,
837 p_proposed_salary_n in number,
838 p_forced_ranking in number,
839 p_date_to in date,
840 p_performance_review_id in number,
841 p_attribute_category in varchar2,
842 p_attribute1 in varchar2,
843 p_attribute2 in varchar2,
844 p_attribute3 in varchar2,
845 p_attribute4 in varchar2,
846 p_attribute5 in varchar2,
847 p_attribute6 in varchar2,
848 p_attribute7 in varchar2,
849 p_attribute8 in varchar2,
850 p_attribute9 in varchar2,
851 p_attribute10 in varchar2,
852 p_attribute11 in varchar2,
853 p_attribute12 in varchar2,
854 p_attribute13 in varchar2,
855 p_attribute14 in varchar2,
856 p_attribute15 in varchar2,
857 p_attribute16 in varchar2,
858 p_attribute17 in varchar2,
859 p_attribute18 in varchar2,
860 p_attribute19 in varchar2,
861 p_attribute20 in varchar2,
862 p_object_version_number in out nocopy number,
863 p_multiple_components in varchar2,
864 p_approved in varchar2,
865 p_validate in boolean,
866 p_inv_next_sal_date_warning out nocopy boolean,
867 p_proposed_salary_warning out nocopy boolean,
868 p_approved_warning out nocopy boolean,
869 p_payroll_warning out nocopy boolean
870
871 ) is
872 --
873 l_rec per_pyp_shd.g_rec_type;
874 l_proc varchar2(72) := g_package||'upd';
875 --
876 Begin
877 hr_utility.set_location('Entering:'||l_proc, 5);
878 hr_utility.set_location('ovn= '||to_char(p_object_version_number)||l_proc, 5);
879 --
880 -- Call conversion function to turn arguments into the
881 -- l_rec structure.
882 --
883 l_rec :=
884 per_pyp_shd.convert_args
885 (
886 p_pay_proposal_id,
887 hr_api.g_number,
888 hr_api.g_number,
889 p_change_date,
890 p_comments,
891 hr_api.g_date,
892 p_next_sal_review_date,
893 p_proposal_reason,
894 p_proposed_salary_n,
895 p_forced_ranking,
896 p_date_to,
897 p_performance_review_id,
898 p_attribute_category,
899 p_attribute1,
900 p_attribute2,
901 p_attribute3,
902 p_attribute4,
903 p_attribute5,
904 p_attribute6,
905 p_attribute7,
906 p_attribute8,
907 p_attribute9,
908 p_attribute10,
909 p_attribute11,
910 p_attribute12,
911 p_attribute13,
912 p_attribute14,
913 p_attribute15,
914 p_attribute16,
915 p_attribute17,
916 p_attribute18,
917 p_attribute19,
918 p_attribute20,
919 p_object_version_number,
920 p_multiple_components,
921 p_approved
922 );
923 --
924 -- Having converted the arguments into the
925 -- plsql record structure we call the corresponding record
926 -- business process.
927 --
928 upd(p_rec => l_rec
929 ,p_validate => p_validate
930 ,p_inv_next_sal_date_warning => p_inv_next_sal_date_warning
931 ,p_proposed_salary_warning => p_proposed_salary_warning
932 ,p_approved_warning => p_approved_warning
933 ,p_payroll_warning => p_payroll_warning
934 );
935 --
936 p_object_version_number := l_rec.object_version_number;
937 --
938 hr_utility.set_location(' Leaving:'||l_proc, 10);
939 End upd;
940 --
941 end per_pyp_upd;