[Home] [Help]
PACKAGE BODY: APPS.CSI_ASSET_PVT
Source
1 PACKAGE BODY csi_asset_pvt AS
2 /* $Header: csivaab.pls 120.18 2006/11/17 06:38:37 sumathur noship $ */
3
4 g_pkg_name varchar2(30) := 'CSI_ASSET_PVT';
5
6 PROCEDURE debug( p_message IN varchar2) IS
7 BEGIN
8 csi_gen_utility_pvt.put_line(p_message);
9 EXCEPTION
10 WHEN others THEN null;
11 END debug;
12
13 /*----------------------------------------------------------*/
14 /* Procedure name: Initialize_asset_rec */
15 /* Description : This procudure recontructs the record */
16 /* from the history */
17 /*----------------------------------------------------------*/
18 PROCEDURE Initialize_asset_rec_no_dump (
19 x_instance_asset_rec IN OUT NOCOPY csi_datastructures_pub.instance_asset_header_rec,
20 p_inst_asset_id IN NUMBER ,
21 x_no_dump IN OUT NOCOPY DATE)
22 IS
23
24 CURSOR Int_no_dump(p_inst_ass_id IN NUMBER ) IS
25 SELECT creation_date,
26 INSTANCE_ASSET_ID,
27 NEW_INSTANCE_ID,
28 NEW_FA_ASSET_ID,
29 NEW_ASSET_QUANTITY,
30 NEW_FA_BOOK_TYPE_CODE,
31 NEW_FA_LOCATION_ID,
32 NEW_UPDATE_STATUS,
33 NEW_ACTIVE_START_DATE,
34 NEW_ACTIVE_END_DATE,
35 FULL_DUMP_FLAG,
36 OBJECT_VERSION_NUMBER
37 FROM CSI_I_ASSETS_H
38 WHERE instance_asset_id = p_inst_ass_id
39 ORDER by creation_date;
40
41 BEGIN
42 FOR C1 IN Int_no_dump(p_inst_asset_id ) LOOP
43 IF Int_no_dump%ROWCOUNT = 1 THEN
44 x_no_dump := C1.creation_date;
45 x_instance_asset_rec.FA_ASSET_ID := C1.NEW_FA_ASSET_ID;
46 x_instance_asset_rec.ASSET_QUANTITY := C1.NEW_ASSET_QUANTITY;
47 x_instance_asset_rec.FA_BOOK_TYPE_CODE := C1.NEW_FA_BOOK_TYPE_CODE;
48 x_instance_asset_rec.FA_LOCATION_ID := C1.NEW_FA_LOCATION_ID;
49 x_instance_asset_rec.UPDATE_STATUS := C1.NEW_UPDATE_STATUS;
50 x_instance_asset_rec.ACTIVE_START_DATE := C1.NEW_ACTIVE_START_DATE;
51 x_instance_asset_rec.ACTIVE_END_DATE := C1.NEW_ACTIVE_END_DATE;
52 ELSE
53 EXIT;
54 END IF;
55 END LOOP;
56 END Initialize_asset_rec_no_dump ;
57
58 /*----------------------------------------------------------*/
59 /* Procedure name: Initialize_asset_rec */
60 /* Description : This procudure recontructs the record */
61 /* from the history */
62 /*----------------------------------------------------------*/
63
64 PROCEDURE Initialize_asset_rec
65 (
66 x_instance_asset_rec IN OUT NOCOPY csi_datastructures_pub.instance_asset_header_rec,
67 p_inst_asset_hist_id IN NUMBER ,
68 x_nearest_full_dump IN OUT NOCOPY DATE
69 ) IS
70
71 CURSOR Int_nearest_full_dump(p_inst_ass_hist_id IN NUMBER ) IS
72 SELECT
73 CREATION_DATE ,
74 INSTANCE_ASSET_ID ,
75 NEW_INSTANCE_ID ,
76 NEW_FA_ASSET_ID ,
77 NEW_ASSET_QUANTITY ,
78 NEW_FA_BOOK_TYPE_CODE ,
79 NEW_FA_LOCATION_ID ,
80 NEW_UPDATE_STATUS ,
81 NEW_ACTIVE_START_DATE ,
82 NEW_ACTIVE_END_DATE ,
83 FULL_DUMP_FLAG ,
84 OBJECT_VERSION_NUMBER
85 FROM CSI_I_ASSETS_H
86 WHERE instance_asset_history_id = p_inst_ass_hist_id
87 and full_dump_flag = 'Y' ;
88
89 BEGIN
90 FOR C1 IN Int_nearest_full_dump(p_inst_asset_hist_id ) LOOP
91 x_nearest_full_dump := C1.creation_date;
92 x_instance_asset_rec.FA_ASSET_ID := C1.NEW_FA_ASSET_ID;
93 x_instance_asset_rec.ASSET_QUANTITY := C1.NEW_ASSET_QUANTITY;
94 x_instance_asset_rec.FA_BOOK_TYPE_CODE := C1.NEW_FA_BOOK_TYPE_CODE;
95 x_instance_asset_rec.FA_LOCATION_ID := C1.NEW_FA_LOCATION_ID;
96 x_instance_asset_rec.UPDATE_STATUS := C1.NEW_UPDATE_STATUS;
97 x_instance_asset_rec.ACTIVE_START_DATE := C1.NEW_ACTIVE_START_DATE;
98 x_instance_asset_rec.ACTIVE_END_DATE := C1.NEW_ACTIVE_END_DATE;
99
100 END LOOP;
101 END Initialize_asset_rec ;
102
103
104 PROCEDURE set_fa_sync_flag (
105 px_instance_asset_rec IN OUT NOCOPY csi_datastructures_pub.instance_asset_rec,
106 p_location_id IN NUMBER,
107 x_return_status OUT NOCOPY VARCHAR2,
108 x_error_msg OUT NOCOPY VARCHAR2)
109 IS
110 l_mapped_fa_location_id NUMBER ;
111 l_cii_location_id NUMBER;
112 l_tot_fa_loc_units NUMBER ;
113 l_synced_fa_loc_units NUMBER ;
114 l_cii_location VARCHAR2(2000);
115 l_fa_location VARCHAR2(240);
116 l_sync_up_flag VARCHAR2(1);
117 --
118 -- Modified the query to look at the Mapping of Item Instance Location with FA Location.
119 -- Since Asset linking will be done only with the Item Instances that are at HZ or HR Locations
120 -- and these ID's are derived from the same Database sequence, the query has been simplified.
121 --
122 -- Following cursor will be used if p_location_id is not passed.
123 CURSOR csi_location_cur IS
124 SELECT decode(cii.location_type_code,'HZ_PARTY_SITES',
125 (select hzp.location_id
126 from hz_party_sites hzp
127 where hzp.party_site_id = cii.location_id),cii.location_id) cii_location_id
128 FROM csi_item_instances cii
129 WHERE cii.instance_id = px_instance_asset_rec.instance_id;
130 --
131 CURSOR csi_a_location_cur IS
132 SELECT fa_location_id
133 FROM csi_a_locations
134 WHERE location_id = l_cii_location_id;
135
136 CURSOR fa_location_units_cur IS
137 SELECT SUM(fdh.units_assigned)
138 FROM fa_distribution_history fdh
139 WHERE fdh.asset_id = px_instance_asset_rec.fa_asset_id
140 AND fdh.date_ineffective is null
141 AND fdh.location_id = px_instance_asset_rec.fa_location_id ;
142
143 CURSOR synced_fa_loc_units_cur IS
144 SELECT SUM(cia.asset_quantity)
145 FROM csi_i_assets cia
146 WHERE cia.fa_asset_id = px_instance_asset_rec.fa_asset_id
147 AND cia.fa_location_id = px_instance_asset_rec.fa_location_id
148 AND cia.asset_quantity > 0
149 AND sysdate between nvl(cia.active_start_date, sysdate-1) and nvl(cia.active_end_date, sysdate+1)
150 AND cia.update_status = 'IN_SERVICE'
151 AND cia.fa_sync_flag = 'Y' ;
152
153 CURSOR inst_sync_over_cur IS
154 SELECT cia.fa_sync_flag
155 FROM csi_i_assets cia
156 WHERE cia.fa_asset_id = px_instance_asset_rec.fa_asset_id
157 AND cia.fa_location_id = px_instance_asset_rec.fa_location_id
158 AND cia.instance_id = px_instance_asset_rec.instance_id
159 AND cia.asset_quantity > 0
160 AND sysdate between nvl(cia.active_start_date, sysdate-1) and nvl(cia.active_end_date, sysdate+1)
161 AND cia.update_status = 'IN_SERVICE';
162
163
164
165 BEGIN
166 x_return_status := fnd_api.G_RET_STS_SUCCESS ;
167
168 debug('set_fa_sync_flag');
169
170 OPEN inst_sync_over_cur;
171 FETCH inst_sync_over_cur
172 INTO l_sync_up_flag ;
173 CLOSE inst_sync_over_cur;
174
175 IF (NVL(l_sync_up_flag,'N') = 'Y') THEN
176 px_instance_asset_rec.fa_sync_flag := 'Y';
177 ELSE
178 IF nvl(p_location_id,-9999) = -9999 THEN -- Gets passed only from GRP API (Create)
179 OPEN csi_location_cur;
180 FETCH csi_location_cur
181 INTO l_cii_location_id;
182 CLOSE csi_location_cur;
183 ELSE
184 l_cii_location_id := p_location_id;
185 END IF;
186 --
187 OPEN csi_a_location_cur ;
188 FETCH csi_a_location_cur
189 INTO l_mapped_fa_location_id;
190 CLOSE csi_a_location_cur ;
191 --
192
193
194
195 csi_gen_utility_pvt.put_line('Mapp FA Loc ID : '|| l_mapped_fa_location_id);
196 IF px_instance_asset_rec.fa_location_id <> NVL(l_mapped_fa_location_id ,0) THEN
197 px_instance_asset_rec.fa_sync_flag := 'N' ;
198 -- Resolve FA Location
199 Begin
200 select concatenated_segments
201 into l_fa_location
202 from FA_LOCATIONS_KFV
203 where location_id = px_instance_asset_rec.fa_location_id;
204 Exception
205 when no_data_found then
206 null;
207 End;
208
209 --
210 -- Resolve CII Location
211 Begin
212 select location_code
213 into l_cii_location
214 from HR_LOCATIONS_ALL
215 where location_id = l_cii_location_id;
216 --
217 FND_MESSAGE.SET_NAME('CSI','CSI_FA_HR_LOCATION_MAP');
218 FND_MESSAGE.SET_TOKEN('FA_LOCATION',l_fa_location);
219 FND_MESSAGE.SET_TOKEN('HR_LOCATION',l_cii_location);
220 FND_MSG_PUB.Add;
221 Exception
222 when no_data_found then
223 Begin
224 select address1||','||address2||','||address3||','||address4||','||city||','||state||','||postal_code||','||country
225 into l_cii_location
226 from HZ_LOCATIONS
227 where location_id = l_cii_location_id;
228 --
229 FND_MESSAGE.SET_NAME('CSI','CSI_FA_HZ_LOCATION_MAP');
230 FND_MESSAGE.SET_TOKEN('FA_LOCATION',l_fa_location);
231 FND_MESSAGE.SET_TOKEN('HZ_LOCATION',l_cii_location);
232 FND_MSG_PUB.Add;
233 Exception
234 when no_data_found then
235 null;
236 End;
237 End;
238 ELSE
239
240 OPEN fa_location_units_cur ;
241 FETCH fa_location_units_cur INTO l_tot_fa_loc_units ;
242 CLOSE fa_location_units_cur ;
243
244 OPEN synced_fa_loc_units_cur ;
245 FETCH synced_fa_loc_units_cur INTO l_synced_fa_loc_units ;
246 CLOSE synced_fa_loc_units_cur ;
247
248 IF NVL(l_tot_fa_loc_units,0) >= NVL(l_synced_fa_loc_units,0) +
249 NVL(px_instance_asset_rec.asset_quantity,0)
250 THEN
251 px_instance_asset_rec.fa_sync_flag := 'Y' ;
252 ELSE
253 px_instance_asset_rec.fa_sync_flag := 'N' ;
254 FND_MESSAGE.SET_NAME('CSI','CSI_FA_CIA_UNITS_MISMATCH');
255 FND_MSG_PUB.Add;
256 END IF ;
257 END IF ; --px_instance_asset_rec.fa_location_id <> l_mapped_fa_location_id ;
258 END IF;
259 EXCEPTION
260 WHEN OTHERS THEN
261 x_return_status := fnd_api.G_RET_STS_ERROR ;
262 END set_fa_sync_flag;
263
264 /*----------------------------------------------------------*/
265 /* Procedure name: Construct_asset_from_hist */
266 /* Description : This procudure recontructs the record */
267 /* from the history */
268 /*----------------------------------------------------------*/
269
270 PROCEDURE Construct_asset_from_hist
271 (
272 x_instance_asset_tbl IN OUT NOCOPY csi_datastructures_pub.instance_asset_header_tbl,
273 p_time_stamp IN DATE
274 ) IS
275
276 l_nearest_full_dump DATE := p_time_stamp;
277 l_inst_asset_history_id NUMBER;
278 l_instance_asset_tbl csi_datastructures_pub.instance_asset_header_tbl;
279 l_asset_count NUMBER := 0;
280 --
281 Process_next EXCEPTION;
282
283
284 CURSOR get_nearest_full_dump(p_asset_id IN NUMBER ,p_time IN DATE) IS
285 SELECT
286 MAX(instance_asset_history_id)
287 FROM CSI_I_ASSETS_H
288 WHERE creation_date <= p_time
289 and instance_asset_id = p_asset_id
290 and full_dump_flag = 'Y';
291
292 CURSOR get_asset_label_hist(p_asset_id IN NUMBER ,
293 p_nearest_full_dump IN DATE,
294 p_time IN DATE ) IS
295 SELECT
296 INSTANCE_ASSET_ID ,
297 TRANSACTION_ID ,
298 OLD_INSTANCE_ID ,
299 NEW_INSTANCE_ID ,
300 OLD_FA_ASSET_ID ,
301 NEW_FA_ASSET_ID ,
302 OLD_ASSET_QUANTITY ,
303 NEW_ASSET_QUANTITY ,
304 OLD_FA_BOOK_TYPE_CODE ,
305 NEW_FA_BOOK_TYPE_CODE ,
306 OLD_FA_LOCATION_ID ,
307 NEW_FA_LOCATION_ID ,
308 OLD_UPDATE_STATUS ,
309 NEW_UPDATE_STATUS ,
310 FULL_DUMP_FLAG ,
311 OLD_ACTIVE_START_DATE ,
312 NEW_ACTIVE_START_DATE ,
313 OLD_ACTIVE_END_DATE ,
314 NEW_ACTIVE_END_DATE ,
315 OBJECT_VERSION_NUMBER
316 FROM CSI_I_ASSETS_H
317 WHERE creation_date <= p_time
318 and creation_date >= p_nearest_full_dump
319 and instance_asset_id = p_asset_id
320 ORDER BY creation_date;
321
322 l_time_stamp DATE := p_time_stamp;
323
324 BEGIN
325 l_instance_asset_tbl := x_instance_asset_tbl;
326 IF l_instance_asset_tbl.COUNT > 0 THEN
327
328 FOR i IN l_instance_asset_tbl.FIRST..l_instance_asset_tbl.LAST LOOP
329 BEGIN
330 OPEN get_nearest_full_dump(l_instance_asset_tbl(i).instance_asset_id, p_time_stamp);
331 FETCH get_nearest_full_dump INTO l_inst_asset_history_id;
332 CLOSE get_nearest_full_dump;
333
334 IF l_inst_asset_history_id IS NOT NULL THEN
335 Initialize_asset_rec( l_instance_asset_tbl(i), l_inst_asset_history_id ,l_nearest_full_dump);
336 ELSE
337 Initialize_asset_rec_no_dump(l_instance_asset_tbl(i), l_instance_asset_tbl(i).instance_asset_id, l_time_stamp);
338
339 l_nearest_full_dump := l_time_stamp;
340 -- If the user chooses a date before the creation date of the instance
341 -- then raise an error
342 IF p_time_stamp < l_time_stamp THEN
343 -- Messages Commented for bug 2423342. Records that do not qualify should get deleted.
344 -- FND_MESSAGE.SET_NAME('CSI','CSI_H_DATE_BEFORE_CRE_DATE');
345 -- FND_MESSAGE.SET_TOKEN('CREATION_DATE',to_char(l_time_stamp, 'DD-MON-YYYY HH24:MI:SS'));
346 -- FND_MESSAGE.SET_TOKEN('USER_DATE',to_char(p_time_stamp, 'DD-MON-YYYY HH24:MI:SS'));
347 -- FND_MSG_PUB.Add;
348 l_instance_asset_tbl.DELETE(i);
349 RAISE Process_next;
350 END IF;
351
352 END IF;
353
354 FOR C2 IN get_asset_label_hist(l_instance_asset_tbl(i).instance_asset_id, l_nearest_full_dump, p_time_stamp ) LOOP
355
356
357 IF (C2.OLD_FA_ASSET_ID IS NULL AND C2.NEW_FA_ASSET_ID IS NOT NULL)
358 OR (C2.OLD_FA_ASSET_ID IS NOT NULL AND C2.NEW_FA_ASSET_ID IS NULL)
359 OR (C2.OLD_FA_ASSET_ID <> C2.NEW_FA_ASSET_ID) THEN
360 l_instance_asset_tbl(i).FA_ASSET_ID := C2.NEW_FA_ASSET_ID;
361 END IF;
362
363 IF (C2.OLD_ASSET_QUANTITY IS NULL AND C2.NEW_ASSET_QUANTITY IS NOT NULL)
364 OR (C2.OLD_ASSET_QUANTITY IS NOT NULL AND C2.NEW_ASSET_QUANTITY IS NULL)
365 OR (C2.OLD_ASSET_QUANTITY <> C2.NEW_ASSET_QUANTITY) THEN
366 l_instance_asset_tbl(i).ASSET_QUANTITY := C2.NEW_ASSET_QUANTITY;
367 END IF;
368
369 IF (C2.OLD_FA_BOOK_TYPE_CODE IS NULL AND C2.NEW_FA_BOOK_TYPE_CODE IS NOT NULL)
370 OR (C2.OLD_FA_BOOK_TYPE_CODE IS NOT NULL AND C2.NEW_FA_BOOK_TYPE_CODE IS NULL)
371 OR (C2.OLD_FA_BOOK_TYPE_CODE <> C2.NEW_FA_BOOK_TYPE_CODE) THEN
372 l_instance_asset_tbl(i).FA_BOOK_TYPE_CODE := C2.NEW_FA_BOOK_TYPE_CODE;
373 END IF;
374
375 IF (C2.OLD_FA_LOCATION_ID IS NULL AND C2.NEW_FA_LOCATION_ID IS NOT NULL)
376 OR (C2.OLD_FA_LOCATION_ID IS NOT NULL AND C2.NEW_FA_LOCATION_ID IS NULL)
377 OR (C2.OLD_FA_LOCATION_ID <> C2.NEW_FA_LOCATION_ID) THEN
378 l_instance_asset_tbl(i).FA_LOCATION_ID := C2.NEW_FA_LOCATION_ID;
379 END IF;
380
381 IF (C2.OLD_UPDATE_STATUS IS NULL AND C2.NEW_UPDATE_STATUS IS NOT NULL)
382 OR (C2.OLD_UPDATE_STATUS IS NOT NULL AND C2.NEW_UPDATE_STATUS IS NULL)
383 OR (C2.OLD_UPDATE_STATUS <> C2.NEW_UPDATE_STATUS) THEN
384 l_instance_asset_tbl(i).UPDATE_STATUS := C2.NEW_UPDATE_STATUS;
385 END IF;
386
387 IF (C2.OLD_ACTIVE_START_DATE IS NULL AND C2.NEW_ACTIVE_START_DATE IS NOT NULL)
391 END IF;
388 OR (C2.OLD_ACTIVE_START_DATE IS NOT NULL AND C2.NEW_ACTIVE_START_DATE IS NULL)
389 OR (C2.OLD_ACTIVE_START_DATE <> C2.NEW_ACTIVE_START_DATE) THEN
390 l_instance_asset_tbl(i).ACTIVE_START_DATE := C2.NEW_ACTIVE_START_DATE;
392
393
394 IF (C2.OLD_ACTIVE_END_DATE IS NULL AND C2.NEW_ACTIVE_END_DATE IS NOT NULL)
395 OR (C2.OLD_ACTIVE_END_DATE IS NOT NULL AND C2.NEW_ACTIVE_END_DATE IS NULL)
396 OR (C2.OLD_ACTIVE_END_DATE <> C2.NEW_ACTIVE_END_DATE) THEN
397 l_instance_asset_tbl(i).ACTIVE_END_DATE := C2.NEW_ACTIVE_END_DATE;
398 END IF;
399
400
401 END LOOP;
402 EXCEPTION
403 WHEN Process_next THEN
404 NULL;
405 END;
406 END LOOP;
407 x_instance_asset_tbl.DELETE;
408 IF l_instance_asset_tbl.count > 0 THEN
409 FOR asset_row in l_instance_asset_tbl.FIRST .. l_instance_asset_tbl.LAST
410 LOOP
411 IF l_instance_asset_tbl.EXISTS(asset_row) THEN
412 l_asset_count := l_asset_count + 1;
413 x_instance_asset_tbl(l_asset_count) := l_instance_asset_tbl(asset_row);
414 END IF;
415 END LOOP;
416 END IF;
417 END IF;
418
419 END Construct_asset_from_hist;
420
421 /*----------------------------------------------------------*/
422 /* Procedure name: Resolve_id_columns */
423 /* Description : This procudure gets the descriptions for */
424 /* id columns */
425 /*----------------------------------------------------------*/
426
427 PROCEDURE Resolve_id_columns
428 (p_asset_header_tbl IN OUT NOCOPY csi_datastructures_pub.instance_asset_header_tbl)
429
430 IS
431 l_code_combination_id NUMBER;
432 l_assigned_to NUMBER;
433 BEGIN
434 l_code_combination_id := NULL;
435 l_assigned_to := NULL;
436 FOR tab_row in p_asset_header_tbl.FIRST..p_asset_header_tbl.LAST
437 LOOP
438
439 /* The following code has been commented for sql performance repository bug 4896250 */
440 /*
441 BEGIN
442 SELECT b.asset_number
443 ,b.serial_number
444 ,b.tag_number
445 ,d.concatenated_segments category
446 ,e.date_placed_in_service
447 ,b.description
448 ,f.name
449 ,g.concatenated_segments
450 INTO p_asset_header_tbl(tab_row).asset_number
451 ,p_asset_header_tbl(tab_row).serial_number
452 ,p_asset_header_tbl(tab_row).tag_number
453 ,p_asset_header_tbl(tab_row).category
454 ,p_asset_header_tbl(tab_row).date_placed_in_service
455 ,p_asset_header_tbl(tab_row).description
456 ,p_asset_header_tbl(tab_row).employee_name
457 ,p_asset_header_tbl(tab_row).expense_account_number
458 FROM fa_additions_vl b
459 ,fa_distribution_history c
460 ,fa_categories_b_kfv d
461 ,fa_books e
462 ,fa_employees f
463 ,gl_code_combinations_kfv g
464 WHERE b.asset_id = c.asset_id
465 AND b.asset_category_id = d.category_id
466 AND b.asset_id = e.asset_id
467 AND c.book_type_code = e.book_type_code
468 AND c.assigned_to = f.employee_id(+)
469 AND c.code_combination_id = g.code_combination_id
470 --AND c.date_ineffective IS NULL -- Commented for bug 4206038
471 -- Added for Bug: 3903805
472 --AND e.date_ineffective IS NULL -- Commented for bug 4206038
473 AND e.book_type_code = p_asset_header_tbl(tab_row).fa_book_type_code
474 AND c.location_id = p_asset_header_tbl(tab_row).fa_location_id
475 -- End of addition for Bug: 3903805
476 AND b.asset_id = p_asset_header_tbl(tab_row).fa_asset_id
477 AND rownum < 2; -- Added for Bug: 3903805
478 EXCEPTION
479 WHEN OTHERS THEN
480 NULL;
481 END;
482 */
483
484 BEGIN
485 SELECT b.asset_number
486 ,b.serial_number
487 ,b.tag_number
488 ,d.concatenated_segments category
489 ,e.date_placed_in_service
490 ,b.description
491 ,c.code_combination_id
492 ,c.assigned_to
493 INTO p_asset_header_tbl(tab_row).asset_number
494 ,p_asset_header_tbl(tab_row).serial_number
495 ,p_asset_header_tbl(tab_row).tag_number
496 ,p_asset_header_tbl(tab_row).category
497 ,p_asset_header_tbl(tab_row).date_placed_in_service
498 ,p_asset_header_tbl(tab_row).description
499 ,l_code_combination_id
500 ,l_assigned_to
501 FROM fa_additions_vl b
502 ,fa_distribution_history c
503 ,fa_categories_b_kfv d
504 ,fa_books e
505 WHERE b.asset_id = c.asset_id
506 AND b.asset_category_id = d.category_id
507 AND b.asset_id = e.asset_id
511 AND b.asset_id = p_asset_header_tbl(tab_row).fa_asset_id
508 AND c.book_type_code = e.book_type_code
509 AND e.book_type_code = p_asset_header_tbl(tab_row).fa_book_type_code
510 AND c.location_id = p_asset_header_tbl(tab_row).fa_location_id
512 AND rownum < 2;
513 EXCEPTION
514 WHEN OTHERS THEN
515 NULL;
516 END;
517
518
519 BEGIN
520 SELECT concatenated_segments
521 INTO p_asset_header_tbl(tab_row).expense_account_number
522 FROM gl_code_combinations_kfv
523 WHERE code_combination_id = l_code_combination_id;
524 l_code_combination_id:=NULL;
525 EXCEPTION
526 WHEN OTHERS THEN
527 NULL;
528 END;
529
530 BEGIN
531 SELECT name
532 INTO p_asset_header_tbl(tab_row).employee_name
533 FROM fa_employees
534 WHERE employee_id=l_assigned_to
535 AND rownum<2;
536 l_assigned_to:=NULL;
537 EXCEPTION
538 WHEN OTHERS THEN
539 NULL;
540 END;
541
542 -- asset location validation
543 BEGIN
544 SELECT segment1,
545 segment2,
546 segment3,
547 segment4,
548 segment5,
549 segment6,
550 segment7
551 INTO p_asset_header_tbl(tab_row).fa_location_segment1,
552 p_asset_header_tbl(tab_row).fa_location_segment2,
553 p_asset_header_tbl(tab_row).fa_location_segment3,
554 p_asset_header_tbl(tab_row).fa_location_segment4,
555 p_asset_header_tbl(tab_row).fa_location_segment5,
556 p_asset_header_tbl(tab_row).fa_location_segment6,
557 p_asset_header_tbl(tab_row).fa_location_segment7
558 FROM fa_locations
559 WHERE location_id = p_asset_header_tbl(tab_row).fa_location_id;
560 EXCEPTION
561 WHEN OTHERS THEN
562 NULL;
563 END;
564 END LOOP;
565 END Resolve_id_columns;
566
567 /*----------------------------------------------------------*/
568 /* Procedure name: Get_Asset_Column_Values */
569 /* Description : This procudure gets the column values */
570 /* for the Dynamic SQL */
571 /*----------------------------------------------------------*/
572
573 PROCEDURE Get_Asset_Column_Values
574 (
575 p_get_asset_cursor_id IN NUMBER ,
576 x_inst_asset_rec OUT NOCOPY csi_datastructures_pub.instance_asset_header_rec
577 )IS
578 BEGIN
579
580 dbms_sql.column_value(p_get_asset_cursor_id, 1, x_inst_asset_rec.instance_asset_id);
581 dbms_sql.column_value(p_get_asset_cursor_id, 2, x_inst_asset_rec.instance_id);
582 dbms_sql.column_value(p_get_asset_cursor_id, 3, x_inst_asset_rec.fa_asset_id);
583 dbms_sql.column_value(p_get_asset_cursor_id, 4, x_inst_asset_rec.fa_book_type_code );
584 dbms_sql.column_value(p_get_asset_cursor_id, 5, x_inst_asset_rec.fa_location_id);
585 dbms_sql.column_value(p_get_asset_cursor_id, 6, x_inst_asset_rec.asset_quantity);
586 dbms_sql.column_value(p_get_asset_cursor_id, 7, x_inst_asset_rec.update_status);
587 dbms_sql.column_value(p_get_asset_cursor_id, 8, x_inst_asset_rec.active_start_date);
588 dbms_sql.column_value(p_get_asset_cursor_id, 9, x_inst_asset_rec.active_end_date);
589 dbms_sql.column_value(p_get_asset_cursor_id, 10, x_inst_asset_rec.object_version_number);
590
591 END Get_Asset_Column_Values;
592
593
594 /*----------------------------------------------------------*/
595 /* Procedure name: Define_Asset_Columns */
596 /* Description : This procudure defines the columns */
597 /* for the Dynamic SQL */
598 /*----------------------------------------------------------*/
599
600 PROCEDURE Define_Asset_Columns
601 (
602 p_get_asset_cursor_id IN NUMBER
603 ) IS
604
605 l_inst_asset_rec csi_datastructures_pub.instance_asset_header_rec;
606
607 BEGIN
608
609 dbms_sql.define_column(p_get_asset_cursor_id, 1, l_inst_asset_rec.instance_asset_id);
610 dbms_sql.define_column(p_get_asset_cursor_id, 2, l_inst_asset_rec.instance_id);
611 dbms_sql.define_column(p_get_asset_cursor_id, 3, l_inst_asset_rec.fa_asset_id);
612 dbms_sql.define_column(p_get_asset_cursor_id, 4, l_inst_asset_rec.fa_book_type_code,30 );
613 dbms_sql.define_column(p_get_asset_cursor_id, 5, l_inst_asset_rec.fa_location_id);
614 dbms_sql.define_column(p_get_asset_cursor_id, 6, l_inst_asset_rec.asset_quantity);
615 dbms_sql.define_column(p_get_asset_cursor_id, 7, l_inst_asset_rec.update_status,30);
616 dbms_sql.define_column(p_get_asset_cursor_id, 8, l_inst_asset_rec.active_start_date);
617 dbms_sql.define_column(p_get_asset_cursor_id, 9, l_inst_asset_rec.active_end_date);
618 dbms_sql.define_column(p_get_asset_cursor_id, 10, l_inst_asset_rec.object_version_number);
619
620 END Define_Asset_Columns;
621
622 /*----------------------------------------------------------*/
623 /* Procedure name: Bind_asset_variable */
624 /* Description : Procedure used to generate the where */
625 /* cluase for Party relationship */
626 /*----------------------------------------------------------*/
630 p_inst_asset_query_rec IN csi_datastructures_pub.instance_asset_query_rec,
627
628 PROCEDURE Bind_asset_variable
629 (
631 p_get_asset_cursor_id IN NUMBER
632 )IS
633
634 BEGIN
635 IF( (p_inst_asset_query_rec.instance_asset_id IS NOT NULL)
636 AND (p_inst_asset_query_rec.instance_asset_id <> FND_API.G_MISS_NUM)) THEN
637 DBMS_SQL.BIND_VARIABLE(p_get_asset_cursor_id, ':instance_asset_id', p_inst_asset_query_rec.instance_asset_id);
638 END IF;
639
640 IF( (p_inst_asset_query_rec.instance_id IS NOT NULL)
641 AND (p_inst_asset_query_rec.instance_id <> FND_API.G_MISS_NUM)) THEN
642 DBMS_SQL.BIND_VARIABLE(p_get_asset_cursor_id, ':instance_id', p_inst_asset_query_rec.instance_id);
643 END IF;
644
645 IF( (p_inst_asset_query_rec.fa_asset_id IS NOT NULL)
646 AND (p_inst_asset_query_rec.fa_asset_id <> FND_API.G_MISS_NUM)) THEN
647 DBMS_SQL.BIND_VARIABLE(p_get_asset_cursor_id, ':fa_asset_id', p_inst_asset_query_rec.fa_asset_id);
648 END IF;
649
650 IF( (p_inst_asset_query_rec.fa_book_type_code IS NOT NULL)
651 AND (p_inst_asset_query_rec.fa_book_type_code <> FND_API.G_MISS_CHAR)) THEN
652 DBMS_SQL.BIND_VARIABLE(p_get_asset_cursor_id, ':fa_book_type_code', p_inst_asset_query_rec.fa_book_type_code);
653 END IF;
654
655 IF( (p_inst_asset_query_rec.fa_location_id IS NOT NULL)
656 AND (p_inst_asset_query_rec.fa_location_id <> FND_API.G_MISS_NUM)) THEN
657 DBMS_SQL.BIND_VARIABLE(p_get_asset_cursor_id, ':fa_location_id', p_inst_asset_query_rec.fa_location_id);
658 END IF;
659 IF( (p_inst_asset_query_rec.update_status IS NOT NULL)
660 AND (p_inst_asset_query_rec.update_status <> FND_API.G_MISS_CHAR)) THEN
661 DBMS_SQL.BIND_VARIABLE(p_get_asset_cursor_id, ':update_status', p_inst_asset_query_rec.update_status);
662 END IF;
663
664 END Bind_asset_variable;
665
666
667 /*----------------------------------------------------------*/
668 /* Procedure name: Gen_Asset_Where_Clause */
669 /* Description : Procedure used to generate the where */
670 /* cluase for Party relationship */
671 /*----------------------------------------------------------*/
672
673 PROCEDURE Gen_Asset_Where_Clause
674 ( p_inst_asset_query_rec IN csi_datastructures_pub.instance_asset_query_rec
675 ,x_where_clause OUT NOCOPY VARCHAR2
676 ) IS
677
678 BEGIN
679 -- Assign null at the start
680 x_where_clause := '';
681
682 IF (( p_inst_asset_query_rec.instance_asset_id IS NOT NULL) AND
683 ( p_inst_asset_query_rec.instance_asset_id <> FND_API.G_MISS_NUM)) THEN
684 x_where_clause := ' instance_asset_id = :instance_asset_id ';
685 ELSIF ( p_inst_asset_query_rec.instance_asset_id IS NULL) THEN
686 x_where_clause := ' instance_asset_id IS NULL ';
687 END IF;
688
689 IF ((p_inst_asset_query_rec.instance_id IS NOT NULL) AND
690 (p_inst_asset_query_rec.instance_id <> FND_API.G_MISS_NUM)) THEN
691 IF x_where_clause IS NULL THEN
692 x_where_clause := ' instance_id = :instance_id ';
693 ELSE
694 x_where_clause := x_where_clause||' AND '||' instance_id = :instance_id ';
695 END IF;
696 ELSIF (p_inst_asset_query_rec.instance_id IS NULL) THEN
697 IF x_where_clause IS NULL THEN
698 x_where_clause := ' instance_id IS NULL ';
699 ELSE
700 x_where_clause := x_where_clause||' AND '||' instance_id IS NULL ';
701 END IF;
702 END IF;
703
704 IF ((p_inst_asset_query_rec.fa_asset_id IS NOT NULL) AND
705 (p_inst_asset_query_rec.fa_asset_id <> FND_API.G_MISS_NUM)) THEN
706 IF x_where_clause IS NULL THEN
707 x_where_clause := ' fa_asset_id = :fa_asset_id ';
708 ELSE
709 x_where_clause := x_where_clause||' AND '||' fa_asset_id = :fa_asset_id ';
710 END IF;
711 ELSIF (p_inst_asset_query_rec.fa_asset_id IS NULL) THEN
712 IF x_where_clause IS NULL THEN
713 x_where_clause := ' fa_asset_id IS NULL ';
714 ELSE
715 x_where_clause := x_where_clause||' AND '||' fa_asset_id IS NULL ';
716 END IF;
717 END IF ;
718 IF ((p_inst_asset_query_rec.fa_book_type_code IS NOT NULL) AND
719 (p_inst_asset_query_rec.fa_book_type_code <> FND_API.G_MISS_CHAR)) THEN
720 IF x_where_clause IS NULL THEN
721 x_where_clause := ' fa_book_type_code = :fa_book_type_code ';
722 ELSE
723 x_where_clause := x_where_clause||' AND '||
724 ' fa_book_type_code = :fa_book_type_code ';
725 END IF;
726 ELSIF (p_inst_asset_query_rec.fa_book_type_code IS NULL) THEN
727 IF x_where_clause IS NULL THEN
728 x_where_clause := ' fa_book_type_code IS NULL ';
729 ELSE
730 x_where_clause := x_where_clause||' AND '||
731 ' fa_book_type_code IS NULL ';
732 END IF;
733 END IF;
734
735 IF ((p_inst_asset_query_rec.fa_location_id IS NOT NULL) AND
736 (p_inst_asset_query_rec.fa_location_id <> FND_API.G_MISS_NUM)) THEN
737 IF x_where_clause IS NULL THEN
738 x_where_clause := ' fa_location_id = :fa_location_id ';
739 ELSE
740 x_where_clause := x_where_clause||' AND '||
741 ' fa_location_id = :fa_location_id ';
742 END IF;
743 ELSIF (p_inst_asset_query_rec.fa_location_id IS NULL) THEN
747 x_where_clause := x_where_clause||' AND '||
744 IF x_where_clause IS NULL THEN
745 x_where_clause := ' fa_location_id IS NULL ';
746 ELSE
748 ' fa_location_id IS NULL ';
749 END IF;
750 END IF;
751
752 IF ((p_inst_asset_query_rec.update_status IS NOT NULL) AND
753 (p_inst_asset_query_rec.update_status <> FND_API.G_MISS_CHAR)) THEN
754 IF x_where_clause IS NULL THEN
755 x_where_clause := ' update_status = :update_status ';
756 ELSE
757 x_where_clause := x_where_clause||' AND '||
758 ' update_status = :update_status ';
759 END IF;
760 ELSIF (p_inst_asset_query_rec.update_status IS NULL) THEN
761 IF x_where_clause IS NULL THEN
762 x_where_clause := ' update_status IS NULL ';
763 ELSE
764 x_where_clause := x_where_clause||' AND '||
765 ' update_status IS NULL ';
766 END IF;
767 END IF;
768
769 END Gen_Asset_Where_Clause;
770
771
772 /*-------------------------------------------------------*/
773 /* procedure name: get_instance_assets */
774 /* description : Get information about the assets */
775 /* associated with an item instance. */
776 /*-------------------------------------------------------*/
777 PROCEDURE get_instance_assets (
778 p_api_version IN NUMBER
779 ,p_commit IN VARCHAR2
780 ,p_init_msg_list IN VARCHAR2
781 ,p_validation_level IN NUMBER
782 ,p_instance_asset_query_rec IN csi_datastructures_pub.instance_asset_query_rec
783 ,p_resolve_id_columns IN VARCHAR2
784 ,p_time_stamp IN DATE
785 ,x_instance_asset_tbl OUT NOCOPY csi_datastructures_pub.instance_asset_header_tbl
786 ,x_return_status OUT NOCOPY VARCHAR2
787 ,x_msg_count OUT NOCOPY NUMBER
788 ,x_msg_data OUT NOCOPY VARCHAR2
789 ) IS
790
791 l_api_name CONSTANT VARCHAR2(30) := 'get_instance_asset';
792 l_api_version CONSTANT NUMBER := 1.0;
793 l_CSI_DEBUG_LEVEL NUMBER;
794 l_instance_asset_rec csi_datastructures_pub.instance_asset_rec;
795 l_msg_index NUMBER;
796 l_msg_count NUMBER;
797 l_VERSION_LABEL_ID NUMBER := NULL;
798 l_count NUMBER := 0;
799 l_instance_asset_id NUMBER;
800 l_where_clause VARCHAR2(2000) := '' ;
801 l_get_inst_asset_cursor_id NUMBER ;
802 l_inst_asset_rec csi_datastructures_pub.instance_asset_header_rec ;
803 l_rows_processed NUMBER ;
804 l_select_stmt VARCHAR2(20000):= ' SELECT INSTANCE_ASSET_ID,INSTANCE_ID,FA_ASSET_ID,FA_BOOK_TYPE_CODE '||
805 ' ,FA_LOCATION_ID,ASSET_QUANTITY,UPDATE_STATUS,ACTIVE_START_DATE,ACTIVE_END_DATE , '||
806 ' OBJECT_VERSION_NUMBER FROM CSI_I_ASSETS ';
807 l_instance_asset_tbl csi_datastructures_pub.instance_asset_header_tbl;
808
809 BEGIN
810 -- Standard Start of API savepoint
811 /*
812 IF fnd_api.to_boolean(p_commit)
813 THEN
814 SAVEPOINT get_instance_asset_pvt;
815 END IF;
816 */
817 -- Standard call to check for call compatibility.
818 IF NOT FND_API.Compatible_API_Call (l_api_version ,
819 p_api_version ,
820 l_api_name ,
821 G_PKG_NAME )
822 THEN
823 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
824 END IF;
825
826 -- Initialize message list if p_init_msg_list is set to TRUE.
827 IF FND_API.to_Boolean( p_init_msg_list ) THEN
828 FND_MSG_PUB.initialize;
829 END IF;
830
831 -- Initialize API return status to success
832 x_return_status := FND_API.G_RET_STS_SUCCESS;
833
834
835 -- Check the profile option CSI_DEBUG_LEVEL for debug message reporting
836 l_CSI_DEBUG_LEVEL:=fnd_profile.value('CSI_DEBUG_LEVEL');
837
838 IF (l_CSI_DEBUG_LEVEL > 0) THEN
839 debug( 'get_instance_asset');
840 END IF;
841
842 -- If the debug level = 2 then dump all the parameters values.
843 IF (l_CSI_DEBUG_LEVEL > 1) THEN
844 debug(p_api_version ||'-'|| p_commit||'-'||p_init_msg_list||'-'||p_validation_level);
845 csi_gen_utility_pvt.dump_asset_query_rec(p_instance_asset_query_rec);
846 END IF;
847
848 -- check if atleast one query parameters are passed
849 IF (p_instance_asset_query_rec.instance_asset_id = FND_API.G_MISS_NUM)
850 AND (p_instance_asset_query_rec.instance_id = FND_API.G_MISS_NUM)
851 AND (p_instance_asset_query_rec.fa_asset_id = FND_API.G_MISS_NUM)
852 AND (p_instance_asset_query_rec.fa_book_type_code = FND_API.G_MISS_CHAR)
853 AND (p_instance_asset_query_rec.fa_location_id = FND_API.G_MISS_NUM)
854 AND (p_instance_asset_query_rec.update_status = FND_API.G_MISS_CHAR) THEN
855
856 FND_MESSAGE.Set_Name('CSI', 'CSI_API_INVALID_PARAMETERS');
857 FND_MSG_PUB.ADD;
858 RAISE FND_API.G_EXC_ERROR;
859 END IF;
860
864 x_where_clause => l_where_clause );
861 -- Generate the where clause
862 Gen_Asset_Where_Clause
863 ( p_inst_asset_query_rec => p_instance_asset_query_rec,
865
866 -- Build the select statement
867 l_select_stmt := l_select_stmt || ' where '||l_where_clause;
868
869 -- Open the cursor
870 l_get_inst_asset_cursor_id := dbms_sql.open_cursor;
871
872 --Parse the select statement
873 dbms_sql.parse(l_get_inst_asset_cursor_id, l_select_stmt , dbms_sql.native);
874
875 -- Bind the variables
876 Bind_asset_variable(p_instance_asset_query_rec, l_get_inst_asset_cursor_id);
877
878 -- Define output variables
879 Define_Asset_Columns(l_get_inst_asset_cursor_id);
880
881 -- execute the select statement
882 l_rows_processed := dbms_sql.execute(l_get_inst_asset_cursor_id);
883
884 LOOP
885 EXIT WHEN DBMS_SQL.FETCH_ROWS(l_get_inst_asset_cursor_id) = 0;
886 Get_asset_Column_Values(l_get_inst_asset_cursor_id, l_inst_asset_rec);
887 l_count := l_count + 1;
888 x_instance_asset_tbl(l_count) := l_inst_asset_rec;
889 END LOOP;
890
891 -- Close the cursor
892 DBMS_SQL.CLOSE_CURSOR(l_get_inst_asset_cursor_id);
893
894 IF (p_time_stamp IS NOT NULL) AND (p_time_stamp <> FND_API.G_MISS_DATE) THEN
895 IF p_time_stamp <= sysdate THEN
896 Construct_asset_from_hist(x_instance_asset_tbl, p_time_stamp);
897 ELSE
898 FND_MESSAGE.Set_Name('CSI', 'CSI_API_INVALID_HIST_PARAMS');
899 FND_MSG_PUB.ADD;
900 RAISE FND_API.G_EXC_ERROR;
901 END IF;
902 END IF;
903
904 -- Resolve the foreign key columns if p_resolve_id_columns is true
905 IF p_resolve_id_columns = fnd_api.g_true THEN
906 IF x_instance_asset_tbl.count > 0 THEN
907 l_instance_asset_tbl := x_instance_asset_tbl;
908 Resolve_id_columns(l_instance_asset_tbl);
909
910 x_instance_asset_tbl := l_instance_asset_tbl;
911 END IF;
912 END IF;
913
914 -- End of API body
915
916 -- Standard check of p_commit.
917 /*
918 IF FND_API.To_Boolean( p_commit ) THEN
919 COMMIT WORK;
920 END IF;
921 */
922
923 /***** srramakr commented for bug # 3304439
924 -- Check for the profile option and disable the trace
925 IF (fnd_profile.value('CSI_ENABLE_SQL_TRACE') = 'Y') THEN
926 dbms_session.set_sql_trace(false);
927 END IF;
928 -- End disable trace
929 ****/
930
931 -- Standard call to get message count and if count is get message info.
932 FND_MSG_PUB.Count_And_Get
933 (p_count => x_msg_count ,
934 p_data => x_msg_data );
935
936 EXCEPTION
937 WHEN FND_API.G_EXC_ERROR THEN
938 /*
939 IF fnd_api.to_boolean(p_commit)
940 THEN
941 ROLLBACK TO get_instance_asset_pvt;
942 END IF;
943 */
944 x_return_status := FND_API.G_RET_STS_ERROR ;
945 FND_MSG_PUB.Count_And_Get
946 ( p_count => x_msg_count,
947 p_data => x_msg_data);
948 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
949 /*
950 IF fnd_api.to_boolean(p_commit)
951 THEN
952 ROLLBACK TO get_instance_asset_pvt;
953 END IF;
954 */
955 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
956 FND_MSG_PUB.Count_And_Get
957 ( p_count => x_msg_count,
958 p_data => x_msg_data);
959 WHEN OTHERS THEN
960 /*
961 IF fnd_api.to_boolean(p_commit)
962 THEN
963 ROLLBACK TO get_instance_asset_pvt;
964 END IF;
965 */
966 IF DBMS_SQL.IS_OPEN(l_get_inst_asset_cursor_id) THEN
967 DBMS_SQL.CLOSE_CURSOR(l_get_inst_asset_cursor_id);
968 END IF;
969 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
970 IF FND_MSG_PUB.Check_Msg_Level
971 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
972 THEN
973 FND_MSG_PUB.Add_Exc_Msg
974 ( G_PKG_NAME ,
975 l_api_name );
976 END IF;
977 FND_MSG_PUB.Count_And_Get
978 ( p_count => x_msg_count,
979 p_data => x_msg_data );
980 END get_instance_assets;
981
982 /*-------------------------------------------------------*/
983 /* Procedure name: create_instance_asset */
984 /* Description : procedure used to update an Item */
985 /* Instance */
986 /*-------------------------------------------------------*/
987 PROCEDURE create_instance_asset (
988 p_api_version IN NUMBER,
989 p_commit IN VARCHAR2,
990 p_init_msg_list IN VARCHAR2,
991 p_validation_level IN NUMBER,
992 p_instance_asset_rec IN OUT NOCOPY csi_datastructures_pub.instance_asset_rec,
996 x_msg_data OUT NOCOPY VARCHAR2,
993 p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec,
994 x_return_status OUT NOCOPY VARCHAR2,
995 x_msg_count OUT NOCOPY NUMBER,
997 p_lookup_tbl IN OUT NOCOPY csi_asset_pvt.lookup_tbl,
998 p_asset_count_rec IN OUT NOCOPY csi_asset_pvt.asset_count_rec,
999 p_asset_id_tbl IN OUT NOCOPY csi_asset_pvt.asset_id_tbl,
1000 p_asset_loc_tbl IN OUT NOCOPY csi_asset_pvt.asset_loc_tbl,
1001 p_called_from_grp IN VARCHAR2)
1002 IS
1003
1004 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_INSTANCE_ASSET';
1005 l_api_version CONSTANT NUMBER := 1.0;
1006 l_CSI_DEBUG_LEVEL NUMBER;
1007 l_process_flag BOOLEAN := TRUE;
1008 l_msg_index NUMBER;
1009 l_msg_count NUMBER;
1010 x_msg_index_out NUMBER;
1011 l_instance_asset_hist_id NUMBER;
1012 l_acct_class_code VARCHAR2(10);
1013 l_record_found BOOLEAN := FALSE;
1014 l_exists_flag VARCHAR2(1);
1015 l_valid_flag VARCHAR2(1);
1016 l_asset_lookup_tbl csi_asset_pvt.lookup_tbl;
1017 l_asset_count_rec csi_asset_pvt.asset_count_rec;
1018 l_asset_id_tbl csi_asset_pvt.asset_id_tbl;
1019 l_asset_loc_tbl csi_asset_pvt.asset_loc_tbl;
1020
1021 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1022 l_error_message varchar2(2000);
1023
1024 BEGIN
1025 -- Standard Start of API savepoint
1026 IF fnd_api.to_boolean(p_commit) THEN
1027 SAVEPOINT create_instance_asset_pvt;
1028 END IF;
1029
1030 -- Standard call to check for call compatibility.
1031 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1032 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1033 END IF;
1034
1035 -- Initialize message list if p_init_msg_list is set to TRUE.
1036 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1037 FND_MSG_PUB.initialize;
1038 END IF;
1039
1040 -- Initialize API return status to success
1041 x_return_status := FND_API.G_RET_STS_SUCCESS;
1042
1043 -- Check the profile option CSI_DEBUG_LEVEL for debug message reporting
1044 l_CSI_DEBUG_LEVEL:=fnd_profile.value('CSI_DEBUG_LEVEL');
1045
1046 IF (l_CSI_DEBUG_LEVEL >= 1) THEN
1047 debug('create_instance_asset:'||
1048 p_api_version||'-'|| p_commit||'-'|| p_init_msg_list);
1049 csi_gen_utility_pvt.dump_txn_rec(p_txn_rec);
1050 csi_gen_utility_pvt.dump_instance_asset_rec(p_instance_asset_rec);
1051 END IF;
1052
1053 -- Start API body
1054 --
1055 -- Initialize the Asset count
1056 --
1057 IF p_asset_count_rec.asset_count IS NULL OR p_asset_count_rec.asset_count = FND_API.G_MISS_NUM
1058 THEN
1059 p_asset_count_rec.asset_count := 0;
1060 END IF;
1061 --
1062 IF p_asset_count_rec.lookup_count IS NULL OR p_asset_count_rec.lookup_count = FND_API.G_MISS_NUM
1063 THEN
1064 p_asset_count_rec.lookup_count := 0;
1065 END IF;
1066 --
1067 IF p_asset_count_rec.loc_count IS NULL OR p_asset_count_rec.loc_count = FND_API.G_MISS_NUM THEN
1068 p_asset_count_rec.loc_count := 0;
1069 END IF;
1070 --
1071 -- Check if all the required parameters are passed
1072 CSI_Asset_vld_pvt.Check_Reqd_Param
1073 ( p_instance_asset_rec.INSTANCE_ID ,
1074 ' p_instance_asset_rec.INSTANCE_ID ',
1075 l_api_name );
1076
1077 IF nvl(p_instance_asset_rec.fa_mass_addition_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
1078 CSI_Asset_vld_pvt.Check_Reqd_Param(
1079 p_instance_asset_rec.FA_ASSET_ID,
1080 'p_instance_asset_rec.FA_ASSET_ID ',
1081 l_api_name);
1082 END IF;
1083
1084 CSI_Asset_vld_pvt.Check_Reqd_Param
1085 ( p_instance_asset_rec.FA_BOOK_TYPE_CODE ,
1086 ' p_instance_asset_rec.FA_BOOK_TYPE_CODE ',
1087 l_api_name );
1088
1089 CSI_Asset_vld_pvt.Check_Reqd_Param
1090 ( p_instance_asset_rec.FA_LOCATION_ID,
1091 ' p_instance_asset_rec.FA_LOCATION_ID',
1092 l_api_name );
1093 -- Added by sk for bug 2232880.
1094 l_record_found := FALSE;
1095 IF ( (p_called_from_grp <> FND_API.G_TRUE) AND
1096 (p_instance_asset_rec.instance_asset_id IS NULL OR
1097 p_instance_asset_rec.instance_asset_id = fnd_api.g_miss_num) )
1098 THEN
1099 BEGIN
1100 SELECT instance_asset_id,
1101 object_version_number
1102 INTO p_instance_asset_rec.instance_asset_id,
1103 p_instance_asset_rec.object_version_number
1104 FROM csi_i_assets
1105 WHERE instance_id = p_instance_asset_rec.instance_id
1106 and fa_asset_id = p_instance_asset_rec.fa_asset_id
1107 and fa_book_type_code = p_instance_asset_rec.fa_book_type_code
1108 AND fa_location_id = p_instance_asset_rec.fa_location_id
1109 AND active_end_date < SYSDATE
1110 AND ROWNUM = 1 ;
1111 l_record_found := TRUE;
1112 EXCEPTION
1113 WHEN OTHERS THEN
1114 NULL;
1115 END;
1116 END IF;
1120 p_instance_asset_rec.active_end_date := NULL;
1117
1118 IF l_record_found THEN
1119 IF p_instance_asset_rec.active_end_date = fnd_api.g_miss_date THEN
1121 END IF;
1122
1123 update_instance_asset(
1124 p_api_version => p_api_version
1125 ,p_commit => fnd_api.g_false
1126 ,p_init_msg_list => p_init_msg_list
1127 ,p_validation_level => p_validation_level
1128 ,p_instance_asset_rec => p_instance_asset_rec
1129 ,p_txn_rec => p_txn_rec
1130 ,x_return_status => x_return_status
1131 ,x_msg_count => x_msg_count
1132 ,x_msg_data => x_msg_data
1133 ,p_lookup_tbl => l_asset_lookup_tbl
1134 ,p_asset_count_rec => l_asset_count_rec
1135 ,p_asset_id_tbl => l_asset_id_tbl
1136 ,p_asset_loc_tbl => l_asset_loc_tbl);
1137
1138
1139 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1140 l_msg_index := 1;
1141 l_msg_count := x_msg_count;
1142 WHILE l_msg_count > 0 LOOP
1143 x_msg_data := FND_MSG_PUB.GET ( l_msg_index, FND_API.G_FALSE );
1144 debug( ' Failed Pvt:update_instance_asset..');
1145 debug('message data = '||x_msg_data);
1146 l_msg_index := l_msg_index + 1;
1147 l_msg_count := l_msg_count - 1;
1148 END LOOP;
1149 RAISE FND_API.G_EXC_ERROR;
1150 END IF;
1151
1152 ELSE
1153 -- End addition by sk for bug 2232880.
1154
1155
1156 CSI_Asset_vld_pvt.Check_Reqd_Param
1157 ( p_instance_asset_rec.ASSET_QUANTITY,
1158 ' p_instance_asset_rec.ASSET_QUANTITY',
1159 l_api_name );
1160 CSI_Asset_vld_pvt.Check_Reqd_Param
1161 ( p_instance_asset_rec.UPDATE_STATUS,
1162 ' p_instance_asset_rec.UPDATE_STATUS',
1163 l_api_name );
1164
1165 -- Validate the Instance id exists in csi_item_instances
1166 IF p_called_from_grp <> FND_API.G_TRUE THEN
1167 IF NOT( CSI_Asset_vld_pvt.Is_InstanceID_Valid
1168 (p_instance_asset_rec.INSTANCE_ID
1169 ,p_instance_asset_rec.check_for_instance_expiry
1170 )) THEN
1171 RAISE FND_API.G_EXC_ERROR;
1172 END IF;
1173 END IF;
1174
1175 IF p_instance_asset_rec.INSTANCE_ASSET_ID is NULL OR
1176 p_instance_asset_rec.INSTANCE_ASSET_ID = FND_API.G_MISS_NUM THEN
1177
1178 -- If the instance_asset id passed is null then generate from sequence
1179 -- and check if the value exists . If exists then generate
1180 -- again from the sequence till we get a value that does not exist
1181 WHILE l_process_flag
1182 LOOP
1183 p_instance_asset_rec.INSTANCE_ASSET_ID := CSI_Asset_vld_pvt.gen_inst_asset_id;
1184 IF NOT(CSI_Asset_vld_pvt.Is_Inst_assetID_exists
1185 (p_instance_asset_rec.INSTANCE_ASSET_ID,
1186 FALSE )) THEN
1187 l_process_flag := FALSE;
1188 END IF;
1189 END LOOP;
1190 ELSE
1191 -- Validate the instance asset id if exist then raise CSI_API_INVALID_PRIMARY_KEY error
1192 IF CSI_Asset_vld_pvt.Is_Inst_assetID_exists
1193 (p_instance_asset_rec.INSTANCE_ASSET_ID,
1194 TRUE ) THEN
1195 RAISE FND_API.G_EXC_ERROR;
1196 END IF;
1197 END IF;
1198
1199 --validation for the asset update status
1200 l_valid_flag := 'Y';
1201 l_exists_flag := 'N';
1202 IF ((p_instance_asset_rec.update_status IS NOT NULL) AND
1203 (p_instance_asset_rec.update_status <> FND_API.G_MISS_CHAR)) THEN
1204 IF p_lookup_tbl.count > 0 THEN
1205 For lookup_count in p_lookup_tbl.FIRST .. p_lookup_tbl.LAST
1206 LOOP
1207 IF p_lookup_tbl(lookup_count).lookup_code = p_instance_asset_rec.update_status THEN
1208 l_valid_flag := p_lookup_tbl(lookup_count).valid_flag;
1209 l_exists_flag := 'Y';
1210 exit;
1211 END IF;
1212 END LOOP;
1213 --
1214 IF l_valid_flag <> 'Y' then
1215 FND_MESSAGE.SET_NAME('CSI','CSI_API_INV_UPDATE_STATUS');
1216 FND_MESSAGE.SET_TOKEN('UPDATE_STATUS',p_instance_asset_rec.update_status);
1217 FND_MSG_PUB.Add;
1218 RAISE fnd_api.g_exc_error;
1219 END IF;
1220 END IF;
1221 --
1222 IF l_exists_flag <> 'Y' THEN
1223 p_asset_count_rec.lookup_count := p_asset_count_rec.lookup_count + 1;
1224 p_lookup_tbl(p_asset_count_rec.lookup_count).lookup_code :=
1225 p_instance_asset_rec.update_status;
1226 IF NOT( CSI_Asset_vld_pvt.Is_Update_Status_Exists
1227 (p_instance_asset_rec.UPDATE_STATUS)) THEN
1228 p_lookup_tbl(p_asset_count_rec.lookup_count).valid_flag := 'N';
1229 RAISE FND_API.G_EXC_ERROR;
1230 ELSE
1231 p_lookup_tbl(p_asset_count_rec.lookup_count).valid_flag := 'Y';
1232 END IF;
1233 END IF;
1234 END IF;
1235 --
1236
1240 RAISE FND_API.G_EXC_ERROR;
1237 -- Validate the quantity > 0
1238 IF NOT( CSI_Asset_vld_pvt.Is_Quantity_Valid
1239 (p_instance_asset_rec.ASSET_QUANTITY)) THEN
1241 END IF;
1242
1243 --check for the exists of asset_id and asset book_type_code combination in the fa_books table
1244 l_valid_flag := 'Y';
1245 l_exists_flag := 'N';
1246 IF ((p_instance_asset_rec.fa_asset_id is not null AND
1247 p_instance_asset_rec.fa_asset_id <> FND_API.G_MISS_NUM) AND
1248 (p_instance_asset_rec.fa_book_type_code is not null AND
1249 p_instance_asset_rec.fa_book_type_code <> FND_API.G_MISS_CHAR)) THEN
1250 IF p_asset_id_tbl.count > 0 then
1251 For asset_count in p_asset_id_tbl.FIRST .. p_asset_id_tbl.LAST
1252 LOOP
1253 IF p_asset_id_tbl(asset_count).asset_id = p_instance_asset_rec.fa_asset_id AND
1254 p_asset_id_tbl(asset_count).asset_book_type = p_instance_asset_rec.fa_book_type_code
1255 THEN
1256 l_valid_flag := p_asset_id_tbl(asset_count).valid_flag;
1257 l_exists_flag := 'Y';
1258 exit;
1259 END IF;
1260 END LOOP;
1261 --
1262 IF l_valid_flag <> 'Y' THEN
1263 FND_MESSAGE.SET_NAME('CSI','CSI_API_INV_ASSET_COMB');
1264 FND_MESSAGE.SET_TOKEN('ASSET_COMBINATION',
1265 p_instance_asset_rec.fa_asset_id||'-'||p_instance_asset_rec.fa_book_type_code);
1266 FND_MSG_PUB.Add;
1267 RAISE fnd_api.g_exc_error;
1268 END IF;
1269 END IF;
1270 --
1271 IF l_exists_flag <> 'Y' THEN
1272 p_asset_count_rec.asset_count := p_asset_count_rec.asset_count + 1;
1273 p_asset_id_tbl(p_asset_count_rec.asset_count).asset_id := p_instance_asset_rec.fa_asset_id;
1274 p_asset_id_tbl(p_asset_count_rec.asset_count).asset_book_type :=
1275 p_instance_asset_rec.fa_book_type_code;
1276 IF NOT( CSI_Asset_vld_pvt.Is_Asset_Comb_Valid
1277 (p_instance_asset_rec.FA_ASSET_ID ,
1278 p_instance_asset_rec.FA_BOOK_TYPE_CODE )) THEN
1279 p_asset_id_tbl(p_asset_count_rec.asset_count).valid_flag := 'N';
1280 RAISE fnd_api.g_exc_error;
1281 ELSE
1282 p_asset_id_tbl(p_asset_count_rec.asset_count).valid_flag := 'Y';
1283 END IF;
1284 END IF;
1285 END IF;
1286 --
1287 IF ((p_instance_asset_rec.active_start_date = FND_API.G_MISS_DATE) OR
1288 (p_instance_asset_rec.active_start_date IS NULL))
1289 THEN
1290 p_instance_asset_rec.active_start_date := SYSDATE;
1291 END IF;
1292
1293 IF (p_instance_asset_rec.active_end_date = FND_API.G_MISS_DATE) THEN
1294 p_instance_asset_rec.active_end_date := NULL;
1295 END IF;
1296 --
1297 IF p_called_from_grp <> FND_API.G_TRUE THEN
1298 -- Validation for the active start date passed
1299 IF NOT(CSI_Asset_vld_pvt.Is_StartDate_Valid(
1300 p_instance_asset_rec.ACTIVE_START_DATE,
1301 p_instance_asset_rec.ACTIVE_END_DATE ,
1302 p_instance_asset_rec.INSTANCE_ID,
1303 p_instance_asset_rec.check_for_instance_expiry ))
1304 THEN
1305 RAISE FND_API.G_EXC_ERROR;
1306 END IF;
1307 END IF;
1308 --
1309 IF p_called_from_grp <> FND_API.G_TRUE THEN
1310 -- Verify if the active_end_date is valid
1311 IF ((p_instance_asset_rec.ACTIVE_END_DATE is NOT NULL) AND
1312 ( p_instance_asset_rec.ACTIVE_END_DATE <> FND_API.G_MISS_DATE)) THEN
1313 IF NOT(CSI_Asset_vld_pvt.Is_EndDate_Valid(
1314 p_instance_asset_rec.ACTIVE_START_DATE,
1315 p_instance_asset_rec.ACTIVE_END_DATE ,
1316 p_instance_asset_rec.INSTANCE_ID,
1317 p_instance_asset_rec.INSTANCE_ASSET_ID,
1318 p_txn_rec.TRANSACTION_ID,
1319 p_instance_asset_rec.check_for_instance_expiry))
1320 THEN
1321 RAISE FND_API.G_EXC_ERROR;
1322 END IF;
1323 END IF;
1324 END IF;
1325
1326 IF nvl(p_instance_asset_rec.fa_asset_id, fnd_api.g_miss_num) = fnd_api.g_miss_num
1327 OR
1328 nvl(p_instance_asset_rec.fa_book_type_code, fnd_api.g_miss_char) = fnd_api.g_miss_char
1329 OR
1330 nvl(p_instance_asset_rec.fa_location_id, fnd_api.g_miss_num) = fnd_api.g_miss_num
1331 OR
1332 nvl(p_instance_asset_rec.asset_quantity, fnd_api.g_miss_num) = fnd_api.g_miss_num
1333 THEN
1334 p_instance_asset_rec.creation_complete_flag := 'N';
1335 ELSE
1336 p_instance_asset_rec.creation_complete_flag := 'Y';
1337 END IF;
1338 --
1339 -- Since Group API already makes a call to this routine, by-passing it.
1340 IF p_called_from_grp <> FND_API.G_TRUE THEN
1341 IF p_instance_asset_rec.fa_sync_validation_reqd = fnd_api.g_true THEN
1342 set_fa_sync_flag (
1343 px_instance_asset_rec => p_instance_asset_rec,
1344 x_return_status => l_return_status,
1345 x_error_msg => l_error_message);
1346 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1347 RAISE fnd_api.g_exc_error;
1348 END IF;
1349 END IF;
1350 END IF; -- Called from grp check as GRP API already does the validation
1351 --
1355 l_valid_flag := 'Y';
1352 IF p_instance_asset_rec.fa_sync_validation_reqd = fnd_api.g_false AND
1353 nvl(p_instance_asset_rec.fa_sync_flag,'N') = 'Y' THEN
1354 --check for the existance of location_id in fa_locations table
1356 l_exists_flag := 'N';
1357 IF p_instance_asset_rec.fa_location_id is not null AND
1358 p_instance_asset_rec.fa_location_id <> FND_API.G_MISS_NUM THEN
1359 IF p_asset_loc_tbl.count > 0 then
1360 For loc_count in p_asset_loc_tbl.FIRST .. p_asset_loc_tbl.LAST
1361 LOOP
1362 IF p_asset_loc_tbl(loc_count).asset_loc_id = p_instance_asset_rec.fa_location_id THEN
1363 l_valid_flag := p_asset_loc_tbl(loc_count).valid_flag;
1364 l_exists_flag := 'Y';
1365 exit;
1366 END IF;
1367 END LOOP;
1368 --
1369 IF l_valid_flag <> 'Y' THEN
1370 FND_MESSAGE.SET_NAME('CSI','CSI_API_INV_ASSET_LOCATION');
1371 FND_MESSAGE.SET_TOKEN('ASSET_LOCATION_ID',p_instance_asset_rec.fa_location_id);
1372 FND_MSG_PUB.Add;
1373 RAISE fnd_api.g_exc_error;
1374 END IF;
1375 END IF;
1376 --
1377 IF l_exists_flag <> 'Y' THEN
1378 p_asset_count_rec.loc_count := p_asset_count_rec.loc_count + 1;
1379 p_asset_loc_tbl(p_asset_count_rec.loc_count).asset_loc_id :=
1380 p_instance_asset_rec.fa_location_id;
1381 IF NOT( CSI_Asset_vld_pvt.Is_Asset_Location_Valid (p_instance_asset_rec.FA_LOCATION_ID ))
1382 THEN
1383 p_asset_loc_tbl(p_asset_count_rec.loc_count).valid_flag := 'N';
1384 RAISE fnd_api.g_exc_error;
1385 ELSE
1386 p_asset_loc_tbl(p_asset_count_rec.loc_count).valid_flag := 'Y';
1387 END IF;
1388 END IF;
1389 END IF;
1390 END IF;
1391
1392 -- Call table handler to insert into csi_i_assets table
1393 IF p_called_from_grp <> FND_API.G_TRUE THEN
1394 CSI_I_ASSETS_PKG.Insert_Row (
1395 px_INSTANCE_ASSET_ID => p_instance_asset_rec.INSTANCE_ASSET_ID,
1396 p_INSTANCE_ID => p_instance_asset_rec.INSTANCE_ID,
1397 p_FA_ASSET_ID => p_instance_asset_rec.FA_ASSET_ID,
1398 p_FA_BOOK_TYPE_CODE => p_instance_asset_rec.FA_BOOK_TYPE_CODE,
1399 p_FA_LOCATION_ID => p_instance_asset_rec.FA_LOCATION_ID,
1400 p_ASSET_QUANTITY => p_instance_asset_rec.ASSET_QUANTITY,
1401 p_UPDATE_STATUS => p_instance_asset_rec.UPDATE_STATUS,
1402 p_FA_SYNC_FLAG => p_instance_asset_rec.FA_SYNC_FLAG,
1403 p_FA_MASS_ADDITION_ID => p_instance_asset_rec.FA_MASS_ADDITION_ID,
1404 p_CREATION_COMPLETE_FLAG=> p_instance_asset_rec.CREATION_COMPLETE_FLAG,
1405 p_CREATED_BY => FND_GLOBAL.USER_ID,
1406 p_CREATION_DATE => SYSDATE,
1407 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
1408 p_LAST_UPDATE_DATE => SYSDATE,
1409 p_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID,
1410 p_OBJECT_VERSION_NUMBER => 1,
1411 p_ACTIVE_START_DATE => p_instance_asset_rec.ACTIVE_START_DATE,
1412 p_ACTIVE_END_DATE => p_instance_asset_rec.ACTIVE_END_DATE);
1413
1414 IF nvl(p_txn_rec.transaction_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
1415 -- Call create_transaction to create txn log
1416 CSI_TRANSACTIONS_PVT.Create_transaction (
1417 p_api_version => p_api_version,
1418 p_commit => p_commit,
1419 p_init_msg_list => p_init_msg_list,
1420 p_validation_level => p_validation_level,
1421 p_Success_If_Exists_Flag => 'Y',
1422 P_transaction_rec => p_txn_rec,
1423 x_return_status => x_return_status,
1424 x_msg_count => x_msg_count,
1425 x_msg_data => x_msg_data);
1426
1427 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1428 FND_MESSAGE.SET_NAME('CSI','CSI_FAILED_TO_VALIDATE_TXN');
1429 FND_MESSAGE.SET_TOKEN('API_NAME',l_api_name);
1430 FND_MESSAGE.SET_TOKEN('TRANSACTION_ID',p_txn_rec.transaction_id );
1431 FND_MSG_PUB.Add;
1432 RAISE fnd_api.g_exc_error;
1433 END IF;
1434 END IF;
1435
1436 -- Generate the instance asset history id from the sequence
1437 l_instance_asset_hist_id := CSI_Asset_vld_pvt.gen_inst_asset_hist_id;
1438 -- Call table handlers to insert into history table
1439 CSI_I_ASSETS_H_PKG.Insert_Row (
1440 px_INSTANCE_ASSET_HISTORY_ID => l_instance_asset_hist_id,
1441 p_INSTANCE_ASSET_ID => p_instance_asset_rec.INSTANCE_ASSET_ID,
1442 p_TRANSACTION_ID => p_txn_rec.transaction_id,
1443 p_OLD_INSTANCE_ID => NULL,
1444 p_NEW_INSTANCE_ID => p_instance_asset_rec.INSTANCE_ID,
1445 p_OLD_FA_ASSET_ID => NULL,
1446 p_NEW_FA_ASSET_ID => p_instance_asset_rec.FA_ASSET_ID,
1447 p_OLD_ASSET_QUANTITY => NULL,
1448 p_NEW_ASSET_QUANTITY => p_instance_asset_rec.ASSET_QUANTITY,
1449 p_OLD_FA_BOOK_TYPE_CODE => NULL,
1450 p_NEW_FA_BOOK_TYPE_CODE => p_instance_asset_rec.FA_BOOK_TYPE_CODE,
1451 p_OLD_FA_LOCATION_ID => NULL,
1455 p_OLD_FA_SYNC_FLAG => NULL,
1452 p_NEW_FA_LOCATION_ID => p_instance_asset_rec.FA_LOCATION_ID,
1453 p_OLD_UPDATE_STATUS => NULL,
1454 p_NEW_UPDATE_STATUS => p_instance_asset_rec.UPDATE_STATUS,
1456 p_NEW_FA_SYNC_FLAG => p_instance_asset_rec.FA_SYNC_FLAG,
1457 p_OLD_FA_MASS_ADDITION_ID => NULL,
1458 p_NEW_FA_MASS_ADDITION_ID => p_instance_asset_rec.FA_MASS_ADDITION_ID,
1459 p_OLD_CREATION_COMPLETE_FLAG => NULL,
1460 p_NEW_CREATION_COMPLETE_FLAG => p_instance_asset_rec.CREATION_COMPLETE_FLAG,
1461 p_FULL_DUMP_FLAG => 'N',
1462 p_CREATED_BY => FND_GLOBAL.USER_ID,
1463 p_CREATION_DATE => SYSDATE,
1464 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
1465 p_LAST_UPDATE_DATE => SYSDATE,
1466 p_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID,
1467 p_OBJECT_VERSION_NUMBER => 1,
1468 p_OLD_ACTIVE_START_DATE => NULL,
1469 p_NEW_ACTIVE_START_DATE => p_instance_asset_rec.ACTIVE_START_DATE,
1470 p_OLD_ACTIVE_END_DATE => NULL,
1471 p_NEW_ACTIVE_END_DATE => p_instance_asset_rec.ACTIVE_END_DATE);
1472
1473 csi_item_instance_pvt.get_and_update_acct_class(
1474 p_api_version => p_api_version,
1475 p_commit => p_commit,
1476 p_init_msg_list => p_init_msg_list,
1477 p_validation_level => p_validation_level,
1478 p_instance_id => p_instance_asset_rec.instance_id,
1479 p_instance_expiry_flag => p_instance_asset_rec.check_for_instance_expiry,
1480 p_txn_rec => p_txn_rec,
1481 x_acct_class_code => l_acct_class_code,
1482 x_return_status => x_return_status,
1483 x_msg_count => x_msg_count,
1484 x_msg_data => x_msg_data);
1485
1486 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1487 RAISE fnd_api.g_exc_error;
1488 END IF;
1489
1490 END IF; -- called from grp check
1491 END IF; -- Added by sk for bug 2232880
1492 --
1493 -- End of API body
1494
1495 -- Standard check of p_commit.
1496 IF FND_API.To_Boolean( p_commit ) THEN
1497 COMMIT WORK;
1498 END IF;
1499
1500 -- Standard call to get message count and if count is get message info.
1501 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
1502
1503 debug(' end of create_instance_asset.'||x_return_status);
1504
1505 EXCEPTION
1506 WHEN fnd_api.g_exc_error THEN
1507 IF fnd_api.to_boolean(p_commit) THEN
1508 ROLLBACK TO create_instance_asset_pvt;
1509 END IF;
1510 x_return_status := fnd_api.g_ret_sts_error;
1511 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
1512 l_error_message := csi_gen_utility_pvt.dump_error_stack;
1513 debug('error(E): '||l_error_message);
1514 WHEN fnd_api.g_exc_unexpected_error THEN
1515 IF fnd_api.to_boolean(p_commit) THEN
1516 ROLLBACK TO create_instance_asset_pvt;
1517 END IF;
1518 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1519 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
1520 l_error_message := csi_gen_utility_pvt.dump_error_stack;
1521 debug('error(U): '||l_error_message);
1522 WHEN OTHERS THEN
1523 IF fnd_api.to_boolean(p_commit) THEN
1524 ROLLBACK TO create_instance_asset_pvt;
1525 END IF;
1526 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1527 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
1528 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
1529 l_error_message := csi_gen_utility_pvt.dump_error_stack;
1530 debug('error(0): '||l_error_message);
1531 END create_instance_asset;
1532
1533
1534 PROCEDURE update_instance_asset(
1535 p_api_version IN NUMBER,
1536 p_commit IN VARCHAR2,
1537 p_init_msg_list IN VARCHAR2,
1538 p_validation_level IN NUMBER,
1539 p_instance_asset_rec IN OUT NOCOPY csi_datastructures_pub.instance_asset_rec,
1540 p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec,
1541 x_return_status OUT NOCOPY VARCHAR2,
1542 x_msg_count OUT NOCOPY NUMBER,
1543 x_msg_data OUT NOCOPY VARCHAR2,
1544 p_lookup_tbl IN OUT NOCOPY csi_asset_pvt.lookup_tbl,
1545 p_asset_count_rec IN OUT NOCOPY csi_asset_pvt.asset_count_rec,
1546 p_asset_id_tbl IN OUT NOCOPY csi_asset_pvt.asset_id_tbl,
1547 p_asset_loc_tbl IN OUT NOCOPY csi_asset_pvt.asset_loc_tbl )
1548 IS
1549 l_api_name CONSTANT VARCHAR2(30) := 'update_instance_asset';
1550 l_api_version CONSTANT NUMBER := 1.0;
1551 l_CSI_DEBUG_LEVEL NUMBER;
1552 l_object_version_number NUMBER;
1553 l_inst_asset_his_id NUMBER;
1554 l_full_dump_frequency NUMBER;
1555 l_mod_value NUMBER;
1556 x_msg_index_out NUMBER;
1557 l_instance_asset_hist_id NUMBER;
1558 l_acct_class_code VARCHAR2(10);
1559 l_exists_flag VARCHAR2(1);
1560 l_valid_flag VARCHAR2(1);
1564 l_error_message varchar2(2000);
1561 l_ins_asset_hist_rec csi_datastructures_pub.ins_asset_history_rec;
1562 l_creation_complete_flag varchar2(1);
1563 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1565
1566 CURSOR get_curr_asset_rec (p_inst_asset_id IN NUMBER) IS
1567 SELECT instance_asset_id,
1568 instance_id,
1569 fa_asset_id,
1570 fa_book_type_code,
1571 fa_location_id,
1572 asset_quantity,
1573 update_status,
1574 fa_sync_flag,
1575 fa_mass_addition_id,
1576 creation_complete_flag,
1577 active_start_date,
1578 active_end_date,
1579 object_version_number
1580 FROM csi_i_assets
1581 WHERE INSTANCE_ASSET_ID = p_inst_asset_id
1582 FOR UPDATE OF object_version_number ;
1583
1584 l_curr_asset_rec get_curr_asset_rec%ROWTYPE;
1585 l_temp_inst_asset_rec get_curr_asset_rec%ROWTYPE;
1586
1587 CURSOR asset_hist_csr (p_asset_hist_id NUMBER) IS
1588 SELECT *
1589 FROM csi_i_assets_h
1590 WHERE csi_i_assets_h.instance_asset_history_id = p_asset_hist_id
1591 FOR UPDATE NOWAIT;
1592
1593 l_asset_hist_csr asset_hist_csr%ROWTYPE;
1594 l_asset_hist_id NUMBER;
1595
1596 BEGIN
1597
1598 -- Standard Start of API savepoint
1599 IF fnd_api.to_boolean(p_commit) THEN
1600 SAVEPOINT update_instance_asset_pvt;
1601 END IF;
1602
1603 -- Standard call to check for call compatibility.
1604 IF NOT FND_API.Compatible_API_Call (l_api_version , p_api_version , l_api_name , G_PKG_NAME) THEN
1605 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1606 END IF;
1607
1608 -- Initialize message list if p_init_msg_list is set to TRUE.
1609 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1610 FND_MSG_PUB.initialize;
1611 END IF;
1612
1613 -- Initialize API return status to success
1614 x_return_status := FND_API.G_RET_STS_SUCCESS;
1615
1616 -- Check the profile option CSI_DEBUG_LEVEL for debug message reporting
1617 l_CSI_DEBUG_LEVEL:=fnd_profile.value('CSI_DEBUG_LEVEL');
1618
1619 -- If CSI_DEBUG_LEVEL = 1 then dump the procedure name
1620 IF (l_CSI_DEBUG_LEVEL > 0) THEN
1621 debug( 'update_instance_asset');
1622 END IF;
1623
1624
1625 -- If the debug level = 2 then dump all the parameters values.
1626 IF (l_CSI_DEBUG_LEVEL > 1) THEN
1627 debug( 'update_instance_asset:'||p_api_version||'-'||p_commit||'-'||p_init_msg_list||'-'||p_validation_level);
1628 -- Dump the records in the log file
1629 csi_gen_utility_pvt.dump_instance_asset_rec(p_instance_asset_rec);
1630 IF nvl(p_txn_rec.transaction_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
1631 csi_gen_utility_pvt.dump_txn_rec(p_txn_rec);
1632 END IF;
1633 END IF;
1634
1635 -- Initialize the Asset count
1636 IF p_asset_count_rec.asset_count IS NULL OR
1637 p_asset_count_rec.asset_count = FND_API.G_MISS_NUM THEN
1638 p_asset_count_rec.asset_count := 0;
1639 END IF;
1640 --
1641 IF p_asset_count_rec.lookup_count IS NULL OR
1642 p_asset_count_rec.lookup_count = FND_API.G_MISS_NUM THEN
1643 p_asset_count_rec.lookup_count := 0;
1644 END IF;
1645 --
1646 IF p_asset_count_rec.loc_count IS NULL OR
1647 p_asset_count_rec.loc_count = FND_API.G_MISS_NUM THEN
1648 p_asset_count_rec.loc_count := 0;
1649 END IF;
1650
1651 -- Check if all the required parameters are passed
1652 CSI_Asset_vld_pvt.Check_Reqd_Param (p_instance_asset_rec.INSTANCE_ASSET_ID,
1653 'p_instance_asset_rec.INSTANCE_ASSET_ID ',
1654 l_api_name);
1655
1656 -- check if the object_version_number passed matches with the one
1657 -- in the database else raise error
1658 OPEN get_curr_asset_rec(p_instance_asset_rec.INSTANCE_ASSET_ID);
1659 FETCH get_curr_asset_rec INTO l_curr_asset_rec;
1660 IF (l_curr_asset_rec.object_version_number <> p_instance_asset_rec.OBJECT_VERSION_NUMBER) THEN
1661 FND_MESSAGE.Set_Name('CSI', 'CSI_API_OBJ_VER_MISMATCH');
1662 FND_MSG_PUB.ADD;
1663 RAISE FND_API.G_EXC_ERROR;
1664 END IF;
1665
1666 IF get_curr_asset_rec%NOTFOUND THEN
1667 FND_MESSAGE.Set_Name('CSI', 'CSI_API_RECORD_LOCKED');
1668 FND_MSG_PUB.ADD;
1669 RAISE FND_API.G_EXC_ERROR;
1670 END IF;
1671 CLOSE get_curr_asset_rec;
1672 --
1673 p_instance_asset_rec.instance_id := l_curr_asset_rec.instance_id;
1674 --
1675 -- Validate the Instance asset id exists in csi_i_assets
1676 IF NOT( CSI_Asset_vld_pvt.Is_Inst_asset_id_valid (p_instance_asset_rec.INSTANCE_ASSET_ID)) THEN
1677 RAISE FND_API.G_EXC_ERROR;
1678 END IF;
1679
1680 -- Validate the Instance id exists in csi_item_instances
1681 IF ((p_instance_asset_rec.INSTANCE_ID IS NOT NULL)
1682 AND (p_instance_asset_rec.INSTANCE_ID <> FND_API.G_MISS_NUM)) THEN
1683 IF NOT( CSI_Asset_vld_pvt.Is_InstanceID_Valid
1684 (p_instance_asset_rec.INSTANCE_ID
1685 ,p_instance_asset_rec.check_for_instance_expiry
1686 )) THEN
1687 RAISE FND_API.G_EXC_ERROR;
1688 END IF;
1689 END IF;
1690
1691 --validation for the asset update status
1695 (p_instance_asset_rec.update_status <> FND_API.G_MISS_CHAR)) THEN
1692 l_valid_flag := 'Y';
1693 l_exists_flag := 'N';
1694 IF ((p_instance_asset_rec.update_status IS NOT NULL) AND
1696 IF p_lookup_tbl.count > 0 THEN
1697 For lookup_count in p_lookup_tbl.FIRST .. p_lookup_tbl.LAST
1698 LOOP
1699 IF p_lookup_tbl(lookup_count).lookup_code = p_instance_asset_rec.update_status THEN
1700 l_valid_flag := p_lookup_tbl(lookup_count).valid_flag;
1701 l_exists_flag := 'Y';
1702 exit;
1703 END IF;
1704 End Loop;
1705 --
1706 if l_valid_flag <> 'Y' then
1707 FND_MESSAGE.SET_NAME('CSI','CSI_API_INV_UPDATE_STATUS');
1708 FND_MESSAGE.SET_TOKEN('UPDATE_STATUS',p_instance_asset_rec.update_status);
1709 FND_MSG_PUB.Add;
1710 RAISE fnd_api.g_exc_error;
1711 end if;
1712 End if;
1713 --
1714 IF l_exists_flag <> 'Y' THEN
1715 p_asset_count_rec.lookup_count := p_asset_count_rec.lookup_count + 1;
1716 p_lookup_tbl(p_asset_count_rec.lookup_count).lookup_code := p_instance_asset_rec.update_status;
1717 IF NOT( CSI_Asset_vld_pvt.Is_Update_Status_Exists
1718 (p_instance_asset_rec.UPDATE_STATUS)) THEN
1719 p_lookup_tbl(p_asset_count_rec.lookup_count).valid_flag := 'N';
1720 RAISE FND_API.G_EXC_ERROR;
1721 ELSE
1722 p_lookup_tbl(p_asset_count_rec.lookup_count).valid_flag := 'Y';
1723 END IF;
1724 END IF;
1725 END IF;
1726 --
1727
1728 --check for the exists of asset_id and asset book_type_code combination in the fa_books table
1729 l_valid_flag := 'Y';
1730 l_exists_flag := 'N';
1731 IF ((p_instance_asset_rec.fa_asset_id is not null AND
1732 p_instance_asset_rec.fa_asset_id <> FND_API.G_MISS_NUM) AND
1733 (p_instance_asset_rec.fa_book_type_code is not null AND
1734 p_instance_asset_rec.fa_book_type_code <> FND_API.G_MISS_CHAR))
1735 THEN
1736 IF p_asset_id_tbl.count > 0 then
1737 For asset_count in p_asset_id_tbl.FIRST .. p_asset_id_tbl.LAST
1738 LOOP
1739 IF p_asset_id_tbl(asset_count).asset_id = p_instance_asset_rec.fa_asset_id AND
1740 p_asset_id_tbl(asset_count).asset_book_type = p_instance_asset_rec.fa_book_type_code
1741 THEN
1742 l_valid_flag := p_asset_id_tbl(asset_count).valid_flag;
1743 l_exists_flag := 'Y';
1744 exit;
1745 END IF;
1746 END LOOP;
1747 --
1748 IF l_valid_flag <> 'Y' THEN
1749 FND_MESSAGE.SET_NAME('CSI','CSI_API_INV_ASSET_COMB');
1750 FND_MESSAGE.SET_TOKEN('ASSET_COMBINATION',p_instance_asset_rec.fa_asset_id||'-'||p_instance_asset_rec.fa_book_type_code);
1751 FND_MSG_PUB.Add;
1752 RAISE fnd_api.g_exc_error;
1753 END IF;
1754 END IF;
1755 --
1756 IF l_exists_flag <> 'Y' THEN
1757 p_asset_count_rec.asset_count := p_asset_count_rec.asset_count + 1;
1758 p_asset_id_tbl(p_asset_count_rec.asset_count).asset_id := p_instance_asset_rec.fa_asset_id;
1759 p_asset_id_tbl(p_asset_count_rec.asset_count).asset_book_type := p_instance_asset_rec.fa_book_type_code;
1760 IF NOT( CSI_Asset_vld_pvt.Is_Asset_Comb_Valid
1761 (p_instance_asset_rec.FA_ASSET_ID ,
1762 p_instance_asset_rec.FA_BOOK_TYPE_CODE )) THEN
1763 p_asset_id_tbl(p_asset_count_rec.asset_count).valid_flag := 'N';
1764 RAISE fnd_api.g_exc_error;
1765 ELSE
1766 p_asset_id_tbl(p_asset_count_rec.asset_count).valid_flag := 'Y';
1767 END IF;
1768 END IF;
1769 END IF;
1770 --
1771
1772 IF p_instance_asset_rec.fa_sync_validation_reqd = fnd_api.g_false AND
1773 nvl(p_instance_asset_rec.fa_sync_flag,'N') = 'Y'
1774 THEN
1775 l_valid_flag := 'Y';
1776 l_exists_flag := 'N';
1777 IF p_instance_asset_rec.fa_location_id is not null AND
1778 p_instance_asset_rec.fa_location_id <> FND_API.G_MISS_NUM THEN
1779 IF p_asset_loc_tbl.count > 0 then
1780 For loc_count in p_asset_loc_tbl.FIRST .. p_asset_loc_tbl.LAST
1781 LOOP
1782 IF p_asset_loc_tbl(loc_count).asset_loc_id = p_instance_asset_rec.fa_location_id THEN
1783 l_valid_flag := p_asset_loc_tbl(loc_count).valid_flag;
1784 l_exists_flag := 'Y';
1785 exit;
1786 END IF;
1787 END LOOP;
1788 --
1789 IF l_valid_flag <> 'Y' THEN
1790 FND_MESSAGE.SET_NAME('CSI','CSI_API_INV_ASSET_LOCATION');
1791 FND_MESSAGE.SET_TOKEN('ASSET_LOCATION_ID',p_instance_asset_rec.fa_location_id);
1792 FND_MSG_PUB.Add;
1793 RAISE fnd_api.g_exc_error;
1794 END IF;
1795 END IF;
1796 --
1797 IF l_exists_flag <> 'Y' THEN
1798 p_asset_count_rec.loc_count := p_asset_count_rec.loc_count + 1;
1799 p_asset_loc_tbl(p_asset_count_rec.loc_count).asset_loc_id := p_instance_asset_rec.fa_location_id;
1800 IF NOT( CSI_Asset_vld_pvt.Is_Asset_Location_Valid (p_instance_asset_rec.FA_LOCATION_ID )) THEN
1801 p_asset_loc_tbl(p_asset_count_rec.loc_count).valid_flag := 'N';
1802 RAISE fnd_api.g_exc_error;
1803 ELSE
1804 p_asset_loc_tbl(p_asset_count_rec.loc_count).valid_flag := 'Y';
1805 END IF;
1806 END IF;
1807 END IF;
1808 END IF;
1812 IF p_instance_asset_rec.active_start_date <> FND_API.G_MISS_DATE THEN
1809 --
1810
1811 -- Validation for the Active start date
1813 IF p_instance_asset_rec.active_start_date <> l_curr_asset_rec.active_start_date THEN
1814 FND_MESSAGE.Set_Name('CSI', 'CSI_API_UPD_NOT_ALLOWED');
1815 FND_MESSAGE.Set_Token('COLUMN', 'ACTIVE_START_DATE');
1816 FND_MSG_PUB.ADD;
1817 RAISE FND_API.G_EXC_ERROR;
1818 END IF;
1819 END IF;
1820
1821 -- Verify end effective end date
1822 IF ( p_instance_asset_rec.active_end_date <> FND_API.G_MISS_DATE) THEN
1823 IF p_instance_asset_rec.active_end_date IS NOT NULL THEN
1824 IF NOT(CSI_Asset_vld_pvt.Is_EndDate_Valid
1825 (p_instance_asset_rec.ACTIVE_START_DATE,
1826 p_instance_asset_rec.ACTIVE_END_DATE,
1827 p_instance_asset_rec.INSTANCE_ID ,
1828 p_instance_asset_rec.INSTANCE_ASSET_ID,
1829 p_txn_rec.TRANSACTION_ID,
1830 p_instance_asset_rec.check_for_instance_expiry)) THEN
1831 RAISE FND_API.G_EXC_ERROR;
1832 END IF;
1833 END IF;
1834 END IF;
1835
1836 IF ((p_instance_asset_rec.fa_asset_id is not null AND p_instance_asset_rec.fa_asset_id <> fnd_api.g_miss_num)
1837 OR
1838 (l_curr_asset_rec.fa_asset_id is not null AND p_instance_asset_rec.fa_asset_id = fnd_api.g_miss_num))
1839 AND
1840 ((p_instance_asset_rec.fa_book_type_code is not null AND
1841 p_instance_asset_rec.fa_book_type_code <> fnd_api.g_miss_char)
1842 OR
1843 (l_curr_asset_rec.fa_book_type_code is not null AND
1844 p_instance_asset_rec.fa_book_type_code = fnd_api.g_miss_char))
1845 AND
1846 ((p_instance_asset_rec.fa_location_id is not null AND
1847 p_instance_asset_rec.fa_location_id <> fnd_api.g_miss_num)
1848 OR
1849 (l_curr_asset_rec.fa_location_id is not null AND
1850 p_instance_asset_rec.fa_location_id = fnd_api.g_miss_num))
1851 AND
1852 ((p_instance_asset_rec.asset_quantity is not null AND
1853 p_instance_asset_rec.asset_quantity <> fnd_api.g_miss_num)
1854 OR
1855 (l_curr_asset_rec.asset_quantity is not null AND
1856 p_instance_asset_rec.asset_quantity = fnd_api.g_miss_num))
1857 THEN
1858 l_creation_complete_flag := 'Y';
1859 ELSE
1860 l_creation_complete_flag := 'N';
1861 END IF;
1862
1863 IF p_instance_asset_rec.fa_sync_validation_reqd = fnd_api.g_true THEN
1864
1865 IF nvl(p_instance_asset_rec.fa_book_type_code, fnd_api.g_miss_char) = fnd_api.g_miss_char THEN
1866 p_instance_asset_rec.fa_book_type_code := l_curr_asset_rec.fa_book_type_code;
1867 END IF;
1868
1869 IF nvl(p_instance_asset_rec.instance_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
1870 p_instance_asset_rec.instance_id := l_curr_asset_rec.instance_id;
1871 END IF;
1872
1873 IF nvl(p_instance_asset_rec.fa_location_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
1874 p_instance_asset_rec.fa_location_id := l_curr_asset_rec.fa_location_id;
1875 END IF;
1876
1877 IF nvl(p_instance_asset_rec.fa_ASSET_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
1878 p_instance_asset_rec.fa_ASSET_id := l_curr_asset_rec.fa_ASSET_id;
1879 END IF;
1880
1881 IF nvl(p_instance_asset_rec.asset_quantity, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
1882 p_instance_asset_rec.asset_quantity := l_curr_asset_rec.asset_quantity;
1883 END IF;
1884
1885 set_fa_sync_flag (
1886 px_instance_asset_rec => p_instance_asset_rec,
1887 x_return_status => l_return_status,
1888 x_error_msg => l_error_message);
1889 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1890 RAISE fnd_api.g_exc_error;
1891 END IF;
1892 END IF;
1893
1894 -- Increment the object_version_number before updating
1895 l_OBJECT_VERSION_NUMBER := l_curr_asset_rec.OBJECT_VERSION_NUMBER + 1 ;
1896
1897 CSI_I_ASSETS_PKG.Update_Row (
1898 p_INSTANCE_ASSET_ID => p_instance_asset_rec.instance_asset_id,
1899 p_INSTANCE_ID => p_instance_asset_rec.INSTANCE_ID,
1900 p_FA_ASSET_ID => p_instance_asset_rec.FA_ASSET_ID,
1901 p_FA_BOOK_TYPE_CODE => p_instance_asset_rec.FA_BOOK_TYPE_CODE,
1902 p_FA_LOCATION_ID => p_instance_asset_rec.FA_LOCATION_ID,
1903 p_ASSET_QUANTITY => p_instance_asset_rec.ASSET_QUANTITY,
1904 p_UPDATE_STATUS => p_instance_asset_rec.UPDATE_STATUS,
1905 p_FA_SYNC_FLAG => p_instance_asset_rec.FA_SYNC_FLAG,
1906 p_FA_MASS_ADDITION_ID => p_instance_asset_rec.FA_MASS_ADDITION_ID,
1907 p_CREATION_COMPLETE_FLAG=> l_creation_complete_flag,
1908 p_CREATED_BY => FND_API.G_MISS_NUM,
1909 p_CREATION_DATE => fnd_api.g_miss_date,
1910 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
1911 p_LAST_UPDATE_DATE => SYSDATE,
1912 p_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID,
1913 p_OBJECT_VERSION_NUMBER => l_OBJECT_VERSION_NUMBER,
1914 p_ACTIVE_START_DATE => p_instance_asset_rec.ACTIVE_START_DATE,
1915 p_ACTIVE_END_DATE => p_instance_asset_rec.ACTIVE_END_DATE);
1916
1917 IF nvl(p_txn_rec.transaction_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN -- changed <> to = bug 5207557
1918 -- Call create_transaction to create txn log
1919 CSI_TRANSACTIONS_PVT.Create_transaction (
1923 p_validation_level => p_validation_level,
1920 p_api_version => p_api_version,
1921 p_commit => p_commit,
1922 p_init_msg_list => p_init_msg_list,
1924 p_Success_If_Exists_Flag => 'Y',
1925 P_transaction_rec => p_txn_rec,
1926 x_return_status => x_return_status,
1927 x_msg_count => x_msg_count,
1928 x_msg_data => x_msg_data);
1929
1930 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1931 FND_MESSAGE.SET_NAME('CSI','CSI_FAILED_TO_VALIDATE_TXN');
1932 FND_MESSAGE.SET_TOKEN('API_NAME',l_api_name);
1933 FND_MESSAGE.SET_TOKEN('TRANSACTION_ID',p_txn_rec.transaction_id );
1934 FND_MSG_PUB.Add;
1935 FOR i in 1..x_msg_Count LOOP
1936 FND_MSG_PUB.Get(p_msg_index => i,
1937 p_encoded => 'F',
1938 p_data => x_msg_data,
1939 p_msg_index_out => x_msg_index_out );
1940 debug( 'message data = '||x_msg_data);
1941 End LOOP;
1942 IF fnd_api.to_boolean(p_commit) THEN
1943 ROLLBACK TO update_instance_asset_pvt;
1944 END IF;
1945 RETURN;
1946 END IF;
1947 END IF;
1948
1949 -- Generate the instance asset history id from the sequence
1950 l_instance_asset_hist_id := CSI_Asset_vld_pvt.gen_inst_asset_hist_id;
1951
1952 -- Get the full_dump_frequency from csi_install_parameter
1953 IF csi_datastructures_pub.g_install_param_rec.fetch_flag IS NULL THEN
1954 csi_gen_utility_pvt.populate_install_param_rec;
1955 END IF;
1956 --
1957 l_full_dump_frequency := csi_datastructures_pub.g_install_param_rec.history_full_dump_frequency;
1958 --
1959 IF l_full_dump_frequency IS NULL THEN
1960 FND_MESSAGE.SET_NAME('CSI','CSI_API_GET_FULL_DUMP_FAILED');
1961 FND_MSG_PUB.ADD;
1962 RAISE FND_API.G_EXC_ERROR;
1963 END IF;
1964 --
1965 SELECT mod(l_object_version_number,l_full_dump_frequency)
1966 INTO l_mod_value
1967 FROM dual;
1968
1969 l_temp_inst_asset_rec.instance_asset_id := p_instance_asset_rec.instance_asset_id ;
1970 l_temp_inst_asset_rec.instance_id := p_instance_asset_rec.instance_id ;
1971 l_temp_inst_asset_rec.fa_asset_id := p_instance_asset_rec.fa_asset_id ;
1972 l_temp_inst_asset_rec.fa_book_type_code := p_instance_asset_rec.fa_book_type_code ;
1973 l_temp_inst_asset_rec.fa_location_id := p_instance_asset_rec.fa_location_id ;
1974 l_temp_inst_asset_rec.asset_quantity := p_instance_asset_rec.asset_quantity ;
1975 l_temp_inst_asset_rec.update_status := p_instance_asset_rec.update_status ;
1976 l_temp_inst_asset_rec.active_start_date := p_instance_asset_rec.active_start_date ;
1977 l_temp_inst_asset_rec.active_end_date := p_instance_asset_rec.active_end_date ;
1978 l_temp_inst_asset_rec.object_version_number := p_instance_asset_rec.object_version_number ;
1979
1980 -- Start of modification for Bug#2547034 on 09/20/02 - rtalluri
1981 BEGIN
1982 SELECT instance_asset_history_id
1983 INTO l_asset_hist_id
1984 FROM csi_i_assets_h h
1985 WHERE h.transaction_id = p_txn_rec.transaction_id
1986 AND h.instance_asset_id = p_instance_asset_rec.instance_asset_id;
1987
1988 OPEN asset_hist_csr(l_asset_hist_id);
1989 FETCH asset_hist_csr INTO l_asset_hist_csr ;
1990 CLOSE asset_hist_csr;
1991
1992 IF l_asset_hist_csr.full_dump_flag = 'Y' THEN
1993 CSI_I_ASSETS_H_PKG.Update_Row (
1994 p_INSTANCE_ASSET_HISTORY_ID => l_asset_hist_id,
1995 p_INSTANCE_ASSET_ID => fnd_api.g_miss_num,
1996 p_TRANSACTION_ID => fnd_api.g_miss_num,
1997 p_OLD_INSTANCE_ID => fnd_api.g_miss_num,
1998 p_NEW_INSTANCE_ID => l_temp_inst_asset_rec.INSTANCE_ID,
1999 p_OLD_FA_ASSET_ID => fnd_api.g_miss_num,
2000 p_NEW_FA_ASSET_ID => l_temp_inst_asset_rec.FA_ASSET_ID,
2001 p_OLD_ASSET_QUANTITY => fnd_api.g_miss_num,
2002 p_NEW_ASSET_QUANTITY => l_temp_inst_asset_rec.ASSET_QUANTITY,
2003 p_OLD_FA_BOOK_TYPE_CODE => fnd_api.g_miss_char,
2004 p_NEW_FA_BOOK_TYPE_CODE => l_temp_inst_asset_rec.FA_BOOK_TYPE_CODE,
2005 p_OLD_FA_LOCATION_ID => fnd_api.g_miss_num,
2006 p_NEW_FA_LOCATION_ID => l_temp_inst_asset_rec.FA_LOCATION_ID,
2007 p_OLD_UPDATE_STATUS => fnd_api.g_miss_char,
2008 p_NEW_UPDATE_STATUS => l_temp_inst_asset_rec.UPDATE_STATUS,
2009 p_OLD_FA_SYNC_FLAG => fnd_api.g_miss_char,
2010 p_NEW_FA_SYNC_FLAG => l_temp_inst_asset_rec.FA_SYNC_FLAG,
2011 p_OLD_FA_MASS_ADDITION_ID => fnd_api.g_miss_num,
2012 p_NEW_FA_MASS_ADDITION_ID => l_temp_inst_asset_rec.FA_MASS_ADDITION_ID,
2013 p_OLD_CREATION_COMPLETE_FLAG => fnd_api.g_miss_char,
2014 p_NEW_CREATION_COMPLETE_FLAG => l_temp_inst_asset_rec.CREATION_COMPLETE_FLAG,
2015 p_FULL_DUMP_FLAG => fnd_api.g_miss_char,
2016 p_CREATED_BY => fnd_api.g_miss_num,
2017 p_CREATION_DATE => fnd_api.g_miss_date,
2018 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
2019 p_LAST_UPDATE_DATE => SYSDATE,
2020 p_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID,
2024 p_OLD_ACTIVE_END_DATE => fnd_api.g_miss_date,
2021 p_OBJECT_VERSION_NUMBER => fnd_api.g_miss_num,
2022 p_OLD_ACTIVE_START_DATE => fnd_api.g_miss_date,
2023 p_NEW_ACTIVE_START_DATE => l_temp_inst_asset_rec.ACTIVE_START_DATE,
2025 p_NEW_ACTIVE_END_DATE => l_temp_inst_asset_rec.ACTIVE_END_DATE);
2026
2027 ELSE
2028
2029 IF ( l_asset_hist_csr.old_instance_id IS NULL AND l_asset_hist_csr.new_instance_id IS NULL ) THEN
2030 IF ( l_temp_inst_asset_rec.instance_id = l_curr_asset_rec.instance_id )
2031 OR ( l_temp_inst_asset_rec.instance_id = fnd_api.g_miss_num )
2032 THEN
2033 l_asset_hist_csr.old_instance_id := NULL;
2034 l_asset_hist_csr.new_instance_id := NULL;
2035 ELSE
2036 l_asset_hist_csr.old_instance_id := fnd_api.g_miss_num;
2037 l_asset_hist_csr.new_instance_id := l_temp_inst_asset_rec.instance_id;
2038 END IF;
2039 ELSE
2040 l_asset_hist_csr.old_instance_id := fnd_api.g_miss_num;
2041 l_asset_hist_csr.new_instance_id := l_temp_inst_asset_rec.instance_id;
2042 END IF;
2043 --
2044 IF ( l_asset_hist_csr.old_fa_asset_id IS NULL AND l_asset_hist_csr.new_fa_asset_id IS NULL ) THEN
2045 IF ( l_temp_inst_asset_rec.fa_asset_id = l_curr_asset_rec.fa_asset_id )
2046 OR
2047 ( l_temp_inst_asset_rec.fa_asset_id = fnd_api.g_miss_num )
2048 THEN
2049 l_asset_hist_csr.old_fa_asset_id := NULL;
2050 l_asset_hist_csr.new_fa_asset_id := NULL;
2051 ELSE
2052 l_asset_hist_csr.old_fa_asset_id := fnd_api.g_miss_num;
2053 l_asset_hist_csr.new_fa_asset_id := l_temp_inst_asset_rec.fa_asset_id;
2054 END IF;
2055 ELSE
2056 l_asset_hist_csr.old_fa_asset_id := fnd_api.g_miss_num;
2057 l_asset_hist_csr.new_fa_asset_id := l_temp_inst_asset_rec.fa_asset_id;
2058 END IF;
2059 --
2060 IF ( l_asset_hist_csr.old_fa_book_type_code IS NULL AND l_asset_hist_csr.new_fa_book_type_code IS NULL) THEN
2061 IF ( l_temp_inst_asset_rec.fa_book_type_code = l_curr_asset_rec.fa_book_type_code )
2062 OR ( l_temp_inst_asset_rec.fa_book_type_code = fnd_api.g_miss_char ) THEN
2063 l_asset_hist_csr.old_fa_book_type_code := NULL;
2064 l_asset_hist_csr.new_fa_book_type_code := NULL;
2065 ELSE
2066 l_asset_hist_csr.old_fa_book_type_code := fnd_api.g_miss_char;
2067 l_asset_hist_csr.new_fa_book_type_code := l_temp_inst_asset_rec.fa_book_type_code;
2068 END IF;
2069 ELSE
2070 l_asset_hist_csr.old_fa_book_type_code := fnd_api.g_miss_char;
2071 l_asset_hist_csr.new_fa_book_type_code := l_temp_inst_asset_rec.fa_book_type_code;
2072 END IF;
2073 --
2074 IF ( l_asset_hist_csr.old_fa_location_id IS NULL
2075 AND l_asset_hist_csr.new_fa_location_id IS NULL ) THEN
2076 IF ( l_temp_inst_asset_rec.fa_location_id = l_curr_asset_rec.fa_location_id )
2077 OR ( l_temp_inst_asset_rec.fa_location_id = fnd_api.g_miss_num ) THEN
2078 l_asset_hist_csr.old_fa_location_id := NULL;
2079 l_asset_hist_csr.new_fa_location_id := NULL;
2080 ELSE
2081 l_asset_hist_csr.old_fa_location_id := fnd_api.g_miss_num;
2082 l_asset_hist_csr.new_fa_location_id := l_temp_inst_asset_rec.fa_location_id;
2083 END IF;
2084 ELSE
2085 l_asset_hist_csr.old_fa_location_id := fnd_api.g_miss_num;
2086 l_asset_hist_csr.new_fa_location_id := l_temp_inst_asset_rec.fa_location_id;
2087 END IF;
2088 --
2089 IF ( l_asset_hist_csr.old_asset_quantity IS NULL
2090 AND l_asset_hist_csr.new_asset_quantity IS NULL ) THEN
2091 IF ( l_temp_inst_asset_rec.asset_quantity = l_curr_asset_rec.asset_quantity )
2092 OR ( l_temp_inst_asset_rec.asset_quantity = fnd_api.g_miss_num ) THEN
2093 l_asset_hist_csr.old_asset_quantity := NULL;
2094 l_asset_hist_csr.new_asset_quantity := NULL;
2095 ELSE
2096 l_asset_hist_csr.old_asset_quantity := fnd_api.g_miss_num;
2097 l_asset_hist_csr.new_asset_quantity := l_temp_inst_asset_rec.asset_quantity;
2098 END IF;
2099 ELSE
2100 l_asset_hist_csr.old_asset_quantity := fnd_api.g_miss_num;
2101 l_asset_hist_csr.new_asset_quantity := l_temp_inst_asset_rec.asset_quantity;
2102 END IF;
2103 --
2104 IF ( l_asset_hist_csr.old_update_status IS NULL
2105 AND l_asset_hist_csr.new_update_status IS NULL ) THEN
2106 IF ( l_temp_inst_asset_rec.update_status = l_curr_asset_rec.update_status )
2107 OR ( l_temp_inst_asset_rec.update_status = fnd_api.g_miss_char ) THEN
2108 l_asset_hist_csr.old_update_status := NULL;
2109 l_asset_hist_csr.new_update_status := NULL;
2110 ELSE
2111 l_asset_hist_csr.old_update_status := fnd_api.g_miss_char;
2115 l_asset_hist_csr.old_update_status := fnd_api.g_miss_char;
2112 l_asset_hist_csr.new_update_status := l_temp_inst_asset_rec.update_status;
2113 END IF;
2114 ELSE
2116 l_asset_hist_csr.new_update_status := l_temp_inst_asset_rec.update_status;
2117 END IF;
2118 --
2119 IF ( l_asset_hist_csr.old_active_start_date IS NULL
2120 AND l_asset_hist_csr.new_active_start_date IS NULL ) THEN
2121 IF ( l_temp_inst_asset_rec.active_start_date = l_curr_asset_rec.active_start_date )
2122 OR ( l_temp_inst_asset_rec.active_start_date = fnd_api.g_miss_date ) THEN
2123 l_asset_hist_csr.old_active_start_date := NULL;
2124 l_asset_hist_csr.new_active_start_date := NULL;
2125 ELSE
2126 l_asset_hist_csr.old_active_start_date := fnd_api.g_miss_date;
2127 l_asset_hist_csr.new_active_start_date := l_temp_inst_asset_rec.active_start_date;
2128 END IF;
2129 ELSE
2130 l_asset_hist_csr.old_active_start_date := fnd_api.g_miss_date;
2131 l_asset_hist_csr.new_active_start_date := l_temp_inst_asset_rec.active_start_date;
2132 END IF;
2133 --
2134 IF ( l_asset_hist_csr.old_active_end_date IS NULL
2135 AND l_asset_hist_csr.new_active_end_date IS NULL ) THEN
2136 IF ( l_temp_inst_asset_rec.active_end_date = l_curr_asset_rec.active_end_date )
2137 OR ( l_temp_inst_asset_rec.active_end_date = fnd_api.g_miss_date ) THEN
2138 l_asset_hist_csr.old_active_end_date := NULL;
2139 l_asset_hist_csr.new_active_end_date := NULL;
2140 ELSE
2141 l_asset_hist_csr.old_active_end_date := fnd_api.g_miss_date;
2142 l_asset_hist_csr.new_active_end_date := l_temp_inst_asset_rec.active_end_date;
2143 END IF;
2144 ELSE
2145 l_asset_hist_csr.old_active_end_date := fnd_api.g_miss_date;
2146 l_asset_hist_csr.new_active_end_date := l_temp_inst_asset_rec.active_end_date;
2147 END IF;
2148
2149
2150 csi_i_assets_h_pkg.update_row (
2151 p_instance_asset_history_id => l_asset_hist_id ,
2152 p_instance_asset_id => fnd_api.g_miss_num ,
2153 p_transaction_id => fnd_api.g_miss_num ,
2154 p_old_instance_id => l_asset_hist_csr.old_instance_id ,
2155 p_new_instance_id => l_asset_hist_csr.new_instance_id ,
2156 p_old_fa_asset_id => l_asset_hist_csr.old_fa_asset_id ,
2157 p_new_fa_asset_id => l_asset_hist_csr.new_fa_asset_id ,
2158 p_old_asset_quantity => l_asset_hist_csr.old_asset_quantity ,
2159 p_new_asset_quantity => l_asset_hist_csr.new_asset_quantity ,
2160 p_old_fa_book_type_code => l_asset_hist_csr.old_fa_book_type_code ,
2161 p_new_fa_book_type_code => l_asset_hist_csr.new_fa_book_type_code ,
2162 p_old_fa_location_id => l_asset_hist_csr.old_fa_location_id ,
2163 p_new_fa_location_id => l_asset_hist_csr.new_fa_location_id ,
2164 p_old_update_status => l_asset_hist_csr.old_update_status ,
2165 p_new_update_status => l_asset_hist_csr.new_update_status ,
2166 p_OLD_FA_SYNC_FLAG => l_asset_hist_csr.old_fa_sync_flag,
2167 p_NEW_FA_SYNC_FLAG => l_asset_hist_csr.new_fa_sync_flag,
2168 p_OLD_FA_MASS_ADDITION_ID => l_asset_hist_csr.old_fa_mass_addition_id,
2169 p_NEW_FA_MASS_ADDITION_ID => l_asset_hist_csr.new_fa_mass_addition_id,
2170 p_OLD_CREATION_COMPLETE_FLAG => l_asset_hist_csr.old_creation_complete_flag,
2171 p_NEW_CREATION_COMPLETE_FLAG => l_asset_hist_csr.new_creation_complete_flag,
2172 p_full_dump_flag => fnd_api.g_miss_char ,
2173 p_created_by => fnd_api.g_miss_num ,
2174 p_creation_date => fnd_api.g_miss_date ,
2175 p_last_updated_by => fnd_global.user_id ,
2176 p_last_update_date => SYSDATE ,
2177 p_last_update_login => fnd_global.login_id ,
2178 p_object_version_number => fnd_api.g_miss_num ,
2179 p_old_active_start_date => l_asset_hist_csr.old_active_start_date ,
2180 p_new_active_start_date => l_asset_hist_csr.new_active_start_date ,
2181 p_old_active_end_date => l_asset_hist_csr.old_active_end_date ,
2182 p_new_active_end_date => l_asset_hist_csr.new_active_end_date );
2183 END IF;
2184
2185 EXCEPTION
2186 WHEN NO_DATA_FOUND THEN
2187
2188 IF (l_mod_value = 0) THEN
2189 -- If the mod value is 0 then dump all the columns both changed and unchanged
2190 -- changed columns have old and new values while the unchanged values have old and new values
2191 -- exactly same
2192 -- assign the party rec
2193 l_temp_inst_asset_rec.instance_asset_id := p_instance_asset_rec.instance_asset_id ;
2197 l_temp_inst_asset_rec.fa_location_id := p_instance_asset_rec.fa_location_id ;
2194 l_temp_inst_asset_rec.instance_id := p_instance_asset_rec.instance_id ;
2195 l_temp_inst_asset_rec.fa_asset_id := p_instance_asset_rec.fa_asset_id ;
2196 l_temp_inst_asset_rec.fa_book_type_code := p_instance_asset_rec.fa_book_type_code ;
2198 l_temp_inst_asset_rec.asset_quantity := p_instance_asset_rec.asset_quantity ;
2199 l_temp_inst_asset_rec.update_status := p_instance_asset_rec.update_status ;
2200 l_temp_inst_asset_rec.active_start_date := p_instance_asset_rec.active_start_date ;
2201 l_temp_inst_asset_rec.active_end_date := p_instance_asset_rec.active_end_date ;
2202 l_temp_inst_asset_rec.object_version_number := p_instance_asset_rec.object_version_number ;
2203
2204 IF (p_instance_asset_rec.FA_ASSET_ID = FND_API.G_MISS_NUM) THEN
2205 l_temp_inst_asset_rec.FA_ASSET_ID := l_curr_asset_rec.FA_ASSET_ID ;
2206 END IF;
2207 IF (p_instance_asset_rec.ASSET_QUANTITY = FND_API.G_MISS_NUM) THEN
2208 l_temp_inst_asset_rec.ASSET_QUANTITY := l_curr_asset_rec.ASSET_QUANTITY ;
2209 END IF;
2210 IF (p_instance_asset_rec.FA_BOOK_TYPE_CODE = FND_API.G_MISS_CHAR) THEN
2211 l_temp_inst_asset_rec.FA_BOOK_TYPE_CODE := l_curr_asset_rec.FA_BOOK_TYPE_CODE ;
2212 END IF;
2213 IF (p_instance_asset_rec.FA_LOCATION_ID = FND_API.G_MISS_NUM) THEN
2214 l_temp_inst_asset_rec.FA_LOCATION_ID := l_curr_asset_rec.FA_LOCATION_ID ;
2215 END IF;
2216 IF (p_instance_asset_rec.UPDATE_STATUS = FND_API.G_MISS_CHAR) THEN
2217 l_temp_inst_asset_rec.UPDATE_STATUS := l_curr_asset_rec.UPDATE_STATUS ;
2218 END IF;
2219
2220 -- Call table handlers to insert into history table
2221 CSI_I_ASSETS_H_PKG.Insert_Row
2222 (
2223 px_INSTANCE_ASSET_HISTORY_ID => l_instance_asset_hist_id ,
2224 p_INSTANCE_ASSET_ID => p_instance_asset_rec.INSTANCE_ASSET_ID ,
2225 p_TRANSACTION_ID => p_txn_rec.transaction_id ,
2226 p_OLD_INSTANCE_ID => l_curr_asset_rec.INSTANCE_ID ,
2227 p_NEW_INSTANCE_ID => l_temp_inst_asset_rec.INSTANCE_ID ,
2228 p_OLD_FA_ASSET_ID => l_curr_asset_rec.FA_ASSET_ID ,
2229 p_NEW_FA_ASSET_ID => l_temp_inst_asset_rec.FA_ASSET_ID ,
2230 p_OLD_ASSET_QUANTITY => l_curr_asset_rec.ASSET_QUANTITY ,
2231 p_NEW_ASSET_QUANTITY => l_temp_inst_asset_rec.ASSET_QUANTITY ,
2232 p_OLD_FA_BOOK_TYPE_CODE => l_curr_asset_rec.FA_BOOK_TYPE_CODE ,
2233 p_NEW_FA_BOOK_TYPE_CODE => l_temp_inst_asset_rec.FA_BOOK_TYPE_CODE ,
2234 p_OLD_FA_LOCATION_ID => l_curr_asset_rec.FA_LOCATION_ID ,
2235 p_NEW_FA_LOCATION_ID => l_temp_inst_asset_rec.FA_LOCATION_ID ,
2236 p_OLD_UPDATE_STATUS => l_curr_asset_rec.UPDATE_STATUS ,
2237 p_NEW_UPDATE_STATUS => l_temp_inst_asset_rec.UPDATE_STATUS ,
2238 p_OLD_FA_SYNC_FLAG => l_curr_asset_rec.FA_SYNC_FLAG,
2239 p_NEW_FA_SYNC_FLAG => l_temp_inst_asset_rec.FA_SYNC_FLAG,
2240 p_OLD_FA_MASS_ADDITION_ID => l_curr_asset_rec.FA_MASS_ADDITION_ID,
2241 p_NEW_FA_MASS_ADDITION_ID => l_temp_inst_asset_rec.FA_MASS_ADDITION_ID,
2242 p_OLD_CREATION_COMPLETE_FLAG => l_curr_asset_rec.CREATION_COMPLETE_FLAG,
2243 p_NEW_CREATION_COMPLETE_FLAG => l_temp_inst_asset_rec.creation_complete_flag,
2244 p_FULL_DUMP_FLAG => 'Y' ,
2245 p_CREATED_BY => FND_GLOBAL.USER_ID ,
2246 p_CREATION_DATE => SYSDATE ,
2247 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID ,
2248 p_LAST_UPDATE_DATE => SYSDATE ,
2249 p_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID ,
2250 p_OBJECT_VERSION_NUMBER => 1 ,
2251 p_OLD_ACTIVE_START_DATE => l_curr_asset_rec.ACTIVE_START_DATE ,
2252 p_NEW_ACTIVE_START_DATE => l_temp_inst_asset_rec.ACTIVE_START_DATE,
2253 p_OLD_ACTIVE_END_DATE => l_curr_asset_rec.ACTIVE_END_DATE ,
2254 p_NEW_ACTIVE_END_DATE => l_temp_inst_asset_rec.ACTIVE_END_DATE );
2255
2256
2257 ELSE
2258 -- assign the party rec
2259 l_temp_inst_asset_rec := l_curr_asset_rec;
2260
2261 -- If the mod value is not equal to zero then dump only the changed columns
2262 -- while the unchanged values have old and new values as null
2263 IF (p_instance_asset_rec.fa_asset_id = fnd_api.g_miss_num) OR
2264 NVL(p_instance_asset_rec.fa_asset_id, fnd_api.g_miss_num) = NVL(l_temp_inst_asset_rec.fa_asset_id, fnd_api.g_miss_num) THEN
2265 l_ins_asset_hist_rec.old_fa_asset_id := NULL;
2266 l_ins_asset_hist_rec.new_fa_asset_id := NULL;
2267 ELSIF
2268 NVL(l_temp_inst_asset_rec.fa_asset_id,fnd_api.g_miss_num) <> NVL(p_instance_asset_rec.fa_asset_id,fnd_api.g_miss_num) THEN
2269 l_ins_asset_hist_rec.old_fa_asset_id := l_temp_inst_asset_rec.fa_asset_id ;
2270 l_ins_asset_hist_rec.new_fa_asset_id := p_instance_asset_rec.fa_asset_id ;
2271 END IF;
2272 --
2276 l_ins_asset_hist_rec.new_asset_quantity := NULL;
2273 IF (p_instance_asset_rec.asset_quantity = fnd_api.g_miss_num) OR
2274 NVL(p_instance_asset_rec.asset_quantity, fnd_api.g_miss_num) = NVL(l_temp_inst_asset_rec.asset_quantity, fnd_api.g_miss_num) THEN
2275 l_ins_asset_hist_rec.old_asset_quantity := NULL;
2277 ELSIF
2278 NVL(l_temp_inst_asset_rec.asset_quantity,fnd_api.g_miss_num) <> NVL(p_instance_asset_rec.asset_quantity,fnd_api.g_miss_num) THEN
2279 l_ins_asset_hist_rec.old_asset_quantity := l_temp_inst_asset_rec.asset_quantity ;
2280 l_ins_asset_hist_rec.new_asset_quantity := p_instance_asset_rec.asset_quantity ;
2281 END IF;
2282 --
2283 IF (p_instance_asset_rec.fa_book_type_code = fnd_api.g_miss_char) OR
2284 NVL(p_instance_asset_rec.fa_book_type_code, fnd_api.g_miss_char) = NVL(l_temp_inst_asset_rec.fa_book_type_code, fnd_api.g_miss_char) THEN
2285 l_ins_asset_hist_rec.old_fa_book_type_code := NULL;
2286 l_ins_asset_hist_rec.new_fa_book_type_code := NULL;
2287 ELSIF
2288 NVL(l_temp_inst_asset_rec.fa_book_type_code,fnd_api.g_miss_char) <> NVL(p_instance_asset_rec.fa_book_type_code,fnd_api.g_miss_char) THEN
2289 l_ins_asset_hist_rec.old_fa_book_type_code := l_temp_inst_asset_rec.fa_book_type_code ;
2290 l_ins_asset_hist_rec.new_fa_book_type_code := p_instance_asset_rec.fa_book_type_code ;
2291 END IF;
2292 --
2293 IF (p_instance_asset_rec.fa_location_id = fnd_api.g_miss_num) OR
2294 NVL(p_instance_asset_rec.fa_location_id, fnd_api.g_miss_num) = NVL(l_temp_inst_asset_rec.fa_location_id, fnd_api.g_miss_num) THEN
2295 l_ins_asset_hist_rec.old_fa_location_id := NULL;
2296 l_ins_asset_hist_rec.new_fa_location_id := NULL;
2297 ELSIF
2298 NVL(l_temp_inst_asset_rec.fa_location_id,fnd_api.g_miss_num) <> NVL(p_instance_asset_rec.fa_location_id,fnd_api.g_miss_num) THEN
2299 l_ins_asset_hist_rec.old_fa_location_id := l_temp_inst_asset_rec.fa_location_id ;
2300 l_ins_asset_hist_rec.new_fa_location_id := p_instance_asset_rec.fa_location_id ;
2301 END IF;
2302 --
2303 IF (p_instance_asset_rec.update_status = fnd_api.g_miss_char) OR
2304 NVL(p_instance_asset_rec.update_status, fnd_api.g_miss_char) = NVL(l_temp_inst_asset_rec.update_status, fnd_api.g_miss_char) THEN
2305 l_ins_asset_hist_rec.old_fa_book_type_code := NULL;
2306 l_ins_asset_hist_rec.new_fa_book_type_code := NULL;
2307 ELSIF
2308 NVL(l_temp_inst_asset_rec.update_status,fnd_api.g_miss_char) <> NVL(p_instance_asset_rec.update_status,fnd_api.g_miss_char) THEN
2309 l_ins_asset_hist_rec.old_fa_book_type_code := l_temp_inst_asset_rec.update_status ;
2310 l_ins_asset_hist_rec.new_fa_book_type_code := p_instance_asset_rec.update_status ;
2311 END IF;
2312 --
2313 IF (p_instance_asset_rec.active_start_date = fnd_api.g_miss_date) OR
2314 NVL(p_instance_asset_rec.active_start_date, fnd_api.g_miss_date) = NVL(l_temp_inst_asset_rec.active_start_date, fnd_api.g_miss_date) THEN
2315 l_ins_asset_hist_rec.old_active_start_date := NULL;
2316 l_ins_asset_hist_rec.new_active_start_date := NULL;
2317 ELSIF
2318 NVL(l_temp_inst_asset_rec.active_start_date,fnd_api.g_miss_date) <> NVL(p_instance_asset_rec.active_start_date,fnd_api.g_miss_date) THEN
2319 l_ins_asset_hist_rec.old_active_start_date := l_temp_inst_asset_rec.active_start_date ;
2320 l_ins_asset_hist_rec.new_active_start_date := p_instance_asset_rec.active_start_date ;
2321 END IF;
2322 --
2323 IF (p_instance_asset_rec.active_end_date = fnd_api.g_miss_date) OR
2324 NVL(p_instance_asset_rec.active_end_date, fnd_api.g_miss_date) = NVL(l_temp_inst_asset_rec.active_end_date, fnd_api.g_miss_date) THEN
2325 l_ins_asset_hist_rec.old_active_end_date := NULL;
2326 l_ins_asset_hist_rec.new_active_end_date := NULL;
2327 ELSIF
2328 NVL(l_temp_inst_asset_rec.active_end_date,fnd_api.g_miss_date) <> NVL(p_instance_asset_rec.active_end_date,fnd_api.g_miss_date) THEN
2329 l_ins_asset_hist_rec.old_active_end_date := l_temp_inst_asset_rec.active_end_date ;
2330 l_ins_asset_hist_rec.new_active_end_date := p_instance_asset_rec.active_end_date ;
2331 END IF;
2332 --
2333
2334 -- Call table handlers to insert into history table
2335 CSI_I_ASSETS_H_PKG.Insert_Row (
2336 px_INSTANCE_ASSET_HISTORY_ID => l_instance_asset_hist_id ,
2337 p_INSTANCE_ASSET_ID => p_instance_asset_rec.INSTANCE_ASSET_ID ,
2338 p_TRANSACTION_ID => p_txn_rec.transaction_id ,
2339 p_OLD_INSTANCE_ID => l_ins_asset_hist_rec.old_INSTANCE_ID ,
2340 p_NEW_INSTANCE_ID => l_ins_asset_hist_rec.new_INSTANCE_ID ,
2341 p_OLD_FA_ASSET_ID => l_ins_asset_hist_rec.old_FA_ASSET_ID ,
2342 p_NEW_FA_ASSET_ID => l_ins_asset_hist_rec.new_FA_ASSET_ID ,
2343 p_OLD_ASSET_QUANTITY => l_ins_asset_hist_rec.old_ASSET_QUANTITY ,
2344 p_NEW_ASSET_QUANTITY => l_ins_asset_hist_rec.new_ASSET_QUANTITY ,
2345 p_OLD_FA_BOOK_TYPE_CODE => l_ins_asset_hist_rec.old_FA_BOOK_TYPE_CODE ,
2346 p_NEW_FA_BOOK_TYPE_CODE => l_ins_asset_hist_rec.new_FA_BOOK_TYPE_CODE ,
2347 p_OLD_FA_LOCATION_ID => l_ins_asset_hist_rec.old_FA_LOCATION_ID ,
2351 p_OLD_FA_SYNC_FLAG => l_ins_asset_hist_rec.old_fa_sync_flag,
2348 p_NEW_FA_LOCATION_ID => l_ins_asset_hist_rec.new_FA_LOCATION_ID ,
2349 p_OLD_UPDATE_STATUS => l_ins_asset_hist_rec.old_UPDATE_STATUS ,
2350 p_NEW_UPDATE_STATUS => l_ins_asset_hist_rec.new_UPDATE_STATUS ,
2352 p_NEW_FA_SYNC_FLAG => l_ins_asset_hist_rec.new_fa_sync_flag,
2353 p_OLD_FA_MASS_ADDITION_ID => l_ins_asset_hist_rec.old_fa_mass_addition_id,
2354 p_NEW_FA_MASS_ADDITION_ID => l_ins_asset_hist_rec.new_fa_mass_addition_id,
2355 p_OLD_CREATION_COMPLETE_FLAG => l_ins_asset_hist_rec.old_creation_complete_flag,
2356 p_NEW_CREATION_COMPLETE_FLAG => l_ins_asset_hist_rec.new_creation_complete_flag,
2357 p_FULL_DUMP_FLAG => 'N' ,
2358 p_CREATED_BY => FND_GLOBAL.USER_ID ,
2359 p_CREATION_DATE => SYSDATE ,
2360 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID ,
2361 p_LAST_UPDATE_DATE => SYSDATE ,
2362 p_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID ,
2363 p_OBJECT_VERSION_NUMBER => 1 ,
2364 p_OLD_ACTIVE_START_DATE => l_ins_asset_hist_rec.old_ACTIVE_START_DATE ,
2365 p_NEW_ACTIVE_START_DATE => l_ins_asset_hist_rec.new_ACTIVE_START_DATE ,
2366 p_OLD_ACTIVE_END_DATE => l_ins_asset_hist_rec.old_ACTIVE_END_DATE ,
2367 p_NEW_ACTIVE_END_DATE => l_ins_asset_hist_rec.new_ACTIVE_END_DATE );
2368
2369 END IF;
2370
2371 END;
2372 -- End of modification for Bug#2547034 on 09/20/02 - rtalluri
2373
2374 --update the accounting class code in the csi_item_instances
2375 csi_item_instance_pvt.get_and_update_acct_class
2376 ( p_api_version => p_api_version
2377 ,p_commit => p_commit
2378 ,p_init_msg_list => p_init_msg_list
2379 ,p_validation_level => p_validation_level
2380 ,p_instance_id => l_curr_asset_rec.instance_id
2381 ,p_instance_expiry_flag => p_instance_asset_rec.check_for_instance_expiry
2382 ,p_txn_rec => p_txn_rec
2383 ,x_acct_class_code => l_acct_class_code
2384 ,x_return_status => x_return_status
2385 ,x_msg_count => x_msg_count
2386 ,x_msg_data => x_msg_data
2387 );
2388
2389 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2390 FOR i in 1..x_msg_Count LOOP
2391 FND_MSG_PUB.Get(p_msg_index => i,
2392 p_encoded => 'F',
2393 p_data => x_msg_data,
2394 p_msg_index_out => x_msg_index_out );
2395 End LOOP;
2396 RAISE fnd_api.g_exc_error;
2397 END IF;
2398
2399 --
2400 -- End of API body
2401
2402 -- Standard check of p_commit.
2403 IF FND_API.To_Boolean( p_commit ) THEN
2404 COMMIT WORK;
2405 END IF;
2406
2407 -- Standard call to get message count and if count is get message info.
2408 FND_MSG_PUB.Count_And_Get(
2409 p_count => x_msg_count ,
2410 p_data => x_msg_data);
2411 EXCEPTION
2412 WHEN FND_API.G_EXC_ERROR THEN
2413 X_return_status := FND_API.G_RET_STS_ERROR ;
2414 IF fnd_api.to_boolean(p_commit) THEN
2415 ROLLBACK TO update_instance_asset_pvt;
2416 END IF;
2417 FND_MSG_PUB.Count_And_Get (
2418 p_count => x_msg_count,
2419 p_data => x_msg_data);
2420 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2421 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2422 IF fnd_api.to_boolean(p_commit) THEN
2423 ROLLBACK TO update_instance_asset_pvt;
2424 END IF;
2425 FND_MSG_PUB.Count_And_Get(
2426 p_count => x_msg_count,
2427 p_data => x_msg_data);
2428 WHEN OTHERS THEN
2429 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2430 IF fnd_api.to_boolean(p_commit) THEN
2431 ROLLBACK TO update_instance_asset_pvt;
2432 END IF;
2433 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2434 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, l_api_name );
2435 END IF;
2436 FND_MSG_PUB.Count_And_Get(
2437 p_count => x_msg_count,
2438 p_data => x_msg_data);
2439 END update_instance_asset;
2440
2441 /*-------------------------------------------------------*/
2442 /* procedure name: get_instance_asset_hist */
2443 /* description : Retreives asset history for */
2444 /* a given transaction */
2445 /*-------------------------------------------------------*/
2446
2447
2448 PROCEDURE get_instance_asset_hist
2449 ( p_api_version IN NUMBER
2450 ,p_commit IN VARCHAR2
2451 ,p_init_msg_list IN VARCHAR2
2452 ,p_validation_level IN NUMBER
2453 ,p_transaction_id IN NUMBER
2454 ,x_ins_asset_hist_tbl OUT NOCOPY csi_datastructures_pub.ins_asset_history_tbl
2455 ,x_return_status OUT NOCOPY VARCHAR2
2456 ,x_msg_count OUT NOCOPY NUMBER
2457 ,x_msg_data OUT NOCOPY VARCHAR2
2458 ) IS
2459
2460 CURSOR txn_asset_hist_csr (p_txn_id IN NUMBER) IS
2461 SELECT ah.INSTANCE_ASSET_HISTORY_ID ,
2462 ah.INSTANCE_ASSET_ID ,
2466 ah.OLD_FA_ASSET_ID ,
2463 ah.TRANSACTION_ID ,
2464 ah.OLD_INSTANCE_ID ,
2465 ah.NEW_INSTANCE_ID ,
2467 ah.NEW_FA_ASSET_ID ,
2468 ah.OLD_ASSET_QUANTITY ,
2469 ah.NEW_ASSET_QUANTITY ,
2470 ah.OLD_FA_BOOK_TYPE_CODE ,
2471 ah.NEW_FA_BOOK_TYPE_CODE ,
2472 ah.OLD_FA_LOCATION_ID ,
2473 ah.NEW_FA_LOCATION_ID ,
2474 ah.OLD_UPDATE_STATUS ,
2475 ah.NEW_UPDATE_STATUS ,
2476 ah.FULL_DUMP_FLAG ,
2477 ah.OBJECT_VERSION_NUMBER ,
2478 ah.SECURITY_GROUP_ID ,
2479 ah.OLD_ACTIVE_START_DATE ,
2480 ah.NEW_ACTIVE_START_DATE ,
2481 ah.OLD_ACTIVE_END_DATE ,
2482 ah.NEW_ACTIVE_END_DATE ,
2483 a.INSTANCE_ID
2484 FROM csi_i_assets_h ah,
2485 csi_i_assets a
2486 WHERE ah.transaction_id = p_txn_id
2487 AND ah.instance_asset_id = a.instance_asset_id;
2488
2489 l_api_name CONSTANT VARCHAR2(30) := 'get_instance_asset_hist';
2490 l_api_version CONSTANT NUMBER := 1.0;
2491 l_old_ins_asset_rec csi_datastructures_pub.instance_asset_header_rec;
2492 l_new_ins_asset_rec csi_datastructures_pub.instance_asset_header_rec;
2493 l_old_ins_asset_tbl csi_datastructures_pub.instance_asset_header_tbl;
2494 l_new_ins_asset_tbl csi_datastructures_pub.instance_asset_header_tbl;
2495 l_ins_asset_rec csi_datastructures_pub.ins_asset_history_rec;
2496 l_temp_asset_rec csi_datastructures_pub.ins_asset_history_rec;
2497 i NUMBER :=0 ;
2498 BEGIN
2499 /*
2500 IF fnd_api.to_boolean(p_commit)
2501 THEN
2502 SAVEPOINT get_instance_asset_hist;
2503 END IF;
2504 */
2505
2506 -- Standard call to check for call compatibility.
2507 IF NOT FND_API.Compatible_API_Call (l_api_version ,
2508 p_api_version ,
2509 l_api_name ,
2510 G_PKG_NAME )
2511 THEN
2512 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2513 END IF;
2514
2515 -- Initialize message list if p_init_msg_list is set to TRUE.
2516 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2517 FND_MSG_PUB.initialize;
2518 END IF;
2519
2520 -- Initialize API return status to success
2521 x_return_status := FND_API.G_RET_STS_SUCCESS;
2522
2523 /***** srramakr commented for bug # 3304439
2524 -- Check for the profile option and enable trace
2525 IF (fnd_profile.value('CSI_ENABLE_SQL_TRACE') = 'Y') THEN
2526 dbms_session.set_sql_trace(TRUE);
2527 END IF;
2528
2529 -- End enable trace
2530 ****/
2531
2532 -- Start API body
2533 --
2534
2535 FOR l_asset_hist_csr IN txn_asset_hist_csr (p_transaction_id)
2536 LOOP
2537 l_ins_asset_rec:=l_temp_asset_rec;
2538
2539
2540 --
2541 IF NVL(l_asset_hist_csr.old_instance_id,fnd_api.g_miss_num) = NVL(l_asset_hist_csr.new_instance_id,fnd_api.g_miss_num)
2542 THEN
2543 l_old_ins_asset_rec.instance_id := NULL;
2544 l_new_ins_asset_rec.instance_id := NULL;
2545 ELSE
2546 l_old_ins_asset_rec.instance_id := l_asset_hist_csr.old_instance_id;
2547 l_new_ins_asset_rec.instance_id := l_asset_hist_csr.new_instance_id;
2548 END IF;
2549 --
2550 IF NVL(l_asset_hist_csr.old_fa_asset_id,fnd_api.g_miss_num) = NVL(l_asset_hist_csr.new_fa_asset_id,fnd_api.g_miss_num)
2551 THEN
2552 l_old_ins_asset_rec.fa_asset_id := NULL;
2553 l_new_ins_asset_rec.fa_asset_id := NULL;
2554 ELSE
2555 l_old_ins_asset_rec.fa_asset_id := l_asset_hist_csr.old_fa_asset_id;
2556 l_new_ins_asset_rec.fa_asset_id := l_asset_hist_csr.new_fa_asset_id;
2557 END IF;
2558 --
2559 IF NVL(l_asset_hist_csr.old_fa_book_type_code,fnd_api.g_miss_char) = NVL(l_asset_hist_csr.new_fa_book_type_code,fnd_api.g_miss_char)
2560 THEN
2561 l_old_ins_asset_rec.fa_book_type_code := NULL;
2562 l_new_ins_asset_rec.fa_book_type_code := NULL;
2563 ELSE
2564 l_old_ins_asset_rec.fa_book_type_code := l_asset_hist_csr.old_fa_book_type_code;
2565 l_new_ins_asset_rec.fa_book_type_code := l_asset_hist_csr.new_fa_book_type_code;
2566 END IF;
2567 --
2568 IF NVL(l_asset_hist_csr.old_fa_location_id,fnd_api.g_miss_num) = NVL(l_asset_hist_csr.new_fa_location_id,fnd_api.g_miss_num)
2569 THEN
2570 l_old_ins_asset_rec.fa_location_id := NULL;
2571 l_new_ins_asset_rec.fa_location_id := NULL;
2572 ELSE
2573 l_old_ins_asset_rec.fa_location_id := l_asset_hist_csr.old_fa_location_id;
2574 l_new_ins_asset_rec.fa_location_id := l_asset_hist_csr.new_fa_location_id;
2575 END IF;
2576 --
2577 IF NVL(l_asset_hist_csr.old_asset_quantity,fnd_api.g_miss_num) = NVL(l_asset_hist_csr.new_asset_quantity,fnd_api.g_miss_num)
2578 THEN
2582 l_old_ins_asset_rec.asset_quantity := l_asset_hist_csr.old_asset_quantity;
2579 l_old_ins_asset_rec.asset_quantity := NULL;
2580 l_new_ins_asset_rec.asset_quantity := NULL;
2581 ELSE
2583 l_new_ins_asset_rec.asset_quantity := l_asset_hist_csr.new_asset_quantity;
2584 END IF;
2585 --
2586 IF NVL(l_asset_hist_csr.old_update_status,fnd_api.g_miss_char) = NVL(l_asset_hist_csr.new_update_status,fnd_api.g_miss_char)
2587 THEN
2588 l_old_ins_asset_rec.update_status := NULL;
2589 l_new_ins_asset_rec.update_status := NULL;
2590 ELSE
2591 l_old_ins_asset_rec.update_status := l_asset_hist_csr.old_update_status;
2592 l_new_ins_asset_rec.update_status := l_asset_hist_csr.new_update_status;
2593 END IF;
2594 --
2595 IF NVL(l_asset_hist_csr.old_active_start_date,fnd_api.g_miss_date) = NVL(l_asset_hist_csr.new_active_start_date,fnd_api.g_miss_date)
2596 THEN
2597 l_old_ins_asset_rec.active_start_date := NULL;
2598 l_new_ins_asset_rec.active_start_date := NULL;
2599 ELSE
2600 l_old_ins_asset_rec.active_start_date := l_asset_hist_csr.old_active_start_date;
2601 l_new_ins_asset_rec.active_start_date := l_asset_hist_csr.new_active_start_date;
2602 END IF;
2603 --
2604 IF NVL(l_asset_hist_csr.old_active_end_date,fnd_api.g_miss_date) = NVL(l_asset_hist_csr.new_active_end_date,fnd_api.g_miss_date)
2605 THEN
2606 l_old_ins_asset_rec.active_end_date := NULL;
2607 l_new_ins_asset_rec.active_end_date := NULL;
2608 ELSE
2609 l_old_ins_asset_rec.active_end_date := l_asset_hist_csr.old_active_end_date;
2610 l_new_ins_asset_rec.active_end_date := l_asset_hist_csr.new_active_end_date;
2611 END IF;
2612 --
2613
2614 l_old_ins_asset_tbl(1):=l_old_ins_asset_rec;
2615 csi_asset_pvt.resolve_id_columns
2616 (p_asset_header_tbl => l_old_ins_asset_tbl);
2617 l_old_ins_asset_rec:=l_old_ins_asset_tbl(1);
2618
2619
2620 l_new_ins_asset_tbl(1):=l_new_ins_asset_rec;
2621 csi_asset_pvt.resolve_id_columns
2622 (p_asset_header_tbl => l_new_ins_asset_tbl);
2623 l_new_ins_asset_rec:=l_new_ins_asset_tbl(1);
2624
2625 --
2626 IF NVL(l_old_ins_asset_rec.asset_number,fnd_api.g_miss_char) = NVL(l_new_ins_asset_rec.asset_number,fnd_api.g_miss_char)
2627 THEN
2628 l_old_ins_asset_rec.asset_number := NULL;
2629 l_new_ins_asset_rec.asset_number := NULL;
2630 END IF;
2631 --
2632 IF NVL(l_old_ins_asset_rec.serial_number,fnd_api.g_miss_char) = NVL(l_new_ins_asset_rec.serial_number,fnd_api.g_miss_char)
2633 THEN
2634 l_old_ins_asset_rec.serial_number := NULL;
2635 l_new_ins_asset_rec.serial_number := NULL;
2636 END IF;
2637 --
2638 IF NVL(l_old_ins_asset_rec.tag_number,fnd_api.g_miss_char) = NVL(l_new_ins_asset_rec.tag_number,fnd_api.g_miss_char)
2639 THEN
2640 l_old_ins_asset_rec.tag_number := NULL;
2641 l_new_ins_asset_rec.tag_number := NULL;
2642 END IF;
2643 --
2644 IF NVL(l_old_ins_asset_rec.category,fnd_api.g_miss_char) = NVL(l_new_ins_asset_rec.category,fnd_api.g_miss_char)
2645 THEN
2646 l_old_ins_asset_rec.category := NULL;
2647 l_new_ins_asset_rec.category := NULL;
2648 END IF;
2649 --
2650 IF NVL(l_old_ins_asset_rec.fa_location_segment1,fnd_api.g_miss_char) = NVL(l_new_ins_asset_rec.fa_location_segment1,fnd_api.g_miss_char)
2651 THEN
2652 l_old_ins_asset_rec.fa_location_segment1 := NULL;
2653 l_new_ins_asset_rec.fa_location_segment1 := NULL;
2654 END IF;
2655 --
2656 IF NVL(l_old_ins_asset_rec.fa_location_segment2,fnd_api.g_miss_char) = NVL(l_new_ins_asset_rec.fa_location_segment2,fnd_api.g_miss_char)
2657 THEN
2658 l_old_ins_asset_rec.fa_location_segment2 := NULL;
2659 l_new_ins_asset_rec.fa_location_segment2 := NULL;
2660 END IF;
2661 --
2662 IF NVL(l_old_ins_asset_rec.fa_location_segment3,fnd_api.g_miss_char) = NVL(l_new_ins_asset_rec.fa_location_segment3,fnd_api.g_miss_char)
2663 THEN
2664 l_old_ins_asset_rec.fa_location_segment3 := NULL;
2665 l_new_ins_asset_rec.fa_location_segment3 := NULL;
2666 END IF;
2667 --
2668 IF NVL(l_old_ins_asset_rec.fa_location_segment4,fnd_api.g_miss_char) = NVL(l_new_ins_asset_rec.fa_location_segment4,fnd_api.g_miss_char)
2669 THEN
2670 l_old_ins_asset_rec.fa_location_segment4 := NULL;
2671 l_new_ins_asset_rec.fa_location_segment4 := NULL;
2672 END IF;
2673 --
2674 IF NVL(l_old_ins_asset_rec.fa_location_segment5,fnd_api.g_miss_char) = NVL(l_new_ins_asset_rec.fa_location_segment5,fnd_api.g_miss_char)
2675 THEN
2676 l_old_ins_asset_rec.fa_location_segment5 := NULL;
2677 l_new_ins_asset_rec.fa_location_segment5 := NULL;
2678 END IF;
2679 --
2680 IF NVL(l_old_ins_asset_rec.fa_location_segment6,fnd_api.g_miss_char) = NVL(l_new_ins_asset_rec.fa_location_segment6,fnd_api.g_miss_char)
2681 THEN
2682 l_old_ins_asset_rec.fa_location_segment6 := NULL;
2683 l_new_ins_asset_rec.fa_location_segment6 := NULL;
2687 THEN
2684 END IF;
2685 --
2686 IF NVL(l_old_ins_asset_rec.fa_location_segment7,fnd_api.g_miss_char) = NVL(l_new_ins_asset_rec.fa_location_segment7,fnd_api.g_miss_char)
2688 l_old_ins_asset_rec.fa_location_segment7 := NULL;
2689 l_new_ins_asset_rec.fa_location_segment7 := NULL;
2690 END IF;
2691 --
2692 IF NVL(l_old_ins_asset_rec.date_placed_in_service,fnd_api.g_miss_date) = NVL(l_new_ins_asset_rec.date_placed_in_service,fnd_api.g_miss_date)
2693 THEN
2694 l_old_ins_asset_rec.date_placed_in_service := NULL;
2695 l_new_ins_asset_rec.date_placed_in_service := NULL;
2696 END IF;
2697 --
2698 IF NVL(l_old_ins_asset_rec.description,fnd_api.g_miss_char) = NVL(l_new_ins_asset_rec.description,fnd_api.g_miss_char)
2699 THEN
2700 l_old_ins_asset_rec.description := NULL;
2701 l_new_ins_asset_rec.description := NULL;
2702 END IF;
2703 --
2704 IF NVL(l_old_ins_asset_rec.employee_name,fnd_api.g_miss_char) = NVL(l_new_ins_asset_rec.employee_name,fnd_api.g_miss_char)
2705 THEN
2706 l_old_ins_asset_rec.employee_name := NULL;
2707 l_new_ins_asset_rec.employee_name := NULL;
2708 END IF;
2709 --
2710 IF NVL(l_old_ins_asset_rec.expense_account_number,fnd_api.g_miss_char) = NVL(l_new_ins_asset_rec.expense_account_number,fnd_api.g_miss_char)
2711 THEN
2712 l_old_ins_asset_rec.expense_account_number := NULL;
2713 l_new_ins_asset_rec.expense_account_number := NULL;
2714 END IF;
2715 --
2716 l_ins_asset_rec.instance_asset_id := l_asset_hist_csr.instance_asset_id;
2717 l_ins_asset_rec.old_instance_id := l_old_ins_asset_rec.instance_id ;
2718 l_ins_asset_rec.new_instance_id := l_new_ins_asset_rec.instance_id ;
2719 l_ins_asset_rec.old_fa_asset_id := l_old_ins_asset_rec.fa_asset_id ;
2720 l_ins_asset_rec.new_fa_asset_id := l_new_ins_asset_rec.fa_asset_id ;
2721 l_ins_asset_rec.old_fa_book_type_code := l_old_ins_asset_rec.fa_book_type_code ;
2722 l_ins_asset_rec.new_fa_book_type_code := l_new_ins_asset_rec.fa_book_type_code ;
2723 l_ins_asset_rec.old_fa_location_id := l_old_ins_asset_rec.fa_location_id ;
2724 l_ins_asset_rec.new_fa_location_id := l_new_ins_asset_rec.fa_location_id ;
2725 l_ins_asset_rec.old_asset_quantity := l_old_ins_asset_rec.asset_quantity ;
2726 l_ins_asset_rec.new_asset_quantity := l_new_ins_asset_rec.asset_quantity ;
2727 l_ins_asset_rec.old_update_status := l_old_ins_asset_rec.update_status ;
2728 l_ins_asset_rec.new_update_status := l_new_ins_asset_rec.update_status ;
2729 l_ins_asset_rec.old_active_start_date := l_old_ins_asset_rec.active_start_date ;
2730 l_ins_asset_rec.new_active_start_date := l_new_ins_asset_rec.active_start_date ;
2731 l_ins_asset_rec.old_active_end_date := l_old_ins_asset_rec.active_end_date ;
2732 l_ins_asset_rec.new_active_end_date := l_new_ins_asset_rec.active_end_date ;
2736 l_ins_asset_rec.new_serial_number := l_new_ins_asset_rec.serial_number ;
2733 l_ins_asset_rec.old_asset_number := l_old_ins_asset_rec.asset_number ;
2734 l_ins_asset_rec.new_asset_number := l_new_ins_asset_rec.asset_number ;
2735 l_ins_asset_rec.old_serial_number := l_old_ins_asset_rec.serial_number ;
2737 l_ins_asset_rec.old_tag_number := l_old_ins_asset_rec.tag_number ;
2738 l_ins_asset_rec.new_tag_number := l_new_ins_asset_rec.tag_number ;
2739 l_ins_asset_rec.old_category := l_old_ins_asset_rec.category ;
2743 l_ins_asset_rec.old_fa_location_segment2 := l_old_ins_asset_rec.fa_location_segment2 ;
2740 l_ins_asset_rec.new_category := l_new_ins_asset_rec.category ;
2741 l_ins_asset_rec.old_fa_location_segment1 := l_old_ins_asset_rec.fa_location_segment1 ;
2742 l_ins_asset_rec.new_fa_location_segment1 := l_new_ins_asset_rec.fa_location_segment1 ;
2744 l_ins_asset_rec.new_fa_location_segment2 := l_new_ins_asset_rec.fa_location_segment2 ;
2745 l_ins_asset_rec.old_fa_location_segment3 := l_old_ins_asset_rec.fa_location_segment3 ;
2746 l_ins_asset_rec.new_fa_location_segment3 := l_new_ins_asset_rec.fa_location_segment3 ;
2747 l_ins_asset_rec.old_fa_location_segment4 := l_old_ins_asset_rec.fa_location_segment4 ;
2748 l_ins_asset_rec.new_fa_location_segment4 := l_new_ins_asset_rec.fa_location_segment4 ;
2749 l_ins_asset_rec.old_fa_location_segment5 := l_old_ins_asset_rec.fa_location_segment5 ;
2750 l_ins_asset_rec.new_fa_location_segment5 := l_new_ins_asset_rec.fa_location_segment5 ;
2751 l_ins_asset_rec.old_fa_location_segment6 := l_old_ins_asset_rec.fa_location_segment6 ;
2752 l_ins_asset_rec.new_fa_location_segment6 := l_new_ins_asset_rec.fa_location_segment6 ;
2753 l_ins_asset_rec.old_fa_location_segment7 := l_old_ins_asset_rec.fa_location_segment7 ;
2754 l_ins_asset_rec.new_fa_location_segment7 := l_new_ins_asset_rec.fa_location_segment7 ;
2755 l_ins_asset_rec.old_date_placed_in_service := l_old_ins_asset_rec.date_placed_in_service ;
2756 l_ins_asset_rec.new_date_placed_in_service := l_new_ins_asset_rec.date_placed_in_service ;
2757 l_ins_asset_rec.old_description := l_old_ins_asset_rec.description ;
2758 l_ins_asset_rec.new_description := l_new_ins_asset_rec.description ;
2759 l_ins_asset_rec.old_employee_name := l_old_ins_asset_rec.employee_name ;
2760 l_ins_asset_rec.new_employee_name := l_new_ins_asset_rec.employee_name ;
2761 l_ins_asset_rec.old_expense_account_number := l_old_ins_asset_rec.expense_account_number ;
2762 l_ins_asset_rec.new_expense_account_number := l_new_ins_asset_rec.expense_account_number ;
2763 l_ins_asset_rec.old_fa_mass_addition_id := l_old_ins_asset_rec.fa_mass_addition_id;
2764 l_ins_asset_rec.new_fa_mass_addition_id := l_new_ins_asset_rec.fa_mass_addition_id;
2765
2766 -- x_ins_asset_hist_rec := l_ins_asset_rec ;
2767 i:=i+1;
2768 x_ins_asset_hist_tbl(i) := l_ins_asset_rec ;
2769 x_ins_asset_hist_tbl(i).instance_id := l_asset_hist_csr.instance_id;
2770 END LOOP;
2771
2772 --
2773 -- End of API body
2774
2775 -- Standard check of p_commit.
2776 /*
2777 IF FND_API.To_Boolean( p_commit ) THEN
2778 COMMIT WORK;
2779 END IF;
2780 */
2781
2782 /***** srramakr commented for bug # 3304439
2783 -- Check for the profile option and disable the trace
2784 IF (fnd_profile.value('CSI_ENABLE_SQL_TRACE') = 'Y') THEN
2785 dbms_session.set_sql_trace(false);
2786 END IF;
2787 -- End disable trace
2788 ****/
2789
2790 -- Standard call to get message count and if count is get message info.
2791 FND_MSG_PUB.Count_And_Get
2792 (p_count => x_msg_count ,
2793 p_data => x_msg_data );
2794 EXCEPTION
2795 WHEN OTHERS THEN
2796 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2797 /*
2798 IF fnd_api.to_boolean(p_commit)
2799 THEN
2800 ROLLBACK TO get_instance_asset_hist;
2801 END IF;
2802 */
2803 IF FND_MSG_PUB.Check_Msg_Level
2804 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2805 THEN
2806 FND_MSG_PUB.Add_Exc_Msg
2807 ( G_PKG_NAME, l_api_name );
2808 END IF;
2809 FND_MSG_PUB.Count_And_Get
2810 ( p_count => x_msg_count,
2811 p_data => x_msg_data);
2812
2813 END get_instance_asset_hist ;
2814
2815 PROCEDURE asset_syncup_validation
2816 ( px_instance_sync_tbl IN OUT NOCOPY CSI_ASSET_PVT.instance_sync_tbl,
2820 x_return_status OUT NOCOPY VARCHAR2
2817 px_instance_asset_sync_tbl IN OUT NOCOPY CSI_ASSET_PVT.instance_asset_sync_tbl,
2818 px_fa_asset_sync_tbl IN OUT NOCOPY CSI_ASSET_PVT.fa_asset_sync_tbl,
2819 x_error_msg OUT NOCOPY VARCHAR2,
2821 ) IS
2822 l_fa_asset_sync_tbl csi_asset_pvt.fa_asset_sync_tbl;
2823 l_Sync_Flag VARCHAR2(1) := FND_API.G_TRUE;
2824 l_location_id NUMBER := 0;
2825 l_fa_location_id NUMBER := 0;
2826
2827 CURSOR csi_a_location_cur( l_inst_location_id NUMBER) IS
2828 SELECT fa_location_id
2829 FROM csi_a_locations
2830 WHERE location_id = l_inst_location_id;
2831
2832 BEGIN
2833 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2834 IF px_instance_sync_tbl.count = 0 OR px_instance_asset_sync_tbl.count = 0 OR
2835 px_fa_asset_sync_tbl.count = 0 THEN
2836 x_error_msg := 'Invalid Parameters';
2837 RAISE fnd_api.g_exc_error;
2838 END IF;
2839 /*-- Cursor on instance-asset pl/sql table --*/
2840
2841 IF px_instance_asset_sync_tbl.count > 0 THEN
2842 FOR c_inst_asset_rec IN px_instance_asset_sync_tbl.FIRST..px_instance_asset_sync_tbl.LAST
2843 LOOP
2844 IF px_fa_asset_sync_tbl.count > 0 THEN
2845 /*-- Cursor on Fixed asset pl/sql table --*/
2846
2847 FOR c_fa_asset_rec IN px_fa_asset_sync_tbl.FIRST..px_fa_asset_sync_tbl.LAST
2848 LOOP
2849 /*-- Searching for matching Fixed asset and location --*/
2850 IF px_fa_asset_sync_tbl(c_fa_asset_rec).fa_asset_id =
2851 px_instance_asset_sync_tbl(c_inst_asset_rec).fa_asset_id AND
2852 px_fa_asset_sync_tbl(c_fa_asset_rec).fa_location_id =
2853 px_instance_asset_sync_tbl(c_inst_asset_rec).fa_location_id THEN
2854 /*-- Accumulating matching asset quantity in sync up qty --*/
2855 IF px_fa_asset_sync_tbl(c_fa_asset_rec).sync_up_quantity = FND_API.G_MISS_NUM THEN
2856 px_fa_asset_sync_tbl(c_fa_asset_rec).sync_up_quantity :=
2857 nvl(px_instance_asset_sync_tbl(c_inst_asset_rec).inst_asset_quantity,0);
2858 ELSE
2859 px_fa_asset_sync_tbl(c_fa_asset_rec).sync_up_quantity :=
2860 nvl( px_fa_asset_sync_tbl(c_fa_asset_rec).sync_up_quantity,0)
2861 +nvl(px_instance_asset_sync_tbl(c_inst_asset_rec).inst_asset_quantity,0);
2862 END IF;
2863 /*-- Validating Accumulated sync qty should not be more than Fa asset qty --*/
2864 IF px_fa_asset_sync_tbl(c_fa_asset_rec).sync_up_quantity >
2865 px_fa_asset_sync_tbl(c_fa_asset_rec).fa_asset_quantity THEN
2866 l_Sync_Flag := FND_API.G_FALSE;
2867 END IF;
2868 END IF;
2869 END LOOP;
2870 END IF;
2871 IF px_instance_sync_tbl.count > 0 THEN
2872 /*-- Cursor on Item Instance pl/sql table --*/
2873 FOR c_inst_rec IN px_instance_sync_tbl.FIRST .. px_instance_sync_tbl.LAST
2874 LOOP
2875 /*-- Searching for matching Item Instances or Interface id's --*/
2876 IF (px_instance_sync_tbl(c_inst_rec).instance_id =
2877 px_instance_asset_sync_tbl(c_inst_asset_rec).instance_id ) OR
2878 (px_instance_sync_tbl(c_inst_rec).inst_interface_id =
2879 px_instance_asset_sync_tbl(c_inst_asset_rec).inst_interface_id ) THEN
2880 /*-- Accumulating matching instance quantity in sync up qty --*/
2881 IF px_instance_sync_tbl(c_inst_rec).sync_up_quantity = FND_API.G_MISS_NUM THEN
2882 px_instance_sync_tbl(c_inst_rec).sync_up_quantity :=
2883 px_instance_asset_sync_tbl(c_inst_asset_rec).inst_asset_quantity;
2884 ELSE
2885 px_instance_sync_tbl(c_inst_rec).sync_up_quantity :=
2886 px_instance_sync_tbl(c_inst_rec).sync_up_quantity
2887 +px_instance_asset_sync_tbl(c_inst_asset_rec).inst_asset_quantity;
2888 END IF;
2889
2890 /*-- Getting instance location setup with Asset location --*/
2891 IF l_location_id <> px_instance_sync_tbl( c_inst_rec ).location_id THEN
2892 l_location_id := px_instance_sync_tbl( c_inst_rec ).location_id;
2893 l_fa_location_id := NULL;
2894
2895 OPEN csi_a_location_cur( l_location_id );
2896 FETCH csi_a_location_cur INTO l_fa_location_id ;
2897 CLOSE csi_a_location_cur;
2898 END IF;
2899
2900 /*-- Validating Accumulated sync qty should not be more than Instance qty --*/
2901 /*-- Also Validating instance location setup with Asset location --*/
2902 IF (px_instance_sync_tbl(c_inst_rec).sync_up_quantity >
2903 px_instance_sync_tbl(c_inst_rec).instance_quantity ) OR
2904 (px_instance_asset_sync_tbl(c_inst_asset_rec).fa_location_id
2905 <> NVL(l_fa_location_id,0) ) THEN
2906 l_Sync_Flag := FND_API.G_FALSE;
2907 END IF;
2908 END IF;
2909 END LOOP;
2910 END IF;
2911 END LOOP;
2912 END IF;
2913 /*-- Cursor on Fixed asset pl/sql table --*/
2914
2915 /* FOR c_fa_asset_rec IN px_fa_asset_sync_tbl.FIRST..px_fa_asset_sync_tbl.LAST
2916 LOOP
2917 */ /*-- Validating all Fa asset qty is matched/sync --*/
2918 /* IF px_fa_asset_sync_tbl(c_fa_asset_rec).sync_up_quantity <>
2919 px_fa_asset_sync_tbl(c_fa_asset_rec).fa_asset_quantity THEN
2920 l_Sync_Flag := FND_API.G_FALSE;
2921 END IF;
2922 END LOOP;
2923 */
2924 /*-- Cursor on Item Instance pl/sql table --*/
2925 FOR c_inst_rec IN px_instance_sync_tbl.FIRST .. px_instance_sync_tbl.LAST
2926 LOOP
2930 l_Sync_Flag := FND_API.G_FALSE;
2927 /*-- Validating All Instance qty is matched/sync --*/
2928 IF px_instance_sync_tbl(c_inst_rec).sync_up_quantity <>
2929 px_instance_sync_tbl(c_inst_rec).instance_quantity THEN
2931 END IF;
2932 END LOOP;
2933 /*-- Cursor on Item Instance pl/sql table --*/
2934 FOR c_inst_rec IN px_instance_sync_tbl.FIRST .. px_instance_sync_tbl.LAST
2935 LOOP
2936 /*-- If All Assets/Item Instances are in sync then set vld flag as S else E --*/
2937 IF l_Sync_Flag = FND_API.G_FALSE THEN
2938 px_instance_sync_tbl(c_inst_rec).vld_status := 'E';
2939 ELSE
2940 px_instance_sync_tbl(c_inst_rec).vld_status := 'S';
2941 END IF;
2942 END LOOP;
2943 EXCEPTION
2944 WHEN fnd_api.g_exc_error THEN
2945 x_return_status := FND_API.G_RET_STS_ERROR ;
2946 END asset_syncup_validation;
2947
2948 PROCEDURE get_attached_item_instances
2949 ( p_api_version IN NUMBER,
2950 p_init_msg_list IN VARCHAR2,
2951 p_instance_asset_sync_tbl IN CSI_ASSET_PVT.instance_asset_sync_tbl,
2952 x_instance_sync_tbl OUT NOCOPY CSI_ASSET_PVT.instance_sync_tbl,
2953 x_return_status OUT NOCOPY VARCHAR2,
2954 x_msg_count OUT NOCOPY NUMBER,
2955 x_msg_data OUT NOCOPY VARCHAR2,
2956 p_source_system_name IN VARCHAR2 DEFAULT NULL,
2957 p_called_from_grp IN VARCHAR2 DEFAULT fnd_api.g_false
2958 ) IS
2959 TYPE num_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2960 TYPE char_tbl IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
2961 l_instance_tbl num_tbl;
2962 l_interface_tbl num_tbl;
2963 l_instance_qty_tbl num_tbl;
2964 l_location_id_tbl num_tbl;
2965 l_location_type_code_tbl char_tbl;
2966
2967 l_api_name CONSTANT VARCHAR2(30) := 'GET_ATTACHED_ITEM_INSTANCES';
2968 l_api_version CONSTANT NUMBER := 1.0 ;
2969 l_csi_debug_level NUMBER ;
2970 l_msg_index NUMBER ;
2971 l_msg_count NUMBER ;
2972 l_sql_stmt VARCHAR2(32767) ;
2973 l_inst_cnt NUMBER;
2974 l_dup_flag BOOLEAN;
2975
2976 CURSOR c_item_instances ( p_fa_asset_id NUMBER, p_fa_location_id NUMBER) IS
2977 SELECT a.instance_id,null inst_interface_id, a.quantity, a.location_id ,a.location_type_code
2978 FROM csi_item_instances a, csi_i_assets b
2979 WHERE a.instance_id = b.instance_id
2980 AND b.fa_asset_id = p_fa_asset_id
2981 AND b.fa_location_id = p_fa_location_id ;
2982
2983 CURSOR c_interf_instance ( p_fa_asset_id NUMBER,
2984 p_fa_location_id NUMBER,
2985 p_source_system_name VARCHAR2 ) IS
2986 SELECT a.instance_id,a.inst_interface_id,a.quantity, a.location_id ,a.location_type_code
2987 FROM csi_instance_interface a ,csi_i_asset_interface b
2988 WHERE a.inst_interface_id=b.inst_interface_id
2989 AND b.fa_asset_id = p_fa_asset_id
2990 AND b.fa_location_id = p_fa_location_id
2991 AND a.process_status IN ('R','X')
2992 AND a.source_system_name = nvl(p_source_system_name ,a.source_system_name)
2993 UNION ALL
2994 SELECT a.instance_id,null inst_interface_id, a.quantity, a.location_id ,a.location_type_code
2995 FROM csi_item_instances a, csi_i_assets b
2996 WHERE a.instance_id = b.instance_id
2997 AND b.fa_asset_id = p_fa_asset_id
2998 AND b.fa_location_id = p_fa_location_id
2999 AND NOT EXISTS ( SELECT 1 FROM csi_instance_interface c
3000 WHERE c.instance_id = a.instance_id
3001 AND c.process_status IN ('R','X')
3002 AND c.source_system_name = nvl(p_source_system_name ,c.source_system_name)) ;
3003
3004 BEGIN
3005 -- Standard call to check for call compatibility.
3006 IF NOT FND_API.Compatible_API_Call (l_api_version ,
3007 p_api_version ,
3008 l_api_name ,
3009 G_PKG_NAME )
3010 THEN
3011 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3012 END IF;
3013 -- Initialize message list if p_init_msg_list is set to TRUE.
3014 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3015 FND_MSG_PUB.initialize;
3016 END IF;
3017 -- Initialize API return status to success
3018 x_return_status := FND_API.G_RET_STS_SUCCESS;
3019 -- Check the profile option CSI_DEBUG_LEVEL for debug message reporting
3020 l_csi_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
3021 -- If csi_debug_level = 1 then dump the procedure name
3022 IF (l_csi_debug_level > 0) THEN
3023 debug( 'get_attached_item_instances');
3024 END IF;
3025 -- If the debug level = 2 then dump all the parameters values.
3026 IF (l_csi_debug_level > 1) THEN
3027 debug( 'get_attached_item_instances:'||
3028 p_api_version ||'-'||
3029 p_init_msg_list );
3030 END IF;
3031 -- Validate asset pl/sql table of records is not null
3032 IF p_instance_asset_sync_tbl.count = 0 THEN
3033 IF (l_csi_debug_level > 0) THEN
3034 debug( 'Asset table of records not provided as input parameter');
3035 RAISE FND_API.G_EXC_ERROR;
3036 END IF;
3037 END IF;
3038
3039 FOR inst_asst_rec IN p_instance_asset_sync_tbl.FIRST..p_instance_asset_sync_tbl.LAST
3040 LOOP
3044
3041 IF p_instance_asset_sync_tbl.exists( inst_asst_rec ) THEN
3042
3043 IF p_called_from_grp = fnd_api.g_true THEN
3045 OPEN c_interf_instance(
3046 p_instance_asset_sync_tbl(inst_asst_rec).fa_asset_id , -- :1
3047 p_instance_asset_sync_tbl(inst_asst_rec).fa_location_id,-- :2
3048 p_source_system_name );
3049
3050 FETCH c_interf_instance BULK COLLECT INTO
3051 l_instance_tbl,l_interface_tbl,l_instance_qty_tbl, l_location_id_tbl, l_location_type_code_tbl;
3052 CLOSE c_interf_instance;
3053 ELSE
3054
3055 OPEN c_item_instances
3056 ( p_instance_asset_sync_tbl(inst_asst_rec).fa_asset_id ,
3057 p_instance_asset_sync_tbl(inst_asst_rec).fa_location_id
3058 );
3059 FETCH c_item_instances BULK COLLECT INTO
3060 l_instance_tbl,l_interface_tbl,l_instance_qty_tbl ,l_location_id_tbl,l_location_type_code_tbl;
3061 CLOSE c_item_instances;
3062 END IF;
3063
3064
3065 l_inst_cnt := x_instance_sync_tbl.count;
3066 IF l_instance_tbl.count > 0 THEN
3067 FOR c_op IN 1..l_instance_tbl.COUNT
3068 LOOP
3069 IF l_instance_tbl.exists( c_op ) THEN
3070 l_dup_flag := FALSE;
3071 FOR c_dup IN 1..x_instance_sync_tbl.count
3072 LOOP
3073 IF x_instance_sync_tbl.exists( c_dup ) THEN
3074 IF x_instance_sync_tbl( c_dup).instance_id = l_instance_tbl(c_op) OR
3075 x_instance_sync_tbl( c_dup).inst_interface_id = l_interface_tbl(c_op)
3076 THEN
3077 l_dup_flag := TRUE ;
3078
3079 END IF;
3080 END IF;
3081 END LOOP;
3082 IF l_dup_flag = FALSE THEN
3083
3084 l_inst_cnt := l_inst_cnt +1 ;
3085 x_instance_sync_tbl( l_inst_cnt ).instance_id := l_instance_tbl(c_op);
3086 x_instance_sync_tbl( l_inst_cnt ).inst_interface_id := l_interface_tbl(c_op);
3087 x_instance_sync_tbl( l_inst_cnt ).instance_quantity := l_instance_qty_tbl(c_op);
3088 x_instance_sync_tbl( l_inst_cnt ).location_id := l_location_id_tbl(c_op);
3089 x_instance_sync_tbl( l_inst_cnt ).location_type_code:= l_location_type_code_tbl(c_op);
3090 END IF;
3091 END IF;
3092 END LOOP;
3093 END IF;
3094
3095 END IF;
3096 END LOOP;
3097
3098 EXCEPTION
3099 WHEN FND_API.G_EXC_ERROR THEN
3100 x_return_status := FND_API.G_RET_STS_ERROR ;
3101 FND_MSG_PUB.Count_And_Get
3102 ( p_count => x_msg_count,
3103 p_data => x_msg_data );
3104 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3105 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3106 FND_MSG_PUB.Count_And_Get
3107 ( p_count => x_msg_count,
3108 p_data => x_msg_data );
3109 WHEN OTHERS THEN
3110 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3111 IF FND_MSG_PUB.Check_Msg_Level
3112 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3113 THEN
3114 FND_MSG_PUB.Add_Exc_Msg
3115 ( G_PKG_NAME ,
3116 l_api_name );
3117 END IF;
3118 FND_MSG_PUB.Count_And_Get
3119 ( p_count => x_msg_count,
3120 p_data => x_msg_data );
3121 END get_attached_item_instances;
3122
3123
3124 PROCEDURE get_attached_asset_links
3125 ( p_api_version IN NUMBER,
3126 p_init_msg_list IN VARCHAR2,
3127 p_instance_sync_tbl IN CSI_ASSET_PVT.instance_sync_tbl,
3128 x_instance_asset_sync_tbl OUT NOCOPY CSI_ASSET_PVT.instance_asset_sync_tbl,
3129 x_return_status OUT NOCOPY VARCHAR2,
3135 TYPE num_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3130 x_msg_count OUT NOCOPY NUMBER,
3131 x_msg_data OUT NOCOPY VARCHAR2,
3132 p_source_system_name IN VARCHAR2 DEFAULT NULL,
3133 p_called_from_grp IN VARCHAR2 DEFAULT fnd_api.g_false
3134 ) IS
3136 l_fa_asset_id_tbl num_tbl;
3137 l_fa_asset_loc_id_tbl num_tbl;
3138 l_asset_qty_tbl num_tbl;
3139 l_api_name CONSTANT VARCHAR2(30) := 'GET_ATTACHED_ASSET_LINKS';
3140 l_api_version CONSTANT NUMBER := 1.0 ;
3141 l_csi_debug_level NUMBER ;
3142 l_msg_index NUMBER ;
3143 l_msg_count NUMBER ;
3144 l_sql_stmt VARCHAR2(32767) ;
3145 l_asset_cnt NUMBER;
3146 CURSOR c_instance_assets( p_instance_id NUMBER ) IS
3147 SELECT b.fa_asset_id,b.fa_location_id, b.asset_quantity
3148 FROM csi_item_instances a, csi_i_assets b
3149 WHERE a.instance_id = b.instance_id
3150 AND a.instance_id = p_instance_id ;
3151
3152 CURSOR c_interface_assets( p_instance_id NUMBER
3153 ,p_interface_id NUMBER
3154 ,p_source_system_name VARCHAR2
3155 ) IS
3156 SELECT b.fa_asset_id,b.fa_location_id,b.asset_quantity
3157 FROM csi_instance_interface a ,csi_i_asset_interface b
3158 WHERE a.inst_interface_id = b.inst_interface_id
3159 AND a.process_status IN ('R','X')
3160 AND (a.instance_id = p_instance_id
3161 OR a.inst_interface_id = p_interface_id )
3162 AND a.source_system_name = nvl(p_source_system_name,a.source_system_name)
3163 UNION ALL
3164 SELECT b.fa_asset_id,b.fa_location_id, b.asset_quantity
3165 FROM csi_item_instances a, csi_i_assets b
3166 WHERE a.instance_id = b.instance_id
3167 AND a.instance_id = p_instance_id
3168 AND NOT EXISTS ( SELECT 1
3169 FROM csi_i_asset_interface c,
3170 csi_instance_interface d
3171 WHERE d.instance_id = a.instance_id
3172 AND c.inst_interface_id = d.inst_interface_id
3173 AND d.source_system_name = nvl(p_source_system_name,d.source_system_name)
3174 AND c.fa_asset_id = b.fa_asset_id
3175 AND d.process_status IN ('R','X')
3176 AND c.fa_location_id = b.fa_location_id );
3177
3178 BEGIN
3179 -- Standard call to check for call compatibility.
3180 IF NOT FND_API.Compatible_API_Call (l_api_version ,
3181 p_api_version ,
3182 l_api_name ,
3183 G_PKG_NAME )
3184 THEN
3185 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3186 END IF;
3187 -- Initialize message list if p_init_msg_list is set to TRUE.
3188 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3189 FND_MSG_PUB.initialize;
3190 END IF;
3191 -- Initialize API return status to success
3192 x_return_status := FND_API.G_RET_STS_SUCCESS;
3193 -- Check the profile option CSI_DEBUG_LEVEL for debug message reporting
3194 l_csi_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
3195 -- If csi_debug_level = 1 then dump the procedure name
3196 IF (l_csi_debug_level > 0) THEN
3197 debug( 'get_attached_asset_links');
3198 END IF;
3199 -- If the debug level = 2 then dump all the parameters values.
3200 IF (l_csi_debug_level > 1) THEN
3201 debug( 'get_attached_asset_links:'||
3202 p_api_version ||'-'||
3203 p_init_msg_list );
3204 END IF;
3205 -- Validate asset pl/sql table of records is not null
3206 IF p_instance_sync_tbl.count = 0 THEN
3207 IF (l_csi_debug_level > 0) THEN
3208 debug( 'Item instance not provided as input parameter');
3209 RAISE FND_API.G_EXC_ERROR;
3210 END IF;
3211 END IF;
3212 FOR inst_rec IN p_instance_sync_tbl.FIRST..p_instance_sync_tbl.LAST
3213 LOOP
3214 IF p_instance_sync_tbl.exists( inst_rec ) THEN
3215 l_fa_asset_id_tbl.delete;
3216 l_fa_asset_loc_id_tbl.delete;
3217 l_asset_qty_tbl.delete;
3218
3219 IF p_called_from_grp = fnd_api.g_true THEN
3220 OPEN c_interface_assets( p_instance_sync_tbl(inst_rec).instance_id ,
3221 p_instance_sync_tbl(inst_rec).inst_interface_id,
3222 p_source_system_name);
3223 FETCH c_interface_assets BULK COLLECT INTO
3224 l_fa_asset_id_tbl,l_fa_asset_loc_id_tbl,l_asset_qty_tbl;
3225 CLOSE c_interface_assets;
3226 ELSE
3227 OPEN c_instance_assets( p_instance_sync_tbl(inst_rec).instance_id );
3228 FETCH c_instance_assets BULK COLLECT INTO
3229 l_fa_asset_id_tbl,l_fa_asset_loc_id_tbl,l_asset_qty_tbl;
3230 CLOSE c_instance_assets;
3231 END IF;
3232 l_asset_cnt := x_instance_asset_sync_tbl.count;
3233 IF l_fa_asset_id_tbl.COUNT > 0 THEN
3234 FOR c_op IN 1..l_fa_asset_id_tbl.COUNT
3235 LOOP
3236 IF l_fa_asset_id_tbl.exists(c_op) THEN
3237 l_asset_cnt := l_asset_cnt +1 ;
3238 x_instance_asset_sync_tbl( l_asset_cnt ).instance_id := p_instance_sync_tbl(inst_rec).instance_id ;
3239 x_instance_asset_sync_tbl( l_asset_cnt ).inst_interface_id := p_instance_sync_tbl(inst_rec).inst_interface_id;
3240 x_instance_asset_sync_tbl( l_asset_cnt ).fa_asset_id := l_fa_asset_id_tbl(c_op);
3241 x_instance_asset_sync_tbl( l_asset_cnt ).fa_location_id := l_fa_asset_loc_id_tbl(c_op);
3245 END IF;
3242 x_instance_asset_sync_tbl( l_asset_cnt ).inst_asset_quantity := l_asset_qty_tbl(c_op);
3243 END IF;
3244 END LOOP;
3246 END IF;
3247 END LOOP;
3248 debug(' After get_attached_asset_links');
3249 EXCEPTION
3250 WHEN FND_API.G_EXC_ERROR THEN
3251 x_return_status := FND_API.G_RET_STS_ERROR ;
3252 FND_MSG_PUB.Count_And_Get
3253 ( p_count => x_msg_count,
3254 p_data => x_msg_data );
3255 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3256 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3257 FND_MSG_PUB.Count_And_Get
3258 ( p_count => x_msg_count,
3259 p_data => x_msg_data );
3260 WHEN OTHERS THEN
3261 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3262 IF FND_MSG_PUB.Check_Msg_Level
3263 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3264 THEN
3265 FND_MSG_PUB.Add_Exc_Msg
3266 ( G_PKG_NAME ,
3267 l_api_name );
3268 END IF;
3269 FND_MSG_PUB.Count_And_Get
3270 ( p_count => x_msg_count,
3271 p_data => x_msg_data );
3272 END get_attached_asset_links;
3273
3274 PROCEDURE get_fa_asset_details
3275 ( p_api_version IN NUMBER,
3276 p_init_msg_list IN VARCHAR2,
3277 p_instance_asset_sync_tbl IN CSI_ASSET_PVT.instance_asset_sync_tbl,
3278 x_fa_asset_sync_tab OUT NOCOPY CSI_ASSET_PVT.fa_asset_sync_tbl,
3279 x_return_status OUT NOCOPY VARCHAR2,
3280 x_msg_count OUT NOCOPY NUMBER,
3281 x_msg_data OUT NOCOPY VARCHAR2,
3282 p_source_system_name IN VARCHAR2 DEFAULT NULL,
3283 p_called_from_grp IN VARCHAR2 DEFAULT fnd_api.g_false
3284 ) IS
3285 TYPE num_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3286 l_fa_asset_id_tbl num_tbl;
3287 l_fa_asset_loc_id_tbl num_tbl;
3288 l_fa_asset_qty_tbl num_tbl;
3289 l_api_name CONSTANT VARCHAR2(30) := 'GET_FA_ASSET_DETAILS';
3290 l_api_version CONSTANT NUMBER := 1.0 ;
3291 l_csi_debug_level NUMBER ;
3292 l_msg_index NUMBER ;
3293 l_msg_count NUMBER ;
3294 l_fa_asset_cnt NUMBER := 0;
3295 l_fetch BOOLEAN := TRUE;
3296
3297 CURSOR c_fa_assets ( p_fa_asset_id NUMBER
3298 ,p_location_id NUMBER )IS
3299 SELECT SUM(units_assigned)
3300 FROM fa_distribution_history fadh
3301 WHERE fadh.asset_id = p_fa_asset_id
3302 AND fadh.location_id = p_location_id
3303 AND units_assigned > 0
3304 AND fadh.date_ineffective IS NULL;
3305 BEGIN
3306 -- Standard call to check for call compatibility.
3307 IF NOT FND_API.Compatible_API_Call (l_api_version ,
3308 p_api_version ,
3309 l_api_name ,
3310 G_PKG_NAME )
3311 THEN
3312 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3313 END IF;
3314 -- Initialize message list if p_init_msg_list is set to TRUE.
3315 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3316 FND_MSG_PUB.initialize;
3317 END IF;
3318 -- Initialize API return status to success
3319 x_return_status := FND_API.G_RET_STS_SUCCESS;
3320 -- Check the profile option CSI_DEBUG_LEVEL for debug message reporting
3321 l_csi_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
3322 -- If csi_debug_level = 1 then dump the procedure name
3323 IF (l_csi_debug_level > 0) THEN
3324 debug( 'get_fa_asset_details');
3325 END IF;
3326 -- If the debug level = 2 then dump all the parameters values.
3327 IF (l_csi_debug_level > 1) THEN
3328 debug( 'get_fa_asset_details:'||
3329 p_api_version ||'-'||
3330 p_init_msg_list );
3331 END IF;
3332 -- Validate asset pl/sql table of records is not null
3333 IF p_instance_asset_sync_tbl.count = 0 THEN
3334 IF (l_csi_debug_level > 0) THEN
3335 debug( 'Fixed Asset details not provided as input parameter');
3336 RAISE FND_API.G_EXC_ERROR;
3337 END IF;
3338 END IF;
3339
3340 FOR c_fa_asset IN p_instance_asset_sync_tbl.FIRST..p_instance_asset_sync_tbl.LAST
3341 LOOP
3342 l_fetch := TRUE;
3343 l_fa_asset_cnt := x_fa_asset_sync_tab.count;
3344 IF l_fa_asset_cnt > 0 THEN
3345 FOR c_out IN x_fa_asset_sync_tab.FIRST..x_fa_asset_sync_tab.LAST
3346 LOOP
3347 IF x_fa_asset_sync_tab(c_out).fa_asset_id = p_instance_asset_sync_tbl(c_fa_asset).fa_asset_id
3348 AND x_fa_asset_sync_tab(c_out).fa_location_id = p_instance_asset_sync_tbl(c_fa_asset).fa_location_id THEN
3349 l_fetch := FALSE;
3350 END IF;
3351 END LOOP;
3352 END IF;
3353 IF l_fetch = TRUE THEN
3354 l_fa_asset_cnt := l_fa_asset_cnt + 1 ;
3355 x_fa_asset_sync_tab(l_fa_asset_cnt).fa_asset_id :=p_instance_asset_sync_tbl(c_fa_asset).fa_asset_id ;
3356 x_fa_asset_sync_tab(l_fa_asset_cnt).fa_location_id :=p_instance_asset_sync_tbl(c_fa_asset).fa_location_id ;
3357
3361 CLOSE c_fa_assets;
3358 OPEN c_fa_assets( p_instance_asset_sync_tbl(c_fa_asset).fa_asset_id
3359 , p_instance_asset_sync_tbl(c_fa_asset).fa_location_id );
3360 FETCH c_fa_assets INTO x_fa_asset_sync_tab(l_fa_asset_cnt).fa_asset_quantity;
3362 END IF;
3363 END LOOP;
3364 EXCEPTION
3365 WHEN FND_API.G_EXC_ERROR THEN
3366 x_return_status := FND_API.G_RET_STS_ERROR ;
3367 FND_MSG_PUB.Count_And_Get
3368 ( p_count => x_msg_count,
3369 p_data => x_msg_data );
3370 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3371 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3372 FND_MSG_PUB.Count_And_Get
3373 ( p_count => x_msg_count,
3374 p_data => x_msg_data );
3375 WHEN OTHERS THEN
3376 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3377 IF FND_MSG_PUB.Check_Msg_Level
3378 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3379 THEN
3380 FND_MSG_PUB.Add_Exc_Msg
3381 ( G_PKG_NAME ,
3382 l_api_name );
3383 END IF;
3384 FND_MSG_PUB.Count_And_Get
3385 ( p_count => x_msg_count,
3386 p_data => x_msg_data );
3387 END get_fa_asset_details ;
3388
3389 PROCEDURE Get_syncup_tree
3390 ( px_instance_sync_tbl IN OUT NOCOPY CSI_ASSET_PVT.instance_sync_tbl,
3391 px_instance_asset_sync_tbl IN OUT NOCOPY CSI_ASSET_PVT.instance_asset_sync_tbl,
3392 x_fa_asset_sync_tbl IN OUT NOCOPY CSI_ASSET_PVT.fa_asset_sync_tbl,
3393 x_return_status OUT NOCOPY VARCHAR2,
3394 x_error_msg OUT NOCOPY VARCHAR2,
3395 p_source_system_name IN VARCHAR2 DEFAULT NULL,
3396 p_called_from_grp IN VARCHAR2 DEFAULT fnd_api.g_false
3397 ) IS
3398 l_csi_debug_level NUMBER := 0;
3399 l_instance_sync_tbl CSI_ASSET_PVT.instance_sync_tbl;
3400 l_tmp_instance_sync_tbl CSI_ASSET_PVT.instance_sync_tbl;
3401 l_instance_asset_sync_tbl CSI_ASSET_PVT.instance_asset_sync_tbl;
3402 l_tmp_instance_asset_sync_tbl CSI_ASSET_PVT.instance_asset_sync_tbl;
3403 l_fa_asset_sync_tbl CSI_ASSET_PVT.fa_asset_sync_tbl;
3404 l_Search_Flag VARCHAR2(1);
3405 l_return_status VARCHAR2(1);
3406 l_msg_data VARCHAR2(500);
3407 l_msg_count NUMBER;
3408 l_init_msg_list VARCHAR2(500);
3409 l_match_cnt BINARY_INTEGER := 0;
3410 l_tbl_cnt BINARY_INTEGER := 0;
3411 l_process_loop BOOLEAN := TRUE;
3412
3413 BEGIN
3414 -- Check the profile option CSI_DEBUG_LEVEL for debug message reporting
3415 l_csi_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
3416 IF (l_csi_debug_level > 0) THEN
3417 debug( 'Get_syncup_tree');
3418 END IF;
3419
3420 IF nvl(px_instance_sync_tbl.count,0) > 0 THEN
3421 l_tbl_cnt := px_instance_sync_tbl.count;
3422 l_instance_sync_tbl := px_instance_sync_tbl ;
3423 l_Search_Flag := 'I';
3424 ELSIF nvl(px_instance_asset_sync_tbl.count,0) >0 THEN
3425 l_tbl_cnt := px_instance_asset_sync_tbl.count;
3426 l_instance_asset_sync_tbl := px_instance_asset_sync_tbl;
3427 l_Search_Flag := 'A';
3428 ELSE
3429 IF (l_csi_debug_level > 0) THEN
3430 debug( 'Get_syncup_tree');
3431 END IF;
3432 FND_MESSAGE.Set_Name('CSI', 'CSI_API_INVALID_PARAMETERS');
3433 FND_MSG_PUB.ADD;
3434 RAISE FND_API.G_EXC_ERROR;
3435 END IF;
3436 /*-- Loop untill all item instances or assets are explored --*/
3437 WHILE l_process_loop
3438 LOOP
3439 IF l_Search_Flag = 'I' THEN
3440 l_instance_asset_sync_tbl.DELETE;
3441 csi_asset_pvt.get_attached_asset_links
3442 ( p_api_version => 1.0,
3443 p_init_msg_list => l_init_msg_list,
3444 p_instance_sync_tbl => l_instance_sync_tbl,
3445 x_instance_asset_sync_tbl => l_instance_asset_sync_tbl,
3446 x_return_status => l_return_status,
3447 x_msg_count => l_msg_count,
3448 x_msg_data => l_msg_data,
3449 p_source_system_name => p_source_system_name,
3450 p_called_from_grp => p_called_from_grp
3451 );
3452 l_instance_sync_tbl.delete;
3453
3454 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3455 debug( 'Get_attached_asset_links returned with error');
3456 RAISE FND_API.G_EXC_ERROR;
3457 END IF;
3458
3459 IF nvl(l_instance_asset_sync_tbl.count,0) > 0 THEN
3460 /*-- Verify revisited Nodes --*/
3461 IF px_instance_asset_sync_tbl.count > 0 THEN
3462 l_tmp_instance_asset_sync_tbl := l_instance_asset_sync_tbl;
3463 -- l_instance_asset_sync_tbl.DELETE;
3464
3465 FOR c_fin_asst IN px_instance_asset_sync_tbl.first .. px_instance_asset_sync_tbl.last
3466 LOOP
3467 IF px_instance_asset_sync_tbl.exists( c_fin_asst ) THEN
3468
3469 l_tbl_cnt := l_tmp_instance_asset_sync_tbl.FIRST;
3470
3471 LOOP
3472 /*-- look for instance/interface-id,asset n location match --*/
3473 IF ((l_tmp_instance_asset_sync_tbl(l_tbl_cnt).instance_id =
3474 px_instance_asset_sync_tbl(c_fin_asst).instance_id) OR
3475 (l_tmp_instance_asset_sync_tbl(l_tbl_cnt).inst_interface_id =
3479 px_instance_asset_sync_tbl(c_fin_asst).fa_asset_id
3476 px_instance_asset_sync_tbl(c_fin_asst).inst_interface_id))
3477 AND
3478 l_tmp_instance_asset_sync_tbl(l_tbl_cnt).fa_asset_id =
3480 AND
3481 l_tmp_instance_asset_sync_tbl(l_tbl_cnt).fa_location_id =
3482 px_instance_asset_sync_tbl(c_fin_asst).fa_location_id THEN
3483
3484 l_tmp_instance_asset_sync_tbl.DELETE(l_tbl_cnt);
3485
3486 END IF;
3487 EXIT WHEN l_tmp_instance_asset_sync_tbl.next(l_tbl_cnt ) IS NULL ;
3488 l_tbl_cnt := l_tmp_instance_asset_sync_tbl.next(l_tbl_cnt );
3489 END LOOP; -- c_asst
3490 END IF;
3491 END LOOP; -- c_fin_asst
3492
3493 l_tbl_cnt := 0;
3494 l_process_loop := FALSE;
3495 l_instance_asset_sync_tbl.DELETE;
3496
3497 IF l_tmp_instance_asset_sync_tbl.count > 0 THEN
3498 FOR c_temp In l_tmp_instance_asset_sync_tbl.first..l_tmp_instance_asset_sync_tbl.last LOOP
3499 IF l_tmp_instance_asset_sync_tbl.exists( c_temp ) THEN
3500 l_tbl_cnt := l_tbl_cnt + 1;
3501 l_instance_asset_sync_tbl(l_tbl_cnt) := l_tmp_instance_asset_sync_tbl(c_temp);
3502 l_process_loop := TRUE;
3503 END IF;
3504 END LOOP;
3505 END IF;
3506 l_tmp_instance_asset_sync_tbl.DELETE;
3507 END IF;
3508 IF nvl(l_instance_asset_sync_tbl.count,0) > 0 THEN
3509 l_match_cnt := px_instance_asset_sync_tbl.count;
3510 FOR c_asst IN 1..l_instance_asset_sync_tbl.count
3511 LOOP
3512 IF l_instance_asset_sync_tbl.exists( c_asst ) THEN
3513 l_match_cnt := l_match_cnt + 1;
3514 px_instance_asset_sync_tbl( l_match_cnt ).instance_id
3515 :=l_instance_asset_sync_tbl( c_asst ).instance_id;
3516 px_instance_asset_sync_tbl( l_match_cnt ).inst_interface_id
3517 :=l_instance_asset_sync_tbl( c_asst ).inst_interface_id;
3518 px_instance_asset_sync_tbl( l_match_cnt ).fa_asset_id
3519 :=l_instance_asset_sync_tbl( c_asst ).fa_asset_id;
3520 px_instance_asset_sync_tbl( l_match_cnt ).fa_location_id
3521 :=l_instance_asset_sync_tbl( c_asst ).fa_location_id;
3522 px_instance_asset_sync_tbl( l_match_cnt ).inst_asset_quantity
3523 :=l_instance_asset_sync_tbl( c_asst ).inst_asset_quantity;
3524 END IF;
3525 END LOOP;
3526 l_Search_Flag := 'A';-- Setting Flag to find instances based on asset
3527 l_match_cnt := 0;
3528 l_tbl_cnt := 0;
3529 END IF;
3530 END IF;
3531
3532 ELSIF l_Search_Flag = 'A' THEN
3533 l_instance_sync_tbl.DELETE;
3534 csi_asset_pvt.get_attached_item_instances
3535 ( p_api_version => 1.0,
3536 p_init_msg_list => l_init_msg_list,
3537 p_instance_asset_sync_tbl => l_instance_asset_sync_tbl,
3538 x_instance_sync_tbl => l_instance_sync_tbl,
3539 x_return_status => l_return_status,
3540 x_msg_count => l_msg_count,
3541 x_msg_data => l_msg_data,
3542 p_source_system_name => p_source_system_name,
3543 p_called_from_grp => p_called_from_grp
3544 );
3545 l_instance_asset_sync_tbl.delete;
3546 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3547 debug( 'Get_attached_item_instances returned with error');
3548 RAISE FND_API.G_EXC_ERROR;
3549 END IF;
3550 IF nvl(l_instance_sync_tbl.count,0) > 0 THEN
3551 /*-- Verify revisited Nodes --*/
3552 l_tmp_instance_sync_tbl := l_instance_sync_tbl;
3553 l_instance_sync_tbl.DELETE;
3554
3555 IF px_instance_sync_tbl.count > 0 THEN
3556 FOR c_fin_inst IN px_instance_sync_tbl.first..px_instance_sync_tbl.last
3557 LOOP
3558 IF px_instance_sync_tbl.exists( c_fin_inst ) THEN
3559 l_tbl_cnt := l_tmp_instance_sync_tbl.FIRST;
3560 LOOP
3561 /*-- look for instance match --*/
3562 IF (l_tmp_instance_sync_tbl( l_tbl_cnt ).instance_id =
3563 px_instance_sync_tbl( c_fin_inst ).instance_id) OR
3564 (l_tmp_instance_sync_tbl( l_tbl_cnt ).inst_interface_id =
3565 px_instance_sync_tbl(c_fin_inst ).inst_interface_id)THEN
3566
3567 l_tmp_instance_sync_tbl.DELETE( l_tbl_cnt );
3568 END IF;
3569 EXIT WHEN l_tmp_instance_sync_tbl.next(l_tbl_cnt) IS NULL ;
3570 l_tbl_cnt := l_tmp_instance_sync_tbl.next(l_tbl_cnt);
3571 END LOOP; -- c_inst
3572 END IF;
3573 END LOOP; -- c_fin_inst
3574
3575 l_tbl_cnt := 0;
3576 l_instance_sync_tbl.delete;
3577 l_process_loop := FALSE;
3578 IF l_tmp_instance_sync_tbl.count > 0 THEN
3579 FOR c_temp in l_tmp_instance_sync_tbl.FIRST..l_tmp_instance_sync_tbl.LAST LOOP
3580 IF l_tmp_instance_sync_tbl.exists(c_temp) THEN
3581 l_tbl_cnt := l_tbl_cnt +1 ;
3582 l_instance_sync_tbl(l_tbl_cnt) := l_tmp_instance_sync_tbl(c_temp);
3583 l_process_loop := TRUE;
3584 END IF;
3585 END LOOP;
3586 END IF;
3587 l_tmp_instance_sync_tbl.delete;
3588
3589 END IF;
3590
3591 IF nvl(l_instance_sync_tbl.count,0) > 0 THEN
3592 l_match_cnt := nvl(px_instance_sync_tbl.count ,0);
3593 FOR c_inst IN l_instance_sync_tbl.first..l_instance_sync_tbl.last
3597 l_match_cnt := l_match_cnt + 1;
3594 LOOP
3595 IF l_instance_sync_tbl.exists( c_inst ) THEN
3596
3598 px_instance_sync_tbl( l_match_cnt ).instance_id
3599 :=l_instance_sync_tbl( c_inst ).instance_id;
3600 px_instance_sync_tbl( l_match_cnt ).inst_interface_id
3601 :=l_instance_sync_tbl( c_inst ).inst_interface_id;
3602 px_instance_sync_tbl( l_match_cnt ).instance_quantity
3603 :=l_instance_sync_tbl( c_inst ).instance_quantity;
3604 px_instance_sync_tbl( l_match_cnt ).location_id
3605 :=l_instance_sync_tbl( c_inst ).location_id;
3606 px_instance_sync_tbl( l_match_cnt ).location_type_code
3607 :=l_instance_sync_tbl( c_inst ).location_type_code;
3608
3609 END IF;
3610 END LOOP;
3611 l_Search_Flag := 'I';-- Setting Flag to find assets based on instanace
3612 END IF;
3613 END IF;
3614 ELSE -- l_Search_Flag <> 'A' OR 'I' THEN
3615 EXIT;
3616 END IF;
3617 END LOOP;
3618 get_fa_asset_details
3619 (
3620 p_api_version => 1.0,
3621 p_init_msg_list => l_init_msg_list,
3622 p_instance_asset_sync_tbl => px_instance_asset_sync_tbl,
3623 x_fa_asset_sync_tab => x_fa_asset_sync_tbl,
3624 x_return_status => l_return_status,
3625 x_msg_count => l_msg_count,
3626 x_msg_data => l_msg_data,
3627 p_source_system_name => p_source_system_name,
3628 p_called_from_grp => p_called_from_grp);
3629
3630 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3631 debug( 'Get_fa_asset_details returned with error');
3632 RAISE FND_API.G_EXC_ERROR;
3633 END IF;
3634
3635 EXCEPTION
3636 WHEN FND_API.G_EXC_ERROR THEN
3637 x_return_status := FND_API.G_RET_STS_ERROR ;
3638 END Get_syncup_tree ;
3639
3640 PROCEDURE create_instance_assets (
3641 p_api_version IN number,
3642 p_commit IN varchar2,
3643 p_init_msg_list IN varchar2,
3644 p_validation_level IN number,
3645 p_instance_asset_tbl IN OUT nocopy csi_datastructures_pub.instance_asset_tbl,
3646 p_txn_rec IN OUT nocopy csi_datastructures_pub.transaction_rec,
3647 p_lookup_tbl IN OUT nocopy csi_asset_pvt.lookup_tbl,
3648 p_asset_count_rec IN OUT nocopy csi_asset_pvt.asset_count_rec,
3649 p_asset_id_tbl IN OUT nocopy csi_asset_pvt.asset_id_tbl,
3650 p_asset_loc_tbl IN OUT nocopy csi_asset_pvt.asset_loc_tbl,
3651 x_return_status OUT nocopy varchar2,
3652 x_msg_count OUT nocopy number,
3653 x_msg_data OUT nocopy varchar2)
3654 IS
3655 l_api_name varchar2(30) := 'create_instance_assets';
3656 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
3657 l_msg_count number;
3658 l_msg_data varchar2(2000);
3659 BEGIN
3660 x_return_status := fnd_api.g_ret_sts_success;
3661
3662 SAVEPOINT create_instance_assets;
3663
3664 IF p_instance_asset_tbl.COUNT > 0 THEN
3665
3666 FOR ia_ind IN p_instance_asset_tbl.FIRST .. p_instance_asset_tbl.LAST
3667 LOOP
3668
3669 create_instance_asset(
3670 p_api_version => 1.0,
3671 p_commit => fnd_api.g_false,
3672 p_init_msg_list => fnd_api.g_true,
3673 p_validation_level => fnd_api.g_valid_level_full,
3674 p_instance_asset_rec => p_instance_asset_tbl(ia_ind),
3675 p_txn_rec => p_txn_rec,
3676 p_lookup_tbl => p_lookup_tbl,
3677 p_asset_count_rec => p_asset_count_rec,
3678 p_asset_id_tbl => p_asset_id_tbl,
3679 p_asset_loc_tbl => p_asset_loc_tbl,
3680 x_return_status => l_return_status,
3681 x_msg_count => l_msg_count,
3682 x_msg_data => l_msg_data);
3683
3684 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3685 RAISE fnd_api.g_exc_error;
3686 END IF;
3687
3688 END LOOP;
3689
3690 END IF;
3691
3692 EXCEPTION
3693 WHEN fnd_api.g_exc_error THEN
3694 ROLLBACK TO create_instance_assets;
3695 x_return_status := fnd_api.g_ret_sts_error;
3696 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data );
3697 WHEN others THEN
3698 ROLLBACK TO create_instance_assets;
3699 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3700 fnd_msg_pub.add_exc_msg(g_pkg_name , l_api_name);
3701 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
3702 END create_instance_assets;
3703
3704 END csi_asset_pvt;
3705