DBA Data[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;