1 PACKAGE BODY PA_ASSET_ALLOCATION_PVT AS
2 /* $Header: PACASALB.pls 120.4.12010000.2 2008/08/06 11:31:07 atshukla ship $ */
3
4 l_mrc_flag VARCHAR2(1) := paapimp_pkg.get_mrc_flag;
5 PROCEDURE ALLOCATE_UNASSIGNED
6 (p_project_asset_line_id IN NUMBER,
7 p_line_type IN VARCHAR2,
8 p_capital_event_id IN NUMBER,
9 p_project_id IN NUMBER,
10 p_task_id IN NUMBER,
11 p_asset_allocation_method IN VARCHAR2,
12 p_asset_category_id IN NUMBER, /* Added for bug#3211946 */
13 x_asset_or_project_err OUT NOCOPY VARCHAR2,
14 x_error_code OUT NOCOPY VARCHAR2,
15 x_err_asset_id OUT NOCOPY NUMBER,
16 x_return_status OUT NOCOPY VARCHAR2,
17 x_msg_count OUT NOCOPY NUMBER,
18 x_msg_data OUT NOCOPY VARCHAR2) IS
19
20
21 CURSOR all_project_assets_cur(x_line_type VARCHAR2) IS
22 SELECT project_asset_id,
23 0, --asset_basis_amount
24 0 --total_basis_amount
25 FROM pa_project_assets_all
26 WHERE project_id = p_project_id
27 AND capital_event_id = p_capital_event_id
28 AND project_asset_type = DECODE(x_line_type,'C','AS-BUILT','R','RETIREMENT_ADJUSTMENT','AS-BUILT')
29 AND nvl(asset_category_id, -99) = nvl(p_asset_category_id, nvl(asset_category_id, -99)) /* Bug#3211946 */
30 AND capital_hold_flag = 'N';
31
32
33 CURSOR project_asgn_assets_cur(x_line_type VARCHAR2) IS
34 SELECT paa.project_asset_id,
35 0, --asset_basis_amount
36 0 --total_basis_amount
37 FROM pa_project_assets_all pa,
38 pa_project_asset_assignments paa
39 WHERE pa.project_asset_id = paa.project_asset_id
40 AND pa.project_id = p_project_id
41 AND paa.project_id = p_project_id
42 AND pa.capital_event_id = p_capital_event_id
43 AND paa.task_id = 0
44 AND pa.project_asset_type = DECODE(x_line_type,'C','AS-BUILT','R','RETIREMENT_ADJUSTMENT','AS-BUILT')
45 AND nvl(pa.asset_category_id, -99) = nvl(p_asset_category_id, nvl(pa.asset_category_id, -99)) /* Bug#3211946 */
46 AND pa.capital_hold_flag = 'N';
47
48
49 CURSOR task_asgn_assets_cur(x_task_id NUMBER, x_line_type VARCHAR2) IS
50 SELECT paa.project_asset_id,
51 0, --asset_basis_amount
52 0 --total_basis_amount
53 FROM pa_project_assets_all pa,
54 pa_project_asset_assignments paa
55 WHERE pa.project_asset_id = paa.project_asset_id
56 AND pa.project_id = p_project_id
57 AND paa.project_id = p_project_id
58 AND pa.capital_event_id = p_capital_event_id
59 AND paa.task_id = x_task_id
60 AND pa.project_asset_type = DECODE(x_line_type,'C','AS-BUILT','R','RETIREMENT_ADJUSTMENT','AS-BUILT')
61 AND nvl(pa.asset_category_id, -99) = nvl(p_asset_category_id, nvl(pa.asset_category_id, -99)) /* Bug#3211946 */
62 AND pa.capital_hold_flag = 'N';
63
64
65 v_top_task_id NUMBER;
66
67 CURSOR top_task_asgn_assets_cur(x_line_type VARCHAR2) IS
68 SELECT paa.project_asset_id,
69 0, --asset_basis_amount
70 0 --total_basis_amount
71 FROM pa_project_assets_all pa,
72 pa_project_asset_assignments paa
73 WHERE pa.project_asset_id = paa.project_asset_id
74 AND pa.project_id = p_project_id
75 AND paa.project_id = p_project_id
76 AND pa.capital_event_id = p_capital_event_id
77 AND paa.task_id = v_top_task_id
78 AND pa.project_asset_type = DECODE(x_line_type,'C','AS-BUILT','R','RETIREMENT_ADJUSTMENT','AS-BUILT')
79 AND nvl(pa.asset_category_id, -99) = nvl(p_asset_category_id, nvl(pa.asset_category_id, -99)) /* Bug#3211946 */
80 AND pa.capital_hold_flag = 'N';
81
82
83 CURSOR wbs_branch_tasks_cur(x_parent_task_id NUMBER) IS
84 SELECT task_id,
85 task_number
86 FROM pa_tasks
87 WHERE task_id <> x_parent_task_id
88 AND task_id <> p_task_id
89 CONNECT BY parent_task_id = PRIOR task_id
90 START WITH task_id = x_parent_task_id;
91
92 wbs_branch_tasks_rec wbs_branch_tasks_cur%ROWTYPE;
93
94
95
96 asset_basis_table PA_ASSET_ALLOCATION_PVT.ASSET_BASIS_TABLE_TYPE;
97
98 v_common_project VARCHAR2(1) := 'N';
99 v_common_task VARCHAR2(1) := 'N';
100 v_common_lowest_task VARCHAR2(1) := 'N';
101 v_assignment_count NUMBER := 0;
102 i NUMBER := 1;
103 v_return_status VARCHAR2(1) := 'S';
104 v_msg_count NUMBER := 0;
105 v_msg_data VARCHAR2(2000);
106 v_asset_count NUMBER := 0;
107 v_project_asset_id NUMBER;
108 v_project_asset_type PA_PROJECT_ASSETS_ALL.project_asset_type%TYPE;
109 v_date_placed_in_service DATE;
110 v_capital_hold_flag PA_PROJECT_ASSETS_ALL.capital_hold_flag%TYPE;
111 v_total_basis_amount NUMBER := 0;
112 v_asset_basis_amount NUMBER := 0;
113 v_init_total_basis_amount NUMBER := 0;
114 v_sum_asset_basis_amount NUMBER := 0;
115 v_project_asset_line_id NUMBER;
116
117 v_project_asset_line_detail_id NUMBER ; /*Bug 4914051*/
118 v_rev_proj_asset_line_id NUMBER;
119
120 v_src_project_asset_line_id NUMBER;
121 v_project_id NUMBER;
122 v_parent_task_id NUMBER;
123 v_asset_units NUMBER := 0;
124 v_std_cost_count NUMBER := 0;
125 v_std_unit_cost NUMBER := 0;
126 v_asset_category_id NUMBER;
127 v_book_type_code FA_BOOK_CONTROLS.book_type_code%TYPE;
128 v_current_asset_cost NUMBER := 0;
129 v_current_cost NUMBER := 0;
130 v_original_asset_cost NUMBER := 0;
131 v_orig_cost NUMBER := 0;
132 v_remaining_cost NUMBER := 0;
133 v_user NUMBER := FND_GLOBAL.user_id;
134 v_login NUMBER := FND_GLOBAL.login_id;
135 v_request_id NUMBER := FND_GLOBAL.conc_request_id;
136 v_program_application_id NUMBER := FND_GLOBAL.prog_appl_id;
137 v_program_id NUMBER := FND_GLOBAL.conc_program_id;
138
139 v_err_code NUMBER := 0;
140 v_err_stage VARCHAR2(2000);
141
142 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
143
144 no_assignments_exist EXCEPTION;
145 unexp_error_in_client_extn EXCEPTION;
146 error_in_client_extn EXCEPTION;
147 asset_not_on_project EXCEPTION;
148 asset_no_generation EXCEPTION;
149 asset_no_dpis EXCEPTION;
150 asset_type_mismatch EXCEPTION;
151 null_asset_basis EXCEPTION;
152 negative_asset_basis EXCEPTION;
153 zero_total_basis EXCEPTION;
154 null_total_basis EXCEPTION;
155 negative_total_basis EXCEPTION;
156 inconsistent_total_basis EXCEPTION;
157 asset_basis_sum_error EXCEPTION;
158 standard_cost_not_found EXCEPTION;
159 error_calling_update_cost EXCEPTION;
160
161 BEGIN
162
163 --Initialize variables
164 x_return_status := 'S';
165 x_msg_count := 0;
166 x_asset_or_project_err := NULL;
167 x_error_code := NULL;
168 x_err_asset_id := 0;
169 v_project_asset_line_id := p_project_asset_line_id;
170 v_project_id := p_project_id;
171
172
173 --If the Asset Allocation Method is 'N' for No Allocation,
174 --then simply return and leave line UNASSIGNED
175 IF NVL(p_asset_allocation_method,'N') = 'N' THEN
176 RETURN;
177 END IF;
178
179
180
181 --Determine if the line is similar enough to the previous line that
182 --the cached G_asset_basis_table can be used
183 IF p_project_id = G_project_id AND -- Fix for bug: 5091281
184 p_task_id = G_task_id AND
185 p_capital_event_id = G_capital_event_id AND
186 p_asset_allocation_method = G_asset_allocation_method AND
187 NVL(p_asset_category_id,-99) = NVL(G_asset_category_id,-99) AND --Added for bug 7175027
188 p_asset_allocation_method <> 'CE' AND --Do not use cache when the Client Extension is used to determine basis
189 p_line_type = G_line_type THEN
190
191 IF PG_DEBUG = 'Y' THEN
192 PA_DEBUG.DEBUG('Using cached Asset Allocation basis table for Project Asset Line ID '||p_project_asset_line_id);
193 END IF;
194
195 asset_basis_table := G_asset_basis_table;
196 GOTO allocate_line;
197 END IF;
198
199
200 --Construct the asset basis table
201 IF PG_DEBUG = 'Y' THEN
202 PA_DEBUG.DEBUG('Constructing the Asset Allocation basis table for Project Asset Line ID '||p_project_asset_line_id);
203 END IF;
204
205
206 --Determine if entire project has a 'Common' Asset Assignment
207 SELECT DECODE(COUNT(*),0,'N','Y')
208 INTO v_common_project
209 FROM pa_project_asset_assignments
210 WHERE project_id = p_project_id
211 AND task_id = 0
212 AND project_asset_id = 0;
213
214
215 IF p_task_id <> 0 THEN
216
217 IF PG_DEBUG = 'Y' THEN
218 PA_DEBUG.DEBUG('Get top and parent task ids for task id '||p_task_id);
219 END IF;
220
221 --Get the Top Task ID
222 SELECT top_task_id,
223 parent_task_id
224 INTO v_top_task_id,
225 v_parent_task_id
226 FROM pa_tasks
227 WHERE task_id = p_task_id;
228
229
230 IF PG_DEBUG = 'Y' THEN
231 PA_DEBUG.DEBUG('Count top task common assignments');
232 END IF;
233
234 --Determine if top task has a 'Common' Asset Assignment
235 --('Common' Asset Assignments at the Top Task level are allocated across
236 --ALL project assets, just like Project-level Common assignments)
237 SELECT DECODE(COUNT(*),0,'N','Y')
238 INTO v_common_task
239 FROM pa_project_asset_assignments
240 WHERE project_id = p_project_id
241 AND task_id = v_top_task_id
242 AND project_asset_id = 0;
243
244 IF v_common_task = 'N' THEN
245 --Determine if the current task has a Lowest Task Common assignment
246 SELECT DECODE(COUNT(*),0,'N','Y')
247 INTO v_common_lowest_task
248 FROM pa_project_asset_assignments
249 WHERE project_id = p_project_id
250 AND task_id = p_task_id
251 AND task_id <> v_top_task_id --Only Lowest "Leaf" Tasks that are not also Top Tasks
252 AND project_asset_id = 0;
253 END IF;
254
255 END IF;
256
257
258 --If there is a 'Common' asset assignment for a Lowest Task, then select
259 --all assets that are assigned beneath the same Parent (not Top) Task. For
260 --example, if a Common assignment is made at task 2.1, the select any assets
261 --assigned to 2.2, 2.3, 2.4.1, 2.4.2, 2.5, etc. But do NOt select assets assigned
262 --to task 3.0, 4.1, and so on, since they reside outside of the current WBS "branch".
263 IF v_common_lowest_task = 'Y' THEN
264
265 --Common Lowest Task Assignment
266 IF PG_DEBUG = 'Y' THEN
267 PA_DEBUG.DEBUG('Lowest Task Common Assignment exists for Project Asset Line ID '||p_project_asset_line_id);
268 END IF;
269
270 FOR wbs_branch_tasks_rec IN wbs_branch_tasks_cur(v_parent_task_id) LOOP
271
272 IF PG_DEBUG = 'Y' THEN
273 PA_DEBUG.DEBUG('Task Number '||wbs_branch_tasks_rec.task_number||' exists beneath Parent Task ID '||v_parent_task_id);
274 END IF;
275
276 --Populate table with all assets in the event assigned to the task
277 OPEN task_asgn_assets_cur(wbs_branch_tasks_rec.task_id, p_line_type);
278 LOOP
279 FETCH task_asgn_assets_cur INTO asset_basis_table(i);
280 EXIT WHEN task_asgn_assets_cur%NOTFOUND;
281 i := i + 1;
282 END LOOP;
283 CLOSE task_asgn_assets_cur;
284
285 END LOOP; --WBS Branch Tasks
286
287
288 END IF; --Common Lowest "Leaf" Assignment
289
290
291
292 --If there is a 'Common' asset assignment for the project or top task,
293 --then select all assets on the project that belong to the current event,
294 --regardless of asset assignment.
295 IF (v_common_task = 'Y') OR (v_common_project = 'Y') THEN
296
297 --Common Assignment
298 IF PG_DEBUG = 'Y' THEN
299 PA_DEBUG.DEBUG('Project or Top Task Common Assignment exists for Project Asset Line ID '||p_project_asset_line_id);
300 END IF;
301
302 OPEN all_project_assets_cur(p_line_type);
303 LOOP
304 FETCH all_project_assets_cur INTO asset_basis_table(i);
305 EXIT WHEN all_project_assets_cur%NOTFOUND;
306 i := i + 1;
307 END LOOP;
308 CLOSE all_project_assets_cur;
309
310 ELSE --Determine the Grouping Level for the current asset line
311
312
313 IF PG_DEBUG = 'Y' THEN
314 PA_DEBUG.DEBUG('Count project level asset assignments');
315 END IF;
316
317 --Check for a project level grouping method
318 SELECT COUNT(*)
319 INTO v_assignment_count
320 FROM pa_project_asset_assignments paa
321 WHERE paa.project_id = p_project_id
322 AND paa.task_id = 0;
323
324
325 IF v_assignment_count > 0 THEN
326
327 --Project Assignment
328 IF PG_DEBUG = 'Y' THEN
329 PA_DEBUG.DEBUG('Project Assignment(s) exist for Project Asset Line ID '||p_project_asset_line_id);
330 END IF;
331
332
333 --Populate table with all assets in the event assigned to the project
334 OPEN project_asgn_assets_cur(p_line_type);
335 LOOP
336 FETCH project_asgn_assets_cur INTO asset_basis_table(i);
337 EXIT WHEN project_asgn_assets_cur%NOTFOUND;
338 i := i + 1;
339 END LOOP;
340 CLOSE project_asgn_assets_cur;
341
342 ELSIF p_task_id <> 0 THEN --Check for task level assignments
343
344 IF PG_DEBUG = 'Y' THEN
345 PA_DEBUG.DEBUG('Count task level asset assignments for task id '||p_task_id);
346 END IF;
347
348 --Check for a task level grouping method
349 SELECT COUNT(*)
350 INTO v_assignment_count
351 FROM pa_project_asset_assignments paa
352 WHERE paa.project_id = p_project_id
353 AND paa.task_id = p_task_id;
354
355 IF v_assignment_count > 0 THEN
356
357 --Task Assignment
358 IF PG_DEBUG = 'Y' THEN
359 PA_DEBUG.DEBUG('Task Assignment(s) exist for Project Asset Line ID '||p_project_asset_line_id);
360 END IF;
361
362
363 --Populate table with all assets in the event assigned to the task
364 OPEN task_asgn_assets_cur(p_task_id, p_line_type);
365 LOOP
366 FETCH task_asgn_assets_cur INTO asset_basis_table(i);
367 EXIT WHEN task_asgn_assets_cur%NOTFOUND;
368 i := i + 1;
369 END LOOP;
370 CLOSE task_asgn_assets_cur;
371
372 ELSE --Check for a top task level grouping method
373
374 IF PG_DEBUG = 'Y' THEN
375 PA_DEBUG.DEBUG('Count top task level asset assignments for top task id '||v_top_task_id);
376 END IF;
377
378 SELECT COUNT(*)
379 INTO v_assignment_count
380 FROM pa_project_asset_assignments paa
381 WHERE paa.project_id = p_project_id
382 AND paa.task_id = v_top_task_id;
383
384 IF v_assignment_count > 0 THEN
385
386 --Top Task Assignment
387 IF PG_DEBUG = 'Y' THEN
388 PA_DEBUG.DEBUG('Top Task Assignment(s) exist for Project Asset Line ID '||p_project_asset_line_id);
389 END IF;
390
391
392 --Populate table with all assets in the event assigned to the top task
393 OPEN top_task_asgn_assets_cur(p_line_type);
394 LOOP
395 FETCH top_task_asgn_assets_cur INTO asset_basis_table(i);
396 EXIT WHEN top_task_asgn_assets_cur%NOTFOUND;
397 i := i + 1;
398 END LOOP;
399 CLOSE top_task_asgn_assets_cur;
400
401 ELSE --No assignments exist, error and exit
402 RAISE no_assignments_exist;
403
404 END IF; --Top Task assignments
405
406 END IF; --Task assignments
407
408 END IF; --Project assignments
409
410 END IF; --Common Task or Project vs. Grouping Level
411
412
413 --If the Asset Allocation Method is 'CE' for Client Extension Basis,
414 --then call the ASSET_ALLOC_BASIS client extension
415 IF p_asset_allocation_method = 'CE' THEN
416
417 --Call the client extension
418 PA_CLIENT_EXTN_ASSET_ALLOC.ASSET_ALLOC_BASIS
419 (p_project_asset_line_id => v_project_asset_line_id,
420 p_project_id => v_project_id,
421 p_asset_basis_table => asset_basis_table,
422 x_return_status => v_return_status,
423 x_msg_count => v_msg_count,
424 x_msg_data => v_msg_data);
425
426 IF v_return_status = 'E' THEN
427 RAISE error_in_client_extn;
428 ELSIF v_return_status = 'U' THEN
429 RAISE unexp_error_in_client_extn;
430 END IF;
431 END IF;
432
433
434
435 IF PG_DEBUG = 'Y' THEN
436 PA_DEBUG.DEBUG('Initial Asset Allocation basis table for Project Asset Line ID '||p_project_asset_line_id||' Line Type '||p_line_type);
437 PA_DEBUG.DEBUG('Project Asset ID - Asset Basis - Total Basis');
438 END IF;
439
440 i := asset_basis_table.FIRST;
441
442 WHILE i IS NOT NULL LOOP
443
444 v_project_asset_id := asset_basis_table(i).PROJECT_ASSET_ID;
445 v_asset_basis_amount := asset_basis_table(i).ASSET_BASIS_AMOUNT;
446 v_total_basis_amount := asset_basis_table(i).TOTAL_BASIS_AMOUNT;
447
448 IF PG_DEBUG = 'Y' THEN
449 PA_DEBUG.DEBUG(v_project_asset_id||' - '||v_asset_basis_amount||' - '||v_total_basis_amount);
450 END IF;
451
452
453 i := asset_basis_table.NEXT(i);
454 END LOOP;
455
456
457
458 --Perform preliminary validations on the resulting assets and basis amounts
459
460 --NOTE: from here forward, use WHILE loops to process the asset_basis_table,
461 --in case the user has deleted index rows from the table in the client extension
462
463
464 --First, verify that there is at least one asset in the table
465 v_asset_count := asset_basis_table.COUNT;
466
467 IF v_asset_count = 0 THEN
468 RETURN;
469 END IF;
470
471
472 --Verify that each project asset ID is valid for the project
473 i := asset_basis_table.FIRST;
474
475 WHILE i IS NOT NULL LOOP
476
477 SELECT COUNT(*)
478 INTO v_asset_count
479 FROM pa_project_assets_all pa
480 WHERE pa.project_id = p_project_id
481 AND pa.project_asset_id = asset_basis_table(i).PROJECT_ASSET_ID;
482
483 IF v_asset_count = 0 THEN
484
485 v_project_asset_id := asset_basis_table(i).PROJECT_ASSET_ID;
486 RAISE asset_not_on_project;
487
488 ELSIF v_asset_count = 1 THEN
489
490 SELECT project_asset_type,
491 date_placed_in_service,
492 capital_hold_flag
493 INTO v_project_asset_type,
494 v_date_placed_in_service,
495 v_capital_hold_flag
496 FROM pa_project_assets_all pa
497 WHERE pa.project_id = p_project_id
498 AND pa.project_asset_id = asset_basis_table(i).PROJECT_ASSET_ID;
499
500 --Verify that the DPIS is NOT NULL
501 IF v_date_placed_in_service IS NULL THEN
502
503 v_project_asset_id := asset_basis_table(i).PROJECT_ASSET_ID;
504 RAISE asset_no_dpis;
505
506 END IF;
507
508
509 --Verify that the asset is eligible for line generation
510 IF NVL(v_capital_hold_flag,'N') = 'Y' THEN
511
512 v_project_asset_id := asset_basis_table(i).PROJECT_ASSET_ID;
513 RAISE asset_no_generation;
514
515 END IF;
516
517
518 --Verify that the project asset type matches the line type
519 IF p_line_type = 'C' AND v_project_asset_type <> 'AS-BUILT' THEN
520
521 v_project_asset_id := asset_basis_table(i).PROJECT_ASSET_ID;
522 RAISE asset_type_mismatch;
523
524 ELSIF p_line_type = 'R' AND v_project_asset_type <> 'RETIREMENT_ADJUSTMENT' THEN
525
526 v_project_asset_id := asset_basis_table(i).PROJECT_ASSET_ID;
527 RAISE asset_type_mismatch;
528
529 END IF;
530
531 END IF;
532
533 i := asset_basis_table.NEXT(i);
534 END LOOP;
535
536
537 --If there is only asset in the table then skip the basis determination and assign asset line
538 v_asset_count := asset_basis_table.COUNT;
539
540 IF v_asset_count = 1 THEN
541
542 i := asset_basis_table.FIRST;
543
544 IF PG_DEBUG = 'Y' THEN
545 PA_DEBUG.DEBUG('Only one asset in basis table, assigning Project Asset Line ID '||p_project_asset_line_id
546 ||' to Project Asset ID '||asset_basis_table(i).PROJECT_ASSET_ID);
547 END IF;
548
549
550 --Assign current UNASSIGNED asset line to current project asset
551
552 UPDATE pa_project_asset_lines_all
553 SET project_asset_id = asset_basis_table(i).PROJECT_ASSET_ID,
554 last_update_date = SYSDATE,
555 last_updated_by = v_user,
556 last_update_login = v_login,
557 request_id = v_request_id,
558 program_application_id = v_program_application_id,
559 program_id = v_program_id,
560 program_update_date = SYSDATE
561 WHERE project_asset_line_id = p_project_asset_line_id;
562
563 --UPDATE Grouped CIP Cost on newly assigned project asset
564
565 --Get the current asset cost of the UNASSIGNED asset line in order to determine the remaining amount
566 SELECT current_asset_cost
567 INTO v_current_asset_cost
568 FROM pa_project_asset_lines_all
569 WHERE project_asset_line_id = p_project_asset_line_id;
570
571 --Call procedure to add this amount to the Grouped CIP Amount of the asset
572 PA_FAXFACE.update_asset_cost
573 (asset_basis_table(i).PROJECT_ASSET_ID,
574 v_current_asset_cost,
575 0, --- capitalized_cost
576 v_err_stage,
577 v_err_code);
578
579 IF v_err_code <> 0 THEN
580 RAISE error_calling_update_cost;
581 END IF;
582
583 RETURN;
584 END IF;
585
586
587
588 --Populate the Basis Amounts according to the Asset Allocation Method specified
589
590
591 --If the Asset Allocation Method is 'SE' for Spread Evenly,
592 --then give each asset an equal basis amount
593 IF p_asset_allocation_method = 'SE' THEN
594
595 --Determine the Total Basis Amount
596 v_total_basis_amount := asset_basis_table.COUNT;
597
598 --Loop through each asset and assign the Total and Asset Basis Amounts
599 i := asset_basis_table.FIRST;
600
601 WHILE i IS NOT NULL LOOP
602
603 asset_basis_table(i).TOTAL_BASIS_AMOUNT := v_total_basis_amount;
604
605 --For Spread Evenly method, Asset Basis Amount = 1
606 asset_basis_table(i).ASSET_BASIS_AMOUNT := 1;
607
608 i := asset_basis_table.NEXT(i);
609 END LOOP;
610 END IF; -- 'SE' allocation method
611
612
613 --If the Asset Allocation Method is 'AU' for Actual Units,
614 --then use each asset's Actual Units as the basis amount
615 IF p_asset_allocation_method = 'AU' THEN
616
617 --Loop through each asset and assign the Asset Basis Amount and sum up the Total Basis Amount
618 i := asset_basis_table.FIRST;
619
620 WHILE i IS NOT NULL LOOP
621
622 v_asset_basis_amount := NULL; --Initialize this to NULL in order to trap NULL units as an error
623
624 --For Actual Units method, Asset Basis Amount = Actual Units
625 SELECT asset_units
626 INTO v_asset_basis_amount
627 FROM pa_project_assets_all
628 WHERE project_asset_id = asset_basis_table(i).PROJECT_ASSET_ID;
629
630 asset_basis_table(i).ASSET_BASIS_AMOUNT := v_asset_basis_amount;
631
632 v_total_basis_amount := v_total_basis_amount + NVL(v_asset_basis_amount,0);
633
634 i := asset_basis_table.NEXT(i);
635 END LOOP;
636
637 --Loop through each asset and assign the Total Basis Amounts
638 i := asset_basis_table.FIRST;
639
640 WHILE i IS NOT NULL LOOP
641
642 asset_basis_table(i).TOTAL_BASIS_AMOUNT := v_total_basis_amount;
643
644 i := asset_basis_table.NEXT(i);
645 END LOOP;
646 END IF; -- 'Q' allocation method
647
648
649 --If the Asset Allocation Method is 'CC' for Current Asset Cost,
650 --then use each asset's Grouped CIP Cost as the basis amount
651 IF p_asset_allocation_method = 'CC' THEN
652
653 --Determine the Total Basis Amount
654 i := asset_basis_table.FIRST;
655
656 WHILE i IS NOT NULL LOOP
657
658 --For Current Asset Cost method, Asset Basis Amount = Grouped CIP Cost
659 v_asset_basis_amount := NULL; --Initialize this to NULL in order to trap NULL units as an error
660
661 SELECT grouped_cip_cost
662 INTO v_asset_basis_amount
663 FROM pa_project_assets_all
664 WHERE project_asset_id = asset_basis_table(i).PROJECT_ASSET_ID;
665
666 asset_basis_table(i).ASSET_BASIS_AMOUNT := v_asset_basis_amount;
667
668 v_total_basis_amount := v_total_basis_amount + NVL(v_asset_basis_amount,0);
669
670 i := asset_basis_table.NEXT(i);
671 END LOOP;
672
673 --Loop through each asset and assign the Total and Asset Basis Amounts
674 i := asset_basis_table.FIRST;
675
676 WHILE i IS NOT NULL LOOP
677
678 asset_basis_table(i).TOTAL_BASIS_AMOUNT := v_total_basis_amount;
679
680 i := asset_basis_table.NEXT(i);
681 END LOOP;
682 END IF; -- 'CC' allocation method
683
684
685
686 --If the Asset Allocation Method is 'EC' for Estimated Cost,
687 --then use each asset's Estimated Cost as the basis amount
688 IF p_asset_allocation_method = 'EC' THEN
689
690 --Determine the Total Basis Amount
691 i := asset_basis_table.FIRST;
692
693 WHILE i IS NOT NULL LOOP
694
695 --For Estimated Cost method, Asset Basis Amount = Estimated Cost
696 v_asset_basis_amount := NULL; --Initialize this to NULL in order to trap NULL units as an error
697
698 SELECT estimated_cost
699 INTO v_asset_basis_amount
700 FROM pa_project_assets_all
701 WHERE project_asset_id = asset_basis_table(i).PROJECT_ASSET_ID;
702
703 asset_basis_table(i).ASSET_BASIS_AMOUNT := v_asset_basis_amount;
704
705 v_total_basis_amount := v_total_basis_amount + NVL(v_asset_basis_amount,0);
706
707 i := asset_basis_table.NEXT(i);
708 END LOOP;
709
710 --Loop through each asset and assign the Total and Asset Basis Amounts
711 i := asset_basis_table.FIRST;
712
713 WHILE i IS NOT NULL LOOP
714
715 asset_basis_table(i).TOTAL_BASIS_AMOUNT := v_total_basis_amount;
716
717 i := asset_basis_table.NEXT(i);
718 END LOOP;
719 END IF; -- 'EC' allocation method
720
721
722 --If the Asset Allocation Method is 'SC' for Standard Cost,
723 --then use each asset's Standard Cost * Actual Units as the basis amount
724 IF p_asset_allocation_method = 'SC' THEN
725
726 --Determine the Total Basis Amount
727 i := asset_basis_table.FIRST;
728
729 WHILE i IS NOT NULL LOOP
730
731 v_project_asset_id := asset_basis_table(i).PROJECT_ASSET_ID;
732
733 --For Standard Cost method, Asset Basis Amount = Standard Cost * Actual Units
734 v_asset_basis_amount := 0;
735
736 SELECT NVL(asset_units,0),
737 NVL(asset_category_id,-99),
738 NVL(book_type_code,'X')
739 INTO v_asset_units,
740 v_asset_category_id,
741 v_book_type_code
742 FROM pa_project_assets_all
743 WHERE project_asset_id = asset_basis_table(i).PROJECT_ASSET_ID;
744
745 SELECT COUNT(*)
746 INTO v_std_cost_count
747 FROM pa_standard_unit_costs
748 WHERE asset_category_id = v_asset_category_id
749 AND book_type_code = v_book_type_code;
750
751 IF v_std_cost_count = 0 THEN
752 RAISE standard_cost_not_found;
753 ELSE
754 SELECT standard_unit_cost
755 INTO v_std_unit_cost
756 FROM pa_standard_unit_costs
757 WHERE asset_category_id = v_asset_category_id
758 AND book_type_code = v_book_type_code;
759 END IF;
760
761 v_asset_basis_amount := v_std_unit_cost * v_asset_units;
762
763 asset_basis_table(i).ASSET_BASIS_AMOUNT := v_asset_basis_amount;
764
765 v_total_basis_amount := v_total_basis_amount + v_asset_basis_amount;
766
767 i := asset_basis_table.NEXT(i);
768 END LOOP;
769
770 --Loop through each asset and assign the Total and Asset Basis Amounts
771 i := asset_basis_table.FIRST;
772
773 WHILE i IS NOT NULL LOOP
774
775 asset_basis_table(i).TOTAL_BASIS_AMOUNT := v_total_basis_amount;
776
777 i := asset_basis_table.NEXT(i);
778 END LOOP;
779 END IF; -- 'SC' allocation method
780
781
782
783 --Perform final validations on the resulting assets and basis amounts
784 i := asset_basis_table.FIRST;
785 v_init_total_basis_amount := asset_basis_table(i).TOTAL_BASIS_AMOUNT;
786 v_sum_asset_basis_amount := 0;
787
788 --DBMS_OUTPUT.PUT_LINE('Asset Allocation basis table for Project Asset Line ID '||p_project_asset_line_id);
789 --DBMS_OUTPUT.PUT_LINE('Project Asset ID - Asset Basis - Total Basis');
790 IF PG_DEBUG = 'Y' THEN
791 PA_DEBUG.DEBUG('Asset Allocation basis table for Project Asset Line ID '||p_project_asset_line_id);
792 PA_DEBUG.DEBUG('Project Asset ID - Asset Basis - Total Basis');
793 END IF;
794
795 i := asset_basis_table.FIRST;
796
797 WHILE i IS NOT NULL LOOP
798
799 v_project_asset_id := asset_basis_table(i).PROJECT_ASSET_ID;
800 v_asset_basis_amount := asset_basis_table(i).ASSET_BASIS_AMOUNT;
801 v_total_basis_amount := asset_basis_table(i).TOTAL_BASIS_AMOUNT;
802 v_sum_asset_basis_amount := v_sum_asset_basis_amount + v_asset_basis_amount;
803
804 --DBMS_OUTPUT.PUT_LINE(v_project_asset_id||' - '||v_asset_basis_amount||' - '||v_total_basis_amount);
805 IF PG_DEBUG = 'Y' THEN
806 PA_DEBUG.DEBUG(v_project_asset_id||' - '||v_asset_basis_amount||' - '||v_total_basis_amount);
807 END IF;
808
809 --Verify that each Asset Basis Amount is NOT NULL and >=0
810 IF v_asset_basis_amount IS NULL THEN
811 RAISE null_asset_basis;
812 ELSIF v_asset_basis_amount < 0 THEN
813 RAISE negative_asset_basis;
814 END IF;
815
816
817 --Validate that the total basis amount is not ZERO or negative or NULL
818 IF v_total_basis_amount = 0 THEN
819 RAISE zero_total_basis;
820 ELSIF v_total_basis_amount < 0 THEN
821 RAISE negative_total_basis;
822 ELSIF v_total_basis_amount IS NULL THEN
823 RAISE null_total_basis;
824 END IF;
825
826
827 --Verify that the Total Basis Amount is the same on each row
828 IF v_total_basis_amount <> v_init_total_basis_amount THEN
829 RAISE inconsistent_total_basis;
830 END IF;
831
832 i := asset_basis_table.NEXT(i);
833 END LOOP;
834
835
836 --Verify that the Total Basis Amount equals the sum of the Asset Basis Amounts
837 IF v_sum_asset_basis_amount <> v_total_basis_amount THEN
838 RAISE asset_basis_sum_error;
839 END IF;
840
841
842 --Validations are complete. Store values in Globals so that subsequent lines
843 --may use the cached value, if appropriate.
844
845 G_project_id := p_project_id; -- 5091281
846 G_task_id := p_task_id;
847 G_capital_event_id := p_capital_event_id;
848 G_asset_allocation_method := p_asset_allocation_method;
849 G_asset_category_id := p_asset_category_id; --Added for bug 7175027
850 G_line_type := p_line_type;
851 G_asset_basis_table := asset_basis_table;
852
853
854 --Use of cached table will begin here
855 <<allocate_line>>
856
857 --Get the original Asset Line Amount
858 --Get CURRENT and not original cost, in case the user has previously done a manual split on the line
859 --This program will allocate the current cost of any UNASSIGNED lines across project assets
860 SELECT current_asset_cost --NOT original_asset_cost, in case of prior manual splits
861 INTO v_original_asset_cost
862 FROM pa_project_asset_lines_all
863 WHERE project_asset_line_id = p_project_asset_line_id;
864
865 --Initialize allocation variables
866 v_project_asset_line_id := p_project_asset_line_id;
867 v_current_cost := 0;
868 v_remaining_cost:= 0;
869
870
871 IF PG_DEBUG = 'Y' THEN
872 PA_DEBUG.DEBUG('Allocating project asset line '|| v_project_asset_line_id);
873 END IF;
874
875 --Allocate the current asset line and assign to each project asset
876 i := asset_basis_table.FIRST;
877
878 WHILE i IS NOT NULL LOOP
879
880 v_project_asset_id := asset_basis_table(i).PROJECT_ASSET_ID;
881
882 IF i = asset_basis_table.LAST THEN
883
884 --Assign current UNASSIGNED asset line to current project asset
885
886 UPDATE pa_project_asset_lines_all
887 SET project_asset_id = asset_basis_table(i).PROJECT_ASSET_ID,
888 last_update_date = SYSDATE,
889 last_updated_by = v_user,
890 last_update_login = v_login,
891 request_id = v_request_id,
892 program_application_id = v_program_application_id,
893 program_id = v_program_id,
894 program_update_date = SYSDATE
895 WHERE project_asset_line_id = v_project_asset_line_id;
896
897
898 --UPDATE Grouped CIP Cost on newly assigned project asset
899
900 --Get the current asset cost of the UNASSIGNED asset line in order to determine the remaining amount
901 SELECT current_asset_cost
902 INTO v_current_asset_cost
903 FROM pa_project_asset_lines_all
904 WHERE project_asset_line_id = v_project_asset_line_id;
905
906 --Call procedure to add this amount to the Grouped CIP Amount of the asset
907 PA_FAXFACE.update_asset_cost
908 (asset_basis_table(i).PROJECT_ASSET_ID,
909 v_current_asset_cost,
910 0, --- capitalized_cost
911 v_err_stage,
912 v_err_code);
913
914 IF v_err_code <> 0 THEN
915 RAISE error_calling_update_cost;
916 END IF;
917
918 ELSIF asset_basis_table(i).ASSET_BASIS_AMOUNT <> 0 THEN
919 --Update the Current Asset Cost on the UNASSIGNED asset line, and assign
920 --the UNASSIGNED line to the current project asset. Then create a new
921 --UNASSIGNED line with the same Original Asset Cost and the Current Asset
922 --Cost equal to the remainder.
923
924 --When asset basis is zero, do nothing and move on to the next project asset
925
926
927 --Calculate the allocated cost amount
928 v_current_cost := ROUND(v_original_asset_cost *
929 (asset_basis_table(i).ASSET_BASIS_AMOUNT/asset_basis_table(i).TOTAL_BASIS_AMOUNT),2);
930
931
932 --Get the current asset cost of the UNASSIGNED asset line in order to determine the remaining amount
933 SELECT current_asset_cost, project_asset_line_detail_id, rev_proj_asset_line_id, original_asset_cost /*Bug 4914051*/
934 INTO v_current_asset_cost, v_project_asset_line_detail_id, v_rev_proj_asset_line_id, v_orig_cost
935 FROM pa_project_asset_lines_all
936 WHERE project_asset_line_id = v_project_asset_line_id;
937
938
939 --Calculate the remaining amount
940 v_remaining_cost := v_current_asset_cost - v_current_cost;
941
942
943 --Update current UNASSIGNED asset line
944 UPDATE pa_project_asset_lines_all
945 SET project_asset_id = asset_basis_table(i).PROJECT_ASSET_ID,
946 current_asset_cost = v_current_cost,
947 last_update_date = SYSDATE,
948 last_updated_by = v_user,
949 last_update_login = v_login,
950 request_id = v_request_id,
951 program_application_id = v_program_application_id,
952 program_id = v_program_id,
953 program_update_date = SYSDATE
954 WHERE project_asset_line_id = v_project_asset_line_id;
955 IF l_mrc_flag = 'Y' THEN /*Bug 4914051*/
956 PA_FAXFACE.update_alc_proj_asset_lines(v_project_asset_line_id, v_orig_cost,
957 v_current_cost);
958 END IF;
959
960
961 --UPDATE Grouped CIP Cost on newly assigned project asset
962
963 --Call procedure to add this amount to the Grouped CIP Amount of the asset
964 PA_FAXFACE.update_asset_cost
965 (asset_basis_table(i).PROJECT_ASSET_ID,
966 v_current_cost,
967 0, --- capitalized_cost
968 v_err_stage,
969 v_err_code);
970
971 IF v_err_code <> 0 THEN
972 RAISE error_calling_update_cost;
973 END IF;
974
975 v_src_project_asset_line_id := v_project_asset_line_id;
976
977 --Get the Sequence ID of the new asset line
978 SELECT pa_project_asset_lines_s.NEXTVAL
979 INTO v_project_asset_line_id
980 FROM SYS.DUAL;
981
982
983 --Create new UNASSIGNED asset line
984 INSERT INTO pa_project_asset_lines_all(
985 project_asset_line_id,
986 description,
987 project_asset_id,
988 project_id,
989 task_id,
990 cip_ccid,
991 asset_cost_ccid,
992 original_asset_cost,
993 current_asset_cost,
994 project_asset_line_detail_id,
995 gl_date,
996 transfer_status_code,
997 transfer_rejection_reason,
998 amortize_flag,
999 asset_category_id,
1000 last_update_date,
1001 last_updated_by,
1002 created_by,
1003 creation_date,
1004 last_update_login,
1005 request_id,
1006 program_application_id,
1007 program_id,
1008 program_update_date,
1009 rev_proj_asset_line_id,
1010 rev_from_proj_asset_line_id,
1011 org_id,
1012 invoice_number,
1013 vendor_number,
1014 po_vendor_id,
1015 po_number,
1016 invoice_date,
1017 invoice_created_by,
1018 invoice_updated_by,
1019 invoice_id,
1020 payables_batch_name,
1021 ap_distribution_line_number,
1022 original_asset_id,
1023 line_type,
1024 capital_event_id,
1025 retirement_cost_type
1026 )
1027 SELECT
1028 v_project_asset_line_id,
1029 pal_rec.description,
1030 0, --project_asset_id
1031 pal_rec.project_id,
1032 pal_rec.task_id,
1033 pal_rec.cip_ccid,
1034 pal_rec.asset_cost_ccid,
1035 pal_rec.original_asset_cost,
1036 v_remaining_cost,
1037 pal_rec.project_asset_line_detail_id,
1038 pal_rec.gl_date,
1039 pal_rec.transfer_status_code,
1040 pal_rec.transfer_rejection_reason,
1041 pal_rec.amortize_flag,
1042 pal_rec.asset_category_id,
1043 SYSDATE, --last_update_date
1044 v_user, --last_updated_by
1045 v_user, --created_by
1046 SYSDATE, --creation_date
1047 v_login,
1048 v_request_id,
1049 v_program_application_id,
1050 v_program_id,
1051 SYSDATE, --program_update_date
1052 pal_rec.rev_proj_asset_line_id,
1053 pal_rec.rev_from_proj_asset_line_id,
1054 pal_rec.org_id,
1055 pal_rec.invoice_number,
1056 pal_rec.vendor_number,
1057 pal_rec.po_vendor_id,
1058 pal_rec.po_number,
1059 pal_rec.invoice_date,
1060 pal_rec.invoice_created_by,
1061 pal_rec.invoice_updated_by,
1062 pal_rec.invoice_id,
1063 pal_rec.payables_batch_name,
1064 pal_rec.ap_distribution_line_number,
1065 pal_rec.original_asset_id,
1066 pal_rec.line_type,
1067 pal_rec.capital_event_id,
1068 pal_rec.retirement_cost_type
1069 FROM pa_project_asset_lines_all pal_rec
1070 WHERE project_asset_line_id = v_src_project_asset_line_id;
1071
1072 IF l_mrc_flag = 'Y' THEN /*Bug 4914051*/
1073 PA_FAXFACE.create_alc_proj_asset_lines (v_project_asset_line_id,
1074 v_project_asset_line_detail_id,
1075 v_rev_proj_asset_line_id,
1076 v_orig_cost,
1077 v_remaining_cost,
1078 v_err_stage,
1079 v_err_code);
1080 END IF;
1081
1082 END IF; --Last project asset ID or not
1083
1084 i := asset_basis_table.NEXT(i);
1085 END LOOP; --Allocate UNASSIGNED lines
1086
1087
1088
1089
1090 EXCEPTION
1091
1092
1093 WHEN no_assignments_exist THEN
1094 x_msg_data := 'No asset assignments exist for project id '||p_project_id;
1095 x_asset_or_project_err := 'P';
1096 x_err_asset_id := 0;
1097 x_error_code := 'NO_ASSIGNMENTS';
1098 x_return_status := 'E';
1099 x_msg_count := x_msg_count + 1;
1100 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_ASSET_ALLOCATION_PVT',
1101 p_procedure_name => 'ALLOCATE_UNASSIGNED',
1102 p_error_text => SUBSTRB(x_msg_data,1,240));
1103 RETURN;
1104
1105
1106 WHEN error_in_client_extn THEN
1107 x_msg_data := v_msg_data;
1108 --x_msg_data := 'Error in ASSET_ALLOC_BASIS client extension for project id '||p_project_id;
1109 x_asset_or_project_err := 'P';
1110 x_err_asset_id := 0;
1111 x_error_code := 'ASSET_ALLOC_BASIS_EXTN';
1112 x_return_status := 'E';
1113 x_msg_count := v_msg_count;
1114 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CLIENT_EXTN_ASSET_ALLOC',
1115 p_procedure_name => 'ASSET_ALLOC_BASIS',
1116 p_error_text => SUBSTRB(x_msg_data,1,240));
1117 RETURN;
1118
1119
1120 WHEN unexp_error_in_client_extn THEN
1121 x_msg_data := v_msg_data;
1122 --x_msg_data := 'Unexpected error in ASSET_ALLOC_BASIS client extension for project id '||p_project_id;
1123 x_return_status := 'U';
1124 x_msg_count := v_msg_count;
1125 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CLIENT_EXTN_ASSET_ALLOC',
1126 p_procedure_name => 'ASSET_ALLOC_BASIS',
1127 p_error_text => SUBSTRB(x_msg_data,1,240));
1128 ROLLBACK;
1129 RAISE;
1130
1131
1132 WHEN asset_not_on_project THEN
1133 x_msg_data := 'Project asset id '||v_project_asset_id||' not valid for project id '||p_project_id;
1134 x_asset_or_project_err := 'A';
1135 x_err_asset_id := v_project_asset_id;
1136 x_error_code := 'ASSET_INVALID_FOR_PROJECT';
1137 x_return_status := 'E';
1138 x_msg_count := x_msg_count + 1;
1139 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_ASSET_ALLOCATION_PVT',
1140 p_procedure_name => 'ALLOCATE_UNASSIGNED',
1141 p_error_text => SUBSTRB(x_msg_data,1,240));
1142 RETURN;
1143
1144
1145 WHEN asset_no_generation THEN
1146 x_msg_data := 'Project asset id '||v_project_asset_id||' is not eligible for asset line generation';
1147 x_asset_or_project_err := 'A';
1148 x_err_asset_id := v_project_asset_id;
1149 x_error_code := 'ASSET_NO_GENERATION';
1150 x_return_status := 'E';
1151 x_msg_count := x_msg_count + 1;
1152 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_ASSET_ALLOCATION_PVT',
1153 p_procedure_name => 'ALLOCATE_UNASSIGNED',
1154 p_error_text => SUBSTRB(x_msg_data,1,240));
1155 RETURN;
1156
1157
1158 WHEN asset_no_dpis THEN
1159 x_msg_data := 'Project asset id '||v_project_asset_id||' does not have an asset date specified';
1160 x_asset_or_project_err := 'A';
1161 x_err_asset_id := v_project_asset_id;
1162 x_error_code := 'ASSET_NO_DPIS';
1163 x_return_status := 'E';
1164 x_msg_count := x_msg_count + 1;
1165 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_ASSET_ALLOCATION_PVT',
1166 p_procedure_name => 'ALLOCATE_UNASSIGNED',
1167 p_error_text => SUBSTRB(x_msg_data,1,240));
1168 RETURN;
1169
1170
1171 WHEN asset_type_mismatch THEN
1172 x_msg_data := 'Project asset id '||v_project_asset_id||' is not valid for line type '||p_line_type;
1173 x_asset_or_project_err := 'A';
1174 x_err_asset_id := v_project_asset_id;
1175 x_error_code := 'ASSET_TYPE_MISMATCH';
1176 x_return_status := 'E';
1177 x_msg_count := x_msg_count + 1;
1178 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_ASSET_ALLOCATION_PVT',
1179 p_procedure_name => 'ALLOCATE_UNASSIGNED',
1180 p_error_text => SUBSTRB(x_msg_data,1,240));
1181 RETURN;
1182
1183
1184 WHEN zero_total_basis THEN
1185 x_msg_data := 'Total basis is ZERO for project id '||p_project_id||' using method '||p_asset_allocation_method;
1186 x_asset_or_project_err := 'P';
1187 x_err_asset_id := 0;
1188 x_error_code := 'ZERO_TOTAL_BASIS';
1189 x_return_status := 'E';
1190 x_msg_count := x_msg_count + 1;
1191 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_ASSET_ALLOCATION_PVT',
1192 p_procedure_name => 'ALLOCATE_UNASSIGNED',
1193 p_error_text => SUBSTRB(x_msg_data,1,240));
1194 x_return_status := 'E';
1195 RETURN;
1196
1197
1198 WHEN negative_total_basis THEN
1199 x_msg_data := 'Total basis is negative for project id '||p_project_id||' using method '||p_asset_allocation_method;
1200 x_asset_or_project_err := 'P';
1201 x_err_asset_id := 0;
1202 x_error_code := 'NEGATIVE_TOTAL_BASIS';
1203 x_return_status := 'E';
1204 x_msg_count := x_msg_count + 1;
1205 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_ASSET_ALLOCATION_PVT',
1206 p_procedure_name => 'ALLOCATE_UNASSIGNED',
1207 p_error_text => SUBSTRB(x_msg_data,1,240));
1208 RETURN;
1209
1210
1211 WHEN null_total_basis THEN
1212 x_msg_data := 'Total basis is NULL for project id '||p_project_id||' using method '||p_asset_allocation_method;
1213 x_asset_or_project_err := 'P';
1214 x_err_asset_id := 0;
1215 x_error_code := 'NULL_TOTAL_BASIS';
1216 x_return_status := 'E';
1217 x_msg_count := x_msg_count + 1;
1218 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_ASSET_ALLOCATION_PVT',
1219 p_procedure_name => 'ALLOCATE_UNASSIGNED',
1220 p_error_text => SUBSTRB(x_msg_data,1,240));
1221 RETURN;
1222
1223
1224 WHEN null_asset_basis THEN
1225 x_msg_data := 'Asset basis is NULL for project asset id '||v_project_asset_id||' using method '||p_asset_allocation_method;
1226 x_asset_or_project_err := 'A';
1227 x_err_asset_id := v_project_asset_id;
1228 x_error_code := 'NULL_ASSET_BASIS';
1229 x_return_status := 'E';
1230 x_msg_count := x_msg_count + 1;
1231 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_ASSET_ALLOCATION_PVT',
1232 p_procedure_name => 'ALLOCATE_UNASSIGNED',
1233 p_error_text => SUBSTRB(x_msg_data,1,240));
1234 x_return_status := 'E';
1235 RETURN;
1236
1237
1238 WHEN negative_asset_basis THEN
1239 x_msg_data := 'Asset basis is negative for project asset id '||v_project_asset_id||' using method '||p_asset_allocation_method;
1240 x_asset_or_project_err := 'A';
1241 x_err_asset_id := v_project_asset_id;
1242 x_error_code := 'NEGATIVE_ASSET_BASIS';
1243 x_return_status := 'E';
1244 x_msg_count := x_msg_count + 1;
1245 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_ASSET_ALLOCATION_PVT',
1246 p_procedure_name => 'ALLOCATE_UNASSIGNED',
1247 p_error_text => SUBSTRB(x_msg_data,1,240));
1248 RETURN;
1249
1250
1251 WHEN inconsistent_total_basis THEN
1252 x_msg_data := 'Total basis is inconsistent for project id '||p_project_id||' using method '||p_asset_allocation_method;
1253 x_asset_or_project_err := 'P';
1254 x_err_asset_id := 0;
1255 x_error_code := 'INCONSISTENT_TOTAL_BASIS';
1256 x_return_status := 'E';
1257 x_msg_count := x_msg_count + 1;
1258 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_ASSET_ALLOCATION_PVT',
1259 p_procedure_name => 'ALLOCATE_UNASSIGNED',
1260 p_error_text => SUBSTRB(x_msg_data,1,240));
1261 x_return_status := 'E';
1262 RETURN;
1263
1264
1265 WHEN asset_basis_sum_error THEN
1266 x_msg_data := 'Asset basis does not sum to Total basis for project id '||p_project_id||' using method '||p_asset_allocation_method;
1267 x_asset_or_project_err := 'P';
1268 x_err_asset_id := 0;
1269 x_error_code := 'ASSET_BASIS_SUM';
1270 x_return_status := 'E';
1271 x_msg_count := x_msg_count + 1;
1272 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_ASSET_ALLOCATION_PVT',
1273 p_procedure_name => 'ALLOCATE_UNASSIGNED',
1274 p_error_text => SUBSTRB(x_msg_data,1,240));
1275 RETURN;
1276
1277
1278 WHEN standard_cost_not_found THEN
1279 x_msg_data := 'Standard Cost not found for project asset id '||v_project_asset_id;
1280 x_asset_or_project_err := 'A';
1281 x_err_asset_id := v_project_asset_id;
1282 x_error_code := 'STANDARD_COST_MISSING';
1283 x_return_status := 'E';
1284 x_msg_count := x_msg_count + 1;
1285 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_ASSET_ALLOCATION_PVT',
1286 p_procedure_name => 'ALLOCATE_UNASSIGNED',
1287 p_error_text => SUBSTRB(x_msg_data,1,240));
1288 RETURN;
1289
1290
1291 WHEN error_calling_update_cost THEN
1292 x_return_status := 'U';
1293 x_msg_count := x_msg_count + 1;
1294 x_msg_data := v_err_code||' Error calling update_asset_cost for project asset id '||v_project_asset_id||' '||SQLERRM;
1295 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_ASSET_ALLOCATION_PVT',
1296 p_procedure_name => 'ALLOCATE_UNASSIGNED',
1297 p_error_text => SUBSTRB(x_msg_data,1,240));
1298 ROLLBACK;
1299 RAISE;
1300
1301
1302 WHEN OTHERS THEN
1303 x_return_status := 'U';
1304 x_msg_count := x_msg_count + 1;
1305 x_msg_data := 'Unexpected '||SQLCODE||' '||SQLERRM||' in ALLOCATE_UNASSIGNED for PROJECT ID: '||p_project_id;
1306 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_ASSET_ALLOCATION_PVT',
1307 p_procedure_name => 'ALLOCATE_UNASSIGNED',
1308 p_error_text => SUBSTRB(x_msg_data,1,240));
1309 ROLLBACK;
1310 RAISE;
1311
1312 END ALLOCATE_UNASSIGNED;
1313
1314
1315 END PA_ASSET_ALLOCATION_PVT;
[Home] [Help]