[Home] [Help]
PACKAGE BODY: APPS.PSP_EFF_REPORT_APPROVALS_API
Source
1 Package Body psp_eff_report_approvals_api as
2 /* $Header: PSPEAAIB.pls 120.3 2006/03/26 01:09:40 dpaudel noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' psp_eff_report_approvals_api.';
7 p_legislation_code varchar(50):=hr_api.userenv_lang;
8
9 --
10 -- ----------------------------------------------------------------------------
11 -- |--------------------------< insert_eff_report_approvals >--------------------|
12 -- ----------------------------------------------------------------------------
13 --
14 procedure insert_eff_report_approvals
15 (p_validate in boolean default false
16 ,p_effort_report_detail_id in number
17 ,p_wf_role_name in varchar2
18 ,p_wf_orig_system_id in number
19 ,p_wf_orig_system in varchar2
20 ,p_approver_order_num in number
21 ,p_approval_status in varchar2
22 ,p_response_date in date
23 ,p_actual_cost_share in number
24 ,p_overwritten_effort_percent in number
25 ,p_wf_item_key in varchar2
26 ,p_comments in varchar2
27 ,p_pera_information_category in varchar2
28 ,p_pera_information1 in varchar2
29 ,p_pera_information2 in varchar2
30 ,p_pera_information3 in varchar2
31 ,p_pera_information4 in varchar2
32 ,p_pera_information5 in varchar2
33 ,p_pera_information6 in varchar2
34 ,p_pera_information7 in varchar2
35 ,p_pera_information8 in varchar2
36 ,p_pera_information9 in varchar2
37 ,p_pera_information10 in varchar2
38 ,p_pera_information11 in varchar2
39 ,p_pera_information12 in varchar2
40 ,p_pera_information13 in varchar2
41 ,p_pera_information14 in varchar2
42 ,p_pera_information15 in varchar2
43 ,p_pera_information16 in varchar2
44 ,p_pera_information17 in varchar2
45 ,p_pera_information18 in varchar2
46 ,p_pera_information19 in varchar2
47 ,p_pera_information20 in varchar2
48 ,p_wf_role_display_name in varchar2
49 ,p_eff_information_category in varchar2
50 ,p_eff_information1 in varchar2
51 ,p_eff_information2 in varchar2
52 ,p_eff_information3 in varchar2
53 ,p_eff_information4 in varchar2
54 ,p_eff_information5 in varchar2
55 ,p_eff_information6 in varchar2
56 ,p_eff_information7 in varchar2
57 ,p_eff_information8 in varchar2
58 ,p_eff_information9 in varchar2
59 ,p_eff_information10 in varchar2
60 ,p_eff_information11 in varchar2
61 ,p_eff_information12 in varchar2
62 ,p_eff_information13 in varchar2
63 ,p_eff_information14 in varchar2
64 ,p_eff_information15 in varchar2
65 ,p_effort_report_approval_id out nocopy number
66 ,p_object_version_number out nocopy number
67 ,p_return_status out nocopy boolean
68 )
69 IS
70 --
71 -- Declare cursors and local variables
72 --
73 l_proc varchar2(72) := g_package||'insert_eff_report_approvals';
74 l_object_version_number number(9);
75 l_response_date date;
76 l_effort_report_approval_id number;
77 l_return_status boolean;
78 begin
79 hr_utility.set_location('Entering:'|| l_proc, 10);
80 --
81 -- Issue a savepoint
82 --
83 savepoint insert_eff_report_approvals;
84 --
85 -- Remember IN OUT parameter IN values
86 --
87 l_object_version_number := p_object_version_number;
88
89 --
90 -- Truncate the time portion from all IN date parameters
91 --
92 l_response_date := trunc(p_response_date);
93 --
94 -- Call Before Process User Hook
95 --
96 begin
97 psp_eff_report_approvals_bk1.insert_eff_report_approvals_b
98 (p_effort_report_detail_id => p_effort_report_detail_id
99 ,p_wf_role_name => p_wf_role_name
100 ,p_wf_orig_system_id => p_wf_orig_system_id
101 ,p_wf_orig_system => p_wf_orig_system
102 ,p_approver_order_num => p_approver_order_num
103 ,p_approval_status => p_approval_status
104 ,p_response_date => l_response_date
105 ,p_actual_cost_share => p_actual_cost_share
106 ,p_overwritten_effort_percent => p_overwritten_effort_percent
107 ,p_wf_item_key => p_wf_item_key
108 ,p_comments => p_comments
109 ,p_pera_information_category => p_pera_information_category
110 ,p_pera_information1 => p_pera_information1
111 ,p_pera_information2 => p_pera_information2
112 ,p_pera_information3 => p_pera_information3
113 ,p_pera_information4 => p_pera_information4
114 ,p_pera_information5 => p_pera_information5
115 ,p_pera_information6 => p_pera_information6
116 ,p_pera_information7 => p_pera_information7
117 ,p_pera_information8 => p_pera_information8
118 ,p_pera_information9 => p_pera_information9
119 ,p_pera_information10 => p_pera_information10
120 ,p_pera_information11 => p_pera_information11
121 ,p_pera_information12 => p_pera_information12
122 ,p_pera_information13 => p_pera_information13
123 ,p_pera_information14 => p_pera_information14
124 ,p_pera_information15 => p_pera_information15
125 ,p_pera_information16 => p_pera_information16
126 ,p_pera_information17 => p_pera_information17
127 ,p_pera_information18 => p_pera_information18
128 ,p_pera_information19 => p_pera_information19
129 ,p_pera_information20 => p_pera_information20
130 ,p_wf_role_display_name => p_wf_role_display_name
131 ,p_eff_information_category => p_eff_information_category
132 ,p_eff_information1 => p_eff_information1
133 ,p_eff_information2 => p_eff_information2
134 ,p_eff_information3 => p_eff_information3
135 ,p_eff_information4 => p_eff_information4
136 ,p_eff_information5 => p_eff_information5
137 ,p_eff_information6 => p_eff_information6
138 ,p_eff_information7 => p_eff_information7
139 ,p_eff_information8 => p_eff_information8
140 ,p_eff_information9 => p_eff_information9
141 ,p_eff_information10 => p_eff_information10
142 ,p_eff_information11 => p_eff_information11
143 ,p_eff_information12 => p_eff_information12
144 ,p_eff_information13 => p_eff_information13
145 ,p_eff_information14 => p_eff_information14
146 ,p_eff_information15 => p_eff_information15
147 );
148 exception
149 when hr_api.cannot_find_prog_unit then
150 hr_api.cannot_find_prog_unit_error
151 (p_module_name => 'insert_eff_report_approvals'
152 ,p_hook_type => 'BP'
153 );
154 end;
155 --
156 -- Validation in addition to Row Handlers
157 --
158
159
160
161 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
162 -- Process Logic - Call the row-handler ins procedure
163 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
164
165 psp_era_ins.ins
166 (p_effort_report_detail_id => p_effort_report_detail_id
167 ,p_wf_role_name => p_wf_role_name
168 ,p_wf_orig_system_id => p_wf_orig_system_id
169 ,p_wf_orig_system => p_wf_orig_system
170 ,p_approver_order_num => p_approver_order_num
171 ,p_approval_status => p_approval_status
172 ,p_response_date => l_response_date
173 ,p_actual_cost_share => p_actual_cost_share
174 ,p_overwritten_effort_percent => p_overwritten_effort_percent
175 ,p_wf_item_key => p_wf_item_key
176 ,p_comments => p_comments
177 ,p_pera_information_category => p_pera_information_category
178 ,p_pera_information1 => p_pera_information1
179 ,p_pera_information2 => p_pera_information2
180 ,p_pera_information3 => p_pera_information3
181 ,p_pera_information4 => p_pera_information4
182 ,p_pera_information5 => p_pera_information5
183 ,p_pera_information6 => p_pera_information6
184 ,p_pera_information7 => p_pera_information7
185 ,p_pera_information8 => p_pera_information8
186 ,p_pera_information9 => p_pera_information9
187 ,p_pera_information10 => p_pera_information10
188 ,p_pera_information11 => p_pera_information11
189 ,p_pera_information12 => p_pera_information12
190 ,p_pera_information13 => p_pera_information13
191 ,p_pera_information14 => p_pera_information14
192 ,p_pera_information15 => p_pera_information15
193 ,p_pera_information16 => p_pera_information16
194 ,p_pera_information17 => p_pera_information17
195 ,p_pera_information18 => p_pera_information18
196 ,p_pera_information19 => p_pera_information19
197 ,p_pera_information20 => p_pera_information20
198 ,p_wf_role_display_name => p_wf_role_display_name
199 ,p_effort_report_approval_id => l_effort_report_approval_id
200 ,p_object_version_number => l_object_version_number
201 ,p_eff_information_category => p_eff_information_category
202 ,p_eff_information1 => p_eff_information1
203 ,p_eff_information2 => p_eff_information2
204 ,p_eff_information3 => p_eff_information3
205 ,p_eff_information4 => p_eff_information4
206 ,p_eff_information5 => p_eff_information5
207 ,p_eff_information6 => p_eff_information6
208 ,p_eff_information7 => p_eff_information7
209 ,p_eff_information8 => p_eff_information8
210 ,p_eff_information9 => p_eff_information9
211 ,p_eff_information10 => p_eff_information10
212 ,p_eff_information11 => p_eff_information11
213 ,p_eff_information12 => p_eff_information12
214 ,p_eff_information13 => p_eff_information13
215 ,p_eff_information14 => p_eff_information14
216 ,p_eff_information15 => p_eff_information15
217 );
218
219 --
220 -- Call After Process User Hook
221 --
222 begin
223 psp_eff_report_approvals_bk1.insert_eff_report_approvals_a
224 (p_effort_report_approval_id => l_effort_report_approval_id
225 ,p_effort_report_detail_id => p_effort_report_detail_id
226 ,p_wf_role_name => p_wf_role_name
227 ,p_wf_orig_system_id => p_wf_orig_system_id
228 ,p_wf_orig_system => p_wf_orig_system
229 ,p_approver_order_num => p_approver_order_num
230 ,p_approval_status => p_approval_status
231 ,p_response_date => l_response_date
232 ,p_actual_cost_share => p_actual_cost_share
233 ,p_overwritten_effort_percent => p_overwritten_effort_percent
234 ,p_wf_item_key => p_wf_item_key
235 ,p_comments => p_comments
236 ,p_pera_information_category => p_pera_information_category
237 ,p_pera_information1 => p_pera_information1
238 ,p_pera_information2 => p_pera_information2
239 ,p_pera_information3 => p_pera_information3
240 ,p_pera_information4 => p_pera_information4
241 ,p_pera_information5 => p_pera_information5
242 ,p_pera_information6 => p_pera_information6
243 ,p_pera_information7 => p_pera_information7
244 ,p_pera_information8 => p_pera_information8
245 ,p_pera_information9 => p_pera_information9
246 ,p_pera_information10 => p_pera_information10
247 ,p_pera_information11 => p_pera_information11
248 ,p_pera_information12 => p_pera_information12
249 ,p_pera_information13 => p_pera_information13
250 ,p_pera_information14 => p_pera_information14
251 ,p_pera_information15 => p_pera_information15
252 ,p_pera_information16 => p_pera_information16
253 ,p_pera_information17 => p_pera_information17
254 ,p_pera_information18 => p_pera_information18
255 ,p_pera_information19 => p_pera_information19
256 ,p_pera_information20 => p_pera_information20
257 ,p_wf_role_display_name => p_wf_role_display_name
258 ,p_object_version_number => l_object_version_number
259 ,p_eff_information_category => p_eff_information_category
260 ,p_eff_information1 => p_eff_information1
261 ,p_eff_information2 => p_eff_information2
262 ,p_eff_information3 => p_eff_information3
263 ,p_eff_information4 => p_eff_information4
264 ,p_eff_information5 => p_eff_information5
265 ,p_eff_information6 => p_eff_information6
266 ,p_eff_information7 => p_eff_information7
267 ,p_eff_information8 => p_eff_information8
268 ,p_eff_information9 => p_eff_information9
269 ,p_eff_information10 => p_eff_information10
270 ,p_eff_information11 => p_eff_information11
271 ,p_eff_information12 => p_eff_information12
272 ,p_eff_information13 => p_eff_information13
273 ,p_eff_information14 => p_eff_information14
274 ,p_eff_information15 => p_eff_information15
275 ,p_return_status => l_return_status
276 );
277 exception
278 when hr_api.cannot_find_prog_unit then
279 hr_api.cannot_find_prog_unit_error
280 (p_module_name => 'insert_eff_report_approvals'
281 ,p_hook_type => 'AP'
282 );
283 end;
284 --
285 -- When in validation only mode raise the Validate_Enabled exception
286 --
287 if p_validate then
288 raise hr_api.validate_enabled;
289 end if;
290 --
291 -- Set all IN OUT and OUT parameters with out values
292 --
293 p_object_version_number := l_object_version_number;
294 p_effort_report_approval_id := l_effort_report_approval_id;
295 p_return_status := l_return_status;
296
297 hr_utility.set_location(' Leaving:'||l_proc, 70);
298 exception
299 when hr_api.validate_enabled then
300 --
301 -- As the Validate_Enabled exception has been raised
302 -- we must rollback to the savepoint
303 --
304 rollback to insert_eff_report_approvals;
305 --
306 -- Reset IN OUT parameters and set OUT parameters
307 -- (Any key or derived arguments must be set to null
308 -- when validation only mode is being used.)
309 --
310 p_object_version_number := null;
311 p_effort_report_approval_id := null;
312 p_return_status := l_return_status;
313 hr_utility.set_location(' Leaving:'||l_proc, 80);
314 when others then
315 --
316 -- A validation or unexpected error has occured
317 --
318 rollback to insert_eff_report_approvals;
319 --
320 -- Reset IN OUT parameters and set all
321 -- OUT parameters, including warnings, to null
322 --
323 p_object_version_number := null;
324 p_effort_report_approval_id := null;
325 p_return_status := null;
329
326 hr_utility.set_location(' Leaving:'||l_proc, 90);
327 raise;
328 end insert_eff_report_approvals;
330
331
332
333
334
335
336
337 --
338 -- ----------------------------------------------------------------------------
339 -- |----------------------< update_eff_report_approvals >---------------------------|
340 -- ----------------------------------------------------------------------------
341 --
342 procedure update_eff_report_approvals
343 (p_validate in boolean default false
344 ,p_effort_report_approval_id in number
345 ,p_effort_report_detail_id in number default hr_api.g_number
346 ,p_wf_role_name in varchar2 default hr_api.g_varchar2
347 ,p_wf_orig_system_id in number default hr_api.g_number
348 ,p_wf_orig_system in varchar2 default hr_api.g_varchar2
349 ,p_approver_order_num in number default hr_api.g_number
350 ,p_approval_status in varchar2 default hr_api.g_varchar2
351 ,p_response_date in date default hr_api.g_date
352 ,p_actual_cost_share in number default hr_api.g_number
353 ,p_overwritten_effort_percent in number default hr_api.g_number
354 ,p_wf_item_key in varchar2 default hr_api.g_varchar2
355 ,p_comments in varchar2 default hr_api.g_varchar2
356 ,p_pera_information_category in varchar2 default hr_api.g_varchar2
357 ,p_pera_information1 in varchar2 default hr_api.g_varchar2
358 ,p_pera_information2 in varchar2 default hr_api.g_varchar2
359 ,p_pera_information3 in varchar2 default hr_api.g_varchar2
360 ,p_pera_information4 in varchar2 default hr_api.g_varchar2
361 ,p_pera_information5 in varchar2 default hr_api.g_varchar2
362 ,p_pera_information6 in varchar2 default hr_api.g_varchar2
363 ,p_pera_information7 in varchar2 default hr_api.g_varchar2
364 ,p_pera_information8 in varchar2 default hr_api.g_varchar2
365 ,p_pera_information9 in varchar2 default hr_api.g_varchar2
366 ,p_pera_information10 in varchar2 default hr_api.g_varchar2
367 ,p_pera_information11 in varchar2 default hr_api.g_varchar2
368 ,p_pera_information12 in varchar2 default hr_api.g_varchar2
369 ,p_pera_information13 in varchar2 default hr_api.g_varchar2
370 ,p_pera_information14 in varchar2 default hr_api.g_varchar2
371 ,p_pera_information15 in varchar2 default hr_api.g_varchar2
372 ,p_pera_information16 in varchar2 default hr_api.g_varchar2
373 ,p_pera_information17 in varchar2 default hr_api.g_varchar2
374 ,p_pera_information18 in varchar2 default hr_api.g_varchar2
375 ,p_pera_information19 in varchar2 default hr_api.g_varchar2
376 ,p_pera_information20 in varchar2 default hr_api.g_varchar2
377 ,p_wf_role_display_name in varchar2 default hr_api.g_varchar2
378 ,p_eff_information_category in varchar2 default hr_api.g_varchar2
379 ,p_eff_information1 in varchar2 default hr_api.g_varchar2
380 ,p_eff_information2 in varchar2 default hr_api.g_varchar2
381 ,p_eff_information3 in varchar2 default hr_api.g_varchar2
382 ,p_eff_information4 in varchar2 default hr_api.g_varchar2
383 ,p_eff_information5 in varchar2 default hr_api.g_varchar2
384 ,p_eff_information6 in varchar2 default hr_api.g_varchar2
385 ,p_eff_information7 in varchar2 default hr_api.g_varchar2
386 ,p_eff_information8 in varchar2 default hr_api.g_varchar2
387 ,p_eff_information9 in varchar2 default hr_api.g_varchar2
388 ,p_eff_information10 in varchar2 default hr_api.g_varchar2
389 ,p_eff_information11 in varchar2 default hr_api.g_varchar2
390 ,p_eff_information12 in varchar2 default hr_api.g_varchar2
391 ,p_eff_information13 in varchar2 default hr_api.g_varchar2
392 ,p_eff_information14 in varchar2 default hr_api.g_varchar2
393 ,p_eff_information15 in varchar2 default hr_api.g_varchar2
394 ,p_object_version_number in out nocopy number
395 ,p_return_status out nocopy boolean
396 ) IS
397 --
398 -- Declare cursors and local variables
399 --
400 l_proc varchar2(72) := g_package||'update_eff_report_approvals';
401 l_response_date date;
402 l_object_version_number number(9);
403 l_return_status boolean;
404
405 l_pera_information1 varchar2(150);
406 l_pera_information2 varchar2(150);
407 l_pera_information3 varchar2(150);
408 l_pera_information4 varchar2(150);
409 l_pera_information5 varchar2(150);
410 l_pera_information6 varchar2(150);
411 l_pera_information7 varchar2(150);
412 l_pera_information8 varchar2(150);
413 l_pera_information9 varchar2(150);
414 l_pera_information10 varchar2(150);
415 l_pera_information11 varchar2(150);
416 l_pera_information12 varchar2(150);
417 l_pera_information13 varchar2(150);
418 l_pera_information14 varchar2(150);
419 l_pera_information15 varchar2(150);
420 l_pera_information16 varchar2(150);
421 l_pera_information17 varchar2(150);
422 l_pera_information18 varchar2(150);
423 l_pera_information19 varchar2(150);
424 l_pera_information20 varchar2(150);
425
426 l_eff_information1 varchar2(150);
427 l_eff_information2 varchar2(150);
431 l_eff_information6 varchar2(150);
428 l_eff_information3 varchar2(150);
429 l_eff_information4 varchar2(150);
430 l_eff_information5 varchar2(150);
432 l_eff_information7 varchar2(150);
433 l_eff_information8 varchar2(150);
434 l_eff_information9 varchar2(150);
435 l_eff_information10 varchar2(150);
436 l_eff_information11 varchar2(150);
437 l_eff_information12 varchar2(150);
438 l_eff_information13 varchar2(150);
439 l_eff_information14 varchar2(150);
440 l_eff_information15 varchar2(150);
441
442 begin
443 hr_utility.set_location('Entering:'|| l_proc, 10);
444 --
445 -- Issue a savepoint
446 --
447 savepoint update_eff_report_approvals;
448 --
449 -- Remember IN OUT parameter IN values
450 --
451 l_object_version_number := p_object_version_number;
452
453 --
454 -- Truncate the time portion from all IN date parameters
455 --
456 l_response_date := trunc(p_response_date);
457
458 -- Remember all user hook dependent values
459 l_pera_information1 := p_pera_information1;
460 l_pera_information2 := p_pera_information2;
461 l_pera_information3 := p_pera_information3;
462 l_pera_information4 := p_pera_information4;
463 l_pera_information5 := p_pera_information5;
464 l_pera_information6 := p_pera_information6;
465 l_pera_information7 := p_pera_information7;
466 l_pera_information8 := p_pera_information8;
467 l_pera_information9 := p_pera_information9;
468 l_pera_information10 := p_pera_information10;
469 l_pera_information11 := p_pera_information11;
470 l_pera_information12 := p_pera_information12;
471 l_pera_information13 := p_pera_information13;
472 l_pera_information14 := p_pera_information14;
473 l_pera_information15 := p_pera_information15;
474 l_pera_information16 := p_pera_information16;
475 l_pera_information17 := p_pera_information17;
476 l_pera_information18 := p_pera_information18;
477 l_pera_information19 := p_pera_information19;
478 l_pera_information20 := p_pera_information20;
479
480 l_eff_information1 := p_eff_information1;
481 l_eff_information2 := p_eff_information2;
482 l_eff_information3 := p_eff_information3;
483 l_eff_information4 := p_eff_information4;
484 l_eff_information5 := p_eff_information5;
485 l_eff_information6 := p_eff_information6;
486 l_eff_information7 := p_eff_information7;
487 l_eff_information8 := p_eff_information8;
488 l_eff_information9 := p_eff_information9;
489 l_eff_information10 := p_eff_information10;
490 l_eff_information11 := p_eff_information11;
491 l_eff_information12 := p_eff_information12;
492 l_eff_information13 := p_eff_information13;
493 l_eff_information14 := p_eff_information14;
494 l_eff_information15 := p_eff_information15;
495
496 --
497 -- Clear Global variables
498 --
499 g_pera_information1 := hr_api.g_varchar2;
500 g_pera_information2 := hr_api.g_varchar2;
501 g_pera_information3 := hr_api.g_varchar2;
502 g_pera_information4 := hr_api.g_varchar2;
503 g_pera_information5 := hr_api.g_varchar2;
504 g_pera_information6 := hr_api.g_varchar2;
505 g_pera_information7 := hr_api.g_varchar2;
506 g_pera_information8 := hr_api.g_varchar2;
507 g_pera_information9 := hr_api.g_varchar2;
508 g_pera_information10 := hr_api.g_varchar2;
509 g_pera_information11 := hr_api.g_varchar2;
510 g_pera_information12 := hr_api.g_varchar2;
511 g_pera_information13 := hr_api.g_varchar2;
512 g_pera_information14 := hr_api.g_varchar2;
513 g_pera_information15 := hr_api.g_varchar2;
514 g_pera_information16 := hr_api.g_varchar2;
515 g_pera_information17 := hr_api.g_varchar2;
516 g_pera_information18 := hr_api.g_varchar2;
517 g_pera_information19 := hr_api.g_varchar2;
518 g_pera_information20 := hr_api.g_varchar2;
519
520 g_eff_information1 := hr_api.g_varchar2;
521 g_eff_information2 := hr_api.g_varchar2;
522 g_eff_information3 := hr_api.g_varchar2;
523 g_eff_information4 := hr_api.g_varchar2;
524 g_eff_information5 := hr_api.g_varchar2;
525 g_eff_information6 := hr_api.g_varchar2;
526 g_eff_information7 := hr_api.g_varchar2;
527 g_eff_information8 := hr_api.g_varchar2;
528 g_eff_information9 := hr_api.g_varchar2;
529 g_eff_information10 := hr_api.g_varchar2;
530 g_eff_information11 := hr_api.g_varchar2;
531 g_eff_information12 := hr_api.g_varchar2;
532 g_eff_information13 := hr_api.g_varchar2;
533 g_eff_information14 := hr_api.g_varchar2;
534 g_eff_information15 := hr_api.g_varchar2;
535
536 --
537 -- Call Before Process User Hook
538 --
539 begin
540 psp_eff_report_approvals_bk2.update_eff_report_approvals_b
541 (p_effort_report_approval_id => p_effort_report_approval_id
542 ,p_effort_report_detail_id => p_effort_report_detail_id
543 ,p_wf_role_name => p_wf_role_name
544 ,p_wf_orig_system_id => p_wf_orig_system_id
545 ,p_wf_orig_system => p_wf_orig_system
546 ,p_approver_order_num => p_approver_order_num
547 ,p_approval_status => p_approval_status
548 ,p_response_date => l_response_date
549 ,p_actual_cost_share => p_actual_cost_share
550 ,p_overwritten_effort_percent => p_overwritten_effort_percent
551 ,p_wf_item_key => p_wf_item_key
552 ,p_comments => p_comments
553 ,p_pera_information_category => p_pera_information_category
554 ,p_pera_information1 => l_pera_information1
555 ,p_pera_information2 => l_pera_information2
556 ,p_pera_information3 => l_pera_information3
557 ,p_pera_information4 => l_pera_information4
558 ,p_pera_information5 => l_pera_information5
562 ,p_pera_information9 => l_pera_information9
559 ,p_pera_information6 => l_pera_information6
560 ,p_pera_information7 => l_pera_information7
561 ,p_pera_information8 => l_pera_information8
563 ,p_pera_information10 => l_pera_information10
564 ,p_pera_information11 => l_pera_information11
565 ,p_pera_information12 => l_pera_information12
566 ,p_pera_information13 => l_pera_information13
567 ,p_pera_information14 => l_pera_information14
568 ,p_pera_information15 => l_pera_information15
569 ,p_pera_information16 => l_pera_information16
570 ,p_pera_information17 => l_pera_information17
571 ,p_pera_information18 => l_pera_information18
572 ,p_pera_information19 => l_pera_information19
573 ,p_pera_information20 => l_pera_information20
574 ,p_wf_role_display_name => p_wf_role_display_name
575 ,p_object_version_number => l_object_version_number
576 ,p_eff_information_category => p_eff_information_category
577 ,p_eff_information1 => l_eff_information1
578 ,p_eff_information2 => l_eff_information2
579 ,p_eff_information3 => l_eff_information3
580 ,p_eff_information4 => l_eff_information4
581 ,p_eff_information5 => l_eff_information5
582 ,p_eff_information6 => l_eff_information6
583 ,p_eff_information7 => l_eff_information7
584 ,p_eff_information8 => l_eff_information8
585 ,p_eff_information9 => l_eff_information9
586 ,p_eff_information10 => l_eff_information10
587 ,p_eff_information11 => l_eff_information11
588 ,p_eff_information12 => l_eff_information12
589 ,p_eff_information13 => l_eff_information13
590 ,p_eff_information14 => l_eff_information14
591 ,p_eff_information15 => l_eff_information15
592 );
593 exception
594 when hr_api.cannot_find_prog_unit then
595 hr_api.cannot_find_prog_unit_error
596 (p_module_name => 'update_eff_report_approvals'
597 ,p_hook_type => 'BP'
598 );
599 end;
600 --
601 -- Validation in addition to Row Handlers
602 --
603
604 -- If user has updated global pera_informations from before hook
605 IF (g_pera_information1 <> hr_api.g_varchar2) THEN
606 l_pera_information1 := g_pera_information1;
607 END IF;
608 IF (g_pera_information2 <> hr_api.g_varchar2) THEN
609 l_pera_information2 := g_pera_information2;
610 END IF;
611 IF (g_pera_information3 <> hr_api.g_varchar2) THEN
612 l_pera_information3 := g_pera_information3;
613 END IF;
614 IF (g_pera_information4 <> hr_api.g_varchar2) THEN
615 l_pera_information4 := g_pera_information4;
616 END IF;
617 IF (g_pera_information5 <> hr_api.g_varchar2) THEN
618 l_pera_information5 := g_pera_information5;
619 END IF;
620 IF (g_pera_information6 <> hr_api.g_varchar2) THEN
621 l_pera_information6 := g_pera_information6;
622 END IF;
623 IF (g_pera_information7 <> hr_api.g_varchar2) THEN
624 l_pera_information7 := g_pera_information7;
625 END IF;
626 IF (g_pera_information8 <> hr_api.g_varchar2) THEN
627 l_pera_information8 := g_pera_information8;
628 END IF;
629 IF (g_pera_information9 <> hr_api.g_varchar2) THEN
630 l_pera_information9 := g_pera_information9;
631 END IF;
632 IF (g_pera_information10 <> hr_api.g_varchar2) THEN
633 l_pera_information10 := g_pera_information10;
634 END IF;
635 IF (g_pera_information11 <> hr_api.g_varchar2) THEN
636 l_pera_information11 := g_pera_information11;
637 END IF;
638 IF (g_pera_information12 <> hr_api.g_varchar2) THEN
639 l_pera_information12 := g_pera_information12;
640 END IF;
641 IF (g_pera_information13 <> hr_api.g_varchar2) THEN
642 l_pera_information13 := g_pera_information13;
643 END IF;
644 IF (g_pera_information14 <> hr_api.g_varchar2) THEN
645 l_pera_information14 := g_pera_information14;
646 END IF;
647 IF (g_pera_information15 <> hr_api.g_varchar2) THEN
648 l_pera_information15 := g_pera_information15;
649 END IF;
650 IF (g_pera_information16 <> hr_api.g_varchar2) THEN
651 l_pera_information16 := g_pera_information16;
652 END IF;
653 IF (g_pera_information17 <> hr_api.g_varchar2) THEN
654 l_pera_information17 := g_pera_information17;
655 END IF;
656 IF (g_pera_information18 <> hr_api.g_varchar2) THEN
657 l_pera_information18 := g_pera_information18;
658 END IF;
659 IF (g_pera_information19 <> hr_api.g_varchar2) THEN
660 l_pera_information19 := g_pera_information19;
661 END IF;
662 IF (g_pera_information20 <> hr_api.g_varchar2) THEN
663 l_pera_information20 := g_pera_information20;
664 END IF;
665
666
667 IF (g_eff_information1 <> hr_api.g_varchar2) THEN
668 l_eff_information1 := g_eff_information1;
669 END IF;
670 IF (g_eff_information2 <> hr_api.g_varchar2) THEN
671 l_eff_information2 := g_eff_information2;
672 END IF;
673 IF (g_eff_information3 <> hr_api.g_varchar2) THEN
674 l_eff_information3 := g_eff_information3;
675 END IF;
676 IF (g_eff_information4 <> hr_api.g_varchar2) THEN
677 l_eff_information4 := g_eff_information4;
678 END IF;
679 IF (g_eff_information5 <> hr_api.g_varchar2) THEN
680 l_eff_information5 := g_eff_information5;
681 END IF;
682 IF (g_eff_information6 <> hr_api.g_varchar2) THEN
683 l_eff_information6 := g_eff_information6;
684 END IF;
685 IF (g_eff_information7 <> hr_api.g_varchar2) THEN
686 l_eff_information7 := g_eff_information7;
690 END IF;
687 END IF;
688 IF (g_eff_information8 <> hr_api.g_varchar2) THEN
689 l_eff_information8 := g_eff_information8;
691 IF (g_eff_information9 <> hr_api.g_varchar2) THEN
692 l_eff_information9 := g_eff_information9;
693 END IF;
694 IF (g_eff_information10 <> hr_api.g_varchar2) THEN
695 l_eff_information10 := g_eff_information10;
696 END IF;
697 IF (g_eff_information11 <> hr_api.g_varchar2) THEN
698 l_eff_information11 := g_eff_information11;
699 END IF;
700 IF (g_eff_information12 <> hr_api.g_varchar2) THEN
701 l_eff_information12 := g_eff_information12;
702 END IF;
703 IF (g_eff_information13 <> hr_api.g_varchar2) THEN
704 l_eff_information13 := g_eff_information13;
705 END IF;
706 IF (g_eff_information14 <> hr_api.g_varchar2) THEN
707 l_eff_information14 := g_eff_information14;
708 END IF;
709 IF (g_eff_information15 <> hr_api.g_varchar2) THEN
710 l_eff_information15 := g_eff_information15;
711 END IF;
712 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
713 -- Process Logic - Call the row-handler upd procedure
714 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
715 psp_era_upd.upd
716 (p_effort_report_approval_id => p_effort_report_approval_id
717 ,p_object_version_number => l_object_version_number
718 ,p_effort_report_detail_id => p_effort_report_detail_id
719 ,p_wf_role_name => p_wf_role_name
720 ,p_wf_orig_system_id => p_wf_orig_system_id
721 ,p_wf_orig_system => p_wf_orig_system
722 ,p_approver_order_num => p_approver_order_num
723 ,p_approval_status => p_approval_status
724 ,p_response_date => l_response_date
725 ,p_actual_cost_share => p_actual_cost_share
726 ,p_overwritten_effort_percent => p_overwritten_effort_percent
727 ,p_wf_item_key => p_wf_item_key
728 ,p_comments => p_comments
729 ,p_pera_information_category => p_pera_information_category
730 ,p_pera_information1 => l_pera_information1
731 ,p_pera_information2 => l_pera_information2
732 ,p_pera_information3 => l_pera_information3
733 ,p_pera_information4 => l_pera_information4
734 ,p_pera_information5 => l_pera_information5
735 ,p_pera_information6 => l_pera_information6
736 ,p_pera_information7 => l_pera_information7
737 ,p_pera_information8 => l_pera_information8
738 ,p_pera_information9 => l_pera_information9
739 ,p_pera_information10 => l_pera_information10
740 ,p_pera_information11 => l_pera_information11
741 ,p_pera_information12 => l_pera_information12
742 ,p_pera_information13 => l_pera_information13
743 ,p_pera_information14 => l_pera_information14
744 ,p_pera_information15 => l_pera_information15
745 ,p_pera_information16 => l_pera_information16
746 ,p_pera_information17 => l_pera_information17
747 ,p_pera_information18 => l_pera_information18
748 ,p_pera_information19 => l_pera_information19
749 ,p_pera_information20 => l_pera_information20
750 ,p_wf_role_display_name => p_wf_role_display_name
751 ,p_eff_information_category => p_eff_information_category
752 ,p_eff_information1 => l_eff_information1
753 ,p_eff_information2 => l_eff_information2
754 ,p_eff_information3 => l_eff_information3
755 ,p_eff_information4 => l_eff_information4
756 ,p_eff_information5 => l_eff_information5
757 ,p_eff_information6 => l_eff_information6
758 ,p_eff_information7 => l_eff_information7
759 ,p_eff_information8 => l_eff_information8
760 ,p_eff_information9 => l_eff_information9
761 ,p_eff_information10 => l_eff_information10
762 ,p_eff_information11 => l_eff_information11
763 ,p_eff_information12 => l_eff_information12
764 ,p_eff_information13 => l_eff_information13
765 ,p_eff_information14 => l_eff_information14
766 ,p_eff_information15 => l_eff_information15
767 );
768
769
770
771 --
772 -- Call After Process User Hook
773 --
774 begin
775 psp_eff_report_approvals_bk2.update_eff_report_approvals_a
776 (p_effort_report_approval_id => p_effort_report_approval_id
777 ,p_effort_report_detail_id => p_effort_report_detail_id
778 ,p_wf_role_name => p_wf_role_name
779 ,p_wf_orig_system_id => p_wf_orig_system_id
780 ,p_wf_orig_system => p_wf_orig_system
781 ,p_approver_order_num => p_approver_order_num
782 ,p_approval_status => p_approval_status
783 ,p_response_date => l_response_date
784 ,p_actual_cost_share => p_actual_cost_share
785 ,p_overwritten_effort_percent => p_overwritten_effort_percent
786 ,p_wf_item_key => p_wf_item_key
787 ,p_comments => p_comments
788 ,p_pera_information_category => p_pera_information_category
789 ,p_pera_information1 => l_pera_information1
790 ,p_pera_information2 => l_pera_information2
791 ,p_pera_information3 => l_pera_information3
792 ,p_pera_information4 => l_pera_information4
793 ,p_pera_information5 => l_pera_information5
794 ,p_pera_information6 => l_pera_information6
795 ,p_pera_information7 => l_pera_information7
796 ,p_pera_information8 => l_pera_information8
800 ,p_pera_information12 => l_pera_information12
797 ,p_pera_information9 => l_pera_information9
798 ,p_pera_information10 => l_pera_information10
799 ,p_pera_information11 => l_pera_information11
801 ,p_pera_information13 => l_pera_information13
802 ,p_pera_information14 => l_pera_information14
803 ,p_pera_information15 => l_pera_information15
804 ,p_pera_information16 => l_pera_information16
805 ,p_pera_information17 => l_pera_information17
806 ,p_pera_information18 => l_pera_information18
807 ,p_pera_information19 => l_pera_information19
808 ,p_pera_information20 => l_pera_information20
809 ,p_wf_role_display_name => p_wf_role_display_name
810 ,p_object_version_number => l_object_version_number
811 ,p_eff_information_category => p_eff_information_category
812 ,p_eff_information1 => l_eff_information1
813 ,p_eff_information2 => l_eff_information2
814 ,p_eff_information3 => l_eff_information3
815 ,p_eff_information4 => l_eff_information4
816 ,p_eff_information5 => l_eff_information5
817 ,p_eff_information6 => l_eff_information6
818 ,p_eff_information7 => l_eff_information7
819 ,p_eff_information8 => l_eff_information8
820 ,p_eff_information9 => l_eff_information9
821 ,p_eff_information10 => l_eff_information10
822 ,p_eff_information11 => l_eff_information11
823 ,p_eff_information12 => l_eff_information12
824 ,p_eff_information13 => l_eff_information13
825 ,p_eff_information14 => l_eff_information14
826 ,p_eff_information15 => l_eff_information15
827 ,p_return_status => l_return_status
828 );
829 exception
830 when hr_api.cannot_find_prog_unit then
831 hr_api.cannot_find_prog_unit_error
832 (p_module_name => 'update_eff_report_approvals'
833 ,p_hook_type => 'AP'
834 );
835 end;
836 --
837 -- When in validation only mode raise the Validate_Enabled exception
838 --
839 if p_validate then
840 raise hr_api.validate_enabled;
841 end if;
842 --
843 -- Set all IN OUT and OUT parameters with out values
844 --
845 p_object_version_number := l_object_version_number;
846 p_return_status := l_return_status;
847 hr_utility.set_location(' Leaving:'||l_proc, 70);
848 exception
849 when hr_api.validate_enabled then
850 --
851 -- As the Validate_Enabled exception has been raised
852 -- we must rollback to the savepoint
853 --
854 rollback to update_eff_report_approvals;
855 --
856 -- Reset IN OUT parameters and set OUT parameters
857 -- (Any key or derived arguments must be set to null
858 -- when validation only mode is being used.)
859 --
860 p_object_version_number := null;
861 p_return_status := l_return_status;
862 hr_utility.set_location(' Leaving:'||l_proc, 80);
863 when others then
864 --
865 -- A validation or unexpected error has occured
866 --
867 rollback to update_eff_report_approvals;
868 --
869 -- Reset IN OUT parameters and set all
870 -- OUT parameters, including warnings, to null
871 --
872 p_object_version_number := null;
873 p_return_status := null;
874 hr_utility.set_location(' Leaving:'||l_proc, 90);
875 raise;
876 end update_eff_report_approvals;
877
878
879
880
881 --
882 -- ----------------------------------------------------------------------------
883 -- |-----------------------< delete_eff_report_approvals >-------------------------|
884 -- ----------------------------------------------------------------------------
885 --
886 procedure delete_eff_report_approvals
887 ( p_validate in boolean default false
888 , p_effort_report_approval_id in number
889 , p_object_version_number in out nocopy number
890 , p_return_status out nocopy boolean
891 )
892 IS
893 --
894 -- Declare cursors and local variables
895 --
896 l_proc varchar2(72) := g_package||'delete_eff_report_approvals_line';
897 l_object_version_number number(9);
898 l_return_status boolean;
899 begin
900 hr_utility.set_location('Entering:'|| l_proc, 10);
901 --
902 -- Issue a savepoint
903 --
904 savepoint delete_eff_report_approvals;
905 --
906 -- Remember IN OUT parameter IN values
907 --
908 l_object_version_number := p_object_version_number;
909
910 --
911 -- Call Before Process User Hook
912 --
913 begin
914 psp_eff_report_approvals_bk3.delete_eff_report_approvals_b
915 ( p_effort_report_approval_id => p_effort_report_approval_id
916 , p_object_version_number => l_object_version_number
917 );
918 exception
919 when hr_api.cannot_find_prog_unit then
920 hr_api.cannot_find_prog_unit_error
921 (p_module_name => 'delete_salary_cap'
922 ,p_hook_type => 'BP'
923 );
924 end;
925 --
926 -- Validation in addition to Row Handlers
927 --
928
929
930
931 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
932 -- Process Logic - Call the row-handler del procedure
933 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
934
935
936 psp_era_del.del
937 ( p_effort_report_approval_id => p_effort_report_approval_id
938 , p_object_version_number => l_object_version_number
939 );
940
941
942 --
943 -- Call After Process User Hook
944 --
945 begin
946 psp_eff_report_approvals_bk3.delete_eff_report_approvals_a
947 ( p_effort_report_approval_id => p_effort_report_approval_id
948 , p_object_version_number => l_object_version_number
949 , p_return_status => l_return_status
950 );
951 exception
952 when hr_api.cannot_find_prog_unit then
953 hr_api.cannot_find_prog_unit_error
954 (p_module_name => 'delete_salary_cap'
955 ,p_hook_type => 'AP'
956 );
957 end;
958 --
959 -- When in validation only mode raise the Validate_Enabled exception
960 --
961 if p_validate then
962 raise hr_api.validate_enabled;
963 end if;
964 --
965 -- Set all IN OUT and OUT parameters with out values
966 --
967 p_object_version_number := l_object_version_number;
968 p_return_status := l_return_status;
969 hr_utility.set_location(' Leaving:'||l_proc, 70);
970 exception
971 when hr_api.validate_enabled then
972 --
973 -- As the Validate_Enabled exception has been raised
974 -- we must rollback to the savepoint
975 --
976 rollback to delete_eff_report_approvals;
977 --
978 -- Reset IN OUT parameters and set OUT parameters
979 -- (Any key or derived arguments must be set to null
980 -- when validation only mode is being used.)
981 --
982 p_object_version_number := null;
983 p_return_status := l_return_status;
984 hr_utility.set_location(' Leaving:'||l_proc, 80);
985 when others then
986 --
987 -- A validation or unexpected error has occured
988 --
989 rollback to delete_eff_report_approvals;
990 --
991 -- Reset IN OUT parameters and set all
992 -- OUT parameters, including warnings, to null
993 --
994 p_object_version_number := null;
995 p_return_status := null;
996 hr_utility.set_location(' Leaving:'||l_proc, 90);
997 raise;
998 end delete_eff_report_approvals;
999
1000
1001 end psp_eff_report_approvals_api;