[Home] [Help]
PACKAGE BODY: APPS.IPA_APIS
Source
1 PACKAGE BODY ipa_apis AS
2 /* $Header: IPASRVB.pls 120.2.12010000.2 2010/02/23 19:17:19 djanaswa ship $ */
3
4 PROCEDURE summarize_dpis (
5 errbug IN OUT NOCOPY VARCHAR2 ,
6 retcode IN OUT NOCOPY varchar2 ) IS
7 l_interface_asset_cost_code pa_project_types.interface_asset_cost_code%TYPE ;
8 l_xface_complete_asset_flag pa_project_types.interface_complete_asset_flag%TYPE;
9 l_earliest_fa_dpis DATE ;
10 l_project_id pa_projects.project_id%TYPE ;
11 l_project_number pa_projects.segment1%TYPE;
12 l_project_name pa_projects.name%TYPE ;
13 l_project_type pa_projects.project_type%TYPE ;
14 l_project_asset_id pa_project_assets.project_asset_id%TYPE ;
15 l_asset_number pa_project_assets.asset_number%TYPE;
16 l_asset_name pa_project_assets.asset_name%TYPE;
17 l_book_type_code pa_project_assets.book_type_code%TYPE;
18 l_asset_units pa_project_assets.asset_units%TYPE;
19 l_asset_category_id pa_project_assets.asset_category_id%TYPE;
20 l_asset_location_id pa_project_assets.location_id%TYPE;
21 l_depreciate_flag pa_project_assets.depreciate_flag%TYPE;
22 l_depreciation_expense_ccid pa_project_assets.depreciation_expense_ccid%TYPE;
23 l_asset_flag VARCHAR2(1) ;
24 l_asset_info VARCHAR2(1) ;
25 l_count NUMBER ;
26
27 result number ;
28 error_code number ;
29 error_msg varchar2(500) ;
30 x_error_msg varchar2(500) ;
31 incomplete_fail_flag varchar2(1) ;
32
33 CURSOR apis_intf IS
34 SELECT * FROM ipa_apis_interface
35 ORDER BY interface_id;
36
37 CURSOR project_cur( grouping_method1 VARCHAR2 ,
38 grouping_method2 VARCHAR2 ,
39 grouping_method3 VARCHAR2 )IS
40 SELECT project_id ,
41 project_asset_id , asset_name , asset_number,
42 book_type_code , asset_units ,
43 asset_category_id , location_id ,
44 depreciate_flag , depreciation_expense_ccid
45 FROM pa_project_assets_all
46 WHERE (nvl(attribute8,'!@#') = nvl(grouping_method1,nvl(attribute8,'!@#'))
47 AND nvl(attribute9,'!@#') = nvl(grouping_method2,nvl(attribute9,'!@#'))
48 AND nvl(attribute10,'!@#') = nvl(grouping_method3,nvl(attribute10,'!@#')))
49 AND project_id = l_project_id
50 AND capitalized_flag <> 'Y' ;
51
52 /* Bug#3018526 Split the project cursor into three cursors */
53 /* The intention is that atleast one of the grouping menthod element (1,2, 3)
54 has to be NOT NULL and use the query accordingly. The Function Based Indexes
55 (FBIs) are expected to be available, which are delivered in a Standalone.
56 See the Bug for more details
57 */
58 CURSOR project_cur1( grouping_method1 VARCHAR2 ,
59 grouping_method2 VARCHAR2 ,
60 grouping_method3 VARCHAR2 )IS
61 SELECT project_id ,
62 project_asset_id , asset_name , asset_number,
63 book_type_code , asset_units ,
64 asset_category_id , location_id ,
65 depreciate_flag , depreciation_expense_ccid
66 FROM pa_project_assets_all
67 WHERE (nvl(attribute8,'!@#') = grouping_method1
68 AND nvl(attribute9,'!@#') = nvl(grouping_method2,nvl(attribute9,'!@#'))
69 AND nvl(attribute10,'!@#') = nvl(grouping_method3,nvl(attribute10,'!@#')))
70 AND capitalized_flag <> 'Y' ;
71
72 CURSOR project_cur2( grouping_method1 VARCHAR2 ,
73 grouping_method2 VARCHAR2 ,
74 grouping_method3 VARCHAR2 )IS
75 SELECT project_id ,
76 project_asset_id , asset_name , asset_number,
77 book_type_code , asset_units ,
78 asset_category_id , location_id ,
79 depreciate_flag , depreciation_expense_ccid
80 FROM pa_project_assets_all
81 WHERE (nvl(attribute8,'!@#') = nvl(grouping_method1,nvl(attribute8,'!@#'))
82 AND nvl(attribute9,'!@#') = grouping_method2
83 AND nvl(attribute10,'!@#') = nvl(grouping_method3,nvl(attribute10,'!@#')))
84 AND capitalized_flag <> 'Y' ;
85
86 CURSOR project_cur3( grouping_method1 VARCHAR2 ,
87 grouping_method2 VARCHAR2 ,
88 grouping_method3 VARCHAR2 )IS
89 SELECT project_id ,
90 project_asset_id , asset_name , asset_number,
91 book_type_code , asset_units ,
92 asset_category_id , location_id ,
93 depreciate_flag , depreciation_expense_ccid
94 FROM pa_project_assets_all
95 WHERE (nvl(attribute8,'!@#') = nvl(grouping_method1,nvl(attribute8,'!@#'))
96 AND nvl(attribute9,'!@#') = nvl(grouping_method2,nvl(attribute9,'!@#'))
97 AND nvl(attribute10,'!@#') = grouping_method3)
98 AND capitalized_flag <> 'Y' ;
99
100 CURSOR asset_cur ( i_project_id NUMBER) IS
101 SELECT project_asset_id , asset_name , asset_number ,
102 book_type_code , asset_units ,
103 asset_category_id , location_id ,
104 depreciate_flag , depreciation_expense_ccid
105 FROM pa_project_assets_all
106 WHERE project_id = i_project_id
107 AND capitalized_flag <> 'Y' ;
108 apis_rec apis_intf%ROWTYPE ;
109 asset_rec asset_cur%ROWTYPE ;
110 project_rec project_cur%ROWTYPE ;
111
112
113 begin
114
115 -- Delete Old error records from error table
116 DELETE FROM ipa_apis_interface_errors;
117 COMMIT ;
118
119 FOR apis_rec in apis_intf
120 LOOP
121
122 x_error_msg := ' ' ;
123 error_msg := 'Value missing for ' ;
124 error_code := 0 ;
125 l_count := 0 ;
126 l_asset_flag := '';
127 incomplete_fail_flag := ' ' ;
128 result := 1;
129
130
131 IF nvl(apis_rec.project_id,1) = 1 AND
132 nvl(apis_rec.project_name,'X') = 'X' AND
133 nvl(apis_rec.project_number,'X') = 'X' AND
134 nvl(apis_rec.grouping_method1,'X') = 'X' AND
135 nvl(apis_rec.grouping_method2,'X') = 'X' AND
136 nvl(apis_rec.grouping_method3,'X') = 'X' AND
137 nvl(apis_rec.project_asset_id,1) = 1 AND
138 nvl(apis_rec.asset_number,'X') = 'X' AND
139 nvl(apis_rec.asset_name,'X') = 'X' THEN
140
141 l_asset_info := 'E' ;
142
143 END IF;
144
145
146 IF ( apis_rec.project_asset_id is NOT NULL OR
147 apis_rec.asset_number is NOT NULL OR
148 apis_rec.asset_name is NOT NULL )THEN
149 l_asset_info := 'A' ;
150
151 ELSIF ( apis_rec.grouping_method1 is NOT NULL OR
152 apis_rec.grouping_method2 is NOT NULL OR
153 apis_rec.grouping_method3 is NOT NULL ) THEN
154 l_asset_info := 'G' ;
155
156 ELSIF ( apis_rec.project_id is NOT NULL OR
157 apis_rec.project_name is NOT NULL OR
158 apis_rec.project_number is NOT NULL) THEN
159 l_asset_info := 'P' ;
160
161 END IF;
162
163
164 IF l_asset_info = 'E' THEN
165 x_error_msg := x_error_msg || 'Project , Asset , Grouping Method ' ;
166 error_code := 1 ;
167 END IF;
168
169
170 IF apis_rec.date_placed_in_service is NULL THEN
171
172 x_error_msg := x_error_msg || ' Date placed in service' ;
173 error_code := 1 ;
174
175 END IF;
176
177
178 IF error_code = 1 THEN -- ( privious error )
179
180 x_error_msg := x_error_msg || ' Values missing ' ;
181 INSERT INTO ipa_apis_interface_errors
182 ( INTERFACE_ID ,
183 BATCH_NAME ,
184 PROJECT_ID ,
185 PROJECT_ASSET_ID ,
186 ERROR_MESSAGE
187 )
188 VALUES
189 ( apis_rec.interface_id ,
190 apis_rec.batch_name ,
191 apis_rec.project_id ,
192 apis_rec.project_asset_id ,
193 x_error_msg ) ;
194 ELSE
195
196 IF l_interface_asset_cost_code = 'F' THEN
197
198 SELECT date_placed_in_service
199 INTO l_earliest_fa_dpis
200 FROM fa_system_controls
201 WHERE rownum < 2 ;
202
203 END IF;
204
205 IF apis_rec.date_placed_in_service < l_earliest_fa_dpis THEN
206
207 INSERT INTO ipa_apis_interface_errors
208 ( INTERFACE_ID ,
209 BATCH_NAME ,
210 PROJECT_ID ,
211 PROJECT_ASSET_ID ,
212 ERROR_MESSAGE
213 )
214 VALUES
215 ( apis_rec.interface_id ,
216 apis_rec.batch_name ,
217 apis_rec.project_id ,
218 apis_rec.project_asset_id ,
219 'Date place in service is earlier than FA System Date Placed in service' );
220
221 END IF;
222
223 IF l_asset_info = 'A' THEN
224
225 BEGIN
226 SELECT pp.project_id , pp.name ,
227 pp.segment1 , pp.project_type,
228 ppt.interface_asset_cost_code, ppt.interface_complete_asset_flag ,
229 pa.project_asset_id , pa.asset_name , pa.asset_number ,
230 pa.book_type_code , pa.asset_units ,
231 pa.asset_category_id , pa.location_id ,
232 pa.depreciate_flag , pa.depreciation_expense_ccid
233 INTO l_project_id , l_project_name ,
234 l_project_number, l_project_type,
235 l_interface_asset_cost_code , l_xface_complete_asset_flag,
236 l_project_asset_id ,l_asset_name , l_asset_number ,
237 l_book_type_code ,l_asset_units ,
238 l_asset_category_id ,l_asset_location_id ,
239 l_depreciate_flag ,l_depreciation_expense_ccid
240 FROM pa_project_assets_all pa,
241 pa_projects_all pp,
242 pa_project_types ppt
243 WHERE ( pa.project_asset_id = apis_rec.project_asset_id
244 OR pa.asset_number = apis_rec.asset_number
245 OR pa.asset_name = apis_rec.asset_name )
246 AND capitalized_flag <> 'Y'
247 AND pp.project_id = pa.project_id
248 AND pp.project_type = ppt.project_type
249 AND PP.org_id = ppt.org_id ; -- Fix for bug: 4960534
250 EXCEPTION
251 WHEN no_data_found THEN
252 INSERT INTO ipa_apis_interface_errors
253 ( INTERFACE_ID ,
254 PROJECT_ID ,
255 PROJECT_ASSET_ID ,
256 ERROR_MESSAGE
257 )
258 VALUES
259 ( apis_rec.interface_id ,
260 l_project_id ,
261 l_project_asset_id ,
262 'NO RECORDS FOUND' ) ;
263 END ;
264
265 Update_dpis(apis_rec.interface_id ,
266 l_project_id ,
267 l_project_asset_id ,
268 apis_rec.date_placed_in_service ,
269 l_xface_complete_asset_flag ,
270 l_book_type_code ,
271 l_asset_units ,
272 l_asset_category_id,
273 l_asset_location_id ,
274 l_depreciate_flag ,
275 l_depreciation_expense_ccid,
276 apis_rec.asset_status,
277 apis_rec.asset_units); -- added bug 9339798
278
279 ELSIF l_asset_info = 'P' THEN
280
281 l_project_id := NULL;
282
283 BEGIN
284 SELECT pp.project_id , pp.name ,
285 pp.segment1 , pp.project_type,
286 ppt.interface_asset_cost_code, ppt.interface_complete_asset_flag
287 INTO l_project_id , l_project_name ,
288 l_project_number, l_project_type,
289 l_interface_asset_cost_code , l_xface_complete_asset_flag
290 FROM pa_projects_all pp,
291 pa_project_types ppt
292 WHERE (pp.project_id = apis_rec.project_id
293 OR pp.name = apis_rec.project_name
294 OR pp.segment1 = apis_rec.project_number)
295 AND pp.project_type = ppt.project_type
296 AND PP.org_id = ppt.org_id ; -- Fix for bug: 4960534
297
298 EXCEPTION
299
300 WHEN no_data_found THEN
301 INSERT INTO ipa_apis_interface_errors
302 ( INTERFACE_ID ,
303 ERROR_MESSAGE
304 )
305 VALUES
306 ( apis_rec.interface_id ,
307 'NO RECORDS FOUND' ) ;
308 END ;
309
310 FOR asset_rec IN asset_cur( l_project_id )
311 LOOP
312
313 -- Call update procedure
314
315 Update_dpis(apis_rec.interface_id ,
316 l_project_id ,
317 asset_rec.project_asset_id ,
318 apis_rec.date_placed_in_service ,
319 l_xface_complete_asset_flag ,
320 asset_rec.book_type_code ,
321 asset_rec.asset_units ,
322 asset_rec.asset_category_id ,
323 asset_rec.location_id ,
324 asset_rec.depreciate_flag ,
325 asset_rec.depreciation_expense_ccid,
326 apis_rec.asset_status,
327 apis_rec.asset_units ); -- added bug 9339798
328
329 END LOOP;
330
331 ELSIF l_asset_info = 'G' THEN
332
333 BEGIN
334 SELECT pp.project_id , pp.name ,
335 pp.segment1 , pp.project_type,
336 ppt.interface_asset_cost_code, ppt.interface_complete_asset_flag
337 INTO l_project_id , l_project_name ,
338 l_project_number, l_project_type,
339 l_interface_asset_cost_code , l_xface_complete_asset_flag
340 FROM pa_projects_all pp,
341 pa_project_types ppt
342 WHERE (pp.project_id = apis_rec.project_id
343 OR pp.name = apis_rec.project_name
344 OR pp.segment1 = apis_rec.project_number)
345 AND pp.project_type = ppt.project_type
346 AND (apis_rec.project_id is not null OR
347 apis_rec.project_name is not null OR
348 apis_rec.project_number is not null)
349 AND PP.org_id = ppt.org_id ; -- Fix for bug: 4960534
350
351 EXCEPTION
352 WHEN no_data_found THEN
353 l_project_id := null;
354 END ;
355
356 /* Bug# 3018526. Commented this as this is not required. While fetching from the cursor
357 which makes use of the same statement, check if there are any records, thereby
358 avoiding the query.
359
360 SELECT count(*)
361 INTO l_count
362 FROM pa_project_assets_all
363 WHERE (
364 nvl(attribute8,'!@#') = nvl(apis_rec.grouping_method1,nvl(attribute8,'!@#'))
365 AND nvl(attribute9,'!@#') = nvl(apis_rec.grouping_method2,nvl(attribute9,'!@#'))
366 AND nvl(attribute10,'!@#') = nvl(apis_rec.grouping_method3,nvl(attribute10,'!@#'))
367 )
368 AND capitalized_flag <> 'Y'
369 AND project_id = nvl(l_project_id,project_id);
370 */
371 l_count := 0;
372
373 IF l_project_id is not null then
374 FOR project_rec in project_cur (
375 apis_rec.grouping_method1,
376 apis_rec.grouping_method2,
377 apis_rec.grouping_method3)
378 LOOP
379
380 -- Call update procedure.
381
382 l_count := l_count + 1;
383 Update_dpis(apis_rec.interface_id ,
384 project_rec.project_id ,
385 project_rec.project_asset_id ,
386 apis_rec.date_placed_in_service ,
387 l_xface_complete_asset_flag ,
388 project_rec.book_type_code ,
389 project_rec.asset_units ,
390 project_rec.asset_category_id ,
391 project_rec.location_id ,
392 project_rec.depreciate_flag ,
393 project_rec.depreciation_expense_ccid,
394 apis_rec.asset_status,
395 apis_rec.asset_units); -- added bug 9339798
396
397 END LOOP ;
398 /* Bug# 3018526. Based on which grouping method is not null,
399 open that corresponding cursor */
400
401 elsif apis_rec.grouping_method1 is not null then
402 FOR project_rec in project_cur1 (
403 apis_rec.grouping_method1,
404 apis_rec.grouping_method2,
405 apis_rec.grouping_method3)
406 LOOP
407
408 -- Call update procedure.
409
410 l_count := l_count + 1;
411 Update_dpis(apis_rec.interface_id ,
412 project_rec.project_id ,
413 project_rec.project_asset_id ,
414 apis_rec.date_placed_in_service ,
415 l_xface_complete_asset_flag ,
416 project_rec.book_type_code ,
417 project_rec.asset_units ,
418 project_rec.asset_category_id ,
419 project_rec.location_id ,
420 project_rec.depreciate_flag ,
421 project_rec.depreciation_expense_ccid,
422 apis_rec.asset_status,
423 apis_rec.asset_units); -- added bug 9339798
424
425 END LOOP ;
426 elsif apis_rec.grouping_method2 is not null then
427 FOR project_rec in project_cur2 (
428 apis_rec.grouping_method1,
429 apis_rec.grouping_method2,
430 apis_rec.grouping_method3)
431 LOOP
432
433 -- Call update procedure.
434
435 l_count := l_count + 1;
436 Update_dpis(apis_rec.interface_id ,
437 project_rec.project_id ,
438 project_rec.project_asset_id ,
439 apis_rec.date_placed_in_service ,
440 l_xface_complete_asset_flag ,
441 project_rec.book_type_code ,
442 project_rec.asset_units ,
443 project_rec.asset_category_id ,
444 project_rec.location_id ,
445 project_rec.depreciate_flag ,
446 project_rec.depreciation_expense_ccid,
447 apis_rec.asset_status,
448 apis_rec.asset_units); -- added bug 9339798
449
450 END LOOP ;
451 elsif apis_rec.grouping_method3 is not null then
452 FOR project_rec in project_cur3 (
453 apis_rec.grouping_method1,
454 apis_rec.grouping_method2,
455 apis_rec.grouping_method3)
456 LOOP
457
458 -- Call update procedure.
459
460 l_count := l_count + 1;
461 Update_dpis(apis_rec.interface_id ,
462 project_rec.project_id ,
463 project_rec.project_asset_id ,
464 apis_rec.date_placed_in_service ,
465 l_xface_complete_asset_flag ,
466 project_rec.book_type_code ,
467 project_rec.asset_units ,
468 project_rec.asset_category_id ,
469 project_rec.location_id ,
470 project_rec.depreciate_flag ,
471 project_rec.depreciation_expense_ccid,
472 apis_rec.asset_status,
473 apis_rec.asset_units); -- added bug 9339798
474
475 END LOOP ;
476 end if;
477
478 IF l_count = 0 THEN
479 INSERT INTO ipa_apis_interface_errors
480 ( INTERFACE_ID ,
481 ERROR_MESSAGE
482 )
483 VALUES
484 ( apis_rec.interface_id ,
485 'NO RECORDS FOUND' ) ;
486 END IF;
487
488 END IF;
489 END IF;
490 COMMIT;
491 END LOOP;
492 end summarize_dpis ;
493
494 -- This procedure finds expenditures related to given asset and updates
495 -- the Date placed in service.
496 /** Commented for CRL Rel 11.5.1 as it is obsoleted
497 procedure Update_expenditure_item
498 (i_project_id IN NUMBER )
499 IS
500
501 CURSOR asset_cur ( i_project_id NUMBER )
502 IS
503 SELECT project_asset_id , date_placed_in_service
504 FROM pa_project_assets_all
505 WHERE project_id = i_project_id
506 and date_placed_in_service is not null;
507
508 asset_rec asset_cur%ROWTYPE ;
509 l_project_id number ;
510 l_project_asset_id number ;
511 l_date_placed_in_service date ;
512
513 BEGIN
514
515 l_project_id := i_project_id ;
516
517 -- Get date placed in service from Asset table
518
519 FOR asset_rec IN asset_cur( l_project_id )
520 LOOP
521
522 UPDATE pa_expenditure_items_all
523 SET date_placed_in_service = asset_rec.date_placed_in_service
524 WHERE expenditure_item_id in (
525 SELECT det.expenditure_item_id
526 FROM pa_project_asset_lines_all line,
527 pa_project_asset_line_details det
528 WHERE line.project_asset_id = asset_rec.project_asset_id
529 AND line.project_asset_line_detail_id = det.project_asset_line_detail_id ) ;
530 END LOOP;
531
532 END update_expenditure_item ;
533 ******/
534 PROCEDURE update_dpis ( i_interface_id IN NUMBER ,
535 i_project_id IN NUMBER ,
536 i_project_asset_id IN NUMBER ,
537 i_date_placed_in_service IN DATE ,
538 i_xface_complete_asset_flag IN VARCHAR2,
539 i_book_type_code IN VARCHAR2,
540 i_asset_units IN NUMBER, -- datatype changed bug 9339798
541 i_asset_category_id IN NUMBER,
542 i_asset_location_id IN NUMBER ,
543 i_depreciate_flag IN VARCHAR2,
544 i_depreciation_expense_ccid IN NUMBER,
545 i_asset_status IN VARCHAR2,
546 i_xface_asset_units IN NUMBER -- added bug 9339798
547 )
548 IS
549
550 error_msg VARCHAR2(200);
551 warning_msg VARCHAR2(200);
552 l_incomplete_fail_flag VARCHAR2(2) ;
553 result NUMBER ;
554
555 /* Bug#3018526. Added variables to get the who column values */
556 l_request_id NUMBER := nvl(fnd_global.conc_request_id(), -1);
557 l_program_id NUMBER := nvl(fnd_global.conc_program_id(), -1);
558 l_update_login NUMBER := nvl(FND_GLOBAL.login_id, -1);
559
560 -- bug 9339798 start
561 CURSOR est_asset_units_cur ( i_project_asset_id NUMBER)
562 IS
563 SELECT estimated_asset_units
564 FROM pa_project_assets_all
565 WHERE project_asset_id = i_project_asset_id;
566
567 l_est_asset_units pa_project_assets.ESTIMATED_ASSET_UNITS%TYPE;
568 -- bug 9339798 end
569
570
571 BEGIN
572 error_msg := ' ';
573 error_msg := 'Error in ' ;
574
575 IF i_xface_complete_asset_flag = 'Y' THEN
576
577 if i_asset_category_id is NULL THEN
578
579 l_incomplete_fail_flag := 'Y' ;
580 error_msg := error_msg || 'Asset category id, ' ;
581
582 end if;
583
584 if i_asset_units is NULL THEN
585
586 l_incomplete_fail_flag := 'Y' ;
587 error_msg := error_msg || 'Asset unit, ' ;
588
589 end if;
590
591 if i_asset_location_id is NULL THEN
592
593 l_incomplete_fail_flag := 'Y' ;
594 error_msg := error_msg || 'Asset Location id, ' ;
595
596 end if;
597
598 if i_depreciate_flag is NULL THEN
599
600 l_incomplete_fail_flag := 'Y' ;
601 error_msg := error_msg || 'Depreceate_flag, ' ;
602
603 end if;
604
605 if i_depreciation_expense_ccid is NULL THEN
606
607 l_incomplete_fail_flag := 'Y' ;
608 error_msg := error_msg || 'Depreciate Expense account ' ;
609
610 end if;
611
612 END IF;
613
614 IF nvl( l_incomplete_fail_flag,'N' ) <> 'Y' THEN
615
616 result := 0 ;
617 result := fa_mass_add_validate.valid_date_in_service
618 ( i_date_placed_in_service ,
619 i_book_type_code ) ;
620
621 if result < 0 then -- warning invalid dpis
622
623 INSERT INTO ipa_apis_interface_errors
624 ( INTERFACE_ID ,
625 PROJECT_ID ,
626 PROJECT_ASSET_ID ,
627 ERROR_MESSAGE
628 )
629 VALUES
630 ( i_interface_id ,
631 i_project_id ,
632 i_project_asset_id ,
633 'ORA-'||to_char(result) ) ;
634
635 elsif result = 0 then
636 warning_msg := '';
637 fnd_message.set_name ('PA','PA_CP_WRN_INVALID_DPIS' );
638 warning_msg := 'Error:'|| fnd_message.get;
639
640 INSERT INTO ipa_apis_interface_errors
641 ( INTERFACE_ID ,
642 PROJECT_ID ,
643 PROJECT_ASSET_ID ,
644 DATE_PLACED_IN_SERVICE ,
645 ASSET_STATUS ,
646 ERROR_MESSAGE
647 )
648 VALUES
649 ( i_interface_id ,
650 i_project_id ,
651 i_project_asset_id ,
652 i_date_placed_in_service,
653 i_asset_status ,
654 warning_msg) ;
655
656 elsif result = 1 then
657 -- bug 9339798 start
658 IF (i_asset_units is NULL and i_xface_asset_units is NULL) THEN
659 OPEN est_asset_units_cur (i_project_asset_id);
660 FETCH est_asset_units_cur INTO l_est_asset_units;
661 CLOSE est_asset_units_cur;
662 END IF;
663 -- bug 9339798 end
664
665 UPDATE pa_project_assets_all
666 SET date_placed_in_service = i_date_placed_in_service,
667 attribute6 = i_asset_status
668 --Bug 3068204
669 ,project_asset_type = 'AS-BUILT'
670 ,asset_units = NVL(i_xface_asset_units,NVL(i_asset_units,l_est_asset_units)) -- added bug 9339798
671 /* Bug#3018526 Updating Who columns */
672 ,last_update_date = SYSDATE
673 ,last_updated_by = l_update_login
674 ,last_update_login = l_update_login
675 ,request_id = l_request_id
676 ,program_id = l_program_id
677 ,program_update_date= SYSDATE
678 WHERE project_id = i_project_id
679 AND project_asset_id = i_project_asset_id ;
680
681
682 IF ( SQL%ROWCOUNT = 0 ) THEN
683 INSERT INTO ipa_apis_interface_errors
684 ( INTERFACE_ID ,
685 PROJECT_ID ,
686 PROJECT_ASSET_ID ,
687 ERROR_MESSAGE
688 )
689 VALUES
690 ( i_interface_id ,
691 i_project_id ,
692 i_project_asset_id ,
693 'NO RECORDS FOUND' ) ;
694 END IF;
695 end if;
696
697 ELSE
698
699 INSERT INTO ipa_apis_interface_errors
700 ( INTERFACE_ID ,
701 PROJECT_ID ,
702 PROJECT_ASSET_ID ,
703 ERROR_MESSAGE
704 )
705 VALUES
706 ( i_interface_id ,
707 i_project_id ,
708 i_project_asset_id ,
709 error_msg ) ;
710
711 END IF;
712
713 update ipa_apis_interface
714 set record_status = 'PROCESSED'
715 where interface_id = i_interface_id ;
716
717 /* COMMIT; */
718
719 END update_dpis ;
720
721
722
723 END ipa_apis ;