[Home] [Help]
PACKAGE BODY: APPS.IGW_BUDGET_PERSONNEL_OH_PVT
Source
1 PACKAGE BODY IGW_BUDGET_PERSONNEL_OH_PVT AS
2 --$Header: igwvbpob.pls 115.8 2002/11/14 18:41:45 vmedikon ship $
3
4
5
6 ----------------------------------------------------------------------------------
7 /*
8 procedure create_budget_line_oh
9 (p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
10 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
11 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
12 ,p_proposal_id NUMBER
13 ,p_version_id NUMBER
14 ,p_budget_period_id NUMBER
15 ,p_line_item_id NUMBER
16 ,p_rate_class_id NUMBER
17 ,p_rate_type_id NUMBER
18 ,p_apply_rate_flag VARCHAR2
19 ,p_calculated_cost NUMBER := 0
20 ,p_calculated_cost_sharing NUMBER := 0
21 ,x_rowid OUT NOCOPY ROWID
22 ,x_return_status OUT NOCOPY VARCHAR2
23 ,x_msg_count OUT NOCOPY NUMBER
24 ,x_msg_data OUT NOCOPY VARCHAR2) IS
25
26 l_api_name VARCHAR2(30) :='CREATE_BUDGET_LINE_OH';
27 l_return_status VARCHAR2(1);
28 l_msg_count NUMBER;
29 l_data VARCHAR2(250);
30 l_msg_index_out NUMBER;
31
32 BEGIN
33 IF p_commit = FND_API.G_TRUE THEN
34 SAVEPOINT create_budget_line_oh;
35 END IF;
36
37 if FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
38 fnd_msg_pub.initialize;
39 end if;
40
41 x_return_status := 'S';
42
43 if (NOT FND_API.TO_BOOLEAN (p_validate_only)) then
44 igw_generate_periods.create_budget_detail_amts(
45 p_proposal_id => p_proposal_id
46 ,p_version_id => p_version_id
47 ,p_budget_period_id => p_budget_period_id
48 ,p_line_item_id => p_line_item_id
49 ,p_rate_class_id => p_rate_class_id
50 ,p_rate_type_id => p_rate_type_id
51 ,p_apply_rate_flag => p_apply_rate_flag
52 ,p_calculated_cost => p_calculated_cost
53 ,p_calculated_cost_sharing => p_calculated_cost_sharing);
54
55 end if; -- p_validate_only = 'Y'
56
57 l_msg_count := FND_MSG_PUB.count_msg;
58 If l_msg_count > 0 THEN
59 x_msg_count := l_msg_count;
60 If l_msg_count = 1 THEN
61 fnd_msg_pub.get
62 (p_encoded => FND_API.G_TRUE ,
63 p_msg_index => 1,
64 p_data => l_data,
65 p_msg_index_out => l_msg_index_out );
66
67 x_msg_data := l_data;
68 End if;
69 RAISE FND_API.G_EXC_ERROR;
70 End if;
71
72 x_return_status := FND_API.G_RET_STS_SUCCESS;
73
74 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
75 IF p_commit = FND_API.G_TRUE THEN
76 ROLLBACK TO create_budget_line_oh;
77 END IF;
78 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
79 fnd_msg_pub.add_exc_msg(p_pkg_name => G_package_name,
80 p_procedure_name => l_api_name,
81 p_error_text => SUBSTRB(SQLERRM,1,240));
82 fnd_msg_pub.count_and_get(p_count => x_msg_count
83 ,p_data => x_msg_data);
84 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
85
86 WHEN FND_API.G_EXC_ERROR THEN
87 IF p_commit = FND_API.G_TRUE THEN
88 ROLLBACK TO create_budget_line_oh;
89 END IF;
90 x_return_status := 'E';
91
92 WHEN OTHERS THEN
93 IF p_commit = FND_API.G_TRUE THEN
94 ROLLBACK TO create_budget_line_oh;
95 END IF;
96 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
97 fnd_msg_pub.add_exc_msg(p_pkg_name => G_package_name,
98 p_procedure_name => l_api_name,
99 p_error_text => SUBSTRB(SQLERRM,1,240));
100 fnd_msg_pub.count_and_get(p_count => x_msg_count
101 ,p_data => x_msg_data);
102 RAISE;
103
104
105 END; --CREATE BUDGET
106
107 */
108 ------------------------------------------------------------------------------------------
109 procedure update_budget_personnel_oh
110 (p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
111 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
112 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
113 ,p_budget_personnel_detail_id NUMBER
114 ,p_rate_class_id NUMBER
115 ,p_rate_type_id NUMBER
116 ,p_apply_rate_flag_oh VARCHAR2
117 ,p_apply_rate_flag_eb VARCHAR2
118 ,p_calculated_cost NUMBER
119 ,p_calculated_cost_sharing NUMBER
120 ,p_record_version_number IN NUMBER
121 ,p_rowid IN ROWID
122 ,x_return_status OUT NOCOPY VARCHAR2
123 ,x_msg_count OUT NOCOPY NUMBER
124 ,x_msg_data OUT NOCOPY VARCHAR2) IS
125
126 l_api_name VARCHAR2(30) :='UPDATE_BUDGET_PERSONNEL_OH';
127 l_proposal_id NUMBER;
128 l_version_id NUMBER;
129 l_budget_period_id NUMBER;
130 l_line_item_id NUMBER;
131 l_calculated_cost NUMBER := p_calculated_cost;
132 l_calculated_cost_sharing NUMBER := p_calculated_cost_sharing;
133 l_return_status VARCHAR2(1);
134 l_msg_count NUMBER;
135 l_data VARCHAR2(250);
136 l_msg_index_out NUMBER;
137 l_dummy VARCHAR2(1);
138
139 BEGIN
140 IF p_commit = FND_API.G_TRUE THEN
141 SAVEPOINT update_budget_personnel_oh;
142 END IF;
143
144 if FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
145 fnd_msg_pub.initialize;
146 end if;
147
148 x_return_status := 'S';
149
150
151 BEGIN
152 SELECT 'x' INTO l_dummy
153 FROM igw_budget_personnel_cal_amts
154 WHERE ((budget_personnel_detail_id = p_budget_personnel_detail_id
155 and rate_class_id = p_rate_class_id --bug 2515925
156 and rate_type_id = p_rate_type_id) --bug 2515925
157 OR rowid = p_rowid)
158 AND record_version_number = p_record_version_number;
159 EXCEPTION
160 WHEN NO_DATA_FOUND THEN
161 FND_MESSAGE.SET_NAME('IGW','IGW_SS_RECORD_CHANGED');
162 FND_MSG_PUB.Add;
163 x_msg_data := 'IGW_SS_RECORD_CHANGED';
164 x_return_status := 'E' ;
165 END;
166
167 l_msg_count := FND_MSG_PUB.count_msg;
168
169 IF l_msg_count > 0 THEN
170 x_msg_count := l_msg_count;
171 x_return_status := 'E';
172 If l_msg_count = 1 THEN
173 fnd_msg_pub.get
174 (p_encoded => FND_API.G_TRUE ,
175 p_msg_index => 1,
176 p_data => l_data,
177 p_msg_index_out => l_msg_index_out );
178
179 x_msg_data := l_data;
180 End if;
181 RAISE FND_API.G_EXC_ERROR;
182 END IF;
183
184 /* need to this because following ids may not be passed and only row id may be passed */
185
186 select proposal_id, version_id, budget_period_id, line_item_id
187 into l_proposal_id, l_version_id, l_budget_period_id, l_line_item_id
188 from igw_budget_personnel_details
189 where budget_personnel_detail_id = p_budget_personnel_detail_id;
190
191
192 if (p_apply_rate_flag_oh = 'N' or p_apply_rate_flag_eb = 'N') then
193 l_calculated_cost := 0;
194 l_calculated_cost_sharing := 0;
195 end if;
196
197 if (NOT FND_API.TO_BOOLEAN (p_validate_only)) then
198
199 update igw_budget_personnel_cal_amts
200 set apply_rate_flag = nvl(p_apply_rate_flag_oh, apply_rate_flag)
201 , calculated_cost = l_calculated_cost
202 , calculated_cost_sharing = l_calculated_cost_sharing
203 , record_version_number = record_version_number + 1
204 where budget_personnel_detail_id = p_budget_personnel_detail_id
205 and rate_class_id IN (select rate_class_id from igw_rate_classes where rate_class_type = 'O');
206
207 update igw_budget_personnel_cal_amts
208 set apply_rate_flag = nvl(p_apply_rate_flag_eb, apply_rate_flag)
209 , calculated_cost = l_calculated_cost
210 , calculated_cost_sharing = l_calculated_cost_sharing
211 , record_version_number = record_version_number + 1
212 where budget_personnel_detail_id = p_budget_personnel_detail_id
213 and rate_class_id IN (select rate_class_id from igw_rate_classes where rate_class_type = 'E');
214
215
216 IGW_BUDGET_OPERATIONS.recalculate_budget (
217 p_proposal_id => l_proposal_id
218 ,p_version_id => l_version_id
219 ,p_budget_period_id => l_budget_period_id
220 ,p_line_item_id => l_line_item_id
221 ,p_budget_personnel_detail_id => p_budget_personnel_detail_id
222 ,x_return_status => x_return_status
223 ,x_msg_data => x_msg_data
224 ,x_msg_count => x_msg_count);
225
226 end if; -- p_validate_only = 'Y'
227
228
229 l_msg_count := FND_MSG_PUB.count_msg;
230 If l_msg_count > 0 THEN
231 x_msg_count := l_msg_count;
232 If l_msg_count = 1 THEN
233 fnd_msg_pub.get
234 (p_encoded => FND_API.G_TRUE ,
235 p_msg_index => 1,
236 p_data => l_data,
237 p_msg_index_out => l_msg_index_out );
238
239 x_msg_data := l_data;
240 End if;
241 RAISE FND_API.G_EXC_ERROR;
242 End if;
243
244 x_return_status := FND_API.G_RET_STS_SUCCESS;
245
246 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
247 IF p_commit = FND_API.G_TRUE THEN
248 ROLLBACK TO update_budget_personnel_oh;
249 END IF;
250 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
251 fnd_msg_pub.add_exc_msg(p_pkg_name => G_package_name,
252 p_procedure_name => l_api_name,
253 p_error_text => SUBSTRB(SQLERRM,1,240));
254 fnd_msg_pub.count_and_get(p_count => x_msg_count
255 ,p_data => x_msg_data);
256 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
257
258 WHEN FND_API.G_EXC_ERROR THEN
259 IF p_commit = FND_API.G_TRUE THEN
260 ROLLBACK TO update_budget_personnel_oh;
261 END IF;
262 x_return_status := 'E';
263
264 WHEN OTHERS THEN
265 IF p_commit = FND_API.G_TRUE THEN
266 ROLLBACK TO update_budget_line_oh;
267 END IF;
268 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
269 fnd_msg_pub.add_exc_msg(p_pkg_name => G_package_name,
270 p_procedure_name => l_api_name,
271 p_error_text => SUBSTRB(SQLERRM,1,240));
272 fnd_msg_pub.count_and_get(p_count => x_msg_count
273 ,p_data => x_msg_data);
274 RAISE;
275
276 END; --UPDATE BUDGET PERSONNEL OH
277
278 -------------------------------------------------------------------------------------------
279 /*
280
281 procedure delete_budget_line_oh
282 (p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
283 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
284 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
285 ,p_proposal_id IN NUMBER := NULL
286 ,p_version_id IN NUMBER := NULL
287 ,p_budget_period_id NUMBER := NULL
288 ,p_line_item_id NUMBER
289 ,p_rate_class_id NUMBER
290 ,p_rate_type_id NUMBER
291 ,p_record_version_number IN NUMBER
292 ,p_rowid IN ROWID
293 ,x_return_status OUT NOCOPY VARCHAR2
294 ,x_msg_count OUT NOCOPY NUMBER
295 ,x_msg_data OUT NOCOPY VARCHAR2) is
296
297 l_api_name VARCHAR2(30) :='DELETE_BUDGET_LINE_OH';
298 l_return_status VARCHAR2(1);
299 l_msg_count NUMBER;
300 l_data VARCHAR2(250);
301 l_msg_index_out NUMBER;
302 l_dummy VARCHAR2(1);
303
304
305
306 BEGIN
307 IF p_commit = FND_API.G_TRUE THEN
308 SAVEPOINT delete_budget_line_oh;
309 END IF;
310
311 if FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
312 fnd_msg_pub.initialize;
313 end if;
314
315 x_return_status := 'S';
316
317 BEGIN
318 SELECT 'x' INTO l_dummy
319 FROM igw_budget_details_cal_amts
320 WHERE ((line_item_id = p_line_item_id and rate_class_id = p_rate_class_id and rate_type_id = p_rate_type_id)
321 OR rowid = p_rowid)
322 AND record_version_number = p_record_version_number;
323 EXCEPTION
324 WHEN NO_DATA_FOUND THEN
325 FND_MESSAGE.SET_NAME('IGW','IGW_SS_RECORD_CHANGED');
326 FND_MSG_PUB.Add;
327 x_msg_data := 'IGW_SS_RECORD_CHANGED';
328 x_return_status := 'E' ;
329 END;
330
331 l_msg_count := FND_MSG_PUB.count_msg;
332
333 IF l_msg_count > 0 THEN
334 x_msg_count := l_msg_count;
335 x_return_status := 'E';
336 If l_msg_count = 1 THEN
337 fnd_msg_pub.get
338 (p_encoded => FND_API.G_TRUE ,
339 p_msg_index => 1,
340 p_data => l_data,
341 p_msg_index_out => l_msg_index_out );
342
343 x_msg_data := l_data;
344 End if;
345 RAISE FND_API.G_EXC_ERROR;
346 END IF;
347
348 if (NOT FND_API.TO_BOOLEAN (p_validate_only)) then
349 delete from igw_budget_details_cal_amts
350 where line_item_id = p_line_item_id
351 and rate_class_id = p_rate_class_id
352 and rate_type_id = p_rate_type_id;
353
354 end if; -- p_validate_only = 'Y'
355
356
357 l_msg_count := FND_MSG_PUB.count_msg;
358 If l_msg_count > 0 THEN
359 x_msg_count := l_msg_count;
360 If l_msg_count = 1 THEN
361 fnd_msg_pub.get
362 (p_encoded => FND_API.G_TRUE ,
363 p_msg_index => 1,
364 p_data => l_data,
365 p_msg_index_out => l_msg_index_out );
366
367 x_msg_data := l_data;
368 End if;
369 RAISE FND_API.G_EXC_ERROR;
370 End if;
371
372 x_return_status := FND_API.G_RET_STS_SUCCESS;
373
374 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
375 IF p_commit = FND_API.G_TRUE THEN
376 ROLLBACK TO delete_budget_line_oh;
377 END IF;
378 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
379 fnd_msg_pub.add_exc_msg(p_pkg_name => G_package_name,
380 p_procedure_name => l_api_name,
381 p_error_text => SUBSTRB(SQLERRM,1,240));
382 fnd_msg_pub.count_and_get(p_count => x_msg_count
383 ,p_data => x_msg_data);
384 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
385
386 WHEN FND_API.G_EXC_ERROR THEN
387 IF p_commit = FND_API.G_TRUE THEN
388 ROLLBACK TO delete_budget_line_oh;
389 END IF;
390 x_return_status := 'E';
391
392 WHEN OTHERS THEN
393 IF p_commit = FND_API.G_TRUE THEN
394 ROLLBACK TO delete_budget_line_oh;
395 END IF;
396 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
397 fnd_msg_pub.add_exc_msg(p_pkg_name => G_package_name,
398 p_procedure_name => l_api_name,
399 p_error_text => SUBSTRB(SQLERRM,1,240));
400 fnd_msg_pub.count_and_get(p_count => x_msg_count
401 ,p_data => x_msg_data);
402 RAISE;
403
404
405 END; --DELETE BUDGET LINE OH
406 */
407
408 END;