[Home] [Help]
PACKAGE BODY: APPS.PA_BUDGET_LINES_V_PKG
Source
1 package body PA_BUDGET_LINES_V_PKG as
2 -- $Header: PAXBUBLB.pls 120.3 2005/09/23 12:17:59 rnamburi noship $
3
4
5 --Name: Insert_Row
6 --Type: Procedure
7 --Description:
8 --
9 --Called Subprograms: none.
10 --
11 --Notes
12 -- !!! This procedure can only be used with r11.5.7 Budgets. !!!
13 --
14 -- For similar functionality for FP plan types, FP specific procedures
15 -- must be called.
16 --
17 -- PA_BUDGET_UTILS.Get_Project_Currency_Info uses package
18 -- globals to optimize peformance. It also uses x_project_id
19 -- to determine when the globals should be refreshed.
20 --
21 --History
22 -- xx-xxx-xxxx who? - Created
23 --
24 -- 16-AUG-02 jwhite As per the FP model conversion effort, did the following:
25 --
26 -- 1) Modidified the pa_resource_assignments insert to populte
27 -- resource_assignment_type as 'USER_ENTERED'.
28 --
29 -- 2) Added a call to PA_BUDGET_UTILS.Get_Project_Currency_Info
30 -- to populate the following new currency columns:
31 -- - projfunc_currency_code
32 -- - project_currency_code
33 -- - txn_currency_code
34 --
35 --
36
37 -- Bug Fix: 4569365. Removed MRC code.
38 -- g_mrc_exception EXCEPTION;
39
40 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
41 X_Resource_Assignment_Id IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
42 X_Budget_Version_Id NUMBER,
43 X_Project_Id NUMBER,
44 X_Task_Id NUMBER,
45 X_Resource_List_Member_Id NUMBER,
46 X_Description VARCHAR2,
47 X_Start_Date DATE,
48 X_End_Date DATE,
49 X_Period_Name VARCHAR2,
50 X_Quantity IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
51 X_Unit_Of_Measure VARCHAR2,
52 X_Track_As_Labor_Flag VARCHAR2,
53 X_Raw_Cost IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
54 X_Burdened_Cost IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
55 X_Revenue IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
56 X_Change_Reason_Code VARCHAR2,
57 X_Last_Update_Date DATE,
58 X_Last_Updated_By NUMBER,
59 X_Creation_Date DATE,
60 X_Created_By NUMBER,
61 X_Last_Update_Login NUMBER,
62 X_Attribute_Category VARCHAR2,
63 X_Attribute1 VARCHAR2,
64 X_Attribute2 VARCHAR2,
65 X_Attribute3 VARCHAR2,
66 X_Attribute4 VARCHAR2,
67 X_Attribute5 VARCHAR2,
68 X_Attribute6 VARCHAR2,
69 X_Attribute7 VARCHAR2,
70 X_Attribute8 VARCHAR2,
71 X_Attribute9 VARCHAR2,
72 X_Attribute10 VARCHAR2,
73 X_Attribute11 VARCHAR2,
74 X_Attribute12 VARCHAR2,
75 X_Attribute13 VARCHAR2,
76 X_Attribute14 VARCHAR2,
77 X_Attribute15 VARCHAR2,
78 -- Bug Fix: 4569365. Removed MRC code.
79 -- x_mrc_flag VARCHAR2, /* FPB2: Added for MRC 20-Sep */
80 X_Calling_Process VARCHAR2 DEFAULT 'PR',
81 X_Pm_Product_Code VARCHAR2 DEFAULT NULL,
82 X_Pm_Budget_Line_Reference VARCHAR2 DEFAULT NULL,
83 X_raw_cost_source VARCHAR2 DEFAULT 'M',
84 X_burdened_cost_source VARCHAR2 DEFAULT 'M',
85 X_quantity_source VARCHAR2 DEFAULT 'M',
86 X_revenue_source VARCHAR2 DEFAULT 'M',
87 /*New parameters added on 16-mar-2001*/
88 x_standard_bill_rate NUMBER DEFAULT NULL,
89 x_average_bill_rate NUMBER DEFAULT NULL,
90 x_average_cost_rate NUMBER DEFAULT NULL,
91 x_project_assignment_id NUMBER DEFAULT -1,
92 x_plan_error_code VARCHAR2 DEFAULT NULL,
93 x_total_plan_revenue NUMBER DEFAULT NULL,
94 x_total_plan_raw_cost NUMBER DEFAULT NULL,
95 x_total_plan_burdened_cost NUMBER DEFAULT NULL,
96 x_total_plan_quantity NUMBER DEFAULT NULL,
97 x_average_discount_percentage NUMBER DEFAULT NULL,
98 x_cost_rejection_code VARCHAR2 DEFAULT NULL,
99 x_burden_rejection_code VARCHAR2 DEFAULT NULL,
100 x_revenue_rejection_code VARCHAR2 DEFAULT NULL,
101 x_other_rejection_code VARCHAR2 DEFAULT NULL,
102 X_Code_Combination_Id NUMBER DEFAULT NULL,
103 X_CCID_Gen_Status_Code VARCHAR2 DEFAULT NULL,
104 X_CCID_Gen_Rej_Message VARCHAR2 DEFAULT NULL
105 )
106
107 IS
108
109 CURSOR C IS SELECT rowid FROM pa_budget_lines
110 WHERE resource_assignment_id = X_Resource_Assignment_Id
111 AND start_date = X_Start_Date;
112
113 cursor get_budget_type_code is
114 select budget_type_code
115 from pa_budget_versions
116 where budget_version_id = x_budget_version_id;
117
118 same_uom number;
119 p_quantity number;
120 v_budget_type_code varchar2(30);
121 v_budget_amount_code PA_BUDGET_TYPES.BUDGET_AMOUNT_CODE%TYPE;
122
123 l_rows_inserted NUMBER := 0;
124
125
126 l_projfunc_currency_code pa_budget_lines.projfunc_currency_code%TYPE := NULL;
127 l_project_currency_code pa_budget_lines.project_currency_code%TYPE := NULL;
128 l_txn_currency_code pa_budget_lines.txn_currency_code%TYPE := NULL;
129
130 l_Return_Status VARCHAR2(1) :=NULL;
131 l_Msg_Data VARCHAR2(2000) :=NULL;
132 l_Msg_Count NUMBER := 0;
133
134 l_budget_line_id pa_budget_lines.budget_line_id%TYPE;
135
136 nc_Rowid VARCHAR2(30);
137 nc_Resource_Assignment_Id NUMBER;
138 nc_Quantity NUMBER;
139 nc_Raw_Cost NUMBER;
140 nc_Burdened_Cost NUMBER;
141 nc_Revenue NUMBER;
142
143 BEGIN
144
145 nc_Rowid := X_Rowid;
146 nc_Resource_Assignment_Id := X_Resource_Assignment_Id;
147 nc_Quantity := X_Quantity;
148 nc_Raw_Cost := X_Raw_Cost;
149 nc_Burdened_Cost := X_Burdened_Cost;
150 nc_Revenue := X_Revenue;
151
152 -- Bug Fix: 4569365. Removed MRC code.
153 /* MRC */
154 /*
155 IF x_mrc_flag IS NULL THEN
156 l_msg_data := 'x_mrc_flag cannot be null to table handler';
157 RAISE FND_API.G_EXC_ERROR;
158 END IF;
159 */
160
161 open get_budget_type_code;
162 fetch get_budget_type_code into v_budget_type_code;
163 close get_budget_type_code;
164
165 -- 23-APR-98, jwhite ------------------------------------------------------
166 -- Added Begin/End block to capture duplicate rows for
167 -- pa_resource_assignments (removed previous
168 -- where-NOT-Exists code)
169 --
170 /* The following code is modified as first check for the resource_Assignment_id,
171 If not exists then insert the record, by having the code like this the sequence
172 pa_resource_assignments_s will not get incremented unnecessarily 23-MAR-2001 */
173
174 BEGIN
175
176 select resource_assignment_id
177 into x_resource_assignment_id
178 from pa_resource_assignments a
179 where a.budget_version_id = x_budget_version_id
180 and a.project_id = x_project_id
181 and nvl(a.task_id,0) = nvl(x_task_id,0)
182 and a.resource_list_member_id = x_resource_list_member_id
183 and a.project_assignment_id = x_project_assignment_id;
184
185 l_rows_inserted := 0;
186 EXCEPTION
187 WHEN NO_DATA_FOUND THEN
188
189 SELECT pa_resource_assignments_s.nextval
190 INTO X_Resource_Assignment_Id
191 FROM sys.dual;
192
193 -- insert into pa_resource_assignments if necessary
194 insert into pa_resource_assignments(
195 resource_assignment_id,
196 budget_version_id,
197 project_id,
198 task_id,
199 resource_list_member_id,
200 last_update_date,
201 last_updated_by,
202 creation_date,
203 created_by,
204 last_update_login,
205 unit_of_measure,
206 track_as_labor_flag,
207 /*Added 16-mar-2001 by N Gupta*/
208 standard_bill_rate,
209 average_bill_rate,
210 average_cost_rate,
211 project_assignment_id,
212 plan_error_code,
213 total_plan_revenue,
214 total_plan_raw_cost,
215 total_plan_burdened_cost,
216 total_plan_quantity,
217 average_discount_percentage,
218 RESOURCE_ASSIGNMENT_TYPE
219 ) VALUES (
220 X_Resource_Assignment_Id ,
221 x_budget_version_id,
222 x_project_id,
223 x_task_id,
224 x_resource_list_member_id,
225 SYSDATE,
226 x_last_updated_by,
227 SYSDATE,
228 x_created_by,
229 x_last_update_login,
230 x_unit_of_measure,
231 x_track_as_labor_flag,
232 /*Added 16-mar-2001 by N Gupta*/
233 x_standard_bill_rate,
234 x_average_bill_rate,
235 x_average_cost_rate,
236 x_project_assignment_id,
237 x_plan_error_code,
238 x_total_plan_revenue,
239 x_total_plan_raw_cost,
240 x_total_plan_burdened_cost,
241 x_total_plan_quantity,
242 x_average_discount_percentage,
243 'USER_ENTERED'
244 );
245 END;
246
247 -- Get the resource assignment id IF PREVIOUS INSERT was
248 -- NOT performed.
249 /* IF (l_rows_inserted = 0)
250 THEN
251 select resource_assignment_id
252 into x_resource_assignment_id
253 from pa_resource_assignments a
254 where a.budget_version_id = x_budget_version_id
255 and a.project_id = x_project_id
256 and nvl(a.task_id,0) = nvl(x_task_id,0)
257 and a.resource_list_member_id = x_resource_list_member_id
258 and a.project_assignment_id = x_project_assignment_id;
259 END IF; */
260 -- -------------------------------------------------------------
261
262
263
264
265 -- insert into budget lines
266
267 -- Fix for Bugs # 475852 and 503183
268 -- Copy raw cost into burdened cost if budrened cost is null.
269 -- If the resource UOM is currency and raw cost is null then
270 -- copy value of quantity amt into raw cost and also set quantity
271 -- amt to null.
272
273 /* Code modified for budget_amount_code for Forecasting changes 03/23/2001 */
274 v_budget_amount_code := pa_budget_utils.get_budget_amount_code(v_budget_type_code);
275 if v_budget_amount_code = 'C' then
276 -- Cost Budget
277 if pa_budget_utils.check_currency_uom(x_unit_of_measure) = 'Y' then
278 if x_raw_cost is null then
279 x_raw_cost := x_quantity;
280 end if;
281 x_quantity := null;
282 end if;
283
284 if x_burdened_cost is null then
285 x_burdened_cost := x_raw_cost;
286 end if;
287
288 elsif v_budget_amount_code = 'R' then -- Revenue Budget
289 if pa_budget_utils.check_currency_uom(x_unit_of_measure) = 'Y' then
290 if x_revenue is null then
291 x_revenue := x_quantity;
292 end if;
293 x_quantity := null;
294 end if;
295 end if;
296
297
298
299 -- Get Project Currency Information for INSERT
300 -- Note: This procedure uses package globals to effect one fecth per x_project_id.
301
302 PA_BUDGET_UTILS.Get_Project_Currency_Info
303 (
304 p_project_id => x_project_id
305 , x_projfunc_currency_code => l_projfunc_currency_code
306 , x_project_currency_code => l_project_currency_code
307 , x_txn_currency_code => l_txn_currency_code
308 , x_msg_count => l_msg_count
309 , x_msg_data => l_msg_data
310 , x_return_status => l_return_status
311 );
312
313 -- This table handler can't really handle public API error handling, but I will put it
314 -- in any way with minimal exception coding and a RAISE.
315
316 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR)
317 THEN
318 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
319
320 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR)
321 THEN
322 RAISE FND_API.G_EXC_ERROR;
323 END IF;
324
325 SELECT pa_budget_lines_s.nextval
326 INTO l_budget_line_id
327 FROM DUAL;
328
329 INSERT INTO pa_budget_lines(
330 budget_line_id, /* FPB2 */
331 budget_version_id, /* FPB2 */
332 resource_assignment_id,
333 start_date,
334 last_update_date,
335 last_updated_by,
336 creation_date,
337 created_by,
338 last_update_login,
339 end_date,
340 period_name,
341 quantity,
342 raw_cost,
343 burdened_cost,
344 revenue,
345 change_reason_code,
346 description,
347 attribute_category,
348 attribute1,
349 attribute2,
350 attribute3,
351 attribute4,
352 attribute5,
353 attribute6,
354 attribute7,
355 attribute8,
356 attribute9,
357 attribute10,
358 attribute11,
359 attribute12,
360 attribute13,
361 attribute14,
362 attribute15,
363 pm_product_code,
367 quantity_source,
364 pm_budget_line_reference,
365 raw_cost_source,
366 burdened_cost_source,
368 revenue_source,
369 /*Added 16-mar-2001 By N gupta*/
370 COST_REJECTION_CODE,
371 BURDEN_REJECTION_CODE,
372 REVENUE_REJECTION_CODE,
373 OTHER_REJECTION_CODE,
374 Code_Combination_Id,
375 CCID_Gen_Status_Code,
376 CCID_Gen_Rej_Message,
377 projfunc_currency_code,
378 project_currency_code,
379 txn_currency_code
380 ) VALUES (
381 l_budget_line_id, /* FPB2 */
382 x_budget_version_id, /* FPB2 */
383 X_Resource_Assignment_Id,
384 X_Start_Date,
385 X_Last_Update_Date,
386 X_Last_Updated_By,
387 X_Creation_Date,
388 X_Created_By,
389 X_Last_Update_Login,
390 X_End_Date,
391 X_Period_Name,
392 (X_Quantity),
393 pa_currency.round_currency_amt(X_Raw_Cost),
394 pa_currency.round_currency_amt(X_Burdened_Cost),
395 pa_currency.round_currency_amt(X_Revenue),
396 X_Change_Reason_Code,
397 X_Description,
398 X_Attribute_Category,
399 X_Attribute1,
400 X_Attribute2,
401 X_Attribute3,
402 X_Attribute4,
403 X_Attribute5,
404 X_Attribute6,
405 X_Attribute7,
406 X_Attribute8,
407 X_Attribute9,
408 X_Attribute10,
409 X_Attribute11,
410 X_Attribute12,
411 X_Attribute13,
412 X_Attribute14,
413 X_Attribute15,
414 X_Pm_Product_Code,
415 X_Pm_Budget_Line_Reference,
416 X_raw_cost_source,
417 X_burdened_cost_source,
418 X_quantity_source ,
419 X_revenue_source ,
420 /*Added 16-mar-2001 By N gupta*/
421 x_cost_rejection_code,
422 x_burden_rejection_code,
423 x_revenue_rejection_code,
424 x_other_rejection_code,
425 X_Code_Combination_Id,
426 X_CCID_Gen_Status_Code,
427 X_CCID_Gen_Rej_Message,
428 l_projfunc_currency_code,
429 l_project_currency_code,
430 l_txn_currency_code
431 );
432
433
434 -- Bug Fix: 4569365. Removed MRC code.
435 /* FPB2: MRC */
436 /*
437 IF x_mrc_flag = 'Y' THEN
438
439 IF PA_MRC_FINPLAN.G_MRC_ENABLED_FOR_BUDGETS IS NULL THEN
440 PA_MRC_FINPLAN.CHECK_MRC_INSTALL
441 (x_return_status => l_return_status,
442 x_msg_count => l_msg_count,
443 x_msg_data => l_msg_data);
444 END IF;
445
446 IF PA_MRC_FINPLAN.G_MRC_ENABLED_FOR_BUDGETS AND
447 PA_MRC_FINPLAN.G_FINPLAN_MRC_OPTION_CODE = 'A' THEN
448 PA_MRC_FINPLAN.MAINTAIN_ONE_MC_BUDGET_LINE
449 (p_budget_line_id => l_budget_line_id,
450 p_budget_version_id => x_budget_version_id,
451 p_action => PA_MRC_FINPLAN.G_ACTION_INSERT,
452 x_return_status => l_return_status,
453 x_msg_count => l_msg_count,
454 x_msg_data => l_msg_data);
455 END IF;
456
457 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
458 RAISE g_mrc_exception;
459 END IF;
460
461 END IF;
462 */
463
464 -- update pa_budget_versions
465 -- Update pa_budget_versions only if the denormalized totals are
466 -- not being maintained in the form. Example the Copy Actual
467 -- process.
468
469 if X_Calling_Process = 'PR' then
470 update pa_budget_versions
471 set raw_cost = pa_currency.round_currency_amt(nvl(raw_cost,0) + nvl(x_raw_cost,0)),
472 burdened_cost = pa_currency.round_currency_amt(nvl(burdened_cost,0) + nvl(x_burdened_cost,0) ),
473 revenue = pa_currency.round_currency_amt(nvl(revenue,0) + nvl(x_revenue,0) ),
474 labor_quantity =
475 (to_number(decode(x_track_as_labor_flag,
476 'Y', nvl(labor_quantity,0) + nvl(x_quantity,0),
477 nvl(labor_quantity,0)))),
478 labor_unit_of_measure =
479 decode(x_track_as_labor_flag, 'Y', x_unit_of_measure,
480 labor_unit_of_measure),
481 last_update_date = x_last_update_date,
485 end if;
482 last_update_login = x_last_update_login,
483 last_updated_by = x_last_updated_by
484 where budget_version_id = x_budget_version_id;
486
487 OPEN C;
488 FETCH C INTO X_Rowid;
489 if (C%NOTFOUND) then
490 CLOSE C;
491 Raise NO_DATA_FOUND;
492 end if;
493 CLOSE C;
494
495
496 -- This EXCEPTION is coded here for the PA_BUDGET_UTILS.Get_Project_Currency_Info call.
497 -- Since the table handler was not designed for this kind of thing, the public API
498 -- error-handling OUT-parameters are not populated.
499
500 EXCEPTION
501 WHEN FND_API.G_EXC_ERROR
502 THEN
503 FND_MSG_PUB.Add_Exc_Msg
504 ( p_pkg_name => 'PA_BUDGET_LINES_V_PKG'
505 , p_procedure_name => 'INSERT_ROW'
506 , p_error_text => l_msg_data || ' ORA-'||LPAD(substr(SQLCODE,2),5,'0')
507 );
508 IF c%ISOPEN THEN /* Bug# 2628072 */
509 CLOSE C;
510 END IF;
511 RAISE;
512 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
513 THEN
514 FND_MSG_PUB.Add_Exc_Msg
515 ( p_pkg_name => 'PA_BUDGET_LINES_V_PKG'
516 , p_procedure_name => 'INSERT_ROW'
517 , p_error_text => 'ORA-'||LPAD(substr(SQLCODE,2),5,'0')
518 );
519 IF c%ISOPEN THEN /* Bug# 2628072 */
520 CLOSE C;
521 END IF;
522 RAISE;
523 WHEN OTHERS
524 THEN
525 nc_Rowid := X_Rowid;
526 nc_Resource_Assignment_Id := X_Resource_Assignment_Id;
527 nc_Quantity := X_Quantity;
528 nc_Raw_Cost := X_Raw_Cost;
529 nc_Burdened_Cost := X_Burdened_Cost;
530 nc_Revenue := X_Revenue;
531 RAISE;
532
533 END Insert_Row;
534
535
536 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
537 X_Resource_Assignment_Id NUMBER,
538 X_Budget_Version_Id NUMBER,
539 X_Project_Id NUMBER,
540 X_Task_Id NUMBER,
541 X_Resource_List_Member_Id NUMBER,
542 X_Description VARCHAR2,
543 X_Start_Date DATE,
544 X_End_Date DATE,
545 X_Period_Name VARCHAR2,
546 X_Quantity NUMBER,
547 X_Unit_Of_Measure VARCHAR2,
548 X_Track_As_Labor_Flag VARCHAR2,
549 X_Raw_Cost NUMBER,
550 X_Burdened_Cost NUMBER,
551 X_Revenue NUMBER,
552 X_Change_Reason_Code VARCHAR2,
553 X_Attribute_Category VARCHAR2,
554 X_Attribute1 VARCHAR2,
555 X_Attribute2 VARCHAR2,
556 X_Attribute3 VARCHAR2,
557 X_Attribute4 VARCHAR2,
558 X_Attribute5 VARCHAR2,
559 X_Attribute6 VARCHAR2,
560 X_Attribute7 VARCHAR2,
561 X_Attribute8 VARCHAR2,
562 X_Attribute9 VARCHAR2,
563 X_Attribute10 VARCHAR2,
564 X_Attribute11 VARCHAR2,
565 X_Attribute12 VARCHAR2,
566 X_Attribute13 VARCHAR2,
567 X_Attribute14 VARCHAR2,
568 X_Attribute15 VARCHAR2
569 ) IS
570 CURSOR C IS
571 SELECT l.resource_assignment_id,
572 l.start_date,
573 l.end_date,
574 l.period_name,
575 l.quantity,
576 l.raw_cost,
577 l.burdened_cost,
578 l.revenue,
579 l.change_reason_code,
580 l.description,
581 l.attribute_category,
582 l.attribute1,
583 l.attribute2,
584 l.attribute3,
585 l.attribute4,
586 l.attribute5,
587 l.attribute6,
588 l.attribute7,
589 l.attribute8,
590 l.attribute9,
591 l.attribute10,
592 l.attribute11,
593 l.attribute12,
594 l.attribute13,
595 l.attribute14,
596 l.attribute15,
597 a.budget_version_id,
598 a.project_id,
599 a.task_id,
600 a.resource_list_member_id,
601 a.unit_of_measure,
602 a.track_as_labor_flag
603 FROM pa_resource_assignments a,
604 pa_budget_lines l
605 WHERE l.rowid = X_Rowid
609
606 AND l.resource_assignment_id = a.resource_assignment_id
607 FOR UPDATE NOWAIT;
608 Recinfo C%ROWTYPE;
610
611 BEGIN
612
613 OPEN C;
614 FETCH C INTO Recinfo;
615 if (C%NOTFOUND) then
616 CLOSE C;
617 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
618 APP_EXCEPTION.Raise_Exception;
619 end if;
620 CLOSE C;
621
622 if (
623 (Recinfo.resource_assignment_id = X_Resource_Assignment_Id)
624 AND (Recinfo.budget_version_id = X_Budget_Version_Id)
625 AND (Recinfo.project_id = X_Project_Id)
626 AND ( (Recinfo.task_id = X_task_Id)
627 OR ( (Recinfo.task_id IS NULL)
628 AND (X_Task_Id IS NULL)))
629 AND (Recinfo.resource_list_member_id = X_Resource_List_Member_Id)
630 AND ( (Recinfo.description = X_Description)
631 OR ( (Recinfo.description IS NULL)
632 AND (X_Description IS NULL)))
633 AND (Recinfo.start_date = X_Start_Date)
634 AND (Recinfo.end_date = X_End_Date)
635 AND ( (Recinfo.period_name = X_Period_Name)
636 OR ( (Recinfo.period_name IS NULL)
637 AND (X_Period_Name IS NULL)))
638 AND ( (Recinfo.quantity = X_Quantity)
639 OR ( (Recinfo.quantity IS NULL)
640 AND (X_Quantity IS NULL)))
641 AND ( (Recinfo.unit_of_measure = X_Unit_Of_Measure)
642 OR ( (Recinfo.unit_of_measure IS NULL)
643 AND (X_Unit_Of_Measure IS NULL)))
644 AND ( (Recinfo.track_as_labor_flag = X_track_as_labor_flag)
645 OR ( (Recinfo.track_as_labor_flag IS NULL)
646 AND (X_track_as_labor_flag IS NULL)))
647 AND ( (Recinfo.raw_cost = X_Raw_Cost)
648 OR ( (Recinfo.raw_cost IS NULL)
649 AND (X_Raw_Cost IS NULL)))
650 AND ( (Recinfo.burdened_cost = X_Burdened_Cost)
651 OR ( (Recinfo.burdened_cost IS NULL)
652 AND (X_Burdened_Cost IS NULL)))
653 AND ( (Recinfo.revenue = X_Revenue)
654 OR ( (Recinfo.revenue IS NULL)
655 AND (X_Revenue IS NULL)))
656 AND ( (Recinfo.change_reason_code = X_Change_Reason_Code)
657 OR ( (Recinfo.change_reason_code IS NULL)
658 AND (X_Change_Reason_Code IS NULL)))
659 AND ( (Recinfo.attribute_category = X_Attribute_Category)
660 OR ( (Recinfo.attribute_category IS NULL)
661 AND (X_Attribute_Category IS NULL)))
662 AND ( (Recinfo.attribute1 = X_Attribute1)
663 OR ( (Recinfo.attribute1 IS NULL)
664 AND (X_Attribute1 IS NULL)))
665 AND ( (Recinfo.attribute2 = X_Attribute2)
666 OR ( (Recinfo.attribute2 IS NULL)
667 AND (X_Attribute2 IS NULL)))
668 AND ( (Recinfo.attribute3 = X_Attribute3)
669 OR ( (Recinfo.attribute3 IS NULL)
670 AND (X_Attribute3 IS NULL)))
671 AND ( (Recinfo.attribute4 = X_Attribute4)
672 OR ( (Recinfo.attribute4 IS NULL)
673 AND (X_Attribute4 IS NULL)))
674 AND ( (Recinfo.attribute5 = X_Attribute5)
675 OR ( (Recinfo.attribute5 IS NULL)
676 AND (X_Attribute5 IS NULL)))
677 AND ( (Recinfo.attribute6 = X_Attribute6)
678 OR ( (Recinfo.attribute6 IS NULL)
679 AND (X_Attribute6 IS NULL)))
680 AND ( (Recinfo.attribute7 = X_Attribute7)
681 OR ( (Recinfo.attribute7 IS NULL)
682 AND (X_Attribute7 IS NULL)))
683 AND ( (Recinfo.attribute8 = X_Attribute8)
684 OR ( (Recinfo.attribute8 IS NULL)
685 AND (X_Attribute8 IS NULL)))
686 AND ( (Recinfo.attribute9 = X_Attribute9)
687 OR ( (Recinfo.attribute9 IS NULL)
688 AND (X_Attribute9 IS NULL)))
689 AND ( (Recinfo.attribute10 = X_Attribute10)
690 OR ( (Recinfo.attribute10 IS NULL)
691 AND (X_Attribute10 IS NULL)))
692 AND ( (Recinfo.attribute11 = X_Attribute11)
693 OR ( (Recinfo.attribute11 IS NULL)
694 AND (X_Attribute11 IS NULL)))
695 AND ( (Recinfo.attribute12 = X_Attribute12)
696 OR ( (Recinfo.attribute12 IS NULL)
697 AND (X_Attribute12 IS NULL)))
698 AND ( (Recinfo.attribute13 = X_Attribute13)
699 OR ( (Recinfo.attribute13 IS NULL)
700 AND (X_Attribute13 IS NULL)))
701 AND ( (Recinfo.attribute14 = X_Attribute14)
702 OR ( (Recinfo.attribute14 IS NULL)
703 AND (X_Attribute14 IS NULL)))
704 AND ( (Recinfo.attribute15 = X_Attribute15)
705 OR ( (Recinfo.attribute15 IS NULL)
706 AND (X_Attribute15 IS NULL)))
707 ) then
708 return;
709 else
710 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
711 APP_EXCEPTION.Raise_Exception;
712 end if;
713 END Lock_Row;
714
715 --Name: Update_Row
719 --Called Subprograms: none.
716 --Type: Procedure
717 --Description:
718 --
720 --
721 --Notes
722 -- !!! This procedure can only be used with r11.5.7 Budgets. !!!
723 --
724 -- For similar functionality for FP plan types, FP specific procedures
725 -- must be called.
726 --
727 -- PA_BUDGET_UTILS.Get_Project_Currency_Info uses package
728 -- globals to optimize peformance. It also uses x_project_id
729 -- to determine when the globals should be refreshed.
730 --
731 --History
732 -- xx-xxx-xxxx who? - Created
733 --
734 -- 16-AUG-02 jwhite As per the FP model conversion effort, did the following:
735 --
736 -- 1) Added a call to PA_BUDGET_UTILS.Get_Project_Currency_Info
737 -- to populate the following new currency columns:
738 -- - projfunc_currency_code
739 -- - project_currency_code
740 -- - txn_currency_code
741 --
742 -- 17-SEP-02 jwhite Removed unnecessary update of currency info. Of course, when
743 -- a budget line is deleted and then re-inserted, the Insert procedure
744 -- will populate the currency code columns.
745 --
746 --
747
748 PROCEDURE Update_Row(X_Rowid VARCHAR2,
749 X_Resource_Assignment_Id NUMBER,
750 X_Budget_Version_Id NUMBER,
751 X_Project_Id NUMBER,
752 X_Task_Id NUMBER,
753 X_Resource_List_Member_Id NUMBER,
754 X_Resource_Id NUMBER,
755 X_Resource_Id_Old NUMBER,
756 X_Description VARCHAR2,
757 X_Start_Date DATE,
758 X_End_Date DATE,
759 X_Period_Name VARCHAR2,
760 X_Quantity IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
761 X_Quantity_Old IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
762 X_Unit_Of_Measure VARCHAR2,
763 X_Track_As_Labor_Flag VARCHAR2,
764 X_Raw_Cost IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
765 X_Raw_Cost_Old IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
766 X_Burdened_Cost IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
767 X_Burdened_Cost_Old IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
768 X_Revenue IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
769 X_Revenue_Old IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
770 X_Change_Reason_Code VARCHAR2,
771 X_Last_Update_Date DATE,
772 X_Last_Updated_By NUMBER,
773 X_Last_Update_Login NUMBER,
774 X_Attribute_Category VARCHAR2,
775 X_Attribute1 VARCHAR2,
776 X_Attribute2 VARCHAR2,
777 X_Attribute3 VARCHAR2,
778 X_Attribute4 VARCHAR2,
779 X_Attribute5 VARCHAR2,
780 X_Attribute6 VARCHAR2,
781 X_Attribute7 VARCHAR2,
782 X_Attribute8 VARCHAR2,
783 X_Attribute9 VARCHAR2,
784 X_Attribute10 VARCHAR2,
785 X_Attribute11 VARCHAR2,
786 X_Attribute12 VARCHAR2,
787 X_Attribute13 VARCHAR2,
788 X_Attribute14 VARCHAR2,
789 X_Attribute15 VARCHAR2,
790 -- Bug Fix: 4569365. Removed MRC code.
791 -- X_MRC_Flag VARCHAR2, /* FPB2: Added for MRC */
792 X_Calling_Process VARCHAR2 DEFAULT 'PR',
793 X_raw_cost_source VARCHAR2 DEFAULT 'M',
794 X_burdened_cost_source VARCHAR2 DEFAULT 'M',
795 X_quantity_source VARCHAR2 DEFAULT 'M',
796 X_revenue_source VARCHAR2 DEFAULT 'M',
797 /*Added following 13 columns on 16-mar-2001*/
798 x_standard_bill_rate NUMBER DEFAULT NULL,
799 x_average_bill_rate NUMBER DEFAULT NULL,
800 x_average_cost_rate NUMBER DEFAULT NULL,
801 x_project_assignment_id NUMBER DEFAULT NULL,
802 x_plan_error_code VARCHAR2 DEFAULT NULL,
803 x_total_plan_revenue NUMBER DEFAULT NULL,
804 x_total_plan_raw_cost NUMBER DEFAULT NULL,
808 x_cost_rejection_code VARCHAR2 DEFAULT NULL,
805 x_total_plan_burdened_cost NUMBER DEFAULT NULL,
806 x_total_plan_quantity NUMBER DEFAULT NULL,
807 x_average_discount_percentage NUMBER DEFAULT NULL,
809 x_burden_rejection_code VARCHAR2 DEFAULT NULL,
810 x_revenue_rejection_code VARCHAR2 DEFAULT NULL,
811 x_other_rejection_code VARCHAR2 DEFAULT NULL,
812 X_Code_Combination_Id NUMBER DEFAULT NULL,
813 X_CCID_Gen_Status_Code VARCHAR2 DEFAULT NULL,
814 X_CCID_Gen_Rej_Message VARCHAR2 DEFAULT NULL
815 ) IS
816 created_by number;
817 last_updated_by number;
818 last_update_login number;
819 res_assignment_id number;
820 new_rowid varchar2(18);
821 v_budget_type_code varchar2(30);
822
823 cursor get_budget_type_code is
824 select budget_type_code
825 from pa_budget_versions
826 where budget_version_id = x_budget_version_id;
827
828
829 l_Return_Status VARCHAR2(1) :=NULL;
830 l_Msg_Data VARCHAR2(2000) :=NULL;
831 l_Msg_Count NUMBER := 0;
832
833 l_budget_line_id PA_BUDGET_LINES.BUDGET_LINE_ID%TYPE; /* FPB2 */
834
835 BEGIN
836 -- Bug Fix: 4569365. Removed MRC code.
837 /* FPB2: MRC */
838 /*
839 IF x_mrc_flag IS NULL THEN
840 l_msg_data := 'x_mrc_flag cannot be null to table handler';
841 RAISE FND_API.G_EXC_ERROR;
842 END IF;
843 */
844
845 open get_budget_type_code;
846 fetch get_budget_type_code into v_budget_type_code;
847 close get_budget_type_code;
848
849 created_by := fnd_global.user_id;
850 last_updated_by := fnd_global.user_id;
851 last_update_login := fnd_global.login_id;
852
853 if (x_resource_id <> x_resource_id_old) then
854 -- delete the orignial one, and insert a new one
855 pa_budget_lines_v_pkg.delete_row(x_rowid => x_rowid,
856 x_calling_process => x_calling_process
857 --,
858 -- Bug Fix: 4569365. Removed MRC code.
859 -- x_mrc_flag => x_mrc_flag /* FPB2: for MRC */
860 );
861
862 pa_budget_lines_v_pkg.insert_row(
863 X_Rowid => new_rowid,
864 X_Resource_Assignment_Id => res_assignment_id,
865 X_Budget_Version_Id => X_Budget_Version_Id,
866 X_Project_Id => X_Project_Id,
867 X_Task_Id => X_Task_Id,
868 X_Resource_List_Member_Id => X_Resource_List_Member_Id,
869 X_Description => X_Description,
870 X_Start_Date => X_Start_Date,
871 X_End_Date => X_End_Date,
872 X_Period_Name => X_Period_Name,
873 X_Quantity => X_Quantity,
874 X_Unit_Of_Measure => X_Unit_Of_Measure,
875 X_Track_As_Labor_Flag => X_Track_As_Labor_Flag,
876 X_Raw_Cost => X_Raw_Cost,
877 X_Burdened_Cost => X_Burdened_Cost,
878 X_Revenue => X_Revenue,
879 X_Change_Reason_Code => X_Change_Reason_Code,
880 x_last_update_date => SYSDATE,
881 X_Last_Updated_by => Last_Updated_By,
882 x_creation_date => SYSDATE,
883 X_Created_By => Created_By,
884 X_Last_Update_Login => Last_Update_Login,
885 X_Attribute_Category => X_Attribute_Category,
886 X_Attribute1 => X_Attribute1,
887 X_Attribute2 => X_Attribute2,
888 X_Attribute3 => X_Attribute3,
889 X_Attribute4 => X_Attribute4,
890 X_Attribute5 => X_Attribute5,
891 X_Attribute6 => X_Attribute6,
892 X_Attribute7 => X_Attribute7,
893 X_Attribute8 => X_Attribute8,
894 X_Attribute9 => X_Attribute9,
895 X_Attribute10 => X_Attribute10,
896 X_Attribute11 => X_Attribute11,
897 X_Attribute12 => X_Attribute12,
898 X_Attribute13 => X_Attribute13,
899 X_Attribute14 => X_Attribute14,
900 X_Attribute15 => X_Attribute15,
901 X_Calling_Process => X_Calling_Process,
902 X_raw_cost_source => X_raw_cost_source,
903 X_burdened_cost_source => X_burdened_cost_source,
904 X_quantity_source => X_quantity_source ,
905 X_revenue_source => X_revenue_source,
906 X_Code_Combination_Id => X_Code_Combination_Id,
907 X_CCID_Gen_Status_Code => X_CCID_Gen_Status_Code,
908 X_CCID_Gen_Rej_Message => X_CCID_Gen_Rej_Message
909 --,
910 -- Bug Fix: 4569365. Removed MRC code.
914 else
911 -- X_mrc_flag => X_mrc_flag /* FPB2: Added x_mrc_flag for MRC changes. Pass same as input */
912 );
913
915 -- resource is not changed
916
917 -- Fix for Bugs # 475852 and 503183
918 -- Copy raw cost into burdened cost if budrened cost is null.
919 -- If the resource UOM is currency and raw cost is null then
920 -- copy value of quantity amt into raw cost and also set quantity
921 -- amt to null.
922
923 if pa_budget_utils.get_budget_amount_code(v_budget_type_code) = 'C' then
924 -- Cost Budget
925
926 if pa_budget_utils.check_currency_uom(x_unit_of_measure) = 'Y' then
927 if x_raw_cost is null then
928 x_raw_cost := x_quantity;
929 end if;
930 x_quantity := null;
931 end if;
932
933 if x_burdened_cost is null then
934 x_burdened_cost := x_raw_cost;
935 end if;
936
937 else -- Revenue Budget
938 if pa_budget_utils.check_currency_uom(x_unit_of_measure) = 'Y' then
939 if x_revenue is null then
940 x_revenue := x_quantity;
941 end if;
942 x_quantity := null;
943 end if;
944 end if;
945
946
947
948 UPDATE pa_budget_lines
949 SET
950 resource_assignment_id = X_Resource_Assignment_Id,
951 start_date = X_Start_Date,
952 last_update_date = X_Last_Update_Date,
953 last_updated_by = X_Last_Updated_By,
954 last_update_login = X_Last_Update_Login,
955 end_date = X_End_Date,
956 period_name = X_Period_Name,
957 quantity = (X_Quantity),
958 raw_cost = pa_currency.round_currency_amt(X_Raw_Cost),
959 burdened_cost = pa_currency.round_currency_amt(X_Burdened_Cost),
960 revenue = pa_currency.round_currency_amt(X_Revenue),
961 change_reason_code = X_Change_Reason_Code,
962 description = X_Description,
963 attribute_category = X_Attribute_Category,
964 attribute1 = X_Attribute1,
965 attribute2 = X_Attribute2,
966 attribute3 = X_Attribute3,
967 attribute4 = X_Attribute4,
968 attribute5 = X_Attribute5,
969 attribute6 = X_Attribute6,
970 attribute7 = X_Attribute7,
971 attribute8 = X_Attribute8,
972 attribute9 = X_Attribute9,
973 attribute10 = X_Attribute10,
974 attribute11 = X_Attribute11,
975 attribute12 = X_Attribute12,
976 attribute13 = X_Attribute13,
977 attribute14 = X_Attribute14,
978 attribute15 = X_Attribute15,
979 raw_cost_source = X_raw_cost_source,
980 burdened_cost_source = X_burdened_cost_source,
981 quantity_source = X_quantity_source,
982 revenue_source = X_revenue_source,
983 /*added 16-mar-2001 by N Gupta*/
984 COST_REJECTION_CODE = DECODE(X_COST_REJECTION_CODE, NULL, COST_REJECTION_CODE, X_COST_REJECTION_CODE),
985 BURDEN_REJECTION_CODE= DECODE(X_BURDEN_REJECTION_CODE, NULL, BURDEN_REJECTION_CODE, X_BURDEN_REJECTION_CODE),
986 REVENUE_REJECTION_CODE=DECODE(X_REVENUE_REJECTION_CODE, NULL, REVENUE_REJECTION_CODE, X_REVENUE_REJECTION_CODE),
987 OTHER_REJECTION_CODE=DECODE(X_OTHER_REJECTION_CODE, NULL, OTHER_REJECTION_CODE, X_OTHER_REJECTION_CODE),
988 Code_Combination_Id = X_Code_Combination_Id,
989 CCID_Gen_Status_Code = X_CCID_Gen_Status_Code,
990 CCID_Gen_Rej_Message = X_CCID_Gen_Rej_Message
991 WHERE rowid = X_Rowid
992 RETURNING budget_line_id INTO l_budget_line_id;
993
994 -- update the project budget
995
996 -- Bug Fix: 4569365. Removed MRC code.
997 /* FPB2: MRC */
998 /*
999 IF x_mrc_flag = 'Y' THEN
1000 IF PA_MRC_FINPLAN.G_MRC_ENABLED_FOR_BUDGETS IS NULL THEN
1001 PA_MRC_FINPLAN.CHECK_MRC_INSTALL
1002 (x_return_status => l_return_status,
1003 x_msg_count => l_msg_count,
1004 x_msg_data => l_msg_data);
1005 END IF;
1006
1007 IF PA_MRC_FINPLAN.G_MRC_ENABLED_FOR_BUDGETS AND
1008 PA_MRC_FINPLAN.G_FINPLAN_MRC_OPTION_CODE = 'A' THEN
1009 PA_MRC_FINPLAN.MAINTAIN_ONE_MC_BUDGET_LINE
1010 (p_budget_line_id => l_budget_line_id,
1011 p_budget_version_id => x_budget_version_id,
1012 p_action => PA_MRC_FINPLAN.G_ACTION_UPDATE,
1013 x_return_status => l_return_status,
1014 x_msg_count => l_msg_count,
1018 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1015 x_msg_data => l_msg_data);
1016 END IF;
1017
1019 RAISE g_mrc_exception;
1020 END IF;
1021
1022 END IF;
1023 */
1024
1025 if ( (nvl(x_quantity,0) <> nvl(x_quantity_old,0))
1026 or (nvl(x_raw_cost,0) <> nvl(x_raw_cost_old,0))
1027 or (nvl(x_burdened_cost,0) <> nvl(x_burdened_cost_old,0))
1028 or (nvl(x_revenue,0) <> nvl(x_revenue_old,0))) then
1029
1030 -- Update pa_budget_versions only if the denormalized totals are
1031 -- not being maintained in the form. Example the Copy Actual
1032 -- process.
1033
1034 if X_Calling_Process = 'PR' then
1035 update pa_budget_versions
1036 set labor_quantity = (to_number(
1037 decode(x_track_as_labor_flag,
1038 'Y', nvl(labor_quantity,0)
1039 - nvl(x_quantity_old,0)
1040 + nvl(x_quantity,0),
1041 nvl(labor_quantity,0))) ),
1042 raw_cost = pa_currency.round_currency_amt(nvl(raw_cost,0) - nvl(x_raw_cost_old,0)
1043 + nvl(x_raw_cost,0) ),
1044 burdened_cost = pa_currency.round_currency_amt(nvl(burdened_cost,0)
1045 - nvl(x_burdened_cost_old,0)
1046 + nvl(x_burdened_cost,0) ),
1047 revenue = pa_currency.round_currency_amt(nvl(revenue,0) - nvl(x_revenue_old,0)
1048 + nvl(x_revenue,0) )
1049 where budget_version_id = x_budget_version_id;
1050
1051 if (SQL%NOTFOUND) then
1052 Raise NO_DATA_FOUND;
1053 end if;
1054 end if;
1055
1056 end if;
1057
1058 end if;
1059
1060 -- This EXCEPTION is coded here for the Insert Procedure call.
1061 -- Since the table handler was not designed for this kind of thing, the public API
1062 -- error-handling OUT-parameters are not populated.
1063
1064 EXCEPTION
1065 WHEN FND_API.G_EXC_ERROR
1066 THEN
1067 FND_MSG_PUB.Add_Exc_Msg
1068 ( p_pkg_name => 'PA_BUDGET_LINES_V_PKG'
1069 , p_procedure_name => 'UPDATE_ROW'
1070 , p_error_text => 'ORA-'||LPAD(substr(SQLCODE,2),5,'0')
1071 );
1072 RAISE;
1073 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1074 THEN
1075 FND_MSG_PUB.Add_Exc_Msg
1076 ( p_pkg_name => 'PA_BUDGET_LINES_V_PKG'
1077 , p_procedure_name => 'UPDATE_ROW'
1078 , p_error_text => 'ORA-'||LPAD(substr(SQLCODE,2),5,'0')
1079 );
1080 RAISE;
1081
1082
1083 END Update_Row;
1084
1085
1086 PROCEDURE Delete_Row(X_Rowid VARCHAR2,
1087 -- Bug Fix: 4569365. Removed MRC code.
1088 -- X_mrc_flag VARCHAR2 ,
1089 X_Calling_Process VARCHAR2 DEFAULT 'PR'
1090 ) IS
1091
1092 x_raw_cost number;
1093 x_burdened_cost number;
1094 x_revenue number;
1095 x_quantity number;
1096 x_resource_assignment_id number;
1097 x_track_as_labor_flag varchar2(2);
1098 x_budget_version_id number;
1099 x_last_updated_by number;
1100 x_last_update_login number;
1101
1102 l_Return_Status VARCHAR2(1) :=NULL;
1103 l_Msg_Data VARCHAR2(2000) :=NULL;
1104 l_Msg_Count NUMBER := 0;
1105 -- Bug Fix: 4569365. Removed MRC code.
1106 -- l_budget_line_id PA_BUDGET_LINES.BUDGET_LINE_ID%TYPE; /* MRC */
1107
1108
1109 BEGIN
1110 -- Bug Fix: 4569365. Removed MRC code.
1111 /* FPB2: MRC */
1112 /*
1113 IF x_mrc_flag IS NULL THEN
1114 l_msg_data := 'x_mrc_flag cannot be null to table handler';
1115 RAISE FND_API.G_EXC_ERROR;
1116 END IF;
1117 */
1118 select l.raw_cost,
1119 l.burdened_cost,
1120 l.revenue,
1121 l.quantity,
1122 l.resource_assignment_id,
1123 a.track_as_labor_flag
1124 into x_raw_cost,
1125 x_burdened_cost,
1126 x_revenue,
1127 x_quantity,
1128 x_resource_assignment_id,
1129 x_track_as_labor_flag
1130 from pa_resource_assignments a,
1131 pa_budget_lines l
1132 where l.rowid = X_Rowid
1133 and l.resource_assignment_id = a.resource_assignment_id;
1134
1135 DELETE FROM pa_budget_lines
1136 WHERE rowid = X_Rowid;
1137 -- Bug Fix: 4569365. Removed MRC code.
1138 -- RETURNING budget_line_id INTO l_budget_line_id ; /* FPB2 */
1139
1140 x_last_updated_by := fnd_global.user_id;
1141 x_last_update_login := fnd_global.login_id;
1142
1143 select budget_version_id
1144 into x_budget_version_id
1145 from pa_resource_assignments
1146 where resource_assignment_id = x_resource_assignment_id;
1147
1148 -- clean up pa_resource_assignments if necessary
1149 delete pa_resource_assignments
1150 where resource_assignment_id = x_resource_assignment_id
1151 and not exists
1152 (select 1
1153 from pa_budget_lines
1154 where resource_assignment_id = x_resource_assignment_id);
1155
1156 -- Update pa_budget_versions only if the denormalized totals are
1157 -- not being maintained in the form. Example the Copy Actual
1158 -- process.
1159
1160 if X_Calling_Process = 'PR' then
1161 update pa_budget_versions
1162 set raw_cost = pa_currency.round_currency_amt(nvl(raw_cost,0) - nvl(x_raw_cost,0) ),
1163 burdened_cost = pa_currency.round_currency_amt(nvl(burdened_cost,0) - nvl(x_burdened_cost,0) ),
1164 revenue = pa_currency.round_currency_amt(nvl(revenue,0) - nvl(x_revenue,0) ),
1165 labor_quantity = (to_number(
1166 decode(x_track_as_labor_flag,
1167 'Y', nvl(labor_quantity,0) - nvl(x_quantity,0),
1168 nvl(labor_quantity,0))) ),
1169 last_update_date = SYSDATE,
1170 last_update_login = x_last_update_login,
1171 last_updated_by = x_last_updated_by
1172 where budget_version_id = x_budget_version_id;
1173
1174 if (SQL%NOTFOUND) then
1175 Raise NO_DATA_FOUND;
1176 end if;
1177 end if;
1178
1179 -- Bug Fix: 4569365. Removed MRC code.
1180 /* MRC */
1181 /*
1182 IF x_mrc_flag = 'Y' THEN
1183 IF PA_MRC_FINPLAN. G_MRC_ENABLED_FOR_BUDGETS IS NULL THEN
1184 PA_MRC_FINPLAN.CHECK_MRC_INSTALL
1185 (x_return_status => l_return_status,
1186 x_msg_count => l_msg_count,
1187 x_msg_data => l_msg_data);
1188 END IF;
1189
1190 IF PA_MRC_FINPLAN.G_MRC_ENABLED_FOR_BUDGETS AND
1194 p_budget_version_id => x_budget_version_id,
1191 PA_MRC_FINPLAN.G_FINPLAN_MRC_OPTION_CODE = 'A' THEN
1192 PA_MRC_FINPLAN.MAINTAIN_ONE_MC_BUDGET_LINE
1193 (p_budget_line_id => l_budget_line_id,
1195 p_action => PA_MRC_FINPLAN.G_ACTION_DELETE,
1196 x_return_status => l_return_status,
1197 x_msg_count => l_msg_count,
1198 x_msg_data => l_msg_data);
1199 END IF;
1200
1201 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1202 RAISE g_mrc_exception;
1203 END IF;
1204
1205 END IF;
1206 */
1207
1208 END Delete_Row;
1209
1210
1211
1212 Procedure check_overlapping_dates ( X_Budget_Version_Id NUMBER,
1213 x_resource_name IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1214 x_err_code IN OUT NOCOPY NUMBER) is --File.Sql.39 bug 4440895
1215
1216 v_temp varchar2(1);
1217 v_res_assignment_id PA_RESOURCE_ASSIGNMENTS.resource_assignment_id%TYPE; -- Added for bug 3777706
1218 cursor c is
1219 /* commented for bug 3777706 and included modified query.
1220 Modified the query to be based on pa_budget_lines table to
1221 instead of pa_budget_lines_v, as pa_budget_lines_v is based
1222 on pa_budget_lines as well as lookup tables, leading to unnecessary
1223 access of lookup tables which is not required in this case
1224 select a.resource_name
1225 from pa_budget_lines_v a, pa_budget_lines_v b
1226 where a.budget_version_id = x_budget_version_id
1227 and b.budget_version_id = x_budget_version_id
1228 and a.task_id||null = b.task_id||null
1229 and a.resource_list_member_id = b.resource_list_member_id
1230 and a.row_id <> b.row_id
1231 and ((a.start_date
1232 between b.start_date
1233 and nvl(b.end_date,a.start_date +1))
1234 or (a.end_date
1235 between b.start_date
1236 and nvl(b.end_date,b.end_date+1))
1237 or (b.start_date
1238 between a.start_date
1239 and nvl(a.end_date,b.start_date+1))
1240 ); */
1241 -- start of modified query bug 3777706
1242 select I1.resource_assignment_id
1243 from
1244 PA_BUDGET_LINES I1,
1245 PA_BUDGET_LINES I2
1246 where
1247 I1.budget_version_id = x_budget_version_id
1248 and I2.budget_version_id = x_budget_version_id
1249 and I1.resource_assignment_id = I2.resource_assignment_id
1250 and I1.txn_currency_code = I2.txn_currency_code
1251 and I1.rowid <> I2.rowid
1252 and ((I1.start_date
1253 between I2.start_date and I2.end_date)
1254 or (I1.end_date
1255 between I2.start_date and I2.end_date)
1256 or (I2.start_date
1257 between I1.start_date and I1.end_date))
1258 and (I1.txn_currency_code = I2.txn_currency_code or I1.txn_currency_code is null and I2.txn_currency_code is null);
1259 -- end of modified query bug 3777706
1260 BEGIN
1261 open c;
1262 fetch c into v_res_assignment_id; -- changed x_resource_name to v_res_assignment_id
1263 if c%found then
1264 -- start of bug 3777706
1265 select SUBSTRB(pa_resources_pkg.get_resource_name(M1.RESOURCE_ID, M1.RESOURCE_TYPE_ID),1,30) resource_name
1266 into x_resource_name
1267 from PA_RESOURCE_LIST_MEMBERS M1,
1268 PA_RESOURCE_ASSIGNMENTS ra
1269 where
1270 ra.resource_assignment_id = v_res_assignment_id and
1271 ra.resource_list_member_id = M1.resource_list_member_id;
1272 -- end of bug 3777706
1273 x_err_code :=1;
1274 else
1275 x_err_code :=0;
1276 end if;
1277 close c;
1278 EXCEPTION
1279 when others then
1280 x_err_code :=sqlcode;
1281 END check_overlapping_dates;
1282
1283 END PA_BUDGET_LINES_V_PKG;