[Home] [Help]
PACKAGE BODY: APPS.PSP_EFF_REPORT_DETAILS_API
Source
1 PACKAGE BODY PSP_EFF_REPORT_DETAILS_API as
2 /* $Header: PSPEDAIB.pls 120.4 2006/01/30 22:33:34 dpaudel noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' PSP_EFF_REPORT_DETAILS_API.';
7 MAX_percent_validation_flag Exception;
8 Range_percent_validation_flag Exception;
9 --
10 -- ----------------------------------------------------------------------------
11 -- |--------------------------< UPDATE_EFF_REPORT_DETAILS >--------------------------|
12 -- ----------------------------------------------------------------------------
13 --
14 procedure UPDATE_EFF_REPORT_DETAILS
15 (p_validate in boolean default false
16 ,p_Request_id in number
17 ,p_start_person in number
18 ,p_end_person in number
19 ,p_warning out nocopy boolean
20 ) is
21 --
22 -- Declare cursors and local variables
23 --
24 l_proc varchar2(72) := g_package||'UPDATE_EFF_REPORT_DETAILS';
25 l_count_eff_detail_id Number := 0;
26 L_OBJECT_VERSION_NUMBER Number;
27 l_Request_id Number;
28 l_person_id Number;
29 l_sum_PROPOSED_EFFORT_PERCENT Number;
30 l_range_PROPOSED_EFFORT_PER Number;
31 l_full_name Varchar2(240);
32 l_message varchar2(240);
33 l_return_status varchar2(30);
34 l_start_date date; --- uva fix
35 l_end_date date; --- uva fix
36
37 l_approver_person_id psp_eff_report_details_api.assignment_id;
38 l_investigator_name psp_eff_report_details_api.full_name_type;
39 l_investigator_org_name psp_eff_report_details_api.full_name_type;
40 l_inv_primary_org_id psp_eff_report_details_api.assignment_id;
41
42
43 -- uva fix.
44 cursor get_er_dates is
45 select parameter_value_2, parameter_value_3
46 from psp_report_templates_h
47 where request_id = p_request_id;
48
49
50 /* Cursor to Find the EFFORT_REPORT_DETAIL_ID from REQUEST_ID */
51 cursor c_EFFORT_REPORT_DETAIL_ID is
52 select perd.effort_report_detail_id , perd.OBJECT_VERSION_NUMBER, Assignment_id,Project_id ,expenditure_organization_id,expenditure_type ,task_id,award_id,
53 GL_SEGMENT1,GL_SEGMENT2 ,GL_SEGMENT3 ,GL_SEGMENT4 ,GL_SEGMENT5 ,GL_SEGMENT6 ,GL_SEGMENT7 ,GL_SEGMENT8 ,GL_SEGMENT9 ,GL_SEGMENT10,
54 GL_SEGMENT11,GL_SEGMENT12,GL_SEGMENT13,GL_SEGMENT14,GL_SEGMENT15,GL_SEGMENT16,GL_SEGMENT17,GL_SEGMENT18,GL_SEGMENT19,GL_SEGMENT20,
55 GL_SEGMENT21,GL_SEGMENT22,GL_SEGMENT23,GL_SEGMENT24,GL_SEGMENT25,GL_SEGMENT26,GL_SEGMENT27,GL_SEGMENT28,GL_SEGMENT29,GL_SEGMENT30, per.full_name, investigator_person_id,
56 INVESTIGATOR_NAME ,
57 INVESTIGATOR_ORG_NAME ,
58 INVESTIGATOR_PRIMARY_ORG_ID
59 from psp_eff_report_details perd,
60 psp_eff_reports per
61 where REQUEST_ID = p_Request_id
62 and perd.EFFORT_REPORT_ID = per.EFFORT_REPORT_ID
63 and person_id between p_start_person and p_end_person;
64 /* Added Person Id check for Bug fix 4089645 */
65
66 /*
67 CURSOR c_MAX_PROPOSED_EFFORT_PERCENT IS
68 select per.PERSON_ID,max(Full_name), sum(perd.PROPOSED_EFFORT_PERCENT)
69 from psp_eff_report_details perd,
70 psp_eff_reports per
71 where perd.EFFORT_REPORT_ID = per.EFFORT_REPORT_ID
72 group by per.PERSON_ID
73 having sum(PROPOSED_EFFORT_PERCENT) > 100;
74 CURSOR c_RAnge_PROPOSED_EFFORT_PER IS
75 select per.PERSON_ID,max(Full_name), sum(perd.PROPOSED_EFFORT_PERCENT)
76 from psp_eff_report_details perd,
77 psp_eff_reports per
78 where perd.EFFORT_REPORT_ID = per.EFFORT_REPORT_ID
79 --AND (PROPOSED_EFFORT_PERCENT <0
80 --or PROPOSED_EFFORT_PERCENT >100)
81 AND PROPOSED_EFFORT_PERCENT >100
82 group by per.PERSON_ID;
83 */
84 begin
85 hr_utility.set_location('Entering:'|| l_proc, 10);
86
87 -- for uva
88 open get_er_dates;
89 fetch get_er_dates into l_start_date, l_end_date;
90 close get_er_dates;
91 --
92 -- Issue a savepoint
93 --
94 savepoint UPDATE_EFF_REPORT_DETAILS;
95 --
96 -- Remember IN OUT parameter IN values
97 --
98 --
99 -- Truncate the time portion from all IN date parameters
100 --
101 open c_EFFORT_REPORT_DETAIL_ID;
102 fetch c_EFFORT_REPORT_DETAIL_ID BULK COLLECT INTO g_er_EFFORT_REPORT_DETAIL_ID,g_er_OBJECT_VERSION_NUMBER, g_er_Assignment_id ,g_er_Project_id ,g_er_expenditure_org_id,g_er_expenditure_type ,g_er_task_id, g_er_award_id,
103 g_er_GL_SEGMENT1, G_ER_GL_SEGMENT2 , G_ER_GL_SEGMENT3 , G_ER_GL_SEGMENT4 , G_ER_GL_SEGMENT5 , G_ER_GL_SEGMENT6 , G_ER_GL_SEGMENT7 , G_ER_GL_SEGMENT8 , G_ER_GL_SEGMENT9 , G_ER_GL_SEGMENT10,
104 G_ER_GL_SEGMENT11, G_ER_GL_SEGMENT12, G_ER_GL_SEGMENT13, G_ER_GL_SEGMENT14, G_ER_GL_SEGMENT15, G_ER_GL_SEGMENT16, G_ER_GL_SEGMENT17, G_ER_GL_SEGMENT18, G_ER_GL_SEGMENT19, G_ER_GL_SEGMENT20,
105 G_ER_GL_SEGMENT21, G_ER_GL_SEGMENT22, G_ER_GL_SEGMENT23, G_ER_GL_SEGMENT24, G_ER_GL_SEGMENT25, G_ER_GL_SEGMENT26, G_ER_GL_SEGMENT27, G_ER_GL_SEGMENT28, G_ER_GL_SEGMENT29, G_ER_GL_SEGMENT30, g_er_full_name, g_er_approver_person_id,
106 g_er_investigator_name, g_er_investigator_org_name, g_er_inv_primary_org_id;
107 Close c_EFFORT_REPORT_DETAIL_ID;
108 l_count_eff_detail_id :=g_er_EFFORT_REPORT_DETAIL_ID.count;
109 /* for Bug fix 4081279 START*/
110 Begin
111 for i in 1..l_count_eff_detail_id Loop
112
113
114 g_er_proposed_salary_amt(i) := hr_api.g_number;
115 g_er_proposed_effort_percent(i) := hr_api.g_number;
116 g_er_committed_cost_share(i) := hr_api.g_number;
117 g_er_value1(i) := hr_api.g_number;
118 g_er_value2(i) := hr_api.g_number;
119 g_er_value3(i) := hr_api.g_number;
120 g_er_value4(i) := hr_api.g_number;
121 g_er_value5(i) := hr_api.g_number;
122 g_er_value6(i) := hr_api.g_number;
123 g_er_value7(i) := hr_api.g_number;
124 g_er_value8(i) := hr_api.g_number;
125 g_er_value9(i) := hr_api.g_number;
126 g_er_value10(i) := hr_api.g_number;
127 g_er_attribute1(i) := hr_api.g_varchar2;
128 g_er_attribute2(i) := hr_api.g_varchar2;
129 g_er_attribute3(i) := hr_api.g_varchar2;
130 g_er_attribute4(i) := hr_api.g_varchar2;
131 g_er_attribute5(i) := hr_api.g_varchar2;
132 g_er_attribute6(i) := hr_api.g_varchar2;
133 g_er_attribute7(i) := hr_api.g_varchar2;
134 g_er_attribute8(i) := hr_api.g_varchar2;
135 g_er_attribute9(i) := hr_api.g_varchar2;
136 g_er_attribute10(i) := hr_api.g_varchar2;
137 g_er_grouping_category(i) := hr_api.g_varchar2; -- Add for Hospital Effort Report
138
139 l_approver_person_id(i) := g_er_approver_person_id(i);
140 l_investigator_name(i) := g_er_investigator_name(i);
141 l_inv_primary_org_id(i) := g_er_inv_primary_org_id(i);
142 l_investigator_org_name(i) := g_er_investigator_org_name(i);
143
144 End loop;
145 END;
146 /* fro Bug fix 4081279 END*/
147
148 begin
149 for i in 1..l_count_eff_detail_id Loop
150 PSP_EFF_REPORT_DETAILS_BK1.UPDATE_EFF_REPORT_DETAILS_b
151 (p_effort_report_detail_id => g_er_effort_report_detail_id (i)
152 ,p_Assignment_id => g_er_Assignment_id(i)
153 ,p_GL_SEGMENT1 => g_er_GL_SEGMENT1(i)
154 ,p_GL_SEGMENT2 => g_er_GL_SEGMENT2(i)
155 ,p_GL_SEGMENT3 => g_er_GL_SEGMENT3(i)
156 ,p_GL_SEGMENT4 => g_er_GL_SEGMENT4(i)
157 ,p_GL_SEGMENT5 => g_er_GL_SEGMENT5(i)
158 ,p_GL_SEGMENT6 => g_er_GL_SEGMENT6(i)
159 ,p_GL_SEGMENT7 => g_er_GL_SEGMENT7(i)
160 ,p_GL_SEGMENT8 => g_er_GL_SEGMENT8(i)
161 ,p_GL_SEGMENT9 => g_er_GL_SEGMENT9(i)
162 ,p_GL_SEGMENT10 => g_er_GL_SEGMENT10(i)
163 ,p_GL_SEGMENT11 => g_er_GL_SEGMENT11(i)
164 ,p_GL_SEGMENT12 => g_er_GL_SEGMENT12(i)
165 ,p_GL_SEGMENT13 => g_er_GL_SEGMENT13(i)
166 ,p_GL_SEGMENT14 => g_er_GL_SEGMENT14(i)
167 ,p_GL_SEGMENT15 => g_er_GL_SEGMENT15(i)
168 ,p_GL_SEGMENT16 => g_er_GL_SEGMENT16(i)
169 ,p_GL_SEGMENT17 => g_er_GL_SEGMENT17(i)
170 ,p_GL_SEGMENT18 => g_er_GL_SEGMENT18(i)
171 ,p_GL_SEGMENT19 => g_er_GL_SEGMENT19(i)
172 ,p_GL_SEGMENT20 => g_er_GL_SEGMENT20(i)
173 ,p_GL_SEGMENT21 => g_er_GL_SEGMENT21(i)
174 ,p_GL_SEGMENT22 => g_er_GL_SEGMENT22(i)
175 ,p_GL_SEGMENT23 => g_er_GL_SEGMENT23(i)
176 ,p_GL_SEGMENT24 => g_er_GL_SEGMENT24(i)
177 ,p_GL_SEGMENT25 => g_er_GL_SEGMENT25(i)
178 ,p_GL_SEGMENT26 => g_er_GL_SEGMENT26(i)
179 ,p_GL_SEGMENT27 => g_er_GL_SEGMENT27(i)
180 ,p_GL_SEGMENT28 => g_er_GL_SEGMENT28(i)
181 ,p_GL_SEGMENT29 => g_er_GL_SEGMENT29(i)
182 ,p_GL_SEGMENT30 => g_er_GL_SEGMENT30(i)
183 ,p_Project_id => g_er_Project_id(i)
184 ,p_expenditure_org_id => g_er_expenditure_org_id(i)
185 ,p_expenditure_type => g_er_expenditure_type(i)
186 ,p_task_id => g_er_task_id(i)
187 ,p_award_id => g_er_Award_id(i)
188 ,p_count_eff_detail_id => i
189 ,p_effort_start_date => l_start_date -- added 5 params for UVA
190 ,p_effort_end_date => l_end_date
191 ,p_investigator_person_id => g_er_approver_person_id(i)
192 ,p_INVESTIGATOR_NAME => g_er_investigator_name(i)
193 ,p_INVESTIGATOR_ORG_NAME => g_er_investigator_org_name(i)
194 ,p_INVESTIGATOR_PRIMARY_ORG_ID => g_er_inv_primary_org_id(i)
195 );
196
197 End Loop;
198 Exception
199 when others then
200 --
201 -- A validation or unexpected error has occured
202 --
203 rollback to UPDATE_EFF_REPORT_DETAILS;
204 --
205 -- Reset IN OUT parameters and set all
206 -- OUT parameters, including warnings, to null
207 --
208 -- p_Request_id := null;
209 p_warning := true;
210 l_message := sqlerrm;
211 PSP_GENERAL.add_report_error(
212 p_request_id => p_Request_id
213 ,p_message_level => 'E'
214 ,p_source_id => NULL
215 ,p_error_message => l_message
216 ,p_return_status => l_return_status
217 );
218
219 hr_utility.set_location(' Leaving:'||l_proc, 90);
220 raise FND_API.G_EXC_UNEXPECTED_ERROR;
221 end;
222 --
223 -- Call Before Process User Hook
224 --
225 --
226 -- Validation in addition to Row Handlers
227 --
228 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
229 -- Process Logic - Call the row-handler upd procedure
230 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
231 for i in 1..l_count_eff_detail_id Loop
232 BEGIN
233 if (g_er_approver_person_id(i) <> l_approver_person_id(i) ) THEN
237 l_investigator_name(i) := g_er_investigator_name(i);
234 l_approver_person_id(i) := g_er_approver_person_id(i);
235 END IF;
236 if (g_er_investigator_name(i) <> l_investigator_name(i) ) THEN
238 END IF;
239 if (g_er_inv_primary_org_id(i) <> l_inv_primary_org_id(i) ) THEN
240 l_inv_primary_org_id(i) := g_er_inv_primary_org_id(i);
241 END IF;
242 if (g_er_investigator_org_name(i) <> l_investigator_org_name(i) ) THEN
243 l_investigator_org_name(i) := g_er_investigator_org_name(i);
244 END IF;
245
246
247 /* Added check to provent unnecessary update */
248 if g_er_proposed_salary_amt(i) <> hr_api.g_number OR g_er_proposed_effort_percent(i) <> hr_api.g_number
249 OR g_er_committed_cost_share(i) <> hr_api.g_number OR g_er_value1(i) <> hr_api.g_number
250 OR g_er_value2(i) <> hr_api.g_number OR g_er_value3(i) <> hr_api.g_number OR g_er_value4(i) <> hr_api.g_number
251 OR g_er_value5(i) <> hr_api.g_number OR g_er_value6(i) <> hr_api.g_number OR g_er_value7(i) <> hr_api.g_number
252 OR g_er_value8(i) <> hr_api.g_number OR g_er_value9(i) <> hr_api.g_number OR g_er_value10(i) <> hr_api.g_number
253 OR g_er_attribute1(i) <> hr_api.g_varchar2 OR g_er_attribute2(i) <> hr_api.g_varchar2
254 OR g_er_attribute3(i) <> hr_api.g_varchar2 OR g_er_attribute4(i) <> hr_api.g_varchar2
255 OR g_er_attribute5(i) <> hr_api.g_varchar2 OR g_er_attribute6(i) <> hr_api.g_varchar2
256 OR g_er_attribute7(i) <> hr_api.g_varchar2 OR g_er_attribute8(i) <> hr_api.g_varchar2
257 OR g_er_attribute9(i) <> hr_api.g_varchar2 OR g_er_attribute10(i) <> hr_api.g_varchar2
258 OR g_er_approver_person_id(i) <> l_approver_person_id(i)
259 OR g_er_investigator_name(i) <> l_investigator_name(i)
260 OR g_er_investigator_org_name(i) <> l_investigator_org_name(i)
261 OR g_er_inv_primary_org_id(i) <> l_inv_primary_org_id(i)
262 OR g_er_grouping_category(i) <> hr_api.g_varchar2
263 THEN
264 psp_erd_upd.upd (
265 p_effort_report_detail_id => g_er_EFFORT_REPORT_DETAIL_ID(i)
266 ,p_object_version_number => g_er_OBJECT_VERSION_NUMBER(i)
267 ,p_proposed_salary_amt => g_er_proposed_salary_amt(i)
268 ,p_proposed_effort_percent => g_er_proposed_effort_percent(i)
269 ,p_committed_cost_share => g_er_committed_cost_share(i)
270 ,p_value1 => g_er_value1(i)
271 ,p_value2 => g_er_value2(i)
272 ,p_value3 => g_er_value3(i)
273 ,p_value4 => g_er_value4(i)
274 ,p_value5 => g_er_value5(i)
275 ,p_value6 => g_er_value6(i)
276 ,p_value7 => g_er_value7(i)
277 ,p_value8 => g_er_value8(i)
278 ,p_value9 => g_er_value9(i)
279 ,p_value10 => g_er_value10(i)
280 ,p_attribute1 => g_er_attribute1(i)
281 ,p_attribute2 => g_er_attribute2(i)
282 ,p_attribute3 => g_er_attribute3(i)
283 ,p_attribute4 => g_er_attribute4(i)
284 ,p_attribute5 => g_er_attribute5(i)
285 ,p_attribute6 => g_er_attribute6(i)
286 ,p_attribute7 => g_er_attribute7(i)
287 ,p_attribute8 => g_er_attribute8(i)
288 ,p_attribute9 => g_er_attribute9(i)
289 ,p_attribute10 => g_er_attribute10(i)
290 ,p_investigator_person_id => l_approver_person_id(i)
291 ,p_INVESTIGATOR_NAME => l_investigator_name(i)
292 ,p_INVESTIGATOR_ORG_NAME => l_investigator_org_name(i)
293 ,p_INVESTIGATOR_PRIMARY_ORG_ID => l_inv_primary_org_id(i)
294 ,p_grouping_category => g_er_grouping_category(i) -- Add for Hospital Effort Report
295 );
296 End if;
297 Exception
298 when others then
299 --
300 -- A validation or unexpected error has occured
301 --
302 rollback to UPDATE_EFF_REPORT_DETAILS;
303 --
304 -- Reset IN OUT parameters and set all
305 -- OUT parameters, including warnings, to null
306 --
307 -- p_Request_id := null;
308 p_warning := true;
309 l_message := sqlerrm;
310 PSP_GENERAL.add_report_error(
311 p_request_id => p_Request_id
312 ,p_message_level => 'E'
313 ,p_source_id => g_er_full_name(i)
314 ,p_error_message => l_message
315 ,p_return_status => l_return_status
316 );
317
318 hr_utility.set_location(' Leaving:'||l_proc, 90);
319 raise FND_API.G_EXC_UNEXPECTED_ERROR;
320 END;
321 End Loop;
322
323
324 /*
325 OPEN c_RAnge_PROPOSED_EFFORT_PER;
326 fetch c_RAnge_PROPOSED_EFFORT_PER into l_person_id,l_full_name, l_Range_PROPOSED_EFFORT_PER;
327 close c_RAnge_PROPOSED_EFFORT_PER;
328 if l_person_id is not null then
329 raise Range_percent_validation_flag;
330 end if;
331 OPEN c_MAX_PROPOSED_EFFORT_PERCENT;
332 fetch c_MAX_PROPOSED_EFFORT_PERCENT into l_person_id, l_full_name, l_sum_PROPOSED_EFFORT_PERCENT;
333 close c_MAX_PROPOSED_EFFORT_PERCENT;
334 if l_sum_PROPOSED_EFFORT_PERCENT is not null then
335 raise MAX_percent_validation_flag;
336 end if;
337 */
338 --
339 -- Call After Process User Hook
340 --
341 begin
342 for i in 1..l_count_eff_detail_id Loop
343 PSP_EFF_REPORT_DETAILS_BK1.UPDATE_EFF_REPORT_DETAILS_a
344 (p_effort_report_detail_id => g_er_effort_report_detail_id (i)
345 ,p_Assignment_id => g_er_Assignment_id(i)
349 ,p_GL_SEGMENT4 => g_er_GL_SEGMENT4(i)
346 ,p_GL_SEGMENT1 => g_er_GL_SEGMENT1(i)
347 ,p_GL_SEGMENT2 => g_er_GL_SEGMENT2(i)
348 ,p_GL_SEGMENT3 => g_er_GL_SEGMENT3(i)
350 ,p_GL_SEGMENT5 => g_er_GL_SEGMENT5(i)
351 ,p_GL_SEGMENT6 => g_er_GL_SEGMENT6(i)
352 ,p_GL_SEGMENT7 => g_er_GL_SEGMENT7(i)
353 ,p_GL_SEGMENT8 => g_er_GL_SEGMENT8(i)
354 ,p_GL_SEGMENT9 => g_er_GL_SEGMENT9(i)
355 ,p_GL_SEGMENT10 => g_er_GL_SEGMENT10(i)
356 ,p_GL_SEGMENT11 => g_er_GL_SEGMENT11(i)
357 ,p_GL_SEGMENT12 => g_er_GL_SEGMENT12(i)
358 ,p_GL_SEGMENT13 => g_er_GL_SEGMENT13(i)
359 ,p_GL_SEGMENT14 => g_er_GL_SEGMENT14(i)
360 ,p_GL_SEGMENT15 => g_er_GL_SEGMENT15(i)
361 ,p_GL_SEGMENT16 => g_er_GL_SEGMENT16(i)
362 ,p_GL_SEGMENT17 => g_er_GL_SEGMENT17(i)
363 ,p_GL_SEGMENT18 => g_er_GL_SEGMENT18(i)
364 ,p_GL_SEGMENT19 => g_er_GL_SEGMENT19(i)
365 ,p_GL_SEGMENT20 => g_er_GL_SEGMENT20(i)
366 ,p_GL_SEGMENT21 => g_er_GL_SEGMENT21(i)
367 ,p_GL_SEGMENT22 => g_er_GL_SEGMENT22(i)
368 ,p_GL_SEGMENT23 => g_er_GL_SEGMENT23(i)
369 ,p_GL_SEGMENT24 => g_er_GL_SEGMENT24(i)
370 ,p_GL_SEGMENT25 => g_er_GL_SEGMENT25(i)
371 ,p_GL_SEGMENT26 => g_er_GL_SEGMENT26(i)
372 ,p_GL_SEGMENT27 => g_er_GL_SEGMENT27(i)
373 ,p_GL_SEGMENT28 => g_er_GL_SEGMENT28(i)
374 ,p_GL_SEGMENT29 => g_er_GL_SEGMENT29(i)
375 ,p_GL_SEGMENT30 => g_er_GL_SEGMENT30(i)
376 ,p_Project_id => g_er_Project_id(i)
377 ,p_expenditure_org_id => g_er_expenditure_org_id(i)
378 ,p_expenditure_type => g_er_expenditure_type(i)
379 ,p_task_id => g_er_task_id(i)
380 ,p_award_id => g_er_Award_id(i)
381 ,p_count_eff_detail_id => i
382 ,p_effort_start_date => l_start_date -- added 3 params for UVA
383 ,p_effort_end_date => l_end_date
384 ,p_investigator_person_id => g_er_approver_person_id(i)
385 ,p_INVESTIGATOR_NAME => g_er_investigator_name(i)
386 ,p_INVESTIGATOR_ORG_NAME => g_er_investigator_org_name(i)
387 ,p_INVESTIGATOR_PRIMARY_ORG_ID => g_er_inv_primary_org_id(i)
388 );
389 End Loop;
390 exception
391 when hr_api.cannot_find_prog_unit then
392 hr_api.cannot_find_prog_unit_error
393 (p_module_name => 'UPDATE_EFF_REPORT_DETAILS'
394 ,p_hook_type => 'AP'
395 );
396 l_message := sqlerrm;
397 PSP_GENERAL.add_report_error(
398 p_request_id => p_Request_id
399 ,p_message_level => 'E'
400 ,p_source_id => NULL
401 ,p_error_message => l_message
402 ,p_return_status => l_return_status
403 );
404
405 hr_utility.set_location(' Leaving:'||l_proc, 90);
406 raise FND_API.G_EXC_UNEXPECTED_ERROR;
407
408 when others then
409 --
410 -- A validation or unexpected error has occured
411 --
412 rollback to UPDATE_EFF_REPORT_DETAILS;
413 --
414 -- Reset IN OUT parameters and set all
415 -- OUT parameters, including warnings, to null
416 --
417 -- p_Request_id := null;
418 p_warning := true;
419 l_message := sqlerrm;
420 PSP_GENERAL.add_report_error(
421 p_request_id => p_Request_id
422 ,p_message_level => 'E'
423 ,p_source_id => NULL
424 ,p_error_message => l_message
425 ,p_return_status => l_return_status
426 );
427
428 hr_utility.set_location(' Leaving:'||l_proc, 90);
429 raise FND_API.G_EXC_UNEXPECTED_ERROR;
430 end;
431 --
432 -- When in validation only mode raise the Validate_Enabled exception
433 --
434 if p_validate then
435 raise hr_api.validate_enabled;
436 end if;
437 --
438 -- Set all IN OUT and OUT parameters with out values
439 --
440 -- p_Request_id := l_Request_id;
441 --
442 hr_utility.set_location(' Leaving:'||l_proc, 70);
443 exception
444 /*
445 when Range_percent_validation_flag then
446 p_warning := true;
447 OPEN c_RAnge_PROPOSED_EFFORT_PER;
448 Loop
449 fetch c_RAnge_PROPOSED_EFFORT_PER into l_person_id, l_full_name, l_range_PROPOSED_EFFORT_PER;
450 exit when c_RAnge_PROPOSED_EFFORT_PER%NOTFOUND;
451 -- fnd_msg_pub.add_exc_msg('PSP_ERD_EXT','UPDATE_EFF_REPORT_DETAILS_EXT');
452 fnd_message.set_name('PSP', 'PSP_RANGE_PERCENT_VALIDAION');
453 fnd_message.SET_TOKEN('EMPLOYEENAME',l_full_name);
454 l_message := fnd_message.get;
455 PSP_GENERAL.add_report_error(
456 p_request_id => p_Request_id
457 ,p_message_level => 'E'
458 ,p_source_id => l_person_id
459 ,p_error_message => l_message
460 ,p_return_status => l_return_status
461 );
462 if l_return_status = 'E' then
463 raise;
464 end if;
465 End Loop;
466 close c_RAnge_PROPOSED_EFFORT_PER;
467 when MAX_percent_validation_flag then
468 p_warning := true;
469 OPEN c_MAX_PROPOSED_EFFORT_PERCENT;
470 Loop
471 fetch c_MAX_PROPOSED_EFFORT_PERCENT into l_person_id, l_full_name, l_sum_PROPOSED_EFFORT_PERCENT;
472 exit when c_MAX_PROPOSED_EFFORT_PERCENT%NOTFOUND;
473 fnd_message.set_name('PSP', 'PSP_MAX_PERCENT_VALIDAION');
474 fnd_message.SET_TOKEN('EMPLOYEENAME',l_full_name);
475 l_message := fnd_message.get;
476 PSP_GENERAL.add_report_error(
477 p_request_id => p_Request_id
478 ,p_message_level => 'E'
479 ,p_source_id => l_person_id
480 ,p_error_message => l_message
481 ,p_return_status => l_return_status
482 );
483 if l_return_status = 'E' then
484 raise;
485 end if;
486 End Loop;
487 close c_MAX_PROPOSED_EFFORT_PERCENT;
488 */
489 when hr_api.validate_enabled then
490 --
491 -- As the Validate_Enabled exception has been raised
492 -- we must rollback to the savepoint
493 --
494 rollback to UPDATE_EFF_REPORT_DETAILS;
495 --
496 -- Reset IN OUT parameters and set OUT parameters
497 -- (Any key or derived arguments must be set to null
498 -- when validation only mode is being used.)
499 --
500 -- p_Request_id := null;
501 p_warning := true;
502 l_message := sqlerrm;
503 PSP_GENERAL.add_report_error(
504 p_request_id => p_Request_id
505 ,p_message_level => 'E'
506 ,p_source_id => NULL
507 ,p_error_message => l_message
508 ,p_return_status => l_return_status
509 );
510 if l_return_status = 'E' then
511 raise FND_API.G_EXC_UNEXPECTED_ERROR;
512 end if;
513 hr_utility.set_location(' Leaving:'||l_proc, 80);
514 when others then
515 --
516 -- A validation or unexpected error has occured
517 --
518 rollback to UPDATE_EFF_REPORT_DETAILS;
519 --
520 -- Reset IN OUT parameters and set all
521 -- OUT parameters, including warnings, to null
522 --
523 -- p_Request_id := null;
524 p_warning := true;
525 l_message := sqlerrm;
526 PSP_GENERAL.add_report_error(
527 p_request_id => p_Request_id
528 ,p_message_level => 'E'
529 ,p_source_id => NULL
530 ,p_error_message => l_message
531 ,p_return_status => l_return_status
532 );
533 if l_return_status = 'E' then
534 raise FND_API.G_EXC_UNEXPECTED_ERROR;
535 end if;
536 hr_utility.set_location(' Leaving:'||l_proc, 90);
537 raise FND_API.G_EXC_UNEXPECTED_ERROR;
538 end UPDATE_EFF_REPORT_DETAILS;
539 --
540 end PSP_EFF_REPORT_DETAILS_API;