[Home] [Help]
PACKAGE BODY: APPS.PA_BUDGET_MATRIX_LINES_V_PKG
Source
1 package body PA_BUDGET_MATRIX_LINES_V_PKG as
2 -- $Header: PAXBUMLB.pls 120.1 2005/09/30 10:10:04 rnamburi noship $
3
4 -- Bug Fix: 4569365. Removed MRC code.
5 -- g_mrc_exception EXCEPTION; /* FPB2: MRC */
6
7 PROCEDURE Update_Row(X_Rowid VARCHAR2,
8 X_Resource_Assignment_Id NUMBER,
9 X_Budget_Version_Id NUMBER,
10 X_Project_Id NUMBER,
11 X_Task_Id NUMBER,
12 X_Resource_List_Member_Id NUMBER,
13 X_Resource_Id NUMBER,
14 X_Description VARCHAR2,
15 X_Start_Date DATE,
16 X_End_Date DATE,
17 X_Period_Name VARCHAR2,
18 X_Quantity NUMBER,
19 X_Unit_Of_Measure VARCHAR2,
20 X_Track_As_Labor_Flag VARCHAR2,
21 X_Raw_Cost NUMBER,
22 X_Burdened_Cost NUMBER,
23 X_Revenue NUMBER,
24 X_Change_Reason_Code VARCHAR2,
25 X_Last_Update_Date DATE,
26 X_Last_Updated_By NUMBER,
27 X_Last_Update_Login NUMBER,
28 X_Attribute_Category VARCHAR2,
29 X_Attribute1 VARCHAR2,
30 X_Attribute2 VARCHAR2,
31 X_Attribute3 VARCHAR2,
32 X_Attribute4 VARCHAR2,
33 X_Attribute5 VARCHAR2,
34 X_Attribute6 VARCHAR2,
35 X_Attribute7 VARCHAR2,
36 X_Attribute8 VARCHAR2,
37 X_Attribute9 VARCHAR2,
38 X_Attribute10 VARCHAR2,
39 X_Attribute11 VARCHAR2,
40 X_Attribute12 VARCHAR2,
41 X_Attribute13 VARCHAR2,
42 X_Attribute14 VARCHAR2,
43 X_Attribute15 VARCHAR2,
44 X_Calling_Process VARCHAR2 DEFAULT 'PR',
45 X_amt_type VARCHAR2,
46 X_raw_cost_source VARCHAR2 DEFAULT 'M',
47 X_burdened_cost_source VARCHAR2 DEFAULT 'M',
48 X_quantity_source VARCHAR2 DEFAULT 'M',
49 X_revenue_source VARCHAR2 DEFAULT 'M'
50 -- Bug Fix: 4569365. Removed MRC code.
51 -- ,X_mrc_flag VARCHAR2 /* FPB2: MRC */
52
53 ) IS
54 created_by number;
55 last_updated_by number;
56 last_update_login number;
57 res_assignment_id number;
58 new_rowid varchar2(18);
59
60
61 /* FPB2: MRC */
62 l_budget_line_id PA_BUDGET_LINES.BUDGET_LINE_ID%type;
63 l_return_status VARCHAR2(1);
64 l_msg_data VARCHAR2(2000);
65 l_msg_count NUMBER;
66
67 BEGIN
68
69 created_by := fnd_global.user_id;
70 last_updated_by := fnd_global.user_id;
71 last_update_login := fnd_global.login_id;
72
73 -- Bug Fix: 4569365. Removed MRC code.
74 /* FPB2: MRC */
75 /*
76 IF x_mrc_flag IS NULL THEN
77 l_msg_data := 'x_mrc_flag cannot be null to table handler';
78 RAISE FND_API.G_EXC_ERROR;
79 END IF;
80 */
81
82 UPDATE pa_budget_lines
83 SET
84 start_date = X_Start_Date,
85 last_update_date = X_Last_Update_Date,
86 last_updated_by = X_Last_Updated_By,
87 last_update_login = X_Last_Update_Login,
88 end_date = X_End_Date,
89 period_name = X_Period_Name,
90 quantity = (X_Quantity),
91 raw_cost = pa_currency.round_currency_amt(X_Raw_Cost),
92 burdened_cost = pa_currency.round_currency_amt(X_Burdened_Cost),
93 revenue = pa_currency.round_currency_amt(X_Revenue),
94 change_reason_code = X_Change_Reason_Code,
95 description = X_Description,
96 attribute_category = X_Attribute_Category,
97 attribute1 = X_Attribute1,
98 attribute2 = X_Attribute2,
99 attribute3 = X_Attribute3,
100 attribute4 = X_Attribute4,
101 attribute5 = X_Attribute5,
102 attribute6 = X_Attribute6,
103 attribute7 = X_Attribute7,
104 attribute8 = X_Attribute8,
105 attribute9 = X_Attribute9,
106 attribute10 = X_Attribute10,
107 attribute11 = X_Attribute11,
108 attribute12 = X_Attribute12,
109 attribute13 = X_Attribute13,
110 attribute14 = X_Attribute14,
111 attribute15 = X_Attribute15,
112 raw_cost_source = decode(x_amt_type,'RC',X_raw_cost_source,raw_cost_source),
113 burdened_cost_source = decode(x_amt_type,'BC',X_burdened_cost_source,burdened_cost_source),
114 quantity_source = decode(x_amt_type,'QU',X_quantity_source,quantity_source),
115 revenue_source = decode(x_amt_type,'RE',X_revenue_source,revenue_source)
116 WHERE rowid = X_Rowid
117 returning budget_line_id into l_budget_line_id;
118
119 if (SQL%NOTFOUND) then
120 Raise NO_DATA_FOUND;
121 end if;
122
123
124 -- Bug Fix: 4569365. Removed MRC code.
125 /* FPB2: MRC */
126 /*
127 IF x_mrc_flag = 'Y' THEN
128 IF PA_MRC_FINPLAN.G_MRC_ENABLED_FOR_BUDGETS IS NULL THEN
129 PA_MRC_FINPLAN.CHECK_MRC_INSTALL
130 (x_return_status => l_return_status,
131 x_msg_count => l_msg_count,
132 x_msg_data => l_msg_data);
133 END IF;
134
135 IF PA_MRC_FINPLAN.G_MRC_ENABLED_FOR_BUDGETS AND
136 PA_MRC_FINPLAN.G_FINPLAN_MRC_OPTION_CODE = 'A' THEN
137 PA_MRC_FINPLAN.MAINTAIN_ONE_MC_BUDGET_LINE
138 (p_budget_line_id => l_budget_line_id,
139 p_budget_version_id => x_budget_version_id,
140 p_action => PA_MRC_FINPLAN.G_ACTION_UPDATE,
141 x_return_status => l_return_status,
142 x_msg_count => l_msg_count,
143 x_msg_data => l_msg_data);
144 END IF;
145
146 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
147 RAISE g_mrc_exception;
148 END IF;
149
150 END IF;
151 */
152
153 l_budget_line_id := Null; /* Since even if delete doesnt delete any rows, the value in
154 l_budget_line_id will be retained. */
155
156 DELETE FROM pa_budget_lines
157 WHERE rowid = X_Rowid
158 and ((quantity is null) and (raw_cost is null) and (burdened_cost is null) and (revenue is null))
159 returning budget_line_id into l_budget_line_id;
160
161 -- Bug Fix: 4569365. Removed MRC code.
162 /* MRC */ /* Delete the mc_budget_line if the above delete was successful */
163 /*
164 IF l_budget_line_id IS NOT NULL AND x_mrc_flag = 'Y' THEN
165 IF PA_MRC_FINPLAN.G_MRC_ENABLED_FOR_BUDGETS IS NULL THEN
166 PA_MRC_FINPLAN.CHECK_MRC_INSTALL
167 (x_return_status => l_return_status,
168 x_msg_count => l_msg_count,
169 x_msg_data => l_msg_data);
170 END IF;
171
172 IF PA_MRC_FINPLAN.G_MRC_ENABLED_FOR_BUDGETS AND
173 PA_MRC_FINPLAN.G_FINPLAN_MRC_OPTION_CODE = 'A' THEN
174 PA_MRC_FINPLAN.MAINTAIN_ONE_MC_BUDGET_LINE
175 (p_budget_line_id => l_budget_line_id,
176 p_budget_version_id => x_budget_version_id,
177 p_action => PA_MRC_FINPLAN.G_ACTION_DELETE,
178 x_return_status => l_return_status,
179 x_msg_count => l_msg_count,
180 x_msg_data => l_msg_data);
181 END IF;
182
183 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
184 RAISE g_mrc_exception;
185 END IF;
186
187 END IF;
188 */
189
190 -- clean up pa_resource_assignments if necessary
191 delete pa_resource_assignments
192 where resource_assignment_id = x_resource_assignment_id
193 and not exists
194 (select 1
195 from pa_budget_lines
196 where resource_assignment_id = x_resource_assignment_id);
197
198
199 END Update_Row;
200
201 PROCEDURE Delete_Row(X_Rowid VARCHAR2,
202 X_Calling_Process VARCHAR2 DEFAULT 'PR',
203 X_amt_type VARCHAR2
204 -- Bug Fix: 4569365. Removed MRC code.
205 --,X_mrc_flag VARCHAR2 /* FPB2: MRC */
206 ) IS
207
208 x_raw_cost number;
209 x_burdened_cost number;
210 x_revenue number;
211 x_quantity number;
212 x_resource_assignment_id number;
213 x_track_as_labor_flag varchar2(2);
214 x_budget_version_id number;
215 x_last_updated_by number;
216 x_last_update_login number;
217
218 /* FPB2: MRC */
219 l_budget_line_id PA_BUDGET_LINES.BUDGET_LINE_ID%type;
220 l_budget_version_id PA_BUDGET_LINES.BUDGET_VERSION_ID%type;
221 l_return_status VARCHAR2(1);
222 l_msg_data VARCHAR2(2000);
223 l_msg_count NUMBER;
224
225 BEGIN
226
227 x_last_updated_by := fnd_global.user_id;
228 x_last_update_login := fnd_global.login_id;
229 -- Bug Fix: 4569365. Removed MRC code.
230 /* FPB2: MRC */
231 /*
232 IF x_mrc_flag IS NULL THEN
233 l_msg_data := 'x_mrc_flag cannot be null to table handler';
234 RAISE FND_API.G_EXC_ERROR;
235 END IF;
236 */
237
238 UPDATE pa_budget_lines
239 SET
240 last_update_date = sysdate,
241 last_updated_by = X_Last_Updated_By,
242 last_update_login = X_Last_Update_Login,
243 quantity = decode(x_amt_type,'QU',null,quantity),
244 raw_cost = pa_currency.round_currency_amt(decode(x_amt_type,'RC',null,raw_cost)),
245 burdened_cost = pa_currency.round_currency_amt(decode(x_amt_type,'BC',null,burdened_cost)),
246 revenue = pa_currency.round_currency_amt(decode(x_amt_type,'RE',null,revenue))
247 WHERE rowid = X_Rowid
248 RETURNING budget_line_id, budget_version_id into l_budget_line_id,l_budget_version_id;
249
250 if (SQL%NOTFOUND) then
251 Raise NO_DATA_FOUND;
252 end if;
253 -- Bug Fix: 4569365. Removed MRC code.
254 /* FPB2: MRC */
255 /*
256 IF x_mrc_flag = 'Y' THEN
257
258 IF PA_MRC_FINPLAN.G_MRC_ENABLED_FOR_BUDGETS IS NULL THEN
259 PA_MRC_FINPLAN.CHECK_MRC_INSTALL
260 (x_return_status => l_return_status,
261 x_msg_count => l_msg_count,
262 x_msg_data => l_msg_data);
263 END IF;
264
265 IF PA_MRC_FINPLAN.G_MRC_ENABLED_FOR_BUDGETS AND
266 PA_MRC_FINPLAN.G_FINPLAN_MRC_OPTION_CODE = 'A' THEN
267 PA_MRC_FINPLAN.MAINTAIN_ONE_MC_BUDGET_LINE
268 (p_budget_line_id => l_budget_line_id,
269 p_budget_version_id => l_budget_version_id,
270 p_action => PA_MRC_FINPLAN.G_ACTION_UPDATE,
271 x_return_status => l_return_status,
272 x_msg_count => l_msg_count,
273 x_msg_data => l_msg_data);
274 END IF;
275
276 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
277 RAISE g_mrc_exception;
278 END IF;
279
280 END IF;
281 */
282
283 select resource_assignment_id
284 into x_resource_assignment_id
285 from pa_budget_lines
286 WHERE rowid = X_Rowid;
287
288 DELETE FROM pa_budget_lines
289 WHERE rowid = X_Rowid
290 and ((quantity is null) and (raw_cost is null) and (burdened_cost is null) and (revenue is null))
291 returning budget_line_id into l_budget_line_id;
292
293 -- Bug Fix: 4569365. Removed MRC code.
294 /* FPB2: MRC */ /* Delete the mc_budget_line if the above delete was successful */
295 /*
296 IF l_budget_line_id IS NOT NULL AND x_mrc_flag = 'Y' THEN
297 IF PA_MRC_FINPLAN.G_MRC_ENABLED_FOR_BUDGETS IS NULL THEN
298 PA_MRC_FINPLAN.CHECK_MRC_INSTALL
299 (x_return_status => l_return_status,
300 x_msg_count => l_msg_count,
301 x_msg_data => l_msg_data);
302 END IF;
303
304 IF PA_MRC_FINPLAN.G_MRC_ENABLED_FOR_BUDGETS AND
305 PA_MRC_FINPLAN.G_FINPLAN_MRC_OPTION_CODE = 'A' THEN
309 p_action => PA_MRC_FINPLAN.G_ACTION_DELETE,
306 PA_MRC_FINPLAN.MAINTAIN_ONE_MC_BUDGET_LINE
307 (p_budget_line_id => l_budget_line_id,
308 p_budget_version_id => l_budget_version_id,
310 x_return_status => l_return_status,
311 x_msg_count => l_msg_count,
312 x_msg_data => l_msg_data);
313 END IF;
314
315 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
316 RAISE g_mrc_exception;
317 END IF;
318
319 END IF;
320 */
321
322 -- clean up pa_resource_assignments if necessary
323 delete pa_resource_assignments
324 where resource_assignment_id = x_resource_assignment_id
325 and not exists
326 (select 1
327 from pa_budget_lines
328 where resource_assignment_id = x_resource_assignment_id);
329 Exception
330 When no_data_found then
331 null;
332 END Delete_Row;
333
334 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
335 X_Resource_Assignment_Id NUMBER,
336 X_Budget_Version_Id NUMBER,
337 X_Project_Id NUMBER,
338 X_Task_Id NUMBER,
339 X_Resource_List_Member_Id NUMBER,
340 X_Description VARCHAR2,
341 X_Start_Date DATE,
342 X_End_Date DATE,
343 X_Period_Name VARCHAR2,
344 X_Quantity NUMBER,
345 X_Unit_Of_Measure VARCHAR2,
346 X_Track_As_Labor_Flag VARCHAR2,
347 X_Raw_Cost NUMBER,
348 X_Burdened_Cost NUMBER,
349 X_Revenue NUMBER,
350 X_Change_Reason_Code VARCHAR2,
351 X_Attribute_Category VARCHAR2,
352 X_Attribute1 VARCHAR2,
353 X_Attribute2 VARCHAR2,
354 X_Attribute3 VARCHAR2,
355 X_Attribute4 VARCHAR2,
356 X_Attribute5 VARCHAR2,
357 X_Attribute6 VARCHAR2,
358 X_Attribute7 VARCHAR2,
359 X_Attribute8 VARCHAR2,
360 X_Attribute9 VARCHAR2,
361 X_Attribute10 VARCHAR2,
362 X_Attribute11 VARCHAR2,
363 X_Attribute12 VARCHAR2,
364 X_Attribute13 VARCHAR2,
365 X_Attribute14 VARCHAR2,
366 X_Attribute15 VARCHAR2)
367 IS
368 CURSOR C IS
369 SELECT l.resource_assignment_id,
370 l.start_date,
371 l.end_date,
372 l.period_name,
373 l.quantity,
374 l.raw_cost,
375 l.burdened_cost,
376 l.revenue,
377 l.change_reason_code,
378 l.description,
379 l.attribute_category,
380 l.attribute1,
381 l.attribute2,
382 l.attribute3,
383 l.attribute4,
384 l.attribute5,
385 l.attribute6,
386 l.attribute7,
387 l.attribute8,
388 l.attribute9,
389 l.attribute10,
390 l.attribute11,
391 l.attribute12,
392 l.attribute13,
393 l.attribute14,
394 l.attribute15,
395 a.budget_version_id,
396 a.project_id,
397 a.task_id,
398 a.resource_list_member_id,
399 a.unit_of_measure,
400 a.track_as_labor_flag
401 FROM pa_resource_assignments a,
402 pa_budget_lines l
403 WHERE l.rowid = X_Rowid
404 AND l.resource_assignment_id = a.resource_assignment_id
405 FOR UPDATE NOWAIT;
406 Recinfo C%ROWTYPE;
407
408
409 BEGIN
410
411 OPEN C;
412 FETCH C INTO Recinfo;
413 if (C%NOTFOUND) then
414 CLOSE C;
415 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
416 APP_EXCEPTION.Raise_Exception;
417 end if;
418 CLOSE C;
419 if (
420 (Recinfo.resource_assignment_id = X_Resource_Assignment_Id)
421 AND (Recinfo.budget_version_id = X_Budget_Version_Id)
422 AND (Recinfo.project_id = X_Project_Id)
423 AND ( (Recinfo.task_id = X_task_Id)
424 OR ( (Recinfo.task_id IS NULL)
425 AND (X_Task_Id IS NULL)))
426 AND (Recinfo.resource_list_member_id = X_Resource_List_Member_Id)
427 AND ( (Recinfo.description = X_Description)
431 OR ( (Recinfo.quantity IS NULL)
428 OR ( (Recinfo.description IS NULL)
429 AND (X_Description IS NULL)))
430 AND ( (Recinfo.quantity = X_Quantity)
432 AND (X_Quantity IS NULL))
433 )
434 AND ( (Recinfo.raw_cost = X_Raw_Cost)
435 OR ( (Recinfo.raw_cost IS NULL)
436 AND (X_Raw_Cost IS NULL))
437 )
438 AND ( (Recinfo.burdened_cost = X_Burdened_Cost)
439 OR ( (Recinfo.burdened_cost IS NULL)
440 AND (X_Burdened_Cost IS NULL))
441 )
442 AND ( (Recinfo.revenue = X_Revenue)
443 OR ( (Recinfo.revenue IS NULL)
444 AND (X_Revenue IS NULL))
445 )
446 AND ( (Recinfo.change_reason_code = X_Change_Reason_Code)
447 OR ( (Recinfo.change_reason_code IS NULL)
448 AND (X_Change_Reason_Code IS NULL)))
449 AND ( (Recinfo.attribute_category = X_Attribute_Category)
450 OR ( (Recinfo.attribute_category IS NULL)
451 AND (X_Attribute_Category IS NULL)))
452 AND ( (Recinfo.attribute1 = X_Attribute1)
453 OR ( (Recinfo.attribute1 IS NULL)
454 AND (X_Attribute1 IS NULL)))
455 AND ( (Recinfo.attribute2 = X_Attribute2)
456 OR ( (Recinfo.attribute2 IS NULL)
457 AND (X_Attribute2 IS NULL)))
458 AND ( (Recinfo.attribute3 = X_Attribute3)
459 OR ( (Recinfo.attribute3 IS NULL)
460 AND (X_Attribute3 IS NULL)))
461 AND ( (Recinfo.attribute4 = X_Attribute4)
462 OR ( (Recinfo.attribute4 IS NULL)
463 AND (X_Attribute4 IS NULL)))
464 AND ( (Recinfo.attribute5 = X_Attribute5)
465 OR ( (Recinfo.attribute5 IS NULL)
466 AND (X_Attribute5 IS NULL)))
467 AND ( (Recinfo.attribute6 = X_Attribute6)
468 OR ( (Recinfo.attribute6 IS NULL)
469 AND (X_Attribute6 IS NULL)))
470 AND ( (Recinfo.attribute7 = X_Attribute7)
471 OR ( (Recinfo.attribute7 IS NULL)
472 AND (X_Attribute7 IS NULL)))
473 AND ( (Recinfo.attribute8 = X_Attribute8)
474 OR ( (Recinfo.attribute8 IS NULL)
475 AND (X_Attribute8 IS NULL)))
476 AND ( (Recinfo.attribute9 = X_Attribute9)
477 OR ( (Recinfo.attribute9 IS NULL)
478 AND (X_Attribute9 IS NULL)))
482 AND ( (Recinfo.attribute11 = X_Attribute11)
479 AND ( (Recinfo.attribute10 = X_Attribute10)
480 OR ( (Recinfo.attribute10 IS NULL)
481 AND (X_Attribute10 IS NULL)))
483 OR ( (Recinfo.attribute11 IS NULL)
484 AND (X_Attribute11 IS NULL)))
485 AND ( (Recinfo.attribute12 = X_Attribute12)
486 OR ( (Recinfo.attribute12 IS NULL)
487 AND (X_Attribute12 IS NULL)))
488 AND ( (Recinfo.attribute13 = X_Attribute13)
489 OR ( (Recinfo.attribute13 IS NULL)
490 AND (X_Attribute13 IS NULL)))
491 AND ( (Recinfo.attribute14 = X_Attribute14)
492 OR ( (Recinfo.attribute14 IS NULL)
493 AND (X_Attribute14 IS NULL)))
494 AND ( (Recinfo.attribute15 = X_Attribute15)
495 OR ( (Recinfo.attribute15 IS NULL)
496 AND (X_Attribute15 IS NULL)))
497 ) then
498 return;
499 else
500 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
501 APP_EXCEPTION.Raise_Exception;
502 end if;
503 END Lock_Row;
504
505 END PA_BUDGET_MATRIX_LINES_V_PKG;