[Home] [Help]
PACKAGE BODY: APPS.PQP_AAT_API
Source
1 Package Body pqp_aat_api as
2 /* $Header: pqaatapi.pkb 120.6 2011/08/01 06:39:46 pbalu ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' pqp_aat_api.';
7
8 --
9 -- ---------------------------------------------------------------------------+
10 -- |---------------------< <create_assignment_attribute> >--------------------|
11 -- ---------------------------------------------------------------------------+
12 --
13 procedure create_assignment_attribute
14 (p_validate in boolean default false
15 ,p_effective_date in date
16 ,p_business_group_id in number
17 ,p_effective_start_date out nocopy date
18 ,p_effective_end_date out nocopy date
19 ,p_assignment_id in number
20 ,p_contract_type in varchar2 default null
21 ,p_work_pattern in varchar2 default null
22 ,p_start_day in varchar2 default null
23 ,p_primary_company_car in number default null
24 ,p_primary_car_fuel_benefit in varchar2 default null
25 ,p_primary_capital_contribution in number default null
26 ,p_primary_class_1a in varchar2 default null
27 ,p_secondary_company_car in number default null
28 ,p_secondary_car_fuel_benefit in varchar2 default null
29 ,p_secondary_capital_contributi in number default null
30 ,p_secondary_class_1a in varchar2 default null
31 ,p_company_car_calc_method in varchar2 default null
32 ,p_company_car_rates_table_id in number default null
33 ,p_company_car_secondary_table in number default null
34 ,p_private_car in number default null
35 ,p_private_car_calc_method in varchar2 default null
36 ,p_private_car_rates_table_id in number default null
37 ,p_private_car_essential_table in number default null
38 ,p_primary_private_contribution in number default null
39 ,p_secondary_private_contributi in number default null
40 ,p_tp_is_teacher in varchar2 default null
41 --added for head Teacher seconded location for salary scale calculation
42 ,p_tp_headteacher_grp_code in number default null
43 ,p_tp_safeguarded_grade in varchar2 default null
44 ,p_tp_safeguarded_grade_id in number default null
45 ,p_tp_safeguarded_rate_type in varchar2 default null
46 ,p_tp_safeguarded_rate_id in number default null
47 ,p_tp_spinal_point_id in number default null
48 ,p_tp_elected_pension in varchar2 default null
49 ,p_tp_fast_track in varchar2 default null
50 ,p_aat_attribute_category in varchar2 default null
51 ,p_aat_attribute1 in varchar2 default null
52 ,p_aat_attribute2 in varchar2 default null
53 ,p_aat_attribute3 in varchar2 default null
54 ,p_aat_attribute4 in varchar2 default null
55 ,p_aat_attribute5 in varchar2 default null
56 ,p_aat_attribute6 in varchar2 default null
57 ,p_aat_attribute7 in varchar2 default null
58 ,p_aat_attribute8 in varchar2 default null
59 ,p_aat_attribute9 in varchar2 default null
60 ,p_aat_attribute10 in varchar2 default null
61 ,p_aat_attribute11 in varchar2 default null
62 ,p_aat_attribute12 in varchar2 default null
63 ,p_aat_attribute13 in varchar2 default null
64 ,p_aat_attribute14 in varchar2 default null
65 ,p_aat_attribute15 in varchar2 default null
66 ,p_aat_attribute16 in varchar2 default null
67 ,p_aat_attribute17 in varchar2 default null
68 ,p_aat_attribute18 in varchar2 default null
69 ,p_aat_attribute19 in varchar2 default null
70 ,p_aat_attribute20 in varchar2 default null
71 ,p_aat_information_category in varchar2 default null
72 ,p_aat_information1 in varchar2 default null
73 ,p_aat_information2 in varchar2 default null
74 ,p_aat_information3 in varchar2 default null
75 ,p_aat_information4 in varchar2 default null
76 ,p_aat_information5 in varchar2 default null
77 ,p_aat_information6 in varchar2 default null
78 ,p_aat_information7 in varchar2 default null
79 ,p_aat_information8 in varchar2 default null
80 ,p_aat_information9 in varchar2 default null
81 ,p_aat_information10 in varchar2 default null
82 ,p_aat_information11 in varchar2 default null
83 ,p_aat_information12 in varchar2 default null
84 ,p_aat_information13 in varchar2 default null
85 ,p_aat_information14 in varchar2 default null
86 ,p_aat_information15 in varchar2 default null
87 ,p_aat_information16 in varchar2 default null
88 ,p_aat_information17 in varchar2 default null
89 ,p_aat_information18 in varchar2 default null
90 ,p_aat_information19 in varchar2 default null
91 ,p_aat_information20 in varchar2 default null
92 ,p_lgps_process_flag in varchar2 default null
93 ,p_lgps_exclusion_type in varchar2 default null
94 ,p_lgps_pensionable_pay in varchar2 default null
95 ,p_lgps_trans_arrang_flag in varchar2 default null
96 ,p_lgps_membership_number in varchar2 default null
97 ,p_assignment_attribute_id out nocopy number
98 ,p_object_version_number out nocopy number
99 ) is
100 --
101 -- Declaring cursors and local variables
102
103 l_assignment_attribute_id number;
104 l_object_version_number number;
105 l_proc varchar2(72) := g_package||'create_assignment_attribute';
106 l_effective_date date;
107 l_effective_start_date date;
108 l_effective_end_date date;
109
110 --
111 begin
112 hr_utility.set_location('Entering:'|| l_proc, 10);
113 --
114 -- Issue a savepoint
115 --
116 savepoint create_assignment_attribute;
117 --
118 -- Truncate the time portion from all IN date parameters
119 --
120 l_effective_date := trunc(p_effective_date);
121 l_effective_start_date := trunc(p_effective_start_date);
122 l_effective_end_date := trunc(p_effective_end_date);
123
124
125 --
126 -- Call Before Process User Hook
127 --
128 begin
129 pqp_aat_api_bk1.create_assignment_attribute_b
130 (p_effective_date => l_effective_date
131 ,p_business_group_id => p_business_group_id
132 ,p_effective_start_date => l_effective_start_date
133 ,p_effective_end_date => l_effective_end_date
134 ,p_assignment_id => p_assignment_id
135 ,p_contract_type => p_contract_type
136 ,p_work_pattern => p_work_pattern
137 ,p_start_day => p_start_day
138 ,p_primary_company_car => p_primary_company_car
139 ,p_primary_car_fuel_benefit => p_primary_car_fuel_benefit
140 ,p_primary_capital_contribution => p_primary_capital_contribution
141 ,p_primary_class_1a => p_primary_class_1a
142 ,p_secondary_company_car => p_secondary_company_car
143 ,p_secondary_car_fuel_benefit => p_secondary_car_fuel_benefit
144 ,p_secondary_capital_contributi => p_secondary_capital_contributi
145 ,p_secondary_class_1a => p_secondary_class_1a
146 ,p_company_car_calc_method => p_company_car_calc_method
147 ,p_company_car_rates_table_id => p_company_car_rates_table_id
148 ,p_company_car_secondary_table => p_company_car_secondary_table
149 ,p_private_car => p_private_car
150 ,p_private_car_calc_method => p_private_car_calc_method
151 ,p_private_car_rates_table_id => p_private_car_rates_table_id
152 ,p_private_car_essential_table => p_private_car_essential_table
153 ,p_primary_private_contribution => p_primary_private_contribution
154 ,p_secondary_private_contributi => p_secondary_private_contributi
155 ,p_tp_is_teacher => p_tp_is_teacher
156 --added for head Teacher seconded location for salary scale calculation
157 ,p_tp_headteacher_grp_code => p_tp_headteacher_grp_code
158 ,p_tp_safeguarded_grade => p_tp_safeguarded_grade
159 ,p_tp_safeguarded_grade_id => p_tp_safeguarded_grade_id
160 ,p_tp_safeguarded_rate_type => p_tp_safeguarded_rate_type
161 ,p_tp_safeguarded_rate_id => p_tp_safeguarded_rate_id
162 ,p_tp_spinal_point_id => p_tp_spinal_point_id
163 ,p_tp_elected_pension => p_tp_elected_pension
164 ,p_tp_fast_track => p_tp_fast_track
165 ,p_aat_attribute_category => p_aat_attribute_category
166 ,p_aat_attribute1 => p_aat_attribute1
167 ,p_aat_attribute2 => p_aat_attribute2
168 ,p_aat_attribute3 => p_aat_attribute3
169 ,p_aat_attribute4 => p_aat_attribute4
170 ,p_aat_attribute5 => p_aat_attribute5
171 ,p_aat_attribute6 => p_aat_attribute6
172 ,p_aat_attribute7 => p_aat_attribute7
173 ,p_aat_attribute8 => p_aat_attribute8
174 ,p_aat_attribute9 => p_aat_attribute9
175 ,p_aat_attribute10 => p_aat_attribute10
176 ,p_aat_attribute11 => p_aat_attribute11
177 ,p_aat_attribute12 => p_aat_attribute12
178 ,p_aat_attribute13 => p_aat_attribute13
179 ,p_aat_attribute14 => p_aat_attribute14
180 ,p_aat_attribute15 => p_aat_attribute15
181 ,p_aat_attribute16 => p_aat_attribute16
182 ,p_aat_attribute17 => p_aat_attribute17
183 ,p_aat_attribute18 => p_aat_attribute18
184 ,p_aat_attribute19 => p_aat_attribute19
185 ,p_aat_attribute20 => p_aat_attribute20
186 ,p_aat_information_category => p_aat_information_category
187 ,p_aat_information1 => p_aat_information1
188 ,p_aat_information2 => p_aat_information2
189 ,p_aat_information3 => p_aat_information3
190 ,p_aat_information4 => p_aat_information4
191 ,p_aat_information5 => p_aat_information5
192 ,p_aat_information6 => p_aat_information6
193 ,p_aat_information7 => p_aat_information7
194 ,p_aat_information8 => p_aat_information8
195 ,p_aat_information9 => p_aat_information9
196 ,p_aat_information10 => p_aat_information10
197 ,p_aat_information11 => p_aat_information11
198 ,p_aat_information12 => p_aat_information12
199 ,p_aat_information13 => p_aat_information13
200 ,p_aat_information14 => p_aat_information14
201 ,p_aat_information15 => p_aat_information15
202 ,p_aat_information16 => p_aat_information16
203 ,p_aat_information17 => p_aat_information17
204 ,p_aat_information18 => p_aat_information18
205 ,p_aat_information19 => p_aat_information19
206 ,p_aat_information20 => p_aat_information20
207 ,p_lgps_process_flag => p_lgps_process_flag
208 ,p_lgps_exclusion_type => p_lgps_exclusion_type
209 ,p_lgps_pensionable_pay => p_lgps_pensionable_pay
210 ,p_lgps_trans_arrang_flag => p_lgps_trans_arrang_flag
211 ,p_lgps_membership_number => p_lgps_membership_number
212 );
213 exception
214 when hr_api.cannot_find_prog_unit then
215 hr_api.cannot_find_prog_unit_error
216 (p_module_name => 'create_assignment_attribute'
217 ,p_hook_type => 'BP'
218 );
219 end;
220 --
221 -- Validation in addition to Row Handlers
222 --
223
224
225
226 --
227 -- Process Logic, calling Row Handler for insert
228
229 pqp_aat_ins.ins
230 (p_effective_date => l_effective_date
231 ,p_business_group_id => p_business_group_id
232 ,p_effective_start_date => l_effective_start_date
233 ,p_effective_end_date => l_effective_end_date
234 ,p_assignment_id => p_assignment_id
235 ,p_contract_type => p_contract_type
236 ,p_work_pattern => p_work_pattern
237 ,p_start_day => p_start_day
238 ,p_primary_company_car => p_primary_company_car
239 ,p_primary_car_fuel_benefit => p_primary_car_fuel_benefit
240 ,p_primary_capital_contribution => p_primary_capital_contribution
241 ,p_primary_class_1a => p_primary_class_1a
242 ,p_secondary_company_car => p_secondary_company_car
243 ,p_secondary_car_fuel_benefit => p_secondary_car_fuel_benefit
244 ,p_secondary_capital_contributi => p_secondary_capital_contributi
245 ,p_secondary_class_1a => p_secondary_class_1a
246 ,p_company_car_calc_method => p_company_car_calc_method
247 ,p_company_car_rates_table_id => p_company_car_rates_table_id
248 ,p_company_car_secondary_table => p_company_car_secondary_table
249 ,p_private_car => p_private_car
250 ,p_private_car_calc_method => p_private_car_calc_method
251 ,p_private_car_rates_table_id => p_private_car_rates_table_id
252 ,p_private_car_essential_table => p_private_car_essential_table
253 ,p_primary_private_contribution => p_primary_private_contribution
254 ,p_secondary_private_contributi => p_secondary_private_contributi
255 ,p_tp_is_teacher => p_tp_is_teacher
256 --added for head Teacher seconded location for salary scale calculation
257 ,p_tp_headteacher_grp_code => p_tp_headteacher_grp_code
258 ,p_tp_safeguarded_grade => p_tp_safeguarded_grade
259 ,p_tp_safeguarded_grade_id => p_tp_safeguarded_grade_id
260 ,p_tp_safeguarded_rate_type => p_tp_safeguarded_rate_type
261 ,p_tp_safeguarded_rate_id => p_tp_safeguarded_rate_id
262 ,p_tp_spinal_point_id => p_tp_spinal_point_id
263 ,p_tp_elected_pension => p_tp_elected_pension
264 ,p_tp_fast_track => p_tp_fast_track
265 ,p_aat_attribute_category => p_aat_attribute_category
266 ,p_aat_attribute1 => p_aat_attribute1
267 ,p_aat_attribute2 => p_aat_attribute2
268 ,p_aat_attribute3 => p_aat_attribute3
269 ,p_aat_attribute4 => p_aat_attribute4
270 ,p_aat_attribute5 => p_aat_attribute5
271 ,p_aat_attribute6 => p_aat_attribute6
272 ,p_aat_attribute7 => p_aat_attribute7
273 ,p_aat_attribute8 => p_aat_attribute8
274 ,p_aat_attribute9 => p_aat_attribute9
275 ,p_aat_attribute10 => p_aat_attribute10
276 ,p_aat_attribute11 => p_aat_attribute11
277 ,p_aat_attribute12 => p_aat_attribute12
278 ,p_aat_attribute13 => p_aat_attribute13
279 ,p_aat_attribute14 => p_aat_attribute14
280 ,p_aat_attribute15 => p_aat_attribute15
281 ,p_aat_attribute16 => p_aat_attribute16
282 ,p_aat_attribute17 => p_aat_attribute17
283 ,p_aat_attribute18 => p_aat_attribute18
284 ,p_aat_attribute19 => p_aat_attribute19
285 ,p_aat_attribute20 => p_aat_attribute20
286 ,p_aat_information_category => p_aat_information_category
287 ,p_aat_information1 => p_aat_information1
288 ,p_aat_information2 => p_aat_information2
289 ,p_aat_information3 => p_aat_information3
290 ,p_aat_information4 => p_aat_information4
291 ,p_aat_information5 => p_aat_information5
292 ,p_aat_information6 => p_aat_information6
293 ,p_aat_information7 => p_aat_information7
294 ,p_aat_information8 => p_aat_information8
295 ,p_aat_information9 => p_aat_information9
296 ,p_aat_information10 => p_aat_information10
297 ,p_aat_information11 => p_aat_information11
298 ,p_aat_information12 => p_aat_information12
299 ,p_aat_information13 => p_aat_information13
300 ,p_aat_information14 => p_aat_information14
301 ,p_aat_information15 => p_aat_information15
302 ,p_aat_information16 => p_aat_information16
303 ,p_aat_information17 => p_aat_information17
304 ,p_aat_information18 => p_aat_information18
305 ,p_aat_information19 => p_aat_information19
306 ,p_aat_information20 => p_aat_information20
307 ,p_lgps_process_flag => p_lgps_process_flag
308 ,p_lgps_exclusion_type => p_lgps_exclusion_type
309 ,p_lgps_pensionable_pay => p_lgps_pensionable_pay
310 ,p_lgps_trans_arrang_flag => p_lgps_trans_arrang_flag
311 ,p_lgps_membership_number => p_lgps_membership_number
312 ,p_assignment_attribute_id => l_assignment_attribute_id
313 ,p_object_version_number => l_object_version_number
314 );
315 --
316
317
318 --
319 -- Call After Process User Hook
320 --
321 begin
322 pqp_aat_api_bk1.create_assignment_attribute_a
323 (p_effective_date => l_effective_date
324 ,p_business_group_id => p_business_group_id
325 ,p_effective_start_date => l_effective_start_date
326 ,p_effective_end_date => l_effective_end_date
327 ,p_assignment_id => p_assignment_id
328 ,p_contract_type => p_contract_type
329 ,p_work_pattern => p_work_pattern
330 ,p_start_day => p_start_day
331 ,p_primary_company_car => p_primary_company_car
332 ,p_primary_car_fuel_benefit => p_primary_car_fuel_benefit
333 ,p_primary_capital_contribution => p_primary_capital_contribution
334 ,p_primary_class_1a => p_primary_class_1a
335 ,p_secondary_company_car => p_secondary_company_car
336 ,p_secondary_car_fuel_benefit => p_secondary_car_fuel_benefit
337 ,p_secondary_capital_contributi => p_secondary_capital_contributi
338 ,p_secondary_class_1a => p_secondary_class_1a
339 ,p_company_car_calc_method => p_company_car_calc_method
340 ,p_company_car_rates_table_id => p_company_car_rates_table_id
341 ,p_company_car_secondary_table => p_company_car_secondary_table
342 ,p_private_car => p_private_car
343 ,p_private_car_calc_method => p_private_car_calc_method
344 ,p_private_car_rates_table_id => p_private_car_rates_table_id
345 ,p_private_car_essential_table => p_private_car_essential_table
346 ,p_primary_private_contribution => p_primary_private_contribution
347 ,p_secondary_private_contributi => p_secondary_private_contributi
348 ,p_tp_is_teacher => p_tp_is_teacher
349 --added for head Teacher seconded location for salary scale calculation
350 ,p_tp_headteacher_grp_code => p_tp_headteacher_grp_code
351 ,p_tp_safeguarded_grade => p_tp_safeguarded_grade
352 ,p_tp_safeguarded_grade_id => p_tp_safeguarded_grade_id
353 ,p_tp_safeguarded_rate_type => p_tp_safeguarded_rate_type
354 ,p_tp_safeguarded_rate_id => p_tp_safeguarded_rate_id
355 ,p_tp_spinal_point_id => p_tp_spinal_point_id
356 ,p_tp_elected_pension => p_tp_elected_pension
357 ,p_tp_fast_track => p_tp_fast_track
358 ,p_aat_attribute_category => p_aat_attribute_category
359 ,p_aat_attribute1 => p_aat_attribute1
360 ,p_aat_attribute2 => p_aat_attribute2
361 ,p_aat_attribute3 => p_aat_attribute3
362 ,p_aat_attribute4 => p_aat_attribute4
363 ,p_aat_attribute5 => p_aat_attribute5
364 ,p_aat_attribute6 => p_aat_attribute6
365 ,p_aat_attribute7 => p_aat_attribute7
366 ,p_aat_attribute8 => p_aat_attribute8
367 ,p_aat_attribute9 => p_aat_attribute9
368 ,p_aat_attribute10 => p_aat_attribute10
369 ,p_aat_attribute11 => p_aat_attribute11
370 ,p_aat_attribute12 => p_aat_attribute12
371 ,p_aat_attribute13 => p_aat_attribute13
372 ,p_aat_attribute14 => p_aat_attribute14
373 ,p_aat_attribute15 => p_aat_attribute15
374 ,p_aat_attribute16 => p_aat_attribute16
375 ,p_aat_attribute17 => p_aat_attribute17
376 ,p_aat_attribute18 => p_aat_attribute18
377 ,p_aat_attribute19 => p_aat_attribute19
378 ,p_aat_attribute20 => p_aat_attribute20
379 ,p_aat_information_category => p_aat_information_category
380 ,p_aat_information1 => p_aat_information1
381 ,p_aat_information2 => p_aat_information2
382 ,p_aat_information3 => p_aat_information3
383 ,p_aat_information4 => p_aat_information4
384 ,p_aat_information5 => p_aat_information5
385 ,p_aat_information6 => p_aat_information6
386 ,p_aat_information7 => p_aat_information7
387 ,p_aat_information8 => p_aat_information8
388 ,p_aat_information9 => p_aat_information9
389 ,p_aat_information10 => p_aat_information10
390 ,p_aat_information11 => p_aat_information11
391 ,p_aat_information12 => p_aat_information12
392 ,p_aat_information13 => p_aat_information13
393 ,p_aat_information14 => p_aat_information14
394 ,p_aat_information15 => p_aat_information15
395 ,p_aat_information16 => p_aat_information16
396 ,p_aat_information17 => p_aat_information17
397 ,p_aat_information18 => p_aat_information18
398 ,p_aat_information19 => p_aat_information19
399 ,p_aat_information20 => p_aat_information20
400 ,p_lgps_process_flag => p_lgps_process_flag
401 ,p_lgps_exclusion_type => p_lgps_exclusion_type
402 ,p_lgps_pensionable_pay => p_lgps_pensionable_pay
403 ,p_lgps_trans_arrang_flag => p_lgps_trans_arrang_flag
404 ,p_lgps_membership_number => p_lgps_membership_number
405 ,p_assignment_attribute_id =>l_assignment_attribute_id
406 ,p_object_version_number => l_object_version_number
407 );
408 exception
409 when hr_api.cannot_find_prog_unit then
410 hr_api.cannot_find_prog_unit_error
411 (p_module_name => 'create_assignment_attribute'
412 ,p_hook_type => 'AP'
413 );
414 end;
415 --
416 -- When in validation only mode raise the Validate_Enabled exception
417 --
418 if p_validate then
419 raise hr_api.validate_enabled;
420 end if;
421 --
422 -- Set all output arguments
423 --
424 p_assignment_attribute_id := l_assignment_attribute_id;
425 p_object_version_number := l_object_version_number;
426 p_effective_start_date := l_effective_start_date;
427 p_effective_end_date := l_effective_end_date;
428
429 --
430 hr_utility.set_location(' Leaving:'||l_proc, 70);
431 exception
432 when hr_api.validate_enabled then
433 --
434 -- As the Validate_Enabled exception has been raised
435 -- we must rollback to the savepoint
436 --
437 rollback to create_assignment_attribute;
438 --
439 -- Only set output warning arguments
440 -- (Any key or derived arguments must be set to null
441 -- when validation only mode is being used.)
442 --
443 p_assignment_attribute_id := null;
444 p_object_version_number := null;
445 p_effective_start_date := null;
446 p_effective_end_date := null;
447
448 hr_utility.set_location(' Leaving:'||l_proc, 80);
449 when others then
450 --
451 -- A validation or unexpected error has occured
452 --
453 rollback to create_assignment_attribute;
454 p_assignment_attribute_id := null;
455 p_object_version_number := null;
456 p_effective_start_date := null;
457 p_effective_end_date := null;
458 hr_utility.set_location(' Leaving:'||l_proc, 90);
459 raise;
460 end create_assignment_attribute;
461 --
462 --
463 -- ---------------------------------------------------------------------------+
464 -- |---------------------< <update_assignment_attribute> >--------------------|
465 -- ---------------------------------------------------------------------------+
466 --
467 procedure update_assignment_attribute
468 (p_validate in boolean default false
469 ,p_effective_date in date
470 ,p_datetrack_mode in varchar2
471 ,p_assignment_attribute_id in number
472 ,p_business_group_id in number
473 ,p_effective_start_date out nocopy date
474 ,p_effective_end_date out nocopy date
475 ,p_assignment_id in number default hr_api.g_number
476 ,p_contract_type in varchar2 default hr_api.g_varchar2
477 ,p_work_pattern in varchar2 default hr_api.g_varchar2
478 ,p_start_day in varchar2 default hr_api.g_varchar2
479 ,p_primary_company_car in number default hr_api.g_number
480 ,p_primary_car_fuel_benefit in varchar2 default hr_api.g_varchar2
481 ,p_primary_capital_contribution in number default hr_api.g_number
482 ,p_primary_class_1a in varchar2 default hr_api.g_varchar2
483 ,p_secondary_company_car in number default hr_api.g_number
484 ,p_secondary_car_fuel_benefit in varchar2 default hr_api.g_varchar2
485 ,p_secondary_capital_contributi in number default hr_api.g_number
486 ,p_secondary_class_1a in varchar2 default hr_api.g_varchar2
487 ,p_company_car_calc_method in varchar2 default hr_api.g_varchar2
488 ,p_company_car_rates_table_id in number default hr_api.g_number
489 ,p_company_car_secondary_table in number default hr_api.g_number
490 ,p_private_car in number default hr_api.g_number
491 ,p_private_car_calc_method in varchar2 default hr_api.g_varchar2
492 ,p_private_car_rates_table_id in number default hr_api.g_number
493 ,p_private_car_essential_table in number default hr_api.g_number
494 ,p_primary_private_contribution in number default hr_api.g_number
495 ,p_secondary_private_contributi in number default hr_api.g_number
496 ,p_tp_is_teacher in varchar2 default hr_api.g_varchar2
497 --added for head Teacher seconded location for salary scale calculation
498 ,p_tp_headteacher_grp_code in number default hr_api.g_number
499 ,p_tp_safeguarded_grade in varchar2 default hr_api.g_varchar2
500 ,p_tp_safeguarded_grade_id in number default hr_api.g_number
501 ,p_tp_safeguarded_rate_type in varchar2 default hr_api.g_varchar2
502 ,p_tp_safeguarded_rate_id in number default hr_api.g_number
503 ,p_tp_spinal_point_id in number default hr_api.g_number
504 ,p_tp_elected_pension in varchar2 default hr_api.g_varchar2
505 ,p_tp_fast_track in varchar2 default hr_api.g_varchar2
506 ,p_aat_attribute_category in varchar2 default hr_api.g_varchar2
507 ,p_aat_attribute1 in varchar2 default hr_api.g_varchar2
508 ,p_aat_attribute2 in varchar2 default hr_api.g_varchar2
509 ,p_aat_attribute3 in varchar2 default hr_api.g_varchar2
510 ,p_aat_attribute4 in varchar2 default hr_api.g_varchar2
511 ,p_aat_attribute5 in varchar2 default hr_api.g_varchar2
512 ,p_aat_attribute6 in varchar2 default hr_api.g_varchar2
513 ,p_aat_attribute7 in varchar2 default hr_api.g_varchar2
514 ,p_aat_attribute8 in varchar2 default hr_api.g_varchar2
515 ,p_aat_attribute9 in varchar2 default hr_api.g_varchar2
516 ,p_aat_attribute10 in varchar2 default hr_api.g_varchar2
517 ,p_aat_attribute11 in varchar2 default hr_api.g_varchar2
518 ,p_aat_attribute12 in varchar2 default hr_api.g_varchar2
519 ,p_aat_attribute13 in varchar2 default hr_api.g_varchar2
520 ,p_aat_attribute14 in varchar2 default hr_api.g_varchar2
521 ,p_aat_attribute15 in varchar2 default hr_api.g_varchar2
522 ,p_aat_attribute16 in varchar2 default hr_api.g_varchar2
523 ,p_aat_attribute17 in varchar2 default hr_api.g_varchar2
524 ,p_aat_attribute18 in varchar2 default hr_api.g_varchar2
525 ,p_aat_attribute19 in varchar2 default hr_api.g_varchar2
526 ,p_aat_attribute20 in varchar2 default hr_api.g_varchar2
527 ,p_aat_information_category in varchar2 default hr_api.g_varchar2
528 ,p_aat_information1 in varchar2 default hr_api.g_varchar2
529 ,p_aat_information2 in varchar2 default hr_api.g_varchar2
530 ,p_aat_information3 in varchar2 default hr_api.g_varchar2
531 ,p_aat_information4 in varchar2 default hr_api.g_varchar2
532 ,p_aat_information5 in varchar2 default hr_api.g_varchar2
533 ,p_aat_information6 in varchar2 default hr_api.g_varchar2
534 ,p_aat_information7 in varchar2 default hr_api.g_varchar2
535 ,p_aat_information8 in varchar2 default hr_api.g_varchar2
536 ,p_aat_information9 in varchar2 default hr_api.g_varchar2
537 ,p_aat_information10 in varchar2 default hr_api.g_varchar2
538 ,p_aat_information11 in varchar2 default hr_api.g_varchar2
539 ,p_aat_information12 in varchar2 default hr_api.g_varchar2
540 ,p_aat_information13 in varchar2 default hr_api.g_varchar2
541 ,p_aat_information14 in varchar2 default hr_api.g_varchar2
542 ,p_aat_information15 in varchar2 default hr_api.g_varchar2
543 ,p_aat_information16 in varchar2 default hr_api.g_varchar2
544 ,p_aat_information17 in varchar2 default hr_api.g_varchar2
545 ,p_aat_information18 in varchar2 default hr_api.g_varchar2
546 ,p_aat_information19 in varchar2 default hr_api.g_varchar2
547 ,p_aat_information20 in varchar2 default hr_api.g_varchar2
548 ,p_lgps_process_flag in varchar2 default hr_api.g_varchar2
549 ,p_lgps_exclusion_type in varchar2 default hr_api.g_varchar2
550 ,p_lgps_pensionable_pay in varchar2 default hr_api.g_varchar2
551 ,p_lgps_trans_arrang_flag in varchar2 default hr_api.g_varchar2
552 ,p_lgps_membership_number in varchar2 default hr_api.g_varchar2
553 ,p_object_version_number in out nocopy number
554 ) is
555 --
556 -- Declaring cursors and local variables
557 --
558 l_object_version_number number;
559 l_proc varchar2(72) := g_package||'update_assignment_attribute';
560 l_effective_date date;
561 l_effective_start_date date;
562 l_effective_end_date date;
563 --10129497 begin
564 --Logic to rollover the start day of the workpattern whenever other fields in this table
565 --are updated but not the contract details or workpattern or start day.
566 CURSOR c_contract_workpattern IS
567 SELECT effective_start_date, effective_end_date effective_end_date,
568 contract_type, work_pattern, start_day, object_version_number
569 FROM pqp_assignment_attributes_f
570 WHERE assignment_id = p_assignment_id
571 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
572
573 l_legislation_code pay_user_rows_f.legislation_code%TYPE;
574
575 CURSOR c_get_legcode
576 (p_business_group_id NUMBER
577 )IS
578 SELECT legislation_code
579 FROM per_business_groups_perf
580 WHERE business_group_id = p_business_group_id;
581
582 CURSOR c_get_days(l_work_pattern varchar2) IS
583 SELECT COUNT(pur.row_low_range_or_name)
584 FROM pay_user_rows_f pur
585 WHERE pur.user_row_id IN
586 (SELECT DISTINCT uci.user_row_id
587 FROM pay_user_tables put,
588 pay_user_columns puc,
589 pay_user_column_instances_f uci
590 WHERE put.user_table_name = 'PQP_COMPANY_WORK_PATTERNS'
591 AND put.legislation_code = l_legislation_code
592 AND puc.user_table_id = put.user_table_id
593 AND puc.user_column_name = l_work_pattern
594 AND (
595 puc.business_group_id = p_business_group_id
596 OR
597 (puc.business_group_id IS NULL
598 AND puc.legislation_code = l_legislation_code)
599 )
600 AND uci.user_column_id = puc.user_column_id
601 AND (
602 uci.business_group_id = p_business_group_id
603 OR
604 (uci.business_group_id IS NULL
605 AND uci.legislation_code = l_legislation_code)
606 )
607 AND (p_effective_date BETWEEN uci.effective_start_date
608 AND uci.effective_end_date
609 OR
610 p_effective_date BETWEEN uci.effective_start_date
611 AND uci.effective_end_date)
612 ) AND pur.row_low_range_or_name like 'Day __';
613
614
615 l_contract_workpattern c_contract_workpattern%ROWTYPE;
616
617 l_start_day pqp_assignment_attributes_f.start_day%TYPE;
618
619 l_wp_days NUMBER:=0;
620 l_no_days NUMBER:=0;
621 l_days_tobe_added NUMBER:=0;
622 l_calc_start_day number:=0;
623 --10129497 End
624 begin
625 hr_utility.set_location('Entering:'|| l_proc, 10);
626 --
627 -- Issue a savepoint
628 --
629 savepoint update_assignment_attribute;
630 --
631 -- Truncate the time portion from all IN date parameters
632 --
633
634 l_effective_date := trunc(p_effective_date);
635 l_effective_start_date := trunc(p_effective_start_date);
636 l_effective_end_date := trunc(p_effective_end_date);
637
638 --10129497 begin
639 l_start_day := p_start_day;
640
641 hr_utility.trace(' p_datetrack_mode '||p_datetrack_mode);
642 hr_utility.trace(' p_start_day ' ||p_start_day);
643
644 open c_contract_workpattern;
645 fetch c_contract_workpattern into l_contract_workpattern;
646 close c_contract_workpattern;
647
648 --If the call is from forms the below condition will fail
649 --hence the below logic will not be fired.
650 if (l_contract_workpattern.contract_type = p_contract_type or p_contract_type = hr_api.g_varchar2) and
651 (l_contract_workpattern.work_pattern = p_work_pattern or p_work_pattern = hr_api.g_varchar2) and
652 (l_contract_workpattern.start_day = p_start_day or p_start_day = hr_api.g_varchar2) then
653
654 if p_datetrack_mode <> 'CORRECTION' then
655
656 l_start_day := l_contract_workpattern.start_day;
657 OPEN c_get_legcode(p_business_group_id);
658 FETCH c_get_legcode INTO l_legislation_code;
659 CLOSE c_get_legcode;
660
661 OPEN c_get_days(l_contract_workpattern.work_pattern);
662 FETCH c_get_days INTO l_wp_days;
663 CLOSE c_get_days;
664
665 hr_utility.trace(' l_start_day '||l_start_day);
666 hr_utility.trace('substr(l_start_day,5) '||substr(l_start_day,5));
667
668 l_no_days := p_effective_date - l_contract_workpattern.effective_start_date;
669 l_days_tobe_added := to_number(substr(l_start_day,5));
670
671 hr_utility.trace(' Current start day '||l_days_tobe_added);
672 hr_utility.trace(' Number of days in work pattern '||l_wp_days);
673 hr_utility.trace(' Number of days between last eff start dt and session dt '||l_no_days);
674 hr_utility.trace(' Sum of l_days_tobe_added and l_no_days '|| (l_days_tobe_added + l_no_days));
675 hr_utility.trace(' mod(l_days_tobe_added + mod(l_no_days,l_wp_days),l_wp_days) '|| mod(l_days_tobe_added + mod(l_no_days,l_wp_days),l_wp_days));
676
677 l_calc_start_day := mod(l_days_tobe_added + l_no_days,l_wp_days);
678 hr_utility.trace(' Calculated start day '||l_calc_start_day);
679 if l_calc_start_day = 0 then
680 l_calc_start_day := l_wp_days;
681 end if;
682
683 --l_start_day := substr(l_start_day,1,4)|| lpad( mod(l_days_tobe_added + mod(l_no_days,l_wp_days),l_wp_days),2,'0');
684
685 l_start_day := substr(l_start_day,1,4)|| lpad(l_calc_start_day,2,'0');
686
687 hr_utility.trace(' Modified l_start_day '||l_start_day);
688 if l_calc_start_day > l_wp_days then
689 hr_utility.trace('Error: The calculated Start day is greater than the number of days in Workpattern');
690 raise_application_error(-20001,' Error: The calculated Start day is greater than the number of days in Workpattern ');
691 end if;
692 end if;
693 end if;
694
695 --10129497 end
696
697 --
698 -- Call Before Process User Hook
699 --
700 begin
701 pqp_aat_api_bk2.update_assignment_attribute_b
702 (p_effective_date => l_effective_date
703 ,p_datetrack_mode => p_datetrack_mode
704 ,p_business_group_id => p_business_group_id
705 ,p_effective_start_date => l_effective_start_date
706 ,p_effective_end_date => l_effective_end_date
707 ,p_assignment_id => p_assignment_id
708 ,p_contract_type => p_contract_type
709 ,p_work_pattern => p_work_pattern
710 ,p_start_day => l_start_day --p_start_day 10129497
711 ,p_primary_company_car => p_primary_company_car
712 ,p_primary_car_fuel_benefit => p_primary_car_fuel_benefit
713 ,p_primary_capital_contribution => p_primary_capital_contribution
714 ,p_primary_class_1a => p_primary_class_1a
715 ,p_secondary_company_car => p_secondary_company_car
716 ,p_secondary_car_fuel_benefit => p_secondary_car_fuel_benefit
717 ,p_secondary_capital_contributi => p_secondary_capital_contributi
718 ,p_secondary_class_1a => p_secondary_class_1a
719 ,p_company_car_calc_method => p_company_car_calc_method
720 ,p_company_car_rates_table_id => p_company_car_rates_table_id
721 ,p_company_car_secondary_table => p_company_car_secondary_table
722 ,p_private_car => p_private_car
723 ,p_private_car_calc_method => p_private_car_calc_method
724 ,p_private_car_rates_table_id => p_private_car_rates_table_id
725 ,p_private_car_essential_table => p_private_car_essential_table
726 ,p_primary_private_contribution => p_primary_private_contribution
727 ,p_secondary_private_contributi => p_secondary_private_contributi
728 ,p_tp_is_teacher => p_tp_is_teacher
729 --added for head Teacher seconded location for salary scale calculation
730 ,p_tp_headteacher_grp_code => p_tp_headteacher_grp_code
731 ,p_tp_safeguarded_grade => p_tp_safeguarded_grade
732 ,p_tp_safeguarded_grade_id => p_tp_safeguarded_grade_id
733 ,p_tp_safeguarded_rate_type => p_tp_safeguarded_rate_type
734 ,p_tp_safeguarded_rate_id => p_tp_safeguarded_rate_id
735 ,p_tp_spinal_point_id => p_tp_spinal_point_id
736 ,p_tp_elected_pension => p_tp_elected_pension
737 ,p_tp_fast_track => p_tp_fast_track
738 ,p_aat_attribute_category => p_aat_attribute_category
739 ,p_aat_attribute1 => p_aat_attribute1
740 ,p_aat_attribute2 => p_aat_attribute2
741 ,p_aat_attribute3 => p_aat_attribute3
742 ,p_aat_attribute4 => p_aat_attribute4
743 ,p_aat_attribute5 => p_aat_attribute5
744 ,p_aat_attribute6 => p_aat_attribute6
745 ,p_aat_attribute7 => p_aat_attribute7
746 ,p_aat_attribute8 => p_aat_attribute8
747 ,p_aat_attribute9 => p_aat_attribute9
748 ,p_aat_attribute10 => p_aat_attribute10
749 ,p_aat_attribute11 => p_aat_attribute11
750 ,p_aat_attribute12 => p_aat_attribute12
751 ,p_aat_attribute13 => p_aat_attribute13
752 ,p_aat_attribute14 => p_aat_attribute14
753 ,p_aat_attribute15 => p_aat_attribute15
754 ,p_aat_attribute16 => p_aat_attribute16
755 ,p_aat_attribute17 => p_aat_attribute17
756 ,p_aat_attribute18 => p_aat_attribute18
757 ,p_aat_attribute19 => p_aat_attribute19
758 ,p_aat_attribute20 => p_aat_attribute20
759 ,p_aat_information_category => p_aat_information_category
760 ,p_aat_information1 => p_aat_information1
761 ,p_aat_information2 => p_aat_information2
762 ,p_aat_information3 => p_aat_information3
763 ,p_aat_information4 => p_aat_information4
764 ,p_aat_information5 => p_aat_information5
765 ,p_aat_information6 => p_aat_information6
766 ,p_aat_information7 => p_aat_information7
767 ,p_aat_information8 => p_aat_information8
768 ,p_aat_information9 => p_aat_information9
769 ,p_aat_information10 => p_aat_information10
770 ,p_aat_information11 => p_aat_information11
771 ,p_aat_information12 => p_aat_information12
772 ,p_aat_information13 => p_aat_information13
773 ,p_aat_information14 => p_aat_information14
774 ,p_aat_information15 => p_aat_information15
775 ,p_aat_information16 => p_aat_information16
776 ,p_aat_information17 => p_aat_information17
777 ,p_aat_information18 => p_aat_information18
778 ,p_aat_information19 => p_aat_information19
779 ,p_aat_information20 => p_aat_information20
780 ,p_lgps_process_flag => p_lgps_process_flag
781 ,p_lgps_exclusion_type => p_lgps_exclusion_type
782 ,p_lgps_pensionable_pay => p_lgps_pensionable_pay
783 ,p_lgps_trans_arrang_flag => p_lgps_trans_arrang_flag
784 ,p_lgps_membership_number => p_lgps_membership_number
785 ,p_assignment_attribute_id =>p_assignment_attribute_id
786 ,p_object_version_number => p_object_version_number
787 );
788 exception
789 when hr_api.cannot_find_prog_unit then
790 hr_api.cannot_find_prog_unit_error
791 (p_module_name => 'update_assignment_attribute'
792 ,p_hook_type => 'BP'
793 );
794 end;
795 --
796 -- Validation in addition to Row Handlers
797 --
798
799 l_object_version_number := p_object_version_number;
800
801 --
802 -- Process Logic, calling Row Handler for update
803 pqp_aat_upd.upd
804 (p_effective_date => l_effective_date
805 ,p_datetrack_mode => p_datetrack_mode
806 ,p_business_group_id => p_business_group_id
807 ,p_effective_start_date => l_effective_start_date
808 ,p_effective_end_date => l_effective_end_date
809 ,p_assignment_id => p_assignment_id
810 ,p_contract_type => p_contract_type
811 ,p_work_pattern => p_work_pattern
812 ,p_start_day => l_start_day --p_start_day 10129497
813 ,p_primary_company_car => p_primary_company_car
814 ,p_primary_car_fuel_benefit => p_primary_car_fuel_benefit
815 ,p_primary_capital_contribution => p_primary_capital_contribution
816 ,p_primary_class_1a => p_primary_class_1a
817 ,p_secondary_company_car => p_secondary_company_car
818 ,p_secondary_car_fuel_benefit => p_secondary_car_fuel_benefit
819 ,p_secondary_capital_contributi => p_secondary_capital_contributi
820 ,p_secondary_class_1a => p_secondary_class_1a
821 ,p_company_car_calc_method => p_company_car_calc_method
822 ,p_company_car_rates_table_id => p_company_car_rates_table_id
823 ,p_company_car_secondary_table => p_company_car_secondary_table
824 ,p_private_car => p_private_car
825 ,p_private_car_calc_method => p_private_car_calc_method
826 ,p_private_car_rates_table_id => p_private_car_rates_table_id
827 ,p_private_car_essential_table => p_private_car_essential_table
828 ,p_primary_private_contribution => p_primary_private_contribution
829 ,p_secondary_private_contributi => p_secondary_private_contributi
830 ,p_tp_is_teacher => p_tp_is_teacher
831 --added for head Teacher seconded location for salary scale calculation
832 ,p_tp_headteacher_grp_code => p_tp_headteacher_grp_code
833 ,p_tp_safeguarded_grade => p_tp_safeguarded_grade
834 ,p_tp_safeguarded_grade_id => p_tp_safeguarded_grade_id
835 ,p_tp_safeguarded_rate_type => p_tp_safeguarded_rate_type
836 ,p_tp_safeguarded_rate_id => p_tp_safeguarded_rate_id
837 ,p_tp_spinal_point_id => p_tp_spinal_point_id
838 ,p_tp_elected_pension => p_tp_elected_pension
839 ,p_tp_fast_track => p_tp_fast_track
840 ,p_aat_attribute_category => p_aat_attribute_category
841 ,p_aat_attribute1 => p_aat_attribute1
842 ,p_aat_attribute2 => p_aat_attribute2
843 ,p_aat_attribute3 => p_aat_attribute3
844 ,p_aat_attribute4 => p_aat_attribute4
845 ,p_aat_attribute5 => p_aat_attribute5
846 ,p_aat_attribute6 => p_aat_attribute6
847 ,p_aat_attribute7 => p_aat_attribute7
848 ,p_aat_attribute8 => p_aat_attribute8
849 ,p_aat_attribute9 => p_aat_attribute9
850 ,p_aat_attribute10 => p_aat_attribute10
851 ,p_aat_attribute11 => p_aat_attribute11
852 ,p_aat_attribute12 => p_aat_attribute12
853 ,p_aat_attribute13 => p_aat_attribute13
854 ,p_aat_attribute14 => p_aat_attribute14
855 ,p_aat_attribute15 => p_aat_attribute15
856 ,p_aat_attribute16 => p_aat_attribute16
857 ,p_aat_attribute17 => p_aat_attribute17
858 ,p_aat_attribute18 => p_aat_attribute18
859 ,p_aat_attribute19 => p_aat_attribute19
860 ,p_aat_attribute20 => p_aat_attribute20
861 ,p_aat_information_category => p_aat_information_category
862 ,p_aat_information1 => p_aat_information1
863 ,p_aat_information2 => p_aat_information2
864 ,p_aat_information3 => p_aat_information3
865 ,p_aat_information4 => p_aat_information4
866 ,p_aat_information5 => p_aat_information5
867 ,p_aat_information6 => p_aat_information6
868 ,p_aat_information7 => p_aat_information7
869 ,p_aat_information8 => p_aat_information8
870 ,p_aat_information9 => p_aat_information9
871 ,p_aat_information10 => p_aat_information10
872 ,p_aat_information11 => p_aat_information11
873 ,p_aat_information12 => p_aat_information12
874 ,p_aat_information13 => p_aat_information13
875 ,p_aat_information14 => p_aat_information14
876 ,p_aat_information15 => p_aat_information15
877 ,p_aat_information16 => p_aat_information16
878 ,p_aat_information17 => p_aat_information17
879 ,p_aat_information18 => p_aat_information18
880 ,p_aat_information19 => p_aat_information19
881 ,p_aat_information20 => p_aat_information20
882 ,p_lgps_process_flag => p_lgps_process_flag
883 ,p_lgps_exclusion_type => p_lgps_exclusion_type
884 ,p_lgps_pensionable_pay => p_lgps_pensionable_pay
885 ,p_lgps_trans_arrang_flag => p_lgps_trans_arrang_flag
886 ,p_lgps_membership_number => p_lgps_membership_number
887 ,p_assignment_attribute_id =>p_assignment_attribute_id
888 ,p_object_version_number => l_object_version_number
889 );
890 --
891
892
893
894 --
895 -- Call After Process User Hook
896 --
897 begin
898 pqp_aat_api_bk2.update_assignment_attribute_a
899 (p_effective_date => l_effective_date
900 ,p_datetrack_mode => p_datetrack_mode
901 ,p_business_group_id => p_business_group_id
902 ,p_effective_start_date => l_effective_start_date
903 ,p_effective_end_date => l_effective_end_date
904 ,p_assignment_id => p_assignment_id
905 ,p_contract_type => p_contract_type
906 ,p_work_pattern => p_work_pattern
907 ,p_start_day => l_start_day --p_start_day 10129497
908 ,p_primary_company_car => p_primary_company_car
909 ,p_primary_car_fuel_benefit => p_primary_car_fuel_benefit
910 ,p_primary_capital_contribution => p_primary_capital_contribution
911 ,p_primary_class_1a => p_primary_class_1a
912 ,p_secondary_company_car => p_secondary_company_car
913 ,p_secondary_car_fuel_benefit => p_secondary_car_fuel_benefit
914 ,p_secondary_capital_contributi => p_secondary_capital_contributi
915 ,p_secondary_class_1a => p_secondary_class_1a
916 ,p_company_car_calc_method => p_company_car_calc_method
917 ,p_company_car_rates_table_id => p_company_car_rates_table_id
918 ,p_company_car_secondary_table => p_company_car_secondary_table
919 ,p_private_car => p_private_car
920 ,p_private_car_calc_method => p_private_car_calc_method
921 ,p_private_car_rates_table_id => p_private_car_rates_table_id
922 ,p_private_car_essential_table => p_private_car_essential_table
923 ,p_primary_private_contribution => p_primary_private_contribution
924 ,p_secondary_private_contributi => p_secondary_private_contributi
925 ,p_tp_is_teacher => p_tp_is_teacher
926 --added for head Teacher seconded location for salary scale calculation
927 ,p_tp_headteacher_grp_code => p_tp_headteacher_grp_code
928 ,p_tp_safeguarded_grade => p_tp_safeguarded_grade
929 ,p_tp_safeguarded_grade_id => p_tp_safeguarded_grade_id
930 ,p_tp_safeguarded_rate_type => p_tp_safeguarded_rate_type
931 ,p_tp_safeguarded_rate_id => p_tp_safeguarded_rate_id
932 ,p_tp_spinal_point_id => p_tp_spinal_point_id
933 ,p_tp_elected_pension => p_tp_elected_pension
934 ,p_tp_fast_track => p_tp_fast_track
935 ,p_aat_attribute_category => p_aat_attribute_category
936 ,p_aat_attribute1 => p_aat_attribute1
937 ,p_aat_attribute2 => p_aat_attribute2
938 ,p_aat_attribute3 => p_aat_attribute3
939 ,p_aat_attribute4 => p_aat_attribute4
940 ,p_aat_attribute5 => p_aat_attribute5
941 ,p_aat_attribute6 => p_aat_attribute6
942 ,p_aat_attribute7 => p_aat_attribute7
943 ,p_aat_attribute8 => p_aat_attribute8
944 ,p_aat_attribute9 => p_aat_attribute9
945 ,p_aat_attribute10 => p_aat_attribute10
946 ,p_aat_attribute11 => p_aat_attribute11
947 ,p_aat_attribute12 => p_aat_attribute12
948 ,p_aat_attribute13 => p_aat_attribute13
949 ,p_aat_attribute14 => p_aat_attribute14
950 ,p_aat_attribute15 => p_aat_attribute15
951 ,p_aat_attribute16 => p_aat_attribute16
952 ,p_aat_attribute17 => p_aat_attribute17
953 ,p_aat_attribute18 => p_aat_attribute18
954 ,p_aat_attribute19 => p_aat_attribute19
955 ,p_aat_attribute20 => p_aat_attribute20
956 ,p_aat_information_category => p_aat_information_category
957 ,p_aat_information1 => p_aat_information1
958 ,p_aat_information2 => p_aat_information2
959 ,p_aat_information3 => p_aat_information3
960 ,p_aat_information4 => p_aat_information4
961 ,p_aat_information5 => p_aat_information5
962 ,p_aat_information6 => p_aat_information6
963 ,p_aat_information7 => p_aat_information7
964 ,p_aat_information8 => p_aat_information8
965 ,p_aat_information9 => p_aat_information9
966 ,p_aat_information10 => p_aat_information10
967 ,p_aat_information11 => p_aat_information11
968 ,p_aat_information12 => p_aat_information12
969 ,p_aat_information13 => p_aat_information13
970 ,p_aat_information14 => p_aat_information14
971 ,p_aat_information15 => p_aat_information15
972 ,p_aat_information16 => p_aat_information16
973 ,p_aat_information17 => p_aat_information17
974 ,p_aat_information18 => p_aat_information18
975 ,p_aat_information19 => p_aat_information19
976 ,p_aat_information20 => p_aat_information20
977 ,p_lgps_process_flag => p_lgps_process_flag
978 ,p_lgps_exclusion_type => p_lgps_exclusion_type
979 ,p_lgps_pensionable_pay => p_lgps_pensionable_pay
980 ,p_lgps_trans_arrang_flag => p_lgps_trans_arrang_flag
981 ,p_lgps_membership_number => p_lgps_membership_number
982 ,p_assignment_attribute_id =>p_assignment_attribute_id
983 ,p_object_version_number => l_object_version_number
984 );
985 exception
986 when hr_api.cannot_find_prog_unit then
987 hr_api.cannot_find_prog_unit_error
988 (p_module_name => 'update_assignment_attribute'
989 ,p_hook_type => 'AP'
990 );
991 end;
992 --
993 -- When in validation only mode raise the Validate_Enabled exception
994 --
995 if p_validate then
996 raise hr_api.validate_enabled;
997 end if;
998 --
999 -- Set all output arguments
1000 --
1001 p_object_version_number := l_object_version_number;
1002 p_effective_start_date := l_effective_start_date;
1003 p_effective_end_date := l_effective_end_date;
1004
1005 --
1006 hr_utility.set_location(' Leaving:'||l_proc, 70);
1007 exception
1008 when hr_api.validate_enabled then
1009 --
1010 -- As the Validate_Enabled exception has been raised
1011 -- we must rollback to the savepoint
1012 --
1013 rollback to update_assignment_attribute;
1014 --
1015 -- Only set output warning arguments
1016 -- (Any key or derived arguments must be set to null
1017 -- when validation only mode is being used.)
1018 --
1019 p_object_version_number := null;
1020 p_effective_start_date := null;
1021 p_effective_end_date := null;
1022
1023 hr_utility.set_location(' Leaving:'||l_proc, 80);
1024 when others then
1025 --
1026 -- A validation or unexpected error has occured
1027 --
1028 rollback to update_assignment_attribute;
1029 p_object_version_number := l_object_version_number;
1030 p_effective_start_date := null;
1031 p_effective_end_date := null;
1032 hr_utility.set_location(' Leaving:'||l_proc, 90);
1033 raise;
1034 end update_assignment_attribute;
1035 --
1036 --
1037 -- ---------------------------------------------------------------------------+
1038 -- |---------------------< <delete_assignment_attribute> >--------------------|
1039 -- ---------------------------------------------------------------------------+
1040 --
1041 procedure delete_assignment_attribute
1042 (p_validate in boolean default false
1043 ,p_effective_date in date
1044 ,p_datetrack_mode in varchar2
1045 ,p_business_group_id in number
1046 ,p_assignment_attribute_id in number
1047 ,p_effective_start_date out nocopy date
1048 ,p_effective_end_date out nocopy date
1049 ,p_object_version_number in out nocopy number
1050 ) is
1051 --
1052 -- Declare cursors and local variables
1053 --
1054
1055 l_proc varchar2(72) := g_package||'delete_assignment_attribute';
1056 l_effective_date date;
1057 l_effective_start_date date;
1058 l_effective_end_date date;
1059 l_object_version_number number;
1060
1061 begin
1062 hr_utility.set_location('Entering:'|| l_proc, 10);
1063 --
1064 -- Issue a savepoint
1065 --
1066 savepoint delete_assignment_attribute;
1067 --
1068 -- Truncate the time portion from all IN date parameters
1069 --
1070
1071 l_effective_date := trunc(p_effective_date);
1072 l_effective_start_date := trunc(p_effective_start_date);
1073 l_effective_end_date := trunc(p_effective_end_date);
1074
1075 --
1076 -- Call Before Process User Hook
1077 --
1078 begin
1079 pqp_aat_api_bk3.delete_assignment_attribute_b
1080 (p_effective_date => l_effective_date
1081 ,p_datetrack_mode => p_datetrack_mode
1082 ,p_assignment_attribute_id => p_assignment_attribute_id
1083 ,p_effective_start_date => l_effective_start_date
1084 ,p_effective_end_date => l_effective_end_date
1085 ,p_object_version_number => p_object_version_number
1086 );
1087 exception
1088 when hr_api.cannot_find_prog_unit then
1089 hr_api.cannot_find_prog_unit_error
1090 (p_module_name => 'delete_assignment_attribute'
1091 ,p_hook_type => 'BP'
1092 );
1093 end;
1094 --
1095 -- Validation in addition to Row Handlers
1096 --
1097
1098 l_object_version_number := p_object_version_number;
1099
1100
1101 --
1102 -- Process Logic
1103 pqp_aat_del.del
1104 (p_effective_date => l_effective_date
1105 ,p_assignment_attribute_id =>p_assignment_attribute_id
1106 ,p_datetrack_mode => p_datetrack_mode
1107 ,p_effective_start_date => l_effective_start_date
1108 ,p_effective_end_date => l_effective_end_date
1109 ,p_object_version_number => l_object_version_number
1110 );
1111 --
1112
1113
1114
1115 --
1116 -- Call After Process User Hook
1117 --
1118 begin
1119 pqp_aat_api_bk3.delete_assignment_attribute_a
1120 (p_effective_date => l_effective_date
1121 ,p_datetrack_mode => p_datetrack_mode
1122 ,p_assignment_attribute_id => p_assignment_attribute_id
1123 ,p_effective_start_date => l_effective_start_date
1124 ,p_effective_end_date => l_effective_end_date
1125 ,p_object_version_number => l_object_version_number
1126 );
1127 exception
1128 when hr_api.cannot_find_prog_unit then
1129 hr_api.cannot_find_prog_unit_error
1130 (p_module_name => 'delete_assignment_attribute'
1131 ,p_hook_type => 'AP'
1132 );
1133 end;
1134 --
1135 -- When in validation only mode raise the Validate_Enabled exception
1136 --
1137 if p_validate then
1138 raise hr_api.validate_enabled;
1139 end if;
1140 --
1141 -- Set all output arguments
1142 --
1143 p_object_version_number := l_object_version_number;
1144 p_effective_start_date := l_effective_start_date;
1145 p_effective_end_date := l_effective_end_date;
1146 --
1147 hr_utility.set_location(' Leaving:'||l_proc, 70);
1148 exception
1149 when hr_api.validate_enabled then
1150 --
1151 -- As the Validate_Enabled exception has been raised
1152 -- we must rollback to the savepoint
1153 --
1154 rollback to delete_assignment_attribute;
1155 --
1156 -- Only set output warning arguments
1157 -- (Any key or derived arguments must be set to null
1158 -- when validation only mode is being used.)
1159 p_object_version_number := null;
1160 p_effective_start_date := null;
1161 p_effective_end_date := null;
1162 --
1163 hr_utility.set_location(' Leaving:'||l_proc, 80);
1164 when others then
1165 --
1166 -- A validation or unexpected error has occured
1167 --
1168 rollback to delete_assignment_attribute;
1169 p_object_version_number := l_object_version_number;
1170 p_effective_start_date := null;
1171 p_effective_end_date := null;
1172 hr_utility.set_location(' Leaving:'||l_proc, 90);
1173 raise;
1174 end delete_assignment_attribute;
1175
1176 --
1177 end pqp_aat_api;