[Home] [Help]
PACKAGE BODY: APPS.IPA_APIS
Source
1 PACKAGE BODY ipa_apis AS
2 /* $Header: IPASRVB.pls 120.2 2006/02/14 12:14:24 dlanka noship $ */
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
278 ELSIF l_asset_info = 'P' THEN
279
280 l_project_id := NULL;
281
282 BEGIN
283 SELECT pp.project_id , pp.name ,
284 pp.segment1 , pp.project_type,
285 ppt.interface_asset_cost_code, ppt.interface_complete_asset_flag
286 INTO l_project_id , l_project_name ,
287 l_project_number, l_project_type,
288 l_interface_asset_cost_code , l_xface_complete_asset_flag
289 FROM pa_projects_all pp,
290 pa_project_types ppt
291 WHERE (pp.project_id = apis_rec.project_id
292 OR pp.name = apis_rec.project_name
293 OR pp.segment1 = apis_rec.project_number)
294 AND pp.project_type = ppt.project_type
295 AND PP.org_id = ppt.org_id ; -- Fix for bug: 4960534
296
297 EXCEPTION
298
299 WHEN no_data_found THEN
300 INSERT INTO ipa_apis_interface_errors
301 ( INTERFACE_ID ,
302 ERROR_MESSAGE
303 )
304 VALUES
305 ( apis_rec.interface_id ,
306 'NO RECORDS FOUND' ) ;
307 END ;
308
309 FOR asset_rec IN asset_cur( l_project_id )
310 LOOP
311
312 -- Call update procedure
313
314 Update_dpis(apis_rec.interface_id ,
315 l_project_id ,
316 asset_rec.project_asset_id ,
317 apis_rec.date_placed_in_service ,
318 l_xface_complete_asset_flag ,
319 asset_rec.book_type_code ,
320 asset_rec.asset_units ,
321 asset_rec.asset_category_id ,
322 asset_rec.location_id ,
323 asset_rec.depreciate_flag ,
324 asset_rec.depreciation_expense_ccid,
325 apis_rec.asset_status );
326
327 END LOOP;
328
329 ELSIF l_asset_info = 'G' THEN
330
331 BEGIN
332 SELECT pp.project_id , pp.name ,
333 pp.segment1 , pp.project_type,
334 ppt.interface_asset_cost_code, ppt.interface_complete_asset_flag
335 INTO l_project_id , l_project_name ,
336 l_project_number, l_project_type,
337 l_interface_asset_cost_code , l_xface_complete_asset_flag
338 FROM pa_projects_all pp,
339 pa_project_types ppt
340 WHERE (pp.project_id = apis_rec.project_id
341 OR pp.name = apis_rec.project_name
342 OR pp.segment1 = apis_rec.project_number)
343 AND pp.project_type = ppt.project_type
344 AND (apis_rec.project_id is not null OR
345 apis_rec.project_name is not null OR
346 apis_rec.project_number is not null)
347 AND PP.org_id = ppt.org_id ; -- Fix for bug: 4960534
348
349 EXCEPTION
350 WHEN no_data_found THEN
351 l_project_id := null;
352 END ;
353
354 /* Bug# 3018526. Commented this as this is not required. While fetching from the cursor
355 which makes use of the same statement, check if there are any records, thereby
356 avoiding the query.
357
358 SELECT count(*)
359 INTO l_count
360 FROM pa_project_assets_all
361 WHERE (
362 nvl(attribute8,'!@#') = nvl(apis_rec.grouping_method1,nvl(attribute8,'!@#'))
363 AND nvl(attribute9,'!@#') = nvl(apis_rec.grouping_method2,nvl(attribute9,'!@#'))
364 AND nvl(attribute10,'!@#') = nvl(apis_rec.grouping_method3,nvl(attribute10,'!@#'))
365 )
366 AND capitalized_flag <> 'Y'
367 AND project_id = nvl(l_project_id,project_id);
368 */
369 l_count := 0;
370
371 IF l_project_id is not null then
372 FOR project_rec in project_cur (
373 apis_rec.grouping_method1,
374 apis_rec.grouping_method2,
375 apis_rec.grouping_method3)
376 LOOP
377
378 -- Call update procedure.
379
380 l_count := l_count + 1;
381 Update_dpis(apis_rec.interface_id ,
382 project_rec.project_id ,
383 project_rec.project_asset_id ,
384 apis_rec.date_placed_in_service ,
385 l_xface_complete_asset_flag ,
386 project_rec.book_type_code ,
387 project_rec.asset_units ,
388 project_rec.asset_category_id ,
389 project_rec.location_id ,
390 project_rec.depreciate_flag ,
391 project_rec.depreciation_expense_ccid,
392 apis_rec.asset_status);
393
394 END LOOP ;
395 /* Bug# 3018526. Based on which grouping method is not null,
396 open that corresponding cursor */
397
398 elsif apis_rec.grouping_method1 is not null then
399 FOR project_rec in project_cur1 (
400 apis_rec.grouping_method1,
401 apis_rec.grouping_method2,
402 apis_rec.grouping_method3)
403 LOOP
404
405 -- Call update procedure.
406
407 l_count := l_count + 1;
408 Update_dpis(apis_rec.interface_id ,
409 project_rec.project_id ,
410 project_rec.project_asset_id ,
411 apis_rec.date_placed_in_service ,
412 l_xface_complete_asset_flag ,
413 project_rec.book_type_code ,
414 project_rec.asset_units ,
415 project_rec.asset_category_id ,
416 project_rec.location_id ,
417 project_rec.depreciate_flag ,
418 project_rec.depreciation_expense_ccid,
419 apis_rec.asset_status);
420
421 END LOOP ;
422 elsif apis_rec.grouping_method2 is not null then
423 FOR project_rec in project_cur2 (
424 apis_rec.grouping_method1,
425 apis_rec.grouping_method2,
426 apis_rec.grouping_method3)
427 LOOP
428
429 -- Call update procedure.
430
431 l_count := l_count + 1;
432 Update_dpis(apis_rec.interface_id ,
433 project_rec.project_id ,
434 project_rec.project_asset_id ,
435 apis_rec.date_placed_in_service ,
436 l_xface_complete_asset_flag ,
437 project_rec.book_type_code ,
438 project_rec.asset_units ,
439 project_rec.asset_category_id ,
440 project_rec.location_id ,
441 project_rec.depreciate_flag ,
442 project_rec.depreciation_expense_ccid,
443 apis_rec.asset_status);
444
445 END LOOP ;
446 elsif apis_rec.grouping_method3 is not null then
447 FOR project_rec in project_cur3 (
448 apis_rec.grouping_method1,
449 apis_rec.grouping_method2,
450 apis_rec.grouping_method3)
451 LOOP
452
453 -- Call update procedure.
454
455 l_count := l_count + 1;
456 Update_dpis(apis_rec.interface_id ,
457 project_rec.project_id ,
458 project_rec.project_asset_id ,
459 apis_rec.date_placed_in_service ,
460 l_xface_complete_asset_flag ,
461 project_rec.book_type_code ,
462 project_rec.asset_units ,
463 project_rec.asset_category_id ,
464 project_rec.location_id ,
465 project_rec.depreciate_flag ,
466 project_rec.depreciation_expense_ccid,
467 apis_rec.asset_status);
468
469 END LOOP ;
470 end if;
471
472 IF l_count = 0 THEN
473 INSERT INTO ipa_apis_interface_errors
474 ( INTERFACE_ID ,
475 ERROR_MESSAGE
476 )
477 VALUES
478 ( apis_rec.interface_id ,
479 'NO RECORDS FOUND' ) ;
480 END IF;
481
482 END IF;
483 END IF;
484 COMMIT;
485 END LOOP;
486 end summarize_dpis ;
487
488 -- This procedure finds expenditures related to given asset and updates
489 -- the Date placed in service.
490 /** Commented for CRL Rel 11.5.1 as it is obsoleted
491 procedure Update_expenditure_item
492 (i_project_id IN NUMBER )
493 IS
494
495 CURSOR asset_cur ( i_project_id NUMBER )
496 IS
497 SELECT project_asset_id , date_placed_in_service
498 FROM pa_project_assets_all
499 WHERE project_id = i_project_id
500 and date_placed_in_service is not null;
501
502 asset_rec asset_cur%ROWTYPE ;
503 l_project_id number ;
504 l_project_asset_id number ;
505 l_date_placed_in_service date ;
506
507 BEGIN
508
509 l_project_id := i_project_id ;
510
511 -- Get date placed in service from Asset table
512
513 FOR asset_rec IN asset_cur( l_project_id )
514 LOOP
515
516 UPDATE pa_expenditure_items_all
517 SET date_placed_in_service = asset_rec.date_placed_in_service
518 WHERE expenditure_item_id in (
519 SELECT det.expenditure_item_id
520 FROM pa_project_asset_lines_all line,
521 pa_project_asset_line_details det
522 WHERE line.project_asset_id = asset_rec.project_asset_id
523 AND line.project_asset_line_detail_id = det.project_asset_line_detail_id ) ;
524 END LOOP;
525
526 END update_expenditure_item ;
527 ******/
528 PROCEDURE update_dpis ( i_interface_id IN NUMBER ,
529 i_project_id IN NUMBER ,
530 i_project_asset_id IN NUMBER ,
531 i_date_placed_in_service IN DATE ,
532 i_xface_complete_asset_flag IN VARCHAR2,
533 i_book_type_code IN VARCHAR2,
534 i_asset_units IN VARCHAR2,
535 i_asset_category_id IN NUMBER,
536 i_asset_location_id IN NUMBER ,
537 i_depreciate_flag IN VARCHAR2,
538 i_depreciation_expense_ccid IN NUMBER,
539 i_asset_status IN VARCHAR2
540 )
541 IS
542
543 error_msg VARCHAR2(200);
544 warning_msg VARCHAR2(200);
545 l_incomplete_fail_flag VARCHAR2(2) ;
546 result NUMBER ;
547
548 /* Bug#3018526. Added variables to get the who column values */
549 l_request_id NUMBER := nvl(fnd_global.conc_request_id(), -1);
550 l_program_id NUMBER := nvl(fnd_global.conc_program_id(), -1);
551 l_update_login NUMBER := nvl(FND_GLOBAL.login_id, -1);
552
553 BEGIN
554 error_msg := ' ';
555 error_msg := 'Error in ' ;
556
557 IF i_xface_complete_asset_flag = 'Y' THEN
558
559 if i_asset_category_id is NULL THEN
560
561 l_incomplete_fail_flag := 'Y' ;
562 error_msg := error_msg || 'Asset category id, ' ;
563
564 end if;
565
566 if i_asset_units is NULL THEN
567
568 l_incomplete_fail_flag := 'Y' ;
569 error_msg := error_msg || 'Asset unit, ' ;
570
571 end if;
572
573 if i_asset_location_id is NULL THEN
574
575 l_incomplete_fail_flag := 'Y' ;
576 error_msg := error_msg || 'Asset Location id, ' ;
577
578 end if;
579
580 if i_depreciate_flag is NULL THEN
581
582 l_incomplete_fail_flag := 'Y' ;
583 error_msg := error_msg || 'Depreceate_flag, ' ;
584
585 end if;
586
587 if i_depreciation_expense_ccid is NULL THEN
588
589 l_incomplete_fail_flag := 'Y' ;
590 error_msg := error_msg || 'Depreciate Expense account ' ;
591
592 end if;
593
594 END IF;
595
596 IF nvl( l_incomplete_fail_flag,'N' ) <> 'Y' THEN
597
598 result := 0 ;
599 result := fa_mass_add_validate.valid_date_in_service
600 ( i_date_placed_in_service ,
601 i_book_type_code ) ;
602
603 if result < 0 then -- warning invalid dpis
604
605 INSERT INTO ipa_apis_interface_errors
606 ( INTERFACE_ID ,
607 PROJECT_ID ,
608 PROJECT_ASSET_ID ,
609 ERROR_MESSAGE
610 )
611 VALUES
612 ( i_interface_id ,
613 i_project_id ,
614 i_project_asset_id ,
615 'ORA-'||to_char(result) ) ;
616
617 elsif result = 0 then
618 warning_msg := '';
619 fnd_message.set_name ('PA','PA_CP_WRN_INVALID_DPIS' );
620 warning_msg := 'Error:'|| fnd_message.get;
621
622 INSERT INTO ipa_apis_interface_errors
623 ( INTERFACE_ID ,
624 PROJECT_ID ,
625 PROJECT_ASSET_ID ,
626 DATE_PLACED_IN_SERVICE ,
627 ASSET_STATUS ,
628 ERROR_MESSAGE
629 )
630 VALUES
631 ( i_interface_id ,
632 i_project_id ,
633 i_project_asset_id ,
634 i_date_placed_in_service,
635 i_asset_status ,
636 warning_msg) ;
637
638 elsif result = 1 then
639
640 UPDATE pa_project_assets_all
641 SET date_placed_in_service = i_date_placed_in_service,
642 attribute6 = i_asset_status
643 --Bug 3068204
644 ,project_asset_type = 'AS-BUILT'
645 /* Bug#3018526 Updating Who columns */
646 ,last_update_date = SYSDATE
647 ,last_updated_by = l_update_login
648 ,last_update_login = l_update_login
649 ,request_id = l_request_id
650 ,program_id = l_program_id
651 ,program_update_date= SYSDATE
652 WHERE project_id = i_project_id
653 AND project_asset_id = i_project_asset_id ;
654
655
656 IF ( SQL%ROWCOUNT = 0 ) THEN
657 INSERT INTO ipa_apis_interface_errors
658 ( INTERFACE_ID ,
659 PROJECT_ID ,
660 PROJECT_ASSET_ID ,
661 ERROR_MESSAGE
662 )
663 VALUES
664 ( i_interface_id ,
665 i_project_id ,
666 i_project_asset_id ,
667 'NO RECORDS FOUND' ) ;
668 END IF;
669 end if;
670
671 ELSE
672
673 INSERT INTO ipa_apis_interface_errors
674 ( INTERFACE_ID ,
675 PROJECT_ID ,
676 PROJECT_ASSET_ID ,
677 ERROR_MESSAGE
678 )
679 VALUES
680 ( i_interface_id ,
681 i_project_id ,
682 i_project_asset_id ,
683 error_msg ) ;
684
685 END IF;
686
687 update ipa_apis_interface
688 set record_status = 'PROCESSED'
689 where interface_id = i_interface_id ;
690
691 /* COMMIT; */
692
693 END update_dpis ;
694
695
696
697 END ipa_apis ;