[Home] [Help]
PACKAGE BODY: APPS.PA_CI_DIR_COST_PUB
Source
1 PACKAGE BODY PA_CI_DIR_COST_PUB AS
2 /* $Header: PAPCDCDB.pls 120.1 2010/10/07 21:24:35 rmunjulu noship $*/
3
4 --
5 -- Procedure insert_row():
6 -- Called from the direct cost region of the planning UI
7 -- Calls the process_planning_lines() to rollup the planning lines,
8 -- which calls the insert/update resource assignment API
9 -- Inserts data into pa_ci_direct_cost_details table at detail level
10 --
11 procedure insert_row(
12 p_api_version IN NUMBER,
13 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
14 x_return_status OUT NOCOPY VARCHAR2,
15 x_msg_count OUT NOCOPY NUMBER,
16 x_msg_data OUT NOCOPY VARCHAR2,
17 p_bvid IN NUMBER,
18 p_dc_line_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
19 p_ci_id IN NUMBER,
20 p_project_id IN NUMBER,
21 p_task_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
22 p_expenditure_type_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE,
23 p_rlmi_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
24 p_unit_of_measure_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE,
25 p_currency_code_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE,
26 p_planning_resource_rate_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
27 p_quantity_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
28 p_raw_cost_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
29 p_burdened_cost_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
30 p_raw_cost_rate_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
31 p_burden_cost_rate_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
32 p_resource_assignment_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
33 p_effective_from_tbl IN SYSTEM.PA_DATE_TBL_TYPE DEFAULT SYSTEM.PA_DATE_TBL_TYPE(),
34 p_effective_to_tbl IN SYSTEM.PA_DATE_TBL_TYPE DEFAULT SYSTEM.PA_DATE_TBL_TYPE(),
35 p_change_reason_code IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE DEFAULT SYSTEM.PA_VARCHAR2_30_TBL_TYPE(),
36 p_change_description IN SYSTEM.PA_VARCHAR2_2000_TBL_TYPE DEFAULT SYSTEM.PA_VARCHAR2_2000_TBL_TYPE()) IS
37
38 l_api_version number := 1;
39 l_api_name CONSTANT VARCHAR2(30) := 'PUB.insert_row';
40 l_return_status VARCHAR2(1) := PA_API.G_RET_STS_SUCCESS;
41 l_msg_count number;
42 l_msg_data varchar2(2000);
43
44 l_dc_line_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
45
46 l_PaCiDirCostDetTbl PaCiDirCostTblType;
47 x_PaCiDirCostDetTbl PaCiDirCostTblType;
48
49 cursor get_budget_details(c_bvid number, c_task_id number,
50 c_rlmi number) IS
51 select prac.resource_assignment_id,
52 planning_start_date,
53 planning_end_date,
54 prac.txn_average_raw_cost_rate planning_resource_rate,
55 prac.txn_average_burden_cost_rate burden_cost_rate
56 from pa_resource_assignments pra, pa_resource_asgn_curr prac
57 where pra.budget_version_id = c_bvId
58 and pra.task_id = c_task_id
59 and pra.resource_list_member_id = c_rlmi
60 and prac.resource_assignment_id = pra.resource_assignment_id;
61
62 begin
63
64 savepoint pub_insert_row;
65
66 if (p_task_id_tbl.count > 0) then
67 for i in p_task_id_tbl.first..p_task_id_tbl.last loop
68
69 l_dc_line_id_tbl.extend(1);
70
71 select pa_ci_dir_cost_details_s.nextval
72 into l_dc_line_id_tbl(i)
73 from dual;
74
75 --l_dc_line_id_tbl(i) := pa_ci_dir_cost_details_s.nextval;
76 l_PaCiDirCostDetTbl(i).dc_line_id := l_dc_line_id_tbl(i);
77
78 l_PaCiDirCostDetTbl(i).ci_id := p_ci_id;
79 l_PaCiDirCostDetTbl(i).project_id := p_project_id;
80 l_PaCiDirCostDetTbl(i).task_id := p_task_id_tbl(i);
81 l_PaCiDirCostDetTbl(i).expenditure_type := p_expenditure_type_tbl(i);
82 l_PaCiDirCostDetTbl(i).resource_list_member_id := p_rlmi_id_tbl(i);
83 l_PaCiDirCostDetTbl(i).unit_of_measure := p_unit_of_measure_tbl(i);
84 l_PaCiDirCostDetTbl(i).currency_code := p_currency_code_tbl(i);
85
86 if p_quantity_tbl.exists(i) then
87 l_PaCiDirCostDetTbl(i).quantity := p_quantity_tbl(i);
88 else
89 l_PaCiDirCostDetTbl(i).quantity := NULL;
90 end if;
91
92 if p_planning_resource_rate_tbl.exists(i) then
93 l_PaCiDirCostDetTbl(i).planning_resource_rate := p_planning_resource_rate_tbl(i);
94 else
95 l_PaCiDirCostDetTbl(i).planning_resource_rate := NULL;
96 end if;
97
98 if p_raw_cost_tbl.exists(i) then
99 l_PaCiDirCostDetTbl(i).raw_cost := p_raw_cost_tbl(i);
100 else
101 l_PaCiDirCostDetTbl(i).raw_cost := null;
102 end if;
103
104 /*
105 p_burdened_cost_tbl, p_raw_cost_rate_tbl, p_burden_cost_rate_tbl,
106 p_effective_from_tbl, p_effective_to_tbl and
107 p_resource_assignment_id_tbl are not available during insert
108 */
109
110 l_PaCiDirCostDetTbl(i).burdened_cost := NULL;
111 l_PaCiDirCostDetTbl(i).raw_cost_rate := NULL;
112 l_PaCiDirCostDetTbl(i).burden_cost_rate := NULL;
113
114 l_PaCiDirCostDetTbl(i).resource_assignment_id := NULL;
115
116 l_PaCiDirCostDetTbl(i).effective_from := NULL;
117 l_PaCiDirCostDetTbl(i).effective_to := NULL;
118
119 if p_change_reason_code.exists(i) then
120 l_PaCiDirCostDetTbl(i).change_reason_code := p_change_reason_code(i);
121 else
122 l_PaCiDirCostDetTbl(i).change_reason_code := NULL;
123 end if;
124
125 if p_change_description.exists(i) then
126 l_PaCiDirCostDetTbl(i).change_description := p_change_description(i);
127 else
128 l_PaCiDirCostDetTbl(i).change_description := NULL;
129 end if;
130
131 l_PaCiDirCostDetTbl(i).creation_date := sysdate;
132 l_PaCiDirCostDetTbl(i).created_by := FND_GLOBAL.USER_ID;
133 l_PaCiDirCostDetTbl(i).last_update_date := sysdate;
134 l_PaCiDirCostDetTbl(i).last_update_by := FND_GLOBAL.USER_ID;
135 l_PaCiDirCostDetTbl(i).last_update_login := FND_GLOBAL.LOGIN_ID;
136 end loop;
137
138 pa_ci_dir_cost_pvt.insert_row(
139 p_api_version => l_api_version,
140 p_init_msg_list => FND_API.G_FALSE,
141 x_return_status => l_return_status,
142 x_msg_count => l_msg_count,
143 x_msg_data => l_msg_data,
144 PPaCiDirectCostDetailsTbl => l_PaCiDirCostDetTbl,
145 XPaCiDirectCostDetailsTbl => x_PaCiDirCostDetTbl);
146
147 IF (l_return_status = PA_API.G_RET_STS_UNEXP_ERROR) THEN
148 RAISE PA_API.G_EXCEPTION_UNEXPECTED_ERROR;
149 ELSIF (l_return_status = PA_API.G_RET_STS_ERROR) THEN
150 RAISE PA_API.G_EXCEPTION_ERROR;
151 END IF;
152
153 pa_process_ci_lines_pkg.process_planning_lines(
154 p_api_version => l_api_version,
155 p_init_msg_list => FND_API.G_FALSE,
156 x_return_status => l_return_status,
157 x_msg_count => l_msg_count,
158 x_msg_data => l_msg_data,
159 p_calling_context => 'DIRECT_COST',
160 p_action_type => 'INSERT',
161 p_bvid => p_bvid,
162 p_ci_id => p_ci_id,
163 p_line_id_tbl => l_dc_line_id_tbl,
164 p_project_id => p_project_id,
165 p_task_id_tbl => p_task_id_tbl,
166 p_currency_code_tbl => p_currency_code_tbl,
167 p_rlmi_id_tbl => p_rlmi_id_tbl,
168 p_res_assgn_id_tbl => p_resource_assignment_id_tbl,
169 p_quantity_tbl => p_quantity_tbl,
170 p_raw_cost_tbl => p_raw_cost_tbl
171 );
172
173 IF (l_return_status = PA_API.G_RET_STS_UNEXP_ERROR) THEN
174 RAISE PA_API.G_EXCEPTION_UNEXPECTED_ERROR;
175 ELSIF (l_return_status = PA_API.G_RET_STS_ERROR) THEN
176 RAISE PA_API.G_EXCEPTION_ERROR;
177 END IF;
178
179 forall i in l_dc_line_id_tbl.first..l_dc_line_id_tbl.last
180 update pa_ci_direct_cost_details pcdc
181 set (resource_assignment_id, effective_from, effective_to,
182 planning_resource_rate, burden_cost_rate,
183 raw_cost, burdened_cost) =
184 (select prac.resource_assignment_id,
185 decode(pcdc.effective_from,
186 null,pra.planning_start_date, pcdc.effective_from),
187 decode(pcdc.effective_to,
188 null, pra.planning_end_date, pcdc.effective_to),
189 prac.txn_average_raw_cost_rate,
190 prac.txn_average_burden_cost_rate,
191 decode(pcdc.quantity, null, pcdc.raw_cost,
192 pcdc.quantity * prac.txn_average_raw_cost_rate),
193 decode(pcdc.quantity, null,
194 pcdc.raw_cost * prac.txn_average_burden_cost_rate,
195 pcdc.quantity * prac.txn_average_burden_cost_rate)
196 from pa_resource_assignments pra, pa_resource_asgn_curr prac
197 where pra.budget_version_id = p_bvId
198 and pra.task_id = pcdc.task_id
199 and pra.resource_list_member_id = pcdc.resource_list_member_id
200 and prac.txn_currency_code = pcdc.currency_code
201 and prac.resource_assignment_id = pra.resource_assignment_id)
202 where ci_id = p_ci_id
203 and dc_line_id = l_dc_line_id_tbl(i);
204
205 end if;
206
207 PA_API.END_ACTIVITY(l_msg_count, l_msg_data);
208 x_return_status := PA_API.G_RET_STS_SUCCESS;
209
210 EXCEPTION
211
212 WHEN PA_API.G_EXCEPTION_ERROR THEN
213 ROLLBACK TO SAVEPOINT PUB_INSERT_ROW;
214
215 FND_MSG_PUB.Count_And_Get
216 (p_count => x_msg_count,
217 p_data => x_msg_data);
218 x_return_status := l_return_status;
219
220 WHEN PA_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
221
222 ROLLBACK TO SAVEPOINT PUB_INSERT_ROW;
223
224 FND_MSG_PUB.Count_And_Get
225 (p_count => x_msg_count,
226 p_data => x_msg_data);
227 x_return_status := l_return_status;
228
229 WHEN OTHERS THEN
230
231 ROLLBACK TO SAVEPOINT PUB_INSERT_ROW;
232
233 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
234 THEN
235 FND_MSG_PUB.Add_Exc_Msg
236 (g_pkg_name,
237 l_api_name);
238 END IF;
239 FND_MSG_PUB.Count_And_Get
240 (p_count => x_msg_count,
241 p_data => x_msg_data
242 );
243
244 end insert_row;
245
246 --
247 -- Procedure update_row():
248 -- Called from the direct cost region of the planning UI
249 -- Calls the process_planning_lines() to rollup the planning lines,
250 -- which calls the update resource assignment API.
251 -- Updates the data in pa_ci_direct_cost_details table.
252 --
253 PROCEDURE update_row(
254 p_api_version IN NUMBER,
255 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
256 x_return_status OUT NOCOPY VARCHAR2,
257 x_msg_count OUT NOCOPY NUMBER,
258 x_msg_data OUT NOCOPY VARCHAR2,
259 p_bvid IN NUMBER,
260 p_dc_line_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
261 p_ci_id IN NUMBER,
262 p_project_id IN NUMBER,
263 p_task_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
264 p_expenditure_type_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE,
265 p_rlmi_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
266 p_unit_of_measure_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE,
267 p_currency_code_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE,
268 p_quantity_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
269 p_planning_resource_rate_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
270 p_raw_cost_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
271 p_burdened_cost_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
272 p_raw_cost_rate_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
273 p_burden_cost_rate_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
274 p_resource_assignment_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
275 p_effective_from_tbl IN SYSTEM.PA_DATE_TBL_TYPE,
276 p_effective_to_tbl IN SYSTEM.PA_DATE_TBL_TYPE,
277 p_change_reason_code IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE,
278 p_change_description IN SYSTEM.PA_VARCHAR2_2000_TBL_TYPE) IS
279
280 l_api_version number := 1;
281 l_api_name CONSTANT VARCHAR2(30) := 'Pub.update_row';
282 l_return_status VARCHAR2(1) := PA_API.G_RET_STS_SUCCESS;
283 l_msg_count number;
284 l_msg_data varchar2(2000);
285 l_PaCiDirCostDetTbl PaCiDirCostTblType;
286 x_PaCiDirCostDetTbl PaCiDirCostTblType;
287
288 cursor get_dc_line(c_dc_line_id number) is
289 select task_id, resource_list_member_id,
290 expenditure_type, nvl(quantity, -1) quantity,
291 nvl(raw_cost, -1) raw_cost,
292 effective_from, effective_to
293 from pa_ci_direct_cost_details
294 where ci_id = p_ci_id
295 and dc_line_id = c_dc_line_id;
296
297 dc_line_row get_dc_line%ROWTYPE;
298
299 k number;
300
301 TYPE varchar1_tbl is table of varchar2(1) index by binary_integer;
302 budget_impact_tbl varchar1_tbl;
303
304 b_task_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
305 b_dc_line_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
306 b_quantity_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
307 b_raw_cost_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
308 b_res_assgn_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
309 b_rlmi_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
310 b_currency_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
311
312 begin
313
314 budget_impact_tbl.delete;
315
316 SAVEPOINT PUB_UPDATE_ROW;
317
318 if (p_task_id_tbl.count > 0) then
319 for i in p_task_id_tbl.first..p_task_id_tbl.last loop
320
321 open get_dc_line(p_dc_line_id_tbl(i));
322 fetch get_dc_line into dc_line_row;
323 close get_dc_line;
324
325 budget_impact_tbl(i) := 'N';
326
327 if p_quantity_tbl.exists(i) and p_quantity_tbl(i) <> FND_API.G_MISS_NUM
328 and p_quantity_tbl(i) is not null then
329 if dc_line_row.quantity <> p_quantity_tbl(i) then
330 budget_impact_tbl(i) := 'Y';
331 end if;
332 end if;
333
334 if p_raw_cost_tbl.exists(i) and p_raw_cost_tbl(i) <> FND_API.G_MISS_NUM and
335 p_raw_cost_tbl(i) is not null then
336 if dc_line_row.raw_cost <> p_raw_cost_tbl(i) then
337 budget_impact_tbl(i) := 'Y';
338 end if;
339 end if;
340
341 if dc_line_row.effective_from <> p_effective_from_tbl(i) then
342 budget_impact_tbl(i) := 'Y';
343 end if;
344
345 if dc_line_row.effective_to <> p_effective_to_tbl(i) then
346 budget_impact_tbl(i) := 'Y';
347 end if;
348
349
350 l_PaCiDirCostDetTbl(i).dc_line_id := p_dc_line_id_tbl(i);
351 l_PaCiDirCostDetTbl(i).ci_id := p_ci_id;
352 l_PaCiDirCostDetTbl(i).project_id := p_project_id;
353 l_PaCiDirCostDetTbl(i).task_id := p_task_id_tbl(i);
354 l_PaCiDirCostDetTbl(i).expenditure_type := p_expenditure_type_tbl(i);
355 l_PaCiDirCostDetTbl(i).resource_list_member_id := p_rlmi_id_tbl(i);
356 l_PaCiDirCostDetTbl(i).unit_of_measure := p_unit_of_measure_tbl(i);
357 l_PaCiDirCostDetTbl(i).currency_code := p_currency_code_tbl(i);
358
359 if p_quantity_tbl.exists(i) then
360 l_PaCiDirCostDetTbl(i).quantity := p_quantity_tbl(i);
361 else
362 l_PaCiDirCostDetTbl(i).quantity := PA_API.G_MISS_NUM;
363 end if;
364
365 if p_planning_resource_rate_tbl.exists(i) then
366 l_PaCiDirCostDetTbl(i).planning_resource_rate := p_planning_resource_rate_tbl(i);
367 else
368 l_PaCiDirCostDetTbl(i).planning_resource_rate := PA_API.G_MISS_NUM;
369 end if;
370
371 if p_raw_cost_tbl.exists(i) then
372 l_PaCiDirCostDetTbl(i).raw_cost := p_raw_cost_tbl(i);
373 else
374 l_PaCiDirCostDetTbl(i).raw_cost := PA_API.G_MISS_NUM;
375 end if;
376
377 l_PaCiDirCostDetTbl(i).burdened_cost := PA_API.G_MISS_NUM;
378 l_PaCiDirCostDetTbl(i).raw_cost_rate := PA_API.G_MISS_NUM;
379 l_PaCiDirCostDetTbl(i).burden_cost_rate := PA_API.G_MISS_NUM;
380 l_PaCiDirCostDetTbl(i).resource_assignment_id := PA_API.G_MISS_NUM;
381 l_PaCiDirCostDetTbl(i).effective_from := p_effective_from_tbl(i);
382 l_PaCiDirCostDetTbl(i).effective_to := p_effective_to_tbl(i);
383
384 if p_change_reason_code.exists(i) then
385 l_PaCiDirCostDetTbl(i).change_reason_code := p_change_reason_code(i);
386 else
387 l_PaCiDirCostDetTbl(i).change_reason_code := NULL;
388 end if;
389
390 if p_change_description.exists(i) then
391 l_PaCiDirCostDetTbl(i).change_description := p_change_description(i);
392 else
393 l_PaCiDirCostDetTbl(i).change_description := NULL;
394 end if;
395
396 end loop;
397
398 pa_ci_dir_cost_pvt.update_row(
399 p_api_version => l_api_version,
400 p_init_msg_list => FND_API.G_FALSE,
401 x_return_status => l_return_status,
402 x_msg_count => l_msg_count,
403 x_msg_data => l_msg_data,
404 PPaCiDirectCostDetailsTbl => l_PaCiDirCostDetTbl,
405 XPaCiDirectCostDetailsTbl => x_PaCiDirCostDetTbl);
406
407 IF (l_return_status = PA_API.G_RET_STS_UNEXP_ERROR) THEN
408 RAISE PA_API.G_EXCEPTION_UNEXPECTED_ERROR;
409 ELSIF (l_return_status = PA_API.G_RET_STS_ERROR) THEN
410 RAISE PA_API.G_EXCEPTION_ERROR;
411 END IF;
412
413 k := 0;
414 for i in budget_impact_tbl.first..budget_impact_tbl.last loop
415 if budget_impact_tbl(i) = 'Y' then
416
417 k := k + 1;
418
419 b_dc_line_id_tbl.extend(1);
420 b_task_id_tbl.extend(1);
421 b_currency_code_tbl.extend(1);
422 b_rlmi_id_tbl.extend(1);
423 b_res_assgn_id_tbl.extend(1);
424 b_quantity_tbl.extend(1);
425 b_raw_cost_tbl.extend(1);
426
427 b_dc_line_id_tbl(k) := p_dc_line_id_tbl(i);
428 b_task_id_tbl(k) := p_task_id_tbl(i);
429 b_currency_code_tbl(k) := p_currency_code_tbl(i);
430 b_rlmi_id_tbl(k) := p_rlmi_id_tbl(i);
431 b_res_assgn_id_tbl(k) := p_resource_assignment_id_tbl(i);
432
433 if p_quantity_tbl.exists(i) then
434 b_quantity_tbl(k) := p_quantity_tbl(i);
435 end if;
436
437 if p_raw_cost_tbl.exists(i) then
438 b_raw_cost_tbl(k) := p_raw_cost_tbl(i);
439 end if;
440
441 end if;
442 end loop;
443
444 if b_task_id_tbl.count > 0 then
445 pa_process_ci_lines_pkg.process_planning_lines(
446 p_api_version => l_api_version,
447 p_init_msg_list => FND_API.G_FALSE,
448 x_return_status => l_return_status,
449 x_msg_count => l_msg_count,
450 x_msg_data => l_msg_data,
451 p_calling_context => 'DIRECT_COST',
452 p_action_type => 'UPDATE',
453 p_bvid => p_bvid,
454 p_ci_id => p_ci_id,
455 p_line_id_tbl => b_dc_line_id_tbl,
456 p_project_id => p_project_id,
457 p_task_id_tbl => b_task_id_tbl,
458 p_currency_code_tbl => b_currency_code_tbl,
459 p_rlmi_id_tbl => b_rlmi_id_tbl,
460 p_res_assgn_id_tbl => b_res_assgn_id_tbl,
461 p_quantity_tbl => b_quantity_tbl,
462 p_raw_cost_tbl => b_raw_cost_tbl
463 );
464
465 IF (l_return_status = PA_API.G_RET_STS_UNEXP_ERROR) THEN
466 RAISE PA_API.G_EXCEPTION_UNEXPECTED_ERROR;
467 ELSIF (l_return_status = PA_API.G_RET_STS_ERROR) THEN
468 RAISE PA_API.G_EXCEPTION_ERROR;
469 END IF;
470 /*
471 forall i in b_task_id_tbl.first..b_task_id_tbl.last
472 update pa_ci_direct_cost_details pcdc
473 set ( raw_cost, burdened_cost) =
474 (select decode(pcdc.quantity, null, pcdc.raw_cost,
475 pcdc.quantity * prac.txn_average_raw_cost_rate),
476 decode(pcdc.quantity, null,
477 pcdc.raw_cost * prac.txn_average_burden_cost_rate,
478 pcdc.quantity * prac.txn_average_burden_cost_rate)
479 from pa_resource_assignments pra, pa_resource_asgn_curr prac
480 where pra.budget_version_id = p_bvId
481 and pra.resource_assignment_id = pcdc.resource_assignment_id
482 and prac.resource_assignment_id = pra.resource_assignment_id
483 and prac.txn_currency_code = pcdc.currency_code)
484 where ci_id = p_ci_id
485 and dc_line_id = b_dc_line_id_tbl(i);
486 */
487 forall i in b_task_id_tbl.first..b_task_id_tbl.last
488 update pa_ci_direct_cost_details pcdc
489 set raw_cost = decode(pcdc.quantity, null, pcdc.raw_cost,
490 pcdc.quantity * pcdc.planning_resource_rate),
491 burdened_cost = decode(pcdc.quantity, null,
492 pcdc.raw_cost * pcdc.burden_cost_rate,
493 pcdc.quantity * pcdc.burden_cost_rate)
494 where ci_id = p_ci_id
495 and dc_line_id = b_dc_line_id_tbl(i);
496
497 end if;
498
499 end if;
500
501 PA_API.END_ACTIVITY(l_msg_count, l_msg_data);
502 x_return_status := PA_API.G_RET_STS_SUCCESS;
503
504 EXCEPTION
505
506 WHEN PA_API.G_EXCEPTION_ERROR THEN
507
508 ROLLBACK TO SAVEPOINT PUB_UPDATE_ROW;
509
510 FND_MSG_PUB.Count_And_Get
511 (p_count => x_msg_count,
512 p_data => x_msg_data);
513 x_return_status := l_return_status;
514
515 WHEN PA_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
516
517 ROLLBACK TO SAVEPOINT PUB_UPDATE_ROW;
518
519 FND_MSG_PUB.Count_And_Get
520 (p_count => x_msg_count,
521 p_data => x_msg_data);
522 x_return_status := l_return_status;
523
524 WHEN OTHERS THEN
525
526 ROLLBACK TO SAVEPOINT PUB_UPDATE_ROW;
527
528 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
529 THEN
530 FND_MSG_PUB.Add_Exc_Msg
531 (g_pkg_name,
532 l_api_name);
533 END IF;
534 FND_MSG_PUB.Count_And_Get
535 (p_count => x_msg_count,
536 p_data => x_msg_data
537 );
538
539 end update_row;
540
541 --
542 -- Procedure delete_row():
543 -- Called from the direct cost region of the planning UI
544 -- Calls the process_planning_lines() to rollup the planning lines,
545 -- which determines if the resource assignment needs to deleted
546 -- or updated.
547 -- Deletes data from pa_ci_direct_cost_details table
548 --
549 PROCEDURE delete_row(
550 p_api_version IN NUMBER,
551 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
552 x_return_status OUT NOCOPY VARCHAR2,
553 x_msg_count OUT NOCOPY NUMBER,
554 x_msg_data OUT NOCOPY VARCHAR2,
555 p_dc_line_id_TBL IN SYSTEM.PA_NUM_TBL_TYPE,
556 p_ci_id IN NUMBER,
557 p_project_id IN NUMBER,
558 p_task_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
559 p_expenditure_type_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE,
560 p_rlmi_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
561 p_currency_code_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE) IS
562
563 l_api_version number := 1;
564 l_api_name CONSTANT VARCHAR2(30) := 'Pub.delete_row';
565 l_return_status VARCHAR2(1) := PA_API.G_RET_STS_SUCCESS;
566 l_msg_count number;
567 l_msg_data varchar2(2000);
568 l_PaCiDirCostDetTbl PaCiDirCostTblType;
569
570 l_resource_assignment_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
571
572 l_bvid NUMBER;
573
574 cursor get_bvid(c_ci_id number) is
575 select budget_version_id
576 from pa_budget_versions
577 where ci_id = c_ci_id
578 and version_type in ('COST', 'ALL');
579
580 begin
581
582 SAVEPOINT PUB_DELETE_ROW;
583
584 open get_bvid(p_ci_id);
585 fetch get_bvid into l_bvid;
586 close get_bvid;
587
588 if (p_task_id_tbl.count > 0) then
589 for i in p_task_id_tbl.first..p_task_id_tbl.last loop
590 l_PaCiDirCostDetTbl(i).dc_line_id := p_dc_line_id_tbl(i);
591 l_PaCiDirCostDetTbl(i).ci_id := p_ci_id;
592 l_PaCiDirCostDetTbl(i).project_id := p_project_id;
593 l_PaCiDirCostDetTbl(i).task_id := p_task_id_tbl(i);
594 l_PaCiDirCostDetTbl(i).expenditure_type := p_expenditure_type_tbl(i);
595 l_PaCiDirCostDetTbl(i).resource_list_member_id := p_rlmi_id_tbl(i);
596 l_PaCiDirCostDetTbl(i).currency_code := p_currency_code_tbl(i);
597 l_PaCiDirCostDetTbl(i).quantity := PA_API.G_MISS_NUM;
598 l_PaCiDirCostDetTbl(i).raw_cost := PA_API.G_MISS_NUM;
599 l_resource_assignment_id_tbl.extend(1);
600 l_resource_assignment_id_tbl(i) := null;
601 end loop;
602
603 pa_ci_dir_cost_pvt.delete_row(
604 p_api_version => l_api_version,
605 p_init_msg_list => FND_API.G_FALSE,
606 x_return_status => l_return_status,
607 x_msg_count => l_msg_count,
608 x_msg_data => l_msg_data,
609 PPaCiDirectCostDetailsTbl => l_PaCiDirCostDetTbl);
610
611 IF (l_return_status = PA_API.G_RET_STS_UNEXP_ERROR) THEN
612 RAISE PA_API.G_EXCEPTION_UNEXPECTED_ERROR;
613 ELSIF (l_return_status = PA_API.G_RET_STS_ERROR) THEN
614 RAISE PA_API.G_EXCEPTION_ERROR;
615 END IF;
616
617 pa_process_ci_lines_pkg.process_planning_lines(
618 p_api_version => l_api_version,
619 p_init_msg_list => FND_API.G_FALSE,
620 x_return_status => l_return_status,
621 x_msg_count => l_msg_count,
622 x_msg_data => l_msg_data,
623 p_calling_context => 'DIRECT_COST',
624 p_action_type => 'DELETE',
625 p_bvid => l_bvid,
626 p_ci_id => p_ci_id,
627 p_line_id_tbl => p_dc_line_id_tbl,
628 p_project_id => p_project_id,
629 p_task_id_tbl => p_task_id_tbl,
630 p_currency_code_tbl => p_currency_code_tbl,
631 p_rlmi_id_tbl => p_rlmi_id_tbl,
632 p_res_assgn_id_tbl => l_resource_assignment_id_tbl
633 );
634
635 IF (l_return_status = PA_API.G_RET_STS_UNEXP_ERROR) THEN
636 RAISE PA_API.G_EXCEPTION_UNEXPECTED_ERROR;
637 ELSIF (l_return_status = PA_API.G_RET_STS_ERROR) THEN
638 RAISE PA_API.G_EXCEPTION_ERROR;
639 END IF;
640
641 end if;
642
643 PA_API.END_ACTIVITY(l_msg_count, l_msg_data);
644 x_return_status := PA_API.G_RET_STS_SUCCESS;
645
646 EXCEPTION
647 WHEN PA_API.G_EXCEPTION_ERROR THEN
648
649 ROLLBACK TO SAVEPOINT PUB_DELETE_ROW;
650
651 FND_MSG_PUB.Count_And_Get
652 (p_count => x_msg_count,
653 p_data => x_msg_data);
654 x_return_status := l_return_status;
655
656 WHEN PA_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
657
658 ROLLBACK TO SAVEPOINT PUB_DELETE_ROW;
659
660 FND_MSG_PUB.Count_And_Get
661 (p_count => x_msg_count,
662 p_data => x_msg_data);
663 x_return_status := l_return_status;
664
665 WHEN OTHERS THEN
666
667 ROLLBACK TO SAVEPOINT PUB_DELETE_ROW;
668
669 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
670 THEN
671 FND_MSG_PUB.Add_Exc_Msg
672 (g_pkg_name,
673 l_api_name);
674 END IF;
675 FND_MSG_PUB.Count_And_Get
676 (p_count => x_msg_count,
677 p_data => x_msg_data
678 );
679
680 end delete_row;
681
682 end pa_ci_dir_cost_pub;